package cn.com.victorysoft.business.technology.service;

import cn.com.victorysoft.business.sys.dto.Result;
import cn.com.victorysoft.business.sys.entity.ExportResult;
import cn.com.victorysoft.business.sys.param.UnitWellMonthPageParam;
import cn.com.victorysoft.business.technology.dao.TechJobFeeDao;
import cn.com.victorysoft.business.technology.entity.TechJobFee;
import cn.com.victorysoft.business.technology.entity.WellTool;
import cn.com.victorysoft.business.technology.param.TechJobFeeExportParam;
import cn.com.victorysoft.business.technology.param.TechJobFeeParam;
import cn.com.victorysoft.business.util.*;
import com.github.pagehelper.PageInfo;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import static cn.com.victorysoft.business.util.ExcelUtils.export;

@Slf4j
@Service
public class TechJobFeeService {

    @Resource
    private TechJobFeeDao techJobFeeDao;

    /**
     * 查询列表数据
     */
    public List<TechJobFee> selectList(TechJobFeeParam param) {
        List<TechJobFee> list = techJobFeeDao.selectList(param);
        if (list.size() > 0) {
            TechJobFee tech = techJobFeeDao.sumList(param);
            tech.setDw("合计");
            list.add(0, tech);
        }

        return list;
    }

    /**
     * 查询列表分页数据
     */
    public PageInfo<TechJobFee> selectPage(TechJobFeeParam param) {
        PageUtils.startPage(param);
        return new PageInfo<>(this.selectList(param));
    }

    /**
     * 查询单位列表数据
     */
    public List<TechJobFee> selectdwList() {
        return techJobFeeDao.selectdwList();
    }

    /**
     * 查询单元列表数据
     */
    public List<TechJobFee> selectdyList() {
        return techJobFeeDao.selectdyList();
    }

    /**
     * 查询井号列表数据
     */
    public List<TechJobFee> selectjhList() {
        return techJobFeeDao.selectjhList();
    }

    /**
     * 查询施工类别列表数据
     */
    public List<TechJobFee> selectsglbList() {
        return techJobFeeDao.selectsglbList();
    }

    /**
     * 查询施工目的列表数据
     */
    public List<TechJobFee> selectsgmdList() {
        return techJobFeeDao.selectsgmdList();
    }

    /**
     * 模板下载
     */
    public void downloadTemplate(String fileName, HttpServletResponse response) throws IOException {
        InputStream in = new ClassPathResource("/import/" + fileName).getInputStream();
        XSSFWorkbook workbook = new XSSFWorkbook(in);
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.reset();
        response.setContentType("application/binary;charset=utf-8");
        response.setHeader("Content-disposition", "attachment; filename=" + fileName);
        OutputStream os = response.getOutputStream();
        workbook.write(os);
        os.flush();
    }

    public TechJobFee selectByPrimaryKey(TechJobFee key) {
        return techJobFeeDao.selectByPrimaryKey(key);
    }

    public int insert(TechJobFee param) {
//        param.setCreateOn(new Date());
        return techJobFeeDao.insert(param);
    }

    private BigDecimal getBigDecimal(List<Object> row, int index) {
        if (index >= row.size()) {
            return null;
        }
        Object value = row.get(index);
        if (value == null) {
            return null;
        }
        return new BigDecimal(standard(value.toString()));
    }

    private BigDecimal getBigDecimalZero(List<Object> row, int index) {
        if (index >= row.size()) {
            return BigDecimal.ZERO;
        }
        Object value = row.get(index);
        if (value == null) {
            return BigDecimal.ZERO;
        }
        return new BigDecimal(standard(value.toString()));
    }

    private String getString(List<Object> row, int index) {
        if (index >= row.size()) {
            return null;
        }
        Object value = row.get(index);
        if (value == null) {
            return null;
        }
        return value.toString();
    }

    private Date getDate(List<Object> row, int index) {
        if (index >= row.size()) {
            return null;
        }
        Object value = row.get(index);
        if (value == null) {
            return null;
        }
        if (value instanceof Date) return (Date)value;
        Date date = null;
        try {
            date = DateUtils.parseDate(value.toString(), "yyyy-MM-dd" ,"yyyy/MM/dd" ,"yyyyMMdd");
//            date = format.parse(value.toString());
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return date;
    }

    /**
     * 规范化字符串
     */
    private String standard(String str) {
        String destination = "";
        Pattern pa = Pattern.compile("\\s*|\t|\r|\n");
        Matcher m = pa.matcher(str);
        destination = m.replaceAll("");
        // 去掉最后的逗号
        String lastChar = destination.substring(destination.length() - 1);
        if (",".equals(lastChar)) {
            destination = destination.substring(0, destination.length() - 1);
        }
        return destination;
    }

    /**
     * 导出内容是否为空
     */
    private String isValueNull(String param) {
        if (param == null) {
            return null;
        } else {
            return param;
        }
    }

    /**
     * 导入
     */
    @SneakyThrows
    @Transactional
    public Result upload(MultipartFile file,String dwdm) {
        try {
            List<List<Object>> list = ExcelUtils.readExcel(file, 0);

            for (int i = 6; i < list.size(); i++) {
                try {
                    List<Object> row = list.get(i);

                    TechJobFee techJobFee = new TechJobFee();
                    techJobFee.setDwdm(dwdm);
                    techJobFee.setDw(getString(row, 0));
                    techJobFee.setJh(getString(row, 3));
                    techJobFee.setJb(getString(row, 4));
                    techJobFee.setYqt(getString(row, 5));
                    techJobFee.setDy(getString(row, 6));
                    techJobFee.setSgdw(getString(row, 9));
                    techJobFee.setSglb(getString(row, 10));
                    techJobFee.setSgmd(getString(row, 11));
                    techJobFee.setXjlj(getString(row, 12));
                    techJobFee.setKgrq(getDate(row, 14));
                    techJobFee.setWgrq(getDate(row, 15));
                    techJobFee.setYsyf(getString(row, 16).substring(0,6));
                    techJobFee.setFyhj(getBigDecimal(row, 17));
                    techJobFee.setLwxj(getBigDecimal(row, 18));
                    techJobFee.setClxj(getBigDecimal(row, 46));
                    techJobFee.setTgxsj(getBigDecimal(row, 59));
                    techJobFee.setTgxje(getBigDecimal(row, 60));
                    techJobFee.setTgjsl(getBigDecimal(row, 61));
                    techJobFee.setTgjje(getBigDecimal(row, 62));
                    techJobFee.setSjgxsl(getBigDecimal(row, 63));
                    techJobFee.setSjgxje(getBigDecimal(row, 64));
                    techJobFee.setSjgjsl(getBigDecimal(row, 65));
                    techJobFee.setSjgjje(getBigDecimal(row, 66));
                    techJobFee.setFslsl(getBigDecimal(row, 107));
                    techJobFee.setFslje(getBigDecimal(row, 108));
                    BigDecimal bigDec = getBigDecimalZero(row, 110).add(getBigDecimalZero(row, 112)).add(getBigDecimalZero(row, 114)).add(getBigDecimalZero(row, 116)).add(getBigDecimalZero(row, 118)).add(getBigDecimalZero(row, 120)).add(getBigDecimalZero(row, 122));
                    techJobFee.setHglhjje(bigDec);
                    techJobFee.setSjfsl(getBigDecimal(row, 147));
                    techJobFee.setSjfje(getBigDecimal(row, 148));
                    techJobFee.setSjgjqtsl(getBigDecimal(row, 151));
                    techJobFee.setSjgjqtje(getBigDecimal(row, 152));
                    techJobFee.setJkzzxsj(getBigDecimal(row, 155));
                    techJobFee.setJkzzxje(getBigDecimal(row, 156));

                    TechJobFee exists = this.selectByPrimaryKey(techJobFee);
                    if (null != exists) {
                        throw new RuntimeException("第" + i + "行记录已存在");
                    }
                    if (techJobFee.getKgrq() != null) {
                        this.insert(techJobFee);
                    }
                } catch (Exception e) {
                    log.error("导入失败", e);
                }
            }
        } catch (Exception e) {
            log.error("文件解析失败", e);
            return Result.fail("导入失败");
        }

        return Result.success();
    }

    /**
     * 导出
     */
    public ExportResult selectSearchList(@RequestBody TechJobFeeExportParam param) {
        List<TechJobFee> list = techJobFeeDao.selectList(param);
        TechJobFee jobFees = techJobFeeDao.sumList(param);
        jobFees.setJh("合计");
        list.add(0, jobFees);
        XSSFWorkbook workbook = null;
        try {
            File file = new ClassPathResource("/import/" + "历年作业费用导出模板.xlsx").getFile();
            FileInputStream inputStream = new FileInputStream(file);
            workbook = new XSSFWorkbook(inputStream);
            XSSFRow xssfRow;
            XSSFSheet sheet = workbook.getSheetAt(0);
            for (int i = 0; i < list.size(); i++) {
                xssfRow = sheet.createRow(6 + i);
                TechJobFee fee = list.get(i);
                xssfRow.createCell(0).setCellValue(fee.getDw());
                xssfRow.createCell(1).setCellValue(fee.getJh());
                xssfRow.createCell(2).setCellValue(fee.getJb());
                xssfRow.createCell(3).setCellValue(fee.getYqt());
                xssfRow.createCell(4).setCellValue(fee.getDy());
                xssfRow.createCell(5).setCellValue(fee.getSgdw());
                xssfRow.createCell(6).setCellValue(fee.getSglb());
                xssfRow.createCell(7).setCellValue(fee.getSgmd());
                xssfRow.createCell(8).setCellValue(fee.getXjlj());
                xssfRow.createCell(9).setCellValue(fee.getKgrq());
                xssfRow.createCell(10).setCellValue(fee.getWgrq());
                xssfRow.createCell(11).setCellValue(fee.getYsyf());
                xssfRow.createCell(12).setCellValue(isValueNull(fee.getFyhj().toString()));
                xssfRow.createCell(13).setCellValue(isValueNull(fee.getLwxj().toString()));
                xssfRow.createCell(14).setCellValue(isValueNull(fee.getClxj().toString()));
                xssfRow.createCell(15).setCellValue(isValueNull(fee.getTgxsj().toString()));
                xssfRow.createCell(16).setCellValue(isValueNull(fee.getTgxje().toString()));
                xssfRow.createCell(17).setCellValue(isValueNull(fee.getTgjsl().toString()));
                xssfRow.createCell(18).setCellValue(isValueNull(fee.getTgjje().toString()));
                xssfRow.createCell(19).setCellValue(isValueNull(fee.getSjgxsl().toString()));
                xssfRow.createCell(20).setCellValue(isValueNull(fee.getSjgxje().toString()));
                xssfRow.createCell(21).setCellValue(isValueNull(fee.getSjgjsl().toString()));
                xssfRow.createCell(22).setCellValue(isValueNull(fee.getSjgjje().toString()));
                xssfRow.createCell(23).setCellValue(isValueNull(fee.getFslsl().toString()));
                xssfRow.createCell(24).setCellValue(isValueNull(fee.getFslje().toString()));
                xssfRow.createCell(25).setCellValue(isValueNull(fee.getHglhjje().toString()));
                xssfRow.createCell(26).setCellValue(isValueNull(fee.getSjfsl().toString()));
                xssfRow.createCell(27).setCellValue(isValueNull(fee.getSjfje().toString()));
                xssfRow.createCell(28).setCellValue(isValueNull(fee.getSjgjqtsl().toString()));
                xssfRow.createCell(29).setCellValue(isValueNull(fee.getSjgjqtje().toString()));
                xssfRow.createCell(30).setCellValue(isValueNull(fee.getJkzzxsj().toString()));
                xssfRow.createCell(31).setCellValue(isValueNull(fee.getJkzzxje().toString()));
            }

            inputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return export(workbook, "历年作业费用导出数据");
    }

}
