package com.cku.checker;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

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

public class 更新年费生效与缴费时间 {
	
	public static void doWork() throws SQLException{
		String sql = "select *,max(charge_date) as zuida,min(charge_date) as zuixiao from sa_payment_detail where create_date>'2017-09-01'"
				+ "and charge_item_id in ('cab4','cab7','cab9','cab10','cab74','cab75','cab80','cab97','cab98') and type ='cab' "
				+ "group by running_number";
		PreparedStatement p = getConn().prepareStatement(sql);
		ResultSet result = p.executeQuery();
		int i =0;
		
		Connection conn =getConn();
		conn.setAutoCommit(false);
	    Statement state = (Statement) conn.createStatement();
		while(result.next()){
			Date zuida = result.getTimestamp("zuida");
			Date zuixiao = result.getTimestamp("zuixiao");
			Date createDate = result.getTimestamp("create_date");
			String runningNumber = result.getString("running_number");
			System.out.println((i++)+":"+DateUtils.formatDateTime(zuida)+"----"+DateUtils.formatDate(zuixiao)+"------"+DateUtils.formatDateTime(createDate));
			String updateSql = "update sa_payment_detail set payment_time='" + DateUtils.formatDateTime(zuixiao) +"' where running_number = '"+runningNumber+"' and charge_item_id in ('cab4','cab7','cab9','cab10','cab74','cab75','cab80','cab97','cab98')";
			state.addBatch(updateSql);
		}
		
		state.executeBatch();
 	    conn.commit();
	}

	
	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;
	}
	
	public static void main(String args[]){
		try {
			doWork();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}