<?php defined('BASEPATH') OR exit('No direct script access allowed');

class Transfers_model extends CI_Model
{

    public function __construct()
    {
        parent::__construct();
    }

    public function getProductNames($term, $warehouse_id, $limit = 5)
    {
        $this->db->select('products.id, code, name, warehouses_products.quantity, cost, tax_rate, type, unit, purchase_unit, tax_method')
            ->join('warehouses_products', 'warehouses_products.product_id=products.id', 'left')
            ->group_by('products.id');
        if ($this->Settings->overselling) {
            $this->db->where("type = 'standard' AND (name LIKE '%" . $term . "%' OR code LIKE '%" . $term . "%' OR  concat(name, ' (', code, ')') LIKE '%" . $term . "%')");
        } else {
            $this->db->where("type = 'standard' AND warehouses_products.warehouse_id = '" . $warehouse_id . "' AND warehouses_products.quantity > 0 AND "
                . "(name LIKE '%" . $term . "%' OR code LIKE '%" . $term . "%' OR  concat(name, ' (', code, ')') LIKE '%" . $term . "%')");
        }
        $this->db->limit($limit);
        $q = $this->db->get('products');
        if ($q->num_rows() > 0) {
            foreach (($q->result()) as $row) {
                $data[] = $row;
            }
            return $data;
        }
    }
        
     public function UpdateSaleDailyOffline($daily_product= array()) 
   {
       
   
        foreach ($daily_product as $daily)
        {
            $warehouse  = $daily['warehouse_id'];
            $product    = $daily['product_id'];
            $date       = $daily['compare_date'];
            $from_warehouse = $daily['from_warehouse'];
            
            if($this->getUserWarehouseID($warehouse))
            {
                $this->db->select('*'); 
                $this->db->from('daily_stock');
                $this->db->where('compare_date', $daily['compare_date']);
                $this->db->where('product_id', $daily['product_id']);
                $this->db->where('warehouse_id', $daily['warehouse_id']);
                $query = $this->db->get();
            
                if ($query->num_rows() == 0)
                {
                    $user_id = $this->db->get_where('users',array('warehouse_id'=>$warehouse))->row()->id;
                    $this->db->select('status, date');
                    $this->db->where('user_id', $user_id);
                    $this->db->order_by('id', 'DESC');
                    $this->db->limit('1');
                    $statu = $this->db->get('pos_register');//print_r($status->row()->status);die();
                    if(($statu->row()->status)=='open')
                    {
                        $prdocts=array();
                        $procts =$this->db->get_where('warehouses_products',array('warehouse_id'=>$warehouse,'product_id'=> $product))->result_array();//print_r($procts);die();
                        if(!empty($procts))
                        { 
                            foreach($procts as $row)
                            {
                                $product_id     = $row['product_id'];
                                $product_qty    = $row['quantity'];
                                $product_name   = $this->db->get_where('products',array('id'=>$product_id))->row()->name;
                                $unit_price     = $this->db->get_where('products',array('id'=>$product_id))->row()->price;
                                $brand          = $this->db->get_where('products',array('id'=>$product_id))->row()->brand;
                                $prot_qty       = $product_qty + $daily['purchase_quantity'];
         
                                $data =array(
        					    	'product_id'        => $product_id,
                                    'product_name'      => $product_name,
                                    'date'              => $daily['date'],
                                    'compare_date'      => $date,
                                    'warehouse_id'      => $warehouse,
                                    'brand'             => $brand,
                                    'unit_price'        => $unit_price,
                                    'previous_stock'    => $prot_qty,
                                    'current_stock'     => $prot_qty
                                    );
        	                    $this->db->insert('daily_stock', $data);
                            }
                        }
                        else
                        { 
                            $product_id     = $product;
                            $product_qty    = $daily['purchase_quantity'];
                            $product_name   = $this->db->get_where('products',array('id'=>$product))->row()->name;
                            $unit_price     = $this->db->get_where('products',array('id'=>$product))->row()->price;
                            $brand          = $this->db->get_where('products',array('id'=>$product))->row()->brand;
                            $data =array(
        						'product_id'        => $product_id,
                                'product_name'      => $product_name,
                                'date'              => $daily['date'],
                                'compare_date'      => $date,
                                'warehouse_id'      => $warehouse,
                                'brand'             => $brand,
                                'unit_price'        => $unit_price,
                                'previous_stock'    => $product_qty,
                                'current_stock'     => $product_qty
                                );
        	                $this->db->insert('daily_stock', $data);
                        }
                    }
                    else
                    {
                        $product_id     = $product;
                        $product_qty    = $daily['purchase_quantity'];
                        $product_name   = $this->db->get_where('products',array('id'=>$product))->row()->name;
                        $unit_price     = $this->db->get_where('products',array('id'=>$product))->row()->price;
                        $brand          = $this->db->get_where('products',array('id'=>$product))->row()->brand;
                    
                        $data =array(
        						'product_id'        => $product_id,
                                'product_name'      => $product_name,
                                'date'              => $daily['date'],
                                'compare_date'      => $date,
                                'warehouse_id'      => $warehouse,
                                'brand'             => $brand,
                                'unit_price'        => $unit_price,
                                'previous_stock'    => '0',
                                'current_stock'     =>$product_qty,
                                'purchase_quantity	'  =>  $daily['purchase_quantity'],
                            );
        	            $this->db->insert('daily_stock', $data);
                  
                    }
                }
            
                else
                {
                    $user_id = $this->db->get_where('users',array('warehouse_id'=>$warehouse))->row()->id;
                    $this->db->select('status, date');
                    $this->db->where('user_id', $user_id);
                    $this->db->order_by('id', 'DESC');
                    $this->db->limit('1');
                    $status = $this->db->get('pos_register');
                    if(($status->row()->status)=='open')
                    { 
                        $previous_stock =  $this->db->get_where('daily_stock',array('product_id'=> $product,'warehouse_id'=>$warehouse,'compare_date'=>$date))->row()->previous_stock;
                        $previous_stock = $previous_stock + $daily['purchase_quantity'];
                        $cur_qty_tot    =  $this->db->get_where('daily_stock',array('product_id'=> $product,'warehouse_id'=>$warehouse,'compare_date'=>$date))->row()->current_stock;
                        $cur_qty        = $cur_qty_tot + $daily['purchase_quantity'];
                        $this->db->where('warehouse_id', $warehouse);
                        $this->db->where('product_id', $product);
                        $this->db->where('compare_date', $date);
                        $this->db->update('daily_stock',array(
                                                'previous_stock	'  => $previous_stock,
                                                'current_stock'=>$cur_qty
                                            
                                            ));
                    }
                    else
                    {
                        $tomrw          = date('Y-m-d', strtotime('+1 day', strtotime($date)));
                        $previous_stock =  $this->db->get_where('daily_stock',array('product_id'=> $product,'warehouse_id'=>$warehouse,'compare_date'=>$tomrw))->row()->previous_stock;
                        $previous_stock = $previous_stock + $daily['purchase_quantity'];
                        $this->db->where('warehouse_id', $warehouse);
                        $this->db->where('product_id', $product);
                        $this->db->where('compare_date', $tomrw);
                        $this->db->update('daily_stock',array(
                                            'previous_stock	'  => $previous_stock,
                                            'current_stock'=>$previous_stock,
                                             
                                        ));
                                      
                        $this->db->where('warehouse_id', $warehouse);
                        $this->db->where('product_id', $product);
                        $this->db->where('compare_date', $date);
                        $this->db->update('daily_stock',array(
                                          
                                             'purchase_quantity	'  =>  $daily['purchase_quantity'],
                                        ));
                    }
                }
            }
            else
            {
             
                $previous_stock     =  $this->db->get_where('daily_stock',array('product_id'=> $product,'warehouse_id'=>$from_warehouse,'compare_date'=>$date))->row()->previous_stock;
                $previous_stock     = $previous_stock - $daily['purchase_quantity'];
                $current_stock      =  $this->db->get_where('daily_stock',array('product_id'=> $product,'warehouse_id'=>$from_warehouse,'compare_date'=>$date))->row()->current_stock;
                $current_stock      = $current_stock - $daily['purchase_quantity'];
                $this->db->where('warehouse_id', $from_warehouse);
                $this->db->where('product_id', $product);
                $this->db->where('compare_date', $date);
                $this->db->update('daily_stock',array(
                                            'previous_stock	'  => $previous_stock,
                                            'current_stock'=>$current_stock,
                                             
                                ));
                                     
            }
        }
          
   }
  
    public function getWHProduct($id)
    {
        $this->db->select('products.id, code, name, warehouses_products.quantity, cost, tax_rate')
            ->join('warehouses_products', 'warehouses_products.product_id=products.id', 'left')
            ->group_by('products.id');
        $q = $this->db->get_where('products', array('warehouses_products.product_id' => $id), 1);
        if ($q->num_rows() > 0) {
            return $q->row();
        }

        return FALSE;
    }
    public function getUserWarehouseID($id) 
	{
        $this->db->select('id');
        $q = $this->db->get_where('users', array('warehouse_id' => $id), 1);
        if ($q->num_rows() > 0) 
		{
            return $q->row();
        }
        return FALSE;
    }

    public function addTransfer($data = array(), $items = array(),$daily_product=array())
    {
        $status = $data['status'];
        if ($this->db->insert('transfers', $data)) {
            $transfer_id = $this->db->insert_id();
            // $this->UpdateSaleDailyOffline($daily_product);
            if ($this->site->getReference('to') == $data['transfer_no']) {
                $this->site->updateReference('to');
            }
            foreach ($items as $item) {
                $item['transfer_id'] = $transfer_id;
                $item['option_id'] = !empty($item['option_id']) && is_numeric($item['option_id']) ? $item['option_id'] : NULL;
                if ($status == 'completed') {
                    $item['date'] = date('Y-m-d');
                    $item['warehouse_id'] = $data['to_warehouse_id'];
                    $item['status'] = 'received';
                    $this->db->insert('purchase_items', $item);
                } else {
                    $this->db->insert('transfer_items', $item);
                }

                if ($status == 'sent' || $status == 'completed') {
                    $this->syncTransderdItem($item['product_id'], $data['from_warehouse_id'], $item['quantity'], $item['option_id']);
                }
            }

            return true;
        }
        return false;
    }

    public function updateTransfer($id, $data = array(), $items = array())
    {
        $ostatus = $this->resetTransferActions($id);
        $status = $data['status'];

        if ($this->db->update('transfers', $data, array('id' => $id))) {
            $tbl = $ostatus == 'completed' ? 'purchase_items' : 'transfer_items';
            $this->db->delete($tbl, array('transfer_id' => $id));

            foreach ($items as $item) {
                $item['transfer_id'] = $id;
                $item['option_id'] = !empty($item['option_id']) && is_numeric($item['option_id']) ? $item['option_id'] : NULL;
                if ($status == 'completed') {
                    $item['date'] = date('Y-m-d');
                    $item['warehouse_id'] = $data['to_warehouse_id'];
                    $item['status'] = 'received';
                    $this->db->insert('purchase_items', $item);
                } else {
                    $this->db->insert('transfer_items', $item);
                }

                if ($data['status'] == 'sent' || $data['status'] == 'completed') {
                    $this->syncTransderdItem($item['product_id'], $data['from_warehouse_id'], $item['quantity'], $item['option_id']);
                }

            }

            return true;
        }

        return false;
    }

    public function updateStatus($id, $status, $note)
    {
        $ostatus = $this->resetTransferActions($id);
        $transfer = $this->getTransferByID($id);
        $items = $this->getAllTransferItems($id, $transfer->status);

        if ($this->db->update('transfers', array('status' => $status, 'note' => $note), array('id' => $id))) {
            $tbl = $ostatus == 'completed' ? 'purchase_items' : 'transfer_items';
            $this->db->delete($tbl, array('transfer_id' => $id));

            foreach ($items as $item) {
                $item = (array) $item;
                $item['transfer_id'] = $id;
                $item['option_id'] = !empty($item['option_id']) && is_numeric($item['option_id']) ? $item['option_id'] : NULL;
                unset($item['id'], $item['variant'], $item['unit'], $item['hsn_code']);
                if ($status == 'completed') {
                    $item['date'] = date('Y-m-d');
                    $item['warehouse_id'] = $transfer->to_warehouse_id;
                    $item['status'] = 'received';
                    $this->db->insert('purchase_items', $item);
                } else {
                    $this->db->insert('transfer_items', $item);
                }

                if ($status == 'sent' || $status == 'completed') {
                    $this->syncTransderdItem($item['product_id'], $transfer->from_warehouse_id, $item['quantity'], $item['option_id']);
                } else {
                    $this->site->syncQuantity(NULL, NULL, NULL, $item['product_id']);
                }
            }
            return true;
        }
        return false;
    }

    public function getProductWarehouseOptionQty($option_id, $warehouse_id)
    {
        $q = $this->db->get_where('warehouses_products_variants', array('option_id' => $option_id, 'warehouse_id' => $warehouse_id), 1);
        if ($q->num_rows() > 0) {
            return $q->row();
        }
        return FALSE;
    }

    public function getProductByCategoryID($id)
    {

        $q = $this->db->get_where('products', array('category_id' => $id), 1);
        if ($q->num_rows() > 0) {
            return true;
        }

        return FALSE;
    }

    public function getProductQuantity($product_id, $warehouse = DEFAULT_WAREHOUSE)
    {
        $q = $this->db->get_where('warehouses_products', array('product_id' => $product_id, 'warehouse_id' => $warehouse), 1);
        if ($q->num_rows() > 0) {
            return $q->row_array(); //$q->row();
        }
        return FALSE;
    }

    public function insertQuantity($product_id, $warehouse_id, $quantity)
    {
        if ($this->db->insert('warehouses_products', array('product_id' => $product_id, 'warehouse_id' => $warehouse_id, 'quantity' => $quantity))) {
            $this->site->syncProductQty($product_id, $warehouse_id);
            return true;
        }
        return false;
    }

    public function updateQuantity($product_id, $warehouse_id, $quantity)
    {
        if ($this->db->update('warehouses_products', array('quantity' => $quantity), array('product_id' => $product_id, 'warehouse_id' => $warehouse_id))) {
            $this->site->syncProductQty($product_id, $warehouse_id);
            return true;
        }
        return false;
    }

    public function getProductByCode($code)
    {

        $q = $this->db->get_where('products', array('code' => $code), 1);
        if ($q->num_rows() > 0) {
            return $q->row();
        }

        return FALSE;
    }

    public function getProductByName($name)
    {

        $q = $this->db->get_where('products', array('name' => $name), 1);
        if ($q->num_rows() > 0) {
            return $q->row();
        }

        return FALSE;
    }

    public function getTransferByID($id)
    {

        $q = $this->db->get_where('transfers', array('id' => $id), 1);
        if ($q->num_rows() > 0) {
            return $q->row();
        }

        return FALSE;
    }

    public function getAllTransferItems($transfer_id, $status)
    {
        if ($status == 'completed') {
            $this->db->select('purchase_items.*, product_variants.name as variant, products.unit, products.hsn_code as hsn_code, products.second_name as second_name')
                ->from('purchase_items')
                ->join('products', 'products.id=purchase_items.product_id', 'left')
                ->join('product_variants', 'product_variants.id=purchase_items.option_id', 'left')
                ->group_by('purchase_items.id')
                ->where('transfer_id', $transfer_id);
        } else {
            $this->db->select('transfer_items.*, product_variants.name as variant, products.unit, products.hsn_code as hsn_code, products.second_name as second_name')
                ->from('transfer_items')
                ->join('products', 'products.id=transfer_items.product_id', 'left')
                ->join('product_variants', 'product_variants.id=transfer_items.option_id', 'left')
                ->group_by('transfer_items.id')
                ->where('transfer_id', $transfer_id);
        }
        $q = $this->db->get();
        if ($q->num_rows() > 0) {
            foreach (($q->result()) as $row) {
                $data[] = $row;
            }
            return $data;
        }
    }

    public function getWarehouseProduct($warehouse_id, $product_id, $variant_id)
    {
        if ($variant_id) {
            return $this->getProductWarehouseOptionQty($variant_id, $warehouse_id);
        } else {
            return $this->getWarehouseProductQuantity($warehouse_id, $product_id);
        }
        return FALSE;
    }

    public function getWarehouseProductQuantity($warehouse_id, $product_id)
    {
        $q = $this->db->get_where('warehouses_products', array('warehouse_id' => $warehouse_id, 'product_id' => $product_id), 1);
        if ($q->num_rows() > 0) {
            return $q->row();
        }
        return FALSE;
    }

    public function resetTransferActions($id, $delete = NULL)
    {
        $otransfer = $this->getTransferByID($id);
        $oitems = $this->getAllTransferItems($id, $otransfer->status);
        $ostatus = $otransfer->status;
        if ($ostatus == 'sent' || $ostatus == 'completed') {
            foreach ($oitems as $item) {
                $option_id = (isset($item->option_id) && ! empty($item->option_id)) ? $item->option_id : NULL;
                $clause = array('purchase_id' => NULL, 'transfer_id' => NULL, 'product_id' => $item->product_id, 'warehouse_id' => $otransfer->from_warehouse_id, 'option_id' => $option_id);
                $this->site->setPurchaseItem($clause, $item->quantity);
                if ($delete) {
                    $option_id = (isset($item->option_id) && ! empty($item->option_id)) ? $item->option_id : NULL;
                    $clause = array('purchase_id' => NULL, 'transfer_id' => NULL, 'product_id' => $item->product_id, 'warehouse_id' => $otransfer->to_warehouse_id, 'option_id' => $option_id);
                    $this->site->setPurchaseItem($clause, ($item->quantity_balance - $item->quantity));
                }
            }
        }
        return $ostatus;
    }

    public function deleteTransfer($id)
    {
        $ostatus = $this->resetTransferActions($id, 1);
        $oitems = $this->getAllTransferItems($id, $ostatus);
        $tbl = $ostatus == 'completed' ? 'purchase_items' : 'transfer_items';
        if ($this->db->delete('transfers', array('id' => $id)) && $this->db->delete($tbl, array('transfer_id' => $id))) {
            foreach ($oitems as $item) {
                $this->site->syncQuantity(NULL, NULL, NULL, $item->product_id);
            }
            return true;
        }
        return FALSE;
    }

    public function getProductOptions($product_id, $warehouse_id, $zero_check = TRUE)
    {
        $this->db->select('product_variants.id as id, product_variants.name as name, product_variants.cost as cost, product_variants.quantity as total_quantity, warehouses_products_variants.quantity as quantity')
            ->join('warehouses_products_variants', 'warehouses_products_variants.option_id=product_variants.id', 'left')
            ->where('product_variants.product_id', $product_id)
            ->where('warehouses_products_variants.warehouse_id', $warehouse_id)
            ->group_by('product_variants.id');
        if ($zero_check) {
            $this->db->where('warehouses_products_variants.quantity >', 0);
        }
        $q = $this->db->get('product_variants');
        if ($q->num_rows() > 0) {
            foreach (($q->result()) as $row) {
                $data[] = $row;
            }
            return $data;
        }
        return FALSE;
    }

    public function getProductComboItems($pid, $warehouse_id)
    {
        $this->db->select('products.id as id, combo_items.item_code as code, combo_items.quantity as qty, products.name as name, warehouses_products.quantity as quantity')
            ->join('products', 'products.code=combo_items.item_code', 'left')
            ->join('warehouses_products', 'warehouses_products.product_id=products.id', 'left')
            ->where('warehouses_products.warehouse_id', $warehouse_id)
            ->group_by('combo_items.id');
        $q = $this->db->get_where('combo_items', array('combo_items.product_id' => $pid));
        if ($q->num_rows() > 0) {
            foreach (($q->result()) as $row) {
                $data[] = $row;
            }

            return $data;
        }
        return FALSE;
    }

    public function getProductVariantByName($name, $product_id)
    {
        $q = $this->db->get_where('product_variants', array('name' => $name, 'product_id' => $product_id), 1);
        if ($q->num_rows() > 0) {
            return $q->row();
        }
        return FALSE;
    }

    public function syncTransderdItem($product_id, $warehouse_id, $quantity, $option_id = NULL)
    {
        if ($pis = $this->site->getPurchasedItems($product_id, $warehouse_id, $option_id)) {
            $balance_qty = $quantity;
            foreach ($pis as $pi) {
                if ($balance_qty <= $quantity && $quantity > 0) {
                    if ($pi->quantity_balance >= $quantity) {
                        $balance_qty = $pi->quantity_balance - $quantity;
                        $this->db->update('purchase_items', array('quantity_balance' => $balance_qty), array('id' => $pi->id));
                        $quantity = 0;
                    } elseif ($quantity > 0) {
                        $quantity = $quantity - $pi->quantity_balance;
                        $balance_qty = $quantity;
                        $this->db->update('purchase_items', array('quantity_balance' => 0), array('id' => $pi->id));
                    }
                }
                if ($quantity == 0) { break; }
            }
        } else {
            $clause = array('purchase_id' => NULL, 'transfer_id' => NULL, 'product_id' => $product_id, 'warehouse_id' => $warehouse_id, 'option_id' => $option_id);
            $this->site->setPurchaseItem($clause, (0-$quantity));
        }
        $this->site->syncQuantity(NULL, NULL, NULL, $product_id);
    }

    public function getProductOptionByID($id)
    {
        $q = $this->db->get_where('product_variants', array('id' => $id), 1);
        if ($q->num_rows() > 0) {
            return $q->row();
        }
        return FALSE;
    }

}
