public static function singleton() {
if (!isset(self::$instance)) {
$c = __CLASS__;
self::$instance = new $c;
}
return self::$instance;
}
/**
* Return all projects that this user is part of
*
* @access public
* @param User $user
* @param
* @return array
*/
function getProjectsByUser(User $user, $additional_conditions = null)
{
$projects_table = Projects::instance()->getTableName(true);
$project_users_table = ProjectUsers::instance()->getTableName(true);
$projects = array();
$sql = "SELECT {$projects_table}.* FROM {$projects_table}, {$project_users_table} WHERE ({$projects_table}.`id` = {$project_users_table}.`project_id` AND {$project_users_table}.`user_id` = " . DB::escape($user->getId()) . ')';
if (trim($additional_conditions) != '') {
$sql .= " AND ({$additional_conditions})";
}
// if
$sql .= " ORDER BY {$projects_table}.`name`";
$rows = DB::executeAll($sql);
if (is_array($rows)) {
foreach ($rows as $row) {
$projects[] = Projects::instance()->loadFromRow($row);
}
// foreach
}
// if
return count($projects) ? $projects : null;
}
/**
* Return all projects that this company is member of
*
* @access public
* @param Company $company
* @param string $additional_conditions Additional SQL conditions
* @return array
*/
static function getProjectsByCompany(Company $company, $additional_conditions = null)
{
if ($company->isOwner()) {
return Projects::getAll();
}
$projects_table = Projects::instance()->getTableName(true);
$project_companies_table = ProjectCompanies::instance()->getTableName(true);
$projects = array();
$sql = "SELECT {$projects_table}.* FROM {$projects_table}, {$project_companies_table} WHERE ({$projects_table}.`id` = {$project_companies_table}.`project_id` AND {$project_companies_table}.`company_id` = " . DB::escape($company->getId()) . ')';
if (trim($additional_conditions) != '') {
$sql .= " AND ({$additional_conditions})";
}
$rows = DB::executeAll($sql);
if (is_array($rows)) {
foreach ($rows as $row) {
$projects[] = Projects::instance()->loadFromRow($row);
}
// foreach
}
// if
return count($projects) ? $projects : null;
}
/**
* Return manager instance
*
* @access protected
* @param void
* @return Projects
*/
function manager()
{
if (!$this->manager instanceof Projects) {
$this->manager = Projects::instance();
}
return $this->manager;
}
/**
* Return array of finished projects
*
* @access public
* @param void
* @return array
*/
function getFinishedProjects() {
if (is_null($this->finished_projects)) {
$projects_table = Projects::instance()->getTableName(true);
$this->finished_projects = ProjectUsers::getProjectsByUser($this, "$projects_table.`completed_on` > " . DB::escape(EMPTY_DATETIME));
} // if
return $this->finished_projects;
} // getFinishedProjects
/**
* This function will return paginated result. Result is an array where first element is
* array of returned object and second populated pagination object that can be used for
* obtaining and rendering pagination data using various helpers.
*
* Items and pagination array vars are indexed with 0 for items and 1 for pagination
* because you can't use associative indexing with list() construct
*
* @access public
* @param array $arguments Query argumens (@see find()) Limit and offset are ignored!
* @param integer $items_per_page Number of items per page
* @param integer $current_page Current page number
* @return array
*/
function paginate($arguments = null, $items_per_page = 10, $current_page = 1)
{
if (isset($this) && instance_of($this, 'Projects')) {
return parent::paginate($arguments, $items_per_page, $current_page);
} else {
return Projects::instance()->paginate($arguments, $items_per_page, $current_page);
//$instance =& Projects::instance();
//return $instance->paginate($arguments, $items_per_page, $current_page);
}
// if
}
/**
* Return array of active projects that this user have access
*
* @access public
* @param void
* @return array
*/
function getActiveProjects($sort = 'name')
{
trace(__FILE__, 'getActiveProjects()');
if (is_null($this->active_projects)) {
trace(__FILE__, '- initialize cache: active_projects');
$this->active_projects = array();
}
// if
if (!isset($this->active_projects[$sort])) {
$projects_table = Projects::instance()->getTableName(true);
$empty_datetime = DB::escape(EMPTY_DATETIME);
$this->active_projects[$sort] = ProjectUsers::getProjectsByUser($this, "{$projects_table}.`completed_on` = {$empty_datetime}", $sort);
}
// if
return $this->active_projects[$sort];
}
function getProjectIdsByUser(User $user, $additional_conditions = null, $order_by = null)
{
$projects_table = Projects::instance()->getTableName(true);
$project_users_table = ProjectUsers::instance()->getTableName(true);
$group_users_table = GroupUsers::instance()->getTableName(true);
$projects = array();
$usercond = "({$project_users_table}.`user_id` = " . DB::escape($user->getId()) . ")";
$groupcond = "({$project_users_table}.`user_id` IN (SELECT `group_id` FROM {$group_users_table} WHERE {$group_users_table}.`user_id` = " . DB::escape($user->getId()) . "))";
$commoncond = "{$projects_table}.`id` = {$project_users_table}.`project_id`";
$sql = "SELECT {$projects_table}.`id` as `id` FROM {$projects_table}, {$project_users_table} WHERE {$commoncond} AND ({$usercond} OR {$groupcond}) ";
if (trim($additional_conditions) != '') {
$sql .= " AND ({$additional_conditions})";
}
// if
if ($order_by) {
$sql .= " ORDER BY '" . $order_by;
} else {
$sql .= " ORDER BY {$projects_table}.`name`";
}
$rows = DB::executeAll($sql);
$ids = array();
foreach ($rows as $row) {
$ids[] = $row['id'];
}
return $ids;
}
/**
* Return all projects that this user is part of
*
* @access public
* @param User $user
* @param
* @return array
*/
function getProjectsByUser(User $user, $additional_conditions = null, $additional_sort = null)
{
trace(__FILE__, "getProjectsByUser(user, {$additional_conditions}, {$additional_sort})");
$projects_table = Projects::instance()->getTableName(true);
trace(__FILE__, "getProjectsByUser():1");
$project_users_table = ProjectUsers::instance()->getTableName(true);
trace(__FILE__, "getProjectsByUser():2");
$project_milestones_table = ProjectMilestones::instance()->getTableName(true);
trace(__FILE__, "getProjectsByUser():3");
$empty_datetime = DB::escape(EMPTY_DATETIME);
$projects = array();
if (trim($additional_sort) == 'milestone') {
$sql = "SELECT distinct {$projects_table}.* FROM {$projects_table}";
$sql .= " left outer join {$project_milestones_table} on {$project_milestones_table}.`project_id` = {$projects_table}.`id`";
$sql .= " inner join {$project_users_table} on {$projects_table}.`id` = {$project_users_table}.`project_id`";
$sql .= " where {$project_users_table}.`user_id` = " . DB::escape($user->getId()) . " and ({$project_milestones_table}.`completed_on` = {$empty_datetime} or isnull({$project_milestones_table}.`completed_on`))";
} else {
$sql = "SELECT {$projects_table}.* FROM {$projects_table}, {$project_users_table} WHERE ({$projects_table}.`id` = {$project_users_table}.`project_id` AND {$project_users_table}.`user_id` = " . DB::escape($user->getId()) . ')';
}
if (trim($additional_conditions) != '') {
$sql .= " AND ({$additional_conditions})";
}
// if
if (trim($additional_sort) == 'priority') {
$sql .= " ORDER BY isnull({$projects_table}.`priority`), {$projects_table}.`priority`, {$projects_table}.`name`";
} elseif (trim($additional_sort) == 'milestone') {
$sql .= " ORDER BY isnull({$project_milestones_table}.`due_date`), {$project_milestones_table}.`due_date`, {$projects_table}.`name` ";
} else {
$sql .= " ORDER BY {$projects_table}.`name`";
}
trace(__FILE__, "getProjectsByUser(): sql={$sql}");
$rows = DB::executeAll($sql);
trace(__FILE__, "getProjectsByUser(): sql={$sql} ok");
if (is_array($rows)) {
foreach ($rows as $row) {
$projects[] = Projects::instance()->loadFromRow($row);
}
// foreach
}
// if
return count($projects) ? $projects : null;
}
/**
* Return the user's workspaces query that returns user's workspaces ids.
* @param bool $active If null, all projects; if true, only active, if false, only archived
* @return string
*/
function getWorkspacesQuery($active = null, $additional_conditions = null)
{
//return $this->getActiveProjectIdsCSV();
$project_users_table = ProjectUsers::instance()->getTableName(true);
$group_users_table = GroupUsers::instance()->getTableName(true);
$usercond = "({$project_users_table}.`user_id` = " . DB::escape($this->getId()) . ")";
$groupcond = "({$project_users_table}.`user_id` IN (SELECT `group_id` FROM {$group_users_table} WHERE {$group_users_table}.`user_id` = " . DB::escape($this->getId()) . "))";
$addcond = $additional_conditions == null ? "" : "AND " . $additional_conditions;
if ($active === null) {
return "SELECT {$project_users_table}.`project_id` FROM {$project_users_table} WHERE ({$usercond} OR {$groupcond}) {$addcond}";
} else {
$projects_table = Projects::instance()->getTableName(true);
$empty_date = DB::escape(EMPTY_DATETIME);
$active_cond = $active ? "{$projects_table}.`completed_on` = {$empty_date}" : "{$projects_table}.`completed_on` <> {$empty_date}";
$projectcond = "({$project_users_table}.`project_id` = {$projects_table}.`id` AND {$active_cond})";
return "SELECT {$project_users_table}.`project_id` FROM {$project_users_table}, {$projects_table} WHERE ({$usercond} OR {$groupcond}) AND {$projectcond} {$addcond}";
}
}
/**
* Return all projects as tree view
*
* @access public
* @param User $user
* @param
* @return array
*/
function getProjectsByParent(User $user, $additional_conditions = null)
{
$projects_table = Projects::instance()->getTableName(true);
$all = self::getActiveProjects();
if (is_array($all)) {
foreach ($all as $proj) {
$projects[$proj->getParentId()][] = $proj;
}
// foreach
}
// if
return count($projects) ? $projects : null;
}
