package cn.com.countrygarden.bi.main.controller.resident;

import cn.com.countrygarden.bi.main.model.resident.ResidentPaymentData;
import cn.com.countrygarden.bi.main.service.dup.impl.ExcelHelper;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.List;

/**
 * Created by chenweijiang on 2017/11/15.
 */
public class PaymentExporter {

    public void createHeader(Workbook workbook, Sheet sheet) {
        ExcelHelper helper = new ExcelHelper();
        CellStyle headerStyle = helper.createHeaderStyle(workbook);
        Row row = sheet.createRow(0);
        Row rowTwo = sheet.createRow(1);
        row.setHeight((short) (250 * 2));
        helper.createCell("序列", headerStyle, row, 0);
        helper.createCell(null, headerStyle, rowTwo, 0);
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));

        helper.createCell("项目信息", headerStyle, row, 1);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 3));
        helper.createCell("区域", headerStyle, rowTwo, 1);
        helper.createCell("项目名称", headerStyle, rowTwo, 2);
        helper.createCell("账套编码", headerStyle, rowTwo, 3);

        helper.createCell("合同编号", headerStyle, row, 4);
        helper.createCell(null, headerStyle, rowTwo, 4);
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 4, 4));

        helper.createCell("签订时间", headerStyle, row, 5);
        helper.createCell(null, headerStyle, rowTwo, 5);
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 5, 5));

        helper.createCell("合同/协议名称", headerStyle, row, 6);
        helper.createCell(null, headerStyle, rowTwo, 6);
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 6, 6));

        helper.createCell("BIP审批号", headerStyle, row, 7);
        helper.createCell(null, headerStyle, rowTwo, 7);
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 7, 7));
        helper.createCell("是否完成合同签订", headerStyle, row, 8);
        helper.createCell(null, headerStyle, rowTwo, 8);
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 8, 8));

        helper.createCell("合作方信息", headerStyle, row, 9);
        helper.createCell("单位名称", headerStyle, rowTwo, 9);
        helper.createCell("纳税人识别号", headerStyle, rowTwo, 10);
        helper.createCell("地址", headerStyle, rowTwo, 11);
        helper.createCell("电话", headerStyle, rowTwo, 12);
        helper.createCell("开户行", headerStyle, rowTwo, 13);
        helper.createCell("银行账号", headerStyle, rowTwo, 14);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 9, 14));

        helper.createCell("起始日", headerStyle, row, 15);
        helper.createCell(null, headerStyle, rowTwo, 15);
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 15, 15));

        helper.createCell("终止日", headerStyle, row, 16);
        helper.createCell(null, headerStyle, rowTwo, 16);
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 16, 16));

        helper.createCell("费用构成", headerStyle, row, 17);
        helper.createCell("固定金额", headerStyle, rowTwo, 17);
        helper.createCell("浮动金额", headerStyle, rowTwo, 18);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 17, 18));

        helper.createCell("收款方式", headerStyle, row, 19);
        helper.createCell(null, headerStyle, rowTwo, 19);
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 19, 19));

        helper.createCell("所属时间", headerStyle, row, 20);
        helper.createCell(null, headerStyle, rowTwo, 20);
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 20, 20));

        helper.createCell("收款金额", headerStyle, row, 21);
        helper.createCell("固定金额", headerStyle, rowTwo, 21);
        helper.createCell("浮动金额", headerStyle, rowTwo, 22);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 21, 22));

        helper.createCell("收款日期", headerStyle, row, 23);
        helper.createCell(null, headerStyle, rowTwo, 23);
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 23, 23));

        helper.createCell("转账凭证", headerStyle, row, 24);
        helper.createCell(null, headerStyle, rowTwo, 24);
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 24, 24));

        helper.createCell("发票号码", headerStyle, row, 25);
        helper.createCell(null, headerStyle, rowTwo, 25);
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 25, 25));

        helper.createCell("发票二维码", headerStyle, row, 26);
        helper.createCell(null, headerStyle, rowTwo, 26);
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 26, 26));

        helper.createCell("开票金额", headerStyle, row, 27);
        helper.createCell("含税金额", headerStyle, rowTwo, 27);
        helper.createCell("不含税金额", headerStyle, rowTwo, 28);
        helper.createCell("增值税", headerStyle, rowTwo, 29);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 27, 29));

        helper.createCell("合同执行情况", headerStyle, row, 30);
        helper.createCell(null, headerStyle, rowTwo, 30);
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 30, 30));

        helper.createCell("备注", headerStyle, row, 31);
        helper.createCell(null, headerStyle, rowTwo, 31);
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 31, 31));

        helper.createCell("创建人", headerStyle, row, 32);
        helper.createCell(null, headerStyle, rowTwo, 32);
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 32, 32));

        helper.createCell("更新人", headerStyle, row, 33);
        helper.createCell(null, headerStyle, rowTwo, 33);
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 33, 33));

        sheet.createFreezePane( 0, 2, 0, 2 );
    }

    public void createBody(List<ResidentPaymentData> dataList, Workbook workbook, Sheet sheet, String urlBase, String fileBase) {
        ExcelHelper helper = new ExcelHelper();
        CellStyle cellStyle = helper.createBodyStyle(workbook);
        Drawing drawing = sheet.createDrawingPatriarch();
        if (dataList.size() == 0) {
            return;
        }
        for (int i = 0; i < dataList.size(); i++) {
            int index = i + 1;
            int rowNum = i + 2;
            createRow(rowNum, index, dataList.get(i), workbook, sheet, cellStyle, helper, drawing, urlBase, fileBase);
        }
        for (int i = 1; i <= 33; i++) {
            sheet.autoSizeColumn(i);
        }
    }

    public void createRow(int rowIndex, int index, ResidentPaymentData data, Workbook workbook, Sheet sheet, CellStyle cellStyle, ExcelHelper helper, Drawing drawing, String urlBase, String fileBase) {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        Row row = sheet.createRow(rowIndex);
        row.setHeight((short) (250 * 2));
        helper.createCell(Integer.toString(index), cellStyle, row, 0);
        helper.createCell(data.getOrganName(), cellStyle, row, 1);
        helper.createCell(data.getCommName(), cellStyle, row, 2);
        helper.createCell(data.getNcSystemNumber(), cellStyle, row, 3);
        helper.createCell(data.getContractCode(), cellStyle, row, 4);
        helper.createCell(format.format(data.getSignTime()), cellStyle, row, 5);
        helper.createCell(data.getContractTypeName(), cellStyle, row, 6);

        helper.createCell(data.getBipApprovalNumber(), cellStyle, row, 7);
        helper.createCell(data.getFinalSigned() == true ? "是" : "否", cellStyle, row, 8);

        helper.createCell(data.getPartyB(), cellStyle, row, 9);
        helper.createCell(data.getTaxpayerNumber(), cellStyle, row, 10);
        helper.createCell(data.getAddress(), cellStyle, row, 11);
        helper.createCell(data.getPhone(), cellStyle, row, 12);
        helper.createCell(data.getBank(), cellStyle, row, 13);
        helper.createCell(data.getAccount(), cellStyle, row, 14);
        helper.createCell(format.format(data.getContractStartDate()), cellStyle, row, 15);
        helper.createCell(format.format(data.getContractEndDate()), cellStyle, row, 16);
        helper.createCell(data.getContractFixedAmount().toPlainString(), cellStyle, row, 17);
        helper.createCell(data.getContractFloatAmount(), cellStyle, row, 18);
        helper.createCell(data.getPaymentMethodName(), cellStyle, row, 19);
        StringBuffer sb = new StringBuffer();
        if (data.getStartDate() != null && data.getEndDate() != null) {

            sb.append(format.format(data.getStartDate()));
            sb.append(" 至 ");
            sb.append(format.format(data.getEndDate()));
        }
        helper.createCell(sb.toString(), cellStyle, row, 20);
        if (data.getFixAmount() != null) {
            helper.createCell(data.getFixAmount().toPlainString(), cellStyle, row, 21);
        } else {
            helper.createCell(null, cellStyle, row, 21);
        }

        if (data.getFloatAmount() != null) {
            helper.createCell(data.getFloatAmount().toPlainString(), cellStyle, row, 22);
        } else {
            helper.createCell(null, cellStyle, row, 22);
        }

        if (data.getSignTime() != null) {
            helper.createCell(format.format(data.getSignTime()), cellStyle, row, 23);
        } else {
            helper.createCell(null, cellStyle, row, 23);
        }
        if (!StringUtils.isEmpty(data.getReceipt())) {

            String receipt = data.getReceipt().replace(urlBase, fileBase);
            File receiptFile = new File(receipt);
            if (receiptFile.exists()) {
                ClientAnchor anchor = sheet.getWorkbook().getCreationHelper().createClientAnchor();
                anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
                anchor.setDx1(0);
                anchor.setDx2(0);
                anchor.setDy1(0);
                anchor.setDy2(0);
                anchor.setCol1(24);
                anchor.setRow1(rowIndex);
                anchor.setCol2(25);
                anchor.setRow2(rowIndex + 1);
                int imageType = Workbook.PICTURE_TYPE_JPEG;
                if (data.getReceipt().endsWith(".png")) {
                    imageType = Workbook.PICTURE_TYPE_PNG;
                }
                FileInputStream fileStream = null;
                try {
                    fileStream = new FileInputStream(receiptFile);
                    byte[] bytes = IOUtils.toByteArray(fileStream);
                    int picIndex = sheet.getWorkbook().addPicture(bytes, imageType);
                    drawing.createPicture(anchor, picIndex);
                } catch (IOException e) {
                    e.printStackTrace();
                } finally {
                    if (fileStream != null) {
                        try {
                            fileStream.close();
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                }
            }


        } else {
            helper.createCell(null, cellStyle, row, 24);
        }

        if (data.getInvoiceNumber() != null) {
            helper.createCell(data.getInvoiceNumber(), cellStyle, row, 25);
        } else {
            helper.createCell(null, cellStyle, row, 25);
        }

        if (!StringUtils.isEmpty(data.getInvoiceImg())) {

            String invoiceImg = data.getInvoiceImg().replace(urlBase, fileBase);
            File invoiceFile = new File(invoiceImg);
            if (invoiceFile.exists()) {
                ClientAnchor anchor = sheet.getWorkbook().getCreationHelper().createClientAnchor();
                anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
                anchor.setDx1(0);
                anchor.setDx2(0);
                anchor.setDy1(0);
                anchor.setDy2(0);
                anchor.setCol1(26);
                anchor.setRow1(rowIndex);
                anchor.setCol2(27);
                anchor.setRow2(rowIndex + 1);
                int imageType = Workbook.PICTURE_TYPE_JPEG;
                if (data.getInvoiceImg().endsWith(".png")) {
                    imageType = Workbook.PICTURE_TYPE_PNG;
                }
                FileInputStream fileStream = null;
                try {
                    fileStream = new FileInputStream(invoiceFile);
                    byte[] bytes = IOUtils.toByteArray(fileStream);
                    int picIndex = sheet.getWorkbook().addPicture(bytes, imageType);
                    drawing.createPicture(anchor, picIndex);
                } catch (IOException e) {
                    e.printStackTrace();
                } finally {
                    if (fileStream != null) {
                        try {
                            fileStream.close();
                        } catch (IOException e) {
                            e.printStackTrace();
                        }
                    }
                }
            }


        } else {
            helper.createCell(null, cellStyle, row, 26);
        }

        if (data.getAmount() != null) {
            helper.createCell(data.getAmount().toPlainString(), cellStyle, row, 27);
        } else {
            helper.createCell(null, cellStyle, row, 27);
        }

        if (data.getAmountWithoutTax() != null) {
            helper.createCell(data.getAmountWithoutTax().toPlainString(), cellStyle, row, 28);
        } else {
            helper.createCell(null, cellStyle, row, 28);
        }

        if (data.getValueAddedTax() != null) {
            helper.createCell(data.getValueAddedTax().toPlainString(), cellStyle, row, 29);
        } else {
            helper.createCell(null, cellStyle, row, 29);
        }

        if (data.getContractStatus() != null) {
            helper.createCell(data.getContractStatus(), cellStyle, row, 30);
        } else {
            helper.createCell(null, cellStyle, row, 30);
        }

        if (data.getRemark() != null) {
            helper.createCell(data.getRemark(), cellStyle, row, 31);
        } else {
            helper.createCell(null, cellStyle, row, 31);
        }

        if (data.getCreatedBy() != null) {
            helper.createCell(data.getCreatedBy(), cellStyle, row, 32);
        } else {
            helper.createCell(null, cellStyle, row, 32);
        }

        if (data.getModifiedBy() != null) {
            helper.createCell(data.getModifiedBy(), cellStyle, row, 33);
        } else {
            helper.createCell(null, cellStyle, row, 33);
        }
    }
}
