baoshi-was/app/RejectedAnalyzeOwner.php

<?php

namespace App;

use App\Traits\ModelTimeFormat;
use Illuminate\Database\Eloquent\Collection;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;
use Illuminate\Support\Facades\DB;

use App\Traits\ModelLogChanging;

class RejectedAnalyzeOwner extends Model
{
    use ModelLogChanging;


    /**
     * @var string[]
     * id_owner:货主id
     * owner_name:货主姓名
     * bounce_amount:退件数
     * check_amount：审核数
     * in_storage_count：入库数
     * created_at：创建时间
     *
     */
//    protected $appends = ['id_owner', 'owner_name', 'bounce_amount', 'check_amount', 'in_storage_count', 'not_in_storage_count'];

    // 按条件查询    zengjun
    public static function findBy($array = null)
    {
        $sql = RejectedAnalyzeOwner::getQuerySQL($array);

        $resultSet = DB::select($sql);
        $collection = array();
        foreach ($resultSet as $result) {
            $rao = new RejectedAnalyzeOwner();
            $rao->id_owner = $result->id_owner;
            $rao->owner_name = $result->owner_name;
            $rao->bounce_amount = $result->bounce_amount;
            $rao->check_amount = $result->check_amount;
            $rao->in_storage_count = $result->in_storage_count;
            $rao->not_in_storage_count = $result->not_in_storage_count;
            $collection[] = $rao;
        }
        return (new Collection($collection));
    }

    // 按条件查询    zengjun
    public static function getFindBy($array){
        $sql = RejectedAnalyzeOwner::getQuerySQL($array);
        $resultSet = DB::select($sql);
        $list = [];
        foreach ($resultSet as $result) {
            $rao =[
                    'id_owner' => $result->id_owner,
                    'owner_name'=>  $result->owner_name,
                    'bounce_amount'=>$result->bounce_amount,
                    'check_amount'=>$result->check_amount,
                    'uncheck_amount'=>$result->bounce_amount-$result->check_amount,
                    'in_storage_count'=>$result->in_storage_count,
                    'not_in_storage_count'=>$result->not_in_storage_count,
            ];
            $list[] = $rao;
        }
        return $list;
    }

    public static function getExcelFromHead($array = null){
        $arr = [[
            'id_owner'=>'货主编号',
            'owner_name'=>'货主名',
            'bounce_amount'=>'退件单数',
            'check_amount'=>'审核单数',
            'uncheck_amount'=>'未审核单数',
            'in_storage_count'=>'入库单数',
            'not_in_storage_count'=>'未入库单数',
        ]];
        return $arr;
    }

    // 拼接条件     zengjun
    public static function getCondition($array, $owners)
    {
        if (isset($array['data'])){
            $condition=RejectedAnalyzeOwner::getSqlToIDs($array['data']);
        }else{
            $condition = '';
            if (!is_null($array)) {
                foreach ($array as $key => $value) {
                    if (!is_null($value)) {
                        if ($key == 'owner_id' and $value!= '' and $value!='null' and $value!= '""') {
                            $condition .= RejectedAnalyzeOwner::getSqlToIDs($value);
                        }
                        if ($key == 'created_at_start' and !is_null($value) and $value!= "null" and $value!= '""') {
                            $value =  str_replace('"','',$value);
                            $condition .= ' and created_at > "';$condition .= $value;$condition .= '"';
                        }
                        if ($key == 'created_at_end' and !is_null($value) and $value!= "null" and $value!= '""') {
                            $value =  str_replace('"','',$value);
                            $condition .= ' and created_at < "';$condition .= $value;$condition .= '"';
                        }
                    }
                }
            }
        }
        if (count($owners) > 0){
            $condition .= "AND id_owner IN (".implode(",",$owners).")";
        }
        $condition .= ' group by id_owner';
        return $condition;
    }

    public static function getSqlToIDs($ids):string
    {
        $sql='';
        if(!is_null($ids) && $ids !='' ) {
            $ids = explode(',',$ids);
            $sql = ' and id_owner in (';
            foreach ($ids as $index => $id){
                if ($index!=0)
                    $sql .=',';
                $sql .= $id;
            }
            $sql .= ') ';
        }
        return $sql;
    }

    // 返回sql    zengjun
    public static  function getQuerySQL($array){
        $owners = app("OwnerService")->getIdArr();
        $condition = RejectedAnalyzeOwner::getCondition($array, $owners);// 条件
        $sql = 'select distinct rao.id_owner,owners.name owner_name,sum(bounce_amount) bounce_amount,sum(check_amount) check_amount,sum(in_storage_count) in_storage_count,sum(not_in_storage_count) not_in_storage_count from';
        $sql .= '(';
        // 退件单数
        $sql .= ' select  distinct id_owner,count(1) bounce_amount,0 check_amount,0 in_storage_count,0 not_in_storage_count from rejected_bills  where deleted_at is null ';
        $sql .= $condition;
        $sql .= ' UNION ';
        // 审核单数
        $sql .= ' select  distinct id_owner,0 bounce_amount,count(1) check_amount,0 in_storage_count,0 not_in_storage_count from rejected_bills where is_checked = 1 and deleted_at is null ';
        $sql .= $condition;
        $sql .= ' UNION ';
        // 未入库数
        $sql .= ' select  distinct id_owner,0 bounce_amount,0 check_amount,0 in_storage_count,count(1) not_in_storage_count from rejected_bills where is_loaded <> 1 and is_loaded is not null and deleted_at is null ';
        $sql .= $condition;
        $sql .= ' UNION ';
        // 入库单数
        $sql .= ' select  distinct id_owner,0 bounce_amount,0 check_amount,count(1) in_storage_count,0 not_in_storage_count from rejected_bills  where is_loaded = 1 and deleted_at is null ';
        $sql .= $condition;
        $sql .= ') rao ';
        $sql .= ' left join owners on owners.id = rao.id_owner and owners.deleted_at is null ';
        if (count($owners) == 0){
            $sql .= " WHERE 0=1 ";
        }
        $sql .= ' group by rao.id_owner';
        if(isset($array['uncheck_amount'])){
            // 审核数为0
            if($array['uncheck_amount'] == 0)$sql.=' and bounce_amount - check_amount in (0)  ';
            // 审核数不为0
            if($array['uncheck_amount'] == 1)$sql.=' and check_amount <> check_amount ';
        }
        if(isset($array['not_in_storage'])){
            // 入库数为0

            if($array['not_in_storage'] == 0)$sql.=' and not_in_storage_count in (0) ';
            // 入库数不为0
            if($array['not_in_storage'] == 1)$sql.=' and not_in_storage_count <> 0 ';
        }

        $sql .= ' order by bounce_amount desc';
        return  $sql;
    }


}