<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use App\Http\Requests\ReportRequest;
use App\Models\Report;
use App\Models\User;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Auth;

class TotalReportController extends Controller
{
    public function quantity(Request $request)
    {
        // チェックポイント保存
        session(['checkpoint' => url()->full()]);

        // パラメータ取得
        $params = $request->all();

        // パラメータ初期値
        if (empty($params['user_id'])) {
          $params['user_id'] = Auth::user()->id;
        }
        if (empty($params['start_at'])) {
          $params['start_at'] = date('Y-m');
        }

        // DBよりReportテーブルの値を検索
        $totals = DB::table('reports')
            ->selectRaw('DATE_FORMAT(reports.start_at, "%Y%m%d") AS date')
            ->selectRaw('SUM(CASE report_waypoints.unit WHEN 1 THEN report_waypoints.quantity ELSE 0 END) AS sum_quantity_unit_1')
            ->selectRaw('SUM(CASE report_waypoints.unit WHEN 2 THEN report_waypoints.quantity ELSE 0 END) AS sum_quantity_unit_2')
            ->selectRaw('SUM(CASE report_waypoints.unit WHEN 3 THEN report_waypoints.quantity ELSE 0 END) AS sum_quantity_unit_3')
            ->selectRaw('SUM(CASE report_waypoints.unit WHEN 4 THEN report_waypoints.quantity ELSE 0 END) AS sum_quantity_unit_4')
            ->selectRaw('SUM(CASE report_waypoints.unit WHEN 5 THEN report_waypoints.quantity ELSE 0 END) AS sum_quantity_unit_5')
            ->leftJoin('report_waypoints', 'reports.id','=','report_waypoints.report_id')
            ->whereYear('reports.start_at', '=', date('Y', strtotime($params['start_at'].'-01')))
            ->whereMonth('reports.start_at', '=', date('m', strtotime($params['start_at'].'-01')))
            ->whereNotNull('report_waypoints.quantity')
            ->when($params['user_id'] !== 'all', function($query) use ($params) {
              return $query->where('user_id', '=', ($params['user_id']));
            })
            ->groupBy('date')
            ->orderBy('date', 'asc')
            ->get();
            
        // セレクトボックス用にUsersを取得
        $users = User::get();

        // 取得した値をビュー「total/report/quantity」に渡す
        return view('total/report/quantity')
            ->with([
                'totals' => $totals,
                'params' => $params,
                'users' => $users,
            ]);
    }

    public function oil(Request $request)
    {
        // チェックポイント保存
        session(['checkpoint' => url()->full()]);

        // パラメータ取得
        $params = $request->all();

        // パラメータ初期値
        if (empty($params['user_id'])) {
          $params['user_id'] = Auth::user()->id;
        }
        if (empty($params['start_at'])) {
          $params['start_at'] = date('Y-m');
        }

        // DBよりReportテーブルの値を検索
        $totals = DB::table('reports')
            ->selectRaw('DATE_FORMAT(reports.start_at, "%Y%m%d") AS date')
            ->selectRaw('SUM(reports.light_oil) AS sum_light_oil')
            ->selectRaw('SUM(reports.adblue) AS sum_adblue')
            ->selectRaw('SUM(reports.adblue_handy) AS sum_adblue_handy')
            ->whereYear('reports.start_at', '=', date('Y', strtotime($params['start_at'].'-01')))
            ->whereMonth('reports.start_at', '=', date('m', strtotime($params['start_at'].'-01')))
            ->when($params['user_id'] !== 'all', function($query) use ($params) {
              return $query->where('user_id', '=', ($params['user_id']));
            })
            ->groupBy('date')
            ->orderBy('date', 'asc')
            ->get();
            
        // セレクトボックス用にUsersを取得
        $users = User::get();

        // 取得した値をビュー「total/report/oil」に渡す
        return view('total/report/oil')
            ->with([
                'totals' => $totals,
                'params' => $params,
                'users' => $users,
            ]);
    }

    public function highway(Request $request)
    {
        // チェックポイント保存
        session(['checkpoint' => url()->full()]);

        // パラメータ取得
        $params = $request->all();

        // パラメータ初期値
        if (empty($params['user_id'])) {
          $params['user_id'] = Auth::user()->id;
        }
        if (empty($params['start_at'])) {
          $params['start_at'] = date('Y-m');
        }

        // DBよりReportテーブルの値を検索
        $totals = DB::table('reports')
            ->selectRaw('DATE_FORMAT(reports.start_at, "%Y%m%d") AS date')
            ->selectRaw('SUM(CASE report_waypoints.highway_payment WHEN 1 THEN report_waypoints.highway_money ELSE 0 END) AS sum_highway_payment_1')
            ->selectRaw('SUM(CASE report_waypoints.highway_payment WHEN 2 THEN report_waypoints.highway_money ELSE 0 END) AS sum_highway_payment_2')
            ->leftJoin('report_waypoints', 'reports.id','=','report_waypoints.report_id')
            ->whereYear('reports.start_at', '=', date('Y', strtotime($params['start_at'].'-01')))
            ->whereMonth('reports.start_at', '=', date('m', strtotime($params['start_at'].'-01')))
            ->when($params['user_id'] !== 'all', function($query) use ($params) {
              return $query->where('user_id', '=', ($params['user_id']));
            })
            ->groupBy('date')
            ->orderBy('date', 'asc')
            ->get();
            
        // セレクトボックス用にUsersを取得
        $users = User::get();

        // 取得した値をビュー「total/report/highway
        return view('total/report/highway')
            ->with([
                'totals' => $totals,
                'params' => $params,
                'users' => $users,
            ]);
    }
}
