package card;

import java.util.ArrayList;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.PreparedStatement;

/**
 * <p>Title: Card</p>
 *
 * <p>Description: </p>
 *
 * <p>Copyright: Copyright (c) 2007</p>
 *
 * <p>Company: </p>
 *
 * @author not attributable
 * @version 1.0
 */
public class DatabaseOperate {
    cardmodel cm;
    typemodel tm;
    allmodel am;
    public DatabaseOperate() {
    }

    /**
     * 功能：從數據庫中查詢出所有滿足條件的記錄
     * @param sql2 String
     * @return ArrayList
     */
    public ArrayList selectall(String sql2) {
        ArrayList al = new ArrayList();
        Connection conn;
        Statement stm;
        ResultSet rs;
        String sql = "select carid,typetable.tpid,tpname,frdname,sex,birthday,depart,power,homephone,officephone,mobil,address,number,email from cardtable,typetable where cardtable.tpid=typetable.tpid " +
                     sql2;
        try {
            conn = new dbconn().getconnection();
            stm = conn.createStatement();
            rs = stm.executeQuery(sql);
            while (rs.next()) {
                cm = new cardmodel();
                tm = new typemodel();
                am = new allmodel();
                cm.setCarid(rs.getInt("carid"));
                tm.setTyid(rs.getInt(2));
                tm.setTyname(rs.getString("tpname"));
                cm.setFrdname(rs.getString("frdname"));
                cm.setSex(rs.getString("sex"));
                cm.setBirthday(rs.getString("birthday"));
                cm.setDepart(rs.getString("depart"));
                cm.setPower(rs.getString("power"));
                cm.setHomephone(rs.getString("homephone"));
                cm.setOfficephone(rs.getString("officephone"));
                cm.setMobil(rs.getString("mobil"));
                cm.setAddress(rs.getString("address"));
                cm.setNumber(rs.getString("number"));
                cm.setEmail(rs.getString("email"));
                am.setall(cm, tm);
                al.add(am);
            }
            rs.close();
            stm.close();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return al;
    }

    /**
     * 功能：刪除指定cardid的記錄
     * @param cardid String
     * @return boolean
     */
    public boolean delInfo(String cardid) {
        boolean flag = true;
        Connection conn;
        PreparedStatement pstm;
        String sql = "delete from cardtable where carid=(?)";
        try {
            conn = new dbconn().getconnection();
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, cardid);
            int intre = pstm.executeUpdate();
            if (intre == 0) {
                flag = false;
            }
            pstm.close();
            conn.close();
        } catch (Exception ex) {
            flag = false;
            ex.printStackTrace();
        }
        return flag;
    }

    /**
     * 類別的增刪改
     * @param tm typemodel
     * @return boolean
     */
    public boolean typeinsert(typemodel tm) {
        boolean flag = false;
        Connection db = new dbconn().getconnection();
        PreparedStatement ps = null;
        String sql = "insert into typetable(tpname) values (?)";
        try {
            ps = db.prepareStatement(sql);
            ps.setString(1, tm.getTyname());
            int n = ps.executeUpdate();
            if (n != 0) {
                flag = true;
            }
            ps.close();
            db.close();
        } catch (Exception ex) {
            System.out.println(ex);
            return false;
        }
        return flag;
    }

    public boolean typeupdate(typemodel tm) {
        boolean flag = false;
        Connection db = new dbconn().getconnection();
        PreparedStatement ps = null;
        String sql = "update typetable set tpname=? where tpid=?";
        try {
            ps = db.prepareStatement(sql);
            ps.setString(1, tm.getTyname());
            ps.setInt(2, tm.getTyid());
            int n = ps.executeUpdate();
            if (n != 0) {
                flag = true;
            }
            ps.close();
            db.close();
        } catch (Exception ex) {
            System.out.println(ex);
            return false;
        }
        return flag;
    }

    public boolean typedelete(typemodel tm) {
        boolean flag = false;
        Connection db = new dbconn().getconnection();
        PreparedStatement ps = null;
        String sql = "delete typetable where tpid=?";
        try {
            ps = db.prepareStatement(sql);
            ps.setInt(1, tm.getTyid());
            int n = ps.executeUpdate();
            if (n != 0) {
                flag = true;
            }
            ps.close();
            db.close();
        } catch (Exception ex) {
            System.out.println(ex);
            return false;
        }
        return flag;
    }

    /**
     * card類的增修sql語句
     */
    public cardmodel cardselect(cardmodel cd) {
        int car = cd.getCarid();
        cardmodel carmodel = new cardmodel();
        Connection db = new dbconn().getconnection();
        Statement ps = null;
        String sql = "select tpid,frdname,sex,birthday,depart,power,homephone,officephone,mobil,address,number,email from cardtable where carid=" +
                     car + "";
        try {
            ps = db.createStatement();
            ResultSet res = ps.executeQuery(sql);
            while (res.next()) {
                carmodel.setTpid(res.getInt(1));
                carmodel.setFrdname(res.getString(2));
                carmodel.setSex(res.getString(3));
                carmodel.setBirthday(res.getString(4));
                carmodel.setDepart(res.getString(5));
                carmodel.setPower(res.getString(6));
                carmodel.setHomephone(res.getString(7));
                carmodel.setOfficephone(res.getString(8));
                carmodel.setMobil(res.getString(9));
                carmodel.setAddress(res.getString(10));
                carmodel.setNumber(res.getString(11));
                carmodel.setEmail(res.getString(12));
            }
            res.close();
            ps.close();
            db.close();
        } catch (Exception ex) {
            System.out.println(ex);
            System.out.println("獲得名片信息時出錯");
        }
        return carmodel;
    }

    public int getTypeId(allmodel am) {
        int flag=0;
        Connection db = new dbconn().getconnection();


        cardmodel cm = am.getcm();
        typemodel tm = am.gettym();
        String name = "select tpid from typetable where tpname='" +
                      tm.getTyname() + "'";
        try {

            Statement sss = db.createStatement();
            ResultSet ress = sss.executeQuery(name);
            while (ress.next()) {
                flag=ress.getInt(1);
            }
            ress.close();
            sss.close();

        } catch (Exception ex) {
            System.out.println("根據類別名字查找類別編號時出錯");
            System.out.println(ex);
        }
        return flag;
    }

    public boolean cardupdate(allmodel am) {
        boolean flag = false;
        Connection db = new dbconn().getconnection();
        PreparedStatement ps = null;

        cardmodel cm = am.getcm();
        typemodel tm = am.gettym();
        String name = "select tpid from typetable where tpname='" +
                      tm.getTyname() + "'";
        try {
            Connection db2 = new dbconn().getconnection();
            Statement sss = db2.createStatement();
            ResultSet ress = sss.executeQuery(name);
            while (ress.next()) {
                cm.setTpid(ress.getInt(1));
            }
            ress.close();
            sss.close();
            db2.close();
        } catch (Exception ex) {
            System.out.println("根據類別名字查找類別編號時出錯");
            System.out.println(ex);
        }
        String sql = "update cardtable set tpid=?,frdname=?,sex=?,birthday=?,depart=?,power=?,homephone=?,officephone=?,mobil=?,address=?,number=?,email=? where carid=?";

        try {
            ps = db.prepareStatement(sql);
            ps.setInt(1, cm.getTpid());
            ps.setString(2, cm.getFrdname());
            ps.setString(3, cm.getSex());
            ps.setString(4, cm.getBirthday());
            ps.setString(5, cm.getDepart());
            ps.setString(6, cm.getPower());
            ps.setString(7, cm.getHomephone());
            ps.setString(8, cm.getOfficephone());
            ps.setString(9, cm.getMobil());
            ps.setString(10, cm.getAddress());
            ps.setString(11, cm.getNumber());
            ps.setString(12, cm.getEmail());
            ps.setInt(13, cm.getCarid());
            int n = ps.executeUpdate();
            if (n != 0) {
                flag = true;
            }
            ps.close();
            db.close();
        } catch (Exception ex) {
            System.out.println(ex);
            System.out.println("更新名片信息時出錯");
        }
        return flag;
    }

    /**
     * 功能：增加名片
     * @param cd cardmodel
     * @return boolean
     */
    public boolean cardinsert(allmodel am) {
        int intTpId=getTypeId(am);
        boolean flag = false;
        Connection db = new dbconn().getconnection();
        PreparedStatement ps = null;
        String sql = "insert into cardtable(carid,tpid,frdname,sex,birthday,depart,power,homephone,officephone,mobil,address,number,email) values (?,?,?,?,?,?,?,?,?,?,?,?,?)";

        try {
            ps = db.prepareStatement(sql);
            ps.setInt(1,am.getcm().getCarid());
            ps.setInt(2, intTpId);
            ps.setString(3, am.getcm().getFrdname());
            ps.setString(4, am.getcm().getSex());
            ps.setString(5, am.getcm().getBirthday());
            ps.setString(6, am.getcm().getDepart());
            ps.setString(7, am.getcm().getPower());
            ps.setString(8, am.getcm().getHomephone());
            ps.setString(9, am.getcm().getOfficephone());
            ps.setString(10, am.getcm().getMobil());
            ps.setString(11, am.getcm().getAddress());
            ps.setString(12, am.getcm().getNumber());
            ps.setString(13, am.getcm().getEmail());
            int n = ps.executeUpdate();
            if (n != 0) {
                flag = true;
            }
            ps.close();
            db.close();
        } catch (Exception ex) {
            System.out.println(ex);
            System.out.println("創建名片信息時出錯");
        }
        return flag;
    }

    public int checkLoginInfo(usermodel um) {
        int ctn = 100;
        Connection conn = new dbconn().getconnection();
        String sql = "select username,password from usertable where username='" +
                     um.getUsername() + "'";
        try {
            Statement s = conn.createStatement();
            ResultSet rs = s.executeQuery(sql);
            boolean b = rs.next();
            if (b) {
                if (um.getPassword().equals(rs.getString(2).trim())) {
                    ctn = 0; //正確
                } else {
                    ctn = 1; //密碼不正確
                }
            } else {
                ctn = 2; //無用戶名
            }
            rs.close();
            s.close();
            conn.close();
        } catch (Exception ex) {
            System.out.println("sss" + ex.getMessage());
        }
        return ctn;
    }

    public boolean updateUserPassword(usermodel um) { //修改用戶密碼
        boolean b = false;
        Connection conn = new dbconn().getconnection();
        String sql = "update usertable set password='" + um.getPassword() +
                     "' where username='" + um.getUsername() + "'";
        try {
            Statement s = conn.createStatement();
            int e = s.executeUpdate(sql);
            if (e != 0) {
                b = true;
            }
            conn.commit();
            s.close();
            conn.close();
        } catch (Exception ex) {
            System.out.println("updateUserPassword:" + ex.getMessage());
        }
        return b;
    }

    public ArrayList getBirthdayArr() {
        Connection conn = new dbconn().getconnection();
        ArrayList birthday = new ArrayList();
        String sql = "select birthday,frdname from cardtable";
        try {
            Statement s = conn.createStatement();
            ResultSet rs = s.executeQuery(sql);

            while (rs.next()) {
                birthday.add(rs.getString(1).trim());
                birthday.add(rs.getString(2).trim());
            }
            s.close();
            conn.close();
        } catch (Exception ex) {
            System.out.println("updateUserPassword:" + ex.getMessage());
        }
        return birthday;
    }

    public ArrayList selectType() {
        ArrayList arr = new ArrayList();
        Connection conn = new dbconn().getconnection();
        String sql = "select tpid,tpname from typetable";
        try {
            Statement s = conn.createStatement();
            ResultSet rs = s.executeQuery(sql);
            while (rs.next()) {
                typemodel tm = new typemodel();
                tm.setTyid(rs.getInt(1));
                tm.setTyname(rs.getString(2));
                arr.add(tm);
            }
            s.close();
            conn.close();
        } catch (Exception ex) {
            System.out.println("updateUserPassword:" + ex.getMessage());
        }
        return arr;
    }
}