<?php
if (!defined('BASEPATH'))
    exit('No direct script access allowed');
class Stock_Counter_Model extends CI_Model
{
    public function __construct()
    {
        parent::__construct();
    }
    //new implimentdation forbatch_counting_process start 2022-08-03
    function get_actual_stock_balance($warehouse_id='',$product_id,$batch_id=''){
        $grn_total= $this->get_grn_stock($warehouse_id,$product_id,$batch_id);
        $dispatch_issue_qty=$this->get_dispatch_note_issued_stock($warehouse_id,$product_id,$batch_id);
        $product_balance=($grn_total)-($dispatch_issue_qty);
        $temp_balance= round($product_balance,3);
        if($temp_balance==(-0)){
            return 0;
        }else{
            return $temp_balance;
        }
    }
    function get_availble_stock_for_invoicing_and_order($warehouse_id='',$product_id='',$batch_id=''){
         
        $grn_total= $this->get_grn_stock($warehouse_id,$product_id,$batch_id); 
        $order_reseved_qty=$this->get_order_plan_reserve_stock($warehouse_id,$product_id,$batch_id);
        $dispatch_reseved_qty=$this->get_dispatch_note_reserve_stock($warehouse_id,$product_id,$batch_id);
        $dispatch_issue_qty=$this->get_dispatch_note_issued_stock($warehouse_id,$product_id,$batch_id);
        $product_balance=($grn_total)-($order_reseved_qty+$dispatch_reseved_qty+$dispatch_issue_qty);
        $temp_balance= round($product_balance,3);
        if($temp_balance==(-0)){
            return 0;
        }else{
            return $temp_balance;
        }
    }
    
    
    
    
    
    
    //sub function ----------------------------------
    
    function get_grn_stock($warehouse_id='',$product_id='',$batch_id='')
    {
        $this->db->select_sum('p.quantity');
        $this->db->from('btatch_prroduct_grn bg');
        $this->db->join('purchase_items p','p.id=bg.grn_item_id','inner');
        $this->db->where('p.product_id', $product_id);
        if($batch_id){
           $this->db->where('bg.batch_id', $batch_id);  
        }
        if($warehouse_id){
          $this->db->join('purchases pm','pm.id=p.purchase_id','inner');
          $this->db->where('pm.warehouse_id', $warehouse_id);
        }
        if($batch_id){
        $this->db->group_by('p.product_id');
        }
        $query = $this->db->get();
        $result=$query->row_array();
        if(isset($result['quantity']))
        {
            return $result['quantity'];
        }else{
       return 0;
        }
    }
    function get_order_plan_reserve_stock($warehouse_id='',$product_id='',$batch_id=''){
        $this->db->select_sum('oi.request_qty');
        $this->db->from('order_items oi');
        if($warehouse_id){
          $this->db->join('order_master o','o.odr_id=oi.odr_id','inner');
        }
        $this->db->join('order_plan_item pi','pi.order_id=oi.odr_id','inner');
        $this->db->join('order_plan_master opm','opm.opm_id=pi.opm_id','inner');
        $this->db->where('opm.opm_approval_status', 0);
        $this->db->where('opm.opm_status', 1);
        $this->db->where('oi.product_id', $product_id);
        if($batch_id){
         $this->db->where('oi.batch_id', $batch_id);   
        }
       if($warehouse_id){
          $this->db->where('o.odr_warehouse_id', $warehouse_id); 
       }
        $query = $this->db->get();
        $result=$query->row_array();
        if(isset($result['request_qty']))
        {
            return $result['request_qty'];
        }else{
       return 0;
        }
    }
    function get_dispatch_note_reserve_stock($warehouse_id='',$product_id='',$batch_id=''){
        $this->db->select_sum('dni.issued_qty');
        $this->db->from('dispatch_note_item dni');
        if($warehouse_id){
          $this->db->join('dispatch_note_master dpn','dpn.dpn_id=dni.dnm_id','inner');
        }
        $this->db->where('dni.product_id', $product_id);
        $this->db->where('dni.issue_status', 0);
        if($batch_id){
         $this->db->where('dni.batch_id', $batch_id);   
        }
       if($warehouse_id){
          $this->db->where('dpn.dpn_warehouse_id', $warehouse_id); 
       }
        $query = $this->db->get();
        $result=$query->row_array();
        if(isset($result['issued_qty']))
        {
            return $result['issued_qty'];
        }else{
       return 0;
        }
    }
    
    
     function get_dispatch_note_issued_stock($warehouse_id='',$product_id='',$batch_id=''){
        $this->db->select_sum('dnb.batch_qty');
        $this->db->from('dispatch_note_batch dnb');
        if($warehouse_id){
          $this->db->join('dispatch_note_item dni','dni.dni_id=dnb.dni_id','inner');
          $this->db->join('dispatch_note_master dpn','dpn.dpn_id=dni.dnm_id','inner');
        }
        $this->db->where('dnb.product_id', $product_id);
      
        if($batch_id){
         $this->db->where('dnb.batch_id', $batch_id);   
        }
       if($warehouse_id){
          $this->db->where('dpn.dpn_warehouse_id', $warehouse_id); 
       }
        $query = $this->db->get();
        $result=$query->row_array();
        if(isset($result['batch_qty']))
        {
            return $result['batch_qty'];
        }else{
       return 0;
        }
    }
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    //new implimentdation forbatch_counting_process end 2022-08-03 
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    //product full_stock_balance start--------------------------------------------------------------------------------
  /*  function get_stock_balance($warehouse_id='',$product_id){
        return $this->get_availble_stock_for_invoicing_and_order($warehouse_id='',$product_id);
        $grn_total= $this->get_grn_qty($warehouse_id,$product_id);
        $damage_total= $this->get_damage_qty($warehouse_id,$product_id);
        $grn_return_total= $this->get_grn_return_qty($warehouse_id,$product_id);
        $order_plan_reserve_qty= $this->get_order_plan_reserve_qty($warehouse_id,$product_id);
        $product_balance=((($grn_total-$order_plan_reserve_qty)-$grn_return_total)-$damage_total);
        $temp_balance= round($product_balance,3);
        if($temp_balance==(-0)){
            return 0;
        }else{
            return $temp_balance;
        }
    }
    function get_grn_qty($warehouse_id='',$product_id)
    {
        $this->db->select_sum('p.quantity');
        $this->db->from('purchase_items p');
        $this->db->where('p.product_id', $product_id);
        $query = $this->db->get();
        $result=$query->row_array();
        if(isset($result['quantity']))
        {
            return $result['quantity'];
        }else{
       return 0;
        }
    }
    
    
   
    function get_damage_qty($warehouse_id='',$product_id)
    {
        $this->db->select_sum('p.pdmgitm_quantity');
        $this->db->from('product_damage_item p');
        $this->db->where('p.product_id', $product_id);
        $query = $this->db->get();
        $result=$query->row_array();
        if(isset($result['pdmgitm_quantity']))
        {
            return $result['pdmgitm_quantity'];
        }else{
       return 0;
        }
    }
     function get_grn_return_qty($warehouse_id='',$product_id)
    {
        $this->db->select_sum('p.quantity');
        $this->db->from('purchase_return_items p');
        $this->db->where('p.product_id', $product_id);
        $query = $this->db->get();
        $result=$query->row_array();
        if(isset($result['quantity']))
        {
            return $result['quantity'];
        }else{
       return 0;
        }
    }
    
     function get_order_plan_reserve_qty($warehouse_id='',$product_id)
    {
        $this->db->select_sum('oi.request_qty');
        $this->db->from('order_items oi');
        $this->db->join('order_plan_item pi','pi.order_id=oi.odr_id','inner');
        $this->db->where('oi.product_id', $product_id);
        $query = $this->db->get();
        $result=$query->row_array();
        if(isset($result['request_qty']))
        {
            return $result['request_qty'];
        }else{
       return 0;
        }
    }
  
    
    
    //product full_stock_balance end----------------------------------------------------------------------------------------------
    
    
    //batch_stock balance start------------------------------------------------------------------------------------------------------
      function get_stock_balance_batch($warehouse_id='',$product_id,$batch_id){
        return $this->get_availble_stock_for_invoicing_and_order($warehouse_id='',$product_id='',$batch_id='');
        $grn_total= $this->get_batch_grn_qty($warehouse_id,$product_id,$batch_id);
        $issue_qty=0;// $this->get_batch_production_issue_qty($warehouse_id,$product_id,$batch_id);
        $sale_return_total= 0;//$this->get_batch_sale_return_qty($warehouse_id,$product_id,$batch_id);
        $adjesment_total= $this->get_batch_adjesment_qty($warehouse_id,$product_id,$batch_id);
        $grn_return_total= $this->get_batch_grn_return_qty($warehouse_id,$product_id,$batch_id);
        $production_issue= 0;//$this->get_production_issued_qty($warehouse_id,$product_id,$batch_id);
        
        $product_balance=($grn_total-$grn_return_total)-($issue_qty-$sale_return_total)-($production_issue)+$adjesment_total;
        $temp_balance= round($product_balance,3);
        if($temp_balance==(-0)){
            return 0;
        }else{
            return $temp_balance;
        }
    }
    function get_batch_grn_qty($warehouse_id='',$product_id,$batch_id)
    {
        $this->db->select_sum('p.quantity');
        $this->db->from('btatch_prroduct_grn bg');
        $this->db->join('purchase_items p','p.id=bg.grn_item_id','inner');
         if($warehouse_id){
          $this->db->join('purchases pm','pm.id=p.purchase_id','inner');
          $this->db->where('pm.warehouse_id', $warehouse_id);
        }
        //$this->db->where('p.product_id', $product_id);
        $this->db->where('bg.batch_id', $batch_id);
        $this->db->group_by('p.product_id');
        $query = $this->db->get();
        $result=$query->row_array();
        if(isset($result['quantity']))
        {
            return $result['quantity'];
        }else{
       return 0;
        }
    }
    function get_batch_production_issue_qty($warehouse_id='',$product_id,$batch_id)
    {
        $this->db->select_sum('p.quantity');
        $this->db->from('production_stock_in_out p');
        $this->db->where('p.product_mat_id', $product_id);
        $this->db->where('p.product_mat_batch_id', $batch_id);
        $query = $this->db->get();
        $result=$query->row_array();
        if(isset($result['quantity']))
        {
            return $result['quantity'];
        }else{
       return 0;
        }
    }
    
     function get_batch_sale_return_qty($warehouse_id='',$product_id,$batch_id)
    {
        $this->db->select_sum('p.quantity');
        $this->db->from('hw_sales_return_items p');
        $this->db->where('p.product_id', $product_id);
        $this->db->where('p.batch_id', $batch_id);
        $query = $this->db->get();
        $result=$query->row_array();
        if(isset($result['quantity']))
        {
            return $result['quantity'];
        }else{
       return 0;
        }
    }
    
     function get_batch_adjesment_qty($warehouse_id='',$product_id,$batch_id)
    {
        $this->db->select_sum('p.batch_qty');
        $this->db->from('batch_stock_adjesment p');
        $this->db->where('p.product_id', $product_id);
        $this->db->where('p.batch_id', $batch_id);
        if($warehouse_id){
             $this->db->where('p.warehouse_id', $warehouse_id);
        }
        $query = $this->db->get();
        $result=$query->row_array();
        if(isset($result['batch_qty']))
        {
            return $result['batch_qty'];
        }else{
       return 0;
        }
    }
     function get_batch_grn_return_qty($warehouse_id='',$product_id,$batch_id)
    {
        $this->db->select_sum('p.quantity');
        $this->db->from('purchase_return_items p');
         if($warehouse_id){
          $this->db->join('purchase_return pr','pr.pur_return_id=p.pur_return_id','inner');
          $this->db->where('pr.warehouse_id', $warehouse_id);
        }
        $this->db->where('p.product_id', $product_id);
        $this->db->where('p.batch_id', $batch_id);
        $query = $this->db->get();
        $result=$query->row_array();
        if(isset($result['quantity']))
        {
            return $result['quantity'];
        }else{
       return 0;
        }
    }
    
    function get_production_issued_qty($warehouse_id='',$product_id,$batch_id){
        $this->db->select_sum('b.psi_item_qty');
        $this->db->from('production_stock_issue b');
        $this->db->where("b.psi_item_id",$product_id);
        $this->db->where("b.batch_id",$batch_id);
         if($warehouse_id){
          $this->db->where('b.psi_wrehouse_id', $warehouse_id);
        }
        $query = $this->db->get();
        $result= $query->row_array();
        if(isset($result['psi_item_qty'])){
          return $result['psi_item_qty'];  
        }else{
            return 0;
        }
    }
     //product batch_stock balance end-------------------------------------------------------------------------------
     
     //product batch purcheses balance stsrt ------------------------------------------------------------------------
     
     function get_purchased_stock_balance($product_id,$grn_id){
        $grn_total= $this->get_purchased_grn_qty($product_id,$grn_id);
        $grn_return_total= $this->get_purchased_return_grn_qty_total($product_id,$grn_id);
        $product_balance=$grn_total-$grn_return_total;
        return $product_balance;
    }
     function get_purchased_grn_qty($product_id,$grn_id)
    {
        $this->db->select_sum('p.quantity');
        $this->db->from('purchase_items p');
        $this->db->where('p.product_id', $product_id);
        $this->db->where('p.id', $grn_id);
        $query = $this->db->get();
        $result=$query->row_array();
        if(isset($result['quantity']))
        {
            return $result['quantity'];
        }else{
       return 0;
        }
    }
    
    function get_manufactured_qty($product_id)
    {
        $this->db->select_sum('p.output_qty');
        $this->db->from('production_batch_items p');
        $this->db->where('p.pbi_product_id', $product_id);
        $query = $this->db->get();
        $result=$query->row_array();
        if(isset($result['output_qty']))
        {
            return $result['output_qty'];
        }else{
       return 0;
        }
    }
    function get_purchased_return_grn_qty_total($product_id,$grn_id)
    {
        $this->db->select_sum('p.quantity');
        $this->db->from('purchase_return_items p');
        $this->db->where('p.product_id', $product_id);
        $this->db->where('p.grn_item_id', $grn_id);
        $query = $this->db->get();
        $result=$query->row_array();
        if(isset($result['quantity']))
        {
            return $result['quantity'];
        }else{
       return 0;
        }
    }
     
     
     
     //product batch purcheses balance end ------------------------------------------------------------------------
     
     
     
     
     
     
     
     
     
     
     
     
     
     //new update for production final product stock count
     
      function getFinalProductStockBalance($warehouse_id='',$product_id){
        $grn_total= $this->getFinalProductStockGRN($warehouse_id,$product_id);
        $intermediate_grn_total= $this->getIntermediateConvertGRN($warehouse_id,$product_id);
        $converted_procuct_total= $this->get_onverted_product_qty($warehouse_id,$product_id);
        $manufactured= $this->get_manufactured_qty($product_id);
        
        
        $product_balance=(($grn_total+$manufactured)-$intermediate_grn_total)+($converted_procuct_total);
       
       
        $temp_balance= round($product_balance,3);
        if($temp_balance==(-0)){
            return 0;
        }else{
            return $temp_balance;
        }
    }
    function getFinalProductStockGRN($warehouse_id='',$product_id)
    {
        $this->db->select_sum('p.quantity');
        $this->db->from('production_grn_items p');
        if($warehouse_id){
            $this->db->join('production_grn g','g.pgrnm_id=p.pgrnm_id','inner');
            $this->db->where('g.pgrnm_warehouse_id', $warehouse_id);
        }
        $this->db->where('p.product_id', $product_id);
        $this->db->where('p.pgrnp_status', 1);
        $query = $this->db->get();
        $result=$query->row_array();
        if(isset($result['quantity']))
        {
            return $result['quantity'];
        }else{
       return 0;
        }
    }
     function getIntermediateConvertGRN($warehouse_id='',$product_id)
    {
        $this->db->select_sum('p.quantity');
        $this->db->from('production_grn_intermediat_product p');
        if($warehouse_id){
            $this->db->join('production_grn g','g.pgrnm_id=p.pgrnm_id','inner');
            $this->db->where('g.pgrnm_warehouse_id', $warehouse_id);
        }
        $this->db->where('p.pgtni_status', 1);
        $this->db->where('p.product_id', $product_id);
        $query = $this->db->get();
        $result=$query->row_array();
        if(isset($result['quantity']))
        {
            return $result['quantity'];
        }else{
       return 0;
        }
    }
    
     function get_onverted_product_qty($warehouse_id='',$product_id)
    {
        $this->db->select_sum('p.pc_qty');
        $this->db->from('production_convert p');
        if($warehouse_id){
            $this->db->where('p.pc_warehouse', $warehouse_id);
        }
        $this->db->where('p.pc_status', 1);
        $this->db->where('p.pc_prodcut_id', $product_id);
        $query = $this->db->get();
        $result=$query->row_array();
        if(isset($result['pc_qty']))
        {
            return $result['pc_qty'];
        }else{
       return 0;
        }
    }
     
     
     */
     
     
     
   
}