clientstatmanager.java

用java开发的一个企业的DRP系统源码

package com.bjsxt.drp.statreport;

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.HashMap;
import java.util.List;
import java.util.Map;

import com.bjsxt.drp.util.DB;

public class ClientStatManager {

	private static ClientStatManager instance = new ClientStatManager();

	private ClientStatManager() {
	}

	public static ClientStatManager getInstance() {
		return instance;
	}

	public Map getClientLevelCount() {
		//注意group by的使用
		String sql = "select b.name, count(a.client_id) as qty "
				+ "from t_client a, t_data_dict b "
				+ "where a.client_level=b.id group by b.name order by b.id";
		System.out.println("ClientStatManager.getClientLevelCount()->>sql="
				+ sql);
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		Map map = new HashMap();
		try {
			conn = DB.getConn();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				map.put(rs.getString("name"), rs.getInt("qty"));
			}
		} catch (SQLException e) {
			System.out
					.println("ClientStatManager.getClientLevelCount() failure\n"
							+ e);
			// throw
		} finally {
			DB.closeRs(rs);
			DB.closeStmt(stmt);
			DB.closeRs(rs);
		}
		return map;
	}
	
	/**
	 * 获取所有的区
	 * @return
	 */
	public Map getRegions() {
		String sql = "select id, name from t_client where pid=1";
		System.out.println("ClientStatManager.getRegions()->>sql=" + sql);
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		Map map = new HashMap();
		try {
			conn = DB.getConn();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				map.put(rs.getInt("id"), rs.getString("name"));
			}
		} catch (SQLException e) {
			System.out.println("ClientStatManager.getRegions() failure\n" + e);
			// throw
		} finally {
			DB.closeRs(rs);
			DB.closeStmt(stmt);
			DB.closeRs(rs);
		}
		return map;
	}
	
	/**
	 * 根据区域取得省
	 * @param regionId
	 * @return
	 */
	public Map getProvincesByRegionId(int regionId) {
		String sql = "select id, name from t_client where pid=? order by id";
		System.out.println("ClientStatManager.getProvincesByRegionId()->>sql="
				+ sql);
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Map map = new HashMap();
		try {
			conn = DB.getConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, regionId);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				map.put(rs.getInt("id"), rs.getString("name"));
			}
		} catch (SQLException e) {
			System.out
					.println("ClientStatManager.getProvincesByRegionId() failure\n"
							+ e);
			// throw
		} finally {
			DB.closeRs(rs);
			DB.closeStmt(pstmt);
			DB.closeRs(rs);
		}
		return map;
	}	
	
}