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

import cn.com.countrygarden.bi.main.core.AbstractService;
import cn.com.countrygarden.bi.main.dao.marketing.QuotaMapper;
import cn.com.countrygarden.bi.main.exception.ServiceException;
import cn.com.countrygarden.bi.main.model.marketing.Quota;
import cn.com.countrygarden.bi.main.model.twerp.hm.TbSysUser;
import cn.com.countrygarden.bi.main.service.common.CommService;
import cn.com.countrygarden.bi.main.service.marketing.QuotaService;
import cn.com.countrygarden.bi.main.util.NumberUtil;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

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;
import java.util.Map;

/**
 * Created by chenweijiang on 2017/9/23.
 */
@Service
@Transactional(readOnly = true)
public class QuotaServiceImpl extends AbstractService<Quota> implements QuotaService {

    private static final Date DATE = new Date();

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


    @Autowired
    private QuotaMapper mapper;
    @Autowired
    private CommService commService;

    @Transactional(readOnly = false)
    @Override
    public void processingExcelValue(Workbook workbook, String userCode) {
        Sheet sheet = workbook.getSheetAt(0);
        Date current = new Date();
        int physicalRows = sheet.getPhysicalNumberOfRows();

        // 第一个sheet第3行起为有效数据
        if (physicalRows < 3 || sheet.getRow(2) == null) {
            throw new ServiceException("数据格式出错，请填写至少一条有效数据");
        }
        Map<String,Object> organMap = commService.findOrganNameMapCache();
        Short year = 0;

        try{
            year = Short.parseShort(
                    sheet.getRow(0).getCell(0).getStringCellValue().trim().substring(0, 4)
            );
        } catch (Exception ex){
            throw new ServiceException("数据格式出错，第一行应有年度数据'2017年****** '");
        }

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

            String organName = row.getCell(1).getStringCellValue().trim();
            if (StringUtils.isEmpty(organName)) {
                continue;
            }
            String organId = (String) organMap.get(organName);
            if(organId==null){
                throw new ServiceException("数据校检失败，区域'"+organName+"' 不存在erp系统中");
            }

            Quota quota = new Quota();
            quota.setYear(year);
            quota.setOrganId(organId);
            quota.setModifiedBy(userCode);
            quota.setGmtModified(current);

            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) {
                throw new ServiceException("无法读取数据：单元格格式有误。");

            } catch (Exception e) {
                throw new ServiceException("无法读取数据：" + e.getMessage());
            }
            quota = convertArea(quota);


            Quota findQuota = findByUk(year,organId);
            if (findQuota == null) {
//                logger.error("new to save");
                quota.setGmtCreate(DATE);
                quota.setCreateBy(userCode);
                save(quota);
            } else {
//                logger.error("exists to update");
                quota.setId(findQuota.getId());
                quota.setIsDelete((byte) 0);
                update(quota);
            }
        }

    }


    private Quota convertArea(Quota quota){
        quota.setContractArea(areaConvert(quota.getContractArea()));
        quota.setContractArea1st(areaConvert(quota.getContractArea1st()));
        quota.setContractArea2nd(areaConvert(quota.getContractArea2nd()));
        quota.setContractArea3rd(areaConvert(quota.getContractArea3rd()));
        quota.setContractArea4th(areaConvert(quota.getContractArea4th()));

        quota.setChargeArea(areaConvert(quota.getChargeArea()));
        quota.setChargeArea1st(areaConvert(quota.getChargeArea1st()));
        quota.setChargeAreaComplete1st(areaConvert(quota.getChargeAreaComplete1st()));
        quota.setChargeArea2nd(areaConvert(quota.getChargeArea2nd()));
        quota.setChargeAreaComplete2nd(areaConvert(quota.getChargeAreaComplete2nd()));
        quota.setChargeArea3rd(areaConvert(quota.getChargeArea3rd()));
        quota.setChargeAreaComplete3rd(areaConvert(quota.getChargeAreaComplete3rd()));
        quota.setChargeArea4th(areaConvert(quota.getChargeArea4th()));
        quota.setChargeAreaComplete4th(areaConvert(quota.getChargeAreaComplete4th()));
        return quota;
    }

    private BigDecimal areaConvert(BigDecimal area){
        return NumberUtil.fromTenOfThousand(area);
//        return area;
    }


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

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

    /**
     * 统计区域本年面积及项目数
     *
     * @return
     */
    public Quota 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<Quota> 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 Quota findById(Integer id) {
        return mapper.selectByPrimaryKey(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;
    }
}
