/**
* 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) . ")";
}
}
$lookupQueryObj->ReplaceFieldsWithDummies($lookupPSet->getBinaryFieldsIndices());
} else {
$LookupSQLTable = "SELECT ";
$lwLinkField = $lookupConnection->addFieldWrappers($gSettings->getLinkField($f));
if ($gSettings->isLookupUnique($f)) {
$LookupSQLTable .= "DISTINCT ";
}
$LookupSQLTable .= $cipherer->GetLookupFieldName($lwLinkField, $f, null, true);
if ($lookupConnection->dbType == nDATABASE_MSSQLServer) {
if ($strUniqueOrderBy && $gSettings->isLookupUnique($f)) {
$LookupSQLTable .= "," . $lookupConnection->addFieldWrappers($strUniqueOrderBy);
}
}
if (!$linkAndDisplaySame) {
$LookupSQLTable .= "," . ($lwDisplayField == $lwLinkField ? $cipherer->GetFieldName($lwDisplayField, $f, true) : $lwDisplayField);
}
$LookupSQLTable .= " FROM " . $lookupConnection->addTableWrappers($lookupTable) . " ";
}
$strLookupWhere = GetLWWhere($f, $pageType, $strTableName);
if ($strLookupWhere) {
$strLookupWhere = " (" . $strLookupWhere . ") AND ";
}
if ($LookupType == LT_QUERY) {
if ($gSettings->getCustomDisplay($f)) {
$likeField = $searchByLinkField ? $linkFieldName : $displayFieldName;
} else {
$likeField = RunnerPage::_getFieldSQLDecrypt($searchByLinkField ? $linkFieldName : $displayFieldName, $lookupConnection, $lookupPSet, $cipherer);
}
} else {
$likeField = $cipherer->GetFieldName($lwDisplayField, $f);
function buildLookupSQL($pageType, $field, $table, $parentVal, $childVal = "", $doCategoryFilter = true, $doValueFilter = false, $addCategoryField = false, $doWhereFilter = true, $oneRecordMode = false, $doValueFilterByLinkField = false)
{
global $strTableName;
if (!strlen($table)) {
$table = $strTableName;
}
$pSet = new ProjectSettings($table, $pageType);
// read settings
$nLookupType = $pSet->getLookupType($field);
if ($nLookupType != LT_LOOKUPTABLE && $nLookupType != LT_QUERY) {
return "";
}
$lookupTable = $pSet->getLookupTable($field);
$displayFieldName = $pSet->getDisplayField($field);
$linkFieldName = $pSet->getLinkField($field);
$linkAndDisplaySame = $displayFieldName == $linkFieldName;
$bUnique = $pSet->isLookupUnique($field);
$strLookupWhere = GetLWWhere($field, $pageType, $table);
$strOrderBy = $pSet->getLookupOrderBy($field);
if (strlen($strOrderBy)) {
$strOrderBy = GetFullFieldName($strOrderBy, $lookupTable);
if ($pSet->isLookupDesc($field)) {
$strOrderBy .= ' DESC';
}
}
$bDesc = $pSet->isLookupDesc($field);
$strCategoryFilter = $pSet->getCategoryFilter($field);
if ($nLookupType == LT_QUERY) {
$lookupPSet = new ProjectSettings($lookupTable, $pageType);
$cipherer = new RunnerCipherer($lookupTable, $lookupPSet);
} else {
$cipherer = new RunnerCipherer($table, $pSet);
}
if ($doCategoryFilter) {
if ($nLookupType == LT_QUERY) {
$parentVal = $cipherer->MakeDBValue($strCategoryFilter, $parentVal, "", $lookupTable, true);
} else {
$parentVal = make_db_value($pSet->getCategoryControl($field), $parentVal, '', '', $table);
}
}
if ($doValueFilter) {
if ($pageType != PAGE_SEARCH || $doValueFilterByLinkField) {
if ($nLookupType == LT_QUERY) {
$childWhereField = $pSet->getLWLinkField($field, false);
} else {
$childWhereField = $pSet->getLWLinkField($field, true);
}
} else {
if ($nLookupType == LT_QUERY) {
$childWhereField = $pSet->getLWDisplayField($field, false);
} else {
$childWhereField = $pSet->getLWDisplayField($field, true);
}
}
if ($nLookupType == LT_QUERY) {
$childVal = $cipherer->MakeDBValue($childWhereField, $childVal, "", $lookupTable, true);
} else {
if ($linkAndDisplaySame) {
$childVal = make_db_value($field, $childVal, '', '', $table);
} else {
$childVal = add_db_quotes($field, $childVal, $table, 200);
}
}
}
// build Where clause
$categoryWhere = "";
$childWhere = "";
if ($pSet->useCategory($field) && $doCategoryFilter) {
$condition = "=" . $parentVal;
if ($parentVal === "null") {
$condition = " is null";
}
if ($nLookupType == LT_QUERY) {
$categoryWhere = $cipherer->GetFieldName(AddFieldWrappers($strCategoryFilter), $strCategoryFilter) . $condition;
} else {
$categoryWhere = AddFieldWrappers($strCategoryFilter) . $condition;
}
}
if ($doValueFilter) {
$condition = "=" . $childVal;
if ($childVal === "null") {
$condition = " is null";
}
if ($nLookupType == LT_QUERY) {
if ($pageType != PAGE_SEARCH || $pSet->lookupControlType($field) == LCT_LIST || $doValueFilterByLinkField) {
$childWhere = GetFullFieldName($pSet->getLinkField($field), $lookupTable, false) . $condition;
} else {
if (!$pSet->getCustomDisplay($field)) {
$childWhere = $cipherer->GetFieldName($lookupPSet->getFullNameField($displayFieldName), $field) . $condition;
} else {
$childWhere = $pSet->getDisplayField($field) . $condition;
}
}
} else {
if ($pageType != PAGE_SEARCH || $doValueFilterByLinkField) {
$childWhere = $pSet->getLWLinkField($field, true) . $condition;
} else {
$childWhere = $pSet->getLWDisplayField($field, true) . $condition;
}
}
}
$strWhere = "";
if ($doWhereFilter && strlen($strLookupWhere)) {
$strWhere = "(" . $strLookupWhere . ")";
}
if (strlen($categoryWhere)) {
if (strlen($strWhere)) {
$strWhere .= " AND ";
}
$strWhere .= $categoryWhere;
}
if (strlen($childWhere)) {
if (strlen($strWhere)) {
$strWhere .= " AND ";
}
$strWhere .= $childWhere;
}
// build SQL string
if ($nLookupType == LT_QUERY) {
$lookupQueryObj = $lookupPSet->getSQLQuery();
if ($pSet->getCustomDisplay($field)) {
$lookupQueryObj->AddCustomExpression($displayFieldName, $lookupPSet, $table, $field);
}
$lookupQueryObj->ReplaceFieldsWithDummies($lookupPSet->getBinaryFieldsIndices());
$strWhere = whereAdd($lookupQueryObj->m_where->toSql($lookupQueryObj), $strWhere);
$LookupSQL = $lookupQueryObj->toSql($strWhere, strlen($strOrderBy) ? ' ORDER BY ' . $strOrderBy : null, null, $oneRecordMode);
} else {
$LookupSQL = "SELECT ";
if ($bUnique && !$oneRecordMode) {
$LookupSQL .= "DISTINCT ";
}
$LookupSQL .= $pSet->getLWLinkField($field);
if (!$linkAndDisplaySame) {
$LookupSQL .= "," . $pSet->getLWDisplayField($field);
}
if ($addCategoryField && strlen($strCategoryFilter)) {
$LookupSQL .= "," . AddFieldWrappers($strCategoryFilter);
}
$LookupSQL .= " FROM " . AddTableWrappers($lookupTable);
if (strlen($strWhere)) {
$LookupSQL .= " WHERE " . $strWhere;
}
// order by clause
if (strlen($strOrderBy)) {
$LookupSQL .= " ORDER BY " . AddTableWrappers($lookupTable) . "." . $strOrderBy;
}
if ($oneRecordMode) {
$LookupSQL .= " limit 1";
}
}
return $LookupSQL;
}
/**
* DEPRECATED! Use RunnerPage::_getFieldSQLDecrypt instead
* Return the full database field original name
*
* @param string $field
* @param string $table The datasource table name
* @param boolean $addAs OPTIONAL
*
* @return String
* @intellisense
* @deprecated
*/
function GetFullFieldName($field, $table = "", $addAs = true, $connection = null)
{
global $strTableName, $cman;
if ($table == "") {
$table = $strTableName;
}
if (!$connection) {
$connection = $cman->byTable($table);
}
$pSet = new ProjectSettings($table);
$fname = RunnerPage::_getFieldSQL($field, $connection, $pSet);
if ($pSet->hasEncryptedFields() && !isEncryptionByPHPEnabled()) {
$cipherer = new RunnerCipherer($table);
return $cipherer->GetFieldName($fname, $field) . ($cipherer->isFieldEncrypted($field) && $addAs ? " as " . $connection->addFieldWrappers($field) : "");
}
return $fname;
}
/**
* return the full database field original name
* @intellisense
*/
function GetFullFieldName($field, $table = "", $addAs = true)
{
if($table == "")
{
global $strTableName;
$table = $strTableName;
}
$pSet = new ProjectSettings($table);
$fname = $pSet->getFullNameField($field);
if($pSet->hasEncryptedFields() && !isEncryptionByPHPEnabled())
{
$cipherer = new RunnerCipherer($table);
return $cipherer->GetFieldName($fname, $field)
.($cipherer->isFieldEncrypted($field) && $addAs ? " as ".AddFieldWrappers($field) : "");
}
return $fname;
}
