PHP File Manager
Editing File: Orders.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 Orders extends CI_Model { private $_created_order_ids = array(); private $_next_row_id; public function get($order_id = 0, $customer_id = 0, $filters = array(), $skip = 0, $limit = 30) { //$debug = debug_backtrace(); //var_dump($debug[0]); $this->_next_row_id = NULL; $filters = array_merge(array ( 'number' => NULL, 'customer_details' => NULL, 'status' => NULL, 'completed' => NULL, 'unpaid' => NULL, 'total' => NULL, 'total_margin' => NULL, 'order_date_from' => NULL, 'order_date_to' => NULL, 'delivery_date_from' => NULL, 'delivery_date_to' => NULL, 'dispatched_date_from' => NULL, 'dispatched_date_to' => NULL, 'delivery_address' => NULL, 'ref_order_number' => NULL, 'recurring' => NULL, 'recurring_id' => NULL, 'skip_finished_recurs' => NULL, 'retail_orders' => NULL, 'delivery_type' => NULL, 'to_review' => NULL, 'gift_wrapped' => NULL, 'discount_id' => NULL, 'ready_to_dispatch' => NULL, 'dispatchment_status' => NULL, 'has_dispatched' => NULL, 'tracking_number' => NULL, 'has_tracking_number' => NULL, 'priority_order' => true, 'order_by' => NULL, 'query' => NULL, 'after' => NULL, 'exclude_review' => NULL, 'exclude_pending' => false, 'allow_deleted' => false, 'sum' => NULL, 'sum_group' => NULL, 'count' => false ), (array) $filters); if(wayne()) { //preprint($filters); } $today = strtotime('00:00:00'); $return_flat_row = false; $allow_deleted_orders = (bool) $filters['allow_deleted']; $sql = ""; if($filters['sum'] !== NULL) { $sql .= "SELECT SUM("; switch($filters['sum']) { default: case 'paid': $sql .= "`o`.`total_amount_paid`"; break; } $sql .= ") AS `summed`, DATE_FORMAT(FROM_UNIXTIME(`o`.`ordered_date`), '%d-%m-%Y') AS `ordered_datestamp` FROM ("; } $sql .= "SELECT *, (`o`.`total_inc_vat` - `o`.`__total_paid`) AS `total_remaining`, `o`.`__total_paid` AS `total_amount_paid`, IF((`o`.`total_inc_vat` - `o`.`__total_paid`) <= 0, 1, 0) AS `order_fully_paid`, IF(`o`.`__total_paid` > 0 AND `o`.`__total_paid` < `o`.`total_inc_vat`, 1, 0) AS `order_partially_paid`, IF(`o`.`recurring_id` IS NOT NULL, 1, 0) AS `is_recurring_order` FROM ( SELECT *, ROUND(((`o`.`total` / 100) * `o`.`vat`), 2) AS `total_vat`, ROUND((`o`.`total` + `o`.`delivery_costs`), 2) AS `total_inc_vat`, # ROUND((`o`.`total` * (`o`.`vat` / 100)), 2) AS `total_vat`, # ROUND((`o`.`total` + (`o`.`total` * (`o`.`vat` / 100))) + `o`.`delivery_costs`, 2) AS `total_inc_vat`, ROUND(`o`.`real_total` + (`o`.`real_total` * (`o`.`vat` / 100)), 2) AS `total_before_discounts`, IF(`o`.`_total_paid` > 0, `o`.`_total_paid`, 0) AS `__total_paid`, IF(`o`.`order_progress` = 100, 1, 0) AS `is_completed`, IF(`o`.`order_progress` = 100 AND `o`.`delivery_date` IS NOT NULL, 1, 0) AS `can_dispatch`, IF(`o`.`has_dispatched` = 1, " . DISPATCHMENT_GONE . ", IF(`o`.`order_progress` < 100 AND `o`.`_dispatchment_status` = " . DISPATCHMENT_PROCESSING . ", `o`.`_dispatchment_status`, IF(`o`.`order_progress` = 100 AND `o`.`_dispatchment_status` = " . DISPATCHMENT_PROCESSING . ", " . DISPATCHMENT_READY . ", `o`.`_dispatchment_status`))) AS `dispatchment_status` FROM ( SELECT `o`.`order_id`, `o`.`order_number`, `o`.`customer_id`, `o`.`ordered_date`, `o`.`delivery_date`, `o`.`auto_delivery_date`, `o`.`discount`, `o`.`discount_type`, `o`.`discount_code`, `dc`.`code` AS `discount_identifier`, `o`.`delivery_type`, IF(`o`.`delivery_cost` IS NOT NULL, `o`.`delivery_cost`, `del`.`costs`) AS `delivery_costs`, `del`.`label` AS `delivery_type_label`, `del`.`delivery_days`, ROUND((`o`.`delivery_date` - $today) / 60 / 60 / 24) AS `delivery_due`, `o`.`delivery_notes`, `o`.`internal_notes`, IF(`oi`.`gift_wrapped` = 1, 1, 0) AS `gift_wrapped`, `oi`.`total_gift_charges`, `o`.`gift_note`, `o`.`alt_address`, `o`.`invoice_address`, `o`.`payment_type`, `pt`.`label` AS `payment_type_label`, `o`.`additional_charges`, `o`.`vat`, `o`.`priority`, `o`.`confirm_email_sent`, `o`.`dispatchment_status` AS `_dispatchment_status`, `o`.`has_dispatched`, `o`.`dispatched_date`, `o`.`dispatch_email_sent`, `o`.`tracking_number`, `o`.`courier_service`, `o`.`courier_pickup_date`, `o`.`courier_arrival_date`, IF(`o`.`tracking_number` IS NOT NULL, IF(`o`.`delivery_date` > $today, 1, 2), 0) AS `dispatch_step`, `o`.`invoice_last_sent`, `o`.`ref_order_number`, `o`.`duplicate_order_id`, `ro`.`recurring_id`, `ro`.`legacy_id` AS `legacy_recurring_id`, `ro`.`repeat_date`, `ro`.`repeat_day`, `ro`.`repeat_week`, `ro`.`repeat_month`, `ro`.`repeat_from`, `ro`.`repeat_till`, `ro`.`last_run`, `ro`.`next_run`, `ro`.`is_finished`, `o`.`recurred_from`, `o`.`recur_identifier`, `o`.`is_deleted`, `oi`.`total` AS `real_total`, `op`.`_total_paid`, 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 `total`, IF(`o`.`discount_type` = " . DISCOUNT_AMOUNT . ", `o`.`discount`, IF(`o`.`discount_type` = " . DISCOUNT_PERCENTAGE . ", ROUND(((`oi`.`total` / 100) * `o`.`discount`), 2), 0 ) ) AS `discounted_amount`, `oi`.`total_items`, `oi`.`total_quantities`, IF(`o`.`alt_address` IS NOT NULL AND `ca`.`address_type` = " . ADDRESS_TYPE_DELIVERY . ", `ca`.`first_name`, `c`.`first_name`) AS `delivery_first_name`, IF(`o`.`alt_address` IS NOT NULL AND `ca`.`address_type` = " . ADDRESS_TYPE_DELIVERY . ", `ca`.`last_name`, `c`.`last_name`) AS `delivery_last_name`, IF(`o`.`alt_address` IS NOT NULL AND `ca`.`address_type` = " . ADDRESS_TYPE_DELIVERY . ", `ca`.`business`, `c`.`business`) AS `delivery_business`, IF(`o`.`alt_address` IS NOT NULL AND `ca`.`address_type` = " . ADDRESS_TYPE_DELIVERY . ", `ca`.`address_1`, `c`.`address_1`) AS `delivery_address_1`, IF(`o`.`alt_address` IS NOT NULL AND `ca`.`address_type` = " . ADDRESS_TYPE_DELIVERY . ", `ca`.`address_2`, `c`.`address_2`) AS `delivery_address_2`, IF(`o`.`alt_address` IS NOT NULL AND `ca`.`address_type` = " . ADDRESS_TYPE_DELIVERY . ", `ca`.`address_3`, `c`.`address_3`) AS `delivery_address_3`, IF(`o`.`alt_address` IS NOT NULL AND `ca`.`address_type` = " . ADDRESS_TYPE_DELIVERY . ", `ca`.`town`, `c`.`town`) AS `delivery_town`, IF(`o`.`alt_address` IS NOT NULL AND `ca`.`address_type` = " . ADDRESS_TYPE_DELIVERY . ", `ca`.`county`, `c`.`county`) AS `delivery_county`, IF(`o`.`alt_address` IS NOT NULL AND `ca`.`address_type` = " . ADDRESS_TYPE_DELIVERY . ", `ca`.`postcode`, `c`.`postcode`) AS `delivery_postcode`, IF(`o`.`alt_address` IS NOT NULL AND `ca`.`address_type` = " . ADDRESS_TYPE_DELIVERY . ", `ca`.`country`, `c`.`country`) AS `delivery_country`, IF(`o`.`alt_address` IS NOT NULL AND `ca`.`address_type` = " . ADDRESS_TYPE_DELIVERY . ", `ca`.`country_code`, `c`.`country_code`) AS `delivery_country_code`, IF(`o`.`alt_address` IS NOT NULL AND `ca`.`address_type` = " . ADDRESS_TYPE_DELIVERY . ", `ca`.`email`, `c`.`email`) AS `delivery_email`, IF(`o`.`alt_address` IS NOT NULL AND `ca`.`address_type` = " . ADDRESS_TYPE_DELIVERY . ", `ca`.`phone`, `c`.`phone`) AS `delivery_phone`, IF(`o`.`alt_address` IS NOT NULL AND `ca`.`address_type` = " . ADDRESS_TYPE_DELIVERY . ", `ca`.`mobile`, `c`.`mobile`) AS `delivery_mobile`, IF(`o`.`alt_address` IS NOT NULL AND `ca`.`address_type` = " . ADDRESS_TYPE_DELIVERY . ", `ca`.`latitude`, `c`.`latitude`) AS `delivery_latitude`, IF(`o`.`alt_address` IS NOT NULL AND `ca`.`address_type` = " . ADDRESS_TYPE_DELIVERY . ", `ca`.`longitude`, `c`.`longitude`) AS `delivery_longitude`, IF(`o`.`invoice_address` IS NOT NULL AND `ca2`.`address_type` = " . ADDRESS_TYPE_INVOICE . ", `ca2`.`first_name`, `c`.`first_name`) AS `invoice_first_name`, IF(`o`.`invoice_address` IS NOT NULL AND `ca2`.`address_type` = " . ADDRESS_TYPE_INVOICE . ", `ca2`.`last_name`, `c`.`last_name`) AS `invoice_last_name`, IF(`o`.`invoice_address` IS NOT NULL AND `ca2`.`address_type` = " . ADDRESS_TYPE_INVOICE . ", `ca2`.`business`, `c`.`business`) AS `invoice_business`, IF(`o`.`invoice_address` IS NOT NULL AND `ca2`.`address_type` = " . ADDRESS_TYPE_INVOICE . ", `ca2`.`address_1`, `c`.`address_1`) AS `invoice_address_1`, IF(`o`.`invoice_address` IS NOT NULL AND `ca2`.`address_type` = " . ADDRESS_TYPE_INVOICE . ", `ca2`.`address_2`, `c`.`address_2`) AS `invoice_address_2`, IF(`o`.`invoice_address` IS NOT NULL AND `ca2`.`address_type` = " . ADDRESS_TYPE_INVOICE . ", `ca2`.`address_3`, `c`.`address_3`) AS `invoice_address_3`, IF(`o`.`invoice_address` IS NOT NULL AND `ca2`.`address_type` = " . ADDRESS_TYPE_INVOICE . ", `ca2`.`town`, `c`.`town`) AS `invoice_town`, IF(`o`.`invoice_address` IS NOT NULL AND `ca2`.`address_type` = " . ADDRESS_TYPE_INVOICE . ", `ca2`.`county`, `c`.`county`) AS `invoice_county`, IF(`o`.`invoice_address` IS NOT NULL AND `ca2`.`address_type` = " . ADDRESS_TYPE_INVOICE . ", `ca2`.`postcode`, `c`.`postcode`) AS `invoice_postcode`, IF(`o`.`invoice_address` IS NOT NULL AND `ca`.`address_type` = " . ADDRESS_TYPE_INVOICE . ", `ca2`.`country`, `c`.`country`) AS `invoice_country`, IF(`o`.`invoice_address` IS NOT NULL AND `ca`.`address_type` = " . ADDRESS_TYPE_INVOICE . ", `ca2`.`country_code`, `c`.`country_code`) AS `invoice_country_code`, IF(`o`.`invoice_address` IS NOT NULL AND `ca2`.`address_type` = " . ADDRESS_TYPE_INVOICE . ", `ca2`.`email`, `c`.`email`) AS `invoice_email`, IF(`o`.`invoice_address` IS NOT NULL AND `ca2`.`address_type` = " . ADDRESS_TYPE_INVOICE . ", `ca2`.`phone`, `c`.`phone`) AS `invoice_phone`, IF(`o`.`invoice_address` IS NOT NULL AND `ca2`.`address_type` = " . ADDRESS_TYPE_INVOICE . ", `ca2`.`mobile`, `c`.`mobile`) AS `invoice_mobile`, IF(`o`.`invoice_address` IS NOT NULL AND `ca`.`address_type` = " . ADDRESS_TYPE_INVOICE . ", `ca`.`latitude`, `c`.`latitude`) AS `invoice_latitude`, IF(`o`.`invoice_address` IS NOT NULL AND `ca`.`address_type` = " . ADDRESS_TYPE_INVOICE . ", `ca`.`longitude`, `c`.`longitude`) AS `invoice_longitude`, `c`.`cc_email`, `c`.`sage_account_code`, ROUND(IFNULL(((100 / (`oi`.`total_items` * 96)) * `oi2`.`total_progress`), 0), 2) AS `order_progress`, `o`.`retail_order`, `c`.`is_deleted` AS `deleted_customer`, `o`.`referral`, `ref`.`label` AS `referral_type`, `ref`.`friendly_name` AS `friendly_referral_type`, `o`.`needs_review`, `o`.`is_pending` # `o`.`raw_data` FROM ( `orders` `o` INNER JOIN ( SELECT `oi`.`item_id`, `oi`.`order_id`, `oi`.`total_items`, `oi`.`total_quantities`, `oi`.`gift_wrapped`, `oi`.`total_gift_charges`, `oi`.`_total` AS `total` FROM ( SELECT SUM((`oi`.`quantity` * (`oi`.`price` + `oi`.`gift_wrap_cost` + IFNULL(`oio`.`option_charges`, 0))) + `o`.`additional_charges`) AS `_total`, COUNT(`oi`.`item_id`) AS `total_items`, SUM(`oi`.`quantity`) AS `total_quantities`, SUM(`oi`.`gift_wrap_cost`) AS `total_gift_charges`, `oi`.`gift_wrapped`, `oi`.`order_id`, `oi`.`item_id` FROM ( `order_items` `oi` LEFT OUTER JOIN ( SELECT SUM(`extra_charge`) AS `option_charges`, `item_id` FROM `order_item_options` GROUP BY `item_id` ) AS `oio` ON `oio`.`item_id` = `oi`.`item_id` 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` = `o`.`order_id` INNER JOIN ( SELECT SUM(`ois`.`numeric_progress`) AS `total_progress`, `oi`.`order_id` FROM ( `order_items` `oi` INNER JOIN `products` `p` ON `p`.`product_id` = `oi`.`product_id` LEFT OUTER JOIN ( SELECT `ois`.`item_id`, `st`.`item_group`, `st`.`gift_message_only`, MAX(`st`.`numeric_progress`) AS `numeric_progress` FROM `order_item_status` `ois` INNER JOIN `status_types` `st` ON `st`.`status_id` = `ois`.`status` GROUP BY `ois`.`item_id` ORDER BY `st`.`status_order` DESC ) AS `ois` ON `ois`.`item_id` = `oi`.`item_id` ) WHERE `oi`.`is_deleted` = 0 GROUP BY `oi`.`order_id` ) AS `oi2` ON `oi2`.`order_id` = `o`.`order_id` LEFT OUTER JOIN ( SELECT SUM(`amount`) AS `_total_paid`, `order_id` FROM `order_payments` WHERE `is_deleted` = 0 GROUP BY `order_id` ) AS `op` ON `op`.`order_id` = `o`.`order_id` LEFT OUTER JOIN `customers` `c` ON `c`.`customer_id` = `o`.`customer_id` LEFT OUTER JOIN ( SELECT `address_id`, `customer_id`, `address_type`, `first_name`, `last_name`, `business`, `address_1`, `address_2`, `address_3`, `town`, `county`, `postcode`, `country`, `country_code`, `email`, `phone`, `mobile`, `latitude`, `longitude` FROM `customer_addresses` ) AS `ca` ON `ca`.`address_id` = `o`.`alt_address` LEFT OUTER JOIN ( SELECT `address_id`, `customer_id`, `address_type`, `first_name`, `last_name`, `business`, `address_1`, `address_2`, `address_3`, `town`, `county`, `postcode`, `country`, `country_code`, `email`, `phone`, `mobile`, `latitude`, `longitude` FROM `customer_addresses` ) AS `ca2` ON `ca2`.`address_id` = `o`.`invoice_address` LEFT OUTER JOIN ( SELECT IF(`parent_id` IS NULL, `id`, `parent_id`) AS `recurring_id`, `legacy_id`, `order_id`, GROUP_CONCAT(`repeat_date`) AS `repeat_date`, GROUP_CONCAT(`repeat_day`) AS `repeat_day`, GROUP_CONCAT(`repeat_week`) AS `repeat_week`, GROUP_CONCAT(`repeat_month`) AS `repeat_month`, GROUP_CONCAT(`repeat_from`) AS `repeat_from`, GROUP_CONCAT(`repeat_till`) AS `repeat_till`, GROUP_CONCAT(`last_run`) AS `last_run`, GROUP_CONCAT(`next_run`) AS `next_run`, GROUP_CONCAT(`is_finished`) AS `is_finished` FROM `recurring_orders` WHERE `is_deleted` = 0 GROUP BY `order_id` ) AS `ro` ON `ro`.`order_id` = `o`.`order_id` INNER JOIN `payment_types` `pt` ON `pt`.`type_id` = `o`.`payment_type` INNER JOIN `delivery_types` `del` ON `del`.`delivery_id` = `o`.`delivery_type` INNER JOIN `referral_types` `ref` ON `ref`.`refer_id` = `o`.`referral` LEFT OUTER JOIN `discount_codes` `dc` ON `dc`.`code_id` = `o`.`discount_code` ) WHERE " . (($allow_deleted_orders) ? '1 = 1' : ' `o`.`is_deleted` = 0 ') . " "; if($filters['exclude_pending'] === true) { $sql .= " AND `o`.`is_pending` = 0 "; } if(is_bool($filters['exclude_review'])) { $sql .= " AND `o`.`needs_review` = " . ((int) !$filters['exclude_review']) . " "; } if(is_array($order_id) && ($order_ids = cast_int_ids($order_id))) { $sql .= " AND `o`.`order_id` IN (" . implode(', ', $order_ids) . ") "; $limit = count($order_ids); } else { if(($order_id = (int) $order_id) > 0) { $sql .= " AND `o`.`order_id` = $order_id "; $limit = 1; } } if(($order_number = $filters['number'])) { $sql .= " AND `o`.`order_number` = " . $this->db->escape($order_number) . " "; $return_flat_row = true; $limit = 1; } if(($order_ref_number = $filters['ref_order_number'])) { $sql .= " AND `o`.`ref_order_number` = " . $this->db->escape($order_ref_number) . " "; $return_flat_row = true; $limit = 1; } if(($customer_id = (int) $customer_id) > 0) { $sql .= " AND `o`.`customer_id` = $customer_id "; } if(($after_order_id = (int) $filters['after']) > 0) { $sql .= " AND `o`.`order_id` > $after_order_id "; } if(is_bool($filters['retail_orders'])) { $sql .= " AND `c`.`retail_customer` = " . (int) $filters['retail_orders'] . " "; } if(is_bool($filters['gift_wrapped'])) { $sql .= " AND `o`.`gift_wrapped` = " . (int) $filters['gift_wrapped'] . " "; } if(($discount_id = (int) $filters['discount_id']) > 0) { $sql .= " AND `o`.`discount_code` = $discount_id "; } if(($delivery_type = (int) $filters['delivery_type']) > 0) { $sql .= " AND `o`.`delivery_type` = $delivery_type "; } if(($tracking_number = remove_whitespace($filters['tracking_number'])) != '') { $sql .= " AND `o`.`tracking_number` = " . $this->db->escape($tracking_number) . " "; $limit = 1; } if(is_bool($filters['has_tracking_number'])) { $sql .= " AND `o`.`tracking_number` IS " . (($filters['has_tracking_number'] === true) ? 'NOT' : '') . " NULL "; } if(is_bool($filters['has_dispatched'])) { $sql .= " AND `o`.`has_dispatched` = " . (int) $filters['has_dispatched'] . " "; } if($filters['to_review'] === true) { $sql .= " AND ( `o`.`needs_review` = 1 OR (`o`.`delivery_type` IS NULL OR `o`.`delivery_type` = 1) OR `o`.`delivery_date` IS NULL ) OR `o`.`auto_delivery_date` = 1 "; } foreach(array('delivery' => 'delivery_date', 'order' => 'ordered_date', 'dispatched' => 'dispatched_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 `o`.`$column_name` >= " . $$date_var_from . " "; } if($$date_var_from <= 0 && $$date_var_to > 0) { $sql .= " AND `o`.`$column_name` <= " . $$date_var_to . " "; } elseif($$date_var_from > 0 && $$date_var_to > 0) { $sql .= " AND ( `o`.`$column_name` >= " . $$date_var_from . " AND `o`.`$column_name` <= " . $$date_var_to . " ) "; } } } if(is_bool(($orders_completed = $filters['completed']))) { //$sql .= " AND `o`.`is_completed` = " . (int) $orders_completed . " "; } if(($dispatchment_status = (int) $filters['dispatchment_status']) > 0) { $sql .= " AND `o`.`dispatchment_status` = $dispatchment_status "; } $sql .= " GROUP BY `o`.`order_id` "; $sql .= " ) AS `o` WHERE " . (($allow_deleted_orders) ? '1 = 1' : ' `o`.`is_deleted` = 0 ') . " "; if(strlen(($delivery_location = $filters['delivery_address']))) { $sql .= " AND MATCH(`o`.`delivery_address_1`, `o`.`delivery_address_2`, `o`.`delivery_address_3`, `o`.`delivery_town`, `o`.`delivery_county`, `o`.`delivery_postcode`, `o`.`delivery_country`) AGAINST ('\"" . $this->db->escape_str($delivery_location) . "\"' IN BOOLEAN MODE) "; } if(strlen(($customer_details = $filters['customer_details']))) { $sql .= " AND ( "; foreach(array('delivery', 'invoice') as $i => $component_type) { $sql .= (($i > 0) ? ' OR ' : '') . " MATCH("; foreach(array('first_name', 'last_name', 'business', 'address_1', 'address_2', 'address_3', 'town', 'county', 'postcode', 'email', 'phone', 'mobile') as $column_name) { $sql .= "`o`.`" . $component_type . "_" . $column_name . "`, "; } $sql = rtrim($sql, ', ') . ") AGAINST('" . $this->db->escape_str($customer_details) . "' IN BOOLEAN MODE) "; } $sql .= " ) "; //$sql .= " AND " } $sql .= " GROUP BY `o`.`order_id` "; $sql .= " ) AS `o` "; $where_appended = false; if(($order_status = (int) $filters['status']) > 0) { $sql .= " WHERE `o`.`order_status` = $order_status "; $where_appended = true; } if($filters['ready_to_dispatch'] === true) { $sql .= (($where_appended) ? ' AND ' : ' WHERE ') . " `o`.`can_dispatch` = 1 "; } $having_appended = false; if(is_bool(($orders_unpaid = $filters['unpaid'])) || $orders_unpaid === '1' || $orders_unpaid === '0') { $having_appended = true; $sql .= " HAVING `order_fully_paid` = " . (int) !$orders_unpaid . " "; } if(($recurring_orders = $filters['recurring'])) { $sql .= ((!$having_appended) ? 'HAVING' : 'AND') . " `recurring_id` IS NOT NULL "; $having_appended = true; if($filters['skip_finished_recurs']) { $sql .= " AND `is_finished` = 0 "; } } if(($order_total = (float) $filters['total'])) { $sql .= (!$having_appended) ? 'HAVING' : 'AND'; $having_appended = true; $total_margin = (($total_margin = (float) $filters['total_margin']) > 0) ? $total_margin : 2; $sql .= " ( `total_inc_vat` >= " . ($order_total - $total_margin) . " AND `total_inc_vat` <= " . ($order_total + $total_margin) . " ) "; } if(($recurring_id = (int) $filters['recurring_id']) > 0) { $sql .= ((!$having_appended) ? 'HAVING' : 'AND') . " `recurring_id` = $recurring_id "; $having_appended = $return_flat_row = true; $limit = 1; } $sql .= " ORDER BY "; if($filters['priority_order']) { $sql .= " `o`.`priority` DESC "; } $has_sort = false; if(is_array($filters['order_by'])) { $valid_columns = array ( 'paid' => '`order_fully_paid`', 'price' => '`o`.`total_inc_vat`', 'dispatched' => '`o`.`has_dispatched`', 'completed' => '`o`.`is_completed`', 'order-date' => '`o`.`ordered_date`', 'delivery-date' => '`o`.`delivery_date`', 'courier-pickup' => '`o`.`courier_pickup_date', 'delivery-type' => '`o`.`delivery_type`', 'due-days' => '`o`.`delivery_due`', 'progress' => '`o`.`order_progress`', 'pending' => '`o`.`is_pending`' ); $sorts = array(); foreach($filters['order_by'] as $sort_by => $sort_type) { if(isset($valid_columns[$sort_by]) && in_array($sort_type, array('desc', 'asc'))) { $sql .= (!$has_sort && $filters['priority_order']) ? ', ' : ''; $has_sort = true; $sorts[] = " " . $valid_columns[$sort_by] . " " . (($sort_type == 'desc') ? 'DESC' : 'ASC'); } } $sql .= (!empty($sorts)) ? implode(', ', $sorts) : ''; } if(!$has_sort) { $sql .= ", `o`.`ordered_date` DESC "; } if($filters['sum'] !== NULL) { $sql .= " ) AS `o`"; if($filters['sum_group']) { switch($filters['sum_group']) { case 'order-date': $sql .= " GROUP BY `ordered_datestamp` "; break; } } } if($filters['count'] === true) { return $this->common->countPreviousQuery($sql . $this->common->returnOffset($skip, $limit)); } $return_single_row = (($order_id > 0 || $return_flat_row) && $limit === 1); $_limit = (!$return_single_row) ? ($limit + 1) : $limit; $sql .= $this->common->returnOffset($skip, $_limit); $result = $this->db->query($sql); if($filters['sum'] !== NULL && !$filters['sum_group']) { return $result->row('summed'); } if(wayne()) { //preprint($this->db->last_query()); } if(($row_count = $result->num_rows())) { $rows = $result->result(); $result->free_result(); if(!$return_single_row) { $last_row = ($row_count === 1) ? end($rows) : (($row_count > $limit) ? array_pop($rows) : NULL); $this->_next_row_id = (isset($last_row->order_id)) ? $last_row->order_id : NULL; } return ($return_single_row) ? reset($rows) : $rows; } return array(); } public function getNextRowId() { return $this->_next_row_id; } public function getOrderIdByNumber($order_number = '') { return (($order_info = $this->get(NULL, NULL, array('number' => $order_number)))) ? (int) $order_info->order_id : false; } public function populateAddressDetails($data = array()) { foreach(array('delivery', 'invoice') as $address_type) { foreach(array('first_name', 'last_name', 'business', 'address_1', 'address_2', 'address_3', 'town', 'county', 'postcode', 'country', 'email', 'phone', 'mobile') as $component_type) { $populated_addresses[$address_type][$component_type] = NULL; if(isset($data[$address_type . '_' . $component_type])) { if(!isset($populated_addresses[$address_type])) { $populated_addresses[$address_type] = array(); } $populated_addresses[$address_type][$component_type] = nullify($data[$address_type . '_' . $component_type]); } if($populated_addresses[$address_type][$component_type] !== NULL && $component_type == 'postcode') $populated_addresses[$address_type][$component_type] = format_postcode($populated_addresses[$address_type][$component_type]); if($populated_addresses[$address_type][$component_type] !== NULL && $component_type == 'country') $populated_addresses[$address_type][$component_type] = strtoupper($populated_addresses[$address_type][$component_type]); } $country_name = iso_code_to_country($populated_addresses[$address_type]['country']); // Create a HTML address string $populated_addresses[$address_type]['html'] = format_customer_details ( array($populated_addresses[$address_type]['first_name'], $populated_addresses[$address_type]['last_name'], $populated_addresses[$address_type]['business']), array($populated_addresses[$address_type]['address_1'], $populated_addresses[$address_type]['address_2'], $populated_addresses[$address_type]['address_3'], $populated_addresses[$address_type]['town'], $populated_addresses[$address_type]['county'], $populated_addresses[$address_type]['postcode'], $country_name, $populated_addresses[$address_type]['country']), array($populated_addresses[$address_type]['email'], $populated_addresses[$address_type]['phone'], $populated_addresses[$address_type]['mobile']) ); } $invoice_same_delivery = (isset($data['invoice_same_delivery'])) ? (bool) $data['invoice_same_delivery'] : false; if($invoice_same_delivery) { $populated_addresses['invoice'] = $populated_addresses['delivery']; } return $populated_addresses; } public function validatePopulatedAddress($populated_addresses = array()) { if(isset($populated_addresses['delivery'], $populated_addresses['invoice'])) { if(!((required_fields_filled($populated_addresses['delivery']['first_name']) || required_fields_filled($populated_addresses['delivery']['business'])) && required_fields_filled($populated_addresses['delivery']['address_1'], $populated_addresses['delivery']['postcode']))) { return $this->common->setResponseMessage('The delivery name/business and address details are required'); } if(!((required_fields_filled($populated_addresses['invoice']['first_name']) || required_fields_filled($populated_addresses['invoice']['business'])) && required_fields_filled($populated_addresses['invoice']['address_1'], $populated_addresses['invoice']['postcode']))) { return $this->common->setResponseMessage('The invoice name/business and address details are required'); } foreach(array('delivery', 'invoice') as $address_type) { /*if(!(required_fields_filled($populated_addresses[$address_type]['email']) || required_fields_filled($populated_addresses[$address_type]['phone']) || required_fields_filled($populated_addresses[$address_type]['mobile']))) { return $this->common->setResponseMessage('At least one contact method is required for each address type'); }*/ if(!(required_fields_filled($populated_addresses[$address_type]['email']))) { return $this->common->setResponseMessage('The delivery and invoice email address is required'); } } return true; } return $this->common->setResponseMessage('The delivery and invoice details are required'); } public function saveOrder($data = array(), $order_id = 0, $pend_order = false) { //decode_post_data($data); $this->_created_order_ids = array(); $order_id = (($order_id = (int) $order_id) > 0) ? $order_id : 0; $is_update = ($order_id > 0); $ref_order_number = (isset($data['ref_order_number']) && ($ref_order_number = trim($data['ref_order_number']))) ? $ref_order_number : NULL; $this->load->model('basket'); $this->basket->checkBasketSession(); $raw_data = json_encode($data); //preprint($data);exit; if($this->basket->basket_id > 0) { $basket_items = $this->basket->getBasket(); if(!empty($basket_items)) { //preprint($basket_items);exit; $delivery_dates = (isset($data['delivery_date']) && is_array($data['delivery_date'])) ? array_filter(array_map(function($value) { return (!empty($value) && ($timestamp = strtotime($value . ' 00:00:00')) > 0) ? $timestamp : NULL; }, $data['delivery_date'])) : array(); /* if(empty($delivery_dates)) { return $this->common->setResponseMessage('The delivery date(s) are required', false); } */ $referral = (isset($data['referral']) && ($referral_type = (int) $data['referral']) > 0 && $this->common->getReferrals($referral_type)) ? $referral_type : 1; // Get the existing customer ID $existing_customer_id = (isset($data['customer_id']) && ($customer_id = (int) $data['customer_id']) > 0) ? $customer_id : 0; $exiting_customer_details = ($existing_customer_id > 0) ? $this->customers->get($existing_customer_id) : NULL; // Then load if we have a customer ID $existing_customer_info = ($existing_customer_id > 0) ? $this->customers->getCustomerAddresses($existing_customer_id, true) : NULL; // See if we're using a selected address from the address book $pre_selected_delivery = ($existing_customer_id && isset($data['existing_delivery']) && ($pre_selected_delivery = (int) $data['existing_delivery']) > 0) ? $pre_selected_delivery : NULL; $pre_selected_invoice = ($existing_customer_id && isset($data['existing_invoice']) && ($pre_selected_invoice = (int) $data['existing_invoice']) > 0) ? $pre_selected_invoice : NULL; if($pre_selected_delivery && !$this->customers->getCustomerExistingAddress($existing_customer_id, $pre_selected_delivery)) $pre_selected_delivery = NULL; if($pre_selected_invoice && !$this->customers->getCustomerExistingAddress($existing_customer_id, $pre_selected_invoice)) $pre_selected_invoice = NULL; $customer_info = array(); $populated_addresses = $this->populateAddressDetails($data); if(!$this->validatePopulatedAddress($populated_addresses)) { return false; } // We'll be putting the address IDs into here to inserted into the database $address_options = array ( 'delivery' => $pre_selected_delivery, // NULL if no existing address is selected 'invoice' => $pre_selected_invoice // NULL if no existing address is selected ); // Add the address details if we have a new customer or no addresses from // an existing customer if(!$existing_customer_id) { $compare_invoice_address = array_slice($populated_addresses['invoice'], 0, -1); $compare_delivery_address = array_slice($populated_addresses['delivery'], 0, -1); $invoice_and_delivery_different = !is_similar_address($compare_invoice_address, $compare_delivery_address); // Save the customer details $order_customer_id = $this->customers->saveCustomer(array ( 'first_name' => $populated_addresses['invoice']['first_name'], 'last_name' => $populated_addresses['invoice']['last_name'], 'business' => $populated_addresses['invoice']['business'], 'address_1' => $populated_addresses['invoice']['address_1'], 'address_2' => $populated_addresses['invoice']['address_2'], 'address_3' => $populated_addresses['invoice']['address_3'], 'town' => $populated_addresses['invoice']['town'], 'county' => $populated_addresses['invoice']['county'], 'postcode' => $populated_addresses['invoice']['postcode'], 'country' => $populated_addresses['invoice']['country'], 'email' => $populated_addresses['invoice']['email'], 'phone' => $populated_addresses['invoice']['phone'], 'mobile' => $populated_addresses['invoice']['mobile'], 'password' => (isset($data['_password'])) ? $data['_password'] : NULL, 'confirm_password' => (isset($data['_confirm_password'])) ? $data['_confirm_password'] : NULL, 'referral' => $referral, 'subscribe_mail_list' => true )); // Check if we have a valid ID before continuing if(!$order_customer_id) { return $this->common->setResponseMessage($this->common->getResponseMessage()); //return $this->common->setResponseMessage('There was a problem trying to save the customer'); } $successful_saves = 0; foreach(array('delivery', 'invoice') as $address_type) { $address_fields = array(); foreach(array('first_name', 'last_name', 'business', 'address_1', 'address_2', 'address_3', 'town', 'county', 'postcode', 'country', 'email', 'phone', 'mobile') as $component_type) { $address_fields[$component_type] = $populated_addresses[$address_type][$component_type]; } $address_fields['customer_id'] = $order_customer_id; if(($address_id = $this->customers->saveCustomerAddress($address_type, $address_fields, true))) { if($invoice_and_delivery_different) { $address_options[$address_type] = $address_id; } $successful_saves++; } } if(!($successful_saves === 2)) { return $this->common->setResponseMessage('There was a problem trying to save the customer'); } } else { $order_customer_id = $existing_customer_id; $default_customer_addresses = $this->customers->getCustomerAddresses($existing_customer_id, true); $existing_customer_addresses = $this->customers->getCustomerAddresses($existing_customer_id); if(!empty($existing_customer_addresses)) { foreach(array('delivery', 'invoice') as $address_type) { //predump($default_customer_addresses[$address_type]['address_html'], $populated_addresses[$address_type]['html']); if(is_similar_address($default_customer_addresses[$address_type]['address_html'], $populated_addresses[$address_type]['html'], $similar_match)) //if(metaphone($existing_customer_info[$address_type]['address_html']) === metaphone($populated_addresses[$address_type]['html'])) { continue; } $address_details_exists = false; if(!empty($existing_customer_addresses)) { foreach($existing_customer_addresses[$address_type] as $address_info) { //predump(is_similar_address($address_info['address_html'], $populated_addresses[$address_type]['html'])); //preprint($address_info['address_html']); //preprint($populated_addresses[$address_type]['html']); if(is_similar_address($address_info['address_html'], $populated_addresses[$address_type]['html'])) //if(metaphone($address_info['address_html']) === metaphone($populated_addresses[$address_type]['html'])) { $address_details_exists = $address_info['id']; break; } } } $address_options[$address_type] = ($address_details_exists > 0) ? $address_details_exists : $this->customers->saveCustomerAddress($address_type, array_merge($populated_addresses[$address_type], array('customer_id' => $existing_customer_id)), !$existing_customer_addresses); //$address_options[$address_type] = (!$address_details_exists) ? $this->customers->saveCustomerAddress($address_type, array_merge($populated_addresses[$address_type], array('customer_id' => $existing_customer_id)), !$existing_customer_addresses) : NULL; } } } if(!$address_options['delivery'] || !$address_options['invoice']) { //return $this->common->setResponseMessage('There was a problem trying to get the customer address details', false); } if(isset($data['vat'])) { if(strlen(($_vat = trim($data['vat']))) && is_numeric($_vat)) { $apply_vat = (($_vat = (float) $_vat) >= 0) ? $_vat : false; if($apply_vat === false) { return $this->common->setResponseMessage('The VAT rate is not a valid number'); } } else { $apply_vat = $this->common->vat; } } else { $apply_vat = $this->common->vat; } //$apply_vat = (isset($data['vat']) && strlen(trim($data['vat'])) && ($apply_vat = (float) $data['vat']) >= 0) ? $apply_vat : $this->common->vat; // As of June 2014 - VAT is 20% if($apply_vat > SYSTEM_LEGAL_VAT) { return $this->common->setResponseMessage('The VAT rate is too high!'); } $this->basket->setVat($apply_vat); $payment_type = (isset($data['payment_type']) && ($payment_type = (int) $data['payment_type']) > 0 && $this->common->getPaymentTypes($payment_type)) ? $payment_type : 1; $this->load->model('offers'); $applied_discount_id = NULL; $discount_code = (isset($data['discount_code']) && strlen(($discount_code = remove_whitespace($data['discount_code'])))) ? $discount_code : NULL; $discount_code_info = ($discount_code) ? $this->offers->findByCode($discount_code, $order_customer_id) : NULL; if($discount_code_info) { $applied_discount_id = $discount_code_info->code_id; $discount_type = NULL; $discount_amount = 0; } else { $discount_type = (isset($data['discount_type']) && ($discount_type = (int) $data['discount_type']) > 0 && in_array($discount_type, array(DISCOUNT_PERCENTAGE, DISCOUNT_AMOUNT))) ? $discount_type : NULL; $discount_amount = ($discount_type && (isset($data['discount_amount'])) && ($discount_amount = (float) $data['discount_amount']) > 0) ? $discount_amount : 0; if($discount_amount <= 0) { $discount_type = NULL; $discount_amount = 0; } // Apply with customer's default discount if none is applied if(!$discount_amount && isset($exiting_customer_details->default_discount) && $exiting_customer_details->default_discount > 0) { $discount_amount = $exiting_customer_details->default_discount; $discount_type = $exiting_customer_details->default_discount_type; } $this->basket->setDiscount($discount_amount); $this->basket->setDiscountType($discount_type); } $basket_amount = $this->basket->calculate('grand-total'); $basket_before_discount = $this->basket->calculate('before-discounts'); if($discount_type === DISCOUNT_PERCENTAGE && $discount_amount > 100) { return $this->common->setResponseMessage('You cannot set a discount over 100%'); } else { if($discount_type === DISCOUNT_AMOUNT) { if($discount_amount > $basket_before_discount) { return $this->common->setResponseMessage('You cannot set a discount more than the order itself'); } } } // If the no charges checkbox is enabled or use the default // customer no charges field if(isset($data['no_charges']) || (isset($existing_customer_info->no_charges) && (bool) $existing_customer_info->no_charges)) { $this->basket->setCharges(0); } $additional_charges = $this->basket->getCharges(); $gift_wrapped = (isset($data['gift_wrapped'])) ? (bool) $data['gift_wrapped'] : false; $gift_note = ($gift_wrapped && isset($data['gift_note']) && strlen(($gift_note = trim($data['gift_note'])))) ? $gift_note : NULL; $delivery_notes = nullify((isset($data['order_notes'])) ? $data['order_notes'] : NULL); $internal_notes = nullify((isset($data['internal_notes'])) ? $data['internal_notes'] : NULL); $ordered_date = (isset($data['ordered_date']) && ($ordered_date = strtotime($data['ordered_date'])) > 1) ? $ordered_date : time(); $dupe_order_id = (isset($data['duplicate_order_id']) && ($dupe_order_id = (int) $data['duplicate_order_id']) > 0) ? $dupe_order_id : NULL; $recurred_id = (isset($data['recurring_id']) && ($recurred_id = (int) $data['recurring_id']) > 0) ? $recurred_id : NULL; $recurred_hash = ($recurred_id && isset($data['recurring_hash']) && preg_match('/^[a-f0-9]{32}$/', $data['recurring_hash'])) ? $data['recurring_hash'] : NULL; $send_confirmation_email = (!isset($data['no_confirmation_email'])) ? true : ((((bool) $data['no_confirmation_email']) === true) ? false : true); $delivery_type = (isset($data['delivery_type']) && ($delivery_type = (int) $data['delivery_type']) > 0 && (($delivery_info = $this->getDeliveryTypes($delivery_type)))) ? $delivery_type : 1; $delivery_info = $this->getDeliveryTypes($delivery_type); $this->basket->setDeliveryType($delivery_type); $delivery_charges = NULL; if(isset($data['delivery_charges'])) { if(is_numeric($data['delivery_charges']) && ($manual_charges = (float) $data['delivery_charges']) >= 0) { $delivery_charges = $manual_charges; $this->basket->setDeliveryCharges($manual_charges); } } $delivery_charges = ($delivery_charges === NULL) ? $this->basket->calculateDeliveryCharges() : $delivery_charges; if($discount_code_info) { $this->basket->applyDiscountByCode($discount_code, $discount_type, $discount_amount, $delivery_charges); } $order_priority = (isset($delivery_info->priority)) ? $delivery_info->priority : 1; $delivery_date_auto_selected = false; // Auto pick the delivery date that can be changed later if(count($delivery_dates) === 0) { $delivery_date_auto_selected = true; $delivery_dates = array($this->estimateDeliveryDate($delivery_info->delivery_days)); } $needs_review = (isset($data['needs_review'])) ? (bool) $data['needs_review'] : false; $full_payment = (int) (isset($data['mark_paid']) && (bool) $data['mark_paid']) ? $data['mark_paid'] : ((isset($data['payment_type'])) ? $data['payment_type'] : false); foreach($delivery_dates as $i => $delivery_timestamp) { $sql_data['order_number'] = $this->generateOrderNumber(); $sql_data['customer_id'] = $order_customer_id; $sql_data['duplicate_order_id'] = $dupe_order_id; $sql_data['recurred_from'] = $recurred_id; $sql_data['ordered_date'] = $ordered_date; $sql_data['delivery_date'] = ($delivery_timestamp > 0) ? $delivery_timestamp : NULL; $sql_data['auto_delivery_date'] = $delivery_date_auto_selected; $sql_data['discount'] = $discount_amount; $sql_data['discount_type'] = $discount_type; $sql_data['discount_code'] = $applied_discount_id; $sql_data['delivery_type'] = $delivery_type; $sql_data['delivery_cost'] = $delivery_charges; $sql_data['delivery_notes'] = $delivery_notes; $sql_data['internal_notes'] = $internal_notes; $sql_data['gift_wrapped'] = $gift_wrapped; $sql_data['gift_note'] = $gift_note; $sql_data['alt_address'] = $address_options['delivery']; $sql_data['invoice_address'] = $address_options['invoice']; $sql_data['payment_type'] = $payment_type; $sql_data['additional_charges'] = $additional_charges; $sql_data['vat'] = $apply_vat; $sql_data['priority'] = $order_priority; $sql_data['referral'] = $referral; $sql_data['needs_review'] = $needs_review; $sql_data['created_by'] = ($this->users->user_id > 0) ? $this->users->user_id : 1; $sql_data['ref_order_number'] = $ref_order_number; $sql_data['recur_identifier'] = $recurred_hash; $sql_data['raw_data'] = $raw_data; $sql_data['is_pending'] = (bool) $pend_order; $sql = $this->db->insert_string('orders', $sql_data); $result = $this->db->query($sql); if($this->db->affected_rows()) { $ref_order_id = ($is_update) ? $order_id : $this->db->insert_id(); $dupe_order_id = ($i === 0) ? $ref_order_id : $dupe_order_id; if(!$is_update) { $this->_created_order_ids[] = $ref_order_id; } $this->moveBasketItemsToOrders($this->basket->basket_id, $ref_order_id); if(isset($basket_discount_type, $basket_discount_amount)) { $this->applyBasketDiscount($ref_order_id, $basket_discount_type, $basket_discount_amount); } } } $this->basket->clearBasket(); if(!$is_update) { $ordered_ids = $this->getCreatedOrderIds(); if(strtotime('00:00:00') === strtotime('00:00:00', $ordered_date) && count($ordered_ids)) { if($send_confirmation_email) { $this->sendConfirmationEmail(reset($ordered_ids)); } } foreach($ordered_ids as $created_order_id) { if(isset($data['push_first_step'])) { //$this->proceedOrderItemsFirstStep($created_order_id); } if($full_payment > 0) { $this->markAsPaid($created_order_id, $full_payment); } } if($applied_discount_id) { $this->offers->deductOfferCount($applied_discount_id); } } return $this->common->setResponseMessage('The order was successfully placed', true); } } return $this->common->setResponseMessage('There are no items in your basket'); } public function getCreatedOrderIds() { return $this->_created_order_ids; } public function estimateDeliveryDate($delivery_days = 0, $from_date = NULL) { $from_date = (isset($from_date)) ? $from_date : time(); $estimated_delivery_date = strtotime('+' . $delivery_days . ' days 00:00:00', $from_date); $closed_delivery_days = array(); foreach(array('monday', 'tuesday', 'wednesday', 'thursday', 'friday', 'saturday', 'sunday') as $delivery_day) { if($this->config->item('delivery_' . $delivery_day)) { continue; } $closed_delivery_days[] = $delivery_day; } if(in_array(strtolower(date('l', $estimated_delivery_date)), $closed_delivery_days) || $this->calendar->isBankHoliday($estimated_delivery_date)) { do { $estimated_delivery_date = strtotime('+1 day', $estimated_delivery_date); $estimated_delivery_day = strtolower(date('l', $estimated_delivery_date)); } while((in_array($estimated_delivery_day, $closed_delivery_days) || $this->calendar->isBankHoliday($estimated_delivery_date)) === true); } return $estimated_delivery_date; } public function deleteOrder($order_id = 0) { if(is_array($order_id)) { array_map(array($this, 'deleteOrder'), $order_id); return true; } else { if(($order_id = (int) $order_id) > 0 && ($order_info = $this->get($order_id))) { if($order_info->is_recurring_order) return $this->common->setResponseMessage('You cannot delete an order that is recurring - delete it from the recurring orders page first', false); $this->db->query($this->db->update_string('orders', array('is_deleted' => 1), "`order_id` = $order_id")); return ($this->db->affected_rows() === 1); } } return false; } public function moveBasketItemsToOrders($basket_id = 0, $order_id = 0) { if(($basket_id = (int) $basket_id) > 0 && ($order_id = (int) $order_id) > 0) { $this->load->model('basket'); $basket_items = $this->basket->getBasket($basket_id); foreach($basket_items as $basket_item_id => $item_info) { $result = $this->db->query($this->db->insert_string('order_items', array ( 'order_id' => $order_id, 'product_id' => $item_info->id, 'override_name' => ($item_info->override_name) ? $item_info->override_name : NULL, 'quantity' => $item_info->qty, 'gift_wrapped' => $item_info->gift_wrapped, 'gift_wrap_cost' => ($item_info->gift_wrap_cost * $item_info->qty), 'gift_message' => $item_info->gift_message, 'price' => $item_info->cost, 'data' => $item_info->data ))); if($this->db->affected_rows($result)) { $order_item_id = $this->db->insert_id(); foreach($item_info->extra_options as $option_info) { $this->saveOrderItemOption($order_item_id, $option_info->extra_id); } } } return true; } return false; } public function applyBasketDiscount($order_id = 0, $discount_type = NULL, $discount_amount = 0) { if(($order_id = (int) $order_id) > 0 && ($discount_amount = (float) $discount_amount) > 0) { if($discount_type == DISCOUNT_PERCENTAGE) { $this->db->query("UPDATE `order_items` SET `price` = IF(ROUND(((`price` / 100) * $discount_amount), 2) < 0, 0, ROUND(((`price` / 100) * $discount_amount), 2)) WHERE `order_id` = $order_id"); } elseif($discount_type == DISCOUNT_AMOUNT) { $this->db->query("UPDATE `order_items` SET `price` = IF((`price` - $discount_amount) < 0, 0, (`price` - $discount_amount)) WHERE `order_id` = $order_id"); } } } public function saveOrderItemOption($order_item_id = 0, $extra_id = 0, $extra_charges = 0, $existing_id = 0) { if(($order_item_id = (int) $order_item_id) > 0 && ($extra_id = (int) $extra_id) > 0 && ($option_info = $this->products->getExtraOption($extra_id))) { $sql_data = array ( 'item_id' => $order_item_id, 'extra_id' => $extra_id, 'extra_charge' => $option_info->extra_charge ); if(($existing_id = (int) $existing_id) > 0) { $sql = $this->db->update_string('order_item_options', $sql_data, "`option_id` = $existing_id"); } else { $sql = $this->db->insert_string('order_item_options', $sql_data); } $this->db->query($sql); return true; } return false; } public function deleteOrderItemOptions($order_item_id = 0) { if(($order_item_id = (int) $order_item_id) > 0) { $this->db->query("DELETE FROM `order_item_options` WHERE `item_id` = $order_item_id"); } } public function getOrderItemOptions($order_item_id = 0) { if(($order_item_id = (int) $order_item_id) > 0) { $sql = "SELECT `io`.*, `pe`.`name` AS `option_name`, (`oi`.`quantity` * `io`.`extra_charge`) AS `extra_charge` FROM `order_item_options` `io` INNER JOIN `product_extras` `pe` ON `pe`.`extra_id` = `io`.`extra_id` INNER JOIN `order_items` `oi` ON `oi`.`item_id` = `io`.`item_id` WHERE `io`.`item_id` = ? GROUP BY `io`.`extra_id`"; $result = $this->db->query($sql, array($order_item_id)); if($result->num_rows()) { $rows = $result->result(); $result->free_result(); return $rows; } } return array(); } public function generateOrderNumber() { do { $order_number = preg_replace('/(O[0-9]{3})([0-9]+)/', '$1-$2', 'O' . rand(100000, 999999)); } while($this->db->query("SELECT 1 FROM `orders` WHERE `order_number` = ? LIMIT 1", array($order_number))->num_rows() > 0); return $order_number; } public function getOrderItems($order_id = 0, $order_item_id = 0, $exclude_samples = false, $filters = array()) { $filters = array_merge(array ( 'status' => NULL, 'count_status' => false, 'order_info' => false, 'order_date_from' => NULL, 'order_date_to' => NULL, 'delivery_date_from' => NULL, 'delivery_date_to' => NULL, 'dispatched_date_from' => NULL, 'dispatched_date_to' => NULL, 'include_to_review' => true, 'exclude_pending' => false ), $filters); $order_id = (int) $order_id; $order_item_id = (int) $order_item_id; $status_type = $filters['status']; $today = strtotime('00:00:00'); $include_order_info = (bool) $filters['order_info']; $count_status_groups = (bool) $filters['count_status']; if($order_id > 0 || $order_item_id > 0 || (is_array($status_type) || (int) $status_type > 0)) { $sql = ''; if($count_status_groups) { $sql .= "SELECT COUNT(`ic`.`item_id`) AS `total_count` FROM ( SELECT `oi`.`item_id`, IFNULL(`ois`.`status`, " . ORDER_STATUS_NEW . ") AS `item_status` "; } else { $sql .= "SELECT `oi`.*, (`oi`.`quantity` * `oi`.`price`) AS `total_cost`, IFNULL(`ois`.`status`, " . ORDER_STATUS_NEW . ") AS `item_status`, `st`.`label` AS `item_status_label`, `ois`.`date_changed`, `r`.*, IF(`oi`.`override_name` IS NOT NULL, NULL, `r`.`url_name`) AS `url_name`, IF(`p2`.`cost` IS NOT NULL, (`r`.`cost` + `p2`.`cost`), `r`.`cost`) AS `cost`, IF(`p2`.`cost` IS NOT NULL, `r`.`cost`, 0) AS `option_cost`, `oi`.`gift_wrap_cost`, IF(`oi`.`override_name` IS NOT NULL, `oi`.`override_name`, IF(`p2`.`name` IS NOT NULL, CONCAT(`p2`.`name`, ' (', `r`.`name`, ')'), `r`.`name`)) AS `name`, IF(`oi`.`override_name` IS NOT NULL, `oi`.`override_name`, IF(`p2`.`name` IS NOT NULL, `p2`.`name`, `r`.`name`)) AS `product_name`, IF(`oi`.`override_name` IS NOT NULL, NULL, IF(`p2`.`name` IS NOT NULL, `r`.`name`, NULL)) AS `option_name`, `rc`.`category`, `rt`.`label` AS `product_type`"; } if($include_order_info) { $sql .= ", `o`.`order_number`, `o`.`priority`, `o`.`delivery_date`, ROUND((`o`.`delivery_date` - $today) / 60 / 60 / 24) AS `delivery_due`, `o`.`delivery_notes`, `o`.`internal_notes`, `o`.`needs_review`, `o`.`is_pending`, `ref`.`label` AS `referral_type`, `ref`.`friendly_name` AS `friendly_referral_type`, `c`.`customer_id`, `c`.`first_name`, `c`.`last_name`, `c`.`business`, `c`.`is_deleted` AS `deleted_customer` "; } $sql .= " FROM ( `order_items` `oi` "; if($include_order_info) { $sql .= " INNER JOIN `orders` `o` ON `o`.`order_id` = `oi`.`order_id` AND `o`.`is_deleted` = 0 INNER JOIN `referral_types` `ref` ON `ref`.`refer_id` = `o`.`referral` LEFT OUTER JOIN `customers` `c` ON `c`.`customer_id` = `o`.`customer_id` LEFT OUTER JOIN ( SELECT `address_id`, `first_name`, `last_name`, `business` FROM `customer_addresses` ) AS `ca` ON `ca`.`address_id` = `o`.`alt_address` "; } $sql .= " INNER JOIN `products` `r` ON `r`.`product_id` = `oi`.`product_id` LEFT OUTER JOIN ( SELECT `product_id`, `parent_id` AS `option_id`, `cost`, `name` FROM `products` WHERE `is_deleted` = 0 ) AS `p2` ON `p2`.`product_id` = `r`.`parent_id` LEFT OUTER JOIN `product_categories` `rc` ON `rc`.`category_id` = `r`.`category_id` LEFT OUTER JOIN `product_type_categories` `rt` ON `r`.`type_category` = `rt`.`type_id` LEFT OUTER JOIN ( SELECT `ois`.`item_id`, `ois`.`status`, `ois`.`date_changed`#, #`st`.`item_group`, #`st`.`label` FROM ( `order_item_status` `ois` INNER JOIN `status_types` `st` ON `st`.`status_id` = `ois`.`status` ) ORDER BY `ois`.`status_id` DESC ) AS `ois` ON `ois`.`item_id` = `oi`.`item_id` INNER JOIN `status_types` `st` ON `st`.`status_id` = IFNULL(`ois`.`status`, 1) AND ( `st`.`item_group` IS NULL OR `st`.`item_group` = `r`.`item_group` ) ) WHERE `oi`.`is_deleted` = 0 "; if($order_id) { $sql .= " AND `oi`.`order_id` = $order_id "; } if($order_item_id) { $sql .= " AND `oi`.`item_id` = $order_item_id "; } if($exclude_samples) { $sql .= " AND `oi`.`is_sample` = 0 "; } if($include_order_info && $filters['include_to_review'] === false) { $sql .= " AND `o`.`needs_review` = 0 "; } if($include_order_info && $filters['exclude_pending'] === true) { $sql .= " AND `o`.`is_pending` = 0 "; } if($include_order_info) { foreach(array('delivery' => 'delivery_date', 'order' => 'ordered_date', 'dispatched' => 'dispatched_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 `o`.`$column_name` >= " . $$date_var_from . " "; } if($$date_var_from <= 0 && $$date_var_to > 0) { $sql .= " AND `o`.`$column_name` <= " . $$date_var_to . " "; } elseif($$date_var_from > 0 && $$date_var_to > 0) { $sql .= " AND ( `o`.`$column_name` >= " . $$date_var_from . " AND `o`.`$column_name` <= " . $$date_var_to . " ) "; } } } } $sql .= " GROUP BY `oi`.`item_id` "; if($status_type) { $sql .= " HAVING "; if(is_array($status_type) && ($status_type = cast_int_ids($status_type))) { $sql .= " `item_status` IN (" . implode(',', $status_type) . ") "; } else { $sql .= " `item_status` = " . (int) $status_type . " "; } } $sql .= " # ORDER BY `r`.`name` ORDER BY " . (($include_order_info) ? " `o`.`priority` DESC, " : '') . " `oi`.`item_id`"; if($count_status_groups) { $sql .= ") AS `ic`"; } $result = $this->db->query($sql); //preprint($this->db->last_query()); if($result->num_rows()) { if($count_status_groups) return (int) $result->row('total_count'); if($order_item_id) return $result->row(); $rows = $result->result(); $result->free_result(); foreach($rows as $row) { if(file_exists('assets/uploads/products/' . $row->identifier . '.jpg')) { $row->photo = '/assets/uploads/products/' . $row->identifier . '.jpg'; $row->photo_thumb = preg_replace('/\.(\w+)$/', '_thumb.$1', $row->photo); } else { $row->photo = $row->photo_thumb = '/assets/images/no-product-image.jpg'; } } return $rows; } } return array(); } public function countStatus($status_type = 0, $include_review = true) { $include_review = (bool) $include_review; return $this->orders->getOrderItems(NULL, NULL, false, array ( 'status' => $status_type, 'order_info' => true, 'include_to_review' => $include_review, 'count_status' => true, 'exclude_pending' => true )); } public function saveOrderItem($data = array(), $order_item_id = 0) { $order_item_id = (($order_item_id = (int) $order_item_id) > 0) ? $order_item_id : NULL; $is_update = ($order_item_id > 0); $sql_data = array(); $is_sample = (isset($data['is_sample'])); $quantity = (isset($data['quantity']) && ($quantity = (int) $data['quantity']) > 0) ? $quantity : 1; $sql_data['quantity'] = $quantity; $sql_data['is_sample'] = $is_sample; $order_id = (isset($data['order_id']) && ($order_id = (int) $data['order_id']) > 0) ? $order_id : NULL; if(isset($data['option']) && ($_option = (int) $data['option']) > 0) { $product_id = $_option; } else { $product_id = (isset($data['product_id']) && ($product_id = (int) $data['product_id']) > 0) ? $product_id : NULL; } if($is_sample) { $sql_data['price'] = 0; } else { if(isset($data['price']) && is_numeric($data['price'])) { if(($item_price = (float) $data['price']) >= 0) { $sql_data['price'] = $item_price; } } } $gift_wrapped = (isset($data['gift_wrapped'])); $gift_wrap_cost = ($gift_wrapped && isset($data['gift_wrap_cost']) && ($gift_wrap_cost = (float) $data['gift_wrap_cost']) >= 0) ? $gift_wrap_cost : 0; $gift_message = (isset($data['gift_message'])) ? nullify($data['gift_message']) : NULL; $sql_data['gift_wrapped'] = $gift_wrapped; $sql_data['gift_wrap_cost'] = $gift_wrap_cost; $sql_data['gift_message'] = $gift_message; if($order_id > 0) $sql_data['order_id'] = $order_id; if($product_id > 0) $sql_data['product_id'] = $product_id; $saved_order_item_id = 0; if($is_update) { if(($order_item_info = $this->getOrderItems(NULL, $order_item_id))) { if($product_id > 0 && $order_item_info->product_id == 1 && $product_id != $order_item_info->product_id) { $sql_data['override_name'] = NULL; } $result = $this->db->query($this->db->update_string('order_items', $sql_data, "`item_id` = $order_item_id")); $saved_order_item_id = $order_item_id; //return ($this->db->affected_rows() === 1); } } else { if($order_id > 0 && $product_id > 0) { //$sql_data = array('order_id' => $order_id, 'product_id' => $product_id) + $sql_data; $result = $this->db->query($this->db->insert_string('order_items', $sql_data)); $saved_order_item_id = ($this->db->affected_rows($result) === 1) ? $this->db->insert_id() : 0; } } $successful_save = ($this->db->affected_rows($result) === 1 || ($is_update && $result)); $this->deleteOrderItemOptions($saved_order_item_id); if(isset($data['extra_options']) && is_array($data['extra_options'])) { foreach($data['extra_options'] as $existing_option_id => $extra_id) { $this->saveOrderItemOption($saved_order_item_id, $extra_id); } } return $successful_save; } public function getOrderTrackingNumbers($order_id = 0) { if(($order_id = (int) $order_id) > 0) { $sql = "SELECT * FROM `shipment_tracking` WHERE `order_id` = $order_id"; $result = $this->db->query($sql); if($result->num_rows()) { $rows = $result->result(); $result->free_result(); return $rows; } } return array(); } public function sendConfirmationEmail($order_id = 0) { if(is_array($order_id)) { array_map(array($this, 'sendConfirmationEmail'), $order_id); return true; } else { if(($order_id = (int) $order_id) > 0 && ($order_info = $this->get($order_id))) { $result = $this->common->sendEmail(array ( 'email' => $order_info->invoice_email, 'bcc' => $order_info->cc_email, 'subject' => $this->config->item('company_name') . ' order #' . $order_info->order_number . ' confirmation', 'variables' => array ( 'name' => ($order_info->invoice_business) ? $order_info->invoice_business : format_name($order_info->invoice_first_name, $order_info->invoice_last_name), 'delivery_date' => $order_info->delivery_date, 'ordered_date' => $order_info->ordered_date, 'order_info' => $order_info, 'order_items' => $this->getOrderItems($order_id) ) ), 'order-confirmation'); if($result) { $this->changeOrderData($order_id, 'confirm_email_sent', time()); } } } return false; } public function markAsComplete($order_id = 0) { if(is_array($order_id)) { $self = $this->{strtolower(__CLASS__)}; // Since we cannot pass $this in an closure array_map(function($order_id) use($self) { $self->markAsComplete($order_id); return true; }, $order_id); } else { if(($order_id = (int) $order_id) > 0 && ($order_info = $this->get($order_id))) { return $this->updateOrderStatus($order_id, ORDER_STATUS_COMPLETE); } } return false; } public function changeDispatchmentStep($order_id = 0, $dispachment_step = DISPATCHMENT_PROCESSING) { if(is_array($order_id)) { $self = $this->{strtolower(__CLASS__)}; // Since we cannot pass $this in an closure array_map(function($order_id) use($self, $dispachment_step) { $self->changeDispatchmentStep($order_id, $dispachment_step); return true; }, $order_id); } else { if(($order_id = (int) $order_id) > 0 && ($order_info = $this->get($order_id)) && in_array($dispachment_step, array(DISPATCHMENT_PROCESSING, DISPATCHMENT_READY, DISPATCHMENT_PACKED, DISPATCHMENT_GONE))) { return $this->changeOrderData($order_id, 'dispatchment_status', $dispachment_step); } } return false; } public function markAsDispatched($order_id = 0, $send_email = false) { if(is_array($order_id)) { $self = $this->{strtolower(__CLASS__)}; // Since we cannot pass $this in an closure array_map(function($order_id) use($self, $send_email) { $self->markAsDispatched($order_id, $send_email); return true; }, $order_id); } else { if(($order_id = (int) $order_id) > 0 && ($order_info = $this->get($order_id))) { $timestamp = time(); if($order_info->can_dispatch && $this->changeOrderData($order_id, array('has_dispatched' => true, 'dispatched_date' => $timestamp, 'dispatchment_status' => DISPATCHMENT_GONE))) //if($this->updateOrderStatus($order_id, ORDER_STATUS_DISPATCHED)) { if($send_email) { $this->changeOrderData($order_id, 'dispatch_email_sent', $timestamp); if($order_info->delivery_email || $order_info->invoice_email) { $response = $this->common->sendEmail(array ( 'email' => ($order_info->delivery_email) ? $order_info->delivery_email : $order_info->invoice_email, 'cc' => ($order_info->delivery_email && ($order_info->invoice_email != $order_info->delivery_email)) ? $order_info->invoice_email : NULL, 'subject' => 'Your order has been dispatched and is now on its way', 'variables' => array ( 'name' => ($order_info->invoice_business) ? $order_info->invoice_business : format_name($order_info->invoice_first_name, $order_info->invoice_last_name), 'delivery_date' => $order_info->delivery_date ) ), 'order-dispatched'); } } if($this->config->item('stock_update_after') == 'DISPATCH') { $this->stocks->updateStocksFromOrder($order_id); } return true; } } } return false; } public function getDeliveryTypes($delivery_type = NULL) { $sql = "SELECT * FROM `delivery_types` WHERE `is_deleted` = 0 "; if(($delivery_type = (int) $delivery_type) > 0) { $sql .= " AND `delivery_id` = $delivery_type "; } $sql .= " ORDER BY CASE WHEN `delivery_id` = 1 THEN 0 ELSE 1 END, `label`"; $result = $this->db->query($sql); if($result->num_rows()) { if($delivery_type) return $result->row(); $rows = $result->result(); $result->free_result(); return $rows; } return array(); } public function getOrderStatuses($group = NULL, $status_type = NULL) { if(in_array($group, array('HE', 'ME', 'SI'))) { $sql = "SELECT * FROM `status_types` WHERE `is_deleted` = 0 "; if(($status_type = (int) $status_type) > 0) { $sql .= " AND `status_id` = $status_type "; } $sql .= " AND `item_group` = '$group' "; $sql .= " ORDER BY `status_order` ASC"; $result = $this->db->query($sql); if($result->num_rows()) { if($status_type) return $result->row(); $rows = $result->result(); $result->free_result(); return $rows; } } return array(); } public function getOrderItemStatusStep($item_id = 0, $mode = NULL) { if(($item_id = (int) $item_id) > 0 && ($item_info = $this->getOrderItems(NULL, $item_id))) { $from_status = (($item_progress = $this->getOrderItemProgress($item_id, true))) ? $item_progress->current_order : 1; return $this->getOrderStatusStep($mode, $item_info->item_group, $from_status); } return false; } public function getOrderStatusStep($mode = NULL, $from_group = 0, $from_status = 0) { if(in_array($mode, array('next', 'previous')) && strlen($from_group) === 2 && ($from_status = (int) $from_status) > 0) { $sql = "SELECT * FROM `status_types` WHERE `is_deleted` = 0 AND ( `item_group` IS NULL OR `item_group` = " . $this->db->escape($from_group) . " ) AND `status_order` > $from_status ORDER BY `status_order` LIMIT 1"; $result = $this->db->query($sql); //echo $sql; if($result->num_rows() > 0) { return $result->row(); } else { $sql = "SELECT * FROM `status_types` WHERE `is_deleted` = 0 AND ( `item_group` IS NULL OR `item_group` = " . $this->db->escape($from_group) . " ) ORDER BY `status_order` LIMIT 1"; $result = $this->db->query($sql); return ($result->num_rows() > 0) ? $result->row() : false; } } return false; } public function getStatusGroupCount() { $sql = "SELECT COUNT(`o`.`order_id`) AS `group_count`, 'New' AS `label`, 1 AS `status_order` FROM `orders` `o` LEFT OUTER JOIN `order_status` `os` ON `o`.`order_id` = `os`.`order_id` WHERE `o`.`is_deleted` = 0 AND `os`.`status` IS NULL UNION SELECT COUNT(`o`.`order_id`) AS `group_count`, `st`.`label`, `st`.`status_order` FROM ( `order_status` `os` INNER JOIN `orders` `o` ON `o`.`order_id` = `os`.`order_id` LEFT OUTER JOIN `status_types` `st` ON `st`.`status_id` = `os`.`status` AND `st`.`is_deleted` = 0 ) GROUP BY `st`.`status_id` ORDER BY `status_order`"; $result = $this->db->query($sql); $rows = $result->result(); $result->free_result(); return $rows; return array(); } public function getOrderProgress($order_id = 0) { if(($order_id = (int) $order_id) > 0) { $sql = "SELECT `st`.`label`, `st`.`status_id`, `st`.`status_order`, IF(`o2`.`status_order` IS NOT NULL, `o2`.`status_order`, " . ORDER_STATUS_NEW . ") AS `current_order`, IF(`st`.`status_id` = " . ORDER_STATUS_NEW . ", `o`.`ordered_date`, `os`.`date_changed`) AS `date_changed`, IF(`o2`.`status` IS NULL AND `st`.`status_id` = " . ORDER_STATUS_NEW . ", 1, IF(`o2`.`status` IS NOT NULL AND `o2`.`status` = `st`.`status_id`, 1, 0)) AS `current_status`, IF(`o2`.`status` IS NOT NULL, 1, 0) AS `has_progressed`, IF(`st`.`status_id` = " . ORDER_STATUS_NEW . ", 1, IF(`o2`.`status_order` IS NOT NULL AND `o2`.`status_order` >= `st`.`status_order`, 1, 0)) AS `status_passed` FROM ( `status_types` `st` LEFT OUTER JOIN ( SELECT `os`.`status`, `os`.`date_changed` FROM `order_status` `os` WHERE `os`.`order_id` = $order_id ORDER BY `os`.`date_changed` ) AS `os` ON `os`.`status` = `st`.`status_id` INNER JOIN `orders` `o` ON `o`.`order_id` = $order_id LEFT OUTER JOIN ( SELECT `os`.`status`, `st`.`status_order` FROM `order_status` `os` INNER JOIN `status_types` `st` ON `st`.`status_id` = `os`.`status` WHERE `os`.`order_id` = $order_id ORDER BY `os`.`date_changed` DESC ) AS `o2` ON 1 = 1 ) WHERE `st`.`is_deleted` = 0 GROUP BY `st`.`status_id` ORDER BY `st`.`status_order`"; $result = $this->db->query($sql); if($result->num_rows()) { $rows = $result->result(); $result->free_result(); return $rows; } } return false; } public function getOrderItemProgress($item_id = 0, $return_current_step = false) { if(($item_id = (int) $item_id) > 0) { $sql = "SELECT $item_id AS `item_id`, `oi`.`order_id`, `st`.`label`, `st`.`status_id`, `st`.`status_order`, `st`.`item_group`, IF(`ois2`.`status` IS NOT NULL, 1, 0) AS `has_progressed`, IF(`st`.`status_id` = " . ORDER_STATUS_NEW . ", `o`.`ordered_date`, `ois`.`date_changed`) AS `date_changed`, `m`.`first_name` AS `changed_by`, IF(`ois2`.`status_order` IS NOT NULL, `ois2`.`status_order`, 1) AS `current_order`, IF(`ois2`.`status` IS NULL AND `st`.`status_id` = " . ORDER_STATUS_NEW . ", 1, IF(`ois2`.`status` IS NOT NULL AND `ois2`.`status` = `st`.`status_id`, 1, 0)) AS `current_status`, IF(`st`.`status_id` = " . ORDER_STATUS_NEW . ", 1, IF(`ois2`.`status_order` IS NOT NULL AND `ois2`.`status_order` >= `st`.`status_order`, 1, 0)) AS `status_passed`, IF(`st`.`status_id` IN(" . ORDER_STATUS_DISPATCHED . ", " . ORDER_STATUS_NEW . "), 0, 1) AS `can_select` FROM ( `status_types` `st` INNER JOIN `order_items` `oi` ON `oi`.`item_id` = $item_id INNER JOIN `products` `p` ON `p`.`product_id` = `oi`.`product_id` LEFT OUTER JOIN ( SELECT `ois`.`status`, `ois`.`date_changed`, `st`.`item_group`, `st`.`gift_message_only` FROM `order_item_status` `ois` INNER JOIN `status_types` `st` ON `st`.`status_id` = `ois`.`status` WHERE `ois`.`item_id` = $item_id ORDER BY `ois`.`date_changed` ) AS `ois` ON `ois`.`status` = `st`.`status_id` INNER JOIN `orders` `o` ON `o`.`order_id` = `oi`.`order_id` LEFT OUTER JOIN ( SELECT `ois`.`status`, `st`.`status_order`, `ois`.`updated_by`, `st`.`gift_message_only` FROM `order_item_status` `ois` INNER JOIN `status_types` `st` ON `st`.`status_id` = `ois`.`status` WHERE `ois`.`item_id` = $item_id ORDER BY `ois`.`date_changed` DESC ) AS `ois2` ON 1 = 1 LEFT OUTER JOIN `members` `m` ON `m`.`user_id` = `ois2`.`updated_by` ) WHERE `st`.`is_deleted` = 0 AND ( `st`.`item_group` IS NULL OR `st`.`item_group` = `p`.`item_group` ) GROUP BY `st`.`status_id` "; if($return_current_step === true) { $sql .= " HAVING `current_status` = 1 "; } $sql .= " ORDER BY `st`.`status_order`"; $result = $this->db->query($sql); if($result->num_rows()) { if($return_current_step === true) return $result->row(); $rows = $result->result(); $result->free_result(); return $rows; } } return false; } public function orderStatus($status_type = 0) { return (($status_label = $this->config->item('order_status_' . $status_type))) ? $status_label : 'N/A'; } public function getTotalClientsOfDay($date = 0) { if(($date = (int) $date) > 1) { $date_start = strtotime('00:00:00', $date); $date_end = strtotime('23:59:59', $date_start); $sql = "SELECT COUNT(DISTINCT `customer_id`) AS `total_clients` FROM `orders` WHERE `is_deleted` = 0 AND ( `delivery_date` >= $date_start AND `delivery_date` <= $date_end )"; $result = $this->db->query($sql); return (int) $result->row('total_clients'); } return 0; } public function signOrder($order_id = 0, $signature_data = NULL, $signature_date = NULL) { if(($order_id = (int) $order_id) > 0 && ($order_info = $this->get($order_id)) && ($signature_data = base64_decode($signature_data)) !== false) { $signature_path = 'assets/uploads/signatures/' . md5($order_id . ':' . $order_info->order_id) . '.png'; if(file_put_contents($signature_path, $signature_data)) { $signature_date = (is_numeric($signature_date) && $signature_date > 1) ? $signature_date : time(); $this->db->query($this->db->insert_string('order_signatures', array ( 'order_id' => $order_id, 'date' => $signature_date ))); return true; } } return false; } public function getPayments($order_id = 0, $filters = array()) { $filters = array_merge(array ( 'date_order' => NULL ), $filters); $sql = "SELECT `op`.*, `pp`.`txn_id`, `pt`.`label` AS `payment_type_label`, `pp`.`refunded`, `pp`.`date_refunded`, IF(`pp`.`refunded` > 0 AND (`pp`.`mc_gross` - `pp`.`refunded`) = 0, 1, 0) AS `fully_refunded` FROM ( `order_payments` `op` INNER JOIN `orders` `o` ON `o`.`order_id` = `op`.`order_id` LEFT OUTER JOIN `paypal_payments` `pp` ON `pp`.`payment_id` = `op`.`payment_id` INNER JOIN `payment_types` `pt` ON `pt`.`type_id` = `op`.`type` ) WHERE `op`.`is_deleted` = 0"; $sql .= (($order_id = (int) $order_id) > 0) ? " AND `op`.`order_id` = $order_id " : ''; $date_order = ($filters['date_order'] == 'asc') ? 'ASC' : 'DESC'; $sql .= " ORDER BY `op`.`date` $date_order"; $result = $this->db->query($sql); if($result->num_rows()) { $rows = $result->result(); $result->free_result(); return $rows; } return array(); } public function markAsPaid($order_id = 0, $payment_type = NULL, $payment_date = NULL) { if(is_array($order_id)) { $self = $this; array_map(function($id) use($payment_type, $payment_date) { $self->markAsPaid($id, $payment_type, $payment_date); }, $order_id); return true; } else { if(($order_id = (int) $order_id) > 0 && ($order_info = $this->get($order_id))) { return $this->orderPayment($order_id, $payment_type, $order_info->total_remaining, ($payment_date === NULL) ? date('d-m-Y', $order_info->ordered_date) : $payment_date); } } return false; } public function orderPayment($order_id = 0, $payment_type = 0, $amount = 0, $date_paid = NULL) { if(($order_id = (int) $order_id) > 0 && ($payment_type = (int) $payment_type) > 0 && ($amount = (float) $amount) != 0 && ($order_info = $this->get($order_id))) { $order_total = (float) $order_info->total_inc_vat; $total_remaining = (float) $order_info->total_remaining; $paid_amount = (float) $order_info->total_amount_paid; $paid_in_full = false; // Non-specific UNIX timestamp, i.e. string date format if(!is_int($date_paid)) { $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; } $is_deduction = (substr((string) $amount, 0, 1) === '-'); // Deduction if($is_deduction) { // DO NOT ALLOW REFUNDS (slight bug going on around) return false; // Remember, - - converts to + so must use + - in order to substract! if($paid_amount > 0 && ($paid_amount + $amount) < 0) { return false; } } else { if(($total_remaining - $amount) < 0) { return false; } $paid_in_full = (($total_remaining - $amount) == 0); } $this->db->query($this->db->insert_string('order_payments', array ( 'order_id' => $order_id, 'type' => $payment_type, 'amount' => $amount, 'date' => $date_paid ))); if($this->db->affected_rows() === 1) { $payment_id = $this->db->insert_id(); if($paid_in_full) { $this->changeOrderData($order_id, 'is_pending', false); } return $payment_id; } } return false; } public function removePayment($payment_id = 0) { if(($payment_id = (int) $payment_id) > 0) { $this->db->query($this->db->update_string('order_payments', array('is_deleted' => 1), "`payment_id` = $payment_id")); return ($this->db->affected_rows() === 1); } return false; } public function removeOrderItem($item_id = 0) { if(($item_id = (int) $item_id) > 0) { $this->db->query($this->db->update_string('order_items', array('is_deleted' => 1), "`item_id` = $item_id")); return ($this->db->affected_rows() === 1); } return false; } public function saveRecurringOrder($order_id = 0, $data = array()) { if(($order_id = (int) $order_id) > 0 && ($order_info = $this->get($order_id))) { $dates = (isset($data['every_date'])) ? NULL : ((!empty($data['date']) && is_array($data['date'])) ? remove_outside_range($data['date'], 1, 31) : NULL); $days = (isset($data['every_day'])) ? NULL : ((!empty($data['day']) && is_array($data['day'])) ? remove_outside_range($data['day'], 1, 7) : NULL); if($dates !== NULL && $days !== NULL) { return $this->common->setResponseMessage('You cannot select both of the dates and the days of the week - please select either'); } $weeks = (isset($data['every_week'])) ? NULL : ((!empty($data['week']) && is_array($data['week'])) ? remove_outside_range($data['week'], 1, 5) : NULL); $months = (isset($data['every_month'])) ? NULL : ((!empty($data['month']) && is_array($data['month'])) ? remove_outside_range($data['month'], 1, 12) : NULL); $repeat_from = (isset($data['recur_start']) && strlen(trim($data['recur_start'])) && ($repeat_from = strtotime($data['recur_start'] . ' 00:00:00')) !== false && $repeat_from >= strtotime('00:00:00')) ? $repeat_from : NULL; $repeat_till = (isset($data['recur_end']) && strlen(trim($data['recur_end'])) && ($repeat_till = strtotime($data['recur_end'] . ' 00:00:00')) !== false && $repeat_till >= strtotime('tomorrow 00:00:00')) ? $repeat_till : NULL; if($repeat_from && $repeat_till && ($repeat_till <= $repeat_from)) { return $this->common->setResponseMessage('The recur length cannot be in the past or ahead of each other'); } $data = array('date' => $dates, 'day' => $days, 'week' => $weeks, 'month' => $months); $sql_data = array(); arsort($data); $is_every_day = (count(array_filter(array_values($data))) === 0); $biggest_array = count(reset($data)); $parent_id = NULL; $legacy_id = ($order_info->legacy_recurring_id) ? $order_info->legacy_recurring_id : (($order_info->recurring_id) ? $order_info->recurring_id : NULL); if($biggest_array > 0 || $is_every_day) { $this->db->trans_start(); $this->db->query("DELETE FROM `recurring_orders` WHERE `order_id` = $order_id"); for($i = 0; $i < (($is_every_day) ? 1 : $biggest_array); $i++) { $recur_data = array ( 'parent_id' => $parent_id, 'legacy_id' => $legacy_id, 'order_id' => $order_id, 'repeat_date' => (isset($data['date'][$i])) ? $data['date'][$i] : NULL, 'repeat_day' => (isset($data['day'][$i])) ? $data['day'][$i] : NULL, 'repeat_week' => (isset($data['week'][$i])) ? $data['week'][$i] : NULL, 'repeat_month' => (isset($data['month'][$i])) ? $data['month'][$i] : NULL, 'repeat_from' => $repeat_from, 'repeat_till' => $repeat_till ); if($i === 0) { $this->db->query($this->db->insert_string('recurring_orders', $recur_data)); // if($this->db->affected_rows() === 0) // { // return $this->common->setResponseMessage('There was a problem trying to save the recurring order - please try again'); // } $parent_id = $this->db->insert_id(); } else { unset($recur_data['repeat_from'], $recur_data['repeat_till']); $sql_data[] = $recur_data; } } if(!empty($sql_data)) { $this->db->insert_batch('recurring_orders', $sql_data); } $this->db->trans_complete(); if($this->db->trans_status()) { $this->db->trans_commit(); $this->common->setResponseMessage('The order was successfully added to the recurring list', true); return $parent_id; } else { $this->db->trans_rollback(); } return $this->common->setResponseMessage('There was a problem trying to save the recurring order - please try again'); } } return false; } public function copyOrder($order_id = 0, $delivery_date = NULL, $recurring_id = NULL) { if(($order_id = (int) $order_id) > 0 && ($order_info = $this->get($order_id))) { if(strlen(trim($delivery_date)) > 0 && ($delivery_date = strtotime($delivery_date . ' ' . date('H:i', $order_info->delivery_date))) !== false) { $this->copyOrderItemsToBasket($order_id); $recurring_id = (($recurring_id = (int) $recurring_id) > 0) ? $recurring_id : NULL; $order_data = array ( 'customer_id' => $order_info->customer_id, 'duplicate_order_id' => $order_id, 'ordered_date' => date('d-m-Y H:i:s', $order_info->ordered_date), 'delivery_date' => array(date('d-m-Y', $delivery_date)), 'delivery_time' => array(date('H:i', $order_info->delivery_date)), 'payment_type' => $order_info->payment_type, 'order_notes' => $order_info->delivery_notes, 'recurring_id' => $recurring_id, 'recurring_hash' => ($recurring_id) ? md5($order_info->order_number . ':' . date('dmY', strtotime('tomorrow 00:00:00'))) : NULL, 'no_confirmation_email' => true ); foreach(array('delivery', 'invoice') as $address_type) { foreach(array('first_name', 'last_name', 'business', 'address_1', 'address_2', 'address_3', 'town', 'county', 'postcode', 'country', 'email', 'phone', 'mobile') as $component_type) { $key_name = $address_type . '_' . $component_type; $order_data[$key_name] = $order_info->{$key_name}; } } return $this->saveOrder($order_data); } } return false; } public function copyOrderItemsToBasket($order_id = 0) { if(($order_id = (int) $order_id) > 0 && ($order_info = $this->get($order_id))) { $this->load->model('basket'); $order_items = $this->getOrderItems($order_info->order_id, NULL, true); $this->basket->clearBasket(); foreach($order_items as $item_info) { $this->basket->addToBasket($item_info->product_id, $item_info->quantity); } //$this->basket->setDiscount($order_info->discount); //$this->basket->setDiscountType($order_info->discount_type); return true; } return false; } public function changeDeliveryDetails($order_id = 0, $delivery_date = NULL, $delivery_type = NULL, $delivery_charges = NULL) { if(($order_id = (int) $order_id) > 0 && ($order_info = $this->get($order_id))) { $change_data = array(); // Change of delivery date if($delivery_date !== NULL && ($delivery_date = trim($delivery_date))) { $change_data['delivery_date'] = strtotime($delivery_date . ' 00:00:00'); if($order_info->delivery_date !== NULL /*&& ($order_info->delivery_date != $change_data['delivery_date']*/) { $change_data['auto_delivery_date'] = false; } } // Change of delivery type if($delivery_type !== NULL && ($delivery_type = trim($delivery_type))) { if(($delivery_type = (int) $delivery_type) > 0 && $this->getDeliveryTypes($delivery_type)) { $change_data['delivery_type'] = $delivery_type; } } // Change of delivery charges if delivery type has been set if(isset($change_data['delivery_type'])) { $default_delivery_info = $this->getDeliveryTypes($change_data['delivery_type']); // Elevate the priority level when needed, i.e. Fast Track if($default_delivery_info->priority > $order_info->priority) { $change_data['priority'] = $default_delivery_info->priority; } } // This charge will override the above if valid if($delivery_charges !== NULL) { // Blank means use the default charges if(trim($delivery_charges) === '') { // Cannot change if order is fully paid if(!$order_info->order_fully_paid && isset($default_delivery_info)) { $change_data['delivery_cost'] = ($default_delivery_info->per_item) ? ($default_delivery_info->costs * $order_info->total_quantities) : $default_delivery_info->costs; } } else { if(is_numeric($delivery_charges) && ($delivery_charges = (float) $delivery_charges) >= 0) { $change_data['delivery_cost'] = $delivery_charges; } } } if(count($change_data)) { $this->changeOrderData($order_id, $change_data); } $this->proceedOrderItemsFirstStep($order_id); return true; } return false; } public function confirmDeliveryDate($order_id = 0) { if(($order_id = (int) $order_id) > 0 && ($order_info = $this->get($order_id))) { return $this->changeDeliveryDetails($order_id, date('d-m-Y', $order_info->delivery_date)); } return false; } public function proceedOrderItemsFirstStep($order_id = 0) { if(($order_id = (int) $order_id) > 0 && ($order_info = $this->get($order_id))) { $order_items = $this->getOrderItems($order_id); foreach($order_items as $item_info) { if(($current_item_progress = $this->getOrderItemProgress($item_info->item_id, true)) && $current_item_progress->current_order == ORDER_STATUS_NEW && ($next_step = $this->getOrderItemStatusStep($item_info->item_id, 'next'))) { $this->updateOrderItemStatus($item_info->item_id, $next_step->status_id); } } return true; } return false; } public function changeOrderData($order_id = 0, $column_name = '', $data_value = NULL) { if(is_array($column_name)) { foreach($column_name as $table_column => $column_value) { $this->changeOrderData($order_id, $table_column, $column_value); } return true; } else { if(($order_id = (int) $order_id) > 0 && strlen(($column_name = trim($column_name))) && $this->db->field_exists($column_name, 'orders')) { $this->db->query($this->db->update_string('orders', array($column_name => $data_value), "`order_id` = $order_id")); return true; } } return false; } public function changeOrderPriority($order_id = 0, $priority_level = 1) { if(($order_id = (int) $order_id) > 0 && ($priority_level = (int) $priority_level) >= 1 && $priority_level <= 3) { $this->db->query($this->db->update_string('orders', array('priority' => $priority_level), "`order_id` = $order_id")); return ($this->db->affected_rows() === 1); } return false; } public function updateOrderItemStatus($item_id = 0, $status = 0, $override_date_changed = NULL) { if(($item_id = (int) $item_id) > 0 && ($item_info = $this->getOrderItems(NULL, $item_id, NULL, array('order_info' => true)))) { // Cannot change an order status once it is already dispatched // or even repeat the same status as it is // or has been flagged for review if($item_info->item_status == ORDER_STATUS_DISPATCHED || $item_info->item_status == $status || (bool) $item_info->needs_review) { return false; } $changed_date = (($override_date_changed = (int) $override_date_changed) > 1) ? $override_date_changed : time(); $sql = $this->db->insert_string('order_item_status', array ( 'item_id' => $item_id, 'updated_by' => $this->users->user_id, 'date_changed' => $changed_date, 'status' => $status )); $this->db->query($sql); return ($this->db->affected_rows() === 1); } return false; } public function setStatusesOrder($statuses_data = array()) { if(is_array($statuses_data)) { $config = $this->config; $statuses_data = array_values(array_unique(array_filter($statuses_data, function($value) use($config) { return ($config->item('order_status_' . $value)) ? (int) $value : false; }))); // Mandatory statuses array_unshift($statuses_data, ORDER_STATUS_NEW); array_push($statuses_data, ORDER_STATUS_COMPLETE, ORDER_STATUS_DISPATCHED); foreach($statuses_data as $order_index => $status_id) { $this->db->query($this->db->update_string('status_types', array('status_order' => ($order_index + 1)), "`status_id` = $status_id")); } return true; } return false; } public function saveReview($order_id = 0, $data = array()) { if(($order_id = (int) $order_id) > 0 && ($order_info = $this->get($order_id))) { if(isset($data['item'], $data['actual_item'])) { $order_item_ids = cast_int_ids($data['item']); $select_item_ids = cast_int_ids($data['actual_item']); if(count($order_item_ids) !== count($data['item']) || count($select_item_ids) !== count($data['actual_item'])) { return false; } foreach($order_item_ids as $i => $order_item_id) { if(isset($select_item_ids[$i])) { $sql_data = array ( 'product_id' => $select_item_ids[$i], 'gift_wrapped' => (isset($data['gift_wrapped'][$i])) ? (bool) $data['gift_wrapped'][$i] : false, 'gift_wrap_cost' => (isset($data['gift_wrap_cost'][$i]) && trim($data['gift_wrap_cost'][$i]) !== '' && ($gift_wrap_cost = (float) $data['gift_wrap_cost'][$i]) >= 0) ? $gift_wrap_cost : NULL, 'gift_message' => (isset($data['gift_message'][$i]) && ($gift_message = trim($data['gift_message'][$i])) !== '') ? $gift_message : NULL, 'data' => NULL ); $result = $this->db->query($this->db->update_string('order_items', $sql_data, "`item_id` = $order_item_id")); if($result) { $this->deleteOrderItemOptions($order_item_id); if(isset($data['extra_options']) && is_array($data['extra_options'])) { foreach($data['extra_options'] as $existing_option_id => $extra_id) { $this->saveOrderItemOption($order_item_id, $extra_id, 0); } } } } } $this->changeOrderData($order_id, 'needs_review', false); } if(isset($data['delivery_date']) || isset($data['delivery_type'])) { $delivery_details_data['order_id'] = $order_id; $delivery_details_data['delivery_date'] = (isset($data['delivery_date'])) ? $data['delivery_date'] : NULL; $delivery_details_data['delivery_type'] = (isset($data['delivery_type'])) ? $data['delivery_type'] : NULL; $delivery_details_data['delivery_charges'] = (isset($data['delivery_charges'])) ? $data['delivery_charges'] : NULL; call_user_func_array(array($this, 'changeDeliveryDetails'), $delivery_details_data); } return true; } return false; } public function saveDeliveryTypes($data = array()) { if(isset($data['existing_delivery'], $data['name'], $data['cost'], $data['days'], $data['priority'], $data['multiplied'])) { if(($existing_delivery_types = cast_int_ids(array_values($data['existing_delivery'])))) { $this->db->query($this->db->update_string('delivery_types', array('is_deleted' => 1), "`delivery_id` <> 1 AND `delivery_id` NOT IN (" . implode(',', $existing_delivery_types) . ")")); } foreach($data['name'] as $i => $delivery_type_name) { if(isset($data['cost'][$i], $data['days'][$i], $data['multiplied'][$i], $data['priority'][$i], $data['existing_delivery'][$i])) { $delivery_type_name = format_whitespace($delivery_type_name); if(!strlen($delivery_type_name)) { continue; } $delivery_cost = (($delivery_cost = (float) $data['cost'][$i]) >= 0) ? $delivery_cost : 0; $delivery_length = (($delivery_length = (int) $data['days'][$i]) >= 1) ? $delivery_length : 1; $costs_multiplied = (bool) $data['multiplied'][$i]; $priority_level = (($priority_level = (int) $data['priority'][$i]) >= 1 && $priority_level <= 3) ? $priority_level : 1; $existing_delivery_id = (($existing_delivery_id = (int) $data['existing_delivery'][$i]) > 0) ? $existing_delivery_id : NULL; $sql_data = array ( 'label' => $delivery_type_name, 'costs' => $delivery_cost, 'per_item' => $costs_multiplied, 'delivery_days' => $delivery_length, 'priority' => $priority_level ); if($existing_delivery_id) { $sql = $this->db->update_string('delivery_types', $sql_data, "`delivery_id` = $existing_delivery_id"); } else { $sql = $this->db->insert_string('delivery_types', $sql_data); } $result = $this->db->query($sql); } } return true; } return false; } public function getUKDeliveryRate($postcode = NULL) { if(($postcode = strtoupper(remove_whitespace($postcode))) != '' && ($postcode_length = strlen($postcode)) > 0 && $postcode_length <= 7) { $first_letter = $postcode[0]; for($i = $postcode_length; $i > 0; $i--) { $search_postcode = substr($postcode, 0, $i); $sql = "SELECT `dr`.`rate_id`, " . $this->db->escape($postcode) . " AS `search_postcode`, `dr`.`postcode` AS `rate_postcode`, `dr`.`delivery_type`, `dt`.`label` AS `delivery_label`, IF(`dr`.`override_cost` IS NOT NULL, `dr`.`override_cost`, `dt`.`costs`) AS `costs`, IF(`dr`.`override_cost` IS NOT NULL, 1, 0) AS `cost_overridden`, `dt`.`per_item`, `dt`.`delivery_days`, `dt`.`priority` FROM `delivery_rates` `dr` INNER JOIN `delivery_types` `dt` ON `dt`.`delivery_id` = `dr`.`delivery_type` WHERE SUBSTRING(`dr`.`postcode`, 1, 1) = ? AND `dr`.`postcode` = ? GROUP BY `dr`.`rate_id` ORDER BY `dr`.`postcode` DESC LIMIT 1"; $result = $this->db->query($sql, array($first_letter, $search_postcode)); if($result->num_rows()) { return $result->row(); } } return $this->common->getResponseMessage('Destination does not qualify for UK delivery'); } return false; } public function getPostcodeRates($rate_id = 0) { $limit = NULL; $sql = "SELECT `dr`.`rate_id`, `dr`.`postcode`, `dr`.`delivery_type`, `dt`.`label` AS `delivery_label`, IF(`dr`.`override_cost` IS NOT NULL, `dr`.`override_cost`, `dt`.`costs`) AS `costs`, IF(`dr`.`override_cost` IS NOT NULL, 1, 0) AS `cost_overridden`, `dt`.`per_item`, `dt`.`delivery_days`, `dt`.`priority` FROM `delivery_rates` `dr` INNER JOIN `delivery_types` `dt` ON `dt`.`delivery_id` = `dr`.`delivery_type`"; if(($rate_id = (int) $rate_id) > 0) { $sql .= " WHERE `rate_id` = $rate_id "; $limit = 1; } $sql .= " GROUP BY `dr`.`rate_id` ORDER BY `dr`.`postcode` DESC"; if($limit > 0) { $sql .= " LIMIT $limit"; } $result = $this->db->query($sql); if($result->num_rows()) { if($rate_id > 0) return $result->row(); $rows = $result->result(); $result->free_result(); return $rows; } return array(); } public function savePostcodeRate($data = array(), $rate_id = 0) { $rate_id = (($rate_id = (int) $rate_id) > 0) ? $rate_id : 0; $is_update = ($rate_id > 0); $postcode = (isset($data['postcode']) && ($postcode = strtoupper(remove_whitespace($data['postcode']))) != '') ? $postcode : NULL; if($postcode === NULL) { return $this->common->setResponseMessage('The postcode is required'); } $delivery_type = (isset($data['delivery_type']) && ($delivery_type = (int) $data['delivery_type']) > 0) ? $delivery_type : NULL; if($delivery_type !== NULL && !$this->getDeliveryTypes($delivery_type)) { return $this->common->setResponseMessage('The delivery type is required'); } $delivery_charges = (isset($data['charge']) && is_numeric($data['charge']) && ($delivery_charges = (float) $data['charge']) >= 0) ? $delivery_charges : NULL; if($this->db->query("SELECT 1 FROM `delivery_rates` WHERE `postcode` = ? AND `rate_id` <> $rate_id LIMIT 1", array($postcode))->num_rows()) { return $this->common->setResponseMessage('An entry already exists for this postcode'); } $sql_data = array ( 'postcode' => $postcode, 'delivery_type' => $delivery_type, 'override_cost' => $delivery_charges ); if($is_update) { $sql = $this->db->update_string('delivery_rates', $sql_data, "`rate_id` = $rate_id"); } else { $sql = $this->db->insert_string('delivery_rates', $sql_data); } $result = $this->db->query($sql); if(($is_update && $result) || (!$is_update && $this->db->affected_rows())) { return $this->common->setResponseMessage('The postcode rate was successfully saved', true); } return $this->common->setResponseMessage('The postcode rate could not be saved at this time - please try again'); } public function deletePostcodeRate($rate_id = 0) { if(($rate_id = (int) $rate_id) > 0) { $this->db->query("DELETE FROM `delivery_rates` WHERE `rate_id` = $rate_id"); return ($this->db->affected_rows() > 0); } return false; } public function totalQualifyFreeDelivery($amount = 0) { if(($minimum_free_amount = (float) $this->config->item('delivery_free_over')) > 0) { return ($amount >= $minimum_free_amount); } return false; } public function eligibleFreeDelivery($destination_lat = NULL, $destination_lng = NULL) { if($this->basket->basket_id > 0 && $this->totalQualifyFreeDelivery($this->basket->calculate('before-discounts'))) { return true; } else { if(isset($destination_lat, $destination_lng)) { $destination_lat = (float) $destination_lat; $destination_lng = (float) $destination_lng; foreach($this->common->getOriginLocations() as $location_info) { if($location_info->free_delivery_range > 0) { if(calculate_distance($location_info->latitude, $location_info->longitude, $destination_lat, $destination_lng) <= $location_info->free_delivery_range) { return true; } } } } } return false; } public function updateOrderNote($order_id = 0, $note_contents = NULL, $note_type = SYSTEM_NOTE_DELIVERY) { if(($order_id = (int) $order_id) > 0) { $note_contents = nullify($note_contents); switch($note_type) { default: case SYSTEM_NOTE_DELIVERY: $table_column = 'delivery_notes'; break; case SYSTEM_NOTE_INTERNAL: $table_column = 'internal_notes'; break; } $this->db->query($this->db->update_string('orders', array($table_column => $note_contents), "`order_id` = $order_id")); return ($this->db->affected_rows() === 1); } return false; } public function getOrderReviewReasons($order_info = array(), $simple_version = false) { if(($order_info = (object) $order_info)) { $reviews = array(); if(isset($order_info->auto_delivery_date) && $order_info->auto_delivery_date) $reviews['delivery_date'] = ($simple_version) ? 'Shipping date' : 'Shipping date was automatically selected'; if(!isset($order_info->delivery_type) || $order_info->delivery_type == 1) $reviews['delivery_type'] = ($simple_version) ? 'Shipping type' : 'Shipping type is required'; if(isset($order_info->needs_review) && $order_info->needs_review) $reviews['general_review'] = ($simple_version) ? 'Order items' : 'The order items/details may need reviewing'; return $reviews; } return array('none' => 'None'); } public function deleteRecurrence($recurring_id = 0, $order_id = 0) { if(($recurring_id = (int) $recurring_id) > 0 || ($order_id = (int) $order_id) > 0) { if($recurring_id) { $sql_where = "`id` = $recurring_id OR `parent_id` = $recurring_id"; } else { $sql_where = "`order_id` = $order_id"; } $this->db->query($this->db->update_string('recurring_orders', array('is_deleted' => 1), $sql_where)); return ($this->db->affected_rows()); } return false; } public function recurrenceInterval($repeat_date = NULL, $repeat_day = NULL, $repeat_week = NULL, $repeat_month = NULL, $return_data = false) { $repeat_date = array_filter(explode(',', $repeat_date)); $repeat_day = array_filter(explode(',', $repeat_day)); $repeat_week = array_filter(explode(',', $repeat_week)); $repeat_month = array_filter(explode(',', $repeat_month)); if($return_data) { return array ( 'date' => nullify($repeat_date), 'day' => nullify($repeat_day), 'week' => nullify($repeat_week), 'month' => nullify($repeat_month) ); } if(count(array_filter(array($repeat_date, $repeat_day, $repeat_week, $repeat_month))) === 0 || count($repeat_day) >= 6) { return 'Every day'; } asort($repeat_date, SORT_NUMERIC); asort($repeat_day, SORT_NUMERIC); asort($repeat_week, SORT_NUMERIC); asort($repeat_month, SORT_NUMERIC); $months_of_the_year = array ( 1 => 'January', 2 => 'February', 3 => 'March', 4 => 'April', 5 => 'May', 6 => 'June', 7 => 'July', 8 => 'August', 9 => 'September', 10 => 'October', 11 => 'November', 12 => 'December' ); $days_of_the_week = array ( 1 => 'Monday', 2 => 'Tuesday', 3 => 'Wednesday', 4 => 'Thursday', 5 => 'Friday', 6 => 'Saturday' ); $dates_of_the_month = array_combine(array_keys(array_fill(1, 31, 'date')), array_keys(array_fill(1, 31, 'date'))); $explain = array(); if(!$repeat_month) { $explain[] = 'monthly'; } else { $order_months = array_intersect_key($months_of_the_year, array_flip($repeat_month)); if(($number_of_months = count($order_months)) >= 8) { $explain[] = 'every month except ' . implode(', ', array_diff_key($order_months, array_flip($repeat_month))); } else { $explain[] = 'every ' . implode(', ', $order_months); } } if(!$repeat_week) { $explain[] = 'weekly'; } else { if(($number_of_weeks = count($repeat_week)) == 2) { $explain[] = 'fortnightly'; } elseif($number_of_weeks >= 4) { $explain[] = 'every ' . implode(', ', array_map('date_suffix', array_intersect($repeat_week, array(1, 2, 3, 4, 5)))) . ' week'; } elseif($number_of_weeks < 4) { $explain[] = 'every week except the ' . implode(', ', array_map('date_suffix', array_diff(array(1, 2, 3, 4, 5), $repeat_week))) . ' week'; } } if(!$repeat_day && !$repeat_date) { $explain[] = 'daily'; } elseif($repeat_date) { if(count($repeat_date) >= 26) { $explain[] = 'every day except ' . implode(', ', array_map('date_suffix', array_diff_key($dates_of_the_month, array_flip($repeat_date)))); } else { $explain[] = 'only ' . implode(', ', array_map('date_suffix', $repeat_date)); } } elseif($repeat_day) { $order_days = array_intersect_key($days_of_the_week, array_flip($repeat_day)); //preprint($days_of_the_week); //preprint(array_flip($repeat_day)); //preprint(array_diff_key()); //preprint($order_days); if(count($order_days) >= 3) { $explain[] = 'every day except ' . implode(', ', array_map('plural', array_diff_key($days_of_the_week, array_flip($repeat_day)))); } else { $explain[] = 'only ' . implode(', ', array_map('plural', $order_days)); } } return ucfirst((count($explain) > 1 && ($last_value = array_pop($explain))) ? implode(', ', $explain) . ' and ' . $last_value : implode(', ', $explain)); } public function getCachedOrder($order_id = 0) { if(($order_id = (int) $order_id) > 0 && ($order_data = $this->cache->get('bakery_order_' . $order_id))) { return $order_data; } return false; } public function cacheOrder($order_data) { if(isset($order_data->order_id, $order_data->order_number)) { return $this->cache->save('bakery_order_' . $order_data->order_id, $order_data); } return false; } public function deleteCachedOrder($order_id = 0) { return $this->cache->delete('bakery_order_' . $order_id); } }
Cancel