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

import cn.com.victorysoft.business.indicator.dao.UnitMonthDao;
import cn.com.victorysoft.business.indicator.dto.UnitMonthDto;
import cn.com.victorysoft.business.indicator.entity.UnitMonth;
import cn.com.victorysoft.business.sys.dto.Result;
import cn.com.victorysoft.business.sys.param.UnitMonthParam;
import cn.com.victorysoft.business.sys.param.UnitMonthRangeParam;
import cn.com.victorysoft.business.sys.param.UnitcodeParam;
import cn.com.victorysoft.business.util.DateUtils;
import cn.com.victorysoft.business.util.ExcelUtils;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
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.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Slf4j
@Service
public class UnitMonthService {

    @Resource
    private UnitMonthDao unitMonthDao;

    public List<UnitMonth> selectListRange(UnitMonthParam param) {
        UnitMonthRangeParam rangeParam = new UnitMonthRangeParam();
        rangeParam.setUnitcode(param.getUnitcode());
        rangeParam.setMonth1(DateUtils.getPreviousMonth(param.getMonth(), 12));
        rangeParam.setMonth2(param.getMonth());
        return unitMonthDao.selectListRange(rangeParam);
    }

    public List<UnitMonth> selectMonthRange(UnitMonthRangeParam param) {
        // 下面两行代码控制年月、单位先后排序，可视情况切换
//        return unitMonthDao.selectMonthRange(param);
        return unitMonthDao.selectListRange(param);
    }

    public List<UnitMonth> selectListMonth(UnitMonthParam param) {
        return unitMonthDao.selectListMonth(param);
    }

    /**
     * 查询上月技术指标
     */
    public UnitMonth selectTechnologyPrev(UnitMonthParam param) {
        UnitMonthParam Param = new UnitMonthParam();
        Param.setUnitcode(param.getUnitcode());
        Param.setMonth(DateUtils.getPreviousMonth(param.getMonth(), 1));
        return unitMonthDao.selectUnitMonth(Param);
    }

    /**
     * 查询前12个月数据
     */
    public UnitMonth selectTechnology12(UnitMonthParam param) {
        UnitMonthRangeParam rangeParam = new UnitMonthRangeParam();
        rangeParam.setUnitcode(param.getUnitcode());
        rangeParam.setMonth1(DateUtils.getPreviousMonth(param.getMonth(), 12));
        rangeParam.setMonth2(param.getMonth());
        return unitMonthDao.selectList12(rangeParam);
    }

    public List<UnitMonth> selectTechnologyBar(UnitMonthParam param) {
        List<UnitMonth> result = new ArrayList<>(2);
        UnitMonth month = unitMonthDao.selectUnitMonth(param);
        UnitMonth month1 = this.selectTechnologyPrev(param);
        if (month != null) {
            result.add(month);
            result.add(month1);
        }
        return result;
    }

    /**
     * 计算环比
     */
    public UnitMonth selectTechnologyMonthDiff(UnitMonthParam param) {
        UnitMonth month = unitMonthDao.selectUnitMonth(param);
        UnitMonth month1 = this.selectTechnologyPrev(param);
        UnitMonth diff = new UnitMonth();
        if (month != null && month1 != null) {
            if (month.getOpenWellRate() != null && month1.getOpenWellRate() != null) {
                diff.setOpenWellRate(month.getOpenWellRate().subtract(month1.getOpenWellRate()));
            }
            if (month.getSeparateRate() != null && month1.getSeparateRate() != null) {
                diff.setSeparateRate(month.getSeparateRate().subtract(month1.getSeparateRate()));
            }
            if (month.getNormalRate() != null && month1.getNormalRate() != null) {
                diff.setNormalRate(month.getNormalRate().subtract(month1.getNormalRate()));
            }
            if (month.getOpenWellRate1() != null && month1.getOpenWellRate1() != null) {
                diff.setOpenWellRate1(month.getOpenWellRate1().subtract(month1.getOpenWellRate1()));
            }
            if (month.getSeparateRate1() != null && month1.getSeparateRate1() != null) {
                diff.setSeparateRate1(month.getSeparateRate1().subtract(month1.getSeparateRate1()));
            }
            if (month.getNormalRate1() != null && month1.getNormalRate1() != null) {
                diff.setNormalRate1(month.getNormalRate1().subtract(month1.getNormalRate1()));
            }
            if (month.getMoreSection() != null && month1.getMoreSection() != null) {
                diff.setMoreSection(month.getMoreSection() - month1.getMoreSection());
            }
            if (month.getLessSection() != null && month1.getLessSection() != null) {
                diff.setLessSection(month.getLessSection() - month1.getLessSection());
            }
            if (month.getUnclearWell() != null && month1.getUnclearWell() != null) {
                diff.setUnclearWell(month.getUnclearWell() - month1.getUnclearWell());
            }
        }
        return diff;
    }

    public List<UnitMonthDto> selectTechnologyTable(UnitMonthParam param) {
        List<UnitMonthDto> result = new ArrayList<>(9);
        UnitMonth unitMonth = unitMonthDao.selectUnitMonth(param);
        UnitMonth unitMonth12 = this.selectTechnology12(param);
        UnitMonth monthDiff = this.selectTechnologyMonthDiff(param);
        if (unitMonth12 == null) unitMonth12 = new UnitMonth();
        if (unitMonth != null) {

            Double openWellRate = pd(unitMonth12.getOpenWellRate());
            Double separateRate = pd(unitMonth12.getSeparateRate());
            Double normalRate = pd(unitMonth12.getNormalRate());
            Double openWellRate1 = pd(unitMonth12.getOpenWellRate1());
            Double separateRate1 = pd(unitMonth12.getSeparateRate1());
            Double normalRate1 = pd(unitMonth12.getNormalRate1());
            Double moreSection = pd(unitMonth12.getMoreSection());
            Double lessSection = pd(unitMonth12.getLessSection());
            Double unclearWell = pd(unitMonth12.getUnclearWell());

            UnitMonthDto unitMonthDto = new UnitMonthDto();
            unitMonthDto.setSerialNumber(1);
            unitMonthDto.setIndex("开井率");
            unitMonthDto.setUnitname(unitMonth.getUnitname());
            unitMonthDto.setCurrentPeriod(pd(unitMonth.getOpenWellRate()));
            unitMonthDto.setLongTerm(openWellRate);
            unitMonthDto.setChainComparison(pd(monthDiff.getOpenWellRate()));
            result.add(unitMonthDto);

            UnitMonthDto unitMonthDto1 = new UnitMonthDto();
            unitMonthDto1.setSerialNumber(2);
            unitMonthDto1.setIndex("分注率");
            unitMonthDto1.setUnitname(unitMonth.getUnitname());
            unitMonthDto1.setCurrentPeriod(pd(unitMonth.getSeparateRate()));
            unitMonthDto1.setLongTerm(separateRate);
            unitMonthDto1.setChainComparison(pd(monthDiff.getSeparateRate()));
            result.add(unitMonthDto1);

            UnitMonthDto unitMonthDto2 = new UnitMonthDto();
            unitMonthDto2.setSerialNumber(3);
            unitMonthDto2.setIndex("层段合格率");
            unitMonthDto2.setUnitname(unitMonth.getUnitname());
            unitMonthDto2.setCurrentPeriod(pd(unitMonth.getNormalRate()));
            unitMonthDto2.setLongTerm(normalRate);
            unitMonthDto2.setChainComparison(pd(monthDiff.getNormalRate()));
            result.add(unitMonthDto2);

            UnitMonthDto unitMonthDto3 = new UnitMonthDto();
            unitMonthDto3.setSerialNumber(4);
            unitMonthDto3.setIndex("一类井开井率");
            unitMonthDto3.setUnitname(unitMonth.getUnitname());
            unitMonthDto3.setCurrentPeriod(pd(unitMonth.getOpenWellRate1()));
            unitMonthDto3.setLongTerm(openWellRate1);
            unitMonthDto3.setChainComparison(pd(monthDiff.getOpenWellRate1()));
            result.add(unitMonthDto3);

            UnitMonthDto unitMonthDto4 = new UnitMonthDto();
            unitMonthDto4.setSerialNumber(5);
            unitMonthDto4.setIndex("一类井分注率");
            unitMonthDto4.setUnitname(unitMonth.getUnitname());
            unitMonthDto4.setCurrentPeriod(pd(unitMonth.getSeparateRate1()));
            unitMonthDto4.setLongTerm(separateRate1);
            unitMonthDto4.setChainComparison(pd(monthDiff.getSeparateRate1()));
            result.add(unitMonthDto4);

            UnitMonthDto unitMonthDto5 = new UnitMonthDto();
            unitMonthDto5.setSerialNumber(6);
            unitMonthDto5.setIndex("一类井层段合格率");
            unitMonthDto5.setUnitname(unitMonth.getUnitname());
            unitMonthDto5.setCurrentPeriod(pd(unitMonth.getNormalRate1()));
            unitMonthDto5.setLongTerm(normalRate1);
            unitMonthDto5.setChainComparison(pd(monthDiff.getNormalRate1()));
            result.add(unitMonthDto5);

            UnitMonthDto unitMonthDto6 = new UnitMonthDto();
            unitMonthDto6.setSerialNumber(7);
            unitMonthDto6.setIndex("超注层");
            unitMonthDto6.setUnitname(unitMonth.getUnitname());
            unitMonthDto6.setCurrentPeriod(pd(unitMonth.getMoreSection()));
            unitMonthDto6.setLongTerm(moreSection);
            unitMonthDto6.setChainComparison(pd(monthDiff.getMoreSection()));
            result.add(unitMonthDto6);

            UnitMonthDto unitMonthDto7 = new UnitMonthDto();
            unitMonthDto7.setSerialNumber(8);
            unitMonthDto7.setIndex("欠注层");
            unitMonthDto7.setUnitname(unitMonth.getUnitname());
            unitMonthDto7.setCurrentPeriod(pd(unitMonth.getLessSection()));
            unitMonthDto7.setLongTerm(lessSection);
            unitMonthDto7.setChainComparison(pd(monthDiff.getLessSection()));
            result.add(unitMonthDto7);

            UnitMonthDto unitMonthDto8 = new UnitMonthDto();
            unitMonthDto8.setSerialNumber(9);
            unitMonthDto8.setIndex("不清层");
            unitMonthDto8.setUnitname(unitMonth.getUnitname());
            unitMonthDto8.setCurrentPeriod(pd(unitMonth.getUnclearWell()));
            unitMonthDto8.setLongTerm(unclearWell);
            unitMonthDto8.setChainComparison(pd(monthDiff.getUnclearWell()));
            result.add(unitMonthDto8);
        } else {
            UnitMonthDto unitMonthDto = new UnitMonthDto();
            unitMonthDto.setSerialNumber(1);
            unitMonthDto.setIndex("开井率");
            result.add(unitMonthDto);

            UnitMonthDto unitMonthDto1 = new UnitMonthDto();
            unitMonthDto1.setSerialNumber(2);
            unitMonthDto1.setIndex("分注率");
            result.add(unitMonthDto1);

            UnitMonthDto unitMonthDto2 = new UnitMonthDto();
            unitMonthDto2.setSerialNumber(3);
            unitMonthDto2.setIndex("层段合格率");
            result.add(unitMonthDto2);

            UnitMonthDto unitMonthDto3 = new UnitMonthDto();
            unitMonthDto3.setSerialNumber(4);
            unitMonthDto3.setIndex("一类井开井率");
            result.add(unitMonthDto3);

            UnitMonthDto unitMonthDto4 = new UnitMonthDto();
            unitMonthDto4.setSerialNumber(5);
            unitMonthDto4.setIndex("一类井分注率");
            result.add(unitMonthDto4);

            UnitMonthDto unitMonthDto5 = new UnitMonthDto();
            unitMonthDto5.setSerialNumber(6);
            unitMonthDto5.setIndex("一类井层段合格率");
            result.add(unitMonthDto5);

            UnitMonthDto unitMonthDto6 = new UnitMonthDto();
            unitMonthDto6.setSerialNumber(7);
            unitMonthDto6.setIndex("超注层");
            result.add(unitMonthDto6);

            UnitMonthDto unitMonthDto7 = new UnitMonthDto();
            unitMonthDto7.setSerialNumber(8);
            unitMonthDto7.setIndex("欠注层");
            result.add(unitMonthDto7);

            UnitMonthDto unitMonthDto8 = new UnitMonthDto();
            unitMonthDto8.setSerialNumber(9);
            unitMonthDto8.setIndex("不清层");
            result.add(unitMonthDto8);
        }
        return result;
    }

    public void syncUnitMonth(String month) {
        unitMonthDao.syncUnitMonth(month);
    }

    private Double pd(BigDecimal b) {
        return b != null ? b.doubleValue() : null;
    }

    private Double pd(Integer b) {
        return b != null ? b.doubleValue() : null;
    }

    /**
     * 规范化字符串
     */
    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 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 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();
    }

    public String selectUnitcode(UnitcodeParam key) {
        return unitMonthDao.selectUnitcode(key);
    }

    public String selectCyc(UnitcodeParam key) {
        return unitMonthDao.selectCyc(key);
    }

    public UnitMonth selectByPrimaryKey(UnitMonth key) {
        return unitMonthDao.selectByPrimaryKey(key);
    }

    public int insert(UnitMonth param) {
        return unitMonthDao.insert(param);
    }

    public int update(UnitMonth param) {
        return unitMonthDao.update(param);
    }

    /**
     * 模板下载
     */
    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();
    }

    /**
     * 导入
     */
    @SneakyThrows
    @Transactional
    public Result upload(MultipartFile file) {

        try {
            List<List<Object>> list = ExcelUtils.readExcel(file, 0);
            String unitcode;
            for (int i = 1; i < list.size(); i++) {
                try {
                    List<Object> row = list.get(i);
                    UnitMonth unitMonth = new UnitMonth();
                    UnitcodeParam unitcodeParam = new UnitcodeParam();

                    unitcodeParam.setCyc(getString(row, 0));
                    unitcodeParam.setGlq(getString(row, 1));

                    if (unitcodeParam.getGlq().equals("全厂")){
                        unitcode = this.selectCyc(unitcodeParam);
                    }else {
                        unitcode = this.selectUnitcode(unitcodeParam);
                    }
                    unitMonth.setUnitcode(unitcode);
                    unitMonth.setMonth(getString(row, 2).substring(0,6));
                    unitMonth.setPlanOpenWellRate(getBigDecimal(row,3));
                    unitMonth.setPlanSeparateRate(getBigDecimal(row,4));
                    unitMonth.setPlanNormalRate(getBigDecimal(row,5));

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

}
