public function getTimeTotals($user_id, $time_range)
{
switch ($time_range) {
case 'overall':
$date_from = null;
$date_to = null;
break;
case 'this_week':
$days = DateTimeHelper::create()->getDaysOfWeek(date('W'), date('Y'));
$date_from = date('Y-m-d 00:00:00', $days[1]);
$date_to = date('Y-m-d 23:59:59', $days[7]);
break;
case 'last_week':
$days = DateTimeHelper::create()->getDaysOfWeek(date('W') - 1, date('Y'));
$date_from = date('Y-m-d 00:00:00', $days[1]);
$date_to = date('Y-m-d 23:59:59', $days[7]);
break;
case 'this_month':
$date_from = date('Y-m-d 00:00:00', mktime(00, 00, 00, date('m'), 01));
$date_to = date('Y-m-d 23:59:59', strtotime($date_from . '+1 MONTH -1 SECOND'));
break;
case 'last_month':
$month = strtotime('last month');
$date_from = date('Y-m-d 00:00:00', mktime(00, 00, 00, date('m', $month), 01));
$date_to = date('Y-m-d 23:59:59', strtotime($date_from . '+1 MONTH -1 SECOND'));
break;
}
$query = Doctrine_Query::create()->select('p.*, i.*, SUM(i.value) as total')->from('Project p')->where('i.user_id=?', $user_id)->innerJoin('p.TimeLogItems i')->groupBy('p.id')->orderBy('p.name');
if ($date_from != null && $date_to != null) {
$query->andWhere('i.itemdate >= ? AND i.itemdate <= ?', array($date_from, $date_to));
}
return $query->execute();
}
/**
* Executes index action
*
* @param sfRequest $request A request object
*/
public function executeIndex(sfWebRequest $request)
{
$this->week = $request->getParameter('week', date('W'));
$this->year = $request->getParameter('year', date('Y'));
$days = DateTimeHelper::create()->getDaysOfWeek($this->week, $this->year);
$this->weekstart = $days[1];
$account_id = $this->getUser()->getAttribute('account_id');
$this->projects = ProjectTable::getInstance()->findByAccountId($account_id);
$this->user = UserTable::getInstance()->find($this->getUser()->getAttribute('uid'));
$this->item_types = TimeItemTypeTable::getInstance()->findByAccountId($account_id);
$this->default_item_type = TimeItemTypeTable::getInstance()->findDefaultForAccount($account_id);
if ($this->default_item_type) {
$this->default_item_type_name = $this->default_item_type->getName();
} else {
$this->default_item_type_name = null;
}
$items = Doctrine_Query::create()->from('TimeLogItem ti')->where('ti.itemdate >= ? and itemdate <= ? and ti.user_id = ?', array(date('Y-m-d', $days[1]), date('Y-m-d', $days[1] + 5 * 24 * 60 * 60), $this->getUser()->getAttribute('uid')))->execute();
$this->time_items = new TimeItemSelector($items);
$this->account = AccountTable::getInstance()->find($account_id);
}
