public static function searchContacts(CAppUI $AppUI = null, $where = '', $searchString = '')
{
global $AppUI;
$showfields = array('contact_address1' => 'contact_address1', 'contact_address2' => 'contact_address2', 'contact_city' => 'contact_city', 'contact_state' => 'contact_state', 'contact_zip' => 'contact_zip', 'contact_country' => 'contact_country', 'contact_company' => 'contact_company', 'company_name' => 'company_name', 'dept_name' => 'dept_name');
$additional_filter = '';
if ($searchString != '') {
$additional_filter = "OR contact_first_name like '%{$searchString}%'\n OR contact_last_name like '%{$searchString}%'\n\t\t\t OR CONCAT(contact_first_name, ' ', contact_last_name) like '%{$searchString}%'\n OR company_name like '%{$searchString}%'\n OR contact_notes like '%{$searchString}%'";
}
// assemble the sql statement
$q = new w2p_Database_Query();
$q->addQuery('contact_id, contact_order_by');
$q->addQuery($showfields);
$q->addQuery('contact_first_name, contact_last_name, contact_title');
$q->addQuery('contact_updatekey, contact_updateasked, contact_lastupdate');
$q->addQuery('contact_email, contact_phone');
$q->addQuery('user_id');
$q->addTable('contacts', 'a');
$q->leftJoin('companies', 'b', 'a.contact_company = b.company_id');
$q->leftJoin('departments', '', 'contact_department = dept_id');
$q->leftJoin('users', '', 'contact_id = user_contact');
$q->addWhere("(contact_first_name LIKE '{$where}%' OR contact_last_name LIKE '{$where}%' " . $additional_filter . ")");
$q->addWhere('
(contact_private=0
OR (contact_private=1 AND contact_owner=' . $AppUI->user_id . ')
OR contact_owner IS NULL OR contact_owner = 0
)');
$company = new CCompany();
$company->setAllowedSQL($AppUI->user_id, $q);
$department = new CDepartment();
$department->setAllowedSQL($AppUI->user_id, $q);
$q->addOrder('contact_first_name');
$q->addOrder('contact_last_name');
return $q->loadList();
}
public static function getDepartments(CAppUI $AppUI = null, $projectId)
{
global $AppUI;
$perms = $AppUI->acl();
if ($AppUI->isActiveModule('departments') && canView('departments')) {
$q = new w2p_Database_Query();
$q->addTable('departments', 'a');
$q->addTable('project_departments', 'b');
$q->addQuery('a.dept_id, a.dept_name, a.dept_phone');
$q->addWhere('a.dept_id = b.department_id and b.project_id = ' . (int) $projectId);
$department = new CDepartment();
$department->setAllowedSQL($AppUI->user_id, $q);
return $q->loadHashList('dept_id');
}
}
public function getContacts(CAppUI $AppUI = null, $task_id)
{
global $AppUI;
$perms = $AppUI->acl();
if (canView('contacts')) {
$q = new w2p_Database_Query();
$q->addTable('contacts', 'c');
$q->addQuery('c.contact_id, contact_first_name, contact_last_name');
$q->leftJoin('departments', 'd', 'dept_id = contact_department');
$q->addQuery('dept_name');
$q->addJoin('task_contacts', 'tc', 'tc.contact_id = c.contact_id', 'inner');
$q->addWhere('tc.task_id = ' . (int) $task_id);
$q->addQuery('c.contact_id, contact_first_name, contact_last_name, contact_order_by, contact_email');
$q->addWhere('(contact_owner = ' . (int) $AppUI->user_id . ' OR contact_private = 0)');
$department = new CDepartment();
$department->setAllowedSQL($AppUI->user_id, $q);
return $q->loadHashList('contact_id');
}
}
public static function getDepartments($AppUI, $companyId)
{
$perms = $AppUI->acl();
if ($AppUI->isActiveModule('departments') && $perms->checkModule('departments', 'view')) {
$q = new DBQuery();
$q->addTable('departments');
$q->addQuery('departments.*, COUNT(contact_department) dept_users');
$q->addJoin('contacts', 'c', 'c.contact_department = dept_id');
$q->addWhere('dept_company = ' . (int) $companyId);
$q->addGroup('dept_id');
$q->addOrder('dept_parent, dept_name');
$department = new CDepartment();
$department->setAllowedSQL($AppUI->user_id, $q);
return $q->loadList();
}
}
public function getTaskContacts(CAppUI $AppUI = null, $taskId)
{
global $AppUI;
$perms = $AppUI->acl();
if ($AppUI->isActiveModule('contacts') && $perms->checkModule('contacts', 'view')) {
$q = new DBQuery();
$q->addTable('contacts', 'c');
$q->addJoin('task_contacts', 'tc', 'tc.contact_id = c.contact_id', 'inner');
$q->leftJoin('departments', 'd', 'dept_id = contact_department');
$q->addWhere('tc.task_id = ' . (int) $taskId);
$q->addQuery('c.contact_id, contact_first_name, contact_last_name, contact_email');
$q->addQuery('contact_phone, dept_name');
$q->addWhere('(contact_owner = ' . (int) $AppUI->user_id . ' OR contact_private = 0)');
$department = new CDepartment();
$department->setAllowedSQL($AppUI->user_id, $q);
return $q->loadHashList('contact_id');
}
}
function projects_list_data($user_id = false)
{
global $AppUI, $addPwOiD, $buffer, $company, $company_id, $company_prefix, $deny, $department, $dept_ids, $orderby, $orderdir, $tasks_problems, $owner, $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');
$q->exec();
$q->clear();
}
// add Projects where the Project Owner is in the given department
if ($addPwOiD && isset($department)) {
$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_scheduled_hours as project_duration,
project_actual_end_date as project_end_actual,
company_id, company_name, project_last_task as critical_task,
tp.task_log_problem, user_username, task_log_problem, u.user_id');
$fields = w2p_System_Module::getSettings('projects', 'index_list');
unset($fields['department_list']);
// added as an alias below
foreach ($fields as $field => $notUsed) {
$q->addQuery($field);
}
$q->addQuery('ct.contact_display_name AS owner_name');
$q->addJoin('companies', 'c', 'c.company_id = pr.project_company');
$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 > 0 && !$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();
$q = $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');
$q = $obj->setAllowedSQL($AppUI->user_id, $q);
$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;
}
function getStructuredProjects($original_project_id = 0, $project_status = -1, $active_only = false)
{
global $AppUI, $st_projects_arr;
$st_projects = array(0 => '');
$q = new w2p_Database_Query();
$q->addTable('projects');
$q->addJoin('companies', '', 'projects.project_company = company_id', 'inner');
$q->addQuery('DISTINCT(projects.project_id), project_name, project_parent');
if ($original_project_id) {
$q->addWhere('project_original_parent = ' . (int) $original_project_id);
}
if ($project_status >= 0) {
$q->addWhere('project_status = ' . (int) $project_status);
}
if ($active_only) {
$q->addWhere('project_active = 1');
}
$q->addOrder('project_start_date, project_end_date');
$obj = new CCompany();
$obj->setAllowedSQL($AppUI->user_id, $q);
$dpt = new CDepartment();
$dpt->setAllowedSQL($AppUI->user_id, $q);
$q->leftJoin('project_departments', 'pd', 'pd.project_id = projects.project_id');
$q->leftJoin('departments', 'd', 'd.dept_id = pd.department_id');
$st_projects = $q->loadList();
$tnums = count($st_projects);
for ($i = 0; $i < $tnums; $i++) {
$st_project = $st_projects[$i];
if ($st_project['project_parent'] == $st_project['project_id']) {
show_st_project($st_project);
find_proj_child($st_projects, $st_project['project_id']);
}
}
}
public static function getDepartmentList(CAppUI $AppUI = null, $companyId, $departmentId = 0)
{
global $AppUI;
$q = new w2p_Database_Query();
$q->addTable('departments');
$q->addQuery('dept_id, dept_name');
if (is_int($departmentId)) {
$q->addWhere('dept_parent = ' . (int) $departmentId);
}
$q->addWhere('dept_company = ' . (int) $companyId);
$q->addOrder('dept_name');
$department = new CDepartment();
$department->setAllowedSQL($AppUI->user_id, $q);
return $q->loadHashList('dept_id');
}
public function getContacts($notUsed = null, $task_id)
{
if (canView('contacts')) {
$q = $this->_getQuery();
$q->addTable('contacts', 'c');
$q->addQuery('c.*, dept_id');
$q->addQuery('contact_display_name as contact_name');
$q->leftJoin('departments', 'd', 'dept_id = contact_department');
$q->addQuery('dept_name');
$q->addJoin('task_contacts', 'tc', 'tc.contact_id = c.contact_id', 'inner');
$q->addWhere('tc.task_id = ' . (int) $task_id);
$q->addWhere('(contact_owner = ' . (int) $this->_AppUI->user_id . ' OR contact_private = 0)');
$department = new CDepartment();
$department->overrideDatabase($this->_query);
$q = $department->setAllowedSQL($this->_AppUI->user_id, $q);
return $q->loadHashList('contact_id');
}
}
public function search($search, $days = 0)
{
$hook = $this->hook_search();
$searchfields = $hook['search_fields'];
foreach ($searchfields as $key => $field) {
$searchfields[$key] = "{$field} like '%{$search}%'";
}
$where = implode(' OR ', $searchfields);
$q = $this->_getQuery();
$q->addQuery('distinct(c.contact_id)');
$q->addQuery('c.*');
$q->addQuery('user_id');
$q->addTable('contacts', 'c');
$q->leftJoin('companies', 'b', 'c.contact_company = b.company_id');
$q->leftJoin('departments', '', 'c.contact_department = dept_id');
$q->leftJoin('users', '', 'contact_id = user_contact');
$q->leftJoin('contacts_methods', 'cm', 'c.contact_id = cm.contact_id');
$q->addWhere('(' . $where . ')');
if ($days) {
$q->addWhere('contact_lastupdate >= ' . $q->dbfnDateAdd($q->dbfnNow(), -$days, 'DAY'));
}
$q->addWhere('
(contact_private=0
OR (contact_private=1 AND contact_owner=' . $this->_AppUI->user_id . ')
OR contact_owner IS NULL OR contact_owner = 0
)');
//TODO: We need to convert this from static to use ->overrideDatabase() for testing.
$company = new CCompany();
$allow_where = $company->getAllowedSQL($this->_AppUI->user_id, 'contact_company');
if (count($allow_where)) {
$q->addWhere('(contact_company = 0 OR contact_company IS NULL OR (' . implode(' AND ', $allow_where) . '))');
}
//TODO: We need to convert this from static to use ->overrideDatabase() for testing.
$department = new CDepartment();
$q = $department->setAllowedSQL($this->_AppUI->user_id, $q);
$q->addOrder('contact_first_name');
$q->addOrder('contact_last_name');
return $q->loadList();
}
echo $AppUI->_('Project Hours');
?>
:</td>
<td class="hilite" width="100%"><?php
echo $total_project_hours;
?>
</td>
</tr>
<?php
$q = new DBQuery();
$q->addTable('departments', 'a');
$q->addTable('project_departments', 'b');
$q->addQuery('a.dept_id, a.dept_name, a.dept_phone');
$q->addWhere('a.dept_id = b.department_id and b.project_id = ' . (int) $project_id);
$department = new CDepartment();
$department->setAllowedSQL($AppUI->user_id, $q);
$depts = $q->loadHashList('dept_id');
if (count($depts) > 0) {
?>
<tr>
<td><strong><?php
echo $AppUI->_('Departments');
?>
</strong></td>
</tr>
<tr>
<td colspan='3' class="hilite">
<?php
foreach ($depts as $dept_id => $dept_info) {
echo '<div>' . $dept_info['dept_name'];
if ($dept_info['dept_phone'] != '') {
public function departments($companyId)
{
if ($this->_AppUI->isActiveModule('departments') && canView('departments')) {
$q = $this->_getQuery();
$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();
$q = $department->setAllowedSQL($this->_AppUI->user_id, $q);
return $q->loadList();
}
}
public static function getDepartmentList($AppUI = null, $companyId, $departmentId = 0)
{
global $AppUI;
$q = new w2p_Database_Query();
$q->addTable('departments');
$q->addQuery('dept_id, dept_name');
if (is_int($departmentId)) {
$q->addWhere('dept_parent = ' . (int) $departmentId);
}
$q->addWhere('dept_company = ' . (int) $companyId);
$q->addOrder('dept_name');
$department = new CDepartment();
//TODO: We need to convert this from static to use ->overrideDatabase() for testing.
$department->setAllowedSQL($AppUI->user_id, $q);
return $q->loadHashList('dept_id');
}
public function getDepartmentList()
{
if ($this->_AppUI->isActiveModule('departments') && canView('departments')) {
$q = $this->_getQuery();
$q->addTable('departments', 'a');
$q->addTable('project_departments', 'b');
$q->addQuery('a.dept_id, a.dept_name, a.dept_phone');
$q->addWhere('a.dept_id = b.department_id and b.project_id = ' . (int) $this->project_id);
$department = new CDepartment();
$department->overrideDatabase($this->_query);
$department->setAllowedSQL($this->_AppUI->user_id, $q);
return $q->loadHashList('dept_id');
}
}
