xky/app/Service/U8XkyServerService.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 U8XkyServerService extends Service
{
    public function recordCommon($data, $user, $field = []){
       $state = $data['state'] ?? null;
       $model = U8State::where('del_time',0)
           ->when(! empty($state), function ($query) use($state){
               return $query->where('state', $state);
           })
           ->where('login_type', $user['login_type'])
           ->select('type', 'order_number', 'state')
           ->orderBy('id','desc');

        return $model;
    }

    public function recordList($data, $user){
        $model = $this->recordCommon($data, $user);
        $list = $this->limit($model,'',$data);
        $list = $this->fillRecordData($list,$user);

        return [true, $list];
    }

    public function fillRecordData($data, $user){
        if(empty($data['data'])) return $data;

        foreach ($data['data'] as $key => $value){
            $data['data'][$key]['type_title'] = FieldData::$name[$value['type']] ?? '';
        }

        return $data;
    }

    //新增存货到本地-----------------------------------------------

    public function inventoryAdd($data, $user){
        list($status, $msg) = $this->inventoryRule($data, $user);
        if(! $status) return [false, $msg];

        try {
            DB::beginTransaction();

            $inventoryData = [
                'order_number' => $this->generateBillNo([
                    'type' => Inventory::Order_type,
                    'login_type' => $user['login_type'],
                    'period' => date("Ym")
                ]),
                'code' => $data['code'] ?? "",
                'title' => $data['title'] ?? "",
                'size' => $data['size'] ?? "",
                'category_code' => $data['category_code'] ?? "",
                'category_code_title' => $data['category_code_title'] ?? "",
                'unit_group_type' => $data['unit_group_type'] ?? "",
                'unit_group_code' => $data['unit_group_code'] ?? "",
                'unit_group_code_title' => $data['unit_group_code_title'] ?? "",
                'unit_code' => $data['unit_code'] ?? "",
                'unit_code_title' => $data['unit_code_title'] ?? "",
                'vendor_code' => $data['vendor_code'] ?? "",
                'vendor_code_title' => $data['vendor_code_title'] ?? "",
                'bSale'         => $data['bSale'] ?? 0, //内销
                'bExpSale'        => $data['bExpSale'] ?? 0, //外销
                'bPurchase'     => $data['bPurchase'] ?? 0, // 采购
                'bSelf'         => $data['bSelf'] ?? 0, // 自制
                'bComsume'      => $data['bComsume'] ?? 0, // 生产耗材
                'iImpTaxRate' => $data['iImpTaxRate'] ?? 0, // 进项税率
                'iTaxRate'      => $data['iTaxRate'] ?? 0, // 销项税率
                'customs_change_rate'      => $data['customs_change_rate'] ?? 0,
                'login_type'      => $user['login_type'],
                'crt_id' => $user['userid'],
                'crt_time' => time(),
            ];

            Inventory::insert($inventoryData);

            DB::commit();
        } catch (\Throwable $exception) {
            DB::rollBack();
            return [false, "创建存货失败: " . $exception->getMessage()];
        }

        return [true, ''];
    }

    public function inventoryEdit($data, $user){
        list($status, $msg) = $this->inventoryRule($data, $user);
        if(! $status) return [false, $msg];

        try {
            DB::beginTransaction();

            $inventoryData = [
                'code' => $data['code'] ?? "",
                'title' => $data['title'] ?? "",
                'size' => $data['size'] ?? "",
                'category_code' => $data['category_code'] ?? "",
                'category_code_title' => $data['category_code_title'] ?? "",
                'unit_group_type' => $data['unit_group_type'] ?? "",
                'unit_group_code' => $data['unit_group_code'] ?? "",
                'unit_group_code_title' => $data['unit_group_code_title'] ?? "",
                'unit_code' => $data['unit_code'] ?? "",
                'unit_code_title' => $data['unit_code_title'] ?? "",
                'vendor_code' => $data['vendor_code'] ?? "",
                'vendor_code_title' => $data['vendor_code_title'] ?? "",
                'bSale'         => $data['bSale'] ?? 0, //内销
                'bExpSale'        => $data['bExpSale'] ?? 0, //外销
                'bPurchase'     => $data['bPurchase'] ?? 0, // 采购
                'bSelf'         => $data['bSelf'] ?? 0, // 自制
                'bComsume'      => $data['bComsume'] ?? 0, // 生产耗材
                'iImpTaxRate' => $data['iImpTaxRate'] ?? 0, // 进项税率
                'iTaxRate'      => $data['iTaxRate'] ?? 0, // 销项税率
                'customs_change_rate'      => $data['customs_change_rate'] ?? 0,
                'upd_time' => time(),
            ];

            Inventory::where('id', $data['id'])->update($inventoryData);

            DB::commit();
        } catch (\Throwable $exception) {
            DB::rollBack();
            return [false, "编辑存货失败: " . $exception->getMessage()];
        }

        return [true, ''];
    }

    public function inventoryRule(&$data, $user, $is_add = true){
        if(! $is_add && empty($data['id'])) return [false, 'ID不能为空'];
        $id = $data['id'] ?? 0;
        $code = $data['code'] ?? "";
        if(empty($data['title'])) return [false, '存货名称不能为空'];
        $title = $data['title'];
        if(empty($data['category_code'])) return [false, '存货分类编码不能为空'];
        $category_code = $data['category_code'];
        if(empty($data['category_code_title'])) return [false, '存货分类名不能为空'];
        $category_code_title = $data['category_code_title'];
        if(! isset($data['unit_group_type'])) return [false, '计量单位组类型不存在'];
        $unit_group_type = $data['unit_group_type'] ?? "";
        if(empty($data['unit_group_code'])) return [false, '计量单位组编码不能为空'];
        $unit_group_code = $data['unit_group_code'] ?? "";
        if(empty($data['unit_group_code_title'])) return [false, '计量单位组名不能为空'];
        $unit_group_code_title = $data['unit_group_code_title'] ?? "";
        if(empty($data['unit_code'])) return [false, '主计量单位编码不能为空'];
        $unit_code = $data['unit_code'] ?? "";
        if(empty($data['unit_code_title'])) return [false, '主计量单位名称不能为空'];
        $unit_code_title = $data['unit_code_title'] ?? "";
        if(! empty($data['vendor_code_title']) && empty($data['vendor_code'])) return [false, '生产企业编码不能为空'];
        $vendor_code = $data['vendor_code'] ?? "";
        $vendor_code_title = $data['vendor_code_title'] ?? "";
        $bSale = $data['bSale'] ?? 0;
        $bExpSale = $data['bExpSale'] ?? 0;
        $bPurchase = $data['bPurchase'] ?? 0;
        $bSelf = $data['bSelf'] ?? 0;
        $bComsume = $data['bComsume'] ?? 0;
        $iImpTaxRate = $data['iImpTaxRate'] ?? 0;
        $iTaxRate = $data['iTaxRate'] ?? 0;
        $res = $this->checkNumber($iImpTaxRate,2,'positive');
        if(! $res['valid']) return [false,'进项税率：' . $res['error']];
        $res = $this->checkNumber($iTaxRate,2,'positive');
        if(! $res['valid']) return [false,'销项税率：' . $res['error']];
        $customs_change_rate = $data['customs_change_rate'] ?? 0;
        $res = $this->checkNumber($customs_change_rate,2,'positive');
        if(! $res['valid']) return [false,'海关换算率：' . $res['error']];

        if(! empty($code)){
            $bool = Inventory::where('del_time', 0)
                ->where('login_type', $user['login_type'])
                ->when(! empty($id), function ($query) use($id){
                    return $query->where('id', '<>', $id);
                })
                ->where('code', $code)
                ->exists();
            if($bool) return [false, '存货编码已存在记录，新增失败'];
        }

        if(! $is_add){
            $order = Inventory::where('id', $data['id'])->first();
            if(empty($order)) return [false, '存货信息不存在或已被删除'];
            $order = $order->toArray();
            list($status, $msg) = $this->checkState(U8State::type_four, $user, $order['order_number']);
            if(! $status) return [false, $msg];
        }

        return [true, ''];
    }

    public function inventoryCommon($data, $user, $field = []){
        $type = U8State::type_four;

        $model = Inventory::from('inventory as i')
            ->leftJoin('u8_state as s', function ($join) use ($type, $user) {
                $join->on('i.order_number', '=', 's.order_number')
                    ->where('s.type', '=', $type)
                    ->where('s.login_type', '=', $user['login_type'])
                    ->where('s.del_time', '=', 0);
            })
            ->where('i.del_time', 0)
            ->where('i.login_type', $user['login_type'])
            ->select('i.*', DB::raw('IFNULL(s.state, -1) as state'))
            ->orderby('i.id', 'desc');

        // 2. 状态过滤逻辑
        if (isset($data['state']) && $data['state'] !== '') {
            $state_filter = $data['state'];
            if ($state_filter == -1) {
                // 未发起审批：在状态表里找不到记录
                $model->whereNull('s.state');
            } else {
                // 0:待审核 1:通过 2:驳回
                $model->where('s.state', $state_filter);
            }
        }

        if(! empty($data['id'])) $model->where('i.id', $data['id']);
        if(! empty($data['order_number'])) $model->where('i.order_number', $data['order_number']);

        if(! empty($data['crt_time'][0]) && ! empty($data['crt_time'][1])) {
            $return = $this->changeDateToTimeStampAboutRange($data['crt_time']);
            $model->where('i.crt_time', '>=', $return[0]);
            $model->where('i.crt_time', '<=', $return[1]);
        }

        return $model;
    }

    public function inventoryDetail($data, $user){
       if(empty($data['id']) && empty($data['order_number'])) return [false, 'ID或流水号不能都为空'];
       list($status, $msg) = $this->inventoryList($data, $user);
       $return = $msg['data'][0] ?? [];

       return [true, $return];
    }

    public function inventoryList($data, $user){
        $model = $this->inventoryCommon($data, $user);
        $list = $this->limit($model,'',$data);
        $list = $this->fillInventoryData($list,$user);

        return [true, $list];
    }

    public function fillInventoryData($data, $user){
        if(empty($data['data'])) return $data;

        $e_map = DDEmployee::whereIn('userid', array_unique(array_column($data['data'], 'crt_id')))
            ->where('login_type', $user['login_type'])
            ->pluck('name', 'userid')
            ->toArray();
        foreach ($data['data'] as $key => $value){
            $data['data'][$key]['bSale_title'] = $value['bSale'] ? '是' : '否';
            $data['data'][$key]['bExpSale_title'] = $value['bExpSale'] ? '是' : '否';
            $data['data'][$key]['bPurchase_title'] = $value['bPurchase'] ? '是' : '否';
            $data['data'][$key]['bSelf_title'] = $value['bSelf'] ? '是' : '否';
            $data['data'][$key]['bComsume_title'] = $value['bComsume'] ? '是' : '否';

            $data['data'][$key]['crt_name'] = $e_map[$value['crt_id']] ?? "";
            $data['data'][$key]['crt_time'] = $value['crt_time'] ? date('Y-m-d H:i:s',$value['crt_time']) : '';
            $data['data'][$key]['state_title'] = Record::state_name[$value['state']];
        }

        return $data;
    }

    public function inventoryDel($data, $user){
        if($this->isEmpty($data,'id')) return [false,'请选择数据！'];

        try {
            DB::beginTransaction();
            $time = time();

            $order = Inventory::where('id', $data['id'])->first();
            if(empty($order)) return [false, '存货信息不存在或已被删除'];
            $order = $order->toArray();
            list($status, $msg) = $this->checkState(U8State::type_five, $user, $order['order_number']);
            if(! $status) return [false, $msg];

            Inventory::where('del_time',0)
                ->where('id',$data['id'])
                ->update(['del_time' => $time]);

            DB::commit();
        }catch (\Exception $exception){
            DB::rollBack();
            return [false,$exception->getMessage()];
        }

        return [true, ''];
    }

    //新增存货到本地-----------------------------------------------

    private function checkState($type, $user, $order_number){
        $lastRecord = U8State::where('del_time', 0)
            ->where('type', $type)
            ->where('login_type', $user['login_type'])
            ->where('order_number', $order_number)
            ->first(['state']); // 只取状态字段
        if ($lastRecord) {
            $text_o = U8State::type_name[$type];
            // 如果状态是 0 (待审核) 或 1 (审核通过)，拦截
            if (in_array($lastRecord->state, [U8State::state_zero, U8State::state_one])) {
                $text = U8State::state_name[$lastRecord->state];
                return [false, $text_o . ' (' . $order_number . ') 处于' . $text . '，操作失败'];
            }
        }

        return [true, ''];
    }

    //新增供应商到本地-----------------------------------------------

    public function vendorAdd($data, $user){
        list($status, $msg) = $this->vendorRule($data, $user);
        if(! $status) return [false, $msg];

        try {
            DB::beginTransaction();

            $vendorData = [
                'order_number' => $this->generateBillNo([
                    'type' => Vendor::Order_type,
                    'login_type' => $user['login_type'],
                    'period' => date("Ym")
                ]),
                'code' => $data['code'] ?? "",
                'title' => $data['title'] ?? "",
                'easy_title' => $data['easy_title'] ?? "",
                'category_code' => $data['category_code'] ?? "",
                'category_code_title' => $data['category_code_title'] ?? "",
                'login_type' => $user['login_type'] ?? "",
                'crt_id' => $user['userid'],
                'crt_time' => time(),
            ];

            Vendor::insert($vendorData);

            DB::commit();
        } catch (\Throwable $exception) {
            DB::rollBack();
            return [false, "创建供应商失败: " . $exception->getMessage()];
        }

        return [true, ''];
    }

    public function vendorEdit($data, $user){
        list($status, $msg) = $this->vendorRule($data, $user);
        if(! $status) return [false, $msg];

        try {
            DB::beginTransaction();

            $vendorData = [
                'code' => $data['code'] ?? "",
                'title' => $data['title'] ?? "",
                'easy_title' => $data['easy_title'] ?? "",
                'category_code' => $data['category_code'] ?? "",
                'category_code_title' => $data['category_code_title'] ?? "",
                'upd_time' => time(),
            ];

            Vendor::where('id', $data['id'])->update($vendorData);

            DB::commit();
        } catch (\Throwable $exception) {
            DB::rollBack();
            return [false, "编辑供应商失败: " . $exception->getMessage()];
        }

        return [true, ''];
    }

    public function vendorRule(&$data, $user, $is_add = true){
        if(! $is_add && empty($data['id'])) return [false, 'ID不能为空'];
        $id = $data['id'] ?? 0;
        $code = $data['code'] ?? "";
        if(empty($data['title'])) return [false, '供应商全称不能为空'];
        $title = $data['title'];
        if(empty($data['easy_title'])) return [false, '供应商简称不能为空'];
        $easy_title = $data['easy_title'];
        if(empty($data['category_code'])) return [false, '供应商分类编码不能为空'];
        $category_code = $data['category_code'];
        if(empty($data['category_code_title'])) return [false, '供应商分类名不能为空'];
        $category_code_title = $data['category_code_title'];

        if(! empty($code)){
            $bool = Vendor::where('del_time', 0)
                ->when(! empty($id), function ($query) use($id){
                    return $query->where('id', '<>', $id);
                })
                ->where('code', $code)
                ->exists();
            if($bool) return [false, '供应商编码已存在记录，新增失败'];
        }

        if(! $is_add){
            $order = Vendor::where('id', $data['id'])->first();
            if(empty($order)) return [false, '供应商信息不存在或已被删除'];
            $order = $order->toArray();
            list($status, $msg) = $this->checkState(U8State::type_five, $user, $order['order_number']);
            if(! $status) return [false, $msg];
        }

        return [true, ''];
    }

    public function vendorCommon($data, $user, $field = []){
        $type = U8State::type_five;

        $model = Vendor::from('vendor as v')
            ->leftJoin('u8_state as s', function ($join) use ($type, $user) {
                $join->on('v.order_number', '=', 's.order_number')
                    ->where('s.type', '=', $type)
                    ->where('s.login_type', '=', $user['login_type'])
                    ->where('s.del_time', '=', 0);
            })
            ->where('v.del_time', 0)
            ->where('v.crt_id', $user['userid'])
            ->select('v.*', DB::raw('IFNULL(s.state, -1) as state'))
            ->orderby('v.id', 'desc');

        if (isset($data['state']) && $data['state'] !== '') {
            $state_filter = $data['state'];
            if ($state_filter == -1) {
                // 未发起审批：u8_states 表里没记录，s.id 就会是 NULL
                $model->whereNull('s.id');
            } else {
                // 0:待审核 1:通过 2:驳回
                $model->where('s.state', $state_filter);
            }
        }

        if(! empty($data['id'])) $model->where('v.id', $data['id']);
        if(! empty($data['order_number'])) $model->where('v.order_number', $data['order_number']);
        if(! empty($data['crt_time'][0]) && ! empty($data['crt_time'][1])) {
            $return = $this->changeDateToTimeStampAboutRange($data['crt_time']);
            $model->where('v.crt_time', '>=', $return[0]);
            $model->where('v.crt_time', '<=', $return[1]);
        }

        return $model;
    }

    public function vendorList($data, $user){
        $model = $this->vendorCommon($data, $user);
        $list = $this->limit($model,'',$data);
        $list = $this->fillVendorData($list,$user);

        return [true, $list];
    }

    public function fillVendorData($data, $user){
        if(empty($data['data'])) return $data;

        $e_map = DDEmployee::whereIn('userid', array_unique(array_column($data['data'], 'crt_id')))
            ->where('login_type', $user['login_type'])
            ->pluck('name', 'userid')
            ->toArray();
        foreach ($data['data'] as $key => $value){
            $data['data'][$key]['crt_name'] = $e_map[$value['crt_id']] ?? "";
            $data['data'][$key]['crt_time'] = $value['crt_time'] ? date('Y-m-d H:i:s',$value['crt_time']) : '';
            $data['data'][$key]['state_title'] = Record::state_name[$value['state']];
        }

        return $data;
    }

    public function vendorDetail($data, $user){
        if(empty($data['id']) && empty($data['order_number'])) return [false, 'ID或流水号不能都为空'];
        list($status, $msg) = $this->vendorList($data, $user);
        $return = $msg['data'][0] ?? [];

        return [true, $return];
    }

    public function vendorDel($data, $user){
        if($this->isEmpty($data,'id')) return [false,'请选择数据！'];

        try {
            DB::beginTransaction();
            $time = time();

            $order = Vendor::where('id', $data['id'])->first();
            if(empty($order)) return [false, '供应商信息不存在或已被删除'];
            $order = $order->toArray();
            list($status, $msg) = $this->checkState(U8State::type_five, $user, $order['order_number']);
            if(! $status) return [false, $msg];

            Vendor::where('del_time',0)
                ->where('id',$data['id'])
                ->update(['del_time' => $time]);

            DB::commit();
        }catch (\Exception $exception){
            DB::rollBack();
            return [false,$exception->getMessage()];
        }

        return [true, ''];
    }

    public function filedCommon($data, $user, $field = []){
        $model = FieldData::select([
                'userid',
                'type',
                // 使用 GROUP_CONCAT 将 title 合并，并起别名为 titles
                DB::raw('GROUP_CONCAT(title SEPARATOR ",") as titles')
            ])
            ->where('login_type', $user['login_type'])
            ->groupBy('userid', 'type')
            ->orderBy('type', 'desc'); // 或者根据你的需求排序

        return $model;
    }

    public function fieldList($data, $user){
        $model = $this->filedCommon($data, $user);
        $list = $this->limit($model,'',$data);
        $list = $this->fillFieldData($list,$user);

        return [true, $list];
    }

    public function fillFieldData($data, $user){
        if(empty($data['data'])) return $data;

        $map = DDEmployee::whereIn('userid', array_unique(array_column($data['data'],'userid')))
            ->pluck('name', 'userid')
            ->toArray();
        foreach ($data['data'] as $key => $value){
            $data['data'][$key]['crt_time'] = $value['crt_time'] ? date('Y-m-d H:i:s',$value['crt_time']) : '';
            $data['data'][$key]['user_title'] = $map[$value['userid']] ?? '';
            $data['data'][$key]['type_title'] = FieldData::$name[$value['type']] ?? '';
        }

        return $data;
    }

    public function fieldDetail($data, $user){
        if(! isset($data['type'])) return [false, 'type不能为空'];
        if(! isset($data['userid'])) return [false, '人员id不能为空'];

        return [true, FieldData::where('type', $data['type'])
            ->where('login_type', $user['login_type'])
            ->where('userid', $data['userid'])
            ->get()->toArray()];
    }

    public function setField($data, $user){
        if(empty($user['qx'])) return [false, '权限不足，设置失败'];
        if(empty($data['userid'])) return [false, '人员ID不能为空'];
        if(! isset($data['field'])) return [false, '字段列不存在'];

        $insert = [];
        foreach ($data['field'] as $value){
            $insert[] = [
                'userid' => $data['userid'],
                'key' => $value['key'],
                'title' => $value['title'],
                'login_type' => $user['login_type']
            ];
        }

        try {

            FieldData::where('userid', $data['userid'])->where('login_type', $user['login_type'])->delete();

            if(! empty($insert)) FieldData::insert($insert);
        } catch (\Throwable $exception) {
            return [false, "异常: " . $exception->getMessage()];
        }

        return [true, ''];
    }

    public function ddEmployeeList($data, $user){
        $model = $this->ddEmployeeCommon($data, $user);
        $list = $this->limit($model,'',$data);
        $list = $this->fillDDEmployeeData($list,$user);

        return [true, $list];
    }

    public function ddEmployeeCommon($data,$user, $field = []){
        $model = DDEmployee::where('del_time',0)
            ->where('login_type', $user['login_type'])
            ->orderby('id', 'desc');

        if(! empty($data['id'])) $model->where('id', $data['id']);
        if(! empty($data['crt_time'][0]) && ! empty($data['crt_time'][1])) {
            $return = $this->changeDateToTimeStampAboutRange($data['crt_time']);
            $model->where('crt_time','>=',$return[0]);
            $model->where('crt_time','<=',$return[1]);
        }

        return $model;
    }

    public function fillDDEmployeeData($data, $user){
        if(empty($data['data'])) return $data;

        foreach ($data['data'] as $key => $value){
            $data['data'][$key]['crt_time'] = $value['crt_time'] ? date('Y-m-d H:i:s',$value['crt_time']) : '';
        }

        return $data;
    }

    //新增供应商到本地-----------------------------------------------

    //U8 存货库存列表
    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 = DB::connection('sqlsrv')
                ->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()];
        }
    }

    //U8 供应商列表
    public function vendorU8List($data, $user)
    {
        // 1. 构建基础查询
        $query = DB::connection('sqlsrv')
            ->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];
    }

    // U8 供应商分类树结构
    public function vendorClassTree($data, $user)
    {
        try {
            // 1. 获取所有供应商分类 (表名: VendorClass)
            $classes = DB::connection('sqlsrv')
                ->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 存货分类树结构
    public function inventoryClassTree($data, $user)
    {
        try {
            // 1. 从数据库获取所有存货分类
            $classes = DB::connection('sqlsrv')
                ->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 编码规则获取父级编码
     * 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 getUnitGroups($data, $user)
    {
        $list = DB::connection('sqlsrv')
            ->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 = DB::connection('sqlsrv')
            ->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];
    }
}