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) {