隨筆分類(157)

//@author lzj
//lzj:根據條件動態查詢
  public List findPhotoAllDyn(String isshow,String classname,String userid,String start, String end,String phototypeid,String pagenum){
  StringBuffer hqlStr = new StringBuffer("select pt.STypename,pc.IPhotoclass,pc.SNames,pc.SUsername,pc.IStatus,pc.IShow ,pc.DAdddate ,pt.ITypeid,count(pr) from TPhototype pt right outer join pt.TPhotoclasses pc left join ");
    try{
     if(Integer.parseInt(isshow)==0)hqlStr.append("pc.TPicturescores pr Where pc.IShow=0 ");
     if(Integer.parseInt(isshow)==1)hqlStr.append("pc.TShowopuses pr Where pc.IShow=1 ");
     if(Integer.parseInt(isshow)==-1)hqlStr.append("pc.TPicturescores pr Where pc.IShow is not null ");
     }catch(Exception e){
      hqlStr.append("pc.TPicturescores pr Where pc.IShow=0 ");
     }
    if(classname!="")hqlStr.append("and pc.SNames=:sn "); 
    if(userid!="")hqlStr.append("and pc.SUsername =:SUsername ");
    if(start!="")hqlStr.append("and pc.DAdddate between :Start ");
    if(end!="")hqlStr.append("and :End ");
    if(phototypeid!="")hqlStr.append("and pt.ITypeid = :ITypeid ");
    hqlStr.append("group by pt.STypename,pc.IPhotoclass,pc.SNames,pc.SUsername,pc.IStatus,pc.IShow ,pc.DAdddate ,pt.ITypeid order by count(pr) desc ");
    Session session =this.getSession();
   Query query = session.createQuery(hqlStr.toString());
   if(classname!="")query.setString("sn",classname);
   if(userid!="")query.setString("SUsername",userid);
   if(phototypeid!="")query.setString("ITypeid",phototypeid);
   SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  
   if(start!="")
    try {
     query.setDate("Start",sdf.parse(start));
    } catch (ParseException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
    }
   if(end!="")
    try {
     query.setDate("End",sdf.parse(end));
    } catch (ParseException e) {
     // TODO Auto-generated catch block
     e.printStackTrace();
    }
   query.setCacheable(true);
   if (pagenum == null){
        /*如果pagenum是空，則數據從第一條開始*/
          query.setFirstResult(0);
        /*設置查詢開始的第幾條數據，這里是從第1條開始*/
          query.setMaxResults(PageSize);
        /*設置查詢數據條數*/
          }else{
           try{
           Integer p = (Integer.valueOf(pagenum)-1) * PageSize;
           query.setFirstResult(p);
           query.setMaxResults(PageSize);
           }catch (Exception e){
            query.setFirstResult(0);
         query.setMaxResults(PageSize);  
           }
          }
   List result = query.list();
      List templist = new ArrayList();
      for (Iterator it = result.iterator();it.hasNext();){
       Map mm = new HashMap();
       Object[] row=(Object[])it.next();
        Query query2 =session.createQuery("from TPopularshow ps where ps.SUpid = :s");
        query2.setString("s", ((Long)row[1]).toString());
        query2.setCacheable(true);
        List result2 = query2.list();
        Iterator it2 = result.iterator();
         if (query2.list().size()>0){
          mm.put("ps", "yes");
         } else{
          mm.put("ps", "");
         }
       mm.put("STypename", (String)row[0]);
       mm.put("IPhotoclass", (Long)row[1]);
       mm.put("SNames", (String)row[2]);
       mm.put("SUsername", (String)row[3]);
       //mm.put("IStatus",(Long)row[4]);
       if((Long)row[4]==null){
        mm.put("IStatus2","");
       }else if((Long)row[4]==1){
        mm.put("IStatus","半開放");
        mm.put("IStatus2",(Long)row[4]);
       }else if((Long)row[4]==2){
       mm.put("IStatus","開放");
       mm.put("IStatus2",(Long)row[4]);
       }else if((Long)row[4]==0){
        mm.put("IStatus","不開放");
        mm.put("IStatus2",(Long)row[4]);
       }
       if((Long)row[5]==0){
        mm.put("IShow", "普通相冊");
        mm.put("ptype", 1);
        mm.put("IShowb", 3);
       }else if((Long)row[5]==1){
        mm.put("IShow", "特殊相冊");
        mm.put("ptype", 2);
        mm.put("IShowb", 4);
       }
       //mm.put("IShow", (Long)row[5]);
       mm.put("DAdddate", (Date)row[6]);
       mm.put("Count", (Long)row[8]);
       templist.add(mm);
      }
      session.close();
      return templist;
     }