<?php

/**
 * Class that operate on table 'accountstree'. Database Mysql.
 *
 * @author: http://phpdao.com
 * @date: 2022-05-08 01:36
 */
class AccountstreeMySqlDAO implements AccountstreeDAO {

    /**
     * Get Domain object by primry key
     *
     * @param String $id primary key
     * @return AccountstreeMySql
     */
    public function load($id) {
        $sql = 'SELECT * FROM accountstree WHERE id = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($id);
        return $this->getRow($sqlQuery);
    }

    /**
     * Get all records from table
     */
    public function queryAll() {
        $sql = 'SELECT * FROM accountstree';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    /**
     * Get all records from table ordered by field
     *
     * @param $orderColumn column name
     */
    public function queryAllOrderBy($orderColumn) {
        $sql = 'SELECT * FROM accountstree ORDER BY ' . $orderColumn;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    /**
     * Delete record from table
     * @param accountstree primary key
     */
    public function delete($id) {
        $sql = 'DELETE FROM accountstree WHERE id = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($id);
        return $this->executeUpdate($sqlQuery);
    }

    /**
     * Insert record to table
     *
     * @param AccountstreeMySql accountstree
     */
    public function insert($accountstree) {

        /*        //get not used id from 1 to 500 to use old value
          $id = R::getCell('select num from(
          SELECT @i := @i+1 AS num
          FROM (
          SELECT 1 AS x UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
          SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 AS x UNION ALL
          SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
          ) AS t
          CROSS JOIN (
          SELECT 1 AS x UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
          SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 AS x UNION ALL
          SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
          ) AS s
          CROSS JOIN (
          SELECT 1 AS x UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
          SELECT 1 UNION ALL SELECT 1
          ) AS u
          CROSS JOIN (SELECT @i := 0) AS var
          ) as t

          where num NOT IN (
          SELECT id FROM accountstree
          )
          '); */


        $sql = 'INSERT INTO accountstree (name, customName, customNameEn, nameEn, parent, accountNature, theValue, theOrder, layingOrder, reportid, listId, notes, itemfrom, itemtype, itemtype2, itemtype3, userid, del, sysdate) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
        $sqlQuery = new SqlQuery($sql);

//        $sqlQuery->set($id);
        $sqlQuery->set($accountstree->name);
        $sqlQuery->set($accountstree->customName);
        $sqlQuery->set($accountstree->customNameEn);
        $sqlQuery->set($accountstree->nameEn);
        $sqlQuery->setNumber($accountstree->parent);
        $sqlQuery->setNumber((int) $accountstree->accountNature);
        $sqlQuery->set($accountstree->theValue);
        $sqlQuery->setNumber($accountstree->theOrder);
        $sqlQuery->set($accountstree->layingOrder);
        $sqlQuery->setNumber((int) $accountstree->reportid);
        $sqlQuery->setNumber((int) $accountstree->listId);
        $sqlQuery->setString2($accountstree->notes);
        $sqlQuery->setNumber($accountstree->itemfrom);
        $sqlQuery->setNumber($accountstree->itemtype);
        $sqlQuery->setNumber($accountstree->itemtype2);
        $sqlQuery->set($accountstree->itemtype3);
        $sqlQuery->setNumber($accountstree->userid);
        $sqlQuery->setNumber($accountstree->del);
        $sqlQuery->set($accountstree->sysdate);

        $id = $this->executeInsert($sqlQuery);
        $accountstree->id = $id;
        return $id;
    }

    /**
     * Update record in table
     *
     * @param AccountstreeMySql accountstree
     */
    public function update($accountstree) {
        $sql = 'UPDATE accountstree SET name = ?, customName = ?, customNameEn = ?, nameEn = ?, parent = ?, accountNature = ?, theValue = ?, theOrder = ?, layingOrder = ?, reportid = ?, listId = ?, notes = ?, itemfrom = ?, itemtype = ?, itemtype2 = ?, itemtype3 = ?, userid = ?, del = ?, sysdate = ? WHERE id = ?';
        $sqlQuery = new SqlQuery($sql);

        $sqlQuery->set($accountstree->name);
        $sqlQuery->set($accountstree->customName);
        $sqlQuery->set($accountstree->customNameEn);
        $sqlQuery->set($accountstree->nameEn);
        $sqlQuery->setNumber($accountstree->parent);
        $sqlQuery->setNumber((int) $accountstree->accountNature);
        $sqlQuery->set($accountstree->theValue);
        $sqlQuery->setNumber($accountstree->theOrder);
        $sqlQuery->set($accountstree->layingOrder);
        $sqlQuery->setNumber((int) $accountstree->reportid);
        $sqlQuery->setNumber((int) $accountstree->listId);
        $sqlQuery->setString2($accountstree->notes);
        $sqlQuery->setNumber($accountstree->itemfrom);
        $sqlQuery->setNumber($accountstree->itemtype);
        $sqlQuery->setNumber($accountstree->itemtype2);
        $sqlQuery->set($accountstree->itemtype3);
        $sqlQuery->setNumber($accountstree->userid);
        $sqlQuery->setNumber($accountstree->del);
        $sqlQuery->set($accountstree->sysdate);

        $sqlQuery->setNumber($accountstree->id);
        return $this->executeUpdate($sqlQuery);
    }

    /**
     * Delete all rows
     */
    public function clean() {
        $sql = 'DELETE FROM accountstree';
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function queryByName($value) {
        $sql = 'SELECT * FROM accountstree WHERE name = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByCustomName($value) {
        $sql = 'SELECT * FROM accountstree WHERE customName = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByCustomNameEn($value) {
        $sql = 'SELECT * FROM accountstree WHERE customNameEn = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByNameEn($value) {
        $sql = 'SELECT * FROM accountstree WHERE nameEn = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByParent($value) {
        $sql = 'SELECT * FROM accountstree WHERE parent = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->getList($sqlQuery);
    }

    public function queryByAccountNature($value) {
        $sql = 'SELECT * FROM accountstree WHERE accountNature = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->getList($sqlQuery);
    }

    public function queryByTheValue($value) {
        $sql = 'SELECT * FROM accountstree WHERE theValue = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByTheOrder($value) {
        $sql = 'SELECT * FROM accountstree WHERE theOrder = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->getList($sqlQuery);
    }

    public function queryByLayingOrder($value) {
        $sql = 'SELECT * FROM accountstree WHERE layingOrder = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByReportid($value) {
        $sql = 'SELECT * FROM accountstree WHERE reportid = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->getList($sqlQuery);
    }

    public function queryByListId($value) {
        $sql = 'SELECT * FROM accountstree WHERE listId = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->getList($sqlQuery);
    }

    public function queryByNotes($value) {
        $sql = 'SELECT * FROM accountstree WHERE notes = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByItemfrom($value) {
        $sql = 'SELECT * FROM accountstree WHERE itemfrom = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->getList($sqlQuery);
    }

    public function queryByItemtype($value) {
        $sql = 'SELECT * FROM accountstree WHERE itemtype = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->getList($sqlQuery);
    }

    public function queryByItemtype2($value) {
        $sql = 'SELECT * FROM accountstree WHERE itemtype2 = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->getList($sqlQuery);
    }

    public function queryByItemtype3($value) {
        $sql = 'SELECT * FROM accountstree WHERE itemtype3 = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByUserid($value) {
        $sql = 'SELECT * FROM accountstree WHERE userid = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->getList($sqlQuery);
    }

    public function queryByDel($value) {
        $sql = 'SELECT * FROM accountstree WHERE del = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->getList($sqlQuery);
    }

    public function queryBySysdate($value) {
        $sql = 'SELECT * FROM accountstree WHERE sysdate = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function deleteByName($value) {
        $sql = 'DELETE FROM accountstree WHERE name = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByCustomName($value) {
        $sql = 'DELETE FROM accountstree WHERE customName = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByCustomNameEn($value) {
        $sql = 'DELETE FROM accountstree WHERE customNameEn = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByNameEn($value) {
        $sql = 'DELETE FROM accountstree WHERE nameEn = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByParent($value) {
        $sql = 'DELETE FROM accountstree WHERE parent = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByAccountNature($value) {
        $sql = 'DELETE FROM accountstree WHERE accountNature = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByTheValue($value) {
        $sql = 'DELETE FROM accountstree WHERE theValue = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByTheOrder($value) {
        $sql = 'DELETE FROM accountstree WHERE theOrder = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByLayingOrder($value) {
        $sql = 'DELETE FROM accountstree WHERE layingOrder = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByReportid($value) {
        $sql = 'DELETE FROM accountstree WHERE reportid = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByListId($value) {
        $sql = 'DELETE FROM accountstree WHERE listId = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByNotes($value) {
        $sql = 'DELETE FROM accountstree WHERE notes = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByItemfrom($value) {
        $sql = 'DELETE FROM accountstree WHERE itemfrom = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByItemtype($value) {
        $sql = 'DELETE FROM accountstree WHERE itemtype = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByItemtype2($value) {
        $sql = 'DELETE FROM accountstree WHERE itemtype2 = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByItemtype3($value) {
        $sql = 'DELETE FROM accountstree WHERE itemtype3 = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByUserid($value) {
        $sql = 'DELETE FROM accountstree WHERE userid = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteByDel($value) {
        $sql = 'DELETE FROM accountstree WHERE del = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->setNumber($value);
        return $this->executeUpdate($sqlQuery);
    }

    public function deleteBySysdate($value) {
        $sql = 'DELETE FROM accountstree WHERE sysdate = ?';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->executeUpdate($sqlQuery);
    }

    /**
     * Read row
     *
     * @return AccountstreeMySql
     */
    protected function readRow($row) {
        $accountstree = new Accountstree();

        $accountstree->id = $row['id'];
        $accountstree->name = $row['name'];
        $accountstree->customName = $row['customName'];
        $accountstree->customNameEn = $row['customNameEn'];
        $accountstree->nameEn = $row['nameEn'];
        $accountstree->parent = $row['parent'];
        $accountstree->accountNature = $row['accountNature'];
        $accountstree->theValue = $row['theValue'];
        $accountstree->theOrder = $row['theOrder'];
        $accountstree->layingOrder = $row['layingOrder'];
        $accountstree->reportid = $row['reportid'];
        $accountstree->listId = $row['listId'];
        $accountstree->notes = $row['notes'];
        $accountstree->itemfrom = $row['itemfrom'];
        $accountstree->itemtype = $row['itemtype'];
        $accountstree->itemtype2 = $row['itemtype2'];
        $accountstree->itemtype3 = $row['itemtype3'];
        $accountstree->userid = $row['userid'];
        $accountstree->del = $row['del'];
        $accountstree->sysdate = $row['sysdate'];
        //
        $accountstree->parentName = $row['parentName'];

        return $accountstree;
    }

    protected function getList($sqlQuery) {
        $tab = QueryExecutor::execute($sqlQuery);
        $ret = array();
        for ($i = 0; $i < count($tab); $i++) {
            $ret[$i] = $this->readRow($tab[$i]);
        }
        return $ret;
    }

    /**
     * Get row
     *
     * @return AccountstreeMySql
     */
    protected function getRow($sqlQuery) {
        $tab = QueryExecutor::execute($sqlQuery);
        if (count($tab) == 0) {
            return null;
        }
        return $this->readRow($tab[0]);
    }

    /**
     * Execute sql query
     */
    protected function execute($sqlQuery) {
        return QueryExecutor::execute($sqlQuery);
    }

    /**
     * Execute sql query
     */
    protected function executeUpdate($sqlQuery) {
        return QueryExecutor::executeUpdate($sqlQuery);
    }

    /**
     * Query for one row and one column
     */
    protected function querySingleResult($sqlQuery) {
        return QueryExecutor::queryForString($sqlQuery);
    }

    /**
     * Insert row to table
     */
    protected function executeInsert($sqlQuery) {
        return QueryExecutor::executeInsert($sqlQuery);
    }

}

?>