<?php

class Currency {

    protected $info = array();
    private $db;
    public static $curr_arr = array();

    public function __construct() {
        $this->db = DBConnectorFactory::loadDriver();
    }

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

    public function createCurrency() {
        for ($i = 0; $i < count($this->info['AddCurrency']); $i++) {
            $result = self::viewAllDeafultCurrency($this->info['AddCurrency'][$i]['Currency_ID']);
            if (count($result) == 0) {
                $temp = [&$this->info['AddCurrency'][$i]['Currency_ID'], &$this->info[0]];
                $this->db->prepare_Stmt(" INSERT INTO tbl_default_currency
                                    ( Currency_ID, Logged_ID ) VALUES (?,?) ", $temp);
            }
        }
        $error = $this->db->error();
        if (empty($error))
            $this->db->query('COMMIT');
        else
            $this->db->query('ROLLBACK');
        return array_unique($error);
    }

    public function createExRate() {
        $this->db->query('START TRANSACTION');
        foreach ($this->info['ExRate'] as $item) {
            $data = [&$this->info['Exchange_Rate_Date'],
                &$item['from_currency'],
                &$item['ExchRate'],
                &$item['to_currency'],
                &$this->info[0]];
            $this->db->prepare_Stmt('INSERT INTO tbl_currencies_rate ( Exchange_Rate_Date, from_currency, ExchRate, to_currency, Logged_ID )  VALUES(?,?,?,?,?)', $data);
        }
        $error = $this->db->error();
        if (empty($error))
            $this->db->query('COMMIT');
        else
            $this->db->query('ROLLBACK');
        return array_unique($error);
    }

//    public function viewExRate($from, $to, $rate) {
//        $data = array();
//        $query = $this->db->query('SELECT * FROM tbl_currencies_rate
//                                    WHERE from_currency = "' . $from . '" AND to_currency = "' . $to . '"
//                                    AND ExchRate = "' . $rate . '" ');
//        while ($row = $this->db->assoc($query))
//            $data[] = $row;
//        return $data;
//    }

    public function getAllCurrencies($with_rate_only = true) {
        $data = array();
        $cond = '';
        if ($with_rate_only)
            $cond .= (empty(smc_session()['system_currencies']) ? " and 1 = 2 " : " and Currency_ID in (" . implode(',', array_keys(smc_session()['system_currencies'])) . ") ");
        $query = $this->db->query('SELECT * FROM tbl_common_currency where 1=1 ' . $cond);
        while ($row = $this->db->assoc($query))
            $data[] = $row;
        return $data;
    }

//    public function viewAllCurrency() {
//        return $this->getAllCurrencies();
//    }

    public function viewAllDeafultCurrency($id = null) {
        $data = array();
        $condition = ' ';
        if (!is_null($id))
            $condition .= ' AND b.Currency_ID = "' . $id . '"';
        $query = $this->db->query('SELECT b.*, c.*
                                    FROM tbl_default_currency b
                                    LEFT JOIN tbl_common_currency  c ON b.Currency_ID = c.Currency_ID
                                    WHERE 1=1 ' . $condition);
        while ($row = $this->db->assoc($query))
            $data[] = $row;
        return $data;
    }

    public function getCurrencyRow($CID) {
        if (!isset(self::$curr_arr[$CID])) {
            $query = $this->db->query('SELECT * FROM tbl_common_currency WHERE Currency_ID = "' . $CID . '"');
            self::$curr_arr[$CID] = $this->db->assoc($query);
        }
        return self::$curr_arr[$CID];
    }

    public function getCurrencyCodeByID($CID) {
        if (!isset(self::$curr_arr[$CID])) {
            $query = $this->db->query('SELECT * FROM tbl_common_currency WHERE Currency_ID = "' . $CID . '"');
            self::$curr_arr[$CID] = $this->db->assoc($query);
        }
        return self::$curr_arr[$CID]['Currency_Code'];
    }

    public function getCurrencyIDByCode($code) {
        $query = $this->db->query('SELECT Currency_ID FROM tbl_common_currency WHERE Currency_Code = "' . $code . '"');
        $row = $this->db->assoc($query);
        $Currency_ID = $row['Currency_ID'];
        return $Currency_ID;
    }

    public function getCurrencyNameByID($CID) {
        if (!isset(self::$curr_arr[$CID])) {
            $query = $this->db->query('SELECT * FROM tbl_common_currency WHERE Currency_ID = "' . $CID . '"');
            self::$curr_arr[$CID] = $this->db->assoc($query);
        }
        return self::$curr_arr[$CID]['Currency_Name'];
    }

    public function getCurrencyByID($CID) {
        if (!isset(self::$curr_arr[$CID])) {
            $query = $this->db->query('SELECT * FROM tbl_common_currency WHERE Currency_ID = "' . $CID . '"');
            self::$curr_arr[$CID] = $this->db->assoc($query);
        }
        return array(self::$curr_arr[$CID]['Currency_Code'], self::$curr_arr[$CID]['Currency_Name'], self::$curr_arr[$CID]['currency_round']);
    }

    public function viewCurrencySymbol($CID) {
        if (!isset(self::$curr_arr[$CID])) {
            $query = $this->db->query('SELECT * FROM tbl_common_currency WHERE Currency_ID = "' . $CID . '"');
            self::$curr_arr[$CID] = $this->db->assoc($query);
        }
        return self::$curr_arr[$CID]['Currency_Symbol'];
    }

//    public function getCurrencyRate($from, $to) {
//        $query = $this->db->query('SELECT ExchRate FROM tbl_currencies_rate WHERE from_currency = "' . $from . '" AND to_currency = "' . $to . '"');
//        while ($row = $this->db->assoc($query)) {
//            $ExchangeRate = $row['ExchRate'];
//        }
//        return $ExchangeRate;
//    }

    public function getExchangeRateTable() {
        $data = array();
        $query = $this->db->query('SELECT MAX(Exchange_Rate_ID) AS max_ID, CONCAT(from_currency,to_currency) AS UNQ 
                                FROM tbl_currencies_rate  GROUP BY  UNQ');
        while ($row = $this->db->assoc($query)) {
            $query2 = $this->db->query('SELECT  * FROM tbl_currencies_rate WHERE Exchange_Rate_ID = "' . $row['max_ID'] . '" ');
            $data[] = $this->db->assoc($query2);
        }
        return $data;
    }

    public function getDefaultCurrency() {
        $query = $this->db->query('SELECT Currency_ID from tbl_default_currency WHERE id="1"');
        while ($row = $this->db->assoc($query)) {
            $DefaultCurrencyID = $row['Currency_ID'];
        }
        return $DefaultCurrencyID;
    }

    public function getSecondCurrency() {
        $query = $this->db->query('SELECT Currency_ID from tbl_default_currency WHERE id="2"');
        while ($row = $this->db->assoc($query)) {
            $SecondCurrencyID = $row['Currency_ID'];
        }
        return $SecondCurrencyID;
    }

    public function getThirdCurrency() {
        $query = $this->db->query('SELECT Currency_ID from tbl_default_currency WHERE id="3"');
        while ($row = $this->db->assoc($query)) {
            $SecondCurrencyID = $row['Currency_ID'];
        }
        return $SecondCurrencyID;
    }

    public function getCurrencyByPlacement($placement) {
        $query = $this->db->query('SELECT d.Currency_ID, c.Currency_Symbol, c.currency_round
                                    FROM tbl_default_currency d
                                    LEFT JOIN tbl_common_currency c ON c.Currency_ID = d.Currency_ID
                                    WHERE d.id="' . $placement . '" ');
        $row = $this->db->assoc($query);
        return $row;
    }

    public function updateCurrency() {
        $this->db->query('START TRANSACTION');
        foreach ($this->info['Currency'] as $item) {
            $data = [&$this->info[0],
                &$item['Currency_ID'],
                &$item['Entry_ID']
            ];
            $this->db->prepare_Stmt('UPDATE tbl_default_currency  SET Logged_ID= ?, Currency_ID = ? WHERE id= ?', $data);
        }
        $error = $this->db->error();
        if (empty($error))
            $this->db->query('COMMIT');
        else
            $this->db->query('ROLLBACK');
        return array_unique($error);
    }

    public function returnExchangeRate($from, $to) {
        static $data = array();
        $data_index = $from . '__' . $to;
        if (!isset($data[$data_index])) {
            $data[$data_index] = array();
            if ($from == $to) {
                array_push($data[$data_index], 1, '*');
                return $data[$data_index];
            }
            //search if rate exchange is available and operation is forward
            $query = $this->db->query('SELECT ExchRate FROM tbl_currencies_rate
                            WHERE from_currency = "' . $from . '" AND to_currency = "' . $to . '"
                            ORDER BY  Exchange_Rate_ID DESC LIMIT 1 ');
            $row = $this->db->assoc($query);
            //if result is found it will return array with rate and forward operation(*)
            //if not it will check if it's found on the opposite direction then if it is found it wilol return array
            //with rate value and reverse operation(/)
            //otherwise it will return empty array
            if (!empty($row)) {
                $rate = $row['ExchRate'];
                $operation = '*';
                array_push($data[$data_index], $rate, $operation);
                return $data[$data_index];
            } else {
                $query2 = $this->db->query('SELECT ExchRate FROM tbl_currencies_rate
                            WHERE from_currency = "' . $to . '" AND to_currency = "' . $from . '"
                            ORDER BY  Exchange_Rate_ID DESC  LIMIT 1 ');
                $row2 = $this->db->assoc($query2);
                if (!empty($row2)) {
                    $rate = $row2['ExchRate'];
                    $operation = '/';
                    array_push($data[$data_index], $rate, $operation);
                    return $data[$data_index];
                } elseif ($from != smc_session()['local_currency'] && $to != smc_session()['local_currency']) {
                    //if no rate available betwwen from/to we try to get [from->local->to]
                    $from_local_rate = self::returnExchangeRate($from, smc_session()['local_currency']);
                    $local_rate_to = self::returnExchangeRate(smc_session()['local_currency'], $to);
                    if (!empty($from_local_rate) && !empty($local_rate_to) && $from_local_rate[0] != 0 && $local_rate_to[0] != 0) {
                        $new_rate = ($from_local_rate[1] == '*' ? $from_local_rate[0] : (1 / $from_local_rate[0]));
                        if ($local_rate_to[1] == '*')
                            $new_rate = $new_rate * $local_rate_to[0];
                        else
                            $new_rate = $new_rate / $local_rate_to[0];
                        array_push($data[$data_index], $new_rate, '*');
                    }
                }
            }
        }
        return $data[$data_index];
    }

    //
    public function convertCurrencyTimeExchangeRate($from, $to, $amount, $record_date) {
        $exc_arr = $this->getCurrencyTimeExchangeRate($from, $to, $record_date);
        if (empty($exc_arr['rate']))
            return 0;
        if ($exc_arr['operation'] == '/')
            return $amount / $exc_arr['rate'];
        return $amount * $exc_arr['rate'];
    }

    //
    public function getCurrencyTimeExchangeRate($from, $to, $record_date) {
        static $data = array();
        $data_index = $from . '__' . $to;
        if (!isset($data[$data_index])) {
            $data[$data_index] = array();
            //search if rate exchange is available and operation is forward
            $query = $this->db->query('SELECT ExchRate, Exchange_Rate_Date FROM tbl_currencies_rate
                            WHERE from_currency = "' . $from . '" AND to_currency = "' . $to . '"
                            ORDER BY Exchange_Rate_Date ASC, Exchange_Rate_ID ASC');
            while ($row = $this->db->assoc($query)) {
                $row['operation'] = '*';
                $data[$data_index][] = $row;
            }
            if (!count($data[$data_index])) {
                //if result is found it will return array with rate and forward operation(*)
                //if not it will check if it's found on the opposite direction then if it is found it wilol return array
                //with rate value and reverse operation(/)
                $query = $this->db->query('SELECT ExchRate, Exchange_Rate_Date FROM tbl_currencies_rate
                            WHERE from_currency = "' . $to . '" AND to_currency = "' . $from . '"
                            ORDER BY Exchange_Rate_Date ASC, Exchange_Rate_ID ASC');
                while ($row = $this->db->assoc($query)) {
                    $row['operation'] = '/';
                    $data[$data_index][] = $row;
                }
            }
        }
        //
        $record_date = substr($record_date, 0, 10);   //if it is time convert it to date only
        $exchange_rate = null;
        $operation = null;
        foreach ($data[$data_index] as $exchange_row) {
            if ($record_date < $exchange_row['Exchange_Rate_Date']) {
                if (is_null($exchange_rate)) {
                    $exchange_rate = $exchange_row['ExchRate'];
                    $operation = $exchange_row['operation'];
                }
                return array('rate' => $exchange_rate, 'operation' => $operation);
            }
            $exchange_rate = $exchange_row['ExchRate'];
            $operation = $exchange_row['operation'];
        }
        return array('rate' => $exchange_rate, 'operation' => $operation);
    }

    public function returnExchangedCurrencyValue($from, $current_value) {
        $to = self::getDefaultCurrency(); //return default currency
        if ($from != $to) {
            $data = self::returnExchangeRate($from, $to);
            if (count($data) != 0) {
                if ($data[1] == '*')
                    $final_value = $current_value * $data[0];
                else
                    $final_value = $current_value / $data[0];
            } else
                $final_value = null;
        } else
            $final_value = $current_value;
        return $final_value;
    }

    public function returnLocalCurrencyValue($from, $current_value) {
        $to = self::viewLocalCurrency(); //return default currency
        if ($from != $to) {
            $data = self::returnExchangeRate($from, $to);
            if (count($data) != 0) {
                if ($data[1] == '*')
                    $final_value = $current_value * $data[0];
                else
                    $final_value = $current_value / $data[0];
            } else
                $final_value = null;
        } else
            $final_value = $current_value;
        return $final_value;
    }

    public function GetLIListCurrency() {
        $this->db->setNames();
        $DefIDs = [];
        $all_currencies = array_values(smc_session()['system_currencies']);
//        $all_currencies = self::getAllCurrencies();
        $all_defaultcurrencies = self::viewAllDeafultCurrency();
        $RET = "";

        for ($x = 0; $x < count($all_defaultcurrencies); $x++) {
            $DefIDs[] = $all_defaultcurrencies[$x]['Currency_ID'];
        }
        for ($x = 0; $x < count($all_currencies); $x++) {
            $CurrencyID = $all_currencies[$x]['Currency_ID'];
            $CurrencySymbol = $all_currencies[$x]['Currency_Symbol'];
            $CurrencyName = $all_currencies[$x]['Currency_Name'];
            $CurrencyCode = $all_currencies[$x]['Currency_Code'];
            $CurrencyRound = $all_currencies[$x]['currency_round'];
            $DoShow = in_array($all_currencies[$x]['Currency_ID'], $DefIDs); //$all_currencies[$x]['Currency_ID'] == 84 || $all_currencies[$x]['Currency_ID'] == 144 || $all_currencies[$x]['Currency_ID'] == 47;
            $RET .= '<li style="' . ($DoShow == true ? "" : "display:none") . '" MySymbol="' . $CurrencySymbol . '" data-round="' . $CurrencyRound . '" value="' . $CurrencyID . '"><a>' . $CurrencyName . ' - ' . $CurrencyCode . '</a></li>';
        }

        return $RET;
    }

    public function returnExchangedBetweenTwoCurrencies($from, $to, $current_value) {
        if ($from != $to) {
            $data = self::returnExchangeRate($from, $to);
            if (count($data) != 0) {
                if ($data[1] == '*')
                    $final_value = $current_value * $data[0];
                else
                    $final_value = $current_value / $data[0];
            } else
                $final_value = null;
        } else
            $final_value = $current_value;
        return $final_value;
    }

    public function getCurrencyID() {
        $query = $this->db->query('SELECT id FROM tbl_default_currency ORDER BY id DESC LIMIT 1 ');
        while ($row = $this->db->assoc($query))
            $rows = $row['id'];
        return $rows;
    }

//    public function viewExchangeRate($from_currency, $to_currency, $date = null, $year = null, $month = null, $from_date = null, $to_date = null) {
//        $data = array();
//        $condition = '';
//        if (!is_null($date))
//            $condition .= ' AND Exchange_Rate_Date = "' . $date . '"';
//        if (!is_null($month))
//            $condition .= ' AND MONTH(Exchange_Rate_Date) = "' . $month . '"';
//        if (!is_null($year))
//            $condition .= ' AND YEAR(Exchange_Rate_Date) = "' . $year . '"';
//        if (!is_null($from_date) and ! is_null($to_date))
//            $condition .= ' AND Exchange_Rate_Date >= "' . $from_date . '" AND Exchange_Rate_Date <= "' . $to_date . '"';
//        $query = $this->db->query('SELECT
//                                        ExchRate AS Exchange_Rate,
//                                        Exchange_Rate_Date AS Transaction_Date
//                                    FROM 
//                                        tbl_currencies_rate 
//                                    WHERE
//                                        from_currency = "' . $from_currency . '"
//                                    AND 
//                                        to_currency = "' . $to_currency . '"
//                                    ' . $condition
//        );
//        while ($row = $this->db->assoc($query)) {
//            $data[] = $row;
//        }
//        return $data;
//    }

    public function viewCurrenciesCodesWithSymbols() {
        $data = array();
        $query = $this->db->query('SELECT Currency_Code, CONCAT(Currency_Code, " - ", Currency_Symbol) AS Currency, Currency_ID
                                    FROM  tbl_common_currency ');
        while ($row = $this->db->assoc($query))
            $data[] = $row;
        return $data;
    }

    public function viewLocalCurrency() {
        return smc_session()['local_currency'];
    }

//    public function selectExchangeRateAccounts($flag = false, $date = null) {
//        $local_currency = self::viewLocalCurrency();
//        $data = array();
////        $diff_exchange_balance = 0.0;
//        $diff_exchange_balance = array();
//        $diff_exchange_balance_accs = array();
//        $cond = ' ';
//        if (!empty($date)) {
//            $cond .= ' and t.Transaction_Date <= "' . $date . '"';
//        }
//        $year_id = Session::getCurrentYearID($flag);
//
////        $balance_current = 0.0; //balance according to current exchange rate
////        $balance_old = 0.0; //balance according to date of transaction exchange rate
//        $balance = 0.0; //difference of exchange between 2 balances
//        $arr_acc = array();
//        $query = $this->db->query('SELECT 
//                                        sum(if(t.Transaction_Type="Credit",t.local_amount*-1,t.local_amount)) as current_amount,
//                                        sum(if(t.Transaction_Type="Credit",t.Transaction_Amount*-1,t.Transaction_Amount)) as amount,
//                                        t.JV_ID,
//                                        t.Transaction_Type,
//                                        t.Currency_ID,
//                                        t.Exchange_Rate_ID,
//                                        t.Current_Ex_Rate,t.Account_ID,a.Account_Code,a.Account_Name,
//                                        a.Currency_ID as account_currency,
//                                        t.branch_id,ifnull(t.job_id,0) as job_id,t.project_id,
//                                        if(isnull(b.branch_name),"",b.branch_name) as branch_name,
//                                        if(isnull(j.job_name),"",j.job_name) as job_name,
//                                        if(isnull(pr.project_name),"",pr.project_name) as project_name,
//                                        t.main_type
//                                    FROM 
//                                       tbl_accounting_transactions  t 
//                                       join tbl_chart_of_accounts a on a.Account_ID = t.Account_ID
//                                       left join tbl_organization_branches b on b.branch_id = t.branch_id
//                                       left join tbl_accounting_jobs j on j.job_id = t.job_id
//                                       left join tbl_projects pr on pr.project_id = t.project_id
//                                    WHERE  
//                                        t.current_year_id = "' . $year_id . '"
//                                    
//                                    and t.is_actual = "' . $flag . '" 
//                                   ' . $cond . '
//                                    group by t.Account_ID,t.main_type,t.Currency_ID,t.branch_id,ifnull(t.job_id,0),ifnull(t.project_id,0)
//                ');
//        while ($row = $this->db->assoc($query)) {
//            $row['account_currency'] = empty($row['account_currency']) ? smc_session()['local_currency'] : $row['account_currency'];
//
//            $old = $row['current_amount'];
//            $new = self::returnExchangedBetweenTwoCurrencies($row['Currency_ID'], $local_currency, $row['amount']); //exchange based on current exchange value
//
//            $balance = $old - $new;
////            if($row['Account_ID']==31){
////                   echo"<pre>";   print_r($row);
////            echo 'account: '.$row['Account_ID'].'==old: ' . $old . '==new: ' . $new . '==<br>';
////            echo 'balance: ' . $balance . '======' . '<br>';
////            }
//            $indx = $row['Account_ID'] . '_' . $row['job_id'] . '_' . $row['project_id'];
//            $indx_main = $row['job_id'] . '_' . $row['project_id'];
//            $diff_exchange_balance_accs[$row['branch_id']][$indx_main] = array('branch_name' => $row['branch_name'],
//                'branch_id' => $row['branch_id'],
//                'job_id' => $row['job_id'], 'job_name' => $row['job_name'], 'project_id' => $row['project_id'],
//                'project_name' => $row['project_name']);
//
//            if (!isset($data[$row['branch_id']][$indx])) {
//                $arr_acc[$row['branch_id']][$indx] = $balance;
//            } else {
//                $arr_acc[$row['branch_id']][$indx] += $balance;
//            }
////            echo 'total_balance: ' . $arr_acc[$row['Account_ID']];
//            if (isset($diff_exchange_balance[$row['branch_id']][$indx_main]))
//                $diff_exchange_balance[$row['branch_id']][$indx_main] += $balance;
//            else
//                $diff_exchange_balance[$row['branch_id']][$indx_main] = $balance;
//            //
//            if (abs($arr_acc[$row['branch_id']][$indx]) > 0.00001) {
////                echo'heree';
//
//                if ($arr_acc[$row['branch_id']][$indx] < 0)
//                    $transaction_type = "Debit";
//                else
//                    $transaction_type = "Credit";
//
//                $data[$row['branch_id']][$indx] = array('Account_Code' => $row['Account_Code'],
//                    'Account_ID' => $row['Account_ID'],
//                    'Account_Name' => $row['Account_Name'],
//                    'Balance' => abs($arr_acc[$row['branch_id']][$indx]),
//                    'local_amount' => abs($arr_acc[$row['branch_id']][$indx]),
//                    'Transaction_Amount' => 0,
//                    'Transaction_Type' => $transaction_type,
//                    'branch_id' => $row['branch_id'],
//                    'branch_name' => $row['branch_name'],
//                    'job_id' => $row['job_id'],
//                    'job_name' => $row['job_name'],
//                    'project_id' => $row['project_id'],
//                    'project_name' => $row['project_name'],
//                    'account_currency' => $row['account_currency'],
//                    'subtype' => 'diff'
//                );
////                echo "<pre>";print_r($data);
//            }else {
//                unset($data[$row['branch_id']][$indx]);
//            }
//        }
////        echo "<pre>";print_r($diff_exchange_balance);
////        echo $diff_exchange_balance . '==';
//        $data_final = array();
//        if (!empty($diff_exchange_balance) && !empty($data)) {
//            foreach ($diff_exchange_balance as $branch_id => $diff_exchange_balance_branches) {
//                $data_final = array_merge($data_final, array_values($data[$branch_id]));
//                if (!empty($diff_exchange_balance_branches) && !empty($data[$branch_id])) {
//                    foreach ($diff_exchange_balance_branches as $indx => $balance) {
//                        if ($balance != 0) {
//
//                            if ($balance < 0) {
//                                $transaction_type = "Credit";
//                                $diff_exchange_id = smc_session()['account_setting']['negative_ex_rate'];
//                                $diff_exchange_code = JV::getAccountCodeByID($diff_exchange_id);
//                            } else {
//                                $transaction_type = "Debit";
//                                $diff_exchange_id = smc_session()['account_setting']['positive_ex_rate'];
//                                $diff_exchange_code = JV::getAccountCodeByID($diff_exchange_id);
//                            }
//                            $temp_arr = array('Account_Code' => $diff_exchange_code,
//                                'Account_ID' => $diff_exchange_id,
//                                'Account_Name' => self::returnAccountName($diff_exchange_code),
//                                'Balance' => abs(round($balance, 3)),
//                                'local_amount' => abs(round($balance, 3)),
//                                'Transaction_Amount' => abs(round($balance, 3)),
//                                'Transaction_Type' => $transaction_type,
//                                'account_currency' => smc_session()['local_currency'],
//                                'branch_id' => $diff_exchange_balance_accs[$branch_id][$indx]['branch_id'],
//                                'branch_name' => $diff_exchange_balance_accs[$branch_id][$indx]['branch_name'],
//                                'job_id' => $diff_exchange_balance_accs[$branch_id][$indx]['job_id'],
//                                'job_name' => $diff_exchange_balance_accs[$branch_id][$indx]['job_name'],
//                                'project_id' => $diff_exchange_balance_accs[$branch_id][$indx]['project_id'],
//                                'project_name' => $diff_exchange_balance_accs[$branch_id][$indx]['project_name'],
//                            );
//                            $data[$branch_id][] = $temp_arr;
//                            $data_final[] = $temp_arr;
//                        }
//                    }
//                }
//            }
//        }
////        echo"<pre>";
////        die(print_r($data_final));
//        return [$data_final, $data];
//    }


    public function selectExchangeRateAccounts($flag = false, $date = null) {
        $local_currency = self::viewLocalCurrency();
        $data = array();
//        $diff_exchange_balance = 0.0;
        $diff_exchange_balance = array();
        $diff_exchange_balance_accs = array();
        $cond = ' ';
        if (!empty($date)) {
            $cond .= ' and t.Transaction_Date <= "' . $date . '"';
        }
        $year_id = Session::getCurrentYearID($flag);

//        $balance_current = 0.0; //balance according to current exchange rate
//        $balance_old = 0.0; //balance according to date of transaction exchange rate
        $balance = 0.0; //difference of exchange between 2 balances
        $arr_acc = array();
        $query = $this->db->query('SELECT 
            sum(if(t.Transaction_Type="Credit",t.local_amount*-1,t.local_amount)) as current_amount,
            /*sum(if(t.Transaction_Type="Credit",t.Transaction_Amount*-1,t.Transaction_Amount)) as amount,*/
            sum(if(t.Transaction_Type="Credit",-1,1) * if(t.Currency_ID=a.Currency_ID,t.Transaction_Amount, if(a.Currency_ID="' . smc_session()['second_currency']['id'] . '", (t.Transaction_Amount*t.second_currency_rate), 0))) as acc_curr_amount,
            sum(if(t.Transaction_Type="Credit",-1,1) * if(t.Currency_ID=a.Currency_ID or a.Currency_ID="' . smc_session()['second_currency']['id'] . '", 0, (t.local_amount))) as acc_curr_local_amount,
            t.JV_ID,
            t.Transaction_Type,
            t.Currency_ID,
            t.Exchange_Rate_ID,
            t.Current_Ex_Rate,t.Account_ID,a.Account_Code,a.Account_Name,
            a.Currency_ID as account_currency,
            t.branch_id,ifnull(t.job_id,0) as job_id,t.project_id,
            if(isnull(b.branch_name),"",b.branch_name) as branch_name,
            if(isnull(j.job_name),"",j.job_name) as job_name,
            if(isnull(pr.project_name),"",pr.project_name) as project_name,
            t.pos_customer_id
        FROM 
           tbl_accounting_transactions  t 
           join tbl_chart_of_accounts a on a.Account_ID = t.Account_ID
           left join tbl_organization_branches b on b.branch_id = t.branch_id
           left join tbl_accounting_jobs j on j.job_id = t.job_id
           left join tbl_projects pr on pr.project_id = t.project_id
        WHERE  
            t.current_year_id = "' . $year_id . '"
            and a.Currency_ID <> "' . smc_session()['local_currency'] . '"
            and t.is_actual = "' . $flag . '" 
           ' . $cond . '
        group by t.Account_ID,t.branch_id,ifnull(t.job_id,0),ifnull(t.project_id,0),t.pos_customer_id
                ');
        while ($row = $this->db->assoc($query)) {
            $row['account_currency'] = empty($row['account_currency']) ? smc_session()['local_currency'] : $row['account_currency'];
            if ($row['account_currency'] == smc_session()['local_currency'])
                continue;
            $old = $row['current_amount'];
//            $new = self::returnExchangedBetweenTwoCurrencies($row['Currency_ID'], $local_currency, $row['amount']); //exchange based on current exchange value

            $new = $row['acc_curr_local_amount'] + CurrencyFactory::returnExchangedValueBetweenTwoCurrencies($row['account_currency'], smc_session()['local_currency'], $row['acc_curr_amount']);

            $balance = $old - $new;
//            if($row['Account_ID']==31){
//                   echo"<pre>";   print_r($row);
//            echo 'account: '.$row['Account_ID'].'==old: ' . $old . '==new: ' . $new . '==<br>';
//            echo 'balance: ' . $balance . '======' . '<br>';
//            }
            $indx = $row['Account_ID'] . '_' . $row['job_id'] . '_' . $row['project_id'] . '_' . $row['pos_customer_id'];
            $indx_main = $row['job_id'] . '_' . $row['project_id'] . '_' . $row['pos_customer_id'];
            $diff_exchange_balance_accs[$row['branch_id']][$indx_main] = array('branch_name' => $row['branch_name'],
                'branch_id' => $row['branch_id'],
                'job_id' => $row['job_id'], 'job_name' => $row['job_name'], 'project_id' => $row['project_id'],
                'project_name' => $row['project_name']);

            if (!isset($data[$row['branch_id']][$indx])) {
                $arr_acc[$row['branch_id']][$indx] = $balance;
            } else {
                $arr_acc[$row['branch_id']][$indx] += $balance;
            }
//            echo 'total_balance: ' . $arr_acc[$row['Account_ID']];
            if (isset($diff_exchange_balance[$row['branch_id']][$indx_main]))
                $diff_exchange_balance[$row['branch_id']][$indx_main] += $balance;
            else
                $diff_exchange_balance[$row['branch_id']][$indx_main] = $balance;
            //
            if (abs($arr_acc[$row['branch_id']][$indx]) > 0.00001) {
//                echo'heree';

                if ($arr_acc[$row['branch_id']][$indx] < 0)
                    $transaction_type = "Debit";
                else
                    $transaction_type = "Credit";

                $data[$row['branch_id']][$indx] = array('Account_Code' => $row['Account_Code'],
                    'Account_ID' => $row['Account_ID'],
                    'Account_Name' => $row['Account_Name'],
                    'Balance' => abs($arr_acc[$row['branch_id']][$indx]),
                    'local_amount' => abs($arr_acc[$row['branch_id']][$indx]),
                    'Transaction_Amount' => 0,
                    'Transaction_Type' => $transaction_type,
                    'branch_id' => $row['branch_id'],
                    'branch_name' => $row['branch_name'],
                    'job_id' => $row['job_id'],
                    'job_name' => $row['job_name'],
                    'project_id' => $row['project_id'],
                    'project_name' => $row['project_name'],
                    'account_currency' => $row['account_currency'],
                    'subtype' => 'diff',
                    'pos_customer_id' => $row['pos_customer_id']
                );
//                echo "<pre>";print_r($data);
            }else {
                unset($data[$row['branch_id']][$indx]);
            }
        }
//        echo "<pre>";print_r($diff_exchange_balance);
//        echo $diff_exchange_balance . '==';
        $data_final = array();
        if (!empty($diff_exchange_balance) && !empty($data)) {
            foreach ($diff_exchange_balance as $branch_id => $diff_exchange_balance_branches) {
                if (!empty($diff_exchange_balance_branches) && !empty($data[$branch_id])) {
                    $data_final = array_merge($data_final, array_values($data[$branch_id]));
                    foreach ($diff_exchange_balance_branches as $indx => $balance) {
                        if ($balance != 0) {

                            if ($balance < 0) {
                                $transaction_type = "Credit";
                                $diff_exchange_id = smc_session()['account_setting']['negative_ex_rate'];
                                $diff_exchange_code = JV::getAccountCodeByID($diff_exchange_id);
                            } else {
                                $transaction_type = "Debit";
                                $diff_exchange_id = smc_session()['account_setting']['positive_ex_rate'];
                                $diff_exchange_code = JV::getAccountCodeByID($diff_exchange_id);
                            }
                            $temp_arr = array('Account_Code' => $diff_exchange_code,
                                'Account_ID' => $diff_exchange_id,
                                'Account_Name' => self::returnAccountName($diff_exchange_code),
                                'Balance' => abs(round($balance, 3)),
                                'local_amount' => abs(round($balance, 3)),
                                'Transaction_Amount' => abs(round($balance, 3)),
                                'Transaction_Type' => $transaction_type,
                                'account_currency' => smc_session()['local_currency'],
                                'branch_id' => $diff_exchange_balance_accs[$branch_id][$indx]['branch_id'],
                                'branch_name' => $diff_exchange_balance_accs[$branch_id][$indx]['branch_name'],
                                'job_id' => $diff_exchange_balance_accs[$branch_id][$indx]['job_id'],
                                'job_name' => $diff_exchange_balance_accs[$branch_id][$indx]['job_name'],
                                'project_id' => $diff_exchange_balance_accs[$branch_id][$indx]['project_id'],
                                'project_name' => $diff_exchange_balance_accs[$branch_id][$indx]['project_name'],
                                'is_close_account' => true
                            );
                            $data[$branch_id][] = $temp_arr;
                            $data_final[] = $temp_arr;
                        }
                    }
                }
            }
        }
//        echo"<pre>";
//        die(print_r($data_final));
        return [$data_final, $data];
    }

    private function returnAccountName($code) {
        $query = $this->db->query('SELECT Account_Name FROM tbl_chart_of_accounts WHERE Account_Code = "' . $code . '"');
        $row = $this->db->assoc($query);
        return $row['Account_Name'];
    }

    public function returnExchangedValueByExchangedID($exchanged_id, $current_value, $from_currency) {
        if ($exchanged_id == 0)
            return $current_value;
        //
        static $rate_data_arr = array();
        //must consider from and to
        $index = $exchanged_id . '_' . $from_currency;
        if (!isset($rate_data_arr[$index])) {
            $query = $this->db->query('SELECT * FROM tbl_currencies_rate WHERE Exchange_Rate_ID = "' . $exchanged_id . '"');
            $row = $this->db->assoc($query);
            if (empty($row))
                $rate_data_arr[$index] = '*0';
            else
                $rate_data_arr[$index] = ($row['from_currency'] == $from_currency ? '*' : '/') . $row['ExchRate'];
        }
        $rate_val = substr($rate_data_arr[$index], 1);
        if (floatval($rate_val) == 0)
            return 0;
        if (substr($rate_data_arr[$index], 0, 1) == '/')
            $final_value = $current_value / $rate_val;
        else
            $final_value = $current_value * $rate_val;
        return $final_value;
    }

//    private function viewBalanceByLocalCurrency($account, $local_currency, $year_id) {
//        $balance_current = 0.0; //balance according to current exchange rate
//        $balance_old = 0.0; //balance according to date of transaction exchange rate
//        $balance = 0.0; //difference of exchange between 2 balances
//        $query = $this->db->query('SELECT 
//                                        sum(t.Transaction_Amount*Current_Ex_Rate) as current_amount,
//                                        sum(t.Transaction_Amount) as amount,
//                                        t.JV_ID,
//                                        t.Transaction_Type,
//                                        t.Currency_ID,
//                                        t.Exchange_Rate_ID,
//                                        t.Current_Ex_Rate
//                                    FROM 
//                                       tbl_accounting_transactions  t join tbl_chart_of_accounts a on a.Account_ID = t.Account_ID
//                                    WHERE  
//                                        a.Account_Code = "' . $account . '"
//                                    AND 
//                                        t.current_year_id = "' . $year_id . '"
//                                    AND
//                                        t.Currency_ID <> "' . $local_currency . '"
//                                    group by t.Currency_ID,Transaction_Type
//                ');
//        while ($row = $this->db->assoc($query)) {
//            $old = $row['current_amount'];
//            $new = self::returnExchangedBetweenTwoCurrencies($row['Currency_ID'], $local_currency, $row['amount']); //exchange based on current exchange value
//
//            if ($row['Transaction_Type'] == "Debit") {
//                $balance_current += $new;
//                $balance_old += $old;
//            } else if ($row['Transaction_Type'] == "Credit") {
//                $balance_current -= $new;
//                $balance_old -= $old;
//            }
//        }
//        $balance = $balance_current - $balance_old;
////                    echo ("account ".$account." new ".$new." old ".$old." balance ".$balance);
//
//        return $balance;
//    }

    public function submitDifferenceOfExchange($data) {
        $this->db->query("START TRANSACTION");
        $flag = $data['actual'];
        $date = $data['date'];
        //
//        $check = JV::checkCurrentYear($date, $date, $flag);
        $check = CommonFunctions::check_lock_data(['type' => 'cle', 'start_date' => $date, 'is_actual' => $flag]);

        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        //
        $ref = "EXR-000";
        $desc = 'Submit difference of exchange on ' . $date;
//        for ($i = 0; $i < count($data['Records']); $i++) {
        if (!empty($data['Records'])) {
            foreach ($data['Records'] as $branch_id => $branch_item) {
                $jv = JV::returnJV($flag);
                foreach ($branch_item as $item) {
//            $item = $data['Records'][$i];
//                if (!isset($item['subtype'])) {
                    $data_1 = array(
                        "JV_ID" => $jv,
                        "Transaction_Reference" => $ref,
                        "Transaction_Account" => $item['Account_ID'],
                        "Transaction_Type" => $item['Transaction_Type'],
                        "Transaction_Amount" => $item['Transaction_Amount'],
                        "local_amount" => $item['local_amount'],
                        "Transaction_Date" => $date,
                        "Currency_ID" => $item['account_currency'],
                        "Logged_ID" => $data['Logged_ID'],
                        "Transaction_Description" => $desc,
                        "Current_Ex_Rate" => "0",
                        "main_id" => "0",
                        "main_type" => "cle",
                        "job_id" => $item['job_id'],
                        "branch_id" => $item['branch_id'],
                        "project_id" => $item['project_id'],
                        "pos_customer_id" => (!empty($item['pos_customer_id']) ? $item['pos_customer_id'] : 0)
                    );
                    //to keep jv balance in 2nd currency
                    if (!empty($item['is_close_account'])) {
                        $data_1['second_currency_rate'] = 0;
                        $data_1['keep_rate'] = 1;
                    }
                    JV::addTransaction($data_1, $flag);
//                } else {
//                    $data_1 = array(
//                        "JV_ID" => $jv,
//                        "Transaction_Reference" => $ref,
//                        "Transaction_Account" => $item['Account_ID'],
//                        "Transaction_Type" => $item['Transaction_Type'],
//                        "Transaction_Amount" => 0,
//                        "Transaction_Date" => $date,
//                        "Currency_ID" => smc_session()['local_currency'],
//                        "Logged_ID" => $data['Logged_ID'],
//                        "Transaction_Description" => $desc,
//                        "Current_Ex_Rate" => "0",
//                        "local_amount" => $item['Balance'],
//                        "main_id" => "0",
//                        "main_type" => "cle",
//                        "job_id"=>$item['job_id'],
//                        "branch_id"=>$item['branch_id'],
//                        "project_id"=>$item['project_id']
//                    );
//                    JV::addTransaction($data_1, $flag);
//                }
                }
            }
        }

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

    public function returnLocalCurrency() {
//        $local_query = $this->db->query('SELECT Local_Currency FROM tbl_organization');
//        $local_row = $this->db->assoc($local_query);
//        $local_currency = $local_row['Local_Currency'];
//        return $local_currency;
        return smc_session()['local_currency'];
    }

    public function getDefaultCurrencyById($id) {
        $query = $this->db->query('SELECT Currency_ID from tbl_default_currency WHERE id="' . $id . '"');
        $row = $this->db->assoc($query);

        return $row['Currency_ID'];
    }

    public function returnBalanceByCurrExchange($balances, $to_currency) {
        $balance = 0.0;
        $balances_arr = explode(";", $balances);
        for ($i = 0; $i < count($balances_arr); $i++) {
            $temp_arr = $balances_arr[$i];
            $curr_bal = explode("#", $temp_arr);
//            echo $curr_bal[0] . '===' . $to_currency . '=====' . $curr_bal[1] . '<br>';
            if ($curr_bal[0] == $to_currency)
                $balance += $curr_bal[1];
            else
                $balance += Currency::getbalancebyRate($curr_bal[0], $to_currency, $curr_bal[1]);
        }
        return $balance;
    }

    public function getbalancebyRate($from_currency, $to_currency, $amount) {
        if ($from_currency == $to_currency)
            return $amount;
        $data = self::returnExchangeRate($from_currency, $to_currency);
        $temp_amount = null;
        if (count($data) != 0) {
            if ($data[1] == '*')
                $temp_amount = $amount * $data[0];
            else
                $temp_amount = $amount / $data[0];
        }
        return $temp_amount;
//        $db = DBConnectorFactory::loadDriver();
//        $ex_query = $db->query('SELECT  ExchRate, from_currency, to_currency FROM  tbl_currencies_rate 
//                                    WHERE (
//                                          (from_currency = "' . $from_currency . '" AND  to_currency = "' . $to_currency . '" )
//                                            OR  ( to_currency = "' . $from_currency . '" AND  from_currency = "' . $to_currency . '" )
//                                        )
//                                    ORDER BY  Exchange_Rate_ID DESC LIMIT 1 ');
//        $row_query = $db->assoc($ex_query);
//        if ($row_query['ExchRate'] != 1) {
//            if ($row_query['from_currency'] == $from_currency)
//                $temp_amount = $amount * $row_query['ExchRate'];
//            else
//                $temp_amount = $amount / $row_query['ExchRate'];
//        }
//        else {
//            $temp_amount = $amount;
//        }
//        return $temp_amount;
    }

    public function getLastExRate($currency) {
        $local_currency = smc_session()['local_currency'];
        if ($currency == $local_currency)
            return 1;
        $data = self::returnExchangeRate($currency, $local_currency);
        if (count($data) != 0) {
            if ($data[1] == '*')
                return $data[0];
            return (1 / $data[0]);
        }
        return 0;
//        $query_rate = $this->db->query('SELECT
//                                ExchRate
//                            FROM
//                                tbl_currencies_rate
//                            WHERE
//                                from_currency = "' . $currency . '"
//                            AND
//                                to_currency = "' . $local_currency . '"
//                            ORDER BY 
//                                Exchange_Rate_ID
//                            DESC 
//                            LIMIT 1');
//        $rate = $this->db->assoc($query_rate)['ExchRate'];
//        return $rate;
    }

    public function getSecondCurrencyLastExRate($currency) {
        $second_currency = smc_session()['second_currency']['id'];
        $rate_arr = $this->returnExchangeRate($currency, $second_currency);
//        echo"<pre>";print_r($rate_arr);
        if ($rate_arr[1] == '*')
            return $rate_arr[0];
        else
            return 1 / $rate_arr[0];
    }

}
