<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of CommonBusiness
 *
 * @author User
 */
class CommonBusiness {

    protected $info = array();
    public static $db = null;

    public function __construct() {
        
    }

    public static function db() {
        if (is_null(self::$db))
            self::$db = DBConnectorFactory::loadDriver();
        return self::$db;
    }

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

    public static function viewCostByAverage($product_ids_arr, $all_qty = false) {
        if (!is_array($product_ids_arr))
            $product_ids_arr = explode(',', $product_ids_arr);
        $product_ids_str = implode(',', $product_ids_arr);
        $sum_costs = array();
        foreach ($product_ids_arr as $prod_id)
            $sum_costs[$prod_id] = 0;
        //
        if (!$all_qty) {
            $query = self::db()->query('SELECT Product_ID, (sum(remaining_quantity*Unit_Cost)/sum(remaining_quantity)) as avg_cost
            FROM tbl_stock_products_costs WHERE Product_ID in (' . $product_ids_str . ')
            group by Product_ID having sum(remaining_quantity) > 0 ');
        } else {
            $query = self::db()->query('SELECT Product_ID,
                (sum(Product_Quantity*Unit_Cost)/sum(Product_Quantity)) as avg_cost
                FROM tbl_stock_product_transactions 
                WHERE Product_ID in (' . $product_ids_str . ')
                    and Transaction_Type_ID in (1,6,22,35,42,10,15,38) and Operation_Type = "+" and is_current = 1
                 group by Product_ID ');
        }
        while ($row = self::db()->assoc($query))
            $sum_costs[$row['Product_ID']] = round($row['avg_cost'], 3);
        //
        return $sum_costs;
    }

    public static function addCostByType($arr, $type) {
//         echo"<pre>";die(print_r($arr));
//        echo $type.'===================';
        $method = smc_session()['stock_method'];
        $cost = 0;
        if ($type == "transfer" || $type == 'delete_transfer' || $type == 'edit_transfer_old' || $type == 'edit_transfer_new')
            $cost = self::viewCurrentProductCost($arr['Product_ID']);
        else {
            if ($method == 3) {
                if ($type == 'add_purchase' || $type == "assign" || $type == 'edit_purchase_new' || $type == 'edit_return_purchase_old' || $type == 'add_produced_items' || $type == 'edit_produced_items_new' || $type == 'add_supplier_delivery' || $type == 'edit_supplier_delivery_new' || $type == 'delete_return_purchase' || $type=='edit_assign_new') {
                    $arr["Operation"] = "+";
                    $arr["is_new"] = true;
                    $arr["time_cond"] = false;
                    if ($arr['is_service'] != 1)
                        $cost = self::addCost($arr);
                } elseif ($type == "add_return_purchase" || $type == 'edit_return_purchase_new' || $type == 'edit_purchase_old' || $type == "edit_produced_items_old" || $type == 'edit_supplier_delivery_old' || $type == 'delete_purchase' || $type == 'delete_produced_items' || $type == 'delete_supplier_delivery' || $type=='delete_assign' || $type=='edit_assign_old') {
                    $arr["Operation"] = "-";
                    $arr["is_new"] = true;
                    $arr["time_cond"] = false;
                    $arr["same_time"] = true;
                    $arr["is_negative"] = true;
                    if ($arr['is_service'] != 1)
                        $cost = self::addCost($arr);
                } else {
                    $avg_cost_arr = self::viewCostByAverage($arr['Product_ID']);
                    $cost = $avg_cost_arr[$arr['Product_ID']];
                    if ($type == 'add_invoice' || $type == 'edit_invoice_new' || $type == 'edit_machine_maintenance_new' || $type == 'add_machine_maintenance' || $type == "adjust_negative" || $type == "damage" || $type == 'edit_return_invoice_old' || $type == 'add_raw_items' || $type == 'edit_raw_items_old' || $type == 'add_customer_delivery' || $type == 'edit_customer_delivery_new' || $type == 'delete_return_invoice' || $type == 'delete_raw_items' || $type == 'add_station_invoice' || $type == 'edit_station_invoice_new' || $type == 'add_consumption' || $type == 'edit_consumption_new' || $type=='edit_damage_new' || $type=='edit_adjust_old_positive' || $type =='edit_adjust_new_negative' || $type=='delete_adjust_positive' || $type=='add_stock_acc_adjust' || $type=='edit_stock_acc_adjust_new')
                        $arr["Operation"] = "-";
                    elseif ($type == 'edit_invoice_old' || $type == 'edit_machine_maintenance_old' || $type == 'delete_machine_maintenance' || $type == 'add_return_invoice' || $type == 'edit_return_invoice_new' || $type == "adjust_positive" || $type == 'edit_customer_delivery_old' || $type == 'edit_damage_old' || $type == 'delete_invoice' || $type == 'delete_damage' || $type == 'edit_station_invoice_old' || $type == 'delete_station_invoice' || $type == 'edit_consumption_old' || $type == 'delete_consumption' || $type == 'delete_customer_delivery' || $type=='edit_adjust_old_negative' || $type =='edit_adjust_new_positive' || $type=='delete_adjust_negative' || $type=='edit_stock_acc_adjust_old' || $type=='delete_stock_acc_adjust')
                        $arr["Operation"] = "+";
                }
            } else {
                if ($type == 'add_purchase' || $type == 'edit_purchase_new' || $type == 'add_produced_items' || $type == 'edit_produced_items_new' || $type == 'add_supplier_delivery' || $type == 'edit_supplier_delivery_new') {
                    $arr["Operation"] = "+";
                    $arr["is_new"] = true;
                    $arr["time_cond"] = false;
                } elseif ($type == 'edit_purchase_old' || $type == 'edit_return_invoice_old' || $type == 'edit_produced_items_old' || $type == 'edit_supplier_delivery_old' || $type == 'delete_return_invoice' || $type == 'delete_purchase' || $type == 'delete_produced_items' || $type == 'delete_supplier_delivery' || $type=='edit_assign_old' || $type=='delete_assign') {
                    $arr["Operation"] = "-";
                    $arr["is_new"] = false;
                    $arr["time_cond"] = true;
                    $arr["is_negative"] = true;
                } elseif ($type == "add_invoice" || $type == 'add_raw_items' || $type == 'add_machine_maintenance' || $type == 'edit_machine_maintenance_new' || $type == 'add_customer_delivery' || $type == 'add_station_invoice' || $type == 'edit_station_invoice_new' || $type == 'add_consumption') {
                    $arr["Operation"] = "-";
                    $arr["is_new"] = false;
                    $arr["time_cond"] = false;
                } elseif ($type == "edit_invoice_old" || $type == "assign" || $type == "edit_return_purchase_old" || $type == 'edit_raw_items_old' || $type == 'edit_machine_maintenance_old' || $type == 'delete_machine_maintenance' || $type == 'edit_customer_delivery_old' || $type == 'edit_damage_old' || $type == 'delete_invoice' || $type == 'delete_return_purchase' || $type == 'delete_damage' || $type == 'delete_raw_items' || $type == 'edit_station_invoice_old' || $type == 'delete_station_invoice' || $type == 'edit_consumption_old' || $type == 'delete_consumption' || $type == 'delete_customer_delivery' || $type=='edit_assign_new' || $type=='edit_stock_acc_adjust_old' || $type=='delete_stock_acc_adjust') {
                    $arr["Operation"] = "+";
                    $arr["is_new"] = true;
                    $arr["time_cond"] = false;
                } elseif ($type == "edit_invoice_new" || $type == 'edit_raw_items_new' || $type == 'edit_customer_delivery_new' || $type == "edit_station_invoice_new" || $type == 'edit_consumption_new') {
                    $arr["Operation"] = "-";
                    $arr["is_new"] = false;
                    $arr["time_cond"] = false;
                    $arr["same_time"] = true;
                } elseif ($type == "adjust_positive" || $type == "adjust_negative" || $type == "damage" || $type=='edit_damage_new' || $type=='edit_adjust_old_negative' || $type =='edit_adjust_new_positive' || $type=='delete_adjust_negative' || $type=='edit_adjust_old_positive' || $type =='edit_adjust_new_negative' || $type=='delete_adjust_positive' || $type=='add_stock_acc_adjust' || $type=='edit_stock_acc_adjust_new') {
                    $arr["Operation"] = ($type == "adjust_positive" || $type=='edit_adjust_old_negative' || $type =='edit_adjust_new_positive' || $type=='delete_adjust_negative' ? "+" : "-");
                    $arr["is_new"] = false;
                    $arr["time_cond"] = false;
                } elseif ($type == "add_return_purchase" || $type == 'edit_return_purchase_new') {
                    $arr["Operation"] = "-";
                    $arr["is_new"] = false;
                    $arr["time_cond"] = true;
                } elseif ($type == "add_return_invoice") {
                    $arr["Operation"] = "+";
                    $arr["is_new"] = false;
                    $arr["time_cond"] = false;
                    $arr["skip_zero"] = true;
                } elseif ($type == "edit_return_invoice_new") {
                    $arr["Operation"] = "+";
                    $arr["is_new"] = false;
                    $arr["time_cond"] = false;
                    $arr["same_time"] = true;
                }
                if ($arr['is_service'] != 1)
                    $cost = self::addCost($arr);
            }
        }

        //if send inverse then we must inverse the operation
        if (!empty($arr['is_inverse']))
            $arr['Operation'] = $arr['Operation'] == '+' ? '-' : '+';
        //
        if ($type == 'add_purchase')
            $arr['trans_type'] = 1;
        elseif ($type == 'edit_purchase_old')
            $arr['trans_type'] = 12;
        elseif ($type == 'edit_purchase_new')
            $arr['trans_type'] = 35;
        elseif ($type == 'delete_purchase')
            $arr['trans_type'] = 30;
        elseif ($type == 'add_invoice')
            $arr['trans_type'] = 2;
        elseif ($type == 'edit_invoice_old')
            $arr['trans_type'] = 11;
        elseif ($type == 'edit_invoice_new')
            $arr['trans_type'] = 43;
        elseif ($type == 'delete_invoice')
            $arr['trans_type'] = 29;
        elseif ($type == 'add_return_purchase')
            $arr['trans_type'] = 8;
        elseif ($type == 'edit_return_purchase_old')
            $arr['trans_type'] = 13;
        elseif ($type == 'edit_return_purchase_new')
            $arr['trans_type'] = 36;
        elseif ($type == 'add_return_invoice')
            $arr['trans_type'] = 9;
        elseif ($type == 'edit_return_invoice_old')
            $arr['trans_type'] = 14;
        elseif ($type == 'edit_return_invoice_new')
            $arr['trans_type'] = 37;
        elseif ($type == 'delete_return_invoice')
            $arr['trans_type'] = 27;
        elseif ($type == 'delete_return_purchase')
            $arr['trans_type'] = 28;
        elseif ($type == 'assign')
            $arr['trans_type'] = 10;
        elseif ($type == "adjust_positive" || $type == "adjust_negative" )
            $arr['trans_type'] = 6;
        elseif ($type == "transfer")
            $arr['trans_type'] = 4;
        elseif ($type == "delete_transfer")
            $arr['trans_type'] = 32;
        elseif ($type == "damage")
            $arr['trans_type'] = 7;
        elseif ($type == "add_produced_items")
            $arr['trans_type'] = 15;
        elseif ($type == "edit_produced_items_old")
            $arr['trans_type'] = 16;
        elseif ($type == "edit_produced_items_new")
            $arr['trans_type'] = 38;
        elseif ($type == "delete_produced_items")
            $arr['trans_type'] = 33;
        elseif ($type == "delete_raw_items")
            $arr['trans_type'] = 34;
        elseif ($type == "add_raw_items")
            $arr['trans_type'] = 17;
        elseif ($type == "edit_raw_items_old")
            $arr['trans_type'] = 18;
        elseif ($type == "edit_raw_items_new")
            $arr['trans_type'] = 39;
        elseif ($type == "add_machine_maintenance")
            $arr['trans_type'] = 19;
        elseif ($type == "edit_machine_maintenance_old")
            $arr['trans_type'] = 20;
        elseif ($type == "edit_machine_maintenance_new")
            $arr['trans_type'] = 40;
        elseif ($type == "add_customer_delivery")
            $arr['trans_type'] = 21;
        elseif ($type == "add_supplier_delivery")
            $arr['trans_type'] = 22;
        elseif ($type == "edit_customer_delivery_old")
            $arr['trans_type'] = 23;
        elseif ($type == 'edit_customer_delivery_new')
            $arr['trans_type'] = 41;
        elseif ($type == "edit_supplier_delivery_old")
            $arr['trans_type'] = 24;
        elseif ($type == 'edit_supplier_delivery_new')
            $arr['trans_type'] = 42;
        elseif ($type == "edit_damage_old")
            $arr['trans_type'] = 25;
        elseif ($type == "delete_damage")
            $arr['trans_type'] = 31;
        elseif ($type == "delete_machine_maintenance")
            $arr['trans_type'] = 26;
        elseif ($type == "add_station_invoice")
            $arr['trans_type'] = 44;
        elseif ($type == 'edit_station_invoice_old')
            $arr['trans_type'] = 45;
        elseif ($type == 'edit_station_invoice_new')
            $arr['trans_type'] = 47;
        elseif ($type == 'delete_station_invoice')
            $arr['trans_type'] = 46;
        elseif ($type == 'add_consumption')
            $arr['trans_type'] = 48;
        elseif ($type == 'edit_consumption_old')
            $arr['trans_type'] = 49;
        elseif ($type == 'edit_consumption_new')
            $arr['trans_type'] = 51;
        elseif ($type == 'delete_consumption')
            $arr['trans_type'] = 50;
        elseif ($type == 'delete_customer_delivery')
            $arr['trans_type'] = 52;
        elseif ($type == 'delete_supplier_delivery')
            $arr['trans_type'] = 53;
        elseif ($type == 'edit_damage_new')
            $arr['trans_type'] = 54;
        elseif ($type == 'edit_assign_old')
            $arr['trans_type'] = 55;
        elseif ($type == 'edit_assign_new')
            $arr['trans_type'] = 56;
        elseif ($type == 'delete_assign')
            $arr['trans_type'] = 57;
        elseif ($type == 'edit_adjust_old_negative'  || $type=='edit_adjust_old_positive')
            $arr['trans_type'] = 58;
        elseif ($type == 'edit_adjust_new_positive'  || $type=='edit_adjust_new_negative')
            $arr['trans_type'] = 59;
        elseif ($type == 'delete_adjust_negative'  || $type=='delete_adjust_positive')
            $arr['trans_type'] = 60;
        elseif ($type == 'edit_transfer_old')
            $arr['trans_type'] = 61;
        elseif ($type == 'edit_transfer_new')
            $arr['trans_type'] = 62;
        elseif ($type == 'add_stock_acc_adjust')
            $arr['trans_type'] = 63;
        elseif ($type == 'edit_stock_acc_adjust_new')
            $arr['trans_type'] = 64;
        elseif ($type == 'edit_stock_acc_adjust_old')
            $arr['trans_type'] = 65;
        elseif ($type == 'delete_stock_acc_adjust')
            $arr['trans_type'] = 66;
        //
//        echo $type.'===';
//        echo"<pre>";die(print_r($arr));
        if(!empty($arr['Unit_Cost']) && ($type=='adjust_negative' || $type=='adjust_positive' || $type=='edit_adjust_new_positive' || $type=='edit_adjust_old_positive' || $type=='edit_adjust_new_negative' || $type=='edit_adjust_old_positive'))
        $cost = ($arr['Unit_Cost'] * $arr['current_rate']);
        //
        if ($type == 'add_purchase' || $type == "assign" || $type == 'edit_purchase_new' || $type == 'edit_return_purchase_old' || $type == "add_return_purchase" || $type == 'edit_return_purchase_new' || $type == 'edit_purchase_old' || $type == 'add_supplier_delivery' || $type == 'edit_supplier_delivery_old' || $type == 'edit_supplier_delivery_new' || $type == 'delete_return_purchase' || $type == 'delete_purchase' || $type == 'delete_supplier_delivery' || $type=='edit_assign_new' || $type=='edit_assign_old' || $type=='delete_assign')
            $cost = ($arr['Unit_Cost'] * $arr['current_rate']);
        $mydate = date("Y-m-d H:i:s");
        $arr['date_time'] = !empty($arr['date_time']) ? $arr['date_time'] : $mydate;
        $logged_id = Session::getUserID();
        $date_time = !empty($arr['date_time_now']) ? $arr['date_time_now'] : $arr['date_time'];
        $arr['Currency_ID'] = !empty($arr['Currency_ID']) ? $arr['Currency_ID'] : smc_session()['local_currency'];
        $rate = !empty($arr['current_rate']) ? $arr['current_rate'] : CurrencyFactory::getLastExRate($arr['Currency_ID']);
        $second_rate = CurrencyFactory::getSecondCurrencyLastExRate($arr['Currency_ID']);
        $current_cost = $cost / $rate;
        $arr['unit_price'] = !empty($arr['unit_price']) ? $arr['unit_price'] : 0;
        $local_price = $arr['unit_price'] * $rate;
        $arr['lot_nb'] = !empty($arr['lot_nb']) ? $arr['lot_nb'] : '';
        $arr['serial_nb'] = !empty($arr['serial_nb']) ? $arr['serial_nb'] : '';
        $arr['main_nb'] = !empty($arr['main_nb']) ? $arr['main_nb'] : '';
        $arr['trans_note'] = !empty($arr['trans_note']) ? $arr['trans_note'] : '';
        $arr['customer_id'] = isset($arr['customer_id']) ? $arr['customer_id'] : null;
        $arr['supplier_id'] = isset($arr['supplier_id']) ? $arr['supplier_id'] : null;
        $data = [
            &$arr['Product_ID'],
            &$arr['depot_id'],
            &$arr['Quantity'],
            &$arr['trans_type'],
            &$arr['trans_date'],
            &$logged_id,
            &$arr['Operation'],
            &$cost,
            &$arr['current_quantity'],
            &$arr['uom_id'],
            &$date_time,
            &$arr['is_actual'],
            &$arr['expiry_date'],
            &$arr['lot_nb'],
            &$arr['serial_nb'],
            &$arr['main_id'],
            &$current_cost, &$rate, &$second_rate, $local_price, $arr['Currency_ID'], $arr['unit_price'],
            $arr['main_nb'], $arr['customer_id'], $arr['supplier_id'], $arr['trans_note']
        ];
        self::db()->prepare_Stmt('insert into tbl_stock_product_transactions 
                        (`Product_ID`, `Depot_ID`, `Product_Quantity`, `Transaction_Type_ID`,
                        `Transaction_Date`, `Logged_ID`, `Operation_Type`, `Unit_Cost`,
                        `Item_Quantity_Current`, `UOM_ID`, `date_time`,is_actual,expiry_date,lot_nb,serial_nb,
                        main_id,current_cost,current_rate,second_currency_rate,unit_price,Currency_ID,current_price,
                        main_nb,customer_id,supplier_id,trans_note)
                        VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
            ', $data);
        //
        return $cost;
    }

    public static function addCost($arr, $is_init = true) {
        if (!isset($arr['is_negative']))
            $arr['is_negative'] = false;
        if (!isset($arr['same_time']))
            $arr['same_time'] = false;
        if (!isset($arr['skip_zero']))
            $arr['skip_zero'] = false;
        //
        $local_cost = floatval($arr['Unit_Cost']) * $arr['current_rate'];
        $data = array(&$arr['Product_ID'], &$arr['Quantity'], &$local_cost, &$arr['date_time'], &$arr['Quantity']);

        $return_cost = null;
        $return_qty = 0;
        $return_avg = 0;
        $method = smc_session()['stock_method'];
        //
        if ($arr['is_new']) {
            self::db()->prepare_Stmt('insert into tbl_stock_products_costs 
                set Product_ID = ?, remaining_quantity = (? ' . ($arr['Operation'] == '+' ? '' : ' * -1') . '),
                     Unit_Cost = ?, date_time = ?
                on duplicate key update remaining_quantity = remaining_quantity ' . $arr['Operation'] . ' ? 
            ', $data);
            $return_cost = $local_cost;
            return $return_cost;
        }
        //else if not is new
        $condition = ' ';
        $order_cond = ' ';
        if ($arr['time_cond'])
            $condition .= ' and date_time <= "' . $arr['date_time'] . '" ';
        if ($arr['same_time'])
            $condition .= ' and date_time = "' . $arr['date_time'] . '" ';
        if ($arr['Unit_Cost'] != '')
            $order_cond = ' order by abs(Unit_Cost-' . $local_cost . ') asc, date_time desc';
        else {
            if (isset($arr['time_order']))
                $order_cond = ' order by date_time ' . $arr['time_order'];
            else {
                if ($method == 2)
                    $order_cond = ' order by date_time desc';
                else if ($method == 1)
                    $order_cond = ' order by date_time asc';
                //                else    //average
                //                    $order_cond .= ' limit 1';
            }
        }
        if ($arr['Operation'] == '+')
            $order_cond .= ' limit 1';
        if ($arr['Operation'] == '-' && !$arr['is_negative'])
            $condition .= ' and remaining_quantity > 0 ';
        if ($arr['skip_zero'])
            $condition .= ' and remaining_quantity <> 0 ';
        //
        $item_qty = $arr['Quantity'];
        $is_done = false;
        $cost_query = self::db()->query('select * from tbl_stock_products_costs 
                        where Product_ID = "' . $arr['Product_ID'] . '" ' . $condition . $order_cond);
        while ($row = self::db()->assoc($cost_query)) {
            $cost_id = $row['Product_Cost_ID'];
            if (empty($return_cost))
                $return_cost = $row['Unit_Cost'];
            $row_qty = $item_qty;
            //to allow negative qty add condition here
            if ($arr['Operation'] == '-' && $row['remaining_quantity'] < $row_qty && !$arr['is_negative'])
                $row_qty = $row['remaining_quantity'];
            self::db()->prepare_Stmt('update tbl_stock_products_costs 
                                    set remaining_quantity = remaining_quantity ' . $arr['Operation'] . ' ? 
                                    where Product_Cost_ID = ?', [&$row_qty, &$cost_id]);
            $item_qty -= $row_qty;
            $return_avg += ($row['Unit_Cost'] * $row_qty);
            $return_qty += $row_qty;
            if ($item_qty <= 0 || $arr['Operation'] == '+') {
                $is_done = true;
                break;
            }
        }
        if (!$is_done) {
            if ($arr['skip_zero'])
                $arr['skip_zero'] = false;
            elseif ($arr['same_time']) {
                $arr['same_time'] = false;
                //                    if (!$arr['time_cond'])   //stop it since in sales/edit sales no cost
                //                        $arr['is_new'] = true;
            } elseif ($arr['time_cond'])
                $arr['time_cond'] = false;
            else
                $arr['is_new'] = true;
            $arr['Quantity'] = $item_qty;
            $tmp_return_cost = self::addCost($arr, false);
            if (!$arr['is_new']) {
                $return_cost = $tmp_return_cost[0];
                $return_avg += $tmp_return_cost[1];
                $return_qty += $tmp_return_cost[2];
            } elseif (empty($return_cost))
                $return_cost = $tmp_return_cost;
        }
//        echo ($return_avg / $return_qty).'==='.$return_avg.'==='.$return_qty.'===';
        if ($return_qty != 0)
            $return_cost = ($return_avg / $return_qty);
        if (!$is_init)
            return array($return_cost, $return_avg, $return_qty);
        return $return_cost;
    }

    public static function viewLastProductCost($product_id, $with_symbol = false, $is_local = true) {
        $cost = 0;
        $cost_col = $is_local ? 'Unit_Cost' : '(current_cost*second_currency_rate) as Unit_Cost ';
        $query = self::db()->query('SELECT ' . $cost_col . ' FROM tbl_stock_product_transactions p
                                        WHERE p.Product_ID = "' . $product_id . '"
                                            and Transaction_Type_ID in (1,6,22,35,42,10,15,38) and Operation_Type = "+" and is_current = 1
                                        ORDER BY p.date_time desc,p.Transaction_ID DESC LIMIT 1

                    ');
        //        $query = self::db()->query('SELECT Unit_Cost FROM tbl_stock_products_costs p
        //                                        WHERE p.Product_ID = "' . $product_id . '"
        //                                        ORDER BY p.date_time desc,p.Product_Cost_ID DESC LIMIT 1
        //                    ');
        $row = self::db()->assoc($query);
        if (!empty($row))
            $cost = $row['Unit_Cost'];
        return $cost . ($with_symbol ? ' ' . ($is_local ? smc_session()['local_currency_symbol'] : smc_session()['second_currency']['symbol']) : '');
    }

    public static function viewCurrentProductCost($product_id, $with_symbol = false, $method = null) {
//        echo $method.'===';
        $cost = 0;
        if (empty($method))
            $method = smc_session()['stock_method'];
        if ($method == 3) {
            $cost_arr = self::viewCostByAverage($product_id);
            $cost = $cost_arr[$product_id];
        } else {
            if ($method == 2)
                $order_cond = ' order by date_time desc';
            else if ($method == 1)
                $order_cond = ' order by date_time asc';
            $query = self::db()->query('SELECT Unit_Cost FROM tbl_stock_products_costs p
                                        WHERE p.Product_ID = "' . $product_id . '" and remaining_quantity > 0
                                        ' . $order_cond . ' LIMIT 1
                    ');


            $row = self::db()->assoc($query);
            if (!empty($row))
                $cost = $row['Unit_Cost'];
        }
        return ($cost . ($with_symbol ? (' ' . smc_session()['local_currency_symbol']) : ''));
    }

    public static function viewLastProductCustomerClassPrice($product_id, $customer_id, $classID) {
        $last_price = 0;
//        $start = microtime(true);
        if (is_array($product_id)) {
            $data = array();

            $query = self::db()->query('select details.Entery_ID,
                                                         (((details.Product_Net_Price * details.Product_Quantity)/Item_Quantity_Current)*main.current_rate) as last_price,
                                                         main.Invoice_Date,
                                                         details.Product_ID
                                                  from tbl_invoice_products_details details
                                                  inner join tbl_invoices main using(Invoice_ID_Auto)
                                                  where main.Customer_ID = "' . $customer_id . '"
                                                  and main.Class_ID = "' . $classID . '"
                                                  and details.Product_ID in (' . implode(',', $product_id) . ')
                                                  order by details.Product_ID,main.Invoice_Date desc,details.Entery_ID desc
                    ');
//            echo (microtime(true)-$start).'======';
            while ($row = self::db()->assoc($query)) {
                if (!array_key_exists($row['Product_ID'], $data))
                    $data[$row['Product_ID']] = $row['last_price'];
            }
//            echo"<pre>";die(print_r($data));
//            echo (microtime(true)-$start).'======';
            return $data;
        } else {
            $query = self::db()->query('select details.Entery_ID,
                                                         (((details.Product_Net_Price * details.Product_Quantity)/Item_Quantity_Current)*main.current_rate) as last_price,
                                                         main.Invoice_Date,
                                                         details.Product_ID
                                                  from tbl_invoice_products_details details
                                                  inner join tbl_invoices main using(Invoice_ID_Auto)
                                                  where main.Customer_ID = "' . $customer_id . '"
                                                  and main.Class_ID = "' . $classID . '"
                                                  and details.Product_ID = "' . $product_id . '"
                                                  order by main.Invoice_Date desc,details.Entery_ID desc
                                                  limit 1
                    ');

            $row = self::db()->assoc($query);
            if (!empty($row))
                $last_price = $row['last_price'];
            return $last_price;
        }
    }

//    public static function viewLastProductCustomerClassPrice($product_id, $customer_id, $classID) {
//        $last_price = 0;
//        $query = self::db()->query('select details.Entery_ID,
//                                                         (((details.Product_Net_Price * details.Product_Quantity)/Item_Quantity_Current)*main.current_rate) as last_price,
//                                                         main.Invoice_Date,
//                                                         details.Product_ID
//                                                  from tbl_invoice_products_details details
//                                                  inner join tbl_invoices main using(Invoice_ID_Auto)
//                                                  where main.Customer_ID = "' . $customer_id . '"
//                                                  and main.Class_ID = "' . $classID . '"
//                                                  and details.Product_ID = "' . $product_id . '"
//                                                  order by main.Invoice_Date desc,details.Entery_ID desc
//                                                  limit 1
//                    ');
//        $row = self::db()->assoc($query);
//        if (count($row) != 0)
//            $last_price = $row['last_price'];
//        return $last_price;
//    }

    public static function updateLinkQuantities($data, $method, $operation) {
        //        $link_qty = $link['quantity'] * $ProductQuantity;
        $cost = CommonBusiness::addCostByType($data, $method);
        if ($data['is_service'] != 1) {
            $up_quan = [&$data['Quantity'], &$data['Product_ID']];
            self::db()->prepare_Stmt("UPDATE 
                                                tbl_stock_products 
                                        SET 
                                                Product_Quantity = Product_Quantity " . $operation . " ?
                                        WHERE 
                                                Product_ID = ? 
                                    ", $up_quan);
            $sub_qty = $operation == '+' ? $data['Quantity'] : $data['Quantity'] * (-1);
            $dep_dat = [&$data['depot_id'], &$data['Product_ID'], &$sub_qty, &$data['Quantity']];
            self::db()->prepare_Stmt("insert into  
                                            tbl_stock_product_location 
                                            (Product_Depot_ID,Location_Product_ID,Product_Depot_Quantity)
                                            values (?,?,?)
                                            on duplicate key update
                                            Product_Depot_Quantity = Product_Depot_Quantity " . $operation . " ?

                                    ", $dep_dat);
        }
        return $cost;
    }

    public static function getBranchByDepot($depot_id) {
        $branch_query = self::db()->query('select branch_id from tbl_stock_depot_warehouse where Depot_ID = "' . $depot_id . '"');
        $branch_id = self::db()->assoc($branch_query)['branch_id'];
        return $branch_id;
    }

    public static function getBranchNameByDepot($depot_id) {
        $branch_arr = array();
        $branch_query = self::db()->query('select branch_id,branch_name from tbl_stock_depot_warehouse join tbl_organization_branches using (branch_id) where Depot_ID = "' . $depot_id . '"');
        $branch_arr = self::db()->assoc($branch_query);
        return $branch_arr;
    }
    public static function getBranchByBranchId($branchid) {
        $branch_arr = array();
        $branch_query = self::db()->query('select * from tbl_organization_branches where branch_id = "' . $branchid . '"');
        $branch_arr = self::db()->assoc($branch_query);
        return $branch_arr;
    }
    public static function getTempableInfoByTempableId($attribute_id)
    {
        $attribute_query = self::db()->query('select * from tbl_template_attribute where attribute_id = "' . $attribute_id . '"');
        $attribute = self::db()->assoc($attribute_query);
        if ($attribute['branch_id'] != 0) {
            return  self::getBranchByBranchId($attribute['branch_id'])['branch_name'];
        } else {
            $template_query = self::db()->query('select * from tbl_print_template where template_id = "' . $attribute['template_id'] . '"');
            $template_name = self::db()->assoc($template_query)['template_name'];
            return $template_name;
        }
    }
    public static function getDriverByDriverId($driver_id) {
        $driver_arr = array();
        $driver_query = self::db()->query('select * from tbl_drivers where driver_id = "' . $driver_id . '"');
        $driver_arr = self::db()->assoc($driver_query);
        return $driver_arr['driver_name'];
    }



    public static function get_item_acc_transactions($product_arr, $prod_ids, $type, $acc_id, $inv_disc_percent = 0, $ship_acc_id = null, $ship_amount = 0, $tax_amount = 0) {
        //$product_arr keys (total_amount,description,vat_amount,product_id)
        //
        //$data keys (trans_amount,trans_desc,trans_account)
//        echo"<pre>";
//        print_r($product_arr);
//        die();
        $data = array();
        //        $prod_ids = implode(array_keys($product_arr), ',');
        $group_accs_arr = array();
        $group_accs_query = self::db()->query('select p.Product_ID,g.* from tbl_stock_products p
            join tbl_group_accounts g using(group_account_id) where p.Product_ID in (' . $prod_ids . ')');
        while ($group_assoc = self::db()->assoc($group_accs_query)) {
            $group_accs_arr[$group_assoc['Product_ID']] = $group_assoc;
        }
        $acc_index = '';
        $acc_tax_index = '';
        if ($type == 'inv') {
            $acc_index = 'sales_acc_id';
            $acc_tax_index = 'sales_tax_acc_id';
        } else if ($type == 'pur') {
            $acc_index = 'pur_acc_id';
            $acc_tax_index = 'pur_tax_acc_id';
        } else if ($type == 'rin') {
            $acc_index = 'sales_return_acc_id';
            $acc_tax_index = 'sales_return_tax_id';
        } else if ($type == 'rpr') {
            $acc_index = 'pur_return_acc_id';
            $acc_tax_index = 'pur_return_tax_acc_id';
        }
        //
        foreach ($product_arr as $indx => $item) {
            if (!isset($group_accs_arr[$item['product_id']]))
                $pr_index = $acc_id;
            else
                $pr_index = $item['vat_amount'] > 0 ? $group_accs_arr[$item['product_id']][$acc_tax_index] : $group_accs_arr[$item['product_id']][$acc_index];

            if (empty($pr_index))
                $pr_index = $acc_id;
            $trans_desc = smc_session()['default_setting']['item_desc_in_transaction'] == 1 ? $item['description'] : '';
            $trans_amount = $item['total_amount']; // - (($item['total_amount'] * $inv_disc_percent) / 100);
            // sales || purchase || return main accounts array
            if (smc_session()['default_setting']['merge_sales_pur_accounts'] == 1) {
                if (isset($data[$pr_index])) {
                    $data[$pr_index]['main']['trans_amount'] += $trans_amount;
                    //                    $data['main'][$pr_index]['trans_account']=$pr_index;
                } else {
                    $data[$pr_index]['main'] = array('trans_amount' => $trans_amount,
                        'trans_account' => $pr_index,
                        'trans_desc' => '');
                }
            } else {
                $data[$indx]['main'] = array('trans_amount' => $trans_amount,
                    'trans_account' => $pr_index,
                    'trans_desc' => $trans_desc);
            }

            // sales || purchase || return person accounts array
            if (smc_session()['default_setting']['merge_cust_supp_accounts'] == 1) {
                if (empty($data[0]['person'])) {
                    $data[0]['person']['trans_amount'] = ($trans_amount + $item['vat_amount']);
                    $data[0]['person']['trans_desc'] = '';
                } else {
                    $data[0]['person']['trans_amount'] += ($trans_amount + $item['vat_amount']);
                }
            } else {
                $data[$indx]['person'] = array('trans_amount' => ($trans_amount + $item['vat_amount']),
                    'trans_desc' => $trans_desc);
            }
        }
        //for shipping
        if ($ship_amount > 0) {
            $indx++;
            $pr_index = $ship_acc_id;
            $trans_amount = $ship_amount;
            //
            $data[$indx]['main'] = array('trans_amount' => $ship_amount,
                'trans_account' => $ship_acc_id,
                'trans_desc' => '',
                'ref' => 'ship');
            // sales || purchase || return person accounts array
            if (smc_session()['default_setting']['merge_cust_supp_accounts'] == 1) {
                $data[0]['person']['trans_amount'] += $ship_amount;
            } else {
                $data[$indx]['person'] = array('trans_amount' => $ship_amount,
                    'trans_desc' => '');
            }
        }
        //        echo $tax_amount.'==';die();
        //for tax_amount
        if ($tax_amount > 0) {
            //            $indx++;
            $pr_index = $acc_id;
            $trans_amount = $tax_amount;
            //
            //            echo $indx.'==';die();
            $data[$pr_index]['main']['trans_amount'] += $tax_amount;
            // sales || purchase || return person accounts array
            if (smc_session()['default_setting']['merge_cust_supp_accounts'] == 1) {
                $data[0]['person']['trans_amount'] += $tax_amount;
            } else {
                $data[$indx]['person'] = array('trans_amount' => $tax_amount,
                    'trans_desc' => '');
            }
        }

        //
//                echo"<pre>";
//                die(print_r($data));
        ksort($data);
        return $data;
    }

    public static function get_trans_main_type($type = null, $is_abrv = false) {
        if (!$is_abrv) {
            $type_arr = [
                'inv' => CommonLanguage::getMessage('invoice'),//'Invoice',
                'pay' => CommonLanguage::getMessage('payment'),//'Payment',
                'rec' => CommonLanguage::getMessage('receipt'),//'Receipt',
                'ret' => CommonLanguage::getMessage('return'),//'Return',
                'pur' => CommonLanguage::getMessage('purchase'),//'Purchase',
                'rin' => CommonLanguage::getMessage('return_invoice'),//'Return Invoice',
                'rpr' => CommonLanguage::getMessage('return_purchase'),//'Return Purchase',
                'cre' => CommonLanguage::getMessage('credit_note'),//'Credit Note',
                'mjv' => CommonLanguage::getMessage('manual_jv'),//'Manual JV',
                'cco' => CommonLanguage::getMessage('customer_coupon'),//'Customer Coupon',
                'sin' => CommonLanguage::getMessage('station_invoice'),//'Station Invoice',
                'cle' => CommonLanguage::getMessage('exchange_rate'),//'Close Exchange Rate',
                'sbl' => CommonLanguage::getMessage('schedule_bill'),//'Schedule Bill',
                'pbl' => CommonLanguage::getMessage('pay_schedule_bill'),//'Payment Schedule Bill',
                'retr' => CommonLanguage::getMessage('return_receipt'),//'Return Receipt',
                'retp' => CommonLanguage::getMessage('return_payment'),//'Return Payment'
                'dln' => CommonLanguage::getMessage('delivery_note'),//'Delivery Note'
            ];
        } else {
            $type_arr = [
                'inv' => CommonLanguage::getMessage('inv_abv'),//'Inv',
                'pay' => CommonLanguage::getMessage('pay_abv'),//'Pay',
                'rec' => CommonLanguage::getMessage('rec_abv'),//'Rec',
                'ret' => CommonLanguage::getMessage('ret_abv'),//'Ret',
                'pur' => CommonLanguage::getMessage('pur_abv'),//'Pur',
                'rin' => CommonLanguage::getMessage('rinv_abv'),//'RInv',
                'rpr' => CommonLanguage::getMessage('rpur_abv'),//'RPur',
                'cre' => CommonLanguage::getMessage('cn_abv'),//'CN',
                'mjv' => CommonLanguage::getMessage('jv_abv'),//'JV',
                'cco' => CommonLanguage::getMessage('ccp_abv'),//'CCP',
                'sin' => CommonLanguage::getMessage('sinv_abv'),//'SInv',
                'cle' => CommonLanguage::getMessage('exrate_abv'),//'ExRate',
                'sbl' => CommonLanguage::getMessage('schbil_abv'),//'SchBill',
                'pbl' => CommonLanguage::getMessage('payschd_abv'),//'Pay SchBill',
                'retr' => CommonLanguage::getMessage('rrec_abv'),//'RRec',
                'retp' => CommonLanguage::getMessage('rpay_abv'),//'RPay'
                'dln' => CommonLanguage::getMessage('dln_abv'),//'Del'
            ];
        }
        if (!is_null($type))
            return (isset($type_arr[$type]) ? $type_arr[$type] : $type);
        else
            return $type_arr;
    }

    public static function get_current_cost_by_uom($product_id, $uom_id) {
        $default_cost = self::viewCurrentProductCost($product_id);
        $query = self::db()->query('SELECT `conversion_amount` FROM `tbl_uom_conversions`
            where uom_from = "' . $uom_id . '" and Product_ID = "' . $product_id . '" ');
        $conv = self::db()->assoc($query)['conversion_amount'];
        return ($default_cost * $conv);
    }

    public static function get_product_links($product_id, $uom_id, $def_qty, $current_qty, $operation, $level = 0, $option_arr = array()) {
        static $initial_operation;
        if ($level == 0)
            $initial_operation = $operation;
        $link_arr = array();
        $link_query = self::db()->query('select * from tbl_products_link where main_product_id = "' . $product_id . '"');
        $count_row = self::db()->rowsno($link_query);
        if ($count_row == 0 && empty($option_arr)) {
            $link_arr[] = array("Quantity" => $def_qty, "current_quantity" => $current_qty, "Product_ID" => $product_id, "uom_id" => $uom_id
                , "operation" => $operation, 'is_main_prdct' => ($level == 0 ? 1 : 0), 'is_oprtn_inverse' => ($initial_operation != $operation));
        } else {
            if (!empty($count_row)) {
                $query = self::db()->query('select recipe_qty from tbl_stock_products where Product_ID = "' . $product_id . '"');
                $recipe_qty = self::db()->assoc($query)['recipe_qty'];
                if (empty($recipe_qty))
                    $recipe_qty = 1;
                while ($link = self::db()->assoc($link_query)) {
                    $new_link_arr = array();
                    $link_qty = $link['quantity'] * $def_qty;
                    $link_crnt_qty = ($link['current_quantity'] * $def_qty);
                    //since in product link we pt links depend on - [sales] here in inverse sign we must inverse them
                    $link_operation = $link['operation'];
                    if ($operation == '+')
                        $link_operation = ($link['operation'] == '+' ? '-' : '+');
                    if ($level == 0) {
                        $new_link_arr = self::get_product_links($link['sub_product_id'], $link['UOM_ID'], ($link_qty / $recipe_qty), ($link_crnt_qty / $recipe_qty), $link_operation, ++$level);
                    } else {
                        $new_link_arr[] = array("Quantity" => ($link_qty / $recipe_qty), "current_quantity" => ($link_crnt_qty / $recipe_qty),
                            "Product_ID" => $link['sub_product_id'], "uom_id" => $link['UOM_ID'], "operation" => $link_operation
                            , 'is_main_prdct' => 0, 'is_oprtn_inverse' => ($initial_operation != $link_operation));
                    }
                    $link_arr = array_merge($link_arr, $new_link_arr);
                }
            }
            if (!empty($option_arr)) {
//                echo"<pre>";die(print_r($option_arr));
                foreach ($option_arr as $option) {
                    $new_link_arr = array();
                    $default_op_qty = Product::getConvertedQuantity($option['uom_id'], $option['qty'], $option['product_id']);
                    $new_link_arr[] = array("Quantity" => $default_op_qty, "current_quantity" => $option['qty'],
                        "Product_ID" => $option['product_id'], "uom_id" => $option['uom_id'], "operation" => $operation
                        , 'is_main_prdct' => 0, 'is_oprtn_inverse' => false);
                    $link_arr = array_merge($link_arr, $new_link_arr);
                }
            }
        }
        return $link_arr;
    }

    public static function update_trans_record_current($main_type, $main_id) {
        self::db()->prepare_Stmt('update tbl_stock_product_transactions t 
            join tbl_stock_transactions_types y on y.Transaction_Type_ID = t.Transaction_Type_ID 
            set t.is_current = 0 where t.main_id = ? and y.main_type=? ', [$main_id, $main_type]);
    }

    public static function get_stock_trans_type($type = null) {
        $type_arr = [
            'inv' => 'Invoice',
            'pur' => 'Purchase',
            'rin' => 'Return Invoice',
            'rpr' => 'Return Purchase',
            'trf' => 'Transfer',
            'adj' => 'Adjust Stock',
            'dmg' => 'Damage',
            'asg' => 'Assign Quantity',
            'prd' => 'Produced Items',
            'raw' => 'Raw Materials',
            'mch' => 'Machine Maintenance',
            'dlc' => 'Customer Delivery Note',
            'dls' => 'Supplier Delivery Note',
            'sin' => 'Station Invoice',
            'con' => 'Consumption',
            'sad' => 'Stock Accounting Adjustment',
        ];
        if (!is_null($type))
            return $type_arr[$type];
        else
            return $type_arr;
    }

    public static function validate_expiry_qty_exist($prod_arr, $depot_arr) {
        $db = DBConnectorFactory::loadDriver();
        $validation_arr = array();
        $query = $db->query('select sum(Product_Quantity*if(Operation_Type="+",1,-1)) as qty,expiry_date,Product_ID,Depot_ID
            from tbl_stock_product_transactions 
            where Product_ID in (' . implode(',', array_keys($prod_arr['prod'])) . ')
                and Depot_ID in (' . implode(',', $depot_arr) . ') 
                    and expiry_date in ("' . implode('","', $prod_arr['expiry']) . '")
                        group by Product_ID,Depot_ID,expiry_date
            ');
        while ($row = $db->assoc($query)) {
            if (isset($prod_arr['prod'][$row['Product_ID']][$row['Depot_ID']][$row['expiry_date']])) {
//                $current_qty = $prod_arr['prod'][$row['Product_ID']][$row['Depot_ID']][$row['expiry_date']];
                if ($row['qty'] < 0)
                    $validation_arr[] = $row;
            }
        }
        return $validation_arr;
    }

    public static function validate_lot_qty_exist($prod_arr, $depot_arr) {
        $db = DBConnectorFactory::loadDriver();
        $validation_arr = array();
        $query = $db->query('select sum(Product_Quantity*if(Operation_Type="+",1,-1)) as qty,lot_nb,Product_ID,Depot_ID
            from tbl_stock_product_transactions 
            where Product_ID in (' . implode(',', array_keys($prod_arr['prod'])) . ')
                and Depot_ID in (' . implode(',', $depot_arr) . ') 
                    and lot_nb in (' . implode(',', $prod_arr['lot']) . ')
                        group by Product_ID,Depot_ID,lot_nb
            ');
        while ($row = $db->assoc($query)) {
            if (isset($prod_arr['prod'][$row['Product_ID']][$row['Depot_ID']][$row['lot_nb']])) {
//                $current_qty = $prod_arr['prod'][$row['Product_ID']][$row['Depot_ID']][$row['lot_nb']];
                if ($row['qty'] < 0)
                    $validation_arr[] = $row;
            }
        }
        return $validation_arr;
    }

    public static function validate_serial_qty_exist($prod_arr, $depot_arr) {
        $db = DBConnectorFactory::loadDriver();
        $validation_arr = array();
        $query = $db->query('select sum(Product_Quantity*if(Operation_Type="+",1,-1)) as qty,serial_nb,Product_ID,Depot_ID
            from tbl_stock_product_transactions 
            where Product_ID in (' . implode(',', array_keys($prod_arr['prod'])) . ')
                and Depot_ID in (' . implode(',', $depot_arr) . ') 
                    and serial_nb in (' . implode(',', $prod_arr['serial']) . ')
                        group by Product_ID,Depot_ID,serial_nb
            ');
        while ($row = $db->assoc($query)) {
            if (isset($prod_arr['prod'][$row['Product_ID']][$row['Depot_ID']][$row['serial_nb']])) {
//                $current_qty = $prod_arr['prod'][$row['Product_ID']][$row['Depot_ID']][$row['serial_nb']];
                if ($row['qty'] < 0)
                    $validation_arr[] = $row;
            }
        }
        return $validation_arr;
    }

    public static function validate_product_qty_exist($prod_arr, $depot_arr) {
//        echo"<pre>";die(print_r($prod_arr));
        $db = DBConnectorFactory::loadDriver();
 
        foreach(array_keys($prod_arr) as $pro_id){
            $query_pro = $db->query('select is_service  from tbl_stock_products where Product_ID = ' . $pro_id . ' ');
            $row_pro = $db->assoc($query_pro);
            if($row_pro['is_service'] == 1)
                unset($prod_arr[$pro_id]);
        }
        $validation_arr = array();
        if(!empty($prod_arr)){
            $query = $db->query('select sum(Product_Quantity*if(Operation_Type="+",1,-1)) as qty,Product_ID,Depot_ID
                from tbl_stock_product_transactions 
                where Product_ID in (' . implode(',', array_keys($prod_arr)) . ')
                    and Depot_ID in (' . implode(',', $depot_arr) . ') 
                            group by Product_ID,Depot_ID
                ');
            while ($row = $db->assoc($query)) {
                if (isset($prod_arr[$row['Product_ID']][$row['Depot_ID']])) {
    //                $current_qty = $prod_arr[$row['Product_ID']][$row['Depot_ID']];
                    //we did not rely on array quantity since already we update stock transactions
                    if ($row['qty'] < 0)
                        $validation_arr[] = $row;
                }
            }
        }
        return $validation_arr;
    }

    public static function close_invoices($close_arr, $record_id, $record_type, $row_data) {
        $db = DBConnectorFactory::loadDriver();
        foreach ($close_arr as $close) {
            $db->prepare_Stmt('insert into  tbl_invoice_payments (record_id,inv_pur_id,
                    Payment_Amount,type) values(?,?,?,?)', [$record_id, &$close['inv_id'],
                &$close['inv_amount'], $record_type]);
            $local_amount = $close['inv_amount'] * $row_data['rate'];
            $amount_inv = CurrencyFactory::returnExchangedValueBetweenTwoCurrencies(smc_session()['default_setting']['local_currency'], $close['inv_currency'], $local_amount);
            if ($record_type == 'rec' || $record_type == 'cmjv' || $record_type == 'cre') {
                $db->prepare_Stmt('update tbl_invoices 
                    set Payment_Paid_Amount = Payment_Paid_Amount + ?,is_main_transfer = 0 
                        where Invoice_ID_Auto = ?', [&$amount_inv, &$close['inv_id']]);
            } else {
                $db->prepare_Stmt('update tbl_inventory_purchases 
                    set Payment_Paid_Amount = Payment_Paid_Amount + ?
                        where Purchase_ID_Auto = ?', [&$amount_inv, &$close['inv_id']]);
            }
        }
    }

    public static function return_inv_pur_close_amount($record_type, $old_currency, $old_rate, $record_id) {
        $db = DBConnectorFactory::loadDriver();
        if ($record_type == 'rec' || $record_type == 'cmjv' || $record_type == 'cre') {
            $db->prepare_Stmt('update tbl_invoices i join tbl_invoice_payments p 
                                on i.Invoice_ID_Auto = p.inv_pur_id and p.type="' . $record_type . '"
                                SET Payment_Paid_Amount = Payment_Paid_Amount -(if(Invoice_Currency = "' . $old_currency . '",Payment_Amount,((Payment_Amount*"' . $old_rate . '")/current_rate)))
                                     ,is_main_transfer = 0
                                    where record_id ="' . $record_id . '" ');
            $db->prepare_Stmt("DELETE FROM tbl_invoice_payments  WHERE record_id  ='" . $record_id . "' and type='" . $record_type . "' ");
        } else {
            $db->prepare_Stmt('update tbl_inventory_purchases i  join tbl_invoice_payments p
                                on i.Purchase_ID_Auto = p.inv_pur_id  and p.type="' . $record_type . '"
                                SET Payment_Paid_Amount = Payment_Paid_Amount -(if(Currency_ID = "' . $old_currency . '",Payment_Amount,((Payment_Amount*"' . $old_rate . '")/current_rate)))
                                    where record_id ="' . $record_id . '" ');
            $db->prepare_Stmt("DELETE FROM tbl_invoice_payments  WHERE record_id  ='" . $record_id . "' and type='" . $record_type . "' ");
        }
    }

    public static function get_account_desc($main_type, $sub_type) {
        static $data = array();
        if (!isset($data[$main_type][$sub_type])) {
            if ($main_type == 'supp') {
                if ($sub_type == 'main') {
                    $data[$main_type][$sub_type] = CommonLanguage::getMessage('purchase_account');
                } else if ($sub_type == 'exp') {
                    $data[$main_type][$sub_type] = CommonLanguage::getMessage('expense_account');
                } else if ($sub_type == 'fa') {
                    $data[$main_type][$sub_type] = CommonLanguage::getMessage('fixed_asset_account');
                } else if ($sub_type == 'pur_vat') {
                    $data[$main_type][$sub_type] = CommonLanguage::getMessage('purchase_vat_account');
                } else if ($sub_type == 'fa_vat') {
                    $data[$main_type][$sub_type] = CommonLanguage::getMessage('fixed_asset_vat_account');
                } else if ($sub_type == 'exp_vat') {
                    $data[$main_type][$sub_type] = CommonLanguage::getMessage('expense_vat_account');
                }
            } else {
                if ($sub_type == 'main') {
                    $data[$main_type][$sub_type] = CommonLanguage::getMessage('sales_account');
                } else if ($sub_type == 'vat') {
                    $data[$main_type][$sub_type] = CommonLanguage::getMessage('vat_account');
                }
            }
        }
        return !empty($data[$main_type][$sub_type]) ? $data[$main_type][$sub_type] : '';
    }


    public static function getDrivers() {
        
        $driver_arr = array();
        $driver_query = self::db()->query('select * from tbl_drivers ');
        while ($row =self::db()->assoc($driver_query)) {
            $driver_arr[] = $row;
        }
        return $driver_arr;
    }

    public static function getAllBranch() {
        $branch_arr = array();
        $branch_query = self::db()->query('select * from tbl_organization_branches ');
        while ($row = self::db()->assoc($branch_query)) {
            $branch_arr[] = $row;
        }
        return $branch_arr;
    }


}
