<?php

/**
 * report actions.
 *
 * @package    foniva
 * @subpackage report
 * @author     Your name here
 * @version    SVN: $Id: actions.class.php 12479 2008-10-31 10:54:40Z fabien $
 */
class reportActions extends sfActions
{
 /**
  * Executes index action
  *
  * @param sfRequest $request A request object
  */
  public function executeIndex(sfWebRequest $request)
  {
	if($this->getUser()->hasCredential("REPORTS_AGENTS"))
    	$this->redirect('report/agents');
	else if($this->getUser()->hasCredential("REPORTS_INBOUNDS"))
    	$this->redirect('report/inbounds');
	else if($this->getUser()->hasCredential("REPORTS_OUTBOUNDS"))
    	$this->redirect('report/outbounds');
	else if($this->getUser()->hasCredential("REPORTS_QUEUES"))
    	$this->redirect('report/queues');
	else
		$this->forward404();
  }

  public function executeAgents(sfWebRequest $request)
  {
	$this->reportcategory = 'AGENT';
    $this->getreports($this->reportcategory);
	$this->buildagentreportform($request);
	$this->reportdefparams = array();
	$this->getResponse()->addStylesheet('chosen/chosen.css', 'last');
	$this->getResponse()->addJavascript('chosen/chosen.jquery.min.js', 'last');
	$this->getResponse()->addJavascript('jquery.toggleswitch.min.js', 'last');
	$this->getResponse()->addStylesheet('toggleswitch.css', 'last');	
  }
  
  public function executeInbounds(sfWebRequest $request)
  {
	$this->reportcategory = 'INBOUND';
    $this->getreports($this->reportcategory);
	$this->buildinboundreportform($request);
	$this->reportdefparams = array();
	$this->getResponse()->addStylesheet('chosen/chosen.css', 'last');
	$this->getResponse()->addJavascript('chosen/chosen.jquery.min.js', 'last');
	$this->getResponse()->addJavascript('jquery.toggleswitch.min.js', 'last');
	$this->getResponse()->addStylesheet('toggleswitch.css', 'last');	
  }
  
  public function executeCampaigns(sfWebRequest $request)
  {
	$this->reportcategory = 'OUTBOUND';
    $this->getreports($this->reportcategory);
	$this->buildoutboundreportform($request);
	$this->reportdefparams = array();
	$this->getResponse()->addStylesheet('chosen/chosen.css', 'last');
	$this->getResponse()->addJavascript('chosen/chosen.jquery.min.js', 'last');
	$this->getResponse()->addJavascript('jquery.toggleswitch.min.js', 'last');
	$this->getResponse()->addStylesheet('toggleswitch.css', 'last');	
  }
  
  public function executeQueues(sfWebRequest $request)
  {
	$this->reportcategory = 'QUEUE';
    $this->getreports($this->reportcategory);
	$this->buildqueuereportform($request);
	$this->reportdefparams = array();
	$this->getResponse()->addStylesheet('chosen/chosen.css', 'last');
	$this->getResponse()->addJavascript('chosen/chosen.jquery.min.js', 'last');
	$this->getResponse()->addJavascript('jquery.toggleswitch.min.js', 'last');
	$this->getResponse()->addStylesheet('toggleswitch.css', 'last');	
  }
  
  public function executeViewreport(sfWebRequest $request)
  {
	$this->forward404Unless(($rarray =  $request->getParameter('report')));
	$this->forward404Unless(($rid = $rarray['report_id']));	
	
	$this->report = Doctrine::getTable('ConfReport')->find($rid);
	$this->forward404Unless($this->report);	
	
	$this->forward('report', $this->report->getRequesturl());	
  }
  
  public function executeAgentstatussummary(sfWebRequest $request)
  {	
  	$this->reportcategory = 'AGENT';
	$this->agentformparse($request);
	$this->getreports('AGENT');
	$this->buildagentreportform($request);
	
	  
	$this->tableclassname = 'log_resume_daily_agent_event';
	$this->tableclassname_doctrine = 'LogResumeDailyAgentEvent';
	if($this->stat_step == 'hourly')
	{
		$this->tableclassname = 'log_resume_hourly_agent_event';
		$this->tableclassname_doctrine = 'LogResumeHourlyAgentEvent';
	}
	if($this->stat_step == 'monthly')
	{
		$this->tableclassname = 'log_resume_monthly_agent_event';
		$this->tableclassname_doctrine = 'LogResumeMonthlyAgentEvent';
	}
	if($this->stat_step == 'weekly')
	{
		$this->tableclassname = 'log_resume_weekly_agent_event';
		$this->tableclassname_doctrine = 'LogResumeWeeklyAgentEvent';
	}
	if($this->stat_step == '15min')
	{
		$this->tableclassname = 'log_resume_15min_agent_event';
		$this->tableclassname_doctrine = 'LogResume15minAgentEvent';
	}
	if($this->stat_step == '30min')
	{
		$this->tableclassname = 'log_resume_30min_agent_event';
		$this->tableclassname_doctrine = 'LogResume30minAgentEvent';
	}
	
	
	$this->stateltscolumn = 'agentdbid';
	if($this->stat_aggregate == 'group')
		$this->stateltscolumn = 'groupdbid';
	if($this->stat_aggregate == 'site')
		$this->stateltscolumn = 'sitedbid';
	
	
	$fieldvalues = array();
	$pdo = Doctrine_Manager::getInstance()->getCurrentConnection()->getDbh();
	
	$query = '';
	$query_count = '';
	
	$query_fields = 'SELECT r.covereddate as covereddate, r.agentdbid as agentdbid, 
								r.groupdbid as groupdbid, r.sitedbid as sitedbid, 
								r.agentstatus as agentstatus, r.notreadyreason as notreadyreason, 							 	
							 	sum(r.nbstates) as data_nbstates, sum(r.totalduration) as data_totalduration, round(sum(r.totalactivitycost), 2) as data_totalactivitycost,
								ca.username as agentusername,
								cai.firstname as agentfirstname, cai.lastname as agentlastname,
								cg.name as agentgroupname
								';
								
	$query_count_fields = 'SELECT COUNT(*) as nbtotalrecords 
								';
								
								
	$query_tables = '
					FROM '.$this->tableclassname.' r
					LEFT JOIN conf_agent ca ON ca.dbid = r.agentdbid
					LEFT JOIN conf_agent_info cai ON ca.info_fk = cai.dbid
					LEFT JOIN conf_group cg ON cg.dbid = r.groupdbid
					
					
					';
					
	$query_count_tables = '
					FROM '.$this->tableclassname.' r
					LEFT JOIN conf_agent ca ON ca.dbid = r.agentdbid
					';
	
	$query_filters = '
				 WHERE 
				(r.covereddate >= :stat_startdate_ext and r.covereddate <= :stat_enddate_ext ) AND r.agentstatus != "LOGGED OUT" 
				';
	
	$fieldvalues['stat_startdate_ext'] = $this->stat_startdate_ext;
	$fieldvalues['stat_enddate_ext'] = $this->stat_enddate_ext;		
	
	if(!fonivaFilter::isCurrentUserSuperTenant())
	{
	  $query_filters .= ' AND r.tenant_fk = :tenantdbid';
	  $fieldvalues['tenantdbid'] = fonivaFilter::getCurrentUserTenantId();
	}
	
	if($this->stat_agent == 'USERFOLLOWEDAGENTS')
	{
	  $query_filters .= ' AND r.agentdbid IN (SELECT agent_fk FROM conf_user_followedagent WHERE user_fk = :currentuserdbid)';
	  $fieldvalues['currentuserdbid'] = fonivaFilter::getCurrentUser()->getDbid();
	} 
	else if($this->stat_agent != '')
	{
	  $query_filters .= ' AND r.agentdbid = :stat_agent';
	  $fieldvalues['stat_agent'] = $this->stat_agent;
	}
	else if($this->stat_agent == '')
	{
		if(!$this->include_inactive_agents)
		  $query_filters .= ' AND ca.active = "yes" ';
	}
	
	if($this->stat_group != '')
	{
	  $query_filters .= ' AND r.groupdbid = :stat_group';
	  $fieldvalues['stat_group'] = $this->stat_group;
	}
	
	// filter by allowed groups
	if(!fonivaFilter::canCurrentUserManageAllGroups())
	{
		$this->groups = fonivaFilter::currentUserManagedGroups();
		$this->groups_list = array();
		$this->groupids_list = array();
		$allowed = false;
		foreach($this->groups as $g)
		{
			$this->groups_list[$g->getDbid()] = $g;
			array_push($this->groupids_list, $g->getDbid());
		}
		
		$this->data->andWhere('r.groupdbid IN ?', array($this->groupids_list));
	}
	
	if($this->stat_team != '')
	{
	  $query_filters .= ' AND r.teamdbid = :stat_team';
	  $fieldvalues['stat_team'] = $this->stat_team;
	}
	
	if($this->stat_site != '')
	{
	  $query_filters .= ' AND r.sitedbid = :stat_site';
	  $fieldvalues['stat_site'] = $this->stat_site;
	}
	
	$query_groupby = ' group by r.'.$this->stateltscolumn.', r.covereddate, r.agentstatus, r.notreadyreason';
	$query_orderby = ' order by r.'.$this->stateltscolumn.', r.covereddate, r.agentstatus, r.notreadyreason';
	
	$query = $query_fields.''.$query_tables.''.$query_filters.''.$query_groupby.''.$query_orderby;
	if(!$this->csv_export)
		$query .= ' LIMIT '.(($this->getRequestParameter('page',1)-1)*sfConfig::get('app_display_nbrecordperpagereports')).','.sfConfig::get('app_display_nbrecordperpagereports');
	
	$query_count = $query_count_fields.''.$query_count_tables.''.$query_filters.''.$query_groupby;
	
	
	$query = $pdo->prepare($query);
	$query_count = $pdo->prepare($query_count);
	
	if(!$this->csv_export)
	{
	  $query_count->execute($fieldvalues);
	  $countdata = $query_count->fetchAll();
	  
	  $nbtotalrecordsfounded = sizeof($countdata); //[0]['nbtotalrecords'];
	  
	  $this->pager = new FonivaPager($this->tableclassname_doctrine, sfConfig::get('app_display_nbrecordperpagereports'));
	  $this->pager->setPage($this->getRequestParameter('page',1));
	  $this->pager->assignNbResults($nbtotalrecordsfounded);
	  $this->pager->init();
	  
		  
	  $query->execute($fieldvalues);
	  $this->data = $query->fetchAll();
	}
	else
	{
		  
	  $query->execute($fieldvalues);
	  $this->data = $query->fetchAll();
	}
	
	
	$infos = array();
	$this->eltinfos = array();
	if($this->stat_aggregate == 'agent') //we want to show agent details
	{
		$infos = Doctrine_Query::create()
					->select('a.dbid as dbid, i.firstname as firstname, i.lastname as lastname , a.username as username')
					->from('ConfAgent a')
					->leftJoin('a.ConfAgentInfo i ON i.dbid = a.info_fk')
					->where('1');
		if($this->stat_group != '')
			$infos->andWhere('a.group_fk = ?', $this->stat_group);
		if($this->stat_team != '')
			$infos->andWhere('a.team_fk = ?', $this->stat_team);
		if($this->stat_site != '')
			$infos->andWhere('a.site_fk = ?', $this->stat_site);
		
		if($this->stat_agent == 'USERFOLLOWEDAGENTS')
	    {
		  $subq = Doctrine_Query::create()
		  				->select('fa.agent_fk')
		  				->from('ConfUserFollowedagent fa')
						->where('fa.user_fk = "'.fonivaFilter::getCurrentUser()->getDbid().'"');
		  $infos->andWhere('a.dbid IN ('.$subq->getDql().')');
	    }
	    else if($this->stat_agent != '')
			$infos->andWhere('a.dbid = ?', $this->stat_agent);
			
	    if(!fonivaFilter::isCurrentUserSuperTenant())
		  $infos->andWhere('a.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	    $infos = $infos->execute();
		foreach($infos as $i)
			$this->eltinfos[$i->dbid] = $i;
	}
	else if($this->stat_aggregate == 'group')
	{
		$infos = Doctrine_Query::create()
					->select('a.dbid as dbid, a.name as name')
					->from('ConfGroup a');
		if($this->stat_group != '')
			$infos->andWhere('a.dbid = ?', $this->stat_group);
	    if(!fonivaFilter::isCurrentUserSuperTenant())
		  $infos->andWhere('a.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	    $infos = $infos->execute();
		
		foreach($infos as $i)
			$this->eltinfos[$i->dbid] = $i;
	}
	else if($this->stat_aggregate == 'team')
	{
		$infos = Doctrine_Query::create()
					->select('a.dbid as dbid, a.name as name')
					->from('ConfTeam a');
		if($this->stat_team != '')
			$infos->andWhere('a.dbid = ?', $this->stat_team);
	    if(!fonivaFilter::isCurrentUserSuperTenant())
		  $infos->andWhere('a.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	    $infos = $infos->execute();
		
		foreach($infos as $i)
			$this->eltinfos[$i->dbid] = $i;
	}
	else if($this->stat_aggregate == 'site')
	{
		$infos = Doctrine_Query::create()
					->select('a.dbid as dbid, a.name as name')
					->from('ConfSite a');
		if($this->stat_site != '')
			$infos->andWhere('a.dbid = ?', $this->stat_site);
	    if(!fonivaFilter::isCurrentUserSuperTenant())
		  $infos->andWhere('a.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	    $infos = $infos->execute();
		
		foreach($infos as $i)
			$this->eltinfos[$i->dbid] = $i;
	}
	
	if($this->csv_export)
	{
		$csvfilename = 'foniva_report_' . $this->getActionName() . '.csv';
		$this->setlayout(false);
		$this->getResponse()->clearHttpHeaders();
		$this->getResponse()->setHttpHeader('Content-Type', 'application/vnd.ms-excel');
		$this->getResponse()->setHttpHeader('Content-Disposition', 'attachment; filename='.$csvfilename);
		return 'Csvexport';
	}
	else
	{  
	    $this->getResponse()->addJavascript('jquery.toggleswitch.min.js', 'last');
	    $this->getResponse()->addStylesheet('toggleswitch.css', 'last');
	}
  }
  
  public function executeAgentstatusloggedvsnotreadysummary(sfWebRequest $request)
  {	
  	$this->reportcategory = 'AGENT';
	$this->agentformparse($request);
	$this->getreports('AGENT');
	$this->buildagentreportform($request);
	
	  
	$this->tableclassname = 'log_resume_daily_agent_event';
	$this->tableclassname_doctrine = 'LogResumeDailyAgentEvent';
	if($this->stat_step == 'hourly')
	{
		$this->tableclassname = 'log_resume_hourly_agent_event';
		$this->tableclassname_doctrine = 'LogResumeHourlyAgentEvent';
	}
	if($this->stat_step == 'monthly')
	{
		$this->tableclassname = 'log_resume_monthly_agent_event';
		$this->tableclassname_doctrine = 'LogResumeMonthlyAgentEvent';
	}
	if($this->stat_step == 'weekly')
	{
		$this->tableclassname = 'log_resume_weekly_agent_event';
		$this->tableclassname_doctrine = 'LogResumeWeeklyAgentEvent';
	}
	if($this->stat_step == '15min')
	{
		$this->tableclassname = 'log_resume_15min_agent_event';
		$this->tableclassname_doctrine = 'LogResume15minAgentEvent';
	}
	if($this->stat_step == '30min')
	{
		$this->tableclassname = 'log_resume_30min_agent_event';
		$this->tableclassname_doctrine = 'LogResume30minAgentEvent';
	}
	
	
	$this->stateltscolumn = 'agentdbid';
	if($this->stat_aggregate == 'group')
		$this->stateltscolumn = 'groupdbid';
	if($this->stat_aggregate == 'site')
		$this->stateltscolumn = 'sitedbid';
	
	$fieldvalues = array();
	$pdo = Doctrine_Manager::getInstance()->getCurrentConnection()->getDbh();
	
	$query = '';
	$query_count = '';
	
	$query_fields = 'SELECT r.covereddate as covereddate, r.agentdbid as agentdbid, 
								r.groupdbid as groupdbid, r.sitedbid as sitedbid, r.agentstatus as agentstatus, r.notreadyreason as notreadyreason, 							 	
							 	sum(r.nbstates) as data_nbstates, sum(r.totalduration) as data_totalduration, round(sum(r.totalactivitycost), 2) as data_totalactivitycost,
								ca.username as agentusername,
								cai.firstname as agentfirstname, cai.lastname as agentlastname,
								cg.name as agentgroupname,
								si.name as agentsitename
								
								
								';
								
	$query_count_fields = 'SELECT COUNT(*) as nbtotalrecords 
								';
								
								
	$query_tables = '
					FROM '.$this->tableclassname.' r
					LEFT JOIN conf_agent ca ON ca.dbid = r.agentdbid
					LEFT JOIN conf_agent_info cai ON ca.info_fk = cai.dbid
					LEFT JOIN conf_group cg ON cg.dbid = r.groupdbid
					LEFT JOIN conf_site si ON si.dbid = r.sitedbid
					
					
					';
					
	$query_count_tables = '
					FROM '.$this->tableclassname.' r
					LEFT JOIN conf_agent ca ON ca.dbid = r.agentdbid
					';
	
	$query_filters = '
				 WHERE 
				(r.covereddate >= :stat_startdate_ext and r.covereddate <= :stat_enddate_ext ) AND r.agentstatus != "LOGGED OUT" 
				';
	
	$fieldvalues['stat_startdate_ext'] = $this->stat_startdate_ext;
	$fieldvalues['stat_enddate_ext'] = $this->stat_enddate_ext;		
	
	if(!fonivaFilter::isCurrentUserSuperTenant())
	{
	  $query_filters .= ' AND r.tenant_fk = :tenantdbid';
	  $fieldvalues['tenantdbid'] = fonivaFilter::getCurrentUserTenantId();
	}
	
	
	if($this->stat_agent == 'USERFOLLOWEDAGENTS')
	{
	  $query_filters .= ' AND r.agentdbid IN (SELECT agent_fk FROM conf_user_followedagent WHERE user_fk = :currentuserdbid)';
	  $fieldvalues['currentuserdbid'] = fonivaFilter::getCurrentUser()->getDbid();
	} 
	else if($this->stat_agent != '')
	{
	  $query_filters .= ' AND r.agentdbid = :stat_agent';
	  $fieldvalues['stat_agent'] = $this->stat_agent;
	}
	else if($this->stat_agent == '')
	{
		if(!$this->include_inactive_agents)
		  $query_filters .= ' AND ca.active = "yes" ';
	}
	
	if($this->stat_group != '')
	{
	  $query_filters .= ' AND r.groupdbid = :stat_group';
	  $fieldvalues['stat_group'] = $this->stat_group;
	}
	
	// filter by allowed groups
	if(!fonivaFilter::canCurrentUserManageAllGroups())
	{
		$this->groups = fonivaFilter::currentUserManagedGroups();
		$this->groups_list = array();
		$this->groupids_list = array();
		$allowed = false;
		foreach($this->groups as $g)
		{
			$this->groups_list[$g->getDbid()] = $g;
			array_push($this->groupids_list, $g->getDbid());
		}
		
		$this->data->andWhere('r.groupdbid IN ?', array($this->groupids_list));
	}
	
	if($this->stat_team != '')
	{
	  $query_filters .= ' AND r.teamdbid = :stat_team';
	  $fieldvalues['stat_team'] = $this->stat_team;
	}
	
	if($this->stat_site != '')
	{
	  $query_filters .= ' AND r.sitedbid = :stat_site';
	  $fieldvalues['stat_site'] = $this->stat_site;
	}
	
	$query_groupby = ' group by r.'.$this->stateltscolumn.', r.covereddate, r.agentstatus, r.notreadyreason';
	$query_orderby = ' order by r.'.$this->stateltscolumn.', r.covereddate, r.agentstatus, r.notreadyreason';
	
	$query = $query_fields.''.$query_tables.''.$query_filters.''.$query_groupby.''.$query_orderby;
	if(!$this->csv_export)
		$query .= ' LIMIT '.(($this->getRequestParameter('page',1)-1)*sfConfig::get('app_display_nbrecordperpagereports')).','.sfConfig::get('app_display_nbrecordperpagereports');
	
	$query_count = $query_count_fields.''.$query_count_tables.''.$query_filters.''.$query_groupby;
	
	
	$query = $pdo->prepare($query);
	$query_count = $pdo->prepare($query_count);
	
	if(!$this->csv_export)
	{
	  $query_count->execute($fieldvalues);
	  $countdata = $query_count->fetchAll();
	  
	  $nbtotalrecordsfounded = sizeof($countdata); //[0]['nbtotalrecords'];
	  
	  $this->pager = new FonivaPager($this->tableclassname_doctrine, sfConfig::get('app_display_nbrecordperpagereports'));
	  $this->pager->setPage($this->getRequestParameter('page',1));
	  $this->pager->assignNbResults($nbtotalrecordsfounded);
	  $this->pager->init();
	  
		  
	  $query->execute($fieldvalues);
	  $this->data = $query->fetchAll();
	}
	else
	{
		  
	  $query->execute($fieldvalues);
	  $this->data = $query->fetchAll();
	}
	
	
	if($this->csv_export)
	{
		$csvfilename = 'foniva_report_' . $this->getActionName() . '.csv';
		$this->setlayout(false);
		$this->getResponse()->clearHttpHeaders();
		$this->getResponse()->setHttpHeader('Content-Type', 'application/vnd.ms-excel');
		$this->getResponse()->setHttpHeader('Content-Disposition', 'attachment; filename='.$csvfilename);
		return 'Csvexport';
	}
	else
	{  
	    $this->getResponse()->addJavascript('jquery.toggleswitch.min.js', 'last');
	    $this->getResponse()->addStylesheet('toggleswitch.css', 'last');		
	}
  }
  
  public function executeAgentstatusdetailed(sfWebRequest $request)
  {
	$this->reportcategory = 'AGENT';
	$this->agentformparse($request);
	$this->getreports('AGENT');
	$this->buildagentreportform($request);
	
	  
	$this->tableclassname = 'log_agent_event';
	$this->tableclassname_doctrine = 'LogAgentEvent';
	
	$this->stateltscolumn = 'agentdbid';
	if($this->stat_aggregate == 'group')
		$this->stateltscolumn = 'groupdbid';
	if($this->stat_aggregate == 'site')
		$this->stateltscolumn = 'sitedbid';
		
	$fieldvalues = array();
	$pdo = Doctrine_Manager::getInstance()->getCurrentConnection()->getDbh();
	
	$query = '';
	$query_count = '';
	
	$query_fields = 'SELECT r.agentdbid, r.groupdbid, r.started_at as started_at, 
								r.agentstatus as agentstatus, r.notreadyreason, r.duration as duration, round(r.activitycost,2) as activitycost,
								ca.username as agentusername,
								cai.firstname as agentfirstname, cai.lastname as agentlastname,
								cg.name as agentgroupname, si.name as agentsitename
								';
								
	$query_count_fields = 'SELECT COUNT(*) as nbtotalrecords 
								';
	
	$query_tables = '
					FROM '.$this->tableclassname.' r
					LEFT JOIN conf_agent ca ON ca.dbid = r.agentdbid
					LEFT JOIN conf_agent_info cai ON ca.info_fk = cai.dbid
					LEFT JOIN conf_group cg ON cg.dbid = r.groupdbid
					LEFT JOIN conf_site si ON si.dbid = r.sitedbid
					';
					
	$query_count_tables = '
					FROM '.$this->tableclassname.' r
					LEFT JOIN conf_agent ca ON ca.dbid = r.agentdbid
					';
	
	$query_filters = '
				 WHERE 
				(r.started_at >= :stat_startdate_ext and r.started_at <= :stat_enddate_ext )  
				';
	
	$fieldvalues['stat_startdate_ext'] = $this->stat_startdate_ext;
	$fieldvalues['stat_enddate_ext'] = $this->stat_enddate_ext;		
	
	if(!fonivaFilter::isCurrentUserSuperTenant())
	{
	  $query_filters .= ' AND r.tenant_fk = :tenantdbid ';
	  $fieldvalues['tenantdbid'] = fonivaFilter::getCurrentUserTenantId();
	}
	
	if($this->stat_agent == 'USERFOLLOWEDAGENTS')
	{
	  $query_filters .= ' AND r.agentdbid IN (SELECT agent_fk FROM conf_user_followedagent WHERE user_fk = :currentuserdbid)';
	  $fieldvalues['currentuserdbid'] = fonivaFilter::getCurrentUser()->getDbid();
	} 
	else if($this->stat_agent != '')
	{
	  $query_filters .= ' AND r.agentdbid = :stat_agent';
	  $fieldvalues['stat_agent'] = $this->stat_agent;
	}
	else if($this->stat_agent == '')
	{
		if(!$this->include_inactive_agents)
	  		$query_filters .= ' AND ca.active = "yes" ';
	}
	
	if($this->stat_group != '')
	{
	  $query_filters .= ' AND r.groupdbid = :stat_group';
	  $fieldvalues['stat_group'] = $this->stat_group;
	}
	
	// filter by allowed groups
	if(!fonivaFilter::canCurrentUserManageAllGroups())
	{
		$this->groups = fonivaFilter::currentUserManagedGroups();
		$this->groups_list = array();
		$this->groupids_list = array();
		$allowed = false;
		foreach($this->groups as $g)
		{
			$this->groups_list[$g->getDbid()] = $g;
			array_push($this->groupids_list, $g->getDbid());
		}
		
		$inQuery = implode(',', $this->groupids_list);
        $query_filters .= ' AND r.groupdbid IN ('.$inQuery.')';
	}
	
	if($this->stat_team != '')
	{
	  $query_filters .= ' AND r.teamdbid = :stat_team';
	  $fieldvalues['stat_team'] = $this->stat_team;
	}
	
	if($this->stat_site != '')
	{
	  $query_filters .= ' AND r.sitedbid = :stat_site';
	  $fieldvalues['stat_site'] = $this->stat_site;
	}
	
	$query_groupby = '  ';
	$query_orderby = ' ORDER BY ca.username, r.started_at';
	
	$query = $query_fields.''.$query_tables.''.$query_filters.''.$query_groupby.''.$query_orderby;
	if(!$this->csv_export)
		$query .= ' LIMIT '.(($this->getRequestParameter('page',1)-1)*sfConfig::get('app_display_nbrecordperpagereports')).','.sfConfig::get('app_display_nbrecordperpagereports');
	
	$query_count = $query_count_fields.''.$query_count_tables.''.$query_filters; //.''.$query_groupby;
	
		
	$query = $pdo->prepare($query);
	$query_count = $pdo->prepare($query_count);
	
	if(!$this->csv_export)
	{
	  $query_count->execute($fieldvalues);
	  $countdata = $query_count->fetchAll();
	  
	  $nbtotalrecordsfounded = $countdata[0]['nbtotalrecords']; //sizeof($countdata); //[0]['nbtotalrecords'];
	  
	  $this->pager = new FonivaPager($this->tableclassname_doctrine, sfConfig::get('app_display_nbrecordperpagereports'));
	  $this->pager->setPage($this->getRequestParameter('page',1));
	  $this->pager->assignNbResults($nbtotalrecordsfounded);
	  $this->pager->init();
	  
		  
	  $query->execute($fieldvalues);
	  $this->data = $query->fetchAll();
	}
	else
	{
		  
	  $query->execute($fieldvalues);
	  $this->data = $query->fetchAll();
	}
	
	
	if($this->csv_export)
	{
		$csvfilename = 'foniva_report_' . $this->getActionName() . '.csv';
		$this->setlayout(false);
		$this->getResponse()->clearHttpHeaders();
		$this->getResponse()->setHttpHeader('Content-Type', 'application/vnd.ms-excel');
		$this->getResponse()->setHttpHeader('Content-Disposition', 'attachment; filename='.$csvfilename);
		return 'Csvexport';
	}
	else
	{  
	    $this->getResponse()->addJavascript('jquery.toggleswitch.min.js', 'last');
	    $this->getResponse()->addStylesheet('toggleswitch.css', 'last');		
	}
  }
  
  public function executeAgentnotreadyreasonstatussummary(sfWebRequest $request)
  {
	$this->reportcategory = 'AGENT';	
	$this->agentformparse($request);
	$this->getreports('AGENT');
	$this->buildagentreportform($request);
	
	  
	$this->tableclassname = 'log_agent_event';
	$this->tableclassname_doctrine = 'LogAgentEvent';
	
	
	$this->stateltscolumn = 'agentdbid';
	if($this->stat_aggregate == 'group')
		$this->stateltscolumn = 'groupdbid';
	if($this->stat_aggregate == 'site')
		$this->stateltscolumn = 'sitedbid';
		
	$fieldvalues = array();
	$pdo = Doctrine_Manager::getInstance()->getCurrentConnection()->getDbh();
	
	$query = '';
	$query_count = '';
	
	$query_fields = 'SELECT r.agentdbid as agentdbid, 
								r.groupdbid as groupdbid, r.sitedbid as sitedbid, r.notreadyreason as notreadyreason, 
								count(r.dbid) as nboccured,							 	
							 	sum(r.duration) as totalduration,
								ca.username as agentusername,
								cai.firstname as agentfirstname, cai.lastname as agentlastname,
								cg.name as agentgroupname								
								
								';
								
	$query_count_fields = 'SELECT COUNT(*) as nbtotalrecords 
								';
								
								
	$query_tables = '
					FROM '.$this->tableclassname.' r
					LEFT JOIN conf_agent ca ON ca.dbid = r.agentdbid
					LEFT JOIN conf_agent_info cai ON ca.info_fk = cai.dbid
					LEFT JOIN conf_group cg ON cg.dbid = r.groupdbid
					
					
					';
					
	$query_count_tables = '
					FROM '.$this->tableclassname.' r
					LEFT JOIN conf_agent ca ON ca.dbid = r.agentdbid
					';
	
	$query_filters = '
				 WHERE 
				(r.started_at >= :stat_startdate_ext and r.started_at <= :stat_enddate_ext ) AND r.agentstatus = "NOT READY" AND r.notreadyreason != "" 
				';
	
	$fieldvalues['stat_startdate_ext'] = $this->stat_startdate_ext;
	$fieldvalues['stat_enddate_ext'] = $this->stat_enddate_ext;		
	
	if(!fonivaFilter::isCurrentUserSuperTenant())
	{
	  $query_filters .= ' AND r.tenant_fk = :tenantdbid';
	  $fieldvalues['tenantdbid'] = fonivaFilter::getCurrentUserTenantId();
	}
	
	if($this->stat_agent == 'USERFOLLOWEDAGENTS')
	{
	  $query_filters .= ' AND r.agentdbid IN (SELECT agent_fk FROM conf_user_followedagent WHERE user_fk = :currentuserdbid)';
	  $fieldvalues['currentuserdbid'] = fonivaFilter::getCurrentUser()->getDbid();
	} 
	else if($this->stat_agent != '')
	{
	  $query_filters .= ' AND r.agentdbid = :stat_agent';
	  $fieldvalues['stat_agent'] = $this->stat_agent;
	}
	else if($this->stat_agent == '')
	{
	  if(!$this->include_inactive_agents)
  	    $query_filters .= ' AND ca.active = "yes" ';
	}
	
	if($this->stat_group != '')
	{
	  $query_filters .= ' AND r.groupdbid = :stat_group';
	  $fieldvalues['stat_group'] = $this->stat_group;
	}
	
	// filter by allowed groups
	if(!fonivaFilter::canCurrentUserManageAllGroups())
	{
		$this->groups = fonivaFilter::currentUserManagedGroups();
		$this->groups_list = array();
		$this->groupids_list = array();
		$allowed = false;
		foreach($this->groups as $g)
		{
			$this->groups_list[$g->getDbid()] = $g;
			array_push($this->groupids_list, $g->getDbid());
		}
		
		$this->data->andWhere('r.groupdbid IN ?', array($this->groupids_list));
	}
	
	if($this->stat_team != '')
	{
	  $query_filters .= ' AND r.teamdbid = :stat_team';
	  $fieldvalues['stat_team'] = $this->stat_team;
	}
	
	if($this->stat_site != '')
	{
	  $query_filters .= ' AND r.sitedbid = :stat_site';
	  $fieldvalues['stat_site'] = $this->stat_site;
	}
	
	$query_groupby = ' group by r.'.$this->stateltscolumn.', r.notreadyreason';
	$query_orderby = ' order by r.'.$this->stateltscolumn.', r.notreadyreason';
	
	$query = $query_fields.''.$query_tables.''.$query_filters.''.$query_groupby.''.$query_orderby;
	if(!$this->csv_export)
		$query .= ' LIMIT '.(($this->getRequestParameter('page',1)-1)*sfConfig::get('app_display_nbrecordperpagereports')).','.sfConfig::get('app_display_nbrecordperpagereports');
	
	$query_count = $query_count_fields.''.$query_count_tables.''.$query_filters.''.$query_groupby;
	
	
	$query = $pdo->prepare($query);
	$query_count = $pdo->prepare($query_count);
	
	if(!$this->csv_export)
	{
	  $query_count->execute($fieldvalues);
	  $countdata = $query_count->fetchAll();
	  
	  $nbtotalrecordsfounded = (sizeof($countdata) <= 0) ? 0 : $countdata[0]['nbtotalrecords']; //sizeof($countdata); //[0]['nbtotalrecords'];
	  
	  $this->pager = new FonivaPager($this->tableclassname_doctrine, sfConfig::get('app_display_nbrecordperpagereports'));
	  $this->pager->setPage($this->getRequestParameter('page',1));
	  $this->pager->assignNbResults($nbtotalrecordsfounded);
	  $this->pager->init();
	  
		  
	  $query->execute($fieldvalues);
	  $this->data = $query->fetchAll();
	}
	else
	{
		  
	  $query->execute($fieldvalues);
	  $this->data = $query->fetchAll();
	}
	
	
	$infos = array();
	$this->eltinfos = array();
	if($this->stat_aggregate == 'agent') //we want to show agent details
	{
		$infos = Doctrine_Query::create()
					->select('a.dbid as dbid, i.firstname as firstname, i.lastname as lastname , a.username as username, t.name as teamname')
					->from('ConfAgent a')
					->leftJoin('a.ConfAgentInfo i ON i.dbid = a.info_fk')
					->leftJoin('a.ConfTeam t ON t.dbid = a.team_fk')
					->where('1');
		if($this->stat_group != '')
			$infos->andWhere('a.group_fk = ?', $this->stat_group);
		if($this->stat_team != '')
			$infos->andWhere('a.team_fk = ?', $this->stat_team);
		if($this->stat_site != '')
			$infos->andWhere('a.site_fk = ?', $this->stat_site);
		
		if($this->stat_agent == 'USERFOLLOWEDAGENTS')
	    {
		  $subq = Doctrine_Query::create()
		  				->select('fa.agent_fk')
		  				->from('ConfUserFollowedagent fa')
						->where('fa.user_fk = "'.fonivaFilter::getCurrentUser()->getDbid().'"');
		  $infos->andWhere('a.dbid IN ('.$subq->getDql().')');
	    }
	    else if($this->stat_agent != '')
			$infos->andWhere('a.dbid = ?', $this->stat_agent);
			
	    if(!fonivaFilter::isCurrentUserSuperTenant())
		  $infos->andWhere('a.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	    $infos = $infos->execute();
		foreach($infos as $i)
			$this->eltinfos[$i->dbid] = $i;
	}
	else if($this->stat_aggregate == 'group')
	{
		$infos = Doctrine_Query::create()
					->select('a.dbid as dbid, a.name as name')
					->from('ConfGroup a');
		if($this->stat_group != '')
			$infos->andWhere('a.dbid = ?', $this->stat_group);
	    if(!fonivaFilter::isCurrentUserSuperTenant())
		  $infos->andWhere('a.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	    $infos = $infos->execute();
		
		foreach($infos as $i)
			$this->eltinfos[$i->dbid] = $i;
	}
	else if($this->stat_aggregate == 'team')
	{
		$infos = Doctrine_Query::create()
					->select('a.dbid as dbid, a.name as name')
					->from('ConfTeam a');
		if($this->stat_team != '')
			$infos->andWhere('a.dbid = ?', $this->stat_team);
	    if(!fonivaFilter::isCurrentUserSuperTenant())
		  $infos->andWhere('a.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	    $infos = $infos->execute();
		
		foreach($infos as $i)
			$this->eltinfos[$i->dbid] = $i;
	}
	else if($this->stat_aggregate == 'site')
	{
		$infos = Doctrine_Query::create()
					->select('a.dbid as dbid, a.name as name')
					->from('ConfSite a');
		if($this->stat_site != '')
			$infos->andWhere('a.dbid = ?', $this->stat_site);
	    if(!fonivaFilter::isCurrentUserSuperTenant())
		  $infos->andWhere('a.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	    $infos = $infos->execute();
		
		foreach($infos as $i)
			$this->eltinfos[$i->dbid] = $i;
	}
	
	if($this->csv_export)
	{
		$csvfilename = 'foniva_report_' . $this->getActionName() . '.csv';
		$this->setlayout(false);
		$this->getResponse()->clearHttpHeaders();
		$this->getResponse()->setHttpHeader('Content-Type', 'application/vnd.ms-excel');
		$this->getResponse()->setHttpHeader('Content-Disposition', 'attachment; filename='.$csvfilename);
		return 'Csvexport';
	}
	else
	{  
	    $this->getResponse()->addJavascript('jquery.toggleswitch.min.js', 'last');
	    $this->getResponse()->addStylesheet('toggleswitch.css', 'last');		
	}
  }
  
  
  public function executeAgentcalls(sfWebRequest $request)
  {
	$this->reportcategory = 'AGENT';
	$this->agentformparse($request);
	$this->getreports('AGENT');
	$this->buildagentreportform($request);
	
	$this->tableclassname = 'LogResumeDailyAgentCall';
	if($this->stat_step == 'hourly')
		$this->tableclassname = 'LogResumeHourlyAgentCall';
	if($this->stat_step == 'monthly')
		$this->tableclassname = 'LogResumeMonthlyAgentCall';
	if($this->stat_step == 'weekly')
		$this->tableclassname = 'LogResumeWeeklyAgentCall';
	if($this->stat_step == '15min')
		$this->tableclassname = 'LogResume15minAgentCall';
	if($this->stat_step == '30min')
		$this->tableclassname = 'LogResume30minAgentCall';
		
	$this->stateltscolumn = 'agentdbid';
	if($this->stat_aggregate == 'group')
		$this->stateltscolumn = 'groupdbid';
	if($this->stat_aggregate == 'site')
		$this->stateltscolumn = 'sitedbid';
	
	$this->data = Doctrine_Query::create()
					->select('r.covereddate as covereddate, r.'.$this->stateltscolumn.' as '.$this->stateltscolumn.', r.callresult as callresult, 
							 sum(r.totalinbounds) as data_totalinbounds, sum(r.totaloutbounds) as data_totaloutbounds, 
							 sum(r.totalmanuals) as data_totalmanuals, sum(r.totaldialing_seconds) as data_totaldialing_seconds, sum(r.totalanswered_seconds) as data_totalanswered_seconds')					
					->from($this->tableclassname.' r')
					->where('r.covereddate >= ? and r.covereddate <= ? ', array($this->stat_startdate_ext, $this->stat_enddate_ext));
	if($this->stat_group != '')
		$this->data->andWhere('r.groupdbid = ?', $this->stat_group);
	if($this->stat_team != '')
		$this->data->andWhere('r.teamdbid = ?', $this->stat_team);
	if($this->stat_site != '')
		$this->data->andWhere('r.sitedbid = ?', $this->stat_site);
	
	// filter by allowed groups
	if(!fonivaFilter::canCurrentUserManageAllGroups())
	{
		$this->groups = fonivaFilter::currentUserManagedGroups();
		$this->groups_list = array();
		$this->groupids_list = array();
		$allowed = false;
		foreach($this->groups as $g)
		{
			$this->groups_list[$g->getDbid()] = $g;
			array_push($this->groupids_list, $g->getDbid());
		}
		
		$this->data->andWhere('r.groupdbid IN ?', array($this->groupids_list));
	}
	
	
	if($this->stat_agent == 'USERFOLLOWEDAGENTS')
	{
		$subq = Doctrine_Query::create()
		  				->select('fa.agent_fk')
		  				->from('ConfUserFollowedagent fa')
						->where('fa.user_fk = "'.fonivaFilter::getCurrentUser()->getDbid().'"');
		$this->data->andWhere('r.agentdbid IN ('.$subq->getDql().')');
	}
	else if($this->stat_agent != '')
		$this->data->andWhere('r.agentdbid = ?', $this->stat_agent);
		
	//todo : use if(!$this->include_inactive_agents) to add a filter if there is an active agent or not
		
	$this->data->groupBy('r.'.$this->stateltscolumn.', r.covereddate, r.callresult ')
				->orderBy('r.'.$this->stateltscolumn.', r.covereddate, r.callresult ');
				
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$this->data->andWhere('r.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	if(!$this->csv_export)
	{
		//echo $this->data;
		//exit();
		$this->pager = new sfDoctrinePager($this->tableclassname, sfConfig::get('app_display_nbrecordperpagereports'));
		$this->pager->setPage($this->getRequestParameter('page',1));
		$this->pager->setQuery($this->data);
		$this->pager->init();
		$this->data = $this->pager->getResults();
		
		
		/* calculate datasums for graphs */
		if(false)
 		{
		$this->datasum = Doctrine_Query::create()
				->select('r.callresult as callresult, 
						 sum(r.totalinbounds) as data_totalinbounds,
						 sum(r.totaloutbounds) as data_totaloutbounds, 
						 sum(r.totalmanuals) as data_totalmanuals, 
						 sum(r.totaldialing_seconds) as data_totaldialing_seconds, 
						 sum(r.totalanswered_seconds) as data_totalanswered_seconds')					
				->from($this->tableclassname.' r')
				->where('r.covereddate >= ? and r.covereddate <= ? ', array($this->stat_startdate_ext, $this->stat_enddate_ext));
		if($this->stat_group != '')
			$this->datasum->andWhere('r.groupdbid = ?', $this->stat_group);
		if($this->stat_team != '')
			$this->datasum->andWhere('r.teamdbid = ?', $this->stat_team);
		if($this->stat_site != '')
			$this->datasum->andWhere('r.sitedbid = ?', $this->stat_site);
		
		
		// filter by allowed groups
		if(!fonivaFilter::canCurrentUserManageAllGroups())
		{
			$this->groups = fonivaFilter::currentUserManagedGroups();
			$this->groups_list = array();
			$this->groupids_list = array();
			$allowed = false;
			foreach($this->groups as $g)
			{
				$this->groups_list[$g->getDbid()] = $g;
				array_push($this->groupids_list, $g->getDbid());
			}
			
			$this->data->andWhere('r.groupdbid IN ?', array($this->groupids_list));
		}
		
		
		if($this->stat_agent == 'USERFOLLOWEDAGENTS')
	    {
		  $subq = Doctrine_Query::create()
		  				->select('fa.agent_fk')
		  				->from('ConfUserFollowedagent fa')
						->where('fa.user_fk = "'.fonivaFilter::getCurrentUser()->getDbid().'"');
		  $this->datasum->andWhere('r.agentdbid IN ('.$subq->getDql().')');
	    }
	    else if($this->stat_agent != '')
			$this->datasum->andWhere('r.agentdbid = ?', $this->stat_agent);
			
		$this->datasum->groupBy('r.callresult ')
					->orderBy('r.callresult ');
					
		if(!fonivaFilter::isCurrentUserSuperTenant())
			$this->datasum->andWhere('r.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
			
		$this->datasum = $this->datasum->execute();
		}
	}
	else
	{
		$this->data = $this->data->execute();
	}
	
	$infos = array();
	$this->eltinfos = array();
	if($this->stat_aggregate == 'agent') //we want to show agent details
	{
		$infos = Doctrine_Query::create()
					->select('a.dbid as dbid, i.firstname as firstname, i.lastname as lastname , a.username as username')
					->from('ConfAgent a')
					->leftJoin('a.ConfAgentInfo i ON i.dbid = a.info_fk')
					->where('1');
		if($this->stat_group != '')
			$infos->andWhere('a.group_fk = ?', $this->stat_group);
		if($this->stat_team != '')
			$infos->andWhere('a.team_fk = ?', $this->stat_team);
		if($this->stat_site != '')
			$infos->andWhere('a.site_fk = ?', $this->stat_site);
		
		if($this->stat_agent == 'USERFOLLOWEDAGENTS')
	    {
		  $subq = Doctrine_Query::create()
		  				->select('fa.agent_fk')
		  				->from('ConfUserFollowedagent fa')
						->where('fa.user_fk = "'.fonivaFilter::getCurrentUser()->getDbid().'"');
		  $infos->andWhere('a.dbid IN ('.$subq->getDql().')');
	    }
	    else if($this->stat_agent != '')
			$infos->andWhere('a.dbid = ?', $this->stat_agent);
			
	    if(!fonivaFilter::isCurrentUserSuperTenant())
		  $infos->andWhere('a.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	    $infos = $infos->execute();
		foreach($infos as $i)
			$this->eltinfos[$i->dbid] = $i;
	}
	else if($this->stat_aggregate == 'group')
	{
		$infos = Doctrine_Query::create()
					->select('a.dbid as dbid, a.name as name')
					->from('ConfGroup a');
		if($this->stat_group != '')
			$infos->andWhere('a.dbid = ?', $this->stat_group);
	    if(!fonivaFilter::isCurrentUserSuperTenant())
		  $infos->andWhere('a.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	    $infos = $infos->execute();
		
		foreach($infos as $i)
			$this->eltinfos[$i->dbid] = $i;
	}
	else if($this->stat_aggregate == 'team')
	{
		$infos = Doctrine_Query::create()
					->select('a.dbid as dbid, a.name as name')
					->from('ConfTeam a');
		if($this->stat_team != '')
			$infos->andWhere('a.dbid = ?', $this->stat_team);
	    if(!fonivaFilter::isCurrentUserSuperTenant())
		  $infos->andWhere('a.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	    $infos = $infos->execute();
		
		foreach($infos as $i)
			$this->eltinfos[$i->dbid] = $i;
	}
	else if($this->stat_aggregate == 'site')
	{
		$infos = Doctrine_Query::create()
					->select('a.dbid as dbid, a.name as name')
					->from('ConfSite a');
		if($this->stat_site != '')
			$infos->andWhere('a.dbid = ?', $this->stat_site);
	    if(!fonivaFilter::isCurrentUserSuperTenant())
		  $infos->andWhere('a.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	    $infos = $infos->execute();
		
		foreach($infos as $i)
			$this->eltinfos[$i->dbid] = $i;
	}
	
	
	if($this->csv_export)
	{
		$csvfilename = 'foniva_report_' . $this->getActionName() . '.csv';
		$this->setlayout(false);
		$this->getResponse()->clearHttpHeaders();
		$this->getResponse()->setHttpHeader('Content-Type', 'application/vnd.ms-excel');
		$this->getResponse()->setHttpHeader('Content-Disposition', 'attachment; filename='.$csvfilename);
		return 'Csvexport'; /* use csv export template instead*/
	}
	else
	{		
		$this->getResponse()->addJavascript('simple.carousel.js', 'last');
		$this->getResponse()->addStylesheet('simple.carousel.css', 'last');
		$this->getResponse()->addJavascript('jquery.toggleswitch.min.js', 'last');
		$this->getResponse()->addStylesheet('toggleswitch.css', 'last');	
	}
  }
  
  public function executeAgentcalldispositions(sfWebRequest $request)
  {
	$this->reportcategory = 'AGENT';
	$this->agentformparse($request);
	$this->getreports('AGENT');
	$this->buildagentreportform($request);
	
	$this->tableclassname = 'LogResumeDailyAgentCall';
	if($this->stat_step == 'hourly')
	{
		$this->query_groupby_dateformat = ' DATE_FORMAT(r.occured_at, "%Y-%m-%d %H:00:00") ';
	}
	if($this->stat_step == 'monthly')
	{
		$this->query_groupby_dateformat = ' DATE_FORMAT(r.occured_at, "%Y-%m-01 00:00:00") ';
	}
	if($this->stat_step == 'daily')
	{
		$this->query_groupby_dateformat = ' DATE_FORMAT(r.occured_at, "%Y-%m-%d 00:00:00") ';
	}
	if($this->stat_step == 'weekly')
	{
		$this->query_groupby_dateformat = ' DATE_FORMAT(r.occured_at, "%Y-%u 00:00:00") ';
	}
	if($this->stat_step == '15min')
	{
		$this->query_groupby_dateformat = ' DATE_FORMAT(r.occured_at, "%Y-%m-%d %H:00:00") ';
	}
	if($this->stat_step == '30min')
	{
		$this->query_groupby_dateformat = ' DATE_FORMAT(r.occured_at, "%Y-%m-%d %H:00:00") ';
	}

		
	$this->stateltscolumn = 'agentdbid';
	if($this->stat_aggregate == 'group')
		$this->stateltscolumn = 'groupdbid';
	if($this->stat_aggregate == 'site')
		$this->stateltscolumn = 'sitedbid';
	
	$this->data = Doctrine_Query::create()
					->select($this->query_groupby_dateformat.' as occured_at, count(r.dbid) as nbelements,
							  r.calldisposition_id, r.calldisposition_name
							  ')					
					->from('LogCallDisposition r')
					->where('r.occured_at >= ? and r.occured_at <= ? ', array($this->stat_startdate_ext, $this->stat_enddate_ext));
	if($this->stat_group != '')
		$this->data->andWhere('r.groupdbid = ?', $this->stat_group);
	if($this->stat_team != '')
		$this->data->andWhere('r.teamdbid = ?', $this->stat_team);
	if($this->stat_site != '')
		$this->data->andWhere('r.sitedbid = ?', $this->stat_site);
	
	
	// filter by allowed groups
	if(!fonivaFilter::canCurrentUserManageAllGroups())
	{
		$this->groups = fonivaFilter::currentUserManagedGroups();
		$this->groups_list = array();
		$this->groupids_list = array();
		$allowed = false;
		foreach($this->groups as $g)
		{
			$this->groups_list[$g->getDbid()] = $g;
			array_push($this->groupids_list, $g->getDbid());
		}
		
		$this->data->andWhere('r.groupdbid IN ?', array($this->groupids_list));
	}
	
	if($this->stat_agent == 'USERFOLLOWEDAGENTS')
	{
		$subq = Doctrine_Query::create()
		  				->select('fa.agent_fk')
		  				->from('ConfUserFollowedagent fa')
						->where('fa.user_fk = "'.fonivaFilter::getCurrentUser()->getDbid().'"');
		$this->data->andWhere('r.agentdbid IN ('.$subq->getDql().')');
	}
	else if($this->stat_agent != '')
		$this->data->andWhere('r.agentdbid = ?', $this->stat_agent);
		
	$this->data->groupBy($this->query_groupby_dateformat.', r.calldisposition_id ')
				->orderBy($this->query_groupby_dateformat.', r.calldisposition_id ');
				
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$this->data->andWhere('r.tenantdbid = ?', fonivaFilter::getCurrentUserTenantId());
	if(!$this->csv_export)
	{
		$this->pager = new sfDoctrinePager($this->tableclassname, sfConfig::get('app_display_nbrecordperpagereports'));
		$this->pager->setPage($this->getRequestParameter('page',1));
		$this->pager->setQuery($this->data);
		$this->pager->init();
		$this->data = $this->pager->getResults();
	}
	else
	{
		$this->data = $this->data->execute();
	}
	
	$infos = array();
	$this->eltinfos = array();
	if($this->stat_aggregate == 'agent') //we want to show agent details
	{
		$infos = Doctrine_Query::create()
					->select('a.dbid as dbid, i.firstname as firstname, i.lastname as lastname , a.username as username')
					->from('ConfAgent a')
					->leftJoin('a.ConfAgentInfo i ON i.dbid = a.info_fk')
					->where('1');
		if($this->stat_group != '')
			$infos->andWhere('a.group_fk = ?', $this->stat_group);
		if($this->stat_team != '')
			$infos->andWhere('a.team_fk = ?', $this->stat_team);
		if($this->stat_site != '')
			$infos->andWhere('a.site_fk = ?', $this->stat_site);
		
		if($this->stat_agent == 'USERFOLLOWEDAGENTS')
	    {
		  $subq = Doctrine_Query::create()
		  				->select('fa.agent_fk')
		  				->from('ConfUserFollowedagent fa')
						->where('fa.user_fk = "'.fonivaFilter::getCurrentUser()->getDbid().'"');
		  $infos->andWhere('a.dbid IN ('.$subq->getDql().')');
	    }
	    else if($this->stat_agent != '')
			$infos->andWhere('a.dbid = ?', $this->stat_agent);
			
	    if(!fonivaFilter::isCurrentUserSuperTenant())
		  $infos->andWhere('a.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	    $infos = $infos->execute();
		foreach($infos as $i)
			$this->eltinfos[$i->dbid] = $i;
	}
	else if($this->stat_aggregate == 'group')
	{
		$infos = Doctrine_Query::create()
					->select('a.dbid as dbid, a.name as name')
					->from('ConfGroup a');
		if($this->stat_group != '')
			$infos->andWhere('a.dbid = ?', $this->stat_group);
	    if(!fonivaFilter::isCurrentUserSuperTenant())
		  $infos->andWhere('a.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	    $infos = $infos->execute();
		
		foreach($infos as $i)
			$this->eltinfos[$i->dbid] = $i;
	}
	else if($this->stat_aggregate == 'team')
	{
		$infos = Doctrine_Query::create()
					->select('a.dbid as dbid, a.name as name')
					->from('ConfTeam a');
		if($this->stat_team != '')
			$infos->andWhere('a.dbid = ?', $this->stat_team);
	    if(!fonivaFilter::isCurrentUserSuperTenant())
		  $infos->andWhere('a.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	    $infos = $infos->execute();
		
		foreach($infos as $i)
			$this->eltinfos[$i->dbid] = $i;
	}
	else if($this->stat_aggregate == 'site')
	{
		$infos = Doctrine_Query::create()
					->select('a.dbid as dbid, a.name as name')
					->from('ConfSite a');
		if($this->stat_site != '')
			$infos->andWhere('a.dbid = ?', $this->stat_site);
	    if(!fonivaFilter::isCurrentUserSuperTenant())
		  $infos->andWhere('a.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	    $infos = $infos->execute();
		
		foreach($infos as $i)
			$this->eltinfos[$i->dbid] = $i;
	}
	
	
	if($this->csv_export)
	{
		$csvfilename = 'foniva_report_' . $this->getActionName() . '.csv';
		$this->setlayout(false);
		$this->getResponse()->clearHttpHeaders();
		$this->getResponse()->setHttpHeader('Content-Type', 'application/vnd.ms-excel');
		$this->getResponse()->setHttpHeader('Content-Disposition', 'attachment; filename='.$csvfilename);
		return 'Csvexport'; /* use csv export template instead*/
	}
	else
	{		
		$this->getResponse()->addJavascript('simple.carousel.js', 'last');
		$this->getResponse()->addStylesheet('simple.carousel.css', 'last');
		$this->getResponse()->addJavascript('jquery.toggleswitch.min.js', 'last');
		$this->getResponse()->addStylesheet('toggleswitch.css', 'last');	
	}
  }
  
  public function executeAgentcalldetails(sfWebRequest $request)
  {
	  $this->reportcategory = 'AGENT';
	  $this->agentformparse($request);
	  $this->getreports('AGENT');
	  $this->buildagentreportform($request);
	  
	  
  	  $fieldvalues = array();
	  $pdo = Doctrine_Manager::getInstance()->getCurrentConnection()->getDbh();
	  
	  $query = '';
	  $query_count = '';
	  
	  $query_fields = 'SELECT la.tenant_fk, la.tracknumber,
						la.callername, la.callernumber, la.callednum,
						la.is_inbound, la.is_manual, la.campaign_id,
						la.dialplan_id, la.queue_id, la.agentdialing_seconds,
						la.agentanswered_seconds, la.agentdbid, la.groupdbid, la.sitedbid,
						la.callreceived_at, la.callresult,
						ca.username as agentusername, 
						cai.firstname as agentfirstname, cai.lastname as agentlastname,
						cg.name as agentgroupname,
						si.name as agentsitename,
						cinb.inbound_name as inbound_name,
						coutb.campaign_name as outbound_name,
						cdia.dialplan as dialplanname,
						cque.name as queuename,
						lacallxfer.transferedto as xfer_transferedto,
						lacallhang.tracknumber as callhangup_tracknumber,
						cdisp.calldisposition_name as cdisp_calldisposition_name ';
						
	  $query_count_fields = 'SELECT COUNT(*) as nbtotalrecords';
		
		
	   $queue_tables =  '				
						FROM log_agent_call la
						LEFT JOIN conf_agent ca ON la.agentdbid = ca.dbid
						LEFT JOIN conf_agent_info cai ON ca.info_fk = cai.dbid
						LEFT JOIN conf_group cg ON la.groupdbid = cg.dbid
						LEFT JOIN conf_site si ON la.sitedbid = si.dbid
						LEFT JOIN conf_inbound cinb ON cinb.dbid = la.campaign_id
						LEFT JOIN conf_campaign coutb ON coutb.dbid = la.campaign_id
						LEFT JOIN conf_queue cque ON cque.dbid = la.queue_id
						LEFT JOIN conf_dialplan cdia ON cdia.dbid = la.dialplan_id
						LEFT JOIN log_agent_calltransfer lacallxfer ON la.tracknumber = lacallxfer.tracknumber
						LEFT JOIN log_agent_callagenthangup lacallhang ON la.tracknumber = lacallhang.tracknumber
						LEFT JOIN log_call_disposition cdisp ON la.tracknumber = cdisp.tracknumber
						';
		$query_count_tables = '
						FROM log_agent_call la
						LEFT JOIN conf_agent ca ON la.agentdbid = ca.dbid
						LEFT JOIN log_agent_callagenthangup lacallhang ON la.tracknumber = lacallhang.tracknumber
						';
						
		$query_filters = '
						 WHERE 
						(la.callreceived_at >= :stat_startdate_ext and la.callreceived_at <= :stat_enddate_ext )
						';
	  	
						
	  $fieldvalues['stat_startdate_ext'] = $this->stat_startdate_ext;
	  $fieldvalues['stat_enddate_ext'] = $this->stat_enddate_ext;
	  if(!fonivaFilter::isCurrentUserSuperTenant())
	  {
		  $query_filters .= ' AND la.tenant_fk = :tenantdbid';
		  $fieldvalues['tenantdbid'] = fonivaFilter::getCurrentUserTenantId();
	  }
	
	  if($this->stat_agent == 'USERFOLLOWEDAGENTS')
	  {
	     $query_filters .= ' AND la.agentdbid IN (SELECT agent_fk FROM conf_user_followedagent WHERE user_fk = :currentuserdbid)';
	     $fieldvalues['currentuserdbid'] = fonivaFilter::getCurrentUser()->getDbid();
	  } 
	  else if($this->stat_agent != '')
	  {
		  $query_filters .= ' AND la.agentdbid = :stat_agent';
		  $fieldvalues['stat_agent'] = $this->stat_agent;
	  }
	  else if($this->stat_agent == '')
	  {
		  if(!$this->include_inactive_agents)
	      	$query_filters .= ' AND ca.active = "yes" ';
	  }
		
	  if($this->stat_group != '')
	  {
		  $query_filters .= ' AND la.groupdbid = :stat_group';
		  $fieldvalues['stat_group'] = $this->stat_group;
	  }
	  	
	  // filter by allowed groups
	  if(!fonivaFilter::canCurrentUserManageAllGroups())
	  {
		  $this->groups = fonivaFilter::currentUserManagedGroups();
		  $this->groups_list = array();
		  $this->groupids_list = array();
		  $allowed = false;
		  foreach($this->groups as $g)
		  {
			  $this->groups_list[$g->getDbid()] = $g;
			  array_push($this->groupids_list, $g->getDbid());
		  }
		
	  	  if(sizeof($this->groupids_list) > 0)
		  {
			  $inQuery = implode(',', $this->groupids_list);
			  $query_filters .= ' AND la.groupdbid IN ('.$inQuery.')';
			 
		  }
	  }

		
	  if($this->stat_team != '')
	  {
		  $query_filters .= ' AND la.teamdbid = :stat_team';
		  $fieldvalues['stat_team'] = $this->stat_team;
	  }
		
	  if($this->stat_site != '')
	  {
		  $query_filters .= ' AND la.sitedbid = :stat_site';
		  $fieldvalues['stat_site'] = $this->stat_site;
	  }
	  
	  if($this->stat_searchcalleridnum != null && $this->stat_searchcalleridnum != '')
	  {
		  $query_filters .= ' AND la.callernumber = :searchcalleridnum';
		  $fieldvalues['searchcalleridnum'] = $this->stat_searchcalleridnum;
	  }
	  
	  if($this->stat_searchcallednum != null && $this->stat_searchcallednum != '')
	  {
		  $query_filters .= ' AND la.callednum = :searchcallednum';
		  $fieldvalues['searchcallednum'] = $this->stat_searchcallednum;
	  }
	  
	  if($this->stat_searchtracknumber != null && $this->stat_searchtracknumber != '')
	  {
		  $query_filters .= ' AND la.tracknumber = :searchtracknumber';
		  $fieldvalues['searchtracknumber'] = $this->stat_searchtracknumber;
	  }
	  
	  if($this->stat_searchagenthangeduponly != null && $this->stat_searchagenthangeduponly == 'on')
	  {
		  $query_filters .= ' AND lacallhang.tracknumber IS NOT NULL ';
		 // $fieldvalues['searchtracknumber'] = $this->stat_searchagenthangeduponly;
	  }
	  
	  $query_groupby = ' GROUP BY la.tracknumber ';
	  
	  $query_orderby = ' ORDER BY la.callreceived_at, ca.username ';
	  
	  
	  $query = $query_fields.''.$queue_tables.''.$query_filters.''.$query_groupby.''.$query_orderby;
	  if(!$this->csv_export)
	  	$query .= ' LIMIT '.(($this->getRequestParameter('page',1)-1)*sfConfig::get('app_display_nbrecordperpagereports')).','.sfConfig::get('app_display_nbrecordperpagereports');
	  
	  $query_count = $query_count_fields.''.$query_count_tables.''.$query_filters;

	  $query = $pdo->prepare($query);
	  $query_count = $pdo->prepare($query_count);
	  
	  if(!$this->csv_export)
	  {
		  $query_count->execute($fieldvalues);
		  $countdata = $query_count->fetchAll();
		  $nbtotalrecordsfounded = $countdata[0]['nbtotalrecords'];
		  
		  $this->pager = new FonivaPager('LogAgentCall', sfConfig::get('app_display_nbrecordperpagereports'));
		  $this->pager->setPage($this->getRequestParameter('page',1));
		  $this->pager->assignNbResults($nbtotalrecordsfounded);
		  $this->pager->init();
		  
			  
		  $query->execute($fieldvalues);
		  $this->data = $query->fetchAll();
	  }
	  else
	  {
			  
		  $query->execute($fieldvalues);
		  $this->data = $query->fetchAll();
	  }
		  
	  
	  if($this->csv_export)
	  {
	    $csvfilename = 'foniva_report_' . $this->getActionName() . '.csv';
		$this->setlayout(false);
		$this->getResponse()->clearHttpHeaders();
		$this->getResponse()->setHttpHeader('Content-Type', 'application/vnd.ms-excel');
		$this->getResponse()->setHttpHeader('Content-Disposition', 'attachment; filename='.$csvfilename);
		return 'Csvexport';
	  }	
	  else
	  {		
		$this->getResponse()->addJavascript('simple.carousel.js', 'last');
		$this->getResponse()->addStylesheet('simple.carousel.css', 'last');	
	    $this->getResponse()->addJavascript('jquery.toggleswitch.min.js', 'last');
	    $this->getResponse()->addStylesheet('toggleswitch.css', 'last');
		$this->getResponse()->addJavascript('jplayer/jPlayer-2.9.2/jplayer/jquery.jplayer.min.js', 'last');
		$this->getResponse()->addStylesheet('/js/jplayer/jPlayer-2.9.2/skin/blue.monday/css/jplayer.blue.monday.css', 'last');
	  }
	  
  }
  
  public function executeAgentoccupancy(sfWebRequest $request)
  {
	  $this->reportcategory = 'AGENT';
	  $this->agentformparse($request);
	  $this->getreports('AGENT');
	  $this->buildagentreportform($request);
	  
	    
	  $this->tableclassname = 'log_agent_event';
	  $this->tableclassname_doctrine = 'LogAgentEvent';
	  
	  
	  if($this->stat_step == 'hourly')
	  {
		$this->query_groupby_dateformat = ' DATE_FORMAT(la.covereddate, "%Y-%m-%d %H:00:00") ';
		  $this->tableclassname = 'log_resume_hourly_agent_event';
		  $this->tableclassname_doctrine = 'LogResumeHourlyAgentEvent';
	  }
	  if($this->stat_step == 'monthly')
	  {
		$this->query_groupby_dateformat = ' DATE_FORMAT(la.covereddate, "%Y-%m-01 00:00:00") ';
		  $this->tableclassname = 'log_resume_monthly_agent_event';
		  $this->tableclassname_doctrine = 'LogResumeMonthlyAgentEvent';
	  }
	  if($this->stat_step == 'daily')
	  {
		$this->query_groupby_dateformat = ' DATE_FORMAT(la.covereddate, "%Y-%m-%d 00:00:00") ';
		  $this->tableclassname = 'log_resume_daily_agent_event';
		  $this->tableclassname_doctrine = 'LogResumeDailyAgentEvent';
	  }
	  if($this->stat_step == 'weekly')
	  {
		$this->query_groupby_dateformat = ' DATE_FORMAT(la.covereddate, "%Y-%u 00:00:00") ';
		  $this->tableclassname = 'log_resume_weekly_agent_event';
		  $this->tableclassname_doctrine = 'LogResumeWeeklyAgentEvent';
	  }
	  if($this->stat_step == '15min')
	  {
		$this->query_groupby_dateformat = ' DATE_FORMAT(la.covereddate, "%Y-%m-%d %H:00:00") ';
		  $this->tableclassname = 'log_resume_15min_agent_event';
		  $this->tableclassname_doctrine = 'LogResult15minAgentEvent';
	  }
	  if($this->stat_step == '30min')
	  {
		$this->query_groupby_dateformat = ' DATE_FORMAT(la.covereddate, "%Y-%m-%d %H:00:00") ';
		  $this->tableclassname = 'log_resume_30min_agent_event';
		  $this->tableclassname_doctrine = 'LogResume30minAgentEvent';
	  }
	  
	  
	  $nrr_occupied = array();
	  $nrr_excluded = array();
	  
	  $this->stat_activity_occupied = $request->getParameter('report_activity_occupied');
	  if($this->stat_activity_occupied != NULL && is_array($this->stat_activity_occupied))
	  {
		  $this->stat_activity_occupied = array_keys($this->stat_activity_occupied);
		  $oo_activities = Doctrine_Query::create()
								->select('a.dbid, a.name, a.colorcode')
								->from('ConfAgentActivity a')
								->where('a.active = "yes"')
								->andWhereIn('a.dbid', $this->stat_activity_occupied)
								->orderBy('a.name');
		  if(!fonivaFilter::isCurrentUserSuperTenant())
			 $oo_activities->andWhere('a.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
		  $oo_activities = $oo_activities->execute();
		  foreach($oo_activities as $oo)
		  	array_push($nrr_occupied, $oo->name);
	  }
	  
	  $this->stat_activity_excluded = $request->getParameter('report_activity_excluded');
	  if($this->stat_activity_excluded != NULL && is_array($this->stat_activity_excluded))
	  {
		  $this->stat_activity_excluded = array_keys($this->stat_activity_excluded);
		  $oo_activities = Doctrine_Query::create()
								->select('a.dbid, a.name, a.colorcode')
								->from('ConfAgentActivity a')
								->where('a.active = "yes"')
								->andWhereIn('a.dbid', $this->stat_activity_excluded)
								->orderBy('a.name');
		  if(!fonivaFilter::isCurrentUserSuperTenant())
			 $oo_activities->andWhere('a.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
		  $oo_activities = $oo_activities->execute();
		  foreach($oo_activities as $oo)
		  	array_push($nrr_excluded, $oo->name);
	  }
	  
		
	  $this->stateltscolumn = 'agentdbid';
	  if($this->stat_aggregate == 'group')
		  $this->stateltscolumn = 'groupdbid';
	  if($this->stat_aggregate == 'site')
		  $this->stateltscolumn = 'sitedbid';
	  if($this->stat_aggregate == 'team')
		  $this->stateltscolumn = 'teamdbid';
	
  	  $fieldvalues = array();
	  $pdo = Doctrine_Manager::getInstance()->getCurrentConnection()->getDbh();
	  
	  $query = '';
	  $query_count = '';
	  
	  $query_fields = 'SELECT 
	  					la.tenant_fk,
	  					'.$this->query_groupby_dateformat.' as covereddate, ';
						
	  if($this->stat_aggregate == 'agent')
	  {
		  $query_fields .= '
	  					la.agentdbid,
						ca.username as agentusername, 
						cai.firstname as agentfirstname, cai.lastname as agentlastname,
						';
	  }
	  else if($this->stat_aggregate == 'group')
	  {
		  $query_fields .= '
						la.groupdbid, 
						cg.name as agentgroupname,
						';
	  }
	  else if($this->stat_aggregate == 'site')
	  {
		  $query_fields .= '
						la.sitedbid, 
						si.name as agentsitename,
						';
	  }
	  else if($this->stat_aggregate == 'team')
	  {
		  $query_fields .= '
						la.teamdbid, 
						te.name as agentteamname,
						';
	  }
	  
	  
	  $query_subfields_occupied = '';
	  $nrr_occupied_sql = '""';
	  if(sizeof($nrr_occupied) > 0)
	  {
		$nrr_occupied_sql = '"' . implode('","', $nrr_occupied) . '"'; 
	  	$query_subfields_occupied = ' OR (la.agentstatus = "NOT READY" AND la.notreadyreason IN ('.$nrr_occupied_sql.')) ';
	  }
		
	  $query_fields .= '
	  					SUM(la.totalduration) as total_loggedin, 
						SUM(IF((la.agentstatus != "READY" and la.agentstatus != "NOT READY") '.$query_subfields_occupied.', la.totalduration, 0)) as total_occupied, 
						SUM(IF((la.agentstatus = "READY"), la.totalduration, 0)) as total_ready, 
						SUM(IF((la.agentstatus = "NOT READY"),la.totalduration,0)) as total_notready
						';
	  				
	  $query_count_fields = 'SELECT COUNT(*) as nbtotalrecords';
		
	  
	  $queue_tables =  '				
						FROM '.$this->tableclassname.' la
						';
	  
	  if($this->stat_aggregate == 'agent')
	  {
		  $queue_tables .= ' 				
						LEFT JOIN conf_agent ca ON la.agentdbid = ca.dbid 
						LEFT JOIN conf_agent_info cai ON ca.info_fk = cai.dbid ';
	  }
	  else if($this->stat_aggregate == 'group')
	  {
		  $queue_tables .=  '
						LEFT JOIN conf_group cg ON la.groupdbid = cg.dbid
						';
	  }
	  else if($this->stat_aggregate == 'site')
	  {
		  $queue_tables .=  '
						LEFT JOIN conf_site si ON la.sitedbid = si.dbid
						';
	  }
	  else if($this->stat_aggregate == 'team')
	  {
		  $queue_tables .=  '
						LEFT JOIN conf_team te ON la.teamdbid = te.dbid
						';
	  }
	  
	  
	  $query_count_tables = '
						FROM '.$this->tableclassname.' la
						';
	  if($this->stat_aggregate == 'agent')
	  {
		  $query_count_tables .= ' 				
						LEFT JOIN conf_agent ca ON la.agentdbid = ca.dbid 
						LEFT JOIN conf_agent_info cai ON ca.info_fk = cai.dbid ';
	  }
	  else if($this->stat_aggregate == 'group')
	  {
		  $query_count_tables .=  '
						LEFT JOIN conf_group cg ON la.groupdbid = cg.dbid
						';
	  }
	  else if($this->stat_aggregate == 'site')
	  {
		  $query_count_tables .=  '
						LEFT JOIN conf_site si ON la.sitedbid = si.dbid
						';
	  }
						
	  $query_filters = '
						 WHERE 
						(la.covereddate >= :stat_startdate_ext and la.covereddate <= :stat_enddate_ext )
						';
	  	
						
	  $fieldvalues['stat_startdate_ext'] = $this->stat_startdate_ext;
	  $fieldvalues['stat_enddate_ext'] = $this->stat_enddate_ext;
	  if(!fonivaFilter::isCurrentUserSuperTenant())
	  {
		  $query_filters .= ' AND la.tenant_fk = :tenantdbid';
		  $fieldvalues['tenantdbid'] = fonivaFilter::getCurrentUserTenantId();
	  }
	  
	  if($this->stat_agent == 'USERFOLLOWEDAGENTS')
	  {
	      $query_filters .= ' AND la.agentdbid IN (SELECT agent_fk FROM conf_user_followedagent WHERE user_fk = :currentuserdbid)';
	      $fieldvalues['currentuserdbid'] = fonivaFilter::getCurrentUser()->getDbid();
	  } 
	  else if($this->stat_agent != '')
	  {
		  $query_filters .= ' AND la.agentdbid = :stat_agent';
		  $fieldvalues['stat_agent'] = $this->stat_agent;
	  }
	  else if($this->stat_agent == '' && $this->stat_aggregate == 'agent')
	  {
		  if(!$this->include_inactive_agents)
		      $query_filters .= ' AND ca.active = "yes" ';
	  }
		
	  if($this->stat_group != '')
	  {
		  $query_filters .= ' AND la.groupdbid = :stat_group';
		  $fieldvalues['stat_group'] = $this->stat_group;
	  }
	  
	  	  	
	  // filter by allowed groups
	  if(!fonivaFilter::canCurrentUserManageAllGroups())
	  {
		  $this->groups = fonivaFilter::currentUserManagedGroups();
		  $this->groups_list = array();
		  $this->groupids_list = array();
		  $allowed = false;
		  foreach($this->groups as $g)
		  {
			  $this->groups_list[$g->getDbid()] = $g;
			  array_push($this->groupids_list, $g->getDbid());
		  }
		
	  	  if(sizeof($this->groupids_list) > 0)
		  {
			  $inQuery = implode(',', $this->groupids_list);
			  $query_filters .= ' AND la.groupdbid IN ('.$inQuery.')';
		  }
	  }


	  if($this->stat_team != '')
	  {
		  $query_filters .= ' AND la.teamdbid = :stat_team';
		  $fieldvalues['stat_team'] = $this->stat_team;
	  }
		
	  if($this->stat_site != '')
	  {
		  $query_filters .= ' AND la.sitedbid = :stat_site';
		  $fieldvalues['stat_site'] = $this->stat_site;
	  }
	  
	  
	  $query_subfilter_excluded = '';
	  $nrr_excluded_sql = '""';
	  if(sizeof($nrr_excluded) > 0)
	  {
		$nrr_excluded_sql = '"' . implode('","', $nrr_excluded) . '"'; 
	  	$query_subfilter_excluded = ' AND !(la.agentstatus = "NOT READY" AND la.notreadyreason IN ('.$nrr_excluded_sql.')) ';
		
		$query_filters .= $query_subfilter_excluded;
	  }
	  
	  
	  $query_groupby = '';
	  if($this->stat_aggregate == 'agent')
	  {
		  $query_groupby .= '
	  					GROUP BY la.agentdbid, '.$this->query_groupby_dateformat.'
						';
	  }
	  else if($this->stat_aggregate == 'group')
	  {
		  $query_groupby .= '
						GROUP BY la.groupdbid, '.$this->query_groupby_dateformat.'
						';
	  }
	  else if($this->stat_aggregate == 'site')
	  {
		  $query_groupby .= '
						GROUP BY la.sitedbid, '.$this->query_groupby_dateformat.'
						';
	  }
	  else if($this->stat_aggregate == 'team')
	  {
		  $query_groupby .= '
						GROUP BY la.teamdbid, '.$this->query_groupby_dateformat.'
						';
	  }
	  
	  $query_orderby = '';
	  if($this->stat_aggregate == 'agent')
	  {
		  $query_orderby .= '
	  					ORDER BY agentfirstname, agentlastname, '.$this->query_groupby_dateformat.'
						';
	  }
	  else if($this->stat_aggregate == 'group')
	  {
		  $query_orderby .= '
						ORDER BY agentgroupname, '.$this->query_groupby_dateformat.'
						';
	  }
	  else if($this->stat_aggregate == 'site')
	  {
		  $query_orderby .= '
						ORDER BY agentsitename, '.$this->query_groupby_dateformat.'
						';
	  }
	  
	  
	  $query = $query_fields.''.$queue_tables.''.$query_filters.''.$query_groupby.''.$query_orderby;
	  if(!$this->csv_export)
	  	$query .= ' LIMIT '.(($this->getRequestParameter('page',1)-1)*sfConfig::get('app_display_nbrecordperpagereports')).','.sfConfig::get('app_display_nbrecordperpagereports');
	  
	  $query_count = $query_count_fields.''.$query_count_tables.''.$query_filters.''.$query_groupby;
	   
	  
	  //echo $query;
	  //exit();
	  
	  $query = $pdo->prepare($query);
	  $query_count = $pdo->prepare($query_count);
	  
	  
	  if(!$this->csv_export)
	  {
		  $query_count->execute($fieldvalues);
		  $countdata = $query_count->fetchAll();
		  $nbtotalrecordsfounded = sizeof($countdata); //[0]['nbtotalrecords']);
		  
		  $this->pager = new FonivaPager($this->tableclassname_doctrine, sfConfig::get('app_display_nbrecordperpagereports'));
		  $this->pager->setPage($this->getRequestParameter('page',1));
		  $this->pager->assignNbResults($nbtotalrecordsfounded);
		  $this->pager->init();
		  
			  
		  $query->execute($fieldvalues);
		  $this->data = $query->fetchAll();
	  }
	  else
	  {			  
		  $query->execute($fieldvalues);
		  $this->data = $query->fetchAll();
	  }
		  
	  
	  //Now ! We need to adjust a little bit.
	  //ALl these data concerns historical data
	  //Current agent events are not counted in those log_agent_event table
	  //We need to add them.
	  //for each set of 
	  if($this->stat_startdate_ext <= date('Y-m-d H:i:s') && date('Y-m-d H:i:s') <= $this->stat_enddate_ext)
	  {
		$this->livedata = $this->getoccupancy_livedata($pdo, $this->data, $this->stat_step, $this->stat_aggregate, $nrr_occupied_sql, $nrr_excluded_sql);  
	  }
	  
	  
	  if($this->csv_export)
	  {
	    $csvfilename = 'foniva_report_' . $this->getActionName() . '.csv';
		$this->setlayout(false);
		$this->getResponse()->clearHttpHeaders();
		$this->getResponse()->setHttpHeader('Content-Type', 'application/vnd.ms-excel');
		$this->getResponse()->setHttpHeader('Content-Disposition', 'attachment; filename='.$csvfilename);
		return 'Csvexport';
	  }	
	  else
	  {		
		$this->getResponse()->addJavascript('simple.carousel.js', 'last');
		$this->getResponse()->addStylesheet('simple.carousel.css', 'last');	
	    $this->getResponse()->addJavascript('jquery.toggleswitch.min.js', 'last');
	    $this->getResponse()->addStylesheet('toggleswitch.css', 'last');
	  }
	  
  }
  
  private function getoccupancy_livedata($pdo, $data, $stat_step, $stat_aggregate, $nrr_occupied_sql, $nrr_excluded_sql)
  {
	  $livedata = array();
	  foreach($data as $d)
	  {
		if($stat_aggregate == 'agent')
			$elementid = $d['agentdbid'];
		else if($stat_aggregate == 'group')
			$elementid = $d['groupdbid'];
		else if($stat_aggregate == 'site')
			$elementid = $d['sitedbid'];
		else if($stat_aggregate == 'team')
			$elementid = $d['teamdbid'];
		
		
		if(isset($livedata[$elementid]))
			continue;
		
		$livedata[$elementid] = $this->getoccupancy_livedata_singleelement($pdo, $elementid, $stat_step, $stat_aggregate, $nrr_occupied_sql, $nrr_excluded_sql);
		  
	  }
	  
	  return $livedata;
  }
  
  private function getoccupancy_livedata_singleelement($pdo, $elementid, $stat_step, $stat_aggregate, $nrr_occupied_sql, $nrr_excluded_sql)
  {
	  $livesingledata = array();
	  
	  $live_singledata['live_loggedin'] = 0;
	  $live_singledata['live_occupied'] = 0;
	  $live_singledata['live_ready'] = 0;
	  $live_singledata['live_notready'] = 0;

	  
	  $agents = array();
	  
	  //get the list of agents
	  $agents = Doctrine_Query::create()
								->select('ca.dbid')
								->from('ConfAgent ca')
								->where('ca.isconnected = ?', "yes");
	  if(!fonivaFilter::isCurrentUserSuperTenant())
	  	$agents->andWhere('ca.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	
	  if($stat_aggregate == 'agent')
		  $agents->andWhere('ca.dbid = ?', $elementid);
	  else if($stat_aggregate == 'group')
		  $agents->andWhere('ca.group_fk = ?', $elementid);  
	  else if($stat_aggregate == 'site')
		  $agents->andWhere('ca.site_fk = ?', $elementid);
	  
	  $agents = $agents->execute();
	  
	  
	  if($stat_step == 'hourly')
	  {
		$query_groupby_dateformat = ' DATE_FORMAT(NOW(), "%Y-%m-%d %H:00:00") ';
	  }
	  if($stat_step == 'monthly')
	  {
		$query_groupby_dateformat = ' DATE_FORMAT(NOW(), "%Y-%m-01 00:00:00") ';
	  }
	  if($stat_step == 'daily')
	  {
		$query_groupby_dateformat = ' DATE_FORMAT(NOW(), "%Y-%m-%d 00:00:00") ';
	  }
	  if($stat_step == 'weekly')
	  {
		$query_groupby_dateformat = ' DATE_FORMAT(NOW(), "%Y-%u 00:00:00") ';
	  }
	  if($stat_step == '15min')
	  {
		$query_groupby_dateformat = ' DATE_FORMAT(NOW(), "%Y-%m-%d %H:00:00") ';
	  }
	  if($stat_step == '30min')
	  {
		$query_groupby_dateformat = ' DATE_FORMAT(NOW(), "%Y-%m-%d %H:00:00") ';
	  }
	  
	  
	  $query_subfields_occupied = ' OR (law.agentstatus = "NOT READY" AND law.notreadyreason IN ('.$nrr_occupied_sql.')) ';
	  foreach($agents as $a)
	  {
		//for each agent, we need to provide the addition live loggin, ready informations
		 $q = 'SELECT 
		 			'.$query_groupby_dateformat.' as live_date_segment,
					TIME_TO_SEC(TIMEDIFF(NOW(), law.started_at)) as live_loggedin, 
					IF((law.agentstatus != "READY" and law.agentstatus != "NOT READY") '.$query_subfields_occupied.', TIME_TO_SEC(TIMEDIFF(NOW(), law.started_at)), 0) as live_occupied, 
					IF((law.agentstatus = "READY"), TIME_TO_SEC(TIMEDIFF(NOW(), law.started_at)), 0) as live_ready, 
					IF((law.agentstatus = "NOT READY"),TIME_TO_SEC(TIMEDIFF(NOW(), law.started_at)),0) as live_notready  
			   FROM 
			   		log_agent_event_whenoccured law
				WHERE agentdbid = :agentdbid
					AND law.agentstatus != "LOGGED OUT"
				    AND !(law.agentstatus = "NOT READY" AND law.notreadyreason IN ('.$nrr_excluded_sql.')) 
				ORDER BY law.started_at DESC 
				LIMIT 1	
			   		';
					
		$fieldvalues = array();
		$fieldvalues['agentdbid'] = $a->dbid;
		$q = $pdo->prepare($q);
	    $q->execute($fieldvalues);
		$qdata = $q->fetchAll();
		
		if(sizeof($qdata) > 0)
	  	{
			$qdata = $qdata[0];
			$live_singledata['live_date_segment'] = $qdata['live_date_segment'];
			$live_singledata['live_loggedin'] += $qdata['live_loggedin'];
		    $live_singledata['live_occupied'] += $qdata['live_occupied'];
		    $live_singledata['live_ready'] += $qdata['live_ready'];
		    $live_singledata['live_notready'] += $qdata['live_notready'];	
		}
	  }
	  
	  return $live_singledata;
  }
  
  public function executeAgentcalltransfered(sfWebRequest $request)
  {	  
  	  $this->reportcategory = 'AGENT';
	  $this->agentformparse($request);
	  $this->getreports('AGENT');
	  $this->buildagentreportform($request);
	  
	  
  	  $fieldvalues = array();
	  $pdo = Doctrine_Manager::getInstance()->getCurrentConnection()->getDbh();
	  
	  if($this->reporton == 'AGENT')
	  	$this->stateltscolumn = 'agent_id';
	  else
	  	$this->stateltscolumn = 'group_id';
		
	  $query = '';
	  $query_count = '';
	  
	  $query_fields = 'SELECT lacallxfer.occured_at as occured_at, la.tenant_fk, la.tracknumber,
	  					lacallxfer.agent_id as agentdbid,
						lacallxfer.group_id as groupdbid,
						la.callername, la.callernumber, la.callednum,
						la.is_inbound, la.is_manual,
						ca.username as agentusername,
						cai.firstname as agentfirstname, cai.lastname as agentlastname,
						cg.name as agentgroupname,
						cque.name as queuename,
						lacallxfer.transferedto as xfer_transferedto ';
						
	  $query_count_fields = 'SELECT COUNT(*) as nbtotalrecords';
		
		
	   $queue_tables =  '				
						FROM log_agent_calltransfer lacallxfer
						LEFT JOIN log_agent_call la ON la.tracknumber = lacallxfer.tracknumber
						LEFT JOIN conf_agent ca ON lacallxfer.agent_id = ca.dbid
						LEFT JOIN conf_agent_info cai ON ca.info_fk = cai.dbid
						LEFT JOIN conf_group cg ON lacallxfer.group_id = cg.dbid
						LEFT JOIN conf_queue cque ON cque.dbid = la.queue_id
						';
		$query_count_tables = '
						FROM log_agent_calltransfer lacallxfer
						LEFT JOIN log_agent_call la ON la.tracknumber = lacallxfer.tracknumber
						LEFT JOIN conf_agent ca ON lacallxfer.agent_id = ca.dbid
						';
						
		$query_filters = '
						 WHERE 
						(lacallxfer.occured_at >= :stat_startdate_ext and lacallxfer.occured_at <= :stat_enddate_ext )
						';
	  	
						
	  $fieldvalues['stat_startdate_ext'] = $this->stat_startdate_ext;
	  $fieldvalues['stat_enddate_ext'] = $this->stat_enddate_ext;
	  if(!fonivaFilter::isCurrentUserSuperTenant())
	  {
		  $query_filters .= ' AND la.tenant_fk = :tenantdbid';
		  $fieldvalues['tenantdbid'] = fonivaFilter::getCurrentUserTenantId();
	  }
	  
	  
	  if($this->stat_agent == 'USERFOLLOWEDAGENTS')
	  {
	    $query_filters .= ' AND la.agentdbid IN (SELECT agent_fk FROM conf_user_followedagent WHERE user_fk = :currentuserdbid)';
	    $fieldvalues['currentuserdbid'] = fonivaFilter::getCurrentUser()->getDbid();
	  } 
	  else if($this->stat_agent != '')
	  {
		 $query_filters .= ' AND la.agentdbid = :stateltsagent';
		 $fieldvalues['stateltsagent'] = $this->stat_agent;
	  }
	  else if($this->stat_agent == '')
	  {
		  if(!$this->include_inactive_agents)
	      	$query_filters .= ' AND ca.active = "yes" ';
	  }
	
	  if($this->stat_group != '')
	  {
		$query_filters .= ' AND la.groupdbid = :stateltsgroup';
		$fieldvalues['stateltsgroup'] = $this->stat_group;
	  }
	  
	  	  	
	  // filter by allowed groups
	  if(!fonivaFilter::canCurrentUserManageAllGroups())
	  {
		  $this->groups = fonivaFilter::currentUserManagedGroups();
		  $this->groups_list = array();
		  $this->groupids_list = array();
		  $allowed = false;
		  foreach($this->groups as $g)
		  {
			  $this->groups_list[$g->getDbid()] = $g;
			  array_push($this->groupids_list, $g->getDbid());
		  }
		
	  	  if(sizeof($this->groupids_list) > 0)
		  {
			  $inQuery = implode(',', $this->groupids_list);
			  $query_filters .= ' AND la.groupdbid IN ('.$inQuery.')';
		  }
	  }


	  if($this->stat_team != '')
	  {
		$query_filters .= ' AND la.teamdbid = :stateltsteam';
		$fieldvalues['stateltsteam'] = $this->stat_team;
	  }
	
	  if($this->stat_site != '')
	  {
	    $query_filters .= ' AND la.sitedbid = :stateltssite';
		 $fieldvalues['stateltssite'] = $this->stat_site;
	  }
	  
	  
	  $query_orderby = ' ORDER BY lacallxfer.occured_at, lacallxfer.'.$this->stateltscolumn.' ';
	  
	  
	  $query = $query_fields.''.$queue_tables.''.$query_filters.''.$query_orderby;
	  if(!$this->csv_export)
	  	$query .= ' LIMIT '.(($this->getRequestParameter('page',1)-1)*sfConfig::get('app_display_nbrecordperpagereports')).','.sfConfig::get('app_display_nbrecordperpagereports');
	  //exit();
	  //echo $query;
	  //exit();
	  $query_count = $query_count_fields.''.$query_count_tables.''.$query_filters;
	
	  $query = $pdo->prepare($query);
	  $query_count = $pdo->prepare($query_count);
	  
	  if(!$this->csv_export)
	  {
		  $query_count->execute($fieldvalues);
		  $countdata = $query_count->fetchAll();
		  $nbtotalrecordsfounded = $countdata[0]['nbtotalrecords'];
		  
		  $this->pager = new FonivaPager('LogAgentCall', sfConfig::get('app_display_nbrecordperpagereports'));
		  $this->pager->setPage($this->getRequestParameter('page',1));
		  $this->pager->assignNbResults($nbtotalrecordsfounded);
		  $this->pager->init();
		  
			  
		  $query->execute($fieldvalues);
		  $this->data = $query->fetchAll();
	  }
	  else
	  {
		  $query->execute($fieldvalues);
		  $this->data = $query->fetchAll();
	  }
	  
	  
	  //go and get list of elements info (agent or groups)
	  $listids = array();
	  $listcampaignids = array();
	  $listinboundsids = array();
	  $listdialplansids = array();
	  foreach($this->data as $d)
	  {
		if($d->is_inbound == 'yes')
		{
			if(!isset($listinboundsids[$d->campaign_id]))
				$listinboundsids[$d->campaign_id] = $d->campaign_id;
		}
		else
		{		
			if(!isset($listcampaignids[$d->campaign_id]))
				$listcampaignids[$d->campaign_id] = $d->campaign_id;
		}
		if(!isset($listdialplansids[$d->dialplan_id]))
			$listdialplansids[$d->dialplan_id] = $d->dialplan_id;	
	  }			
						
	  $infos = array();
	  $this->campaignsinfos = array();
	  $this->inboundsinfos = array();
	  $this->dialplaninfos = array();
	  
	  //get campaign infos
	  $infos = Doctrine_Query::create()
						->select('a.dbid as dbid, a.campaign_name')
						->from('ConfCampaign a')
						->wherein('a.dbid', array_values($listcampaignids));
	  if(!fonivaFilter::isCurrentUserSuperTenant())
		$infos->andWhere('a.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	  $infos = $infos->execute();
	  foreach($infos as $i)
			$this->campaignsinfos[$i->dbid] = $i;
			
	  //get inbound infos 
	  $infos = Doctrine_Query::create()
						->select('a.dbid as dbid, a.inbound_name')
						->from('ConfInbound a')
						->wherein('a.dbid', array_values($listinboundsids));
	  if(!fonivaFilter::isCurrentUserSuperTenant())
		$infos->andWhere('a.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	  $infos = $infos->execute();
	  foreach($infos as $i)
			$this->inboundsinfos[$i->dbid] = $i;			
	  
	  //get dialplan infos 
	  $infos = Doctrine_Query::create()
						->select('a.dbid as dbid, a.dialplan')
						->from('ConfDialplan a')
						->wherein('a.dbid', array_values($listdialplansids));
	  if(!fonivaFilter::isCurrentUserSuperTenant())
		$infos->andWhere('a.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	  $infos = $infos->execute();
	  foreach($infos as $i)
			$this->dialplaninfos[$i->dbid] = $i;
	  
	  
	  if($this->csv_export)
	  {
	    $csvfilename = 'foniva_report_' . $this->getActionName() . '.csv';
		$this->setlayout(false);
		$this->getResponse()->clearHttpHeaders();
		$this->getResponse()->setHttpHeader('Content-Type', 'application/vnd.ms-excel');
		$this->getResponse()->setHttpHeader('Content-Disposition', 'attachment; filename='.$csvfilename);
		return 'Csvexport';
	  }	
	  else
	  {		
		$this->getResponse()->addJavascript('simple.carousel.js', 'last');
		$this->getResponse()->addStylesheet('simple.carousel.css', 'last');	
	    $this->getResponse()->addJavascript('jquery.toggleswitch.min.js', 'last');
	    $this->getResponse()->addStylesheet('toggleswitch.css', 'last');	
	  }
	  
  }
  
  public function executeAgentadherencedetailed(sfWebRequest $request)
  {
	  $this->reportcategory = 'AGENT';
	  $this->agentformparse($request);
	  $this->getreports('AGENT');
	  $this->buildagentreportform($request);
	  
	  //retrieve the activites for colors 
	  $this->activities = Doctrine_Query::create()
						->select('a.name, a.colorcode')
						->from('ConfAgentActivity a');
	  if(!fonivaFilter::isCurrentUserSuperTenant())
		$this->activities->andWhere('a.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	  $this->activities = $this->activities->execute();
	  
	  $this->activitiescolors = array();
	  foreach($this->activities as $act)
	  	$this->activitiescolors[$act->name] = $act->colorcode;
	  
	  $this->getResponse()->addStylesheet('adherencereport.css', 'last');
	  $this->getResponse()->addJavascript('adherencereport.js', 'last');
	  
	  
	  $this->selectedagents = Doctrine_Query::create()
						->select('ca.dbid, ca.username, ca.isconnected, ca.group_fk, ca.active, ca.shortusername, ci.firstname as firstname, ci.lastname as lastname')
						->from('ConfAgent ca')						
						->leftJoin('ca.ConfAgentInfo ci ON ci.dbid = ca.info_fk')
						->orderBy('ca.username')
						->where('1');
	  if(!fonivaFilter::isCurrentUserSuperTenant())
		$this->selectedagents->andWhere('ca.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
		
	  if(!fonivaFilter::canCurrentUserManageAllGroups())
		$this->selectedagents->andWhere('ca.group_fk in ?', array($this->groupids_list));
		
	  if(!fonivaFilter::canCurrentUserManageAllSites())
		$this->selectedagents->andWhere('ca.site_fk in ?', array($this->siteids_list));
	  
	  if($this->stat_agent == 'USERFOLLOWEDAGENTS')
	  {
		  $subq = Doctrine_Query::create()
		  				->select('fa.agent_fk')
		  				->from('ConfUserFollowedagent fa')
						->where('fa.user_fk = "'.fonivaFilter::getCurrentUser()->getDbid().'"');
		  $this->selectedagents->andWhere('ca.dbid IN ('.$subq->getDql().')');
	  }
	  else if($this->stat_agent != '')
	  {
		$this->selectedagents->andWhere('ca.dbid = ?', $this->stat_agent);
	  }
	  else if($this->stat_agent == '')
	  {
		$this->selectedagents->andWhere('ca.active = "yes"');
	  }
	
	  if($this->stat_group != '')
	  {
		$this->selectedagents->andWhere('ca.group_fk = ?', $this->stat_group);
	  }
	
	  if($this->stat_team != '')
	  {
		$this->selectedagents->andWhere('ca.team_fk = ?', $this->stat_team);
	  }
	
	  if($this->stat_site != '')
	  {
	    $this->selectedagents->andWhere('ca.site_fk = ?', $this->stat_site);
	  }
	  
	  $this->selectedagents = $this->selectedagents->execute();
	  
	  
	  //get coverage data
	  $pdo = Doctrine_Manager::getInstance()->getCurrentConnection()->getDbh();	  
	  $query = '';
	  $query_count = '';
	
	  $query_fields = 'SELECT ds.starttime as starttime, ds.endtime as endtime, ca.starttime as castarttime, ca.endtime as caendtime,
						SUM((CONCAT(:concerneddate, " ", ds.starttime) < ca.endtime 
									AND ca.starttime < CONCAT(:concerneddate, " ", ds.endtime) AND ca.activity = "WORKING"))  as totalagents_working,
						SUM((CONCAT(:concerneddate, " ", ds.starttime) < ca.endtime 
									AND ca.starttime < CONCAT(:concerneddate, " ", ds.endtime) AND ca.activity != "WORKING"))  as totalagents_notworking
	  ';
	  if($this->stat_group != '')
	  {
		  $query_fields .= ' , cfa.group_fk  ';
	  }
	  
	  if($this->stat_team != '')
	  {
		  $query_fields .= ' , cfa.team_fk  ';
	  }
	  
 	  $query_tables = '
	  				FROM ( dataseries_daytimes_15min ds , conf_agent_schedule_event ca )
					';
					
	  //if($this->stat_group != '')
	  //{
		  $query_tables .= ' , conf_agent cfa  ';
	  //}
	  
	  $query_filters = '
	  			 WHERE 1 
				';
	   $query_filters .= ' AND cfa.dbid = ca.agent_fk ';
	   
	  if($this->stat_team != '')
	  {
	      $query_filters .= '
		  			AND cfa.team_fk = :selectedteam
		  			';
		  $fieldvalues['selectedteam'] = $this->stat_team;	
	  }
	  
	  if($this->stat_group != '')
	  {
	      $query_filters .= '
		  			AND cfa.group_fk = :selectedgroup 
		  			';
		  $fieldvalues['selectedgroup'] = $this->stat_group;	
	  }
	  else if($this->stat_agent == 'USERFOLLOWEDAGENTS')
	  {
	      $query_filters .= ' AND ca.agent_fk IN (SELECT agent_fk FROM conf_user_followedagent WHERE user_fk = :currentuserdbid)';
	     $fieldvalues['currentuserdbid'] = fonivaFilter::getCurrentUser()->getDbid();
	  } 
	  else if($this->stat_agent != '')
	  {
	      $query_filters .= '
		  			AND ca.agent_fk = :selectedagent 
		  			';
		  $fieldvalues['selectedagent'] = $this->stat_agent;	
	  }
	  else if($this->stat_agent == '')
	  {
	      $query_filters .= ' AND cfa.active = "yes" ';
	  }
	  
	  
	  $this->selecteddate = substr($this->stat_startdate_ext, 0, 10);
	  $fieldvalues['concerneddate'] = $this->selecteddate;	
	  
	  $query_groupby = '  GROUP BY ds.starttime, ds.endtime ';
	  
	  //if($request->getParameter('groupid') != NULL)
	  //{
	  //	  $query_groupby .= ' , cfa.group_fk  ';
	  //}
	  
	  $query_orderby = '  ';
	  $query = $query_fields.''.$query_tables.''.$query_filters.''.$query_groupby.''.$query_orderby;
	  //echo $query;
	  $query = $pdo->prepare($query);
	  $query->execute($fieldvalues);
	  $this->coverageddata = $query->fetchAll();
	  
	  $this->getResponse()->addStylesheet('tooltipster.css', 'last');
	  $this->getResponse()->addStylesheet('tooltipster-shadow.css', 'last');
	  $this->getResponse()->addJavascript('jquery.tooltipster.min.js', 'last');  
	  	
	  $this->getResponse()->addJavascript('chart.min.js', 'last');
	  $this->getResponse()->addJavascript('Chart.StackedBar.js', 'last');
	  
	  	
	  $this->getResponse()->addJavascript('jquery.toggleswitch.min.js', 'last');
	  $this->getResponse()->addStylesheet('toggleswitch.css', 'last');
  }
  
  public function executeAgentadherencecompareddetail(sfWebRequest $request)
  {
	 sfConfig::set('sf_web_debug', false);  
	$this->getResponse()->setContentType('text/json');
	$this->setLayout(false);
	
	
	// STEP 2: retrieve agent schedule
	$this->forward404Unless($this->agentid = $request->getParameter('agentid'));
	$this->forward404Unless($this->conf_agent = Doctrine::getTable('ConfAgent')->find($request->getParameter('agentid')), sprintf('Object conf_agent does not exist (%s).', $request->getParameter('agentid')));
    $this->forward404Unless(fonivaFilter::isCurrentUserSuperTenant() || $this->conf_agent->getTenantFk() == fonivaFilter::getCurrentUserTenantId(), sprintf('Object conf_agent cannot be access (%s).', $request->getParameter('agentid')));
	$this->conf_agent_info = $this->conf_agent->ConfAgentInfo;
	
	$this->activities = Doctrine_Query::create()
						->select('a.dbid, a.name, a.colorcode')
						->from('ConfAgentActivity a')
						->where('a.active = "yes"')
						->orderBy('a.name');
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$this->activities->andWhere('a.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	$this->activities = $this->activities->execute();
	
	$this->activitycolors = array();
	foreach($this->activities as $a)
		$this->activitycolors[$a->name] = $a->colorcode;
	
	if(!fonivaFilter::canCurrentUserManageAllGroups())
	{
		$allowed = false;
		$groups = fonivaFilter::currentUserManagedGroups();
		foreach($groups as $g)
		{
			if($this->conf_agent->getGroupFk() == $g->getDbid())
			{
				$allowed = true;
				break;	
			}
		}
		
		if(!$allowed)
			$this->forward404();
	}
	
	if(!fonivaFilter::canCurrentUserManageAllSites())
	{
		$allowed = false;
		$sites = fonivaFilter::currentUserManagedSites();
		foreach($sites as $s)
		{
			if($this->conf_agent->getSiteFk() == $s->getDbid())
			{
				$allowed = true;
				break;	
			}
		}
		
		if(!$allowed)
			$this->forward404();
	}
	
	$this->forward404Unless($this->starttime = $request->getParameter('start'));
	$this->forward404Unless($this->endtime = $request->getParameter('end'));
	$this->starttime = $this->starttime.' 00:00:00';
	$this->endtime = $this->endtime.' 23:59:59';
	
	$this->scheduleevents = Doctrine_Query::create()
							->select('a.dbid, a.agent_fk, a.activity as activityname, a.starttime, a.endtime, 
										TIMESTAMPDIFF(SECOND, a.starttime, a.endtime) as duration, 
										TIMESTAMPDIFF(MINUTE, "'.$this->starttime.'", a.starttime) as schedulestarttime_minutes, 
										ac.colorcode as colorcode, ac.ispaid as ispaid, ac.paidpercentage as paidpercentage')
							->from('ConfAgentScheduleEvent a')
							->leftJoin('a.ConfAgentActivity ac ON ac.name = a.activity')
							->where('a.agent_fk = ? and starttime >= ? and endtime <= ?', array($this->agentid, $this->starttime, $this->endtime))
							->orderBy('a.starttime')
							->execute();
	
	
	// STEP 2: retrieve agent real data 
	$pdo = Doctrine_Manager::getInstance()->getCurrentConnection()->getDbh();
	$query = '';	  
	$query_fields = 'SELECT las.dbid, las.agentdbid as agentdbid, las.agentstatus, las.notreadyreason,
								(CASE las.agentstatus WHEN "NOT READY" THEN IF(las.notreadyreason != "", las.notreadyreason, "NOT READY")  WHEN "LOGGED OUT" THEN "LOGGED OUT" ELSE "WORKING" END) as activityname,
								las.started_at, las.duration, DATE_ADD(las.started_at, INTERVAL las.duration SECOND) as ended_at,
								TIMESTAMPDIFF(MINUTE, "'.$this->starttime.'", las.started_at) as workstarttime_minutes,
								round(las.activitycost, 2) as activitycost
							  ';
	$queue_tables =  '				
					FROM log_agent_event las
					';
	$query_filters = '
					 WHERE las.agentdbid = :stateltsvalue AND 
					(DATE_ADD(las.started_at, INTERVAL las.duration SECOND) >= :stat_startdate_ext AND las.started_at <= :stat_enddate_ext )
					';
	$query_orderby = ' ORDER BY  las.started_at ';	  
	
	$fieldvalues['stat_startdate_ext'] = $this->starttime;
	$fieldvalues['stat_enddate_ext'] = $this->endtime;
	$fieldvalues['stateltsvalue'] = $this->agentid;
	
	$query = $query_fields.''.$queue_tables.''.$query_filters.''.$query_orderby;
	$query = $pdo->prepare($query);
	$query->execute($fieldvalues);
	$this->workedevents = $query->fetchAll();
	
  }
  
  public function executeInboundcalldetails(sfWebRequest $request)
  {
	  $this->reportcategory = 'INBOUND';
	  $this->inboundformparse($request);
	  $this->getreports('INBOUND');
	  $this->buildinboundreportform($request);
	  
	  $this->data = Doctrine_Query::create()
	  					->select('a.tracknumber, a.callername, a.callernumber, a.callednumber, a.is_inbound '.
								 'a.campaign_id, a.dialplan_id, a.dialplan_type, a.callresult, '.
								 'a.call_init_time, a.dialplan_end_time')
	  					->from('LogCallRecord a')
						->where('a.call_init_time >= ? and a.call_init_time <= ? '.
								(($this->stat_inbound == '')?'':' and a.campaign_id = ? '), 
								($this->stat_inbound == '') ? array($this->stat_startdate_ext, $this->stat_enddate_ext) : array($this->stat_startdate_ext, $this->stat_enddate_ext, $this->stat_inbound)
								)
						->andWhere('a.is_inbound="yes"')
						->orderBy('a.call_init_time, a.campaign_id ');
	  if(!fonivaFilter::isCurrentUserSuperTenant())
		  $this->data->andWhere('a.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
		  
	  if(!fonivaFilter::canCurrentUserManageAllInbounds())
	  {
		  $inbounds = fonivaFilter::currentUserManagedInbounds();
		  $inbounds_dbids = array();
		  foreach($inbounds as $is)
		  	array_push($inbounds_dbids, $is->dbid);
		  $this->data->andWhereIn('a.campaign_id', $inbounds_dbids);
	  }
		  
	  if($this->stat_searchcalleridnum != null && $this->stat_searchcalleridnum != '')
	  	  $this->data->andWhere('a.callernumber = ?', $this->stat_searchcalleridnum);
	  
	  if($this->stat_searchcallednum != null && $this->stat_searchcallednum != '')
	  	  $this->data->andWhere('a.callednumber = ?', $this->stat_searchcallednum);
	  
	  if($this->stat_searchtracknumber != null && $this->stat_searchtracknumber != '')
	  	  $this->data->andWhere('a.tracknumber = ?', $this->stat_searchtracknumber);
	  
	  if(!$this->csv_export)
	  {
		  $this->pager = new sfDoctrinePager('LogCallRecord', sfConfig::get('app_display_nbrecordperpagereports'));
		  $this->pager->setPage($this->getRequestParameter('page',1));
		  $this->pager->setQuery($this->data);
		  $this->pager->init();
		  $this->data = $this->pager->getResults();
	  }
	  else
		  $this->data = $this->data->execute();					
	  
	  //go and get list of elements info (agent or groups)
	  $listids = array();
	  $listcampaignids = array();
	  $listinboundsids = array();
	  $listdialplansids = array();
	  foreach($this->data as $d)
	  {
		if($d->is_inbound == 'yes')
		{
			if(!isset($listinboundsids[$d->campaign_id]))
				$listinboundsids[$d->campaign_id] = $d->campaign_id;
		}
		else
		{		
			if(!isset($listcampaignids[$d->campaign_id]))
				$listcampaignids[$d->campaign_id] = $d->campaign_id;
		}
		if(!isset($listdialplansids[$d->dialplan_id]))
			$listdialplansids[$d->dialplan_id] = $d->dialplan_id;	
	  }			
						
	  $infos = array();
	  $this->campaignsinfos = array();
	  $this->inboundsinfos = array();
	  $this->dialplaninfos = array();
	  
	  //get campaign infos
	  $infos = Doctrine_Query::create()
						->select('a.dbid as dbid, a.campaign_name')
						->from('ConfCampaign a')
						->wherein('a.dbid', array_values($listcampaignids));
	  if(!fonivaFilter::isCurrentUserSuperTenant())
		$infos->andWhere('a.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	  $infos = $infos->execute();
	  foreach($infos as $i)
			$this->campaignsinfos[$i->dbid] = $i;
			
	  //get inbound infos 
	  $infos = Doctrine_Query::create()
						->select('a.dbid as dbid, a.inbound_name')
						->from('ConfInbound a')
						->wherein('a.dbid', array_values($listinboundsids));
	  if(!fonivaFilter::isCurrentUserSuperTenant())
		$infos->andWhere('a.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	  $infos = $infos->execute();
	  foreach($infos as $i)
			$this->inboundsinfos[$i->dbid] = $i;			
	  
	  //get dialplan infos 
	  $infos = Doctrine_Query::create()
						->select('a.dbid as dbid, a.dialplan')
						->from('ConfDialplan a')
						->wherein('a.dbid', array_values($listdialplansids));
	  if(!fonivaFilter::isCurrentUserSuperTenant())
		$infos->andWhere('a.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	  $infos = $infos->execute();
	  foreach($infos as $i)
			$this->dialplaninfos[$i->dbid] = $i;
	  
	  
	  if($this->csv_export)
	  {
		$csvfilename = 'foniva_report_' . $this->getActionName() . '.csv';
		$this->setlayout(false);
		$this->getResponse()->clearHttpHeaders();
		$this->getResponse()->setHttpHeader('Content-Type', 'application/vnd.ms-excel');
		$this->getResponse()->setHttpHeader('Content-Disposition', 'attachment; filename='.$csvfilename);
		return 'Csvexport'; /* use csv export template instead*/
	  }	
	  else
	  {		
		$this->getResponse()->addJavascript('simple.carousel.js', 'last');
		$this->getResponse()->addStylesheet('simple.carousel.css', 'last');		
	    $this->getResponse()->addJavascript('jquery.toggleswitch.min.js', 'last');
	    $this->getResponse()->addStylesheet('toggleswitch.css', 'last');
		$this->getResponse()->addJavascript('jplayer/jPlayer-2.9.2/jplayer/jquery.jplayer.min.js', 'last');
		$this->getResponse()->addStylesheet('/js/jplayer/jPlayer-2.9.2/skin/blue.monday/css/jplayer.blue.monday.css', 'last');
	  }
  }
  
  public function executeOutboundcalldetails(sfWebRequest $request)
  {
	  $this->reportcategory = 'OUTBOUND';
	  $this->outboundformparse($request);
	  $this->getreports('OUTBOUND');
	  $this->buildoutboundreportform($request);
	  
	  $this->data = Doctrine_Query::create()
	  					->select('a.tracknumber, a.callername, a.callernumber, a.callednumber, a.is_inbound '.
								 'a.campaign_id, a.dialplan_id, a.dialplan_type, a.callresult, '.
								 'a.call_init_time, a.dialplan_end_time')
	  					->from('LogCallRecord a')
						->where('a.call_init_time >= ? and a.call_init_time <= ? '.
								(($this->stat_outbound == '')?'':' and a.campaign_id = ? '), 
								($this->stat_outbound == '') ? array($this->stat_startdate_ext, $this->stat_enddate_ext) : array($this->stat_startdate_ext, $this->stat_enddate_ext, $this->stat_outbound)
								)
						->andWhere('a.is_inbound="no"')
						->orderBy('a.call_init_time, a.campaign_id ');
	  if(!fonivaFilter::isCurrentUserSuperTenant())
		  $this->data->andWhere('a.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
		  
	  if($this->stat_searchcalleridnum != null && $this->stat_searchcalleridnum != '')
	  	  $this->data->andWhere('a.callernumber = ?', $this->stat_searchcalleridnum);
	  
	  if($this->stat_searchcallednum != null && $this->stat_searchcallednum != '')
	  	  $this->data->andWhere('a.callednumber = ?', $this->stat_searchcallednum);
	  
	  if($this->stat_searchtracknumber != null && $this->stat_searchtracknumber != '')
	  	  $this->data->andWhere('a.tracknumber = ?', $this->stat_searchtracknumber);
		  
	  if(!$this->csv_export)
      {
	    $this->pager = new sfDoctrinePager('LogCallRecord', sfConfig::get('app_display_nbrecordperpagereports'));
	    $this->pager->setPage($this->getRequestParameter('page',1));
	    $this->pager->setQuery($this->data);
	    $this->pager->init();
	    $this->data = $this->pager->getResults();
	  }
	  else
	    $this->data = $this->data->execute();					
	  
	  //go and get list of elements info (agent or groups)
	  $listids = array();
	  $listcampaignids = array();
	  $listinboundsids = array();
	  $listdialplansids = array();
	  foreach($this->data as $d)
	  {
		if($d->is_inbound == 'yes')
		{
			if(!isset($listinboundsids[$d->campaign_id]))
				$listinboundsids[$d->campaign_id] = $d->campaign_id;
		}
		else
		{		
			if(!isset($listcampaignids[$d->campaign_id]))
				$listcampaignids[$d->campaign_id] = $d->campaign_id;
		}
		if(!isset($listdialplansids[$d->dialplan_id]))
			$listdialplansids[$d->dialplan_id] = $d->dialplan_id;	
	  }			
						
	  $infos = array();
	  $this->campaignsinfos = array();
	  $this->inboundsinfos = array();
	  $this->dialplaninfos = array();
	  
	  //get campaign infos
	  $infos = Doctrine_Query::create()
						->select('a.dbid as dbid, a.campaign_name')
						->from('ConfCampaign a')
						->wherein('a.dbid', array_values($listcampaignids));
	  if(!fonivaFilter::isCurrentUserSuperTenant())
		$infos->andWhere('a.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	  $infos = $infos->execute();
	  foreach($infos as $i)
			$this->campaignsinfos[$i->dbid] = $i;
			
	  //get inbound infos 
	  $infos = Doctrine_Query::create()
						->select('a.dbid as dbid, a.inbound_name')
						->from('ConfInbound a')
						->wherein('a.dbid', array_values($listinboundsids));
	  if(!fonivaFilter::isCurrentUserSuperTenant())
		$infos->andWhere('a.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	  $infos = $infos->execute();
	  foreach($infos as $i)
			$this->inboundsinfos[$i->dbid] = $i;			
	  
	  //get dialplan infos 
	  $infos = Doctrine_Query::create()
						->select('a.dbid as dbid, a.dialplan')
						->from('ConfDialplan a')
						->wherein('a.dbid', array_values($listdialplansids));
	  if(!fonivaFilter::isCurrentUserSuperTenant())
		$infos->andWhere('a.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	  $infos = $infos->execute();
	  foreach($infos as $i)
			$this->dialplaninfos[$i->dbid] = $i;
			
			
	  if($this->csv_export)
	  {
		$csvfilename = 'foniva_report_' . $this->getActionName() . '.csv';
		$this->setlayout(false);
		$this->getResponse()->clearHttpHeaders();
		$this->getResponse()->setHttpHeader('Content-Type', 'application/vnd.ms-excel');
		$this->getResponse()->setHttpHeader('Content-Disposition', 'attachment; filename='.$csvfilename);
		return 'Csvexport'; /* use csv export template instead*/
	  }	
	  else
	  {		
		$this->getResponse()->addJavascript('simple.carousel.js', 'last');
		$this->getResponse()->addStylesheet('simple.carousel.css', 'last');		
	    $this->getResponse()->addJavascript('jquery.toggleswitch.min.js', 'last');
	    $this->getResponse()->addStylesheet('toggleswitch.css', 'last');
		$this->getResponse()->addJavascript('jplayer/jPlayer-2.9.2/jplayer/jquery.jplayer.min.js', 'last');
		$this->getResponse()->addStylesheet('/js/jplayer/jPlayer-2.9.2/skin/blue.monday/css/jplayer.blue.monday.css', 'last');
	  }
		
  }
  
  public function executeOutbounddialedcalldetails(sfWebRequest $request)
  {
	  $this->reportcategory = 'OUTBOUND';
	  $this->outboundformparse($request);
	  $this->getreports('OUTBOUND');
	  $this->buildoutboundreportform($request);
	  
	  $this->data = Doctrine_Query::create()
	  					->select('a.tracknumber, a.event, a.campaign_dbid, a.callinglist_id, a.phone_number_dbid, '.
								 'a.phone_number, a.dialing_seconds, a.billable_seconds, a.created_at ')
	  					->from('LogDialing a')
						->where('a.created_at >= ? and a.created_at <= ? ', array($this->stat_startdate_ext, $this->stat_enddate_ext));
	
	  if($this->stat_outbound != '')
	  	$this->data->andWhere('a.campaign_dbid = ?', $this->stat_outbound);
		
	  if($this->stat_callinglist != '')
	    $this->data->andWhere('a.callinglist_id = ?', $this->stat_callinglist);
						
	  if(!fonivaFilter::isCurrentUserSuperTenant())
		  $this->data->andWhere('a.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	  
	  if($this->stat_searchcallednum != null && $this->stat_searchcallednum != '')
	  	  $this->data->andWhere('a.phone_number = ?', $this->stat_searchcallednum);
	  
	  if($this->stat_searchtracknumber != null && $this->stat_searchtracknumber != '')
	  	  $this->data->andWhere('a.tracknumber = ?', $this->stat_searchtracknumber);
		  
	  if(!$this->csv_export)
      {
	    $this->pager = new sfDoctrinePager('LogDialing', sfConfig::get('app_display_nbrecordperpagereports'));
	    $this->pager->setPage($this->getRequestParameter('page',1));
	    $this->pager->setQuery($this->data);
	    $this->pager->init();
	    $this->data = $this->pager->getResults();
	  }
	  else
	    $this->data = $this->data->execute();					
	  
	  //go and get list of elements info (agent or groups)
	  $listids = array();
	  $listcampaignids = array();
	  $listcallinglistsids = array();
	  foreach($this->data as $d)
	  {
		if(!isset($listcampaignids[$d->campaign_dbid]))
			$listcampaignids[$d->campaign_dbid] = $d->campaign_dbid;
	
		
		if(!isset($listcallinglistsids[$d->callinglist_id]))
			$listcallinglistsids[$d->callinglist_id] = $d->callinglist_id;	
	  }			
						
	  $infos = array();
	  $this->campaignsinfos = array();
	  $this->callinglistinfos = array();
	  
	  //get campaign infos
	  $infos = Doctrine_Query::create()
						->select('a.dbid as dbid, a.campaign_name')
						->from('ConfCampaign a');
	  if(sizeof($listcampaignids) > 0)
		$infos->wherein('a.dbid', array_values($listcampaignids));
	  if(!fonivaFilter::isCurrentUserSuperTenant())
		$infos->andWhere('a.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	  $infos = $infos->execute();
	  foreach($infos as $i)
			$this->campaignsinfos[$i->dbid] = $i;			
	  
	  //get dialplan infos 
	  $infos = Doctrine_Query::create()
						->select('a.dbid as dbid, a.calling_list_name')
						->from('ConfCallingList a, ConfCampaign c')
						->andwhere('a.campaign_dbid = c.dbid');
	  if(sizeof($listcallinglistsids) > 0)
		$infos->wherein('a.dbid', array_values($listcallinglistsids));
	  if(!fonivaFilter::isCurrentUserSuperTenant())
		$infos->andWhere('c.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	  $infos = $infos->execute();
	  foreach($infos as $i)
			$this->callinglistinfos[$i->dbid] = $i;
			
			
	  if($this->csv_export)
	  {
		$csvfilename = 'foniva_report_' . $this->getActionName() . '.csv';
		$this->setlayout(false);
		$this->getResponse()->clearHttpHeaders();
		$this->getResponse()->setHttpHeader('Content-Type', 'application/vnd.ms-excel');
		$this->getResponse()->setHttpHeader('Content-Disposition', 'attachment; filename='.$csvfilename);
		return 'Csvexport'; /* use csv export template instead*/
	  }	
	  else
	  {		
		$this->getResponse()->addJavascript('simple.carousel.js', 'last');
		$this->getResponse()->addStylesheet('simple.carousel.css', 'last');		
	    $this->getResponse()->addJavascript('jquery.toggleswitch.min.js', 'last');
	    $this->getResponse()->addStylesheet('toggleswitch.css', 'last');
		$this->getResponse()->addJavascript('jplayer/jPlayer-2.9.2/jplayer/jquery.jplayer.min.js', 'last');
		$this->getResponse()->addStylesheet('/js/jplayer/jPlayer-2.9.2/skin/blue.monday/css/jplayer.blue.monday.css', 'last');
	  }
		
  }
  
  public function executeQueuecalldetails(sfWebRequest $request)
  {
	  $this->reportcategory = 'QUEUE';
	  $this->queueformparse($request);
	  $this->getreports('QUEUE');
	  $this->buildqueuereportform($request);
	  
	  $fieldvalues = array();
	  $pdo = Doctrine_Manager::getInstance()->getCurrentConnection()->getDbh();
	  
	  $query = '';
	  $query_count = '';
	  
	  $query_fields = 'SELECT la.tracknumber, la.callername, la.callernumber, la.callednumber, la.queueresult, la.is_inbound, '.
								 'la.campaign_id, la.dialplan_id, la.queue_id, la.dialplan_type, la.queue_enter_time, '.
								 'la.queue_bridge_time, la.queue_exit_time, la.queue_agentdialing_result, la.queue_agentdialing_seconds, '.
								 'la.queue_agentanswered_seconds, la.agentdbid, la.groupdbid, (UNIX_TIMESTAMP(IFNULL(la.queue_bridge_time, la.queue_exit_time)) - UNIX_TIMESTAMP(la.queue_enter_time)) as customer_waittime , '.
								 'la.servicelevel, '.
								  'ca.username as agentusername, '.
								  'cai.firstname as agentfirstname, cai.lastname as agentlastname, '.
								  'cg.name as agentgroupname, '.
								  'cinb.inbound_name as inbound_name, '.
								  'coutb.campaign_name as outbound_name, '.
								  'cdia.dialplan as dialplanname, '.
								  'cque.name as queuename, '.
								  'lacallxfer.transferedto as xfer_transferedto, '.
								  'cdisp.calldisposition_name as cdisp_calldisposition_name ';
								  
	  $query_count_fields = 'SELECT COUNT(*) as nbtotalrecords';
	  
	  $queue_tables =  '				
						FROM log_queue_record la
						LEFT JOIN conf_agent ca ON la.agentdbid = ca.dbid
						LEFT JOIN conf_agent_info cai ON ca.info_fk = cai.dbid
						LEFT JOIN conf_group cg ON la.groupdbid = cg.dbid
						LEFT JOIN conf_inbound cinb ON cinb.dbid = la.campaign_id
						LEFT JOIN conf_campaign coutb ON coutb.dbid = la.campaign_id
						LEFT JOIN conf_queue cque ON cque.dbid = la.queue_id
						LEFT JOIN conf_dialplan cdia ON cdia.dbid = la.dialplan_id
						LEFT JOIN log_agent_calltransfer lacallxfer ON la.tracknumber = lacallxfer.tracknumber
						LEFT JOIN log_call_disposition cdisp ON la.tracknumber = cdisp.tracknumber
						';
	  $query_count_tables = '
						FROM log_queue_record la
						';
						
	  $query_filters = '
						 WHERE 
						(la.queue_enter_time >= :stat_startdate_ext and la.queue_enter_time <= :stat_enddate_ext )
						';
	  	
	  $fieldvalues['stat_startdate_ext'] = $this->stat_startdate_ext;
	  $fieldvalues['stat_enddate_ext'] = $this->stat_enddate_ext;
	  if(!fonivaFilter::isCurrentUserSuperTenant())
	  {
		  $query_filters .= ' AND la.tenant_fk = :tenantdbid ';
		  $fieldvalues['tenantdbid'] = fonivaFilter::getCurrentUserTenantId();
	  }
	  
	  if($this->stat_queue != '')
	  {
		  $query_filters .= ' AND la.queue_id = :stateltsvalue ';
		  $fieldvalues['stateltsvalue'] = $this->stat_queue;
	  } else if(!fonivaFilter::canCurrentUserManageAllQueues())
	  {
			  $query_filters .= ' AND la.queue_id IN (SELECT queue_fk FROM conf_user_assignedqueue WHERE user_fk = :currentuserid) ';
			  $fieldvalues['currentuserid'] = fonivaFilter::getCurrentUser()->getDbid();
	  }
	  
	  if($this->stat_searchcalleridnum != null && $this->stat_searchcalleridnum != '')
	  {
		  $query_filters .= ' AND la.callernumber = :searchcalleridnum';
		  $fieldvalues['searchcalleridnum'] = $this->stat_searchcalleridnum;
	  }
	  
	  if($this->stat_searchcallednum != null && $this->stat_searchcallednum != '')
	  {
		  $query_filters .= ' AND la.callednumber = :searchcallednum';
		  $fieldvalues['searchcallednum'] = $this->stat_searchcallednum;
	  }
	  
	  if($this->stat_searchtracknumber != null && $this->stat_searchtracknumber != '')
	  {
		  $query_filters .= ' AND la.tracknumber = :searchtracknumber';
		  $fieldvalues['searchtracknumber'] = $this->stat_searchtracknumber;
	  }
	  
	  if($this->stat_searchcalldirection != null && $this->stat_searchcalldirection != '')
	  {
		  $query_filters .= ' AND la.is_inbound = :searchcalldirection';
		  $fieldvalues['searchcalldirection'] = ($this->stat_searchcalldirection == 'INBOUND') ? 'yes' : 'no';
	  }
	  
	  if($this->stat_searchfrominboundid != null && $this->stat_searchfrominboundid != '')
	  {
		  $query_filters .= ' AND la.campaign_id = :searchfrominboundid';
		  $fieldvalues['searchfrominboundid'] = $this->stat_searchfrominboundid;
	  } 
	  else if($this->stat_searchfromoutboundid != null && $this->stat_searchfromoutboundid != '')
	  {
		  $query_filters .= ' AND la.campaign_id = :searchfromoutboundid';
		  $fieldvalues['searchfromoutboundid'] = $this->stat_searchfromoutboundid;
	  }
	  
	  if($this->stat_disposition != null && $this->stat_disposition != '')
	  {
		  $query_filters .= ' AND la.queueresult = :searchdisposition';
		  $fieldvalues['searchdisposition'] = $this->stat_disposition;
	  }
	  
	  
	  $query_orderby = ' ORDER BY la.queue_enter_time, la.queue_id ';
	  
	  
	  $query = $query_fields.''.$queue_tables.''.$query_filters.''.$query_orderby;
	  if(!$this->csv_export)
	  	$query .= ' LIMIT '.(($this->getRequestParameter('page',1)-1)*sfConfig::get('app_display_nbrecordperpagereports')).','.sfConfig::get('app_display_nbrecordperpagereports');
	  
	  $query_count = $query_count_fields.''.$query_count_tables.''.$query_filters;
	
		//echo $query;
		//exit();
	  $query = $pdo->prepare($query);
	  $query_count = $pdo->prepare($query_count);
	  
	  
	  if(!$this->csv_export)
	  {
		  $query_count->execute($fieldvalues);
		  $countdata = $query_count->fetchAll();
		  $nbtotalrecordsfounded = $countdata[0]['nbtotalrecords'];
		  
		  $this->pager = new FonivaPager('LogCallRecord', sfConfig::get('app_display_nbrecordperpagereports'));
		  $this->pager->setPage($this->getRequestParameter('page',1));
		  $this->pager->assignNbResults($nbtotalrecordsfounded);
		  $this->pager->init();
		  
			  
		  $query->execute($fieldvalues);
		  $this->data = $query->fetchAll();
	  }
	  else
	  {
		  $query->execute($fieldvalues);
		  $this->data = $query->fetchAll();
	  }
	  	
	  
	  if($this->csv_export)
	  {
	    $csvfilename = 'foniva_report_' . $this->getActionName() . '.csv';
		$this->setlayout(false);
		$this->getResponse()->clearHttpHeaders();
		$this->getResponse()->setHttpHeader('Content-Type', 'application/vnd.ms-excel');
		$this->getResponse()->setHttpHeader('Content-Disposition', 'attachment; filename='.$csvfilename);
		return 'Csvexport';
	  }	
	  else
	  {		
		$this->getResponse()->addJavascript('simple.carousel.js', 'last');
		$this->getResponse()->addStylesheet('simple.carousel.css', 'last');		
	    $this->getResponse()->addJavascript('jquery.toggleswitch.min.js', 'last');
	    $this->getResponse()->addStylesheet('toggleswitch.css', 'last');
		$this->getResponse()->addJavascript('jplayer/jPlayer-2.9.2/jplayer/jquery.jplayer.min.js', 'last');
		$this->getResponse()->addStylesheet('/js/jplayer/jPlayer-2.9.2/skin/blue.monday/css/jplayer.blue.monday.css', 'last');
	  }
  }
  
  public function agentformparse(sfWebRequest $request)
  {	 
	$this->forward404Unless(($rarray =  $request->getParameter('report')));
	$this->forward404Unless(($rid = $rarray['report_id']));
	$this->report = Doctrine::getTable('ConfReport')->find($rid);
	$this->forward404Unless($this->report);
	
	$this->stat_group = isset($rarray['report_group']) ? $rarray['report_group'] : NULL;
	$this->stat_team = isset($rarray['report_team']) ? $rarray['report_team'] : NULL;
	$this->stat_site = isset($rarray['report_site']) ? $rarray['report_site'] : NULL;
	$this->stat_agent = isset($rarray['report_agent']) ? $rarray['report_agent'] : NULL;
	$this->csv_export = (isset($rarray['csv_export']) && ($rarray['csv_export'] == 'on' || $rarray['csv_export'] == '1')) ? true : false;
	$this->include_inactive_agents = (isset($rarray['inactiveagents']) && ($rarray['inactiveagents'] == 'on' || $rarray['inactiveagents'] == '1')) ? true : false;
	
	
	if(is_array($rarray['start_date']))
		$this->stat_startdate = $rarray['start_date']['year'].'-'.$rarray['start_date']['month'].'-'.$rarray['start_date']['day'];
	else
		$this->stat_startdate = $rarray['start_date'];
	
	if(is_array($rarray['end_date']))
		$this->stat_enddate = $rarray['end_date']['year'].'-'.$rarray['end_date']['month'].'-'.$rarray['end_date']['day'];
	else
		$this->stat_enddate = $rarray['end_date'];
	
	$this->stat_startdate_ext = $this->stat_startdate.' 00:00:00';
	$this->stat_enddate_ext   = $this->stat_enddate.' 23:59:59';	
			
	
	$this->stat_step = isset($rarray['step']) ? $rarray['step'] : NULL;
	
	$this->stat_aggregate = isset($rarray['aggregate']) ? $rarray['aggregate'] : NULL;
	
	$this->stat_searchcalleridnum = isset($rarray['calleridnum']) ? $rarray['calleridnum'] : NULL;
	$this->stat_searchcallednum = isset($rarray['callednum']) ? $rarray['callednum'] : NULL;
	$this->stat_searchtracknumber = isset($rarray['tracknumber']) ? $rarray['tracknumber'] : NULL;
	$this->stat_searchagenthangeduponly = isset($rarray['agenthangeduponly']) ? $rarray['agenthangeduponly'] : NULL;
  }
  
  public function buildagentreportform(sfWebRequest $request)
  {
	/* built defaults */
	$this->reportdefparams = array();
	
	$rarray =  $request->getParameter('report');
	if( isset($rarray['report_id']) )
		$this->reportdefparams['report_id'] = $rarray['report_id'];
		
	
	if( isset($rarray['report_group']) )
		$this->reportdefparams['report_group'] = $rarray['report_group'];
	if( isset($rarray['report_team']) )
		$this->reportdefparams['report_team'] = $rarray['report_team'];
	if( isset($rarray['report_site']) )
		$this->reportdefparams['report_site'] = $rarray['report_site'];
	if( isset($rarray['report_agent']) )
		$this->reportdefparams['report_agent'] = $rarray['report_agent'];		
	if( isset($rarray['start_date']) )
		$this->reportdefparams['start_date'] = $rarray['start_date'];
	else
		$this->reportdefparams['start_date'] = date('Y-m-d');
	if( isset($rarray['end_date']) )
		$this->reportdefparams['end_date'] = $rarray['end_date'];
	else
		$this->reportdefparams['end_date'] = date('Y-m-d');
	if( isset($rarray['date_range']) )
		$this->reportdefparams['date_range'] = $rarray['date_range'];
	else
		$this->reportdefparams['date_range'] = 'TODAY';
	if( isset($rarray['step']) )
		$this->reportdefparams['step'] = $rarray['step'];
	if( isset($rarray['aggregate']) )
		$this->reportdefparams['aggregate'] = $rarray['aggregate'];
	if( isset($rarray['csv_export']) )
		$this->reportdefparams['csv_export'] = $rarray['csv_export'];
	if( isset($rarray['calleridnum']) )
		$this->reportdefparams['calleridnum'] = $rarray['calleridnum'];
	if( isset($rarray['callednum']) )
		$this->reportdefparams['callednum'] = $rarray['callednum'];
	if( isset($rarray['tracknumber']) )
		$this->reportdefparams['tracknumber'] = $rarray['tracknumber'];
	if( isset($rarray['agenthangeduponly']) )
		$this->reportdefparams['agenthangeduponly'] = $rarray['agenthangeduponly'];
	
	
	$this->form = new ReportslistForm($this->reportdefparams);
	$this->form['report_id']->getWidget()->setOption('choices', $this->reports_r);
	
	
	$this->getResponse()->addStylesheet('chosen/chosen.css', 'last');
	$this->getResponse()->addJavascript('chosen/chosen.jquery.min.js', 'last'); 
	
	
	//$this->getResponse()->addJavascript('mediaelement-and-player.min.js', 'last');
	//$this->getResponse()->addStylesheet('mediaelementplayer.min.css', 'last');
	
	$this->getResponse()->addJavascript('jplayer/jPlayer-2.9.2/jplayer/jquery.jplayer.min.js', 'last');
	$this->getResponse()->addStylesheet('/js/jplayer/jPlayer-2.9.2/skin/blue.monday/css/jplayer.blue.monday.css', 'last');
  }
  
  public function getreports($reportcategory)
  {
	$this->reports_r = array();
	$reports = Doctrine_Query::create()
						->select('r.dbid, r.reportlabel, r.name, r.subcategory')
						->from('ConfReport r')
						->where('r.category = ?', $reportcategory)
						->orderBy('r.subcategory, r.reportlabel')
						->execute();
	
	foreach($reports as $r)
	{
		$curcategory = $r->subcategory;
		if(!isset($this->reports_r[$curcategory]))
			$this->reports_r[$curcategory] = array();
		$this->reports_r[$curcategory][$r->dbid] = $r->reportlabel;
	}
					
  }
  
  
  public function humanreadabletime($s)
  {
	$str = '';
	$h = floor($s/3600);
	$s = $s - $h*3600;
	$m = floor($s/60);
	$s = $s - $m*60;
	$str = ($h<10?'0':'').$h.':'.($m<10?'0':'').$m.':'.($s<10?'0':'').$s;	
	return $str;	  
  }  
  
  
  public function executeInboundcalls(sfWebRequest $request)
  {
	$this->reportcategory = 'INBOUND';
	$this->inboundformparse($request);
    $this->getreports('INBOUND');
    $this->buildinboundreportform($request);
	
	$this->tableclassname = 'LogResumeDailyCallRecord';
	if($this->stat_step == 'hourly')
		$this->tableclassname = 'LogResumeHourlyCallRecord';
	if($this->stat_step == 'monthly')
		$this->tableclassname = 'LogResumeMonthlyCallRecord';
	if($this->stat_step == 'weekly')
		$this->tableclassname = 'LogResumeWeeklyCallRecord';  
	if($this->stat_step == '15min')
		$this->tableclassname = 'LogResume15minCallRecord';  
	if($this->stat_step == '30min')
		$this->tableclassname = 'LogResume30minCallRecord';  
		
	
	$this->data = Doctrine_Query::create()
					->select('r.covereddate as covereddate, r.is_inbound, r.campaign_id as campaign_id, r.dialplan_type, r.callresult,
							 		sum(r.nbcalls) as data_nbcalls, sum(r.dialingtime) as data_dialingtime')					
					->from($this->tableclassname.' r')
					->where('r.covereddate >= ? and r.covereddate <= ? '.
								(($this->stat_inbound == '')?'':' and r.campaign_id = ?'), 
								($this->stat_inbound == '') ? array($this->stat_startdate_ext, $this->stat_enddate_ext) : array($this->stat_startdate_ext, $this->stat_enddate_ext, $this->stat_inbound)
  							)
					->andWhere('r.is_inbound = "yes"')
					->groupBy('r.covereddate, r.is_inbound, r.campaign_id, r.dialplan_type, r.callresult')
					->orderBy('r.is_inbound, r.campaign_id, r.covereddate, r.dialplan_type, r.callresult ');
					
	
    if(!fonivaFilter::isCurrentUserSuperTenant())
	  $this->data->andWhere('r.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
		   
	  if(!fonivaFilter::canCurrentUserManageAllInbounds())
	  {
		  $inbounds = fonivaFilter::currentUserManagedInbounds();
		  $inbounds_dbids = array();
		  foreach($inbounds as $is)
		  	array_push($inbounds_dbids, $is->dbid);
		  $this->data->andWhereIn('r.campaign_id', $inbounds_dbids);
	  }
		  

	if(!$this->csv_export)
    {
	  $this->pager = new sfDoctrinePager($this->tableclassname, sfConfig::get('app_display_nbrecordperpagereports'));
	  $this->pager->setPage($this->getRequestParameter('page',1));
	  $this->pager->setQuery($this->data);
	  $this->pager->init();
	  $this->data = $this->pager->getResults();
	}
	else
	  $this->data = $this->data->execute();	
	  			
	$this->datasum = Doctrine_Query::create()
					->select('r.covereddate as covereddate, r.campaign_id as campaign_id,
							 		sum(r.nbcalls) as datasum_nbcalls, sum(r.dialingtime) as datasum_dialingtime')					
					->from($this->tableclassname.' r')
					->where('r.covereddate >= ? and r.covereddate <= ?'.
								(($this->stat_inbound == '')?'':' and r.campaign_id = ?'), 
									($this->stat_inbound == '') ? array($this->stat_startdate_ext, $this->stat_enddate_ext) : array($this->stat_startdate_ext, $this->stat_enddate_ext, $this->stat_inbound)
								)
					->andWhere('r.is_inbound = "yes"')
					->groupBy('r.covereddate');
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$this->datasum->andWhere('r.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	$this->datasum = $this->datasum->execute();  
	
	
	//go and get list of elements info 
	$listids = array();
	foreach($this->data as $d)
	{ 
		$theid = $d->campaign_id;
		
		if(!isset($listids[$theid]))
			$listids[$theid] = $theid;
	}
	
	$infos = array();
	$this->eltinfos = array();
	$infos = Doctrine_Query::create()
				->select('a.dbid as dbid, a.inbound_name as campaign_name')
				->from('ConfInbound a')
				->wherein('a.dbid', array_values($listids))
				->execute();
	foreach($infos as $i)
		$this->eltinfos[$i->dbid] = $i;
		
	//echo sizeof($this->eltinfos);
	//exit();

	if($this->csv_export)
	{
	  $csvfilename = 'foniva_report_' . $this->getActionName() . '.csv';
	  $this->setlayout(false);
	  $this->getResponse()->clearHttpHeaders();
	  $this->getResponse()->setHttpHeader('Content-Type', 'application/vnd.ms-excel');
	  $this->getResponse()->setHttpHeader('Content-Disposition', 'attachment; filename='.$csvfilename);
	  return 'Csvexport'; /* use csv export template instead*/
	}
	else
  	{
	    $this->getResponse()->addJavascript('jquery.toggleswitch.min.js', 'last');
	    $this->getResponse()->addStylesheet('toggleswitch.css', 'last');	
	}
  }
  
  public function executeInboundcalldispositions(sfWebRequest $request)
  {
	$this->reportcategory = 'INBOUND';
	$this->inboundformparse($request);
    $this->getreports('INBOUND');
    $this->buildinboundreportform($request);
	
	
	$this->tableclassname = 'LogResumeDailyAgentCall';
	if($this->stat_step == 'hourly')
	{
		$this->query_groupby_dateformat = ' DATE_FORMAT(r.occured_at, "%Y-%m-%d %H:00:00") ';
	}
	if($this->stat_step == 'monthly')
	{
		$this->query_groupby_dateformat = ' DATE_FORMAT(r.occured_at, "%Y-%m-01 00:00:00") ';
	}
	if($this->stat_step == 'daily')
	{
		$this->query_groupby_dateformat = ' DATE_FORMAT(r.occured_at, "%Y-%m-%d 00:00:00") ';
	}
	if($this->stat_step == 'weekly')
	{
		$this->query_groupby_dateformat = ' DATE_FORMAT(r.occured_at, "%Y-%u 00:00:00") ';
	}
	if($this->stat_step == '15min')
	{
		$this->query_groupby_dateformat = ' DATE_FORMAT(r.occured_at, "%Y-%m-%d %H:00:00") ';
	}
	if($this->stat_step == '30min')
	{
		$this->query_groupby_dateformat = ' DATE_FORMAT(r.occured_at, "%Y-%m-%d %H:00:00") ';
	}
	
	$this->data = Doctrine_Query::create()
					->select($this->query_groupby_dateformat.' as occured_at, count(r.dbid) as nbelements,
							  r.calldisposition_id, r.calldisposition_name
							  ')					
					->from('LogCallDisposition r')
					->where('r.isinbound = "yes"')
					->andwhere('r.occured_at >= ? and r.occured_at <= ? ', array($this->stat_startdate_ext, $this->stat_enddate_ext));
	if($this->stat_inbound != '')
		$this->data->andWhere('r.campaign_id = ?', $this->stat_inbound);
		
	$this->data->groupBy($this->query_groupby_dateformat.', r.campaign_id, r.calldisposition_id ')
				->orderBy($this->query_groupby_dateformat.', r.campaign_id, r.calldisposition_id ');
				
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$this->data->andWhere('r.tenantdbid = ?', fonivaFilter::getCurrentUserTenantId());
	if(!$this->csv_export)
	{
		$this->pager = new sfDoctrinePager($this->tableclassname, sfConfig::get('app_display_nbrecordperpagereports'));
		$this->pager->setPage($this->getRequestParameter('page',1));
		$this->pager->setQuery($this->data);
		$this->pager->init();
		$this->data = $this->pager->getResults();
	}
	else
	{
		$this->data = $this->data->execute();
	}
	
	$infos = array();
	$this->eltinfos = array();

	$infos = Doctrine_Query::create()
				->select('a.dbid as dbid, a.inbound_name as name')
				->from('ConfInbound a');
	if($this->stat_inbound != '')
		$infos->andWhere('a.dbid = ?', $this->stat_inbound);
	if(!fonivaFilter::isCurrentUserSuperTenant())
	  $infos->andWhere('a.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	$infos = $infos->execute();
	
	foreach($infos as $i)
		$this->eltinfos[$i->dbid] = $i;
	
	if($this->csv_export)
	{
		$csvfilename = 'foniva_report_' . $this->getActionName() . '.csv';
		$this->setlayout(false);
		$this->getResponse()->clearHttpHeaders();
		$this->getResponse()->setHttpHeader('Content-Type', 'application/vnd.ms-excel');
		$this->getResponse()->setHttpHeader('Content-Disposition', 'attachment; filename='.$csvfilename);
		return 'Csvexport'; /* use csv export template instead*/
	}
	else
	{		
		$this->getResponse()->addJavascript('simple.carousel.js', 'last');
		$this->getResponse()->addStylesheet('simple.carousel.css', 'last');
		$this->getResponse()->addJavascript('jquery.toggleswitch.min.js', 'last');
		$this->getResponse()->addStylesheet('toggleswitch.css', 'last');	
	}
  }  
  
  
  public function executeInboundcallflowcounters(sfWebRequest $request)
  {
	$this->reportcategory = 'INBOUND';
	$this->inboundformparse($request);
    $this->getreports('INBOUND');
    $this->buildinboundreportform($request);
	
	$this->tableclassname = 'log_dialplan_counter_track';
	if($this->stat_step == 'hourly')
	{
		$this->query_groupby_dateformat = ' DATE_FORMAT(r.occureddate, "%Y-%m-%d %H:00:00") ';
	}
	if($this->stat_step == 'monthly')
	{
		$this->query_groupby_dateformat = ' DATE_FORMAT(r.occureddate, "%Y-%m-01 00:00:00") ';
	}
	if($this->stat_step == 'daily')
	{
		$this->query_groupby_dateformat = ' DATE_FORMAT(r.occureddate, "%Y-%m-%d 00:00:00") ';
	}
	if($this->stat_step == 'weekly')
	{
		$this->query_groupby_dateformat = ' DATE_FORMAT(r.occureddate, "%Y-%u 00:00:00") ';
	}
	if($this->stat_step == '15min')
	{
		$this->query_groupby_dateformat = ' DATE_FORMAT(r.occureddate, "%Y-%m-%d %H:00:00") ';
	}
	if($this->stat_step == '30min')
	{
		$this->query_groupby_dateformat = ' DATE_FORMAT(r.occureddate, "%Y-%m-%d %H:00:00") ';
	}
	
	$pdo = Doctrine_Manager::getInstance()->getCurrentConnection()->getDbh();
	
	$query = '';
	
	$query_fields = 'SELECT '.$this->query_groupby_dateformat.' as occureddate, r.trackname as trackname,
									a.tracknumber, a.callernumber, a.callednumber,
							 		count(r.dbid) as data_nbcalls ';
								
	$query_count_fields = 'SELECT COUNT(*) as nbtotalrecords ';
	
	$query_tables = '
					FROM '.$this->tableclassname.' r, log_call_record a
					';
	
	$query_count_tables = '
					FROM '.$this->tableclassname.' r, log_call_record a
					';
					
	$query_filters = '
				 WHERE 
				 r.tracknumber = a.tracknumber
				 AND
				(r.occureddate >= :stat_startdate_ext and r.occureddate <= :stat_enddate_ext ) 
				';
	  
	  $fieldvalues['stat_startdate_ext'] = $this->stat_startdate_ext;
	  $fieldvalues['stat_enddate_ext'] = $this->stat_enddate_ext;
	  
	  		
		if(!fonivaFilter::isCurrentUserSuperTenant())
		{
		  $query_filters .= ' AND a.tenant_fk = :tenantdbid';
		  $fieldvalues['tenantdbid'] = fonivaFilter::getCurrentUserTenantId();
		}
			  
	  if($this->stat_searchcalleridnum != null && $this->stat_searchcalleridnum != '')
	  {
		  $query_filters .= ' AND a.callernumber = :searchcalleridnum';
		  $fieldvalues['searchcalleridnum'] = $this->stat_searchcalleridnum;
	  }
	  
	  if($this->stat_searchcallednum != null && $this->stat_searchcallednum != '')
	  {
		  $query_filters .= ' AND a.callednumber = :searchcallednum';
		  $fieldvalues['searchcallednum'] = $this->stat_searchcallednum;
	  }
	  
	  if($this->stat_searchtracknumber != null && $this->stat_searchtracknumber != '')
	  {
		  $query_filters .= ' AND a.tracknumber = :searchtracknumber';
		  $fieldvalues['searchtracknumber'] = $this->stat_searchtracknumber;
	  }
	  
	 $query_groupby = ' group by '.$this->query_groupby_dateformat.', r.trackname';
	$query_orderby = ' order by '.$this->query_groupby_dateformat.', r.trackname';
	
	
	$query = $query_fields.''.$query_tables.''.$query_filters.''.$query_groupby.''.$query_orderby;
	$query_count = $query_count_fields.''.$query_count_tables.''.$query_filters.''.$query_groupby;
	
	
	if(!$this->csv_export)
		$query .= ' LIMIT '.(($this->getRequestParameter('page',1)-1)*sfConfig::get('app_display_nbrecordperpagereports')).','.sfConfig::get('app_display_nbrecordperpagereports');
	
	//echo $query;
	//exit();
	$query = $pdo->prepare($query);
	$query_count = $pdo->prepare($query_count);
	
	if(!$this->csv_export)
	{
	  $query_count->execute($fieldvalues);
	  $countdata = $query_count->fetchAll();
	  
	  $nbtotalrecordsfounded = sizeof($countdata); //[0]['nbtotalrecords'];
	  
	  $this->pager = new FonivaPager($this->tableclassname_doctrine, sfConfig::get('app_display_nbrecordperpagereports'));
	  $this->pager->setPage($this->getRequestParameter('page',1));
	  $this->pager->assignNbResults($nbtotalrecordsfounded);
	  $this->pager->init();
	  
		  
	  $query->execute($fieldvalues);
	  $this->data = $query->fetchAll();
	}
	else
	{
		  
	  $query->execute($fieldvalues);
	  $this->data = $query->fetchAll();
	}
	
	if($this->csv_export)
	{
	  $csvfilename = 'foniva_report_' . $this->getActionName() . '.csv';
	  $this->setlayout(false);
	  $this->getResponse()->clearHttpHeaders();
	  $this->getResponse()->setHttpHeader('Content-Type', 'application/vnd.ms-excel');
	  $this->getResponse()->setHttpHeader('Content-Disposition', 'attachment; filename='.$csvfilename);
	  return 'Csvexport'; /* use csv export template instead*/
	}
	else
  	{
	    $this->getResponse()->addJavascript('jquery.toggleswitch.min.js', 'last');
	    $this->getResponse()->addStylesheet('toggleswitch.css', 'last');	
	}
  }
  
  public function inboundformparse(sfWebRequest $request)
  {
	$this->forward404Unless(($rarray =  $request->getParameter('report')));
	$this->forward404Unless(($rid = $rarray['report_id']));	
	$this->report = Doctrine::getTable('ConfReport')->find($rid);
	$this->forward404Unless($this->report);  
	$this->stat_inbound = $rarray['report_inbound'];
	$this->csv_export = isset($rarray['csv_export']) && ($rarray['csv_export'] == 'on' || $rarray['csv_export'] == '1') ? true : false;
	
	$this->stat_searchcalleridnum = isset($rarray['calleridnum']) ? $rarray['calleridnum'] : NULL;
	$this->stat_searchcallednum = isset($rarray['callednum']) ? $rarray['callednum'] : NULL;
	$this->stat_searchtracknumber = isset($rarray['tracknumber']) ? $rarray['tracknumber'] : NULL;
	
	if(is_array($rarray['start_date']))
		$this->stat_startdate = $rarray['start_date']['year'].'-'.$rarray['start_date']['month'].'-'.$rarray['start_date']['day'];
	else
		$this->stat_startdate = $rarray['start_date'];
	
	if(is_array($rarray['end_date']))
		$this->stat_enddate = $rarray['end_date']['year'].'-'.$rarray['end_date']['month'].'-'.$rarray['end_date']['day'];
	else
		$this->stat_enddate = $rarray['end_date'];
			
	
	$this->stat_step = isset($rarray['step']) ? $rarray['step'] : NULL;
	
	$this->stat_startdate_ext = $this->stat_startdate.' 00:00:00';
	$this->stat_enddate_ext   = $this->stat_enddate.' 23:59:59';	 
  }
  
  public function buildinboundreportform(sfWebRequest $request)
  {
	/* built defaults */
	$this->reportdefparams = array();
	 $inbounds = Doctrine_Query::create()
						->select('i.dbid, i.inbound_name')
						->from('ConfInbound i')
						->orderBy('i.inbound_name');
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$inbounds->andWhere('i.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
		
	if(!fonivaFilter::canCurrentUserManageAllInbounds())
	{
		$inbounds->leftJoin('i.ConfUserAssignedinbound a')
				->andWhere('a.user_fk = ?', fonivaFilter::getCurrentUser()->getDbid());
	}
	
	$inbounds = $inbounds->execute();
	
	$fulllist = array();
	$fulllist[''] = "All Inbounds";
	foreach($inbounds as $i)
	{
		$fulllist[$i->dbid] = $i->inbound_name;
	}
	
	/* built defaults */
	$defparams = array();
	
	$rarray =  $request->getParameter('report');
	if( isset($rarray['report_id']) )
		$this->reportdefparams['report_id'] = $rarray['report_id'];
	if( isset($rarray['report_inbound']) )
		$this->reportdefparams['report_inbound'] = $rarray['report_inbound'];
	if( isset($rarray['start_date']) )
		$this->reportdefparams['start_date'] = $rarray['start_date'];
	else
		$this->reportdefparams['start_date'] = date('Y-m-d');
	if( isset($rarray['end_date']) )
		$this->reportdefparams['end_date'] = $rarray['end_date'];
	else
		$this->reportdefparams['end_date'] = date('Y-m-d');
	if( isset($rarray['date_range']) )
		$this->reportdefparams['date_range'] = $rarray['date_range'];
	else
		$this->reportdefparams['date_range'] = 'TODAY';
	if( isset($rarray['step']) )
		$this->reportdefparams['step'] = $rarray['step'];
	if( isset($rarray['csv_export']) )
		$this->reportdefparams['csv_export'] = $rarray['csv_export'];
	if( isset($rarray['calleridnum']) )
		$this->reportdefparams['calleridnum'] = $rarray['calleridnum'];
	if( isset($rarray['callednum']) )
		$this->reportdefparams['callednum'] = $rarray['callednum'];
	if( isset($rarray['tracknumber']) )
		$this->reportdefparams['tracknumber'] = $rarray['tracknumber'];
	
	
	$this->form = new ReportslistForm($this->reportdefparams);
	$this->form['report_id']->getWidget()->setOption('choices', $this->reports_r);

	
	$this->getResponse()->addStylesheet('chosen/chosen.css', 'last');
	$this->getResponse()->addJavascript('chosen/chosen.jquery.min.js', 'last');
	
	
	$this->getResponse()->addJavascript('mediaelement-and-player.min.js', 'last');
	$this->getResponse()->addStylesheet('mediaelementplayer.min.css', 'last');
  }
  
  public function executeOutboundcalls(sfWebRequest $request)
  {
	$this->reportcategory = 'OUTBOUND';	  
	$this->outboundformparse($request);
	$this->getreports('OUTBOUND');
	$this->buildoutboundreportform($request);
	
	$this->tableclassname = 'LogResumeDailyCallRecord';
	if($this->stat_step == 'hourly')
		$this->tableclassname = 'LogResumeHourlyCallRecord';
	if($this->stat_step == 'monthly')
		$this->tableclassname = 'LogResumeMonthlyCallRecord';
	if($this->stat_step == 'weekly')
		$this->tableclassname = 'LogResumeWeeklyCallRecord';  
	if($this->stat_step == '15min')
		$this->tableclassname = 'LogResume15minCallRecord';  
	if($this->stat_step == '30min')
		$this->tableclassname = 'LogResume30minCallRecord';  
		
	
	$this->data = Doctrine_Query::create()
					->select('r.covereddate as covereddate, r.is_inbound, r.campaign_id as campaign_id, r.dialplan_type, r.callresult,
							 		sum(r.nbcalls) as data_nbcalls, sum(r.dialingtime) as data_dialingtime')					
					->from($this->tableclassname.' r')
					->where('r.covereddate >= ? and r.covereddate <= ? '.
								(($this->stat_outbound == '')?'':' and r.campaign_id = ?'), 
								($this->stat_outbound == '') ? array($this->stat_startdate_ext, $this->stat_enddate_ext) : array($this->stat_startdate_ext, $this->stat_enddate_ext, $this->stat_outbound)
  							)
					->andWhere('r.is_inbound = "no"')
					->groupBy('r.covereddate, r.is_inbound, r.campaign_id, r.dialplan_type, r.callresult')
					->orderBy('r.is_inbound, r.campaign_id, r.covereddate, r.dialplan_type, r.callresult ');
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$this->data->andWhere('r.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	if(!$this->csv_export)
    {
	  $this->pager = new sfDoctrinePager($this->tableclassname, sfConfig::get('app_display_nbrecordperpagereports'));
	  $this->pager->setPage($this->getRequestParameter('page',1));
	  $this->pager->setQuery($this->data);
	  $this->pager->init();
	  $this->data = $this->pager->getResults();
	}
	else
	  $this->data = $this->data->execute();
	
	$infos = array();
	$this->eltinfos = array();
	$infos = Doctrine_Query::create()
				->select('o.dbid as dbid, o.campaign_name as campaign_name')
				->from('ConfCampaign o');
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$infos->andWhere('o.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	$infos = $infos->execute();
	foreach($infos as $i)
		$this->eltinfos[$i->dbid] = $i;
		
	if($this->csv_export)
	{
	  $csvfilename = 'foniva_report_' . $this->getActionName() . '.csv';
	  $this->setlayout(false);
	  $this->getResponse()->clearHttpHeaders();
	  $this->getResponse()->setHttpHeader('Content-Type', 'application/vnd.ms-excel');
	  $this->getResponse()->setHttpHeader('Content-Disposition', 'attachment; filename='.$csvfilename);
	  return 'Csvexport'; /* use csv export template instead*/
	}
	else
	{		
	  $this->getResponse()->addJavascript('jquery.toggleswitch.min.js', 'last');
	  $this->getResponse()->addStylesheet('toggleswitch.css', 'last');	
    }
  }
  
  public function executeOutbounddialedcalls(sfWebRequest $request)
  {
	$this->reportcategory = 'OUTBOUND';	  
	$this->outboundformparse($request);
	$this->getreports('OUTBOUND');
	$this->buildoutboundreportform($request);
	
	$this->tableclassname = 'LogResumeDailyDialing';
	if($this->stat_step == 'hourly')
		$this->tableclassname = 'LogResumeHourlyDialing';
	if($this->stat_step == 'monthly')
		$this->tableclassname = 'LogResumeMonthlyDialing';
	if($this->stat_step == 'weekly')
		$this->tableclassname = 'LogResumeWeeklyDialing';  
	if($this->stat_step == '15min')
		$this->tableclassname = 'LogResume15minDialing';  
	if($this->stat_step == '30min')
		$this->tableclassname = 'LogResume30minDialing';  
		
	
	$this->data = Doctrine_Query::create()
					->select('r.covereddate as covereddate, r.campaign_dbid as campaign_dbid, r.callinglist_id as callinglist_id, r.event as event,
							 		sum(r.nbcalls) as data_nbcalls, sum(r.total_dialing_seconds) as data_total_dialing_seconds, sum(r.total_billable_seconds) as data_total_billable_seconds')					
					->from($this->tableclassname.' r')
					->where('r.covereddate >= ? and r.covereddate <= ? '.
								(($this->stat_outbound == '')?'':' and r.campaign_dbid = ?'), 
								($this->stat_outbound == '') ? array($this->stat_startdate_ext, $this->stat_enddate_ext) : array($this->stat_startdate_ext, $this->stat_enddate_ext, $this->stat_outbound)
  							)
					->groupBy('r.covereddate, r.campaign_dbid, r.callinglist_id, r.event')
					->orderBy('r.campaign_dbid, r.callinglist_id, r.covereddate, r.event');
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$this->data->andWhere('r.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	if(!$this->csv_export)
    {
	  $this->pager = new sfDoctrinePager($this->tableclassname, sfConfig::get('app_display_nbrecordperpagereports'));
	  $this->pager->setPage($this->getRequestParameter('page',1));
	  $this->pager->setQuery($this->data);
	  $this->pager->init();
	  $this->data = $this->pager->getResults();
	}
	else
	  $this->data = $this->data->execute();
	
	$infos = array();
	$this->eltinfos = array();
	$infos = Doctrine_Query::create()
				->select('o.dbid as dbid, o.campaign_name as campaign_name')
				->from('ConfCampaign o');
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$infos->andWhere('o.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	$infos = $infos->execute();
	foreach($infos as $i)
		$this->eltinfos[$i->dbid] = $i;
	
	$infos = array();
	$this->eltlistinfos = array();
	$infos = Doctrine_Query::create()
				->select('o.dbid as dbid, o.calling_list_name as calling_list_name')
				->from('ConfCallingList o, ConfCampaign c')
				->andwhere('o.campaign_dbid = c.dbid');
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$infos->andWhere('c.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	$infos = $infos->execute();
	foreach($infos as $i)
		$this->eltlistinfos[$i->dbid] = $i;
		
	if($this->csv_export)
	{
	  $csvfilename = 'foniva_report_' . $this->getActionName() . '.csv';
	  $this->setlayout(false);
	  $this->getResponse()->clearHttpHeaders();
	  $this->getResponse()->setHttpHeader('Content-Type', 'application/vnd.ms-excel');
	  $this->getResponse()->setHttpHeader('Content-Disposition', 'attachment; filename='.$csvfilename);
	  return 'Csvexport'; /* use csv export template instead*/
	}
	else
	{		
	  $this->getResponse()->addJavascript('jquery.toggleswitch.min.js', 'last');
	  $this->getResponse()->addStylesheet('toggleswitch.css', 'last');	
    }
  }
  
  public function executeOutboundcalldispositions(sfWebRequest $request)
  {
	$this->reportcategory = 'OUTBOUND';	  
	$this->outboundformparse($request);
	$this->getreports('OUTBOUND');
	$this->buildoutboundreportform($request);
	
	
	$this->tableclassname = 'LogResumeDailyAgentCall';
	if($this->stat_step == 'hourly')
	{
		$this->query_groupby_dateformat = ' DATE_FORMAT(r.occured_at, "%Y-%m-%d %H:00:00") ';
	}
	if($this->stat_step == 'monthly')
	{
		$this->query_groupby_dateformat = ' DATE_FORMAT(r.occured_at, "%Y-%m-01 00:00:00") ';
	}
	if($this->stat_step == 'daily')
	{
		$this->query_groupby_dateformat = ' DATE_FORMAT(r.occured_at, "%Y-%m-%d 00:00:00") ';
	}
	if($this->stat_step == 'weekly')
	{
		$this->query_groupby_dateformat = ' DATE_FORMAT(r.occured_at, "%Y-%u 00:00:00") ';
	}
	if($this->stat_step == '15min')
	{
		$this->query_groupby_dateformat = ' DATE_FORMAT(r.occured_at, "%Y-%m-%d %H:00:00") ';
	}
	if($this->stat_step == '30min')
	{
		$this->query_groupby_dateformat = ' DATE_FORMAT(r.occured_at, "%Y-%m-%d %H:00:00") ';
	}
	
	$this->data = Doctrine_Query::create()
					->select($this->query_groupby_dateformat.' as occured_at, count(r.dbid) as nbelements,
							  r.calldisposition_id, r.calldisposition_name
							  ')					
					->from('LogCallDisposition r')
					->where('r.isinbound = "no" and r.ismanual = "no"')
					->andwhere('r.occured_at >= ? and r.occured_at <= ? ', array($this->stat_startdate_ext, $this->stat_enddate_ext));
	if($this->stat_outbound != '')
		$this->data->andWhere('r.campaign_id = ?', $this->stat_outbound);
		
	$this->data->groupBy($this->query_groupby_dateformat.', r.campaign_id, r.calldisposition_id ')
				->orderBy($this->query_groupby_dateformat.', r.campaign_id, r.calldisposition_id ');
				
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$this->data->andWhere('r.tenantdbid = ?', fonivaFilter::getCurrentUserTenantId());
	if(!$this->csv_export)
	{
		$this->pager = new sfDoctrinePager($this->tableclassname, sfConfig::get('app_display_nbrecordperpagereports'));
		$this->pager->setPage($this->getRequestParameter('page',1));
		$this->pager->setQuery($this->data);
		$this->pager->init();
		$this->data = $this->pager->getResults();
	}
	else
	{
		$this->data = $this->data->execute();
	}
	
	$infos = array();
	$this->eltinfos = array();
	$infos = Doctrine_Query::create()
				->select('o.dbid as dbid, o.campaign_name as campaign_name')
				->from('ConfCampaign o')
				->where('1');
    if($this->stat_outbound != '')
                $infos->andWhere('o.dbid = ?', $this->stat_outbound);
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$infos->andWhere('o.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	$infos = $infos->execute();
	foreach($infos as $i)
		$this->eltinfos[$i->dbid] = $i;
	
	if($this->csv_export)
	{
		$csvfilename = 'foniva_report_' . $this->getActionName() . '.csv';
		$this->setlayout(false);
		$this->getResponse()->clearHttpHeaders();
		$this->getResponse()->setHttpHeader('Content-Type', 'application/vnd.ms-excel');
		$this->getResponse()->setHttpHeader('Content-Disposition', 'attachment; filename='.$csvfilename);
		return 'Csvexport'; /* use csv export template instead*/
	}
	else
	{		
		$this->getResponse()->addJavascript('simple.carousel.js', 'last');
		$this->getResponse()->addStylesheet('simple.carousel.css', 'last');
		$this->getResponse()->addJavascript('jquery.toggleswitch.min.js', 'last');
		$this->getResponse()->addStylesheet('toggleswitch.css', 'last');	
	}
  }
  
  public function outboundformparse(sfWebRequest $request)
  {
	$this->forward404Unless(($rarray =  $request->getParameter('report')));
	$this->forward404Unless(($rid = $rarray['report_id']));
	$this->report = Doctrine::getTable('ConfReport')->find($rid);
	$this->forward404Unless($this->report);  
	$this->stat_outbound = $rarray['report_outbound'];
	$this->stat_callinglist = isset($rarray['report_callinglist']) ? $rarray['report_callinglist'] : '';
	$this->csv_export = isset($rarray['csv_export']) && ($rarray['csv_export'] == 'on' || $rarray['csv_export'] == '1') ? true : false;
	
	$this->stat_searchcalleridnum = isset($rarray['calleridnum']) ? $rarray['calleridnum'] : NULL;
	$this->stat_searchcallednum = isset($rarray['callednum']) ? $rarray['callednum'] : NULL;
	$this->stat_searchtracknumber = isset($rarray['tracknumber']) ? $rarray['tracknumber'] : NULL;
	
	if(is_array($rarray['start_date']))
		$this->stat_startdate = $rarray['start_date']['year'].'-'.$rarray['start_date']['month'].'-'.$rarray['start_date']['day'];
	else
		$this->stat_startdate = $rarray['start_date'];
	
	if(is_array($rarray['end_date']))
		$this->stat_enddate = $rarray['end_date']['year'].'-'.$rarray['end_date']['month'].'-'.$rarray['end_date']['day'];
	else
		$this->stat_enddate = $rarray['end_date'];
			
	
	$this->stat_step = isset($rarray['step']) ? $rarray['step'] : NULL;
	
	$this->stat_startdate_ext = $this->stat_startdate.' 00:00:00';
	$this->stat_enddate_ext   = $this->stat_enddate.' 23:59:59';	  
  }
  
  public function buildoutboundreportform(sfWebRequest $request)
  {
	$outbounds = Doctrine_Query::create()
						->select('o.dbid, o.campaign_name')
						->from('ConfCampaign o')
						->orderBy('o.campaign_name');
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$outbounds->andWhere('o.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
		
	if(!fonivaFilter::canCurrentUserManageAllOutbounds())
	{
		$outbounds->leftJoin('o.ConfUserAssignedoutbound a')
				->andWhere('a.user_fk = ?', fonivaFilter::getCurrentUser()->getDbid());
	}
	
	$outbounds = $outbounds->execute();
	
	$fulllist = array();
	$fulllist[''] = "All Campaigns";
	foreach($outbounds as $o)
	{
		$fulllist[$o->dbid] = $o->campaign_name;
	}
	
	/* built defaults */
	$this->reportdefparams = array();
	
	$rarray =  $request->getParameter('report');
	if( isset($rarray['report_id']) )
		$this->reportdefparams['report_id'] = $rarray['report_id'];
	if( isset($rarray['report_outbound']) )
		$this->reportdefparams['start_date'] = date('Y-m-d');
	if( isset($rarray['end_date']) )
		$this->reportdefparams['end_date'] = $rarray['end_date'];
	else
		$this->reportdefparams['end_date'] = date('Y-m-d');
	if( isset($rarray['date_range']) )
		$this->reportdefparams['date_range'] = $rarray['date_range'];
	else
		$this->reportdefparams['report_outbound'] = $rarray['report_outbound'];
	if( isset($rarray['start_date']) )
		$this->reportdefparams['start_date'] = $rarray['start_date'];
	else
		$this->reportdefparams['date_range'] = 'TODAY';
	if( isset($rarray['step']) )
		$this->reportdefparams['step'] = $rarray['step'];
	if( isset($rarray['csv_export']) )
		$this->reportdefparams['csv_export'] = $rarray['csv_export'];
	if( isset($rarray['calleridnum']) )
		$this->reportdefparams['calleridnum'] = $rarray['calleridnum'];
	if( isset($rarray['callednum']) )
		$this->reportdefparams['callednum'] = $rarray['callednum'];
	if( isset($rarray['tracknumber']) )
		$this->reportdefparams['tracknumber'] = $rarray['tracknumber'];
	
	
	$this->form = new ReportslistForm($this->reportdefparams);
	$this->form['report_id']->getWidget()->setOption('choices', $this->reports_r);
	
	
	$this->getResponse()->addStylesheet('chosen/chosen.css', 'last');
	$this->getResponse()->addJavascript('chosen/chosen.jquery.min.js', 'last'); 
	
	
	$this->getResponse()->addJavascript('mediaelement-and-player.min.js', 'last');
	$this->getResponse()->addStylesheet('mediaelementplayer.min.css', 'last');
  }
  
  public function executeQueuecalls(sfWebRequest $request)
  {
	$this->reportcategory = 'QUEUE';
	$this->queueformparse($request);	
	$this->getreports('QUEUE');
	$this->buildqueuereportform($request);
	
	$this->tableclassname = 'LogResumeDailyQueueRecord';
	if($this->stat_step == 'hourly')
		$this->tableclassname = 'LogResumeHourlyQueueRecord';  
	if($this->stat_step == 'monthly')
		$this->tableclassname = 'LogResumeMonthlyQueueRecord';  
	if($this->stat_step == 'weekly')
		$this->tableclassname = 'LogResumeWeeklyQueueRecord';   
	if($this->stat_step == '15min')
		$this->tableclassname = 'LogResume15minQueueRecord';   
	if($this->stat_step == '30min')
		$this->tableclassname = 'LogResume30minQueueRecord';  
		
	
	$this->seriesdata = Doctrine_Query::create()
					->select('r.covereddate as covereddate, r.queue_id as queue_id, r.queueresult as queueresult, r.is_inbound as is_inbound, r.campaign_id as campaign_id, r.dialplan_id as dialplan_id, r.dialplan_type as dialplan_type,
							 		sum(r.nbcalls) as sum_seriesdatanbcalls, sum(r.totalqueueseconds) as sum_seriesdatatotalqueueseconds, sum(r.totalbridgeseconds) as sum_seriesdatatotalbridgeseconds')					
					->from($this->tableclassname.' r')
					->where('r.covereddate >= ? and r.covereddate <= ? '.
								(($this->stat_queue == '')?'':' and r.queue_id = ?'), 
								($this->stat_queue == '') ? array($this->stat_startdate_ext, $this->stat_enddate_ext) : array($this->stat_startdate_ext, $this->stat_enddate_ext, $this->stat_queue)
  							)
					->groupBy('r.covereddate, r.queue_id, r.queueresult')
					->orderBy('r.queue_id, r.covereddate, r.queueresult');
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$this->seriesdata->andWhere('r.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	
  
	if($this->stat_searchcalldirection != null && $this->stat_searchcalldirection != '')
		$this->seriesdata->andWhere('r.is_inbound = ?', (($this->stat_searchcalldirection == 'INBOUND') ? 'yes' : 'no'));
	
	if($this->stat_searchfrominboundid != null && $this->stat_searchfrominboundid != '')
		$this->seriesdata->andWhere('r.campaign_id = ?', $this->stat_searchfrominboundid);
	else if($this->stat_searchfromoutboundid != null && $this->stat_searchfromoutboundid != '')
		$this->seriesdata->andWhere('r.campaign_id = ?', $this->stat_searchfromoutboundid);
  
	if(!$this->csv_export)
	{
	  $this->pager = new sfDoctrinePager($this->tableclassname, sfConfig::get('app_display_nbrecordperpagereports'));
	  $this->pager->setPage($this->getRequestParameter('page',1));
	  $this->pager->setQuery($this->seriesdata);
	  $this->pager->init();
	  $this->seriesdata = $this->pager->getResults();
	}
	else
	  $this->seriesdata = $this->seriesdata->execute();
	
	$this->datasum = Doctrine_Query::create()
					->select('r.covereddate as covereddate, r.queue_id as queue_id,
							 		sum(r.nbcalls) as sum_datasumnbcalls, sum(r.totalqueueseconds) as sum_datasumtotalqueueseconds, sum(r.totalbridgeseconds) as sum_datasumtotalbridgeseconds')
					->from($this->tableclassname.' r')
					->where('r.covereddate >= ? and r.covereddate <= ?'.
								(($this->stat_queue == '')?'':' and r.queue_id = ?'), 
									($this->stat_queue == '') ? array($this->stat_startdate_ext, $this->stat_enddate_ext) : array($this->stat_startdate_ext, $this->stat_enddate_ext, $this->stat_queue)
								)
					->groupBy('r.covereddate, r.queue_id');
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$this->datasum->andWhere('r.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	
  
	if($this->stat_searchcalldirection != null && $this->stat_searchcalldirection != '')
		$this->datasum->andWhere('r.is_inbound = ?', (($this->stat_searchcalldirection == 'INBOUND') ? 'yes' : 'no'));
	if($this->stat_searchfrominboundid != null && $this->stat_searchfrominboundid != '')
		$this->datasum->andWhere('r.campaign_id = ?', $this->stat_searchfrominboundid);
	else if($this->stat_searchfromoutboundid != null && $this->stat_searchfromoutboundid != '')
		$this->datasum->andWhere('r.campaign_id = ?', $this->stat_searchfromoutboundid);
		
	$this->datasum = $this->datasum->execute();
					
	$this->datasum2 = Doctrine_Query::create()
					->select('r.covereddate as covereddate, r.queue_id as queue_id, r.queueresult as queueresult,
							 		sum(r.nbcalls) as sum_datasum2nbcalls, sum(r.totalqueueseconds) as sum_datasum2totalqueueseconds, sum(r.totalbridgeseconds) as sum_datasum2totalbridgeseconds')
					->from($this->tableclassname.' r')
					->where('r.covereddate >= ? and r.covereddate <= ?'.
								(($this->stat_queue == '')?'':' and r.queue_id = ?'), 
									($this->stat_queue == '') ? array($this->stat_startdate_ext, $this->stat_enddate_ext) : array($this->stat_startdate_ext, $this->stat_enddate_ext, $this->stat_queue)
								)
					->groupBy('r.covereddate, r.queueresult');
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$this->datasum2->andWhere('r.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	$this->datasum2 = $this->datasum2->execute();
	
	
	//go and get list of elements info 
	$listids = array();
	foreach($this->datasum as $d)
	{ 
		$theid = $d->queue_id;
		
		if(!isset($listids[$theid]))
			$listids[$theid] = $theid;
	}
	
	$infos = array();
	$this->eltinfos = array();
	$infos = Doctrine_Query::create()
				->select('q.dbid as dbid, q.name as queue_name')
				->from('ConfQueue q')
				->wherein('q.dbid', array_values($listids));
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$infos->andWhere('q.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	$infos = $infos->execute();
	foreach($infos as $i)
		$this->eltinfos[$i->dbid] = $i;
	
	if($this->csv_export)
    {
	  $csvfilename = 'foniva_report_' . $this->getActionName() . '.csv';
	  $this->setlayout(false);
	  $this->getResponse()->clearHttpHeaders();
	  $this->getResponse()->setHttpHeader('Content-Type', 'application/vnd.ms-excel');
	  $this->getResponse()->setHttpHeader('Content-Disposition', 'attachment; filename='.$csvfilename);
	  return 'Csvexport'; 
    }
	else
	{	  	
	  $this->getResponse()->addJavascript('jquery.toggleswitch.min.js', 'last');
	  $this->getResponse()->addStylesheet('toggleswitch.css', 'last');	
	}
	
  }
  
  public function executeQueueservicelevelsummary(sfWebRequest $request)
  {
	$this->reportcategory = 'QUEUE';
	$this->queueformparse($request);	
	$this->getreports('QUEUE');
	$this->buildqueuereportform($request);
	
	
	$this->tableclassname = 'log_resume_daily_queue_record';
	$this->tableclassname_doctrine = 'LogResumeDailyQueueRecord';
	if($this->stat_step == 'hourly')
	{
		$this->tableclassname = 'log_resume_hourly_queue_record';
		$this->tableclassname_doctrine = 'LogResumeHourlyQueueRecord';
	}
	if($this->stat_step == 'monthly')
	{
		$this->tableclassname = 'log_resume_monthly_queue_record';
		$this->tableclassname_doctrine = 'LogResumeMonthlyQueueRecord';
	}
	if($this->stat_step == 'weekly')
	{
		$this->tableclassname = 'log_resume_weekly_queue_record';
		$this->tableclassname_doctrine = 'LogResumeWeeklyQueueRecord';
	}
	if($this->stat_step == '15min')
	{
		$this->tableclassname = 'log_resume_15min_queue_record';
		$this->tableclassname_doctrine = 'LogResume15minQueueRecord';
	}
	if($this->stat_step == '30min')
	{
		$this->tableclassname = 'log_resume_30min_queue_record';
		$this->tableclassname_doctrine = 'LogResume30minQueueRecord';
	}
	
	$fieldvalues = array();
	$pdo = Doctrine_Manager::getInstance()->getCurrentConnection()->getDbh();
	
	$query = '';
	
	$query_fields = 'SELECT r.covereddate as covereddate, ';
	$query_fields.=	$this->stat_queue != '' ? 'r.queue_id as queue_id, ' : '';
	
	$query_fields.= '
					SUM(r.totalbridgeseconds) as totalbridgeseconds,
					SUM(IF(r.in_servicelevel = "yes" and r.queueresult = "DISTRIBUTED",r.nbcalls,0)) as total_in_servicelevel,
								SUM(IF(r.in_servicelevel = "yes" and r.queueresult = "DISTRIBUTED",0,r.nbcalls)) as total_not_in_servicelevel ';
	$query_fields.= $this->stat_queue != '' ? '
								, q.name as queuename
								' : '';
								
	$query_count_fields = 'SELECT r.covereddate ';
	
	$query_tables = '
					FROM '.$this->tableclassname.' r ';
	$query_tables .= $this->stat_queue != '' ? '
					LEFT JOIN conf_queue q ON (r.queue_id = q.dbid)
					' : '';
	
	$query_count_tables = '
					FROM '.$this->tableclassname.' r
					';
	
	$query_filters = '
				 WHERE 
				(r.covereddate >= :stat_startdate_ext and r.covereddate <= :stat_enddate_ext ) 
				';
	
	$fieldvalues['stat_startdate_ext'] = $this->stat_startdate_ext;
	$fieldvalues['stat_enddate_ext'] = $this->stat_enddate_ext;		
	
	if(!fonivaFilter::isCurrentUserSuperTenant())
	{
	  $query_filters .= ' AND r.tenant_fk = :tenantdbid';
	  $fieldvalues['tenantdbid'] = fonivaFilter::getCurrentUserTenantId();
	}
	
	if($this->stat_queue != '')
	{
	  $query_filters .= ' AND r.queue_id = :stateltsvalue';
	  $fieldvalues['stateltsvalue'] = $this->stat_queue;
	}
	  
	if($this->stat_searchcalldirection != null && $this->stat_searchcalldirection != '')
	{
		$query_filters .= ' AND r.is_inbound = :searchcalldirection';
		$fieldvalues['searchcalldirection'] = ($this->stat_searchcalldirection == 'INBOUND') ? 'yes' : 'no';
	}
	
	if($this->stat_searchfrominboundid != null && $this->stat_searchfrominboundid != '')
	{
		$query_filters .= ' AND r.campaign_id = :searchfrominboundid';
		$fieldvalues['searchfrominboundid'] = $this->stat_searchfrominboundid;
	} 
	else if($this->stat_searchfromoutboundid != null && $this->stat_searchfromoutboundid != '')
	{
		$query_filters .= ' AND r.campaign_id = :searchfromoutboundid';
		$fieldvalues['searchfromoutboundid'] = $this->stat_searchfromoutboundid;
	}
	
	$query_groupby = ' group by r.covereddate';
	$query_orderby = ' order by r.covereddate';
	
	$query = $query_fields.''.$query_tables.''.$query_filters.''.$query_groupby.''.$query_orderby;
	
	if(!$this->csv_export)
		$query .= ' LIMIT '.(($this->getRequestParameter('page',1)-1)*sfConfig::get('app_display_nbrecordperpagereports')).','.sfConfig::get('app_display_nbrecordperpagereports');
	
	$query_count = $query_count_fields.''.$query_count_tables.''.$query_filters.''.$query_groupby;
	
	
	$query = $pdo->prepare($query);
	$query_count = $pdo->prepare($query_count);
	
	if(!$this->csv_export)
	{
	  $query_count->execute($fieldvalues);
	  $countdata = $query_count->fetchAll();
	  
	  $nbtotalrecordsfounded = sizeof($countdata); //[0]['nbtotalrecords'];
	  
	  $this->pager = new FonivaPager($this->tableclassname_doctrine, sfConfig::get('app_display_nbrecordperpagereports'));
	  $this->pager->setPage($this->getRequestParameter('page',1));
	  $this->pager->assignNbResults($nbtotalrecordsfounded);
	  $this->pager->init();
	  
		  
	  $query->execute($fieldvalues);
	  $this->data = $query->fetchAll();
	}
	else
	{
		  
	  $query->execute($fieldvalues);
	  $this->data = $query->fetchAll();
	}
	
	
	if($this->csv_export)
	{
		$csvfilename = 'foniva_report_' . $this->getActionName() . '.csv';
		$this->setlayout(false);
		$this->getResponse()->clearHttpHeaders();
		$this->getResponse()->setHttpHeader('Content-Type', 'application/vnd.ms-excel');
		$this->getResponse()->setHttpHeader('Content-Disposition', 'attachment; filename='.$csvfilename);
		return 'Csvexport';
	}
	else
	{
		$this->getResponse()->addJavascript('chart.min.js', 'last');
		$this->getResponse()->addJavascript('Chart.MultiAxisLine.js', 'last');	
	    $this->getResponse()->addJavascript('jquery.toggleswitch.min.js', 'last');
	    $this->getResponse()->addStylesheet('toggleswitch.css', 'last');
	}
  }
  
  public function executeQueuecalldispositions(sfWebRequest $request)
  {
	$this->reportcategory = 'QUEUE';
	$this->queueformparse($request);	
	$this->getreports('QUEUE');
	$this->buildqueuereportform($request);
	
	$this->tableclassname = 'LogResumeDailyAgentCall';
	if($this->stat_step == 'hourly')
	{
		$this->query_groupby_dateformat = ' DATE_FORMAT(r.occured_at, "%Y-%m-%d %H:00:00") ';
	}
	if($this->stat_step == 'monthly')
	{
		$this->query_groupby_dateformat = ' DATE_FORMAT(r.occured_at, "%Y-%m-01 00:00:00") ';
	}
	if($this->stat_step == 'daily')
	{
		$this->query_groupby_dateformat = ' DATE_FORMAT(r.occured_at, "%Y-%m-%d 00:00:00") ';
	}
	if($this->stat_step == 'weekly')
	{
		$this->query_groupby_dateformat = ' DATE_FORMAT(r.occured_at, "%Y-%u 00:00:00") ';
	}
	if($this->stat_step == '15min')
	{
		$this->query_groupby_dateformat = ' DATE_FORMAT(r.occured_at, "%Y-%m-%d %H:00:00") ';
	}
	if($this->stat_step == '30min')
	{
		$this->query_groupby_dateformat = ' DATE_FORMAT(r.occured_at, "%Y-%m-%d %H:00:00") ';
	}
	
	$this->data = Doctrine_Query::create()
					->select($this->query_groupby_dateformat.' as occured_at, count(r.dbid) as nbelements,
							  r.calldisposition_id, r.calldisposition_name
							  ')					
					->from('LogCallDisposition r')
					->where('r.ismanual = "no"')
					->andwhere('r.occured_at >= ? and r.occured_at <= ? ', array($this->stat_startdate_ext, $this->stat_enddate_ext));
	if($this->stat_queue != '')
		$this->data->andWhere('r.queue_id = ?', $this->stat_queue);
		
	$this->data->groupBy($this->query_groupby_dateformat.', r.queue_id, r.calldisposition_id ')
				->orderBy($this->query_groupby_dateformat.', r.queue_id, r.calldisposition_id ');
				
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$this->data->andWhere('r.tenantdbid = ?', fonivaFilter::getCurrentUserTenantId());
	if(!$this->csv_export)
	{
		$this->pager = new sfDoctrinePager($this->tableclassname, sfConfig::get('app_display_nbrecordperpagereports'));
		$this->pager->setPage($this->getRequestParameter('page',1));
		$this->pager->setQuery($this->data);
		$this->pager->init();
		$this->data = $this->pager->getResults();
	}
	else
	{
		$this->data = $this->data->execute();
	}
	
	$infos = array();
	$this->eltinfos = array();
	$infos = Doctrine_Query::create()
				->select('q.dbid as dbid, q.name as name')
				->from('ConfQueue q')
				->where('1');
	if($this->stat_queue != '')
				$infos->andWhere('q.dbid = ?', $this->stat_queue);
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$infos->andWhere('q.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	$infos = $infos->execute();
	foreach($infos as $i)
		$this->eltinfos[$i->dbid] = $i;
	
	if($this->csv_export)
	{
		$csvfilename = 'foniva_report_' . $this->getActionName() . '.csv';
		$this->setlayout(false);
		$this->getResponse()->clearHttpHeaders();
		$this->getResponse()->setHttpHeader('Content-Type', 'application/vnd.ms-excel');
		$this->getResponse()->setHttpHeader('Content-Disposition', 'attachment; filename='.$csvfilename);
		return 'Csvexport'; /* use csv export template instead*/
	}
	else
	{		
		$this->getResponse()->addJavascript('simple.carousel.js', 'last');
		$this->getResponse()->addStylesheet('simple.carousel.css', 'last');
		$this->getResponse()->addJavascript('jquery.toggleswitch.min.js', 'last');
		$this->getResponse()->addStylesheet('toggleswitch.css', 'last');	
	}
  }
  
  public function queueformparse(sfWebRequest $request)
  {
	$this->forward404Unless(($rarray =  $request->getParameter('report')));
	$this->forward404Unless(($rid = $rarray['report_id']));
	$this->report = Doctrine::getTable('ConfReport')->find($rid);
	$this->forward404Unless($this->report);  
	$this->stat_queue = $rarray['report_queue'];
	$this->csv_export = isset($rarray['csv_export']) && ($rarray['csv_export'] == 'on' || $rarray['csv_export'] == '1') ? true : false;
	
	$this->stat_searchcalleridnum = isset($rarray['calleridnum']) ? $rarray['calleridnum'] : NULL;
	$this->stat_searchcallednum = isset($rarray['callednum']) ? $rarray['callednum'] : NULL;
	$this->stat_searchtracknumber = isset($rarray['tracknumber']) ? $rarray['tracknumber'] : NULL;
	$this->stat_searchcalldirection = isset($rarray['calldirection']) ? $rarray['calldirection'] : NULL;
	$this->stat_searchfrominboundid = isset($rarray['searchedfrominboundid']) ? $rarray['searchedfrominboundid'] : NULL;
	$this->stat_searchfromoutboundid = isset($rarray['searchedfromoutboundid']) ? $rarray['searchedfromoutboundid'] : NULL;
	$this->stat_disposition = isset($rarray['disposition']) ? $rarray['disposition'] : NULL;
	
	if(is_array($rarray['start_date']))
		$this->stat_startdate = $rarray['start_date']['year'].'-'.$rarray['start_date']['month'].'-'.$rarray['start_date']['day'];
	else
		$this->stat_startdate = $rarray['start_date'];
	
	if(is_array($rarray['end_date']))
		$this->stat_enddate = $rarray['end_date']['year'].'-'.$rarray['end_date']['month'].'-'.$rarray['end_date']['day'];
	else
		$this->stat_enddate = $rarray['end_date'];
			
	
	$this->stat_step = isset($rarray['step']) ? $rarray['step'] : NULL;
	
	$this->stat_startdate_ext = $this->stat_startdate.' 00:00:00';
	$this->stat_enddate_ext   = $this->stat_enddate.' 23:59:59';  
  }
  
  public function buildqueuereportform(sfWebRequest $request)
  {	
	$queues = Doctrine_Query::create()
						->select('q.dbid, q.name')
						->from('ConfQueue q')
						->orderBy('q.name')
						->where('1');
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$queues->andWhere('q.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
		
	if(!fonivaFilter::canCurrentUserManageAllQueues())
	{
		$queues->leftJoin('q.ConfUserAssignedqueue a')
				->andWhere('a.user_fk = ?', fonivaFilter::getCurrentUser()->getDbid());
	}
	
	$queues = $queues->execute();
	
	
	$fulllist = array();
	$fulllist[''] = "All Queues";
	foreach($queues as $q)
	{
		$fulllist[$q->dbid] = $q->name;
	}
	
	/* built defaults */
	$this->reportdefparams = array();
	
	$rarray =  $request->getParameter('report');
	if( isset($rarray['report_id']) )
		$this->reportdefparams['report_id'] = $rarray['report_id'];
	if( isset($rarray['report_queue']) )
		$this->reportdefparams['report_queue'] = $rarray['report_queue'];
	if( isset($rarray['start_date']) )
		$this->reportdefparams['start_date'] = $rarray['start_date'];
	else
		$this->reportdefparams['start_date'] = date('Y-m-d');
	if( isset($rarray['end_date']) )
		$this->reportdefparams['end_date'] = $rarray['end_date'];
	else
		$this->reportdefparams['end_date'] = date('Y-m-d');
	if( isset($rarray['date_range']) )
		$this->reportdefparams['date_range'] = $rarray['date_range'];
	else
		$this->reportdefparams['date_range'] = 'TODAY';
	if( isset($rarray['step']) )
		$this->reportdefparams['step'] = $rarray['step'];
	if( isset($rarray['csv_export']) )
		$this->reportdefparams['csv_export'] = $rarray['csv_export'];
	if( isset($rarray['calleridnum']) )
		$this->reportdefparams['calleridnum'] = $rarray['calleridnum'];
	if( isset($rarray['callednum']) )
		$this->reportdefparams['callednum'] = $rarray['callednum'];
	if( isset($rarray['tracknumber']) )
		$this->reportdefparams['tracknumber'] = $rarray['tracknumber'];
	if( isset($rarray['calldirection']) )
		$this->reportdefparams['calldirection'] = $rarray['calldirection'];
	if( isset($rarray['searchedfrominboundid']) )
		$this->reportdefparams['searchedfrominboundid'] = $rarray['searchedfrominboundid'];
	if( isset($rarray['searchedfromoutboundid']) )
		$this->reportdefparams['searchedfromoutboundid'] = $rarray['searchedfromoutboundid'];
	if( isset($rarray['disposition']) )
		$this->reportdefparams['disposition'] = $rarray['disposition'];
		
	
	$this->form = new ReportslistForm($this->reportdefparams);
	$this->form['report_id']->getWidget()->setOption('choices', $this->reports_r);
	
	
	$this->getResponse()->addStylesheet('chosen/chosen.css', 'last');
	$this->getResponse()->addJavascript('chosen/chosen.jquery.min.js', 'last');
	
	$this->getResponse()->addJavascript('mediaelement-and-player.min.js', 'last');
	$this->getResponse()->addStylesheet('mediaelementplayer.min.css', 'last');
  }
  
  public function executeCalldetail(sfWebRequest $request)
  {
	$this->tracknumber = $request->getParameter('tracknumber');
	$this->forward404Unless($this->tracknumber);
	
	$this->callername = NULL;
	$this->callernumber = NULL;
	$this->callednumber = NULL;
	
	$this->logcallrecord = Doctrine_Query::create()
								->from('LogCallRecord a')
								->where('a.tracknumber = ?', $this->tracknumber);
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$this->logcallrecord->andWhere('a.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	$this->logcallrecord = $this->logcallrecord->execute();
	
	if(sizeof($this->logcallrecord) > 0)
	{
		$this->callername = $this->logcallrecord[0]->callername;
		$this->callernumber = $this->logcallrecord[0]->callernumber;
		$this->callednumber = $this->logcallrecord[0]->callednumber;
	}
	else
	{
		//maybe if was an agent outbound call
		$this->logagentrecord = Doctrine_Query::create()
								->from('LogAgentCall agc')
								->where('agc.tracknumber = ?', $this->tracknumber)
								->execute();
								
		$this->forward404Unless((sizeof($this->logagentrecord) > 0));
		$this->callername = $this->logagentrecord[0]->callername;
		$this->callernumber = $this->logagentrecord[0]->callernumber;
		$this->callednumber = $this->logagentrecord[0]->callednum;
	}
	
	$this->logcallevents =   Doctrine_Query::create()
                                                        ->from('LogCallEvent e')
                                                        ->where('e.tracknumber = ?', $this->tracknumber)
                                                        ->orderBy('e.occured_at, e.dbid');
    $this->logcallevents = $this->logcallevents->execute();
	
	///TODO : Remove me	. This is just a fallback for older calls					
	if(sizeof($this->logcallevents) <= 0)
	{
		$this->redirect('report/calldetaildeprecated?tracknumber='.$this->tracknumber);
	}
	
	//search also for all call properties
	$this->logcallproperties = Doctrine_Query::create()
															->from('LogCallProperty p')
															->where('p.tracknumber = ?', $this->tracknumber)
															->orderBy('p.propertyname')
															->execute();
															
	
	//search also for all call dispositions
	$this->logcalldispositions = Doctrine_Query::create()
															->from('LogCallDisposition d')
															->where('d.tracknumber = ?', $this->tracknumber)
															->orderBy('d.occured_at')
															->execute();
	
	//$this->getResponse()->addJavascript('mediaelement-and-player.min.js', 'last');
	//$this->getResponse()->addStylesheet('mediaelementplayer.min.css', 'last');
	
	$this->getResponse()->addJavascript('jplayer/jPlayer-2.9.2/jplayer/jquery.jplayer.min.js', 'last');
	$this->getResponse()->addStylesheet('/js/jplayer/jPlayer-2.9.2/skin/blue.monday/css/jplayer.blue.monday.css', 'last');
	
  }
  
  static public function comparevariousccalllogs($clog1, $clog2)
  {
	  $date1 = '';
	  if(($clog1 instanceof LogAgentCall)) $date1 = $clog1->callreceived_at;
	  if(($clog1 instanceof LogCallRecord)) $date1 = $clog1->call_init_time;
	  if(($clog1 instanceof LogCallVoicerecording)) $date1 = $clog1->started_at;
	  if(($clog1 instanceof LogDialing)) $date1 = $clog1->created_at;
	  if(($clog1 instanceof LogQueueRecord)) $date1 = $clog1->queue_enter_time;
	  
	  $date2 = '';
	  if(($clog2 instanceof LogAgentCall)) $date2 = $clog2->callreceived_at;
	  if(($clog2 instanceof LogCallRecord)) $date2 = $clog2->call_init_time;
	  if(($clog2 instanceof LogCallVoicerecording)) $date2 = $clog2->started_at;
	  if(($clog2 instanceof LogDialing)) $date2 = $clog2->created_at;
	  if(($clog2 instanceof LogQueueRecord)) $date2 = $clog2->queue_enter_time;
	  
	  //when date are different, it is easy
	  
	  if($date1 != $date2)
	  {
	  	return strcasecmp($date1, $date2);
	  }
	  else
	  {
		
		//when  there are the same, we can assume that some classes event happen before others
		$classrank1 = 0;   
		$classrank2 = 0;
		
		
		if(($clog1 instanceof LogDialing)) $classrank1 = 1;
		if(($clog1 instanceof LogCallRecord)) $classrank1 = 2;
		if(($clog1 instanceof LogAgentCall)) $classrank1 = 3;
		if(($clog1 instanceof LogQueueRecord)) $classrank1 = 4;
		if(($clog1 instanceof LogCallVoicerecording)) $classrank1 = 5;
		
		if(($clog2 instanceof LogDialing)) $classrank2 = 1;
		if(($clog2 instanceof LogCallRecord)) $classrank2 = 2;
		if(($clog2 instanceof LogAgentCall)) $classrank2 = 3;
		if(($clog2 instanceof LogQueueRecord)) $classrank2 = 4;
		if(($clog2 instanceof LogCallVoicerecording)) $classrank2 = 5;
		
		return ($classrank1 - $classrank2);
	  }
  }
  
  public function executeVoicerecordingaudio(sfWebRequest $request)
  {
	  $recordid = $request->getParameter('recordid');
	  $this->forward404Unless($recordid);

	  $vrecord = Doctrine::getTable('LogCallVoicerecording')
	  				->findOneByRecordid($recordid);
					
	  $this->forward404Unless($vrecord);
	  
	  $storage = $vrecord->ConfStorageserver;
	  $this->forward404Unless($storage);
	  
	  //first download the file by ftp
	  $ftp_host = $storage->ftp_host;
	  $ftp_user = $storage->ftp_username_read;
	  $ftp_pass = $storage->ftp_password_read;
	  $ftp_port = $storage->ftp_port;
	  $ftp_path = $storage->directory;
	  $ftp_file = $vrecord->subdir.'/'.$vrecord->filename;
	  
	  $localfilesdir = sfConfig::get('sf_upload_dir').'/tmp-recordings/';
	  //$localfilesdir = '/tmp/';
	  
	  if(!is_dir($localfilesdir))
	  {
		 mkdir($localfilesdir, 0777, true);  
	  }
	  $ftp_localfile = $localfilesdir.''.basename($ftp_file);
	  $ftp_remotefile = 'ftp://'.$ftp_user.':'.$ftp_pass.'@'.$ftp_host.':'.$ftp_port.'/'.$ftp_file;
	  //echo $ftp_remotefile;
	  
	  $oldErrorReportingValue = error_reporting(0);
	  
	  
	  $downloadres = @copy($ftp_remotefile, $ftp_localfile);
	  
	   //try another one
      if(!$downloadres)
	  {
			$ftp_file = basename($vrecord->filename);
			$ftp_remotefile = 'ftp://'.$ftp_user.':'.$ftp_pass.'@'.$ftp_host.':'.$ftp_port.'/'.$ftp_file;
			$downloadres = @copy($ftp_remotefile, $ftp_localfile);
	  }

	  //try another one
	  if(!$downloadres)
	  {
			$ftp_file = str_replace($ftp_path, "", $vrecord->filename);
			$ftp_remotefile = 'ftp://'.$ftp_user.':'.$ftp_pass.'@'.$ftp_host.':'.$ftp_port.'/'.$ftp_file;
			$downloadres = @copy($ftp_remotefile, $ftp_localfile);
	  }

	  //try another one
	  if(!$downloadres)
	  {
			$ftp_file = str_replace("/data/foniva/storage", "", $vrecord->filename);
			$ftp_remotefile = 'ftp://'.$ftp_user.':'.$ftp_pass.'@'.$ftp_host.':'.$ftp_port.'/'.$ftp_file;
			$downloadres = @copy($ftp_remotefile, $ftp_localfile);
	  }

	  //try another one
	  if(!$downloadres)
	  {
			$ftp_file = str_replace("/var/data/foniva/storage", "", $vrecord->filename);
			$ftp_remotefile = 'ftp://'.$ftp_user.':'.$ftp_pass.'@'.$ftp_host.':'.$ftp_port.'/'.$ftp_file;
			$downloadres = @copy($ftp_remotefile, $ftp_localfile);
	  }
	  
	  error_reporting($oldErrorReportingValue);
	  
	  if(!$downloadres)
	  {
	  	$this->logMessage('Unable to download '.$ftp_remotefile.' to '.$ftp_localfile, 'DEBUG');
		
		$this->setLayout(false);
		
		
		echo "Missing recording file. File may have been moved or Foniva storage process is not running.";
		
		//echo $ftp_remotefile;
	  }
	  else
	  {  
		  $response = $this->getResponse();
		  
		  $fileparts = pathinfo($ftp_localfile);
		  $fileexten = 'mp3';
		  $mp3_shortlocalfile = basename($ftp_localfile,'.'.$fileparts['extension']).'.mp3';
		  $mp3_localfile = $fileparts['dirname'].'/'.$mp3_shortlocalfile;
		  
		  if(!file_exists($mp3_localfile))
		  {
			  $tmpwav = $fileparts['dirname'].'/'.(rand()*10000000).'.wav';
			  $convertcmd = 'sox "'.$ftp_localfile.'" "'.$tmpwav.'" && lame --silent "'.$tmpwav.'" "'.$mp3_localfile.'"  && rm -f  "'.$tmpwav.'" "'.$ftp_localfile.'"  ';
			  system($convertcmd, $retval);
				  
			  //maybe conversion failed
			  if(!file_exists($mp3_localfile))
			  {
					$convertcmd = 'cp "'.$ftp_localfile.'" "'.$mp3_localfile.'"';
					 system($convertcmd, $retval);
			  }
		  }
		  
		  $response->setHttpHeader('Location',  $request->getRelativeUrlRoot().'/uploads/tmp-recordings/'.$mp3_shortlocalfile);
		  $response->sendHttpHeaders();
		  ob_clean();
		  flush();
	  }
  }
  
  /* */
  /* graph actions */
  /* - called by dashboard*/
  
  public function executeGraphagentstatus(sfWebRequest $request)
  {
	$this->setLayout(false);
	$this->stat_startdate_ext = $request->getParameter('start_date');
	$this->stat_enddate_ext	  = $request->getParameter('end_date');
	$this->stat_step		  = $request->getParameter('step');
	$this->stat_element		  = $request->getParameter('stat_element');
	$this->graphsize		  = $request->getParameter('graphsize');
	  
	$this->tableclassname = 'LogResumeDailyAgentEvent';
	if($this->stat_step == 'hourly')
		$this->tableclassname = 'LogResumeHourlyAgentEvent';
	if($this->stat_step == 'weekly')
		$this->tableclassname = 'LogResumeWeeklyAgentEvent';
	if($this->stat_step == 'monthly')
		$this->tableclassname = 'LogResumeMonthlyAgentEvent';
	if($this->stat_step == '15min')
		$this->tableclassname = 'LogResume15minAgentEvent';
	if($this->stat_step == '30min')
		$this->tableclassname = 'LogResume30minAgentEvent';
		
	$this->datasum = Doctrine_Query::create()
					->select('r.agentstatus as agentstatus, 
							 sum(r.totalduration) as totalduration')
					->from($this->tableclassname.' r')
					->where('r.covereddate >= ? and r.covereddate <= ?'.
								(($this->stat_element == '')?'':' and r.'.$this->stateltscolumn.' = ?'), 
								($this->stat_element == '') ? array($this->stat_startdate_ext, $this->stat_enddate_ext) : array($this->stat_startdate_ext, $this->stat_enddate_ext, $this->stat_element)
								)
					->andWhere('r.agentstatus != "LOGGED OUT"') /* do not count LOGOUT */
					->groupBy('r.agentstatus')
					->orderBy('r.covereddate, r.agentstatus');
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$this->datasum->andWhere('r.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	$this->datasum = $this->datasum->execute();  
  }
  
  public function executeGraphcallrecords(sfWebRequest $request)
  {
	$this->setLayout(false);
	$this->stat_startdate_ext = $request->getParameter('start_date');
	$this->stat_enddate_ext	  = $request->getParameter('end_date');
	$this->stat_step		  = $request->getParameter('step');
	$this->stat_element		  = $request->getParameter('stat_element');
	$this->graphsize		  = $request->getParameter('graphsize');
	
	$this->tableclassname = 'LogResumeDailyCallRecord';
	if($this->stat_step == 'hourly')
		$this->tableclassname = 'LogResumeHourlyCallRecord';
	if($this->stat_step == 'monthly')
		$this->tableclassname = 'LogResumeMonthlyCallRecord';
	if($this->stat_step == 'weekly')
		$this->tableclassname = 'LogResumeWeeklyCallRecord';
	if($this->stat_step == '15min')
		$this->tableclassname = 'LogResume15minCallRecord';
	if($this->stat_step == '30min')
		$this->tableclassname = 'LogResume30minCallRecord';
		
	
	$this->datasum = Doctrine_Query::create()
					->select('r.covereddate as covereddate, r.is_inbound as is_inbound,
							 		sum(r.nbcalls) as nbcalls, sum(r.dialingtime) as dialingtime')					
					->from($this->tableclassname.' r')
					->where('r.covereddate >= ? and r.covereddate <= ?'.
								(($this->stat_element == '')?'':' and r.campaign_id = ?'), 
									($this->stat_element == '') ? array($this->stat_startdate_ext, $this->stat_enddate_ext) : array($this->stat_startdate_ext, $this->stat_enddate_ext, $this->stat_element)
								)
					->groupBy('r.covereddate, r.is_inbound');
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$this->datasum->andWhere('r.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	$this->datasum = $this->datasum->execute();  
	
  }
  
  /******* AJAX REQUESTS */
  public function executeGetreports_extraform(sfWebRequest $request)
  {
	$this->forward404Unless(($rid = $request->getParameter('reportid')));
	$this->report = Doctrine::getTable('ConfReport')->find($rid);	
	$this->forward404Unless($this->reportcategory = $request->getParameter('reportcategory'));
	$this->forward404Unless($this->report);
	
	$this->reportdefparams = $request->getParameter('report');
	
	$this->subreport = NULL;
	
	switch($this->reportcategory)
	{
		case 'AGENT':
			$this->sites = fonivaFilter::currentUserManagedSites();
			$this->sites_list = array();
			$this->sites_list[''] = 'All sites';
			foreach($this->sites as $s)
			{
				$this->sites_list[$s->getDbid()] = $s->name;
			}
			
			$this->groups = fonivaFilter::currentUserManagedGroups();
			$this->groups_list = array();
			$this->groups_list[''] = 'All groups';
			foreach($this->groups as $g)
			{
				$this->groups_list[$g->dbid] = $g->name;
			}
			
			
			$this->teams = Doctrine_Query::create()
								->select('t.dbid, t.name')
								->from('ConfTeam t')
								->orderBy('t.name');
			if(!fonivaFilter::isCurrentUserSuperTenant())
				$this->teams->andWhere('t.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
			$this->teams = $this->teams->execute();
			
			$this->teams_list = array();
			$this->teams_list[''] = 'All teams';
			foreach($this->teams as $g)
			{
				$this->teams_list[$g->dbid] = $g->name;
			}
			
			
			$this->activities = Doctrine_Query::create()
								->select('a.dbid, a.name, a.colorcode')
								->from('ConfAgentActivity a')
								->where('a.active = "yes"')
								->andwhere('a.name != "WORKING"')
								->orderBy('a.name');
			if(!fonivaFilter::isCurrentUserSuperTenant())
				$this->activities->andWhere('a.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
			$this->activities = $this->activities->execute();
			
		break;
		case 'INBOUND':
			$this->inbounds = fonivaFilter::currentUserManagedInbounds();
			$this->inbounds_list = array();
			$this->inbounds_list[''] = 'All inbounds';
			foreach($this->inbounds as $i)
			{
				$this->inbounds_list[$i->dbid] = $i->inbound_name;
			}
		break;
		case 'OUTBOUND':
			$this->outbounds = fonivaFilter::currentUserManagedOutbounds();
			$this->outbounds_list = array();
			$this->outbounds_list[''] = 'All campaigns';
			foreach($this->outbounds as $o)
			{
				$this->outbounds_list[$o->dbid] = $o->campaign_name;
			}
			
			$this->outbounds_callinglists_list = array();
			$this->outbounds_callinglists_list[''] = 'All lists';
			
			$q = Doctrine_Query::create()
								->select('a.dbid, a.calling_list_name')
								->from('ConfCallingList a, ConfCampaign c')
								->andwhere('a.campaign_dbid = c.dbid')
								->orderBy('a.calling_list_name');
			if(!fonivaFilter::isCurrentUserSuperTenant())
				$q->andWhere('c.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
			$q = $q->execute();
			
			foreach($q as $cl)
			{
				$this->outbounds_callinglists_list[$cl->dbid] = $cl->calling_list_name;
			}
			
		break;
		case 'QUEUE':
			$this->queues = fonivaFilter::currentUserManagedQueues();
			$this->queues_list = array();
			$this->queues_list[''] = 'All queues';
			foreach($this->queues as $q)
			{
				$this->queues_list[$q->dbid] = $q->name;
			}
			
			$this->inbounds = fonivaFilter::currentUserManagedInbounds();
			$this->inbounds_list = array();
			$this->inbounds_list[''] = 'All inbounds';
			foreach($this->inbounds as $i)
			{
				$this->inbounds_list[$i->dbid] = $i->inbound_name;
			}
			
			$this->outbounds = fonivaFilter::currentUserManagedOutbounds();
			$this->outbounds_list = array();
			$this->outbounds_list[''] = 'All campaigns';
			foreach($this->outbounds as $o)
			{
				$this->outbounds_list[$o->dbid] = $o->campaign_name;
			}
		break;
	}
	
	
	switch($this->report->name)
	{
		case 'agent-calloperations-agentstatussummary':
		case 'agent-calloperations-agentcalls':
		case 'agent-calloperations-agentnotreadyreasonstatussummary':
		case 'agent-calloperations-agentstatusloggedvsnotreadysummary':
		case 'agent-calloperations-agentcalldispositions':
			$this->form = new ReportAgentForm();
			$this->form['report_site']->getWidget()->setOption('choices', $this->sites_list);
			$this->form['report_group']->getWidget()->setOption('choices', $this->groups_list);
			$this->form['report_team']->getWidget()->setOption('choices', $this->teams_list);
			$this->form->setDefaults($this->reportdefparams);
			$this->setTemplate('agentsubform');
		break;		
		
		case 'agent-calloperations-agentoccupancy':
			$this->form = new ReportAgentForm();
			$this->form['report_site']->getWidget()->setOption('choices', $this->sites_list);
			$this->form['report_group']->getWidget()->setOption('choices', $this->groups_list);
			$this->form['report_team']->getWidget()->setOption('choices', $this->teams_list);
			$this->form->setDefaults($this->reportdefparams);
			$this->setTemplate('agentoccupancysubform');
		break;
		
		case 'agent-calloperations-agentstatusdetailed':
		case 'agent-calloperations-agentcalltransfered':
			$this->form = new ReportAgentDetailedForm();
			$this->form['report_site']->getWidget()->setOption('choices', $this->sites_list);
			$this->form['report_group']->getWidget()->setOption('choices', $this->groups_list);
			$this->form['report_team']->getWidget()->setOption('choices', $this->teams_list);
			$this->form->setDefaults($this->reportdefparams);
			$this->setTemplate('agentdetailedsubform');
		break;		
		case 'agent-adherence-agentadherencedetailed':
			$this->form = new ReportAgentAdherenceDetailedForm();
			$this->form['report_site']->getWidget()->setOption('choices', $this->sites_list);
			$this->form['report_group']->getWidget()->setOption('choices', $this->groups_list);
			$this->form['report_team']->getWidget()->setOption('choices', $this->teams_list);
			$this->form->setDefaults($this->reportdefparams);
			$this->setTemplate('agentadherencedetailedsubform');
		break;		
		
		case 'agent-calloperations-agentcalldetails':
			$this->form = new ReportAgentCallDetailedForm();
			$this->form['report_site']->getWidget()->setOption('choices', $this->sites_list);
			$this->form['report_group']->getWidget()->setOption('choices', $this->groups_list);
			$this->form['report_team']->getWidget()->setOption('choices', $this->teams_list);
			$this->form->setDefaults($this->reportdefparams);
			$this->setTemplate('agentcalldetailedsubform');
		break;
		
		case 'inbound-inboundcalls':
		case 'inbound-inboundcalldispositions':
			$this->form = new ReportInboundForm();
			$this->form['report_inbound']->getWidget()->setOption('choices', $this->inbounds_list);
			$this->form->setDefaults($this->reportdefparams);
			$this->setTemplate('inboundsubform');
		break;
		
		case 'inbound-callflowcounters':
			$this->form = new ReportInboundFullForm();
			$this->form['report_inbound']->getWidget()->setOption('choices', $this->inbounds_list);
			$this->form->setDefaults($this->reportdefparams);
			$this->setTemplate('inboundfullsubform');
		break;
		
		case 'inbound-inboundcalldetails':
			$this->form = new ReportInboundCallDetailedForm();
			$this->form['report_inbound']->getWidget()->setOption('choices', $this->inbounds_list);
			$this->form->setDefaults($this->reportdefparams);
			$this->setTemplate('inboundcalldetailedsubform');
		break;
		
		case 'outbound-outboundcalls':
		case 'outbound-outboundcalldispositions':
			$this->form = new ReportOutboundForm();
			$this->form['report_outbound']->getWidget()->setOption('choices', $this->outbounds_list);
			$this->form->setDefaults($this->reportdefparams);
			$this->setTemplate('outboundsubform');
		break;
		
		case 'outbound-outboundcalldetails':
			$this->form = new ReportOutboundCallDetailedForm();
			$this->form['report_outbound']->getWidget()->setOption('choices', $this->outbounds_list);
			$this->form->setDefaults($this->reportdefparams);
			$this->setTemplate('outboundcalldetailedsubform');
		break;
		
		case 'outbound-dialedcalls':
			$this->form = new ReportOutboundDialedForm();
			$this->form['report_outbound']->getWidget()->setOption('choices', $this->outbounds_list);
			$this->form['report_callinglist']->getWidget()->setOption('choices', $this->outbounds_callinglists_list);
			$this->form->setDefaults($this->reportdefparams);
			$this->setTemplate('outbounddialedsubform');
		break;
		
		case 'outbound-dialedcalldetails':
			$this->form = new ReportOutboundDialedCallDetailedForm();
			$this->form['report_outbound']->getWidget()->setOption('choices', $this->outbounds_list);
			$this->form['report_callinglist']->getWidget()->setOption('choices', $this->outbounds_callinglists_list);
			$this->form->setDefaults($this->reportdefparams);
			$this->setTemplate('outbounddialedcalldetailedsubform');
		break;
		
		case 'queue-queuecalls':
		case 'queue-queueservicelevelsummary':
		case 'queue-queuecalldispositions':
			$this->form = new ReportQueueForm();
			$this->form['report_queue']->getWidget()->setOption('choices', $this->queues_list);
			$this->form['searchedfrominboundid']->getWidget()->setOption('choices', $this->inbounds_list);
			$this->form['searchedfromoutboundid']->getWidget()->setOption('choices', $this->outbounds_list);
			$this->form->setDefaults($this->reportdefparams);
			$this->setTemplate('queuesubform');
		break;
		
		case 'queue-queuecalldetails':
			$this->form = new ReportQueueCallDetailedForm();
			$this->form['report_queue']->getWidget()->setOption('choices', $this->queues_list);
			$this->form['searchedfrominboundid']->getWidget()->setOption('choices', $this->inbounds_list);
			$this->form['searchedfromoutboundid']->getWidget()->setOption('choices', $this->outbounds_list);
			$this->form->setDefaults($this->reportdefparams);
			$this->setTemplate('queuecalldetailedsubform');
		break;
		
		default:
			$this->forward404("Unsupported report name ".$this->report->name);	
	}
	
  }
  
  public function executeGetreports_agentslist(sfWebRequest $request)
  {
	$this->siteid = $request->getParameter('siteid');
	$this->groupid = $request->getParameter('groupid');
	$this->teamid = $request->getParameter('teamid');
	
	$this->reportdefparams = $request->getParameter('report');
	
	$this->groups = fonivaFilter::currentUserManagedGroups();
	
	$this->groups_list = array();
	$this->groupids_list = array('');
	$allowed = false;
	foreach($this->groups as $g)
	{
		$this->groups_list[$g->getDbid()] = $g;
		array_push($this->groupids_list, $g->getDbid());
		
		if($this->groupid == $g->getDbid())
		{
			$allowed = true;
		}
	}
	
	if($this->groupid != NULL && $this->groupid != '' && !$allowed)
		$this->forward404();
		
	
	$this->sites = fonivaFilter::currentUserManagedSites();
	
	$this->sites_list = array();
	$this->siteids_list = array('');
	$allowed = false;
	foreach($this->sites as $s)
	{
		$this->sites_list[$s->getDbid()] = $s;
		array_push($this->siteids_list, $s->getDbid());
		
		if($this->siteid == $s->getDbid())
		{
			$allowed = true;
		}
	}
	
	if($this->siteid != NULL && $this->siteid != '' && !$allowed)
		$this->forward404();
	
  
	$this->agents = Doctrine_Query::create()
						->select('ca.dbid, ca.username, ca.shortusername, ci.firstname as firstname, ci.lastname as lastname')
						->from('ConfAgent ca')						
						->leftJoin('ca.ConfAgentInfo ci ON ci.dbid = ca.info_fk')
						->orderBy('ca.username')
						->where('ca.active = "yes"');
	if($this->groupid != NULL && $this->groupid != '')
		$this->agents->andWhere('ca.group_fk = ?', $this->groupid);
	else if(sizeof($this->groupids_list) > 1)
	{
		$this->agents->andWhere('ca.group_fk in ?', array($this->groupids_list));
	}
	
	if($this->teamid != NULL && $this->teamid != '')
		$this->agents->andWhere('ca.team_fk = ?', $this->teamid);
	
	if($this->siteid != NULL && $this->siteid != '')
		$this->agents->andWhere('ca.site_fk = ?', $this->siteid);
	else if(sizeof($this->siteids_list) > 1)
	{
		$this->agents->andWhere('ca.site_fk in ?', array($this->siteids_list));
	}
	
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$this->agents->andWhere('ca.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	$this->agents->orderBy('ca.shortusername, ci.lastname, ci.firstname');
	$this->agents = $this->agents->execute();
	
	$this->agentslist_arr = array();
	$this->agentslist_arr[''] = 'All agents';
	
	$followedagents = Doctrine_Query::create()
							->select('count(fa.dbid) as nbfollowedagents') 
							->from('ConfUserFollowedagent fa')
							->where('fa.user_fk = ?', fonivaFilter::getCurrentUser()->getDbid())
							->execute();
	if(isset($followedagents) && isset($followedagents[0]) && isset($followedagents[0]->nbfollowedagents) 
			&& $followedagents[0]->nbfollowedagents > 0) //curent user has at least one followed agent
	{
		$this->agentslist_arr['USERFOLLOWEDAGENTS'] = 'My followed agents';
	}
	
	
	foreach($this->agents as $a)
		$this->agentslist_arr[$a->dbid] = ''.$a->shortusername.' '.$a->lastname.', '.$a->firstname;
	
	
	//create form on the fly
	$this->agentslistform = new sfForm();
	$this->agentslistform->setWidgets(array(
					'report_agent' => new sfWidgetFormSelect(array('choices'=>array())),
					));
	$this->agentslistform->getWidgetschema()->setNameFormat('report[%s]');
	$this->agentslistform['report_agent']->getWidget()->setLabel('Agents');
	$this->agentslistform['report_agent']->getWidget()->setOption('choices', $this->agentslist_arr);
	//$this->agentslistform->setLabels(array('report_agent' => 'Agents'));
	
	$this->agentslistform->setDefaults($this->reportdefparams);
	
  }
  
  
  public function executeGetreports_callinglistslist(sfWebRequest $request)
  {
	$this->campaignid = $request->getParameter('campaignid');
	
	$this->reportdefparams = $request->getParameter('report');
	
	$this->campaigns = fonivaFilter::currentUserManagedOutbounds();
	
	$this->campaigns_list = array();
	$this->campaignids_list = array('');
	$allowed = false;
	foreach($this->campaigns as $g)
	{
		$this->campaigns_list[$g->getDbid()] = $g;
		array_push($this->campaignids_list, $g->getDbid());
		
		if($this->campaignid == $g->getDbid())
		{
			$allowed = true;
		}
	}
	
	if($this->campaignid != NULL && $this->campaignid != '' && !$allowed)
		$this->forward404();
  
	$this->callinglists = Doctrine_Query::create()
						->select('ca.dbid, ca.calling_list_name')
						->from('ConfCallingList ca')
						->orderBy('ca.calling_list_name');
	if($this->campaignid != NULL && $this->campaignid != '')
		$this->callinglists->andWhere('ca.campaign_dbid = ?', $this->campaignid);
	$this->callinglists = $this->callinglists->execute();
	
	$this->callinglists_arr = array();
	$this->callinglists_arr[''] = 'All Calling Lists';
	
	foreach($this->callinglists as $a)
		$this->callinglists_arr[$a->dbid] = $a->calling_list_name;
	
	
	//create form on the fly
	$this->calllinglistform = new sfForm();
	$this->calllinglistform->setWidgets(array(
					'report_callinglist' => new sfWidgetFormSelect(array('choices'=>array())),
					));
	$this->calllinglistform->getWidgetschema()->setNameFormat('report[%s]');
	$this->calllinglistform['report_callinglist']->getWidget()->setLabel('Calling Lists');
	$this->calllinglistform['report_callinglist']->getWidget()->setOption('choices', $this->callinglists_arr);
	
	
	$this->calllinglistform->setDefaults($this->reportdefparams);
	
  }
  
  public function executeCustomreports(sfWebRequest $request)
  {
	$this->customreports = Doctrine_Query::create()
						->from('ConfCustomReport cr')						
						->orderBy('cr.reportname')
						->where('cr.active = "yes"');
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$this->customreports->andWhere('cr.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	$this->customreports = $this->customreports->execute();
  }
}