I am new at stackoverflow, please accept my apologies for the long question. I have a large database of a retail outlet. We wanted to prepare a report to get the opening and closing balances of products, products have batch number and every batch has unique serial number which are assigned to multiple locations. The challenge is to get the opening balances of each batch of the selected product for each warehouse location. If location isn't specified in report filter then the report should give the result for each location's selected product and all of its batches.
I get all the locations using PHP / PDO and loop through it, with in the first loop i get all the batches against the selected product and with in the batches loop i first get the opening balance (which is calculated like, sum of quantity before the from date filter), i then calculate the quantity received of that product's batch in that location (sum of quantity received within the from and to date filter), then quantity sold or quantity moved to other warehouses (sum of quantity moved to other warehouse within the selected from and to date filter).
I can now calculate the closing balance as i have all the values. This runs fine but when the query runs for 20 warehouses and there are almost 20+ batches for a single product, the report takes ages to process and MySQL CPU usage goes upto 200%.
I tried to optimize the queries, my tables are properly indexed. Tables have alot of data. millions of records. I need advise on how to improve my code or methods, what am I doing wrong and how can I make the report faster.
I am using code igniter for my application and following is the code.
foreach($locations as $loc){
//if batch is selected put it in array
if($query_array['batch_no'] != ''){
$batches[] = $query_array['batch_no'];
}else{
// or get all batches from the inventory table for the location
$this->db->select('distinct(batch_no)');
$this->db->from('product_details');
$this->db->where('product_id',$query_array ['product']);
$this->db->where('warehouse_id',$loc);
$query = $this->db->get();
foreach($query->result() as $row){
$batches[] = $row->batch_no;
}
$query->free_result();
}
foreach($batches as $batch){
//GIN IN Opening Balance
$this->db->select('IFNULL(SUM(gi.qty),0) as gin_in',FALSE);
$this->db->from('gin as g');
$this->db->join('gin_items as gi','gi.gin_id=g.id');
$this->db->where('gi.product_id',$query_array ['product']);
$this->db->where('DATE(g.creation_date) < ',$query_array ['date_from']);
$this->db->where('g.to_location_id', $loc);
$this->db->where_in('gi.batch_no',$batch);
$this->db->where_in('gi.status',2);
$this->db->where('g.status',3);
$query = $this->db->get();
if($query->num_rows()==1)
{
$var1=$query->row()->gin_in;
$query->free_result();
}
// SUM (Return Invoices for a specific product, for this particular store,
// before this date range) -> $var2
$this->db->select('IFNULL(SUM(ii.qty),0) as sale_return_in',FALSE);
$this->db->from('return_sales_invoice rs');
$this->db->join('return_invoice_items as ii','ii.invoice_id=rs.id');
$this->db->where('ii.medicine_id',$query_array ['product']);
$this->db->where_in('ii.batch_no',$batch);
$this->db->where_in('rs.location_id',$loc);
$this->db->where('rs.dated < ',$query_array ['date_from']);
$query = $this->db->get();
if($query->num_rows()==1){
$var2=$query->row()->sale_return_in;
$query->free_result();
}
//SUM (Sales Invoices of a specific product, from this particular store,
// before this date range) -> $var3
$this->db->select('IFNULL(SUM(ii.qty),0) as sale_out',FALSE);
$this->db->from('sales_invoice si');
$this->db->join('invoice_items as ii','ii.invoice_id=si.id');
$this->db->where('ii.medicine_id',$query_array ['product']);
$this->db->where_in('ii.batch_no',$batch);
$this->db->where_in('si.location_id',$loc);
$this->db->where('si.dated < ',$query_array ['date_from']);
$query = $this->db->get();
if($query->num_rows()==1){
$var3=$query->row()->sale_out;
$query->free_result();
}
// SUM (GIN of a specific product, from this particular store,
// before this date range) -> $var4
// if from location then minis stock
$this->db->select('IFNULL(SUM(gi.qty),0) as gin_out',FALSE);
$this->db->from('gin as g');
$this->db->join('gin_items as gi','gi.gin_id=g.id');
$this->db->where('DATE(g.creation_date) < ',$query_array ['date_from']);
$this->db->where('gi.product_id',$query_array ['product']);
$this->db->where_in('gi.batch_no',$batch);
$this->db->where_in('g.from_location_id',$loc);
$this->db->where('gi.status',2);
$this->db->where('g.status',3);
$query = $this->db->get();
if($query->num_rows()==1){
$var4=$query->row()->gin_out;
$query->free_result();
}
$op_bal = ($var1 + $var2) - ($var3 + $var4);
//---------------------------------------------------------------------------------
$where_from = "DATE(g.creation_date) >='" . $query_array ['date_from'] . "'";
$where_to = "DATE(g.creation_date) <='" . $query_array ['date_to'] . "'";
$rs_where_from = "DATE(rs.creation_date) >='" . $query_array ['date_from'] . "'";
$rs_where_to = "DATE(rs.creation_date) <='" . $query_array ['date_to'] . "'";
$si_where_from = "DATE(si.creation_date) >='" . $query_array ['date_from'] . "'";
$si_where_to = "DATE(si.creation_date) <='" . $query_array ['date_to'] . "'";
//GIN IN Opening Balance
$this->db->select('IFNULL(SUM(gi.qty),0) as gin_in',FALSE);
$this->db->from('gin as g');
$this->db->join('gin_items as gi','gi.gin_id=g.id');
$this->db->where($where_from);
$this->db->where($where_to);
$this->db->where('gi.product_id',$query_array ['product']);
$this->db->where_in('gi.batch_no',$batch);
$this->db->where_in('g.to_location_id', $loc);
$this->db->where('g.status',3);
$this->db->where('gi.status',2);
$query = $this->db->get();
if($query->num_rows()==1){
$g_stock_in=$query->row()->gin_in;
$query->free_result();
}
// SUM (Return Invoices for a specific product, for this particular store,
// before this date range) -> $var2
$this->db->select('IFNULL(SUM(ii.qty),0) as sale_return_in',FALSE);
$this->db->from('return_sales_invoice rs');
$this->db->join('return_invoice_items as ii','ii.invoice_id=rs.id');
$this->db->where('ii.medicine_id',$query_array ['product']);
$this->db->where($rs_where_from);
$this->db->where($rs_where_to);
$this->db->where_in('ii.batch_no',$batch);
$this->db->where_in('rs.location_id',$loc);
$query = $this->db->get();
if($query->num_rows()==1){
$s_stock_in=$query->row()->sale_return_in;
$query->free_result();
}
//SUM (Sales Invoices of a specific product, from this particular store,
// before this date range) -> $var3
$this->db->select('IFNULL(SUM(ii.qty),0) as sale_out',FALSE);
$this->db->from('sales_invoice si');
$this->db->join('invoice_items as ii','ii.invoice_id=si.id');
$this->db->where('ii.medicine_id',$query_array ['product']);
$this->db->where_in('ii.batch_no',$batch);
$this->db->where_in('si.location_id',$loc);
$this->db->where($si_where_from);
$this->db->where($si_where_to);
$query = $this->db->get();
if($query->num_rows()==1){
$s_stock_out=$query->row()->sale_out;
$query->free_result();
}
// SUM (GIN of a specific product, from this particular store,
// before this date range) -> $var4
// if from location then minis stock
$this->db->select('IFNULL(SUM(gi.qty),0) as gin_out',FALSE);
$this->db->from('gin as g');
$this->db->join('gin_items as gi','gi.gin_id=g.id');
$this->db->where($where_from);
$this->db->where($where_to);
$this->db->where('gi.product_id',$query_array ['product']);
$this->db->where_in('gi.batch_no',$batch);
$this->db->where_in('g.from_location_id',$loc);
$this->db->where('g.status',3);
$this->db->where('gi.status',2);
$query = $this->db->get();
if($query->num_rows()==1){
$g_stock_out=$query->row()->gin_out;
$query->free_result();
}
$qty_in=$g_stock_in+$s_stock_in;
$qty_out=$g_stock_out+$s_stock_out;
$productName=$this->getProductName($query_array ['product']);
$locationName=$this->getLocationName($loc);
$data [] = array (
'location' => $locationName,
'product' => $productName,
'batchno' => $batch ,
'op_bal' => $res['op_bal'] ,
'qty_in' => $qty_in,
'qty_out' => $qty_out,
'cl_bal' => ($res['op_bal'] + $qty_in ) - $qty_out
);
}
}
return $data;
Aucun commentaire:
Enregistrer un commentaire