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]);
}
public function generateCriteria($search, $params)
{
$condition = $this->generateCondition($search, $params);
$nolimit = false;
$this->relationCriteria['condition'] = $condition["sql"];
$this->params = array_merge(is_null($this->params) ? [] : $this->params, $condition['params']);
$criteria = DataSource::generateCriteria($this->params, $this->relationCriteria, $this);
##clean criteria condition
if (isset($criteria['condition'])) {
if (stripos($criteria['condition'], "and ") === 0) {
$criteria['condition'] = substr($criteria['condition'], 4);
}
if (stripos($criteria['condition'], " and") === strlen($criteria['condition']) - 4) {
$criteria['condition'] = substr($criteria['condition'], 0, strlen($criteria['condition']) - 4);
}
if (stripos($criteria['condition'], "or ") === 0) {
$criteria['condition'] = substr($criteria['condition'], 3);
}
if (stripos($criteria['condition'], " or") === strlen($criteria['condition']) - 3) {
$criteria['condition'] = substr($criteria['condition'], 0, strlen($criteria['condition']) - 3);
}
}
if (array_key_exists('page', $criteria)) {
$start = ($criteria['page'] - 1) * $criteria['pageSize'];
$pageSize = $criteria['pageSize'];
$criteria['limit'] = $pageSize;
if (!isset($criteria['offset'])) {
$criteria['offset'] = $start;
}
unset($criteria['pageSize']);
unset($criteria['page']);
}
if ($nolimit) {
if (isset($criteria['limit'])) {
unset($criteria['limit']);
}
if (isset($criteria['offset'])) {
unset($criteria['offset']);
}
$criteria['nolimit'] = true;
}
## softDelete behavior
$this->applySoftDelete($criteria);
return $criteria;
}
public function getRelated($params = [], $isGenerate = false)
{
$postedParams = array_merge($params, $this->queryParams);
$relChanges = $this->model->getRelChanges($this->relationTo);
$criteria = DataSource::generateCriteria($postedParams, $this->relationCriteria, $this);
if (@$criteria['params']) {
$criteria['params'] = array_filter($criteria['params']);
}
$criteriaCount = $criteria;
if ($this->relationTo == 'currentModel') {
$tableSchema = $this->model->tableSchema;
$builder = $this->model->commandBuilder;
if (array_key_exists('page', $criteriaCount)) {
$start = ($criteriaCount['page'] - 1) * $criteriaCount['pageSize'];
$pageSize = $criteriaCount['pageSize'];
$criteriaCount['limit'] = $pageSize;
$criteriaCount['offset'] = $start;
unset($criteriaCount['pageSize']);
unset($criteriaCount['page']);
}
$countCommand = $builder->createCountCommand($tableSchema, new CDbCriteria($criteriaCount));
$count = $countCommand->queryScalar();
} else {
$criteriaCount = $criteria;
$criteriaCount['select'] = 'count(1) as id';
$rawCount = $this->model->getRelated($this->relationTo, true, $criteriaCount);
if (is_object($rawCount)) {
$count = 1;
} else {
if (!is_array($rawCount) && !is_null($rawCount)) {
throw new Exception('Relation defintion is wrong! check your relations() function in model');
} else {
$count = count($rawCount) > 0 ? $rawCount[0]['id'] : 0;
if (isset($count['id'])) {
$count = $count['id'];
}
}
}
}
if (!empty($this->aggregateGroups) && !$isGenerate) {
$criteria['aggregate'] = ['groups' => $this->aggregateGroups, 'columns' => []];
foreach ($this->aggregateColumns as $k => $c) {
$criteria['aggregate']['columns'][$c['col']] = ['type' => $c['colType'], 'col' => $c['col']];
if ($c['colType'] == 'custom') {
$criteria['aggregate']['columns'][$c['col']]['custom'] = $c['customType'];
}
}
}
$rawData = $this->model->{$this->relationTo}($criteria, false);
## cleanup rawData from relation
// foreach ($rawData as $dataIdx=>$data) {
// foreach ($data as $fieldIdx => $field) {
// if (is_array($field)) {
// unset($rawData[$dataIdx][$fieldIdx]);
// }
// }
// }
if (count($rawData) == 0 && $isGenerate) {
if ($this->relationTo != 'currentModel') {
$rels = $this->model->relations();
$relClass = $rels[$this->relationTo][1];
} else {
$relClass = get_class($this->model);
}
$rawData = [$relClass::model()->getAttributes(true, false)];
}
if ($this->maxAggregateLevel <= count($this->aggregateGroups)) {
$max = $this->maxAggregateLevel - 1;
$rawDataCount = count($rawData) - 1;
for ($k = $rawDataCount; $k >= 0; $k--) {
if (isset($rawData[$k]['$type']) && $rawData[$k]['$type'] == 'a' && $rawData[$k]['$level'] > $max) {
$rawData[$k]['$aggr'] = false;
}
if ($max == -1 && $rawDataCount == $k) {
$rawData[$k]['$aggr'] = true;
}
}
}
$data = ['data' => $rawData, 'debug' => ['count' => $count, 'params' => $postedParams, 'debug' => $criteria], 'rel' => ['insert_data' => $relChanges['insert'], 'update_data' => $relChanges['update'], 'delete_data' => $relChanges['delete']]];
return $data;
}
