package com.cku.checker;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
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 org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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;
import com.thinkgem.jeesite.common.utils.IdGen;

/**
 * 基于石冰老板的收费项标准，生成新的数据
 * @author fanhuibin
 *
 */
public class QuanliangImport {
	
	public static void main(String args[]){
		QuanliangImport i = new QuanliangImport();
		try {
			i.doImport();
		} 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{
		
        InputStream is = new FileInputStream("C:\\Users\\fanhuibin\\Desktop\\犬粮new2.xlsx");
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        Map<String,Map> chargeItems = new HashMap<String,Map>();
        	
        XSSFSheet xssfSheet = xssfWorkbook.getSheet("2016年犬粮");
        List<Map> inSql = new ArrayList<Map>();
        for (int rowNum = (2-1); rowNum <= 7461; rowNum++) {
            XSSFRow xssfRow = xssfSheet.getRow(rowNum);
            System.out.println(rowNum);
            if (xssfRow != null) {
            	Map chargeMap = new HashMap();
            	Map itemMap = new HashMap();
            	
            	String chargeName = getCellStringValue(xssfRow.getCell(0));
            	String memberName = getCellStringValue(xssfRow.getCell(3));
            	String price = getCellStringValue(xssfRow.getCell(4));
            	String num = getCellStringValue(xssfRow.getCell(5));
            	String paymentAmount = getCellStringValue(xssfRow.getCell(8));
            	SimpleDateFormat dateformat = new SimpleDateFormat("yyyy-MM-dd");  
                Date dt = HSSFDateUtil.getJavaDate(xssfRow.getCell(12).getNumericCellValue());//获取成DATE类型     
                String chargeDate = dateformat.format(dt);  
            	
            	chargeMap.put("name", chargeName);
            	chargeMap.put("price",price);
            	
            	itemMap.put("name",chargeName );
            	itemMap.put("memberName",memberName );
            	itemMap.put("price",price );
            	itemMap.put("num",num );
            	itemMap.put("paymentAmount",paymentAmount );
            	itemMap.put("chargeDate", chargeDate);
            	
            	chargeItems.put(chargeName, chargeMap);
            	inSql.add(itemMap);
            	System.out.println(chargeName);
            }
        }
        
        Connection conn = getConn();
        conn.setAutoCommit(false);
	    Statement state = (Statement) conn.createStatement();
        
	    //插入cab收费项
	    int i =1;
        for(String name:chargeItems.keySet()){
        	Map map = chargeItems.get(name);
        	String dept = "87f04dd3165b408dabe629c7630f1a1e";
        	map.put("id", "shangpin"+i);
        	String cabAdd = "insert into sa_cab_payment_item values('shangpin"+i+"','"+map.get("name")+"','"+map.get("price")+"','"+dept+"')";
        	
        	i++;
        	state.addBatch(cabAdd);
        }
        System.out.println(123);
       
        for(Map map :inSql){
        	String chargeId = (String) chargeItems.get(map.get("name")).get("id");
        	BigDecimal big = new BigDecimal((String)map.get("paymentAmount"));
        	big = big.setScale(2, RoundingMode.HALF_UP);  
        	String sql1 = "insert into sa_payment_detail(charge_item_id,charge_date,member_code,id,payment_money,running_number,order_id,price,type,payment_num,create_by,create_date,update_by,update_date,del_flag) "
    				+ "values('"+chargeId+"','"+map.get("chargeDate")+"','"+map.get("memberName")+"','"+IdGen.uuid()+"','"+big.toString()+"','','','"+map.get("price")+"','cab','"+map.get("num")+"','1','2017-03-24 18:00:00','1','2017-03-24 18:00:00',0)";
        	state.addBatch(sql1);
        }
        
        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: //数值类型  
        	if (HSSFDateUtil.isCellDateFormatted(xssfCell)) {    //判断是日期类型  
                SimpleDateFormat dateformat = new SimpleDateFormat("yyyy-MM-dd");  
                Date dt = HSSFDateUtil.getJavaDate(xssfCell.getNumericCellValue());//获取成DATE类型     
                cellValue = dateformat.format(dt);   
            }else{  
                DecimalFormat df = new DecimalFormat("0.000");    
                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;
	}
}