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()];
        }
    }

    public function getStockCountWithPercentage()
    {
        try {
            // 1. 获取基础数据：明细分类库存
            $details = 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 sub_code',     // 明细编码 (如 0102)
                    'ic.cInvCName as sub_name',     // 明细名称
                    DB::raw('SUM(s.iQuantity) as sub_quantity')
                ])
                ->groupBy('ic.cInvCCode', 'ic.cInvCName')
                ->get();

            // 2. 转换数据结构，按大类进行逻辑汇总
            $collection = $details->map(function ($item) {
                return [
                    'parent_code'  => substr(trim($item->sub_code), 0, 2), // 截取前两位作为大类编码
                    'sub_code'     => trim($item->sub_code),
                    'sub_name'     => trim($item->sub_name),
                    'sub_quantity' => (float)$item->sub_quantity,
                ];
            });

            // 3. 计算每个大类的总数 (以 parent_code 分组求和)
            $parentTotals = $collection->groupBy('parent_code')->map(function ($group) {
                return $group->sum('sub_quantity');
            });

            // 4. 组装最终结果，合并大类总数并计算百分比
            $result = $collection->map(function ($item) use ($parentTotals) {
                $parentSum = $parentTotals[$item['parent_code']] ?? 0;

                // 计算占比：(子类数量 / 父类总数) * 100
                $percentage = $parentSum > 0
                    ? round(($item['sub_quantity'] / $parentSum) * 100, 2)
                    : 0;

                return [
                    'parent_category_code' => $item['parent_code'],
                    'category_code'        => $item['sub_code'],
                    'category_name'        => $item['sub_name'],
                    'total_quantity'       => $item['sub_quantity'],
                    'parent_total'         => $parentSum,
                    'percentage'           => $percentage, // 格式化为百分比字符串
                ];
            })->values()->toArray();

            return [true, $result];

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