baoshi-was/app/Services/OwnerService.php

<?php

namespace App\Services;

use App\Authority;
use App\Interfaces\UserFilter;
use App\OracleBasCustomer;
use App\Owner;
use App\OwnerPriceDirectLogistic;
use App\OwnerPriceExpress;
use App\OwnerPriceLogistic;
use App\OwnerPriceOperation;
use App\OwnerPriceSystem;
use App\OwnerStoragePriceModel;
use App\Services\common\BatchUpdateService;
use App\User;
use Carbon\Carbon;
use Doctrine\DBAL\Exception\DatabaseObjectExistsException;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;
use App\Traits\ServiceAppAop;


class OwnerService implements UserFilter
{
    use ServiceAppAop;
    protected $modelClass=Owner::class;
    /** @var CacheService $cacheService */
    private $cacheService;
    function __construct(){
        $this->instant($this->cacheService,'CacheService');
    }
    /*
     * array | string $column
     * 默认一些select字段,可传递string 或 array来指定select字段
     */
    public function getIntersectPermitting(array $column = ['id', 'name'])
    {
        $ownerIds=app('OwnerService')->getIdArr();
        return $this->cacheService->getOrExecute('OwnersAll_IdName'.md5(json_encode($column).json_encode($ownerIds)),function()use($column,$ownerIds){
            if(empty($ownerIds))return new Collection();
            return Owner::query()->select($column)->whereIn('id', $ownerIds)->whereNull('deleted_at')->get();
        },config('cache.expirations.owners'));
    }

    public function getSelection($column = ['id'])
    {
        return $this->cacheService->getOrExecute('OwnersAll_'.md5(json_encode($column)),function()use($column){
            return Owner::filterAuthorities()->select($column)->get();
        },config('cache.expirations.owners'));
    }

    /**
     *同步WMS全部货主至WAS
     */
    public function syncOwnersData()
    {
        $basCustomers = OracleBasCustomer::query()
            ->select('CUSTOMERID', 'DESCR_C')
            ->where('DESCR_C', 'not like', '%换ERP%')
            ->where('DESCR_C', 'not like', '%退仓%')
            ->where('CUSTOMER_TYPE', 'OW')
            ->get();
        $ownerCount = Owner::query()->count();
        if (count($basCustomers) == $ownerCount) return null;
        foreach ($basCustomers as $basCustomer) {
            $owner = Owner::query()->where('code', $basCustomer['customerid'])->first();
            if (!isset($owner)){
                Owner::query()->create([
                    'code' => $basCustomer['customerid'],
                    'name' => $basCustomer['descr_c'],
                    'created_at' => Carbon::now()->format('Y-m-d H:i:s'),
                ]);
                continue;
            }
            if ($owner['name']!=$basCustomer['descr_c']){
                $owner->update([
                    'code' => $basCustomer['customerid'],
                    'name' => $basCustomer['descr_c'],
                ]);
            }
        }
        $owners = Owner::query()->select('id', 'name')->get();
        return $owners;
    }

    public function first(array $params, array $rules =[]){
        return $this->cacheService->getOrExecute('OwnersFirst'.md5(json_encode($params),json_encode($rules)),function()use($params,$rules){
            $owner = Owner::query();
            foreach ($params as $column => $value){
                if (!isset($rules[$column]))$owner->where($column, $value);
                else{
                    switch ($rules[$column]){
                        case "or":
                            $owner->orWhere($column, $value);
                            break;
                    }
                }
            }
            return $owner->first();
        },config('cache.expirations.rarelyChange'));
    }

    public function find($id, $with = [])
    {
        return Owner::query()->with($with)->find($id);
    }

    public function update(Owner $owner, array $values, array $related = [])
    {
        if ($related["ownerStoragePriceModels"] ?? false)$owner->ownerStoragePriceModels()->sync($related["ownerStoragePriceModels"]);
        return $owner->update($values);
    }

    public function create(array $params, array $related = []){
        /** @var Owner $owner */
        $owner = Owner::query()->create($params);
        if ($related["ownerStoragePriceModels"] ?? false)$owner->ownerStoragePriceModels()->syncWithoutDetaching($related["ownerStoragePriceModels"]);
        return $owner;
    }

    public function firstOrCreate(array $params, array $values = null){
        if (!$values) return Owner::query()->whereNull("deleted_at")->firstOrCreate($params);
        return Owner::query()->whereNull("deleted_at")->firstOrCreate($params,$values);
    }


    public function 获取订单跟踪的货主(){
        return Owner::query()->with('orderTrackingOwner')->whereHas('orderTrackingOwner',function($query){
            $query->where('status','启用');
        })->get();
    }

    public function getByWmsOrders($orderHeaders){
        $customerIds = array_unique(data_get($orderHeaders,'*.customerid'));
        $customerIds = array_diff($customerIds,[null,'','*']);
        $owners = Owner::query()->whereIn('code',$customerIds)->get();

        if($owners->count() < count($customerIds)){
            $customerIds = array_diff($customerIds,data_get($owners,'*.code'));
            $owner_list = $this->createByWmsCustomerIds($customerIds);
            $owners=$owners->concat($owner_list);
        }
        return $owners;
    }

    public function createByWmsCustomerIds($codes){
        if(!$codes) {return [];}
        $basCustomer = OracleBasCustomer::query()
            ->where('Customer_Type','OW')
            ->whereIn('CustomerID', $codes)
            ->get();

        $insert_params = [];
        $created_at = Carbon::now()->format('Y-m-d H:i:s');
        foreach ($basCustomer as $item) {
            $insert_params[] = [
                'code' => $item->customerid,
                'name' => $item->descr_c,
                'created_at' => $created_at,
            ];
        }

        try {
            if (count($insert_params) > 0) {
                $this->insert($insert_params);
                app('LogService')->log(__METHOD__, __FUNCTION__, '批量创建 owner ' . count($insert_params) . json_encode($insert_params) );
            }
        } catch (\Exception $e) {
            app('LogService')->log(__METHOD__, __FUNCTION__, '批量创建 owner error' . json_encode($insert_params) . '||' . $e->getMessage() . '||' . $e->getTraceAsString());
        } finally {
            return Owner::query()->whereIn('code', $codes)->get();
        }
    }

    public function insert($fillables){
        return Owner::query()->insert($fillables);
    }

    public function getAuthorizedOwners(){
        return Owner::query()->whereIn('id',app("OwnerService")->getQuery())->get();
    }

    public function get(array $params, array $withs = null, bool $authority = true, bool $notShowSoftDelete = false, $user = null)
    {
        return Cache::remember(
            'owner_'.md5(json_encode($params).json_encode($withs).$authority.$notShowSoftDelete.json_encode($user))
            ,config('cache.expirations.rarelyChange')
            ,function()use($params,$withs,$authority,$notShowSoftDelete){
            $query = $this->query($this->getQueryConstructor($withs, $authority, $notShowSoftDelete),$params);
            return $query->get();
        });
    }

    private function getQueryConstructor(array $withs, bool $authority, bool $notShowSoftDelete) :Builder
    {
        $query = Owner::query();
        if ($withs)$query->with($withs);
        if ($authority){
            $ids = $this->getIdArr();
            if (count($ids) > 0){
                $query->whereIn("id", $ids);
            } else {
                $query->where("id", 0);
            }
        }
        if ($notShowSoftDelete) $query->whereNull('deleted_at');
        return $query;
    }
    public function paginate(array $params, array $withs = null, bool $authority = true, bool $notShowSoftDelete = false)
    {
        $query = $this->query($this->getQueryConstructor($withs, $authority, $notShowSoftDelete),$params)->
                orderByDesc("id");
        return $query->paginate($params["paginate"] ?? 50);
    }

    private function query(Builder $builder, array $params)
    {
        foreach ($params as $column => $param){
            if ($column == 'paginate' || $column == 'page' || !$param)continue;
            if ($param === true){
                $builder->whereNotNull($column);
                continue;
            }
            if ($param === false){
                $builder->whereNull($column);
                continue;
            }
            if ($column == 'created_at_start'){
                $builder->where("created_at",">=",$param.":00");
                continue;
            }
            if ($column == 'created_at_end'){
                $builder->where("created_at","<=",$param.":59");
                continue;
            }
            if ($column == 'contract_number'){
                $builder->whereHas("contracts",function ($query)use($param){
                    /** @var Builder $query */
                    $query->where("contract_number","like",$param."%");
                });
                continue;
            }
            if ($column == 'using_type'){
                $builder->whereHas("ownerStoragePriceModels",function ($query)use($param){
                    /** @var Builder $query */
                    $query->where("using_type",$param);
                });
                continue;
            }
            if ($column == 'customers'){
                if (is_array($param))$builder->whereIn('customer_id',$param);
                else $builder->where('customer_id',$param);
                continue;
            }
            if ($column == 'ids'){
                if (is_array($param))$builder->whereIn('id',$param);
                else $builder->where('id',$param);
                continue;
            }
            if ($column == 'owners'){
                if (is_array($param))$builder->whereIn('owner_id',$param);
                else $builder->where('owner_id',$param);
                continue;
            }
//            if ($column == 'user_work_group'){
//                $builder->where("user_workgroup_id",$param);
//                continue;
//            }
            if ($column == 'kcGroup'){
                $builder->whereHas("departmentObligationOwner",function($query)use($param){
                    $query->where('obligation_code','kc')->where('department_id',$param);
                });
                continue;
            }
            if ($column == 'jgGroup'){
                $builder->whereHas("departmentObligationOwner",function($query)use($param){
                    $query->where('obligation_code','jg')->where('department_id',$param);
                });
                continue;
            }
            if ($column == 'fhGroup'){
                $builder->whereHas("departmentObligationOwner",function($query)use($param){
                    $query->where('obligation_code','fh')->where('department_id',$param);
                });
                continue;
            }
            if ($column == 'thGroup'){
                $builder->whereHas("departmentObligationOwner",function($query)use($param){
                    $query->where('obligation_code','th')->where('department_id',$param);
                });
                continue;
            }
            if ($column == 'shGroup'){
                $builder->whereHas("departmentObligationOwner",function($query)use($param){
                    $query->where('obligation_code','sh')->where('department_id',$param);
                });
                continue;
            }
            if (is_array($param))$builder->whereIn($column,$param);
            else $builder->where($column,$param);
        }
        return $builder;
    }

    public function getOwnerByCodes($codes)
    {
        $collect = collect();
        if(count($codes) == 0)return $collect;
        foreach ($codes as $code) {
            $collect = $collect->push($this->getOwnerByCode($code));
        }
        return $collect;
    }

    public function getOwnerByCode($code){
        return Cache::remember("getOwnerByCode_{$code}", config('cache.expirations.owners'), function ()use($code){
            $owner = Owner::query()->where('code',$code)->first();
            if($owner) return $owner;
             $basCustomer = app('OracleBasCustomerService')->first(['Customer_Type'=>'OW','CustomerID'=>$code]);
            if(!$basCustomer)return null;
            if($basCustomer && $basCustomer['active_flag']=='Y') return Owner::query()
                ->create(['name'=>$basCustomer['descr_c'],'code'=>$basCustomer['customerid']]);
            $deleted_at=Carbon::now()->toDateTimeString();
            if($basCustomer && $basCustomer['active_flag']=='N') return Owner::query()
                ->create(['name'=>$basCustomer['descr_c'],'code'=>$basCustomer['customerid'],'deleted_at'=>$deleted_at]);
        });
    }
    public function codeGetOwner($code)
    {
        return app(CacheService::class)->getOrExecute("owner_".$code,function ()use($code){
            return Owner::query()->firstOrCreate(["code"=>$code],["code"=>$code,"name"=>$code]);
        });
    }

    /**
     * 向FLUX同步推送WAS本地录入信息
     *
     * @param array|Owner|integer $owner
     * @return bool
     */
    public function syncPush($owner)
    {
        if (is_array($owner)){
            $owner = new Owner();
            foreach ($owner as $column=>$value){
                $owner[$column] = $value;
            }
        }
        if (is_numeric($owner)){
            $owner = Owner::query()->find($owner);
            if (!$owner)return false;
        }
        $wms = DB::connection("oracle")->selectOne(DB::raw("SELECT CUSTOMERID FROM BAS_CUSTOMER WHERE CUSTOMER_TYPE = ? AND CUSTOMERID = ?"),["OW",$owner->code]);
        if (!$wms && $owner->code){
            $query = DB::raw(<<<sql
    INSERT INTO BAS_CUSTOMER(CUSTOMERID,CUSTOMER_TYPE,DESCR_C,ADDTIME,EDITTIME,ADDWHO)
        VALUES(?,?,?,TO_DATE(?,'yyyy-mm-dd hh24:mi:ss'),TO_DATE(?,'yyyy-mm-dd hh24:mi:ss'),?)
sql
            );
            $date = date('Y-m-d H:i:s');
            DB::connection("oracle")->insert($query,[$owner->code,'OW',$owner->name,$date,$date,'WAS-'.(Auth::user() ? Auth::user()['name'] : 'SYSTEM')]);
        }
        return true;
    }


    public function syncUpdate($owner)
    {
        if (is_array($owner)){
            $owner = new Owner();
            foreach ($owner as $column=>$value){
                $owner[$column] = $value;
            }
        }
        if (is_numeric($owner)){
            $owner = Owner::query()->find($owner);
            if (!$owner)return false;
        }
        $sql = DB::raw(<<<sql
    update BAS_CUSTOMER set ACTIVE_FLAG = ?,EDITTIME = TO_DATE(?,'yyyy-mm-dd hh24:mi:ss'),EDITWHO = ? where CUSTOMERID = ? and CUSTOMER_TYPE = ?
sql
        );
        $date = date('Y-m-d H:i:s');
        if ($owner && $owner->deleted_at){
            DB::connection("oracle")->update($sql,['N',$date,'WAS-'.(Auth::user() ? Auth::user()['name'] : 'SYSTEM'),$owner->code,'OW']);
        }
       if ($owner && $owner->deleted_at==null) {
            DB::connection("oracle")->update($sql,['Y',$date,'WAS-'.(Auth::user() ? Auth::user()['name'] : 'SYSTEM'),$owner->code,'OW']);
        }
        return true;
    }

    /**
     * 同步货主时创建权限
     *
     * @param array|Owner $owner
     */
    public function createAuthority($owner)
    {
        Authority::query()->create([
            'name' => "_{$owner['id']}",
            'alias_name' => "(货主：{$owner['name']})",
            'remark' => "(key: _{$owner['id']})",
        ]);
    }

    /**
     * 停用货主时删除权限
     *
     * @param array|Owner $owner
     */
    public function deleteAuthority($owner)
    {
        $authorities = Authority::query()->where('name',"_{$owner['id']}")
            ->where("alias_name","like","(货主%")
            ->get(["id"]);
        $ids = array_column($authorities->toArray(),"id");
        DB::table("authority_role")->whereIn("id_authority",$ids)->delete();
        Authority::destroy($ids);
    }

    /**
     * 计费模型变动时更新货主中关联属性
     *
     * @param integer $ownerId
     *
     */
    public function refreshRelevance($ownerId)
    {
        $relevance = [];
        $sql = <<<sql
SELECT 1 FROM owner_storage_price_models a
LEFT JOIN owner_storage_price_model_owner b ON a.id =  b.owner_storage_price_model_id
LEFT JOIN owners c ON b.owner_id = c.id
WHERE (a.operation IS NULL OR a.operation = '') AND c.id = ? LIMIT 1
sql;
        if (DB::selectOne(DB::raw($sql),[$ownerId]))$relevance[] = 0;
        $sql = <<<sql
SELECT 1 FROM owner_price_operations a
LEFT JOIN owner_price_operation_owner b ON a.id =  b.owner_price_operation_id
LEFT JOIN owners c ON b.owner_id = c.id
WHERE (a.operation IS NULL OR a.operation = '') AND c.id = ? LIMIT 1
sql;
        if (DB::selectOne(DB::raw($sql),[$ownerId]))$relevance[] = 1;
        $sql = <<<sql
SELECT 1 FROM owner_price_expresses a
LEFT JOIN owner_price_express_owner b ON a.id =  b.owner_price_express_id
LEFT JOIN owners c ON b.owner_id = c.id
WHERE (a.operation IS NULL OR a.operation = '') AND c.id = ? LIMIT 1
sql;
        if (DB::selectOne(DB::raw($sql),[$ownerId]))$relevance[] = 2;
        $sql = <<<sql
SELECT 1 FROM owner_price_logistics a
LEFT JOIN owner_price_logistic_owner b ON a.id =  b.owner_price_logistic_id
LEFT JOIN owners c ON b.owner_id = c.id
WHERE (a.operation IS NULL OR a.operation = '') AND c.id = ? LIMIT 1
sql;
        if (DB::selectOne(DB::raw($sql),[$ownerId]))$relevance[] = 3;
        $sql = <<<sql
SELECT 1 FROM owner_price_direct_logistics a
LEFT JOIN owner_price_direct_logistic_owner b ON a.id =  b.owner_price_direct_logistic_id
LEFT JOIN owners c ON b.owner_id = c.id
WHERE (a.operation IS NULL OR a.operation = '') AND c.id = ? LIMIT 1
sql;
        if (DB::selectOne(DB::raw($sql),[$ownerId]))$relevance[] = 4;
        $sql = <<<sql
SELECT 1 FROM owner_price_systems a LEFT JOIN owners b ON a.owner_id = b.id
WHERE b.id = ? LIMIT 1
sql;
        if (DB::selectOne(DB::raw($sql),[$ownerId]))$relevance[] = 5;
        Owner::query()->where("id",$ownerId)->update(["relevance"=>$relevance]);
    }

    /**
     * 税率变更时附加税率
     *
     * @param Owner|\stdClass $owner
     */
    public function attachTaxRate(Owner $owner)
    {
        OwnerStoragePriceModel::query()->whereHas("owners",function (Builder $query)use($owner){
            $query->where("id",$owner->id);
        })->whereNull("tax_rate_id")->update(["tax_rate_id"=>$owner->tax_rate_id]);
        OwnerPriceOperation::query()->whereHas("owners",function (Builder $query)use($owner){
            $query->where("id",$owner->id);
        })->whereNull("tax_rate_id")->update(["tax_rate_id"=>$owner->tax_rate_id]);
        OwnerPriceExpress::query()->whereHas("owners",function (Builder $query)use($owner){
            $query->where("id",$owner->id);
        })->whereNull("tax_rate_id")->update(["tax_rate_id"=>$owner->tax_rate_id]);
        OwnerPriceLogistic::query()->whereHas("owners",function (Builder $query)use($owner){
            $query->where("id",$owner->id);
        })->whereNull("tax_rate_id")->update(["tax_rate_id"=>$owner->tax_rate_id]);
        OwnerPriceDirectLogistic::query()->whereHas("owners",function (Builder $query)use($owner){
            $query->where("id",$owner->id);
        })->whereNull("tax_rate_id")->update(["tax_rate_id"=>$owner->tax_rate_id]);
        OwnerPriceSystem::query()->where("owner_id",$owner->id)->whereNull("tax_rate_id")
            ->update(["tax_rate_id"=>$owner->tax_rate_id]);
    }

    /**
     * 税率变更时取消税率
     *
     * @param Owner|\stdClass $owner
     */
    public function removeTaxRate(Owner $owner)
    {
        OwnerStoragePriceModel::query()->whereHas("owners",function (Builder $query)use($owner){
            $query->where("id",$owner->id);
        })->update(["tax_rate_id"=>null]);
        OwnerPriceOperation::query()->whereHas("owners",function (Builder $query)use($owner){
            $query->where("id",$owner->id);
        })->update(["tax_rate_id"=>null]);
        OwnerPriceExpress::query()->whereHas("owners",function (Builder $query)use($owner){
            $query->where("id",$owner->id);
        })->update(["tax_rate_id"=>null]);
        OwnerPriceLogistic::query()->whereHas("owners",function (Builder $query)use($owner){
            $query->where("id",$owner->id);
        })->update(["tax_rate_id"=>null]);
        OwnerPriceDirectLogistic::query()->whereHas("owners",function (Builder $query)use($owner){
            $query->where("id",$owner->id);
        })->update(["tax_rate_id"=>null]);
        OwnerPriceSystem::query()->where("owner_id",$owner->id)
            ->update(["tax_rate_id"=>null]);
    }

    /**
     * 获取税率 或 税费
     *
     * @param Model|\stdClass $model
     * @param int $ownerId
     * @param float|null $money
     *
     * @return float|null
     */
    public function getTaxRateFee(Model $model, int $ownerId, ?float $money = null):?float
    {
        $taxRate = null;
        if ($model->tax_rate_id){
            $model->loadMissing("taxRate");
            $taxRate = $model->taxRate;
        }
        if (!$taxRate){
            /** @var Model|\stdClass $owner */
            $owner = new Owner();
            $owner->id = $ownerId;
            $owner->load("taxRate");
            $taxRate = $owner->taxRate;
        }
        if (!$taxRate)return null;
        if ($money===null)return $taxRate->value;
        return $money*($taxRate->value/100);
    }

    public function changeManualBackStatus($id,$isManual)
    {
        $owner=Owner::query()->find($id);
        if ($isManual==0)$owner->update(['is_manual_back'=>1]);
        else $owner->update(['is_manual_back'=>0]);
        return $owner;
    }
    public function changeIntervalTime($id,$intervalTime)
    {
        $owner=Owner::query()->find($id);
        $owner->update(['interval_time'=>$intervalTime]);
        return $owner;
    }

    function getIdArr(?int $userId = null): array
    {
        if (!$userId)$userId = Auth::id();
        return $this->cacheService->getOrExecute("USER.{$userId}.OWNER.ID",function()use($userId){
            return array_column($this->getQuery($userId)->get()->toArray(),"id");
        });
    }
    function getCodeArr(?int $userId): array
    {
        $column = "code";
        if (!$userId)$userId = Auth::id();
        return $this->cacheService->getOrExecute("USER.{$userId}.OWNER.CODE",function()use($userId, $column){
            return array_column($this->getQuery($userId, $column)->get()->toArray(),$column);
        });
    }
    function getQuery(?int $userId = null, $column = "id"): Builder
    {
        if (!$userId)$userId = Auth::id();
        $query = Owner::query()->select("owners.".$column);
        if (!app("UserService")->checkAdminIdentity($userId) && !app("AuthorityService")->checkAllOwner($userId)){
            //$query->whereHas("roles",function ($query)use($userId){
                $query->whereHas("users",function ($query)use($userId){
                    $query->where("users.id",$userId);
                });
            //});
        }
        return $query->whereNull("deleted_at");
    }

    public function combineOwners($owners)
    {
        foreach ($owners as $owner){
            $departmentObligationOwner=$owner->departmentObligationOwner??false;
            if (!$departmentObligationOwner)continue;
            foreach ($departmentObligationOwner as $item){
                if ($item->obligation_code=='kc'){
                    $owner->kc=$item->department_id;$owner->kcGroup=$item->department?$item->department->name:'';
                }
                if ($item->obligation_code=='jg'){
                    $owner->jg=$item->department_id;$owner->jgGroup=$item->department?$item->department->name:'';
                }
                if ($item->obligation_code=='th'){
                    $owner->th=$item->department_id;$owner->thGroup=$item->department?$item->department->name:'';
                }
                if ($item->obligation_code=='sh'){
                    $owner->sh=$item->department_id;$owner->shGroup=$item->department?$item->department->name:'';
                }
                if ($item->obligation_code=='fh'){
                    $owner->fh=$item->department_id;$owner->fhGroup=$item->department?$item->department->name:'';
                }
            }
        }
        return $owners;
    }
}