package com.cku.checker;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.cku.oa.finance.entity.PaymentChargingItem;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;

/**
 * 基于石冰老板的收费项标准，生成新的数据
 * @author fanhuibin
 *
 */
public class ExcelImport {
	
	public static void main(String args[]){
		ExcelImport i = new ExcelImport();
		try {
			i.doImportCsv();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public  void doImport() throws IOException, SQLException{
		//查询所有的原来的收费项
		Map<String,String> originalChargingItem = new HashMap<String,String>();
		Connection conn = getConn();
		String sql = "select id,name from payment_charging_item";
		PreparedStatement pstmt = (PreparedStatement)conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        while(rs.next()){
        	 String id =  rs.getString(1);
        	 String name = rs.getString(2);
        	 if(originalChargingItem.containsKey(name)&&!name.equals("藏獒牵犬培训报名费")&&!name.equals("展位费")){
        		 if(name.equals("藏獒配种证明登记费")){
        			 id = "181";
        		 }else{
        			 throw new IOException("重复的收费项"+name);
        		 }
        	 }
        	 originalChargingItem.put(name, id);
        }
		
		//收费项之间的关系
        Map<String,Map<String,List<String>>> map = new HashMap<String,Map<String,List<String>>>();
		
        //收费项cab与cku的列表
        Map<String,PaymentChargingItem> cabItems = new HashMap<String,PaymentChargingItem>();
        Map<String,PaymentChargingItem> ckuItems = new HashMap<String,PaymentChargingItem>();
        
        InputStream is = new FileInputStream("C:\\Users\\fanhuibin\\Desktop\\收费对比（CKU  CSV)-20170322-2.xlsx");
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        List<PaymentChargingItem> items = new ArrayList<PaymentChargingItem>();
        	
            XSSFSheet xssfSheet = xssfWorkbook.getSheet("收费调整对比明细");
            // 从第
            String preKey = "";
            String preCabDept = "";
            String preCkuDept = "";
            for (int rowNum = (4-1); rowNum <= 110; rowNum++) {
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                System.out.println(rowNum);
                if (xssfRow != null) {
                	PaymentChargingItem original = new PaymentChargingItem();
                	
                	PaymentChargingItem cab = new PaymentChargingItem();
                	
                	PaymentChargingItem cku = new PaymentChargingItem();
                	
                	String key = getCellStringValue(xssfRow.getCell(0)).split(" ")[0];
                	if(!key.trim().equals("")&&!originalChargingItem.containsKey(key)){
                		continue;
                	}
                	
                	if(!key.trim().equals("")){
                		preKey = key;
                	}else{
                		key = preKey;
                	}
                	key = originalChargingItem.get(key);
                	
                	String cabName = getCellStringValue(xssfRow.getCell(1));
                	String cabPrice = getCellStringValue(xssfRow.getCell(2));
                	String cabDept = getCellStringValue(xssfRow.getCell(3));
                	String ckuName = getCellStringValue(xssfRow.getCell(4));
                	String ckuPrice = getCellStringValue(xssfRow.getCell(5));
                	String ckuDept = getCellStringValue(xssfRow.getCell(6));
                	
                	Map<String,List<String>> chaifenMap = new HashMap<String,List<String>>();
                	if(map.containsKey(key)){
                		chaifenMap = map.get(key);
                	}else{
                		List<String> cabList = new ArrayList<String>();
                		List<String> ckuList = new ArrayList<String>();
                		chaifenMap.put("cab", cabList);
                		chaifenMap.put("cku", ckuList);
                	}
                	
                	if(!cabDept.trim().equals("")){
                		preCabDept = cabDept;
                	}else{
                		cabDept = preCabDept;
                	}
                	
            		if(!ckuDept.trim().equals("")){
                		preCkuDept = ckuDept;
                	}else{
                		ckuDept = preCkuDept;
                	}
                	
                	if(!cabName.trim().equals("")){
                		cab.setName(cabName);
                		cab.setId("cab"+(rowNum+1));
                		cab.setPrice(cabPrice);
                		
                		cab.setRemarks(cabDept);
                		
                		cabItems.put(cabName, cab);
                		List<String> cabList = chaifenMap.get("cab");
                		cabList.add(cabName);
                	}
                	
                	if(!ckuName.trim().equals("")){
                		cku.setName(ckuName);
                		cku.setId("cku"+(rowNum+1));
                		cku.setPrice(ckuPrice);

                		cku.setRemarks(ckuDept);
                		
                		ckuItems.put(ckuName, cku);
                		List<String> ckuList = chaifenMap.get("cku");
                		ckuList.add(ckuName);
                	}
                	map.put(key, chaifenMap);
                }
            }
            
            conn.setAutoCommit(false);
    	    Statement state = (Statement) conn.createStatement();
            //插入cab收费项
            for(String name:cabItems.keySet()){
            	PaymentChargingItem i = cabItems.get(name);
            	String dept = i.getRemarks();
            	//用户管理部 b6aed90fa186432aa16ad45a66410e75
            	//档案管理部 9df34d25aebc4ceaa7e326acb296c614
            	//赛事部         e8f30851b7494896a1805d5fa8b87a54
            	//美容部         eaaaaeff90f34d68a2f704f6ba246911
            	//培训部         1a8f8d269209469797179133d60a2fc6
            	//地方俱乐部 0788ef2fb6fc43b7a62fe90489af712f
            	//杭州办事处 8c9825c10d714397b464ec796435748a
            	//重庆办事处 c2ce7357a6a945008a5cd3d264b384f1
            	if(dept.equals("用户管理部")){
            		dept = "b6aed90fa186432aa16ad45a66410e75";
            	}else if(dept.equals("档案管理部")){
            		dept = "9df34d25aebc4ceaa7e326acb296c614";
            	}else if(dept.equals("赛事部")){
            		dept = "e8f30851b7494896a1805d5fa8b87a54";
            	}else if(dept.equals("美容部")){
            		dept = "eaaaaeff90f34d68a2f704f6ba246911";
            	}else if(dept.equals("培训部")){
            		dept = "1a8f8d269209469797179133d60a2fc6";
            	}else if(dept.equals("地方俱乐部项目组")){
            		dept = "0788ef2fb6fc43b7a62fe90489af712f";
            	}else if(dept.equals("杭州办事处")){
            		dept = "8c9825c10d714397b464ec796435748a";
            	}else if(dept.equals("重庆办事处")){
            		dept = "c2ce7357a6a945008a5cd3d264b384f1";
            	}
            	
            	String cabAdd = "insert into sa_cab_payment_item values('"+i.getId()+"','"+i.getName()+"','"+i.getPrice()+"','"+dept+"')";
            	state.addBatch(cabAdd);
            }
            
            //插入cku收费项
            for(String name:ckuItems.keySet()){
            	PaymentChargingItem i = ckuItems.get(name);
            	String ckuAdd = "";
            	//会员部                  b6aed90fa186432aa16ad45a66410e75
            	//繁殖部                  9df34d25aebc4ceaa7e326acb296c614
            	//协会代收代付     daishoudaifu
            	
            	String dept = i.getRemarks();
            	if(dept.equals("会员部")){
            		dept = "b6aed90fa186432aa16ad45a66410e75";
            	}else if(dept.equals("繁殖部")){
            		dept = "9df34d25aebc4ceaa7e326acb296c614";
            	}else if(dept.equals("协会代收代付")){
            		dept = "daishoudaifu";
            	}
            	try{
            		ckuAdd = "insert into sa_cku_payment_item values('"+i.getId()+"','"+i.getName()+"','"+i.getPrice()+"','"+dept+"')";
            	}catch(Exception e){
            		e.printStackTrace();
            	}
            	state.addBatch(ckuAdd);
            }
            
            //插入cku、cab、旧收费项的关系
            for(String name:map.keySet()){
            	String cabIds = "";
            	String ckuIds = "";
            	List<String> cabs = map.get(name).get("cab");
            	List<String> ckus = map.get(name).get("cku");
            	
            	for(int i=0;i<cabs.size();i++){
            		cabIds+=cabItems.get(cabs.get(i)).getId();
            		if(i+1<cabs.size()){
            			cabIds+=",";
            		}
            	}
            	
            	for(int i=0;i<ckus.size();i++){
            		ckuIds+=ckuItems.get(ckus.get(i)).getId();
            		if(i+1<ckus.size()){
            			ckuIds+=",";
            		}
            	}
            	String insertRelation = "insert into sa_relation_ship values('"+name+"','"+cabIds+"','"+ckuIds+"','','0')";
            	System.out.println(insertRelation);
            	state.addBatch(insertRelation);
            }
            
            state.executeBatch();
    	    conn.commit();
	}
	
	
	public void doImportCsv() throws SQLException, IOException{
		//查询所有的原来的收费项
		Map<String,String> originalChargingItem = new HashMap<String,String>();
		Connection conn = getConn();
		String sql = "select id,name from csv_catalog";
		PreparedStatement pstmt = (PreparedStatement)conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        while(rs.next()){
        	 String id =  rs.getString(1);
        	 String name = rs.getString(2);
        	 if(originalChargingItem.containsKey(name)){
    			 throw new IOException("重复的收费项"+name);
        	 }
        	 originalChargingItem.put(name, id);
        }
		
		//收费项之间的关系
        Map<String,Map<String,List<String>>> map = new HashMap<String,Map<String,List<String>>>();
		
        //收费项cab与cku的列表
        Map<String,PaymentChargingItem> cabItems = new HashMap<String,PaymentChargingItem>();
        Map<String,PaymentChargingItem> csvItems = new HashMap<String,PaymentChargingItem>();
        
        InputStream is = new FileInputStream("C:\\Users\\fanhuibin\\Desktop\\收费对比（CKU  CSV)-20170322-2.xlsx");
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        List<PaymentChargingItem> items = new ArrayList<PaymentChargingItem>();
        	
            XSSFSheet xssfSheet = xssfWorkbook.getSheet("csv");
            // 从第
            String preKey = "";
            String preCabDept = "";
            String preCsvDept = "";
            for (int rowNum = (4-1); rowNum <= 41; rowNum++) {
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                System.out.println(rowNum);
                if (xssfRow != null) {
                	PaymentChargingItem original = new PaymentChargingItem();
                	
                	PaymentChargingItem cab = new PaymentChargingItem();
                	
                	PaymentChargingItem csv = new PaymentChargingItem();
                	
                	String key = getCellStringValue(xssfRow.getCell(0)).split(" ")[0];
                	if(!key.trim().equals("")&&!originalChargingItem.containsKey(key)){
                		continue;
                	}
                	
                	if(!key.trim().equals("")){
                		preKey = key;
                	}else{
                		key = preKey;
                	}
                	key = originalChargingItem.get(key);
                	
                	String cabName = getCellStringValue(xssfRow.getCell(1));
                	String cabPrice = getCellStringValue(xssfRow.getCell(2));
                	String cabDept = getCellStringValue(xssfRow.getCell(3));
                	String csvName = getCellStringValue(xssfRow.getCell(4));
                	String csvPrice = getCellStringValue(xssfRow.getCell(5));
                	String csvDept = getCellStringValue(xssfRow.getCell(6));
                	
                	Map<String,List<String>> chaifenMap = new HashMap<String,List<String>>();
                	if(map.containsKey(key)){
                		chaifenMap = map.get(key);
                	}else{
                		List<String> cabList = new ArrayList<String>();
                		List<String> csvList = new ArrayList<String>();
                		chaifenMap.put("cab", cabList);
                		chaifenMap.put("csv", csvList);
                	}
                	
                	if(!cabDept.trim().equals("")){
                		preCabDept = cabDept;
                	}else{
                		cabDept = preCabDept;
                	}
                	
            		if(!csvDept.trim().equals("")){
                		preCsvDept = csvDept;
                	}else{
                		csvDept = preCsvDept;
                	}
                	
                	if(!cabName.trim().equals("")){
                		cab.setName(cabName);
                		cab.setId("cab"+(rowNum+1));
                		cab.setPrice(cabPrice);
                		
                		cab.setRemarks(cabDept);
                		
                		cabItems.put(cabName, cab);
                		List<String> cabList = chaifenMap.get("cab");
                		cabList.add(cabName);
                	}
                	
                	if(!csvName.trim().equals("")){
                		csv.setName(csvName);
                		csv.setId("csv"+(rowNum+1));
                		csv.setPrice(csvPrice);

                		csv.setRemarks(csvDept);
                		
                		csvItems.put(csvName, csv);
                		List<String> csvList = chaifenMap.get("csv");
                		csvList.add(csvName);
                	}
                	map.put(key, chaifenMap);
                }
            }
            
            conn.setAutoCommit(false);
    	    Statement state = (Statement) conn.createStatement();
            //插入cab收费项
            for(String name:cabItems.keySet()){
            	PaymentChargingItem i = cabItems.get(name);
            	String dept = i.getRemarks();
            	//用户管理部 b6aed90fa186432aa16ad45a66410e75
            	//档案管理部 9df34d25aebc4ceaa7e326acb296c614
            	//赛事部         e8f30851b7494896a1805d5fa8b87a54
            	//美容部         eaaaaeff90f34d68a2f704f6ba246911
            	//培训部         1a8f8d269209469797179133d60a2fc6
            	//地方俱乐部 0788ef2fb6fc43b7a62fe90489af712f
            	//杭州办事处 8c9825c10d714397b464ec796435748a
            	//重庆办事处 c2ce7357a6a945008a5cd3d264b384f1
            	if(dept.equals("用户管理部")){
            		dept = "b6aed90fa186432aa16ad45a66410e75";
            	}else if(dept.equals("档案管理部")){
            		dept = "9df34d25aebc4ceaa7e326acb296c614";
            	}else if(dept.equals("赛事部")){
            		dept = "e8f30851b7494896a1805d5fa8b87a54";
            	}else if(dept.equals("美容部")){
            		dept = "eaaaaeff90f34d68a2f704f6ba246911";
            	}else if(dept.equals("培训部")){
            		dept = "1a8f8d269209469797179133d60a2fc6";
            	}else if(dept.equals("地方俱乐部项目组")){
            		dept = "0788ef2fb6fc43b7a62fe90489af712f";
            	}else if(dept.equals("杭州办事处")){
            		dept = "8c9825c10d714397b464ec796435748a";
            	}else if(dept.equals("重庆办事处")){
            		dept = "c2ce7357a6a945008a5cd3d264b384f1";
            	}
            	
            	String csvAdd = "insert into sa_csv_cab_payment_item values('"+i.getId()+"','"+i.getName()+"','"+i.getPrice()+"','"+dept+"')";
            	state.addBatch(csvAdd);
            }
            
            //插入csv收费项
            for(String name:csvItems.keySet()){
            	PaymentChargingItem i = csvItems.get(name);
            	String ckuAdd = "";
            	//会员部                  b6aed90fa186432aa16ad45a66410e75
            	//繁殖部                  9df34d25aebc4ceaa7e326acb296c614
            	//协会代收代付     daishoudaifu
            	
            	String dept = i.getRemarks();
            	if(dept.equals("会员部")){
            		dept = "b6aed90fa186432aa16ad45a66410e75";
            	}else if(dept.equals("繁殖部")){
            		dept = "9df34d25aebc4ceaa7e326acb296c614";
            	}else if(dept.equals("协会代收代付")){
            		dept = "daishoudaifu";
            	}
            	try{
            		ckuAdd = "insert into sa_csv_payment_item values('"+i.getId()+"','"+i.getName()+"','"+i.getPrice()+"','"+dept+"')";
            	}catch(Exception e){
            		e.printStackTrace();
            	}
            	state.addBatch(ckuAdd);
            }
            
            //插入cku、csv、旧收费项的关系
            for(String name:map.keySet()){
            	String cabIds = "";
            	String csvIds = "";
            	List<String> cabs = map.get(name).get("cab");
            	List<String> csvs = map.get(name).get("csv");
            	
            	for(int i=0;i<cabs.size();i++){
            		cabIds+=cabItems.get(cabs.get(i)).getId();
            		if(i+1<cabs.size()){
            			cabIds+=",";
            		}
            	}
            	
            	for(int i=0;i<csvs.size();i++){
            		csvIds+=csvItems.get(csvs.get(i)).getId();
            		if(i+1<csvs.size()){
            			csvIds+=",";
            		}
            	}
            	String insertRelation = "insert into sa_csv_relation_ship values('"+name+"','"+cabIds+"','"+csvIds+"','','0')";
            	System.out.println(insertRelation);
            	state.addBatch(insertRelation);
            }
            
            state.executeBatch();
    	    conn.commit();
	}
	
	public String getCellStringValue(XSSFCell xssfCell) {      
        String cellValue = "";
        if(xssfCell == null){
        	return cellValue;
        }
        switch (xssfCell.getCellType()) {      
        case HSSFCell.CELL_TYPE_STRING://字符串类型  
            cellValue = xssfCell.getStringCellValue();      
            if(cellValue.trim().equals("")||cellValue.trim().length()<=0)      
                cellValue=" ";      
            break;      
        case HSSFCell.CELL_TYPE_NUMERIC: //数值类型  
        	DecimalFormat df = new DecimalFormat("0");  
        	cellValue = df.format(xssfCell.getNumericCellValue());  
            break;      
        case HSSFCell.CELL_TYPE_FORMULA: //公式  
            xssfCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);      
            cellValue = String.valueOf(xssfCell.getNumericCellValue());      
            break;      
        case HSSFCell.CELL_TYPE_BLANK:      
            cellValue=" ";      
            break;      
        case HSSFCell.CELL_TYPE_BOOLEAN:      
            break;      
        case HSSFCell.CELL_TYPE_ERROR:      
            break; 
        default:      
            break;      
        }      
        return cellValue;      
    }

	private static Connection getConn() {
	    String driver = "com.mysql.jdbc.Driver";
	    String url = "jdbc:mysql://192.168.18.152:3306/ckuoa-chaizhang?useUnicode=true&characterEncoding=utf-8";
	    String username = "root";
	    String password = "123456";
	    Connection conn = null;
	    try {
	        Class.forName(driver); //classLoader,加载对应驱动
	        conn = (Connection) DriverManager.getConnection(url, username, password);
	    } catch (ClassNotFoundException e) {
	        e.printStackTrace();
	    } catch (SQLException e) {
	        e.printStackTrace();
	    }
	    return conn;
	}
}