package com.cku.checker;

import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.cku.oa.finance.entity.PaymentRecord;
import com.cku.oa.statistics.entity.SaPaymentDetail;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.thinkgem.jeesite.common.utils.DateUtils;
import com.thinkgem.jeesite.common.utils.IdGen;

/**
 * @author fanhuibin
 *
 */
public class exportChaizhang {
	
	/**

	 * @throws SQLException 
	 */
	public void chipPayment() throws SQLException{
		
		// 第一步，创建一个webbook，对应一个Excel文件  
        HSSFWorkbook wb = new HSSFWorkbook(); 
		HSSFSheet uc = wb.createSheet("会员1");
		HSSFSheet uc2 = wb.createSheet("会员2");
    	HSSFCellStyle style = wb.createCellStyle();  
    	style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
    	
    	HSSFRow row = uc.createRow((int) 0); 
    	HSSFCell cell = row.createCell((short) 0);  
        cell.setCellValue("会员号");  
        cell.setCellStyle(style);  
        cell = row.createCell((short) 1);  
        cell.setCellValue("姓名");  
        cell.setCellStyle(style);
        cell = row.createCell((short) 2);  
        cell.setCellValue("身份证号");  
        cell.setCellStyle(style); 
        
        row = uc2.createRow((int) 0); 
    	cell = row.createCell((short) 0);  
        cell.setCellValue("会员号");  
        cell.setCellStyle(style);  
        cell = row.createCell((short) 1);  
        cell.setCellValue("姓名");  
        cell.setCellStyle(style);
        cell = row.createCell((short) 2);  
        cell.setCellValue("身份证号");  
        cell.setCellStyle(style); 

		
		Map<String,Map> result = new HashMap<String,Map>();
		Connection conn = getConn();
		//select c.member,y.* from huiyuan y left join ProfileCsv c on y.member_code = c.membernumber
		String sql = "select l.member,l.first_name,y.card_no from csv_profile_layer l left join  ProfileCsv c on l.member = c.member left join huiyuan y  on y.member_code = c.membernumber ";
		PreparedStatement pstmt = (PreparedStatement)conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        int i = 0;
        while (rs.next()){
        	String memberCode = rs.getString(1);
        	String name = rs.getString(2);
        	String cardNo = rs.getString(3);
        	cardNo = getCardNo(cardNo);
        	if(i<60000){
	        	HSSFRow row2 = uc.createRow((int) (i+1)); 
	        	HSSFCell cell2 = row2.createCell((short) (0));  
	            cell2.setCellValue(memberCode);  
	            cell2 = row2.createCell((short) 1); 
	            cell2.setCellValue(name);
	            cell2 = row2.createCell((short) 2);  
            	cell2.setCellValue(cardNo);
        	}else{
        		HSSFRow row2 = uc2.createRow((int) (i-60000+1)); 
	        	HSSFCell cell2 = row2.createCell((short) (0));  
	            cell2.setCellValue(memberCode);  
	            cell2 = row2.createCell((short) 1); 
	            cell2.setCellValue(name);
	            cell2 = row2.createCell((short) 2);  
            	cell2.setCellValue(cardNo);
        	}
            i++;
        }
        
        // 第六步，将文件存到指定位置  
        try  
        {  
            FileOutputStream fout = new FileOutputStream("E:/csv会员信息.xls");  
            wb.write(fout);  
            fout.close();  
        }  
        catch (Exception e)  
        {  
            e.printStackTrace();  
        }  
        
	}
	
	private String getCardNo(String card){
		if(card!=null && card.length() == 18){
			card = card.substring(0, 6)+"********"+card.substring(14);
		}
		return card;
	}
	
	public void exportShoufei() throws SQLException{
		//查询收费项目对应关系
		String sql = "select * from sa_relation_ship";
		
		//查询收费项表
		String sql1 = "select * from payment_charging_item";
		
		//查询新收费项表
		String sql2 = "select * from sa_cab_payment_item";
		
		//查询新收费项
		String sql3 = "select * from sa_cku_payment_item";
		
		PreparedStatement pstmt;
		PreparedStatement pstmt1;
		PreparedStatement pstmt2;
		PreparedStatement pstmt3;
		
		pstmt = (PreparedStatement)getConn().prepareStatement(sql);
		
		pstmt1 = (PreparedStatement)getConn().prepareStatement(sql1);
		
		pstmt2 = (PreparedStatement)getConn().prepareStatement(sql2);
		
		pstmt3 =  (PreparedStatement)getConn().prepareStatement(sql3);
		
		ResultSet rs = pstmt.executeQuery();
		List<Map> relation = new ArrayList<Map>();
        while (rs.next()){
        	Map map = new HashMap();
        	String id = rs.getString("id");
         	String cab = rs.getString("CAB");
         	String cku = rs.getString("CKU");
         	map.put("id", id);
        	map.put("cab", cab);
        	map.put("cku", cku);
        	relation.add(map);
        }
        
        ResultSet rs1 = pstmt1.executeQuery();
        List<Map> oldCharge = new ArrayList<Map>();
        while (rs1.next()){
        	Map map = new HashMap();
        	String id = rs1.getString("id");
        	String name = rs1.getString("name");
        	String price = rs1.getString("price");
        	map.put("id", id);
        	map.put("name", name);
        	map.put("price", price);
        	oldCharge.add(map);
        }
        
        ResultSet rs2 = pstmt2.executeQuery();
        List<Map> cabCharge = new ArrayList<Map>();
        while (rs2.next()){
        	Map map = new HashMap();
        	String id = rs2.getString("id");
        	String name = rs2.getString("charging_item_name");
        	String price = rs2.getString("price");
        	map.put("id", id);
        	map.put("name", name);
        	map.put("price", price);
        	cabCharge.add(map);
        }
		
        ResultSet rs3 = pstmt3.executeQuery();
        List<Map> ckuCharge = new ArrayList<Map>();
        while (rs3.next()){
        	Map map = new HashMap();
        	String id = rs3.getString("id");
        	String name = rs3.getString("charging_item_name");
        	String price = rs3.getString("price");
        	map.put("id", id);
        	map.put("name", name);
        	map.put("price", price);
        	ckuCharge.add(map);
        }
		
        
        for(Map map:relation){
        	String id = (String) map.get("id");
        	String cku = (String) map.get("cku");
        	String cab = (String) map.get("cab");
        	
        	String ckuNames = "";
        	String cabNames = "";
        	String oldNames = "";
        	
        	for(Map old: oldCharge){
        		String oldName = (String) old.get("name");
        		String oldId = (String) old.get("id");
        		String oldPrice = (String)old.get("price");
        		if(oldId.equals(id)){
        			oldNames = oldName+"("+oldPrice+")";
        		}
        	}
        	
        	if(StringUtils.isNotEmpty(cku)&&cku.contains(",")){
        		String[] ckus = cku.split(",");
        		for(String s:ckus){
        			for(Map ckuMap :ckuCharge){
        				String ckuId = (String) ckuMap.get("id");
        	        	String ckuName = (String) ckuMap.get("name");
        	        	String ckuPrice = (String) ckuMap.get("price");
        	        	
        	        	if(ckuId.equals(s)){
        	        		ckuNames+=ckuName+"("+ckuPrice+")";
        	        	}
        			}
        		}
        		
        	}else if(StringUtils.isNotEmpty(cku)){
        		for(Map ckuMap :ckuCharge){
    				String ckuId = (String) ckuMap.get("id");
    	        	String ckuName = (String) ckuMap.get("name");
    	        	String ckuPrice = (String) ckuMap.get("price");
    	        	
    	        	if(ckuId.equals(cku)){
    	        		ckuNames+=ckuName+"("+ckuPrice+")";
    	        	}
    			}
        	}
        	if(StringUtils.isNotEmpty(cab)&&cab.contains(",")){
        		String[] cabs = cab.split(",");
        		for(String s:cabs){
        			for(Map cabMap :cabCharge){
        				String cabId = (String) cabMap.get("id");
        	        	String cabName = (String) cabMap.get("name");
        	        	String cabPrice = (String) cabMap.get("price");
        	        	
        	        	if(cabId.equals(s)){
        	        		cabNames+=cabName+"("+cabPrice+")";
        	        	}
        			}
        		}
        		
        	}else if(StringUtils.isNotEmpty(cab)){
        		for(Map cabMap :cabCharge){
    				String cabId = (String) cabMap.get("id");
    	        	String cabName = (String) cabMap.get("name");
    	        	String cabPrice = (String) cabMap.get("price");
    	        	
    	        	if(cabId.equals(cab)){
    	        		cabNames +=cabName+"("+cabPrice+")";
    	        	}
    			}
        	}
        	
        	System.out.println(oldNames+","+ckuNames+","+cabNames);
        }
        
	}
	
	
	public static void main(String args[]) throws SQLException{
		exportChaizhang cs = new exportChaizhang();
		//cs.chipPayment();
		//System.out.println(cs.getCardNo("140702198701087056"));
		cs.exportShoufei();
	}
	
	
	private static Connection getConn() {
	    String driver = "com.mysql.jdbc.Driver";
	    String url = "jdbc:mysql://192.168.18.203:3306/ckuoa?useUnicode=true&characterEncoding=utf-8";
	    String username = "root";
	    String password = "Chongaibao123";
	    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;
	}
}