baoshi-was/app/Services/NewOrderCountingRecordService.php

<?php


namespace App\Services;


use App\Logistic;
use App\Order;
use App\OrderCountingRecord;
use App\Warehouse;
use Carbon\Carbon;
use Illuminate\Support\Arr;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\Cache;

class NewOrderCountingRecordService
{
    public function orderCountingRecordsFromCache($start, $end, $unit, $ownerIds)
    {
        $dataList = collect();
        if (count($ownerIds) == 1) return $this->orderCountingRecords($start, $end, $unit, $ownerIds);//当前用户只有一个货主权限，只需执行旧方法即可
        $queryCondition = $this->transfersToCondition($start, $end, $unit, $ownerIds);//根据条件获取查询条件
        $md5OwnerIds = md5(join(Arr::sortRecursive($ownerIds)));//将$ownerIds排序，保证md5加密后结果一致  将当前用户拥有的ownerIds进行md5加密
        foreach ($queryCondition['data'] as $dateStr => $ownerIds) {
            $key = 'new_order_counting_records_' . $dateStr . '_' . $md5OwnerIds . '_' . $queryCondition['unit'];
            $data = cache()->remember($key, $this->getTtl($dateStr, $unit), function () use ($dateStr, $unit, $ownerIds) {
                //如果缓存不存在，则将$dateStr作为查询条件，使用之前的接口查询改日的订单量统计结果，由于接口返回collect，去其中的第一个
                //因为查询条件仅为一天
                return $this->orderCountingRecords($dateStr, $dateStr, $unit, $ownerIds)->first();
            });
            $dataList->push($data);
        }
        return $dataList;
    }

    public function orderCountingRecords($start, $end, $unit, $ownerIds)
    {
        $orders = $this->get($start, $end, $unit, $ownerIds);
        $dataList = collect();
        $orders->groupBy('date_target')->sortKeys()->each(function ($items) use (&$dataList, $unit) {
            $counter = $items->reduce(function ($sum, $item) {
                return $sum + $item->amount;
            }, 0);
            $date_target = $items[0]->date_target;
            $dataList->push([
                'counter' => $counter,
                'date_target' => $date_target,
            ]);
        });
        return $dataList;
    }

    public function logisticsCountingRecords($start, $end, $ownerIds)
    {
        $key = 'logisticsCountingRecords_' . $start . '_' . $end . '_' . json_encode($ownerIds);
        return Cache::remember($key, config('cache.expirations.oftenChange'), function () use ($start, $end, $ownerIds) {
            $dataList = collect();
            $resultOrders = $this->get($start, $end, '日', $ownerIds);
            $resultOrders->groupBy('logistic_id')->each(function ($item) use (&$dataList) {
                $counter = $item->reduce(function ($sum, $item) {
                    return $sum + $item->amount;
                }, 0);
                $dataList->push([
                    'value' => $counter,
                    'logistic_id' => $item[0]->logistic_id,
                ]);
            });
            $map = [];
            $logistics = Logistic::query()->whereIn('id', data_get($dataList, '*.logistic_id'))->get();
            $logistics->each(function ($logistic) use (&$map) {
                $map[$logistic->id] = $logistic;
            });
            return $dataList->map(function (&$item) use ($map) {
                $logistic = $map[$item['logistic_id']] ?? '';
                $item['name'] = $logistic->name ?? '';
                return $item;
            });
        });
    }

    public function warehouseCountingRecords($start, $end, $ownerIds)
    {
        $key = 'warehouseCountingRecords_' . $start . '_' . $end . '_' . json_encode($ownerIds);
        return Cache::remember($key, config('cache.expirations.oftenChange'), function () use ($start, $end, $ownerIds) {
            $dataList = collect();
            $resultOrders = $this->get($start, $end, '日', $ownerIds);
            $resultOrders->groupBy('warehouse_id')->each(function ($item) use (&$dataList) {
                $counter = $item->reduce(function ($sum, $item) {
                    return $sum + $item->amount;
                }, 0);
                $dataList->push([
                    'value' => $counter,
                    'warehouse_id' => $item[0]->warehouse_id,
                ]);
            });
            $map = [];
            $logistics = Warehouse::query()->whereIn('id', data_get($dataList, '*.warehouse_id'))->get();
            $logistics->each(function ($warehouse) use (&$map) {
                $map[$warehouse->id] = $warehouse;
            });
            return $dataList->map(function (&$item) use ($map) {
                $warehouse = $map[$item['warehouse_id']] ?? '';
                $item['code'] = $warehouse->name ?? '';
                switch ($item['code']) {
                    case 'WH01':
                        $item['name'] = '松江一仓';
                        break;
                    case 'WH02':
                        $item['name'] = '松江二仓';
                        break;
                    case 'WH03':
                        $item['name'] = '嘉定一仓';
                        break;
                    default:
                        $item['name'] = '仓库为空';
                        break;
                }
                return $item;
            });
        });
    }


    public function get($start, $end, $unit, $ownerIds)
    {
        $queryCondition = $this->transfersToCondition($start, $end, $unit, $ownerIds);
        return $this->getOrderCountingRecords($queryCondition);
    }

    public function getFromCache($queryCondition): array
    {
        $lackingCondition = [];
        $orderCountingRecords_FromCache = collect();
        $lackingCondition['unit'] = $queryCondition['unit'];
        foreach ($queryCondition['data'] as $dateStr => $ownerIds) {
            foreach ($ownerIds as $ownerId) {
                $key = 'order_counting_records_' . $dateStr . '_' . $ownerId . '_' . $queryCondition['unit'];
                $items = cache()->get($key);
                if (empty($items)) {
                    if (empty($lackingCondition['data'][$dateStr])) {
                        $lackingCondition['data'][$dateStr] = [];
                    }
                    $lackingCondition['data'][$dateStr][] = $ownerId;
                } else {
                    foreach ($items as $item) {
                        $orderCountingRecords_FromCache->push($item);
                    }
                }
            }
        }
        return [$orderCountingRecords_FromCache, $lackingCondition];
    }

    public function getFromMiddleTable($queryCondition): array
    {
        $orderSqlBuilder = OrderCountingRecord::query();
        $unit = $queryCondition['unit'];
        foreach ($queryCondition['data'] as $date => $owners) {
            $orderSqlBuilder->orWhere(function ($query) use ($owners, $date, $unit) {
                $query->whereIn('owner_id', $owners)->where('date_target', $date)->where('counting_unit', $unit);
            });
        }
        $dataFromMiddleTable = $orderSqlBuilder->get();
        $this->insertIntoCache($dataFromMiddleTable, $unit);
        $queryCondition = $this->unQueryCondition($dataFromMiddleTable, $queryCondition);
        $orderCountingRecords_fromSelfTable = $dataFromMiddleTable;
        $lackingCondition = $queryCondition;
        return [$orderCountingRecords_fromSelfTable, $lackingCondition];
    }

    /**
     * 根据$queryCondition从多种数据源中查询数据
     * 并将结果插入中间表或者缓存
     * @param $queryCondition
     * @return Collection|mixed|\Tightenco\Collect\Support\Collection
     */
    public function getOrderCountingRecords($queryCondition)
    {
        list($orderCountingRecords_fromCache, $lackingCondition)
            = $this->getFromCache($queryCondition);
        if (empty($lackingCondition['data'])) {
            return $orderCountingRecords_fromCache;
        }

        list($orderCountingRecords_fromSelfTable, $lackingCondition)
            = $this->getFromMiddleTable($lackingCondition);
        if (empty($lackingCondition['data'])) {
            return $orderCountingRecords_fromCache->concat($orderCountingRecords_fromSelfTable);
        }
        /**
         * 1 如果查询粒度大于日 则进入getFromLowerUnit()
         * 2 如果查询粒度为日 则进入dataFromOrder()
         */
        if ($lackingCondition['unit'] != '日') {//日为最低粒度，不用进入此方法
            $orderCountingRecords_combinedByLower = $this->getFromLowerUnit($lackingCondition);
        } else {
            $orderCountingRecords_FromOrder = $this->dataFromOrder($lackingCondition);
        }
        $result = collect();
        if (isset($orderCountingRecords_FromOrder) && !$orderCountingRecords_FromOrder->isEmpty()) {
            $result = $result->concat($orderCountingRecords_FromOrder);
        }
        if (!$orderCountingRecords_fromCache->isEmpty()) {
            $result = $result->concat($orderCountingRecords_fromCache);
        }
        if (!$orderCountingRecords_fromSelfTable->isEmpty()) {
            $result = $result->concat($orderCountingRecords_fromSelfTable);
        }
        if (isset($orderCountingRecords_combinedByLower)) {
            $result = $result->concat($orderCountingRecords_combinedByLower);
        }
        return $result;
    }


    public function getFromLowerUnit($queryCondition)
    {
        switch ($queryCondition['unit']) {
            case '年':
                $lowUnit = '月';
                $conditionClone = ['unit' => $lowUnit, 'data' => []];
                foreach ($queryCondition['data'] as $date => $ownerIds) {
                    $startAt = $date;
                    $endAt = Carbon::parse($date)->endOfYear()->toDateString();
                    $items = $this->transfersToCondition(
                        $startAt, $endAt, $lowUnit, $ownerIds
                    )['data'];
                    foreach ($items as $dateStr => $item) {
                        if (empty($conditionClone['data'][$dateStr])) $conditionClone['data'][$dateStr] = $dateStr;
                        $conditionClone['data'][$dateStr] = ($item);
                    }
                }
                $orderCountingRecords_days = $this->getOrderCountingRecords($conditionClone);
                $orderCountingRecords_combinedByLower = $this->turnGradingUpToLow($orderCountingRecords_days, $lowUnit, 'year');
                $this->insertIntoMiddleTable($orderCountingRecords_combinedByLower, '年');
                $this->insertIntoCache(collect($orderCountingRecords_combinedByLower), '年');
                break;
            case '月':
                $lowUnit = '日';
                $conditionClone = ['unit' => $lowUnit, 'data' => []];
                foreach ($queryCondition['data'] as $date => $ownerIds) {
                    $startAt = $date;
                    $endAt = Carbon::parse($date)->endOfMonth()->toDateString();
                    $items = $this->transfersToCondition(
                        $startAt, $endAt, $lowUnit, $ownerIds
                    )['data'];
                    foreach ($items as $dateStr => $item) {
                        if (empty($conditionClone['data'][$dateStr])) $conditionClone['data'][$dateStr] = $dateStr;
                        $conditionClone['data'][$dateStr] = ($item);
                    }
                }
                $orderCountingRecords_days = $this->getOrderCountingRecords($conditionClone);
                $orderCountingRecords_combinedByLower = $this->turnGradingUpToLow($orderCountingRecords_days, $lowUnit, 'month');
                $this->insertIntoMiddleTable($orderCountingRecords_combinedByLower, '月');
                $this->insertIntoCache(collect($orderCountingRecords_combinedByLower), '月');

                break;
            case '日':
                return [[], $queryCondition];
            default:
        }
        return $orderCountingRecords_combinedByLower;
    }

    public function transfersToCondition($start, $end, $unit, $ownerIds): array
    {
        $condition = [
            'data' => [],
            'unit' => $unit,];
        $startAt = Carbon::parse($start);
        $dates = [];
        switch ($unit) {
            case '年':
                $dates = collect($startAt->yearsUntil($end, 1)->toArray())
                    ->map(function (Carbon $date) {
                        return $date->firstOfYear();
                    });
                break;
            case '月':
                $dates = collect($startAt->monthsUntil($end, 1)->toArray())
                    ->map(function (Carbon $date) {
                        return $date->firstOfMonth();
                    });
                break;
            case '日':
                $dates = collect($startAt->daysUntil($end, 1)->toArray())
                    ->map(function (Carbon $date) {
                        return $date->startOfDay();
                    });
                break;
            default:
        }

        foreach ($dates as $day) {
            $condition['data'][$day->toDateString()] = $ownerIds;
        }
        return $condition;
    }

    public function dataFromOrder($queryCondition)
    {
        $orderSqlBuilder = Order::query()->selectRaw("owner_id,warehouse_id,shop_id,logistic_id,count(1) as amounts ,DATE_FORMAT(created_at,'%Y-%m-%d') as date_target");
        foreach ($queryCondition['data'] as $dateStr => $ownerIds) {
            $orderSqlBuilder->orWhere(function ($query) use ($ownerIds, $dateStr) {
                $query->whereIn('owner_id', $ownerIds)->where('created_at', '>=', $dateStr)->where('created_at', '<', Carbon::parse($dateStr)->addDay()->toDateString())->where('wms_status', '订单完成');
            });
        }
        $dataFromOrder = $orderSqlBuilder->groupBy(['owner_id', 'warehouse_id', 'shop_id', 'logistic_id', 'date_target'])->get();
        $dataFromMiddleTable = $this->ordersToOrderCountingRecords($dataFromOrder, $queryCondition['unit']);
        $this->setDefultData($dataFromMiddleTable, $queryCondition);
        $this->insertIntoMiddleTable($dataFromMiddleTable, $queryCondition['unit']);
        $this->insertIntoCache($dataFromMiddleTable, $queryCondition['unit']);
        return $dataFromMiddleTable;
    }

    public function insertIntoMiddleTable($data, $unit)
    {

        $filter = $data->filter(function ($item) use ($unit) {
            return $this->isNotCurrentDate($item->date_target, $unit);
        });
        $filter->chunk(1000)->each(function ($item) {
            OrderCountingRecord::query()->insert($item->toArray());
        });

    }

    public function insertIntoCache($collect, $unit)
    {
        $map = [];
        $collect->each(function ($order) use ($unit, &$map) {
            $key = "order_counting_records_{$order->date_target}_{$order->owner_id}_{$unit}";
            if (empty($map[$key])) $map[$key] = [];
            $map[$key][] = $order;
        });

        foreach ($map as $key => $orders) {
            Cache::put($key, collect($orders), $this->getTtl($orders[0]['date_target'], $unit));
        }
    }

    public function isNotCurrentDate($dateStr, $unit): bool
    {
        switch ($unit) {
            case '月':
                return !Carbon::parse($dateStr)->isCurrentMonth();
            case '年':
                return !Carbon::parse($dateStr)->isCurrentYear();
            default:
                return !Carbon::parse($dateStr)->isCurrentDay();
        }
    }


    public function unQueryCondition($dataFromMiddleTable, $unQueryCondition)
    {
        $map = [];
        $dataFromMiddleTable->each(function ($item) use (&$map) {
            $key = 'owner_id=' . $item->owner_id . ' date_target' . $item->date_target;
            $map[$key] = true;
        });
        foreach ($unQueryCondition['data'] as $dateStr => $ownerIds) {
            foreach ($ownerIds as $key => $ownerId) {
                $key1 = 'owner_id=' . $ownerId . ' date_target' . $dateStr;
                if (isset($map[$key1])) {
                    //中间表查找到数据
                    unset($unQueryCondition['data'][$dateStr][$key]);
                }
            }
            if (empty($unQueryCondition['data'][$dateStr])) {
                unset($unQueryCondition['data'][$dateStr]);
            }
        }
        return $unQueryCondition;
    }


    public function ordersToOrderCountingRecords($dataFromOrder, $unit)
    {
        $dataFromMiddleTable = collect();
        //当日当周当月当年的数据不能插入到中间表
        $dateTime = Carbon::now()->toDateTimeString();
        foreach ($dataFromOrder as $order) {
            $carbon = Carbon::parse($order->date_target);
            $year = $carbon->year;
            $week = $carbon->week;
            $month = $carbon->month;
            $dataFromMiddleTable->push(new OrderCountingRecord([
                'owner_id' => $order->owner_id,
                'shop_id' => $order->shop_id,
                'warehouse_id' => $order->warehouse_id,
                'logistic_id' => $order->logistic_id,
                'date_target' => $order->date_target,
                'counting_unit' => $unit,
                'amount' => $order->amounts,
                'week' => $year . '-' . $week,
                'month' => $year . '-' . $month,
                'year' => $year . '',
                'created_at' => $dateTime,
                'updated_at' => $dateTime,
            ]));
        }
        return $dataFromMiddleTable;
    }

    public function count($orderCountingRecords_days, $unit)
    {
        $isEnd = false;
        $result = collect();
        $item = null;
        $amount = 0;
        foreach ($orderCountingRecords_days as $itemToCount) {
            if ($itemToCount instanceof OrderCountingRecord) {
                if (!$isEnd) {
                    $isEnd = true;
                    $item = clone($itemToCount);
                }
                $amount += $itemToCount->amount;
            } else {
                $result = $result->concat($this->count($itemToCount, $unit));
            }
        }
        if ($isEnd) {
            switch ($unit) {
                case "日":
                    $item['date_target'] = Carbon::parse($item['date_target'])->firstOfMonth()->toDateString();
                    $item['counting_unit'] = '月';
                    break;
                case "月":
                    $item['date_target'] = Carbon::parse($item['date_target'])->firstOfYear()->toDateString();
                    $item['counting_unit'] = '年';
                    break;
            }
            $item['amount'] = $amount;
            return $result->push(new OrderCountingRecord([
                'owner_id' => $item->owner_id,
                'shop_id' => $item->shop_id,
                'warehouse_id' => $item->warehouse_id,
                'logistic_id' => $item->logistic_id,
                'date_target' => $item->date_target,
                'counting_unit' => $item->counting_unit,
                'amount' => $item->amount,
                'year' => $item->year,
                'month' => $item->month,
                'week' => $item->week
            ]));
        }
        return $result;
    }

    public function turnGradingUpToLow($orderCountingRecords_days, $unit, $grading)
    {
        $orderCountingRecords_days = $orderCountingRecords_days->groupBy(['owner_id', 'shop_id', 'warehouse_id', 'logistic_id', $grading]);
        return $this->count($orderCountingRecords_days, $unit);
    }

    public function setDefultData($dataFromMiddleTable, $queryCondition)
    {
        $map = [];
        foreach ($dataFromMiddleTable as $data) {
            if (empty($map[$data->date_target . '-' . $data->owner_id])) $map[$data->date_target . '-' . $data->owner_id] = true;
        }
        foreach ($queryCondition['data'] as $dateStr => $ownerIds) {
            foreach ($ownerIds as $ownerId) {
                if (empty($map[$dateStr . '-' . $ownerId]) && Carbon::parse($dateStr)->isBefore(Carbon::now())) {
                    $carbon = Carbon::parse($dateStr);
                    $year = $carbon->year;
                    $week = $carbon->week;
                    $month = $carbon->month;
                    $dataFromMiddleTable->push(new OrderCountingRecord([
                        'owner_id' => $ownerId,
                        'shop_id' => null,
                        'warehouse_id' => null,
                        'logistic_id' => null,
                        'date_target' => $dateStr,
                        'counting_unit' => $queryCondition['unit'],
                        'amount' => 0,
                        'week' => $year . '-' . $week,
                        'month' => $year . '-' . $month,
                        'year' => $year . '',
                        'created_at' => $carbon->toDateString(),
                        'updated_at' => $carbon->toDateString(),
                    ]));
                }
            }
        }
    }

    private function getTtl($dateStr, $unit)
    {
        if ($this->isNotCurrentDate($dateStr, $unit)) {
            $ttl = config('cache.expirations.forever');//非当前日期的缓存为永久
        } else {
            $ttl = config('cache.expirations.orderCountingRecord');//当前日期缓存为1800s
        }
        return $ttl;
    }
}