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);
}
}
public function getTotalEntries()
{
$select = $this->dbService->select();
/* @var $select Zend_Db_Select */
$select->from('trackerentry', new Zend_Db_Expr("count(*) as total"));
$count = $this->dbService->fetchOne($select);
return $count;
}
/**
* Get a list of related items for the passed in item.
*
* works by comparing the list of things this item has against what all other items have,
* and returns the results ordered by the item with the most matches
*/
public function getRelatedItems($item, $type = null)
{
// First get the tags for this item
$tags = $this->getItemTags($item);
$in = '';
$sep = '';
foreach ($tags as $tag) {
$in .= $sep . $this->dbService->quote($tag->tag);
$sep = ',';
}
if (!mb_strlen($in)) {
return new ArrayObject();
}
$in = '(' . $in . ')';
// Alright, now lets query for those items with these tags, ordering by the most tagged
$select = $this->dbService->select();
$select->from('tag', array('tag', 'itemid', 'itemtype', new Zend_Db_Expr('count(itemid) as score')));
$select->where(new Zend_Db_Expr('tag in ' . $in));
if ($type) {
$select->where('itemtype=?', $type);
}
// Make sure to ignore the current object
$select->where('itemid<>?', $item->id);
$select->where('itemtype<>?', mb_strtolower(get_class($item)));
$select->group('itemid');
$select->order('score desc');
$result = $this->dbService->query($select, null);
$items = new ArrayObject();
$tags = $result->fetchAll(Zend_Db::FETCH_ASSOC);
foreach ($tags as $tag) {
// get the item
$items[] = $this->dbService->getById($tag['itemid'], $tag['itemtype']);
}
return $items;
}
/**
* 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 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 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;
}
/**
* Creates a new user
*
* @param Array $params
* @return The created user
* @throws InvalidModelException
* @throws ExistingUserException
*/
public function createUser($params, $setAsAuthenticated = true, $role = User::ROLE_USER, $userType = null)
{
if ($userType == null) {
$userType = $this->userClass;
}
// Check if there's a user with this email first.
$select = $this->dbService->select();
$select->from(strtolower($userType), '*')->where('username=?', $params['username'])->orWhere('email=?', $params['email']);
$existing = $this->dbService->getObject($select, $userType);
if ($existing) {
throw new ExistingUserException($params['username'] . ' already exists');
}
$newPass = null;
if (isset($params['password'])) {
$newPass = $params['password'];
}
$params['role'] = $role;
// Create a user with initial information
$user = $userType;
$user = new $user();
$user->generateSaltedPassword($params['password']);
unset($params['password']);
$user->bind($params);
$validator = new ModelValidator();
if (!$validator->isValid($user)) {
throw new InvalidModelException($validator->getMessages());
}
// so now we save the user, then reset their password which emails,
// then we set them as the authenticated user.
if ($this->dbService->createObject($user)) {
$this->trackerService->track('create-user', $user->id);
if ($setAsAuthenticated) {
$this->authService->setAuthenticatedUser($user);
}
return $user;
}
return null;
}
/**
* 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;
}
