hcsl/app/Service/FyySqlServerService.php

<?php

namespace App\Service;

use App\Model\BoxDetail;
use App\Model\Orders;
use App\Model\SaleOrdersProduct;
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 $error = null;
    public $host = "s6j9560970.goho.co";//数据库外网域名
    public $host_api = '13083970779.gnway.cc:8000';//用友接口外网域名
    public $port = 29703;
    public $database = "UFDATA_999_2023";
    public $url = "";
    public $sAccID = "(default)@999";
    public $sUserID = "demo";
    public $sPassword = "";

    public function __construct()
    {
        $fyy_array = Redis::get('fyy_sql_server');
        if(! empty($fyy_array)) {
            $fyy_array = json_decode($fyy_array,true);
            if(! empty($fyy_array['sqlserver_host'])) $this->host = $fyy_array['sqlserver_host'];
            if(! empty($fyy_array['sqlserver_host_api'])) $this->host_api = $fyy_array['sqlserver_host_api'];
            if(! empty($fyy_array['sqlserver_port'])) $this->port = $fyy_array['sqlserver_port'];
            if(! empty($fyy_array['sqlserver_database'])) $this->database = $fyy_array['sqlserver_database'];
            if(! empty($fyy_array['sqlserver_zt'])) $this->sAccID = '(default)@' . $fyy_array['sqlserver_zt'];
            if(! empty($fyy_array['sqlserver_account'])) $this->sUserID = $fyy_array['sqlserver_account'];
            if(! empty($fyy_array['sqlserver_password'])) $this->sPassword = $fyy_array['sqlserver_password'];
        }
        $this->url = $this->host_api . "/U8Sys/U8API";

        if(! $this->db){
            $config = [
                'driver' => 'sqlsrv',
                'host' =>  $this->host,
                'port' => $this->port,
                'database' => $this->database,
                'username' => env('SQLSRV_USERNAME'),
                'password' => env('SQLSRV_PASSWORD'),
            ];

            // 进行数据库连接
            Config::set('database.connections.sqlsrvs', $config);

            try {
                $pdo = DB::connection('sqlsrvs')->getPdo();
                if ($pdo instanceof \PDO) {
                    // 连接成功的逻辑代码
                    $this->db = DB::connection('sqlsrvs');
                } else {
                    $this->error = '连接失败！';
                    return;
                }

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

    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' => '产品单位米',
                'order_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' => '产品单位米',
                'order_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' => '产品单位米',
                'order_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 is_same_month($timestamp1,$timestamp2){
        date_default_timezone_set("PRC");

        // 格式化时间戳为年份和月份
        $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,'制单日期必须同月！',''];

        //查询产品主表副表数据
        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]);
        $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');

        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]);
        }

        $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}' 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();

        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]->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','wood_name')
            ->get()->toArray();

        //现存量查询开始 ---组织查询条件
        $args = '';
        foreach ($product as $value){
            $args .= "(a.cInvCode = '{$value['product_no']}' and a.cFree1 = '{$value['technology_name']}' and a.cFree2 = '{$value['wood_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 U8Rdrecord10Save($data,$bredvouch = 0){
        if(! empty($this->error)) return [false,$this->error];
        date_default_timezone_set("PRC");

        if($bredvouch){
            $cmemo = '来源:福羊羊完工操作撤回';
        }else{
            $cmemo = '来源:福羊羊完工操作 派工单号:'. $data['dispatch_no'];
        }

        //数据
        $bodys[] = [
            "cinvcode"=> $data["product_no"],
            "cposition"=> "",
            "cbatch"=> "",
            "iquantity"=> $data["quantity"],
            "inum"=> $data["quantity"],
            "iunitcost"=> $data["price"] * 0.95,
            "iprice"=> $data["price"] * 0.95 * $data['quantity'],
            "iinvexchrate"=> "1.00",
            "impoids"=> "",
            "cmocode"=> "",
            "imoseq"=> "",
            "cbmemo"=> "",
            "cfree1"=> $data['technology_name'],
            "cfree2"=> $data['wood_name'],
            "cdefine28" => $data['technology_material'],
        ];
        $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"=>$this->sUserID,
                "dnmaketime"=> date("Y-m-d"),
                "IsExamine"=>true,
                "bredvouch"=> $bredvouch,
                "cwhcode"=>"02",
                "cdepcode"=>"06",
                "crdcode"=>"102", //生产入库
                "cmemo"=> $cmemo,
                "cdefine10" => $data['customer_name'], //客户名称
                "bodys"=>$bodys
            ]
        ];
        $return = $this->post_helper($this->url,json_encode($post), ['Content-Type:application/json']);

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

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

        $new = [];
        foreach ($data as $value){
            $keys = $value['id'] . $value['cwhcode'];
            $new[$keys][] = $value;
        }

        foreach ($new as $value){
            $main_tmp = $value[0];
            $bodys_tmp = [];
            foreach ($value as $val){
                $bodys_tmp[] = [
                    "idlsid"=>$val['idlsid'],
                    "cdlcode"=>"",
                    "dlrowno"=>"",
                    "cbdlcode"=>"",
                    "cinvcode"=>$val['cinvcode'],
                    "cposition"=>$val['cposition'] ?? "",
                    "cbatch"=>$val['cbatch'] ?? "",
                    "iquantity"=>$val['iquantity'],
                    "inum"=>$val['inum'],
                    "iinvexchrate"=> $val['iinvexchrate'] ?? 0,
                    "iunitcost"=>$val['iunitcost'] * 0.95,
                    "iprice"=>$val['iunitcost'] * 0.95 * $val['iquantity'],
                    "cbmemo"=>"",
                    "cfree1"=> $val['cfree1'],
                    "cfree2"=> $val['cfree2'],
                    "cdefine28" => $val['technology_material'],
                    "cdefine30" => $val['process_mark'],
                ];
            }
            $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"=>$this->sUserID,
                    "dnmaketime"=> date("Y-m-d"),
                    "IsExamine"=>true,
                    "bredvouch"=> $bredvouch,
                    "cwhcode"=>$main_tmp['cwhcode'],
                    "cdepcode"=>$main_tmp['cdepcode'],
                    "ccuscode"=>$main_tmp['cuscode'],
                    "crdcode"=>'202',
                    "cmemo"=> '',
                    "cdefine10" => $main_tmp['customer_name'], //客户名称
                    "bodys"=>$bodys_tmp,
                ]
            ];

            $return = $this->post_helper($this->url,json_encode($post_tmp), ['Content-Type:application/json']);
            file_put_contents('record.txt',json_encode($new). PHP_EOL .json_encode($post_tmp) . PHP_EOL,8);

            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('a.cSOcode',$data['order_no']);

        $message = $model->select('a.cDLCode as cdlcode','a.DLID as id','a.cDefine10 as customer_name','a.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 createOrderNumberAboutSCRK(){
        date_default_timezone_set("PRC");

        $current_month = date('m'); // 获取当前月份
        $date1 = date('y') . $current_month  ; // 格式 "2307"
        $date2 = date('Y') . $current_month; // 格式 "202307"

        $record = $this->db->table('VoucherHistory')
            ->where('CardNumber','0411')
            ->whereRaw("(cSeed = '{$date1}' or cSeed = '{$date2}')")
            ->select('cNumber')
            ->first();
        $record = (array)$record;
        if(empty($record)) return [false,'未找到产成品入库当月流水号信息！'];

        $tmp = $record['cNumber'] + 1;
        if(strlen($tmp) > 4) return [false,'产成品入库当月流水号超过四位数！'];

        $number = str_pad($tmp,4,'0',STR_PAD_LEFT);
        $number = $tmp . $number;
        $ccode = "SCRK" . $date2 . $number;

        return [true,$ccode];
    }

    //销售出库单单据号
    public function createOrderNumberAboutXC(){
        date_default_timezone_set("PRC");

        $current_month = date('m'); // 获取当前月份
        $date1 = date('y') . $current_month  ; // 格式 "2307"
        $date2 = date('Y') . $current_month; // 格式 "202307"

        $record = $this->db->table('VoucherHistory')
            ->where('CardNumber','0303')
            ->whereRaw("(cSeed = '{$date1}' or cSeed = '{$date2}')")
            ->select('cNumber')
            ->first();
        $record = (array)$record;
        if(empty($record)) return [false,'未找到销售出库单当月流水号信息！'];

        $tmp = $record['cNumber'] + 1;
        if(strlen($tmp) > 4) return [false,'销售出库单当月流水号超过四位数！'];

        $number = str_pad($tmp,4,'0',STR_PAD_LEFT);
        $number = $tmp . $number;
        $ccode = "XC" . $date2 . $number;

        return [true,$ccode];
    }

    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);
    }
}