public function execute()
{
$updateTime = date('Y-m-d H:i:s', $this->lastRun);
$select = $this->dbService->select();
$select->from('project')->joinInner('task', 'task.projectid = project.id', new Zend_Db_Expr('task.id as taskid'))->where("task.updated > ?", $updateTime);
$projects = $this->dbService->fetchObjects('Project', $select);
$done = array();
foreach ($projects as $project) {
if (in_array($project->id, $done)) {
continue;
}
$done[] = $project->id;
echo "Updating time for project #{$project->id} " . $project->title . "\n";
$this->projectService->updateProjectEstimate($project);
}
$select = $this->dbService->select();
$select->from('project')->joinInner('feature', 'feature.projectid = project.id', new Zend_Db_Expr('feature.id as featureid'))->where("feature.updated > ?", $updateTime);
$projects = $this->dbService->fetchObjects('Project', $select);
foreach ($projects as $project) {
if (in_array($project->id, $done)) {
continue;
}
$done[] = $project->id;
echo "Updating time for project #{$project->id} " . $project->title . "\n";
$this->projectService->updateProjectEstimate($project);
}
}
/**
* Get a list of users for a particular client
*
* This must bind against the 'user' table to make sure there's both a
* user record AND a contact record under the given client name
*
* @param String $clientName
*/
public function getUsersForClient($id)
{
$select = $this->dbService->select();
/* @var $select Zend_Db_Select */
$select->from(mb_strtolower($this->userClass))->joinInner('contact', mb_strtolower($this->userClass) . '.contactid = contact.id')->joinInner('client', 'contact.clientid = client.id')->where('client.id=?', $id);
$items = $this->dbService->fetchObjects($this->userClass, $select);
return $items;
}
/**
* Get all the current subscribers to an item
*
* Returned array is indexed by username => watch details
*/
public function getSubscribers($id, $type)
{
if ($type == null) {
$type = get_class($id);
$id = $id->id;
}
$select = $this->dbService->select();
$select->from('itemwatch', '*')->where('itemtype = ?', $type)->where('itemid = ?', $id);
$select->order('created desc');
$items = $this->dbService->fetchObjects('ItemWatch', $select);
$subscribers = array();
foreach ($items as $item) {
$subscribers[$item->userid] = $item;
}
return $subscribers;
}
/**
* Get a summary of task timing for a given period
*
* @param RegisteredUser $user
* @param int $taskid
* @param int $projectid
* @param int $clientid
* @param time $start
* @param time $end
* @return ArrayObject
*/
public function getSummaryTimesheet($user = null, $taskid = null, $projectid = null, $clientid = null, $timesheet = -1, $start = null, $end = null)
{
$select = $this->dbService->select()->from('task', array(new Zend_Db_Expr('task.title as title'), 'id'))->joinLeft('crmuser', 'task.userid=crmuser.username', 'username')->joinLeft('timesheetrecord', 'task.id=timesheetrecord.taskid', new Zend_Db_Expr('SUM(endtime - starttime) as timespent'));
$select = $this->filterBaseTimesheetQuery($select, $taskid, $projectid, $clientid, $start, $end);
// If we weren't passed a user, just load
// one from the request
if ($user != null) {
$select->where('task.userid = ?', $user->getUsername());
}
if ($timesheet >= 0) {
$select->where('timesheetrecord.timesheetid = ?', $timesheet);
}
$select->group(new Zend_Db_Expr('task.id'));
$select->order('endtime DESC');
$tasks = $this->dbService->fetchObjects('task', $select);
return $tasks;
}
/**
* Get the expenses for a client
* @param array $where
*/
public function getExpenses($where = array(), $page = null, $number = null)
{
$select = $this->dbService->select();
/* @var $select Zend_Db_Select */
$select->from(strtolower('Expense'), '*');
$select->joinLeft('project', 'project.id=expense.projectid', 'project.title as projecttitle');
$select->joinLeft('client', 'client.id=expense.clientid', 'client.title as clienttitle');
$select->joinInner('crmuser', 'crmuser.username=expense.username', array('crmuser.firstname as firstname', 'crmuser.lastname as lastname'));
foreach ($where as $field => $value) {
$select->where($field . ' ?', $value);
}
$select->order('expensedate desc');
if (!is_null($page)) {
$select->limitPage($page, $number);
}
$items = $this->dbService->fetchObjects('Expense', $select);
return $items;
}
/**
* Get all items that are the pointy end of a linking tree. These items
* exist in a 'from' relationship, but NOT as a 'to' target of a link.
* @return ArrayObject
*/
public function getOrphanItems($type, $where = array(), $sortby = 'id asc')
{
/* @var $select Zend_Db_Select */
$select = $this->dbService->select();
$tableName = mb_strtolower($this->sanitizeType($type));
// $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
$select->from($tableName)->joinLeft('itemlink', 'itemlink.totype = ' . $this->dbService->quote($type) . ' and itemlink.toid=' . $tableName . '.id', 'itemlink.toid')->where(new Zend_Db_Expr('itemlink.toid is null '));
foreach ($where as $field => $value) {
if ($value instanceof Zend_Db_Expr && is_int($field)) {
$select->where($value);
} else {
$select->where($field . ' ?', $value);
}
}
$select->order($sortby);
$objects = $this->dbService->fetchObjects($type, $select);
return $objects;
}
/**
* Get contacts for a given client
*
* @param Client $client
*/
public function getContacts($client = null, $where = array(), $order = 'firstname asc', $page = null, $number = null)
{
if ($client) {
$where['clientid='] = $client->id;
}
$select = $this->dbService->select();
/* @var $select Zend_Db_Select */
$select->from('contact', '*');
$select->joinLeft('client', 'contact.clientid=client.id', 'client.title as company');
foreach ($where as $field => $value) {
$select->where($field . ' ?', $value);
}
if (!is_null($page)) {
$select->limitPage($page, $number);
}
$select->order($order);
$contacts = $this->dbService->fetchObjects('Contact', $select);
return $contacts;
}
/**
* 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;
}
/**
* 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;
}
