<?php

/**
 * workforce actions.
 *
 * @package    foniva
 * @subpackage workforce
 * @author     Your name here
 * @version    SVN: $Id: actions.class.php 23810 2009-11-12 11:07:44Z Kris.Wallsmith $
 */
class workforceActions extends sfActions
{
 /**
  * Executes index action
  *
  * @param sfRequest $request A request object
  */
  public function executeIndex(sfWebRequest $request)
  {
    $this->forward('workforce', 'activities');
  }
  
  
  /*********   ************/
  /***   ACTIVITIES   *****/
  /*********   ************/	 
  
  public function executeActivities(sfWebRequest $request)
  {
	$this->searchedActivity = $this->getRequestParameter('searchedActivity');
	$this->searchedActivity = trim(strtolower($this->searchedActivity));
	
	$this->pager = new sfDoctrinePager('ConfAgentActivity', sfConfig::get('app_display_nbrecordperpage'));
	$this->pager->getQuery()->from('ConfAgentActivity ca')
							->orderBy('ca.name');
	
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$this->pager->getQuery()->andwhere('ca.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	
	$defaultpage = $this->getcurrentmodule_defaultpage();
	if(!empty($this->searchedActivity))
	{
		$this->pager->getQuery()->where('lower(ca.name) LIKE ?', array('%'.$this->searchedActivity.'%'));
		$defaultpage = 1;
	}
									
	$this->setcurrentmodule_defaultpage();
	$this->pager->setPage($defaultpage); //$this->getRequestParameter('page',1));
	$this->pager->init();
	$this->activities = $this->pager->getResults();
  }
  
  public function executeActivitynew(sfWebRequest $request)
  {
    $conf_activity = new ConfAgentActivity();
	$conf_activity->setColorcode('#f1c232');	//default color code
    $this->form = new ConfAgentActivityForm($conf_activity);
  }

  public function executeActivitycreate(sfWebRequest $request)
  {
    $this->forward404Unless($request->isMethod(sfRequest::POST));
    $conf_activity = new ConfAgentActivity();
    $this->form = new ConfAgentActivityForm($conf_activity);
    $this->processActivityForm($request, $this->form);
    $this->setTemplate('activitynew');
  }

  public function executeActivityedit(sfWebRequest $request)
  {
    $this->forward404Unless($conf_activity = Doctrine::getTable('ConfAgentActivity')->find($request->getParameter('dbid')), sprintf('Object conf_activity does not exist (%s).', $request->getParameter('dbid')));
    $this->form = new ConfAgentActivityForm($conf_activity);
  }

  public function executeActivityupdate(sfWebRequest $request)
  {
    $this->forward404Unless($request->isMethod(sfRequest::POST) || $request->isMethod(sfRequest::PUT));
    $this->forward404Unless($conf_activity = Doctrine::getTable('ConfAgentActivity')->find($request->getParameter('dbid')), sprintf('Object conf_activity does not exist (%s).', $request->getParameter('dbid')));
    $this->form = new ConfAgentActivityForm($conf_activity);
    $this->processActivityForm($request, $this->form);
    $this->setTemplate('activityedit');
  }

  public function executeActivitydelete(sfWebRequest $request)
  {
    $request->checkCSRFProtection();
    $this->forward404Unless($conf_activity = Doctrine::getTable('ConfAgentActivity')->find($request->getParameter('dbid')), sprintf('Object conf_activity does not exist (%s).', $request->getParameter('dbid')));
    $conf_activity->delete();
    $this->redirect('workforce/activities');
  }

  protected function processActivityForm(sfWebRequest $request, sfForm $form)
  {
    $form->bind($request->getParameter($form->getName()), $request->getFiles($form->getName()));
    if ($form->isValid())
    {
	  $isnew = $form->getObject()->isNew();
      $conf_activity = $form->getObject();
	  $conf_activity->setName($form->getValue('name'));
	  $conf_activity->setColorcode($form->getValue('colorcode'));
	  if($form->getValue('ispaid') != NULL)
		  $conf_activity->setIspaid($form->getValue('ispaid'));
      if($form->getValue('paidpercentage') != NULL)
	  	$conf_activity->setPaidpercentage($form->getValue('paidpercentage'));
	  $conf_activity->setActive($form->getValue('active'));
	  if(fonivaFilter::isCurrentUserSuperTenant())
	     $conf_activity->setTenantFk($form->getValue('tenant_fk'));
	  else
	     $conf_activity->setTenantFk(fonivaFilter::getCurrentUserTenantId());
	  if ($form->getObject()->isNew())
	  {
	  	$conf_activity->setCreatedBy($this->getUser()->getUsername());
	  }
	  $conf_activity->setModifiedBy($this->getUser()->getUsername());
	  $conf_activity->save();
	  
      $this->redirect('workforce/activities');
    }
  }
  
  
  
  /*********   ************/
  /***   SHIFTS       *****/
  /*********   ************/	 
  
  public function executeShifts(sfWebRequest $request)
  {
	$this->searchedShift = $this->getRequestParameter('searchedShift');
	$this->searchedShift = trim(strtolower($this->searchedShift));
	
	$this->pager = new sfDoctrinePager('ConfAgentShift', sfConfig::get('app_display_nbrecordperpage'));
	$this->pager->getQuery()->from('ConfAgentShift ca')
							->orderBy('ca.name');
	
	$defaultpage = $this->getcurrentmodule_defaultpage();
	if(!empty($this->searchedShift))
	{
		$this->pager->getQuery()->where('lower(ca.name) LIKE ?', array('%'.$this->searchedShift.'%'));
		$defaultpage = 1;
	}
									
	$this->setcurrentmodule_defaultpage();
	$this->pager->setPage($defaultpage); //$this->getRequestParameter('page',1));
	$this->pager->init();
	$this->shifts = $this->pager->getResults();
  }
  
  public function executeShiftnew(sfWebRequest $request)
  {
    $conf_shift = new ConfAgentShift();
    $this->form = new ConfAgentShiftForm($conf_shift);
	
	$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();
	
	//Empty row of activites
	$this->shiftevents = array();
	
  }

  public function executeShiftcreate(sfWebRequest $request)
  {
    $this->forward404Unless($request->isMethod(sfRequest::POST));
    $conf_shift = new ConfAgentShift();
    $this->form = new ConfAgentShiftForm($conf_shift);
    $this->processShiftForm($request, $this->form);
	
	
	//Create failed	
	$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();
	
	//Empty row of activites
	$this->shiftevents = array();
	$scheduledevents = $request->getParameter('scheduledevent');
	if(is_array($scheduledevents) && sizeof($scheduledevents) > 0)
	{
		for($i = 0; $i < sizeof($scheduledevents) ; $i++)
		{
			
			$sched = $scheduledevents[$i];
			
			$dayofweek = 'sunday';
			switch($sched['dayofweek'])
			{
				case '0': $dayofweek = 'sunday'; break;
				case '1': $dayofweek = 'monday'; break;
				case '2': $dayofweek = 'tuesday'; break;
				case '3': $dayofweek = 'wednesday'; break;
				case '4': $dayofweek = 'thursday'; break;
				case '5': $dayofweek = 'friday'; break;
				case '6': $dayofweek = 'saturday'; break;
			}
			
			$csevent = new ConfAgentShiftEvent();
			$csevent->shift_fk = $conf_shift->getDbid();
			$csevent->activity = $sched['activity'];
			$csevent->dayofweek = $dayofweek;
			$csevent->starttime = $sched['start'];
			$csevent->endtime = $sched['end'];
			array_push($this->shiftevents, $csevent);
		}
	}
	  
	
    $this->setTemplate('shiftnew');
  }

  public function executeShiftedit(sfWebRequest $request)
  {
    $this->forward404Unless($conf_shift = Doctrine::getTable('ConfAgentShift')->find($request->getParameter('dbid')), sprintf('Object conf_shift does not exist (%s).', $request->getParameter('dbid')));
    $this->form = new ConfAgentShiftForm($conf_shift);
	
	//Get the list of all available activities
	$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();
	
	
	//Get the list of the current shift events
	$this->shiftevents = Doctrine_Query::create()
						->select('a.activity, a.dayofweek, a.starttime, a.endtime')
						->from('ConfAgentShiftEvent a')
						->where('a.shift_fk = ?', $conf_shift->getDbid())
						->execute();
						
  }

  public function executeShiftupdate(sfWebRequest $request)
  {
    $this->forward404Unless($request->isMethod(sfRequest::POST) || $request->isMethod(sfRequest::PUT));
    $this->forward404Unless($conf_shift = Doctrine::getTable('ConfAgentShift')->find($request->getParameter('dbid')), sprintf('Object conf_shift does not exist (%s).', $request->getParameter('dbid')));
    $this->form = new ConfAgentShiftForm($conf_shift);
    $this->processShiftForm($request, $this->form);
	
	//update failed
	//Create failed	
	$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();
	
	//Empty row of activites
	$this->shiftevents = array();
	$scheduledevents = $request->getParameter('scheduledevent');
	if(is_array($scheduledevents) && sizeof($scheduledevents) > 0)
	{
		for($i = 0; $i < sizeof($scheduledevents) ; $i++)
		{
			
			$sched = $scheduledevents[$i];
			
			$dayofweek = 'sunday';
			switch($sched['dayofweek'])
			{
				case '0': $dayofweek = 'sunday'; break;
				case '1': $dayofweek = 'monday'; break;
				case '2': $dayofweek = 'tuesday'; break;
				case '3': $dayofweek = 'wednesday'; break;
				case '4': $dayofweek = 'thursday'; break;
				case '5': $dayofweek = 'friday'; break;
				case '6': $dayofweek = 'saturday'; break;
			}
			
			$csevent = new ConfAgentShiftEvent();
			$csevent->shift_fk = $conf_shift->getDbid();
			$csevent->activity = $sched['activity'];
			$csevent->dayofweek = $dayofweek;
			$csevent->starttime = $sched['start'];
			$csevent->endtime = $sched['end'];
			array_push($this->shiftevents, $csevent);
		}
	}
    $this->setTemplate('shiftedit');
  }

  public function executeShiftdelete(sfWebRequest $request)
  {
    $request->checkCSRFProtection();
    $this->forward404Unless($conf_shift = Doctrine::getTable('ConfAgentShift')->find($request->getParameter('dbid')), sprintf('Object conf_shift does not exist (%s).', $request->getParameter('dbid')));
    $conf_shift->delete();
    $this->redirect('workforce/shifts');
  }

  protected function processShiftForm(sfWebRequest $request, sfForm $form)
  {
    $form->bind($request->getParameter($form->getName()), $request->getFiles($form->getName()));
    if ($form->isValid())
    {
	  $isnew = $form->getObject()->isNew();	
	  
	  $conf_shift = $form->getObject();
	  $conf_shift->setName($form->getValue('name'));
	  $conf_shift->setActive($form->getValue('active'));
	  if(fonivaFilter::isCurrentUserSuperTenant())
	     $conf_shift->setTenantFk($form->getValue('tenant_fk'));
	  else
	     $conf_shift->setTenantFk(fonivaFilter::getCurrentUserTenantId());
		 
      $conf_shift->save();
	  
	  //Delete all shifts and recreate them
	  Doctrine_Query::create()
	  ->delete()
	  ->from('ConfAgentShiftEvent')
	  ->andWhere('shift_fk = ?', $conf_shift->getDbid())
	  ->execute();
	  
	  //create the new ones
	  $scheduledevents = $request->getParameter('scheduledevent');
	  if(is_array($scheduledevents) && sizeof($scheduledevents) > 0)
	  {
		for($i = 0; $i < sizeof($scheduledevents) ; $i++)
		{
			
			$sched = $scheduledevents[$i];
			
			$dayofweek = 'sunday';
			switch($sched['dayofweek'])
			{
				case '0': $dayofweek = 'sunday'; break;
				case '1': $dayofweek = 'monday'; break;
				case '2': $dayofweek = 'tuesday'; break;
				case '3': $dayofweek = 'wednesday'; break;
				case '4': $dayofweek = 'thursday'; break;
				case '5': $dayofweek = 'friday'; break;
				case '6': $dayofweek = 'saturday'; break;
			}
			
			$csevent = new ConfAgentShiftEvent();
			$csevent->shift_fk = $conf_shift->getDbid();
			$csevent->activity = $sched['activity'];
			$csevent->dayofweek = $dayofweek;
			$csevent->starttime = $sched['start'];
			$csevent->endtime = $sched['end'];
			$csevent->save();
		}
	  }
	  
	  
	  
      $this->redirect('workforce/shifts');
    }
  }
  
  /*********   ***********************/
  /***   AGENTS SCHEDULING       *****/
  /*********   ***********************/
  public function executeAgentscheduling(sfWebRequest $request)
  {
	$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->agentid = $request->getParameter('sched_agent_id');
	$this->groupid = $request->getParameter('sched_group_id');
	$this->display_agentscheduling = $request->getParameter('display_agentscheduling');
	
	/*
	echo 'agent = '.$agentid;
	echo 'group = '.$groupid;
	exit();
	*/
	
	/* retrieve all agent groups */
	/*
	$this->agentgroups = Doctrine_Query::create()
							->from('ConfGroup cg')
							->orderBy('cg.name')
							->where('1');
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$this->agentgroups->andWhere('cg.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	
	if(!fonivaFilter::canCurrentUserManageAllGroups())
	{
		$this->agentgroups->leftJoin('cg.ConfUserAssignedgroup a')
								->andWhere('a.user_fk = ?', fonivaFilter::getCurrentUser()->getDbid());
	}
	$this->agentgroups = $this->agentgroups->execute();	
	*/
	
	$this->agentgroups = fonivaFilter::currentUserManagedGroups();
	$this->groupids_list = array('');
	foreach($this->agentgroups as $g)
	{
		array_push($this->groupids_list, $g->getDbid());
	}
	
	/* agents list */
	$this->agents = Doctrine_Query::create()
						->select('ca.dbid, ca.username, ca.shortusername, ci.firstname as firstname, ci.lastname as lastname, cg.name as groupname')
						->from('ConfAgent ca')						
						->leftJoin('ca.ConfAgentInfo ci ON ci.dbid = ca.info_fk')
						->leftJoin('ca.ConfGroup cg ON ca.group_fk = cg.dbid')
						->orderBy('cg.name, ca.username')
						->where('ca.active = "yes"');
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$this->agents->andWhere('ca.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
		
	if(!fonivaFilter::canCurrentUserManageAllGroups())
		$this->agents->andWhere('ca.group_fk in ?', array($this->groupids_list));
		
	if($this->agentid != NULL && $this->agentid != '')
		$this->agents->andWhere('ca.dbid = ?', $this->agentid);
	if($this->groupid != NULL && $this->groupid != '')
		$this->agents->andWhere('cg.dbid = ?', $this->groupid);
		
	$this->agents = $this->agents->execute();
	
	/* assigned shifts */
	$this->shifts = Doctrine_Query::create() 
						->select('ca.dbid, ca.name')
						->from('ConfAgentShift ca')
						->orderBy('ca.name');
	
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$this->shifts->where('ca.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	$this->shifts = $this->shifts->execute();
	
	$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();
	
  }
  
  public function executeAgenteditscheduling(sfWebRequest $request)
  {
	$this->forward404Unless($conf_agent = Doctrine::getTable('ConfAgent')->find($request->getParameter('dbid')), sprintf('Object conf_agent does not exist (%s).', $request->getParameter('dbid')));
    $this->forward404Unless(fonivaFilter::isCurrentUserSuperTenant() || $conf_agent->getTenantFk() == fonivaFilter::getCurrentUserTenantId());
	$this->forward404Unless(fonivaFilter::canCurrentUserManageAllGroups() || fonivaFilter::canCurrentUserManageGroup($conf_agent->group_fk));
		
	$this->conf_agent = $conf_agent;
	
	/* assigned shifts */
	$this->shifts = Doctrine_Query::create() 
						->select('ca.dbid, ca.name')
						->from('ConfAgentShift ca')
						->orderBy('ca.name');
	
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$this->shifts->where('ca.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	$this->shifts = $this->shifts->execute();
	
	$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->getResponse()->addStylesheet('chosen/chosen.css', 'last');
	$this->getResponse()->addJavascript('chosen/chosen.jquery.min.js', 'last');
  }
  
  /*********   ***********************/
  /***   FORECASTING             *****/
  /*********   ***********************/
  public function executeForecasting(sfWebRequest $request)
  {
	$this->getResponse()->addStylesheet('chosen/chosen.css', 'last');
	$this->getResponse()->addJavascript('chosen/chosen.jquery.min.js', 'last');
	$this->getResponse()->addJavascript('chart.min.js', 'last');
	$this->getResponse()->addJavascript('forecasting.js', 'last');
	$this->getResponse()->addStylesheet('forecasting.css', 'last');
  }
  
  
  
  public function executeForecasting_loadhistform(sfWebRequest $request)
  {
	sfConfig::set('sf_web_debug', false);
	$this->setLayout(false);
	
	$this->queues = Doctrine_Query::create()
						->from('ConfQueue q')
						->orderBy('q.name');
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$this->queues->andWhere('q.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
		
	if(!fonivaFilter::canCurrentUserManageAllQueues())
	{
		$this->queues->leftJoin('q.ConfUserAssignedqueue a')
								->andWhere('a.user_fk = ?', fonivaFilter::getCurrentUser()->getDbid());
	}
	$this->queues = $this->queues->execute();
	
	$this->queue_r = array();
	foreach($this->queues as $q)
	{
		$this->queue_r[$q->dbid] = $q->name;
	}
	
	
	
	/* built defaults */
	$defparams = array();
	
	$rarray =  $request->getParameter('forecasting');
	if( isset($rarray['queue_id']) )
		$defparams['queue_id'] = $rarray['queue_id'];
		
	if( isset($rarray['start_date']) )
		$defparams['start_date'] = $rarray['start_date'];
	else
		$defparams['start_date'] = date('Y-m-d', mktime(date('H'), date('i'), date('s'), date('n') - 3, date('j'), date('Y')));
		
	if( isset($rarray['end_date']) )
		$defparams['end_date'] = $rarray['end_date'];
	else
		$defparams['end_date'] = date('Y-m-d');
	
	if( isset($rarray['step']) )
		$defparams['step'] = $rarray['step'];
	else
		$defparams['step'] = '15min';
		
	if( isset($rarray['date_range']) )
		$defparams['date_range'] = $rarray['date_range'];
	else
		$defparams['date_range'] = 'LAST_3MONTH';
		
	
	$this->forecastloadhistqueueform = new ForecastingloadhistoricalqueueForm($defparams);
	$this->forecastloadhistqueueform['queue_id']->getWidget()->setOption('choices', $this->queue_r);
  }
  
  
  public function executeForecasting_loadhistqueuedata(sfWebRequest $request)
  {
	sfConfig::set('sf_web_debug', false);
	$this->setLayout(false);
	
	$this->queueid = $request->getParameter('queueid');
	$this->start_date = $request->getParameter('start_date');
	$this->end_date = $request->getParameter('end_date');
	$this->stat_step = $request->getParameter('step');
	$this->stat_startdate_ext = $this->start_date.' 00:00:00';
	$this->stat_enddate_ext = $this->end_date.' 23:59:59';
	
	$this->forward404Unless($this->queue = Doctrine::getTable('ConfQueue')->find($this->queueid));
    $this->forward404Unless(fonivaFilter::isCurrentUserSuperTenant() || $this->queue->getTenantFk() == fonivaFilter::getCurrentUserTenantId()); 
	
	
	
	$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';
	}
	
	$this->nbweeks = ceil( strtotime($this->stat_enddate_ext) - strtotime($this->stat_startdate_ext) ) / (7*24*3600);
	if($this->nbweeks < 1)
		$this->nbweeks = 1;
	
	$fieldvalues = array();
	$pdo = Doctrine_Manager::getInstance()->getCurrentConnection()->getDbh();
	
	$query = '';
	
	$query_fields = 'SELECT DATE_FORMAT(r.covereddate, "%w") as covereddatedayname, 
							DATE_FORMAT(r.covereddate, "%H%i") as covereddatehour, ';
	
	$query_fields.= '
					SUM(r.nbcalls) as totalcallsreceived,
					ROUND(SUM(r.nbcalls)/'.$this->nbweeks.', 2) as totalcallsreceivedperweek,
					SUM(IF(r.queueresult = "DISTRIBUTED",r.nbcalls,0)) as totalcallsanswered,
					SUM(IF(r.queueresult = "ABANDON",r.nbcalls,0)) as totalcallsabandoned,
					ROUND(SUM(r.totalbridgeseconds)/'.$this->nbweeks.', 2) 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_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();
	}
	
	$query_filters .= ' AND r.queue_id = :stateltsvalue';
	$fieldvalues['stateltsvalue'] = $this->queueid;
	
	
	$query_groupby = ' group by DATE_FORMAT(r.covereddate, "%w"), DATE_FORMAT(r.covereddate, "%H%i")';
	$query_orderby = ' order by DATE_FORMAT(r.covereddate, "%w"), DATE_FORMAT(r.covereddate, "%H%i")';
	
	$query = $query_fields.''.$query_tables.''.$query_filters.''.$query_groupby.''.$query_orderby;
	//echo $query;
	//exit();
	
	$query = $pdo->prepare($query);
	$query->execute($fieldvalues);
	$this->data = $query->fetchAll();
	
	
	$this->sdata = array();
	$this->queue_totalcalls = 0;
	$this->queue_totalcalls_answered = 0;
	$this->queue_totalcalls_abandoned = 0;
	$this->queue_totalcalls_inservice = 0;
	$this->queue_totalconvtime = 0;
	$this->queue_aht = 0;
	$this->queue_servicelevel = 0.00;
	$this->queue_abandonrate = 0.00;
	
	foreach($this->data as $d)
	{
		if(!isset($this->sdata[$d['covereddatehour']]))
			$this->sdata[$d['covereddatehour']] = array();
		$this->sdata[$d['covereddatehour']][$d['covereddatedayname']] = $d;
		
		$this->queue_totalcalls += $d['totalcallsreceived'];
		$this->queue_totalcallsperweek += $d['totalcallsreceivedperweek'];
		$this->queue_totalcalls_answered += $d['totalcallsanswered'];
		$this->queue_totalcalls_abandoned += $d['totalcallsabandoned'];
		$this->queue_totalconvtime += $d['totalbridgeseconds'];
		$this->queue_totalcalls_inservice += $d['total_in_servicelevel'];
	}
	
	if($this->queue_totalcalls_answered > 0)
		$this->queue_aht = ceil($this->queue_totalconvtime/60/$this->queue_totalcalls_answered); 
	
	if($this->queue_totalcalls_answered > 0)
		$this->queue_servicelevel = $this->queue_totalcalls_inservice/$this->queue_totalcalls_answered*100;
		
	if($this->queue_totalcalls > 0)
		$this->queue_abandonrate = $this->queue_totalcalls_abandoned/$this->queue_totalcalls*100;
	
	$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');
	$this->getResponse()->addStylesheet('forecasting.css', 'last');
  }
  
  
  
  public function executeForecasting_loadprojectionform(sfWebRequest $request)
  {
	sfConfig::set('sf_web_debug', false);  
	$this->setLayout(false);
	
	$this->queueid = $request->getParameter('queueid');
	$this->hist_start_date = $request->getParameter('hist_start_date');
	$this->hist_end_date = $request->getParameter('hist_end_date');
	$this->hist_step = $request->getParameter('hist_step');
	$this->default_svl = $request->getParameter('default_svl');
	$this->default_abandonrate = $request->getParameter('default_abandonrate');
	$this->default_aht = $request->getParameter('default_aht');
	
	/* built defaults */
	$defparams = array();
	
	$rarray =  $request->getParameter('forecastingqueueprojection');
	$defparams['queue_id'] = $this->queueid;
	$defparams['hist_start_date'] = $this->hist_start_date;
	$defparams['hist_end_date'] = $this->hist_end_date;
	$defparams['hist_step'] = $this->hist_step;
	$defparams['start_date'] = date('Y-m-d', strtotime('Next sunday'));
	$defparams['end_date'] = date('Y-m-d', strtotime('Next sunday') + 6*24*3600);
	$defparams['date_range'] = 'LAST_3MONTH';
	$defparams['targetservicelevel'] = round($this->default_svl);
	$defparams['targetedabandonrate'] = round($this->default_abandonrate);
	$defparams['agentoccupationrate'] = '75';
	$defparams['minagents'] = '2';
	$defparams['agentsaht'] = round($this->default_aht*60);
	$defparams['agentshrinkage'] = '5';
	
	
	$this->forecastqueueprojectionform = new ForecastingqueueprojectionForm($defparams);
	
  }
  
  
  public function executeForecasting_loadqueueprojectiondata(sfWebRequest $request)
  {
	sfConfig::set('sf_web_debug', false);
	$this->setLayout(false);
	
	$this->queueid = $request->getParameter('queueid');
	$this->start_date = $request->getParameter('hist_start_date');
	$this->end_date = $request->getParameter('hist_end_date');
	$this->stat_step = $request->getParameter('hist_step');
	$this->stat_startdate_ext = $this->start_date.' 00:00:00';
	$this->stat_enddate_ext = $this->end_date.' 23:59:59';
	
	$this->projstart_date = $request->getParameter('start_date');
	$this->projend_date = $request->getParameter('end_date');
	
	$this->targetedsvl = $request->getParameter('targetedsvl');
	$this->targetedabandonrate = $request->getParameter('targetedabandonrate');
	$this->occupationrate = $request->getParameter('occupationrate');
	$this->minagents = $request->getParameter('minagents');
	$this->agentsaht = $request->getParameter('agentsaht');
	$this->shrinkage = $request->getParameter('shrinkage');
	
	
	
	$this->forward404Unless($this->queue = Doctrine::getTable('ConfQueue')->find($this->queueid));
    $this->forward404Unless(fonivaFilter::isCurrentUserSuperTenant() || $this->queue->getTenantFk() == fonivaFilter::getCurrentUserTenantId()); 
	
	
	
	$this->tableclassname = 'log_resume_daily_queue_record';
	$this->tableclassname_doctrine = 'LogResumeDailyQueueRecord';
	$this->stat_stepmin = 24*60;
	
	if($this->stat_step == 'hourly')
	{
		$this->tableclassname = 'log_resume_hourly_queue_record';
		$this->tableclassname_doctrine = 'LogResumeHourlyQueueRecord';
		$this->stat_stepmin = 60;
	}
	if($this->stat_step == '15min')
	{
		$this->tableclassname = 'log_resume_15min_queue_record';
		$this->tableclassname_doctrine = 'LogResume15minQueueRecord';
		
		$this->stat_stepmin = 15;
	}
	if($this->stat_step == '30min')
	{
		$this->tableclassname = 'log_resume_30min_queue_record';
		$this->tableclassname_doctrine = 'LogResume30minQueueRecord';
		
		$this->stat_stepmin = 30;
	}
	
	
	$this->nbweeks = ceil(( strtotime($this->stat_enddate_ext) - strtotime($this->stat_startdate_ext) ) / (7*24*3600));
	if($this->nbweeks < 1)
		$this->nbweeks = 1;	
	
	$fieldvalues = array();
	$pdo = Doctrine_Manager::getInstance()->getCurrentConnection()->getDbh();
	
	$query = '';
	
	$query_fields = 'SELECT r.covereddate as covereddate, ';
	
	
	$query_fields.= '
					SUM(r.nbcalls) as totalcallsreceived,
					SUM(IF(r.queueresult = "DISTRIBUTED",r.nbcalls,0)) as totalcallsanswered,
					SUM(IF(r.queueresult = "ABANDON",r.nbcalls,0)) as totalcallsabandoned,
					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_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();
	}
	
	$query_filters .= ' AND r.queue_id = :stateltsvalue';
	$fieldvalues['stateltsvalue'] = $this->queueid;
	
	
	$query_groupby = ' GROUP BY covereddate, queue_id ';
	$query_orderby = ' ORDER BY r.covereddate, queue_id ';
	
	$query = $query_fields.''.$query_tables.''.$query_filters.''.$query_groupby.''.$query_orderby;
	//echo $query;
	//exit();
	
	$query = $pdo->prepare($query);
	$query->execute($fieldvalues);
	$this->data = $query->fetchAll();
	
	
	$this->queue_totalcalls = 0;
	$this->queue_totalcalls_answered = 0;
	$this->queue_totalcalls_inservice = 0;
	$this->queue_totalconvtime = 0;
	$this->queue_aht = 0;
	$this->queue_servicelevel = 0.00;
	$this->queue_abandonrate = 0.00;
	
	$this->sdatacalls = array();
	$currentdate = $this->stat_startdate_ext;
	$datepos = 0;
	$sdatcnt = 0;
	while($currentdate < $this->stat_enddate_ext)
	{
		$this->sdatacalls[$sdatcnt] = 0;
		
		if(isset($this->data[$datepos]))
		{
			$d = $this->data[$datepos];
			if($currentdate == $d['covereddate'])
			{
				$this->sdatacalls[$sdatcnt] = $d['totalcallsreceived'];
				$datepos++;	
			}
		}			
		
		$currentdate = date("Y-m-d H:i:s", strtotime($currentdate) + $this->stat_stepmin * 60);
		$sdatcnt++;
	}
	
	
	//Next - Load the last 3 months data of this queue information - relations between nbagents elligible and nbcallanswered/received/abandon
	$mydatecolumn = 'DATE_FORMAT(DATE_SUB(snapdate, INTERVAL (MINUTE(snapdate)%30) MINUTE), "%%Y-%%m-%%d %%H:%%i:00")';
	
	if($this->stat_step == 'hourly')
	{
		$mydatecolumn = 'DATE_FORMAT(snapdate, "%Y-%m-%d %H:00:00")';
	}
	if($this->stat_step == '15min')
	{
		$mydatecolumn = 'DATE_FORMAT(DATE_SUB(snapdate, INTERVAL (MINUTE(snapdate)%15) MINUTE), "%Y-%m-%d %H:%i:00")';
	}
	if($this->stat_step == '30min')
	{
		$mydatecolumn = 'DATE_FORMAT(DATE_SUB(snapdate, INTERVAL (MINUTE(snapdate)%30) MINUTE), "%Y-%m-%d %H:%i:00")';
	}
	
	
	$query = '
		SELECT T1.avgwaitingcalls, T1.avgagentsincall, T1.avgloggedin, T1.avgagentswaiting,
			T2.nbcallreceived, T2.nbcallanswered, T2.nbcallabandonned, T2.in_servicelevel, T2.avgconvtime
			FROM 
		(
			SELECT '.$mydatecolumn.' as thecovereddate, 
			AVG(waitingcalls) as avgwaitingcalls, 
			AVG(agentsincall) as avgagentsincall, 
			AVG(agentsloggedin) as avgloggedin, 
			AVG(agentswaiting) as avgagentswaiting 
			FROM log_statsnap_queuestat 
			WHERE queuedbid = :queueid 
			AND snapdate >= "'.date("Y-m-d H:i:s", time()-3600*24*30).'"
			GROUP BY '.$mydatecolumn.'
			ORDER BY '.$mydatecolumn.'
		) 
		T1
		RIGHT JOIN
		(
			SELECT covereddate as thecovereddate, 
			SUM(nbcalls) as nbcallreceived, 
			SUM(IF(queueresult = "DISTRIBUTED",nbcalls,0)) as nbcallanswered, 
			SUM(IF(queueresult = "ABANDON",nbcalls,0)) as nbcallabandonned, 
			SUM(IF(in_servicelevel = "yes",nbcalls,0)) as in_servicelevel, 
			SUM(totalbridgeseconds)/SUM(IF(queueresult = "DISTRIBUTED",nbcalls,0)) as avgconvtime  
			FROM '.$this->tableclassname.' 
			WHERE covereddate >= "2015-07-02 10:00:00" AND covereddate >= "'.date("Y-m-d H:i:s", time()-3600*24*30).'"
			AND queue_id = :queueid
			GROUP BY covereddate
		)
		T2
		
		ON 
		T1.thecovereddate = T2.thecovereddate
		ORDER BY T2.thecovereddate
	';
	
	$fieldvalues = array();
	$fieldvalues['queueid'] = $this->queueid;
	//echo $query;
	//exit();
	
	$query = $pdo->prepare($query);
	$query->execute($fieldvalues);
	$this->historicalsnapdata = $query->fetchAll();
	//print_r($this->historicalsnapdata);
	//exit();
	
	
	//Now run the R scripts
	require_once(dirname(__FILE__).'/../thirdparty/phpr/RCore.php');
	require_once(dirname(__FILE__).'/../thirdparty/phpr/ROutputParser.php');
	require_once(dirname(__FILE__).'/../thirdparty/phpr/Engine/CommandLineREngine.php');

	$r = new RCore(new CommandLineREngine('/usr/bin/R'));
	$rProcess = $r->createInteractiveProcess();
	$rProcess->start();
	
	//do forecast of the calls for next period of time
	$rProcess->write('sdatacalls <- c('.implode(',', $this->sdatacalls).')');
	$rProcess->write('mytssdatacalls <- ts(sdatacalls, 1, '.$this->nbweeks.', '.(7 * 24 * 60 / $this->stat_stepmin).')');
	$rProcess->write('fit <- HoltWinters(mytssdatacalls)');
	$rProcess->write('library(forecast)');
	$rProcess->write('futuredata <- forecast(fit, '.(7 * 24 * 60 / $this->stat_stepmin).')');
	
	//$rProcess->write('futuredata');
	//echo $rProcess->getAllResult();
	//exit();
	
	$rProcess->write('write.csv(futuredata)');
	$this->projectionsr_csvdata =   $rProcess->getLastWriteOutput();
	
	
	//clear everything
	$rProcess->write('closeAllConnections()');
	$rProcess->write('rm(list=ls())');
	
	
	//do linear regression
	
	$matrixstr = '';
	$i = 0;
	$nbhistsnapdata = sizeof($this->historicalsnapdata);
	$matrixfilename = '/tmp/foniva-historicalsnapdata.'.rand(0,1000).time();
	$matrixfilecontent = '"avgwaitingcalls","avgagentsincall","avgloggedin","avgagentswaiting","nbcallreceived","nbcallanswered","nbcallabandonned","in_servicelevel","avgconvtime"
';
	file_put_contents($matrixfilename, $matrixfilecontent); //just to create the file
	
	foreach($this->historicalsnapdata as $hs)
	{
		$matrixfilecontent = '"'.(!empty($hs['avgwaitingcalls']) ? $hs['avgwaitingcalls'] : '0').
						'","'.
						(!empty($hs['avgagentsincall']) ? $hs['avgagentsincall'] : '0').
						'","'.
						(!empty($hs['avgloggedin']) ? $hs['avgloggedin'] : '0').
						'","'.
						(!empty($hs['avgagentswaiting']) ? $hs['avgagentswaiting'] : '0').
						'","'.
						(!empty($hs['nbcallreceived']) ? $hs['nbcallreceived'] : '0').
						'","'.
						(!empty($hs['nbcallanswered']) ? $hs['nbcallanswered'] : '0').
						'","'.
						(!empty($hs['nbcallabandonned']) ? $hs['nbcallabandonned'] : '0').
						'","'.
						(!empty($hs['in_servicelevel']) ? $hs['in_servicelevel'] : '0').
						'","'.
						(!empty($hs['avgconvtime']) ? $hs['avgconvtime'] : '0').'"
'
						;
		$i++;
		file_put_contents($matrixfilename, $matrixfilecontent,  FILE_APPEND);
	}
	//echo 'histsnap = matrix(c('.$matrixstr.'), ncol=9, byrow=TRUE)';
	//exit();
	
	$rProcess->write('histsnap = read.csv("'.$matrixfilename.'", header=TRUE)');
	
	//delete the data file
	unlink($matrixfilename);
	
	//select relevant variables only
	$rProcess->write('myvars = c("avgloggedin","nbcallreceived","nbcallanswered","nbcallabandonned","in_servicelevel","avgconvtime")');
	$rProcess->write('histsnaprel = histsnap[myvars]');
	$rProcess->write('histsnapfit = lm(avgloggedin ~ nbcallreceived + nbcallanswered + nbcallabandonned + in_servicelevel + avgconvtime, histsnaprel)');
	//$rProcess->write('histsnapfit');
	
	
	
	$this->projectionsr = array();
	$this->projectionsr_calls = array();
	$this->projectionsr_callsmin80 = array();
	$this->projectionsr_callsmax80 = array();
	$this->projectionsr_callsmin95 = array();
	$this->projectionsr_callsmax95 = array();
	
	$this->predictedr_nbagents = array();
	$plines = explode(PHP_EOL, $this->projectionsr_csvdata);
	
	$cumul_nbcalls_residus = 0;
	$pred_nbcalls_catstr = '';
	$pred_nbcalls_abandonned_catstr = '';
	$pred_nbcalls_answered_catstr = '';
	$pred_nbcalls_inservicelevel_catstr = '';
	$pred_avgconvtime_catstr = '';
	
	$cntpline = 0;
	foreach ($plines as $pline) 
	{
		$plinearr = str_getcsv($pline);
    	$this->projectionsr[] = $plinearr;
		
		$nbcalls = $plinearr[1];
		if($nbcalls < 0)
			$nbcalls = 0;
		$nbcalls = $nbcalls + $cumul_nbcalls_residus;
		$cumul_nbcalls_residus = $nbcalls - floor($nbcalls);
		$nbcalls = floor($nbcalls);
		array_push($this->projectionsr_calls, $nbcalls);
		array_push($this->projectionsr_callsmin80, $plinearr[2]);
		array_push($this->projectionsr_callsmax80, $plinearr[3]);
		array_push($this->projectionsr_callsmin95, $plinearr[4]);
		array_push($this->projectionsr_callsmax95, $plinearr[5]);
		
		
		//make nbagents predictions
		$pred_nbcalls = $nbcalls;
		$pred_nbcalls_abandonned = floor($nbcalls * $this->targetedabandonrate / 100);
		$pred_nbcalls_answered = $nbcalls - $pred_nbcalls_abandonned;
		$pred_nbcalls_inservicelevel = ceil($nbcalls * $this->targetedsvl / 100);
		$pred_avgconvtime = $this->agentsaht;
		
		$pred_nbcalls_catstr .= $pred_nbcalls;
		$pred_nbcalls_abandonned_catstr .= $pred_nbcalls_abandonned;
		$pred_nbcalls_answered_catstr .= $pred_nbcalls_answered;
		$pred_nbcalls_inservicelevel_catstr .= $pred_nbcalls_inservicelevel;
		$pred_avgconvtime_catstr .= $pred_avgconvtime;
		
		if($cntpline < sizeof($plines) - 1 )
		{
			$pred_nbcalls_catstr .= ',';
			$pred_nbcalls_abandonned_catstr .= ',';
			$pred_nbcalls_answered_catstr .= ',';
			$pred_nbcalls_inservicelevel_catstr .= ',';
			$pred_avgconvtime_catstr .= ',';
		}
		$cntpline++;
		//$rProcess->write('predict(histsnapfit,data.frame(nbcallreceived='.$pred_nbcalls.', nbcallanswered='.$pred_nbcalls_answered.', nbcallabandonned='.$pred_nbcalls_abandonned.', in_servicelevel='.$pred_nbcalls_inservicelevel.', avgconvtime='.$pred_avgconvtime.'))');
		
		//$pred_nbrequiredagents = $rProcess->getLastWriteOutput(true);
		//array_push($this->predictedr_nbagents, $pred_nbrequiredagents);
		
		
	}
	
	$rProcess->write('pred_nbcalls_catstr=c('.$pred_nbcalls_catstr.')');
	$rProcess->write('pred_nbcalls_abandonned_catstr=c('.$pred_nbcalls_abandonned_catstr.')');
	$rProcess->write('pred_nbcalls_answered_catstr=c('.$pred_nbcalls_answered_catstr.')');
	$rProcess->write('pred_nbcalls_inservicelevel_catstr=c('.$pred_nbcalls_inservicelevel_catstr.')');
	$rProcess->write('pred_avgconvtime_catstr=c('.$pred_avgconvtime_catstr.')');
	
	
	$rProcess->write('predicteddata = predict(histsnapfit,data.frame('
							.'nbcallreceived='
							.'pred_nbcalls_catstr'
							.', nbcallanswered='
							.'pred_nbcalls_abandonned_catstr'
							.', nbcallabandonned='
							.'pred_nbcalls_answered_catstr'
							.', in_servicelevel='
							.'pred_nbcalls_inservicelevel_catstr'
							.', avgconvtime='
							.'pred_avgconvtime_catstr'
							.'))'
							);
	$rProcess->write('matrix(predicteddata)');
	//echo $rProcess->getAllResult();
	$rOutputParser = new ROutputParser();
	$this->predictedr_nbagents = $rOutputParser->numericVector($rProcess->getLastWriteOutput());
	//print_r($this->predictedr_nbagents);
	//exit();
  }
  
  /*********   ***********************/
  /***   OTHERS AJAX CALLS       *****/
  /*********   ***********************/
  public function executeGetagentsingroup(sfWebRequest $request)
  {
	sfConfig::set('sf_web_debug', false);  
	$this->getResponse()->setContentType('text/json');
	$this->setLayout(false);
	
	//$this->forward404Unless($this->groupid = $request->getParameter('groupid'));
	$this->groupid = $request->getParameter('groupid');

	$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->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
	{
		$this->agents->andWhere('ca.group_fk in ?', array($this->groupids_list));
	}
	
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$this->agents->andWhere('ca.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	$this->agents = $this->agents->execute();
  }
  
  public function executeGetagentschedule(sfWebRequest $request)
  {
	sfConfig::set('sf_web_debug', false);  
	$this->getResponse()->setContentType('text/json');
	$this->setLayout(false);
	
	$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());
	$this->conf_agent_info = $this->conf_agent->ConfAgentInfo;
	
	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();
	}
	
	$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->sheduleevents = Doctrine_Query::create()
							->select('a.dbid, a.agent_fk, a.activity as activityname, a.starttime, a.endtime, ac.colorcode as colorcode')
							->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))
							->execute();
  }
  
  public function executeGetallagentschedules(sfWebRequest $request)
  {
	sfConfig::set('sf_web_debug', false);  
	$this->getResponse()->setContentType('text/json');
	$this->setLayout(false);
	/*
	$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());
	$this->conf_agent_info = $this->conf_agent->ConfAgentInfo;
	
	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();
	}
	*/
	
	$this->forward404Unless($this->starttime = $request->getParameter('from'));
	$this->forward404Unless($this->endtime = $request->getParameter('to'));
	$this->starttime = $this->starttime.' 00:00:00';
	$this->endtime = $this->endtime.' 23:59:59';
	$this->agentid = $request->getParameter('agentid');
	$this->groupid = $request->getParameter('groupid');
	
	
	$this->sheduleevents = Doctrine_Query::create()
							->select('a.dbid, a.agent_fk, a.activity as activityname, a.starttime, a.endtime, ac.colorcode as colorcode, 
										ac.ispaid as ispaid, ac.paidpercentage as paidpercentage');
	
	$this->sheduleevents = $this->sheduleevents->from('ConfAgentScheduleEvent a');
	
	
	if($this->groupid != NULL && $this->groupid != '')
		$this->sheduleevents = $this->sheduleevents->leftJoin('a.ConfAgent ag ON ag.dbid = a.agent_fk');
		
		
	$this->sheduleevents = $this->sheduleevents->leftJoin('a.ConfAgentActivity ac ON ac.name = a.activity')
									->where('starttime >= ? and endtime <= ?', array($this->starttime, $this->endtime));
	
	if($this->groupid != NULL && $this->groupid != '')
		$this->sheduleevents = $this->sheduleevents->andWhere('ag.group_fk = ?', $this->groupid)
													->andWhere('ag.active = "yes"');
							
	if($this->agentid != NULL && $this->agentid != '')
		$this->sheduleevents = $this->sheduleevents->andWhere('a.agent_fk = ?', $this->agentid);
		
	
	$this->sheduleevents = $this->sheduleevents->execute();
  }
  
  public function executeAgentassignshift(sfWebRequest $request)
  {
	 sfConfig::set('sf_web_debug', false);  
	$this->getResponse()->setContentType('text/json');
	$this->setLayout(false);
	
	$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());
	$this->forward404Unless($this->shiftid = $request->getParameter('shiftid'));
	$this->forward404Unless($this->conf_shift = Doctrine::getTable('ConfAgentShift')->find($request->getParameter('shiftid')), sprintf('Object conf_shift does not exist (%s).', $request->getParameter('shiftid')));
    $this->forward404Unless(fonivaFilter::isCurrentUserSuperTenant() || $this->conf_shift->getTenantFk() == fonivaFilter::getCurrentUserTenantId());
	$this->forward404Unless($this->weekstartingat = $request->getParameter('weekstartingat'));
	
	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();
	}
	
	
	//do the assignment
	//will do a flat query to speed things up
	
	//get the pdo connection 
	$pdo = Doctrine_Manager::getInstance()->getCurrentConnection()->getDbh();
	
	$fieldvalues = array();
	$flushquery = 'DELETE FROM conf_agent_schedule_event WHERE agent_fk = :agentid AND starttime >= CONCAT(:weekstartingat, " 00:00:00") AND endtime <= CONCAT(DATE_ADD(:weekstartingat, INTERVAL 6 DAY), " 23:59:59")';
	$fieldvalues['agentid'] = $this->agentid;
	$fieldvalues['weekstartingat'] = $this->weekstartingat;
	$flushquery = $pdo->prepare($flushquery);
	$flushquery->execute($fieldvalues);
	
	
	$fieldvalues = array();
	$casesql = '(CASE dayofweek
    				WHEN "sunday" THEN 0
					WHEN "monday" THEN 1
					WHEN "tuesday" THEN 2
					WHEN "wednesday" THEN 3
					WHEN "thursday" THEN 4
					WHEN "friday" THEN 5
					WHEN "saturday" THEN 6
					ELSE 0
				END)';
	$insertquery = 'INSERT INTO conf_agent_schedule_event (agent_fk, activity, starttime, endtime) ';
	$insertquery .= ' SELECT :agentid as agent_fk, 
								activity ,  
								CONCAT(DATE_ADD(:weekstartingat, INTERVAL '.$casesql.' DAY), " ", starttime) as schedulefullstarttime, 
								CONCAT(DATE_ADD(:weekstartingat, INTERVAL '.$casesql.' DAY), " ", endtime)  as schedulefullendtime
								FROM conf_agent_shift_event 
								WHERE shift_fk = :shiftid  ';
  	$insertquery = $pdo->prepare($insertquery);
	$fieldvalues['agentid'] = $this->agentid;
	$fieldvalues['shiftid'] = $this->shiftid;
	$fieldvalues['weekstartingat'] = $this->weekstartingat;
	
	$insertquery->execute($fieldvalues);
	
	//logs 
	$log = new LogAgentschedulesChangelog();
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$log->setTenantDbid(fonivaFilter::getCurrentUserTenantId());
	$log->setOperationtype('AGENT_ASSIGN_SHIFT');
	$log->setAgentid($this->agentid);
	$log->setAgentusername($this->conf_agent->username);
	//$log->setGroupid();
	//$log->setGroupname();
	//$log->setAgentsidList();
	$log->setShiftid($this->shiftid);
	$log->setShiftname($this->conf_shift->name);
	$log->setWeekstartingat($this->weekstartingat);
	//$log->setFromweek();
	//$log->setToweeks();
	//$log->setFromday();
	//$log->setTodays();
	$log->setByuser($this->getUser()->getUsername());
	$log->save();
	
	exit();
  }
  
  public function executeAgentsassignschedulefromweek(sfWebRequest $request)
  {
	sfConfig::set('sf_web_debug', false);  
	$this->getResponse()->setContentType('text/json');
	$this->setLayout(false);
	
	//$this->forward404Unless($this->agentid = $request->getParameter('agentid'));
	$this->agentid = $request->getParameter('agentid');
	//$this->forward404Unless($this->groupid = $request->getParameter('groupid'));
	$this->groupid = $request->getParameter('groupid');
	$this->forward404Unless($this->fromweek = $request->getParameter('fromweek'));
	$this->forward404Unless($this->toweeks = $request->getParameter('toweeks'));
	
	
	$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 != '' && $this->groupid == $g->getDbid())
			$allowed = true;
	}
	
	if($this->groupid != '' && !$allowed)
		$this->forward404();
	
	
	//get all users with their ID
	$this->agentids = Doctrine_Query::create()
								->select('a.dbid')
								->from('ConfAgent a');
	if($this->groupid != '' && $this->agentid != '')
		$this->agentids = $this->agentids->where('a.dbid = ?', $this->agentid);
	else if($this->groupid != '' && $this->agentid == '')
		$this->agentids = $this->agentids->where('a.group_fk = ?', $this->groupid);
	else if($this->groupid == '')
		$this->agentids = $this->agentids->whereIn('a.group_fk', $this->groupids_list);
	$this->agentids = $this->agentids->execute();
	
	$this->agentids_liststr = '';
	$this->agentids_listsqlstr = '';
	$cnt = 0;
	foreach($this->agentids as $ai)
	{
		$cnt++;
		$this->agentids_liststr .= '"'.$ai->dbid.'"';
		$this->agentids_listsqlstr .= '("'.$ai->dbid.'")';
		if($cnt <= sizeof($this->agentids)-1) 
		{
			$this->agentids_liststr .= ',';
			$this->agentids_listsqlstr .= ',';
		}
	}
	
	
	$this->toweeks_listsqlstr = '';
	$this->toweeks_list = '';
	for($i = 0; $i < sizeof($this->toweeks); $i++)
	{
		$this->toweeks_listsqlstr .= '("'.$this->toweeks[$i].'")';
		$this->toweeks_list .= $this->toweeks[$i];
		if($i < sizeof($this->toweeks)-1) 
		{
			$this->toweeks_listsqlstr .= ',';
			$this->toweeks_list .= ',';
		}
	}
	
	//exit();
	//do the assignment
	//will do a flat query to speed things up
	
	//get the pdo connection 
	$pdo = Doctrine_Manager::getInstance()->getCurrentConnection()->getDbh();
	
	for($i = 0; $i < sizeof($this->toweeks); $i++)
	{
		$toweek = $this->toweeks[$i];
		$fieldvalues = array();
		$flushquery = 'DELETE FROM conf_agent_schedule_event WHERE 1';
		if(sizeof($this->agentids) > 0)
			$flushquery .= ' AND agent_fk IN ('.$this->agentids_liststr.') ';
		$flushquery .= ' AND endtime >= CONCAT(:weekstartingat, " 00:00:00") AND starttime <= CONCAT(DATE_ADD(:weekstartingat, INTERVAL 6 DAY), " 23:59:59")';
		$fieldvalues['weekstartingat'] = $toweek;
		$flushquery = $pdo->prepare($flushquery);
		$flushquery->execute($fieldvalues);
	}
	
	//$temptable = 'CREATE TEMPORARY TABLE tmpListAgents (agentid int(11) not null)';
	//$temptable = $pdo->prepare($temptable);
	//$temptable = $temptable->execute();
	
	//$insertquery = 'INSERT INTO tmpListAgents VALUES '.$this->agentids_listsqlstr;
	//$insertquery = $pdo->prepare($insertquery);
	//$insertquery = $temptable->execute();
	
	$temptable = 'CREATE TEMPORARY TABLE tmpListToWeeks (toweek varchar(50) not null)';
	$temptable = $pdo->prepare($temptable);
	$temptable = $temptable->execute();
	
	$insertquery = 'INSERT INTO tmpListToWeeks VALUES '.$this->toweeks_listsqlstr;
	
	$insertquery = $pdo->prepare($insertquery);
	$insertquery = $insertquery->execute();
	
	
	$insertquery = 'INSERT INTO conf_agent_schedule_event (agent_fk, activity, starttime, endtime, lastmodification_by, created_at, updated_at) ';
	$insertquery .= ' SELECT conf_agent_schedule_event.agent_fk as agent_fk, 
									conf_agent_schedule_event.activity as activity,  
									ADDTIME(CONCAT(tmpListToWeeks.toweek, " 00:00:00"), TIMEDIFF(conf_agent_schedule_event.starttime, "'.$this->fromweek.' 00:00:00")) as schedulefullstarttime, 
									ADDTIME(CONCAT(tmpListToWeeks.toweek, " 00:00:00"), TIMEDIFF(conf_agent_schedule_event.endtime, "'.$this->fromweek.' 00:00:00")) as schedulefullendtime,
									"'.$this->getUser()->getUsername().'" as lastmodification_by,
									NOW() as created_at,
									NOW() as updated_at
									FROM conf_agent_schedule_event, tmpListToWeeks
									WHERE conf_agent_schedule_event.agent_fk IN  ('.$this->agentids_liststr.') 
									AND conf_agent_schedule_event.endtime >= CONCAT("'.$this->fromweek.'", " 00:00:00") AND conf_agent_schedule_event.starttime <= CONCAT(DATE_ADD("'.$this->fromweek.'", INTERVAL 6 DAY), " 23:59:59")';
	
	$insertquery = $pdo->prepare($insertquery);
	$insertquery->execute();
	
	
	
	//logs 
	$log = new LogAgentschedulesChangelog();
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$log->setTenantDbid(fonivaFilter::getCurrentUserTenantId());
	$log->setOperationtype('AGENT_ASSIGN_SCHEDULE_FROM_WEEK');
	$log->setAgentid($this->agentid);
	//$log->setAgentusername($this->conf_agent->username);
	$log->setGroupid($this->groupid);
	//$log->setGroupname();
	$log->setAgentsidList($this->agentids_liststr);
	//$log->setShiftid($this->shiftid);
	//$log->setShiftname($this->conf_shift->name);
	//$log->setWeekstartingat($this->weekstartingat);
	$log->setFromweek($this->fromweek);
	$log->setToweeks($this->toweeks_list);
	//$log->setFromday();
	//$log->setTodays();
	$log->setByuser($this->getUser()->getUsername());
	$log->save();
	
	exit();
  }
  
  public function executeAgentsassignschedulefromday(sfWebRequest $request)
  {
	sfConfig::set('sf_web_debug', false);  
	$this->getResponse()->setContentType('text/json');
	$this->setLayout(false);
	
	//$this->forward404Unless($this->agentid = $request->getParameter('agentid'));
	$this->agentid = $request->getParameter('agentid');
	//$this->forward404Unless($this->groupid = $request->getParameter('groupid'));
	$this->groupid = $request->getParameter('groupid');
	$this->forward404Unless($this->fromday = $request->getParameter('fromday'));
	$this->forward404Unless($this->todayfrom = $request->getParameter('todayfrom'));
	$this->forward404Unless($this->todayto = $request->getParameter('todayto'));
	
	
	$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 != '' && $this->groupid == $g->getDbid())
			$allowed = true;
	}
	
	if($this->groupid != '' && !$allowed)
		$this->forward404();
	
	
	//get all users with their ID
	$this->agentids = Doctrine_Query::create()
								->select('a.dbid')
								->from('ConfAgent a');
	if($this->groupid != '' && $this->agentid != '')
		$this->agentids = $this->agentids->where('a.dbid = ?', $this->agentid);
	else if($this->groupid != '' && $this->agentid == '')
		$this->agentids = $this->agentids->where('a.group_fk = ?', $this->groupid);
	else if($this->groupid == '')
		$this->agentids = $this->agentids->whereIn('a.group_fk', $this->groupids_list);
	$this->agentids = $this->agentids->execute();
	
	$this->agentids_liststr = '';
	$this->agentids_listsqlstr = '';
	$cnt = 0;
	foreach($this->agentids as $ai)
	{
		$cnt++;
		$this->agentids_liststr .= '"'.$ai->dbid.'"';
		$this->agentids_listsqlstr .= '("'.$ai->dbid.'")';
		if($cnt <= sizeof($this->agentids)-1) 
		{
			$this->agentids_liststr .= ',';
			$this->agentids_listsqlstr .= ',';
		}
	}
	
	
	$this->todays_listsqlstr = '';
	$this->todays_list = '';
	$tmpday = $this->todayfrom;
	while($tmpday <= $this->todayto)
	{
		$this->todays_listsqlstr .= '("'.$tmpday.'")';
		$this->todays_list .= $tmpday;
		if($tmpday != $this->todayto) 
		{
			$this->todays_listsqlstr .= ',';
			$this->todays_list .= ',';
		}
		
		$tmpday = date('Y-m-d',  strtotime('+1 day', strtotime($tmpday)));
	}
	
	
	//exit();
	//do the assignment
	//will do a flat query to speed things up
	
	//get the pdo connection 
	$pdo = Doctrine_Manager::getInstance()->getCurrentConnection()->getDbh();
	
	$flushquery = 'DELETE FROM conf_agent_schedule_event WHERE 1';
	if(sizeof($this->agentids) > 0)
		$flushquery .= ' AND agent_fk IN ('.$this->agentids_liststr.') ';
	$flushquery .= ' AND endtime >= CONCAT(:todayfrom, " 00:00:00") AND starttime <= CONCAT(:todayto, " 23:59:59")';
	$flushquery .= ' AND !(endtime >= CONCAT(:fromday, " 00:00:00") AND starttime <= CONCAT(:fromday, " 23:59:59"))';
	$fieldvalues['todayfrom'] = $this->todayfrom;
	$fieldvalues['todayto'] = $this->todayto;
	$fieldvalues['fromday'] = $this->fromday;
	
	$flushquery = $pdo->prepare($flushquery);
	$flushquery->execute($fieldvalues);
	
	/*
	for($i = 0; $i < sizeof($this->toweeks); $i++)
	{
		$toweek = $this->toweeks[$i];
		$fieldvalues = array();
		$flushquery = 'DELETE FROM conf_agent_schedule_event WHERE 1';
		if(sizeof($this->agentids) > 0)
			$flushquery .= ' AND agent_fk IN ('.$this->agentids_liststr.') ';
		$flushquery .= ' AND endtime >= CONCAT(:weekstartingat, " 00:00:00") AND starttime <= CONCAT(DATE_ADD(:weekstartingat, INTERVAL 6 DAY), " 23:59:59")';
		$fieldvalues['weekstartingat'] = $toweek;
		$flushquery = $pdo->prepare($flushquery);
		$flushquery->execute($fieldvalues);
	}
	*/
	
	//$temptable = 'CREATE TEMPORARY TABLE tmpListAgents (agentid int(11) not null)';
	//$temptable = $pdo->prepare($temptable);
	//$temptable = $temptable->execute();
	
	//$insertquery = 'INSERT INTO tmpListAgents VALUES '.$this->agentids_listsqlstr;
	//$insertquery = $pdo->prepare($insertquery);
	//$insertquery = $temptable->execute();
	
	$temptable = 'CREATE TEMPORARY TABLE tmpListToDays (today varchar(50) not null)';
	$temptable = $pdo->prepare($temptable);
	$temptable = $temptable->execute();
	
	$insertquery = 'INSERT INTO tmpListToDays VALUES '.$this->todays_listsqlstr;
	
	$insertquery = $pdo->prepare($insertquery);
	$insertquery = $insertquery->execute();
	
	
	$insertquery = 'INSERT INTO conf_agent_schedule_event (agent_fk, activity, starttime, endtime, lastmodification_by, created_at, updated_at) ';
	$insertquery .= ' SELECT conf_agent_schedule_event.agent_fk as agent_fk, 
									conf_agent_schedule_event.activity as activity,  
									CONCAT(tmpListToDays.today, " ", DATE_FORMAT(conf_agent_schedule_event.starttime, "%H:%i:%d")) as schedulefullstarttime, 
									CONCAT(tmpListToDays.today, " ", DATE_FORMAT(conf_agent_schedule_event.endtime, "%H:%i:%d")) as schedulefullendtime,
									"'.$this->getUser()->getUsername().'" as lastmodification_by,
									NOW() as created_at,
									NOW() as updated_at
									FROM conf_agent_schedule_event, tmpListToDays
									WHERE conf_agent_schedule_event.agent_fk IN  ('.$this->agentids_liststr.') 
									AND conf_agent_schedule_event.endtime >= CONCAT("'.$this->fromday.'", " 00:00:00") AND conf_agent_schedule_event.starttime <= CONCAT(DATE_ADD("'.$this->fromday.'", INTERVAL 6 DAY), " 23:59:59")';
	
	$insertquery = $pdo->prepare($insertquery);
	$insertquery->execute();
	
	//logs 
	$log = new LogAgentschedulesChangelog();
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$log->setTenantDbid(fonivaFilter::getCurrentUserTenantId());
	$log->setOperationtype('AGENT_ASSIGN_SCHEDULE_FROM_DAY');
	$log->setAgentid($this->agentid);
	//$log->setAgentusername($this->conf_agent->username);
	$log->setGroupid($this->groupid);
	//$log->setGroupname();
	$log->setAgentsidList($this->agentids_liststr);
	//$log->setShiftid($this->shiftid);
	//$log->setShiftname($this->conf_shift->name);
	//$log->setWeekstartingat($this->weekstartingat);
	//$log->setFromweek($this->fromweek);
	//$log->setToweeks($this->toweeks_list);
	$log->setFromday($this->todayfrom);
	$log->setTodays($this->todays_list);
	$log->setByuser($this->getUser()->getUsername());
	$log->save();
	
	
	
	exit();
  }
  
  public function executeAgentsassignscheduleshift(sfWebRequest $request)
  {
	sfConfig::set('sf_web_debug', false);  
	$this->getResponse()->setContentType('text/json');
	$this->setLayout(false);
	
	//$this->forward404Unless($this->agentid = $request->getParameter('agentid'));
	$this->agentid = $request->getParameter('agentid');
	//$this->forward404Unless($this->groupid = $request->getParameter('groupid'));
	$this->groupid = $request->getParameter('groupid');
	$this->forward404Unless($this->shiftid = $request->getParameter('shiftid'));
	$this->forward404Unless($this->toweeks = $request->getParameter('toweeks'));
	
	
	$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 != '' && $this->groupid == $g->getDbid())
			$allowed = true;
	}
	
	if($this->groupid != '' && !$allowed)
		$this->forward404();
	
	
	//get all users with their ID
	$this->agentids = Doctrine_Query::create()
								->select('a.dbid')
								->from('ConfAgent a');
	if($this->groupid != '' && $this->agentid != '')
		$this->agentids = $this->agentids->where('a.dbid = ?', $this->agentid);
	else if($this->groupid != '' && $this->agentid == '')
		$this->agentids = $this->agentids->where('a.group_fk = ?', $this->groupid);
	else if($this->groupid == '')
		$this->agentids = $this->agentids->whereIn('a.group_fk', $this->groupids_list);
	$this->agentids = $this->agentids->execute();
	
	$this->agentids_liststr = '';
	$this->agentids_listsqlstr = '';
	$cnt = 0;
	foreach($this->agentids as $ai)
	{
		$cnt++;
		$this->agentids_liststr .= '"'.$ai->dbid.'"';
		$this->agentids_listsqlstr .= '("'.$ai->dbid.'")';
		if($cnt <= sizeof($this->agentids)-1) 
		{
			$this->agentids_liststr .= ',';
			$this->agentids_listsqlstr .= ',';
		}
	}
	
	
	$this->toweeks_listsqlstr = '';
	$this->toweeks_list = '';
	for($i = 0; $i < sizeof($this->toweeks); $i++)
	{
		$this->toweeks_listsqlstr .= '("'.$this->toweeks[$i].'")';
		$this->toweeks_list .= $this->toweeks[$i];
		if($i < sizeof($this->toweeks)-1) 
		{
			$this->toweeks_listsqlstr .= ',';
			$this->toweeks_list .= ',';
		}
	}
	
	//exit();
	//do the assignment
	//will do a flat query to speed things up
	
	//get the pdo connection 
	$pdo = Doctrine_Manager::getInstance()->getCurrentConnection()->getDbh();
	
	for($i = 0; $i < sizeof($this->toweeks); $i++)
	{
		$toweek = $this->toweeks[$i];
		$fieldvalues = array();
		$flushquery = 'DELETE FROM conf_agent_schedule_event WHERE 1';
		if(sizeof($this->agentids) > 0)
			$flushquery .= ' AND agent_fk IN ('.$this->agentids_liststr.') ';
		$flushquery .= ' AND endtime >= CONCAT(:weekstartingat, " 00:00:00") AND starttime <= CONCAT(DATE_ADD(:weekstartingat, INTERVAL 6 DAY), " 23:59:59")';
		$fieldvalues['weekstartingat'] = $toweek;
		$flushquery = $pdo->prepare($flushquery);
		$flushquery->execute($fieldvalues);
	}
	
	$temptable = 'CREATE TEMPORARY TABLE tmpListAgents (agentid int(11) not null)';
	$temptable = $pdo->prepare($temptable);
	$temptable = $temptable->execute();
	
	$insertquery = 'INSERT INTO tmpListAgents VALUES '.$this->agentids_listsqlstr;
	$insertquery = $pdo->prepare($insertquery);
	$insertquery = $insertquery->execute();
	
	$temptable = 'CREATE TEMPORARY TABLE tmpListToWeeks (toweek varchar(50) not null)';
	$temptable = $pdo->prepare($temptable);
	$temptable = $temptable->execute();
	
	$insertquery = 'INSERT INTO tmpListToWeeks VALUES '.$this->toweeks_listsqlstr;
	
	$insertquery = $pdo->prepare($insertquery);
	$insertquery = $insertquery->execute();
	
	
	
	
	
	
	$fieldvalues = array();
	$casesql = '(CASE dayofweek
    				WHEN "sunday" THEN 0
					WHEN "monday" THEN 1
					WHEN "tuesday" THEN 2
					WHEN "wednesday" THEN 3
					WHEN "thursday" THEN 4
					WHEN "friday" THEN 5
					WHEN "saturday" THEN 6
					ELSE 0
				END)';
	$insertquery = 'INSERT INTO conf_agent_schedule_event (agent_fk, activity, starttime, endtime) ';
	$insertquery .= ' SELECT tmpListAgents.agentid as agent_fk, 
								activity ,  
								CONCAT(DATE_ADD(tmpListToWeeks.toweek, INTERVAL '.$casesql.' DAY), " ", starttime) as schedulefullstarttime, 
								CONCAT(DATE_ADD(tmpListToWeeks.toweek, INTERVAL '.$casesql.' DAY), " ", endtime)  as schedulefullendtime
								FROM conf_agent_shift_event, tmpListToWeeks, tmpListAgents
								WHERE shift_fk = :shiftid  ';
  	$insertquery = $pdo->prepare($insertquery);
	$fieldvalues['shiftid'] = $this->shiftid;
	
	$insertquery->execute($fieldvalues);
	
	
	//logs 
	$log = new LogAgentschedulesChangelog();
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$log->setTenantDbid(fonivaFilter::getCurrentUserTenantId());
	$log->setOperationtype('AGENT_ASSIGN_SCHEDULE_SHIFT');
	$log->setAgentid($this->agentid);
	//$log->setAgentusername($this->conf_agent->username);
	$log->setGroupid($this->groupid);
	//$log->setGroupname();
	$log->setAgentsidList($this->agentids_liststr);
	$log->setShiftid($this->shiftid);
	//$log->setShiftname($this->conf_shift->name);
	//$log->setWeekstartingat($this->weekstartingat);
	//$log->setFromweek($this->fromweek);
	$log->setToweeks($this->toweeks_list);
	$log->setFromday($this->todayfrom);
	$log->setTodays($this->todays_list);
	$log->setByuser($this->getUser()->getUsername());
	$log->save();
	
	/*
	$insertquery = 'INSERT INTO conf_agent_schedule_event (agent_fk, activity, starttime, endtime, lastmodification_by, created_at, updated_at) ';
	$insertquery .= ' SELECT conf_agent_schedule_event.agent_fk as agent_fk, 
									conf_agent_schedule_event.activity as activity,  
									ADDTIME(CONCAT(tmpListToWeeks.toweek, " 00:00:00"), TIMEDIFF(conf_agent_schedule_event.starttime, "'.$this->fromweek.' 00:00:00")) as schedulefullstarttime, 
									ADDTIME(CONCAT(tmpListToWeeks.toweek, " 00:00:00"), TIMEDIFF(conf_agent_schedule_event.endtime, "'.$this->fromweek.' 00:00:00")) as schedulefullendtime,
									"'.$this->getUser()->getUsername().'" as lastmodification_by,
									NOW() as created_at,
									NOW() as updated_at
									FROM conf_agent_schedule_event, tmpListToWeeks
									WHERE conf_agent_schedule_event.agent_fk IN  ('.$this->agentids_liststr.') 
									AND conf_agent_schedule_event.endtime >= CONCAT("'.$this->fromweek.'", " 00:00:00") AND conf_agent_schedule_event.starttime <= CONCAT(DATE_ADD("'.$this->fromweek.'", INTERVAL 6 DAY), " 23:59:59")';
	
	$insertquery = $pdo->prepare($insertquery);
	$insertquery->execute();
	*/
	
	exit();
  }
  
  public function executeAgentclearschedule(sfWebRequest $request)
  {
	 sfConfig::set('sf_web_debug', false);  
	$this->getResponse()->setContentType('text/json');
	$this->setLayout(false);
	
	$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());
	$this->forward404Unless($this->weekstartingat = $request->getParameter('weekstartingat'));
	
	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();
	}
	
	
	//do the assignment
	//will do a flat query to speed things up
	
	//get the pdo connection 
	$pdo = Doctrine_Manager::getInstance()->getCurrentConnection()->getDbh();
	
	$fieldvalues = array();
	$flushquery = 'DELETE FROM conf_agent_schedule_event WHERE agent_fk = :agentid AND starttime >= CONCAT(:weekstartingat, " 00:00:00") AND endtime <= CONCAT(DATE_ADD(:weekstartingat, INTERVAL 6 DAY), " 23:59:59")';
	$fieldvalues['agentid'] = $this->agentid;
	$fieldvalues['weekstartingat'] = $this->weekstartingat;
	$flushquery = $pdo->prepare($flushquery);
	$flushquery->execute($fieldvalues);
	
	
	//logs 
	$log = new LogAgentschedulesChangelog();
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$log->setTenantDbid(fonivaFilter::getCurrentUserTenantId());
	$log->setOperationtype('AGENT_CLEAR_SCHEDULE');
	$log->setAgentid($this->agentid);
	//$log->setAgentusername($this->conf_agent->username);
	//$log->setGroupid($this->groupid);
	//$log->setGroupname();
	//$log->setAgentsidList($this->agentids_liststr);
	//$log->setShiftid($this->shiftid);
	//$log->setShiftname($this->conf_shift->name);
	$log->setWeekstartingat($this->weekstartingat);
	//$log->setFromweek($this->fromweek);
	//$log->setToweeks($this->toweeks_list);
	//$log->setFromday($this->todayfrom);
	//$log->setTodays($this->todays_list);
	$log->setByuser($this->getUser()->getUsername());
	$log->save();
  }
  
  public function executeSaveagentscheduleevent(sfWebRequest $request)
  {
	 //$this->forward404Unless($request->getParameter('eventdbid'));
	 $this->forward404Unless($request->getParameter('agentdbid'));
	 
	 if($request->getParameter('eventdbid') != null && $request->getParameter('eventdbid') != "")
	 {
		 Doctrine_Query::create()
		  ->delete()
		  ->from('ConfAgentScheduleEvent')
		  ->andWhere('dbid = ? and agent_fk = ?', array($request->getParameter('eventdbid'), $request->getParameter('agentdbid')))
		  ->execute();
	 }
	 
	 $se = new ConfAgentScheduleEvent();
	 $se->setAgentFk($request->getParameter('agentdbid'));
	 $se->setActivity($request->getParameter('activity'));
	 $se->setStarttime($request->getParameter('starttime'));
	 $se->setEndtime($request->getParameter('endtime'));
	 $se->setActivitynote($request->getParameter('activitynote'));
	 $se->setLastmodificationBy($this->getUser()->getUsername());
	 $se->save();
	 return sfView::HEADER_ONLY;
  }
  
  
  public function executeScheduleeventdataprocessor(sfWebRequest $request)
  {
	  //$d_ids = $request->getParameter('ids');
	  //$d_ids = explode(",", $d_ids);
	  //for($i = 0; $i < sizeof($d_ids); $i++)
	  //{
		  //$id = $d_ids[$i];
		  $id = $request->getParameter('ids');
		  $this->operation = $request->getParameter($id.'_!nativeeditor_status');
		  if($this->operation == 'inserted')
		  {
			$agentid = $request->getParameter($id.'_agentid');
			$start_date = $request->getParameter($id.'_start_date');
			$activity = $request->getParameter($id.'_text');
			$end_date = $request->getParameter($id.'_end_date');
			$activitynote = $request->getParameter($id.'_activitynote');
			$lastmodifiedby = $this->getUser()->getUsername();
			
			$se = new ConfAgentScheduleEvent();
			$se->setAgentFk($agentid);
			$se->setActivity($activity);
			$se->setStarttime($start_date);
			$se->setEndtime($end_date);
			$se->setActivitynote($activitynote);
			$se->setLastmodificationBy($lastmodifiedby);
			$se->save();
			
			$this->data_sid = $id;
			$this->data_tid = $se->getDbid();
		  }
		  else if($this->operation == 'updated')
		  {
			$this->forward404Unless($se = Doctrine::getTable('ConfAgentScheduleEvent')->find($id), sprintf('Object ConfAgentScheduleEvent does not exist (%s).', $id));
			$agentid = $request->getParameter($id.'_agentid');
			$start_date = $request->getParameter($id.'_start_date');
			$activity = $request->getParameter($id.'_text');
			$end_date = $request->getParameter($id.'_end_date');
			$activitynote = $request->getParameter($id.'_activitynote');
			$lastmodifiedby = $this->getUser()->getUsername();
			
			$se->setAgentFk($agentid);
			$se->setActivity($activity);
			$se->setStarttime($start_date);
			$se->setEndtime($end_date);
			$se->setActivitynote($activitynote);
			$se->setLastmodificationBy($lastmodifiedby);
			$se->save();
			
			$this->data_sid = $id;
			$this->data_tid = $se->getDbid();
		  }
		  else if($this->operation == 'deleted')
		  {
			$se = Doctrine::getTable('ConfAgentScheduleEvent')->find($id);
			if($se)
				$se->delete();
			
			$this->data_sid = $id;
			$this->data_tid = $this->data_sid;
		  }
		  else
		  	$this->forward404();
	  //}
	  
	  sfConfig::set('sf_web_debug', false);  
	  $this->getResponse()->setContentType('text/xml');
	  $this->setLayout(false);
  }
  
  public function executeDeleteagentscheduleevent(sfWebRequest $request)
  {
	 $this->forward404Unless($request->getParameter('eventdbid'));
	 $this->forward404Unless($request->getParameter('agentdbid'));
	 
	 Doctrine_Query::create()
	  ->delete()
	  ->from('ConfAgentScheduleEvent')
	  ->andWhere('dbid = ? and agent_fk = ?', array($request->getParameter('eventdbid'), $request->getParameter('agentdbid')))
	  ->execute();
	  
	 return sfView::HEADER_ONLY;
  }
  
  
  public function executeGetagentscheduleadherence(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;
	
	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();
	}
	
	$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))
							->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") 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 executeGetqueuehistoricaldata(sfWebRequest $request)
  {
	sfConfig::set('sf_web_debug', false);  
	$this->getResponse()->setContentType('text/json');
	$this->setLayout(false);
	
	$this->forward404Unless($this->queueid = $request->getParameter('queueid'));
	$this->forward404Unless($this->conf_queue = Doctrine::getTable('ConfQueue')->find($request->getParameter('queueid')), sprintf('Object conf_queue does not exist (%s).', $request->getParameter('queueid')));
    $this->forward404Unless(fonivaFilter::isCurrentUserSuperTenant() || $this->conf_queue->getTenantFk() == fonivaFilter::getCurrentUserTenantId(), sprintf('Object conf_queue cannot be access (%s).', $request->getParameter('queueid')));
	
	if(!fonivaFilter::canCurrentUserManageAllQueues())
	{
		$allowed = false;
		$queues = fonivaFilter::currentUserManagedQueues();
		foreach($queues as $q)
		{
			if($this->conf_queue->getDbid() == $q->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->nbweeks = ceil( strtotime($this->stat_enddate_ext) - strtotime($this->stat_startdate_ext) ) / (7*24*3600);
	if($this->nbweeks < 1)
		$this->nbweeks = 1;
	
	$pdo = Doctrine_Manager::getInstance()->getCurrentConnection()->getDbh();
	$query = '
		SELECT 
		date_format(queue_enter_time, "%a %H") as readablecovereddate, 
		date_format(queue_enter_time, "%w-%H") as covereddate, 
		ROUND((count(*)/'.$this->nbweeks.'), 2) as total_calls,
		sum(IF(queueresult = "DISTRIBUTED",1,0)) as total_distributed,
		sum(IF(queueresult = "ABANDON",1,0)) as total_abandon
		
		
		FROM log_queue_record 
		WHERE 
			queue_id = :queue_id
			AND
			queue_enter_time >= :starttime
			AND
			queue_enter_time <= :endtime
		GROUP BY date_format(queue_enter_time, "%w-%H")
		ORDER BY covereddate ASC
	
	';
	
	$fieldvalues['queue_id'] = $this->queueid;
	$fieldvalues['starttime'] = $this->starttime;
	$fieldvalues['endtime'] = $this->endtime;
	
	$query = $pdo->prepare($query);
	$query->execute($fieldvalues);
	$this->queuedata = $query->fetchAll();	
  }
  
  /*********   ************/
  /***Evaluation Forms*****/
  /*********   ************/	
  
  public function executeEvaluationforms(sfWebRequest $request)
  {
	return; //todo: complete implementation of this
	$this->searchedEvaluationform = $this->getRequestParameter('searchedEvaluationform');
	$this->searchedEvaluationform = trim(strtolower($this->searchedEvaluationform));
	
	$this->pager = new sfDoctrinePager('ConfEvaluationform', sfConfig::get('app_display_nbrecordperpage'));
	$this->pager->getQuery()->from('ConfEvaluationform ef')
							->orderBy('ef.name')
							->where('1');
							
	
	if(!fonivaFilter::isCurrentUserSuperTenant())
   		$this->pager->getQuery()->andWhere('ef.tenant_fk = ?', FonivaFilter::getCurrentUserTenantId());
		
	$defaultpage = $this->getcurrentmodule_defaultpage();
	if(!empty($this->searchedEvaluationform))
	{
		$this->pager->getQuery()->andWhere('lower(ef.name) LIKE ?', array('%'.$this->searchedEvaluationform.'%'));
		$defaultpage = 1;
	}
									
	$this->setcurrentmodule_defaultpage();
	$this->pager->setPage($defaultpage); //$this->getRequestParameter('page',1));
	$this->pager->init();
	$this->evaluationforms = $this->pager->getResults();
  }
  
  public function executeEvaluationformnew(sfWebRequest $request)
  {
    $conf_evaluationform = new ConfEvaluationform();
	$this->form = new ConfEvaluationformForm($conf_evaluationform);
  }
  
  public function executeEvaluationformcreate(sfWebRequest $request)
  {
    $this->forward404Unless($request->isMethod(sfRequest::POST));
    $conf_evaluationform = new ConfEvaluationform();
    $this->form = new ConfEvaluationformForm($conf_evaluationform);
    $this->processEvaluationformForm($request, $this->form);
    $this->setTemplate('evaluationformnew');
  }
  
  public function executeEvaluationformedit(sfWebRequest $request)
  {
    $this->forward404Unless($conf_evaluationform = Doctrine::getTable('ConfEvaluationform')->find($request->getParameter('dbid')), sprintf('Object conf_evaluationform does not exist (%s).', $request->getParameter('dbid')));
    $this->forward404Unless(fonivaFilter::isCurrentUserSuperTenant() || $conf_evaluationform->getTenantFk() == fonivaFilter::getCurrentUserTenantId());
	$this->form = new ConfEvaluationformForm($conf_evaluationform);
  }

  public function executeEvaluationformupdate(sfWebRequest $request)
  {
    $this->forward404Unless($request->isMethod(sfRequest::POST) || $request->isMethod(sfRequest::PUT));
    $this->forward404Unless($conf_evaluationform = Doctrine::getTable('ConfEvaluationform')->find($request->getParameter('dbid')), sprintf('Object conf_evaluationform does not exist (%s).', $request->getParameter('dbid')));
	$this->forward404Unless(fonivaFilter::isCurrentUserSuperTenant() || $conf_evaluationform->getTenantFk() == fonivaFilter::getCurrentUserTenantId());
    $this->form = new ConfEvaluationformForm($conf_evaluationform);
    $this->processEvaluationformForm($request, $this->form);
    $this->setTemplate('evaluationformedit');
  }

  public function executeEvaluationformdelete(sfWebRequest $request)
  {
    $request->checkCSRFProtection();
    $this->forward404Unless($conf_evaluationform = Doctrine::getTable('ConfEvaluationform')->find($request->getParameter('dbid')), sprintf('Object conf_evaluationform does not exist (%s).', $request->getParameter('dbid')));
	$this->forward404Unless(fonivaFilter::isCurrentUserSuperTenant() || $conf_evaluationform->getTenantFk() == fonivaFilter::getCurrentUserTenantId());
    $conf_evaluationform->delete();
    $this->redirect('workforce/evaluationforms');
  }

  protected function processEvaluationformForm(sfWebRequest $request, sfForm $form)
  {
    $form->bind($request->getParameter($form->getName()), $request->getFiles($form->getName()));
    if ($form->isValid())
    {
	  $isnew = $form->getObject()->isNew();
      $conf_evaluationform = $form->getObject();
	  $conf_evaluationform->setName($form->getValue('name'));
	  if ($form->getObject()->isNew())
	  {
	  	$conf_evaluationform->setCreatedBy($this->getUser()->getUsername());
	  }
	  $conf_evaluationform->setModifiedBy($this->getUser()->getUsername());
	  if(fonivaFilter::isCurrentUserSuperTenant())
	     $conf_evaluationform->setTenantFk($form->getValue('tenant_fk'));
	  else
	     $conf_evaluationform->setTenantFk(fonivaFilter::getCurrentUserTenantId());
	  $conf_evaluationform->save();
	  
      $this->redirect('workforce/evaluationforms');
    }
  }
  
  /*********   *****************/
  /***   EVALUATIONS       *****/
  /*********   *****************/
  
  public function executeEvaluations(sfWebRequest $request)
  {
	$this->sites = Doctrine_Query::create()
							->select('st.dbid, st.name')
							->from('ConfSite st')
							->orderBy('st.name')
							->execute();
	$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->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;
	}
	
	
	//retrieve defaults 
	$this->reportdefparams = $request->getParameter('agentconversationsearch');
	if(!isset($this->reportdefparams['withcallrecording']))
		$this->reportdefparams['withcallrecording'] = 1;
	if(!isset($this->reportdefparams['mincallrecordinglength']))
		$this->reportdefparams['mincallrecordinglength'] = 0;
	
	
	$this->form = new AgentConversationSearchForm();
	$this->form['report_site']->getWidget()->setOption('choices', $this->sites_list);
	$this->form['report_group']->getWidget()->setOption('choices', $this->groups_list);
	$this->form['searchfrominboundid']->getWidget()->setOption('choices', $this->inbounds_list);
	$this->form['searchfromoutboundid']->getWidget()->setOption('choices', $this->outbounds_list);
	$this->form->setDefaults($this->reportdefparams);
	
	
    $this->getResponse()->addStylesheet('chosen/chosen.css', 'last');
	$this->getResponse()->addJavascript('chosen/chosen.jquery.min.js', 'last');
	
	$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('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');
	
	$this->getResponse()->addStylesheet('ratingthemes/bars-1to10.css', 'last');
	$this->getResponse()->addJavascript('jquery.barrating.min.js', 'last');
	
  }
  
  public function executeEvaluationconversationsshow(sfWebRequest $request)
  {
	$this->stat_siteid = $request->getParameter('siteid');
	$this->stat_groupid = $request->getParameter('groupid');
	$this->stat_agent = $request->getParameter('agentid');
	$this->stat_start_date = $request->getParameter('start_date');
	$this->stat_end_date = $request->getParameter('end_date');
	$this->stat_searchcalleridnum = $request->getParameter('searchcalleridnum');
	$this->stat_searchcallednum = $request->getParameter('searchcallednum');
	$this->stat_searchtracknumber = $request->getParameter('searchtracknumber');
	$this->stat_withcallrecording = $request->getParameter('withcallrecording');
	$this->stat_mincallrecordinglength = $request->getParameter('mincallrecordinglength');
	
	$this->stat_searchcalldirection = $request->getParameter('calldirection');
	$this->stat_searchfrominboundid = $request->getParameter('searchfrominboundid');
	$this->stat_searchfromoutboundid = $request->getParameter('searchfromoutboundid');
	
	
	$this->stat_startdate_ext = $this->stat_start_date.' 00:00:00';
	$this->stat_enddate_ext = $this->stat_end_date.' 23:59:59';
	
	
	
	$fieldvalues = array();
	$pdo = Doctrine_Manager::getInstance()->getCurrentConnection()->getDbh();
	
	$query = '';
	$query_count = '';
	
	$query_fields = 'SELECT la.dbid, 
					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,
					count(cv.dbid) as nbvoicerecordings,
					sum(cv.duration) as totalvoicerecordingdurations ';
					
	$query_count_fields = 'SELECT COUNT(*) as nbtotalrecords,
							count(cv.dbid) as nbvoicerecordings,
							sum(cv.duration) as totalvoicerecordingdurations ';
	
	
	$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_call_voicerecording cv ON la.tracknumber = cv.calltracknumber
					';
	$query_count_tables = '
					FROM log_agent_call la
					LEFT JOIN conf_agent ca ON la.agentdbid = ca.dbid 
					LEFT JOIN log_call_voicerecording cv ON la.tracknumber = cv.calltracknumber
					';
					
	$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 == '')
	{
	  $query_filters .= ' AND ca.active = "yes" ';
	}
	
	if($this->stat_group != '')
	{
	  $query_filters .= ' AND la.groupdbid = :stat_group';
	  $fieldvalues['stat_group'] = $this->stat_group;
	}
	
	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_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;
    }
  
	
	$query_having = '';
	if($this->stat_withcallrecording == '1')
	{
		$query_having = ' HAVING ';
		$query_having .= ' nbvoicerecordings > 0 ';
		
		if($this->stat_mincallrecordinglength != null && $this->stat_mincallrecordinglength != '' && $this->stat_mincallrecordinglength > 0)
		{
			$query_having .= ' AND totalvoicerecordingdurations >= :mincallrecordinglength ';
	  		$fieldvalues['mincallrecordinglength'] = $this->stat_mincallrecordinglength;
		}
	}
	
	$query_orderby = ' ORDER BY la.callreceived_at, ca.username ';
	$query_groupby = ' GROUP BY la.tracknumber';
	
	$query_count_orderby = '';
	$query_count_groupby = ' GROUP BY la.tracknumber ';
	
	
	$query = $query_fields.''.$queue_tables.''.$query_filters.''.$query_groupby.''.$query_having.''.$query_orderby;
	$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_count_groupby.''.$query_having;
	
	//echo $query;
	//exit();
	
	$query = $pdo->prepare($query);
	$query_count = $pdo->prepare($query_count);
	
	$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();
	
  }
  
  public function executeEvaluationagentconversationevaluate(sfWebRequest $request)
  {
	$this->forward404Unless($this->logagentcall = Doctrine::getTable('LogAgentCall')->find($request->getParameter('dbid')), sprintf('Object log_agent_call does not exist (%s).', $request->getParameter('dbid')));
    $this->forward404Unless(fonivaFilter::isCurrentUserSuperTenant() || $this->logagentcall->getTenantFk() == fonivaFilter::getCurrentUserTenantId());


	$this->campaign = NULL;
	if($this->logagentcall->campaign_id != '' && $this->logagentcall->campaign_id != NULL)
	{
		if($this->logagentcall->is_inbound == 'yes')
			$this->campaign = Doctrine::getTable('ConfInbound')->find($this->logagentcall->campaign_id);
		else if($this->logagentcall->is_manual == 'no')
			$this->campaign = Doctrine::getTable('ConfCampaign')->find($this->logagentcall->campaign_id);
	}
		
	$this->queue = NULL;
	if($this->logagentcall->queue_id != '' && $this->logagentcall->queue_id != NULL)
		$this->queue = Doctrine::getTable('ConfQueue')->find($this->logagentcall->queue_id);
		
	$this->recordings = Doctrine_Query::create()
							->from('LogCallVoicerecording c')
							->where('c.calltracknumber = ?', $this->logagentcall->tracknumber)
							->orderBy('c.started_at')
							->execute();
							
	$this->callxfers = Doctrine_Query::create()
							->from('LogAgentCalltransfer c')
							->where('c.tracknumber = ?', $this->logagentcall->tracknumber)
							->andwhere('c.agent_id = ?', $this->logagentcall->agentdbid)
							->orderBy('c.occured_at')
							->execute();
							
	$this->evaluationforms = Doctrine_Query::create()
								->from('ConfEvaluationForm ce')
								->where('ce.active = "yes"')
								->orderby('ce.name');
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$this->evaluationforms->andWhere('ce.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	$this->evaluationforms = $this->evaluationforms->execute();							
	
	$this->agent = Doctrine::getTable('ConfAgent')->find($this->logagentcall->agentdbid);
	$this->group = Doctrine::getTable('ConfGroup')->find($this->logagentcall->groupdbid);
	$this->site  = Doctrine::getTable('ConfSite')->find($this->logagentcall->sitedbid);
	
	$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 executeEvaluationagentconversationshowevaluations(sfWebRequest $request)
  {
	$this->forward404Unless($this->logagentcall = Doctrine::getTable('LogAgentCall')->find($request->getParameter('logagentcalldbid')), sprintf('Object log_agent_call does not exist (%s).', $request->getParameter('logagentcalldbid')));
    $this->forward404Unless(fonivaFilter::isCurrentUserSuperTenant() || $this->logagentcall->getTenantFk() == fonivaFilter::getCurrentUserTenantId());
	
	$this->logagentcallevaluations = Doctrine_Query::create()
								->select('le.dbid, le.sitedbid, le.groupdbid, le.agentdbid, SUM(li.score) as totalscore, le.maxtotalscore,
											le.created_by, le.created_at,
											ce.dbid as formdbid, ce.name as formname, ce.active as formactive')
								->from('LogAgentCallEvaluation le')
								->leftJoin('le.ConfEvaluationform ce ON le.evaluationform_fk = ce.dbid')
								->leftJoin('le.LogAgentCallEvaluationItem li')
								->where('le.logagentcall_fk = ?', $this->logagentcall->dbid)
								->orderby('le.updated_at desc, le.created_at desc, ce.name')
								->groupby('le.dbid')
								->execute();
  }
  
  public function executeEvaluationagentconversationaddevaluation(sfWebRequest $request)
  {
	 $this->forward404Unless($this->logagentcall = Doctrine::getTable('LogAgentCall')->find($request->getParameter('logagentcalldbid')), sprintf('Object log_agent_call does not exist (%s).', $request->getParameter('logagentcalldbid')));
     $this->forward404Unless($this->evaluationform = Doctrine::getTable('ConfEvaluationform')->find($request->getParameter('evaluationformdbid')), sprintf('Object conf_evaluation_form does not exist (%s).', $request->getParameter('evaluationformdbid'))); 
	 $this->forward404Unless(fonivaFilter::isCurrentUserSuperTenant() || $this->logagentcall->getTenantFk() == fonivaFilter::getCurrentUserTenantId());
	 $this->forward404Unless(fonivaFilter::isCurrentUserSuperTenant() || $this->evaluationform->getTenantFk() == fonivaFilter::getCurrentUserTenantId()); 
	 
	 $this->lace = new LogAgentCallEvaluation();
	 $this->lace->setLogagentcallFk($this->logagentcall->dbid);
	 $this->lace->setEvaluationformFk($this->evaluationform->dbid);
	 $this->lace->setCalloccuredat($this->logagentcall->callreceived_at);
	 $this->lace->setIsInbound($this->logagentcall->is_inbound);
	 $this->lace->setIsManual($this->logagentcall->is_manual);
	 $this->lace->setCampaignId($this->logagentcall->campaign_id);
	 $this->lace->setDialplanId($this->logagentcall->dialplan_id);
	 $this->lace->setQueueId($this->logagentcall->queue_id);
	 $this->lace->setSitedbid($request->getParameter('sitedbid'));
	 $this->lace->setGroupdbid($request->getParameter('groupdbid'));
	 $this->lace->setAgentdbid($request->getParameter('agentdbid'));
	 $this->lace->setCreatedBy( $this->getUser()->getUsername());
	 $this->lace->save();
	 
	 
	 sfConfig::set('sf_web_debug', false);
	 $this->setLayout(false);
  }
  
  public function executeEvaluationagentconversationeditevaluation(sfWebRequest $request)
  {
	 $this->forward404Unless($this->logagentcallevaluation = Doctrine::getTable('LogAgentCallEvaluation')->find($request->getParameter('evaluationdbid')), sprintf('Object log_agent_call_evaluation does not exist (%s).', $request->getParameter('evaluationdbid')));
	 $this->evaluationform = $this->logagentcallevaluation->ConfEvaluationform;
	 $this->evaluationitems = Doctrine_Query::create()
	 							->select('lei.dbid as dbid, lei.score as score, lei.maxscore as maxscore, lei.note as note,
											cei.category as category, cei.label as label, cei.labeldescription as labeldescription')
								->from('LogAgentCallEvaluationItem lei')
								->leftJoin('lei.ConfEvaluationformItem cei ON cei.dbid = lei.evaluationformitem_fk')
								->where('lei.logagentcallevaluation_fk = ?', $this->logagentcallevaluation->dbid)
								->andwhere('cei.active = "yes"')
								->orderby('cei.category, cei.dbid')
								->execute();
	 
	       
	 sfConfig::set('sf_web_debug', false);
	 $this->setLayout(false);
  }
  
  public function executeEvaluationagentconversationsaveevaluation(sfWebRequest $request)
  {
	 $evaluationitems = $request->getParameter('evaluationitem');
	 foreach($evaluationitems as $item)
	 {
		$dbid = $item['dbid'];
		$score = $item['score'];
		$note = $item['note'];
		$notapplicable = $item['notapplicable'];
		$logagentcallevaluation_fk = $item['logagentcallevaluation_fk'];
		$evaluationformitem_fk = $item['evaluationformitem_fk'];
		
		$di = Doctrine::getTable('LogAgentCallEvaluationItem')->find($dbid);
		if(!$di)
		{
			$di = new LogAgentCallEvaluationItem();
			$di->setLogagentcallevaluationFk($logagentcallevaluation_fk);
			$di->setEvaluationformitemFk($evaluationformitem_fk);
		}
		
		$di->setScore($score);
		$di->setNote($note);
		$di->setNotapplicable(($notapplicable == 'yes' ? 'yes' : 'no'));
		$di->setUpdatedBy($this->getUser()->getUsername());
		$di->save();
     }
	 
	 
	 
	 
  }
  
  public function executeGetagentconversationsearchagentslist(sfWebRequest $request)
  {
	$this->siteid = $request->getParameter('siteid');
	$this->groupid = $request->getParameter('groupid');
	
	$this->reportdefparams = $request->getParameter('agentconversationsearch');
	
	$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->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
	{
		$this->agents->andWhere('ca.group_fk in ?', array($this->groupids_list));
	}
	if($this->siteid != NULL && $this->siteid != '')
	$this->agents->andWhere('ca.site_fk = ?', $this->siteid);
	
	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('agentconversationsearch[%s]');
	$this->agentslistform['report_agent']->getWidget()->setLabel('Agents');
	$this->agentslistform['report_agent']->getWidget()->setOption('choices', $this->agentslist_arr);
	
	$this->agentslistform->setDefaults($this->reportdefparams);
	
  }
  
  public function executeGetagentevaluationreviewagentslist(sfWebRequest $request)
  {
	$this->siteid = $request->getParameter('siteid');
	$this->groupid = $request->getParameter('groupid');
	
	$this->reportdefparams = $request->getParameter('agentevaluationsearch');
	
	$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->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
	{
		$this->agents->andWhere('ca.group_fk in ?', array($this->groupids_list));
	}
	if($this->siteid != NULL && $this->siteid != '')
	$this->agents->andWhere('ca.site_fk = ?', $this->siteid);
	
	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('agentevaluationsearch[%s]');
	$this->agentslistform['report_agent']->getWidget()->setLabel('Agents');
	$this->agentslistform['report_agent']->getWidget()->setOption('choices', $this->agentslist_arr);
	
	$this->agentslistform->setDefaults($this->reportdefparams);
	
  }
  
  
  /*********   ***********************/
  /***   EVALUATIONS  REVIEW     *****/
  /*********   ***********************/
  
  public function executeEvaluationsreview(sfWebRequest $request)
  {
	$this->sites = Doctrine_Query::create()
							->select('st.dbid, st.name')
							->from('ConfSite st')
							->orderBy('st.name')
							->execute();
	$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->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;
	}
	
	$this->evaluationforms = Doctrine_Query::create()
							->select('cef.dbid, cef.name')
							->from('ConfEvaluationform cef')
							->orderBy('cef.name')
							->where('cef.active = "yes"');
	
	if(!fonivaFilter::isCurrentUserSuperTenant())
		$this->evaluationforms->andWhere('cef.tenant_fk = ?', fonivaFilter::getCurrentUserTenantId());
	$this->evaluationforms = $this->evaluationforms->execute();
	
	$this->evaluationforms_list = array();
	foreach($this->evaluationforms as $f)
	{
		$this->evaluationforms_list[$f->getDbid()] = $f->name;
	}
	
	
	$this->form = new AgentEvaluationSearchForm();
	$this->form['report_site']->getWidget()->setOption('choices', $this->sites_list);
	$this->form['report_group']->getWidget()->setOption('choices', $this->groups_list);
	$this->form['searchfrominboundid']->getWidget()->setOption('choices', $this->inbounds_list);
	$this->form['searchfromoutboundid']->getWidget()->setOption('choices', $this->outbounds_list);
	$this->form['report_evaluationform']->getWidget()->setOption('choices', $this->evaluationforms_list);
	
	$this->form->setDefaults($this->reportdefparams);
	
	
    $this->getResponse()->addStylesheet('chosen/chosen.css', 'last');
	$this->getResponse()->addJavascript('chosen/chosen.jquery.min.js', 'last');
	
	$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('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 executeEvaluationsreviewshow(sfWebRequest $request)
  {
	$this->forward404Unless($this->evaluationform = Doctrine::getTable('ConfEvaluationform')->find($request->getParameter('evaluationformid')), sprintf('Object log_agent_call_evaluation does not exist (%s).', $request->getParameter('evaluationdbid')));
	
	$this->stat_site = $request->getParameter('siteid');
	$this->stat_group = $request->getParameter('groupid');
	$this->stat_agent = $request->getParameter('agentid');
	$this->stat_start_date = $request->getParameter('start_date');
	$this->stat_end_date = $request->getParameter('end_date');
	$this->stat_calldirection = $request->getParameter('calldirection');
	$this->stat_searchfrominboundid = $request->getParameter('searchfrominboundid');
	$this->stat_searchfromoutboundid = $request->getParameter('searchfromoutboundid');
	
	$this->stat_startdate_ext = $this->stat_start_date.' 00:00:00';
	$this->stat_enddate_ext = $this->stat_end_date.' 23:59:59';
	
	
	
	$this->site = NULL;
	$this->group = NULL;
	$this->agent = NULL;
	
	$this->site = Doctrine::getTable('ConfSite')->find($request->getParameter('siteid'));
	$this->group = Doctrine::getTable('ConfGroup')->find($request->getParameter('groupid'));
	$this->agent = Doctrine::getTable('ConfAgent')->find($request->getParameter('agentid'));
	
	
	$fieldvalues = array();
	$this->pdo = Doctrine_Manager::getInstance()->getCurrentConnection()->getDbh();
	
	
	$this->data_allevaluations = $this->evaluationsreview_pull_evaluationresumed();
	$this->data_allevaluations_byitemcategory = $this->evaluationsreview_pull_evaluationresumed_by_itemcategory();
	$this->data_allevaluations_byitem = $this->evaluationsreview_pull_evaluationresumed_by_item();
  }
	
  protected function evaluationsreview_pull_evaluationresumed()
  {
	$query = '';
	
	$query = '
			  SELECT 
			  	conf_evaluationform.dbid as evaluationform_dbid,
			  	conf_evaluationform.name as evaluationform_name,
				COUNT(lace_resumed.dbid) as nb_evaluations,
				SUM(lace_resumed.totalscore) as totalscore,
				SUM(lace_resumed.maxtotalscore) as maxtotalscore
				
				FROM conf_evaluationform, 
				  (
					SELECT 
						lace.dbid, 
						lace.evaluationform_fk,
						lace.calloccuredat,
						lace.sitedbid,
						lace.groupdbid,
						lace.agentdbid,
						lace.is_inbound,
						lace.is_manual,
						lace.campaign_id,
						lace.dialplan_id,
						lace.queue_id,
						SUM(lacei.score) as totalscore,
						SUM(lacei.maxscore) as maxtotalscore
						FROM log_agent_call_evaluation lace,
								log_agent_call_evaluation_item lacei
						WHERE 
							lacei.logagentcallevaluation_fk = lace.dbid
							AND lace.evaluationform_fk = :evaluationform_fk
							AND ( lace.calloccuredat >= :stat_startdate_ext and lace.calloccuredat <= :stat_enddate_ext )
						GROUP BY 
							lace.dbid
						) AS lace_resumed 
						
				WHERE 
					conf_evaluationform.dbid = :evaluationform_fk
					AND conf_evaluationform.dbid = lace_resumed.evaluationform_fk
					AND ( lace_resumed.calloccuredat >= :stat_startdate_ext and lace_resumed.calloccuredat <= :stat_enddate_ext )
				';
	
	$fieldvalues['evaluationform_fk'] = $this->evaluationform->dbid;
	$fieldvalues['stat_startdate_ext'] = $this->stat_startdate_ext;
	$fieldvalues['stat_enddate_ext'] = $this->stat_enddate_ext;
	
	$query_filters = '';
	
	if($this->stat_agent == 'USERFOLLOWEDAGENTS')
	{
	 $query_filters .= ' AND lace_resumed.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 lace_resumed.agentdbid = :stat_agent';
	  $fieldvalues['stat_agent'] = $this->stat_agent;
	}
	
	if($this->stat_group != '')
	{
	  $query_filters .= ' AND lace_resumed.groupdbid = :stat_group';
	  $fieldvalues['stat_group'] = $this->stat_group;
	}
	
	if($this->stat_site != '')
	{
	  $query_filters .= ' AND lace_resumed.sitedbid = :stat_site';
	  $fieldvalues['stat_site'] = $this->stat_site;
	}
	
	if($this->stat_calldirection != '')
	{
	  $query_filters .= ' AND lace_resumed.is_inbound = :stat_calldirection';
	  $fieldvalues['stat_calldirection'] = $this->stat_calldirection;
	}
	
	if($this->stat_searchfrominboundid != '')
	{
	  $query_filters .= ' AND lace_resumed.campaign_id = :stat_searchfrominboundid';
	  $fieldvalues['stat_searchfrominboundid'] = $this->stat_searchfrominboundid;
	}
	
	if($this->stat_searchfromoutboundid != '')
	{
	  $query_filters .= ' AND lace_resumed.campaign_id = :stat_searchfromoutboundid';
	  $fieldvalues['stat_searchfromoutboundid'] = $this->stat_searchfromoutboundid;
	}
	
	$query_having = '';
	
	$query_orderby = '  ';
	$query_groupby = ' GROUP BY lace_resumed.evaluationform_fk ';	
	
	$query = $query.$query_filters.''.$query_groupby.''.$query_having.''.$query_orderby;
	
	$query = $this->pdo->prepare($query);
	$query->execute($fieldvalues);
	return $query->fetchAll();
	 
  }
  
  protected function evaluationsreview_pull_evaluationresumed_by_itemcategory()
  {
	$query = '';
	
	$query = '
			  SELECT 
			  	conf_evaluationform.dbid as evaluationform_dbid,
			  	conf_evaluationform.name as evaluationform_name,
				lace_resumed.category,
				COUNT(lace_resumed.dbid) as nb_evaluations,
				SUM(lace_resumed.totalscore) as totalscore,
				SUM(lace_resumed.maxtotalscore) as maxtotalscore
				
				FROM conf_evaluationform, 
				  (
					SELECT 
						lace.dbid, 
						lace.evaluationform_fk,
						lace.calloccuredat,
						lace.sitedbid,
						lace.groupdbid,
						lace.agentdbid,
						lace.is_inbound,
						lace.is_manual,
						lace.campaign_id,
						lace.dialplan_id,
						lace.queue_id,
						cefi.category,
						SUM(lacei.score) as totalscore,
						SUM(lacei.maxscore) as maxtotalscore
						FROM log_agent_call_evaluation lace,
								log_agent_call_evaluation_item lacei,
								conf_evaluationform_item cefi
						WHERE 
							lacei.logagentcallevaluation_fk = lace.dbid
							AND lacei.evaluationformitem_fk = cefi.dbid
							AND lace.evaluationform_fk = :evaluationform_fk
							AND ( lace.calloccuredat >= :stat_startdate_ext and lace.calloccuredat <= :stat_enddate_ext )
						GROUP BY 
							cefi.category 
						) AS lace_resumed 
						
				WHERE 
					conf_evaluationform.dbid = :evaluationform_fk
					AND conf_evaluationform.dbid = lace_resumed.evaluationform_fk
					AND ( lace_resumed.calloccuredat >= :stat_startdate_ext and lace_resumed.calloccuredat <= :stat_enddate_ext )
				';
	
	$fieldvalues['evaluationform_fk'] = $this->evaluationform->dbid;
	$fieldvalues['stat_startdate_ext'] = $this->stat_startdate_ext;
	$fieldvalues['stat_enddate_ext'] = $this->stat_enddate_ext;
	
	$query_filters = '';
	
	if($this->stat_agent == 'USERFOLLOWEDAGENTS')
	{
	 $query_filters .= ' AND lace_resumed.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 lace_resumed.agentdbid = :stat_agent';
	  $fieldvalues['stat_agent'] = $this->stat_agent;
	}
	
	if($this->stat_group != '')
	{
	  $query_filters .= ' AND lace_resumed.groupdbid = :stat_group';
	  $fieldvalues['stat_group'] = $this->stat_group;
	}
	
	if($this->stat_site != '')
	{
	  $query_filters .= ' AND lace_resumed.sitedbid = :stat_site';
	  $fieldvalues['stat_site'] = $this->stat_site;
	}
	
	if($this->stat_calldirection != '')
	{
	  $query_filters .= ' AND lace_resumed.is_inbound = :stat_calldirection';
	  $fieldvalues['stat_calldirection'] = $this->stat_calldirection;
	}
	
	if($this->stat_searchfrominboundid != '')
	{
	  $query_filters .= ' AND lace_resumed.campaign_id = :stat_searchfrominboundid';
	  $fieldvalues['stat_searchfrominboundid'] = $this->stat_searchfrominboundid;
	}
	
	if($this->stat_searchfromoutboundid != '')
	{
	  $query_filters .= ' AND lace_resumed.campaign_id = :stat_searchfromoutboundid';
	  $fieldvalues['stat_searchfromoutboundid'] = $this->stat_searchfromoutboundid;
	}
	
	$query_having = '';
	
	$query_orderby = '  ';
	$query_groupby = ' GROUP BY lace_resumed.evaluationform_fk, lace_resumed.category ';	
	
	$query = $query.$query_filters.''.$query_groupby.''.$query_having.''.$query_orderby;
	//echo $query;
	//exit();
	$query = $this->pdo->prepare($query);
	$query->execute($fieldvalues);
	return $query->fetchAll();
	 
  }
  
  protected function evaluationsreview_pull_evaluationresumed_by_item()
  {
	$query = '';
	
	$query = '
			  SELECT 
			  	conf_evaluationform.dbid as evaluationform_dbid,
			  	conf_evaluationform.name as evaluationform_name,
				lace_resumed.evaluationformitem_fk as evaluationformitem_fk,
				lace_resumed.category as category,
				lace_resumed.label as label,
				lace_resumed.labeldescription as labeldescription,
				COUNT(lace_resumed.dbid) as nb_evaluations,
				SUM(lace_resumed.totalscore) as totalscore,
				SUM(lace_resumed.maxtotalscore) as maxtotalscore
				
				FROM conf_evaluationform, 
				  (
					SELECT 
						lace.dbid, 
						lace.evaluationform_fk,
						lace.calloccuredat,
						lacei.evaluationformitem_fk,
						cefi.label,
						cefi.labeldescription,
						cefi.category,
						lace.sitedbid,
						lace.groupdbid,
						lace.agentdbid,
						lace.is_inbound,
						lace.is_manual,
						lace.campaign_id,
						lace.dialplan_id,
						lace.queue_id,
						SUM(lacei.score) as totalscore,
						SUM(lacei.maxscore) as maxtotalscore
						FROM log_agent_call_evaluation lace,
								log_agent_call_evaluation_item lacei,
								conf_evaluationform_item cefi
						WHERE 
							lacei.logagentcallevaluation_fk = lace.dbid
							AND lacei.evaluationformitem_fk = cefi.dbid
							AND lace.evaluationform_fk = :evaluationform_fk
							AND ( lace.calloccuredat >= :stat_startdate_ext and lace.calloccuredat <= :stat_enddate_ext )
						GROUP BY 
							lacei.evaluationformitem_fk
						) AS lace_resumed 
						
				WHERE 
					conf_evaluationform.dbid = :evaluationform_fk
					AND conf_evaluationform.dbid = lace_resumed.evaluationform_fk
					AND ( lace_resumed.calloccuredat >= :stat_startdate_ext and lace_resumed.calloccuredat <= :stat_enddate_ext )
				';
	
	$fieldvalues['evaluationform_fk'] = $this->evaluationform->dbid;
	$fieldvalues['stat_startdate_ext'] = $this->stat_startdate_ext;
	$fieldvalues['stat_enddate_ext'] = $this->stat_enddate_ext;
	
	$query_filters = '';
	
	if($this->stat_agent == 'USERFOLLOWEDAGENTS')
	{
	 $query_filters .= ' AND lace_resumed.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 lace_resumed.agentdbid = :stat_agent';
	  $fieldvalues['stat_agent'] = $this->stat_agent;
	}
	
	if($this->stat_group != '')
	{
	  $query_filters .= ' AND lace_resumed.groupdbid = :stat_group';
	  $fieldvalues['stat_group'] = $this->stat_group;
	}
	
	if($this->stat_site != '')
	{
	  $query_filters .= ' AND lace_resumed.sitedbid = :stat_site';
	  $fieldvalues['stat_site'] = $this->stat_site;
	}
	
	if($this->stat_calldirection != '')
	{
	  $query_filters .= ' AND lace_resumed.is_inbound = :stat_calldirection';
	  $fieldvalues['stat_calldirection'] = $this->stat_calldirection;
	}
	
	if($this->stat_searchfrominboundid != '')
	{
	  $query_filters .= ' AND lace_resumed.campaign_id = :stat_searchfrominboundid';
	  $fieldvalues['stat_searchfrominboundid'] = $this->stat_searchfrominboundid;
	}
	
	if($this->stat_searchfromoutboundid != '')
	{
	  $query_filters .= ' AND lace_resumed.campaign_id = :stat_searchfromoutboundid';
	  $fieldvalues['stat_searchfromoutboundid'] = $this->stat_searchfromoutboundid;
	}
	
	$query_having = '';
	
	$query_orderby = '  ';
	$query_groupby = ' GROUP BY lace_resumed.evaluationform_fk, lace_resumed.evaluationformitem_fk ';	
	
	$query = $query.$query_filters.''.$query_groupby.''.$query_having.''.$query_orderby;
	//echo $query;
	//exit();
	$query = $this->pdo->prepare($query);
	$query->execute($fieldvalues);
	return $query->fetchAll();
	 
  }
  
  /*********   ************/
  /***   OTHERS       *****/
  /*********   ************/
  
  private function getcurrentmodule_defaultpage()
  {
	  $sessionvarname = $this->getModuleName().'_'.$this->getActionName().'_currentpage';
	  $currentpage = $this->getRequestParameter('page', $this->getUser()->getAttribute($sessionvarname, '1'));
	  return $currentpage;
  }
  
  private function setcurrentmodule_defaultpage()
  {
	  $sessionvarname = $this->getModuleName().'_'.$this->getActionName().'_currentpage';
	  if($this->getRequestParameter('page') != NULL)
	  	$this->getUser()->setAttribute($sessionvarname, $this->getRequestParameter('page'));  
  }
  
  
  private function holt_winters($data, $season_length = 7, $alpha = 0, $beta = 0, $gamma = 0, $dev_gamma = 0) 
  {  
    // Calculate an initial trend level
    $trend1 = 0;
    for($i = 0; $i < $season_length; $i++) {
        $trend1 += $data[$i];
    }
    $trend1 /= $season_length;
    
    $trend2 = 0;
    for($i = $season_length; $i < 2*$season_length; $i++) {
        $trend2 += $data[$i];
    }
    $trend2 /= $season_length;
    
    $initial_trend = ($trend2 - $trend1) / $season_length;
    
    // Take the first value as the initial level
    $initial_level = $data[0];
        
    // Build index
    $index = array();
    foreach($data as $key => $val) {
        $index[$key] = $val / ($initial_level + ($key + 1) * $initial_trend);
    }
    
    // Build season buffer
    $season = array_fill(0, count($data), 0);
    for($i = 0; $i < $season_length; $i++) {
        $season[$i] = ($index[$i] + $index[$i+$season_length]) / 2;
    }
    
    // Normalise season
    $season_factor = $season_length / array_sum($season);
    foreach($season as $key => $val) {
        $season[$key] *= $season_factor;
    }
    
    
    $holt_winters = array();
    $deviations = array();
    $alpha_level = $initial_level;
    $beta_trend = $initial_trend;
    foreach($data as $key => $value) {
        $temp_level = $alpha_level;
        $temp_trend = $beta_trend;
        
        $alpha_level = $alpha * $value / $season[$key] + (1.0 - $alpha) * ($temp_level + $temp_trend);
        $beta_trend = $beta * ($alpha_level - $temp_level) + ( 1.0 - $beta ) * $temp_trend;
        
        $season[$key + $season_length] = $gamma * $value / $alpha_level + (1.0 - $gamma) * $season[$key];
        
        $holt_winters[$key] = ($alpha_level + $beta_trend * ($key + 1)) * $season[$key];
        $deviations[$key] = $dev_gamma * abs($value - $holt_winters[$key]) + (1-$dev_gamma) 
                                * (isset($deviations[$key - $season_length]) ? $deviations[$key - $season_length] : 0);
    }
    
    /* Could forecast a bit! */
    for($i = 1; $i <= $season_length; $i++) {
        $holt_winters[$key + $i] = $alpha_level + $beta_trend * $season[$key + $i];
    }
    
    
    return array($holt_winters, $deviations);
  }


}
