Untitled

else if ($action == 'getStockCardReport') {
            function generateTableSubquery($tableConfigs) {
                $subquery = '';
                foreach ($tableConfigs as $tableName => $codeColumn) {
                    $subquery .= "SELECT CAST(id AS bigint) as id, $codeColumn as code, '$tableName' as table_name FROM $tableName UNION ALL ";
                }
                return rtrim($subquery, ' UNION ALL ');
            }

            $tableConfig = DB::table('report_stocks')->pluck('table_name');
            $tableConfigs = $tableConfig->mapWithKeys(function ($tableName) {
                return [$tableName => 'code'];
            })->toArray();

            $querys = DB::table('report_stocks')
                ->select([
                    'report_stocks.date as tanggal',
                    DB::raw('COALESCE(items.name, \'Unknown\') as relasi'),
                    'items.name as barang',
                    'warehouses.name as gudang',
                    'report_stocks.quantity as awal',
                    'units.name as satuan',
                    'tables.code as keterangan',
                    DB::raw("SUM(CASE WHEN report_stocks.quantity > 0 THEN report_stocks.quantity ELSE 0 END) as masuk"),
                    DB::raw("SUM(CASE WHEN report_stocks.quantity < 0 THEN ABS(report_stocks.quantity) ELSE 0 END) as keluar"),
                    DB::raw("(SUM(CASE WHEN report_stocks.quantity > 0 THEN report_stocks.quantity ELSE 0 END) - SUM(CASE WHEN report_stocks.quantity < 0 THEN ABS(report_stocks.quantity) ELSE 0 END)) as akhir"),
                ])
                ->leftJoin('items', 'report_stocks.item_id', '=', 'items.id')
                ->leftJoin('units', 'items.unit3_id', '=', 'units.id')
                ->leftJoin('warehouses', 'report_stocks.warehouse_id', '=', 'warehouses.id')
                ->leftJoin('locations', 'report_stocks.location_id', '=', 'locations.id')
                ->leftJoin(DB::raw("(" . generateTableSubquery($tableConfigs) . ") as tables"), function ($join) {
                    $join->on('report_stocks.table_name', '=', 'tables.table_name')
                        ->on(DB::raw('CAST(report_stocks.header_transaction_id AS bigint)'), '=', 'tables.id');
                })
                ->whereNull('report_stocks.deleted_at')
                ->groupBy('report_stocks.date', 'items.name', 'warehouses.name', 'report_stocks.quantity', 'tables.code', 'report_stocks.header_transaction_id','units.name')
                ->orderBy('report_stocks.date', 'ASC')
                ->skip($filters['skip'])
                ->take($filters['take'])
                ->get();

            $results = [];
            $cumulativeStock = [];

            foreach ($querys as $item) {
                $key = $item->barang . '-' . $item->gudang;

                if (!isset($cumulativeStock[$key])) {
                    $cumulativeStock[$key] = 0;
                }

                $cumulativeStock[$key] += $item->masuk;
                $cumulativeStock[$key] -= $item->keluar;

                $item->akhir = $cumulativeStock[$key];
                $results[] = [
                    'date' => $item->tanggal,
                    'relation' => $item->relasi,
                    'warehouse' => $item->gudang,
                    'remark' => $item->keterangan,
                    'incoming' => $item->masuk,
                    'outgoing' => $item->keluar,
                    'end' => $item->akhir,
                    'unit' => $item->satuan,
                ];
            }
            $query ->select([
                    'report_stocks.date as tanggal',
                    DB::raw('COALESCE(items.name, \'Unknown\') as relasi'),
                    'items.name as barang',
                    'warehouses.name as gudang',
                    'report_stocks.quantity as awal',
                    'units.name as satuan',
                    'tables.code as keterangan',
                    DB::raw("SUM(CASE WHEN report_stocks.quantity > 0 THEN report_stocks.quantity ELSE 0 END) as masuk"),
                    DB::raw("SUM(CASE WHEN report_stocks.quantity < 0 THEN ABS(report_stocks.quantity) ELSE 0 END) as keluar"),
                    DB::raw("(SUM(CASE WHEN report_stocks.quantity > 0 THEN report_stocks.quantity ELSE 0 END) - SUM(CASE WHEN report_stocks.quantity < 0 THEN ABS(report_stocks.quantity) ELSE 0 END)) as akhir"),
                ])
                ->leftJoin('items', 'report_stocks.item_id', '=', 'items.id')
                ->leftJoin('units', 'items.unit3_id', '=', 'units.id')
                ->leftJoin('warehouses', 'report_stocks.warehouse_id', '=', 'warehouses.id')
                ->leftJoin('locations', 'report_stocks.location_id', '=', 'locations.id')
                ->leftJoin(DB::raw("(" . generateTableSubquery($tableConfigs) . ") as tables"), function ($join) {
                    $join->on('report_stocks.table_name', '=', 'tables.table_name')
                        ->on(DB::raw('CAST(report_stocks.header_transaction_id AS bigint)'), '=', 'tables.id');
                })
                ->whereNull('report_stocks.deleted_at')
                ->groupBy('report_stocks.date', 'items.name', 'warehouses.name', 'report_stocks.quantity', 'tables.code', 'report_stocks.header_transaction_id','units.name')
                ->orderBy('report_stocks.date', 'ASC');

            $countDataQuery = function ($query) {
                return $query->toBase()->getCountForPagination();
            };
    
            $countData = $countDataQuery($query);
    
            if (isset($requestBody['draw'])) {
                $draw = $requestBody['draw'];
            }
    
            $result = ['draw' => $draw ?? '', 'recordsTotal' => $countData, 'recordsFiltered' => $countData, 'total_rows' => $countData, 'data' => $results];
    
            return $this->sendResponse(
                true,
                Response::HTTP_OK,
                $result
            );
            // return response()->json([
            //     'success' => true,
            //     'status_code' => 200,
            //     'data' => [
            //         'draw' => '',
            //         'recordsTotal' => count($results),
            //         'recordsFiltered' => count($results),
            //         'total_rows' => count($results),
            //         'data' => $results
            //     ]
            // ]);
        }