File: /home/cafsindia/wealth_cafsindia_com/application/controllers/Stock.php
<?php if ( ! defined('BASEPATH')) exit('No direct script is allowed');
require_once("Base_controller.php");
class Stock extends Base_controller{
public function __construct(){
parent::__construct('stock');
if(!$this->Appconfig->isAppvalid()){
redirect('config');
}
}
public function index(){
$data['table_headers']= $this->xss_clean(get_stock_headers());
$this->load->view("stock/manage",$data);
}
public function add_stock($prime_stock_id){
$this->load->view("stock/form");
}
public function view($prime_stock_id){
$data["view_info"] = $this->get_buy_list($prime_stock_id);
$this->load->view("stock/view",$data);
}
public function save(){
$customer_name = $this->input->post('customer_name');
$stock_name = $this->input->post('stock_name');
$date = date('Y-m-d',strtotime($this->input->post('date')));
$quantity = $this->input->post('quantity');
$do_know_price = $this->input->post('do_know_price');
$price = $this->input->post('price');
$total_amount = $this->input->post('total_amount');
$logged_id = $this->session->userdata('logged_id');
$created_on = date("Y-m-d h:i:s");
$is_exist_qry = 'SELECT * FROM cw_stock where customer_name = "'.$customer_name.'" and stock_name = "'.$stock_name.'" and trans_status = 1 ';
$is_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$is_exist_qry')");
$exist_rslt = $is_exist_data->result();
$exist_count = $is_exist_data->num_rows();
$is_exist_data->next_result();
if((int)$exist_count === 0){
$stock_qry = 'INSERT INTO cw_stock (customer_name, stock_name,trans_created_by, trans_created_date) VALUES ("'.$customer_name.'","'.$stock_name.'","'.$logged_id.'","'.$created_on.'")';
$insert_data = $this->db->query("CALL sp_a_run ('INSERT','$stock_qry')");
$insert_rslt = $insert_data->result();
$insert_data->next_result();
$prime_stock_id = $insert_rslt[0]->ins_id;
}else{
$prime_stock_id = $exist_rslt[0]->prime_stock_id;
}
$is_exist_qry = 'SELECT * FROM cw_stock_buy where prime_stock_id = "'.$prime_stock_id.'" and date = "'.$date.'" and price = "'.$price.'" and trans_status = 1 ';
$is_exist_data = $this->db->query("CALL sp_a_run ('SELECT','$is_exist_qry')");
$exist_rslt = $is_exist_data->result();
$exist_count = $is_exist_data->num_rows();
$is_exist_data->next_result();
if((int)$exist_count === 0){
$stock_qry = 'INSERT INTO cw_stock_buy (prime_stock_id,date, purchased_quantity,quantity,price,total_amount,trans_created_by, trans_created_date) VALUES ("'.$prime_stock_id.'","'.$date.'","'.$quantity.'","'.$quantity.'","'.$price.'","'.$total_amount.'","'.$logged_id.'","'.$created_on.'")';
$this->db->query("CALL sp_a_run ('RUN','$stock_qry')");
$rslt_array = array('success' => true, 'message' => "Stock successfully added");
}else
if((int)$exist_count === 1){
$prime_stock_buy_id = $exist_rslt[0]->prime_stock_buy_id;
$exist_quantity = $exist_rslt[0]->quantity;
$quantity = floor($quantity+$exist_quantity);
$total_amount = floor($quantity*$price);
$stock_upd_qry = 'UPDATE cw_stock_buy SET quantity = "'.$quantity.'",purchased_quantity = "'.$quantity.'",price = "'.$price.'",total_amount = "'.$total_amount.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$created_on.'" where prime_stock_buy_id = "'.$prime_stock_buy_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$stock_upd_qry')");
$rslt_array = array('success' => true, 'message' => "Stock successfully updated");
}else{
$rslt_array = array('success' => FALSE, 'message' => "Stock already exist");
}
$final_quantity = 0;
$upd_head_qry = 'SELECT IFNULL(sum(quantity),0) as quantity,IFNULL(sum(total_amount),0) as total_amount FROM cw_stock_buy where prime_stock_id = "'.$prime_stock_id.'" and trans_status = 1 ';
$upd_head_data = $this->db->query("CALL sp_a_run ('SELECT','$upd_head_qry')");
$upd_head_rslt = $upd_head_data->result();
$upd_head_data->next_result();
$final_quantity = $upd_head_rslt[0]->quantity;
$total_amount = $upd_head_rslt[0]->total_amount;
$avg_invest_price = floor($total_amount/$final_quantity);
$stock_head_qry = 'UPDATE cw_stock SET quantity = "'.$final_quantity.'",avg_invest_price = "'.$avg_invest_price.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$created_on.'" where prime_stock_id = "'.$prime_stock_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$stock_head_qry')");
echo json_encode($rslt_array);
}
public function search(){
$search = $this->input->get('search');
$limit = $this->input->get('limit');
$offset = $this->input->get('offset');
$sort = $this->input->get('sort');
$order = $this->input->get('order');
$this->db->select('prime_stock_id,customer.prime_customer_id,stock.stock_name as stock_id,customer.short_name as customer_name,equity.security_name as stock_name,IFNULL(sum(cw_stock.quantity), 0) as quantity , equity_data_daily.close as current_price,avg_invest_price');
$this->db->from('stock');
$this->db->join('customer', 'customer.prime_customer_id = stock.customer_name','inner');
$this->db->join('equity', 'equity.prime_equity_id = stock.stock_name','inner');
$this->db->join('equity_data_daily', 'equity_data_daily.security_code = equity.security_code','left');
if($search){
$this->db->group_start();
$this->db->like('customer.short_name',$search);
$this->db->or_like('equity.security_name',$search);
$this->db->group_end();
}
$this->db->where('stock.trans_status', 1);
$this->db->group_by('customer.prime_customer_id,stock.stock_name');
$stock_data = $this->db->get();
$stock_rslt = $stock_data->result();
$num_rows = $stock_data->num_rows();
$datarows=array();
foreach ($stock_rslt as $stock){
$datarows[]=get_stock_datarows($stock,$this);
}
echo json_encode(array('total'=>$num_rows,'rows'=>$datarows));
}
public function customer_list(){
$search_term = $this->input->post_get('term');
$cust_query = 'select * from cw_customer where short_name like "'.$search_term.'%" or group_code like "'.$search_term.'%" or map_group like "'.$search_term.'"';
$cust_data = $this->db->query("CALL sp_a_run ('SELECT','$cust_query')");
$cust_result = $cust_data->result();
$cust_data->next_result();
foreach($cust_result as $rslt){
$prime_customer_id = $rslt->prime_customer_id;
$short_name = $rslt->short_name;
$group_code = $rslt->group_code;
$map_group = $rslt->map_group;
$suggestions[] = array('value' => $prime_customer_id, 'label' => "$short_name - $group_code - $map_group", 'display_name' => $short_name);
}
if(empty($suggestions)){
$suggestions[] = array('value' => "0", 'label' => "No data found for this search");
}
echo json_encode($suggestions);
}
public function stock_list(){
$search_term = $this->input->post_get('term');
$stock_query = 'select * from cw_equity where security_name like "'.$search_term.'%"';
$stock_data = $this->db->query("CALL sp_a_run ('SELECT','$stock_query')");
$stock_result = $stock_data->result();
$stock_data->next_result();
foreach($stock_result as $rslt){
$prime_equity_id = $rslt->prime_equity_id;
$security_code = $rslt->security_code;
$security_id = $rslt->security_id;
$security_name = $rslt->security_name;
$suggestions[] = array('value' => $prime_equity_id, 'label' => "$security_code - $security_id - $security_name", 'display_name' => $security_name, 'security_code' => $security_code);
}
if(empty($suggestions)){
$suggestions[] = array('value' => "0", 'label' => "No data found for this search");
}
echo json_encode($suggestions);
}
public function get_price(){
$security_code = $this->input->post('security_code');
$date = date('Y-m-d',strtotime($this->input->post('date')));
$quantity = $this->input->post('quantity');
$price_query = 'select close from cw_equity_data_all where security_code = "'.$security_code.'" and date = "'.$date.'"';
$price_data = $this->db->query("CALL sp_a_run ('SELECT','$price_query')");
$price_result = $price_data->result();
$price_data->next_result();
$price = 0;
if($price_result){
$price = $price_result[0]->close;
$total_amount = floor($price*$quantity);
echo json_encode(array('success' => true, 'message'=>"Successfully price added",'price' => $price,'total_amount' => $total_amount,));
}else{
echo json_encode(array('success' => false, 'message'=>"No data available",));
}
}
public function get_total(){
$price = $this->input->post('price');
$quantity = $this->input->post('quantity');
$total_amount = floor($price*$quantity);
echo json_encode(array('success' => true,'total_amount' => $total_amount,));
}
public function get_buy_list($prime_stock_id){
$buy_query = 'SELECT cw_stock_buy.prime_stock_buy_id,short_name,cw_equity.security_name,cw_equity.security_code,cw_stock_buy.prime_stock_id,cw_stock_buy.date,cw_stock_buy.purchased_quantity,cw_stock_buy.quantity,cw_stock_buy.price,cw_stock_buy.total_amount FROM cw_stock_buy left join cw_stock on cw_stock.prime_stock_id = cw_stock_buy.prime_stock_id INNER join cw_equity on cw_equity.prime_equity_id = cw_stock.stock_name inner join cw_customer on cw_customer.prime_customer_id = cw_stock.customer_name where cw_stock_buy.prime_stock_id = "'.$prime_stock_id.'" and cw_stock_buy.trans_status = 1';
$buy_data = $this->db->query("CALL sp_a_run ('SELECT','$buy_query')");
$buy_result = $buy_data->result();
$buy_data->next_result();
$head_line = "";
$security_code = $buy_result[0]->security_code;
$today_date = new DateTime();
$today_date = $today_date->modify("-1 day");
$today_date = $today_date->format("Y-m-d");
$today_price_query = 'select close from cw_equity_data_daily where security_code = "'.$security_code.'" and date = "'.$today_date.'"';
$today_price_data = $this->db->query("CALL sp_a_run ('SELECT','$today_price_query')");
$today_price_result = $today_price_data->result();
$today_price_data->next_result();
foreach($buy_result as $rslt){
$prime_stock_buy_id = $rslt->prime_stock_buy_id;
$short_name = $rslt->short_name;
$security_name = $rslt->security_name;
$security_code = $rslt->security_code;
$prime_stock_id = $rslt->prime_stock_id;
$date = date('d-m-Y',strtotime($rslt->date));
$purchased_quantity = $rslt->purchased_quantity;
$quantity = $rslt->quantity;
$price = $rslt->price;
$total_amount = $rslt->total_amount;
$over_all_gain = 0;
$over_all_gain_per = 0;
if($today_price_result){
$today_close = $today_price_result[0]->close;
$over_all_gain = round(($quantity * $today_close) - $total_amount,2);
$over_all_gain_per = round($over_all_gain / $total_amount*100,2);
}
$over_all_gain_style = "style='color: #CC3366; font-weight: bold;'";
$over_all_gain_per_style = "style='color: #CC3366; font-weight: bold;'";
if($over_all_gain > 0){
$over_all_gain_style = "style='color: #1cc41c; font-weight: bold;'";
$over_all_gain_per_style = "style='color: #1cc41c; font-weight: bold;'";
}
$edit_quantity_label = form_label('Quantity', "edit_quantity_$prime_stock_buy_id", array('class' => 'control-label required'));
$edit_quantity = form_input(array( 'name' => "edit_quantity_$prime_stock_buy_id", 'id' => "edit_quantity_$prime_stock_buy_id", 'class' => 'form-control input-sm number', 'placeholder'=>'Quantity','value' =>$purchased_quantity));
$edit_price_label = form_label('Price', "edit_price_$prime_stock_buy_id", array('class' => 'control-label required'));
$edit_price = form_input(array( 'name' => "edit_price_$prime_stock_buy_id", 'id' => "edit_price_$prime_stock_buy_id", 'class' => 'form-control input-sm number', 'placeholder'=>'Purchase / Sell Price','value' =>$price));
$sell_date_label = form_label('Date', "sell_date_$prime_stock_buy_id", array('class' => 'control-label required'));
$sell_id = form_input(array('name' => "sell_id_$prime_stock_buy_id", 'id' => "sell_id_$prime_stock_buy_id", 'type' => 'hidden','class' => 'form-control input-sm alpha','value' =>0));
$buy_id = form_input(array('name' => "buy_id_$prime_stock_buy_id", 'id' => "buy_id_$prime_stock_buy_id", 'type' => 'hidden','class' => 'form-control input-sm alpha','value' =>$prime_stock_buy_id));
$sell_date_input = form_input(array( 'name' => "sell_date_$prime_stock_buy_id", 'id' => "sell_date_$prime_stock_buy_id", 'class' => 'form-control input-sm datepicker', 'placeholder'=>'Date','value' =>''));
$sell_quantity_label = form_label('Quantity', "sell_quantity_$prime_stock_buy_id", array('class' => 'control-label required'));
$sell_quantity_input = form_input(array( 'name' => "sell_quantity_$prime_stock_buy_id", 'id' => "sell_quantity_$prime_stock_buy_id", 'class' => 'form-control input-sm number', 'placeholder'=>'Quantity','value' =>''));
$do_know_pruchase_list = array(""=>"--- Do You know_Price ? ---","1"=>"Yes","2"=>"No");
$sell_know_price_label = form_label("Do You know_Price ?", "know_price_$prime_stock_buy_id", array('class' => 'control-label required'));
$sell_know_price = form_dropdown(array( 'name' => "know_price_$prime_stock_buy_id", 'id' => "know_price_$prime_stock_buy_id", 'class' => 'form-control input-sm do_know_price'),$do_know_pruchase_list);
$sell_know_price_span = "<span style='color:#CC3366;display:none;' id='price_process_$prime_stock_buy_id'><i class='fa fa-spinner fa-spin fa-2x'></i> Please wait getting price...</span>";
$sell_security_code = form_input(array('name' => "sell_security_code_$prime_stock_buy_id", 'id' => "sell_security_code_$prime_stock_buy_id", 'type' => 'hidden','class' => 'form-control input-sm alpha','value' =>$security_code));
$sell_price_label = form_label('Price', "sell_price_$prime_stock_buy_id", array('class' => 'control-label required'));
$sell_price_input = form_input(array( 'name' => "sell_price_$prime_stock_buy_id", 'id' => "sell_price_$prime_stock_buy_id", 'class' => 'form-control input-sm number', 'placeholder'=>'Purchase / Sell Price','value' =>''));
//SELL LIST
$sell_query = 'SELECT * FROM cw_stock_sell where prime_stock_buy_id = "'.$prime_stock_buy_id.'" and trans_status = 1';
$sell_data = $this->db->query("CALL sp_a_run ('SELECT','$sell_query')");
$sell_result = $sell_data->result();
$sell_data->next_result();
$sell_tr_line = "";
$sell_count = 0;
foreach($sell_result as $rslt){
$sell_count++;
$prime_stock_sell_id = $rslt->prime_stock_sell_id;
$sell_date = date('d-m-Y',strtotime($rslt->date));
$sell_quantity = $rslt->quantity;
$sell_price = $rslt->price;
$sell_total_amount = $rslt->total_amount;
$sell_tr_line .= "<tr>
<td>$sell_count </td>
<td>$sell_date </td>
<td>$sell_quantity </td>
<td>$sell_price </td>
<td>$sell_total_amount </td>
<td>
<a class='btn btn-xs btn-edit btn_action' id='sell_edit_$prime_stock_buy_id::$prime_stock_sell_id'> Edit </a>
<a class='btn btn-xs btn-danger btn_action' id='sell_delete_$prime_stock_buy_id::$prime_stock_sell_id'> Delete </a>
</td>
</tr>";
}
$head_line .= "<div class='panel panel-default'>
<div class='panel-heading' role='tab'>
<table style='width:100%;'>
<tr>
<td> <span style='font-weight:bold;'>$date</span><br/> <span class='span_label'>Date</span> </td>
<td> $purchased_quantity<br/> <span class='span_label'>Purchased Quantity</span> </td>
<td> $quantity<br/> <span class='span_label'>Holding Quantity</span> </td>
<td> $price<br/> <span class='span_label'>Invest Price</span></td>
<td> $total_amount<br/> <span class='span_label'>Total Amount</span></td>
<td> <span $over_all_gain_style>$over_all_gain</span><br/> <span class='span_label'>Over all Gain</span> </td>
<td> <span $over_all_gain_per_style>$over_all_gain_per%</span><br/> <span class='span_label'>Over all Gain%</span> </td>
<td style='text-align:center;cursor:pointer;'>
<a class='btn btn-xs btn-edit stock_btn' id='sell_$prime_stock_buy_id'> <i class='fa fa-inr' aria-hidden='true'></i> Sell </a>
</td>
<td style='text-align:center;cursor:pointer;'>
<a class='stock_btn' id='edit_$prime_stock_buy_id' style='display:block;text-align:center;color:#1883E9;'> <i class='fa fa-pencil-square-o fa-2x' aria-hidden='true'></i> </a>
</td>
<td style='text-align:center;cursor:pointer;'>
<a class='stock_btn' id='remove_$prime_stock_buy_id' style='display:block;text-align:center;color:#CC3366;'> <i class='fa fa-trash fa-2x' aria-hidden='true'></i> </a>
</td>
</tr>
</table>
</div>
<div id='collapse_edit_$prime_stock_buy_id' class='panel-collapse collapse' role='tabpanel'>
<div class='panel-body'>
<form class='form-inline'>
<div class='form-group' style='margin-left:0px;'>
$edit_quantity_label
$edit_quantity
</div>
<div class='form-group'>
$edit_price_label
$edit_price
</div>
<div class='form-group'>
<a class='btn btn-primary btn-sm btn_action' id='edit_save_$prime_stock_buy_id'>Add/Update</a>
<a class='btn btn-danger btn-sm btn_action' id='edit_cancel_$prime_stock_buy_id'>Cancel</a>
</div>
</form>
</div>
</div>
<div id='collapse_sell_$prime_stock_buy_id' class='panel-collapse collapse' role='tabpanel'>
<div class='panel-body'>
<form class='form-inline'>
<div class='form-group' style='margin-left:0px;'>
$sell_date_label
$sell_id
$buy_id
$sell_date_input
</div>
<div class='form-group'>
$sell_quantity_label
$sell_quantity_input
</div>
<div class='form-group'>
$sell_know_price_label
$sell_know_price
$sell_security_code
$sell_know_price_span
</div>
<div class='form-group' style='display:none;'>
$sell_price_label
$sell_price_input
</div>
<div class='form-group'>
<a class='btn btn-primary btn-sm btn_action' id='sell_save_$prime_stock_buy_id'>Add/Update</a>
<a class='btn btn-danger btn-sm btn_action' id='sell_cancel_$prime_stock_buy_id'>Cancel</a>
</div>
</form>
<div style='padding:8px;background-color:#e6e6e6;border-radius:3px;box-shadow:0 2px 2px 0 rgba(0,0,0,0.14), 0 3px 1px -2px rgba(0,0,0,0.12), 0 1px 5px 0 rgba(0,0,0,0.2);'>
<table class='table table-bordered dataTable sell_table' style='width:100%;background-color:#FFFFFF;'>
<thead>
<tr>
<th>S.no</th>
<th>Date</th>
<th>Quantity</th>
<th>Price</th>
<th>Total Price</th>
<th>Action</th>
</tr>
</thead>
<tbody>
$sell_tr_line
</tbody>
</table>
</div>
</div>
</div>
</div>";
}
$final_info = "<table style='font-size:16px;font-weight:bold;'>
<tr>
<td>$short_name</td>
<td style='color:#1883E9;padding:8px;'> - $security_name</td>
</tr>
</table>
<div class='panel-group' id='accordion' role='tablist' aria-multiselectable='true'>
$head_line
</div>";
return $final_info;
}
public function sell_save(){
$sell_id = (int)$this->input->post('sell_id');
$buy_id = $this->input->post('buy_id');
$sell_date = date('Y-m-d',strtotime($this->input->post('sell_date')));
$sell_quantity = $this->input->post('sell_quantity');
$sell_price = $this->input->post('sell_price');
$sell_total_amt = floor($sell_quantity * $sell_price);
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
$buy_qry = 'SELECT * FROM cw_stock_buy where prime_stock_buy_id = "'.$buy_id.'" and trans_status = 1 ';
$buy_data = $this->db->query("CALL sp_a_run ('SELECT','$buy_qry')");
$buy_rslt = $buy_data->result();
$buy_count = (int)$buy_data->num_rows();
$buy_data->next_result();
if($buy_count === 1){
$prime_stock_id = $buy_rslt[0]->prime_stock_id;
$purchased_quantity = $buy_rslt[0]->purchased_quantity;
$buy_quantity = $buy_rslt[0]->quantity;
$buy_price = $buy_rslt[0]->price;
$buy_date = $buy_rslt[0]->date;
$can_sell_qnt = floor($buy_quantity - $sell_quantity);
$can_process = false;
if($can_sell_qnt >= 0){
$can_process = true;
}
if(!$can_process){
echo json_encode(array('success' => FALSE, 'message' => "Trying to sell more than buy quantity"));
}else{
$date_can_process = false;
$date2 = new DateTime($sell_date);
$date_now = new DateTime($buy_date);
if($date2 >= $date_now) {
$date_can_process = true;
}
if(!$date_can_process){
echo json_encode(array('success' => FALSE, 'message' => "Please change sell date greater than equal to buy date"));
}else{
$total_amount = floor($sell_quantity*$sell_price);
if($sell_id === 0){
$sell_qry = 'INSERT INTO cw_stock_sell (prime_stock_buy_id, prime_stock_id,date,quantity,price,total_amount,trans_created_by, trans_created_date) VALUES ("'.$buy_id.'","'.$prime_stock_id.'","'.$sell_date.'","'.$sell_quantity.'","'.$sell_price.'","'.$total_amount.'","'.$logged_id.'","'.$date.'")';
$this->db->query("CALL sp_a_run ('RUN','$sell_qry')");
$rslt_array = array('success' => true, 'message' => "Stock sell successfully updated");
}else{
$upd_qry = 'UPDATE cw_stock_sell SET date = "'.$sell_date.'",quantity = "'.$sell_quantity.'",price = "'.$sell_price.'",total_amount = "'.$total_amount.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$date.'" where prime_stock_sell_id = "'.$sell_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$upd_qry')");
$rslt_array = array('success' => true, 'message' => "Stock updated successfully updated");
}
$sell_qry = 'SELECT IFNULL(sum(quantity),0) as quantity FROM cw_stock_sell where prime_stock_buy_id = "'.$buy_id.'" and trans_status = 1 ';
$sell_data = $this->db->query("CALL sp_a_run ('SELECT','$sell_qry')");
$sell_rslt = $sell_data->result();
$sell_data->next_result();
$total_sell_qnt = $sell_rslt[0]->quantity;
$balance_qnt = floor($purchased_quantity - $total_sell_qnt);
// UPDATED BUY QUANTITY
$buy_total_amt = floor($balance_qnt*$buy_price);
$stock_buy_qry = 'UPDATE cw_stock_buy SET quantity = "'.$balance_qnt.'",total_amount = "'.$buy_total_amt.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$created_on.'" where prime_stock_buy_id = "'.$buy_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$stock_buy_qry')");
// UPDATED TOTAL STOCK QUANTITY
$final_quantity = 0;
$upd_head_qry = 'SELECT IFNULL(sum(quantity),0) as quantity,IFNULL(sum(total_amount),0) as total_amount FROM cw_stock_buy where prime_stock_id = "'.$prime_stock_id.'" and trans_status = 1 ';
$upd_head_data = $this->db->query("CALL sp_a_run ('SELECT','$upd_head_qry')");
$upd_head_rslt = $upd_head_data->result();
$upd_head_data->next_result();
$final_quantity = $upd_head_rslt[0]->quantity;
$total_amount = $upd_head_rslt[0]->total_amount;
$avg_invest_price = floor($total_amount/$final_quantity);
$stock_head_qry = 'UPDATE cw_stock SET quantity = "'.$final_quantity.'",avg_invest_price = "'.$avg_invest_price.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$created_on.'" where prime_stock_id = "'.$prime_stock_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$stock_head_qry')");
$rslt_array['view_info'] = $this->get_buy_list($prime_stock_id);
echo json_encode($rslt_array);
}
}
}else{
echo json_encode(array('success' => FALSE, 'message' => "Unable to process your request"));
}
}
public function sell_edit(){
$process_id = $this->input->post('process_id');
$sell_id = $this->input->post('sell_id');
$sell_qry = 'SELECT * FROM cw_stock_sell where prime_stock_sell_id = "'.$sell_id.'" and trans_status = 1 ';
$sell_data = $this->db->query("CALL sp_a_run ('SELECT','$sell_qry')");
$sell_rslt = $sell_data->result();
$sell_count = (int)$sell_data->num_rows();
$sell_data->next_result();
if($sell_count === 1){
$prime_stock_sell_id = $sell_rslt[0]->prime_stock_sell_id;
$prime_stock_buy_id = $sell_rslt[0]->prime_stock_buy_id;
$prime_stock_id = $sell_rslt[0]->prime_stock_id;
$date = date('d-m-Y',strtotime($sell_rslt[0]->date));
$quantity = $sell_rslt[0]->quantity;
$price = $sell_rslt[0]->price;
$rslt_info = array("sell_date_$process_id"=> $date,"sell_id_$process_id"=> $prime_stock_sell_id,"buy_id_$process_id"=> $prime_stock_buy_id,"sell_quantity_$process_id"=> $quantity,"sell_price_$process_id"=> $price);
echo json_encode(array('success' => true,'message' => "",'rslt_info'=>$rslt_info));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Unable to process your request"));
}
}
public function sell_delete(){
$process_id = $this->input->post('process_id');
$sell_id = $this->input->post('sell_id');
$sell_qry = 'SELECT * FROM cw_stock_sell where prime_stock_sell_id = "'.$sell_id.'" and trans_status = 1 ';
$sell_data = $this->db->query("CALL sp_a_run ('SELECT','$sell_qry')");
$sell_rslt = $sell_data->result();
$sell_count = (int)$sell_data->num_rows();
$sell_data->next_result();
if($sell_count === 1){
$prime_stock_buy_id = $sell_rslt[0]->prime_stock_buy_id;
$prime_stock_id = $sell_rslt[0]->prime_stock_id;
$sell_quantity = $sell_rslt[0]->quantity;
$sell_price = $sell_rslt[0]->price;
$buy_qry = 'SELECT * FROM cw_stock_buy where prime_stock_buy_id = "'.$prime_stock_buy_id.'" and trans_status = 1 ';
$buy_data = $this->db->query("CALL sp_a_run ('SELECT','$buy_qry')");
$buy_rslt = $buy_data->result();
$buy_data->next_result();
$buy_quantity = $buy_rslt[0]->quantity;
$buy_price = $buy_rslt[0]->price;
$final_quantity = floor($sell_quantity + $buy_quantity);
$final_total = floor($final_quantity *$buy_price);
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
// UPDATE BUY RECORD
$stock_buy_qry = 'UPDATE cw_stock_buy SET quantity = "'.$final_quantity.'",total_amount = "'.$final_total.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$created_on.'" where prime_stock_buy_id = "'.$prime_stock_buy_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$stock_buy_qry')");
// UPDATED TOTAL STOCK QUANTITY
$final_quantity = 0;
$upd_head_qry = 'SELECT IFNULL(sum(quantity),0) as quantity,IFNULL(sum(total_amount),0) as total_amount FROM cw_stock_buy where prime_stock_id = "'.$prime_stock_id.'" and trans_status = 1 ';
$upd_head_data = $this->db->query("CALL sp_a_run ('SELECT','$upd_head_qry')");
$upd_head_rslt = $upd_head_data->result();
$upd_head_data->next_result();
$final_quantity = $upd_head_rslt[0]->quantity;
$total_amount = $upd_head_rslt[0]->total_amount;
$avg_invest_price = floor($total_amount/$final_quantity);
$stock_head_qry = 'UPDATE cw_stock SET quantity = "'.$final_quantity.'",avg_invest_price = "'.$avg_invest_price.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$created_on.'" where prime_stock_id = "'.$prime_stock_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$stock_head_qry')");
$del_qry = 'UPDATE cw_stock_sell SET trans_status = 0,trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$created_on.'" where prime_stock_sell_id = "'.$sell_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$del_qry')");
$view_info = $this->get_buy_list($prime_stock_id);
echo json_encode(array('success' => true, 'message' => "Stock deleted",'view_info'=>$view_info));
}else{
echo json_encode(array('success' => FALSE, 'message' => "Unable to process your request"));
}
}
public function edit_save(){
$prime_stock_buy_id = $this->input->post('prime_stock_buy_id');
$edit_quantity = $this->input->post('edit_quantity');
$edit_price = $this->input->post('edit_price');
$total_amount = floor($edit_quantity *$edit_price);
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
$buy_qry = 'SELECT * FROM cw_stock_buy where prime_stock_buy_id = "'.$prime_stock_buy_id.'" and trans_status = 1 ';
$buy_data = $this->db->query("CALL sp_a_run ('SELECT','$buy_qry')");
$buy_rslt = $buy_data->result();
$buy_data->next_result();
$prime_stock_id = $buy_rslt[0]->prime_stock_id;
$sell_qry = 'SELECT IFNULL(sum(quantity),0) as quantity FROM cw_stock_sell where prime_stock_buy_id = "'.$prime_stock_buy_id.'" and trans_status = 1 ';
$sell_data = $this->db->query("CALL sp_a_run ('SELECT','$sell_qry')");
$sell_rslt = $sell_data->result();
$sell_data->next_result();
$total_sell_qnt = $sell_rslt[0]->quantity;
if($edit_quantity >= $total_sell_qnt){
// UPDATE BUY RECORD
$quantity = floor($edit_quantity - $total_sell_qnt);
$total_amount = floor($quantity * $edit_price);
$stock_buy_qry = 'UPDATE cw_stock_buy SET purchased_quantity = "'.$edit_quantity.'",quantity = "'.$quantity.'",price = "'.$edit_price.'",total_amount = "'.$total_amount.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$created_on.'" where prime_stock_buy_id = "'.$prime_stock_buy_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$stock_buy_qry')");
// UPDATED TOTAL STOCK QUANTITY
$final_quantity = 0;
$upd_head_qry = 'SELECT IFNULL(sum(quantity),0) as quantity,IFNULL(sum(total_amount),0) as total_amount FROM cw_stock_buy where prime_stock_id = "'.$prime_stock_id.'" and trans_status = 1 ';
$upd_head_data = $this->db->query("CALL sp_a_run ('SELECT','$upd_head_qry')");
$upd_head_rslt = $upd_head_data->result();
$upd_head_data->next_result();
$final_quantity = $upd_head_rslt[0]->quantity;
$total_amount = $upd_head_rslt[0]->total_amount;
$avg_invest_price = floor($total_amount/$final_quantity);
$stock_head_qry = 'UPDATE cw_stock SET quantity = "'.$final_quantity.'",avg_invest_price = "'.$avg_invest_price.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$created_on.'" where prime_stock_id = "'.$prime_stock_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$stock_head_qry')");
$view_info = $this->get_buy_list($prime_stock_id);
echo json_encode(array('success' => true, 'message' => "Stock updated",'view_info'=>$view_info));
}else{
echo json_encode(array('success' => false, 'message' => "Adding less than previous sell quantity"));
}
exit(0);
}
public function remove_buy(){
$prime_stock_buy_id = $this->input->post('prime_stock_buy_id');
$logged_id = $this->session->userdata('logged_id');
$date = date("Y-m-d h:i:s");
$buy_qry = 'SELECT * FROM cw_stock_buy where prime_stock_buy_id = "'.$prime_stock_buy_id.'" and trans_status = 1 ';
$buy_data = $this->db->query("CALL sp_a_run ('SELECT','$buy_qry')");
$buy_rslt = $buy_data->result();
$buy_data->next_result();
$prime_stock_id = $buy_rslt[0]->prime_stock_id;
// UPDATE BUY RECORD
$stock_buy_qry = 'UPDATE cw_stock_buy SET trans_status = 0,trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$created_on.'" where prime_stock_buy_id = "'.$prime_stock_buy_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$stock_buy_qry')");
// UPDATED TOTAL STOCK QUANTITY
$final_quantity = 0;
$upd_head_qry = 'SELECT IFNULL(sum(quantity),0) as quantity,IFNULL(sum(total_amount),0) as total_amount FROM cw_stock_buy where prime_stock_id = "'.$prime_stock_id.'" and trans_status = 1 ';
$upd_head_data = $this->db->query("CALL sp_a_run ('SELECT','$upd_head_qry')");
$upd_head_rslt = $upd_head_data->result();
$upd_head_data->next_result();
$final_quantity = $upd_head_rslt[0]->quantity;
$total_amount = $upd_head_rslt[0]->total_amount;
$avg_invest_price = floor($total_amount/$final_quantity);
$stock_head_qry = 'UPDATE cw_stock SET quantity = "'.$final_quantity.'",avg_invest_price = "'.$avg_invest_price.'",trans_updated_by = "'.$logged_id.'",trans_updated_date = "'.$created_on.'" where prime_stock_id = "'.$prime_stock_id.'"';
$this->db->query("CALL sp_a_run ('RUN','$stock_head_qry')");
$view_info = $this->get_buy_list($prime_stock_id);
echo json_encode(array('success' => true, 'message' => "Stock Removed",'view_info'=>$view_info));
}
}
?>