function findchilddept(&$tarr, $parent, $level = 0)
{
    $level = $level + 1;
    $n = count($tarr);
    for ($x = 0; $x < $n; $x++) {
        if ($tarr[$x]["dept_parent"] == $parent && $tarr[$x]["dept_parent"] != $tarr[$x]["dept_id"]) {
            showchilddept($tarr[$x], $level);
            findchilddept($tarr, $tarr[$x]["dept_id"], $level);
        }
    }
}
function projects_list_data($user_id = false)
{
    global $AppUI, $addPwOiD, $cBuffer, $company, $company_id, $company_prefix, $deny, $department;
    global $dept_ids, $dPconfig, $orderby, $orderdir, $projects, $tasks_critical, $tasks_problems;
    global $tasks_sum, $tasks_summy, $tasks_total, $owner, $projectTypeId, $project_status;
    global $currentTabId;
    $addProjectsWithAssignedTasks = $AppUI->getState('addProjWithTasks') ? $AppUI->getState('addProjWithTasks') : 0;
    //for getting permissions on project records
    $obj_project = new CProject();
    // Let's delete temproary tables
    $q = new DBQuery();
    $table_list = array('tasks_sum', 'tasks_total', 'tasks_summy', 'tasks_critical', 'tasks_problems', 'tasks_users');
    $q->dropTemp($table_list);
    $q->exec();
    $q->clear();
    // Task sum table
    // by Pablo Roca (pabloroca@mvps.org)
    // 16 August 2003
    $working_hours = $dPconfig['daily_working_hours'] ? $dPconfig['daily_working_hours'] : 8;
    // GJB: Note that we have to special case duration type 24
    // and this refers to the hours in a day, NOT 24 hours
    $q->createTemp('tasks_sum');
    $q->addTable('tasks', 't');
    $q->addQuery('t.task_project, SUM(t.task_duration * t.task_percent_complete' . ' * IF(t.task_duration_type = 24, ' . $working_hours . ', t.task_duration_type)) / SUM(t.task_duration' . ' * IF(t.task_duration_type = 24, ' . $working_hours . ', t.task_duration_type)) AS project_percent_complete, SUM(t.task_duration' . ' * IF(t.task_duration_type = 24, ' . $working_hours . ', t.task_duration_type)) AS project_duration');
    if ($user_id) {
        $q->addJoin('user_tasks', 'ut', 'ut.task_id = t.task_id');
        $q->addWhere('ut.user_id = ' . $user_id);
    }
    $q->addWhere('t.task_id = t.task_parent');
    $q->addGroup('t.task_project');
    $tasks_sum = $q->exec();
    $q->clear();
    // At this stage tasks_sum contains the project id, and the total of tasks as percentage complate and project duration.
    // I.e. one record per project
    // Task total table
    $q->createTemp('tasks_total');
    $q->addTable('tasks', 't');
    $q->addQuery('t.task_project, COUNT(distinct t.task_id) AS total_tasks');
    if ($user_id) {
        $q->addJoin('user_tasks', 'ut', 'ut.task_id = t.task_id');
        $q->addWhere('ut.user_id = ' . $user_id);
    }
    $q->addGroup('t.task_project');
    $tasks_total = $q->exec();
    $q->clear();
    // tasks_total contains the total number of tasks for each project.
    // temporary My Tasks
    // by Pablo Roca (pabloroca@mvps.org)
    // 16 August 2003
    $q->createTemp('tasks_summy');
    $q->addTable('tasks', 't');
    $q->addQuery('t.task_project, COUNT(DISTINCT t.task_id) AS my_tasks');
    $q->addWhere('t.task_owner = ' . ($user_id ? $user_id : $AppUI->user_id));
    $q->addGroup('t.task_project');
    $tasks_summy = $q->exec();
    $q->clear();
    // tasks_summy contains total count of tasks for each project that I own.
    // temporary critical tasks
    $q->createTemp('tasks_critical');
    $q->addTable('tasks', 't');
    $q->addQuery('t.task_project, t.task_id AS critical_task' . ', MAX(t.task_end_date) AS project_actual_end_date');
    // MerlinYoda: we don't join tables if we don't get anything out of the process
    // $q->addJoin('projects', 'p', 'p.project_id = t.task_project');
    $q->addOrder('t.task_end_date DESC');
    $q->addGroup('t.task_project');
    $tasks_critical = $q->exec();
    $q->clear();
    // tasks_critical contains the latest ending task and its end date.
    // temporary task problem logs
    $q->createTemp('tasks_problems');
    $q->addTable('tasks', 't');
    $q->addQuery('t.task_project, tl.task_log_problem');
    $q->addJoin('task_log', 'tl', 'tl.task_log_task = t.task_id');
    $q->addWhere('tl.task_log_problem > 0');
    $q->addGroup('t.task_project');
    $tasks_problems = $q->exec();
    $q->clear();
    // tasks_problems contains an indication of any projects that have task logs set to problem.
    if ($addProjectsWithAssignedTasks) {
        // temporary users tasks
        $q->createTemp('tasks_users');
        $q->addTable('tasks', 't');
        $q->addQuery('t.task_project, ut.user_id');
        $q->addJoin('user_tasks', 'ut', 'ut.task_id = t.task_id');
        if ($user_id) {
            $q->addWhere('ut.user_id = ' . $user_id);
        }
        $q->addOrder('t.task_end_date DESC');
        $q->addGroup('t.task_project');
        $tasks_users = $q->exec();
        $q->clear();
    }
    // tasks_users contains all projects with tasks that have user assignments. (isn't this getting pointless?)
    // add Projects where the Project Owner is in the given department
    if ($addPwOiD && isset($department)) {
        $owner_ids = array();
        $q->addTable('users', 'u');
        $q->addQuery('u.user_id');
        $q->addJoin('contacts', 'c', 'c.contact_id = u.user_contact');
        $q->addWhere('c.contact_department = ' . $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[] = $department;
    }
    $q->clear();
    $q->addTable('projects', 'p');
    $q->addQuery('p.project_id, p.project_status, p.project_color_identifier, p.project_type' . ', p.project_name, p.project_description, p.project_start_date' . ', p.project_end_date, p.project_color_identifier, p.project_company' . ', p.project_status, p.project_priority, com.company_name' . ', com.company_description, tc.critical_task, tc.project_actual_end_date' . ', if (tp.task_log_problem IS NULL, 0, tp.task_log_problem) AS task_log_problem' . ', tt.total_tasks, tsy.my_tasks, ts.project_percent_complete' . ', ts.project_duration, u.user_username');
    $q->addJoin('companies', 'com', 'p.project_company = com.company_id');
    $q->addJoin('users', 'u', 'p.project_owner = u.user_id');
    $q->addJoin('tasks_critical', 'tc', 'p.project_id = tc.task_project');
    $q->addJoin('tasks_problems', 'tp', 'p.project_id = tp.task_project');
    $q->addJoin('tasks_sum', 'ts', 'p.project_id = ts.task_project');
    $q->addJoin('tasks_total', 'tt', 'p.project_id = tt.task_project');
    $q->addJoin('tasks_summy', 'tsy', 'p.project_id = tsy.task_project');
    if ($addProjectsWithAssignedTasks) {
        $q->addJoin('tasks_users', 'tu', 'p.project_id = tu.task_project');
    }
    if (isset($project_status) && $currentTabId != 500) {
        $q->addWhere('p.project_status = ' . $project_status);
    }
    if (isset($department)) {
        $q->addJoin('project_departments', 'pd', 'pd.project_id = p.project_id');
        if (!$addPwOiD) {
            $q->addWhere('pd.department_id in (' . implode(',', $dept_ids) . ')');
        } else {
            // Show Projects where the Project Owner is in the given department
            $q->addWhere('p.project_owner IN (' . (!empty($owner_ids) ? implode(',', $owner_ids) : 0) . ')');
        }
    } else {
        if ($company_id && !$addPwOiD) {
            $q->addWhere('p.project_company = ' . $company_id);
        }
    }
    if ($projectTypeId > -1) {
        $q->addWhere('p.project_type = ' . $projectTypeId);
    }
    if ($user_id && $addProjectsWithAssignedTasks) {
        $q->addWhere('(tu.user_id = ' . $user_id . ' OR p.project_owner = ' . $user_id . ')');
    } else {
        if ($user_id) {
            $q->addWhere('p.project_owner = ' . $user_id);
        }
    }
    if ($owner > 0) {
        $q->addWhere('p.project_owner = ' . $owner);
    }
    $q->addGroup('p.project_id');
    $q->addOrder($orderby . ' ' . $orderdir);
    $obj_project->setAllowedSQL($AppUI->user_id, $q, null, 'p');
    $projects = $q->loadList();
    // retrieve list of records
    // modified for speed
    // by Pablo Roca (pabloroca@mvps.org)
    // 16 August 2003
    // get the list of permitted companies
    $obj_company = new CCompany();
    $companies = $obj_company->getAllowedRecords($AppUI->user_id, 'company_id,company_name', 'company_name');
    if (count($companies) == 0) {
        $companies = array(0);
    }
    // get the list of permitted companies
    $companies = arrayMerge(array('0' => $AppUI->_('All')), $companies);
    //get list of all departments, filtered by the list of permitted companies.
    $q->clear();
    $q->addTable('companies', 'c');
    $q->addQuery('c.company_id, c.company_name, dep.*');
    $q->addJoin('departments', 'dep', 'c.company_id = dep.dept_company');
    $q->addJoin('projects', 'p', 'p.project_company = c.company_id');
    $q->addWhere('p.project_status NOT IN (1, 4, 5, 6, 7)');
    $q->addOrder('c.company_name, dep.dept_parent, dep.dept_name');
    $obj_company->setAllowedSQL($AppUI->user_id, $q);
    $active_companies = $q->loadList();
    $q->clear();
    $q->addTable('companies', 'c');
    $q->addQuery('c.company_id, c.company_name, dep.*');
    $q->addJoin('departments', 'dep', 'c.company_id = dep.dept_company');
    $q->addJoin('projects', 'p', 'p.project_company = c.company_id');
    $q->addOrder('c.company_name, dep.dept_parent, dep.dept_name');
    $obj_company->setAllowedSQL($AppUI->user_id, $q);
    $all_companies = $q->loadList();
    //display the select list
    $cBuffer = '<select name="department" onchange="javascript:document.pickCompany.submit()" class="text">';
    $cBuffer .= '<option value="company_0" style="font-weight:bold;">' . $AppUI->_('All') . '</option>' . "\n";
    $company = '';
    $active_company_ids = array();
    // Active companies first
    $cBuffer .= '<optgroup label="Active">';
    foreach ($active_companies as $row) {
        if ($row['dept_parent'] == 0) {
            if ($company != $row['company_id']) {
                $cBuffer .= '<option value="' . $AppUI->___($company_prefix . $row['company_id']) . '" style="font-weight:bold;"' . ($company_id == $row['company_id'] ? 'selected="selected"' : '') . '>' . $AppUI->___($row['company_name']) . '</option>' . "\n";
                $company = $row['company_id'];
                $active_company_ids[] = $company;
            }
            if ($row['dept_parent'] != null) {
                showchilddept($row);
                findchilddept($rows, $row['dept_id']);
            }
        }
    }
    $cBuffer .= '</optgroup>';
    // Inactive companies
    $cBuffer .= '<optgroup label="Inactive">';
    foreach ($all_companies as $row) {
        if ($row['dept_parent'] == 0 and !in_array($row['company_id'], $active_company_ids)) {
            if ($company != $row['company_id']) {
                $cBuffer .= '<option value="' . $AppUI->___($company_prefix . $row['company_id']) . '" style="font-weight:bold;"' . ($company_id == $row['company_id'] ? 'selected="selected"' : '') . '>' . $AppUI->___($row['company_name']) . '</option>' . "\n";
                $company = $row['company_id'];
            }
            if ($row['dept_parent'] != null) {
                showchilddept($row);
                findchilddept($rows, $row['dept_id']);
            }
        }
    }
    $cBuffer .= '</optgroup>';
    $cBuffer .= '</select>';
}
Beispiel #3
0
$q->addJoin('departments', 'dep', 'c.company_id = dep.dept_company');
$q->addOrder('c.company_name, dep.dept_parent, dep.dept_name');
$rows = $q->loadList();
//display the select list
$cBuffer = '<select name="department" onChange="document.companyFilter.submit()" class="text">';
$cBuffer .= '<option value="' . $company_prefix . '0" style="font-weight:bold;">' . $AppUI->_('All') . '</option>' . "\n";
$company = '';
foreach ($rows as $row) {
    if ($row['dept_parent'] == 0) {
        if ($company != $row['company_id']) {
            $cBuffer .= '<option value="' . $AppUI->___($company_prefix . $row['company_id']) . '" style="font-weight:bold;"' . ($company_id == $row['company_id'] ? 'selected="selected"' : '') . '>' . $AppUI->___($row['company_name']) . '</option>' . "\n";
            $company = $row['company_id'];
        }
        if ($row['dept_parent'] != null) {
            showchilddept($row);
            findchilddept($rows, $row['dept_id']);
        }
    }
}
$cBuffer .= '</select>';
$titleBlock->addCell('<form action="?m=tasks" method="post" name="companyFilter">' . "\n" . $cBuffer . "\n" . '</form>' . "\n");
$titleBlock->addCell();
if ($canEdit && $project_id) {
    $titleBlock->addCell('<input type="submit" class="button" value="' . $AppUI->_('new task') . '">', '', '<form action="?m=tasks&a=addedit&task_project=' . $project_id . '" method="post">', '</form>');
}
$titleBlock->show();
if (dPgetParam($_GET, 'inactive', '') == 'toggle') {
    $AppUI->setState('inactive', $AppUI->getState('inactive') == -1 ? 0 : -1);
}
$in = $AppUI->getState('inactive') == -1 ? '' : 'in';
// use a new title block (a new row) to prevent from oversized sites
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;
}
function projects_list_data($user_id = false)
{
    global $AppUI, $addPwOiD, $buffer, $company, $company_id, $company_prefix, $deny, $department, $dept_ids, $dPconfig, $orderby, $orderdir, $projects, $tasks_critical, $tasks_problems, $tasks_sum, $tasks_summy, $tasks_total, $owner;
    $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 DBQuery();
    $q->dropTemp('tasks_sum, tasks_total, tasks_summy, tasks_critical, tasks_problems, tasks_users');
    $q->exec();
    $q->clear();
    // Task sum table
    // by Pablo Roca (pabloroca@mvps.org)
    // 16 August 2003
    $working_hours = $dPconfig['daily_working_hours'] ? $dPconfig['daily_working_hours'] : 8;
    // GJB: Note that we have to special case duration type 24 and this refers to the hours in a day, NOT 24 hours
    $q->createTemp('tasks_sum');
    $q->addTable('tasks');
    $q->addQuery("task_project, 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, SUM(task_duration * IF(task_duration_type = 24," . " {$working_hours}, task_duration_type)) AS project_duration");
    if ($user_id) {
        $q->addJoin('user_tasks', 'ut', 'ut.task_id = tasks.task_id');
        $q->addWhere('ut.user_id = ' . $user_id);
    }
    $q->addWhere("tasks.task_id = tasks.task_parent");
    $q->addGroup('task_project');
    $tasks_sum = $q->exec();
    $q->clear();
    // Task total table
    $q->createTemp('tasks_total');
    $q->addTable('tasks');
    $q->addQuery("task_project, COUNT(distinct tasks.task_id) AS total_tasks");
    if ($user_id) {
        $q->addJoin('user_tasks', 'ut', 'ut.task_id = tasks.task_id');
        $q->addWhere('ut.user_id = ' . $user_id);
    }
    $q->addGroup('task_project');
    $tasks_total = $q->exec();
    $q->clear();
    // temporary My Tasks
    // by Pablo Roca (pabloroca@mvps.org)
    // 16 August 2003
    $q->createTemp('tasks_summy');
    $q->addTable('tasks');
    $q->addQuery('task_project, COUNT(distinct task_id) AS my_tasks');
    if ($user_id) {
        $q->addWhere('task_owner = ' . $user_id);
    } else {
        $q->addWhere('task_owner = ' . $AppUI->user_id);
    }
    $q->addGroup('task_project');
    $tasks_summy = $q->exec();
    $q->clear();
    // temporary critical tasks
    $q->createTemp('tasks_critical');
    $q->addTable('tasks');
    $q->addQuery('task_project, task_id AS critical_task, MAX(task_end_date) AS project_actual_end_date');
    $q->addJoin('projects', 'p', 'p.project_id = task_project');
    $q->addOrder("task_end_date DESC");
    $q->addGroup('task_project');
    $tasks_critical = $q->exec();
    $q->clear();
    // temporary task problem logs
    $q->createTemp('tasks_problems');
    $q->addTable('tasks');
    $q->addQuery('task_project, task_log_problem');
    $q->addJoin('task_log', 'tl', 'tl.task_log_task = task_id');
    $q->addWhere("task_log_problem > '0'");
    $q->addGroup('task_project');
    $tasks_problems = $q->exec();
    $q->clear();
    if ($addProjectsWithAssignedTasks) {
        // temporary users tasks
        $q->createTemp('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 = ' . $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');
        $q->addWhere('c.contact_department = ' . $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[] = $department;
    }
    $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, 'company_id,company_name', 'company_name');
    if (count($companies) == 0) {
        $companies = array(0);
    }
    $q->addTable('projects');
    $q->addQuery('projects.project_id, project_status, project_color_identifier, project_name, project_description, project_duration,
		project_start_date, project_end_date, project_color_identifier, project_company, company_name, company_description, project_status,
		project_priority, tc.critical_task, tc.project_actual_end_date, tp.task_log_problem, tt.total_tasks, tsy.my_tasks,
		ts.project_percent_complete, user_username');
    $q->addJoin('companies', 'com', 'projects.project_company = company_id');
    $q->addJoin('users', 'u', 'projects.project_owner = u.user_id');
    $q->addJoin('tasks_critical', 'tc', 'projects.project_id = tc.task_project');
    $q->addJoin('tasks_problems', 'tp', 'projects.project_id = tp.task_project');
    $q->addJoin('tasks_sum', 'ts', 'projects.project_id = ts.task_project');
    $q->addJoin('tasks_total', 'tt', 'projects.project_id = tt.task_project');
    $q->addJoin('tasks_summy', 'tsy', 'projects.project_id = tsy.task_project');
    if ($addProjectsWithAssignedTasks) {
        $q->addJoin('tasks_users', 'tu', 'projects.project_id = tu.task_project');
    }
    // DO we have to include the above DENY WHERE restriction, too?
    //$q->addJoin('', '', '');
    if (isset($department)) {
        $q->addJoin('project_departments', 'pd', 'pd.project_id = projects.project_id');
    }
    if (!isset($department) && $company_id && !$addPwOiD) {
        $q->addWhere("projects.project_company = '{$company_id}'");
    }
    if (isset($department) && !$addPwOiD) {
        $q->addWhere("pd.department_id in ( " . implode(',', $dept_ids) . " )");
    }
    if ($user_id && $addProjectsWithAssignedTasks) {
        $q->addWhere('(tu.user_id = ' . $user_id . ' OR projects.project_owner = ' . $user_id . ' )');
    } elseif ($user_id) {
        $q->addWhere('projects.project_owner = ' . $user_id);
    }
    if ($owner > 0) {
        $q->addWhere('projects.project_owner = ' . $owner);
    }
    // Show Projects where the Project Owner is in the given department
    if ($addPwOiD && !empty($owner_ids)) {
        $q->addWhere('projects.project_owner IN (' . implode(',', $owner_ids) . ')');
    }
    $q->addGroup('projects.project_id');
    $q->addOrder("{$orderby} {$orderdir}");
    $obj->setAllowedSQL($AppUI->user_id, $q);
    $projects = $q->loadList();
    // get the list of permitted companies
    $companies = arrayMerge(array('0' => $AppUI->_('All')), $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);
    $rows = $q->loadList();
    //display the select list
    $buffer = '<select name="department" onChange="document.pickCompany.submit()" class="text">';
    $buffer .= '<option value="company_0" style="font-weight:bold;">' . $AppUI->_('All') . '</option>' . "\n";
    $company = '';
    foreach ($rows as $row) {
        if ($row["dept_parent"] == 0) {
            if ($company != $row['company_id']) {
                $buffer .= '<option value="' . $company_prefix . $row['company_id'] . '" style="font-weight:bold;"' . ($company_id == $row['company_id'] ? 'selected="selected"' : '') . '>' . $row['company_name'] . '</option>' . "\n";
                $company = $row['company_id'];
            }
            if ($row["dept_parent"] != null) {
                showchilddept($row);
                findchilddept($rows, $row["dept_id"]);
            }
        }
    }
    $buffer .= '</select>';
}
Beispiel #6
0
    ?>
<th nowrap="nowrap">
                <a href="?m=departments&orderby=<?php 
    echo $fieldList[$index];
    ?>
" class="hdr">
                    <?php 
    echo $AppUI->_($fieldNames[$index]);
    ?>
                </a>
            </th><?php 
}
?>
	</tr>
<?php 
if (count($deptList) > 0) {
    $displayList = array();
    foreach ($deptList as $dept) {
        if ($dept['dept_parent'] == 0) {
            findchilddept($deptList, $dept['dept_id']);
        }
        echo '<tr><td>' . (mb_trim($dept['dept_desc']) ? w2PtoolTip($dept['dept_name'], $dept['dept_desc']) : '') . '<a href="./index.php?m=departments&a=view&dept_id=' . $dept['dept_id'] . '" >' . $dept['dept_name'] . '</a>' . (mb_trim($dept['dept_desc']) ? w2PendTip() : '') . '</td>';
        echo '<td width="125" align="right" nowrap="nowrap">' . $dept['countp'] . '</td>';
        echo '<td width="125" align="right" nowrap="nowrap">' . $dept['inactive'] . '</td>';
        echo '<td align="left" nowrap="nowrap">' . $AppUI->_($types[$dept['dept_type']]) . '</td></tr>';
    }
} else {
    echo '<tr><td colspan="4">' . $AppUI->_('No data available') . '</td></tr>';
}
?>
</table>
Beispiel #7
0
function projects_list_data($user_id = false)
{
    global $AppUI, $addPwOiD, $buffer, $company, $company_id, $company_prefix, $deny, $department, $dept_ids, $w2Pconfig, $orderby, $orderdir, $projects, $tasks_critical, $tasks_problems, $tasks_sum, $tasks_summy, $tasks_total, $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 DBQuery();
    // Let's delete support tables data
    $q->setDelete('tasks_sum');
    $q->exec();
    $q->clear();
    //BEGIN: Deprecated in v2.0
    $q->setDelete('tasks_total');
    $q->exec();
    $q->clear();
    //END: Deprecated in v2.0
    $q->setDelete('tasks_summy');
    $q->exec();
    $q->clear();
    $q->setDelete('tasks_critical');
    $q->exec();
    $q->clear();
    $q->setDelete('tasks_problems');
    $q->exec();
    $q->clear();
    $q->setDelete('tasks_users');
    $q->exec();
    $q->clear();
    // support task sum table
    // by Pablo Roca (pabloroca@mvps.org)
    // 16 August 2003
    $working_hours = $w2Pconfig['daily_working_hours'] ? $w2Pconfig['daily_working_hours'] : 8;
    // GJB: Note that we have to special case duration type 24 and this refers to the hours in a day, NOT 24 hours
    $q->addInsertSelect('tasks_sum');
    $q->addTable('tasks');
    $q->addQuery('task_project, COUNT(distinct tasks.task_id) AS total_tasks');
    $q->addQuery('-1 AS project_percent_complete');
    $q->addQuery('SUM(task_duration * IF(task_duration_type = 24, ' . $working_hours . ', task_duration_type)) AS project_duration');
    if ($user_id) {
        $q->addJoin('user_tasks', 'ut', 'ut.task_id = tasks.task_id');
        $q->addWhere('ut.user_id = ' . (int) $user_id);
    }
    $q->addWhere('tasks.task_id = tasks.task_parent');
    $q->addGroup('task_project');
    $tasks_sum = $q->exec();
    $q->clear();
    //BEGIN: Deprecated in v2.0
    // support task total table
    $q->addInsertSelect('tasks_total');
    $q->addTable('tasks');
    $q->addQuery('task_project, COUNT(distinct tasks.task_id) AS total_tasks');
    if ($user_id) {
        $q->addJoin('user_tasks', 'ut', 'ut.task_id = tasks.task_id');
        $q->addWhere('ut.user_id = ' . (int) $user_id);
    }
    $q->addGroup('task_project');
    $tasks_total = $q->exec();
    $q->clear();
    //END: Deprecated in v2.0
    // support My Tasks
    $q->addInsertSelect('tasks_summy');
    $q->addTable('tasks');
    $q->addQuery('task_project, COUNT(distinct task_id) AS my_tasks');
    if ($user_id) {
        $q->addWhere('task_owner = ' . (int) $user_id);
    } else {
        $q->addWhere('task_owner = ' . (int) $AppUI->user_id);
    }
    $q->addGroup('task_project');
    $tasks_summy = $q->exec();
    $q->clear();
    // support critical tasks
    $q->addInsertSelect('tasks_critical');
    $q->addTable('tasks', 't');
    $q->addQuery('task_project, task_id AS critical_task, task_end_date AS project_actual_end_date');
    $sq = new DBQuery();
    $sq->addTable('tasks', 'st');
    $sq->addQuery('MAX(task_end_date)');
    $sq->addWhere('st.task_project = t.task_project');
    $q->addWhere('task_end_date = (' . $sq->prepare() . ')');
    $q->addGroup('task_project');
    $tasks_critical = $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_duration, project_parent, project_original_parent,
		project_start_date, project_end_date, project_color_identifier, project_company, company_name, company_description, project_status,
		project_priority, tc.critical_task, tc.project_actual_end_date, tp.task_log_problem, pr.project_task_count, tsy.my_tasks,
		pr.project_percent_complete, user_username, project_active');
    $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_critical', 'tc', 'pr.project_id = tc.task_project');
    $q->addJoin('tasks_problems', 'tp', 'pr.project_id = tp.task_project');
    $q->addJoin('tasks_sum', 'ts', 'pr.project_id = ts.task_project');
    $q->addJoin('tasks_summy', 'tsy', 'pr.project_id = tsy.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) . ')');
    }
    $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) {
                        showchilddept($row);
                        findchilddept($rows, $row['dept_id']);
                    }
                }
            }
        }
    }
    $buffer .= '</select>';
}