public static function generateTemplate($sql, $postedParams = [], $field, $paramDefs = [])
{
$returnParams = [];
## find all params
preg_match_all("/\\:[\\w\\d_]+/", $sql, $params);
$model = $field->model;
foreach ($params[0] as $idx => $p) {
if (isset($postedParams[$p])) {
if (is_string($postedParams[$p])) {
$isJs = strpos($postedParams[$p], 'js:') !== false || isset($paramDefs[$p]) && strpos($paramDefs[$p], 'js:') !== false;
if ($isJs) {
switch (get_class($field)) {
case "DataSource":
$returnParams[$p] = @$field->queryParams[$p];
break;
default:
$returnParams[$p] = '';
break;
}
} else {
$postParam = $postedParams[$p];
if (stripos($postParam, 'php:') === 0) {
$postParam = substr($postParam, 4);
}
if (!!$postParam) {
$returnParams[$p] = $field->evaluate($postParam, true, ['model' => $model]);
}
}
} else {
if (is_array($postedParams[$p]) && !empty($postedParams[$p])) {
$returnParams[$p] = $postedParams[$p];
$params[0][$idx] = ['name' => $p, 'length' => count($postedParams[$p])];
}
}
}
}
## find all blocks
preg_match_all("/\\{(.*?)\\}/", $sql, $blocks);
foreach ($blocks[1] as $block) {
if (strtolower($block) == "and" || strtolower($block) == "or") {
continue;
}
$bracket = DataSource::processSQLBracket($block, $postedParams, $field);
$renderBracket = false;
if (isset($bracket['render'])) {
$renderBracket = $bracket['render'];
}
foreach ($bracket['params'] as $bracketParam => $bracketValue) {
if (is_array($bracketValue) && count($bracketValue) > 0) {
$renderBracket = true;
foreach ($bracketValue as $k => $p) {
$returnParams[$k] = $p;
}
}
}
## check if there is another params
preg_match_all("/\\:[\\w\\d_]+/", $bracket['sql'], $attachedParams);
if (count($attachedParams[0]) > 0) {
$inParams = 0;
foreach ($attachedParams[0] as $ap) {
if (isset($returnParams[$ap]) && $returnParams[$ap] != "") {
$inParams++;
## if current params is an ARRAY then convert to multiple params
if (is_array($returnParams[$ap]) && !empty($returnParams[$ap])) {
$newParamString = [];
foreach ($returnParams[$ap] as $rpIdx => $rp) {
$rpKey = $ap . "_" . $rpIdx;
$newParamString[] = $rpKey;
$returnParams[$rpKey] = $rp;
}
unset($returnParams[$ap]);
$bracket['sql'] = Helper::strReplaceFirst($ap, implode(",", $newParamString), $bracket['sql']);
}
}
}
if ($inParams == count($attachedParams)) {
$renderBracket = true;
}
}
if ($renderBracket) {
$sql = str_replace("{{$block}}", $bracket['sql'], $sql);
} else {
$sql = str_replace("{{$block}}", "", $sql);
}
}
## concat 'WHERE' sql with operators
if ($sql != "") {
$sql = DataSource::concatSql($sql, "AND");
$sql = DataSource::concatSql($sql, "OR");
}
## remove uneeded return params
preg_match_all("/\\:[\\w\\d_]+/", $sql, $cp);
foreach ($returnParams as $k => $p) {
if (!in_array($k, $cp[0]) && !in_array(':' . $k, $cp[0])) {
unset($returnParams[$k]);
}
}
return ['sql' => trim($sql), 'params' => $returnParams];
}
