Untitled

elseif ($action == 'getStockCard') {

            $tableNames = ReportStock::select('table_name')
                ->where('table_name', '!=', 'stock_loads')
                ->where('table_name', '!=', 'preorder_transactions')
                ->distinct()
                ->pluck('table_name');

            $query->leftJoin('warehouses as warehouse_parent', 'warehouse_parent.id', '=', 'report_stocks.warehouse_parent_id');
            $query->leftJoin('warehouses', 'warehouses.id', '=', 'report_stocks.warehouse_id');

            $query->leftJoin('product_brands', 'product_brands.id', '=', 'report_stocks.product_id');

            $query->selectRaw('
                (array_agg(report_stocks.id order by report_stocks.id))[1] AS id,
                report_stocks.warehouse_parent_id,
                report_stocks.table_name AS table_name,
                --report_stocks.header_transaction_code AS header_transaction_code,
                --report_stocks.created_at,
                (array_agg(report_stocks.created_at order by report_stocks.created_at))[1] AS created_at,
                warehouse_parent.name as warehouse_parent,
                warehouse_parent.code || \' - \' || warehouse_parent.name as warehouse_parent_name,
                warehouses.name as warehouse,
                warehouses.code || \' - \' || warehouses.name as warehouse_name,
                product_brands.code AS brand_code,
                product_brands.name AS brand_name,

                CASE
                    WHEN report_stocks.table_name = \'good_receives\' THEN \'Purchase Order\'
                    WHEN report_stocks.table_name IN (\'stock_transfer_orders\', \'good_issues\', \'wdp_good_receives\') THEN \'STO\'
                    WHEN report_stocks.table_name = \'disposal_scrappings\' THEN \'Disposal\'
                    WHEN report_stocks.table_name = \'PID\' THEN \'PID\'
                    WHEN report_stocks.table_name = \'stock_conditions\' THEN \'Stock Condition\'
                    WHEN report_stocks.table_name = \'migos\' THEN \'Migo\'
                    WHEN report_stocks.table_name = \'preorder_transactions\' THEN \'Preorder Transaction\'
                    WHEN report_stocks.table_name = \'stock_loads\' THEN \'Stock Load\'
                    ELSE \'Other\'
                END AS type,

                to_char(SUM(CASE WHEN report_stocks.quantity > 0 THEN report_stocks.quantity ELSE 0 END), \'999,999,999,999\') AS in_qty,
                to_char(SUM(CASE WHEN report_stocks.quantity < 0 THEN report_stocks.quantity ELSE 0 END), \'999,999,999,999\') AS out_qty,
                to_char(
                    SUM(CASE WHEN report_stocks.quantity > 0 THEN report_stocks.quantity ELSE 0 END) +
                    SUM(CASE WHEN report_stocks.quantity < 0 THEN report_stocks.quantity ELSE 0 END),
                    \'999,999,999,999\'
                ) AS balance
            ');

            // Loop through the tables and add the join conditions dynamically
            foreach ($tableNames as $tableName) {
                $query->leftJoin($tableName, function ($join) use ($tableName) {
                    $join->on('report_stocks.header_transaction_id', '=', "$tableName.id")
                        ->where('report_stocks.table_name', '=', $tableName);
                });

                if ($tableName == 'credit_note_transactions') {
                    $query->addSelect(
                        DB::raw("CASE WHEN report_stocks.table_name = '$tableName' THEN $tableName.credit_note_number ELSE NULL END AS transaction_code_" . $tableName)
                    );
                } else {
                    $query->addSelect(
                        DB::raw("CASE WHEN report_stocks.table_name = '$tableName' THEN $tableName.code ELSE NULL END AS transaction_code_" . $tableName)
                    );
                }

                if ($tableName == 'credit_note_transactions') {
                    $query->groupBy(
                        DB::raw("CASE WHEN report_stocks.table_name = '$tableName' THEN $tableName.credit_note_number ELSE NULL END")
                    );
                } else {
                    $query->groupBy(
                        DB::raw("CASE WHEN report_stocks.table_name = '$tableName' THEN $tableName.code ELSE NULL END")
                    );
                }
            }

            $query->where('report_stocks.product_id', '!=', null);
            $query->where('report_stocks.is_active', true);
            $query->where('report_stocks.deleted_at', null);

            $startDate = date('Y-m-d');
            $startDateTime = $startDate . ' 00:00:00';
            $endDateTime = $startDate . ' 23:59:59';

            if (isset($filters['custom_filters'])) {
                foreach ($filters['custom_filters'] as $filter) {
                    if ($filter['key'] == 'report_stocks.created_at' && $filter['term'] == 'less than') {
                        $startDate = $filter['query'];
                        $startDateTime = $startDate . ' 00:00:00';
                        $endDateTime = $startDate . ' 23:59:59';

                        $filter_date = Date('Y-m-d H:i:s', strtotime($startDateTime . ' +1 day'));
                        $query->where('report_stocks.created_at', '<', $filter_date);
                    }
                }
            }

            $query->orderBy('report_stocks.created_at');
            $query->orderBy('product_brands.code');

            $query->groupBy(
                'report_stocks.warehouse_parent_id',
                'report_stocks.created_at',
                'report_stocks.table_name',
                // 'report_stocks.header_transaction_code',

                'warehouse_parent.name',
                'warehouse_parent.code',
                'warehouses.name',
                'warehouses.code',

                'product_brands.code',
                'product_brands.name',
            );
        }