package cn.com.countrygarden.bi.main.util;
import java.io.BufferedInputStream;
import java.util.List;

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.web.multipart.MultipartFile;

import cn.com.countrygarden.bi.main.dao.common.OrganMapper;
import cn.com.countrygarden.bi.main.dao.project.ProjectTargetMapper;
import cn.com.countrygarden.bi.main.model.common.Organ;
import cn.com.countrygarden.bi.main.model.project.ProjectTarget;


@Service
public class ProjectTargetExcel {
    @Autowired
	private OrganMapper organMapper;
    @Autowired
    private ProjectTargetMapper projectTargetMapper;
	
	public void excelToObject(MultipartFile mf,String fileName,String OperatorId,String year) {
		Workbook xwb = null;
        BufferedInputStream bf=null;
        Sheet sheet0 = null;   
        Sheet sheet1 = 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);
            }
			sheet0 = xwb.getSheetAt(0);
			sheet1 = xwb.getSheetAt(1);
			 for(int i = 3; i <= sheet0.getPhysicalNumberOfRows(); i++) {
				 Row row = sheet0.getRow(i); 
				 String organName = row.getCell(3).getStringCellValue();
				 Organ organ = new Organ();
				 organ.setName(organName);
				 List<Organ> organList = organMapper.select(organ);
				 if(organList.size() < 1) {
					 throw new Exception("第" + i + "区域不存在。");
				 }
				 organ = organList.get(0);
				 ProjectTarget projectTarget = new ProjectTarget();
				 projectTarget.setYear(year);
				 projectTarget.setOrganId(organ.getOrganCode());
				 List<ProjectTarget> pList =  projectTargetMapper.select(projectTarget);
				 if(pList.size() > 0) {
					 projectTarget = pList.get(0);
					 projectTarget.setSaturationIncomeOne( row.getCell(5).getNumericCellValue());
					 projectTarget.setSaturationIncomeTwo( row.getCell(6).getNumericCellValue());
					 projectTarget.setSaturationIncomeThree( row.getCell(7).getNumericCellValue());
					 projectTarget.setSaturationIncomeFour( row.getCell(8).getNumericCellValue());
					 projectTarget.setSaturationIncomeAll(projectTarget.getSaturationIncomeOne() + projectTarget.getSaturationIncomeTwo() + projectTarget.getSaturationIncomeThree() + projectTarget.getSaturationIncomeFour());
					 projectTarget.setBeIncomeOne( row.getCell(10).getNumericCellValue());
					 projectTarget.setBeIncomeTwo(  row.getCell(11).getNumericCellValue());
					 projectTarget.setBeIncomeThree(  row.getCell(12).getNumericCellValue());
					 projectTarget.setBeIncomeFour(  row.getCell(13).getNumericCellValue());
					 projectTarget.setBeIncomeAll(projectTarget.getBeIncomeOne() + projectTarget.getBeIncomeTwo() + projectTarget.getBeIncomeThree() + projectTarget.getBeIncomeFour());
					 projectTargetMapper.updateByPrimaryKey(projectTarget);
				 }else {
					 projectTarget = new ProjectTarget();
					 projectTarget.setOrganId(organ.getOrganCode());
					 projectTarget.setProvince(row.getCell(3).getStringCellValue());
					 projectTarget.setSaturationIncomeOne( row.getCell(5).getNumericCellValue());
					 projectTarget.setSaturationIncomeTwo( row.getCell(6).getNumericCellValue());
					 projectTarget.setSaturationIncomeThree( row.getCell(7).getNumericCellValue());
					 projectTarget.setSaturationIncomeFour( row.getCell(8).getNumericCellValue());
					 projectTarget.setSaturationIncomeAll(projectTarget.getSaturationIncomeOne() + projectTarget.getSaturationIncomeTwo() + projectTarget.getSaturationIncomeThree() + projectTarget.getSaturationIncomeFour());
					 projectTarget.setBeIncomeOne( row.getCell(10).getNumericCellValue());
					 projectTarget.setBeIncomeTwo(  row.getCell(11).getNumericCellValue());
					 projectTarget.setBeIncomeThree(  row.getCell(12).getNumericCellValue());
					 projectTarget.setBeIncomeFour(  row.getCell(13).getNumericCellValue());
					 projectTarget.setBeIncomeAll(projectTarget.getBeIncomeOne() + projectTarget.getBeIncomeTwo() + projectTarget.getBeIncomeThree() + projectTarget.getBeIncomeFour());
					 projectTargetMapper.insert(projectTarget);
				 }
				 
			 }
	    } 
	    catch (Exception e) {
	        e.printStackTrace();
	    	e.getMessage();
			throw new RuntimeException(e);
	    }
	}
}
