Commit Review Workspace ?

- $designId  = null;
- 
- if ($caseId) {
-             $aonRows = DB::select("SELECT SP.Section, SP.PartNo, SP.Nomenclature, AU.AUName AS AU,
-                 SUM(LPS.Qty) AS Qty, LPS.ProposalPrice AS Rate, LPS.GST, LPS.OtherCharges,
-                 SUM(LPS.TotalAONAmount) AS Amount, GROUP_CONCAT(J.JobNo SEPARATOR ', ') AS JobNos
-                 FROM cases C INNER JOIN new_lprs L ON C.CaseID=L.CaseNoID
- INNER JOIN lprspares LPS ON L.LPRID=LPS.LPRID
-                 INNER JOIN spares SP ON LPS.SpareID=SP.SpareID
-                 INNER JOIN au AU ON SP.AUID=AU.AUID
-                 INNER JOIN jobs J ON LPS.JobID=J.JobID
-                 WHERE C.CaseID=? AND LPS.Qty>0
-                 GROUP BY SP.Section,SP.PartNo,SP.Nomenclature,AU.AUName,LPS.ProposalPrice,LPS.GST,LPS.OtherCharges
-                 ORDER BY SP.Section,SP.PartNo", [$caseId]);
- 
- $aonAmount = array_sum(array_map(fn($r) => (float)$r->Amount, $aonRows));

+ $designId  = null;
+ 
+ if ($caseId) {
+             $aonRows = DB::select("SELECT
+                 COALESCE(SP.Section,'') AS Section,
+                 COALESCE(SP.PartNo,'') AS PartNo,
+                 COALESCE(SP.Nomenclature,'') AS Nomenclature,
+                 COALESCE(AU.AUName,'') AS AU,
+                 SUM(COALESCE(LPS.Qty,0)) AS Qty,
+                 COALESCE(LPS.ProposalPrice,0) AS Rate,
+                 COALESCE(LPS.GST,0) AS GST,
+                 COALESCE(LPS.OtherCharges,0) AS OtherCharges,
+                 SUM(COALESCE(LPS.TotalAONAmount,0)) AS Amount,
+                 GROUP_CONCAT(DISTINCT J.JobNo ORDER BY J.JobNo SEPARATOR ', ') AS JobNos
+                 FROM cases C
+                 INNER JOIN new_lprs L ON C.CaseID=L.CaseNoID
+ INNER JOIN lprspares LPS ON L.LPRID=LPS.LPRID
+                 LEFT JOIN spares SP ON LPS.SpareID=SP.SpareID

- if (!empty($bidData->$col)) $bidData->$col = LpoHelper::fmtDate($bidData->$col);
- }
- }
-             $bidSpares = DB::select("SELECT SP.Section,SP.PartNo,SP.Nomenclature,AU.AUName AS AU,SUM(LPS.Qty) AS Qty,AVG(LPS.ProposalPrice) AS Rate,GROUP_CONCAT(J.JobNo SEPARATOR ', ') AS JobNos FROM cases C INNER JOIN new_lprs L ON C.CaseID=L.CaseNoID INNER JOIN lprspares LPS ON L.LPRID=LPS.LPRID INNER JOIN spares SP ON LPS.SpareID=SP.SpareID INNER JOIN au AU ON SP.AUID=AU.AUID INNER JOIN jobs J ON LPS.JobID=J.JobID WHERE C.CaseID=? GROUP BY SP.Section,SP.PartNo,SP.Nomenclature,AU.AUName ORDER BY SP.Section,SP.PartNo", [$caseId]);
- }
- 
- return view('bid.entry', compact('caseId','cases','bidData','bidSpares'));

+ if (!empty($bidData->$col)) $bidData->$col = LpoHelper::fmtDate($bidData->$col);
+ }
+ }
+             $bidSpares = DB::select("SELECT
+                 COALESCE(SP.Section,'') AS Section,
+                 COALESCE(SP.PartNo,'') AS PartNo,
+                 COALESCE(SP.Nomenclature,'') AS Nomenclature,
+                 COALESCE(AU.AUName,'') AS AU,
+                 SUM(COALESCE(LPS.Qty,0)) AS Qty,
+                 AVG(COALESCE(LPS.ProposalPrice,0)) AS Rate,
+                 GROUP_CONCAT(DISTINCT J.JobNo ORDER BY J.JobNo SEPARATOR ', ') AS JobNos
+                 FROM cases C
+                 INNER JOIN new_lprs L ON C.CaseID=L.CaseNoID
+                 INNER JOIN lprspares LPS ON L.LPRID=LPS.LPRID
+                 LEFT JOIN spares SP ON LPS.SpareID=SP.SpareID
+                 LEFT JOIN au AU ON SP.AUID=AU.AUID
+                 LEFT JOIN jobs J ON LPS.JobID=J.JobID
+                 WHERE C.CaseID=? AND COALESCE(LPS.Qty,0) > 0

- if ($caseId) {
- $hasSaved = DB::selectOne("SELECT COUNT(*) AS cnt FROM comparativestatements WHERE CaseID=?", [$caseId])->cnt;
- if ($hasSaved) {
-                 $csData = DB::select("SELECT ls.LPRSpareID,c.CaseID,COALESCE(s.Section,'') AS Section,COALESCE(s.PartNo,'') AS PartNo,COALESCE(s.Nomenclature,'') AS Nomenclature,COALESCE(s.AUID,'EA') AS AU,ls.Qty,COALESCE(cs.Vendor1Name,'') AS Vendor1Name,COALESCE(cs.Vendor2Name,'') AS Vendor2Name,COALESCE(cs.Vendor3Name,'') AS Vendor3Name,COALESCE(cs.Vendor1Rate,0) AS V1Rate,COALESCE(cs.Vendor1GST,0) AS V1GST,COALESCE(cs.Vendor2Rate,0) AS V2Rate,COALESCE(cs.Vendor2GST,0) AS V2GST,COALESCE(cs.Vendor3Rate,0) AS V3Rate,COALESCE(cs.Vendor3GST,0) AS V3GST,COALESCE(cs.LPP,0) AS LPP,COALESCE(cs.LPPGST,0) AS LPPGST,COALESCE(cs.L1Vendor,'') AS L1Vendor,COALESCE(cs.L1Rate,0) AS L1Rate FROM lprspares ls INNER JOIN new_lprs l ON ls.LPRID=l.LPRID INNER JOIN cases c ON l.CaseNoID=c.CaseID INNER JOIN comparativestatements cs ON cs.LPRSpareID=ls.LPRSpareID AND cs.CaseID=c.CaseID LEFT JOIN spares s ON ls.SpareID=s.SpareID WHERE c.CaseID=? AND ls.Qty>0 ORDER BY COALESCE(s.Section,'ZZZ'),COALESCE(s.PartNo,'ZZZ')", [$caseId]);
- $v1Name = $csData[0]->Vendor1Name ?? '';
- $v2Name = $csData[0]->Vendor2Name ?? '';
- $v3Name = $csData[0]->Vendor3Name ?? '';
- } else {
-                 $csData = DB::select("SELECT ls.LPRSpareID,c.CaseID,COALESCE(s.Section,'') AS Section,COALESCE(s.PartNo,'') AS PartNo,COALESCE(s.Nomenclature,'') AS Nomenclature,COALESCE(s.AUID,'EA') AS AU,ls.Qty FROM lprspares ls INNER JOIN new_lprs l ON ls.LPRID=l.LPRID INNER JOIN cases c ON l.CaseNoID=c.CaseID LEFT JOIN spares s ON ls.SpareID=s.SpareID WHERE c.CaseID=? ORDER BY COALESCE(s.Section,'ZZZ'),COALESCE(s.PartNo,'ZZZ')", [$caseId]);
- foreach ($csData as $r) {
- $r->V1Rate = $r->V1GST = $r->V2Rate = $r->V2GST = $r->V3Rate = $r->V3GST = $r->LPP = $r->LPPGST = $r->L1Rate = 0;
- $r->L1Vendor = '';

+ if ($caseId) {
+ $hasSaved = DB::selectOne("SELECT COUNT(*) AS cnt FROM comparativestatements WHERE CaseID=?", [$caseId])->cnt;
+ if ($hasSaved) {
+                 $csData = DB::select("SELECT ls.LPRSpareID,c.CaseID,COALESCE(s.Section,'') AS Section,COALESCE(s.PartNo,'') AS PartNo,COALESCE(s.Nomenclature,'') AS Nomenclature,COALESCE(a.AUName,'EA') AS AU,ls.Qty,COALESCE(cs.Vendor1Name,'') AS Vendor1Name,COALESCE(cs.Vendor2Name,'') AS Vendor2Name,COALESCE(cs.Vendor3Name,'') AS Vendor3Name,COALESCE(cs.Vendor1Rate,0) AS V1Rate,COALESCE(cs.Vendor1GST,0) AS V1GST,COALESCE(cs.Vendor2Rate,0) AS V2Rate,COALESCE(cs.Vendor2GST,0) AS V2GST,COALESCE(cs.Vendor3Rate,0) AS V3Rate,COALESCE(cs.Vendor3GST,0) AS V3GST,COALESCE(cs.LPP,0) AS LPP,COALESCE(cs.LPPGST,0) AS LPPGST,COALESCE(cs.L1Vendor,'') AS L1Vendor,COALESCE(cs.L1Rate,0) AS L1Rate FROM lprspares ls INNER JOIN new_lprs l ON ls.LPRID=l.LPRID INNER JOIN cases c ON l.CaseNoID=c.CaseID INNER JOIN comparativestatements cs ON cs.LPRSpareID=ls.LPRSpareID AND cs.CaseID=c.CaseID LEFT JOIN spares s ON ls.SpareID=s.SpareID LEFT JOIN au a ON s.AUID=a.AUID WHERE c.CaseID=? AND COALESCE(ls.Qty,0)>0 ORDER BY COALESCE(s.Section,'ZZZ'),COALESCE(s.PartNo,'ZZZ')", [$caseId]);
+ $v1Name = $csData[0]->Vendor1Name ?? '';
+ $v2Name = $csData[0]->Vendor2Name ?? '';
+ $v3Name = $csData[0]->Vendor3Name ?? '';
+ } else {
+                 $csData = DB::select("SELECT ls.LPRSpareID,c.CaseID,COALESCE(s.Section,'') AS Section,COALESCE(s.PartNo,'') AS PartNo,COALESCE(s.Nomenclature,'') AS Nomenclature,COALESCE(a.AUName,'EA') AS AU,ls.Qty FROM lprspares ls INNER JOIN new_lprs l ON ls.LPRID=l.LPRID INNER JOIN cases c ON l.CaseNoID=c.CaseID LEFT JOIN spares s ON ls.SpareID=s.SpareID LEFT JOIN au a ON s.AUID=a.AUID WHERE c.CaseID=? AND COALESCE(ls.Qty,0)>0 ORDER BY COALESCE(s.Section,'ZZZ'),COALESCE(s.PartNo,'ZZZ')", [$caseId]);
+ foreach ($csData as $r) {
+ $r->V1Rate = $r->V1GST = $r->V2Rate = $r->V2GST = $r->V3Rate = $r->V3GST = $r->LPP = $r->LPPGST = $r->L1Rate = 0;
+ $r->L1Vendor = '';

- FROM cases c
- INNER JOIN new_lprs l    ON c.CaseID  = l.CaseNoID
- INNER JOIN lprspares ls ON l.LPRID = ls.LPRID
-              INNER JOIN spares s  ON ls.SpareID = s.SpareID
- LEFT  JOIN au a      ON s.AUID     = a.AUID
- WHERE c.CaseID = ? AND ls.Qty > 0
-              ORDER BY s.Section, s.PartNo",
- [$caseId]
- );

+ FROM cases c
+ INNER JOIN new_lprs l    ON c.CaseID  = l.CaseNoID
+ INNER JOIN lprspares ls ON l.LPRID = ls.LPRID
+              LEFT  JOIN spares s  ON ls.SpareID = s.SpareID
+ LEFT  JOIN au a      ON s.AUID     = a.AUID
+ WHERE c.CaseID = ? AND ls.Qty > 0
+              ORDER BY COALESCE(s.Section,''), COALESCE(s.PartNo,'')",
+ [$caseId]
+ );

- if (!empty($case->$col)) $case->$col = LpoHelper::fmtDate($case->$col);
- }
- 
-         $grid = DB::select("SELECT ls.LPRSpareID, COALESCE(s.Section,'') AS Section, COALESCE(s.PartNo,'') AS PartNo, COALESCE(s.Nomenclature,'') AS Nomenclature, COALESCE(a.AUName,'') AS AUID, COALESCE(ls.Qty,0) AS Qty, COALESCE(ls.TotalAONAmount,0) AS TotalAONAmount, COALESCE(ls.BidAmount,0) AS BidAmount, COALESCE(ls.DiscountPrice,0) AS DiscountPrice, CASE WHEN COALESCE(ls.DiscountPrice,0)=0 THEN COALESCE(ls.BidAmount,0) ELSE COALESCE(ls.BidAmount,0)-COALESCE(ls.DiscountPrice,0) END AS PriceAfterPNC FROM cases c INNER JOIN new_lprs l ON c.CaseID=l.CaseNoID INNER JOIN lprspares ls ON l.LPRID=ls.LPRID INNER JOIN spares s ON ls.SpareID=s.SpareID LEFT JOIN au a ON s.AUID=a.AUID WHERE c.CaseID=? AND ls.Qty>0 ORDER BY s.Section,s.PartNo", [$caseId]);
- 
- return response()->json(['success' => true, 'case' => $case, 'grid' => $grid]);
- }

+ if (!empty($case->$col)) $case->$col = LpoHelper::fmtDate($case->$col);
+ }
+ 
+         $grid = DB::select("SELECT ls.LPRSpareID, COALESCE(s.Section,'') AS Section, COALESCE(s.PartNo,'') AS PartNo, COALESCE(s.Nomenclature,'') AS Nomenclature, COALESCE(a.AUName,'') AS AUID, COALESCE(ls.Qty,0) AS Qty, COALESCE(ls.TotalAONAmount,0) AS TotalAONAmount, COALESCE(ls.BidAmount,0) AS BidAmount, COALESCE(ls.DiscountPrice,0) AS DiscountPrice, CASE WHEN COALESCE(ls.DiscountPrice,0)=0 THEN COALESCE(ls.BidAmount,0) ELSE COALESCE(ls.BidAmount,0)-COALESCE(ls.DiscountPrice,0) END AS PriceAfterPNC FROM cases c INNER JOIN new_lprs l ON c.CaseID=l.CaseNoID INNER JOIN lprspares ls ON l.LPRID=ls.LPRID LEFT JOIN spares s ON ls.SpareID=s.SpareID LEFT JOIN au a ON s.AUID=a.AUID WHERE c.CaseID=? AND COALESCE(ls.Qty,0)>0 ORDER BY COALESCE(s.Section,''),COALESCE(s.PartNo,'')", [$caseId]);
+ 
+ return response()->json(['success' => true, 'case' => $case, 'grid' => $grid]);
+ }

- $complaintData['equipment_items'] = $complaint->equipment_items;
- $complaintData['total_equipment_cost'] = $complaint->total_equipment_cost;
- $complaintData['total_equipment_items'] = $complaint->total_equipment_items;
- 
- // Add resolver details
- $complaintData['resolver_status'] = $complaint->resolver_status;
- 
- // Get the complaints with pagination
- $complaints = $complaintsQuery->orderBy('created_at', 'desc')->paginate(10);
- 
- // Get all users grouped by group_id for assignment dropdowns
- $groupUsers = [];
- $groupUsers = []; // Empty collection on error
- }
- 
-         return view('complaint-assignment.index', compact('complaints', 'groupUsers','groups'));
- }
- 

+ $complaintData['equipment_items'] = $complaint->equipment_items;
+ $complaintData['total_equipment_cost'] = $complaint->total_equipment_cost;
+ $complaintData['total_equipment_items'] = $complaint->total_equipment_items;
+             $complaintData['assigned_to_name'] = $complaint->assigned_to_display;
+             $complaintData['resolver_name'] = $complaint->resolver_name_display;
+ 
+ // Add resolver details
+ $complaintData['resolver_status'] = $complaint->resolver_status;
+ 
+ // Get the complaints with pagination
+ $complaints = $complaintsQuery->orderBy('created_at', 'desc')->paginate(10);
+         $assignedNameOptions = Complaint::query()
+             ->whereNotNull('assigned_to_name')
+             ->where('assigned_to_name', '!=', '')
+             ->distinct()
+             ->orderBy('assigned_to_name')
+             ->pluck('assigned_to_name');
+ 

- });
- }
- 
- /*
- |--------------------------------------------------------------------------
- | EXPORTS

+ });
+ }
+ 
+                 $query->orderBy(
+                     WorkOrder::select('job_date')
+                         ->whereColumn('work_orders.id', 'work_equipment.work_order_id')
+                         ->limit(1),
+                     'asc'
+                 )
+                 ->orderBy(
+                     WorkOrder::select('job_no')
+                         ->whereColumn('work_orders.id', 'work_equipment.work_order_id')
+                         ->limit(1),
+                     'asc'
+                 )
+                 ->orderBy('work_equipment.id', 'asc');
+ 
+ /*

- $powerassignedAmounts[$fund->id] = $fund->pivot->power_in_consultation_with_isa; // assuming pivot table
- }
- 
-     $allFunds = LpoFund::all(); // or whichever funds should appear in modal
- 
- // IDs of already assigned funds
- $assigned = $fp->funds->pluck('id')->toArray();
- {
- $fp = FinancialPower::findOrFail($id);
- $funds = $request->input('funds', []);
-     $inheritPower = $request->input('inherit_power'); // still used for validation
- 
- $syncData = [];
- 
- foreach ($funds as $fundId => $data) {
-         if (isset($data['amount'])) {
-             $amount = floatval($data['amount']);
-             $powerInConsultation = isset($data['power_in_consultation_with_isa'])

+ $powerassignedAmounts[$fund->id] = $fund->pivot->power_in_consultation_with_isa; // assuming pivot table
+ }
+ 
+     $allFunds = LpoFund::orderBy('name_of_fund')->get();
+ 
+ // IDs of already assigned funds
+ $assigned = $fp->funds->pluck('id')->toArray();
+ {
+ $fp = FinancialPower::findOrFail($id);
+ $funds = $request->input('funds', []);
+     $inheritPower = is_numeric($fp->inherit_power) ? (float) $fp->inherit_power : null;
+     $consultationPower = is_numeric($fp->power_in_consultation_with_isa) ? (float) $fp->power_in_consultation_with_isa : null;
+ 
+ $syncData = [];
+ 
+ foreach ($funds as $fundId => $data) {
+         $rawAmount = $data['amount'] ?? null;
+         $rawConsultation = $data['power_in_consultation_with_isa'] ?? null;

- {
- $validated = $request->validate([
- 'lpo_fund_id' => 'required|exists:lpo_funds,id',
-             'total_allotment' => 'nullable|string',
-             'total_expenditure' => 'nullable|string',
-             'bills_fwd_to_cda' => 'nullable|string',
-             'bills_passed_by_cda' => 'nullable|string',
- ]);
- 
- LpoFundStatus::updateOrCreate(
- {
- $request->validate([
- 'name_of_fund' => 'required|string|max:255',
-             'code_head' => 'nullable|string|max:255',
- 'amount_allotted' => 'required|numeric|min:0',
- 'financial_year' => 'required|string',
-             'authority' => 'nullable|string|max:255',
- 'date' => 'nullable|date',

+ {
+ $validated = $request->validate([
+ 'lpo_fund_id' => 'required|exists:lpo_funds,id',
+             'total_allotment' => 'nullable|numeric|min:0',
+             'total_expenditure' => 'nullable|numeric|min:0',
+             'bills_fwd_to_cda' => 'nullable|numeric|min:0',
+             'bills_passed_by_cda' => 'nullable|numeric|min:0',
+ ]);
+ 
+ LpoFundStatus::updateOrCreate(
+ {
+ $request->validate([
+ 'name_of_fund' => 'required|string|max:255',
+             'code_head' => 'required|string|max:255',
+ 'amount_allotted' => 'required|numeric|min:0',
+ 'financial_year' => 'required|string',
+             'authority' => 'required|string|max:255',
+ 'date' => 'nullable|date',

- /** PATCH /lpr/edit/{lprId} – update LPR master */
- public function updateMaster(Request $request, int $lprId)
- {
- try {
- DB::statement("UPDATE new_lprs SET UrgencyNo=?,UrgencyDate=?,LPRDate=?,NACNo=?,NACDate=?,NACValidity=? WHERE LPRID=?", [
-                 $request->UrgencyNo ?: null,
- $request->UrgencyDate ?: null,
- $request->LPRDate ?: null,
-                 $request->NACNo ?: null,
- $request->NACDate ?: null,
- $request->NACValidity ?: null,
- $lprId,

+ /** PATCH /lpr/edit/{lprId} – update LPR master */
+ public function updateMaster(Request $request, int $lprId)
+ {
+         $request->validate([
+             'UrgencyNo'   => 'nullable|string|max:50',
+             'UrgencyDate' => 'nullable|date',
+             'LPRDate'     => 'nullable|date',
+             'NACNo'       => 'nullable|string|max:15',
+             'NACDate'     => 'nullable|date',
+             'NACValidity' => 'nullable|date',
+         ]);
+ 
+ try {
+ DB::statement("UPDATE new_lprs SET UrgencyNo=?,UrgencyDate=?,LPRDate=?,NACNo=?,NACDate=?,NACValidity=? WHERE LPRID=?", [
+                 filled($request->UrgencyNo) ? trim($request->UrgencyNo) : null,
+ $request->UrgencyDate ?: null,
+ $request->LPRDate ?: null,
+                 filled($request->NACNo) ? trim($request->NACNo) : null,

- {
- $request->validate([
- 'UserGroupID'  => 'required|integer|min:1',
-             'UrgencyNo'    => 'required|string|max:100',
- 'UrgencyDate'  => 'required|date',
-             'LPRNo'        => 'required|string|max:100',
- 'LPRDate'      => 'required|date',
- 'GrantID'      => 'required|integer|min:1',
- ]);
- 
- try {
- $lprId = DB::transaction(function () use ($request) {
- DB::statement("INSERT INTO new_lprs (UserGroupID,UrgencyNo,UrgencyDate,LPRNo,LPRDate,GrantID,NACNo,NACDate,NACValidity) VALUES (?,?,?,?,?,?,?,?,?)", [
- $request->UserGroupID,
-                     $request->UrgencyNo,
- $request->UrgencyDate,
-                     $request->LPRNo,
- $request->LPRDate,

+ {
+ $request->validate([
+ 'UserGroupID'  => 'required|integer|min:1',
+             'UrgencyNo'    => 'required|string|max:50',
+ 'UrgencyDate'  => 'required|date',
+             'LPRNo'        => 'required|string|max:10',
+ 'LPRDate'      => 'required|date',
+ 'GrantID'      => 'required|integer|min:1',
+             'NACNo'        => 'nullable|string|max:15',
+             'NACDate'      => 'nullable|date',
+             'NACValidity'  => 'nullable|date',
+ ]);
+ 
+ try {
+ $lprId = DB::transaction(function () use ($request) {
+ DB::statement("INSERT INTO new_lprs (UserGroupID,UrgencyNo,UrgencyDate,LPRNo,LPRDate,GrantID,NACNo,NACDate,NACValidity) VALUES (?,?,?,?,?,?,?,?,?)", [
+ $request->UserGroupID,
+                     trim($request->UrgencyNo),

- /** GET /lpr/pending/{lprId}/spares */
- public function spares(int $lprId)
- {
-         $spares = DB::select("SELECT LS.LPRSpareID, LS.SpareID, S.Nomenclature, S.PartNo, LS.Qty, J.JobNo,
- COALESCE(LS.ProposalPrice,0) AS ProposalPrice, COALESCE(LS.GST,0) AS GST,
- COALESCE(LS.OtherCharges,0) AS OtherCharges, COALESCE(LS.TotalAONAmount,0) AS TotalAONAmount
-             FROM lprspares LS INNER JOIN spares S ON LS.SpareID=S.SpareID
- LEFT JOIN jobs J ON LS.JobID=J.JobID WHERE LS.LPRID=? ORDER BY LS.LPRSpareID", [$lprId]);
- return response()->json($spares);
- }

+ /** GET /lpr/pending/{lprId}/spares */
+ public function spares(int $lprId)
+ {
+         $spares = DB::select("SELECT LS.LPRSpareID, LS.SpareID,
+             COALESCE(S.Nomenclature,'') AS Nomenclature,
+             COALESCE(S.PartNo,'') AS PartNo,
+             LS.Qty, J.JobNo,
+ COALESCE(LS.ProposalPrice,0) AS ProposalPrice, COALESCE(LS.GST,0) AS GST,
+ COALESCE(LS.OtherCharges,0) AS OtherCharges, COALESCE(LS.TotalAONAmount,0) AS TotalAONAmount
+             FROM lprspares LS LEFT JOIN spares S ON LS.SpareID=S.SpareID
+ LEFT JOIN jobs J ON LS.JobID=J.JobID WHERE LS.LPRID=? ORDER BY LS.LPRSpareID", [$lprId]);
+ return response()->json($spares);
+ }

- 
- ->select(
- 'work_orders.job_no',
- 'mco_demands.prod_year',
- 'equipments.name as equipment_name',
- 'repair_classes.name as repair_class_name',
- // ✅ GROUPING
- $reports = $query->groupBy(
- 'work_orders.job_no',
- 'mco_demands.prod_year',
- 'equipments.name',
- 'repair_classes.name'
- )
-         ->orderByDesc('work_orders.job_no')
- ->get();
- 
- return view('mco_reports.index', compact(
- ->whereDate('work_orders.job_date', $jobDate);

+ 
+ ->select(
+ 'work_orders.job_no',
+             'work_orders.job_date',
+ 'mco_demands.prod_year',
+ 'equipments.name as equipment_name',
+ 'repair_classes.name as repair_class_name',
+ // ✅ GROUPING
+ $reports = $query->groupBy(
+ 'work_orders.job_no',
+             'work_orders.job_date',
+ 'mco_demands.prod_year',
+ 'equipments.name',
+ 'repair_classes.name'
+ )
+         ->orderBy('work_orders.job_date', 'asc')
+         ->orderBy('work_orders.job_no', 'asc')
+ ->get();

- 
- private function caseSpares(int $caseId): array
- {
-         return DB::select("SELECT SP.Section,SP.PartNo,SP.Nomenclature,AU.AUName AS AU,SUM(LPS.Qty) AS Qty,LPS.ProposalPrice AS Rate,LPS.GST,LPS.OtherCharges,SUM(LPS.TotalAONAmount) AS Amount,GROUP_CONCAT(J.JobNo SEPARATOR ', ') AS JobNos FROM cases C INNER JOIN new_lprs L ON C.CaseID=L.CaseNoID INNER JOIN lprspares LPS ON L.LPRID=LPS.LPRID INNER JOIN spares SP ON LPS.SpareID=SP.SpareID INNER JOIN au AU ON SP.AUID=AU.AUID INNER JOIN jobs J ON LPS.JobID=J.JobID WHERE C.CaseID=? AND LPS.Qty>0 GROUP BY SP.Section,SP.PartNo,SP.Nomenclature,AU.AUName,LPS.ProposalPrice,LPS.GST,LPS.OtherCharges ORDER BY SP.Section,SP.PartNo", [$caseId]);
- }
- 
- private function easSpares(int $caseId): array
- {
-         return DB::select("SELECT SP.Section,SP.PartNo,SP.Nomenclature,AU.AUName AS AU,LPS.Qty,LPS.OrderPrice,LPS.FinalGST,LPS.TotalOrderAmount,V.VendorName FROM cases C INNER JOIN new_lprs L ON C.CaseID=L.CaseNoID INNER JOIN lprspares LPS ON L.LPRID=LPS.LPRID INNER JOIN spares SP ON LPS.SpareID=SP.SpareID INNER JOIN au AU ON SP.AUID=AU.AUID LEFT JOIN vendors V ON LPS.L1VendorID=V.VendorID WHERE C.CaseID=? AND LPS.Qty>0 ORDER BY SP.Section,SP.PartNo", [$caseId]);
- }
- 
- // ── AON prints ─────────────────────────────────────────────────────────
- public function comparativeStatement(int $caseId)
- {
- $case   = $this->caseData($caseId);
-         $csRows = DB::select("SELECT SP.Section,SP.PartNo,SP.Nomenclature,AU.AUName AS AU,LPS.Qty,cs.Vendor1Name,cs.Vendor1Rate,cs.Vendor1GST,cs.Vendor2Name,cs.Vendor2Rate,cs.Vendor2GST,cs.Vendor3Name,cs.Vendor3Rate,cs.Vendor3GST,cs.LPP,cs.LPPGST,cs.L1Vendor,cs.L1Rate FROM comparativestatements cs INNER JOIN lprspares LPS ON cs.LPRSpareID=LPS.LPRSpareID INNER JOIN spares SP ON LPS.SpareID=SP.SpareID INNER JOIN au AU ON SP.AUID=AU.AUID WHERE cs.CaseID=? ORDER BY SP.Section,SP.PartNo", [$caseId]);
- return view('boo.print.comparative-statement', compact('case','csRows'));
- }

+ 
+ private function caseSpares(int $caseId): array
+ {
+         return DB::select("SELECT
+                 COALESCE(SP.Section,'') AS Section,
+                 COALESCE(SP.PartNo,'') AS PartNo,
+                 COALESCE(SP.Nomenclature,'') AS Nomenclature,
+                 COALESCE(AU.AUName,'') AS AU,
+                 SUM(COALESCE(LPS.Qty,0)) AS Qty,
+                 COALESCE(LPS.ProposalPrice,0) AS Rate,
+                 COALESCE(LPS.GST,0) AS GST,
+                 COALESCE(LPS.OtherCharges,0) AS OtherCharges,
+                 SUM(COALESCE(LPS.TotalAONAmount,0)) AS Amount,
+                 GROUP_CONCAT(DISTINCT J.JobNo ORDER BY J.JobNo SEPARATOR ', ') AS JobNos
+             FROM cases C
+             INNER JOIN new_lprs L ON C.CaseID=L.CaseNoID
+             INNER JOIN lprspares LPS ON L.LPRID=LPS.LPRID
+             LEFT JOIN spares SP ON LPS.SpareID=SP.SpareID

- \App\Http\Middleware\VerifyCsrfToken::class,
- \Illuminate\Routing\Middleware\SubstituteBindings::class,
- \App\Http\Middleware\DailyLoginLogMiddleware::class,
- ],
- 
- 'api' => [

+ \App\Http\Middleware\VerifyCsrfToken::class,
+ \Illuminate\Routing\Middleware\SubstituteBindings::class,
+ \App\Http\Middleware\DailyLoginLogMiddleware::class,
+             \App\Http\Middleware\UserActivityLogMiddleware::class,
+ ],
+ 
+ 'api' => [

+ <?php
+ 
+ namespace App\Http\Middleware;
+ 
+ use App\Models\Role;
+ use App\Models\UserActivityLog;
+ use Closure;
+ use Illuminate\Support\Facades\Auth;
+ use Illuminate\Support\Facades\Schema;
+ use Illuminate\Support\Str;
+ 
+ class UserActivityLogMiddleware
+ {
+     public function handle($request, Closure $next)
+     {
+         $response = $next($request);
+ 
+         if ($this->shouldLog($request)) {

- 'resolver_status',
- 'assigned_by',
- 'assigned_to',
- 'resolved_by',
- 'resolved_at',
- 'equipment_details',
- 'complaint_number',
- return $this->equipment_details['total_items'] ?? 0;
- }
- 
- public static function getNatureOfComplaintOptions(): array
- {
- return [

+ 'resolver_status',
+ 'assigned_by',
+ 'assigned_to',
+         'assigned_to_name',
+ 'resolved_by',
+         'resolver_name',
+ 'resolved_at',
+ 'equipment_details',
+ 'complaint_number',
+ return $this->equipment_details['total_items'] ?? 0;
+ }
+ 
+     public function getAssignedToDisplayAttribute(): string
+     {
+         return $this->assigned_to_name ?: ($this->assignedTo?->name ?? '');
+     }
+ 
+     public function getResolverNameDisplayAttribute(): string

+ <?php
+ 
+ namespace App\Models;
+ 
+ use Illuminate\Database\Eloquent\Model;
+ 
+ class UserActivityLog extends Model
+ {
+     protected $table = 'user_activity_logs';
+ 
+     protected $fillable = [
+         'group_name',
+         'username',
+         'user_id',
+         'group_id',
+         'role_name',
+         'role_id',
+         'action',

- 
- 'attendance_sqlsrv' => [
- 'driver' => 'sqlsrv',
-             'host' => env('ATTENDANCE_SQLSRV_HOST', 'WIN-A9HI59EFOOC'),
-             'port' => env('ATTENDANCE_SQLSRV_PORT', '1433'),
-             'database' => env('ATTENDANCE_SQLSRV_DATABASE', 'Realtime1'),
-             'username' => env('ATTENDANCE_SQLSRV_USERNAME', 'sa'),
-             'password' => env('ATTENDANCE_SQLSRV_PASSWORD', ''),
- 'charset' => 'utf8',
- 'prefix' => '',
- 'prefix_indexes' => true,

+ 
+ 'attendance_sqlsrv' => [
+ 'driver' => 'sqlsrv',
+             'host' => 'WIN-A9HI59EFOOC',
+             'port' => '1433',
+             'database' => 'Realtime1',
+             'username' => 'sa',
+             'password' => 'abc@123',
+ 'charset' => 'utf8',
+ 'prefix' => '',
+ 'prefix_indexes' => true,

+ <?php
+ 
+ use Illuminate\Database\Migrations\Migration;
+ use Illuminate\Database\Schema\Blueprint;
+ use Illuminate\Support\Facades\Schema;
+ 
+ return new class extends Migration
+ {
+     public function up(): void
+     {
+         Schema::table('complaints', function (Blueprint $table) {
+             if (!Schema::hasColumn('complaints', 'assigned_to_name')) {
+                 $table->string('assigned_to_name')->nullable()->after('assigned_to');
+             }
+ 
+             if (!Schema::hasColumn('complaints', 'resolver_name')) {
+                 $table->string('resolver_name')->nullable()->after('resolved_by');
+             }

+ <?php
+ 
+ use Illuminate\Database\Migrations\Migration;
+ use Illuminate\Database\Schema\Blueprint;
+ use Illuminate\Support\Facades\Schema;
+ 
+ return new class extends Migration
+ {
+     public function up(): void
+     {
+         Schema::create('user_activity_logs', function (Blueprint $table) {
+             $table->id();
+             $table->string('group_name')->nullable();
+             $table->string('username')->nullable();
+             $table->unsignedBigInteger('user_id')->nullable()->index();
+             $table->unsignedBigInteger('group_id')->nullable()->index();
+             $table->string('role_name')->nullable();
+             $table->unsignedBigInteger('role_id')->nullable()->index();

- </select></td>
- <td>{{ Str::limit($complaint->complaint_description, 50) }}</td>
- <td>
-                                 <select class="form-select form-select-sm assign-user-select select2" 
-                                         data-complaint-id="{{ $complaint->id }}" 
-                                         style="min-width: 150px;"
-                                         {{ $complaint->resolver_status && $complaint->user_status === 'completed' ? 'disabled readonly' : '' }}>
-                                     <option value="">Select User</option>
-                                     @if(isset($groupUsers[$complaint->natureOfComplaint->group_id]))
-                                         @foreach($groupUsers[$complaint->natureOfComplaint->group_id] as $user)
-                                             <option value="{{ $user->id }}" 
-                                                     {{ $complaint->assigned_to == $user->id ? 'selected' : '' }}>
-                                                 {{ $user->name }}
-                                             </option>
-                                         @endforeach
-                                     @else
-                                         <option value="" disabled>No users available for this group</option>
-                                     @endif

+ </select></td>
+ <td>{{ Str::limit($complaint->complaint_description, 50) }}</td>
+ <td>
+                                 <input type="text"
+                                        class="form-control form-control-sm assign-user-input"
+                                        list="assignedToNames"
+                                        data-complaint-id="{{ $complaint->id }}"
+                                        data-current-value="{{ $complaint->assigned_to_display }}"
+                                        value="{{ $complaint->assigned_to_display }}"
+                                        placeholder="Assigned To"
+                                        style="min-width: 150px;"
+                                        {{ $complaint->resolver_status && $complaint->user_status === 'completed' ? 'disabled readonly' : '' }}>
+ </td>
+ <td>
+ <span class="badge bg-{{ $complaint->complaint_status_color }}">
+ </div>
+ </div>
+ 

- </style>
- </head>
- <body>
- <div class="container">
- <div class="header">
- <h1 class="">JOB CARD</h1>
- </div>
- <div class="field">
- <label>Sec:</label>
-                 <span class="field-value">{{ 'WSG/' . ($complaint->natureOfComplaint?->group?->name ?? '') }}</span>
- </div>
- <div class="field">
- <label>Date:</label>
- <div>
- <div class="section-title">Nomenclature of Equipment:</div>
- <div class="items-list">
-                         <div class="item">1. <span class="field-value" style="display: inline-block; width: 300px;"></span></div>
-                         <div class="item">2. <span class="field-value" style="display: inline-block; width: 300px;"></span></div>

+ </style>
+ </head>
+ <body>
+     @php
+         $equipmentItems = $complaint->equipment_items ?? [];
+     @endphp
+ <div class="container">
+ <div class="header">
+ <h1 class="">JOB CARD</h1>
+ </div>
+ <div class="field">
+ <label>Sec:</label>
+                 <span class="field-value">{{ 'WSG/' . ($complaint->assigned_to_display ?: '') }}</span>
+ </div>
+ <div class="field">
+ <label>Date:</label>
+ <div>
+ <div class="section-title">Nomenclature of Equipment:</div>

- <div class="col-md-2">
- <label for="assigned_to" class="form-label">Assigned To</label>
- <select id="assigned_to" name="assigned_to" class="form-select select2">
-                         <option value="">All Users</option>
-                         @foreach($users as $user)
-                             <option value="{{ $user->id }}">{{ $user->name }}</option>
- @endforeach
- </select>
- </div>
- <option value="partially_completed">Partially Completed</option>
- </select>
- </div>
- <div class="col-md-2">
- <label for="complaint_status" class="form-label">Complaint Status</label>
- <select id="complaint_status" name="complaint_status" class="form-select select2">
- </thead>
- <tbody id="complaintTableBody">
- <tr>

+ <div class="col-md-2">
+ <label for="assigned_to" class="form-label">Assigned To</label>
+ <select id="assigned_to" name="assigned_to" class="form-select select2">
+                         <option value="">All Names</option>
+                         @foreach($assignedNames as $name)
+                             <option value="{{ $name }}">{{ $name }}</option>
+ @endforeach
+ </select>
+ </div>
+ <option value="partially_completed">Partially Completed</option>
+ </select>
+ </div>
+                 <div class="col-md-2">
+                     <label for="resolver_name" class="form-label">Resolver Name</label>
+                     <select id="resolver_name" name="resolver_name" class="form-select select2">
+                         <option value="">All Names</option>
+                         @foreach($resolverNames as $name)
+                             <option value="{{ $name }}">{{ $name }}</option>

- <!-- Delete -->
- <a href="javascript:void(0);" class="text-danger ms-3 btn-delete"
- data-id="{{ $p->id }}"
-        data-url="{{ url('/financial-powers/delete/' . $p->id) }}">
- <i class="fas fa-trash text-dark"></i>
- </a>
- 
- 
- @section('scripts')
- <script>
- $(document).ready(function() {
- // Reset
- $('[data-bs-target="#fpModal"]').on('click', function() {
- $('#inherit_power').val($(this).data('inherit'));
- $('#power_in_consultation_with_isa').val($(this).data('power'));
- $('#fpModalTitle').text('Edit Financial Power');
-         $('#fpModal').modal('show');
- $('#cfaError').addClass('d-none');

+ <!-- Delete -->
+ <a href="javascript:void(0);" class="text-danger ms-3 btn-delete"
+ data-id="{{ $p->id }}"
+        data-url="{{ route('financialpowers.destroy', $p->id) }}">
+ <i class="fas fa-trash text-dark"></i>
+ </a>
+ 
+ 
+ @section('scripts')
+ <script>
+ const financialPowerRoutes = {
+     store: @json(route('financialpowers.store')),
+     updateBase: @json(url('/financial-powers/update')),
+     fundsBase: @json(url('/financial-powers')),
+ };
+ 
+ $(document).ready(function() {
+ // Reset

- <div class="col-md-9">
- <div class="d-flex justify-content-end align-items-end gap-4">
- {{-- Filter Form --}}
-                             <form id="filterForm" method="GET" action="{{ url('lpofunds') }}" class="d-flex gap-2">
- <div>
- <label class="form-label">Search</label>
- <input type="text" name="search" class="form-control"
- $endYear = $currentYear + 5;
- @endphp
- @for($year = $currentYear; $year <= $endYear; $year++)
-                                             @php $fy = $year . '-' . ($year + 1); @endphp
- <option value="{{ $fy }}" {{ request('financial_year') == $fy ? 'selected' : '' }}>
- {{ $fy }}
- </option>
- <div class="modal fade" id="lpoModal" tabindex="-1" aria-labelledby="lpoModalLabel" aria-hidden="true">
- <div class="modal-dialog">
- <div class="modal-content">
-                             <form method="POST" action="{{ url('/lpofunds/store') }}" id="lpoForm">

+ <div class="col-md-9">
+ <div class="d-flex justify-content-end align-items-end gap-4">
+ {{-- Filter Form --}}
+                             <form id="filterForm" method="GET" action="{{ route('lpofunds.index') }}" class="d-flex gap-2">
+ <div>
+ <label class="form-label">Search</label>
+ <input type="text" name="search" class="form-control"
+ $endYear = $currentYear + 5;
+ @endphp
+ @for($year = $currentYear; $year <= $endYear; $year++)
+                                             @php $fy = $year . '-' . substr($year + 1, 2); @endphp
+ <option value="{{ $fy }}" {{ request('financial_year') == $fy ? 'selected' : '' }}>
+ {{ $fy }}
+ </option>
+ <div class="modal fade" id="lpoModal" tabindex="-1" aria-labelledby="lpoModalLabel" aria-hidden="true">
+ <div class="modal-dialog">
+ <div class="modal-content">
+                             <form method="POST" action="{{ route('lpofunds.store') }}" id="lpoForm">

- 
- <div class="col-md-2">
- <label class="form-label">Total Allotment</label>
-                             <input type="text" name="total_allotment" id="totalAllotment" class="form-control">
- </div>
- 
- <div class="col-md-2">
- <label class="form-label">Total Expenditure</label>
-                             <input type="text" name="total_expenditure" id="totalExpenditure" class="form-control">
- </div>
- 
- <div class="col-md-2">
- <label class="form-label">Bills Fwd to CDA</label>
-                             <input type="text" name="bills_fwd_to_cda" id="billsFwdToCda" class="form-control">
- </div>
- 
- <div class="col-md-2">
- <label class="form-label">Bills Passed by CDA</label>

+ 
+ <div class="col-md-2">
+ <label class="form-label">Total Allotment</label>
+                             <input type="number" step="0.01" min="0" name="total_allotment" id="totalAllotment" class="form-control">
+ </div>
+ 
+ <div class="col-md-2">
+ <label class="form-label">Total Expenditure</label>
+                             <input type="number" step="0.01" min="0" name="total_expenditure" id="totalExpenditure" class="form-control">
+ </div>
+ 
+ <div class="col-md-2">
+ <label class="form-label">Bills Fwd to CDA</label>
+                             <input type="number" step="0.01" min="0" name="bills_fwd_to_cda" id="billsFwdToCda" class="form-control">
+ </div>
+ 
+ <div class="col-md-2">
+ <label class="form-label">Bills Passed by CDA</label>

- 
- const CSRF = document.querySelector('meta[name="csrf-token"]')?.content || '{{ csrf_token() }}';
- 
- async function calcNACValidity() {
- const d = document.getElementById('txtNACDate').value;
- if (!d) {

+ 
+ const CSRF = document.querySelector('meta[name="csrf-token"]')?.content || '{{ csrf_token() }}';
+ 
+ function showMsg(text, cls = 'info') {
+     const box = document.getElementById('msgBox');
+     if (!box) return;
+ 
+     box.textContent = text;
+     box.className = `alert alert-${cls} fw-bold border-start border-5`;
+     box.classList.remove('d-none');
+     setTimeout(() => box.classList.add('d-none'), 8000);
+ }
+ 
+ async function calcNACValidity() {
+ const d = document.getElementById('txtNACDate').value;
+ if (!d) {

- </div>
- </div>
- <div class="card-footer text-end">
- <button type="submit" class="btn btn-success px-4">
- Save Challan
- </button>

+ </div>
+ </div>
+ <div class="card-footer text-end">
+                     <a href="{{ url('print/inspection-note') . '?orderNo=' . base64_encode($orderNo) }}"
+                        target="_blank"
+                        class="btn btn-primary px-4 me-2">
+                         Print Inspection Note
+                     </a>
+ <button type="submit" class="btn btn-success px-4">
+ Save Challan
+ </button>

- });
- 
- // ── REPORTS ───────────────────────────────────────────────────────────────
-     Route::prefix('reports')->name('reports.')->group(function () {
- Route::get('/abc',             [AbcReportController::class, 'index'])->name('abc');
- Route::get('/order-state',     [OrderStateController::class, 'index'])->name('order-state');
- Route::get('/search',          [SearchController::class, 'index'])->name('search');

+ });
+ 
+ // ── REPORTS ───────────────────────────────────────────────────────────────
+     Route::prefix('lpo-reports')->name('reports.')->group(function () {
+ Route::get('/abc',             [AbcReportController::class, 'index'])->name('abc');
+ Route::get('/order-state',     [OrderStateController::class, 'index'])->name('order-state');
+ Route::get('/search',          [SearchController::class, 'index'])->name('search');