/** * 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; } }