📄 consultationdao.java
字号:
/*
* 创建日期 2005-7-24
*
*/
package com.doone.fj1w.fjmgr.order;
import com.doone.data.DataTable;
import com.doone.util.FileLogger;
/**
* @author huzx
*
*/
public class ConsultationDAO extends DAO {
private int pageSize = 20; // 默认每页20条
private String sql = "";
private FileLogger _logger = new FileLogger();
private String CONSULTATIONID = "";//NUMBER(10) 业务咨询序号
private String USERID = "";// VARCHAR2(20) Y 登陆帐号
private String CITYCODE = "";// VARCHAR2(4) Y 地市编码
private String OPERATIONTYPE = "";// VARCHAR2(9) 此值来源于系统参数配置表中
private String CONSCONTENT = "";// VARCHAR2(500) 咨询内容
private String PHONENUM = "";// VARCHAR2(20) Y 服务号码
private String RELATIONTEL = "";//VARCHAR2(30) 联系电话
private String EMAIL = "";// VARCHAR2(50) Y 邮件地址
private String RESTORECONTENT = "";//VARCHAR2(500) Y 回复内容
private String RESTOREMAN = "";//VARCHAR2(10) Y 回复员有可能来自第三方系统中的管理员
private String RESTORETIME = "";// DATE Y 回复时间
private String PROGRESSSTATE = "";//VARCHAR2(9)
public ConsultationDAO() {
FileLogger.getLogger().debug("ConsultationDAO 数据库链接" + _dac);
}
/**
* @param pageNo
* 查询第N页记录
* @param sql
* 查询sql
* @return
*/
public DataTable getConsultationListBySate(String pageNo, String procState) {
return getList(makeSql(Integer.parseInt(pageNo), procState));
}
/**
* @param pageNo
* 查询第N页记录
* @param AppealMode
* 业务类型 1、故障2、投诉3、建议
* @param procState
* 订单处理状态 1、等待处理2、处理中3、处理结束
* @return
*/
public String makeSql(int pageNo, String procState) {
sql = "select a.consultationid,b.name,c.cityname,a.phonenum,a.relationtel,a.conscontent,"
+ "decode(a.progressstate,'1','等待处理','2','处理中','3','处理结束') progressstate"
+ " from tf_consultation a, tf_custinfoweb b ,td_city c where a.citycode=c.citycode"
+ " and b.userid = a.userid";
if (!procState.equals(""))
sql += " and a.progressstate='" + procState + "'";
sql = "SELECT * FROM ( SELECT row_.*, rownum rownum_ FROM(" + sql
+ ")row_ WHERE rownum <= " + pageNo * pageSize
+ ") WHERE rownum_ >" + (pageNo - 1) * pageSize;
return sql;
}
/**
* @param pageNo
* 查询第N页记录
* @param sql
* 查询sql
* @return
*/
private DataTable getList(String sql) {
DataTable dataTable = null;
try {
dataTable = _dac.executeQuery(sql);
} catch (Exception e) {
FileLogger.getLogger().debug(e.getMessage());
}
return dataTable;
}
// public String getValidataOrder(String prodorderID,String staffId) {
// try {
// String newId = "";
// DataTable dataTable = null;//PRODORDERID
// String sql1
// = "select count(t.lockstaffid) lockstaffid from tf_prodorder t where "+
// "t.prodorderid=to_number(?) and t.upprodorderid=0";
//
// Object[] value = new Object[1];
// value[0] = prodorderID;
// /**1.受理单是否被锁定*/
// dataTable = _dac.executeQuery(sql1,value);
// int row = dataTable.getRows().getCount();
// if(row > 0)
// row = dataTable.getRow(0).getInt("lockstaffid");
// else
// row = 0;
//
// String lockId = "";
// if(dataTable != null){
// switch(row){
//
// case 0:
// /**
// * 不被锁定
// * -----------------------------
// * 1.将此受理单改为自己锁定
// * 2.查出此受理单的详细资料
// * 3.将详细资料中的父受理单只向自己()生成一个历史记录，
// */
// /**自己能否锁定它*/
// boolean flags = false;
// ProdorderDAO _pp = null;
// String bakss = prodorderID;
// try{
// if(setConsulLock(prodorderID,staffId)){
// /**查出是原定单是否有被修改的最近子单*/
// String finalID = this.getProdHistoryPId(prodorderID);
// if(finalID != null || !finalID.equals("")){
// prodorderID = finalID;
// }
//
// /**查出此单*/
// _pp = getUserLockOrder(prodorderID,staffId);
// if(_pp != null){
// _pp.setUPPRODORDERID(_pp.getPRODORDERID());
// /**生成一张跟据上一次生成记录的子记录单*/
// if(addLockProdOrder(_pp)){
// /**newId是现有刚刚成的子记录单id，我们将对这张单进行改，查，操作*/
// newId = getProdHistoryPId(prodorderID);
// if(newId == null || newId.equals("")){
// flags = true;
// return "3";
// }
// return newId;
// }
// else{
// flags = true;
// return "2";
// }
// }else{
// flags = true;
// return "1";
// }
// }else{
// return "0";
// }
//
// }catch(RuntimeException rux){
// _logger.warn(rux.getMessage(),rux);
// throw rux;
// }finally{
// if(flags){
// this.setConsulUnLock(bakss);
// }
// _pp = null;
// }
// case 1:
// try{
// //锁定，是否被自己锁定
// sql1
// = "select to_char(t.lockstaffid) locks from tf_prodorder t where "+
// "t.prodorderid=to_number(?) and t.upprodorderid=0";
// value = null;
// value = new Object[1];
// value[0] = prodorderID;
// dataTable = _dac.executeQuery(sql1,value);
// if(dataTable != null && dataTable.getRows().getCount() > 0)
// lockId = dataTable.getRow(0).getString("locks");
// else
// return "4";
//
// if(lockId.equals(staffId)){
// newId = getProdHistoryPId(prodorderID);
// if(newId == null || newId.equals("")){
// this.setConsulUnLock(prodorderID);
// return "3";
// }
// return newId;
// }else{
// return "4";
// }
// }catch(RuntimeException rux){
// _logger.warn(rux.getMessage(),rux);
// throw rux;
// }
// default:
// return "-1";
// }
//
// }
// return "-2";
// } catch (Exception e) {
// _logger.warn(e.getCause(),e);
// }
// return "-3";
// }
//
public String getProdHistoryPId(String id){
try{
}catch(Exception ex){
_logger.warn(ex.getMessage(),ex);
}
return "";
}
public String getStaffCode(String lockid){
try{
Object[] _object = new Object[1];
_object[0] = lockid;
String sql = "select staffcode from tf_staff where staffid=? and STATE='E'";
DataTable _dt = _dac.executeQuery(sql,_object);
if(_dt != null && _dt.getRows().getCount() > 0){
String code = _dt.getRow(0).getString("staffcode");
return code;
}
}catch(Exception ex){
_logger.warn(ex.getMessage(),ex);
}
return "";
}
/**
* 咨询单记录条数
*
* @param procState
* 进度状态
* @return
*/
public int getConsultationSize(String procState) {
DataTable dataTable = null;
String sql = "select count(CONSULTATIONID) from tf_consultation t where 1=1";
if (!procState.equals(""))
sql += " and PROGRESSSTATE = '" + procState + "'";
try {
dataTable = _dac.executeQuery(sql);
} catch (Exception e) {
FileLogger.getLogger().debug(e.getMessage());
}
return Integer.parseInt(dataTable.getRow(0).getString(0));
}
public DataTable getConsulDetail(String consultationid) {
DataTable dataTable = null;
String sql = "select b.name,b.sex,b.cartname,b.cartno,c.cityname,a.phonenum,b.POSTALCODE ,"
+ "a.relationtel,a.email,b.relationaddress,a.conscontent,a.progressstate,to_char(a.RestoreTime,'yyyy-MM-dd HH24:mi')RestoreTime,"
+ "a.RESTOREMAN,a.RestoreContent from tf_consultation a ,tf_custinfoweb b ,td_city c "
+ " where a.userid = b.userid and a.citycode = c.citycode"
+ " and a.consultationid= ?";
Object[] value = new Object[1];
value[0] = consultationid;
try {
dataTable = _dac.executeQuery(sql, value);
} catch (Exception e) {
FileLogger.getLogger().debug(e.getMessage());
}
return dataTable;
}
public boolean setConsulLock(String staffID, String consultationid) {
String sql = "update tf_consultation t set PROGRESSSTATE=2,LOCKSTAFFID=? where CONSULTATIONID =?";
Object[] value = new Object[2];
value[0] = staffID;
value[1] = consultationid;
try {
int i=_dac.executeUpdate(sql, 10000, value);
if(i > 0)
return true;
} catch (Exception e) {
FileLogger.getLogger().error("Consul 锁定失败 " + e.getMessage());
}
return false;
}
public void setConsulUnLock(String consultationid) {
String sql = "update tf_consultation t set LOCKSTAFFID=null where CONSULTATIONID =?";
Object[] value = new Object[1];
value[1] = consultationid;
try {
_dac.executeUpdate(sql, 10000, value);
} catch (Exception e) {
FileLogger.getLogger().error("Consul 解锁失败 " + e.getMessage());
}
}
/**
* 设置进度状态
*
* @param state
* 1、等待处理 2、处理中 （分二种，一种已提供给第三方系统，一种是审核岗查阅过但还未提供给第三方系统，
* 他们根据Tf_ProdOrderInterface表中的第三方定单流水号字段来区分） 3、处理结束
* @param prodorderID
*/
public void setProcessState(String state, String consultationid) {
String sql = "update tf_consultation t set PROGRESSSTATE = ? where CONSULTATIONID = ? ";
Object[] value = new Object[2];
value[0] = state;
value[1] = consultationid;
try {
_dac.executeUpdate(sql, 10000, value);
} catch (Exception e) {
FileLogger.getLogger().error(e.getMessage());
}
}
/**
* 作废咨询单
*
* @param consultationid
* 订单ID
*/
public void blankOut(String consultationid) {
//String sql = "update tf_consultation t set PROGRESSSTATE =
// '3',BOSOMPROGRESSSTATE= '8', LOCKSTAFFID ='' where APPEALID = ? ";
String sql = "update tf_consultation t set PROGRESSSTATE = '3' where consultationid = ?";
Object[] value = new Object[1];
value[0] = consultationid;
try {
_dac.executeUpdate(sql, 10000, value);
} catch (Exception e) {
FileLogger.getLogger().error(e.getMessage());
}
}
public void saveRestorecontent(String consultationid, String progressstate,
String restoreman, String restorecontent) {
String sql = "update tf_consultation t set PROGRESSSTATE = ? , RESTOREMAN = ? , RESTORECONTENT = ? , RESTORETIME = "
+ "sysdate where CONSULTATIONID = ?";
Object[] value = new Object[4];
value[0] = progressstate;
value[1] = restoreman;
value[2] = restorecontent;
value[3] = consultationid;
try {
_dac.executeUpdate(sql, 10000, value);
} catch (Exception e) {
FileLogger.getLogger().error(e.getMessage());
}
}
public String getCITYCODE() {
return CITYCODE;
}
public void setCITYCODE(String citycode) {
CITYCODE = citycode;
}
public String getCONSCONTENT() {
return CONSCONTENT;
}
public void setCONSCONTENT(String conscontent) {
CONSCONTENT = conscontent;
}
public String getCONSULTATIONID() {
return CONSULTATIONID;
}
public void setCONSULTATIONID(String consultationid) {
CONSULTATIONID = consultationid;
}
public String getEMAIL() {
return EMAIL;
}
public void setEMAIL(String email) {
EMAIL = email;
}
public String getOPERATIONTYPE() {
return OPERATIONTYPE;
}
public void setOPERATIONTYPE(String operationtype) {
OPERATIONTYPE = operationtype;
}
public String getPHONENUM() {
return PHONENUM;
}
public void setPHONENUM(String phonenum) {
PHONENUM = phonenum;
}
public String getPROGRESSSTATE() {
return PROGRESSSTATE;
}
public void setPROGRESSSTATE(String progressstate) {
PROGRESSSTATE = progressstate;
}
public String getRELATIONTEL() {
return RELATIONTEL;
}
public void setRELATIONTEL(String relationtel) {
RELATIONTEL = relationtel;
}
public String getRESTORECONTENT() {
return RESTORECONTENT;
}
public void setRESTORECONTENT(String restorecontent) {
RESTORECONTENT = restorecontent;
}
public String getRESTOREMAN() {
return RESTOREMAN;
}
public void setRESTOREMAN(String restoreman) {
RESTOREMAN = restoreman;
}
public String getRESTORETIME() {
return RESTORETIME;
}
public void setRESTORETIME(String restoretime) {
RESTORETIME = restoretime;
}
public String getUSERID() {
return USERID;
}
public void setUSERID(String userid) {
USERID = userid;
}
}⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -