PHP w2p_Database_Query::addJoin方法代码示例

function sendNewPass()
{
    global $AppUI;
    $_live_site = w2PgetConfig('base_url');
    $_sitename = w2PgetConfig('company_name');
    // ensure no malicous sql gets past
    $checkusername = trim(w2PgetParam($_POST, 'checkusername', ''));
    $checkusername = db_escape($checkusername);
    $confirmEmail = trim(w2PgetParam($_POST, 'checkemail', ''));
    $confirmEmail = strtolower(db_escape($confirmEmail));
    $q = new w2p_Database_Query();
    $q->addTable('users');
    $q->addJoin('contacts', 'con', 'user_contact = contact_id', 'inner');
    $q->addQuery('user_id');
    $q->addWhere('user_username = \'' . $checkusername . '\'');
    /* 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
     */
    $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_value = '{$confirmEmail}'");
    } else {
        $q->addWhere("LOWER(contact_email) = '{$confirmEmail}'");
    }
    /* End Hack */
    if (!($user_id = $q->loadResult()) || !$checkusername || !$confirmEmail) {
        $AppUI->setMsg('Invalid username or email.', UI_MSG_ERROR);
        $AppUI->redirect();
    }
    $newpass = makePass();
    $message = $AppUI->_('sendpass0', UI_OUTPUT_RAW) . ' ' . $checkusername . ' ' . $AppUI->_('sendpass1', UI_OUTPUT_RAW) . ' ' . $_live_site . ' ' . $AppUI->_('sendpass2', UI_OUTPUT_RAW) . ' ' . $newpass . ' ' . $AppUI->_('sendpass3', UI_OUTPUT_RAW);
    $subject = $_sitename . ' :: ' . $AppUI->_('sendpass4', UI_OUTPUT_RAW) . ' - ' . $checkusername;
    $m = new w2p_Utilities_Mail();
    // create the mail
    $m->To($confirmEmail);
    $m->Subject($subject);
    $m->Body($message, isset($GLOBALS['locale_char_set']) ? $GLOBALS['locale_char_set'] : '');
    // set the body
    $m->Send();
    // send the mail
    $newpass = md5($newpass);
    $q->addTable('users');
    $q->addUpdate('user_password', $newpass);
    $q->addWhere('user_id=' . $user_id);
    $cur = $q->exec();
    if (!$cur) {
        die('SQL error' . $database->stderr(true));
    } else {
        $AppUI->setMsg('New User Password created and emailed to you');
        $AppUI->redirect();
    }
}

 public static function getProjects($contactId)
 {
     $q = new w2p_Database_Query();
     $q->addQuery('p.project_id, p.project_name');
     $q->addTable('project_contacts', 'pc');
     $q->addJoin('projects', 'p', 'p.project_id = pc.project_id', 'inner');
     $q->addWhere("contact_id =  {$contactId}");
     return $q->loadList();
 }

    echo '<table class="std">
	<tr>
		<td align="center">';
    // Let's figure out which users we have
    $user_list = w2PgetUsersHashList();
    if ($log_userfilter != 0) {
        $user_list = array($log_userfilter => $user_list[$log_userfilter]);
    }
    $ss = "'" . $start_date->format(FMT_DATETIME_MYSQL) . "'";
    $se = "'" . $end_date->format(FMT_DATETIME_MYSQL) . "'";
    $and = false;
    $where = false;
    $q = new w2p_Database_Query();
    $q->addTable('tasks', 't');
    $q->addQuery('t.*');
    $q->addJoin('projects', '', 'projects.project_id = task_project', 'inner');
    $q->addJoin('project_departments', '', 'project_departments.project_id = projects.project_id');
    $q->addJoin('departments', '', 'department_id = dept_id');
    $q->addWhere('project_active = 1');
    if (($template_status = w2PgetConfig('template_projects_status_id')) != '') {
        $q->addWhere('project_status <> ' . (int) $template_status);
    }
    if ($use_period) {
        $q->addWhere('( (task_start_date >= ' . $ss . ' AND task_start_date <= ' . $se . ') OR ' . '(task_end_date <= ' . $se . ' AND task_end_date >= ' . $ss . ') )');
    }
    if ($project_id != 0) {
        $q->addWhere('task_project=' . $project_id);
    }
    $proj = new CProject();
    $obj = new CTask();
    $allowedProjects = $proj->getAllowedSQL($AppUI->user_id, 'task_project');

 public function getTaskLogs($taskId, $problem = false)
 {
     $q = new w2p_Database_Query();
     $q->addTable('task_log');
     $q->addQuery('task_log.*, user_username, billingcode_name as task_log_costcode');
     $q->addQuery('CONCAT(contact_first_name, \' \', contact_last_name) AS real_name');
     $q->addWhere('task_log_task = ' . (int) $taskId . ($problem ? ' AND task_log_problem > 0' : ''));
     $q->addOrder('task_log_date');
     $q->addOrder('task_log_created');
     $q->leftJoin('billingcode', '', 'task_log.task_log_costcode = billingcode_id');
     $q->addJoin('users', '', 'task_log_creator = user_id', 'inner');
     $q->addJoin('contacts', 'ct', 'contact_id = user_contact', 'inner');
     return $q->loadList();
 }

    $owner_ids = array();
    $q = new w2p_Database_Query();
    $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();
}
// pull valid projects and their percent complete information
$q = new w2p_Database_Query();
$q->addTable('projects', 'pr');
$q->addQuery('DISTINCT pr.project_id, project_color_identifier, project_name, project_start_date, project_end_date,
                max(t1.task_end_date) AS project_actual_end_date, project_percent_complete,
                project_status, project_active');
$q->addJoin('tasks', 't1', 'pr.project_id = t1.task_project');
$q->addJoin('companies', 'c1', 'pr.project_company = c1.company_id');
if ($department > 0 && !$addPwOiD) {
    $q->addWhere('project_departments.department_id = ' . (int) $department);
}
if ($project_type > -1) {
    $q->addWhere('pr.project_type = ' . (int) $project_type);
}
if ($owner > 0) {
    $q->addWhere('pr.project_owner = ' . (int) $owner);
}
if ($proFilter == '-3') {
    $q->addWhere('pr.project_owner = ' . (int) $user_id);
} elseif ($proFilter != '-1') {
    $q->addWhere('pr.project_status = ' . (int) $proFilter);
}

 public static function getFileList($AppUI = null, $company_id = 0, $project_id = 0, $task_id = 0, $category_id = 0)
 {
     global $AppUI;
     $q = new w2p_Database_Query();
     $q->addQuery('f.*');
     $q->addTable('files', 'f');
     $q->addJoin('projects', 'p', 'p.project_id = file_project');
     $q->addJoin('project_departments', 'pd', 'p.project_id = pd.project_id');
     $q->addJoin('departments', '', 'pd.department_id = dept_id');
     $q->addJoin('tasks', 't', 't.task_id = file_task');
     $project = new CProject();
     //TODO: We need to convert this from static to use ->overrideDatabase() for testing.
     $allowedProjects = $project->getAllowedSQL($AppUI->user_id, 'file_project');
     if (count($allowedProjects)) {
         $q->addWhere('( ( ' . implode(' AND ', $allowedProjects) . ') OR file_project = 0 )');
     }
     if (isset($company_id) && (int) $company_id > 0) {
         $q->addWhere('project_company = ' . (int) $company_id);
     }
     if (isset($project_id) && (int) $project_id > 0) {
         $q->addWhere('file_project = ' . (int) $project_id);
     }
     if (isset($task_id) && (int) $task_id > 0) {
         $q->addWhere('file_task = ' . (int) $task_id);
     }
     if ($category_id >= 0) {
         $q->addWhere('file_category = ' . (int) $category_id);
     }
     return $q->loadList();
 }

$today->convertTZ($AppUI->getPref('TIMEZONE'));
//Lets load the users panel viewing options
$q = new w2p_Database_Query();
$q->addTable('project_designer_options', 'pdo');
$q->addQuery('pdo.*');
$q->addWhere('pdo.pd_option_user = ' . (int) $AppUI->user_id);
$view_options = $q->loadList();
$project_id = (int) w2PgetParam($_POST, 'project_id', 0);
$project_id = (int) w2PgetParam($_GET, 'project_id', $project_id);
$extra = array('where' => 'project_active = 1');
$project = new CProject();
$projects = $project->getAllowedRecords($AppUI->user_id, 'projects.project_id,project_name', 'project_name', null, $extra, 'projects');
$q = new w2p_Database_Query();
$q->addTable('projects');
$q->addQuery('projects.project_id, company_name');
$q->addJoin('companies', 'co', 'co.company_id = project_company');
$idx_companies = $q->loadHashList();
$q->clear();
foreach ($projects as $prj_id => $prj_name) {
    $projects[$prj_id] = $idx_companies[$prj_id] . ': ' . $prj_name;
}
asort($projects);
$projects = arrayMerge(array('0' => $AppUI->_('(None)', UI_OUTPUT_RAW)), $projects);
$extra = array();
$task = new CTask();
$tasks = $task->getAllowedRecords($AppUI->user_id, 'task_id,task_name', 'task_name', null, $extra);
$tasks = arrayMerge(array('0' => $AppUI->_('(None)', UI_OUTPUT_RAW)), $tasks);
if (!$project_id) {
    // setup the title block
    $ttl = 'ProjectDesigner';
    $titleBlock = new w2p_Theme_TitleBlock($ttl, 'projectdesigner.png', $m, $m . '.' . $a);

 public function getTaskLogs(CAppUI $AppUI = null, $projectId, $user_id = 0, $hide_inactive = false, $hide_complete = false, $cost_code = 0)
 {
     global $AppUI;
     $q = new w2p_Database_Query();
     $q->addTable('task_log');
     $q->addQuery('DISTINCT task_log.*, user_username, task_id');
     $q->addQuery("CONCAT(contact_first_name, ' ', contact_last_name) AS real_name");
     $q->addQuery('billingcode_name as task_log_costcode');
     $q->addJoin('users', 'u', 'user_id = task_log_creator');
     $q->addJoin('tasks', 't', 'task_log_task = t.task_id');
     $q->addJoin('contacts', 'ct', 'contact_id = user_contact');
     $q->addJoin('billingcode', 'b', 'task_log.task_log_costcode = billingcode_id');
     $q->addWhere('task_project = ' . (int) $projectId);
     if ($user_id > 0) {
         $q->addWhere('task_log_creator=' . $user_id);
     }
     if ($hide_inactive) {
         $q->addWhere('task_status>=0');
     }
     if ($hide_complete) {
         $q->addWhere('task_percent_complete < 100');
     }
     if ($cost_code > 0) {
         $q->addWhere("billingcode_id = {$cost_code}");
     }
     $q->addOrder('task_log_date');
     $q->addOrder('task_log_created');
     $this->setAllowedSQL($AppUI->user_id, $q, 'task_project');
     return $q->loadList();
 }

 public function _buildQuery()
 {
     $q = new w2p_Database_Query();
     if ($this->table_alias) {
         $q->addTable($this->table, $this->table_alias);
     } else {
         $q->addTable($this->table);
     }
     $q->addQuery('DISTINCT(' . $this->table_key . ')');
     if (isset($this->table_key2)) {
         $q->addQuery($this->table_key2);
     }
     //--MSy--
     foreach ($this->table_joins as $join) {
         $q->addJoin($join['table'], $join['alias'], $join['join']);
     }
     foreach ($this->display_fields as $fld) {
         $q->addQuery($fld);
     }
     $q->addOrder($this->table_orderby);
     if ($this->table_groupby) {
         $q->addGroup($this->table_groupby);
     }
     if ($this->table_extra) {
         $q->addWhere($this->table_extra);
     }
     $ignore = w2PgetSysVal('FileIndexIgnoreWords');
     $ignore = explode(',', $ignore['FileIndexIgnoreWords']);
     $this->keywords = array_diff(array_keys($this->keywords), $ignore);
     $sql = '';
     foreach ($this->keywords as $keyword) {
         $sql .= '(';
         foreach ($this->search_fields as $field) {
             //OR treatment to each keyword
             // Search for semi-colons, commas or spaces and allow any to be separators
             $or_keywords = preg_split('/[\\s,;]+/', $keyword);
             foreach ($or_keywords as $or_keyword) {
                 if ($this->search_options['ignore_specchar'] == 'on') {
                     $tmppattern = recode2regexp_utf8($or_keyword);
                     if ($this->search_options['ignore_case'] == 'on') {
                         $sql .= ' ' . $field . ' REGEXP \'' . $tmppattern . '\' or ';
                     } else {
                         $sql .= ' ' . $field . ' REGEXP BINARY \'' . $tmppattern . '\' or ';
                     }
                 } else {
                     if ($this->search_options['ignore_case'] == 'on') {
                         $sql .= ' ' . $field . ' LIKE "%' . $or_keyword . '%" or ';
                     } else {
                         $sql .= ' ' . $field . ' LIKE BINARY "%' . $or_keyword . '%" or ';
                     }
                 }
             }
         }
         // foreach $field
         $sql = substr($sql, 0, -4);
         if ($this->search_options['all_words'] == 'on') {
             $sql .= ') and ';
         } else {
             $sql .= ') or ';
         }
     }
     // foreach $keyword
     //--MSy--
     $sql = substr($sql, 0, -4);
     if ($sql) {
         $q->addWhere($sql);
         return $q;
     } else {
         return null;
     }
 }

</tr>
<tr>
	<td align="right" valign="top"><?php 
echo $AppUI->_('Email Log to');
?>
:</td>
	<td>
<?php 
$tl = $AppUI->getPref('TASKLOGEMAIL');
$ta = $tl & 1;
$tt = $tl & 2;
$tp = $tl & 4;
$task_email_title = array();
$q = new w2p_Database_Query();
$q->addTable('task_contacts', 'tc');
$q->addJoin('contacts', 'c', 'c.contact_id = tc.contact_id', 'inner');
$q->addWhere('tc.task_id = ' . (int) $obj->task_id);
$q->addQuery('tc.contact_id');
$q->addQuery('c.contact_first_name, c.contact_last_name');
$req =& $q->exec();
$cidtc = array();
for ($req; !$req->EOF; $req->MoveNext()) {
    $cidtc[] = $req->fields['contact_id'];
    $task_email_title[] = $req->fields['contact_first_name'] . ' ' . $req->fields['contact_last_name'];
}
$q->clear();
$q->addTable('project_contacts', 'pc');
$q->addJoin('contacts', 'c', 'c.contact_id = pc.contact_id', 'inner');
$q->addWhere('pc.project_id = ' . (int) $obj->task_project);
$q->addQuery('pc.contact_id');
$q->addQuery('c.contact_first_name, c.contact_last_name');

if ($do_report) {
    if (function_exists('styleRenderBoxBottom')) {
        echo styleRenderBoxBottom();
    }
    echo '<br />';
    if (function_exists('styleRenderBoxTop')) {
        echo styleRenderBoxTop();
    }
    echo '<table cellspacing="0" cellpadding="4" border="0" width="100%" class="std">
<tr>
	<td>';
    // Let's figure out which users we have
    $q = new w2p_Database_Query();
    $q->addTable('users', 'u');
    $q->addQuery('u.user_id, u.user_username, contact_first_name, contact_last_name');
    $q->addJoin('contacts', 'c', 'u.user_contact = contact_id', 'inner');
    $user_list = $q->loadHashList('user_id');
    $q->clear();
    $q = new w2p_Database_Query();
    $q->addTable('tasks', 't');
    $q->addTable('user_tasks', 'ut');
    $q->addTable('projects', 'pr');
    $q->addQuery('t.*, ut.*, pr.project_name');
    $q->addWhere('( task_start_date
			   BETWEEN \'' . $start_date->format(FMT_DATETIME_MYSQL) . '\' 
	                AND \'' . $end_date->format(FMT_DATETIME_MYSQL) . '\' 
	           OR task_end_date	BETWEEN \'' . $start_date->format(FMT_DATETIME_MYSQL) . '\' 
	                AND \'' . $end_date->format(FMT_DATETIME_MYSQL) . '\' 
		   OR ( task_start_date <= \'' . $start_date->format(FMT_DATETIME_MYSQL) . '\'
	                AND task_end_date >= \'' . $end_date->format(FMT_DATETIME_MYSQL) . '\') )');
    $q->addWhere('task_end_date IS NOT NULL');

$q->addQuery('*');
$q->addJoin('tasks', '', 'task_project = project_id');
if (!empty($project_id)) {
    $q->addWhere('project_id = ' . (int) $project_id);
}
$obj = new CTask();
$allowedTasks = $obj->getAllowedSQL($AppUI->user_id);
if (count($allowedTasks)) {
    $obj->getAllowedSQL($AppUI->user_id, $q);
}
$all_tasks = $q->loadList();
$q->clear();
$q = new w2p_Database_Query();
$q->addTable('projects');
$q->addQuery('*, round(sum(task_log_hours),2) as work');
$q->addJoin('tasks', '', 'task_project = project_id');
$q->addJoin('user_tasks', '', 'user_tasks.task_id = tasks.task_id');
$q->addJoin('users', '', 'user_tasks.user_id = users.user_id');
$q->addJoin('contacts', '', 'users.user_contact = contact_id');
$q->addJoin('task_log', '', 'task_log_task = tasks.task_id AND task_log_creator = users.user_id');
$q->addWhere('project_active = 1');
if (($template_status = w2PgetConfig('template_projects_status_id')) != '') {
    $q->addWhere('project_status <> ' . (int) $template_status);
}
if (!empty($project_id)) {
    $q->addWhere('project_id = ' . (int) $project_id);
}
$q->addGroup('tasks.task_id');
$q->addGroup('users.user_id');
$obj = new CTask();
$allowedTasks = $obj->getAllowedSQL($AppUI->user_id);

<br />
<?php 
if (function_exists('styleRenderBoxTop')) {
    echo styleRenderBoxTop();
}
if ($do_report) {
    // Let's figure out which users we have
    $user_list = $active_users;
    $ss = '\'' . $start_date->format(FMT_DATETIME_MYSQL) . '\'';
    $se = '\'' . $end_date->format(FMT_DATETIME_MYSQL) . '\'';
    $and = false;
    $where = false;
    $q = new w2p_Database_Query();
    $q->addTable('tasks', 't');
    $q->addQuery('t.*');
    $q->addJoin('projects', 'pr', 'pr.project_id = t.task_project', 'inner');
    $q->addWhere('pr.project_active = 1');
    if (($template_status = w2PgetConfig('template_projects_status_id')) != '') {
        $q->addWhere('pr.project_status <> ' . (int) $template_status);
    }
    if ($use_period) {
        $q->addWhere('(( task_start_date >= ' . $ss . ' AND task_start_date <= ' . $se . ' ) OR ' . '  ( task_end_date <= ' . $se . ' AND task_end_date >= ' . $ss . ' ))');
    }
    $q->addWhere('(task_percent_complete < 100)');
    $q->addJoin('user_tasks', 'ut', 'ut.task_id = t.task_id');
    if ($log_userfilter > -1) {
        $q->addWhere('ut.user_id = ' . $log_userfilter);
    }
    if ($project_id != 'all') {
        $q->addWhere('t.task_project=' . (int) $project_id);
    }

 /**
  *	Generic check for whether dependencies exist for this object in the db schema
  *
  *	Can be overloaded/supplemented by the child class
  *	@param string $msg Error message returned
  *	@param int Optional key index
  *	@param array Optional array to compiles standard joins: format [label=>'Label',name=>'table name',idfield=>'field',joinfield=>'field']
  *	@return true|false
  */
 public function canDelete(&$msg, $oid = null, $joins = null)
 {
     global $AppUI;
     // First things first.  Are we allowed to delete?
     $acl =& $AppUI->acl();
     if (!$acl->checkModuleItem($this->_tbl_module, 'delete', $oid)) {
         $msg = $AppUI->_('noDeletePermission');
         return false;
     }
     $k = $this->_tbl_key;
     if ($oid) {
         $this->{$k} = intval($oid);
     }
     if (is_array($joins)) {
         $select = $k;
         $join = '';
         $q = new w2p_Database_Query();
         $q->addTable($this->_tbl);
         $q->addWhere($k . ' = \'' . $this->{$k} . '\'');
         $q->addGroup($k);
         foreach ($joins as $table) {
             $q->addQuery('COUNT(DISTINCT ' . $table['idfield'] . ') AS ' . $table['idfield']);
             $q->addJoin($table['name'], $table['name'], $table['joinfield'] . ' = ' . $k);
         }
         $obj = null;
         $q->loadObject($obj);
         $q->clear();
         if (!$obj) {
             $msg = db_error();
             return false;
         }
         $msg = array();
         foreach ($joins as $table) {
             $k = $table['idfield'];
             if ($obj->{$k}) {
                 $msg[] = $AppUI->_($table['label']);
             }
         }
         if (count($msg)) {
             $msg = $AppUI->_('noDeleteRecord') . ': ' . implode(', ', $msg);
             $this->_error = $msg;
             return false;
         } else {
             return true;
         }
     }
     return true;
 }

 public function getChildren($group_id, $group_type = 'ARO', $recurse = 'NO_RECURSE')
 {
     switch (strtolower(trim($group_type))) {
         case 'axo':
             $group_type = 'axo';
             $table = $this->_db_acl_prefix . 'axo_groups';
             break;
         default:
             $group_type = 'aro';
             $table = $this->_db_acl_prefix . 'aro_groups';
     }
     if (empty($group_id)) {
         $this->debug_text("get_group_children(): ID ({$group_id}) is empty, this is required");
         return false;
     }
     $q = new w2p_Database_Query();
     $q->addTable($table, 'g1');
     $q->addQuery('g1.id, g1.name, g1.value, g1.parent_id');
     $q->addOrder('g1.value');
     switch (strtoupper($recurse)) {
         case 'RECURSE':
             $q->addJoin($table, 'g2', 'g2.lft<g1.lft AND g2.rgt>g1.rgt');
             $q->addWhere('g2.id=' . $group_id);
             break;
         default:
             $q->addWhere('g1.parent_id=' . $group_id);
     }
     $result = array();
     $q->exec();
     while ($row = $q->fetchRow()) {
         $result[] = array('id' => $row[0], 'name' => $row[1], 'value' => $row[2], 'parent_id' => $row[3]);
     }
     $q->clear();
     return $result;
 }