Untitled
unknown
plain_text
22 days ago
6.6 kB
2
Indexable
Never
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 // ] // ]); }
Leave a Comment