$q->addQuery('task_description, task_owner, task_status');
$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');
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 '';
}
}
}
/**
* Login function
*
* A number of things are done in this method to prevent illegal entry:
* <ul>
* <li>The username and password are trimmed and escaped to prevent malicious
* SQL being executed
* </ul>
* The schema previously used the MySQL PASSWORD function for encryption. This
* Method has been deprecated in favour of PHP's MD5() function for database independance.
* The check_legacy_password option is no longer valid
*
* Upon a successful username and password match, several fields from the user
* table are loaded in this object for convenient reference. The style, locales
* and preferences are also loaded at this time.
*
* @param string The user login name
* @param string The user password
* @return boolean True if successful, false if not
*/
public function login($username, $password)
{
$auth_method = w2PgetConfig('auth_method', 'sql');
if ($_POST['login'] != 'login' && $_POST['login'] != $this->_('login', UI_OUTPUT_RAW) && $_REQUEST['login'] != $auth_method) {
die('You have chosen to log in using an unsupported or disabled login method');
}
$auth =& getauth($auth_method);
$username = trim(db_escape($username));
$password = trim($password);
if (!$auth->authenticate($username, $password)) {
return false;
}
$user_id = $auth->userId($username);
$username = $auth->username;
// Some authentication schemes may collect username in various ways.
// Now that the password has been checked, see if they are allowed to
// access the system
if (!isset($GLOBALS['acl'])) {
$GLOBALS['acl'] = new w2p_Extensions_Permissions();
}
if (!$GLOBALS['acl']->checkLogin($user_id)) {
dprint(__FILE__, __LINE__, 1, 'Permission check failed');
return false;
}
$q = new w2p_Database_Query();
$q->addTable('users');
$q->addQuery('user_id, contact_first_name as user_first_name, contact_last_name as user_last_name, contact_company as user_company, contact_department as user_department, user_type');
$q->addJoin('contacts', 'con', 'con.contact_id = user_contact', 'inner');
/* Begin Hack */
/*
* This is a particularly annoying hack but I don't know of a better
* way to resolve #457. In v2.0, there was a refactoring to allow for
* muliple contact methods which resulted in the contact_email being
* removed from the contacts table. If the user is upgrading from
* v1.x and they try to log in before applying the database, crash.
* Info: http://bugs.web2project.net/view.php?id=457
* This hack was deprecated in dbVersion 26 for v2.2 in December 2010.
*/
$qTest = new w2p_Database_Query();
$qTest->addTable('w2pversion');
$qTest->addQuery('max(db_version)');
$dbVersion = $qTest->loadResult();
if ($dbVersion >= 21 && $dbVersion < 26) {
$q->leftJoin('contacts_methods', 'cm', 'cm.contact_id = con.contact_id');
$q->addWhere("cm.method_name = 'email_primary'");
$q->addQuery('cm.method_value AS user_email');
} else {
$q->addQuery('contact_email AS user_email');
}
/* End Hack */
$q->addWhere('user_id = ' . (int) $user_id . ' AND user_username = \'' . $username . '\'');
$sql = $q->prepare();
$q->loadObject($this);
dprint(__FILE__, __LINE__, 7, 'Login SQL: ' . $sql);
if (!$this) {
dprint(__FILE__, __LINE__, 1, 'Failed to load user information');
return false;
}
// load the user preferences
$this->loadPrefs($this->user_id);
$this->setUserLocale();
$this->checkStyle();
// Let's see if this user has admin privileges
if (canView('admin')) {
$this->user_is_admin = 1;
}
return true;
}
/**
* @return String
*/
function __extract_from_tasks1()
{
//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();
return $subquery;
}
public function gc()
{
global $AppUI;
$max = $this->convertTime('max_lifetime');
$idle = $this->convertTime('idle_time');
// First pass is to kill any users that are logged in at the time of the session.
$where = 'UNIX_TIMESTAMP() - UNIX_TIMESTAMP(session_updated) > ' . $idle . ' OR UNIX_TIMESTAMP() - UNIX_TIMESTAMP(session_created) > ' . $max;
$q = new w2p_Database_Query();
$q->addTable('user_access_log');
$q->addUpdate('date_time_out', $q->dbfnNowWithTZ());
$q2 = new w2p_Database_Query();
$q2->addTable('sessions');
$q2->addQuery('session_user');
$q2->addWhere($where);
$q->addWhere('user_access_log_id IN ( ' . $q2->prepare() . ' )');
$q->exec();
$q->clear();
$q2->clear();
// Now we simply delete the expired sessions.
$q->setDelete('sessions');
$q->addWhere($where);
$q->exec();
$q->clear();
if (w2PgetConfig('session_gc_scan_queue')) {
// We need to scan the event queue. If $AppUI isn't created yet
// And it isn't likely that it will be, we create it and run the
// queue scanner.
if (!isset($AppUI)) {
$AppUI = new w2p_Core_CAppUI();
$queue = new w2p_System_EventQueue();
$queue->scan();
}
}
return true;
}
