tseradvisedao.java.svn-base

本例使用Java + Jsp 实现选择树的过程,主要使用Struts+JDBC/hibernate架构

/*
 * $Workfile: TserAdviseDao.java$ 
 * Copyright (c) 2007 Jama, International.
 * All rights reserved.
 * Creator Fan
 * Last Changed by: $Author: Fan$ 
 * On: $Date: 2007-8-29 下午05:08:26$ 
 * $Revision: 1$ 
 */

package com.jama.rcrm.service.dao;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.beanutils.BeanUtils;
import org.hibernate.Query;
import org.hibernate.Session;

import com.jama.common.base.BaseDAO;
import com.jama.common.taglib.QueryInfo;
import com.jama.common.util.DateUtil;
import com.jama.common.util.Tools;
import com.jama.rcrm.service.ServiceConstants;

import com.jama.rcrm.service.business.dto.TserAdviseAccessDto;
import com.jama.rcrm.service.dao.pojo.TSerAdvise;
import com.jama.rcrm.service.dao.pojo.TSerAdviseProblem;
 
 
public class TserAdviseDao extends BaseDAO
{   
	
   /**
    * 根据条件拿到相应的记录条数
    * @return
    * @throws Exception
    */
	private int getTotal(String condition) throws Exception
	{
		int total = 0;  //记录数
		String selhql="select count(distinct advise_guid) from t_ser_advise ta ";
		if(condition!=null&&condition.length()>0)
		{
			selhql=selhql+condition;
		}
		
		Object obj = (Object) getSession().createSQLQuery(selhql).uniqueResult();
		if (obj != null)
		{
			total = Integer.parseInt(obj.toString());
		}
		return total;
	}
	
	/**
	 * 从投诉问题与问题类型关系表拿到所有guid对应的问题类别名
	 * @param guid
	 * @return
	 */
	private String getProblemName(String guid){
	   StringBuffer problemName=new StringBuffer();
	   StringBuffer problemGuid=new StringBuffer();
 	   String selhql2="select tp.problem_guid,tp.problem_name from t_ser_problem_define tp where " +
 	   		"tp.problem_guid in(select tb.problem_guid from t_ser_advise_problem tb " +
 	   		"where tb.advise_guid=:guid)"; 
 	   Query query=this.getSession().createSQLQuery(selhql2);
 	   query.setString("guid",guid);
 	   List results=query.list();
 	   Object[] obj=null;
 	   for(int i=0;i<results.size();i++){
 		  obj=(Object[])results.get(i);
 		  problemGuid.append(String.valueOf(obj[0]!=null?obj[0]:"")+",");
 		  problemName.append(String.valueOf(obj[1]!=null?obj[1]:"")+",");
 		  
 	   }
 	   problemGuid.append(";"+problemName);
 	   return problemGuid.toString();
 	   
		
	}
	
	 /**
	  * 分页拿到投诉处理状态为没有回访的记录集
	  * @param queryInfo
	  * @param process
	  * @return
	  * @throws Exception
	  */
	public List findAllTserAdviseAccessByPage(QueryInfo queryInfo) throws Exception
	{   
		if (queryInfo.getRecordCount() == -1)
		{   String condition=" where process="+ServiceConstants.STATUS_QEND +" or process="+ServiceConstants.STATUS_NO;
		    queryInfo.setRecordCount(this.getTotal(condition));
		}
			String selhql="select ta.advise_guid,ta.customer_name,ta.topic,ta.process,ta.dispose_date,ta.current_date from t_ser_advise ta where process="+ServiceConstants.STATUS_QEND+" or process="+ServiceConstants.STATUS_NO;
			List dtoList=this.getTserAdviseListBysql(selhql, queryInfo);
			
			return dtoList;
	}
	
	/**
	 * 分页拿到所有投诉记录集(历史投诉列表)
	 * @param queryInfo
	 * @return
	 * @throws Exception
	 */
	public List findAllTserAdviseList(QueryInfo queryInfo,String process) throws Exception
	{
		String findprocess=process;
		String addsql="";
		if(findprocess!=null&&(!findprocess.equals("-1")))
		{
			addsql=" where process="+findprocess;
		}
		else
		{
		
			addsql=" where process in("+ServiceConstants.STATUS_NEW+","+ServiceConstants.STATUS_ING+","+ServiceConstants.STATUS_QEND+","+ServiceConstants.STATUS_AEND+")";
		}
		if (queryInfo.getRecordCount() == -1)
		{    queryInfo.setRecordCount(this.getTotal(addsql));
		}
			String selhql="select ta.advise_guid,ta.customer_name,ta.topic,ta.process,ta.dispose_date,ta.current_date from t_ser_advise ta "+addsql;
			List dtoList=this.getTserAdviseListBysql(selhql, queryInfo);
			
			return dtoList;
		
	}
	
	/**
	 * 按id查询相符的一条投诉记录
	 * @param guid
	 * @return
	 */
	public TserAdviseAccessDto findTserAdviseInfoByGuid(String guid) throws Exception
	{
		StringBuffer sql = new StringBuffer();
		sql.append(" select ");
		sql.append(" ta.unit_guid,ta.customer_name,ta.advise_means,ta.multimedia_flag, ");
		sql.append(" ta.topic,ta.instancy_status,ta.advise_number,ta.user_guid,ta.tel_phone, ");
		sql.append(" ta.process,ta.finish_date,ta.checkin_date,ta.living_flag,ta.description, ");
		sql.append(" ta.answer_flag,ta.answer,ta.answer_date,ta.dispose_date,ta.standard_remark, ");
		sql.append(" ta.current_date,ta.last_result,ta.result_level,ta.result_description,ta.approve_level,ta.approve_description, ");
		sql.append(" ta.time_level,ta.time_description,ta.user_level,ta.user_description, ");
		sql.append(" ta.worker_level,ta.worker_description,tg.name,tu.unit_name ");
	

		sql.append("  from t_ser_advise ta  ");
		sql.append("  left join t_sys_orgnization tg on ta.user_guid=tg.id ");
		sql.append(" left join t_hou_unit tu on ta.unit_guid=tu.unit_guid");
		sql.append(" where ta.advise_guid='" + guid + "' ");
		
		TserAdviseAccessDto dto=null;
		List result=this.getSession().createSQLQuery(sql.toString()).list() ;
	  Object[] obj = null;
	   
		if(result.size()>0)
		{   
			dto=new TserAdviseAccessDto();
			obj = (Object[]) result.get(0);
		
		dto.setAdviseGuid(guid);
		dto.setUnitGuid(String.valueOf(obj[0]!=null?obj[0]:""));
		dto.setCustomerName(String.valueOf(obj[1]!=null?obj[1]:""));
		dto.setAdviseMeans(String.valueOf(obj[2]!=null?obj[2].toString():""));
		if(obj[2]!=null)
		{
			dto.setAdviseMeansName(ServiceConstants.ADVISEMODE_STRING[Integer.parseInt(obj[2].toString())]);	
		}
		dto.setMultimediaFlag(String.valueOf(obj[3]!=null?obj[3].toString():""));
		if(obj[3]!=null)
		{
			dto.setMultimediaFlagName(ServiceConstants.MULTIMEDIAFLAG_STRING[Integer.parseInt(obj[3].toString())]);
		}
		dto.setTopic(String.valueOf(obj[4]!=null?obj[4]:""));
		dto.setInstancyStatus(String.valueOf(obj[5]!=null?obj[5].toString():""));
		if(obj[5]!=null)
		{
			dto.setInstancyStatusName(ServiceConstants.GRADE_STRING[Integer.parseInt(obj[5].toString())]);
		}
		dto.setAdviseNumber(String.valueOf(obj[6]!=null?obj[6]:""));
		dto.setUserGuid(String.valueOf(obj[7]!=null?obj[7]:""));
		dto.setTelPhone(String.valueOf(obj[8]!=null?obj[8]:""));
		dto.setProcess(String.valueOf(obj[9]!=null?obj[9].toString():""));
		if(obj[9]!=null)
		{
			dto.setProcessName(ServiceConstants.STATUS_STRING[Integer.parseInt(obj[9].toString())]);
		}
		dto.setFinishDateS(String.valueOf(obj[10]!=null?obj[10].toString():""));
		dto.setCheckinDateS(String.valueOf(obj[11]!=null?obj[11].toString():""));
		dto.setLivingFlag(String.valueOf(obj[12]!=null?obj[12].toString():""));
		if(obj[12]!=null)
		{
			dto.setLivingFlagName(String.valueOf(ServiceConstants.LIVINGFLAGE_STRIGN[Integer.parseInt(obj[12].toString())]));
		}
		dto.setDescription(String.valueOf(obj[13]!=null?obj[13]:""));
		dto.setAnswerFlag(String.valueOf(obj[14]!=null?obj[14].toString():""));
		if(obj[14]!=null)
		{
			dto.setAnswerFlagName(ServiceConstants.ANSWERFLAG_STRING[Integer.parseInt(obj[14].toString())]);
		}
		dto.setAnswer(String.valueOf(obj[15]!=null?obj[15].toString():""));
		dto.setAnswerDateS(String.valueOf(obj[16]!=null?obj[16].toString():""));
		dto.setDisposeDateS(String.valueOf(obj[17]!=null?obj[17].toString():""));
		dto.setStandardRemark(String.valueOf(obj[18]!=null?obj[18]:""));
		dto.setCurrentDateS(String.valueOf(obj[19]!=null?obj[19].toString():""));
		dto.setLastResult(String.valueOf(obj[20]!=null?obj[20]:""));
		
	
		dto.setResultLevel(String.valueOf(obj[21]!=null?obj[21].toString():""));
		if(obj[21]!=null)
		{
				dto.setResultLevelName(obj[21].toString().equals("0")?"":ServiceConstants.RESULT_STRING[Integer.parseInt(obj[21].toString())]);	
		
		}
		dto.setResultDescription(String.valueOf(obj[22]!=null?obj[22]:""));
		
		dto.setApproveLevel(String.valueOf(obj[23]!=null?obj[23].toString():""));
	
		if(obj[23]!=null)
		{
			dto.setApproveLevelName(obj[23].toString().equals("0")?"":ServiceConstants.APPROVE_STRIGN[Integer.parseInt(obj[23].toString())]);	
		}
		dto.setApproveDescription(String.valueOf(obj[24]!=null?obj[24]:""));
		dto.setTimeLevel(String.valueOf(obj[25]!=null?obj[25].toString():""));
		if(obj[25]!=null)
		{
			dto.setTimeLevelName(obj[25].toString().equals("0")?"":ServiceConstants.TIME_STRING[Integer.parseInt(obj[25].toString())]);	
		}
		
		dto.setTimeDescription(String.valueOf(obj[26]!=null?obj[26]:""));
		dto.setUserLevel(String.valueOf(obj[27]!=null?obj[27].toString():""));
		if(obj[27]!=null)
		{
			dto.setUserLevelName(obj[25].toString().equals("0")?"":ServiceConstants.USERRECEIVE_STRIGN[Integer.parseInt(obj[27].toString())]);	
		}
		
		dto.setUserDescription(String.valueOf(obj[28]!=null?obj[28]:""));
		dto.setWorkerLevel(String.valueOf(obj[29]!=null?obj[29].toString():""));
		if(obj[29]!=null)
		{
			dto.setWorkerLevelName(obj[29].toString().equals("0")?"":ServiceConstants.WORKER_STRIGN[Integer.parseInt(obj[29].toString())]);	
		}
		
		dto.setWorkerDescription(String.valueOf(obj[30]!=null?obj[30]:""));
		
		
		dto.setUserName(String.valueOf(obj[31]!=null?obj[31]:""));
		dto.setUnitGuidName(String.valueOf(obj[32]!=null?obj[32]:""));
		String [] problem=this.getProblemName(guid).split(";");
		if(problem!=null&&problem.length>0)
		{
		dto.setProblem_nameids(problem[0]!=null?problem[0]:"");
		dto.setProblem_name(problem[1]!=null?problem[1]:"");
		}
		else
		{
			dto.setProblem_nameids("");
			dto.setProblem_name("");
		}
		
	}
		
		return dto;
	}
	
    /**
     * 分页拿到所有已经处理完成但没有归档的投诉记录集(投诉登记列表)
     * @param queryInfo
     * @return
     * @throws Exception
     */
	public List findAllTserAdviseNotPigeonhole(QueryInfo queryInfo,String process) throws Exception
	{ 
		String findprocess=process;
		String addsql="";
		if(findprocess!=null&&(!findprocess.equals("-1")))
		{
			addsql=" where process="+findprocess;
		}
		else
		{
			addsql=" where process in("+ServiceConstants.STATUS_NEW+","+ServiceConstants.STATUS_ING+","+ServiceConstants.STATUS_QEND+")";
		}
		
		if (queryInfo.getRecordCount() == -1)
		{  
		    queryInfo.setRecordCount(this.getTotal(addsql));
		}
		String selhql="select ta.advise_guid,ta.customer_name,ta.topic,ta.process,ta.dispose_date,ta.current_date from t_ser_advise ta "+addsql;
		List dtoList=this.getTserAdviseListBysql(selhql, queryInfo);
			
		return dtoList;
	}
	/**
	 * 修改当前处理状态
	 * @param adviseGuid
	 * @param process
	 * @throws Exception
	 */
	public void modifyProcess(String adviseGuid,String process) throws Exception
	{
		String sql = "update t_ser_advise tsa set tsa.process = '"+ process +"' where tsa.advise_guid='"+ adviseGuid +"'";
		this.getSession().createSQLQuery(sql).executeUpdate();
	}
	
	/**
	 * 修改投诉和问题类型相对应的关系
	 * @param adviseGuid
	 * @param problemGuids
	 */
	public void modifyTserAdviseProblem(String adviseGuid,String problemGuids) throws Exception
	{
		 String delsql="delete from t_ser_advise_problem where advise_guid='"+adviseGuid+"'";
		 getSession().createSQLQuery(delsql).executeUpdate();
		 if(problemGuids!=null&&problemGuids.length()>0)
		 {
			 String[] pguid=problemGuids.split(",");
				for(int i=0;i<pguid.length;i++)
				{
					TSerAdviseProblem tpo=new TSerAdviseProblem();
					tpo.setAdviseProblemGuid(Tools.getUUID());