<?php

/**
 * Class that operate on table 'dailyentry'. Database Mysql.
 *
 * @author: http://phpdao.com
 * @date: 2016-04-04 09:53
 */
class DailyentryMySqlExtDAO extends DailyentryMySqlDAO {

    public function queryAllEXDebtor($dailyEntryId, $queryString) {
        $sql = 'SELECT dailyentry.* ,
				dailyentrydebtor.id as idChild,dailyentrydebtor.dailyentryid,dailyentrydebtor.accountstreeid,dailyentrydebtor.value,dailyentrydebtor.dComment as dComment,
				0 as type ,concat(dailyentrydebtor.accountstreeid,"-",parent.customName,"/",accountstree.customName) as accountstreeName
				FROM dailyentry
				join dailyentrydebtor on dailyentrydebtor.dailyentryid = dailyentry.id
				join accountstree on accountstree.id = dailyentrydebtor.accountstreeid
                                join accountstree parent on parent.id = accountstree.parent
				where dailyentrydebtor.dailyentryid = ' . $dailyEntryId . '
				' . $queryString . '
				order by dailyentry.related,dailyentry.id,dailyentrydebtor.id';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllnothavebranch() {
        //$sql = 'SELECT * FROM dailyentry  where Dailyentry.branchid =  0 ';
        $sql = 'SELECT dailyentry.*,user.branchId as value
                FROM dailyentry
                join user on user.userid = dailyentry.userid
                where dailyentry.branchid =  0
                and user.branchId > 0'; //has certain branch not all branches
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllEXCreditor($queryString, $jon_branch = '') {
        $branch_name = '';
        if ($jon_branch != '') {
            $branch_name = ',branch.branchName';
        }
        $sql = 'SELECT dailyentry.* ,
				dailyentrycreditor.id as idChild,dailyentrycreditor.dailyentryid,dailyentrycreditor.accountstreeid,dailyentrycreditor.value,dailyentrycreditor.dComment as dComment,
				1 as type ,concat(dailyentrycreditor.accountstreeid,"-",parent.customName,"/",accountstree.customName) as accountstreeName' . $branch_name . '
				FROM dailyentry
				join dailyentrycreditor on dailyentrycreditor.dailyentryid = dailyentry.id
				join accountstree on accountstree.id = dailyentrycreditor.accountstreeid
				join accountstree parent on parent.id = accountstree.parent
                                ' . $jon_branch . '
				' . $queryString . '
				order by dailyentry.related,dailyentry.id desc,dailyentrycreditor.id';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllEXDebtorTree($queryString) {
        $sql = 'SELECT dailyentry.* ,
				dailyentrydebtor.id as idChild,dailyentrydebtor.dailyentryid,dailyentrydebtor.accountstreeid,dailyentrydebtor.value,dailyentrydebtor.dComment as dComment,
				0 as type ,concat(dailyentrydebtor.accountstreeid,"-",parent.customName,"/",accountstree.customName) as accountstreeName
				FROM dailyentry
				join dailyentrydebtor on dailyentrydebtor.dailyentryid = dailyentry.id
				join accountstree on accountstree.id = dailyentrydebtor.accountstreeid
                                join accountstree parent on parent.id = accountstree.parent
				' . $queryString . '
				order by dailyentry.related,dailyentry.id,dailyentrydebtor.id';
        //echo $sql . '<br/>';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllEXCreditorTree($queryString) {
        $sql = 'SELECT dailyentry.* ,
				dailyentrycreditor.id as idChild,dailyentrycreditor.dailyentryid,dailyentrycreditor.accountstreeid,dailyentrycreditor.value,dailyentrycreditor.dComment as dComment,
				1 as type ,concat(dailyentrycreditor.accountstreeid,"-",parent.customName,"/",accountstree.customName) as accountstreeName
				FROM dailyentry
				join dailyentrycreditor on dailyentrycreditor.dailyentryid = dailyentry.id
				join accountstree on accountstree.id = dailyentrycreditor.accountstreeid
                                join accountstree parent on parent.id = accountstree.parent
				' . $queryString . '
				order by dailyentry.related,dailyentry.id,dailyentrycreditor.id';
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllEXDebtorTreeReverseType($queryString) {
        $sql = 'SELECT dailyentry.* ,
				dailyentrydebtor.id as idChild,dailyentrydebtor.dailyentryid,dailyentrydebtor.accountstreeid,dailyentrydebtor.value,dailyentrydebtor.dComment as dComment,
				1 as type ,concat(dailyentrydebtor.accountstreeid,"-",parent.customName,"/",accountstree.customName) as accountstreeName
				FROM dailyentry
				join dailyentrydebtor on dailyentrydebtor.dailyentryid = dailyentry.id
				join accountstree on accountstree.id = dailyentrydebtor.accountstreeid
                                join accountstree parent on parent.id = accountstree.parent
				' . $queryString . '
				order by dailyentry.related,dailyentry.id,dailyentrydebtor.id';
        //echo $sql . '<br/>';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllEXCreditorTreeReverseType($queryString) {
        $sql = 'SELECT dailyentry.* ,
				dailyentrycreditor.id as idChild,dailyentrycreditor.dailyentryid,dailyentrycreditor.accountstreeid,dailyentrycreditor.value,dailyentrycreditor.dComment as dComment,
				0 as type ,concat(dailyentrycreditor.accountstreeid,"-",parent.customName,"/",accountstree.customName) as accountstreeName
				FROM dailyentry
				join dailyentrycreditor on dailyentrycreditor.dailyentryid = dailyentry.id
				join accountstree on accountstree.id = dailyentrycreditor.accountstreeid
                                join accountstree parent on parent.id = accountstree.parent
				' . $queryString . '
				order by dailyentry.related,dailyentry.id,dailyentrycreditor.id';
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllEXDebtorTreeMain($queryString, $queryString2) {
        $sql = 'SELECT dailyentry.id ,dailyentrydebtor.value,0 as type
				FROM dailyentry
				join dailyentrydebtor on dailyentrydebtor.dailyentryid = dailyentry.id
				join accountstree on accountstree.id = dailyentrydebtor.accountstreeid
				' . $queryString . ' ' . $queryString2 . '
				order by dailyentry.related,dailyentry.id,dailyentrydebtor.id';
        //echo $sql . '<br/>';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllEXCreditorTreeMain($queryString, $queryString2) {
        $sql = 'SELECT dailyentry.id,dailyentrycreditor.value ,1 as type
				FROM dailyentry
				join dailyentrycreditor on dailyentrycreditor.dailyentryid = dailyentry.id
				join accountstree on accountstree.id = dailyentrycreditor.accountstreeid
				' . $queryString . ' ' . $queryString2 . '
				order by dailyentry.related,dailyentry.id,dailyentrycreditor.id';
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllEXDebtorTreeMainBroCount($queryString, $queryString3) {
        $sql = 'SELECT dailyentry.id
				FROM dailyentry
				join dailyentrydebtor on dailyentrydebtor.dailyentryid = dailyentry.id
                                join accountstree on accountstree.id = dailyentrydebtor.accountstreeid
				' . $queryString . ' ' . $queryString3 . '
				order by dailyentry.related,dailyentry.id,dailyentrydebtor.id';
        //echo $sql . '<br/>';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllEXCreditorTreeMainBroCount($queryString, $queryString3) {
        $sql = 'SELECT dailyentry.id
				FROM dailyentry
				join dailyentrycreditor on dailyentrycreditor.dailyentryid = dailyentry.id
                                join accountstree on accountstree.id = dailyentrycreditor.accountstreeid
				' . $queryString . ' ' . $queryString3 . '
				order by dailyentry.related,dailyentry.id,dailyentrycreditor.id';
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllEXDebtorTreeSimple($queryString) {
        $sql = 'SELECT dailyentry.id
				FROM dailyentry
				join dailyentrydebtor on dailyentrydebtor.dailyentryid = dailyentry.id
				join accountstree on accountstree.id = dailyentrydebtor.accountstreeid
				' . $queryString . '
				order by dailyentry.related,dailyentry.id,dailyentrydebtor.id';
        //echo $sql . '<br/>';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllEXCreditorTreeSimple($queryString) {
        $sql = 'SELECT dailyentry.id
				FROM dailyentry
				join dailyentrycreditor on dailyentrycreditor.dailyentryid = dailyentry.id
				join accountstree on accountstree.id = dailyentrycreditor.accountstreeid
				' . $queryString . '
				order by dailyentry.related,dailyentry.id,dailyentrycreditor.id';
        //echo $sql;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllEXTreeSimpleUnion($queryStringDebtor, $queryStringCreditor, $queryStringGeneral = "") {
        $sql = 'select id from(
                    (
                        SELECT dailyentry.id,dailyentry.related,dailyentrydebtor.id detailId
                        FROM dailyentry
                        join dailyentrydebtor on dailyentrydebtor.dailyentryid = dailyentry.id
                        join accountstree on accountstree.id = dailyentrydebtor.accountstreeid
                        ' . $queryStringDebtor . '
                    ) union (
                        SELECT dailyentry.id,dailyentry.related,dailyentrycreditor.id detailId
                        FROM dailyentry
                        join dailyentrycreditor on dailyentrycreditor.dailyentryid = dailyentry.id
                        join accountstree on accountstree.id = dailyentrycreditor.accountstreeid
                        ' . $queryStringCreditor . '
                    )
                ) as temp
                where 1 ' . $queryStringGeneral . '
                order by related,id,detailId
                limit 10';
        //echo $sql . '<br/>';
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryByEntryComment($value) {
        $sql = 'SELECT * FROM dailyentry WHERE entryComment = ? order br id desc';
        $sqlQuery = new SqlQuery($sql);
        $sqlQuery->set($value);
        return $this->getList($sqlQuery);
    }

    public function getMaxRelated() {
        $sql = 'SELECT max(`related`) as `related` FROM dailyentry';
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function updatebranchid($branchid, $id) {
        $sql = 'UPDATE dailyentry SET branchid = ' . $branchid . ' WHERE id = ' . $id . '';
        //echo $sql . "<br/>";
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

}

?>