CodeIgniter- Arrange a report according to batches

Question

I am working on Codeigniter. As a newbie, I am engaged in updating a stock report batch-wise.

Below is the report view

There is more than one batch per product

I want to re-create this report batch-wise, so the report would show batch-wise rate, in-out qty, stock and etc

Actual Code

Controller

 public function task_checkStocklist(){
    // GET data
    $postData = $this->input->post();
    $data = $this->report_model->task_getStock($postData);
   
            echo json_encode($data);
} 

Model

public function task_getStock($postData=null){

    $response = array();

     ## Read value
     $draw = $postData['draw'];
     $start = $postData['start'];
     $rowperpage = $postData['length']; // Rows display per page
     $columnIndex = $postData['order'][0]['column']; // Column index
     $columnName = $postData['columns'][$columnIndex]['data']; // Column name
     $columnSortOrder = $postData['order'][0]['dir']; // asc or desc
     $searchValue = $postData['search']['value']; // Search value

     ## Search 
     $searchQuery = "";
     if($searchValue != ''){
        $searchQuery = " (a.product_name like '%".$searchValue."%' or a.product_model like '%".$searchValue."%') ";
     }

     ## Total number of records without filtering
     $this->db->select('count(*) as allcount');
     $this->db->from('product_information a');
      if($searchValue != ''){
     $this->db->where($searchQuery);
 }
    $this->db->group_by('a.product_id');
     $records = $this->db->get()->num_rows();
     $totalRecords = $records;

     ## Total number of record with filtering
     $this->db->select('count(*) as allcount');
     $this->db->from('product_information a');
     if($searchValue != ''){
        $this->db->where($searchQuery);
    }
     $this->db->group_by('a.product_id');
     $records = $this->db->get()->num_rows();
     $totalRecordwithFilter = $records;

     ## Fetch records
     $this->db->select("a.*,
            a.product_name,
            a.product_id,
            a.product_model
            ");
     $this->db->from('product_information a');
     if($searchValue != '')
     $this->db->where($searchQuery);
     $this->db->order_by($columnName, $columnSortOrder);
     $this->db->group_by('a.product_id');
     $this->db->limit($rowperpage, $start);
     $records = $this->db->get()->result();
     $data = array();
     $sl =1;
     foreach($records as $record ){
      $stockin = $this->db->select('sum(quantity) as totalSalesQnty')->from('invoice_details')->where('product_id',$record->product_id)->get()->row();
     $stockout = $this->db->select('sum(quantity) as totalPurchaseQnty,Avg(rate) as purchaseprice')->from('product_purchase_details')->where('product_id',$record->product_id)->get()->row();
        

        $sprice = (!empty($record->price)?$record->price:0);
        $pprice = (!empty($stockout->purchaseprice)?sprintf('%0.2f',$stockout->purchaseprice):0); 
        $stock =  (!empty($stockout->totalPurchaseQnty)?$stockout->totalPurchaseQnty:0)-(!empty($stockin->totalSalesQnty)?$stockin->totalSalesQnty:0);
        $data[] = array( 
            'sl'            =>   $sl,
            'product_name'  =>  $record->product_name,
            'product_model' =>  $record->product_model,
            'sales_price'   =>  sprintf('%0.2f',$sprice),
            'purchase_p'    =>  $pprice,
            'totalPurchaseQnty'=>$stockout->totalPurchaseQnty,
            'totalSalesQnty'=>  $stockin->totalSalesQnty,
            'stok_quantity' => sprintf('%0.2f',$stock),
            
            'total_sale_price'=> ($stockout->totalPurchaseQnty-$stockin->totalSalesQnty)*$sprice,
            'purchase_total' =>  ($stockout->totalPurchaseQnty-$stockin->totalSalesQnty)*$pprice,
        ); 
        $sl++;
     }

     ## Response
     $response = array(
        "draw" => intval($draw),
        "iTotalRecords" => $totalRecordwithFilter,
        "iTotalDisplayRecords" => $totalRecords,
        "aaData" => $data
     );

     return $response; 
}

Updated Code

Below is the code which I have updated

public function task_getStock($postData=null){

     $response = array();

     ## Read value
     $draw = $postData['draw'];
     $start = $postData['start'];
     $rowperpage = $postData['length']; // Rows display per page
     $columnIndex = $postData['order'][0]['column']; // Column index
     $columnName = $postData['columns'][$columnIndex]['data']; // Column name
     $columnSortOrder = $postData['order'][0]['dir']; // asc or desc
     $searchValue = $postData['search']['value']; // Search value
    //  var_dump($response);
    //  die();
     ## Search 
     $searchQuery = "";
     if($searchValue != ''){
        $searchQuery = " (a.product_name like '%".$searchValue."%' or a.product_model like '%".$searchValue."%') ";
     }

     ## Total number of records without filtering
     $this->db->select('count(*) as allcount');
     $this->db->from('product_information a');
     //$this->db->from('product_purchase_details a');
     $this->db->join('product_purchase_details b','a.product_id = b.product_id','left');
      if($searchValue != ''){
     $this->db->where($searchQuery);
 }
     $this->db->group_by('b.batch_id');
     $records = $this->db->get()->num_rows();
     $totalRecords = $records;

     ## Total number of record with filtering
     $this->db->select('count(*) as allcount');
      $this->db->from('product_information a');
     //$this->db->from('product_purchase_details a');
     $this->db->join('product_purchase_details b','a.product_id = b.product_id','left');
     if($searchValue != ''){
        $this->db->where($searchQuery);
    }
     //$this->db->group_by('a.product_id');
      $this->db->group_by('b.batch_id');
     $records = $this->db->get()->num_rows();
     $totalRecordwithFilter = $records;

     ## Fetch records
     $this->db->select("a.*,b.*,
            a.product_name,
            a.product_id,
            a.product_model,
            b.batch_id
            ");
     $this->db->from('product_information a');
     $this->db->join('product_purchase_details b','a.product_id = b.product_id','left');
     if($searchValue != '')
     $this->db->where($searchQuery);
     $this->db->order_by($columnName, $columnSortOrder);
     $this->db->group_by('b.batch_id');
     $this->db->limit($rowperpage, $start);
     $records = $this->db->get()->result();
    //  var_dump($records);
    //  die();
     $data = array();
     $sl =1;
     foreach($records as $record)
     {
         
      $stockin = $this->db->select('sum(quantity) as totalSalesQnty')->from('invoice_details')
      ->where('product_id',$record->product_id)
     ->group_by('batch_id')
     ->get()->row();
    
     
      $stockout = $this->db->select('sum(a.quantity) as totalPurchaseQnty,b.supplier_price as purchaseprice')->from('product_purchase_details a')
      ->join('supplier_product b','a.product_id = b.product_id','left')
      ->where('a.product_id',$record->product_id)
      ->group_by('a.batch_id')
      ->get()->row();
      
    //   $stockin = $this->db->select('sum(quantity) as totalSalesQnty')->from('invoice_details')
    //   ->where('product_id',$record->product_id)
    //  ->group_by('product_id')
    //  ->get()->row();
     
    //   $stockout = $this->db->select('sum(quantity) as totalPurchaseQnty,Avg(rate) as purchaseprice')->from('product_purchase_details')
    //   ->where('product_id',$record->product_id)
    //   ->group_by('product_id')
    //   ->get()->row();

     
        $sprice = (!empty($record->rate)?$record->rate:0);
        $pprice = (!empty($stockout->purchaseprice)?sprintf('%0.2f',$stockout->purchaseprice):0); 
        $stock =  (!empty($stockout->totalPurchaseQnty)?$stockout->totalPurchaseQnty:0)-(!empty($stockin->totalSalesQnty)?$stockin->totalSalesQnty:0);
        $data[] = array( 
            'sl'            =>   $sl,
            'product_name'  =>  $record->product_name,
            'product_model' =>  $record->product_model,
            // 'batch' =>$record->batch_id,
            'sales_price'   =>  sprintf('%0.2f',$sprice),
            'purchase_p'    =>  $pprice,
            'totalPurchaseQnty'=>$stockout->totalPurchaseQnty,
            'totalSalesQnty'=>  $stockin->totalSalesQnty,
            'stok_quantity' => sprintf('%0.2f',$stock),
            
            'total_sale_price'=> ($stockout->totalPurchaseQnty-$stockin->totalSalesQnty)*$sprice,
            'purchase_total' =>  ($stockout->totalPurchaseQnty-$stockin->totalSalesQnty)*$pprice,
        ); 
        $sl++;
     }

     ## Response
     $response = array(
        "draw" => intval($draw),
        "iTotalRecords" => $totalRecordwithFilter,
        "iTotalDisplayRecords" => $totalRecords,
        "aaData" => $data
     );

     return $response; 
}

Report After Update

In the above image The In,Out, stock sale price, and stock purchase price are in minus which is not correct

Here is my DB Fiddle

Any help would be highly appreciated