<?php 
namespace App\Models;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
/**
 * Components data Model
 * Use for getting values from the database for page components
 * Support raw query builder
 * @category Model
 */
class ComponentsData{
	

	/**
     * jumlah_option_list Model Action
     * @return array
     */
	function jumlah_option_list(){
		$sqltext = "";
		$query_params = [];
		$arr = DB::select(DB::raw($sqltext), $query_params);
		return $arr;
	}
	

	/**
     * Check if value already exist in User table
	 * @param string $value
     * @return bool
     */
	function user_username_value_exist(Request $request){
		$value = trim($request->value);
		$exist = DB::table('user')->where('username', $value)->value('username');   
		if($exist){
			return true;
		}
		return false;
	}
	

	/**
     * Check if value already exist in User table
	 * @param string $value
     * @return bool
     */
	function user_email_value_exist(Request $request){
		$value = trim($request->value);
		$exist = DB::table('user')->where('email', $value)->value('email');   
		if($exist){
			return true;
		}
		return false;
	}
	

	/**
     * layanan_kecamatan_id_kecamatan_option_list Model Action
     * @return array
     */
	function layanan_kecamatan_id_kecamatan_option_list(){
		$sqltext = "SELECT  DISTINCT id AS value,kecamatan AS label FROM jenis_kecamatan ORDER BY id ASC";
		$query_params = [];
		$arr = DB::select(DB::raw($sqltext), $query_params);
		return $arr;
	}
	

	/**
	* barchart_phpsetlocalelc_allididid_idjumlahcetakektpwnistrftimeb Model Action
	* @return array
	*/
	function barchart_phpsetlocalelc_allididid_idjumlahcetakektpwnistrftimeb(){
		$request = request();
		$chart_data  = [];
		$sqltext = "select DATE_FORMAT(a.created,'%d-%m-%Y') as tahun, a.jumlah as jmldensel, b.jumlah as jmldentim, c.jumlah as jmldenbar, d.jumlah as jmldenut from layanan_kecamatan a inner join layanan_kecamatan b on a.created = b.created inner join layanan_kecamatan c on b.created = c.created inner join layanan_kecamatan d on c.created = d.created and a.id_kecamatan = 1 and b.id_kecamatan = 2 and c.id_kecamatan = 3 and d.id_kecamatan = 4 and a.id_jenis_layanan_kecamatan = 4 and b.id_jenis_layanan_kecamatan = 4 and c.id_jenis_layanan_kecamatan = 4 and d.id_jenis_layanan_kecamatan = 4 and MONTH(a.created) = Month(Now()) order by a.created";
		$query_params = [];
		$records = DB::select(DB::raw($sqltext), $query_params);
		$chart_labels = array_column($records, 'tahun');
		$datasets = [];
		$dataset1 = [
			'data' =>  array_column($records, 'jmldensel'),
			'label' => "Denpasar Selatan",
	'backgroundColor' =>  random_color(), 
	'borderColor' =>  random_color(), 
	'borderWidth' => '2',
		];
		$datasets[] = $dataset1;
		$dataset2 = [
			'data' =>  array_column($records, 'jmldentim'),
			'label' => "Denpasar Timur",
	'backgroundColor' =>  random_color(), 
	'borderColor' =>  random_color(), 
	'borderWidth' => '2',
		];
		$datasets[] = $dataset2;
		$dataset3 = [
			'data' =>  array_column($records, 'jmldenbar'),
			'label' => "Denpasar Barat",
	'backgroundColor' =>  random_color(), 
	'borderColor' =>  random_color(), 
	'borderWidth' => '2',
		];
		$datasets[] = $dataset3;
		$chart_data['datasets'] = $datasets;
		$chart_data['labels'] = $chart_labels;
		return $chart_data;
	}
	

	/**
	* barchart_jumlahperekamanbarudiatas17tahunstrftimeb Model Action
	* @return array
	*/
	function barchart_jumlahperekamanbarudiatas17tahunstrftimeb(){
		$request = request();
		$chart_data  = [];
		$sqltext = "select DATE_FORMAT(a.created,'%d-%m-%Y') as tahun, a.jumlah as jmldensel, b.jumlah as jmldentim, c.jumlah as jmldenbar, d.jumlah as jmldenut from layanan_kecamatan a inner join layanan_kecamatan b on a.created = b.created inner join layanan_kecamatan c on b.created = c.created inner join layanan_kecamatan d on c.created = d.created and a.id_kecamatan = 1 and b.id_kecamatan = 2 and c.id_kecamatan = 3 and d.id_kecamatan = 4 and a.id_jenis_layanan_kecamatan = 2 and b.id_jenis_layanan_kecamatan = 2 and c.id_jenis_layanan_kecamatan = 2 and d.id_jenis_layanan_kecamatan = 2 and MONTH(a.created) = Month(Now()) order by a.created";
		$query_params = [];
		$records = DB::select(DB::raw($sqltext), $query_params);
		$chart_labels = array_column($records, 'tahun');
		$datasets = [];
		$dataset1 = [
			'data' =>  array_column($records, 'jmldensel'),
			'label' => "Denpasar Selatan",
	'backgroundColor' =>  random_color(), 
	'borderColor' =>  random_color(), 
	'borderWidth' => '2',
		];
		$datasets[] = $dataset1;
		$dataset2 = [
			'data' =>  array_column($records, 'jmldentim'),
			'label' => "Denpasar Timur",
	'backgroundColor' =>  random_color(), 
	'borderColor' =>  random_color(), 
	'borderWidth' => '2',
		];
		$datasets[] = $dataset2;
		$dataset3 = [
			'data' =>  array_column($records, 'jmldenbar'),
			'label' => "Denpasar Barat",
	'backgroundColor' =>  random_color(), 
	'borderColor' =>  random_color(), 
	'borderWidth' => '2',
		];
		$datasets[] = $dataset3;
		$chart_data['datasets'] = $datasets;
		$chart_data['labels'] = $chart_labels;
		return $chart_data;
	}
	

	/**
	* barchart_jumlahcetakkiastrftimeb Model Action
	* @return array
	*/
	function barchart_jumlahcetakkiastrftimeb(){
		$request = request();
		$chart_data  = [];
		$sqltext = "select DATE_FORMAT(a.created,'%d-%m-%Y') as tahun, a.jumlah as jmldensel, b.jumlah as jmldentim, c.jumlah as jmldenbar, d.jumlah as jmldenut from layanan_kecamatan a inner join layanan_kecamatan b on a.created = b.created inner join layanan_kecamatan c on b.created = c.created inner join layanan_kecamatan d on c.created = d.created and a.id_kecamatan = 1 and b.id_kecamatan = 2 and c.id_kecamatan = 3 and d.id_kecamatan = 4 and a.id_jenis_layanan_kecamatan = 18 and b.id_jenis_layanan_kecamatan = 18 and c.id_jenis_layanan_kecamatan = 18 and d.id_jenis_layanan_kecamatan = 18 and MONTH(a.created) = Month(Now()) order by a.created";
		$query_params = [];
		$records = DB::select(DB::raw($sqltext), $query_params);
		$chart_labels = array_column($records, 'tahun');
		$datasets = [];
		$dataset1 = [
			'data' =>  array_column($records, 'jmldensel'),
			'label' => "Denpasar Selatan",
	'backgroundColor' =>  random_color(), 
	'borderColor' =>  random_color(), 
	'borderWidth' => '2',
		];
		$datasets[] = $dataset1;
		$dataset2 = [
			'data' =>  array_column($records, 'jmldentim'),
			'label' => "Denpasar Timur",
	'backgroundColor' =>  random_color(), 
	'borderColor' =>  random_color(), 
	'borderWidth' => '2',
		];
		$datasets[] = $dataset2;
		$dataset3 = [
			'data' =>  array_column($records, 'jmldenbar'),
			'label' => "Denpasar Barat",
	'backgroundColor' =>  random_color(), 
	'borderColor' =>  random_color(), 
	'borderWidth' => '2',
		];
		$datasets[] = $dataset3;
		$chart_data['datasets'] = $datasets;
		$chart_data['labels'] = $chart_labels;
		return $chart_data;
	}
	

	/**
	* barchart_sisablankowniperharistrftimeb Model Action
	* @return array
	*/
	function barchart_sisablankowniperharistrftimeb(){
		$request = request();
		$chart_data  = [];
		$sqltext = "select DATE_FORMAT(a.created,'%d-%m-%Y') as tahun, a.jumlah as jmldensel, b.jumlah as jmldentim, c.jumlah as jmldenbar, d.jumlah as jmldenut from layanan_kecamatan a inner join layanan_kecamatan b on a.created = b.created inner join layanan_kecamatan c on b.created = c.created inner join layanan_kecamatan d on c.created = d.created and a.id_kecamatan = 1 and b.id_kecamatan = 2 and c.id_kecamatan = 3 and d.id_kecamatan = 4 and a.id_jenis_layanan_kecamatan = 14 and b.id_jenis_layanan_kecamatan = 14 and c.id_jenis_layanan_kecamatan = 14 and d.id_jenis_layanan_kecamatan = 14 and MONTH(a.created) = Month(Now()) order by a.created";
		$query_params = [];
		$records = DB::select(DB::raw($sqltext), $query_params);
		$chart_labels = array_column($records, 'tahun');
		$datasets = [];
		$dataset1 = [
			'data' =>  array_column($records, 'jmldensel'),
			'label' => "Denpasar Selatan",
	'backgroundColor' =>  random_color(), 
	'borderColor' =>  random_color(), 
	'borderWidth' => '2',
		];
		$datasets[] = $dataset1;
		$dataset2 = [
			'data' =>  array_column($records, 'jmldentim'),
			'label' => "Denpasar Timur",
	'backgroundColor' =>  random_color(), 
	'borderColor' =>  random_color(), 
	'borderWidth' => '2',
		];
		$datasets[] = $dataset2;
		$dataset3 = [
			'data' =>  array_column($records, 'jmldenbar'),
			'label' => "Denpasar Barat",
	'backgroundColor' =>  random_color(), 
	'borderColor' =>  random_color(), 
	'borderWidth' => '2',
		];
		$datasets[] = $dataset3;
		$chart_data['datasets'] = $datasets;
		$chart_data['labels'] = $chart_labels;
		return $chart_data;
	}
	

	/**
	* barchart_sisablankownaperharistrftimeb Model Action
	* @return array
	*/
	function barchart_sisablankownaperharistrftimeb(){
		$request = request();
		$chart_data  = [];
		$sqltext = "select DATE_FORMAT(a.created,'%d-%m-%Y') as tahun, a.jumlah as jmldensel, b.jumlah as jmldentim, c.jumlah as jmldenbar, d.jumlah as jmldenut from layanan_kecamatan a inner join layanan_kecamatan b on a.created = b.created inner join layanan_kecamatan c on b.created = c.created inner join layanan_kecamatan d on c.created = d.created and a.id_kecamatan = 1 and b.id_kecamatan = 2 and c.id_kecamatan = 3 and d.id_kecamatan = 4 and a.id_jenis_layanan_kecamatan = 17 and b.id_jenis_layanan_kecamatan = 17 and c.id_jenis_layanan_kecamatan = 17 and d.id_jenis_layanan_kecamatan = 17 and MONTH(a.created) = Month(Now()) order by a.created";
		$query_params = [];
		$records = DB::select(DB::raw($sqltext), $query_params);
		$chart_labels = array_column($records, 'tahun');
		$datasets = [];
		$dataset1 = [
			'data' =>  array_column($records, 'jmldensel'),
			'label' => "Denpasar Selatan",
	'backgroundColor' =>  random_color(), 
	'borderColor' =>  random_color(), 
	'borderWidth' => '2',
		];
		$datasets[] = $dataset1;
		$dataset2 = [
			'data' =>  array_column($records, 'jmldentim'),
			'label' => "Denpasar Timur",
	'backgroundColor' =>  random_color(), 
	'borderColor' =>  random_color(), 
	'borderWidth' => '2',
		];
		$datasets[] = $dataset2;
		$dataset3 = [
			'data' =>  array_column($records, 'jmldenbar'),
			'label' => "Denpasar Barat",
	'backgroundColor' =>  random_color(), 
	'borderColor' =>  random_color(), 
	'borderWidth' => '2',
		];
		$datasets[] = $dataset3;
		$chart_data['datasets'] = $datasets;
		$chart_data['labels'] = $chart_labels;
		return $chart_data;
	}
	

	/**
	* doughnutchart_layananverifikatorhariini Model Action
	* @return array
	*/
	function doughnutchart_layananverifikatorhariini(){
		$request = request();
		$chart_data  = [];
		$sqltext = "select b.layanan_verifikator, a.jumlah from layanan_verifikator a left join jenis_layanan_verifikator b on a.id_jenis_layanan_verifikator = b.id where created = CURDATE() and a.id_jenis_layanan_verifikator <> 8 order by a.id_jenis_layanan_verifikator";
		$query_params = [];
		$records = DB::select(DB::raw($sqltext), $query_params);
		$chart_labels = array_column($records, 'layanan_verifikator');
		$datasets = [];
		$dataset1 = [
			'data' =>  array_column($records, 'jumlah'),
			'label' => "",
	'backgroundColor' =>  arr_random_color(count($chart_labels)), 
	'borderColor' =>  arr_random_color(count($chart_labels)), 
	'borderWidth' => '2',
		];
		$datasets[] = $dataset1;
		$chart_data['datasets'] = $datasets;
		$chart_data['labels'] = $chart_labels;
		return $chart_data;
	}
	

	/**
     * getcount_denpasarselatan Model Action
     * @return int
     */
	function getcount_denpasarselatan(){
		$sqltext = "SELECT jumlah as num FROM layanan_kecamatan where id_kecamatan = 1 and id_jenis_layanan_kecamatan = 14 and created = CURDATE()";
		$query_params = [];
		$val = DB::selectOne(DB::raw($sqltext), $query_params);
		return $val->num;
	}
	

	/**
     * getcount_denpasartimur Model Action
     * @return int
     */
	function getcount_denpasartimur(){
		$sqltext = "SELECT jumlah as num FROM layanan_kecamatan where id_kecamatan = 2 and id_jenis_layanan_kecamatan = 14 and created = CURDATE()";
		$query_params = [];
		$val = DB::selectOne(DB::raw($sqltext), $query_params);
		return $val->num;
	}
	

	/**
     * getcount_denpasarbarat Model Action
     * @return int
     */
	function getcount_denpasarbarat(){
		$sqltext = "SELECT jumlah as num FROM layanan_kecamatan where id_kecamatan = 3 and id_jenis_layanan_kecamatan = 14 and created = CURDATE()";
		$query_params = [];
		$val = DB::selectOne(DB::raw($sqltext), $query_params);
		return $val->num;
	}
	

	/**
     * getcount_denpasarutara Model Action
     * @return int
     */
	function getcount_denpasarutara(){
		$sqltext = "SELECT jumlah as num FROM layanan_kecamatan where id_kecamatan = 4 and id_jenis_layanan_kecamatan = 14 and created = CURDATE()";
		$query_params = [];
		$val = DB::selectOne(DB::raw($sqltext), $query_params);
		return $val->num;
	}
	

	/**
     * getcount_cetakkiaektppaket Model Action
     * @return int
     */
	function getcount_cetakkiaektppaket(){
		$sqltext = "SELECT jumlah as num FROM layanan_cetak_kia_ktp_paket where id_jenis_layanan_cetak_kia_ktp_paket = 11 and created = CURDATE()";
		$query_params = [];
		$val = DB::selectOne(DB::raw($sqltext), $query_params);
		return $val->num;
	}
	

	/**
     * getcount_layanangsd Model Action
     * @return int
     */
	function getcount_layanangsd(){
		$sqltext = "SELECT jumlah as num FROM layanan_gsd where id_jenis_layanan_gsd = 16 and created = CURDATE()";
		$query_params = [];
		$val = DB::selectOne(DB::raw($sqltext), $query_params);
		return $val->num;
	}
	

	/**
	* barchart_phpsetlocalelc_allididid_idjumlahaktakelahiranstrftimeb Model Action
	* @return array
	*/
	function barchart_phpsetlocalelc_allididid_idjumlahaktakelahiranstrftimeb(){
		$request = request();
		$chart_data  = [];
		$sqltext = "select DATE_FORMAT(created,'%d-%m-%Y') as tahun, jumlah FROM `layanan_verifikator` where id_jenis_layanan_verifikator = 1 and MONTH(created) = Month(Now()) order by created";
		$query_params = [];
		$records = DB::select(DB::raw($sqltext), $query_params);
		$chart_labels = array_column($records, 'tahun');
		$datasets = [];
		$dataset1 = [
			'data' =>  array_column($records, 'jumlah'),
			'label' => "Akta Kelahiran",
	'backgroundColor' =>  random_color(), 
	'borderColor' =>  random_color(), 
	'borderWidth' => '2',
		];
		$datasets[] = $dataset1;
		$chart_data['datasets'] = $datasets;
		$chart_data['labels'] = $chart_labels;
		return $chart_data;
	}
	

	/**
	* barchart_jumlahaktaperkawinanstrftimeb Model Action
	* @return array
	*/
	function barchart_jumlahaktaperkawinanstrftimeb(){
		$request = request();
		$chart_data  = [];
		$sqltext = "SELECT DATE_FORMAT(created,'%d-%m-%Y') as tahun, jumlah FROM `layanan_verifikator` where id_jenis_layanan_verifikator = 2 and MONTH(created) = Month(Now()) order by created";
		$query_params = [];
		$records = DB::select(DB::raw($sqltext), $query_params);
		$chart_labels = array_column($records, 'tahun');
		$datasets = [];
		$dataset1 = [
			'data' =>  array_column($records, 'jumlah'),
			'label' => "Akta Perkawinan",
	'backgroundColor' =>  random_color(), 
	'borderColor' =>  random_color(), 
	'borderWidth' => '2',
		];
		$datasets[] = $dataset1;
		$chart_data['datasets'] = $datasets;
		$chart_data['labels'] = $chart_labels;
		return $chart_data;
	}
	

	/**
	* barchart_jumlahaktaperceraianstrftimeb Model Action
	* @return array
	*/
	function barchart_jumlahaktaperceraianstrftimeb(){
		$request = request();
		$chart_data  = [];
		$sqltext = "SELECT DATE_FORMAT(created,'%d-%m-%Y') as tahun, jumlah FROM `layanan_verifikator` where id_jenis_layanan_verifikator = 3 and MONTH(created) = Month(Now()) order by created";
		$query_params = [];
		$records = DB::select(DB::raw($sqltext), $query_params);
		$chart_labels = array_column($records, 'tahun');
		$datasets = [];
		$dataset1 = [
			'data' =>  array_column($records, 'jumlah'),
			'label' => "Akta Perceraian",
	'backgroundColor' =>  random_color(), 
	'borderColor' =>  random_color(), 
	'borderWidth' => '2',
		];
		$datasets[] = $dataset1;
		$chart_data['datasets'] = $datasets;
		$chart_data['labels'] = $chart_labels;
		return $chart_data;
	}
	

	/**
	* barchart_jumlahaktakematianstrftimeb Model Action
	* @return array
	*/
	function barchart_jumlahaktakematianstrftimeb(){
		$request = request();
		$chart_data  = [];
		$sqltext = "SELECT DATE_FORMAT(created,'%d-%m-%Y') as tahun, jumlah FROM `layanan_verifikator` where id_jenis_layanan_verifikator = 4 and MONTH(created) = Month(Now()) order by created";
		$query_params = [];
		$records = DB::select(DB::raw($sqltext), $query_params);
		$chart_labels = array_column($records, 'tahun');
		$datasets = [];
		$dataset1 = [
			'data' =>  array_column($records, 'jumlah'),
			'label' => "Akta Kematian",
	'backgroundColor' =>  random_color(), 
	'borderColor' =>  random_color(), 
	'borderWidth' => '2',
		];
		$datasets[] = $dataset1;
		$chart_data['datasets'] = $datasets;
		$chart_data['labels'] = $chart_labels;
		return $chart_data;
	}
	

	/**
	* barchart_jumlahkartukeluargastrftimeb Model Action
	* @return array
	*/
	function barchart_jumlahkartukeluargastrftimeb(){
		$request = request();
		$chart_data  = [];
		$sqltext = "SELECT DATE_FORMAT(created,'%d-%m-%Y') as tahun, jumlah FROM `layanan_verifikator` where id_jenis_layanan_verifikator = 5 and MONTH(created) = Month(Now()) order by created";
		$query_params = [];
		$records = DB::select(DB::raw($sqltext), $query_params);
		$chart_labels = array_column($records, 'tahun');
		$datasets = [];
		$dataset1 = [
			'data' =>  array_column($records, 'jumlah'),
			'label' => "Kartu Keluarga",
	'backgroundColor' =>  random_color(), 
	'borderColor' =>  random_color(), 
	'borderWidth' => '2',
		];
		$datasets[] = $dataset1;
		$chart_data['datasets'] = $datasets;
		$chart_data['labels'] = $chart_labels;
		return $chart_data;
	}
	

	/**
	* barchart_jumlahsuratpindahstrftimeb Model Action
	* @return array
	*/
	function barchart_jumlahsuratpindahstrftimeb(){
		$request = request();
		$chart_data  = [];
		$sqltext = "SELECT DATE_FORMAT(created,'%d-%m-%Y') as tahun, jumlah FROM `layanan_verifikator` where id_jenis_layanan_verifikator = 6 and MONTH(created) = Month(Now()) order by created";
		$query_params = [];
		$records = DB::select(DB::raw($sqltext), $query_params);
		$chart_labels = array_column($records, 'tahun');
		$datasets = [];
		$dataset1 = [
			'data' =>  array_column($records, 'jumlah'),
			'label' => "Surat Pindah",
	'backgroundColor' =>  random_color(), 
	'borderColor' =>  random_color(), 
	'borderWidth' => '2',
		];
		$datasets[] = $dataset1;
		$chart_data['datasets'] = $datasets;
		$chart_data['labels'] = $chart_labels;
		return $chart_data;
	}
	

	/**
	* barchart_jumlahaktaatausklainnyastrftimeb Model Action
	* @return array
	*/
	function barchart_jumlahaktaatausklainnyastrftimeb(){
		$request = request();
		$chart_data  = [];
		$sqltext = "SELECT DATE_FORMAT(created,'%d-%m-%Y') as tahun, jumlah FROM `layanan_verifikator` where id_jenis_layanan_verifikator = 7 and MONTH(created) = Month(Now()) order by created";
		$query_params = [];
		$records = DB::select(DB::raw($sqltext), $query_params);
		$chart_labels = array_column($records, 'tahun');
		$datasets = [];
		$dataset1 = [
			'data' =>  array_column($records, 'jumlah'),
			'label' => "Akta atau SK Lainnya",
	'backgroundColor' =>  random_color(), 
	'borderColor' =>  random_color(), 
	'borderWidth' => '2',
		];
		$datasets[] = $dataset1;
		$chart_data['datasets'] = $datasets;
		$chart_data['labels'] = $chart_labels;
		return $chart_data;
	}
	

	/**
	* barchart_jumlahtotallayananstrftimeb Model Action
	* @return array
	*/
	function barchart_jumlahtotallayananstrftimeb(){
		$request = request();
		$chart_data  = [];
		$sqltext = "SELECT DATE_FORMAT(created,'%d-%m-%Y') as tahun, jumlah FROM `layanan_verifikator` where id_jenis_layanan_verifikator = 8 and MONTH(created) = Month(Now()) order by created";
		$query_params = [];
		$records = DB::select(DB::raw($sqltext), $query_params);
		$chart_labels = array_column($records, 'tahun');
		$datasets = [];
		$dataset1 = [
			'data' =>  array_column($records, 'jumlah'),
			'label' => "Total Layanan",
	'backgroundColor' =>  random_color(), 
	'borderColor' =>  random_color(), 
	'borderWidth' => '2',
		];
		$datasets[] = $dataset1;
		$chart_data['datasets'] = $datasets;
		$chart_data['labels'] = $chart_labels;
		return $chart_data;
	}
}
