package com.jb.jubmis.Dao.DaoImpl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.jb.jubmis.Dao.SqliteDao;
import com.jb.jubmis.entity.ContactHistorys;
import com.jb.jubmis.entity.ContactPlans;
import com.jb.jubmis.entity.SalesTask;
import com.jb.jubmis.entity.Scheduals;
import com.jb.jubmis.entity.Tradings;
import com.jb.jubmis.entity.Xskh;

public class SqliteDaoImpl implements SqliteDao {

public List<Xskh> qryGetAll() throws SQLException {
  ResultSet rs = null;
  Connection conn = null;
  Statement stat = null;
  List<Xskh> list = new ArrayList<Xskh>();
  try {
   conn = connOpen();
   stat = conn.createStatement();
   rs = stat.executeQuery(" select * from xskh "); // 查询数据
   while (rs.next()) {
    Xskh xskh = new Xskh();
    xskh.setAguid(rs.getString("aguid"));
    xskh.setPguid(rs.getString("pguid"));
    xskh.setStatus(rs.getInt("status"));
    xskh.setPxxh(rs.getInt("pxxh"));
    xskh.setRemark(rs.getString("remark"));
    // xskh.setLastsqlupd(rs.getString("lastupdate"));
    xskh.setAname(rs.getString("aname"));
    xskh.setAnameLong(rs.getString("aname_long"));
    xskh.setTel(rs.getString("tel"));
    xskh.setMobile(rs.getString("mobile"));
    xskh.setEmail(rs.getString("email"));
    xskh.setAddr(rs.getString("addr"));
    xskh.setKfPeo(rs.getString("kf_peo"));
    xskh.setGuidJb(rs.getString("guid_jb"));
    xskh.setGuidKind(rs.getString("guid_kind"));
    xskh.setGuidFrom(rs.getString("guid_from"));
    xskh.setGuidSheng(rs.getString("guid_sheng"));
    xskh.setGuidShi(rs.getString("guid_shi"));
    xskh.setDateCreate(rs.getString("date_create"));
    xskh.setDateLastupdate(rs.getString("date_lastupd"));
    xskh.setDateLastcheck(rs.getString("date_lastcontact"));
    xskh.setDateLastin(rs.getString("date_lastin"));
    xskh.setGuidYw(rs.getString("guid_yw"));
    xskh.setKfPeoBirthday(rs.getString("kf_peo_birthday"));
    xskh.setZjm(rs.getString("zjm"));

list.add(xskh);

}
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   rs.close();
   stat.close();
   conn.close();

}
  return list;
 }

public void saveInfo(Xskh xskh) throws SQLException {
  Connection conn = null;
  PreparedStatement stat = null;
  try {
   // String sql=" insert into xskh" +
   // "(aguid,pguid,pxxh,status,guid_yw,guid_Lastupd,guid_Jb,guid_Kind,guid_from,guid_brand,guid_sheng,guid_shi,guid_xian,guid_fhkind,guid_station,acode,aname,aname_long,tax_no,zjm,tel,mobile,fax,addr,sh_Addr,postcode,www,email,qq,kf_peo,kf_peo_bm,kf_peo_zhiwei,kf_peo_birthday,kf_peo_tel,wl_times,thday,date_create,date_lastupdate,date_lastin,date_lastout,kf_peo2,kf_tel2,kf_peo3,kf_tel3,zk_kind,zk_fix,zk_min,thratio,thratio_calc,remark,likeme,jycontent,payment,date_lastcheck,sy_year,mainsale_kcsp,date_begindeal,photo1,photo2,xsrl,xsyc,jy_area,company_pw,date_lastgj,lastsqlupd)"
   // +
   // " values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

String sql = " insert into xskh"
     + "(aguid,pguid,status,pxxh,remark,aname,aname_long,tel,mobile,email,addr,kf_peo,guid_jb,guid_kind,guid_from,guid_sheng,guid_shi,date_create,date_lastupd,date_lastcontact,"
     + "date_lastin,guid_yw,kf_peo_birthday,zjm)"
     + " values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
   conn = connOpen();
   stat = conn.prepareStatement(sql);
   stat.setString(1, xskh.getAguid());
   stat.setString(2, xskh.getPguid());
   stat.setInt(3, xskh.getStatus());
   stat.setInt(4, xskh.getPxxh());
   stat.setString(5, xskh.getRemark());
   stat.setString(6, xskh.getAname());
   stat.setString(7, xskh.getAnameLong());
   stat.setString(8, xskh.getTel());
   stat.setString(9, xskh.getMobile());
   stat.setString(10, xskh.getEmail());
   stat.setString(11, xskh.getAddr());
   stat.setString(12, xskh.getKfPeo());
   stat.setString(13, xskh.getGuidJb());
   stat.setString(14, xskh.getGuidKind());
   stat.setString(15, xskh.getGuidFrom());
   stat.setString(16, xskh.getGuidSheng());
   stat.setString(17, xskh.getGuidShi());
   stat.setString(18, xskh.getDateCreate());
   stat.setString(19, xskh.getDateLastupdate());
   stat.setString(20, xskh.getDateLastcheck());
   stat.setString(21, xskh.getDateLastin());
   stat.setString(22, xskh.getGuidYw());
   stat.setString(23, xskh.getKfPeoBirthday());
   stat.setString(24, xskh.getZjm());

stat.executeUpdate();
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   stat.close();
   conn.close();
  }
 }

public void updateInfo(Xskh xskh) throws SQLException {
  Connection conn = null;
  PreparedStatement stat = null;
  try {
   String sql = " update xskh"
     + "(aguid,pguid,status,pxxh,remark,aname,aname_long,tel,mobile,email,addr,kf_peo,guid_jb,guid_kind,guid_from,guid_sheng,guid_shi,date_create,date_lastupd,date_lastcontact,"
     + "date_lastin,guid_yw,kf_peo_birthday,zjm)"
     + " values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
   conn = connOpen();
   stat = conn.prepareStatement(sql);
   stat.setString(1, xskh.getAguid());
   stat.setString(2, xskh.getPguid());
   stat.setInt(3, xskh.getStatus());
   stat.setInt(4, xskh.getPxxh());
   stat.setString(5, xskh.getRemark());
   stat.setString(6, xskh.getAname());
   stat.setString(7, xskh.getAnameLong());
   stat.setString(8, xskh.getTel());
   stat.setString(9, xskh.getMobile());
   stat.setString(10, xskh.getEmail());
   stat.setString(11, xskh.getAddr());
   stat.setString(12, xskh.getKfPeo());
   stat.setString(13, xskh.getGuidJb());
   stat.setString(14, xskh.getGuidKind());
   stat.setString(15, xskh.getGuidFrom());
   stat.setString(16, xskh.getGuidSheng());
   stat.setString(17, xskh.getGuidShi());
   stat.setString(18, xskh.getDateCreate());
   stat.setString(19, xskh.getDateLastupdate());
   stat.setString(20, xskh.getDateLastcheck());
   stat.setString(21, xskh.getDateLastin());
   stat.setString(22, xskh.getGuidYw());
   stat.setString(23, xskh.getKfPeoBirthday());
   stat.setString(24, xskh.getZjm());

stat.executeUpdate();
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   stat.close();
   conn.close();
  }
 }

public void daleteInfo(Xskh xskh) throws SQLException {
  Connection conn = null;
  PreparedStatement stat = null;
  try {
   String sql = "delete from xskh where aguid='" + xskh.getAguid()
     + "'";
   conn = connOpen();
   stat = conn.prepareStatement(sql);
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   stat.close();
   conn.close();
  }
 }

public Connection connOpen() throws SQLException {
  Connection conn = null;
  try {
   Class.forName("org.sqlite.JDBC");//驱动我这里导入的驱动是sqlitejdbc-v056.jar
   conn = DriverManager
     .getConnection("jdbc:sqlite:D:\\eclipse\\project\\jee\\workspace\\jubmis\\WebRoot\\WEB-INF\\ebusiness.db");//db文件
  } catch (ClassNotFoundException e) {
   e.printStackTrace();
  }
  return conn;
 }

public Integer qryMaxPxxh() throws SQLException {
  Connection conn = null;
  PreparedStatement stat = null;
  ResultSet rs = null;
  try {
   String sql = "select max(pxxh) as pxxh from xskh ";
   conn = connOpen();
   stat = conn.prepareStatement(sql);
   rs = stat.executeQuery();
   if (rs.next()) {
    Integer pxxh = rs.getInt("pxxh");
    if (pxxh == null) {
     return 1;
    }
    return pxxh + 1;
   } else {
    return 1;
   }
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   rs.close();
   stat.close();
   conn.close();
  }
  return 1;
 }

public void saveInfo(ContactHistorys contactHistorys) throws SQLException {
  Connection conn = null;
  PreparedStatement stat = null;
  try {
   String sql = " insert into xskh_touch "
     + "(aguid,pguid,status,pxxh,remark,lastupdate,guid_kind,guid_style,guid_kh,guid_work,date_work)"
     + "values(?,?,?,?,?,?,?,?,?,?,?)";
   conn = connOpen();
   stat = conn.prepareStatement(sql);
   stat.setString(1, contactHistorys.getId());
   stat.setString(2, contactHistorys.getPid());
   stat.setInt(3, contactHistorys.getStatus());
   stat.setInt(4, contactHistorys.getSerialNum());
   stat.setString(5, contactHistorys.getRemark());
   stat.setString(6, contactHistorys.getTheLastOfUpdate());
   stat.setString(7, contactHistorys.getContactType());
   stat.setString(8, contactHistorys.getContactWay());
   stat.setString(9, contactHistorys.getCustomer());
   stat.setString(10, contactHistorys.getUser());
   stat.setString(11, contactHistorys.getTimeOfContact());

stat.executeUpdate();
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   stat.close();
   conn.close();
  }
 }

public void saveInfo(ContactPlans contactPlans) throws SQLException {
  Connection conn = null;
  PreparedStatement stat = null;
  try {
   String sql = " insert into xskh_waring "
     + "(aguid,pguid,status,pxxh,remark,lastupdate,date_work,date_waring,guid_user,guid_kh)"
     + "values(?,?,?,?,?,?,?,?,?,?)";
   conn = connOpen();
   stat = conn.prepareStatement(sql);
   stat.setString(1, contactPlans.getId());
   stat.setString(2, contactPlans.getPid());
   stat.setInt(3, contactPlans.getStatus());
   stat.setInt(4, contactPlans.getSerialNum());
   stat.setString(5, contactPlans.getRemark());
   stat.setString(6, contactPlans.getTheLastOfUpdate());

stat.setString(7, contactPlans.getDateCreate());
   stat.setString(8, contactPlans.getDateWarning());
   stat.setString(9, contactPlans.getUser());
   stat.setString(10, contactPlans.getCustomer());

stat.executeUpdate();
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   stat.close();
   conn.close();
  }
 }

public void saveInfo(SalesTask salesTask) throws SQLException {
  Connection conn = null;
  PreparedStatement stat = null;
  try {
   String sql = " insert into xskh_task "
     + "(aguid,pguid,status,pxxh,remark,lastupdate,sy_goal,sy_task,date_yw,date_lastupd)"
     + "values(?,?,?,?,?,?,?,?,?,?)";
   conn = connOpen();
   stat = conn.prepareStatement(sql);
   stat.setString(1, salesTask.getId());
   stat.setString(2, salesTask.getPid());
   stat.setInt(3, salesTask.getStatus());
   stat.setInt(4, salesTask.getSerialNum());
   stat.setString(5, salesTask.getRemark());
   stat.setString(6, salesTask.getTheLastOfUpdate());

stat.setDouble(7, salesTask.getGoal());
   stat.setInt(8, salesTask.getContact());
   stat.setString(9, salesTask.getTaskMonth());
   stat.setString(10, salesTask.getDateOfLastUpdate());

stat.executeUpdate();
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   stat.close();
   conn.close();
  }
 }

public void saveInfo(Scheduals scheduals) throws SQLException {
  Connection conn = null;
  PreparedStatement stat = null;
  try {
   String sql = " insert into msg_m "
     + "(aguid,pguid,status,pxxh,remark,lastupdate,aname,date_send,date_begin,guid_sender,dllformclass)"
     + "values(?,?,?,?,?,?,?,?,?,?,?)";
   conn = connOpen();
   stat = conn.prepareStatement(sql);
   stat.setString(1, scheduals.getId());
   stat.setString(2, scheduals.getPid());
   stat.setInt(3, scheduals.getStatus());
   stat.setInt(4, scheduals.getSerialNum());
   stat.setString(5, scheduals.getRemark());
   stat.setString(6, scheduals.getTheLastOfUpdate());

stat.setString(7, scheduals.getTopic());
   stat.setString(8, scheduals.getDateCreate());
   stat.setString(9, scheduals.getDateWarning());
   stat.setString(10, scheduals.getUser());
   stat.setString(11, scheduals.getResult());

stat.executeUpdate();
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   stat.close();
   conn.close();
  }
 }

public void saveInfo(Tradings tradings) throws SQLException {
  Connection conn = null;
  PreparedStatement stat = null;
  try {
   String sql = " insert into xsbilldd_m "
     + "(aguid,pguid,status,pxxh,remark,lastupdate,guid_kh,guid_yw,qty,sy_fee,bj,date_yw)"
     + "values(?,?,?,?,?,?,?,?,?,?,?,?)";
   conn = connOpen();
   stat = conn.prepareStatement(sql);
   stat.setString(1, tradings.getId());
   stat.setString(2, tradings.getPid());
   stat.setInt(3, tradings.getStatus());
   stat.setInt(4, tradings.getSerialNum());
   stat.setString(5, tradings.getRemark());
   stat.setString(6, tradings.getTheLastOfUpdate());

stat.setString(7, tradings.getGuid_kh());
   stat.setString(8, tradings.getUser());
   stat.setDouble(9, tradings.getNumber());
   stat.setDouble(10, tradings.getCost());
   stat.setDouble(11, tradings.getPrice());
   stat.setString(12, tradings.getDate());

stat.executeUpdate();
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   stat.close();
   conn.close();
  }
 }