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.excel.ImportExcel;

/**
 * 支付宝的账户核对功能,核对支付宝导出的数据与数据库中的数据是否能够匹配，
 * 不匹配的字段会被打印出来
 * @author fanhuibin
 *
 */
public class AliPayChecker {
	
	
	//核对某一事件段的数据，正反匹配
	public void dbToAlipay() throws InvalidFormatException, IOException{
		
		
		//第一步读取支付宝的文件的excel
			ImportExcel ie = new ImportExcel("C:\\Users\\fanhuibin\\Desktop\\alipay_record_20161108_1051_1.xlsx",5);
			Connection conn = getConn();
			String sql = "select r.running_number,o.id,r.payment_time,r.out_trade_no,r.payment_amount from payment_record r left join payment_order o on o.order_code = r.running_number where    r.payment_time> '2016-10-26 00:00:00' and r.payment_time<'2016-10-26 23:59:59' and r.payment_way = '1'";
			PreparedStatement pstmt;
			//解析excel从第几行开始，到第几行结束
			int begin = 5518; int end = 5975;
			
			List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
			
			//筛选出excel中符合要求的数据，构建map
			List<Map<String,Object>> excelList = new ArrayList<Map<String,Object>>();
			
			//匹配记录数量
			double totalprice = 0;
			for(int i=begin-1;i<end;i++){
        		
 				Row row = ie.getRow(i);
 				String rN = row.getCell(1).getStringCellValue().trim();
 				String type = row.getCell(10).getStringCellValue().trim();
 				double price = row.getCell(9).getNumericCellValue();
 				if(type.trim().equals("收入")){
 					Date payDate = null;
	 				try{
	 					payDate = row.getCell(3).getDateCellValue();
	 				}catch(Exception e){
	 					
	 				}
 					totalprice+=price;
 					Map map = new HashMap();
 					map.put("rN",rN);
 					map.put("price", price);
 					map.put("payDate", payDate);
 					map.put("success", false);
 					
 					excelList.add(map);
 				}
 			   
 			}
			
			
			//查看数据库有没有多出来 没交费的记录
			System.out.println("查看数据库有没有少记录");
			double dbPrice = 0.0;
			try {
		        pstmt = (PreparedStatement)conn.prepareStatement(sql);
		        ResultSet rs = pstmt.executeQuery();
		        while(rs.next()){
		        	 String runningNumber =  rs.getString(1);
		        	 String id = rs.getString(2);
		        	 Date d = rs.getDate(3);
		        	 String no = rs.getString(4);
		        	 double price = rs.getDouble(5);
		        	 boolean flag = false;
		        	 Map m = new HashMap();
		        	 m.put("runningNumber", runningNumber);
		        	 m.put("id", id);
		        	 m.put("date", d);
		        	 m.put("no", no);
		        	 m.put("price", price);
		        	 m.put("success", false);
		        	 list.add(m);
		        	 dbPrice+=price;
		        	 for(Map map:excelList){
		        		
		 				String rN = (String) map.get("rN");
		 				Date payDate = (Date) map.get("payDate");
		 				String type = (String) map.get("type");
		 				double jiage = (double) map.get("price");
		 				
		 				if(runningNumber.trim().equals(rN)||(id!=null && id.trim().equals(rN))||(no!=null && no.trim().equals(rN))){
		 					
		 					//校验时间是否对得上
		 					if(d!=null && payDate!=null && d.getDay() != payDate.getDay()){
		 						System.out.println(rN+"支付宝与系统的付款时间对不上");
		 						continue;
		 					}
		 					
		 					//校验钱是不是能对上
		 					if(price != 0.0 && price != jiage){
		 						System.out.println(rN+"支付宝与系统的价格不匹配");
		 						continue;
		 					}
		 					
		 					map.put("success", true);
		 					m.put("success", true);
		 					break;
		 				}
		 			   
		 			}
		        }
		        pstmt.close();
		    } catch (SQLException e) {
		        e.printStackTrace();
		    }
			

			System.out.println("数据库的符合要求的数据总数："+list.size()+"---------excel符合要求的数据总数："+excelList.size());
			//总价格比对
			System.out.println("数据库的符合要求的数据总金额："+dbPrice+"---------excel符合要求的数据总金额："+totalprice+"--");
			
			
			//excel比对失败的数据
			for(Map map :excelList){
				boolean success = (boolean) map.get("success");
				if(!success){
					System.out.println("未在数据库找到合适记录的数据"+map.get("rN"));
				}
			}
			
			for(Map map:list){
				boolean success = (boolean) map.get("success");
				if(!success){
					System.out.println("未在excel找到合适记录的数据"+map.get("runningNumber"));
				}
			}
			
			
			System.out.println("下面开始反向校验，如果excel表里有数据，但是数据库没有找到就会输出");
			
			 for(int i=begin-1;i<end;i++){
	        	boolean flag = false;
 				Row row = ie.getRow(i);
 				String rN = row.getCell(1).getStringCellValue().trim();
 				Date payDate = null;
 				try{
 					payDate = row.getCell(3).getDateCellValue();
 				}catch(Exception e){
 					
 				}
 				String type = row.getCell(10).getStringCellValue().trim();
 				
 				if(type.equals("收入")){
	 				for(int j=0;j<list.size();j++){
	 					Map m = list.get(j);
	 					if(m.get("runningNumber").equals(rN) || (m.get("id")!=null&&m.get("id").equals(rN)) || (m.get("no")!=null && m.get("no").equals(rN))){
	 						//检查时间
	 						Date d1 = (Date)m.get("date");
	 						if(payDate!=null&&d1.getDay() != payDate.getDay()){
	 							System.out.println(rN+"支付宝与系统的付款时间对不上");
	 						}
	 						flag = true;
	 						break;
	 					}
	 					
	 				}
 				}else{
 					flag = true;
 				}
 				
 				if(!flag){
 					System.out.println(rN+"校对出错，数据库中没有找到合适的数据");
 				}
 			   
 			}
			

			
	}
	
	
	public static void main(String args[]) throws InvalidFormatException, IOException, InstantiationException, IllegalAccessException, SQLException, ParseException{
		AliPayChecker checker = new AliPayChecker();
		checker.dbToAlipay();
	}
	private static Connection getConn() {
	    String driver = "com.mysql.jdbc.Driver";
	    String url = "jdbc:mysql://139.129.211.179:3306/ckuoa?useUnicode=true&characterEncoding=utf-8";
	    String username = "roUser";
	    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;
	}
}