function _buildQuery() { $q = new DBQuery(); $q->addTable($this->table); $q->addTable('files'); $q->addQuery('*'); $q->addWhere("files.file_id = {$this->table}.file_id"); $sql = ''; foreach ($this->search_fields as $field) { $sql .= " {$field} LIKE '%{$this->keyword}%' or "; } $sql = substr($sql, 0, -4); $q->addWhere("({$sql})"); $q->addGroup('files.file_id'); return $q->prepare(true); }
$q->addTable('projects', 'pr'); $q->leftJoin('tasks', 't', 'task_project = project_id'); if (!empty($project_id)) { $q->addWhere('project_id = ' . (int) $project_id); } $all_tasks = $q->loadList(); $q->addQuery('*, round(sum(task_log_hours),2) as work'); $q->addTable('projects', 'pr'); $q->leftJoin('tasks', 't', 'task_project = project_id'); $q->leftJoin('user_tasks', 'ut', 'ut.task_id = t.task_id'); $q->leftJoin('users', 'u', 'ut.user_id = u.user_id'); $q->leftJoin('task_log', 'tl', 'task_log_task = t.task_id AND task_log_creator = u.user_id'); if (!empty($project_id)) { $q->addWhere('project_id = ' . (int) $project_id); } $q->addGroup('t.task_id, u.user_id'); $users_all = $q->loadList(); foreach ($users_all as $user) { $users_per_task[$user['task_id']][] = $user['user_id']; $users[$user['user_id']]['all'][$user['task_id']] = $user; $users[$user['user_id']]['name'] = !empty($user['user_username']) ? $user['user_username'] : $user['user_id']; $users[$user['user_id']]['hours'] = 0; $users[$user['user_id']]['completed'] = array(); $users[$user['user_id']]['inprogress'] = array(); $users[$user['user_id']]['pending'] = array(); $users[$user['user_id']]['overdue'] = array(); } $tasks['hours'] = 0; $tasks['inprogress'] = array(); $tasks['completed'] = array(); $tasks['pending'] = array();
$allowedProjects = $project->getAllowedSQL($AppUI->user_id); $working_hours = $w2Pconfig['daily_working_hours'] ? $w2Pconfig['daily_working_hours'] : 8; $q->addQuery('projects.project_id, project_color_identifier, project_name'); $q->addQuery('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'); $q->addQuery('company_name'); $q->addTable('projects'); $q->leftJoin('tasks', 't1', 'projects.project_id = t1.task_project'); $q->leftJoin('companies', 'c', 'company_id = project_company'); $q->leftJoin('project_departments', 'project_departments', 'projects.project_id = project_departments.project_id OR project_departments.project_id IS NULL'); $q->leftJoin('departments', 'departments', 'departments.dept_id = project_departments.department_id OR dept_id IS NULL'); $q->addWhere('t1.task_id = t1.task_parent'); $q->addWhere('projects.project_id=' . $project_id); if (count($allowedProjects)) { $q->addWhere($allowedProjects); } $q->addGroup('projects.project_id'); $q2 = new DBQuery(); $q2 = $q; $q2->addQuery('projects.project_id, COUNT(t1.task_id) as total_tasks'); $perms =& $AppUI->acl(); $projects = array(); if ($canViewTasks) { $prc = $q->exec(); echo db_error(); while ($row = $q->fetchRow()) { $projects[$row['project_id']] = $row; } $prc2 = $q2->exec(); echo db_error(); while ($row2 = $q2->fetchRow()) { $projects[$row2['project_id']] = !$projects[$row2['project_id']] ? array() : $projects[$row2['project_id']];
/** * Calculate the extent of utilization of user assignments * @param string hash a hash for the returned hashList * @param array users an array of user_ids calculating their assignment capacity * @return array returns hashList of extent of utilization for assignment of the users */ function getAllocation($hash = NULL, $users = NULL) { // if (! dPgetConfig('check_overallocation') && ! dPgetConfig('direct_edit_assignment')) { return array(); //} $q = new DBQuery(); // retrieve the systemwide default preference for the assignment maximum $q->addTable('user_preferences'); $q->addQuery('pref_value'); $q->addWhere("pref_user = 0 AND pref_name = 'TASKASSIGNMAX'"); $sql = $q->prepare(); $q->clear(); $result = db_loadHash($sql, $sysChargeMax); if (!$result) { $scm = 0; } else { $scm = $sysChargeMax['pref_value']; } /* * provide actual assignment charge, individual chargeMax * and freeCapacity of users' assignments to tasks */ $q->addTable('users', 'u'); $q->leftJoin('contacts', 'c', 'c.contact_id = u.user_contact'); $q->leftJoin('user_tasks', 'ut', 'ut.user_id = u.user_id'); $q->leftJoin('user_preferences', 'up', 'up.pref_user = u.user_id'); $q->addQuery("u.user_id, CONCAT(CONCAT_WS(' [', CONCAT_WS(' '" . ', contact_first_name, contact_last_name), IF(IFNULL((IFNULL(up.pref_value' . ', ' . $scm . ') - SUM(ut.perc_assignment)), up.pref_value) > 0' . ', IFNULL((IFNULL(up.pref_value, ' . $scm . ') - SUM(ut.perc_assignment))' . ', up.pref_value), 0)), ' . "'%]')" . ' AS userFC' . ', IFNULL(SUM(ut.perc_assignment), 0) AS charge, u.user_username' . ', IFNULL(up.pref_value,' . $scm . ') AS chargeMax' . ', IF(IFNULL((IFNULL(up.pref_value, ' . $scm . ') ' . '- SUM(ut.perc_assignment)), up.pref_value) > 0' . ', IFNULL((IFNULL(up.pref_value, ' . $scm . ') - SUM(ut.perc_assignment))' . ', up.pref_value), 0) AS freeCapacity'); if (!empty($users)) { // use userlist if available otherwise pull data for all users $q->addWhere('u.user_id IN (' . implode(',', $users) . ')'); } $q->addGroup('u.user_id'); $q->addOrder('contact_last_name, contact_first_name'); $sql = $q->prepare(); $q->clear(); //echo "<pre>$sql</pre>"; return db_loadHashList($sql, $hash); }
function displayFiles($folder) { global $m, $a, $tab, $AppUI, $xpg_min, $xpg_pagesize; global $deny1, $deny2, $project_id, $task_id, $showProject, $file_types, $cfObj; global $xpg_totalrecs, $xpg_total_pages, $page; global $company_id, $allowed_companies, $current_uri, $dPconfig; $canEdit = !getDenyEdit($m, $folder); $canRead = !getDenyRead($m, $folder); $df = $AppUI->getPref('SHDATEFORMAT'); $tf = $AppUI->getPref('TIMEFORMAT'); // SETUP FOR FILE LIST $q = new DBQuery(); $q->addTable('files'); $q->addQuery('files.*,count(file_version) as file_versions,round(max(file_version), 2) as file_lastversion,file_folder_id, file_folder_name,project_name, project_color_identifier,contact_first_name, contact_last_name,task_name,task_id'); $q->addJoin('projects', 'p', 'p.project_id = file_project'); $q->addJoin('users', 'u', 'u.user_id = file_owner'); $q->addJoin('contacts', 'c', 'c.contact_id = u.user_contact'); $q->addJoin('tasks', 't', 't.task_id = file_task'); $q->addJoin('file_folders', 'ff', 'ff.file_folder_id = file_folder'); $q->addWhere('file_folder = ' . $folder); if (count($deny1) > 0) { $q->addWhere('file_project NOT IN (' . implode(',', $deny1) . ')'); } if (count($deny2) > 0) { $q->addWhere('file_task NOT IN (' . implode(',', $deny2) . ')'); } if ($project_id) { $q->addWhere('file_project = ' . $project_id); } if ($task_id) { $q->addWhere('file_task = ' . $task_id); } if ($company_id) { $q->innerJoin('companies', 'co', 'co.company_id = p.project_company'); $q->addWhere('company_id = ' . $company_id); $q->addWhere('company_id IN (' . $allowed_companies . ')'); } $q->addGroup('file_folder'); $q->addGroup('project_name'); $q->addGroup('file_name'); $q->addOrder('file_folder'); $q->addOrder('project_name'); $q->addOrder('file_name'); $q->setLimit($xpg_pagesize, $xpg_min); $files_sql = $q->prepare(); $q->clear(); $q = new DBQuery(); $q->addTable('files'); $q->addQuery('files.file_id, file_version, file_project, file_name, file_task, file_description, user_username as file_owner, file_size, file_category, file_type, file_date, file_folder_name'); $q->addJoin('projects', 'p', 'p.project_id = file_project'); $q->addJoin('users', 'u', 'u.user_id = file_owner'); $q->addJoin('tasks', 't', 't.task_id = file_task'); $q->addJoin('file_folders', 'ff', 'ff.file_folder_id = file_folder'); $q->addWhere('file_folder = ' . $folder); if ($project_id) { $q->addWhere('file_project = ' . $project_id); } if ($task_id) { $q->addWhere('file_task = ' . $task_id); } if ($company_id) { $q->innerJoin('companies', 'co', 'co.company_id = p.project_company'); $q->addWhere('company_id = ' . $company_id); $q->addWhere('company_id IN (' . $allowed_companies . ')'); } $file_versions_sql = $q->prepare(); $q->clear(); $files = array(); $file_versions = array(); if ($canRead) { $files = db_loadList($files_sql); $file_versions = db_loadList($file_versions_sql); } if ($files === array()) { return 0; } ?> <table width="100%" border="0" cellpadding="2" cellspacing="1" class="tbl"> <tr> <th nowrap="nowrap"><?php echo $AppUI->_('File Name'); ?> </th> <th><?php echo $AppUI->_('Description'); ?> </th> <th><?php echo $AppUI->_('Versions'); ?> </th> <th><?php echo $AppUI->_('Category'); ?> </th> <th nowrap="nowrap"><?php echo $AppUI->_('Task Name'); ?> </th> <th><?php echo $AppUI->_('Owner'); ?> </th> <th><?php echo $AppUI->_('Size'); ?> </th> <th><?php echo $AppUI->_('Type'); ?> </a></th> <th><?php echo $AppUI->_('Date'); ?> </th> <th nowrap="nowrap"><?php echo $AppUI->_('co Reason'); ?> </th> <th><?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']); if ($fp != $row["file_project"]) { if (!$row["project_name"]) { $row["project_name"] = $AppUI->_('All Projects'); $row["project_color_identifier"] = 'f4efe3'; } if ($showProject) { $s = '<tr>'; $s .= '<td colspan="20" style="background-color:#' . $row["project_color_identifier"] . '">'; $s .= '<font color="' . bestColor($row["project_color_identifier"]) . '">'; if ($row['file_project'] > 0) { $href = './index.php?m=projects&a=view&project_id=' . $row['file_project']; } else { $href = './index.php?m=projects'; } $s .= '<a href="' . $href . '">' . $row["project_name"] . '</a>'; $s .= '</font></td></tr>'; echo $s; } } $fp = $row["file_project"]; if ($row['file_versions'] > 1) { $file = last_file($file_versions, $row['file_name'], $row['file_project']); } else { $file = $row; } ?> <form name="frm_remove_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_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%"> <?php $file_icon = getIcon($row['file_type']); echo "<a href=\"./fileviewer.php?file_id={$file['file_id']}\" title=\"{$file['file_description']}\"><img border=\"0\" width=\"16\" heigth=\"16\" src=\"" . DP_BASE_URL . "/modules/files/images/{$file_icon}\" /> {$row['file_name']}</a>"; ?> </td> <td width="20%"><?php echo $file['file_description']; ?> </td> <td width="5%" nowrap="nowrap" align="center"> <?php $hidden_table = ''; echo $row['file_lastversion']; if ($row['file_versions'] > 1) { echo ' <a href="#" onClick="expand(\'versions_' . $file['file_id'] . '\'); ">(' . $row['file_versions'] . ')</a>'; $hidden_table = '<tr><td colspan="20"> <table style="display: none" id="versions_' . $file['file_id'] . '" width="100%" border="0" cellpadding="2" cellspacing="1" class="tbl"> <tr> <th nowrap="nowrap">' . $AppUI->_('File Name') . '</th> <th>' . $AppUI->_('Description') . '</th> <th>' . $AppUI->_('Versions') . '</th> <th>' . $AppUI->_('Category') . '</th> <th nowrap="nowrap">' . $AppUI->_('Task Name') . '</th> <th>' . $AppUI->_('Owner') . '</th> <th>' . $AppUI->_('Size') . '</th> <th>' . $AppUI->_('Type') . '</a></th> <th>' . $AppUI->_('Date') . '</th> <th nowrap="nowrap">' . $AppUI->_('co Reason') . '</th> <th>' . $AppUI->_('co') . '</th> <th nowrap width="1"></th> <th nowrap width="1"></th> </tr> '; foreach ($file_versions as $file_row) { if ($file_row['file_name'] == $row['file_name'] && $file_row['file_project'] == $row['file_project']) { $file_icon = getIcon($file_row['file_type']); $file_date = new CDate($file_row['file_date']); $hidden_table .= ' <form name="frm_delete_sub_file_' . $file_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="' . $file_row['file_id'] . '" /> <input type="hidden" name="redirect" value="' . $current_uri . '" /> </form>'; $hidden_table .= ' <form name="frm_duplicate_sub_file_' . $file_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="' . $file_row['file_id'] . '" /> <input type="hidden" name="redirect" value="' . $current_uri . '" /> </form>'; $hidden_table .= ' <tr> <td nowrap="8%"><a href="./fileviewer.php?file_id=' . $file_row['file_id'] . '" title="' . $file_row['file_description'] . '">' . "<img border=\"0\" width=\"16\" heigth=\"16\" src=\"" . DP_BASE_URL . "/modules/files/images/{$file_icon}\" /> " . $file_row['file_name'] . ' </a></td> <td width="20%">' . $file_row['file_description'] . '</td> <td width="5%" nowrap="nowrap" align="center">' . $file_row['file_version'] . '</td> <td width="10%" nowrap="nowrap" align="center"><a href="./index.php?m=' . $m . '&a=' . $a . '&tab=' . ($file_row['file_category'] + 1) . '">' . $file_types[$file_row['file_category'] + 1] . '</a></td> <td width="5%" align="center"><a href="./index.php?m=tasks&a=view&task_id=' . $file_row["file_task"] . '">' . $row["task_name"] . '</a></td> <td width="15%" nowrap="nowrap">' . $row["contact_first_name"] . ' ' . $row["contact_last_name"] . '</td> <td width="5%" nowrap="nowrap" align="right">' . intval($file_row['file_size'] / 1024) . 'kb </td> <td width="15%" nowrap="nowrap">' . $file_row['file_type'] . '</td> <td width="15%" nowrap="nowrap" align="right">' . $file_date->format("{$df} {$tf}") . '</td> <td width="10%">' . $row['file_co_reason'] . '</td> <td nowrap="nowrap" align="center">'; if ($canEdit && empty($file_row['file_checkout'])) { $hidden_table .= '<a href="?m=files&a=co&file_id=' . $file_row['file_id'] . '">' . dPshowImage('./modules/files/images/up.png', '16', '16', 'checkout', 'checkout file') . '</a>'; } else { if ($row['file_checkout'] == $AppUI->user_id) { $hidden_table .= '<a href="?m=files&a=addedit&ci=1&file_id=' . $file_row['file_id'] . '">' . dPshowImage('./modules/files/images/down.png', '16', '16', 'checkin', 'checkin file') . '</a>'; } else { if ($file_row['file_checkout'] == 'final') { $hidden_table .= 'final'; } else { $q4 = new DBQuery(); $q4->addQuery("file_id, file_checkout, user_username as co_user, contact_first_name, contact_last_name"); $q4->addTable('files'); $q4->leftJoin('users', 'cu', 'cu.user_id = file_checkout'); $q4->leftJoin('contacts', 'co', 'co.contact_id = cu.user_contact'); $q4->addWhere('file_id = ' . $file_row['file_id']); $co_user = array(); $co_user = $q4->loadList(); $co_user = $co_user[0]; $q4->clear(); $hidden_table .= $co_user['contact_first_name'] . ' ' . $co_user['contact_last_name'] . '<br>(' . $co_user['co_user'] . ')'; } } } $hidden_table .= '</td>'; $hidden_table .= '<td nowrap="nowrap" align="right" width="48">'; if ($canEdit && (empty($file_row['file_checkout']) || $file_row['file_checkout'] == 'final' && ($canEdit || $row['project_owner'] == $AppUI->user_id))) { $hidden_table .= '<a href="./index.php?m=files&a=addedit&file_id=' . $file_row["file_id"] . '">' . dPshowImage('./modules/files/images/kedit.png', '16', '16', 'edit file', 'edit file') . "</a>" . '<a href="#" onclick="document.frm_duplicate_sub_file_' . $file_row['file_id'] . '.submit()">' . dPshowImage('./modules/files/images/duplicate.png', '16', '16', 'duplicate file', 'duplicate file') . "</a>" . '<a href="#" onclick="if (confirm(\'Are you sure you want to delete this file?\')) {document.frm_delete_sub_file_' . $file_row['file_id'] . '.submit()}">' . dPshowImage('./modules/files/images/remove.png', '16', '16', 'delete file', 'delete file') . "</a>"; } $hidden_table .= '</td>'; $hidden_table .= '<td nowrap="nowrap" align="right" width="1">'; if ($canEdit && (empty($row['file_checkout']) || $row['file_checkout'] == 'final' && ($canEdit || $row['project_owner'] == $AppUI->user_id))) { $bulk_op = 'onchange="(this.checked) ? addBulkComponent(' . $file_row['file_id'] . ') : removeBulkComponent(' . $file_row['file_id'] . ')"'; $hidden_table .= '<input type="checkbox" ' . $bulk_op . ' name="chk_sub_sel_file_' . $file_row['file_id'] . '" />'; } $hidden_table .= '</td>'; $hidden_table .= '</tr>'; } } $hidden_table .= '</table>'; //$hidden_table .= '</span>'; } ?> </td> <td width="10%" nowrap="nowrap" align="center"><a href="./index.php?m=<?php echo $m; ?> &a=<?php echo $a; ?> &view=categories&tab=<?php echo $file['file_category']; ?> "><?php echo $file_types[$file["file_category"]]; ?> </a></td> <td width="5%" align="center"><a href="./index.php?m=tasks&a=view&task_id=<?php echo $file["task_id"]; ?> "><?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 intval($file["file_size"] / 1024); ?> kb</td> <td width="15%" nowrap="nowrap"><?php echo $file["file_type"]; ?> </td> <td width="15%" nowrap="nowrap" align="right"><?php echo $file_date->format("{$df} {$tf}"); ?> </td> <td width="10%"><?php echo $file['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 $file['file_id']; ?> "><?php echo dPshowImage('./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 $file['file_id']; ?> "><?php echo dPshowImage('./modules/files/images/down.png', '16', '16', 'checkin', 'checkin file'); ?> </a> <?php } else { if ($file['file_checkout'] == 'final') { echo 'final'; } else { $q4 = new DBQuery(); $q4->addQuery("file_id, file_checkout, user_username as co_user, contact_first_name, contact_last_name"); $q4->addTable('files'); $q4->leftJoin('users', 'cu', 'cu.user_id = file_checkout'); $q4->leftJoin('contacts', 'co', 'co.contact_id = cu.user_contact'); $q4->addWhere('file_id = ' . $file['file_id']); $co_user = array(); $co_user = $q4->loadList(); $co_user = $co_user[0]; $q4->clear(); echo $co_user['contact_first_name'] . ' ' . $co_user['contact_last_name'] . '<br>(' . $co_user['co_user'] . ')'; } } } ?> </td> <td nowrap="nowrap" align="center" width="48"> <?php if ($canEdit && (empty($file['file_checkout']) || $file['file_checkout'] == 'final' && ($canEdit || $file['project_owner'] == $AppUI->user_id))) { echo '<a href="./index.php?m=files&a=addedit&file_id=' . $file["file_id"] . '">'; echo dPshowImage('./modules/files/images/kedit.png', '16', '16', 'edit file', 'edit file'); echo "</a>"; echo '<a href="#" onclick="document.frm_duplicate_file_' . $file['file_id'] . '.submit()">' . dPshowImage('./modules/files/images/duplicate.png', '16', '16', 'duplicate file', 'duplicate file') . '</a>'; echo '<a href="#" onclick="if (confirm(\'Are you sure you want to delete this file?\')) {document.frm_remove_file_' . $file['file_id'] . '.submit()}">' . dPshowImage('./modules/files/images/remove.png', '16', '16', 'delete file', 'delete file') . '</a>'; } ?> <td nowrap="nowrap" align="center" width="1"> <?php if ($canEdit && (empty($file['file_checkout']) || $file['file_checkout'] == 'final' && ($canEdit || $file['project_owner'] == $AppUI->user_id))) { $bulk_op = 'onchange="(this.checked) ? addBulkComponent(' . $file['file_id'] . ') : removeBulkComponent(' . $file['file_id'] . ')"'; echo '<input type="checkbox" ' . $bulk_op . ' name="chk_sel_file_' . $file['file_id'] . '" />'; } ?> </td> </tr> <?php echo $hidden_table; ?> <?php $hidden_table = ''; } ?> </table> <?php if ($xpg_totalrecs > $xpg_pagesize) { showfnavbar($xpg_totalrecs, $xpg_pagesize, $xpg_total_pages, $page, $folder); } echo "<br />"; }
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 />"; }
$q->addQuery('v1.visit_user'); $q->leftJoin('users', 'u', 'fm1.message_author = u.user_id'); $q->leftJoin('contacts', 'con', 'contact_id = user_contact'); $q->leftJoin('forum_messages', 'fm2', 'fm1.message_id = fm2.message_parent'); $q->leftJoin('forum_watch', 'fw', 'watch_user = '******' AND watch_topic = fm1.message_id'); $q->leftJoin('forum_visits', 'v1', 'v1.visit_user = '******' AND v1.visit_message = fm1.message_id'); $q->addWhere('fm1.message_forum = ' . (int) $forum_id); switch ($f) { case 1: $q->addWhere('watch_user IS NOT NULL'); break; case 2: $q->addWhere('(NOW() < DATE_ADD(fm2.message_date, INTERVAL 30 DAY) OR NOW() < DATE_ADD(fm1.message_date, INTERVAL 30 DAY))'); break; } $q->addGroup('fm1.message_id, fm1.message_parent'); $q->addOrder($orderby . ' ' . $orderdir); $topics = $q->loadList(); $crumbs = array(); $crumbs['?m=forums'] = 'forums list'; ?> <br /> <?php if (function_exists('styleRenderBoxTop')) { echo styleRenderBoxTop(); } ?> <table width="100%" cellspacing="1" cellpadding="2" border="0" class="tbl"> <form name="watcher" action="?m=forums&a=viewer&forum_id=<?php echo $forum_id; ?>
public static function getDepartments($AppUI, $companyId) { $perms = $AppUI->acl(); if ($AppUI->isActiveModule('departments') && $perms->checkModule('departments', 'view')) { $q = new DBQuery(); $q->addTable('departments'); $q->addQuery('departments.*, COUNT(contact_department) dept_users'); $q->addJoin('contacts', 'c', 'c.contact_department = dept_id'); $q->addWhere('dept_company = ' . (int) $companyId); $q->addGroup('dept_id'); $q->addOrder('dept_parent, dept_name'); $department = new CDepartment(); $department->setAllowedSQL($AppUI->user_id, $q); return $q->loadList(); } }
} } $users[$user]['hours'] += $users[$user]['all'][$task['task_id']]['work']; $tasks['hours'] += $users[$user]['all'][$task['task_id']]['work']; } } } $q = new DBQuery(); $q->addTable('files'); $q->addQuery('sum(file_size)'); if ($project_id) { $q->addWhere('file_project = ' . (int) $project_id); } else { $q->addWhere('file_project = 0'); } $q->addGroup('file_project'); $files = $q->loadResult(); $q->clear(); $ontime = round(100 * (1 - count($tasks['overdue']) / count($all_tasks) - count($tasks['completed']) / count($all_tasks))); ?> <table width="100%" border="1" cellpadding="0" cellspacing="0" class="tbl"> <tr> <th colspan="3"><?php echo $AppUI->_('Progress Chart (completed/in progress/pending)'); ?> </th> </tr> <tr> <td width="<?php echo round(count($tasks['completed']) / count($all_tasks) * 100);
$q->addJoin('forum_watch', 'fw', "watch_user = {$AppUI->user_id} AND watch_topic = fm1.message_id"); $q->addJoin('forum_visits', 'v1', "v1.visit_user = {$AppUI->user_id} AND v1.visit_message = fm2.message_id"); $q->addJoin('forum_visits', 'v2', "v2.visit_user = {$AppUI->user_id} AND v2.visit_message = fm1.message_id"); $q->addWhere("fm1.message_forum = {$forum_id}"); switch ($f) { case 1: $q->addWhere("watch_user IS NOT NULL"); break; case 2: $q->addWhere("(NOW() < DATE_ADD(fm2.message_date, INTERVAL 30 DAY) OR NOW() < DATE_ADD(fm1.message_date, INTERVAL 30 DAY))"); break; } $q->addGroup('fm1.message_id, fm1.message_parent, fm1.message_author, fm1.message_title, fm1.message_date, fm1.message_body, fm1.message_published'); $q->addOrder("{$orderby} {$orderdir}"); $topics = $q->loadList(); $crumbs = array(); $crumbs["?m=forums"] = "forums list"; ?> <table width="100%" cellspacing="1" cellpadding="2" border="0"> <tr> <td><?php echo breadCrumbs($crumbs); ?> </td> <td align="right">
$q = new DBQuery(); $q->addTable('users', 'u'); $q->addQuery('DISTINCT(user_id), user_username, contact_last_name, contact_first_name, permission_user, contact_email, company_name, contact_company'); $q->addJoin('contacts', 'con', 'user_contact = contact_id'); $q->addJoin('companies', 'com', 'contact_company = company_id'); $q->addJoin('permissions', 'per', 'user_id = permission_user'); if ($stub) { $q->addWhere("(UPPER(user_username) LIKE '{$stub}%' or UPPER(contact_first_name) LIKE '{$stub}%' OR UPPER(contact_last_name) LIKE '{$stub}%')"); } else { if ($where) { $where = $q->quote("%{$where}%"); $q->addWhere("(UPPER(user_username) LIKE {$where} or UPPER(contact_first_name) LIKE {$where} OR UPPER(contact_last_name) LIKE {$where})"); } } $q->addGroup('user_id'); $q->addOrder($orderby); $users = $q->loadList(); $canLogin = !isset($_REQUEST['inactive']) || $_REQUEST['inactive'] == 'false'; ?> <table cellpadding="7" cellspacing="0" border="0" width="100%" class="infopanel"> <tr> <td> <?php showButton('New User', '?m=admin&a=addedituser'); ?> </td> <td align="right"> <table class="internal"> <tr><td>Show:</td> <td><input id="idInactive" type="checkbox" name="inactive" <?php
## 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(); $q->addTable('projects', 'pr'); $q->addQuery('project_id, project_name, project_status, project_color_identifier, project_start_date, project_end_date, project_priority,
/** * Generic check for whether dependencies exist for this object in the db schema * * Can be overloaded/supplemented by the child class * @param string $msg Error message returned * @param int Optional key index * @param array Optional array to compiles standard joins: format [label=>'Label',name=>'table name',idfield=>'field',joinfield=>'field'] * @return true|false */ function canDelete(&$msg, $oid = null, $joins = null) { global $AppUI; // First things first. Are we allowed to delete? $acl =& $AppUI->acl(); if (!$acl->checkModuleItem($this->_tbl, "delete", $oid)) { $msg = $AppUI->_("noDeletePermission"); return false; } $k = $this->_tbl_key; if ($oid) { $this->{$k} = intval($oid); } if (is_array($joins)) { $select = "{$k}"; $join = ""; $q = new DBQuery(); $q->addTable($this->_tbl); $q->addWhere("{$k} = '" . $this->{$k} . "'"); $q->addGroup($k); foreach ($joins as $table) { $q->addQuery("COUNT(DISTINCT {$table['idfield']}) AS {$table['idfield']}"); $q->addJoin($table['name'], $table['name'], "{$table['joinfield']} = {$k}"); } $sql = $q->prepare(); $q->clear(); $obj = null; if (!db_loadObject($sql, $obj)) { $msg = db_error(); return false; } $msg = array(); foreach ($joins as $table) { $k = $table['idfield']; if ($obj->{$k}) { $msg[] = $AppUI->_($table['label']); } } if (count($msg)) { $msg = $AppUI->_("noDeleteRecord") . ": " . implode(', ', $msg); return false; } else { return true; } } return true; }
<TH ALIGN="center" VALIGN="top" class="hdr"><a href="?m=risks&sort=risk_owner">Owner</a></TH> <TH ALIGN="center" VALIGN="top" class="hdr"><a href="?m=risks&sort=risk_status">Status</a></TH> <TH ALIGN="center" VALIGN="top" class="hdr"><a href="?m=risks&sort=risk_note_date">Last Note</a></TH> </TR> <?php $projects = array_merge(array(0 => array('project_id' => 0)), $projects); foreach ($projects as $p) { $q->clear(); $q->addQuery('risks.*'); $q->addQuery('max(risk_note_date) as risk_note_date'); $q->addQuery('task_name'); $q->addTable('risks'); $q->leftJoin('risk_notes', 'r', 'risk_id = risk_note_risk'); $q->leftJoin('tasks', 't', 'task_id = risk_task'); $q->addWhere('risk_project = ' . $p['project_id']); $q->addGroup('risk_id'); $q->addOrder($order); if ($riskFilter != 0) { $q->addWhere('risk_status = ' . $riskFilter); } $risks = $q->loadList(); if (!empty($risks)) { // if(!isset($_GET['project_id'])){ echo '<TR><TD colspan="12" style="background-color:#' . $p['project_color_identifier'] . '"> <a href="?m=projects&a=view&project_id=' . $p['project_id'] . '"> <font color="' . bestColor($p["project_color_identifier"]) . '">' . $p['project_name'] . '</font> </a> </TD></TR>'; // } //---- Print Table Data ----// foreach ($risks as $row) { if (!$row['task_name']) {
$q->addTable('resources', 'a'); $q->addJoin('resource_tasks', 'b', 'b.resource_id = a.resource_id', 'inner'); $q->addJoin('resource_types', 'c', 'c.resource_type_id = a.resource_type', 'inner'); $q->addWhere('b.task_id = ' . (int) $task_id); $resources = $q->loadHashList('resource_id'); // Determine any other clashes. $resource_tasks = array(); if (count($resources)) { $q->clear(); $q->addQuery('b.resource_id, sum(b.percent_allocated) as total_allocated'); $q->addTable('tasks', 'a'); $q->addJoin('resource_tasks', 'b', 'b.task_id = a.task_id', 'inner'); $q->addWhere('b.resource_id IN (' . implode(',', array_keys($resources)) . ')'); $q->addWhere('task_start_date <= \'' . $obj->task_end_date . '\''); $q->addWhere('task_end_date >= \'' . $obj->task_start_date . '\''); $q->addGroup('resource_id'); $resource_tasks = $q->loadHashList(); } ?> <table class="std" width="100%" cellpadding="4" cellspacing="1"> <tr><th>Type</th><th>Resource</th><th>Allocation</th><th> </th></tr> <?php foreach ($resources as $res) { $output = '<tr><td class="hilite">' . $res['resource_type_name'] . '</td> <td class="hilite">' . $res['resource_name'] . '</td> <td class="hilite">' . $res['percent_allocated'] . '%</td><td class="warning">'; if (isset($resource_tasks[$res['resource_id']]) && $resource_tasks[$res['resource_id']] > $res['resource_max_allocation']) { $output .= 'OVERALLOCATED'; } $output .= ' </td></tr>'; echo $output;
$q2->addWhere($category_filter); } if ($company_id) { $q2->addWhere('p.project_company = ' . $company_id); } if ($project_id) { $q2->addWhere('f.file_project = ' . $project_id); } if ($task_id) { $q2->addWhere('f.file_task = ' . $task_id); } $q2->setLimit($xpg_pagesize, $xpg_min); // Adding an Order by that is different to a group by can cause // performance issues. It is far better to rearrange the group // by to get the correct ordering. $q2->addGroup('p.project_id'); $q2->addGroup('f.file_version_id DESC'); $q3 = new DBQuery(); $q3->addQuery('f.file_id, f.file_version, f.file_version_id, f.file_project, f.file_name' . ', f.file_task, t.task_name, f.file_description, f.file_checkout, f.file_co_reason' . ', u.user_username as file_owner, f.file_size, f.file_category, f.file_type' . ', f.file_date, cu.user_username as co_user, p.project_name' . ', p.project_color_identifier, p.project_owner, con.contact_first_name' . ', con.contact_last_name, co.contact_first_name as co_contact_first_name' . ', co.contact_last_name as co_contact_last_name '); $q3->addQuery('ff.*'); $q3->addTable('files', 'f'); $q3->addJoin('users', 'u', 'u.user_id = file_owner'); $q3->addJoin('contacts', 'con', 'con.contact_id = u.user_contact'); $q3->addJoin('file_folders', 'ff', 'ff.file_folder_id = f.file_folder'); $q3->addJoin('projects', 'p', 'p.project_id = f.file_project'); $q3->addJoin('tasks', 't', 't.task_id = f.file_task'); $q3->leftJoin('users', 'cu', 'cu.user_id = f.file_checkout'); $q3->leftJoin('contacts', 'co', 'co.contact_id = cu.user_contact'); if (count($allowedProjects)) { $q3->addWhere('((' . implode(' AND ', $allowedProjects) . ') OR f.file_project = 0)'); }
$q->addTable('resources', 'a'); $q->leftJoin('resource_tasks', 'b', 'b.resource_id = a.resource_id'); $q->leftJoin('resource_types', 'c', 'c.resource_type_id = a.resource_type'); $q->addWhere('b.task_id = ' . $task_id); $resources = $q->loadHashList('resource_id'); // Determine any other clashes. $resource_tasks = array(); if (count($resources)) { $q->clear(); $q->addQuery('b.resource_id, sum(b.percent_allocated) as total_allocated'); $q->addTable('tasks', 'a'); $q->leftJoin('resource_tasks', 'b', 'b.task_id = a.task_id'); $q->addWhere('b.resource_id in (' . implode(',', array_keys($resources)) . ')'); $q->addWhere("task_start_date <= '" . $obj->task_end_date . "'"); $q->addWhere("task_end_date >= '" . $obj->task_start_date . "'"); $q->addGroup("resource_id"); $resource_tasks = $q->loadHashList(); } ?> <table class='std' width='100%' cellpadding=4 cellspacing=1> <tr><th>Type</th><th>Resource</th><th>Allocation</th><th> </th></tr> <?php foreach ($resources as $res) { $output = "<tr><td class='hilite'>{$res['resource_type_name']}</td>\n\t<td class='hilite'>{$res['resource_name']}</td>\n\t<td class='hilite'>{$res['percent_allocated']}%</td><td class='warning'>"; if (isset($resource_tasks[$res['resource_id']]) && $resource_tasks[$res['resource_id']] > $res['resource_max_allocation']) { $output .= "OVERALLOCATED"; } $output .= " </td></tr>\n"; echo $output; } ?>
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>'; }
function getAssignedProjectsInRows($userId) { $q = new DBQuery(); $q->addQuery('project_id, project_status, project_name, project_description' . ', project_short_name'); $q->addTable('projects'); $q->addJoin('tasks', 't', 't.task_project = project_id'); $q->addJoin('user_tasks', 'ut', 'ut.task_id = t.task_id'); $q->addWhere('ut.user_id = ' . $userId); $q->addGroup('project_id'); $q->addOrder('project_name'); $this->setAllowedSQL($userId, $q); $allowedProjectRows = $q->exec(); return $allowedProjectRows; }
if (dPgetParam($_REQUEST, "showdetails", 0) == 1) { $start_date = date("Y-m-d", strtotime(dPgetParam($_REQUEST, "start_date", date("Y-m-d")))); $end_date = date("Y-m-d 23:59:59", strtotime(dPgetParam($_REQUEST, "end_date", date("Y-m-d")))); $q = new DBQuery(); $q->addTable('user_access_log', 'ual'); $q->addTable('users', 'u'); $q->addTable('contacts', 'c'); $q->addQuery('ual.*, u.*, c.*'); $q->addWhere('ual.user_id = u.user_id'); $q->addWhere('user_contact = contact_id '); if (user_id != 0) { $q->addWhere("ual.user_id='{$user_id}'"); } $q->addWhere("ual.date_time_in >='{$start_date}'"); $q->addWhere("ual.date_time_out <='{$end_date}'"); $q->addGroup('ual.date_time_last_action DESC'); $logs = $q->loadList(); ?> <table align="center" class="tbl" width="50%"> <th nowrap="nowrap" STYLE="background: #08245b"><?php echo $AppUI->_('Name(s)'); ?> </th> <th nowrap="nowrap" STYLE="background: #08245b"><?php echo $AppUI->_('Last Name'); ?> </th> <th nowrap="nowrap" STYLE="background: #08245b"><?php echo $AppUI->_('Internet Address'); ?> </th>
public function canDelete(&$msg, $oid = null, $joins = null) { global $AppUI; $q = new DBQuery(); // First things first. Are we allowed to delete? $acl =& $AppUI->acl(); if (!$acl->checkModule('task_log', 'delete')) { $msg = $AppUI->_('noDeletePermission'); return false; } $k = $this->_tbl_key; if ($oid) { $this->{$k} = intval($oid); } if (is_array($joins)) { $q->addTable($this->_tbl, 'k'); $q->addQuery($k); $i = 0; foreach ($joins as $table) { $table_alias = 't' . $i++; $q->leftJoin($table['name'], $table_alias, $table_alias . '.' . $table['joinfield'] . ' = ' . 'k' . '.' . $k); $q->addQuery('COUNT(DISTINCT ' . $table_alias . '.' . $table['idfield'] . ') AS ' . $table['idfield']); } $q->addWhere($k . ' = ' . $this->{$k}); $q->addGroup($k); $obj = null; $q->loadObject($obj); $q->clear(); if (!$obj) { $msg = db_error(); return false; } $msg = array(); foreach ($joins as $table) { $k = $table['idfield']; if ($obj->{$k}) { $msg[] = $AppUI->_($table['label']); } } if (count($msg)) { $msg = $AppUI->_('noDeleteRecord') . ': ' . implode(', ', $msg); return false; } } return true; }
function showcompany($company, $restricted = false) { global $AppUI, $allpdfdata, $log_start_date, $log_end_date, $log_all; $q = new DBQuery(); $q->addTable('projects'); $q->addQuery('project_id, project_name'); $q->addWhere('project_company = ' . (int) $company); $projects = $q->loadHashList(); $q->clear(); $q->addTable('companies'); $q->addQuery('company_name'); $q->addWhere('company_id = ' . (int) $company); $company_name = $q->loadResult(); $q->clear(); $table = '<h2>Company: ' . $company_name . '</h2> <table cellspacing="1" cellpadding="4" border="0" class="tbl">'; $project_row = ' <tr> <th>' . $AppUI->_('Project') . '</th>'; $pdfth[] = $AppUI->_('Project'); $project_row .= '<th>' . $AppUI->_('Total') . '</th></tr>'; $pdfth[] = $AppUI->_('Total'); $pdfdata[] = $pdfth; $hours = 0.0; $table .= $project_row; foreach ($projects as $project => $name) { $pdfproject = array(); $pdfproject[] = $name; $project_hours = 0; $project_row = '<tr><td>' . $name . '</td>'; $q->addTable('projects'); $q->addTable('tasks'); $q->addTable('task_log'); $q->addQuery('task_log_costcode, SUM(task_log_hours) as hours'); $q->addWhere('project_id = ' . (int) $project); $q->addWhere('project_active = 1'); if (($template_status = w2PgetConfig('template_projects_status_id')) != '') { $q->addWhere('project_status <> ' . (int) $template_status); } if ($log_start_date != 0 && !$log_all) { $q->addWhere('task_log_date >=' . $log_start_date); } if ($log_end_date != 0 && !$log_all) { $q->addWhere('task_log_date <=' . $log_end_date); } if ($restricted) { $q->addWhere('task_log_creator = ' . (int) $AppUI->user_id); } $q->addWhere('project_id = task_project'); $q->addWhere('task_id = task_log_task'); $q->addGroup('project_id'); $task_logs = $q->loadHashList(); $q->clear(); foreach ($task_logs as $task_log) { $project_hours += $task_log; } $project_row .= '<td style="text-align:right;">' . sprintf('%.2f', round($project_hours, 2)) . '</td></tr>'; $pdfproject[] = round($project_hours, 2); $hours += $project_hours; if ($project_hours > 0) { $table .= $project_row; $pdfdata[] = $pdfproject; } } if ($hours > 0) { $allpdfdata[$company_name] = $pdfdata; echo $table; echo '<tr><td>' . $AppUI->_('Total') . '</td><td style="text-align:right;">' . sprintf('%.2f', round($hours, 2)) . '</td></tr></table>'; } return $hours; }
<?php /* COMPANIES $Id: vw_depts.php,v 1.15.2.1 2006/07/31 11:54:15 nybod Exp $ */ ## ## Companies: View Projects sub-table ## global $AppUI, $company_id, $canEdit; $q = new DBQuery(); $q->addTable('departments'); $q->addQuery('departments.*, COUNT(contact_department) dept_users'); $q->addJoin('contacts', 'c', 'c.contact_department = dept_id'); $q->addWhere('dept_company = ' . $company_id); $q->addGroup('dept_id'); $q->addOrder('dept_parent, dept_name'); $sql = $q->prepare(); $q->clear(); // function renamed to avoid naming clash function showchilddept_comp(&$a, $level = 0) { global $AppUI; $s = ' <td> <a href="index.php?m=departments&a=addedit&dept_id=' . $a['dept_id'] . '" title="' . $AppUI->_('edit') . '"> ' . dPshowImage('images/edit.gif', 12, 12, '') . ' </td> <td>'; for ($y = 0; $y < $level; $y++) { if ($y + 1 == $level) { $s .= '<img src="images/corner-dots.gif" width="16" height="12" border="0">'; } else { $s .= '<img src="images/shim.gif" width="16" height="12" border="0">';
function showcompany($company, $restricted = false) { global $AppUI, $allpdfdata, $log_start_date, $log_end_date, $log_all; /* $sql=" SELECT billingcode_id, billingcode_name, billingcode_value FROM billingcode WHERE company_id=$company ORDER BY billingcode_name ASC "; $company_billingcodes=NULL; $ptrc=db_exec($sql); $nums=db_num_rows($ptrc); echo db_error(); for ($x=0; $x < $nums; $x++) { $row=db_fetch_assoc($ptrc); $company_billingcodes[$row['billingcode_id']]=$row['billingcode_name']; } */ $q = new DBQuery(); $q->addTable('projects'); $q->addQuery('project_id, project_name'); $q->addWhere('project_company = ' . $company); $projects = $q->loadHashList(); $q->addTable('companies'); $q->addQuery('company_name'); $q->addWhere('company_id = ' . $company); $company_name = $q->loadResult(); $table = '<h2>Company: ' . $company_name . '</h2> <table cellspacing="1" cellpadding="4" border="0" class="tbl">'; $project_row = ' <tr> <th>' . $AppUI->_('Project') . '</th>'; $pdfth[] = $AppUI->_('Project'); /* if (isset($company_billingcodes)) foreach ($company_billingcodes as $code) { $project_row .= '<th>' . $code . ' ' . $AppUI->_('Hours') . '</th>'; $pdfth[] = $code; } */ $project_row .= '<th>' . $AppUI->_('Total') . '</th></tr>'; $pdfth[] = $AppUI->_('Total'); $pdfdata[] = $pdfth; $hours = 0.0; $table .= $project_row; foreach ($projects as $project => $name) { $pdfproject = array(); $pdfproject[] = safe_utf8_decode($name); $project_hours = 0; $project_row = "<tr><td>{$name}</td>"; $q->addTable('projects', 'prj'); $q->addTable('tasks', 't'); $q->addTable('task_log', 'tl'); $q->addQuery('task_log_costcode, sum(task_log_hours) as hours'); $where = 'project_id = ' . $project; if ($log_start_date != 0 && !$log_all) { $where .= " AND task_log_date >= {$log_start_date}"; } if ($log_end_date != 0 && !$log_all) { $where .= " AND task_log_date <= {$log_end_date}"; } if ($restricted) { $where .= " AND task_log_creator = '" . $AppUI->user_id . "'"; } $where .= ' AND project_id = task_project' . ' AND task_id = task_log_task'; $q->addWhere($where); $q->addGroup('project_id'); //task_log_costcode $task_logs = $q->loadHashList(); /* if (isset($company_billingcodes)) foreach ($company_billingcodes as $code => $name) { if (isset($task_logs[$code])) { $value = sprintf("%.2f", $task_logs[$code]); $project_row .= '<td>' . $value . '</td>'; $project_hours += $task_logs[$code]; $pdfproject[] = $value; } else { $project_row .= '<td> </td>'; $pdfproject[] = 0; } } */ foreach ($task_logs as $task_log) { $project_hours += $task_log; } $project_row .= '<td>' . round($project_hours, 2) . '</td></tr>'; $pdfproject[] = round($project_hours, 2); $hours += $project_hours; if ($project_hours > 0) { $table .= $project_row; $pdfdata[] = $pdfproject; } } if ($hours > 0) { $allpdfdata[$company_name] = $pdfdata; echo $table; echo '<tr><td>Total</td><td>' . round($hours, 2) . '</td></tr></table>'; } return $hours; }
$q->addJoin('users', 'u', 'c.company_owner = u.user_id'); $q->addJoin('contacts', 'con', 'u.user_contact = con.contact_id'); $q->addJoin('projects', 'p2', 'c.company_id = p2.project_company AND p2.project_status = 7'); if (count($allowedCompanies) > 0) { $q->addWhere('c.company_id IN (' . implode(',', array_keys($allowedCompanies)) . ')'); } if ($companiesType) { $q->addWhere('c.company_type = ' . $company_type_filter); } if ($search_string != "") { $q->addWhere("c.company_name LIKE '%{$search_string}%'"); } if ($owner_filter_id > 0) { $q->addWhere("c.company_owner = {$owner_filter_id} "); } $q->addGroup('c.company_id'); $q->addOrder($orderby . ' ' . $orderdir); $rows = $q->loadList(); ?> <table width="100%" border="0" cellpadding="2" cellspacing="1" class="tbl"> <tr> <td nowrap="nowrap" width="60" align="right"> <?php echo $AppUI->_('sort by'); ?> : </td> <th nowrap="nowrap"> <a href="?m=companies&orderby=company_name" class="hdr"><?php echo $AppUI->_('Company Name'); ?> </a> </th>
function w2PgetUsersHashList($stub = null, $where = null, $orderby = 'contact_first_name, contact_last_name') { global $AppUI; $q = new DBQuery(); $q->addTable('users'); $q->addQuery('DISTINCT(user_id), user_username, contact_last_name, contact_first_name, contact_email, company_name, contact_company, dept_id, dept_name, CONCAT(contact_first_name,\' \',contact_last_name) contact_name, user_type'); $q->addJoin('contacts', 'con', 'contact_id = user_contact', 'inner'); if ($stub) { $q->addWhere('(UPPER(user_username) LIKE \'' . $stub . '%\' or UPPER(contact_first_name) LIKE \'' . $stub . '%\' OR UPPER(contact_last_name) LIKE \'' . $stub . '%\')'); } elseif ($where) { $where = $q->quote('%' . $where . '%'); $q->addWhere('(UPPER(user_username) LIKE ' . $where . ' OR UPPER(contact_first_name) LIKE ' . $where . ' OR UPPER(contact_last_name) LIKE ' . $where . ')'); } $q->addGroup('user_id'); $q->addOrder($orderby); // get CCompany() to filter by company $obj = new CCompany(); $companies = $obj->getAllowedSQL($AppUI->user_id, 'company_id'); $q->addJoin('companies', 'com', 'company_id = contact_company'); if ($companies) { $q->addWhere('(' . implode(' OR ', $companies) . ' OR contact_company=\'\' OR contact_company IS NULL OR contact_company = 0)'); } $dpt = new CDepartment(); $depts = $dpt->getAllowedSQL($AppUI->user_id, 'dept_id'); $q->addJoin('departments', 'dep', 'dept_id = contact_department'); if ($depts) { $q->addWhere('(' . implode(' OR ', $depts) . ' OR contact_department=0)'); } return $q->loadHashList('user_id'); }
$q->addTable('tasks'); $q->addQuery("COUNT(distinct tasks.task_id) AS total_tasks"); $q->addWhere('task_project = ' . $project_id); $hasTasks = $q->loadResult(); $q->clear(); // load the record data // GJB: Note that we have to special case duration type 24 and this refers to the hours in a day, NOT 24 hours if ($hasTasks) { $q->addTable('projects'); $q->addQuery("company_name, CONCAT_WS(', ',contact_last_name,contact_first_name) user_name, projects.*," . " SUM(t1.task_duration * t1.task_percent_complete" . " * IF(t1.task_duration_type = 24, {$working_hours}, t1.task_duration_type))" . " / SUM(t1.task_duration * IF(t1.task_duration_type = 24, {$working_hours}, t1.task_duration_type))" . " AS project_percent_complete"); $q->addJoin('companies', 'com', 'company_id = project_company'); $q->addJoin('users', 'u', 'user_id = project_owner'); $q->addJoin('contacts', 'con', 'contact_id = user_contact'); $q->addJoin('tasks', 't1', 'projects.project_id = t1.task_project'); $q->addWhere('project_id = ' . $project_id . ' AND t1.task_id = t1.task_parent'); $q->addGroup('project_id'); $sql = $q->prepare(); } else { $q->addTable('projects'); $q->addQuery("company_name, CONCAT_WS(' ',contact_first_name,contact_last_name) user_name, projects.*, " . "(0.0) AS project_percent_complete"); $q->addJoin('companies', 'com', 'company_id = project_company'); $q->addJoin('users', 'u', 'user_id = project_owner'); $q->addJoin('contacts', 'con', 'contact_id = user_contact'); $q->addWhere('project_id = ' . $project_id); $q->addGroup('project_id'); $sql = $q->prepare(); } $q->clear(); $obj = null; if (!db_loadObject($sql, $obj)) { $AppUI->setMsg('Project');
$q = new DBQuery(); $q->addQuery('count(file_id)'); $q->addTable('files', 'f'); if ($catsql) { $q->addWhere($catsql); } if ($company_id) { $q->addWhere("project_company = {$company_id}"); } if ($project_id) { $q->addWhere("file_project = {$project_id}"); } if ($task_id) { $q->addWhere("file_task = {$task_id}"); } $q->addGroup("file_version_id"); $project->setAllowedSQL($AppUI->user_id, $q, 'file_project'); $task->setAllowedSQL($AppUI->user_id, $q, 'file_task'); // SETUP FOR FILE LIST $q2 = new DBQuery(); $q2->addQuery(array('f.*', 'max(f.file_id) as latest_id', 'count(f.file_version) as file_versions', 'round(max(f.file_version),2) as file_lastversion')); $q2->addTable('files', 'f'); //$q2->leftJoin('users', 'cu', 'cu.user_id = f.file_checkout'); //$q2->leftJoin('users', 'u', 'u.user_id = f.file_owner'); //$q2->leftJoin('contacts', 'cont', 'cont.contact_id = u.user_contact'); $project->setAllowedSQL($AppUI->user_id, $q2, 'file_project'); $task->setAllowedSQL($AppUI->user_id, $q2, 'file_task'); if ($catsql) { $q2->addWhere($catsql); } if ($company_id) {
$q->addWhere('pr.project_status <> ' . (int) $template_status); } } if (!$showLowTasks) { $q->addWhere('task_priority >= 0'); } if (!$showHoldProjs) { $q->addWhere('project_active = 1'); } if (!$showDynTasks) { $q->addWhere('task_dynamic <> 1'); } if ($showPinned) { $q->addWhere('task_pinned = 1'); } $q->addGroup('ta.task_id'); if ($sortByName) { $q->addOrder('ta.task_name, ta.task_end_date'); } else { $q->addOrder('ta.task_end_date'); } $q->addOrder('task_priority DESC'); ############################################################## } else { // pull tasks $q = new DBQuery(); $q->addTable('tasks', 't'); $q->addQuery('t.task_id, task_parent, task_name, task_start_date, task_end_date, task_duration, task_duration_type, task_priority, task_percent_complete, task_order, task_project, task_milestone, project_name, task_dynamic'); $q->addJoin('projects', 'p', 'project_id = t.task_project', 'inner'); if ($sortByName) { $q->addOrder('project_id, t.task_name, task_start_date');
$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(); // temporary critical tasks $q->createTemp('tasks_critical'); $q->addTable('tasks');