package com.cku.checker;

import java.io.IOException;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;

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

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

	 * @throws SQLException 
	 */
	public void createChip() throws SQLException{
		//从数据库中取出来2401条记录，
		Map<String,String> originalChargingItem = new HashMap<String,String>();
		Connection conn = getConn();
		String sql = "select id,running_number from sa_payment_detail where charge_date>'2015-01-01' and charge_date<'2015-12-31' and charge_item_id = 'cab24' order by id limit 2401";
		PreparedStatement pstmt = (PreparedStatement)conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        
        conn.setAutoCommit(false);
	    Statement state = (Statement) conn.createStatement();
	    String youhuiId = "";
	    
        while(rs.next()){
        	 String id =  rs.getString(1);
        	 String running_number = rs.getString(2);
        	 youhuiId = id;
        	 String updateSql = "update sa_payment_detail set payment_num = (payment_num+1) where id = '"+id+"'";
        	// state.addBatch(updateSql);
        }
        
        String sql2 = "select id,running_number from sa_payment_detail where charge_date>'2015-01-01' and charge_date<'2015-12-31' and charge_item_id = 'cab23' order by id limit 2401";
        PreparedStatement pstmt2 = (PreparedStatement)conn.prepareStatement(sql2);
        ResultSet rs2 = pstmt2.executeQuery();
        
	    
        while(rs2.next()){
        	 String id =  rs2.getString(1);
        	 String running_number = rs2.getString(2);
        	 String updateSql = "update sa_payment_detail set payment_num = (payment_num+1) where id = '"+id+"'";
        	// state.addBatch(updateSql);
        }
        
        //抹平所有的芯片费
        String upSql = "update sa_payment_detail set payment_money = (payment_num*50) where charge_item_id = 'cab24' and charge_date>'2015-01-01' and charge_date<'2015-12-31'";
        String upSql2 = "update sa_payment_detail set payment_money = (payment_num*100) where charge_item_id = 'cab23' and  charge_date>'2015-01-01' and charge_date<'2015-12-31'";
        
        //计算出24块的优惠
        String youhui = "update sa_payment_detail set payment_money = (payment_money-24) where id = '"+youhuiId+"'";
        state.addBatch(upSql);
        state.addBatch(upSql2);
        state.addBatch(youhui);
        state.executeBatch();
        conn.close();
	}
	
	
	/**

	 * @throws SQLException 
	 */
	public void addNum() throws SQLException{
		//从数据库中取出来2401条记录，
		Map<String,String> originalChargingItem = new HashMap<String,String>();
		Connection conn = getConn();
		String sql = "select id,running_number,payment_num from sa_payment_detail where charge_item_id = 'cab22'  and type = 'cab' and payment_num > 4 order by id limit 60 ";
		PreparedStatement pstmt = (PreparedStatement)conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        
        conn.setAutoCommit(false);
	    Statement state = (Statement) conn.createStatement();
	    
	    int calc = 0;
        while(rs.next()){
        	 String id =  rs.getString(1);
        	 String running_number = rs.getString(2);
        	 String payment_num = rs.getString(3);
    		 String updateSql = "update sa_payment_detail set payment_num = (payment_num+"+1+") where id = '"+id+"'";
    		 state.addBatch(updateSql);
        	 
        	 calc++;
        }
        System.out.println(calc);
        
       String upSql = "update sa_payment_detail set payment_money = (payment_num*price) where charge_item_id = 'cab22' ";
       state.addBatch(upSql);
        state.executeBatch();
        conn.close();
	}
	
	/**
	 * 删除用户管理部的退款项
	 * 用户管理项的收入抹平
	 * @throws SQLException 
	 */
	public void deleteTuikuan() throws SQLException{
		Connection conn = getConn();
		Statement state = (Statement) conn.createStatement();
		state.execute("delete from sa_payment_detail where charge_item_id = 'cku17'");
		//信息变更服务费抹平
		state.execute("update sa_payment_detail set payment_money = (payment_num*price) where charge_item_id = 'cab11'");
		//用户管理部的各种费用抹平
		state.execute("update sa_payment_detail set payment_money = (payment_num*price) where charge_item_id = 'cab9'");
		state.execute("update sa_payment_detail set payment_money = (payment_num*price) where charge_item_id = 'cab4'");
		state.execute("update sa_payment_detail set payment_money = (payment_num*price) where charge_item_id = 'cab6'");
		state.execute("update sa_payment_detail set payment_money = (payment_num*price) where charge_item_id = 'cab8'");
		conn.close();
	}
	
	/**
	 * 赛事部添加赛事服务费
	 */
	public void showFee(){
	}
	

	
	/**
	 * 魔力水
	 * @throws SQLException 
	 */
	public void magicWaterFee() throws SQLException{
		
		Connection conn = getConn();
		Statement state = (Statement) conn.createStatement();
		conn.setAutoCommit(false);
		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('molipian','2015-01-31','','"+IdGen.uuid()+"','400','20150204135428316','','250','cab','2','1','2017-03-21','1','2017-03-21',0)";
		String sql2 = "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('molipian','2015-02-28','','"+IdGen.uuid()+"','570','20150303150918866','','250','cab','3','1','2017-03-21','1','2017-03-21',0)";
		String sql3 = "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('molipian','2015-03-31','','"+IdGen.uuid()+"','190','20150318093851020','','250','cab','1','1','2017-03-21','1','2017-03-21',0)";
		String sql4 = "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('molipian','2015-08-26','','"+IdGen.uuid()+"','1000','20150204135428316','','250','cab','4','1','2017-03-21','1','2017-03-21',0)";
		String sql5 = "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('molishui3','2015-01-31','','"+IdGen.uuid()+"','540','20150204135428316','','540','cab','1','1','2017-03-21','1','2017-03-21',0)";
		String sql6 = "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('molishui3','2015-11-30','','"+IdGen.uuid()+"','2520','20151123110148083','','540','cab','12','1','2017-03-21','1','2017-03-21',0)";
		String sql7 = "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('molishui3','2015-05-12','','"+IdGen.uuid()+"','2376','2015051211461409','','540','cab','12','1','2017-03-21','1','2017-03-21',0)";
		String sql8 = "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('molishui1','2015-05-12','','"+IdGen.uuid()+"','790','2015051211461409','','510','cab','2','1','2017-03-21','1','2017-03-21',0)";
		String sql9 = "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('molishui1','2015-11-30','','"+IdGen.uuid()+"','1020','20151123110148083','','510','cab','2','1','2017-03-21','1','2017-03-21',0)";
		String sql10 = "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('molifen','2015-12-11','','"+IdGen.uuid()+"','400','20151211141403411','','220','cab','60','1','2017-03-21','1','2017-03-21',0)";
		String sql11 = "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('molifen','2015-08-26','','"+IdGen.uuid()+"','660','20150826093601597','','220','cab','3','1','2017-03-21','1','2017-03-21',0)";

		
		
		state.addBatch(sql1);
		state.addBatch(sql2);
		state.addBatch(sql3);
		state.addBatch(sql4);
		state.addBatch(sql5);
		state.addBatch(sql6);
		state.addBatch(sql7);
		state.addBatch(sql8);
		state.addBatch(sql9);
		state.addBatch(sql10);
		state.addBatch(sql11);
		state.executeBatch();
		conn.commit();
	}
	
	public static void main(String args[]){
		CreateData2015 cd2 = new CreateData2015();
		try {
//			cd2.addNum();
//			cd2.deleteTuikuan();
//			cd2.magicWaterFee();
			cd2.createChip();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			
			e.printStackTrace();
		}
	}
	
	
	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;
	}
}