syscontent.java

日志管理源代码

			if (!timeBegin.equals("") && timeEnd.equals("")) {
				querySql += "and a.mod_date > '" + timeBegin + "' ";
			}
			if (timeBegin.equals("") && !timeEnd.equals("")) {
				querySql += "and a.mod_date < '" + timeEnd + "' ";
			}
			if (!handle.trim().equals("")) {
				querySql += "and a.handlestate  = '" + handle + "' ";
			}
			if (!handleUser.trim().equals("")) {
				querySql += "and a.handleMan  like '" + handleUser + "%' ";
			}
			if (!handleBTime.equals("") && !handleETime.equals("")) {
				querySql += "and a.handletime between '" + handleBTime
						+ "' and '" + handleETime + "' ";
			}
			if (!handleBTime.equals("") && handleETime.equals("")) {
				querySql += "and a.handletime > '" + handleBTime + "' ";
			}
			if (handleBTime.equals("") && !handleETime.equals("")) {
				querySql += "and a.handletime < '" + handleETime + "' ";
			}
			if (!findtime.equals("")) {
				querySql += "and a.findtime >= " + findtime;
			}
			if (!userName.trim().equals("")) {
				querySql += "and b.username  like '" + userName + "%' ";
			}
			if (!userType.trim().equals("")) {
				querySql += "and b.usertype  like '" + userType + "%' ";
			}
			if (!userGroup.trim().equals("")) {
				querySql += "and b.usergroup  like '" + userGroup + "%' ";
			}
			if (!userSkill.trim().equals("")) {
				querySql += "and b.userSkill  like '" + userSkill + "%' ";
			}
			if (!querySql.equals("")) {
				sql = sql + querySql;
			}
			String sort = "时间";
			if ("mod_date".equalsIgnoreCase(sortID)) {
				sort = "时间";
			} else if ("serial_no".equalsIgnoreCase(sortID)) {
				sort = "坐席流水号";
			} else if ("staff_id".equalsIgnoreCase(sortID)) {
				sort = "工号";
			} else if ("state".equalsIgnoreCase(sortID)) {
				sort = "满意度";
			}else if ("findtime".equalsIgnoreCase(sortID)) {
				sort = "通话时长";
			}
			
			sql += " order by " + sort + " " + sortOrder;
			//System.out.println("do export sql:"+ sql);
		} catch (Exception e) {
			System.out.println("Error in getSysContentSql:" + e);
		}
		return sql;
	}

	// 查找syscontent表
	public String getSysContentSqlToOutFile(String serial_No, String phoneNbr,
			String staffId, String state, String timeBegin, String timeEnd,
			String serialNo, String userName, String userType,
			String userGroup, String userSkill, String findtime, String sortID,
			String sortOrder) throws Exception {

		// String sql = "select * from syscontent ";V_SatisfyLevel
		// String sql = "SELECT isnull(a.SERIALNO,'') 业务流水号,a.serial_no 坐席流水号,"
		// + "a.STAFF_ID 工号,b.UserName 姓名,a.PHONE_NBR 主叫号码,"
		// + "case a.state when 0 then '满意' when 1 then '基本满意' "
		// + "when 2 then '不满意' end 满意度 ," + "b.UserGroup 班组,"
		// + "b.UserType 岗位技能,b.UserSkill 队列技能," + "a.MOD_DATE 时间 "
		// + "FROM syscontent a INNER JOIN "
		// + "UserInfo b ON a.STAFF_ID = b.UserID ";
		String sql = "SELECT a.serial_no 坐席流水号,isnull(a.SERIALNO,'') 业务流水号,"
				+ "a.STAFF_ID 工号,b.UserName 姓名,a.PHONE_NBR 主叫号码,"
				+ "case a.state when 0 then '满意' when 1 then '基本满意' "
				+ "when 2 then '不满意' end 满意度 ," + "b.UserGroup 班组,"
				+ "b.UserType 岗位技能,b.UserSkill 队列技能,a.MOD_DATE 受理时间,"
				+ "case a.handlestate when 0 then '未处理' when 1 then '已处理' when 2 then '已报无' when 3 then '已放音' end 处理状态,isnull(a.handleman,'') 处理人,isnull(a.findtime,'0') 通话时长  "
				+ "FROM V_SatisfyLevel a INNER JOIN "
				+ "UserInfo b ON a.STAFF_ID = b.UserID ";
		try {
			String querySql = "";
			if (!timeEnd.trim().equals("")) {
				timeEnd = DateUtil.addlongDate(timeEnd, 1);
			}
			if (!serial_No.trim().equals("")) {
				querySql += "and a.serial_no  like '" + serial_No + "%' ";
			}
			if (!serialNo.trim().equals("")) {
				querySql += "and a.serialno  like '" + serialNo + "%' ";
			}
			if (!phoneNbr.trim().equals("")) {
				querySql += "and a.phone_nbr  like '" + phoneNbr + "%' ";
			}
			if (!staffId.trim().equals("")) {
				querySql += "and a.staff_id ='" + staffId + "' ";
			}
			if (!findtime.trim().equals("")) {
				querySql += "and a.findtime >=" + findtime;
			}
			if (!state.trim().equals("")) {
				querySql += "and a.state =" + Integer.parseInt(state) + " ";
			}
			if (!timeBegin.equals("") && !timeEnd.equals("")) {
				querySql += "and a.mod_date between '" + timeBegin + "' and '"
						+ timeEnd + "' ";
			}
			if (!timeBegin.equals("") && timeEnd.equals("")) {
				querySql += "and a.mod_date > '" + timeBegin + "' ";
			}
			if (timeBegin.equals("") && !timeEnd.equals("")) {
				querySql += "and a.mod_date < '" + timeEnd + "' ";
			}
			if (!userName.trim().equals("")) {
				querySql += "and b.username  like '" + userName + "%' ";
			}
			if (!userType.trim().equals("")) {
				querySql += "and b.usertype  like '" + userType + "%' ";
			}
			if (!userGroup.trim().equals("")) {
				querySql += "and b.usergroup  like '" + userGroup + "%' ";
			}
			if (!userSkill.trim().equals("")) {
				querySql += "and b.userSkill  like '" + userSkill + "%' ";
			}
			if (!querySql.equals("")) {
				querySql = querySql.substring(4, querySql.length());
				querySql = " where " + querySql;
				sql = sql + querySql;
			}
			String sort = "时间";
			if ("mod_date".equalsIgnoreCase(sortID)) {
				sort = "时间";
			} else if ("serial_no".equalsIgnoreCase(sortID)) {
				sort = "坐席流水号";
			} else if ("staff_id".equalsIgnoreCase(sortID)) {
				sort = "工号";
			} else if ("state".equalsIgnoreCase(sortID)) {
				sort = "满意度";
			}else if ("findtime".equalsIgnoreCase(sortID)) {
				sort = "通话时长";
			}
			sql += " order by " + sort + " " + sortOrder;
			// System.out.println("sql"+ sql);
		} catch (Exception e) {
			System.out.println("Error in getSysContentSql:" + e);
		}
		return sql;
	}

	// 查找满意度处理统计
	public String getStatHandleContentSql(String phoneNbr, String staffId,
			String state, String handleBTime, String handleETime,
			String serialNo, String handleUser) throws Exception {
		String sql = "SELECT a.SERIALNO 业务流水号,"
				+ "a.STAFF_ID 工号,b.UserName 姓名,"
				+ "b.UserGroup 班组,a.handlereason 原因,"
				+ "isnull(a.handledesc,'') 内容,a.handleman 处理人,"
				+ "a.handletime 处理时间, "
				+ "case a.handlecontinue when 1 then '是' when 0 then '否' end 是否后续处理 "
				+ "FROM V_SatisfyLevel a INNER JOIN "
				+ "UserInfo b ON a.STAFF_ID = b.UserID "
				+ "where a.handlestate<>0 ";
		try {
			String querySql = "";
			if (!handleETime.trim().equals("")) {
				handleETime = DateUtil.addlongDate(handleETime, 1);
			}
			if (!serialNo.trim().equals("")) {
				querySql += "and a.serialno  like '" + serialNo + "%' ";
			}
			if (!phoneNbr.trim().equals("")) {
				querySql += "and a.phone_nbr  like '" + phoneNbr + "%' ";
			}
			if (!staffId.trim().equals("")) {
				querySql += "and a.staff_id ='" + staffId + "' ";
			}
			if (!state.trim().equals("")) {
				querySql += "and a.state =" + Integer.parseInt(state) + " ";
			}
			if (!handleUser.trim().equals("")) {
				querySql += "and a.handleMan  like '" + handleUser + "%' ";
			}
			if (!handleBTime.equals("") && !handleETime.equals("")) {
				querySql += "and a.handletime between '" + handleBTime
						+ "' and '" + handleETime + "' ";
			}
			if (!handleBTime.equals("") && handleETime.equals("")) {
				querySql += "and a.handletime > '" + handleBTime + "' ";
			}
			if (handleBTime.equals("") && !handleETime.equals("")) {
				querySql += "and a.handletime < '" + handleETime + "' ";
			}

			if (!querySql.equals("")) {
				sql = sql + querySql;
			}
			sql += " order by 处理时间 desc";
//			System.out.println("sql"+ sql);
		} catch (Exception e) {
			System.out.println("Error in getStatHandleContentSql:" + e);
		}
		return sql;
	}

	// 查找sysloginfo表
	public String getSysLogInfoSql(String logContent, String logUser,
			String timeBegin, String timeEnd) throws Exception {
		String sql = "select * from sysloginfo where logtype=1101 ";
		try {
			String querySql = "";
			if (!logContent.equals("")) {
				querySql += "and logcontent like '%" + logContent + "%' ";
			}
			if (!logUser.equals("")) {
				querySql += "and loguser ='" + logUser + "' ";
			}
			if (!timeBegin.equals("") && !timeEnd.equals("")) {
				querySql += "and logtime between '" + timeBegin + "' and '"
						+ timeEnd + "' ";
			}
			if (!timeBegin.equals("") && timeEnd.equals("")) {
				querySql += "and logtime > '" + timeBegin + "' ";
			}
			if (timeBegin.equals("") && !timeEnd.equals("")) {
				querySql += "and logtime < '" + timeEnd + "' ";
			}

			if (!querySql.equals("")) {
				// querySql=querySql.substring(4, querySql.length());
				// querySql=" where "+querySql;
				sql = sql + querySql;
			}
			sql += " order by logtime desc";
		} catch (Exception e) {
			System.out.println("Error in getSysLogInfoSql:" + e);
		}
		return sql;
	}

	// 查找数据统计表
	public String[][] getStatSysContent(String startTime, String endTime,
			String statType, String staffID, String state, String userGroup,
			String userType, String userSkill, String handlestate) throws Exception {
		String[][] data = null;
		try {
			if (null != startTime && !startTime.equals("")) {
				String sql = "exec sp_ContTentStat '" + startTime + "','"
						+ endTime + "'," + statType + "," + staffID + ","
						+ state + "," + userGroup + "," + userType + ","
						+ userSkill+ ","+ handlestate;

				data = DBManager.getNewInstance("szhtp").query(sql, -1, 1);
			}
		} catch (Exception e) {
			System.out.println("Error in getStatSysContent:" + e);
		}
		return data;
	}

	// 查找数据统计表
	public String getStatSysContentSql(String startTime, String endTime,
			String statType, String staffID, String state, String userGroup,
			String userType, String userSkill, String handlestate) throws Exception {
		String sql = "";
		try {
			if (null != startTime && !startTime.equals("")) {
				sql = "exec sp_ContTentStat '" + startTime + "','" + endTime
						+ "'," + statType + "," + staffID + "," + state + ","
						+ userGroup + "," + userType + "," + userSkill+ "," 
						+ handlestate;
			}
		} catch (Exception e) {
			System.out.println("Error in getStatSysContentSql:" + e);
		}
		return sql;
	}
	// 查找数据统计表 查无统计
	public String[][] getStatNoComInfo(String startTime, String endTime,
			String statType, String staffID, String state, String userGroup,
			String userType, String userSkill, String pointType) throws Exception {
		String[][] data = null;
		try {
			if (null != startTime && !startTime.equals("")) {
				String sql = "exec sp_NoComInfoStat '" + startTime + "','"
						+ endTime + "'," + statType + "," + staffID + ","
						+ state + "," + userGroup + "," + userType + ","
						+ userSkill+ ","+ pointType;
				System.out.println(sql);
				data = DBManager.getNewInstance("infomgr").query(sql, -1, 1);
			}
		} catch (Exception e) {
			System.out.println("Error in getStatNoComInfo:" + e);
		}
		return data;
	}

	// 查找数据统计表 查无统计
	public String getStatNoComInfoSql(String startTime, String endTime,
			String statType, String staffID, String state, String userGroup,
			String userType, String userSkill, String pointType) throws Exception {
		String sql = "";
		try {
			if (null != startTime && !startTime.equals("")) {
				sql = "exec sp_NoComInfoStat '" + startTime + "','" + endTime
						+ "'," + statType + "," + staffID + "," + state + ","
						+ userGroup + "," + userType + "," + userSkill+ "," + pointType;
			}
		} catch (Exception e) {
			System.out.println("Error in getStatNoComInfoSql:" + e);
		}
		return sql;
	}
	// 查找数据统计表 纠错统计
	public String[][] getStatQueInfo(String startTime, String endTime,
			String statType, String staffID, String state, String userGroup,
			String userType, String userSkill, String pointType) throws Exception {
		String[][] data = null;
		try {
			if (null != startTime && !startTime.equals("")) {
				String sql = "exec sp_QueInfoStat '" + startTime + "','"
						+ endTime + "'," + statType + "," + staffID + ","
						+ state + "," + userGroup + "," + userType + ","
						+ userSkill+ ","+ pointType;

				data = DBManager.getNewInstance("infomgr").query(sql, -1, 1);
			}
		} catch (Exception e) {
			System.out.println("Error in getStatQueInfo:" + e);
		}
		return data;
	}

	// 查找数据统计表 纠错统计
	public String getStatQueInfoSql(String startTime, String endTime,
			String statType, String staffID, String state, String userGroup,
			String userType, String userSkill, String pointType) throws Exception {
		String sql = "";
		try {
			if (null != startTime && !startTime.equals("")) {
				sql = "exec sp_QueInfoStat '" + startTime + "','" + endTime
						+ "'," + statType + "," + staffID + "," + state + ","
						+ userGroup + "," + userType + "," + userSkill+ "," + pointType;
			}
		} catch (Exception e) {
			System.out.println("Error in getStatQueInfoSql:" + e);
		}
		return sql;
	}

	/**
	 * 
	 * @param serialNo
	 *            坐席流水号
	 * @param name
	 *            处理人名称或工号
	 * @param info
	 *            处理内容
	 * @param handle
	 *            处理状态
	 * @throws Exception
	 */
	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);
		}
	}
}