package com.wang.db;

import java.sql.*;
import com.wang.bean.*;
import java.util.ArrayList;
import java.text.SimpleDateFormat;
import java.util.Calendar;

public class MyDb
{
    private Connection conn = null;
    private ResultSet res = null;
    private java.sql.PreparedStatement prepar = null;
    private boolean flag = false;
    private java.sql.CallableStatement proc = null;
    private int pagecount = 0;
    private int pagedata = 0;
    public MyDb()
    {
        try
        {
            Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
            conn = DriverManager.getConnection("jdbc:microsoft:sqlserver://127.0.0.1:1433;databasename=MyShop", "sa", "");
            System.out.println("連接成功");
        }
        catch (SQLException ex)
        {
            //System.out.println(ex.getMessage() + "1路徑錯誤");
        }
        catch (ClassNotFoundException ex)
        {
            // System.out.println(ex.getMessage() + "驅動錯誤");
        }

    }

    public userBean checkUsersLogin(String userName, String userPwd)
    {
        userBean useBean;
        flag = false;
        try
        {
            String sql = "select count(*) from users where userName=? and userPwd=?";
            prepar = conn.prepareStatement(sql);
            prepar.setString(1, userName);
            prepar.setString(2, userPwd);
            res = prepar.executeQuery();
            if (res.next())
            {
                if (res.getInt(1) > 0)
                {
                    useBean = this.getUser(userName);
                }
                else
                {
                    useBean = null;
                }
            }
            else
            {
                useBean = null;
            }
        }
        catch (Exception e)
        {
            useBean = null;
            e.printStackTrace();
        }
        return useBean;
    }

    public userBean getUser(String userName)
    {
        userBean useBean = new userBean();
        String sql = "select * from users where userName=?";
        try
        {
            prepar = conn.prepareStatement(sql);
            prepar.setString(1, userName);
            res = prepar.executeQuery();
            while (res.next())
            {
                useBean.setRealName(res.getString("realName"));
                useBean.setUserAdds(res.getString("UserAdds"));
                useBean.setUserAge(res.getInt("UserAge"));
                useBean.setUserCard(res.getString("Usercard"));
                useBean.setUserCity(res.getInt("UserCity"));
                useBean.setUserCode(res.getInt("UserCode"));
                useBean.setUserId(res.getInt("userId"));
                useBean.setUserMail(res.getString("UserMail"));
                useBean.setUserName(res.getString("userName"));
                useBean.setUserPhone(res.getString("UserPhone"));
                useBean.setUserPwd(res.getString("UserPwd"));
                useBean.setUserSex(res.getInt("UserSex"));
                useBean.setUserStrp(res.getInt("UserStep"));
                useBean.setUserWork(res.getString("UserWork"));
            }
        }
        catch (SQLException ex)
        {
            ex.printStackTrace();
        }

        return useBean;
    }

    public String getCity(int cityId)
    {
        try
        {

            prepar = conn.prepareStatement("select Display from City where CityId=?");
            prepar.setInt(1, cityId);
            res = prepar.executeQuery();
            res.next();
            return res.getString("Display");
        }
        catch (SQLException ex)
        {

            return null;
        }

    }

    public String getStep(int stepId)
    {
        try
        {

            prepar = conn.prepareStatement("select Display from Step where StepId=?");
            prepar.setInt(1, stepId);
            res = prepar.executeQuery();
            res.next();
            return res.getString("Display");
        }
        catch (SQLException ex)
        {

            return null;
        }

    }

    public int getUserOrderCount(int userId)
    {
        String sql = "select count(*) from OrderForm where userId=?";
        try
        {
            prepar = conn.prepareStatement(sql);
            prepar.setInt(1, userId);
            res = prepar.executeQuery();
            res.next();
            return res.getInt(1);
        }
        catch (SQLException ex)
        {
            ex.printStackTrace();
        }
        return 0;
    }

    public userBean getUser(int userId)
    {
        userBean useBean = new userBean();
        String sql = "select * from users where userId=?";
        try
        {
            prepar = conn.prepareStatement(sql);
            prepar.setInt(1, userId);
            res = prepar.executeQuery();
            while (res.next())
            {
                useBean.setRealName(res.getString("realName"));
                useBean.setUserAdds(res.getString("UserAdds"));
                useBean.setUserAge(res.getInt("UserAge"));
                useBean.setUserCard(res.getString("Usercard"));
                useBean.setUserCity(res.getInt("UserCity"));
                useBean.setUserCode(res.getInt("UserCode"));
                useBean.setUserId(res.getInt("userId"));
                useBean.setUserMail(res.getString("UserMail"));
                useBean.setUserName(res.getString("userName"));
                useBean.setUserPhone(res.getString("UserPhone"));
                useBean.setUserPwd(res.getString("UserPwd"));
                useBean.setUserSex(res.getInt("UserSex"));
                useBean.setUserStrp(res.getInt("UserStep"));
                useBean.setUserWork(res.getString("UserWork"));
            }
        }
        catch (SQLException ex)
        {
            ex.printStackTrace();
        }

        return useBean;
    }

    public boolean insertUser(userBean useBean)
    {
        boolean bool = false;
        if (!this.hasUser(useBean.getUserName()))
        {

            bool = true;
            String sql = "insert into users (userName,UserPwd,realName,UserSex,UserPhone,UserMail,UserCity,UserAdds,UserCode,UserWork,Usercard,UserAge) values(?,?,?,?,?,?,?,?,?,?,?,?)";
            try
            {
                prepar = conn.prepareStatement(sql);
                prepar.setString(1, useBean.getUserName());
                prepar.setString(2, useBean.getUserPwd());
                prepar.setString(3, useBean.getRealName());
                prepar.setInt(4, useBean.getUserSex());
                prepar.setString(5, useBean.getUserPhone());
                prepar.setString(6, useBean.getUserMail());
                prepar.setInt(7, useBean.getUserCity());
                prepar.setString(8, useBean.getUserAdds());
                prepar.setInt(9, useBean.getUserCode());
                prepar.setString(10, useBean.getUserWork());
                prepar.setString(11, useBean.getUserCard());
                prepar.setInt(12, useBean.getUserAge());
                int flag = prepar.executeUpdate();
                if (flag == 0)
                {
                    bool = false;
                }
            }
            catch (SQLException ex)
            {
                ex.printStackTrace();
                bool = false;
            }
        }
        return bool;
    }
    public boolean updateUser(userBean useBean)
        {
            boolean bool = false;

                bool = true;
                String sql = "update users set userName=?,UserPwd=?,realName=?,UserSex=?,UserPhone=?,UserMail=?,UserCity=?,UserAdds=?,UserCode=?,UserWork=?,Usercard=?,UserAge=? where userId=?";
                try
                {
                    prepar = conn.prepareStatement(sql);
                    prepar.setString(1, useBean.getUserName());
                    prepar.setString(2, useBean.getUserPwd());
                    prepar.setString(3, useBean.getRealName());
                    prepar.setInt(4, useBean.getUserSex());
                    prepar.setString(5, useBean.getUserPhone());
                    prepar.setString(6, useBean.getUserMail());
                    prepar.setInt(7, useBean.getUserCity());
                    prepar.setString(8, useBean.getUserAdds());
                    prepar.setInt(9, useBean.getUserCode());
                    prepar.setString(10, useBean.getUserWork());
                    prepar.setString(11, useBean.getUserCard());
                    prepar.setInt(12, useBean.getUserAge());
                    prepar.setInt(13, useBean.getUserId());
                    int flag = prepar.executeUpdate();
                    if (flag == 0)
                    {
                        bool = false;
                    }
                }
                catch (SQLException ex)
                {
                    ex.printStackTrace();
                    bool = false;
                }
            return bool;
        }

    public boolean hasUser(String userName)
    {