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

import java.io.BufferedInputStream;
import java.io.IOException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import com.github.pagehelper.PageRowBounds;

import cn.com.countrygarden.bi.main.dao.dup.LastTransferDetailMapper;
import cn.com.countrygarden.bi.main.dao.dup.TbHsprContractMapper;
import cn.com.countrygarden.bi.main.model.PageData;
import cn.com.countrygarden.bi.main.model.dup.ContractStatistical;
import cn.com.countrygarden.bi.main.model.dup.LastTransferDetail;
import cn.com.countrygarden.bi.main.model.dup.LastTransferDetailReports;
import cn.com.countrygarden.bi.main.model.dup.SellDataManagement;
import cn.com.countrygarden.bi.main.service.dup.LastTransferDetailService;

/**
 * <pre>
 * 。尾盘转移明细ServiceImpl
 * </pre>
 *
 * @author developer developer@meicloud.com
 * @version 1.00.00
 *
 *          <pre>
 * 修改记录
 *    修改后版本:     修改人：  修改日期:     修改内容:
 * </pre>
 */
@Service
@Transactional
public class LastTransferDetailServiceImpl extends ExcelHelper implements
        LastTransferDetailService {
    private Logger logger = LoggerFactory
            .getLogger(LastTransferDetailServiceImpl.class);
    @Resource
    private LastTransferDetailMapper lastTransferDetailMapper;

    @Resource
    private TbHsprContractMapper tbHsprContractMapper;

    /**
     * 查询尾盘转移数据
     */
    @Override
    public PageData findLastTransferDetailLike(
            LastTransferDetail lastTransferDetail) {
        PageRowBounds pageRowBounds = new PageRowBounds(
                lastTransferDetail.getPageIndex(),
                lastTransferDetail.getPageSize());
        List<LastTransferDetail> list = lastTransferDetailMapper
                .findLastTransferDetailLike(lastTransferDetail, pageRowBounds);
        PageData PageData = new PageData(list, pageRowBounds);
        return PageData;
    }

    /**
     * 修改尾盘转移数据
     */
    @Override
    public boolean updataLastTransferDetail(
            LastTransferDetail lastTransferDetail) {
        lastTransferDetail.setReviewStatus("1");
        if ("1".equals(lastTransferDetail.getWhetherSell())) {
            lastTransferDetail.setStatus("2");
        } else {
            lastTransferDetail.setStatus("1");
        }
        return lastTransferDetailMapper
                .updataLastTransferDetail(lastTransferDetail);
    }

    /**
     * 删除尾盘转移数据
     */
    @Override
    public boolean deleteLastTransferDetail(
            LastTransferDetail lastTransferDetail) {
        return lastTransferDetailMapper
                .deleteLastTransferDetail(lastTransferDetail);
    }

    /**
     * 新增尾盘转移数据
     *
     * @param lastTransferDetail
     * @return
     */
    public boolean addLastTransferDetail(LastTransferDetail lastTransferDetail) {
        // Map<String,Object> maps=new HashMap<String,Object>();
        // maps.put("CommName", lastTransferDetail.getCommName());
        // //根据项目名获取对应的id
        // List<ContractStatistical>
        // map=tbHsprContractMapper.findAllCommName(maps);
        // if(map.size()!=0) {
        // lastTransferDetail.setCommId(map.get(0).getCommId());
        // }
        return lastTransferDetailMapper
                .addLastTransferDetail(lastTransferDetail);
    }

    /**
     * 导入尾盘转移数据
     */
    @Override
    public void excelToObject(MultipartFile mf, String fileName, String userCode) {
        Workbook xwb = null;
        BufferedInputStream bf = null;
        Sheet sheet = null;
        try {
            bf = new BufferedInputStream(mf.getInputStream());

            if (fileName.endsWith("xls")) { // Excel&nbsp;2003
                xwb = new HSSFWorkbook(bf);
            } else if (fileName.endsWith("xlsx")) { // Excel 2007/2010
                xwb = new XSSFWorkbook(bf);
            }
            sheet = xwb.getSheetAt(0);//获取第几个页面0开始
            //存放数据
            List<LastTransferDetail> list=new ArrayList<LastTransferDetail>();
            //存放导入进来的房间代码
            List<String> roomCodeList=new ArrayList<String>();
            for (int i = 3; i <= sheet.getPhysicalNumberOfRows(); i++) {
                Row row = sheet.getRow(i);
                //怎么判断是否为结束的字段.(是否为合并单元格;
                if (row == null)
                    break;
                if (row.getCell(0) == null){
                    logger.error("第" + i + "行，区域Id为空。");
                    break;
                }
                LastTransferDetail lastTransferDetail = new LastTransferDetail();
                // 获取数据    将所获取文本单元格除日期外全部强制转化为文本类型再进行读取
                for(int j=0;j<=28;j++){
                    if(row.getCell(j)==null){
                        row.createCell(j);
                    }
                    //数字类型
                    if(j==19||j==23||j==24||j==25){
                        row.getCell(j).setCellType(Cell.CELL_TYPE_NUMERIC);
                        //日期类型
                    }else if(j==16||j==17||j==18){
                        row.getCell(j).setCellType(Cell.CELL_TYPE_NUMERIC);
                        //字符串类型
                    }else{
                        row.getCell(j).setCellType(Cell.CELL_TYPE_STRING);
                    }
                    System.out.println("+++++++++++++"+j);
                }
                lastTransferDetail.setOrganName(row.getCell(1).getStringCellValue());
                lastTransferDetail.setCommName(row.getCell(2).getStringCellValue());
                lastTransferDetail.setParkName(row.getCell(3).getStringCellValue());
                lastTransferDetail.setContractAddress(row.getCell(4).getStringCellValue());
                lastTransferDetail.setRoomCode(row.getCell(5).getStringCellValue());
                roomCodeList.add(row.getCell(5).getStringCellValue());
                lastTransferDetail.setBuildingProperty(row.getCell(6).getStringCellValue());
                lastTransferDetail.setBuildingPropertys(row.getCell(7).getStringCellValue());
                lastTransferDetail.setDecorationStandard(row.getCell(8).getStringCellValue());
                if("是".equals(row.getCell(9).getStringCellValue())) {
                    lastTransferDetail.setWhetherDemonstrationUnit("1");
                }else {
                    lastTransferDetail.setWhetherDemonstrationUnit("0");
                }
                lastTransferDetail.setTemporaryMeasureDuilding(row.getCell(10).getStringCellValue());
                lastTransferDetail.setTemporaryMeasureDwellingSpace(row.getCell(11).getStringCellValue());
                lastTransferDetail.setTemporaryMeasureGarden(row.getCell(12).getStringCellValue());
                lastTransferDetail.setActualMeasurementDuilding(row.getCell(13).getStringCellValue());
                lastTransferDetail.setActualMeasurementDwellingSpace(row.getCell(14).getStringCellValue());
                lastTransferDetail.setActualMeasurementgarden(row.getCell(15).getStringCellValue());
                lastTransferDetail.setAdvanceDate(row.getCell(16).getDateCellValue());
                lastTransferDetail.setCompletionDate(row.getCell(17).getDateCellValue());
                lastTransferDetail.setContractRepossessionDate(row.getCell(18).getDateCellValue());
                //状态
                lastTransferDetail.setStatus("3");//保留
//			//销售状态
//			if("未售".equals(row.getCell(17).getStringCellValue())) {
//				lastTransferDetail.setSalesStatus("0");
//			}else if("保留".equals(row.getCell(17).getStringCellValue())) {
//				lastTransferDetail.setSalesStatus("1");
//			}else if("预留".equals(row.getCell(17).getStringCellValue())) {
//				lastTransferDetail.setSalesStatus("2");
//			}else {
//				lastTransferDetail.setSalesStatus("");
//			}
                lastTransferDetail.setCost(row.getCell(19).getColumnIndex());
                lastTransferDetail.setHeadquartersLowPriceMonitor(row.getCell(20).getStringCellValue());
                lastTransferDetail.setHeadquartersLowPriceRoughMonitor(row.getCell(21).getStringCellValue());
                if("是".equals(row.getCell(22).getStringCellValue())) {
                    lastTransferDetail.setWhetherPartakeLightDisk("1");
                }else if("否".equals(row.getCell(21).getStringCellValue())) {
                    lastTransferDetail.setWhetherPartakeLightDisk("0");
                }else {
                    lastTransferDetail.setWhetherPartakeLightDisk("");
                }
                lastTransferDetail.setFixedPrice(row.getCell(23).getColumnIndex());
                lastTransferDetail.setSalesPromotionDiscount(row.getCell(24).getColumnIndex());
                lastTransferDetail.setDiscountPrice(row.getCell(25).getColumnIndex());
                if("是".equals(row.getCell(26).getStringCellValue())) {
                    lastTransferDetail.setContractVersion("1");
                }else if("否".equals(row.getCell(26).getStringCellValue())) {
                    lastTransferDetail.setContractVersion("0");
                }else {
                    lastTransferDetail.setContractVersion("");
                }


                if("是".equals(row.getCell(27).getStringCellValue())) {
                    lastTransferDetail.setWhetherSell("1");
                }else if("否".equals(row.getCell(27).getStringCellValue())) {
                    lastTransferDetail.setWhetherSell("0");
                }else {
                    lastTransferDetail.setWhetherSell("");
                }
                lastTransferDetail.setNoSellReason(row.getCell(28).getStringCellValue());
                lastTransferDetail.setReviewStatus("0");
                lastTransferDetail.setCreateDate(new Date());
                //将导入进来的数据存进集合--默认保留状态
                list.add(lastTransferDetail);
                //boolean a =this.addLastTransferDetail(lastTransferDetail);
//			if (a != true) {
//				try {
//					throw new Exception("插入失败");
//				} catch (Exception e) {
//					e.printStackTrace();
//				}
//			}
            }
            //获取数据库已存的所有的房间代码
            List<String> roomCodeLists=lastTransferDetailMapper.findAllRoomCode();
            List<String> roomCodeLists1=lastTransferDetailMapper.findAllRoomCode();
            //导入的数据与数据库的数据对比,--留下的是新增的数据
            roomCodeList.removeAll(roomCodeLists);
            //数据库里的数据与导入的数据对比--留下的是已售的数据--即数据库存在的数据,但是现在没有导入
            roomCodeLists1.removeAll(roomCodeList);
            for (LastTransferDetail dto : list) {
                //新增数据
                for (String string : roomCodeList) {
                    if(string.equals(dto.getRoomCode())) {
                        lastTransferDetailMapper.addLastTransferDetail(dto);
                    }
                }
                //更新数据
                for (String string : roomCodeLists1) {
                    if(string.equals(dto.getRoomCode())) {
                        dto.setStatus("1");
                        lastTransferDetailMapper.updataLastTransferDetail(dto);
                    }
                }
            }
        } catch (IOException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }

    }

    /**
     * 创建尾盘转移数就的表头
     */
    public void createLastTransferDetailHeader(Workbook workbook, Sheet sheet) {
        int rowIndex = 0;
        CellStyle headerStyle = createHeaderStyle(workbook);
        Row row = sheet.createRow(rowIndex);
        row.setHeight((short) (250 * 2));
        createCell("序号", headerStyle, row, 0);
        createCell("区域", headerStyle, row, 1);
        createCell("楼盘", headerStyle, row, 2);
        createCell("苑区", headerStyle, row, 3);
        createCell("合同路址", headerStyle, row, 4);
        createCell("房间代码", headerStyle, row, 5);
        createCell("建筑性质", headerStyle, row, 6);
        createCell("建筑性质2", headerStyle, row, 7);
        createCell("装修标准", headerStyle, row, 8);
        createCell("是否示范单位", headerStyle, row, 9);
        createCell("暂测建筑", headerStyle, row, 10);
        createCell("暂测套内", headerStyle, row, 11);
        createCell("暂测花园", headerStyle, row, 12);
        createCell("实测建筑", headerStyle, row, 13);
        createCell("实测套内", headerStyle, row, 14);
        createCell("实测花园", headerStyle, row, 15);
        createCell("预售证日期", headerStyle, row, 16);
        createCell("竣工日期", headerStyle, row, 17);
        createCell("合同收楼日期", headerStyle, row, 18);
        createCell("状态", headerStyle, row, 19);
        createCell("成本", headerStyle, row, 20);
        createCell("总部底价监控", headerStyle, row, 21);
        createCell("总部底价（毛坯）监控", headerStyle, row, 22);
        createCell("是否参与光盘行动", headerStyle, row, 23);
        createCell("在售价格", headerStyle, row, 24);

        createCell("合同版本是否已确定", headerStyle, row, 27);
        createCell("是否可售", headerStyle, row, 28);
        createCell("特殊情况备注", headerStyle, row, 29);

        Row rows = sheet.createRow(rowIndex + 1);
        createCell("定价", headerStyle, rows, 24);
        createCell("促销优惠", headerStyle, rows, 25);
        createCell("优惠折后价", headerStyle, rows, 26);

        // 合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 3, 3));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 4, 4));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 5, 5));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 6, 6));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 7, 7));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 8, 8));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 9, 9));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 10, 10));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 11, 11));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 12, 12));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 13, 13));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 14, 14));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 15, 15));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 16, 16));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 17, 17));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 18, 18));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 19, 19));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 20, 20));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 21, 21));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 22, 22));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 23, 23));
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 24, 26));// 在售价格
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 27, 27));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 28, 28));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 29, 29));

    }

    /**
     * 创建尾盘数据导出的表体
     */
    public void createLastTransferDetailBody(List<LastTransferDetail> dataList,
                                             Workbook workbook, Sheet sheet) {
        CellStyle style = createBodyStyle(workbook);
        int rowIndex = 2;
        int a = 1;
        for (LastTransferDetail data : dataList) {
            Row row = sheet.createRow(rowIndex);
            createCell(a + "", style, row, 0);
            a++;
            createCell(data.getOrganName(), style, row, 1);
            createCell(data.getCommName(), style, row, 2);
            createCell(data.getParkName(), style, row, 3);
            createCell(data.getContractAddress(), style, row, 4);
            createCell(data.getRoomCode(), style, row, 5);
            createCell(data.getBuildingProperty(), style, row, 6);
            createCell(data.getBuildingPropertys(), style, row, 7);
            createCell(data.getDecorationStandard(), style, row, 8);
            if (data.getWhetherDemonstrationUnit().equals("1")) {
                data.setWhetherDemonstrationUnit("是");
            } else {
                data.setWhetherDemonstrationUnit("否");
            }
            createCell(data.getWhetherDemonstrationUnit(), style, row, 9);
            createCell(data.getTemporaryMeasureDuilding(), style, row, 10);
            createCell(data.getTemporaryMeasureDwellingSpace(), style, row, 11);
            createCell(data.getTemporaryMeasureGarden(), style, row, 12);
            createCell(data.getActualMeasurementDuilding(), style, row, 13);
            createCell(data.getActualMeasurementDwellingSpace(), style, row, 14);
            createCell(data.getActualMeasurementgarden(), style, row, 15);
            // 转换日期
            String advanceDates = changeDate(data.getAdvanceDate());
            createCell(advanceDates, style, row, 16);
            // 转换日期
            String completionDates = changeDate(data.getCompletionDate());
            createCell(completionDates, style, row, 17);
            // 转换日期
            String contractRepossessionDates = changeDate(data
                    .getContractRepossessionDate());
            createCell(contractRepossessionDates, style, row, 18);
            if ("1".equals(data.getStatus())) {
                data.setStatus("已售");
            } else if ("2".equals(data.getStatus())) {
                data.setStatus("未售");
            } else if ("3".equals(data.getStatus())) {
                data.setStatus("保留");
            } else {
                data.setStatus("");
            }
            createCell(data.getStatus(), style, row, 19);
            createCell(data.getCost() + "", style, row, 20);
            createCell(data.getHeadquartersLowPriceMonitor(), style, row, 21);
            createCell(data.getHeadquartersLowPriceRoughMonitor(), style, row,
                    22);
            if ("0".equals(data.getWhetherPartakeLightDisk())) {
                data.setWhetherPartakeLightDisk("否");
            } else if ("1".equals(data.getWhetherPartakeLightDisk())) {
                data.setWhetherPartakeLightDisk("是");
            } else {
                data.setWhetherPartakeLightDisk("");
            }

            createCell(data.getWhetherPartakeLightDisk(), style, row, 23);
            createCell(data.getFixedPrice() + "", style, row, 24);
            createCell(data.getSalesPromotionDiscount() + "", style, row, 25);
            createCell(data.getDiscountPrice() + "", style, row, 26);
            if ("1".equals(data.getContractVersion())) {
                data.setContractVersion("是");
            } else if ("0".equals(data.getContractVersion())) {
                data.setContractVersion("否");
            } else {
                data.setContractVersion("");
            }
            createCell(data.getContractVersion(), style, row, 27);
            if ("1".equals(data.getWhetherSell())) {
                data.setWhetherSell("是");
            } else if ("0".equals(data.getContractVersion())) {
                data.setWhetherSell("否");
            } else {
                data.setContractVersion("");
            }
            createCell(data.getWhetherSell(), style, row, 28);
            createCell(data.getNoSellReason(), style, row, 29);
            // //是否审核
            // if("1".equals(data.getReviewStatus())) {
            // data.setReviewStatus("是");
            // }else {
            // data.setReviewStatus("否");
            // }
            // createCell(data.getReviewStatus(), style, row, 28);

            rowIndex++;
        }

    }

    /**
     * 日期转字符
     *
     * @param date
     * @return
     */
    private String changeDate(Date date) {
        String dateStr = "";
        // format的格式可以任意
        DateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
        try {
            dateStr = sdf.format(date);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return dateStr;
    }

    /**
     * 查询尾盘转移数据报表(支持模糊查询)
     */
    @Override
    public PageData findLastTransferDetailLikeReports(
            LastTransferDetailReports lastTransferDetailReports) {
        PageRowBounds pageRowBounds = new PageRowBounds(
                lastTransferDetailReports.getPageIndex(),
                lastTransferDetailReports.getPageSize());
        List<LastTransferDetailReports> list = lastTransferDetailMapper
                .findLastTransferDetailLikeReports(lastTransferDetailReports,
                        pageRowBounds);
        for (LastTransferDetailReports dto : list) {
            // 根据对应的数据对应数据
            LastTransferDetailReports lastTransferDetail = lastTransferDetailMapper
                    .findLastTransferDetailLikeReportsNumber(dto);
            dto.setNumber(lastTransferDetail.getNumber());
            dto.setFixedPrice(lastTransferDetail.getFixedPrice());
        }
        PageData PageData = new PageData(list, pageRowBounds);
        return PageData;
    }

}
