File: /home/cafsindia/ntc_cafsinfotech_in_bk/application/controllers/Inventory.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Base_controller.php");
class Inventory extends Base_controller{
public function __construct(){
parent::__construct('inventory');
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
$this->collect_base_info();
}
// LOAD PAGE WITH TABLE DATA
public function index(){
$data['table_headers']= $this->xss_clean(get_dbtable_headers($this->table_info));
$data['fliter_list'] = $this->fliter_list;
$data['link_info'] = $this->quick_link;
$cart_view = array();
$this->session->set_userdata('cart_view',$cart_view);
$data['cart_view'] = $this->get_cart_view();
$this->load->view("$this->control_name/manage",$data);
}
//AUTO SEARCH BY ITEMS OR BY POD Number
public function item_search(){
$search_by = (int)$this->input->post_get('search_by');
$search_items = $this->input->post_get('search_items');
$request_mode = $this->input->post_get('request_mode');
if($search_by === 1){
$this->db->select('prime_purchase_order_id,pod_number,invoice_no');
$this->db->from('cw_purchase_order');
if((int)$request_mode === 3){
$this->db->group_start();
$this->db->like('invoice_no',$search_items);
$this->db->group_end();
$this->db->where('po_status',2);
}else{
$this->db->group_start();
$this->db->like('pod_number',$search_items);
$this->db->group_end();
$this->db->where('po_status',1);
}
$this->db->where('trans_status',1);
$search_info = $this->db->get()->result();
foreach($search_info as $search){
$prime_purchase_order_id = $search->prime_purchase_order_id;
$pod_number = $search->pod_number;
$invoice_no = $search->invoice_no;
if((int)$request_mode === 3){
$label = "$invoice_no";
}else
if((int)$request_mode === 2){
$label = "$pod_number";
}
$suggestions[] = array('value'=>$prime_purchase_order_id,'label'=>$label,'search_by'=>$search_by,'prime_id' => $prime_purchase_order_id,'invoice_no' => $invoice_no);
}
if(empty($suggestions)){
$suggestions[] = array('value' => "0", 'label' => "No data found for this search");
}
}else
if($search_by === 2){
$this->db->select('prime_inventory_product_id,product_name,inventory_brand.brand_name,inventory_category.category_name');
$this->db->from('inventory_product');
$this->db->join('inventory_brand','inventory_product.brand = inventory_brand.prime_inventory_brand_id');
$this->db->join('inventory_category','inventory_category.prime_inventory_category_id = inventory_product.category');
$this->db->where('inventory_product.trans_status', 1);
$this->db->where('inventory_brand.status', 1);
$this->db->where('inventory_category.status', 1);
$this->db->group_start();
$this->db->like('product_name',$search_items);
$this->db->or_like('cw_inventory_brand.brand_name',$search_items);
$this->db->or_like('cw_inventory_category.category_name',$search_items);
$this->db->group_end();
$search_info = $this->db->get()->result();
foreach($search_info as $search_rslt){
$prime_inventory_product_id = $search_rslt->prime_inventory_product_id;
$product_name = $search_rslt->product_name;
$brand_name = $search_rslt->brand_name;
$category_name = $search_rslt->category_name;
$label = "$product_name - $brand_name - $category_name";
$suggestions[] = array('value'=>$prime_inventory_product_id,'label'=>$label,'search_by'=>$search_by,'item_id' => $prime_inventory_product_id);
}
if(empty($suggestions)){
$suggestions[] = array('value' => "0", 'label' => "No data found for this search");
}
}
echo json_encode($suggestions);
}
//ADD ITEMS TO CART
public function get_items(){
$cart_view = $this->session->userdata('cart_view');
$search_by = (int)$this->input->post('search_by');
$request_mode = $this->input->post('request_mode');
$prime_id = $this->input->post('prime_id');
$item_id = $this->input->post('item_id');
$search_items = $this->input->post('search_items');
$cart_view['error'] = array();
if($search_by === 1){
if($this->order_exists($prime_id)){
$search_info = $this->get_table_data($prime_id,$request_mode);
$cart_view_infomation = $this->get_cart_view();
echo json_encode(array('cart_view_infomation'=>$cart_view_infomation,'error'=>""),JSON_FORCE_OBJECT);
}
}else
if($search_by === 2){
if($this->order_exists($prime_id)){
$cart_view_infomation = $this->get_cart_view();
if($this->material_exists_in_order($prime_id,$item_id)){
echo json_encode(array('cart_view_infomation'=>$cart_view_infomation,'error'=>"Material Already Exists"),JSON_FORCE_OBJECT);
}else{
$this->db->select('prime_inventory_product_id,product_name,product_type,material_code,inventory_brand.brand_name,inventory_category.category_name,total_quantity,inventory_supplier.prime_inventory_supplier_id as supplier_id,supplier_name,mobile_number,address,inventory_product.price');
$this->db->from('inventory_product');
$this->db->join('inventory_supplier','inventory_supplier.prime_inventory_supplier_id = inventory_product.supplier');
$this->db->join('inventory_brand','inventory_product.brand = inventory_brand.prime_inventory_brand_id');
$this->db->join('inventory_category','inventory_category.prime_inventory_category_id = inventory_product.category');
$this->db->where('inventory_product.trans_status', 1);
$this->db->where('inventory_brand.status', 1);
$this->db->where('inventory_category.status', 1);
$this->db->where('inventory_product.prime_inventory_product_id',$item_id);
$item_info = $this->db->get()->row();
$prime_inventory_product_id = $item_info->prime_inventory_product_id;
$supplier_id = $item_info->supplier_id;
$product_name = $item_info->product_name;
$brand_name = $item_info->brand_name;
$category_name = $item_info->category_name;
$quantity = 1;
$price = $item_info->price;
$supplier_inventory = $cart_view['supplier_inventory'];
$check_id = $supplier_inventory['supplier_id'];
$prime_purchase_order_id = $supplier_inventory['prime_purchase_order_id'];
if((int)$check_id === (int)$supplier_id){
$cart_view['purchase_inventory'][$prime_inventory_product_id] =
array('prime_inventory_product_id' => $prime_inventory_product_id,
'supplier_id' => $supplier_id,
'prime_purchase_order_id' => $prime_id,
'product_name' => $product_name,
'brand_name' => $brand_name,
'category_name' => $category_name,
'quantity' => $quantity,
'receiving_quantity' => $quantity,
'price' => $price
);
$this->session->set_userdata('cart_view', $cart_view);
$cart_view_infomation = $this->get_cart_view();
echo json_encode(array('cart_view_infomation'=>$cart_view_infomation,'error'=>""),JSON_FORCE_OBJECT);
}else{
$cart_view_infomation = $this->get_cart_view();
echo json_encode(array('cart_view_infomation'=>$cart_view_infomation,'error'=>"Material doesn't available in current supplier"),JSON_FORCE_OBJECT);
}
}
}else{
$cart_view_infomation = $this->get_cart_view();
echo json_encode(array('cart_view_infomation'=>$cart_view_infomation,'error'=>"Add POD Number And Try Again"),JSON_FORCE_OBJECT);
}
}else{
$cart_view_infomation = $this->get_cart_view();
echo json_encode(array('cart_view_infomation'=>$cart_view_infomation,'error'=>"Please Contact The Admin"),JSON_FORCE_OBJECT);
}
}
//Save to PURCHASE ORDER
public function save_order(){
$prime_supplier_id = (int)$this->input->post('prime_inventory_supplier_id');
$purchase_order_id = (int)$this->input->post('prime_purchase_order_id');
$request_mode = (int)$this->input->post('request_mode');
$invoice_no = $this->input->post('invoice_no');
$cart_view = $this->session->userdata('cart_view');
$supplier_inventory = $cart_view['supplier_inventory'];
$supplier_id = $supplier_inventory['supplier_id'];
$prime_purchase_order_id = $supplier_inventory['prime_purchase_order_id'];
$purchase_inventory = $cart_view['purchase_inventory'];
$cart_view = array();
$table_content = $this->get_cart_view();
if($this->order_exists($prime_purchase_order_id)){
if(((int)$supplier_id === (int)$prime_supplier_id) && ((int)$prime_purchase_order_id === (int)$purchase_order_id)){
if(!empty($purchase_inventory)){
foreach ($purchase_inventory as $value){
$product_name = $value['product_name'];
$brand_name = $value['brand_name'];
$category_name = $value['category_name'];
$quantity = $value['quantity'];
$receiving_quantity = $value['receiving_quantity'];
$price = $value['price'];
$material_id = $value['prime_inventory_product_id'];
$check_list[] = $material_id;
$amount = 0;
$insert_data = array(
'prime_purchase_order_id' => $prime_purchase_order_id,
'supplier_id' => $supplier_id,
'prime_inventory_product_id' => $material_id,
'product_name' => $product_name,
'brand_name' => $brand_name,
'category_name' => $category_name,
'quantity' => $quantity,
'price' => $price
);
$amount = (int)$quantity*(int)$price;
$insert_data['amount'] = $amount;
$insert_data['trans_created_by'] = $this->session->userdata('logged_id');
$insert_data['trans_created_date'] = date("Y-m-d H:i:s");
if($request_mode === 3){
$insert_data['return_quantity'] = $receiving_quantity;
$this->db->insert('cw_purchase_return',$insert_data);
$message = "Purchase Return Successfully";
}else{
$insert_data['receiving_quantity'] = $receiving_quantity;
$this->db->insert('cw_purchase_receiving',$insert_data);
$message = "Purchase Recevied Successfully";
}
}
$update_order['po_status'] = $request_mode;
$update_order['invoice_no'] = $invoice_no;
$update_order['trans_updated_by'] = $this->session->userdata('logged_id');
$update_order['trans_updated_date'] = date("Y-m-d H:i:s");
$this->db->where('prime_purchase_order_id', $prime_purchase_order_id);
$this->db->update('cw_purchase_order',$update_order);
$this->update_quantity($prime_purchase_order_id,$request_mode);
$this->session->set_userdata('cart_view',$cart_view);
$table_content = $this->get_cart_view();
echo json_encode(array('success'=>true,'message'=>$message,'table_content'=>$table_content));
}else{
echo json_encode(array('success'=>false,'message'=>'Please add the items and try again','table_content'=>$table_content));
}
}
}else{
echo json_encode(array('success'=>false,'message'=>'Please Contact Admin','table_content'=>$table_content));
}
}
public function get_table_data($prime_id = -1,$request_mode = -1){
$cart_view = array();
$query = "select cw_purchase_order.prime_purchase_order_id,cw_purchase_order.supplier_id,cw_purchase_order.pod_number,cw_purchase_order.invoice_no,cw_purchase_order.entry_date,cw_purchase_item_list.prime_inventory_product_id,cw_purchase_item_list.product_name,cw_purchase_item_list.brand_name,cw_purchase_item_list.category_name,cw_purchase_item_list.quantity,cw_inventory_product.price,cw_inventory_supplier.supplier_name,cw_inventory_supplier.contact_person,cw_inventory_supplier.mobile_number,cw_inventory_supplier.address,cw_purchase_order.total_amount from cw_purchase_order join cw_purchase_item_list on cw_purchase_item_list.prime_purchase_order_id = cw_purchase_order.prime_purchase_order_id join cw_inventory_supplier on cw_inventory_supplier.prime_inventory_supplier_id = cw_purchase_item_list.supplier_id join cw_inventory_product on cw_inventory_product.prime_inventory_product_id = cw_purchase_item_list.prime_inventory_product_id where cw_purchase_order.trans_status = 1 and cw_purchase_item_list.trans_status = 1 and cw_purchase_order.prime_purchase_order_id = $prime_id";
if((int)$request_mode === 3){
$query = str_replace("cw_purchase_item_list","cw_purchase_receiving",$query);
$query = str_replace("quantity","receiving_quantity",$query);
}
$rlst_info = $this->db->query("CALL sp_a_run ('SELECT','$query')");
$result = $rlst_info->result();
$rlst_info->next_result();
$supplier_name = $result[0]->supplier_name;
$supplier_id = $result[0]->supplier_id;
$order_id = $result[0]->prime_purchase_order_id;
$mobile_number = $result[0]->mobile_number;
$pod_number = $result[0]->pod_number;
$invoice_no = $result[0]->invoice_no;
$address = $result[0]->address;
$total_amount = $result[0]->total_amount;
$cart_view['supplier_inventory'] =
array(
'supplier_id' => $supplier_id,
'prime_purchase_order_id' => $order_id,
'supplier_name' => $supplier_name,
'mobile_number' => $mobile_number,
'address' => $address,
'pod_number' => $pod_number,
'invoice_no' => $invoice_no
);
foreach($result as $result_info){
$prime_inventory_product_id = $result_info->prime_inventory_product_id;
$supplier_id = $result_info->supplier_id;
$prime_purchase_order_id = $result_info->prime_purchase_order_id;
$product_name = $result_info->product_name;
$brand_name = $result_info->brand_name;
$category_name = $result_info->category_name;
if($result_info->quantity){
$quantity = $result_info->quantity;
}else
if($result_info->receiving_quantity){
$quantity = $result_info->receiving_quantity;
}
if((int)$quantity === 0){
$quantity = 1;
}
$price = $result_info->price;
$total_price = (int)$price*(int)$quantity;
$cart_view['purchase_inventory'][$prime_inventory_product_id] =
array('prime_inventory_product_id' => $prime_inventory_product_id,
'supplier_id' => $supplier_id,
'prime_purchase_order_id' => $prime_purchase_order_id,
'product_name' => $product_name,
'brand_name' => $brand_name,
'category_name' => $category_name,
'quantity' => $quantity,
'receiving_quantity' => $quantity,
'price' => $price
);
}
$this->session->set_userdata('cart_view', $cart_view);
}
public function get_cart_view(){
$request_mode = (int)$this->input->post('request_mode');
$cart_view = $this->session->userdata('cart_view');
$tr_line = "";
$supplier_info = $cart_view['supplier_inventory'];
$supplier_name = $supplier_info['supplier_name'];
$supplier_id = $supplier_info['supplier_id'];
$order_id = $supplier_info['prime_purchase_order_id'];
$mobile_number = $supplier_info['mobile_number'];
$pod_number = $supplier_info['pod_number'];
$address = $supplier_info['address'];
$total_amount = $supplier_info['total_amount'];
if($request_mode === 3){
$td = "<td style='padding:4px;'><span style='color: #4e5b9a; font-size: 12px; font-weight: bold;'>POD number </span><br/>$pod_number</td>";
}else{
$td = '';
}
$supplier_content = "<table class='table' style='background-color:#ffffff;'>
<tr>
<input type='hidden' name='purchase_order_id' id='purchase_order_id' value='$order_id'>
<input type='hidden' name='inventory_supplier_id' id='inventory_supplier_id' value='$supplier_id'>
<td style='padding:4px;'><span style='color: #4e5b9a; font-size: 12px; font-weight: bold;'>Supplier Name</span><br/>$supplier_name</td>
<td style='padding:4px;'><span style='color: #4e5b9a; font-size: 12px; font-weight: bold;'>Mobile Number</span><br/>$mobile_number</td>
<td style='padding:4px;'><span style='color: #4e5b9a; font-size: 12px; font-weight: bold;'>Address</span><br/>$address</td>
$td
</tr>
</table>";
foreach(array_reverse($cart_view['purchase_inventory']) as $data){
$prime_id = $data['prime_inventory_product_id'];
$prime_inventory_supplier_id = $data['supplier_id'];
$prime_purchase_order_id = $data['prime_purchase_order_id'];
$product_name = $data['product_name'];
$brand_name = $data['brand_name'];
$category_name = $data['category_name'];
$quantity = $data['quantity'];
$receiving_quantity = (int)$data['receiving_quantity'];
$price = $data['price'];
$difference = (int)$quantity - (int)$receiving_quantity;
if((int)$quantity === 0){
$quantity = 1;
}
$total_price = (int)$price*(int)$quantity;
$tr_line .= "<tr id='tr_data_$prime_id'>
<td>
<button onclick = 'delete_item($prime_id)' class = 'btn btn-xs btn-edit'><span class='fa fa-trash'></span> Delete </button>
</td>
<td>
<input type='hidden' name='prime_purchase_order_id' id='prime_purchase_order_id' value='$prime_purchase_order_id'>
<input type='hidden' name='prime_inventory_supplier_id' id='prime_inventory_supplier_id' value='$prime_inventory_supplier_id'>
<input type='hidden' name='material_id_[]' id='material_id_$prime_id' value='$prime_id'>
<input type='hidden' name='brand_name_$prime_id' id='brand_name_$prime_id' value='$brand_name' >
<input type='hidden' name='category_name_$prime_id' id='category_name_$prime_id' value='$category_name'>
<input type='hidden' name='product_name_$prime_id' id='product_name_$prime_id' value='$product_name'>$product_name
</td>
<td>
$brand_name
</td>
<td>
$category_name
</td>
<td>
<input type='hidden' name='exist_quantity_$prime_id' id='exist_quantity_$prime_id' value='$quantity' >
<span id='exist_quantity_span_$prime_id' > $quantity </span>
</td>
<td>
<input name='quantity[]' id='receiving_quantity_$prime_id' value='$receiving_quantity' class='number' style='width:100px;' onchange=update_quantity('$prime_id');>
</td>
<td>
<input name='difference_[]' id='difference_$prime_id' value='$difference' class='number' style='width:100px;' type='hidden'>
<span id='difference_span_$prime_id' > $difference </span>
</td>
<td>
<input name='price[]' id='price_$prime_id' value='$price' class='number' style='width:100px;' type='hidden'>
<span id='total_price_$prime_id' > $total_price </span>
</td>
</tr>";
}
if($tr_line == ""){
$tr_line = "<tr colspan='5'><td>No Data Found</td></tr>";
}
$cart_content = "<h4 class='tab_head' style='margin:8px 0px;'>Product List</h4>
<table class='table table-striped'>
<thead>
<tr>
<td>Option</td>
<td>Items Name</td>
<td>Brand Name</td>
<td>Category Name</td>
<td>Ordered Quantity</td>
<td>Return/Receiving Quantity</td>
<td>pending Quantity</td>
<td>Total Amount</td>
</tr>
</thead>
<tbody>
$tr_line
</tbody>
</table>";
return array('supplier_content'=>$supplier_content,'cart_content'=>$cart_content);
}
public function material_exists_in_receiving($purchase_order_id,$material_id){
$this->db->from('cw_purchase_receiving');
$this->db->where('trans_status',1);
$this->db->where('prime_purchase_order_id',(int)$purchase_order_id);
$this->db->where('prime_inventory_product_id',(int)$material_id);
$count = $this->db->get()->num_rows();
if((int)$count === 1){
return TRUE;
}else{
return FALSE;
}
}
//UPDATE QUANTITY TO PURCHASE LIST
public function update_quantity_in_list(){
$material_id = (int)trim($this->input->post('prime_id'));
$prime_purchase_order_id = $this->input->post('prime_purchase_order_id');
$quantity = $this->input->post('quantity');
if((int)$quantity === 0){
$quantity = 1;
}
$price = $this->input->post('price');
$amount = (int)$quantity*(int)$price;
$cart_view = $this->session->userdata('cart_view');
$exist_quantity = $cart_view['purchase_inventory'][$material_id]['quantity'];
if((int)$quantity <= (int)$exist_quantity){
$cart_view['purchase_inventory'][$material_id]['receiving_quantity'] = (int)$quantity;
}else{
$cart_view['purchase_inventory'][$material_id]['receiving_quantity'] = (int)$exist_quantity;
}
$cart_view['purchase_inventory'][$material_id]['price'] = $price;
$this->session->set_userdata('cart_view', $cart_view);
}
//CHECK PURCHASE ORDER EXISTS
public function order_exists($prime_purchase_order_id){
$this->db->from('cw_purchase_order');
$this->db->where('trans_status',1);
$this->db->where('prime_purchase_order_id',(int)$prime_purchase_order_id);
$count = $this->db->get()->num_rows();
if((int)$count === 1){
return TRUE;
}else{
return FALSE;
}
}
//CHECK MATERIAL EXISTS
public function material_exists_in_order($prime_purchase_order_id,$material_id){
$cart_view = $this->session->userdata('cart_view');
if($cart_view['purchase_inventory'][$material_id]){
return TRUE;
}else{
return FALSE;
}
}
public function cancel_order(){
$cart_view = array();
$this->session->set_userdata('cart_view',$cart_view);
$cart_view_infomation = $this->get_cart_view();
echo json_encode(array('sts'=>true,'message'=>"Purchase Order removed successfully",'cart_view_infomation'=>$cart_view_infomation),JSON_FORCE_OBJECT);
}
//DELETE ITEM FROM SESSION
public function delete_item(){
$prime_id = (int)$this->input->post('prime_id');
$cart_view = $this->session->userdata('cart_view');
if($cart_view['purchase_inventory'][$prime_id]){
unset($cart_view['purchase_inventory'][$prime_id]);
}
$this->session->set_userdata('cart_view',$cart_view);
$table_content = $this->get_cart_view();
echo json_encode(array('success'=>True,'message'=>'Deleted Successfully','cart_view_infomation'=>$table_content));
}
//UPDATE QUANTITY INFOMATION
public function update_quantity($prime_purchase_order_id,$request_mode){
if((int)$request_mode === 2){
$this->db->select('cw_purchase_receiving.prime_inventory_product_id,cw_purchase_receiving.supplier_id,cw_purchase_receiving.receiving_quantity as quantity,IFNULL(cw_inventory_product.total_quantity,0) as total_quantity');
$this->db->from('cw_purchase_receiving');
$this->db->join('cw_inventory_product','cw_inventory_product.prime_inventory_product_id = cw_purchase_receiving.prime_inventory_product_id');
$this->db->where('cw_purchase_receiving.trans_status',1);
$this->db->where('cw_purchase_receiving.prime_purchase_order_id',(int)$prime_purchase_order_id);
}else{
$this->db->select('cw_purchase_return.prime_inventory_product_id,cw_purchase_return.supplier_id,cw_purchase_return.return_quantity as quantity,IFNULL(cw_inventory_product.total_quantity,0) as total_quantity');
$this->db->from('cw_purchase_return');
$this->db->join('cw_inventory_product','cw_inventory_product.prime_inventory_product_id = cw_purchase_return.prime_inventory_product_id');
$this->db->where('cw_purchase_return.trans_status',1);
$this->db->where('cw_purchase_return.prime_purchase_order_id',(int)$prime_purchase_order_id);
}
$purchase_received_info = $this->db->get();
$quantity = 0;
if((int)$purchase_received_info->num_rows() > 0){
$purchase_rslt = $purchase_received_info->result();
foreach($purchase_rslt as $rslt){
$prime_inventory_product_id = (int)$rslt->prime_inventory_product_id;
$quantity = (int)$rslt->quantity;
$supplier_id = (int)$rslt->supplier_id;
$total_quantity = (int)$rslt->total_quantity;
if((int)$request_mode === 2){
$material_quantity = (int)$total_quantity + (int)$quantity;
}else{
$material_quantity = (int)$total_quantity - (int)$quantity;
}
$update_material_info['trans_updated_by'] = $this->session->userdata('logged_id');
$update_material_info['trans_updated_date'] = date("Y-m-d H:i:s");
$update_material_info['total_quantity'] = $material_quantity;
$this->db->where('prime_inventory_product_id', $prime_inventory_product_id);
$this->db->update('cw_inventory_product',$update_material_info);
}
}
}
}
?>