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();
}
}