package com.qhyf.app.bl.service;
import java.awt.Color;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.google.common.io.Files;
import com.jfinal.aop.Before;
import com.jfinal.core.PageInfo;
import com.jfinal.plugin.activerecord.tx.Tx;
import com.qhyf.app.bl.BlConstant;
import com.qhyf.app.bl.base.common.EnvironmentUtils;
import com.qhyf.app.bl.base.controller.QhyfController;
import com.qhyf.app.bl.base.service.QhyfAbstractService;
import com.qhyf.app.bl.base.service.QhyfService;
import com.qhyf.app.bl.base.util.AlertUtils;
import com.qhyf.app.bl.base.util.StringTools;
import com.qhyf.app.bl.base.util.SysFileUtils;
import com.qhyf.app.bl.lc.service.QhyfGetLcAttachmentService;
import com.qhyf.app.bl.lc.service.QhyfToLCSendCheckStateService;
import com.qhyf.app.bl.model.BizLcPaymentInfo;
import com.qhyf.app.bl.model.BizLcPaymentInvoiceInfo;
import club.newepoch.interfaces.nio.IFileReader;
import club.newepoch.interfaces.nio.IInputStreamCallback;
import club.newepoch.iweb.model.SysFile;
import club.newepoch.nio.exception.NenioCompressionException;
import club.newepoch.nio.exception.NenioExportException;
import club.newepoch.nio.impl.aliyun.AliOssReaderImpl;
import club.newepoch.persistent.db.common.Db;
import club.newepoch.persistent.db.common.Page;
import club.newepoch.persistent.db.exception.ActiveRecordException;
import club.newepoch.persistent.db.pojo.Record;
import club.newepoch.pojo.nio.FileInfo;
import club.newepoch.utils.AssertUtils;
import club.newepoch.utils.NioUtils;
import club.newepoch.utils.StringUtils;
public class LcPaymentOrderExportService extends QhyfService {
/**
* 绿城付款单导出初始化
*
* @param ctl
* @return
* @throws ActiveRecordException
*/
public Page <Record> pagelist(QhyfController ctl) throws ActiveRecordException {
PageInfo pageInfo = ctl.getPageInfo();
AssertUtils.notNull(pageInfo, "分页信息不能为空");
int pageNumber = pageInfo.getPageIndex();
int pageSize = pageInfo.getPageSize();
// 分页查询
Page <Record> lcPayInfoPage = Db.paginate(pageNumber, pageSize, selectSql, fromSql(ctl));
return lcPayInfoPage;
}
/**
* 主画面查询SELECT SQL构造
*/
private String selectSql =
"SELECT " +
" CAST(@i :=@i + 1 AS UNSIGNED) AS number, " +
" x.uuid, " +
" x.payment_code, " +
" x.supplier_name, " +
" x.receive_money, " +
" x.is_export_flag, " +
" CONVERT(x.export_time, char) AS export_time, " +
" x.payer, " +
" x.region_name, " +
" x.user_name, " +
" x.marketer_id , " +
" x.region_id , " +
" x.payment_id , " +
" x.contract_type , " +
" x.supplier_account , " +
" x.contract_name , " +
" x.receiver_name , " +
" x.contract_code , " +
" x.supplier_account_bank " ;
/**
* 主画面查询from SQL构造
* @param ctl
* @throws ActiveRecordException
*/
public String fromSql(QhyfController ctl) throws ActiveRecordException {
// 初始化对象
QhyfService qhyhfService = new QhyfService();
String where = "";
// 页面排序
String order = ctl.getOrderString(ctl.getPageInfo());
if(!order.contains("receive_money")) {
order = " ORDER BY x.payment_id ";
}
// 供应商名称查询
String LcSupplier = ctl.getPara("LcSupplier");
if (StringUtils.notBlank(LcSupplier)) {
where = " where x.supplier_name like '%" + qhyhfService.strEscape(LcSupplier) + "%' ";
}
// 是否已导出查询
String isExportFlag = ctl.getPara("isExportFlag");
if (StringUtils.notBlank(isExportFlag)) {
if (isExportFlag.equals("1")){
isExportFlag = "已导出";
if (where.equals("")){
where = "where x.is_export_flag = '" + isExportFlag + "' ";
}else {
where += " AND x.is_export_flag = '" + isExportFlag + "' ";
}
}else if (isExportFlag.equals("0")){
isExportFlag = "未导出";
if (where.equals("")){
where = "where x.is_export_flag = '" + isExportFlag + "' ";
}else {
where += " AND x.is_export_flag = '" + isExportFlag + "' ";
}
}
}
// 项目公司查询
String payer = ctl.getPara("projectCompany");
if (StringUtils.notBlank(payer)) {
if (where.equals("")){
where = "where x.payer like '%" + qhyhfService.strEscape(payer) + "%' ";
}else {
where += " AND x.payer like '%" + qhyhfService.strEscape(payer) + "%' ";
}
}
// 区域查询
String regionId = ctl.getPara("regionId");
if (StringUtils.notBlank(regionId)) {
if (where.equals("")){
where = "where x.region_id = '" + regionId + "' ";
}else {
where += " AND x.region_id = '" + regionId + "' ";
}
}
// 区域专员查询
String userNameId = ctl.getPara("userId");
if (StringUtils.notBlank(userNameId)) {
if (where.equals("")){
where = "where x.marketer_id like '%" + userNameId + "%' ";
}else {
where += " AND x.marketer_id like '%" + userNameId + "%' ";
}
}
// fromSql构造
String fromSql =
"FROM " +
"( " +
" SELECT DISTINCT " +
" a.uuid, " +
" a.payment_code, " +
" a.supplier_name, " +
" a.receive_money, " +
" IF ( is_export_flag = 0, '未导出', '已导出') AS is_export_flag, " +
" a.export_time, " +
" b.payer, " +
" d.region_name, " +
" GROUP_CONCAT(DISTINCT f.user_name) AS user_name, " +
" e.marketer_id, " +
" d.uuid as region_id , " +
" a.payment_id , " +
" a.contract_type,"+
" a.supplier_account, " +
" a.contract_name, " +
" a.receiver_name, " +
" a.contract_code, " +
" a.supplier_account_bank " +
" FROM " +
" biz_lc_payment_info a, " +
" biz_lc_payment_invoice_info b " +
" LEFT JOIN biz_item_company c ON b.payer = c.item_company_name " +
" AND c.sys_status = 1 " +
" LEFT JOIN biz_item_region d ON c.region_id = d.uuid " +
" AND d.sys_status = 1 " +
" LEFT JOIN biz_link_marketer_region e ON e.region_id = d.uuid " +
" AND d.sys_status = 1 " +
" LEFT JOIN sys_user f ON e.marketer_id LIKE CONCAT('%', f.uuid, '%') " +
" AND f.sys_status = 1 " +
" WHERE " +
" a.payment_id = b.payment_id " +
" AND a.sys_status = 1 " +
" AND b.sys_status = 1 " +
" GROUP BY " +
" a.uuid " +
") x, " +
"(SELECT @i := 0) AS y " ;
return fromSql + where + order;
}
/**
* 绿城付款单导出详情
*
* @param ctl
* @return
* @throws ActiveRecordException
*/
public Record seedetails(QhyfController ctl) throws ActiveRecordException {
String uuid = ctl.getPara("uuid");
String detailssql = "" +
" SELECT " +
" contract_code, " +
" contract_name, " +
" if(contract_type= 1,'工程类','贸易类') AS contract_type, " +
" payment_code, " +
" payment_theme, " +
" payment_method , " +
" payment_channel, " +
" currency, " +
" receive_money, " +
" supplier_name, " +
" supplier_account_bank, " +
" supplier_account, " +
" receiver_name, " +
" create_time " +
" FROM " +
" biz_lc_payment_info " +
" WHERE " +
" sys_status = 1 " +
"AND uuid = ? ";
Record Details = Db.findFirst(detailssql, uuid);
return Details;
}
/**
* 绿城付款单导出详情发票
*
* @param ctl
* @return
* @throws ActiveRecordException
*/
public Page <Record> invoice(QhyfController ctl) throws ActiveRecordException {
PageInfo pageInfo = ctl.getPageInfo();
int pageNumber = pageInfo.getPageIndex();
int pageSize = pageInfo.getPageSize();
String uuid = ctl.getPara("uuid");
String invoicesql = "" +
" SELECT " +
" b.invoice_no, " +
" b.invoice_code, " +
" b.billing_date, " +
" b.money, " +
" b.tax_rate, " +
" b.tax_free_money, " +
" b.can_assigment_money ";
String invoicefrom = String.format("" +
" FROM " +
" %s a , " +
" %s b " +
" WHERE " +
" a.sys_status =1 " +
" AND b.sys_status =1 " +
" AND a.payment_id = b.payment_id " +
" AND a.uuid = ?" +
" ORDER BY " +
" b.invoice_no ASC ",
BizLcPaymentInfo.dao.getTable().getName(),
BizLcPaymentInvoiceInfo.dao.getTable().getName());
Page <Record> invoiceInfo = Db.paginate(pageNumber, pageSize, invoicesql, invoicefrom, uuid);
return invoiceInfo;
}
/**
* 绿城付款单导出详情发票总计
*
* @param ctl
* @return
*/
public Record total(QhyfController ctl) throws ActiveRecordException {
String uuid = ctl.getPara("uuid");
String totalsql = "" +
" SELECT " +
" sum(b.money) as totalInvoiceAmount " +
" FROM " +
" biz_lc_payment_info a, " +
" biz_lc_payment_invoice_info b " +
" WHERE " +
" a.sys_status = 1 " +
" AND b.sys_status = 1 " +
" AND a.payment_id = b.payment_id " +
" AND a.uuid = ? ";
Record totalinfo = Db.findFirst(totalsql, uuid);
return totalinfo;
}
/**
* 导出付款单
*
* @param ctl
* @return
*/
@Before(Tx.class)
public String exportlcpayment(QhyfController ctl) throws ActiveRecordException, NenioExportException, NenioCompressionException, IOException {
// 查询列表付款单信息
List <Record> PayInfoList = Db.find(selectSql+ fromSql(ctl));
if (PayInfoList.size() == 0) {
AssertUtils.isTrue(false, AlertUtils.getErrMsg("ME499E008"));
}
List <String> uuids = new ArrayList <String>();
// 将发票的中金额，设置对应的付款单里面去
for (Record payInfo: PayInfoList){
// 获取该笔付款单的uuid
String uuid=payInfo.getStr("uuid");
// 通过该笔的付款单的uuid,来查询该笔付款单金额的总钱 、
String sql =
"SELECT " +
" a.uuid, " +
" SUM(b.money) AS invoice_money " +
"FROM " +
" biz_lc_payment_info a, " +
" biz_lc_payment_invoice_info b " +
"WHERE " +
" a.payment_id = b.payment_id " +
"AND a.sys_status = 1 " +
"AND b.sys_status = 1 " +
"AND a.uuid = ? " ;
Record moneyRecord= Db.findFirst(sql,uuid);
// 设置进该笔付款单里面
payInfo.set("invoiceMoney",moneyRecord.getStr("invoiceMoney"));
// 设置导出付款单UUID
uuids.add(payInfo.get("uuid"));
// 调用发票说明共通
String lcInvoiceDescription = createInvoiceDescription(payInfo.get("paymentId"));
// 设置发票说明
payInfo.set("lcInvoiceDescription", lcInvoiceDescription);
}
// 获取付款单ID
String paymentIds = "'" + PayInfoList.get(0).get("paymentId").toString()+ "'" ;
for (int i = 1; i < PayInfoList.size(); i++) {
paymentIds += ",'" + PayInfoList.get(i).get("paymentId").toString()+ "'" ;
}
// 查询付款单发票信息SQL
String selectPayInvoice = String.format(
" SELECT" +
" b.invoice_no," +
" b.billing_date," +
" b.money ," +
" b.payment_id" +
" FROM " +
" biz_lc_payment_invoice_info b " +
" WHERE b.payment_id IN (" + paymentIds +
" ) AND b.sys_status = 1" +
" ORDER BY b.payment_id,b.invoice_no ");
// 定义付款单发票信息列表
List <Record> PayInvoiceList = Db.find(selectPayInvoice);
if (PayInfoList.size() == 0) {
AssertUtils.isTrue(false, AlertUtils.getErrMsg("ME499E009"));
}
// 设置发票序号
for (Record payInfo: PayInfoList){
// 付款单ID
String paymentId = payInfo.get("paymentId").toString();
for (Record payInvoice : PayInvoiceList) {
if(paymentId.equals(payInvoice.getStr("paymentId"))) {
payInvoice.set("number",payInfo.get("number"));
}
}
}
// 设置导出时间
SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String exportTime = sd.format(new Date());
// 创建表头
XSSFWorkbook workbook = new XSSFWorkbook();
String savePath = EnvironmentUtils.me().getEnv().getGeneratePath();
File save = new File(savePath);
if (!save.exists() && !save.isDirectory()) {
save.mkdirs();
}
String fileName = "绿城付款单.xlsx";
savePath = savePath + File.separator + fileName;
FileOutputStream fout = new FileOutputStream(savePath);
// 创建检测结果sheet
XSSFSheet sheetPay = workbook.createSheet("付款通知书");
XSSFSheet sheetPayInvoice = workbook.createSheet("发票信息");
sheetPay.createFreezePane(0, 1, 0, 1);
sheetPayInvoice.createFreezePane(0, 1, 0, 1);
// 设置付款单信息筛选
CellRangeAddress payChoose = CellRangeAddress.valueOf("B1:V1");
sheetPay.setAutoFilter(payChoose);
// 设置付款单发票信息筛选
CellRangeAddress payInvoiceChoose = CellRangeAddress.valueOf("A1:G1");
sheetPayInvoice.setAutoFilter(payInvoiceChoose);
XSSFRow headPayRow = sheetPay.createRow(0);
XSSFRow headPayInvoiceRow = sheetPayInvoice.createRow(0);
// 设置付款单sheet行高
sheetPay.setDefaultRowHeightInPoints(3 * 10);
// 设置付款单发票sheet行高
sheetPayInvoice.setDefaultRowHeightInPoints(2 * 10);
// 定义付款单sheet表头
String[] headPayArr = {"序号", "应收账款金额", "账款到期日", "项目公司名称", "供应商名称", "供应商开户行", "供应商账号", "合同名称", "合同编号",
"账号名称", "付款确认书编号", "内引", "核心企业", "金融机构", "签约主体", "基础合同类别", "期数", "发行日", "项目名称", "发票说明", "绿城标识UUID",
"区域","区域专员","发票金额","付款申请编码","导出时间"};
// 定义付款单发票sheet表头
String[] headPayInvoiceArr = {"批次", "序号", "发票号码", "票据名称", "开票日期", "发票金额", "应收账款金额"};
// 单元格
XSSFCell headPayCell = null;
XSSFCell headPayInvoiceCell = null;
XSSFCellStyle style = workbook.createCellStyle();
// 表头样式设置
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
// 设置背景色
style.setFillForegroundColor(new XSSFColor(new Color(216, 216, 216)));
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
// 设置表头字体
XSSFFont headFont = workbook.createFont();
// 字体样式
headFont.setFontName("宋体");
// 字体大小
headFont.setFontHeightInPoints((short) 9);
// 加粗
headFont.setBold(true);
style.setFont(headFont);
// 付款单sheet列宽设置
sheetPay.setColumnWidth(0, 2 * 256);// 空
sheetPay.setColumnWidth(1, 10 * 256);// 序号
sheetPay.setColumnWidth(2, 15 * 256);// 应收账款金额
sheetPay.setColumnWidth(3, 10 * 256);// 账款到期日
sheetPay.setColumnWidth(4, 40 * 256);// 项目公司名称
sheetPay.setColumnWidth(5, 40 * 256);// 供应商名称
sheetPay.setColumnWidth(6, 40 * 256);// 供应商开户行
sheetPay.setColumnWidth(7, 30 * 256);// 供应商账号
sheetPay.setColumnWidth(8, 40 * 256);// 合同名称
sheetPay.setColumnWidth(9, 40 * 256);// 合同编号
sheetPay.setColumnWidth(10, 30 * 256);// 账号名称
sheetPay.setColumnWidth(11, 30 * 256);// 付款确认书编号
sheetPay.setColumnWidth(12, 30 * 256);// 内引
sheetPay.setColumnWidth(13, 10 * 256);// 核心企业
sheetPay.setColumnWidth(14, 10 * 256);// 金融机构
sheetPay.setColumnWidth(15, 30 * 256);// 签约主体
sheetPay.setColumnWidth(16, 15 * 256);// 基础合同类别
sheetPay.setColumnWidth(17, 10 * 256);// 期数
sheetPay.setColumnWidth(18, 10 * 256);// 发行日
sheetPay.setColumnWidth(19, 20 * 256);// 项目名称
sheetPay.setColumnWidth(20, 40 * 256);// 发票说明
sheetPay.setColumnWidth(21, 40 * 256);// 绿城标识UUID
// 项目公司、区域、区域专员、发票金额（即该付款单的发票总金额）、付款申请编码、导出时间等信息
// 新增的需求
sheetPay.setColumnWidth(22, 10 * 256);// 区域
sheetPay.setColumnWidth(23, 15 * 256);// 区域专员
sheetPay.setColumnWidth(24, 15 * 256);// 发票金额
sheetPay.setColumnWidth(25, 25 * 256);// 付款申请编码
sheetPay.setColumnWidth(26, 20 * 256);// 导出时间
// 付款发票sheet列宽设置
sheetPayInvoice.setColumnWidth(0, 10 * 256);// 批次
sheetPayInvoice.setColumnWidth(1, 10 * 256);// 序号
sheetPayInvoice.setColumnWidth(2, 10 * 256);// 发票号码
sheetPayInvoice.setColumnWidth(3, 15 * 256);// 票据名称
sheetPayInvoice.setColumnWidth(4, 13 * 256);// 开票日期
sheetPayInvoice.setColumnWidth(5, 15 * 256);// 发票金额
sheetPayInvoice.setColumnWidth(6, 15 * 256);// 应收账款金额
// 写入付款通知书表头
for (int i = 0; i < headPayArr.length; i++) {
sheetPay.autoSizeColumn(i + 1);
headPayCell = headPayRow.createCell(i + 1);
headPayCell.setCellValue(headPayArr[i]);
headPayCell.setCellStyle(style);
}
// 写入发票信息表头
for (int i = 0; i < headPayInvoiceArr.length; i++) {
sheetPayInvoice.autoSizeColumn(i);
headPayInvoiceCell = headPayInvoiceRow.createCell(i);
headPayInvoiceCell.setCellValue(headPayInvoiceArr[i]);
headPayInvoiceCell.setCellStyle(style);
}
// 设置单元格样式
XSSFCellStyle cellStyle = workbook.createCellStyle();
// 边框
cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
// 内容换行
cellStyle.setWrapText(true);
// 垂直居中
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
// 设置单元格字体
XSSFFont font = workbook.createFont();
// 字体样式
font.setFontName("微软雅黑");
// 字体大小
font.setFontHeightInPoints((short) 9);
cellStyle.setFont(font);
// 加粗
XSSFCell cellPay = null;
XSSFRow rowPay = null;
// 查询结果不为空
for (int i = 0; i < PayInfoList.size(); i++) {
Record recordPay = PayInfoList.get(i);
// 序号
Long number = recordPay.get("number");
String orderNumber = null;
if (number < 10) {
orderNumber = "inv100" + number;
} else if (number < 100) {
orderNumber = "inv10" + number;
} else if (number < 1000) {
orderNumber = "inv1" + number;
} else {
orderNumber = "inv" + number;
}
String contractType = "";
//合同类型
if (recordPay.get("contractType").equals("1")) {
contractType = "工程类";
} else if (recordPay.get("contractType").equals("2")) {
contractType = "贸易类";
}
// 应收账款金额
Double receiveMoney = Double.parseDouble(recordPay.getBigDecimal("receiveMoney").toString());
// 供应商名称
String supplierName = recordPay.get("supplierName");
// 供应商开户行
String supplierAccountBank = recordPay.get("supplierAccountBank");
// 供应商账号
String supplierAccount = recordPay.get("supplierAccount");
// 合同名称
String contractName = recordPay.get("contractName");
// 合同编号
String contractCode = recordPay.get("contractCode");
//账号名称
String receiverName = recordPay.get("receiverName");
// 核心企业
String coreEnterpriseName = "绿城";
// 金融机构
String financialInstitutionsName = "华夏ABS";
// 签约主体
String signingBodyName = "深圳市前海一方恒融商业保理有限公司";
// 发票说明
String lcInvoiceDescription = recordPay.get("lcInvoiceDescription");
// 绿城标识UUID
String paymentId = recordPay.get("paymentId");
//新增需求
// 项目公司、区域、区域专员、发票金额（即该付款单的发票总金额）、付款申请编码、导出时间等信息
// 项目公司
String payyer = recordPay.get("payer");
// 区域
String regionName = recordPay.get("regionName");
// 区域专员
String userName = recordPay.get("userName");
// 发票金额
// Double invoiceMoney = Double.parseDouble(recordPay.getBigDecimal("invoiceMoney").toString());
Double invoiceMoney = Double.parseDouble(recordPay.get("invoiceMoney"));
// 付款申请编码
String paymentCode = recordPay.get("paymentCode");
// 导出时间
// 对各元素设值
rowPay = sheetPay.createRow(i + 1);
cellPay = rowPay.createCell(1);
cellPay.setCellValue(orderNumber);
cellPay.setCellStyle(cellStyle);
cellPay = rowPay.createCell(2);
cellPay.setCellValue(receiveMoney);
cellPay.setCellStyle(cellStyle);
cellPay = rowPay.createCell(3);
cellPay.setCellStyle(cellStyle);
//为项目公司赋值
cellPay = rowPay.createCell(4);
cellPay.setCellValue(payyer);
cellPay.setCellStyle(cellStyle);
cellPay = rowPay.createCell(5);
cellPay.setCellValue(supplierName);
cellPay.setCellStyle(cellStyle);
cellPay = rowPay.createCell(6);
cellPay.setCellValue(supplierAccountBank);
cellPay.setCellStyle(cellStyle);
cellPay = rowPay.createCell(7);
cellPay.setCellValue(supplierAccount);
cellPay.setCellStyle(cellStyle);
cellPay = rowPay.createCell(8);
cellPay.setCellValue(contractName);
cellPay.setCellStyle(cellStyle);
cellPay = rowPay.createCell(9);
cellPay.setCellValue(contractCode);
cellPay.setCellStyle(cellStyle);
cellPay = rowPay.createCell(10);
cellPay.setCellValue(receiverName);
cellPay.setCellStyle(cellStyle);
cellPay = rowPay.createCell(11);
cellPay.setCellStyle(cellStyle);
cellPay = rowPay.createCell(12);
cellPay.setCellStyle(cellStyle);
cellPay = rowPay.createCell(13);
cellPay.setCellValue(coreEnterpriseName);
cellPay.setCellStyle(cellStyle);
cellPay = rowPay.createCell(14);
cellPay.setCellValue(financialInstitutionsName);
cellPay.setCellStyle(cellStyle);
cellPay = rowPay.createCell(15);
cellPay.setCellValue(signingBodyName);
cellPay.setCellStyle(cellStyle);
cellPay = rowPay.createCell(16);
cellPay.setCellValue(contractType);
cellPay.setCellStyle(cellStyle);
cellPay = rowPay.createCell(17);
cellPay.setCellStyle(cellStyle);
cellPay = rowPay.createCell(18);
cellPay.setCellStyle(cellStyle);
cellPay = rowPay.createCell(19);
cellPay.setCellStyle(cellStyle);
cellPay = rowPay.createCell(20);
cellPay.setCellValue(lcInvoiceDescription);
cellPay.setCellStyle(cellStyle);
cellPay = rowPay.createCell(21);
cellPay.setCellValue(paymentId);
cellPay.setCellStyle(cellStyle);
// 新增需求
// 项目公司、区域、区域专员、发票金额（即该付款单的发票总金额）、付款申请编码、导出时间等信息
//区域
cellPay = rowPay.createCell(22);
cellPay.setCellValue(regionName);
cellPay.setCellStyle(cellStyle);
//区域专员
cellPay = rowPay.createCell(23);
cellPay.setCellValue(userName);
cellPay.setCellStyle(cellStyle);
//发票金额
cellPay = rowPay.createCell(24);
cellPay.setCellValue(invoiceMoney);
cellPay.setCellStyle(cellStyle);
//付款申请编码
cellPay = rowPay.createCell(25);
cellPay.setCellValue(paymentCode);
cellPay.setCellStyle(cellStyle);
//导出时间
cellPay = rowPay.createCell(26);
cellPay.setCellValue(exportTime);
cellPay.setCellStyle(cellStyle);
}
// 定义付款发票行信息
XSSFCell cellPayInvoice = null;
XSSFRow rowPayInvoice = null;
// 查询结果不为空
for (int i = 0; i < PayInvoiceList.size(); i++) {
Record recordPayInvoice = PayInvoiceList.get(i);
// 序号
Long number = recordPayInvoice.get("number");
String orderNumber = null;
if (number < 10) {
orderNumber = "inv100" + number;
} else if (number < 100) {
orderNumber = "inv10" + number;
} else if (number < 1000) {
orderNumber = "inv1" + number;
} else {
orderNumber = "inv" + number;
}
rowPayInvoice = sheetPayInvoice.createRow(i + 1);
//批次
cellPayInvoice = rowPayInvoice.createCell(0);
cellPayInvoice.setCellStyle(cellStyle);
//序号
cellPayInvoice = rowPayInvoice.createCell(1);
cellPayInvoice.setCellValue(orderNumber);
cellPayInvoice.setCellStyle(cellStyle);
//发票号码
cellPayInvoice = rowPayInvoice.createCell(2);
cellPayInvoice.setCellValue(recordPayInvoice.getStr("invoiceNo"));
cellPayInvoice.setCellStyle(cellStyle);
//发票名称
cellPayInvoice = rowPayInvoice.createCell(3);
cellPayInvoice.setCellStyle(cellStyle);
//开票日期
//datetime转换时间戳
Date billingDate = recordPayInvoice.getDate("billingDate");
String billingDateStr = "";
if (billingDate != null) {
DateFormat format = new SimpleDateFormat("yyyy-MM-dd");
billingDateStr = format.format(billingDate);
}
cellPayInvoice = rowPayInvoice.createCell(4);
cellPayInvoice.setCellValue(billingDateStr);
cellPayInvoice.setCellStyle(cellStyle);
//发票金额
cellPayInvoice = rowPayInvoice.createCell(5);
cellPayInvoice.setCellValue(Double.parseDouble(recordPayInvoice.getBigDecimal("money").toString()));
cellPayInvoice.setCellStyle(cellStyle);
//应收账款金额
cellPayInvoice = rowPayInvoice.createCell(6);
cellPayInvoice.setCellStyle(cellStyle);
}
// 保存到文件
workbook.write(fout);
fout.close();
// 保存文件信息到文件表
String fileUuid = StringUtils.randomUuid();
File file = new File(savePath);
FileInfo fileInfo = new FileInfo();
fileInfo.setFile(file);
fileInfo.setOriginalFileName(fileName);
SysFile saveFile = SysFileUtils.fileInfo2SysFile(fileInfo, null);
saveFile.set("uuid", fileUuid);
boolean isTrue = saveFile.save();
if (isTrue) {
// 保存成功
// 文件上传云端
FileInfo UpFileInfo = this.getFileInfoByUuid(fileUuid);
this.upload2cloud(UpFileInfo, true);
// 更新绿城付款单数据表对应导出状态为已导出
for (String uuid : uuids) {
BizLcPaymentInfo bizLcPaymentInfo = new BizLcPaymentInfo();
bizLcPaymentInfo.set("uuid", uuid);
bizLcPaymentInfo.set("isExportFlag", BlConstant.EXPORT_FLAG_1);
// 新加设置，对导出时间的设置
bizLcPaymentInfo.set("exportTime",exportTime);
ctl.merge(BizLcPaymentInfo.dao.getTable().getComment(), bizLcPaymentInfo);
}
return fileUuid;
} else {
return null;
}
}
/**
* 如果号码相邻的发票号码没有用“-”连接，就修改为“-”连接
*
* @param payUuid 付款单uuid
* @return
* @throws ActiveRecordException
*/
private static String createInvoiceDescription(String payUuid) throws ActiveRecordException {
// 查询发票号码
String selSql = String.format(
"SELECT " +
" invoice_no " +
"FROM " +
" biz_lc_payment_invoice_info " +
"WHERE " +
" payment_id = ? " +
"AND sys_status = 1 " +
"ORDER BY " +
" invoice_no ASC");
// db查询
List <Record> records = Db.find(selSql, payUuid);
BigDecimal b1 = null;
BigDecimal b2 = null;
if (records.size() == 0) {
// 没有发票信息，发票说明为空
return "";
} else if (records.size() == 1) {
// 只有一条发票信息
return records.get(0).get("invoiceNo");
} else if (records.size() == 2) {
// 只有两条发票信息
b1 = new BigDecimal(records.get(0).get("invoiceNo").toString());
b2 = new BigDecimal(records.get(1).get("invoiceNo").toString());
// 判断发票号码是否相邻
if (b2.compareTo(b1.add(new BigDecimal(1))) == 0) {
// 相邻
return records.get(0).get("invoiceNo").toString() + "-" + records.get(1).get("invoiceNo").toString();
} else {
// 不相邻
return records.get(0).get("invoiceNo").toString() + "," + records.get(1).get("invoiceNo").toString();
}
} else {
// 发票信息记录数大于2
String tmpRtnStr = records.get(0).get("invoiceNo");
String rtnStr = "";
for (int i = 1; i < records.size(); i++) {
b1 = new BigDecimal(records.get(i - 1).get("invoiceNo").toString());
b2 = new BigDecimal(records.get(i).get("invoiceNo").toString());
if (b2.compareTo(b1.add(new BigDecimal("1"))) == 0) {
// 当前两个发票号码相邻
} else {
// 当前两个发票号码不相邻
if (new BigDecimal(tmpRtnStr)
.compareTo(new BigDecimal(records.get(i - 1).get("invoiceNo").toString())) == 0) {
rtnStr = rtnStr + records.get(i - 1).get("invoiceNo").toString() + ",";
} else {
tmpRtnStr = tmpRtnStr + "-" + records.get(i - 1).get("invoiceNo").toString();
rtnStr = rtnStr + tmpRtnStr + ",";
}
tmpRtnStr = records.get(i).get("invoiceNo").toString();
}
if (i == records.size() - 1) {
if ((new BigDecimal(tmpRtnStr))
.compareTo(new BigDecimal(records.get(i).get("invoiceNo").toString())) == 0) {
} else {
tmpRtnStr += "-" + records.get(i).get("invoiceNo").toString();
}
rtnStr = rtnStr + tmpRtnStr + ",";
}
}
return rtnStr.substring(0, rtnStr.length() - 1);
}
}
/**
* 获取附件
*
* @param ctl
* @throws Exception
*/
@Before(Tx.class)
public int download(QhyfController ctl) throws Exception {
String annex = "";
String uuid = ctl.getPara("uuid");
String sql = "SELECT payment_id FROM biz_lc_payment_info WHERE uuid = ? and sys_status = 1";
Record only = Db.findFirst(sql, uuid);
if (only != null) {
annex = only.getStr("paymentId");
}
QhyfGetLcAttachmentService qhyfGetLcAttachmentService = new QhyfGetLcAttachmentService();
int ret = qhyfGetLcAttachmentService.PaymentListAttachmentIAnformation(ctl, annex);
return ret;
}
/**
* 附件下载
*
* @throws Exception
*/
@Before(Tx.class)
public String getziofile(QhyfController ctl) throws Exception {
FileInfo fileInfo = null;
FileInfo fileInfoAddress = null;
List <FileInfo> fileInfos = new ArrayList <FileInfo>();
//查询当前UUID的绿城唯一UUID
String uuid = ctl.getPara("uuid");
String sql = "SELECT payment_id,payment_code FROM biz_lc_payment_info WHERE uuid = ? and sys_status = 1 ";
Record lcPaymentInfo = Db.findFirst(sql, uuid);
String paymentId = lcPaymentInfo.getStr("paymentId");
String paymentCode = lcPaymentInfo.getStr("paymentCode");
//查找绿城附件ID
String filesql = " " +
" SELECT " +
" a.file_id ," +
" b.file_name " +
" FROM " +
" biz_lc_payment_file a ," +
" sys_file b " +
" WHERE " +
" payment_id = ? " +
" and a.file_id=b.uuid " +
" and b.sys_status =1 and a.sys_status = 1";
List <Record> fileinfoList = Db.find(filesql, paymentId);
if (fileinfoList == null || fileinfoList.size() <= 0) {
AssertUtils.isTrue(false, "该付款单没有可下载附件，请先获取附件");
}
// 定义打包文件的路径
DateFormat format = new java.text.SimpleDateFormat("yyyyMMddhhmmss");
String time = format.format(new Date());
String address = paymentCode + "绿城附件包_" + time + ".zip";
fileInfoAddress = new FileInfo(address);
// 调用共通方法【文件打包】
fileInfo = new FileInfo(null);
// 定义基础路径
String basePath = StringUtils.randomUuid();
//读取文件信息
for (Record fileinfo : fileinfoList) {
String fileName = fileinfo.get("fileName").toString();
fileName = fileName.substring(0, fileName.lastIndexOf("."));
// 根据文件uuid和临时目录从阿里云下载文件
FileInfo file = ctl.getService(DownloadFilesFromCloudService.class).downloadFile(fileinfo.get("fileId"), basePath, fileName);
fileInfos.add(file);
}
//压缩文件
FileInfo[] fileInfoArray = new FileInfo[fileInfos.size()];
for (int m = 0, lengthOfFile = fileInfos.size(); m < lengthOfFile; m++) {
fileInfoArray[m] = fileInfos.get(m);
}
fileInfo = ctl.getService(QhyfAbstractService.class).compressionLocation(fileInfoArray, fileInfoAddress, null, null, null, null, true);
// 获取压缩文件ID
String filePath = fileInfo.getUuid();
return filePath;
}
/**
* 获取区域的下拉框数据
* @throws Exception
*/
public List<Record> initRegion() throws Exception {
String sql ="SELECT " +
"DISTINCT c.uuid as region_id, " +
" c.region_name " +
"FROM " +
" biz_item_region c " +
"WHERE " +
" c.sys_status = 1 " +
"AND c.core_enterprise_id = 'c1008' " ;
List<Record> regionRecord = Db.find(sql);
return regionRecord ;
}
/**
* 获取区域专员的下拉框数据
* @throws Exception
*/
public List<Record> initRegionCommissioner() throws Exception {
String sql =
"SELECT DISTINCT " +
" b.uuid as user_id, " +
" b.user_name " +
"FROM " +
" biz_link_marketer_region a, " +
" sys_user b " +
"WHERE " +
" a.core_enterprise_id = 'c1008' " +
"AND a.sys_status = 1 " +
"AND b.sys_status = 1 " +
"AND a.marketer_id LIKE CONCAT('%%',b.uuid,'%%') ORDER BY a.uuid ";
List<Record> regionRecordCommissioner = Db.find(sql);
return regionRecordCommissioner ;
}
/**
* 获取：推送补资料
*
* @param ctl
* @throws Exception
*/
public boolean updateStatus(QhyfController ctl) throws Exception {
boolean isOk = false ;
String uuid = ctl.getPara("uuid");
// 付款进度状态同步接口，推送 业务状态为40001(ABS退回)、状态码为4000101（补资料）
if (StringUtils.notBlank(uuid)) {
ctl.getService(QhyfToLCSendCheckStateService.class).pushMessageStatus(ctl, uuid, BlConstant.LC_BUINNESS_STATE_01, BlConstant.FILL_MATERIAL_0101);
isOk = true;
}
return isOk ;
}
/**
* 批量下载附件
* @param ctl
* @throws Exception
*/
@Before(Tx.class)
public Map<String, Object> batchDownload(QhyfController ctl) throws Exception{
//首先获取符合查询条件的付款单
// 查询画面列表付款单信息
List <Record> payInfoList = Db.find(selectSql + fromSql(ctl));
AssertUtils.notNull(payInfoList,"所要下载的文件为空");
// 然后遍历每一笔的付款单，获取去对应的附件数据,有就有，没有就为空
List <Record> fileinfoList = null;
//空folder标志：true--空folder；false--非空foler
boolean isNullFolder = true;
//随机生成码
String randomUuid = StringUtils.randomUuid();
// 定义一个存放 文件uuid的盒子
Map<String, Object> data = new HashMap<String, Object>();
//压缩目标路径共通前缀
String targetDirCommon = EnvironmentUtils.me().getEnv().getGeneratePath()+File.separator+randomUuid+File.separator;
File parentDir = new File(targetDirCommon);
//判断文件目录是否存在
if (!parentDir.exists()) {//不存在--新建目录
parentDir.mkdirs();
}
//目标文件folder
List<FileInfo> fileInfoList = new ArrayList<FileInfo>();
for (Record lcPayInfo : payInfoList){
String paymentId = lcPayInfo.getStr("paymentId");
String paymentCode = lcPayInfo.getStr("paymentCode");
//查找绿城附件ID
String filesql = " " +
" SELECT " +
" a.file_id ," +
" b.file_name " +
" FROM " +
" biz_lc_payment_file a ," +
" sys_file b " +
" WHERE " +
" payment_id = ? " +
" and a.file_id=b.uuid " +
" and b.sys_status =1 and a.sys_status = 1";
//获取每一笔付款单的附件数
fileinfoList = Db.find(filesql, paymentId);
// 当前文件夹
String curFileDir = targetDirCommon + paymentCode + File.separator;
if(fileinfoList != null && fileinfoList.size()>0){
//读取文件信息
for (Record fileinfo : fileinfoList) {
//构造压缩文件信息：文件名、存储路径
FileInfo curFileInfo;
String curFileUuid = fileinfo.get("fileId");
// 文件名称
String fileName = fileinfo.getStr("fileName");
// 获取文件名称，除去后缀名
fileName = fileName.substring(0,fileName.lastIndexOf("."));
//从云端下载文件到指定目录
curFileInfo = this.downloadFileFromOss(curFileUuid, curFileDir, fileName);
AssertUtils.notNull(curFileInfo, AlertUtils.getErrMsgWithCode("ME441E007"));
//修改文件空folder标志
isNullFolder = false;
}
FileInfo targetFileInfoFolder = new FileInfo(curFileDir);
fileInfoList.add(targetFileInfoFolder);
}
}
// 外面最大的文件夹
//文件夹非空时
if(!isNullFolder){
// 目标文件folder （最外层的）
FileInfo targetFileInfoFolder = new FileInfo(targetDirCommon);
// 目标文件folder
FileInfo[] targetFileFolder = new FileInfo[1];
targetFileFolder[0] = targetFileInfoFolder;
// 定义打包文件的路径
DateFormat format = new SimpleDateFormat("yyyyMMddhhmmss");
String time = format.format(new Date());
String address = "绿城附件包_" + time + ".zip";
FileInfo fileInfoAddress = new FileInfo(address);
FileInfo[] strings = new FileInfo[fileInfoList.size()];
fileInfoList.toArray(strings);
//压缩
FileInfo compredPackageInfo = ctl.getService(QhyfAbstractService.class).compressionLocation(strings, fileInfoAddress, null, null, null, null, true);
// 获取压缩文件ID
String filePath = compredPackageInfo.getUuid();
//递归删除临时文件
StringTools.isTrue(this.deleteAllDirAndFile(new File(EnvironmentUtils.me().getEnv().getGeneratePath()+File.separator+randomUuid+File.separator)));
//返回压缩文件id
data.put("downloadList",filePath);
}
return data;
}
/**
* 从云端获取文件--下载到本地
* @param fileUuid
* 文件Uuid
* @param targetPath
* 下载目标路径
* @param storeFileName
* 文件名
* @return FileInfo 下载后的文件信息
* @throws Exception
*/
public FileInfo downloadFileFromOss(String fileUuid, String targetPath, String storeFileName) throws Exception {
//文件下载对象
IFileReader AliReader = new AliOssReaderImpl();
//根据文件id获取文件信息
FileInfo fileInfo = this.getFileInfo(fileUuid);
//设置文件名 = 文件名+后缀
storeFileName = storeFileName + "." +fileInfo.getType();
fileInfo.setStorePath(targetPath + storeFileName);
fileInfo.setFileName(storeFileName);
//数据库中路径
String dbStorePath = fileInfo.getStorePath();
//判断分离器,Windows环境下和Linux环境下不同
if(dbStorePath.contains("\")){
dbStorePath = dbStorePath.substring(dbStorePath.lastIndexOf("\")+1);
}else if(dbStorePath.contains("/")){
dbStorePath = dbStorePath.substring(dbStorePath.lastIndexOf("/")+1);
}
//构造路径
String absolutePathParm = String.format(
"%s",targetPath);
//最终路径
String path = club.newepoch.utils.FileUtils.getAbsolutePath(absolutePathParm, dbStorePath);
fileInfo.setStorePath(path);
//下载文件到本地
if(!club.newepoch.utils.FileUtils.exists(path)){
Files.createParentDirs(club.newepoch.utils.FileUtils.getFile(fileInfo.getStorePath()));
AliReader.read(fileInfo, NioUtils.DEFAULT_CHARSETS
,new IInputStreamCallback(){
@Override
public void executeInputStream(InputStream inputStream, FileInfo saveFileInfo) throws Exception {
// 写入到本地文件
NioUtils.write2file(fileInfo.getStorePath(), inputStream, NioUtils.DEFAULT_CHARSETS);
}
});
}
File nfile = new File(club.newepoch.utils.FileUtils.getAbsolutePath(fileInfo.getStorePath()));
fileInfo.setFile(nfile);
return fileInfo;
}
/**
* 递归删除文件及文件夹下所有文件
*
* @param dir
* 将要删除的文件目录
* @return true/false
* 删除成功与否
*/
public boolean deleteAllDirAndFile(File dir) {
if (dir.isDirectory()) {//是目录
String[] children = dir.list();
//递归删除目录中所有文件
for (int i=0; i<children.length; i++) {
boolean success = deleteAllDirAndFile(new File(dir, children[i]));
if (!success) {
return false;
}
}
}
// 目录此时为空，可以删除
return dir.delete();
}
}
