sysbo.java

数据库管理代码 数据库管理代码 数据库管理代码

package com.soboss.dao;

import com.soboss.util.DateUtil;
import com.lzc.util.Tools;
import com.lzc.util.db.DBManager;
import com.lzc.util.db.pool.PoolControl;
import java.sql.*;
import java.util.prefs.BackingStoreException;
import sun.misc.BASE64Encoder;

public class SysBo {
	private static SysBo instance = null;

	public static SysBo getInstance() {
		if (instance == null) {
			synchronized (new Object()) {
				if (instance == null) {
					instance = new SysBo();
				}
			}
		}
		return instance;
	}

	public static void main(String[] args) throws Exception {
		SysBo sbo = SysBo.getInstance();
	}
	public String[][] getSearchWordInfoByid(String id) throws Exception {
		String[][] data = null;
		try {
			String sql = "select * from c_search_keyword where keyid=" + id;
			System.out.println(sql);
			data = DBManager.getNewInstance("szhtp160").query(sql, 10, 1);
		} catch (Exception e) {
			System.out.println("Error in getSearchWordInfoByid:" + e);
		}
		return data;
	}
	public boolean editSearchWordInfo(String[] paramVal) throws Exception {
		boolean flag = false;
		try {
			String sql = "update c_search_keyword set keycode=?,keyname=?,keytype=? where keyid=?";
			System.out.println(sql);
			flag = DBManager.getNewInstance("szhtp160").update(sql, paramVal);
		} catch (Exception e) {
			flag = false;
			System.out.println("Error in editSearchWordInfo:" + e);
		}
		return flag;
	}
	public boolean delSearchWordInfo(String[] paramVal) throws Exception {
		boolean flag = false;
		try {
			String sql = "delete c_search_keyword where keyid=?";
			flag = DBManager.getNewInstance("szhtp160").update(sql, paramVal);
		} catch (Exception e) {
			flag = false;
			System.out.println("Error in delSearchWordInfo:" + e);
		}
		return flag;
	}
	
	public String[][] getKeyTypeList() throws Exception {
		String[][] data = null;
		try {
			String sql = "select * from c_search_keytype ";
			data = DBManager.getNewInstance("szhtp160").query(sql, -1, 1);
		} catch (Exception e) {
			System.out.println("Error in getKeyTypeList:" + e);
			return null;
		}
		return data;
	}
	public String getWorkGroupByUserId(String UserId) throws Exception {

		String[][] data = null;
		String workgroup = "";
		try {
			String sql = "select UserGroup from userinfo where userId='"
					+ UserId + "'";
			data = DBManager.getNewInstance("szhtp160").query(sql, -1, 1);
			if (data.length > 0) {
				workgroup = Tools.getRsString("UserGroup", data, 1, "");
			}
			System.out.println("获取工作组:" + UserId + ":" + workgroup);
		} catch (Exception e) {
			System.out.println("Error in getWorkGroupByUserId:" + e);
			return "无";
		}
		return workgroup;
	}

	public void handleSysContent(String serialNo, String name, String info,
			String handle, String reason, String handleConti) throws Exception {
		try {
			// String sql = "update syscontent set handlestate=?,handleman=?,"
			// + "handletime=?,handledesc=?,handlereason=?,handlecontinue=?
			// where serial_no=? ";
			//
			// String time = DateUtil.getCurrentTimeString_HHMMSS();
			// String[] paraVal = new String[] { handle, name, time, info,
			// reason,
			// handleConti, serialNo };
			// DBManager.getNewInstance("szhtp").update(sql, paraVal);

			// @SERIAL_NO varchar(100),
			// @HANDLESTATE int,
			// @HANDLEMAN varchar(50),
			// @HANDLEDESC varchar(1000),
			// @HANDLEREASON varchar(50),
			// @HANDLECONTINUE int

			String sql = "exec sp_UpdateSatisfyLevel '" + serialNo + "',"
					+ handle + ",'" + name + "','" + info + "','" + reason
					+ "'," + handleConti;

			// System.out.println("sql:"+sql);

			String time = DateUtil.getCurrentTimeString_HHMMSS();
			String[] paraVal = new String[] { handle, name, time, info, reason,
					handleConti, serialNo };
			DBManager.getNewInstance("szhtp").update(sql);
		} catch (Exception e) {
			System.out.println("Error in handleSysContent:" + e);
		}
	}

	// 比较平台版本号
	public String checkVersion(String platformflag, String oldVerNo)
			throws Exception {

		String[][] data = null;
		String verno = "";
		String flag = "FALSE";
		try {
			String sql = "select verno from b_ftpconfig where platformflag="
					+ platformflag + "";
			data = DBManager.getNewInstance("szhtp160").query(sql, -1, 1);
			if (data.length > 0) {
				verno = Tools.getRsString("verno", data, 1, "");
			}
			if (!"".equals(oldVerNo) && !"".equals(verno)
					&& !verno.equals(oldVerNo)) {
				flag = "TRUE";
			} else {
				flag = "FALSE";
			}
			System.out.println("比较版本:" + oldVerNo + ":" + verno + ":" + flag);
		} catch (Exception e) {
			System.out.println("Error in checkVersion:" + e);
			return "FALSE";
		}
		return flag;
	}

	// 校验权限
	public String checkRight(String userid, String roleid) throws Exception {

		String[][] data = null;
		String flag = "FALSE";
		try {
			String sql = "select * from s_userroleid where userid='" + userid
					+ "' and roleid=" + roleid;
			data = DBManager.getNewInstance("szhtp160").query(sql, -1, 1);
			if (data.length > 0) {
				flag = "TRUE";
			} else {
				flag = "FALSE";
			}
			System.out.println("校验权限:" + userid + ":" + roleid + ":" + flag);
		} catch (Exception e) {
			System.out.println("Error in checkRight:" + e);
			return "FALSE";
		}
		return flag;
	}

	// 根据工号装载权限
	public String LoadRight(String WorkNo) throws Exception {
		String[][] data = null;
		String str = "";
		StringBuffer strBuf = new StringBuffer();
		String sql = "select distinct ActionName from s_right m,userinfo u,";
		sql += "s_roleright r where (u.userid=";
		sql += "'";
		sql += WorkNo;
		sql += "') and (m.ID=r.RightID) and ";
		sql += " (r.RoleID IN (select RoleID from s_userroleid ";
		sql += " where userid='";
		sql += WorkNo;
		sql += "'))";
		try {

			data = DBManager.getNewInstance("szhtp160").query(sql, -1, 1);
			for (int i = 1; i < data.length; i++) {
				strBuf.append(Tools.getRsString("ActionName", data, i, ""));
				strBuf.append(",");
			}
			str = strBuf.toString();
			// System.out.println(str);
		} catch (Exception e) {
			System.out.println("Error in LoadRight:" + e);
			return "";
		}
		return str;
	}

	// 加载快餐总机数据，用于便利中国
	public String loadData() throws Exception {
		String[][] data = null;
		String str = "";
		StringBuffer strBuf = new StringBuffer();
		try {
			String sql = "select * from t_comphone where transtype=1";
			data = DBManager.getNewInstance("szhtp160").query(sql, -1, 1);
			for (int i = 1; i < data.length; i++) {
				strBuf.append(Tools.getRsString("centrexid", data, i, ""));
				strBuf.append(",");
			}
			str = strBuf.toString();
			// System.out.println("快餐企业总机：" + str);
		} catch (Exception e) {
			System.out.println("Error in loadComphoneData:" + e);
			return "";
		}
		return str;
	}

	// 加载系统参数，根据参数ID
	public String getSysParameter(String ParamId, String PlatFormFlag)
			throws Exception {
		String[][] data = null;
		String str = "";
		StringBuffer strBuf = new StringBuffer();
		try {
			String sql = "select * from SYS_PARAMETER where Paramid='"
					+ ParamId + "'";
			data = DBManager.getNewInstance("szhtp160").query(sql, -1, 1);
			if (data.length > 0) {
				if ("0".equals(PlatFormFlag) || "3".equals(PlatFormFlag)) {
					strBuf.append(Tools.getRsString("paramname", data, 1, ""));
					strBuf.append("|");
					strBuf.append(Tools.getRsString("paramvalue", data, 1, ""));
				} else if ("1".equals(PlatFormFlag) || "4".equals(PlatFormFlag)) {
					strBuf.append(Tools.getRsString("paramname", data, 1, ""));
					strBuf.append("|");
					strBuf
							.append(Tools.getRsString("paramvalue2", data, 1,
									""));
				} else if ("2".equals(PlatFormFlag) || "5".equals(PlatFormFlag)) {
					strBuf.append(Tools.getRsString("paramname", data, 1, ""));
					strBuf.append("|");
					strBuf
							.append(Tools.getRsString("paramvalue3", data, 1,
									""));
				}
			}
			str = strBuf.toString();
		} catch (Exception e) {
			System.out.println("Error in loadSysParameter:" + e);
			return "";
		}
		return str;
	}

	// 加载系统参数
	public String loadAllParameter() throws Exception {
		String[][] data = null;
		String str = "";
		StringBuffer strBuf = new StringBuffer();
		try {
			String sql = "select * from SYS_PARAMETER";
			data = DBManager.getNewInstance("szhtp160").query(sql, -1, 1);
			strBuf.append("<?xml version=\"1.0\" encoding=\"gb2312\" ?>");
			strBuf.append("<Result>");
			String[] cols = data[0];
			for (int i = 1; i < data.length; i++) {
				for (int j = 0; j < cols.length; j++) {
					strBuf.append("<");
					strBuf.append(cols[j]);
					strBuf.append(">");
					strBuf.append("<![CDATA[");
					strBuf.append(Tools.getRsString(cols[j], data, i, "null"));
					strBuf.append("]]>");
					strBuf.append("</");
					strBuf.append(cols[j]);
					strBuf.append(">");
				}
			}
			strBuf.append("</Result>");
			str = strBuf.toString();
		} catch (Exception e) {
			System.out.println("Error in loadAllParameter:" + e);
			return "";
		}
		return str;
	}

	// 获取主叫被叫地址，用于便利中国
	public String getAddressByPhone(String Phone) throws Exception {
		String[][] data = null;
		String str = "";
		try {
			String sql = "select * from  GetIbData@ibss TB  where  acc_nbr ='"
					+ Phone + "'";
			data = DBManager.getNewInstance("szhtp160").query(sql, -1, 1);
			if (data.length > 0) {
				str = Tools.getRsString("addr_all", data, 1, "");
			}
			System.out.println("便利中国 电话：" + Phone + " 地址：" + str);
		} catch (Exception e) {
			System.out.println("Error in getAddressByPhone:" + e);
			return "获取地址失败！";
		}
		return str;
	}

	// 获取录音文件名，通过流水号
	public String getRecordFileBySerialNo(String SerialNo) throws Exception {
		String[][] data = null;
		String str = "";
		try {
			String sql = "select recordfilename from  v_calllog_all  where  SerialNo ='"
					+ SerialNo + "'";
			data = DBManager.getNewInstance("szhtp160").query(sql, -1, 1);
			if (data.length > 0) {
				str = Tools.getRsString("recordfilename", data, 1, "");
			}
			System.out.println("录音文件 流水号：" + SerialNo + " 录音文件：" + str);
		} catch (Exception e) {
			System.out.println("Error in getRecordFileBySerialNo:" + e);
			return "";
		}
		return str;
	}

	// 获取冠名报号语音
	public String GetGmbhVoiceFile() throws Exception {
		String[][] data = null;
		String sql = "SELECT CASE WHEN LENGTH(CONTRACTID)>=13 THEN SUBSTR(CONTRACTID,9)||'0' WHEN LENGTH(CONTRACTID)<13 THEN CONTRACTID||'0' END CONTRACTID FROM T_INC_CONTRACT_ITEM WHERE (INCTYPE='gmbh') AND (CONSTARTTIME<=SYSDATE) AND (CONENDTIME>=SYSDATE)";
		String str = "";
		try {
			data = DBManager.getNewInstance("szhtp114").query(sql, -1, 1);
			if (data.length > 0) {
				str = Tools.getRsString("CONTRACTID", data, 1, "");
			}
		} catch (Exception e) {
			System.out.println("Error in GetGmbhVoiceFile:" + e);
			return "0827630";
		}
		return str;
	}

	// 搜索词库
	public String GetSearchWordInfoSql(String keycode, String keyname,
			String keytype, String searchcount1, String searchcount2)
			throws Exception {
		String sql = "";
		try {
			if (!keycode.equals("") || !keyname.equals("")
					|| !keytype.equals("") || !searchcount1.equals("")
					|| !searchcount2.equals("")) {
				sql = GetSearchWordSql(keycode, keyname, keytype, searchcount1,
						searchcount2);
			}
		} catch (Exception e) {
			System.out.println("Error in GetSearchWordInfoSql:" + e);
		}
		return sql;
	}

	// 搜索词库
	public String GetSearchWordSql(String keycode, String keyname,
			String keytype, String searchcount1,String searchcount2) throws Exception {
		CallableStatement stmt = null;
		Connection conn = null;
		String sql = "";
		try {
			conn = PoolControl.getConnection("szhtp160");
			stmt = conn
					.prepareCall("{ call P_SOBOSS_GETSEARCHWORDINFOSQL(?,?,?,?,?,?) }");
			stmt.setString(1, keycode);
			stmt.setString(2, keyname);
			stmt.setString(3, keytype);
			stmt.setString(4, searchcount1);
			stmt.setString(5, searchcount2);
			stmt.registerOutParameter(6, oracle.jdbc.OracleTypes.VARCHAR);
			stmt.execute();
			sql = stmt.getString(6);
			System.out.println(sql);
		} catch (Exception e) {
			System.out.println("Error in GetSearchWordSql:" + e);
		} finally {
			if (stmt != null) {
				stmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		}
		return sql;
	}
	// 新增搜索关键字
	public boolean AddSearchWord(String searchword, int keytype)
			throws Exception {
		boolean flag =false;
		CallableStatement stmt = null;
		Connection conn = null;
		try {
			conn = PoolControl.getConnection("szhtp160");
			stmt = conn.prepareCall("{ call SP_ADD_SEARCHWORD(?,?) }");
			stmt.setString(1, searchword);
			stmt.setInt(2, keytype);
			flag = stmt.execute();
			System.out.println(flag);
		} catch (Exception e) {
			System.out.println("Error in AddSearchWord:" + e);
		} finally {
			if (stmt != null) {
				stmt.close();
			}
			if (conn != null) {
				conn.close();
			}
		}
		return flag;
	}
}