package Library.lib;


import Library.pub.*;
import java.sql.*;
import java.util.*;

public class LibManager extends PubDb{

    Statement stmt;
    ResultSet rs;
    private String sql;

    public LibManager() {
        stmt = null;
        rs = null;
    }

    public Hashtable Sel_Detail(Hashtable list){
        Hashtable hs = new Hashtable();
        sql = "select * from BookDetail a,Book b where a.bkCode=b.bkCode";
        if (list.containsKey("bkIndex")) {
          sql += " and a.bkIndex="+list.get("bkIndex").toString();
        }
        System.out.println("sql:" + sql);
        hs=super.execSelSingle(sql);
        return hs;
    }

    public Collection execSel(Hashtable lists) throws SQLException {
    int count=-1;
    int stat = 0;
    Collection col = null;
    Vector vVector = new Vector();
    try {
        sql="select count(*) from BookDetail a,Book b where a.bkCode=b.bkCode";
        if (lists.containsKey("bkIndex")) {
          sql += " and a.bkIndex="+lists.get("bkIndex").toString();
        }
        if (lists.containsKey("bkCode")) {
          sql += " and a.bkCode="+lists.get("bkCode").toString();
        }

        count=super.getRecordcount(sql);
        System.out.println("count:" + count);
        stat=setConnection();

        sql="select * from BookDetail a,Book b where a.bkCode=b.bkCode";
        if (lists.containsKey("bkIndex")) {
          sql += " and a.bkIndex="+lists.get("bkIndex").toString();
        }
        if (lists.containsKey("bkCode")) {
          sql += " and b.bkCode like "+"'"+lists.get("bkCode").toString()+"%'";
        }
        if (lists.containsKey("bkName")) {
          sql += " and b.bkName like "+"'%"+lists.get("bkName").toString()+"%'";
        }
        if (lists.containsKey("bkAuthor")) {
          sql += " and b.bkAuthor like "+"'%"+lists.get("bkAuthor").toString()+"%'";
        }
        if (lists.containsKey("bkPress")) {
          sql += " and b.bkPress like "+"'%"+lists.get("bkPress").toString()+"%'";
        }
        if (lists.containsKey("bkDatePress")) {
          sql += " and b.bkDatePress="+lists.get("bkDatePress").toString();
        }
        if (lists.containsKey("bkDateIn")) {
          sql += " and b.bkDateIn="+lists.get("bkDateIn").toString();
        }
        if (lists.containsKey("bkNum")) {
          sql += " and b.bkNum="+lists.get("bkNum").toString();
        }


        System.out.println("sql1:" + sql);
        if(stat==1){
        stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                            ResultSet.CONCUR_READ_ONLY);
        rs = stmt.executeQuery(sql);
        ResultSetMetaData r = rs.getMetaData();
        String vFieldName = "";
        String vFieldValue = "";

        //for (int j = 1; j <= count; j++) {
        while(rs.next()){

                        Hashtable vTmp = new Hashtable();
                        //循環(huán)把某條記錄的所有字段及其值存入 hashtable 中

                        for (int i = 1; i <= r.getColumnCount(); i++) {
                            //讀取字段名
                            vFieldName = r.getColumnName(i);

                            //讀取字段名相應(yīng)值
                            vFieldValue = rs.getString(i);

                            if (vFieldValue == null)
                                vFieldValue = "";

                            vTmp.put(vFieldName, vFieldValue);

                        }
                        vVector.add(vTmp);

                        //if (!rs.next())
                        //    break;
                    }
        }

    }catch (Exception e) {
        e.printStackTrace();
        //System.out.println("connect database be defeated!");
    }finally {

            try {
                rs = null;
                stmt = null;

                releaseConnection();
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }

    col = vVector;
    return col;

    }


    public int addRec(Hashtable list) {
        int st = 1;
        int st1 = 1;
        int st2 = 1;
        int st3 = 1;
        int id = 0;
        //String sql1 = "select max(bkIndex) from BookDetail";
        //System.out.println("sql1:" + sql1);
        Pub pub = new Pub();
        id = pub.getMax_lib();
        System.out.println("id:" + id);
        String sql2_bt =
                "insert into BookDetail (bkIndex,bkCode) VALUES(" +
                id + ",'" + list.get("bkCode").toString() + "')";

        System.out.println("sql2_bt:" + sql2_bt);
        Calendar cal=Calendar.getInstance();

        String sql3 =
            "insert into Book (bkCode,bkDateIn) values ('" +
               list.get("bkCode").toString() + "','"+cal.get(Calendar.YEAR)+"-"+(cal.get(Calendar.MONTH)+1)+"-"+cal.get(Calendar.DATE)+"')";

        System.out.println("sql3:" + sql3);

        st1 = super.execUpd(sql2_bt);
        st2 = super.execUpd(sql3);

        String sql4 = "update Book set";
            if (list.containsKey("bkLanguage")) {
                sql4 += " bkLanguage='"+list.get("bkLanguage").toString()+"'";
            }
            if (list.containsKey("bkName")) {
                sql4 += ", bkName='"+list.get("bkName").toString()+"'";
            }
            if (list.containsKey("bkAuthor")) {
                sql4 += ", bkAuthor='"+list.get("bkAuthor").toString()+"'";
            }
            if (list.containsKey("bkPress")) {
                sql4 += ", bkPress='"+list.get("bkPress").toString()+"'";
            }
            if (list.containsKey("bkISBN")) {
                sql4 += ", bkISBN='"+list.get("bkISBN").toString()+"'";
            }
            if (list.containsKey("bkDatePress")) {
                sql4 += ", bkDatePress='"+list.get("bkDatePress").toString()+"'";
            }
            if (list.containsKey("bkPages")) {
                sql4 += ", bkPages='"+list.get("bkPages").toString()+"'";
            }
            if (list.containsKey("bkPrice")) {
                sql4 += ", bkPrice='"+list.get("bkPrice").toString()+"'";
            }
            if (list.containsKey("bkCover")) {
                sql4 += ", bkCover='"+list.get("bkCover").toString()+"'";
            }
            if (list.containsKey("bkBrief")) {
                sql4 += ", bkBrief='"+list.get("bkBrief").toString()+"'";
            }
            if (list.containsKey("bkNum")) {
                sql4 += ", bkNum='"+list.get("bkNum").toString()+"'";
            }
            sql4 +=" where bkCode='" + list.get("bkCode").toString() + "'";
            System.out.println("sql4:" + sql4);

            st2 = super.execUpd(sql4);

           /*
            ,bkLanguage,bkName,bkAuthor,bkPress,bkISBN,bkDatePress,bkPages,bkPrice,bkCover,bkBrief,bkNum) VALUES('" +
               list.get("bkCode").toString() + "','" +
               list.get("bkLanguage").toString() + "','" +
               list.get("bkName").toString() + "','" +
               list.get("bkAuthor").toString() + "','" +
               list.get("bkPress").toString() + "','" +
               list.get("bkISBN").toString() + "','" +
               list.get("bkDatePress").toString() + "','" +
               list.get("bkPages").toString() + "','" +
               list.get("bkPrice").toString() + "','" +
               list.get("bkCover").toString() + "','" +
               list.get("bkBrief").toString() + "','" +
               list.get("bkNum").toString() + "')";
*/




        if(st1==1&&st2==1&&st3==1)
            st=1;
        else st=0;
        return st;
    }


    public int setRec(String bkCode, Hashtable list) {
        int st = 0;
         /*
        String sql = "update Book SET bkLanguage='" +
                     list.get("bkLanguage").toString() + "',bkName='" +
                     list.get("bkName").toString() + "', bkAuthor='" +
                     list.get("bkAuthor").toString() + "', bkPress='" +
                     list.get("bkPress").toString() + "', bkISBN='" +
                     list.get("bkISBN").toString() + "', bkPages='" +
                     list.get("bkPages").toString() + "', bkCover='" +
                     list.get("bkCover").toString() + "', bkPrice='" +
                     list.get("bkPrice").toString() + "', bkBrief='" +
                     list.get("bkBrief").toString() + "' where bkCode='" +
                     bkCode + "'";
         */

        String sql = "update Book SET";
        if (list.containsKey("bkLanguage")) {
          sql += " bkLanguage='"+list.get("bkLanguage").toString()+"'";
        }
        if (list.containsKey("bkName")) {
          sql += " , bkName='"+list.get("bkName").toString()+"'";
        }
        if (list.containsKey("bkAuthor")) {
          sql += " , bkAuthor='"+list.get("bkAuthor").toString()+"'";
        }
        if (list.containsKey("bkPress")) {
          sql += " , bkPress='"+list.get("bkPress").toString()+"'";
        }
        if (list.containsKey("bkISBN")) {
          sql += " , bkISBN='"+list.get("bkISBN").toString()+"'";
        }
        if (list.containsKey("bkDatePress")) {
          sql += " , bkDatePress='"+list.get("bkDatePress").toString()+"'";
        }
        if (list.containsKey("bkPages")) {
          sql += " , bkPages='"+list.get("bkPages").toString()+"'";
        }
        if (list.containsKey("bkCover")) {
          sql += " , bkCover='"+list.get("bkCover").toString()+"'";
        }
        if (list.containsKey("bkPrice")) {
          sql += " , bkPrice='"+list.get("bkPrice").toString()+"'";
        }
        if (list.containsKey("bkBrief")) {
          sql += " , bkBrief='"+list.get("bkBrief").toString()+"'";
        }

        sql +=" where bkCode='" + bkCode + "'";

        st = super.execUpd(sql);
        return st;
    }

    public void delRec(Hashtable list) {
        String sql1 = " ";
        Hashtable hast = new Hashtable();
        String sql = "select bkCode from BookDetail where bkIndex = '"+list.get("bkIndex")+"'";
        hast = super.execSelSingle(sql);

        sql = "delete from BookDetail where bkIndex = '"+list.get("bkIndex")+"'";
        sql1 = "delete from Book where bkCode = '"+hast.get("bkCode")+"'";
        super.execUpd(sql);
        super.execUpd(sql1);
    }

}