rmrecommanddao.java

电信的网厅的整站代码

package com.doone.fj1w.fjmgr.sysmgr;

import java.sql.SQLException;
import java.util.*;

import com.doone.data.*;

public class RMRecommandDAO implements RMRecommandInterface{

	private DacClient dacClient=null;
	private final int PAGESIZE=20;
	
	/**
	 * 构造参数,从外部传入DacClient对象,以便外部事务控制
	 * @param dacClient DacClient对象
	 */
	public RMRecommandDAO(DacClient dacClient){
		this.dacClient=dacClient;
	}
	
	/**
	 * 获取seq_recommandid.nextval的值
	 * @return
	 * @throws SQLException
	 */
	public long get_seq_recommandid() throws SQLException{
		//sql语句
		String sql="select seq_recommandproductid.nextval from dual";
		//执行sql,返回序列号
		DataTable dt=dacClient.executeQuery(sql);
		if(dt!=null && dt.getRows().getCount()>0){
			return dt.getRow(0).getLong(0);
		}
		return -1;	
	}
	
	/**
	 * 获取seq_recommandconditionid.nextval的值
	 * @return
	 * @throws SQLException
	 */
	public long get_seq_recommandconditionid() throws SQLException{
		//sql语句
		String sql="select seq_recommandconditionid.nextval from dual";
		//执行sql,返回序列号
		DataTable dt=dacClient.executeQuery(sql);
		if(dt!=null && dt.getRows().getCount()>0){
			return dt.getRow(0).getLong(0);
		}
		return -1;	
	}

	
	
	/**
	 * 增加推荐产品
	 * @param rmrpb RMRecommandProductBean对象
	 * @throws SQLException
	 */
	public void insertRMRecommandProduct(RMRecommandProductBean rmrpb) throws SQLException{
		List lst=new LinkedList();//存放参数,后面转成数组,确保索引对应相应的参数
		String sql="insert into td_recommandproduct values(?,?,?,?,sysdate,?,?,?,?,?,?)";//sql语句
		
		//增加参数
		lst.add(new Long(rmrpb.getRecommandId()));
		lst.add(rmrpb.getTitle());
		lst.add(rmrpb.getCityCode());
		lst.add(rmrpb.getState());
		lst.add(rmrpb.getAbstracts());
		lst.add(rmrpb.getStartTime());
		lst.add(rmrpb.getEndTime());
		lst.add(new Integer(rmrpb.getRmindex()));
		lst.add(rmrpb.getDisplaytype());
		lst.add(rmrpb.getDescrption());
		
		//将list转成数组,并执行sql
		dacClient.executeUpdate(sql,-1,lst.toArray());
	}

	/**
	 * 修改推荐产品信息
	 * @param rmrb RMRecommandProductBean对象
	 * @return
	 * @throws SQLException
	 */
	public int updateRMRecommandProduct(RMRecommandProductBean rmrpb) throws SQLException{
		StringBuffer sql=new StringBuffer();
		List lst=new LinkedList();//存放参数,后面转成数组,确保索引对应相应的参数
		
		sql.append("update td_recommandproduct set")
		   .append(" TITLE=? ,")
		   .append(" CITYCODE=? ,")
		   .append(" STATE=? ,")
		   .append(" ABSTRACT=? ,")
		   .append(" STARTTIME=? ,")
		   .append(" ENDTIME=? ,")
		   .append(" RMINDEX=? ,")
		   .append(" DISPLAYTYPE=? ,")
		   .append(" DESCRIPTION=?")		   
		   .append(" where RECOMMENDID=?");

		lst.add(rmrpb.getTitle());
		lst.add(rmrpb.getCityCode());
		lst.add(rmrpb.getState());
		lst.add(rmrpb.getAbstracts());
		lst.add(rmrpb.getStartTime());
		lst.add(rmrpb.getEndTime());
		lst.add(new Integer(rmrpb.getRmindex()));
		lst.add(rmrpb.getDisplaytype());
		lst.add(rmrpb.getDescrption());
		lst.add(new Long(rmrpb.getRecommandId()));
		
		//将list转成数组,并执行sql
		return dacClient.executeUpdate(sql.toString(),-1,lst.toArray());
	}

	/**
	 * 删除推荐产品(修改状态)
	 * @param number 产品序号
	 * @return
	 * @throws SQLException
	 */
	public int deleteRMRecommandProduct(Long number) throws SQLException{
		String sql="update td_recommandproduct t set t.state='D' where t.recommendid=?";
		Object[] object=new Object[1];
		object[0]=number;
		return dacClient.executeUpdate(sql,-1,object);
	}

	/**
	 * 通过条件查询符合条件的推荐产品总数
	 * @param map 开始时间STARTTIME,结束时间ENDTIME,标题TITLE,推荐产品摘要ABSTRACT,地市CITY,业务类型BUSINESSTYPE
	 * @return
	 * @throws SQLException 
	 */
	public int getRecommandProductCount(Map map) throws SQLException{
		StringBuffer sql=new StringBuffer();
		List lst=new LinkedList();//存放参数,后面转成数组,确保索引对应相应的参数
	
		//从hashmap中获取参数
		String sStartTime=(String)map.get("STARTTIME");
		String sEndTime=(String)map.get("ENDTIME");
		String sTitle=(String)map.get("TITLE");
		String sAbstract=(String)map.get("ABSTRACT");
		String sCity=(String)map.get("CITY");
		String sProduct=(String)map.get("PRODUCT");
		String sState=(String)map.get("STATE");
		
		
		//构造查询语句,增加相应的条件
		sql.append("select count(*) from(")
		   .append(" select distinct t1.recommendid")
		   .append(" from td_recommandproduct t1 left join td_recommendcondition t2")
		   .append(" on t1.recommendid=t2.recommendid where 1=1 ");
		   		   
		if(sState!=null && !sState.equals("") && !sState.equals("A")){
			sql.append(" and t1.state=?");
			lst.add(sState);
		}	
		if(sStartTime!=null && !sStartTime.equals("")){
			sql.append(" and t1.endtime>=to_date(?,'yyyy-mm-dd')");
			lst.add(sStartTime);
		}
		if(sEndTime!=null && !sEndTime.equals("")){
			sql.append(" and t1.endtime<=to_date(?,'yyyy-mm-dd')");
			lst.add(sEndTime);
		}
		if(sTitle!=null && !sTitle.equals("")){
			sql.append(" and t1.title like ?");
			lst.add("%"+sTitle+"%");
		}
		if(sAbstract!=null && !sAbstract.equals("")){
			sql.append(" and t1.abstract like ?");
			lst.add("%"+sAbstract+"%");
		}
		if(sCity!=null && !sCity.equals("") && !sCity.equals("0590")){
			sql.append(" and t1.citycode=?");
			lst.add(sCity);
		}
		if(sProduct!=null && !sProduct.equals("") && !sProduct.equals("0")){
			sql.append(" and t2.parameter=? and t2.state='E'");
			lst.add(sProduct);
		}
		
		sql.append(")");
		
		//将list转成数组,并执行sql
		DataTable dt= dacClient.executeQuery(sql.toString(),lst.toArray());
		
		if(dt!=null && dt.getRows().getCount()>0){
			return dt.getRow(0).getInt(0);
		}
		return -1;
	}
	
	/**
	 * 通过条件查询符合条件的推荐产品列表
	 * @param map
	 * @return
	 */
	public DataTable getRecommandProductList(Map map) throws SQLException{
		StringBuffer sql=new StringBuffer();
		List lst=new LinkedList();//存放参数,后面转成数组,确保索引对应相应的参数

		//从hashmap中获取参数
		String sStartTime=(String)map.get("STARTTIME");
		String sEndTime=(String)map.get("ENDTIME");
		String sTitle=(String)map.get("TITLE");
		String sAbstract=(String)map.get("ABSTRACT");
		String sCity=(String)map.get("CITY");
		String sProduct=(String)map.get("PRODUCT");
		String sStart=(String)map.get("START");
		String sEnd=(String)map.get("END");
		String action=(String)map.get("ACTION");
		String state=(String)map.get("STATE");
		
			
		//构造查询语句,增加相应的条件
		sql.append("select * from( select t.*,rownum rid from(select * from(")
		   .append(" select distinct t3.recommendid,title,abstract,")
		   .append(" cityname,rmindex,starttime,endtime,t3.state")		   
		   .append(" from (select t1.recommendid,t1.title,t1.abstract,")
		   .append(" t2.cityname,t1.rmindex,t1.starttime,t1.endtime,")
		   .append(" decode(t1.state,'E','可用','禁用') as state")
		   .append(" from td_recommandproduct t1,td_city t2")
		   .append(" where t1.citycode=t2.citycode");
					
		if(state!=null && !state.equals("") && !state.equals("A")){
			sql.append(" and t1.state=?");
			lst.add(state);
		}
		
		if(sStartTime!=null && !sStartTime.equals("")){
			sql.append(" and t1.endtime>=to_date(?,'yyyy-mm-dd')");
			lst.add(sStartTime);
		}
		if(sEndTime!=null && !sEndTime.equals("")){
			sql.append(" and t1.endtime<=to_date(?,'yyyy-mm-dd')");
			lst.add(sEndTime);
		}
		
		if(sCity!=null && !sCity.equals("") && !sCity.equals("0590")){
			sql.append(" and t1.citycode=?");
			lst.add(sCity);
		}
		
		if(sTitle!=null && !sTitle.equals("")){
			sql.append(" and t1.title like ?");
			lst.add("%"+sTitle+"%");
		}
		if(sAbstract!=null && !sAbstract.equals("")){
			sql.append(" and t1.abstract like ?");
			lst.add("%"+sAbstract+"%");
		}		
		
		sql	.append(" ) t3 left join td_recommendcondition t4")
			.append(" on t3.recommendid=t4.recommendid where 1=1");
		if(sProduct!=null && !sProduct.equals("") && !sProduct.equals("0")){
			sql.append(" and t4.parameter=? and t4.state='E'");
			lst.add(sProduct);

		}		
		sql.append(" )order by state desc,rmindex desc,starttime desc )t)");
		
		if(action!=null && !action.equals("")){
			if(sStart==null || !sStart.equals("")){
				//特别推荐前3条
				if(action.equals("special")){
					sStart="1";
					sEnd="4";
				}
				//其他推荐从4开始
				else if(action.equals("others")){
					if(sStart==null || sStart.equals("")){
						sStart="4";
						sEnd=""+(Integer.parseInt(sStart)+this.PAGESIZE);
					}
				}
			}
		}
		//没有告诉我们action是特别,还是其它,表示是用条件查询出来的,就有传start,end进来
		else{
			sStart="1";
			sEnd=""+(Integer.parseInt(sStart)+this.PAGESIZE);
		}
		//分页处理

		sql.append(" where rid>=?");
		lst.add(sStart);
		sql.append(" and rid<?");
		lst.add(sEnd);
		
		//将list转成数组,并执行sql		
		return dacClient.executeQuery(sql.toString(),lst.toArray());
	}
	
	/**
	 * 增加推荐产品条件
	 * @param rmrcb RMRecommendConditionBean对象
	 */
	public void insertRMRecommendCondition(RMRecommendConditionBean rmrcb) throws SQLException{
		List lst=new LinkedList();//存放参数,后面转成数组,确保索引对应相应的参数
		String sql="insert into td_recommendcondition values(?,?,?,?,?,?,?)";
		lst.add(new Long(rmrcb.getConditionId()));
		lst.add(new Long(rmrcb.getRecommandId()));
		lst.add(rmrcb.getConditionType());
		lst.add(rmrcb.getExpression());
		lst.add(rmrcb.getParameter());
		lst.add(rmrcb.getState());
		lst.add(new Long(rmrcb.getUpConditionId()));
		
		//将list转成数组,并执行sql
		dacClient.executeUpdate(sql,-1,lst.toArray());
	}
	
	
	/**
	 * 删除推荐产品条件(修改状态)
	 * @param number 产品序号
	 * @return
	 * @throws SQLException
	 */
	public int deleteRMRecommandCondition(Long number) throws SQLException{
		String sql="update td_recommendcondition set state='D' where recommendId=?";
		Object[] object=new Object[1];
		object[0]=number;
		//执行sql
		return dacClient.executeUpdate(sql,-1,object);
	}
	

	/**
	 * 通过产品序号获取产品信息
	 * @param number 产品序号
	 * @return
	 * @throws SQLException
	 */
	public DataTable getRMRecommandProductBean(Long number)throws SQLException{
		String sql="select t1.RECOMMENDID,t1.TITLE,T1.CITYCODE,"+
						  "t1.ABSTRACT,t1.STARTTIME,t1.ENDTIME,"+
						  "t1.RMINDEX,t1.DISPLAYTYPE,t1.DESCRIPTION,t1.STATE "+
				   " from td_recommandproduct t1"+
				   " WHERE t1.recommendid=?";
		Object[] object=new Object[1];
		object[0]=number;
		//执行sql
		return dacClient.executeQuery(sql,object);
		
	}
	
	/**
	 * 通过产品序号获取产品条件信息
	 * @param number 产品序号
	 * @return
	 * @throws SQLException
	 */
	public DataTable getRMRecommendConditionBean(Long number)throws SQLException{
		StringBuffer sql=new StringBuffer();
		sql.append("select t.recommendid,t.conditiontype,t.experssion,t.parameter,t.upconditionid ")
		   .append(" decode(t.conditiontype,")
		   .append(" 'P',(select productName from td_product where productId=t.parameter ),")
		   .append(" 'F',(select funname from td_prodfun p  where p.prodfunid=t.parameter),")
		   .append(" t.parameter) as parameterName")
		   .append(" from td_recommendcondition t")
		   .append(" where t.recommendid=?")
		   .append(" and t.state='E'")
		   .append(" connect by prior t.conditionid = t.upconditionid")
		   .append(" start with t.upconditionid = 0");
		   
		Object[] object=new Object[1];
		object[0]=number;
		//执行sql
		return dacClient.executeQuery(sql.toString(),object);
	}
	
	/**
	 * 获取某个推荐产品下的条件id和产品号
	 * @param number 推荐产品id
	 * @throws SQLException
	 */
	public DataTable getUpRMRecommend(Long number)throws SQLException{
		StringBuffer sql=new StringBuffer();
		sql.append("select CONDITIONID,PARAMETER")
		   .append(" from td_recommendcondition")
		   .append(" where recommendid=?")
		   .append(" and conditiontype='P'")
		   .append(" and state='E'");
		   
		Object[] object=new Object[1];
		object[0]=number;
		//执行sql
		return dacClient.executeQuery(sql.toString(),object);
	}
	
	/**
	 * 获取所有的产品列表
	 * @return
	 * @throws SQLException
	 */
	public DataTable getProduct()throws SQLException{
		String sql="select T.productid,T.PRODUCTNAME from td_product t where t.upproductid=0";
		return dacClient.executeQuery(sql);
	}
		/**
	 * 通过产品序号获取所属的程控
	 * @param productId 产品序号
	 * @param cityCode 城市编号 
	 * @return
	 * @throws SQLException
	 */
	public DataTable getProgrammedByProduct(String productId,String cityCode)throws SQLException{
		StringBuffer sql=new StringBuffer();
		sql.append("select (p.productId || ';' || p.prodfunid) prodfunid,p.funname ")
		   .append(" from td_prodfun p ")
		   .append(" where p.productid=? ")
		   .append(" and p.citycode=?")
		   .append(" and p.FUNTYPE='C'")
		   .append("and p.OUTPUT='1'")
		   .append("and p.funstate='E'");
		Object[] object=new Object[2];
		object[0]=productId;
		object[1]=cityCode;

		return dacClient.executeQuery(sql.toString(),object);
	}

	/**
	 * 推荐产品的下属程控
	 * @param upConditionId
	 * @return 
	 * @throws SQLException
	 */
	public DataTable getProgrammed(String upConditionId) throws SQLException{
		StringBuffer sql=new StringBuffer();
		sql.append("select (select parameter from td_recommendcondition t2 ")
		   .append(" where t2.conditionid=t1.upconditionid) || ';' || t1.parameter as prodfunid")
		   .append(" from td_recommendcondition t1")
		   .append(" where upconditionid=?")
		   .append(" and state='E'");
		Object[] object=new Object[1];
		object[0]=upConditionId;
		return dacClient.executeQuery(sql.toString(),object);
	}

	/**
	 * 根据标识字符获取最高,最低,普通级别指数
	 * @param flag
	 * @return
	 * @throws SQLException
	 */
	public int get_max_min_avg_rmindex(String flag)throws SQLException{
		StringBuffer sql=new StringBuffer();
		if(flag.equals("H")){
			flag=" max(rmindex) ";
		}else if(flag.equals("L")){
			flag=" min(rmindex) ";
		}else{
			flag=" floor(avg(rmindex)) ";
		}
		sql.append("select ").append(flag).append(" from td_recommandproduct");
		DataTable dt=dacClient.executeQuery(sql.toString());
		if(dt!=null && dt.getRows().getCount()>0){
			return dt.getRow(0).getInt(0);
		}else{
			return 0;
		}
	}
}