package com.cku.checker;

import java.io.IOException;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
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.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.restlet.engine.util.DateUtils;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.thinkgem.jeesite.common.utils.IdGen;
import com.thinkgem.jeesite.common.utils.excel.ImportExcel;

/**
 * 支付宝的账户核对功能,核对支付宝导出的数据与数据库中的数据是否能够匹配，
 * 不匹配的字段会被打印出来
 * @author fanhuibin
 *
 */
public class chaizhangChecker {
	
	
	public void doCheck() throws SQLException{
		//查询拆分前的总记录情况
		String beforeSql = "select order_code,num from payment_order_detail where charging_item_id = '12' and create_date >= '2017-08-01' and create_date <= '2017-08-31 23:59:59' and del_flag = 0  ";
		
		//查询拆分之后的总记录情况
		String afterSql = "select running_number,payment_num from sa_payment_detail where  charge_item_id = 'cab116' and charge_date >= '2017-08-01' and charge_date <= '2017-08-31 23:59:59' and type='cab'";
		
		//比对
		List<Map> beforeList = new ArrayList<Map>();
		PreparedStatement pstmt1 = (PreparedStatement)getConn().prepareStatement(beforeSql);
        ResultSet rs1 = pstmt1.executeQuery();
        while(rs1.next()){
        	Map map = new HashMap();
        	map.put("running_number", rs1.getString("order_code"));
        	map.put("num", rs1.getString("num"));
        	beforeList.add(map);
        }
        
        PreparedStatement pstmt2 = (PreparedStatement)getConn().prepareStatement(afterSql);
        ResultSet rs2 = pstmt2.executeQuery();
        List<Map> afterList = new ArrayList<Map>();
        while(rs2.next()){
        	Map map = new HashMap();
        	map.put("running_number", rs2.getString("running_number"));
        	map.put("num", rs2.getString("payment_num"));
        	afterList.add(map);
        }
        
        loop:for(Map map:beforeList){
        	String running_number = (String) map.get("running_number");
        	String num = (String) map.get("num");
        	for(Map map1:afterList){
        		String running_number1 = (String) map1.get("running_number");
        		String num1 = (String) map1.get("num");
        		if(running_number.equals(running_number1)){
        			continue loop;
        		}
        	}
        	System.out.println("没有找到匹配项目"+running_number);
        }
       
	}
	
	public void insertData(){
		String chargeId = "cku25";
		String memberCode = "5525";
		String date = "2017-08-21 20:28:08";
		String money = "";
		String price = "";
		String runningNumber = "";
		String type = "cku";
		String num = "";
		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+"','"+date+"','"+memberCode+"','"+IdGen.uuid()+"','"+money+"','"+runningNumber+"','','"+price+"','"+type+"','"+num+"','1','2017-09-24','1','2017-09-24',0)";
    	
	}
	
	
	public static void main(String args[]) throws InvalidFormatException, IOException, InstantiationException, IllegalAccessException, SQLException, ParseException{
		chaizhangChecker checker = new chaizhangChecker();
		checker.doCheck();
	}
	private static Connection getConn() {
	    String driver = "com.mysql.jdbc.Driver";
	    String url = "jdbc:mysql://192.168.18.152:3306/fhb_temp_20170920?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;
	}
}