duanju_manage/modules/CpManage/Http/Controllers/CpSubscribeStatisticDataController.php

<?php

namespace Modules\CpManage\Http\Controllers;

use Catch\Base\CatchController;
use Illuminate\Foundation\Validation\ValidatesRequests;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;


class CpSubscribeStatisticDataController extends CatchController
{
    use ValidatesRequests, UserTrait;


    /**
     * cp结算数据中心列表
     * @param Request $request
     * @return \Illuminate\Contracts\Pagination\LengthAwarePaginator
     * @throws \Illuminate\Validation\ValidationException
     */
    public function list(Request $request) {
        $this->validate($request, [
            'book_name' => 'nullable|string|max:256',
            'bid' => 'nullable|integer|min:1',
            'cp_name' => 'nullable|string|min:1',
            'start_date' => 'nullable|date_format:Y-m-d',
            'end_date' => 'nullable|date_format:Y-m-d',
            'page' => 'required|integer|min:1',
            'limit' => 'integer|min:10|max:300',
            'is_export' => 'nullable|integer|in:0,1',   // 是否导出全部, 1-是,0-否
            'final_amount_gt0' => 'nullable|integer|in:0,1', // 应结算金额是否要大于0,
            'book_settlement_type' => 'nullable|in:share,bottomline', // 书籍合作模式
        ]);
        $bookName = $request->input('book_name');
        $bid = $request->input('bid');
        $cpName = $request->input('cp_name');
        $startDate = $request->input('start_date');
        $endDate = $request->input('end_date');
        $perPage = $request->input('limit', 15);
        $isExport = $request->input('is_export', 0);
        $finalAmountGt0 = $request->input('final_amount_gt0', 0);
        $bookSettlementType = $request->input('book_settlement_type');
        $settlementTypes = [
            'buyout' => '买断', 'bottomline' => '保底', 'share' => '分成', 'zhuishuyun' => '追书云计算'
        ];

        $cpName = $this->getUserCpName() ?? $cpName;


        $sql = DB::table('cp_subscribe_statistic_data as statistic')
            ->leftJoin('books as book', 'statistic.bid' , 'book.id')
            ->when($bookName, function ($query, $bookName) {
                return $query->where('book.name', 'like', '%'.$bookName. '%');
            })->when($bid , function ($query, $bid) {
                return $query->where(['statistic.bid' => $bid]);
            })->when($cpName, function ($query, $cpName) {
                return $query->where(['statistic.cp_name' => $cpName]);
            })->when($startDate, function ($query, $startDate) {
                $realStartDate = date_add(date_create($startDate), date_interval_create_from_date_string('1 day'))
                    ->format('Y-m-d');
                return $query->where('statistic.calculate_date', '>=', $realStartDate);
            })->when($endDate, function ($query, $endDate) {
                $realEndDate = date_add(date_create($endDate), date_interval_create_from_date_string('1 day'))
                    ->format('Y-m-d');
                return $query->where('statistic.calculate_date', '<=', $realEndDate);
            })->when($finalAmountGt0, function ($query) {
                return $query->where('statistic.yesterday_final_amount', '>', 0);
            })->when($bookSettlementType, function ($query, $bookSettlementType){
                return $query->where('statistic.book_settlement_type', $bookSettlementType);
            })
            ->select('statistic.bid', 'book.name as book_name', 'statistic.cp_name',
            'statistic.yesterday_total_coins', 'statistic.yesterday_available_amount', 'statistic.yesterday_final_amount',
            'statistic.calculate_date', 'statistic.book_settlement_type')
            ->orderBy('statistic.calculate_date', 'desc')
            ->orderBy('statistic.bid', 'desc');


        if($isExport) {
            return $sql->get()->map(function ($item) use($settlementTypes) {
                $item->date = date_sub(date_create($item->calculate_date), date_interval_create_from_date_string('1 day'))
                    ->format('Y-m-d');
                $item->book_settlement_type_str = $settlementTypes[$item->book_settlement_type] ?? '';
                return $item;
            });
        } else {
            return $sql->paginate($perPage)->through(function ($item) use ($settlementTypes) {
                $item->date = date_sub(date_create($item->calculate_date), date_interval_create_from_date_string('1 day'))
                    ->format('Y-m-d');
                $item->book_settlement_type_str = $settlementTypes[$item->book_settlement_type] ?? '';
                return $item;
            });
        }



    }

    public function listStatistic(Request $request) {
        $this->validate($request, [
            'book_name' => 'nullable|string|max:256',
            'bid' => 'nullable|integer|min:1',
            'cp_name' => 'nullable|string|min:1',
            'start_date' => 'nullable|date_format:Y-m-d',
            'end_date' => 'nullable|date_format:Y-m-d',
            'book_settlement_type' => 'nullable|in:share,bottomline'
        ]);
        $bookName = $request->input('book_name');
        $bid = $request->input('bid');
        $cpName = $request->input('cp_name');
        $startDate = $request->input('start_date');
        $endDate = $request->input('end_date');
        $bookSettlementType = $request->input('book_settlement_type');

        $cpName = $this->getUserCpName() ?? $cpName;

        $res = DB::table('cp_subscribe_statistic_data as statistic')
            ->leftJoin('books as book', 'statistic.bid' , 'book.id')
            ->when($bookName, function ($query, $bookName) {
                return $query->where('book.name', 'like', '%'.$bookName. '%');
            })->when($bid , function ($query, $bid) {
                return $query->where(['statistic.bid' => $bid]);
            })->when($cpName, function ($query, $cpName) {
                return $query->where(['statistic.cp_name' => $cpName]);
            })->when($startDate, function ($query, $startDate) {
                $realStartDate = date_add(date_create($startDate), date_interval_create_from_date_string('1 day'))
                    ->format('Y-m-d');
                return $query->where('statistic.calculate_date', '>=', $realStartDate);
            })->when($endDate, function ($query, $endDate) {
                $realEndDate = date_add(date_create($endDate), date_interval_create_from_date_string('1 day'))
                    ->format('Y-m-d');
                return $query->where('statistic.calculate_date', '<=', $realEndDate);
            })->when($bookSettlementType, function ($query, $bookSettlementType){
                return $query->where('statistic.book_settlement_type', $bookSettlementType);
            })
            ->select(
                DB::raw('sum(yesterday_total_coins) as yesterday_total_coins'),
                DB::raw('sum(yesterday_available_amount) as yesterday_available_amount'),
                DB::raw('sum(yesterday_final_amount) as yesterday_final_amount')
            )->first();

        return $res;
    }

    /**
     * cp结算列表
     * @param Request $request
     * @return \Illuminate\Contracts\Pagination\LengthAwarePaginator
     * @throws \Illuminate\Validation\ValidationException
     */
    public function monthList(Request $request) {
        $this->validate($request, [
            'cp_name' => 'nullable|string',
            'start_month' => 'nullable|date_format:Y-m',
            'end_month' => 'nullable|date_format:Y-m',
            'page' => 'required|integer|min:1',
            'limit' => 'integer|min:10',
            'is_export' => 'integer|in:0,1'
        ]);

        $cpName = $request->input('cp_name');
        $startMonth = $request->input('start_month');
        $endMonth = $request->input('end_month');
        $isExport = $request->input('is_export', 0);
        $finalStateMap = ['notCheck' => '未结算', 'done' => '已结算'];

        $cpName = $this->getUserCpName() ?? $cpName;
        $sql =  DB::table('cp_subscribe_month_statistic_data as data')
            ->leftJoin('cps as cp' , 'cp.cp_name', 'data.cp_name')
            ->when($cpName, function ($query, $cpName){
                return $query->where(['data.cp_name' => $cpName]);
            })->when($startMonth, function ($query, $startMonth){
                return $query->where('data.month', '>=', $startMonth);
            })->when($endMonth, function ($query, $endMonth){
                return $query->where('data.month', '<=', $endMonth);
            })
            ->select('data.id','data.month', 'cp.cp_id', 'cp.cp_name', 'cp.cp_company',
            'data.book_num', 'data.final_amount', 'data.final_state', 'data.final_time')
            ->orderBy('data.month', 'desc')
            ->orderBy('cp.cp_id', 'desc');


        if($isExport) {
            return $sql->get()->map(function ($item) use ($finalStateMap){
                $item->final_state_str = $finalStateMap[$item->final_state] ?? '';
                return $item;
            });
        }else {
            return $sql->paginate($request->integer('limit', 10));
        }
    }

    public function saveFinalState(Request $request){
        $this->validate($request, [
            'id' => 'required|integer|min:1',
            'final_state' => 'required|string|in:done'
        ]);
        $now = date('Y-m-d H:i:s');
        DB::table('cp_subscribe_month_statistic_data')
            ->where(['id' => $request->input('id'), 'final_state' => 'notCheck'])
            ->update([
                'final_state' => $request->input('final_state'),
                'final_time' => $now,
                'updated_at' => $now,
            ]);
        return 'ok';
    }

    /**
     * 导出某个cp某个月的各个书籍的应结算金额
     * @param Request $request
     * @return \Illuminate\Support\Collection
     * @throws \Illuminate\Validation\ValidationException
     */
    public function listCpMonthFinalAmount(Request $request) {
        $this->validate($request, [
            'cp_name' => 'required|string',
            'month' => 'required|date_format:Y-m'
        ]);
        $cpName = $request->input('cp_name');
        $cpName = $this->getUserCpName() ?? $cpName;
        return DB::table('cp_book_month_final_amounts as mfa')
            ->leftJoin('books', 'books.id', 'mfa.bid')
            ->where([
                'mfa.is_enabled' => 1,
                'mfa.cp_name' => $cpName,
                'mfa.month' => $request->input('month')
            ])
            ->select('mfa.id', 'mfa.bid', 'books.name as book_name', 'mfa.final_amount', 'mfa.cp_name','mfa.month')
            ->get();


    }
}