xky/app/Service/U8ServerService.php

<?php

namespace App\Service;

use App\Model\DDEmployee;
use App\Model\FieldData;
use App\Model\Inventory;
use App\Model\Record;
use App\Model\U8State;
use App\Model\Vendor;
use Illuminate\Support\Facades\DB;

class U8ServerService extends Service
{
    protected $databaseService;

    /**
     * @var string|null
     */
    protected $error;

    /**
     * U8ServerService constructor.
     */
    public function __construct($loginUser = [])
    {
        $service = new U8DatabaseServerService($loginUser);
        $this->databaseService = $service->db;
        $this->error = $service->error;
    }

    /**
     * 获取错误信息
     *
     * @return string|null
     */
    public function getError()
    {
        return $this->error;
    }

    public function purchaseOrder($data, $user){
        $qx = $user['qx'];
        $order_date = $data['order_date'] ?? [];
        $order_date = array_filter($order_date);
        $order_number = $data['order_number'] ?? "";

        $model = $this->databaseService->table('PO_Pomain as a')
            ->leftJoin('Vendor as c', 'c.cVenCode', 'a.cVenCode')      // 供应商
            ->leftJoin('Department as d', 'd.cDepCode', 'a.cDepCode')  // 部门
            ->leftJoin('Person as e', 'e.cPersonCode', 'a.cPersonCode')// 业务员
            ->leftJoin('PurchaseType as f', 'f.cPTCode', 'a.cPTCode')  // 采购类型
            ->when(empty($qx), function ($query) use($user){
                return $query->where('a.cMaker', $user['name']);
            })
            ->when(!empty($order_number), function ($query) use($order_number){
                return $query->where('a.cPOID', 'LIKE', '%'.$order_number.'%');
            })
            ->when(!empty($order_date), function ($query) use($order_date){
                $start = date('Y-m-d 00:00:00.000', $order_date[0]);
                $end = date('Y-m-d 23:59:59.000', $order_date[1]);
                return $query->whereBetween('a.dPODate', [$start, $end]);
            })
            ->where(function ($query) {
                $query->where('a.iverifystateex', 0)
                    ->orWhereNull('a.iverifystateex');
            })
            ->select(
                DB::raw("ISNULL(a.cBusType, '') as business_type"),      // 业务类型
                DB::raw("ISNULL(CONVERT(varchar(10), a.dPODate, 120), '') as order_date"), // 订单日期
                DB::raw("ISNULL(a.cPOID, '') as order_number"),          // 订单编号
                DB::raw("ISNULL(f.cPTName, '') as purchase_type"),       // 采购类型
                DB::raw("ISNULL(c.cVenName, '') as supplier_title"),     // 供应商
                DB::raw("ISNULL(d.cDepName, '') as department_name"),    // 部门
                DB::raw("ISNULL(e.cPersonName, '') as person_name"),     // 业务员
                DB::raw("CAST(ISNULL(a.iTaxRate, 0) AS varchar) as tax_rate"), // 税率（数值转字符，null转0或空）
                DB::raw("ISNULL(a.cexch_name, '') as currency_name"),    // 币种
                DB::raw("CAST(ISNULL(a.nflat, 0) AS varchar) as exchange_rate"), // 汇率
                DB::raw("ISNULL(a.cMemo, '') as remark"),                // 备注
                DB::raw("ISNULL(a.cMaker, '') as crt_name")              // 制单人
            )
            ->orderBy('a.POID', 'desc');

        $list = $this->limit($model, '', $data);
        $list = $this->fillAll($list, U8State::type_one, $user['login_type']);

        return [true, $list];
    }

    public function purchaseOrderDetail($data, $user){
        if(empty($data['order_number'])) return [false, '采购单号不能为空'];

        $order = $this->databaseService->table('PO_Pomain as a')
            ->leftJoin('Vendor as c', 'c.cVenCode', 'a.cVenCode')
            ->leftJoin('Department as d', 'd.cDepCode', 'a.cDepCode')
            ->leftJoin('Person as e', 'e.cPersonCode', 'a.cPersonCode')
            ->leftJoin('PurchaseType as f', 'f.cPTCode', 'a.cPTCode')
            ->where('a.cPOID', $data['order_number'])
            ->select(
                'a.POID',
                DB::raw("ISNULL(a.cBusType, '') as business_type"),
                DB::raw("ISNULL(CONVERT(varchar(10), a.dPODate, 120), '') as order_date"),
                DB::raw("ISNULL(a.cPOID, '') as order_number"),
                DB::raw("ISNULL(f.cPTName, '') as purchase_type"),
                DB::raw("ISNULL(c.cVenName, '') as supplier_title"),
                DB::raw("ISNULL(d.cDepName, '') as department_name"),
                DB::raw("ISNULL(e.cPersonName, '') as person_name"),
                DB::raw("CAST(ISNULL(a.iTaxRate, 0) AS varchar) as tax_rate"),
                DB::raw("ISNULL(a.cexch_name, '') as currency_name"),
                DB::raw("CAST(ISNULL(a.nflat, 0) AS varchar) as exchange_rate"),
                DB::raw("ISNULL(a.cMemo, '') as remark"),
                DB::raw("ISNULL(a.cMaker, '') as crt_name")
            )
            ->first();

        if(empty($order)) return [false, '采购单不存在'];
        $order = (array) $order;
        $order = $this->fillDetail($order, U8State::type_one, $user['login_type']);

        // 获取明细
        $detail = $this->databaseService->table('PO_Podetails as a')
            ->leftJoin('Inventory as b', 'b.cInvCode', 'a.cInvCode')
            ->leftJoin('ComputationUnit as c', 'c.cComunitCode', 'b.cComUnitCode')
            ->where('a.POID', $order['POID'])
            ->select(
                DB::raw("ISNULL(b.cInvCode, '') as product_code"),
                DB::raw("ISNULL(b.cInvName, '') as product_title"),
                DB::raw("ISNULL(b.cInvStd, '') as product_std"),
                DB::raw("ISNULL(c.cComUnitName, '') as unit_title"),
                // 数字转字符串并去除空格，NULL 则返回 '0.00' 或 ''
                DB::raw("ISNULL(LTRIM(STR(a.iQuantity, 20, 2)), '0.00') as quantity"),
                DB::raw("ISNULL(LTRIM(STR(a.iTaxPrice, 20, 4)), '0.0000') as tax_unit_price"),
                DB::raw("ISNULL(LTRIM(STR(a.iUnitPrice, 20, 4)), '0.0000') as unit_price"),
                DB::raw("ISNULL(LTRIM(STR(a.iMoney, 20, 2)), '0.00') as amount"),
                DB::raw("ISNULL(LTRIM(STR(a.iSum, 20, 2)), '0.00') as tax_amount"),
                DB::raw("ISNULL(LTRIM(STR(a.iPerTaxRate, 20, 2)), '0.00') as tax_rate"),
                DB::raw("ISNULL(CONVERT(varchar(10), a.dArriveDate, 120), '') as arrive_date"),
                DB::raw("ISNULL(b.cEnterprise, '') as factory_name")
            )
            ->get();

        // 转为数组
        $order['detail'] = $detail->map(function ($item) {
            return (array) $item;
        })->toArray();

        // 移除内部 ID 避免暴露
        unset($order['POID']);

        return [true, $order];
    }

    public function purchaseRequisition($data, $user){
        $qx = $user['qx'];
        $order_date = $data['order_date'] ?? [];
        $order_date = array_filter($order_date);
        $order_number = $data['order_number'] ?? "";

        $model = $this->databaseService->table('PU_AppVouch as a')
            ->leftJoin('Person as c', 'c.cPersonCode', 'a.cPersonCode') // 请购人
            ->leftJoin('Department as d', 'd.cDepCode', 'a.cDepCode')  // 请购部门
            ->leftJoin('PurchaseType as f', 'f.cPTCode', 'a.cPTCode')  // 采购类型
            ->when(empty($qx), function ($query) use($user){
                return $query->where('a.cMaker',$user['name']);
            })
            ->when(! empty($order_number), function ($query) use($order_number){
                return $query->where('a.cCode','LIKE', '%'.$order_number.'%');
            })
            ->when(! empty($order_date), function ($query) use($order_date){
                $start = date('Y-m-d 00:00:00.000', $order_date[0]);
                $end = date('Y-m-d 23:59:59.000', $order_date[1]);
                return $query->whereBetween('a.dDate', [$start, $end]);
            })
            ->where(function ($query) {
                $query->where('a.iverifystateex', 0)
                    ->orWhereNull('a.iverifystateex');
            })
            ->select(
                DB::raw("ISNULL(a.cBusType, '') as business_type"),      // 业务类型
                DB::raw("ISNULL(a.cCode, '') as order_number"),          // 单据号
                DB::raw("ISNULL(CONVERT(varchar(10), a.dDate, 120), '') as order_date"), // 日期
                DB::raw("ISNULL(d.cDepName, '') as department_name"),    // 请购部门
                DB::raw("ISNULL(c.cPersonName, '') as purchase_name"),   // 请购人员
                DB::raw("ISNULL(f.cPTName, '') as purchase_type"),       // 采购类型
                DB::raw("ISNULL(a.cMaker, '') as crt_name")              // 制单人
            )
            ->orderBy('a.ID','desc');

        $list = $this->limit($model,'',$data);
        $list = $this->fillAll($list, U8State::type_two, $user['login_type']);

        return [true , $list];
    }

    public function purchaseRequisitionDetail($data, $user){
        if(empty($data['order_number'])) return [false, '采购请购单号不能为空'];

        $order = $this->databaseService->table('PU_AppVouch as a')
            ->leftJoin('Person as c', 'c.cPersonCode', 'a.cPersonCode')
            ->leftJoin('Department as d', 'd.cDepCode', 'a.cDepCode')
            ->leftJoin('PurchaseType as f', 'f.cPTCode', 'a.cPTCode')
            ->where('a.cCode', $data['order_number'])
            ->select(
                'a.ID', // 用于关联子表
                DB::raw("ISNULL(a.cBusType, '') as business_type"),
                DB::raw("ISNULL(a.cCode, '') as order_number"),
                DB::raw("ISNULL(CONVERT(varchar(10), a.dDate, 120), '') as order_date"),
                DB::raw("ISNULL(d.cDepName, '') as department_name"),
                DB::raw("ISNULL(c.cPersonName, '') as purchase_name"),
                DB::raw("ISNULL(f.cPTName, '') as purchase_type"),
                DB::raw("ISNULL(a.cMaker, '') as crt_name")
            )
            ->first();

        if(empty($order)) return [false, '采购请购单不存在'];
        $order = (array) $order;
        $order = $this->fillDetail($order, U8State::type_two, $user['login_type']);

        $detail = $this->databaseService->table('PU_AppVouchs as a')
            ->leftJoin('Inventory as b', 'b.cInvCode', 'a.cInvCode')
            ->leftJoin('ComputationUnit as c', 'c.cComunitCode', 'b.cComUnitCode')
            ->where('a.ID', $order['ID'])
            ->select(
                DB::raw("ISNULL(b.cInvCode, '') as product_code"),    // 存货编码
                DB::raw("ISNULL(b.cInvName, '') as product_title"),   // 存货名称
                DB::raw("ISNULL(b.cInvStd, '') as product_std"),      // 规格型号
                DB::raw("ISNULL(c.cComUnitName, '') as unit_title"),  // 主计量
                DB::raw("ISNULL(LTRIM(STR(a.fQuantity, 20, 2)), '0.00') as quantity"), // 数量
                DB::raw("ISNULL(LTRIM(STR(a.fUnitPrice, 20, 4)), '0.0000') as unit_price"), // 本币单价
                DB::raw("ISNULL(LTRIM(STR(a.iOriSum, 20, 2)), '0.00') as tax_amount"), // 本币价税合计
                DB::raw("ISNULL(CONVERT(varchar(10), a.dRequirDate, 120), '') as need_arrived_date"), // 需求日期
                DB::raw("ISNULL(CONVERT(varchar(10), a.dArriveDate, 120), '') as suggest_order_date"), // 建议订货日期
                DB::raw("ISNULL(b.cEnterprise, '') as factory_name") // 生产企业
            )
            ->get();

        // 转为数组格式
        $order['detail'] = $detail->map(function ($item) {
            return (array) $item;
        })->toArray();

        unset($order['ID']); // 隐藏内部ID

        return [true, $order];
    }

    public function purchaseInOrder($data, $user){
        $qx = $user['qx'];
        $order_date = $data['order_date'] ?? [];
        $order_date = array_filter($order_date);
        $order_number = $data['order_number'] ?? "";

        $model = $this->databaseService->table('RdRecord01 as a')
            ->leftJoin('Vendor as c', 'c.cVenCode', 'a.cVenCode')      // 供货单位
            ->leftJoin('Warehouse as w', 'w.cWhCode', 'a.cWhCode')    // 仓库
            ->leftJoin('Department as d', 'd.cDepCode', 'a.cDepCode')  // 部门
            ->leftJoin('Person as p', 'p.cPersonCode', 'a.cPersonCode')// 业务员
            ->leftJoin('PurchaseType as pt', 'pt.cPTCode', 'a.cPTCode')// 采购类型
            ->leftJoin('Rd_Style as rs', 'rs.cRdCode', 'a.cRdCode')   // 入库类别
            ->when(empty($qx), function ($query) use($user){
                return $query->where('a.cMaker',$user['name']);
            })
            ->when(! empty($order_number), function ($query) use($order_number){
                return $query->where('a.cCode','LIKE', '%'.$order_number.'%');
            })
            ->when(! empty($order_date), function ($query) use($order_date){
                $start = date('Y-m-d 00:00:00.000', $order_date[0]);
                $end = date('Y-m-d 23:59:59.000', $order_date[1]);
                return $query->whereBetween('a.dDate', [$start, $end]);
            })
            ->where(function ($query) {
                $query->whereNull('a.cHandler')
                    ->orWhere('a.cHandler', '');
            })// 未审核
            ->select(
                DB::raw("ISNULL(a.cCode, '') as order_number"),           // 入库单号
                DB::raw("ISNULL(CONVERT(varchar(10), a.dDate, 120), '') as order_date"), // 入库日期
                DB::raw("ISNULL(w.cWhName, '') as warehouse_name"),       // 仓库
                DB::raw("ISNULL(a.cOrderCode, '') as po_number"),         // 订单号
                DB::raw("ISNULL(a.cARVCode, '') as arrival_number"),      // 到货单号
                DB::raw("ISNULL(p.cPersonName, '') as person_name"),      // 业务员
                DB::raw("ISNULL(c.cVenName, '') as supplier_title"),      // 供货单位
                DB::raw("ISNULL(d.cDepName, '') as department_name"),     // 部门
                DB::raw("ISNULL(CONVERT(varchar(10), a.dARVDate, 120), '') as arrival_date"), // 到货日期
                DB::raw("ISNULL(a.cBusType, '') as business_type"),       // 业务类型
                DB::raw("ISNULL(pt.cPTName, '') as purchase_type"),       // 采购类型
                DB::raw("ISNULL(rs.cRdName, '') as rd_style_name"),       // 入库类别
                DB::raw("ISNULL(CONVERT(varchar(10), a.dVeriDate, 120), '') as audit_date"), // 审核日期
                DB::raw("ISNULL(a.cMemo, '') as remark"),                 // 备注
                DB::raw("ISNULL(a.cMaker, '') as crt_name")               // 制单人
            )
            ->orderBy('a.ID','desc');

        $list = $this->limit($model,'',$data);
        $list = $this->fillAll($list, U8State::type_three, $user['login_type']);

        return [true , $list];
    }

    public function purchaseInOrderDetail($data, $user){
        if(empty($data['order_number'])) return [false, '入库单号不能为空'];

        $order = $this->databaseService->table('RdRecord01 as a')
            ->leftJoin('Vendor as c', 'c.cVenCode', 'a.cVenCode')
            ->leftJoin('Warehouse as w', 'w.cWhCode', 'a.cWhCode')
            ->leftJoin('Department as d', 'd.cDepCode', 'a.cDepCode')
            ->leftJoin('Person as p', 'p.cPersonCode', 'a.cPersonCode')
            ->leftJoin('PurchaseType as pt', 'pt.cPTCode', 'a.cPTCode')
            ->leftJoin('Rd_Style as rs', 'rs.cRdCode', 'a.cRdCode')
            ->where('a.cCode', $data['order_number'])
            ->select(
                'a.ID',
                DB::raw("ISNULL(a.cCode, '') as order_number"),
                DB::raw("ISNULL(CONVERT(varchar(10), a.dDate, 120), '') as order_date"),
                DB::raw("ISNULL(w.cWhName, '') as warehouse_name"),
                DB::raw("ISNULL(a.cOrderCode, '') as po_number"),
                DB::raw("ISNULL(a.cARVCode, '') as arrival_number"),
                DB::raw("ISNULL(p.cPersonName, '') as person_name"),
                DB::raw("ISNULL(c.cVenName, '') as supplier_title"),
                DB::raw("ISNULL(d.cDepName, '') as department_name"),
                DB::raw("ISNULL(CONVERT(varchar(10), a.darvdate, 120), '') as arrival_date"),
                DB::raw("ISNULL(a.cBusType, '') as business_type"),
                DB::raw("ISNULL(pt.cPTName, '') as purchase_type"),
                DB::raw("ISNULL(rs.cRdName, '') as rd_style_name"),
                DB::raw("ISNULL(CONVERT(varchar(10), a.dVeriDate, 120), '') as audit_date"),
                DB::raw("ISNULL(a.cMemo, '') as remark"),
                DB::raw("ISNULL(a.cMaker, '') as crt_name")
            )
            ->first();

        if(empty($order)) return [false, '采购入库单不存在'];
        $order = (array) $order;
        $order = $this->fillDetail($order, U8State::type_three, $user['login_type']);

        $detail = $this->databaseService->table('rdrecords01 as a')
            ->leftJoin('Inventory as b', 'b.cInvCode', 'a.cInvCode')
            ->leftJoin('ComputationUnit as c', 'c.cComunitCode', 'b.cComUnitCode')
            ->where('a.ID', $order['ID'])
            ->select(
                DB::raw("ISNULL(b.cInvCode, '') as product_code"),    // 存货编码
                DB::raw("ISNULL(b.cInvName, '') as product_title"),   // 存货名称
                DB::raw("ISNULL(b.cInvStd, '') as product_std"),      // 规格型号
                DB::raw("ISNULL(c.cComUnitName, '') as unit_title"),  // 主计量单位
                DB::raw("ISNULL(LTRIM(STR(a.iQuantity, 20, 2)), '0.00') as quantity"), // 数量
                DB::raw("ISNULL(LTRIM(STR(a.iUnitCost, 20, 4)), '0.0000') as unit_price"), // 本币单价
                DB::raw("ISNULL(LTRIM(STR(a.iPrice, 20, 2)), '0.00') as amount"), // 本币金额
                DB::raw("ISNULL(LTRIM(STR(a.iTax, 20, 2)), '0.00') as tax"), // 税额
                DB::raw("ISNULL(LTRIM(STR(a.iTaxPrice, 20, 2)), '0.00') as nat_tax"), // 本币税额 (入库单本币税额通常等于原币税额)
                DB::raw("ISNULL(LTRIM(STR(a.iSum, 20, 2)), '0.00') as tax_amount") // 本币价税合计
            )
            ->get();

        $detailArr = $detail->map(function ($item) {
            return (array) $item;
        })->toArray();

        // 计算总金额（价税合计之和）
        $order['total_amount'] = number_format(array_sum(array_column($detailArr, 'tax_amount')), 2, '.', '');
        $order['detail'] = $detailArr;

        unset($order['ID']);

        return [true, $order];
    }

    public function inventoryDetail($data, $user){
        if(empty($data['order_number'])) return [false, '流水单号不能为空'];

        $order = Inventory::where('del_time',0)
            ->where('order_number', $data['order_number'])
            ->where('login_type', $user['login_type'])
            ->first();
        if(empty($order)) return [false, '存货不存在'];
        $order = $order->toArray() ;
        $order['crt_name'] = DDEmployee::where('login_type', $user['login_type'])->where('userid', $order['crt_id'])->value('name') ?? '';

        return [true, $order];
    }

    public function vendorDetail($data, $user){
        if(empty($data['order_number'])) return [false, '流水单号不能为空'];

        $order = Vendor::where('del_time',0)
            ->where('order_number', $data['order_number'])
            ->where('login_type', $user['login_type'])
            ->first();
        if(empty($order)) return [false, '供应商不存在'];
        $order = $order->toArray() ;
        $order['crt_name'] = DDEmployee::where('login_type', $user['login_type'])->where('userid', $order['crt_id'])->value('name') ?? '';

        return [true, $order];
    }

    public function getOrderDetails($data,$user){
        $type = $data['type'];
        [$success, $order] = [false, '异常错误'];

        if($type == 1){
            // 采购单
//            [$success, $order] = $this->purchaseOrderDetail($data,$user);
            [$success, $order] = (new U8XkyServerService())->purchaseOrderMyDetail($data,$user);
        }elseif ($type == 2){
            // 采购请购单
//            [$success, $order] = $this->purchaseRequisitionDetail($data,$user);
            [$success, $order] = (new U8XkyServerService())->purchaseRequisitionMyDetail($data,$user);
        }elseif ($type == 3){
            // 采购入库
//            [$success, $order] = $this->purchaseInOrderDetail($data,$user);
            [$success, $order] = (new U8XkyServerService())->purchaseOrderInMyDetail($data,$user);
        }elseif ($type == 4){
            // 存货
            [$success, $order] = $this->inventoryDetail($data,$user);
        }elseif ($type == 5){
            // 供应商
            [$success, $order] = $this->vendorDetail($data,$user);
        }

        return [$success, $order];
    }

    private function fillAll($list, $type, $login_type){
        if(empty($list['data'])) return $list;

        $map = U8State::where('del_time', 0)
            ->where('type', $type)
            ->where('login_type', $login_type)
            ->whereIn('order_number', array_column($list['data'], 'order_number'))
            ->pluck('state', 'order_number')
            ->toArray();

        foreach ($list['data'] as $key => $value){
            if(isset($map[$value->order_number])) {
                $m = $map[$value->order_number];
                $state = $m;
                $state_title = Record::state_name[$state];
            }else{
                $state = Record::state_minus_one;
                $state_title = Record::state_name[$state];
            }
            $list['data'][$key]->state = $state;
            $list['data'][$key]->state_title = $state_title;
        }

        return $list;
    }

    private function fillDetail($list, $type, $login_type)
    {
        if (empty($list)) return $list;

        // 1. 从 U8State 表中查询 state 和 result
        $u8Status = U8State::where('del_time', 0)
            ->where('type', $type)
            ->where('login_type', $login_type)
            ->where('order_number', $list['order_number'])
            ->select('state', 'result') // 明确查询需要的字段
            ->first();

        // 2. 逻辑判断
        if ($u8Status) {
            $state = $u8Status->state;
            $result = $u8Status->result; // 获取你想要的 result 字段
            $state_title = Record::state_name[$state] ?? '';
        } else {
            $state = Record::state_minus_one;
            $result = ''; // 或者根据业务给个默认值，如 ''
            $state_title = Record::state_name[$state] ?? '';
        }

        // 3. 注入到 list 中
        $list['state'] = $state;
        $list['state_title'] = $state_title;
        $list['result'] = $result; // 将 result 返回给前端或后续逻辑

        return $list;
    }

    public function stockList($data, $user)
    {
        try {
            $field_list = [];
            $employee = DDEmployee::where('userid', $user['userid'])->where('login_type', $user['login_type'])->first();
            $qx = $employee['qx'] ?? 0;
            if(empty($qx)) {
                $field_list = FieldData::where('userid', $user['userid'])
                    ->where('login_type', $user['login_type'])
                    ->where('type', FieldData::STATE_ZERO)
                    ->pluck('key')
                    ->all();
            }

            // 1. 构建基础查询：关联现存量表和存货档案表
            $query = $this->databaseService->table('CurrentStock as S')
                ->select([
                    'S.cWhCode',              // 仓库编码
                    'W.cWhName',              // 仓库名称
                    'S.cInvCode',             // 存货编码
                    'I.cInvName',             // 存货名称
                    'I.cInvStd',              // 规格型号
                    'I.cInvCCode',            // 分类编码

                    // --- 数量字段对齐你的结构 ---
                    'S.iQuantity',            // 结存数量 (账面现存量)
                    'S.fAvaQuantity',         // 可用数量
                    'S.fOutQuantity',         // 待发货数量 (待出)
                    'S.fInQuantity',          // 待入库数量 (待入)
                    'S.fStopQuantity',        // 冻结数量

                    // --- 批次与日期 ---
                    'S.cBatch',               // 批号
                    'S.dMdate',               // 生产日期
                    'S.dVDate',               // 失效日期
                ])
                ->join('Inventory as I', 'S.cInvCode', '=', 'I.cInvCode')
                ->leftJoin('Warehouse as W', 'S.cWhCode', '=', 'W.cWhCode')
                ->leftJoin('InventoryClass as IC', 'I.cInvCCode', '=', 'IC.cInvCCode');

            // 2. 过滤条件：存货名称 (支持模糊查询)
            if (!empty($data['material_title'])) {
                $query->where('I.cInvName', 'like', '%' . $data['material_title'] . '%');
            }

            // 2. 过滤条件：存货编码 (支持模糊查询)
            if (!empty($data['material_code'])) {
                $query->where('S.cInvCode', 'like', '%' . $data['material_code'] . '%');
            }

            // 3. 过滤条件：存货分类 (支持左匹配，即选大类查出所有子类)
            if (!empty($data['category_code'])) {
                // U8 分类是级次结构，用 like '01%' 可以查出 01 开头的所有子类
                $query->where('I.cInvCCode', 'like', $data['category_code'] . '%');
            }

            // 6. 排序
            $query->orderBy('S.cInvCode', 'asc')->orderBy('S.cWhCode', 'asc');

            // 7. 调用你定义的分页方法
            $columns = ['*'];
            $result = $this->limit($query, $columns, $data);

            // 注意这里的 &$item，加了 & 符号才能直接修改原数组里的内容
            foreach ($result['data'] as &$item) {
                $numFields = ['iQuantity', 'fAvaQuantity', 'fOutQuantity', 'fInQuantity', 'fStopQuantity'];

                foreach ($numFields as $field) {
                    if (isset($item->$field)) {
                        $item->$field = (float)$item->$field;
                    }
                }

                $item->dMdate = $item->dMdate ? date('Y-m-d', strtotime($item->dMdate)) : '';
                $item->dVDate = $item->dVDate ? date('Y-m-d', strtotime($item->dVDate)) : '';

                // 脱敏处理
                if (!empty($field_list)) {
                    foreach ($field_list as $blackField) {
                        if (isset($item->$blackField)) {
                            $item->$blackField = '*****';
                        }
                    }
                }
            }
            unset($item); // 销毁引用

            return [true, $result];

        } catch (\Throwable $exception) {
            return [false, "查询库存失败: " . $exception->getMessage()];
        }
    }

    public function vendorU8List($data, $user)
    {
        // 1. 构建基础查询
        $query = $this->databaseService->table('Vendor as V')
            ->select([
                'V.cVenCode',          // 供应商编码
                'V.cVenName',          // 供应商名称
                'V.cVenAbbName',       // 供应商简称
                'V.cVCCode',           // 分类编码
                'VC.cVCName',         // 分类名称 (来自 VendorClass)
                'V.cVenAddress',       // 地址
                'V.cVenPhone',         // 电话
                'V.dVenDevDate',       // 发展日期
                'V.cCreatePerson',     // 创建人
                'V.bVenTax',           // 是否计税
                'V.iId'                // 内部ID
            ])
            // 关联供应商分类表获取分类名称
            ->leftJoin('VendorClass as VC', 'V.cVCCode', '=', 'VC.cVCCode');

        // 2. 增加搜索逻辑 (可选)
        if (!empty($data['keyword'])) {
            $keyword = $data['keyword'];
            $query->where(function($q) use ($keyword) {
                $q->where('V.cVenCode', 'like', "%{$keyword}%")
                    ->orWhere('V.cVenName', 'like', "%{$keyword}%")
                    ->orWhere('V.cVenAbbName', 'like', "%{$keyword}%");
            });
        }

        // 3. 排序 (默认按编码排序)
        $query->orderBy('V.cVenCode', 'ASC');

        // 4. 调用你定义的分页方法
        // 注意：limit 方法内部会执行 paginate 并将结果填充到 $data
        $columns = ['*']; // select 已经在上面定义过了，这里传 * 即可
        $result = $this->limit($query, $columns, $data);

        return [true, $result];
    }

    public function vendorClassTree($data, $user)
    {
        try {
            // 1. 获取所有供应商分类 (表名: VendorClass)
            $classes = $this->databaseService->table('VendorClass')
                ->select('cVCCode', 'cVCName', 'iVCGrade', 'bVCEnd') // 编码、名称、级次
                ->orderBy('cVCCode', 'asc')
                ->get();

            if ($classes->isEmpty()) {
                return [true, []];
            }

            // 2. 格式化数据，以编码为 Key
            $classList = [];
            foreach ($classes as $item) {
                $classList[$item->cVCCode] = [
                    'label'    => $item->cVCName,
                    'value'    => $item->cVCCode, // 前端通常需要 value 字段
                    'code'     => $item->cVCCode,
                    'grade'    => $item->iVCGrade,
                    'is_end'    => $item->bVCEnd,
                    'children' => []
                ];
            }

            // 3. 构建引用树
            $tree = [];
            foreach ($classList as $code => &$node) {
                // 获取父级编码
                $parentCode = $this->getParentCode($code);

                if ($parentCode === null || !isset($classList[$parentCode])) {
                    // 顶级节点
                    $tree[] = &$node;
                } else {
                    // 挂载到父节点
                    $classList[$parentCode]['children'][] = &$node;
                }
            }

            return [true, $tree];

        } catch (\Throwable $exception) {
            return [false, "获取供应商分类树失败: " . $exception->getMessage()];
        }
    }

    /**
     * 辅助函数：根据 U8 编码规则获取父级编码
     * U8 的级次通常存储在 GradeDef 表，但通用逻辑是截取末尾
     */
    private function getParentCode($code)
    {
        $len = strlen($code);
        if ($len <= 2) return null; // 假设第一级是2位，小于等于2位则无父级

        // 这里假设级次是 2-2-2-2 (最常见配置)
        // 实际生产中，如果级次不固定，建议查询 GradeDef 表
        return substr($code, 0, $len - 2);
    }

    //U8 存货分类树结构
    public function inventoryClassTree($data, $user)
    {
        try {
            // 1. 从数据库获取所有存货分类
            $classes = $this->databaseService->table('InventoryClass')
                ->select('cInvCCode', 'cInvCName', 'iInvCGrade', 'bInvCEnd')
                ->orderBy('cInvCCode', 'asc')
                ->get();

            if ($classes->isEmpty()) return [true, []];

            // 2. 将集合转换为数组并以编码作为 Key，方便查找
            $classList = [];
            foreach ($classes as $item) {
                $classList[$item->cInvCCode] = [
                    'label'    => $item->cInvCName,
                    'code'     => $item->cInvCCode,
                    'grade'    => $item->iInvCGrade,
                    'is_end'    => $item->bInvCEnd,
                    'children' => []
                ];
            }

            // 3. 构建树形结构
            $tree = [];
            foreach ($classList as $code => &$node) {
                // 获取当前分类的级次 (U8 逻辑通常根据编码长度判断父级)
                // 比如 0101 的父级是 01
                $parentCode = $this->getParentCode($code);

                if ($parentCode === null || !isset($classList[$parentCode])) {
                    // 如果没有父级编码，或者父级编码不在列表里，说明是顶级分类
                    $tree[] = &$node;
                } else {
                    // 将当前节点引用到父节点的 children 数组中
                    $classList[$parentCode]['children'][] = &$node;
                }
            }

            return [true, $tree];

        } catch (\Throwable $exception) {
            return [false, "获取分类树失败: " . $exception->getMessage()];
        }
    }

    //U8 计量单位组（带默认主计量单位）
    public function getUnitGroups($data, $user)
    {
        $list = $this->databaseService->select("
        SELECT 
            G.cGroupCode, 
            G.cGroupName, 
            G.iGroupType, 
            U.cComUnitCode, 
            U.cComUnitName,
            U.iNumber
        FROM ComputationGroup AS G
        OUTER APPLY (
            SELECT TOP 1 cComUnitCode, cComUnitName, iNumber
            FROM ComputationUnit
            WHERE cGroupCode = G.cGroupCode
            ORDER BY 
                bMainUnit DESC,         -- 1. 优先主计量
                iNumber ASC,            -- 2. 序号最小 (若 NULL 会排在最前)
                cComUnitCode ASC        -- 3. 编码最小
        ) AS U
    ");

        return [true, $list];
    }

    //U8 计量单位档案
    public function getComputationUnitList($data, $user)
    {
        $list = $this->databaseService->table('ComputationUnit as U')
            ->select(
                'U.cComUnitCode',   // 单位编码
                'U.cComUnitName',   // 单位名称
                'U.cGroupCode',     // 所属组编码
                'U.bMainUnit',      // 是否主单位
                'U.iNumber'         // 排序序号
            )
            ->orderBy('U.cGroupCode', 'ASC')
            ->orderBy('U.iNumber', 'ASC') // 按照你要求的 iNumber 排序
            ->get();

        return [true, $list];
    }

    // U8 采购类型
    public function getPurchaseTypeList($data, $user)
    {
        $list = $this->databaseService->table('PurchaseType as P')
            // 核心修改：左关联收发类别表 Rd_Style
            ->leftJoin('Rd_Style as R', 'R.cRdCode', '=', 'P.cRdCode')
            ->select(
                'P.cPTCode',     // 采购类型编码 (如: 01, 02)
                'P.cPTName',     // 采购类型名称 (如: 国内采购, 国外采购)
                'P.bDefault',    // 是否默认值
                // 核心修改：查出对应的默认收发类别编码和名称
                DB::raw("ISNULL(P.cRdCode, '') as rd_code"),
                DB::raw("ISNULL(R.cRdName, '') as rd_name")
            )
            ->orderBy('P.bDefault', 'DESC')
            ->get();

        return [true, $list];
    }

    // 存货档案
    public function inventoryU8List($data, $user)
    {
        // 获取前端传过来的查询参数
        $search_code = $data['code'] ?? ""; // 存货编码查询条件
        $search_name = $data['name'] ?? ""; // 存货名称查询条件

        $model = $this->databaseService->table('Inventory as i')
            // 核心修改：关联计量单位表获取单位名称
            ->leftJoin('ComputationUnit as u', 'u.cComUnitCode', 'i.cComUnitCode')
            // 当编码查询条件不为空时，进行模糊查询
            ->when(!empty($search_code), function ($query) use ($search_code) {
                return $query->where('i.cInvCode', 'LIKE', '%' . $search_code . '%');
            })
            // 当名称查询条件不为空时，进行模糊查询
            ->when(!empty($search_name), function ($query) use ($search_name) {
                return $query->where('i.cInvName', 'LIKE', '%' . $search_name . '%');
            })
            ->select(
                'i.cInvCode as code',     // 存货编码
                'i.cInvName as name',     // 存货名称
                'i.cInvStd as size',       // 规格型号
                'i.cComUnitCode as unit_code',     // 主计量单位编码
                'u.cComUnitName as unit',     // 核心修改：主计量单位名称 (如: 个、千克、箱)
                'i.iImpTaxRate as purchase_rate'   // 进项税率
            )
            ->orderBy('i.cInvCode', 'ASC');

        // 如果存货档案数据量非常大，建议这里配合分页使用，例如：
        $list = $this->limit($model, '', $data);

        return [true, $list];
    }

    // 仓库档案
    public function warehouseU8List($data, $user)
    {
        $list = $this->databaseService->table('Warehouse as W')
            ->select(
                'W.cWhCode as code',         // 仓库编码 (如: 01, 02)
                'W.cWhName as name'        // 仓库名称 (如: 原材料库, 半成品库)
            )
            ->orderBy('W.cWhCode', 'ASC')
            ->get();

        return [true, $list];
    }

    // 部门档案（树形结构返回）
    public function departmentU8List($data, $user)
    {
        // 1. 从 U8 数据库中获取所有启用的部门（按编码升序）
        $list = $this->databaseService->table('Department as D')
            ->select(
                'D.cDepCode as code',       // 部门编码
                'D.cDepName as name'        // 部门名称
            )
            ->orderBy('D.cDepCode', 'ASC')
            ->get();

        // 转换为标准数组
        $departments = json_decode(json_encode($list), true);

        if (empty($departments)) {
            return [true, []];
        }

        // 2. 构建树形结构
        $tree = $this->buildDepartmentTree($departments);

        return [true, $tree];
    }

    /**
     * 递归/双指针法构建部门树
     */
    private function buildDepartmentTree(array $departments)
    {
        $tree = [];
        $referMap = [];

        // 将所有节点放入临时 Map 中，方便通过 Code 快速查找，并初始化 children 容器
        foreach ($departments as $dep) {
            $dep['children'] = [];
            $referMap[$dep['code']] = $dep;
        }

        foreach ($referMap as $code => &$node) {
            // 寻找当前部门的父级编码
            $parentCode = $this->getDParentCode($code, $referMap);

            if ($parentCode && isset($referMap[$parentCode])) {
                // 如果找到了父级，就将当前节点挂载到父级的 children 下
                $referMap[$parentCode]['children'][] = &$node;
            } else {
                // 如果没有父级（代表是一级部门），直接放入根节点数组中
                $tree[] = &$node;
            }
        }

        return $tree;
    }

    private function getDParentCode($code, $referMap)
    {
        $len = strlen($code);
        if ($len <= 1) return null;

        // 从当前编码的倒数第一位开始向前裁剪，寻找第一个在 Map 中存在的上级编码
        for ($i = $len - 1; $i > 0; $i--) {
            $parentCandidate = substr($code, 0, $i);
            if (isset($referMap[$parentCandidate])) {
                return $parentCandidate;
            }
        }

        return null;
    }

    //获取u8请购单
    /**
     * 获取 U8 请购单列表（带存货明细 detail 字段）
     */
    public function purchaseRequisitionU8List($data, $user)
    {
        $order_date = $data['order_date'] ?? [];
        $order_date = array_filter($order_date);
        $code = $data['code'] ?? "";

        // 1. 构建主表查询 Model
        $model = $this->databaseService->table('PU_AppVouch as a')
            ->when(!empty($code), function ($query) use ($code) {
                return $query->where('a.cCode', 'LIKE', '%' . $code . '%');
            })
            ->when(!empty($order_date), function ($query) use ($order_date) {
                $start = date('Y-m-d 00:00:00.000', $order_date[0]);
                $end = date('Y-m-d 23:59:59.000', $order_date[1]);
                return $query->whereBetween('a.dDate', [$start, $end]);
            })
            ->where('a.iverifystateex', 2) // 已审核状态
            ->whereExists(function ($query) {
                $query->select(DB::raw(1))
                    ->from('PU_AppVouchs as b')
                    ->whereRaw('b.ID = a.ID')
                    ->whereRaw('ISNULL(b.fQuantity, 0) > ISNULL(b.iReceivedQTY, 0)');
            })
            ->select(
                'a.ID as id', // 必须查出主表 ID 用来后续关联子表
                DB::raw("ISNULL(a.cBusType, '') as business_type"),
                DB::raw("ISNULL(a.cCode, '') as order_number"),
                DB::raw("ISNULL(CONVERT(varchar(10), a.dDate, 120), '') as order_date"),
                DB::raw("ISNULL(a.cMaker, '') as crt_name")
            )
            ->orderBy('a.ID', 'desc');

        // 2. 获取主表分页列表数据
        $list = $this->limit($model, '', $data);

        // 如果列表为空，直接返回
        $items = $list['data'] ?? [];
        if (empty($items)) {
            return [true, $list];
        }

        // 3. 【核心结合】批量提取当前页所有主表的 ID
        $mainIds = array_column($items, 'id');

        // 4. 一次性批量查出这些主表对应的所有存货明细
        $details = $this->databaseService->table('PU_AppVouchs as b')
            ->leftJoin('Inventory as i', 'i.cInvCode', 'b.cInvCode')
            ->leftJoin('ComputationUnit as u', 'u.cComUnitCode', 'i.cComUnitCode')
            ->whereIn('b.ID', $mainIds) // 批量范围查询
            ->whereRaw('ISNULL(b.fQuantity, 0) > ISNULL(b.iReceivedQTY, 0)')
            ->select(
                'b.AutoID as detail_id',
                'b.ID as main_id', // 核心：用这个字段在内存里与主表归类映射
                'b.ivouchrowno as row_no',
                'b.cInvCode as code',
                'b.dRequirDate as plan_date',
                'b.fTaxPrice as price',
                'b.iPerTaxRate as rate',
                'i.cComUnitCode as unit_code',
                'u.cComUnitName as unit',
                DB::raw("ISNULL(i.cInvName, '') as name"),
                DB::raw("ISNULL(i.cInvStd, '') as size"),
                DB::raw("ISNULL(b.fQuantity, 0) as req_qty"),
                DB::raw("ISNULL(b.iReceivedQTY, 0) as order_qty"),
                DB::raw("(ISNULL(b.fQuantity, 0) - ISNULL(b.iReceivedQTY, 0)) as available_qty")
            )
            ->orderBy('b.ivouchrowno', 'asc')
            ->get();

        // 转化为普通纯数组
        $detailList = json_decode(json_encode($details), true);

        // 5. 按 main_id 将明细数据分门别类组装成 Map [main_id => [明细数组]]
        $detailMap = [];
        foreach ($detailList as $detail) {
            $detail['price']         = floatval($detail['price']);
            $detail['rate']          = floatval($detail['rate']);
            $detail['req_qty']         = floatval($detail['req_qty']);
            $detail['order_qty']        = floatval($detail['order_qty']);
            $detail['available_qty'] = floatval($detail['available_qty']);

            $detailMap[$detail['main_id']][] = $detail;
        }

        // 6. 将明细 Map 塞回主表列表项的 detail 字段中
        foreach ($items as &$item) {
            $item->detail = $detailMap[$item->id] ?? [];
        }

        // 重写回原列表结构中
        $list['data'] = $items;

        return [true, $list];
    }

    public function purchaseRequisitionU8List1($data, $user){
        $order_date = $data['order_date'] ?? [];
        $order_date = array_filter($order_date);
        $code = $data['code'] ?? "";

        $model = $this->databaseService->table('PU_AppVouch as a')
            ->when(! empty($code), function ($query) use($code){
                return $query->where('a.cCode', 'LIKE', '%'.$code.'%');
            })
            ->when(! empty($order_date), function ($query) use($order_date){
                $start = date('Y-m-d 00:00:00.000', $order_date[0]);
                $end = date('Y-m-d 23:59:59.000', $order_date[1]);
                return $query->whereBetween('a.dDate', [$start, $end]);
            })
        ->where('a.iverifystateex', 2)
        ->whereExists(function ($query) {
        $query->select(DB::raw(1))
            ->from('PU_AppVouchs as b')
            ->whereRaw('b.ID = a.ID') // 主外键关联
            ->whereRaw('ISNULL(b.fQuantity, 0) > ISNULL(b.iReceivedQTY, 0)');
        })
        ->select(
            'a.ID as id', // 必须把主表ID查出来，后续点击需要用来查子表
            DB::raw("ISNULL(a.cBusType, '') as business_type"),
            DB::raw("ISNULL(a.cCode, '') as order_number"),
            DB::raw("ISNULL(CONVERT(varchar(10), a.dDate, 120), '') as order_date"),
            DB::raw("ISNULL(a.cMaker, '') as crt_name")
        )
        ->orderBy('a.ID', 'desc');

        $list = $this->limit($model, '', $data);

        return [true, $list];
    }

    public function getRequisitionDetails($data){
        if (empty($data['id'])) return [false, '请购单主表ID不能为空'];

        $mainId = $data['id'];

        $details = $this->databaseService->table('PU_AppVouchs as b')
            ->leftJoin('Inventory as i', 'i.cInvCode', 'b.cInvCode') // 通常明细需要关联存货档案拿名称和规格
            ->leftJoin('ComputationUnit as u', 'u.cComUnitCode', 'i.cComUnitCode')
            ->where('b.ID', $mainId)
            ->whereRaw('ISNULL(b.fQuantity, 0) > ISNULL(b.iReceivedQTY, 0)')
            ->select(
                'b.AutoID as detail_id',          // 子表行单据唯一标识
                'b.ID as main_id',                // 主表ID
                'b.ivouchrowno as row_no',         // 行号
                'b.cInvCode as code',     // 存货编码
                'b.dRequirDate as plan_date',     // 需求日期 | 采购订单里的计划到货日期
                'b.fTaxPrice as price',     // 原币含税单价
                'b.iPerTaxRate as rate',     // 税率
                'i.cComUnitCode as unit_code',     // 主计量单位编码
                'u.cComUnitName as unit',     // 核心修改：主计量单位名称 (如: 个、千克、箱)
                DB::raw("ISNULL(i.cInvName, '') as name"), // 存货名称
                DB::raw("ISNULL(i.cInvStd, '') as size"),   // 规格型号
                DB::raw("ISNULL(b.fQuantity, 0) as req_qty"),       // 请购数量 (fQuantity)
                DB::raw("ISNULL(b.iReceivedQTY, 0) as order_qty"),  // 累计订货数量 (iReceivedQTY)
                DB::raw("(ISNULL(b.fQuantity, 0) - ISNULL(b.iReceivedQTY, 0)) as available_qty") // 剩余数量
            )
            ->orderBy('b.ivouchrowno', 'asc')
            ->get();

            // 转化为普通数组返回
            $list = json_decode(json_encode($details), true);

            return [true, $list];
    }

    public function getRequisitionDetailsByCode($code = [])
    {
        if (empty($code)) return [false, '请购单号不能为空'];

        // 2. 执行 U8 数据库查询
        $list = $this->databaseService->table('PU_AppVouchs as b')
            ->join('PU_AppVouch as a', 'a.ID', 'b.ID')
            ->leftJoin('Inventory as i', 'i.cInvCode', 'b.cInvCode')
            ->whereIn('a.cCode', $code)
            // 过滤：只查出【请购数量 > 累计订货数量】即还未订货完的明细
            ->whereRaw('ISNULL(b.fQuantity, 0) > ISNULL(b.iReceivedQTY, 0)')
            ->select(
                'a.cCode as cappcode',             // 顺便把主表单号也查出来，方便前端知道这一行属于哪张请购单
                'b.AutoID as detail_id',          // 子表行单据唯一标识 (iAppIds)
                'b.ID as main_id',                // 主表ID (cappcodeId)
                'b.ivouchrowno as row_no',         // 行号
                'b.cInvCode as material_code',     // 存货编码
                'b.dRequirDate as plan_date',     // 需求日期 -> 对应采购订单的计划到货日期
                DB::raw("ISNULL(i.cInvName, '') as material_name"), // 存货名称
                DB::raw("ISNULL(i.cInvStd, '') as material_std"),   // 规格型号
                DB::raw("ISNULL(b.fQuantity, 0) as req_qty"),       // 请购数量
                DB::raw("ISNULL(b.iReceivedQTY, 0) as order_qty"),  // 累计订货数量
                DB::raw("(ISNULL(b.fQuantity, 0) - ISNULL(b.iReceivedQTY, 0)) as available_qty") // 剩余可用数量
            )
            // 按照主表单号和子表行号升序排序，方便前端按单据分组展示
            ->orderBy('a.cCode', 'asc')
            ->orderBy('b.ivouchrowno', 'asc')
            ->get();

        // 3. 转化为普通数组返回
        $list = json_decode(json_encode($list), true);

        return [true, $list];
    }

    //获取u8采购订单
    /**
     * 获取 U8 采购订单列表（带存货明细 detail 字段）
     */
    public function purchaseOrderU8List($data, $user)
    {
        // 0 蓝单  1 红单
        if (!isset($data['bredvouch'])) return [false, '参照类型（蓝单|红单不能为空）'];
        $type = $data['bredvouch'];
        $order_date = $data['order_date'] ?? [];
        $order_date = array_filter($order_date);
        $code = $data['code'] ?? "";

        // 1. 构建主表查询 Model
        $model = $this->databaseService->table('PO_Pomain as a')
            ->leftJoin('Vendor as v', 'v.cVenCode', '=', 'a.cVenCode')
            ->when(!empty($code), function ($query) use ($code) {
                return $query->where('a.cPOID', 'LIKE', '%' . $code . '%');
            })
            ->when(!empty($order_date), function ($query) use ($order_date) {
                $start = date('Y-m-d 00:00:00.000', $order_date[0]);
                $end = date('Y-m-d 23:59:59.000', $order_date[1]);
                return $query->whereBetween('a.dPODate', [$start, $end]);
            })
            ->where('a.iverifystateex', 2) // 已审核
            ->whereExists(function ($query) use ($type) {
                if ($type == 0) {
                    $query->select(DB::raw(1))
                        ->from('PO_Podetails as b')
                        ->whereRaw('b.POID = a.POID')
                        ->whereRaw('ISNULL(b.iQuantity, 0) > ISNULL(b.iReceivedQTY, 0)');
                } elseif ($type == 1) {
                    $query->select(DB::raw(1))
                        ->from('PO_Podetails as b')
                        ->whereRaw('b.POID = a.POID')
                        ->whereRaw('ISNULL(b.iReceivedQTY, 0) > 0');
                }
            })
            ->select(
                'a.POID as id', // 对应明细表的 b.POID
                DB::raw("ISNULL(a.cBusType, '') as business_type"),
                DB::raw("ISNULL(a.cVenCode, '') as supply_code"),
                DB::raw("ISNULL(v.cVenName, '') as supply_name"),
                DB::raw("ISNULL(a.cexch_name, '') as cexch_name"),
                DB::raw("ISNULL(a.nflat, '') as nflat"),
                DB::raw("ISNULL(a.iDiscountTaxType, '') as iDiscountTaxType"),
                DB::raw("ISNULL(a.cPOID, '') as order_number"),
                DB::raw("ISNULL(CONVERT(varchar(10), a.dPODate, 120), '') as order_date"),
                DB::raw("ISNULL(a.cMaker, '') as crt_name")
            )
            ->orderBy('a.POID', 'desc');

        // 2. 获取主表分页列表数据
        $list = $this->limit($model, '', $data);

        $items = $list['data'] ?? [];
        if (empty($items)) {
            return [true, $list];
        }

        // 3. 批量提取当前页所有主表 POID
        $mainIds = array_column($items, 'id');

        // 4. 一次性批量查出这些主表对应的所有子表存货明细（继承蓝单/红单过滤逻辑）
        $details = $this->databaseService->table('PO_Podetails as b')
            ->leftJoin('Inventory as i', 'i.cInvCode', 'b.cInvCode')
            ->leftJoin('ComputationUnit as u', 'u.cComUnitCode', 'i.cComUnitCode')
            ->whereIn('b.POID', $mainIds) // 批量范围锁定
            ->when(isset($type), function ($query) use ($type) {
                if ($type == 0) {
                    return $query->whereRaw('ISNULL(b.iQuantity, 0) > ISNULL(b.iReceivedQTY, 0)');
                } elseif ($type == 1) {
                    return $query->whereRaw('ISNULL(b.iReceivedQTY, 0) > 0');
                }
            })
            ->select(
                'b.POID as main_id',                // 用于在内存中与主表 id 映射
                'b.ID as detail_id',                // 子表行单据唯一标识
                'b.ivouchrowno as row_no',
                'b.cInvCode as code',
                'b.iTaxPrice as price',
                'b.iPerTaxRate as rate',
                'i.cComUnitCode as unit_code',
                'i.bInvBatch as pici',               // 批次管理
                'i.bInvQuality as baozhiqi',         // 保质期管理
                'u.cComUnitName as unit',
                DB::raw("ISNULL(i.cInvName, '') as name"),
                DB::raw("ISNULL(i.cInvStd, '') as size"),
                DB::raw("ISNULL(b.iQuantity, 0) as i_qty"),
                DB::raw("ISNULL(b.iReceivedQTY, 0) as in_qty"),
                DB::raw($type == 0
                    ? "(ISNULL(b.iQuantity, 0) - ISNULL(b.iReceivedQTY, 0)) as available_qty"
                    : "ISNULL(b.iReceivedQTY, 0) as available_qty"
                )
            )
            ->orderBy('b.ivouchrowno', 'asc')
            ->get();

        // 转化为普通纯数组
        $detailList = json_decode(json_encode($details), true);

        // 5. 按 main_id 将明细数据归类到 Map 容器中
        $detailMap = [];
        foreach ($detailList as $detail) {
            $detail['price']         = floatval($detail['price']);
            $detail['rate']          = floatval($detail['rate']);
            $detail['i_qty']         = floatval($detail['i_qty']);
            $detail['in_qty']        = floatval($detail['in_qty']);
            $detail['available_qty'] = floatval($detail['available_qty']);
            $detailMap[$detail['main_id']][] = $detail;
        }

        // 6. 将明细 Map 塞回主表对应的列表项中
        foreach ($items as &$item) {
            $item->detail = $detailMap[$item->id] ?? [];
        }

        // 重写回原分页列表结构
        $list['data'] = $items;

        return [true, $list];
    }

    public function purchaseOrderU8List1($data, $user){
        // 0 蓝单  1 红单
        if(! isset($data['bredvouch'])) return [false, '参照类型（蓝单|红单不能为空）'];
        $type = $data['bredvouch'];
        $order_date = $data['order_date'] ?? [];
        $order_date = array_filter($order_date);
        $code = $data['code'] ?? "";

        $model = $this->databaseService->table('PO_Pomain as a')
            ->leftJoin('Vendor as v', 'v.cVenCode', '=', 'a.cVenCode')
            ->when(! empty($code), function ($query) use($code){
                return $query->where('a.cPOID', 'LIKE', '%'.$code.'%');
            })
            ->when(! empty($order_date), function ($query) use($order_date){
                $start = date('Y-m-d 00:00:00.000', $order_date[0]);
                $end = date('Y-m-d 23:59:59.000', $order_date[1]);
                return $query->whereBetween('a.dPODate', [$start, $end]);
            })
            ->where('a.iverifystateex', 2)
            ->whereExists(function ($query) use($type){
                if($type == 0){
                    $query->select(DB::raw(1))
                        ->from('PO_Podetails as b')
                        ->whereRaw('b.POID = a.POID')
                        ->whereRaw('ISNULL(b.iQuantity, 0) > ISNULL(b.iReceivedQTY, 0)');
                }elseif($type == 1){
                    $query->select(DB::raw(1))
                        ->from('PO_Podetails as b')
                        ->whereRaw('b.POID = a.POID')
                        ->whereRaw('ISNULL(b.iReceivedQTY, 0) > 0');
                }
            })
            ->select(
                'a.POID as id',
                DB::raw("ISNULL(a.cBusType, '') as business_type"),
                DB::raw("ISNULL(a.cVenCode, '') as supply_code"),
                DB::raw("ISNULL(v.cVenName, '') as supply_name"),
                DB::raw("ISNULL(a.cexch_name, '') as cexch_name"),
                DB::raw("ISNULL(a.nflat, '') as nflat"),
                DB::raw("ISNULL(a.iDiscountTaxType, '') as iDiscountTaxType"),
                DB::raw("ISNULL(a.cPOID, '') as order_number"),
                DB::raw("ISNULL(CONVERT(varchar(10), a.dPODate, 120), '') as order_date"),
                DB::raw("ISNULL(a.cMaker, '') as crt_name")
            )
            ->orderBy('a.ID', 'desc');

        $list = $this->limit($model, '', $data);

        return [true, $list];
    }
    // 供应商 业务类型 汇率 扣税类别 币种 相同才能一起选
    // 只能同时选择供应商、币种、汇率、扣税类别、业务类型、流程模式相同的行! 这是用友的提示

    // 获取u8采购订单明细
    public function getPurchaseOrderDetails($data){
        if (empty($data['id'])) return [false, '采购订单主表ID不能为空'];
        // 0 蓝单  1 红单
        if(! isset($data['bredvouch'])) return [false, '参照类型（蓝单|红单不能为空）'];
        $type = $data['bredvouch'];

        $mainId = $data['id'];
        $list = $this->databaseService->table('PO_Podetails as b')
            ->leftJoin('Inventory as i', 'i.cInvCode', 'b.cInvCode') // 通常明细需要关联存货档案拿名称和规格
            ->leftJoin('ComputationUnit as u', 'u.cComUnitCode', 'i.cComUnitCode')
            ->where('b.POID', $mainId)
            ->when(!empty($type), function ($query) use($type){
                if($type == 0){
                    return $query->whereRaw('ISNULL(b.iQuantity, 0) > ISNULL(b.iReceivedQTY, 0)');
                }elseif($type == 1){
                    return $query->whereRaw('ISNULL(b.iReceivedQTY, 0) > 0');
                }
            })
            ->select(
                'b.POID as main_id',                // 主表ID
                'b.ID as detail_id',          // 子表行单据唯一标识
                'b.ivouchrowno as row_no',         // 行号
                'b.cInvCode as code',     // 存货编码
                'b.iTaxPrice as price',     // 原币含税单价
                'b.iPerTaxRate as rate',     // 税率
                'i.cComUnitCode as unit_code',     // 主计量单位编码
                'i.bInvBatch as pici',     // 批次管理
                'i.bInvQuality as baozhiqi',     // 保质期管理
                'u.cComUnitName as unit',     // 核心修改：主计量单位名称 (如: 个、千克、箱)
                DB::raw("ISNULL(i.cInvName, '') as name"), // 存货名称
                DB::raw("ISNULL(i.cInvStd, '') as size"),   // 规格型号
                DB::raw("ISNULL(b.iQuantity, 0) as i_qty"),       // 采购订单数量 (iQuantity)
                DB::raw("ISNULL(b.iReceivedQTY, 0) as in_qty"),  // 累计入库数量 (iReceivedQTY)
                DB::raw("(ISNULL(b.iQuantity, 0) - ISNULL(b.iReceivedQTY, 0)) as available_qty") // 剩余可入库数量
            )
            ->orderBy('b.ivouchrowno', 'asc')
            ->get();

        // 转化为普通数组返回
//        $list = json_decode(json_encode($list), true);

        return [true, $list];
    }

    public function getPurchaseOrderDetailsByCode($code = [])
    {
        if (empty($code)) return [false, '采购订单号不能为空'];

        // 执行 U8 数据库查询
        $list = $this->databaseService->table('PO_Podetails as b')
            ->join('PO_Pomain as a', 'a.POID', '=', 'b.POID')
            ->leftJoin('Inventory as i', 'i.cInvCode', '=', 'b.cInvCode')
            ->whereIn('a.cPOID', $code)
            ->select(
                'a.cPOID as order_number',
                'b.ID as detail_id',
                'b.POID as main_id',
                'b.ivouchrowno as row_no',
                'b.cInvCode as cInvCode',
                DB::raw("ISNULL(i.cInvName, '') as name"),
                DB::raw("ISNULL(i.cInvStd, '') as size"),
                DB::raw("ISNULL(i.cComUnitCode, '') as unit"),
                DB::raw("ISNULL(b.iPerTaxRate, 0) as rate"),
                DB::raw("ISNULL(b.iUnitPrice, 0) as price"),
                DB::raw("ISNULL(b.iQuantity, 0) as po_qty"),
                DB::raw("ISNULL(b.iReceivedQTY, 0) as received_qty"),
                DB::raw("(ISNULL(b.iQuantity, 0) - ISNULL(b.iReceivedQTY, 0)) as available_qty"),

                // ==================== 新增：主表强控制联动校验字段 ====================
                DB::raw("ISNULL(a.cBusType, '') as cBusType"),                 // 业务类型
                DB::raw("ISNULL(a.cVenCode, '') as cVenCode"),                 // 供应商编码
                DB::raw("ISNULL(a.cexch_name, '人民币') as cexch_name"),        // 币种
                DB::raw("CAST(ISNULL(a.nflat, 1.0) AS DECIMAL(10,4)) as nflat"),// 汇率
                DB::raw("ISNULL(a.iDiscountTaxType, 0) as iDiscountTaxType")   // 扣税类别
            )
            ->orderBy('a.cPOID', 'asc')
            ->orderBy('b.ivouchrowno', 'asc')
            ->get();

        // 转化为普通数组返回
        $list = json_decode(json_encode($list), true);

        if (empty($list)) {
            return [false, '未查询到对应的采购订单明细数据'];
        }

        // ==================== 核心修改：多单合单严格一致性校验 ====================
        if (count($code) > 1) {
            // 1. 校验供应商
            $venCodes = array_unique(array_column($list, 'cVenCode'));
            if (count($venCodes) > 1) return [false, '只能同时选择【供应商】相同的采购订单进行合并入库！'];

            // 2. 校验业务类型
            $busTypes = array_unique(array_column($list, 'cBusType'));
            if (count($busTypes) > 1) return [false, '只能同时选择【业务类型】相同的采购订单进行合并入库！'];

            // 3. 校验币种
            $exchNames = array_unique(array_column($list, 'cexch_name'));
            if (count($exchNames) > 1) return [false, '只能同时选择【币种】相同的采购订单进行合并入库！'];

            // 4. 校验汇率 (转成 float 排除数据库浮点数末尾 0 的干扰)
            $nflats = array_unique(array_map('floatval', array_column($list, 'nflat')));
            if (count($nflats) > 1) return [false, '只能同时选择【汇率】相同的采购订单进行合并入库！'];

            // 5. 校验扣税类别
            $taxTypes = array_unique(array_column($list, 'iDiscountTaxType'));
            if (count($taxTypes) > 1) return [false, '只能同时选择【扣税类别】相同的采购订单进行合并入库！'];
        }
        // ====================================================================

        return [true, $list];
    }
}