PHP File Manager

Editing File: Customers.php

<?php

/* 
 * Copyright (C) Wayne Purton-Smith - All Rights Reserved
 * Unauthorized copying of this file or removing this paragraph, via any medium is strictly prohibited
 * Proprietary and confidential
 * Written by Wayne Purton-Smith <waynepurtonsmith@hotmail.co.uk> February 2014
 */

class Customers extends CI_Model {
    
    public function get($customer_id = 0, $filters = array(), $skip = 0, $limit = 30)
    {
        $filters = array_merge(array
        (
            'count'                 => false,
            'legacy'                => NULL,
            'exclude_requests'      => false,
            'email'                 => NULL,
            'customer_details'      => NULL,
            'query'                 => NULL,
            'registered_date_from'  => NULL,
            'registered_date_to'    => NULL,
            'sage_account'          => NULL,
            'unsubscribe_key'       => NULL,
            'retail'                => NULL,
            'archived'              => NULL
        ), (array) $filters);
        
        $flat_row = false;

$sql = "SELECT *,
                       IF(UPPER(LEFT(`c`.`name_order`, 1)) REGEXP '^[^A-Za-z]$', '#', UPPER(LEFT(`c`.`name_order`, 1))) AS `name_letter`,
                       IF((`c`.`total_inc_vat` - `c`.`total_paid`) < 0, 0, (`c`.`total_inc_vat` - `c`.`total_paid`)) AS `total_outstanding`,
                       IFNULL(ROUND(((100 / `c`.`total_inc_vat`) * `c`.`total_paid`), 2), 0) AS `percentage_paid`,
                       IFNULL(`c`.`_total_payments`, 0) AS `total_payments`,
                       0 AS `remaining_credit`, GROUP_CONCAT(`c`.`total_inc_vat`) AS `totalamount`
                FROM
                (
                    SELECT *,
                           
                           ROUND((`c`.`total` + `c`.`delivery_costs`), 2) AS `total_inc_vat`

# ROUND((`c`.`total` + (`c`.`total` * (`c`.`_vat` / 100))) + `c`.`delivery_costs`, 2) AS `total_inc_vat`
                    FROM
                    (
                        SELECT `c`.*,
                               IF(`c`.`trade_request` IS NOT NULL, '', IF(`c`.`business` IS NOT NULL, `c`.`business`, TRIM(CONCAT_WS(', ', `c`.`last_name`, `c`.`first_name`)))) AS `name_order`,
                               `o2`.`total_orders`,
                               `o`.`_total`,
                               `o`.`_vat`,
                               `o`.`order_id` AS `latest_order_id`,
                               `o`.`order_number` AS `latest_order_number`,
                               `o`.`ordered_date` AS `latest_order_date`,

IFNULL((SUM(IF(`op`.`amount` > 0, `op`.`amount`, 0)) + IFNULL(`cp`.`total_paid`, 0)), 0) AS `total_paid`,

(IFNULL(`cp`.`total_payments`, 0) + COUNT(`op`.`order_id`)) AS `_total_payments`,

SUM(IF(`o`.`discount_type` = " . DISCOUNT_AMOUNT . ", 
                                   `o`.`_total` - `o`.`discount`,

IF(`o`.`discount_type` = " . DISCOUNT_PERCENTAGE . ",
                                       ROUND(`o`.`_total` - ((`o`.`_total` / 100) * `o`.`discount`), 2),
                                       `o`.`_total`
                                   )
                               )) AS `total`,
                               
                               IFNULL(IF(`o`.`delivery_cost` IS NOT NULL, `o`.`delivery_cost`, `del`.`costs`), 0) AS `delivery_costs`,
                               
                               `ref`.`label` AS `referral_type`,
                               `ref`.`friendly_name` AS `friendly_referral_type` ";
        
        if(($customer_details = trim($filters['customer_details'])))
        {
            $sql .= ", MATCH(`c`.`first_name`, `c`.`last_name`, `c`.`business`, `c`.`address_1`, `c`.`address_2`, `c`.`address_3`, `c`.`town`, `c`.`county`, `c`.`postcode`, `c`.`email`, `c`.`phone`, `c`.`mobile`) AGAINST('" . $this->db->escape_str($customer_details) . "') AS `search_relevance`";
        }
        
        $sql .= "   FROM 
                    (
                        `customers` `c`

LEFT OUTER JOIN
                        (
                            SELECT `o`.`order_id`,
                                   `o`.`order_number`,
                                   `o`.`ordered_date`,
                                   `o`.`customer_id`,
                                   `o`.`discount`,
                                   `o`.`discount_type`,
                                   `o`.`delivery_type`,
                                   `o`.`delivery_cost`,
                                   `o`.`vat` AS `_vat`,
                                   `oi`.`_total`
                            FROM
                            (
                                `orders` `o`

INNER JOIN
                                (
                                    SELECT *,
                                           `oi`.`_total` AS `total`
                                    FROM 
                                    (
                                        SELECT SUM((`oi`.`quantity` * `oi`.`price`) + `oi`.`gift_wrap_cost` + `o`.`additional_charges`) AS `_total`,
                                               `oi`.`order_id`,
                                               `oi`.`item_id`
                                        FROM
                                        (
                                            `order_items` `oi`
                                            
                                            INNER JOIN `orders` `o` ON `oi`.`order_id` = `o`.`order_id` AND `oi`.`is_deleted` = 0
                                        )
                                        GROUP BY `oi`.`order_id`
                                    ) AS `oi` 
                                    GROUP BY `oi`.`order_id`
                                ) AS `oi` ON `oi`.`order_id` = `o`.`order_id`
                            )
                            WHERE `o`.`is_deleted` = 0
                            ORDER BY `o`.`ordered_date` DESC
                        ) AS `o` ON `o`.`customer_id` = `c`.`customer_id`

LEFT OUTER JOIN
                        (
                            SELECT COUNT(`order_id`) AS `total_orders`,
                                   `customer_id`
                            FROM `orders`
                            WHERE `is_deleted` = 0
                            GROUP BY `customer_id`
                        ) AS `o2` ON `o2`.`customer_id` = `c`.`customer_id`
                        
                        LEFT OUTER JOIN 
                        (
                            SELECT `payment_id`,
                                   `order_id`,
                                   `amount`
                            FROM `order_payments`
                            WHERE `is_deleted` = 0
                        ) AS `op` ON `op`.`order_id` = `o`.`order_id`
                        
                        LEFT OUTER JOIN
                        (
                            SELECT `payment_id`,
                                   SUM(`amount`) AS `total_paid`,
                                   COUNT(`payment_id`) AS `total_payments`,
                                   `customer_id`,
                                   `amount`
                            FROM `customer_payments`
                            WHERE `is_deleted` = 0
                            GROUP BY `customer_id`
                        ) AS `cp` ON `cp`.`customer_id` = `c`.`customer_id`
                        
                        LEFT OUTER JOIN `delivery_types` `del` ON `del`.`delivery_id` = `o`.`delivery_type`

INNER JOIN `referral_types` `ref` ON `ref`.`refer_id` = `c`.`referral`
                    )
                    WHERE `c`.`is_deleted` = 0";
        
        if(is_bool($filters['archived']))
        {
            $sql .= " AND `c`.`is_archived` = " . (int) $filters['archived'] . " ";
        }
        
        if(($customer_id = (int) $customer_id) > 0)
        {
            $sql .= " AND `c`.`customer_id` = $customer_id ";
            $limit = 1;
        }
        
        if(($legacy_customer_id = (int) $filters['legacy']) > 0)
        {
            $sql .= " AND `c`.`legacy_customer_id` = $legacy_customer_id ";
            
            $limit = 1;
            $flat_row = true;
        }
        
        if($filters['exclude_requests'] === true)
        {
            $sql .= " AND `c`.`trade_request` IS NULL ";
        }
        
        if(is_bool($filters['retail']))
        {
            $sql .= " AND `c`.`retail_customer` = " . (int) $filters['retail'] . " ";
        }
        
        if($filters['sage_account'])
        {
            $sql .= " AND `c`.`sage_account_code` = " . $this->db->escape($filters['sage_account']) . " ";
            
            $limit = 1;
            $flat_row = true;
        }
        
        if(($email_lookup = remove_whitespace($filters['email'])) != '')
        {
            $sql .= " AND `c`.`email` = " . $this->db->escape($email_lookup) . " ";
            
            $limit = 1;
            $flat_row = true;
        }
        
        if(($unsubscribe_key = remove_whitespace($filters['unsubscribe_key'])) != '')
        {
            $sql .= " AND `c`.`unsubscribe_key` = " . $this->db->escape($unsubscribe_key) . " ";
            
            $limit = 1;
            $flat_row = true;
        }
        
        if(strlen(($search_query = trim($filters['query']))) >= 3)
        {
            $escaped_query = $this->db->escape_like_str($search_query);
            
            $sql .= " AND ( CONCAT_WS(' ', `c`.`first_name`, `c`.`last_name`) LIKE '%$escaped_query%' OR `c`.`business` LIKE '%$escaped_query%' ) ";
        }
        
        foreach(array('registered' => 'registered_date') as $date_mode => $column_name)
        {
            $date_var_from = $date_mode . '_date_from';
            $date_var_to = $date_mode . '_date_to';
            
            if($filters[$date_mode . '_date_from'] || $filters[$date_mode . '_date_to'])
            {
                $$date_var_from = $filters[$date_mode . '_date_from'];
                $$date_var_to = $filters[$date_mode . '_date_to'];
                
                if(!is_numeric($$date_var_to) && !is_numeric($$date_var_to) && (is_string($$date_var_to) || is_string($$date_var_to)))
                {
                    $$date_var_from = strtotime($$date_var_from . ' 00:00:00');
                    $$date_var_to = strtotime($$date_var_to . ' 23:59:59');
                }

if($$date_var_from > 0 && $$date_var_to <= 0)
                {
                    $sql .= " AND `c`.`$column_name` >= " . $$date_var_from . " ";
                }
                if($$date_var_from <= 0 && $$date_var_to > 0)
                {
                    $sql .= " AND `c`.`$column_name` <= " . $$date_var_to . " ";
                }
                elseif($$date_var_from > 0 && $$date_var_to > 0)
                {
                    $sql .= " AND ( `c`.`$column_name` >= " . $$date_var_from . " AND `c`.`$column_name` <= " . $$date_var_to . " ) ";
                }
            }
        }
        
        $sql .= " GROUP BY `c`.`customer_id` ";
        
        if($customer_details)
        {
            $sql .= " HAVING `search_relevance` >= 3
                      ORDER BY `search_relevance` DESC ";
        }
        else
        {
            $sql .= " ORDER BY `name_order` ";
        }
        
        $sql .= " ) AS `c` ";
        
        $sql .= " ) AS `c` GROUP BY `c`.`customer_id` ";
        
        $sql .= $this->common->returnOffset($skip, $limit);

if($filters['count'] === true)
        {
            return $this->common->countPreviousQuery($sql);
        }
        
        $result = $this->db->query($sql);

if(wayne())
        {
            //preprint($this->db->last_query());
        }
        
        if($result->num_rows())
        {
            $rows = $result->result();
            $result->free_result();

return ($customer_id > 0 && $limit === 1 || $flat_row) ? reset($rows) : $rows;
        }
        
        return array();
    }
    
    public function saveCustomer($data = array(), $customer_id = 0, $save_address_details = false)
    {
        //decode_post_data($data);
        
        $customer_id = (($customer_id = (int) $customer_id) > 0) ? $customer_id : 0;
        $is_update = ($customer_id > 0);
        
        $is_trade_request = (!$is_update && isset($data['is_trade_request']));
        $is_retail_customer = false;
        
        $customer_details = array
        (
            'legacy_customer_id'        => NULL,
            'retail_customer'           => NULL,
            'first_name'                => NULL, 
            'last_name'                 => NULL, 
            'business'                  => NULL,
            'address_1'                 => NULL, 
            'address_2'                 => NULL, 
            'address_3'                 => NULL, 
            'town'                      => NULL, 
            'county'                    => NULL, 
            'postcode'                  => NULL,
            'country'                   => NULL,
            'country_code'              => NULL,
            'latitude'                  => NULL,
            'longitude'                 => NULL,
            'email'                     => NULL,
            'cc_email'                  => NULL,
            'phone'                     => NULL,
            'mobile'                    => NULL,
            'username'                  => NULL,
            'password'                  => NULL,
            'referral'                  => NULL,
            'registered_date'           => NULL,
            'preferred_delivery_time'   => NULL,
            'confirm_password'          => NULL,
            'default_discount'          => NULL,
            'default_discount_type'     => NULL,
            'no_charges'                => NULL,
            'sage_account_code'         => NULL,
            'trade_request'             => NULL,
            'change_password'           => NULL,
            'subscribed_newsletter'     => true
        );
        
        if(isset($data['is_retail']))
        {
            $customer_details['retail_customer'] = (bool) $data['is_retail'];
            $is_retail_customer = $customer_details['retail_customer'];
        }
        else
        {
            unset($customer_details['retail_customer']);
        }
        
        $customer_details['first_name'] = nullify((isset($data['first_name'])) ? format_name($data['first_name']) : NULL);
        $customer_details['last_name'] = nullify((isset($data['last_name'])) ? format_name($data['last_name']) : NULL);
        $customer_details['business'] = nullify((isset($data['business'])) ? format_name($data['business']) : NULL);
        
        foreach(array('address_1', 'address_2', 'address_3', 'town', 'county', 'postcode') as $detail_key)
        {
            if(isset($data[$detail_key]))
            {
                $customer_details[$detail_key] = nullify(($detail_key == 'postcode') ? format_postcode($data[$detail_key]) : (($detail_key == 'country') ? strtoupper($data[$detail_key]) : format_name($data[$detail_key])));
            }
        }
        
        $customer_details['country_code'] = (isset($data['country']) && strlen(($country_code = remove_whitespace($data['country']))) === 2) ? $country_code : 'GB';
        $customer_details['country'] = (($valid_country = $this->common->getCountries(NULL, $customer_details['country_code']))) ? strtoupper($valid_country->name) : 'UNITED KINGDOM';
        
        $customer_details['email'] = nullify((isset($data['email']) && ($data['email'] = strtolower(trim($data['email']))) && filter_var($data['email'], FILTER_VALIDATE_EMAIL)) ? $data['email'] : NULL);
        $customer_details['phone'] = nullify((isset($data['phone']) && $data['phone'] = trim($data['phone'])) ? $data['phone'] : NULL);
        $customer_details['mobile'] = nullify((isset($data['mobile']) && $data['mobile'] = trim($data['mobile'])) ? $data['mobile'] : NULL);
        
        if(!$is_retail_customer && $is_trade_request)
        {
            if(!$customer_details['email'])
            {
                return $this->common->setResponseMessage('An email address is required', false);
            }
        }
        
        if($customer_details['email'])
        {
            if($this->checkUsedEmail($customer_details['email'], $customer_id))
            {
                return $this->common->setResponseMessage('This email address is already taken by another account', false);
            }
        }
        
        if(isset($data['cc_email']))
        {
            $customer_details['cc_email'] = (($cc_email = strtolower(trim($data['cc_email']))) && filter_var($cc_email, FILTER_VALIDATE_EMAIL)) ? $cc_email : NULL;
        }
        else
        {
            unset($customer_details['cc_email']);
        }
        
        if(isset($data['sage_account_code']))
        {
            $customer_details['sage_account_code'] = nullify(strtoupper(trim(substr($data['sage_account_code'], 0, 8))));
        }
        else
        {
            unset($customer_details['sage_account_code']);
        }
        
        $customer_details['referral'] = (isset($data['referral']) && ($referral = (int) $data['referral']) > 0) ? $referral : 1;
        
        if(!$is_retail_customer && !$is_update && isset($data['password']))
        {
            if(!strlen(trim($data['password'])))
            {
                return $this->common->setResponseMessage('A custom password is required', false);
            }
            
            if(isset($data['confirm_password']))
            {
                if($data['password'] != $data['confirm_password'])
                {
                    return $this->common->setResponseMessage('The passwords do not match', false);
                }
            }
            
            $customer_details['password'] = hash_password($data['password']);
        }
        else
        {
            unset($customer_details['password']);
        }
        
        // Admin panel on custom change of password
        if(!$is_retail_customer && $is_update && isset($data['new_password']))
        {
            if(trim(strlen($data['new_password'])))
            {
                $customer_details['password'] = hash_password($data['new_password']);
                $custom_password = $customer_details['change_password'] = true;
            }
        }
        
        if(!isset($custom_password))
        {
            unset($customer_details['change_password']);
        }
        
        // Just to keep the old bakery website customer IDs
        if(isset($data['legacy']) && ($legacy_customer_id = (int) $data['legacy']) > 0)
        {
            $customer_details['legacy_customer_id'] = $legacy_customer_id;
        }
        
        if(isset($data['discount_amount']))
        {
            $discount_amount = (($discount_amount = (float) $data['discount_amount']) >= 0) ? $discount_amount : 0;
            $discount_type = ($discount_amount && isset($data['discount_type']) && in_array($data['discount_type'], array(DISCOUNT_PERCENTAGE, DISCOUNT_AMOUNT))) ? $data['discount_type'] : NULL;
            
            $customer_details['default_discount'] = $discount_amount;
            $customer_details['default_discount_type'] = $discount_type;
        }
        else
        {
            unset($customer_details['default_discount'], $customer_details['default_discount_type']);
        }
        
        if(isset($data['preferred_delivery_time']) && preg_match('/^(6|7|8|9|10|11|12)\:(00|30)$/', $data['preferred_delivery_time']))
        {
            $customer_details['preferred_delivery_time'] = $data['preferred_delivery_time'];
        }
        else
        {
            unset($customer_details['preferred_delivery_time']);
        }
        
        if(isset($data['no_charges']))
        {
            $customer_details['no_charges'] = (bool) $data['no_charges'];
        }
        else
        {
            unset($customer_details['no_charges']);
        }
        
        $subscribe_newsletter = (isset($data['subscribe_mail_list']));
        
        $customer_details['subscribed_newsletter'] = $subscribe_newsletter;
        $customer_details['unsubscribe_key'] = ($subscribe_newsletter) ? generate_unique_identifier(18, 'customers', 'unsubscribe_key') : NULL;
        
        if(required_fields_filled($customer_details['first_name']) || required_fields_filled($customer_details['business']))
        {
            if(!$is_retail_customer && !required_fields_filled($customer_details['address_1'], $customer_details['postcode']))
            {
                return $this->common->setResponseMessage('The customer address is required', false);
            }
            
            // Remove data before executing query
            unset($customer_details['legacy_customer_id'], $customer_details['confirm_password'], $customer_details['username']);
            
            if(($geocoded_address = $this->common->geocodeAddress(implode(' ', array_filter(array($customer_details['address_1'], $customer_details['address_2'], $customer_details['town'], $customer_details['county'], $customer_details['postcode'], $customer_details['country']))))))
            {
                $customer_details['latitude'] = $geocoded_address->lat;
                $customer_details['longitude'] = $geocoded_address->lng;
            }
            
            if($is_trade_request)
            {
                $trade_request_key = generate_unique_identifier(32, 'customers', 'trade_request');
                $customer_details['trade_request'] = $trade_request_key;
            }
            else
            {
                unset($customer_details['trade_request']);
            }
            
            if($is_update)
            {
                unset($customer_details['registered_date']);
            }
            else
            {
                $customer_details['registered_date'] = time();
            }
            
            $sql = (!$is_update) ? $this->db->insert_string('customers', $customer_details) : $this->db->update_string('customers', $customer_details, "customer_id = $customer_id");
            $result = $this->db->query($sql);
            
            if($this->db->affected_rows() || ($is_update && $result))
            {
                $saved_customer_id = (int) ($is_update) ? $customer_id : $this->db->insert_id();
                
                if($is_update)
                {
                    $this->saveCustomerAddress(NULL, array_merge($customer_details, array('customer_id' => $saved_customer_id)), true, NULL, true);
                }
                else
                {
                    if($save_address_details)
                    {
                        foreach(array('invoice', 'delivery') as $address_type)
                        {
                            $this->saveCustomerAddress($address_type, array_merge($customer_details, array('customer_id' => $saved_customer_id)), true);
                        }
                    }
                }
                
                if($is_update && isset($data['apply_discount'], $customer_details['default_discount']))
                {
                    $this->db->query($this->db->update_string('orders', array
                    (
                        'discount'      => $customer_details['default_discount'],
                        'discount_type' => $customer_details['default_discount_type']
                    ), "`customer_id` = $saved_customer_id")); 
                            
                    //), "`customer_id` = $customer_id AND `delivery_date` >= " . strtotime('2nd july 2014 00:00:00')));
                }
                
                if($is_trade_request)
                {
                    $this->common->sendEmail(array
                    (
                        'email'     => 'gareth@lymingecountrybakery.co.uk',
                        'subject'   => 'A trade request has been submitted recently',
                        'variables' => array
                        (
                            'trader_info'   => $this->get($saved_customer_id)
                        )
                    ), 'trade-request');
                }
                
                if(isset($custom_password, $customer_details['email']))
                {
                    $this->common->sendEmail(array
                    (
                        'email'     => $customer_details['email'],
                        'subject'   => 'Your account password has been temporarily reset to a new one',
                        'variables' => array
                        (
                            'temp_password'     => $data['new_password'],
                            'customer_info'     => $this->get($saved_customer_id)
                        )
                    ), 'reset-password');
                }
                
                $this->common->setResponseMessage('The customer was successfully saved', true);
                
                return $saved_customer_id;
            }
            
            return $this->common->setResponseMessage('There was a problem trying to update the customer details', false);
        }
        
        return $this->common->setResponseMessage('The customer name/business is required', false);
    }
    
    public function changeAccountPassword($customer_id = 0, $old_password = NULL, $new_password = NULL, $confirm_password = NULL)
    {
        if((($customer_id = (int) $customer_id) > 0 && ($customer_info = $this->get($customer_id))))
        {
            if(!strlen(($old_password = trim($old_password)))) return $this->common->setResponseMessage('Your old password is required');
            if(!(strlen(($new_password = trim($new_password))) && strlen(($confirm_password = trim($confirm_password))))) return $this->common->setResponseMessage('Your new password fields are required');
            
            if(!verify_password($old_password, $customer_info->password))
            {
                return $this->common->setResponseMessage('Your old password was not correct');
            }
            
            if($new_password !== $confirm_password)
            {
                return $this->common->setResponseMessage('The passwords do not match');
            }
            
            $new_password = hash_password($new_password);
            
            $this->db->query($this->db->update_string('customers', array('password' => $new_password, 'change_password' => false), "`customer_id` = $customer_id"));
            
            if($this->db->affected_rows() === 1)
            {
                return $this->common->setResponseMessage('Your account password was successfully changed', true);
            }
            
            return $this->common->setResponseMessage('Your account password could not be changed at this time - try again');
        }
        
        return false;
    }
    
    public function saveCustomerAddress($type = NULL, $data = array(), $is_default = false, $address_id = 0, $update_default = false)
    {
        //decode_post_data($data);
     
        $address_id = (($address_id = (int) $address_id) > 0) ? $address_id : 0;
        //$customer_id = (($customer_id = (int) $customer_id) > 0) ? $customer_id : 0;
        
        $is_address_update = ($address_id > 0);
        $is_default_update = (bool) $update_default;
        
        $type = strtolower($type);
        $address_types = array('invoice' => ADDRESS_TYPE_INVOICE, 'delivery' => ADDRESS_TYPE_DELIVERY);
        
        if(!$is_default_update && (!isset($address_types[$type]) || empty($data)))
        {
            return false;
        }
        
        $address_details = array
        (
            'customer_id'   => NULL,
            'address_type'  => NULL,
            'first_name'    => NULL, 
            'last_name'     => NULL, 
            'business'      => NULL,
            'address_1'     => NULL, 
            'address_2'     => NULL, 
            'address_3'     => NULL, 
            'town'          => NULL, 
            'county'        => NULL, 
            'postcode'      => NULL,
            'country'       => NULL,
            'country_code'  => NULL,
            'latitude'      => NULL,
            'longitude'     => NULL,
            'email'         => NULL,
            'phone'         => NULL,
            'mobile'        => NULL,
            'is_default'    => NULL
        );
        
        foreach(array_keys($address_details) as $detail_key)
        {
            if(isset($data[$detail_key]))
            {
                $address_details[$detail_key] = nullify(($detail_key == 'postcode') ? format_postcode($data[$detail_key]) : ((in_array($detail_key, array('email', 'phone', 'mobile'))) ? strtolower(trim($data[$detail_key])) : (($detail_key == 'country') ? strtoupper(trim($data[$detail_key])) : format_name($data[$detail_key]))));
            }
            
            $address_details['email'] = (filter_var($address_details['email'], FILTER_VALIDATE_EMAIL)) ? $address_details['email'] : NULL;
        }
        
        $address_details['country_code'] = (isset($address_details['country']) && strlen(($country_code = remove_whitespace($address_details['country']))) === 2) ? $country_code : 'GB';
        $address_details['country'] = (($valid_country = $this->common->getCountries(NULL, $address_details['country_code']))) ? strtoupper($valid_country->name) : 'UNITED KINGDOM';
        
        if(($customer_id = (int) $address_details['customer_id']) > 0)
        {
            if($type) $address_details['address_type'] = $address_types[$type];
            $address_details['is_default'] = ($is_default);
            
            if($is_address_update && $is_default)
            {
                $this->db->query($this->db->update_string('customer_addresses', array('is_default' => 0), "`is_default` = 1 AND `customer_id` = $customer_id"));
            }
            
            if(($geocoded_address = $this->common->geocodeAddress(implode(' ', array_filter(array($address_details['address_1'], $address_details['address_2'], $address_details['town'], $address_details['county'], $address_details['postcode'], $address_details['country']))))))
            {
                $address_details['latitude'] = $geocoded_address->lat;
                $address_details['longitude'] = $geocoded_address->lng;
            }
            
            if($is_default_update || $is_address_update)
            {
                if($is_default_update)
                {
                    unset($address_details['address_type'], $address_details['is_default']);
                }
                
                $sql_where = ($is_default_update) ? "`is_default` = 1 AND `customer_id` = $customer_id" : "`address_id` = $address_id";
                $sql = $this->db->update_string('customer_addresses', $address_details, $sql_where);
            }
            else
            {
                $sql = $this->db->insert_string('customer_addresses', $address_details);
            }
            
            $result = $this->db->query($sql);
            
            if($this->db->affected_rows())
            {
                return ($is_address_update) ? (int) $address_id : (($is_default_update) ? true : (int) $this->db->insert_id());
            }
        }
        
        return false;
    }
    
    public function checkUsedEmail($email = '', $exclude_customer_id = 0)
    {
        if(filter_var($email, FILTER_VALIDATE_EMAIL))
        {
            $sql = "SELECT 1
                    FROM `customers`
                    WHERE `is_deleted` = 0
                    AND `email` = ?";
            
            if(($exclude_customer_id = (int) $exclude_customer_id) > 0)
            {
                $sql .= " AND `customer_id` <> $exclude_customer_id ";
            }
            
            $sql .= " LIMIT 1";
            
            return ($this->db->query($sql, array($email))->num_rows() === 1);
        }

return false;
    }
    
    public function getCustomerExistingAddress($customer_id = 0, $address_id = 0)
    {
        if(($customer_id = (int) $customer_id) > 0 && ($address_id = (int) $address_id) > 0)
        {
            $sql = "SELECT *
                    FROM `customer_addresses`
                    WHERE `customer_id` = $customer_id 
                    AND `address_id` = $address_id
                    LIMIT 1";
                
            $result = $this->db->query($sql);

if($result->num_rows())
            {
                return $result->row();
            }
        }
        
        return false;
    }
    
    public function getCustomerAddresses($customer_id = 0, $default_only = false)
    {
        if(($customer_id = (int) $customer_id) > 0)
        {
            $sql = "SELECT `ca`.*
                    FROM `customer_addresses` `ca`
                    WHERE `ca`.`customer_id` = $customer_id ";
                
            $sql .= ($default_only) ? " AND `ca`.`is_default` = 1 " : '';
                    
            //$sql .= " GROUP BY `ca`.`address_type`";
            
            $result = $this->db->query($sql);

if($result->num_rows())
            {
                $rows = $result->result();
                $result->free_result();
                
                $addresses = array();
                
                foreach($rows as $i => $row)
                {
                    $address_key = (!$default_only) ? $i : (($row->address_type == ADDRESS_TYPE_DELIVERY) ? 'delivery' : 'invoice');
                    
                    $address_details = array
                    (
                        'id'            => $row->address_id,
                        'first_name'    => $row->first_name,
                        'last_name'     => $row->last_name,
                        'business'      => $row->business,
                        'address_1'     => $row->address_1,
                        'address_2'     => $row->address_2,
                        'address_3'     => $row->address_3,
                        'town'          => $row->town,
                        'county'        => $row->county,
                        'postcode'      => $row->postcode,
                        'country'       => $row->country,
                        'email'         => $row->email,
                        'phone'         => $row->phone,
                        'mobile'        => $row->mobile,
                        'address_html'  => format_customer_details(array($row->first_name, $row->last_name, $row->business), array($row->address_1, $row->address_2, $row->address_3, $row->town, $row->county, $row->postcode, $row->country, $row->country_code), array($row->email, $row->phone, $row->mobile)),
                        //'address_html'  => format_name($row->first_name, $row->last_name) . '<br />' . format_address($row->address_1, $row->address_2, $row->address_3, $row->town, $row->county, $row->postcode) . (($contact_details = array_filter(array($row->email, $row->phone, $row->mobile))) ? '<br />' . implode(' / ', $contact_details) : '')
                    );
                    
                    if(is_numeric($address_key))
                    {
                        $addresses[($row->address_type == ADDRESS_TYPE_DELIVERY) ? 'delivery' : 'invoice'][$address_key] = $address_details;
                    }
                    else
                    {
                        $addresses[$address_key] = $address_details;
                    }
                }
                
                if(!$default_only)
                {
                    if(isset($addresses['delivery']))
                    {
                        $addresses['delivery'] = array_values($addresses['delivery']);
                    }
                    if(isset($addresses['invoice']))
                    {
                        $addresses['invoice'] = array_values($addresses['invoice']);
                    }
                }
                
                return $addresses;
            }
        }
        
        return array();
    }
    
    public function addressMatches($address_one = '', $address_two = '')
    {
        $address_one = (is_array($address_one)) ? format_address($address_one) : $address_one;
        $address_two = (is_array($address_two)) ? format_address($address_two) : $address_two;
        
        if(strlen(trim($address_one)) === 0 || strlen(trim($address_two)) === 0)
        {
            return NULL;
        }
        
        if(metaphone($address_one) === metaphone($address_two))
        {
            return true;
        }
        
        return false;
    }
    
    public function isDefaultAddress($customer_id = 0, $address_details = array())
    {
        if(($customer_id = (int) $customer_id) > 0)
        {
            $sql = "SELECT 1
                    FROM `customers`
                    WHERE `customer_id` = ?
                    AND `first_name` = ?
                    AND `last_name` = ?
                    AND `business` = ?
                    AND `address_1` = ?
                    AND `address_2` = ?
                    AND `address_3` = ?
                    AND `town` = ?
                    AND `county` = ?
                    AND `postcode` = ?
                    AND `country` = ?
                    AND `email` = ?
                    AND `phone` = ?
                    AND `mobile` = ?
                    LIMIT 1";
            
            $result = $this->db->query($sql, array
            (
                $customer_id,
                $address_details['first_name'],
                $address_details['last_name'],
                $address_details['business'],
                $address_details['address_1'],
                $address_details['address_2'],
                $address_details['address_3'],
                $address_details['town'],
                $address_details['county'],
                $address_details['postcode'],
                $address_details['country'],
                $address_details['email'],
                $address_details['phone'],
                $address_details['mobile']
            ));
            
            return ($result->num_rows() === 1);
        }
        
        return false;
    }
    
    public function archive($customer_id = 0, $restore = false)
    {
        if(($customer_id = (int) $customer_id) > 0)
        {
            $this->db->query($this->db->update_string('customers', array('is_archived' => !((bool) $restore)), "`customer_id` = $customer_id"));
            return ($this->db->affected_rows() === 1);
        }
        
        return false;
    }
    
    // Potentially a dangerous function....
    public function merge($customer_id = 0, $to_customer_id = 0)
    {
        if(($customer_id = (int) $customer_id) > 0 && ($to_customer_id = (int) $to_customer_id) > 0 && ($customer_info = $this->get($customer_id)) && ($to_customer_info = $this->get($to_customer_id)))
        {
            $this->db->query($this->db->update_string('orders',                 array('customer_id' => $to_customer_id),                        "`customer_id` = $customer_id"));
            $this->db->query($this->db->update_string('customer_addresses',     array('customer_id' => $to_customer_id, 'is_default' => false), "`customer_id` = $customer_id"));
            $this->db->query($this->db->update_string('customer_fixed_prices',  array('customer_id' => $to_customer_id),                        "`customer_id` = $customer_id"));
            $this->db->query($this->db->update_string('customer_notes',         array('customer_id' => $to_customer_id),                        "`customer_id` = $customer_id"));
            $this->db->query($this->db->update_string('customer_payments',      array('customer_id' => $to_customer_id),                        "`customer_id` = $customer_id"));
            
            $this->db->query("DELETE FROM `customers` WHERE `customer_id` = $customer_id");
            
            return true;
        }
        
        return false;
    }
    
    public function delete($customer_id = 0)
    {
        if(($customer_id = (int) $customer_id) > 0)
        {
            $this->db->query($this->db->update_string('customers', array('is_deleted' => 1), "`customer_id` = $customer_id"));
            return ($this->db->affected_rows() === 1);
        }
        
        return false;
    }
    
    public function sendTradesRequest($data = array())
    {
        $trade_name = (isset($data['trade_name'])) ? format_name(nullify(format_whitespace($data['trade_name']))) : NULL;
        
        if($trade_name == '')
        {
            return $this->common->setResponseMessage('The trading name is required');
        }
        
        foreach(array('address_1', 'address_2', 'address_3', 'town', 'county', 'postcode', 'email', 'phone') as $input_key)
        {
            $$input_key = (isset($data[$input_key])) ? nullify(format_whitespace($data[$input_key])) : NULL;
            
            if(!in_array($input_key, array('email', 'phone')))
            {
                $$input_key = format_address($$input_key);
            }
        }
        
        if(count(array_filter(array($address_1, $postcode))) !== 2)
        {
            return $this->common->setResponseMessage('Your address is required');
        }
        
        if($email == '' || !filter_var($email, FILTER_VALIDATE_EMAIL) || $phone == '')
        {
            return $this->common->setResponseMessage('Please enter a valid email and a telephone number');
        }
        
        $postcode = format_postcode($postcode);
        $email = strtolower($email);
        
        $send_to_email = ($this->config->item('trades_request_email')) ? $this->config->item('trades_request_email') : $this->config->item('company_email');
        
        $email_send = $this->common->sendEmail(array
        (
            'email'     => $send_to_email,
            'subject'   => $this->config->item('company_name') . ' trades request',
            'reply'     => $email,
            'variables' => array
            (
                'name'      => $trade_name,
                'address'   => lined_address($address_1, $address_2, $address_3, $town, $county, $postcode),
                'email'     => $email,
                'phone'     => $phone
            )
        ), 'trade-request');    
        
        if($email_send)
        {
            return $this->common->setResponseMessage('Your request was successfully sent', true);
        }
        
        return $this->common->setResponseMessage('Your request could not be sent at this time - please try again');
    }
    
    public function tradeRequestDecision($customer_id = 0, $decision = NULL, $send_confirmation_email = true)
    {
        if(($customer_id = (int) $customer_id) > 0 && ($decision === true || $decision === false) && ($customer_info = $this->get($customer_id)) && isset($customer_info->trade_request))
        {
            if($decision)
            {
                $this->db->query($this->db->update_string('customers', array('trade_request' => NULL), "`customer_id` = $customer_id"));
            }
            else
            {
                $this->db->query("DELETE FROM `customers` WHERE `customer_id` = $customer_id");
                $this->db->query("DELETE FROM `customer_addresses` WHERE `customer_id` = $customer_id");
            }
            
            if($this->db->affected_rows())
            {
                if($send_confirmation_email)
                {
                    $this->common->sendEmail(array
                    (
                        'email'     => $customer_info->email,
                        'subject'   => 'Your trader account request was ' . (($decision) ? 'approved' : 'rejected'),
                        'variables' => array
                        (
                            'name'      => ($customer_info->business) ? $customer_info->business : format_display($customer_info->first_name, $customer_info->last_name),
                            'decision'  => $decision
                        )
                    ), 'trade-decision');
                }
                
                return $this->common->setResponseMessage('The trader was successfully ' . (($decision) ? 'approved' : 'rejected'), true);
            }
            
            return $this->common->setResponseMessage('The trader could not be ' . (($decision) ? 'approved' : 'rejected') . ' at this time - try again', false);
        }
        
        return false;
    }
    
    public function getCustomerNotes($customer_id = 0, $skip = 0, $limit = 30)
    {
        if(($customer_id = (int) $customer_id) > 0)
        {
            $sql = "SELECT `cn`.*,
                           `m`.`first_name` AS `created_by`
                    FROM `customer_notes` `cn`
                    INNER JOIN `members` `m` ON `cn`.`user_id` = `m`.`user_id`
                    WHERE `cn`.`customer_id` = $customer_id
                    AND `cn`.`is_deleted` = 0
                    ORDER BY `cn`.`date` DESC";
            
            $sql .= $this->common->returnOffset($skip, $limit);
            
            $result = $this->db->query($sql);
            
            if($result->num_rows() > 0)
            {
                $rows = $result->result();
                $result->free_result();
                
                return $rows;
            }
        }
        
        return array();
    }
    
    public function saveCustomerNote($data = array(), $note_id = 0)
    {
        $note_id = (($note_id = (int) $note_id) > 0) ? $note_id : 0;
        $is_update = ($note_id > 0);
        
        if(isset($data['note'], $data['customer_id']) && ($customer_id = (int) $data['customer_id']) > 0 && strlen(($note_contents = trim($data['note']))))
        {
            $sql_data = array
            (
                'customer_id'       => $customer_id,
                'user_id'           => $this->users->user_id,
                'date'              => time(),
                'note'              => $note_contents
            );
            
            if($is_update)
            {
                unset($sql_data['date']);
                
                $sql = $this->db->update_string('customer_notes', $sql_data, "`note_id` = $note_id");
            }
            else
            {
                $sql = $this->db->insert_string('customer_notes', $sql_data);
            }
            
            $this->db->query($sql);
            
            if($this->db->affected_rows() || ($is_update && $result))
            {
                $this->common->setResponseMessage('The note was successfully saved', true);
                
                return ($is_update) ? $note_id : $this->db->insert_id();
            }
            
            return $this->common->setResponseMessage('The note could not be saved at this time - try again', false);
        }
        
        return false;
    }
    
    public function deleteCustomerNote($note_id = 0)
    {
        if(($note_id = (int) $note_id) > 0)
        {
            $this->db->query($this->db->update_string('customer_notes', array('is_deleted' => 1), "`note_id` = $note_id"));
            return ($this->db->affected_rows() === 1);
        }
        
        return false;
    }
    
    public function getFixedPrices($customer_id = 0, $product_id = 0)
    {
        if(($customer_id = (int) $customer_id) > 0)
        {
            $sql = "SELECT `f`.`fix_id`,
                           `f`.`customer_id`,
                           `f`.`product_id`,
                           `f`.`price`,
                           `r`.`name`,
                           `r`.`cost` AS `default_price`,
                           (`f`.`price` - `r`.`cost`) AS `price_difference`
                    FROM `customer_fixed_prices` `f`
                    INNER JOIN `products` `r` ON `r`.`product_id` = `f`.`product_id`
                    WHERE `f`.`is_deleted` = 0
                    AND `r`.`is_deleted` = 0
                    AND `f`.`customer_id` = $customer_id ";
            
            if(($product_id = (int) $product_id) > 0)
            {
                $sql .= " AND `f`.`product_id` = $product_id";
            }
            
            $result = $this->db->query($sql);
            
            //preprint($this->db->last_query());
            
            if($result->num_rows())
            {
                if($product_id > 0) return $result->row();
                
                $rows = $result->result();
                
                return $rows;
            }
        }
        
        return array();
    }
    
    public function saveAgreements($customer_id = 0, $data = array())
    {
        if(($customer_id = (int) $customer_id) > 0 && isset($data['agreed_product'], $data['agreed_price']) && is_array($data['agreed_product']) && is_array($data['agreed_price']))
        {
            $agreed_products = array_values(array_unique(array_filter($data['agreed_product'], function($value)
            {
                return (($value = (int) $value) > 0) ? $value : false;
            })));
            
            $agreed_prices = array_values(array_filter($data['agreed_price'], function($value)
            {
                return (($value = (float) $value) > 0) ? $value : false;
            }));
            
            if(count($agreed_products) === count($agreed_prices))
            {
                $this->db->query("DELETE FROM `customer_fixed_prices` WHERE `customer_id` = $customer_id");
                
                $sql_data = array();
                
                foreach($agreed_products as $i => $product_id)
                {
                    if($this->products->getProducts($product_id))
                    {
                        $sql_data[] = array
                        (
                            'customer_id'       => $customer_id,
                            'product_id'         => $product_id,
                            'price'             => $agreed_prices[$i]
                        );
                    }
                }
                
                if(count($sql_data))
                {
                    $this->db->insert_batch('customer_fixed_prices', $sql_data);
                }
                
                return true;
            }
        }
        
        return false;
    }
    
    public function deleteAgreement($agreement_id = 0)
    {
        if(($agreement_id = (int) $agreement_id) > 0)
        {
            $this->db->query($this->db->update_string('customer_fixed_prices', array('is_deleted' => 1), "`fix_id` = $agreement_id"));
            
            return ($this->db->affected_rows() === 1);
        }
        
        return false;
    }
    
    public function getAllPayments($customer_id = 0, $skip = 0, $limit = 30)
    {
        if(($customer_id = (int) $customer_id) > 0)
        {
            $sql = "SELECT *,
                           ROUND(`p`.`order_total` + (`p`.`order_total` * (`p`.`vat` / 100)), 2) AS `order_total_inc_vat`,
                           
                           IF(`p`.`pending_payment`, 3, IF(`p`.`unpaid_order`, 2, 1)) AS `payment_order`
                    FROM
                    (
                        SELECT `p`.*,
                               `o`.`vat`,
                               IF(`o`.`discount_type` = " . DISCOUNT_AMOUNT . ", 
                                    `oi`.`total` - `o`.`discount`,

IF(`o`.`discount_type` = " . DISCOUNT_PERCENTAGE . ",
                                        ROUND(`oi`.`total` - ((`oi`.`total` / 100) * `o`.`discount`), 2),
                                        `oi`.`total`
                                    )
                               ) AS `order_total`
                        FROM
                        (
                            SELECT IF(`op`.`payment_id` IS NULL AND `o`.`payment_type` IN (3, 4), 1, 0) AS `pending_payment`,
                                   IF(`op`.`payment_id` IS NULL AND `o`.`payment_type` NOT IN (3, 4), 1, 0) AS `unpaid_order`,
                                   `op`.`payment_id`,
                                   `o`.`order_id`,
                                   `o`.`order_number`,
                                   `o`.`payment_type` AS `preferred_payment`,
                                   `op`.`type` AS `payment_type`,
                                   `op`.`amount`,
                                   IF(`op`.`payment_id` IS NULL, `o`.`delivery_date`, `op`.`date`) AS `date`,
                                   1 AS `is_order_payment`
                            FROM `orders` `o`
                            LEFT OUTER JOIN `order_payments` `op` ON `op`.`order_id` = `o`.`order_id` AND `op`.`is_deleted` = 0
                            WHERE `o`.`is_deleted` = 0
                            AND `o`.`customer_id` = $customer_id

UNION ALL
                            SELECT 0 AS `pending_payment`,
                                   0 AS `unpaid_order`,
                                   `cp`.`payment_id`,
                                   `cp`.`order_id`,
                                   NULL AS `order_number`,
                                   NULL AS `preferred_payment`,
                                   `cp`.`type` AS `payment_type`,
                                   `cp`.`amount`,
                                   `cp`.`date`,
                                   0 AS `is_order_payment`
                            FROM `customer_payments` `cp`
                            WHERE `cp`.`is_deleted` = 0
                            AND `cp`.`customer_id` = $customer_id
                            GROUP BY `cp`.`payment_id`
                        ) AS `p`

LEFT OUTER JOIN `orders` `o` ON `o`.`order_id` = `p`.`order_id`

LEFT OUTER JOIN
                        (
                            SELECT `oi`.`order_id`,
                                   `oi`.`total_items`,
                                   `oi`.`_total` AS `total`
                            FROM 
                            (
                                SELECT SUM((`oi`.`quantity` * `oi`.`price`) + `oi`.`gift_wrap_cost`) + `o`.`additional_charges` AS `_total`,
                                       COUNT(`oi`.`item_id`) AS `total_items`,
                                       `oi`.`order_id`,
                                       `oi`.`item_id`
                                FROM
                                (
                                    `order_items` `oi`

INNER JOIN `orders` `o` ON `oi`.`order_id` = `o`.`order_id`
                                )
                                WHERE `oi`.`is_deleted` = 0
                                GROUP BY `oi`.`order_id`
                            ) AS `oi` 
                            GROUP BY `oi`.`order_id`
                        ) AS `oi` ON `oi`.`order_id` = `p`.`order_id`
                    ) AS `p`
                    ORDER BY `payment_order` DESC,
                             `p`.`date` DESC";
            
            $sql .= $this->common->returnOffset($skip, $limit);
            
            $result = $this->db->query($sql);
            
            //preprint($this->db->last_query());
            
            if($result->num_rows())
            {
                $rows = $result->result();
                $result->free_result();
                
                return $rows;
            }
        }
        
        return array();
    }
    
    public function addPayment($customer_id = 0, $payment_type = PAYMENT_TYPE_CASH, $amount = 0, $date_paid = NULL, $order_id = NULL)
    {
        if(($customer_id = (int) $customer_id) > 0 && ($amount = (float) $amount) > 0 && ($customer_info = $this->get($customer_id)))
        {
            if(!in_array($payment_type, array(PAYMENT_TYPE_CASH, PAYMENT_TYPE_BACS, PAYMENT_TYPE_CREDIT_CARD, PAYMENT_TYPE_CHEQUE)))
            {
                $payment_type = PAYMENT_TYPE_CASH;
            }
            
            if(($order_id = (int) $order_id) <= 0 || !$this->orders->get($order_id))
            {
                $order_id = NULL;
            }
            
            $date_paid = (($date_paid = strtotime($date_paid . ' 00:00:00')) !== false) ? $date_paid : NULL;
            
            if($date_paid !== NULL && $date_paid >= strtotime('tomorrow 00:00:00'))
            {
                return false;
            }
            
            $date_paid = ($date_paid === NULL) ? strtotime('00:00:00') : $date_paid;
            
            $this->db->query($this->db->insert_string('customer_payments', array
            (
                'customer_id'   => $customer_id,
                'order_id'      => $order_id,
                'type'          => $payment_type,
                'amount'        => $amount,
                'date'          => $date_paid
            )));

return ($this->db->affected_rows() === 1);
        }
    }
    
    public function removePayment($payment_id = 0)
    {
        if(($payment_id = (int) $payment_id) > 0)
        {
            $this->db->query($this->db->update_string('customer_payments', array('is_deleted' => 1), "`payment_id` = $payment_id"));
            return ($this->db->affected_rows() === 1);
        }
        
        return false;
    }
    
    public function comboBox($name = '', $selected = array(), $attributes = '')
    {
        $customers = $this->get(NULL, NULL, -1);
        $options = array();
        
        foreach($customers as $customer_info)
        {
            $options[$customer_info->customer_id] = ($customer_info->business) ? format_name($customer_info->business) : $customer_info->name_order;
        }
                
        $options = array('' => '---') + $options;
        
        return form_dropdown($name, $options, $selected, $attributes);
    }
}

Cancel