package family.yin.senqi.dao;

//import java.util.HashMap;
import java.sql.*;
import family.yin.senqi.model.*;
//import java.text.SimpleDateFormat;

/**
 * 
 * @author user
 *
 */
public class MyDAO{
  private Connection con = null;
  private Statement st = null;
  
  /**
   * 構造函數(shù)，初始化con（類中定義的私有變量）
   *
   */
  public MyDAO() {
    try {
      Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
    }
    catch (ClassNotFoundException ex) {
      ex.printStackTrace();
    }
    try {
      con = DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=myFamaly","sa","sa");
    }
    catch (SQLException ex1) {
      ex1.printStackTrace();
    }
  }

  /**
   * 
   * @param tables
   * @param indexid
   * @return
   * @throws SQLException
   */
  public ModelOne[] getMyTableResults(int tables,String indexid) throws SQLException {
    st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

    ResultSet rs = st.executeQuery("select * from mytable where indexid = '"+indexid+"' and tables="+tables);
    rs.last();
    int row = rs.getRow();
    ModelOne[] ms =  new ModelOne[row];
    rs.beforeFirst();
    int i = 0;
    while (rs.next()) {
      ms[i] = new ModelOne();
      ms[i].setId(rs.getString(1));
      ms[i].setIndexid(rs.getString(2));
      ms[i].setType(rs.getInt(3));
      ms[i].setPerson(rs.getInt(4));
      ms[i].setMoney(rs.getFloat(5));
      ms[i].setName(rs.getString(6));
      ms[i].setTables(rs.getInt(7));
      ++i;
    }
    rs.close();
    st.close();
    return ms;
  }


  public ModelFour[] getBalance(int nYear,int nMonth) throws SQLException {
    st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
    String sql = "select a.rectime,SUM(b.money) as money from family_rectime a,mytable b"
        +" where a.id=b.indexid and Year(a.rectime)="+nYear+" and Month(a.rectime)="+nMonth
        +" and not b.tables=5 group by a.rectime order by DAY(a.rectime)";
    ResultSet rs = st.executeQuery(sql);
    rs.last();
    int row = rs.getRow();
    ModelFour[] mf =  new ModelFour[row];

    rs.beforeFirst();
    int i = 0;
    while (rs.next()) {
      mf[i] = new ModelFour();
      mf[i].setRecTime(rs.getString(1));
      mf[i].setTotalMoney(rs.getFloat(2));
      ++i;
    }
    rs.close();
    st.close();
    return mf;
  }

  public ModelTwo getRecTimeResults(String sql) throws SQLException {
    ModelTwo mt = new ModelTwo();
    st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
    ResultSet rs = st.executeQuery(sql);
    int row = 0;
    if(rs.last()){
      row = rs.getRow();
    }
    rs.beforeFirst();
    int i = 0;
    while (rs.next()) {
      mt.setId(rs.getString(1));
      mt.setRecTime(rs.getString(2));
      i++;
    }
    rs.close();
    st.close();
    return mt;
  }

  public void insertInto(String sql) throws SQLException {
    st = con.createStatement();
    st.executeUpdate(sql);
    st.close();
  }

  public ModelTwo TestResult(String sql) throws SQLException {
    st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
    ResultSet rs = st.executeQuery(sql);
    rs.last();
    int row = rs.getRow();

    String[] test = new String[row];
    rs.beforeFirst();
    int i=0;
    ModelTwo mt = new ModelTwo();
    while(rs.next()){
      String r = rs.getString(1);
      mt.setId(r);
      r = rs.getString(2);
      mt.setRecTime(r);
      i++;
    }
    rs.close();
    st.close();
    return mt;
  }

  public void insertRecTime(String recTime){
    try {
      String id = this.getSid(1);
      String sql_insert = "insert into family_rectime (id,rectime) values"
          +" ('"+id+"','"+recTime+"')";
        this.insertInto(sql_insert);
    }
    catch (SQLException ex) {
      ex.printStackTrace();
    }
  }

  public String getSid(int i) throws SQLException {
    String sql = "select  MAX(CAST(ID AS int))  as id,'' as rectime from family_rectime";
    if(i!=1)sql = "select  MAX(CAST(ID AS int))  as id,'' as rectime from mytable";
    ModelTwo mt = this.getRecTimeResults(sql);
    String id = String.valueOf(new Integer(mt.getId()).intValue() + 1);
    return id;
  }

  public String getIndexid(String recTime) throws SQLException {
    String sql = "select * from family_rectime where rectime='"+recTime+"'";
    ModelTwo mt = this.getRecTimeResults(sql);
    if(mt!=null)
    return mt.getId();
  else
    return null;
  }

  public void insertToMytable(ModelOne model) throws SQLException {
    String sql = "insert into mytable (id,indexid,type,person,money,name,tables) values"
       + " ('"+model.getId()+"','"+model.getIndexid()+"',"+model.getType()
       +","+model.getPerson()+","+model.getMoney()+",'"+model.getName()+"',"+model.getTables()+")";

   this.insertInto(sql);
 }

 public void deleteFromMytable(String recId) throws SQLException {
   String sql = "delete mytable where id='"+recId+"'";
   this.insertInto(sql);
 }

 public ModelFour[] getEverydayMoney(String indexid) throws SQLException {
   st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
   String sql = "SELECT SUM(MONEY) AS total, tables FROM MYTABLE WHERE INDEXID="+ indexid + " AND NOT TABLES=5 GROUP BY tables";
   ResultSet rs = st.executeQuery(sql);
   rs.last();
   int row = rs.getRow();
   ModelFour[] mf =  new ModelFour[row];

   rs.beforeFirst();
   int i = 0;
   while (rs.next()) {
     mf[i] = new ModelFour();
     mf[i].setTotalMoney(rs.getFloat(1));
     mf[i].setType(rs.getInt(2));
     ++i;
   }
   rs.close();
   st.close();
   return mf;
 }

 public ModelFour[] getMoneyInTotal() throws SQLException {
   st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
   String sql = "SELECT a.MONEY, b.RECTIME FROM MYTABLE a,FAMILY_RECTIME b WHERE a.indexid=b.id and a.tables=5";
   ResultSet rs = st.executeQuery(sql);
   rs.last();
   int row = rs.getRow();
   ModelFour[] mf =  new ModelFour[row];

   rs.beforeFirst();
   int i = 0;
   while (rs.next()) {
     mf[i] = new ModelFour();
     mf[i].setTotalMoney(rs.getFloat(1));
     mf[i].setRecTime(rs.getString(2));
     ++i;
   }
   rs.close();
   st.close();
   return mf;
 }

 public float getMonthTotalMoney(int nMonth) throws SQLException {
   float result = 0;
   st = con.createStatement();
   String sql = "SELECT SUM(a.MONEY) as money FROM MYTABLE a,FAMILY_RECTIME b WHERE a.indexid=b.id and a.tables=5 and MONTH(b.rectime)="+nMonth;
   ResultSet rs = st.executeQuery(sql);
   if(rs.next()) {
     result = rs.getFloat(1);
   }
   rs.close();
   st.close();
   return result;
 }
}