/** * 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; }
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; }
/** * Get master record * * User function * Using only in events by users * @return{array} * @intellisense */ function getMasterRecord() { if (!is_null($this->masterRecordData)) return $this->masterRecordData; if(!$this->masterTable) return null; global $detailsTablesData, $masterTablesData; $settings = new ProjectSettings($this->masterTable, PAGE_LIST); $where = ""; $masterTablesInfoArr = $this->pSet->getMasterTablesArr($this->tName); for($i=0; $i < count($masterTablesInfoArr); $i++) { if($this->masterTable == $masterTablesInfoArr[$i]['mDataSourceTable']) { $masterKeys = $this->getActiveMasterKeys(); $cipherer = new RunnerCipherer($this->masterTable); for($j=0; $j < count($masterTablesInfoArr[$i]['masterKeys']); $j++) { if($j) $where.= " and "; $mKey = $masterTablesInfoArr[$i]['masterKeys'][$j]; $where.= $settings->getFullNameField($mKey)."=".$cipherer->MakeDBValue($mKey, $masterKeys[$j], "", "", true); } } } if(!$where) return null; $masterQuery = $settings->getSQLQuery(); $str = SecuritySQL("Search"); if(strlen($str)) $where.= " and ".$str; $strWhere = whereAdd($masterQuery->WhereToSql(),$where); if(strlen($strWhere)) $strWhere = " where ".$strWhere." "; $strSQL = $masterQuery->HeadToSql().' '.$masterQuery->FromToSql().$strWhere.$masterQuery->TailToSql(); global $conn; LogInfo($strSQL); $rs = db_query($strSQL,$conn); $this->masterRecordData = $cipherer->DecryptFetchedArray($rs); return $this->masterRecordData; }