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