baoshi-was/app/Services/OwnerStoreFeeReportService.php

<?php

namespace App\Services;

use App\Owner;
use App\OwnerBillReport;
use App\OwnerBillReportArchive;
use App\OwnerPriceOperation;
use App\Traits\ServiceAppAop;
use App\OwnerStoreFeeReport;
use Carbon\Carbon;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\DB;

class OwnerStoreFeeReportService implements \App\Interfaces\SettlementBillReportInterface
{
    const TYPE = '入库费-合计';

    use ServiceAppAop;
    use \App\Traits\SettlementBillServiceTrait;

    protected $modelClass = OwnerStoreFeeReport::class;

    /** @var  $archiveService OwnerBillReportArchiveService */
    private $archiveService;
    /** @var  $detailService OwnerStoreFeeDetailService */
    private $detailService;


    /**
     * 生成报表数据
     * 如果参数$counting_month为空 统计上一个月的
     * 如果参数$counting_month为2021-01-01 则统计2021-01-01 -- 2021-01-31之间的数据
     * @param null $counting_month 统计月份，默认统计上个月的 2021-05-01
     */
    public function recordReport($counting_month = null, array $ownerIds = [])
    {
        $this->detailService = app('OwnerStoreFeeDetailService');
        if (is_null($counting_month)) {
            //默认统计上个月的数据
            $counting_month = now()->subMonth()->startOfMonth()->toDateString();
        }
        list($start, $end) = $this->getStartAndEnd($counting_month);
        $builder = DB::table('owner_fee_operations')
            ->leftJoin('owner_fee_operation_details', 'owner_fee_operations.id', '=', 'owner_fee_operation_details.owner_fee_operation_id')
            ->leftJoin('owner_price_operations', 'owner_fee_operations.model_id', '=', 'owner_price_operations.id')
            ->selectRaw("
                DATE_FORMAT(owner_fee_operations.worked_at,'%Y-%m') as counting_month,
                owner_fee_operation_details.unit_id,
                owner_fee_operation_details.price,
                owner_price_operations.name,
                sum(owner_fee_operation_details.amount) as amounts ,
                owner_fee_operations.owner_id,
                owner_fee_operations.model_id,
                sum(owner_fee_operation_details.price*owner_fee_operation_details.amount) as fee,
                sum(owner_fee_operation_details.price*owner_fee_operation_details.amount*owner_fee_operations.tax_rate) as tax_fee
                ")
            ->whereBetween('owner_fee_operations.worked_at', [$start, $end])
            ->whereNotNull('owner_fee_operation_details.price')
            ->whereNotNull('owner_fee_operation_details.amount')
            ->whereIn('model_id', OwnerPriceOperation::query()->select('id')->where('operation_type', '入库'));
        if (!empty($ownerIds)) {
            $builder->whereIn('owner_fee_operations.owner_id', $ownerIds);
        }
        $details = $builder->groupBy(
            'counting_month',
            'owner_id',
            'unit_id',
            'price',
            'model_id'
        )
            ->get();
        $reports = [];
        foreach ($details as $detail) {
            $counting_month = Carbon::parse($detail->counting_month)->startOfMonth()->toDateString();
            $ownerBillReport = OwnerBillReport::query()
                ->selectRaw("id")
                ->where('owner_id', $detail->owner_id)
                ->where('counting_month', $counting_month)->first();
            $reports[] = [
                'owner_bill_report_id' => $ownerBillReport->id ?? 1,
                'owner_id' => $detail->owner_id,
                'counting_month' => $counting_month,
                'unit_id' => $detail->unit_id,
                'unit_price' => $detail->price,
                'amount' => $detail->amounts,
                'fee' => $detail->fee,
                'work_name' => $detail->name,
                'model_id' => $detail->model_id,
                'tax_fee' => $detail->tax_fee,
            ];
        }
        $reports_chunked = array_chunk($reports, 1000);
        //保证幂等性 插入前删除该月的统计数据
        OwnerStoreFeeReport::query()->where('counting_month', $counting_month)->delete();

        foreach ($reports_chunked as $items) {
            OwnerStoreFeeReport::query()->insertOrIgnore($items);
        }

        //没有数量的计费模型填充0数据
        //查询到全部的货主
        $owners = Owner::query()->with('ownerPriceOperations.items')->get();
        foreach ($owners as $owner) {
            $owner_id = $owner->id;
            foreach ($owner->ownerPriceOperations as $ownerPriceOperation) {
                $ownerPriceOperation_id = $ownerPriceOperation->id;
                $has_report = OwnerStoreFeeReport::query()
                    ->where('owner_id', $owner_id)
                    ->where('model_id', $ownerPriceOperation_id)
                    ->where('counting_month', $counting_month)
                    ->exists();
                if ($has_report) continue;

//                foreach ($ownerPriceOperation->items as $ownerPriceOperationItem) {
                OwnerStoreFeeReport::query()->insert([
                    'owner_bill_report_id' => 1,
                    'owner_id' => $owner_id,
                    'counting_month' => $counting_month, //统计月份
                    'unit_id' => 4, //件
                    'unit_price' => $ownerPriceOperation->items[0]->unit_price ?? 0, //单价
                    'amount' => 0, //数量
                    'fee' => 0,//费用
                    'work_name' => $ownerPriceOperation->name,//作业名称
                    'model_id' => $ownerPriceOperation_id,//计费模型
                    'tax_fee' => 0,//税费
                ]);
            }
        }
    }

    public function get(array $kvPairs): array
    {
        $this->archiveService = app('OwnerBillReportArchiveService');
        $this->detailService = app('OwnerStoreFeeDetailService');
        if ($this->archiveService->isArchived($kvPairs['counting_month'], $kvPairs['owner_id'], $kvPairs['type']) == 1) {
            //查询存档数据
            $archived = $this->archiveService->get($kvPairs);
            $reports = collect($archived->information['reports']);
            $totalAmount = $archived->information['totalAmount'];
            $totalFee = $archived->information['totalFee'];
            $owner_price_operation_fees = collect($archived->information['owner_price_operation_fees']);
        } else {
            $reports = $this->getSql($kvPairs['owner_id'], $kvPairs['counting_month'])->get();
            $totalAmount = $reports->sum('amount');
            $totalFee = number_format($reports->sum('fee'), 2);
            $owner_price_operation_fees = OwnerStoreFeeReport::query()
                ->selectRaw("sum(fee) as fee,work_name")
                ->where('owner_id', $kvPairs['owner_id'])
                ->where('counting_month', $kvPairs['counting_month'])
                ->groupBy('work_name')->get();
        }
        return array($reports, $totalAmount, $totalFee, $owner_price_operation_fees);
    }

    /**
     * @param $owner_id
     * @param $counting_month
     * @return Builder
     */
    public function getSql($owner_id, $counting_month): Builder
    {
        return OwnerStoreFeeReport::query()
            ->with(['unit:id,name'])
            ->where('owner_id', $owner_id)
            ->where('counting_month', $counting_month);
    }

    public function switchType($type)
    {
        // TODO: Implement switchType() method.
    }

    public function buildExport($details): array
    {
        // TODO: Implement buildExport() method.
    }

    public function confirmBill($counting_month, $owner_id)
    {
        $billReport = OwnerBillReport::query()
            ->select('storage_fee', 'id')
            ->where('owner_id', $owner_id)
            ->where('counting_month', $counting_month)
            ->firstOr(function () {
                return new OwnerBillReport();
            });
        list($reports, $totalAmount, $totalFee, $owner_price_operation_fees) = $this->get([
            'owner_id' => $owner_id,
            'counting_month' => $counting_month,
            'type' => $this::TYPE,
        ]);
        OwnerBillReportArchive::query()->create([
            'owner_bill_report_id' => $billReport->id ?? null,
            'owner_id' => $owner_id,
            'counting_month' => $counting_month,
            'type' => $this::TYPE,
            'archiver_id' => auth()->id(),
            'archived_at' => now(),
            'information' => [
                'reports' => $reports,
                'totalAmount' => $totalAmount,
                'totalFee' => $totalFee,
                'owner_price_operation_fees' => $owner_price_operation_fees
            ],
        ]);
        $this->confirmBillFeeTotal($counting_month, $owner_id);
    }


}