hcsl/app/Service/FyySqlServerService.php

<?php

namespace App\Service;

use Illuminate\Support\Facades\DB;

class FyySqlServerService extends Service
{
    public $db = null;

    public function __construct()
    {
        $domain_name = $_SERVER['HTTP_HOST'];
        if(! $this->db) $this->db = DB::connection('sqlsrvs');
    }

    private function fakeData(){
        $return = [
            [
                'out_order_no' => '001',
                'out_order_no_time' => 1685928995,
                'customer_no' => '201603012',
                'customer_name' => '巴大胡',
                'table_header_mark' => '表头备注',
                'product_no' => '产品编码001',
                'product_title' => '产品名称001',
                'product_size' => '产品规格30c',
                'product_unit' => '产品单位米',
                'product_quantity' => 100,
                'product_quantity_on_hand' => 50,
                'technology_material' => '工艺材质',
                'technology_name' => '工艺名称：同命',
                'wood_name' => '木皮名称：梭梭树皮',
                'process_mark' => '加工备注',
                'table_body_mark' => '表体备注',
                'out_crt_man' => '外部人员一',
                'out_checker_man' => '外部人员二',
                'out_checker_time' => 1685928995,
            ],
            [
                'out_order_no' => '001',
                'out_order_no_time' => 1685928995,
                'customer_no' => '201603012',
                'customer_name' => '巴大胡',
                'table_header_mark' => '表头备注',
                'product_no' => '产品编码002',
                'product_title' => '产品名称002',
                'product_size' => '产品规格30d',
                'product_unit' => '产品单位米',
                'product_quantity' => 100,
                'product_quantity_on_hand' => 50,
                'technology_material' => '工艺材质',
                'technology_name' => '工艺名称：同命',
                'wood_name' => '木皮名称：梭梭树皮',
                'process_mark' => '加工备注',
                'table_body_mark' => '表体备注',
                'out_crt_man' => '外部人员一',
                'out_checker_man' => '外部人员二',
                'out_checker_time' => 1685928995,
            ],
            [
                'out_order_no' => '002',
                'out_order_no_time' => 1685928995,
                'customer_no' => '201603012',
                'customer_name' => '巴大胡',
                'table_header_mark' => '表头备注',
                'product_no' => '产品编码002',
                'product_title' => '产品名称002',
                'product_size' => '产品规格30d',
                'product_unit' => '产品单位米',
                'product_quantity' => 100,
                'product_quantity_on_hand' => 50,
                'technology_material' => '工艺材质',
                'technology_name' => '工艺名称：同命',
                'wood_name' => '木皮名称：梭梭树皮',
                'process_mark' => '加工备注',
                'table_body_mark' => '表体备注',
                'out_crt_man' => '外部人员一',
                'out_checker_man' => '外部人员二',
                'out_checker_time' => 1685928995,
            ],
        ];
        return $return;
    }

    //获取数据
    public function getDataFromSqlServer($data){
        if(empty($data['out_order_no_time'][0]) || empty($data['out_order_no_time'][1])) return [false,'制单日期不能为空！'];

        //查询产品主表副表数据
        date_default_timezone_set("PRC");
        $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]);
        $result = $this->db->table('SO_SOMain as a')
            ->leftJoin('SO_SODetails as b','b.cSOCode','a.cSOCode')
            ->whereBetween('a.dDate',[$start, $end])
            ->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 product_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')
            ->get()->toArray();
        if(empty($result)) return [false,'暂无数据，更新结束！'];

        //查询附带的一些信息（比较少）
        $product_no = array_column($result,'product_no');
        $messageOne = $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)
            ->select('a.cInvCode as product_no','a.cInvName as product_title','a.cInvStd as product_size','b.cComUnitName as product_unit')
            ->get()->toArray();
        $messageMap = array_column($messageOne,null,'product_no');

        //组织查询条件
        $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}' and a.cFree2 = '{$wood_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();

        $messageTwoTotal = [];
        if(! empty($messageTwo)){
            foreach ($messageTwo as $key => $value){
                $keys = $value->product_no . $value->technology_name . $value->wood_name;
                if(isset($messageTwoTotal[$keys])){
                    $messageTwoTotal[$keys] += $value->product_quantity_on_hand;
                }else{
                    $messageTwoTotal[$keys] = $value->product_quantity_on_hand;
                }

                $messageTwo[$key] = (array)$value;
            }
        }

        foreach ($result as $key => $value){
            $keys = $value->product_no . $value->technology_name . $value->wood_name;
            $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]->product_quantity_on_hand = $messageTwoTotal[$keys] ?? 0;
            $result[$key] = (array)$value;
        }

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