package com.crm.app.fubon.deliver.service;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.LabelCell;
import jxl.Sheet;
import jxl.Workbook;
import org.apache.log4j.Logger;
import org.apache.struts2.ServletActionContext;
import com.crm.app.fubon.deliver.action.DeliverDataAction;
import com.crm.app.fubon.deliver.model.DeliverData;
import com.crm.extend.file.fileUpload.model.FileUpload;
import com.crm.extend.file.fileUpload.page.FileUploadPage;
import com.crm.extend.file.fileUpload.util.DownLoad;
import com.crm.extend.file.fileUpload.util.ExcelFile;
import com.crm.extend.file.fileUpload.util.WriteExcel;
import com.crm.framework.common.CommonPage;
import com.crm.framework.common.Constant;
import com.crm.framework.common.util.CUID;
import com.crm.framework.common.util.DateTime;
import com.crm.framework.dao.selector.Query;
import com.crm.framework.service.ServiceFactory;
import com.crm.framework.service.SimpleService;
import com.crm.framework.util.fileUpload.CommonFilesUpload;
import com.lowagie.text.pdf.codec.Base64.InputStream;
import com.opensymphony.xwork2.ActionContext;
import com.sybase.jdbc3.tds.e;
/**
*/
public class DeliverDataService extends SimpleService {
private static Logger logger = Logger.getLogger(DeliverDataService.class);
public static DeliverDataAction deliverDataaction = new DeliverDataAction();
/**
* 数据导出excel
*
* @return
*/
public static void exportExcel(Query query, int count) {
try {
HttpServletResponse response = (HttpServletResponse) ActionContext
.getContext().get(ServletActionContext.HTTP_RESPONSE);
SimpleService service = (SimpleService) ServiceFactory
.getService(SimpleService.class);
DateTime datetime = new DateTime(new Date(),
DateTime.YEAR_TO_SECOND);
String createfiletime = datetime.toString().replace("-", "")
.replace(" ", "").replace(":", "");
String distr = Constant.REAL_PATH + "/" + "download/";
File file = new File(distr);
if (!file.exists()) {
file.mkdirs();
}
distr += "download.xls";
File f = new File(distr);
f.createNewFile();
String fileName = createfiletime + ".xls";
WriteExcel writeExcel = new WriteExcel(distr);
writeExcel.createNewSheet("导出数据");
writeExcel.createNewRow();
/** 写表头 */
writeExcel.createNewCell("时间");
writeExcel.createNewCell("险种");
writeExcel.createNewCell("单证类别");
writeExcel.createNewCell("快递公司");
writeExcel.currNumOfColumns = 0; // 记录当前的列数
List<DeliverData> deliverDatas = new ArrayList<DeliverData>();
int rows = 0;
while ((rows * 100) <= count) { // 每次导100条
deliverDatas = (List<DeliverData>) service.queryPage(query,
rows, 100).getResult();
rows++;
writeExcelNew(writeExcel, deliverDatas);
deliverDatas.clear();
}
writeExcel.close();
DownLoad down = new DownLoad();
down.DownLoadFile(response, distr, fileName);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 生成Excel文件 excel方式
*
* @param WriteExcel
* writeExcel excel对象
* @param List
* templist 数据列表
*/
private static void writeExcelNew(WriteExcel writeExcel,
List<DeliverData> templist) {
if (templist != null) {
for (int i = 0; i < templist.size(); i++) {
writeExcel.createNewRow();
writeExcel.createNewCell(new DateTime(templist.get(i)
.getCreatetime(), DateTime.YEAR_TO_SECOND).toString());
String insflag = templist.get(i).getInsurflag();
if (insflag != null && !insflag.equals("")&& !insflag.equals("null")) {
writeExcel.createNewCell(insflag + "");
} else {
writeExcel.createNewCell("");
}
String docutype = templist.get(i).getDocutype();
if(docutype == "1" || docutype.equals("1")){
writeExcel.createNewCell("投保单");
}else if(docutype == "2" || docutype.equals("2")){
writeExcel.createNewCell("保单");
}else if(docutype == "3" || docutype.equals("3")){
writeExcel.createNewCell("保单与投保单");
}else {
writeExcel.createNewCell("");
}
String policyno = templist.get(i).getPolicyno();
if (policyno != null && !policyno.equals("")&&!policyno.equals("null")) {
writeExcel.createNewCell(policyno + "");
} else {
writeExcel.createNewCell("");
}
String proposalno = templist.get(i).getProposalno();
if (proposalno != null && !proposalno.equals("")&& !proposalno.equals("null")) {
writeExcel.createNewCell(proposalno + "");
} else {
writeExcel.createNewCell("");
}
String appliname = templist.get(i).getAppliname();
if (appliname != null && !appliname.equals("")&& !appliname.equals("null")) {
writeExcel.createNewCell(appliname + "");
} else {
writeExcel.createNewCell("");
}
String area = templist.get(i).getDeliverarea() + "";
if (area !=null && !area.equals("")&&!area.equals("null")) {
try {
area=String.valueOf(DeliverDataAction.getBaseName("deliverarea",area));
} catch (Exception e) {
e.printStackTrace();
}
writeExcel.createNewCell(area);
} else {
writeExcel.createNewCell("");
}
String address = templist.get(i).getDeliveraddress();
if (address != null && !address.equals("") && !address.equals("null")) {
writeExcel.createNewCell(address + "");
} else {
writeExcel.createNewCell("");
}
String tel = templist.get(i).getTel();
if (tel != null && !tel.equals("") && !tel.equals("null")) {
writeExcel.createNewCell(tel + "");
} else {
writeExcel.createNewCell("");
}
String gift = templist.get(i).getGiftid() + "";
if (gift !=null && !gift.equals("") && !gift.equals("null")) {
try {
gift=DeliverDataAction.getBaseName("gifttype",gift);
} catch (Exception e) {
e.printStackTrace();
}
writeExcel.createNewCell(gift);
} else {
writeExcel.createNewCell("");
}
String giftid = templist.get(i).getGiftid();
if (giftid != null && !giftid.equals("") && !giftid.equals("null")) {
writeExcel.createNewCell(giftid + "");
} else {
writeExcel.createNewCell("");
}
String delcompany = templist.get(i).getDelcompany();
if (delcompany !=null && !delcompany.equals("") && !delcompany.equals("null")) {
try {
delcompany=DeliverDataAction.getBaseName("sendcom",delcompany);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
writeExcel.createNewCell(delcompany);
} else {
writeExcel.createNewCell("");
}
writeExcel.currNumOfColumns = 0; // 记录当前的列数
}
}
}
/**
* 读取Excel
*/
public static void readExcel(String filePath, DeliverData deliverData) {
try {
Workbook book = Workbook.getWorkbook(new File(filePath));
Sheet sheet = book.getSheet(0);
int rows = sheet.getRows() - 1;
String feedbackdate = "";
for (int i = 1; i < rows; i++) {
System.out.println("--------------i---------------" + i);
String proposalno = sheet.getCell(4, i).getContents();
if (proposalno != "" && !proposalno.equals("")
&& proposalno != null && !proposalno.equals(null)) {
if (sheet.getCell(12, i).getType() == CellType.DATE) {
DateCell dateCell = (DateCell) sheet.getCell(12, i);
Date date = dateCell.getDate();
feedbackdate = new SimpleDateFormat("yyyy-MM-dd")
.format(date);
}
String delresults = sheet.getCell(13, i).getContents();
if (delresults == "待配送" || delresults.equals("待配送")) {
delresults = "0";
} else if (delresults == "配送中" || delresults.equals("配送中")) {
delresults = "1";
}
String delnote = sheet.getCell(14, i).getContents();
String deliver = sheet.getCell(15, i).getContents();
String delamount = sheet.getCell(16, i).getContents();
SimpleService server = ServiceFactory.getCurentService();
System.out
.println("--------------feedbackdate---------------"
+ feedbackdate);
System.out
.println("--------------delresults---------------"
+ delresults);
System.out.println("--------------delnote---------------"
+ delnote);
System.out.println("--------------deliver---------------"
+ deliver);
System.out.println("--------------delamount---------------"
+ delamount);
String hql = " update DeliverData set feedbackdate=to_date('"
+ feedbackdate
+ "','yyyy-MM-dd'),delresults='"
+ delresults
+ "',delnote='"
+ delnote
+ "',deliver='"
+ deliver
+ "',delamount='"
+ delamount + "' where proposalno=?";
server.updateByHql(DeliverData.class, hql, proposalno);
}
}
// 关闭文件
book.close();
} catch (Exception e) {
e.printStackTrace();
}
}