<?php

namespace App\Http\Controllers;

use App\Models\Customer;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Auth;
use Illuminate\Support\Facades\DB;
use App\Exports\RiwayatVoucherExport;
use App\Exports\RiwayatVoucherCoinAllExport;
use Maatwebsite\Excel\Facades\Excel;
use DateTime;

class RiwayatVoucherGbController extends Controller
{
    public function __construct()
    {
        $this->middleware('auth');
        $this->middleware('permission:view-riwayatVoucherGB', ['only' => ['index','export','search']]);
    }
    public function index(Request $request)
    {
        $customer = Customer::all();
        $nowss = Carbon::now();
        $now = Carbon::now()->format('d-m-Y');
        $sevenDaysAgo = $nowss->subDays(7);
        $format_seven_days = $sevenDaysAgo->format('d-m-Y');
        return view('riwayat-voucher-gb.index', compact('customer', 'now', 'format_seven_days'));
    }
    public function search(Request $request)
    {
          $dateFrom = $request->input('dateFrom');
        $dateTos = $request->input('dateTo');
        $dateFrom_converted = DateTime::createFromFormat('d-m-Y', $dateFrom);
        $dateTo_converted = DateTime::createFromFormat('d-m-Y', $dateTos);
        if ($dateFrom_converted) {
            $request->merge(['dateFrom' => $dateFrom_converted->format('Y-m-d')]);
        }
        if ($dateTo_converted) {
            $request->merge(['dateTo' => $dateTo_converted->format('Y-m-d')]);
        }

       $dateTo = Carbon::parse($request->dateTo)->addDay();
       $date_from = $request->dateFrom;
       $date_to = $request->dateTo;
       $customers_data = [];
       $customer = $request->customer;
       $page = $request->page;
       $search = $request->input('search');
       if ($request->dateFrom > $request->dateTo){
           return redirect()->back()->with('danger', 'Format pengisian tanggal salah');
       }
       if ($request->dateFrom == null || $request->dateTo == null)
       {
           return redirect()->back()->with('danger', 'Tanggal Wajib Diisi');

       } else {
            if ($request->customer != null){
                $data = DB::table('customer_coupon as cc')->whereBetween('cc.created_at', [$date_from, $dateTo])
                    ->join('customer as c', 'cc.customer_id', 'c.customer_id')
                    ->join('voucher_promo_point as v', 'cc.coupon_id', 'v.id')
                    ->join('gerai as g', 'cc.outlet_id', 'g.gerai_id')
                    ->where('cc.customer_id', $request->customer)
                    ->where(function ($query) use ($search) {
                        $query->whereRaw('LOWER(v.nama_voucher) LIKE ?', ['%' . strtolower($search) . '%'])
                            ->orWhereRaw('LOWER(cc.code_coupon) LIKE ?', ['%' . strtolower($search) . '%'])
                            ->orWhereRaw('LOWER(cc.used_for) LIKE ?', ['%' . strtolower($search) . '%'])
                            ->orWhereRaw('LOWER(cc.sumber) LIKE ?', ['%' . strtolower($search) . '%'])
                            ->orWhereRaw('to_char(cc.masa_berlaku, \'DD-MM-YYYY\') LIKE ?', ['%' . $search . '%']);
                    })
                    ->select('c.nama_customer as nama', 'v.nama_voucher as desc', DB::raw("to_char(cc.created_at, 'DD-MM-YYYY HH24:MI:SS') as waktu"),'cc.status_reward_desc as status',
                        'cc.code_coupon as code','cc.point_type as type','g.nama_gerai as nama_gerai','cc.used_for as used', DB::raw("to_char(cc.masa_berlaku, 'DD-MM-YYYY ') as berlaku"),'cc.sumber as sumber',
                        DB::raw("CONCAT(SUBSTRING(c.nomor_member, 1, 4), '-', SUBSTRING(c.nomor_member, 5, 4), '-', SUBSTRING(c.nomor_member, 9, 4)) as nomor_member"))
                    ->orderByDesc('cc.created_at')
                    ->paginate(10)
                    ->appends(request()->query());

                $customers_data = Customer::where('customer_id', $customer)->select('nama_customer')->first();
            }else{
                $data = DB::table('customer_coupon as cc')->whereBetween('cc.created_at', [$date_from, $dateTo])
                    ->join('customer as c', 'cc.customer_id', 'c.customer_id')
                    ->join('voucher_promo_point as v', 'cc.coupon_id', 'v.id')
                    ->join('gerai as g', 'cc.outlet_id', 'g.gerai_id')
                    ->where(function ($query) use ($search) {
                        $query->whereRaw('LOWER(v.nama_voucher) LIKE ?', ['%' . strtolower($search) . '%'])
                            ->orWhereRaw('LOWER(cc.code_coupon) LIKE ?', ['%' . strtolower($search) . '%'])
                            ->orWhereRaw('LOWER(cc.used_for) LIKE ?', ['%' . strtolower($search) . '%'])
                            ->orWhereRaw('LOWER(cc.sumber) LIKE ?', ['%' . strtolower($search) . '%'])
                            ->orWhereRaw('to_char(cc.masa_berlaku, \'DD-MM-YYYY\') LIKE ?', ['%' . $search . '%']);
                    })
                    ->select('c.nama_customer as nama', 'v.nama_voucher as desc', DB::raw("to_char(cc.created_at, 'DD-MM-YYYY HH24:MI:SS') as waktu"),'cc.status_reward_desc as status',
                        'cc.code_coupon as code','cc.point_type as type','g.nama_gerai as nama_gerai', 'cc.used_for as used', DB::raw("to_char(cc.masa_berlaku, 'DD-MM-YYYY ') as berlaku"),'cc.sumber as sumber',
                        DB::raw("CONCAT(SUBSTRING(c.nomor_member, 1, 4), '-', SUBSTRING(c.nomor_member, 5, 4), '-', SUBSTRING(c.nomor_member, 9, 4)) as nomor_member"))
                    ->orderByDesc('cc.created_at')
                    ->paginate(10)
                    ->appends(request()->query());
            }
       }
        return view('riwayat-voucher-gb.list', compact('data', 'date_from', 'date_to', 'customer','page', 'customers_data','search'));
    }
    public function export(Request $request)
   {
         $dateFrom = $request->input('dateFrom');
        $dateTos = $request->input('dateTo');
        $dateFrom_converted = DateTime::createFromFormat('d/m/Y', $dateFrom);
        $dateTo_converted = DateTime::createFromFormat('d/m/Y', $dateTos);
        if ($dateFrom_converted) {
            $request->merge(['dateFrom' => $dateFrom_converted->format('Y-m-d')]);
            $request->merge(['dateTo' => $dateTo_converted->format('Y-m-d')]);
        }
        $dateTo = Carbon::parse($request->dateTo)->addDay();
        $dateFrom = $request->dateFrom;
        $customer = $request->customer;
        $page = $request->page;
        $customers_data = [];
        $perPage = 10;
        $offset = ($page - 1) * $perPage;
        $search = $request->input('search');
       if ($request->customer != null){
           $data = DB::table('customer_coupon as cc')->whereBetween('cc.created_at', [$dateFrom, $dateTo])
               ->join('customer as c', 'cc.customer_id', 'c.customer_id')
               ->join('voucher_promo_point as v', 'cc.coupon_id', 'v.id')
               ->join('gerai as g', 'cc.outlet_id', 'g.gerai_id')
               ->where('cc.customer_id', $request->customer)
               ->select('c.nama_customer as nama', 'v.nama_voucher as desc', DB::raw("to_char(cc.created_at, 'DD-MM-YYYY HH24:MI:SS') as waktu"),'cc.status_reward_desc as status',
                   'cc.code_coupon as code', 'cc.point_type as type','g.nama_gerai as nama_gerai','cc.used_for as used', DB::raw("to_char(cc.masa_berlaku, 'DD-MM-YYYY ') as berlaku"),'cc.sumber as sumber',
                   DB::raw("CONCAT(SUBSTRING(c.nomor_member, 1, 4), '-', SUBSTRING(c.nomor_member, 5, 4), '-', SUBSTRING(c.nomor_member, 9, 4)) as nomor_member"))
               ->where(function ($query) use ($search) {
                   $query->whereRaw('LOWER(v.nama_voucher) LIKE ?', ['%' . strtolower($search) . '%'])
                       ->orWhereRaw('LOWER(cc.code_coupon) LIKE ?', ['%' . strtolower($search) . '%'])
                       ->orWhereRaw('LOWER(cc.used_for) LIKE ?', ['%' . strtolower($search) . '%'])
                       ->orWhereRaw('LOWER(cc.sumber) LIKE ?', ['%' . strtolower($search) . '%'])
                       ->orWhereRaw('to_char(cc.masa_berlaku, \'DD-MM-YYYY\') LIKE ?', ['%' . $search . '%']);
               })
               ->orderByDesc('cc.created_at')
               ->skip($offset)
               ->take($perPage)
               ->get();

           $customers_data = Customer::where('customer_id', $customer)->select('nama_customer')->first();

       }else {
           $data = DB::table('customer_coupon as cc')->whereBetween('cc.created_at', [$dateFrom, $dateTo])
               ->join('customer as c', 'cc.customer_id', 'c.customer_id')
               ->join('voucher_promo_point as v', 'cc.coupon_id', 'v.id')
               ->join('gerai as g', 'cc.outlet_id', 'g.gerai_id')
               ->select('c.nama_customer as nama', 'v.nama_voucher as desc', DB::raw("to_char(cc.created_at, 'DD-MM-YYYY HH24:MI:SS') as waktu"),'cc.status_reward_desc as status',
                   'cc.code_coupon as code','g.nama_gerai as nama_gerai', 'cc.used_for as used', DB::raw("to_char(cc.masa_berlaku, 'DD-MM-YYYY ') as berlaku"),'cc.sumber as sumber',
                   DB::raw("CONCAT(SUBSTRING(c.nomor_member, 1, 4), '-', SUBSTRING(c.nomor_member, 5, 4), '-', SUBSTRING(c.nomor_member, 9, 4)) as nomor_member"))
               ->where(function ($query) use ($search) {
                   $query->whereRaw('LOWER(v.nama_voucher) LIKE ?', ['%' . strtolower($search) . '%'])
                       ->orWhereRaw('LOWER(cc.code_coupon) LIKE ?', ['%' . strtolower($search) . '%'])
                       ->orWhereRaw('LOWER(cc.used_for) LIKE ?', ['%' . strtolower($search) . '%'])
                       ->orWhereRaw('LOWER(cc.sumber) LIKE ?', ['%' . strtolower($search) . '%'])
                       ->orWhereRaw('to_char(cc.masa_berlaku, \'DD-MM-YYYY\') LIKE ?', ['%' . $search . '%']);
               })
               ->orderByDesc('cc.created_at')
               ->skip($offset)
               ->take($perPage)
               ->get();
       }
       $riwayat_voucher_coin = new RiwayatVoucherExport($data, $customers_data);
       return Excel::download($riwayat_voucher_coin, 'report_riwayat_voucher_coin.xlsx', \Maatwebsite\Excel\Excel::XLSX, [
           'preCalculateFormulas' => false,
       ]);
   }
    public function export_all(Request $request)
   {
       $dateFrom = $request->input('dateFrom');
       $dateTos = $request->input('dateTo');
       $dateFrom_converted = DateTime::createFromFormat('d/m/Y', $dateFrom);
       $dateTo_converted = DateTime::createFromFormat('d/m/Y', $dateTos);
       if ($dateFrom_converted) {
           $request->merge(['dateFrom' => $dateFrom_converted->format('Y-m-d')]);
           $request->merge(['dateTo' => $dateTo_converted->format('Y-m-d')]);
       }
       $dateTo = Carbon::parse($request->dateTo)->addDay();
       $dateFrom = $request->dateFrom;
       $customer = $request->customer;
       $page = $request->page;
       $customers_data = [];
       $perPage = 10;
       $offset = ($page - 1) * $perPage;
        if ($request->customer != null) {
            $data = DB::table('customer_coupon as cc')->whereBetween('cc.created_at', [$dateFrom, $dateTo])
                ->join('customer as c', 'cc.customer_id', 'c.customer_id')
                ->join('voucher_promo_point as v', 'cc.coupon_id', 'v.id')
                ->join('gerai as g', 'cc.outlet_id', 'g.gerai_id')
                ->where('cc.customer_id', $request->customer)
                ->select('c.nama_customer as nama', 'v.nama_voucher as desc', DB::raw("to_char(cc.created_at, 'DD-MM-YYYY HH24:MI:SS') as waktu"), 'cc.status_reward_desc as status',
                    'cc.code_coupon as code','g.nama_gerai as nama_gerai','cc.used_for as used', DB::raw("to_char(cc.masa_berlaku, 'DD-MM-YYYY ') as berlaku"), 'cc.sumber as sumber',
                    DB::raw("CONCAT(SUBSTRING(c.nomor_member, 1, 4), '-', SUBSTRING(c.nomor_member, 5, 4), '-', SUBSTRING(c.nomor_member, 9, 4)) as nomor_member"))
                ->orderByDesc('cc.created_at')
                ->get();

            $customers_data = Customer::where('customer_id', $customer)->select('nama_customer')->first();
        }else {
            $data = DB::table('customer_coupon as cc')->whereBetween('cc.created_at', [$dateFrom, $dateTo])
                ->join('customer as c', 'cc.customer_id', 'c.customer_id')
                ->join('voucher_promo_point as v', 'cc.coupon_id', 'v.id')
                ->join('gerai as g', 'cc.outlet_id', 'g.gerai_id')
                ->select('c.nama_customer as nama', 'v.nama_voucher as desc', DB::raw("to_char(cc.created_at, 'DD-MM-YYYY HH24:MI:SS') as waktu"),'cc.status_reward_desc as status' ,
                    'cc.code_coupon as code','g.nama_gerai as nama_gerai', 'cc.used_for as used', DB::raw("to_char(cc.masa_berlaku, 'DD-MM-YYYY ') as berlaku"),'cc.sumber as sumber',
                    DB::raw("CONCAT(SUBSTRING(c.nomor_member, 1, 4), '-', SUBSTRING(c.nomor_member, 5, 4), '-', SUBSTRING(c.nomor_member, 9, 4)) as nomor_member"))
                ->orderByDesc('cc.created_at')
                ->get();

        }
       $riwayat_voucher_coin_all = new RiwayatVoucherCoinAllExport($data, $customers_data);
       return Excel::download($riwayat_voucher_coin_all, 'report_riwayat_voucher_coin_all.xlsx', \Maatwebsite\Excel\Excel::XLSX, [
           'preCalculateFormulas' => false,
       ]);
   }
}


?>