22,209
社区成员
发帖
与我相关
我的任务
分享
try{
con.setAutoCommit(false);
// 标记区间的开始部分
String begin;
//标记区间的结束部分
String end;
// 定义一个用于删除,并添加区间的 st
st=con.createStatement();
String sql2="";
String sql3="";
FanTime ft=new FanTime();
//查询出区间的开头结尾的Lang形式
String selbegin;
String selend;
//原来区间截取后的数组
ArrayList<String> al=new ArrayList<String>();
for(int i=0;i<cit.getInterval().size();i++){
begin= cit.getInterval().get(i).substring(0, 12);
end=cit.getInterval().get(i).substring(14, 26);
sql="select top 1 * from DealerProductTemp d where convert(bigint,'"+end+"')>=(convert(bigint,rtrim(substring(d.ProtectIDarea,0,13)))) and convert(bigint,'"+begin+"')<=(convert(bigint,rtrim(substring(d.ProtectIDarea,15,30)))) and state='A' ";
ps=con.createStatement();
rs=ps.executeQuery(sql);
while(rs.next()){
dt.setAddress(rs.getString("address"));
dt.setChanger(rs.getString("changer"));
dt.setChangeTime(rs.getString("changeTime"));
dt.setCityID(rs.getString("cityID"));
dt.setCreateTime(rs.getString("createTime"));
dt.setDealerID(rs.getString("dealerID"));
dt.setDealerName(rs.getString("dealerName"));
dt.setLinkMan(rs.getString("linkMan"));
dt.setMemo(rs.getString("memo"));
dt.setPhone(rs.getString("phone"));
dt.setProductID(rs.getString("productID"));
dt.setProductName(rs.getString("productName"));
dt.setProtectIDarea(rs.getString("protectIDarea"));
dt.setProtectNum(rs.getString("protectNum"));
dt.setProvinceID(rs.getString("provinceID"));
dt.setSellTime(rs.getString("sellTime"));
dt.setState(rs.getString("state"));
//获取区间的开头
sql2="delete DealerProductTemp where protectIDarea='"+dt.getProtectIDarea()+"'";
st.executeUpdate(sql2);
selbegin=dt.getProtectIDarea().substring(0, 12);
selend=dt.getProtectIDarea().substring(14, 26);
//判断区间。进行处理
// if(Long.parseLong(begin)>Long.parseLong(selbegin)){
//
// al.add(selbegin+"--"+this.calculation(begin, "-"));
//// al.add(String.valueOf(Long.parseLong(end)+1)+"--"+String.valueOf(selend));
//
// }
if(Long.parseLong(selbegin)<Long.parseLong(begin)){
al.add(selbegin+"--"+this.calculation(begin, "-"));
}
if(Long.parseLong(selend)>Long.parseLong(end)){
al.add(this.calculation(end, "+")+"--"+selend);
}
}
//循环将之前的非重合部分再存入到表里面
for(int j=0;j<al.size();j++){
StringBuilder sb = new StringBuilder();
sb.append("select ProtectIDarea from DealerProductTemp where ProtectIDarea ='"+al.get(j)+"'");
rs=ps.executeQuery(sb.toString());
if(rs.getRow()==0){
sql3="insert into DealerProductTemp(ProductID,ProtectIDarea,DealerID,ProductName,DealerName,CreateTime,SellTime,State,ProtectNum," +
"ChangeTime,Changer,ProvinceID,CityID,Address,Phone,LinkMan)" +
" values('"+dt.getProductID()+"','"+al.get(j)+"','"+dt.getDealerID()+"','"+dt.getProductName()+"','"+dt.getDealerName()+"','"+dt.getCreateTime()+"','"+dt.getSellTime()+"','"+dt.getState()+"','"+((Long.parseLong(al.get(j).substring(14, 26))-Long.parseLong(al.get(j).substring(0, 12)))/10+1)+"'," +
"'"+dt.getChangeTime()+"','"+dt.getChanger()+"','"+dt.getProvinceID()+"','"+dt.getCityID()+"','"+dt.getAddress()+"','"+dt.getPhone()+"','"+dt.getLinkMan()+"')";
st.executeUpdate(sql3);
}
}
//将新的记录插入到该表
sql="insert into DealerProductTemp(ProductID,ProtectIDarea,DealerID,ProductName,DealerName,CreateTime,SellTime,State,ProtectNum," +
"ChangeTime,Changer,ProvinceID,CityID,Address,Phone,LinkMan)" +
"values('"+dt.getProductID()+"','"+cit.getInterval().get(i)+"','"+db.getDealerID()+"','"+dt.getProductName()+"','"+db.getDealerName()+"','"+ft.nowDate("yyyy-MM-dd")+"','"+dt.getSellTime()+"','"+state+"','"+((Long.parseLong(end)-Long.parseLong(begin))/10+1)+"'," +
"'"+dt.getChangeTime()+"','"+dt.getChanger()+"','"+db.getProvinceID()+"','"+db.getCityID()+"','"+db.getAddress()+"','"+db.getPhone()+"','"+db.getLinkMan()+"')";
st.executeUpdate(sql);
//将新的记录插入到该表
al.clear();
}
con.setAutoCommit(true);