reckonmgmtdaojdbcimpl.java

电信用户自服务系统

package tarena.netctoss.dao.impl;

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.Collection;
import java.util.Date;
import java.util.List;
import java.util.Set;

import javax.sql.DataSource;

import tarena.netctoss.dao.IReckonMgmtDAO;
import tarena.netctoss.model.Account;
import tarena.netctoss.model.Reckon;
import tarena.netctoss.model.User;
import tarena.netctoss.model.UserService;
import tarena.netctoss.util.JdbcUtil;

public class ReckonMgmtDAOJDBCImpl implements IReckonMgmtDAO {

	private DataSource dataSource;

	public Collection pagination(int pageNumber, int pageLength, int year,
			int month) {
		// TODO Auto-generated method stub
		return null;
	}

	public Collection selectAllReckons(int year, int month) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement ps = null;
		Collection<Account> c = null;
		Statement st = null;
		ResultSet rs = null;
		List list = new ArrayList();
		Reckon rk = null;
		String login = null;
		String uip = null;
		Date d = null;
		String ip = null;
		double time = 0.0;
		String str = null;
		String sql = null;
	//	String sqls = "alter session set nls_date_format=\'yyyy mm\'";
		if(month >= 10){
			sql = "select * from detalidays_24" + year +
					" where to_char(trunc(logout_date, \'month\'))" + " = \'" + year + " " + month + "\'";
		}else{
			sql = "select * from detalidays_24" + year +
					" where to_char(trunc(logout_date, \'month\'))" + " = \'" + year + " 0" + month + "\'";
		}
		
		con = JdbcUtil.getConnection();
		try {
			con.setAutoCommit(false);
			st = con.createStatement();
		//	st.execute(sqls);
			rs = st.executeQuery(sql);
			while(rs.next()){
				login = rs.getString(1);
				uip = rs.getString(2);
				d = rs.getDate(3);
				ip = rs.getString("lab_ip");
				time = rs.getDouble("time_duration");
				rk = new Reckon();
				rk.setLoginName(login);
				rk.setLab_ip(rs.getString("lab_ip"));
				rk.setTimeDuration(time);
				list.add(rk);
			}
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}finally{
			this.release(rs, st, con);
		}
		return list;
	}

	public Collection selectLoginOutDetailByLabLoginName(String labLoginName,
			String labIp, int year, int month) {
		// TODO Auto-generated method stub
		return null;
	}

	public Collection selectReckonByLoginName(int year, int month,
			String loginName) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement ps = null;
		Collection<Account> c = null;
		Statement st = null;
		ResultSet rs = null;
		List<Reckon> list = new ArrayList();
		List<Reckon> list2 = new ArrayList();
		Reckon rk = null;
		String login = null;
		String uip = null;
		Date d = null;
		String ip = null;
		double time = 0.0;
		String str = null;
		int temp = 1;
		User user = null;
		String sql = null;
	//	String sqls = "alter session set nls_date_format=\'yyyy mm\'";
		if(month >= 10){
			sql = "select * from detalidays_24" + year +
					" where to_char(trunc(logout_date, \'month\'))" + " = \'" + year + " " + month + "\'" + " and login_name = " + "\'" + loginName + "\'";
		}else{
			sql = "select * from detalidays_24" + year +
					" where to_char(trunc(logout_date, \'month\'))" + " = \'" + year + " 0" + month + "\'" + " and login_name = " + "\'" + loginName + "\'";
		}
		String sql2 = "select u.id from lhy_netctoss_user u where u.login_name = \'" + loginName + "\'";
		con = JdbcUtil.getConnection();
		try {
			con.setAutoCommit(false);
			st = con.createStatement();
	//		st.execute(sqls);
			rs = st.executeQuery(sql);
			while(rs.next()){
				login = rs.getString(1);
				System.out.println(login);
				uip = rs.getString(2);
				System.out.println(uip);
				d = rs.getDate(3);
				System.out.println(d);
				ip = rs.getString("lab_ip");
				System.out.println(ip);
				time = rs.getDouble("time_duration");
				System.out.println(time);
				rk = new Reckon();
				rk.setLoginName(login);
				rk.setLab_ip(ip);
				rk.setTimeDuration(time);
				list.add(rk);
				System.out.println("记录次数:" + temp);
				temp++;
			}
//			JdbcUtil.release(rs);
//			double totalTime = 0.0;
//			double totalfee = 0.0;
//			for(Reckon r : list){
//				totalTime += r.getTimeDuration();
//				rs = st.executeQuery(sql2);
//				int uid = rs.getInt("login_name");
//				JdbcUtil.release(rs);
//				String sql3 = "select lab_ip, pricing_id from lhy_netctoss_user_service where user_id = " + uid;
//				rs = st.executeQuery(sql3);
//				int pid = 0;
//				while(rs.next()){
//					if(rs.getString("lab_ip").equals(r.getLab_ip())){
//						pid = rs.getInt("pricing_id");
//						break;
//					}
//				}
//				JdbcUtil.release(rs);
//				String sql4 = "select base_fee, rate_fee from lhy_netctoss_t_pricing where pricing_id = " + pid;
//				rs = st.executeQuery(sql4);
//				double basefee = rs.getDouble("base_fee");
//				double ratefee = rs.getDouble("rate_fee");
//				totalfee += basefee; 
//				totalfee += r.getTimeDuration() * ratefee;
//			}
			
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}finally{
			this.release(rs, st, con);
		}
		return list;
	}

	public Integer selectReckonCount(int year, int month) {
		// TODO Auto-generated method stub
		return null;
	}

	public Collection selectReckonDetailsByLoginName(int year, int month,
			String loginName) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement ps = null;
		Collection<Account> c = null;
		Statement st = null;
		ResultSet rs = null;
		List<Reckon> list = new ArrayList();
		List<Reckon> list2 = new ArrayList();
		Reckon rk = null;
		String login = null;
		String uip = null;
		Date d = null;
		String ip = null;
		double time = 0.0;
		String str = null;
		int temp = 1;
		User user = null;
		String sql = null;
	//	String sqls = "alter session set nls_date_format=\'yyyy mm\'";
		if(month >= 10){
			sql = "select * from detalidays_24" + year +
					" where to_char(trunc(logout_date, \'month\'))" + " = \'" + year + " " + month + "\'" + " and login_name = " + "\'" + loginName + "\'";
		}else{
			sql = "select * from detalidays_24" + year +
					" where to_char(trunc(logout_date, \'month\'))" + " = \'" + year + " 0" + month + "\'" + " and login_name = " + "\'" + loginName + "\'";
		}
		String sql2 = "select u.id from lhy_netctoss_user u where u.login_name = \'" + loginName + "\'";
		con = JdbcUtil.getConnection();
		try {
			con.setAutoCommit(false);
			st = con.createStatement();
	//		st.execute(sqls);
			rs = st.executeQuery(sql);
			while(rs.next()){
				login = rs.getString(1);
				System.out.println(login);
				uip = rs.getString(2);
				System.out.println(uip);
				d = rs.getDate(3);
				System.out.println(d);
				ip = rs.getString("lab_ip");
				System.out.println(ip);
				time = rs.getDouble("time_duration");
				System.out.println(time);
				rk = new Reckon();
				rk.setLoginName(login);
				rk.setLab_ip(ip);
				rk.setTimeDuration(time);
				list.add(rk);
				System.out.println("记录次数:" + temp);
				temp++;
			}
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}finally{
			this.release(rs, st, con);
		}
		return list;
	}

	public void release(ResultSet rs, Statement stm, Connection con) {
		if (rs != null) {
			try {
				rs.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		if (stm != null) {
			try {
				stm.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		if (con != null) {
			try {
				con.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	public DataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

}