<?php

namespace App\Http\Controllers\Core;

use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\Crypt;
use Illuminate\Http\Request;
use App\Model\Privillage\Role;
use App\Model\Inspeksi\Inspeksi;
use Auth;
use Session;
use App\Model\UPTSModel;
use Yajra\DataTables\Facades\DataTables;

class DashboardController extends Controller
{
	public function __construct()
    {
        $this->middleware('auth');
    }

	public function index(){
		$year = date('Y');
		if (Role::where('id',Auth::user()->role_id)->value('akses') == 'administrator') {
			//SPP
			$rt_paid = \App\Model\SPP\RincianTagihan::where('status','paid')->where('status_izin','Perpanjangan')->where('bi_type',8)->whereYear('bi_begin',$year)->count();
			$total_rt = \App\Model\SPP\RincianTagihan::where('status_izin','Perpanjangan')->whereYear('bi_begin',$year)->count();
			if ($total_rt == 0) {
				$rt_presentation = 0;
			}else{
				$rt_presentation = $rt_paid / $total_rt * 100;
			}
			$st_paid = \App\Model\SPP\StatusTagihan::where('status','paid')->where('status_izin','Perpanjangan')->whereYear('bi_create_date',$year)->count();
			$total_st = \App\Model\SPP\StatusTagihan::where('status_izin','Perpanjangan')->whereYear('bi_create_date',$year)->count();
			if ($total_st == 0) {
				$st_presentation = 0;
			}else{
				$st_presentation = $st_paid / $total_st * 100;
			}

			//Loket Pelayanan
			$open = \App\Model\Service\LoketPengaduan::where('active',1)->where('tujuan',1)->count();
			$reopen = \App\Model\Service\LoketPengaduan::where('active',3)->where('tujuan',1)->count();
			$fixOpen = $open + $reopen;
			$closed = \App\Model\Service\LoketPengaduan::where('active',0)->where('tujuan',1)->count();
			$totalpengaduan = \App\Model\Service\LoketPengaduan::where('tujuan',1)->count();
			if ($closed <= 0) {
				$loketpengaduanpercent = 0;
			}else{
				$loketpengaduanpercent = $closed / $totalpengaduan * 100;	
			}

			//SosialisasiBimtek
			$terealisasi = \App\Model\SosialisasiBimtek\MonevSosialisasi::whereNotNull('id_rensos')->count();
			$blm = \App\Model\SosialisasiBimtek\RencanaSosialisasi::count();
			if ($blm == 0) {
				$total_sosialisasi = 0;
			}else{
				$total_sosialisasi = $terealisasi / $blm * 100;
				if($total_sosialisasi >= 100){
					$total_sosialisasi = 100;
				}
			}

			//UNAR
			$total_unar = \App\Model\Service\Unar::where('full_cat',1)->count();
			$tahun_sekarang = date('Y');
			$rencana_unar = \App\RencanaUnar::where('tahun',$tahun_sekarang)->value('jumlah');
			$getUptUnar = \App\Model\Setting\UPT::distinct('office_id')->count();
			$rencana_unar_fix = $rencana_unar * $getUptUnar;
			if ($rencana_unar_fix == 0) {
				$unarpercent = 0;
			}elseif($total_unar >= $rencana_unar){
				$unarpercent = 100;
			}
			else{
				$unarpercent = $total_unar / $rencana_unar_fix * 100;
			}

			//Penanganan Piutang
			$petang_psbdt = \App\Model\Service\PenangananPiutang::where('tahapan_pengurusan','PSBDT')->count();
			$petang_lunas = \App\Model\Service\PenangananPiutang::where('tahapan_pengurusan','Lunas (SPPNL)')->count();
			$petang_pengembalian = \App\Model\Service\PenangananPiutang::where('tahapan_pengurusan','Pengembalian')->count();
			$petang_pembatalan = \App\Model\Service\PenangananPiutang::where('tahapan_pengurusan','Pembatalan')->count();
			$petang_data = \App\Model\Service\PenangananPiutang::count();

			if ($petang_data <= 0) {
				$petang = 0;
			}else{
				$petang = ($petang_psbdt + $petang_lunas + $petang_pengembalian +  $petang_pembatalan) / ($petang_data) * 100;
			}

			//Inspeksi
			$date = Inspeksi::select('tanggal_lapor')->where('status',1)->orderBy('tanggal_lapor','desc')->first();
        	 if (!$date) {
	           $month=date("m");
	           $year=date("Y");
	        }else{
	           $time=strtotime($date->tanggal_lapor);
	           $month=date("m",$time);
	           $year=date("Y",$time);
	        }
			$hi_sesuai_isr = Inspeksi::where('status',1)->whereMonth('tanggal_lapor',$month)->whereYear('tanggal_lapor',$year)->sum('hi_sesuai_isr');
			$tl_sesuai_isr = Inspeksi::where('status',1)->whereMonth('tanggal_lapor',$month)->whereYear('tanggal_lapor',$year)->sum('tl_sesuai_isr');
			$data_sampling = Inspeksi::where('status',1)->whereMonth('tanggal_lapor',$month)->whereYear('tanggal_lapor',$year)->sum('data_sampling');
			if($data_sampling == 0){
                    $total_inspeksi = 0;
                }else{
                    $total_inspeksi = ( $hi_sesuai_isr + $tl_sesuai_isr ) / ($data_sampling) * 100;
                }
                if ($total_inspeksi >= 100) {
                    $total_inspeksi = 100;
                }

		}else{

			//SPP
			$getUptName = \App\Model\Setting\UPT::where('office_id',Auth::user()->upt)->select('office_name')->distinct()->value('office_name');
			$rt_paid = \App\Model\SPP\RincianTagihan::where('status','paid')->where('status_izin','Perpanjangan')->where('upt',$getUptName)->where('bi_type',8)->whereYear('bi_begin',$year)->count();
			$total_rt = \App\Model\SPP\RincianTagihan::where('status_izin','Perpanjangan')->whereYear('bi_begin',$year)->where('upt',$getUptName)->count();
			if ($total_rt == 0) {
				$rt_presentation = 0;
			}else{
				$rt_presentation = $rt_paid / $total_rt * 100;
			}

			$st_paid = \App\Model\SPP\StatusTagihan::where('status_izin','Perpanjangan')->where('status','paid')->whereYear('bi_create_date',$year)->where('upt',$getUptName)->count();
			$total_st = \App\Model\SPP\StatusTagihan::where('status_izin','Perpanjangan')->whereYear('bi_create_date',$year)->where('upt',$getUptName)->count();
			if ($total_st == 0) {
				$st_presentation = 0;
			}else{
				$st_presentation = $st_paid / $total_st * 100;
			}

			//Loket Pelayanan
			$open = \App\Model\Service\LoketPengaduan::where('active',1)->where('tujuan',1)->where('kode_upt',Auth::user()->upt)->count();
			$reopen = \App\Model\Service\LoketPengaduan::where('active',3)->where('tujuan',1)->where('kode_upt',Auth::user()->upt)->count();
			$fixOpen = $open + $reopen;
			$closed = \App\Model\Service\LoketPengaduan::where('active',0)->where('tujuan',1)->where('kode_upt',Auth::user()->upt)->count();
			$totalpengaduan = \App\Model\Service\LoketPengaduan::where('tujuan',1)->where('kode_upt',Auth::user()->upt)->count();

			if ($closed <= 0) {
				$loketpengaduanpercent = 0;
			}else{
				$loketpengaduanpercent = $closed / $totalpengaduan * 100;	
			}

			//SosialisasiBimtek
			$terealisasi = \App\Model\SosialisasiBimtek\MonevSosialisasi::whereNotNull('id_rensos')->where('kode_upt',Auth::user()->upt)->count();
			$blm = \App\Model\SosialisasiBimtek\RencanaSosialisasi::where('kode_upt',Auth::user()->upt)->count();
			if ($blm == 0) {
				$total_sosialisasi = 0;
			}else{
				$total_sosialisasi = $terealisasi / $blm * 100;
				if($total_sosialisasi >= 100){
					$total_sosialisasi = 100;
				}
			}

			//UNAR
			$total_unar = \App\Model\Service\Unar::where('full_cat',1)->where('kode_upt',Auth::user()->upt)->count();
			$tahun_sekarang = date('Y');
			$rencana_unar = \App\RencanaUnar::where('tahun',$tahun_sekarang)->value('jumlah');
			$getUptUnar = \App\Model\Setting\UPT::where('office_id',Auth::user()->upt)->distinct('office_id')->count();
			$rencana_unar_fix = $rencana_unar * $getUptUnar;
			if ($rencana_unar_fix == 0) {
				$unarpercent = 0;
			}elseif($total_unar >= $rencana_unar){
				$unarpercent = 100;
			}
			else{
				$unarpercent = $total_unar / $rencana_unar_fix * 100;
			}

			//Inspeksi
			$date = Inspeksi::select('tanggal_lapor')->where('kode_upt',Auth::user()->upt)->orderBy('tanggal_lapor','desc')->first();
        	$time=strtotime($date->tanggal_lapor);
	        $month=date("m",$time);
	        $year=date("Y",$time);
			$hi_sesuai_isr = Inspeksi::where('status',1)->where('kode_upt',Auth::user()->upt)->whereMonth('tanggal_lapor',$month)->whereYear('tanggal_lapor',$year)->sum('hi_sesuai_isr');
			$tl_sesuai_isr = Inspeksi::where('status',1)->where('kode_upt',Auth::user()->upt)->whereMonth('tanggal_lapor',$month)->whereYear('tanggal_lapor',$year)->sum('tl_sesuai_isr');
			$data_sampling = Inspeksi::where('status',1)->where('kode_upt',Auth::user()->upt)->whereMonth('tanggal_lapor',$month)->whereYear('tanggal_lapor',$year)->sum('data_sampling');
			if($data_sampling == 0){
                    $total_inspeksi = 0;
                }else{
                    $total_inspeksi = ( $hi_sesuai_isr + $tl_sesuai_isr ) / ($data_sampling) * 100;
                }
                if ($total_inspeksi >= 100) {
                    $total_inspeksi = 100;
                }
			// $jumlahdata = \App\Model\Inspeksi\Inspeksi::where('kode_upt',Auth::user()->upt)->count();
			// $capaian = \App\Model\Inspeksi\Inspeksi::where('kode_upt',Auth::user()->upt)->where('status',1)->sum('capaian_valid');
			// if ($capaian == 0) {
			// 	$total_inspeksi = 0;
			// }else{
			// 	$total_inspeksi = $capaian / $jumlahdata;
			// }
			// $data_sampling = Inspeksi::where('kode_upt',Auth::user()->upt)->where('status',1)->sum('data_sampling');
			// $hi_sesuai_isr = Inspeksi::where('kode_upt',Auth::user()->upt)->where('status',1)->sum('hi_sesuai_isr');
			// $tl_sesuai_isr = Inspeksi::where('kode_upt',Auth::user()->upt)->where('status',1)->sum('tl_sesuai_isr');
			// $capaian = \App\Model\Inspeksi\Inspeksi::where('status',1)->sum('capaian_valid');
			// if($data_sampling == 0)
   //              $total_inspeksi = 0;
   //          else{
   //              $total_inspeksi = ( $hi_sesuai_isr + $tl_sesuai_isr ) / ($data_sampling) * 100;
   //          }

			//Penanganan Piutang
			$petang_psbdt = \App\Model\Service\PenangananPiutang::where('tahapan_pengurusan','PSBDT')->where('kode_upt',Auth::user()->upt)->count();
			$petang_lunas = \App\Model\Service\PenangananPiutang::where('tahapan_pengurusan','Lunas (SPPNL)')->where('kode_upt',Auth::user()->upt)->count();
			$petang_pengembalian = \App\Model\Service\PenangananPiutang::where('tahapan_pengurusan','Pengembalian')->where('kode_upt',Auth::user()->upt)->count();
			$petang_pembatalan = \App\Model\Service\PenangananPiutang::where('tahapan_pengurusan','Pembatalan')->where('kode_upt',Auth::user()->upt)->count();
			$petang_data = \App\Model\Service\PenangananPiutang::where('kode_upt',Auth::user()->upt)->count();

			if ($petang_data <= 0) {
				$petang = 0;
			}else{
				$petang = ($petang_psbdt + $petang_lunas + $petang_pengembalian +  $petang_pembatalan) / ($petang_data) * 100;
			}

		}
		$id_bulan = 'Dashboard ' . date('Y');
		return view('welcome',compact('loketpengaduanpercent','total_sosialisasi','unarpercent','total_inspeksi','id_bulan','petang','rt_presentation','st_presentation'));
	}

	public function searchByMonth(Request $request){
		$years = date('Y');
		if ($request->id_bulan == 1) {
				$bulan = 12;
				$year = $years - 1;
			}
			else{
				$bulan = $request->id_bulan - 1;
				$year = $years;
			}
		if (Role::where('id',Auth::user()->role_id)->value('akses') == 'administrator') {
			//SPP
			$rt_paid = \App\Model\SPP\RincianTagihan::where('status','paid')->where('status_izin','Perpanjangan')->where('bi_type',8)->whereMonth('bi_begin',$bulan)->whereYear('bi_begin',$year)->count();
			$total_rt = \App\Model\SPP\RincianTagihan::where('status_izin','Perpanjangan')->whereMonth('bi_begin',$bulan)->whereYear('bi_begin',$year)->count();
			if ($total_rt == 0) {
				$rt_presentation = 0;
			}else{
				$rt_presentation = $rt_paid / $total_rt * 100;
			}
			$st_paid = \App\Model\SPP\StatusTagihan::where('status','paid')->where('status_izin','Perpanjangan')->whereMonth('bi_create_date',$bulan)->whereYear('bi_create_date',$year)->count();
			$total_st = \App\Model\SPP\StatusTagihan::where('status_izin','Perpanjangan')->whereMonth('bi_create_date',$bulan)->whereYear('bi_create_date',$year)->count();
			if ($total_st == 0) {
				$st_presentation = 0;
			}else{
				$st_presentation = $st_paid / $total_st * 100;
			}

			//Loket Pelayanan
			$open = \App\Model\Service\LoketPengaduan::where('active',1)->whereMonth('tanggal_pelayanan',$request->id_bulan)->whereYear('tanggal_pelayanan',$year)->where('tujuan',1)->count();
			$reopen = \App\Model\Service\LoketPengaduan::where('active',3)->whereMonth('tanggal_pelayanan',$request->id_bulan)->where('tujuan',1)->whereYear('tanggal_pelayanan',$year)->count();
			$fixOpen = $open + $reopen;
			$closed = \App\Model\Service\LoketPengaduan::where('active',0)->whereMonth('tanggal_pelayanan',$request->id_bulan)->whereYear('tanggal_pelayanan',$year)->where('tujuan',1)->count();
			$totalpengaduan = \App\Model\Service\LoketPengaduan::where('tujuan',1)->whereMonth('tanggal_pelayanan',$request->id_bulan)->whereYear('tanggal_pelayanan',$year)->count();
			if ($closed <= 0) {
				$loketpengaduanpercent = 0;
			}else{
				$loketpengaduanpercent = $closed / $totalpengaduan * 100;	
			}

			//SosialisasiBimtek
			$terealisasi = \App\Model\SosialisasiBimtek\MonevSosialisasi::whereNotNull('id_rensos')->whereMonth('tanggal_pelaksanaan',$request->id_bulan)->whereYear('tanggal_pelaksanaan',$year)->count();
			$blm = \App\Model\SosialisasiBimtek\RencanaSosialisasi::whereMonth('tanggal_pelaksanaan',$request->id_bulan)->whereYear('tanggal_pelaksanaan',$year)->count();
			if ($blm == 0) {
				$total_sosialisasi = 0;
			}else{
				$total_sosialisasi = $terealisasi / $blm * 100;
			}

			//UNAR
			$total_unar = \App\Model\Service\Unar::where('full_cat',1)->whereMonth('tanggal_ujian',$request->id_bulan)->whereYear('tanggal_ujian',$year)->count();
			$tahun_sekarang = date('Y');
			$rencana_unar = \App\RencanaUnar::where('tahun',$tahun_sekarang)->value('jumlah');
			if ($rencana_unar == 0) {
				$unarpercent = 0;
			}elseif($total_unar >= $rencana_unar){
				$unarpercent = 100;
			}
			else{
				$unarpercent = $total_unar / $rencana_unar * 100;
			}

			//Penanganan Piutang
			$petang_psbdt = \App\Model\Service\PenangananPiutang::where('tahapan_pengurusan','PSBDT')->whereMonth('tanggal',$request->id_bulan)->whereYear('tanggal',$year)->count();
			$petang_lunas = \App\Model\Service\PenangananPiutang::where('tahapan_pengurusan','Lunas (SPPNL)')->whereMonth('tanggal',$request->id_bulan)->whereYear('tanggal',$year)->count();
			$petang_data = \App\Model\Service\PenangananPiutang::where('kode_upt',Auth::user()->upt)->whereMonth('tanggal',$request->id_bulan)->whereYear('tanggal',$year)->count();
			if ($petang_data <= 0) {
				$petang = 0;
			}else{
				$petang = ($petang_psbdt + $petang_lunas ) / ($petang_data) * 100;
			}


			//Inspeksi
			$jumlahdata = \App\Model\Inspeksi\Inspeksi::whereMonth('tanggal_lapor',$request->id_bulan)->whereYear('tanggal_lapor',$year)->count();
			$capaian = \App\Model\Inspeksi\Inspeksi::where('status',1)->whereMonth('tanggal_lapor',$request->id_bulan)->whereYear('tanggal_lapor',$year)->sum('capaian_valid');
			if ($capaian == 0) {
				$total_inspeksi = 0;
			}else{
				$total_inspeksi = $capaian / $jumlahdata;
			}

		}else{
			//SPP
			$getUptName = \App\Model\Setting\UPT::where('office_id',Auth::user()->upt)->select('office_name')->distinct()->value('office_name');
			$rt_paid = \App\Model\SPP\RincianTagihan::where('status','paid')->where('status_izin','Perpanjangan')->where('upt',$getUptName)->where('bi_type',8)->whereMonth('bi_begin',$bulan)->whereYear('bi_begin',$year)->count();
			$total_rt = \App\Model\SPP\RincianTagihan::where('status_izin','Perpanjangan')->where('upt',$getUptName)->whereMonth('bi_begin',$bulan)->whereYear('bi_begin',$year)->count();
			if ($total_rt == 0) {
				$rt_presentation = 0;
			}else{
				$rt_presentation = $rt_paid / $total_rt * 100;
			}

			$st_paid = \App\Model\SPP\StatusTagihan::where('status','paid')->where('status_izin','Perpanjangan')->where('upt',$getUptName)->whereMonth('bi_create_date',$bulan)->whereYear('bi_create_date',$year)->count();
			$total_st = \App\Model\SPP\StatusTagihan::where('status_izin','Perpanjangan')->where('upt',$getUptName)->whereMonth('bi_create_date',$bulan)->whereYear('bi_create_date',$year)->count();
			if ($total_st == 0) {
				$st_presentation = 0;
			}else{
				$st_presentation = $st_paid / $total_st * 100;
			}

			//Loket Pelayanan
			$open = \App\Model\Service\LoketPengaduan::where('active',1)->whereMonth('tanggal_pelayanan',$request->id_bulan)->whereYear('tanggal_pelayanan',$year)->where('tujuan',1)->where('kode_upt',Auth::user()->upt)->count();
			$reopen = \App\Model\Service\LoketPengaduan::where('active',3)->whereMonth('tanggal_pelayanan',$request->id_bulan)->whereYear('tanggal_pelayanan',$year)->where('tujuan',1)->where('kode_upt',Auth::user()->upt)->count();
			$fixOpen = $open + $reopen;
			$closed = \App\Model\Service\LoketPengaduan::where('active',0)->whereMonth('tanggal_pelayanan',$request->id_bulan)->whereYear('tanggal_pelayanan',$year)->where('tujuan',1)->where('kode_upt',Auth::user()->upt)->count();
			$totalpengaduan = \App\Model\Service\LoketPengaduan::where('tujuan',1)->whereMonth('tanggal_pelayanan',$request->id_bulan)->whereYear('tanggal_pelayanan',$year)->where('kode_upt',Auth::user()->upt)->count();

			if ($closed <= 0) {
				$loketpengaduanpercent = 0;
			}else{
				$loketpengaduanpercent = $closed / $totalpengaduan * 100;	
			}

			//SosialisasiBimtek
			$terealisasi = \App\Model\SosialisasiBimtek\MonevSosialisasi::whereNotNull('id_rensos')->whereMonth('tanggal_pelaksanaan',$request->id_bulan)->whereYear('tanggal_pelaksanaan',$year)->where('kode_upt',Auth::user()->upt)->count();
			$blm = \App\Model\SosialisasiBimtek\RencanaSosialisasi::where('kode_upt',Auth::user()->upt)->whereMonth('tanggal_pelaksanaan',$request->id_bulan)->whereYear('tanggal_pelaksanaan',$year)->count();
			if ($blm == 0) {
				$total_sosialisasi = 0;
			}else{
				$total_sosialisasi = $terealisasi / $blm * 100;
			}

			//UNAR
			$total_unar = \App\Model\Service\Unar::where('full_cat',1)->whereMonth('tanggal_ujian',$request->id_bulan)->whereYear('tanggal_ujian',$year)->where('kode_upt',Auth::user()->upt)->count();
			$tahun_sekarang = date('Y');
			$rencana_unar = \App\RencanaUnar::where('tahun',$tahun_sekarang)->value('jumlah');
			if ($rencana_unar == 0) {
				$unarpercent = 0;
			}elseif($total_unar >= $rencana_unar){
				$unarpercent = 100;
			}
			else{
				$unarpercent = $total_unar / $rencana_unar * 100;
			}

			//Inspeksi
			$jumlahdata = \App\Model\Inspeksi\Inspeksi::where('kode_upt',Auth::user()->upt)->whereMonth('tanggal_lapor',$request->id_bulan)->whereYear('tanggal_lapor',$year)->count();
			$capaian = \App\Model\Inspeksi\Inspeksi::where('kode_upt',Auth::user()->upt)->where('status',1)->whereMonth('tanggal_lapor',$request->id_bulan)->whereYear('tanggal_lapor',$year)->sum('capaian_valid');
			if ($capaian == 0) {
				$total_inspeksi = 0;
			}else{
				$total_inspeksi = $capaian / $jumlahdata;
			}

			//Penanagnan Piutang
			$petang_psbdt = \App\Model\Service\PenangananPiutang::where('tahapan_pengurusan','PSBDT')->whereMonth('tanggal',$request->id_bulan)->whereYear('tanggal',$year)->where('kode_upt',Auth::user()->upt)->count();
			$petang_lunas = \App\Model\Service\PenangananPiutang::where('tahapan_pengurusan','Lunas (SPPNL)')->whereMonth('tanggal',$request->id_bulan)->whereYear('tanggal',$year)->where('kode_upt',Auth::user()->upt)->count();
			$petang_data = \App\Model\Service\PenangananPiutang::where('kode_upt',Auth::user()->upt)->whereMonth('tanggal',$request->id_bulan)->whereYear('tanggal',$year)->count();
			if ($petang_data <= 0) {
				$petang = 0;
			}else{
				$petang = ($petang_psbdt + $petang_lunas ) / ($petang_data) * 100;
			}

		}
		$id_bulan = 'Dashboard Bulan ' .  \App\Model\Date\ListMonth::where('id_bulan',$request->id_bulan)->value('nama') . ' ' . date('Y');
		return view('welcome',compact('loketpengaduanpercent','total_sosialisasi','unarpercent','total_inspeksi','id_bulan','petang','rt_presentation','st_presentation'));
	}

	public function ubah_password(){
		return view('dev.core.password');
	}

	public function post_password(Request $request){
		$password = \App\User::find(Auth::user()->id);
		$password->password = bcrypt($request->kata_sandi);
		$password->save();
		Session::flash('info', 'Kata Sandi');
		Session::flash('colors', 'green');
		Session::flash('icons', 'fas fa-clipboard-check');
		Session::flash('alert', 'Berhasil Mengubah Kata Sandi, Silahkan Login Ulang!');
		return redirect(route('logout'));
	}

	public function surveyLink(){
		return view('dev.helpers.survey');
	}

	public function postSurveyLink(Request $request){
		$link = \App\SurveyLink::where('id',1)->update([
			'link' => $request->link
		]);
		Session::flash('info', 'Survey Link');
		Session::flash('colors', 'green');
		Session::flash('icons', 'fas fa-clipboard-check');
		Session::flash('alert', 'Berhasil Mengubah Link');
		return redirect()->back();
	}

	public function getUpts(){
		$upts = new UPTSModel();
		$data = $upts->getData();
		return DataTables::of($data)->make(true);
	}

	public function newDashboard(){
		return view('new-dashboard');
	}
}