<?php namespace App\Http\Controllers;

	use Session;
	use Request;
	use DB;
	use CRUDBooster;
	use Excel;

	class AdminPppkeImportController extends \crocodicstudio\crudbooster\controllers\CBController {

	    public function cbInit() {

			# START CONFIGURATION DO NOT REMOVE THIS LINE
			$this->title_field = "id";
			$this->limit = "20";
			$this->orderby = "id,desc";
			$this->global_privilege = false;
			$this->button_table_action = true;
			$this->button_bulk_action = true;
			$this->button_action_style = "button_icon";
			$this->button_add = true;
			$this->button_edit = true;
			$this->button_delete = true;
			$this->button_detail = true;
			$this->button_show = false;
			$this->button_filter = true;
			$this->button_import = false;
			$this->button_export = false;
			$this->table = "pppke_import";
			# END CONFIGURATION DO NOT REMOVE THIS LINE

			# START COLUMNS DO NOT REMOVE THIS LINE
			$this->col = [];
			$this->col[] = ["label"=>"File","name"=>"file"];
			$this->col[] = ["label"=>"Tanggal","name"=>"tanggal"];
			$this->col[] = ["label"=>"Uploaded By","name"=>"cms_users_id","join"=>"cms_users,name"];
			//$this->col[] = ["label"=>"Hasil","name"=>"hasil"];
			$this->col[] = ["label"=>"Imported at","name"=>"imported_at"];
			# END COLUMNS DO NOT REMOVE THIS LINE

			# START FORM DO NOT REMOVE THIS LINE
			$this->form = [];
			$this->form[] = ['label'=>'Upload xlsx','name'=>'file','type'=>'upload','validation'=>'required','width'=>'col-sm-5'];
			$this->form[] = ['label'=>'Tanggal Data','name'=>'tanggal','type'=>'date','validation'=>'required|date','width'=>'col-sm-3'];
			$this->form[] = ['label'=>'Hasil','name'=>'hasil','type'=>'textarea','width'=>'col-sm-10'];
			$this->form[] = ['label'=>'Cms Users Id','name'=>'cms_users_id','type'=>'hidden','value'=>CRUDBooster::myId()];
			# END FORM DO NOT REMOVE THIS LINE

			# OLD START FORM
			//$this->form = [];
			//$this->form[] = ["label"=>"File","name"=>"file","type"=>"textarea","required"=>TRUE,"validation"=>"required|string|min:5|max:5000"];
			//$this->form[] = ["label"=>"Tanggal","name"=>"tanggal","type"=>"date","required"=>TRUE,"validation"=>"required|date"];
			//$this->form[] = ["label"=>"Cms Users Id","name"=>"cms_users_id","type"=>"select2","required"=>TRUE,"validation"=>"required|integer|min:0","datatable"=>"cms_users,name"];
			//$this->form[] = ["label"=>"Hasil","name"=>"hasil","type"=>"textarea","required"=>TRUE,"validation"=>"required|string|min:5|max:5000"];
			# OLD END FORM

			/* 
	        | ---------------------------------------------------------------------- 
	        | Sub Module
	        | ----------------------------------------------------------------------     
			| @label          = Label of action 
			| @path           = Path of sub module
			| @foreign_key 	  = foreign key of sub table/module
			| @button_color   = Bootstrap Class (primary,success,warning,danger)
			| @button_icon    = Font Awesome Class  
			| @parent_columns = Sparate with comma, e.g : name,created_at
	        | 
	        */
	        $this->sub_module = array();


	        /* 
	        | ---------------------------------------------------------------------- 
	        | Add More Action Button / Menu
	        | ----------------------------------------------------------------------     
	        | @label       = Label of action 
	        | @url         = Target URL, you can use field alias. e.g : [id], [name], [title], etc
	        | @icon        = Font awesome class icon. e.g : fa fa-bars
	        | @color 	   = Default is primary. (primary, warning, succecss, info)     
	        | @showIf 	   = If condition when action show. Use field alias. e.g : [id] == 1
	        | 
	        */
	        $this->addaction = array();
	        $this->addaction[] = ['label'=>'Import','icon'=>'fa','color'=>'warning','url'=>CRUDBooster::mainpath('import').'/[id]'];


	        /* 
	        | ---------------------------------------------------------------------- 
	        | Add More Button Selected
	        | ----------------------------------------------------------------------     
	        | @label       = Label of action 
	        | @icon 	   = Icon from fontawesome
	        | @name 	   = Name of button 
	        | Then about the action, you should code at actionButtonSelected method 
	        | 
	        */
	        $this->button_selected = array();

	                
	        /* 
	        | ---------------------------------------------------------------------- 
	        | Add alert message to this module at overheader
	        | ----------------------------------------------------------------------     
	        | @message = Text of message 
	        | @type    = warning,success,danger,info        
	        | 
	        */
	        $this->alert        = array();
	                

	        
	        /* 
	        | ---------------------------------------------------------------------- 
	        | Add more button to header button 
	        | ----------------------------------------------------------------------     
	        | @label = Name of button 
	        | @url   = URL Target
	        | @icon  = Icon from Awesome.
	        | 
	        */
	        $this->index_button = array();



	        /* 
	        | ---------------------------------------------------------------------- 
	        | Customize Table Row Color
	        | ----------------------------------------------------------------------     
	        | @condition = If condition. You may use field alias. E.g : [id] == 1
	        | @color = Default is none. You can use bootstrap success,info,warning,danger,primary.        
	        | 
	        */
	        $this->table_row_color = array();     	          

	        
	        /*
	        | ---------------------------------------------------------------------- 
	        | You may use this bellow array to add statistic at dashboard 
	        | ---------------------------------------------------------------------- 
	        | @label, @count, @icon, @color 
	        |
	        */
	        $this->index_statistic = array();



	        /*
	        | ---------------------------------------------------------------------- 
	        | Add javascript at body 
	        | ---------------------------------------------------------------------- 
	        | javascript code in the variable 
	        | $this->script_js = "function() { ... }";
	        |
	        */
	        $this->script_js = NULL;


            /*
	        | ---------------------------------------------------------------------- 
	        | Include HTML Code before index table 
	        | ---------------------------------------------------------------------- 
	        | html code to display it before index table
	        | $this->pre_index_html = "<p>test</p>";
	        |
	        */
	        $this->pre_index_html = null;
	        
	        
	        
	        /*
	        | ---------------------------------------------------------------------- 
	        | Include HTML Code after index table 
	        | ---------------------------------------------------------------------- 
	        | html code to display it after index table
	        | $this->post_index_html = "<p>test</p>";
	        |
	        */
	        $this->post_index_html = null;
	        
	        
	        
	        /*
	        | ---------------------------------------------------------------------- 
	        | Include Javascript File 
	        | ---------------------------------------------------------------------- 
	        | URL of your javascript each array 
	        | $this->load_js[] = asset("myfile.js");
	        |
	        */
	        $this->load_js = array();
	        
	        
	        
	        /*
	        | ---------------------------------------------------------------------- 
	        | Add css style at body 
	        | ---------------------------------------------------------------------- 
	        | css code in the variable 
	        | $this->style_css = ".style{....}";
	        |
	        */
	        $this->style_css = NULL;
	        
	        
	        
	        /*
	        | ---------------------------------------------------------------------- 
	        | Include css File 
	        | ---------------------------------------------------------------------- 
	        | URL of your css each array 
	        | $this->load_css[] = asset("myfile.css");
	        |
	        */
	        $this->load_css = array();
	        
	        
	    }


	    /*
	    | ---------------------------------------------------------------------- 
	    | Hook for button selected
	    | ---------------------------------------------------------------------- 
	    | @id_selected = the id selected
	    | @button_name = the name of button
	    |
	    */
	    public function actionButtonSelected($id_selected,$button_name) {
	        //Your code here
	            
	    }


	    /*
	    | ---------------------------------------------------------------------- 
	    | Hook for manipulate query of index result 
	    | ---------------------------------------------------------------------- 
	    | @query = current sql query 
	    |
	    */
	    public function hook_query_index(&$query) {
	        //Your code here
	            
	    }

	    /*
	    | ---------------------------------------------------------------------- 
	    | Hook for manipulate row of index table html 
	    | ---------------------------------------------------------------------- 
	    |
	    */    
	    public function hook_row_index($column_index,&$column_value) {	        
	    	//Your code here
	    }

	    /*
	    | ---------------------------------------------------------------------- 
	    | Hook for manipulate data input before add data is execute
	    | ---------------------------------------------------------------------- 
	    | @arr
	    |
	    */
	    public function hook_before_add(&$postdata) {        
	        //Your code here

	    }

	    /* 
	    | ---------------------------------------------------------------------- 
	    | Hook for execute command after add public static function called 
	    | ---------------------------------------------------------------------- 
	    | @id = last insert id
	    | 
	    */
	    public function hook_after_add($id) {        
	        //Your code here

	    }

	    /* 
	    | ---------------------------------------------------------------------- 
	    | Hook for manipulate data input before update data is execute
	    | ---------------------------------------------------------------------- 
	    | @postdata = input post data 
	    | @id       = current id 
	    | 
	    */
	    public function hook_before_edit(&$postdata,$id) {        
	        //Your code here

	    }

	    /* 
	    | ---------------------------------------------------------------------- 
	    | Hook for execute command after edit public static function called
	    | ----------------------------------------------------------------------     
	    | @id       = current id 
	    | 
	    */
	    public function hook_after_edit($id) {
	        //Your code here 

	    }

	    /* 
	    | ---------------------------------------------------------------------- 
	    | Hook for execute command before delete public static function called
	    | ----------------------------------------------------------------------     
	    | @id       = current id 
	    | 
	    */
	    public function hook_before_delete($id) {
	        //Your code here

	    }

	    /* 
	    | ---------------------------------------------------------------------- 
	    | Hook for execute command after delete public static function called
	    | ----------------------------------------------------------------------     
	    | @id       = current id 
	    | 
	    */
	    public function hook_after_delete($id) {
	        //Your code here

	    }
	    
	    public function getImport($id) {
	        DB::table('pppke_import')->where('id',$id)->update(["imported_at"=>now()]);
            $data['file_import'] = DB::table('pppke_import')->where('id',$id)->first();
            
            try {
                $file = storage_path('app/'.$data['file_import']->file);
                $rows = Excel::load($file, function ($reader) {
                })->get();
                
                $countRows = ($rows)?count($rows):0;
                
                Session::put('total_data_import', $countRows);
    
                $data_import_column = ['id_keluarga_p3ke','dimutakhirkan','kelurahan','village_id','alamat','nama_kk','nik_kk','jk_kk','pekerjaan_kk','tgl_lhr_kk'];
                $field_to_compare = ['village_id','alamat','nama_kk','nik_kk','jk_kk','pekerjaan_kk','tgl_lhr_kk'];
    
                $logs="";
                $updated = 0;
                $added = 0;
                $error = 0;
                $tglerror = 0;
                $warning = 0;
                foreach ($rows as $value) {
                    try{
                        $nik = str_replace(' ', '', $value->nik_kk);
                        $exist = DB::table('warga_bjb')->whereRaw('REPLACE(nik, " ","") = "'. $nik . '"')->first();
        
                        if($exist){
                            similar_text($exist->nama_lgkp,$value->nama_kk,$percentnama);
                            similar_text($exist->tgl_lhr,$value->tgl_lhr_kk,$percenttgl);
                            similar_text($exist->alamat,$value->alamat,$percentalamat);
                            similar_text($exist->nama_kel,$value->village_id,$percentkel);
                            $percenttotal = ($percentnama+$percenttgl)/2;
                            if($percenttotal>=88){
                                //$logs = $logs . ";" . $percenttotal . '; nama :' . $percentnama . '; tgl_lhr :' . $percenttgl . '; alamat :' . $percentalamat;
                                //dd($logs);
                                $wargaexist = DB::table('pppke')->where('warga_bjb_id',$exist->id)->where('dimutakhirkan',$value->dimutakhirkan)->first();
                                if(!$wargaexist){
                                    DB::table('pppke')->insert([
                                        "pppke_import_id"=>$id,
                                        "id_keluarga_p3ke"=>$value->id_keluarga_p3ke,
                                        "warga_bjb_id"=>$exist->id,
                                        "village_id"=>$value->village_id,
                                        "provinsi"=>$value->provinsi,
                                        "kabupaten"=>$value->kabupaten,
                                        "kecamatan"=>$value->kecamatan,
                                        "kelurahan"=>$value->kelurahan,
                                        "dimutakhirkan"=>$value->dimutakhirkan,
                                        "desil"=>$value->desil,
                                        "persentil"=>$value->persentil,
                                        "note"=>"alamat : " . $percentalamat . " Kelurahan : " . $percentkel
                                        ]);
                                        
                                    $added++;
                                }
                                //echo "->Update->Lolos->" . date('Y-m-d H:i:s',strtotime($value->tgl_peserta)) .";";
                            }else{
                                
                                $logs = $logs . " nik :" . $nik . " similarity :" . $percenttotal . '; nama :' . $percentnama . '; tgl_lhr :' . $percenttgl . '; alamat :' . $percentalamat . " Tidak Sesuai.\n";
                                //dd($logs);
                                $wargaexist = DB::table('pppke')->where('warga_bjb_id',$exist->id)->where('dimutakhirkan',$value->dimutakhirkan)->first();
                                if(!$wargaexist){
                                    DB::table('pppke')->insert([
                                        "pppke_import_id"=>$id,
                                        "id_keluarga_p3ke"=>$value->id_keluarga_p3ke,
                                        "warga_bjb_id"=>$exist->id,
                                        "village_id"=>$value->village_id,
                                        "provinsi"=>$value->provinsi,
                                        "kabupaten"=>$value->kabupaten,
                                        "kecamatan"=>$value->kecamatan,
                                        "kelurahan"=>$value->kelurahan,
                                        "dimutakhirkan"=>$value->dimutakhirkan,
                                        "desil"=>$value->desil,
                                        "persentil"=>$value->persentil,
                                        "note"=>" nik :" . $nik . " similarity :" . $percenttotal . '; nama :' . $percentnama . '; tgl_lhr :' . $percenttgl . '; alamat :' . $percentalamat . " Tidak Sesuai."
                                        ]);
                                        
                                    $warning++;
                                }
                            }
                        }else{
                           // DB::table('akumulasi_iuran')->insert(["nip"=>$nip,"sandi"=>$value->kode,"nama_peserta"=>$value->nama,"tgl_peserta"=>date('Y-m-d',strtotime($value->tgl_peserta)),"iuran_peserta"=>$value->iuran_peserta,"tgl_update"=>date('Y-m-d',strtotime($value->tgl_update))]);
                            $logs = $logs . $nik . " Tidak ditemukan. Menambahkan\n";
                            if($value->nik_kk){
                                if($value->jk_kk=="Laki-laki"){
                                    $jk = "L";
                                }else{
                                    $jk="P";
                                }
                                $newwarga =  DB::table('warga_bjb')->insert([
                                                    "nik"=>$value->nik_kk,
                                                    "nama_lgkp"=>$value->nama_kk,
                                                    "jk"=>$jk,
                                                    "tmpt_lhr"=>"Unknown",
                                                    "tgl_lhr"=>$value->tgl_lhr_kk,
                                                    "pekerjaan"=>$value->pekerjaan_kk,
                                                    "pddk_akhir"=>$value->pddk_kk,
                                                    "alamat"=>$value->alamat,
                                                    "alamat2"=>$value->alamat,
                                                    "nama_prop"=>substr($value->village_id, 0, 2),
                                                    "nama_kab"=>substr($value->village_id, 0, 4),
                                                    "nama_kec"=>substr($value->village_id, 0, 6),
                                                    "nama_kel"=>$value->village_id,
                                                    "rt"=>0,
                                                    "rw"=>0,
                                                ]);
                                DB::table('catatan_warga')->insert([
                                    'warga_bjb_id'=>$newwarga,
                                    'catatan' => "Ditambahkan dari data P3KE",
                                    'created_by' => "1",
                                    'created_at' => now(),
                                    'cms_privilege_id' => "1"
                                    ]);
                                $wargaexist = DB::table('pppke')->where('warga_bjb_id',$newwarga)->where('dimutakhirkan',$value->dimutakhirkan)->first();
                                if(!$wargaexist){
                                    DB::table('pppke')->insert([
                                            "pppke_import_id"=>$id,
                                            "id_keluarga_p3ke"=>$value->id_keluarga_p3ke,
                                            "warga_bjb_id"=>$newwarga,
                                            "village_id"=>$value->village_id,
                                            "provinsi"=>$value->provinsi,
                                            "kabupaten"=>$value->kabupaten,
                                            "kecamatan"=>$value->kecamatan,
                                            "kelurahan"=>$value->kelurahan,
                                            "dimutakhirkan"=>$value->dimutakhirkan,
                                            "desil"=>$value->desil,
                                            "persentil"=>$value->persentil,
                                            "note"=>" nik :" . $value->nik_kk . " data warga baru"
                                        ]);
                                        
                                    $updated++;
                                }
                            }else{
                                $logs = $logs . $nik . " Kosong. Skipped\n";
                            }
                        }
                    }catch(Exception $e) {
                        CRUDBooster::redirect(CRUDBooster::mainpath(),$e->getMessage(),"danger");
                    }
                    
    
                    
                }
                
                $logs =  $added . " ditambahkan, ". $warning . " warning."  . $error . " error.". $updated . " updated.\n". $logs; 
     
                
                DB::table('pppke_import')->where('id',$id)->update(["hasil"=>$logs]);
                CRUDBooster::redirect(CRUDBooster::mainpath(),"Data telah di import","success");
            }catch(Exception $e) {
                CRUDBooster::redirect(CRUDBooster::mainpath(),$e->getMessage(),"danger");
            }
            //$file = base64_decode(Request::get('file'));
            
    	}



	    //By the way, you can still create your own method in here... :) 


	}