import java.io.*;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

public class jq {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		
		String jdbc="jdbc:db2://127.0.0.1:55000/testdb";
		String user="posp_usr";
		String pass="1qaz@WSX";
		
		
		Date now=new Date();
		Calendar cal=Calendar.getInstance();
		SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMdd");
		cal.setTime(now);
		for(int i=0;i<7;i++){
		System.out.println("================="+sdf.format(cal.getTime())+"-1=================");
		saveDataToFile("./", "weekreport","================="+sdf.format(cal.getTime())+"-1=================");
		//posp激活量 
		String sql1="select count(*) from pem_usr.TBL_PEM_POS_INFO where POS_STATUS='3' and ACTIVE_TIME <'"+sdf.format(cal.getTime())+"'";
		DBselect(jdbc,user,pass,sql1);
		
		cal.add(Calendar.DATE,-1);
		
		//posp库存量 
		String sql2="select count(*) from ...";
		//posp活跃量 
		String sql3="select count(distinct MPP_TRML_ID) from ...";
		//posp交易量 包含46被扫消费和47被扫退货//-线下卡交易 
		String sql4="select count(*) from ...";
		//posp交易量 包含46被扫消费和47被扫退货//-扫码交易 
		String sql5="select count(*) from ...";
		String sql6="select count(*) from ...";
		//posp交易信息统计 
		String sql7="select count(*) from ...";

		
		DBselect(jdbc,user,pass,sql2);
		DBselect(jdbc,user,pass,sql3);
		DBselect(jdbc,user,pass,sql4);
		
		DBselect(jdbc,user,pass,sql6);
		DBselect(jdbc,user,pass,sql5);
		
		DBselect7(jdbc,user,pass,sql7);
		
		}
		
		
		
		
		

	}
	
	public static void DBselect(String jdbc,String user,String pass,String sql) {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try{
			Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
			conn=DriverManager.getConnection(jdbc,user,pass);
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()){
				saveDataToFile("./", "weekreport", rs.getString(1));
			}
			conn.close();
			ps.close();
			rs.close();
			
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	
	public static void DBselect7(String jdbc,String user,String pass,String sql) {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try{
			Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
			conn=DriverManager.getConnection(jdbc,user,pass);
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()){
				saveDataToFile("./", "weekreport", rs.getString(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3));
			}
			conn.close();
			ps.close();
			rs.close();
			
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	
	public static void saveDataToFile(String destDirName,String fileName,String data){
		BufferedWriter writer=null;
		File dir=new File(destDirName);
		
		if(!destDirName.endsWith(File.separator)){
			destDirName=destDirName+File.separator;
		}
		if(!dir.exists()){
			if(dir.mkdir()){
				System.out.println("Success!");
			}else{
				System.out.println("Failed!");
				
			}
		}
		File file=new File(destDirName+fileName+".txt");
		if(!file.exists()){
			try{
				file.createNewFile();
			}catch(IOException e){
				e.printStackTrace();
			}
		}
		
		//write
		try{
			writer=new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file,true),"UTF-8"));
			writer.write(data+"\r\n");
		}catch(IOException e){
			e.printStackTrace();
		}finally{
			try{
				if(writer!=null){
					writer.close();
				}
				
			}catch(IOException e){
				e.printStackTrace();
			}
		}
	}

	
}