<?php

class Customer {

    protected $info = array();
    private $year_id;
    private $controller_permission;
     

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

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

    public function get_db() {
        return $this->db;
    }

    public function createCustomer() {
        //        echo '<pre>';
        //        print_r($this->info);
        //        die('ok');
        $SID = 0;
        $this->db->query('START TRANSACTION');
        $date_time = date("Y-m-d H:i:s");
        $this->info['branch_id'] = !empty($this->info['branch_id']) ? $this->info['branch_id'] : smc_session()['user_accounts']['branch_id'];
        $this->info['limit_exceed'] = !empty($this->info['limit_exceed']) ? $this->info['limit_exceed'] : '';
        $this->info['customer_password'] = !empty($this->info['customer_password']) ? $this->info['customer_password'] : '';
        $this->info['Customer_Code'] = !empty($this->info['Customer_Code']) ? $this->info['Customer_Code'] : '';
        $this->info['Customer_Email'] = !empty($this->info['Customer_Email']) ? $this->info['Customer_Email'] : '';
        $this->info['Customer_Phone'] = !empty($this->info['Customer_Phone']) ? $this->info['Customer_Phone'] : '';
        $this->info['Customer_Work_Phone'] = !empty($this->info['Customer_Work_Phone']) ? $this->info['Customer_Work_Phone'] : '';
        $this->info['Customer_Company'] = !empty($this->info['Customer_Company']) ? $this->info['Customer_Company'] : '';
        $this->info['Customer_Limit_Balance'] = !empty($this->info['Customer_Limit_Balance']) ? $this->info['Customer_Limit_Balance'] : '';
        $this->info['Customer_Discount'] = !empty($this->info['Customer_Discount']) ? $this->info['Customer_Discount'] : '';
        $this->info['Customer_CR'] = !empty($this->info['Customer_CR']) ? $this->info['Customer_CR'] : '';
        $this->info['Customer_Lat'] = !empty($this->info['Customer_Lat']) ? $this->info['Customer_Lat'] : '';
        $this->info['Customer_Lng'] = !empty($this->info['Customer_Lng']) ? $this->info['Customer_Lng'] : '';
        $this->info['customer_due_date_period'] = !empty($this->info['customer_due_date_period']) ? $this->info['customer_due_date_period'] : '';
        $this->info['customer_user_name'] = !empty($this->info['customer_user_name']) ? $this->info['customer_user_name'] : '';
        $this->info['Is_Vendor'] = !empty($this->info['Is_Vendor']) ? $this->info['Is_Vendor'] : 0;
        $this->info['Customer_Country_ID'] = !empty($this->info['Customer_Country_ID']) ? $this->info['Customer_Country_ID'] : 0;
        $this->info['Customer_City_ID'] = !empty($this->info['Customer_City_ID']) ? $this->info['Customer_City_ID'] : 0;
        $this->info['Customer_Address'] = !empty($this->info['Customer_Address']) ? $this->info['Customer_Address'] : '';
        $this->info['customer_group_id'] = !empty($this->info['customer_group_id']) ? $this->info['customer_group_id'] : NULL;
        $this->info['cust_class_id'] = !empty($this->info['cust_class_id']) ? $this->info['cust_class_id'] : NULL;
        $this->info['other_pay_acc_id'] = !empty($this->info['other_pay_acc_id']) ? $this->info['other_pay_acc_id'] : NULL;
        $this->info['customer_note'] = !empty($this->info['customer_note']) ? $this->info['customer_note'] : '';
        $stop = $this->info['limit_exceed'];
        $pass = md5(sha1(md5($this->info['customer_password'])));
        $account_parent_id = empty($this->info['account_parent_id']) ? null : $this->info['account_parent_id'];
        $allergy = !empty($this->info['allergy_id']) ? $this->info['allergy_id'] : '';
        $intolerance = !empty($this->info['intolerance_id']) ? $this->info['intolerance_id'] : '';
        $logged_id = !empty($this->info['Logged_ID']) ? $this->info['Logged_ID'] : smc_session()['user_logged_id'];
        $web_api_user_id = !empty($this->info['web_api_user_id']) ? $this->info['web_api_user_id'] : null;
        $region_id = !empty($this->info['region_id']) ? $this->info['region_id'] : null;
        $app_id = !empty($this->info['app_id']) ? $this->info['app_id'] : null;
        $type = !empty($this->info['type']) ? $this->info['type'] : 0;
        $data_customer = [&$this->info['Customer_Name'],
            &$this->info['Customer_Code'],
            &$this->info['Customer_Email'],
            &$this->info['Customer_Phone'],
            &$this->info['Customer_Work_Phone'],
            &$this->info['Customer_Mobile'],
            &$this->info['Customer_Company'],
            &$this->info['Customer_Country_ID'],
            &$this->info['Customer_City_ID'],
            &$this->info['Customer_Address'],
            &$this->info['Customer_Limit_Balance'],
            &$this->info['Customer_Discount'],
            &$this->info['Currency_ID'],
            &$this->info['Customer_CR'],
            &$this->info['Customer_Class_ID'],
            &$this->info['Customer_Lat'],
            &$this->info['Customer_Lng'],
            &$this->info['customer_due_date_period'],
            &$account_parent_id,
            &$stop,
            &$logged_id,
            &$this->info['customer_user_name'],
            &$pass,
            &$allergy,
            &$intolerance,
            &$web_api_user_id, &$app_id, &$type,
            &$this->info['customer_group_id'],
            &$this->info['cust_class_id'],
            &$region_id, $this->info['customer_note']
            , $this->info['other_pay_acc_id']
            ,$this->info['customer_vat']

            ,$this->info['customer_additional_number']
            ,$this->info['customer_postal_code']
            ,$this->info['customer_other_id']
            ,$this->info['branch_id']
        ];
        $this->db->prepare_Stmt('INSERT 
                                    INTO 
                                        tbl_customers 
                                    (
                                        Customer_Name,
                                        Customer_Code,
                                        Customer_Email,
                                        Customer_Phone,
                                        Customer_Work_Phone,
                                        Customer_Mobile,
                                        Customer_Company,
                                        Customer_Country_ID,
                                        Customer_City_ID,
                                        Customer_Address,
                                        Customer_Limit_Balance,
                                        Customer_Discount,
                                        Currency_ID,
                                        Customer_CR,
                                        Class_ID,
                                        Customer_Lat,
                                        Customer_Lng,
                                        customer_due_date_period,
                                        account_parent_id,
                                        stop_invoice,
                                        Logged_ID,
                                        user_name,
                                        password,
                                        allergy_id,
                                        intolerance_id,web_api_user_id,app_id,type,
                                        customer_group_id,customer_class_id,region_id,customer_note,other_pay_acc_id,customer_vat,customer_additional_number,customer_postal_code,customer_other_id ,branch_id
                                    )
                                        VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', $data_customer);
        $CID = $this->db->id();
        if (!empty($this->info['contacts'])) {
            $contacts = json_decode($this->info['contacts'], true);
            self::insertCustomerContacts($contacts, $CID);
        }

        if (!empty($this->info['shipping_address'])) {
            $shipping_addresses = json_decode($this->info['shipping_address'], true);
            foreach ($shipping_addresses as $address) {
                $address['fastpax_city_id'] = !empty($address['fastpax_city_id']) ? $address['fastpax_city_id'] : NULL;
                $this->db->prepare_Stmt('insert into tbl_customer_shipping_address 
            (shipping_title,city_id,fastpax_city_id,shipping_street,shipping_building,shipping_address,is_default,Customer_ID) 
            VALUES (?,?,?,?,?,?,?,?)', [&$address['shipping_title'], &$address['city_id'], &$address['fastpax_city_id'], &$address['shipping_street'], &$address['shipping_building'], &$address['shipping_address'], &$address['is_default'], &$CID]);
            }
        }
        if (!empty($this->info['branches'])) {
            $branches = json_decode($this->info['branches'], true);
            foreach ($branches as $branch) {
                $this->db->prepare_Stmt('
                insert into tbl_customer_branches 
            (customer_branch_name,customer_id) 
            VALUES (?,?)', [&$branch['customer_branch_name'], &$CID]);
            }
        }
        if (!empty($this->info['user_id'])) {
            $user_ids = explode(",", $this->info['user_id']);
            foreach ($user_ids as $user_id) {

                $this->db->prepare_Stmt('
                insert into tbl_user_customers 
            (user_id,customer_id) 
            VALUES (?,?)', [&$user_id, &$CID]);
            }
        }
        if (!empty($this->info['sales_man_id'])) {
            $sales_man_ids = explode(",", $this->info['sales_man_id']);
            foreach ($sales_man_ids as $sales_man_id) {
                $this->db->prepare_Stmt('
                insert into tbl_salesman_customers 
            (employee_id,customer_id) 
            VALUES (?,?)', [&$sales_man_id, &$CID]);
            }
        }
        $arr_cust = ['type' => 'cust', 'main_id' => $CID, 'action' => 0, 'date_time' => $date_time,
            'user_id' => $logged_id];
        CommonFunctions::add_user_log($arr_cust);

        if ($this->info['Is_Vendor'] == 1) {
            $data_vendor = [&$this->info['Customer_Name'],
                &$this->info['Customer_Email'],
                &$this->info['Customer_Phone'],
                &$this->info['Customer_Work_Phone'],
                &$this->info['Customer_Mobile'],
                &$this->info['Customer_Company'],
                &$this->info['Customer_Country_ID'],
                &$this->info['Customer_City_ID'],
                &$this->info['Customer_Address'],
                &$this->info['Customer_CR'],
                $this->info['Currency_ID'],
                &$logged_id];
            $this->db->prepare_Stmt('
                                    INSERT 
                                    INTO 
                                        tbl_stock_suppliers 
                                    (
                                        Supplier_Name,
                                        Supplier_Email,
                                        Supplier_Phone,
                                        Supplier_Work_Phone,
                                        Supplier_Mobile,
                                        Supplier_Company,
                                        Supplier_Country,
                                        City_ID,
                                        Supplier_Address,
                                        Supplier_CR,Currency_ID,
                                        Logged_ID
                                    ) 
                                        VALUES 
                                    ( ?,?,?,?,?,?,?,?,?,?,?,?)', $data_vendor);
            $SID = $this->db->id();
            if (!empty($contacts))
                self::insertCustomerContacts($contacts, $SID);
            $arr_supp = ['type' => 'supp', 'main_id' => $SID, 'action' => 0, 'date_time' => $date_time,
                'user_id' => $logged_id];
            CommonFunctions::add_user_log($arr_supp);
        }

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

    public function updateCustomer() {
        $this->db->query('START TRANSACTION');
        $date_time = date("Y-m-d H:i:s");
        //        $logged_id = smc_session()['user_logged_id'];
        $this->info['branch_id'] = !empty($this->info['branch_id']) ? $this->info['branch_id'] : smc_session()['user_accounts']['branch_id'];
        $this->info['limit_exceed'] = !empty($this->info['limit_exceed']) ? $this->info['limit_exceed'] : '';
        $this->info['customer_password'] = !empty($this->info['customer_password']) ? $this->info['customer_password'] : '';
        $this->info['Customer_Code'] = !empty($this->info['Customer_Code']) ? $this->info['Customer_Code'] : '';
        $this->info['Customer_Email'] = !empty($this->info['Customer_Email']) ? $this->info['Customer_Email'] : '';
        $this->info['Customer_Phone'] = !empty($this->info['Customer_Phone']) ? $this->info['Customer_Phone'] : '';
        $this->info['Customer_Work_Phone'] = !empty($this->info['Customer_Work_Phone']) ? $this->info['Customer_Work_Phone'] : '';
        $this->info['Customer_Company'] = !empty($this->info['Customer_Company']) ? $this->info['Customer_Company'] : '';
        $this->info['Customer_Limit_Balance'] = !empty($this->info['Customer_Limit_Balance']) ? $this->info['Customer_Limit_Balance'] : '';
        $this->info['Customer_Discount'] = !empty($this->info['Customer_Discount']) ? $this->info['Customer_Discount'] : '';
        $this->info['Customer_CR'] = !empty($this->info['Customer_CR']) ? $this->info['Customer_CR'] : '';
        $this->info['Customer_Lat'] = !empty($this->info['Customer_Lat']) ? $this->info['Customer_Lat'] : '';
        $this->info['Customer_Lng'] = !empty($this->info['Customer_Lng']) ? $this->info['Customer_Lng'] : '';
        $this->info['customer_due_date_period'] = !empty($this->info['customer_due_date_period']) ? $this->info['customer_due_date_period'] : '';
        $this->info['customer_user_name'] = !empty($this->info['customer_user_name']) ? $this->info['customer_user_name'] : '';
        $this->info['customer_note'] = !empty($this->info['customer_note']) ? $this->info['customer_note'] : '';
        $this->info['Is_Vendor'] = !empty($this->info['Is_Vendor']) ? $this->info['Is_Vendor'] : 0;
        //        $stop = isset($this->info['limit_exceed']) ? 1 : 0;
        $account_parent_id = empty($this->info['account_parent_id']) ? null : $this->info['account_parent_id'];
        $allergy = !empty($this->info['allergy_id']) ? $this->info['allergy_id'] : '';
        $intolerance = !empty($this->info['intolerance_id']) ? $this->info['intolerance_id'] : '';
        $logged_id = !empty($this->info['Logged_ID']) ? $this->info['Logged_ID'] : smc_session()['user_logged_id'];
        $web_api_user_id = !empty($this->info['web_api_user_id']) ? $this->info['web_api_user_id'] : null;
        $app_id = !empty($this->info['app_id']) ? $this->info['app_id'] : null;
        $type = !empty($this->info['type']) ? $this->info['type'] : 0;
        $this->info['customer_group_id'] = !empty($this->info['customer_group_id']) ? $this->info['customer_group_id'] : NULL;
        $this->info['cust_class_id'] = !empty($this->info['cust_class_id']) ? $this->info['cust_class_id'] : NULL;
        $this->info['other_pay_acc_id'] = !empty($this->info['other_pay_acc_id']) ? $this->info['other_pay_acc_id'] : NULL;
        $region_id = !empty($this->info['region_id']) ? $this->info['region_id'] : NULL;
        $crm_customer_id = CommonFunctions::SpecificAttribute('tbl_customers', ['Customer_ID'], [$this->info['Customer_ID']], 'Organization_id');
        if (!empty($crm_customer_id)) {
            $this->db->prepare_Stmt('update tbl_customer_organization 
            set organization_email = ?,organization_customer_name = ?,orgaization_work_phone = ?,
            orgaization_mobile_phone = ?,city_id = ?,organization_address = ?, organization_longitude = ? , organization_latitude= ? , region_id = ?,modified_by = ?,last_modified =?,organization_group_id = ?
            where Organization_id = ? 
            ', [&$this->info['Customer_Email'], &$this->info['Customer_Name'], &$this->info['Customer_Work_Phone'],
                &$this->info['Customer_Mobile'], &$this->info['Customer_City_ID'], &$this->info['Customer_Address'],
                &$this->info['Customer_Lng'], &$this->info['Customer_Lat'], &$region_id,
                &$logged_id, &$date_time, &$this->info['customer_group_id'], &$crm_customer_id]);
        }
        $data = [&$this->info['Customer_Name'],
            &$this->info['Customer_Code'],
            &$this->info['Customer_Email'],
            &$this->info['Customer_Phone'],
            &$this->info['Customer_Work_Phone'],
            &$this->info['Customer_Mobile'],
            &$this->info['Customer_Company'],
            &$this->info['Customer_Country_ID'],
            &$this->info['Customer_City_ID'],
            &$this->info['Customer_Address'],
            &$this->info['Customer_Discount'],
            &$this->info['Customer_Limit_Balance'],
            &$this->info['Currency_ID'],
            &$this->info['Customer_CR'],
            &$this->info['Customer_Class_ID'],
            &$this->info['Customer_Lat'],
            &$this->info['Customer_Lng'],
            &$this->info['customer_due_date_period'],
            &$this->info['limit_exceed'],
            &$account_parent_id,
            &$logged_id,
            &$this->info['customer_user_name'],
            &$allergy,
            &$intolerance, &$web_api_user_id, &$app_id, &$type,
            &$this->info['customer_group_id'],
            &$this->info['cust_class_id'],
            &$region_id, $this->info['customer_note'],
            $this->info['other_pay_acc_id'],
            $this->info['customer_vat'],

            $this->info['customer_additional_number'],
            $this->info['customer_postal_code'],
            $this->info['customer_other_id'],
            $this->info['branch_id'],
            &$this->info['Customer_ID']
        ];

        $this->db->prepare_Stmt('
                                    UPDATE 
                                        tbl_customers
                                    SET 
                                        Customer_Name            = ?,
                                        Customer_Code            = ?,
                                        Customer_Email           = ?,
                                        Customer_Phone           = ?,
                                        Customer_Work_Phone      = ?,
                                        Customer_Mobile          = ?,
                                        Customer_Company         = ?,
                                        Customer_Country_ID      = ?,
                                        Customer_City_ID         = ?,
                                        Customer_Address         = ?,
                                        Customer_Discount        = ?,
                                        Customer_Limit_Balance   = ?,
                                        Currency_ID              = ?,
                                        Customer_CR              = ?,
                                        Class_ID                 = ?,
                                        Customer_Lat             = ?,
                                        Customer_Lng             = ?,
                                        customer_due_date_period = ?,
                                        stop_invoice             = ?,
                                        account_parent_id        = ?,
                                        Logged_ID                = ?,
                                        user_name                = ?,
                                        allergy_id               = ?,
                                        intolerance_id           = ?,
                                        web_api_user_id          = ?,app_id=?,type=?,
                                        customer_group_id = ?,
                                        customer_class_id = ?,
                                        region_id         = ?,
                                        customer_note         = ?,other_pay_acc_id=?,customer_vat=?,customer_additional_number=?,customer_postal_code=?,customer_other_id =?,
                                        branch_id = ?

                                    WHERE 
                                        Customer_ID         = ?', $data);
        $this->db->prepare_Stmt('delete from tbl_customer_supplier_contacts where main_id =?  and type_id= 1', [&$this->info['Customer_ID']]);
        if (!empty($this->info['contacts'])) {
            $contacts = json_decode($this->info['contacts'], true);
            self::insertCustomerContacts($contacts, $this->info['Customer_ID']);
        }
        //
        $shipping_address_condition = '';
        if (!empty($this->info['shipping_address_ids'])) {
            $shipping_address_ids = json_decode($this->info['shipping_address_ids'], true);
            if (!empty($shipping_address_ids))
                $shipping_address_condition .= ' and shipping_address_id not in(' . implode(',', $shipping_address_ids) . ') ';
        }

        //
        $this->db->prepare_Stmt('delete from tbl_customer_shipping_address 
                            where  Customer_ID = ? ' . $shipping_address_condition, [&$this->info['Customer_ID']]);
        if (!empty($this->info['shipping_address'])) {
            $shipping_addresses = json_decode($this->info['shipping_address'], true);
            foreach ($shipping_addresses as $address) {
                $address['fastpax_city_id'] = !empty($address['fastpax_city_id']) ? $address['fastpax_city_id'] : NULL;
                if ($address['shipping_address_id'] == 0) {
                    $this->db->prepare_Stmt('insert into tbl_customer_shipping_address 
            (shipping_title,city_id,fastpax_city_id,shipping_street,shipping_building,shipping_address,is_default,Customer_ID) 
            VALUES (?,?,?,?,?,?,?,?)', [&$address['shipping_title'], &$address['city_id'], &$address['fastpax_city_id'], &$address['shipping_street'], &$address['shipping_building'], &$address['shipping_address'], &$address['is_default'], &$this->info['Customer_ID']]);
                } else {
                    $this->db->prepare_Stmt('update  tbl_customer_shipping_address 
            set shipping_title = ?,city_id = ?,fastpax_city_id = ?,shipping_street = ?,shipping_building = ?,
            shipping_address = ?,is_default = ? where shipping_address_id = ?', [&$address['shipping_title'], &$address['city_id'], &$address['fastpax_city_id'], &$address['shipping_street'], &$address['shipping_building'], &$address['shipping_address'], &$address['is_default'], &$address['shipping_address_id']]);
                }
            }
        }
        $this->db->prepare_Stmt('delete from tbl_customer_branches 
                            where  customer_id = ? ', [&$this->info['Customer_ID']]);
        if (!empty($this->info['branches'])) {
            $branches = json_decode($this->info['branches'], true);
            foreach ($branches as $branch) {
                $this->db->prepare_Stmt('
                insert into tbl_customer_branches 
            (customer_branch_name,customer_id) 
            VALUES (?,?)', [&$branch['customer_branch_name'], &$this->info['Customer_ID']]);
            }
        }

        $this->db->prepare_Stmt('delete from tbl_salesman_customers 
                            where  customer_id = ? ', [&$this->info['Customer_ID']]);
        if (!empty($this->info['sales_man_id'])) {
            $sales_man_ids = explode(",", $this->info['sales_man_id']);
            foreach ($sales_man_ids as $sales_man_id) {
                $this->db->prepare_Stmt('
                insert into tbl_salesman_customers 
            (employee_id,customer_id) 
            VALUES (?,?)', [&$sales_man_id, &$this->info['Customer_ID']]);
            }
        }

        $this->db->prepare_Stmt('delete from tbl_user_customers 
                            where  customer_id = ? ', [&$this->info['Customer_ID']]);
        if (!empty($this->info['user_id'])) {
            $user_ids = explode(",", $this->info['user_id']);
            foreach ($user_ids as $user_id) {
                $this->db->prepare_Stmt('
                insert into tbl_user_customers 
            (user_id,customer_id) 
            VALUES (?,?)', [&$user_id, &$this->info['Customer_ID']]);
            }
        }
        $arr_cust = ['type' => 'cust', 'main_id' => $this->info['Customer_ID'], 'action' => 1, 'date_time' => $date_time,
            'user_id' => $logged_id];
        CommonFunctions::add_user_log($arr_cust);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return array_unique($error);
    }

    public function insertCustomerContacts($contacts, $customer_id) {
        foreach ($contacts as $contact) {
            $this->db->prepare_Stmt('insert into tbl_customer_supplier_contacts 
            (contact_name,contact_position,contact_phone_number,contact_note,main_id,type_id) VALUES (?,?,?,?,?,1)', [&$contact['contact_name'], &$contact['contact_position'], &$contact['contact_phone_number'], &$contact['contact_note'], &$customer_id]);
        }
    }

    public function ViewAllCustomerDetails($customer_id) {
        $data = array();
        $class_query = $this->db->query('SELECT pc.Currency_ID ,
                                                cr.Currency_Name AS Cur_Name,
                                                cr.Currency_Symbol AS Cur_Symbol,
                                                pc.Class_Name,
                                                pc.tax_amount
                                                    FROM
                                                        tbl_customers c
                                                    LEFT JOIN
                                                        tbl_pricing_class pc
                                                    ON 
                                                        c.Class_ID = pc.Class_ID
                                                    LEFT JOIN 
                                                        tbl_common_currency cr 
                                                    ON 
                                                        cr.Currency_ID = pc.Currency_ID
                                                    WHERE 
                                                        c.Customer_ID = "' . $customer_id . '"



                        ');
        $class_assoc = $this->db->assoc($class_query);
        $class_currency = $class_assoc['Currency_ID'];
        $cur_name = $class_assoc['Cur_Name'];
        $cur_symbol = $class_assoc['Cur_Symbol'];
        $class_name = $class_assoc['Class_Name'];
        //
        $query = $this->db->query('select customer_id from tbl_customer_branches 
            where customer_id = "' . $customer_id . '"');
        $row_branch = $this->db->rowsno($query);
        $has_branch = !empty($row_branch) ? 1 : 0;
        //
        $query = $this->db->query('SELECT s.*,s.Currency_ID as Customer_Currency,c.*,ct.*,cr.*,pc.*,
           ca.Account_Code,ca.Account_Name,ca.Account_ID,s.account_parent_id,coa.Account_Name as account_parent_name,
           general_class.class_name as cust_class_name,general_group.group_name,regions.region_name,
            s.Customer_CR as CR,ifnull(concat(pac.Account_Name,"-",pac.Account_Code),"") as other_pay_acc_name
            FROM tbl_customers s 
            LEFT JOIN tbl_all_cities ct ON s.Customer_City_ID = ct.ID
            LEFT JOIN tbl_all_countries c ON s.Customer_Country_ID = c.Country_ID
            LEFT JOIN tbl_common_currency cr ON cr.Currency_ID = s.Currency_ID
            LEFT JOIN tbl_pricing_class pc ON pc.Class_ID = s.Class_ID
            join tbl_chart_of_accounts ca on ca.main_id = s.Customer_ID
                 and ca.main_type="cust" and ca.sub_type = "main"
            left join tbl_chart_of_accounts coa on coa.Account_ID = s.account_parent_id
            left join tbl_general_groups general_group on general_group.group_id = s.customer_group_id
            left join tbl_general_classes general_class on general_class.class_id = s.customer_class_id
            left join tbl_regions regions using(region_id)
            left join tbl_chart_of_accounts pac on pac.Account_ID = s.other_pay_acc_id
            WHERE s.Customer_ID = "' . $customer_id . '" ');

        while ($row = $this->db->assoc($query)) {
            $row['Class_Currency'] = $class_currency;
            $row['Cur_Name'] = $cur_name;
            $row['Cur_Symbol'] = $cur_symbol;
            $row['class_name'] = $class_name;
            $row['has_branch'] = $has_branch;
            $row['branch_name'] = CommonBusiness::getBranchByBranchId($row['branch_id'])['branch_name'];
            $data[] = $row;
        }

        if (smc_session()['default_setting']['show_ret_cancel_alert_sales'] == 1) {
            $return_count_query = $this->db->query('select count(*) as total from tbl_accounting_return_invoices 
            where Person_ID = "' . $customer_id . '" ');
            $data['total_returns'] = $this->db->assoc($return_count_query)['total'];
            $return_query = $this->db->query('select Return_Invoice_Date from tbl_accounting_return_invoices 
            where Person_ID = "' . $customer_id . '"  order by Return_Invoice_Date desc limit 1');
            $data['last_return_date'] = $this->db->assoc($return_query)['Return_Invoice_Date'];

            $cancelled_count_query = $this->db->query('
            select count(*) as total 
            from tbl_invoices inv 
            inner join tbl_invoice_shipping inv_ship using (Invoice_ID_Auto)
            where inv.Customer_ID = "' . $customer_id . '" and inv_ship.shipping_status_id = 4
            ');
            $data['total_cancelled'] = $this->db->assoc($cancelled_count_query)['total'];
            $cancelled_query = $this->db->query('select inv.Invoice_Date
            from tbl_invoices inv 
            inner join tbl_invoice_shipping inv_ship using (Invoice_ID_Auto)
            where inv.Customer_ID = "' . $customer_id . '" and inv_ship.shipping_status_id = 4
            order by inv.Invoice_Date desc limit 1');
            $data['last_cancelled_date'] = $this->db->assoc($cancelled_query)['Invoice_Date'];
        }
        $data['main_shipping_address'] = '';
        $shipping_address_query = $this->db->query('
        select address.*,city.city_name 
        from tbl_customer_shipping_address address
        inner join tbl_fastpax_cities city on city.city_id = address.fastpax_city_id
        where  Customer_ID ="' . $customer_id . '" and is_default = 1 limit 1');
        $shipping_address_row = $this->db->assoc($shipping_address_query);
        if (!empty($shipping_address_row))
            $data['main_shipping_address'] = $shipping_address_row['city_name'] . ' - ' . $shipping_address_row['shipping_street'] . ' - ' . $shipping_address_row['shipping_building'] . ' - ' . $shipping_address_row['shipping_address'];
        return $data;
    }

    public function ViewByTableEntry($table, $entry_id, $entry_name, $ids) {
        $data = array();
        $query = $this->db->query("select
                                        {$entry_id}  as id,
                                        {$entry_name} as text
                                   from
                                        {$table}
                                   where 
                                        {$entry_id} IN (" . $ids . ")
        ");
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function returnBalanceByAccount($customer_id) {
        $balance = 0.0;
        $query = $this->db->query('SELECT 
                                        t.Currency_ID,
                                        t.Transaction_Amount,
                                        t.Transaction_Type,
                                        t.Current_Ex_Rate as rate
                                    FROM 
                                        tbl_accounting_transactions t
                                     JOIN 
                                        tbl_chart_of_accounts c
                                    ON 
                                        c.Account_ID = t.Account_ID
                                    WHERE  
                                        c.main_type = "cust" and c.sub_type = "main"
                                    AND 
                                        c.main_id = "' . $customer_id . '" and t.current_year_id = "' . $this->year_id . '"
                ');
        while ($row = $this->db->assoc($query)) {
            $amount = $row['rate'] == 0 ? CurrencyFactory::returnLocalCurrencyValue($row['Currency_ID'], $row['Transaction_Amount']) : ($row['rate'] * $row['Transaction_Amount']);
            if ($row['Transaction_Type'] == 'Credit')
                $amount *= (-1);
            $balance += $amount;
        }
        return $balance;
    }

    public function return_customer_all_balances($customer_id, $is_default = true) {
        $query = $this->db->query('SELECT 
                                        sum(t.local_amount * if(t.Transaction_Type="Debit",1,-1)) as amount,
                                        sum(t.Transaction_Amount*second_currency_rate * if(t.Transaction_Type="Debit",1,-1)) as second_amount,
                                        t.Currency_ID,
                                        t.is_actual
                                    FROM 
                                        tbl_accounting_transactions t
                                     JOIN 
                                        tbl_chart_of_accounts c
                                    ON 
                                        c.Account_ID = t.Account_ID
                                    WHERE  
                                        c.main_type = "cust" and c.sub_type = "main"
                                    AND c.main_id = "' . $customer_id . '" 
                                    and t.current_year_id = "' . $this->year_id . '"
                                    group by t.is_actual

                ');
        while ($row = $this->db->assoc($query)) {
            if ($is_default && smc_session()['default_currency']['Currency_ID'] != smc_session()['local_currency']) { //return in default currency else by default
                if (smc_session()['default_currency']['Currency_ID'] == smc_session()['second_currency']['id'])
                    $data[$row['is_actual']] = $row['second_amount'];
                else
                    $data[$row['is_actual']] = CurrencyFactory::returnPriceInDefaultCurrencyValue(smc_session()["local_currency"], $row['amount']);
            } else
                $data[$row['is_actual']] = $row['amount'];
        }
        $data['actual_balance'] = floatval(empty($data[1]) ? 0 : $data[1]);
        $data['off_balance'] = floatval(empty($data[0]) ? 0 : $data[0]);
        $data['balance'] = floatval($data['actual_balance'] + $data['off_balance']);
        return $data;
    }

    public function view($cutomer_id = null, $condition = "", $with_balance = 0) {
        $data = array();
        if (!is_null($cutomer_id))
            $condition .= ' AND c.Customer_ID = "' . $cutomer_id . '"';
        $query = $this->db->query('SELECT
                                            c.*,
                                            ca.Account_Code,
                                            ca.Account_ID,
                                            ct.*,
                                            cy.*,
                                            cu.*,
                                            pc.Currency_ID as Class_Currency,
                                            pc.Class_Name
                                        FROM 
                                            tbl_customers c 
                                        join tbl_chart_of_accounts ca on ca.main_id = c.Customer_ID
                                        and ca.main_type = "cust" and ca.sub_type = "main" 
                                        LEFT JOIN 
                                                tbl_all_countries cy
                                        ON 
                                                c.Customer_Country_ID = cy.Country_ID
                                        LEFT JOIN 
                                                tbl_all_cities ct
                                        ON 
                                                c.Customer_City_ID = ct.ID
                                        left join 
                                            tbl_pricing_class pc 
                                        on 
                                            pc.Class_ID = c.Class_ID
                                        LEFT JOIN 
                                                tbl_common_currency cu
                                        ON 
                                                c.Currency_ID = cu.Currency_ID
                                        WHERE 
                                            Status_ID = 1 ' . $condition . ' limit 30'
        );
        while ($row = $this->db->assoc($query)) {
            $row['Default_Currency'] = CurrencyFactory::getDefaultCurrencySymbol();
            if ($with_balance) {
                $row['Customer_Balance'] = self::returnBalanceByAccount($row['Customer_ID']);
                $row['balance'] = $row['Customer_Balance'];
            }
            //            $row[] = self::viewCustomerSumSales($row['Customer_ID']);
            $data[] = $row;
        }
        //        for ($i=0 ; $i<count($data); $i++){
        //            $total = self::viewCustomerSumSales($data[$i]['Customer_ID']);
        //            array_push($data[$i],$total);
        //        }
        return $data;
    }

    public function viewCustomerSumSales($customer_id) {
        $sum = 0;
        $condition = $this->controller_permission->hasPermission('show_actual_invoices') ? ' ' : ' and is_actual = 0';
        $query = $this->db->query('SELECT Invoice_Currency, Invoice_Net_Total FROM tbl_invoices 
            WHERE current_year_id = "' . $this->year_id . '" and Customer_ID = "' . $customer_id . '" ' . $condition);
        while ($row = $this->db->assoc($query)) {
            $sum += CurrencyFactory::returnPriceInDefaultCurrencyValue($row['Invoice_Currency'], $row['Invoice_Net_Total']);
        }
        return $sum;
    }

    //    public function viewCustomerSumSales($customer_id) {
    //        $sum_sales = 0;
    //        $query = $this->db->query('SELECT sum(Invoice_Net_Total*current_rate) as total
    //            FROM tbl_invoices  WHERE Customer_ID = "' . $customer_id . '"');
    //        $row = $this->db->assoc($query);
    //        if(!empty($row))
    //            $sum_sales = $row['total'];
    //        return $sum_sales;
    //    }

    public function viewCustomerInv($cutomer_id) {
        $data = array();
        $query = $this->db->query('SELECT 
                                            c.Customer_Name,
                                            i.*
                                        FROM 
                                            tbl_customers c 
                                        RIGHT JOIN 
                                            tbl_invoices i
                                        ON 
                                            c.Customer_ID = i.Customer_ID
                                        WHERE 
                                            c.Customer_ID = "' . $cutomer_id . '"');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function viewCities() {
        $data = array();
        $query = $this->db->query('SELECT * FROM tbl_all_cities');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function viewCountries() {
        $data = array();
        $query = $this->db->query('SELECT * FROM tbl_common_countries');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function deleteCustomer($customer_id) {
        $check = self::checkIfCustomerUsed($customer_id);
        $mydate = date("Y-m-d H:i:s");
        if ($check) {
            return ['check_customer'];
        }
        $this->db->query('START TRANSACTION');
        $delete = [&$customer_id];
        $this->db->prepare_Stmt("DELETE FROM tbl_customers WHERE Customer_ID = ? ", $delete);
        $this->db->prepare_Stmt("DELETE FROM tbl_customer_shipping_address WHERE Customer_ID = ? ", $delete);
        $this->db->prepare_Stmt("DELETE FROM tbl_chart_of_accounts 
                where main_type = 'cust' and main_id = ?", $delete);
        $arr_cust = ['type' => 'cust', 'main_id' => $customer_id,
            'action' => 2, 'date_time' => $mydate,
            'user_id' => smc_session()['user_logged_id']];
        CommonFunctions::add_user_log($arr_cust);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    /**
     * Generic function to get an uploaded $_FILE[name_of_file] and save it to a specific path 
     * @param [[Type]] $FileName The name of the input[file] tag that is assigned in the client-side
     * @param [[Type]] $SavePath Destination path
     */
    public function SaveUploadedPhoto($FileName, $SavePath) {
        if (!isset($_FILES[$FileName]))
            return false;
        $SaveToPath = substr($SavePath, 0, strrpos($SavePath, "/") + 1);
        $SaveAsFile = substr($SavePath, strrpos($SavePath, "/") + 1);
        $SaveAsExtention = explode(".", $SaveAsFile);
        $SaveAsExtention = strtolower($SaveAsExtention[count($SaveAsExtention) - 1]);
        $SaveAsFile = substr($SaveAsFile, 0, strrpos($SaveAsFile, "."));
        $fileType = explode(".", basename($_FILES[$FileName]['name']));
        $fileType = strtolower($fileType[count($fileType) - 1]);
        $DestinationFile = $SaveToPath . $SaveAsFile . ".";
        if (file_exists($DestinationFile . $fileType))
            unlink($DestinationFile . $fileType);
        move_uploaded_file($_FILES[$FileName]['tmp_name'], $DestinationFile . $fileType);

        if ($fileType != "jpg" && $SaveAsExtention == "jpg") {
            //            if(file_exists($DestinationFile.$fileType))
            //                unlink($DestinationFile.$fileType);
            switch ($fileType) {
                case 'gif' :
                    $image = imageCreateFromGif($DestinationFile . $fileType);
                    break;
                case 'jpeg':
                    $image = imageCreateFromJpeg($DestinationFile . $fileType);
                    break;
                case 'png':
                    $image = imagecreatefrompng($DestinationFile . $fileType);
                    break;
            }
            imagejpeg($image, $DestinationFile . $SaveAsExtention, 100);
            imagedestroy($image);
            if (file_exists($DestinationFile . $fileType))
                unlink($DestinationFile . $fileType);
        }
    }

    public function customerSearch($term = null, $page, $limit) {
        $data = array();
        
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if($this->controller_permission->hasPermission('Branches')){
            $cond  .= '';
        }else{
            $cond  .= ' and (branch_id ="' . (smc_session()['user_accounts']['branch_id']) . '" or customer_id = 0   )  '  ;
        }

        if (!is_null($term)) {
            $cond .= ' and (Customer_Name LIKE ? or Customer_Mobile LIKE ? )';
            $data_filter[] = "%" . $term . "%";
            $data_filter[] = "%" . $term . "%";
        }
        if (!empty($this->info['Status_ID'])) {
            $cond .= ' and Status_ID = "1" ';
        }
        if (!empty($this->info['group'])) {
            $cond .= ' and customer_group_id = "' . $this->info['group'] . '" ';
        }
        if (isset($this->info['is_edited'])) {
            $cond .= ' and (Status_ID = 1 or (Status_ID = 0 and Customer_ID = ?) )';
            $data_filter[] = $this->info['is_edited'];
        }
        $cust_emp_arr = array();
        if (!empty($this->info['auto_salesman'])) {
            $query = $this->db->query('select 
                                    e.Employee_ID as id,uc.customer_id,
                                    concat_ws("",e.Employee_FirstName," ",e.Employee_MiddleName," ",e.Employee_LastName) as text
                                from tbl_hrm_employee e  join tbl_salesman_customers uc on uc.employee_id = e.Employee_ID 
                                ');
            while ($row = $this->db->assoc($query)) {
                //                    $cust_emp_arr[$row['customer_id']]['customer_count'].'===';
                $cust_emp_arr[$row['customer_id']]['id'] = $row['id'];
                $cust_emp_arr[$row['customer_id']]['text'] = $row['text'];
                if (empty($cust_emp_arr[$row['customer_id']]['customer_count']))
                    $cust_emp_arr[$row['customer_id']]['customer_count'] = 1;
                else
                    $cust_emp_arr[$row['customer_id']]['customer_count'] ++;

                //                $data['data'][0]['emp_id'] = $row_emp['id'];
                //                $data['data'][0]['emp_name'] = $row_emp['text'];
                //                $data['data'][0]['selected_emp'] = 1;
            }
        }
        if (!empty($this->info['is_customer_group']) && !empty(smc_session()['customer_group_ids'])) {
            $cond .= ' and customer_group_id in (' . smc_session()['customer_group_ids'] . ')';
        }
         
        $Employee_ID  =  smc_session()['user_details']['Employee_ID'];
        if (smc_session()['user_details']['limit_customer_invoice'] == 1) {
            $query = $this->db->prepare_Stmt('select 
            tbl_customers.Customer_ID as id,
            concat_ws(" - ",Customer_Name,Customer_Mobile) as text,
            0 as selected_emp,
            Customer_Name as cust_name
        from 
            tbl_customers 
            LEFT JOIN tbl_salesman_customers sales ON sales.customer_id = tbl_customers.Customer_ID 
            where 1=1  and sales.employee_id ='.$Employee_ID .'
        ' . $cond . '
        limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        }else{
            $query = $this->db->prepare_Stmt('select 
                Customer_ID as id,
                concat_ws(" - ",Customer_Name,Customer_Mobile) as text,
                0 as selected_emp,
                Customer_Name as cust_name
            from 
                tbl_customers 
                where 1=1 
            ' . $cond . '
            limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        }
         
        while ($row = $this->db->assoc($query)) {
            if (isset($cust_emp_arr[$row['id']]) && $cust_emp_arr[$row['id']]['customer_count'] == 1) {
                $row['emp_name'] = $cust_emp_arr[$row['id']]['text'];
                $row['emp_id'] = $cust_emp_arr[$row['id']]['id'];
                $row['selected_emp'] = 1;
            }
            $data['data'][] = $row;
        }

        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function searchShippingAddress($term = null, $page, $limit) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!empty($this->info['api_folder'])) {
            $cond .= $this->info['api_folder'] == 'fastpax' ? ' and fastpax_city_id is not null  and fastpax_city_id <> 0' : '';
        }
        if (!is_null($term)) {
            $cond .= ' and shipping_title LIKE ? ';
            $data_filter[] = "%" . $term . "%";
        }
        $query = $this->db->prepare_Stmt('select 
                                    shipping_address_id as id,
                                    shipping_title as text
                                from 
                                    tbl_customer_shipping_address
                                where  Customer_ID = "' . $this->info['customer_id'] . '"
                                ' . $cond . '
                                limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function poscustomerSearch($term = null, $page, $limit) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' where (Customer_Name LIKE ? or customer_phone LIKE ? )';
            $data_filter[] = "%" . $term . "%";
            $data_filter[] = "%" . $term . "%";
        }
        $query = $this->db->prepare_Stmt('select 
                                    customer_id as id,
                                    concat_ws(" - ",customer_name,customer_phone) as text
                                from 
                                    tbl_pos_customers
                                ' . $cond . '
                                limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public static function returnCustomerVATAccount($cust_id) {
        $db = DBConnectorFactory::loadDriver();
        $query = $db->query('SELECT Account_ID,Account_Code FROM tbl_chart_of_accounts
                            WHERE main_id = "' . $cust_id . '" and main_type="cust"
                            AND sub_type="vat"
                ');
        $row = $db->assoc($query);
        return $row;
    }

    public function checkIfCustomerUsed($cid) {
        $account_arr = array();
        $query_account = $this->db->query('select a.Account_ID,a.Account_Code 
                from tbl_chart_of_accounts a 
                where a.main_type = "cust" and a.main_id = "' . $cid . '"');
        while ($c_account_assoc = $this->db->assoc($query_account))
            $account_arr[] = $c_account_assoc['Account_ID'];

        //check if if customer is used in transactions
        $trans_query = $this->db->query('select * from tbl_accounting_transactions 
            where Account_ID in(' . implode(',', $account_arr) . ') limit 1');
        $trans_count = $this->db->rowsno($trans_query);
        if ($trans_count > 0) {
            return true;
        }
        //check if if customer is used in invoices
        $inv_query = $this->db->query('select * from tbl_invoices where Customer_ID = "' . $cid . '" limit 1');
        $inv_count = $this->db->rowsno($inv_query);
        if ($inv_count > 0) {
            return true;
        }

        //check if if customer is used in return
        $return_query = $this->db->query('select * from tbl_accounting_return_invoices where Person_ID = "' . $cid . '" and Is_Customer_Invoice = 1 limit 1');
        $return_count = $this->db->rowsno($return_query);
        if ($return_count > 0) {
            return true;
        }

        //check if if customer is used in sales order
        $sorder_query = $this->db->query('select * from tbl_purchase_order where Supplier_ID = "' . $cid . '" and is_sales = 1 limit 1');
        $sorder_count = $this->db->rowsno($sorder_query);
        if ($sorder_count > 0) {
            return true;
        }
        //check if if customer is used in service invoice
        $service_query = $this->db->query('select * from tbl_inventory_services_invoices where Customer_ID = "' . $cid . '" limit 1');
        $service_count = $this->db->rowsno($service_query);
        if ($service_count > 0) {
            return true;
        }

        return false;
    }

    public static function getCustomerByID($id, $with_balance = 0, $is_actual = 0) {
        $db = DBConnectorFactory::loadDriver();
        $cust_emp_arr = array();

        $query = $db->query('select 
                                e.Employee_ID as id,uc.customer_id,
                                concat_ws("",e.Employee_FirstName," ",e.Employee_MiddleName," ",e.Employee_LastName) as text
                                from tbl_hrm_employee e  join tbl_salesman_customers uc 
                                on uc.employee_id = e.Employee_ID  where uc.customer_id = "' . $id . '"
                                ');
        while ($row = $db->assoc($query)) {
            $cust_emp_arr[$row['customer_id']]['id'] = $row['id'];
            $cust_emp_arr[$row['customer_id']]['text'] = $row['text'];
            if (empty($cust_emp_arr[$row['customer_id']]['customer_count']))
                $cust_emp_arr[$row['customer_id']]['customer_count'] = 1;
            else
                $cust_emp_arr[$row['customer_id']]['customer_count'] ++;
        }
        $query_customer = $db->query('SELECT 
                                          s.Customer_Company AS Company,
                                          s.Customer_ID AS ID,
                                          s.customer_vat AS person_vat,
                                          s.Customer_CR AS person_cr,

                                          s.customer_note AS customer_note,

                                          s.Customer_Name AS Name,
                                          s.Customer_Name,
                                          s.Customer_Email AS Email,
                                          s.customer_vat AS customer_vat,
                                          s.customer_additional_number AS customer_additional_number,
                                          s.customer_postal_code AS customer_postal_code,
                                          s.customer_other_id AS customer_other_id,
                                          ifnull(s.Customer_Phone,"") AS Phone,
                                          s.Customer_Mobile AS Mobile,
                                          s.Customer_Address AS Address,
                                          s.Customer_CR as CR,
                                          ifnull(c.Country_Name,"") as Country_Name,
                                          ca.Account_Code,
                                          ca.Account_ID,
                                          s.Class_ID,
                                          pc.Class_Name,
                                          pc.Currency_ID AS Class_Currency,
                                          pc.tax_amount,
                                          0 as customer_is_pos,
                                          0 as customer_pos_id,
                                          ci.Name as customer_city_name,s.other_pay_acc_id,
                                          s.customer_due_date_period as due_date_period 
                                        FROM 
                                            tbl_customers s 
                                        LEFT JOIN
                                            tbl_all_countries c
                                        ON 
                                            s.Customer_Country_ID = c.Country_ID
                                        left join 
                                            tbl_all_cities ci on ci.ID = s.Customer_City_ID
                                        inner JOIN
                                            tbl_pricing_class pc
                                        ON
                                            pc.Class_ID = s.Class_ID
                                        LEFT JOIN
                                            tbl_common_currency cr
                                        ON 
                                            cr.Currency_ID = pc.Currency_ID
                                        join 
                                            tbl_chart_of_accounts ca on ca.main_id = s.Customer_ID

                                        AND 
                                            ca.main_type = "cust" and ca.sub_type = "main"
                                        WHERE 
                                            s.Customer_ID = "' . $id . '"
                                 ');
        $data = $db->assoc($query_customer);
        if (isset($cust_emp_arr[$data['ID']]) && $cust_emp_arr[$data['ID']]['customer_count'] == 1) {
            $data['emp_name'] = $cust_emp_arr[$data['ID']]['text'];
            $data['emp_id'] = $cust_emp_arr[$data['ID']]['id'];
            $data['selected_emp'] = 1;
        }
        if ($with_balance <> 0) {
            $today = Date::getToday();
            $report = new Report;
            $data['Customer_Balance'] = $report->returnCustomerExactBalance($id, smc_session()["local_currency"], smc_session()['start_date'], $today, 0, $is_actual)['Balance'];
        }
        $data['main_shipping_address'] = '';
        $shipping_address_query = $db->query('
        select address.*,city.city_name 
        from tbl_customer_shipping_address address
        inner join tbl_fastpax_cities city on city.city_id = address.fastpax_city_id
        where  Customer_ID ="' . $id . '" and is_default = 1 limit 1');
        $shipping_address_row = $db->assoc($shipping_address_query);
        if (!empty($shipping_address_row))
            $data['main_shipping_address'] = $shipping_address_row['city_name'] . ' - ' . $shipping_address_row['shipping_street'] . ' - ' . $shipping_address_row['shipping_building'] . ' - ' . $shipping_address_row['shipping_address'];
        //        echo"<pre>";die(print_r($data));
        return $data;
    }

    public function ReturnClassCurrency($class_id) {
        $query = $this->db->query('select c.*,pc.tax_amount,pc.is_ttc from tbl_pricing_class pc join tbl_common_currency c
            on c.Currency_ID = pc.Currency_ID where pc.Class_ID = "' . $class_id . '"');
        $data = $this->db->assoc($query);
        return $data;
    }

    public function updateInvoiceShippingAddress() {
        $this->db->prepare_Stmt('update tbl_invoices set address_id = ?  where Invoice_ID_Auto = ?', [&$this->info['address_id'], &$this->info['Invoice_ID_Auto']]);
        $error = $this->db->error();
        return $error;
    }

    public function addCustomerPOS() {
        $logged_id = SESSION::getUserID();

        if($this->controller_permission->hasPermission('Branches')){
            $branch_id  = !empty($this->info['search_branch_id_customer_pos']) ? $this->info['search_branch_id_customer_pos'] : smc_session()['user_accounts']['branch_id'];
        }else{
            $branch_id  = smc_session()['user_accounts']['branch_id'] ;
        }
        
        
        $allergy = !empty($this->info['allergy_id']) ? $this->info['allergy_id'] : '';
        $intolerance = !empty($this->info['intolerance_id']) ? $this->info['intolerance_id'] : '';
        $online_customer_id = !empty($this->info['online_customer_id']) ? $this->info['online_customer_id'] : null;
        $online_region_id = !empty($this->info['pos_cust_region_id']) ? $this->info['pos_cust_region_id'] : 0;
        $is_main_transfer = !empty($this->info['is_main_transfer']) ? $this->info['is_main_transfer'] : 0;
        $data = [
            &$this->info['customer_name'],
            &$this->info['customer_address'],
            &$this->info['customer_phone'],
            &$this->info['customer_dob'],
            &$online_region_id,
            &$online_customer_id,
            &$allergy,
            &$intolerance,
            &$logged_id, &$is_main_transfer, &$branch_id
        ];
        $this->db->prepare_Stmt("
                                INSERT
                                INTO
                                    tbl_pos_customers
                                (
                                " . (!empty($this->info['fill_auto_id']) ? 'customer_id,' : '') . "
                                    customer_name,
                                    customer_address,
                                    customer_phone,
                                    customer_dob,region_id,
                                    online_customer_id,
                                    allergy_id,
                                    intolerance_id,
                                    Logged_ID,is_main_transfer,
                                    branch_id
                                    
                                )
                                    VALUES
                                (
                                    " . (!empty($this->info['fill_auto_id']) ? intval($this->info['fill_auto_id']) . ',' : '') . "?,?,?,?,?,?,?,?,?,?,?
                                )
        ", $data);
        $id = $this->db->id();
        $error = $this->db->error();
        if (empty($error)) {
            return $id;
        } else {
            return array_unique($error);
        }
    }

    public function getCustomerForAutoComplete($term= null, $is_exact = false, $customer_id = null, $customer_code = null) {
        //get cash account code & id [needed for pos customer info]
        $class_id = smc_session()['default_setting']['pricing_class_id'];
        //$search_pos_customer (0:Account Customers,1:Road Customers:2:both)
        $search_pos_customer = smc_session()['default_setting']['search_pos_customer'];
        $allergy_array = array();
        $intolerance_array = array();
        $srch_cond = $is_exact ? ' = "' . smc_escape($term) . '"' : 'like "%' . smc_escape($term) . '%"';
        $data = array();
        $allergy_query = $this->db->query('select * from tbl_allergy');
        while ($row = $this->db->assoc($allergy_query)) {
            $allergy_array[$row['allergy_id']] = $row['allergy_name'];
        }
        $intolerance_query = $this->db->query('select * from tbl_intolerance');
        while ($row = $this->db->assoc($intolerance_query)) {
            $intolerance_array[$row['intolerance_id']] = $row['intolerance_name'];
        }
        $cond = ' ';
        if (!empty($term))
            $cond .= ' and (c.Customer_Name ' . $srch_cond . ' or c.Customer_Mobile ' . $srch_cond . ') ';
        if(!empty($customer_id) && !empty($customer_code)){
            $cond .= ' and Customer_ID = "' . $customer_id . '" or Customer_Code = "' . $customer_code . '" ';
            $search_pos_customer = 0;
        }else{
            if (!empty($customer_id)) {
                $cond .= ' and Customer_ID = "' . $customer_id . '" ';
                $search_pos_customer = 0;
            }
            if (!empty($customer_code)) {
                $cond .= ' and Customer_Code = "' . $customer_code . '" ';
                $search_pos_customer = 0;
            }
        }

        if($this->controller_permission->hasPermission('Branches')){
            $cond .=   '';
            $pos_cond = '';
        }else{
            $cond .= ' and c.branch_id = "' . (smc_session()['user_accounts']['branch_id']) . '" || c.Customer_ID = 0 '  ;
            $pos_cond =  ' and c.branch_id = "' . (smc_session()['user_accounts']['branch_id']) . '" || c.Customer_ID = 1217'  ;

        }
        


        $cond2 ='';
        if(!$this->controller_permission->hasPermission('show_all_sales_and_customers')){
            $Employee_ID =  smc_session()['user_details']['Employee_ID'];
            $cond2  = ' and su.employee_id = "' . $Employee_ID . '" ';
        }
        //        die($cond);
        $pos_customers = ' select 
                        c.customer_name as Customer_Name,
                        c.customer_address as Customer_Address,
                        0 as Customer_ID,
                        c.customer_phone as Customer_Mobile,
                        c.customer_id as pos_customer_id,
                        "' . $class_id . '" as Class_ID,
                        0 as Customer_Discount,
                        1 as is_pos_customer,
                        allergy_id as allergy,
                        intolerance_id as intolerance,
                         accounts.Account_ID  as account_id
                    from tbl_pos_customers c 
                    inner join tbl_chart_of_accounts accounts on accounts.main_id = 0 and accounts.main_type="cust" and accounts.sub_type = "main"
                    where (c.customer_name  ' . $srch_cond . '  or c.customer_phone  ' . $srch_cond . ') '.$pos_cond.'   ';
            
            
        $Employee_ID =  smc_session()['user_details']['Employee_ID'];

        if (smc_session()['user_details']['limit_customer_invoice'] == 1) {
            $account_customers = 'SELECT
                        c.Customer_Name,
                        c.branch_id,
                        c.Customer_Address,
                        c.Customer_ID,
                        c.Customer_Mobile,
                        0 as pos_customer_id,
                        c.Class_ID,
                        c.Customer_Discount,
                        0 as is_pos_customer,
                        allergy_id as allergy,
                        intolerance_id as intolerance,
                        accounts.Account_ID  as account_id
                        FROM tbl_customers c 
                        inner join tbl_chart_of_accounts accounts on accounts.main_id = c.Customer_ID and accounts.main_type="cust" and accounts.sub_type = "main"
                        LEFT JOIN tbl_salesman_customers sales ON sales.customer_id = c.Customer_ID 
                        WHERE c.Status_ID = 1  and sales.employee_id ="' . $Employee_ID . '" ' . $cond;
        } else {
            $account_customers = 'SELECT
                        c.Customer_Name,
                        c.Customer_Address,
                        c.Customer_ID,
                        c.Customer_Mobile,
                        0 as pos_customer_id,
                        c.Class_ID,
                        c.Customer_Discount,
                        0 as is_pos_customer,
                        allergy_id as allergy,
                        intolerance_id as intolerance,
                        accounts.Account_ID  as account_id
                    FROM tbl_customers c 
                    inner join tbl_chart_of_accounts accounts on accounts.main_id = c.Customer_ID and accounts.main_type="cust" and accounts.sub_type = "main"
                    WHERE c.Status_ID = 1
                        ' . $cond;
        }
                    
        if ($search_pos_customer == 0) {
            $query = $this->db->query($account_customers . ' order by Customer_Name asc limit 30');
        } else if ($search_pos_customer == 1) {
            $query = $this->db->query($pos_customers . ' order by Customer_Name asc limit 30');
        } else {
            $query = $this->db->query('
                (' . $account_customers . '
                ) union (
                   ' . $pos_customers . '
                ) order by Customer_Name asc limit 30');
        }

        //die(print_r($query));

        while ($row = $this->db->assoc($query)) {
            if (!empty($row['allergy'])) {
                $allergy_arr = explode(",", $row['allergy']);
                $row['allergy'] = '';
                foreach ($allergy_arr as $allergy)
                    $row['allergy'] .= $allergy_array[$allergy] . ',';
                $row['allergy'] = substr($row['allergy'], 0, -1);
            }
            if (!empty($row['intolerance'])) {
                $intolerance_arr = explode(",", $row['intolerance']);
                $row['intolerance'] = '';
                foreach ($intolerance_arr as $intolerance)
                    $row['intolerance'] .= $intolerance_array[$intolerance] . ',';
                $row['intolerance'] = substr($row['intolerance'], 0, -1);
            }
            $data[] = $row;
        }
        return $data;
    }

    public function searchRoadCustomer() {
        //get cash account code & id [needed for pos customer info]
        $class_id = smc_session()['default_setting']['pricing_class_id'];
        $allergy_array = array();
        $intolerance_array = array();
        $allergy_query = $this->db->query('select * from tbl_allergy');
        while ($row = $this->db->assoc($allergy_query))
            $allergy_array[$row['allergy_id']] = $row['allergy_name'];
        $intolerance_query = $this->db->query('select * from tbl_intolerance');
        while ($row = $this->db->assoc($intolerance_query))
            $intolerance_array[$row['intolerance_id']] = $row['intolerance_name'];
        $query = $this->db->query('
                    select 
                        c.customer_name as Customer_Name,
                        c.customer_address as Customer_Address,
                        0 as Customer_ID,
                        c.customer_phone as Customer_Mobile,
                        c.customer_id as pos_customer_id,
                        "' . $class_id . '" as Class_ID,
                        0 as Customer_Discount,
                        1 as is_pos_customer,
                        allergy_id as allergy,
                        intolerance_id as intolerance
                    from tbl_pos_customers c where c.customer_id = "' . $this->info['customer_id'] . '"
               ');
        $row = $this->db->assoc($query);
        if (!empty($row['allergy'])) {
            $allergy_arr = explode(",", $row['allergy']);
            $row['allergy'] = '';
            foreach ($allergy_arr as $allergy)
                $row['allergy'] .= $allergy_array[$allergy] . ',';
            $row['allergy'] = substr($row['allergy'], 0, -1);
        }
        if (!empty($row['intolerance'])) {
            $intolerance_arr = explode(",", $row['intolerance']);
            $row['intolerance'] = '';
            foreach ($intolerance_arr as $intolerance)
                $row['intolerance'] .= $intolerance_array[$intolerance] . ',';
            $row['intolerance'] = substr($row['intolerance'], 0, -1);
        }
        return ['item' => $row];
    }

    public function getPOSCustomers($cond = null, $limit = null) {
        $data = [];
        $query = $this->db->query('SELECT * FROM tbl_pos_customers where 1=0  ' . $cond . ' ' . (empty($limit) ? '' : ' limit ' . $limit));
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function updateCustomerPOS() {
        $allergy = !empty($this->info['allergy_id']) ? $this->info['allergy_id'] : '';
        $intolerance = !empty($this->info['intolerance_id']) ? $this->info['intolerance_id'] : '';
        $is_main_transfer = !empty($this->info['online_customer_id']) ? 1 : 0;
        $online_region_id = !empty($this->info['pos_cust_region_id']) ? $this->info['pos_cust_region_id'] : 0;
        if($this->controller_permission->hasPermission('Branches')){
            $branch_id  = !empty($this->info['search_branch_id_customer_pos']) ? $this->info['search_branch_id_customer_pos'] : smc_session()['user_accounts']['branch_id'];
        }else{
            $branch_id  = smc_session()['user_accounts']['branch_id'] ;
        }

        $data = [
            &$this->info['customer_name'],
            &$this->info['customer_address'],
            &$this->info['customer_phone'],
            &$this->info['customer_dob'],
            &$online_region_id,
            &$allergy,
            &$intolerance, &$is_main_transfer,&$branch_id,
            &$this->info['customer_id']
        ];
        $this->db->prepare_Stmt("update
                                    tbl_pos_customers
                                set
                                    customer_name=?,
                                    customer_address=?,
                                    customer_phone=?,
                                    customer_dob=?,region_id=?,
                                    allergy_id=?,
                                    intolerance_id=?,
                                    is_main_transfer=?,
                                    branch_id=?
                                    where customer_id = ?
        ", $data);
        //if we are on server and we edit customer we must update them to sync zero not delete them, so we put sync 0 to send it to branches
        if (smc_session()['default_setting']['sync_type'] == 1)
            $this->db->prepare_Stmt("update tbl_pos_customers_sync set is_sync = 0 where online_customer_id = ? ", [&$this->info['customer_id']]);
        //
        $error = $this->db->error();
        return $error;
    }

    public function viewLastCaller() {
        $data = array();
        $query = $this->db->query('SELECT caller_phone_number FROM tbl_pos_caller_id order by caller_time desc limit 1');
        $row = $this->db->assoc($query);
        if (empty($row))
            $data = array('item' => array('caller_phone_number' => ''));
        else {
            $temp_data = $this->getCustomerForAutoComplete($row['caller_phone_number'], true);
            if (!empty($temp_data[0]))
                $data['item'] = $temp_data[0];
            $data['item']['caller_phone_number'] = $row['caller_phone_number'];
        }
        return $data;
    }

    public static function getPOSCustomerByID($id, $address_id = null) {
        $db = DBConnectorFactory::loadDriver();
        $cond = ' ';
        if (!empty($address_id))
            $cond .= ' and a.address_id = "' . $address_id . '"';
        $query_customer = $db->query('SELECT 

                                          s.customer_name AS Name,
                                          s.customer_name AS Customer_Name,
                                          s.customer_phone AS Phone,
                                          if(isnull(a.address_id),s.customer_address,a.address_name) AS Address,
                                          1 as customer_is_pos,
                                          s.customer_id as customer_pos_id,s.customer_dob,s.allergy_id,s.intolerance_id,
                                          accounts.Account_ID as account_id
                                        FROM 
                                            tbl_pos_customers s 
                                        left join tbl_customer_address a 
                                        on a.customer_id = s.customer_id
                                        inner join tbl_chart_of_accounts accounts on accounts.main_id = 0 and accounts.main_type="cust" and accounts.sub_type = "main"
                                        WHERE 
                                            s.customer_id = "' . $id . '" 
                                 ' . $cond);
        $data = $db->assoc($query_customer);
        return $data;
    }

    public function insert_customer_details() {
        //        echo"<pre>";
        //        die(print_r($this->info));
        // check that id not exist
        $this->db->query('START TRANSACTION');

        $query_cust_header = ' ';
        $query_acc_header = ' ';
        $cust_param = '';
        $acc_param = '';
        if (!empty($this->info['data']['customer'])) {
            //check if customer id already exist
            $exist_customers = "";
            $customers = $this->db->query('select Customer_ID from tbl_customers 
                where Customer_ID in (' . implode(',', array_keys($this->info['data']['customer'])) . ')');
            while ($row_assoc = $this->db->assoc($customers)) {
                //                if (isset($this->info['data']['customer'][$row_assoc['Customer_ID']]))
                //                    unset($this->info['data']['customer'][$row_assoc['Customer_ID']]);
                if (isset($this->info['data']['customer'][$row_assoc['Customer_ID']]))
                    $exist_customers .= (empty($exist_customers) ? "" : ",") . $row_assoc['Customer_ID'];
            }
            //if customers already exist then delete them to re-insert again
            if (!empty($exist_customers)) {
                $this->db->query("SET FOREIGN_KEY_CHECKS=0");
                $this->db->query("delete from tbl_customers where Customer_ID in ({$exist_customers}) ");
                $this->db->query("delete from tbl_chart_of_accounts where main_type = 'cust' and main_id in ({$exist_customers}) ");
                $this->db->query("delete from tbl_customer_supplier_contacts where type_id = 1 and main_id in ({$exist_customers}) ");
                $this->db->query("SET FOREIGN_KEY_CHECKS=1");
            }
            //disable trigger to do not automatic insert account, so we insert them to chart account manual below
            $this->db->query('set @disable_customer_trigger=1;');
            foreach ($this->info['data']['customer'] as $cust_id => $cust_row) {
                $data_cust = array();
                foreach ($cust_row as $cust_col => &$cust) {
                    if (empty($cust_param))
                        $query_cust_header .= $cust_col . ',';
                    $data_cust[] = &$cust;
                }
                if (empty($cust_param)) {
                    $query_cust_header = rtrim($query_cust_header, ',');
                    $cust_param = rtrim(str_repeat("?,", count($cust_row)), ',');
                }
                $this->db->prepare_Stmt('insert into tbl_customers (' . $query_cust_header . ') 
                    values( ' . $cust_param . ' )', $data_cust);
                //accounts
                foreach ($this->info['data']['accounts'][$cust_id] as $acc_row) {
                    $data_accounts = array();
                    foreach ($acc_row as $acc_col => &$acc) {
                        if (empty($acc_param))
                            $query_acc_header .= $acc_col . ',';
                        $data_accounts[] = &$acc;
                    }
                    if (empty($acc_param)) {
                        $query_acc_header = rtrim($query_acc_header, ',');
                        $acc_param = rtrim(str_repeat("?,", count($acc_row)), ',');
                    }
                    $this->db->prepare_Stmt('insert into tbl_chart_of_accounts (' . $query_acc_header . ') 
                        values( ' . $acc_param . ' )', $data_accounts);
                }
                //
                //accounts
                foreach ($this->info['data']['contacts'][$cust_id] as $cont_row) {
                    $data_contacts = array();
                    foreach ($cont_row as $cont_col => &$acc) {
                        if (empty($acc_param))
                            $query_cont_header .= $cont_col . ',';
                        $data_contacts[] = &$acc;
                    }
                    if (empty($acc_param)) {
                        $query_cont_header = rtrim($query_cont_header, ',');
                        $acc_param = rtrim(str_repeat("?,", count($cont_row)), ',');
                    }
                    $this->db->prepare_Stmt('insert into tbl_customer_supplier_contacts (' . $query_cont_header . ') 
                        values( ' . $acc_param . ' )', $data_contacts);
                }
                //
            }
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    public function updateCustomerPassword() {
        $pass = md5(sha1(md5($this->info['current_customer_password'])));
        $this->db->prepare_Stmt("
                                UPDATE
                                    tbl_customers
                                SET
                                    password = ?
                                WHERE
                                    Customer_ID = ?
        ", [&$pass, &$this->info['current_customer_id']]);
        $error = $this->db->error();
        return $error;
    }

    public function get_customer_visit_by_id($id) {
        $data = array();
        $query = $this->db->query('select v.time as user_track_time,0 as user_track_speed,
            v.longitude as user_track_lng,v.latitude as user_track_lat,v.app_user_id,v.app_user_id as user_track_id ,
            c.Customer_ID
            from tbl_customer_visit v join tbl_customers c on c.Customer_ID = v.customer_id where v.visit_id = "' . $id . '"');
        $row = $this->db->assoc($query);
        if (!empty($row))
            $data[] = $row;
        return $data;
    }

    public function submitAllergyIntolerance() {
        $type_id = $this->info['type_id'];
        if ($type_id == 1) {
            $this->db->prepare_Stmt("
                                insert into 
                                    tbl_allergy
                                    (allergy_name) Values (?)
        ", [&$this->info['title']]);
            $this->db->prepare_Stmt("
                                insert into 
                                    tbl_intolerance
                                    (intolerance_name) Values (?)
        ", [&$this->info['title']]);
        }

        $error = $this->db->error();
        return $error;
    }

    public function get_road_customer_data($id) {
        $query_customer = $this->db->query('SELECT 
                                          s.branch_id,
                                          s.customer_name,
                                          s.customer_phone,
                                          if(isnull(a.address_id),s.customer_address,a.address_name) AS Address,
                                          1 as customer_is_pos,
                                          s.customer_id as customer_pos_id,
                                          s.customer_dob,
                                          s.allergy_id,
                                          s.intolerance_id,s.region_id,ifnull(r.region_name,"") as region_name
                                        FROM 
                                            tbl_pos_customers s 
                                        left join tbl_customer_address a 
                                        on a.customer_id = s.customer_id
                                        left join tbl_regions r on r.region_id = s.region_id

                                        WHERE 
                                            s.customer_id = "' . $id . '" 
                                 ');
        $data = $this->db->assoc($query_customer);
        if (!empty($data['allergy_id'])) {
            $query_allergy = $this->db->query('select allergy_id as id,allergy_name as text from tbl_allergy where allergy_id in (' . $data['allergy_id'] . ')');
            $data['allergy_id'] = array();
            while ($row = $this->db->assoc($query_allergy)) {
                $data['allergy_id'][] = $row;
            }
        }
        if (!empty($data['intolerance_id'])) {
            $query_int = $this->db->query('select intolerance_id as id,intolerance_name as text from tbl_intolerance where intolerance_id in (' . $data['intolerance_id'] . ')');
            $data['intolerance_id'] = array();
            while ($row = $this->db->assoc($query_int)) {
                $data['intolerance_id'][] = $row;
            }
        }
        if (!empty($data['branch_id'])) {
            $data['branch_name'] = CommonBusiness::getBranchByBranchId($data['branch_id'])['branch_name'];
        }else{
            $data['branch_id'] = '';
        }
        return $data;
    }

    public static function getCustomerAccount($customer_id) {
        $db = DBConnectorFactory::loadDriver();
        $query = $db->query('SELECT Account_ID,Account_Code,Account_Name
                                FROM tbl_chart_of_accounts 
                                WHERE main_id = "' . $customer_id . '"
                                AND main_type = "cust" and sub_type = "main"	');


        $row = $db->assoc($query);
        return $row;
    }

    public static function get_customer_bill_acc($customer_id) {
        $db = DBConnectorFactory::loadDriver();
        $query = $db->query('select Account_ID from tbl_chart_of_accounts
            where main_id = "' . $customer_id . '" and main_type="cust" and sub_type="bill"');
        $account_id = $db->assoc($query)['Account_ID'];
        if (!empty($account_id))
            return $account_id;
        //
        $query_cust = $db->query('select Customer_Name,Currency_ID from tbl_customers where Customer_ID ="' . $customer_id . '"');
        $row = $db->assoc($query_cust);
        $account_code = CommonACC::generate_account(smc_session()['account_setting']['customer_bill_acc_id'], 1);
        //
        $data = [&$row['Customer_Name'],
            &smc_session()['account_setting']['customer_bill_acc_id'],
            &$account_code,
            &$row['Currency_ID'],
            &$customer_id];
        $db->prepare_Stmt('INSERT INTO
            tbl_chart_of_accounts (Account_Name,Parent_ID,Account_Code,Currency_ID,
            main_type,main_id,sub_type,IsAdded)VALUES (?,?,?,?,"cust",?,"bill",1)', $data);
        $account_id = $db->id();
        return $account_id;
    }

    public function getShippingAddresses($customer_id) {
        $data = array();
        $query = $this->db->query('select address.*,
                                          cities.Name as city_name,
                                          countries.Country_Name,
                                          districts.district_name,
                                          fast_pax_cities.city_name as fastpax_city_name
                                   from tbl_customer_shipping_address address
                                   inner join tbl_all_cities cities on cities.ID = address.city_id
                                   left join tbl_fastpax_cities fast_pax_cities on fast_pax_cities.city_id = address.fastpax_city_id  
                                   left join tbl_shipping_district districts on districts.district_id = fast_pax_cities.district_id
                                   inner join tbl_all_countries countries on countries.Code =  cities.CountryCode
                                   where address.Customer_ID = "' . $customer_id . '"');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function customerAddressSearch($term = null, $page, $limit) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' and (Customer_Address LIKE ? )';
            $data_filter[] = "%" . $term . "%";
        }
        $query = $this->db->prepare_Stmt('select 
                                    DISTINCT(`Customer_Address`) as text,
                                    Customer_Address as id 
                                from  tbl_customers
                                where Customer_Address is not null and Customer_Address <> ""
                                ' . $cond . '
                                limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function assign_salesman_user_customer() {
        $this->db->query('START TRANSACTION');
        if (!empty($this->info['customer_ids'])) {
            $this->db->prepare_Stmt('delete from tbl_salesman_customers 
                where customer_id in (' . implode(',', $this->info['customer_ids']) . ')');
            $this->db->prepare_Stmt('delete from tbl_user_customers 
                where customer_id in (' . implode(',', $this->info['customer_ids']) . ')');
        }
        if (!empty($this->info['customers'])) {
            foreach ($this->info['customers'] as $row) {
                if (!empty($row['user_ids'])) {
                    foreach ($row['user_ids'] as $user_id) {
                        $this->db->prepare_Stmt('insert into tbl_user_customers
                                (user_id,customer_id) 
                                values(?,?)', [&$user_id, &$row['customer_id']]);
                    }
                }
                if (!empty($row['sales_ids'])) {
                    foreach ($row['sales_ids'] as $sales_id) {
                        $this->db->prepare_Stmt('insert into tbl_salesman_customers
                                (employee_id,customer_id) 
                                values(?,?)', [&$sales_id, &$row['customer_id']]);
                    }
                }
            }
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query('COMMIT');
        } else {
            $this->db->query('ROLLBACK');
        }
        return $error;
    }

    //    public function assign_salesman_customer() {
    //
    //        if ($this->info['is_checked'] == 1) {
    //            $this->db->prepare_Stmt('insert into tbl_salesman_customers
    //                                (employee_id,customer_id) 
    //                                values(?,?)', [&$this->info['employee_id'], &$this->info['customer_id']]);
    //        } else {
    //            $this->db->prepare_Stmt('delete from tbl_salesman_customers
    //                                where employee_id = ? and customer_id = ? ', [&$this->info['employee_id'], &$this->info['customer_id']]);
    //        }
    //        $error = $this->db->error();
    //        return $error;
    //    }

    public function searchCustomerBranch($term = null, $page, $limit) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' and (customer_branch_name LIKE ? )';
            $data_filter[] = "%" . $term . "%";
        }
        $query = $this->db->prepare_Stmt('select 
                                    customer_branch_name as text,
                                    customer_branch_id as id 
                                from  tbl_customer_branches
                                where customer_id  = "' . $this->info['customer_id'] . '"
                                ' . $cond . '
                                limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function get_customer_salesman($customer_id) {
        $data = array();
        $query = $this->db->query('select 
                e.Employee_ID as id,uc.customer_id,
                concat_ws("",e.Employee_FirstName," ",e.Employee_MiddleName," ",e.Employee_LastName) as text
            from tbl_hrm_employee e  join tbl_salesman_customers uc on uc.employee_id = e.Employee_ID 
            where uc.customer_id="' . $customer_id . '"
            ');
        $rowno = $this->db->rowsno($query);
        if ($rowno == 1) {
            $data = $this->db->assoc($query);
        }
        return $data;
    }

    public function getCustomerSales($customer_id) {
        $data = array();
        $query = $this->db->query('select 
                e.Employee_ID as id,uc.customer_id,
                concat_ws("",e.Employee_FirstName," ",e.Employee_MiddleName," ",e.Employee_LastName) as text
            from tbl_hrm_employee e  join tbl_salesman_customers uc on uc.employee_id = e.Employee_ID 
            where uc.customer_id="' . $customer_id . '"
            ');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function searchCustomerUsers($term = null, $page, $limit, $is_app = null) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' and (User_Full_Name LIKE ?)';
            $data_filter[] = "%" . $term . "%";
        }
        if (!is_null($is_app)) {
            $cond .= ' and is_app = "' . $is_app . '" ';
        }
        $query = $this->db->prepare_Stmt('
                                select 
                                     User_ID as id,
                                    User_Full_Name as text
                                from tbl_common_users 
                                where  Status_ID = 1 
                                and User_ID <> 1
                                ' . $cond . '
                                limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $this->db->assoc($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public function getCustomerUser($customer_id) {
        $data = array();
        $query = $this->db->query('select users.User_ID as id,
                                          users.User_Full_Name as text
                                   from tbl_user_customers  user_cust
                                   inner join tbl_common_users users on users.User_ID = user_cust.user_id
                                   where user_cust.customer_id ="' . $customer_id . '"');
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function searchReservations($term = null, $page, $limit) {
        $data = array();
        $data['is_more'] = false;
        $data['data'] = array();
        $hotel_db = CommonFunctions::get_hotel_connection();
        if (empty($hotel_db))
            return $data;
        $cond = '';
        $data_filter = array();
        if (!is_null($term)) {
            $cond .= ' and (guest LIKE ?   or room_name LIKE ?) ';
            $data_filter[] = "%" . $term . "%";
            $data_filter[] = "%" . $term . "%";
        }
        $query = $hotel_db->query_execute('select 
                                    reservation_id as id,
                                    concat_ws(" - ",reservation_id,guest,room_name) as text,
                                    room_name,
                                    guest
                                from 
                                    reservation_list
                                where  reservation_status_id = 2
                                ' . $cond . '
                                limit ' . ($page * $limit) . ',' . ($limit + 1), $data_filter);
        while ($row = $hotel_db->query_fetch($query)) {
            $data['data'][] = $row;
        }
        if (count($data['data']) > $limit) {
            array_pop($data['data']);
            $data['is_more'] = true;
        }
        return $data;
    }

    public static function get_cash_customer_acc() {
        $data = array();
        $db = DBConnectorFactory::loadDriver();
        $query = $db->query('select Account_ID,Account_Code,Account_Name
            from tbl_chart_of_accounts where main_type="cust" and sub_type="main" and main_id=0');
        $row = $db->assoc($query);
        if (!empty($row))
            $data = $row;
        return $data;
    }

    public function get_customers_route_map() {
        $condition = '';
        if (!empty($this->info['city_id']))
            $condition .= ' and ' . ($this->info['is_crm'] == 0 ? 'Customer_City_ID' : 'city_id') . ' = "' . $this->info['city_id'] . '"';
        if (!empty($this->info['region_id']))
            $condition .= ' and region_id = "' . $this->info['region_id'] . '"';
        if (!empty($this->info['customer_group_id']))
            $condition .= ' and ' . ($this->info['is_crm'] == 0 ? 'customer_group_id' : 'organization_group_id') . ' = "' . $this->info['customer_group_id'] . '"';
        if (!empty($this->info['route_ids']))
            $condition .= ' and route_id in (' . $this->info['route_ids'] . ') ';
        if (!empty($this->info['is_not_linked_to_route']))
            $condition .= ' and route_id is null';
        else
            $condition .= ' and route_id is not null';
        //      echo '<pre>';
        //      print_r($this->info);
        $data = array();
        if ($this->info['is_crm'] == 0) {
            $query = $this->db->query('
            select Customer_ID as customer_id,
                   Customer_Name as customer_name,
                   Customer_Lat as customer_latitude,
                   Customer_Lng as customer_longitude,
                   customer_group_id as customer_group_id,
                   Customer_City_ID as city_id
            from tbl_customers
            where Customer_Lat <> 0 and Customer_Lng <> 0
            and Customer_Lat is not null and Customer_Lng is not null ' . $condition);
        } else {
            $query = $this->db->query('
            select organization_group_id as customer_group_id,
                   organization_longitude as customer_longitude,
                   organization_latitude as customer_latitude,
                   city_id as city_id,
                   region_id as region_id,
                   organization_id as customer_id,
                   organization_customer_name as customer_name
            from tbl_customer_organization
            where organization_longitude <> 0 and organization_latitude <> 0 
            and organization_longitude is not null and organization_latitude is not null ' . $condition);
        }
        while ($row = $this->db->assoc($query)) {
            $data[] = $row;
        }
        return $data;
    }

    public function import_customers_excel() {
        //when finish to download an error excel if there is
        if (!empty($_GET['action']) && $_GET['action'] == 'error_excel') {
            $csv_err_file = empty($_GET['path']) ? '' : $_GET['path'];
            if (!empty($csv_err_file) && file_exists($csv_err_file) && strtolower(pathinfo($csv_err_file, PATHINFO_EXTENSION)) == 'csv') {
                header('Content-Type: text/csv; charset=utf-8');
                header('Content-Description: File Transfer');
                header('Content-Disposition: attachment; filename="' . basename($csv_err_file) . '"');
                header('Expires: 0');
                header('Content-Length: ' . filesize($csv_err_file));
                readfile($csv_err_file);
                unlink($csv_err_file);
            }
            exit;
        }
        //
        $jv = !empty($this->info['merge_to_jv']) ? $this->info['merge_to_jv'] : JV::returnJV();
        $open_balance_date = $this->info['opening_balance_date'];
        $created_user = Session::getUserID();
        $created_time = date('Y-m-d H:i:s');
        if (!empty($this->info['merge_to_jv'])) {
            $query = $this->db->query('select date_created,created_logged_id,Transaction_Date
                from tbl_accounting_transactions where JV_ID = ? and is_actual=0 
                and current_year_id = ? and main_type="mjv" ', [$this->info['merge_to_jv'], $this->year_id]);
            $row = $this->db->assoc($query);
            if (empty($row)) {
                return array('is_error' => 1, 'html' => [CommonLanguage::getMessage('no_jv_import_alert')]);
            }
            $open_balance_date = $row['Transaction_Date'];
            $created_time = $row['date_created'];
            $created_user = $row['created_logged_id'];
        }
        //
        $check = CommonFunctions::check_lock_data(['type' => 'mjv', 'start_date' => $open_balance_date, 'is_actual' => 0]);

//        $check = JV::checkCurrentYear($open_balance_date, $open_balance_date, 0);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return array('is_error' => 1, 'html' => $error);
        }
        //
        require dirname(__FILE__) . '/../PHPExcel/PHPExcel/IOFactory.php';
        $Fileextension = $_FILES['import_customer_document']['tmp_name'];
        $excel = PHPExcel_IOFactory::load($Fileextension);
        $writer = PHPExcel_IOFactory::createWriter($excel, 'CSV');
        $writer->setDelimiter(";");
        $writer->setEnclosure("");
        CommonFunctions::convertxlsxtoCSV($Fileextension, 'output.csv');
        //
        //fill needed data array like pricing class, group, country, city,..
        $pricing_class_arr = array();
        $query = $this->db->query('SELECT * FROM tbl_pricing_class');
        while ($row = $this->db->assoc($query))
            $pricing_class_arr[strtolower($row['Class_Name'])] = $row['Class_ID'];
        //
        $country_arr = array();
        $query = $this->db->query('SELECT * FROM tbl_all_countries');
        while ($row = $this->db->assoc($query))
            $country_arr[strtolower($row['Country_Name'])] = $row['Country_ID'];
        //
        $city_arr = array();
        $query = $this->db->query('SELECT c.ID, c.Name, t.Country_ID FROM tbl_all_cities c 
            inner join tbl_all_countries t on t.Code = c.CountryCode ');
        while ($row = $this->db->assoc($query))
            $city_arr[strtolower($row['Name'])] = $row;
        //
        $region_arr = array();
        $query = $this->db->query('SELECT * FROM tbl_regions');
        while ($row = $this->db->assoc($query))
            $region_arr[strtolower($row['region_name'])] = $row['region_id'];
        //
        $group_arr = array();
        $query = $this->db->query('SELECT * FROM tbl_general_groups where group_type="cust"');
        while ($row = $this->db->assoc($query))
            $group_arr[strtolower($row['group_name'])] = $row['group_id'];
        //
        $class_arr = array();
        $query = $this->db->query('SELECT * FROM tbl_general_classes where class_type="cust"');
        while ($row = $this->db->assoc($query))
            $class_arr[strtolower($row['class_name'])] = $row['class_id'];
        //
        $currency_arr = array();
        $query = $this->db->query('SELECT * FROM tbl_common_currency');
        while ($row = $this->db->assoc($query))
            $currency_arr[strtolower($row['Currency_Code'])] = $row['Currency_ID'];
        //
        $user_arr = array();
        $query = $this->db->query('SELECT * FROM tbl_common_users');
        while ($row = $this->db->assoc($query))
            $user_arr[strtolower($row['User_Name'])] = $row['User_ID'];
        //
        $salesman_arr = array();
        $query = $this->db->query('SELECT concat_ws("",`Employee_FirstName`, `Employee_MiddleName`, `Employee_LastName`) as employee_name,
            Employee_ID FROM tbl_hrm_employee');
        while ($row = $this->db->assoc($query))
            $salesman_arr[str_replace(' ', '', strtolower($row['employee_name']))] = $row['Employee_ID'];
        //
        $table_name = "tmp_customer_import_" . microtime(true) . str_shuffle(microtime(true));
        $create_table = "create table `{$table_name}` (
            tmp_excel_row_no int(11) not null UNIQUE,
            tmp_name varchar(255) not null,
            tmp_phone varchar(255) not null,
            tmp_country int(11) not null,
            tmp_city int(11) not null,
            tmp_address varchar(255) not null,
            tmp_pricing_class int(11) not null,
            tmp_cust_currency int(11) not null,
            tmp_discount double DEFAULT '0',
            tmp_limit_balance double DEFAULT '0',
            tmp_limit_days double DEFAULT '0',
            tmp_company varchar(255) not null,
            tmp_region int(11) not null,
            tmp_group int(11) not null,
            tmp_class int(11) not null,
            tmp_commercial_registry varchar(255) not null,
            tmp_second_phone varchar(255) not null,
            tmp_third_phone varchar(255) not null,
            tmp_email varchar(255) not null,
            tmp_note text not null,
            tmp_opening_amount double DEFAULT '0',
            tmp_opening_currency int(11) not null,
            tmp_opening_local double DEFAULT '0',
            tmp_opening_second double DEFAULT '0',
            tmp_users text not null,
            tmp_salesman text not null,
            tmp_cust_exist_id int(11) NULL DEFAULT NULL,
            tmp_cust_insert_id int(11) NULL DEFAULT NULL,
            tmp_is_error tinyint(4) DEFAULT '0',
            tmp_error_text varchar(255) not null
            )
            ENGINE=InnoDB DEFAULT CHARSET=utf8  ";
        //
        $this->db->query($create_table);
        //we moved start transaction below create table since if above and get error will not rollback
        $this->db->query('START TRANSACTION');
        //
        $insert_str = "insert into `{$table_name}` ( tmp_excel_row_no,tmp_name,tmp_phone,tmp_country,
            tmp_city,tmp_address,tmp_pricing_class,tmp_cust_currency,tmp_discount,tmp_limit_balance,
            tmp_limit_days,tmp_company,tmp_region,tmp_group,tmp_class,tmp_commercial_registry,
            tmp_second_phone,tmp_third_phone,tmp_email,tmp_note,tmp_opening_amount,tmp_opening_currency,
            tmp_opening_local,tmp_opening_second,tmp_users,tmp_salesman)";
        if (($getfile = fopen('output.csv', "r"))) {
            fgetcsv($getfile, 1000, ",");   //escape first row/header
            $row_no = 1;
            while (($data = fgetcsv($getfile, 1000, ","))) {
                //trim the data
                array_walk_recursive($data, function(&$value, $index) {
                    $value = trim($value);
                });
                //inset the row to the temp table
                $data_arr = array();
                $data_arr[] = $row_no;    //excel row no.
                $col = 0;
                $data_arr[] = $data[$col++];    //customer name
                $data_arr[] = $data[$col++];    //customer phone
                $country_name = strtolower($data[$col++]);   //here make strtolower to make sure of small/capital 
                $country_id = (isset($country_arr[$country_name]) ? $country_arr[$country_name] : 0);
                $data_arr[] = $country_id;    //country id
                $city_name = strtolower($data[$col++]);   //here make strtolower to make sure of small/capital 
                $city_id = 0;
                if (!empty($city_arr[$city_name]) && $city_arr[$city_name]['Country_ID'] == $country_id)
                    $city_id = $city_arr[$city_name]['ID'];
                $data_arr[] = $city_id;    //city id
                $data_arr[] = $data[$col++];    //customer address
                $pricing_class_name = strtolower($data[$col++]);   //here make strtolower to make sure of small/capital 
                if (empty($pricing_class_name))
                    $pricing_class_id = smc_session()['default_setting']['pricing_class_id'];
                else
                    $pricing_class_id = (isset($pricing_class_arr[$pricing_class_name]) ? $pricing_class_arr[$pricing_class_name] : 0);
                $data_arr[] = $pricing_class_id;    //pricing class id
                $cust_cur_name = strtolower($data[$col++]);   //here make strtolower to make sure of small/capital 
                $data_arr[] = (isset($currency_arr[$cust_cur_name]) ? $currency_arr[$cust_cur_name] : 0);    //customer currency id
                $data_arr[] = $data[$col++]; //discount 
                $data_arr[] = $data[$col++]; //limit balance 
                $data_arr[] = $data[$col++]; //limit days 
                $data_arr[] = $data[$col++]; //company name 
                $region_name = strtolower($data[$col++]);   //here make strtolower to make sure of small/capital 
                $data_arr[] = (isset($region_arr[$region_name]) ? $region_arr[$region_name] : 0);    //region id
                $group_name = strtolower($data[$col++]);   //here make strtolower to make sure of small/capital 
                $data_arr[] = (isset($group_arr[$group_name]) ? $group_arr[$group_name] : 0);    //group id
                $class_name = strtolower($data[$col++]);   //here make strtolower to make sure of small/capital 
                $data_arr[] = (isset($class_arr[$class_name]) ? $class_arr[$class_name] : 0);    //class id
                $data_arr[] = $data[$col++]; //commercial registry 
                $data_arr[] = $data[$col++]; //second phone 
                $data_arr[] = $data[$col++]; //third phone 
                $data_arr[] = $data[$col++]; //email 
                $data_arr[] = $data[$col++]; //customer note 
                $data_arr[] = $data[$col++]; //opening amount
                $op_cur_name = strtolower($data[$col++]);   //here make strtolower to make sure of small/capital 
                $data_arr[] = (isset($currency_arr[$op_cur_name]) ? $currency_arr[$op_cur_name] : 0);    //opening currency id
                $data_arr[] = $data[$col++]; //opening in local 
                $data_arr[] = $data[$col++]; //opening in second 
                $excel_users = $data[$col++];
                $user_ids = '';
                if (!empty($excel_users)) {
                    $user_names = explode(',', $excel_users);
                    foreach ($user_names as $username) {
                        $user_id = (isset($user_arr[$username]) ? $user_arr[$username] : 0);
                        if (!empty($user_id))
                            $user_ids .= ((empty($user_ids) ? '' : ',') . $user_id);
                    }
                }
                $data_arr[] = $user_ids; //users
                //
                $excel_sales = str_replace(' ', '', $data[$col++]);
                $salesman_ids = '';
                if (!empty($excel_sales)) {
                    $salesman_names = explode(',', $excel_sales);
                    foreach ($salesman_names as $name) {
                        $salesman_id = (isset($salesman_arr[$name]) ? $salesman_arr[$name] : 0);
                        if (!empty($salesman_id))
                            $salesman_ids .= ((empty($salesman_ids) ? '' : ',') . $salesman_id);
                    }
                }
                $data_arr[] = $salesman_ids;
                //echo "<pre>" . print_r($data, true) . print_r($data_arr, true) . "<br/>=====" . $col;
                if ($row_no == 1)
                    $insert_str .= " values (" . substr(str_repeat(",?", count($data_arr)), 1) . ") ";
                $this->db->prepare_Stmt($insert_str, $data_arr);
                $row_no++;
            }
        }
        //
        if (empty($this->info['only_opening_balance'])) {
            //check that required fields not empty
            $this->db->query("update `{$table_name}` set tmp_is_error = 1, tmp_error_text = 'Name,  Phone, Country, City [and related to country], Currency, Pricing Class are Required Fields!' 
                where tmp_is_error <> '1' and (tmp_name = '' || tmp_phone = '' || tmp_country = 0 || tmp_city = 0 || tmp_cust_currency = 0 || tmp_pricing_class = 0  ) ");
            //check discount between 0 & 100
            $this->db->query("update `{$table_name}` set tmp_is_error = 1, tmp_error_text = 'Discount must be between 0 & 100' 
                where tmp_is_error <> '1' and (tmp_discount < 0 || tmp_discount >100) ");
            //if there is opening balance then must required to fill the related currency
            $this->db->query("update `{$table_name}` set tmp_is_error = 1, tmp_error_text = 'Opening Balance Amount but no currency!' 
                where tmp_is_error <> '1' and tmp_opening_amount <> 0 && tmp_opening_currency = 0 ");
        }
        // check customer exist
        $this->db->query("update `{$table_name}` t
                inner join tbl_customers p on tmp_name = Customer_Name
            set tmp_cust_exist_id = Customer_ID " . (empty($this->info['only_opening_balance']) ? ", tmp_is_error = 1, tmp_error_text = 'Customer Exist!'" : "") . "
            where tmp_is_error <> '1' ");
        //check for duplications based on customer name in excel
        $this->db->query("update `{$table_name}` t1
                inner join `{$table_name}` t2 on t1.tmp_name = t2.tmp_name
            set t1.tmp_is_error = '1', t1.tmp_error_text = concat('Same Customer Exist in Excel At Row: ', t2.tmp_excel_row_no)
            where t1.tmp_excel_row_no <> t2.tmp_excel_row_no and t1.tmp_is_error <> '1' ");
        //
        if (empty($this->info['only_opening_balance'])) {
            $this->db->query("INSERT INTO `tbl_customers` (`Customer_Name`, 
                `Customer_Email`,`Customer_Mobile`, `Customer_Phone`, `Customer_Work_Phone`,  `Customer_Company`,
                `region_id`, `Customer_Country_ID`, `Customer_City_ID`, `Customer_Address`, `Customer_Discount`,
                `Customer_Limit_Balance`, `Currency_ID`, `Customer_CR`, `Class_ID`, `Logged_ID`, `Parent_ID`,
                `Status_ID`, `stop_invoice`,  `customer_due_date_period`,  `customer_class_id`,
                `customer_group_id`,`customer_note`)
            SELECT tmp_name, tmp_email, tmp_phone, tmp_second_phone,tmp_third_phone,
                tmp_company,tmp_region,tmp_country,tmp_city,tmp_address,tmp_discount,tmp_limit_balance,
                tmp_cust_currency,tmp_commercial_registry,tmp_pricing_class,'" . Session::getUserID() . "',0,1,0,
                tmp_limit_days,tmp_class,tmp_group,tmp_note
            from `{$table_name}` where tmp_is_error <> '1' and isNULL(tmp_cust_exist_id) ");
        }
        //fill the new inserted products ids which is not error and not already exist before
        $this->db->query("update `{$table_name}` t
                inner join tbl_customers p on tmp_name = Customer_Name 
            set tmp_cust_insert_id = Customer_ID where tmp_is_error <> '1' and isNULL(tmp_cust_exist_id) ");
        //delete old customers users & salesman to insert the new one
        if (empty($this->info['only_opening_balance'])) {
            $this->db->prepare_Stmt("delete u.* from tbl_user_customers u join `{$table_name}` t 
            on ifnull(t.tmp_cust_insert_id,t.tmp_cust_exist_id) = u.customer_id where tmp_is_error <> '1'");
            //
            $this->db->prepare_Stmt("delete u.* from tbl_salesman_customers u join `{$table_name}` t 
            on ifnull(t.tmp_cust_insert_id,t.tmp_cust_exist_id) = u.customer_id where tmp_is_error <> '1'");
            //
            $user_query_header = 'INSERT INTO `tbl_user_customers`( `user_id`, `customer_id`) values ';
            $user_body = '';
            $user_count = 0;
            $sales_query_header = 'INSERT INTO `tbl_salesman_customers`(`employee_id`, `customer_id`) values ';
            $sales_body = '';
            $sales_count = 0;
        }

        //
        $total = 0;
        $query = $this->db->query("select * from `{$table_name}` where tmp_is_error <> '1' 
            and (!isNULL(tmp_cust_exist_id) or !isNULL(tmp_cust_insert_id)) ");
        while ($row = $this->db->assoc($query)) {
            $customer_id = empty($row['tmp_cust_insert_id']) ? $row['tmp_cust_exist_id'] : $row['tmp_cust_insert_id'];
            //insert the customer users/salesman
            if (empty($this->info['only_opening_balance'])) {
                if (!empty($row['tmp_users'])) {
                    $users = explode(',', $row['tmp_users']);
                    foreach ($users as $user) {
                        if ($user_count > 1000) {
                            $this->db->prepare_Stmt($user_query_header . $user_body);
                            $user_count = 0;
                            $user_body = '';
                        }
                        $user_body .= ((empty($user_body) ? '' : ',') . ' (' . $user . ',' . $customer_id . ')');
                        $user_count++;
                    }
                }
                //
                if (!empty($row['tmp_salesman'])) {
                    $sales = explode(',', $row['tmp_salesman']);
                    foreach ($sales as $salesman) {
                        if ($sales_count > 1000) {
                            $this->db->prepare_Stmt($sales_query_header . $sales_body);
                            $sales_count = 0;
                            $sales_body = '';
                        }
                        $sales_body .= ((empty($sales_body) ? '' : ',') . ' (' . $salesman  . ',' . $customer_id . ')');
                        $sales_count++;
                    }
                }
            }
            //
            if (!empty($row['tmp_opening_amount'])) {
                $current_rate = 0;
                if ($row['tmp_opening_currency'] == smc_session()['default_setting']['local_currency'])
                    $current_rate = 1;
                elseif (!empty($row['tmp_opening_local']))
                    $current_rate = abs($row['tmp_opening_local'] / $row['tmp_opening_amount']);
                else
                    $current_rate = CurrencyFactory::getLastExRate($row['tmp_opening_currency']);
                //
                $second_rate = 0;
                if ($row['tmp_opening_currency'] == smc_session()['default_setting']['second_currency'])
                    $second_rate = 1;
                elseif (!empty($row['tmp_opening_second']))
                    $second_rate = abs($row['tmp_opening_second'] / $row['tmp_opening_amount']);
                else
                    $second_rate = CurrencyFactory::getSecondCurrencyLastExRate($row['tmp_opening_currency']);
                //
                $customer_account = Customer::getCustomerAccount($customer_id)['Account_ID'];
                //
                $data_trans = ["JV_ID" => $jv,
                    "Transaction_Reference" => 'opening balances',
                    "Transaction_Account" => $customer_account,
                    "Transaction_Type" => ($row['tmp_opening_amount'] > 0 ? "Debit" : "Credit"),
                    "Transaction_Amount" => abs($row['tmp_opening_amount']),
                    "Transaction_Date" => $open_balance_date,
                    "Currency_ID" => $row['tmp_opening_currency'],
                    "Logged_ID" => Session::getUserID(),
                    "created_logged_id" => $created_user,
                    "Transaction_Description" => 'opening balances',
                    "date_created" => $created_time,
                    "date_time" => date('Y-m-d H:i:s'),
                    "Current_Ex_Rate" => $current_rate,
                    "second_currency_rate" => $second_rate,
                    "main_id" => 0,
                    "main_type" => "mjv",
                    "main_nb" => $jv
                ];
                JV::addTransaction($data_trans);
                $total += ($row['tmp_opening_amount'] * $current_rate);
            }
        }
        //for the opening balance fill the opposite account
        if (!empty($total)) {
            $data_trans = ["JV_ID" => $jv,
                "Transaction_Reference" => 'opening balances',
                "Transaction_Account" => $this->info['close_opening_jv_acc'],
                "Transaction_Type" => $total > 0 ? "Credit" : "Debit",
                "Transaction_Amount" => abs($total),
                "Transaction_Date" => $open_balance_date,
                "Currency_ID" => smc_session()['default_setting']['local_currency'],
                "Logged_ID" => Session::getUserID(),
                "created_logged_id" => $created_user,
                "Transaction_Description" => 'opening balances',
                "date_created" => $created_time,
                "date_time" => date('Y-m-d H:i:s'),
                "Current_Ex_Rate" => 1,
                "main_id" => 0,
                "main_type" => "mjv",
                "main_nb" => $jv
            ];
            JV::addTransaction($data_trans);
        }
        //
        if (empty($this->info['only_opening_balance'])) {
            if (!empty($user_body))
                $this->db->prepare_Stmt($user_query_header . $user_body);
            if (!empty($sales_body))
                $this->db->prepare_Stmt($sales_query_header . $sales_body);
        }
        //
        $error = $this->db->error();
        //
        $html = "";
        $is_error = '0';
        if (empty($error)) {
            $this->db->query('COMMIT');
            //create error csv if there is
            $query = $this->db->query("SELECT tmp_excel_row_no,  tmp_name, tmp_error_text 
                                    FROM `{$table_name}`  where tmp_is_error = '1' order by tmp_excel_row_no asc");
            if ($this->db->rowsno($query)) {
                $import_error_csv_path = SYSTEM_PATH . 'import-customer-error.csv';
                $output = fopen($import_error_csv_path, 'w');
                //output the column header
                fputcsv($output, array('Excel Row Number', 'Name', 'Error'));
                while ($row = $this->db->assoc($query))
                    fputcsv($output, $row);
                fclose($output);
                $html = "<iframe src='" . SITE_LINK . "/InventoryAjax/import_customers_excel/?action=error_excel&path=" . urlencode($import_error_csv_path) . "' style='display:none;width:0px;height:0px;' ></iframe>";
            }
        } else {
            $this->db->query('ROLLBACK');
            $is_error = '1';
            $html = array_unique($error);
        }
        //
        $this->db->query("DROP TABLE IF EXISTS `{$table_name}`");
        //
        return array('is_error' => $is_error, 'html' => $html);
    }

    
    public function import_supplier_excel() {
        //when finish to download an error excel if there is
        if (!empty($_GET['action']) && $_GET['action'] == 'error_excel') {
            $csv_err_file = empty($_GET['path']) ? '' : $_GET['path'];
            if (!empty($csv_err_file) && file_exists($csv_err_file) && strtolower(pathinfo($csv_err_file, PATHINFO_EXTENSION)) == 'csv') {
                header('Content-Type: text/csv; charset=utf-8');
                header('Content-Description: File Transfer');
                header('Content-Disposition: attachment; filename="' . basename($csv_err_file) . '"');
                header('Expires: 0');
                header('Content-Length: ' . filesize($csv_err_file));
                readfile($csv_err_file);
                unlink($csv_err_file);
            }
            exit;
        }
        //
        $jv = !empty($this->info['merge_to_jv_supp']) ? $this->info['merge_to_jv_supp'] : JV::returnJV();
        $open_balance_date = $this->info['opening_balance_date_supp'];
        $created_user = Session::getUserID();
        $created_time = date('Y-m-d H:i:s');
        if (!empty($this->info['merge_to_jv_supp'])) {
            $query = $this->db->query('select date_created,created_logged_id,Transaction_Date
                from tbl_accounting_transactions where JV_ID = ? and is_actual=0 
                and current_year_id = ? and main_type="mjv" ', [$this->info['merge_to_jv_supp'], $this->year_id]);
            $row = $this->db->assoc($query);
            if (empty($row)) {
                return array('is_error' => 1, 'html' => [CommonLanguage::getMessage('no_jv_import_alert')]);
            }
            $open_balance_date = $row['Transaction_Date'];
            $created_time = $row['date_created'];
            $created_user = $row['created_logged_id'];
        }
        //
        $check = CommonFunctions::check_lock_data(['type' => 'mjv', 'start_date' => $open_balance_date, 'is_actual' => 0]);

//        $check = JV::checkCurrentYear($open_balance_date, $open_balance_date, 0);
        if ($check !== '') {
            $error = array();
            $error[0] = $check;
            return array('is_error' => 1, 'html' => $error);
        }
        //
        require dirname(__FILE__) . '/../PHPExcel/PHPExcel/IOFactory.php';
        $Fileextension = $_FILES['import_supplier_document']['tmp_name'];
        $excel = PHPExcel_IOFactory::load($Fileextension);
        $writer = PHPExcel_IOFactory::createWriter($excel, 'CSV');
        $writer->setDelimiter(";");
        $writer->setEnclosure("");
        CommonFunctions::convertxlsxtoCSV($Fileextension, 'output.csv');
        //
        //fill needed data array like pricing class, group, country, city,..
        $country_arr = array();
        $query = $this->db->query('SELECT * FROM tbl_all_countries');
        while ($row = $this->db->assoc($query))
            $country_arr[strtolower($row['Country_Name'])] = $row['Country_ID'];
        //
        $city_arr = array();
        $query = $this->db->query('SELECT c.ID, c.Name, t.Country_ID FROM tbl_all_cities c 
            inner join tbl_all_countries t on t.Code = c.CountryCode ');
        while ($row = $this->db->assoc($query))
            $city_arr[strtolower($row['Name'])] = $row;
        //
        //
        //
        $currency_arr = array();
        $query = $this->db->query('SELECT * FROM tbl_common_currency');
        while ($row = $this->db->assoc($query))
            $currency_arr[strtolower($row['Currency_Code'])] = $row['Currency_ID'];
        //
        $user_arr = array();
        $query = $this->db->query('SELECT * FROM tbl_common_users');
        while ($row = $this->db->assoc($query))
            $user_arr[strtolower($row['User_Name'])] = $row['User_ID'];
        //
        $salesman_arr = array();
        $query = $this->db->query('SELECT concat_ws("",`Employee_FirstName`, `Employee_MiddleName`, `Employee_LastName`) as employee_name,
            Employee_ID FROM tbl_hrm_employee');
        while ($row = $this->db->assoc($query))
            $salesman_arr[str_replace(' ', '', strtolower($row['employee_name']))] = $row['Employee_ID'];
        //
        $table_name = "tmp_supplier_import_" . microtime(true) . str_shuffle(microtime(true));
        $create_table = "create table `{$table_name}` (
            tmp_excel_row_no int(11) not null UNIQUE,
            tmp_name varchar(255) not null,
            tmp_phone varchar(255) not null,
            tmp_country int(11) not null,
            tmp_city int(11) not null,
            tmp_address varchar(255) not null,
            tmp_cust_currency int(11) not null,
            tmp_email varchar(255) not null,
            tmp_limit_balance double DEFAULT '0',
            tmp_limit_days double DEFAULT '0',
            tmp_opening_amount double DEFAULT '0',
            tmp_opening_currency int(11) not null,
            tmp_opening_local double DEFAULT '0',
            tmp_opening_second double DEFAULT '0',
            tmp_cust_exist_id int(11) NULL DEFAULT NULL,
            tmp_cust_insert_id int(11) NULL DEFAULT NULL,
            tmp_is_error tinyint(4) DEFAULT '0',
            tmp_error_text varchar(255) not null
            )
            ENGINE=InnoDB DEFAULT CHARSET=utf8  ";
        //
        $this->db->query($create_table);
        //we moved start transaction below create table since if above and get error will not rollback
        $this->db->query('START TRANSACTION');
        //
        $insert_str = "insert into `{$table_name}` ( 
            tmp_excel_row_no,
            tmp_name,
            tmp_phone,
            tmp_country,
            tmp_city,
            tmp_address,
            tmp_cust_currency,
            tmp_email,
            tmp_limit_balance,
            tmp_limit_days,
            tmp_opening_amount,
            tmp_opening_currency,
            tmp_opening_local,
            tmp_opening_second)";
        if (($getfile = fopen('output.csv', "r"))) {
            fgetcsv($getfile, 1000, ",");   //escape first row/header
            $row_no = 1;
            while (($data = fgetcsv($getfile, 1000, ","))) {
                //trim the data
                array_walk_recursive($data, function(&$value, $index) {
                    $value = trim($value);
                });
                //inset the row to the temp table
                $data_arr = array();
                $data_arr[] = $row_no;    //excel row no.
                $col = 0;
                $data_arr[] = $data[$col++];    //supplier name
                $data_arr[] = $data[$col++];    //supplier phone
                $country_name = strtolower($data[$col++]);   //here make strtolower to make sure of small/capital 
                $country_id = (isset($country_arr[$country_name]) ? $country_arr[$country_name] : 0);
                $data_arr[] = $country_id;    //country id
                $city_name = strtolower($data[$col++]);   //here make strtolower to make sure of small/capital 
                $city_id = 0;
                if (!empty($city_arr[$city_name]) && $city_arr[$city_name]['Country_ID'] == $country_id)
                    $city_id = $city_arr[$city_name]['ID'];
                $data_arr[] = $city_id;    //city id
                $data_arr[] = $data[$col++];    //supplier address
                $cust_cur_name = strtolower($data[$col++]);   //here make strtolower to make sure of small/capital 
                $data_arr[] = (isset($currency_arr[$cust_cur_name]) ? $currency_arr[$cust_cur_name] : 0);    //supplier currency id
                $data_arr[] = $data[$col++]; //email 
                $data_arr[] = $data[$col++]; //limit balance 
                $data_arr[] = $data[$col++]; //limit days 
                $data_arr[] = $data[$col++]; //opening balance
                $op_cur_name = strtolower($data[$col++]);   //here make strtolower to make sure of small/capital 
                $data_arr[] = (isset($currency_arr[$op_cur_name]) ? $currency_arr[$op_cur_name] : 0);    //opening currency id
                $data_arr[] = $data[$col++]; //opening in local 
                $data_arr[] = $data[$col++]; //opening in second 
                //
                
                //echo "<pre>" . print_r($data, true) . print_r($data_arr, true) . "<br/>=====" . $col;
                if ($row_no == 1)
                    $insert_str .= " values (" . substr(str_repeat(",?", count($data_arr)), 1) . ") ";
                $this->db->prepare_Stmt($insert_str, $data_arr);
                $row_no++;
            }
        }
        //
        if (empty($this->info['only_opening_balance_supp'])) {
            //check that required fields not empty
            $this->db->query("update `{$table_name}` set tmp_is_error = 1, tmp_error_text = 'Name,  Phone, Country, City [and related to country], Currency, Pricing Class are Required Fields!' 
                where tmp_is_error <> '1' and (tmp_name = '' || tmp_phone = '' || tmp_country = 0 || tmp_city = 0   ) ");
            
            //if there is opening balance then must required to fill the related currency
            $this->db->query("update `{$table_name}` set tmp_is_error = 1, tmp_error_text = 'Opening Balance Amount but no currency!' 
                where tmp_is_error <> '1' and tmp_opening_amount <> 0 && tmp_opening_currency = 0 ");
        }
        // check supplier exist
        $this->db->query("update `{$table_name}` t
                inner join tbl_stock_suppliers p on tmp_name = Supplier_Name
            set tmp_cust_exist_id = Supplier_ID " . (empty($this->info['only_opening_balance_supp']) ? ", tmp_is_error = 1, tmp_error_text = 'Supplier Exist!'" : "") . "
            where tmp_is_error <> '1' ");
        //check for duplications based on supplier name in excel
        $this->db->query("update `{$table_name}` t1
                inner join `{$table_name}` t2 on t1.tmp_name = t2.tmp_name
            set t1.tmp_is_error = '1', t1.tmp_error_text = concat('Same supplier Exist in Excel At Row: ', t2.tmp_excel_row_no)
            where t1.tmp_excel_row_no <> t2.tmp_excel_row_no and t1.tmp_is_error <> '1' ");
        //
        if (empty($this->info['only_opening_balance_supp'])) {
            $this->db->query("INSERT INTO `tbl_stock_suppliers` (`Supplier_Name`, 
                `Supplier_Phone`,`Supplier_Country`, `City_ID`, `Supplier_Address`,  `Currency_ID`,
                `Supplier_Email`)
            SELECT tmp_name, tmp_phone, tmp_country, tmp_city,tmp_address,tmp_cust_currency,tmp_email
            from `{$table_name}` where tmp_is_error <> '1' and isNULL(tmp_cust_exist_id) ");
        }
        //fill the new inserted products ids which is not error and not already exist before
        $this->db->query("update `{$table_name}` t
                inner join tbl_stock_suppliers p on tmp_name = Supplier_Name 
            set tmp_cust_insert_id = Supplier_ID where tmp_is_error <> '1' and isNULL(tmp_cust_exist_id) ");
        
            
        $total = 0;
        $query = $this->db->query("select * from `{$table_name}` where tmp_is_error <> '1' 
            and (!isNULL(tmp_cust_exist_id) or !isNULL(tmp_cust_insert_id)) ");
        while ($row = $this->db->assoc($query)) {
            $Supplier_ID = empty($row['tmp_cust_insert_id']) ? $row['tmp_cust_exist_id'] : $row['tmp_cust_insert_id'];
              
            //
            if (!empty($row['tmp_opening_amount'])) {
                $current_rate = 0;
                if ($row['tmp_opening_currency'] == smc_session()['default_setting']['local_currency'])
                    $current_rate = 1;
                elseif (!empty($row['tmp_opening_local']))
                    $current_rate = abs($row['tmp_opening_local'] / $row['tmp_opening_amount']);
                else
                    $current_rate = CurrencyFactory::getLastExRate($row['tmp_opening_currency']);
                //
                $second_rate = 0;
                if ($row['tmp_opening_currency'] == smc_session()['default_setting']['second_currency'])
                    $second_rate = 1;
                elseif (!empty($row['tmp_opening_second']))
                    $second_rate = abs($row['tmp_opening_second'] / $row['tmp_opening_amount']);
                else
                    $second_rate = CurrencyFactory::getSecondCurrencyLastExRate($row['tmp_opening_currency']);
                //
                $Supplier_account = Supplier::getSupplierAccount($Supplier_ID);
                //
                $data_trans = ["JV_ID" => $jv,
                    "Transaction_Reference" => 'opening balances',
                    "Transaction_Account" => $Supplier_account,
                    "Transaction_Type" => "Credit",
                    "Transaction_Amount" => abs($row['tmp_opening_amount']),
                    "Transaction_Date" => $open_balance_date,
                    "Currency_ID" => $row['tmp_opening_currency'],
                    "Logged_ID" => Session::getUserID(),
                    "created_logged_id" => $created_user,
                    "Transaction_Description" => 'opening balances',
                    "date_created" => $created_time,
                    "date_time" => date('Y-m-d H:i:s'),
                    "Current_Ex_Rate" => $current_rate,
                    "second_currency_rate" => $second_rate,
                    "main_id" => 0,
                    "main_type" => "mjv",
                    "main_nb" => $jv
                ];
                JV::addTransaction($data_trans);
                $total += ($row['tmp_opening_amount'] * $current_rate);
            }
        }
        //for the opening balance fill the opposite account
        if (!empty($total)) {
            $data_trans = ["JV_ID" => $jv,
                "Transaction_Reference" => 'opening balances',
                "Transaction_Account" => $this->info['close_opening_jv_acc_supp'],
                "Transaction_Type" => "Credit",
                "Transaction_Amount" => abs($total),
                "Transaction_Date" => $open_balance_date,
                "Currency_ID" => smc_session()['default_setting']['local_currency'],
                "Logged_ID" => Session::getUserID(),
                "created_logged_id" => $created_user,
                "Transaction_Description" => 'opening balances',
                "date_created" => $created_time,
                "date_time" => date('Y-m-d H:i:s'),
                "Current_Ex_Rate" => 1,
                "main_id" => 0,
                "main_type" => "mjv",
                "main_nb" => $jv
            ];
            JV::addTransaction($data_trans);
        }
        //
        if (empty($this->info['only_opening_balance_supp'])) {
            if (!empty($user_body))
                $this->db->prepare_Stmt($user_query_header . $user_body);
            if (!empty($sales_body))
                $this->db->prepare_Stmt($sales_query_header . $sales_body);
        }
        //
        $error = $this->db->error();
        //
        $html = "";
        $is_error = '0';
        if (empty($error)) {
            $this->db->query('COMMIT');
            //create error csv if there is
            $query = $this->db->query("SELECT tmp_excel_row_no,  tmp_name, tmp_error_text 
                                    FROM `{$table_name}`  where tmp_is_error = '1' order by tmp_excel_row_no asc");
            if ($this->db->rowsno($query)) {
                $import_error_csv_path = SYSTEM_PATH . 'import-supplier-error.csv';
                $output = fopen($import_error_csv_path, 'w');
                //output the column header
                fputcsv($output, array('Excel Row Number', 'Name', 'Error'));
                while ($row = $this->db->assoc($query))
                    fputcsv($output, $row);
                fclose($output);
                $html = "<iframe src='" . SITE_LINK . "/InventoryAjax/import_supplier_excel/?action=error_excel&path=" . urlencode($import_error_csv_path) . "' style='display:none;width:0px;height:0px;' ></iframe>";
            }
        } else {
            $this->db->query('ROLLBACK');
            $is_error = '1';
            $html = array_unique($error);
        }
        //
        $this->db->query("DROP TABLE IF EXISTS `{$table_name}`");
        //
        return array('is_error' => $is_error, 'html' => $html);
    }

    public function get_customer_count() {
        $Employee_ID =smc_session()['user_details']['Employee_ID'];
         $cond2 ='';
 
        if($this->controller_permission->hasPermission('Branches')){
            $cond2 .=  '';

        }else{
            $cond2 .=' and branch_id ="' . (smc_session()['user_accounts']['branch_id']) . '"'  ;

        }

        if (smc_session()['user_details']['limit_customer_invoice'] == 1) {
            $query = $this->db->query('SELECT count(*) as customer_count FROM `tbl_customers` as c, tbl_salesman_customers as s where c.Customer_ID = s.customer_id and s.employee_id = '.$Employee_ID.' '.$cond2.' ');
        }else{
            $query = $this->db->query('SELECT count(*) as customer_count FROM `tbl_customers` '.$cond2.' ');
        }

        
        $row = $this->db->assoc($query);
        return $row['customer_count'];
    }
    public function check_customer_phone($Customer_Mobile) {
        $query = $this->db->query('SELECT * FROM `tbl_customers` WHERE Customer_Mobile = "' . $Customer_Mobile . '" ');
        $row = $this->db->assoc($query);
        return $row;
    }

}
