<?php namespace App\Http\Controllers;

	use Session;
	use Request;
	use DB;
	use CRUDBooster;
	use Debugbar;
	use Carbon\Carbon;
	use User;
	use Barryvdh\DomPDF\Facade as PDF;
	use \PhpOffice\PhpWord as WORD;
	use File;
	use Illuminate\Support\Facades\Schema;
	use App\QRlib\QRCodeProv;
	
	


	class AdminSuratMasukController extends \crocodicstudio\crudbooster\controllers\CBController {
	    protected static $delimiter;
	    protected static $url;

	    public function cbInit() {
	        
            static::$delimiter = uniqid();

			# 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 = false;
			$this->button_action_style = "button_dropdown";
			$this->button_add = false;
			$this->button_edit = false;
			$this->button_delete = true;
			$this->button_detail = false;
			$this->button_show = false;
			$this->button_filter = true;
			$this->button_import = false;
			$this->button_export = false;
			$this->table = "surat_masuk";
// 			$this->layanan = DB::table('layanan')->where('created_by_priv',CRUDBooster::myPrivilegeId())->get();
			$this->layanan = DB::table('notasi_surat')->where('id_cms_privileges',CRUDBooster::myPrivilegeId())->get();
			$this->supported = [];
			foreach($this->layanan as $lyn){
			    array_push($this->supported,$lyn->id_surat);
			}
 			// var_dump($this->supported);
			
			# END CONFIGURATION DO NOT REMOVE THIS LINE

			# START COLUMNS DO NOT REMOVE THIS LINE
			$this->col = [];
			$this->col[] = ["label"=>"Jenis Surat","name"=>"jenis_surat_id","join"=>"surat,nama_surat"];
			$this->col[] = ["label"=>"Tanggal","name"=>"created_at"];
			$this->col[] = ["label"=>"Pemohon","name"=>"created_by","join"=>"cms_users,name"];
			$this->col[] = ["label"=>"Kelurahan","name"=>"village_id","join"=>"villages,name"];
			$this->col[] = ["label"=>"Kecamatan","name"=>"district_id","join"=>"districts,name"];
			$this->col[] = ["label"=>"RT","name"=>"rt"];
			$this->col[] = ["label"=>"RW","name"=>"rw"];
			$this->col[] = ["label"=>"No SK RT","name"=>"no_sk_rt"];
			$this->col[] = ["label"=>"Tanggal SK RT","name"=>"tgl_sk_rt"];
			if(CRUDBooster::myPrivilegeId()>4 && CRUDBooster::myPrivilegeId()==1) {
			$this->col[] = ["label"=>"Doc","name"=>"gen_doc","download"=>true];
			$this->col[] = ["label"=>"Pdf","name"=>"gen_pdf","download"=>true];
			}
			$this->col[] = ["label"=>"Status","name"=>"status_id","join"=>"status_surat,status"];
			$this->col[] = ["label"=>"Oleh","name"=>"status_by_priv","join"=>"cms_privileges,name"];
			$this->col[] = ["label"=>"Tabel","name"=>"rel_table"];
			$this->col[] = ["label"=>"ID Rel","name"=>"rel_id"];
			$this->col[] = ["label"=>"Role","name"=>"(SELECT aksi_diperlukan.keterangan_aksi FROM notasi_surat,aksi_diperlukan WHERE notasi_surat.aksi_diperlukan_id = aksi_diperlukan.id AND notasi_surat.id_cms_privileges = " . CRUDBooster::myPrivilegeID() . " AND id_surat = surat_masuk.jenis_surat_id) as role","callback_php"=>"$row->role"];
			//$this->col[] = ["label"=>"Status","name"=>"(SELECT CONCAT(status_surat.status , ' ' ,cms_users.name) FROM verifikasi_surat LEFT JOIN status_surat ON verifikasi_surat.aksi = status_surat.id LEFT JOIN cms_users ON verifikasi_surat.user_id = cms_users.id LEFT JOIN surat_masuk ON verifikasi_surat.surat_id = surat_masuk.id WHERE verifikasi_surat.surat_id = surat_masuk.id ORDER BY verifikasi_surat.id desc LIMIT 1) as status","callback_php"=>'$row->status'];
			//$this->col[] = ["label"=>"Status","name"=>"(SELECT CONCAT(max(verifikasi_surat.aksi), ' ', status_surat.status) FROM verifikasi_surat,status_surat WHERE status_surat.id = verifikasi_surat.aksi AND verifikasi_surat.surat_id = surat_masuk.id GROUP BY verifikasi_surat.aksi) as status","callback_php"=>'$row->status'];
			# END COLUMNS DO NOT REMOVE THIS LINE

			# START FORM DO NOT REMOVE THIS LINE
			$this->form = [];
			//$this->form[] = ['label'=>'Jenis Surat','name'=>'jenis_surat_id','type'=>'select2','validation'=>'required|integer|min:0','width'=>'col-sm-10','datatable'=>'surat,nama_surat'];
			//$this->form[] = ['label'=>'Pemohon','name'=>'created_by','type'=>'text','validation'=>'required|min:1|max:255','width'=>'col-sm-10'];
			//$this->form[] = ['label'=>'Kelurahan','name'=>'village_id','type'=>'select2','validation'=>'required|min:1|max:255','width'=>'col-sm-6','datatable'=>'villages,name','datatable_where'=>'district_id like "'.CRUDBooster::getSetting('kode_kabupaten').'%"','value' => CRUDBooster::menik(CRUDBooster::me()->nik)->nama_kel];
			//$this->form[] = ['label'=>'Status Id','name'=>'status_id','type'=>'select2','validation'=>'required|integer|min:0','width'=>'col-sm-10','datatable'=>'status_surat,status'];
			$columns[] = ['label'=>'User ID','name'=>'user_id','type'=>'hidden','required'=>true,'value' => CRUDBooster::myId()];
			$columns[] = ['label'=>'Unggah','name'=>'file','type'=>'upload','required'=>true];
			$this->form[] = ['label'=>'Data Pendukung','name'=>'unggahan','type'=>'child','columns'=>$columns,'table'=>'unggahan','foreign_key'=>'surat_masuk_id'];
			$this->form[] = ['label'=>'AKSI','name'=>'verif_ket_rt_id','type'=>'radio','dataenum'=>'5|Verifikasi;4|Tolak'];
			# END FORM DO NOT REMOVE THIS LINE

			# OLD START FORM
			//$this->form = [];
			//$this->form[] = ["label"=>"Jenis Surat Id","name"=>"jenis_surat_id","type"=>"select2","required"=>TRUE,"validation"=>"required|integer|min:0","datatable"=>"jenis_surat,id"];
			//$this->form[] = ["label"=>"Created By","name"=>"created_by","type"=>"text","required"=>TRUE,"validation"=>"required|min:1|max:255"];
			//$this->form[] = ["label"=>"Status Id","name"=>"status_id","type"=>"select2","required"=>TRUE,"validation"=>"required|integer|min:0","datatable"=>"status,id"];
			# 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();
// 			if(CRUDBooster::myPrivilegeId()==5) { // admin lurah
// 				$this->addaction[] = ['label'=>'Proses','icon'=>'fa fa-eye','color'=>'info','url'=>CRUDBooster::mainpath('edit-custom').'/[id]','showIf'=>'[status_id] == 2'];
// 				$this->addaction[] = ['label'=>'Periksa','icon'=>'fa fa-eye','color'=>'info','url'=>CRUDBooster::mainpath('edit-custom').'/[id]','showIf'=>'[status_id] >= 3'];
// 				$this->addaction[] = ['label'=>'Selesaikan','icon'=>'fa fa-eye','color'=>'info','url'=>CRUDBooster::mainpath('set-selesai').'/[id]','showIf'=>'[status_id] == 4'];
// 				$this->addaction[] = ['label'=>'Baca','icon'=>'fa fa-eye','color'=>'warning','url'=>CRUDBooster::mainpath('edit-custom').'/[id]','showIf'=>'[status_id] == 1'];
// 			}
			
			
// 			if(CRUDBooster::myPrivilegeId()==6) { //lurah
// 				$this->addaction[] = ['label'=>'Tandatangani','icon'=>'fa fa-eye','color'=>'info','url'=>CRUDBooster::mainpath('edit-custom').'/[id]','showIf'=>'[status_id] == 3'];
// 				$this->addaction[] = ['label'=>'Periksa','icon'=>'fa fa-eye','color'=>'info','url'=>CRUDBooster::mainpath('edit-custom').'/[id]','showIf'=>'[status_id] >= 4'];
// 				$this->addaction[] = ['label'=>'Baca','icon'=>'fa fa-eye','color'=>'warning','url'=>CRUDBooster::mainpath('edit-custom').'/[id]','showIf'=>'[status_id] == 1'];
// 			}
			
			if(CRUDBooster::myPrivilegeId()!=2) { //Admin dinkop
			
			    //$this->addaction[] = ['label'=>'Verifikasi','icon'=>'fa fa-eye','color'=>'info','url'=>CRUDBooster::mainpath('lihat-surat').'/[id]/[rel_table]/[rel_id]','showIf'=>'[status_id] < 5 AND [role] == Memverifikasi'];
				$this->addaction[] = ['label'=>'Lihat','icon'=>'fa fa-eye','color'=>'warning','url'=>CRUDBooster::mainpath('lihat-surat').'/[id]/[rel_table]/[rel_id]'];//,'showIf'=>'[status_id] == 1 OR [status_id] == 5 OR [status_id] == 4 OR [status_by] > ' . CRUDBooster::myPrivilegeId()];
			 //   $this->addaction[] = ['label'=>'PDF','icon'=>'fa fa-letter','color'=>'success','url'=>CRUDBooster::mainpath('konversi-pdf').'/[id]/[rel_table]/[rel_id]'];
			  //  $this->addaction[] = ['label'=>'Lihat','icon'=>'fa fa-eye','color'=>'warning','url'=>CRUDBooster::mainpath('lihat-surat-dua').'/[id]'];
			}
			
// 			if(CRUDBooster::myPrivilegeId()==12) { //Admin DKP3
// 			    $this->addaction[] = ['label'=>'Verifikasi','icon'=>'fa fa-eye','color'=>'info','url'=>CRUDBooster::mainpath('lihat-surat').'/[id]/[rel_table]/[rel_id]/surat.lihatskph','showIf'=>'[status_id] == 2'];
// 				$this->addaction[] = ['label'=>'Lihat','icon'=>'fa fa-eye','color'=>'warning','url'=>CRUDBooster::mainpath('lihat-surat').'/[id]/[rel_table]/[rel_id]/surat.lihatskph','showIf'=>'[status_id] == 1'];
// 			}
			
			


	        /* 
	        | ---------------------------------------------------------------------- 
	        | 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();
    	    if(CRUDBooster::myPrivilegeId()==5 || CRUDBooster::myPrivilegeId()==6) {
    			$this->index_statistic[] = ['label'=>'Surat Masuk Baru','count'=>DB::table($this->table)->where('district_id',CRUDBooster::me()->district_id)
    																									 ->where('village_id',CRUDBooster::me()->village_id)
    																								// 	 ->where('owner_priv',CRUDBooster::myPrivilegeId())
    																									 ->where('status_id',3)
    																								// 	 ->where('status_by', '<' , CRUDBooster::myPrivilegeID())
    																								// 	 ->where('surat_masuk.owner_priv','<=',CRUDBooster::myPrivilegeId())
    																								     ->where('status_by_priv', '<' , CRUDBooster::myPrivilegeID())
    																									 ->whereIn('jenis_surat_id',$this->supported)
    																									 ->count(),'icon'=>'fa fa-bars','color'=>'red','width'=>'col-sm-3'];
    			$this->index_statistic[] = ['label'=>'Surat Diproses','count'=>DB::table($this->table)->where('district_id',CRUDBooster::me()->district_id)
    																									 ->where('village_id',CRUDBooster::me()->village_id)
    																								// 	 ->where('owner_priv',CRUDBooster::myPrivilegeId())
    																									 ->where('status_id','>',1)
    																									 ->where('status_id','<',5)
    																									 ->where('status_by', CRUDBooster::myPrivilegeID())
    																								// 	 ->where('surat_masuk.owner_priv','<=',CRUDBooster::myPrivilegeId())
    																									 ->whereIn('jenis_surat_id',$this->supported)   
    																									 ->count(),'icon'=>'fa fa-bars','color'=>'green','width'=>'col-sm-3'];
    			$this->index_statistic[] = ['label'=>'Surat Selesai','count'=>DB::table($this->table)->where('district_id',CRUDBooster::me()->district_id)
    																									 ->where('village_id',CRUDBooster::me()->village_id)
    																								// 	 ->where('owner_priv',CRUDBooster::myPrivilegeId())
    																									 ->where('status_id',5)
    																								// 	 ->where('surat_masuk.owner_priv','<=',CRUDBooster::myPrivilegeId())
    																									 ->whereIn('jenis_surat_id',$this->supported)
    																									 ->count(),'icon'=>'fa fa-bars','color'=>'blue','width'=>'col-sm-3'];
	        }elseif(CRUDBooster::myPrivilegeId()==2) {
	            
	        }else {
    			$this->index_statistic[] = ['label'=>'Permohonan Baru','count'=>DB::table($this->table)
    																									 ->where('status_id',0)
    																									 ->where('status_by','<', CRUDBooster::myPrivilegeID())
    																								 	 ->where('surat_masuk.owner_priv',CRUDBooster::myPrivilegeId())
    																									 ->whereIn('jenis_surat_id',$this->supported)
    																									 ->count(),'icon'=>'fa fa-bars','color'=>'red','width'=>'col-sm-3'];
    // 			$this->index_statistic[] = ['label'=>'Permohonan Dibaca','count'=>DB::table($this->table)
    // 																									 ->where('status_id',2)
    // 																									 ->where('surat_masuk.status_by_priv',CRUDBooster::myPrivilegeId())
    // 																									 ->whereIn('jenis_surat_id',$this->supported)
    // 																									 ->count(),'icon'=>'fa fa-bars','color'=>'yellow','width'=>'col-sm-3'];
    			$this->index_statistic[] = ['label'=>'Permohonan Diproses','count'=>DB::table($this->table)
    																									 ->where('status_id','>',1)
    																									 ->where('status_id','<',5)
    																									 ->where('status_by','>=', CRUDBooster::myPrivilegeID())
    																								 	 ->where('surat_masuk.owner_priv',CRUDBooster::myPrivilegeId())
    																									 ->whereIn('jenis_surat_id',$this->supported)
    																									 ->count(),'icon'=>'fa fa-bars','color'=>'green','width'=>'col-sm-3'];
    			$this->index_statistic[] = ['label'=>'Permohonan Selesai','count'=>DB::table($this->table)
    																									 ->where('status_id',5)
    																									 //->where('surat_masuk.status_by_priv',CRUDBooster::myPrivilegeId())
    																									 ->where('surat_masuk.owner_priv',CRUDBooster::myPrivilegeId())
    																									 ->whereIn('jenis_surat_id',$this->supported)
    																									 ->count(),'icon'=>'fa fa-bars','color'=>'blue','width'=>'col-sm-3'];
	        }
	        
	   //     if(CRUDBooster::myPrivilegeId()==12) {
    // 			$this->index_statistic[] = ['label'=>'Permohonan Baru','count'=>DB::table($this->table)
    // 																									 ->where('status_id',1)
    // 																									 ->where('surat_masuk.status_by_priv','<=',CRUDBooster::myPrivilegeId())
    // 																									 ->where('surat_masuk.jenis_surat_id','=',9)
    // 																									 ->count(),'icon'=>'fa fa-bars','color'=>'red','width'=>'col-sm-3'];
    // 			$this->index_statistic[] = ['label'=>'Permohonan Diproses','count'=>DB::table($this->table)
    // 																									 ->where('status_id',2)
    // 																									 ->where('surat_masuk.status_by_priv','<=',CRUDBooster::myPrivilegeId())
    // 																									 ->where('surat_masuk.jenis_surat_id','=',9)
    // 																									 ->count(),'icon'=>'fa fa-bars','color'=>'green','width'=>'col-sm-3'];
    // 			$this->index_statistic[] = ['label'=>'Permohonan Selesai','count'=>DB::table($this->table)
    // 																									 ->where('status_id',5)
    // 																									 ->where('surat_masuk.status_by_priv','<=',CRUDBooster::myPrivilegeId())
    // 																									 ->where('surat_masuk.jenis_surat_id','=',9)
    // 																									 ->count(),'icon'=>'fa fa-bars','color'=>'blue','width'=>'col-sm-3'];
	   //     }


	        /*
	        | ---------------------------------------------------------------------- 
	        | 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();
	        
	        
	    }
		
		// public function getDetail($id) {
		  // //Create an Auth
		  // if(!CRUDBooster::isRead() && $this->global_privilege==FALSE || $this->button_edit==FALSE) {    
			// CRUDBooster::redirect(CRUDBooster::adminPath(),trans("crudbooster.denied_access"));
		  // }
		  
		  // $data = [];
		  // $data['page_title'] = 'Detail Surat';
		  // $data['row'] = DB::table('surat_masuk')
				 // ->join('skck','surat_masuk.surat_id','=','skck.id')
				 // ->where('surat_masuk.id',$id)->first();
		  
		  // //Please use cbView method instead view method from laravel
		  // var_dump($data);
		  // $this->cbView('custom.detail_rt',$data);
		// }
		
		public function getEditCustom($id) {
		  //Create an Auth
		  // if(!CRUDBooster::isUpdate() && $this->global_privilege==FALSE || $this->button_edit==FALSE) {    
			// CRUDBooster::redirect(CRUDBooster::adminPath(),trans("crudbooster.denied_access"));
		  // }
		 
					
		  $data = [];
		  $data['page_title'] = 'Verifikasi Permohoan';
		  $data['row'] = DB::table('surat_masuk')
				->join('districts','surat_masuk.district_id','=','districts.id')
				->join('villages','surat_masuk.village_id','=','villages.id')
				->join('surat','surat_masuk.jenis_surat_id','=','surat.id')
				->where('surat_masuk.id',$id)
				->select('surat_masuk.*','villages.name as kelurahan','districts.name as  kecamatan', 'surat.nama_surat','surat_masuk.jenis_surat_id')
				->first();
				
    		$data['layanan'] = DB::table('layanan')
    							->where('jenis_surat_id',$data['row']->jenis_surat_id)
    							->where('village_id',CRUDBooster::me()->village_id)
    							->first();
    				
    		 if($data['row']->status_id == 1){
    		  DB::table('surat_masuk')
    		  ->where('id',$id)
    		  ->update(['status_id'=>2,'status_by'=>CRUDBooster::myId(),'status_by_priv'=>CRUDBooster::myPrivilegeId()]);
    		 }
    		
    		$data['unggahan'] = DB::table('unggahan')
    				->join('syarat','unggahan.syarat_id','=','syarat.id')
    				->where('unggahan.surat_masuk_id',$id)
    				->where('syarat.jenis_syarat','nomor')
    				->select('unggahan.*','syarat.deskripsi')
    				->get();
    				
    		 $data['verifikasi_surat']  = DB::Table('verifikasi_surat')
    				   ->where('surat_id',$data['row']->id)
    				   ->where('privilege_id',CRUDBooster::myPrivilegeId())
    				   ->first();	
    				   
    		$data['kembali'] = CRUDBooster::mainPath();
            $data['my_notasi'] = CRUDBooster::myNotasi($data['row']->jenis_surat_id);
            //	$data['my_notasi'] = CRUDBOOSTER::myPrivilegeId();
            
    		 $this->cbView( $data['my_notasi']->view_aksi,$data);
		}
		
		public function getLihatUsaha($id,$rel_table,$rel_id,$view){
		    $data = [];
		    $data['row'] = DB::table($rel_table)->where('id',$rel_id)->first();
		    $data['id'] = $id;
		    $data['rel_table'] = $rel_table;
		    $data['rel_id'] = $rel_id;
		    DB::table('surat_masuk')
    		  ->where('id',$id)
    		  ->update(['status_id'=>2,'status_by'=>CRUDBooster::myId(),'status_by_priv'=>CRUDBooster::myPrivilegeId()]);
		    
		    $this->cbView( $view,$data);
		}
		
		public function getLihatSurat($id,$rel_table,$rel_id){
		    $data = [];
		    $data['surat'] = DB::table('surat_masuk')->where('id',$id)->first();
		    $data['row'] = DB::table($rel_table)->where('id',$rel_id)->first();

		    if(property_exists($data['row'],'satuan_id')){
		        $data['row'] = DB::table($rel_table)
		                        ->leftJoin('satuan',$rel_table.'.satuan_id','satuan.id')
		                        ->where($rel_table.'.id',$rel_id)
		                        ->select($rel_table.'.*','satuan.satuan')
		                        ->first();
		    }
		    if($data['row']->nomor){
		        $data['nomor'] = '';
		    }
		    
		    $data['admrole'] = DB::table('notasi_surat')
		                            ->join('aksi_diperlukan','aksi_diperlukan.id','notasi_surat.aksi_diperlukan_id')
		                            ->where('notasi_surat.id_cms_privileges', CRUDBooster::myPrivilegeID())
		                            ->where('notasi_surat.id_surat',$data['surat']->jenis_surat_id)
		                            ->first();
		    
		  //  dd($data['admrole']);
		    
		    $data['warga'] = DB::table('warga_bjb')
            		        ->join('districts','warga_bjb.nama_kec','=','districts.id')
            				->join('villages','warga_bjb.nama_kel','=','villages.id')
            				->where('warga_bjb.id',$data['row']->warga_bjb_id)
            				->select('warga_bjb.*','villages.name as kelurahan','districts.name as  kecamatan')
		                    ->first();
		    $data['pemilik'] = DB::table('umkm_pemilik')->where('warga_bjb_id',$data['row']->warga_bjb_id)->first();
		    $data['usahaku'] = DB::table('umkm_usaha')->where('id',$data['row']->umkm_usaha_id)->get();
		    $data['usaha'] = DB::table('umkm_usaha')->where('warga_bjb_id',$data['row']->warga_bjb_id)->get();
		    $data['kelompokku'] = DB::table('grup')->where('id',$data['row']->grup_id)->get();
		    $data['bantuanku'] = DB::table('surat_permohonan_bantuan')->where('warga_bjb_id',$data['row']->warga_bjb_id)->get();
		    $data['pelatihanku'] = DB::table('surat_reg_pelatihan')->where('warga_bjb_id',$data['row']->warga_bjb_id)->get();
		  //  $data['jenis_surat'] = DB::table('surat')->where('tabel',$rel_table)->first();
		  //  $data['notasi_surat'] = DB::table('notasi_surat')->where('id_surat',$data['jenis_surat']->id)->where('id_cms_privileges',CRUDBooster::myPrivilegeId())->first();
		    if($data['row']->relasi){
		        //terpakasa dilakukan
		        if($data['row']->relasi=='pelatihan'){
    		        $data['relasi'] = DB::table($data['row']->relasi)->where('id',$data['row']->pelatihan_id)->first();
    		        $data['history'] =  DB::table($rel_table)
    		                                    ->join($data['row']->relasi,$data['row']->relasi.'.id',$rel_table.'.pelatihan_id')
    		                                    ->where('warga_bjb_id',$data['row']->warga_bjb_id)
    		                                    ->where('status_id',5)
    		                                    ->get();
		        }else{
		            $data['relasi'] = DB::table($data['row']->relasi)->where('id',$data['row']->relasi_id)->first();
    		        $data['history'] =  DB::table($rel_table)
    		                                    ->join($data['row']->relasi,$data['row']->relasi.'.id',$rel_table.'.relasi_id')
    		                                    ->where('warga_bjb_id',$data['row']->warga_bjb_id)
    		                                    ->where('status_id',5)
    		                                    ->get();
		        }
		    }
		  //  else{
		  //      $data['history'] =  DB::table($rel_table)->where('warga_bjb_id',$data['row']->warga_bjb_id)->where('status_id',5)->get();
		  //  }
		    $data['id'] = $id;
		    $data['rel_table'] = $rel_table;
		    $data['rel_id'] = $rel_id;
		    
		    
		    
		    
		    $data['pejabat'] = DB::table('pejabat')->where('dinas_id',Session::get("dinas_id"))->get();
		    
		    $data['jenis_surat'] = DB::table('surat')->where('id',$data['surat']->jenis_surat_id)->first();
		    $data['notasi_surat'] = DB::table('notasi_surat')->where('id_surat',$data['jenis_surat']->id)->where('id_cms_privileges',CRUDBooster::myPrivilegeId())->first();
		    
		    $data['layanan'] = DB::table('layanan')
						->where('jenis_surat_id',$data['surat']->jenis_surat_id)
						->where('village_id',CRUDBooster::me()->village_id)
						->first();
 		    if( $data['surat']->status_by_priv < CRUDBooster::myPrivilegeId()){
 		        //dd('masyuk');
    		    DB::table('surat_masuk')
        		  ->where('id',$id)
        		  ->update(['status_id'=>2,'status_by'=>CRUDBooster::myId(),'status_by_priv'=>CRUDBooster::myPrivilegeId()]);
 		    }
    		  
    		DB::table($rel_table)
    		  ->where('id', $rel_id)
    		  ->update(['status_id'=>2,'updated_by'=>CRUDBooster::myId()]);
    		  
    		  
    		$data['histpelatihan'] = DB::table('surat_reg_pelatihan')
                            ->join('pelatihan','pelatihan.id','surat_reg_pelatihan.pelatihan_id')
                            ->where('warga_bjb_id',$data['warga']->id)
                            ->where('status_id',5)
                            ->get();
                            
            $data['histbantuan'] = DB::table('surat_permohonan_bantuan')
                            ->join('bantuan','bantuan.id','surat_permohonan_bantuan.relasi_id')
                            ->where('warga_bjb_id',$data['warga']->id)
                            ->where('status_id',5)
                            ->get();
		    
		    $this->cbView( $data['notasi_surat']->view,$data);
		}
		
		public function postTeruskan($id){
		    $eksis = DB::table('surat_masuk')->where('no_sk_rt',Request::input('no_sk_rt'))->first();
		    if($eksis){
		        CRUDBooster::redirectBack('warning','Nomor Sudah Ada !');
		    }else{
		        DB::table('surat_masuk')->where('id',Request::input('id'))->update(['no_sk_rt'=>Request::input('no_sk_rt'),'tgl_sk_rt'=>Request::input('tgl_sk_rt'),'status_id'=>3,'status_by'=>CRUDBooster::myId(),'status_by_priv'=>CRUDBooster::myPrivilegeId()]);
		        return redirect(CRUDBooster::mainpath())->with('success', 'Permohonan berhasil di teruskan');
		    }
		}
		
		public function postVerifikasiUsaha(){
		    $usaha = DB::table(Request::input('rel_table'))->where('id',Request::input('rel_id'))->first();
		    DB::table(Request::input('rel_table'))->where('id',Request::input('rel_id'))->update(['verified'=>'Diverifikasi']);
		    
		    DB::table('surat_masuk')
    		  ->where('id',Request::input('id'))
    		  ->update(['status_id'=>5,'status_by'=>CRUDBooster::myId(),'status_by_priv'=>CRUDBooster::myPrivilegeId()]);
    		  
    		DB::table(Request::input('rel_table'))->where('id',Request::input('rel_id'))
    		    ->update(['status_id'=>5,'updated_by'=>CRUDBooster::myId()]);
    		  
    		$surat = DB::table('surat_masuk')->where('id',Request::input('id'))->first();
    		
    		CRUDBooster::notifPemohon($surat->created_by,'Permohonan usaha anda "'. $usaha->nama_usaha . '" disetujui',"openUrl;".Request::input('id').";umkm_usaha");
    		//CRUDBooster::createMasterNotif("8",'Pemohonan usaha ' . $usaha . " membuat permohonan Ijin Usaha","umkm_usaha","openUrl;".$last_id.";umkm_usaha");
		    
		    return redirect(CRUDBooster::mainpath())->with('success', 'Permohonan berhasil di setujui');
		}
		
		public function postVerifikasiSurat(){
		    $surat = DB::table(Request::input('rel_table'))->where('id',Request::input('rel_id'))->first();
		    DB::table(Request::input('rel_table'))->where('id',Request::input('rel_id'))->update(['status_id'=>5]);
		    
		    DB::table('surat_masuk')
    		  ->where('id',Request::input('id'))
    		  ->update(['status_id'=>5,'status_by'=>CRUDBooster::myId(),'status_by_priv'=>CRUDBooster::myPrivilegeId()]);
    		  
    		$surat = DB::table('surat_masuk')->where('id',Request::input('id'))->first();
    		
    		$jenis_surat = DB::table('surat')->where('tabel',Request::input('rel_table'))->first();
    		
    		CRUDBooster::notifPemohon($surat->created_by,'"'. $jenis_surat->nama_surat . '" anda disetujui',"openUrl;".Request::input('id').";".Request::input('rel_table'));
    	    CRUDBooster::createMasterNotif($jenis_surat->id,'"'. $jenis_surat->nama_surat . "anda disetujui","umkm_usaha","openUrl;".Request::input('id').";".Request::input('rel_table'));
		    
		    return redirect(CRUDBooster::mainpath())->with('success', 'Permohonan berhasil di setujui');
		}
		
		// public function postEditSave($id){
		 // //Create an Auth
		  // if(!CRUDBooster::isUpdate() && $this->global_privilege==FALSE || $this->button_edit==FALSE) {    
			// CRUDBooster::redirect(CRUDBooster::adminPath(),trans("crudbooster.denied_access"));
		  // }
		  
		  // $data['surat_masuk'] = DB::Table('surat_masuk')
					// ->join('surat','surat_masuk.jenis_surat_id','=','surat.id')
					// ->join('cms_users','cms_users.nik','=','surat_masuk.nik')
				   // ->where('surat_masuk.id',$id)
				   // ->first();
          
          // $data['verifikasi_surat']  = DB::Table('verifikasi_surat')
				   // ->where('surat_id',$id)
				   // ->where('privilege_id',CRUDBooster::myPrivilegeId())
				   // ->first();			  
		
			// if(Request::input('verifikasi')){
				// if($data['verifikasi_surat'] ){
					// //feed sudah diverifikasi
				// }else{
					// $lastid = DB::table('verifikasi_surat')->insertGetId([
						// 'user_id' => CRUDBooster::myId(),
						// 'privilege_id' => CRUDBooster::myPrivilegeId(),
						// 'jenis_surat_id' => $data['surat_masuk']->jenis_surat_id,
						// 'surat_id' => $id,
						// 'token' => " ",
						// 'aksi' => 5,
					// ]);	
					
					// DB::Table('surat_masuk')
					// ->where('id',$id)
					// ->update(['status_id' => 5,'status_by' => CRUDBooster::myId(),'status_by_priv' => CRUDBooster::myPrivilegeId()]);	
					
					// $config['content'] = CRUDBooster::myName() . "(" . CRUDBooster::myPrivilegeName() . ") memverifikasi " . $data['surat_masuk']->nama_surat . " milik " . $data['surat_masuk']->name ;
					// $config['to'] = CRUDBooster::adminPath('skck_warga/detail/' . $id);
					// //pilih seua user id yang ada di notasi
					// $config['id_cms_users'] = [1,$data[$tsurat]->created_by];
					// CRUDBooster::sendNotification($config);
				// }        

			// }else{
				// if($data['verifikasi_surat'] ){
					// DB::table('verifikasi_surat')->where('id', '=', $data['verifikasi_surat']->id)->delete();
					// DB::Table('surat_masuk')->where('id',$id)->update(['status_id' => 4]);
					
					// $config['content'] = CRUDBooster::myName() . "(" . CRUDBooster::myPrivilegeName() . ") membatalkan Verifikasi " . $data['surat_masuk']->nama_surat . " milik " . $data['surat_masuk']->name ;
					// $config['to'] = CRUDBooster::adminPath('skck_warga/detail/' . $id);
					// $config['id_cms_users'] = [1,$data[$tsurat]->created_by];
					// CRUDBooster::sendNotification($config);
				// }
			// }
			
			
			// return redirect(CRUDBooster::mainPath());
		// }
		
		public function getCek($id){
			$surat = DB::table('surat_masuk')
							->join('surat','surat_masuk.jenis_surat_id','=','surat.id')
							->join('districts','surat_masuk.district_id','=','districts.id')
							->join('villages','surat_masuk.village_id','=','villages.id')
							->leftJoin('cms_moduls','surat.model','=','cms_moduls.id')
							->where('surat_masuk.id',$id)
							->select('surat_masuk.*','surat_masuk.district_id as kec_id', 'surat_masuk.village_id as kel_id', 'districts.name as kecamatan', 'villages.name as kelurahan', 'surat.kode', 'surat.nama_surat', 'surat.model', 'surat.template_file','cms_moduls.path', 'surat_masuk.tgl_pernyataan')
							->first();
			var_dump($surat);
		}
		
		public function postCek($id){
		    $surat_masuk = DB::table('surat_masuk')
							->join('surat','surat_masuk.jenis_surat_id','=','surat.id')
							->join('districts','surat_masuk.district_id','=','districts.id')
							->join('villages','surat_masuk.village_id','=','villages.id')
							->leftJoin('cms_moduls','surat.model','=','cms_moduls.id')
							->leftJoin('layanan','surat.model','=','layanan.jenis_surat_id')
							->where('surat_masuk.id',$id)
							->select('surat_masuk.*','surat_masuk.district_id as kec_id', 'surat_masuk.village_id as kel_id', 'districts.name as kecamatan', 'villages.name as kelurahan', 'surat.kode', 'surat.nama_surat', 'layanan.kode_depan', 'surat.model', 'surat.template_file','cms_moduls.path', 'surat_masuk.tgl_pernyataan', 'layanan.jumlah_digit')
							->first();
							
		    if(Schema::hasColumn($surat_masuk->rel_table,'satuan_id')){
    		    $rel_data = DB::table($surat_masuk->rel_table)
    		                ->join('satuan','satuan.id',$surat_masuk->rel_table.'.satuan_id')
    		                ->where($surat_masuk->rel_table.'.id',$surat_masuk->rel_id)
    		                ->first();
		    }
		    $surat = DB::table('surat')->where('id',$surat_masuk->jenis_surat_id)->first();
		    
		    $pejabat = DB::table('pejabat')->where('id',Request::input('penandatangan'))->first();
		    
			var_dump($surat_masuk);
		}

		public function getCetakSurat($id)
		{
			//setlocale(LC_ALL, 'IND'); //windows
			setlocale(LC_ALL, 'id_ID.utf8'); //linux
			
			if(!CRUDBooster::isView()) CRUDBooster::denyAccess();
			
			$data = [];
			
			$unggahan = DB::Table('unggahan')
						->join('syarat','unggahan.syarat_id','syarat.id')
						->where('surat_masuk_id',$id)
						->where('syarat_id',1)
						->select('unggahan.*','syarat.deskripsi')
						->get();			
			
			
			// foreach($unggahan as $unggah){
				// DB::Table('unggahan')
					// ->where('id',$unggah->id)
					// ->update(['is_verified' => Request::input('verifikasi-'.$unggah->id)]);
					
				// $data[Str::slug($unggah->deskripsi,'_')] = $unggah->file;
			// }		
			
			
			
			$surat = DB::table('surat_masuk')
							->join('surat','surat_masuk.jenis_surat_id','=','surat.id')
							->join('districts','surat_masuk.district_id','=','districts.id')
							->join('villages','surat_masuk.village_id','=','villages.id')
							->leftJoin('cms_moduls','surat.model','=','cms_moduls.id')
							->leftJoin('layanan','surat.model','=','layanan.jenis_surat_id')
							->where('surat_masuk.id',$id)
							->select('surat_masuk.*','surat_masuk.district_id as kec_id', 'surat_masuk.village_id as kel_id', 'districts.name as kecamatan', 'villages.name as kelurahan', 'surat.kode', 'surat.nama_surat', 'layanan.kode_depan', 'surat.model', 'surat.template_file','cms_moduls.path', 'surat_masuk.tgl_pernyataan', 'layanan.jumlah_digit')
							->first();
							
			$layanan = DB::table('layanan')
						->where('jenis_surat_id',$surat->jenis_surat_id)
						->where('village_id',CRUDBooster::me()->village_id)
						->first();
						
							
			$kantor = DB::table('kantor')
							->where('district_id',$surat->kec_id)
							->where('village_id',$surat->kel_id)
							->first();
							
			
							
			if($surat->status_id == 2){	
				  DB::table('surat_masuk')
				  ->where('id',$id)
				  ->update(['status_id'=>3,'status_by'=>CRUDBooster::myId(),'status_by_priv'=>CRUDBooster::myPrivilegeId()]);
				  
				$next_no = Request::input('nomor_surat');
			
				$noID = str_pad($next_no, $layanan->jumlah_digit, '0', STR_PAD_LEFT);
				
				$nomor_surat = $layanan->kode_depan. '/' . $noID . '/' .$layanan->kode_belakang;
				  
				  DB::Table('surat_masuk')
					 ->where('id',$id)
					 ->update(['nomor' => $nomor_surat,'expired_at'=>date('Y-m-d', strtotime(Request::input('expired_at')))]);
					 
				 DB::Table('layanan')
				 ->where('jenis_surat_id',$surat->jenis_surat_id)
				 ->update(['no_terakhir' => $next_no]);

				$data['verifikasi_surat']  = DB::Table('verifikasi_surat')
					   ->where('surat_id',$id)
					   ->where('privilege_id',CRUDBooster::myPrivilegeId())
					   ->first();
					   
				if(Request::input('verifikasi')==1){
					if($data['verifikasi_surat'] ){
						//feed sudah diverifikasi
					}else{
						$lastid = DB::table('verifikasi_surat')->insertGetId([
							'user_id' => CRUDBooster::myId(),
							'privilege_id' => CRUDBooster::myPrivilegeId(),
							'jenis_surat_id' => $surat->jenis_surat_id,
							'surat_id' => $id,
							'token' => " ",
							'aksi' => 5,
						]);							
						
						CRUDBooster::notifPemohon($surat->created_by,CRUDBooster::myName() . " memproses permohonan " . $surat->nama_surat . " Anda.",CRUDBooster::adminPath($surat->path.'/detail/' . $id));
						CRUDBooster::notifUserTerlibat($surat->jenis_surat_id,CRUDBooster::myName() . " Memproses permohonan " . $surat->nama_surat, CRUDBooster::adminPath($surat->path.'/detail/' . $id));
					}        

				}else{
					if($data['verifikasi_surat'] ){
						DB::table('verifikasi_surat')->where('id', '=', $data['verifikasi_surat']->id)->delete();
						DB::Table('surat_masuk')->where('id',$id)->update(['status_id' => 4,'status_by' => CRUDBooster::myId(),'status_by_priv' => CRUDBooster::myPrivilegeId()]);
						
						CRUDBooster::notifPemohon($surat->created_by,"Permohonan " . $surat->nama_surat . " Anda ditolak.",CRUDBooster::adminPath($surat->path.'/detail/' . $id));
						
					}
				}

						//CRUDBooster::notifPemohon($surat->created_by,CRUDBooster::myName() . " memproses permohonan " . $surat->nama_surat . " Anda.",CRUDBooster::adminPath('skck_warga/detail/' . $id));
			}else{
				$nomor_surat = $surat->nomor;
			}
					
			$path = $surat->template_file;
			$fileName = storage_path('app/' . $path);
			$file = basename($path);         // $file is set to "index.php"
			$file = basename($path , ".docx");
			
			$templateProcessor = new WORD\TemplateProcessor($fileName);
			
			$templateProcessor->setValue('kecamatan', $surat->kecamatan);
			
			$templateProcessor->setValue('kelurahan', $surat->kelurahan);
			
			$templateProcessor->setValue('alamat_kantor', $kantor->alamat . ' Telp : ' . $kantor->no_telp);
			
			
			
			$templateProcessor->setValue('nomor_surat', $nomor_surat);
			
			$templateProcessor->setValue('nama', $surat->nama_lgkp);
			
			$templateProcessor->setValue('nik', $surat->nik);
			
			$templateProcessor->setValue('tempat_lahir', $surat->tmpt_lhr);
			
			$date = strftime( "%d %B %Y", strtotime($surat->tgl_lhr));
			
			$templateProcessor->setValue('tanggal_lahir', $date);
			
			$templateProcessor->setValue('alamat', $surat->alamat );
			
			if($surat->jk == "L"){
				$templateProcessor->setValue('jenis_kelamin', 'Laki-laki');
			}else{
				$templateProcessor->setValue('jenis_kelamin', 'Perempuan');
			}	
			
			//$templateProcessor->setValue('warga_negara', $surat->warga_negara);
			
			$templateProcessor->setValue('agama', $surat->agama);
			
			$templateProcessor->setValue('status_perkawinan', $surat->status_kawin);
			
			//$templateProcessor->setValue('golongan_darah', $surat->golongan_darah);
			
			$templateProcessor->setValue('pekerjaan', $surat->pekerjaan);
			
			$templateProcessor->setValue('rt', $surat->rt);
			
			$templateProcessor->setValue('rw', $surat->rw);
			
			// foreach($unggahan as $unggah){
				// DB::Table('unggahan')
					// ->where('id',$unggah->id)
					// ->update(['is_verified' => Request::input('verifikasi-'.$unggah->id)]);
				
				// if(Request::input('verifikasi-'.$unggah->id) == 1){
					// $templateProcessor->setValue(Str::slug($unggah->deskripsi,'_'), $unggah->file);
				// }
			// }
			
			$templateProcessor->setValue('no_sk_rt', $surat->no_sk_rt);
			
			$tgl_sk_date = strftime( "%d %B %Y", strtotime($surat->tgl_sk_rt));
			
			$templateProcessor->setValue('tgl_sk_rt', $tgl_sk_date);
			
			$templateProcessor->setValue('penghasilan', $surat->penghasilan);
			
			$templateProcessor->setValue('keperluan', $surat->keperluan);
			
			$created_date = strftime( "%d %B %Y", strtotime($surat->created_at));
			
			$templateProcessor->setValue('tanggal', $created_date);
			
			$expired_at = strftime( "%d %B %Y", strtotime($surat->expired_at));
			
			$templateProcessor->setValue('masa_berlaku', $expired_at);
			
			$date_pernyataan = strftime( "%d %B %Y", strtotime($surat->tgl_pernyataan));
			
			$templateProcessor->setValue('tgl_pernyataan', $date_pernyataan);
			
			$templateProcessor->setValue('nama_ttd', $kantor->nama_ttd);
			$templateProcessor->setValue('nip_ttd', $kantor->nik_ttd);
			
			$templateProcessor->saveAs(storage_path('surat_gen/'. $surat->path . $id . '.docx'));			
			
			//olah domain dinamis
			//$data['localdocfile'] = storage_path('surat_gen/skck' . $id . '.docx');
			
				//open or download local
				$file = storage_path('surat_gen/'. $surat->path .  $id . '.docx');
				//open or download server
				//$file = CRUDBooster::getSetting('domain') . '/surat/' . $surat->path . $id . '.docx';
			
				header('Content-Type: application/download');
				header('Content-Disposition: attachment; filename= ' .  $surat->path . $id . '.docx');
				header("Content-Length: " . filesize($file));

				$fp = fopen($file, "r");
				fpassthru($fp);
				fclose($fp);
				
				 
				
			//to the viewer
			//$data['docfile'] = CRUDBooster::getSetting('domain') . '/surat/' . $surat->path . $id . '.docx';
			//$this->cbView('viewdoc',$data);
			//
		}
		
		public function postCetakSuratDua($id,$reprint=0){
		    setlocale(LC_ALL, 'IND');
		    
		    
		    
		    $surat_masuk = DB::table('surat_masuk')
							->join('surat','surat_masuk.jenis_surat_id','=','surat.id')
							->join('districts','surat_masuk.district_id','=','districts.id')
							->join('villages','surat_masuk.village_id','=','villages.id')
							->leftJoin('cms_moduls','surat.model','=','cms_moduls.id')
							->leftJoin('layanan','surat.model','=','layanan.jenis_surat_id')
							->where('surat_masuk.id',$id)
							->select('surat_masuk.*','surat_masuk.district_id as kec_id', 'surat_masuk.village_id as kel_id', 'districts.name as kecamatan', 'villages.name as kelurahan', 'surat.kode', 'surat.nama_surat', 'layanan.kode_depan', 'surat.model', 'surat.template_file','cms_moduls.path', 'surat_masuk.tgl_pernyataan', 'layanan.jumlah_digit')
							->first();
			
							
		    if(Schema::hasColumn($surat_masuk->rel_table,'satuan_id')){
    		    $rel_data = DB::table($surat_masuk->rel_table)
    		                ->join('satuan','satuan.id',$surat_masuk->rel_table.'.satuan_id')
    		                ->where($surat_masuk->rel_table.'.id',$surat_masuk->rel_id)
    		                ->first();
		    }else{
		        $rel_data = DB::table($surat_masuk->rel_table)
    		                ->where($surat_masuk->rel_table.'.id',$surat_masuk->rel_id)
    		                ->first();
		    }
		    
		    //dd($rel_data);
		    
		    $surat = DB::table('surat')->where('id',$surat_masuk->jenis_surat_id)->first();
		    
		    $pejabat = DB::table('pejabat')
		            ->join('jabatan','pejabat.jabatan_id','jabatan.id')
		            ->where('pejabat.id',Request::input('penandatangan'))
		            ->first();
		    
		    $kantor = DB::table('kantor')
							->where('district_id',$surat_masuk->district_id)
							->where('village_id',$surat_masuk->village_id)
							->first();
			
			$layanan = DB::table('layanan')
						->where('jenis_surat_id',$surat_masuk->jenis_surat_id)
						->where('village_id',CRUDBooster::me()->village_id)
						->first();
						
			$nomor_surat = $layanan->kode_depan . Request::input('nomor_surat') . $layanan->kode_belakang;
		    
		    if($rel_data->nomor == $nomor_surat){
		        if($reprint==0){
		            CRUDBooster::redirectBack('Surat nomor ' . $layanan->kode_depan . Request::input('nomor_surat') . $layanan->kode_belakang . ' sudah ada !');
		        }
		    }
		    
		   
		    
		    $path = $surat->template_file;
			$fileName = storage_path('app/' . $path);
			$file = basename($path);
			$file = basename($path , ".docx");
			
			$templateProcessor = new WORD\TemplateProcessor($fileName);
			
			
			$templateProcessor->setValue('nomor_surat', $nomor_surat);
			
			$templateProcessor->setValue('jumlah', $rel_data->jumlah);
			
			$templateProcessor->setValue('satuan', $rel_data->satuan);
			
			$templateProcessor->setValue('jenis', $rel_data->jenis_produk);
			
			$templateProcessor->setValue('tanda_khusus', $surat_masuk->tanda_khusus);
			
			$templateProcessor->setValue('warna', $surat_masuk->warna);
			
			$templateProcessor->setValue('nama', $surat_masuk->nama_lgkp);
			
			$templateProcessor->setValue('nik', $surat_masuk->nik);
			
			$templateProcessor->setValue('tempat_lahir', $surat_masuk->tmpt_lhr);
			
			$date = strftime( "%d %B %Y", strtotime($surat_masuk->tgl_lhr));
			
			$templateProcessor->setValue('tanggal_lahir', $date);
			
			$templateProcessor->setValue('alamat', $surat_masuk->alamat );
			
			$templateProcessor->setValue('tujuan_pengiriman', $rel_data->tujuan_pengiriman );
			
			if($surat_masuk->jk == "L"){
				$templateProcessor->setValue('jenis_kelamin', 'Laki-laki');
			}else{
				$templateProcessor->setValue('jenis_kelamin', 'Perempuan');
			}	
			
		
			$templateProcessor->setValue('pekerjaan', $surat_masuk->pekerjaan);
			
			$templateProcessor->setValue('agama', $surat_masuk->agama);
			
			$templateProcessor->setValue('kecamatan', $surat_masuk->kecamatan);
			
			$templateProcessor->setValue('kelurahan', $surat_masuk->kelurahan);
			
			$templateProcessor->setValue('rt', $surat_masuk->rt);
			
			$templateProcessor->setValue('rw', $surat_masuk->rw);
			
			$templateProcessor->setValue('alamat_kantor', $kantor->alamat . ' Telp : ' . $kantor->no_telp);
			
			$templateProcessor->setValue('no_sk_rt', $surat_masuk->no_sk_rt);
			
			$tgl_sk_date = strftime( "%d %B %Y", strtotime($surat_masuk->tgl_sk_rt));
			
			$templateProcessor->setValue('tgl_sk_rt', $tgl_sk_date);
			
			$templateProcessor->setValue('keperluan', $rel_data->keperluan);
			
			$templateProcessor->setValue('jabatan', $pejabat->jabatan);
			$templateProcessor->setValue('jenis_usaha', $rel_data->jenis_usaha);
			$templateProcessor->setValue('nama_perusahaan', $rel_data->nama_perusahaan);
			$templateProcessor->setValue('pimpinan_perusahaan', $rel_data->pimpinan_perusahaan);
			$templateProcessor->setValue('alamat_perusahaan', $rel_data->alamat_perusahaan);
			$templateProcessor->setValue('akte_notaris', $rel_data->no_akte_notaris);
			$templateProcessor->setValue('siup', $rel_data->no_siup);
			$templateProcessor->setValue('izin_domisili', $rel_data->no_izin_domisili);
			$templateProcessor->setValue('ktp', $rel_data->no_ktp);
			$templateProcessor->setValue('npwp', $rel_data->no_npwp);
			
			
			// foreach($unggahan as $unggah){
				// DB::Table('unggahan')
					// ->where('id',$unggah->id)
					// ->update(['is_verified' => Request::input('verifikasi-'.$unggah->id)]);
				
				// if(Request::input('verifikasi-'.$unggah->id) == 1){
					// $templateProcessor->setValue(Str::slug($unggah->deskripsi,'_'), $unggah->file);
				// }
			// }
			
			
// 			$created_date = strftime( "%d %B %Y", strtotime($surat->created_at));
			
// 			$templateProcessor->setValue('tanggal', $created_date);
			
// 			$expired_at = strftime( "%d %B %Y", strtotime($surat->expired_at));
			
// 			$templateProcessor->setValue('masa_berlaku', $expired_at);
			
			$date_pernyataan = strftime( "%d %B %Y", strtotime(Request::input('tanggal')));
			
			$templateProcessor->setValue('tanggal', $date_pernyataan);
			
			$templateProcessor->setValue('nama_ttd', $pejabat->nama_pejabat);
			$templateProcessor->setValue('nip_ttd', $pejabat->nip_pejabat);
			
// 			$kunci = CRUDBooster::generateRandomString(17);
			
// 			$newhandap = DB::table('bahandap')->insertGetId(['kunci'=>$kunci,'redurl'=>CRUDBooster::adminPath($current->pdf_file),'updated_by'=>CRUDBooster::myId(),'updated_by_priv'=>CRUDBooster::myPrivilegeId(),'gen'=>1,'hit_limit'=>0]);
//             $qrpath = 'uploads/' . CRUDBooster::myId() . '/' . date("Y") . '-' . date("m") . '/' . $kunci . '.png';
//             $realqrpath = storage_path('app/'.$qrpath);
//             $qr = new QRCodeProv();
//             $qr->makeqr($text = 'https://bjb.city/' . $kunci ,$path = storage_path('app/'.$qrpath));
            
//             $templateProcessor->setImageValue(
//                 'qr',
//                 array('path' => $realqrpath, 'width' => 100, 'height' => 100, 'ratio' => false)
//             );
            
            $templateProcessor->setImageValue(
                'footer',
                array('path' => storage_path('app/'.CRUDBooster::getSetting('logo_bsre')), 'width' => 100, 'height' => 58, 'ratio' => false)
                );
                
            $templateProcessor->setValue(
                'footer_text',
                'Dokumen ini telah ditanda tangani secara elektronik menggunakan sertifikat elektronik yang diterbitkan oleh Balai Sertifikasi Elektronik (BSrE), BSSN.'
                );
			
			$nama_file = $surat_masuk->rel_table . $surat->kode . $id;
			$nama_file_surat = $nama_file . '.docx';
			
			$file = public_path('surat_gen/'. $nama_file_surat);
			$path = storage_path('surat_gen/'. $nama_file_surat);
			
			//dd($file);
			
			$templateProcessor->saveAs($file);
			
			
// 			$durl = CRUDBooster::convertPdf($file,$nama_file);
			
// 			sleep(3);
			
		//	$newgenpdf = CRUDBooster::downloadGenPdf($durl);
			
			
			DB::table('surat_masuk')->where('id',$id)->update(['gen_doc'=>'surat_gen/'. $nama_file_surat ,'gen_pdf'=>$newgenpdf ,'status_id'=>3,'status_by'=>CRUDBooster::myId(),'status_by_priv'=>CRUDBooster::myPrivilegeId()]);
			DB::table($surat_masuk->rel_table)->where('id',$surat_masuk->rel_id)->update(['nomor'=> $nomor_surat]);
			
			//$newDokumen = DB::table('dokumen')->insertGetId(['']);
			
			//olah domain dinamis
			//$data['localdocfile'] = storage_path('surat_gen/skck' . $id . '.docx');
			
			
	
			
			$headers = [
              'Content-Type' => 'application/docx',
           ];

           return response()->download($file, $nama_file_surat, $headers);
			
		  //  return CRUDBooster::redirect(CRUDBooster::adminPath('surat_masuk'),'Permohonan ' . $id .' sudah dikirim untuk di tandatangani.','success');
			
				
			//to the viewer
			//$data['docfile'] = CRUDBooster::getSetting('domain') . '/surat/' . $surat->path . $id . '.docx';
			//$this->cbView('viewdoc',$data);
			//
		}
		
		public function getlihatSuratDua($id){
		    $data = [];
		    $data['surat_masuk'] = DB::table('surat_masuk')->where('id',$id)->first();
		    $data['rel_data'] = DB::table($data['surat_masuk']->rel_table)
		                ->join('satuan','satuan.id',$data['surat_masuk']->rel_table.'.satuan_id')
		                ->where($data['surat_masuk']->rel_table.'.id',$data['surat_masuk']->rel_id)
		                ->first();
		    $data['surat'] = DB::table('surat')->where('id',$data['surat_masuk']->jenis_surat_id)->first();
		    
		    $data['pejabat'] = DB::table('pejabat')->where('id',Request::input('penandatangan'))->first();
		    
		
		    if(!is_null($data['surat']->template_html)){
                //$pdf = PDF::loadView('pdf.genpdf',$data);
                
                return view('pdf.genpdf2',$data);
	        
    	       // $path = public_path('gen_pdfs');
            //     $fileName =  'dok_' . CRUDBooster::myId() . '.' . 'pdf' ;
            //     $pdf->save($path . '/' . $fileName);
                
            //     DB::table($this->table)->where('id',$id)->update(['pdf_file'=>'gen_pdfs/' . $fileName,'updated_by'=>CRUDBooster::myId()]);
                
            }
		}
		
		public function getSetSelesai($id){
			if(!CRUDBooster::isView()) CRUDBooster::denyAccess();
			
			$surat = DB::table('surat_masuk')
							->join('surat','surat_masuk.jenis_surat_id','surat.id')
							->leftJoin('cms_moduls','surat.model','cms_moduls.id')
							->where('surat_masuk.id',$id)
							->first();
			
			if($surat->status_id >= 3){
			  DB::table('surat_masuk')
			  ->where('id',$id)
			  ->update(['status_id'=>5,'status_by'=>CRUDBooster::myId(),'status_by_priv'=>CRUDBooster::myPrivilegeId()]);
			 }
			 
			 CRUDBooster::notifPemohon($surat->created_by,CRUDBooster::myName() . " memproses permohonan " . $surat->nama_surat . " Anda.",CRUDBooster::adminPath($surat->path.'/detail/' . $id));
			 CRUDBooster::notifUserTerlibat($surat->jenis_surat_id,CRUDBooster::myName() . " Telah meyelesaikan " . $surat->nama_surat, CRUDBooster::adminPath('surat_masuk/' . $id));
			 
			 return CRUDBooster::redirect(CRUDBooster::adminPath('surat_masuk'),'Permohonan ' . $id .' sudah diselesaikan.','info');
		}
		


	    /*
	    | ---------------------------------------------------------------------- 
	    | 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) {
			if(CRUDBooster::myPrivilegeId()==3){
			    // Ketua RT
			   //$query->join('verifikasi_surat','verifikasi_surat.surat_id','=','surat_masuk.id')
				$query
				    ->where('surat_masuk.district_id',CRUDBooster::menik(CRUDBooster::me()->nik)->district_id)
					 ->where('surat_masuk.village_id',CRUDBooster::menik(CRUDBooster::me()->nik)->village_id)
					 ->whereRaw('CAST(surat_masuk.rw AS UNSIGNED)='.CRUDBooster::me()->rw)
	                 ->whereRaw('CAST(surat_masuk.rt AS UNSIGNED)='.CRUDBooster::me()->rt)
					 ->whereIn('jenis_surat_id',$this->supported);
				// 	 ->where('surat_masuk.status_by_priv','<=',CRUDBooster::myPrivilegeId());
					 //->whereRaw('(SELECT max(aksi) FROM verifikasi_surat WHERE surat_id = surat_masuk.id ) AND verifikasi_surat.aksi = 5 AND verifikasi_surat.user_id = '. CRUDBooster::myId())
					 //->orderBy('verifikasi_surat.id', 'desc');
					// 			   
			}else if(CRUDBooster::myPrivilegeId()==4){
				//RW
			}else if(CRUDBooster::myPrivilegeId()==5){
				//staf kelurahan
				// var_dump(CRUDBooster::me()->village_id);
				$query->where('surat_masuk.district_id',CRUDBooster::me()->district_id)
					 ->where('surat_masuk.village_id',CRUDBooster::me()->village_id)
					 //->where('surat_masuk.owner_priv',CRUDBooster::myPrivilegeId())
					 //->where('surat_masuk.status_by_priv','<=',CRUDBooster::myPrivilegeId())
					 ->whereIn('jenis_surat_id',$this->supported);
			}else if(CRUDBooster::myPrivilegeId()==6){
				//staf kelurahan
				// var_dump(CRUDBooster::me()->village_id);
				$query->where('surat_masuk.district_id',CRUDBooster::me()->district_id)
					 ->where('surat_masuk.village_id',CRUDBooster::me()->village_id)
					 //->where('surat_masuk.owner_priv',CRUDBooster::myPrivilegeId())
					 //->where('surat_masuk.status_by_priv','<=',CRUDBooster::myPrivilegeId())
					 ->whereIn('jenis_surat_id',$this->supported);
			}else if(CRUDBooster::myPrivilegeId()==7){
				//staf kecamatan
				 $query->where('surat_masuk.district_id',CRUDBooster::me()->district_id)
				    ->where('surat_masuk.owner_priv',CRUDBooster::myPrivilegeId())
					//->where('surat_masuk.status_by_priv','<=',CRUDBooster::myPrivilegeId())
					->whereIn('jenis_surat_id',$this->supported);
			}else if(CRUDBooster::myPrivilegeId()==8){
				 $query->where('surat_masuk.district_id',CRUDBooster::me()->district_id)
				->where('surat_masuk.status_by_priv','<=',CRUDBooster::myPrivilegeId());
			}else if(CRUDBooster::myPrivilegeId()==2){
				 $query->where('surat_masuk.created_by',CRUDBooster::myId());
				    //->where('surat_masuk.status_by_priv','<=',CRUDBooster::myPrivilegeId());
			}else {
			 //   $query->where('surat_masuk.owner_priv',CRUDBooster::myPrivilegeId())
			            $query->whereIn('jenis_surat_id',$this->supported);
			    //dd($this->supported);
			}
	    }

	    /*
	    | ---------------------------------------------------------------------- 
	    | 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 putFile($param) {
            $post_data = static::buildData($param);
            var_dump($post_data);
            $curl = curl_init(static::$url);
            curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
            curl_setopt($curl, CURLOPT_POST, true);
            curl_setopt($curl, CURLOPT_POSTFIELDS, $post_data);
            curl_setopt($curl, CURLOPT_HTTPHEADER, [
                "Content-Type: multipart/form-data; boundary=" . static::$delimiter,
                "Content-Length: " . strlen($post_data)
            ]);
            $response = curl_exec($curl);
            curl_close($curl);
            $info = json_decode($response, true);
            return $response;
        }
     
        private static function buildData($param){
            $data = '';
            $eol = "\r\n";
            $upload = $param['file'];
            unset($param['file']);
     
            foreach ($param as $name => $content) {
                $data .= "--" . static::$delimiter . "\r\n"
                    . 'Content-Disposition: form-data; name="' . $name . "\"\r\n\r\n"
                    . $content . "\r\n";
            }
                     // splicing file stream
            $data .= "--" . static::$delimiter . $eol
                . 'Content-Disposition: form-data; name="file"; filename="' . $param['filename'] . '"' . "\r\n"
                . 'Content-Type:application/octet-stream'."\r\n\r\n";
     
            $data .= $upload . "\r\n";
            $data .= "--" . static::$delimiter . "--\r\n";
            return $data;
        }



	}
	
	class CURLFile
    {
        /**
         * Create CurlFile object
         * @param string $name File name
         * @param string $mimetype Mime type, optional
         * @param string $postfilename Post filename, defaults to actual filename
         */
        public function __construct($name, $mimetype = '', $postfilename = '')
        {}
     
        /**
         * Set mime type
         * @param string $mimetype
         * @return CurlFile
         */
        public function setMimeType($mimetype)
        {}
     
        /**
         * Set mime type
         * @param string $mimetype
         * @return string
         */
        public function getMimeType($mimetype)
        {}
     
        /**
         * Get file name from which the data will be read
         * @return string
         */
        public function getFilename()
        {}
     
        /**
         * Get file name which will be sent in the post
         * @param string $name File name
         * @return string
         */
        public function setPostFilename($name)
        {}
     
        /**
         * Set file name which will be sent in the post
         * @return string
         * @return CurlFile
         */
        public function getPostFilename()
        {}
    }