baoshi-was/app/Services/WaveService.php

<?php


namespace App\Services;


use App\OracleDOCWaveHeader;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class WaveService
{
    public function queryWave(Request $request)
    {
        $sql = $this->createSqlByCondition($request);
        $dataSet = DB::connection('oracle')->select(DB::raw($sql));
        return $dataSet;
    }

    public function getPageParameter(Request $request)
    {
        $page = $request->input('page') ?? 1;
        $paginate = $request->input('paginate') ?? 50;
        $sql = "select count(1) as count from doc_wave_header " . $this->getQueryCondition($request);
        $count = DB::connection('oracle')->select(DB::raw($sql))[0]->count;
        $pageTotal = ceil($count / $paginate);
        $parameter = ['currPage' => $page, 'paginate' => $paginate, 'count' => $count, 'pageTotal' => $pageTotal];
        return $parameter;
    }


    public function createSqlByCondition(Request $request)
    {
        $childSql = " select row_number() over (order by addTime desc ) nums,UdfPrintFlag2,WaveNo,WaveStatus,Descr,CarrierID,WaveDispatchID,addWho,addTime,WaveRule,UserDefine1,UserDefine2 from doc_wave_header";
        $childSql .= $this->getQueryCondition($request);
        $childSql = "( select *  from (" . $childSql . ")";
        $childSql .= $this->getPagingCondition($request);
        $childSql .= " ) wave_header ";
        /*
         * Wav$idsNo 波次编号,WaveStatus 波次状态,CODENAME_C 波次状态编码,Descr 波次描述,Descr_c 承运人,addTime 添加时间,addWho 操作人
         * WaveRule
         * USERDEFINE1 pengzhenzhen-PK2020-07-22 16:49:37    拣货人打印人及时间
         * USERDEFINE2 pengzhenzhen-EX2020-07-22 16:51:43    快递单打印人及时间
         *
         */
        $sql = ' select wave_header.WaveNo,wave_header.WaveStatus,codes.CODENAME_C,wave_header.Descr,customer.Descr_c,wave_header.addTime,wave_header.addWho,wave_header.WaveRule,wave_header.UserDefine1,wave_header.UserDefine2,wave_header.WaveDispatchID,wave_header.UdfPrintFlag2 from';
        $sql .= $childSql;
        $sql .= " left join BAS_CUSTOMER  customer on  customer.CustomerID  = wave_header.CarrierID and customer.CUSTOMER_TYPE = 'CA'";
        $sql .= " left join BAS_CODES codes on wave_header.WaveStatus = codes.CODE and codes.codeId = 'SO_STS'";
        return $sql;
    }

    // cancel Print
    public function cancelPrint($ids)
    {
        $meg = ['success' => false, 'fail_info' => null];
        if (!$ids) {
            $meg['fail_info'] = '没有选中需要重置打印标记的波次!';
            return $meg;
        }
        if (is_string($ids)) {
            $ids = [$ids];
        }
        $count = $this->verfifyIds($ids);
        if(!$count) {
            $meg['fail_info'] = "取消打印的波次中有不符合条件的波次";
            return $meg;
        }
        $cancelPrint = OracleDOCWaveHeader::whereIn("WAVENO", $ids)->update(['udfprintflag2' => 'N','UserDefine2'=>'']);
        if (!$cancelPrint) {
            $meg['fail_info'] = '没有选中需要重置打印标记的波次!';
            return $meg;
        }
        $meg['success'] = true;
        return $meg;
    }

    public function getWaveStatus()
    {
        $sql = "select CODE,CODENAME_C from BAS_CODES where CODEID = 'SO_STS'";
        $dataSet = DB::connection('oracle')->select(DB::raw($sql));
        return $dataSet;
    }

    public function getQueryCondition(Request $request)
    {
        $start_time = $request->input('start_time') ?? null;
        $end_time = $request->input('end_time') ?? null;
        $wave_num = $request->input('wave_num') ?? null;
        $wave_status = $request->input('wave_status') ?? null;
        $childSql = " where 1=1 ";
        if ($start_time) {
            $childSql .= " and addTime > '" . $start_time . " 00:00:00'";
        }
        if ($end_time) {
            $childSql .= " and addTime < '" . $end_time . " 23:59:59'";
        }
        if ($wave_num) {
            $childSql .= " and waveNo like '" .trim($wave_num). "'";
        }
        if ($wave_status) {
            $childSql .= " and waveStatus = " . $wave_status;
        }
        return $childSql;
    }

    public function getPagingCondition(Request $request)
    {
        $page = $request->input('page') ?? 1;
        $paginate = $request->input('paginate') ?? 50;
        $childSql = " where nums >  " . ($page - 1) * $paginate . " and nums <= " . $page * $paginate;
        return $childSql;
    }

    public function getSearchCondition(Request $request)
    {
        $page = $request->input('page') ?? 1;
        $paginate = $request->input('paginate') ?? 50;
        $start_time = $request->input('start_time') ?? null;
        $end_time = $request->input('end_time') ?? null;
        $wave_num = $request->input('wave_num') ?? null;
        $wave_status = $request->input('wave_status') ?? null;
        return ['page' => $page, 'paginate' => $paginate, 'start_time' => $start_time, 'end_time' => $end_time, 'wave_num' => $wave_num, 'wave_status' => $wave_status,];
    }

    public function getSql(Request $request){
        $childSql = "( select row_number() over (order by addTime desc ) nums,UdfPrintFlag2,WaveNo,WaveStatus,Descr,CarrierID,WaveDispatchID,addWho,addTime,WaveRule,UserDefine1,UserDefine2 from doc_wave_header where 1=1 ";
        if ($request->input('start_time')) {
            $childSql .= " and addTime > '" . $request->input('start_time') . " 00:00:00' ";
        }
        if ($request->input('end_time')) {
            $childSql .= " and addTime < '" . $request->input('end_time') . " 23:59:59' ";
        }
        if ($request->input('wave_num')) {
            $wave_num = $request->input('wave_num');
            $wave_num = $this->getWaveNoString($wave_num);
            if(strpos($wave_num,',')){
                $childSql .= " and WaveNo in (" .$wave_num . ") ";
            }else{
                $childSql .= " and WaveNo like '" .$wave_num . "' ";
            }
        }
        if ($request->input('wave_status')) {
            $childSql .= " and WaveStatus =  '" . $request->input('wave_status') . "' ";
        }
        $childSql.=' order by addTime';
        $childSql =  $childSql . ") wave_header ";
        $sql = ' select wave_header.WaveNo,' .
            'wave_header.WaveStatus,' .
            'codes.CODENAME_C,' .
            'wave_header.Descr,' .
            'customer.Descr_c,' .
            'wave_header.addTime,' .
            'wave_header.addWho,' .
            'wave_header.WaveRule,' .
            'wave_header.UserDefine1,' .
            'wave_header.UserDefine2,' .
            'wave_header.WaveDispatchID,' .
            'wave_header.UdfPrintFlag2 from ' ;
        $sql .= $childSql;
        $sql .= " left join BAS_CUSTOMER  customer on  customer.CustomerID  = wave_header.CarrierID and customer.CUSTOMER_TYPE = 'CA'";
        $sql .= " left join BAS_CODES codes on wave_header.WaveStatus = codes.CODE and codes.codeId = 'SO_STS'";
        return $sql;
    }

    public function downloadExec(Request $request)
    {
        $sql = $this->getSql($request);
        $data = DB::connection('oracle')->select(DB::raw($sql));
        return $data;
    }

    public function getExcelFromHead()
    {
        $arr = [[
            'waveNo' => '波次号',
            'codename_c' => '波次状态',
            'waveRule' => '波次规则',
            'descr' => '波次描述',
            'descr_c' => '承运人',
            'addWho' => '操作员',
            'addTime' => '创建时间',
            'pickerPrint' => '拣货单打印人',
            'pickerPrintTime' => '拣货单打印时间',
            'expressPrinting' => '快递单号打印人',
            'expressPrintTime' => '快递单号打印时间',
        ]];
        return $arr;
    }
    public function getWaveNoString($wave_num){
        $sqlString = '';
        if(!$wave_num){
            return $sqlString;
        }
        if(strpos($wave_num,"%")){
            return $wave_num;
        }
        if(strpos($wave_num,",") ){

            $arr = explode(',',$wave_num);
            $let = count($arr);
            for($i=0;$i<$let;$i++){
                $arr[$i] = "'".$arr[$i]."'";
            }
            $sqlString = implode(",",$arr);
            return $sqlString;
        }
        if($wave_num){
            $sqlString = "'".$wave_num."'";
        }
        return $sqlString;
    }

    public function verfifyIds($ids){
        $sql =  "select count(1) count from DOC_WAVE_HEADER where  WaveNo in (".$this->getWaveNoString(implode(',',$ids)).") and WAVESTATUS ='99' and UdfPrintFlag2 ='Y'";
        $count=  DB::connection('oracle')->select(DB::raw($sql))[0]->count;
        return $count = 0;
    }
}