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

import cn.com.countrygarden.bi.main.exception.ServiceException;
import cn.com.countrygarden.bi.main.model.common.CommConstant;
import cn.com.countrygarden.bi.main.service.resident.model.QuotaData;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.*;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.regex.Pattern;

public class ExcelDataParser {

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

        List<QuotaData> result = new ArrayList<>();
        Sheet sheet = workbook.getSheetAt(0);
        Iterator<Row> iterator = sheet.rowIterator();
        if (iterator.hasNext()) {
            iterator.next();
        } else {
            return result;
        }
        if (iterator.hasNext()) {
            iterator.next();
        } else {
            return result;
        }
        if (iterator.hasNext()) {
            iterator.next();
        } else {
            return result;
        }
        if (iterator.hasNext()) {
            iterator.next();
        } else {
            return result;
        }
        while (iterator.hasNext()) {
            Row row = iterator.next();
            QuotaData data = new QuotaData();
            String organName = readCellValue(row.getCell(1));
            if (StringUtils.isEmpty(organName)) {
                throw new ServiceException("文件格或不正确，区域名称不能为空");
            }

            String total = readCellValue(row.getCell(2));
            if (StringUtils.isEmpty(total)) {
                throw new ServiceException("文件格或不正确，总体指标不能为空");
            }
            if (!pattern.matcher(total).matches()) {
                throw new ServiceException("文件格或不正确，总体指标格式为非负的数字");
            }

            String gross = readCellValue(row.getCell(3));
            if (StringUtils.isEmpty(gross)) {
                throw new ServiceException("文件格或不正确，整体毛利指标不能为空");
            }
            if (!pattern.matcher(gross).matches()) {
                throw new ServiceException("文件格或不正确，整体毛利指标格式为非负的数字");
            }

            String house = readCellValue(row.getCell(4));
            if (StringUtils.isEmpty(house)) {
                throw new ServiceException("文件格或不正确，房屋配套指标不能为空");
            }
            if (!pattern.matcher(house).matches()) {
                throw new ServiceException("文件格或不正确，房屋配套指标格式为非负的数字");
            }

            String arena = readCellValue(row.getCell(5));
            if (StringUtils.isEmpty(arena)) {
                throw new ServiceException("文件格或不正确，场地运营指标不能为空");
            }
            if (!pattern.matcher(arena).matches()) {
                throw new ServiceException("文件格或不正确，场地运营指标格式为非负的数字");
            }

            String groupBuying = readCellValue(row.getCell(6));
            if (StringUtils.isEmpty(groupBuying)) {
                throw new ServiceException("文件格或不正确，团购指标不能为空");
            }
            if (!pattern.matcher(groupBuying).matches()) {
                throw new ServiceException("文件格或不正确，团购指标格式为非负的数字");
            }

            String tourism = readCellValue(row.getCell(7));
            if (StringUtils.isEmpty(tourism)) {
                throw new ServiceException("文件格或不正确，旅游指标不能为空");
            }
            if (!pattern.matcher(tourism).matches()) {
                throw new ServiceException("文件格或不正确，旅游指标格式为非负的数字");
            }

            String carService = readCellValue(row.getCell(9));
            if (StringUtils.isEmpty(carService)) {
                throw new ServiceException("文件格或不正确，汽车服务指标不能为空");
            }
            if (!pattern.matcher(carService).matches()) {
                throw new ServiceException("文件格或不正确，汽车服务指标格式为非负的数字");
            }

            String shortRent = readCellValue(row.getCell(10));
            if (StringUtils.isEmpty(shortRent)) {
                throw new ServiceException("文件格或不正确，短租指标不能为空");
            }
            if (!pattern.matcher(shortRent).matches()) {
                throw new ServiceException("文件格或不正确，短租指标格式为非负的数字");
            }

            String insurance = readCellValue(row.getCell(11));
            if (StringUtils.isEmpty(insurance)) {
                throw new ServiceException("文件格或不正确，保险指标不能为空");
            }
            if (!pattern.matcher(insurance).matches()) {
                throw new ServiceException("文件格或不正确，保险指标格式为非负的数字");
            }

            String media = readCellValue(row.getCell(12));
            if (StringUtils.isEmpty(media)) {
                throw new ServiceException("文件格或不正确，社区媒体指标不能为空");
            }
            if (!pattern.matcher(media).matches()) {
                throw new ServiceException("文件格或不正确，社区媒体指标格式为非负的数字");
            }

            String other = readCellValue(row.getCell(8));
            if (StringUtils.isEmpty(other)) {
                throw new ServiceException("文件格或不正确，其他指标不能为空");
            }
            if (!pattern.matcher(other).matches()) {
                throw new ServiceException("文件格或不正确，其他指标格式为非负的数字");
            }

            data.setOrganName(organName);
            data.setTotal(total);
            data.setGross(gross);
            data.setHouse(house);
            data.setArena(arena);
            data.setGroupBuying(groupBuying);
            data.setTourism(tourism);
            data.setCarService(carService);
            data.setShortRent(shortRent);
            data.setInsurance(insurance);
            data.setMedia(media);
            data.setOther(other);
            result.add(data);
        }
        return result;
    }

    public String readCellValue(Cell cell) {
        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;
                    }
                    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
                    return dateFormat.format(date);
                }
                return cell.getNumericCellValue() + "";
            case ERROR:
                return null;
            default:
                return null;
        }
    }
}
