<?php

include('db_con.php');
  if (isset($_SESSION['user_id'])) {
    $user_type = $_SESSION['user_type'];
    $user_id = $_SESSION['user_id'];
    $user_name = $_SESSION['user_name'];
    $branch_id = $_SESSION['branch_id'];
    $branch_name = $_SESSION['branch_name'];
    $store_id = $_SESSION['store_id'];
    $store_name = $_SESSION['store_name'];
}else{
  header('Location: login');
  die();
}
$output = array();
$sql = "SELECT app_status FROM app_settings WHERE app_name = 'stock_value'";
            $statement = $connect->prepare($sql);
            $statement->execute(); // no need to add `$sql` here, you can take that out
            $stock_value = $statement->fetchColumn();
          $sql = "SELECT IFNULL(doc_status,'OFF') FROM user_rights WHERE user_id = '".$user_id."' and doc_name = 'cost'";
            $statement = $connect->prepare($sql);
            $statement->execute(); // no need to add `$sql` here, you can take that out
            $cost = $statement->fetchColumn();
            $type_id = $_POST['type_id'];
            $item_id = $_POST['item_id'];
            $cust_id = $_POST['cust_id'];
$query = '';
$query = "
  select a.id,a.inv_no,a.dated,a.item_name,a.name,a.description,a.item_cost,a.lp_rate,a.rate,a.qty_in,a.qty_out from(
  select m.p_id as id,m.p_id as inv_no, m.dated,i.item_name,v.acc_name name,'Purchased' as description,i.item_cost,i.lp_rate,FORMAT(d.rate,2)as rate,d.qty qty_in,0 as qty_out From
  purchase m,purchase_detail d,accounts v,items i where m.p_id=d.p_id and v.acc_no=m.v_id and i.item_id=d.item_no 
  union all
  select m.s_id as id,m.inv_no, m.dated,i.item_name,c.acc_name name,'Sale' as description,i.item_cost,i.lp_rate,d.rate,0 as qty_in,d.qty qty_out From
  sale m,sale_detail d,accounts c,items i where m.s_id=d.s_id and c.acc_no=m.cust_id and i.item_id=d.item_no
    union all
  select m.pr_id as id,m.pr_id as inv_no, m.dated, i.item_name,v.acc_name name,'Pur Return' as description,i.lp_rate,i.item_cost,d.rate,0 as qty_in,d.qty qty_out From
  pr m,pr_detail d,accounts v,items i where m.pr_id=d.pr_id and v.acc_no=m.v_id and i.item_id=d.item_no 
  union all
  select m.sr_id as id,m.sr_id as inv_no, m.dated, i.item_name,c.acc_name name,'Sale Return' as description,i.item_cost,i.lp_rate,d.rate, d.qty as qty_in,0 as qty_out From
  sr m,sr_detail d,accounts c,items i where m.sr_id=d.sr_id and c.acc_no=m.cust_id and i.item_id=d.item_no  
  )a WHERE";
if($type_id > 0)
{
	$query = "
  select a.id,a.inv_no,a.dated,a.item_name,a.name,a.description,a.item_cost,a.lp_rate,a.rate,a.qty_in,a.qty_out from(
  select m.p_id as id,m.p_id as inv_no, m.dated,i.item_name,v.acc_name name,'Purchased' as description,i.item_cost,i.lp_rate,FORMAT(d.rate,2)as rate,d.qty qty_in,0 as qty_out From
  purchase m,purchase_detail d,accounts v,items i where m.p_id=d.p_id and v.acc_no=m.v_id and i.item_id=d.item_no  and i.type_id = '".$type_id."'
  union all
  select m.s_id as id,m.inv_no, m.dated,i.item_name,c.acc_name name,'Sale' as description,i.item_cost,i.lp_rate,d.rate,0 as qty_in,d.qty qty_out From
  sale m,sale_detail d,accounts c,items i where m.s_id=d.s_id and c.acc_no=m.cust_id and i.item_id=d.item_no and i.type_id = '".$type_id."' 
    union all
  select m.pr_id as id,m.pr_id as inv_no, m.dated, i.item_name,v.acc_name name,'Pur Return' as description,i.item_cost,i.lp_rate,d.rate,0 as qty_in,d.qty qty_out From
  pr m,pr_detail d,accounts v,items i where m.pr_id=d.pr_id and v.acc_no=m.v_id and i.item_id=d.item_no and i.type_id  = '".$type_id."' 
  union all
  select m.sr_id as id,m.sr_id as inv_no, m.dated, i.item_name,c.acc_name name,'Sale Return' as description,i.item_cost,i.lp_rate,d.rate, d.qty as qty_in,0 as qty_out From
  sr m,sr_detail d,accounts c,items i where m.sr_id=d.sr_id and c.acc_no=m.cust_id and i.item_id=d.item_no and  i.type_id = '".$type_id."'    
  )a WHERE";
}

if($type_id > 0 && $item_id > 0)
{
	$query = "
  select a.id,a.inv_no,a.dated,a.item_name,a.name,a.description,a.item_cost,a.lp_rate,a.rate,a.qty_in,a.qty_out from(
  select m.p_id as id,m.p_id as inv_no, m.dated,i.item_name,v.acc_name name,'Purchased' as description,i.item_cost,i.lp_rate,FORMAT(d.rate,2)as rate,d.qty qty_in,0 as qty_out From
  purchase m,purchase_detail d,accounts v,items i where m.p_id=d.p_id and v.acc_no=m.v_id and i.item_id=d.item_no  and i.type_id = '".$type_id."' and i.item_id = '".$item_id."'
  union all
  select m.s_id as id,m.inv_no, m.dated,i.item_name,c.acc_name name,'Sale' as description,i.item_cost,i.lp_rate,d.rate, 0 as qty_in,d.qty qty_out From
  sale m,sale_detail d,accounts c,items i where m.s_id=d.s_id and c.acc_no=m.cust_id and i.item_id=d.item_no and i.type_id = '".$type_id."' and i.item_id = '".$item_id."' 
    union all
  select m.pr_id as id,m.pr_id as inv_no, m.dated, i.item_name,v.acc_name name,'Pur Return' as description,i.item_cost,i.lp_rate,d.rate, 0 as qty_in,d.qty qty_out From
  pr m,pr_detail d,accounts v,items i where m.pr_id=d.pr_id and v.acc_no=m.v_id and i.item_id=d.item_no and i.type_id  = '".$type_id."' and i.item_id = '".$item_id."'
  union all
  select m.sr_id as id,m.sr_id as inv_no, m.dated, i.item_name,c.acc_name name,'Sale Return' as description,i.item_cost,i.lp_rate,d.rate, d.qty as qty_in,0 as qty_out From
  sr m,sr_detail d,accounts c,items i where m.sr_id=d.sr_id and c.acc_no=m.cust_id and i.item_id=d.item_no and  i.type_id = '".$type_id."'  and i.item_id = '".$item_id."'  
  )a WHERE";
}
  
  if($cust_id > 0 && $type_id > 0)
{
$query = "
  select a.id,a.inv_no,a.dated,a.item_name,a.name,a.description,a.item_cost,a.lp_rate,a.rate,a.qty_in,a.qty_out from(
  
  select m.s_id as id,m.inv_no, m.dated,i.item_name,c.acc_name name,'Sale' as description,i.item_cost,i.lp_rate,d.rate,0 as qty_in,d.qty as qty_out From
  sale m,sale_detail d,accounts c,items i where m.s_id=d.s_id and c.acc_no=m.cust_id and i.item_id=d.item_no and i.type_id = '".$type_id."' and m.cust_id = '".$cust_id."' 
  
  union all
  select m.sr_id as id,m.sr_id as inv_no, m.dated, i.item_name,c.acc_name name,'Sale Return' as description,i.item_cost,i.lp_rate,d.rate, d.qty as qty_in,0 as qty_out From
  sr m,sr_detail d,accounts c,items i where m.sr_id=d.sr_id and c.acc_no=m.cust_id and i.item_id=d.item_no and i.type_id = '".$type_id."'  and m.cust_id = '".$cust_id."' 
  
  )a WHERE";   
  }


   if($cust_id > 0 && $type_id > 0 && $item_id > 0)
{
$query = "
  select a.id,a.inv_no,a.dated,a.item_name,a.name,a.description,a.item_cost,a.lp_rate,a.rate,a.qty_in,a.qty_out from(
  
  select m.s_id as id,m.inv_no, m.dated,concat(t.type_name, ' ',i.item_name) item_name,c.acc_name name,'Sale' as description,i.item_cost,i.lp_rate,d.rate,0 as qty_in,d.qty as qty_out From
  sale m,sale_detail d,accounts c,items i where m.s_id=d.s_id and c.acc_no=m.cust_id and i.item_id=d.item_no and i.item_id = '".$item_id."' and i.type_id = '".$type_id."' and m.cust_id = '".$cust_id."' 
  
  union all
  select m.sr_id as id,m.sr_id as inv_no, m.dated, i.item_name,c.acc_name name,'Sale Return' as description,i.item_cost,i.lp_rate,d.rate, d.qty as qty_in,0 as qty_out From
  sr m,sr_detail d,accounts c,items i where m.sr_id=d.sr_id and c.acc_no=m.cust_id and i.item_id=d.item_no and i.item_id = '".$item_id."%' and i.type_id = '".$type_id."'  and m.cust_id = '".$cust_id."' 
  
  )a WHERE";   
  }
  
//$query .= 'SELECT i.item_no,i.item_name,i.item_type,i.item_size,i.item_cost,i.item_cost_sm,
//s.stock as op_stock from items i,cur_stock s where i.item_no=s.item_no AND';
if(isset($_POST["search"]["value"]))
{
	$query .= '( description LIKE "%'.$_POST["search"]["value"].'%" ';
	$query .= 'OR dated LIKE "%'.$_POST["search"]["value"].'%" ';
	$query .= 'OR id LIKE "%'.$_POST["search"]["value"].'%" ';
	$query .= 'OR item_name LIKE "%'.$_POST["search"]["value"].'%" ';
	$query .= 'OR name LIKE "%'.$_POST["search"]["value"].'%" )';
	
	
	
	
}
if(isset($_POST["order"]))
{
	$query .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
}
else
{
	$query .= 'ORDER BY dated asc ';
}
if($_POST["length"] != -1)
{
	$query .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$statement = $connect->prepare($query);
$statement->execute();
$result = $statement->fetchAll();
$data = array();
$filtered_rows = $statement->rowCount();
$stock = 0;
foreach($result as $row)
{
	
	$qty_in = $row['qty_in'];
	$qty_out = $row['qty_out'];
	$stock = $stock + $qty_in - $qty_out;
	$sub_array = array();
	//$sub_array[] = $row['item_id'];
	$sub_array[] = $row['id'];
	
	$cost =  '<span class="text-danger">'.$row["item_cost"].'</span>';
	$sub_array[] = $row['dated'];
	
	$sub_array[] = $row['name'];
	$sub_array[] = $row['item_name'];
	$sub_array[] = $row['description'];
  $sub_array[] = $cost;
	$sub_array[] = $row['lp_rate'];
  $sub_array[] = $row["rate"];
	$sub_array[] = $row['qty_in'];
	$sub_array[] = $row['qty_out'];
	$sub_array[] = $stock;
	
	
	$data[] = $sub_array;
}
$output = array(
	"draw"				=>	intval($_POST["draw"]),
	"recordsTotal"  	=>  $filtered_rows,
	"recordsFiltered" 	=> 	get_total_all_records($connect),
	"data"    			=> 	$data
);
echo json_encode($output);
function get_total_all_records($connect)
{
	$statement = $connect->prepare("SELECT * FROM items");
	$statement->execute();
	return $statement->rowCount();
}
?>