package com.shopping.dao;

import java.sql.*;
import java.util.*;

import com.comm.db.*;
import com.comm.util.*;
import com.comm.vo.*;

public class UserDao {

    public int add(GenericVO gvo) throws SQLException {

        String userName = gvo.getItemStr("USERNAME");
        String pass = gvo.getItemStr("PASSWORD");
        String tel = gvo.getItemStr("TEL");
        String qq = gvo.getItemStr("QQ");
        String email = gvo.getItemStr("EMAIL");
        Timestamp createtime = new Timestamp(System.currentTimeMillis());

        Vector param = new Vector();
        String sql =
                "INSERT INTO user (username, password, tel, qq, email, createtime, status )" +
                " VALUES (?, ?, ?, ?, ?, ?, 1)";
        param.addElement(userName);
        param.addElement(pass);
        param.addElement(tel);
        param.addElement(qq);
        param.addElement(email);
        param.addElement(createtime);

        DBFactory.getDBI().execute(sql, param);
        int userID = -1;
        sql = "SELECT user_id FROM user WHERE username=?";
        param.clear();
        param.add(userName);

        Vector tv = DBFactory.getDBI().getResult(sql, param);
        if (tv.size() > 0) {
            userID = BaseUtil.toInt(((GenericVO) tv.elementAt(0)).getItem(
                    "USER_ID"));
        }

        return userID;
    }

    public void modify(GenericVO gvo) throws SQLException {

        String userId = gvo.getItemStr("USER_ID");
        String realName = gvo.getItemStr("REALNAME");
        String tel = gvo.getItemStr("TEL");
        String qq = gvo.getItemStr("QQ");
        String email = gvo.getItemStr("EMAIL");
        String city = gvo.getItemStr("CITY");
        String ic = gvo.getItemStr("IC");
        String sex = gvo.getItemStr("SEX");
        String age = gvo.getItemStr("AGE");
        String marry = gvo.getItemStr("MARRY");
        String mobile = gvo.getItemStr("MOBILE");
        String post = gvo.getItemStr("POST");
        String address = gvo.getItemStr("ADDRESS");

        Vector param = new Vector();
        String sql = "UPDATE user " +
                     "SET realname = ?, tel=?, qq=?, email=?, city = ?, ic = ?" +
                     ", sex = ?, age = ?, marry = ?, mobile = ?, post = ?, address = ? " +
                     "WHERE user_id=?";
        param.addElement(realName);
        param.addElement(tel);
        param.addElement(qq);
        param.addElement(email);
        param.addElement(city);
        param.addElement(ic);
        param.addElement(sex);
        param.addElement(age);
        param.addElement(marry);
        param.addElement(mobile);
        param.addElement(post);
        param.addElement(address);
        param.addElement(userId);

        DBFactory.getDBI().execute(sql, param);
    }

    public ListVO search(GenericVO gvo) throws SQLException {
        Vector param = new Vector();
        StringBuffer sb = new StringBuffer();

        sb.append("SELECT * FROM user ORDER BY user_id ");
        return DBFactory.getDBI().getResult(sb.toString(), param,
                                            gvo.getListVO());
    }

    public GenericVO getDetail(int userId) throws SQLException {
        String sql = "SELECT * FROM user WHERE user_id=" + userId;
        Vector r = DBFactory.getDBI().getResult(sql);
        if (r.size() > 0) {
            return (GenericVO) r.elementAt(0);
        } else {
            return null;
        }
    }

    public void updateUserDot(int userId, int dot) throws SQLException {
        String sql = "UPDATE user SET dot=dot-" + dot + " WHERE user_id=" +
                     userId;
        DBFactory.getDBI().execute(sql);
    }

    public GenericVO login(String userName, String password) throws
            SQLException {
        String sql = "SELECT * FROM user WHERE username='" + userName +
                     "' AND password='" + password + "'";
        Vector r = DBFactory.getDBI().getResult(sql);
        if (r.size() > 0) {
            return (GenericVO) r.elementAt(0);
        } else {
            return null;
        }
    }

    public int passModify(int userId, String oldPass, String newPass) throws
            SQLException {
        String sql = "SELECT * FROM user WHERE user_id=" + userId +
                     " AND password='" + oldPass + "'";
        Vector r = DBFactory.getDBI().getResult(sql);
        if (r.size() > 0) {
            sql = "UPDATE user SET password='" + newPass + "' WHERE user_id=" +
                  userId;
            DBFactory.getDBI().execute(sql);
            return 1;
        } else {
            return 0;
        }
    }
}