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

import cn.com.countrygarden.bi.main.dao.MarketingProposalMapper;
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.MarketingProposalService;
import cn.com.countrygarden.bi.main.core.AbstractService;
import cn.com.countrygarden.bi.main.service.PreOrganService;
import cn.com.countrygarden.bi.main.util.DateUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.apache.poi.ss.usermodel.Cell;
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.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;


@Service
@Transactional
public class MarketingProposalServiceImpl extends AbstractService<MarketingProposal> implements MarketingProposalService {
    @Resource
    private MarketingProposalMapper mapper;

    private static final Date DATE = new Date();

    @Resource
    private PreCommService preCommService;
    @Resource
    private PreOrganService preOrganService;

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

    public Workbook processingExcelValue(Workbook workbook, MarketingUploadFile uploadFile, TbSysUser sysUser) {
        Sheet sheet = workbook.getSheetAt(0);
        int physicalRows = sheet.getPhysicalNumberOfRows();

        // 第一个sheet第3行起为有效数据
        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(10);
            if (organCell == null || organCell.equals("")) {
                continue;
            }
            String organName = organCell.getStringCellValue().trim();
            if (organName.equals("")) {
                continue;
            }

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

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

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

            PreOrgan proposalPreOrgan = preOrganService.findOrCreate(proposalOrganName, sysUser);
            if (proposalPreOrgan == null) {
                continue;
            }

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

            MarketingProposal item = new MarketingProposal();

            item.setModifiedBy(sysUser.getUserCode());
            item.setGmtModified(DATE);
            item.setCommId(preComm.getId());
            Date proposalDate = row.getCell(7).getDateCellValue();
            String proposalDateStr = new SimpleDateFormat("yyyy-MM-dd").format(proposalDate);
            item.setProposalDate(proposalDate);
            try {
                // 更新comm
                preComm.setProjectType(row.getCell(3).getStringCellValue().trim());
                preComm.setProjectNature(row.getCell(4).getStringCellValue().trim());
                preComm.setCorpType(row.getCell(5).getStringCellValue().trim());
                preComm.setArea(BigDecimal.valueOf(row.getCell(6).getNumericCellValue()));
                preComm.setContacts(row.getCell(9).getStringCellValue().trim());
                preComm.setProposalOrganId(proposalPreOrgan.getId());
                preComm.setModifiedBy(sysUser.getUserCode());
                preComm.setGmtModified(DATE);
                preCommService.update(preComm);

                item.setResult(row.getCell(8).getStringCellValue().trim());
                Cell memoCell = row.getCell(11);
                if (memoCell != null) {
                    item.setMemo(memoCell.getStringCellValue().trim());
                }
            } catch (IllegalStateException e) {
                logger.error("无法读取数据：单元格格式有误。" + e.getMessage());
                row.getCell(13).setCellValue("无法读取数据：单元格格式有误。");
                continue;
            } catch (Exception e) {
                logger.error("无法读取数据：" + e.getMessage());
//                row.getCell(13).setCellValue("无法读取数据：" + e.getMessage());
                continue;
            }

            MarketingProposal findItem = findByUk(preComm.getId(), proposalDateStr);
            if (findItem == null) {
                item.setGmtCreate(DATE);
                item.setCreateBy(sysUser.getUserCode());
                save(item);
            } else {
                item.setId(findItem.getId());
                item.setIsDelete((byte) 0);
                update(item);
            }
        }

        return workbook;
    }

    public MarketingProposal findByUk(int commId, String proposalDate) {
        return mapper.findByUk(new HashMap<String, Object>() {{
            put("commId", commId);
            put("proposalDate", proposalDate);
        }});
    }

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

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

    /**
     * 上会详情列表
     *
     * @return
     */
    public List<MarketingProposal> search(String search, String after, String before, String status, int offset, int limit) {
        String result;
        switch (status) {
            case "approved":
                result = "通过";
                break;
            case "pending":
                result = "待定";
                break;
            case "stop":
                result = "停止";
                break;
            case "all":
            default:
                result = "";
                break;
        }
        return mapper.search(new HashMap<String, Object>() {{
            put("after", parseDate(after));
            put("before", parseDate(before));
            put("search", search.trim());
            put("result", result);
            put("offset", Math.max(0, offset));
            put("limit", Math.max(0, limit));
        }});
    }

    /**
     * find by id
     * @param id
     * @return
     */
    @Override
    public MarketingProposal findById(Integer id) {
        return mapper.findBy(new HashMap<String, Object>() {{
            put("id", id);
        }});
    }

    /**
     * soft delete
     * @param id
     * @param sysUser
     * @return
     */
    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;
    }

    private Date parseDate(String dateString) {
        Date date;
        try {
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-d");
            date = dateFormat.parse(dateString);
        } catch (ParseException e) {
            date = null;
        }
        return date;
    }
}
