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.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;

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

/**
 * 2016年的数据创建
 * @author fanhuibin
 *
 */
public class CreateData2016 {
	
	/**

	 * @throws SQLException 
	 */
	public void createChip() throws SQLException{
		//从数据库中取出来943条记录，
		Map<String,String> originalChargingItem = new HashMap<String,String>();
		Connection conn = getConn();
		String sql = "select id,running_number from sa_payment_detail where charge_date>'2016-01-01' and charge_date<'2016-12-31' and charge_item_id = 'cab24' order by id limit 943";
		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>'2016-01-01' and charge_date<'2016-12-31' and charge_item_id = 'cab23' order by id limit 943";
        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'";
        String upSql2 = "update sa_payment_detail set payment_money = (payment_num*100) where charge_item_id = 'cab23'";
        
        //计算出24块的优惠
        String youhui = "update sa_payment_detail set payment_money = (payment_money-12) where id = '"+youhuiId+"'";
        state.addBatch(upSql);
        state.addBatch(upSql2);
        state.addBatch(youhui);
        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("delete from sa_payment_detail where charge_item_id = 'cab103'");
		state.execute("delete from sa_payment_detail where charge_item_id = 'cab17'");
		state.execute("delete from sa_payment_detail where charge_item_id = 'cab96'");
		
		//赛事服务费的应收和实收一样
		conn.close();
	}
	
	/**
	 * 增加20w的电子出生纸
	 * @throws SQLException 
	 */
	public void addNum() throws SQLException{
		//从数据库中取出来943条记录，
		Connection conn = getConn();
		String sql = "select id,running_number,member_code,payment_num,charge_date from sa_payment_detail where charge_date>'2016-01-01' and type='cab' and charge_date<'2016-12-31' and charge_item_id = 'cab22'";
		PreparedStatement pstmt = (PreparedStatement)conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        
        conn.setAutoCommit(false);
	    Statement state = (Statement) conn.createStatement();
	    
	    List<String> members = new ArrayList<String>();
	    List<String> nums = new ArrayList<String>();
	    List<Date>  dates= new ArrayList<Date>();
	    
        while(rs.next()){
        	 String id =  rs.getString(1);
        	 String running_number = rs.getString(2);
        	 String memberCode = rs.getString(3);
        	 String payment_num = rs.getString(4);
        	 Date date = rs.getTimestamp(5);
        	 members.add(memberCode);
        	 nums.add(payment_num);
        	 dates.add(date);
        }
        
        Collections.shuffle(members);
        Collections.shuffle(nums);
        Collections.shuffle(dates);
        int total=14708;
        
        for(int i=0;i<members.size();i++){
        	String memberCode = members.get(i);
        	Date date = dates.get(i);
        	String num = nums.get(i);
        	Random r = new Random();
        	long rNum = Long.valueOf(r.nextInt(1000)*60*60);
        	System.out.println(rNum+""+ date.getTime()+DateUtils.formatDate(date, "yyyy-MM-dd HH:mm:ss"));
        	date = new Date(date.getTime()+rNum);
        	System.out.println("-----------"+date.getTime()+DateUtils.formatDate(date, "yyyy-MM-dd HH:mm:ss"));
        	int money = Integer.parseInt(num)*50;
        	System.out.println();
        	if(!DateUtils.formatDate(date, "yyyy").equals("2016")){
        		System.out.println("到"+DateUtils.formatDate(date, "yyyy"));
        		date = dates.get(i);
        	}
        	 String dateStr = DateUtils.formatDate(date,"yyMMddHHmmss");
             java.util.Random random=new java.util.Random();// 定义随机类
             int result=random.nextInt(100);
             DecimalFormat df = new DecimalFormat("00");
             String ra = df.format(result);
             String runningNum = dateStr+ra+"@"+memberCode;
        	total = total-Integer.valueOf(num);
        	if(total<0){
        		break;
        	}
        	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('cab22','"+DateUtils.formatDate(date, "yyyy-MM-dd HH:mm:ss")+"','"+memberCode+"','"+IdGen.uuid()+"','"+money+"','"+runningNum+"','','"+50+"','cab','"+num+"','1','2017-03-26','1','2017-03-26',0)";
        	
        	state.addBatch(sql1);
        }
        
        state.executeBatch();
        conn.close();
	}
	

	
	/**
	 * 魔力水
	 * @throws SQLException 
	 */
	public void magicWaterFee() throws SQLException{
		
		
	}
	
	public static void main(String args[]){
		CreateData2016 cd2 = new CreateData2016();
		try {
		//	cd2.deleteTuikuan();
//			cd2.magicWaterFee();
			//cd2.createChip();
			cd2.addNum();
		} 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;
	}
}