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

class Stock_management {

  function __construct()
  {
    ini_set('max_execution_time', 5000);
    ini_set("memory_limit", "-1");


    $CI =& get_instance();
    $CI->CI =& get_instance();
    $CI->CI->load->library('session');
    $CI->load->model('User_model', 'User');
    $CI->load->model('Work_model', 'Work');
    $CI->load->model('Client_model', 'Client');
    $CI->load->model('Supplier_model', 'Supplier');
    $CI->load->model('Purchase_bill_model', 'Purchases');
    $CI->load->model('Purchase_order_model', 'Purchase_order');
    $CI->load->model('Material_model', 'Material');
    $CI->load->model('Stock_model', 'Stock');
    $CI->load->model('Subcontracts_model', 'Subcontracts');
    $CI->load->model('Sales_invoice_model','Sales_invoice');
    $CI->load->model('Production_model', 'Production');
    $CI->load->library('Bills_management','bills_management');
  }


  public function calculate_stock_balance($stock_id){
    $CI =& get_instance();
    $stock_data = $CI->Stock->select_stock_register("",array("sr.stock_id"=>$stock_id));
    if($stock_data->num_rows()==1){
      $stock_data = $stock_data->row();
      $total_opening_qty = $stock_data->total_opening_qty;
      $total_purchased_qty = $stock_data->total_purchased_qty;
      $data['total_production_qty'] = $CI->Production->select_production("COALESCE(SUM(p.production_qty),0) as total", array("p.stock_id"=>$stock_data->stock_id,"sr.material_id"=>$stock_data->material_id,"sr.unit"=>$stock_data->unit))->row()->total;
      $data['total_transferred_in_qty'] = $CI->Stock->select_stock_transfers("COALESCE(SUM(st.transferred_qty),0) as total", array("st.transferred_to_stock_id"=>$stock_data->stock_id,"sr1.material_id"=>$stock_data->material_id,"sr1.unit"=>$stock_data->unit))->row()->total;
      $data['total_transferred_out_qty'] = $CI->Stock->select_stock_transfers("COALESCE(SUM(st.transferred_qty),0) as total", array("st.transferred_from_stock_id"=>$stock_data->stock_id,"sr1.material_id"=>$stock_data->material_id,"sr1.unit"=>$stock_data->unit))->row()->total;
      $data['total_damaged_qty'] = $CI->Stock->select_stock_damages("COALESCE(SUM(sd.damage_qty),0) as total", array("sd.stock_id"=>$stock_data->stock_id))->row()->total;
      $data['total_consumption_qty'] = $CI->Stock->select_stock_consumption("COALESCE(SUM(sc.consumption_qty),0) as total", array("sc.stock_id"=>$stock_data->stock_id))->row()->total;
      $data['total_consumption_qty'] += $CI->Subcontracts->select_material_issue_items("COALESCE(SUM(smi.quantity),0) as total", array("smi.stock_id"=>$stock_data->stock_id))->row()->total;
      $data['total_consumption_qty'] -= $CI->Subcontracts->select_material_return("COALESCE(SUM(smr.returned_qty),0) as total", array("smi.stock_id"=>$stock_data->stock_id))->row()->total;
      $data['total_purchase_return_qty'] = $CI->Purchases->select_purchase_return("COALESCE(SUM(pr.returned_qty),0) as total", array("pr.stock_id"=>$stock_data->stock_id))->row()->total;
      $data['total_sold_qty'] = $CI->Sales_invoice->select_invoice_sale_item("COALESCE(SUM(sii.quantity),0) as total", array("sii.stock_id"=>$stock_data->stock_id))->row()->total;
      $data['total_balance_qty'] = $data['total_production_qty']+$total_opening_qty+$total_purchased_qty+$data['total_transferred_in_qty']-$data['total_transferred_out_qty']-$data['total_damaged_qty']-$data['total_consumption_qty']-$data['total_sold_qty']-$data['total_purchase_return_qty'];
      $data['stock_id'] = $stock_id;
      $result = $CI->Stock->update_stock_register($data);
    }
  }
  public function update_stock_register($purchase_id,$created_by=''){
    $CI =& get_instance();
    // Select Purchase Bill Items
    $total_items = $CI->Purchases->select_purchase_items("pi.*,p.entry_approval_status,m.material_name,p.location_id,p.supplier_id,p.delete_status as bill_delete_status,pi.delete_status as item_delete_status", array("pi.purchase_id" => $purchase_id),'','','all',$CI->config->item('select_data_entry_approval'),'',$CI->config->item('select_data_entry_approval'));
    foreach ($total_items->result() as $key => $value) {
      $stock_data = array();
      $stock_data['purchase_item_id'] = $value->item_id;
      $stock_data['purchase_supplier_id'] = $value->supplier_id;
      $stock_data['location_id'] = $value->location_id;
      $stock_data['stock_name'] = $value->material_name;
      $stock_data['material_id'] = $value->material_id;
      $stock_data['stock_description'] = $value->description;
      $stock_data['unit'] = $value->unit_id;
      if($value->total_amount!=0 && $value->quantity!=0){
        $stock_data['unit_price'] = $value->total_amount/$value->quantity;
      }
      if($value->entry_approval_status!=1 || $value->bill_delete_status==1 || $value->item_delete_status==1){
        $stock_data['total_purchased_qty'] = 0;
      }else{
        $stock_data['total_purchased_qty'] = $value->quantity;
      }

      //  Check Already Exist
      $stock_count = $CI->Stock->select_stock_register("",array("sr.purchase_item_id"=>$stock_data['purchase_item_id']));
      if($stock_count->num_rows()==0){
        // Create New Entry
        $stock_data['created_date'] = date('Y-m-d H:i:s');
        if($created_by!=''){
          $stock_data['created_by'] = $created_by;
        }else{
          $stock_data['created_by'] = $CI->session->userdata('user_id');
        }
        $result = $CI->Stock->insert_stock_register($stock_data);
        $CI->stock_management->calculate_stock_balance($result['insert_id']);
      }else{
        // Update Existing Entry
        $stock_data['updated_date'] = date('Y-m-d H:i:s');
        if($created_by!=''){
          $stock_data['updated_by'] = $created_by;
        }else{
          $stock_data['updated_by'] = $CI->session->userdata('user_id');
        }
        $stock_data['stock_id'] = $stock_count->row()->stock_id;
        $result = $CI->Stock->update_stock_register($stock_data);
        $CI->stock_management->calculate_stock_balance($stock_data['stock_id']);
      }
    }
  }
  public function calculate_stock_balance_by_project($project_id){
    $CI =& get_instance();
    // Select Purchase Bill Items
    $total_items = $CI->Purchases->select_purchase_items("pi.*,m.material_name,p.location_id,p.supplier_id,p.delete_status as bill_delete_status,pi.delete_status as item_delete_status", array("sl.work_id" => $project_id),'','','all');
    foreach ($total_items->result() as $key => $value) {
      $stock_data = array();
      $stock_data['purchase_item_id'] = $value->item_id;
      $stock_data['purchase_supplier_id'] = $value->supplier_id;
      $stock_data['location_id'] = $value->location_id;
      $stock_data['stock_name'] = $value->material_name;
      $stock_data['material_id'] = $value->material_id;
      $stock_data['unit'] = $value->unit_id;
      if($value->quantity!=0){
        $stock_data['unit_price'] = $value->total_amount/$value->quantity;
      }else{
        $stock_data['unit_price'] = 0;
      }
      if($value->bill_delete_status==1 || $value->item_delete_status==1){
        $stock_data['total_purchased_qty'] = 0;
      }else{
        $stock_data['total_purchased_qty'] = $value->quantity;
      }

      //  Check Already Exist
      $stock_count = $CI->Stock->select_stock_register("",array("sr.purchase_item_id"=>$stock_data['purchase_item_id']));
      if($stock_count->num_rows()==0){
        // Create New Entry
        $stock_data['created_date'] = date('Y-m-d H:i:s');
        $stock_data['created_by'] = $CI->session->userdata('user_id');
        $result = $CI->Stock->insert_stock_register($stock_data);
        $CI->stock_management->calculate_stock_balance($result['insert_id']);
      }else{
        // Update Existing Entry
        $stock_data['updated_date'] = date('Y-m-d H:i:s');
        $stock_data['updated_by'] = $CI->session->userdata('user_id');
        $stock_data['stock_id'] = $stock_count->row()->stock_id;
        $result = $CI->Stock->update_stock_register($stock_data);
        $CI->stock_management->calculate_stock_balance($stock_data['stock_id']);
      }

    }

    $stock_data = $CI->Stock->select_stock_register("",array("sl.work_id"=>$project_id))->result();
    foreach ($stock_data as $key => $value) {
      $total_purchased_qty = $value->total_purchased_qty;
      $total_opening_qty = $value->total_opening_qty;
      $data['total_transferred_in_qty'] = $CI->Stock->select_stock_transfers("COALESCE(SUM(st.transferred_qty),0) as total", array("st.transferred_to_stock_id"=>$value->stock_id,"sr1.material_id"=>$value->material_id,"sr1.unit"=>$value->unit))->row()->total;
      $data['total_transferred_out_qty'] = $CI->Stock->select_stock_transfers("COALESCE(SUM(st.transferred_qty),0) as total", array("st.transferred_from_stock_id"=>$value->stock_id,"sr1.material_id"=>$value->material_id,"sr1.unit"=>$value->unit))->row()->total;
      $data['total_consumption_qty'] = $CI->Stock->select_stock_consumption("COALESCE(SUM(sc.consumption_qty),0) as total", array("sc.stock_id"=>$value->stock_id))->row()->total;
      $data['total_sold_qty'] = $CI->Sales_invoice->select_invoice_sale_item("COALESCE(SUM(sii.quantity),0) as total", array("sii.stock_id"=>$value->stock_id))->row()->total;
      // $data['total_sold_qty'] += $CI->Invoices->select_invoice_items("COALESCE(SUM(ii.quantity),0) as total", array("ii.stock_id"=>$value->stock_id))->row()->total;
      $data['total_balance_qty'] = $total_opening_qty+$total_purchased_qty+$data['total_transferred_in_qty']-$data['total_transferred_out_qty']-$data['total_consumption_qty']-$data['total_sold_qty'];
      $data['stock_id'] = $value->stock_id;
      $result = $CI->Stock->update_stock_register($data);
    }
  }
  public function calculate_stock_transfer_bill($bill_id){
    $CI =& get_instance();
    $bill_data = $CI->Stock->select_stock_transfer_bill("",array("stb.bill_id"=>$bill_id));
    if($bill_data->num_rows()==1){
      $bill_data = $bill_data->row();
      //Transfer Items
      $bill_update_data['bill_id'] = $bill_id;
      $bill_update_data['total_bill_amount'] = $CI->Stock->select_stock_transfers("COALESCE(SUM(st.total_amount),0) as total", array("st.transfer_bill_id"=>$bill_id))->row()->total;
      $bill_update_data['total_received_amount'] = 0;
      $bill_update_data['total_balance_amount'] = $bill_update_data['total_bill_amount']-$bill_update_data['total_received_amount'];
      $CI->Stock->update_stock_transfer_bill($bill_update_data);
    }
  }
  public function sales_invoice_branch_purchase_bill_generate($invoice_id){
    $CI =& get_instance();
    $invoice_data = $CI->Sales_invoice->select_invoices("si.*,ba.*,u.user_alias,c.customer_id,c.customer_name,c.customer_location,sl.location_name,sl.work_id,sl.location_type,sl.work_id as location_work_id,tsc.state_name,tsc.state_code,si.branch_id as invoice_branch_id,b.branch_name,b.branch_address", array("si.invoice_id" =>$invoice_id ));
    if($invoice_data->num_rows()==1){
      $invoice_data = $invoice_data->row();
      if($invoice_data->branch_id==0 && $invoice_data->invoice_type=='B2B' && $invoice_data->invoice_to=='Branch'){
        $invoice_items = $CI->Sales_invoice->select_invoice_sale_item("sii.*",array("si.invoice_id"=>$invoice_id))->result();

        // Check Already Bill Generated
        $purchase_data = $CI->Purchases->select_purchase("p.*", array("p.sales_invoice_id" =>$invoice_id));
        if($purchase_data->num_rows()==0){
          // Get Branch Default Location
          $location_data = $CI->Stock->select_stock_locations("sl.*",array("location_branch_id"=>$invoice_data->buyer_branch_id,"editable_status"=>0));
          $data['location_id'] = $location_data->row()->location_id;
          $data['sales_invoice_id'] = $invoice_id;
          $data['supplier_id'] = 1;//default
          $data['purchase_date'] = $invoice_data->invoice_date;
          $data['bill_invoice_no'] = $invoice_data->invoice_no_prefix.$invoice_data->invoice_no;
          $data['remarks'] = $invoice_data->bill_remarks;
          $data['vehicle_no'] = $invoice_data->vehicle;
          $data['created_date'] = date('Y-m-d H:i:s');
          $data['created_by'] = $CI->session->userdata('user_id');
          $data['bill_save_status'] = 1;
          $result = $CI->Purchases->insert_purchase($data);
          if($result['status']==1){
            $item_data['purchase_id'] = $result['insert_id'];
            $discount_amount = $tax_amount = $taxable_amount = 0;
            foreach ($invoice_items as $row) {
              $item_data['material_id'] = $row->material_id;
              $item_data['quantity'] = $row->quantity;
              $item_data['unit_id'] = $row->unit_id;
              $item_data['total_amount'] = $row->total_amount;
              $item_data['unit_price'] = $row->unit_sale_price;
              $item_data['item_code'] = $row->item_code;
              $item_data['tax_percent'] = $row->tax_percent;
              $item_data['tax_amount'] = $row->tax_amount;
              $item_data['description'] = $row->description;
              $item_data['disc_amt'] = $row->discount_amount;
              $discount_amount += $row->discount_amount;
              $tax_amount += $row->tax_amount;
              $taxable_amount += ($row->quantity*$row->unit_sale_price)-$row->discount_amount;

              $item_result = $CI->Purchases->create_purchase_item($item_data);
            }

            $bill_update_data['bill_discount_amount'] = $discount_amount;
            $bill_update_data['bill_tax_amount'] = $tax_amount;
            $bill_update_data['bill_round_amount'] = $invoice_data->round_amount;
            $bill_update_data['bill_total_amount'] = $taxable_amount;
            $bill_update_data['bill_payable_amount'] = $invoice_data->receivable_amount;
            $bill_update_data['purchase_id'] = $result['insert_id'];
            $result2 = $CI->Purchases->update_purchase($bill_update_data);

            $CI->bills_management->calculate_purchase_bill_total($result['insert_id']);
            $this->update_stock_register($result['insert_id']);
          }
        }
      }

    }
  }
}
?>
