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

import cn.com.countrygarden.bi.main.dao.common.CommMapper;
import cn.com.countrygarden.bi.main.dao.marketing.ContractMapper;
import cn.com.countrygarden.bi.main.dao.marketing.ProposalMapper;
import cn.com.countrygarden.bi.main.exception.ServiceException;
import cn.com.countrygarden.bi.main.model.common.Comm;
import cn.com.countrygarden.bi.main.model.common.CommConstant;
import cn.com.countrygarden.bi.main.model.marketing.CommOperationHistory;
import cn.com.countrygarden.bi.main.model.marketing.Contract;
import cn.com.countrygarden.bi.main.model.marketing.Proposal;
import cn.com.countrygarden.bi.main.service.common.CommService;
import cn.com.countrygarden.bi.main.service.common.CooperationTypeService;
import cn.com.countrygarden.bi.main.service.common.ProjectNatureService;
import cn.com.countrygarden.bi.main.service.common.ProjectTypeService;
import cn.com.countrygarden.bi.main.service.marketing.CommOperationHistoryService;
import cn.com.countrygarden.bi.main.util.NumberUtil;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;


import java.io.FileInputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;


/**
 * Created by chenweijiang on 2017/11/1.
 */
@Service
public class ExcelDataImportService {

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

    @Autowired
    private CooperationTypeService cooperationTypeService;

    @Autowired
    private ProjectNatureService projectNatureService;

    @Autowired
    private ProjectTypeService projectTypeService;

    @Autowired
    private CommService commService;

    @Autowired
    private CommMapper commMapper;
    @Autowired
    private CommOperationHistoryService commOperationHistoryService;

    @Autowired
    private ProposalMapper proposalMapper;

    @Autowired
    private ContractMapper contractMapper;


    @Transactional(readOnly = false)
    public void importFile(Workbook workbook,boolean isThenOfThousand)throws Exception{
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
        SimpleDateFormat defaultDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        Map<String,Object> projectTypeMap = projectTypeService.projectTypeCache();
        Map<String,Object> projectNatureMap = projectNatureService.projectNatureCache();
        Map<String,Object> cooperationTypeMap = cooperationTypeService.cooperationTypeCache();
        Map<String,Object> organNameMap = commService.findOrganNameMapCache();


        Sheet sheet = workbook.getSheetAt(0);
        Iterator<Row> iterator = sheet.rowIterator();
        iterator.next();
        int i=1;
        String systemUser = "009420";
        while (iterator.hasNext()) {
            Row row = iterator.next();

            //接管区域
            String organName = readCellValue(row.getCell(3));
            String organId = (String) organNameMap.get(organName);

            if(StringUtils.isEmpty(organId)){
                logger.error("行"+i+" 接管区域："+organName+" 不存在");
                throw new ServiceException("行"+i+" 接管区域："+organName+" 不存在");
            }
            //拓展区域
            String proposalOrgan = readCellValue(row.getCell(2));
            String proposalOrganId = (String) organNameMap.get(proposalOrgan);
            if(StringUtils.isEmpty(proposalOrganId)){
                logger.error("行"+i+" 拓展区域："+proposalOrgan+" 不存在");
                throw new ServiceException("行"+i+" 拓展区域："+proposalOrgan+" 不存在");
            }
            String projectName = readCellValue(row.getCell(4));
            String projectTypeName = readCellValue(row.getCell(5));
            String projectType = (String)projectTypeMap.get(projectTypeName);
            if(StringUtils.isEmpty(projectType)){
                logger.error("行"+i+" 项目类型："+projectTypeName+" 不存在");
                throw new ServiceException("行"+i+" 项目类型："+projectTypeName+" 不存在");

            }
            //合作类型
            String cropTypeName = readCellValue(row.getCell(6));
            String cropType = (String)cooperationTypeMap.get(cropTypeName);
            if(StringUtils.isEmpty(cropType)){
                logger.error("行"+i+" 合作类型："+cropTypeName+" 不存在");
                throw new ServiceException("行"+i+" 合作类型："+cropTypeName+" 不存在");
            }

            //项目性质
            String natureName = readCellValue(row.getCell(7));
            String projectNature = (String)projectNatureMap.get(natureName);
            if(StringUtils.isEmpty(projectNature)){
                logger.error("行"+i+" 项目性质："+natureName+" 不存在");
                throw new ServiceException("行"+i+" 项目性质："+natureName+" 不存在");
            }
            String areaStr = readCellValue(row.getCell(8));
            BigDecimal area = new BigDecimal(0);
            if(!StringUtils.isEmpty(areaStr)){
                area = new BigDecimal(areaStr);
            }

            if(isThenOfThousand){
                area = NumberUtil.fromTenOfThousand(area);
            }
            //签约面积
            String contractAreaStr = readCellValue(row.getCell(9));
            BigDecimal contractArea = new BigDecimal(0);
            if(!StringUtils.isEmpty(contractAreaStr)){
                contractArea = new BigDecimal(contractAreaStr);
            }
            if(isThenOfThousand){
                contractArea = NumberUtil.fromTenOfThousand(contractArea);
            }

            //上会日期
            String proposalDateStr = readCellValue(row.getCell(10));
            Date proposalDate = null;
            if(!StringUtils.isEmpty(proposalDateStr)){
                if(proposalDateStr.length()==8){
                    proposalDate = dateFormat.parse(proposalDateStr);
                } else {
                    proposalDate = defaultDateFormat.parse(proposalDateStr);
                }
            }

            //评审结果
            String proposalName = readCellValue(row.getCell(11));
            String proposalStatus = CommConstant.proposalStatus.get(proposalName);

            //联系人

            //签约日期
            String contractSignDateStr = readCellValue(row.getCell(13));
            Date contractSignDate = null;
            if(!StringUtils.isEmpty(contractSignDateStr)){
                if(contractSignDateStr.length()==8){
                    contractSignDate = dateFormat.parse(contractSignDateStr);
                } else {
                    contractSignDate = defaultDateFormat.parse(contractSignDateStr);
                }
            }
            //解约日期
            String contractCancelDateStr = readCellValue(row.getCell(14));
            Date contractCancleDate = null;
            if(!StringUtils.isEmpty(contractCancelDateStr)){
                if(contractCancelDateStr.length()==8){
                    contractCancleDate = dateFormat.parse(contractCancelDateStr);
                } else {
                    contractCancleDate = defaultDateFormat.parse(contractCancelDateStr);
                }
            }

            //状态
            String contractStatusName = readCellValue(row.getCell(15));
            String contractStatus = CommConstant.contractStatus.get(contractStatusName);


            Comm comm = findCommBy(organId,projectName);
            if(comm == null){
                comm = new Comm();
                comm.setCreateBy(systemUser);
                comm.setGmtCreate(new Date());
                comm.setName(projectName);
                comm.setOrganId(organId);
                comm.setProposalOrganId(proposalOrganId); // TODO 提报区域ID
                comm.setProposalDate(proposalDate);
                comm.setProjectType(0);
                comm.setProjectNature(0);
                comm.setCorpType(0);
                comm.setArea(area);
                comm.setContractArea(contractArea);
                comm.setContractorCode("009420");
                comm.setContractorName("总部06");
                comm.setContacts(""); // TODO set mobile / phone or contracts
                comm.setComment("");
                comm.setProphase("");
                comm.setStatus(CommConstant.CommStatus.prophase);
                commService.save(comm);

                // 设置6位自增编号
                String commNum = "WT" + String.format("%06d", comm.getId());
                comm.setCommNum(commNum);
                commService.update(comm);
                CommOperationHistory operationHistory = new CommOperationHistory();
                operationHistory.setCommId(comm.getId());
                operationHistory.setName(CommConstant.OperationName.prophase);
                operationHistory.setComment("创建项目");
                operationHistory.setContent("");
                if(proposalDate!=null){
                    operationHistory.setGmtCreate(proposalDate);
                } else if(contractSignDate!=null){
                    operationHistory.setGmtCreate(contractSignDate);
                } else {
                    operationHistory.setGmtCreate(new Date());
                }

                operationHistory.setCreateBy("009420");
                commOperationHistoryService.save(operationHistory);
            }



            //添加上会数据
            if(proposalDate!=null){
                comm.setProposalOrganId(proposalOrganId); // TODO 提报区域ID
                comm.setProposalDate(proposalDate);
                comm.setProjectType(0);
                comm.setProjectNature(0);
                comm.setCorpType(0);
                comm.setArea(area);
                comm.setContractArea(contractArea);
                comm.setStatus(proposalStatus);
                commService.update(comm);

                Proposal proposal = new Proposal();
                proposal.setCommId(comm.getId());
                proposal.setProposalDate(comm.getProposalDate());
                proposal.setIsDelete((byte)0);
                proposal.setMemo("");
                proposal.setGmtCreate(proposalDate);
                proposal.setCreateBy("009420");
                proposal.setGmtModified(proposalDate);
                proposal.setModifiedBy("009420");
                proposal.setResult(proposalStatus);
                proposalMapper.insert(proposal);

                CommOperationHistory history = new CommOperationHistory();
                history.setCommId(comm.getId());
                history.setComment("");
                String operationName = "";
                switch (comm.getStatus()){
                    case CommConstant.CommStatus.candidate:
                        operationName = CommConstant.OperationName.candidate;
                        break;
                    case CommConstant.CommStatus.passed:
                        operationName = CommConstant.OperationName.passed;
                        break;
                    case CommConstant.CommStatus.suspend:
                        operationName = CommConstant.OperationName.suspend;
                        break;

                }
                history.setGmtCreate(proposalDate);
                history.setCreateBy("009420");
                history.setName(operationName);
                commOperationHistoryService.save(history);
            }

            if(!StringUtils.isEmpty(contractAreaStr)){

                if (contractSignDate != null) {
                    comm.setSignDate(contractSignDate);
                }
                if (contractCancleDate!=null) {
                    comm.setContractCancelDate(contractCancleDate);
                }
                comm.setProposalOrganId(proposalOrganId); // TODO 提报区域ID
                comm.setProposalDate(proposalDate);
                comm.setProjectType(0);
                comm.setProjectNature(0);
                comm.setCorpType(0);
                comm.setContractArea(contractArea);
                comm.setStatus(contractStatus);
                commService.update(comm);

                Contract contract = new Contract();
                contract.setCommId(comm.getId());
                contract.setCancelDate(contractCancleDate);
                contract.setContractArea(contractArea);
                contract.setIsDelete((byte) 0);
                contract.setState(contractStatus);
                contract.setContractDate(contractSignDate);
                contract.setGmtCreate(contractSignDate);
                contract.setCreateBy("009420");
                contract.setGmtModified(contractSignDate);
                contract.setModifiedBy("009420");
                contractMapper.insert(contract);


                CommOperationHistory history = new CommOperationHistory();
                history.setCommId(comm.getId());
                history.setGmtCreate(contractSignDate);
                history.setCreateBy("009420");
                String operationName = "";
                switch (comm.getStatus()) {
                    case CommConstant.CommStatus.contract:
                        operationName = CommConstant.OperationName.contract;
                        break;
                    case CommConstant.CommStatus.release:
                        operationName = CommConstant.OperationName.release;
                        break;

                }
                history.setName(operationName);
                commOperationHistoryService.save(history);
            }

            i++;
        }
    }

    @Transactional(readOnly = false)
    public void importFile(String file,boolean isThenOfThousand){

        FileInputStream fileInputStream = null;
        try{
            fileInputStream = new FileInputStream(file);
            Workbook workbook = null;
            if (file.indexOf(".xlsx") > 0) {
                workbook = new XSSFWorkbook(fileInputStream);
            } else {
                workbook = new HSSFWorkbook(fileInputStream);
            }
            importFile(workbook,isThenOfThousand);
        } catch (Exception ex){
            throw new ServiceException(ex.getMessage());
        } finally {
            if(fileInputStream!=null){
                try{
                    fileInputStream.close();
                } catch (Exception ex){
                    throw new ServiceException(ex.getMessage());
                }

            }
        }

    }

    public Comm findCommBy(String organId, String projectName){
        Comm query = new Comm();
        query.setOrganId(organId);
        query.setName(projectName);
        query.setIsDelete((byte)0);
        List<Comm> commList = commMapper.select(query);
        if(commList.size()>0){
            return commList.get(0);
        }
        return null;
    }


    public String readCellValue(Cell cell){
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
        if(cell==null){
            return null;
        }
        switch (cell.getCellTypeEnum()){
            case STRING:
                return cell.getStringCellValue();
            case BLANK:
                return null;
            case BOOLEAN:
                return cell.getBooleanCellValue()+"";
            case FORMULA:
                return null;
            case NUMERIC:
                if(DateUtil.isCellDateFormatted(cell)){
                    Date date = cell.getDateCellValue();
                    if(date == null){
                        return null;
                    }
                    return dateFormat.format(date);
                }
                BigDecimal bigDecimal = new BigDecimal(cell.getNumericCellValue());
                return bigDecimal.toPlainString();
            case ERROR:
                return null;
            default:
                return null;
        }
    }


}
