admin_api/app/Http/Controllers/Admin/AgentController.php

<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use App\Util;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;

class AgentController extends Controller
{
    public function index()
    {

    }
    public function AriesCount(Request $request)
    {
        $yestoday=Carbon::yesterday()->format("Y-m-d");
        $today=Carbon::now()->format("Y-m-d");
        $tommorrow=Carbon::tomorrow()->format("Y-m-d");
        $start_time=$request->start_time?Carbon::parse($request->start_time)->format("Y-m-d"):$yestoday;
        $end_time=$request->end_time?Carbon::parse($request->end_time)->format("Y-m-d"):$today;

//        if($start_time==$today){
//            $end_time=$tommorrow;
//        }
//        if($end_time=$today){
//            $start_time=$yestoday;
//        }
//
//        if($start_time==$end_time){
//            $start_time=$yestoday;
//            $end_time=$today;
//        }
//        $ccs='103,104,113,106,123,127,131,148,141,162,163,144,149,150,151,164,153,154,155,167,168,169,172,174,179,304,316,317';
        $ccs=DB::table('QPPlatformDB.dbo.ChannelPackageName')->select('Channel')->where('UnionSign',1)->pluck('Channel')->toArray();
        $ccs=array_unique($ccs);
        $ccs=implode(",",$ccs);
        $res= DB::connection('sqlsrv')->select("
select
        o.Channel,
    cp.Remarks,
    ISNULL(o.amount, 0) AS amount,
    ISNULL(o.payment_fee, 0) AS payment_fee,
    ISNULL(o.amount-o.payment_fee , 0) AS amountReal,
    CAST(ISNULL(wd.WithDraw, 0) AS decimal(10,2)) AS WithDraw,
    CAST(ISNULL(wd.withdraw_fee, 0) AS decimal(10,2)) AS withdraw_fee,
    CAST(ISNULL(o.amount, 0) -ISNULL(o.payment_fee, 0)  - ISNULL(wd.WithDraw, 0)-ISNULL(wd.withdraw_fee, 0) AS decimal(10,2)) AS NetAmount

    from
(select cast(sum(amount)/100 as decimal(10,2)) as amount,cast(sum(payment_fee)/100 as decimal(10,2)) as payment_fee,Channel from agent.dbo.[order] where pay_at<'$end_time' and pay_at>'$start_time' and pay_status=1 and  Channel in ($ccs) group by Channel) o
left join  (SELECT Channel,MAX(Remarks) AS Remarks   FROM      QPPlatformDB.dbo.ChannelPackageName group by Channel)  cp
on o.Channel=cp.Channel
left join (select cast(sum(WithDraw) as float)/100 as WithDraw, cast(sum(withdraw_fee) as float)/100 as withdraw_fee,AI.Channel from QPAccountsDB.dbo.OrderWithDraw ow left join QPAccountsDB.dbo.AccountsInfo AI
    on ow.UserID = AI.UserID where AI.Channel in ($ccs)
                               and ow.finishDate>'$start_time' and ow.finishDate<'$end_time' and ow.State=2 group by AI.Channel) wd
on o.Channel=wd.Channel
ORDER BY o.Channel");

        $total=(object)[];
        $total->amount=0;
        $total->amountReal=0;
        $total->WithDraw=0;
        $total->withdraw_fee=0;
        $total->payment_fee=0;
        $total->NetAmount=0;
        $total=array_reduce($res,function ($carry,$item){
            $carry->amount+=$item->amount;
            $carry->amountReal+=$item->amountReal;
            $carry->payment_fee+=$item->payment_fee;
            $carry->WithDraw+=$item->WithDraw;
            $carry->withdraw_fee+=$item->withdraw_fee;
            $carry->NetAmount+=$item->NetAmount;

            return $carry;
        },$total);
        $total->Channel=0;
        $total->Remarks='总重';
        $res[]=$total;
        return view('admin.agent.AriesCount',compact('res','start_time','end_time'));
    }
}