/**
* Returns protected var $oberserver.
* @param string observer key
* @return array
*/
protected function parseCriteria($SqlParser, $searchcriteria)
{
if (!$searchcriteria || !is_array($searchcriteria)) {
return;
}
foreach ($searchcriteria as $key => $value) {
switch ($key) {
case 'no_id':
$SqlParser->addCriteria(new SqlCriteria('a.att_id', $value, '<>'));
break;
case 'archiveonline':
$SqlParser->addCriteria(new SqlCriteria('unix_timestamp(a.att_online)', $value, '>'));
break;
case 'archiveoffline':
$SqlParser->addCriteria(new SqlCriteria('unix_timestamp(a.att_offline)', $value, '<='));
$SqlParser->addCriteria(new SqlCriteria('unix_timestamp(a.att_offline)', 0, '>'));
break;
case 'activated':
// only active pages
$SqlParser->addCriteria(new SqlCriteria('a.att_active', 1));
// only pages that are online
$SqlParser->addCriteria(new SqlCriteria('a.att_online', 'now()', '<='));
$offline = new SqlCriteria('a.att_offline', 'now()', '>');
$offline->addCriteria(new SqlCriteria('unix_timestamp(a.att_offline)', 0, '='), SqlCriteria::REL_OR);
$SqlParser->addCriteria($offline);
break;
}
}
}
/**
* Returns protected var $oberserver.
* @param string observer key
* @return array
*/
protected function parseCriteria($SqlParser, $searchcriteria)
{
if (!$searchcriteria || !is_array($searchcriteria)) {
return;
}
foreach ($searchcriteria as $key => $value) {
switch ($key) {
case 'no_id':
$SqlParser->addCriteria(new SqlCriteria('a.com_id', $value, '<>'));
break;
case 'cal_active':
$SqlParser->addCriteria(new SqlCriteria('b.cal_active', $value));
break;
case 'activated':
// only active pages
$SqlParser->addCriteria(new SqlCriteria('a.com_active', 1));
$SqlParser->addCriteria(new SqlCriteria('b.cal_active', 1));
break;
case 'search':
$search = new SqlCriteria('a.com_name', "%{$value}%", 'like');
$search->addCriteria(new SqlCriteria('a.com_text', "%{$value}%", 'like'), SqlCriteria::REL_OR);
$SqlParser->addCriteria($search);
break;
}
}
}
public function actionPreviewSQL()
{
$postdata = file_get_contents("php://input");
$post = json_decode($postdata, true);
$criteria = @$post['criteria'] ? $post['criteria'] : [];
$params = @$post['params'] ? $post['params'] : [];
$baseClass = $post['baseclass'];
switch ($baseClass) {
case "DataGrid":
case "DataFilter":
case "RelationField":
case "TextField":
$rel = 'currentModel';
$name = $post['rfname'];
$classPath = $post['rfclass'];
$modelClassPath = $post['rfmodel'];
$modelClass = Helper::explodeLast(".", $modelClassPath);
Yii::import($modelClassPath);
$class = Helper::explodeLast(".", $classPath);
Yii::import($classPath);
$model = new $modelClass();
$builder = $model->commandBuilder;
$fb = FormBuilder::load($classPath);
$field = $fb->findField(['name' => $name]);
$rf = new RelationField();
$rf->builder = $fb;
$rf->attributes = $field;
$rf->relationCriteria = $criteria;
$rf->params = $post['params'];
$criteria = $rf->generateCriteria('', []);
$criteria = new CDbCriteria($criteria);
break;
case "DataSource":
$rel = $post['rel'];
$name = $post['dsname'];
$classPath = $post['dsclass'];
$class = Helper::explodeLast(".", $classPath);
Yii::import($classPath);
$model = new $class();
$builder = $model->commandBuilder;
$fb = FormBuilder::load($classPath);
$fb->model = new $model();
$field = $fb->findField(['name' => $name]);
$ds = new DataSource();
$ds->attributes = $field;
$criteria = DataSource::generateCriteria($params, $criteria, $ds);
$criteria = SqlCriteria::convertPagingCriteria($criteria);
$criteria = new CDbCriteria($criteria);
break;
}
if (!isset($rel)) {
echo json_encode(["sql" => '', "error" => '']);
return false;
}
$isRelated = false;
if ($rel == 'currentModel') {
$tableSchema = $model->tableSchema;
} else {
$parent = $model::model()->find();
$relMeta = $model->getMetadata()->relations[$rel];
$relClass = $relMeta->className;
if (!is_subclass_of($relClass, 'ActiveRecord')) {
throw new CException("Class {$relClass} harus merupakan subclass dari ActiveRecord");
}
$tableSchema = $relClass::model()->tableSchema;
if (!is_null($parent)) {
$parentPrimaryKey = $parent->metadata->tableSchema->primaryKey;
switch (get_class($relMeta)) {
case 'CHasOneRelation':
case 'CBelongsToRelation':
if (is_string($relMeta->foreignKey)) {
$criteria->addColumnCondition([$relMeta->foreignKey => $parent->{$parentPrimaryKey}]);
$isRelated = true;
}
break;
case 'CManyManyRelation':
$parser = new PhpParser\Parser(new PhpParser\Lexer\Emulative());
$stmts = $parser->parse('<?php ' . $relMeta->foreignKey . ';');
$bridgeTable = $stmts[0]->name->parts[0];
$arg0 = $stmts[0]->args[0]->value->name->parts[0];
$arg1 = $stmts[0]->args[1]->value->name->parts[0];
$criteria->join .= " " . $relMeta->joinType . " {$bridgeTable} ON t.{$tableSchema->primaryKey} = {$bridgeTable}.{$arg1} ";
break;
case 'CHasManyRelation':
//without through
if (is_string($relMeta->foreignKey)) {
$criteria->addColumnCondition([$relMeta->foreignKey => $parent->{$parentPrimaryKey}]);
$isRelated = true;
}
//with through
//todo..
break;
}
}
}
$command = $builder->createFindCommand($tableSchema, $criteria);
$commandText = $command->text;
if ($isRelated) {
$commandText = str_replace(":ycp0", "\n" . '"{$model->' . $relMeta->foreignKey . '}"', $commandText);
}
$commandText = SqlFormatter::highlight($commandText);
$errMsg = '';
try {
$command->queryScalar();
} catch (Exception $e) {
$errMsg = $e->getMessage();
$errMsg = str_replace("CDbCommand gagal menjalankan statement", "", $errMsg);
}
echo json_encode(["sql" => $commandText, "error" => $errMsg]);
}
/**
* Returns protected var $oberserver.
* @param string observer key
* @return array
*/
protected function parseCriteria($SqlParser, $searchcriteria)
{
if (!$searchcriteria || !is_array($searchcriteria)) {
return;
}
foreach ($searchcriteria as $key => $value) {
switch ($key) {
case 'no_id':
$SqlParser->addCriteria(new SqlCriteria('a.cal_id', $value, '<>'));
break;
case 'archive':
$SqlParser->addCriteria(new SqlCriteria('unix_timestamp(a.cal_stop)', $value, '<='));
break;
case 'finished':
$today = mktime(0, 0, 0);
$SqlParser->addCriteria(new SqlCriteria('unix_timestamp(a.cal_stop)', $today, '<'));
break;
case 'archive_start':
// cal_start must by less or equal to end of period
$SqlParser->addCriteria(new SqlCriteria('unix_timestamp(a.cal_stop)', $value, '>='));
break;
case 'archive_stop':
// cal_stop must by greater or equeal to start of period
$SqlParser->addCriteria(new SqlCriteria('unix_timestamp(a.cal_start)', $value, '<='));
break;
case 'activated':
// only active pages
$SqlParser->addCriteria(new SqlCriteria('a.cal_active', 1));
// only pages that are not history
$SqlParser->addCriteria(new SqlCriteria('a.cal_stop', is_string($value) ? $value : 'now()', '>='));
break;
case 'search':
$search = new SqlCriteria('a.cal_name', "%{$value}%", 'like');
$search->addCriteria(new SqlCriteria('a.cal_intro', "%{$value}%", 'like'), SqlCriteria::REL_OR);
$search->addCriteria(new SqlCriteria('a.cal_text', "%{$value}%", 'like'), SqlCriteria::REL_OR);
$SqlParser->addCriteria($search);
break;
}
}
}
/**
* Returns protected var $oberserver.
* @param string observer key
* @return array
*/
protected function parseCriteria($SqlParser, $searchcriteria)
{
if (!$searchcriteria || !is_array($searchcriteria)) {
return;
}
foreach ($searchcriteria as $key => $value) {
switch ($key) {
case 'no_id':
$SqlParser->addCriteria(new SqlCriteria('a.lnk_id', $value, '<>'));
break;
case 'previous_id':
$SqlParser->addFrom("left join links as b on b.lnk_tree_id = a.lnk_tree_id and b.lnk_tag = a.lnk_tag");
$SqlParser->addCriteria(new SqlCriteria('b.lnk_id', $value, '='));
$SqlParser->addCriteria(new SqlCriteria('a.lnk_weight', 'b.lnk_weight', '>', true));
break;
case 'next_id':
$SqlParser->addFrom("left join links as b on b.lnk_tree_id = a.lnk_tree_id and b.lnk_tag = a.lnk_tag");
$SqlParser->addCriteria(new SqlCriteria('b.lnk_id', $value, '='));
$SqlParser->addCriteria(new SqlCriteria('a.lnk_weight', 'b.lnk_weight', '<', true));
break;
case 'search':
$search = new SqlCriteria('a.lnk_name', "%{$value}%", 'like');
$search->addCriteria(new SqlCriteria('a.lnk_intro', "%{$value}%", 'like'), SqlCriteria::REL_OR);
$search->addCriteria(new SqlCriteria('a.lnk_text', "%{$value}%", 'like'), SqlCriteria::REL_OR);
$SqlParser->addCriteria($search);
break;
}
}
}
/**
* Returns protected var $oberserver.
* @param string observer key
* @return array
*/
protected function parseCriteria($SqlParser, $searchcriteria)
{
if (!$searchcriteria || !is_array($searchcriteria)) {
return;
}
foreach ($searchcriteria as $key => $value) {
switch ($key) {
case 'no_id':
$SqlParser->addCriteria(new SqlCriteria('a.rcd_id', $value, '<>'));
break;
case 'search':
$SqlParser->addFrom('inner join form_record_item as b on a.rcd_id = b.item_rcd_id');
$search = new SqlCriteria('a.rcd_ip', "%{$value}%", 'like');
$search->addCriteria(new SqlCriteria('b.item_value', "%{$value}%", 'like'), SqlCriteria::REL_OR);
$SqlParser->addCriteria($search);
break;
}
}
}
/**
* @see DbConnector::getDefaultValue
*/
protected function parseCriteria($sqlParser, $searchcriteria)
{
if (!$searchcriteria || !is_array($searchcriteria)) {
return;
}
foreach ($searchcriteria as $key => $value) {
switch ($key) {
case 'usr_id':
$sqlParser->addCriteria(new SqlCriteria('a.usr_id', $value));
break;
case 'no_id':
$sqlParser->addCriteria(new SqlCriteria('a.usr_id', $value, '<>'));
break;
case 'grp_id':
$sqlParser->addFrom('left join usergroup as b on b.usr_id = a.usr_id');
$sqlParser->addCriteria(new SqlCriteria('b.grp_id', $value, '='));
break;
case 'no_grp_id':
//$sqlParser->addFrom('left join usergroup as b on b.usr_id = a.usr_id');
//$sqlParser->addCriteria(new SqlCriteria('b.grp_id', $value, '<>'));
$parser = new SqlParser();
$parser->setSelect('select');
$parser->setTable('usergroup', 'b');
$parser->addField(new SqlField('b', 'grp_id', 'grp_id', 'grp_id', SqlParser::SEL_LIST, SqlField::TYPE_INTEGER));
$parser->parseCriteria(array('grp_id' => $value));
$parser->addCriteria(new SqlCriteria('b.usr_id', 'a.usr_id', '=', true));
$subquery = $parser->getSql(SqlParser::SEL_LIST);
$sqlParser->addCriteria(new SqlCriteria(NULL, "({$subquery})", 'not exists', true));
break;
case 'tree_access':
$sqlParser->addFrom('left join usergroup as b on b.usr_id = a.usr_id');
$search = new SqlCriteria('a.usr_role', self::ROLE_ADMIN, '=');
$searchGroup = new SqlCriteria('a.usr_role', self::ROLE_FRONTEND, '<>');
$searchGroup->addCriteria(new SqlCriteria('b.grp_id', $group, '='), SqlCriteria::REL_AND);
$search->addCriteria($searchGroup, SqlCriteria::REL_OR);
$sqlParser->addCriteria($search);
break;
case 'search':
//$search = new SqlCriteria('a.usr_name', $value, 'regexp');
$value = "%{$value}%";
$search = new SqlCriteria('a.usr_name', $value, 'like');
$search->addCriteria(new SqlCriteria('a.usr_firstname', $value, 'like'), SqlCriteria::REL_OR);
$search->addCriteria(new SqlCriteria('a.usr_username', $value, 'like'), SqlCriteria::REL_OR);
$search->addCriteria(new SqlCriteria('a.usr_email', $value, 'like'), SqlCriteria::REL_OR);
$search->addCriteria(new SqlCriteria('a.usr_city', $value, 'like'), SqlCriteria::REL_OR);
$search->addCriteria(new SqlCriteria('a.usr_country', $value, 'like'), SqlCriteria::REL_OR);
//$search->addCriteria(new SqlCriteria('c.grp_name', $value, 'like'), SqlCriteria::REL_OR);
$sqlParser->addCriteria($search);
/*
$key = "concat(a.usr_name, a.usr_firstname, a.usr_username, a.usr_email, a.usr_city, a.usr_country)";
$sqlParser->addCriteria(new SqlCriteria($key, $value, 'regexp'));
*/
break;
}
}
}
/**
* @see DbConnector::getDefaultValue
*/
protected function parseCriteria($sqlParser, $searchcriteria)
{
if (!$searchcriteria || !is_array($searchcriteria)) {
return;
}
foreach ($searchcriteria as $key => $value) {
switch ($key) {
case 'no_tag':
$sqlParser->addCriteria(new SqlCriteria('a.tag', $value, '<>'));
break;
case 'no_plugin_view':
$sqlParser->addCriteria(new SqlCriteria('a.plug_view', $value, '<>'));
break;
case 'recursive_tree_id':
$self = $value['self'];
$recursive = $value['recursive'];
$search = new SqlCriteria('a.tree_id', $self);
if ($recursive) {
$recsearch = new SqlCriteria('a.tree_id', $recursive);
$recsearch->addCriteria(new SqlCriteria('a.plug_recursive', 1), SqlCriteria::REL_AND);
$search->addCriteria($recsearch, SqlCriteria::REL_OR);
}
$sqlParser->addCriteria($search);
break;
}
}
}
/**
* Returns protected var $oberserver.
* @param string observer key
* @return array
*/
protected function parseCriteria($sqlParser, $searchcriteria, $prefix = true)
{
if (!$searchcriteria || !is_array($searchcriteria)) {
return;
}
foreach ($searchcriteria as $key => $value) {
switch ($key) {
case 'no_id':
$sqlParser->addCriteria(new SqlCriteria('a.res_id', $value, '<>'));
break;
case 'start':
$sqlParser->addCriteria(new SqlCriteria('unix_timestamp(a.res_date)', $value, '>'));
break;
case 'stop':
$sqlParser->addCriteria(new SqlCriteria('unix_timestamp(a.res_date)', $value, '<='));
break;
case 'max_count':
//$sqlParser->addField(new SqlField('', 'count(a.res_id)', 'res_count', 'Reservation Count', SqlParser::getTypeSelect(), SqlField::TYPE_INTEGER));
//$sqlParser->addCriteria(new SqlCriteria('a.res_count', $value, '>=')); break;
$sqlParser->setGroupby("group by a.res_usr_id having count(a.res_id) >= {$value}");
//$sqlParser->setGroupby("having count(a.res_id) >= $value");
//$sqlParser->setGroupby("having count(a.res_id) >= $value");
case 'activated':
// only active pages
$today = date('Y-m-d');
//, mktime(0,0,0));
$time = date('H');
$search = new SqlCriteria($sqlParser->getFieldByName('reservation_date')->getField($prefix), $today, '>');
$addsearch = new SqlCriteria($sqlParser->getFieldByName('reservation_date')->getField($prefix), $today, '=');
$addsearch->addCriteria(new SqlCriteria($sqlParser->getFieldByName('reservation_time')->getField($prefix), $time, '>'), SqlCriteria::REL_AND);
$search->addCriteria($addsearch, SqlCriteria::REL_OR);
$sqlParser->addCriteria(new SqlCriteria($sqlParser->getFieldByName('active')->getField($prefix), 1));
//$sqlParser->addCriteria(new SqlCriteria($sqlParser->getFieldByName('reservation_date')->getField($prefix), 'now()', '>'));
$sqlParser->addCriteria($search);
break;
case 'search':
$search = new SqlCriteria('b.usr_name', "%{$value}%", 'like');
$search->addCriteria(new SqlCriteria('unix_timestamp(a.res_date)', strtotime($value), '='), SqlCriteria::REL_OR);
$search->addCriteria(new SqlCriteria('b.usr_firstname', "%{$value}%", 'like'), SqlCriteria::REL_OR);
$search->addCriteria(new SqlCriteria('b.usr_email', "%{$value}%", 'like'), SqlCriteria::REL_OR);
$sqlParser->addCriteria($search);
break;
}
}
}
/**
* Returns protected var $oberserver.
* @param string observer key
* @return array
*/
protected function parseCriteria($sqlParser, $searchcriteria, $prefix = true)
{
if (!$searchcriteria || !is_array($searchcriteria)) {
return;
}
foreach ($searchcriteria as $key => $value) {
switch ($key) {
case 'no_id':
$sqlParser->addCriteria(new SqlCriteria('a.usr_id', $value, '<>'));
break;
case 'grp_id':
$sqlParser->addFrom('left join newsletter_usergroup as b on b.usr_id = a.usr_id');
$sqlParser->addCriteria(new SqlCriteria('b.grp_id', $value, '='));
break;
case 'no_grp_id':
throw new Exception("NewsletterUser search for no_grp_id not implemented! add logic for tree_id and tag?");
$sqlParser->addFrom('left join usergroup as b on b.usr_id = a.usr_id');
$sqlParser->addCriteria(new SqlCriteria('b.grp_id', $value, '<>'));
break;
case 'activated':
$sqlParser->addCriteria(new SqlCriteria($sqlParser->getFieldByName('active')->getField($prefix), 1));
$sqlParser->addCriteria(new SqlCriteria($sqlParser->getFieldByName('optin')->getField($prefix), ''));
$objUnsubscribe = $sqlParser->getFieldByName('unsubscribe_date');
$subscribe = new SqlCriteria($objUnsubscribe->getField($prefix), 'null', '=');
$subscribe->addCriteria(new SqlCriteria($objUnsubscribe->getField($prefix), 0, '='), SqlCriteria::REL_OR);
$sqlParser->addCriteria($subscribe);
break;
case 'search':
$sqlParser->addCriteria(new SqlCriteria('a.usr_name', "%{$value}%", 'like'));
break;
}
}
}
/**
* Returns protected var $oberserver.
* @param string observer key
* @return array
*/
protected function parseCriteria($sqlParser, $searchcriteria, $prefix = true)
{
if (!$searchcriteria || !is_array($searchcriteria)) {
return;
}
foreach ($searchcriteria as $key => $value) {
switch ($key) {
case 'no_id':
$sqlParser->addCriteria(new SqlCriteria($sqlParser->getFieldByName('id')->getField($prefix), $value, '<>'));
break;
case 'archiveonline':
$sqlParser->addCriteria(new SqlCriteria('unix_timestamp(a.nl_online)', $value, '>'));
break;
case 'archiveoffline':
$sqlParser->addCriteria(new SqlCriteria('unix_timestamp(a.nl_offline)', $value, '<='));
$sqlParser->addCriteria(new SqlCriteria('unix_timestamp(a.nl_offline)', 0, '>'));
break;
case 'activated':
// only active pages
$sqlParser->addCriteria(new SqlCriteria($sqlParser->getFieldByName('active')->getField($prefix), 1));
// only pages that are online
$sqlParser->addCriteria(new SqlCriteria($sqlParser->getFieldByName('online')->getField($prefix), 'now()', '<='));
$offlineField = $sqlParser->getFieldByName('offline')->getField($prefix);
$offline = new SqlCriteria($offlineField, 'now()', '>');
$offline->addCriteria(new SqlCriteria($offlineField, 'null', '='), SqlCriteria::REL_OR);
$offline->addCriteria(new SqlCriteria($offlineField, 0, '='), SqlCriteria::REL_OR);
$sqlParser->addCriteria($offline);
break;
case 'search':
$search = new SqlCriteria('a.nl_name', "%{$value}%", 'like');
$search->addCriteria(new SqlCriteria('a.nl_intro', "%{$value}%", 'like'), SqlCriteria::REL_OR);
$sqlParser->addCriteria($search);
break;
}
}
}
