workapi/app/Service/U8ThirtyPartyDatabaseServerService.php

<?php
namespace App\Service;

use Illuminate\Support\Facades\Config;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class U8ThirtyPartyDatabaseServerService extends Service
{
    protected $connectionName = '';

    public function __construct($data)
    {
        $this->createConnection($data);
    }

    private function createConnection($data)
    {
        $mainConnName = $data['connect_name'];
        $this->connectionName = $mainConnName;

        // 创建连接
        $mainConfig = [
            'driver'   => 'sqlsrv',
            'host'     => $data['api_host'],
            'port'     => $data['database_port'],
            'database' => 'UFDATA_001_2025',
            'username' => $data['username'],
            'password' => $data['password'],
            'options'  => [
                \PDO::SQLSRV_ATTR_QUERY_TIMEOUT => 15,  // 减少超时
                \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
            ],
        ];

        // 配置注入
        Config::set("database.connections.{$mainConnName}", $mainConfig);
    }

    public function getStockCountByCategory()
    {
        try {
            $result = DB::connection($this->connectionName)
                ->table('Inventory as i')
                ->lock('WITH(NOLOCK)')
                ->join('InventoryClass as ic', 'i.cInvCCode', '=', 'ic.cInvCCode')
                ->join('CurrentStock as s', 'i.cInvCode', '=', 's.cInvCode')
                ->select([
                    'ic.cInvCCode as category_code',
                    'ic.cInvCName as category_name',
                    DB::raw('SUM(s.iQuantity) as total_quantity')
                ])
                ->groupBy('ic.cInvCCode', 'ic.cInvCName')
                ->orderBy('ic.cInvCCode', 'ASC')
                ->get()
                ->map(function($item) {
                    return [
                        'category_code'  => trim($item->category_code),
                        'category_name'  => trim($item->category_name),
                        'total_quantity' => (float)$item->total_quantity,
                    ];
                })
                ->toArray();

            return [true, $result];

        } catch (\Throwable $e) {
            return [false, $e->getMessage()];
        }
    }
}