public static function getDepartments(CAppUI $AppUI, $companyId) { $perms = $AppUI->acl(); if ($AppUI->isActiveModule('departments') && canView('departments')) { $q = new w2p_Database_Query(); $q->addTable('departments'); $q->addQuery('departments.*, COUNT(contact_department) dept_users'); $q->addJoin('contacts', 'c', 'c.contact_department = dept_id'); $q->addWhere('dept_company = ' . (int) $companyId); $q->addGroup('dept_id'); $q->addOrder('dept_parent, dept_name'); $department = new CDepartment(); $department->setAllowedSQL($AppUI->user_id, $q); return $q->loadList(); } }
function projects_list_data($user_id = false) { global $AppUI, $addPwOiD, $buffer, $company, $company_id, $company_prefix, $deny, $department, $dept_ids, $w2Pconfig, $orderby, $orderdir, $tasks_problems, $owner, $projectTypeId, $search_text, $project_type; $addProjectsWithAssignedTasks = $AppUI->getState('addProjWithTasks') ? $AppUI->getState('addProjWithTasks') : 0; // get any records denied from viewing $obj = new CProject(); $deny = $obj->getDeniedRecords($AppUI->user_id); // Let's delete temproary tables $q = new w2p_Database_Query(); $q->setDelete('tasks_problems'); $q->exec(); $q->clear(); $q->setDelete('tasks_users'); $q->exec(); $q->clear(); // support task problem logs $q->addInsertSelect('tasks_problems'); $q->addTable('tasks'); $q->addQuery('task_project, task_log_problem'); $q->addJoin('task_log', 'tl', 'tl.task_log_task = task_id', 'inner'); $q->addWhere('task_log_problem = 1'); $q->addGroup('task_project'); $tasks_problems = $q->exec(); $q->clear(); if ($addProjectsWithAssignedTasks) { // support users tasks $q->addInsertSelect('tasks_users'); $q->addTable('tasks'); $q->addQuery('task_project'); $q->addQuery('ut.user_id'); $q->addJoin('user_tasks', 'ut', 'ut.task_id = tasks.task_id'); if ($user_id) { $q->addWhere('ut.user_id = ' . (int) $user_id); } $q->addOrder('task_end_date DESC'); $q->addGroup('task_project'); $tasks_users = $q->exec(); $q->clear(); } // add Projects where the Project Owner is in the given department if ($addPwOiD && isset($department)) { $owner_ids = array(); $q->addTable('users'); $q->addQuery('user_id'); $q->addJoin('contacts', 'c', 'c.contact_id = user_contact', 'inner'); $q->addWhere('c.contact_department = ' . (int) $department); $owner_ids = $q->loadColumn(); $q->clear(); } if (isset($department)) { //If a department is specified, we want to display projects from the department, and all departments under that, so we need to build that list of departments $dept_ids = array(); $q->addTable('departments'); $q->addQuery('dept_id, dept_parent'); $q->addOrder('dept_parent,dept_name'); $rows = $q->loadList(); addDeptId($rows, $department); $dept_ids[] = isset($department->dept_id) ? $department->dept_id : 0; $dept_ids[] = $department > 0 ? $department : 0; } $q->clear(); // retrieve list of records // modified for speed // by Pablo Roca (pabloroca@mvps.org) // 16 August 2003 // get the list of permitted companies $obj = new CCompany(); $companies = $obj->getAllowedRecords($AppUI->user_id, 'companies.company_id,companies.company_name', 'companies.company_name'); if (count($companies) == 0) { $companies = array(); } $q->addTable('projects', 'pr'); $q->addQuery('pr.project_id, project_status, project_color_identifier, project_type, project_name, project_description, project_scheduled_hours as project_duration, project_parent, project_original_parent, project_percent_complete, project_color_identifier, project_company, company_name, project_status, project_last_task as critical_task, tp.task_log_problem, user_username, project_active'); $fields = w2p_Core_Module::getSettings('projects', 'index_list'); unset($fields['department_list']); // added as an alias below foreach ($fields as $field => $text) { $q->addQuery($field); } $q->addQuery('CONCAT(ct.contact_first_name, \' \', ct.contact_last_name) AS owner_name'); $q->addJoin('users', 'u', 'pr.project_owner = u.user_id'); $q->addJoin('contacts', 'ct', 'ct.contact_id = u.user_contact'); $q->addJoin('tasks_problems', 'tp', 'pr.project_id = tp.task_project'); if ($addProjectsWithAssignedTasks) { $q->addJoin('tasks_users', 'tu', 'pr.project_id = tu.task_project'); } if (!isset($department) && $company_id && !$addPwOiD) { $q->addWhere('pr.project_company = ' . (int) $company_id); } if ($project_type > -1) { $q->addWhere('pr.project_type = ' . (int) $project_type); } if (isset($department) && !$addPwOiD) { $q->addWhere('project_departments.department_id in ( ' . implode(',', $dept_ids) . ' )'); } if ($user_id && $addProjectsWithAssignedTasks) { $q->addWhere('(tu.user_id = ' . (int) $user_id . ' OR pr.project_owner = ' . (int) $user_id . ' )'); } elseif ($user_id) { $q->addWhere('pr.project_owner = ' . (int) $user_id); } if ($owner > 0) { $q->addWhere('pr.project_owner = ' . (int) $owner); } if (mb_trim($search_text)) { $q->addWhere('pr.project_name LIKE \'%' . $search_text . '%\' OR pr.project_description LIKE \'%' . $search_text . '%\''); } // Show Projects where the Project Owner is in the given department if ($addPwOiD && !empty($owner_ids)) { $q->addWhere('pr.project_owner IN (' . implode(',', $owner_ids) . ')'); } $orderby = 'project_company' == $orderby ? 'company_name' : $orderby; $q->addGroup('pr.project_id'); $q->addOrder($orderby . ' ' . $orderdir); $prj = new CProject(); $prj->setAllowedSQL($AppUI->user_id, $q, null, 'pr'); $dpt = new CDepartment(); $projects = $q->loadList(); // get the list of permitted companies $companies = arrayMerge(array('0' => $AppUI->_('All')), $companies); $company_array = $companies; //get list of all departments, filtered by the list of permitted companies. $q->clear(); $q->addTable('companies'); $q->addQuery('company_id, company_name, dep.*'); $q->addJoin('departments', 'dep', 'companies.company_id = dep.dept_company'); $q->addOrder('company_name,dept_parent,dept_name'); $obj->setAllowedSQL($AppUI->user_id, $q); $dpt->setAllowedSQL($AppUI->user_id, $q); $rows = $q->loadList(); //display the select list $buffer = '<select name="department" id="department" onChange="document.pickCompany.submit()" class="text" style="width: 200px;">'; $company = ''; foreach ($company_array as $key => $c_name) { $buffer .= '<option value="' . $company_prefix . $key . '" style="font-weight:bold;"' . ($company_id == $key ? 'selected="selected"' : '') . '>' . $c_name . '</option>' . "\n"; foreach ($rows as $row) { if ($row['dept_parent'] == 0) { if ($key == $row['company_id']) { if ($row['dept_parent'] != null) { findchilddept($rows, $row['dept_id']); } } } } } $buffer .= '</select>'; return $projects; }
public function getAllowedProjects($userId, $activeOnly = true) { $q = new w2p_Database_Query(); $q->addTable('projects', 'pr'); $q->addQuery('pr.project_id, project_color_identifier, project_name, project_start_date, project_end_date, project_company, project_parent'); if ($activeOnly) { $q->addWhere('project_active = 1'); } $q->addGroup('pr.project_id'); $q->addOrder('project_name'); $this->setAllowedSQL($userId, $q, null, 'pr'); return $q->loadHashList('project_id'); }
public function _buildQuery() { $q = new w2p_Database_Query(); if ($this->table_alias) { $q->addTable($this->table, $this->table_alias); } else { $q->addTable($this->table); } $q->addQuery('DISTINCT(' . $this->table_key . ')'); if (isset($this->table_key2)) { $q->addQuery($this->table_key2); } //--MSy-- foreach ($this->table_joins as $join) { $q->addJoin($join['table'], $join['alias'], $join['join']); } foreach ($this->display_fields as $fld) { $q->addQuery($fld); } $q->addOrder($this->table_orderby); if ($this->table_groupby) { $q->addGroup($this->table_groupby); } if ($this->table_extra) { $q->addWhere($this->table_extra); } $ignore = w2PgetSysVal('FileIndexIgnoreWords'); $ignore = explode(',', $ignore['FileIndexIgnoreWords']); $this->keywords = array_diff(array_keys($this->keywords), $ignore); $sql = ''; foreach ($this->keywords as $keyword) { $sql .= '('; foreach ($this->search_fields as $field) { //OR treatment to each keyword // Search for semi-colons, commas or spaces and allow any to be separators $or_keywords = preg_split('/[\\s,;]+/', $keyword); foreach ($or_keywords as $or_keyword) { if ($this->search_options['ignore_specchar'] == 'on') { $tmppattern = recode2regexp_utf8($or_keyword); if ($this->search_options['ignore_case'] == 'on') { $sql .= ' ' . $field . ' REGEXP \'' . $tmppattern . '\' or '; } else { $sql .= ' ' . $field . ' REGEXP BINARY \'' . $tmppattern . '\' or '; } } else { if ($this->search_options['ignore_case'] == 'on') { $sql .= ' ' . $field . ' LIKE "%' . $or_keyword . '%" or '; } else { $sql .= ' ' . $field . ' LIKE BINARY "%' . $or_keyword . '%" or '; } } } } // foreach $field $sql = substr($sql, 0, -4); if ($this->search_options['all_words'] == 'on') { $sql .= ') and '; } else { $sql .= ') or '; } } // foreach $keyword //--MSy-- $sql = substr($sql, 0, -4); if ($sql) { $q->addWhere($sql); return $q; } else { return null; } }
$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); } if ($project_id > 0) { $q2->addWhere('project_id = ' . $project_id); } $q2->addGroup('project_id'); $perms =& $AppUI->acl(); $projects = array(); $canViewTask = canView('tasks'); if ($canViewTask) { $prc = $q->exec(); echo db_error(); while ($row = $q->fetchRow()) { $projects[$row['project_id']] = $row; } $prc2 = $q2->fetchRow(); echo db_error(); while ($row2 = $q2->fetchRow()) { if ($projects[$row2['project_id']]) { array_push($projects[$row2['project_id']], $row2); }
public static function getLogs($userId, $startDate, $endDate) { $q = new w2p_Database_Query(); $q->addTable('user_access_log', 'ual'); $q->addTable('users', 'u'); $q->addTable('contacts', 'c'); $q->addQuery('ual.*, u.*, c.*'); $q->addWhere('ual.user_id = u.user_id'); $q->addWhere('user_contact = contact_id '); if ($userId > 0) { $q->addWhere('ual.user_id = ' . (int) $userId); } $q->addWhere("ual.date_time_in >= '{$startDate}'"); $q->addWhere("ual.date_time_out <= '{$endDate}'"); $q->addGroup('ual.date_time_last_action DESC'); return $q->loadList(); }
function w2PgetUsersHashList($stub = null, $where = null, $orderby = 'contact_first_name, contact_last_name') { global $AppUI; $q = new w2p_Database_Query(); $q->addTable('users'); $q->addQuery('DISTINCT(user_id), user_username, contact_last_name, contact_first_name, company_name, contact_company, dept_id, dept_name, CONCAT(contact_first_name,\' \',contact_last_name) contact_name, user_type'); $q->addJoin('contacts', 'con', 'con.contact_id = user_contact', 'inner'); if ($stub) { $q->addWhere('(UPPER(user_username) LIKE \'' . $stub . '%\' or UPPER(contact_first_name) LIKE \'' . $stub . '%\' OR UPPER(contact_last_name) LIKE \'' . $stub . '%\')'); } elseif ($where) { $where = $q->quote('%' . $where . '%'); $q->addWhere('(UPPER(user_username) LIKE ' . $where . ' OR UPPER(contact_first_name) LIKE ' . $where . ' OR UPPER(contact_last_name) LIKE ' . $where . ')'); } $q->addQuery('contact_email'); $q->addGroup('user_id'); $q->addOrder($orderby); // get CCompany() to filter by company $obj = new CCompany(); $companies = $obj->getAllowedSQL($AppUI->user_id, 'company_id'); $q->addJoin('companies', 'com', 'company_id = contact_company'); if ($companies) { $q->addWhere('(' . implode(' OR ', $companies) . ' OR contact_company=\'\' OR contact_company IS NULL OR contact_company = 0)'); } $dpt = new CDepartment(); $depts = $dpt->getAllowedSQL($AppUI->user_id, 'dept_id'); $q->addJoin('departments', 'dep', 'dept_id = contact_department'); if ($depts) { $q->addWhere('(' . implode(' OR ', $depts) . ' OR contact_department=0)'); } return $q->loadHashList('user_id'); }
$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) { $prc = $q->exec(); echo db_error(); while ($row = $q->fetchRow()) { $projects[$row['project_id']] = $row; } $prc2 = $q2->exec(); echo db_error(); while ($row2 = $q2->fetchRow()) { $projects[$row2['project_id']] = !$projects[$row2['project_id']] ? array() : $projects[$row2['project_id']];
/** * @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; }
$q->addTable('resources', 'a'); $q->addJoin('resource_tasks', 'b', 'b.resource_id = a.resource_id', 'inner'); $q->addJoin('resource_types', 'c', 'c.resource_type_id = a.resource_type', 'inner'); $q->addWhere('b.task_id = ' . (int) $task_id); $resources = $q->loadHashList('resource_id'); // Determine any other clashes. $resource_tasks = array(); if (count($resources)) { $q->clear(); $q->addQuery('b.resource_id, sum(b.percent_allocated) as total_allocated'); $q->addTable('tasks', 'a'); $q->addJoin('resource_tasks', 'b', 'b.task_id = a.task_id', 'inner'); $q->addWhere('b.resource_id IN (' . implode(',', array_keys($resources)) . ')'); $q->addWhere('task_start_date <= \'' . $obj->task_end_date . '\''); $q->addWhere('task_end_date >= \'' . $obj->task_start_date . '\''); $q->addGroup('resource_id'); $resource_tasks = $q->loadHashList(); } ?> <table class="std" width="100%" cellpadding="4" cellspacing="1"> <tr><th>Type</th><th>Resource</th><th>Allocation</th><th> </th></tr> <?php foreach ($resources as $res) { $output = '<tr><td class="hilite">' . $res['resource_type_name'] . '</td> <td class="hilite">' . $res['resource_name'] . '</td> <td class="hilite">' . $res['percent_allocated'] . '%</td><td class="warning">'; if (isset($resource_tasks[$res['resource_id']]) && $resource_tasks[$res['resource_id']] > $res['resource_max_allocation']) { $output .= 'OVERALLOCATED'; } $output .= ' </td></tr>'; echo $output;
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(); }
/** * 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; }
$q2->addWhere('file_project = ' . (int) $project_id); } if ($task_id) { $q2->addWhere('file_task = ' . (int) $task_id); } if ($company_id) { $q2->addWhere('project_company = ' . (int) $company_id); } if ($catsql) { $q2->addWhere($catsql); } $q2->setLimit($xpg_pagesize, $xpg_min); // Adding an Order by that is different to a group by can cause // performance issues. It is far better to rearrange the group // by to get the correct ordering. $q2->addGroup('p.project_id'); $q2->addGroup('file_version_id DESC'); $q3 = new w2p_Database_Query(); $q3->addTable('files'); $q3->addQuery('file_id, file_version, file_project, file_name, file_task, file_description, u.user_username as file_owner, file_size, file_category, task_name, file_version_id, file_checkout, file_co_reason, file_type, file_date, cu.user_username as co_user, project_name, project_color_identifier, project_owner, con.contact_first_name, con.contact_last_name, co.contact_first_name as co_contact_first_name, co.contact_last_name as co_contact_last_name'); $q3->addQuery('file_folder_id, file_folder_name'); $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');
function showcompany($company, $restricted = false) { global $AppUI, $allpdfdata, $log_start_date, $log_end_date, $log_all; $q = new w2p_Database_Query(); $q->addTable('projects'); $q->addQuery('project_id, project_name'); $q->addWhere('project_company = ' . (int) $company); $projects = $q->loadHashList(); $q->clear(); $q->addTable('companies'); $q->addQuery('company_name'); $q->addWhere('company_id = ' . (int) $company); $company_name = $q->loadResult(); $q->clear(); $table = '<h2>Company: ' . $company_name . '</h2> <table cellspacing="1" cellpadding="4" border="0" class="tbl">'; $project_row = ' <tr> <th>' . $AppUI->_('Project') . '</th>'; $pdfth[] = $AppUI->_('Project'); $project_row .= '<th>' . $AppUI->_('Total') . '</th></tr>'; $pdfth[] = $AppUI->_('Total'); $pdfdata[] = $pdfth; $hours = 0.0; $table .= $project_row; foreach ($projects as $project => $name) { $pdfproject = array(); $pdfproject[] = $name; $project_hours = 0; $project_row = '<tr><td>' . $name . '</td>'; $q->addTable('projects'); $q->addTable('tasks'); $q->addTable('task_log'); $q->addQuery('task_log_costcode, SUM(task_log_hours) as hours'); $q->addWhere('project_id = ' . (int) $project); $q->addWhere('project_active = 1'); if (($template_status = w2PgetConfig('template_projects_status_id')) != '') { $q->addWhere('project_status <> ' . (int) $template_status); } if ($log_start_date != 0 && !$log_all) { $q->addWhere('task_log_date >=' . $log_start_date); } if ($log_end_date != 0 && !$log_all) { $q->addWhere('task_log_date <=' . $log_end_date); } if ($restricted) { $q->addWhere('task_log_creator = ' . (int) $AppUI->user_id); } $q->addWhere('project_id = task_project'); $q->addWhere('task_id = task_log_task'); $q->addGroup('project_id'); $task_logs = $q->loadHashList(); $q->clear(); foreach ($task_logs as $task_log) { $project_hours += $task_log; } $project_row .= '<td style="text-align:right;">' . sprintf('%.2f', round($project_hours, 2)) . '</td></tr>'; $pdfproject[] = round($project_hours, 2); $hours += $project_hours; if ($project_hours > 0) { $table .= $project_row; $pdfdata[] = $pdfproject; } } if ($hours > 0) { $pdfdata[] = array($AppUI->_('Total'), round($hours, 2)); $allpdfdata[$company_name] = $pdfdata; echo $table; echo '<tr><td>' . $AppUI->_('Total') . '</td><td style="text-align:right;">' . sprintf('%.2f', round($hours, 2)) . '</td></tr></table>'; } return $hours; }
$q->addQuery('v1.visit_user'); $q->leftJoin('users', 'u', 'fm1.message_author = u.user_id'); $q->leftJoin('contacts', 'con', 'contact_id = user_contact'); $q->leftJoin('forum_messages', 'fm2', 'fm1.message_id = fm2.message_parent'); $q->leftJoin('forum_watch', 'fw', 'watch_user = '******' AND watch_topic = fm1.message_id'); $q->leftJoin('forum_visits', 'v1', 'v1.visit_user = '******' AND v1.visit_message = fm1.message_id'); $q->addWhere('fm1.message_forum = ' . (int) $forum_id); switch ($f) { case 1: $q->addWhere('watch_user IS NOT NULL'); break; case 2: $q->addWhere('(NOW() < DATE_ADD(fm2.message_date, INTERVAL 30 DAY) OR NOW() < DATE_ADD(fm1.message_date, INTERVAL 30 DAY))'); break; } $q->addGroup('fm1.message_id, fm1.message_parent'); $q->addOrder($orderby . ' ' . $orderdir); $topics = $q->loadList(); $crumbs = array(); $crumbs['?m=forums'] = 'forums list'; ?> <br /> <?php if (function_exists('styleRenderBoxTop')) { echo styleRenderBoxTop(); } ?> <form name="watcher" action="?m=forums&a=viewer&forum_id=<?php echo $forum_id; ?> &f=<?php
/** * Calculate the extent of utilization of user assignments * @param string hash a hash for the returned hashList * @param array users an array of user_ids calculating their assignment capacity * @return array returns hashList of extent of utilization for assignment of the users */ public function getAllocation($hash = null, $users = null, $get_user_list = false) { /* * TODO: The core of this function has been simplified to always return 100% * free capacity available. The allocation checking (aka resource * management) is a complex subject which is currently not even close to be * handled properly. */ global $AppUI; if (!w2PgetConfig('check_overallocation', false)) { if ($get_user_list) { $users_list = w2PgetUsersHashList(); foreach ($users_list as $key => $user) { $users_list[$key]['userFC'] = $user['contact_name']; } $hash = $users_list; } else { $hash = array(); } } else { $q = new w2p_Database_Query(); // retrieve the systemwide default preference for the assignment maximum $q->addTable('user_preferences'); $q->addQuery('pref_value'); $q->addWhere('pref_user = 0 AND pref_name = \'' . TASKASSIGNMAX . '\''); $sysChargeMax = $q->loadHash(); $q->clear(); if (!$sysChargeMax) { $scm = 0; } else { $scm = $sysChargeMax['pref_value']; } /* * provide actual assignment charge, individual chargeMax * and freeCapacity of users' assignments to tasks */ $q->addTable('users', 'u'); $q->addJoin('contacts', 'c', 'c.contact_id = u.user_contact', 'inner'); $q->leftJoin('user_tasks', 'ut', 'ut.user_id = u.user_id'); $q->leftJoin('user_preferences', 'up', 'up.pref_user = u.user_id'); $q->addWhere("up.pref_name = 'TASKASSIGNMAX'"); $q->addQuery('u.user_id, CONCAT(CONCAT_WS(\' [\', CONCAT_WS(\' \', contact_first_name, contact_last_name), IF(IFNULL((IFNULL(up.pref_value, ' . $scm . ') - SUM(ut.perc_assignment)), up.pref_value) > 0, IFNULL((IFNULL(up.pref_value, ' . $scm . ') - SUM(ut.perc_assignment)), up.pref_value), 0)), \'%]\') AS userFC, IFNULL(SUM(ut.perc_assignment), 0) AS charge'); $q->addQuery('u.user_username, IFNULL(up.pref_value,' . $scm . ') AS chargeMax'); $q->addQuery('IFNULL(up.pref_value, ' . $scm . ') AS freeCapacity'); if (!empty($users)) { // use userlist if available otherwise pull data for all users $q->addWhere('u.user_id IN (' . implode(',', $users) . ')'); } $q->addGroup('u.user_id'); $q->addOrder('contact_first_name, contact_last_name'); // get CCompany() to filter by company $obj = new CCompany(); $companies = $obj->getAllowedSQL($AppUI->user_id, 'company_id'); $q->addJoin('companies', 'com', 'company_id = contact_company'); if ($companies) { $q->addWhere('(' . implode(' OR ', $companies) . ' OR contact_company=\'\' OR contact_company IS NULL OR contact_company = 0)'); } $dpt = new CDepartment(); $depts = $dpt->getAllowedSQL($AppUI->user_id, 'dept_id'); $q->addJoin('departments', 'dep', 'dept_id = contact_department'); if ($depts) { $q->addWhere('(' . implode(' OR ', $depts) . ' OR contact_department=0)'); } $hash = $q->loadHashList($hash); $q->clear(); } return $hash; }
/** * Generic check for whether dependencies exist for this object in the db schema * * Can be overloaded/supplemented by the child class * @param string $msg Error message returned * @param int Optional key index * @param array Optional array to compiles standard joins: format [label=>'Label',name=>'table name',idfield=>'field',joinfield=>'field'] * @return true|false */ public function canDelete(&$msg, $oid = null, $joins = null) { global $AppUI; // First things first. Are we allowed to delete? $acl =& $AppUI->acl(); if (!$acl->checkModuleItem($this->_tbl_module, 'delete', $oid)) { $msg = $AppUI->_('noDeletePermission'); return false; } $k = $this->_tbl_key; if ($oid) { $this->{$k} = intval($oid); } if (is_array($joins)) { $select = $k; $join = ''; $q = new w2p_Database_Query(); $q->addTable($this->_tbl); $q->addWhere($k . ' = \'' . $this->{$k} . '\''); $q->addGroup($k); foreach ($joins as $table) { $q->addQuery('COUNT(DISTINCT ' . $table['idfield'] . ') AS ' . $table['idfield']); $q->addJoin($table['name'], $table['name'], $table['joinfield'] . ' = ' . $k); } $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); $this->_error = $msg; return false; } else { return true; } } return true; }
$q->addJoin('projects', '', 'project_id = task_project', 'inner'); $q->addQuery('t.task_id, round(t.task_duration * IF(t.task_duration_type = 24, ' . $working_hours . ', t.task_duration_type)/count(ut.task_id),2) as hours_allocated'); $q->addWhere('t.task_id = ut.task_id'); $q->addWhere('t.task_milestone = 0'); $q->addWhere('project_active = 1'); if (($template_status = w2PgetConfig('template_projects_status_id')) != '') { $q->addWhere('project_status <> ' . (int) $template_status); } if ($project_id != 0) { $q->addWhere('t.task_project = ' . (int) $project_id); } if (!$log_all) { $q->addWhere('t.task_start_date >= \'' . $start_date->format(FMT_DATETIME_MYSQL) . '\''); $q->addWhere('t.task_start_date <= \'' . $end_date->format(FMT_DATETIME_MYSQL) . '\''); } $q->addGroup('t.task_id'); $task_list = $q->loadHashList('task_id'); $q->clear(); ?> <table cellspacing="1" cellpadding="4" border="0" class="tbl"> <tr> <th colspan='2'><?php echo $AppUI->_('User'); ?> </th> <th><?php echo $AppUI->_('Hours allocated'); ?> </th> <th><?php
$users[0]['inprogress']++; } } $users[0]['hours'] += $users[0]['all'][$task['task_id']]['work']; $tasks['hours'] += $users[0]['all'][$task['task_id']]['work']; } } $q = new w2p_Database_Query(); $q->addTable('files'); $q->addQuery('sum(file_size)'); if ($project_id) { $q->addWhere('file_project = ' . (int) $project_id); } else { $q->addWhere('file_project = 0'); } $q->addGroup('file_project'); $files = $q->loadResult(); $q->clear(); $ontime = round(100 * (1 - $tasks['overdue'] / count($all_tasks) - $tasks['completed'] / count($all_tasks))); ?> <table width="100%" border="1" cellpadding="0" cellspacing="0" class="tbl"> <tr> <th colspan="3"><?php echo $AppUI->_('Progress Chart (completed/in progress/pending)'); ?> </th> </tr> <tr> <td width="<?php echo round($tasks['completed'] / count($all_tasks) * 100);
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); } if ($task_id) { $q->addWhere('file_task = ' . (int) $task_id); } $q->addGroup('file_version_id'); // counts total recs from selection $xpg_totalrecs = count($q->loadList()); $pageNav = buildPaginationNav($AppUI, $m, $tab, $xpg_totalrecs, $xpg_pagesize, $page); echo $pageNav; ?> <script language="javascript" type="text/javascript"> function expand(id){ var element = document.getElementById(id); element.style.display = (element.style.display == '' || element.style.display == 'none') ? 'block' : 'none'; } </script> <table width="100%" border="0" cellpadding="2" cellspacing="1" class="tbl list"> <?php global $showProject; $showProject = true;
public function getFileCountByFolder(CAppUI $AppUI, $folder_id, $task_id, $project_id, $company_id) { // SQL text for count the total recs from the selected option $q = new w2p_Database_Query(); $q->addTable('files'); $q->addQuery('count(files.file_id)'); $q->addJoin('projects', 'p', 'p.project_id = file_project'); $q->addJoin('users', 'u', 'u.user_id = file_owner'); $q->addJoin('tasks', 't', 't.task_id = file_task'); $q->addJoin('file_folders', 'ff', 'ff.file_folder_id = file_folder'); $q->addWhere('file_folder = ' . (int) $folder_id); //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->innerJoin('companies', 'co', 'co.company_id = p.project_company'); $q->addWhere('company_id = ' . (int) $company_id); $q->addWhere('company_id IN (' . $allowed_companies . ')'); } $q->addGroup('file_folder_name'); $q->addGroup('project_name'); $q->addGroup('file_name'); // counts total recs from selection return count($q->loadList()); }