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

import java.io.BufferedInputStream;
import java.io.IOException;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections4.map.HashedMap;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
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.common.OrganMapper;
import cn.com.countrygarden.bi.main.dao.project.ProjectTargetMapper;
import cn.com.countrygarden.bi.main.exception.ServiceException;
import cn.com.countrygarden.bi.main.model.PageData;
import cn.com.countrygarden.bi.main.model.common.Organ;
import cn.com.countrygarden.bi.main.model.project.ProjectJointPlan;
import cn.com.countrygarden.bi.main.model.project.ProjectTarget;
import cn.com.countrygarden.bi.main.service.project.ProjectTargetService;
import cn.com.countrygarden.bi.main.service.project.impl.vo.ProjectTargetDto;
@Service
@Transactional
public class ProjectTargetServiceImpl implements ProjectTargetService{
	@Autowired
	private ProjectTargetMapper mapper;
	 @Autowired
		private OrganMapper organMapper;
	    @Autowired
	    private ProjectTargetMapper projectTargetMapper;
	@Override
	public PageData select(String year,Integer page, Integer size) {		
		 PageRowBounds pageRowBounds = new PageRowBounds(page,size);
		 List<ProjectTargetDto> pList = mapper.search(year, pageRowBounds);
		return new PageData( pList,pageRowBounds);
	}
	@Override
	public Integer delete(Integer id) {
		return mapper.deleteByPrimaryKey(id);
	}
//	public void save (List<ProjectTarget> pList,String year,List<String> ids) {
//		for (ProjectTarget projectTarget : pList) {
//			
//		}
//	}
	@Override
	public void excelToObject(MultipartFile mf,String fileName,String OperatorId,String year) throws IOException {
		Workbook xwb = null;
        BufferedInputStream bf=null;
        Sheet sheet0 = null;   
      //  Sheet sheet1 = null; 
		
			bf=new BufferedInputStream(mf.getInputStream());
			
            	xwb = new XSSFWorkbook(bf);
            
			sheet0 = xwb.getSheetAt(0);
		//	sheet1 = xwb.getSheetAt(1);
			 for(int i = 2; i <= sheet0.getPhysicalNumberOfRows(); i++) {
				 ProjectTarget projectTarget = new ProjectTarget();
				 Row row = sheet0.getRow(i); 
				 if(null == row) {
					 break;
				 }
				 String organName = row.getCell(2).getStringCellValue();
				 Organ organ = new Organ();
				 organ.setName(organName);
				 List<Organ> organList = organMapper.select(organ);
				 if(organList.size() < 1) {
					 projectTarget.setOrganId(orgMap(organName));
					 //throw new ServiceException("区域"+organName+"不存在");
				 }else {
					 organ = organList.get(0);
					 projectTarget.setOrganId(organ.getOrganCode());
				 }
				 
				
				 projectTarget.setYear(year);
				
				 List<ProjectTarget> pList =  projectTargetMapper.select(projectTarget);
				 if(pList.size() > 0) {
					 projectTarget = pList.get(0);
					 projectTarget.setProvince(row.getCell(3).getStringCellValue());
					 projectTarget.setSaturationIncomeOne( row.getCell(5).getNumericCellValue() * 10000);
					 projectTarget.setSaturationIncomeTwo( row.getCell(6).getNumericCellValue()* 10000);
					 projectTarget.setSaturationIncomeThree( row.getCell(7).getNumericCellValue()* 10000);
					 projectTarget.setSaturationIncomeFour( row.getCell(8).getNumericCellValue()* 10000);
					 projectTarget.setSaturationIncomeAll(projectTarget.getSaturationIncomeOne() + projectTarget.getSaturationIncomeTwo() + projectTarget.getSaturationIncomeThree() + projectTarget.getSaturationIncomeFour());
					 projectTarget.setBeIncomeOne( row.getCell(10).getNumericCellValue()* 10000);
					 projectTarget.setBeIncomeTwo(  row.getCell(11).getNumericCellValue()* 10000);
					 projectTarget.setBeIncomeThree(  row.getCell(12).getNumericCellValue()* 10000);
					 projectTarget.setBeIncomeFour(  row.getCell(13).getNumericCellValue()* 10000);
					 projectTarget.setBeIncomeAll(projectTarget.getBeIncomeOne() + projectTarget.getBeIncomeTwo() + projectTarget.getBeIncomeThree() + projectTarget.getBeIncomeFour());
					 projectTarget.setIntelligenceNumberOne((int) row.getCell(15).getNumericCellValue());
					 projectTarget.setIntelligenceNumberTwo((int) row.getCell(16).getNumericCellValue());
					 projectTarget.setIntelligenceNumberThree((int) row.getCell(17).getNumericCellValue());
					 projectTarget.setIntelligenceNumberFour((int) row.getCell(18).getNumericCellValue());
					 projectTarget.setIntelligenceNumberAll(projectTarget.getIntelligenceNumberOne()+projectTarget.getIntelligenceNumberTwo()+projectTarget.getIntelligenceNumberThree()+projectTarget.getIntelligenceNumberFour());
					 projectTargetMapper.updateByPrimaryKeySelective(projectTarget);
				 }else {
					 projectTarget = new ProjectTarget();
					 projectTarget.setYear(year);
					 projectTarget.setOrganId(organ.getOrganCode());
					 projectTarget.setProvince(row.getCell(3).getStringCellValue());
					 projectTarget.setSaturationIncomeOne( row.getCell(5).getNumericCellValue()* 10000);
					 projectTarget.setSaturationIncomeTwo( row.getCell(6).getNumericCellValue()* 10000);
					 projectTarget.setSaturationIncomeThree( row.getCell(7).getNumericCellValue()* 10000);
					 projectTarget.setSaturationIncomeFour( row.getCell(8).getNumericCellValue()* 10000);
					 projectTarget.setSaturationIncomeAll(projectTarget.getSaturationIncomeOne() + projectTarget.getSaturationIncomeTwo() + projectTarget.getSaturationIncomeThree() + projectTarget.getSaturationIncomeFour());
					 projectTarget.setBeIncomeOne( row.getCell(10).getNumericCellValue()* 10000);
					 projectTarget.setBeIncomeTwo(  row.getCell(11).getNumericCellValue()* 10000);
					 projectTarget.setBeIncomeThree(  row.getCell(12).getNumericCellValue()* 10000);
					 projectTarget.setBeIncomeFour(  row.getCell(13).getNumericCellValue()* 10000);
					 projectTarget.setBeIncomeAll(projectTarget.getBeIncomeOne() + projectTarget.getBeIncomeTwo() + projectTarget.getBeIncomeThree() + projectTarget.getBeIncomeFour());
					 projectTarget.setIntelligenceNumberOne((int) row.getCell(15).getNumericCellValue());
					 projectTarget.setIntelligenceNumberTwo((int) row.getCell(16).getNumericCellValue());
					 projectTarget.setIntelligenceNumberThree((int) row.getCell(17).getNumericCellValue());
					 projectTarget.setIntelligenceNumberFour((int) row.getCell(18).getNumericCellValue());
					 projectTarget.setIntelligenceNumberAll(projectTarget.getIntelligenceNumberOne()+projectTarget.getIntelligenceNumberTwo()+projectTarget.getIntelligenceNumberThree()+projectTarget.getIntelligenceNumberFour());
					 projectTargetMapper.insert(projectTarget);
				 }
				 
			 }
			 
			
	   
	}
	/**
	 * 通过年获取目标
	 */
	@Override
	public List<ProjectTarget> getTargetByYear(String y) {
		ProjectTarget pt = new ProjectTarget();
		pt.setYear(y);
		return mapper.select(pt);
		
	}
	
	public String orgMap(String orgName) {
		Map<String,String> map = new HashedMap<String,String>();
		map.put("重庆金阳", "01BU");
		map.put("碧桂园金阳物业服务有限公司", "01BU");
		map.put("天津区域", "01BG");
		
		map.put("衡水碧桂园城市服务有限公司", "01BT");
		map.put("衡水城市公司", "01BT");
		map.put("粤北区域", "dup0303");
		map.put("清远碧桂园城市服务有限公司", "dup0303");
		map.put("菏泽碧桂园凯兴物业服务有限公司", "01BV");
		map.put("青岛碧桂园润锦物业服务有限公司", "dup0103");
		map.put("北京盛世", "dup0102");
		map.put("北京盛世物业服务公司", "dup0102");
		map.put("西藏城市公司", "01BW");
		map.put("西藏碧桂园物业城市服务有限公司", "01BW");
		map.put("苏州区域", "01BQ");
		map.put("上海/苏州区域", "dup0201");
		map.put("韩城城市公司", "01BI");
		map.put("韩城公司", "01BI");
		map.put("韩城碧桂园城市服务有限公司", "01BI");
		
		map.put("湖北区域", "0114");
		map.put("湖北清能", "01BD");
		map.put("清能碧桂园", "01BD");
		map.put("山东润锦公司", "dup0103");
		map.put("遵义城市公司", "01BK");
		map.put("遵义碧桂园物业城市服务有限公司", "01BK");
		map.put("重庆碧桂园坤煌城市服务有限公司", "0153");
		map.put("肇庆区域", "0154");
		
		map.put("河北碧桂园环城物业服务有限公司", "01BH");
		String name = map.get(orgName);
		if(null == name || "".equals(name)) {
			return null;
		}
		return name;
		
	}

}
