<?php

class SalesPurchase {

    /**
     * @var array Holds an array of data
     */
    protected $info = array();

    /**
     * @var bool Will be used to check whether the organization is added or not. TRUE when the organization is
     *      successfully created. Default value is FALSE
     */
    private $invoice;
    private $purchase;

    /**
     * @var obj Holds the database adapter object
     */
    private $db;
    private $year_id;
    private $year_id_actual;

    /**
     * The constructor will create an instant of the chosen database adapter (MySQL, MySQLi, etc)
     * and saves it in a private variable $db as an object to be used throughout the class.
     *
     * @author  Taha Sayyad <info@trustotech.com>
     * @uses DBConnectorFactory::loadDriver() to load the database adapter
     */
    public function __construct() {
        $this->db = DBConnectorFactory::loadDriver();
        $this->year_id = Session::getCurrentYearID();
        $this->year_id_actual = Session::getCurrentYearIDActual();
        $this->invoice = new Invoice();
        $this->purchase = new Purchase();
        $this->return = new ReturnInvoice();
    }

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

    public function insertSalesPurchase($is_edit = false) {
        //
        $sales_inv_id = null;
        $this->db->query('START TRANSACTION');
        //
        $purchase_array = array();
        foreach ($this->info['Products'] as $line_index => $item) {
            if (!empty($item['current_supplier'])) {
                $pur_account = !empty($item['purchase_account']) ? $item['purchase_account'] : smc_session()['user_accounts']['purchase_account_id'];
                $pur_indx = $item['current_supplier'] . '__' . $pur_account;
                if (!isset($purchase_array[$pur_indx])) {
                    if ($is_edit)
                        $purchase_array[$pur_indx]['Old_Depot_ID'] = $this->info['Old_Depot_ID'];
                    $purchase_array[$pur_indx]['vat1_total'] = 0;
                    $purchase_array[$pur_indx]['Purchase_Sub_Total'] = 0;
                    $purchase_array[$pur_indx]['Purchase_Net_Total'] = 0;
                    $purchase_array[$pur_indx]['Supplier_ID'] = $item['current_supplier'];
                    $purchase_array[$pur_indx]['Supplier_Name'] = $item['current_supplier_name'];
                    $purchase_array[$pur_indx]['Purchase_Date'] = $this->info['Invoice_Date'];
                    $purchase_array[$pur_indx]['Card_Amount'] = 0;
                    $purchase_array[$pur_indx]['Cash_Amount'] = 0;
                    $purchase_array[$pur_indx]['Check_Amount'] = 0;
                    $purchase_array[$pur_indx]['Card_Account'] = '';
                    $purchase_array[$pur_indx]['Cash_Account'] = smc_session()['user_accounts']['cash_account_id'];
                    $purchase_array[$pur_indx]['Check_Account'] = smc_session()['user_accounts']['cheque_account_id'];
                    $purchase_array[$pur_indx]['Purchase_Discount'] = 0;
                    $purchase_array[$pur_indx]['discount_amount'] = 0;
                    $purchase_array[$pur_indx]['extra_cost'] = 0;
                    $purchase_array[$pur_indx]['tax_amount'] = 0;
                    $purchase_array[$pur_indx]['Supplier_Invoice_ID'] = '';
                    $purchase_array[$pur_indx]['Invoice_VAT'] = 0;
                    $purchase_array[$pur_indx][0] = $this->info['Logged_ID'];
                    $purchase_array[$pur_indx]['shipping_account'] = smc_session()['user_accounts']['pur_ship_account_id'];
                    $purchase_array[$pur_indx]['shipping'] = 0;
                    $purchase_array[$pur_indx]['Account_Code'] = $pur_account;
                    $purchase_array[$pur_indx]['Currency_ID'] = $this->info['Invoice_Currency'];
                    $purchase_array[$pur_indx]['Purchase_Due_Date'] = $this->info['Invoice_Due_Date'];
                    $purchase_array[$pur_indx]['Depot_ID'] = $this->info['Depot_ID'];
                    $purchase_array[$pur_indx]['CurrencySymbole'] = $this->info['CurrencySymbole'];
                    $purchase_array[$pur_indx]['Supplier_Account'] = Supplier::getSupplierAccount($item['current_supplier']); //$item['current_supplier'];
                }
                $prod_total_price = ($item['Item_Quantity'] * $item['cost_price']);
                $purchase_array[$pur_indx]['Products'][] = ["Product_ID" => $item['Product_ID'],
                    "UOM_ID" => $item['UOM_ID'],
                    "Item_Quantity" => $item['Item_Quantity'],
                    "product_description" => $item['product_description'],
                    "Product_Total_Price" => $prod_total_price,
                    "Item_Price" => $item['cost_price'],
                    "vat_percent" => $item['pur_vat_percent'],
                    "vat_val" => ($item['pur_vat_amount'] * $item['Item_Quantity']),
                    "Item_Discount" => 0,
                    "sales_detail_id" => "",
                    "cost_percent" => 0,
                    "invoice_detail_line" => $line_index,
                    "iata_charges" => $item['iata_charges'],
                    "fare_basis" => $item['fare_basis'],
                    "taxes" => $item['taxes'],
                    "class_id" => $item['supp_class_id'],
                    "farebasis_disc" => $item['farebasis_disc']
                ];
                $purchase_array[$pur_indx]['vat1_total'] += ($item['pur_vat_amount'] * $item['Item_Quantity']);
                $purchase_array[$pur_indx]['Invoice_VAT'] += ($item['pur_vat_amount'] * $item['Item_Quantity']);
                $purchase_array[$pur_indx]['Purchase_Sub_Total'] += $prod_total_price;
                $purchase_array[$pur_indx]['Purchase_Net_Total'] = ($purchase_array[$pur_indx]['Purchase_Sub_Total'] + $purchase_array[$pur_indx]['Invoice_VAT']);
            }
        }
//        die(print_r($purchase_array));
        $purchase_inv_ids = array();
        $this->purchase->stop_transaction_start_end = true;
        $old_purchase_ids = array();
        if ($is_edit) {
            $query_old_pur = $this->db->query('select Purchase_ID_Auto,Supplier_ID,Account_ID from tbl_inventory_purchases where sales_purchase_id = "' . $this->info['Sales_Purchase_ID_Auto'] . '"');
            while ($row_pur = $this->db->assoc($query_old_pur)) {
                $pur_indx = $row_pur['Supplier_ID'] . '__' . $row_pur['Account_ID'];
                $old_purchase_ids[$pur_indx] = $row_pur['Purchase_ID_Auto'];
            }
        }
        foreach ($purchase_array as $pur_indx=>$purchaseData) {
//            $supplier_id = $purchaseData['Supplier_ID'];
//            $pur_indx = $purchaseData['Supplier_ID'] . '__' . $purchaseData['Account_Code'];
            if (isset($old_purchase_ids[$pur_indx])) {
                $purchaseData['Invoice_ID_Auto'] = $old_purchase_ids[$pur_indx];
//                echo"<pre>";
//                print_r($purchaseData);
//                echo'================================';
                $returnPurchase = $this->purchase->setData($purchaseData)->editPurchase(true);
                unset($old_purchase_ids[$pur_indx]);
            } else {
                $returnPurchase = $this->purchase->setData($purchaseData)->createInvoicePurchase(true);
            }
            if (!is_numeric($returnPurchase[0])) {
                $this->db->query('ROLLBACK');
                return $returnPurchase[0];
            }
//            die(print_r($returnPurchase));
            $purchase_inv_ids[] = $returnPurchase[0];
            //
            foreach ($returnPurchase[2] as $line_index => $purchase_detail_id)
                $this->info['Products'][$line_index]['purchase_detail_id'] = $purchase_detail_id;
        }
        //

        foreach ($old_purchase_ids as $old_pur) {
            $delete_purchase = $this->purchase->delete_purchase([$old_pur]);
            if (!empty($delete_purchase)) {
                $this->db->query('ROLLBACK');
                return $delete_purchase[0];
            }
        }
        if ($is_edit) {
            $sales_purchase_id = $this->info['Sales_Purchase_ID_Auto'];
            $query_nb = $this->db->query('select sales_purchase_number,Invoice_ID_Auto from tbl_sales_purchase where sales_purchase_id_auto = "' . $this->info['Sales_Purchase_ID_Auto'] . '"');
            $salesPurchaseAssoc = $this->db->assoc($query_nb);
            $salesPurchaseNb = $salesPurchaseAssoc['sales_purchase_number'];
            $this->info['Invoice_ID_Auto'] = $salesPurchaseAssoc['Invoice_ID_Auto'];
        }
        //
        $this->invoice->stop_transaction_start_end = true; //prevent auto start transaction rollback & commit
        if ($is_edit) {
//            $this->info['Invoice_ID_Auto'] = $invoice_auto_old;
            $submit_invoice = $this->invoice->setData($this->info)->editInvoice();
        } else {
            $submit_invoice = $this->invoice->setData($this->info)->insertSalesInvoice();
        }

        if (!is_numeric($submit_invoice[0])) {
            $this->db->query('ROLLBACK');
            return $submit_invoice;
        }
        $sales_inv_id = $submit_invoice[0];
        //
        //		insert salespurchase (max no +1) [salers pur]
        //			update sales, purchase save salespurchase auto id
        //				update purchase details with sales invoice product id...	
        //
       
        $purchase_inv_ids_values = null;
        if (!empty($purchase_inv_ids))
            $purchase_inv_ids_values = implode(",", $purchase_inv_ids);

        if ($is_edit) {
            $this->db->prepare_Stmt('update tbl_sales_purchase set Purchase_IDs_Auto = ? where sales_purchase_id_auto = ?', [&$purchase_inv_ids_values, &$this->info['Sales_Purchase_ID_Auto']]);
            $sales_purchase_id = $this->info['Sales_Purchase_ID_Auto'];
        } else {
            $salesPurchaseNb = self::getMaxSalesPurchase();
            $this->db->prepare_Stmt('insert into  tbl_sales_purchase (Invoice_ID_Auto,sales_purchase_number,Purchase_IDs_Auto)
											VALUES (?,?,?) ', [&$sales_inv_id,
                &$salesPurchaseNb,
                &$purchase_inv_ids_values]);
            $sales_purchase_id = $this->db->id();
        }

        $this->db->prepare_Stmt('update tbl_invoices set sales_purchase_id = ? ,is_main_transfer = 0
									 where Invoice_ID_Auto = ?', [&$sales_purchase_id,
            &$sales_inv_id]);
        if (!empty($purchase_inv_ids_values))
            $this->db->prepare_Stmt('update tbl_inventory_purchases set sales_purchase_id = ? 
									 where Purchase_ID_Auto IN (' . $purchase_inv_ids_values . ')', [&$sales_purchase_id]);

        $error = $this->db->error();
//		print_r($error);
        if (empty($error)) {
            $this->db->query("COMMIT");
            return [$sales_inv_id, $salesPurchaseNb];
//			return [$sales_inv_id,$purchase_inv_ids];
        } else {
            $this->db->query("ROLLBACK");
            return array_unique($error);
        }
    }

    public function getMaxSalesPurchase($is_return = false) {
        $cond = $is_return ? ' where is_return  = 1' : ' where is_return = 0';
        $query = $this->db->query('SELECT MAX(sales_purchase_number) AS id 
            FROM tbl_sales_purchase ' . $cond);
        $row = $this->db->assoc($query);
        if (!empty($row))
            $salesPurchase = $row['id'] + 1;
        else
            $salesPurchase = 1;
        return $salesPurchase;
    }

    public function getSalesPurchaseByID($id) {
        $data = array();
        $query_main = $this->db->query('select 
                                            i.current_year_id,
                                            s.sales_purchase_number AS ID,
                                            sales_purchase_id_auto AS Auto_ID,
                                            i.Invoice_ID_Auto as inv_auto_id,
                                            i.Invoice_Date AS Date,
                                            i.Invoice_Due_Date AS Due_Date,
                                            i.Invoice_Discount AS Discount,
                                            i.discount_amount,
                                            i.Invoice_VAT AS VAT,
                                            i.Invoice_Sub_Total AS Sub_Total,
                                            i.Invoice_Net_Total AS Net_Total,
                                            i.Invoice_Paid_Amount AS Paid_Amount,
                                            i.Invoice_Currency as Currency_ID,
                                            i.is_main_transfer,
                                            cu.Currency_Symbol,
                                            cu.Currency_Name,
                                            cu.currency_round,
                                            i.Depot_ID,
                                            i.truck,
                                            i.Customer_ID,
                                            i.Invoice_Shipping as shipping,
                                            i.Class_ID,
                                            c.Class_Name,
                                            i.Account_ID,coa.Account_Code,coa.Account_Name,i.Sales_ID,
                                            CONCAT(e.Employee_FirstName, " ", e.Employee_MiddleName, " ", e.Employee_LastName) AS Employee_Name
                                        from tbl_sales_purchase s join tbl_invoices i 
                                        on i.Invoice_ID_Auto = s.Invoice_ID_Auto 
                                        join tbl_chart_of_accounts coa on coa.Account_ID = i.Account_ID
                                        join tbl_common_currency cu on cu.Currency_ID = i.Invoice_Currency
                                        join tbl_pricing_class c on c.Class_ID = i.Class_ID
                                        left join tbl_hrm_employee e on e.Employee_ID = i.Sales_ID
                                        where s.sales_purchase_id_auto = "' . smc_escape($id) . '" and i.current_year_id = "' . $this->year_id . '"');
        $row = $this->db->assoc($query_main);
        $data['Main'] = $row;
        $acc_query = $this->db->query("SELECT 
                                            p.Account_Name,
                                            p.Account_Code,
                                            p.Account_ID,
                                             t.Transaction_Reference
                                        FROM 
                                            tbl_accounting_transactions t
                                        LEFT JOIN 
                                            tbl_chart_of_accounts p
                                        ON 
                                            t.Account_ID = p.Account_ID
                                       WHERE 
                                            t.main_id = '" . $data['Main']['inv_auto_id'] . "'
                                        AND 
                                            t.main_type = 'inv'
                                        AND 
                                        t.Transaction_Reference = 'ship'
                                        ");


        while ($acc_row = $this->db->assoc($acc_query)) {
            $data['Main']['shipping_account'] = '';
            $data['Main']['shipping_name'] = '';
//            if ($acc_row['Transaction_Reference'] === 'ship') {
            $data['Main']['shipping_account'] = $acc_row['Account_Code'];
            $data['Main']['shipping_id'] = $acc_row['Account_ID'];
            $data['Main']['shipping_name'] = $acc_row['Account_Name'];
//            } else {
//                $data['Main']['Account_Code'] = $acc_row['Account_Code'];
//                $data['Main']['Account_Name'] = $acc_row['Account_Name'];
//                $data['Main']['Account_ID'] = $acc_row['Account_ID'];
//            }
        }
        $data['Person'] = Customer::getCustomerByID($data['Main']['Customer_ID']);
        $purchase_arr = array();
        $query_purchase_detail = $this->db->query('select d.*,p.Supplier_ID,s.Supplier_Name,
                                    ifnull(pc.Class_Name,"") as supp_class_name,
                                    p.Account_ID as purchase_account_id,
                                    coa.Account_Name as purchase_account_name
                                                 from   tbl_inventory_purchase_items d
                                                join tbl_inventory_purchases p 
                                                on p.Purchase_ID_Auto = d.Purchase_ID_Auto
                                                join tbl_stock_suppliers s on s.Supplier_ID = p.Supplier_ID
                                                left join tbl_pricing_class pc on pc.Class_ID = d.supp_class_id
                                                join tbl_chart_of_accounts coa on coa.Account_ID = p.Account_ID
                                                where p.sales_purchase_id = "' . smc_escape($id) . '"');
        while ($row_pur = $this->db->assoc($query_purchase_detail)) {
            $purchase_arr[$row_pur['Item_ID']] = $row_pur;
        }
//        echo"<pre>";
//        die(print_r($purchase_arr));
        $query_details = $this->db->query('select
                                                p.Invoice_ID_Auto AS Invoice_ID_Auto,
                                                i.Item_Quantity_Current AS Item_Quantity_Current,
                                                ps.Product_Code AS Code,
                                                ps.Product_Name AS Name,
                                                if(i.vat_percent <> 0,1,0) AS VAT,
                                                ps.Product_Quantity,
                                                ps.Status_ID,
                                                i.Product_ID AS Product_ID,
                                                i.vat_percent,
                                                i.vat_amount,
                                                i.unit_cost,
                                                i.product_description,
                                                i.purchase_detail_id,
                                                uc.Bar_Code,
                                                i.Product_Discount AS Product_Discount,
                                                i.Product_Net_Price AS Product_Net_Price,
                                                i.Product_Total_Price AS Product_Total_Price,
                                                (i.Product_Quantity/i.Item_Quantity_Current) AS uom_con,
                                                 i.UOM_ID,
                                                 if(isnull(l.Product_Depot_Quantity),0,l.Product_Depot_Quantity)/uc.conversion_amount AS Quantity,
                                                GROUP_CONCAT(uom_from separator "/") AS Product_UOM,
                                                "" as Supplier_ID,
                                                "" as Supplier_Name,
                                                0 as cost_price,
                                                0 as Purchase_ID_Auto,ps.is_service,ps.is_ticket
                                                
                                            from 
                                                tbl_invoice_products_details i 
                                            left join 
                                                tbl_invoices p 
                                            on
                                                p.Invoice_ID_Auto = i.Invoice_ID_Auto
                                            LEFT JOIN
                                                uom_conversions uc
                                            ON 
                                                uc.Product_ID = i.Product_ID
                                            left join 
                                                tbl_stock_products ps 
                                            on
                                                ps.Product_ID = i.Product_ID
                                                left  join tbl_stock_product_location l on l.Location_Product_ID = i.Product_ID and l.Product_Depot_ID = "' . $data['Main']['Depot_ID'] . '"
                                            where
                                                p.current_year_id = "' . $this->year_id . '"
                                            and 
                                                p.Invoice_ID_Auto = "' . $data['Main']['inv_auto_id'] . '"
                                            GROUP BY i.Entery_ID
                                            order by p.Invoice_ID

                                            ');
        //        $query_details = $this->db->query('select * from invoice_details where Invoice_ID_Auto = "'.smc_escape($id).'"');


        while ($row = $this->db->assoc($query_details)) {
            if (!empty($row['purchase_detail_id'])) {
//                echo'heree';
                $row['Supplier_ID'] = $purchase_arr[$row['purchase_detail_id']]['Supplier_ID'];
                $row['Supplier_Name'] = $purchase_arr[$row['purchase_detail_id']]['Supplier_Name'];
                $row['purchase_account_name'] = $purchase_arr[$row['purchase_detail_id']]['purchase_account_name'];
                $row['purchase_account_id'] = $purchase_arr[$row['purchase_detail_id']]['purchase_account_id'];
                $row['cost_price'] = $purchase_arr[$row['purchase_detail_id']]['Item_Price'];
                $row['farebasis'] = $purchase_arr[$row['purchase_detail_id']]['farebasis'];
                $row['taxes'] = $purchase_arr[$row['purchase_detail_id']]['taxes'];
                $row['iata_charges'] = $purchase_arr[$row['purchase_detail_id']]['iata_charges'];
                $row['farebasis_disc'] = $purchase_arr[$row['purchase_detail_id']]['farebasis_disc'];
                $row['supp_class_id'] = $purchase_arr[$row['purchase_detail_id']]['supp_class_id'];
                $row['supp_class_name'] = $purchase_arr[$row['purchase_detail_id']]['supp_class_name'];
                $row['Purchase_ID_Auto'] = $purchase_arr[$row['purchase_detail_id']]['Purchase_ID_Auto'];
                $row['pur_vat_amount'] = $purchase_arr[$row['purchase_detail_id']]['vat_amount'];
                $row['pur_vat_percent'] = $purchase_arr[$row['purchase_detail_id']]['vat_percent'];
            }
            $data['Details'][] = $row;
        }
        $query = $this->db->query("SELECT 
                                            t.Transaction_Amount,
                                            t.Transaction_Reference,
                                            t.Account_ID,
                                            p.Account_Name AS P_Name
                                        FROM 
                                            tbl_accounting_transactions t
                                        LEFT JOIN 
                                            tbl_chart_of_accounts p
                                        ON 
                                            t.Account_ID = p.Account_ID
                                       WHERE 
                                             t.main_id = '" . $data['Main']['inv_auto_id'] . "'
                                        AND 
                                            t.main_type = 'inv'
                                        AND 
                                            t.Transaction_Reference in ('cash','check','credit')
                                        ");
        $data['Payment'] = array();
        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;
        }
//                            echo"<pre>";
//                            die(print_r($data));
        return $data;
    }

    public function insertSalesPurchaseReturn($is_edit = false) {
        //
//        die('heree');
        $sales_inv_id = null;
        $this->db->query('START TRANSACTION');
        //
        $purchase_array = array();
        foreach ($this->info['Products'] as $line_index => $item) {
            if (!empty($item['current_supplier'])) {
                $pur_account = !empty($item['purchase_account']) ? $item['purchase_account'] : smc_session()['user_accounts']['purchase_account_id'];
                $pur_indx = $item['current_supplier'] . '_' . $pur_account;
                if (!isset($purchase_array[$pur_indx])) {
                    if ($is_edit)
                        $purchase_array[$pur_indx]['Old_Depot_ID'] = $this->info['Old_Depot_ID'];
                    $purchase_array[$pur_indx]['Return_Invoice_Total'] = 0;
                    $purchase_array[$pur_indx]['Purchase_Net_Total'] = 0;
                    $purchase_array[$pur_indx]['Person_ID'] = $item['current_supplier'];
                    $purchase_array[$pur_indx]['Return_Invoice_Date'] = $this->info['Return_Invoice_Date'];
                    $purchase_array[$pur_indx]['Card_Amount'] = 0;
                    $purchase_array[$pur_indx]['Cash_Amount'] = 0;
                    $purchase_array[$pur_indx]['Check_Amount'] = 0;
                    $purchase_array[$pur_indx]['Card_Account'] = '';
                    $purchase_array[$pur_indx]['Cash_Account'] = smc_session()['user_accounts']['cash_account_id'];
                    $purchase_array[$pur_indx]['Check_Account'] = smc_session()['user_accounts']['cheque_account_id'];
                    $purchase_array[$pur_indx]['discount_percent'] = 0;
                    $purchase_array[$pur_indx]['discount_amount'] = 0;
                    $purchase_array[$pur_indx]['tax_amount'] = 0;
                    $purchase_array[$pur_indx]['Invoice_VAT'] = 0;
                    $purchase_array[$pur_indx]['vat1_total'] = 0;
                    $purchase_array[$pur_indx]['Is_Customer_Invoice'] = 0;
                    $purchase_array[$pur_indx]['Return_Invoice_Note'] = '';
                    $purchase_array[$pur_indx]['Class_ID'] = null;
                    $purchase_array[$pur_indx]['Account_Code'] = $pur_account;
                    $purchase_array[$pur_indx]['Currency_ID'] = $this->info['Currency_ID'];
                    $purchase_array[$pur_indx]['Depot_ID'] = $this->info['Depot_ID'];
                    $purchase_array[$pur_indx]['Person_Account'] = Supplier::getSupplierAccount($item['current_supplier']); //$item['current_supplier'];
                    $purchase_array[$pur_indx]['Is_Actual'] = 0; //$item['current_supplier'];
                }
                $prod_total_price = ($item['Return_Product_Quantity'] * $item['cost_price']);
                $purchase_array[$pur_indx]['Products'][] = ["Product_ID" => $item['Product_ID'],
                    "UOM_ID" => $item['UOM_ID'],
                    "Return_Product_Quantity" => $item['Item_Quantity'],
                    "product_description" => $item['product_description'],
                    "Return_Product_Total" => $prod_total_price,
                    "Unit_Cost" => $item['cost_price'],
                    "vat_percent" => $item['pur_vat_percent'],
                    "vat_val" => ($item['pur_vat_amount'] * $item['Item_Quantity']),
                    "Item_Discount" => 0,
                    "return_detail_id" => "",
                    "invoice_detail_line" => $line_index
                ];
                $purchase_array[$pur_indx]['Invoice_VAT'] += ($item['pur_vat_amount'] * $item['Item_Quantity']);
                $purchase_array[$pur_indx]['vat1_total'] += ($item['pur_vat_amount'] * $item['Item_Quantity']);
                $purchase_array[$pur_indx]['Return_Invoice_Total'] += $prod_total_price;
                $purchase_array[$pur_indx]['Purchase_Net_Total'] = ($purchase_array[$pur_indx]['Return_Invoice_Total'] + $purchase_array[$pur_indx]['Invoice_VAT']);
            }
        }
//        echo"<pre>";
//        die(print_r($this->info));
        $purchase_inv_ids = array();
        $this->purchase->stop_transaction_start_end = true;
        $old_purchase_ids = array();
        if ($is_edit) {
            $query_old_pur = $this->db->query('select Return_Invoice_ID,Person_ID,Account_ID from tbl_accounting_return_invoices where sales_purchase_id = "' . $this->info['Sales_Purchase_ID_Auto'] . '" and Is_Customer_Invoice = 0 ');
            while ($row_pur = $this->db->assoc($query_old_pur)) {
                $pur_indx = $row_pur['Person_ID'] . '_' . $row_pur['Account_ID'];
                $old_purchase_ids[$pur_indx] = $row_pur['Return_Invoice_ID'];
            }
        }

        foreach ($purchase_array as $pur_indx => $purchaseData) {

            if (isset($old_purchase_ids[$pur_indx])) {
                $purchaseData['Return_ID'] = $old_purchase_ids[$pur_indx];
//                echo"<pre>";
//                die(print_r($purchaseData));
                $returnPurchase = $this->return->setData($purchaseData)->editReturnNoInvoice(true);
                unset($old_purchase_ids[$pur_indx]);
            } else {
//                if ($this->info['is_return'] == 1)
                $returnPurchase = $this->return->setData($purchaseData)->ReturnNoInvoice(true);
//                else
//                    $returnPurchase = $this->purchase->setData($purchaseData)->createInvoicePurchase(true);
            }
//                    $error = $this->db->error();
//            die(print_r($error));
            if (!is_numeric($returnPurchase[0])) {
                $this->db->query('ROLLBACK');
                return $returnPurchase[0];
            }
//            die(print_r($returnPurchase));
            $purchase_inv_ids[] = $returnPurchase[0];
            //
            foreach ($returnPurchase[2] as $line_index => $purchase_detail_id)
                $this->info['Products'][$line_index]['purchase_detail_id'] = $purchase_detail_id;
        }

        //
// echo"<pre>";
//        die(print_r($purchase_inv_ids));
        foreach ($old_purchase_ids as $old_pur) {
            $delete_purchase = $this->return->deleteReturn([$old_pur]);
            if (!empty($delete_purchase)) {
                $this->db->query('ROLLBACK');
                return $delete_purchase[0];
            }
        }

        if ($is_edit) {
            $sales_purchase_id = $this->info['Sales_Purchase_ID_Auto'];
            $query_nb = $this->db->query('select sales_purchase_number,Invoice_ID_Auto from tbl_sales_purchase where sales_purchase_id_auto = "' . $this->info['Sales_Purchase_ID_Auto'] . '"');
            $salesPurchaseAssoc = $this->db->assoc($query_nb);
            $salesPurchaseNb = $salesPurchaseAssoc['sales_purchase_number'];
            $this->info['Return_ID'] = $salesPurchaseAssoc['Invoice_ID_Auto'];
        }
//         $error = $this->db->error();
//            die(print_r($error));
        //
        $this->invoice->stop_transaction_start_end = true; //prevent auto start transaction rollback & commit
        if ($is_edit) {
//            $this->info['Invoice_ID_Auto'] = $invoice_auto_old;
            $submit_invoice = $this->return->setData($this->info)->editReturnNoInvoice(true);
        } else {
//            if ($this->info['is_return'] == 1)
            $submit_invoice = $this->return->setData($this->info)->ReturnNoInvoice(true);
//            else
//                $submit_invoice = $this->invoice->setData($this->info)->insertSalesInvoice();
        }
//$error = $this->db->error();
//            die(print_r($error));
        if (!is_numeric($submit_invoice[0])) {
            $this->db->query('ROLLBACK');
            return $submit_invoice;
        }
        $sales_inv_id = $submit_invoice[0];
        //
        //		insert salespurchase (max no +1) [salers pur]
        //			update sales, purchase save salespurchase auto id
        //				update purchase details with sales invoice product id...	
        //
       
        $purchase_inv_ids_values = null;
        if (!empty($purchase_inv_ids))
            $purchase_inv_ids_values = implode(",", $purchase_inv_ids);

        if ($is_edit) {
            $this->db->prepare_Stmt('update tbl_sales_purchase set Purchase_IDs_Auto = ? where sales_purchase_id_auto = ?', [&$purchase_inv_ids_values, &$this->info['Sales_Purchase_ID_Auto']]);
            $sales_purchase_id = $this->info['Sales_Purchase_ID_Auto'];
        } else {
            $salesPurchaseNb = self::getMaxSalesPurchase(true);
            $this->db->prepare_Stmt('insert into  tbl_sales_purchase (Invoice_ID_Auto,sales_purchase_number,Purchase_IDs_Auto,is_return)
											VALUES (?,?,?,?) ', [&$sales_inv_id,
                &$salesPurchaseNb,
                &$purchase_inv_ids_values,
                &$this->info['is_return']]);
            $sales_purchase_id = $this->db->id();
        }
//        echo $sales_purchase_id.'===';die();
//        if ($this->info['is_return'] == 1) {
        $purchase_inv_ids[] = $sales_inv_id;
        $this->db->prepare_Stmt('update tbl_accounting_return_invoices set sales_purchase_id = ?,is_main_transfer = 0
                where Return_Invoice_ID IN (' . implode(",", $purchase_inv_ids) . ')', [&$sales_purchase_id]);
//        } 
//        else {
//            $this->db->prepare_Stmt('update tbl_invoices set sales_purchase_id = ? 
//									 where Invoice_ID_Auto = ?', [&$sales_purchase_id,
//                &$sales_inv_id]);
//            if (!empty($purchase_inv_ids_values))
//                $this->db->prepare_Stmt('update tbl_inventory_purchases set sales_purchase_id = ? 
//									 where Purchase_ID_Auto IN (' . $purchase_inv_ids_values . ')', [&$sales_purchase_id]);
//        }
        $error = $this->db->error();
//		print_r($error);
        if (empty($error)) {
            $this->db->query("COMMIT");
            return [$sales_purchase_id, $salesPurchaseNb];
//			return [$sales_inv_id,$purchase_inv_ids];
        } else {
            $this->db->query("ROLLBACK");
            return array_unique($error);
        }
    }

    public function getSalesPurchaseReturnByID($id) {
        $data = array();
        $query_main = $this->db->query('select 
                                            i.current_year_id,
                                            s.sales_purchase_number AS ID,
                                            sales_purchase_id_auto AS Auto_ID,
                                            i.Return_Invoice_ID as inv_auto_id,
                                            i.Return_Invoice_Date AS Date,
                                            i.Return_Invoice_Date AS Due_Date,
                                            i.discount_percent AS Discount,
                                            i.discount_amount,
                                            i.Invoice_VAT AS VAT,
                                            i.Return_Invoice_Total AS Sub_Total,
                                            i.Return_Net_Total AS Net_Total,
                                            i.Currency_ID,
                                            i.is_main_transfer,
                                            cu.Currency_Symbol,
                                            cu.Currency_Name,
                                            cu.currency_round,
                                            i.Depot_ID,
                                            i.Person_ID as Customer_ID,
                                            i.Class_ID,
                                            c.Class_Name,
                                            i.Account_ID,coa.Account_Code,coa.Account_Name
                                        from tbl_sales_purchase s join tbl_accounting_return_invoices i 
                                        on i.Return_Invoice_ID = s.Invoice_ID_Auto 
                                        join tbl_chart_of_accounts coa on coa.Account_ID = i.Account_ID
                                        join tbl_common_currency cu on cu.Currency_ID = i.Currency_ID
                                        join tbl_pricing_class c on c.Class_ID = i.Class_ID
                                        where s.sales_purchase_id_auto = "' . smc_escape($id) . '" and i.current_year_id = "' . $this->year_id . '"');
        $row = $this->db->assoc($query_main);
        $data['Main'] = $row;
        $acc_query = $this->db->query("SELECT 
                                            p.Account_Name,
                                            p.Account_Code,
                                            p.Account_ID,
                                             t.Transaction_Reference
                                        FROM 
                                            tbl_accounting_transactions t
                                        LEFT JOIN 
                                            tbl_chart_of_accounts p
                                        ON 
                                            t.Account_ID = p.Account_ID
                                       WHERE 
                                            t.main_id = '" . $data['Main']['inv_auto_id'] . "'
                                        AND 
                                            t.main_type = 'rin'
                                        AND 
                                        t.Transaction_Reference = 'ret'
                                        ");


        while ($acc_row = $this->db->assoc($acc_query)) {
            $data['Main']['shipping_account'] = '';
            $data['Main']['shipping_name'] = '';

            $data['Main']['Account_Code'] = $acc_row['Account_Code'];
            $data['Main']['Account_Name'] = $acc_row['Account_Name'];
            $data['Main']['Account_ID'] = $acc_row['Account_ID'];
        }
        $data['Person'] = Customer::getCustomerByID($data['Main']['Customer_ID']);
        $purchase_arr = array();
        $query_purchase_detail = $this->db->query('select d.*,p.Person_ID as Supplier_ID,s.Supplier_Name,
                        coa.Account_ID as purchase_account_id,coa.Account_Name as purchase_account_name
                                                 from   tbl_accounting_return_items d
                                                join  tbl_accounting_return_invoices p 
                                                on p.Return_Invoice_ID = d.Return_Invoice_ID
                                                join tbl_stock_suppliers s on s.Supplier_ID = p.Person_ID
                                                join tbl_chart_of_accounts coa on coa.Account_ID = p.Account_ID
                                                where p.sales_purchase_id = "' . smc_escape($id) . '"');
        while ($row_pur = $this->db->assoc($query_purchase_detail)) {
            $purchase_arr[$row_pur['Return_Item_ID']] = $row_pur;
        }
//        echo"<pre>";
//        die(print_r($purchase_arr));
        $query_details = $this->db->query('select
                                                p.Return_Invoice_ID AS Invoice_ID_Auto,
                                                i.Item_Quantity_Current AS Item_Quantity_Current,
                                                ps.Product_Code AS Code,
                                                ps.Product_Name AS Name,
                                                if(i.vat_percent <> 0,1,0) AS VAT,
                                                ps.Product_Quantity,
                                                ps.Status_ID,
                                                i.Product_ID AS Product_ID,
                                                i.vat_percent,
                                                i.vat_amount,
                                                i.unit_cost,
                                                i.product_description,
                                                i.return_detail_id,
                                                uc.Bar_Code,
                                                i.Product_Discount AS Product_Discount,
                                                i.unit_price AS Product_Net_Price,
                                                i.Return_Product_Total AS Product_Total_Price,
                                                (i.Return_Product_Quantity/i.Item_Quantity_Current) AS uom_con,
                                                 i.UOM_ID,
                                                 if(isnull(l.Product_Depot_Quantity),0,l.Product_Depot_Quantity)/uc.conversion_amount AS Quantity,
                                                GROUP_CONCAT(uom_from separator "/") AS Product_UOM,
                                                "" as Supplier_ID,
                                                "" as Supplier_Name,
                                                0 as cost_price,
                                                0 as Purchase_ID_Auto,ps.is_service,ps.is_ticket
                                                
                                            from 
                                                tbl_accounting_return_items i 
                                            left join 
                                                tbl_accounting_return_invoices p 
                                            on
                                                p.Return_Invoice_ID = i.Return_Invoice_ID
                                            LEFT JOIN
                                                uom_conversions uc
                                            ON 
                                                uc.Product_ID = i.Product_ID
                                            left join 
                                                tbl_stock_products ps 
                                            on
                                                ps.Product_ID = i.Product_ID
                                                left  join tbl_stock_product_location l on l.Location_Product_ID = i.Product_ID and l.Product_Depot_ID = "' . $data['Main']['Depot_ID'] . '"
                                            where
                                                p.current_year_id = "' . $this->year_id . '"
                                            and 
                                                p.Return_Invoice_ID = "' . $data['Main']['inv_auto_id'] . '"
                                            GROUP BY i.Return_Item_ID
                                            order by p.Return_ID

                                            ');
        //        $query_details = $this->db->query('select * from invoice_details where Invoice_ID_Auto = "'.smc_escape($id).'"');


        while ($row = $this->db->assoc($query_details)) {
//            echo"<pre>";
//            die(print_r( $purchase_arr));
            if (!empty($row['return_detail_id'])) {
//                echo'heree';
                $row['Supplier_ID'] = $purchase_arr[$row['return_detail_id']]['Supplier_ID'];
                $row['Supplier_Name'] = $purchase_arr[$row['return_detail_id']]['Supplier_Name'];
                $row['purchase_account_name'] = $purchase_arr[$row['return_detail_id']]['purchase_account_name'];
                $row['purchase_account_id'] = $purchase_arr[$row['return_detail_id']]['purchase_account_id'];
                $row['cost_price'] = $purchase_arr[$row['return_detail_id']]['unit_price'];
                $row['Purchase_ID_Auto'] = $purchase_arr[$row['return_detail_id']]['Return_Invoice_ID'];
                $row['pur_vat_amount'] = $purchase_arr[$row['return_detail_id']]['vat_amount'];
                $row['pur_vat_percent'] = $purchase_arr[$row['return_detail_id']]['vat_percent'];
            }
            $data['Details'][] = $row;
        }
        $query = $this->db->query("SELECT 
                                            t.Transaction_Amount,
                                            t.Transaction_Reference,
                                            t.Account_ID,
                                            p.Account_Name AS P_Name
                                        FROM 
                                            tbl_accounting_transactions t
                                        LEFT JOIN 
                                            tbl_chart_of_accounts p
                                        ON 
                                            t.Account_ID = p.Account_ID
                                       WHERE 
                                             t.main_id = '" . $data['Main']['inv_auto_id'] . "'
                                        AND 
                                            t.main_type = 'rin'
                                        AND 
                                            t.Transaction_Reference in ('cash','check','credit')
                                        ");
        $data['Payment'] = array();
        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;
        }
        //                    echo"<pre>";
        //                    die(print_r($data));
        return $data;
    }

    public function delete_sales_purchase($id) {
        $this->db->query('START TRANSACTION');
        $query = $this->db->query('select * from tbl_sales_purchase where sales_purchase_id_auto = "' . $id . '"');
        $row = $this->db->assoc($query);
        $pur_id = explode(',', $row['Purchase_IDs_Auto']);
        $inv_id = $row['Invoice_ID_Auto'];
//        echo $pur_id.'==='.$inv_id.'==';
        $invoice = new Invoice;
        $invoice->stop_transaction_start_end = true;
        $invoice->deleteSalesInvoice([$inv_id]);
        $purchase = new Purchase;
        $purchase->stop_transaction_start_end = true;

        $purchase->delete_purchase($pur_id);
        $this->db->prepare_Stmt('delete from tbl_sales_purchase where sales_purchase_id_auto = ?', [&$id]);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function delete_return_sales_purchase($id) {
        $this->db->query('START TRANSACTION');
        $query = $this->db->query('select * from tbl_sales_purchase where sales_purchase_id_auto = "' . $id . '"');
        $row = $this->db->assoc($query);
        $pur_id = explode(',', $row['Purchase_IDs_Auto']);
        $inv_id = $row['Invoice_ID_Auto'];
//        echo $pur_id.'==='.$inv_id.'==';
        $return = new ReturnInvoice;
        $return->deleteReturn([$inv_id], true);
        $return->deleteReturn($pur_id, true);
        $this->db->prepare_Stmt('delete from tbl_sales_purchase where sales_purchase_id_auto = ?', [&$id]);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

}
