<?php

define('CMS__APP_VERSION', 1.1);
header('Content-Type: application/json');

class app extends app_base_class {

    public $def_uom_ids = array();

    /**
     * Functions for send data to App. from server
     * ====================================================================>
     */
	 //
	 public function get_current_year() {
        $year_arr = $this->select_query("select * from tbl_common_current_year where is_current = 1");
        die(json_encode(array('data' => $year_arr)));
    }
	 
	 
    //
    public function get_payment_accounts() {
        $arr = array();
        foreach (array('cash' => 'app_cash_pay_accounts', 'cheque' => 'app_cheque_pay_accounts') as $pay_type => $pay_stngs) {
            $stmt = $this->db->query_execute("select setting_value from tbl_default_settings where setting_name = '{$pay_stngs}' ");
            $tmp_stng_row = $this->db->query_fetch($stmt);
            if (!empty($tmp_stng_row['setting_value'])) {
                $stmt = $this->db->query_execute("select Account_ID as payment_account_id, Account_Name as payment_account_name 
                    from tbl_chart_of_accounts where Account_ID in ({$tmp_stng_row['setting_value']}) ");
                while ($tmp_row = $this->db->query_fetch($stmt)) {
                    $tmp_row['payment_account_type'] = $pay_type;
                    $arr[] = $tmp_row;
                }
            }
        }
        die(json_encode(array('data' => $arr)));
    }

    //
    public function get_uoms() {
        $uoms_arr = $this->select_query("select UOM_ID as uom_ID, UOM_Name as uom_name from tbl_stock_uom");
        die(json_encode(array('data' => $uoms_arr)));
    }

    //
    public function get_products() {
        //below we get qty. depend on user depot, we make group by just in case if there is more than one row, but it must be max one row
        $products_arr = $this->select_query("select Product_ID, Product_Code, Product_Name, sum(if(isnull(prod_loc.Product_Depot_ID),0,Product_Depot_Quantity)) as Product_Quantity,vat_percent as Product_Vat , force_change_price from tbl_stock_products prod
                left join tbl_stock_product_location prod_loc on (prod.Product_ID = prod_loc.Location_Product_ID and prod_loc.Product_Depot_ID = ?) where product_type in (0,1)
            group by prod.Product_ID ", array($this->get_login_data('depot_id')));
        die(json_encode(array('data' => $products_arr)));
    }

    //
    public function get_products_uoms() {
        $products_prices_arr = $this->select_query("select `uom_from` as from_uom_ID, `conversion_amount` as conversion_amount, `uom_to` as to_uom_ID
            , `Product_ID` as product_ID, `is_default`, `Bar_Code` as barcode, `additional_barcodes` as additional_barcode FROM `tbl_uom_conversions` ");
        die(json_encode(array('data' => $products_prices_arr)));
    }

    //
    public function get_products_prices() {
        $stmt = $this->db->query_execute("select is_ttc,Class_ID from tbl_pricing_class where is_virtual = 0 and type=0");
        $class_arr = array();
        while ($row = $this->db->query_fetch($stmt))
            $class_arr[$row['Class_ID']] = $row['is_ttc'];
        //
        $stmt = $this->db->query_execute("select Product_VAT,Product_ID,vat_percent from tbl_stock_products");
        $prod_arr = array();
        while ($row = $this->db->query_fetch($stmt))
            $prod_arr[$row['Product_ID']] = $row;
        //
        $stmt = $this->db->query_execute("select *, `UOM_ID` as uom_id from tbl_item_price_by_classes where Class_ID in (" . implode(',', array_keys($class_arr)) . ") ");
        while ($row = $this->db->query_fetch($stmt)) {
            if ($class_arr[$row['Class_ID']] == 1 && $prod_arr[$row['Product_ID']]['Product_VAT'] == 1)
                $row['Product_Price'] = $row['Product_Price'] / (1 + ($prod_arr[$row['Product_ID']]['vat_percent'] / 100));
            $products_prices_arr[] = $row;
        }
        die(json_encode(array('data' => $products_prices_arr)));
    }

    //
    public function get_customers() {
        $this->fill_trusto_session();
        //
        $customer = new Customer;
        $arr = array();
        //get customers related to the current login user, there is session allow_all_customers which will get all customer for all users
        //but in this case user may not get correct balance since another user may already create invoice, payment for this customer
        //and will not be able to make payment in invoice since that invoice maybe created by another user already.. but this case where
        //we activate this settings needed from customer where he need only sales invoice no need for real invoice or payment..
        if (smc_session()['customer_code_in_app']) {
            $query = "select c.Customer_ID,  concat(a.Account_Code,' - ',c.Customer_Name) as Customer_Account_Name,c.Customer_Name, c.Customer_Company
                , c.Customer_Mobile, c.Customer_Address, c.Customer_Lat, c.Customer_Lng, c.Class_ID, c.Customer_Discount
                , if(stop_invoice=1,c.Customer_Limit_Balance,null) as Customer_Limit_Balance, c.Currency_ID, c.customer_due_date_period 
            from tbl_customers c
                inner join tbl_chart_of_accounts a on a.main_id = c.Customer_ID
            where a.main_type='cust' and a.sub_type='main' 
                " . ($this->get_login_data('allow_all_customers') == 1 ? " " : " and Customer_ID in (select customer_id from tbl_user_customers where user_id = '{$this->get_login_user_id()}') ") . " ";
        } else {
            $query = "select Customer_ID, Customer_Name, Customer_Company, Customer_Mobile, Customer_Address, Customer_Lat, Customer_Lng, Class_ID
                , Customer_Discount, if(stop_invoice=1,Customer_Limit_Balance,null) as Customer_Limit_Balance, Currency_ID, customer_due_date_period 
            from tbl_customers where 1=1 
            " . ($this->get_login_data('allow_all_customers') == 1 ? " " : " and Customer_ID in (select customer_id from tbl_user_customers where user_id = '{$this->get_login_user_id()}') ") . " ";
        }
        $stmt = $this->db->query_execute($query);
        while ($row = $this->db->query_fetch($stmt)) {
            $cust_balance = $customer->return_customer_all_balances($row['Customer_ID'], false);
            $row['Balance'] = $cust_balance['balance'];
            if ($this->check_app_permission('app_show_customer_code'))
                $row['Customer_Name'] = $row['Customer_Account_Name'];
            $row['off_balance'] = $cust_balance['off_balance'];
            $row['actual_balance'] = $cust_balance['actual_balance'];
            $arr[] = $row;
        }
        die(json_encode(array('data' => $arr)));
    }

    //
    public function get_invoices() {
        $arr = array();
        $query = "SELECT `Invoice_ID_Auto`,
                        `Invoice_ID` as invoice_number,
                        `Customer_ID` as customer_id,
                        `Invoice_Date` as invoice_date, 
                        `Invoice_Currency` as currency_id,
                        `Invoice_Sub_Total` as invoice_sub_total,
                        `Invoice_Discount` as invoice_discount_percentage,
                        `discount_amount` as invoice_discount_value,
                        `Invoice_Net_Total` as invoice_net_total,
                        `Invoice_Due_Date` as invoice_due_date,
                        `invoice_app_id` as invoice_id, 
                        `current_rate` as currency_rate,
                        Invoice_VAT as invoice_vat_total,
                        Invoice_Description as invoice_notes,
                        `Payment_Paid_Amount` as invoice_paid_payment_amount,
                         0 as is_payment,
                         0 as invoice_is_return,
                         0 as invoice_is_new,
                         is_actual
                        from tbl_invoices where app_user_id = ? ";
        $stmt = $this->db->query_execute($query, array($this->get_login_user_id()));
        while ($row = $this->db->query_fetch($stmt)) {
            $row['invoice_sub_total'] = 0;
            $row['invoice_discount_value'] = 0;
            $row['invoice_cash_paid_amount'] = 0;
            $row['invoice_cheque_paid_amount'] = 0;
            $arr[$row['Invoice_ID_Auto']] = $row;
        }
        if (count($arr)) {
            $inv_ids = implode(',', array_keys($arr));
            $details_query = "select `Invoice_ID_Auto`,
                                `Product_ID`, 
                                `Product_Net_Price` as invoice_detail_unit_price,
                                (`Product_Net_Price`+((`Product_Net_Price`*vat_percent)/100)) as invoice_detail_unit_price_with_vat,
                                `Product_Total_Price` as invoice_detail_total_price,
                                (`Product_Total_Price`+vat_amount) as invoice_detail_total_price_with_vat,
                                `UOM_ID` as invoice_detail_uom_id,
                                `Item_Quantity_Current` as User_Product_Quantity,
                                `Product_Quantity` as invoice_detail_default_quantity,
                                vat_percent as invoice_detail_vat_perc,
                                vat_amount as invoice_detail_vat_value,
                                Product_Discount as invoice_detail_discount_percentage,
                                ((Product_Net_Price*Product_Discount*Item_Quantity_Current)/100) as invoice_detail_discount_value,
                                0 as form_type
                            from tbl_invoice_products_details  where Invoice_ID_Auto in (" . $inv_ids . ")";
            $detail_stmt = $this->db->query_execute($details_query);
            while ($row_det = $this->db->query_fetch($detail_stmt)) {
                $row_det['invoice_id'] = $arr[$row_det['Invoice_ID_Auto']]['invoice_id'];
                //
                $row_det['invoice_detail_unit_price_with_vat'] = round($row_det['invoice_detail_unit_price_with_vat'], 7);
                $row_det['invoice_detail_total_price_with_vat'] = round($row_det['invoice_detail_total_price_with_vat'], 7);
                $row_det['invoice_detail_discount_value'] = round($row_det['invoice_detail_discount_value'], 7);
                $arr[$row_det['Invoice_ID_Auto']]['details']['data'][] = $row_det;
                //
                $arr[$row_det['Invoice_ID_Auto']]['invoice_sub_total'] += ($row_det['invoice_detail_unit_price_with_vat'] * $row_det['User_Product_Quantity'] * (1 - ($row_det['invoice_detail_discount_percentage'] / 100)));
                $arr[$row_det['Invoice_ID_Auto']]['invoice_discount_value'] = round(($arr[$row_det['Invoice_ID_Auto']]['invoice_sub_total'] * (($arr[$row_det['Invoice_ID_Auto']]['invoice_discount_percentage'] / 100))), 7);
            }

            $trans_query = 'select Transaction_Amount,Transaction_Reference,main_id 
            from tbl_accounting_transactions where main_type = "inv" and main_id in (' . $inv_ids . ') and Transaction_Reference in("cash","check")';
            $trans_stmt = $this->db->query_execute($trans_query);
            while ($trans_row = $this->db->query_fetch($trans_stmt)) {
                if ($trans_row['Transaction_Reference'] === 'cash') {
                    $arr[$trans_row['main_id']]['invoice_cash_paid_amount'] += $trans_row['Transaction_Amount'];
                } else {
                    $arr[$trans_row['main_id']]['invoice_cheque_paid_amount'] += $trans_row['Transaction_Amount'];
                }
                //            
            }
        }
        $json_str = json_encode(array('invoices' => array_values($arr)));
        //if need compress data we gzip data then since they are bytes we base64 encode to convert to string
        //and we make json encode, since in android automaic make json decode on response.
        if (!empty($_GET['response_type']) && $_GET['response_type'] == 'compress')
            die(json_encode(base64_encode(gzencode($json_str, 9))));
        die($json_str);
    }

    //
    public function get_return_invoices() {
        $arr = array();
        $query = "SELECT `Return_Invoice_ID`,
                        `Return_ID` as invoice_number,
                        `Person_ID` as customer_id,
                        `Return_Invoice_Date` as invoice_date, 
                        `Currency_ID` as currency_id,
                        `Return_Invoice_Total` as invoice_sub_total,
                        `discount_percent` as invoice_discount_percentage,
                        `discount_amount` as invoice_discount_value,
                        `Return_Net_Total` as invoice_net_total,
                        `Return_Invoice_Date` as invoice_due_date,
                        `invoice_app_id` as invoice_id, 
                        `current_rate` as currency_rate,
                        Invoice_VAT as invoice_vat_total,
                        Return_Invoice_Note as invoice_notes,
                        0 as invoice_paid_payment_amount,
                        0 as is_payment,
                        1 as invoice_is_return,
                        0 as invoice_is_new,
                        Is_Actual as is_actual
                    from tbl_accounting_return_invoices where app_user_id = ? and Is_Customer_Invoice = 1";
        $stmt = $this->db->query_execute($query, array($this->get_login_user_id()));
        while ($row = $this->db->query_fetch($stmt)) {
            $row['invoice_sub_total'] = 0;
            $row['invoice_discount_value'] = 0;
            $row['invoice_cash_paid_amount'] = 0;
            $row['invoice_cheque_paid_amount'] = 0;
            $arr[$row['Return_Invoice_ID']] = $row;
        }
        if (count($arr)) {
            $inv_ids = implode(',', array_keys($arr));
            $details_query = "select `Return_Invoice_ID`,
                                `Product_ID`,  
                                `unit_price` as invoice_detail_unit_price,
                                (`unit_price`+((`unit_price`*vat_percent)/100)) as invoice_detail_unit_price_with_vat,
                                `Return_Product_Total` as invoice_detail_total_price,
                                (`Return_Product_Total`+vat_amount) as invoice_detail_total_price_with_vat,
                                `UOM_ID` as invoice_detail_uom_id,
                                `Item_Quantity_Current` as User_Product_Quantity,
                                `Return_Product_Quantity` as invoice_detail_default_quantity,
                                vat_percent as invoice_detail_vat_perc,
                                vat_amount as invoice_detail_vat_value,
                                Product_Discount as invoice_detail_discount_percentage,
                                ((unit_price*Product_Discount*Item_Quantity_Current)/100) as invoice_detail_discount_value,
                                0 as form_type
                            from tbl_accounting_return_items  where Return_Invoice_ID in (" . $inv_ids . ")";
            $detail_stmt = $this->db->query_execute($details_query);
            while ($row_det = $this->db->query_fetch($detail_stmt)) {
                $row_det['invoice_id'] = $arr[$row_det['Return_Invoice_ID']]['invoice_id'];
                //
                $row_det['invoice_detail_unit_price_with_vat'] = round($row_det['invoice_detail_unit_price_with_vat'], 7);
                $row_det['invoice_detail_total_price_with_vat'] = round($row_det['invoice_detail_total_price_with_vat'], 7);
                $row_det['invoice_detail_discount_value'] = round($row_det['invoice_detail_discount_value'], 7);
                $arr[$row_det['Return_Invoice_ID']]['details']['data'][] = $row_det;
                //
                $arr[$row_det['Return_Invoice_ID']]['invoice_sub_total'] += ($row_det['invoice_detail_unit_price_with_vat'] * $row_det['User_Product_Quantity'] * (1 - ($row_det['invoice_detail_discount_percentage'] / 100)));
                $arr[$row_det['Return_Invoice_ID']]['invoice_discount_value'] = ($arr[$row_det['Return_Invoice_ID']]['invoice_sub_total'] * (($arr[$row_det['Return_Invoice_ID']]['invoice_discount_percentage'] / 100)));
            }
            $trans_query = 'select Transaction_Amount,Transaction_Reference,main_id 
            from tbl_accounting_transactions where main_type = "rin" and main_id in (' . $inv_ids . ') and Transaction_Reference in("cash","check")';
            $trans_stmt = $this->db->query_execute($trans_query);
            while ($trans_row = $this->db->query_fetch($trans_stmt)) {
                if ($trans_row['Transaction_Reference'] === 'cash') {
                    $arr[$trans_row['main_id']]['invoice_cash_paid_amount'] += $trans_row['Transaction_Amount'];
                } else {
                    $arr[$trans_row['main_id']]['invoice_cheque_paid_amount'] += $trans_row['Transaction_Amount'];
                }
            }
        }
        die(json_encode(array('invoices' => array_values($arr))));
    }

    //
    public function get_requests() {
        $arr = array();
        $query = "SELECT `Request_ID` as auto_id,
                        `Request_Date` as request_date,
                        `Request_Number` as request_number,
                        `app_request_id` as request_id,
                         0 as request_is_new
                    FROM `tbl_stock_request` where app_user_id = ? ";
        $stmt = $this->db->query_execute($query, array($this->get_login_user_id()));
        while ($row = $this->db->query_fetch($stmt)) {
            $arr[$row['auto_id']] = $row;
        }
        if (count($arr)) {
            $req_ids = implode(',', array_keys($arr));
            $details_query = "SELECT        
                                `Request_ID` as auto_id,
                                `Product_ID`,
                                `Request_UOM` as request_detail_uom_id,
                                `Request_Qty` as User_Product_Quantity,
                                `Current_Quantity` as request_detail_default_quantity,
                                1 as form_type
                            FROM `tbl_stock_request_details`  
                            where Request_ID in (" . $req_ids . ")";
            $detail_stmt = $this->db->query_execute($details_query);
            while ($row_det = $this->db->query_fetch($detail_stmt)) {
                $row_det['request_details_request_id'] = $arr[$row_det['auto_id']]['request_id'];
                $arr[$row_det['auto_id']]['details']['data'][] = $row_det;
            }
        }
        die(json_encode(array('requests' => array_values($arr))));
    }

    //
    public function get_payments() {
        $arr = array();
        $query = "SELECT p.pay_rec_id as `Payment_Auto_ID`,
                    p.`pay_rec_nb` as payment_number,
                    d.`payment_amount` as payment_details_amount,
                    p.payment_date,
                    d.`currency_id` as payment_currency_id,
                    d.`cheque_nb` as payment_cheque_number,
                    d.`payment_due_date` as payment_details_cheque_due_date,
                    d.`current_rate` as payment_currency_rate,
                    p.`payment_app_id` as payment_id,
                    d.`is_cheque` as payment_is_cheque,
                    0 as payment_is_new,
                    0 as payment_total_paid_amount,
                    0 as payment_has_invoice,
                    a.main_id as payment_customer_id,
                    d.payment_account_id,
                    p.is_actual
                FROM `tbl_payment_receipt_details` d 
                    join tbl_payment_receipt p on p.pay_rec_id = d.pay_rec_id
                    join tbl_chart_of_accounts a on p.Account_ID = a.Account_ID 
                where app_user_id = ? and is_receipt = 1 and is_return = 0 ";
        $stmt = $this->db->query_execute($query, array($this->get_login_user_id()));
        $pay_auto_ids = array();
        while ($row = $this->db->query_fetch($stmt)) {
            $pay_auto_ids[$row['Payment_Auto_ID']] = $row['payment_id'];
            if (!isset($arr[$row['payment_id']])) {
                $row['details'] = array();
//                $row['invoices'] = array();
                $arr[$row['payment_id']] = $row;
            }
            //here if payment type account is same as the user account so return it empty
            $pay_acc_id = $row['payment_account_id'];
            if (($row['payment_is_cheque'] && $pay_acc_id == $this->get_login_data('cheque_account_id') ) || (!$row['payment_is_cheque'] && $pay_acc_id == $this->get_login_data('cash_account_id')))
                $pay_acc_id = 0;
            //
            $arr[$row['payment_id']]['payment_total_paid_amount'] += $row['payment_details_amount'];
            $arr[$row['payment_id']]['details']['data'][] = array(
                'payment_id' => $row['payment_id'],
                'payment_details_amount' => $row['payment_details_amount'],
                'payment_details_cheque_due_date' => $row['payment_details_cheque_due_date'],
                'payment_cheque_number' => $row['payment_cheque_number'],
                'payment_is_cheque' => $row['payment_is_cheque'],
                'payment_account_id' => $pay_acc_id
            );
        }
        if (count($arr)) {
            $details_query = "SELECT        
                                ip.`Payment_Auto_ID`, 
                                ip.`Payment_Amount` as payment_invoice_amount,
                                i.invoice_app_id as invoice_id,
                                1 as is_payment
                                FROM `tbl_invoice_payments` ip 
                                join tbl_invoices i 
                                on i.Invoice_ID_Auto = ip.Invoice_ID_Auto and i.app_user_id = ?
                                where ip.type='rec' and  ip.`Payment_Auto_ID` in (" . implode(',', array_keys($pay_auto_ids)) . ") ";
            $detail_stmt = $this->db->query_execute($details_query, array($this->get_login_user_id()));
            while ($row_det = $this->db->query_fetch($detail_stmt)) {
                $row_det['payment_invoice_payment_id'] = $pay_auto_ids[$row_det['Payment_Auto_ID']];
                if (!isset($arr[$row_det['payment_invoice_payment_id']]['invoices']))
                    $arr[$row_det['payment_invoice_payment_id']]['invoices'] = array();
                $arr[$row_det['payment_invoice_payment_id']]['invoices']['invoices'][] = $row_det;
                $arr[$row_det['payment_invoice_payment_id']]['payment_has_invoice'] = 1;
            }
        }
        die(json_encode(array('payments' => array_values($arr))));
    }

    //
    public function get_currencies() {
        $def_curr_row = $this->get_default_currency();
        $def_curr_id = $def_curr_row['Currency_ID'];
        $curr_rates_arr = array();
        //
        $stmt = $this->db->query_execute("select from_currency, ExchRate from tbl_currencies_rate
                inner join (select from_currency as curr_id, max(concat(Exchange_Rate_Date,'--',Exchange_Rate_ID)) as id_rate from tbl_currencies_rate where to_currency = ? group by from_currency) as tbl_rate
                 on (from_currency = curr_id and concat(Exchange_Rate_Date,'--',Exchange_Rate_ID) = id_rate)
            where to_currency = ? ", array($def_curr_id, $def_curr_id));
        while ($row = $this->db->query_fetch($stmt))
            $curr_rates_arr[$row['from_currency']] = $row['ExchRate'];
        //here sometimes we add the reverse rate for that we select it here again
        if (count($curr_rates_arr)) {
            $stmt = $this->db->query_execute("select to_currency, ExchRate from tbl_currencies_rate
                    inner join (select to_currency as curr_id, max(concat(Exchange_Rate_Date,'--',Exchange_Rate_ID)) as id_rate from tbl_currencies_rate where from_currency = ? group by to_currency) as tbl_rate
                     on (to_currency = curr_id and concat(Exchange_Rate_Date,'--',Exchange_Rate_ID) = id_rate)
                where from_currency = ? and to_currency not in (" . implode(',', array_keys($curr_rates_arr)) . ") ", array($def_curr_id, $def_curr_id));
            while ($row = $this->db->query_fetch($stmt))
                $curr_rates_arr[$row['to_currency']] = round((empty($row['ExchRate']) ? 0 : (1 / $row['ExchRate'])), 12);
        }
        //default curr. rate
        $curr_rates_arr[$def_curr_id] = 1;
        //here we add condition to get currencies that only with exchange rates
        $stmt = $this->db->query_execute("select Currency_ID, Currency_Name, if(Currency_Symbol='',Currency_Code,Currency_Symbol) as Currency_Symbol 
            from tbl_common_currency where Currency_ID in (" . implode(',', array_keys($curr_rates_arr)) . ") ");
        $currencies_arr = array();
        while ($row = $this->db->query_fetch($stmt)) {
            $row['currency_rate'] = empty($curr_rates_arr[$row['Currency_ID']]) ? null : $curr_rates_arr[$row['Currency_ID']];
            $currencies_arr[] = $row;
        }
        die(json_encode(array('data' => $currencies_arr)));
    }

    /**
     * Functions for add data to server from App.
     * ====================================================================>
     */
    public function add_customers() {
        //this must be called always before app. call get customers, add invoices, add payment because get customers will remove all customers
        //from app. and will delete not yet sync records. and before add invoice as here will recieve app. customer id that not exist here or wrong id
        //while if we make sync of add customers app., in app will replace id by our id we send here and app. will update customer id in invoice, payment.
        $customer_fields_arr = array('customer_app_id', 'app_user_id', 'Customer_Name', 'Customer_Mobile', 'Customer_Address', 'Customer_Company'
            , 'Customer_Lat', 'Customer_Lng', 'Class_ID', 'Customer_Limit_Balance', 'Customer_Discount', 'Currency_ID', 'customer_due_date_period');
        $inserted_customers_arr = array();
        $id = $user = $name = $phone = $address = $company = $lat = $lng = $class = $limit_balance = $discount = $currency = $due_date_period = null;
        $user = $this->get_login_user_id();
        $this->db->transaction_start();
        $query = "insert into tbl_customers (`" . implode('`, `', $customer_fields_arr) . "`) values (?,?,?,?,?,?,?,?,?,?,?,?,?)";
        $stmt = $this->db->query_prepare_init($query, array(&$id, &$user, &$name, &$phone, &$address, &$company, &$lat, &$lng, &$class, &$limit_balance, &$discount, &$currency, &$due_date_period));
        // '{"customers":[{"Customer_ID":"2023","Customer_Name":"test","Customer_Mobile":"123456","Customer_Address":"hello","Customer_Company":"test co","Customer_Lat":"33.5553","Customer_Lng":"35.3731","is_new":"1","Class_ID":"0","Customer_Discount":"0","customer_due_date_period":"15"}, {"Customer_ID":"2024","Customer_Name":"bil","Customer_Mobile":"456","Customer_Address":"aaaa","Customer_Company":"bbbb","Customer_Lat":"33.5554","Customer_Lng":"35.3731","is_new":"1","Class_ID":"0","Customer_Discount":"456","customer_due_date_period":"54694"}, {"Customer_ID":"2025","Customer_Name":"ggg","Customer_Mobile":"1234","Customer_Address":"aaa","Customer_Company":"aaa","Customer_Lat":"33.5554","Customer_Lng":"35.3731","is_new":"1","Class_ID":"0","Customer_Discount":"1235","customer_due_date_period":"18897"}]}'
        $body_result = json_decode($this->_body, true);
        if (empty($body_result) || empty($body_result['customers']) || !is_array($body_result['customers']))
            $this->die_error('no customers send to add them!');
        $new_ids_arr = array();
        foreach ($body_result['customers'] as $row) {
            $id = $row['Customer_ID'];
            $name = $row['Customer_Name'];
            $phone = $row['Customer_Mobile'];
            $address = $row['Customer_Address'];
            $company = $row['Customer_Company'];
            $lat = $row['Customer_Lat'];
            $lng = $row['Customer_Lng'];
            $class = empty($row['Class_ID']) ? $this->get_login_data('class_id') : $row['Class_ID'];
            $limit_balance = /*!empty($row['Customer_Limit_Balance']) ? $row['Customer_Limit_Balance'] :*/ 0; //$row['Customer_Limit_Balance'];
            $discount = $row['Customer_Discount'];
            $currency = /*!empty($row['Currency_ID']) ? $row['Currency_ID'] :*/ $this->get_login_data('default_currency_id');  //$row['Currency_ID']
            $due_date_period = $row['customer_due_date_period'];
            //
            $this->db->query_prepare_execute($stmt);
            $new_id = $this->db->last_id();
            $inserted_customers_arr[] = array('old_id' => $id, 'new_id' => $new_id);
            $new_ids_arr[] = array('user_id' => $this->get_login_user_id(), 'customer_id' => $new_id);
        }
        //
        if ($this->get_login_data('allow_all_customers') != 1)
            $this->insert_query('tbl_user_customers', array('user_id', 'customer_id'), $new_ids_arr);
        //
        $this->db->transaction_end();

        //here we must sort the array by new id first so on the app. when update the id's don't gave error ex: in app send [5,6,7] and new
        //id's [7,8,9] if in app update 5 by new id 7 will gave error duplicate primary key exist so must start with 7 replace by 9 & so on..
        function tmp_sort_ids_compare($a, $b) {
            if ($a['new_id'] == $b['new_id'])
                return 0;
            return ($a < $b) ? 1 : -1;  //return in desc. 
        }

        usort($inserted_customers_arr, "tmp_sort_ids_compare");
        //
        die(json_encode(array('data' => $inserted_customers_arr)));
    }

    //
    public function add_invoices() {
        //
        $succ_inv_ids = array();
        $err_inv_ids = array();
        $err_msgs = null;
        //fill in session since some methods depend on it like for logged id, current year,.. also we need it to 
        //fill permission or it will make logout when call new InventoryAjax() class or other classes
        $this->fill_trusto_session();
        //
        $InventoryAjax = new InventoryAjax();  //we need it as it initialize the model and classes we will use
        //$InventoryAjax->addPurchaseAjax();    //we stop it and add what we want below
        $body_result = json_decode($this->_body, true);
        if (empty($body_result) || empty($body_result['invoices']) || !is_array($body_result['invoices']))
            $this->die_error('no invoices send to insert them!');
//        file_put_contents(__DIR__ . '/invoices.txt', print_r($body_result, true));
        //here since the transaction start/end/rollback inside the insertSalesInvoice method some invoices insert may success and other may failed
        foreach ($body_result['invoices'] as $row) {
            //
            $app_inv_id = $row["Invoice_ID"];
            $row['invoice_prefix'] = $this->get_login_data('serial_prefix');
            //@todo: inv insert invoice must fill them.. and make sure not already inserd 
            //here Invoice_Number is already filled from app.
            $logged_id = $this->get_login_user_id();
            $row['invoice_app_id'] = $app_inv_id;
            $row['app_user_id'] = $logged_id;
            $stmt = $this->db->query_execute("select ac.Account_ID, c.Customer_Name from tbl_chart_of_accounts ac
                        inner join tbl_customers c on c.Customer_ID = ac.main_id
                    where ac.main_id = ? and ac.main_type='cust' and ac.sub_type='main' limit 1", array($row["Customer_ID"]));
            $cstmr_row = $this->db->query_fetch($stmt);
            $customer_account = $cstmr_row['Account_ID'];
            $customer_name = $cstmr_row['Customer_Name'];
            $inv_rtrn = null;
            $record_id = null;
            $row['products'] = json_decode($row['products'], true); //since from app. it is also encoded
            if (is_numeric($row["Customer_ID"]) && !empty($row["Invoice_Date"]) && !empty($row["Invoice_ID"]) && !empty($row['products']) && is_array($row['products'])) {
                if ($row['is_return'] == 0) {
                    $exist_inv_auto_id = 0;
                    $check_invoice = $this->db->query_execute('select Invoice_ID_Auto from tbl_invoices where invoice_app_id = ? and app_user_id = ?', array($app_inv_id, $logged_id));
                    //for update
                    if ($this->db->query_result_count($check_invoice) > 0) {
                        $exist_inv_auto_id = $this->db->query_fetch($check_invoice)['Invoice_ID_Auto'];
                        $row['Invoice_ID_Auto'] = $exist_inv_auto_id;
                    }
                    //
                    $row['Customer_Account'] = $customer_account;
                    $row['Customer_Name'] = $customer_name;
                    //
                    $stmt = $this->db->query_execute("select if(Currency_Symbol='',Currency_Code,Currency_Symbol) as Currency_Symbol 
                        from tbl_common_currency where Currency_ID = ? ", array($row["Invoice_Currency"]));
                    $curr_row = $this->db->query_fetch($stmt);
                    $row['CurrencySymbole'] = $curr_row['Currency_Symbol'];
                    //Invoice :: Invoice_Date, Depot_ID, Customer_ID, Invoice_Due_Date, Invoice_Sub_Total, Invoice_Discount, Invoice_Net_Total, Invoice_VAT
                    //Invoice_Currency, Account_Code, Card_Amount, Cash_Amount, Check_Amount, Customer_Account, pending_status, Logged_ID, Customer_Name
                    //CurrencySymbole, shipping, shipping_account, products {[0=>product_id, 1=>qty, 2=>disc, 3=>net_price, 4=>total_price, 5=>uom_id], ..}
                    $row['Logged_ID'] = $logged_id;
                    $row['IsSalesMode'] = 1;
                    $row['Depot_ID'] = $this->get_login_data('depot_id');
                    if (empty($row['Class_ID']))
                        $row['Class_ID'] = !empty($this->get_login_data('class_id')) ? $this->get_login_data('class_id') : $this->get_login_data('default_class_id');
                    $row['Invoice_VAT'] = $row['invoice_vat_total'];
                    $row['Invoice_Description'] = $row['invoice_notes'];
                    $row['Account_Code'] = (empty($this->get_login_data('sales_account_id')) ? 0 : $this->get_login_data('sales_account_id'));   //it will take default 70
                    $row['pending_status'] = 4;
                    $row['Card_Amount'] = 0;
                    $row['tax_amount'] = 0;
                    $row['Card_Amount'] = 0;
                    $row['Card_Account'] = '';
                    $row['shipping'] = 0;
                    $row['shipping_account'] = $this->get_login_data('sales_ship_account_id');
                    $row['Cash_Account'] = $this->get_login_data('cash_account_id');
                    $row['Check_Account'] = $this->get_login_data('cheque_account_id');
                    $row['truck'] = '';
                    //
                    $sub_total = 0;
                    foreach ($row['products'] as $inv_dtl_row) {
                        $unit_price_bfr_vat = ((floatval($inv_dtl_row[3]) * 100) / (100 + floatval($inv_dtl_row[5])));
                        $sub_total += (($inv_dtl_row[1] * $unit_price_bfr_vat) - $inv_dtl_row[7]);
                        //get default uom id
                        $row['Products'][] = [
                            'UOM_ID' => $inv_dtl_row[8], //$this->get_default_uom($inv_dtl_row[0]),
                            'Product_ID' => $inv_dtl_row[0],
                            'Item_Quantity' => $inv_dtl_row[1],
                            'Item_Discount' => $inv_dtl_row[2],
                            'Item_Price' => $unit_price_bfr_vat, //unit price before vat
                            'vat_percent' => $inv_dtl_row[5],
                            'vat_val' => $inv_dtl_row[6],
                            'Discount_Amount' => $inv_dtl_row[7],
                            //total price after row discount & invoice discount without vat
                            'Product_Total_Price' => ($inv_dtl_row[4] - $inv_dtl_row[6])];
                    }
                    unset($row['products']);
                    $row['discount_amount'] = $sub_total * $row['Invoice_Discount'] / 100;
                    $row['Invoice_Sub_Total'] = $sub_total;
                    if (!isset($row['Invoice_Net_Total']) || empty($row['Invoice_Net_Total'])) //app. null problem
                        $row['Invoice_Net_Total'] = $sub_total - $row['discount_amount'] + $row['Invoice_VAT'];
                    //
                    if (empty($exist_inv_auto_id))
                        $inv_rtrn = $InventoryAjax->get_registry()->invoice->setData($row)->insertSalesInvoice();
                    else
                        $inv_rtrn = $InventoryAjax->get_registry()->invoice->setData($row)->editInvoice();
                    if (is_numeric($inv_rtrn[0]))
                        $record_id = $inv_rtrn[0];
                } else {
                    $exist_rtrn_id = 0;
                    $check_return = $this->db->query_execute('select Return_Invoice_ID from tbl_accounting_return_invoices where invoice_app_id = ? and app_user_id = ?', array($app_inv_id, $logged_id));
                    if ($this->db->query_result_count($check_return) > 0) {
                        $exist_rtrn_id = $this->db->query_fetch($check_return)['Return_Invoice_ID'];
                        $row['Return_ID'] = $exist_rtrn_id;
                    } else
                        $row['Return_ID'] = $row['Invoice_Number'];
                    $row['Person_Account'] = $customer_account;
                    $row['Return_Invoice_Date'] = $row['Invoice_Date'];
                    $row['Return_Invoice_Total'] = $row['Invoice_Net_Total'];
                    $row['Return_Invoice_Note'] = $row['invoice_notes'];
                    $row['Is_Actual'] = $row['is_actual'];
                    $row['Currency_ID'] = $row['Invoice_Currency'];
                    $row['Person_ID'] = $row['Customer_ID'];
                    $row[0] = $logged_id;
                    $row['Is_Customer_Invoice'] = 1;
                    $row['Depot_ID'] = $this->get_login_data('depot_id');
                    if (empty($row['Class_ID']))
                        $row['Class_ID'] = !empty($this->get_login_data('class_id')) ? $this->get_login_data('class_id') : $this->get_login_data('default_class_id');
                    $row['TotalTax'] = 0;
                    $row['Account_Code'] = (empty($this->get_login_data('sales_account_id')) ? 0 : $this->get_login_data('sales_account_id'));   //it will take default 70
                    $row['Card_Amount'] = 0;
                    $row['tax_amount'] = 0;
                    $row['Card_Account'] = '';
                    $row['Cash_Account'] = $this->get_login_data('cash_account_id');
                    $row['Check_Account'] = $this->get_login_data('cheque_account_id');
                    $row['Invoice_Purchase_ID'] = NULL;
                    $row['Invoice_VAT'] = $row['invoice_vat_total'];
                    $row['discount_percent'] = $row['Invoice_Discount'];

                    $row['Products'] = array();
                    $sub_total = 0;
                    foreach ($row['products'] as $inv_dtl_row) {
                        $unit_price_bfr_vat = ((floatval($inv_dtl_row[3]) * 100) / (100 + floatval($inv_dtl_row[5])));
                        $sub_total += (($inv_dtl_row[1] * $unit_price_bfr_vat) - $inv_dtl_row[7]);
                        $row['Products'][] = array(
                            'UOM_ID' => $inv_dtl_row[8], //$this->get_default_uom($inv_dtl_row[0]),
                            'Return_Product_Quantity' => $inv_dtl_row[1],
                            'Product_ID' => $inv_dtl_row[0],
                            'Unit_Cost' => $unit_price_bfr_vat,
                            'vat_percent' => $inv_dtl_row[5],
                            'vat_val' => $inv_dtl_row[6],
                            'Item_Discount' => $inv_dtl_row[2],
                            'Discount_Amount' => $inv_dtl_row[7],
                            'Return_Product_Total' => ($inv_dtl_row[4] - $inv_dtl_row[6])
                        );
                    }
                    $row['discount_amount'] = $sub_total * $row['Invoice_Discount'] / 100;
                    $row['Return_Invoice_Total'] = $sub_total;
                    //
                    if (empty($exist_rtrn_id))
                        $inv_rtrn = $InventoryAjax->get_registry()->return->setData($row)->ReturnNoInvoice();
                    else
                        $inv_rtrn = $InventoryAjax->get_registry()->return->setData($row)->editReturnNoInvoice();
                    if (is_numeric($inv_rtrn))
                        $record_id = $inv_rtrn;
                }
            }
            if (!empty($record_id)) {
                $succ_inv_ids[] = array('succ_inv_ids' => $app_inv_id);
            } else {
                $err_inv_ids[] = array('err_inv_ids' => $app_inv_id);
                $err_msgs .= $inv_rtrn[0] . "<br/>";
            }
        }
        $result_arr = array();
        if (count($err_inv_ids)) {
            $result_arr['error_info'] = "Error Occured while inserting invoices: " . $err_msgs;
            $result_arr['error_inv_ids'] = $err_inv_ids;
        }
        if (count($succ_inv_ids))
            $result_arr['invoices'] = $succ_inv_ids;
        if (!count($result_arr))
            $this->die_error('no invoices inserted!');
        die(json_encode($result_arr));
    }

    public function add_requests() {
        //
        $succ_req_ids = array();
        $err_req_ids = array();
        $err_msgs = null;
        //fill in session since some methods depend on it like for logged id, current year,.. also we need it to 
        //fill permission or it will make logout when call new StockAjax() class or other classes
        $this->fill_trusto_session();
        //
        $StockAjax = new StockAjax();  //we need it as it initialize the model and classes we will use
        $body_result = json_decode($this->_body, true);
//        file_put_contents(__DIR__ . '/request.txt', print_r($body_result, true));
        if (empty($body_result) || empty($body_result['rto']) || !is_array($body_result['rto']))
            $this->die_error('no requests send to insert them!');

        foreach ($body_result['rto'] as $row) {
            $row['request_prefix'] = $this->get_login_data('serial_prefix');
            $row['app_request_id'] = $row['Request_ID'];
            $logged_id = $this->get_login_user_id();
            $row['app_user_id'] = $logged_id;
            $check_request = $this->db->query_execute('select Request_ID from tbl_stock_request where app_request_id = ? and app_user_id = ?', array($row['app_request_id'], $row['app_user_id']));
//            $check_request_row = $this->db->query_fetch($check_request);
            if ($this->db->query_result_count($check_request) == 0) {
                $row['products'] = json_decode($row['products'], true); //since from app. it is also encoded
                if (!empty($row["Request_Number"]) && !empty($row["Request_Date"]) && !empty($row['products']) && is_array($row['products'])) {

                    $row[0] = $logged_id;
                    $row['Request_From'] = $this->get_login_data('depot_id');
                    $row['Request_To'] = $this->get_login_data('default_depot_id');
                    $row['Delivery_Date'] = $row['Request_Date'];
                    foreach ($row['products'] as &$inv_dtl_row) {
                        $row['Products'][] = array(
                            'UOM_ID' => $inv_dtl_row[2], //$this->get_default_uom($inv_dtl_row[0]),
                            'Product_ID' => $inv_dtl_row[0],
                            'Request_Qty' => $inv_dtl_row[1],
                        );
                    }

                    $request = $StockAjax->get_registry()->depot->setData($row)->createRequest();
                    //debug($row);
                }
            } else {
                $request = array($row['Request_ID']);
            }

            if (isset($request[0]) && is_numeric($request[0])) {
                $succ_req_ids[] = array('succ_req_ids' => $row['Request_ID']);
            } else {
                $err_req_ids[] = array('err_req_ids' => $row['Request_ID']);
                $err_msgs .= empty($request) ? '' : $request[0] . "<br/>";
            }
        }
        $result_arr = array();
        if (count($err_req_ids)) {
            $result_arr['error_info'] = "Error Occured while inserting requests: " . $err_msgs;
            $result_arr['err_req_ids'] = $err_req_ids;
        }
        if (count($succ_req_ids))
            $result_arr['requests'] = $succ_req_ids;
        if (!count($result_arr))
            $this->die_error('no requests inserted!');
        die(json_encode($result_arr));
    }

    public function add_payments() {

        $succ_pay_ids = array();
        $err_pay_ids = array();
        $err_msgs = null;
        $this->fill_trusto_session();
        $ACCAjax = new ACCAjax();
        $body_result = json_decode($this->_body, true);
        // file_put_contents(__DIR__ . '/payment.txt', print_r($body_result, true));
        if (empty($body_result) || empty($body_result['payments']) || !is_array($body_result['payments']))
            $this->die_error('no payments send to insert them!');
        //        
        foreach ($body_result['payments'] as $row) {
            //
            $app_pay_id = $row["Payment_ID"];
            $row['payment_prefix'] = $this->get_login_data('serial_prefix');
            $row['payment_app_id'] = $app_pay_id;
            $row['app_user_id'] = $this->get_login_user_id();
            $check_payment = $this->db->query_execute('select pay_rec_id from tbl_payment_receipt where payment_app_id = ? and app_user_id = ? and is_return=0', array($app_pay_id, $row['app_user_id']));
//            $check_payment_row = $this->db->query_fetch($check_payment);
            if ($this->db->query_result_count($check_payment) == 0) {
                $stmt = $this->db->query_execute("select ac.Account_ID, c.Customer_Name from tbl_chart_of_accounts ac
                        inner join tbl_customers c on c.Customer_ID = ac.main_id
                    where ac.main_id = ? and ac.main_type='cust' and ac.sub_type='main' limit 1", array($row["Customer_ID"]));
                $cstmr_row = $this->db->query_fetch($stmt);
                $row['Account_Code'] = $cstmr_row['Account_ID'];
                $row['Account_Name'] = $cstmr_row['Customer_Name'];
                $payment_details = json_decode($row['payment_details'], true); //since from app. it is also encoded
                $invoices = json_decode($row['invoices'], true); //since from app. it is also encoded
//                   file_put_contents(__DIR__ . '/payment.txt', print_r($invoices, true));
                $payment_insert = null;
                if (is_numeric($row["Customer_ID"]) && !empty($row["Payment_Date"]) && !empty($row["Payment_ID"]) && !empty($payment_details) && is_array($payment_details)/* && !empty($invoices) && is_array($invoices) */) {
                    $logged_id = $this->get_login_user_id();
                    $row[0] = $logged_id;
                    $row['IsReceipt'] = 1;
                    $row['Status_ID'] = $row['is_actual'];
                    //
                    $cash_account = $this->get_login_data('cash_account_id');
                    $check_account = $this->get_login_data('cheque_account_id');
                    //$cash_payment_query = $this->db->query_execute('select Payment_Type_ID from tbl_payment_types where Account_ID = ?', array($cash_account));
                    //$cash_payment_id = $this->db->query_fetch($cash_payment_query)['Payment_Type_ID'];
                    $cash_payment_id = $cash_account;
                    //$check_payment_query = $this->db->query_execute('select Payment_Type_ID from tbl_payment_types where Account_ID = ?', array($check_account));
                    //$check_payment_id = $this->db->query_fetch($check_payment_query)['Payment_Type_ID'];
                    $check_payment_id = $check_account;
                    //
                    $row['total_amount'] = 0;
                    foreach ($payment_details as $pay_dtl_row) {
                        if (empty($pay_dtl_row['payment_account_id']))
                            $pay_type_id = ($pay_dtl_row['Is_Cheque'] == 1 ? $check_payment_id : $cash_payment_id);
                        else
                            $pay_type_id = $pay_dtl_row['payment_account_id'];
                        //get default uom id
                        $row['payment'][] = [
                            'Payment_Type_ID' => $pay_type_id,
                            'Payment_Amount' => $pay_dtl_row['Amount'],
                            'Payment_Note' => 'Payment from ' . $row['Account_Name'],
                            'Check_Number' => ($pay_dtl_row['Is_Cheque'] == 1 ? $pay_dtl_row['Cheque_Number'] : ''),
                            'is_cheque' => $pay_dtl_row['Is_Cheque'],
                            'Check_Date' => ($pay_dtl_row['Is_Cheque'] == 1 ? $pay_dtl_row['Cheque_Due_Date'] : '')];
                        //from app all rows in same currency
                        $row['total_amount'] += $pay_dtl_row['Amount'];
                    }
                    $row['close_arr'] = array();
//                    if ( is_array($row['invoices'])) {
                    foreach ($invoices as $inv) {
                        $inv_query = $this->db->query_execute('select Invoice_ID_Auto,Invoice_Currency from tbl_invoices 
                            where invoice_app_id = ? and app_user_id = ? ', array($inv['Invoice_ID'], $row['app_user_id']));
                        $inv_data = $this->db->query_fetch($inv_query);
                        $row['close_arr'][] = [
                            'inv_id' => $inv_data['Invoice_ID_Auto'],
                            'inv_currency' => $inv_data['Invoice_Currency'],
                            'inv_amount' => $inv['Paid_Amount']
                        ];
                    }
//                    }
                    $payment_insert = $ACCAjax->get_registry()->payment->setData($row)->create();
                }
            } else {
                $payment_insert = array();
            }
            if (is_array($payment_insert) && empty($payment_insert)) {
                $succ_pay_ids[] = array('succ_pay_ids' => $app_pay_id);
            } else {
                $err_pay_ids[] = array('err_pay_ids' => $app_pay_id);
                if (is_array($payment_insert))
                    $err_msgs .= $payment_insert[0] . "<br/>";
            }
        }
        $result_arr = array();
        if (count($err_pay_ids)) {
            $result_arr['error_info'] = "Error Occured while inserting payments: " . $err_msgs;
            $result_arr['err_pay_ids'] = $err_pay_ids;
        }
        if (count($succ_pay_ids))
            $result_arr['payments'] = $succ_pay_ids;
//        header('Content-Type: application/json');
        if (!count($result_arr))
            $this->die_error('no payments inserted!');
        die(json_encode($result_arr));
    }

    //
    public function add_damages() {
        $body_result = json_decode($this->_body, true);
        //
        $succ_damage_ids = array();
        $err_damage_ids = array();
        $err_msgs = null;
        //fill in session since some methods depend on it like for logged id, current year,.. also we need it to 
        //fill permission or it will make logout when call new StockAjax() class or other classes
        $this->fill_trusto_session();
        //
        $StockAjax = new StockAjax();  //we need it as it initialize the model and classes we will use

        if (empty($body_result) || empty($body_result['damage']) || !is_array($body_result['damage']))
            $this->die_error('no damages send to insert them!');
        $damage_data = array();
        $depot = $this->get_login_data('depot_id');
        $logged_id = $this->get_login_user_id();
        $prefix = $this->get_login_data('serial_prefix');
//file_put_contents(__DIR__ . '/damages.txt', print_r($body_result, true));
        foreach ($body_result['damage'] as $row) {
            $check_request = $this->db->query_execute('select damage_id from tbl_damage where app_damage_id = ? and app_user_id = ?', array($row['Damage_ID'], $logged_id));
            if ($this->db->query_result_count($check_request) == 0) {
                $row['products'] = json_decode($row['products'], true);
                if (!empty($row["Damage_Number"]) && !empty($row["Damage_Date"]) && !empty($row['products'])) {
                    $prod_details = array();
                    $row['customer_id'] = isset($row['customer_id']) ? $row['customer_id'] : null;

                    foreach ($row['products'] as &$inv_dtl_row) {
                        $prod_details[] = array(
                            'UOM_ID' => $inv_dtl_row[2],
                            'Product_ID' => $inv_dtl_row[0],
                            'quantity' => $inv_dtl_row[1]
                        );
                    }
                    $damage_data = array(
                        'note' => '',
                        'Depot_ID' => $depot,
                        'app_user_id' => $logged_id,
                        'Logged_ID' => $logged_id,
                        'created_logged_id' => $logged_id,
                        'app_damage_id' => $row['Damage_ID'],
                        'damage_prefix' => $prefix,
                        'damage_date' => $row['Damage_Date'],
                        'customer_id' => $row['customer_id'],
                        'damage_nb' => $row["Damage_Number"],
                        'is_actual' => 0,
                        'Products' => $prod_details
                    );
//                        echo"<pre>";print_r($damage_data);
//                        file_put_contents(__DIR__ . '/damages.txt', print_r($damage_data, true));
                    $damage = $StockAjax->get_registry()->depot->setData($damage_data)->add_damage();
                    if (empty($damage)) {
                        $succ_damage_ids[] = array('succ_damage_ids' => $row['Damage_ID']);
                    } else {
                        $err_damage_ids[] = array('err_damage_ids' => $row['Damage_ID']);
                        $err_msgs .= empty($damage) ? '' : $damage[0] . "<br/>";
                    }
                }
            } else {
                $succ_damage_ids[] = array('succ_damage_ids' => $row['Damage_ID']);
                $damage = array();
            }
        }
        $result_arr = array();
        if (count($err_damage_ids)) {
            $result_arr['error_info'] = "Error Occured while inserting damages: " . $err_msgs;
            $result_arr['err_damage_ids'] = $err_damage_ids;
        }
        if (count($succ_damage_ids))
            $result_arr['damage'] = $succ_damage_ids;
        if (!count($result_arr))
            $this->die_error('no damages inserted!');
        die(json_encode($result_arr));
    }

    public function get_damages() {
        $arr = array();
        $query = "SELECT dg.app_damage_id as damage_id,dg.damage_nb as damage_number,d.UOM_ID as damage_detail_uom_id,
            d.product_id as Product_ID,d.quantity as User_Product_Quantity,d.quantity as damage_detail_default_quantity,
            dg.damage_date, dg.customer_id
            FROM `tbl_damage` dg join tbl_damage_details d on d.damage_id = dg.damage_id where dg.app_user_id = ? ";
        $stmt = $this->db->query_execute($query, array($this->get_login_user_id()));
        while ($row = $this->db->query_fetch($stmt)) {
            if (!isset($arr[$row['damage_id']])) {
                $row['details'] = array();
                $row['form_type'] = 2;
                $row['damage_is_new'] = 0;
                $arr[$row['damage_id']] = $row;
            }
            $arr[$row['damage_id']]['details']['data'][] = array(
                'damage_details_damage_id' => $row['damage_id'],
                'damage_detail_uom_id' => $row['damage_detail_uom_id'],
                'User_Product_Quantity' => $row['User_Product_Quantity'],
                'Product_ID' => $row['Product_ID'],
                'damage_detail_default_quantity' => $row['damage_detail_default_quantity']);
        }
        die(json_encode(array('damage' => array_values($arr))));
    }

    /**
     * ..
     * ====================================================================>
     */
    public function add_visits() {
//        die(print_r($this->_body));
        $body_result = json_decode($this->_body, true);
//        file_put_contents(__DIR__ . '/visit.txt', print_r($body_result, true));
        $succ_visit_ids = array();
        $err_visit_ids = array();
        $err_msgs = null;
        //fill in session since some methods depend on it like for logged id, current year,.. also we need it to 
        //fill permission or it will make logout when call new StockAjax() class or other classes
//        $this->fill_trusto_session();
        //
        if (empty($body_result) || empty($body_result['visits']) || !is_array($body_result['visits']))
            $this->die_error('no visits send to insert them!');
        $visits_data = array();
        $logged_id = $this->get_login_user_id();
        $prefix = $this->get_login_data('serial_prefix');
        foreach ($body_result['visits'] as $row) {
            $check_request = $this->db->query_execute('select visit_id from tbl_customer_visit where app_visit_id = ? and app_user_id = ?', array($row['visit_id'], $logged_id));
            if ($this->db->query_result_count($check_request) == 0) {
                if (!empty($row["visit_nb"]) && !empty($row["date"])) {
                    $visits_data = array(
                        $row['customer_id'],
                        $row['longitude'],
                        $row['latitude'],
                        date("Y-m-d", strtotime($row['date'])),
                        $row['time'],
                        $prefix,
                        $logged_id,
                        $row['visit_id'],
                        $row['visit_nb']
                    );
                    $visit_query = $this->db->query_execute('INSERT INTO `tbl_customer_visit`( `customer_id`,
                            `longitude`, `latitude`, `date`, `time`, `visit_prefix`, `app_user_id`,
                            `app_visit_id`, `visit_nb`) values (?,?,?,?,?,?,?,?,?) ', $visits_data);
                    if ($visit_query) {
                        $succ_visit_ids[] = array('succ_visit_ids' => $row['visit_id']);
                    } else {
                        $err_visit_ids[] = array('err_visit_ids' => $row['visit_id']);
                        $err_msgs .= $visit_query ? '' : $row['visit_id'] . "<br/>";
                    }
                }
            } else {
                $succ_visit_ids[] = array('succ_visit_ids' => $row['visit_id']);
            }
        }
        $result_arr = array();
        if (count($err_visit_ids)) {
            $result_arr['error_info'] = "Error Occured while inserting visits: " . $err_msgs;
            $result_arr['err_visit_ids'] = $err_visit_ids;
        }
        if (count($succ_visit_ids))
            $result_arr['visits'] = $succ_visit_ids;
        if (!count($result_arr))
            $this->die_error('no visits inserted!');
        die(json_encode($result_arr));
    }

    //
    public function get_visits() {
        $arr = array();
        $query = "SELECT app_visit_id as `visit_id`, `customer_id`, `longitude`, `latitude`, `date`, `time`, `visit_prefix`,
            `app_user_id`, `visit_nb` FROM `tbl_customer_visit`
            where app_user_id = ? ";
        $stmt = $this->db->query_execute($query, array($this->get_login_user_id()));
        while ($row = $this->db->query_fetch($stmt)) {
            $row['is_new'] = false;
            $arr[$row['visit_id']] = $row;
        }
        die(json_encode(array('visits' => array_values($arr))));
    }

    //
    public function add_sales() {
        //
        $succ_inv_ids = array();
        $err_inv_ids = array();
        $err_msgs = null;
        //fill in session since some methods depend on it like for logged id, current year,.. also we need it to 
        //fill permission or it will make logout when call new InventoryAjax() class or other classes
        $this->fill_trusto_session();
        //
        $InventoryAjax = new InventoryAjax();  //we need it as it initialize the model and classes we will use
        $body_result = json_decode($this->_body, true);
        if (empty($body_result) || empty($body_result['sales']) || !is_array($body_result['sales']))
            $this->die_error('no sales send to insert them!');
        //here since the transaction start/end/rollback inside the insertSalesInvoice method some invoices insert may success and other may failed
        foreach ($body_result['sales'] as $row) {
            $app_inv_id = $row["Sale_ID"];
            $row['sale_prefix'] = $this->get_login_data('serial_prefix');
            //here Invoice_Number is already filled from app.
            $logged_id = $this->get_login_user_id();
            $row['sale_app_id'] = $app_inv_id;
            $row['app_user_id'] = $logged_id;
            $inv_rtrn = null;
            $row['products'] = json_decode($row['products'], true); //since from app. it is also encoded
            if (is_numeric($row["Customer_ID"]) && !empty($row["Sale_Date"]) && !empty($row["Sale_ID"]) && !empty($row['products']) && is_array($row['products'])) {
                $check_invoice = $this->db->query_execute('select Order_ID from tbl_purchase_order where sale_app_id = ? and app_user_id = ? 
                    and is_sales = 1 and is_quotation = 0 and is_actual= ? ', array($app_inv_id, $logged_id, $row['is_actual']));
                if ($this->db->query_result_count($check_invoice) == 0) {
                    $stmt = $this->db->query_execute("select if(Currency_Symbol='',Currency_Code,Currency_Symbol) as Currency_Symbol 
                        from tbl_common_currency where Currency_ID = ? ", array($row["Sale_Currency"]));
                    $curr_row = $this->db->query_fetch($stmt);
                    $row['CurrencySymbole'] = $curr_row['Currency_Symbol'];
                    $row['Logged_ID'] = $logged_id;
                    $row['IsSales'] = 1;
                    $row['is_quotation'] = 0;
                    $row['Invoice_VAT'] = $row['Sale_Vat_Total'];
                    $row['Invoice_Description'] = $row['sale_notes'];
                    $row['Depot_ID'] = $this->get_login_data('depot_id');
                    if (empty($row['Class_ID']))
                        $row['Class_ID'] = !empty($this->get_login_data('class_id')) ? $this->get_login_data('class_id') : $this->get_login_data('default_class_id');
                    $row['Invoice_VAT'] = $row['Sale_Vat_Total'];
                    $row['Invoice_Net_Total'] = $row['Sale_Net_Total'];
                    //$row['Invoice_Sub_Total'] = $row['Sale_Sub_Total'];
                    $row['Invoice_Discount'] = $row['Sale_Discount'];
                    $row['Invoice_Currency'] = $row['Sale_Currency'];
                    $row['Invoice_Due_Date'] = $row['Sale_Due_Date'];
                    $row['Invoice_Date'] = $row['Sale_Date'];
                    //
                    $sub_total = 0;
                    foreach ($row['products'] as $inv_dtl_row) {
                        $unit_price_bfr_vat = ((floatval($inv_dtl_row[3]) * 100) / (100 + floatval($inv_dtl_row[5])));
                        $sub_total += (($inv_dtl_row[1] * $unit_price_bfr_vat) - $inv_dtl_row[7]);
                        //get default uom id
                        $row['Products'][] = [
                            'UOM_ID' => $inv_dtl_row[8], //$this->get_default_uom($inv_dtl_row[0]),
                            'Product_ID' => $inv_dtl_row[0],
                            'Item_Quantity' => $inv_dtl_row[1],
                            'Item_Discount' => $inv_dtl_row[2],
                            'Item_Price' => $unit_price_bfr_vat,
                            'vat_percent' => $inv_dtl_row[5],
                            'vat_val' => $inv_dtl_row[6],
                            'Discount_Amount' => $inv_dtl_row[7],
                            'Product_Total_Price' => ($inv_dtl_row[4] - $inv_dtl_row[6])];
                    }
                    unset($row['products']);
                    $row['discount_amount'] = $sub_total * $row['Invoice_Discount'] / 100;
                    $row['Invoice_Sub_Total'] = $sub_total;
                    if (!isset($row['Invoice_Net_Total']) || empty($row['Invoice_Net_Total'])) //app. null problem
                        $row['Invoice_Net_Total'] = $sub_total - $row['discount_amount'] + $row['Invoice_VAT'];
                    $inv_rtrn = $InventoryAjax->get_registry()->purchase->setData($row)->addPurchaseOrder()[0];
                } else {
                    $inv_rtrn = 1;
                }
            }
            if (is_numeric($inv_rtrn)) {
                $succ_inv_ids[] = array('succ_sale_ids' => $app_inv_id);
            } else {
                $err_inv_ids[] = array('err_sale_ids' => $app_inv_id);
                $err_msgs .= $inv_rtrn[0] . "<br/>";
            }
        }
        $result_arr = array();
        if (count($err_inv_ids)) {
            $result_arr['error_info'] = "Error Occured while inserting sales: " . $err_msgs;
            $result_arr['error_sale_ids'] = $err_inv_ids;
        }
        if (count($succ_inv_ids))
            $result_arr['sales'] = $succ_inv_ids;
        if (!count($result_arr))
            $this->die_error('no sales inserted!');
        die(json_encode($result_arr));
    }

    //
    public function get_sales() {
        $arr = array();
        $query = "SELECT `Order_ID`,
                        `Order_ID_Auto` as sale_number,
                        `Supplier_ID` as customer_id,
                        `Order_Date` as sale_date, 
                        `Currency_ID` as currency_id,
                        `sale_due_date` as sale_due_date,
                        current_rate as currency_rate,order_description as sale_notes,
                        discount_percent as sale_discount_percentage,discount_amount as sale_discount_value,
                        sub_total as sale_sub_total,net_total as sale_net_total,total_vat as sale_vat_total,
                        `sale_app_id` as sale_id, 
                         0 as sale_is_new,is_actual
                        from tbl_purchase_order where app_user_id = ? and is_sales = 1 and is_quotation = 0 ";
        $stmt = $this->db->query_execute($query, array($this->get_login_user_id()));
        while ($row = $this->db->query_fetch($stmt)) {
            $row['sale_sub_total'] = 0;
            $row['sale_discount_value'] = 0;
            $arr[$row['Order_ID']] = $row;
        }
        //
        if (count($arr)) {
            $sales_ids = implode(',', array_keys($arr));
            $details_query = "select i.Order_ID,
                                i.Item_Quantity_Current AS Item_Quantity_Current,
                               i.Item_ID AS Product_ID, i.Unit_Price as sale_detail_unit_price,
                                (`Unit_Price`+((`Unit_Price`*vat_percent)/100)) as sale_detail_unit_price_with_vat,
                                total_price as sale_detail_total_price,
                                (`total_price`+vat_amount) as sale_detail_total_price_with_vat,
                                 i.UOM_ID as sale_detail_uom_id,
                                 `Item_Quantity_Current` as User_Product_Quantity,
                                `Quantity` as sale_detail_default_quantity,
                                vat_percent as sale_detail_vat_perc,
                                vat_amount as sale_detail_vat_value,
                                i.discount_percent as sale_detail_discount_percentage,
                                ((Unit_Price*i.discount_percent*Item_Quantity_Current)/100) as sale_detail_discount_value
                            from tbl_purchase_order_details i 
                            where i.Order_ID in (" . $sales_ids . ")";
            $detail_stmt = $this->db->query_execute($details_query);
            while ($row_det = $this->db->query_fetch($detail_stmt)) {
                $row_det['sale_id'] = $arr[$row_det['Order_ID']]['sale_id'];
                //
                $row_det['sale_detail_unit_price_with_vat'] = round($row_det['sale_detail_unit_price_with_vat'], 7);
                $row_det['sale_detail_total_price_with_vat'] = round($row_det['sale_detail_total_price_with_vat'], 7);
                $row_det['sale_detail_discount_value'] = round($row_det['sale_detail_discount_value'], 7);
                $arr[$row_det['Order_ID']]['details']['data'][] = $row_det;
                //
                $arr[$row_det['Order_ID']]['sale_sub_total'] += ($row_det['sale_detail_unit_price_with_vat'] * $row_det['User_Product_Quantity'] * (1 - ($row_det['sale_detail_discount_percentage'] / 100)));
                $arr[$row_det['Order_ID']]['sale_discount_value'] = ($arr[$row_det['Order_ID']]['sale_sub_total'] * (($arr[$row_det['Order_ID']]['sale_discount_percentage'] / 100)));
            }
        }
        die(json_encode(array('sales' => array_values($arr))));
    }

}
