<?php

/**
 * Class that operate on table 'accountstree'. Database Mysql.
 *
 * @author: http://phpdao.com
 * @date: 2016-03-21 14:29
 */
class AccountstreeMySqlExtDAO extends AccountstreeMySqlDAO {

    public function queryFirstOne() {
        $sql = 'SELECT * FROM accountstree Limit 1';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getExtepensesTypeParents() {
        $sql = 'SELECT *
		FROM accountstree
		WHERE parent = 0
		AND del = 0
                order by theOrder';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByParentExt($parent) {
        $sql = 'SELECT accountstree.*,parenttable.customName as parentName
		FROM accountstree
                left join accountstree as parenttable on accountstree.`parent` = parenttable.id
		WHERE accountstree.parent = ' . $parent . '
		AND accountstree.del=0
                order by accountstree.theOrder';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByParentExtReport1($parent) {
        $sql = 'SELECT *
		FROM accountstree
		WHERE parent = ' . $parent . '
		AND del=0
                and reportid = 1
                order by theOrder';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByParentExtReport2($parent) {
        $sql = 'SELECT *
		FROM accountstree
		WHERE parent = ' . $parent . '
		AND del=0
                and reportid = 2
                order by theOrder';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getlayingOrder($name) {
        $sql = 'SELECT accountstree.*
		FROM accountstree
		WHERE  layingOrder LIKE "%' . $name . '%" AND del=0 order by layingOrder';
        //CONCAT(product.productName, "/",  productcat.productCatName)
        $sqlQuery = new SqlQuery($sql);
        //print($sql);
        return $this->getList($sqlQuery);
    }

    public function getlayingOrder2($name, $idsQueryString) {
        $sql = 'SELECT t.*,parenttable.name as parentName
		FROM accountstree t join accountstree as parenttable on t.`parent` = parenttable.id
                WHERE  (t.layingOrder LIKE "%' . $name . '%" or  t.name LIKE "%' . $name . '%" or  parenttable.name LIKE "%' . $name . '%") AND t.del=0 ' . $idsQueryString . ' order by t.layingOrder ';
        //CONCAT(product.productName, "/",  productcat.productCatName)
        $sqlQuery = new SqlQuery($sql);
        //print_r($sql);
        return $this->getList($sqlQuery);
    }

    public function getlayingOrder3($name) {
        $sql = 'SELECT t.*,parenttable.name as parentName
		FROM accountstree t
                join accountstree as parenttable on t.`parent` = parenttable.id
                WHERE  (t.layingOrder LIKE "%' . $name . '%" or  t.name LIKE "%' . $name . '%"  or  parenttable.name LIKE "%' . $name . '%") AND t.del=0 order by t.layingOrder ';
        //CONCAT(product.productName, "/",  productcat.productCatName)
        $sqlQuery = new SqlQuery($sql);
        //print_r($sql);
        return $this->getList($sqlQuery);
    }

    public function getlayingOrder4($name, $idsQueryString) {
        $sql = 'SELECT t.*,parenttable.name as parentName
		FROM accountstree t left join accountstree as parenttable on t.`parent` = parenttable.id
                WHERE  (t.layingOrder LIKE "%' . $name . '%" or  t.name LIKE "%' . $name . '%" or  parenttable.name LIKE "%' . $name . '%") AND t.del=0 ' . $idsQueryString . '
                ORDER BY case when t.parent=0 then t.theOrder else t.id end * 1000 + t.theOrder ASC ';
        //ORDER BY t.theOrder ';
        //CONCAT(product.productName, "/",  productcat.productCatName)
        $sqlQuery = new SqlQuery($sql);
        //print_r($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByNameAndParent($value, $parentid = 0) {
        $sql = 'SELECT * FROM accountstree WHERE name = ? and parent = ' . $parentid;
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function queryByCustomNameAndParent($value, $parentid = 0) {
        $sql = 'SELECT * FROM accountstree WHERE customName = ? and parent = ' . $parentid;
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function getLastLeafsAll($hideParentStr = '') {//,parent.customName as parentName
        $sql = 'SELECT accountstree.*
		FROM accountstree
                left join accountstree as child on child.parent = accountstree.id
		WHERE child.id is null
		AND accountstree.del=0
                and accountstree.parent not in(' . $hideParentStr . ')
                order by accountstree.theOrder'; //join accountstree as parent on parent.id = accountstree.parent
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function getLastNodeSumById($parentId) {
        $sql = 'SELECT sum(theValue)
		FROM accountstree
		WHERE parent = ' . $parentId . '
		AND del=0';
        $sqlQuery = new SqlQuery($sql);
        return $this->querySingleResult($sqlQuery);
    }

    public function Truncate() {
        $sql = 'TRUNCATE accountstree';
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

}

?>