dms.rar_dms_dms java

package com.tarena.dms20.server; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.text.SimpleDateFormat; import java.util.Calendar; import java.util.GregorianCalendar; import com.tarena.dms20.exceptions.serverException.DataCombinateException; import tarena.util.DBUtil; //数据整合类：封装数据整合的业务逻辑 /** * 该类封装数据整合的业务逻辑 * Connection conn ：连接数据库的Connection * @author tarena02 * */ public class DataCombinator { private Connection conn;//连接数据库的Connection public DataCombinator() throws Exception { conn=DBUtil.openInThread(); } //读取用户登录明细表数据，按照既定规则整合出用户登录记录日报表 /** * 方法原型 : public void combineDayData() throws CombinateDataException * 方法功能 : 读取用户登录明细表数据，按照既定规则整合出用户登录记录日报表 * 参数说明: 无 * 返回类型: void * 抛出异常:DataCombinateException 数据整合时发生的异常 * @throws DataCombinateException */ public void combineDayData() throws DataCombinateException { PreparedStatement pstmt=null; ResultSet rs=null; Calendar day=new GregorianCalendar(1969,11,26); SimpleDateFormat f=new SimpleDateFormat("yyyyMMdd"); SimpleDateFormat f1=new SimpleDateFormat("yyyyMM"); day.set(Calendar.HOUR_OF_DAY, 0); day.set(Calendar.MINUTE, 0); day.set(Calendar.SECOND, 0); Long time2=day.getTimeInMillis(); day.add(Calendar.DATE, -1); Long time1=day.getTimeInMillis(); String month=f1.format(day.getTime()); String table_name=f.format(day.getTimeInMillis()); System.out.println("开始整合日报表................"); try { conn=DBUtil.openInThread(); pstmt=conn.prepareStatement("use dms"); pstmt.execute(); String sql_creat=//建立空的日报表 "create table logs_day_"+table_name + " (logname varchar(20)," + "labip varchar(15)," + "DAY_TIME numeric(12) not null," + "month varchar(20)," + "constraint logs_day_pk primary key(logname,labip))"; pstmt=conn.prepareStatement(sql_creat); pstmt.execute(); //System.out.println("logs_day_"+table_name+"空的日报表建立成功"); //－－－－－－－－－－－－－－－－－－－－－－－－－－－ String sql_total=//从用户登录明细表中查询前一天的记录 "select logname,labip,sum(durations) DAY_TIME "+ "from logs where logintime>=? and logintime<? group by logname,labip"; pstmt=conn.prepareStatement(sql_total); pstmt.setLong(1, time1); pstmt.setLong(2, time2); rs=pstmt.executeQuery(); //－－－－－－－－－－－－－－－－－－－－－－－－－－－ String sql_insert=//把查询结果插入日报表中 "insert into logs_day_"+table_name+ "(logname,labip,DAY_TIME,month) values(?,?,?,?)"; int n=0; pstmt=conn.prepareStatement(sql_insert); while(rs.next()) {n++; pstmt.setString(1, rs.getString("logname")); pstmt.setString(2, rs.getString("labip")); pstmt.setLong(3, rs.getLong("DAY_TIME")); pstmt.setString(4, month); pstmt.addBatch(); } pstmt.executeBatch(); RizhiWriter.writeRizhi("logs_day_"+table_name, n); System.out.println("logs_day_"+table_name+":日报表整合成功"); }catch(Exception e) { throw new DataCombinateException("整合日报表失败"); } finally { try{pstmt.close();}catch(Exception e){} try{DBUtil.closeInThread();}catch(Exception e){}; } } //读取用户登录记录日报表，按照既定规则整合出用户登录记录月报表 /** * 方法原型: public void combineMonthData() throws CombinateDataException * 方法功能:读取用户登录记录日报表，按照既定规则整合出用户登录记录月报表 * 参数说明: 无 * 返回类型: void * 抛出异常:DataCombinateException 数据整合时发生的异常 * * @throws DataCombinateException */ public void combineMonthData() throws DataCombinateException { PreparedStatement pstmt=null; ResultSet rs=null; int month_start = 0; Calendar month1=new GregorianCalendar(1970,0,1); SimpleDateFormat fday=new SimpleDateFormat("yyyyMMdd"); SimpleDateFormat fmonth=new SimpleDateFormat("yyyyMM"); SimpleDateFormat fyear=new SimpleDateFormat("yyyy"); month1.set(Calendar.DATE, 1); month1.set(Calendar.HOUR_OF_DAY, 0); month1.set(Calendar.MINUTE, 0); month1.set(Calendar.SECOND, 0); month1.add(Calendar.MONTH, -1); String year=fyear.format(month1.getTime()); String table_name=fmonth.format(month1.getTimeInMillis()); month_start=Integer.parseInt(fday.format(month1.getTime())); System.out.println("开始整合月报表................"); try { conn=DBUtil.openInThread(); pstmt=conn.prepareStatement("use dms"); pstmt.execute(); String sql_creat=//建立空的月报表 "create table logs_month_"+table_name + " (logname varchar(20)," + "labip varchar(15)," + "MONTH_TIME numeric(12) not null," + "year varchar(20)," + "constraint logs_month_pk primary key(logname,labip))"; pstmt=conn.prepareStatement(sql_creat); pstmt.execute(); //System.out.println("建立空的月报表：执行成功"); //－－－－－－－－－－－－－－－－－－－－－－－－－－－ String sql_tempory=//建立空的临时月报表 //"create temporary table temp_month" "create table temp_month" + "(logname varchar(20)," + "labip varchar(15)," + "day_time numeric(12) not null)" ; pstmt=conn.prepareStatement(sql_tempory); pstmt.execute(); //System.out.println("建立空的临时月报表：执行成功"); //－－－－－－－－－－－－－－－－－－－－－－－－－－－ //把该月每日报表记录插入到临时月报表中 int n=month_start; for(int i=0;i<31;i++) { try { String name="logs_day_"+n; //System.out.println(name); pstmt=conn.prepareStatement( "insert into temp_month" + "(logname,labip,day_time) " + "select logname,labip,DAY_TIME from "+name+" where month='"+table_name+"'"); n++; pstmt.addBatch(); pstmt.executeBatch(); //System.out.println("日表插入成功"); } catch(Exception e) { continue; } } //－－－－－－－－－－－－－－－－－－－－－－－－－－－ String sql_insert_month=//把该月的汇总插入到月报表中 "insert into logs_month_"+table_name+ "(logname,labip,MONTH_TIME,year) " + "select logname,labip,sum(day_time),'"+year+ "'from temp_month group by logname,labip"; pstmt=conn.prepareStatement(sql_insert_month); pstmt.addBatch(); pstmt.executeBatch(); pstmt=conn.prepareStatement("select count(*) from logs_month_"+table_name); rs=pstmt.executeQuery(); rs.next(); RizhiWriter.writeRizhi("logs_month_"+table_name, rs.getInt(1)); System.out.println("logs_month_"+table_name+":月报表整合成功"); }catch(Exception e) { throw new DataCombinateException("整合月报表失败");} finally { try{pstmt.close();}catch(Exception e){} try{DBUtil.closeInThread();}catch(Exception e){}; } } //读取用户登录记录月报表，按照既定规则整合出用户登录记录年报表 /** * 方法原型: public void combineYearData() throws CombinateDataException * 方法功能: 读取用户登录记录月报表，按照既定规则整合出用户登录记录年报表 * 参数说明: 无 * 返回类型: void * 抛出异常:DataCombinateException 数据整合时发生的异常 * * @throws DataCombinateException */ public void combineYearData() throws DataCombinateException { PreparedStatement pstmt=null; Calendar year1=new GregorianCalendar(1970,0,1); SimpleDateFormat fmonth=new SimpleDateFormat("yyyyMM"); SimpleDateFormat fyear=new SimpleDateFormat("yyyy"); year1.set(Calendar.MONTH, 0); year1.set(Calendar.DATE, 1); year1.add(Calendar.YEAR, -1); String table_name=fyear.format(year1.getTimeInMillis()); int year_start=Integer.parseInt(fmonth.format(year1.getTime())); String year=fyear.format(year1.getTime()); System.out.println("开始整合年报表................"); try { conn=DBUtil.openInThread(); pstmt=conn.prepareStatement("use dms"); pstmt.execute(); String sql_creat=//建立空的年报表 "create table logs_year_"+table_name + "(logname varch