hcsl/app/Service/FyySqlServerService.php

<?php

namespace App\Service;

use App\Model\BoxDetail;
use App\Model\Employee;
use App\Model\ErrorTable;
use App\Model\Orders;
use App\Model\SaleOrdersProduct;
use App\Model\SalesFrom;
use Illuminate\Support\Facades\Config;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Redis;

class FyySqlServerService extends Service
{
    public $db = null;
    public $db2 = null;
    public $error = null;
    public $host = "192.168.0.157";//数据库外网域名
    public $host_api = '192.168.0.157';//用友接口外网域名
    public $port = 1433;
    public $database = "UFDATA_001_2024";
    public $url = "";
    public $sAccID = "(default)@001";
    public $sUserID = "0001";
    public $sPassword = "";

    public function __construct($user_id = [])
    {
        try {
            //用户信息校验
            if (empty($user_id['id'])) {
                $this->error = '恒成塑业数据库连接用户参数不能为空！';
                return;
            }

            //获取用友账号密码
            $emp = Employee::where('id', $user_id['id'])->select('sqlserver_account', 'sqlserver_password')->first();
            if (empty($emp) || empty($emp->sqlserver_account)) {
                $this->error = '恒成塑业连接构造失败,未找到账号对应的用友账号信息';
                return;
            }

            $this->host = env('Yongyou_database_ip');
            $this->port = env('Yongyou_database_port');
            $this->host_api = env('Yongyou_api_ip');
            $this->database = env('Yongyou_database');

            //映射ip是否通畅
            $bool = $this->isHostReachable($this->host);
            if(! $bool) {
                $this->error = $this->host . "连接不可达,请稍后重新操作！";
                return;
            }

            //用友接口统一登录账号密码
            $this->sUserID = $emp->sqlserver_account ?? '';
            $this->sPassword = $emp->sqlserver_password ?? '';
            $this->url = $this->host_api . "/U8Sys/U8API";

            $this->createConnection();
        } catch (\Throwable $e) {
            $this->error = $e->getMessage();
        }
    }

    private function createConnection()
    {
        // 主数据库连接
        $mainConnName = 'sqlsrv_main_' . uniqid();
        $mainConfig = [
            'driver' => 'sqlsrv',
            'host' => $this->host,
            'port' => $this->port,
            'database' => $this->database,
            'username' => env('SQLSRV_USERNAME'),
            'password' => env('SQLSRV_PASSWORD'),
            'options' => [
//                \PDO::ATTR_TIMEOUT => 30, // 查询超时30秒
                \PDO::SQLSRV_ATTR_QUERY_TIMEOUT => 30, // SQL Server专用超时
                \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
//                \PDO::ATTR_PERSISTENT => false, // 重要：禁用持久连接
            ],
        ];

        Config::set("database.connections.{$mainConnName}", $mainConfig);
        $this->db = DB::connection($mainConnName);

        // 测试连接有效性
        $this->validateConnection($this->db);
    }

    private function validateConnection($connection)
    {
        try {
            $pdo = $connection->getPdo();
            $stmt = $pdo->prepare("SELECT 1 AS connection_test");
            $stmt->execute();
            $result = $stmt->fetch(\PDO::FETCH_ASSOC);

            if (empty($result) || $result['connection_test'] != 1) {
                $this->error = "数据库连接失败";
            }
        } catch (\Throwable $e) {
            $this->error = "数据库连接验证失败: " . $e->getMessage();
        }
    }

    public function __destruct()
    {
        // 主动关闭连接
        $this->safeDisconnect($this->db);
    }

    private function safeDisconnect(&$connection)
    {
        try {
            if ($connection instanceof \Illuminate\Database\Connection) {
                // 物理断开连接
                $connection->disconnect();

                // 清除连接引用
                $connection = null;
//                Log::channel('sendData')->info('动作', ["param" => "执行了析构"]);
            }
        } catch (\Throwable $e) {
            // 静默处理断开错误
            Log::channel('sendData')->info('错误', ["param" => $e->getMessage()]);
        }
    }

    public function is_same_month($timestamp1, $timestamp2)
    {
        // 格式化时间戳为年份和月份
        $year1 = date('Y', $timestamp1);
        $month1 = date('m', $timestamp1);

        $year2 = date('Y', $timestamp2);
        $month2 = date('m', $timestamp2);

        if ($year1 === $year2 && $month1 === $month2) {
            return true;
        } else {
            return false;
        }
    }

    //获取数据（点击引入）
    public function getDataFromSqlServer($data)
    {
        if (!empty($this->error)) return [false, $this->error, ''];

        if (empty($data['out_order_no_time'][0]) || empty($data['out_order_no_time'][1])) return [false, '制单日期不能为空！', ''];
        $bool = $this->is_same_month($data['out_order_no_time'][0], $data['out_order_no_time'][1]);
        if (!$bool) return [false, '制单日期必须同月！', ''];

        //查询产品主表副表数据

        $start = date('Y-m-d H:i:s.000', $data['out_order_no_time'][0]);
        $end = date('Y-m-d H:i:s.000', $data['out_order_no_time'][1]);
        $model = $this->db->table('SO_SOMain as a')
            ->leftJoin('SO_SODetails as b', 'b.cSOCode', 'a.cSOCode')
            ->whereBetween('a.dDate', [$start, $end])
            ->whereNotNull('a.cVerifier')
            ->select('a.cSOCode as out_order_no', 'a.dDate as out_order_no_time', 'a.cCusCode as customer_no', 'a.cCusName as customer_name', 'a.cMemo as table_header_mark', 'a.cMaker as out_crt_man', 'a.cVerifier as out_checker_man', 'a.dverifydate as out_checker_time', 'b.cInvCode as product_no', 'b.iQuantity as order_quantity', 'b.cDefine28 as technology_material', 'b.cFree1 as technology_name', 'b.cFree2 as wood_name', 'b.cDefine30 as process_mark', 'b.cMemo as table_body_mark', 'b.iTaxUnitPrice as price','b.dPreDate as pre_shipment_time');

        if (!empty($data['out_order_no'])) $model->where('a.cSOCode', 'LIKE', '%' . $data['out_order_no'] . '%');
        if (!empty($data['customer_no'])) $model->where('a.cCusCode', 'LIKE', '%' . $data['customer_no'] . '%');
        if (!empty($data['customer_name'])) $model->where('a.cCusName', 'LIKE', '%' . $data['customer_name'] . '%');
        if (!empty($data['table_header_mark'])) $model->where('a.cMemo', 'LIKE', '%' . $data['table_header_mark'] . '%');
        if (!empty($data['out_crt_man'])) $model->where('a.cMaker', 'LIKE', '%' . $data['out_crt_man'] . '%');
        if (!empty($data['out_checker_man'])) $model->where('a.cVerifier', 'LIKE', '%' . $data['out_checker_man'] . '%');
        if (!empty($data['out_checker_time'][0]) && !empty($data['out_checker_time'][1])) {
            $start1 = date('Y-m-d H:i:s.000', $data['out_checker_time'][0]);
            $end1 = date('Y-m-d H:i:s.000', $data['out_checker_time'][1]);
            $model->whereBetween('a.dverifydate', [$start1, $end1]);
        }
        if (!empty($data['pre_shipment_time'][0]) && !empty($data['pre_shipment_time'][1])) {
            $start1 = date('Y-m-d H:i:s.000', $data['pre_shipment_time'][0]);
            $end1 = date('Y-m-d H:i:s.000', $data['pre_shipment_time'][1]);
            $model->whereBetween('b.dPreDate', [$start1, $end1]);
        }

        $result = $model->get()->toArray();
        if (empty($result)) return [false, '暂无数据，更新结束！', ''];
        list($status, $msg) = $this->orderRule($result);
        if (empty($msg)) return [false, '暂无数据，更新结束！', ''];
        $result = $msg;

        //查询附带的一些信息（比较少）
        $product_no = array_filter(array_column($result, 'product_no'));
        $chunkSize = 1000; // 每个子集的大小
        $chunks = array_chunk($product_no, $chunkSize); // 将原始数组拆分成多个较小的子数组

        $results = []; // 存储查询结果的数组
        foreach ($chunks as $chunk) {
            $tmp = $this->db->table('Inventory as a')
                ->join('ComputationUnit as b', function ($join) {
                    $join->on('a.cGroupCode', '=', 'b.cGroupCode')
                        ->on('a.cComUnitCode', '=', 'b.cComUnitCode');
                }, null, null, 'left')
                ->whereIn('a.cInvCode', $chunk)
                ->select('a.cInvCode as product_no', 'a.cInvName as product_title', 'a.cInvStd as product_size', 'b.cComUnitName as product_unit')
                ->get()
                ->toArray();
            $results = array_merge($results, $tmp); // 将每个子集的结果合并到总结果数组中
        }
        $messageMap = array_column($results, null, 'product_no');
        unset($results);

        //现存量查询开始 ---组织查询条件
        $args = '';
        foreach ($result as $value) {
            $product = $value->product_no;
            $technology_name = $value->technology_name ?? '';
//            $wood_name = $value->wood_name ?? '';
            $args .= "(a.cInvCode = '{$product}' and a.cFree1 = '{$technology_name}') OR ";
        }
        $args = rtrim($args, 'OR ');

        $messageTwo = $this->db->table('CurrentStock as a')
            ->leftJoin('Warehouse as b', 'b.cWhCode', 'a.cWhCode')
            ->whereRaw("($args)")
            ->where('a.iQuantity', '>', 0)
            ->select('a.iQuantity as product_quantity_on_hand', 'a.cInvCode as product_no', 'a.cFree1 as technology_name', 'a.cFree2 as wood_name', 'b.cWhName as warehouse_name')
            ->get()->toArray();

        if (!empty($messageTwo)) {
            foreach ($messageTwo as $key => $value) {
                $messageTwo[$key] = (array)$value;
            }
        }
        //现存量查询结束

        foreach ($result as $key => $value) {
            $result[$key]->technology_material = $value->technology_material ?? '';
            $result[$key]->technology_name = $value->technology_name ?? '';
            $result[$key]->wood_name = $value->wood_name ?? '';
            $result[$key]->process_mark = $value->process_mark ?? '';
            $result[$key]->table_body_mark = $value->table_body_mark ?? '';
            $result[$key]->table_header_mark = $value->table_header_mark ?? '';

            $keys = $value->product_no . $value->technology_name . $value->wood_name;
            $result[$key]->out_order_no_time = $value->out_order_no_time ? strtotime($value->out_order_no_time) : 0;
            $result[$key]->out_checker_time = $value->out_checker_time ? strtotime($value->out_checker_time) : 0;
            $result[$key]->pre_shipment_time = $value->pre_shipment_time ? strtotime($value->pre_shipment_time) : 0;
            $result[$key]->product_title = $messageMap[$value->product_no]->product_title ?? '';
            $result[$key]->product_size = $messageMap[$value->product_no]->product_size ?? '';
            $result[$key]->product_unit = $messageMap[$value->product_no]->product_unit ?? '';
            $result[$key] = (array)$value;
        }

        return [true, $result, $messageTwo];
    }

    public function orderRule($data)
    {
        $result = Orders::where('del_time', 0)
            ->whereIn('out_order_no', array_column($data, 'out_order_no'))
            ->select('out_order_no')
            ->get()->toArray();
        $out_order_no = array_column($result, 'out_order_no');
        if (!empty($out_order_no)) {
            foreach ($data as $key => $value) {
                if (in_array($value->out_order_no, $out_order_no)) {
                    unset($data[$key]);
                }
            }
        }

        return [true, $data];
    }

    //获取数据（刷新现存量）
    public function getDataFromSqlServerForOnHand($data)
    {
        if (!empty($this->error)) return [false, $this->error, ''];

        if (empty($data['id'])) return [false, '数据不能为空！', ''];

        $product = SaleOrdersProduct::whereIn('id', $data['id'])
            ->select('product_no', 'technology_name')
            ->get()->toArray();

        //现存量查询开始 ---组织查询条件
        $args = '';
        foreach ($product as $value) {
            $args .= "(a.cInvCode = '{$value['product_no']}' and a.cFree1 = '{$value['technology_name']}') OR ";
        }
        $args = rtrim($args, 'OR ');

        $message = $this->db->table('CurrentStock as a')
            ->leftJoin('Warehouse as b', 'b.cWhCode', 'a.cWhCode')
            ->whereRaw("($args)")
            ->where('a.iQuantity', '>', 0)
            ->select('a.iQuantity as product_quantity_on_hand', 'a.cInvCode as product_no', 'a.cFree1 as technology_name', 'a.cFree2 as wood_name', 'b.cWhName as warehouse_name')
            ->get()->toArray();

        if (!empty($message)) {
            foreach ($message as $key => $value) {
                $message[$key] = (array)$value;
            }
        }
        //现存量查询结束

        return [true, $message, $product];
    }

    //获取用友账号的人名
    public function getYongyouName(){
        if (!empty($this->error)) return [false, $this->error, ''];

        $str = "";
        $model = $this->db->table('UA_User')
            ->where('cUser_Id',$this->sUserID)
            ->select('cUser_Name')
            ->first();
        if(! empty($model)) $str = $model->cUser_Name;
        return $str;
    }

    //产成品入库单保存接口以及审核
    public function U8Rdrecord10Save($data, $data_detail, $bredvouch = 0)
    {
        if (! empty($this->error)) return [false, $this->error];

        if ($bredvouch) {
            $cmemo = '来源:恒成塑业完工操作撤回';
        } else {
            $cmemo = '来源:恒成塑业包装操作 包装单号:' . $data['order_no'];
        }

        //数据
        $bodys = [];
        foreach ($data_detail as $value){
            $key = $value['ext_1'] . $value['ext_3'];
            if(! isset($bodys[$key])){
                $bodys[$key] = [
                    "cinvcode" => $value["ext_1"],
                    "cposition" => "",
                    "cbatch" => "",
                    "iquantity" => $value["num"],
                    "inum" => $value["num"],
                    "iunitcost" => 0,
                    "iprice" => 0,
                    "iinvexchrate" => 0,
                    "impoids" => "",
                    "cmocode" => "",
                    "imoseq" => "",
                    "cbmemo" => "",
                    "cfree1" => $value['ext_3'], //颜色
                    "cfree2" => "",
                    "cdefine28" => "",
                ];
            }else{
                $bodys[$key]['iquantity'] += $value['num'];
                $bodys[$key]['inum'] += $value['num'];
            }
        }
        $bodys = array_values($bodys);

        $post = [
            "password" => "cloud@123456",
            "entity" => "U8Rdrecord10Save",
            "login" => [
                "sAccID" => $this->sAccID,
                "sDate" => date("Y-m-d"),
                "sServer" => '127.0.0.1',
                "sUserID" => $this->sUserID,
                "sSerial" => "",
                "sPassword" => $this->sPassword
            ],
            "data" => [
                "ccode" => '',
                "ddate" => date("Y-m-d"),
                "cmaker" => $data['create_name'],
                "dnmaketime" => date("Y-m-d"),
                "IsExamine" => true,
                "chandler" => $data['create_name'],
                "dnverifytime" => date("Y-m-d"),
                "bredvouch" => $bredvouch,
                "cwhcode" => "002",
                "cdepcode" => "03",
                "crdcode" => "102", //生产入库
                "cmemo" => $cmemo,
                "cdefine10" => $data['ext_1'] ?? "", //客户名称
                "bodys" => $bodys
            ]
        ];

        Log::channel('apiLog')->info('产成品入库:源数据', ["param" => $post]);

        $return = $this->post_helper($this->url, json_encode($post), ['Content-Type:application/json'],70);

        Log::channel('apiLog')->info('产成品入库:返回结果', ["param" => $return]);

        if (empty($return)) return [false, '异常错误，请确认请求接口地址！'];
        return [$return['flag'], $return['msg']];
    }

    //销售出库单保存接口给以及审核
    public function U8Rdrecord32Save($data,$create_name, $bredvouch = 0)
    {
        if (!empty($this->error)) return [false, $this->error];

        if ($bredvouch) {
            $cmemo = '来源:恒成塑业发货出库操作（撤回）';
        } else {
            $cmemo = '来源:恒成塑业发货出库操作';
        }
        foreach ($data as $value) {
            $bodys_tmp = [];
            foreach ($value['product'] as $v){
                $bodys_tmp[] = [
                    "idlsid" => "",
                    "cdlcode" => $value['cdlcode_string'],
                    "dlrowno" => $v['line'],
                    "cbdlcode" => "",
                    "cinvcode" => $v['cinvcode'],
                    "cposition" => "",
                    "cbatch" => "",
                    "iquantity" => $v['iquantity'],
                    "inum" => 0,
                    "iinvexchrate" => 0,
                    "iunitcost" => 0,
                    "iprice" => 0,
                    "cbmemo" => "",
                    "cfree1" => $v['cfree1'],
                    "cfree2" => "",
                ];
            }
            $cmemo = $cmemo . '(发货单信息:' . $value['cdlcode_string'] . ')';
            $post_tmp = [
                "password" => "cloud@123456",
                "entity" => "U8Rdrecord32Save",
                "login" => [
                    "sAccID" => $this->sAccID,
                    "sDate" => date("Y-m-d"),
                    "sServer" => '127.0.0.1',
                    "sUserID" => $this->sUserID,
                    "sSerial" => "",
                    "sPassword" => $this->sPassword
                ],
                "data" => [
                    "ccode" => '',
                    "ddate" => date("Y-m-d"),
                    "cmaker" => $create_name,
                    "dnmaketime" => date("Y-m-d"),
                    "IsExamine" => true,
                    "chandler" => $create_name,
                    "dnverifytime" => date("Y-m-d"),
                    "bredvouch" => $bredvouch,
                    "cdepcode" => "03",
                    "ccuscode" => $value['customer_code'],
                    "crdcode" => '202',
                    "cmemo" => $cmemo,
                    "cwhcode" => "002",
                    "bodys" => $bodys_tmp,
                ]
            ];
        }

        Log::channel('apiLog')->info('销售出库单:源数据', ["param" => $post_tmp]);
        $return = $this->post_helper($this->url, json_encode($post_tmp), ['Content-Type:application/json'], 70);

        Log::channel('apiLog')->info('销售出库单:返回结果', ["param" => $return]);

        if (empty($return)) return [false, '异常错误，请确认请求接口地址！'];
        if (! $return['flag']) return [false, $return['msg']];

        return [true, ''];
    }

    public function getBoxData($data)
    {
        $boxData = BoxDetail::from('box_detail as a')
            ->leftJoin('sale_orders_product as b', 'b.id', 'a.top_id')
            ->where('a.del_time', 0)
            ->where('a.order_no', $data['order_number']) //包装单号
            ->select('a.num as iquantity', 'b.product_no as cinvcode', 'b.technology_name as cfree1', 'b.wood_name as cfree2', 'b.out_order_no as cSOCode');

        return $boxData;
    }

    //获取发货单数据 还没发的
    public function getDataFromDispatchList($data)
    {
        $model = $this->db->table('DispatchList as a')
            ->leftJoin('DispatchLists as b', 'b.DLID', 'a.DLID')
            ->leftJoin('Inventory as c', 'c.cInvCode', 'b.cInvCode')
            ->whereNotNull('a.cVerifier');
//            ->whereColumn('b.iQuantity', '>', 'b.fOutQuantity');

        //检索条件
        if (!empty($data['time'][0]) && !empty($data['time'][1])) {
            $model->where('a.dDate', '>=', $data['time'][0]);
            $model->where('a.dDate', '<=', $data['time'][1]);
        }
        if (!empty($data['order_no'])) $model->where('b.cSOcode', $data['order_no']);
        if (!empty($data['out_order_no'])) $model->where('b.cSOcode', $data['out_order_no']);

        $message = $model->select('a.cDLCode as cdlcode', 'a.DLID as id', 'a.cCusName as customer_name', 'b.cSOCode as csocode', 'a.cDepCode as cdepcode', 'a.cCusCode as cuscode', 'b.iDLsID as idlsid', 'b.cWhCode as cwhcode', 'b.cInvCode as cinvcode', 'b.cInvName as product_title', 'b.cFree1 as cfree1', 'b.cFree2 as cfree2', 'b.cPosition as cposition', 'b.cBatch as cbatch', 'b.iQuantity as iquantity', 'b.iNum as inum', 'b.iInvExchRate as iinvexchrate', 'b.fOutQuantity as out_quantity', 'b.iUnitPrice as iunitcost', 'b.iMoney as imoney', 'b.cDefine28 as technology_material', 'b.cDefine30 as process_mark', 'c.cInvStd as product_size')
            ->get()->toArray();

        if (!empty($message)) {
            foreach ($message as $key => $value) {
//                $message[$key]->iquantity = $value->iquantity - $value->out_quantity;
                $message[$key] = (array)$value;
            }
        }

        return $message;
    }

    public function post_helper($url, $data, $header = [], $timeout = 60)
    {
        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, $url);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($ch, CURLOPT_ENCODING, '');
        curl_setopt($ch, CURLOPT_POST, 1);
        curl_setopt($ch, CURLOPT_CUSTOMREQUEST, 'POST');
        curl_setopt($ch, CURLOPT_HTTPHEADER, $header);
        curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
        curl_setopt($ch, CURLOPT_TIMEOUT, $timeout);
        if (!is_null($data)) curl_setopt($ch, CURLOPT_POSTFIELDS, $data);
        $r = curl_exec($ch);
        curl_close($ch);
        return json_decode($r, true);
    }

    //获取发货单数据 还没发的 分页
    public function getDataFromDispatchListPage($data)
    {
        //检索条件
        if (empty($data['time'][0]) || empty($data['time'][1])) return [false, '时间区间不能为空！'];
        if(empty($data['page_size'])) $data['page_size'] = 20;
        if(empty($data['page_index'])) $data['page_index'] = 1;

            $model = $this->db->table('DispatchList as a')
            ->leftJoin('DispatchLists as b', 'b.DLID', 'a.DLID')
            ->leftJoin('Inventory as c', 'c.cInvCode', 'b.cInvCode')
            ->select('a.cDLCode as cdlcode', 'a.DLID as id', 'a.cCusName as customer_name', 'b.cSOCode as csocode', 'a.cDepCode as cdepcode', 'a.cCusCode as cuscode', 'a.dDate as date','b.irowno', 'b.iDLsID as idlsid', 'b.cWhCode as cwhcode', 'b.cInvCode as cinvcode', 'b.cInvName as product_title', 'b.cFree1 as cfree1', 'b.cFree2 as cfree2', 'b.cPosition as cposition', 'b.cBatch as cbatch', 'b.iQuantity as iquantity', 'b.iNum as inum', 'b.iInvExchRate as iinvexchrate', 'b.fOutQuantity as out_quantity', 'b.iUnitPrice as iunitcost', 'b.iMoney as imoney', 'b.cDefine28 as technology_material', 'b.cDefine30 as process_mark', 'c.cInvStd as product_size', DB::raw('(b.iQuantity - b.fOutQuantity) as quantity'), 'a.cMemo as table_header_mark', 'b.cMemo as table_body_mark')
            ->whereNotNull('a.cVerifier')
//            ->whereColumn('b.iQuantity', '>', 'b.fOutQuantity')
            ->where('a.dDate', '>=', $data['time'][0])
            ->where('a.dDate', '<=', $data['time'][1]);

        if (!empty($data['cdlcode'])) $model->where('a.cDLCode', 'Like', '%' . $data['cdlcode'] . '%');
        if (!empty($data['cinvcode'])) $model->where('b.cInvCode', 'Like', '%' . $data['cinvcode'] . '%');
        if (!empty($data['product_title'])) $model->where('b.cInvName', 'Like', '%' . $data['product_title'] . '%');
        if (!empty($data['product_size'])) $model->where('c.cInvStd', 'Like', '%' . $data['product_size'] . '%');
        if (!empty($data['technology_material'])) $model->where('b.cDefine28', 'Like', '%' . $data['technology_material'] . '%');
        if (!empty($data['cfree1'])) $model->where('b.cFree1', 'Like', '%' . $data['cfree1'] . '%');
        if (!empty($data['cfree2'])) $model->where('b.cFree2', 'Like', '%' . $data['cfree2'] . '%');
        if (!empty($data['process_mark'])) $model->where('b.cDefine30', 'Like', '%' . $data['process_mark'] . '%');

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

        if (! empty($list['data'])) {
            $product_no = array_unique(array_column($list['data'], 'cinvcode'));
            $messageMap = $this->db->table('Inventory as a')
                ->join('ComputationUnit as b', function ($join) {
                    $join->on('a.cGroupCode', '=', 'b.cGroupCode')
                        ->on('a.cComUnitCode', '=', 'b.cComUnitCode');
                }, null, null, 'left')
                ->whereIn('a.cInvCode', $product_no)
                ->pluck('b.cComUnitName as product_unit', 'a.cInvCode')
                ->toArray();
            $list['data'] = Collect($list['data'])->map(function ($object) {
                return (array)$object;
            })->toArray();

            $new_data = [];
            foreach ($list['data'] as $value) {
                $unit = $messageMap[$value['cinvcode']] ?? '';
                $iquantity = floatval($value['iquantity']);
                $out_quantity = floatval($value['out_quantity']);
                $quantity = floatval($value['quantity']);
                $inum = floatval($value['inum']);
                $pro = [
                    "cinvcode" => $value['cinvcode'],
                    "product_title" => $value['product_title'],
                    "cfree1" => $value['cfree1'],
                    "iquantity" => $iquantity,
                    "inum" => $inum,
                    "out_quantity" => $out_quantity,
                    "product_size" => $value['product_size'] ?? "",
                    "quantity" => $quantity,
                    "unit" => $unit,
                    "line" => $value['irowno'],
                ];

                if(isset($new_data[$value['cdlcode']])){
                    $new_data[$value['cdlcode']]['product'][] = $pro;
                }else{
                    $new_data[$value['cdlcode']] = [
                        "cdlcode" => $value['cdlcode'],
                        "csocode" => $value['csocode'],
                        "customer_code" => $value['cuscode'] ?? "",
                        "customer_name" => $value['customer_name'] ?? "",
                        "product" => [$pro]
                    ];
                }
            }

            $list['data'] = array_values($new_data);
        }

        return $list;
    }

    //获取发货单数据 做校验
    public function getDataFromDispatchListForCheck($data)
    {
        $model = $this->db->table('DispatchList as a')
            ->leftJoin('DispatchLists as b', 'b.DLID', 'a.DLID')
            ->select('a.cDLCode as cdlcode', 'a.cCusName as customer_name', 'b.cSOCode as csocode', 'a.cCusCode as cuscode','b.cInvCode as cinvcode', 'b.cInvName as product_title', 'b.cFree1 as cfree1','b.iQuantity as iquantity', 'b.iNum as inum', 'b.iInvExchRate as iinvexchrate', 'b.fOutQuantity as out_quantity', 'b.iUnitPrice as iunitcost', 'b.iMoney as imoney');

        if (!empty($data['cdlcode'])) $model->whereIn('a.cDLCode', $data['cdlcode']);
        //颜色
        if (!empty($data['cfree1'])) $model->where('b.cFree1', 'Like', '%' . $data['cfree1'] . '%');

        $list = $model->get()->toArray();

        if (! empty($list)) {
            $list = Collect($list)->map(function ($object) {
                return (array)$object;
            })->toArray();

            $new_data = [];
            foreach ($list as $value) {
                $iquantity = floatval($value['iquantity']);
                $out_quantity = floatval($value['out_quantity']);
                $inum = floatval($value['inum']);
                $pro = [
                    "cinvcode" => $value['cinvcode'],
                    "product_title" => $value['product_title'],
                    "cfree1" => $value['cfree1'],
                    "iquantity" => $iquantity,
                    "inum" => $inum,
                    "out_quantity" => $out_quantity,
                ];

                if(isset($new_data[$value['cdlcode']])){
                    $new_data[$value['cdlcode']]['product'][] = $pro;
                }else{
                    $new_data[$value['cdlcode']] = [
                        "cdlcode" => $value['cdlcode'],
                        "csocode" => $value['csocode'],
                        "customer_code" => $value['cuscode'] ?? "",
                        "customer_name" => $value['customer_name'] ?? "",
                        "product" => [$pro]
                    ];
                }
            }

            $list = $new_data;
        }

        return $list;
    }

    public function recordErrorTable($msg,$user,$data,$time,$type){
        // 连接到指定数据库连接
        ErrorTable::insert([
            'msg' => $msg,
            'data' => json_encode($data),
            'user_id' => $user['id'],
            'user_operation_time' => $time,
            'type' => $type,
            'order_no' => $data['order_no'] ?? ""
        ]);
    }

    public function getStorehouseDataFromSqlServer($data)
    {
        if (!empty($this->error)) return [false, $this->error, ''];

        $model = $this->db->table('Warehouse as a')
            ->select('cWhCode as code', 'cWhName as name');

        if (!empty($data['code'])) $model->where('cWhCode', 'LIKE', '%' . $data['code'] . '%');
        if (!empty($data['name'])) $model->where('cWhName', 'LIKE', '%' . $data['name'] . '%');

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

        return $list;
    }

    //获取产品的原材料
    public function getProductFromSqlServer($data){
        if (!empty($this->error)) return [false, $this->error, ''];

        $return = [];
        $result = $this->db->table('Inventory')
            ->whereIn('cInvCode', $data['product_no'])
            ->select('cInvCode as product_code','CINVDEFINE1 as placode', 'CINVDEFINE2 as paper')
            ->get()
            ->toArray();
        $product_code = [];
        foreach ($result as $value){
            if(! in_array($value->placode, $product_code) && $value->placode) $product_code[] = $value->placode;
            if(! in_array($value->paper, $product_code) && $value->paper) $product_code[] = $value->paper;
        }
        if(! empty($product_code)){
            $product_list = $this->db->table('Inventory as a')
                ->join('ComputationUnit as b', function ($join) {
                    $join->on('a.cGroupCode', '=', 'b.cGroupCode')
                        ->on('a.cComUnitCode', '=', 'b.cComUnitCode');
                }, null, null, 'left')
                ->whereIn('a.cInvCode', $product_code)
                ->select('a.cInvCode as product_no', 'a.cInvName as product_title', 'a.cInvStd as product_size', 'b.cComUnitName as product_unit')
                ->get()
                ->toArray();
            $map = array_column($product_list, null, 'product_no');
            foreach ($result as $value){
                $tmp = [];
                if(! empty($value->placode)) $tmp[] = (array)$map[$value->placode] ?? [];
                if(! empty($value->paper)) $tmp[] = (array)$map[$value->paper] ?? [];
                $return[$value->product_code] = $tmp;
            }
        }

        return $return;
    }

    //获取产品的包装材料
    public function getProductBzFromSqlServer($data){
        if (!empty($this->error)) return [false, $this->error, ''];

        $return = [];
        $result = $this->db->table('Inventory')
            ->whereIn('cInvCode', $data['product_no'])
            ->select('cInvCode as product_code','CINVDEFINE3 as bz')
            ->get()
            ->toArray();
        $product_code = [];
        foreach ($result as $value){
            if(! in_array($value->bz, $product_code) && $value->bz) $product_code[] = $value->bz;
        }
        if(! empty($product_code)){
            $product_list = $this->db->table('Inventory as a')
                ->join('ComputationUnit as b', function ($join) {
                    $join->on('a.cGroupCode', '=', 'b.cGroupCode')
                        ->on('a.cComUnitCode', '=', 'b.cComUnitCode');
                }, null, null, 'left')
                ->whereIn('a.cInvCode', $product_code)
                ->select('a.cInvCode as product_no', 'a.cInvName as product_title', 'a.cInvStd as product_size', 'b.cComUnitName as product_unit')
                ->get()
                ->toArray();
            $map = array_column($product_list, null, 'product_no');
            foreach ($result as $value){
                if(isset($map[$value->bz])){
                    $return[$value->product_code][] = (array)$map[$value->bz];
                }
            }
        }

        return $return;
    }

    //获取销售单客户来源
    public function getCustomerFromSqlServer1($data){
        if (! empty($this->error)) return [false, $this->error, ''];

        $result = $this->db->table('SO_SOMain')
            ->whereIn('cSOCode', $data['sale_order'])
            ->select('cCusCode as customer_no', DB::raw("count(cCusCode) as num"))
            ->groupBy('cCusCode')
            ->get()
            ->toArray();
        if(empty($result)) return [true, []];
        $map = [];
        foreach ($result as $value){
            $map[$value->customer_no] = $value->num;
        }

        $return = [];
        $customer = $this->db->table('Customer')
            ->whereIn('cCusCode', array_column($result,'customer_no'))
            ->select('cCusCode as customer_no', 'cDCCode as address')
            ->get()
            ->toArray();
        foreach ($customer as $value){
            if(empty($value->address)) continue;
            $num = $map[$value->customer_no];
            if(isset($return[$value->address])){
                $return[$value->address] += $num;
            }else{
                $return[$value->address] = $num;
            }
        }

        return [true, $return];
    }

    public function insertSaleOrderFrom1(){
        $time = time();
        $return = [];
        $address_map = config('address');
        foreach ($address_map as $value){
            $return[$value['value']] = 0;
        }

        try {
            Orders::where('del_time', 0)
                ->select('out_order_no')
                ->orderBy('id','desc')
                ->chunk(200, function ($records) use(&$return) {
                    $out_order_no = [];
                    foreach ($records as $record){
                        $out_order_no[] = $record->out_order_no;
                    }

                    $sqlServerModel = new FyySqlServerService(['id' => 1, 'zt' => '001']);
                    list($status,$msg) = $sqlServerModel->getCustomerFromSqlServer(['sale_order' => $out_order_no]);
                    if($status){
                        foreach ($return as $key => $value){
                            if(isset($msg[$key])){
                                $return[$key] += $msg[$key];
                            }
                        }
                    }

                    echo '更新中--------' . "\n";
                });

            $insert = [];
            foreach ($return as $key => $value){
                $insert[] = [
                    'code' => $key,
                    'num' => $value,
                    'crt_time' => $time
                ];
            }
            SalesFrom::where('del_time', 0)
                ->update(['del_time' => $time]);

            SalesFrom::insert($insert);

            echo '更新结束--------' . "\n";
        }catch (\Throwable $exception){
            echo $exception->getMessage() . "\n";
        }
    }

    //获取客户来源
    public function getCustomerFromSqlServer(){
        if (! empty($this->error)) return [false, $this->error, ''];

        $return = [];
        $customer = $this->db->table('Customer')
            ->select('cCusCode as customer_no', 'cDCCode as address')
            ->get()->toArray();

        foreach ($customer as $value){
            if(empty($value->address)) continue;
            if(isset($return[$value->address])){
                $return[$value->address] += 1;
            }else{
                $return[$value->address] = 1;
            }
        }

        return [true, $return];
    }

    public function insertSaleOrderFrom(){
        $time = time();
        $return = [];
        $address_map = config('address');
        foreach ($address_map as $value){
            $return[$value['value']] = 0;
        }

        try {
            $sqlServerModel = new FyySqlServerService(['id' => 1, 'zt' => '001']);
            list($status,$msg) = $sqlServerModel->getCustomerFromSqlServer();
            if($status){
                foreach ($return as $key => $value){
                    if(isset($msg[$key])){
                        $return[$key] += $msg[$key];
                    }
                }
            }

            echo '更新中--------' . "\n";

            $insert = [];
            foreach ($return as $key => $value){
                $insert[] = [
                    'code' => $key,
                    'num' => $value,
                    'crt_time' => $time
                ];
            }
            SalesFrom::where('del_time', 0)
                ->update(['del_time' => $time]);

            SalesFrom::insert($insert);

            echo '更新结束--------' . "\n";
        }catch (\Throwable $exception){
            echo $exception->getMessage() . "\n";
        }
    }
}