syscontent.java

日志管理源代码

			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;
			}
			//sql += " order by " + sortID + " " + sortOrder;
//			System.out.println("sql"+ sql);
		} catch (Exception e) {
			System.out.println("Error in getSysContentSql:" + e);
		}
		return sql;
	}

	// 查找syscontent表
	public String getaHandleSysContentSqlToOutFile(String serial_No,
			String phoneNbr, String staffId, String state, String timeBegin,
			String timeEnd, String serialNo, String userName, String userType,
			String userGroup, String userSkill, String handle,
			String handleUser, String handleBTime, String handleETime,
			String findtime, String sortID, String sortOrder) throws Exception {
		// String sql = "select * from syscontent ";
		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 时间,"
				+ "a.handlestate 状态,isnull(a.handleman,'') 处理人,isnull(a.findtime,'0') 通话时长  "
				+ "FROM V_SatisfyLevel a INNER JOIN "
				+ "UserInfo b ON a.STAFF_ID = b.UserID where a.recordfilename is not null ";

		// String sql = "SELECT isnull(a.SERIALNO,'') as SERIALNO,a.serial_no ,"
		// + "a.STAFF_ID ,b.UserName ,a.PHONE_NBR ,"
		// + "a.state ,"
		// + "b.UserGroup ,"
		// + "b.UserType ,b.UserSkill ,"
		// + "a.MOD_DATE ,isnull(recordfilename,'') as recordfilename ,"
		// + "a.handlestate ,isnull(a.handleman,'') as handleman "
		// + "FROM V_SatisfyLevel a INNER JOIN "
		// + "UserInfo b ON a.STAFF_ID = b.UserID where a.recordfilename is not
		// null ";
		try {
			String querySql = "";
			if (!timeEnd.trim().equals("")) {
				timeEnd = DateUtil.addlongDate(timeEnd, 1);
			}
			if (!handleETime.trim().equals("")) {
				handleETime = DateUtil.addlongDate(handleETime, 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 (!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 (!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 时间,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) throws Exception {
		String[][] data = null;
		try {
			if (null != startTime && !startTime.equals("")) {
				String sql = "exec sp_ContTentStat '" + startTime + "','"
						+ endTime + "'," + statType + "," + staffID + ","
						+ state + "," + userGroup + "," + userType + ","
						+ userSkill;

				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) throws Exception {
		String sql = "";
		try {
			if (null != startTime && !startTime.equals("")) {
				sql = "exec sp_ContTentStat '" + startTime + "','" + endTime
						+ "'," + statType + "," + staffID + "," + state + ","
						+ userGroup + "," + userType + "," + userSkill;
			}
		} catch (Exception e) {
			System.out.println("Error in getStatSysContentSql:" + 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);
		}
	}
}