public function remove()
{
$q = new w2p_Database_Query();
$q->dropTable('resources');
$q->exec();
$q->clear();
$q->dropTable('resource_tasks');
$q->exec();
$q->clear();
$q->dropTable('resource_types');
$q->exec();
return null;
}
public function store($object_id)
{
global $db;
$object_id = (int) $object_id;
if ($object_id) {
$this->value_intvalue = (int) $this->value_intvalue;
$ins_charvalue = $this->value_charvalue == null ? '' : stripslashes($this->value_charvalue);
$q = new w2p_Database_Query();
$q->addTable('custom_fields_values');
if ($this->value_id) {
$q->addUpdate('value_charvalue', $ins_charvalue);
$q->addUpdate('value_intvalue', $this->value_intvalue);
$q->addWhere('value_id = ' . $this->value_id);
} else {
$q->addInsert('value_module', '');
$q->addInsert('value_field_id', $this->field_id);
$q->addInsert('value_object_id', $object_id);
$q->addInsert('value_charvalue', $ins_charvalue);
$q->addInsert('value_intvalue', $this->value_intvalue);
}
$rs = $q->exec();
$q->clear();
if (!$rs) {
return $db->ErrorMsg() . ' | SQL: ';
}
} else {
return 'Error: Cannot store field (' . $this->field_name . '), associated id not supplied.';
}
}
public function bind($hash)
{
if (!is_array($hash)) {
return get_class($this) . "::bind failed";
} else {
$q = new w2p_Database_Query();
$q->bindHashToObject($hash, $this);
$q->clear();
return null;
}
}
public function authenticate($username, $password)
{
global $db, $AppUI;
$this->username = $username;
$q = new w2p_Database_Query();
$q->addTable('users');
$q->addQuery('user_id, user_password');
$q->addWhere('user_username = \'' . $username . '\'');
if (!($rs = $q->exec())) {
$q->clear();
return false;
}
if (!($row = $q->fetchRow())) {
$q->clear();
return false;
}
$this->user_id = $row['user_id'];
$q->clear();
if (MD5($password) == $row['user_password']) {
return true;
}
return false;
}
public function load()
{
global $db;
$q = new w2p_Database_Query();
$q->addTable('custom_fields_lists');
$q->addWhere('field_id = ' . $this->field_id);
$q->addOrder('list_value');
if (!($rs = $q->exec())) {
$q->clear();
return $db->ErrorMsg();
}
while ($opt_row = $q->fetchRow()) {
$this->options[$opt_row['list_option_id']] = $opt_row['list_value'];
}
}
public function canDelete($msg, $oid = 0, $joins = null)
{
$msg = array();
$q = new w2p_Database_Query();
$q->addTable('file_folders');
$q->addQuery('COUNT(DISTINCT file_folder_id) AS num_of_subfolders');
$q->addWhere('file_folder_parent=' . $oid);
$res1 = $q->loadResult();
if ($res1) {
$msg[] = "Can't delete folder, it has subfolders.";
//') . ': ' . implode(', ', $msg);
}
$q->clear();
$q = new w2p_Database_Query();
$q->addTable('files');
$q->addQuery('COUNT(DISTINCT file_id) AS num_of_files');
$q->addWhere('file_folder=' . $oid);
$res2 = $q->loadResult();
if ($res2) {
$msg[] = "Can't delete folder, it has files within it.";
//') . ': ' . implode(', ', $msg);
}
return $msg;
}
$q->addQuery('usernames.user_username, usernames.user_id');
$q->addQuery('assignees.user_username as assignee_username');
$q->addQuery('count(distinct assignees.user_id) as assignee_count');
$q->addQuery('co.contact_first_name, co.contact_last_name');
$q->addQuery('CONCAT(co.contact_first_name,\' \', co.contact_last_name) AS owner');
$q->addQuery('task_milestone');
$q->addQuery('count(distinct f.file_task) as file_count');
$q->addQuery('tlog.task_log_problem');
$q->addQuery('task_access');
//subquery the parent state
$sq = new w2p_Database_Query();
$sq->addTable('tasks', 'stasks');
$sq->addQuery('COUNT(stasks.task_id)');
$sq->addWhere('stasks.task_id <> tasks.task_id AND stasks.task_parent = tasks.task_id');
$subquery = $sq->prepare();
$sq->clear();
$q->addQuery('(' . $subquery . ') AS task_nr_of_children');
$q->addTable('tasks');
$mods = $AppUI->getActiveModules();
if (!empty($mods['history']) && canView('history')) {
$q->addQuery('MAX(history_date) as last_update');
$q->leftJoin('history', 'h', 'history_item = tasks.task_id AND history_table=\'tasks\'');
}
$q->addJoin('projects', 'p', 'p.project_id = task_project', 'inner');
$q->leftJoin('users', 'usernames', 'task_owner = usernames.user_id');
$q->leftJoin('user_tasks', 'ut', 'ut.task_id = tasks.task_id');
$q->leftJoin('users', 'assignees', 'assignees.user_id = ut.user_id');
$q->leftJoin('contacts', 'co', 'co.contact_id = usernames.user_contact');
$q->leftJoin('task_log', 'tlog', 'tlog.task_log_task = tasks.task_id AND tlog.task_log_problem > 0');
$q->leftJoin('files', 'f', 'tasks.task_id = f.file_task');
$q->leftJoin('project_departments', 'project_departments', 'p.project_id = project_departments.project_id OR project_departments.project_id IS NULL');
public function indexStrings()
{
global $w2Pconfig;
$nwords_indexed = 0;
/* Workaround for indexing large files:
** Based on the value defined in config data,
** files with file_size greater than specified limit
** are not indexed for searching.
** Negative value :<=> no filesize limit
*/
$index_max_file_size = w2PgetConfig('index_max_file_size', 0);
if ($this->file_size > 0 && ($index_max_file_size < 0 || (int) $this->file_size <= $index_max_file_size * 1024)) {
// get the parser application
$parser = $w2Pconfig['parser_' . $this->file_type];
if (!$parser) {
$parser = $w2Pconfig['parser_default'];
}
if (!$parser) {
return false;
}
// buffer the file
$this->_filepath = W2P_BASE_DIR . '/files/' . $this->file_project . '/' . $this->file_real_filename;
if (file_exists($this->_filepath)) {
$fp = fopen($this->_filepath, 'rb');
$x = fread($fp, $this->file_size);
fclose($fp);
// parse it
$parser = $parser . ' ' . $this->_filepath;
$pos = strpos($parser, '/pdf');
/*
* TODO: I *really* hate using error surpression here and I would
* normally just detect if safe_mode is on and if it was, skip
* this call. Unfortunately, safe_mode has been deprecated in
* 5.3 and will be removed in 5.4
*/
if (false !== $pos) {
$x = @shell_exec(`{$parser} -`);
} else {
$x = @shell_exec(`{$parser}`);
}
// if nothing, return
if (strlen($x) < 1) {
return 0;
}
// remove punctuation and parse the strings
$x = str_replace(array('.', ',', '!', '@', '(', ')'), ' ', $x);
$warr = explode(' ', $x);
$wordarr = array();
$nwords = count($warr);
for ($x = 0; $x < $nwords; $x++) {
$newword = $warr[$x];
if (!preg_match('[!"#$%&\'()*+,\\-./:;<=>?@[\\\\]^_`{|}~]', $newword) && mb_strlen(mb_trim($newword)) > 2 && !preg_match('[0-9]', $newword)) {
$wordarr[$newword] = $x;
}
}
// filter out common strings
$ignore = w2PgetSysVal('FileIndexIgnoreWords');
$ignore = str_replace(' ,', ',', $ignore);
$ignore = str_replace(', ', ',', $ignore);
$ignore = explode(',', $ignore);
foreach ($ignore as $w) {
unset($wordarr[$w]);
}
$nwords_indexed = count($wordarr);
// insert the strings into the table
while (list($key, $val) = each($wordarr)) {
$q = new w2p_Database_Query();
$q->addTable('files_index');
$q->addReplace('file_id', $this->file_id);
$q->addReplace('word', $key);
$q->addReplace('word_placement', $val);
$q->exec();
$q->clear();
}
} else {
//TODO: if the file doesn't exist.. should we delete the db record?
}
}
$q = new w2p_Database_Query();
$q->addTable('files');
$q->addUpdate('file_indexed', 1);
$q->addWhere('file_id = ' . $this->file_id);
$q->exec();
return $nwords_indexed;
}
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;
}
public function hook_cron()
{
if (w2PgetConfig('system_update_check', true)) {
$lastCheck = w2PgetConfig('system_update_last_check', '');
$nowDate = new DateTime("now");
if ('' == $lastCheck) {
$checkForUpdates = true;
} else {
$systemDate = new DateTime($lastCheck);
$difference = 0;
//$nowDate->diff($systemDate)->format('%d');
$checkForUpdates = $difference >= 7 ? true : false;
}
if ($checkForUpdates) {
$AppUI = new w2p_Core_CAppUI();
$configList = array();
$moduleList = $AppUI->getLoadableModuleList();
foreach ($moduleList as $module) {
$configList[$module['mod_directory']] = $module['mod_version'];
}
$configList['w2p_ver'] = $AppUI->getVersion();
$configList['php_ver'] = PHP_VERSION;
$configList['database'] = $this->_w2Pconfig['dbtype'];
$configList['server'] = $_SERVER['SERVER_SOFTWARE'];
$configList['connector'] = php_sapi_name();
$configList['database_ver'] = mysql_get_client_info();
$libraries = array('tidy', 'json', 'libxml', 'mysql');
foreach ($libraries as $library) {
$configList[$library . '_extver'] = phpversion($library);
}
if (function_exists('gd_info')) {
$lib_version = gd_info();
$configList['gd_extver'] = $lib_version['GD Version'];
}
if (function_exists('curl_version')) {
$lib_version = curl_version();
$configList['curl_extver'] = $lib_version['version'];
}
$request = new w2p_Utilities_HTTPRequest('http://stats.web2project.net');
$request->addParameters($configList);
$result = $request->processRequest();
$data = json_decode($result);
$q = new w2p_Database_Query();
$q->addTable('config');
if ('' == w2PgetConfig('available_version', '')) {
$q->addInsert('config_name', 'available_version');
$q->addInsert('config_value', $data->w2p_ver);
$q->addInsert('config_group', 'admin_system');
$q->addInsert('config_type', 'text');
} else {
$q->addUpdate('config_value', $data->w2p_ver);
$q->addWhere("config_name = 'available_version'");
}
$q->exec();
$q->clear();
$q->addTable('config');
$q->addUpdate('config_value', date('Y-m-d H:i:s'));
$q->addWhere("config_name = 'system_update_last_check'");
$q->exec();
}
}
}
public function setContactMethods(array $methods)
{
$q = new w2p_Database_Query();
$q->setDelete('contacts_methods');
$q->addWhere('contact_id=' . (int) $this->contact_id);
$q->exec();
$q->clear();
if (!empty($methods)) {
$q = new w2p_Database_Query();
$q->addTable('contacts_methods');
$q->addInsert('contact_id', (int) $this->contact_id);
foreach ($methods as $name => $value) {
if (!empty($value)) {
$q->addInsert('method_name', $name);
$q->addInsert('method_value', $value);
$q->exec();
}
}
$q->clear();
}
}
public function delete()
{
$q = new w2p_Database_Query();
$q->setDelete('user_preferences');
$q->addWhere('pref_user = '******'pref_name = \'' . $this->pref_name . '\'');
if (!$q->exec()) {
$q->clear();
return db_error();
} else {
$q->clear();
return null;
}
}
public function createsqluser($username, $password, $email, $first, $last)
{
global $db, $AppUI;
$c = new CContact();
$c->contact_first_name = $first;
$c->contact_last_name = $last;
$c->contact_order_by = $first . ' ' . $last;
$c->contact_email = $email;
$c->store();
$q = new w2p_Database_Query();
$q->addTable('users');
$q->addInsert('user_username', $username);
$q->addInsert('user_password', $password);
$q->addInsert('user_type', '1');
$q->addInsert('user_contact', $c->contact_id);
if (!$q->exec()) {
die($AppUI->_('Failed to create user credentials'));
}
$user_id = $db->Insert_ID();
$this->user_id = $user_id;
$q->clear();
$acl =& $AppUI->acl();
$acl->insertUserRole($acl->get_group_id('anon'), $this->user_id);
}
/**
* @param $module
* @param $mod_data
*
* @return Value
*/
function __extract_from_role_perms($module, $mod_data)
{
$q = new w2p_Database_Query();
$q->addTable($module['permissions_item_table']);
$q->addQuery($module['permissions_item_label']);
$q->addWhere($module['permissions_item_field'] . '=' . $mod_data['name']);
$data = $q->loadResult();
$q->clear();
return $data;
}
/**
*
* @param w2p_Core_CAppUI $AppUI
* @param CProject $project_id
*
* The point of this function is to create/update a task to represent a
* subproject.
*
*/
public static function storeTokenTask(w2p_Core_CAppUI $AppUI, $project_id)
{
$subProject = new CProject();
//TODO: We need to convert this from static to use ->overrideDatabase() for testing.
$subProject->load($project_id);
if ($subProject->project_parent > 0 && $subProject->project_id != $subProject->project_parent) {
$q = new w2p_Database_Query();
$q->addTable('tasks');
$q->addQuery('MIN(task_start_date) AS min_task_start_date');
$q->addQuery('MAX(task_end_date) AS max_task_end_date');
$q->addWhere('task_project = ' . $subProject->project_id);
$q->addWhere('task_status <> -1');
$projectDates = $q->loadList();
$q->clear();
$q->addTable('tasks');
$q->addQuery('task_id');
$q->addWhere('task_represents_project = ' . $subProject->project_id);
$task_id = (int) $q->loadResult();
$task = new CTask();
//TODO: We need to convert this from static to use ->overrideDatabase() for testing.
if ($task_id) {
$task->load($task_id);
} else {
$task->task_description = $task->task_name;
$task->task_priority = $subProject->project_priority;
$task->task_project = $subProject->project_parent;
$task->task_represents_project = $subProject->project_id;
$task->task_owner = $AppUI->user_id;
}
$task->task_name = $AppUI->_('Subproject') . ': ' . $subProject->project_name;
$task->task_duration_type = 1;
$task->task_duration = $subProject->project_scheduled_hours;
$task->task_start_date = $projectDates[0]['min_task_start_date'];
$task->task_end_date = $projectDates[0]['max_task_end_date'];
$task->task_percent_complete = $subProject->project_percent_complete;
$task->store();
//TODO: we should do something with this store result?
}
}
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;
}
/**
* 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;
}
public function deleteField($field_id)
{
global $db;
$q = new w2p_Database_Query();
$q->setDelete('custom_fields_struct');
$q->addWhere('field_id = ' . $field_id);
if (!$q->exec()) {
$q->clear();
return $db->ErrorMsg();
}
}
public function userId($username)
{
global $db;
$q = new w2p_Database_Query();
$q->addTable('users');
$q->addWhere('user_username = \'' . $username . '\'');
$rs = $q->exec();
$row = $rs->FetchRow();
$q->clear();
return $row['user_id'];
}
public function upgrade($old_version)
{
switch ($old_version) {
case '0.1':
// There is no way to change the name of database field with w2p_Database_Query().
db_exec("ALTER TABLE holiday CHANGE holiday_white holiday_type int(10) NOT NULL DEFAULT '0'");
if (db_error()) {
return false;
}
$q = new w2p_Database_Query();
$q->alterTable('holiday');
$q->createDefinition('index holiday_start_end_date (holiday_start_date, holiday_end_date)');
$q->exec();
$q->clear();
$q->alterTable('holiday');
$q->createDefinition('index holiday_start_end_date (holiday_start_date, holiday_end_date)');
$q->exec();
$q->clear();
$q->alterTable('holiday');
$q->createDefinition('index holiday_user (holiday_user)');
$q->exec();
$q->clear();
$q->alterTable('holiday');
$q->createDefinition('index holiday_type (holiday_type)');
$q->exec();
$q->clear();
default:
}
return true;
}
/**
* Post Request Handler
*
* This method is called when a request is a POST
*
* @return array
*/
public function executePost()
{
/**
* @todo Remove this once we figure out how to reference vars in file
* that is autoloaded
*/
global $tracking_dynamics;
$valid = $this->hasRequiredParameters($this->requiredParams);
if ($valid instanceof Frapi_Error) {
return $valid;
}
$username = $this->getParam('username');
$password = $this->getParam('password');
$project_id = $this->getParam('project_id', self::TYPE_INT);
$hassign = $this->getParam('hassign');
$hdependencies = $this->getParam('hdependencies');
$notify = $this->getParam('task_notify');
$comment = $this->getParam('email_comment');
$task_id = $this->getParam('task_id');
$adjustStartDate = $this->getParam('set_task_start_date');
$task = new CTask();
// Attempt to login as user, a little bit of a hack as we currently
// require the $_POST['login'] var to be set as well as a global AppUI
$AppUI = new CAppUI();
$GLOBALS['AppUI'] = $AppUI;
$_POST['login'] = '******';
if (!$AppUI->login($username, $password)) {
throw new Frapi_Error('INVALID_LOGIN');
}
$post_data = array('task_id' => $this->getParam('task_id'), 'task_name' => $this->getParam('task_name'), 'task_status' => $this->getParam('task_status'), 'task_percent_complete' => $this->getParam('task_percent_complete'), 'task_milestone' => $this->getParam('task_milestone'), 'task_owner' => $this->getParam('task_owner'), 'task_access' => $this->getParam('task_access'), 'task_related_url' => $this->getParam('task_related_url'), 'task_parent' => $this->getParam('task_parent'), 'task_type' => $this->getParam('task_type'), 'task_target_budget' => $this->getParam('task_target_budget'), 'task_description' => $this->getParam('task_description'), 'task_start_date' => $this->getParam('task_start_date'), 'task_end_date' => $this->getParam('task_end_date'), 'task_duration' => $this->getParam('task_duration'), 'task_duration_type' => $this->getParam('task_duration_type'), 'task_dynamic' => $this->getParam('task_dynamic'), 'task_allow_other_user_tasklogs' => $this->getParam('task_allow_other_user_tasklogs'), 'task_project' => $this->getParam('task_project'), 'task_priority' => $this->getParam('task_priority'));
// Include any files for handling module-specific requirements
foreach (findTabModules('tasks', 'addedit') as $mod) {
$fname = W2P_BASE_DIR . '/modules/' . $mod . '/tasks_dosql.addedit.php';
if (file_exists($fname)) {
require_once $fname;
}
}
// Find the task if we are set
$task_end_date = null;
if ($task_id) {
$task->load($task_id);
$task_end_date = new w2p_Utilities_Date($task->task_end_date);
}
$task = new CTask();
if (!$task->bind($post_data)) {
throw new Frapi_Error('SAVE_ERROR', $task->getError());
}
if ($task->task_dynamic != 1) {
$task_dynamic_delay = $this->getParam('task_dynamic_nodelay') ? $this->getParam('task_dynamic_nodelay') : '0';
if (in_array($task->task_dynamic, $tracking_dynamics)) {
$task->task_dynamic = $task_dynamic_delay ? 21 : 31;
} else {
$task->task_dynamic = $task_dynamic_delay ? 11 : 0;
}
}
// Let's check if task_dynamic is unchecked
if (!$this->getParam('task_dynamic')) {
$task->task_dynamic = false;
}
// Make sure task milestone is set or reset as appropriate
if ($this->getParam('task_milestone')) {
$task->task_milestone = false;
}
//format hperc_assign user_id=percentage_assignment;user_id=percentage_assignment;user_id=percentage_assignment;
$tmp_ar = explode(';', $this->getParam('hperc_assign'));
$i_cmp = sizeof($tmp_ar);
$hperc_assign_ar = array();
for ($i = 0; $i < $i_cmp; $i++) {
$tmp = explode('=', $tmp_ar[$i]);
if (count($tmp) > 1) {
$hperc_assign_ar[$tmp[0]] = $tmp[1];
} elseif ($tmp[0] != '') {
$hperc_assign_ar[$tmp[0]] = 100;
}
}
// let's check if there are some assigned departments to task
$task->task_departments = implode(',', $this->getParam('dept_ids', self::TYPE_ARRAY));
// convert dates to SQL format first
if ($task->task_start_date) {
$date = new w2p_Utilities_Date($task->task_start_date);
$task->task_start_date = $date->format(FMT_DATETIME_MYSQL);
}
$end_date = null;
if ($task->task_end_date) {
if (strpos($task->task_end_date, '2400') !== false) {
$task->task_end_date = str_replace('2400', '2359', $task->task_end_date);
}
$end_date = new w2p_Utilities_Date($task->task_end_date);
$task->task_end_date = $end_date->format(FMT_DATETIME_MYSQL);
}
$error_array = $task->store($AppUI);
// Return all the validation messages
if ($error_array !== true) {
$error_message = '';
foreach ($error_array as $error) {
$error_message .= $error . '. ';
}
throw new Frapi_Error('SAVE_ERROR', $error_message);
}
$task_parent = $this->getParam('task_parent') ? $this->getParam('task_parent', SELF::TYPE_INT) : 0;
$old_task_parent = $this->getParam('old_task_parent') ? $this->getParam('old_task_parent', SELF::TYPE_INT) : 0;
if ($task_parent != $old_task_parent) {
$oldTask = new CTask();
$oldTask->load($old_task_parent);
$oldTask->updateDynamics(false);
}
// How to handle custom fields? Do we support it in api?
// Now add any task reminders
// If there wasn't a task, but there is one now, and
// that task date is set, we need to set a reminder.
if (empty($task_end_date) || !empty($end_date) && $task_end_date->dateDiff($end_date)) {
$task->addReminder();
}
if (isset($hassign)) {
$task->updateAssigned($hassign, $hperc_assign_ar);
}
if (isset($hdependencies)) {
// && !empty($hdependencies)) {
// there are dependencies set!
// backup initial start and end dates
$tsd = new w2p_Utilities_Date($task->task_start_date);
$ted = new w2p_Utilities_Date($task->task_end_date);
// updating the table recording the
// dependency relations with this task
$task->updateDependencies($hdependencies, $task_parent);
// we will reset the task's start date based upon dependencies
// and shift the end date appropriately
if ($adjustStartDate && !is_null($hdependencies)) {
// load already stored task data for this task
$tempTask = new CTask();
$tempTask->load($task->task_id);
// shift new start date to the last dependency end date
$nsd = new w2p_Utilities_Date($tempTask->get_deps_max_end_date($tempTask));
// prefer Wed 8:00 over Tue 16:00 as start date
$nsd = $nsd->next_working_day();
// prepare the creation of the end date
$ned = new w2p_Utilities_Date();
$ned->copy($nsd);
if (empty($task->task_start_date)) {
// appropriately calculated end date via start+duration
$ned->addDuration($task->task_duration, $task->task_duration_type);
} else {
// calc task time span start - end
$d = $tsd->calcDuration($ted);
// Re-add (keep) task time span for end date.
// This is independent from $obj->task_duration.
// The value returned by Date::Duration() is always in hours ('1')
$ned->addDuration($d, '1');
}
// prefer tue 16:00 over wed 8:00 as an end date
$ned = $ned->prev_working_day();
$task->task_start_date = $nsd->format(FMT_DATETIME_MYSQL);
$task->task_end_date = $ned->format(FMT_DATETIME_MYSQL);
$q = new w2p_Database_Query();
$q->addTable('tasks', 't');
$q->addUpdate('task_start_date', $task->task_start_date);
$q->addUpdate('task_end_date', $task->task_end_date);
$q->addWhere('task_id = ' . (int) $task->task_id);
$q->addWhere('task_dynamic <> 1');
$q->exec();
$q->clear();
}
$task->pushDependencies($task->task_id, $task->task_end_date);
}
//$task = (array)$task;
$task->load($task_id);
unset($task->_query, $task->_error, $task->_tbl_prefix, $task->_tbl, $task->_tbl_key, $task->_tbl_module);
$this->data['task'] = $task;
$this->data['success'] = true;
return $this->toArray();
}
/**
* Default delete method
*
* Can be overloaded/supplemented by the child class
* @return null|string null if successful otherwise returns and error message
*/
public function delete($oid = null)
{
$k = $this->_tbl_key;
if ($oid) {
$this->{$k} = intval($oid);
}
if (!$this->canDelete($msg)) {
return $msg;
}
$q = new w2p_Database_Query();
$q->setDelete($this->_tbl);
$q->addWhere($this->_tbl_key . ' = \'' . $this->{$k} . '\'');
$result = !$q->exec() ? db_error() : null;
if (!$result) {
// only record history if deletion actually occurred
addHistory($this->_tbl, $this->{$k}, 'delete');
}
$q->clear();
return $result;
}
public static function updatePercentComplete($project_id)
{
$working_hours = w2PgetConfig('daily_working_hours') ? w2PgetConfig('daily_working_hours') : 8;
$q = new w2p_Database_Query();
$q->addTable('projects');
$q->addQuery('SUM(t1.task_duration * t1.task_percent_complete * IF(t1.task_duration_type = 24, ' . $working_hours . ', t1.task_duration_type)) / SUM(t1.task_duration * IF(t1.task_duration_type = 24, ' . $working_hours . ', t1.task_duration_type)) AS project_percent_complete');
$q->addJoin('tasks', 't1', 'projects.project_id = t1.task_project', 'inner');
$q->addWhere('project_id = ' . $project_id . ' AND t1.task_id = t1.task_parent');
$project_percent_complete = $q->loadResult();
$q->clear();
$q->addTable('projects');
$q->addUpdate('project_percent_complete', $project_percent_complete);
$q->addWhere('project_id = ' . (int) $project_id);
$q->exec();
global $AppUI;
CTask::storeTokenTask($AppUI, $project_id);
}
public function getTotalProjectHours()
{
global $w2Pconfig;
// now milestones are summed up, too, for consistence with the tasks duration sum
// the sums have to be rounded to prevent the sum form having many (unwanted) decimals because of the mysql floating point issue
// more info on http://www.mysql.com/doc/en/Problems_with_float.html
$q = new w2p_Database_Query();
$q->addTable('tasks');
$q->addQuery('ROUND(SUM(task_duration),2)');
$q->addWhere('task_project = ' . (int) $this->project_id . ' AND task_duration_type = 24 AND task_dynamic <> 1');
$days = $q->loadResult();
$q->clear();
$q->addTable('tasks');
$q->addQuery('ROUND(SUM(task_duration),2)');
$q->addWhere('task_project = ' . (int) $this->project_id . ' AND task_duration_type = 1 AND task_dynamic <> 1');
$hours = $q->loadResult();
$total_project_hours = $days * $w2Pconfig['daily_working_hours'] + $hours;
return rtrim($total_project_hours, '.');
}
public static function updateHoursWorked($taskId, $totalHours)
{
$q = new w2p_Database_Query();
$q->addTable('tasks');
$q->addUpdate('task_hours_worked', $totalHours + 0);
$q->addWhere('task_id = ' . $taskId);
$q->exec();
$q->clear();
$q->addTable('tasks');
$q->addQuery('task_project');
$q->addWhere('task_id = ' . $taskId);
$project_id = $q->loadResult();
CProject::updateHoursWorked($project_id);
}
public function notifyContacts($notifyContacts)
{
global $AppUI, $w2Pconfig, $locale_char_set;
if ($notifyContacts) {
//if no project specified than we will not do anything
if ($this->file_project != 0) {
$this->_project = new CProject();
$this->_project->load($this->file_project);
$mail = new w2p_Utilities_Mail();
if ($this->file_task == 0) {
//notify all developers
$mail->Subject($AppUI->_('Project') . ': ' . $this->_project->project_name . '::' . $this->file_name, $locale_char_set);
} else {
//notify all assigned users
$this->_task = new CTask();
$this->_task->load($this->file_task);
$mail->Subject($AppUI->_('Project') . ': ' . $this->_project->project_name . '::' . $this->_task->task_name . '::' . $this->file_name, $locale_char_set);
}
$body = $AppUI->_('Project') . ': ' . $this->_project->project_name;
$body .= "\n" . $AppUI->_('URL') . ': ' . W2P_BASE_URL . '/index.php?m=projects&a=view&project_id=' . $this->_project->project_id;
if (intval($this->_task->task_id) != 0) {
$body .= "\n\n" . $AppUI->_('Task') . ': ' . $this->_task->task_name;
$body .= "\n" . $AppUI->_('URL') . ': ' . W2P_BASE_URL . '/index.php?m=tasks&a=view&task_id=' . $this->_task->task_id;
$body .= "\n" . $AppUI->_('Description') . ":\n" . $this->_task->task_description;
$q = new w2p_Database_Query();
$q->addTable('project_contacts', 'pc');
$q->addQuery('c.contact_email as contact_email, c.contact_first_name as contact_first_name, c.contact_last_name as contact_last_name');
$q->addJoin('contacts', 'c', 'c.contact_id = pc.contact_id');
$q->addWhere('pc.project_id = ' . (int) $this->_project->project_id);
$sql = '(' . $q->prepare() . ')';
$q->clear();
$sql .= ' UNION ';
$q->addTable('task_contacts', 'tc');
$q->addQuery('c.contact_email as contact_email, c.contact_first_name as contact_first_name, c.contact_last_name as contact_last_name');
$q->addJoin('contacts', 'c', 'c.contact_id = tc.contact_id');
$q->addWhere('tc.task_id = ' . (int) $this->_task->task_id);
} else {
$q = new w2p_Database_Query();
$q->addTable('project_contacts', 'pc');
$q->addQuery('pc.project_id, pc.contact_id');
$q->addQuery('c.contact_email as contact_email, c.contact_first_name as contact_first_name, c.contact_last_name as contact_last_name');
$q->addJoin('contacts', 'c', 'c.contact_id = pc.contact_id');
$q->addWhere('pc.project_id = ' . (int) $this->file_project);
}
$this->_users = $q->loadList();
$body .= "\n\nFile " . $this->file_name . ' was ' . $this->_message . ' by ' . $AppUI->user_first_name . ' ' . $AppUI->user_last_name;
if ($this->_message != 'deleted') {
$body .= "\n" . $AppUI->_('URL') . ': ' . W2P_BASE_URL . '/fileviewer.php?file_id=' . $this->file_id;
$body .= "\n" . $AppUI->_('Description') . ":\n" . $this->file_description;
}
//send mail
$mail->Body($body, isset($GLOBALS['locale_char_set']) ? $GLOBALS['locale_char_set'] : '');
foreach ($this->_users as $row) {
if ($mail->ValidEmail($row['contact_email'])) {
$mail->To($row['contact_email'], true);
$mail->Send();
}
}
return '';
}
}
}
public static function getFirstLetters($userId, $onlyUsers = false)
{
$letters = '';
$search_map = array('contact_first_name', 'contact_last_name');
$q = new w2p_Database_Query();
foreach ($search_map as $search_name) {
$q->addTable('contacts');
$q->addQuery('DISTINCT SUBSTRING(' . $search_name . ', 1, 1) as L');
if ($onlyUsers) {
$q->addJoin('users', 'u', 'user_contact = contact_id', 'inner');
}
$q->addWhere('contact_private=0 OR (contact_private=1 AND contact_owner=' . (int) $userId . ') OR contact_owner IS NULL OR contact_owner = 0');
$arr = $q->loadList();
foreach ($arr as $L) {
$letters .= $L['L'];
}
$q->clear();
}
return strtoupper($letters);
}
public function delete()
{
$q = new w2p_Database_Query();
if ($this->sysval_title) {
$q->setDelete('sysvals');
$q->addWhere('sysval_title = \'' . $this->sysval_title . '\'');
if (!$q->exec()) {
$q->clear();
return get_class($this) . '::delete failed <br />' . db_error();
}
}
return null;
}
if ($project_id != 0) {
$q->addWhere('task_project=' . $project_id);
}
$proj = new CProject();
$obj = new CTask();
$allowedProjects = $proj->getAllowedSQL($AppUI->user_id, 'task_project');
$allowedTasks = $obj->getAllowedSQL($AppUI->user_id);
if (count($allowedProjects)) {
$q->addWhere(implode(' AND ', $allowedProjects));
}
if (count($allowedTasks)) {
$q->addWhere(implode(' AND ', $allowedTasks));
}
$q->addOrder('task_end_date');
$task_list_hash = $q->loadHashList('task_id');
$q->clear();
$task_list = array();
$task_assigned_users = array();
$i = 0;
foreach ($task_list_hash as $task_id => $task_data) {
$task = new CTask();
$task->bind($task_data);
$task_list[$i] = $task;
$task_assigned_users[$i] = $task->assignees($task_id);
$i++;
}
$Ntasks = $i;
$user_usage = array();
$task_dates = array();
$actual_date = $start_date;
$days_header = "";
public function install()
{
global $AppUI;
$q = new w2p_Database_Query();
$q->createTable('links');
$q->createDefinition('(
link_id int( 11 ) NOT NULL AUTO_INCREMENT ,
link_url varchar( 255 ) NOT NULL default "",
link_project int( 11 ) NOT NULL default "0",
link_task int( 11 ) NOT NULL default "0",
link_name varchar( 255 ) NOT NULL default "",
link_parent int( 11 ) default "0",
link_description text,
link_owner int( 11 ) default "0",
link_date datetime default NULL ,
link_icon varchar( 20 ) default "obj/",
link_category int( 11 ) NOT NULL default "0",
PRIMARY KEY ( link_id ) ,
KEY idx_link_task ( link_task ) ,
KEY idx_link_project ( link_project ) ,
KEY idx_link_parent ( link_parent )
) ENGINE = MYISAM DEFAULT CHARSET=utf8 ');
$q->exec($sql);
$i = 0;
$linkTypes = array('Unknown', 'Document', 'Application');
foreach ($linkTypes as $linkType) {
$q->clear();
$q->addTable('sysvals');
$q->addInsert('sysval_key_id', 1);
$q->addInsert('sysval_title', 'LinkType');
$q->addInsert('sysval_value', $linkType);
$q->addInsert('sysval_value_id', $i);
$q->exec();
$i++;
}
$perms = $AppUI->acl();
return $perms->registerModule('Links', 'links');
}
