PHP RunnerPage::_getFieldSQL方法代码示例

 /**
  * Get a filter's SQL where clause condition 
  *
  * @param String fName
  * @param Array intervalData
  * @param Object pSet
  * @param Object cipherer
  * @param String tableName
  * @return String
  */
 static function getIntervalFilterWhere($fName, $intervalData, $pSet, $cipherer, $tableName, $connection)
 {
     $fullFieldName = RunnerPage::_getFieldSQL($fName, $connection, $pSet);
     if ($intervalData["remainder"]) {
         $index = $intervalData["index"];
         $intervalsData = $pSet->getFilterIntervals($fName);
         $conditions = array();
         foreach ($intervalsData as $intervalData) {
             if ($intervalData["noLimits"]) {
                 return "1=0";
             }
             if ($intervalData["index"] == $index) {
                 continue;
             }
             $conditions[] = FilterIntervalList::getLimitsConditions($fName, $fullFieldName, $intervalData, $cipherer, $tableName, $connection, true);
         }
         return implode(" AND ", $conditions);
     }
     if ($intervalData["noLimits"]) {
         return $fullFieldName . " is not NULL AND " . $fullFieldName . " <> '' ";
     }
     return FilterIntervalList::getLimitsConditions($fName, $fullFieldName, $intervalData, $cipherer, $tableName, $connection);
 }

$pSet = new ProjectSettings($strTableName, $pageType);
$denyChecking = $pSet->allowDuplicateValues($fieldName);
$denyChecking = $denyChecking && ($strTableName != "DashboardUsers" || $fieldName != $cUserNameField && $fieldName != $cEmailField);
if ($denyChecking) {
    $returnJSON = array("success" => false, "error" => "Duplicated values are allowed");
    echo printJSON($returnJSON);
    return;
}
$cipherer = new RunnerCipherer($strTableName, $pSet);
if ($cipherer->isFieldEncrypted($fieldName)) {
    $value = $cipherer->MakeDBValue($fieldName, $value, $fieldControlType, true);
} else {
    $value = make_db_value($fieldName, $value, $fieldControlType, "", $strTableName);
}
if ($value == "null") {
    $fieldSQL = RunnerPage::_getFieldSQL($fieldName, $_connection, $pSet);
} else {
    $fieldSQL = RunnerPage::_getFieldSQLDecrypt($fieldName, $_connection, $pSet, $cipherer);
}
$where = $fieldSQL . ($value == "null" ? ' is ' : '=') . $value;
$sql = "SELECT count(*) from " . $_connection->addTableWrappers($pSet->getOriginalTableName()) . " where " . $where;
$qResult = $_connection->query($sql);
if (!$qResult || !($data = $qResult->fetchNumeric())) {
    $returnJSON = array("success" => false, "error" => "Error: Wrong SQL query");
    echo printJSON($returnJSON);
    return;
}
$hasDuplicates = $data[0] ? true : false;
$returnJSON = array("success" => true, "hasDuplicates" => $hasDuplicates, "error" => "");
echo printJSON($returnJSON);
return;

 /**
  * Use for count details recs number, if subQueryes not supported, or keys have different types
  *
  * @param integer $i
  * @param array $detailid
  * @intellisense
  */
 function countDetailsRecsNoSubQ($dInd, &$detailid)
 {
     global $tables_data, $masterTablesData, $detailsTablesData, $allDetailsTablesArr, $cman;
     $dDataSourceTable = $this->allDetailsTablesArr[$dInd]['dDataSourceTable'];
     $detPSet = $this->pSet->getTable($dDataSourceTable);
     $detCipherer = new RunnerCipherer($dDataSourceTable, $detPSet);
     $detConnection = $cman->byTable($dDataSourceTable);
     $detailsQuery = $detPSet->getSQLQuery();
     $dSqlWhere = $detailsQuery->WhereToSql();
     $detailKeys = $detPSet->getDetailKeysByMasterTable($this->tName);
     $securityClause = SecuritySQL("Search", $dDataSourceTable);
     // add where
     if (strlen($securityClause)) {
         $dSqlWhere = whereAdd($dSqlWhere, $securityClause);
     }
     $masterwhere = "";
     foreach ($this->masterKeysByD[$dInd] as $idx => $val) {
         if ($masterwhere) {
             $masterwhere .= " and ";
         }
         $mastervalue = $detCipherer->MakeDBValue($detailKeys[$idx], $detailid[$idx], "", true);
         if ($mastervalue == "null") {
             $masterwhere .= RunnerPage::_getFieldSQL($detailKeys[$idx], $detConnection, $detPSet) . " is NULL ";
         } else {
             $masterwhere .= RunnerPage::_getFieldSQLDecrypt($detailKeys[$idx], $detConnection, $detPSet, $detCipherer) . "=" . $mastervalue;
         }
     }
     return SQLQuery::gSQLRowCount_int($detailsQuery->HeadToSql(), $detailsQuery->FromToSql(), $dSqlWhere, $detailsQuery->GroupByToSql(), $detailsQuery->Having()->toSql($detailsQuery), $masterwhere, "", $detConnection);
 }

/**
 *  DEPRECATED. Use RunnerPage::keysSQLExpression instead
 *	Construct WHERE clause with key values
 * 
 * @param &Array	$keys
 * @param String	$table Teh data source table name OPTIONAL
 * 
 * @return String
 * @intellisense
 * @deprecated
 */
function KeyWhere(&$keys, $table = "")
{
    global $strTableName, $cman;
    if (!$table) {
        $table = $strTableName;
    }
    $strWhere = "";
    $pSet = new ProjectSettings($table);
    $cipherer = new RunnerCipherer($table);
    $connection = $cman->byTable($table);
    $keyFields = $pSet->getTableKeys();
    foreach ($keyFields as $kf) {
        if (strlen($strWhere)) {
            $strWhere .= " and ";
        }
        $value = $cipherer->MakeDBValue($kf, $keys[$kf], "", true);
        if ($connection->dbType == nDATABASE_Oracle) {
            $valueisnull = $value === "null" || $value == "''";
        } else {
            $valueisnull = $value === "null";
        }
        if ($valueisnull) {
            $strWhere .= RunnerPage::_getFieldSQL($kf, $connection, $pSet) . " is null";
        } else {
            $strWhere .= RunnerPage::_getFieldSQLDecrypt($kf, $connection, $pSet, $cipherer) . "=" . $cipherer->MakeDBValue($kf, $keys[$kf], "", true);
        }
    }
    return $strWhere;
}

 /**
  * Get a where condition for a dependent lookup
  * @param String parentVal			The main lookup control's value
  * @param String strCategoryFilter	The main lookup control's field
  * @return String
  */
 protected function getCategoryWhere($parentVal, $strCategoryFilter)
 {
     if (!$this->bUseCategory) {
         return "";
     }
     $strCategoryControl = $this->pageObject->pSetEdit->getCategoryControl($this->field);
     $parentValsPlain = $this->pageObject->pSetEdit->multiSelect($strCategoryControl) ? splitvalues($parentVal) : array($parentVal);
     $parentVals = array();
     foreach ($parentValsPlain as $arKey => $arElement) {
         if ($this->lookupType == LT_QUERY) {
             $parentVals[$arKey] = $this->ciphererDisplay->MakeDBValue($strCategoryFilter, $arElement, '', true);
         } else {
             $parentVals[$arKey] = make_db_value($strCategoryControl, $arElement, '', '', $this->tName);
         }
     }
     $categoryWhere = array();
     foreach ($parentVals as $arKey => $arValue) {
         $condition = $arValue === "null" ? " is null" : "=" . $arValue;
         if ($this->lookupType == LT_QUERY) {
             $categoryWhere[] = $this->ciphererDisplay->GetFieldName(RunnerPage::_getFieldSQL($strCategoryFilter, $this->lookupConnection, $this->lookupPSet), $strCategoryFilter) . $condition;
         } else {
             $categoryWhere[] = $this->lookupConnection->addFieldWrappers($strCategoryFilter) . $condition;
         }
     }
     return count($categoryWhere) == 1 ? $categoryWhere[0] : "(" . implode(" OR ", $categoryWhere) . ")";
 }

 /**
  * Adds sub query for counting details recs number
  */
 protected function addMasterDetailSubQuery()
 {
     for ($i = 0; $i < count($this->allDetailsTablesArr); $i++) {
         $detailData = $this->allDetailsTablesArr[$i];
         if ($detailData['dispChildCount'] || $detailData['hideChild']) {
             $dataSourceTName = $detailData['dDataSourceTable'];
             if (!$this->isDetailTableSubquerySupported($dataSourceTName, $i)) {
                 continue;
             }
             $detailsSettings = $this->pSet->getTable($dataSourceTName);
             $detailsQuery = $detailsSettings->getSQLQuery();
             $detailsSqlWhere = $detailsQuery->WhereToSql();
             $masterWhere = "";
             foreach ($this->masterKeysByD[$i] as $idx => $val) {
                 if ($masterWhere) {
                     $masterWhere .= " and ";
                 }
                 $masterWhere .= $this->cipherer->GetFieldName($this->connection->addTableWrappers("subQuery_cnt") . "." . $this->connection->addFieldWrappers($this->detailKeysByD[$i][$idx]), $this->masterKeysByD[$i][$idx]) . "=" . $this->cipherer->GetFieldName($this->connection->addTableWrappers($this->origTName) . "." . $this->connection->addFieldWrappers($this->masterKeysByD[$i][$idx]), $this->masterKeysByD[$i][$idx]);
             }
             //	add a key field to the select list
             $subQ = "";
             foreach ($this->detailKeysByD[$i] as $k) {
                 if (strlen($subQ)) {
                     $subQ .= ",";
                 }
                 $subQ .= RunnerPage::_getFieldSQL($k, $this->connection, $detailsSettings);
             }
             $subQ = "SELECT " . $subQ . " " . $detailsQuery->FromToSql();
             //	add security where clause for sub query
             $securityClause = SecuritySQL("Search", $dataSourceTName);
             if (strlen($securityClause)) {
                 $subQ .= " WHERE " . whereAdd($detailsSqlWhere, $securityClause);
             } elseif (strlen($detailsSqlWhere)) {
                 $subQ .= " WHERE " . whereAdd("", $detailsSqlWhere);
             }
             // add detail table query tail
             $subQ .= " " . $detailsQuery->TailToSql();
             $countsql = "SELECT count(*) FROM (" . $subQ . ") " . $this->connection->addTableWrappers("subQuery_cnt") . " WHERE " . $masterWhere;
             $this->gsqlHead .= ",(" . $countsql . ") as " . $this->connection->addFieldWrappers($dataSourceTName . "_cnt") . " ";
         }
     }
 }

 function getStringSql($forGroupedField = false)
 {
     $fname = $this->_oldAlgorithm && !$forGroupedField ? RunnerPage::_getFieldSQL($this->_name, $this->_connection, $this->pSet) : cached_ffn($this->_name, $forGroupedField);
     if ($this->_interval > 0) {
         if ($this->_connection->dbType == nDATABASE_MySQL || $this->_connection->dbType == nDATABASE_PostgreSQL) {
             return 'substr(' . $fname . ', 1, ' . $this->_interval . ')';
         }
         if ($this->_connection->dbType == nDATABASE_MSSQLServer) {
             return 'substring(' . $fname . ', 1, ' . $this->_interval . ')';
         }
         if ($this->_connection->dbType == nDATABASE_Access) {
             return 'Mid(' . $fname . ', 1, ' . $this->_interval . ')';
         }
         if ($this->_connection->dbType == nDATABASE_Oracle) {
             return 'SUBSTR(' . $fname . ', 1, ' . $this->_interval . ')';
         }
     } else {
         return $fname;
     }
 }