📄 appealdao.java
字号:
}
/**
* @param pageNo
* 查询第N页记录
* @param sql
* 查询sql
* @return
*/
DataTable getAppealList(int pageNo, String sql) {
DataTable dataTable = null;
try {
dataTable = _dac.executeQuery(sql);
} catch (Exception e) {
FileLogger.getLogger().debug(e.getMessage());
}
return dataTable;
}
/**
* @param proState
* 订单处理状态
* @param appealMode
* 业务类型 1、故障2、投诉3、建议
* @return 返回记录数
*/
public int getAppealListSize(String proState, String appealMode,
String cityCodes) {
if (cityCodes == null || cityCodes.equals("0590")) {
cityCodes = "0591,0592,0593,0594,0595,0596,0597,0598,0599";
}
DataTable dataTable = null;
StringBuffer sql = new StringBuffer();
sql.append( "select count(APPEALID) as count from tf_appeal a" +
" join td_appealtype b on a.appealtypeid =b.appealtypeid " +
" where a.UPAPPEALID=0 and instr(?,a.citycode)>0" );
if (appealMode.equals(""))
sql.append(" and a.APPEALMODE in('2','3')");
else
sql.append(" and a.APPEALMODE='1'");
if(proState.equals("unlock"))
sql.append(" and a.LOCKSTAFFID is null ");
else if(proState.startsWith("lock")){
String[] value = proState.split("~");
sql.append(" and a.LOCKSTAFFID="+value[1]);
}else if (!proState.equals("all"))
sql.append(" and a.PROGRESSSTATE='" + proState + "'");
Object[] value = new Object[1];
value[0] = cityCodes;
//System.out.println("sql: "+sql.toString());
try {
dataTable = _dac.executeQuery("select * from(" + sql.toString() + ")", value);
} catch (Exception e) {
FileLogger.getLogger().debug(e.getMessage());
return 0;
}
return dataTable.getRow(0).getInt("count");
}
public int getCustomServiceOrderSizeByState(String proState,
String cityCodes) {
return getAppealListSize(proState, "", cityCodes);
}
public int getAppealOrderSizeByState(String proState, String cityCodes) {
return getAppealListSize(proState, "1", cityCodes);
}
/**
* 权限认证
* @param prodorderID
* @param sStaffCode
* @return
*/
public String getValidataOrder(String appealid,String staffId) {
try {
String newId = "";
DataTable dataTable = null;//PRODORDERID
String sql1
= "select count(t.LOCKSTAFFID) as LOCKSTAFFID from tf_appeal t where "+
"t.APPEALID=to_number(?) and t.upappealid=0";
Object[] value = new Object[1];
value[0] = appealid;
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:
boolean flag = false;
String bakss = appealid;
try{
if(setAppealLock(appealid,staffId)){
//查出被锁定的原定单
String finalID = this.getProdHistoryPId(appealid);
if(finalID != null && !finalID.equals("")){
appealid = finalID;
}
AppealDAO _pp = getUserLockOrder(appealid,staffId);
if(_pp != null){
_pp.setUpappealid(bakss);
//生成自己的历史记录单
if(addLockProdOrder(_pp)){
newId = getProdHistoryPId(bakss);
if(newId == null || newId.equals("")){
_pp = null;
flag = true;
return "3";
}
return newId;
}
else{
flag = true;
_pp = null;
return "2";
}
}else{
flag = true;
return "1";
}
}else{
return "0";
}
}catch(RuntimeException rux){
_logger.warn(rux.getMessage(),rux);
throw rux;
}finally{
if(flag){
setAppealUnLock(bakss);
}
}
case 1:
try{
//锁定，是否被自己锁定
sql1
= "select to_char(t.LOCKSTAFFID) locks from tf_appeal t where "+
"t.APPEALID=to_number(?) and t.upappealid=0";
value = null;
value = new Object[1];
value[0] = appealid;
dataTable = _dac.executeQuery(sql1,value);
if(dataTable != null && dataTable.getRows().getCount() > 0)
lockId = dataTable.getRow(0).getString("locks");
if(lockId != null && lockId.equals(staffId)){
newId = getProdHistoryPId(appealid);
if(newId == null || newId.equals("")){
setAppealUnLock(appealid);
return "3";
}
return newId;
}else{
lockId = getLockMan(lockId);
return "4~"+lockId;
}
}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";
}
/**
* <code>
* 获取一张原工单的最新子单
* 输入原工单id，跟据id查询到此工单的流水号，
* 然后按流水号的降序，取最大时间的子单。
* </code>
* @param id
* @return
*/
public String getProdHistoryPId(String id){
try{
String sql = "select appealid from "+
"(select appealid from tf_appeal "+
"where weborder="+
"(select weborder from tf_appeal where appealid=?) "+
"order by createtime desc) "+
"where rownum = 1";
DataTable dataTable = _dac.executeQuery(sql,new Object[] {id});
if(dataTable!=null && dataTable.getRows().getCount() > 0){
String ids = dataTable.getRow(0).getString("appealid");
return ids;
}
}catch(Exception e){
_logger.warn(e.getMessage(),e);
}
return "";
}
/**
* 显示一张用户锁定的受理单详细资料(不跨表)
* @param prodorderID
* @return
*/
public AppealDAO getUserLockOrder(String prodorderID,String lockid){
try{
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm");
DataTable dataTable = null;
Object[] _object = new Object[1];
_object[0] = prodorderID;
String sql = "select APPEALID,UPAPPEALID,USERID,APPEALTYPEID,CITYCODE,ACCOUNTNO,"
+"SEX,EMAIL,to_char(REQURESTTIME,'yyyy-MM-dd') REQURESTTIME,RELATIONTEL,"
+"FAILURETEL,APPEALTITLE,APPEACONTENT,APPEALMODE,PROGRESSSTATE,"
+"OTHERORDER,IMPORTANCEDEGREE,to_char(CREATETIME,'yyyy-mm-dd hh24:mm:ss')"
+" CREATETIME,to_char(LOCKSTAFFID) LOCKSTAFFID,OUTDATE,BOSOMPROGRESSSTATE,"
+"FAULTTYPE,CUSTTYPE,WEBORDER,APPEALORDERNUM from tf_appeal where APPEALID=?";
dataTable = _dac.executeQuery(sql,_object);
if(dataTable != null && dataTable.getRows().getCount() >0){
AppealDAO _prod = new AppealDAO();
_prod.setAppealid(String.valueOf(dataTable.getRow(0).getInt("APPEALID")));
_prod.setUpappealid(String.valueOf(dataTable.getRow(0).getInt("UPAPPEALID")));
_prod.setUserid(dataTable.getRow(0).getString("USERID"));
_prod.setAppealtypeid(dataTable.getRow(0).getString("APPEALTYPEID"));
_prod.setCitycode(dataTable.getRow(0).getString("CITYCODE"));
_prod.setAccountno(dataTable.getRow(0).getString("ACCOUNTNO"));
_prod.setSex(dataTable.getRow(0).getString("SEX"));
_prod.setEmail(dataTable.getRow(0).getString("EMAIL"));
_prod.setRequresttime(dataTable.getRow(0).getString("REQURESTTIME"));
_prod.setRelationtel(dataTable.getRow(0).getString("RELATIONTEL"));
_prod.setFailuretel(dataTable.getRow(0).getString("FAILURETEL"));
_prod.setAppealtitle(dataTable.getRow(0).getString("APPEALTITLE"));
_prod.setAppeacontent(dataTable.getRow(0).getString("APPEACONTENT"));
_prod.setAppealmode(dataTable.getRow(0).getString("APPEALMODE"));
_prod.setProgressstate(dataTable.getRow(0).getString("PROGRESSSTATE"));
_prod.setOtherorder(dataTable.getRow(0).getString("OTHERORDER"));
_prod.setImportancedegree(dataTable.getRow(0).getString("IMPORTANCEDEGREE"));
_prod.setCreatetime(dataTable.getRow(0).getString("CREATETIME"));
_prod.setLOCKSTAFFID(dataTable.getRow(0).getString("LOCKSTAFFID"));
_prod.setOutdate(df.format(dataTable.getRow(0).getDate("OUTDATE")));
_prod.setBOSOMPROGRESSSTATE(dataTable.getRow(0).getString("BOSOMPROGRESSSTATE"));
_prod.setFAULTTYPE(dataTable.getRow(0).getString("FAULTTYPE"));
_prod.setCUSTTYPE(dataTable.getRow(0).getString("CUSTTYPE"));
_prod.setWEBORDER(dataTable.getRow(0).getString("WEBORDER"));
_prod.setAPPEALORDERNUM(dataTable.getRow(0).getString("APPEALORDERNUM"));
return _prod;
}
}catch(RuntimeException rux){
_logger.warn(rux.getMessage(),rux);
//throw rux;
}catch(Exception e){
_logger.warn(e.getMessage(),e);
}
return null;
}
public String getPerentID(String appealid){
try{
String sql = "select t.upappealid from tf_appeal t " +
"where t.appealid=?";
Object[] _object = new Object[1];
_object[0] = appealid;
DataTable dataTable = _dac.executeQuery(sql,_object);
if(dataTable != null && dataTable.getRows().getCount() > 0)
{
String upid = dataTable.getRow(0).getString("upappealid");
return (upid == null || upid.equals("0")) ? appealid : upid;
}
}catch(Exception ex){
_logger.warn(ex.getMessage(),ex);
}
return "";
}
/**
* 添加一条历史记录
* @param _prod
* @return productid
*/
public boolean addLockProdOrder(AppealDAO _prod){
try{
StringBuffer sql = new StringBuffer();
sql.append("insert into tf_appeal(APPEALID,UPAPPEALID,USERID,APPEALTYPEID");
sql.append(",CITYCODE,ACCOUNTNO,SEX,EMAIL,REQURESTTIME,RELATIONTEL,FAILURETEL,");
sql.append("APPEALTITLE,APPEACONTENT,APPEALMODE,PROGRESSSTATE,OTHERORDER,");
sql.append("IMPORTANCEDEGREE,CREATETIME,LOCKSTAFFID,OUTDATE,");
sql.append("BOSOMPROGRESSSTATE,FAULTTYPE,CUSTTYPE,WEBORDER,APPEALORDERNUM) ");
sql.append("values(seq_appealid.nextval,to_number(?),?,to_number(?),?,?,?,?,");
sql.append("to_date(?,'yyyy-mm-dd'),?,?,?,?,?,?,?,?,");
sql.append("sysdate,to_number(?),to_date(?,'yyyy-mm-dd hh24:mi'),?,?,?,?,?)");
Object[] _object = new Object[23];
_object[0]=_prod.getUpappealid();
_object[1]=_prod.getUserid();
_object[2]=_prod.getAppealtypeid();
_object[3]=_prod.getCitycode();
_object[4]=_prod.getAccountno();
_object[5]=_prod.getSex();
_object[6]=_prod.getEmail();
_object[7]=_prod.getRequresttime();
_object[8]=_prod.getRelationtel();
_object[9]=_prod.getFailuretel();
_object[10]=_prod.getAppealtitle();
_object[11]=_prod.getAppeacontent();
_object[12]=_prod.getAppealmode();
_object[13]=_prod.getProgressstate();
_object[14]=_prod.getOtherorder();
_object[15]=_prod.getImportancedegree();
_object[16]=_prod.getLOCKSTAFFID();
_object[17]=_prod.getOutdate();
_object[18]=_prod.getBOSOMPROGRESSSTATE();
_object[19]=_prod.getFAULTTYPE();
_object[20]=_prod.getCUSTTYPE();
_object[21]=_prod.getWEBORDER();
_object[22]=_prod.getAPPEALORDERNUM();
int i = _dac.executeUpdate(sql.toString(),1000,_object);
if(i > 0)
return true;
}catch(Exception e){
_logger.warn(e.getMessage(),e);
}
return false;
}
/**
* wull 2007-1-29修改。工单Email地址改为取tf_appeal表
* 受理单详细信息
*
* @param APPEALID
* 受理单ID
* @return
*/
public DataTable getAppealDeail(String appealid) {
StringBuffer _sb = new StringBuffer();
_sb.append("SELECT APPEALID,APPEALTYPENAME,a.USERID,f.NAME,");
_sb.append("a.APPEALTYPEID,a.CITYCODE,a.ACCOUNTNO,a.APPEALMODE,");
_sb.append("to_char(a.REQURESTTIME,'yyyymmdd hh24:mi:ss') REQURESTTIME,a.RELATIONTEL,a.FAILURETEL,a.appealtitle,");
_sb.append("decode(a.PROGRESSSTATE,'1','等待处理','2','处理中',3,'处理结束','待处理') PROGRESSSTATE,");
_sb.append("a.APPEACONTENT,a.OTHERORDER,a.LOCKSTAFFID,a.IMPORTANCEDEGREE,a.CREATETIME,");
_sb.append("to_char(a.OUTDATE,'yyyy-MM-dd') OUTDATE,");
_sb.append("decode(a.BOSOMPROGRESSSTATE,'1','待处理','2','转派','3','暂存','8','作废',9,'完成','待处理') BOSOMPROGRESSSTATE,");
_sb.append("a.FAULTTYPE,a.CUSTTYPE,a.WEBORDER,a.APPEALORDERNUM,a.SEX ,a.EMAIL,f.AREACODE,b.CITYNAME ");
_sb.append(" FROM tf_custinfoweb f,tf_appeal a,td_appealtype c,td_city b ");
_sb.append(" where a.userid = f.userid");
_sb.append(" and a.appealtypeid = c.appealtypeid");
_sb.append(" and a.citycode = b.citycode");
_sb.append(" and a.APPEALID = ?");
try{
return _dac.executeQuery(_sb.toString(),new Object[]{appealid});
}catch(java.sql.SQLException sqlx){
_logger.warn("获取故障，建议，投诉单详细信息异常：" + sqlx.getMessage());
}
return null;
}
public static boolean setAppealLock(String APPEALID, String staffID) {
String sql = "update tf_appeal t set t.lockstaffid=to_number(?) where "
+"t.appealid=to_number(?) and t.lockstaffid is null and t.upappealid=0";
Object[] value = new Object[2];
value[0] = staffID;
value[1] = APPEALID;
try {
_dac.executeUpdate(sql, 10000, value);
return true;
} catch (Exception e) {
_logger.warn("Appeal 锁定失败 " + e.getMessage());
}
return false;
}
/**
* <code>解锁单张工单</code>
* @param APPEALID
* @return
*/
public static boolean setAppealUnLock(String APPEALID){
String sql = "update tf_appeal t set LOCKSTAFFID=null where APPEALID =to_number(?) " +
"and UPAPPEALID=0";
Object[] value = new Object[1];
value[0] = APPEALID;
try {
_dac.executeUpdate(sql, 10000, value);
return true;
} catch (Exception e) {
_logger.warn("Appeal 解锁失败 " + e.getMessage());
}
return false;
}
/**
* <code>解锁这工号锁定的所有工单</code>
* @param staffid
* @return
*/
public static boolean setAppealUnlockAll(String staffid){
String sql = "update tf_appeal t set t.LOCKSTAFFID=null where t.LOCKSTAFFID=? and t.upappealid=0";
Object[] value = new Object[1];
value[0] = staffid;
try {
int i = _dac.executeUpdate(sql, 20000, value);
if (i > 0)
return true;
} catch (Exception e) {
_logger.warn("Appeal 解锁失败 " + e.getMessage());
}
return false;
}
/**
* <p>故障单归档</p>
*
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -