<?php

class common_scripts {

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

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

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

    public function swap_local_second_rate() {

        $local_currency = smc_session()['default_setting']['local_currency'];
        $second_currency = smc_session()['default_setting']['second_currency'];
        $this->db->query('START TRANSACTION');
        //swap local and second currencies in default settings
        $this->db->prepare_Stmt('update tbl_default_settings set setting_value = ? 
            where setting_name="local_currency"', [&$second_currency]);
        $this->db->prepare_Stmt('update tbl_default_settings set setting_value = ? 
            where setting_name="second_currency"', [&$local_currency]);
        smc_session()['default_setting']['local_currency'] = $second_currency;
        smc_session()['default_setting']['second_currency'] = $local_currency;
//        echo smc_session()['default_setting']['local_currency'].'=='.smc_session()['default_setting']['second_currency'];
//        die();
        //swap rates in table transaction
        $this->db->query('ALTER TABLE `tbl_accounting_transactions` 
            ADD `temp_rate` DOUBLE NOT NULL DEFAULT "0" AFTER `value_date`;');
        $this->db->prepare_Stmt('update tbl_accounting_transactions 
            set temp_rate = Current_Ex_Rate');
        $this->db->prepare_Stmt('update tbl_accounting_transactions 
            set Current_Ex_Rate = second_currency_rate');
        $this->db->prepare_Stmt('update tbl_accounting_transactions 
            set second_currency_rate = temp_rate,local_amount=(Transaction_Amount*Current_Ex_Rate)');
        //swap rates in invoices
        $this->db->prepare_Stmt('update tbl_invoices i join tbl_accounting_transactions t
            on t.main_id = i.Invoice_ID_Auto and t.main_type = "inv" and t.Transaction_Reference="inv"
            set i.current_rate = t.Current_Ex_Rate,i.second_currency_rate=t.second_currency_rate
            where Invoice_Net_Total > 0');
        $query = $this->db->query('select * from tbl_invoices where Invoice_Net_Total=0');
        while ($row = $this->db->assoc($query)) {
            $local_rate = CurrencyFactory::getCurrencyTimeExchangeRate($row['Invoice_Currency'], smc_session()['default_setting']['local_currency'], $row['Invoice_Date']);
            $second_rate = CurrencyFactory::getCurrencyTimeExchangeRate($row['Invoice_Currency'], smc_session()['default_setting']['second_currency'], $row['Invoice_Date']);
            $this->db->prepare_Stmt('update tbl_invoices 
                set current_rate=?,second_currency_rate=? where Invoice_ID_Auto= ?', [&$local_rate, &$second_rate, &$row['Invoice_ID_Auto']]);
        }
        //swap rates in purchases
        $this->db->prepare_Stmt('update tbl_inventory_purchases i join tbl_accounting_transactions t
            on t.main_id = i.Purchase_ID_Auto and t.main_type = "pur" and t.Transaction_Reference="pur"
            set i.current_rate = t.Current_Ex_Rate,i.second_currency_rate=t.second_currency_rate
            where Purchase_Net_Total > 0');
        $query = $this->db->query('select * from tbl_inventory_purchases where Purchase_Net_Total=0');
        while ($row = $this->db->assoc($query)) {
            $local_rate = CurrencyFactory::getCurrencyTimeExchangeRate($row['Currency_ID'], smc_session()['default_setting']['local_currency'], $row['Purchase_Date']);
            $second_rate = CurrencyFactory::getCurrencyTimeExchangeRate($row['Currency_ID'], smc_session()['default_setting']['second_currency'], $row['Purchase_Date']);
            $this->db->prepare_Stmt('update tbl_inventory_purchases 
                set current_rate=?,second_currency_rate=? where Purchase_ID_Auto= ?', [&$local_rate, &$second_rate, &$row['Purchase_ID_Auto']]);
        }
        //swap rates in returns
        $this->db->prepare_Stmt('update tbl_accounting_return_invoices i join tbl_accounting_transactions t
            on t.main_id = i.Return_Invoice_ID and t.main_type in("rin","rpr") and t.Transaction_Reference="ret"
            set i.current_rate = t.Current_Ex_Rate,i.second_currency_rate=t.second_currency_rate
            where Return_Net_Total > 0');
        $query = $this->db->query('select * from tbl_accounting_return_invoices where Return_Net_Total=0');
        while ($row = $this->db->assoc($query)) {
            $local_rate = CurrencyFactory::getCurrencyTimeExchangeRate($row['Currency_ID'], smc_session()['default_setting']['local_currency'], $row['Return_Invoice_Date']);
            $second_rate = CurrencyFactory::getCurrencyTimeExchangeRate($row['Currency_ID'], smc_session()['default_setting']['second_currency'], $row['Return_Invoice_Date']);
            $this->db->prepare_Stmt('update tbl_accounting_return_invoices 
                set current_rate=?,second_currency_rate=? where Return_Invoice_ID= ?', [&$local_rate, &$second_rate, &$row['Return_Invoice_ID']]);
        }
        //swap rates in expenses
        $this->db->prepare_Stmt('update tbl_accounting_expenses i join tbl_accounting_transactions t
            on t.main_id = i.Expense_ID and t.main_type = "exp" 
            set i.current_rate = t.Current_Ex_Rate,i.second_currency_rate=t.second_currency_rate');
        //swap rates in payments/receipts
        $this->db->prepare_Stmt(' UPDATE `tbl_payment_receipt_details`  i join tbl_accounting_transactions t 
            on t.main_id = i.`pay_rec_id` and t.sub_id = i.payment_detail_id
             set i.second_currency_rate = t.second_currency_rate,
            i.current_rate = t.Current_Ex_Rate where main_type in("pay","rec")');
//        //swap rates in receipts
//        $this->db->prepare_Stmt(' UPDATE `tbl_payments_on_accounts`  i join tbl_accounting_transactions t 
//            on t.main_id = i.`Payment_ID`and t.current_year_id = i.`current_year_id` 
//            and t.is_actual = i.Status_ID set i.second_currency_rate = t.second_currency_rate,
//            i.current_rate = t.Current_Ex_Rate
//            where  i.`IsReceipt` = 1  and t.main_type = "rec"');
        $query = $this->db->query('select d.*,p.payment_date from tbl_payment_receipt_details d 
            join tbl_payment_receipt p on p.pay_rec_id = d.pay_rec_id where payment_amount=0');
        while ($row = $this->db->assoc($query)) {
            $local_rate = CurrencyFactory::getCurrencyTimeExchangeRate($row['currency_id'], smc_session()['default_setting']['local_currency'], $row['payment_date']);
            $second_rate = CurrencyFactory::getCurrencyTimeExchangeRate($row['currency_id'], smc_session()['default_setting']['second_currency'], $row['payment_date']);
            $this->db->prepare_Stmt('update tbl_payment_receipt_details 
                set current_rate=?,second_currency_rate=? where payment_detail_id= ?', [&$local_rate, &$second_rate, &$row['payment_detail_id']]);
        }
        //change local rate in stock transactions
        $query = $this->db->query('select * from tbl_stock_product_transactions ');
        while ($row = $this->db->assoc($query)) {
            $local_rate = CurrencyFactory::getCurrencyTimeExchangeRate($local_currency, smc_session()['default_setting']['local_currency'], $row['Transaction_Date']);
            $unit_cost = $row['Unit_Cost'] * ($local_rate == 0 ? 1 : $local_rate);
//            echo $local_currency.'=='.smc_session()['default_setting']['local_currency'].'=='.$local_rate.'==<br>';
            $this->db->prepare_Stmt('update tbl_stock_product_transactions 
                set Unit_Cost=? where Transaction_ID = ?', [&$unit_cost, &$row['Transaction_ID']]);
        }
        //change local rate in cost
        $query = $this->db->query('select *,date(date_time) as mydate
            from tbl_stock_products_costs ');
        while ($row = $this->db->assoc($query)) {
            $local_rate = CurrencyFactory::getCurrencyTimeExchangeRate($local_currency, smc_session()['default_setting']['local_currency'], $row['mydate']);
            $unit_cost = $row['Unit_Cost'] * ($local_rate == 0 ? 1 : $local_rate);
            $this->db->prepare_Stmt('update tbl_stock_products_costs 
                set Unit_Cost=? where Product_Cost_ID=?', [&$unit_cost, &$row['Product_Cost_ID']]);
        }
        //swap rates in credit/debit notes
        $this->db->prepare_Stmt(' UPDATE `tbl_accounting_debit_credit_note`  i join tbl_accounting_transactions t 
            on t.main_id = i.`Note_ID` set i.second_currency_rate = t.second_currency_rate,
            i.current_rate = t.Current_Ex_Rate
            where  t.main_type in ("deb","cre")');
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function delete_disabled_not_used_products() {
        $data = array();
        $product = new Product;
        $this->db->query('START TRANSACTION');
        $query = $this->db->query('select Product_ID,concat_ws(" - ",Product_Code,Product_Name) as name from tbl_stock_products where Status_ID = 0');
        while ($row = $this->db->assoc($query)) {
            $delete = $product->setData(['Product_ID' => $row['Product_ID']])->deleteProduct(true);
            if (!empty($delete))
                $data[] = $row['name'] . '<br>';
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
            return $data;
        } else {
            $this->db->query('ROLLBACK');
            return $error;
        }
    }

    public function correct_assign_product_cost() {
//        $query = $this->db->query('select c.*,ROUND((c.Unit_Cost/u.conversion_amount), 5)as new_cost,
//            t.Transaction_ID
//            from   tbl_stock_products_costs c
//        inner join `tbl_stock_product_transactions` t on c.Product_ID = t.`Product_ID` and c.Unit_Cost = t.`Unit_Cost` 
//        inner join tbl_uom_conversions u on t.Product_ID = u.Product_ID and t.UOM_ID = u.uom_from
//        where  u.conversion_amount <> 1 and `Transaction_Type_ID` = 10
//        ');
        $query = $this->db->query('
            select c.*, ROUND(t.Unit_Cost, 5) as new_cost from `tbl_stock_product_transactions` t 
inner join tbl_stock_products_costs c on t.`Product_ID` = c.Product_ID and t.date_time = c.date_time and t.`current_cost` = c.Unit_Cost
where `Depot_ID` = 1  
        ');
        while ($row = $this->db->assoc($query)) {
            $this->db->prepare_Stmt('update tbl_stock_products_costs set Unit_Cost = ? 
                where Product_Cost_ID = ?', [&$row['new_cost'], &$row['Product_Cost_ID']]);
            $error = $this->db->error();
            if (!empty($error)) {
                $this->db->clear_error();
                $this->db->prepare_Stmt('delete from  tbl_stock_products_costs 
                where Product_Cost_ID = ?', [&$row['Product_Cost_ID']]);
                $this->db->prepare_Stmt('update tbl_stock_products_costs set Unit_Cost = ? ,
                    remaining_quantity = remaining_quantity + ?
                where Product_ID = ? and Unit_Cost = ? and date_time = ?', [&$row['new_cost'], &$row['remaining_quantity'], &$row['Product_ID'], &$row['new_cost'], &$row['date_time']]);
            }
            $this->db->prepare_Stmt('update tbl_stock_product_transactions set Unit_Cost = ? 
                where Transaction_ID = ?', [&$row['new_cost'], &$row['Transaction_ID']]);
        }
    }

    public function correct_loc_qty_from_trans() {
        $this->db->prepare_Stmt('UPDATE tbl_stock_product_location l
        inner join (select Product_ID, Depot_ID, sum(Product_Quantity * if(Operation_Type="-",-1,+1)) as ttl_qty FROM tbl_stock_product_transactions group by Product_ID, Depot_ID) t 
        on  t.Product_ID = l.Location_Product_ID and t.Depot_ID = l.Product_Depot_ID
        set l.Product_Depot_Quantity = t.ttl_qty
        where l.Product_Depot_Quantity <> t.ttl_qty');
        $error = $this->db->error();
        return $error;
    }
    
    public function correct_main_prod_qty_from_loc(){
        $this->db->prepare_Stmt('UPDATE `tbl_stock_products` p 
        inner join (select sum(l.Product_Depot_Quantity) as qty, Location_Product_ID 
        from tbl_stock_product_location l group by Location_Product_ID) l
        on p.Product_ID = l.Location_Product_ID
        SET p.`Product_Quantity` = qty');
        $error = $this->db->error();
        return $error;
    }

//    update `Sheet1` s
//
//inner join tbl_stock_product_transactions t on s.`pid` = t.Product_ID and t.Transaction_Type_ID = 10 and `OpHx` = t.Item_Quantity_Current
//inner join tbl_stock_products_costs c on c.`Product_ID` = t.Product_ID and t.date_time = c.date_time and t.Unit_Cost = c.Unit_Cost
//inner join tbl_stock_products p on p.`Product_ID` = t.Product_ID  
//inner join tbl_uom_conversions u on u.`Product_ID` = t.Product_ID and u.uom_from = t.UOM_ID 
//inner join tbl_stock_product_location l on t.Product_ID = l.Location_Product_ID and t.Depot_ID = l.Product_Depot_ID
//
//set 
//t.Product_Quantity = t.Product_Quantity + ((s.Diff*-1) * u.conversion_amount)
//, t.Item_Quantity_Current = t.Item_Quantity_Current + (s.Diff*-1)
//, c.remaining_quantity = c.remaining_quantity + ((s.Diff*-1) * u.conversion_amount)
//, p.Product_Quantity = p.Product_Quantity + ((s.Diff*-1) * u.conversion_amount)
//, l.Product_Depot_Quantity = l.Product_Depot_Quantity + ((s.Diff*-1) * u.conversion_amount)


    public function correct_trans_types_current() {
        $this->db->query('START TRANSACTION');
        $last_record = array();
        $oprtn_typ_updt = array();
        $oprtn_typ_updt['-'] = array(11 => 43, 13 => 36, 18 => 39, 20 => 40, 23 => 41);
        $oprtn_typ_updt['+'] = array(12 => 35, 14 => 37, 16 => 38, 24 => 42);
        foreach ($oprtn_typ_updt as $opr => $rows) {
            foreach ($rows as $old_type => $new_type) {
                $this->db->prepare_Stmt('update tbl_stock_product_transactions 
                    set Transaction_Type_ID = ? where Transaction_Type_ID=? 
                    and Operation_Type=?', [$new_type, $old_type, $opr]);
            }
        }

        //
        $query = $this->db->query('select t.date_time,y.main_type,t.main_id
            from tbl_stock_product_transactions t 
            join tbl_stock_transactions_types y on y.Transaction_Type_ID = t.Transaction_Type_ID
            order by t.date_time,y.main_type,t.main_id');
        while ($row = $this->db->assoc($query)) {
            $last_record[$row['main_type']][$row['main_id']] = $row['date_time'];
        }
        $exist_records = $this->get_exist_record_ids();
        //
        $trans_type_arr = array(
            'inv' => 29, 'pur' => 30, 'mch' => 26, 'rin' => 27, 'rpr' => 28, 'trf' => 32, 'prd' => 33, 'raw' => 34
        );
        //
        $this->db->prepare_Stmt('update tbl_stock_product_transactions set is_current = 0');
        $edit_old_trns_types = implode(',', array_keys($oprtn_typ_updt['-'])) . ',' . implode(',', array_keys($oprtn_typ_updt['+']));
        foreach ($last_record as $main_type => $main_row) {
            foreach ($main_row as $main_id => $date_time) {
                $is_current = 0;
                $cond = '';
//                if (!in_array($main_id, $exist_records[$main_type])) {
//                    $new_type = $trans_type_arr[$main_type];
//                    $is_current = 0;
//                    $cond.=' t.Transaction_Type_ID = ?, ';
//                    $query_data = [$new_type, $is_current, $main_id, $date_time, $main_type];
//                } else {
//                    $is_current = 1;
//                    $query_data = [$is_current, $main_id, $date_time, $main_type];
//                }
//                $this->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 ' . $cond . ' is_current=?
//                    where t.main_id = ? and t.date_time=? and y.main_type=? 
//                    ', $query_data);
                $query_data = [ $main_id, $date_time, $main_type];
                //lsat type make it is current except for edit old qty
                $this->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 is_current=1
                        where t.main_id = ? and t.date_time=? and y.main_type=? 
                        and T.Transaction_Type_ID not in (' . $edit_old_trns_types . ')
                    ', $query_data);
                //update type to delete type
                if (!empty($exist_records[$main_type]) && !in_array($main_id, $exist_records[$main_type])) {
                    $query_data = [$new_type, $main_id, $date_time, $main_type];
                    $this->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.Transaction_Type_ID = ?
                            where t.main_id = ? and t.date_time=? and y.main_type=? 
                            and T.Transaction_Type_ID in (' . $edit_old_trns_types . ')
                        ', $query_data);
                }
            }
        }
        $this->db->prepare_Stmt('update tbl_stock_product_transactions set is_current = 1 where Transaction_Type_ID in(6,10)');

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

    public function get_exist_record_ids() {
        $data = array();
        $query = $this->db->query('select Invoice_ID_Auto from tbl_invoices');
        while ($row = $this->db->assoc($query)) {
            $data['inv'][] = $row['Invoice_ID_Auto'];
        }
        //
        $query = $this->db->query('SELECT `Purchase_ID_Auto` FROM `tbl_inventory_purchases`');
        while ($row = $this->db->assoc($query)) {
            $data['pur'][] = $row['Purchase_ID_Auto'];
        }
        //
        $query = $this->db->query('SELECT `Return_Invoice_ID`,Is_Customer_Invoice FROM `tbl_accounting_return_invoices`');
        while ($row = $this->db->assoc($query)) {
            $type = $row['Is_Customer_Invoice'] == 1 ? 'rin' : 'rpr';
            $data[$type][] = $row['Return_Invoice_ID'];
        }
        //
        $query = $this->db->query('SELECT `expense_id` FROM `tbl_product_expenses`');
        while ($row = $this->db->assoc($query)) {
            $data['mch'][] = $row['expense_id'];
        }
        //
        $query = $this->db->query('SELECT `damage_id` FROM `tbl_damage`');
        while ($row = $this->db->assoc($query)) {
            $data['dmg'][] = $row['damage_id'];
        }
        //
        $query = $this->db->query('SELECT `Request_ID` FROM `tbl_stock_request`');
        while ($row = $this->db->assoc($query)) {
            $data['trf'][] = $row['Request_ID'];
        }
        //
        $query = $this->db->query('SELECT `process_id` FROM `tbl_production_process`');
        while ($row = $this->db->assoc($query)) {
            $data['trf'][] = $row['process_id'];
        }
        return $data;
    }

    public function fill_pay_rec_total_amount() {
        $this->db->query('START TRANSACTION');
        $data = array();
        $query = $this->db->query('select d.currency_id,d.current_rate,d.payment_amount,
            d.pay_rec_id,p.Currency_ID as main_currency_id, p.current_rate as main_current_rate
            from tbl_payment_receipt_details d 
            join tbl_payment_receipt p on p.pay_rec_id = d.pay_rec_id  where p.total_amount=0');
        while ($row = $this->db->assoc($query)) {
            if (empty($data[$row['pay_rec_id']]))
                $data[$row['pay_rec_id']] = 0;
            $data[$row['pay_rec_id']]+=($row['payment_amount'] * $row['current_rate'] / $row['main_current_rate']);
        }
        if (!empty($data)) {
            foreach ($data as $pay_id => $pay_amount) {
                $this->db->prepare_Stmt('update tbl_payment_receipt set total_amount=? 
                where pay_rec_id=?', [$pay_amount, $pay_id]);
            }
        }
        $error = $this->db->error();
        if (empty($error))
            $this->db->query('COMMIT');
        else
            $this->db->query('ROLLBACK');
        return $error;
    }

//    set @nb=0;
//UPDATE tbl_sales_purchase sp 
//INNER JOIN 
//(
//    SELECT 
//    ss.*,
//    (@nb := (@nb + 1)) AS row_number
//    FROM tbl_sales_purchase ss 
//inner join tbl_accounting_return_invoices r on r.Return_Invoice_ID = ss.Invoice_ID_Auto
//    WHERE ss.is_return=1
//order by r.Return_Invoice_Date asc, sales_purchase_id_auto asc
//)AS t
//ON sp.sales_purchase_id_auto = t.sales_purchase_id_auto
//SET sp.sales_purchase_number = t.row_number;

    public function generate_inv_trans() {
        $this->db->query('START TRANSACTION');
        $query = $this->db->query('select i.*,c.Customer_Name from tbl_invoices i 
            join tbl_customers c on c.Customer_ID = i.Customer_ID
            join tbl_common_currency cu on cu.Currency_ID = i.Invoice_Currency
            where is_committed=0 ');
        while ($row = $this->db->assoc($query)) {
            $jv = JV::returnJV($row['is_actual']);
            $ref_num = 'Inv.' . $row['invoice_prefix'] . $row['Invoice_ID'];
            $desc = !empty($row['Invoice_Description']) ? $row['Invoice_Description'] : ($row['Customer_Name'] . ' bought products with ' . round(($row['Invoice_Net_Total'] + $row['Invoice_Shipping']), 2) . ' ' . $row['Currency_Symbol']);
            $data_trans = ["JV_ID" => $jv,
                "Transaction_Reference" => 'inv',
                "Transaction_Account" => $row['Account_ID'],
                "Transaction_Type" => "Credit",
                "Transaction_Amount" => $row['Invoice_Net_Total'],
                "Transaction_Date" => $row['Invoice_Date'],
                "Currency_ID" => $row['Invoice_Currency'],
                "Logged_ID" => $row['Logged_ID'],
                "created_logged_id" => $row['created_logged_id'],
                "Transaction_Description" => $desc,
                "date_created" => $row['date_created'],
                "date_time" => $row['date_time'],
                "Current_Ex_Rate" => $row['current_rate'],
                "second_currency_rate" => $row['second_currency_rate'],
                "main_id" => $row['Invoice_ID_Auto'],
                "main_type" => "inv",
                "branch_id" => $row['branch_id'],
                "due_date" => $row['Invoice_Due_Date'],
                "subtype" => "inv",
                "main_nb" => $row['Invoice_ID'],
                "prefix" => $row['invoice_prefix'],
                "user_ref" => '',
                "job_id" => $row['job_id'],
                "project_id" => $row['project_id'],
                "salesman_id" => $row['Sales_ID'],
            ];
            JV::addTransaction($data_trans, $row['is_actual']);
            $customer_acc = Customer::getCustomerAccount($row['Customer_ID'])['Account_ID'];
            $data_trans = ["JV_ID" => $jv,
                "Transaction_Reference" => $ref_num,
                "Transaction_Account" => $customer_acc,
                "Transaction_Type" => "Debit",
                "Transaction_Amount" => ($row['Invoice_Net_Total'] + $row['Invoice_Shipping'] + $row['currency_round']),
                "Transaction_Date" => $row['Invoice_Date'],
                "Currency_ID" => $row['Invoice_Currency'],
                "Logged_ID" => $row['Logged_ID'],
                "created_logged_id" => $row['created_logged_id'],
                "Transaction_Description" => $desc,
                "date_created" => $row['date_created'],
                "date_time" => $row['date_time'],
                "Current_Ex_Rate" => $row['current_rate'],
                "second_currency_rate" => $row['second_currency_rate'],
                "main_id" => $row['Invoice_ID_Auto'],
                "main_type" => "inv",
                "branch_id" => $row['branch_id'],
                "due_date" => $row['Invoice_Due_Date'],
                "subtype" => "inv",
                "main_nb" => $row['Invoice_ID'],
                "prefix" => $row['invoice_prefix'],
                "user_ref" => '',
                "job_id" => $row['job_id'],
                "project_id" => $row['project_id'],
                "salesman_id" => $row['Sales_ID'],
                "pos_customer_id" => $row['pos_customer_id']
            ];
            JV::addTransaction($data_trans, $row['is_actual']);
            $trans_disc_type = $row['currency_round'] > 0 ? 'Credit' : 'Debit';
            $data_disc = ["JV_ID" => $jv,
                "Transaction_Reference" => $ref_num,
                "Transaction_Account" => $row['Account_ID'],
                "Transaction_Type" => $trans_disc_type,
                "Transaction_Amount" => abs($row['currency_round']),
                "Transaction_Date" => $row['Invoice_Date'],
                "Currency_ID" => $row['Invoice_Currency'],
                "Logged_ID" => $row['Logged_ID'],
                "created_logged_id" => $row['created_logged_id'],
                "Transaction_Description" => $desc,
                "date_created" => $row['date_created'],
                "date_time" => $row['date_time'],
                "Current_Ex_Rate" => $row['current_rate'],
                "second_currency_rate" => $row['second_currency_rate'],
                "main_id" => $row['Invoice_ID_Auto'],
                "main_type" => "inv",
                "branch_id" => $row['branch_id'],
                "due_date" => $row['Invoice_Due_Date'],
                "subtype" => "inv",
                "main_nb" => $row['Invoice_ID'],
                "prefix" => $row['invoice_prefix'],
                "user_ref" => '',
                "job_id" => $row['job_id'],
                "project_id" => $row['project_id'],
                "salesman_id" => $row['Sales_ID'],
            ];
            JV::addTransaction($data_disc, $row['is_actual']);
            $customer_vat_acc = Customer::returnCustomerVATAccount($row['Customer_ID'])['Account_ID'];
            $dat_3 = ["JV_ID" => $jv,
                "Transaction_Reference" => $ref_num,
                "Transaction_Account" => (!empty(smc_session()['account_setting']['customer_fixed_vat_acc_id']) ? smc_session()['account_setting']['customer_fixed_vat_acc_id'] : $customer_vat_acc),
                "Transaction_Type" => "Credit",
                "Transaction_Amount" => $row['vat1_total'],
                "Transaction_Date" => $row['Invoice_Date'],
                "Currency_ID" => $row['Invoice_Currency'],
                "Logged_ID" => $row['Logged_ID'],
                "created_logged_id" => $row['created_logged_id'],
                "Transaction_Description" => $desc,
                "date_created" => $row['date_created'],
                "date_time" => $row['date_time'],
                "Current_Ex_Rate" => $row['current_rate'],
                "second_currency_rate" => $row['second_currency_rate'],
                "main_id" => $row['Invoice_ID_Auto'],
                "main_type" => "inv",
                "branch_id" => $row['branch_id'],
                "due_date" => $row['Invoice_Due_Date'],
                "main_nb" => $row['Invoice_ID'],
                "prefix" => $row['invoice_prefix'],
                "user_ref" => '',
                "job_id" => $row['job_id'],
                "project_id" => $row['project_id'],
                "salesman_id" => $row['Sales_ID'],
                "subtype" => "vat1"
            ];
            JV::addTransaction($dat_3, $row['is_actual']);
            $dat_3 = ["JV_ID" => $jv,
                "Transaction_Reference" => $ref_num,
                "Transaction_Account" => (!empty(smc_session()['account_setting']['customer_fixed_vat_acc_id']) ? smc_session()['account_setting']['customer_fixed_vat_acc_id'] : $customer_vat_acc),
                "Transaction_Type" => "Credit",
                "Transaction_Amount" => $row['vat2_total'],
                "Transaction_Date" => $row['Invoice_Date'],
                "Currency_ID" => $row['Invoice_Currency'],
                "Logged_ID" => $row['Logged_ID'],
                "created_logged_id" => $row['created_logged_id'],
                "Transaction_Description" => $desc,
                "date_created" => $row['date_created'],
                "date_time" => $row['date_time'],
                "Current_Ex_Rate" => $row['current_rate'],
                "second_currency_rate" => $row['second_currency_rate'],
                "main_id" => $row['Invoice_ID_Auto'],
                "main_type" => "inv",
                "branch_id" => $row['branch_id'],
                "due_date" => $row['Invoice_Due_Date'],
                "main_nb" => $row['Invoice_ID'],
                "prefix" => $row['invoice_prefix'],
                "user_ref" => '',
                "job_id" => $row['job_id'],
                "project_id" => $row['project_id'],
                "salesman_id" => $row['Sales_ID'],
                "subtype" => "vat2"
            ];
            JV::addTransaction($dat_3, $row['is_actual']);

            if ($row['is_invoice_total_on_shipping'] == 1 && !empty($row['shipping_company_id'])) {
                $dat_122 = ["JV_ID" => $jv,
                    "Transaction_Reference" => $ref_num,
                    "Transaction_Account" => $customer_acc,
                    "Transaction_Type" => "Credit",
                    "Transaction_Amount" => ($row['Invoice_Net_Total'] + $row['Invoice_Shipping']),
                    "Transaction_Date" => $row['Invoice_Date'],
                    "Currency_ID" => $row['Invoice_Currency'],
                    "Logged_ID" => $row['Logged_ID'],
                    "created_logged_id" => $row['created_logged_id'],
                    "Transaction_Description" => $desc,
                    "date_created" => $row['date_created'],
                    "date_time" => $row['date_time'],
                    "Current_Ex_Rate" => $row['current_rate'],
                    "second_currency_rate" => $row['second_currency_rate'],
                    "main_id" => $row['Invoice_ID_Auto'],
                    "main_type" => "inv",
                    "branch_id" => $row['branch_id'],
                    "due_date" => $row['Invoice_Due_Date'],
                    "main_nb" => $row['Invoice_ID'],
                    "prefix" => $row['invoice_prefix'],
                    "user_ref" => '',
                    "job_id" => $row['job_id'],
                    "project_id" => $row['project_id'],
                    "salesman_id" => $row['Sales_ID'],
                    "pos_customer_id" => $row['pos_customer_id']
                ];
                JV::addTransaction($dat_122, $row['is_actual']);
                $query_ship = $this->db->query('select main_account_id from tbl_shipping_company 
                    where shipping_company_id = "' . $row['shipping_company_id'] . '"');
                $ship_comp_acc_id = $this->db->assoc($query_ship)['main_account_id'];
                $dat_122 = ["JV_ID" => $jv,
                    "Transaction_Reference" => $ref_num,
                    "Transaction_Account" => $ship_comp_acc_id,
                    "Transaction_Type" => "Debit",
                    "Transaction_Amount" => $row['Invoice_Net_Total'],
                    "Transaction_Date" => $row['Invoice_Date'],
                    "Currency_ID" => $row['Invoice_Currency'],
                    "Logged_ID" => $row['Logged_ID'],
                    "created_logged_id" => $row['created_logged_id'],
                    "Transaction_Description" => $desc,
                    "date_created" => $row['date_created'],
                    "date_time" => $row['date_time'],
                    "Current_Ex_Rate" => $row['current_rate'],
                    "second_currency_rate" => $row['second_currency_rate'],
                    "main_id" => $row['Invoice_ID_Auto'],
                    "main_type" => "inv",
                    "branch_id" => $row['branch_id'],
                    "due_date" => $row['Invoice_Due_Date'],
                    "main_nb" => $row['Invoice_ID'],
                    "prefix" => $row['invoice_prefix'],
                    "user_ref" => '',
                    "job_id" => $row['job_id'],
                    "project_id" => $row['project_id'],
                    "salesman_id" => $row['Sales_ID'],
                ];
                JV::addTransaction($dat_122, $row['is_actual']);
                $dat_122 = ["JV_ID" => $jv,
                    "Transaction_Reference" => $ref_num,
                    "Transaction_Account" => $row['shipping_account_id'],
                    "Transaction_Type" => "Debit",
                    "Transaction_Amount" => $row['Invoice_Shipping'],
                    "Transaction_Date" => $row['Invoice_Date'],
                    "Currency_ID" => $row['Invoice_Currency'],
                    "Logged_ID" => $row['Logged_ID'],
                    "created_logged_id" => $row['created_logged_id'],
                    "Transaction_Description" => $desc,
                    "date_created" => $row['date_created'],
                    "date_time" => $row['date_time'],
                    "Current_Ex_Rate" => $row['current_rate'],
                    "second_currency_rate" => $row['second_currency_rate'],
                    "main_id" => $row['Invoice_ID_Auto'],
                    "main_type" => "inv",
                    "branch_id" => $row['branch_id'],
                    "due_date" => $row['Invoice_Due_Date'],
                    "main_nb" => $row['Invoice_ID'],
                    "prefix" => $row['invoice_prefix'],
                    "user_ref" => '',
                    "job_id" => $row['job_id'],
                    "project_id" => $row['project_id'],
                    "salesman_id" => $row['Sales_ID'],
                ];
                JV::addTransaction($dat_122, $row['is_actual']);
            }
            //customer transaction
            $remaining = $row['Invoice_Net_Total'] + $row['Invoice_Shipping'] - $row['Invoice_Paid_Amount'];
            $dat_122 = ["JV_ID" => $jv,
                "Transaction_Reference" => $ref_num,
                "Transaction_Account" => ($row['is_invoice_total_on_shipping'] == 0 ? $customer_acc : $ship_comp_acc_id),
                "Transaction_Type" => "Credit",
                "Transaction_Amount" => $row['Invoice_Paid_Amount'],
                "Transaction_Date" => $row['Invoice_Date'],
                "Currency_ID" => $row['Invoice_Currency'],
                "Logged_ID" => $row['Logged_ID'],
                "created_logged_id" => $row['created_logged_id'],
                "Transaction_Description" => $desc,
                "date_created" => $row['date_created'],
                "date_time" => $row['date_time'],
                "Current_Ex_Rate" => $row['current_rate'],
                "second_currency_rate" => $row['second_currency_rate'],
                "main_id" => $row['Invoice_ID_Auto'],
                "main_type" => "inv",
                "branch_id" => $row['branch_id'],
                "due_date" => $row['Invoice_Due_Date'],
                "main_nb" => $row['Invoice_ID'],
                "prefix" => $row['invoice_prefix'],
                "user_ref" => '',
                "job_id" => $row['job_id'],
                "project_id" => $row['project_id'],
                "salesman_id" => $row['Sales_ID'],
                "pos_customer_id" => ($row['is_invoice_total_on_shipping'] == 0 ? $row['pos_customer_id'] : 0)
            ];
            JV::addTransaction($dat_122, $row['is_actual']);
            $cash_query = $this->db->query('select cash_account_id 
                from tbl_users_accounting_accounts where User_ID = "' . $row['Logged_ID'] . '"');
            $cash_acc_id = $this->db->assoc($cash_query)['cash_account_id'];
            $dat_5 = ["JV_ID" => $jv,
                "Transaction_Reference" => 'cash',
                "Transaction_Account" => $cash_acc_id,
                "Transaction_Type" => "Debit",
                "Transaction_Amount" => $row['Invoice_Paid_Amount'],
                "Transaction_Date" => $row['Invoice_Date'],
                "Currency_ID" => $row['Invoice_Currency'],
                "Logged_ID" => $row['Logged_ID'],
                "created_logged_id" => $row['created_logged_id'],
                "Transaction_Description" => $desc,
                "date_created" => $row['date_created'],
                "date_time" => $row['date_time'],
                "Current_Ex_Rate" => $row['current_rate'],
                "second_currency_rate" => $row['second_currency_rate'],
                "main_id" => $row['Invoice_ID_Auto'],
                "main_type" => "inv",
                "branch_id" => $row['branch_id'],
                "due_date" => $row['Invoice_Due_Date'],
                "main_nb" => $row['Invoice_ID'],
                "prefix" => $row['invoice_prefix'],
                "user_ref" => '',
                "job_id" => $row['job_id'],
                "project_id" => $row['project_id'],
                "salesman_id" => $row['Sales_ID'],
            ];
            JV::addTransaction($dat_5, $row['is_actual']);
        }
         $this->db->prepare_Stmt('update tbl_invoices set is_committed=1,
             invoice_type=if(invoice_type=3,1,invoice_type) where is_committed=0  ');
         $error = $this->db->error();
         if(empty($error))
             $this->db->query('COMMIT');
         else
             $this->db->query('ROLLBACK');
         return $error;
    }

}
