drb/app/Service/U8ThirtyPartyDatabaseServerService.php

<?php
namespace App\Service;

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

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

    // Service 内部查询
    public function getPendingBills($config, $lastId)
    {
        try {

            $mainTable = $config['main'];
            $detailTable = $config['detail'];
            $mainKey = $config['main_key'];
            $foreignKey = $config['key'];
            $whereRaw = $config['whereRaw'];
            $main_field = $config['main_field'];
            $son_field = $config['son_field'];
            $limit = $config['limit'];

            // 1. 获取主表
            $mainRows = DB::connection($this->connectionName)
                ->table($mainTable)
                ->lock('WITH(NOLOCK)')
                ->where($mainKey, '>', $lastId) // 改用 ID
                ->whereRaw($whereRaw)
                ->select($main_field)
                ->orderBy($mainKey, 'ASC') // 按 ID 排序保证分页连续
                ->limit($limit)
                ->get();

            if ($mainRows->isEmpty()) return [true, []];
            if (empty($foreignKey) || empty($son_field)) {
                $array = $mainRows->map(function ($item) {
                    return (array) $item;
                })->toArray();
                return [true, $array];
            }

            // 2. 批量获取子表
            $mainIds = $mainRows->pluck('id')->toArray();
//            $details = DB::connection($this->connectionName)
//                ->table($detailTable . ' as detail') // 给子表起别名
//                ->leftJoin('Inventory as inv', 'detail.cInvCode', '=', 'inv.cInvCode') // 关联存货表
//                ->lock('WITH(NOLOCK)')
//                ->whereIn('detail.' . $foreignKey, $mainIds) // 注意这里要指定表别名
//                ->select($son_field) // 这里会读取你配置里的新字段
//                ->get()
//                ->groupBy('id');
            $details = DB::connection($this->connectionName)
                ->table($detailTable. ' as detail')
                ->lock('WITH(NOLOCK)')
                ->whereIn($foreignKey, $mainIds)
                ->select($son_field)
                ->addSelect(DB::raw("ROW_NUMBER() OVER(PARTITION BY detail.{$foreignKey} ORDER BY detail.AutoID) as lineNum"))
                ->get()
                ->groupBy('id');

            // 3. 组合
            $result = $mainRows->map(function($main) use ($details, $foreignKey) {
                $res = (array)$main;
                $mainId = $res['id'];
                $res['details'] = isset($details[$mainId])
                    ? $details[$mainId]->map(function($item) {
                        return (array)$item; // 强制把子表对象转成数组
                    })->values()->toArray()
                    : [];
                return $res;
            })->toArray();

            return [true, $result];

        } catch (\Throwable $e) {
            Log::channel('apiLog')->info('查询失败', [
                "error"    => $e->getMessage(),
            ]);
            return [false, $e->getMessage()];
        }
    }

    public function getBillSingle($config, $id)
    {
        try {
            $mainTable = $config['main'];
            $detailTable = $config['detail'];
            $mainKey = $config['main_key']; // 比如 ID
            $foreignKey = $config['key'];   // 比如 id (子表关联主表的字段)
            $main_field = $config['main_field'];
            $son_field = $config['son_field'];

            // 1. 获取主表单条记录
            $mainRow = DB::connection($this->connectionName)
                ->table($mainTable)
                ->lock('WITH(NOLOCK)')
                ->where($mainKey, $id) // 精确匹配单据 ID
                ->select($main_field)
                ->first();

            if (!$mainRow) {
                return [false, "单据 ID:{$id} 未找到数据"];
            }

            $result = (array)$mainRow;

            // 2. 如果不需要子表，直接返回
            if (empty($foreignKey) || empty($son_field)) {
                return [true, $result];
            }

            // 3. 获取该单据对应的子表详情
            // 注意：U8 子表通常用 id 关联主表的 ID，这里使用配置中的 $foreignKey
            $details = DB::connection($this->connectionName)
                ->table($detailTable . ' as detail')
                ->lock('WITH(NOLOCK)')
                ->where($foreignKey, $id)
                ->select($son_field)
                // 自动生成行号，按 AutoID 排序
//                ->addSelect(DB::raw("ROW_NUMBER() OVER(ORDER BY detail.AutoID) as lineNum"))
                ->orderBy('detail.AutoID', 'ASC')
                ->get();

            // 4. 组合数据
            $result['details'] = $details->map(function ($item) {
                return (array)$item;
            })->toArray();

            return [true, $result];

        } catch (\Throwable $e) {
            Log::channel('apiLog')->info('查询单个单据失败', [
                "id"    => $id,
                "error" => $e->getMessage(),
            ]);
            return [false, $e->getMessage()];
        }
    }

    public function getArrivalVouchById($orderId)
    {
        try {
            // 1. 获取主表 (PU_ArrivalVouch) 并关联 采购类型表 (PurchaseType)
            $main = DB::connection($this->connectionName)
                ->table('PU_ArrivalVouch as main')
                ->leftJoin('PurchaseType as pt', 'main.cPTCode', '=', 'pt.cPTCode') // 关联采购类型表
                ->lock('WITH(NOLOCK)')
                ->where('main.ID', $orderId)
                ->select([
                    'main.ID as id',
                    'main.cCode as no',
                    'main.cVenCode as supply_code',
                    'main.cDepCode as depart_code',
                    'main.dDate as order_date',
                    'main.cBusType as b_type',
                    'main.cPTCode as cg_type',
                    'pt.cRdCode as rd_code'
                ])
                ->first();

            if (!$main) {
                return [false, "到货单 ID:{$orderId} 不存在"];
            }

            $order = (array)$main;

            // 2. 获取子表 (PU_ArrivalVouchs)
            $details = DB::connection($this->connectionName)
                ->table('PU_ArrivalVouchs as detail')
                ->lock('WITH(NOLOCK)')
                ->where('ID', $orderId) // 子表里的 ID 关联主表的 ID
                ->select([
                    'detail.cInvCode',
                    'detail.iQuantity',
                    'detail.iQuantity as iNNum',
                    'detail.iinvexchrate',
                    'detail.cBatch',
                    'detail.dPDate',
                    'detail.dVDate',
                    'detail.Autoid as iArrsId',
                    'detail.ivouchrowno as iRowNo',
                    'detail.iOriTaxCost',
                    'detail.iTaxRate',
//                    'detail.cordercode',
                    'detail.cWhCode',
                ])
                ->orderBy('detail.ivouchrowno', 'ASC') // 按 U8 原始行号排序
                ->get();

            // 3. 组合数据并返回
            $order['details'] = $details->map(function ($item) {
                return (array)$item;
            })->toArray();

            return [true, $order];

        } catch (\Throwable $e) {
            \Log::error("获取到货单失败: " . $e->getMessage());
            return [false, "数据库查询异常: " . $e->getMessage()];
        }
    }

    /**
     * 获取存货档案（带分类名称）
     * @param array $config 配置项
     * @param mixed $lastId 上次分页的标识（这里用存货编码 cInvCode 或 自动增量标识）
     * @return array [bool, items]
     */
    public function getInventoryData1($config, $lastId = null)
    {
        try {
            $query = DB::connection('u8_third_sqlserver')
                ->table('Inventory as main')
                ->leftJoin('InventoryClass as sub', 'main.cInvCCode', '=', 'sub.cInvCCode')
                ->select([
                    'main.cInvCode as product_code',
                    'main.cInvName as product_name',
                    'main.cInvStd as product_size',
                    'main.cInvCCode as product_category_code',
                    'sub.cInvCName as product_category_name', // 直接联查分类名
                    'main.cComUnitCode as product_unit',
                    'main.fOutExcess as out_limit',
                    'main.cCurrencyName as common_name',
                    'main.fGrossW as grossWeight',
                    'main.dModifyDate as upd_time'
                ]);

            // 分页逻辑
            if ($lastId) {
                $query->where('main.cInvCode', '>', $lastId);
            }

            // 基础过滤条件
            if (!empty($config['whereRaw'])) {
                $query->whereRaw($config['whereRaw']);
            }

            $items = $query->orderBy('main.cInvCode', 'asc')
                ->limit($config['limit'])
                ->get();

            // 将 Collection 转为数组方便后续处理
            $items = json_decode(json_encode($items), true);

            return [true, $items];
        } catch (\Exception $e) {
            Log::channel('apiLog')->error("U8获取存货异常: " . $e->getMessage());
            return [false, []];
        }
    }

    public function getInventoryData($config, $lastCode = null)
    {
        try {
            $query = DB::connection('u8_third_sqlserver')
                ->table('Inventory as main')
                ->leftJoin('InventoryClass as sub', 'main.cInvCCode', '=', 'sub.cInvCCode')
                ->leftJoin('ComputationUnit as unit', 'main.cComUnitCode', '=', 'unit.cComUnitCode')
                ->select([
                    'main.cInvCode as product_code',
                    'main.cInvName as product_name',
                    'main.cInvStd as product_size',
                    'main.cInvCCode as product_category_code',
                    'sub.cInvCName as product_category_name', // 直接联查分类名
                    'main.cComUnitCode as product_unit',
                    'unit.cComUnitName as product_unit_title',
//                    'main.fOutExcess as out_limit',
//                    'main.cCurrencyName as common_name',
                    'main.fGrossW as grossWeight',
                    // 关键点：将二进制 ufts 转换为 0x... 字符串
                    DB::raw("master.sys.fn_varbintohexstr(main.pubufts) as ufts_str")
                ]);

            // 使用 product_code 做分页依据
            if ($lastCode) {
                $query->where('main.cInvCode', '>', $lastCode);
            }

            if (!empty($config['whereRaw'])) {
                $query->whereRaw($config['whereRaw']);
            }

            // 必须按编码排序，保证分页不重不漏
            $items = $query->orderBy('main.cInvCode', 'asc')
                ->limit($config['limit'])
                ->get();

            return [true, json_decode(json_encode($items), true)];
        } catch (\Exception $e) {
            Log::channel('apiLog')->error("U8获取存货异常: " . $e->getMessage());
            return [false, []];
        }
    }
}