grouptrafficstatmanagebean.java.svn-base

利用sip协议

package com.xunlei.callcenter.web.model;

import java.text.SimpleDateFormat;
import java.util.*;

import javax.servlet.http.HttpServletRequest;

import antlr.collections.Enumerator;

import com.xunlei.callcenter.vo.*;
import com.xunlei.common.facade.IFacadeCommon;
import com.xunlei.common.util.PagedFliper;
import com.xunlei.common.web.bean.PagedListDataModel;
import com.xunlei.common.web.model.AbstractManagedBean;

public class GrouptrafficstatManageBean extends com.xunlei.callcenter.web.model.BaseManagedBean {
	private Grouptrafficstat conditionbean;
	

	private Calendar calendar = Calendar.getInstance();
	public GrouptrafficstatManageBean() {	
		super();
		
	}
		public PagedListDataModel getQuerygroupslist() {
		
	        int rowcount =IFacadeCommon.INSTANCE.getRecordCount(getSqlCount());
	        List list =facade.getGrouptrafficstatView(getSql(), rowcount, getPageSize(), getPageNoInt());
	        
	        return createPagedListDataModel().setDataList(rowcount, list);
	    }
		
		public String godetail() {
	        //long seqid = Long.valueOf(findParameter("seqid"));
	       // mergeBean(facade.(seqid));
	        return "go_detail";
	       
	    }
		
		
		public List<Groups> getAllGroups(){
			List<Groups> list = new ArrayList<Groups>();
			Groups groups = new Groups();
			groups.setGroupidname("日期");
			list.add(groups);
			List glist = facade.getAllGroups();
			for(int i=0;glist!=null&&i<glist.size();i++){
				Groups g = (Groups)glist.get(i);
				if(g.getPgroupidid().equals("-1"))continue;
				list.add(g);
			}
			
			groups = new Groups();
			groups.setGroupidname("总计");
			list.add(groups);
			return list;
		}
			
		
		
		
		public String getSqlCount(){
			List<Groups> list = facade.getAllGroups();
			String datebegin = this.getConditionbean().getDateBegin();
			String dateend = this.getConditionbean().getDateEnd();
			StringBuilder str = new StringBuilder("select count(*) from ");
			str.append("(select BalanceDate, ");
			for(int i=0;list!=null&&i<list.size();i++){
				Groups groups = (Groups)list.get(i);
				if(groups.getPgroupidid().equals("-1"))continue;
				String groupname = groups.getGroupidname();
				String groupid =groups.getGroupid();
				str.append("sum(if(groupid='"+groupname+"',traffic,0)) 'a"+groupid+"',");	
			}
			str.append("sum(traffic) total from grouptrafficstat " );
			if(datebegin!=null&&!"".equals(datebegin.trim())&&dateend!=null&&!"".equals(dateend)){
				str.append(" where BalanceDate >="+datebegin.replaceAll("-", "")+" and BalanceDate<="+dateend.replaceAll("-", ""));
			}
			str.append(" group by BalanceDate union all select _utf8'统计', ");
			for(int i=0;list!=null&&i<list.size();i++){
				Groups groups = (Groups)list.get(i);
				if(groups.getPgroupidid().equals("-1"))continue;
				String groupname = groups.getGroupidname();
				String groupid =groups.getGroupid();
				str.append("sum(if(groupid='"+groupname+"',traffic,0)) 'a"+groupid+"',");	
			}
			str.append("sum(traffic) from ( select groupid,sum(traffic) as traffic from grouptrafficstat " );
			if(datebegin!=null&&!"".equals(datebegin.trim())&&dateend!=null&&!"".equals(dateend)){
				str.append(" where BalanceDate >="+datebegin.replaceAll("-", "")+" and BalanceDate<="+dateend.replaceAll("-", ""));
			}
			str.append(" group by GroupId )a) b ");
			return str.toString();
		}
		
		public String getSql(){
			List<Groups> list = facade.getAllGroups();
			String datebegin = this.getConditionbean().getDateBegin();
			String dateend = this.getConditionbean().getDateEnd();
			StringBuilder str = new StringBuilder("select BalanceDate," );
			for(int i=0;list!=null&&i<list.size();i++){
				Groups groups = (Groups)list.get(i);
				if(groups.getPgroupidid().equals("-1"))continue;				
				String groupid =groups.getGroupid();
				str.append("a"+groupid+",");	
			}	
			str.append(" total");	
			str.append(" from ");
			str.append("(select BalanceDate, ");
			for(int i=0;list!=null&&i<list.size();i++){
				Groups groups = (Groups)list.get(i);
				if(groups.getPgroupidid().equals("-1"))continue;
				String groupname = groups.getGroupidname();
				String groupid =groups.getGroupid();
				str.append("sum(if(groupid='"+groupname+"',traffic,0)) 'a"+groupid+"',");	
			}
			str.append("sum(traffic) total from grouptrafficstat " );
			if(datebegin!=null&&!"".equals(datebegin.trim())&&dateend!=null&&!"".equals(dateend)){
				str.append(" where BalanceDate >="+datebegin.replaceAll("-", "")+" and BalanceDate<="+dateend.replaceAll("-", ""));
			}
			str.append(" group by BalanceDate  union all select _utf8'统计', ");
			for(int i=0;list!=null&&i<list.size();i++){
				Groups groups = (Groups)list.get(i);
				if(groups.getPgroupidid().equals("-1"))continue;
				String groupname = groups.getGroupidname();
				String groupid =groups.getGroupid();
				str.append("sum(if(groupid='"+groupname+"',traffic,0)) 'a"+groupid+"',");	
			}
			str.append("sum(traffic) from ( select groupid,sum(traffic) as traffic from grouptrafficstat " );
			if(datebegin!=null&&!"".equals(datebegin.trim())&&dateend!=null&&!"".equals(dateend)){
				str.append(" where BalanceDate >="+datebegin.replaceAll("-", "")+" and BalanceDate<="+dateend.replaceAll("-", ""));
			}
			str.append(" group by GroupId )a) b ");
			return str.toString();
		}
	
		public String goout()  {
			Grouptrafficstat t = getConditionbean();
			if(null!=t){
				HttpServletRequest request = getHttpServletRequest();
				if(t.getDateBegin()!=null&&!"".equals(t.getDateBegin().trim())){
					request.setAttribute("pbegin", t.getDateBegin());
				}
				if(t.getDateEnd()!=null&&!"".equals(t.getDateEnd())){
					request.setAttribute("pend", t.getDateEnd());
				}
			}
	        return "go_out";
	    }
		
		public Grouptrafficstat getConditionbean() {
			if (this.conditionbean == null) {
				this.conditionbean = findBean(Grouptrafficstat.class);
			}				
			return conditionbean;
		}
	   
		public void setConditionbean(Grouptrafficstat conditionbean) {
			if(conditionbean != null) {
	            this.conditionbean = conditionbean; 
	        }
		}
}