servicetimedaoimpl.java

java电信经典-学习例子源代码

package netctoss.account.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Collection;

import netctoss.jdbc.JdbcUtil;

import netctoss.jdbc.entities.DayAccount;
import netctoss.jdbc.entities.MonthAccount;
import netctoss.jdbc.entities.YearAccount;



public class ServiceTimeDAOImpl implements ServiceTimeDAO{

	public Collection getServiceDetailByMonth(String year,String month, String labip) {
		Collection detailM = new ArrayList();
		Connection con=null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = JdbcUtil.getConnection();
			ps=con.prepareStatement(
					"select logouttime,labip,duration from detailmonths where year(logouttime)="
					+ year +" and month(logouttime)="+ month+" and labip='" + labip+"'");
	
			System.out.println("select logouttime,labip,duration from detailmonths where year(logouttime)="
					+ year +" and month(logouttime)="+ month+" and labip='" + labip+"'");
			 rs = ps.executeQuery();
			
			while(rs.next()){
				DayAccount da = new DayAccount();
				da.setLogouttime(rs.getDate(1));
				da.setLabip(rs.getString(2));
				long duration = rs.getLong(3);
				double hour =  duration/(1000*60*60);
				DecimalFormat df = new DecimalFormat("######0.00"); 
				hour = Double.parseDouble(df.format(hour));
				da.setDuration(hour);
				da.setDay(rs.getDate(1));
				detailM.add(da);
			}
		} catch (SQLException e) {
			e.printStackTrace();  
		}
		return detailM;
	}

	public Collection getServiceDetailByYear(String year,String labip) {
		Collection detailY = new ArrayList();
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = JdbcUtil.getConnection();
			ps = con.prepareStatement(
					"select logouttime,labip,duration from detailyears where year(logouttime)="+year+" and labip='"+ labip+"'");
			System.out.println("select logouttime,labip,duration from detailyears where year(logouttime)="+year+" and labip='"+ labip+"'");
			rs = ps.executeQuery();
			
			while(rs.next()){
				MonthAccount monthAccount=new MonthAccount();
				monthAccount.setLogouttime(rs.getDate(1));
				monthAccount.setLabip(rs.getString(2));
				long duration = rs.getLong(3);
				double hour =  duration/(1000*60*60);
				DecimalFormat df = new DecimalFormat("######0.00"); 
				hour = Double.parseDouble(df.format(hour));
				monthAccount.setDuration(hour);
				monthAccount.setMonth(rs.getDate(1));
				detailY.add(monthAccount);
			}
		} catch (SQLException e) {
			e.printStackTrace();  
		}finally{
			JdbcUtil.release(rs, ps, con);
		}
		return detailY;
	}

	public Collection getServiceTimeByMonth(String year,String month) {
		Collection col= new ArrayList();
		Connection con=null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = JdbcUtil.getConnection();
			ps=con.prepareStatement(
					"select logouttime,labip,sum(Duration)duration from detailmonths where year(logouttime)="
					+ year +" and month(logouttime)= "+ month +" group by labip");
			System.out.println("select logouttime,labip,sum(Duration)duration from detailmonths where year(logouttime)="
					+ year +" and month(logouttime)= "+ month +" group by labip");
			System.out.println("=====");
			rs = ps.executeQuery();
			 
			while(rs.next()){
				MonthAccount ma = new MonthAccount();
				ma.setLogouttime(rs.getDate(1));
				ma.setLabip(rs.getString(2));
				long duration = rs.getLong(3);
				double hour =  duration/(1000*60*60);
				DecimalFormat df = new DecimalFormat("######0.00"); 
				hour = Double.parseDouble(df.format(hour));
				ma.setDuration(hour);
				System.out.println(ma.getLabip()+">> " + ma.getDuration());
				col.add(ma);
			}
		} catch (SQLException e) {
			e.printStackTrace();  
		}
		return col;
	}

	public Collection getServiceTimeByYear(String year) {
		Collection col = new ArrayList();
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = JdbcUtil.getConnection();
			ps = con.prepareStatement(
					"select logouttime,labip,sum(Duration) duration from detailyears where year(logouttime)="+year +" group by labip");
			System.out.println("select logouttime,labip,duration from detailyears where year(logouttime)="+year);
			rs = ps.executeQuery();
			
			while(rs.next()){
				YearAccount yearAccount=new YearAccount();
				yearAccount.setLogouttime(rs.getDate(1));
				yearAccount.setLabip(rs.getString(2));
				long duration = rs.getLong(3);
				double hour =  duration/(1000*60*60);
				DecimalFormat df = new DecimalFormat("######0.00"); 
				hour = Double.parseDouble(df.format(hour));
				yearAccount.setDuration(hour);
				yearAccount.setYear(rs.getDate(1));
				System.out.println("duration"+duration+"  hour"+hour);
				col.add(yearAccount);
			}
		} catch (SQLException e) {
			e.printStackTrace();  
		}finally{
			JdbcUtil.release(rs, ps, con);
		}
		return col;
	}


	
	
}