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