/app/Http/Controllers/CampaignController.php

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use \Carbon\Carbon;
use DB;

class CampaignController extends Controller
{
    public function __construct()
    {
        $this->data = [
            'startToday' => Carbon::now()->format('Y-m-d 00:00:00'),
            'endToday'   => Carbon::now()->format('Y-m-d 23:59:59'),
            'now'        => Carbon::now()->format('Y-m-d H:i:s')
        ];
    }

    public function index(Request $request)
    {
        $campaignId = $request->id;

        $campaign = (
            DB::table('Campaign')
                ->select([
                    'campaignId',
                    'campaignName',
                    'startDate',
                    'endDate'
                ])
                ->where('campaignId', $campaignId)
                ->where('status', 'active')
                ->orderBy('createdAt', 'ASC')
                ->first()
        );

        $contractIds = (
            DB::table('Contract')
                ->where('campaignId', $campaign->campaignId)
                ->where('status', 'approved')
                ->groupBy('contractId')
                ->pluck('contractId')
        );

        $trips = (
            DB::table('Trip')
                ->join('User', 'Trip.driverId', '=', 'User.userId')
                ->select(DB::raw('
                    User.userId as driverId,
                    User.fullname as driverName,
                    User.email as driverEmail,
                    SUM(Trip.totalCredit) as totalCredit,
                    SUM(Trip.totalDistance) as totalDistance,
                    SUM(Trip.totalImpression) as totalImpression
                '))
                ->whereIn('Trip.contractId', $contractIds)
                ->where('Trip.latitude', '!=', 0)
                ->where('Trip.longitude', '!=', 0)
                ->where('Trip.status', '!=', 'closed')
                ->where('Trip.createdAt', '>=', $this->data['startToday'])
                ->orderBy('User.fullname', 'ASC')
                ->groupBy(['Trip.driverId', 'Trip.contractId'])
                ->get()
        );


        $data = [
            'title'      => $campaign->campaignName,
            'campaignId' => $campaign->campaignId,
            'online'     => count($trips),
            'total'      => count($contractIds),
            'percentage' => round(count($trips) / count($contractIds) * 100),
            'trips'      => $trips
        ];

        return view('campaign.index', $data);
    }

    public function start(Request $request)
    {
        $campaign = (
            DB::table('Campaign')
                ->select([
                    'campaignId',
                    'areaId'
                ])
                ->where('campaignId', $request->id)
                ->where('status', 'active')
                ->orderBy('createdAt', 'ASC')
                ->first()
        );

        $lola = (
            DB::table('Lola')
                ->where('areaId', $campaign->areaId)
                ->first()
        );

        if ($lola) {
            $contractIds = (
                DB::table('Contract')
                    ->where('campaignId', $campaign->campaignId)
                    ->where('status', 'approved')
                    ->groupBy('contractId')
                    ->pluck('contractId')
            );

            // START:stop trip before today
            $beforeTodayIds = (
                DB::table('Trip')
                    ->whereIn('contractId', $contractIds)
                    ->where('status', '!=', 'closed')
                    ->where('createdAt', '<', $this->data['startToday'])
                    ->pluck('tripId')
            );

            DB::table('Trip')
                ->whereIn('tripId', $beforeTodayIds)
                ->update([
                    'status' => 'closed'
                ]);
            // END:stop trip before today

            $todayOnTrips = (
                DB::table('Trip')
                    ->select(DB::raw('
                        Trip.driverId,
                        Trip.contractId,
                        SUM(Trip.totalCredit) as totalCredit,
                        SUM(Trip.totalDistance) as totalDistance,
                        SUM(Trip.totalImpression) as totalImpression
                    '))
                    ->whereIn('Trip.contractId', $contractIds)
                    ->where('Trip.latitude', '!=', 0)
                    ->where('Trip.longitude', '!=', 0)
                    ->where('Trip.status', '!=', 'closed')
                    ->where('Trip.createdAt', '>=', $this->data['startToday'])
                    ->groupBy(['Trip.driverId', 'Trip.contractId'])
                    ->get()
            );

            // START: stop trip today with lola = 0
            $todayLola0Ids = (
                DB::table('Trip')
                    ->whereIn('contractId', $contractIds)
                    ->whereNotIn('contractId', $todayOnTrips->pluck('contractId'))
                    ->where('status', '!=', 'closed')
                    ->where('createdAt', '>=', $this->data['startToday'])
                    ->pluck('tripId')
            );

            DB::table('Trip')
                ->whereIn('tripId', $todayLola0Ids)
                ->update([
                    'status' => 'closed'
                ]);
            // END: stop trip today with lola = 0

            $limit = round(count($contractIds) * $request->percentage / 100) - count($todayOnTrips);

            $insertTrips = (
                DB::table('Contract')
                    ->select([
                        'driverId',
                        'contractId'
                    ])
                    ->where('campaignId', $campaign->campaignId)
                    ->whereNotIn('contractId', $todayOnTrips->pluck('contractId'))
                    ->where('status', 'approved')
                    ->inRandomOrder()
                    ->limit($limit <= 0 ? 0 : $limit)
                    ->get()
            );

            if (count($insertTrips)) {
                foreach ($insertTrips as $key => $insertTrip) {
                    DB::table('Trip')->insert([
                        'tripUUID'   => DB::select('SELECT UUID() AS UUID')[0]->UUID,
                        // 'latitude'   => -(rand(abs($lola->latMin) * 1000000, abs($lola->latMax) * 1000000) / 1000000),
                        // 'longitude'  => rand(abs($lola->longMin) * 1000000, abs($lola->longMax) * 1000000) / 1000000,
                        'latitude'   => rand($lola->latMin * 1000000, $lola->latMax * 1000000) / 1000000,
                        'longitude'  => rand($lola->longMin * 1000000, $lola->longMax * 1000000) / 1000000,
                        'driverId'   => $insertTrip->driverId,
                        'contractId' => $insertTrip->contractId,
                        'status'     => 'ongoing',
                        'createdAt'  => $this->data['now']
                    ]);
                }
            }

            $data = [
                'message' => 'Success',
                'status'  => true
            ];
        } else {
            $data = [
                'message' => 'Failed Start Trip, LOLA does not exists.',
                'status'  => false
            ];
        }

        return response()->json($data);
    }

    public function report(Request $request)
    {
        $campaignId = $request->id;

        $campaign = (
            DB::table('Campaign')
                ->select([
                    'campaignId',
                    'campaignName',
                    'startDate',
                    'endDate'
                ])
                ->where('campaignId', $campaignId)
                ->where('status', 'active')
                ->orderBy('createdAt', 'ASC')
                ->first()
        );

        $contractIds = (
            DB::table('Contract')
                ->where('campaignId', $campaign->campaignId)
                ->where('status', 'approved')
                ->groupBy('contractId')
                ->pluck('contractId')
        );

        $reports = (
            DB::table('Trip')
                ->select(DB::raw('
                    DATE(createdAt) as date,
                    SUM(Trip.totalDistance) as totalDistance
                '))
                ->whereIn('contractId', $contractIds)
                ->where('createdAt', '>=', $campaign->startDate)
                ->where('createdAt', '<', $campaign->endDate)
                ->groupBy(DB::raw('
                    DATE(createdAt)
                '))
                ->get()
        );

        $data = [
            'title'      => $campaign->campaignName,
            'campaignId' => $campaign->campaignId,
            'maxDate'    => Carbon::now()->format('Y-m-d'),
            'startDate'  => $campaign->startDate,
            'endDate'    => $campaign->endDate,
            'reports'    => $reports
        ];

        return view('campaign.report', $data);
    }

    public function inject(Request $request)
    {
        $date = $request->date;
        $startDate = Carbon::createFromFormat('Y-m-d H:i:s', $date . ' 00:00:00')->toDateTimeString();
        $endDate = Carbon::createFromFormat('Y-m-d H:i:s', $date . ' 23:59:59')->toDateTimeString();

        $campaign = (
            DB::table('Campaign')
                ->select([
                    'campaignId',
                    'areaId'
                ])
                ->where('campaignId', $request->id)
                ->where('status', 'active')
                ->orderBy('createdAt', 'ASC')
                ->first()
        );

        $lola = (
            DB::table('Lola')
                ->where('areaId', $campaign->areaId)
                ->first()
        );

        if ($lola) {
            $wrappingCredit = (
                DB::table('Wrapping')
                    ->where('campaignId', $campaign->campaignId)
                    ->where('status', 'active')
                    ->pluck('credit')
                    ->first()
            );

            $allContracts = (
                DB::table('Contract')
                    ->select([
                        'contractId',
                        'driverId'
                    ])
                    ->where('campaignId', $campaign->campaignId)
                    ->where('status', 'approved')
                    ->groupBy('contractId')
                    ->get()
            );

            $contractExists = (
                DB::table('Trip')
                    ->select(DB::raw('
                        contractId,
                        SUM(totalDistance) as totalDistance
                    '))
                    ->whereIn('contractId', $allContracts->pluck('contractId'))
                    ->where('createdAt', '>=', $startDate)
                    ->where('createdAt', '<=', $endDate)
                    ->groupBy('contractId')
                    ->get()
            );

            $contractNotExists = (
                DB::table('Contract')
                    ->select([
                        'contractId',
                        'driverId'
                    ])
                    ->whereNotIn('contractId', $contractExists->pluck('contractId'))
                    ->where('campaignId', $campaign->campaignId)
                    ->where('status', 'approved')
                    ->groupBy('contractId')
                    ->get()
            );

            foreach ($contractExists as $key => $contractExist) {
                $totalDistance = mt_rand(50000, 150000) - intval($contractExist->totalDistance);
                $totalCredit = round($totalDistance * intval($wrappingCredit) / 1000);
                $totalImpression = round($totalDistance * intval($wrappingCredit) * (mt_rand(1000, 3000) / 1000) / 1000);
                $driverId = DB::table('Contract')->where('contractId', $contractExist->contractId)->pluck('driverId')->first();
                $contractId = $contractExist->contractId;

                if ($totalDistance > 0 && $totalCredit > 0 && $totalImpression > 0 ) {
                    DB::table('Trip')->insert([
                        'tripUUID'        => DB::select('SELECT UUID() AS UUID')[0]->UUID,
                        'totalCredit'     => $totalCredit,
                        'totalDistance'   => $totalDistance,
                        'totalImpression' => $totalImpression,
                        'latitude'        => -(rand(abs($lola->latMin) * 1000000, abs($lola->latMax) * 1000000) / 1000000),
                        'longitude'       => rand(abs($lola->longMin) * 1000000, abs($lola->longMax) * 1000000) / 1000000,
                        'driverId'        => $driverId,
                        'contractId'      => $contractId,
                        'status'          => 'closed',
                        'createdAt'       => date("Y-m-d H:i:s", mt_rand(strtotime($startDate), strtotime($endDate)))
                    ]);
                }

            }

            foreach ($contractNotExists as $key => $contractNotExist) {
                $totalDistance = mt_rand(50000, 150000);
                $totalCredit = round($totalDistance * intval($wrappingCredit) / 1000);
                $totalImpression = round($totalDistance * intval($wrappingCredit) * (mt_rand(1000, 3000) / 1000) / 1000);
                $driverId = $contractNotExist->driverId;
                $contractId = $contractNotExist->contractId;

                DB::table('Trip')->insert([
                    'tripUUID'        => DB::select('SELECT UUID() AS UUID')[0]->UUID,
                    'totalCredit'     => $totalCredit,
                    'totalDistance'   => $totalDistance,
                    'totalImpression' => $totalImpression,
                    'latitude'        => -(rand(abs($lola->latMin) * 1000000, abs($lola->latMax) * 1000000) / 1000000),
                    'longitude'       => rand(abs($lola->longMin) * 1000000, abs($lola->longMax) * 1000000) / 1000000,
                    'driverId'        => $driverId,
                    'contractId'      => $contractId,
                    'status'          => 'closed',
                    'createdAt'       => date("Y-m-d H:i:s", mt_rand(strtotime($startDate), strtotime($endDate)))
                ]);
            }

            $data = [
                'message' => 'success',
                'status'  => true
            ];
        } else {
            $data = [
                'message' => 'Failed Start Trip, LOLA does not exists.',
                'status'  => false
            ];
        }

        return response()->json($data);
    }
}