PHP CSQLWhere::MakeOperation方法代碼示例

 /**
  * @param array $arOrder
  * @param array $arFilter
  * @param array $arSelect
  * @return CDBResult
  * Obsolete. Always select all record from database. Please use GetListEx instead.
  */
 public static function GetList($arOrder = array('DATE_CREATE' => 'DESC'), $arFilter = array(), $arSelect = array(), $nPageTop = false)
 {
     global $DB, $USER_FIELD_MANAGER;
     // fields
     $arFields = array('ID' => 'L.ID', 'COMMENTS' => 'L.COMMENTS', 'ADDITIONAL_INFO' => 'L.ADDITIONAL_INFO', 'LOCATION_ID' => 'L.LOCATION_ID', 'TITLE' => 'L.TITLE', 'LEAD_ID' => 'L.LEAD_ID', 'COMPANY_ID' => 'L.COMPANY_ID', 'COMPANY_TITLE' => 'C.TITLE', 'CONTACT_ID' => 'L.CONTACT_ID', 'CONTACT_FULL_NAME' => 'CT.FULL_NAME', 'STAGE_ID' => 'L.STAGE_ID', 'CLOSED' => 'L.CLOSED', 'TYPE_ID' => 'L.TYPE_ID', 'PRODUCT_ID' => 'L.PRODUCT_ID', 'PROBABILITY' => 'L.PROBABILITY', 'OPPORTUNITY' => 'L.OPPORTUNITY', 'TAX_VALUE' => 'L.TAX_VALUE', 'CURRENCY_ID' => 'L.CURRENCY_ID', 'OPPORTUNITY_ACCOUNT' => 'L.OPPORTUNITY_ACCOUNT', 'TAX_VALUE_ACCOUNT' => 'L.TAX_VALUE_ACCOUNT', 'ACCOUNT_CURRENCY_ID' => 'L.ACCOUNT_CURRENCY_ID', 'BEGINDATE' => $DB->DateToCharFunction('L.BEGINDATE'), 'CLOSEDATE' => $DB->DateToCharFunction('L.CLOSEDATE'), 'EVENT_ID' => 'L.EVENT_ID', 'EVENT_DATE' => $DB->DateToCharFunction('L.EVENT_DATE'), 'EVENT_DESCRIPTION' => 'L.EVENT_DESCRIPTION', 'ASSIGNED_BY' => 'L.ASSIGNED_BY_ID', 'ASSIGNED_BY_ID' => 'L.ASSIGNED_BY_ID', 'CREATED_BY' => 'L.CREATED_BY_ID', 'CREATED_BY_ID' => 'L.CREATED_BY_ID', 'MODIFY_BY' => 'L.MODIFY_BY_ID', 'MODIFY_BY_ID' => 'L.MODIFY_BY_ID', 'DATE_CREATE' => $DB->DateToCharFunction('L.DATE_CREATE'), 'DATE_MODIFY' => $DB->DateToCharFunction('L.DATE_MODIFY'), 'OPENED' => 'L.OPENED', 'EXCH_RATE' => 'L.EXCH_RATE', 'ORIGINATOR_ID' => 'L.ORIGINATOR_ID', 'ORIGIN_ID' => 'L.ORIGIN_ID', 'ASSIGNED_BY_LOGIN' => 'U.LOGIN', 'ASSIGNED_BY_NAME' => 'U.NAME', 'ASSIGNED_BY_LAST_NAME' => 'U.LAST_NAME', 'ASSIGNED_BY_SECOND_NAME' => 'U.SECOND_NAME', 'CREATED_BY_LOGIN' => 'U2.LOGIN', 'CREATED_BY_NAME' => 'U2.NAME', 'CREATED_BY_LAST_NAME' => 'U2.LAST_NAME', 'CREATED_BY_SECOND_NAME' => 'U2.SECOND_NAME', 'MODIFY_BY_LOGIN' => 'U3.LOGIN', 'MODIFY_BY_NAME' => 'U3.NAME', 'MODIFY_BY_LAST_NAME' => 'U3.LAST_NAME', 'MODIFY_BY_SECOND_NAME' => 'U3.SECOND_NAME');
     $arSqlSelect = array();
     $sSqlJoin = '';
     if (count($arSelect) == 0) {
         $arSelect = array_merge(array_keys($arFields), array('UF_*'));
     }
     $obQueryWhere = new CSQLWhere();
     $arFilterField = $arSelect;
     foreach ($arFilter as $sKey => $sValue) {
         $arField = $obQueryWhere->MakeOperation($sKey);
         $arFilterField[] = $arField['FIELD'];
     }
     if (in_array('ASSIGNED_BY_LOGIN', $arFilterField) || in_array('ASSIGNED_BY', $arFilterField)) {
         $arSelect[] = 'ASSIGNED_BY_LOGIN';
         $arSelect[] = 'ASSIGNED_BY_NAME';
         $arSelect[] = 'ASSIGNED_BY_LAST_NAME';
         $arSelect[] = 'ASSIGNED_BY_SECOND_NAME';
         $sSqlJoin .= ' LEFT JOIN b_user U ON L.ASSIGNED_BY_ID = U.ID ';
     }
     if (in_array('CREATED_BY_LOGIN', $arFilterField)) {
         $arSelect[] = 'CREATED_BY';
         $arSelect[] = 'CREATED_BY_LOGIN';
         $arSelect[] = 'CREATED_BY_NAME';
         $arSelect[] = 'CREATED_BY_LAST_NAME';
         $arSelect[] = 'CREATED_BY_SECOND_NAME';
         $sSqlJoin .= ' LEFT JOIN b_user U2 ON L.CREATED_BY_ID = U2.ID ';
     }
     if (in_array('MODIFY_BY_LOGIN', $arFilterField)) {
         $arSelect[] = 'MODIFY_BY';
         $arSelect[] = 'MODIFY_BY_LOGIN';
         $arSelect[] = 'MODIFY_BY_NAME';
         $arSelect[] = 'MODIFY_BY_LAST_NAME';
         $arSelect[] = 'MODIFY_BY_SECOND_NAME';
         $sSqlJoin .= ' LEFT JOIN b_user U3 ON  L.MODIFY_BY_ID = U3.ID ';
     }
     if (in_array('COMPANY_ID', $arFilterField) || in_array('COMPANY_TITLE', $arFilterField)) {
         $arSelect[] = 'COMPANY_ID';
         $arSelect[] = 'COMPANY_TITLE';
         $sSqlJoin .= ' LEFT JOIN b_crm_company C ON L.COMPANY_ID = C.ID ';
     }
     if (in_array('CONTACT_ID', $arFilterField) || in_array('CONTACT_FULL_NAME', $arFilterField)) {
         $arSelect[] = 'CONTACT_ID';
         $arSelect[] = 'CONTACT_FULL_NAME';
         $sSqlJoin .= ' LEFT JOIN b_crm_contact CT ON L.CONTACT_ID = CT.ID ';
     }
     foreach ($arSelect as $field) {
         $field = strtoupper($field);
         if (array_key_exists($field, $arFields)) {
             $arSqlSelect[$field] = $arFields[$field] . ($field != '*' ? ' AS ' . $field : '');
         }
     }
     if (!isset($arSqlSelect['ID'])) {
         $arSqlSelect['ID'] = $arFields['ID'];
     }
     $sSqlSelect = implode(",\n", $arSqlSelect);
     $obUserFieldsSql = new CUserTypeSQL();
     $obUserFieldsSql->SetEntity(self::$sUFEntityID, 'L.ID');
     $obUserFieldsSql->SetSelect($arSelect);
     $obUserFieldsSql->SetFilter($arFilter);
     $obUserFieldsSql->SetOrder($arOrder);
     $arSqlSearch = array();
     // check permissions
     $sSqlPerm = '';
     if (!CCrmPerms::IsAdmin() && (!array_key_exists('CHECK_PERMISSIONS', $arFilter) || $arFilter['CHECK_PERMISSIONS'] !== 'N')) {
         $arPermType = array();
         if (!isset($arFilter['PERMISSION'])) {
             $arPermType[] = 'READ';
         } else {
             $arPermType = is_array($arFilter['PERMISSION']) ? $arFilter['PERMISSION'] : array($arFilter['PERMISSION']);
         }
         $sSqlPerm = self::BuildPermSql('L', $arPermType);
         if ($sSqlPerm === false) {
             $CDBResult = new CDBResult();
             $CDBResult->InitFromArray(array());
             return $CDBResult;
         }
         if (strlen($sSqlPerm) > 0) {
             $sSqlPerm = ' AND ' . $sSqlPerm;
         }
     }
     // where
     $arWhereFields = array('ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.ID', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'LEAD_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.LEAD_ID', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'CONTACT_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.CONTACT_ID', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'CONTACT_FULL_NAME' => array('TABLE_ALIAS' => 'CT', 'FIELD_NAME' => 'CT.FULL_NAME', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'COMPANY_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.COMPANY_ID', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'COMPANY_TITLE' => array('TABLE_ALIAS' => 'C', 'FIELD_NAME' => 'C.TITLE', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'STATE_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.STATE_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'STAGE_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.STAGE_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'TYPE_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.TYPE_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'PRODUCT_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.PRODUCT_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'CURRENCY_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.CURRENCY_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'OPPORTUNITY' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.OPPORTUNITY', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'TAX_VALUE' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.TAX_VALUE', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'ACCOUNT_CURRENCY_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.ACCOUNT_CURRENCY_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'OPPORTUNITY_ACCOUNT' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.OPPORTUNITY_ACCOUNT', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'TAX_VALUE_ACCOUNT' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.TAX_VALUE_ACCOUNT', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'TITLE' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.TITLE', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'CLOSED' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.CLOSED', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'COMMENTS' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.COMMENTS', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'ADDITIONAL_INFO' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.ADDITIONAL_INFO', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'LOCATION_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.LOCATION_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'DATE_CREATE' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.DATE_CREATE', 'FIELD_TYPE' => 'datetime', 'JOIN' => false), 'BEGINDATE' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.BEGINDATE', 'FIELD_TYPE' => 'datetime', 'JOIN' => false), 'CLOSEDATE' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.CLOSEDATE', 'FIELD_TYPE' => 'datetime', 'JOIN' => false), 'EVENT_DATE' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.EVENT_DATE', 'FIELD_TYPE' => 'datetime', 'JOIN' => false), 'DATE_MODIFY' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.DATE_MODIFY', 'FIELD_TYPE' => 'datetime', 'JOIN' => false), 'PROBABILITY' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.PROBABILITY', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'EVENT_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.EVENT_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'CREATED_BY_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.CREATED_BY_ID', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'ASSIGNED_BY_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.ASSIGNED_BY_ID', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'OPENED' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.OPENED', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'MODIFY_BY_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.MODIFY_BY_ID', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'EXCH_RATE' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.EXCH_RATE', 'FIELD_TYPE' => 'int', 'JOIN' => false), 'ORIGINATOR_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.ORIGINATOR_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false), 'ORIGIN_ID' => array('TABLE_ALIAS' => 'L', 'FIELD_NAME' => 'L.ORIGIN_ID', 'FIELD_TYPE' => 'string', 'JOIN' => false));
     $obQueryWhere->SetFields($arWhereFields);
     if (!is_array($arFilter)) {
         $arFilter = array();
     }
     $sQueryWhereFields = $obQueryWhere->GetQuery($arFilter);
     $sSqlSearch = '';
     foreach ($arSqlSearch as $r) {
//.........這裏部分代碼省略.........

 protected function getJoinCswFields($reference)
 {
     $fields = array();
     foreach ($reference as $k => $v) {
         if ($k === 'LOGIC') {
             continue;
         }
         if (is_numeric($k)) {
             $fields = array_merge($fields, $this->getJoinCswFields($v));
         } else {
             // key
             $csw_result = \CSQLWhere::MakeOperation($k);
             list($field, ) = array_values($csw_result);
             $fields[$field] = array('TABLE_ALIAS' => 'alias', 'FIELD_NAME' => $field, 'FIELD_TYPE' => 'string', 'MULTIPLE' => '', 'JOIN' => '');
             // no need to add values as csw fields
         }
     }
     return $fields;
 }