package cn.com.countrygarden.bi.main.service.impl;

import cn.com.countrygarden.bi.main.dao.MarketingQuotaMapper;
import cn.com.countrygarden.bi.main.model.*;
import cn.com.countrygarden.bi.main.model.twerp.hm.TbSysUser;
import cn.com.countrygarden.bi.main.service.MarketingQuotaService;
import cn.com.countrygarden.bi.main.core.AbstractService;
import cn.com.countrygarden.bi.main.service.PreOrganService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.Date;
import java.util.HashMap;
import java.util.List;


@Service
@Transactional
public class MarketingQuotaServiceImpl extends AbstractService<MarketingQuota> implements MarketingQuotaService {

    private static final Date DATE = new Date();

    private static final LocalDateTime LOCAL_DATE_TIME = LocalDateTime.ofInstant(Instant.now(), ZoneId.systemDefault());


    private final Logger logger = LoggerFactory.getLogger(MarketingQuotaServiceImpl.class);

    @Resource
    private MarketingQuotaMapper mapper;
    @Resource
    private PreOrganService preOrganService;

    /**
     * 上传数据处理
     *
     * @param workbook
     * @param uploadFile
     * @param sysUser
     * @return
     */
    public Workbook processingExcelValue(Workbook workbook, MarketingUploadFile uploadFile, TbSysUser sysUser) {
        Sheet sheet = workbook.getSheetAt(0);
        int physicalRows = sheet.getPhysicalNumberOfRows();

        // 第一个sheet第3行起为有效数据
        if (physicalRows < 3 || sheet.getRow(2) == null) {
            return workbook;
        }

        Short year = Short.parseShort(
                sheet.getRow(0).getCell(0).getStringCellValue().trim().substring(0, 4)
        );

        for (int i = 2; i <= physicalRows; i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }

            String organName = row.getCell(1).getStringCellValue().trim();
            if (organName.equals("")) {
                continue;
            }

            PreOrgan preOrgan = preOrganService.findOrCreate(organName, sysUser);
            MarketingQuota quota = new MarketingQuota();
            quota.setYear(year);
            quota.setOrganId(preOrgan.getId()+"");
            quota.setModifiedBy(sysUser.getUserCode());
            quota.setGmtModified(DATE);

            try {
                quota.setContractArea(BigDecimal.valueOf(row.getCell(2).getNumericCellValue()));
                quota.setContractArea1st(BigDecimal.valueOf(row.getCell(3).getNumericCellValue()));
                quota.setContractArea2nd(BigDecimal.valueOf(row.getCell(4).getNumericCellValue()));
                quota.setContractArea3rd(BigDecimal.valueOf(row.getCell(5).getNumericCellValue()));
                quota.setContractArea4th(BigDecimal.valueOf(row.getCell(6).getNumericCellValue()));

                quota.setChargeArea(BigDecimal.valueOf(row.getCell(7).getNumericCellValue()));
                quota.setChargeArea1st(BigDecimal.valueOf(row.getCell(8).getNumericCellValue()));
                quota.setChargeAreaComplete1st(BigDecimal.valueOf(row.getCell(9).getNumericCellValue()));
                quota.setChargeArea2nd(BigDecimal.valueOf(row.getCell(10).getNumericCellValue()));
                quota.setChargeAreaComplete2nd(BigDecimal.valueOf(row.getCell(11).getNumericCellValue()));
                quota.setChargeArea3rd(BigDecimal.valueOf(row.getCell(12).getNumericCellValue()));
                quota.setChargeAreaComplete3rd(BigDecimal.valueOf(row.getCell(13).getNumericCellValue()));
                quota.setChargeArea4th(BigDecimal.valueOf(row.getCell(14).getNumericCellValue()));
                quota.setChargeAreaComplete4th(BigDecimal.valueOf(row.getCell(15).getNumericCellValue()));

            } catch (IllegalStateException e) {
                logger.error("无法读取数据：单元格格式有误。");
                row.getCell(16).setCellValue("无法读取数据：单元格格式有误。");
                continue;
            } catch (Exception e) {
                logger.error("无法读取数据：" + e.getMessage());
                row.getCell(16).setCellValue("无法读取数据：" + e.getMessage());
                continue;
            }

//            logger.error("preOrgan: " + row.getCell(1).getStringCellValue());
//            logger.error("organCode: " + organCode);
            MarketingQuota findQuota = findByUk(year, preOrgan.getId());
            if (findQuota == null) {
//                logger.error("new to save");
                quota.setGmtCreate(DATE);
                quota.setCreateBy(sysUser.getUserCode());
                save(quota);
            } else {
//                logger.error("exists to update");
                quota.setId(findQuota.getId());
                quota.setIsDelete((byte) 0);
                update(quota);
            }
        }

        return workbook;
    }

    /**
     * 唯一查找
     *
     * @param year
     * @param organId
     * @return
     */
    public MarketingQuota findByUk(int year, int organId) {
        return mapper.findByUk(new HashMap<String, Object>() {{
            put("year", year);
            put("organId", organId);
        }});
    }

    /**
     * 统计本年面积及项目数
     *
     * @return
     */
    public MarketingQuota sumThisYear() {
        return mapper.sum(new HashMap<String, Object>() {{
            put("year", LOCAL_DATE_TIME.getYear());
        }});
    }

    /**
     * 统计区域本年面积及项目数
     *
     * @return
     */
    public MarketingQuota sumOrganOfThisYear(int organId) {
        return mapper.sum(new HashMap<String, Object>() {{
            put("year", new Date().getYear());
            put("organId", organId);
        }});
    }

    /**
     * 后台指标列表
     *
     * @param search
     * @param offset
     * @param limit
     * @return
     */
    public List<MarketingQuota> search(
            String search,
            int offset,
            int limit
    ) {
        return mapper.search(new HashMap<String, Object>() {{
            put("year", LOCAL_DATE_TIME.getYear());
            put("search", search);
            put("offset", Math.max(0, offset));
            put("limit", Math.max(0, limit));
        }});
    }

    @Override
    public MarketingQuota findById(Integer id) {
        return mapper.findById(id);
    }

    public boolean softDelete(int id, TbSysUser sysUser) {
        try {
            mapper.softDelete(new HashMap<String, Object>() {{
                put("id", id);
                put("userCode", sysUser.getUserCode());
            }});
        } catch (Exception e) {
            deleteById(id);
        }
        return true;
    }
}
