PHP File Manager
Editing File: Stocks.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 Stocks extends CI_Model { private $stock_statuses = array ( STOCK_OUT_OF_STOCK, STOCK_LEVEL_LOW, STOCK_LEVEL_MODERATE, STOCK_LEVEL_HIGH ); public function getStock($item_id = 0, $product_id = 0, $filters = array(), $skip = 0, $limit = 30) { $filters = array_merge(array ( 'level' => NULL, 'count' => false ), (array) $filters); $sql = "SELECT *, IFNULL(ROUND(((`si`.`level` / " . $this->config->item(STOCK_LEVEL_HIGH) . ") * 100), 2), 0) AS `level_percentage` FROM ( SELECT `p`.`product_id`, NULL AS `item_id`, `p`.`name`, `sc`.`date`, IFNULL(`sc`.`level`, 0) AS `level`, IF(`sc2`.`date` IS NOT NULL, `sc2`.`date`, `sc`.`date`) AS `last_checked`, `sc2`.`level` AS `previous_level` FROM ( `products` `p` LEFT OUTER JOIN ( SELECT `check_id`, `item_id`, `product_id`, `date`, `level` FROM `stock_checks` WHERE `product_id` IS NOT NULL ORDER BY `date` DESC ) AS `sc` ON `sc`.`product_id` = `p`.`product_id` LEFT OUTER JOIN ( SELECT `check_id`, `item_id`, `product_id`, `date`, `level` FROM `stock_checks` WHERE `product_id` IS NOT NULL ORDER BY `date` DESC ) AS `sc2` ON `sc2`.`product_id` = `p`.`product_id` AND `sc2`.`check_id` < `sc`.`check_id` ) WHERE `p`.`product_id` > 1 AND `p`.`parent_id` IS NULL AND `p`.`item_group` = 'SI' GROUP BY `p`.`product_id`"; $sql .= " UNION "; $sql .= "SELECT `p`.`product_id`, `si`.`item_id`, `p`.`name`, `sc`.`date`, `sc`.`level`, `sc2`.`date` AS `last_checked`, `sc2`.`level` AS `previous_level` FROM ( `stock_items` `si` INNER JOIN `products` `p` ON `p`.`product_id` = `si`.`product_id` LEFT OUTER JOIN ( SELECT `check_id`, `item_id`, `product_id`, `date`, `level` FROM `stock_checks` WHERE `item_id` IS NOT NULL ORDER BY `date` DESC ) AS `sc` ON `sc`.`item_id` = `si`.`item_id` LEFT OUTER JOIN ( SELECT `check_id`, `item_id`, `product_id`, `date`, `level` FROM `stock_checks` WHERE `item_id` IS NOT NULL ORDER BY `date` DESC ) AS `sc2` ON `sc2`.`item_id` = `si`.`item_id` AND `sc2`.`check_id` < `sc`.`check_id` ) GROUP BY `si`.`item_id` ) AS `si`"; if(($stock_level = $filters['level']) !== NULL && in_array($stock_level, $this->stock_statuses)) { $min_level = $this->config->item($stock_level); if($stock_level == STOCK_OUT_OF_STOCK) { $sql .= " HAVING ( `level` IS NULL OR `level` = 0 ) "; } elseif($stock_level == STOCK_LEVEL_HIGH) { $sql .= " HAVING `level` >= $min_level "; } else { switch($stock_level) { case STOCK_LEVEL_LOW: $min_level = $this->config->item(STOCK_OUT_OF_STOCK); $max_level = $this->config->item(STOCK_LEVEL_LOW); break; case STOCK_LEVEL_MODERATE: $min_level = $this->config->item(STOCK_LEVEL_LOW); $max_level = $this->config->item(STOCK_LEVEL_MODERATE); break; } $sql .= " HAVING ( `level` > $min_level AND `level` <= $max_level ) "; } } else { if(($item_id = (int) $item_id) > 0) { $sql .= " HAVING `item_id` = $item_id "; } if(($product_id = (int) $product_id) > 0) { $sql .= " HAVING `product_id` = $product_id "; } } if($filters['count'] === true) { return $this->common->countPreviousQuery($sql); } $sql .= " ORDER BY `level` DESC, `name` "; $sql .= $this->common->returnOffset($skip, $limit); $result = $this->db->query($sql); //preprint($this->db->last_query()); if($result->num_rows()) { if($item_id > 0 || $product_id > 0) return $result->row(); $rows = $result->result(); $result->free_result(); return $rows; } return array(); } public function updateReading($item_id = 0, $product_id = 0, $new_level = 0, $related_order = NULL) { if((($item_id = (int) $item_id) > 0 || ($product_id = (int) $product_id) > 0) && ($item_info = $this->getStock($item_id, $product_id))) { $new_level = (($new_level = (int) $new_level) >= 0) ? $new_level : 0; $related_order = (($related_order = (int) $related_order) > 0 && $this->orders->get($related_order)) ? $related_order : NULL; $item_id = ($item_id > 0) ? $item_id : NULL; $product_id = ($product_id > 0) ? $product_id : NULL; $this->db->insert('stock_checks', array ( 'item_id' => $item_id, 'product_id' => $product_id, 'order_id' => $related_order, 'date' => time(), 'level' => $new_level )); return ($this->db->affected_rows() === 1); } return false; } public function getReadingHistory($item_id = 0) { $sql = "SELECT `sc`.* FROM `stock_checks` `sc` WHERE `sc`.`item_id` = $item_id AND `sc`.`before_unlimited` = 0 ORDER BY `sc`.`date`"; $result = $this->db->query($sql); if($result->num_rows()) { $rows = $result->result(); $result->free_result(); return $rows; } return array(); } public function deductStockAmount($item_id = 0, $product_id = 0, $amount = 0, $related_order = 0) { if((($item_id = (int) $item_id) > 0 || ($product_id = (int) $product_id) > 0) && ($amount = (float) $amount) > 0 && ($stock_info = $this->getStock($item_id, $product_id))) { $new_amount = (($new_amount = $stock_info->level - $amount) <= 0) ? 0 : $new_amount; return $this->updateReading($item_id, $product_id, $new_amount, $related_order); } return false; } public function updateStocksFromOrder($order_id = 0) { if(($order_id = (int) $order_id) > 0 && ($order_items = $this->orders->getOrderItems($order_id))) { foreach($order_items as $item_info) { if($this->getStock(NULL, $item_info->product_id)) { $this->deductStockAmount(NULL, $item_info->product_id, $item_info->quantity, $order_id); } } } return false; } public function stockLevelCount($level = NULL) { if($level !== NULL && in_array($level, $this->stock_statuses)) { return $this->getStock(NULL, NULL, array('level' => $level, 'count' => true)); } return 0; } public function itemsComboBox($name = '', $selected = array(), $attributes = '') { $items = $this->getStock(NULL, NULL, false, -1); $options[''] = '---'; foreach($items as $item_info) { $options[$item_info->item_id] = $item_info->item; } asort($options); return form_dropdown($name, $options, $selected, $attributes); } }
Cancel