<?php

class AccountingDashboard {

    protected $info = array();
    private $db;

    /*     * viewDebitCreditperCustomer(
     * The constructor will create an instant of the chosen database adapter (MySQL, MySQLi, etc)
     * and saves it in a private variable $db as an object to be used throughout the class.
     *
     * @author  Taha Sayyad <info@trustotech.com>
     * @uses DBConnectorFactory::loadDriver() to load the database adapter
     */

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

    /**
     * The first thing to do is to set organization data using this method.<br .>
     * Before setting the organization data, the method will remove all unnecessary data from the array.
     * Next it will trim every single value.
     *
     * @author  Taha Sayyad <info@trustotech.com>
     * @param array $data Array of organization data
     */
    public function setdata(array $data) {
        $this->info = $data;
        return $this;
    }

//    public function viewTotalIncomeInvoices() {
//        $condition = '';
//        $total_netincomes = 0;
//        $data = array();
//        $query = $this->db->query('SELECT 
//                                            c.Currency_Symbol,    
//                                            i.Invoice_Net_Total,
//                                            i.Invoice_Currency
//                                        FROM 
//                                            tbl_invoices i
//                                       LEFT JOIN 
//								            tbl_common_currency c
//							           ON 
//								       c.Currency_ID = i.Invoice_Currency
//                                       WHERE 
//                                             1= 1 ' . $condition
//                                 );
//        while ($row = $this->db->assoc($query)) {
//            $netincomes = CurrencyFactory::returnPriceInDefaultCurrencyValue($row['Invoice_Currency'], $row['Invoice_Net_Total']);
//
//            $total_netincomes += $netincomes;
//            // $total_netincomes .= ' ' . $row['Currency_Symbol'] ;
//        }
//
//        $data[] = $total_netincomes;
//        return $data;
//    }
    //    public function viewTotalExpenses() {
    //        $total_expenses = 0;
    //        $condition = '';
    //        $data = array();
    //        $query = $this->db->query('SELECT 
    //                                            e.Expense_Total_Price,
    //                                            e.Currency_ID
    //                                        FROM 
    //                                            tbl_accounting_expenses e
    //                                        WHERE 
    //                                             1= 1 ' .$condition 
    //                                 );
    //        while ($row = $this->db->assoc($query)) {
    //            $expense        = CurrencyFactory::returnPriceInDefaultCurrencyValue($row['Currency_ID'],$row['Expense_Total_Price']);
    //            $total_expenses += $expense;
    //        }
    //        $data[] = $total_expenses;
    //        return $data;
    //    }
    public function viewSalesInvoicesByDate($date = null, $year = null, $month = null, $week = false) {
        $sum = 0.0;
        $condition = '';
        $default_currency = CurrencyFactory::getDefaultCurrency();
        if (!is_null($date) && is_null($year) && is_null($month) && !$week) {

            if (empty(smc_session()['default_setting']['dashboard_daily_cards_type'])) {
                $condition .= ' AND Invoice_Date = "' . $date . '"';
            } elseif (smc_session()['default_setting']['dashboard_daily_cards_type'] == 1) {
                $start_date = $date . ' ' . smc_session()['default_setting']['default_start_time'];
                $end_date = date('Y-m-d H:i:s', strtotime($start_date . ' +1 day -1 second'));
                $condition.=' and date_created between "' . $start_date . '" and "' . $end_date . '" ';
            } else if (smc_session()['default_setting']['dashboard_daily_cards_type'] == 2) {
                $branch_id = !empty($this->info['branch_id']) ? $this->info['branch_id'] : '';
                $start_date = Invoicepos::get_last_close_end_day($branch_id);
                $condition.=' and date_created > "' . $start_date . '" ';
            }
        }
        if ($week)
            $condition .= ' AND WEEKOFYEAR(Invoice_Date)=WEEKOFYEAR("' . $date . '")';
        if (!is_null($month))
            $condition .= ' AND MONTH(Invoice_Date) = "' . $month . '"';
        if (!is_null($year))
            $condition .= ' AND YEAR(Invoice_Date) = "' . $year . '"';
        if (!empty($date))
            $condition .= ' AND YEAR(Invoice_Date) = YEAR("' . $date . '")';
        if (!empty($this->info['branch_id']))
            $condition .= ' AND branch_id = "' . $this->info['branch_id'] . '"';
        $query = $this->db->query('SELECT 
                                            (Invoice_Net_Total - Return_Invoice_Total) AS Net_Amount,
                                            Invoice_Currency,second_currency_rate
                                        FROM 
                                            tbl_invoices 
                                        WHERE   
                                             current_year_id = "' . smc_session()['current_year_id'] . '" and  is_committed = 1 ' . $condition
        );
        while ($row = $this->db->assoc($query)) {
            if ($default_currency == smc_session()['default_setting']['second_currency'])
                $sum+=($row['Net_Amount'] * $row['second_currency_rate']);
            else
                $sum += CurrencyFactory::returnPriceInDefaultCurrencyValue($row['Invoice_Currency'], $row['Net_Amount']);
        }
        return $sum;
    }

    public function viewPurchaseInvoicesByDate($date = null, $year = null, $month = null, $week = false) {
        $sum = 0.0;
        $condition = '';
        $default_currency = CurrencyFactory::getDefaultCurrency();
        if (!is_null($date) && !$week && is_null($year) && is_null($month)) {
            if (empty(smc_session()['default_setting']['dashboard_daily_cards_type'])) {
                $condition .= ' AND Purchase_Date = "' . $date . '"';
            } elseif (smc_session()['default_setting']['dashboard_daily_cards_type'] == 1) {
                $start_date = $date . ' ' . smc_session()['default_setting']['default_start_time'];
                $end_date = date('Y-m-d H:i:s', strtotime($start_date . ' +1 day -1 second'));
                $condition.=' and date_created between "' . $start_date . '" and "' . $end_date . '" ';
            } else if (smc_session()['default_setting']['dashboard_daily_cards_type'] == 2) {
                $branch_id = !empty($this->info['branch_id']) ? $this->info['branch_id'] : '';
                $start_date = Invoicepos::get_last_close_end_day($branch_id);
                $condition.=' and date_created > "' . $start_date . '" ';
            }
        }

        if ($week)
            $condition .= ' AND WEEKOFYEAR(Purchase_Date)=WEEKOFYEAR("' . $date . '")';
        if (!is_null($month))
            $condition .= ' AND MONTH(Purchase_Date) = "' . $month . '"';
        if (!is_null($year))
            $condition .= ' AND YEAR(Purchase_Date) = "' . $year . '"';
        if (!empty($this->info['branch_id']))
            $condition .= ' AND branch_id = "' . $this->info['branch_id'] . '"';
        if (!empty($date))
            $condition .= ' AND YEAR(Purchase_Date) = YEAR("' . $date . '")';
        $query = $this->db->query('SELECT 
                                            (Purchase_Net_Total - Return_Purchase_Total) AS Net_Amount,
					Currency_ID,second_currency_rate
                                        FROM 
                                            tbl_inventory_purchases p
                                        WHERE 
                                             current_year_id = "' . smc_session()['current_year_id'] . '" ' . $condition
        );
        while ($row = $this->db->assoc($query)) {
            if ($default_currency == smc_session()['default_setting']['second_currency'])
                $sum+=($row['Net_Amount'] * $row['second_currency_rate']);
            else
                $sum += CurrencyFactory::returnPriceInDefaultCurrencyValue($row['Currency_ID'], $row['Net_Amount']);
        }
        return $sum;
    }

//    public function ListPurchaseByDate($date = null, $week = null, $month = null, $year = null) {
//        $data = array();
//        $condition = '';
//        if (!is_null($date))
//            $condition .= ' AND e.Purchase_Date = "' . $date . '"';
//        if (!is_null($week))
//            $condition .= ' AND WEEKOFYEAR(e.Purchase_Date) = "' . $week . '"';
//        if (!is_null($month))
//            $condition .= ' AND MONTH(e.Purchase_Date) = "' . $month . '"';
//        if (!is_null($year))
//            $condition .= ' AND YEAR(e.Purchase_Date) = "' . $year . '"';
//        $query = $this->db->query('SELECT 
//                                            e.*,
//                                            s.*,
//                                            cu.*
//                                        FROM 
//                                            tbl_inventory_purchases e
//                                        LEFT JOIN
//                                            tbl_stock_suppliers s
//                                        ON 
//                                            s.Supplier_ID = e.Supplier_ID
//                                        LEFT JOIN
//                                            tbl_common_currency cu
//                                        ON
//                                            cu.Currency_ID =  e.Currency_ID
//                                        WHERE 
//                                            1=1 ' . $condition
//                                 );
//        while ($row = $this->db->assoc($query)) {
//            $data[] = $row;
//        }
//        return $data;
//    }
//    public function ListInvoiceByDate($date = null, $week = null, $month = null, $year = null) {
//        $data = array();
//        $condition = '';
//        if (!is_null($date))
//            $condition .= ' AND e.Invoice_Date = "' . $date . '"';
//        if (!is_null($week))
//            $condition .= ' AND WEEKOFYEAR(e.Invoice_Date) = "' . $week . '"';
//        if (!is_null($month))
//            $condition .= ' AND MONTH(e.Invoice_Date) = "' . $month . '"';
//        if (!is_null($year))
//            $condition .= ' AND YEAR(e.Invoice_Date) = "' . $year . '"';
//        $query = $this->db->query('SELECT 
//                                            e.*,
//                                            s.*,
//                                            cu.*
//                                        FROM 
//                                            tbl_invoices e
//                                        LEFT JOIN
//                                            tbl_customers s
//                                        ON 
//                                            s.Customer_ID = e.Customer_ID
//                                        LEFT JOIN
//                                            tbl_common_currency cu
//                                        ON
//                                            cu.Currency_ID =  e.Invoice_Currency
//                                        WHERE 
//                                            1=1 ' . $condition
//                                 );
//        while ($row = $this->db->assoc($query)) {
//            if ($row['Customer_Name'] == "") {
//                $row['Customer_Name'] = "Road Customer";
//                $row['Customer_ID'] = 0;
//            }
//            $data[] = $row;
//        }
//        return $data;
//    }
//    public function viewPurchaseByDate($date) {
//
//        $data = '';
//        $condition = '';
//        switch ($date) {
//            case 'Day' :
//                $condition .= 'AND e.Purchase_Date = CURRENT_DATE';
//                break;
//            case 'Week':
//                $condition .= 'AND WEEKOFYEAR(e.Purchase_Date)=WEEKOFYEAR(CURRENT_DATE)';
//                break;
//            case 'Month':
//                $condition .= 'AND e.Purchase_Date > DATE_SUB(NOW(), INTERVAL 1 MONTH)';
//                break;
//        }
//        $query = $this->db->query('SELECT 
//                                            e.*,
//                                            s.Supplier_Name,
//                                            s.Supplier_Company,
//                                            cu.*
//                                        FROM 
//                                            tbl_inventory_purchases e
//                                        LEFT JOIN
//                                            tbl_stock_suppliers s
//                                        ON 
//                                            s.Supplier_ID = e.Supplier_ID
//                                        LEFT JOIN
//                                            tbl_common_currency cu
//                                        ON
//                                            cu.Currency_ID =  e.Currency_ID
//                                        WHERE 
//                                             1= 1 ' . $condition
//                                 );
//        while ($row = $this->db->assoc($query)) {
//
//            $data[] = $row;
//        }
//        return $data;
//    }
//    public function viewSalesByDate($date) {
//
//        $data = '';
//        $condition = '';
//        switch ($date) {
//            case 'Day' :
//                $condition .= 'AND e.Invoice_Date = CURRENT_DATE';
//                break;
//            case 'Week':
//                $condition .= 'AND WEEKOFYEAR(e.Invoice_Date)=WEEKOFYEAR(CURRENT_DATE)';
//                break;
//            case 'Month':
//                $condition .= 'AND e.Invoice_Date > DATE_SUB(NOW(), INTERVAL 1 MONTH)';
//                break;
//            case 'Year':
//                $condition .= 'AND e.Invoice_Date > DATE_SUB(NOW(), INTERVAL 1 Year)';
//                break;
//        }
//        $query = $this->db->query('SELECT 
//                                            e.*,
//                                            s.Customer_Name,
//                                            s.Customer_Company,
//                                            cu.*
//                                        FROM 
//                                            tbl_invoices e
//                                        LEFT JOIN
//                                            tbl_customers s
//                                        ON 
//                                            s.Customer_ID = e.Customer_ID
//                                        LEFT JOIN
//                                            tbl_common_currency cu
//                                        ON
//                                            cu.Currency_ID =  e.Invoice_Currency
//                                        WHERE 
//                                             1= 1 ' . $condition
//                                 );
//        while ($row = $this->db->assoc($query)) {
//
//            $data[] = $row;
//        }
//        return $data;
//    }

    public function viewExpensesByDate($date) {
        $total_expenses = 0;
        $data = '';
        $condition = '';
        switch ($date) {
            case 'Day' :
                $condition .= 'AND e.Expense_Date = CURRENT_DATE';
                break;
            case 'Week':
                $condition .= 'AND WEEKOFYEAR(e.Expense_Date)=WEEKOFYEAR(CURRENT_DATE)';
                break;
            case 'Month':
                $condition .= 'AND e.Expense_Date > DATE_SUB(NOW(), INTERVAL 1 MONTH)';
                break;
        }
        $query = $this->db->query('SELECT 
                                            e.Expense_Total_Price,
                                            e.Currency_ID
                                        FROM 
                                            tbl_accounting_expenses e
                                        WHERE YEAR(e.Expense_Date) = YEAR("' . $date . '") AND
                                             e.current_year_id = "' . smc_session()['current_year_id'] . '" ' . $condition
        );
        while ($row = $this->db->assoc($query)) {
            $expense = CurrencyFactory::returnPriceInDefaultCurrencyValue($row['Currency_ID'], $row['Expense_Total_Price']);
            $total_expenses += $expense;
        }
        return $total_expenses;
    }

    public function viewTopSaledOrPurchasedProducts($IsSales, $limit, $order) {
        $data = array();
        $cond = '';
        if (!empty($this->info['branch_id']))
            $cond .= ' and i.branch_id = "' . $this->info['branch_id'] . '"';
        $query = $this->db->query($IsSales ? '  
                                            SELECT COUNT(*) AS count,  
                                                    d.Product_ID, 
                                                    p.Product_Name, 
                                                SUM(d.Product_Total_Price*i.current_rate) AS SUM,
                                                SUM(d.Product_Quantity) AS QTY
                                            FROM 
                                                    tbl_invoice_products_details d
                                            INNER JOIN tbl_invoices i on i.Invoice_ID_Auto = d.Invoice_ID_Auto
                                            INNER JOIN tbl_stock_products p ON d.Product_ID = p.Product_ID
                                where i.current_year_id = "' . smc_session()['current_year_id'] . '" ' . $cond . '
                                            GROUP BY 
                                                    d.Product_ID, 
                                                    p.Product_Name
                                                    ORDER BY ' . $order . ' DESC
                                                LIMIT ' . $limit . '
                                            ' : 'SELECT COUNT(*) AS count,  
                                                    d.Product_ID, 
                                                    p.Product_Name, 
                                                SUM(d.Product_Total_Price*i.current_rate) AS SUM,
                                                SUM(d.Item_Quantity) AS QTY
                                            FROM 
                                                    tbl_inventory_purchase_items d
                                                    INNER JOIN tbl_inventory_purchases i on i.Purchase_ID_Auto = d.Purchase_ID_Auto
                                            INNER JOIN 
                                                    tbl_stock_products p
                                            ON 
                                                    d.Product_ID = p.Product_ID
     where  i.current_year_id = "' . smc_session()['current_year_id'] . '" ' . $cond . '
                                            GROUP BY 
                                                    d.Product_ID, 
                                                    p.Product_Name
                                                    ORDER BY ' . $order . ' DESC
                                        LIMIT  ' . $limit . '');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function PieChart($is_invoice, $limit, $order) {
        $total = 0;
        $data = self::viewTopSaledOrPurchasedProducts($is_invoice, $limit, $order);
        for ($i = 0; $i < count($data); $i++)
            $total += $data[$i]['count'];
        $final_names = array();
        $final_perencts = array();
        foreach ($data as $product) {
            $percent = ($product['count'] / $total) * 100;
            array_push($final_names, $product['Product_Name']);
            array_push($final_perencts, round($percent, 2));
        }
        $final_result = array_merge($final_names, $final_perencts);
        return $final_result;
    }

    public function viewLeastSaledOrPurchasedProducts($table, $limit) {
        $data = '';
        $query = $this->db->query('SELECT 
                                            Product_ID,
                                            Count(Product_ID) as c_p 
                                        FROM 
                                            ' . $table . '
                                        GROUP BY 
                                            Product_ID 
                                        ORDER BY 
                                            c_p
                                        LIMIT ' . $limit . '
                        ');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function viewUnpaidInvoicesByNeededOrder($order_by, $limit, $current_date) {
        $data = array();
        $cond = '';
        if (!empty($this->info['branch_id']))
            $cond = ' and i.branch_id ="' . $this->info['branch_id'] . '"';
        $query = $this->db->query('SELECT
                                i.Customer_ID AS Customer_ID,
                                c.Customer_Name AS Customer_Name,
                                i.Invoice_ID AS Invoice_ID,
                                i.Invoice_Net_Total,
                                i.Invoice_Paid_Amount,
								i.Invoice_Net_Total-i.Invoice_Paid_Amount AS Upaid_Ammount,
                                i.Invoice_Currency,
                                cy.Currency_Symbol,
                                i.Invoice_Date AS Invoice_Date,
                                i.Invoice_Due_Date AS Invoice_Due_Date
							FROM 
								tbl_invoices i
							LEFT JOIN 
								tbl_customers c
							ON 
								c.Customer_ID = i.Customer_ID
							LEFT JOIN 
								tbl_common_currency cy
							ON 
								cy.Currency_ID = i.Invoice_Currency
							WHERE 
								i.Invoice_Status != 2 
                            AND 
                                i.Invoice_Due_Date <  "' . $current_date . '" 
                                    and i.current_year_id = "' . smc_session()['current_year_id'] . '"
                                ' . $cond . '
                            ORDER BY 
                                ' . $order_by . '
                            LIMIT 
                                ' . $limit . '
                ');
        while ($row = $this->db->assoc($query))
            $data[] = $row;
        return $data;
    }

    public function returnBalance($type, $code_id = null) {
        $balance = 0.00;
        $final_bal = 0.00;
        $data_d = self::retrieveDebits($type, $code_id);
        $data_c = self::retrieveCredits($type, $code_id);


        $data = array_merge($data_d, $data_c);
        $count = count($data);
        $trial = array();
        $final_trial = array();
        for ($i = 0; $i < $count; $i++) {
            $debit = 0.0;
            $credit = 0.0;

            if (array_key_exists($i, $data)) {
                $accountName = $data[$i]['Account_Name'];
                $accountCode = $data[$i]['Account_Code'];
                if (array_key_exists('Debit', $data[$i]))
                    $balance += CurrencyFactory::returnPriceInDefaultCurrencyValue($data[$i]['Currency_ID'], $data[$i]['Debit']);
                else
                    $balance -= CurrencyFactory::returnPriceInDefaultCurrencyValue($data[$i]['Currency_ID'], $data[$i]['Credit']);
                for ($j = $i + 1; $j < $count; $j++)
                    if (array_key_exists($j, $data))
                        if ($data[$i]['Account_ID'] == $data[$j]['Account_ID']) {

                            if (array_key_exists('Debit', $data[$j]))
                                $balance += CurrencyFactory::returnPriceInDefaultCurrencyValue($data[$j]['Currency_ID'], $data[$j]['Debit']);
                            else
                                $balance -= CurrencyFactory::returnPriceInDefaultCurrencyValue($data[$j]['Currency_ID'], $data[$j]['Credit']);

                            unset($data[$j]);
                        }
                //                if($balance<0) {
                $trial = array(
                    'Code' => $accountCode,
                    'Name' => ucwords($accountName),
                    'Balance' => $balance
                );
                array_push($final_trial, $trial);
                $trial = array();
                $final_bal += $balance;
                $balance = 0.00;
                //                }
                unset($data[$i]);
                $accountName = '';
                $accountCode = '';
            }
        }
        array_push($final_trial, $final_bal);
        return $final_trial;
    }

    private function retrieveDebits($type, $code_id = null) {
        $data = array();
        switch ($type) {
            case 'Customer' :
                $trans_type = 'Debit';
                //                $code       = 4111;
                break;
            case 'Supplier':
                $trans_type = 'Credit';
                //                $code       = 4011;
                break;
        }
        $condition = ' ';
        if (!is_null($code_id))
            $condition .= 'AND d.Account_ID = "' . $code_id . '"';
        $query = $this->db->query('SELECT 
                                a.Account_Name,
                                d.Account_ID,
                                a.Account_Code,
                                d.Currency_ID,
                                d.Transaction_Amount AS Debit 
                            FROM 
                                tbl_accounting_transactions d 
                            LEFT JOIN
                                tbl_chart_of_accounts a
                            ON
                                a.Account_ID =  d.Account_ID
                            WHERE 
                                d.Transaction_Type = "' . $trans_type . '"
                            AND 
                                d.Transaction_Date <= CURRENT_DATE
                            and  d.current_year_id = "' . smc_session()['current_year_id'] . '"

                            ' . $condition);
        while ($row = $this->db->assoc($query))
            $data[] = $row;
        return $data;
    }

    private function retrieveCredits($type, $code_id = null) {
        $data = array();
        switch ($type) {
            case 'Supplier' :
                $trans_type = 'Debit';
                //                $code       = 4011;
                break;
            case 'Customer':
                $trans_type = 'Credit';
                //                $code       = 4111;
                break;
        }
        $condition = ' ';
        if (!is_null($code_id))
            $condition .= 'AND c.Account_ID = "' . $code_id . '"';
        $query = $this->db->query('SELECT 
                                a.Account_Name,
                                a.Account_Code,
                                c.Account_ID,
                                c.Currency_ID,
                                c.Transaction_Amount AS Credit
                            FROM 
                                tbl_accounting_transactions c
                            LEFT JOIN
                                tbl_chart_of_accounts a
                            ON
                                a.Account_ID =  c.Account_ID
                            WHERE 
                                c.Transaction_Type = "' . $trans_type . '"
                            AND 
                                c.Transaction_Date <= CURRENT_DATE
                            and c.current_year_id = "' . smc_session()['current_year_id'] . '"

                            ' . $condition);
        while ($row = $this->db->assoc($query))
            $data[] = $row;
        return $data;
    }

    //	private function retrieveDebits($type,$code_id=null) {
    //        $data = array();
    //		  $condition = ' ';
    //        switch($type) {
    //            case 'Customer' :
    //                $trans_type = 'Debit';
    //                $code       = 4111;
    //                break;
    //            case 'Supplier':
    //                $trans_type = 'Credit';
    //                $code       = 4011;
    //				  $condition .= ' OR LEFT(d.Transaction_Account,4) = 4031 OR LEFT(d.Transaction_Account,5) = 46190';
    //                break;
    //        }
    //        
    //        if(!is_null($code_id))
    //            $condition .= 'AND d.Transaction_Account = "'.$code_id.'"';
    //        $query = $this->db->query('SELECT 
    //                                a.Account_Name,
    //                                d.Transaction_Account,
    //                                d.Currency_ID,
    //                                d.Transaction_Amount AS Debit 
    //                            FROM 
    //                                tbl_accounting_transactions d 
    //                            LEFT JOIN
    //                                tbl_chart_of_accounts a
    //                            ON
    //                                a.Account_Code =  d.Transaction_Account
    //                            WHERE 
    //                                d.Transaction_Type = "'.$trans_type.'"
    //                            AND 
    //                                d.Transaction_Date <= CURRENT_DATE
    //                            AND 
    //                                LEFT(d.Transaction_Account,4) = '.$code.'
    //                            '.$condition);
    //        while ($row = $this->db->assoc($query))
    //            $data[]=$row;
    //        return $data;
    //    }
    //    private function retrieveCredits($type,$code_id=null) {
    //        $data = array();
    //        switch($type) {
    //            case 'Supplier' :
    //                $trans_type = 'Debit';
    //                $code       = 4011;
    //				  $condition .= ' OR LEFT(d.Transaction_Account,4) = 4031 OR LEFT(d.Transaction_Account,5) = 46190';
    //                break;
    //            case 'Customer':
    //                $trans_type = 'Credit';
    //                $code       = 4111;
    //                break;
    //        }
    //        $condition = ' ';
    //        if(!is_null($code_id))
    //            $condition .= 'AND c.Transaction_Account = "'.$code_id.'"';
    //        $query = $this->db->query('SELECT 
    //                                a.Account_Name,
    //                                c.Transaction_Account,
    //                                c.Currency_ID,
    //                                c.Transaction_Amount AS Credit
    //                            FROM 
    //                                tbl_accounting_transactions c
    //                            LEFT JOIN
    //                                tbl_chart_of_accounts a
    //                            ON
    //                                a.Account_Code =  c.Transaction_Account
    //                            WHERE 
    //                                c.Transaction_Type = "'.$trans_type.'"
    //                            AND 
    //                                LEFT(c.Transaction_Account,4) = '.$code.'
    //                            AND 
    //                                c.Transaction_Date <= CURRENT_DATE
    //                            '.$condition);
    //        while ($row = $this->db->assoc($query))
    //            $data[]=$row;
    //        return $data;
    //    } 

    public function viewDebitCreditperCustomer($Code, $is_vendor = false) {
        $data = array();
        if ($is_vendor) {
            $query = $this->db->query('SELECT 
                                        s.*,
                                        a.Account_Code
                                    FROM tbl_chart_of_accounts a
                                    LEFT JOIN tbl_stock_suppliers s
                                    ON s.Supplier_ID = a.main_id
                                    and a.main_type="supp" and a.sub_type="main"
                                    WHERE a.Account_ID ="' . $Code . '"

                                        ');
        } else {
            $query = $this->db->query('SELECT 
                                        s.*,
                                        a.Account_Code
                                    FROM tbl_chart_of_accounts a
                                    LEFT JOIN tbl_customers s
                                    ON s.Customer_ID = a.main_id
                                    and a.main_type="cust" and a.sub_type = "main"
                                    WHERE a.Account_ID ="' . $Code . '"');
        }

        $data = $this->db->assoc($query);
        return $data;
    }

    public function viewCustomersSuppliersBalance($is_supp) {
        //1->supp
        //2->customer
        $data = array();
        $cond = '';
        $main_type = $is_supp ? 'supp' : 'cust';
        $year_id = smc_session()['current_year_id'];

        //final balance for all suppliers or all customers
        $total_balance = 0.0;
        if (!empty($this->info['branch_id']))
            $cond = ' and t.branch_id = "' . $this->info['branch_id'] . '"';
        $query_account = $this->db->query( 'SELECT 
                sum(if(Transaction_Type="Debit",local_amount,(local_amount*-1))) as balance,
                a.Account_Code,a.Account_ID,a.Account_Name FROM tbl_chart_of_accounts a
                join tbl_accounting_transactions t on t.Account_ID = a.Account_ID
                WHERE t.current_year_id= "' . $year_id . '" and a.main_type = "' . $main_type . '" and a.sub_type = "main" ' . $cond . ' group by a.Account_ID order by balance desc ');
         while ($account_row = $this->db->assoc($query_account)) {

            if ($account_row['balance'] > 0 || ($is_supp && $account_row['balance'] < 0)) {
                $total_balance +=  ($account_row['balance']);
                //for every balance greater than zero add it to the final array
                $data[] = array('Name' => $account_row['Account_Name'],
                    'Code' => $account_row['Account_Code'],
                    'ID' => $account_row['Account_ID'],
                    'Balance' => abs($account_row['balance'])
                );
            }
        }
        //        }
        //final balance for all debetors or creditors
        $data['Total_Balance'] = $total_balance;

        return $data;
    }

    //   public function viewCustomersSuppliersBalance($is_supp) {
    //        //1->supp
    //        //2->customer
    //        $data = array();
    //        $main_type = $is_supp ? 'supp' : 'cust';
    //        //final balance for all suppliers or all customers
    //        $total_balance = 0.0;
    //        //if supp then select all active suppliers 
    //        if ($is_supp==1)
    //            $query = $this->db->query('SELECT Supplier_ID AS ID,Supplier_Name AS Name FROM tbl_stock_suppliers WHERE Status_ID = 1');
    //        //if customer then select all active customers
    //        else
    //            $query = $this->db->query('SELECT Customer_ID AS ID,Customer_Name AS Name FROM tbl_customers WHERE Status_ID = 1');
    //        while ($row = $this->db->assoc($query)) {
    //            //for each customer or supplier select account codes
    //            $name = $row['Name'];
    //            $query_account = $this->db->query('SELECT a.Account_Code,a.Account_ID FROM tbl_chart_of_accounts a
    //                WHERE a.main_id = "'.$row['ID'].'" AND a.main_type = "'.$main_type.'" and a.sub_type = "main"');
    //            while ($account_row = $this->db->assoc($query_account)) {
    //                //for each account code select all transactions related to this account
    //                $balance = 0.0;
    //                $trans_query = $this->db->query('SELECT Transaction_Type,Transaction_Amount,Current_Ex_Rate,Currency_ID FROM tbl_accounting_transactions WHERE Account_ID = "'.$account_row['Account_ID'].'"');
    //                while ($row_trans = $this->db->assoc($trans_query)) {
    //                    //for each account if customer:
    //                    // if credit then subtract it from customer's balance
    //                    // if debit then add it to customer's balance
    //                    //for each account if supp:
    //                    // if debit then subtract it from supp's balance
    //                    // if credit then add it to supp's balance
    ////                    if ($is_supp==1) {
    ////                        if ($row_trans['Transaction_Type'] == 'Credit')
    ////                            $balance += CurrencyFactory::returnPriceInDefaultCurrencyValue($row_trans['Currency_ID'],$row_trans['Transaction_Amount']);
    ////                        else 
    ////                            $balance -= CurrencyFactory::returnPriceInDefaultCurrencyValue($row_trans['Currency_ID'],$row_trans['Transaction_Amount']);
    //////                   echo $balance.'=';
    ////                        }
    ////                    else {
    ////                        if ($row_trans['Transaction_Type'] == 'Credit')
    ////                            $balance -= CurrencyFactory::returnPriceInDefaultCurrencyValue($row_trans['Currency_ID'],$row_trans['Transaction_Amount']);
    ////                        else 
    ////                            $balance += CurrencyFactory::returnPriceInDefaultCurrencyValue($row_trans['Currency_ID'],$row_trans['Transaction_Amount']);
    ////                    }
    //                    if ($is_supp==1) {
    //                        if ($row_trans['Transaction_Type'] == 'Credit')
    //                            $balance += $row_trans['Transaction_Amount']*$row_trans['Current_Ex_Rate'];
    //                        else 
    //                            $balance -= $row_trans['Transaction_Amount']*$row_trans['Current_Ex_Rate'];
    ////                   echo $balance.'=';
    //                        }
    //                    else {
    //                        if ($row_trans['Transaction_Type'] == 'Credit')
    //                            $balance -= $row_trans['Transaction_Amount']*$row_trans['Current_Ex_Rate'];
    //                        else 
    //                            $balance += $row_trans['Transaction_Amount']*$row_trans['Current_Ex_Rate'];
    //                    }
    //                }
    //                if ($balance > 0) {
    //                    $total_balance += $balance;
    //                    //for every balance greater than zero add it to the final array
    //                    $data[] = array( 'Name'    => $name,
    //                                     'Code'    => $account_row['Account_Code'],
    //                                     'ID'    => $account_row['Account_ID'],
    //                                     'Balance' => $balance
    //                                );
    //                }
    //            }
    //        }
    //        //final balance for all debetors or creditors
    //        $data['Total_Balance'] = $total_balance; 
    ////        echo"<pre>";
    ////        die(print_r($data));
    //        
    //        return $data;
    //            
    //    }
    public function PieChartInventory($year, $month, $Is_Actual = null) {
        $data = array();
        $dataQty = array();
        $dataName = array();
        $cond = is_null($Is_Actual) ? ' and i.is_actual = 0' : ' ';
        $query = $this->db->query('SELECT 
                                        d.Product_ID AS ID, 
                                        p.Product_Name AS Name, 
                                        SUM(d.Product_Quantity) AS QTY
                                    FROM 
                                        tbl_invoice_products_details d
                                    LEFT JOIN
                                        tbl_invoices i
                                    ON
                                        i.Invoice_ID = d.Invoice_ID
                                    LEFT JOIN
                                        tbl_stock_products p
                                    ON 
                                        d.Product_ID = p.Product_ID
                                    WHERE
                                        YEAR(i.Invoice_Date) = "' . $year . '"
                                    AND 
                                        MONTH(i.Invoice_Date) = "' . $month . '" ' . $cond . '
                                            and i.current_year_id = "' . $_SESSION['current_year_id'] . '"
                                    GROUP BY 
                                        d.Product_ID
                                    ORDER BY
                                        QTY DESC
                                    LIMIT 5
                            ');

        while ($row = $this->db->assoc($query)) {
            $dataName[] = $row['Name'];
            $dataQty[] = $row['QTY'];
        }
        $data = array_merge($dataName, $dataQty);

        return $data;
    }

    public function viewCashBankBalance($is_cash, $year = null) {
        $data = array();
        $total_balance = 0.0;
        if ($is_cash == 1) {
            $query = $this->db->query('SELECT Account_ID,Account_Code,Account_Name FROM tbl_chart_of_accounts WHERE LEFT(Account_Code,2) = 53');
        } else {
            $parent_query = $this->db->query('SELECT Account_ID FROM tbl_chart_of_accounts WHERE Account_Code = 51');
            $parent_assoc = $this->db->assoc($parent_query);
            $query = $this->db->query('SELECT Account_ID,Account_Code,Account_Name FROM tbl_chart_of_accounts WHERE Parent_ID = "' . $parent_assoc['Account_ID'] . '" OR left(Account_Code,2) = 51');
        }
        $i = 0;
        while ($row = $this->db->assoc($query)) {
            $temp = self::returnBalanceByAccount(null, null, $year, $row['Account_Code']);
            if ($temp['Balance'] != 0) {
                $data[$i]['ID'] = $row['Account_ID'];
                $data[$i]['Code'] = $row['Account_Code'];
                $data[$i]['Name'] = $row['Account_Name'];
                $data[$i]['Balance'] = $temp['Balance'] >= 0 ? $temp['Balance'] : '(' . ltrim(CommonFunctions::formatNumber($temp['Balance']), '-') . ')';
                $total_balance += $temp['Balance'];
                $i++;
            }
        }
        $data['Total_Balance'] = $total_balance >= 0 ? $total_balance : '(' . ltrim(CommonFunctions::formatNumber($total_balance), '-') . ')';
        return $data;
    }

    public function viewTransactionsByCode($code = null, $year = null, $left_code = null, $len = null) {
        $data = array();
        $condition = '';
        $debits = 0.0;
        $credits = 0.0;
        $balance = 0.0;
        if (!is_null($year))
            $condition .= ' AND YEAR(t.Transaction_Date) = "' . $year . '"';
        if (!is_null($left_code))
            $condition .= ' AND LEFT(a.Account_Code,' . $len . ') = "' . $left_code . '" ';
        if (!is_null($code))
            $condition .= ' AND a.Account_ID = "' . $code . '" ';
        $query = $this->db->query('SELECT 
                                        t.*,
                                        a.Account_Name,
                                        a.Account_Code,
                                        c.Currency_Symbol
                                    FROM 
                                        tbl_accounting_transactions t
                                    LEFT JOIN 
                                        tbl_common_currency c
                                    ON 
                                        t.Currency_ID = c.Currency_ID
                                     JOIN
                                        tbl_chart_of_accounts a
                                    ON 
                                        t.Account_ID = a.Account_ID
                                    WHERE 
                                        t.current_year_id = "' . smc_session()['current_year_id'] . '"

                ' . $condition);
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
            if ($row['Transaction_Type'] == 'Debit') {
                $debits += CurrencyFactory::returnPriceInDefaultCurrencyValue($row['Currency_ID'], $row['Transaction_Amount']);
                $balance += CurrencyFactory::returnPriceInDefaultCurrencyValue($row['Currency_ID'], $row['Transaction_Amount']);
            } elseif ($row['Transaction_Type'] == 'Credit') {
                $credits += CurrencyFactory::returnPriceInDefaultCurrencyValue($row['Currency_ID'], $row['Transaction_Amount']);
                $balance -= CurrencyFactory::returnPriceInDefaultCurrencyValue($row['Currency_ID'], $row['Transaction_Amount']);
            }
        }
        $data['Total_Debit'] = $debits;
        $data['Total_Credit'] = $credits;
        $data['Total_Balance'] = $balance >= 0 ? $balance : '(' . ltrim($balance, '-') . ')';
        return $data;
    }

    public function financialSettings($year = null) {
        $total_incomes = self::returnBalanceByAccount(70, 2, $year)['Balance'] * (-1); // credit account so balance normally negative so must multiply with -1 to be positive
        $total_cost = self::returnBalanceByAccount(601, 3, $year)['Balance'];
        $gross_margin = $total_incomes - $total_cost;

        $expense_64 = self::returnBalanceByAccount(64, 2, $year)['Balance'];
        $expense_67 = self::returnBalanceByAccount(67, 2, $year)['Balance'];
        $expense_68 = self::returnBalanceByAccount(68, 2, $year)['Balance'];
        $expense_69 = self::returnBalanceByAccount(69, 2, $year)['Balance'];

        $expenses = $expense_64 + $expense_67 + $expense_68 + $expense_69;
        $depreciation = self::returnBalanceByAccount(65, 2, $year)['Balance'];
        $salaries = self::returnBalanceByAccount(63, 2, $year)['Balance'];
        $other_expenses = self::returnBalanceByAccount(6, 1, $year)['Balance'] - ($total_cost + $expenses + $depreciation + $salaries);
        $other_incomes = self::returnBalanceByAccount(77, 2, $year)['Balance'];
        $net_profit = ($total_incomes + $gross_margin + $other_incomes) - ($total_cost + $expenses + $depreciation + $salaries + $other_expenses);

        $data = array(
            array(
                $total_incomes, $total_cost, $gross_margin, $expenses, $depreciation, $salaries, $other_expenses, $other_incomes, $net_profit
            ),
            array(
                CommonLanguage::getMessage('total_incomes'),
                CommonLanguage::getMessage('total_costs'),
                CommonLanguage::getMessage('gross_margins'),
                CommonLanguage::getMessage('expenses'),
                CommonLanguage::getMessage('depreciation'),
                CommonLanguage::getMessage('salaries'),
                CommonLanguage::getMessage('other_expenses'),
                CommonLanguage::getMessage('other_incomes'),
                CommonLanguage::getMessage('net_profit')
            )
        );
        return $data;
    }

    private function returnBalanceByAccount($left_code = null, $len = null, $year= null, $code = null) {
        $data = array();
        $condition = '';
        $balance = 0.0;
        $debits = 0.0;
        $credits = 0.0;
        if (!is_null($left_code))
            $condition .= ' AND LEFT(a.Account_Code,' . $len . ') = "' . $left_code . '" ';
        if (!is_null($code))
            $condition .= ' AND a.Account_Code = "' . $code . '" ';
        if (!empty($this->info['branch_id']))
            $condition .= ' AND t.branch_id = "' . $this->info['branch_id'] . '" ';
        if (!empty($year))
            $condition .= ' AND YEAR(Transaction_Date) = "' . $year . '" ';
        $query = $this->db->query('SELECT 
                                        t.*,
                                        a.Account_Code
                                    FROM 
                                        tbl_accounting_transactions t
                                        join tbl_chart_of_accounts a on a.Account_ID = t.Account_ID
                                    WHERE  t.current_year_id = "' . smc_session()['current_year_id'] . '"
                ' . $condition);
        while ($row = $this->db->assoc($query)) {
            if ($row['Transaction_Type'] == 'Debit') {
                $debits += CurrencyFactory::returnPriceInDefaultCurrencyValue($row['Currency_ID'], $row['Transaction_Amount']);
                $balance += CurrencyFactory::returnPriceInDefaultCurrencyValue($row['Currency_ID'], $row['Transaction_Amount']);
            } elseif ($row['Transaction_Type'] == 'Credit') {
                $credits += CurrencyFactory::returnPriceInDefaultCurrencyValue($row['Currency_ID'], $row['Transaction_Amount']);
                $balance -= CurrencyFactory::returnPriceInDefaultCurrencyValue($row['Currency_ID'], $row['Transaction_Amount']);
            }
        }
        $result = array(
            'Credit' => $credits,
            'Debit' => $debits,
            'Balance' => $balance
        );
        return $result;
    }

    public function get_total_expenses($date) {
        $condition = $date_cond = $date_cond_truck = '';
        $total = 0;
        if (!is_null($date)) {
            if (empty(smc_session()['default_setting']['dashboard_daily_cards_type'])) {
                $date_cond .= ' AND Expense_Date = "' . $date . '"';
                $date_cond_truck .= ' AND Truck_Expense_Date = "' . $date . '"';
            } elseif (smc_session()['default_setting']['dashboard_daily_cards_type'] == 1) {
                $start_date = $date . ' ' . smc_session()['default_setting']['default_start_time'];
                $end_date = date('Y-m-d H:i:s', strtotime($start_date . ' +1 day -1 second'));
                $condition.=' and date_created between "' . $start_date . '" and "' . $end_date . '" ';
            } elseif (smc_session()['default_setting']['dashboard_daily_cards_type'] == 2) {
                $branch_id = !empty($this->info['branch_id']) ? $this->info['branch_id'] : '';
                $start_date = Invoicepos::get_last_close_end_day($branch_id);
                $condition.=' and date_created > "' . $start_date . '" ';
            }
        }
        if (!empty($this->info['branch_id']))
            $condition = ' AND branch_id = "' . $this->info['branch_id'] . '"';
        $query = $this->db->query('select sum(Expense_Total_Price*current_rate) as total_expense
            from tbl_accounting_expenses where  1=1' . $date_cond . ' ' . $condition);
        $total = $this->db->assoc($query)['total_expense'];
//        $total = CurrencyFactory::returnPriceInDefaultCurrencyValue(smc_session()['local_currency'], $total);


        $query = $this->db->query('select sum(Truck_Expense_Amount*current_rate) as total_expense
            from tbl_garage_truck_expenses where 1=1 ' . $date_cond_truck . ' ' . $condition);
        $total+= $this->db->assoc($query)['total_expense'];
        $total = CurrencyFactory::returnPriceInDefaultCurrencyValue(smc_session()['local_currency'], $total);


        return $total;
    }

}
