📄 syscontent.java
字号:
package com.log.bo;
import com.log.util.DateUtil;
import com.lzc.util.db.DBManager;
public class SysContent {
private static SysContent instance = null;
public static SysContent getInstance() {
if (instance == null) {
synchronized (new Object()) {
if (instance == null) {
instance = new SysContent();
}
}
}
return instance;
}
public static void main(String[] args) throws Exception {
SysContent bo = SysContent.getInstance();
bo.searchStaff("11", "", "22", "33");
}
public String searchStaff(String workno, String name, String phone,
String department) throws Exception {
String sql = "select* from staffinfo";
try {
if (!workno.equals("") || !name.equals("") || !phone.equals("")
|| !department.equals("")) {
sql += " where ";
int i = 0;
if (!workno.equals("")) {
i++;
sql += " workno like '%" + workno + "%'";
}
if (!name.equals("")) {
if (i > 0) {
sql += " and ";
}
i++;
sql += " name like '%" + name + "%'";
}
if (!phone.equals("")) {
if (i > 0) {
sql += " and ";
}
i++;
sql += " phone like '%" + phone + "%'";
}
if (!department.equals("")) {
if (i > 0) {
sql += " and ";
}
i++;
sql += " department like '%" + department + "%'";
}
}
// System.out.println("sql:" + sql);
} catch (Exception e) {
System.out.println("Error in searchStaff:" + e);
}
return sql;
}
public boolean delStaffInfo(String[] paramVal) throws Exception {
boolean flag = false;
try {
String sql = "delete from staffinfo where id=?";
flag = DBManager.getNewInstance("szhtp").update(sql, paramVal);
} catch (Exception e) {
flag = false;
System.out.println("Error in delStaffInfo:" + e);
}
return flag;
}
public boolean editStaffInfo(String[] paramVal) throws Exception {
boolean flag = false;
try {
String sql = "update staffinfo set workno=?,name=?,phone=?,idcard=?,sex=?,department=? where id=?";
flag = DBManager.getNewInstance("szhtp").update(sql, paramVal);
} catch (Exception e) {
flag = false;
System.out.println("Error in editStaffInfo:" + e);
}
return flag;
}
public String[][] getStaffinfoByID(String id) throws Exception {
String[][] data = null;
try {
String sql = "select * from staffinfo where id=" + id;
data = DBManager.getNewInstance("szhtp").query(sql, -1, 1);
} catch (Exception e) {
System.out.println("Error in getStaffinfoByID:" + e);
}
return data;
}
public String[][] getStaffinfoByID(String id, String workno)
throws Exception {
String[][] data = null;
try {
String sql = "select * from staffinfo where id <>" + id
+ " and workno=" + workno;
data = DBManager.getNewInstance("szhtp").query(sql, 10, 1);
} catch (Exception e) {
System.out.println("Error in getStaffinfoByID:" + e);
}
return data;
}
public String[][] getStaffinfoByNo(String workno) throws Exception {
String[][] data = null;
try {
String sql = "select * from staffinfo where workno=" + workno;
data = DBManager.getNewInstance("szhtp").query(sql, 10, 1);
} catch (Exception e) {
System.out.println("Error in getStaffinfoByNo:" + e);
}
return data;
}
public boolean addStaffInfo(String[] paramVal) throws Exception {
boolean flag = false;
try {
String sql = "insert into staffinfo(workno,name,phone,idcard,sex,department)values(?,?,?,?,?,?)";
flag = DBManager.getNewInstance("szhtp").update(sql, paramVal);
} catch (Exception e) {
flag = false;
System.out.println("Error in addStaffInfo:" + e);
}
return flag;
}
public String[][] getUserGroup() throws Exception {
String[][] data = null;
try {
String sql = "select distinct usergroup from userinfo where platformflag=1 order by usergroup desc";
data = DBManager.getNewInstance("szhtp").query(sql, -1, 1);
} catch (Exception e) {
System.out.println("Error in getUserGroup:" + e);
return null;
}
return data;
}
public String[][] getUserType() throws Exception {
String[][] data = null;
try {
String sql = "select distinct usertype from userinfo where platformflag=1 order by usertype desc";
data = DBManager.getNewInstance("szhtp").query(sql, -1, 1);
} catch (Exception e) {
System.out.println("Error in getUserType:" + e);
return null;
}
return data;
}
public String[][] getUserSkill() throws Exception {
String[][] data = null;
try {
String sql = "select distinct userskill from userinfo where platformflag=1 order by userskill desc";
data = DBManager.getNewInstance("szhtp").query(sql, -1, 1);
} catch (Exception e) {
System.out.println("Error in getUserSkill:" + e);
return null;
}
return data;
}
// 查找syscontent表
public String getSysContentSql(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 ";
// 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 时间,isnull(recordfilename,'') 操作 "
// + "FROM syscontent a INNER JOIN "
// + "UserInfo b ON a.STAFF_ID = b.UserID ";
String sql = "SELECT a.serial_no ,isnull(a.SERIALNO,'') as SERIALNO,"
+ "a.STAFF_ID ,b.UserName ,a.PHONE_NBR ," + " a.state, "
+ "b.UserGroup ," + "b.UserType ,b.UserSkill ,"
+ "a.MOD_DATE ,isnull(a.findtime,'0') as findtime,isnull(recordfilename,'') as recordfilename,a.handlestate "
+ "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;
}
//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 getaHandleSysContentSql(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 a.serial_no ,isnull(a.SERIALNO,'') as SERIALNO,"
+ "a.STAFF_ID ,b.UserName ,a.PHONE_NBR ,"
+ "a.state ,"
+ "b.UserGroup ,"
+ "b.UserType ,b.UserSkill ,"
+ "a.MOD_DATE,isnull(a.findtime,'0') findtime ,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;
}
//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 时间,"
+ "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 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 + "' ";
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -