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

import cn.com.countrygarden.bi.main.dao.MarketingContractMapper;
import cn.com.countrygarden.bi.main.model.*;
import cn.com.countrygarden.bi.main.model.twerp.hm.TbSysUser;
import cn.com.countrygarden.bi.main.service.PreCommService;
import cn.com.countrygarden.bi.main.service.MarketingContractService;
import cn.com.countrygarden.bi.main.core.AbstractService;
import cn.com.countrygarden.bi.main.util.DateUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.apache.poi.ss.usermodel.*;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


@Service
@Transactional
public class MarketingContractServiceImpl extends AbstractService<MarketingContract> implements MarketingContractService {

    private static final Date DATE = new Date();

    @Resource
    private MarketingContractMapper mapper;

    @Resource
    private PreCommService preCommService;

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

    /**
     * 处理上传签约数据
     *
     * @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第2行起为有效数据
        if (physicalRows < 2 || sheet.getRow(1) == null) {
            return workbook;
        }

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

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

            Cell commCell = row.getCell(2);
            if (commCell == null || commCell.equals("")) {
                continue;
            }

            String commName = commCell.getStringCellValue().trim();
            if (commName.equals("")) {
                continue;
            }

            PreComm preComm = preCommService.findOrCreate(commName, organName, sysUser);
            if (preComm == null) {
                continue;
            }

            // 更新comm
            preComm.setProjectType(row.getCell(3).getStringCellValue().trim());
            preComm.setCorpType(row.getCell(4).getStringCellValue().trim());
            preComm.setModifiedBy(sysUser.getUserCode());
            preComm.setGmtModified(DATE);
            preCommService.update(preComm);

            MarketingContract item = new MarketingContract();
            item.setModifiedBy(sysUser.getUserCode());
            item.setGmtModified(DATE);
            item.setCommId(preComm.getId());
            try {
                item.setContractArea(BigDecimal.valueOf(row.getCell(5).getNumericCellValue()));
                item.setContractDate(row.getCell(6).getDateCellValue());
                item.setState(row.getCell(7).getStringCellValue().trim());
            } catch (Exception e) {
                logger.error("无法读取数据：" + e.getMessage());
                row.getCell(8).setCellValue("无法读取数据：" + e.getMessage());
                continue;
            }

//            logger.error("organ: " + row.getCell(1).getStringCellValue());
//            logger.error("organCode: " + organCode);
            MarketingContract findItem = findByUk(preComm.getId());
            if (findItem == null) {
//                logger.error("new to save");
                item.setGmtCreate(DATE);
                item.setCreateBy(sysUser.getUserCode());
                save(item);
            } else {
//                logger.error("exists to update");
                item.setId(findItem.getId());
                item.setIsDelete((byte) 0);
                update(item);
            }
        }

        return workbook;
    }

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

    /**
     * 查找最后签约
     *
     * @return
     */
    public MarketingContract findLastContract() {
        return mapper.findLastContract();
    }

    /**
     * 统计本年面积及项目数
     *
     * @return
     */
    public MarketingContract sumThisYear() {
        Date firstDateOfYear = DateUtil.parseDate(DateUtil.getFirstDayOfThisYear());
        SimpleDateFormat format = new SimpleDateFormat("yyyy-12-31");
        Date lastDateOfYear = DateUtil.parseDate(format.format(firstDateOfYear));
        return mapper.sum(new HashMap<String, Object>() {{
            put("afterDate", firstDateOfYear);
            put("beforeDate", lastDateOfYear);
        }});
    }

    /**
     * 签约趋势
     *
     * @return
     */
    public List<MarketingContract> tendency() {
        return mapper.tendency(new HashMap<String, Object>() {{
            put("afterDate", DateUtil.getFirstDayOfThisYear());
        }});
    }

    /**
     * 签约趋势
     *
     * @return
     */
    public List<MarketingContract> ranking(int limit) {
        return mapper.ranking(new HashMap<String, Object>() {{
            put("afterDate", DateUtil.getFirstDayOfThisYear());
            put("limit", limit);
        }});
    }

    /**
     * 统计上N周面积及项目数
     *
     * @return
     */
    public MarketingContract sumLastWeek(int interval) {
        return mapper.sum(new HashMap<String, Object>() {{
            put("afterDate", DateUtil.getMonday(interval));
            put("beforeDate", DateUtil.getSunday(interval));
        }});
    }

    /**
     * 按合作类型汇总本年面积及项目数
     *
     * @return
     */
    public List<MarketingContract> sumThisYearByCorpType() {
        return mapper.sumBy(new HashMap<String, Object>() {{
            put("afterDate", DateUtil.getFirstDayOfThisYear());
            put("by", "corpType");
        }});
    }

    /**
     * 按项目类型汇总本年面积及项目数
     *
     * @return
     */
    public List<MarketingContract> sumThisYearByProjectType() {
        return mapper.sumBy(new HashMap<String, Object>() {{
            put("afterDate", DateUtil.getFirstDayOfThisYear());
            put("by", "projectType");
        }});
    }

    /**
     * 签约排行
     *
     * @return
     */
    public List<MarketingContract> rankingByOrgan(int season, int offset, int limit) {
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy");
        String year = dateFormat.format(DATE);
        Map<String, Object> param = new HashMap<String, Object>() {{
            put("year", year);
            put("season", season);
            put("limit", limit);
            put("offset", offset);
        }};
        switch (season) {
            case 0:
                param.put("after", year + "-01-01");
                param.put("before", year + "-12-31");
                break;
            case 1:
                param.put("after", year + "-01-01");
                param.put("before", year + "-03-31");
                break;
            case 2:
                param.put("after", year + "-04-01");
                param.put("before", year + "-06-30");
                break;
            case 3:
                param.put("after", year + "-07-01");
                param.put("before", year + "-09-30");
                break;
            case 4:
                param.put("after", year + "-10-01");
                param.put("before", year + "-12-31");
                break;
        }
        return mapper.rankingByOrgan(param);
    }

    /**
     * 签约排行
     *
     * @return
     */
    public List<MarketingContract> listProjectByOrgan(int organId, int season) {
        Map<String, Object> param = new HashMap<String, Object>() {{
            put("season", season);
            put("organId", organId);
        }};
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy");
        String year = dateFormat.format(DATE);
        switch (season) {
            case 0:
                param.put("after", year + "-01-01");
                param.put("before", year + "-12-31");
                break;
            case 1:
                param.put("after", year + "-01-01");
                param.put("before", year + "-03-31");
                break;
            case 2:
                param.put("after", year + "-04-01");
                param.put("before", year + "-06-30");
                break;
            case 3:
                param.put("after", year + "-07-01");
                param.put("before", year + "-09-30");
                break;
            case 4:
                param.put("after", year + "-10-01");
                param.put("before", year + "-12-31");
                break;
        }
        return mapper.listProjectByOrgan(param);
    }

    /**
     * 后台签约列表
     *
     * @param search
     * @param state
     * @param projectType
     * @param corpType
     * @param after
     * @param before
     * @param offset
     * @param limit
     * @return
     */
    public List<MarketingContract> search(
            String search,
            String state,
            String projectType,
            String corpType,
            String after,
            String before,
            int offset,
            int limit
    ) {
        return mapper.search(new HashMap<String, Object>() {{
            put("search", search);
            put("state", state);
            put("projectType", projectType);
            put("corpType", corpType);
            put("after", DateUtil.parseDate(after));
            put("before", DateUtil.parseDate(after));
            put("offset", Math.max(0, offset));
            put("limit", Math.max(0, limit));
        }});
    }

    @Override
    public MarketingContract 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;
    }
}
