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