public function accounts($increaseon = false, $usedAccounts = [])
{
/*
$rows = (new \yii\db\Query())
->select(["t.id AS id", "case when (t.code < 0) then t.name else concat (t.code,' - ',t.name) end as name,t.increaseon,t.isbalance,t.exchangable"])
->from(AccountCode::tableName()." AS t")
->leftJoin(AccountCode::tableName()." AS a", "t.id = a.parent_id")
->where(($increaseon?"t.increaseon = :increaseon AND ":"")."a.id is null AND not (t.id = ANY(array".json_encode($usedAccounts)."::integer[]))",$increaseon?['increaseon'=>$increaseon]:[])
->orderBy("name")
->all();
*/
$rows = AccountCodeSearch::find()->select(["" . AccountCode::tableName() . ".id AS id", "case when (" . AccountCode::tableName() . ".code < 0) then " . AccountCode::tableName() . ".name else concat (" . AccountCode::tableName() . ".code,' - '," . AccountCode::tableName() . ".name) end as name," . AccountCode::tableName() . ".increaseon," . AccountCode::tableName() . ".isbalance," . AccountCode::tableName() . ".exchangable"])->leftJoin(AccountCode::tableName() . " AS a", AccountCode::tableName() . ".id = a.parent_id")->andWhere(($increaseon ? "" . AccountCode::tableName() . ".increaseon = :increaseon AND " : "") . "a.id is null AND not (" . AccountCode::tableName() . ".id = ANY(array" . json_encode($usedAccounts) . "::integer[]))", $increaseon ? ['increaseon' => $increaseon] : [])->orderBy("name")->asArray()->all();
return $rows;
}
public static function find()
{
return parent::find()->where([AccountCode::tableName() . '.isdel' => 0]);
}
/**
* Finds the AccountCode model based on its primary key value.
* If the model is not found, a 404 HTTP exception will be thrown.
* @param integer $id
* @return AccountCode the loaded model
* @throws NotFoundHttpException if the model cannot be found
*/
protected function findModel($id)
{
if (($model = AccountCode::findOne($id)) !== null) {
return $model;
} else {
throw new NotFoundHttpException('The requested page does not exist.');
}
}
public function afterSave($insert, $changedAttributes)
{
$run = false;
if ($insert || $this->isdel == 1) {
$run = true;
} else {
$isk = false;
foreach ($changedAttributes as $k => $v) {
if ($k == "parent_id") {
$isk = true;
}
}
if ($isk) {
if ($this->parent_id != $changedAttributes["parent_id"]) {
$run = true;
}
}
}
if ($run) {
$jml = $this->db->createCommand("SELECT count(1)\n\t\t\t\t\tFROM " . AccountCode::tableName() . "\n\t\t\t\t\tWHERE isdel = 0\n\t\t\t\t\t")->queryScalar();
$pId = $this->parent_id;
if ($pId == null) {
if ($jml > 1) {
$parent = $this->find()->where("parent_id is null AND id != :id", [":id" => $this->id])->one();
$pId = $parent->id;
$res = $this->db->createCommand("UPDATE \n\t\t\t\t\t\t" . AccountCode::tableName() . " SET\n\t\t\t\t\t\tparent_id = " . $pId . "\n\t\t\t\t\t\tWHERE id = " . $this->id)->execute();
} else {
$parent = $this->find()->where("parent_id is null")->one();
}
} else {
$parent = $this->findOne(["id" => $pId]);
}
$pLeft = $parent->id_left;
$pRight = $parent->id_right;
$pLevel = $parent->id_level;
$left = $this->id_left == null ? -1 : $this->id_left;
$right = $this->id_right == null ? 0 : $this->id_right;
$level = $this->id_level == null ? 0 : $this->id_level;
$opLeft = false;
$opRight = false;
$opLevel = false;
if (isset($changedAttributes["parent_id"])) {
$oldparent = $this->findOne(["id" => $changedAttributes["parent_id"]]);
$opId = $oldparent->id;
$opLeft = $oldparent->id_left;
$opRight = $oldparent->id_right;
$opLevel = $oldparent->id_level;
}
if ($this->isdel == 1 || !$opLeft) {
$op = $this->isdel == 1 ? "-" : "+";
} else {
$op = $pLeft - $left < 0 ? "+" : "-";
}
$lmin = $left > 0 ? min($pLeft, $left) : $pLeft;
$lmax = $opLeft && $left > 0 && $this->isdel == 1 ? max($pLeft, $left) : false;
$rmin = $lmin;
$rmax = $lmax;
$rLeft = 'id_left > ' . $lmin . ($lmax ? ' AND id_left <= ' . $lmax : '');
$rRight = 'id_right >= ' . $rmin . ($rmax ? ' AND id_right < ' . $rmax : '');
$data = 'id_left >= ' . $left . ' AND id_left < ' . $right;
if ($this->isdel == 1) {
$vRest = "2";
$vChild = "1";
$vLevel = "1";
} else {
$vRest = "(" . ($right - $left + 1) . ")";
$vChild = "(" . $left . ")+(" . $pLeft . ($pLeft - $left > 0 && $left > 0 ? $op . $vRest : "") . "+1)";
$vLevel = "(" . $level . ")+" . ($pLevel + 1);
}
die("UPDATE \n\t\t\t\t\t" . AccountCode::tableName() . " SET\n\t\t\t\t\t(id_left,id_right,id_level)\n\t\t\t\t\t= (\n\t\t\t\t\t\tcase when " . $rLeft . " and (" . $data . ") is not true then id_left" . $op . $vRest . " else\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\tcase when " . $data . " then id_left-" . $vChild . " else\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\tid_left\t\t\t\t\t\t\n\t\t\t\t\t\t\tend\t\n\t\t\t\t\t\tend,\t\t\n\t\t\t\t\t\tcase when " . $rRight . " and (" . $data . ") is not true then id_right" . $op . $vRest . " else\t\t\t\t\t\n\t\t\t\t\t\t\tcase when " . $data . " then id_right-" . $vChild . " else\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\tid_right\t\t\t\t\t\t\n\t\t\t\t\t\t\tend\t\n\t\t\t\t\t\tend,\t\t\t\t\t\n\t\t\t\t\t\tcase when " . $data . " then id_level-" . $vLevel . " else\n\t\t\t\t\t\t\tid_level\t\t\t\t\t\t\t\n\t\t\t\t\t\tend\n\t\t\t\t\t)\t\t\n\t\t\t\t\tWHERE isdel = 0");
$res = $this->db->createCommand("UPDATE \n\t\t\t\t\t" . AccountCode::tableName() . " SET\n\t\t\t\t\t(id_left,id_right,id_level)\n\t\t\t\t\t= (\n\t\t\t\t\t\tcase when " . $rLeft . " and (" . $data . ") is not true then id_left" . $op . $vRest . " else\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\tcase when " . $data . " then id_left-" . $vChild . " else\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\tid_left\t\t\t\t\t\t\n\t\t\t\t\t\t\tend\t\n\t\t\t\t\t\tend,\t\t\n\t\t\t\t\t\tcase when " . $rRight . " and (" . $data . ") is not true then id_right" . $op . $vRest . " else\t\t\t\t\t\n\t\t\t\t\t\t\tcase when " . $data . " then id_right-" . $vChild . " else\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\tid_right\t\t\t\t\t\t\n\t\t\t\t\t\t\tend\t\n\t\t\t\t\t\tend,\t\t\t\t\t\n\t\t\t\t\t\tcase when " . $data . " then id_level-" . $vLevel . " else\n\t\t\t\t\t\t\tid_level\t\t\t\t\t\t\t\n\t\t\t\t\t\tend\n\t\t\t\t\t)\t\t\n\t\t\t\t\tWHERE isdel = 0")->execute();
if ($this->isdel == 1) {
$res = $this->db->createCommand("UPDATE \n\t\t\t\t\t" . AccountCode::tableName() . " SET\n\t\t\t\t\t(id_left,id_right,id_level)\n\t\t\t\t\t= ((-1),0,0)\t\t\n\t\t\t\t\tWHERE id = " . $this->id)->execute();
}
}
parent::afterSave($insert, $changedAttributes);
}
/**
* Creates data provider instance with search query applied
*
* @param array $params
*
* @return ActiveDataProvider
*/
public function search($params)
{
$query = $this->find();
$query->joinWith(['transaction', 'account']);
$dataProvider = new ActiveDataProvider(['query' => $query]);
$dataProvider->sort->attributes['time'] = ['asc' => [Transaction::tableName() . '.time' => SORT_ASC], 'desc' => [Transaction::tableName() . '.time' => SORT_DESC]];
$dataProvider->sort->attributes['title'] = ['asc' => [Transaction::tableName() . '.title' => SORT_ASC], 'desc' => [Transaction::tableName() . '.title' => SORT_DESC]];
$dataProvider->sort->attributes['subject'] = ['asc' => [Transaction::tableName() . '.subject' => SORT_ASC], 'desc' => [Transaction::tableName() . '.subject' => SORT_DESC]];
$dataProvider->sort->attributes['tags'] = ['asc' => [Transaction::tableName() . '.tags' => SORT_ASC], 'desc' => [Transaction::tableName() . '.tags' => SORT_DESC]];
$dataProvider->sort->attributes['transactionRemarks'] = ['asc' => [Transaction::tableName() . '.remarks' => SORT_ASC], 'desc' => [Transaction::tableName() . '.remarks' => SORT_DESC]];
$dataProvider->sort->attributes['accountName'] = ['asc' => [AccountCode::tableName() . '.code' => SORT_ASC], 'desc' => [AccountCode::tableName() . '.code' => SORT_DESC]];
if (!($this->load($params) && $this->validate())) {
return $dataProvider;
}
$query->andFilterWhere(['id' => $this->id, 'account_id' => $this->account_id, 'transaction_id' => $this->transaction_id, 'type' => $this->type, 'isdel' => $this->isdel]);
$params = self::queryNumber([['quantity'], ['amount']]);
foreach ($params as $p) {
$query->andFilterWhere($p);
}
$params = self::queryTime([['time', Transaction::tableName()]]);
foreach ($params as $p) {
$query->andFilterWhere($p);
}
$params = self::queryString([['remarks', Journal::tableName()], ['title', Transaction::tableName()], ['subject', Transaction::tableName()], ['tags', Transaction::tableName()]]);
foreach ($params as $p) {
$query->andFilterWhere($p);
}
$query->andFilterWhere(['like', 'lower(' . Transaction::tableName() . '.remarks)', strtolower($this->transactionRemarks)])->andFilterWhere(['like', "lower(concat(" . AccountCode::tableName() . ".code,' - '," . AccountCode::tableName() . ".name))", strtolower($this->accountName)]);
return $dataProvider;
}
/**
* @return \yii\db\ActiveQuery
*/
public function getAccountCodes()
{
return $this->hasMany(AccountCode::className(), ['parent_id' => 'id']);
}
/**
* @return \yii\db\ActiveQuery
*/
public function getAccount()
{
return $this->hasOne(AccountCode::className(), ['id' => 'account_id']);
}
