<?php

class CommonACC {

    protected $info = array();
    private $db;
    private $year_id;
    private $jv;
    private $user_id;
    private $year_id_actual;

    public function __construct() {
        $this->db = DBConnectorFactory::loadDriver();
        $this->year_id = Session::getCurrentYearID();
        $this->jv = new JV();
        $this->user_id = Session::getUserID();
        $this->year_id_actual = Session::getCurrentYearIDActual();
    }

    public function setData(array $data) {
        $this->info = $data;
        return $this;
    }

    public function add_edit_group_accounts() {
        if ($this->info['is_edit'] == 0) {
            $data = [
                &$this->info['pur_acc_id'],
                &$this->info['pur_tax_acc_id'],
                &$this->info['pur_return_acc_id'],
                &$this->info['pur_return_tax_acc_id'],
                &$this->info['sales_acc_id'],
                &$this->info['sales_tax_acc_id'],
                &$this->info['sales_return_acc_id'],
                &$this->info['sales_return_tax_id'],
                &$this->info['group_name']
            ];
            $this->db->prepare_Stmt('INSERT INTO `tbl_group_accounts`( `pur_acc_id`,
            `pur_tax_acc_id`, `pur_return_acc_id`, `pur_return_tax_acc_id`, `sales_acc_id`,
            `sales_tax_acc_id`, `sales_return_acc_id`, `sales_return_tax_id`, `group_name`)
            values(?,?,?,?,?,?,?,?,?) ', $data);
        } else {
            $data = [
                &$this->info['pur_acc_id'],
                &$this->info['pur_tax_acc_id'],
                &$this->info['pur_return_acc_id'],
                &$this->info['pur_return_tax_acc_id'],
                &$this->info['sales_acc_id'],
                &$this->info['sales_tax_acc_id'],
                &$this->info['sales_return_acc_id'],
                &$this->info['sales_return_tax_id'],
                &$this->info['group_name'],
                &$this->info['group_account_id']
            ];
            $this->db->prepare_Stmt('update  `tbl_group_accounts` set 
            `pur_acc_id` = ?,
            `pur_tax_acc_id`= ?, `pur_return_acc_id`= ?, `pur_return_tax_acc_id`= ?, `sales_acc_id`= ?,
            `sales_tax_acc_id`= ?, `sales_return_acc_id`= ?, `sales_return_tax_id`= ?,group_name = ?
            where group_account_id = ?
            ', $data);
        }

        $error = $this->db->error();
        //        die(print_r($error));
        return $error;
    }

    public function get_group_accounts($id) {
        $row = array();
        $query = $this->db->query('select * from  tbl_group_accounts where group_account_id = "' . $id . '"');
        $row = $this->db->assoc($query);
        if (!empty($row)) {
            $row['pur_acc_name'] = JV::getAccountNameByID($row['pur_acc_id']);
            $row['pur_tax_acc_name'] = JV::getAccountNameByID($row['pur_tax_acc_id']);
            $row['pur_return_acc_name'] = JV::getAccountNameByID($row['pur_return_acc_id']);
            $row['pur_return_tax_acc_name'] = JV::getAccountNameByID($row['pur_return_tax_acc_id']);
            $row['sales_acc_name'] = JV::getAccountNameByID($row['sales_acc_id']);
            $row['sales_tax_acc_name'] = JV::getAccountNameByID($row['sales_tax_acc_id']);
            $row['sales_return_acc_name'] = JV::getAccountNameByID($row['sales_return_acc_id']);
            $row['sales_return_tax_name'] = JV::getAccountNameByID($row['sales_return_tax_id']);
        }
        return $row;
    }

    public function GroupAccountSearch($term = null, $page, $limit) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' where group_name LIKE ? ';
            $data_filter[] = "%" . $term . "%";
        }

        $query = $this->db->prepare_Stmt('SELECT group_account_id as id,group_name as text FROM tbl_group_accounts 
            ' . $cond . ' limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public static function generate_account($parent_id, $with_length) {
        $db = DBConnectorFactory::loadDriver();
        $db->prepare_Stmt("SET @p0='" . $parent_id . "';");
        $db->prepare_Stmt("SET @p1='" . $with_length . "';");
        $db->prepare_Stmt("CALL `sp_get_auto_account_by_parent`(@p0, @p1, @p2);");
        $query = $db->prepare_Stmt("
                            SELECT @p2 AS `acc_code`;SELECT @p2 AS `acc_code`;");
        $row = $db->assoc($query);
        $account_code = $row['acc_code'];
        return $account_code;
    }

    public function coa_generate_code($parent_id, $with_length = 0) {
        $parent_code = JV::getAccountCodeByID($parent_id);
        $generated_code = CommonACC::generate_account($parent_id, $with_length);
        $last_code = substr($generated_code, strlen($parent_code));
        return ['parent_code' => $parent_code, "right_code" => $last_code];
    }

    public function acc_subtype_search($term = null, $page, $limit, $type) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!empty($type)) {
            $cond.= ' and sub_type = ? ';
            $data_filter[] = $type;
        }
        if (!is_null($term)) {
            $cond .=' AND ( Account_Name LIKE ? OR Account_Code LIKE ? )';
            $data_filter[] = "%" . $term . "%";
            $data_filter[] = "%" . $term . "%";
        }

        $query = $this->db->prepare_Stmt('SELECT Account_ID as id,
                                          concat(Account_Name," - ",Account_Code) as text
                                    FROM 
                                        tbl_chart_of_accounts
                                        WHERE Status_ID = 1
                                    ' . $cond . '
                                    limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);

        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public static function account_is_child($parent_id, $child_id) {
        $db = DBConnectorFactory::loadDriver();
        $query = $db->query('select Parent_ID from tbl_chart_of_accounts where Account_ID = "' . $child_id . '" ');
        $current_parent_id = $db->assoc($query)['Parent_ID'];
        //        echo $current_parent_id.'==';
        //        die();
        if ($current_parent_id == $parent_id) {
            return 1;
        } elseif (!empty($current_parent_id)) {
            return CommonACC::account_is_child($parent_id, $current_parent_id);
        }
        return 0;
    }

    public static function change_parent_code($parent_id, $old_code, $new_code, $is_change_itself = true) {
        $db = DBConnectorFactory::loadDriver();
        //        echo'heree';
        $last_code = $new_code . substr($old_code, strlen($old_code));
        //        echo $last_code . '==';
        if ($is_change_itself)
            $db->prepare_Stmt('update tbl_chart_of_accounts set Account_Code = ? where Account_ID = ?', [&$new_code, &$parent_id]);
        $query = $db->query('select Account_ID,Account_Code from tbl_chart_of_accounts where Parent_ID = "' . $parent_id . '"');
        while ($row = $db->assoc($query)) {
            $child_old_code = $row['Account_Code'];
            $child_new_code = $new_code . substr($child_old_code, strlen($old_code));
            $db->prepare_Stmt('update tbl_chart_of_accounts set Account_Code = ? where Account_ID = ?', [&$child_new_code, &$row['Account_ID']]);
            self::change_parent_code($row['Account_ID'], $child_old_code, $child_new_code, false);
        }
    }

    public function add_edit_jobs() {
        $this->db->query('START TRANSACTION');
        if ($this->info['is_edit'] == 0) {

            $data = [
                &$this->info['job_code'],
                &$this->info['job_name'],
                &$this->info['parent_id']
            ];
            $this->db->prepare_Stmt('INSERT INTO `tbl_accounting_jobs`( job_code,`job_name`,parent_id) values(?,?,?) ', $data);
        } else {
            $query = $this->db->query("SELECT job_name,job_code FROM tbl_accounting_jobs WHERE job_id = '" . $this->info['job_id'] . "'");
            $row = $this->db->assoc($query);
            $old_code = $row['job_code'];

            CommonACC::change_job_parent_code($this->info['job_id'], $old_code, $this->info['job_code'], false);
            //           $error = $this->db->error();
            //             print_r($error);
            //             die();
            $data = [

                &$this->info['job_name'],
                &$this->info['job_code'],
                &$this->info['job_id']
            ];
            $this->db->prepare_Stmt('update  `tbl_accounting_jobs` set job_name = ?,job_code = ?
            where job_id = ?
            ', $data);
        }

        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query("COMMIT");
        } else {
            $this->db->query("ROLLBACK");
        }
        return array_unique($error);
    }

    public function get_next_job_code($parent_id) {
        $max_code = 0;
        $query_parent = $this->db->query('select job_code from tbl_accounting_jobs where job_id = "' . $parent_id . '"');
        $parent_code = $this->db->assoc($query_parent)['job_code'];

        $code_length = smc_session()['account_setting']['job_code_length'];
        $right_length = $code_length;
        if ($right_length <= 0)
            $right_length = 1;
        $last_code_query = $this->db->query('select c.job_code from tbl_accounting_jobs c
                where c.Parent_ID = "' . $parent_id . '" order by cast(c.job_code as unsigned) desc limit 1');
        $last_code = $this->db->assoc($last_code_query)['job_code'];

        if (is_null($last_code))
            $max_code = 0;
        else
            $max_code = substr($last_code, (strlen($parent_code)));

        if ($right_length > strlen($max_code))
            $max_code = 0;
        else
            $right_length = strlen($max_code);

        $max_code = $max_code + 1;
        $job_code = $parent_code . str_repeat(0, $right_length - strlen($max_code)) . $max_code;
        return $job_code;
    }

    public function add_edit_trans_types() {
        if ($this->info['is_edit'] == 0) {
            $data = [
                &$this->info['subtype_name'],
                &$this->info['subtype_abbrv']
            ];
            $this->db->prepare_Stmt('INSERT INTO `tbl_accounting_transaction_subtype`
                ( `subtype_name`,subtype_abbrv) values(?,?) ', $data);
        } else {
            $data = [

                &$this->info['subtype_name'],
                &$this->info['subtype_abbrv'],
                &$this->info['subtype_id']
            ];
            $this->db->prepare_Stmt('update  `tbl_accounting_transaction_subtype` set subtype_name = ?,subtype_abbrv=?
            where subtype_id = ?
            ', $data);
        }

        $error = $this->db->error();
        return $error;
    }

    public function get_jobs_by_ids($job_ids) {
        $data = array();
        if (!empty($job_ids)) {
            $query = $this->db->query('select job_id as id, job_name as text from tbl_accounting_jobs where job_id in (' . $job_ids . ')');
            while ($row = $this->db->assoc($query)) {
                $data[] = $row;
            }
        }
        return $data;
    }

    public static function JobSearch($term = null, $page, $limit, $job_ids = null) {
        $db = DBConnectorFactory::loadDriver();
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        if (!is_null($term)) {
            $cond .=' and job_name LIKE "%' . $term . '%"';
        }
        if (!is_null($job_ids)) {
            $cond.=' and job_id in (' . $job_ids . ')';
        }
        $query = $db->query('select 
                                    job_id as id,
                                    concat(job_code," - ",job_name) as text
                                from 
                                    tbl_accounting_jobs
                                    where job_id not in (select distinct parent_id from tbl_accounting_jobs ) 
                                ' . $cond . '
                                limit ' . ($page * $limit) . ',' . ($limit + 1));
        while ($row = $db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function add_edit_family_accounts() {
        if ($this->info['is_edit'] == 0) {
            $data = [
                &$this->info['family_name'],
                &$this->info['family_accounts']
            ];
            $this->db->prepare_Stmt('INSERT INTO `tbl_family_accounts`
                ( `family_name`,family_accounts) values(?,?) ', $data);
        } else {
            $data = [

                &$this->info['family_name'],
                &$this->info['family_accounts'],
                &$this->info['family_id']
            ];
            $this->db->prepare_Stmt('update  `tbl_family_accounts` set family_name = ?,family_accounts=?
            where family_id = ?
            ', $data);
        }

        $error = $this->db->error();
        return $error;
    }

    public function get_family_accounts($id) {
        $data = array();
        $query = $this->db->query('select * from tbl_family_accounts where family_id = "' . $id . '"');
        $data['main'] = $this->db->assoc($query);

        $query_accs = $this->db->query('select Account_ID as id, concat(Account_Name," - ",Account_Code) as text
            from tbl_chart_of_accounts where Account_ID in (' . $data['main']['family_accounts'] . ')');
        while ($row = $this->db->assoc($query_accs)) {
            $data['accounts'][] = $row;
        }
        return $data;
    }

    public static function get_family_accs_by_id($id) {
        $db = DBConnectorFactory::loadDriver();
        $query = $db->query('select family_accounts from tbl_family_accounts where family_id = "' . $id . '"');
        $family_accounts = $db->assoc($query)['family_accounts'];
        return $family_accounts;
    }

    public function get_job_tree($parent_id = 0) {
        $data = array();
        $query = $this->db->query('select * from tbl_accounting_jobs where parent_id = "' . $parent_id . '"');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public static function change_job_parent_code($parent_id, $old_code, $new_code, $is_change_itself = true) {

        $db = DBConnectorFactory::loadDriver();
        $last_code = $new_code . substr($old_code, strlen($old_code));
        if ($is_change_itself)
            $db->prepare_Stmt('update tbl_accounting_jobs set job_code = ? where job_id = ?', [&$new_code, &$parent_id]);
        $query = $db->query('select job_id,job_code from tbl_accounting_jobs where parent_id = "' . $parent_id . '"');
        //        echo 'select job_id,job_code from tbl_accounting_jobs where parent_id = "' . $parent_id . '"<br>';
        while ($row = $db->assoc($query)) {
            $child_old_code = $row['job_code'];
            $child_new_code = $new_code . substr($child_old_code, strlen($old_code));
            $db->prepare_Stmt('update tbl_accounting_jobs set job_code = ? where job_id = ?', [&$child_new_code, &$row['job_id']]);
            self::change_job_parent_code($row['job_id'], $child_old_code, $child_new_code, false);
        }
    }

    public static function job_is_parent($job_id) {
        $db = DBConnectorFactory::loadDriver();
        $query = $db->query('select job_id from tbl_accounting_jobs where parent_id = "' . $job_id . '" limit 1 ');
        $row = $db->assoc($query)['job_id'];
        if (!empty($row))
            return 1;
        return 0;
    }

    public function get_job_by_id($id) {
        $data = array();
        $query = $this->db->query('select job_name from tbl_accounting_jobs where job_id = "' . $id . '"');
        $data = $this->db->assoc($query);
        return $data;
    }

    public function add_edit_financial_year() {
        $cond = ' ';
        if ($this->info['is_edit'] != 0) {
            $cond.=' and current_year_id <> "' . $this->info['current_year_id'] . '"';
        }
        $query = $this->db->query("select * from tbl_common_current_year
        where ( ('" . $this->info['start_date'] . "' >= start_date and '" . $this->info['start_date'] . "' < end_date)
        or ('" . $this->info['end_date'] . "'> start_date and '" . $this->info['end_date'] . "' <= end_date)
        or ( start_date between '" . $this->info['start_date'] . "' and '" . $this->info['end_date'] . "'  )
        or ( end_date between '" . $this->info['start_date'] . "' and '" . $this->info['end_date'] . "'  ))" . $cond);
        $row = $this->db->assoc($query);
        if (!empty($row)) {
            $error = $this->db->error();
            $error[0] = CommonLanguage::getMessage('duplicate_entry');
            return $error;
        }
        if ($this->info['is_edit'] == 0) {

            $data = [
                &$this->info['current_year'],
                &$this->info['start_date'],
                &$this->info['end_date']
            ];
            $this->db->prepare_Stmt('INSERT INTO `tbl_common_current_year`
                ( `current_year`,start_date,end_date) values(?,?,?) ', $data);
        } else {
            $data = [

                &$this->info['current_year'],
                &$this->info['start_date'],
                &$this->info['end_date'],
                &$this->info['current_year_id']
            ];
            $this->db->prepare_Stmt('update  `tbl_common_current_year` set current_year = ?,start_date=?,end_date=?
            where current_year_id = ?
            ', $data);
        }
        $error = $this->db->error();
        //                die(print_r($error));

        return $error;
    }

    public function submitCashDrawerAccount() {
        $cash_drawer_id = $this->info['cash_drawer_id'];
        if ($cash_drawer_id == 0) {
            $this->db->prepare_Stmt('INSERT INTO tbl_cash_drawer_accounts
                (cash_drawer_title,cash_account_id,branch_id) values(?,?,?) ', [
                &$this->info['cash_drawer_title'], &$this->info['cash_account'], &$this->info['default_branch']
            ]);
        } else {
            $this->db->prepare_Stmt('update  tbl_cash_drawer_accounts set cash_drawer_title = ?,cash_account_id=?
            ,branch_id = ? where cash_drawer_id = ?
            ', [&$this->info['cash_drawer_title'], &$this->info['cash_account'], &$this->info['default_branch'], &$cash_drawer_id]);
        }
        $error = $this->db->error();
        return $error;
    }

    public function getCashDrawerBalance($cash_drawer_id, $is_actual = 0) {
        //for cash drawer get last row if not yet logout depend on the login time else on last logout time
//        $query = $this->db->query('SELECT if(isNULL(logout_date_time),login_date_time,logout_date_time) as open_time
//            from tbl_cash_drawer_amount 
//            where cash_drawer_id = ? order by login_date_time desc limit 1', array(&$cash_drawer_id));
//        $row = $this->db->assoc($query);
//        if (empty($row))
        $open_time = date('Y-m-d ' . smc_session()['default_setting']['default_start_time']);   //needed if first time use of end shift
//        else
//            $open_time = date('Y-m-d H:i:s', strtotime($row['open_time'] . ' +1 second'));
        //
        $cash_account = CommonFunctions::SpecificAttribute('tbl_cash_drawer_accounts', 'cash_drawer_id', $cash_drawer_id, 'cash_account_id');
        $query = $this->db->query('SELECT SUM(Transaction_Amount*Current_Ex_Rate*if(Transaction_Type = "Debit",1,-1)) AS Amount
                                    FROM tbl_accounting_transactions 
                                    WHERE Account_ID = "' . $cash_account . '"
                                        /*AND date_time >= "' . $open_time . '"*/
                                        and is_actual = "' . $is_actual . '" ');
        $row = $this->db->assoc($query);
        return (empty($row) ? 0 : $row['Amount']);
    }

    public function submitCashDrawerAmount() {
        //before submit user to cash drawer make sure that no user still login to same cash drawer
        $tmp_query = $this->db->query('select * from tbl_cash_drawer_amount
             where cash_drawer_id = ? and user_id != ? and isnull(logout_date_time) 
            ', array(&$this->info['cash_drawer_id_ref'], &smc_session()['user_logged_id']));
        $tmp_row = $this->db->assoc($tmp_query);
        if (!empty($tmp_row)) {
            $login_user_name = CommonFunctions::SpecificAttribute('tbl_common_users', 'User_ID', $tmp_row['user_id'], 'User_Name');
            return array("cash drawer already login by user <span style='font-weight:bold;'>[{$login_user_name}]</span>, user must logout first.");
        }
        //
        $this->db->query('START TRANSACTION');
        $today = Date::getToday();
        $date_time = !empty($this->info['login_date_time']) ? $this->info['login_date_time'] : date('Y-m-d H:i:s');
        $user_id = !empty($this->info['user_id']) ? $this->info['user_id'] : smc_session()['user_logged_id'];
        $cash_drawer_account_id = CommonFunctions::SpecificAttribute('tbl_cash_drawer_accounts', 'cash_drawer_id', $this->info['cash_drawer_id_ref'], 'cash_account_id');
        $app_user_id = !empty($this->info['app_user_id']) ? $this->info['app_user_id'] : 0;
        $drawer_app_id = !empty($this->info['drawer_app_id']) ? $this->info['drawer_app_id'] : 0;
        $app_branch_id = isset($this->info['app_branch_id']) ? $this->info['app_branch_id'] : 0;
        //
        $system_amount = !empty($this->info['system_amount']) ? $this->info['system_amount'] : $this->getCashDrawerBalance($this->info['cash_drawer_id_ref']);
//        $system_amount = abs($system_amount);
        $this->db->prepare_Stmt('INSERT INTO tbl_cash_drawer_amount
                (login_system_amount,real_amount,cash_drawer_id,login_date_time,user_id,drawer_app_id,app_user_id,app_branch_id) 
                values(?,?,?,?,?,?,?,?) ', [
            &$system_amount, &$this->info['real_amount'],
            &$this->info['cash_drawer_id_ref'], &$date_time, &$user_id, &$drawer_app_id, &$app_user_id, &$app_branch_id
        ]);
        if (empty($this->info['app_user_id'])) {
            $difference = $system_amount - $this->info['real_amount'];
            //        $operation = $difference > 0 ? '+' : '-';
            $description = "difference  " . ($difference < 0 ? "deposit" : "cash withdrawal") . " for " . smc_session()['user_logged_username'];
            //        $data = ['cash_drawer_id_ref' => $cash_drawer_account_id,
            //            'Currency_ID' => smc_session()['local_currency'],
            //            'Org_Type' => 1,
            //            'Deposit' => [['Deposit_Quantity' => abs($difference),
            //            'Operation' => $operation,
            //            'Currency_ID' => smc_session()['local_currency']]],
            //            'description' => $description
            //        ];
            $from_account = $difference > 0 ? $cash_drawer_account_id : smc_session()['user_accounts']['withdrawal_account_id'];
            $to_account = $difference > 0 ? smc_session()['user_accounts']['deposit_account_id'] : $cash_drawer_account_id;
            $data = array('Deposit_WithDrawal_Date' => $today,
                'Is_Actual' => 0,
                'Deposit_WithDrawal_Description' => $description,
                'To_Account' => $to_account,
                'From_Account' => $from_account,
                '0' => $user_id,
                'Deposit_WithDrawal_Amount' => abs($difference),
                'Deposit_WithDrawal_Amount_To' => abs($difference),
                'Currency_To' => smc_session()['local_currency'],
                'Currency_From' => smc_session()['local_currency']);
            //         $_POST['Deposit_WithDrawal_Date'] = $today;
            //                            $_POST['Is_Actual'] = 0;
            //                            $_POST['Deposit_WithDrawal_Description'] = ($type == 1 ? "Deposit To " : "Withdrawal From " ) . smc_session()['user_logged_username'] . ' pos cash.';
            //                            $_POST['To_Account'] = $type == 1 ? $cash_drawer_account_id : smc_session()['user_accounts']['withdrawal_account_id'];
            //                            $_POST['From_Account'] = $type == 1 ? smc_session()['user_accounts']['deposit_account_id'] : $cash_drawer_account_id;
            //                            $_POST[0] = Session::getUserID();

            if ($difference != 0)
                $this->jv->setData($data)->addDepositWithDrawal(1, true);
        }


        //        if ($difference > 0) { // withdrawal
        //            $this->jv->setData($data)->addDailyWithDrawal(true);
        //        } else if ($difference < 0) { // deposite
        //            $this->jv->setData($data)->addDailyDeposit(true);
        //        }

        $error = $this->db->error();
        if (empty($error)) {
            smc_session()['user_accounts']['cash_account_id'] = $cash_drawer_account_id;
            $this->db->query("COMMIT");
        } else {
            $this->db->query("ROLLBACK");
        }
        return array_unique($error);
    }

    public function submitCashDrawerAmountLogout() {
        $this->db->query('START TRANSACTION');
        $today = Date::getToday();
        $date_time = !empty($this->info['login_date_time']) ? $this->info['login_date_time'] : date('Y-m-d H:i:s');
        $user_id = !empty($this->info['user_id']) ? $this->info['user_id'] : smc_session()['user_logged_id'];
//        $cash_drawer_account_id = CommonFunctions::SpecificAttribute('tbl_cash_drawer_accounts', 'cash_drawer_id', $this->info['cash_drawer_id_ref'], 'cash_account_id');
        $system_amount = !empty($this->info['system_amount']) ? $this->info['system_amount'] : $this->getCashDrawerBalance($this->info['cash_drawer_id_ref']);
//        $system_amount = abs($system_amount);
        $this->db->prepare_Stmt('Update tbl_cash_drawer_amount set logout_system_amount = ?,
            withdrawal_amount = ?, remaining_amount = ?, logout_date_time = ?
        where user_id = ? and isnull(logout_date_time) and cash_drawer_id = ?', [
            &$system_amount, &$this->info['withdrawal_amount'], &$this->info['remaining_amount'],
            &$date_time, &$user_id, &$this->info['cash_drawer_id_ref']
        ]);
        if (empty($this->info['app_user_id'])) {
            $difference = $system_amount - ($this->info['remaining_amount'] + $this->info['withdrawal_amount']);
            $description = "difference  " . ($difference < 0 ? "deposit" : "cash withdrawal") . " for " . smc_session()['user_logged_username'];

            $from_account = $difference > 0 ? smc_session()['user_accounts']['cash_account_id'] : smc_session()['user_accounts']['withdrawal_account_id'];
            $to_account = $difference > 0 ? smc_session()['user_accounts']['deposit_account_id'] : smc_session()['user_accounts']['cash_account_id'];
            $data = array('Deposit_WithDrawal_Date' => $today,
                'Is_Actual' => 0,
                'Deposit_WithDrawal_Description' => $description,
                'To_Account' => $to_account,
                'From_Account' => $from_account,
                '0' => $user_id,
                'Deposit_WithDrawal_Amount' => abs($difference),
                'Deposit_WithDrawal_Amount_To' => abs($difference),
                'Currency_To' => smc_session()['local_currency'],
                'Currency_From' => smc_session()['local_currency']);
            if ($difference != 0)
                $this->jv->setData($data)->addDepositWithDrawal(1, true);
            // automatic withdrawal
            if ($this->info['withdrawal_amount'] != 0) {
                $description_automatic_withdrawal = "automatic withdrawal for " . smc_session()['user_logged_username'];
                $automatic_withdrawal_data = array('Deposit_WithDrawal_Date' => $today,
                    'Is_Actual' => 0,
                    'Deposit_WithDrawal_Description' => $description_automatic_withdrawal,
                    'To_Account' => smc_session()['user_accounts']['withdrawal_account_id'],
                    'From_Account' => smc_session()['user_accounts']['cash_account_id'],
                    '0' => $user_id,
                    'Deposit_WithDrawal_Amount' => abs($this->info['withdrawal_amount']),
                    'Deposit_WithDrawal_Amount_To' => abs($this->info['withdrawal_amount']),
                    'Currency_To' => smc_session()['local_currency'],
                    'Currency_From' => smc_session()['local_currency']);
                $this->jv->setData($automatic_withdrawal_data)->addDepositWithDrawal(1, true);
            }
        }
        $error = $this->db->error();
        if (empty($error)) {
//            smc_session()['user_accounts']['cash_account_id'] = $cash_drawer_account_id;
            $this->db->query("COMMIT");
        } else {
            $this->db->query("ROLLBACK");
        }
        return array_unique($error);
    }

    public function getCashDrawerLogoutData() {
        $data = array();
        $query = $this->db->query('select drawer.cash_drawer_title, drawer.cash_account_id, amounts.*
            from  tbl_cash_drawer_amount amounts 
                inner join tbl_cash_drawer_accounts drawer using(cash_drawer_id) 
             where amounts.user_id = "' . smc_session()['user_logged_id'] . '" and isnull(logout_date_time)');
        $data = $this->db->assoc($query);
        return $data;
    }

    public function cashDrawerSearch($term = null, $page, $limit) {

        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .=' and cash_drawer_title LIKE ? ';
            $data_filter[] = "%" . $term . "%";
        }
        $query = $this->db->prepare_Stmt('select 
                                    cash_drawer_id as id,
                                    cash_drawer_title as text
                                from 
                                    tbl_cash_drawer_accounts
                                where branch_id = "' . (empty(smc_session()['user_accounts']['branch_id']) ? smc_session()['default_setting']['default_branch_id'] : smc_session()['user_accounts']['branch_id']) . '"
                                ' . $cond . '
                                limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public static function get_pay_acc_by_type($type) {
        $db = DBConnectorFactory::loadDriver();
        $data = array();
        $sub_type = ($type == "credit_card" ? "credit" : $type);
        $query = $db->query('SELECT Account_ID
                                    FROM 
                                        tbl_chart_of_accounts
                                        WHERE  main_type="pay" and concat(",",sub_type,",") like "%,' . $sub_type . ',%" 
                                    ');
        while ($row = $db->assoc($query)) {
            $data[] = $row['Account_ID'];
        }
        return $data;
    }

    public function insert_cash_drawer_sync() {
        $app_user_id = !empty($this->info['app_user_id']) ? $this->info['app_user_id'] : 0;
        $drawer_app_id = !empty($this->info['drawer_app_id']) ? $this->info['drawer_app_id'] : 0;
        $app_branch_id = isset($this->info['app_branch_id']) ? $this->info['app_branch_id'] : 0;
        //
        $data = [
            &$this->info['login_system_amount'],
            &$this->info['real_amount'],
            &$this->info['cash_drawer_id'],
            &$this->info['user_id'],
            &$this->info['login_date_time'],
            &$this->info['logout_system_amount'],
            &$this->info['withdrawal_amount'],
            &$this->info['remaining_amount'],
            &$this->info['logout_date_time'],
            &$drawer_app_id,
            &$app_user_id,
            &$app_branch_id
        ];
        $this->db->prepare_Stmt('INSERT INTO `tbl_cash_drawer_amount`(
            ' . (!empty($this->info['fill_auto_id']) ? 'cash_drawer_amount_id,' : '') . '
            `login_system_amount`, `real_amount`, `cash_drawer_id`, `user_id`, `login_date_time`,
            `logout_system_amount`, `withdrawal_amount`, `remaining_amount`, `logout_date_time`,
            `drawer_app_id`, `app_user_id`, `app_branch_id`)
            values(' . (!empty($this->info['fill_auto_id']) ? intval($this->info['fill_auto_id']) . ',' : '') . '?,?,?,?,?,?,?,?,?,?,?,?)', $data);
        $error = $this->db->error();
        return $error;
    }

    public function update_cash_drawer_sync() {
        $data = [
            &$this->info['login_system_amount'],
            &$this->info['real_amount'],
            &$this->info['cash_drawer_id'],
            &$this->info['user_id'],
            &$this->info['login_date_time'],
            &$this->info['logout_system_amount'],
            &$this->info['withdrawal_amount'],
            &$this->info['remaining_amount'],
            &$this->info['logout_date_time'],
            &$this->info['cash_drawer_amount_id_exist']
        ];
        $this->db->prepare_Stmt('update `tbl_cash_drawer_amount` set
            `login_system_amount`=?, `real_amount`=?, `cash_drawer_id`=?, `user_id`=?, `login_date_time`=?
            ,`logout_system_amount`=?, `withdrawal_amount`=?, `remaining_amount`=?, `logout_date_time`=?
            where cash_drawer_amount_id = ?
            ', $data);
        $error = $this->db->error();
        return $error;
    }

    public function add_edit_dynamic_report() {
        $this->db->query('START TRANSACTION');
        if ($this->info['is_edit'] == 0) {
            $this->db->prepare_Stmt('insert into tbl_dynamic_report_template 
                ( report_name,role_ids,currency_id,type ,show_menu,is_hide_unmoved) values (?,?,?,?,?,?)', [&$this->info['report_name'], &$this->info['role_ids'],
                &$this->info['currency_id'], &$this->info['type'], &$this->info['show_menu'], &$this->info['is_hide_unmoved']]);
            $report_id = $this->db->id();
            foreach ($this->info['sub'] as $sub) {
                $this->db->prepare_Stmt('INSERT INTO `tbl_dynamic_report_template_sub`( `sub_title`, `report_id`)
                    VALUES(?,?) ', [&$sub['sub_title'], &$report_id]);
                $sub_id = $this->db->id();
                foreach ($sub['accounts'] as $account) {
                    $account_id = $account['sub_type'] == 1 ? $account['account_id'] : 0;
                    $detail_sub_id = $account['sub_type'] == 2 ? $account['account_id'] : 0;
                    $trans_type = !empty($account['trans_type']) ? implode(',', $account['trans_type']) : '';
                    $this->db->prepare_Stmt('insert into `tbl_dynamic_report_template_details`
                    ( `account_id`, `operation`,with_child,sub_id,detail_sub_id,detail_sub_title,trans_types) 
                    values(?,?,?,?,?,?,?) ', [&$account_id, &$account['operation'],
                        &$account['with_child'], &$sub_id, &$detail_sub_id, &$account['detail_sub_title'], &$trans_type]);
                }
            }
        } else {
            $this->db->prepare_Stmt('update tbl_dynamic_report_template 
                set report_name=?,role_ids=?,currency_id=?,type=?,show_menu=?,is_hide_unmoved=? where report_id = ?', [&$this->info['report_name'], &$this->info['role_ids'],
                &$this->info['currency_id'], &$this->info['type'], &$this->info['show_menu'], &$this->info['is_hide_unmoved'],
                &$this->info['report_id']]);
            //
            $this->db->prepare_Stmt(' delete d.* from tbl_dynamic_report_template_details d
                join tbl_dynamic_report_template_sub s on s.sub_id = d.sub_id
                where report_id = ?', [&$this->info['report_id']]);
            //
            $this->db->prepare_Stmt(' delete from tbl_dynamic_report_template_sub where report_id = ?', [&$this->info['report_id']]);
            //
            foreach ($this->info['sub'] as $sub) {
                $this->db->prepare_Stmt('INSERT INTO `tbl_dynamic_report_template_sub`( `sub_title`, `report_id`)
                    VALUES(?,?) ', [&$sub['sub_title'], &$this->info['report_id']]);
                $sub_id = $this->db->id();
                foreach ($sub['accounts'] as $account) {
                    $account_id = $account['sub_type'] == 1 ? $account['account_id'] : 0;
                    $detail_sub_id = $account['sub_type'] == 2 ? $account['account_id'] : 0;
                    $trans_type = !empty($account['trans_type']) ? implode(',', $account['trans_type']) : '';

                    $this->db->prepare_Stmt('insert into `tbl_dynamic_report_template_details`
                    ( `account_id`, `operation`,with_child,sub_id,detail_sub_id,detail_sub_title,trans_types) 
                    values(?,?,?,?,?,?,?) ', [&$account_id, &$account['operation'], &$account['with_child'],
                        &$sub_id, &$detail_sub_id, &$account['detail_sub_title'], &$trans_type]);
                }
            }
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query("COMMIT");
        } else {
            $this->db->query("ROLLBACK");
        }
        return array_unique($error);
    }

    public function get_dynamic_report_by_id($id) {
        $data = array();
        $query = $this->db->query('select * from tbl_dynamic_report_template t
            join tbl_common_currency c on c.Currency_ID = t.currency_id
            where report_id = "' . $id . '"');
        $data['main'] = $this->db->assoc($query);
        //
        $query_sub = $this->db->query('select * from tbl_dynamic_report_template_sub where report_id = "' . $id . '"');
        while ($row = $this->db->assoc($query_sub)) {
            $data['details'][$row['sub_id']] = $row;
        }
        //
        $query_details = $this->db->query('select d.*,ifnull(a.Account_Name,"") as account_name,
             ifnull(s.sub_title,"") as sub_title 
            from tbl_dynamic_report_template_details d
            left join tbl_chart_of_accounts a on a.Account_ID = d.account_id 
            left join tbl_dynamic_report_template_sub s on s.sub_id = d.detail_sub_id
            where d.sub_id in (' . implode(',', array_keys($data['details'])) . ')');
        while ($row = $this->db->assoc($query_details)) {
            $row['sub_type'] = $row['account_id'] == 0 ? 2 : 1;
            $row['trans_types'] = explode(',', $row['trans_types']);
            $data['details'][$row['sub_id']]['accounts'][] = $row;
        }
        //
        $query_role = $this->db->query('select Role_ID as id, Role_Name as text from tbl_common_roles
            where Role_ID in (' . $data['main']['role_ids'] . ')');
        while ($row = $this->db->assoc($query_role)) {
            $data['role_ids'][] = $row;
        }

        return $data;
    }

    public function delete_dynamic_report($id) {
        $this->db->query('START TRANSACTION');
        $this->db->prepare_Stmt(' delete d.* from tbl_dynamic_report_template_details d
                join tbl_dynamic_report_template_sub s on s.sub_id = d.sub_id
                where report_id = ?', [&$id]);
        //
        $this->db->prepare_Stmt(' delete from tbl_dynamic_report_template_sub where report_id = ?', [&$id]);
        //
        $this->db->prepare_Stmt(' delete from tbl_dynamic_report_template where report_id = ?', [&$id]);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query("COMMIT");
        } else {
            $this->db->query("ROLLBACK");
        }
        return array_unique($error);
    }

    public function search_report_by_role($term = null, $page, $limit) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        if (!is_null($term)) {
            $cond .= ' where concat(",",role_ids,",") LIKE "%,' . smc_session()['user_role_id'] . ',%"';
        }

        $query = $this->db->query('SELECT report_id as id,report_name as text FROM tbl_dynamic_report_template 
            ' . $cond . ' limit ' . ($page * $limit) . ',' . ($limit + 1));
        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function CurrencySearch($term = null, $page, $limit) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' and Currency_Name LIKE ? ';
            $data_filter[] = "%" . $term . "%";
        }

        $query = $this->db->prepare_Stmt('SELECT Currency_ID as id,concat(Currency_Name," - ",Currency_Code) as text,Currency_Symbol
            FROM tbl_common_currency
            where Currency_ID in (' . (implode(',', array_keys(smc_session()['system_currencies']))) . ')
            ' . $cond . ' limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public static function account_is_parent($account_id) {
        $db = DBConnectorFactory::loadDriver();
        $query = $db->query('select Account_ID from tbl_chart_of_accounts where Parent_ID = "' . $account_id . '" limit 1 ');
        $row = $db->assoc($query)['Account_ID'];
        if (!empty($row))
            return 1;
        return 0;
    }

    public function add_edit_projects() {
        $this->db->query('START TRANSACTION');
        if ($this->info['is_edit'] == 0) {

            $data = [
                &$this->info['project_code'],
                &$this->info['project_name'],
                &$this->info['parent_id']
            ];
            $this->db->prepare_Stmt('INSERT INTO `tbl_projects`( project_code,`project_name`,parent_id) values(?,?,?) ', $data);
        } else {
            $query = $this->db->query("SELECT project_name,project_code FROM tbl_projects WHERE project_id = '" . $this->info['project_id'] . "'");
            $row = $this->db->assoc($query);
            $old_code = $row['project_code'];

            CommonACC::change_project_parent_code($this->info['project_id'], $old_code, $this->info['project_code'], false);
            //           $error = $this->db->error();
            //             print_r($error);
            //             die();
            $data = [

                &$this->info['project_name'],
                &$this->info['project_code'],
                &$this->info['project_id']
            ];
            $this->db->prepare_Stmt('update  `tbl_projects` set project_name = ?,project_code = ?
            where project_id = ?
            ', $data);
        }

        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query("COMMIT");
        } else {
            $this->db->query("ROLLBACK");
        }
        return array_unique($error);
    }

    public function get_next_project_code($parent_id) {
        $max_code = 0;
        $query_parent = $this->db->query('select project_code from tbl_projects where project_id = "' . $parent_id . '"');
        $parent_code = $this->db->assoc($query_parent)['project_code'];

        $code_length = smc_session()['account_setting']['project_code_length'];
        $right_length = $code_length;
        if ($right_length <= 0)
            $right_length = 1;
        $last_code_query = $this->db->query('select c.project_code from tbl_projects c
                where c.parent_id = "' . $parent_id . '" order by cast(c.project_code as unsigned) desc limit 1');
        $last_code = $this->db->assoc($last_code_query)['project_code'];

        if (is_null($last_code))
            $max_code = 0;
        else
            $max_code = substr($last_code, (strlen($parent_code)));

        if ($right_length > strlen($max_code))
            $max_code = 0;
        else
            $right_length = strlen($max_code);

        $max_code = $max_code + 1;
        $project_code = $parent_code . str_repeat(0, $right_length - strlen($max_code)) . $max_code;
        return $project_code;
    }

    public function get_projects_by_ids($project_ids) {
        $data = array();
        $query = $this->db->query('select project_id as id, project_name as text from tbl_projects where project_id in (' . $project_ids . ')');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public static function projectSearch($term = null, $page, $limit) {
        $db = DBConnectorFactory::loadDriver();
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        if (!is_null($term)) {
            $cond .=' and project_name LIKE "%' . $term . '%"';
        }

        $query = $db->query('select 
                                    project_id as id,
                                    concat(project_code," - ",project_name) as text
                                from 
                                    tbl_projects
                                    where project_id not in (select distinct parent_id from tbl_projects ) 
                                ' . $cond . '
                                limit ' . ($page * $limit) . ',' . ($limit + 1));
        while ($row = $db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function get_project_tree($parent_id = 0) {
        $data = array();
        $query = $this->db->query('select * from tbl_projects where parent_id = "' . $parent_id . '"');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public static function change_project_parent_code($parent_id, $old_code, $new_code, $is_change_itself = true) {

        $db = DBConnectorFactory::loadDriver();
        $last_code = $new_code . substr($old_code, strlen($old_code));
        if ($is_change_itself)
            $db->prepare_Stmt('update tbl_projects set project_code = ? where project_id = ?', [&$new_code, &$parent_id]);
        $query = $db->query('select project_id,project_code from tbl_projects where parent_id = "' . $parent_id . '"');
        while ($row = $db->assoc($query)) {
            $child_old_code = $row['project_code'];
            $child_new_code = $new_code . substr($child_old_code, strlen($old_code));
            $db->prepare_Stmt('update tbl_projects set project_code = ? where project_id = ?', [&$child_new_code, &$row['project_id']]);
            self::change_project_parent_code($row['project_id'], $child_old_code, $child_new_code, false);
        }
    }

    public static function project_is_parent($project_id) {
        $db = DBConnectorFactory::loadDriver();
        $query = $db->query('select project_id from tbl_projects where parent_id = "' . $project_id . '" limit 1 ');
        $row = $db->assoc($query)['project_id'];
        if (!empty($row))
            return 1;
        return 0;
    }

    public function get_project_by_id($id) {
        $data = array();
        $query = $this->db->query('select project_name from tbl_projects where project_id = "' . $id . '"');
        $data = $this->db->assoc($query);
        return $data;
    }

    public function add_edit_dynamic_report_blocks() {
        $this->db->query('START TRANSACTION');
        if ($this->info['is_edit'] == 0) {

            $this->db->prepare_Stmt('INSERT INTO `tbl_dynamic_report_template_sub`( `sub_title`, `report_id`)
                    VALUES(?,"0") ', [&$this->info['sub_title']]);
            $sub_id = $this->db->id();
            foreach ($this->info['sub'] as $account) {
                $account_id = $account['sub_type'] == 1 ? $account['account_id'] : 0;
                $detail_sub_id = $account['sub_type'] == 2 ? $account['account_id'] : 0;
                $trans_type = $account['sub_type'] == 3 ? $account['stock_type'] : (!empty($account['trans_type']) ? implode(',', $account['trans_type']) : '');
                $this->db->prepare_Stmt('insert into `tbl_dynamic_report_template_details`
                    ( `account_id`, `operation`,with_child,sub_id,detail_sub_id,detail_sub_title,trans_types,sub_type) 
                    values(?,?,?,?,?,?,?,?) ', [&$account_id, &$account['operation'],
                    &$account['with_child'], &$sub_id, &$detail_sub_id, &$account['detail_sub_title'], &$trans_type, $account['sub_type']]);
            }
        } else {
            $this->db->prepare_Stmt('update `tbl_dynamic_report_template_sub` set `sub_title`=?
                    where sub_id = ? ', [&$this->info['sub_title'], &$this->info['sub_id']]);
            //
            $this->db->prepare_Stmt('delete from tbl_dynamic_report_template_details where 
                sub_id = ?', [&$this->info['sub_id']]);
            foreach ($this->info['sub'] as $account) {
                $account_id = $account['sub_type'] == 1 ? $account['account_id'] : 0;
                $detail_sub_id = $account['sub_type'] == 2 ? $account['account_id'] : 0;
                $trans_type = $account['sub_type'] == 3 ? $account['stock_type'] : (!empty($account['trans_type']) ? implode(',', $account['trans_type']) : '');

                $this->db->prepare_Stmt('insert into `tbl_dynamic_report_template_details`
                    ( `account_id`, `operation`,with_child,sub_id,detail_sub_id,detail_sub_title,trans_types,sub_type) 
                    values(?,?,?,?,?,?,?,?) ', [&$account_id, &$account['operation'],
                    &$account['with_child'], &$this->info['sub_id'], &$detail_sub_id, &$account['detail_sub_title'],
                    &$trans_type, $account['sub_type']]);
            }
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query("COMMIT");
        } else {
            $this->db->query("ROLLBACK");
        }
        return array_unique($error);
    }

    public static function search_dynamic_blocks($term = null, $page, $limit) {
        $db = DBConnectorFactory::loadDriver();
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .=' and sub_title LIKE ? ';
            $data_filter[] = "%" . $term . "%";
        }

        $query = $db->prepare_Stmt('select 
                                    sub_id as id,
                                    sub_title as text
                                from 
                                    tbl_dynamic_report_template_sub
                                    where report_id = 0 
                                ' . $cond . '
                                limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function get_dynamic_report_block_by_id($id) {

        $data = array();
        $query = $this->db->query('select * from tbl_dynamic_report_template_sub
            where sub_id = "' . $id . '"');
        $data['main'] = $this->db->assoc($query);

        $query_details = $this->db->query('select d.*,ifnull(a.Account_Name,"") as account_name,
            ifnull(s.sub_title,"") as sub_title 
            from tbl_dynamic_report_template_details d
            left join tbl_chart_of_accounts a on a.Account_ID = d.account_id 
            left join tbl_dynamic_report_template_sub s on s.sub_id = d.detail_sub_id
            where d.sub_id ="' . $id . '"');
        while ($row = $this->db->assoc($query_details)) {
//            $row['sub_type'] = $row['account_id'] == 0 ? 2 : 1;
            $trans_type = $row['trans_types'];
            $row['trans_types'] = $row['sub_type'] == 3 ? array() : explode(',', $trans_type);
            $row['stock_type'] = $row['sub_type'] == 3 ? $trans_type : "";
//            echo"<pre>";print_r($row);
            $data['details'][] = $row;
        }


        return $data;
    }

    public function get_menu_dynamic_reports() {
        $data = array();
        $query = $this->db->query('select report_id,report_name
            from tbl_dynamic_report_template where show_menu = 1');
        while ($row = $this->db->assoc($query)) {
            $data[$row['report_id']] = $row['report_name'];
        }
        return $data;
    }

    public function get_report_name_by_id($id) {
        $report_name = '';
        $query = $this->db->query('select report_name
            from tbl_dynamic_report_template where report_id="' . $id . '"');
        $row = $this->db->assoc($query);
        if (!empty($row))
            $report_name = $row['report_name'];
        return $report_name;
    }

    public static function get_edit_link_by_type($type, $main_id, $is_actual, $jv_id) {
        $link = '';
        $act = $is_actual ? 'act/' : '';
        $permission = '';
        if ($type == 'mjv') {//manual jv
            $link = 'ACC/addJV/' . $act . $jv_id;
            $permission = $is_actual ? 'editJVActual' : 'editJV';
        } else if ($type == 'inv') {//sales invoice
            $link = 'Inventory/addpurchase/sales/' . $act . $main_id;
            $permission = $is_actual ? "editInvoiceActual" : "editInvoice";
        } else if ($type == 'pur') {//purchase invoice
            $link = 'Inventory/addpurchase/' . $act . $main_id;
            $permission = $is_actual ? "editPurchaseActual" : "editPurchase";
        } else if ($type == 'pay') {//payment
            $link = 'ACC/addpayment/' . $act . $main_id;
            $permission = $is_actual ? 'EditPaymentActual' : 'amendPaymentAccount';
        } else if ($type == 'rec') {//receipt
            $link = 'ACC/addpayment/Receipt/' . $act . $main_id;
            $permission = $is_actual ? 'EditReceiptActual' : 'EditReceipt';
        } else if ($type == 'retp') {//payment
            $link = 'ACC/addpayment/return' . $act . $main_id;
            $permission = $is_actual ? 'edit_actual_return_payment' : 'edit_return_payment';
        } else if ($type == 'retr') {//receipt
            $link = 'ACC/addpayment/Receipt/return' . $act . $main_id;
            $permission = $is_actual ? 'edit_actual_return_receipt' : 'edit_return_receipt';
        } else if ($type == 'rin') {//return sales invoice
            $link = 'Inventory/ReturnNoInvoice/sales/' . $act . $main_id;
            $permission = $is_actual ? "edit_act_return_customer" : "edit_return_customer";
        } else if ($type == 'rpr') {//return purchase invoice
            $link = 'Inventory/ReturnNoInvoice/' . $act . $main_id;
            $permission = $is_actual ? "edit_act_return_vendor" : "edit_return_vendor";
        } else if ($type == 'dwt') {//deposit withdrawal
            $link = 'ACC/addDepositWithdrawal/' . $act . $main_id;
            $permission = $is_actual ? "editDepositWithDrawalActual" : "editDepositWithDrawal";
        } else if ($type == 'deb') {//debit note
            $link = 'ACC/DebitNote/vendor/' . $act . $main_id;
            $permission = $is_actual ? "EditActualDebitNote" : "EditDebitNote";
        } else if ($type == 'cre') {//credit note
            $link = 'ACC/DebitNote/' . $act . $main_id;
            $permission = $is_actual ? "EditActualCreditNote" : "EditCreditNote";
        } else if ($type == 'exp') {//credit note
            $link = 'ACC/add_expense/' . $act . $main_id;
            $permission = $is_actual ? "amendExpensesActual" : "amendExpenses";
        } else if ($type == 'spinv') {//credit note
            $link = 'Inventory/salesPurchase/' . $act . $main_id;
            $permission = "edit_sales_purchase";
        } else if ($type == 'pfd') {//project funding
            $link = 'ACC/project_funding/' . $act . $main_id;
            $permission = "edit_project_funding";
        } else if ($type == 'pex') {//project expense
            $link = 'ACC/project_expense/' . $act . $main_id;
            $permission = "edit_project_expense";
        }
        return ['link' => $link, 'permission' => $permission];
    }

    public function get_block_rows($block_id, &$data) {
        $query = $this->db->query('select d.*
            from tbl_dynamic_report_template_details d
            where d.sub_id = "' . $block_id . '" order by d.detail_id');
        //echo $block_id . '===';
        while ($row = $this->db->assoc($query)) {
            //block
            if (!empty($row['detail_sub_id']))
                $row['details'] = $this->get_block_rows($row['detail_sub_id'], $data);
            //stock
            elseif ($row['sub_type'] == 3) {
                $data[$row['detail_id']] = $row;
            }
        }
        return $data;
    }

    public function get_dynamic_report_stock_rows($report_id) {
        $data = array();
        $query = $this->db->query('select * from tbl_dynamic_report_template_sub s
                where report_id="' . $report_id . '" order by sub_id');
        while ($row = $this->db->assoc($query)) {
            $this->get_block_rows($row['sub_id'], $data);
        }
        return $data;
    }

}
