function displayFiles($folder_id)
{
global $AppUI, $m, $a, $tab, $page;
global $current_uri;
global $canAccess, $canRead, $canEdit, $canAuthor, $canDelete;
global $canAccess_folders, $canRead_folders, $canEdit_folders;
global $canAuthor_folders, $canDelete_folders;
global $company_id, $project_id, $task_id;
global $allowedCompanies, $allowedProjects, $allowedTasks, $allowedFolders;
global $showProject, $cfObj, $dPconfig;
$df = $AppUI->getPref('SHDATEFORMAT');
$tf = $AppUI->getPref('TIMEFORMAT');
$file_types = dPgetSysVal('FileType');
$xpg_pagesize = 30;
//TODO?: Set by System Config Value ...
$xpg_totalrecs = countFiles($folder_id);
//get file count for folder
$xpg_total_pages = $xpg_totalrecs > $xpg_pagesize ? ceil($xpg_totalrecs / $xpg_pagesize) : 1;
$xpg_min = $xpg_pagesize * ($page - 1);
// This is where we start our record set from
$q = new DBQuery();
// most recent version info per file_project and file_version_id
$q->createTemp('files_count_max' . $folder_id);
$q->addTable('files', 'f');
$q->addQuery('DISTINCT count(f.file_id) as file_versions' . ', max(f.file_version) as file_lastversion' . ', file_version_id, f.file_project');
$q->addJoin('projects', 'p', 'p.project_id = f.file_project');
$q->addJoin('tasks', 't', 't.task_id = f.file_task');
$q->addJoin('file_folders', 'ff', 'ff.file_folder_id = f.file_folder');
$q->addWhere('f.file_folder = ' . $folder_id);
if (count($allowedProjects)) {
$q->addWhere('((' . implode(' AND ', $allowedProjects) . ') OR f.file_project = 0)');
}
if (count($allowedTasks)) {
$q->addWhere('((' . implode(' AND ', $allowedTasks) . ') OR f.file_task = 0)');
}
if (count($allowedFolders)) {
$q->addWhere('((' . implode(' AND ', $allowedFolders) . ') OR f.file_folder = 0)');
}
if ($company_id) {
$q->innerJoin('companies', 'co', 'co.company_id = p.project_company');
$q->addWhere('co.company_id = ' . $company_id);
if (count($allowedCompanies)) {
$q->addWhere('(' . implode(' AND ', $allowedCompanies) . ')');
}
}
$q->addGroup('f.file_version_id');
$q->addGroup('f.file_project');
$file_version_max_counts = $q->exec();
$q->clear();
// most recent version
$q->addTable('files', 'f');
$q->addQuery('f.*, fmc.file_versions, round(fmc.file_lastversion, 2) as file_lastversion' . ', u.user_username as file_owner, ff.file_folder_name' . ', ff.file_folder_id, ff.file_folder_name, p.project_name' . ', p.project_color_identifier, p.project_owner, c.contact_first_name' . ', c.contact_last_name, t.task_name, u.user_username as file_owner' . ', cc.contact_first_name as checkout_first_name' . ', cc.contact_last_name as checkout_last_name');
$q->addJoin('files_count_max' . $folder_id, 'fmc', '(fmc.file_lastversion=f.file_version AND fmc.file_version_id=f.file_version_id' . ' AND fmc.file_project=f.file_project)', 'inner');
$q->addJoin('projects', 'p', 'p.project_id = f.file_project');
$q->addJoin('users', 'u', 'u.user_id = f.file_owner');
$q->addJoin('contacts', 'c', 'c.contact_id = u.user_contact');
$q->addJoin('tasks', 't', 't.task_id = f.file_task');
$q->addJoin('file_folders', 'ff', 'ff.file_folder_id = f.file_folder');
$q->leftJoin('users', 'cu', 'cu.user_id = f.file_checkout');
$q->leftJoin('contacts', 'cc', 'cc.contact_id = cu.user_contact');
$q->addWhere('f.file_folder = ' . $folder_id);
if (count($allowedProjects)) {
$q->addWhere('((' . implode(' AND ', $allowedProjects) . ') OR f.file_project = 0)');
}
if (count($allowedTasks)) {
$q->addWhere('((' . implode(' AND ', $allowedTasks) . ') OR f.file_task = 0)');
}
if (count($allowedFolders)) {
$q->addWhere('((' . implode(' AND ', $allowedFolders) . ') OR f.file_folder = 0)');
}
if ($project_id) {
$q->addWhere('f.file_project = ' . $project_id);
}
if ($task_id) {
$q->addWhere('f.file_task = ' . $task_id);
}
if ($company_id) {
$q->innerJoin('companies', 'co', 'co.company_id = p.project_company');
$q->addWhere('co.company_id = ' . $company_id);
if (count($allowedCompanies)) {
$q->addWhere('(' . implode(' AND ', $allowedCompanies) . ')');
}
}
$q->addOrder('p.project_name');
$q->setLimit($xpg_pagesize, $xpg_min);
$files_sql = $q->prepare();
$q->clear();
// all versions
$q->addTable('files', 'f');
$q->addQuery('f.*, ff.file_folder_id, ff.file_folder_name, p.project_name' . ', p.project_color_identifier, p.project_owner, c.contact_first_name' . ', c.contact_last_name, t.task_name, u.user_username as file_owner');
$q->addJoin('projects', 'p', 'p.project_id = f.file_project');
$q->addJoin('users', 'u', 'u.user_id = f.file_owner');
$q->addJoin('contacts', 'c', 'c.contact_id = u.user_contact');
$q->addJoin('tasks', 't', 't.task_id = f.file_task');
$q->addJoin('file_folders', 'ff', 'ff.file_folder_id = f.file_folder');
$q->addWhere('f.file_folder = ' . $folder_id);
if (count($allowedProjects)) {
$q->addWhere('((' . implode(' AND ', $allowedProjects) . ') OR f.file_project = 0)');
}
if (count($allowedTasks)) {
$q->addWhere('((' . implode(' AND ', $allowedTasks) . ') OR f.file_task = 0)');
}
if (count($allowedFolders)) {
$q->addWhere('((' . implode(' AND ', $allowedFolders) . ') OR f.file_folder = 0)');
}
if ($project_id) {
$q->addWhere('f.file_project = ' . $project_id);
}
if ($task_id) {
$q->addWhere('f.file_task = ' . $task_id);
}
if ($company_id) {
$q->innerJoin('companies', 'co', 'co.company_id = p.project_company');
$q->addWhere('co.company_id = ' . $company_id);
if (count($allowedCompanies)) {
$q->addWhere('(' . implode(' AND ', $allowedCompanies) . ')');
}
}
$file_versions_sql = $q->prepare();
$q->clear();
//file arrays
$files = array();
$file_versions = array();
if ($canRead) {
$files = db_loadList($files_sql);
$file_versions = db_loadHashList($file_versions_sql, 'file_id');
}
$q->dropTemp('files_count_max' . $folder_id);
$q->exec();
if ($files == array()) {
return;
}
?>
<table width="100%" border="0" cellpadding="2" cellspacing="1" class="tbl">
<tr>
<th nowrap="nowrap"><?php
echo $AppUI->_('File Name');
?>
</th>
<th nowrap="nowrap"><?php
echo $AppUI->_('Description');
?>
</th>
<th nowrap="nowrap"><?php
echo $AppUI->_('Versions');
?>
</th>
<th nowrap="nowrap"><?php
echo $AppUI->_('Category');
?>
</th>
<th nowrap="nowrap"><?php
echo $AppUI->_('Task Name');
?>
</th>
<th nowrap="nowrap"><?php
echo $AppUI->_('Owner');
?>
</th>
<th nowrap="nowrap"><?php
echo $AppUI->_('Size');
?>
</th>
<th nowrap="nowrap"><?php
echo $AppUI->_('Date');
?>
</th>
<th nowrap="nowrap"><?php
echo $AppUI->_('co Reason');
?>
</th>
<th nowrap="nowrap"><?php
echo $AppUI->_('co');
?>
</th>
<th nowrap width="1"></th>
<th nowrap width="1"></th>
</tr>
<?php
$fp = -1;
$file_date = new CDate();
$id = 0;
foreach ($files as $row) {
$file_date = new CDate($row['file_date']);
$canEdit_file = getPermission('files', 'edit', $row['file_id']);
//single file
if ($fp != $row['file_project']) {
if (!$row['file_project']) {
$row['project_name'] = $AppUI->_('Not associated to projects');
$row['project_color_identifier'] = 'f4efe3';
}
if ($showProject) {
$style = 'background-color:#' . $row['project_color_identifier'] . ';color:' . bestColor($row['project_color_identifier']);
?>
<tr>
<td colspan="20" style="border: outset 2px #eeeeee;<?php
echo $style;
?>
">
<a href="?m=projects&a=view&project_id=<?php
echo $row['file_project'];
?>
">
<span style="<?php
echo $style;
?>
"><?php
echo $row['project_name'];
?>
</span></a>
</td>
</tr>
<?php
}
}
$fp = $row['file_project'];
?>
<form name="frm_remove_file_<?php
echo $row['file_id'];
?>
" action="?m=files"
method="post">
<input type="hidden" name="dosql" value="do_file_aed" />
<input type="hidden" name="del" value="1" />
<input type="hidden" name="file_id" value="<?php
echo $row['file_id'];
?>
" />
<input type="hidden" name="redirect" value="<?php
echo $current_uri;
?>
" />
</form>
<form name="frm_duplicate_file_<?php
echo $row['file_id'];
?>
" action="?m=files"
method="post">
<input type="hidden" name="dosql" value="do_file_aed" />
<input type="hidden" name="duplicate" value="1" />
<input type="hidden" name="file_id" value="<?php
echo $row['file_id'];
?>
" />
<input type="hidden" name="redirect" value="<?php
echo $current_uri;
?>
" />
</form>
<tr>
<td nowrap="8%">
<?php
$file_icon = getIcon($row['file_type']);
?>
<a href="./fileviewer.php?file_id=<?php
echo $row['file_id'];
?>
"
title="<?php
echo $row['file_description'];
?>
">
<?php
echo dPshowImage(DP_BASE_URL . '/modules/files/images/' . $file_icon, '16', '16');
?>
<?php
echo $row['file_name'];
?>
</a>
</td>
<td width="20%"><?php
echo $row['file_description'];
?>
</td>
<td width="5%" nowrap="nowrap" align="center">
<?php
$hidden_table = '';
echo $row['file_lastversion'];
if ($row['file_versions'] > 1) {
?>
<a href="#" onClick="expand('versions_<?php
echo $row['file_id'];
?>
');">
(<?php
echo $row['file_versions'];
?>
)
</a>
<?php
}
?>
</td>
<td width="10%" nowrap="nowrap" align="center">
<?php
echo $file_types[$row['file_category']];
?>
</td>
<td width="5%" align="center">
<a href="./index.php?m=tasks&a=view&task_id=<?php
echo $row['file_task'];
?>
">
<?php
echo $row['task_name'];
?>
</a>
</td>
<td width="15%" nowrap="nowrap">
<?php
echo $row["contact_first_name"] . ' ' . $row["contact_last_name"];
?>
</td>
<td width="5%" nowrap="nowrap" align="right">
<?php
echo file_size(intval($row['file_size']));
?>
</td>
<td width="15%" nowrap="nowrap" align="right">
<?php
echo $file_date->format($df . ' ' . $tf);
?>
</td>
<td width="10%"><?php
echo $row['file_co_reason'];
?>
</td>
<td nowrap="nowrap" align="center">
<?php
if ($canEdit && empty($row['file_checkout'])) {
?>
<a href="?m=files&a=co&file_id=<?php
echo $row['file_id'];
?>
">
<?php
echo dPshowImage(DP_BASE_URL . '/modules/files/images/up.png', '16', '16', 'checkout', 'checkout file');
?>
</a>
<?php
} else {
if ($row['file_checkout'] == $AppUI->user_id) {
?>
<a href="?m=files&a=addedit&ci=1&file_id=<?php
echo $row['file_id'];
?>
">
<?php
echo dPshowImage(DP_BASE_URL . '/modules/files/images/down.png', '16', '16', 'checkin', 'checkin file');
?>
</a>
<?php
} else {
if ($file['file_checkout'] == 'final') {
echo ' ' . $AppUI->_('final');
} else {
echo ' ' . $row['checkout_first_name'] . ' ' . $row['checkout_last_name'] . '<br />(' . $row['co_user'] . ')';
}
}
}
?>
</td>
<td nowrap="nowrap" align="right" width="48">
<?php
if (empty($row['file_checkout']) || $row['file_checkout'] == 'final') {
// Edit File
if ($canEdit || $row['project_owner'] == $AppUI->user_id) {
?>
<a href="./index.php?m=files&a=addedit&file_id=<?php
echo $row['file_id'];
?>
">
<?php
echo dPshowImage(DP_BASE_URL . '/modules/files/images/kedit.png', '16', '16', 'edit file', 'edit file');
?>
</a>
<?php
}
// Duplicate File
if ($canAuthor || $row['project_owner'] == $AppUI->user_id) {
?>
<a href="#"
onclick="document.frm_duplicate_file_<?php
echo $row['file_id'];
?>
.submit()">
<?php
echo dPshowImage(DP_BASE_URL . '/modules/files/images/duplicate.png', '16', '16', 'duplicate file', 'duplicate file');
?>
</a>
<?php
}
// Delete File
if ($canDelete || $row['project_owner'] == $AppUI->user_id) {
?>
<a href="#"
onclick="if (confirm('Are you sure you want to delete this file?')) {document.frm_remove_file_<?php
echo $row['file_id'];
?>
.submit()}">
<?php
echo dPshowImage(DP_BASE_URL . '/modules/files/images/remove.png', '16', '16', 'delete file', 'delete file');
?>
</a>
<?php
}
}
?>
</td>
<td nowrap="nowrap" align="center" width="1">
<?php
if ((empty($row['file_checkout']) || $row['file_checkout'] == 'final') && ($canEdit || $row['project_owner'] == $AppUI->user_id)) {
$bulk_op = 'onchange="(this.checked) ? addBulkComponent(' . $row['file_id'] . ') : removeBulkComponent(' . $row['file_id'] . ')"';
?>
<input type="checkbox" <?php
echo $bulk_op;
?>
name="chk_sub_sel_file_<?php
echo $file_row['file_id'];
?>
" />
<?php
}
?>
</td>
</tr>
<?php
if ($row['file_versions'] > 1) {
?>
<tr><td colspan="20">
<table style="display: none" id="versions_<?php
echo $row['file_id'];
?>
"
width="100%" border="0" cellpadding="2" cellspacing="1" class="tbl">
<tr>
<th nowrap="nowrap"><?php
echo $AppUI->_('File Name');
?>
</th>
<th nowrap="nowrap"><?php
echo $AppUI->_('Description');
?>
</th>
<th nowrap="nowrap"><?php
echo $AppUI->_('Versions');
?>
</th>
<th nowrap="nowrap"><?php
echo $AppUI->_('Category');
?>
</th>
<th nowrap="nowrap"><?php
echo $AppUI->_('Task Name');
?>
</th>
<th nowrap="nowrap"><?php
echo $AppUI->_('Owner');
?>
</th>
<th nowrap="nowrap"><?php
echo $AppUI->_('Size');
?>
</th>
<th nowrap="nowrap"><?php
echo $AppUI->_('Type');
?>
</th>
<th nowrap="nowrap"><?php
echo $AppUI->_('Date');
?>
</th>
<th nowrap="nowrap"width="1"> </th>
<th nowrap="nowrap"width="1"> </th>
</tr>
<?php
foreach ($file_versions as $file) {
if ($file['file_version_id'] == $row['file_version_id']) {
$file_icon = getIcon($file['file_type']);
$file_version_date = new Date($file['file_date']);
?>
<form name="frm_delete_sub_file_<?php
echo $file['file_id'];
?>
"
action="?m=files" method="post">
<input type="hidden" name="dosql" value="do_file_aed" />
<input type="hidden" name="del" value="1" />
<input type="hidden" name="file_id" value="<?php
echo $file['file_id'];
?>
" />
<input type="hidden" name="redirect" value="<?php
echo $current_uri;
?>
" />
</form>
<form name="frm_duplicate_sub_file_<?php
echo $file['file_id'];
?>
"
action="?m=files" method="post">
<input type="hidden" name="dosql" value="do_file_aed" />
<input type="hidden" name="duplicate" value="1" />
<input type="hidden" name="file_id" value="<?php
echo $file['file_id'];
?>
" />
<input type="hidden" name="redirect" value="<?php
echo $current_uri;
?>
" />
</form>
<tr>
<td nowrap="8%">
<a href="./fileviewer.php?file_id=<?php
echo $file['file_id'];
?>
"
title="<?php
echo $file['file_description'];
?>
">
<?php
echo dPshowImage(DP_BASE_URL . '/modules/files/images/' . $file_icon, '16', '16');
?>
<?php
echo $file['file_name'];
?>
</a>
</td>
<td width="20%"><?php
echo $file['file_description'];
?>
</td>
<td width="5%" nowrap="nowrap" align="center"><?php
echo $file['file_version'];
?>
</td>
<td width="10%" nowrap="nowrap" align="center">
<?php
echo $file_types[$file['file_category']];
?>
</td>
<td width="5%" align="center">
<a href="./index.php?m=tasks&a=view&task_id=<?php
echo $file['file_task'];
?>
">
<?php
echo $file['task_name'];
?>
</a>
</td>
<td width="15%" nowrap="nowrap">
<?php
echo $file["contact_first_name"] . ' ' . $file["contact_last_name"];
?>
</td>
<td width="5%" nowrap="nowrap" align="right">
<?php
echo file_size(intval($file['file_size']));
?>
</td>
<td nowrap="nowrap">
<?php
echo $row['file_type'];
?>
</td>
<td width="15%" nowrap="nowrap" align="right">
<?php
echo $file_version_date->format($df . ' ' . $tf);
?>
</td>
<td nowrap="nowrap" align="right" width="48">
<?php
if (empty($file['file_checkout']) || $file['file_checkout'] == 'final') {
// Edit File
if ($canEdit || $row['project_owner'] == $AppUI->user_id) {
?>
<a href="./index.php?m=files&a=addedit&file_id=<?php
echo $row['file_id'];
?>
">
<?php
echo dPshowImage(DP_BASE_URL . '/modules/files/images/kedit.png', '16', '16', 'edit file', 'edit file');
?>
</a>
<?php
}
// Duplicate File
if ($canAuthor) {
?>
<a href="#"
onclick="document.frm_duplicate_file_<?php
echo $row['file_id'];
?>
.submit()">
<?php
echo dPshowImage(DP_BASE_URL . '/modules/files/images/duplicate.png', '16', '16', 'duplicate file', 'duplicate file');
?>
</a>
<?php
}
// Delete File
if ($canDelete) {
?>
<a href="#"
onclick="if (confirm('<?php
echo $AppUI->_('Are you sure you want to delete this file?');
?>
')) {document.frm_remove_file_<?php
echo $row['file_id'];
?>
.submit()}">
<?php
echo dPshowImage(DP_BASE_URL . '/modules/files/images/remove.png', '16', '16', 'delete file', $AppUI->_('delete file'));
?>
</a>
<?php
}
}
?>
</td>
<td nowrap="nowrap" align="center" width="1">
<?php
if ((empty($row['file_checkout']) || $row['file_checkout'] == 'final') && ($canEdit || $row['project_owner'] == $AppUI->user_id)) {
$bulk_op = 'onchange="(this.checked) ? addBulkComponent(' . $row['file_id'] . ') : removeBulkComponent(' . $row['file_id'] . ')"';
?>
<input type="checkbox" <?php
echo $bulk_op;
?>
name="chk_sub_sel_file_<?php
echo $file_row['file_id'];
?>
" />
<?php
}
?>
</td>
</tr>
<?php
}
}
?>
</table>
</td></tr>
<?php
}
}
?>
</table>
<?php
shownavbar($xpg_totalrecs, $xpg_pagesize, $xpg_total_pages, $page, $folder_id);
echo "<br />";
}
function projects_list_data($user_id = false)
{
global $AppUI, $addPwOiD, $cBuffer, $company, $company_id, $company_prefix, $deny, $department;
global $dept_ids, $dPconfig, $orderby, $orderdir, $projects, $tasks_critical, $tasks_problems;
global $tasks_sum, $tasks_summy, $tasks_total, $owner, $projectTypeId, $project_status;
global $currentTabId;
$addProjectsWithAssignedTasks = $AppUI->getState('addProjWithTasks') ? $AppUI->getState('addProjWithTasks') : 0;
//for getting permissions on project records
$obj_project = new CProject();
// Let's delete temproary tables
$q = new DBQuery();
$table_list = array('tasks_sum', 'tasks_total', 'tasks_summy', 'tasks_critical', 'tasks_problems', 'tasks_users');
$q->dropTemp($table_list);
$q->exec();
$q->clear();
// Task sum table
// by Pablo Roca (pabloroca@mvps.org)
// 16 August 2003
$working_hours = $dPconfig['daily_working_hours'] ? $dPconfig['daily_working_hours'] : 8;
// GJB: Note that we have to special case duration type 24
// and this refers to the hours in a day, NOT 24 hours
$q->createTemp('tasks_sum');
$q->addTable('tasks', 't');
$q->addQuery('t.task_project, SUM(t.task_duration * t.task_percent_complete' . ' * IF(t.task_duration_type = 24, ' . $working_hours . ', t.task_duration_type)) / SUM(t.task_duration' . ' * IF(t.task_duration_type = 24, ' . $working_hours . ', t.task_duration_type)) AS project_percent_complete, SUM(t.task_duration' . ' * IF(t.task_duration_type = 24, ' . $working_hours . ', t.task_duration_type)) AS project_duration');
if ($user_id) {
$q->addJoin('user_tasks', 'ut', 'ut.task_id = t.task_id');
$q->addWhere('ut.user_id = ' . $user_id);
}
$q->addWhere('t.task_id = t.task_parent');
$q->addGroup('t.task_project');
$tasks_sum = $q->exec();
$q->clear();
// At this stage tasks_sum contains the project id, and the total of tasks as percentage complate and project duration.
// I.e. one record per project
// Task total table
$q->createTemp('tasks_total');
$q->addTable('tasks', 't');
$q->addQuery('t.task_project, COUNT(distinct t.task_id) AS total_tasks');
if ($user_id) {
$q->addJoin('user_tasks', 'ut', 'ut.task_id = t.task_id');
$q->addWhere('ut.user_id = ' . $user_id);
}
$q->addGroup('t.task_project');
$tasks_total = $q->exec();
$q->clear();
// tasks_total contains the total number of tasks for each project.
// temporary My Tasks
// by Pablo Roca (pabloroca@mvps.org)
// 16 August 2003
$q->createTemp('tasks_summy');
$q->addTable('tasks', 't');
$q->addQuery('t.task_project, COUNT(DISTINCT t.task_id) AS my_tasks');
$q->addWhere('t.task_owner = ' . ($user_id ? $user_id : $AppUI->user_id));
$q->addGroup('t.task_project');
$tasks_summy = $q->exec();
$q->clear();
// tasks_summy contains total count of tasks for each project that I own.
// temporary critical tasks
$q->createTemp('tasks_critical');
$q->addTable('tasks', 't');
$q->addQuery('t.task_project, t.task_id AS critical_task' . ', MAX(t.task_end_date) AS project_actual_end_date');
// MerlinYoda: we don't join tables if we don't get anything out of the process
// $q->addJoin('projects', 'p', 'p.project_id = t.task_project');
$q->addOrder('t.task_end_date DESC');
$q->addGroup('t.task_project');
$tasks_critical = $q->exec();
$q->clear();
// tasks_critical contains the latest ending task and its end date.
// temporary task problem logs
$q->createTemp('tasks_problems');
$q->addTable('tasks', 't');
$q->addQuery('t.task_project, tl.task_log_problem');
$q->addJoin('task_log', 'tl', 'tl.task_log_task = t.task_id');
$q->addWhere('tl.task_log_problem > 0');
$q->addGroup('t.task_project');
$tasks_problems = $q->exec();
$q->clear();
// tasks_problems contains an indication of any projects that have task logs set to problem.
if ($addProjectsWithAssignedTasks) {
// temporary users tasks
$q->createTemp('tasks_users');
$q->addTable('tasks', 't');
$q->addQuery('t.task_project, ut.user_id');
$q->addJoin('user_tasks', 'ut', 'ut.task_id = t.task_id');
if ($user_id) {
$q->addWhere('ut.user_id = ' . $user_id);
}
$q->addOrder('t.task_end_date DESC');
$q->addGroup('t.task_project');
$tasks_users = $q->exec();
$q->clear();
}
// tasks_users contains all projects with tasks that have user assignments. (isn't this getting pointless?)
// add Projects where the Project Owner is in the given department
if ($addPwOiD && isset($department)) {
$owner_ids = array();
$q->addTable('users', 'u');
$q->addQuery('u.user_id');
$q->addJoin('contacts', 'c', 'c.contact_id = u.user_contact');
$q->addWhere('c.contact_department = ' . $department);
$owner_ids = $q->loadColumn();
$q->clear();
}
if (isset($department)) {
/*
* If a department is specified, we want to display projects from the department
* and all departments under that, so we need to build that list of departments
*/
$dept_ids = array();
$q->addTable('departments');
$q->addQuery('dept_id, dept_parent');
$q->addOrder('dept_parent,dept_name');
$rows = $q->loadList();
addDeptId($rows, $department);
$dept_ids[] = $department;
}
$q->clear();
$q->addTable('projects', 'p');
$q->addQuery('p.project_id, p.project_status, p.project_color_identifier, p.project_type' . ', p.project_name, p.project_description, p.project_start_date' . ', p.project_end_date, p.project_color_identifier, p.project_company' . ', p.project_status, p.project_priority, com.company_name' . ', com.company_description, tc.critical_task, tc.project_actual_end_date' . ', if (tp.task_log_problem IS NULL, 0, tp.task_log_problem) AS task_log_problem' . ', tt.total_tasks, tsy.my_tasks, ts.project_percent_complete' . ', ts.project_duration, u.user_username');
$q->addJoin('companies', 'com', 'p.project_company = com.company_id');
$q->addJoin('users', 'u', 'p.project_owner = u.user_id');
$q->addJoin('tasks_critical', 'tc', 'p.project_id = tc.task_project');
$q->addJoin('tasks_problems', 'tp', 'p.project_id = tp.task_project');
$q->addJoin('tasks_sum', 'ts', 'p.project_id = ts.task_project');
$q->addJoin('tasks_total', 'tt', 'p.project_id = tt.task_project');
$q->addJoin('tasks_summy', 'tsy', 'p.project_id = tsy.task_project');
if ($addProjectsWithAssignedTasks) {
$q->addJoin('tasks_users', 'tu', 'p.project_id = tu.task_project');
}
if (isset($project_status) && $currentTabId != 500) {
$q->addWhere('p.project_status = ' . $project_status);
}
if (isset($department)) {
$q->addJoin('project_departments', 'pd', 'pd.project_id = p.project_id');
if (!$addPwOiD) {
$q->addWhere('pd.department_id in (' . implode(',', $dept_ids) . ')');
} else {
// Show Projects where the Project Owner is in the given department
$q->addWhere('p.project_owner IN (' . (!empty($owner_ids) ? implode(',', $owner_ids) : 0) . ')');
}
} else {
if ($company_id && !$addPwOiD) {
$q->addWhere('p.project_company = ' . $company_id);
}
}
if ($projectTypeId > -1) {
$q->addWhere('p.project_type = ' . $projectTypeId);
}
if ($user_id && $addProjectsWithAssignedTasks) {
$q->addWhere('(tu.user_id = ' . $user_id . ' OR p.project_owner = ' . $user_id . ')');
} else {
if ($user_id) {
$q->addWhere('p.project_owner = ' . $user_id);
}
}
if ($owner > 0) {
$q->addWhere('p.project_owner = ' . $owner);
}
$q->addGroup('p.project_id');
$q->addOrder($orderby . ' ' . $orderdir);
$obj_project->setAllowedSQL($AppUI->user_id, $q, null, 'p');
$projects = $q->loadList();
// retrieve list of records
// modified for speed
// by Pablo Roca (pabloroca@mvps.org)
// 16 August 2003
// get the list of permitted companies
$obj_company = new CCompany();
$companies = $obj_company->getAllowedRecords($AppUI->user_id, 'company_id,company_name', 'company_name');
if (count($companies) == 0) {
$companies = array(0);
}
// get the list of permitted companies
$companies = arrayMerge(array('0' => $AppUI->_('All')), $companies);
//get list of all departments, filtered by the list of permitted companies.
$q->clear();
$q->addTable('companies', 'c');
$q->addQuery('c.company_id, c.company_name, dep.*');
$q->addJoin('departments', 'dep', 'c.company_id = dep.dept_company');
$q->addJoin('projects', 'p', 'p.project_company = c.company_id');
$q->addWhere('p.project_status NOT IN (1, 4, 5, 6, 7)');
$q->addOrder('c.company_name, dep.dept_parent, dep.dept_name');
$obj_company->setAllowedSQL($AppUI->user_id, $q);
$active_companies = $q->loadList();
$q->clear();
$q->addTable('companies', 'c');
$q->addQuery('c.company_id, c.company_name, dep.*');
$q->addJoin('departments', 'dep', 'c.company_id = dep.dept_company');
$q->addJoin('projects', 'p', 'p.project_company = c.company_id');
$q->addOrder('c.company_name, dep.dept_parent, dep.dept_name');
$obj_company->setAllowedSQL($AppUI->user_id, $q);
$all_companies = $q->loadList();
//display the select list
$cBuffer = '<select name="department" onchange="javascript:document.pickCompany.submit()" class="text">';
$cBuffer .= '<option value="company_0" style="font-weight:bold;">' . $AppUI->_('All') . '</option>' . "\n";
$company = '';
$active_company_ids = array();
// Active companies first
$cBuffer .= '<optgroup label="Active">';
foreach ($active_companies as $row) {
if ($row['dept_parent'] == 0) {
if ($company != $row['company_id']) {
$cBuffer .= '<option value="' . $AppUI->___($company_prefix . $row['company_id']) . '" style="font-weight:bold;"' . ($company_id == $row['company_id'] ? 'selected="selected"' : '') . '>' . $AppUI->___($row['company_name']) . '</option>' . "\n";
$company = $row['company_id'];
$active_company_ids[] = $company;
}
if ($row['dept_parent'] != null) {
showchilddept($row);
findchilddept($rows, $row['dept_id']);
}
}
}
$cBuffer .= '</optgroup>';
// Inactive companies
$cBuffer .= '<optgroup label="Inactive">';
foreach ($all_companies as $row) {
if ($row['dept_parent'] == 0 and !in_array($row['company_id'], $active_company_ids)) {
if ($company != $row['company_id']) {
$cBuffer .= '<option value="' . $AppUI->___($company_prefix . $row['company_id']) . '" style="font-weight:bold;"' . ($company_id == $row['company_id'] ? 'selected="selected"' : '') . '>' . $AppUI->___($row['company_name']) . '</option>' . "\n";
$company = $row['company_id'];
}
if ($row['dept_parent'] != null) {
showchilddept($row);
findchilddept($rows, $row['dept_id']);
}
}
}
$cBuffer .= '</optgroup>';
$cBuffer .= '</select>';
}
$q->addWhere('((' . implode(' AND ', $allowedFolders) . ') OR f.file_folder = 0)');
}
if ($category_filter) {
$q->addWhere($category_filter);
}
if ($company_id) {
$q->addWhere('p.project_company = ' . $company_id);
}
if ($project_id) {
$q->addWhere('f.file_project = ' . $project_id);
}
if ($task_id) {
$q->addWhere('f.file_task = ' . $task_id);
}
// most recent version info per file_project and file_version_id
$r->createTemp('files_count_max');
$r->addTable('files', 'f');
$r->addQuery('DISTINCT count(f.file_id) as file_versions' . ', max(f.file_version) as file_lastversion' . ', f.file_version_id, f.file_project');
$r->addJoin('projects', 'p', 'p.project_id = f.file_project');
$r->addJoin('tasks', 't', 't.task_id = f.file_task');
$r->addJoin('file_folders', 'ff', 'ff.file_folder_id = f.file_folder');
if (count($allowedProjects)) {
$r->addWhere('((' . implode(' AND ', $allowedProjects) . ') OR f.file_project = 0)');
}
if (count($allowedTasks)) {
$r->addWhere('((' . implode(' AND ', $allowedTasks) . ') OR f.file_task = 0)');
}
if (count($allowedFolders)) {
$r->addWhere('((' . implode(' AND ', $allowedFolders) . ') OR f.file_folder = 0)');
}
if ($company_id) {
$orderby = $AppUI->getState('ProjIdxOrderBy') ? $AppUI->getState('ProjIdxOrderBy') : 'project_end_date';
$orderdir = $AppUI->getState('ProjIdxOrderDir') ? $AppUI->getState('ProjIdxOrderDir') : 'asc';
// get any records denied from viewing
$obj = new CProject();
$deny = $obj->getDeniedRecords($AppUI->user_id);
// Let's delete temproary tables
$q = new DBQuery();
$q->dropTemp('tasks_sum, tasks_summy, tasks_critical, tasks_problems');
$q->exec();
$q->clear();
// Task sum table
// by Pablo Roca (pabloroca@mvps.org)
// 16 August 2003
$working_hours = $dPconfig['daily_working_hours'];
// GJB: Note that we have to special case duration type 24 and this refers to the hours in a day, NOT 24 hours
$q->createTemp('tasks_sum');
$q->addTable('tasks');
$q->addQuery("task_project, COUNT(distinct task_id) AS total_tasks, \n\t\tSUM(task_duration * task_percent_complete * IF(task_duration_type = 24, " . $working_hours . ", task_duration_type))/\n\t\tSUM(task_duration * IF(task_duration_type = 24, " . $working_hours . ", task_duration_type)) AS project_percent_complete");
$q->addGroup('task_project');
$tasks_sum = $q->exec();
$q->clear();
// temporary My Tasks
// by Pablo Roca (pabloroca@mvps.org)
// 16 August 2003
$q->createTemp('tasks_summy');
$q->addTable('tasks');
$q->addQuery('task_project, COUNT(distinct task_id) AS my_tasks');
$q->addWhere("task_owner = {$AppUI->user_id}");
$q->addGroup('task_project');
$tasks_summy = $q->exec();
$q->clear();
function projects_list_data($user_id = false)
{
global $AppUI, $addPwOiD, $buffer, $company, $company_id, $company_prefix, $deny, $department, $dept_ids, $dPconfig, $orderby, $orderdir, $projects, $tasks_critical, $tasks_problems, $tasks_sum, $tasks_summy, $tasks_total, $owner;
$addProjectsWithAssignedTasks = $AppUI->getState('addProjWithTasks') ? $AppUI->getState('addProjWithTasks') : 0;
// get any records denied from viewing
$obj = new CProject();
$deny = $obj->getDeniedRecords($AppUI->user_id);
// Let's delete temproary tables
$q = new DBQuery();
$q->dropTemp('tasks_sum, tasks_total, tasks_summy, tasks_critical, tasks_problems, tasks_users');
$q->exec();
$q->clear();
// Task sum table
// by Pablo Roca (pabloroca@mvps.org)
// 16 August 2003
$working_hours = $dPconfig['daily_working_hours'] ? $dPconfig['daily_working_hours'] : 8;
// GJB: Note that we have to special case duration type 24 and this refers to the hours in a day, NOT 24 hours
$q->createTemp('tasks_sum');
$q->addTable('tasks');
$q->addQuery("task_project, SUM(task_duration * task_percent_complete * IF(task_duration_type = 24, {$working_hours}," . " task_duration_type)) / SUM(task_duration * IF(task_duration_type = 24, {$working_hours}," . " task_duration_type)) AS project_percent_complete, SUM(task_duration * IF(task_duration_type = 24," . " {$working_hours}, task_duration_type)) AS project_duration");
if ($user_id) {
$q->addJoin('user_tasks', 'ut', 'ut.task_id = tasks.task_id');
$q->addWhere('ut.user_id = ' . $user_id);
}
$q->addWhere("tasks.task_id = tasks.task_parent");
$q->addGroup('task_project');
$tasks_sum = $q->exec();
$q->clear();
// Task total table
$q->createTemp('tasks_total');
$q->addTable('tasks');
$q->addQuery("task_project, COUNT(distinct tasks.task_id) AS total_tasks");
if ($user_id) {
$q->addJoin('user_tasks', 'ut', 'ut.task_id = tasks.task_id');
$q->addWhere('ut.user_id = ' . $user_id);
}
$q->addGroup('task_project');
$tasks_total = $q->exec();
$q->clear();
// temporary My Tasks
// by Pablo Roca (pabloroca@mvps.org)
// 16 August 2003
$q->createTemp('tasks_summy');
$q->addTable('tasks');
$q->addQuery('task_project, COUNT(distinct task_id) AS my_tasks');
if ($user_id) {
$q->addWhere('task_owner = ' . $user_id);
} else {
$q->addWhere('task_owner = ' . $AppUI->user_id);
}
$q->addGroup('task_project');
$tasks_summy = $q->exec();
$q->clear();
// temporary critical tasks
$q->createTemp('tasks_critical');
$q->addTable('tasks');
$q->addQuery('task_project, task_id AS critical_task, MAX(task_end_date) AS project_actual_end_date');
$q->addJoin('projects', 'p', 'p.project_id = task_project');
$q->addOrder("task_end_date DESC");
$q->addGroup('task_project');
$tasks_critical = $q->exec();
$q->clear();
// temporary task problem logs
$q->createTemp('tasks_problems');
$q->addTable('tasks');
$q->addQuery('task_project, task_log_problem');
$q->addJoin('task_log', 'tl', 'tl.task_log_task = task_id');
$q->addWhere("task_log_problem > '0'");
$q->addGroup('task_project');
$tasks_problems = $q->exec();
$q->clear();
if ($addProjectsWithAssignedTasks) {
// temporary users tasks
$q->createTemp('tasks_users');
$q->addTable('tasks');
$q->addQuery('task_project');
$q->addQuery('ut.user_id');
$q->addJoin('user_tasks', 'ut', 'ut.task_id = tasks.task_id');
if ($user_id) {
$q->addWhere('ut.user_id = ' . $user_id);
}
$q->addOrder("task_end_date DESC");
$q->addGroup('task_project');
$tasks_users = $q->exec();
$q->clear();
}
// add Projects where the Project Owner is in the given department
if ($addPwOiD && isset($department)) {
$owner_ids = array();
$q->addTable('users');
$q->addQuery('user_id');
$q->addJoin('contacts', 'c', 'c.contact_id = user_contact');
$q->addWhere('c.contact_department = ' . $department);
$owner_ids = $q->loadColumn();
$q->clear();
}
if (isset($department)) {
//If a department is specified, we want to display projects from the department, and all departments under that, so we need to build that list of departments
$dept_ids = array();
$q->addTable('departments');
$q->addQuery('dept_id, dept_parent');
$q->addOrder('dept_parent,dept_name');
$rows = $q->loadList();
addDeptId($rows, $department);
$dept_ids[] = $department;
}
$q->clear();
// retrieve list of records
// modified for speed
// by Pablo Roca (pabloroca@mvps.org)
// 16 August 2003
// get the list of permitted companies
$obj = new CCompany();
$companies = $obj->getAllowedRecords($AppUI->user_id, 'company_id,company_name', 'company_name');
if (count($companies) == 0) {
$companies = array(0);
}
$q->addTable('projects');
$q->addQuery('projects.project_id, project_status, project_color_identifier, project_name, project_description, project_duration,
project_start_date, project_end_date, project_color_identifier, project_company, company_name, company_description, project_status,
project_priority, tc.critical_task, tc.project_actual_end_date, tp.task_log_problem, tt.total_tasks, tsy.my_tasks,
ts.project_percent_complete, user_username');
$q->addJoin('companies', 'com', 'projects.project_company = company_id');
$q->addJoin('users', 'u', 'projects.project_owner = u.user_id');
$q->addJoin('tasks_critical', 'tc', 'projects.project_id = tc.task_project');
$q->addJoin('tasks_problems', 'tp', 'projects.project_id = tp.task_project');
$q->addJoin('tasks_sum', 'ts', 'projects.project_id = ts.task_project');
$q->addJoin('tasks_total', 'tt', 'projects.project_id = tt.task_project');
$q->addJoin('tasks_summy', 'tsy', 'projects.project_id = tsy.task_project');
if ($addProjectsWithAssignedTasks) {
$q->addJoin('tasks_users', 'tu', 'projects.project_id = tu.task_project');
}
// DO we have to include the above DENY WHERE restriction, too?
//$q->addJoin('', '', '');
if (isset($department)) {
$q->addJoin('project_departments', 'pd', 'pd.project_id = projects.project_id');
}
if (!isset($department) && $company_id && !$addPwOiD) {
$q->addWhere("projects.project_company = '{$company_id}'");
}
if (isset($department) && !$addPwOiD) {
$q->addWhere("pd.department_id in ( " . implode(',', $dept_ids) . " )");
}
if ($user_id && $addProjectsWithAssignedTasks) {
$q->addWhere('(tu.user_id = ' . $user_id . ' OR projects.project_owner = ' . $user_id . ' )');
} elseif ($user_id) {
$q->addWhere('projects.project_owner = ' . $user_id);
}
if ($owner > 0) {
$q->addWhere('projects.project_owner = ' . $owner);
}
// Show Projects where the Project Owner is in the given department
if ($addPwOiD && !empty($owner_ids)) {
$q->addWhere('projects.project_owner IN (' . implode(',', $owner_ids) . ')');
}
$q->addGroup('projects.project_id');
$q->addOrder("{$orderby} {$orderdir}");
$obj->setAllowedSQL($AppUI->user_id, $q);
$projects = $q->loadList();
// get the list of permitted companies
$companies = arrayMerge(array('0' => $AppUI->_('All')), $companies);
//get list of all departments, filtered by the list of permitted companies.
$q->clear();
$q->addTable('companies');
$q->addQuery('company_id, company_name, dep.*');
$q->addJoin('departments', 'dep', 'companies.company_id = dep.dept_company');
$q->addOrder('company_name,dept_parent,dept_name');
$obj->setAllowedSQL($AppUI->user_id, $q);
$rows = $q->loadList();
//display the select list
$buffer = '<select name="department" onChange="document.pickCompany.submit()" class="text">';
$buffer .= '<option value="company_0" style="font-weight:bold;">' . $AppUI->_('All') . '</option>' . "\n";
$company = '';
foreach ($rows as $row) {
if ($row["dept_parent"] == 0) {
if ($company != $row['company_id']) {
$buffer .= '<option value="' . $company_prefix . $row['company_id'] . '" style="font-weight:bold;"' . ($company_id == $row['company_id'] ? 'selected="selected"' : '') . '>' . $row['company_name'] . '</option>' . "\n";
$company = $row['company_id'];
}
if ($row["dept_parent"] != null) {
showchilddept($row);
findchilddept($rows, $row["dept_id"]);
}
}
}
$buffer .= '</select>';
}
<?php
## Active Projects View for Calendar
## based on Companies: View Projects sub-table by gregorerhardt
##
global $AppUI, $company_id, $pstatus, $dPconfig;
$df = $AppUI->getPref('SHDATEFORMAT');
$project_types = dPgetSysVal('ProjectStatus');
// get any records denied from viewing
$obj = new CProject();
// Task sum table
// by Pablo Roca (pabloroca@mvps.org)
// 16 August 2003
$q = new DBQuery();
$q->createTemp('tasks_sum');
$q->addQuery('task_project, COUNT(DISTINCT task_id) AS total_tasks,
SUM(task_duration * task_duration_type * task_percent_complete) / SUM(task_duration * task_duration_type) AS project_percent_complete');
$q->addTable('tasks');
$q->addGroup('task_project');
$q->exec();
$q->clear();
// temporary My Tasks
// by Pablo Roca (pabloroca@mvps.org)
// 16 August 2003
$q->createTemp('tasks_summy');
$q->addQuery('task_project, COUNT(DISTINCT task_id) AS my_tasks');
$q->addTable('tasks');
$q->addWhere('task_owner = ' . $AppUI->user_id);
$q->addGroup('task_project');
$q->exec();
$q->clear();
