<?php

class Station {

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

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

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

    public function submitBoneType() {
        $this->db->query('START TRANSACTION');
        $bone_type_id = $this->info['bone_type_id'];
        $services = json_decode($this->info['service_details'], true);
        if ($bone_type_id == 0) {
            $this->db->prepare_Stmt('INSERT INTO tbl_bone_type (bone_type_name,Account_ID,vat_account_id,service_account_id) VALUES(?,?,?,?)', [&$this->info['bone_type_name'], &$this->info['Account_ID'], &$this->info['vat_account_id'], &$this->info['service_account_id']]);

            $bone_type_id = $this->db->id();
        } else {
            $this->db->prepare_Stmt('Update  tbl_bone_type set bone_type_name = ?, Account_ID=?,vat_account_id=?,service_account_id = ? where bone_type_id= ?', [&$this->info['bone_type_name'], &$this->info['Account_ID'], &$this->info['vat_account_id'], &$this->info['service_account_id'], &$bone_type_id]);
            $this->db->prepare_Stmt('delete from tbl_bone_type_services where bone_type_id = ?', [&$bone_type_id]);
        }
        foreach ($services as $service) {
            $service['uom_id'] = Product::get_product_default_uom($service['product_id']);
            $this->db->prepare_Stmt('INSERT INTO tbl_bone_type_services 
                                    (product_id,uom_id,service_price,bone_type_id)
                                     VALUES(?,?,?,?)', [&$service['product_id'], &$service['uom_id'],
                &$service['service_price'], &$bone_type_id]);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function deleteBoneType() {
        $this->db->query('START TRANSACTION');
        $this->db->prepare_Stmt('Delete from tbl_bone_type_services WHERE bone_type_id = ?', [&$this->info['bone_type_id']]);
        $this->db->prepare_Stmt('Delete from tbl_bone_type WHERE bone_type_id = ?', [&$this->info['bone_type_id']]);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function submitPump() {
        if ($this->info['pump_id'] == 0) {
            $this->db->prepare_Stmt('INSERT INTO tbl_pump (pump_name,Product_ID) VALUES(?,?)', [&$this->info['pump_name'], &$this->info['Product_ID']]);
        } else {
            $this->db->prepare_Stmt('Update  tbl_pump set pump_name = ?, Product_ID=? where pump_id= ?', [&$this->info['pump_name'], &$this->info['Product_ID'], &$this->info['pump_id']]);
        }
        $error = $this->db->error();
        return $error;
    }

    public function deletePump() {
        $pump_query = $this->db->query('select * from  tbl_station_invoice_pump where pump_id = "' . $this->info['pump_id'] . '" limit 1');
        $pump_count = $this->db->rowsno($pump_query);
        if ($pump_count > 0) {
            $error = array();
            $error[0] = CommonLanguage::getMessage('unable_delete_pump');
            return $error;
        }
        $this->db->prepare_Stmt('Delete from tbl_pump WHERE pump_id = ?', [&$this->info['pump_id']]);
        $error = $this->db->error();
        return $error;
    }

    public function enableDisablePump() {
        $status_id = $this->info['status_id'] == 0 ? 1 : 0;
        $this->db->prepare_Stmt('UPDATE tbl_pump SET Status_ID = ? WHERE pump_id = ?', [&$status_id, &$this->info['pump_id']]);
        $error = $this->db->error();
        return $error;
    }

    public function get_pumps($pump_status = null) {
        $data = array();
        $class = smc_session()['pricing_class_id'];
        $query = $this->db->query('select p.*,pr.UOM_ID,pr.Product_Name,u.UOM_Name,pr.Product_VAT,
            pr.vat_percent,pr.is_service
            from tbl_pump p join tbl_stock_products pr on pr.Product_ID=p.Product_ID 
            join tbl_stock_uom u on u.UOM_ID = pr.UOM_ID ' . ($pump_status == 1 ? ' where p.Status_ID = 1' : ''));
        while ($row = $this->db->assoc($query)) {
            $row['old_counter'] = $this->get_pump_last_counter($row['pump_id']);
            $row['price'] = Product::get_product_price($row['Product_ID'], $class, $row['UOM_ID']);
            $data[] = $row;
        }
        return $data;
    }

    public function get_pump_last_counter($pump_id) {
        $counter = 0;
        $query = $this->db->query('select new_counter from tbl_station_invoice_pump p
            join tbl_station_invoice i on i.invoice_id = p.invoice_id
            where pump_id = "' . $pump_id . '" 
            order by i.invoice_date desc,i.invoice_id desc limit 1');
        $row = $this->db->assoc($query);
        if (!empty($row))
            $counter = $row['new_counter'];
        return $counter;
    }

    public function get_max_station_invoice($prefix, $is_actual = 0) {
        $yearid = $is_actual ? $this->year_id_actual : $this->year_id;
        $query = $this->db->query('SELECT MAX(Invoice_ID) AS Entry_ID 
            FROM tbl_station_invoice where 1=1 AND current_year_id = "' . $yearid . '" '
                . 'and invoice_prefix = "' . $prefix . '" and is_actual = "' . $is_actual . '"');
        $row = $this->db->assoc($query);
        if (!empty($row))
            $Invoice = $row['Entry_ID'] + 1;
        else
            $Invoice = 1;

        return $Invoice;
    }

    public function add_station_invoice() {
        $this->db->query('START TRANSACTION');
        $check = CommonFunctions::check_lock_data(['type' => 'station_invoice', 'start_date' => $this->info['invoice_date'], 'is_actual' => $this->info['is_actual']]);

//        $check = JV::checkCurrentYear($this->info['invoice_date'], $this->info['invoice_date'], $this->info['is_actual']);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $depot_id = $this->info['depot_id'];
        $branch_id = isset($this->info['branch_id']) ? $this->info['branch_id'] : CommonBusiness::getBranchByDepot($depot_id);
        $query_branch = $this->db->query('select branch_prefix from tbl_organization_branches where branch_id = "' . $branch_id . '"');
        $branch_prefix = $this->db->assoc($query_branch)['branch_prefix'];
        $invoice_prefix = smc_session()['user_accounts']['serial_prefix'] ? smc_session()['user_accounts']['serial_prefix'] : $branch_prefix;
        $mydate = date("Y-m-d H:i:s");
        $year_id = $this->info['is_actual'] ? $this->year_id_actual : $this->year_id;
        $rate = !empty($this->info['current_rate']) ? $this->info['current_rate'] : CurrencyFactory::getLastExRate($this->info['currency_id']);

        $second_rate = CurrencyFactory::getSecondCurrencyLastExRate($this->info['currency_id']);
        $logged_id = smc_session()['user_logged_id'];
        $class_id = smc_session()['pricing_class_id'];
        $today = Date::getToday();
        $inv_num = self::get_max_station_invoice($invoice_prefix, $this->info['is_actual']);
        $data = [&$inv_num, &$invoice_prefix, &$this->info['customer_id'], &$this->info['invoice_date'], &$this->info['invoice_vat'],
            &$this->info['overall_total'], &$this->info['currency_id'], $this->info['depot_id'], &$branch_id,
            &$rate, &$this->info['job_id'], &$this->info['description'], &$logged_id, &$logged_id, &$mydate, &$mydate,
            &$this->info['is_actual'], &$year_id, &$class_id, &$this->info['employee_id'],
            &$this->info['account_id'], &$this->info['cash_difference'], &$second_rate];
        $this->db->prepare_Stmt('INSERT INTO `tbl_station_invoice`( `invoice_nb`, `invoice_prefix`,
            `customer_id`, `invoice_date`,  `invoice_vat`, `net_total`, `currency_id`, `depot_id`,
            `branch_id`, `current_rate`, `job_id`, `description`, `logged_id`, `created_logged_id`, `date_time`,
            `date_created`, `is_actual`, `current_year_id`, `class_id`,employee_id,Account_ID,cash_difference_amount,second_currency_rate) 
            values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', $data);
        $invoice_id = $this->db->id();
        $prod_ids_arr = array();
        $prod_arr = array();
        $prod_trans_arr = array();
        $prod_str = '';


        if (!empty($this->info['pumps'])) {
            foreach ($this->info['pumps'] as $pump) {
                $prod_ids_arr[] = $pump['product_id'];
                $default_qty = Product::getConvertedQuantity($pump['uom_id'], $pump['diff'], $pump['product_id']);
                $prod_arr[] = array("product_id" => $pump['product_id'],
                    "total_amount" => ($pump['total'] - $pump['pump_vat']),
                    "description" => '', "vat_amount" => $pump['pump_vat']);
                $pump['is_service'] = !empty($pump['is_service']) ? $pump['is_service'] : 0;
                $costdata = [
                    "Product_ID" => $pump['product_id'],
                    "Quantity" => $default_qty,
                    "Unit_Cost" => "",
                    "Currency_ID" => $this->info['currency_id'],
                    "date_time" => $mydate,
                    "current_rate" => $rate,
                    "depot_id" => $depot_id,
                    "trans_date" => $today,
                    "current_quantity" => $pump['diff'],
                    "uom_id" => $pump['uom_id'],
                    "is_actual" => $this->info['is_actual'],
                    "expiry_date" => null,
                    "lot_nb" => '',
                    "serial_nb" => '',
                    "main_id" => $invoice_id,
                    "is_service" => $pump['is_service'],
                    "main_nb" => $invoice_prefix . $inv_num,
                    "customer_id" => $this->info['customer_id']
                ];
                $cost = self::updateLinkQuantities($costdata, "add_station_invoice", '-');
                $pump_data = [&$invoice_id, &$pump['pump_id'], &$pump['old_counter'], &$pump['new_counter'], &$pump['diff'],
                    &$pump['product_id'], &$pump['uom_id'], &$pump['pump_unit_price'], &$pump['total'], &$pump['pump_vat_percent'],
                    &$pump['pump_vat'], &$default_qty, &$cost];
                $this->db->prepare_Stmt('INSERT INTO `tbl_station_invoice_pump`( `invoice_id`, `pump_id`,
                `old_counter`, `new_counter`, `counter_diff`, `product_id`, `uom_id`, `unit_price`, `total_price`,
                `vat_percent`, `vat_amount`, `default_quantity`,unit_cost) values(?,?,?,?,?,?,?,?,?,?,?,?,?)', $pump_data);
            }
        }

        if (!empty($this->info['product'])) {

            foreach ($this->info['product'] as $pump) {
                $prod_ids_arr[] = $pump['product_id'];
                $default_qty = Product::getConvertedQuantity($pump['uom_id'], $pump['qty'], $pump['product_id']);
                $disc_amount = $pump['qty'] * $pump['unit_price'] * $pump['disc'] / 100;
                $pump['is_service'] = !empty($pump['is_service']) ? $pump['is_service'] : 0;

                $costdata = [
                    "Product_ID" => $pump['product_id'],
                    "Quantity" => $default_qty,
                    "Unit_Cost" => "",
                    "Currency_ID" => $this->info['currency_id'],
                    "date_time" => $mydate,
                    "current_rate" => $rate,
                    "depot_id" => $depot_id,
                    "trans_date" => $today,
                    "current_quantity" => $pump['qty'],
                    "uom_id" => $pump['uom_id'],
                    "is_actual" => $this->info['is_actual'],
                    "expiry_date" => null,
                    "lot_nb" => '',
                    "serial_nb" => '',
                    "main_id" => $invoice_id,
                    "is_service" => $pump['is_service'],
                    "main_nb" => $invoice_prefix . $inv_num,
                    "customer_id" => $this->info['customer_id']
                ];
                $cost = self::updateLinkQuantities($costdata, "add_station_invoice", '-');
                $pump_data = [&$invoice_id, &$pump['product_id'], &$pump['unit_price'], &$pump['qty'], &$pump['uom_id'],
                    &$default_qty, &$pump['disc'], &$disc_amount, &$pump['total'], &$pump['vat_percent'],
                    &$pump['vat'], &$cost, &$pump['desc']];
                $this->db->prepare_Stmt('INSERT INTO `tbl_station_invoice_products`(`invoice_id`, `product_id`,
                `unit_price`, `current_quantity`, `uom_id`, `default_quantity`, `discount_percent`, `discount_amount`,
                `total_price`, `vat_percent`, `vat_amount`, `unit_cost`, `description`) values(?,?,?,?,?,?,?,?,?,?,?,?,?)', $pump_data);
                $prod_arr[] = array("product_id" => $pump['product_id'],
                    "total_amount" => ($pump['total'] - $pump['vat']),
                    "description" => '', "vat_amount" => $pump['vat']);
            }
        }

        $sales_account = empty($this->info['account_id']) ? smc_session()['user_accounts']['sales_account_id'] : $this->info['account_id'];
        if (!empty($prod_arr)) {
            $prod_str =implode( ',',$prod_ids_arr);
            $prod_trans_arr = CommonBusiness::get_item_acc_transactions($prod_arr, $prod_str, 'sin', $sales_account);
        }

        $jv = JV::returnJV($this->info['is_actual']);
        $ref_num = 'SInv.' . $invoice_prefix . $inv_num;
        $customerAccount = Customer::getCustomerAccount($this->info['customer_id'])['Account_ID'];
        foreach ($prod_trans_arr as $trans) {
            if (isset($trans['main'])) {
                $trans_main = $trans['main'];
                $data_trans = ["JV_ID" => $jv,
                    "Transaction_Reference" => (empty($trans_main['ref']) ? 'sin' : $trans_main['ref']),
                    "Transaction_Account" => $trans_main['trans_account'],
                    "Transaction_Type" => "Credit",
                    "Transaction_Amount" => $trans_main['trans_amount'],
                    "Transaction_Date" => $today,
                    "Currency_ID" => $this->info['currency_id'],
                    "Logged_ID" => $logged_id,
                    "created_logged_id" => $logged_id,
                    "Transaction_Description" => (empty($trans_main['trans_desc']) ? $this->info['description'] : $trans_main['trans_desc']),
                    "Current_Ex_Rate" => $rate,
                    "second_currency_rate" => $second_rate,
                    "main_id" => $invoice_id,
                    "main_type" => "sin",
                    "branch_id" => $branch_id,
                    "due_date" => $this->info['invoice_date'],
                    "subtype" => "sin",
                    "main_nb" => $inv_num,
                    "prefix" => $invoice_prefix,
                    "job_id" => $this->info['job_id']
                ];
                JV::addTransaction($data_trans, $this->info['is_actual']);
            }
        }

        $data_trans = ["JV_ID" => $jv,
            "Transaction_Reference" => $ref_num,
            "Transaction_Account" => $customerAccount,
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => ($this->info['pump_total'] + $this->info['prod_total'] - $this->info['bon_total'] + $this->info['service_total']) - $this->info['cash_difference'],
            "Transaction_Date" => $today,
            "Currency_ID" => $this->info['currency_id'],
            "Logged_ID" => $logged_id,
            "created_logged_id" => $logged_id,
            "Transaction_Description" => $this->info['description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $invoice_id,
            "main_type" => "sin",
            "branch_id" => $branch_id,
            "due_date" => $this->info['invoice_date'],
            "main_nb" => $inv_num,
            "prefix" => $invoice_prefix,
            "job_id" => $this->info['job_id']
        ];
        JV::addTransaction($data_trans, $this->info['is_actual']);
        $data_trans = ["JV_ID" => $jv,
            "Transaction_Reference" => $ref_num,
            "Transaction_Account" => $customerAccount,
            "Transaction_Type" => "Credit",
            "Transaction_Amount" => ($this->info['pump_total'] + $this->info['prod_total'] - $this->info['bon_total'] + $this->info['service_total']) - $this->info['cash_difference'],
            "Transaction_Date" => $today,
            "Currency_ID" => $this->info['currency_id'],
            "Logged_ID" => $logged_id,
            "created_logged_id" => $logged_id,
            "Transaction_Description" => $this->info['description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $invoice_id,
            "main_type" => "sin",
            "branch_id" => $branch_id,
            "due_date" => $this->info['invoice_date'],
            "main_nb" => $inv_num,
            "prefix" => $invoice_prefix,
            "job_id" => $this->info['job_id']
        ];
        JV::addTransaction($data_trans, $this->info['is_actual']);
        $data_trans = ["JV_ID" => $jv,
            "Transaction_Reference" => $ref_num,
            "Transaction_Account" => smc_session()['user_accounts']['cash_account_id'],
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => ($this->info['pump_total'] + $this->info['prod_total'] - $this->info['bon_total'] + $this->info['service_total']) - $this->info['cash_difference'],
            "Transaction_Date" => $today,
            "Currency_ID" => $this->info['currency_id'],
            "Logged_ID" => $logged_id,
            "created_logged_id" => $logged_id,
            "Transaction_Description" => $this->info['description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $invoice_id,
            "main_type" => "sin",
            "branch_id" => $branch_id,
            "due_date" => $this->info['invoice_date'],
            "main_nb" => $inv_num,
            "prefix" => $invoice_prefix,
            "job_id" => $this->info['job_id']
        ];
        JV::addTransaction($data_trans, $this->info['is_actual']);
        if (!empty($this->info['coupon'])) {
            foreach ($this->info['coupon'] as $coupon) {
                $coupon['uom_id'] = Product::get_product_default_uom($coupon['product_id']);
                $default_qty = Product::getConvertedQuantity($coupon['uom_id'], $coupon['liters'], $coupon['product_id']);

                $coupon_acc = Station::get_coupon_account($coupon['bone_type_id']);
                $account_id = $coupon_acc['Account_ID'];
                $vat_account_id = $coupon_acc['vat_account_id'];
                $service_account_id = !empty($coupon_acc['service_account_id']) ? $coupon_acc['service_account_id'] : $this->info['account_id'];
                $data_coupon = [&$invoice_id, &$coupon['bone_type_id'], &$coupon['product_id'], &$coupon['liters'],
                    &$coupon['uom_id'], &$default_qty, &$coupon['unit_price'], &$coupon['total'], &$coupon['vat_percent'],
                    &$coupon['vat'], &$account_id, &$vat_account_id, &$coupon['service']];
                $this->db->prepare_Stmt('INSERT INTO `tbl_station_invoice_bone`( `invoice_id`, `bon_type_id`, `product_id`,
                `current_quantity`, `uom_id`, `default_quantity`, `unit_price`, `total_price`, `vat_percent`,
                `vat_amount`,account_id,vat_account_id,service_price) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)', $data_coupon);
                $data_trans = ["JV_ID" => $jv,
                    "Transaction_Reference" => $ref_num,
                    "Transaction_Account" => $account_id,
                    "Transaction_Type" => "Debit",
                    "Transaction_Amount" => $coupon['total'],
                    "Transaction_Date" => $today,
                    "Currency_ID" => $this->info['currency_id'],
                    "Logged_ID" => $logged_id,
                    "created_logged_id" => $logged_id,
                    "Transaction_Description" => $this->info['description'],
                    "Current_Ex_Rate" => $rate,
                    "second_currency_rate" => $second_rate,
                    "main_id" => $invoice_id,
                    "main_type" => "sin",
                    "branch_id" => $branch_id,
                    "due_date" => $this->info['invoice_date'],
                    "main_nb" => $inv_num,
                    "prefix" => $invoice_prefix,
                    "job_id" => $this->info['job_id']
                ];
                JV::addTransaction($data_trans, $this->info['is_actual']);
                $data_trans = ["JV_ID" => $jv,
                    "Transaction_Reference" => $ref_num,
                    "Transaction_Account" => $vat_account_id,
                    "Transaction_Type" => "Credit",
                    "Transaction_Amount" => $coupon['vat'],
                    "Transaction_Date" => $today,
                    "Currency_ID" => $this->info['currency_id'],
                    "Logged_ID" => $logged_id,
                    "created_logged_id" => $logged_id,
                    "Transaction_Description" => $this->info['description'],
                    "Current_Ex_Rate" => $rate,
                    "second_currency_rate" => $second_rate,
                    "main_id" => $invoice_id,
                    "main_type" => "sin",
                    "branch_id" => $branch_id,
                    "due_date" => $this->info['invoice_date'],
                    "main_nb" => $inv_num,
                    "prefix" => $invoice_prefix,
                    "job_id" => $this->info['job_id']
                ];
                JV::addTransaction($data_trans, $this->info['is_actual']);
                $data_trans = ["JV_ID" => $jv,
                    "Transaction_Reference" => $ref_num,
                    "Transaction_Account" => $service_account_id,
                    "Transaction_Type" => "Credit",
                    "Transaction_Amount" => $coupon['service'],
                    "Transaction_Date" => $today,
                    "Currency_ID" => $this->info['currency_id'],
                    "Logged_ID" => $logged_id,
                    "created_logged_id" => $logged_id,
                    "Transaction_Description" => $this->info['description'],
                    "Current_Ex_Rate" => $rate,
                    "second_currency_rate" => $second_rate,
                    "main_id" => $invoice_id,
                    "main_type" => "sin",
                    "branch_id" => $branch_id,
                    "due_date" => $this->info['invoice_date'],
                    "main_nb" => $inv_num,
                    "prefix" => $invoice_prefix,
                    "job_id" => $this->info['job_id']
                ];
                JV::addTransaction($data_trans, $this->info['is_actual']);
            }
        }
        $vat_account = Customer::returnCustomerVATAccount($this->info['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'] : $vat_account),
            "Transaction_Type" => "Credit",
            "Transaction_Amount" => ($this->info['pump_vat'] + $this->info['prod_vat']),
            "Transaction_Date" => $today,
            "Currency_ID" => $this->info['currency_id'],
            "Logged_ID" => $logged_id,
            "created_logged_id" => $logged_id,
            "Transaction_Description" => $this->info['description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $invoice_id,
            "main_type" => "sin",
            "branch_id" => $branch_id,
            "due_date" => $this->info['invoice_date'],
            "main_nb" => $inv_num,
            "prefix" => $invoice_prefix,
            "job_id" => $this->info['job_id']
        ];
        JV::addTransaction($dat_3, $this->info['is_actual']);
        $user_discount_account = !empty(smc_session()['user_accounts']['discount_account']) ? smc_session()['user_accounts']['discount_account'] : $this->info['account_id'];
        $dat_4 = ["JV_ID" => $jv,
            "Transaction_Reference" => $ref_num,
            "Transaction_Account" => $user_discount_account,
            "Transaction_Type" => $this->info['cash_difference'] < 0 ? "Credit" : "Debit",
            "Transaction_Amount" => $this->info['cash_difference'],
            "Transaction_Date" => $today,
            "Currency_ID" => $this->info['currency_id'],
            "Logged_ID" => $logged_id,
            "created_logged_id" => $logged_id,
            "Transaction_Description" => $this->info['description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $invoice_id,
            "main_type" => "sin",
            "branch_id" => $branch_id,
            "due_date" => $this->info['invoice_date'],
            "main_nb" => $inv_num,
            "prefix" => $invoice_prefix,
            "job_id" => $this->info['job_id']
        ];
        JV::addTransaction($dat_4, $this->info['is_actual']);
        //        $error = $this->db->error();
        //        die(print_r($error));
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
            return [$invoice_id, ($invoice_prefix . $inv_num)];
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public static function get_coupon_account($cid) {
        $db = DBConnectorFactory::loadDriver();
        $query = $db->query('select Account_ID,vat_account_id,service_account_id from tbl_bone_type where bone_type_id = "' . $cid . '"');
        $row = $db->assoc($query);
        return $row;
    }

    public 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']];
            $this->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);
            //        echo $operation.'<br>';
            //        $sub_qty = $data['Quantity'] ;
            $dep_dat = [&$data['depot_id'], &$data['Product_ID'], &$sub_qty, &$data['Quantity']];
            //        echo $operation.' =='.$data['Quantity'].'<br>';
            //        die();
            $this->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 function get_station_invoice($id) {
        $data = array();
        $total_pump = $total_coupon = $total_prod = $overall_total = $total_cash = $total_vat = 0;
        $query = $this->db->query('SELECT i.`invoice_id`, i.`invoice_nb`, i.`invoice_prefix`, i.`customer_id`, i.`invoice_date`,
            i.`subtotal`, i.`invoice_vat`, i.`net_total`, i.`currency_id`, i.`depot_id`, i.`branch_id`, i.`current_rate`, i.`job_id`,
            i.`description`, i.`logged_id`, i.`created_logged_id`, i.`date_time`, i.`date_created`, i.`is_actual`,
            i.`current_year_id`, i.`class_id`, i.`employee_id`,c.Customer_Name, cu.Currency_Name,cu.Currency_Symbol,
            cu.Currency_Code,d.Depot_Name,b.branch_name, j.job_name,coa.Account_Code,coa.Account_Name,i.Account_ID,
            CONCAT(e.Employee_FirstName, " ", e.Employee_MiddleName, " ", e.Employee_LastName) AS Employee_Name,i.cash_difference_amount
            FROM `tbl_station_invoice` i join tbl_customers c on c.Customer_ID = i.customer_id 
            join tbl_common_currency cu on cu.Currency_ID = i.currency_id
            join tbl_stock_depot_warehouse d on d.Depot_ID = i.depot_id
            join tbl_organization_branches b on b.branch_id = i.branch_id
            left join tbl_hrm_employee e on e.Employee_ID = i.employee_id
            left join tbl_accounting_jobs j on j.job_id = i.job_id
            join tbl_chart_of_accounts coa on coa.Account_ID = i.Account_ID
            WHERE invoice_id = "' . $id . '"');
        $data['main'] = $this->db->assoc($query);

        $query_pump = $this->db->query('select p.`entry_id`, p.`invoice_id`, p.`pump_id`, p.`old_counter`, p.`new_counter`,
            p.`counter_diff`, p.`product_id`, p.`uom_id`, p.`unit_price`, p.`total_price`, p.`vat_percent`, p.`vat_amount`,
            p.`default_quantity`,pr.Product_Name,u.UOM_Name,pu.pump_name,pr.is_service from tbl_station_invoice_pump p
            join tbl_stock_products pr on pr.Product_ID = p.product_id
            join tbl_stock_uom u on u.UOM_ID = p.uom_id
            join tbl_pump pu on pu.pump_id = p.pump_id
            where p.invoice_id = "' . $id . '"');
        while ($row = $this->db->assoc($query_pump)) {
            $data['pump'][$row['pump_id']] = $row;
            $total_vat+=$row['vat_amount'];
            $total_pump+=($row['counter_diff'] * $row['unit_price']) + $row['vat_amount'];
        }
        $query_coupon = $this->db->query('SELECT d.`entry_id`, d.`invoice_id`, d.`bon_type_id`, d.`product_id`,
            d.`current_quantity`, d.`uom_id`, d.`default_quantity`, d.`unit_price`, d.`total_price`, d.`vat_percent`,
            d.`vat_amount` ,p.Product_Code,p.Product_Name,b.bone_type_name,d.service_price,p.is_service
            FROM `tbl_station_invoice_bone` d join tbl_stock_products p on p.Product_ID = d.product_id
            join tbl_bone_type b on b.bone_type_id = d.bon_type_id
            WHERE d.invoice_id = "' . $id . '"');
        while ($row = $this->db->assoc($query_coupon)) {
            $data['coupon'][] = $row;
            $total_vat+=$row['vat_amount'];

            $total_coupon+=($row['current_quantity'] * $row['unit_price']) + $row['vat_amount'];
        }
        $query_prod = $this->db->query('SELECT d.`entry_id`, d.`invoice_id`, d.`product_id`, d.`unit_price`,
            d.`current_quantity`, d.`uom_id`, d.`default_quantity`, d.`discount_percent`, d.`discount_amount`, d.`total_price`,
            d.`vat_percent`, d.`vat_amount`, d.`unit_cost`, d.`description` ,p.Product_Name,
            p.Product_Code,u.UOM_Name,p.is_service
            FROM `tbl_station_invoice_products` d join tbl_stock_products p on p.Product_ID = d.product_id
            join tbl_stock_uom u on u.UOM_ID = d.UOM_ID
            WHERE d.invoice_id = "' . $id . '"');
        while ($row = $this->db->assoc($query_prod)) {
            $data['products'][] = $row;
            $total_vat+=$row['vat_amount'];
            $total_prod = ($row['current_quantity'] * $row['unit_price'] * (100 - $row['discount_percent']) / 100) + $row['vat_amount'];
        }
        $data['main']['total_pump'] = $total_pump;
        $data['main']['total_coupon'] = $total_coupon;
        $data['main']['total_prod'] = $total_prod;
        $data['main']['overall_total'] = $total_prod + $total_coupon + $total_pump;
        $data['main']['total_vat'] = $total_vat;
        $data['main']['total_cash'] = $total_pump + $total_prod - $total_coupon;
        return $data;
    }

    public function addCustomerCoupon() {
        $is_actual = isset($this->info['is_actual']) ? $this->info['is_actual'] : 0;
        //        $this->info['Invoice_Date'] = date('Y-m-d');
        $logged_id = smc_session()['user_logged_id'];
        $check = CommonFunctions::check_lock_data(['type' => 'customer_coupon', 'start_date' => $this->info['invoice_date'], 'is_actual' => $is_actual]);

//        $check = JV::checkCurrentYear($this->info['invoice_date'], $this->info['invoice_date'], $is_actual);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $depot_id = $this->info['depot_id'];
        $branch_id = isset($this->info['branch_id']) ? $this->info['branch_id'] : CommonBusiness::getBranchByDepot($depot_id);

        $query_branch = $this->db->query('select branch_prefix from tbl_organization_branches where branch_id = "' . $branch_id . '"');
        $branch_prefix = $this->db->assoc($query_branch)['branch_prefix'];
        $invoice_prefix = smc_session()['user_accounts']['serial_prefix'] ? smc_session()['user_accounts']['serial_prefix'] : $branch_prefix;

        //customer name and code if ($Customer_ID == 0) "Customer_Name", Customer_Account, CurrencySymbole
        //$customerAccount = 53 name = customer ;,, currency symbol 
        //status:
        //0->unpaid
        //1->partially_paid
        //2->paid
        $Customer_ID = $this->info['customer_id'];
        $inv_num = self::getMaxCustomerCoupon($invoice_prefix, $is_actual);

        $Invoice_Sub_Total = $this->info['Sub_Total']; // check
        $Invoice_Net_Total = $this->info['Net_Total']; // check

        $Invoice_VAT = $this->info['invoice_vat'];

        $Invoice_Currency = $this->info['currency_id'];
        $mydate = date("Y-m-d H:i:s");
        $paid = $this->info['Card_Amount'] + $this->info['Cash_Amount'] + $this->info['Check_Amount'];
        //        $remaining = $Invoice_Net_Total - $paid;
        $second_rate = CurrencyFactory::getSecondCurrencyLastExRate($Invoice_Currency);
        $rate = !empty($this->info['current_rate']) ? $this->info['current_rate'] : CurrencyFactory::getLastExRate($Invoice_Currency);

        $this->db->query('START TRANSACTION');
        $this->info['description'] = !empty($this->info['description']) ? $this->info['description'] : '';
        $year_id = $is_actual ? $this->year_id_actual : $this->year_id;
        $class_id = smc_session()['pricing_class_id'];
        $data = [&$inv_num, &$invoice_prefix, &$Customer_ID, &$Invoice_Currency, &$Invoice_VAT,
            &$Invoice_Sub_Total, &$Invoice_Net_Total, &$paid, &$mydate, &$mydate, &$logged_id, &$logged_id, &$year_id,
            &$this->info['description'], &$depot_id, &$branch_id, &$rate, &$class_id, &$this->info['bone_type_id'],
            &$is_actual, &$this->info['job_id'], &$this->info['invoice_date'], &$second_rate,
            &$this->info['discount_amount'], &$this->info['discount_percent']];
        $this->db->prepare_Stmt("INSERT INTO tbl_customer_coupon (
            customer_coupon_id,customer_coupon_prefix,Customer_ID,Currency_ID,total_vat,sub_total,
            net_total,paid_amount,date_created,date_time,created_logged_id,logged_id,current_year_id,
            description,depot_id,branch_id,current_rate,class_id,bone_type_id,is_actual,job_id,invoice_date,second_currency_rate,discount_amount,discount_percent) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", $data);


        $Invoice_ID = $this->db->id();
        $jv = JV::returnJV($is_actual);
        $ref_num = 'CCOInvoice.' . $invoice_prefix . $inv_num;
        $bone_account_details = self::getCouponAccount($this->info['bone_type_id']);
        $bone_account = $bone_account_details['Account_ID'];
        $bone_vat_account = $bone_account_details['vat_account_id'];
        $customer_account = Customer::getCustomerAccount($this->info['customer_id'])['Account_ID'];
        $customer_vat_account = Customer::returnCustomerVATAccount($this->info['customer_id'])['Account_ID'];

        if (!empty($this->info['coupon'])) {
            foreach ($this->info['coupon'] as $item) {
                $uom_id = Product::get_product_default_uom($item['product_id']);
                $default_qty = Product::getConvertedQuantity($uom_id, $item['liters'], $item['product_id']);
                $prod = [&$Invoice_ID, &$item['product_id'], &$uom_id, &$item['liters'], &$default_qty,
                    &$item['unit_price'], &$item['total'], &$item['vat_percent'], &$item['vat'],
                    &$item['disc_amount'], &$item['disc_percent'], &$item['bone_note'], &$item['bone_date'], &$item['coupon_number']];
                $this->db->prepare_Stmt("INSERT INTO tbl_customer_coupon_details (
            auto_id,Product_ID,UOM_ID,current_quantity,default_quantity,unit_price,total_price,
            vat_percent,vat_amount,discount_amount,discount_percent,note,coupon_date,type_id,coupon_number
            ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,1,?)", $prod);
                if (!smc_session()['default_setting']['merge_cust_coupon_prod_details']) {
                    $item_trans_note = ((empty($item['coupon_number']) ? '' : $item['coupon_number'] . ' | ') . (empty($item['bone_note']) ? $this->info['description'] : $item['bone_note']));
                    $dat_2 = ["JV_ID" => $jv,
                        "Transaction_Reference" => $ref_num,
                        "Transaction_Account" => $customer_account,
                        "Transaction_Type" => "Debit",
                        "Transaction_Amount" => ($item['total'] * (100 - $this->info['discount_percent']) / 100),
                        "Transaction_Date" => $this->info['invoice_date'],
                        "Currency_ID" => $Invoice_Currency,
                        "Logged_ID" => $logged_id,
                        "created_logged_id" => $logged_id,
                        "Transaction_Description" => $item_trans_note,
                        "Current_Ex_Rate" => $rate,
                        "second_currency_rate" => $second_rate,
                        "main_id" => $Invoice_ID,
                        "main_type" => "cco",
                        "branch_id" => $branch_id,
                        "due_date" => $this->info['invoice_date'],
                        "main_nb" => $inv_num,
                        "prefix" => $invoice_prefix,
                        "job_id" => $this->info['job_id']
                    ];
                    JV::addTransaction($dat_2, $is_actual);
                }
            }
        }
        $total_prod = 0;
        if (!empty($this->info['product'])) {
            foreach ($this->info['product'] as $item) {
                $default_qty = Product::getConvertedQuantity($item['uom_id'], $item['qty'], $item['product_id']);
                $item_data = [&$Invoice_ID, &$item['product_id'], &$item['uom_id'], &$item['qty'], &$default_qty, &$item['unit_price'], &$item['disc_amount'], &$item['disc_percent'],
                    &$item['total'], &$item['vat_percent'], &$item['vat'], &$item['desc']];
                $this->db->prepare_Stmt("INSERT INTO tbl_customer_coupon_details (
            auto_id,Product_ID,UOM_ID,current_quantity,default_quantity,
            unit_price,discount_amount,discount_percent,total_price,
            vat_percent,vat_amount,note,type_id
            ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,2)", $item_data);
                $total_prod+=$item['total'];
            }
        }


        $dat_1 = ["JV_ID" => $jv,
            "Transaction_Reference" => $ref_num,
            "Transaction_Account" => $bone_account,
            "Transaction_Type" => "Credit",
            "Transaction_Amount" => $this->info['Net_Total'],
            "Transaction_Date" => $this->info['invoice_date'],
            "Currency_ID" => $Invoice_Currency,
            "Logged_ID" => $logged_id,
            "created_logged_id" => $logged_id,
            "Transaction_Description" => $this->info['description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $Invoice_ID,
            "main_type" => "cco",
            "branch_id" => $branch_id,
            "due_date" => $this->info['invoice_date'],
            "main_nb" => $inv_num,
            "prefix" => $invoice_prefix,
            "job_id" => $this->info['job_id']
        ];
        JV::addTransaction($dat_1, $is_actual);
        $dat_2 = ["JV_ID" => $jv,
            "Transaction_Reference" => $ref_num,
            "Transaction_Account" => $customer_account,
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => (smc_session()['default_setting']['merge_cust_coupon_prod_details'] == 0 ? $total_prod : $this->info['Net_Total']),
            "Transaction_Date" => $this->info['invoice_date'],
            "Currency_ID" => $Invoice_Currency,
            "Logged_ID" => $logged_id,
            "created_logged_id" => $logged_id,
            "Transaction_Description" => $this->info['description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $Invoice_ID,
            "main_type" => "cco",
            "branch_id" => $branch_id,
            "due_date" => $this->info['invoice_date'],
            "main_nb" => $inv_num,
            "prefix" => $invoice_prefix,
            "job_id" => $this->info['job_id']
        ];
        JV::addTransaction($dat_2, $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_account),
            "Transaction_Type" => "Credit",
            "Transaction_Amount" => $Invoice_VAT,
            "Transaction_Date" => $this->info['invoice_date'],
            "Currency_ID" => $Invoice_Currency,
            "Logged_ID" => $logged_id,
            "created_logged_id" => $logged_id,
            "Transaction_Description" => $this->info['description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $Invoice_ID,
            "main_type" => "cco",
            "branch_id" => $branch_id,
            "due_date" => $this->info['invoice_date'],
            "main_nb" => $inv_num,
            "prefix" => $invoice_prefix,
            "job_id" => $this->info['job_id']
        ];
        JV::addTransaction($dat_3, $is_actual);
        $dat_4 = ["JV_ID" => $jv,
            "Transaction_Reference" => $ref_num,
            "Transaction_Account" => $bone_vat_account,
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => $Invoice_VAT,
            "Transaction_Date" => $this->info['invoice_date'],
            "Currency_ID" => $Invoice_Currency,
            "Logged_ID" => $logged_id,
            "created_logged_id" => $logged_id,
            "Transaction_Description" => $this->info['description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $Invoice_ID,
            "main_type" => "cco",
            "branch_id" => $branch_id,
            "due_date" => $this->info['invoice_date'],
            "main_nb" => $inv_num,
            "prefix" => $invoice_prefix,
            "job_id" => $this->info['job_id']
        ];
        JV::addTransaction($dat_4, $is_actual);
        $dat_5 = ["JV_ID" => $jv,
            "Transaction_Reference" => $ref_num,
            "Transaction_Account" => $customer_account,
            "Transaction_Type" => "Credit",
            "Transaction_Amount" => $paid,
            "Transaction_Date" => $this->info['invoice_date'],
            "Currency_ID" => $Invoice_Currency,
            "Logged_ID" => $logged_id,
            "created_logged_id" => $logged_id,
            "Transaction_Description" => $this->info['description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $Invoice_ID,
            "main_type" => "cco",
            "branch_id" => $branch_id,
            "due_date" => $this->info['invoice_date'],
            "main_nb" => $inv_num,
            "prefix" => $invoice_prefix,
            "job_id" => $this->info['job_id']
        ];
        JV::addTransaction($dat_5, $is_actual);
        $dat_6 = ["JV_ID" => $jv,
            "Transaction_Reference" => 'credit',
            "Transaction_Account" => $this->info['Card_Account'],
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => $this->info['Card_Amount'],
            "Transaction_Date" => $this->info['invoice_date'],
            "Currency_ID" => $Invoice_Currency,
            "Logged_ID" => $logged_id,
            "created_logged_id" => $logged_id,
            "Transaction_Description" => $this->info['description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $Invoice_ID,
            "main_type" => "cco",
            "branch_id" => $branch_id,
            "subtype" => "credit",
            "main_nb" => $inv_num,
            "prefix" => $invoice_prefix,
            "job_id" => $this->info['job_id']
        ];
        JV::addTransaction($dat_6, $is_actual);
        $dat_7 = ["JV_ID" => $jv,
            "Transaction_Reference" => 'cash',
            "Transaction_Account" => $this->info['Cash_Account'],
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => $this->info['Cash_Amount'],
            "Transaction_Date" => $this->info['invoice_date'],
            "Currency_ID" => $Invoice_Currency,
            "Logged_ID" => $logged_id,
            "created_logged_id" => $logged_id,
            "Transaction_Description" => $this->info['description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $Invoice_ID,
            "main_type" => "cco",
            "branch_id" => $branch_id,
            "subtype" => "cash",
            "main_nb" => $inv_num,
            "prefix" => $invoice_prefix,
            "job_id" => $this->info['job_id']
        ];
        JV::addTransaction($dat_7, $is_actual);
        $dat_8 = ["JV_ID" => $jv,
            "Transaction_Reference" => 'check',
            "Transaction_Account" => $this->info['Check_Account'],
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => $this->info['Check_Amount'],
            "Transaction_Date" => $this->info['invoice_date'],
            "Currency_ID" => $Invoice_Currency,
            "Logged_ID" => $logged_id,
            "created_logged_id" => $logged_id,
            "Transaction_Description" => $this->info['description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $Invoice_ID,
            "main_type" => "sin",
            "branch_id" => $branch_id,
            "due_date" => (!empty($this->info['check_due_date']) ? $this->info['check_due_date'] : $this->info['invoice_date']),
            "value_date" => $this->info['value_date'],
            "check_nb" => $this->info['Check_Num'],
            "subtype" => "cco",
            "main_nb" => $inv_num,
            "prefix" => $invoice_prefix,
            "job_id" => $this->info['job_id']
        ];
        JV::addTransaction($dat_8, $is_actual);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query("COMMIT");
            return [$Invoice_ID, ($invoice_prefix . $inv_num)];
        } else {
            $this->db->query("ROLLBACK");
            return array_unique($error);
        }
    }

    public function getCouponAccount($bone_type_id) {
        $query = $this->db->query('select Account_ID,vat_account_id from tbl_bone_type where bone_type_id = "' . $bone_type_id . '"');
        $row = $this->db->assoc($query);
        return $row;
    }

    public function editCustomerCoupon() {
        $is_actual = isset($this->info['is_actual']) ? $this->info['is_actual'] : 0;
        $logged_id = smc_session()['user_logged_id'];
        $check = CommonFunctions::check_lock_data(['type' => 'customer_coupon', 'start_date' => $this->info['invoice_date'], 'is_actual' => $is_actual]);

//        $check = JV::checkCurrentYear($this->info['invoice_date'], $this->info['invoice_date'], $is_actual);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $jv_query = $this->db->query("SELECT DISTINCT JV_ID,Current_Ex_Rate FROM tbl_accounting_transactions  WHERE main_id='" . $this->info['auto_id'] . "' AND main_type= 'cco' ");
        $jv_assoc = $this->db->assoc($jv_query);
        if (!empty($jv_assoc))
            $jv = $jv_assoc['JV_ID'];
        else
            $jv = JV::returnJV($is_actual);
        $this->db->prepare_Stmt("DELETE FROM tbl_accounting_transactions  WHERE main_id= ? AND main_type= 'cco' ", [&$this->info['auto_id']]);

        $depot_id = $this->info['depot_id'];
        $branch_id = isset($this->info['branch_id']) ? $this->info['branch_id'] : CommonBusiness::getBranchByDepot($depot_id);
        $query_main = $this->db->query('select * from tbl_customer_coupon where auto_id = "' . $this->info['auto_id'] . '"');
        $row_main = $this->db->assoc($query_main);
        $invoice_prefix = $row_main['customer_coupon_prefix'];
        $inv_num = $row_main['customer_coupon_id'];
        $old_created_logged_id = $row_main['created_logged_id'];
        $old_date_created = $row_main['date_created'];
        //        $query_branch = $this->db->query('select branch_prefix from tbl_organization_branches where branch_id = "' . $branch_id . '"');
        //        $branch_prefix = $this->db->assoc($query_branch)['branch_prefix'];
        //        $invoice_prefix = smc_session()['user_accounts']['serial_prefix'] ? smc_session()['user_accounts']['serial_prefix'] : $branch_prefix;
        $Customer_ID = $this->info['customer_id'];
        $Invoice_Sub_Total = $this->info['Sub_Total']; // check
        $Invoice_Net_Total = $this->info['Net_Total']; // check
        $Invoice_VAT = $this->info['invoice_vat'];
        $Invoice_Currency = $this->info['currency_id'];
        $mydate = date("Y-m-d H:i:s");
        $paid = $this->info['Card_Amount'] + $this->info['Cash_Amount'] + $this->info['Check_Amount'];

        $rate = !empty($this->info['current_rate']) ? $this->info['current_rate'] : CurrencyFactory::getLastExRate($Invoice_Currency);
        $second_rate = CurrencyFactory::getSecondCurrencyLastExRate($Invoice_Currency);

        $this->db->query('START TRANSACTION');
        $this->info['description'] = !empty($this->info['description']) ? $this->info['description'] : '';
        $class_id = smc_session()['pricing_class_id'];
        $data = [&$Customer_ID, &$Invoice_Currency, &$Invoice_VAT,
            &$Invoice_Sub_Total, &$Invoice_Net_Total, &$paid, &$mydate, &$logged_id,
            &$this->info['description'], &$depot_id, &$branch_id, &$rate, &$class_id,
            &$this->info['bone_type_id'], &$is_actual, &$this->info['job_id'], &$this->info['invoice_date'],
            &$second_rate, &$this->info['discount_amount'], &$this->info['discount_percent'], &$this->info['auto_id']];
        $this->db->prepare_Stmt("Update  tbl_customer_coupon set
            Customer_ID =?,Currency_ID =? ,total_vat =?,sub_total =?,
            net_total =?,paid_amount=?,date_time =?,logged_id =? ,
            description=?,depot_id=?,branch_id=?,current_rate =?,class_id =?,bone_type_id =?,is_actual =?,job_id =?,
            invoice_date = ?,second_currency_rate=?,discount_amount=?,discount_percent=?
              where auto_id = ?", $data);
        $Invoice_ID = $this->info['auto_id'];


        $this->db->prepare_Stmt('delete from tbl_customer_coupon_details where auto_id =? ', [&$Invoice_ID]);
        //        $jv = JV::returnJV($is_actual);
        $ref_num = 'CCOInvoice.' . $invoice_prefix . $inv_num;
        $bone_account_details = self::getCouponAccount($this->info['bone_type_id']);
        $bone_account = $bone_account_details['Account_ID'];
        $bone_vat_account = $bone_account_details['vat_account_id'];
        $customer_account = Customer::getCustomerAccount($this->info['customer_id'])['Account_ID'];
        $customer_vat_account = Customer::returnCustomerVATAccount($this->info['customer_id'])['Account_ID'];

        if (!empty($this->info['coupon'])) {
            foreach ($this->info['coupon'] as $item) {
                $uom_id = Product::get_product_default_uom($item['product_id']);
                $default_qty = Product::getConvertedQuantity($uom_id, $item['liters'], $item['product_id']);
                $prod = [&$Invoice_ID, &$item['product_id'], &$uom_id, &$item['liters'], &$default_qty,
                    &$item['unit_price'], &$item['total'], &$item['vat_percent'], &$item['vat'],
                    &$item['disc_amount'], &$item['disc_percent'],
                    &$item['bone_note'], &$item['bone_date'], &$item['coupon_number']];
                $this->db->prepare_Stmt("INSERT INTO tbl_customer_coupon_details (
            auto_id,Product_ID,UOM_ID,current_quantity,default_quantity,unit_price,total_price,
            vat_percent,vat_amount,discount_amount,discount_percent,note,coupon_date,type_id,coupon_number
            ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,1,?)", $prod);
                if (smc_session()['default_setting']['merge_cust_coupon_prod_details'] == 0) {
                    $item_trans_note = ((empty($item['coupon_number']) ? '' : $item['coupon_number'] . ' | ') . (empty($item['bone_note']) ? $this->info['description'] : $item['bone_note']));
                    $dat_2 = ["JV_ID" => $jv,
                        "Transaction_Reference" => $ref_num,
                        "Transaction_Account" => $customer_account,
                        "Transaction_Type" => "Debit",
                        "Transaction_Amount" => ($item['total'] * (100 - $this->info['discount_percent']) / 100),
                        "Transaction_Date" => $this->info['invoice_date'],
                        "Currency_ID" => $Invoice_Currency,
                        "Logged_ID" => $logged_id,
                        "created_logged_id" => $logged_id,
                        "Transaction_Description" => $item_trans_note,
                        "Current_Ex_Rate" => $rate,
                        "second_currency_rate" => $second_rate,
                        "main_id" => $Invoice_ID,
                        "main_type" => "cco",
                        "branch_id" => $branch_id,
                        "due_date" => $this->info['invoice_date'],
                        "main_nb" => $inv_num,
                        "prefix" => $invoice_prefix,
                        "job_id" => $this->info['job_id']
                    ];
                    JV::addTransaction($dat_2, $is_actual);
                }
            }
        }
        $total_prod = 0;
        if (!empty($this->info['product'])) {
            foreach ($this->info['product'] as $item) {
                $default_qty = Product::getConvertedQuantity($item['uom_id'], $item['qty'], $item['product_id']);
                $item_data = [&$Invoice_ID, &$item['product_id'], &$item['uom_id'], &$item['qty'], &$default_qty, &$item['unit_price'], &$item['disc_amount'], &$item['disc_percent'],
                    &$item['total'], &$item['vat_percent'], &$item['vat'], &$item['desc']];
                $this->db->prepare_Stmt("INSERT INTO tbl_customer_coupon_details (
            auto_id,Product_ID,UOM_ID,current_quantity,default_quantity,
            unit_price,discount_amount,discount_percent,total_price,
            vat_percent,vat_amount,note,type_id
            ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,2)", $item_data);
                $total_prod +=$item['total'];
            }
        }



        $dat_1 = ["JV_ID" => $jv,
            "Transaction_Reference" => $ref_num,
            "Transaction_Account" => $bone_account,
            "Transaction_Type" => "Credit",
            "Transaction_Amount" => $this->info['Net_Total'],
            "Transaction_Date" => $this->info['invoice_date'],
            "Currency_ID" => $Invoice_Currency,
            "Logged_ID" => $logged_id,
            "date_created" => $old_date_created,
            "created_logged_id" => $old_created_logged_id,
            "Transaction_Description" => $this->info['description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $Invoice_ID,
            "main_type" => "cco",
            "branch_id" => $branch_id,
            "due_date" => $this->info['invoice_date'],
            "main_nb" => $inv_num,
            "prefix" => $invoice_prefix,
            "job_id" => $this->info['job_id']
        ];
        JV::addTransaction($dat_1, $is_actual);


        $dat_2 = ["JV_ID" => $jv,
            "Transaction_Reference" => $ref_num,
            "Transaction_Account" => $customer_account,
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => (smc_session()['default_setting']['merge_cust_coupon_prod_details'] == 0 ? $total_prod : $this->info['Net_Total']),
            "Transaction_Date" => $this->info['invoice_date'],
            "Currency_ID" => $Invoice_Currency,
            "Logged_ID" => $logged_id,
            "date_created" => $old_date_created,
            "created_logged_id" => $old_created_logged_id,
            "Transaction_Description" => $this->info['description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $Invoice_ID,
            "main_type" => "cco",
            "branch_id" => $branch_id,
            "due_date" => $this->info['invoice_date'],
            "main_nb" => $inv_num,
            "prefix" => $invoice_prefix,
            "job_id" => $this->info['job_id']
        ];
        JV::addTransaction($dat_2, $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_account),
            "Transaction_Type" => "Credit",
            "Transaction_Amount" => $Invoice_VAT,
            "Transaction_Date" => $this->info['invoice_date'],
            "Currency_ID" => $Invoice_Currency,
            "date_created" => $old_date_created,
            "Logged_ID" => $old_created_logged_id,
            "created_logged_id" => $logged_id,
            "Transaction_Description" => $this->info['description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $Invoice_ID,
            "main_type" => "cco",
            "branch_id" => $branch_id,
            "due_date" => $this->info['invoice_date'],
            "main_nb" => $inv_num,
            "prefix" => $invoice_prefix,
            "job_id" => $this->info['job_id']
        ];
        JV::addTransaction($dat_3, $is_actual);

        $dat_4 = ["JV_ID" => $jv,
            "Transaction_Reference" => $ref_num,
            "Transaction_Account" => $bone_vat_account,
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => $Invoice_VAT,
            "Transaction_Date" => $this->info['invoice_date'],
            "Currency_ID" => $Invoice_Currency,
            "Logged_ID" => $logged_id,
            "date_created" => $old_date_created,
            "created_logged_id" => $old_created_logged_id,
            "Transaction_Description" => $this->info['description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $Invoice_ID,
            "main_type" => "cco",
            "branch_id" => $branch_id,
            "due_date" => $this->info['invoice_date'],
            "main_nb" => $inv_num,
            "prefix" => $invoice_prefix,
            "job_id" => $this->info['job_id']
        ];
        JV::addTransaction($dat_4, $is_actual);

        $dat_5 = ["JV_ID" => $jv,
            "Transaction_Reference" => $ref_num,
            "Transaction_Account" => $customer_account,
            "Transaction_Type" => "Credit",
            "Transaction_Amount" => $paid,
            "Transaction_Date" => $this->info['invoice_date'],
            "Currency_ID" => $Invoice_Currency,
            "date_created" => $old_date_created,
            "created_logged_id" => $old_created_logged_id,
            "Logged_ID" => $logged_id,
            "Transaction_Description" => $this->info['description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $Invoice_ID,
            "main_type" => "cco",
            "branch_id" => $branch_id,
            "due_date" => $this->info['invoice_date'],
            "main_nb" => $inv_num,
            "prefix" => $invoice_prefix,
            "job_id" => $this->info['job_id']
        ];
        JV::addTransaction($dat_5, $is_actual);
        //           $error = $this->db->error();
        //        die(print_r($error));
        $dat_6 = ["JV_ID" => $jv,
            "Transaction_Reference" => 'credit',
            "Transaction_Account" => $this->info['Card_Account'],
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => $this->info['Card_Amount'],
            "Transaction_Date" => $this->info['invoice_date'],
            "Currency_ID" => $Invoice_Currency,
            "Logged_ID" => $logged_id,
            "date_created" => $old_date_created,
            "created_logged_id" => $old_created_logged_id,
            "Transaction_Description" => $this->info['description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $Invoice_ID,
            "main_type" => "cco",
            "branch_id" => $branch_id,
            "due_date" => $this->info['invoice_date'],
            "subtype" => "credit",
            "main_nb" => $inv_num,
            "prefix" => $invoice_prefix,
            "job_id" => $this->info['job_id']
        ];
        JV::addTransaction($dat_6, $is_actual);
        $dat_7 = ["JV_ID" => $jv,
            "Transaction_Reference" => 'cash',
            "Transaction_Account" => $this->info['Cash_Account'],
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => $this->info['Cash_Amount'],
            "Transaction_Date" => $this->info['invoice_date'],
            "Currency_ID" => $Invoice_Currency,
            "Logged_ID" => $logged_id,
            "date_created" => $old_date_created,
            "created_logged_id" => $old_created_logged_id,
            "Transaction_Description" => $this->info['description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $Invoice_ID,
            "main_type" => "cco",
            "branch_id" => $branch_id,
            "due_date" => $this->info['invoice_date'],
            "subtype" => "cash",
            "main_nb" => $inv_num,
            "prefix" => $invoice_prefix,
            "job_id" => $this->info['job_id']
        ];
        JV::addTransaction($dat_7, $is_actual);
        $dat_8 = ["JV_ID" => $jv,
            "Transaction_Reference" => 'check',
            "Transaction_Account" => $this->info['Check_Account'],
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => $this->info['Check_Amount'],
            "Transaction_Date" => $this->info['invoice_date'],
            "Currency_ID" => $Invoice_Currency,
            "Logged_ID" => $logged_id,
            "date_created" => $old_date_created,
            "created_logged_id" => $old_created_logged_id,
            "Transaction_Description" => $this->info['description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $Invoice_ID,
            "main_type" => "sin",
            "branch_id" => $branch_id,
            "due_date" => (!empty($this->info['check_due_date']) ? $this->info['check_due_date'] : $this->info['invoice_date']),
            "value_date" => $this->info['value_date'],
            "check_nb" => $this->info['Check_Num'],
            "subtype" => "cco",
            "main_nb" => $inv_num,
            "prefix" => $invoice_prefix,
            "job_id" => $this->info['job_id']
        ];
        JV::addTransaction($dat_8, $is_actual);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query("COMMIT");
            return [$Invoice_ID, ($invoice_prefix)];
        } else {
            $this->db->query("ROLLBACK");
            return array_unique($error);
        }
    }

    public function getMaxCustomerCoupon($prefix, $is_actual = 0) {
        $yearid = $is_actual ? $this->year_id_actual : $this->year_id;
        $query = $this->db->query('SELECT MAX(customer_coupon_id) AS Entry_ID 
            FROM tbl_customer_coupon where 1=1 AND current_year_id = "' . $yearid . '" '
                . 'and customer_coupon_prefix = "' . $prefix . '" and is_actual = "' . $is_actual . '"');
        $row = $this->db->assoc($query);
        if (!empty($row))
            $Invoice = $row['Entry_ID'] + 1;
        else
            $Invoice = 1;

        return $Invoice;
    }

    public function getCustomerCouponData($id) {
        $data = array('main' => array(),
            'details' => array('coupons' => array(), 'products' => array()),
            'Payment' => array());
        $customer_coupon_main = $this->db->query('select list.*,
                                                        depot.Depot_Name,
                                                        job.job_name
                                                    from customer_coupons_list list 
                                                   inner join tbl_stock_depot_warehouse depot on depot.Depot_ID = list.depot_id
                                                   left join tbl_accounting_jobs job using(job_id)
                                                   where list.auto_id = "' . $id . '"');
        $data['main'] = $this->db->assoc($customer_coupon_main);
        if (!empty($data['main'])) {
            $data['main']['temp_description'] = $data['main']['description'] ? $data['main']['description'] : '-- --';
            $data['main']['temp_total_discount'] = CommonFunctions::formatNumber($data['main']['discount_amount'] ? $data['main']['discount_amount'] : 0, 2);
            $data['main']['temp_NetTotal'] = CommonFunctions::formatNumber($data['main']['NetTotal'], 2);
            $data['main']['temp_paid_amount'] = CommonFunctions::formatNumber($data['main']['paid_amount'], 2);
            $data['main']['temp_SubTotal'] = CommonFunctions::formatNumber($data['main']['SubTotal'], 2);
            $data['main']['temp_TotalVat'] = CommonFunctions::formatNumber($data['main']['TotalVat'], 2);
            $data['main']['temp_net_total_word_ar'] = Forms::num_to_word($data['main']['NetTotal'], $data['main']['Currency_ID'], 'ar');
            $data['main']['temp_net_total_word_en'] = Forms::num_to_word($data['main']['NetTotal'], $data['main']['Currency_ID'], 'en');
        }
        $coupon_details = $this->db->query('select details.*,
                                                            concat_ws(" - ",products.Product_Name,products.Product_Code) as prod,
                                                            products.Product_Name,
                                                            uom.UOM_Name
                                                     from tbl_customer_coupon_details details
                                                     inner join tbl_stock_products products using(Product_ID)
                                                     inner join tbl_stock_uom uom on uom.UOM_ID = details.uom_id
                                                     where details.auto_id = "' . $id . '"');
        $i = $j = 0;
        while ($row = $this->db->assoc($coupon_details)) {
            $row['temp_current_quantity'] = CommonFunctions::formatNumber($row['current_quantity'], 2);
            $row['temp_unit_price'] = CommonFunctions::formatNumber($row['unit_price'], 2);
            $row['temp_total_price'] = CommonFunctions::formatNumber($row['total_price'], 2);
            if ($row['type_id'] == 1) {
                $row['count_c'] = ++$i;
                $data['details']['coupons'][] = $row;
            } else {
                $row['count_p'] = ++$j;
                $data['details']['products'][] = $row;
            }
        }
        $data['main']['product_class'] = count($data['details']['products']) > 0 ? '' : 'hidden';
        $data['main']['coupon_class'] = count($data['details']['coupons']) > 0 ? '' : 'hidden';
        $query = $this->db->query("SELECT 
                                            t.Transaction_Amount,
                                            t.Transaction_Reference,
                                            t.Account_ID,
                                            p.Account_Name AS P_Name,
                                            t.check_nb,t.due_date,t.value_date
                                        FROM 
                                            tbl_accounting_transactions t
                                        inner JOIN 
                                            tbl_chart_of_accounts p
                                        ON 
                                            t.Account_ID = p.Account_ID
                                       WHERE 
                                             t.main_id = '" . $id . "'
                                        AND 
                                            t.main_type = 'cco'
                                        AND 
                                            t.Transaction_Reference in ('cash','check','credit')
                                        ");
        while ($row = $this->db->assoc($query)) {
            if (stripos($row['Transaction_Reference'], 'cash') !== false)
                $data['Payment']['Cash'] = $row;
            if (stripos($row['Transaction_Reference'], 'check') !== false) {
                $data['Payment']['Cheque'] = $row;
            }
            if (stripos($row['Transaction_Reference'], 'credit') !== false)
                $data['Payment']['Credit_Card'] = $row;
        }
        return $data;
    }

    public function edit_station_invoice() {
        $this->db->query('START TRANSACTION');
        $is_actual = $this->info['is_actual'];
        $check = CommonFunctions::check_lock_data(['type' => 'station_invoice', 'start_date' => $this->info['invoice_date'], 'is_actual' => $this->info['is_actual']]);

//        $check = JV::checkCurrentYear($this->info['invoice_date'], $this->info['invoice_date'], $this->info['is_actual']);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $depot_id = $this->info['depot_id'];
        $branch_id = isset($this->info['branch_id']) ? $this->info['branch_id'] : CommonBusiness::getBranchByDepot($depot_id);
        //        $query_branch = $this->db->query('select branch_prefix from tbl_organization_branches where branch_id = "' . $branch_id . '"');
        //        $branch_prefix = $this->db->assoc($query_branch)['branch_prefix'];
        //        $invoice_prefix = smc_session()['user_accounts']['serial_prefix'] ? smc_session()['user_accounts']['serial_prefix'] : $branch_prefix;
        $mydate = date("Y-m-d H:i:s");
        //        $year_id = $this->info['is_actual'] ? $this->year_id_actual : $this->year_id;
        $rate = !empty($this->info['current_rate']) ? $this->info['current_rate'] : CurrencyFactory::getLastExRate($this->info['currency_id']);
        $second_rate = CurrencyFactory::getSecondCurrencyLastExRate($this->info['currency_id']);
        $logged_id = smc_session()['user_logged_id'];
        //        $class_id = smc_session()['pricing_class_id'];
        $today = Date::getToday();
        $jv_query = $this->db->query("SELECT DISTINCT JV_ID,Current_Ex_Rate FROM tbl_accounting_transactions  WHERE main_id='" . $this->info['invoice_id'] . "' AND main_type= 'sin' ");
        $jv_assoc = $this->db->assoc($jv_query);
        if (!empty($jv_assoc))
            $jv = $jv_assoc['JV_ID'];
        else
            $jv = JV::returnJV($is_actual);
        $this->db->prepare_Stmt("DELETE FROM tbl_accounting_transactions  WHERE main_id= ? AND main_type= 'sin' ", [&$this->info['invoice_id']]);



        $inv_query = $this->db->query('select invoice_nb,invoice_prefix,created_logged_id,date_created,date_time 
            from tbl_station_invoice where invoice_id = "' . $this->info['invoice_id'] . '"');
        $row_inv = $this->db->assoc($inv_query);
        //        $main_nb = $row_inv['invoice_nb'];
        $inv_num = $row_inv['invoice_nb'];
        $invoice_prefix = $row_inv['invoice_prefix'];
        $old_created_logged_id = $row_inv['created_logged_id'];
        $old_date_created = $row_inv['date_created'];
        //        $invoice_prefix . $inv_num
        $query = $this->db->query("SELECT 
                                        p.product_id as sub_product_id,
                                        p.current_quantity as Current_Quantity,
                                        p.default_quantity as Quantity,
                                        p.uom_id as UOM_ID,
                                        '-' as operation,
                                        p.unit_cost,
                                        i.currency_id AS Currency_ID,
                                        i.depot_id as Depot_ID,
                                        i.current_rate,
                                        i.date_time,
                                        i.invoice_prefix, 
                                        i.invoice_nb,i.created_logged_id,i.date_created,pr.is_service,
                                        i.customer_id
                                    FROM 
                                        tbl_station_invoice_products p
                                    join tbl_station_invoice i on i.invoice_id = p.invoice_id
                                    join tbl_stock_products pr on pr.Product_ID = p.product_id
                                    WHERE 
                                        p.invoice_id = '" . $this->info['invoice_id'] . "'
                ");
        while ($row = $this->db->assoc($query)) {
            $sub_cost = $row['unit_cost'];
            $old_expiry_date = null;
            $costdata = [
                "Product_ID" => $row['sub_product_id'],
                "Quantity" => $row['Quantity'],
                "Unit_Cost" => $sub_cost,
                "Currency_ID" => $row['Currency_ID'],
                "date_time" => $row['date_time'],
                "current_rate" => $row['current_rate'],
                "depot_id" => $row['Depot_ID'],
                "trans_date" => $this->info['invoice_date'],
                "current_quantity" => $row['Current_Quantity'],
                "uom_id" => $row['UOM_ID'],
                "is_actual" => $is_actual,
                "expiry_date" => $old_expiry_date,
                "lot_nb" => '',
                "serial_nb" => '',
                "main_id" => $this->info['invoice_id'],
                "is_service" => $row['is_service'],
                "main_nb" => $invoice_prefix . $inv_num,
                "customer_id" => $row['customer_id']
            ];

            self::updateLinkQuantities($costdata, "edit_station_invoice_old", ($row['operation'] == '+' ? '-' : '+'));
        }
        $query = $this->db->query("SELECT 
                                        p.product_id as sub_product_id,
                                        p.counter_diff as Current_Quantity,
                                        p.default_quantity as Quantity,
                                        p.uom_id as UOM_ID,
                                        '-' as operation,
                                        p.unit_cost,
                                        i.currency_id AS Currency_ID,
                                        i.depot_id as Depot_ID,
                                        i.current_rate,
                                        i.date_time,
                                        i.invoice_prefix, 
                                        i.invoice_nb,i.created_logged_id,i.date_created,pr.is_service,
                                        i.customer_id
                                    FROM 
                                        tbl_station_invoice_pump p
                                    join tbl_station_invoice i on i.invoice_id = p.invoice_id
                                    join tbl_stock_products pr on pr.Product_ID = p.product_id

                                    WHERE 
                                        p.invoice_id = '" . $this->info['invoice_id'] . "'
                ");
        while ($row = $this->db->assoc($query)) {
            $sub_cost = $row['unit_cost'];
            $old_expiry_date = null;
            $costdata = [
                "Product_ID" => $row['sub_product_id'],
                "Quantity" => $row['Quantity'],
                "Unit_Cost" => $sub_cost,
                "Currency_ID" => $row['Currency_ID'],
                "date_time" => $row['date_time'],
                "current_rate" => $row['current_rate'],
                "depot_id" => $row['Depot_ID'],
                "trans_date" => $this->info['invoice_date'],
                "current_quantity" => $row['Current_Quantity'],
                "uom_id" => $row['UOM_ID'],
                "is_actual" => $is_actual,
                "expiry_date" => $old_expiry_date,
                "lot_nb" => '',
                "serial_nb" => '',
                "main_id" => $this->info['invoice_id'],
                "is_service" => $row['is_service'],
                "main_nb" => $invoice_prefix . $inv_num,
                "customer_id" => $row['customer_id']
            ];

            self::updateLinkQuantities($costdata, "edit_station_invoice_old", ($row['operation'] == '+' ? '-' : '+'));
        }

        $this->db->prepare_Stmt("DELETE FROM tbl_station_invoice_pump  WHERE invoice_id = ?", [&$this->info['invoice_id']]);
        $this->db->prepare_Stmt("DELETE FROM tbl_station_invoice_bone  WHERE invoice_id = ?", [&$this->info['invoice_id']]);
        $this->db->prepare_Stmt("DELETE FROM tbl_station_invoice_products  WHERE invoice_id = ?", [&$this->info['invoice_id']]);

        //        $inv_num = self::get_max_station_invoice($invoice_prefix, $this->info['is_actual']);
        $data = [&$this->info['customer_id'], &$this->info['invoice_date'], &$this->info['invoice_vat'],
            &$this->info['overall_total'], &$this->info['currency_id'], $this->info['depot_id'], &$branch_id,
            &$rate, &$this->info['job_id'], &$this->info['description'], &$logged_id, &$mydate,
            &$this->info['employee_id'], &$this->info['account_id'], &$this->info['cash_difference'],
            &$second_rate, &$this->info['invoice_id']];
        $this->db->prepare_Stmt('update `tbl_station_invoice`
            set `customer_id`=?, `invoice_date`=?,  `invoice_vat`=?, `net_total`=?, `currency_id`=?, `depot_id`=?,
            `branch_id`=?, `current_rate`=?, `job_id`=?, `description`=?, `logged_id`=?,  `date_time`=?,
            employee_id=?,Account_ID=?,cash_difference_amount=?,second_currency_rate=? where invoice_id = ?', $data);
        $invoice_id = $this->info['invoice_id'];
        $prod_ids_arr = array();
        CommonBusiness::update_trans_record_current('sin', $invoice_id);
        if (!empty($this->info['pumps'])) {
            foreach ($this->info['pumps'] as $pump) {
                $prod_ids_arr[] = $pump['product_id'];
                $default_qty = Product::getConvertedQuantity($pump['uom_id'], $pump['diff'], $pump['product_id']);

                $pump_data = [&$invoice_id, &$pump['pump_id'], &$pump['old_counter'], &$pump['new_counter'], &$pump['diff'],
                    &$pump['product_id'], &$pump['uom_id'], &$pump['pump_unit_price'], &$pump['total'], &$pump['pump_vat_percent'],
                    &$pump['pump_vat'], &$default_qty];
                $this->db->prepare_Stmt('INSERT INTO `tbl_station_invoice_pump`( `invoice_id`, `pump_id`,
                `old_counter`, `new_counter`, `counter_diff`, `product_id`, `uom_id`, `unit_price`, `total_price`,
                `vat_percent`, `vat_amount`, `default_quantity`) values(?,?,?,?,?,?,?,?,?,?,?,?)', $pump_data);
                $prod_arr[] = array("product_id" => $pump['product_id'],
                    "total_amount" => ($pump['total'] - $pump['pump_vat']),
                    "description" => '', "vat_amount" => $pump['pump_vat']);
                $pump['is_service'] = !empty($pump['is_service']) ? $pump['is_service'] : 0;

                $costdata = [
                    "Product_ID" => $pump['product_id'],
                    "Quantity" => $default_qty,
                    "Unit_Cost" => "",
                    "Currency_ID" => $this->info['currency_id'],
                    "date_time" => $mydate,
                    "current_rate" => $rate,
                    "depot_id" => $depot_id,
                    "trans_date" => $today,
                    "current_quantity" => $pump['diff'],
                    "uom_id" => $pump['uom_id'],
                    "is_actual" => $this->info['is_actual'],
                    "expiry_date" => null,
                    "lot_nb" => '',
                    "serial_nb" => '',
                    "main_id" => $invoice_id,
                    "is_service" => $pump['is_service'],
                    "main_nb" => $invoice_prefix . $inv_num,
                    "customer_id" => $this->info['customer_id']
                ];
                $cost = self::updateLinkQuantities($costdata, "edit_station_invoice_new", '-');
            }
        }


        if (!empty($this->info['product'])) {

            foreach ($this->info['product'] as $pump) {
                $prod_ids_arr[] = $pump['product_id'];
                $default_qty = Product::getConvertedQuantity($pump['uom_id'], $pump['qty'], $pump['product_id']);
                $disc_amount = $pump['qty'] * $pump['unit_price'] * $pump['disc'] / 100;
                $pump['is_service'] = !empty($pump['is_service']) ? $pump['is_service'] : 0;

                $costdata = [
                    "Product_ID" => $pump['product_id'],
                    "Quantity" => $default_qty,
                    "Unit_Cost" => "",
                    "Currency_ID" => $this->info['currency_id'],
                    "date_time" => $mydate,
                    "current_rate" => $rate,
                    "depot_id" => $depot_id,
                    "trans_date" => $today,
                    "current_quantity" => $pump['qty'],
                    "uom_id" => $pump['uom_id'],
                    "is_actual" => $this->info['is_actual'],
                    "expiry_date" => null,
                    "lot_nb" => '',
                    "serial_nb" => '',
                    "main_id" => $invoice_id,
                    "is_service" => $pump['is_service'],
                    "main_nb" => $invoice_prefix . $inv_num,
                    "customer_id" => $this->info['customer_id']
                ];
                $cost = self::updateLinkQuantities($costdata, "edit_station_invoice_new", '-');
                $pump_data = [&$invoice_id, &$pump['product_id'], &$pump['unit_price'], &$pump['qty'], &$pump['uom_id'],
                    &$default_qty, &$pump['disc'], &$disc_amount, &$pump['total'], &$pump['vat_percent'],
                    &$pump['vat'], &$cost, &$pump['desc']];
                $this->db->prepare_Stmt('INSERT INTO `tbl_station_invoice_products`(`invoice_id`, `product_id`,
                `unit_price`, `current_quantity`, `uom_id`, `default_quantity`, `discount_percent`, `discount_amount`,
                `total_price`, `vat_percent`, `vat_amount`, `unit_cost`, `description`) values(?,?,?,?,?,?,?,?,?,?,?,?,?)', $pump_data);
                $prod_arr[] = array("product_id" => $pump['product_id'],
                    "total_amount" => ($pump['total'] - $pump['vat']),
                    "description" => '', "vat_amount" => $pump['vat']);
            }
        }

        $sales_account = empty($this->info['account_id']) ? smc_session()['user_accounts']['sales_account_id'] : $this->info['account_id'];
        $prod_str =implode( ',',$prod_ids_arr);
        $prod_trans_arr = CommonBusiness::get_item_acc_transactions($prod_arr, $prod_str, 'sin', $sales_account);
        //        $jv = JV::returnJV($this->info['is_actual']);
        $ref_num = 'SInv.' . $invoice_prefix . $inv_num;
        $customerAccount = Customer::getCustomerAccount($this->info['customer_id'])['Account_ID'];
        //       echo"<pre>";die(print_r($prod_trans_arr));
        foreach ($prod_trans_arr as $trans) {
            if (isset($trans['main'])) {
                $trans_main = $trans['main'];
                $data_trans = ["JV_ID" => $jv,
                    "Transaction_Reference" => (empty($trans_main['ref']) ? 'sin' : $trans_main['ref']),
                    "Transaction_Account" => $trans_main['trans_account'],
                    "Transaction_Type" => "Credit",
                    "Transaction_Amount" => $trans_main['trans_amount'],
                    "Transaction_Date" => $today,
                    "Currency_ID" => $this->info['currency_id'],
                    "Logged_ID" => $logged_id,
                    "date_created" => $old_date_created,
                    "created_logged_id" => $old_created_logged_id,
                    "Transaction_Description" => (empty($trans_main['trans_desc']) ? $this->info['description'] : $trans_main['trans_desc']),
                    "Current_Ex_Rate" => $rate,
                    "second_currency_rate" => $second_rate,
                    "main_id" => $invoice_id,
                    "main_type" => "sin",
                    "branch_id" => $branch_id,
                    "due_date" => $this->info['invoice_date'],
                    "subtype" => "sin",
                    "main_nb" => $inv_num,
                    "prefix" => $invoice_prefix,
                    "job_id" => $this->info['job_id']
                ];
                JV::addTransaction($data_trans, $this->info['is_actual']);
            }
        }
        //      $error = $this->db->error();
        //        die(print_r($error));
        $data_trans = ["JV_ID" => $jv,
            "Transaction_Reference" => $ref_num,
            "Transaction_Account" => $customerAccount,
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => ($this->info['pump_total'] + $this->info['prod_total'] - $this->info['bon_total'] + $this->info['service_total']) - $this->info['cash_difference'],
            "Transaction_Date" => $today,
            "Currency_ID" => $this->info['currency_id'],
            "Logged_ID" => $logged_id,
            "date_created" => $old_date_created,
            "created_logged_id" => $old_created_logged_id,
            "Transaction_Description" => $this->info['description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $invoice_id,
            "main_type" => "sin",
            "branch_id" => $branch_id,
            "due_date" => $this->info['invoice_date'],
            "main_nb" => $inv_num,
            "prefix" => $invoice_prefix,
            "job_id" => $this->info['job_id']
        ];
        JV::addTransaction($data_trans, $this->info['is_actual']);
        $data_trans = ["JV_ID" => $jv,
            "Transaction_Reference" => $ref_num,
            "Transaction_Account" => $customerAccount,
            "Transaction_Type" => "Credit",
            "Transaction_Amount" => ($this->info['pump_total'] + $this->info['prod_total'] - $this->info['bon_total'] + $this->info['service_total']) - $this->info['cash_difference'],
            "Transaction_Date" => $today,
            "Currency_ID" => $this->info['currency_id'],
            "Logged_ID" => $logged_id,
            "created_logged_id" => $logged_id,
            "Transaction_Description" => $this->info['description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $invoice_id,
            "main_type" => "sin",
            "branch_id" => $branch_id,
            "due_date" => $this->info['invoice_date'],
            "main_nb" => $inv_num,
            "prefix" => $invoice_prefix,
            "job_id" => $this->info['job_id']
        ];
        JV::addTransaction($data_trans, $this->info['is_actual']);
        $data_trans = ["JV_ID" => $jv,
            "Transaction_Reference" => $ref_num,
            "Transaction_Account" => smc_session()['user_accounts']['cash_account_id'],
            "Transaction_Type" => "Debit",
            "Transaction_Amount" => ($this->info['pump_total'] + $this->info['prod_total'] - $this->info['bon_total'] + $this->info['service_total']) - $this->info['cash_difference'],
            "Transaction_Date" => $today,
            "Currency_ID" => $this->info['currency_id'],
            "Logged_ID" => $logged_id,
            "created_logged_id" => $logged_id,
            "Transaction_Description" => $this->info['description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $invoice_id,
            "main_type" => "sin",
            "branch_id" => $branch_id,
            "due_date" => $this->info['invoice_date'],
            "main_nb" => $inv_num,
            "prefix" => $invoice_prefix,
            "job_id" => $this->info['job_id']
        ];
        JV::addTransaction($data_trans, $this->info['is_actual']);
        if (!empty($this->info['coupon'])) {
            foreach ($this->info['coupon'] as $coupon) {
                $coupon['uom_id'] = Product::get_product_default_uom($coupon['product_id']);
                $default_qty = Product::getConvertedQuantity($coupon['uom_id'], $coupon['liters'], $coupon['product_id']);

                $coupon_acc = Station::get_coupon_account($coupon['bone_type_id']);
                $account_id = $coupon_acc['Account_ID'];
                $vat_account_id = $coupon_acc['vat_account_id'];
                $service_account_id = !empty($coupon_acc['service_account_id']) ? $coupon_acc['service_account_id'] : $this->info['account_id'];
                $data_coupon = [&$invoice_id, &$coupon['bone_type_id'], &$coupon['product_id'], &$coupon['liters'],
                    &$coupon['uom_id'], &$default_qty, &$coupon['unit_price'], &$coupon['total'], &$coupon['vat_percent'],
                    &$coupon['vat'], &$account_id, &$vat_account_id, &$coupon['service']];
                $this->db->prepare_Stmt('INSERT INTO `tbl_station_invoice_bone`( `invoice_id`, `bon_type_id`, `product_id`,
                `current_quantity`, `uom_id`, `default_quantity`, `unit_price`, `total_price`, `vat_percent`,
                `vat_amount`,account_id,vat_account_id,service_price) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)', $data_coupon);
                $data_trans = ["JV_ID" => $jv,
                    "Transaction_Reference" => $ref_num,
                    "Transaction_Account" => $account_id,
                    "Transaction_Type" => "Debit",
                    "Transaction_Amount" => $coupon['total'],
                    "Transaction_Date" => $today,
                    "Currency_ID" => $this->info['currency_id'],
                    "Logged_ID" => $logged_id,
                    "date_created" => $old_date_created,
                    "created_logged_id" => $old_created_logged_id,
                    "Transaction_Description" => $this->info['description'],
                    "Current_Ex_Rate" => $rate,
                    "second_currency_rate" => $second_rate,
                    "main_id" => $invoice_id,
                    "main_type" => "sin",
                    "branch_id" => $branch_id,
                    "due_date" => $this->info['invoice_date'],
                    "main_nb" => $inv_num,
                    "prefix" => $invoice_prefix,
                    "job_id" => $this->info['job_id']
                ];
                JV::addTransaction($data_trans, $this->info['is_actual']);
                $data_trans = ["JV_ID" => $jv,
                    "Transaction_Reference" => $ref_num,
                    "Transaction_Account" => $vat_account_id,
                    "Transaction_Type" => "Credit",
                    "Transaction_Amount" => $coupon['vat'],
                    "Transaction_Date" => $today,
                    "Currency_ID" => $this->info['currency_id'],
                    "Logged_ID" => $logged_id,
                    "date_created" => $old_date_created,
                    "created_logged_id" => $old_created_logged_id,
                    "Transaction_Description" => $this->info['description'],
                    "Current_Ex_Rate" => $rate,
                    "second_currency_rate" => $second_rate,
                    "main_id" => $invoice_id,
                    "main_type" => "sin",
                    "branch_id" => $branch_id,
                    "due_date" => $this->info['invoice_date'],
                    "main_nb" => $inv_num,
                    "prefix" => $invoice_prefix,
                    "job_id" => $this->info['job_id']
                ];
                JV::addTransaction($data_trans, $this->info['is_actual']);
                $data_trans = ["JV_ID" => $jv,
                    "Transaction_Reference" => $ref_num,
                    "Transaction_Account" => $service_account_id,
                    "Transaction_Type" => "Credit",
                    "Transaction_Amount" => $coupon['service'],
                    "Transaction_Date" => $today,
                    "Currency_ID" => $this->info['currency_id'],
                    "Logged_ID" => $logged_id,
                    "date_created" => $old_date_created,
                    "created_logged_id" => $old_created_logged_id,
                    "Transaction_Description" => $this->info['description'],
                    "Current_Ex_Rate" => $rate,
                    "second_currency_rate" => $second_rate,
                    "main_id" => $invoice_id,
                    "main_type" => "sin",
                    "branch_id" => $branch_id,
                    "due_date" => $this->info['invoice_date'],
                    "main_nb" => $inv_num,
                    "prefix" => $invoice_prefix,
                    "job_id" => $this->info['job_id']
                ];
                JV::addTransaction($data_trans, $this->info['is_actual']);
            }
        }
        $vat_account = Customer::returnCustomerVATAccount($this->info['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'] : $vat_account),
            "Transaction_Type" => "Credit",
            "Transaction_Amount" => ($this->info['pump_vat'] + $this->info['prod_vat']),
            "Transaction_Date" => $today,
            "Currency_ID" => $this->info['currency_id'],
            "Logged_ID" => $logged_id,
            "date_created" => $old_date_created,
            "created_logged_id" => $old_created_logged_id,
            "Transaction_Description" => $this->info['description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $invoice_id,
            "main_type" => "sin",
            "branch_id" => $branch_id,
            "due_date" => $this->info['invoice_date'],
            "main_nb" => $inv_num,
            "prefix" => $invoice_prefix,
            "job_id" => $this->info['job_id']
        ];
        JV::addTransaction($dat_3, $this->info['is_actual']);

        $user_discount_account = !empty(smc_session()['user_accounts']['discount_account']) ? smc_session()['user_accounts']['discount_account'] : $this->info['account_id'];
        $dat_4 = ["JV_ID" => $jv,
            "Transaction_Reference" => $ref_num,
            "Transaction_Account" => $user_discount_account,
            "Transaction_Type" => $this->info['cash_difference'] < 0 ? "Credit" : "Debit",
            "Transaction_Amount" => $this->info['cash_difference'],
            "Transaction_Date" => $today,
            "Currency_ID" => $this->info['currency_id'],
            "Logged_ID" => $logged_id,
            "created_logged_id" => $logged_id,
            "Transaction_Description" => $this->info['description'],
            "Current_Ex_Rate" => $rate,
            "second_currency_rate" => $second_rate,
            "main_id" => $invoice_id,
            "main_type" => "sin",
            "branch_id" => $branch_id,
            "due_date" => $this->info['invoice_date'],
            "main_nb" => $inv_num,
            "prefix" => $invoice_prefix,
            "job_id" => $this->info['job_id']
        ];
        JV::addTransaction($dat_4, $this->info['is_actual']);
        //        $error = $this->db->error();
        //        die(print_r($error));
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
            return [$invoice_id, ($invoice_prefix . $inv_num)];
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function get_coupon_type_balance() {
        $data = array();
        $start_date = smc_session()['start_date'];
        $product_ids = array();
        $previous_arr = array();
        $current_arr = array();
        $balance_arr = array();
        $cond = ' ';
        if (!empty($this->info['coupon_type'])) {
            $cond.=' and bone_type_id = "' . $this->info['coupon_type'] . '" ';
        }
        if (!empty($this->info['with_previous'])) {
            $query = $this->db->query('select product_id,"opening" as date,product_name,sum(product_quantity) as product_quantity
            from purchase_invoice_coupon_details where date >= "' . $start_date . '" and date < "' . $this->info['Date_From'] . '" ' . $cond . '
                    order by product_id');
            while ($row = $this->db->assoc($query)) {
                $pid = $row['product_id'];
                $product_ids[$pid] = $row['product_name'];
                unset($row['product_id']);
                $previous_arr[$pid] = $row;
            }
        }
        //        echo"<pre>";die(print_r($previous_arr));

        $query = $this->db->query('select product_id,date,product_name,product_quantity
            from purchase_invoice_coupon_details where  date between "' . $this->info['Date_From'] . '" and "' . $this->info['Date_To'] . '" ' . $cond . '
                    order by product_id,date asc');
        while ($row = $this->db->assoc($query)) {
            $pid = $row['product_id'];
            if (empty($balance_arr[$pid])) {
                $balance_arr[$pid] = ($this->info['with_previous'] && !empty($previous_arr[$pid])) ? $previous_arr[$pid]['product_quantity'] : $row['product_quantity'];
            } else {
                $balance_arr[$pid] += $row['product_quantity'];
            }
            $product_ids[$pid] = $row['product_name'];
            unset($row['product_id']);
            $current_arr[$pid][] = $row;
        }

        //         echo"<pre>";
        //        die(print_r($product_ids));
        foreach (array_unique(array_keys($product_ids)) as $prod) {
            if (!empty($this->info['with_details'])) {
                if (!empty($this->info['with_previous'])) {
                    if (!empty($previous_arr[$prod]))
                        $data[] = array_values($previous_arr[$prod]);
                    else {
                        $data[] = array("opening", $product_ids[$prod], 0);
                    }
                }
                //
                if (!empty($current_arr[$prod])) {
                    foreach ($current_arr[$prod] as $cur_prod) {
                        $data[] = array_values($cur_prod);
                    }
                }
            }
            //            print_r($data);
            if (!empty($balance_arr[$prod]))
                $data[] = array("balance", $product_ids[$prod], $balance_arr[$prod]);
            else {
                if (!empty($previous_arr[$prod]))
                    $data[] = array("balance", $product_ids[$prod], $previous_arr[$prod]['product_quantity']);
                else {
                    $data[] = array("balance", $product_ids[$prod], 0);
                }
            }
            //
        }
        //        echo"<pre>";die(print_r($data));
        return $data;
    }

    public function delete_customer_coupon($id) {
        if (smc_session()['year_closed'] == 1) {
            $error = array();
            $error[0] = CommonLanguage::getMessage('delete_year_alert');
            return $error;
        }
        $query = $this->db->query('select invoice_date,is_actual from tbl_customer_coupon where auto_id ="'.$id.'"');
        $row = $this->db->assoc($query);
         $check = CommonFunctions::check_lock_data(['type' => 'customer_coupon', 'start_date' => $row['invoice_date'], 'is_actual' => $row['is_actual']]);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return $error;
        }
        $this->db->query('START TRANSACTION');
        $this->db->prepare_Stmt('delete from tbl_customer_coupon where auto_id = ?', [&$id]);
        $this->db->prepare_Stmt('delete from tbl_customer_coupon_details where auto_id = ?', [&$id]);
        $this->db->prepare_Stmt('delete from tbl_accounting_transactions where main_id = ? and main_type="cco"', [&$id]);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function customerCouponSearch($term = null, $page, $limit) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' and (cust.Customer_Name LIKE ? or cust_coup.invoice_date LIKE ? or cust_coup.customer_coupon_id LIKE ? )';
            $data_filter[] = "%" . $term . "%";
            $data_filter[] = "%" . $term . "%";
            $data_filter[] = "%" . $term . "%";
        }
        if (!empty($this->info['customer_id'])) {
            $cond .= ' and cust_coup.Customer_ID = ? ';
            $data_filter[] = $this->info['customer_id'];
        }
        $query = $this->db->prepare_Stmt('select 
                                    cust_coup.auto_id as id,
                                    concat_ws(" -  ",' . (empty($this->info['customer_id']) ? 'cust.Customer_Name,' : '') . 'concat_ws("",cust_coup.customer_coupon_prefix,cust_coup.customer_coupon_id),cust_coup.invoice_date) as text
                                from 
                                    tbl_customer_coupon cust_coup
                                inner join tbl_customers cust on cust.Customer_ID = cust_coup.Customer_ID
                                where 1=1
                                ' . $cond . '
                                order by cust_coup.invoice_date desc
                                limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function MergeCustomerCoupon() {
        $main_data = array();
        $this->db->query('START TRANSACTION');
        $coupons_query = $this->db->query('select concat_ws(",",coupon.Customer_ID,coupon.bone_type_id) as current_key,
            coupon.*,coupon.auto_id as auto_id_str from tbl_customer_coupon coupon 
            where coupon.invoice_date >= "2018-11-01" and coupon.invoice_date <= "2018-11-30" 
          /*  and auto_id in(1686,1755,1996,1690,1839,1995,2109)*/');
        while ($row = $this->db->assoc($coupons_query)) {
            if (!empty($main_data[$row['current_key']])) {
                $row['total_vat'] += $main_data[$row['current_key']]['total_vat'];
                $row['total_discount'] += $main_data[$row['current_key']]['total_discount'];
                $row['sub_total'] += $main_data[$row['current_key']]['sub_total'];
                $row['net_total'] += $main_data[$row['current_key']]['net_total'];
                $row['paid_amount'] += $main_data[$row['current_key']]['paid_amount'];
                $row['auto_id'] = $main_data[$row['current_key']]['auto_id'] . ',' . $row['auto_id'];
                $row['description'] = $main_data[$row['current_key']]['description'] . ' , ' . $row['description'];
            }
            $main_data[$row['current_key']] = $row;
        }
        //                echo"<pre>";die(print_r($main_data));
        //                $transaction_data = array();
        //        
        //        $transaction_coupon_credits_query = $this->db->query('select 
        //                                        transac.*,coupon.Customer_ID,
        //                                        coupon.bone_type_id,
        //                                        sum(Transaction_Amount) as total_credits,
        //                                        concat_ws(",",coupon.Customer_ID,coupon.bone_type_id) as current_key
        //                                    from tbl_accounting_transactions  transac
        //                                    inner join tbl_customer_coupon coupon  on transac.main_id = coupon.auto_id
        //                                     where transac.Transaction_Date >= "2018-11-01" 
        //                                     and transac.Transaction_Date <= "2018-11-30"
        //                                     and transac.main_type= "cco"
        //                                     and transac.Transaction_Type= "Credit" 
        //                                     group by coupon.Customer_ID,coupon.bone_type_id 
        //                                     '); 
        //        while($row= $this->db->assoc($transaction_coupon_credits_query)){
        //            $transaction_data[$row['current_key']]['total_credits'] = $row['total_credits'];
        //        }
        //        $transaction_coupon_debits_query = $this->db->query('select 
        //                                        transac.*,coupon.Customer_ID,
        //                                        coupon.bone_type_id,
        //                                        sum(Transaction_Amount) as total_debits,
        //                                        concat_ws(",",coupon.Customer_ID,coupon.bone_type_id) as current_key
        //                                    from tbl_accounting_transactions  transac
        //                                    inner join tbl_customer_coupon coupon  on transac.main_id = coupon.auto_id
        //                                     where transac.Transaction_Date >= "2018-11-01" 
        //                                     and transac.Transaction_Date <= "2018-11-30"
        //                                     and transac.main_type= "cco"
        //                                     and transac.Transaction_Type= "Debit" 
        //                                     group by coupon.Customer_ID,coupon.bone_type_id 
        //                                     '); 
        //        while($row= $this->db->assoc($transaction_coupon_debits_query)){
        //            $transaction_data[$row['current_key']]['total_debits'] = $row['total_debits'];
        //        }

        foreach ($main_data as $row) {
            $messages = explode(",", $row['auto_id']);
            //            echo '<pre>';
            //            print_r($row);die();
            $array_difference = array_diff($messages, [$row['auto_id_str']]);
            $row['auto_id'] = implode(",", $array_difference);

            if (!empty($array_difference)) {
                //                echo $row['auto_id_str'].'==';
                //            die();

                $this->db->prepare_Stmt('update tbl_customer_coupon 
                            set total_vat = ?,total_discount=?,sub_total=?,net_total=?,paid_amount=?
                            where auto_id = ?', [&$row['total_vat'], &$row['total_discount'], &$row['sub_total'], &$row['net_total'],
                    &$row['paid_amount'], &$row['auto_id_str']]);
                $this->db->prepare_Stmt('update tbl_customer_coupon_details set auto_id = ? where auto_id IN (' . $row['auto_id'] . ')', [&$row['auto_id_str']]);
                $this->db->prepare_Stmt('update tbl_accounting_transactions set main_id = ?  where main_type="cco" and main_id  In (' . $row['auto_id'] . ')', [&$row['auto_id_str']]);
                $this->db->prepare_Stmt('delete from  tbl_customer_coupon  where auto_id IN (' . $row['auto_id'] . ')');
                //                $error = $this->db->error();
                $transaction_coupon_debits_query = $this->db->query('select *,
                                                sum(Transaction_Amount)as trans_amount,
                                                GROUP_CONCAT(Transaction_Description separator " , ") as trans_desc
                                            from tbl_accounting_transactions  
                                           where main_type = "cco" and main_id = "' . $row['auto_id_str'] . '"
                                            group by Account_ID,Transaction_Type
                                             ');
                $transaction_array = array();
                while ($row_temp = $this->db->assoc($transaction_coupon_debits_query)) {
                    $transaction_array[] = $row_temp;
                }
                //                                echo '<pre>';print_r($transaction_array);
                $this->db->prepare_Stmt('delete from tbl_accounting_transactions where main_id = ?  and main_type="cco"', [&$row['auto_id_str']]);
                foreach ($transaction_array as $transaction) {
                    $dat_1 = ["JV_ID" => $transaction['JV_ID'],
                        "Transaction_Reference" => $transaction['Transaction_Reference'],
                        "Transaction_Account" => $transaction['Account_ID'],
                        "Transaction_Type" => $transaction['Transaction_Type'],
                        "Transaction_Amount" => $transaction['trans_amount'],
                        "Transaction_Date" => $transaction['Transaction_Date'],
                        "Currency_ID" => $transaction['Currency_ID'],
                        "Logged_ID" => $transaction['Logged_ID'],
                        "created_logged_id" => $transaction['created_logged_id'],
                        "Transaction_Description" => $transaction['trans_desc'],
                        "Current_Ex_Rate" => $transaction['Current_Ex_Rate'],
                        "second_currency_rate" => $transaction['second_currency_rate'],
                        "main_id" => $row['auto_id_str'],
                        "main_type" => $transaction['main_type'],
                        "branch_id" => $transaction['branch_id'],
                        "due_date" => $transaction['due_date'],
                        "main_nb" => $transaction['main_nb'],
                        "prefix" => $transaction['prefix'],
                        "job_id" => $transaction['job_id'],
                        "date_created" => $transaction['date_created'],
                        "date_time" => $transaction['date_time']
                    ];
                    JV::addTransaction($dat_1, 0);
                    //                    echo"<pre>";print_r($dat_1);
                }
            }
        }



        $error = $this->db->error();
        //                 echo '<pre>';
        //                print_r($error);
        //                die();
        if (empty($error)) {
            //            echo'heree';
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

}
