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

import cn.com.countrygarden.bi.main.exception.ServiceException;
import cn.com.countrygarden.bi.main.model.common.CommConstant;
import cn.com.countrygarden.bi.main.service.marketing.model.ChargeData;
import cn.com.countrygarden.bi.main.service.marketing.model.ContractData;
import cn.com.countrygarden.bi.main.service.marketing.model.ProposalData;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.*;

import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Pattern;

/**
 * Created by chenweijiang on 2017/9/21.
 */
public class ExcelDataParser {

    public List<ProposalData> parseProposal(Workbook workbook){
        Pattern pattern = Pattern.compile(CommConstant.areaRex);

        List<ProposalData> result = new ArrayList<ProposalData>();
        Sheet sheet = workbook.getSheetAt(0);
        Iterator<Row> iterator = sheet.rowIterator();
        if(iterator.hasNext()){
            iterator.next();
        } else {
            return result;
        }
        while (iterator.hasNext()){
            Row row =iterator.next();
            if(row.getRowNum()<2) {
            	continue;
            }
            ProposalData data = new ProposalData();
            String projectNum = readCellValue(row.getCell(0));
//            if(StringUtils.isEmpty(projectNum)){
//                throw  new ServiceException("文件格式不正确，项目编号不能为空");
//            }
            String proposalOrgan = readCellValue(row.getCell(1));
            String projectName = readCellValue(row.getCell(2));
//            if(StringUtils.isEmpty(projectName)){
//                throw  new ServiceException("文件格式不正确，项目名称不能为空");
//            }
            String projectType = readCellValue(row.getCell(3));
//            if(StringUtils.isEmpty(projectType)){
//                throw  new ServiceException("文件格式不正确，项目类型不能为空");
//            }
            String projectNature = readCellValue(row.getCell(4));
//            if(StringUtils.isEmpty(projectNature)){
//                throw  new ServiceException("文件格式不正确，项目性质不能为空");
//            }
            String corpType = readCellValue(row.getCell(5));
//            if(StringUtils.isEmpty(corpType)){
//                throw  new ServiceException("文件格式不正确，合作类型不能为空");
//            }
            DecimalFormat df = new DecimalFormat("0");
         //   String arear = df.format(row.getCell(7).getNumericCellValue());
//            String arear = readCellValue(row.getCell(7));
//            if(StringUtils.isEmpty(arear)){
//                throw  new ServiceException("文件格式不正确，规划面积不能为空");
//            }
//            if(!pattern.matcher(arear).matches()){
//                throw  new ServiceException("文件格式不正确，规划面积格式为非负的数字");
//            }
            String proposalDate = readCellValue(row.getCell(7));
            String status = readCellValue(row.getCell(8));
//            if(StringUtils.isEmpty(status)){
//                throw  new ServiceException("文件格式不正确，评审结果不能为空");
//            }
  //          String statusCode = CommConstant.proposalStatus.get(status);
//            if(statusCode == null){
//                throw  new ServiceException("文件格式不正确，评审结果只能为：'通过'，'停止'，'待定'");
//            }
            String contacts= readCellValue(row.getCell(9));
            String organ= readCellValue(row.getCell(10));
            String remark= readCellValue(row.getCell(11));

            data.setProjectNum(projectNum);
            data.setProposalOrgan(proposalOrgan);
            data.setProjectName(projectName);
            data.setProjectType(projectType);
            data.setProjectNature(projectNature);
            data.setCorpType(corpType);
            data.setArear(0+"");
            data.setProposalDate(proposalDate);
            data.setStatus(status);
            data.setContacts(contacts);
            data.setOrgan(organ);
            data.setRemark(remark);
            result.add(data);
        }
        return result;
    }

    public List<ContractData> parseContract(Workbook workbook){
        Pattern pattern = Pattern.compile(CommConstant.areaRex);
        Sheet sheet = workbook.getSheetAt(0);
        List<ContractData> result = new ArrayList<ContractData>();
        Iterator<Row> iterator = sheet.rowIterator();
        if(iterator.hasNext()){
            iterator.next();
        } else {
            return result;
        }
        while (iterator.hasNext()){
            Row row =iterator.next();
            ContractData data = new ContractData();
            String projectNum = readCellValue(row.getCell(1));
            if(StringUtils.isEmpty(projectNum)){
                throw  new ServiceException("文件格式不正确，项目编号不能为空");
            }
            String proposalOrgan = readCellValue(row.getCell(2));
            if(StringUtils.isEmpty(proposalOrgan)){
                throw  new ServiceException("文件格式不正确，提报区域不能为空");
            }

            String projectName = readCellValue(row.getCell(3));
            if(StringUtils.isEmpty(projectName)){
                throw  new ServiceException("文件格式不正确，项目名称不能为空");
            }
            String projectType = readCellValue(row.getCell(4));
            if(StringUtils.isEmpty(projectType)){
                throw  new ServiceException("文件格式不正确，项目类型不能为空");
            }

            String corpType = readCellValue(row.getCell(5));
            if(StringUtils.isEmpty(corpType)){
                throw  new ServiceException("文件格式不正确，合作类型不能为空");
            }
            DecimalFormat df = new DecimalFormat("0");
            String arear = df.format(row.getCell(6).getNumericCellValue());
//            String arear = readCellValue(row.getCell(6));
            if(StringUtils.isEmpty(arear)){
                throw  new ServiceException("文件格式不正确，规划面积不能为空");
            }

            if(!pattern.matcher(arear).matches()){
                throw  new ServiceException("文件格式不正确，规划面积格式为非负的数字");
            }
            BigDecimal contractArea = new BigDecimal(arear);
//            contractArea = NumberUtil.fromTenOfThousand(contractArea);

            String signDateStr = readCellValue(row.getCell(7));
            String contractCancelDateStr = readCellValue(row.getCell(8));
            Date signDate =null;
            Date contractCancelDate =null;
            if(!StringUtils.isEmpty(signDateStr)){
                signDate = cn.com.countrygarden.bi.main.util.DateUtil.parseDate(signDateStr);
            }
            if(!StringUtils.isEmpty(contractCancelDateStr)){
                contractCancelDate = cn.com.countrygarden.bi.main.util.DateUtil.parseDate(contractCancelDateStr);
            }

            if(signDate ==null && contractCancelDate == null){
                throw  new ServiceException("文件格式不正确，签约日期与解约日期不能同时为空");
            }
            String status = readCellValue(row.getCell(9));
            if(StringUtils.isEmpty(status)){
                throw  new ServiceException("文件格式不正确，状态不能为空");
            }
            String statusCode = CommConstant.contractStatus.get(status);
            if(statusCode == null){
                throw  new ServiceException("文件格式不正确，状态只能为：'已签约'，'已解约'");
            }


            data.setProjectNum(projectNum);
            data.setProposalOrgan(proposalOrgan);
            data.setProjectName(projectName);
            data.setProjectType(projectType);
            data.setCorpType(corpType);
            data.setContractArea(contractArea);
            data.setSignDate(signDate);
            data.setContractCancelDate(contractCancelDate);
            data.setStatus(statusCode);
            result.add(data);
        }
        return result;
    }

    public List<ChargeData> parseCharge(Workbook workbook,boolean isIncrease){
        Pattern pattern = Pattern.compile(CommConstant.areaRex);
        Sheet sheet = workbook.getSheetAt(0);
        List<ChargeData> result = new ArrayList<ChargeData>();
        Iterator<Row> iterator = sheet.rowIterator();
        Date startDate =null;
        Date endDate = null;
        if(iterator.hasNext()){
           Row row = iterator.next();
           String dateStr = readCellValue(row.getCell(0));
           if(StringUtils.isEmpty(dateStr)){
               throw new ServiceException("文件格式不正确, 请在第一项存放日期");
           }

            if(isIncrease){
                //取季度开始时间与结束时间
                int firstIndex = dateStr.indexOf("第");
                int secondIndex = dateStr.indexOf("季度");
                if(firstIndex<=0 || secondIndex<=0){
                    if(StringUtils.isEmpty(dateStr)){
                        throw new ServiceException("文件格式不正确, 日期格式应为:2017第1季度");
                    }
                }
                int year = Integer.parseInt(dateStr.substring(0,firstIndex));
                int quarter= Integer.parseInt(dateStr.substring(firstIndex+1,secondIndex));
                Date[] dates = cn.com.countrygarden.bi.main.util.DateUtil.getQuarterTime(year,quarter);
                startDate = dates[0];
                endDate = dates[1];
            } else {
                Date date = cn.com.countrygarden.bi.main.util.DateUtil.parseDate(dateStr);
                Calendar calendar = Calendar.getInstance();
                calendar.setTime(date);
                int firstDay = calendar.getActualMinimum(Calendar.DAY_OF_MONTH);
                calendar.set(Calendar.DAY_OF_MONTH,firstDay);
                startDate = calendar.getTime();
                int lastDay = calendar.getActualMaximum(Calendar.DAY_OF_MONTH);
                calendar.set(Calendar.DAY_OF_MONTH,lastDay);
                endDate = calendar.getTime();
            }

        } else {
            return result;
        }
        if(iterator.hasNext()){
            iterator.next();
        } else {
            return result;
        }
        while (iterator.hasNext()){
            Row row =iterator.next();
            ChargeData data = new ChargeData();
            String projectNum = readCellValue(row.getCell(0));
            if(StringUtils.isEmpty(projectNum)){
                throw  new ServiceException("文件格式不正确，项目编号不能为空");
            }
            String organ = readCellValue(row.getCell(1));
            if(StringUtils.isEmpty(organ)){
                throw  new ServiceException("文件格式不正确，接管区域不能为空");
            }

            String projectName = readCellValue(row.getCell(2));
            if(StringUtils.isEmpty(projectName)){
                throw  new ServiceException("文件格式不正确，项目名称不能为空");
            }

            String ncAccount = readCellValue(row.getCell(3));
            if(StringUtils.isEmpty(ncAccount)){
                throw  new ServiceException("文件格式不正确，NC系统账号不能为空");
            }
            String ncNumber = readCellValue(row.getCell(4));
            if(StringUtils.isEmpty(ncNumber)){
                throw  new ServiceException("文件格式不正确，NC系统编号不能为空");
            }

            String developSubject = readCellValue(row.getCell(5));
            if(StringUtils.isEmpty(developSubject)){
                throw  new ServiceException("文件格式不正确，项目开发主体不能为空");
            }

            String chargeAreaStr = readCellValue(row.getCell(6));
            if(StringUtils.isEmpty(chargeAreaStr)){
                throw  new ServiceException("文件格式不正确，收费面积不能为空");
            }
            if(!pattern.matcher(chargeAreaStr).matches()){
                throw  new ServiceException("文件格式不正确，收费面积格式为非负的数字");
            }
            BigDecimal chargeArea = new BigDecimal(chargeAreaStr);
//            chargeArea = NumberUtil.fromTenOfThousand(chargeArea);
            Boolean additionalCharge = false;
            String additionalChargeStr = readCellValue(row.getCell(7));
            if(!"是".equals(additionalChargeStr) &&!"否".equals(additionalChargeStr)){
                throw  new ServiceException("文件格式不正确，是否非标只能是'是'或'否'");
            } else if("是".equals(additionalChargeStr)){
                additionalCharge = true;
            }
            String memo = readCellValue(row.getCell(8));



            data.setProjectNum(projectNum);
            data.setOrgan(organ);
            data.setProjectName(projectName);
            data.setNcAccount(ncAccount);
            data.setNcNumber(ncNumber);
            data.setDevelopSubject(developSubject);
            data.setChargeArea(chargeArea);
            data.setAdditionalCharge(additionalCharge);
            data.setMemo(memo);
            data.setStartDate(startDate);
            data.setEndDate(endDate);
            result.add(data);
        }
        return result;
    }

    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);
                }
                return cell.getNumericCellValue()+"";
            case ERROR:
                return null;
            default:
                return null;
        }
    }
}
