public function getProjectTaskLinksByCategory(CAppUI $AppUI, $project_id = 0, $task_id = 0, $category_id = 0, $search = '')
{
// load the following classes to retrieved denied records
$project = new CProject();
$task = new CTask();
// SETUP FOR LINK LIST
$q = new w2p_Database_Query();
$q->addQuery('DISTINCT links.*');
$q->addQuery('contact_first_name, contact_last_name');
$q->addQuery('project_name, project_color_identifier, project_status');
$q->addQuery('task_name, task_id');
$q->addTable('links');
$q->leftJoin('users', 'u', 'user_id = link_owner');
$q->leftJoin('contacts', 'c', 'user_contact = contact_id');
if ($search != '') {
$q->addWhere('(link_name LIKE \'%' . $search . '%\' OR link_description LIKE \'%' . $search . '%\')');
}
if ($project_id > 0) {
// Project
$q->addWhere('link_project = ' . (int) $project_id);
}
if ($task_id > 0) {
// Task
$q->addWhere('link_task = ' . (int) $task_id);
}
if ($category_id >= 0) {
// Category
$q->addWhere('link_category = ' . $category_id);
}
// Permissions
$project->setAllowedSQL($AppUI->user_id, $q, 'link_project');
$task->setAllowedSQL($AppUI->user_id, $q, 'link_task and task_project = link_project');
$q->addOrder('project_name, link_name');
return $q->loadList();
}
$proj = new CProject();
$tobj = new CTask();
$allowedProjects = $proj->getAllowedSQL($AppUI->user_id, 'pr.project_id');
$allowedTasks = $tobj->getAllowedSQL($AppUI->user_id, 'ta.task_id');
// query my sub-tasks (ignoring task parents)
$q = new w2p_Database_Query();
$q->addQuery('ta.*');
$q->addQuery('project_name, pr.project_id, project_color_identifier');
$q->addQuery('tp.task_pinned');
$q->addQuery('ut.user_task_priority');
$dateDiffString = $q->dbfnDateDiff('ta.task_end_date', $q->dbfnNow()) . ' AS task_due_in';
$q->addQuery($dateDiffString);
$q->addTable('projects', 'pr');
$q->addTable('tasks', 'ta');
$q->addTable('user_tasks', 'ut');
$q->leftJoin('user_task_pin', 'tp', 'tp.task_id = ta.task_id and tp.user_id = ' . (int) $user_id);
$q->leftJoin('project_departments', 'project_departments', 'pr.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('ut.task_id = ta.task_id');
$q->addWhere('ut.user_id = ' . (int) $user_id);
$q->addWhere('( ta.task_percent_complete < 100 or ta.task_percent_complete is null)');
$q->addWhere('ta.task_status = 0');
$q->addWhere('pr.project_id = ta.task_project');
if (!$showArcProjs) {
$q->addWhere('project_active = 1');
if (($template_status = w2PgetConfig('template_projects_status_id')) != '') {
$q->addWhere('project_status <> ' . (int) $template_status);
}
}
if (!$showLowTasks) {
$q->addWhere('task_priority >= 0');
/**
* Determines whether the currently logged in user can delete this task log.
*
* @global AppUI $AppUI global user permissions
*
* @param string by ref $msg error msg to be populated on failure
* @param int optional $oid key to check
* @param array $joins optional list of tables to join on
*
* @return bool
*/
public function canDelete(&$msg, $oid = null, $joins = null)
{
global $AppUI;
$q = new w2p_Database_Query();
// First things first. Are we allowed to delete?
$acl =& $AppUI->acl();
if (!canDelete('task_log')) {
$msg = $AppUI->_('noDeletePermission');
return false;
}
$k = $this->_tbl_key;
if ($oid) {
$this->{$k} = (int) $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;
}
if ($tab < 0) {
$catsql = false;
} else {
$catsql = 'file_category = ' . $tab;
}
}
// Fetch permissions once for all queries
$allowedProjects = $project->getAllowedSQL($AppUI->user_id, 'file_project');
$allowedTasks = $task->getAllowedSQL($AppUI->user_id, 'file_task');
// SQL text for count the total recs from the selected option
$q = new w2p_Database_Query();
$q->addQuery('count(file_id)');
$q->addTable('files', 'f');
$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');
if (count($allowedProjects)) {
$q->addWhere('( ( ' . implode(' AND ', $allowedProjects) . ') OR file_project = 0 )');
}
if (count($allowedTasks)) {
$q->addWhere('( ( ' . implode(' AND ', $allowedTasks) . ') OR file_task = 0 )');
}
if ($catsql) {
$q->addWhere($catsql);
}
if ($company_id) {
$q->addWhere('project_company = ' . (int) $company_id);
}
if ($project_id) {
$q->addWhere('file_project = ' . (int) $project_id);
$task_sort_order1 = (int) w2PgetParam($_GET, 'task_sort_order1', 0);
$task_sort_order2 = (int) w2PgetParam($_GET, 'task_sort_order2', 0);
if (isset($_POST['show_task_options'])) {
$AppUI->setState('TaskListShowIncomplete', w2PgetParam($_POST, 'show_incomplete', 0));
}
$showIncomplete = $AppUI->getState('TaskListShowIncomplete', 0);
$project = new CProject();
$allowedProjects = $project->getAllowedSQL($AppUI->user_id, 'p.project_id');
$where_list = count($allowedProjects) ? implode(' AND ', $allowedProjects) : '';
$working_hours = $w2Pconfig['daily_working_hours'] ? $w2Pconfig['daily_working_hours'] : 8;
$q = new w2p_Database_Query();
$q->addTable('projects', 'p');
$q->addQuery('company_name, p.project_id, project_color_identifier, project_name, project_percent_complete');
$q->addJoin('companies', 'com', 'company_id = project_company', 'inner');
$q->addJoin('tasks', 't1', 'p.project_id = t1.task_project', 'inner');
$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');
$q->addWhere($where_list . ($where_list ? ' AND ' : '') . 't1.task_id = t1.task_parent');
$q->addGroup('p.project_id');
if (!$project_id && !$task_id) {
$q->addOrder('project_name');
}
if ($project_id > 0) {
$q->addWhere('p.project_id = ' . $project_id);
}
$q2 = new w2p_Database_Query();
$q2->addTable('projects');
$q2->addQuery('project_id, COUNT(t1.task_id) AS total_tasks');
$q2->addJoin('tasks', 't1', 'projects.project_id = t1.task_project', 'inner');
if ($where_list) {
$q2->addWhere($where_list);
public function getTaskLogs($taskId, $problem = false)
{
$q = new w2p_Database_Query();
$q->addTable('task_log');
$q->addQuery('task_log.*, user_username, billingcode_name as task_log_costcode');
$q->addQuery('CONCAT(contact_first_name, \' \', contact_last_name) AS real_name');
$q->addWhere('task_log_task = ' . (int) $taskId . ($problem ? ' AND task_log_problem > 0' : ''));
$q->addOrder('task_log_date');
$q->addOrder('task_log_created');
$q->leftJoin('billingcode', '', 'task_log.task_log_costcode = billingcode_id');
$q->addJoin('users', '', 'task_log_creator = user_id', 'inner');
$q->addJoin('contacts', 'ct', 'contact_id = user_contact', 'inner');
return $q->loadList();
}
public static function getOwners()
{
$q = new w2p_Database_Query();
$q->addTable('projects', 'p');
$q->addQuery('user_id, contact_display_name');
$q->leftJoin('users', 'u', 'u.user_id = p.project_owner');
$q->leftJoin('contacts', 'c', 'c.contact_id = u.user_contact');
$q->addOrder('contact_first_name, contact_last_name');
$q->addWhere('user_id > 0');
$q->addWhere('p.project_owner IS NOT NULL');
return $q->loadHashList();
}
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;
}
$orderdir = $AppUI->getState('ForumVwOrderDir') ? $AppUI->getState('ForumVwOrderDir') == 'asc' ? 'desc' : 'asc' : 'desc';
$AppUI->setState('ForumVwOrderBy', w2PgetParam($_GET, 'orderby', null));
$AppUI->setState('ForumVwOrderDir', $orderdir);
}
$orderby = $AppUI->getState('ForumVwOrderBy') ? $AppUI->getState('ForumVwOrderBy') : 'latest_reply';
$orderdir = $AppUI->getState('ForumVwOrderDir') ? $AppUI->getState('ForumVwOrderDir') : 'desc';
//Pull All Messages
$q = new w2p_Database_Query();
$q->addTable('forum_messages', 'fm1');
$q->addQuery('fm1.*');
$q->addQuery('COUNT(distinct fm2.message_id) AS replies');
$q->addQuery('MAX(fm2.message_date) AS latest_reply');
$q->addQuery('user_username, contact_first_name, contact_last_name, watch_user');
$q->addQuery('count(distinct v1.visit_message) as reply_visits');
$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);
/**
* @param $where_list
* @param $project_id
* @param $task_id
* @return Array
*/
function __extract_from_tasks4($where_list, $project_id, $task_id)
{
$q = new w2p_Database_Query();
$q->addTable('projects', 'p');
$q->addQuery('company_name, p.project_id, project_color_identifier, project_name, project_percent_complete, project_task_count');
$q->addJoin('companies', 'com', 'company_id = project_company', 'inner');
$q->addJoin('tasks', 't1', 'p.project_id = t1.task_project', 'inner');
$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');
$q->addWhere($where_list . ($where_list ? ' AND ' : '') . 't1.task_id = t1.task_parent');
$q->addGroup('p.project_id');
if (!$project_id && !$task_id) {
$q->addOrder('project_name');
}
if ($project_id > 0) {
$q->addWhere('p.project_id = ' . $project_id);
}
$projects = $q->loadList(-1, 'project_id');
return $projects;
}
public function getCalendarEvents($userId, $days = 30)
{
/*
* This list of fields - id, name, description, startDate, endDate,
* updatedDate - are named specifically for the iCal creation.
* If you change them, it's probably going to break. So don't do that.
*/
$q = new w2p_Database_Query();
$q->addQuery('e.event_id as id');
$q->addQuery('event_title as name');
$q->addQuery('event_description as description');
$q->addQuery('event_start_date as startDate');
$q->addQuery('event_end_date as endDate');
$q->addQuery("'" . $q->dbfnNowWithTZ() . "' as updatedDate");
$q->addQuery('CONCAT(\'' . W2P_BASE_URL . '/index.php?m=calendar&a=view&event_id=' . '\', e.event_id) as url');
$q->addQuery('projects.project_id, projects.project_name');
$q->addTable('events', 'e');
$q->leftJoin('projects', 'projects', 'e.event_project = projects.project_id');
$q->addWhere('(event_start_date > ' . $q->dbfnNow() . ' OR event_end_date > ' . $q->dbfnNow() . ')');
$q->addWhere('(event_start_date < ' . $q->dbfnDateAdd($q->dbfnNow(), $days, 'DAY') . ' OR event_end_date < ' . $q->dbfnDateAdd($q->dbfnNow(), $days, 'DAY') . ')');
$q->innerJoin('user_events', 'ue', 'ue.event_id = e.event_id');
$q->addWhere('ue.user_id = ' . $userId);
$q->addOrder('event_start_date');
return $q->loadList();
}
public function getFilteredDepartmentList(CAppUI $AppUI = null, $deptType = -1, $searchString = '', $ownerId = 0, $orderby = 'dept_name', $orderdir = 'ASC')
{
global $AppUI;
$orderby = in_array($orderby, array('dept_name', 'dept_type', 'countp', 'inactive')) ? $orderby : 'dept_name';
$q = new w2p_Database_Query();
$q->addTable('departments');
$q->addQuery('departments.*, COUNT(ct.contact_department) dept_users, count(distinct p.project_id) as countp, count(distinct p2.project_id) as inactive, con.contact_first_name, con.contact_last_name');
$q->addJoin('companies', 'c', 'c.company_id = departments.dept_company');
$q->addJoin('project_departments', 'pd', 'pd.department_id = dept_id');
$q->addJoin('projects', 'p', 'pd.project_id = p.project_id AND p.project_active = 1');
$q->leftJoin('users', 'u', 'dept_owner = u.user_id');
$q->leftJoin('contacts', 'con', 'u.user_contact = con.contact_id');
$q->addJoin('projects', 'p2', 'pd.project_id = p2.project_id AND p2.project_active = 0');
$q->addJoin('contacts', 'ct', 'ct.contact_department = dept_id');
$q->addGroup('dept_id');
$q->addOrder('dept_parent, dept_name');
$oCpy = new CCompany();
$where = $oCpy->getAllowedSQL($AppUI->user_id, 'c.company_id');
$q->addWhere($where);
if ($deptType > -1) {
$q->addWhere('dept_type = ' . (int) $deptType);
}
if ($searchString != '') {
$q->addWhere("dept_name LIKE '%{$searchString}%'");
}
if ($ownerId > 0) {
$q->addWhere('dept_owner = ' . $ownerId);
}
$q->addGroup('dept_id');
$q->addOrder($orderby . ' ' . $orderdir);
return $q->loadList();
}
public function sendWatchMail($debug = false)
{
global $AppUI, $debug, $w2Pconfig;
$subj_prefix = $AppUI->_('forumEmailSubj', UI_OUTPUT_RAW);
$body_msg = $AppUI->_('forumEmailBody', UI_OUTPUT_RAW);
// Get the message from details.
$q = new w2p_Database_Query();
$q->addTable('users', 'u');
$q->addQuery('contact_first_name, contact_last_name, contact_email');
$q->addJoin('contacts', 'con', 'contact_id = user_contact', 'inner');
$q->addWhere('user_id = ' . (int) $this->message_author);
$res = $q->exec();
if ($row = $q->fetchRow()) {
$message_from = $row['contact_first_name'] . ' ' . $row['contact_last_name'] . '<' . $row['contact_email'] . '>';
} else {
$message_from = 'Unknown user';
}
// Get the forum name;
$q->clear();
$q->addTable('forums');
$q->addQuery('forum_name');
$q->addWhere('forum_id = \'' . $this->message_forum . '\'');
$res = $q->exec();
if ($row = $q->fetchRow()) {
$forum_name = $row['forum_name'];
} else {
$forum_name = 'Unknown';
}
// SQL-Query to check if the message should be delivered to all users (forced)
// In positive case there will be a (0,0,0) row in the forum_watch table
$q->clear();
$q->addTable('forum_watch');
$q->addQuery('*');
$q->addWhere('watch_user = 0 AND watch_forum = 0 AND watch_topic = 0');
$resAll = $q->exec();
$AllCount = db_num_rows($resAll);
$q->clear();
$q->addTable('users');
$q->addQuery('DISTINCT user_id, contact_first_name, contact_last_name, contact_email');
$q->leftJoin('contacts', 'con', 'con.contact_id = user_contact');
if ($AllCount < 1) {
//message is only delivered to users that checked the forum watch
$q->addTable('forum_watch');
$q->addWhere('user_id = watch_user AND (watch_forum = ' . (int) $this->message_forum . ' OR watch_topic = ' . (int) $this->message_parent . ')');
}
if (!($res = $q->exec(ADODB_FETCH_ASSOC))) {
$q->clear();
return;
}
if (db_num_rows($res) < 1) {
return;
}
$mail = new w2p_Utilities_Mail();
$mail->Subject($subj_prefix . ' ' . $this->message_title, isset($GLOBALS['locale_char_set']) ? $GLOBALS['locale_char_set'] : '');
$body = $body_msg;
$body .= "\n\n" . $AppUI->_('Forum', UI_OUTPUT_RAW) . ': ' . $forum_name;
$body .= "\n" . $AppUI->_('Subject', UI_OUTPUT_RAW) . ': ' . $this->message_title;
$body .= "\n" . $AppUI->_('Message From', UI_OUTPUT_RAW) . ': ' . $message_from;
$body .= "\n\n" . W2P_BASE_URL . '/index.php?m=forums&a=viewer&forum_id=' . $this->message_forum;
$body .= "\n\n" . $this->message_body;
$mail->Body($body, isset($GLOBALS['locale_char_set']) ? $GLOBALS['locale_char_set'] : '');
while ($row = $q->fetchRow()) {
if ($mail->ValidEmail($row['contact_email'])) {
$mail->To($row['contact_email'], true);
$mail->Send();
}
}
$q->clear();
return;
}
public static function getContacts(CAppUI $AppUI, $companyId)
{
$results = array();
$perms = $AppUI->acl();
if ($AppUI->isActiveModule('contacts') && canView('contacts') && (int) $companyId > 0) {
$q = new w2p_Database_Query();
$q->addQuery('a.*');
$q->addQuery('dept_name');
$q->addTable('contacts', 'a');
$q->leftJoin('companies', 'b', 'a.contact_company = b.company_id');
$q->leftJoin('departments', '', 'contact_department = dept_id');
$q->addWhere('contact_company = ' . (int) $companyId);
$q->addWhere('
(contact_private=0
OR (contact_private=1 AND contact_owner=' . $AppUI->user_id . ')
OR contact_owner IS NULL OR contact_owner = 0
)');
$department = new CDepartment();
$department->setAllowedSQL($AppUI->user_id, $q);
$q->addOrder('contact_first_name');
$q->addOrder('contact_last_name');
$results = $q->loadHashList('contact_id');
}
return $results;
}
** there is a different filter approach in todo
** so we have to tweak a little bit,
** also we do not have a special project available
*/
$caller = w2PgetParam($_REQUEST, 'caller', null);
if ($caller == 'todo') {
$user_id = w2PgetParam($_REQUEST, 'user_id', $AppUI->user_id);
$projects[$project_id]['project_name'] = $AppUI->_('Todo for') . ' ' . CContact::getContactByUserid($user_id);
$projects[$project_id]['project_color_identifier'] = 'ff6000';
$q = new w2p_Database_Query();
$q->addQuery('t.*');
$q->addQuery('project_name, project_id, project_color_identifier');
$q->addQuery('tp.task_pinned');
$q->addTable('tasks', 't');
$q->innerJoin('projects', 'pr', 'pr.project_id = t.task_project');
$q->leftJoin('user_tasks', 'ut', 'ut.task_id = t.task_id AND ut.user_id = ' . (int) $user_id);
$q->leftJoin('user_task_pin', 'tp', 'tp.task_id = t.task_id and tp.user_id = ' . (int) $user_id);
$q->addWhere('(t.task_percent_complete < 100 OR t.task_percent_complete IS NULL)');
$q->addWhere('t.task_status = 0');
if (!$showArcProjs) {
$q->addWhere('pr.project_active = 1');
if (($template_status = w2PgetConfig('template_projects_status_id')) != '') {
$q->addWhere('pr.project_status <> ' . (int) $template_status);
}
}
if (!$showLowTasks) {
$q->addWhere('task_priority >= 0');
}
if (!$showHoldProjs) {
$q->addWhere('project_active = 1');
}
/**
* Login function
*
* A number of things are done in this method to prevent illegal entry:
* <ul>
* <li>The username and password are trimmed and escaped to prevent malicious
* SQL being executed
* </ul>
* The schema previously used the MySQL PASSWORD function for encryption. This
* Method has been deprecated in favour of PHP's MD5() function for database independance.
* The check_legacy_password option is no longer valid
*
* Upon a successful username and password match, several fields from the user
* table are loaded in this object for convenient reference. The style, locales
* and preferences are also loaded at this time.
*
* @param string The user login name
* @param string The user password
* @return boolean True if successful, false if not
*/
public function login($username, $password)
{
$auth_method = w2PgetConfig('auth_method', 'sql');
if ($_POST['login'] != 'login' && $_POST['login'] != $this->_('login', UI_OUTPUT_RAW) && $_REQUEST['login'] != $auth_method) {
die('You have chosen to log in using an unsupported or disabled login method');
}
$auth =& getauth($auth_method);
$username = preg_replace("/[^A-Za-z0-9._@-]/", "", $username);
$username = trim($username);
$password = trim($password);
if (!$auth->authenticate($username, $password)) {
return false;
}
$user_id = $auth->userId($username);
$username = $auth->username;
// Some authentication schemes may collect username in various ways.
// Now that the password has been checked, see if they are allowed to
// access the system
if (!isset($GLOBALS['acl'])) {
$GLOBALS['acl'] = new w2p_Extensions_Permissions();
}
if (!$GLOBALS['acl']->checkLogin($user_id)) {
dprint(__FILE__, __LINE__, 1, 'Permission check failed');
return false;
}
$q = new w2p_Database_Query();
$q->addTable('users');
$q->addQuery('user_id, contact_first_name as user_first_name, ' . 'contact_last_name as user_last_name, contact_display_name as user_display_name, ' . 'contact_company as user_company, contact_department as user_department, user_type');
$q->addJoin('contacts', 'con', 'con.contact_id = user_contact', 'inner');
/* Begin Hack */
/*
* This is a particularly annoying hack but I don't know of a better
* way to resolve #457. In v2.0, there was a refactoring to allow for
* muliple contact methods which resulted in the contact_email being
* removed from the contacts table. If the user is upgrading from
* v1.x and they try to log in before applying the database, crash.
* Info: http://bugs.web2project.net/view.php?id=457
* This hack was deprecated in dbVersion 26 for v2.2 in December 2010.
*/
$qTest = new w2p_Database_Query();
$qTest->addTable('w2pversion');
$qTest->addQuery('max(db_version)');
$dbVersion = $qTest->loadResult();
if ($dbVersion >= 21 && $dbVersion < 26) {
$q->leftJoin('contacts_methods', 'cm', 'cm.contact_id = con.contact_id');
$q->addWhere("cm.method_name = 'email_primary'");
$q->addQuery('cm.method_value AS user_email');
}
/* End Hack */
$q->addWhere('user_id = ' . (int) $user_id . ' AND user_username = \'' . $username . '\'');
$q->loadObject($this);
if (!$this) {
dprint(__FILE__, __LINE__, 1, 'Failed to load user information');
return false;
}
// load the user preferences
$this->loadPrefs($this->user_id);
$this->setUserLocale();
$this->setStyle();
return true;
}
public static function getCustomFieldByModule($AppUI, $module, $objectId)
{
$perms = $AppUI->acl();
$canRead = canView($module, $objectId);
if ($canRead) {
$q = new w2p_Database_Query();
$q->addTable('custom_fields_struct', 'cfs');
$q->addQuery('cfv.value_charvalue, cfl.list_value');
$q->leftJoin('custom_fields_values', 'cfv', 'cfv.value_field_id = cfs.field_id');
$q->leftJoin('custom_fields_lists', 'cfl', 'cfl.list_option_id = cfv.value_intvalue');
$q->addWhere("cfs.field_module = '{$module}'");
$q->addWhere('cfv.value_object_id =' . $objectId);
return $q->loadList();
}
}
/**
* w2Pacl::recalcPermissions()
*
* @param mixed $user_id
* @param mixed $user_aro_id
* @param mixed $role_id
* @param mixed $module
* @return
*/
public function recalcPermissions($user_id = null, $user_aro_id = null, $role_id = null, $module = '', $method = 1)
{
$q = new w2p_Database_Query();
$q->addTable($this->_db_acl_prefix . 'aco_sections', 'a');
$q->addQuery('a.value AS a_value, a.name AS a_name,
b.value AS b_value, b.name AS b_name,
c.value AS c_value, c.name AS c_name,
d.value AS d_value, d.name AS d_name,
e.value AS e_value, e.name AS e_name,
f.value AS f_value, f.name AS f_name
');
$q->leftJoin($this->_db_acl_prefix . 'aco', 'b', 'a.value=b.section_value,' . w2PgetConfig('dbprefix') . $this->_db_acl_prefix . 'aro_sections c');
$q->leftJoin($this->_db_acl_prefix . 'aro', 'd', 'c.value=d.section_value,' . w2PgetConfig('dbprefix') . $this->_db_acl_prefix . 'axo_sections e');
$q->leftJoin($this->_db_acl_prefix . 'axo', 'f', 'e.value=f.section_value');
if ($user_id) {
$q->addWhere('d.value = \'' . $user_id . '\'');
} elseif ($user_aro_id) {
$q->addWhere('d.id = \'' . $user_aro_id . '\'');
} else {
//only recalculate permissions for users able to login (that have at least one role)
$active_users = $this->getUsersWithRole();
$q->addWhere('d.id IN (' . implode(',', array_keys($active_users)) . ')');
}
if ($role_id) {
$role_users = $this->getRoleUsers($role_id);
if ($role_users) {
$q->addWhere('d.value IN (' . implode(',', array_keys($role_users)) . ')');
} else {
//If there are no users affected then make it so nothing is recalculated
$q->addWhere('d.value = 0');
}
}
if ($module) {
$q->addWhere('f.value = \'' . $module . '\'');
}
//Make sure things without axos are not ported, this would make addon modules to carry wrong soft denials affecting visible addon modules
$q->addWhere('f.value IS NOT NULL');
$rows = $q->loadList();
$q->clear();
$total_rows = count($rows);
$acls = array();
while (list(, $row) = @each($rows)) {
$aco_section_value = $row['a_value'];
$aco_value = $row['b_value'];
$aro_section_value = $row['c_value'];
$aro_value = $row['d_value'];
$aro_name = $row['d_name'];
$axo_section_value = $row['e_value'];
$axo_value = $row['f_value'];
$acl_result = $this->acl_query($aco_section_value, $aco_value, $aro_section_value, $aro_value, $axo_section_value, $axo_value);
$acl_id =& $acl_result['acl_id'];
$access =& $acl_result['allow'];
$acls[] = array('aco_section_value' => $aco_section_value, 'aco_value' => $aco_value, 'aro_section_value' => $aro_section_value, 'aro_value' => $aro_value, 'aro_name' => $aro_name, 'axo_section_value' => $axo_section_value, 'axo_value' => $axo_value, 'acl_id' => $acl_id, 'access' => $access);
}
$user_permissions = array();
foreach ($acls as $key => $acl) {
$user_permissions[$acl['aro_value']][$key]['user_id'] = $acl['aro_value'];
$user_permissions[$acl['aro_value']][$key]['user_name'] = $acl['aro_name'];
$user_permissions[$acl['aro_value']][$key]['module'] = $acl['axo_section_value'] == 'app' || $acl['axo_section_value'] == 'sys' ? $acl['axo_value'] : $acl['axo_section_value'];
$user_permissions[$acl['aro_value']][$key]['item_id'] = $acl['axo_section_value'] == 'app' || $acl['axo_section_value'] == 'sys' ? 0 : $acl['axo_value'];
$user_permissions[$acl['aro_value']][$key]['action'] = $acl['aco_value'];
$user_permissions[$acl['aro_value']][$key]['access'] = $acl['access'] ? 1 : 0;
$user_permissions[$acl['aro_value']][$key]['acl_id'] = $acl['acl_id'];
}
// Now that we have the users permissions lets delete the existing ones and insert the new ones
$q = new w2p_Database_Query();
$q->setDelete($this->_db_acl_prefix . 'permissions');
if ($user_id) {
$q->addWhere('user_id = \'' . $user_id . '\'');
}
if ($user_aro_id) {
$qui = new w2p_Database_Query();
$qui->addTable($this->_db_acl_prefix . 'aro');
$qui->addQuery('value');
$qui->addWhere('id = \'' . $user_aro_id . '\'');
$id = $qui->loadResult();
if ($id) {
$q->addWhere('user_id = \'' . $id . '\'');
}
}
if ($role_id) {
$role_users = $this->getRoleUsers($role_id);
if ($role_users) {
$q->addWhere('user_id IN (' . implode(',', array_keys($role_users)) . ')');
} else {
//If there are no users affected then don not delete anything
$q->addWhere('user_id = 0');
}
}
if ($module) {
$q->addWhere('module = \'' . $module . '\'');
}
$q->exec();
$q->clear();
$q = new w2p_Database_Query();
foreach ($user_permissions as $user => $permissions) {
foreach ($permissions as $permission) {
//Only show permissions with acl_id and item_id when item permissions are to show
//Don't show login ACOs
if (!($permission['item_id'] && !$permission['acl_id']) && $permission['action'] != 'login') {
$q->addTable($this->_db_acl_prefix . 'permissions');
$q->addInsert('user_id', $permission['user_id']);
$q->addInsert('user_name', $permission['user_name']);
$q->addInsert('module', $permission['module']);
$q->addInsert('item_id', $permission['item_id'] ? $permission['item_id'] : 0);
$q->addInsert('action', $permission['action']);
$q->addInsert('access', $permission['access']);
$q->addInsert('acl_id', $permission['acl_id'] ? $permission['acl_id'] : 0);
$q->exec();
$q->clear();
}
}
}
return true;
}
$isNewUser = !w2PgetParam($_REQUEST, 'user_id', 0);
if ($isNewUser) {
// check if a user with the param Username already exists
if (is_array($contactListByUsername)) {
$AppUI->setMsg('This username is not available, please try another.', UI_MSG_ERROR, true);
$AppUI->redirect();
} else {
$contact->contact_owner = $AppUI->user_id;
}
}
$result = $contact->store($AppUI);
if ($result) {
$user->user_contact = $contact->contact_id;
if ($msg = $user->store()) {
$AppUI->setMsg($msg, UI_MSG_ERROR);
} else {
if ($isNewUser) {
notifyNewExternalUser($contact->contact_email, $contact->contact_first_name, $user->user_username, $_POST['user_password']);
}
notifyHR(w2PgetConfig('admin_email', '*****@*****.**'), 'w2P System Human Resources', $contact->contact_email, $contact->contact_first_name, $user->user_username, $_POST['user_password'], $user->user_id);
$q = new w2p_Database_Query();
$q->addTable('users', 'u');
$q->addQuery('contact_email');
$q->leftJoin('contacts', 'c', 'c.contact_id = u.user_contact');
$q->addWhere('u.user_username = \'admin\'');
$admin_user = $q->loadList();
}
} else {
$AppUI->setMsg($msg, UI_MSG_ERROR);
}
echo "<script language='javascript'>\n\t alert('The User Administrator has been notified to grant you access to the system and an email message was sent to you with your login info. Thank you very much.');\n\t history.go(-2);\n </script>";
$where = '0' . $where;
}
$where = $where ? 'contact_company IN(' . $where . ')' : '';
} elseif (!$company_id && !$show_all) {
// Contacts from all allowed companies
$where = '(contact_company IS NULL OR contact_company = 0)';
$company_name = $AppUI->_('No Company');
} elseif ($show_all) {
$company_name = $AppUI->_('Allowed Companies');
} else {
// Contacts for this company only
$q->addWhere('contact_company = ' . (int) $company_id);
}
// This should now work on company ID, but we need to be able to handle both
$q->addTable('contacts', 'a');
$q->leftJoin('companies', 'b', 'company_id = contact_company');
$q->leftJoin('departments', 'c', 'dept_id = contact_department');
$q->addQuery('contact_id, contact_first_name, contact_last_name, contact_company, contact_department');
$q->addQuery('company_name');
$q->addQuery('dept_name');
if ($where) {
// Don't assume where is set. Change needed to fix Mantis Bug 0002056
$q->addWhere($where);
}
if ($where_dept) {
// Don't assume where is set. Change needed to fix Mantis Bug 0002056
$q->addWhere($where_dept);
}
$oCpy = new CCompany();
$aCpies = $oCpy->getAllowedRecords($AppUI->user_id, 'company_id, company_name', 'company_name');
$where = $oCpy->getAllowedSQL($AppUI->user_id, 'contact_company');
echo '<table class="std">
<tr>
<td>';
$q = new w2p_Database_Query();
$q->addTable('task_log', 't');
$q->addQuery('distinct(t.task_log_id), contact_display_name AS creator');
$q->addQuery('billingcode_value, billingcode_name');
$q->addQuery('ROUND((billingcode_value * t.task_log_hours), 2) AS amount');
$q->addQuery('c.company_name, project_name');
$q->addQuery('ts.task_name, task_log_task, task_log_hours, task_log_description, task_log_date');
$q->addJoin('tasks', 'ts', 'ts.task_id = t.task_log_task');
$q->addJoin('projects', '', 'projects.project_id = ts.task_project');
$q->addJoin('users', 'u', 'user_id = task_log_creator');
$q->addJoin('contacts', '', 'user_contact = contact_id');
$q->addJoin('companies', 'c', 'c.company_id = projects.project_company');
$q->leftJoin('billingcode', '', 'billingcode_id = task_log_costcode');
$q->addJoin('project_departments', '', 'project_departments.project_id = projects.project_id');
$q->addJoin('departments', '', 'department_id = dept_id');
$q->addWhere('task_log_task > 0');
if ($project_id) {
$q->addWhere('projects.project_id = ' . (int) $project_id);
}
if ($company_id) {
$q->addWhere('c.company_id = ' . (int) $company_id);
}
if (!$log_all) {
$q->addWhere('task_log_date >= \'' . $start_date->format(FMT_DATETIME_MYSQL) . '\'');
$q->addWhere('task_log_date <= \'' . $end_date->format(FMT_DATETIME_MYSQL) . '\'');
}
if ($log_ignore) {
$q->addWhere('task_log_hours > 0');
$task_sort_item2 = w2PgetParam($_GET, 'task_sort_item2', '');
$task_sort_type2 = w2PgetParam($_GET, 'task_sort_type2', '');
$task_sort_order1 = (int) w2PgetParam($_GET, 'task_sort_order1', 0);
$task_sort_order2 = (int) w2PgetParam($_GET, 'task_sort_order2', 0);
if (isset($_POST['show_task_options'])) {
$AppUI->setState('TaskListShowIncomplete', w2PgetParam($_POST, 'show_incomplete', 0));
}
$showIncomplete = $AppUI->getState('TaskListShowIncomplete', 0);
$project = new CProject();
$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 w2p_Database_Query();
$q2 = $q;
$q2->addQuery('projects.project_id, COUNT(t1.task_id) as total_tasks');
$perms =& $AppUI->acl();
$projects = array();
if ($canViewTasks) {
** so we have to tweak a little bit,
** also we do not have a special project available
*/
$caller = w2PgetParam($_REQUEST, 'caller', null);
if ($caller == 'todo') {
$user_id = w2PgetParam($_REQUEST, 'user_id', $AppUI->user_id);
$projects[$project_id]['project_name'] = $AppUI->_('Todo for') . ' ' . CContact::getContactByUserid($user_id);
$projects[$project_id]['project_color_identifier'] = 'ff6000';
$q = new w2p_Database_Query();
$q->addQuery('t.*');
$q->addQuery('project_name, project_id, project_color_identifier');
$q->addQuery('tp.task_pinned');
$q->addTable('tasks', 't');
$q->innerJoin('projects', 'pr', 'pr.project_id = t.task_project');
$q->innerJoin('user_tasks', 'ut', 'ut.task_id = t.task_id AND ut.user_id = ' . (int) $user_id);
$q->leftJoin('user_task_pin', 'tp', 'tp.task_id = t.task_id and tp.user_id = ' . (int) $user_id);
$q->addWhere('(t.task_percent_complete < 100 OR t.task_percent_complete IS NULL)');
$q->addWhere('t.task_status = 0');
if (!$showArcProjs) {
$q->addWhere('pr.project_active = 1');
if (($template_status = w2PgetConfig('template_projects_status_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) {
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;
}
function sendNewPass()
{
global $AppUI;
$_live_site = w2PgetConfig('base_url');
$_sitename = w2PgetConfig('company_name');
// ensure no malicous sql gets past
$checkusername = trim(w2PgetParam($_POST, 'checkusername', ''));
$checkusername = db_escape($checkusername);
$confirmEmail = trim(w2PgetParam($_POST, 'checkemail', ''));
$confirmEmail = strtolower(db_escape($confirmEmail));
$q = new w2p_Database_Query();
$q->addTable('users');
$q->addJoin('contacts', 'con', 'user_contact = contact_id', 'inner');
$q->addQuery('user_id');
$q->addWhere('user_username = \'' . $checkusername . '\'');
/* Begin Hack */
/*
* This is a particularly annoying hack but I don't know of a better
* way to resolve #457. In v2.0, there was a refactoring to allow for
* muliple contact methods which resulted in the contact_email being
* removed from the contacts table. If the user is upgrading from
* v1.x and they try to log in before applying the database, crash.
* Info: http://bugs.web2project.net/view.php?id=457
*/
$qTest = new w2p_Database_Query();
$qTest->addTable('w2pversion');
$qTest->addQuery('max(db_version)');
$dbVersion = $qTest->loadResult();
if ($dbVersion >= 21 && $dbVersion < 26) {
$q->leftJoin('contacts_methods', 'cm', 'cm.contact_id = con.contact_id');
$q->addWhere("cm.method_value = '{$confirmEmail}'");
} else {
$q->addWhere("LOWER(contact_email) = '{$confirmEmail}'");
}
/* End Hack */
if (!($user_id = $q->loadResult()) || !$checkusername || !$confirmEmail) {
$AppUI->setMsg('Invalid username or email.', UI_MSG_ERROR);
$AppUI->redirect();
}
$newpass = makePass();
$message = $AppUI->_('sendpass0', UI_OUTPUT_RAW) . ' ' . $checkusername . ' ' . $AppUI->_('sendpass1', UI_OUTPUT_RAW) . ' ' . $_live_site . ' ' . $AppUI->_('sendpass2', UI_OUTPUT_RAW) . ' ' . $newpass . ' ' . $AppUI->_('sendpass3', UI_OUTPUT_RAW);
$subject = $_sitename . ' :: ' . $AppUI->_('sendpass4', UI_OUTPUT_RAW) . ' - ' . $checkusername;
$m = new w2p_Utilities_Mail();
// create the mail
$m->To($confirmEmail);
$m->Subject($subject);
$m->Body($message, isset($GLOBALS['locale_char_set']) ? $GLOBALS['locale_char_set'] : '');
// set the body
$m->Send();
// send the mail
$newpass = md5($newpass);
$q->addTable('users');
$q->addUpdate('user_password', $newpass);
$q->addWhere('user_id=' . $user_id);
$cur = $q->exec();
if (!$cur) {
die('SQL error' . $database->stderr(true));
} else {
$AppUI->setMsg('New User Password created and emailed to you');
$AppUI->redirect();
}
}
public function getAllTasksForPeriod($start_date, $end_date, $company_id = 0, $user_id = null)
{
global $AppUI;
$q = new w2p_Database_Query();
// convert to default db time stamp
$db_start = $start_date->format(FMT_DATETIME_MYSQL);
$db_end = $end_date->format(FMT_DATETIME_MYSQL);
// Allow for possible passing of user_id 0 to stop user filtering
if (!isset($user_id)) {
$user_id = $AppUI->user_id;
}
// check permissions on projects
$proj = new CProject();
$task_filter_where = $proj->getAllowedSQL($AppUI->user_id, 't.task_project');
// exclude read denied projects
$deny = $proj->getDeniedRecords($AppUI->user_id);
// check permissions on tasks
$obj = new CTask();
$allow = $obj->getAllowedSQL($AppUI->user_id, 't.task_id');
$q->addTable('tasks', 't');
if ($user_id) {
$q->innerJoin('user_tasks', 'ut', 't.task_id=ut.task_id');
}
$q->innerJoin('projects', 'projects', 't.task_project = projects.project_id');
$q->innerJoin('companies', 'companies', 'projects.project_company = companies.company_id');
$q->leftJoin('project_departments', '', 'projects.project_id = project_departments.project_id');
$q->leftJoin('departments', '', 'departments.dept_id = project_departments.department_id');
$q->addQuery('DISTINCT t.task_id, t.task_name, t.task_start_date, t.task_end_date, t.task_percent_complete, t.task_duration' . ', t.task_duration_type, projects.project_color_identifier AS color, projects.project_name, t.task_milestone, task_description, task_type, company_name, task_access, task_owner');
$q->addWhere('task_status > -1' . ' AND (task_start_date <= \'' . $db_end . '\' AND t.task_percent_complete<100 OR task_end_date = \'0000-00-00 00:00:00\' OR task_end_date = NULL )');
$q->addWhere('project_active = 1');
if (($template_status = w2PgetConfig('template_projects_status_id')) != '') {
$q->addWhere('project_status <> ' . $template_status);
}
if ($user_id) {
$q->addWhere('ut.user_id = ' . (int) $user_id);
}
if ($company_id) {
$q->addWhere('projects.project_company = ' . (int) $company_id);
}
if (count($task_filter_where) > 0) {
$q->addWhere('(' . implode(' AND ', $task_filter_where) . ')');
}
if (count($deny) > 0) {
$q->addWhere('(t.task_project NOT IN (' . implode(', ', $deny) . '))');
}
if (count($allow) > 0) {
$q->addWhere('(' . implode(' AND ', $allow) . ')');
}
$q->addOrder('t.task_start_date');
// assemble query
$tasks = $q->loadList(-1, 'task_id');
// check tasks access
$result = array();
foreach ($tasks as $key => $row) {
$obj->load($row['task_id']);
$canAccess = $obj->canAccess();
if (!$canAccess) {
continue;
}
$result[$key] = $row;
}
// execute and return
return $result;
}
$q3->addQuery('file_folder_id, file_folder_name');
$q3->addJoin('projects', 'p', 'p.project_id = file_project');
$q3->addJoin('users', 'u', 'u.user_id = file_owner');
$q3->addJoin('contacts', 'con', 'con.contact_id = u.user_contact');
$q3->addJoin('tasks', 't', 't.task_id = file_task');
$q3->addJoin('file_folders', 'ff', 'ff.file_folder_id = file_folder');
if ($project_id) {
$q3->addWhere('file_project = ' . (int) $project_id);
}
if ($task_id) {
$q3->addWhere('file_task = ' . (int) $task_id);
}
if ($company_id) {
$q3->addWhere('project_company = ' . (int) $company_id);
}
$q3->leftJoin('users', 'cu', 'cu.user_id = file_checkout');
$q3->leftJoin('contacts', 'co', 'co.contact_id = cu.user_contact');
$q3->leftJoin('project_departments', 'project_departments', 'p.project_id = project_departments.project_id OR project_departments.project_id IS NULL');
$q3->leftJoin('departments', 'departments', 'departments.dept_id = project_departments.department_id OR dept_id IS NULL');
if (count($allowedProjects)) {
$q3->addWhere('( ( ' . implode(' AND ', $allowedProjects) . ') OR file_project = 0 )');
}
if (count($allowedTasks)) {
$q3->addWhere('( ( ' . implode(' AND ', $allowedTasks) . ') OR file_task = 0 )');
}
if ($catsql) {
$q3->addWhere($catsql);
}
$files = array();
$file_versions = array();
if ($canRead) {
