java批量导入excel到Mysql数据库

package com.google.i_sales.service.data;import java.io.File;import java.io.FileInputStream;import java.io.IOException;import java.io.InputStream;import java.util.ArrayList;import java.util.Date;import java.util.HashSet;import java.util.List;import java.util.Locale;import java.util.Map;import java.util.Set;import jxl.Cell;import jxl.Sheet;import jxl.Workbook;import jxl.WorkbookSettings;import jxl.read.biff.BiffException;import org.extremecomponents.table.limit.Limit;import org.hibernate.Criteria;import org.hibernate.JDBCException;import org.hibernate.criterion.CriteriaSpecification;import org.hibernate.criterion.Order;import org.hibernate.criterion.Projection;import org.hibernate.criterion.Projections;import org.hibernate.criterion.Restrictions;import org.hibernate.impl.CriteriaImpl;import org.springframework.util.Assert;import org.springside.core.dao.HibernateEntityDao;import org.springside.core.dao.support.Page;import org.springside.core.utils.BeanUtils;import com.google.i_sales.components.acegi.domain.User;import com.google.i_sales.domain.Business;import com.google.i_sales.domain.Dcompany;import com.google.i_sales.domain.MsalesCompany;import com.google.i_sales.domain.Reject;import com.google.i_sales.domain.SalesCompany;import com.google.i_sales.domain.Service;import com.google.i_sales.domain.TelephoneCompany;import com.google.i_sales.domain.TradeType;import com.google.i_sales.domain.UnitInfo;import com.google.i_sales.domain.Vcompany;import com.google.i_sales.service.common.BusinessManager;import com.google.i_sales.service.common.RejectManager;import com.google.i_sales.service.common.ServiceManager;import com.google.i_sales.service.common.TradeTypeManager;import com.google.i_sales.service.msales.MsalesCompanyManager;import com.google.i_sales.service.sales.DcompanyManager;import com.google.i_sales.service.sales.SalesCompanyManager;import com.google.i_sales.service.security.UserManager;import com.google.i_sales.service.telephone.TelephoneCompanyManager;/** * @author wxl2012 */public class VcompanyManager extends HibernateEntityDao<Vcompany> {Integer[] states = {new Integer(0),new Integer(2),new Integer(5),new Integer(6)};private ServiceManager serviceManager; public void setServiceManager(ServiceManager serviceManager) {this.serviceManager = serviceManager;}public Vcompany getVcompany(Integer id) {return get(Vcompany.class, id);}/** * 批量倒入客户信息 */public List<String> saveImportCompany(File file,BusinessManager businessManager, User user,TradeTypeManager tradeTypeManager, RejectManager rejectManager,ServiceManager serviceManager) {List<String> log = new ArrayList<String>();int count = 0;if (!file.exists()) {System.err.println("Directory or file not exists.");log.add("系统错误");return log;}if (user == null) {log.add("用户超时，请重新登录");return log;}try {//fis is the input fileInputStream fis = new FileInputStream(file);WorkbookSettings ws = new WorkbookSettings();ws.setLocale(Locale.SIMPLIFIED_CHINESE);//get the excel workbook Workbook rwb = Workbook.getWorkbook(fis, ws);//return how many sheets the workbook have Sheet[] sheets = rwb.getSheets(); for (int i = 0; i < sheets.length; ++i) {System.out.println("number" + sheets[i].getRows());//operate every row in the sheetfor (int row = 1; row < sheets[i].getRows(); ++row) {//get all the cells of a certain columnCell[] cells = sheets[i].getRow(row);Vcompany vcompany = new Vcompany();String company_name = "";String company_fax = "";String company_add = "";//地区String region = "";String company_website = "";String company_tel="";Integer i_tradetype = 1;Integer i_business =1;Integer i_service = 4;String company_email = "";String company_mainfunction = "";String company_remarks = "";String company_person1 = "";String company_person1_tel = "";String company_person1_title = "";String company_person2 = "";String company_person2_tel = "";String company_person2_title = "";String company_person3 = "";String company_person3_tel = "";String company_person3_title = "";String company_source = "";//exception caused by invalidate company nametry {//if company name is nullcompany_name = cells[0].getContents();System.out.println(count+":"+company_name);if (company_name.equals("") || company_name == null) {log.add("工作表" + i + " 第" + (row + 1)+ "行：没有公司名称,批量导入停止");break;}//if company name does existint i_com_name = 0;try{ i_com_name = findBy("comName", company_name).size();}catch(Exception e){System.out.println("query com name fail");e.printStackTrace();}if (i_com_name != 0) {log.add("工作表" + i + " 第" + (row + 1) + "行：公司名称"+ company_name + "已存在");continue;} else {if (company_name.length() > 40) {log.add("工作表" + i + " 第" + (row + 1)+ "行：公司名称超长");continue;} else {vcompany.setComName(company_name);}}} catch (java.lang.ArrayIndexOutOfBoundsException e) {log.add("工作表" + i + " 第" + (row + 1)+ "行：没有公司名称,批量导入停止");break;} //exception caused by invalidate trade typetry {i_tradetype = Integer.valueOf(cells[1].getContents());if (i_tradetype == null) {log.add("工作表" + i + " 第" + (row + 1)+ "行：企业性质不能为空");continue;} else if (i_tradetype > 6 || i_tradetype < 1) {log.add("工作表" + i + " 第" + (row + 1)+ "行：企业性质代号错误");continue;} else {TradeType tempTradeType = tradeTypeManager.getTradeType(i_tradetype);vcompany.setTradetype(tempTradeType);}} catch (NumberFormatException e) {// e.printStackTrace();log.add("工作表" + i + " 第" + (row + 1) + "行：企业性质格式不对");continue;}try {i_business = Integer.valueOf(cells[2].getContents());// System.out.println("工作表" + i + " 第" + (row + 1)// + "行：i_business" + i_business);if (i_business == null) {log.add("工作表" + i + " 第" + (row + 1) + "行：行业不能为空");continue;} else if (i_business > 23 || i_business < 1) {log.add("工作表" + i + " 第" + (row + 1) + "行：行业代号错误");continue;} else {Business tempBusiness = businessManager.getBusiness(i_business);vcompany.setBusiness(tempBusiness);}} catch (NumberFormatException e) {// e.printStackTrace();log.add("工作表" + i + " 第" + (row + 1) + "行：行业格式不对");continue;} catch (java.lang.ArrayIndexOutOfBoundsException e) {}try {region = cells[3].getContents();if (region != null) {if (region.length() <= 20) {vcompany.setRegion(region);} else {log.add("工作表" + i + " 第" + (row + 1)+ "行：区域名称超长");continue;}}} catch (java.lang.ArrayIndexOutOfBoundsException e) {}try{company_tel = cells[4].getContents();if (company_tel != null) {if (company_tel.length() <= 20) {vcompany.setComTel(company_tel);} else {log.add("工作表" + i + " 第" + (row + 1) + "行：公司电话超长");continue;}} else {log.add("工作表" + i + " 第" + (row + 1) + "行：公司电话不能为空");continue;}} catch (java.lang.ArrayIndexOutOfBoundsException e) {}try {company_fax = cells[5].getContents();// System.out.println("工作表" + i + " 第" + (row + 1)// + "行：company_fax" + company_fax);if (company_fax != null) {if (company_fax.length() <= 20) {vcompany.setComFax(company_fax);} else {log.add("工作表" + i + " 第" + (row + 1)+ "行：公司传真超长");continue;}}} catch (java.lang.ArrayIndexOutOfBoundsException e) {}try {company_add = cells[6].getContents();if (company_add != null) {if (company_add.length() <= 255) {vcompany.setComAddress(company_add);} else {log.add("工作表" + i + " 第" + (row + 1)+ "行：公司地址超长");continue;}}} catch (java.lang.ArrayIndexOutOfBoundsException e) {}try {company_website = cells[7].getContents();if (company_website != null) {if (company_website.length() <= 255) {vcompany.setComWebsite(company_website);} else {log.add("工作表" + i + " 第" + (row + 1)+ "行：公司网址超长");continue;}}} catch (java.lang.ArrayIndexOutOfBoundsException e) {}try {company_email = cells[8].getContents();if (company_email != null) {if (company_email.length() <= 255) {vcompany.setComEmail(company_email);} else {log.add("工作表" + i + " 第" + (row + 1)+ "行：公司Email超长");continue;}}} catch (java.lang.ArrayIndexOutOfBoundsException e) {}try {company_mainfunction = cells[9].getContents();if (company_mainfunction != null) {if (company_mainfunction.length() <= 200) {vcompany.setMainfunction(company_mainfunction);} else {log.add("工作表" + i + " 第" + (row + 1)+ "行：公司主营业务超长");continue;}}} catch (java.lang.ArrayIndexOutOfBoundsException e) {}try {company_remarks = cells[10].getContents();// System.out.println("工作表" + i + " 第" + (row + 1)// + "行：company_remarks" + company_remarks);if (company_remarks != null) {if (company_remarks.length() <= 100) {vcompany.setRemarks(company_remarks);} else {log.add("工作表" + i + " 第" + (row + 1)+ "行：备注超长");continue;}}} catch (java.lang.ArrayIndexOutOfBoundsException e) {}vcompany.setState(new Integer(0));try {company_person1 = cells[11].getContents();if (company_person1 != null) {if (company_person1.length() <= 30) {vcompany.setContactPerson1(company_person1);} else {log.add("工作表" + i + " 第" + (row + 1)+ "行：联系人1姓名超长");continue;}}} catch (java.lang.ArrayIndexOutOfBoundsException e) {}try {company_person1_tel = cells[12].getContents();if (company_person1_tel != null) {if (company_person1_tel.length() <= 30) {vcompany.setContactPerson1Phone(company_person1_tel);} else {log.add("工作表" + i + " 第" + (row + 1)+ "行：联系人1电话超长");continue;}}} catch (java.lang.ArrayIndexOutOfBoundsException e) {}try {company_person1_title = cells[13].getContents();if (company_person1_title != null) {if (company_person1_title.length() <= 30) {vcompany.setContactPerson1Title(company_person1_title);} else {log.add("工作表" + i + " 第" + (row + 1)+ "行：联系人1职位超长");continue;}}} catch (java.lang.ArrayIndexOutOfBoundsException e) {}try {company_person2 = cells[14].getContents();if (company_person2 != null) {if (company_person2.length() <= 30) {vcompany.setContactPerson2(company_person2);} else {log.add("工作表" + i + " 第" + (row + 1)+ "行：联系人2姓名超长");continue;}}} catch (java.lang.ArrayIndexOutOfBoundsException e) {}try {company_person2_tel = cells[15].getContents();if (company_person2_tel != null) {if (company_person2_tel.length() <= 30) {vcompany.setContactPerson2Phone(company_person2_tel);} else {log.add("工作表" + i + " 第" + (row + 1)+ "行：联系人2电话超长");continue;}}} catch (java.lang.ArrayIndexOutOfBoundsException e) {}try {company_person2_title = cells[16].getContents();if (company_person2_title != null) {if (company_person2_title.length() <= 30) {vcompany.setContactPerson2Title(company_person2_title);} else {log.add("工作表" + i + " 第" + (row + 1)+ "行：联系人2职位超长");continue;}}} catch (java.lang.ArrayIndexOutOfBoundsException e) {}try {company_person3 = cells[17].getContents();// System.out.println("工作表" + i + " 第" + (row + 1)// + "行：company_person3" + company_person3);if (company_person3 != null) {if (company_person3.length() <= 30) {vcompany.setContactPerson3(company_person3);} else {log.add("工作表" + i + " 第" + (row + 1)+ "行：联系人3姓名超长");continue;}}} catch (java.lang.ArrayIndexOutOfBoundsException e) {}try {company_person3_tel = cells[18].getContents();if (company_person3_tel != null) {if (company_person3_tel.length() <= 30) {vcompany.setContactPerson3Phone(company_person3_tel);} else {log.add("工作表" + i + " 第" + (row + 1)+ "行：联系人3电话超长");continue;}}} catch (java.lang.ArrayIndexOutOfBoundsException e) {}try {company_person3_title = cells[19].getContents();if (company_person3_title != null) {if (company_person3_title.length() <= 30) {vcompany.setContactPerson3Title(company_person3_title);} else {log.add("工作表" + i + " 第" + (row + 1)+ "行：联系人3职位超长");continue;}}} catch (java.lang.ArrayIndexOutOfBoundsException e) {}try {company_source = cells[20].getContents();// System.out.println("工作表" + i + " 第" + (row + 1)// + "行：company_source" + company_source);if (company_source != null) {// System.out.println("company_source.length()"// + company_source.length());if (company_source.length() <= 20) {vcompany.setSource(company_source);} else {log.add("工作表" + i + " 第" + (row + 1)+ "行：公司来源超长");continue;}}} catch (java.lang.ArrayIndexOutOfBoundsException e) {}try {i_service = Integer.valueOf(cells[21].getContents());if (i_service == null) {log.add("工作表" + i + " 第" + (row + 1)+ "行：企业业务形态不能为空,批量导入停止");break;} else if (i_service > 5 || i_service < 1) {log.add("工作表" + i + " 第" + (row + 1)+ "行：企业业务形态代号错误，批量导入停止");break;} else {Service tempService = serviceManager.get(i_service);vcompany.setService(tempService);}} catch (NumberFormatException e) {// e.printStackTrace();log.add("工作表" + i + " 第" + (row + 1) + "行：企业业务形态错误，批量导入停止");break;}vcompany.setUser(user);Date date = new Date();vcompany.setTime(date);super.save(vcompany);++count;}}log.add("成功导入" + count + "条数据");rwb.close();fis.close();} catch (BiffException e) {System.err.println("Open file ERROR! - BiffException");e.printStackTrace();log.add("系统错误");return log;} catch (IOException e) {System.err.println("Open file ERROR! - IOException");e.printStackTrace();log.add("系统错误");return log;} catch (NumberFormatException e) {System.err.println("Open file ERROR! - IOException");e.printStackTrace();log.add("系统错误,但成功导入" + count + "条数据");return log;} catch (java.lang.OutOfMemoryError e) {log.add("系统错误,xls文件出错");return log;} catch (Exception e) {System.err.println("Some Error! - Exception");e.printStackTrace();log.add("发生错误,但成功导入" + count + "条数据");return log;}return log;}/*save information for the stated company * * pjj * */public List<String> saveStateImportCompany(File file,TelephoneCompanyManager telephoneCompanyManager, User user,SalesCompanyManager salesCompanyManager, MsalesCompanyManager msalesCompanyManager,DcompanyManager dcompanyManager,UserManager userManager,RejectManager rejectManager){//initialize the log file System.out.println("begin saveStateImportCompany");List<String> log = new ArrayList<String>();Set<String> states = new HashSet<String>(){{add("0");add("1");add("2"); add("3");add("4");add("5");add("6");add("7"); add("8");add("9");add("10");add("11");}}; Set<String> rejects = new HashSet<String>(){{add("0");add("1");add("2"); add("3");add("4");add("5");add("6");add("7"); add("8");add("9"); add("10");add("11");add("12");add("13");add("14");add("15"); add("16");add("17");add("18");add("19");add("20");add("21");}}; Set<String> scores = new HashSet<String>(){{add("0");add("1");add("3");add("5");}}; int count = 0;if (!file.exists()) {System.err.println("Directory or file not exists.");log.add("系统错误");return log;}if (user == null) {log.add("用户超时，请重新登录");return log;}try{//fis is the input fileInputStream fis = new FileInputStream(file);WorkbookSettings ws = new WorkbookSettings();ws.setLocale(Locale.SIMPLIFIED_CHINESE);//get the excel workbook Workbook rwb = Workbook.getWorkbook(fis, ws);//return how many sheets the workbook have Sheet[] sheets = rwb.getSheets();for (int i = 0; i < sheets.length; ++i) {System.out.println("number" + sheets[i].getRows());//operate every row in the sheetfor (int row = 1; row < sheets[i].getRows(); ++row) {//get all the cells of a certain columnSystem.out.println("sheet"+i+"row"+row);Cell[] cells = sheets[i].getRow(row);String comName = "";String comState = "";String comReject = "1";String comTelPerson = "";String comAplusTime = "";String comSalesPerson = "";String comScore = "";//检查公司名是否合法,由公司名称得到vcompanytry {//if company name is nullcomName = cells[0].getContents();if (comName.equals("") || comName == null) {log.add("工作表" + i + " 第" + (row + 1)+ "行：没有公司名称,批量导入停止");break;}//if company name does not existif (super.findBy("comName", comName).size()== 0) {log.add("工作表" + i + " 第" + (row + 1) + "行：公司："+ comName + "不存在");break;} } catch (java.lang.ArrayIndexOutOfBoundsException e) {log.add("工作表" + i + " 第" + (row + 1)+ "行：没有公司名称,批量导入停止");break;}Vcompany vcompany = null;vcompany = super.findUniqueBy("comName", comName);System.out.println(vcompany.getComName());//检查状态是否合法,得到导入状态try{comState = cells[1].getContents();if(comState == null || comState.equals("")){log.add("工作表" + i + " 第" + (row + 1)+ "行：状态不能为空,批量导入停止");break;} if(!states.contains(comState)){ log.add("工作表" + i + " 第" + (row + 1)+ "行：状态值非法,批量导入停止"); break; }}catch(java.lang.ArrayIndexOutOfBoundsException e){log.add("工作表" + i + " 第" + (row + 1)+ "行：状态值不能为空,批量导入停止"); break;}Integer state = new Integer(comState); System.out.println("import state:"+state);//检查拒绝理由是否合法,得到拒绝理由try{comReject = cells[2].getContents();if (comReject ==""||comReject==null){log.add("工作表" + i + " 第" + (row + 1)+ "行：拒绝理由不能为空,批量导入停止");break;}if(!rejects.contains(comReject)){log.add("工作表" + i + " 第" + (row + 1)+ "行：拒绝理由输入值非法,批量导入停止");break;}}catch(java.lang.ArrayIndexOutOfBoundsException e){e.printStackTrace();log.add("工作表" + i + " 第" + (row + 1)+ "行：拒绝理由不能为空,批量导入停止");break;}Reject reject = rejectManager.getReject(new Integer(comReject));System.out.println("reject:"+reject.getId());//检查电话专员,若输入不为空User telPerson = null;try{comTelPerson = cells[3].getContents();if(comTelPerson!=null && comTelPerson!=""){ if(userManager.findBy("loginid", comTelPerson).size() == 0){ log.add("工作表" + i + " 第" + (row + 1)+ "行：该电话专员不存在,批量导入停止"); break; }}}catch(Exception e){e.printStackTrace();//continue;}if(comTelPerson!=null && comTelPerson!=""){ telPerson = userManager.findUniqueBy("loginid", comTelPerson);}//得到销售专员User salesPerson = null;try{comSalesPerson = cells[4].getContents();System.out.println("get user from excl:"+ comSalesPerson);if(comSalesPerson!=null && comSalesPerson!=""){System.out.println("get user from excl:"+ userManager.findBy("loginid", comSalesPerson).size());if(userManager.findBy("loginid", comSalesPerson).size()== 0){log.add("工作表" + i + " 第" + (row + 1)+ "行：该销售专员不存在,批量导入停止"); break;}}}catch(Exception e){System.out.println("Exception when get salesperson"); e.printStackTrace();}if(comSalesPerson!=null && comSalesPerson!=""){ salesPerson = userManager.findUniqueBy("loginid", comSalesPerson);}//得到返单评分Integer score = null;try{comScore = cells[5].getContents();if(comScore!=null && comScore!=""){if(!scores.contains(comScore)){log.add("工作表" + i + " 第" + (row + 1)+ "行：评分输入值非法,批量导入停止");break;}}}catch(Exception e){//continue;}if(comScore!=null && comScore!=""){score = new Integer(comScore);}//aplus timeDate aplus = null;try{comAplusTime = cells[6].getContents();if(comAplusTime!=null && comAplusTime!=""){System.out.println("get time:"+comAplusTime);char[] ArrayTime = comAplusTime.toCharArray();char[] finalTime = new char[11];finalTime[0] = ArrayTime[6];finalTime[1] = ArrayTime[7];finalTime[2] = ArrayTime[8];finalTime[3] = ArrayTime[9];finalTime[4] = '-';finalTime[5] = ArrayTime[3];finalTime[6] = ArrayTime[4];finalTime[7] = '-';finalTime[8] = ArrayTime[0];finalTime[9] = ArrayTime[1];String gettime = String.copyValueOf(finalTime);;System.out.println("get time:"+gettime);java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd"); aplus = sdf.parse(gettime); System.out.println("prase time:"+aplus);}}catch(Exception e){log.add("工作表" + i + " 第" + (row + 1)+ "行：时间格式错误"); } /************************************************按状态向各个表插入记录*********************************************/TelephoneCompany telephoneCompany = new TelephoneCompany();SalesCompany salesCompany = new SalesCompany();MsalesCompany msalesCompany = new MsalesCompany();Dcompany dcompany = new Dcompany();int duplicate = 0;int k=0;//未分配if(state==0){if (telPerson != null){//若电话专员属于中型部门，则向中型表中插入数据 if(telPerson.getUnitInfo().getUnitType().getId()==5){ //若表中公司已存在且状态和导入状态一样，则为重复数据 duplicate = 0; List<MsalesCompany> msalesconpanys = msalesCompanyManager.findBy("vcompany", vcompany); if(msalesconpanys.size()!=0){ for(k=0;k<msalesconpanys.size();k++){ if(msalesconpanys.get(k).getAddstate()== 0){ duplicate = 1; break; } } if(duplicate == 1){ log.add("工作表" + i + " 第" + (row + 1)+ "行：该公司已存在于该中型部门,批量导入停止"); break; } } msalesCompany.setAddstate(new Integer(0)); msalesCompany.setState(new Integer(0)); msalesCompany.setVcompany(vcompany); msalesCompany.setUser(telPerson); msalesCompany.setUnitInfo(telPerson.getUnitInfo()); msalesCompanyManager.save(msalesCompany); vcompany.setState(new Integer(1)); } else{ log.add("工作表" + i + " 第" + (row + 1)+ "行：电话专员不属于中型部门，该公司置为未分配状态"); }}vcompany.setReject(reject);if(reject.getRejectid()!=1){vcompany.setState(new Integer(2));vcompany.setRejecttimes(new Integer(1));} save(vcompany); count++;}//已分配未打else if(state==1){ if (telPerson != null){ //若电话专员属于中型部门 if(telPerson.getUnitInfo().getUnitType().getId()==5){ //若表中公司已存在且状态和导入状态一样，则为重复数据 duplicate = 0; List<MsalesCompany> msalesconpanys = msalesCompanyManager.findBy("vcompany", vcompany); if(msalesconpanys.size()!=0){ for(k=0;k<msalesconpanys.size();k++){ if(msalesconpanys.get(k).getAddstate()== 0){ duplicate = 1; break; } } if(duplicate == 1){ log.add("工作表" + i + " 第" + (row + 1)+ "行：该公司已存在于该中型部门,批量导入停止"); break; } } msalesCompany.setAddstate(new Integer(0)); msalesCompany.setState(new Integer(2)); msalesCompany.setVcompany(vcompany); msalesCompany.setUser(telPerson); msalesCompany.setUnitInfo(telPerson.getUnitInfo()); msalesCompanyManager.save(msalesCompany); } //否则电话专员属于电话部门 else{ duplicate = 0; List<TelephoneCompany> telcompanys = telephoneCompanyManager.findBy("vcompany", vcompany); if(telcompanys.size()!=0){ /*for(k=0;k<telcompanys.size();k++){ if(telcompanys.get(k).getState()== 0){ duplicate = 1; break; } }*/ duplicate = 1; if(1==duplicate){ log.add("工作表" + i + " 第" + (row + 1)+ "行：该公司已存在于电话部门，批量导入停止"); // msalesCompanyManager.remove(msalesCompany); break; } } telephoneCompany.setUser(telPerson); telephoneCompany.setVcompany(vcompany); telephoneCompany.setState(new Integer(0)); telephoneCompanyManager.save(telephoneCompany); } }else{ log.add("工作表" + i + " 第" + (row + 1)+ "行：在已分配状态电话专员不能为空,批量导入停止");break;} vcompany.setReject(reject); vcompany.setState(new Integer(1));if(reject.getRejectid()!=1){vcompany.setRejecttimes(new Integer(1));} save(vcompany); count++;}//Aelse if(state==2){if (telPerson != null){ if(telPerson.getUnitInfo().getUnitType().getId()==5){ //若表中公司已存在且状态和导入状态一样，则为重复数据 duplicate = 0; List<MsalesCompany> msalesconpanys = msalesCompanyManager.findBy("vcompany", vcompany); if(msalesconpanys.size()!=0){ for(k=0;k<msalesconpanys.size();k++){ if(msalesconpanys.get(k).getAddstate()== 0){ duplicate = 1; break; } } if(duplicate == 1){ log.add("工作表" + i + " 第" + (row + 1)+ "行：该公司已存在于该中型部门,批量导入停止"); break; } } msalesCompany.setAddstate(new Integer(0)); msalesCompany.setState(new Integer(4)); msalesCompany.setVcompany(vcompany); msalesCompany.setUser(telPerson); msalesCompany.setUnitInfo(telPerson.getUnitInfo()); msalesCompanyManager.save(msalesCompany); } else{ duplicate = 0; List<TelephoneCompany> telcompanys = telephoneCompanyManager.findBy("vcompany", vcompany); if(telcompanys.size()!=0){ /*for(k=0;k<telcompanys.size();k++){ if(telcompanys.get(k).getState()== 1){ duplicate = 1; break; } }*/ duplicate = 1; if(1==duplicate){ log.add("工作表" + i + " 第" + (row + 1)+ "行：该公司已存在于电话部门"); // msalesCompanyManager.remove(msalesCompany); break; } } telephoneCompany.setUser(telPerson); telephoneCompany.setVcompany(vcompany); telephoneCompany.setState(new Integer(1)); telephoneCompanyManager.save(telephoneCompany); } }else{ log.add("工作表" + i + " 第" + (row + 1)+ "行：在a状态电话专员不能为空,批量导入停止");break;}vcompany.setReject(reject); vcompany.setState(new Integer(1));if(reject.getRejectid()!=1){vcompany.setRejecttimes(new Integer(1));} save(vcompany); count++;}//A+伪状态，未转移else if(state==3){duplicate = 0; List<TelephoneCompany> telcompanys = telephoneCompanyManager.findBy("vcompany", vcompany); if(telcompanys.size()!=0){ /*for(k=0;k<telcompanys.size();k++){ if(telcompanys.get(k).getState()== 2){ duplicate = 1; break; } }*/ duplicate = 1; if(1==duplicate){ log.add("工作表" + i + " 第" + (row + 1)+ "行：该公司已存在于电话部门，批量导入停止"); break; } }telephoneCompany.setVcompany(vcompany); telephoneCompany.setState(new Integer(2)); if (telPerson != null){ telephoneCompany.setUser(telPerson); }else{ log.add("工作表" + i + " 第" + (row + 1)+ "行：在伪A+未转移状态电话专员不能为空,批量导入停止");break; } telephoneCompanyManager.save(telephoneCompany); vcompany.setReject(reject); vcompany.setState(new Integer(1));if(reject.getRejectid()!=1){vcompany.setRejecttimes(new Integer(1));} save(vcompany); count++;}//A+伪状态，已转移,未分配else if(state==4){ if (salesPerson != null){ if(salesPerson.getUnitInfo().getUnitType().getId()==5){ //若表中公司已存在且状态和导入状态一样，则为重复数据 duplicate = 0; List<MsalesCompany> msalesconpanys = msalesCompanyManager.findBy("vcompany", vcompany); if(msalesconpanys.size()!=0){ for(k=0;k<msalesconpanys.size();k++){ if(msalesconpanys.get(k).getAddstate()== 0){ duplicate = 1; break; } } if(duplicate == 1){ log.add("工作表" + i + " 第" + (row + 1)+ "行：该公司已存在于该中型部门,批量导入停止"); break; } } msalesCompany.setAddstate(new Integer(0)); msalesCompany.setState(new Integer(1)); msalesCompany.setVcompany(vcompany); msalesCompany.setUser(salesPerson); msalesCompany.setUnitInfo(salesPerson.getUnitInfo()); msalesCompanyManager.save(msalesCompany); } else{ duplicate = 0; List<SalesCompany> salescompanys = salesCompanyManager.findBy("vcompany", vcompany); if(salescompanys.size()!=0){ for(k=0;k<salescompanys.size();k++){ if(salescompanys.get(k).getAddState()==0){ duplicate = 1; break; } } if(1==duplicate){ log.add("工作表" + i + " 第" + (row + 1)+ "行：该公司已存在于销售部门"); break; } } salesCompany.setVcompany(vcompany); salesCompany.setAddState(new Integer(0)); salesCompany.setState(new Integer(0)); salesCompany.setUser(salesPerson); salesCompany.setUnitInfo(salesPerson.getUnitInfo()); salesCompanyManager.save(salesCompany); } }else{ log.add("工作表" + i + " 第" + (row + 1)+ "行：在伪A+已转移未分配状态销售专员不能为空,批量导入停止");break; } //若电话专员为空则获取途径为抓取 if (telPerson != null){ duplicate = 0; List<TelephoneCompany> telcompanys = telephoneCompanyManager.findBy("vcompany", vcompany); if(telcompanys.size()!=0){ /*for(k=0;k<telcompanys.size();k++){ if(telcompanys.get(k).getState()== 3){ duplicate = 1; break; } }*/ duplicate = 1; if(1==duplicate){ log.add("工作表" + i + " 第" + (row + 1)+ "行：该公司已存在于电话部门，批量导入停止"); if(salesPerson.getUnitInfo().getUnitType().getId()==5){ msalesCompanyManager.remove(msalesCompany); } else{ salesCompanyManager.remove(salesCompany); } break; } } telephoneCompany.setVcompany(vcompany); telephoneCompany.setState(new Integer(3)); telephoneCompany.setUser(telPerson); telephoneCompanyManager.save(telephoneCompany); } vcompany.setReject(reject); vcompany.setState(new Integer(1));if(reject.getRejectid()!=1){vcompany.setRejecttimes(new Integer(1));} save(vcompany); count++;} //A+伪状态，已转移,已分配else if(state==5){ if (salesPerson != null){ if(salesPerson.getUnitInfo().getUnitType().getId()==5){ //若表中公司已存在且状态和导入状态一样，则为重复数据 duplicate = 0; List<MsalesCompany> msalesconpanys = msalesCompanyManager.findBy("vcompany", vcompany); if(msalesconpanys.size()!=0){ for(k=0;k<msalesconpanys.size();k++){ if(msalesconpanys.get(k).getAddstate()== 0){ duplicate = 1; break; } } if(duplicate == 1){ log.add("工作表" + i + " 第" + (row + 1)+ "行：该公司已存在于该中型部门,批量导入停止"); break; } } msalesCompany.setAddstate(new Integer(0)); msalesCompany.setState(new Integer(3)); msalesCompany.setVcompany(vcompany); msalesCompany.setUser(salesPerson); msalesCompany.setUnitInfo(salesPerson.getUnitInfo()); msalesCompanyManager.save(msalesCompany); } else{ duplicate = 0; List<SalesCompany> salescompanys = salesCompanyManager.findBy("vcompany", vcompany); if(salescompanys.size()!=0){ for(k=0;k<salescompanys.size();k++){ if(salescompanys.get(k).getAddState()==0){ duplicate = 1; break; } } if(1==duplicate){ log.add("工作表" + i + " 第" + (row + 1)+ "行：该公司已存在于销售部门"); break; } } salesCompany.setVcompany(vcompany); salesCompany.setAddState(new Integer(0)); salesCompany.setState(new Integer(1)); salesCompany.setUser(salesPerson); salesCompany.setUnitInfo(salesPerson.getUnitInfo()); salesCompanyManager.save(salesCompany); } }else{ log.add("工作表" + i + " 第" + (row + 1)+ "行：在伪A+已转移已分配状态销售专员不能为空,批量导入停止");break; } //若电话专员为空则获取途径为抓取 if (telPerson != null){ duplicate = 0; List<TelephoneCompany> telcompanys = telephoneCompanyManager.findBy("vcompany", vcompany); if(telcompanys.size()!=0){ /*for(k=0;k<telcompanys.size();k++){ if(telcompanys.get(k).getState()== 3){ duplicate = 1; break; } }*/ duplicate = 1; if(1==duplicate){ log.add("工作表" + i + " 第" + (row + 1)+ "行：该公司已存在于电话部门，批量导入停止"); if(salesPerson.getUnitInfo().getUnitType().getId()==5){ msalesCompanyManager.remove(msalesCompany); } else{ salesCompanyManager.remove(salesCompany); } break; } } telephoneCompany.setVcompany(vcompany); telephoneCompany.setState(new Integer(3)); telephoneCompany.setUser(telPerson); telephoneCompanyManager.save(telephoneCompany); } vcompany.setReject(reject); vcompany.setState(new Integer(1));if(reject.getRejectid()!=1){vcompany.setRejecttimes(new Integer(1));} save(vcompany); count++;} //A+意向else if(state==6){System.out.println("state = 6"); salesCompany.setVcompany(vcompany); if (salesPerson != null){ duplicate = 0; List<SalesCompany> salescompanys = salesCompanyManager.findBy("vcompany", vcompany); if(salescompanys.size()!=0){ for(k=0;k<salescompanys.size();k++){ if(salescompanys.get(k).getAddState()==0){ duplicate = 1; break; } } if(1==duplicate){ log.add("工作表" + i + " 第" + (row + 1)+ "行：该公司已存在于销售部门"); break; } } System.out.println("sales person not null"); salesCompany.setVcompany(vcompany); salesCompany.setAddState(new Integer(0)); salesCompany.setState(new Integer(2)); salesCompany.setUser(salesPerson); salesCompany.setUnitInfo(salesPerson.getUnitInfo()); System.out.println("do save!"); salesCompanyManager.save(salesCompany); }else{ log.add("工作表" + i + " 第" + (row + 1)+ "行：在在A+意向状态销售专员不能为空,批量导入停止");break; }//若电话专员为空则获取途径为抓取 if (telPerson != null){ System.out.println("not break out of for loop"); duplicate = 0; List<TelephoneCompany> telcompanys = telephoneCompanyManager.findBy("vcompany", vcompany); if(telcompanys.size()!=0){ /*for(k=0;k<telcompanys.size();k++){ if(telcompanys.get(k).getState()== 0){ duplicate = 1; break; } }*/ duplicate = 1; if(1==duplicate){ log.add("工作表" + i + " 第" + (row + 1)+ "行：该公司已存在于电话部门，批量导入停止"); salesCompanyManager.remove(salesCompany); break; } } telephoneCompany.setVcompany(vcompany); telephoneCompany.setState(new Integer(4)); telephoneCompany.setUser(telPerson); if(score == null ||score== 0){ log.add("工作表" + i + " 第" + (row + 1) + "行：在非抓取状态电话评分不能为空或0,系统将默认评分3"); score = 3; } telephoneCompany.setGrade(score); telephoneCompanyManager.save(telephoneCompany); } vcompany.setReject(reject); vcompany.setState(new Integer(1)); vcompany.setAplusTime(aplus);if(reject.getRejectid()!=1){vcompany.setRejecttimes(new Integer(1));} save(vcompany); count++;}//返单else if(state==7){ if (telPerson != null){ duplicate = 0; List<TelephoneCompany> telcompanys = telephoneCompanyManager.findBy("vcompany", vcompany); if(telcompanys.size()!=0){ /*for(k=0;k<telcompanys.size();k++){ if(telcompanys.get(k).getState()== 5){ duplicate = 1; break; } }*/ duplicate = 1; if(1==duplicate){ log.add("工作表" + i + " 第" + (row + 1)+ "行：该公司已存在于电话部门，批量导入停止"); break; } } telephoneCompany.setVcompany(vcompany); telephoneCompany.setState(new Integer(5)); telephoneCompany.setUser(telPerson); telephoneCompany.setGrade(new Integer(0)); telephoneCompanyManager.save(telephoneCompany); }else{ log.add("工作表" + i + " 第" + (row + 1)+ "行：在返单状态电话专员不能为空,批量导入停止");break; } vcompany.setReject(reject); vcompany.setState(new Integer(1));if(reject.getRejectid()!=1){vcompany.setRejecttimes(new Integer(1));} save(vcompany); count++;} //Belse if(state==8){ if (salesPerson != null){ if(salesPerson.getUnitInfo().getUnitType().getId()==5){ //若表中公司已存在且状态和导入状态一样，则为重复数据 duplicate = 0; List<MsalesCompany> msalesconpanys = msalesCompanyManager.findBy("vcompany", vcompany); if(msalesconpanys.size()!=0){ for(k=0;k<msalesconpanys.size();k++){ if(msalesconpanys.get(k).getAddstate()== 0){ duplicate = 1; break; } } if(duplicate == 1){ log.add("工作表" + i + " 第" + (row + 1)+ "行：该公司已存在于该中型部门,批量导入停止"); break; } } msalesCompany.setAddstate(new Integer(0)); msalesCompany.setState(new Integer(5)); msalesCompany.setVcompany(vcompany); msalesCompany.setUser(salesPerson); msalesCompany.setUnitInfo(salesPerson.getUnitInfo()); msalesCompanyManager.save(msalesCompany); } else{ duplicate = 0; List<SalesCompany> salescompanys = salesCompanyManager.findBy("vcompany", vcompany); if(salescompanys.size()!=0){ for(k=0;k<salescompanys.size();k++){ if(salescompanys.get(k).getAddState()==0){ duplicate = 1; break; } } if(1==duplicate){ log.add("工作表" + i + " 第" + (row + 1)+ "行：该公司已存在于销售部门"); break; } } salesCompany.setVcompany(vcompany); salesCompany.setAddState(new Integer(0)); salesCompany.setState(new Integer(3)); salesCompany.setUser(salesPerson); salesCompany.setUnitInfo(salesPerson.getUnitInfo()); salesCompanyManager.save(salesCompany); } }else{ log.add("工作表" + i + " 第" + (row + 1)+ "行：在B状态销售专员不能为空,批量导入停止");break; } //若电话专员为空则获取途径为抓取 if (telPerson != null){ duplicate = 0; List<TelephoneCompany> telcompanys = telephoneCompanyManager.findBy("vcompany", vcompany); if(telcompanys.size()!=0){ /*for(k=0;k<telcompanys.size();k++){ if(telcompanys.get(k).getState()== 4){ duplicate = 1; break; } }*/ duplicate = 1; if(1==duplicate){ log.add("工作表" + i + " 第" + (row + 1)+ "行：该公司已存在于电话部门，批量导入停止"); if(salesPerson.getUnitInfo().getUnitType().getId()==5){ msalesCompanyManager.remove(msalesCompany); } else{ salesCompanyManager.remove(salesCompany); } break; } } telephoneCompany.setVcompany(vcompany); telephoneCompany.setState(new Integer(4)); telephoneCompany.setUser(telPerson); if(score == null||score==0){ log.add("工作表" + i + " 第" + (row + 1) + "行：在非抓取状态电话评分不能为空或0,系统将默认评分3"); score = 3; } telephoneCompany.setGrade(score); telephoneCompanyManager.save(telephoneCompany); } vcompany.setReject(reject); vcompany.setState(new Integer(1)); vcompany.setAplusTime(aplus);if(reject.getRejectid()!=1){vcompany.setRejecttimes(new Integer(1));} save(vcompany); count++;} //Celse if(state==9){ if (salesPerson != null){ if(salesPerson.getUnitInfo().getUnitType().getId()==5){ //若表中公司已存在且状态和导入状态一样，则为重复数据 duplicate = 0; List<MsalesCompany> msalesconpanys = msalesCompanyManager.findBy("vcompany", vcompany); if(msalesconpanys.size()!=0){ for(k=0;k<msalesconpanys.size();k++){ if(msalesconpanys.get(k).getAddstate()== 0){ duplicate = 1; break; } } if(duplicate == 1){ log.add("工作表" + i + " 第" + (row + 1)+ "行：该公司已存在于该中型部门,批量导入停止"); break; } } msalesCompany.setAddstate(new Integer(0)); msalesCompany.setState(new Integer(6)); msalesCompany.setVcompany(vcompany); msalesCompany.setUser(salesPerson); msalesCompany.setUnitInfo(salesPerson.getUnitInfo()); msalesCompanyManager.save(msalesCompany); } else{ duplicate = 0; List<SalesCompany> salescompanys = salesCompanyManager.findBy("vcompany", vcompany); if(salescompanys.size()!=0){ for(k=0;k<salescompanys.size();k++){ if(salescompanys.get(k).getAddState()==0){ duplicate = 1; break; } } if(1==duplicate){ log.add("工作表" + i + " 第" + (row + 1)+ "行：该公司已存在于销售部门"); break; } } salesCompany.setVcompany(vcompany); salesCompany.setAddState(new Integer(0)); salesCompany.setState(new Integer(4)); salesCompany.setUser(salesPerson); salesCompany.setUnitInfo(salesPerson.getUnitInfo()); salesCompanyManager.save(salesCompany); } }else{ log.add("工作表" + i + " 第" + (row + 1)+ "行：在C状态销售专员不能为空,批量导入停止");break; } //若电话专员为空则获取途径为抓取 if (telPerson != null){ duplicate = 0; List<TelephoneCompany> telcompanys = telephoneCompanyManager.findBy("vcompany", vcompany); if(telcompanys.size()!=0){ /*for(k=0;k<telcompanys.size();k++){ if(telcompanys.get(k).getState()== 4){ duplicate = 1; break; } }*/ duplicate = 1; if(1==duplicate){ log.add("工作表" + i + " 第" + (row + 1)+ "行：该公司已存在于电话部门，批量导入停止"); if(salesPerson.getUnitInfo().getUnitType().getId()==5){ msalesCompanyManager.remove(msalesCompany); } else{ salesCompanyManager.remove(salesCompany); } break; } } telephoneCompany.setVcompany(vcompany); telephoneCompany.setState(new Integer(4)); telephoneCompany.setUser(telPerson); if(score == null||score==0){ log.add("工作表" + i + " 第" + (row + 1) + "行：在非抓取状态电话评分不能为空或0,系统将默认评分3"); score = 3; } telephoneCompany.setGrade(score); telephoneCompanyManager.save(telephoneCompany); } vcompany.setReject(reject); vcompany.setState(new Integer(1)); vcompany.setAplusTime(aplus);if(reject.getRejectid()!=1){vcompany.setRejecttimes(new Integer(1));} save(vcompany); count++;}//Delse if(state==10){ if (salesPerson != null){ if(salesPerson.getUnitInfo().getUnitType().getId()==5){ //若表中公司已存在且状态和导入状态一样，则为重复数据 duplicate = 0; List<MsalesCompany> msalesconpanys = msalesCompanyManager.findBy("vcompany", vcompany); if(msalesconpanys.size()!=0){ for(k=0;k<msalesconpanys.size();k++){ if(msalesconpanys.get(k).getAddstate()== 0){ duplicate = 1; break; } } if(duplicate == 1){ log.add("工作表" + i + " 第" + (row + 1)+ "行：该公司已存在于该中型部门,批量导入停止"); break; } } msalesCompany.setAddstate(new Integer(0)); msalesCompany.setState(new Integer(7)); msalesCompany.setVcompany(vcompany); msalesCompany.setUser(salesPerson); msalesCompany.setUnitInfo(salesPerson.getUnitInfo()); msalesCompanyManager.save(msalesCompany); } else{ duplicate = 0; List<SalesCompany> salescompanys = salesCompanyManager.findBy("vcompany", vcompany); if(salescompanys.size()!=0){ for(k=0;k<salescompanys.size();k++){ if(salescompanys.get(k).getAddState()==0){ duplicate = 1; break; } } if(1==duplicate){ log.add("工作表" + i + " 第" + (row + 1)+ "行：该公司已存在于销售部门"); break; } } salesCompany.setVcompany(vcompany); salesCompany.setAddState(new Integer(0)); salesCompany.setState(new Integer(5)); salesCompany.setUser(salesPerson); salesCompany.setUnitInfo(salesPerson.getUnitInfo()); salesCompanyManager.save(salesCompany); } }else{ log.add("工作表" + i + " 第" + (row + 1)+ "行：在D状态销售专员不能为空,批量导入停止");break; } //若电话专员为空则获取途径为抓取 if (telPerson != null){ duplicate = 0; List<TelephoneCompany> telcompanys = telephoneCompanyManager.findBy("vcompany", vcompany); if(telcompanys.size()!=0){ /*for(k=0;k<telcompanys.size();k++){ if(telcompanys.get(k).getState()== 4){ duplicate = 1; break; } }*/ duplicate = 1; if(1==duplicate){ log.add("工作表" + i + " 第" + (row + 1)+ "行：该公司已存在于电话部门，批量导入停止"); if(salesPerson.getUnitInfo().getUnitType().getId()==5){ msalesCompanyManager.remove(msalesCompany); } else{ salesCompanyManager.remove(salesCompany); } break; } } telephoneCompany.setVcompany(vcompany); telephoneCompany.setState(new Integer(4)); telephoneCompany.setUser(telPerson); if(score == null ||score==0){ log.add("工作表" + i + " 第" + (row + 1) + "行：在非抓取状态电话评分不能为空或0,系统将默认评分3"); score = 3; } telephoneCompany.setGrade(score); telephoneCompanyManager.save(telephoneCompany); } //若在d表中已存在记录 List<Dcompany> dcompanys = dcompanyManager.findBy("vcompany", vcompany); if(dcompanys.size()== 0){ dcompany.setVcompany(vcompany); dcompany.setEstate(new Integer(0)); dcompanyManager.save(dcompany); } else{ log.add("工作表" + i + " 第" + (row + 1)+ "行：该公司已存在于签约公司记录中，批量导入停止"); break; } vcompany.setReject(reject); vcompany.setState(new Integer(1)); vcompany.setAplusTime(aplus);if(reject.getRejectid()!=1){vcompany.setRejecttimes(new Integer(1));} save(vcompany); count++;} //Eelse if(state==11){if (salesPerson != null){ if(salesPerson.getUnitInfo().getUnitType().getId()==5){ //若表中公司已存在且状态和导入状态一样，则为重复数据 duplicate = 0; List<MsalesCompany> msalesconpanys = msalesCompanyManager.findBy("vcompany", vcompany); if(msalesconpanys.size()!=0){ for(k=0;k<msalesconpanys.size();k++){ if(msalesconpanys.get(k).getAddstate()== 0){ duplicate = 1; break; } } if(duplicate == 1){ log.add("工作表" + i + " 第" + (row + 1)+ "行：该公司已存在于该中型部门,批量导入停止"); break; } } msalesCompany.setAddstate(new Integer(0)); msalesCompany.setState(new Integer(7)); msalesCompany.setVcompany(vcompany); msalesCompany.setUser(salesPerson); msalesCompany.setUnitInfo(salesPerson.getUnitInfo()); msalesCompanyManager.save(msalesCompany); } else{ duplicate = 0; List<SalesCompany> salescompanys = salesCompanyManager.findBy("vcompany", vcompany); if(salescompanys.size()!=0){ for(k=0;k<salescompanys.size();k++){ if(salescompanys.get(k).getAddState()==0){ duplicate = 1; break; } } if(1==duplicate){ log.add("工作表" + i + " 第" + (row + 1)+ "行：该公司已存在于销售部门,批量导入停止"); break; } } salesCompany.setVcompany(vcompany); salesCompany.setAddState(new Integer(0)); salesCompany.setState(new Integer(5)); salesCompany.setUser(salesPerson); salesCompany.setUnitInfo(salesPerson.getUnitInfo()); salesCompanyManager.save(salesCompany); }}else{ log.add("工作表" + i + " 第" + (row + 1)+ "行：在E状态销售专员不能为空,批量导入停止");break; } //若电话专员为空则获取途径为抓取 if (telPerson != null){ duplicate = 0; List<TelephoneCompany> telcompanys = telephoneCompanyManager.findBy("vcompany", vcompany); if(telcompanys.size()!=0){ /*for(k=0;k<telcompanys.size();k++){ if(telcompanys.get(k).getState()== 4){ duplicate = 1; break; } } */ duplicate = 1; if(1==duplicate){ log.add("工作表" + i + " 第" + (row + 1)+ "行：该公司已存在于电话部门，批量导入停止"); if(salesPerson.getUnitInfo().getUnitType().getId()==5){ msalesCompanyManager.remove(msalesCompany); } else{ salesCompanyManager.remove(salesCompany); } break; } } telephoneCompany.setVcompany(vcompany); telephoneCompany.setState(new Integer(4)); telephoneCompany.setUser(telPerson); if(score == null|| score==0){ log.add("工作表" + i + " 第" + (row + 1)+ "行：在非抓取状态电话评分不能为空或0,系统将默认评分3"); score = 3; } telephoneCompany.setGrade(score); telephoneCompanyManager.save(telephoneCompany); } //若在d表中已存在记录 List<Dcompany> dcompanys = dcompanyManager.findBy("vcompany", vcompany); if(dcompanys.size()== 0){ dcompany.setVcompany(vcompany); dcompany.setEstate(new Integer(1)); dcompanyManager.save(dcompany); } else{ log.add("工作表" + i + " 第" + (row + 1)+ "行：该公司已存在于签约公司记录中，批量导入停止"); break; } vcompany.setReject(reject); vcompany.setState(new Integer(1)); vcompany.setAplusTime(aplus);if(reject.getRejectid()!=1){vcompany.setRejecttimes(new Integer(1));} save(vcompany); count++;}}//for cells}//for rowslog.add("成功导入" + count + "条数据");rwb.close();fis.close();} catch (BiffException e) {System.err.println("Open file ERROR! - BiffException");e.printStackTrace();log.add("系统错误");return log;} catch (IOException e) {System.err.println("Open file ERROR! - IOException");e.printStackTrace();log.add("系统错误");return log;} catch (NumberFormatException e) {System.err.println("Open file ERROR! - IOException");e.printStackTrace();log.add("系统错误,但成功导入" + count + "条数据");return log;} catch (java.lang.OutOfMemoryError e) {log.add("系统错误,xls文件出错");return log;} catch (Exception e) {System.err.println("Some Error! - Exception");e.printStackTrace();log.add("发生错误,但成功导入" + count + "条数据");return log;}return log;}/** * 查询公司信息 */@SuppressWarnings("unchecked")public List<Vcompany> searchVcompany(Map filter,BusinessManager businessManager, TradeTypeManager tradeTypeManager,RejectManager rejectManager, Integer apply) {Criteria criteria = createCriteria();Integer valuetradetype = Integer.valueOf(filter.get("tradetypeid").toString());Integer valuebusiness = Integer.valueOf(filter.get("businessid").toString());String valueregion = filter.get("region").toString(); Integer valueservice = Integer.valueOf(filter.get("service").toString());if(valueservice.intValue()!=-1){criteria.add(Restrictions.eq("service", serviceManager.get(valueservice)));}if (valuetradetype.intValue() != -1) { System.out.println("in if valuetradetype"+valuetradetype); // 获取所有在Vcompany中是tradeTypeManager.getTradeType(valuetradetype)类型的所有Vcompanycriteria.add(Restrictions.eq("tradetype", tradeTypeManager.getTradeType(valuetradetype)));}if (valuebusiness.intValue() != -1) {// System.out.println("in if valuebusiness"+valuebusiness);criteria.add(Restrictions.eq("business", businessManager.getBusiness(valuebusiness)));}if (!(valueregion.equals("") || valueregion == null)) {criteria.add(Restrictions.like("region", "%" + valueregion + "%"));}criteria.add(Restrictions.like("comName", "%"+ filter.get("name").toString() + "%"));if (!filter.get("mainfunction").toString().equals("")) {criteria.add(Restrictions.like("mainfunction", "%"+ filter.get("mainfunction").toString() + "%"));}if (apply == 1) {criteria.add(Restrictions.or(Restrictions.eq("state",new Integer(0)), Restrictions.eq("state", new Integer(2))));Integer valuereject = Integer.valueOf(filter.get("rejectid").toString());if (valuereject.intValue() != -1) {criteria.add(Restrictions.eq("reject", rejectManager.getReject(valuereject)));criteria.addOrder(Order.asc("aplusTime"));}}List<Vcompany> vcompanys = criteria.list();return vcompanys;}@SuppressWarnings("unchecked")public List<Vcompany> searchVcompany(Map filter) {Criteria criteria = createCriteria();criteria.add(Restrictions.like("comName", "%"+ filter.get("name").toString() + "%"));List<Vcompany> vcompanys = criteria.list();return vcompanys;}/** * 得到待分配公司信息(电话经理) */@SuppressWarnings("unchecked")public Page getApplyCom(int pageNo, int pageSize,Limit limit) {Criteria criteria = createCriteria();//列出状态是未分配，拒绝可再打，返单再打的公司criteria.add(Restrictions.in("state", states));if(limit.getSort().getProperty()!= null){if(limit.getSort().getSortOrder()=="asc"){criteria.addOrder(Order.asc(limit.getSort().getProperty()));}else{criteria.addOrder(Order.desc(limit.getSort().getProperty()));}}Assert.isTrue(pageNo >= 1, "pageNo should start from 1");CriteriaImpl impl = (CriteriaImpl) criteria;System.out.println("apply here1");// 先把Projection和OrderBy条件取出来,清空两者来执行Count操作Projection projection = impl.getProjection();List<CriteriaImpl.OrderEntry> orderEntries;try {orderEntries = (List) BeanUtils.forceGetProperty(impl, "orderEntries");BeanUtils.forceSetProperty(impl, "orderEntries", new ArrayList());} catch (Exception e) {throw new InternalError(" Runtime Exception impossibility throw ");}System.out.println("apply here2");// 执行查询long totalCount = (Integer)criteria.setProjection(Projections.rowCount()).uniqueResult();System.out.println("totalCount:"+totalCount);// 将之前的Projection和OrderBy条件重新设回去criteria.setProjection(projection);if (projection == null) {criteria.setResultTransformer(CriteriaSpecification.ROOT_ENTITY);}try {BeanUtils.forceSetProperty(impl, "orderEntries", orderEntries);} catch (Exception e) {throw new InternalError(" Runtime Exception impossibility throw ");}// 返回分页对象if (totalCount < 1)return new Page();System.out.println("Mypage here3");int startIndex = Page.getStartOfPage(pageNo, pageSize);System.out.println("startIndex:"+startIndex);List list = criteria.setFirstResult(startIndex).setMaxResults(pageSize).list();return new Page(startIndex, totalCount, pageSize, list);}/*列出无效客户pjj*/public List<Vcompany> getInvalidCom(){Criteria criteria = createCriteria();criteria.add(Restrictions.eq("state", new Integer(3)));List<Vcompany> invalidComs = criteria.list();return invalidComs;}/*search invalid companys pjj*/public List<Vcompany> searchInvalidCom(Map filter, BusinessManager businessManager, TradeTypeManager tradeTypeManager){Criteria criteria = createCriteria();Integer valuetradetype = Integer.valueOf(filter.get("tradetypeid").toString());Integer valuebusiness = Integer.valueOf(filter.get("businessid").toString());String valueregion = filter.get("region").toString(); Integer valueservice = Integer.valueOf(filter.get("service").toString());if(valueservice.intValue()!=-1){criteria.add(Restrictions.eq("service", serviceManager.get(valueservice)));}if (valuetradetype.intValue() != -1) {criteria.add(Restrictions.eq("tradetype", tradeTypeManager.getTradeType(valuetradetype)));}if (valuebusiness.intValue() != -1) {criteria.add(Restrictions.eq("business", businessManager.getBusiness(valuebusiness)));}if (!(valueregion.equals("") || valueregion == null)) {criteria.add(Restrictions.like("region", "%" + valueregion + "%"));}criteria.add(Restrictions.like("comName", "%"+ filter.get("name").toString() + "%"));if (!filter.get("mainfunction").toString().equals("")) {criteria.add(Restrictions.like("mainfunction", "%"+ filter.get("mainfunction").toString() + "%"));}criteria.add(Restrictions.eq("state", new Integer(3)));List<Vcompany> vcompanys = criteria.list();return vcompanys;}/** * 分页得到公司信息 */@SuppressWarnings("unchecked")public Page getPageAll(int pageNo,int pageSize,Limit limit) {System.out.println("page here1");Criteria criteria = createCriteria();System.out.println("page here2");//若页面需要排序if(limit.getSort().getProperty()!= null){if(limit.getSort().getSortOrder()=="asc"){criteria.addOrder(Order.asc(limit.getSort().getProperty()));}else{criteria.addOrder(Order.desc(limit.getSort().getProperty()));}}//return this.pagedQuery(hql, new Integer(1), new Integer(5), null);Page result = this.pagedQuery(criteria,pageNo,pageSize);System.out.println("result.getPageSize:"+result.getPageSize());return result;}/** * 分页查询函数，使用已设好查询条件与排序的<code>Criteria</code>. * @param pageNo 页号,从1开始. * @return 含总记录数和当前页数据的Page对象. * 分页查询公司信息（apply=1则为电话经理查询未分配公司信息） */public Page MypagedQuery(Map filter,BusinessManager businessManager, TradeTypeManager tradeTypeManager,RejectManager rejectManager,Integer apply, int pageNo, int pageSize,Limit limit) {System.out.println("MypagedQuery");Criteria criteria = createCriteria();//若页面需要排序if(limit.getSort().getProperty()!= null){if(limit.getSort().getSortOrder()=="asc"){criteria.addOrder(Order.asc(limit.getSort().getProperty()));}else{criteria.addOrder(Order.desc(limit.getSort().getProperty()));}}System.out.println("filter:"+filter);System.out.println("filter valuetradetype:"+filter.get("tradetypeid"));Integer valuetradetype = Integer.valueOf(filter.get("tradetypeid").toString());Integer valuebusiness = Integer.valueOf(filter.get("businessid").toString());Integer valuerejecttimes = Integer.valueOf(filter.get("rejecttimes").toString());String valueregion = filter.get("region").toString(); Integer valueservice = Integer.valueOf(filter.get("service").toString());if(valueservice.intValue()!=-1){criteria.add(Restrictions.eq("service", serviceManager.get(valueservice)));}if (valuetradetype.intValue() != -1) { System.out.println("in if valuetradetype"+valuetradetype); // 获取所有在Vcompany中是tradeTypeManager.getTradeType(valuetradetype)类型的所有Vcompanycriteria.add(Restrictions.eq("tradetype", tradeTypeManager.getTradeType(valuetradetype)));}if (valuebusiness.intValue() != -1) {// System.out.println("in if valuebusiness"+valuebusiness);criteria.add(Restrictions.eq("business", businessManager.getBusiness(valuebusiness)));}if (!(valueregion.equals("") || valueregion == null)) {criteria.add(Restrictions.like("region", "%" + valueregion + "%"));}System.out.println("comname in search:"+filter.get("name").toString());criteria.add(Restrictions.like("comName", "%"+ filter.get("name").toString() + "%"));if (!filter.get("mainfunction").toString().equals("")) {criteria.add(Restrictions.like("mainfunction", "%"+ filter.get("mainfunction").toString() + "%"));}//apply=1为电话经理搜索未分配的v公司if (apply == 1) {criteria.add(Restrictions.in("state", states));Integer valuereject = Integer.valueOf(filter.get("rejectid").toString());if (valuereject.intValue() != -1) {criteria.add(Restrictions.eq("reject", rejectManager.getReject(valuereject)));criteria.addOrder(Order.asc("aplusTime"));}}if(valuerejecttimes != -1){if(valuerejecttimes==6){criteria.add(Restrictions.gt("rejecttimes", 5));}else{ criteria.add(Restrictions.eq("rejecttimes",valuerejecttimes));}}System.out.println(criteria);Assert.isTrue(pageNo >= 1, "pageNo should start from 1");CriteriaImpl impl = (CriteriaImpl) criteria;System.out.println("Mypage here1");// 先把Projection和OrderBy条件取出来,清空两者来执行Count操作Projection projection = impl.getProjection();List<CriteriaImpl.OrderEntry> orderEntries;try {orderEntries = (List) BeanUtils.forceGetProperty(impl, "orderEntries");BeanUtils.forceSetProperty(impl, "orderEntries", new ArrayList());} catch (Exception e) {throw new InternalError(" Runtime Exception impossibility throw ");}System.out.println("Mypage here2");// 执行查询long totalCount = (Integer)criteria.setProjection(Projections.rowCount()).uniqueResult();System.out.println("totalCount:"+totalCount);// 将之前的Projection和OrderBy条件重新设回去criteria.setProjection(projection);if (projection == null) {criteria.setResultTransformer(CriteriaSpecification.ROOT_ENTITY);}try {BeanUtils.forceSetProperty(impl, "orderEntries", orderEntries);} catch (Exception e) {throw new InternalError(" Runtime Exception impossibility throw ");}// 返回分页对象if (totalCount < 1)return new Page();System.out.println("Mypage here3");int startIndex = Page.getStartOfPage(pageNo, pageSize);System.out.println("startIndex:"+startIndex);List list = criteria.setFirstResult(startIndex).setMaxResults(pageSize).list();return new Page(startIndex, totalCount, pageSize, list);}}