reckondaoimpl.java

电信计费项目 该系统在Sun Solaris下开发,运行于Apache Tomcat平台,采用J2EE架构

package com.tarena.netctoss.model.persist;

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

import com.tarena.netctoss.model.biz.entity.ServiceUser;
import com.tarena.netctoss.model.biz.entity.User;
import com.tarena.netctoss.model.biz.entity.UserDetail;
import com.tarena.netctoss.model.biz.entity.UserSelf;
import com.tarena.util.DateUtil;
import com.tarena.util.JDBCFactoryManager;

public class ReckonDAOImpl implements IReckonDAO {

	@SuppressWarnings("unchecked")
	public List findAllByDay(String user_login_name, int year, int month) {
		Connection con = JDBCFactoryManager.getConnection();
		int days = DateUtil.getLastDay(year, month);
		PreparedStatement ps = null;
		ResultSet rs = null;
		List lists = new ArrayList();
		for (int i = 1; i < days; i++) {
			String sql = "select to_char(td.login_date,'YYYY-MM-DD HH24:MI:SS') login_time,to_char(td.logout_date,'YYYY-MM-DD HH24:MI:SS') logout_time,td.login_name,td.time_duration,a.base_fee,a.rate_fee from t_detailwdd_"
					+ i
					+ " td, "
					+ "(select tu.user_id,tus.lab_login_name,tus.lab_ip,tu.user_status ,tp.base_fee,tp.rate_fee from"
					+ " t_user_wdd tu,t_ServiceUser_wdd tus ,t_price_wdd tp where tu.user_id = tus.user_fid and tp.price_id=tus.price_fid"
					+ " and tu.login_name =?) a"
					+ " where td.login_name = a.lab_login_name";
			try {
				ps = con.prepareStatement(sql);
				ps.setString(1, user_login_name);
				rs = ps.executeQuery();
				while (rs.next()) {
					UserDetail detail = new UserDetail();
					detail
							.setLogout_date(rs
									.getString("logout_time"));
					detail
							.setLogin_date(rs
									.getString("login_time"));
					detail
							.setTime_duration(rs
									.getDouble("time_duration"));
					detail.setBase_fee(rs
							.getDouble("base_fee"));
					detail.setRate_fee(rs
							.getDouble("rate_fee"));
					detail
							.setLogin_name(rs
									.getString("login_name"));
					lists.add(detail);
				}
			} catch (SQLException e) {
				e.printStackTrace();
				JDBCFactoryManager.release(rs, ps, null);
			}
		}
		return lists;
	}

	@SuppressWarnings("unchecked")
	public List findAllByMonth(int year, int month) {
		Connection con = JDBCFactoryManager.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		List list = new ArrayList();
		String sql = "select month.time_duration,month.login_ip,a.lab_login_name,a.lab_ip,a.base_fee,a.rate_fee,a.user_id from t_monthwdd_"
				+ month
				+ " month,(select tu.user_id,tus.lab_login_name,tus.lab_ip,tp.base_fee,tp.rate_fee from "
				+ " t_user_wdd tu,t_ServiceUser_wdd tus,t_price_wdd tp where tu.user_id = tus.user_fid and tp.price_id=tus.price_fid "
				+ ") a where "
				+ "to_char(month.logout_date,'YYYY-MM') ="
				+ "'"
				+ year
				+ (month < 10 ? "-0" : "-")
				+ month
				+ "'"
				+ " and month.login_name=a.lab_login_name";
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				UserSelf self = new UserSelf();
				self.setBase_fee(rs.getDouble("base_fee"));
				self.setRate_fee(rs.getDouble("rate_fee"));
				self.setLab_ip(rs.getString("lab_ip"));
				self.setUser_id(rs.getLong("user_id"));
				self.setLogin_name(rs
						.getString("lab_login_name"));
				self.setTime_duration(rs
						.getDouble("time_duration"));
				list.add(self);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCFactoryManager.release(rs, stmt, null);
		}
		return list;
	}

	@SuppressWarnings("unchecked")
	public List findAllByMonth(String user_login_name, int year, int month) {
		Connection con = JDBCFactoryManager.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		List list = new ArrayList();
		String sql = "select month.time_duration,a.lab_login_name,a.lab_ip,a.base_fee,a.rate_fee,a.user_id from t_monthwdd_"
				+ month
				+ " month,(select tu.user_id,tus.lab_login_name,tus.lab_ip,tp.base_fee,tp.rate_fee from "
				+ " t_user_wdd tu,t_ServiceUser_wdd tus,t_price_wdd tp where tu.user_id = tus.user_fid and tp.price_id=tus.price_fid and tu.login_name =?) a where "
				+ "to_char(month.logout_date,'YYYY-MM') ="
				+ "'"
				+ year
				+ (month < 10 ? "-0" : "-")
				+ month
				+ "'"
				+ " and month.login_name=a.lab_login_name";
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, user_login_name);
			rs = ps.executeQuery();
			while (rs.next()) {
				UserSelf self = new UserSelf();
				self.setBase_fee(rs.getDouble("base_fee"));
				self.setRate_fee(rs.getDouble("rate_fee"));
				self.setLab_ip(rs.getString("lab_ip"));
				self.setUser_id(rs.getLong("user_id"));
				self.setLogin_name(rs
						.getString("lab_login_name"));
				self.setTime_duration(rs
						.getDouble("time_duration"));
				list.add(self);
			}
			System.out.println(list.isEmpty());
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCFactoryManager.release(rs, ps, null);
		}
		return list;
	}

	public List<User> findByName(String login_name) {
		Connection con = JDBCFactoryManager.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<User> users = new ArrayList<User>();
		StringBuffer sb = new StringBuffer();
		sb.append("select user_id from t_user_wdd where login_name=?");
		try {
			ps = con.prepareStatement(sb.toString());
			ps.setString(1, login_name);
			rs = ps.executeQuery();
			while (rs.next()) {
				User user = new User();
				user.setUser_id(rs.getLong("user_id"));
				user.setLogin_name(login_name);
				users.add(user);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCFactoryManager.release(rs, ps, null);
		}
		return users;
	}

	public List<User> findAll() {
		Connection con = JDBCFactoryManager.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		List<User> users = new ArrayList<User>();
		StringBuffer sb = new StringBuffer();
		sb.append("select user_id,login_name from t_user_wdd");
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sb.toString());
			while (rs.next()) {
				User user = new User();
				user.setUser_id(rs.getLong("user_id"));
				user.setLogin_name(rs.getString("login_name"));
				users.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCFactoryManager.release(rs, stmt, null);
		}
		return users;
	}

	public List<ServiceUser> findAllByName(String login_name) {
		Connection con = JDBCFactoryManager.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<ServiceUser> s_users = new ArrayList<ServiceUser>();
		String sql = "select tus.lab_login_name, tus.lab_ip, tus.service_user_id from t_user_wdd tu, t_ServiceUser_wdd tus where tu.user_id=tus.user_fid and tu.login_name=?";
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, login_name);
			rs = ps.executeQuery();
			while (rs.next()) {
				ServiceUser s_user = new ServiceUser();
				s_user.setService_user_id(rs
						.getLong("service_user_id"));
				s_user.setLab_login_name(rs
						.getString("lab_login_name"));
				s_user.setLab_ip(rs.getString("lab_ip"));
				s_users.add(s_user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCFactoryManager.release(rs, ps, null);
		}
		return s_users;
	}
}