/**
* Gets the list of locations expenses are located
* in.
*/
public function getExpenseLocations()
{
$query = 'select distinct(location) from expense';
$result = $this->dbService->query($query);
/* @var $result Zend_Db_Statement_Pdo */
$res = $result->fetchAll(PDO::FETCH_COLUMN);
return $res;
}
/**
* 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 all the releases for a given project
*
* @param Project $project
*/
public function getProjectReleases(Project $project)
{
$query = 'select distinct(`release`) from issue where projectid=?';
$result = $this->dbService->query($query, array($project->id));
/* @var $result Zend_Db_Statement_Pdo */
$res = $result->fetchAll(PDO::FETCH_COLUMN);
return $res;
}
/**
* Get the number of note threads in the system
*
* @param array $where
* @return int
*/
public function getNoteThreadCount($where = array())
{
$sql = "SELECT count(*) as total FROM `note` where id in\n (select id from note GROUP BY `attachedtoid`,`attachedtotype`)";
$result = $this->dbService->query($sql);
/* @var $result Zend_Db_Statement_Pdo */
$row = $result->fetch();
return $row['total'];
}
/**
* Get all the first letters of contact names
*/
public function getContactLetters()
{
$query = "SELECT DISTINCT UPPER(LEFT(firstname,1)) as letter FROM contact ORDER BY letter";
$result = $this->dbService->query($query);
$letters = array();
while ($row = $result->fetch(Zend_Db::FETCH_ASSOC)) {
$letters[] = $row['letter'];
}
return $letters;
}
/**
* Update a project's estimated time
*
* This method does NOT save the project explicitly - it is assumed that
* our caller will do that.
*/
public function updateProjectEstimate(Project $project)
{
if (!$project->id) {
return;
}
$estimate = 0;
// do we actually save this project and update its parents?
$update = false;
// if it's a milestone, then update based on all of its tasks
if ($project->ismilestone) {
$allTasks = $project->getContainedTasks();
$estimate = 0;
foreach ($allTasks as $task) {
$estimate += $task->estimated;
}
$taskestimate = $estimate ? $estimate / za()->getConfig('day_length', 8) : 0;
if ($taskestimate && $taskestimate != $project->taskestimate) {
$project->taskestimate = $taskestimate;
$update = true;
}
} else {
// otherwise, update based on the total of all of its direct children projects,
// plus any features attached at this project level
$select = $this->dbService->select();
/* @var $select Zend_Db_Select */
$select->from('feature', 'sum(estimated) as projectestimate');
$select->where('projectid=?', $project->id);
$result = $this->dbService->query($select, array());
/* @var $result Zend_Db_Statement */
$row = $result->fetch(Zend_Db::FETCH_ASSOC);
$estimate = ifset($row, 'projectestimate', 0);
// now update based on all its children projects/milestones
$children = $project->getChildProjects();
$taskestimate = 0;
$featureestimate = $estimate;
foreach ($children as $child) {
$taskestimate += $child->taskestimate;
$featureestimate += $child->featureestimate;
}
if ($taskestimate && $taskestimate != $project->taskestimate) {
$project->taskestimate = $taskestimate;
$update = true;
}
if ($featureestimate && $featureestimate != $project->featureestimate) {
$project->featureestimate = $featureestimate;
$project->estimated = $featureestimate * za()->getConfig('day_length', 8);
$update = true;
}
}
// Now, get all the time for this project
$summary = $this->getSummaryTimesheet(null, null, $project->id, null, -1, '2000-01-01 00:00:00', '2100-01-01 00:00:00');
$taken = 0;
foreach ($summary as $task) {
$taken += $task->timespent;
}
$grandchildren = $project->getAllSubProjects();
foreach ($grandchildren as $grandkid) {
$taken += $grandkid->currenttime;
}
$taken = $taken > 0 ? $taken / 3600 : 0;
if ($taken > 0 && $project->currenttime != $taken) {
$project->currenttime = $taken;
// note that we do NOT save this here; we're assuming our caller will
// save when appropriate
$update = true;
}
if ($update) {
// get its parent and update that too
if ($project->parentid) {
$parent = $this->getProject($project->parentid);
$this->saveProject($parent);
}
}
}
