package com.cku.checker;

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 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;

/**
 * 2015年的数据创建
 * @author fanhuibin
 *
 */
public class CsvChaizhang {
	
	/**

	 * @throws SQLException 
	 */
	public void chipPayment() throws SQLException{
		List<Map> records = new ArrayList<Map>();
		Connection conn = getConn();
		String sql = "select id,member,catalogId,catalogName,money,createTime from CsvAccountDetail where changeType = 0 and createTime>'2017-01-01 00:00:00' and createTime<'2017-04-30 23:59:59'";
		PreparedStatement pstmt = (PreparedStatement)conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        while(rs.next()){
        	 String id =  rs.getString(1);
        	 String member = rs.getString(2);
        	 String catalogId = rs.getString(3);
        	 String catalogName = rs.getString(4);
        	 String money = rs.getString(5);
        	 Date createDate = rs.getDate(6);
        	 Map map = new HashMap();
        	 map.put("id", id);
        	 map.put("member", member);
        	 map.put("catalogId", catalogId);
        	 map.put("catalogName", catalogName);
        	 map.put("money", money);
        	 map.put("createDate", createDate);
        	 records.add(map);
        }
        
        
        Map<String,Map> relations = new HashMap<String,Map>();//所有的收费项的表
        String querySql = "select id,CAB,CSV from sa_csv_relation_ship  ";
        PreparedStatement pstmt1 = (PreparedStatement)conn.prepareStatement(querySql);
        ResultSet rs1 = pstmt1.executeQuery();
        while(rs1.next()){
        	 String id =  rs1.getString(1);
        	 String CAB = rs1.getString(2);
        	 String CSV = rs1.getString(3);
        	 Map map = new HashMap();
        	 map.put("id", id);
        	 map.put("CAB", CAB);
        	 map.put("CSV", CSV);
        	 relations.put(id,map);
        }
        
        Map<String,Map> cabCharges = new HashMap<String,Map>();
        String cabChargeSql = "select id,charging_item_name,price from sa_csv_cab_payment_item ";
        PreparedStatement pstmt2 = (PreparedStatement)conn.prepareStatement(cabChargeSql);
        ResultSet rs2 = pstmt2.executeQuery();
        while(rs2.next()){
        	 String id =  rs2.getString(1);
        	 String charging_item_name = rs2.getString(2);
        	 String price = rs2.getString(3);
        	 Map map = new HashMap();
        	 map.put("id", id);
        	 map.put("charging_item_name", charging_item_name);
        	 map.put("price", price);
        	 cabCharges.put(id,map);
        }
        
        Map<String,Map> csvCharges = new HashMap<String,Map>();
        String csvChargeSql = "select id,charging_item_name,price from sa_csv_payment_item ";
        PreparedStatement pstmt3 = (PreparedStatement)conn.prepareStatement(csvChargeSql);
        ResultSet rs3 = pstmt3.executeQuery();
        while(rs3.next()){
        	 String id =  rs3.getString(1);
        	 String charging_item_name = rs3.getString(2);
        	 String price = rs3.getString(3);
        	 Map map = new HashMap();
        	 map.put("id", id);
        	 map.put("charging_item_name", charging_item_name);
        	 map.put("price", price);
        	 csvCharges.put(id,map);
        }
        
        conn.setAutoCommit(false);
	    Statement state = (Statement) conn.createStatement();
		//开始做数据
        for(Map map:records){
        	 String id =  (String) map.get("id");
        	 String member = (String) map.get("member");
        	 String catalogId = (String) map.get("catalogId");
        	 String catalogName = (String) map.get("catelogName");
        	 String money = (String) map.get("money");
        	 Date createDate = (Date) map.get("createDate");
        	 
        	 Map relation = relations.get(catalogId);
        	 String CAB = "";
        	 String CSV = "";
        	 if(relations.containsKey(catalogId)){
        		 CAB = (String) relation.get("CAB");
        		 CSV = (String) relation.get("CSV");
            	 //建档费  42,43,44,45,70
            	 //A类会员 72,47
            	 //E类会员 49,50,51,52,73
            	 //A类永久犬舍 48,74
            	 if(catalogId.equals("42")
            			 ||catalogId.equals("43")
            			 ||catalogId.equals("44")
            			 ||catalogId.equals("45")
            			 ||catalogId.equals("47")
            			 ||catalogId.equals("48")
            			 ||catalogId.equals("49")
            			 ||catalogId.equals("50")
            			 ||catalogId.equals("51")
            			 ||catalogId.equals("52")
            			 ||catalogId.equals("70")
            			 ||catalogId.equals("72")
            			 ||catalogId.equals("73")
            			 ||catalogId.equals("74")
            			 ){
            		 continue;
            	 }
            	 
            	 chaizhang(state, member,new BigDecimal(money),createDate,CSV,CAB,csvCharges,cabCharges); 
        	 }else{
        		 System.out.println(catalogId+"不支持");
        	 }

        }
        state.executeBatch();
        conn.commit();
        
	}
	
	private void chaizhang(Statement state,String member,BigDecimal countFee,Date createDate,String csv,String cab,Map csvCharges,Map cabCharges) throws SQLException{
		List<Map> list = new ArrayList<Map>();
		//将CSV的收费结果整理出来
		if(StringUtils.isNotEmpty(csv)){
			if(csv.contains(",")){
				String[] ckuArray = csv.split(",");
				for(String ckuCharge : ckuArray){
					Map map = new HashMap();
					map.put("type","csv" );
					map.put("id", ckuCharge);
					list.add(map);
				}
			}else{
				Map map = new HashMap();
				map.put("type", "csv");
				map.put("id", csv);
				list.add(map);
			}
		}
		
		//将CAB的收费结果整理出来
		if(StringUtils.isNotEmpty(cab)){
			if(cab.contains(",")){
				String[] csvArray = cab.split(",");
				for(String csvCharge : csvArray){
					Map map = new HashMap();
					map.put("type", "cab");
					map.put("id", csvCharge);
					list.add(map);
				}
			}else{
				Map map = new HashMap();
				map.put("type", "cab");
				map.put("id", cab);
				list.add(map);
			}
		}
		
		
		BigDecimal allItemMoney = new BigDecimal(0);
		BigDecimal num = new BigDecimal(0);
		for(int i=0;i<list.size();i++){
			Map map = list.get(i);
			String type = (String) map.get("type");
			String chargeId = (String) map.get("id");
			if(type.equals("csv")){
				Map csvCharge = (Map) csvCharges.get(chargeId);
				String price = (String)csvCharge.get("price");
				allItemMoney = allItemMoney.add(new BigDecimal(price));
			}else{
				Map cabCharge = (Map) cabCharges.get(chargeId);
				String price = (String)cabCharge.get("price");
				allItemMoney = allItemMoney.add(new BigDecimal(price));
			}
		}
		if(allItemMoney.compareTo(new BigDecimal(0))>0){
			num = countFee.divide(allItemMoney,0,BigDecimal.ROUND_UP);
		}else{
			num = new BigDecimal(1);
		}
		
		//分账功能
		BigDecimal totalMoney = new BigDecimal(0);
		for(int i=0;i<list.size();i++){
			Map map = list.get(i);
			String type = (String) map.get("type");
			String chargeId = (String) map.get("id");
			
			//封装数据
			SaPaymentDetail saDetail = new SaPaymentDetail();
			saDetail.setType(type);
			saDetail.setMemberCode(member);
			saDetail.setChargeDate(createDate);
			saDetail.setChargeItemId(chargeId);
			saDetail.setRunningNumber("");
			saDetail.setPaymentNum(num.abs().intValue());
			if(type.equals("csv")){
				Map csvCharge = (Map) csvCharges.get(chargeId);
				BigDecimal money = num.multiply(new BigDecimal((String)csvCharge.get("price")));
				saDetail.setPrice((String)csvCharge.get("price"));
				saDetail.setChargeItemId((String)csvCharge.get("id"));
				saDetail.setPaymentMoney(money.abs().toString());
				if(saDetail.getPrice().equals(" ")){
					System.out.println(saDetail.getChargeDate()+""+saDetail.getMemberCode());
				}
			}else{
				Map csvCharge = (Map) cabCharges.get(chargeId);
				BigDecimal money = num.multiply(new BigDecimal((String)csvCharge.get("price")));
				saDetail.setPrice((String)csvCharge.get("price"));
				saDetail.setPaymentMoney(money.abs().toString());
			}
			
			if(totalMoney.compareTo(new BigDecimal(0)) == 0){
				saDetail.setPaymentMoney(countFee.abs().toString());
				saDetail.setPrice(countFee.abs().toString());
			}
			
			String sql = "insert into sa_csv_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('"+saDetail.getChargeItemId()+"','"+saDetail.getChargeDate()+"','"+saDetail.getMemberCode()+"','"+IdGen.uuid()+"','"+saDetail.getPaymentMoney()+"','','','"+saDetail.getPrice()+"','"+type+"','"+saDetail.getPaymentNum()+"','1','2017-03-21','1','2017-03-21','0')";

			state.addBatch(sql);
		}
	}
	
	
	public void chaifenYearFee() throws SQLException{
		Connection conn = getConn();
		String sql = "select id,member,catalogId,catalogName,money,createTime from CsvAccountDetail where changeType = 0 and createTime>'2017-01-01 00:00:00' and createTime<'2017-04-30 23:59:59' and catalogId in ('42','45','72','70')";
		PreparedStatement pstmt = (PreparedStatement)conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        conn.setAutoCommit(false);
	    Statement state = (Statement) conn.createStatement();
        while(rs.next()){
        	 String id =  rs.getString(1);
        	 String member = rs.getString(2);
        	 String catalogId = rs.getString(3);
        	 String catalogName = rs.getString(4);
        	 String money = rs.getString(5);
        	 Date createDate = rs.getTimestamp(6);
        	 String chargeDate = DateUtils.formatDate(createDate,"yyyy-MM-dd HH:mm:ss");
        	 
        	 BigDecimal paymentMoney = new BigDecimal(money).abs();
        	 
        	 if(paymentMoney.compareTo(new BigDecimal(1200)) == 0){
        		 String sql1 = "insert into sa_csv_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('cab4','"+chargeDate+"','"+member+"','"+IdGen.uuid()+"','200','','','200','cab','1','1','2017-03-28','1','2017-03-28','0')";
        		 state.addBatch(sql1);
        		 
        		 PaymentRecord pr = new PaymentRecord();
        		 pr.setMemberCode(member);
        		 pr.setPaymentAmount(new BigDecimal(1000).toString());
        		 
        		 splitYearToMonth(state,pr,createDate,12,"cab5",new BigDecimal(1000));
        	 }else if(paymentMoney.compareTo(new BigDecimal(1000)) == 0){
        		 PaymentRecord pr = new PaymentRecord();
        		 pr.setMemberCode(member);
        		 pr.setPaymentAmount(new BigDecimal(1000).toString());
        		 
        		 splitYearToMonth(state,pr,createDate,12,"cab5",new BigDecimal(1000));
        	 }else if(paymentMoney.compareTo(new BigDecimal(450)) == 0){
        		 String sql1 = "insert into sa_csv_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('cab4','"+chargeDate+"','"+member+"','"+IdGen.uuid()+"','200','','','200','cab','1','1','2017-03-28','1','2017-03-28','0')";
        		 state.addBatch(sql1);
        		 
        		 PaymentRecord pr = new PaymentRecord();
        		 pr.setMemberCode(member);
        		 pr.setPaymentAmount(new BigDecimal(250).toString());
        		 splitYearToMonth(state,pr,createDate,3,"cab5",new BigDecimal(250));
        	 }else if(paymentMoney.compareTo(new BigDecimal(500)) == 0){
        		 PaymentRecord pr = new PaymentRecord();
        		 pr.setMemberCode(member);
        		 pr.setPaymentAmount(new BigDecimal(500).toString());
        		 splitYearToMonth(state,pr,createDate,6,"cab5",new BigDecimal(500));
        	 }else {
        		 //都按一年计算
        		 PaymentRecord pr = new PaymentRecord();
        		 pr.setMemberCode(member);
        		 pr.setPaymentAmount(new BigDecimal(money).toString());
        		 
        		 splitYearToMonth(state,pr,createDate,12,"cab5",new BigDecimal(1000));
        	 }
        	 
        }
        
        state.executeBatch();
        conn.commit();
	}
	public void chaifenEYearFee() throws SQLException{
		Connection conn = getConn();
		String sql = "select id,member,catalogId,catalogName,money,createTime from CsvAccountDetail where changeType = 0 and createTime>'2017-01-01 00:00:00' and createTime<'2017-04-30 23:59:59' and catalogId in ('71','73')";
		PreparedStatement pstmt = (PreparedStatement)conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        conn.setAutoCommit(false);
	    Statement state = (Statement) conn.createStatement();
        while(rs.next()){
        	 String id =  rs.getString(1);
        	 String member = rs.getString(2);
        	 String catalogId = rs.getString(3);
        	 String catalogName = rs.getString(4);
        	 String money = rs.getString(5);
        	 Date createDate = rs.getTimestamp(6);
        	 String chargeDate = DateUtils.formatDate(createDate,"yyyy-MM-dd HH:mm:ss");
        	 
        	 BigDecimal paymentMoney = new BigDecimal(money).abs();
        	 
        	 if(paymentMoney.compareTo(new BigDecimal(500)) == 0){
        		 PaymentRecord pr = new PaymentRecord();
        		 pr.setMemberCode(member);
        		 pr.setPaymentAmount(new BigDecimal(500).toString());
        		 splitYearToMonth(state,pr,createDate,12,"cab6",new BigDecimal(500));
        	 }else {
        		 //都按一年计算
        		 PaymentRecord pr = new PaymentRecord();
        		 pr.setMemberCode(member);
        		 pr.setPaymentAmount(new BigDecimal(money).toString());
        		 
        		 splitYearToMonth(state,pr,createDate,12,"cab6",new BigDecimal(500));
        	 }
        	 
        }
        
        state.executeBatch();
        conn.commit();
	}
	
	private void splitYearToMonth(Statement state,PaymentRecord paymentRecord,Date beginDate,int month,String chargeItemId,BigDecimal money) throws SQLException{
		String paymentAmount = paymentRecord.getPaymentAmount();
		BigDecimal moneyPerMonth = new BigDecimal(0);
		BigDecimal moneyLastMonth = new BigDecimal(0);
		BigDecimal pricePerMonth = new BigDecimal(0);
		BigDecimal priceLastMonth = new BigDecimal(0);
		//实缴金额为0的不处理
		if(!paymentAmount.equals("0")){
			BigDecimal realMoney = new BigDecimal(paymentAmount);
			if(realMoney.compareTo(money) >= 0){
				moneyPerMonth = money.divide(new BigDecimal(month),2,BigDecimal.ROUND_HALF_UP);
				moneyLastMonth = money.subtract(new BigDecimal(month-1).multiply(moneyPerMonth));
				
				pricePerMonth = moneyPerMonth;
				priceLastMonth = moneyLastMonth;
			}else{
				moneyPerMonth = realMoney.divide(new BigDecimal(month),2,BigDecimal.ROUND_HALF_UP);
				moneyLastMonth = realMoney.subtract(new BigDecimal(month-1).multiply(moneyPerMonth));
				
				pricePerMonth = money.divide(new BigDecimal(month),2,BigDecimal.ROUND_HALF_UP);
				priceLastMonth = money.subtract(new BigDecimal(month-1).multiply(pricePerMonth));
			}
			
			beginDate.setHours(23);
			beginDate.setMinutes(0);
			beginDate.setSeconds(0);
			Calendar ca = Calendar.getInstance();
			ca.setTime(beginDate);
			
			//存入12个月中
			for(int i=0;i<month;i++){
				SaPaymentDetail sa = new SaPaymentDetail();
				sa.setChargeItemId(chargeItemId);
				ca.add(ca.MONTH, 1);
				ca.set(Calendar.DAY_OF_MONTH, ca.getActualMaximum(Calendar.DAY_OF_MONTH));
				sa.setChargeDate(ca.getTime());
				if(i == (month-1)){
					sa.setPaymentMoney(moneyLastMonth.toString());//实收
					sa.setPrice(priceLastMonth.toString());//应收
				}else{
					sa.setPaymentMoney(moneyPerMonth.toString());
					sa.setPrice(pricePerMonth.toString());
				}
				sa.setType("cab");
				sa.setMemberCode(paymentRecord.getMemberCode());
				sa.setRunningNumber(paymentRecord.getRunningNumber());
				sa.setPaymentNum(1);
				String sql1 = "insert into sa_csv_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('"+sa.getChargeItemId()+"','"+DateUtils.formatDate(sa.getChargeDate(),"yyyy-MM-dd HH:mm:ss")+"','"+sa.getMemberCode()+"','"+IdGen.uuid()+"','"+sa.getPaymentMoney()+"','','','"+sa.getPrice()+"','cab','"+sa.getPaymentNum()+"','1','2017-03-21','1','2017-03-21','0')";

				state.addBatch(sql1);
			}
		}
	} 
	
	public  void insertChip() throws SQLException{
		Connection conn = getConn();
		String sql = "select id,member_code,charge_item_id,payment_money,charge_date from sa_csv_payment_detail where charge_item_id = 'cab22' order by id limit 1";
		PreparedStatement pstmt = (PreparedStatement)conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        conn.setAutoCommit(false);
	    Statement state = (Statement) conn.createStatement();
        while(rs.next()){
        	 String id =  rs.getString(1);
        	 String member = rs.getString(2);
        	 String catalogId = rs.getString(3);
        	 String money = rs.getString(4);
        	 Date createDate = rs.getTimestamp(5);
        	 String chargeDate = DateUtils.formatDate(createDate,"yyyy-MM-dd HH:mm:ss");
        	 
        	 BigDecimal paymentMoney = new BigDecimal(money).abs();
        	 
        	 String sql1 = "insert into sa_csv_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('"+catalogId+"','"+chargeDate+"','"+member+"','"+IdGen.uuid()+"','"+paymentMoney.toString()+"','','','"+paymentMoney.toString()+"','cab','1','1','2017-03-29','1','2017-03-29','0')";

			state.addBatch(sql1);
        	 
        }
        
        state.executeBatch();
	}
	
	public  void insertXueTong() throws SQLException{
		Connection conn = getConn();
		String sql = "select id,member,catalog_id,catalog_name,money,create_date from csv_account_detail where change_type = 0 and create_date>'2016-02-01 00:00:00' and create_date<'2016-12-31 23:59:59' and catalog_id in (7)";
		PreparedStatement pstmt = (PreparedStatement)conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        conn.setAutoCommit(false);
	    Statement state = (Statement) conn.createStatement();
        while(rs.next()){
        	 String id =  rs.getString(1);
        	 String member = rs.getString(2);
        	 String catalogId = rs.getString(3);
        	 String catalogName = rs.getString(4);
        	 String money = rs.getString(5);
        	 Date createDate = rs.getTimestamp(6);
        	 String chargeDate = DateUtils.formatDate(createDate,"yyyy-MM-dd HH:mm:ss");
        	 
        	 BigDecimal paymentMoney = new BigDecimal(money).abs();
        	 
        	 String sql1 = "insert into sa_csv_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('csv35','"+chargeDate+"','"+member+"','"+IdGen.uuid()+"','600','','','600','csv','1','1','2017-03-29','1','2017-03-29','0')";
        	 String sql2 = "insert into sa_csv_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('cab35','"+chargeDate+"','"+member+"','"+IdGen.uuid()+"','400','','','400','cab','1','1','2017-03-29','1','2017-03-29','0')";
             
			state.addBatch(sql1);
			state.addBatch(sql2);
        	 
        }
        
        state.executeBatch();
	}
	
	
	
	public static void main(String args[]) throws SQLException{
		CsvChaizhang cs = new CsvChaizhang();
		cs.chipPayment();
		cs.chaifenYearFee();
		cs.chaifenEYearFee();
	//	cs.chaifenEYearFee();
		//cs.insertXueTong();
		//cs.insertChip();
	}
	
	
	private static Connection getConn() {
	    String driver = "com.mysql.jdbc.Driver";
	    String url = "jdbc:mysql://192.168.18.152:3306/ckuoa-chaizhang2?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;
	}
}