/** Retrieve tasks with first task_end_dates within given project
* @param int Project_id
* @param int SQL-limit to limit the number of returned tasks
* @return array List of criticalTasks
*/
function getCriticalTasksInverted($project_id = null, $limit = 1)
{
if (!$project_id) {
$result = array();
$result[0]['task_end_date'] = '0000-00-00 00:00:00';
return $result;
} else {
$q = new w2p_Database_Query();
$q->addTable('tasks');
$q->addWhere('task_project = ' . (int) $project_id . ' AND NOT ISNULL( task_end_date ) AND task_end_date <> \'0000-00-00 00:00:00\'');
$q->addOrder('task_start_date ASC');
$q->setLimit($limit);
return $q->loadList();
}
}
public static function getContactByEmail($email)
{
$q = new w2p_Database_Query();
$q->addTable('users');
$q->addQuery('contact_first_name, contact_last_name');
$q->addJoin('contacts', 'con', 'contact_id = user_contact', 'inner');
$q->leftJoin('contacts_methods', 'cm', 'cm.contact_id = user_contact');
$q->addWhere("cm.method_value = '{$email}'");
//TODO: add primary email
$q->setLimit(1);
$r = $q->loadResult();
$result = is_array($r) ? $r[0]['contact_first_name'] . ' ' . $r[0]['contact_last_name'] : 'User Not Found';
return $result;
}
</table>
<?php
}
?>
</td>
<?php
if (w2PgetParam($_REQUEST, 'tab', 0) == 0) {
?>
<td>
<?php
$q = new w2p_Database_Query();
$q->addTable('user_access_log', 'ual');
$q->addQuery('user_access_log_id, ( unix_timestamp( \'' . $q->dbfnNowWithTZ() . '\' ) - unix_timestamp( date_time_in ) ) / 3600 as hours, ( unix_timestamp( \'' . $q->dbfnNowWithTZ() . '\' ) - unix_timestamp( date_time_last_action ) ) / 3600 as idle, if(isnull(date_time_out) or date_time_out =\'0000-00-00 00:00:00\',\'1\',\'0\') as online');
$q->addWhere('user_id = ' . (int) $row['user_id']);
$q->addOrder('user_access_log_id DESC');
$q->setLimit(1);
$user_logs = $q->loadList();
if ($user_logs) {
foreach ($user_logs as $row_log) {
if ($row_log['online'] == '1') {
echo '<span style="color: green">' . $row_log['hours'] . ' ' . $AppUI->_('hrs.') . '( ' . $row_log['idle'] . ' ' . $AppUI->_('hrs.') . ' ' . $AppUI->_('idle') . ') - ' . $AppUI->_('Online');
} else {
echo '<span style="color: red">' . $AppUI->_('Offline');
}
}
} else {
echo '<span style="color: grey">' . $AppUI->_('Never Visited');
}
echo '</span>';
}
?>
/** Retrieve tasks with latest task_end_dates within given project
* @param int Project_id
* @param int SQL-limit to limit the number of returned tasks
* @return array List of criticalTasks
*/
public function getCriticalTasks($project_id = null, $limit = 1)
{
$project_id = !empty($project_id) ? $project_id : $this->project_id;
$q = new w2p_Database_Query();
$q->addTable('tasks');
$q->addWhere('task_project = ' . (int) $project_id . ' AND task_end_date IS NOT NULL AND task_end_date <> \'0000-00-00 00:00:00\'');
$q->addOrder('task_end_date DESC');
$q->setLimit($limit);
return $q->loadList();
}
/**
* @param $row
*
* @return Array
*/
function __extract_from_vw_usr($row)
{
$q = new w2p_Database_Query();
$q->addTable('user_access_log', 'ual');
$q->addQuery('user_access_log_id, ( unix_timestamp( \'' . $q->dbfnNowWithTZ() . '\' ) - unix_timestamp( date_time_in ) ) / 3600 as hours, ( unix_timestamp( \'' . $q->dbfnNowWithTZ() . '\' ) - unix_timestamp( date_time_last_action ) ) / 3600 as idle, if(isnull(date_time_out) or date_time_out =\'0000-00-00 00:00:00\',\'1\',\'0\') as online');
$q->addWhere('user_id = ' . (int) $row['user_id']);
$q->addOrder('user_access_log_id DESC');
$q->setLimit(1);
$user_logs = $q->loadList();
return $user_logs;
}
$q->addWhere('history_user = user_id');
$q->addTable('contacts');
$q->addWhere('contact_id = user_contact');
$q->addWhere($filter);
$count = (int) $q->loadResult();
$q = new w2p_Database_Query();
$q->addQuery('history_date, history_id, history_item, history_table, history_description, history_action');
$q->addQuery('CONCAT(contact_first_name, \' \', contact_last_name) AS history_user_name');
$q->addTable('history', 'h');
$q->addTable('users');
$q->addWhere('history_user = user_id');
$q->addTable('contacts');
$q->addWhere('contact_id = user_contact');
$q->addWhere($filter);
$q->addOrder('history_date DESC');
$q->setLimit($limit, $offset);
$history = $q->loadList();
} else {
$history = array();
}
$pages = (int) ($count / $limit) + 1;
$max_pages = 20;
if ($pages > $max_pages) {
$first_page = max($page - (int) ($max_pages / 2), 1);
$last_page = min($first_page + $max_pages - 1, $pages);
} else {
$first_page = 1;
$last_page = $pages;
}
?>
if (count($allowedTasks)) {
$q2->addWhere('( ( ' . implode(' AND ', $allowedTasks) . ') OR file_task = 0 )');
}
if ($project_id) {
$q2->addWhere('file_project = ' . (int) $project_id);
}
if ($task_id) {
$q2->addWhere('file_task = ' . (int) $task_id);
}
if ($company_id) {
$q2->addWhere('project_company = ' . (int) $company_id);
}
if ($catsql) {
$q2->addWhere($catsql);
}
$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('file_version_id DESC');
$q3 = new w2p_Database_Query();
$q3->addTable('files');
$q3->addQuery('file_id, file_version, file_project, file_name, file_task,
file_description, u.user_username as file_owner, file_size, file_category,
task_name, file_version_id, file_checkout, file_co_reason, file_type,
file_date, cu.user_username as co_user, project_name,
project_color_identifier, 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('file_folder_id, file_folder_name');
function displayFiles($AppUI, $folder_id, $task_id, $project_id, $company_id)
{
global $m, $a, $tab, $xpg_min, $xpg_pagesize, $showProject, $file_types, $cfObj, $xpg_totalrecs, $xpg_total_pages, $page, $company_id, $allowed_companies, $current_uri, $w2Pconfig, $canEdit, $canRead;
$df = $AppUI->getPref('SHDATEFORMAT');
$tf = $AppUI->getPref('TIMEFORMAT');
// SETUP FOR FILE LIST
$q = new w2p_Database_Query();
$q->addQuery('f.*, max(f.file_id) as latest_id, count(f.file_version) as file_versions, round(max(file_version), 2) as file_lastversion');
$q->addQuery('ff.*');
$q->addTable('files', 'f');
$q->addJoin('file_folders', 'ff', 'ff.file_folder_id = file_folder');
$q->addJoin('projects', 'p', 'p.project_id = file_project');
$q->addJoin('tasks', 't', 't.task_id = file_task');
$q->leftJoin('project_departments', 'project_departments', 'p.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');
//TODO: apply permissions properly
$project = new CProject();
$deny1 = $project->getDeniedRecords($AppUI->user_id);
if (count($deny1) > 0) {
$q->addWhere('file_project NOT IN (' . implode(',', $deny1) . ')');
}
//TODO: apply permissions properly
$task = new CTask();
$deny2 = $task->getDeniedRecords($AppUI->user_id);
if (count($deny2) > 0) {
$q->addWhere('file_task NOT IN (' . implode(',', $deny2) . ')');
}
if ($project_id) {
$q->addWhere('file_project = ' . (int) $project_id);
}
if ($task_id) {
$q->addWhere('file_task = ' . (int) $task_id);
}
if ($company_id) {
$q->addWhere('project_company = ' . (int) $company_id);
}
$q->setLimit($xpg_pagesize, $xpg_min);
$q->addWhere('file_folder = ' . (int) $folder_id);
$q->addGroup('file_version_id DESC');
$qv = new w2p_Database_Query();
$qv->addTable('files');
$qv->addQuery('file_id, file_version, file_project, file_name, file_task,
file_description, u.user_username as file_owner, file_size, file_category,
task_name, file_version_id, file_checkout, file_co_reason, file_type,
file_date, cu.user_username as co_user, project_name,
project_color_identifier, 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 ');
$qv->addJoin('projects', 'p', 'p.project_id = file_project');
$qv->addJoin('users', 'u', 'u.user_id = file_owner');
$qv->addJoin('contacts', 'con', 'con.contact_id = u.user_contact');
$qv->addJoin('tasks', 't', 't.task_id = file_task');
$qv->addJoin('file_folders', 'ff', 'ff.file_folder_id = file_folder');
if ($project_id) {
$qv->addWhere('file_project = ' . (int) $project_id);
}
if ($task_id) {
$qv->addWhere('file_task = ' . (int) $task_id);
}
if ($company_id) {
$qv->addWhere('project_company = ' . (int) $company_id);
}
$qv->leftJoin('users', 'cu', 'cu.user_id = file_checkout');
$qv->leftJoin('contacts', 'co', 'co.contact_id = cu.user_contact');
$qv->addWhere('file_folder = ' . (int) $folder_id);
$files = array();
$file_versions = array();
$files = $q->loadList();
$file_versions = $qv->loadHashList('file_id');
$q->clear();
$qv->clear();
if ($files === array()) {
return 0;
}
$s = '
<table 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="nowrap" width="5%"></th>
<th nowrap="nowrap" width="1"></th>
</tr>';
$fp = -1;
$file_date = new w2p_Utilities_Date();
$id = 0;
foreach ($files as $row) {
$latest_file = $file_versions[$row['latest_id']];
$file_date = new w2p_Utilities_Date($latest_file['file_date']);
if ($fp != $latest_file['file_project']) {
if (!$latest_file['file_project']) {
$latest_file['project_name'] = $AppUI->_('Not attached to a project');
$latest_file['project_color_identifier'] = 'f4efe3';
}
if ($showProject) {
$style = 'background-color:#' . $latest_file['project_color_identifier'] . ';color:' . bestColor($latest_file['project_color_identifier']);
$s .= '<tr>';
$s .= '<td colspan="20" style="border: outset 2px #eeeeee;' . $style . '">';
if ($latest_file['file_project'] > 0) {
$href = './index.php?m=projects&a=view&project_id=' . $latest_file['file_project'];
} else {
$href = './index.php?m=projects';
}
$s .= '<a href="' . $href . '">';
$s .= '<span style="' . $style . '">' . $latest_file['project_name'] . '</span></a>';
$s .= '</td></tr>';
}
}
$fp = $latest_file['file_project'];
$s .= '<tr>
<td nowrap="8%">
<form name="frm_remove_file_' . $latest_file['file_id'] . '" action="?m=files" method="post" accept-charset="utf-8">
<input type="hidden" name="dosql" value="do_file_aed" />
<input type="hidden" name="del" value="1" />
<input type="hidden" name="file_id" value="' . $latest_file['file_id'] . '" />
<input type="hidden" name="redirect" value="' . $current_uri . '" />
</form>
<form name="frm_duplicate_file_' . $latest_file['file_id'] . '" action="?m=files" method="post" accept-charset="utf-8">
<input type="hidden" name="dosql" value="do_file_aed" />
<input type="hidden" name="duplicate" value="1" />
<input type="hidden" name="file_id" value="' . $latest_file['file_id'] . '" />
<input type="hidden" name="redirect" value="' . $current_uri . '" />
</form>
';
$junkFile = new CFile();
// TODO: This is just to get getIcon included..
$file_icon = getIcon($row['file_type']);
$s .= '<a href="./fileviewer.php?file_id=' . $latest_file['file_id'] . '"><img border="0" width="16" heigth="16" src="' . w2PfindImage($file_icon, 'files') . '" alt="" /> ' . $latest_file['file_name'] . '</a></td>';
$s .= '<td width="20%">' . w2p_textarea($latest_file['file_description']) . '</td><td width="5%" nowrap="nowrap" align="right">';
$hidden_table = '';
$s .= $row['file_lastversion'];
if ($row['file_versions'] > 1) {
$s .= ' <a href="javascript: void(0);" onClick="expand(\'versions_' . $latest_file['file_id'] . '\'); ">(' . $row['file_versions'] . ')</a>';
$hidden_table = '<tr><td colspan="20">
<table style="display: none" id="versions_' . $latest_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>' . $AppUI->_('Folder') . '</th>
<th>' . $AppUI->_('Task Name') . '</th>
<th>' . $AppUI->_('Owner') . '</th>
<th>' . $AppUI->_('Size') . '</th>
<th>' . $AppUI->_('Type') . '</a></th>
<th>' . $AppUI->_('Date') . '</th>
</tr>';
foreach ($file_versions as $file) {
if ($file['file_version_id'] == $latest_file['file_version_id']) {
$file_icon = getIcon($file['file_type']);
$hdate = new w2p_Utilities_Date($file['file_date']);
$hidden_table .= '<tr><td nowrap="8%"><a href="./fileviewer.php?file_id=' . $file['file_id'] . '" title="' . $file['file_description'] . '">' . '<img border="0" width="16" heigth="16" src="' . w2PfindImage($file_icon, 'files') . '" alt="" /> ' . $file['file_name'] . '
</a></td>
<td width="20%">' . $file['file_description'] . '</td>
<td width="5%" nowrap="nowrap" align="right">' . $file['file_version'] . '</td>
<td nowrap="nowrap" align="left">' . $file_types[$file['file_category']] . '</td>
<td nowrap="nowrap" align="left">' . ($file['file_folder_name'] != '' ? '<a href="' . W2P_BASE_URL . '/index.php?m=files&tab=' . (count($file_types) + 1) . '&folder=' . $file['file_folder_id'] . '">' . w2PshowImage('folder5_small.png', '16', '16', 'folder icon', 'show only this folder', 'files') . $file['file_folder_name'] . '</a>' : 'Root') . '</td>
<td nowrap="nowrap" align="left"><a href="./index.php?m=tasks&a=view&task_id=' . $file['file_task'] . '">' . $file['task_name'] . '</a></td>
<td nowrap="nowrap">' . $file['contact_first_name'] . ' ' . $file['contact_last_name'] . '</td>
<td width="5%" nowrap="nowrap" align="right">' . file_size(intval($file['file_size'])) . '</td>
<td nowrap="nowrap">' . $file['file_type'] . '</td>
<td width="5%" nowrap="nowrap" align="center">' . $AppUI->formatTZAwareTime($file['file_date'], $df . ' ' . $tf) . '</td>';
if ($canEdit && $w2Pconfig['files_show_versions_edit']) {
$hidden_table .= '<a href="./index.php?m=files&a=addedit&file_id=' . $file['file_id'] . '">' . w2PshowImage('kedit.png', '16', '16', 'edit file', 'edit file', 'files') . "</a>";
}
$hidden_table .= '</td><tr>';
}
}
$hidden_table .= '</table>';
}
$s .= '</td>
<td width="10%" nowrap="nowrap" align="left">' . $file_types[$file['file_category']] . '</td>
<td nowrap="nowrap" align="left"><a href="./index.php?m=tasks&a=view&task_id=' . $latest_file['file_task'] . '">' . $latest_file['task_name'] . '</a></td>
<td nowrap="nowrap">' . $latest_file['contact_first_name'] . ' ' . $latest_file['contact_last_name'] . '</td>
<td width="5%" nowrap="nowrap" align="right">' . intval($latest_file['file_size'] / 1024) . ' kb</td>
<td nowrap="nowrap">' . $latest_file['file_type'] . '</td>
<td nowrap="nowrap" align="center">' . $AppUI->formatTZAwareTime($latest_file['file_date'], $df . ' ' . $tf) . '</td>
<td width="10%">' . $latest_file['file_co_reason'] . '</td>
<td nowrap="nowrap">';
if (empty($row['file_checkout'])) {
$s .= '<a href="?m=files&a=co&file_id=' . $latest_file['file_id'] . '">' . w2PshowImage('up.png', '16', '16', 'checkout', 'checkout file', 'files') . '</a>';
} elseif ($row['file_checkout'] == $AppUI->user_id) {
$s .= '<a href="?m=files&a=addedit&ci=1&file_id=' . $latest_file['file_id'] . '">' . w2PshowImage('down.png', '16', '16', 'checkin', 'checkin file', 'files') . '</a>';
} else {
if ($latest_file['file_checkout'] == 'final') {
$s .= 'final';
} else {
$s .= $latest_file['co_contact_first_name'] . ' ' . $latest_file['co_contact_last_name'] . '<br>(' . $latest_file['co_user'] . ')';
}
}
$s .= '</td><td nowrap="nowrap" width="50">';
if ($canEdit && (empty($latest_file['file_checkout']) || $latest_file['file_checkout'] == 'final' && ($canEdit || $latest_file['project_owner'] == $AppUI->user_id))) {
$s .= '<a style="float: left;" href="./index.php?m=files&a=addedit&file_id=' . $latest_file['file_id'] . '">' . w2PshowImage('kedit.png', '16', '16', 'edit file', 'edit file', 'files') . '</a>';
$s .= '<a style="float: left;" href="javascript: void(0);" onclick="document.frm_duplicate_file_' . $latest_file['file_id'] . '.submit()">' . w2PshowImage('duplicate.png', '16', '16', 'duplicate file', 'duplicate file', 'files') . '</a>';
$s .= '<a style="float: left;" href="javascript: void(0);" onclick="if (confirm(\'Are you sure you want to delete this file?\')) {document.frm_remove_file_' . $latest_file['file_id'] . '.submit()}">' . w2PshowImage('remove.png', '16', '16', 'delete file', 'delete file', 'files') . '</a>';
}
$s .= '</td>';
$s .= '<td nowrap="nowrap" align="center" width="1">';
if ($canEdit && (empty($latest_file['file_checkout']) || $latest_file['file_checkout'] == 'final' && ($canEdit || $latest_file['project_owner'] == $AppUI->user_id))) {
$bulk_op = 'onchange="(this.checked) ? addBulkComponent(' . $latest_file['file_id'] . ') : removeBulkComponent(' . $latest_file['file_id'] . ')"';
$s .= '<input type="checkbox" ' . $bulk_op . ' name="chk_sel_file_' . $latest_file['file_id'] . '" />';
}
$s .= '</td></tr>';
$s .= $hidden_table;
$hidden_table = '';
}
return $s;
}
