/**
* Get a list of issues.
*
* @return arrayObject
*/
public function getIssues($where = array(), $order = 'issue.created desc', $page = null, $number = null)
{
// if the current user is an external, filter by their clientid
if (za()->getUser()->getRole() == User::ROLE_EXTERNAL) {
// get their client
$client = $this->clientService->getUserClient(za()->getUser());
$where['issue.clientid='] = $client->id;
}
$select = $this->dbService->select();
/* @var $select Zend_Db_Select */
$select->from('issue', '*');
$select->joinInner('client', 'client.id=issue.clientid', 'client.title as clientname');
$select->joinLeft('project', 'project.id=issue.projectid', array('project.title as projectname', 'project.isprivate as privateproject'));
$this->dbService->applyWhereToSelect($where, $select);
if (!is_null($page)) {
$select->limitPage($page, $number);
}
$select->order($order);
$tasks = $this->dbService->fetchObjects('Issue', $select);
return $tasks;
}
/**
* Gets a list of all items a user is watching
*
* @param $user
* The user for whom to get subscribed items
* @return
* List of items
*/
public function getWatchedItems($user, $type = null)
{
$select = $this->dbService->select();
$where = array('userid=' => $user->username);
$select->from('itemwatch', '*');
if ($type != null) {
$where['itemtype'] = is_array($type) ? $type : array($type);
// $select->where('itemtype = ?', $type);
}
$this->dbService->applyWhereToSelect($where, $select);
$select->order('created desc');
$watches = $this->dbService->fetchObjects('ItemWatch', $select);
$items = new ArrayObject();
foreach ($watches as $watch) {
$item = $this->dbService->getById($watch->itemid, $watch->itemtype);
if ($item) {
$items[] = $item;
}
}
return $items;
}
/**
* Get a list of versions of objects for the passed in objects.
*
* This is a useful way of getting a complete history of the state of objects
* at a particular time. To get a snapshot of the 'valid' objects for
* a given time, use the 'getVersionedObjects' method
*
* @param ArrayObject $objects
*/
public function getVersionsFor($object, $from = null, $to = null, $filter = array())
{
$type = null;
if (is_object($object) && (!is_array($object) || !$object instanceof ArrayAccess)) {
$object = array($object);
} else {
if (is_string($object)) {
$type = $object;
}
}
$ids = array();
if (!$type) {
foreach ($object as $obj) {
$ids[] = $obj->id;
$type = get_class($obj);
}
}
$table = mb_strtolower($type . 'Version');
$select = $this->dbService->select();
/* @var $select Zend_Db_Select */
$select->from($table, '*');
if ($from) {
// From means versions that were created after this date
$select->where('versioncreated > ?', $from);
// $select->where('validfrom > ?', $from);
}
if ($to) {
$select->where('validfrom < ?', $to);
}
if (count($ids)) {
$filter['recordid'] = $ids;
}
$select->order('id DESC');
$this->dbService->applyWhereToSelect($filter, $select);
$versions = $this->dbService->fetchObjects($type . 'Version', $select);
return $versions;
}
/**
* Gets linked items of a particular type. Used when you know that
* the linked item is definitely of a certain type, means that a quicker
* query can be used, as well as sorting.
*
* @param mixed $item The item to get the links for
* @param String $side Which side are the items to fetch compared to the $item?
* @param String $type the type of objects to be getting
* @param array $where an optional where clause to apply
*/
public function getLinkedItemsOfType($item, $side = 'from', $type, $where = array(), $sortby = 'id asc')
{
if ($item == null) {
$this->log->warn("Tried getting linked items of null object");
return new ArrayObject();
}
$results = null;
$finalResults = new ArrayObject();
/* @var $select Zend_Db_Select */
$select = $this->dbService->select();
$tableName = mb_strtolower($this->sanitizeType($type));
$itemType = get_class($item);
// $tableName = $this->dbService->quote(mb_strtolower($type));
// select * from $type left join itemlink on itemlink.type = $type and itemlink.to=$type.id where itemlink.to is null
if ($side == 'from') {
$select->from($tableName)->joinLeft('itemlink', 'itemlink.totype = ' . $this->dbService->quote($type) . ' and itemlink.toid=' . $tableName . '.id and itemlink.fromtype = ' . $this->dbService->quote($itemType), 'itemlink.toid')->where('itemlink.fromid = ?', $item->id);
} else {
$select->from($tableName)->joinLeft('itemlink', 'itemlink.fromtype = ' . $this->dbService->quote($type) . ' and itemlink.fromid=' . $tableName . '.id and itemlink.totype = ' . $this->dbService->quote($itemType), 'itemlink.fromid')->where('itemlink.toid = ?', $item->id);
}
$this->dbService->applyWhereToSelect($where, $select);
$select->order($sortby);
$objects = $this->dbService->fetchObjects($type, $select);
return $objects;
}
/**
* Builds the basic select statement needed for filtering
* out which timesheet records we're interested in.
*
* @param $select the base select to add date filters to
* @return Zend_Db_Select
*/
private function filterBaseTimesheetQuery($select, $taskid = null, $projectId = null, $clientId = null, $start = null, $end = null)
{
if ($start) {
if (!preg_match('|[0-9]+-[0-9]+-[0-9]+ [0-9]+:[0-9]+:[0-9]+|', $start)) {
$start = date('Y-m-d 00:00:00', strtotime($start));
}
}
if ($end) {
if (!preg_match('|[0-9]+-[0-9]+-[0-9]+ [0-9]+:[0-9]+:[0-9]+|', $end)) {
$end = date('Y-m-d 23:59:59', strtotime($end));
}
}
// Filter for the project and client.
if ($projectId && !$clientId) {
$select->joinInner('project', 'project.id=task.projectid', array(new Zend_Db_Expr('project.title as projecttitle'), new Zend_Db_Expr('project.id as projectid')));
$select->joinLeft('client', 'client.id=project.clientid', array(new Zend_Db_Expr('client.title as clienttitle'), new Zend_Db_Expr('client.id as clientid')));
// need to get this project and all its children
$project = $this->getProject($projectId);
$ids = $project->getChildIds(true, null);
$ids[] = $project->id;
$this->dbService->applyWhereToSelect(array('project.id' => $ids), $select);
// $select->where('project.id = ?', $projectId);
}
if ($clientId) {
$select->joinInner('project', 'project.id=task.projectid', array(new Zend_Db_Expr('project.title as projecttitle'), new Zend_Db_Expr('project.id as projectid')));
$select->joinInner('client', 'client.id=project.clientid', array(new Zend_Db_Expr('client.title as clienttitle'), new Zend_Db_Expr('client.id as clientid')));
$select->where('client.id = ?', $clientId);
}
// If no IDs given, still join to get the relevant titles
if (!$projectId && !$clientId) {
$select->joinLeft('project', 'project.id=task.projectid', array(new Zend_Db_Expr('project.title as projecttitle'), new Zend_Db_Expr('project.id as projectid')));
$select->joinLeft('client', 'client.id=project.clientid', array(new Zend_Db_Expr('client.title as clienttitle'), new Zend_Db_Expr('client.id as clientid')));
}
// Here we add in the clauses for limiting based
// on the dates. The rules around this are that
// if the timesheet record STARTS AFTER the selected start
// time, or the record STARTS BEFORE the selected end
// time. That allows us to grab records that straddle
// tricky points (such as those that live either
// side of a day cut off point).
// The 4 hour limit in record length prevents the chance
// of this allowing something like 28 hour days.
if ($taskid != null) {
// If for one individual task, add the id to limit the query
$select->where('taskid = ?', $taskid);
$view->showTask = false;
if ($start) {
$start = strtotime($start);
// $select->where('timesheetrecord.endtime > ?', $start);
$select->where('timesheetrecord.starttime > ?', $start);
}
if ($end) {
$end = strtotime($end);
//$select->where('timesheetrecord.endtime < ?', $end);
$select->where('timesheetrecord.starttime < ?', $end);
}
} else {
if (!$start) {
// beginning of today
$time = time();
$start = date('Y-m-d 00:00:00', $time);
}
if (!$end) {
// End of today
$time = time();
$end = date('Y-m-d 23:59:59', $time);
}
$start = strtotime($start);
$end = strtotime($end);
$select->where('timesheetrecord.starttime > ?', $start)->where('timesheetrecord.starttime < ?', $end);
// where('timesheetrecord.endtime < ?', $end);
}
return $select;
}
