PHP appendFromClauseToQuery函数代码示例

 /**
  * Function to get relation query for particular module with function name
  * @param <record> $recordId
  * @param <String> $functionName
  * @param Vtiger_Module_Model $relatedModule
  * @return <String>
  */
 public function getRelationQuery($recordId, $functionName, $relatedModule)
 {
     if ($functionName === 'get_activities') {
         $userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users');
         $query = "SELECT CASE WHEN (vtiger_users.user_name not like '') THEN {$userNameSql} ELSE vtiger_groups.groupname END AS user_name,\n\t\t\t\t\t\tvtiger_crmentity.*, vtiger_activity.activitytype, vtiger_activity.subject, vtiger_activity.date_start, vtiger_activity.time_start,\n\t\t\t\t\t\tvtiger_activity.recurringtype, vtiger_activity.due_date, vtiger_activity.time_end, vtiger_activity.visibility, vtiger_seactivityrel.crmid AS parent_id,\n\t\t\t\t\t\tCASE WHEN (vtiger_activity.activitytype = 'Task') THEN (vtiger_activity.status) ELSE (vtiger_activity.eventstatus) END AS status\n\t\t\t\t\t\tFROM vtiger_activity\n\t\t\t\t\t\tINNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_activity.activityid\n\t\t\t\t\t\tLEFT JOIN vtiger_seactivityrel ON vtiger_seactivityrel.activityid = vtiger_activity.activityid\n\t\t\t\t\t\tLEFT JOIN vtiger_cntactivityrel ON vtiger_cntactivityrel.activityid = vtiger_activity.activityid\n\t\t\t\t\t\tLEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\t\t\t\tLEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\t\t\t\t\tWHERE vtiger_crmentity.deleted = 0 AND vtiger_activity.activitytype = 'Task'\n\t\t\t\t\t\t\t\tAND vtiger_seactivityrel.crmid = " . $recordId;
         $relatedModuleName = $relatedModule->getName();
         $query .= $this->getSpecificRelationQuery($relatedModuleName);
         $nonAdminQuery = $this->getNonAdminAccessControlQueryForRelation($relatedModuleName);
         if ($nonAdminQuery) {
             $query = appendFromClauseToQuery($query, $nonAdminQuery);
         }
     } else {
         $query = parent::getRelationQuery($recordId, $functionName, $relatedModule);
     }
     return $query;
 }

 /**
  * Function to get relation query for particular module with function name
  * @param <record> $recordId
  * @param <String> $functionName
  * @param Vtiger_Module_Model $relatedModule
  * @return <String>
  */
 public function getRelationQuery($recordId, $functionName, $relatedModule)
 {
     $relatedModuleName = $relatedModule->getName();
     $focus = CRMEntity::getInstance($this->getName());
     $focus->id = $recordId;
     $result = $focus->{$functionName}($recordId, $this->getId(), $relatedModule->getId());
     $query = $result['query'] . ' ' . $this->getSpecificRelationQuery($relatedModuleName);
     $nonAdminQuery = $this->getNonAdminAccessControlQueryForRelation($relatedModuleName);
     //modify query if any module has summary fields, those fields we are displayed in related list of that module
     $relatedListFields = $relatedModule->getConfigureRelatedListFields();
     if ($relatedModuleName == 'Documents') {
         $relatedListFields['filelocationtype'] = 'filelocationtype';
         $relatedListFields['filestatus'] = 'filestatus';
     }
     if (count($relatedListFields) > 0) {
         $currentUser = Users_Record_Model::getCurrentUserModel();
         $queryGenerator = new QueryGenerator($relatedModuleName, $currentUser);
         $queryGenerator->setFields($relatedListFields);
         $selectColumnSql = $queryGenerator->getSelectClauseColumnSQL();
         $newQuery = spliti('FROM', $query);
         $selectColumnSql = 'SELECT DISTINCT vtiger_crmentity.crmid,' . $selectColumnSql;
         $query = $selectColumnSql . ' FROM ' . $newQuery[1];
     }
     if ($nonAdminQuery) {
         $query = appendFromClauseToQuery($query, $nonAdminQuery);
     }
     return $query;
 }

/** Function to get related list entries in detailed array format
 * @param $module -- modulename:: Type string
 * @param $relatedmodule -- relatedmodule:: Type string
 * @param $focus -- focus:: Type object
 * @param $query -- query:: Type string
 * @param $button -- buttons:: Type string
 * @param $returnset -- returnset:: Type string
 * @param $id -- id:: Type string
 * @param $edit_val -- edit value:: Type string
 * @param $del_val -- delete value:: Type string
 * @returns $related_entries -- related entires:: Type string array
 */
function GetRelatedListBase($module, $relatedmodule, $focus, $query, $button, $returnset, $id = '', $edit_val = '', $del_val = '', $skipActions = false)
{
    $log = LoggerManager::getLogger('account_list');
    $log->debug("Entering GetRelatedList(" . $module . "," . $relatedmodule . "," . get_class($focus) . "," . $query . "," . $button . "," . $returnset . "," . $edit_val . "," . $del_val . ") method ...");
    require_once 'Smarty_setup.php';
    require_once "data/Tracker.php";
    require_once 'include/database/PearDatabase.php';
    global $adb, $app_strings, $current_language;
    $current_module_strings = return_module_language($current_language, $module);
    global $list_max_entries_per_page, $urlPrefix, $currentModule, $theme, $theme_path, $theme_path, $mod_strings;
    $smarty = new vtigerCRM_Smarty();
    if (!isset($where)) {
        $where = "";
    }
    $button = '<table cellspacing=0 cellpadding=2><tr><td>' . $button . '</td></tr></table>';
    // Added to have Purchase Order as form Title
    $theme_path = "themes/" . $theme . "/";
    $image_path = $theme_path . "images/";
    $smarty->assign("MOD", $mod_strings);
    $smarty->assign("APP", $app_strings);
    $smarty->assign("THEME", $theme);
    $smarty->assign("IMAGE_PATH", $image_path);
    $smarty->assign("MODULE", $relatedmodule);
    // We do not have RelatedListView in Detail View mode of Calendar module. So need to skip it.
    if ($module != 'Calendar') {
        $focus->initSortByField($relatedmodule);
    }
    //Retreive the list from Database
    //Appending the security parameter Security fix by Don
    if ($relatedmodule != 'Faq' && $relatedmodule != 'PriceBook' && $relatedmodule != 'Vendors' && $relatedmodule != 'Users') {
        global $current_user;
        $secQuery = getNonAdminAccessControlQuery($relatedmodule, $current_user);
        if (strlen($secQuery) > 1) {
            $query = appendFromClauseToQuery($query, $secQuery);
        }
    }
    if ($relatedmodule == 'Leads') {
        $query .= " AND vtiger_leaddetails.converted = 0";
    }
    if (isset($where) && $where != '') {
        $query .= ' and ' . $where;
    }
    if (!$_SESSION['rlvs'][$module][$relatedmodule]) {
        $modObj = new ListViewSession();
        $modObj->sortby = $focus->default_order_by;
        $modObj->sorder = $focus->default_sort_order;
        $_SESSION['rlvs'][$module][$relatedmodule] = get_object_vars($modObj);
    }
    if (!empty($_REQUEST['order_by'])) {
        if (method_exists($focus, getSortOrder)) {
            $sorder = $focus->getSortOrder();
        }
        if (method_exists($focus, getOrderBy)) {
            $order_by = $focus->getOrderBy();
        }
        if (isset($order_by) && $order_by != '') {
            $_SESSION['rlvs'][$module][$relatedmodule]['sorder'] = $sorder;
            $_SESSION['rlvs'][$module][$relatedmodule]['sortby'] = $order_by;
        }
    } elseif ($_SESSION['rlvs'][$module][$relatedmodule]) {
        $sorder = $_SESSION['rlvs'][$module][$relatedmodule]['sorder'];
        $order_by = $_SESSION['rlvs'][$module][$relatedmodule]['sortby'];
    } else {
        $order_by = $focus->default_order_by;
        $sorder = $focus->default_sort_order;
    }
    //Added by Don for AssignedTo ordering issue in Related Lists
    $query_order_by = $order_by;
    if ($order_by == 'smownerid') {
        $userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users');
        $query_order_by = "case when (vtiger_users.user_name not like '') then {$userNameSql} else vtiger_groups.groupname end ";
    } elseif ($order_by != 'crmid' && !empty($order_by)) {
        $tabname = getTableNameForField($relatedmodule, $order_by);
        if ($tabname !== '' and $tabname != NULL) {
            $query_order_by = $tabname . "." . $query_order_by;
        }
    }
    if (!empty($query_order_by)) {
        $query .= ' ORDER BY ' . $query_order_by . ' ' . $sorder;
    }
    if ($relatedmodule == 'Calendar') {
        $mod_listquery = "activity_listquery";
    } else {
        $mod_listquery = strtolower($relatedmodule) . "_listquery";
    }
    $_SESSION[$mod_listquery] = $query;
    $url_qry = "&order_by=" . $order_by . "&sorder=" . $sorder;
    $computeCount = isset($_REQUEST['withCount']) ? $_REQUEST['withCount'] : '';
//.........这里部分代码省略.........

function getDashboardQuery($query, $module)
{
    global $current_user;
    $secQuery = getNonAdminAccessControlQuery($module, $current_user);
    if (strlen($secQuery) > 1) {
        $query = appendFromClauseToQuery($query, $secQuery);
    }
    return $query;
}

 function generateRelatedListModels($model)
 {
     global $adb;
     $lists = $adb->pquery("select vtiger_tab.name as relmodulename, vtiger_relatedlists.name as listtype from vtiger_relatedlists inner join vtiger_tab on vtiger_tab.tabid=vtiger_relatedlists.related_tabid where vtiger_relatedlists.tabid=? and vtiger_relatedlists.name in ('get_related_list', 'get_dependents_list')", array(getTabid($this->moduleName)));
     for ($i = 0; $i < $adb->num_rows($lists); $i++) {
         $relmodulename = $adb->query_result($lists, $i, 'relmodulename');
         $listtype = $adb->query_result($lists, $i, 'listtype');
         if ($listtype == 'get_related_list') {
             $listrecords = $adb->pquery('select vtiger_crmentityrel.relcrmid from vtiger_crmentityrel inner join vtiger_crmentity on (vtiger_crmentity.crmid=vtiger_crmentityrel.crmid and vtiger_crmentity.deleted=0) where vtiger_crmentityrel.crmid=? and vtiger_crmentityrel.relmodule=?', array($this->focus->id, $relmodulename));
             for ($j = 0; $j < 10; $j++) {
                 $entity = CRMEntity::getInstance($relmodulename);
                 if ($j < $adb->num_rows($listrecords)) {
                     $relcrmid = $adb->query_result($listrecords, $j, 'relcrmid');
                     $entity->retrieve_entity_info($relcrmid, $relmodulename);
                 }
                 $this->generateEntityModel($entity, $relmodulename, 'Related' . $relmodulename . ($j + 1) . '_', $model);
             }
         } else {
             if ($listtype == 'get_dependents_list') {
                 $dependentFieldSql = $adb->pquery("SELECT tabid, fieldname, columnname FROM vtiger_field WHERE uitype='10' AND" . " fieldid IN (SELECT fieldid FROM vtiger_fieldmodulerel WHERE relmodule=? AND module=?)", array($this->moduleName, $relmodulename));
                 $numOfFields = $adb->num_rows($dependentFieldSql);
                 if ($numOfFields > 0) {
                     $dependentColumn = $adb->query_result($dependentFieldSql, 0, 'columnname');
                     $dependentField = $adb->query_result($dependentFieldSql, 0, 'fieldname');
                     $thisModule = $this->focus;
                     $other = CRMEntity::getInstance($relmodulename);
                     vtlib_setup_modulevars($this->moduleName, $this->focus);
                     vtlib_setup_modulevars($relmodulename, $other);
                     $query = "SELECT vtiger_crmentity.*";
                     $userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users');
                     $query .= ", CASE WHEN (vtiger_users.user_name NOT LIKE '') THEN {$userNameSql} ELSE vtiger_groups.groupname END AS user_name";
                     $more_relation = '';
                     if (!empty($other->related_tables)) {
                         foreach ($other->related_tables as $tname => $relmap) {
                             $query .= ", {$tname}.*";
                             // Setup the default JOIN conditions if not specified
                             if (empty($relmap[1])) {
                                 $relmap[1] = $other->table_name;
                             }
                             if (empty($relmap[2])) {
                                 $relmap[2] = $relmap[0];
                             }
                             $more_relation .= " LEFT JOIN {$tname} ON {$tname}.{$relmap['0']} = {$relmap['1']}.{$relmap['2']}";
                         }
                     }
                     $query .= " FROM {$other->table_name}";
                     $query .= " INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = {$other->table_name}.{$other->table_index}";
                     $query .= " INNER  JOIN {$thisModule->table_name}   ON {$thisModule->table_name}.{$thisModule->table_index} = {$other->table_name}.{$dependentColumn}";
                     $query .= $more_relation;
                     $query .= " LEFT  JOIN vtiger_users        ON vtiger_users.id = vtiger_crmentity.smownerid";
                     $query .= " LEFT  JOIN vtiger_groups       ON vtiger_groups.groupid = vtiger_crmentity.smownerid";
                     $query .= " WHERE vtiger_crmentity.deleted = 0 AND {$thisModule->table_name}.{$thisModule->table_index} = {$thisModule->id}";
                     if ($relmodulename != 'Faq' && $relmodulename != 'PriceBook' && $relmodulename != 'Users') {
                         global $current_user;
                         $secQuery = getNonAdminAccessControlQuery($relmodulename, $current_user);
                         if (strlen($secQuery) > 1) {
                             $query = appendFromClauseToQuery($query, $secQuery);
                         }
                     }
                     $listrecords = $adb->pquery($query, array());
                     for ($j = 0; $j < 10; $j++) {
                         $entity = CRMEntity::getInstance($relmodulename);
                         if ($j < $adb->num_rows($listrecords)) {
                             $relcrmid = $adb->query_result($listrecords, $j, 'crmid');
                             $entity->retrieve_entity_info($relcrmid, $relmodulename);
                         }
                         $this->generateEntityModel($entity, $relmodulename, 'Related' . $relmodulename . ($j + 1) . '_', $model);
                     }
                 }
             }
         }
     }
 }

function __getRLQueryFromJoins($query, $meta)
{
    global $log;
    if ($meta->getEntityName() == 'Emails') {
        // this query is non-standard, I try to fix it a bit to get it working
        $chgFrom = "from vtiger_activity, vtiger_seactivityrel, vtiger_contactdetails, vtiger_users, vtiger_crmentity";
        $chgTo = "from vtiger_activity\n\t\t\t\t\tinner join vtiger_crmentity on vtiger_crmentity.crmid = vtiger_activity.activityid\n\t\t\t\t\tleft join vtiger_seactivityrel on vtiger_seactivityrel.activityid = vtiger_activity.activityid\n\t\t\t\t\tleft join vtiger_users on vtiger_users.id=vtiger_crmentity.smownerid\n\t\t\t\t\tleft join vtiger_contactdetails on vtiger_contactdetails.contactid = vtiger_seactivityrel.crmid ";
        $query = str_replace($chgFrom, $chgTo, $query);
    }
    $etable = $meta->getEntityBaseTable();
    $eindex = $meta->getIdColumn();
    $posFrom = stripos($query, " from ");
    foreach ($meta->getEntityTableIndexList() as $tbl => $fld) {
        if ($tbl == 'vtiger_crmentity' or $tbl == $etable) {
            continue;
        }
        // these are always in the query
        if (stripos($query, "join {$tbl}") > 0) {
            continue;
        }
        // it is already joined
        if (stripos($query, $tbl) > $posFrom) {
            continue;
        }
        // the table is present after FROM
        if ($tbl == 'vtiger_ticketcomments' or $tbl == 'vtiger_faqcomments') {
            continue;
        }
        // these are obtained through comments
        $secQuery = " left join {$tbl} on {$tbl}.{$fld} = {$etable}.{$eindex} ";
        $query = appendFromClauseToQuery($query, $secQuery);
    }
    return $query;
}

 /**
  * Function to get relation query for particular module with function name
  * @param <record> $recordId
  * @param <String> $functionName
  * @param Vtiger_Module_Model $relatedModule
  * @return <String>
  */
 public function getRelationQuery($recordId, $functionName, $relatedModule)
 {
     if ($functionName === 'get_activities') {
         $focus = CRMEntity::getInstance($this->getName());
         $focus->id = $recordId;
         $entityIds = $focus->getRelatedContactsIds();
         $entityIds = implode(',', $entityIds);
         $userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users');
         $query = "SELECT CASE WHEN (vtiger_users.user_name not like '') THEN {$userNameSql} ELSE vtiger_groups.groupname END AS user_name,\n\t\t\t\t\t\tvtiger_crmentity.*, vtiger_activity.activitytype, vtiger_activity.subject, vtiger_activity.date_start, vtiger_activity.time_start,\n\t\t\t\t\t\tvtiger_activity.recurringtype, vtiger_activity.due_date, vtiger_activity.time_end, vtiger_activity.visibility, vtiger_seactivityrel.crmid AS parent_id,\n\t\t\t\t\t\tCASE WHEN (vtiger_activity.activitytype = 'Task') THEN (vtiger_activity.status) ELSE (vtiger_activity.eventstatus) END AS status\n\t\t\t\t\t\tFROM vtiger_activity\n\t\t\t\t\t\tINNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_activity.activityid\n\t\t\t\t\t\tLEFT JOIN vtiger_seactivityrel ON vtiger_seactivityrel.activityid = vtiger_activity.activityid\n\t\t\t\t\t\tLEFT JOIN vtiger_cntactivityrel ON vtiger_cntactivityrel.activityid = vtiger_activity.activityid\n\t\t\t\t\t\tLEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\t\t\t\tLEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid\n\t\t\t\t\t\t\tWHERE vtiger_crmentity.deleted = 0 AND vtiger_activity.activitytype <> 'Emails'\n\t\t\t\t\t\t\t\tAND (vtiger_seactivityrel.crmid = " . $recordId;
         if ($entityIds) {
             $query .= " OR vtiger_cntactivityrel.contactid IN (" . $entityIds . "))";
         } else {
             $query .= ")";
         }
         $relatedModuleName = $relatedModule->getName();
         $query .= $this->getSpecificRelationQuery($relatedModuleName);
         $nonAdminQuery = $this->getNonAdminAccessControlQueryForRelation($relatedModuleName);
         if ($nonAdminQuery) {
             $query = appendFromClauseToQuery($query, $nonAdminQuery);
         }
         // There could be more than one contact for an activity.
         $query .= ' GROUP BY vtiger_activity.activityid';
     } else {
         $query = parent::getRelationQuery($recordId, $functionName, $relatedModule);
     }
     return $query;
 }