feedaoimpl.java

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

/**
 * @author tiantian
 *
 * Jun 11, 2008 10:07:46 AM
 */
package netctoss.reckon.dao;

import global.PageParameters;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
import java.util.Vector;

import netctoss.jdbc.JdbcUtil;
import netctoss.jdbc.entities.ServReckon;
import netctoss.jdbc.entities.UserReckon;




public class FeeDAOImpl implements FeeDAO {
	/**
	 * 查询满足条件的数据记录数
	 * */
	
	public int getFeeCount(String name, int year, int month) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "";
		try {
			
			con = JdbcUtil.getConnection();
			sql+="select count(*)";
			sql+="from users u,userservices us ,pricings p,detailyears_"+year+" d ";
			sql+=" where u.name=? and  u.id = us.userid and us.loginname = d.loginname and month(d.logouttime)=? group by loginname ";		// 所有叫name用户的帐务帐号
			ps = con.prepareStatement(sql);
			ps.setString(1, name);
			ps.setInt(2, month);
			rs = ps.executeQuery();
			rs.next();
			return rs.getInt(1);
			
		} catch (SQLException e) {
			return 0;
		}finally{
			JdbcUtil.release(rs, ps, con);
		}
	}
	//明细最多31条，是否分页值得考虑
	public int getFeeDetailCount(String username,int year ,int month){
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "";
		try {
			con = JdbcUtil.getConnection();
			
			int userid = getUserIdByuserName(username);//这里不能采用用户名的方式查询
			sql +="select count(*) ";
			sql+="from pricings p, userservices u,detailmonths_"+month+" d ";
			sql+="where userid="+userid+" and p.id = u.priceid  ";
			sql+="and u.loginname = d.loginname  ";
			sql+="and year(d.logouttime)="+year;
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			rs.next();				
			return rs.getInt(1);
			
		} catch (Exception e) {
			return 0;//查询错误记录条数为0
		}finally{
			JdbcUtil.release(rs, ps, con);
		}
	}
	/**
	 * 	符合查询条件的用户的月账单,而且是分页
	 */
	public List getFeeByCondition(String name ,int year ,int month,int currentpage) {
		Connection con = null;
		Statement ps = null;
		ResultSet rs = null;
		String sql = "";
		List urs = new Vector();
		long duration = 0 ;
		double feeTitle = 0 ;
		try {
			//查询根据用户名,按业务分组.
			con = JdbcUtil.getConnection();
			sql ="";
			sql+="select u.loginname, sum(d.duration) duration,p.basefee,p.ratefee,u.name ";
			sql+="from users u,userservices us ,pricings p,detailyears_"+year+" d   ";
			sql+="where u.name like '%"+name+"%' and  u.id = us.userid and us.loginname = d.loginname  and ";
			sql+="month(d.logouttime)="+month+" group by u.loginname order by u.name  ";
			sql+="limit "+currentpage*PageParameters.ITEMS_EACH_PAGE+","+PageParameters.ITEMS_EACH_PAGE;		// 所有叫name用户的帐务帐号
			ps = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			rs = ps.executeQuery(sql);
			rs.beforeFirst();
			while(rs.next()){
				UserReckon ur = new UserReckon();
				ur.setUsername(rs.getString(1));
				duration = rs.getLong(2);
				double hour =  duration/(1000*60*60);
				DecimalFormat df = new DecimalFormat("######0.00"); 
				hour = Double.parseDouble(df.format(hour));
				feeTitle = rs.getDouble(3) + hour*rs.getDouble(4);
				ur.setFee(feeTitle);
				ur.setName(rs.getString(5));
				urs.add(ur);
			}			
			
		} catch (SQLException e) {
			e.printStackTrace();
			//不进行任何处理
			return new Vector();
		}finally{
			JdbcUtil.release(rs, ps, con);
		}
		return urs;
	}
	
	private int getUserIdByuserName(String username){
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "";
		int userid = 0;
		try {
			con = JdbcUtil.getConnection();
			sql = "select id from users where loginname like '" + username + "'";	
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()){
			  userid = rs.getInt(1);
			}
		}catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				JdbcUtil.release(rs, ps, con);
			}
			
			return userid;
			
	}
	
	/**
	 * 	查询某个用户的所有业务帐号的账单
	 * @param userid
	 * @return
	 */
	//查询某个帐务帐号的所有业务的明细的所有日明细
	/**
	 * @param user 是帐务帐号
	 * @param year 查询的年
	 * @param month 查询的月
	 * @param currentpage 分页查询的当前页
	 * */
	public List getFeeDetail(String username,int year ,int month,int currentpage){
		List srs = new Vector();
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "";
		long duration = 0;
		double fee = 0;
		try {
			con = JdbcUtil.getConnection();
			
			int userid = getUserIdByuserName(username);//这里不能采用用户名的方式查询
			sql +="select u.loginname,u.labip,p.name,p.basefee,p.ratefee,d.duration,d.logouttime ";
			sql+="from pricings p, userservices u,detailmonths_"+month+" d ";
			sql+="where userid="+userid+" and p.id = u.priceid  ";
			sql+="and u.loginname = d.loginname  ";
			sql+="and year(d.logouttime)="+year+" ";
			sql+="limit "+currentpage*PageParameters.ITEMS_EACH_PAGE+","+PageParameters.ITEMS_EACH_PAGE;
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()){
				ServReckon sr = new ServReckon();
				sr.setLoginname(rs.getString(1));
				sr.setLabip(rs.getString(2));
				sr.setPricename(rs.getString(3));
				duration = rs.getLong(6);
				double hour =  duration/(1000*60*60);
				DecimalFormat df = new DecimalFormat("######0.00"); 
				hour = Double.parseDouble(df.format(hour));
				fee = rs.getDouble(4) + hour* rs.getDouble(5);
				sr.setFee(fee);
				sr.setDuration(hour);
				sr.setLogouttime(new Date((rs.getTimestamp(7)).getTime()));
				srs.add(sr);
			}
			
		} catch (Exception e) {
			new Vector();
		}finally{
			JdbcUtil.release(rs, ps, con);
		}		
		return srs;
	}
}