<?php

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

class app extends app_base_class {

    public function die_error($error_msg) {
        //header("HTTP/1.0 500 Internal Server Error");
        die(json_encode(array('error' => $error_msg)));
    }

    //
    private function validate_date_time($date_time, $format = 'Y-m-d') {
        $d = DateTime::createFromFormat($format, $date_time);
        return ($d !== false && $d->format($format) === $date_time);
    }

    /**
     * Functions for send data to App. from server
     * ====================================================================>
     */
    public function get_addons() {
        $stmt = $this->db->query_execute("SELECT * FROM `tbl_add_ons_details` ");
        $addons_arr = array();
        $addons_dtl_arr = array();
        while ($row = $this->db->query_fetch($stmt))
            $addons_dtl_arr[$row['add_ons_id']][] = array('add_ons_details_id' => $row['add_ons_details_id'], 'add_ons_details_title' => $row['add_ons_details_title']);
        //
        $stmt = $this->db->query_execute("SELECT * FROM `tbl_add_ons` ");
        while ($row = $this->db->query_fetch($stmt))
            if (!empty($addons_dtl_arr[$row['add_ons_id']])) {
                $row['detail'] = $addons_dtl_arr[$row['add_ons_id']];
                $addons_arr[] = $row;
            }
        //
        die(json_encode(array('data' => $addons_arr)));
    }

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

    public function get_categories() {
        $category_arr = $this->select_query("select Cat_ID, Parent_ID, Cat_Name, category_color from tbl_product_cat");
        die(json_encode(array('data' => $category_arr)));
    }

    public function get_brands() {
        $brand_arr = $this->select_query("select brand_id, brand_name from tbl_brand_products");
        die(json_encode(array('data' => $brand_arr)));
    }

    public function get_families() {
        $family_arr = $this->select_query("select family_id, family_name from tbl_inventory_family");
        die(json_encode(array('data' => $family_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 = array();
        $cond = "";
        $param_arr = array();
        if (!empty($this->_body['category_id'])) {
            $cond .= " and prod.Category_ID = ? ";
            $param_arr[] = $this->_body['category_id'];
        }
        $stmt = $this->db->query_execute("select Product_ID, Product_Code, product_second_code, Product_Name,prod.Category_ID 
                        , sum(if(isnull(prod_loc.Product_Depot_ID),0,Product_Depot_Quantity)) as Product_Quantity
                        , vat_percent as Product_Vat, UOM_ID, product_description, if(prod.Status_ID = 1, 0, 1) as Product_Disabled
                        , product_param1, product_param2, product_param3, product_param4
                        , brand_id, family_id, date_created as created_time, date_time as modified_time
                    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 = '{$this->get_login_data('depot_id')}') 
                    where product_type in (0,1) {$cond}
                    group by prod.Product_ID ", $param_arr);
        while ($row = $this->db->query_fetch($stmt)) {
            $row['uoms'] = array();
            $row['add_ons'] = array();
            $row['product_image_url'] = file_exists(dirBase . "uploads/products_photos/{$row['Product_ID']}.jpg") ? SITE_LINK . "uploads/products_photos/{$row['Product_ID']}.jpg" : "";
            $products_arr[$row['Product_ID']] = $row;
        }
        $this->get_products_details($products_arr);
        //    
        die(json_encode(array('data' => $products_arr)));
    }

    //
    private function get_products_details(&$products_arr) {
        $api_class_id = $this->get_login_data('default_class_id');
        $class_is_ttc = $this->get_login_data('default_class_is_ttc');
        //
        if (empty($products_arr))
            return;
        //if want to send another class id
        $api_other_class_id = $this->get_login_data('api__other_data', 'other_class_id');
        //
        $product_ids = implode(',', array_keys($products_arr));
        //get products related uoms
        $stmt = $this->db->query_execute("select uom_conv.Product_ID , uom_conv.`conversion_amount` as conversion_amount,
                uom_conv.`uom_from` as uom_id, uom_conv.`is_default`, prices.Product_Price as Price, prices.product_disc as discount_percentage
            FROM `tbl_uom_conversions` uom_conv
                left join tbl_item_price_by_classes prices on prices.Product_ID = uom_conv.Product_ID and prices.UOM_ID = uom_conv.uom_from and prices.Class_ID = ? 
            where uom_conv.Product_ID in ({$product_ids}) ", array($api_class_id));
        while ($row = $this->db->query_fetch($stmt)) {
            $product_vat = $products_arr[$row['Product_ID']]['Product_Vat'];
            if (empty($row['Price']))
                $row['Price'] = 0;
            //here always send the ttc price
            if ($product_vat > 0 && $class_is_ttc == 0)
                $row['Price'] = $row['Price'] * (1 + ($product_vat / 100));
            //
            $tmp_price_aftr_disc = $row['Price'];
            if ($row['discount_percentage'] > 0) {
                $tmp_price_aftr_disc /= (1 + ($product_vat / 100)); //remove vat
                $tmp_price_aftr_disc *= ((100 - $row['discount_percentage']) / 100);    //subtract disc.
                $tmp_price_aftr_disc *= (1 + ($product_vat / 100)); //add vat
            }
            $row['price_after_disc'] = $tmp_price_aftr_disc;
            //
            $row['Price'] = round($row['Price'], 5);
            $row['price_after_disc'] = round($row['price_after_disc'], 5);
            //
            if (!empty($api_other_class_id)) {
                $row['other_price'] = 0;
                $row['other_price_after_disc'] = 0;
            }
            //
            $products_arr[$row['Product_ID']]['uoms'][$row['uom_id']] = $row;
        }
        //get the another class id
        if (!empty($api_other_class_id)) {
            $stmt = $this->db->query_execute("select is_ttc from tbl_pricing_class where Class_ID = ? ", array($api_other_class_id));
            $curr_row = $this->db->query_fetch($stmt);
            $other_class_is_ttc = $curr_row['is_ttc'];
            //
            $stmt = $this->db->query_execute("select Product_ID, UOM_ID, Product_Price as other_price, product_disc as discount_percentage
                FROM tbl_item_price_by_classes
                where Product_Price <> 0 and Class_ID = ? and Product_ID in ({$product_ids}) ", array($api_other_class_id));
            while ($row = $this->db->query_fetch($stmt)) {
                if (isset($products_arr[$row['Product_ID']]['uoms'][$row['UOM_ID']])) {
                    $product_vat = $products_arr[$row['Product_ID']]['Product_Vat'];
                    if (empty($row['other_price']))
                        $row['other_price'] = 0;
                    //here always send the ttc price
                    if ($product_vat > 0 && $other_class_is_ttc == 0)
                        $row['other_price'] = $row['other_price'] * (1 + ($product_vat / 100));
                    //
                    $tmp_price_aftr_disc = $row['other_price'];
                    if ($row['discount_percentage'] > 0) {
                        $tmp_price_aftr_disc /= (1 + ($product_vat / 100)); //remove vat
                        $tmp_price_aftr_disc *= ((100 - $row['discount_percentage']) / 100);    //subtract disc.
                        $tmp_price_aftr_disc *= (1 + ($product_vat / 100)); //add vat
                    }
                    $row['other_price_after_disc'] = $tmp_price_aftr_disc;
                    //
                    $products_arr[$row['Product_ID']]['uoms'][$row['UOM_ID']]['other_price'] = round($row['other_price'], 5);
                    $products_arr[$row['Product_ID']]['uoms'][$row['UOM_ID']]['other_price_after_disc'] = round($row['other_price_after_disc'], 5);
                    $products_arr[$row['Product_ID']]['uoms'][$row['UOM_ID']]['other_discount_percentage'] = $row['discount_percentage'];
                }
            }
        }
        //get products related add-ons if exist
        $stmt = $this->db->query_execute("select * from tbl_product_add_ons where product_id in ({$product_ids}) ");
        while ($row = $this->db->query_fetch($stmt)) {
            $products_arr[$row['product_id']]['add_ons'][] = $row;
        }
        //
    }

    public function get_countries() {
        $country_arr = $this->select_query("select Country_ID,Country_Name,Code as Country_Code from tbl_all_countries");
        die(json_encode(array('data' => $country_arr)));
    }

    public function get_cities() {
        $cond = '';
        $param_arr = array();
        if (!is_null($this->_body['country_id'])) {
            $cond .= ' and countries.Country_ID = ? ';
            $param_arr[] = $this->_body['country_id'];
        }
        $stmt = "select cities.ID as City_ID,cities.Name as City_Name, countries.Country_ID
                    from tbl_all_cities cities
                        inner join tbl_all_countries countries on countries.Code = cities.CountryCode 
                    where 1=1 " . $cond;
        $query = $this->select_query($stmt, $param_arr);
        die(json_encode(array('data' => $query)));
    }

    //
    public function get_customers() {
        $arr = array();
        $query = "select c.Customer_ID,c.Customer_Name,c.Customer_Mobile,
            country.Country_Name,cities.Name as City_Name,
            ship_address.shipping_title as Customer_Shipping_Title,
            ship_address.shipping_street as Customer_Street,ship_address.shipping_building as Customer_Building,
            ship_address.shipping_address as Customer_Address,c.app_id as customer_id,
            c.Customer_City_ID,c.Customer_Country_ID
        from tbl_customers c
            left join tbl_customer_shipping_address ship_address on c.Customer_ID = ship_address.Customer_ID
            left join tbl_all_countries country on country.Country_ID = c.Customer_Country_ID
            left join tbl_all_cities cities  on cities.ID = c.Customer_City_ID
        where c.type = 3 and c.web_api_user_id = ? ";
        $stmt = $this->db->query_execute($query, array($this->get_login_data('api__user_id')));
        while ($row = $this->db->query_fetch($stmt)) {
            $arr[] = $row;
        }
        die(json_encode(array('data' => $arr)));
    }

    /**
     * Functions for add data to server from App.
     * ====================================================================>
     */
    public function add_customers() {
        $this->fill_trusto_session();
        //
        $customer = new Customer();
        $inserted_customers_arr = array();
        $user = $this->get_login_user_id();
        $this->db->transaction_start();
        $body_result = $this->_body;
        if (empty($body_result) || empty($body_result['customers']) || !is_array($body_result['customers']))
            $this->die_error('no customers send to add them!');
        //        row should include: Customer_Name, Customer_Mobile, Customer_Country_ID,app_id,
        //        Customer_City_ID, Customer_Address, shipping_title,shipping_street, shipping_building, shipping_address,is_default,
        $err_cust_ids = array();
        $err_msgs = "";
        //
        foreach ($body_result['customers'] as $row) {
            if (empty($row['customer_id']) || !is_numeric($row['customer_id']) || empty($row['Customer_Name']) || empty($row['Customer_Mobile'])) {
                $err_cust_ids[] = $row["Customer_ID"];
                $err_msgs .= "Customer {$row["Customer_ID"]}, Fill All Required Fields. (Id, Name, Mobile,..)<br/>";
                continue;
            }
            //
            $row['web_api_user_id'] = $this->get_login_data('api__user_id');
            $row['app_id'] = $row['customer_id'];
            $row['type'] = 3;
            $row['Customer_Class_ID'] = $this->get_login_data('default_class_id');
            $row['Currency_ID'] = $this->get_login_data('default_currency_id');
            $row['Logged_ID'] = $user;
            if (!empty($row['shipping_address'])) {
                $row['shipping_address'] = array(array(
                        'shipping_title' => 'Automatic API Address',
                        'shipping_street' => $row['shipping_street'],
                        'shipping_building' => $row['shipping_building'],
                        'shipping_address' => $row['shipping_address'],
                        'is_default' => 1,
                        'city_id' => $row['Customer_City_ID'],
                ));
                $row['shipping_address'] = json_encode($row['shipping_address']);
            }
            $check_customer = $this->db->query_execute('select Customer_ID from tbl_customers where app_id = ?
                and web_api_user_id = ? and type=3', [&$row['customer_id'], &$row['web_api_user_id']]);
            unset($row['customer_id']); //customer_id is the web customer id..
            if ($this->db->query_result_count($check_customer) == 0) {
                $insert = $customer->setData($row)->createCustomer();
            } else {
                $check_row = $this->db->query_fetch($check_customer);
                $row['Customer_ID'] = $check_row['Customer_ID'];
                $insert = $customer->setData($row)->updateCustomer();
            }
            if (empty($insert))
                $inserted_customers_arr[] = $row['app_id'];
            else {
                $err_cust_ids[] = $row["app_id"];
                $err_msgs .= "Customer {$row["app_id"]}, " . implode(', ', $insert) . ".<br/>";
            }
        }
        //
        $this->db->transaction_end();
        //
        $return_arr = array('data' => $inserted_customers_arr, 'err_cust_ids' => $err_cust_ids, 'error_info' => $err_msgs);
        die(json_encode($return_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 = $this->_body;
        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
        $api_cstmr_id = $this->get_login_data('api__other_data', 'customer_id');
        $invoice_date = date('Y-m-d');
        foreach ($body_result['invoices'] as $row) {
            //here if define specific customer id in api, then if we don't receive an customer id we use this
            if (empty($row['Invoice_ID']) || !is_numeric($row['Invoice_ID']) || empty($row['products']) || !is_array($row['products']) ||
                    ((empty($row['Customer_ID']) || !is_numeric($row['Customer_ID'])) && empty($api_cstmr_id))) {
                $err_inv_ids[] = $row["Invoice_ID"];
                $err_msgs .= "Invoice {$row["Invoice_ID"]}, Fill All Required Fields. (Id, Products Arr., Customer [from record or api])<br/>";
                continue;
            }
            if (!empty($row['date_created'])) {
                if (!$this->validate_date_time($row['date_created'])) {
                    $err_inv_ids[] = $row["Invoice_ID"];
                    $err_msgs .= "Invoice {$row["Invoice_ID"]}, Bad Created Time Format.<br/>";
                    continue;
                }
                $row['date_time'] = $row['date_created'];
            } else
                unset($row['date_time']);
            //
            $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['web_api_inv_id'] = $app_inv_id;
            $row['web_api_user_id'] = $this->get_login_data('api__user_id');
            if (!empty($row['Customer_ID'])) {
                $stmt = $this->db->query_execute("select Customer_ID from tbl_customers
                        where app_id =? and web_api_user_id=? and type=3 ", array($row["Customer_ID"], $row['web_api_user_id']));
                $cstmr_id_row = $this->db->query_fetch($stmt);
                if (empty($cstmr_id_row)) {
                    $err_inv_ids[] = $app_inv_id;
                    $err_msgs .= "Customer {$row["Customer_ID"]} does not exist<br/>";
                    continue;
                }
                $cust_id = $cstmr_id_row['Customer_ID'];
            } else
                $cust_id = $api_cstmr_id;
            $row['Customer_ID'] = $cust_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($cust_id));
            $cstmr_row = $this->db->query_fetch($stmt);
            $customer_account = $cstmr_row['Account_ID'];
            $customer_name = $cstmr_row['Customer_Name'];
            $inv_rtrn = null;
            //
            $check_invoice = $this->db->query_execute('select Invoice_ID_Auto from tbl_invoices 
                    where web_api_inv_id = ? and web_api_user_id = ?', array($app_inv_id, $row['web_api_user_id']));
            if ($this->db->query_result_count($check_invoice) == 0 || $this->check_app_permission('app_api_updt_inv')) {
                $exist_inv_auto_id = 0;
                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;
                //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['Depot_ID'] = $this->get_login_data('depot_id');
                if (empty($row['Invoice_Currency'])) {
                    $row['Invoice_Currency'] = $this->get_login_data('default_currency_id');
                    $row['CurrencySymbole'] = $this->get_login_data('default_currency_symbol');
                }
                $row['Class_ID'] = $this->get_login_data('default_class_id');
                $row['Invoice_Date'] = $invoice_date;
                //$row['Invoice_Description'] = '';
                $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'] = '';
                if (empty($row['shipping']))
                    $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'] = '';
                $row['Invoice_Discount'] = empty($row['inv_discount_percent']) ? 0 : floatval($row['inv_discount_percent']);   //whole invoice disc. percentage
                $row['Invoice_Discount'] = min($row['Invoice_Discount'], 100);
                //
                $sub_total = 0;
                $total_vat = 0;
                $row['Products'] = array();
                $is_valid_rows = true;
                foreach ($row['products'] as $inv_dtl_row) {
                    if (empty($inv_dtl_row['product_id']) || !is_numeric($inv_dtl_row['product_id']) ||
                            empty($inv_dtl_row['uom_id']) || !is_numeric($inv_dtl_row['uom_id']) ||
                            !is_numeric($inv_dtl_row['quantity']) || !is_numeric($inv_dtl_row['unit_price'])) {
                        $err_inv_ids[] = $row["Invoice_ID"];
                        $err_msgs .= "Invoice {$row["Invoice_ID"]}, Fill All Detail Required Fields. (Product, Uom, Qty, Price)<br/>";
                        $is_valid_rows = false;
                        break;
                    }
                    $prdct_row = $this->get_product_info($inv_dtl_row['product_id']);
                    if (empty($prdct_row) || !in_array($inv_dtl_row['uom_id'], $prdct_row['uom_ids'])) {
                        $err_inv_ids[] = $row["Invoice_ID"];
                        $err_msgs .= "Invoice {$row["Invoice_ID"]}, Product/UOM ID Error.<br/>";
                        $is_valid_rows = false;
                        break;
                    }
                    //the unit price we receive is ttc price for that we remove the vat from it
                    $vat_percent = $prdct_row['vat_percent'];
                    $itm_disc_per = empty($inv_dtl_row['discount_percent']) ? 0 : floatval($inv_dtl_row['discount_percent']);
                    $itm_disc_per = min($itm_disc_per, 100);
                    $unit_price_bfr_vat = ((floatval($inv_dtl_row['unit_price']) * 100) / (100 + floatval($vat_percent)));
                    $unit_price_aftr_row_disc = (($unit_price_bfr_vat * (100 - $itm_disc_per)) / 100);
                    $unit_price_aftr_both_disc = (($unit_price_aftr_row_disc * (100 - $row['Invoice_Discount'])) / 100);
                    $vat_amount = (($unit_price_aftr_both_disc * $inv_dtl_row['quantity'] * $vat_percent) / 100);   //vat after both disc
                    $sub_total += (($inv_dtl_row['quantity'] * $unit_price_aftr_row_disc)); //after row discount only
                    $total_vat += $vat_amount;
                    //get default uom id
                    $row['Products'][] = [
                        'UOM_ID' => $inv_dtl_row['uom_id'],
                        'Product_ID' => $inv_dtl_row['product_id'],
                        'Item_Quantity' => $inv_dtl_row['quantity'],
                        'Item_Price' => $unit_price_bfr_vat, //unit price before vat
                        'vat_percent' => $vat_percent,
                        'vat_val' => $vat_amount,
                        'Item_Discount' => $itm_disc_per,
                        'Discount_Amount' => ($inv_dtl_row['quantity'] * $unit_price_bfr_vat * ( $itm_disc_per / 100)),
                        'Product_Total_Price' => ($inv_dtl_row['quantity'] * $unit_price_aftr_both_disc), //after both disc and before vat
                        'product_description' => (empty($inv_dtl_row['product_description']) ? '' : $inv_dtl_row['product_description'])
                    ];
                }
                if (!$is_valid_rows)
                    continue;
                unset($row['products']);
                $row['discount_amount'] = (($sub_total * $row['Invoice_Discount']) / 100);
                $row['Invoice_Sub_Total'] = $sub_total;
                $row['Invoice_VAT'] = $total_vat;
                $row['Invoice_Net_Total'] = $sub_total + $total_vat - $row['discount_amount'];
                if (!empty($row['is_full_paid']) && $row['is_full_paid'] == 1)
                    $row['Cash_Amount'] = $row['Invoice_Net_Total'] + $row['shipping'];
                //
                if (empty($exist_inv_auto_id))
                    $inv_rtrn = $InventoryAjax->get_registry()->invoice->setData($row)->insertSalesInvoice()[0];
                else
                    $inv_rtrn = $InventoryAjax->get_registry()->invoice->setData($row)->editInvoice()[0];
                //
            } else
                $inv_rtrn = 1;
            //
            if (is_numeric($inv_rtrn)) {
                $succ_inv_ids[] = $app_inv_id;
            } else {
                $err_inv_ids[] = $app_inv_id;
                $err_msgs .= $inv_rtrn . "<br/>";
            }
        }
        $result_arr = array('success_inv_ids' => array(), 'error_inv_ids' => array(), 'error_info' => '');
        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['success_inv_ids'] = $succ_inv_ids;
        if (!count($result_arr['success_inv_ids']) && !count($result_arr['error_inv_ids']))
            $this->die_error('no invoices inserted!');
        die(json_encode($result_arr));
    }

    //
    private function get_product_info($product_id) {
        static $products_arr = array();
        if (!isset($products_arr[$product_id])) {
            $products_arr[$product_id] = false; //don't put null since we are checking if isset here
            $stmt = $this->db->query_execute('select Product_ID, vat_percent from tbl_stock_products where Product_ID = ? ', array($product_id));
            $row = $this->db->query_fetch($stmt);
            if (!empty($row)) {
                $row['uom_ids'] = array();
                //get product related uoms
                $uom_stmt = $this->db->query_execute('select uom_from from tbl_uom_conversions where Product_ID = ? ', array($product_id));
                $uom_row = $this->db->query_fetch($uom_stmt);
                $row['uom_ids'][] = $uom_row['uom_from'];
                $products_arr[$product_id] = $row;
            }
        }
        return $products_arr[$product_id];
    }

    public function add_returns() {
        //
        $succ_inv_ids = array();
        $err_inv_ids = array();
        $err_msgs = null;
        $this->fill_trusto_session();
        //
        $InventoryAjax = new InventoryAjax();  //we need it as it initialize the model and classes we will use
        $body_result = $this->_body;
        if (empty($body_result) || empty($body_result['returns']) || !is_array($body_result['returns']))
            $this->die_error('no returns send to insert them!');
        $api_cstmr_id = $this->get_login_data('api__other_data', 'customer_id');
        $invoice_date = date('Y-m-d');
        foreach ($body_result['returns'] as $row) {
            if (empty($row['Invoice_ID']) || !is_numeric($row['Invoice_ID']) || empty($row['products']) || !is_array($row['products']) ||
                    ((empty($row['Customer_ID']) || !is_numeric($row['Customer_ID'])) && empty($api_cstmr_id))) {
                $err_inv_ids[] = $row["Invoice_ID"];
                $err_msgs .= "Return {$row["Invoice_ID"]}, Fill All Required Fields. (Id, Products Arr., Customer [from record or api])<br/>";
                continue;
            }
            $app_inv_id = $row["Invoice_ID"];
            $row['invoice_prefix'] = $this->get_login_data('serial_prefix');
            $logged_id = $this->get_login_user_id();
            $row['web_api_inv_id'] = $app_inv_id;
            $row['web_api_user_id'] = $this->get_login_data('api__user_id');
            if (!empty($row['Customer_ID'])) {
                $stmt = $this->db->query_execute("select Customer_ID from tbl_customers
                        where app_id =? and web_api_user_id=? and type=3 ", array($row["Customer_ID"], $row['web_api_user_id']));
                $cstmr_id_row = $this->db->query_fetch($stmt);
                if (empty($cstmr_id_row)) {
                    $err_inv_ids[] = $app_inv_id;
                    $err_msgs .= "Customer {$row["Customer_ID"]} does not exist<br/>";
                    continue;
                }
                $cust_id = $cstmr_id_row['Customer_ID'];
            } else
                $cust_id = $api_cstmr_id;
            $row['Customer_ID'] = $cust_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($cust_id));
            $cstmr_row = $this->db->query_fetch($stmt);
            $customer_account = $cstmr_row['Account_ID'];
            //$customer_name = $cstmr_row['Customer_Name'];
            $inv_rtrn = null;
            $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 || $this->check_app_permission('app_api_updt_inv')) {
                $exist_rtrn_id = 0;
                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;
                }
                $row['Person_Account'] = $customer_account;
                $row['Return_Invoice_Date'] = $invoice_date;
                //$row['Return_Invoice_Total'] = $row['Invoice_Net_Total'];
                $row['Return_Invoice_Note'] = empty($row['Invoice_Description']) ? '' : $row['Invoice_Description'];
                $row['Is_Actual'] = 0;
                if (empty($row['Invoice_Currency']))
                    $row['Currency_ID'] = $this->get_login_data('default_currency_id');
                else
                    $row['Currency_ID'] = $row['Invoice_Currency'];
                $row['Person_ID'] = $cust_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'] = $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'] = '';
                $row['Check_Account'] = '';
                $row['Invoice_Purchase_ID'] = NULL;
                //$row['Invoice_VAT'] = $row['invoice_vat_total'];
                $row['discount_percent'] = empty($row['inv_discount_percent']) ? 0 : floatval($row['inv_discount_percent']);   //whole invoice disc. percentage
                $row['discount_percent'] = min($row['discount_percent'], 100);
                //
                $sub_total = 0;
                $total_vat = 0;
                $row['Products'] = array();
                $is_valid_rows = true;
                foreach ($row['products'] as $inv_dtl_row) {
                    if (empty($inv_dtl_row['product_id']) || !is_numeric($inv_dtl_row['product_id']) ||
                            empty($inv_dtl_row['uom_id']) || !is_numeric($inv_dtl_row['uom_id']) ||
                            !is_numeric($inv_dtl_row['quantity']) || !is_numeric($inv_dtl_row['unit_price'])) {
                        $err_inv_ids[] = $row["Invoice_ID"];
                        $err_msgs .= "Return {$row["Invoice_ID"]}, Fill All Detail Required Fields. (Product, Uom, Qty, Price)<br/>";
                        $is_valid_rows = false;
                        break;
                    }
                    $prdct_row = $this->get_product_info($inv_dtl_row['product_id']);
                    if (empty($prdct_row) || !in_array($inv_dtl_row['uom_id'], $prdct_row['uom_ids'])) {
                        $err_inv_ids[] = $row["Invoice_ID"];
                        $err_msgs .= "Return {$row["Invoice_ID"]}, Product/UOM ID Error.<br/>";
                        $is_valid_rows = false;
                        break;
                    }
                    $vat_percent = $prdct_row['vat_percent'];
                    $itm_disc_per = empty($inv_dtl_row['discount_percent']) ? 0 : floatval($inv_dtl_row['discount_percent']);
                    $itm_disc_per = min($itm_disc_per, 100);
                    $unit_price_bfr_vat = ((floatval($inv_dtl_row['unit_price']) * 100) / (100 + floatval($vat_percent)));
                    $unit_price_aftr_row_disc = (($unit_price_bfr_vat * (100 - $itm_disc_per)) / 100);
                    $unit_price_aftr_both_disc = (($unit_price_aftr_row_disc * (100 - $row['discount_percent'])) / 100);
                    $vat_amount = (($unit_price_aftr_both_disc * $inv_dtl_row['quantity'] * $vat_percent) / 100);   //vat after both disc
                    $sub_total += (($inv_dtl_row['quantity'] * $unit_price_aftr_row_disc)); //after row discount only
                    $total_vat += $vat_amount;
                    $row['Products'][] = array(
                        'UOM_ID' => $inv_dtl_row['uom_id'], //$this->get_default_uom($inv_dtl_row[0]),
                        'Return_Product_Quantity' => $inv_dtl_row['quantity'],
                        'Product_ID' => $inv_dtl_row['product_id'],
                        'Unit_Cost' => $unit_price_bfr_vat,
                        'vat_percent' => $vat_percent,
                        'vat_val' => $vat_amount,
                        'Item_Discount' => $itm_disc_per,
                        'Discount_Amount' => ($inv_dtl_row['quantity'] * $unit_price_bfr_vat * ( $itm_disc_per / 100)),
                        'Return_Product_Total' => ($inv_dtl_row['quantity'] * $unit_price_aftr_both_disc), //after both disc and before vat
                    );
                }
                if (!$is_valid_rows)
                    continue;
                $row['discount_amount'] = $sub_total * $row['discount_percent'] / 100;
                $row['Return_Invoice_Total'] = $sub_total;
                $row['Invoice_Net_Total'] = $sub_total + $total_vat - $row['discount_amount'];
                $row['Invoice_VAT'] = $total_vat;
                if (!empty($row['is_full_paid']) && $row['is_full_paid'] == 1)
                    $row['Cash_Amount'] = $row['Invoice_Net_Total']/* + $row['shipping'] */;
                //
                if (empty($exist_rtrn_id))
                    $inv_rtrn = $InventoryAjax->get_registry()->return->setData($row)->ReturnNoInvoice();
                else
                    $inv_rtrn = $InventoryAjax->get_registry()->return->setData($row)->editReturnNoInvoice();
                //
            } else
                $inv_rtrn = 1;
            //
            if (is_numeric($inv_rtrn)) {
                $succ_inv_ids[] = $app_inv_id;
            } else {
                $err_inv_ids[] = $app_inv_id;
                $err_msgs .= $inv_rtrn . "<br/>";
            }
        }
        $result_arr = array('success_inv_ids' => array(), 'error_inv_ids' => array(), 'error_info' => '');
        if (count($err_inv_ids)) {
            $result_arr['error_info'] = "Error Occured while inserting return invoices: " . $err_msgs;
            $result_arr['error_inv_ids'] = $err_inv_ids;
        }
        if (count($succ_inv_ids))
            $result_arr['success_inv_ids'] = $succ_inv_ids;
        if (!count($result_arr['success_inv_ids']) && !count($result_arr['error_inv_ids']))
            $this->die_error('no return invoices inserted!');
        die(json_encode($result_arr));
    }

    public function add_orders() {
        //
        $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 = $this->_body;
        if (empty($body_result) || empty($body_result['orders']) || !is_array($body_result['orders']))
            $this->die_error('no orders 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
        $api_cstmr_id = $this->get_login_data('api__other_data', 'customer_id');
        $invoice_date = date('Y-m-d');
        foreach ($body_result['orders'] as $row) {
            if (empty($row['Invoice_ID']) || !is_numeric($row['Invoice_ID']) || empty($row['products']) || !is_array($row['products']) ||
                    ((empty($row['Customer_ID']) || !is_numeric($row['Customer_ID'])) && empty($api_cstmr_id))) {
                $err_inv_ids[] = $row["Invoice_ID"];
                $err_msgs .= "Order {$row["Invoice_ID"]}, Fill All Required Fields. (Id, Products Arr., Customer [from record or api])<br/>";
                continue;
            }
            $app_inv_id = $row["Invoice_ID"];
            $row['sale_prefix'] = $this->get_login_data('serial_prefix');
            $logged_id = $this->get_login_user_id();
            $row['web_api_ord_id'] = $app_inv_id;
            $row['web_api_user_id'] = $this->get_login_data('api__user_id');
            if (!empty($row['Customer_ID'])) {
                $stmt = $this->db->query_execute("select Customer_ID from tbl_customers
                        where app_id =? and web_api_user_id=? and type=3 ", array($row["Customer_ID"], $row['web_api_user_id']));
                $cstmr_id_row = $this->db->query_fetch($stmt);
                if (empty($cstmr_id_row)) {
                    $err_inv_ids[] = $app_inv_id;
                    $err_msgs .= "Customer {$row["Customer_ID"]} does not exist<br/>";
                    continue;
                }
                $row['Customer_ID'] = $cstmr_id_row['Customer_ID'];
            } else
                $row['Customer_ID'] = $api_cstmr_id;
            //
            $inv_rtrn = null;
            //
            $check_invoice = $this->db->query_execute('select Order_ID from tbl_purchase_order where web_api_ord_id = ? 
                    and web_api_user_id = ? and is_sales = 1 and is_quotation = 0 ', array($app_inv_id, $row['web_api_user_id']));
            if ($this->db->query_result_count($check_invoice) == 0 || $this->check_app_permission('app_api_updt_ordr')) {
                $exist_inv_auto_id = 0;
                if ($this->db->query_result_count($check_invoice) > 0) {
                    $exist_inv_auto_id = $this->db->query_fetch($check_invoice)['Order_ID'];
                    $row['ID_Auto'] = $exist_inv_auto_id;
                }
                $row['Logged_ID'] = $logged_id;
                $row['Depot_ID'] = $this->get_login_data('depot_id');
                if (empty($row['Invoice_Currency'])) {
                    $row['Invoice_Currency'] = $this->get_login_data('default_currency_id');
                }
                $row['Class_ID'] = $this->get_login_data('default_class_id');
                $row['Invoice_Date'] = $invoice_date;
                $row['is_actual'] = 0;
                $row['IsSales'] = 1;
                $row['is_quotation'] = 0;
                $row['total_cost'] = 0;
                if (empty($row['shipping']))
                    $row['shipping'] = 0;
                //$row['shipping_account'] = $this->get_login_data('sales_ship_account_id');
                $row['Invoice_Discount'] = empty($row['inv_discount_percent']) ? 0 : floatval($row['inv_discount_percent']);   //whole invoice disc. percentage
                $row['Invoice_Discount'] = min($row['Invoice_Discount'], 100);
                //
                $sub_total = 0;
                $total_vat = 0;
                $row['Products'] = array();
                $is_valid_rows = true;
                foreach ($row['products'] as $inv_dtl_row) {
                    if (empty($inv_dtl_row['product_id']) || !is_numeric($inv_dtl_row['product_id']) ||
                            empty($inv_dtl_row['uom_id']) || !is_numeric($inv_dtl_row['uom_id']) ||
                            !is_numeric($inv_dtl_row['quantity']) || !is_numeric($inv_dtl_row['unit_price'])) {
                        $err_inv_ids[] = $row["Invoice_ID"];
                        $err_msgs .= "Order {$row["Invoice_ID"]}, Fill All Detail Required Fields. (Product, Uom, Qty, Price)<br/>";
                        $is_valid_rows = false;
                        break;
                    }
                    $prdct_row = $this->get_product_info($inv_dtl_row['product_id']);
                    if (empty($prdct_row) || !in_array($inv_dtl_row['uom_id'], $prdct_row['uom_ids'])) {
                        $err_inv_ids[] = $row["Invoice_ID"];
                        $err_msgs .= "Order {$row["Invoice_ID"]}, Product/UOM ID Error.<br/>";
                        $is_valid_rows = false;
                        break;
                    }
                    //the unit price we receive is ttc price for that we remove the vat from it
                    $vat_percent = $prdct_row['vat_percent'];
                    $itm_disc_per = empty($inv_dtl_row['discount_percent']) ? 0 : floatval($inv_dtl_row['discount_percent']);
                    $itm_disc_per = min($itm_disc_per, 100);
                    $unit_price_bfr_vat = ((floatval($inv_dtl_row['unit_price']) * 100) / (100 + floatval($vat_percent)));

                    $unit_price_aftr_row_disc = (($unit_price_bfr_vat * (100 - $itm_disc_per)) / 100);
                    $unit_price_aftr_both_disc = (($unit_price_aftr_row_disc * (100 - $row['Invoice_Discount'])) / 100);
                    $vat_amount = (($unit_price_aftr_both_disc * $inv_dtl_row['quantity'] * $vat_percent) / 100);   //vat after both disc
                    $sub_total += (($inv_dtl_row['quantity'] * $unit_price_aftr_row_disc)); //after row discount only
                    $total_vat += $vat_amount;
                    //get default uom id
                    $row['Products'][] = [
                        'UOM_ID' => $inv_dtl_row['uom_id'],
                        'Product_ID' => $inv_dtl_row['product_id'],
                        'Item_Quantity' => $inv_dtl_row['quantity'],
                        'Item_Discount' => $itm_disc_per, //disc percent
                        'Item_Price' => $unit_price_bfr_vat, //unit price before vat
                        'vat_percent' => $vat_percent,
                        'vat_val' => $vat_amount,
                        'Product_Total_Price' => ($inv_dtl_row['quantity'] * $unit_price_aftr_both_disc), //after both disc and before vat
                        'product_description' => (empty($inv_dtl_row['product_description']) ? '' : $inv_dtl_row['product_description'])
                    ];
                }
                if (!$is_valid_rows)
                    continue;
                unset($row['products']);
                $row['discount_amount'] = $sub_total * $row['Invoice_Discount'] / 100;
                $row['Invoice_Sub_Total'] = $sub_total;
                $row['Invoice_VAT'] = $total_vat;
                $row['Invoice_Net_Total'] = $sub_total + $total_vat - $row['discount_amount'];
                //
                if (empty($exist_inv_auto_id))
                    $inv_rtrn = $InventoryAjax->get_registry()->purchase->setData($row)->addPurchaseOrder()[0];
                else
                    $inv_rtrn = $InventoryAjax->get_registry()->purchase->setData($row)->editPurchaseOrder()[0];
                //
            } else
                $inv_rtrn = 1;
            //
            if (is_numeric($inv_rtrn)) {
                $succ_inv_ids[] = $app_inv_id;
            } else {
                $err_inv_ids[] = $app_inv_id;
                $err_msgs .= $inv_rtrn . "<br/>";
            }
        }
        $result_arr = array('success_inv_ids' => array(), 'error_inv_ids' => array(), 'error_info' => '');
        if (count($err_inv_ids)) {
            $result_arr['error_info'] = "Error Occured while inserting orders: " . $err_msgs;
            $result_arr['error_inv_ids'] = $err_inv_ids;
        }
        if (count($succ_inv_ids))
            $result_arr['success_inv_ids'] = $succ_inv_ids;
        if (!count($result_arr['success_inv_ids']) && !count($result_arr['error_inv_ids']))
            $this->die_error('no orders inserted!');
        die(json_encode($result_arr));
    }

    /**
     * Functions related to customer/supplier
     * ====================================================================>
     */
    public function customer_login() {
        if (empty($this->_body['username']) || empty($this->_body['password']))
            $this->die_error('please send username and password.');
        $stmt = $this->db->query_execute("SELECT c.*, a.Account_ID FROM tbl_customers c
            inner join tbl_chart_of_accounts a on c.Customer_ID = a.main_id and a.main_type = 'cust' and a.sub_type = 'main'
            where ifnull(c.user_name,'') <> '' and ifnull(c.password,'') <> '' and c.user_name = ? and c.password = ? "
                , array($this->_body['username'], $this->_body['password']));
        if ($this->db->query_result_count($stmt) <= 0)
            $this->die_error('wrong username or passsword.');
        $customer_user = $this->db->query_fetch($stmt);
        unset($customer_user['password']);
        if ($customer_user['Status_ID'] != 1)
            $this->die_error('customer user is disabled.');
        //
        $exp_after_in_sec = (86400 * 1);  //1 day
        $customer_token = my_jwt::my_encode($customer_user, $exp_after_in_sec);
        die(json_encode(array('data' => array('customer_token' => $customer_token, 'user_data' => $customer_user))));
    }

    private function check_customer_token() {
        if (empty(empty($this->_body['customer_token'])))
            $this->die_error('Customer Token Error, Empty Token!');
        my_jwt::$is_error_die = false;   //to don't die inside the class
        my_jwt::$error_msg = null;  //clear error message
        $customer_data = my_jwt::my_decode($this->_body['customer_token']);
        if (my_jwt::$error_msg)
            $this->die_error('Customer Token Error: ' . my_jwt::$error_msg);
        return $customer_data;
    }

    public function customer_account_transactions() {
        $customer_data = $this->check_customer_token();
        $acc_id = $customer_data['Account_ID'];
        //
        $this->fill_trusto_session();
        $report = new Report();
        $data = $report->get_account_transactions($acc_id);
        die(json_encode(array('data' => $data)));
    }

    public function track_invoice_code() {
        if (empty($this->_body['track_code']))
            $this->die_error('Error Invoice Code Empty!');
        $inv_obj = CommonFunctions::restore_track_code($this->_body['track_code']);
        if (empty($inv_obj) || empty($inv_obj[0]) || !is_numeric($inv_obj[0]))
            $this->die_error('Error Unexist Invoice Code!');
        $inv_id = $inv_obj[0];
        //
        die(json_encode(array('data' => $data)));
    }

}
