Not getting unpaid or partially paid records some can help me?

Question

$draw = intval($request->draw);
$start = intval($request->start);
$length = intval($request->length);
$order = $request->order;
$search = $request->search;
$search = $search['value'];
$col = 0;
$dir = "";
if (!empty($order)) {
  foreach ($order as $o) {
    $col = $o['column'];
    $dir = $o['dir'];
  }
}

if ($dir != "asc" && $dir != "desc") {
  $dir = "desc";
}
$valid_columns = array(
  0 => '',
  //   1=>'vendor',
  1 => 'confirmed_at',
  2 => 'invoice',
  3 => 'client_name',
  4 => 'total_amount',
  5 => '',
  6 => '',
  7 => '',
  8 => '',

);
if (!isset($valid_columns[$col])) {
  $order = null;
} else {
  $order = $valid_columns[$col];
}
if($os_only == 1)
{
    $sales = Sale::selectRaw('sales.*, sum(sales_payments.payment_amount) as paid')
        ->join('sales_payments', 'sales.invoice', '=', 'sales_payments.invoice')
        ->where('invoice_status', 1)
        ->groupBy('sales.invoice');
        ->havingRaw('sales.total_amount < paid');
}
else
{
      $sales = Sale::select('sales.*', 'clients.client_name')
    ->where('invoice_status', 1)
    ->join('clients', 'sales.client_id', '=', 'clients.uid');
}

if (!empty($search)) {
  $x = 0;

  foreach ($valid_columns as $sterm) {
    if (!empty($sterm)) {

      if ($x == 0) {
        // $this->db->like($sterm,$search);
        $sales->where($sterm, 'LIKE', '%' . $search . '%')->where('invoice_status', 1);
      } else {
        // $this->db->or_like($sterm,$search);
        $sales->orWhere($sterm, 'LIKE', '%' . $search . '%')->where('invoice_status', 1);
      }
      $x++;
    }
  }
}
if ($order != null) {
  $sales->orderBy($order, $dir);
} else {
  $sales->orderBy('uid', 'DESC');
}

  $query = $sales->offset($start)->limit($length)->get();

i want to get unpaid or partially paid records in $query i dont want fully paid records if $os_only = 1 Note:the payment records are multiple or single in sales_payments or if there is no payment with invoice there is no records in sales_payment along with this invoice. how can i achive from this i am not getting results can some one help what i am doing wrong ?

Answer 1

Try modifying the query:

if($os_only == 1)
{
   $sales = Sale::selectRaw('sales.*, IFNULL(SUM(sales_payments.payment_amount), 0) as paid')
    ->leftJoin('sales_payments', 'sales.invoice', '=', 'sales_payments.invoice')
    ->where('invoice_status', 1)
    ->groupBy('sales.invoice')
    ->havingRaw('sales.total_amount > paid');
}
else
{
    $sales = Sale::select('sales.*', 'clients.client_name')
    ->where('invoice_status', 1)
    ->join('clients', 'sales.client_id', '=', 'clients.uid');
}

Explanation:

• IFNULL(SUM(sales_payments.payment_amount), 0) ensures that if there are no payments in the sales_payments, the paid amount will be considered as 0.
• leftJoin is used so that invoices with no payments also appear in the query results.
• havingRaw('sales.total_amount > paid') is used to filter invoice records that are either unpaid or partially paid.