package com.yutu.base.service;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.yutu.base.entity.Response;
import com.yutu.base.entity.Store;
import com.yutu.base.entity.User;
import com.yutu.base.utils.MyComparator;
import jxl.CellView;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.*;
import jxl.write.Number;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import java.io.File;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
@Service
public class SaleDetailReportService extends BaseService {
private Logger logger = Logger.getLogger("download");
public Map<String, String> createSaleDetailReport(Map<String, JSONArray> resultMap, String[] tableNames, String filePath, String requestId) {
logger.info("开始解析数据");
long beginTime = System.currentTimeMillis();
JSONArray resultArr = resultMap.get("orderinfo");
List<String> tableNameList = Arrays.asList(tableNames);
for (int k = 0; k < resultArr.size(); k++) {
JSONObject orderInfoObj = resultArr.getJSONObject(k);
String id = orderInfoObj.getString("id");
for (int t = 0; t < tableNameList.size(); t++) {
JSONArray Arr = resultMap.get(tableNameList.get(t));//乘客信息表中匹配该订单
JSONArray array = new JSONArray();
for (int a = 0; a < Arr.size(); a++) {
String orderId = "";
if ("ordervalueadded".equals(tableNames[t])) {
orderId = Arr.getJSONObject(a).getString("orderId");
} else if ("orderpurchase".equals(tableNames[t])) {
} else if ("transactiondetail_old".equals(tableNames[t])) {
orderId = Arr.getJSONObject(a).getString("orderId");
} else {
orderId = Arr.getJSONObject(a).getString("order_ID");
}
if (id.equals(orderId)) {
array.add(Arr.getJSONObject(a));
resultArr.getJSONObject(k).put(tableNames[t], array);
}
}
}
}
JSONArray puchArr = resultMap.get("orderpurchase");
JSONArray transArr = resultMap.get("transactiondetail");
//去除采购信息的重复信息，并将orderId逗号分隔写入orderIds
List<Integer> listRemove = new ArrayList<>();
for (int s = 0; s < puchArr.size(); s++) {
JSONObject puchObj_out = puchArr.getJSONObject(s);
String purchaseId_out = puchObj_out.getString("purchaseId");
String orderId_out = puchObj_out.getString("orderId");
String passengerIds_out = puchObj_out.getString("passengerIds");
String flightInfoIds_out = puchObj_out.getString("flightInfoIds");
for (int i = 0; i < puchArr.size(); i++) {
if (i == s) continue;
JSONObject puchObj_in = puchArr.getJSONObject(i);
String purchaseId_in = puchObj_in.getString("purchaseId");
String orderId_in = puchObj_in.getString("orderId");
String passengerIds_in = puchObj_in.getString("passengerIds");
String flightInfoIds_in = puchObj_in.getString("flightInfoIds");
if (purchaseId_out.equals(purchaseId_in)) {
listRemove.add(i);
orderId_out += "," + orderId_in;
passengerIds_out += "," + passengerIds_in;
flightInfoIds_out += "," + flightInfoIds_in;
passengerIds_out = removeDuplicateStr(passengerIds_out);
flightInfoIds_out = removeDuplicateStr(flightInfoIds_out);
puchObj_out.remove("orderId");
puchObj_out.remove("passengerIds");
puchObj_out.remove("flightInfoIds");
puchObj_in.remove("orderId");
puchObj_out.put("orderId", orderId_out);
puchObj_out.put("passengerIds", passengerIds_out);
puchObj_out.put("flightInfoIds", flightInfoIds_out);
puchObj_in.put("orderId", "");
}
}
}
List<Object> transList = Arrays.asList(transArr.toArray());
transList = removeDuplicateList(transList);
JSONArray transConvert = new JSONArray();
for (Object obj : transList) {
transConvert.add(obj);
}
puchArr = sortJsonArray(puchArr);
Map<String, String> map = new HashMap<String, String>();//进行数据整合后的 Map
map.put("orderpurchase", puchArr.toJSONString());
map.put("transactiondetail", transConvert.toJSONString());
map.put("other", resultArr.toJSONString());
logger.info("解析数据完毕,共用时：" + (System.currentTimeMillis() - beginTime) + " ms");
return map;
}
//去除Str重复
public String removeDuplicateStr(String str) {
if (str == null || "".equals(str)) return str;
List<String> strList = Arrays.asList(str.split(",", -1));
Set set = new HashSet();
List newList = new ArrayList();
for (Iterator iter = strList.iterator(); iter.hasNext(); ) {
Object element = iter.next();
if (set.add(element))
newList.add(element);
}
str = "";
for (Object obj : newList) {
str += "," + obj;
}
return str.substring(1);
}
//去除List重复
public List removeDuplicateList(List list) {
Set set = new HashSet();
List newList = new ArrayList();
for (Iterator iter = list.iterator(); iter.hasNext(); ) {
Object element = iter.next();
if (set.add(element))
newList.add(element);
}
return newList;
}
//jsonarray排序
public JSONArray sortJsonArray(JSONArray jsonArr) {
//转成list
List<JSONObject> list = new ArrayList<JSONObject>();
JSONObject jsonObj = null;
for (int i = 0; i < jsonArr.size(); i++) {
jsonObj = (JSONObject) jsonArr.get(i);
list.add(jsonObj);
}
Collections.sort(list, new MyComparator());
jsonArr.clear();
for (int i = 0; i < list.size(); i++) {
jsonObj = list.get(i);
jsonArr.add(jsonObj);
}
//jsonArr排序
return jsonArr;
}
//生成销售明细报表
public String createExcel(Map<String, String> map, String filePath, String requestId) throws Exception {
logger.info("开始生成报表");
List<User> userlist = getUsers();
List<Store> storelist = getStores();
File files = new File(filePath);
if (!files.exists()) {
files.mkdir();
}
String file = filePath + new SimpleDateFormat("yyMMddHHmmss").format(new Date()) + ".xls";
WritableWorkbook workbook = Workbook.createWorkbook(new File(file));
DecimalFormat df = new DecimalFormat("######0.00");
String orderDetail = map.get("other");
String purchase = map.get("orderpurchase");
String transactiondetail = map.get("transactiondetail");
JSONArray orderArr = JSONArray.parseArray(orderDetail);
JSONArray purchArr = JSONArray.parseArray(purchase);
JSONArray transArr = JSONArray.parseArray(transactiondetail);
try {
WritableFont wf = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
WritableFont wf_auto = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
WritableCellFormat wcf_integer = new WritableCellFormat(wf_auto, NumberFormats.INTEGER); // 整数
WritableCellFormat wcf_float = new WritableCellFormat(wf_auto, NumberFormats.FLOAT); // 小数
//wcf_integer.setBackground(Colour.LIGHT_ORANGE); // 设置单元格的背景颜色(海洋蓝)
wcf_integer.setAlignment(Alignment.CENTRE); // 设置对齐方式
//wcf_float.setBackground(Colour.LIGHT_ORANGE); // 设置单元格的背景颜色(海洋蓝)
wcf_float.setAlignment(Alignment.CENTRE); // 设置对齐方式
WritableCellFormat wcf = new WritableCellFormat(wf); // 单元格定义
//wcf.setBackground(Colour.LIGHT_ORANGE); // 设置单元格的背景颜色(海洋蓝)
wcf.setAlignment(Alignment.CENTRE); // 设置对齐方式
WritableCellFormat wcf1 = new WritableCellFormat(wf); // 单元格定义
//wcf1.setBackground(Colour.PALE_BLUE); // 设置单元格的背景颜色 (苍白之蓝)
wcf1.setAlignment(Alignment.CENTRE); // 设置对齐方式
WritableCellFormat wcf2 = new WritableCellFormat();
wcf2.setAlignment(Alignment.CENTRE); // 设置对齐方式
wcf2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
wcf2.setWrap(true);
WritableSheet sheet = workbook.createSheet("First Sheet", 0);//创建新的一页
CellView cellView = new CellView();
cellView.setAutosize(true); //设置自动大小
cellView.setFormat(wcf1);
CellView cellView_float = new CellView();
cellView.setAutosize(true); //设置自动大小
cellView.setFormat(wcf_float);
sheet.setColumnView(0, cellView);
sheet.setColumnView(1, cellView);
sheet.setColumnView(2, cellView);
sheet.setColumnView(3, cellView);
sheet.setColumnView(4, cellView);
sheet.setColumnView(5, cellView);
sheet.setColumnView(6, cellView);
sheet.setColumnView(7, cellView);
sheet.setColumnView(8, cellView);
sheet.setColumnView(9, cellView);
sheet.setColumnView(10, cellView);
sheet.setColumnView(11, cellView);
sheet.setColumnView(12, cellView);
sheet.setColumnView(13, cellView);
sheet.setColumnView(14, cellView);
sheet.setColumnView(15, cellView);
sheet.setColumnView(16, cellView);
sheet.setColumnView(17, cellView);
sheet.setColumnView(18, cellView);
sheet.setColumnView(19, cellView);
sheet.setColumnView(20, cellView);
sheet.setColumnView(21, cellView);
sheet.setColumnView(22, cellView);
sheet.setColumnView(23, cellView);
sheet.setColumnView(24, cellView);
sheet.setColumnView(25, cellView);
sheet.setColumnView(26, cellView);
sheet.setColumnView(27, cellView);
sheet.setColumnView(28, cellView);
sheet.setColumnView(29, cellView);
sheet.setColumnView(30, cellView);
sheet.setColumnView(31, cellView);
sheet.setColumnView(32, cellView);
sheet.setColumnView(33, cellView);
sheet.setColumnView(34, cellView);
sheet.setColumnView(35, cellView);
sheet.setColumnView(36, cellView);
sheet.setColumnView(37, cellView);
sheet.setColumnView(38, cellView);
sheet.getSettings().setDefaultColumnWidth(22);//设置默认宽度22px
sheet.addCell(new Label(0, 0, "生单时间", wcf2));
sheet.addCell(new Label(1, 0, "出票时间", wcf2));
sheet.addCell(new Label(2, 0, "订单号", wcf2));
sheet.addCell(new Label(3, 0, "销售渠道", wcf2));
sheet.addCell(new Label(4, 0, "销售店铺", wcf2));
sheet.addCell(new Label(5, 0, "航线", wcf2));
sheet.addCell(new Label(6, 0, "起飞时间", wcf2));
sheet.addCell(new Label(7, 0, "航班号", wcf2));
sheet.addCell(new Label(8, 0, "行程类型", wcf2));
sheet.addCell(new Label(9, 0, "票号类型", wcf2));
sheet.addCell(new Label(10, 0, "编码", wcf2));
sheet.addCell(new Label(11, 0, "人数", wcf2));
sheet.addCell(new Label(12, 0, "销售—金额", wcf2));
sheet.addCell(new Label(13, 0, "行李总金额", wcf2));
sheet.addCell(new Label(14, 0, "采购积分", wcf2));
sheet.addCell(new Label(15, 0, "采购币种", wcf2));
sheet.addCell(new Label(16, 0, "采购金额(外)", wcf2));
sheet.addCell(new Label(17, 0, "采购金额(本)", wcf2));
sheet.addCell(new Label(18, 0, "支付明细(外)", wcf2));
sheet.addCell(new Label(19, 0, "支付明细(本)", wcf2));
sheet.addCell(new Label(20, 0, "支付方式", wcf2));
sheet.addCell(new Label(21, 0, "卡号", wcf2));
sheet.addCell(new Label(22, 0, "利润", wcf2));
sheet.addCell(new Label(23, 0, "交易流水号", wcf2));
sheet.addCell(new Label(24, 0, "辅营订单号", wcf2));
sheet.addCell(new Label(25, 0, "采购渠道", wcf2));
sheet.addCell(new Label(26, 0, "采购账户", wcf2));
sheet.addCell(new Label(27, 0, "采购订单号", wcf2));
sheet.addCell(new Label(28, 0, "政策类型", wcf2));
sheet.addCell(new Label(29, 0, "政策编码", wcf2));
sheet.addCell(new Label(30, 0, "订单状态", wcf2));
sheet.addCell(new Label(31, 0, "出票人", wcf2));
sheet.addCell(new Label(32, 0, "采购时间", wcf2));
sheet.addCell(new Label(33, 0, "附加产品", wcf2));
sheet.addCell(new Label(34, 0, "附加产品规格", wcf2));
sheet.addCell(new Label(35, 0, "备注", wcf2));
sheet.addCell(new Label(36, 0, "财务-回款状态", wcf2));
sheet.addCell(new Label(37, 0, "财务-实际支出金额", wcf2));
sheet.addCell(new Label(38, 0, "财务-补差", wcf2));
sheet.addCell(new Label(39, 0, "财务-外币调整日期", wcf2));
//遍历采购信息
int num = 1;//当前行
int count_transDetail = 0;
int count_saleOrder = 0;
String orderIdStr = "";
for (int k = 0; k < purchArr.size(); k++) {
int mergeNum = 0;//合并行数
Double profit = 0.0;//利润
JSONObject purchObj = purchArr.getJSONObject(k);
Integer deleted = purchObj.getInteger("deleted");
if (deleted != 0) {//已作废的采购信息跳过
continue;
}
String purchaseId = purchObj.getString("purchaseId");//采购id
String saleOrderId = purchObj.getString("orderId");//销售id
if ("".equals(saleOrderId) || saleOrderId == null) continue;
String flightInfoIds = purchObj.getString("flightInfoIds");//航班id
String passengerIds = purchObj.getString("passengerIds");//乘客id
int flightSize = flightInfoIds.replace(",", "").length() / 36;//行程段数
int passengerSize = passengerIds.replace(",", "").length() / 36;//乘客人量
String route = purchObj.getString("routeType");
String orderTripType = "";//订单行程类型
String routeType = convertRouteType(route);//采购行程类型
String ticketType = convertRouteType(route);//票号类型
String points = purchObj.getString("points");//使用积分
String localCurrency = purchObj.getString("localCurrencyCode");//采购币种
String currency = purchObj.getString("currencyCode");//采购币种
String localTotalPrice = purchObj.getString("localTotalPrice");//外币金额
String purchaseTotalPrice = purchObj.getString("purchaseTotalPrice");//本币金额
String orderValuedAddedOrderNo = purchObj.getString("orderValuedAddedOrderNo");//增值服务单号
String purchaseChannel = purchObj.getString("purchaseChannel");//采购渠道
String purchaseAccount = purchObj.getString("purchaseAccount");//采购账户
String purchaseOrderNo = purchObj.getString("purchaseOrderNo");//采购订单号
String purchaseOperator = purchObj.getString("purchaseOperator");//出票人
String purchaseTime = purchObj.getString("purchaseTime");//出票时间
String remarkText = purchObj.getString("remarkText");//备注信息
String trueName = "";
if (userlist != null && userlist.size() > 0) {
for (int tName = 0; tName < userlist.size(); tName++) {
if (userlist.get(tName).getUserID().equals(purchaseOperator)) {
trueName = userlist.get(tName).getUsername();//出票人真名
break;
}
}
}
String orderType = purchObj.getString("orderType");
orderType = convertOrderType(orderType);
String owPolicyType = "";//政策类型
String rtPolicyType = "";//政策类型(返程)
String owPolicyCode = "";
String rtPolicyCode = "";
List<String> policySourceArr = new ArrayList<String>();
sheet.addCell(new Number(14, num, Double.parseDouble(points), wcf2));
sheet.addCell(new Label(15, num, localCurrency, wcf2));
sheet.addCell(new Label(24, num, orderValuedAddedOrderNo, wcf2));
sheet.addCell(new Label(25, num, purchaseChannel, wcf2));
sheet.addCell(new Label(26, num, purchaseAccount, wcf2));
sheet.addCell(new Label(27, num, purchaseOrderNo, wcf2));
sheet.addCell(new Label(31, num, trueName, wcf2));
sheet.addCell(new Label(32, num, date_alt(purchaseTime), wcf2));
sheet.addCell(new Label(33, num, orderType, wcf2));
sheet.addCell(new Label(35, num, remarkText, wcf2));
sheet.addCell(new Number(16, num, Double.parseDouble(localTotalPrice), wcf2));
sheet.addCell(new Number(17, num, Double.parseDouble(purchaseTotalPrice), wcf2));
//反推销售单信息
count_saleOrder = 0;
Double combinOrderTotalPrice = 0.0;
String ORDERID = "";
String createTime = "";//采购时间
String ticketTime = "";//出票时间
String outerOrderNo = "";//外币订单号
Store store = new Store();//店铺信息
String flightLine = "";
String flightTime = "";
String flightNo = "";
String productType = "";//附加产品类型
String productDetail = "";//附加产品规格
String pnr = "";
String ticketNumber = "";
String status = "";
Double orderValuedPrice = 0.0;
for (int j = 0; j < orderArr.size(); j++) {
JSONObject orderObj = orderArr.getJSONObject(j);
String orderId = orderObj.getString("iD");
// if(orderArr.size() == 1){
// ORDERID = orderId;//为合适以前的报表，此变量只在匹配交易信息时匹配orderid,因为历史问题导致purhaseId字段无用
// }
if (saleOrderId.indexOf(orderId) > -1) {//匹配成功
ORDERID = orderId;
count_saleOrder++;
createTime = orderObj.getString("createTime");
createTime = date_alt(createTime);
ticketTime = orderObj.getString("ticketTime");
ticketTime = date_alt(ticketTime);
outerOrderNo = orderObj.getString("outerOrderNo");
String orderBelong = orderObj.getString("orderBelong");
if (storelist != null && storelist.size() > 0) {
for (int tName = 0; tName < storelist.size(); tName++) {
if (storelist.get(tName).getId().equals(orderBelong)) {
store = storelist.get(tName);
break;
}
}
}
String saleTotlalPrice = orderObj.getString("totalPrice");
int orderStatus = orderObj.getInteger("orderStatus");//订单状态
String policySource = orderObj.getString("policySource");//政策信息
status = convertOrderStatus(orderStatus);
sheet.addCell(new Label(0, num + count_saleOrder - 1, createTime, wcf2));
sheet.addCell(new Label(1, num + count_saleOrder - 1, ticketTime, wcf2));
sheet.addCell(new Label(2, num + count_saleOrder - 1, outerOrderNo, wcf2));
sheet.addCell(new Label(3, num + count_saleOrder - 1, store.getChannelCode(), wcf2));
sheet.addCell(new Label(4, num + count_saleOrder - 1, store.getStoreShortName(), wcf2));
sheet.addCell(new Label(30, num + count_saleOrder - 1, status, wcf2));
//政策信息
if (policySource != null && !"".equals(policySource)) {
policySourceArr = Arrays.asList(policySource.split("_", -1));
}
if (policySourceArr.size() == 2) {// 去哪儿、同城、航管等 //单程
if ("0".equals(policySourceArr.get(0))) owPolicyType = "默认";
if ("1".equals(policySourceArr.get(0))) owPolicyType = "普通";
if ("2".equals(policySourceArr.get(0))) owPolicyType = "积分";
if ("3".equals(policySourceArr.get(0))) owPolicyType = "普通促销";
owPolicyCode = policySourceArr.get(1);
sheet.addCell(new Label(28, num + count_saleOrder - 1, owPolicyType, wcf2));//转换后政策类型
sheet.addCell(new Label(29, num + count_saleOrder - 1, policySourceArr.get(1), wcf2));//转换后政策编码
}
if (policySourceArr.size() == 4) {// 淘宝-->单程或者往返 其他平台-->往返
if ("0".equals(policySourceArr.get(0))) owPolicyType = "默认";
if ("1".equals(policySourceArr.get(0))) owPolicyType = "普通";
if ("2".equals(policySourceArr.get(0))) owPolicyType = "积分";
if ("3".equals(policySourceArr.get(0))) owPolicyType = "普通促销";
if ("0".equals(policySourceArr.get(2))) rtPolicyType = "默认";
if ("1".equals(policySourceArr.get(2))) rtPolicyType = "普通";
if ("2".equals(policySourceArr.get(2))) rtPolicyType = "积分";
if ("3".equals(policySourceArr.get(2))) rtPolicyType = "普通促销";
if ("".equals(policySourceArr.get(2)) && "".equals(policySourceArr.get(3))) {
sheet.addCell(new Label(28, num + count_saleOrder - 1, owPolicyType, wcf2));//转换后政策类型
sheet.addCell(new Label(29, num + count_saleOrder - 1, policySourceArr.get(1), wcf2));//转换后政策编码
} else {
sheet.addCell(new Label(28, num + count_saleOrder - 1,
"去程:" + owPolicyType + "\r\n回程:" + rtPolicyType, wcf2));//转换后政策类型
sheet.addCell(new Label(29, num + count_saleOrder - 1,
"去程:" + policySourceArr.get(1) + "\r\n回程:" + policySourceArr.get(3), wcf2));//转换后政策编码
owPolicyCode = "去程:" + owPolicyType + "\r\n回程:" + rtPolicyType;
rtPolicyCode = "去程:" + policySourceArr.get(1) + "\r\n回程:" + policySourceArr.get(3);
}
}
if (policySourceArr.size() >= 6) {// 最新更新后政策信息
if ("0".equals(policySourceArr.get(1))) owPolicyType = "默认";
if ("1".equals(policySourceArr.get(1))) owPolicyType = "普通";
if ("2".equals(policySourceArr.get(1))) owPolicyType = "积分";
if ("3".equals(policySourceArr.get(1))) owPolicyType = "普通促销";
if ("4".equals(policySourceArr.get(1))) owPolicyType = "官网押位";
if ("0".equals(policySourceArr.get(4))) rtPolicyType = "默认";
if ("1".equals(policySourceArr.get(4))) rtPolicyType = "普通";
if ("2".equals(policySourceArr.get(4))) rtPolicyType = "积分";
if ("3".equals(policySourceArr.get(4))) rtPolicyType = "普通促销";
if ("4".equals(policySourceArr.get(4))) rtPolicyType = "官网押位";
if ("".equals(policySourceArr.get(4))) {
sheet.addCell(new Label(28, num + count_saleOrder - 1, owPolicyType, wcf2));//转换后政策类型
sheet.addCell(new Label(29, num + count_saleOrder - 1, policySourceArr.get(2), wcf2));//转换后政策编码
} else {
sheet.addCell(new Label(28, num + count_saleOrder - 1,
"去程:" + owPolicyType + "\r\n回程:" + rtPolicyType, wcf2));//转换后政策类型
sheet.addCell(new Label(29, num + count_saleOrder - 1,
"去程:" + policySourceArr.get(2) + "\r\n回程:" + policySourceArr.get(5), wcf2));//转换后政策编码
owPolicyCode = "去程:" + owPolicyType + "\r\n回程:" + rtPolicyType;
rtPolicyCode = "去程:" + policySourceArr.get(2) + "\r\n回程:" + policySourceArr.get(5);
}
}
//写入附加产品规格
JSONArray ordervalueadded_info = orderObj.getJSONArray("ordervalueadded");//附加产品信息json
if (!"".equals(ordervalueadded_info) && ordervalueadded_info != null) {
for (int x = 0; x < ordervalueadded_info.size(); x++) {
JSONObject ordervalueadded_data = ordervalueadded_info.getJSONObject(x);
productType = ordervalueadded_data.getString("productType");//产品类型
String specifications = ordervalueadded_data.getString("specifications");//产品规格
String productUnit = ordervalueadded_data.getString("productUnit");//产品单位
String quantity = ordervalueadded_data.getString("quantity");//购买数量
String price = ordervalueadded_data.getString("price");//购买价格
String tripType = ordervalueadded_data.getString("tripType");//行程类型
String passengerName = ordervalueadded_data.getString("passengerName");//乘客姓名
if ("1".equals(productType)) {
productType = "行李";
} else if ("2".equals(productType)) {
productType = "值机";
} else if ("3".equals(productType)) {
productType = "餐食";
} else if ("4".equals(productType)) {
productType = "WIFI";
}
if ("0".equals(tripType)) {
tripType = "所有";
} else if ("1".equals(tripType)) {
tripType = "单去程";
} else if ("2".equals(tripType)) {
tripType = "单回程";
}
// orderValuedPrice += Double.parseDouble(price)*Double.parseDouble(quantity);
orderValuedPrice += Double.parseDouble(price);
productDetail += ("[" + passengerName + "-规格：" + specifications + productUnit + ";数量：" + quantity + ";价格:" + price + ";行程:" + tripType + "];");
}
}
//销售金额
if (orderIdStr.indexOf(orderId) == -1) {
orderIdStr += orderId;
} else {
saleTotlalPrice = "0";
orderValuedPrice = 0.0;
}
sheet.addCell(new Label(34, num + count_saleOrder - 1, productDetail, wcf2));
sheet.addCell(new Number(13, num + count_saleOrder - 1, orderValuedPrice, wcf2));
sheet.addCell(new Number(12, num + count_saleOrder - 1, Double.parseDouble(saleTotlalPrice), wcf2));
combinOrderTotalPrice += Double.parseDouble(saleTotlalPrice);
//航班信息
JSONArray flightArr = orderObj.getJSONArray("orderflightinfo");
flightLine = "";
flightTime = "";
flightNo = "";
if (flightArr.size() == 1) orderTripType = "单程";
if (flightArr.size() == 2) orderTripType = "往返";
if (flightArr.size() == 3) orderTripType = "转机";
for (int fli = 0; fli < flightArr.size(); fli++) {
JSONObject flightObject = flightArr.getJSONObject(fli);
String flightId = flightObject.getString("iD");
if (flightSize == 1) {
if (flightInfoIds.indexOf(flightId) > -1) {
flightLine = flightObject.getString("org") + "-" + flightObject.getString("dst");
flightTime = flightObject.getString("flightTime");
flightNo = flightObject.getString("flightNo");
}
} else {
if (flightInfoIds.indexOf(flightId) > -1) {
flightLine += flightObject.getString("org") + "-" + flightObject.getString("dst") + "\r\n";
flightTime += flightObject.getString("flightTime");
flightNo += flightObject.getString("flightNo");
}
}
}
sheet.addCell(new Label(5, num + count_saleOrder - 1, flightLine, wcf2));
sheet.addCell(new Label(6, num + count_saleOrder - 1, date_alt(String.valueOf(flightTime)), wcf2));
sheet.addCell(new Label(7, num + count_saleOrder - 1, flightNo, wcf2));
sheet.addCell(new Label(8, num + count_saleOrder - 1, orderTripType, wcf2));
sheet.addCell(new Label(9, num + count_saleOrder - 1, ticketType, wcf2));
//乘客信息
JSONArray passengerArr = orderObj.getJSONArray("orderpassenger");
for (int pass = 0; pass < passengerArr.size(); pass++) {
JSONObject passengerObj = passengerArr.getJSONObject(pass);
String passengerId = passengerObj.getString("iD");
if (passengerIds.indexOf(passengerId) > -1) {
pnr = passengerObj.getString("pnr");
ticketNumber = passengerObj.getString("ticketNumber");
}
}
if (pnr.indexOf("/") > -1) {
if ("去程".equals(routeType) || "往返".equals(routeType)) {
pnr = pnr.substring(0, pnr.indexOf("/"));
} else {
pnr = pnr.substring(pnr.indexOf("/") + 1);
}
}
sheet.addCell(new Label(10, num, pnr, wcf2));
sheet.addCell(new Number(11, num, passengerSize, wcf2));
}
}
//写入利润
profit = combinOrderTotalPrice - Double.parseDouble(purchaseTotalPrice) + orderValuedPrice;
sheet.addCell(new Number(22, num, profit, wcf2));
//正推交易流水
count_transDetail = 0;
String purchaseId_trans = "";
String orderId_trans = "";
String accountId = "";
String cardNumber = "";
String transactionNumber = "";
String localPrice = "";
String rmbPrice = "";
if (transArr.size() > 0) {
for (int m = 0; m < transArr.size(); m++) {
JSONObject transObj = transArr.getJSONObject(m);
String transId = transObj.getString("id");
purchaseId_trans = transObj.getString("purchaseId");
orderId_trans = transObj.getString("orderId");
accountId = transObj.getString("accountId");
cardNumber = transObj.getString("cardNumber");
transactionNumber = transObj.getString("transactionNumber");
localPrice = transObj.getString("localPrice");
rmbPrice = transObj.getString("rmbPrice");
if (purchaseId.equals(purchaseId_trans)) {
count_transDetail++;
sheet.addCell(new Label(20, num + count_transDetail - 1, accountId, wcf2));
sheet.addCell(new Label(21, num + count_transDetail - 1, cardNumber, wcf2));
sheet.addCell(new Label(23, num + count_transDetail - 1, transactionNumber, wcf2));
sheet.addCell(new Number(18, num + count_transDetail - 1, Double.parseDouble(localPrice), wcf2));
sheet.addCell(new Number(19, num + count_transDetail - 1, Double.parseDouble(rmbPrice), wcf2));
} else {
//以前的报表导出的订单
for (int ord = 0; ord < orderArr.size(); ord++) {
JSONObject orderInfo = orderArr.getJSONObject(ord);
JSONArray transcationDetail_old = orderInfo.getJSONArray("transactiondetail_old");
if (transcationDetail_old != null && !"".equals(transcationDetail_old)) {
for (int tra = 0; tra < transcationDetail_old.size(); tra++) {
JSONObject traObj = transcationDetail_old.getJSONObject(tra);
orderId_trans = traObj.getString("orderId");
accountId = traObj.getString("accountId");
cardNumber = traObj.getString("cardNumber");
transactionNumber = traObj.getString("transactionNumber");
localPrice = traObj.getString("localPrice");
rmbPrice = traObj.getString("rmbPrice");
if (ORDERID.equals(orderId_trans)) {
count_transDetail++;
sheet.addCell(new Label(20, num + count_transDetail - 1, accountId, wcf2));
sheet.addCell(new Label(21, num + count_transDetail - 1, cardNumber, wcf2));
sheet.addCell(new Label(23, num + count_transDetail - 1, transactionNumber, wcf2));
sheet.addCell(new Number(18, num + count_transDetail - 1, Double.parseDouble(localPrice), wcf2));
sheet.addCell(new Number(19, num + count_transDetail - 1, Double.parseDouble(rmbPrice), wcf2));
}
}
}
}
}
}
} else {
//以前的报表导出的订单
for (int ord = 0; ord < orderArr.size(); ord++) {
JSONObject orderInfo = orderArr.getJSONObject(ord);
JSONArray transcationDetail_old = orderInfo.getJSONArray("transactiondetail_old");
if (transcationDetail_old != null && !"".equals(transcationDetail_old)) {
if (transcationDetail_old.size() > 0) {
for (int tra = 0; tra < transcationDetail_old.size(); tra++) {
JSONObject traObj = transcationDetail_old.getJSONObject(tra);
orderId_trans = traObj.getString("orderId");
accountId = traObj.getString("accountId");
cardNumber = traObj.getString("cardNumber");
transactionNumber = traObj.getString("transactionNumber");
localPrice = traObj.getString("localPrice");
rmbPrice = traObj.getString("rmbPrice");
if (ORDERID.equals(orderId_trans)) {
count_transDetail++;
sheet.addCell(new Label(20, num + count_transDetail - 1, accountId, wcf2));
sheet.addCell(new Label(21, num + count_transDetail - 1, cardNumber, wcf2));
sheet.addCell(new Label(23, num + count_transDetail - 1, transactionNumber, wcf2));
sheet.addCell(new Number(18, num + count_transDetail - 1, Double.parseDouble(localPrice), wcf2));
sheet.addCell(new Number(19, num + count_transDetail - 1, Double.parseDouble(rmbPrice), wcf2));
break;
}
}
}
}
}
}
int D_value = 0;
D_value = Math.abs(count_saleOrder - count_transDetail);
mergeNum = count_saleOrder > count_transDetail ? count_saleOrder : count_transDetail;
//销售单对支付信息 一对多，多余的行填充重复的销售单信息
if (count_saleOrder == 1 && count_transDetail > 1) {
for (int repeat = 0; repeat < (count_transDetail - count_saleOrder); repeat++) {
sheet.addCell(new Label(0, num + repeat + 1, createTime, wcf2));
sheet.addCell(new Label(1, num + repeat + 1, ticketTime, wcf2));
sheet.addCell(new Label(2, num + repeat + 1, outerOrderNo, wcf2));
sheet.addCell(new Label(3, num + repeat + 1, store.getChannelCode(), wcf2));
sheet.addCell(new Label(4, num + repeat + 1, store.getStoreShortName(), wcf2));
sheet.addCell(new Label(5, num + repeat + 1, flightLine, wcf2));
sheet.addCell(new Label(6, num + repeat + 1, date_alt(String.valueOf(flightTime)), wcf2));
sheet.addCell(new Label(7, num + repeat + 1, flightNo, wcf2));
sheet.addCell(new Label(8, num + repeat + 1, orderTripType, wcf2));
sheet.addCell(new Label(9, num + repeat + 1, ticketType, wcf2));
sheet.addCell(new Label(10, num + repeat + 1, pnr, wcf2));
// sheet.addCell(new Label(27,num + repeat +1 ,owPolicyCode,wcf2));
// sheet.addCell(new Label(28,num + repeat +1 ,rtPolicyCode,wcf2));
// sheet.addCell(new Label(29,num + repeat +1 ,status,wcf2));
// sheet.addCell(new Label(30,num + repeat +1 ,trueName,wcf2));
}
sheet.mergeCells(12, num, 12, num + mergeNum - 1);//合并订单金额
}
//合并采购信息
sheet.mergeCells(11, num, 11, num + mergeNum - 1);
sheet.mergeCells(14, num, 14, num + mergeNum - 1);
sheet.mergeCells(15, num, 15, num + mergeNum - 1);
sheet.mergeCells(16, num, 16, num + mergeNum - 1);
sheet.mergeCells(17, num, 17, num + mergeNum - 1);
sheet.mergeCells(22, num, 22, num + mergeNum - 1);
sheet.mergeCells(24, num, 24, num + mergeNum - 1);
sheet.mergeCells(25, num, 25, num + mergeNum - 1);
sheet.mergeCells(26, num, 26, num + mergeNum - 1);
sheet.mergeCells(27, num, 27, num + mergeNum - 1);
//sheet.mergeCells(31,num,31,num+mergeNum-1);
sheet.mergeCells(33, num, 33, num + mergeNum - 1);
sheet.mergeCells(35, num, 35, num + mergeNum - 1);
//合并销售单信息
if (count_saleOrder < count_transDetail && D_value > 0) {
// sheet.mergeCells(0,num+count_saleOrder-1,0,num+D_value);
// sheet.mergeCells(1,num+count_saleOrder-1,1,num+D_value);
// sheet.mergeCells(2,num+count_saleOrder-1,2,num+D_value);
// sheet.mergeCells(3,num+count_saleOrder-1,3,num+D_value);
// sheet.mergeCells(4,num+count_saleOrder-1,4,num+D_value);
// sheet.mergeCells(5,num+count_saleOrder-1,5,num+D_value);
// sheet.mergeCells(6,num+count_saleOrder-1,6,num+D_value);
// sheet.mergeCells(7,num+count_saleOrder-1,7,num+D_value);
// sheet.mergeCells(8,num+count_saleOrder-1,8,num+D_value);
// sheet.mergeCells(9,num+count_saleOrder-1,9,num+D_value);
// sheet.mergeCells(12,num+count_saleOrder-1,12,num+D_value);
// sheet.mergeCells(30,num+count_saleOrder-1,29,num+D_value);
}
num += mergeNum;
}
logger.info("生成销售明细报表成功！");
//生成完毕后，将响应结果存入消息队列
// Response response = new Response();
// response.setData(file);
// response.setStatus(Response.Status.SUCCEED);
// response.setErrorMessage(requestId);
// String responseUrl = overseasCheetahUrl + "receive/saleReportResponse.do";
// MessageQueueController.setResponseMessageQueue(responseUrl,response);
} catch (Exception e) {
e.printStackTrace();
logger.error("导出Execl异常", e);
} finally {
workbook.write();
workbook.close();
}
return file;
}
/**
* 将毫秒数转化为日期格式
*
* @param timeMs 传入毫秒数
* @return
*/
public String date_s(String timeMs) {
// long time=Long.parseLong(timeMs);
// Date date=new Date(time);
// SimpleDateFormat sdf=new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
// return sdf.format(date).toString();
return timeMs;
}
public String date_alt(String timeMs) {
try {
long time = Long.parseLong(timeMs);
Date date = new Date(time);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
return sdf.format(date);
} catch (Exception e) {
}
return timeMs;
}
public String OperName(String userId) {
String TrueName = "";
List<User> list = new ArrayList<>();
for (int n = 0; n < list.size(); n++) {
if (list.get(n).getUserID().equals(userId)) {
TrueName = list.get(n).getName();
}
}
return TrueName;
}
//订单状态转换
public String convertOrderStatus(int orderStatus) {
String status = "";
switch (orderStatus) {
case 0:
status = "未支付";
break;
case 2:
status = "等待出票";
break;
case 3:
status = "出票中";
break;
case 4:
status = "已出票";
break;
case 5:
status = "出票失败";
break;
default:
status = "";
break;
}
return status;
}
public String convertRouteType(String key) {
switch (key) {
case "OW":
key = "去程";
break;
case "RT":
key = "回程";
break;
case "OWRT":
key = "往返";
break;
default:
key = "";
break;
}
return key;
}
public String convertTripType(String key) {
switch (key) {
case "OW":
key = "单程";
break;
case "RT":
key = "单程";
break;
case "OWRT":
key = "往返";
break;
default:
key = "";
break;
}
return key;
}
public String convertOrderType(String key) {
switch (key) {
case "ticket":
key = "";
break;
case "luggage":
key = "行李";
break;
case "food":
key = "餐食";
break;
case "checkin":
key = "值机";
break;
default:
key = "";
break;
}
return key;
}
}