public function update(Request $request, $id)
{
$validated = $request->validate([
'cohort_id' => ['required', 'integer'],
'learners' => ['nullable', 'integer'],
'status' => ['nullable', 'string'],
'invoice_status' => ['nullable', 'string'],
'discount' => ['nullable', 'numeric', 'min:0'],
'cost' => ['nullable', 'numeric', 'min:0'],
'cost_price' => ['nullable', 'numeric', 'min:0'],
'payment_status' => ['nullable', 'string'],
]);
DB::beginTransaction();
try {
$send = false;
$learner = null;
if (!empty($validated['learners']) && $request->filled('status')) {
$learner = User::find($validated['learners']);
if ($learner) {
$send = $learner->learner_status !== $validated['status'];
$learner->learner_status = $validated['status'];
$learner->save();
}
}
$costing = FrontOrderDetails::with('course')
->where('id', $id)
->where('cohort_id', $validated['cohort_id'])
->firstOrFail();
$qty = max(1, (int)($costing->quantity ?: 1));
$unitNet = $request->filled('cost') ? (float)$validated['cost'] : (float)($costing->course_price ?? 0.0);
$discPerUnit = min(max(0.0, (float)($validated['discount'] ?? 0.0)), $unitNet);
$unitNetAfterDisc = $unitNet - $discPerUnit;
$vatRate = 20.0;
$lineNet = round($unitNetAfterDisc * $qty, 2);
$lineVat = round($lineNet * ($vatRate / 100), 2);
$lineGross = round($lineNet + $lineVat, 2);
$depositPaid = (float)($costing->deposit_paid ?? 0.0);
$remaining = round(max(0.0, $lineGross - $depositPaid), 2);
if ($request->filled('cost')) {
$costing->course_price = $unitNet;
}
if ($request->filled('cost_price')) {
$costing->cost_price = (float)$validated['cost_price'];
}
$costing->discount = $discPerUnit;
$costing->total_price = $lineNet;
$costing->vat = $lineVat;
$costing->remaining_balance = $remaining;
if ($request->filled('invoice_status')) {
$costing->status = $validated['invoice_status'];
}
$costing->save();
$orderId = $costing->order_id;
$sumNet = (float) FrontOrderDetails::where('order_id', $orderId)->sum('total_price');
$sumVat = (float) FrontOrderDetails::where('order_id', $orderId)->sum('vat');
$orderGross = round($sumNet + $sumVat, 2);
$order = FrontOrder::findOrFail($orderId);
$order->total_amount = round($sumNet, 2);
$order->tax_amount = round($sumVat, 2);
$paid = (float) FrontPayment::where('order_id', $orderId)
->whereIn('status', ['paid', 'success', 'succeeded'])
->sum('amount');
$order->remaining_balance = round(max(0.0, $orderGross - $paid), 2);
$order->save();
$invoice = ProductInvoice::where('order_detail_id', $costing->id)->first();
if ($invoice) {
$invoice->lines()->delete();
$invoice->lines()->create([
'qty' => $qty,
'product_description' => $costing->course_name ?? optional($costing->course)->name ?? 'Course',
'unit_cost' => $unitNet,
'vat_rate' => $vatRate,
'net_amount' => $lineNet,
'vat_amount' => $lineVat,
'gross_amount' => $lineGross,
]);
$sums = $invoice->lines()
->selectRaw('COALESCE(SUM(net_amount),0) as net, COALESCE(SUM(vat_amount),0) as vat, COALESCE(SUM(gross_amount),0) as gross')
->first();
$linesNet = (float)($sums->net ?? 0.0);
$linesVat = (float)($sums->vat ?? 0.0);
$totalNet = round($linesNet, 2);
$totalVat = round($linesVat, 2);
$totalGross = round($totalNet + $totalVat, 2);
$invoice->total_net = $totalNet;
$invoice->total_vat = $totalVat;
$invoice->total_gross = $totalGross;
if ($request->filled('payment_status')) {
$invoice->status = $validated['payment_status'];
}
$invoice->save();
}
if ($send && $learner) {
(new EmailSendingService())->fire('status', [
'email' => $learner->email,
'name' => $learner->name,
'status' => $validated['status'],
'course' => [
'title' => optional($costing->course)->name ?? '',
'description' => optional($costing->course)->description ?? '',
],
'mailable_type' => get_class($learner),
'mailable_id' => $learner->id,
]);
}
DB::commit();
return response()->json(['status' => 'success']);
} catch (\Throwable $e) {
DB::rollBack();
return response()->json([
'status' => 'error',
'message' => $e->getMessage(),
], 500);
}
}
public function learners($id, Request $request)
{
$ids = DB::table('cohort_user')->where('cohort_id', $id)->pluck('user_id');
$q = User::whereIn('id', $ids)->whereNull('deleted_at');
if ($request->filled('learner_status')) {
$q->where('learner_status', $request->learner_status);
}
if ($s = trim($request->get('search', ''))) {
$q->where(function ($qq) use ($s) {
$qq->where('name', 'like', "%$s%")
->orWhereRaw("CONCAT(name,' ',last_name) LIKE ?", ["%$s%"]);
});
}
$users = $q->orderByDesc('id')->get();
$rows = [];
foreach ($users as $u) {
$p = getFrontOrder($u->id, $id);
$od = $p ? FrontOrderDetails::where('order_id', $p->order_id)->where('cohort_id', $id)->first() : null;
$cost = round((float)($od->course_price ?? 0), 2);
$discount = round((float)($od->discount ?? 0), 2);
$net = round(max(0, $cost - $discount), 2);
$vat = round($net * 0.20, 2);
$rows[] = [
'id' => $u->id,
'date_added' => optional($u->created_at)->format('d-m-Y'),
'delegate_name' => trim(($u->name ?? '') . ' ' . ($u->last_name ?? '')) ?: ($u->name ?? '-'),
'customer' => ($u->email ?? '-'),
'status' => ($u->learner_status ?? '-'),
'cost' => $cost,
'discount' => $discount,
'net_cost' => $net,
'vat' => $vat,
'invoice_number' => $p->invoice_number ?? null,
'invoice_status' => $p->status ?? null,
'invoice_pdf_url' => $p?->invoice_pdf_url ? asset('storage/' . $p->invoice_pdf_url) : null,
'order_detail_id' => $od->id ?? null,
'payment_status' => $od->status ?? null,
];
}
return response()->json([
'items' => $rows,
'totals' => [
'sub_total' => round(array_sum(array_map(fn($r) => $r['cost'], $rows)), 2),
'discount' => round(array_sum(array_map(fn($r) => $r['discount'], $rows)), 2),
'total_cost' => round(array_sum(array_map(fn($r) => $r['net_cost'], $rows)), 2),
'vat' => round(array_sum(array_map(fn($r) => $r['vat'], $rows)), 2),
'count' => count($rows),
],
]);
}