<?php
class M_acara extends CI_Model{
	
	
	function upload_materi($userfile)
	{
		$simpan_data=array(
            
			'id_acara'  			=>$this->input->post('id_acara'),
			'materi'  			=>$this->input->post('materi'),
			'narasumber'  			=>$this->input->post('narasumber'),
            		'jpl'   			=>$this->input->post('jpl'),
			'link_file'   			=>$userfile
       			);
		$this->db->insert('tbl_program_acara', $simpan_data);
		
		
	}
	
	function delete_program($id_program,$id_acara)
	{
		$this->db->where('id_program', $id_program);
		$this->db->where('id_acara', $id_acara);
		$this->db->delete('tbl_program_acara');
		
	}
	
	function cek_verifikasi($id_acara)  
    { 
		$email=$this->input->post('email');
		$no_hp=$this->input->post('no_hp');
		$this->db->select('*');
		$this->db->where('tbl_registrasi.email', $email);
		$this->db->where('tbl_registrasi.no_hp', $no_hp);
		$this->db->where('tbl_registrasi.id_acara', $id_acara);
		$query = $this->db->get('tbl_registrasi');
        return $query->result();
    }
	
	function ubah_verifikasi($id_acara)  
    {   
		$email=$this->input->post('email');
		
		$this->db->select('*');
		$this->db->where('email', $email);
		$this->db->where('id_acara', $id_acara);
		$query = $this->db->get('tbl_registrasi');
        return $query->row();
    }	
	
	function simpan_edit_peserta($id_tamu,$id_acara)
	{
		//$id_acara=$this->input->post('id_acara');
		//$id_tamu=$this->input->post('id_tamu');
		
		$nip=$this->input->post('nip');
		$nip_nospasi = str_replace(' ', '', $nip); //menghilangkan spasi
		
		{
			$data = array(
            'nama'   						=>$this->input->post('nama'),
			'nip'   						=>$this->input->post('nip'),
			'unit_kerja'   					=>$this->input->post('unit_kerja'),
			'jabatan'   					=>$this->input->post('jabatan')
			);
		}
		$this->db->where('id_tamu', $id_tamu);
		$this->db->where('id_acara', $id_acara);
		//$this->db->where('sts', 0);
        $this->db->update('tbl_registrasi', $data);
		
		//Ubah status menjadi 1 hadir
		$result = $this->db->query("SELECT sts FROM tbl_registrasi WHERE id_tamu='$id_tamu' AND id_acara='$id_acara'");
		foreach ($result->result_array() as $id)
		$sts=$id['sts'];
		//Update menjadi AKtif di tabel member
		if ($sts==0){
		$simpan_data=array(
		'sts'			=>1
		);
		$this->db->where('id_tamu', $id_tamu);
		$this->db->where('id_acara', $id_acara);
        $this->db->update('tbl_registrasi', $simpan_data);
		}
	}
	
	
	function cek_email($id_acara)  
    { 
		$email=$this->input->post('email');
		$this->db->select('*');
		$this->db->where('tbl_registrasi.email', $email);
		$this->db->where('tbl_registrasi.id_acara', $id_acara);
		$query = $this->db->get('tbl_registrasi');
        return $query->result();
    }
	
	function cek_email_cadangan($id_acara)  
    { 
		$email=$this->input->post('email');
		$this->db->select('*');
		$this->db->where('tbl_cadangan.email', $email);
		$this->db->where('tbl_cadangan.id_acara', $id_acara);
		$query = $this->db->get('tbl_cadangan');
        return $query->result();
    }
	
//Tampil Data
	function download($id_acara)  
    { 
		$this->db->select('*');
		$this->db->where('id_acara',$id_acara);
		$query = $this->db->get('tbl_registrasi');
        return $query->result();
    }
	
	//Tampil Data
	function cadangan($id_acara)  
    { 
		$this->db->select('*');
		$this->db->where('id_acara',$id_acara);
		$query = $this->db->get('tbl_cadangan');
        return $query->result();
    }
	
	//Tampil Data
	function verifikasi($id_acara)  
    { 
		$this->db->select('*');
		$this->db->where('id_acara',$id_acara);
		$this->db->where('tbl_registrasi.sts',1);
		$query = $this->db->get('tbl_registrasi');
		
		return $query->result();
    }
	
	//Tampil Data
	function tampil($limit, $uri)  
    { 
		
        	
        	$sess_unit=$this->session->userdata('id_unit');
		
		$sess_level=$this->session->userdata('level');
		
		if ($sess_level==2){
		$this->db->select('*');
		//$this->db->join('tbl_peg','tbl_peg.nip=tbl_file.pns','left');
		//$this->db->join('tbl_unit','tbl_unit.id_unit=tbl_file.unit','left');
		$this->db->order_by('tgl_acara desc');
		$query = $this->db->get('tbl_acara', $limit, $uri);
        	return $query->result();	
		}else{
		$this->db->select('*');
		//$this->db->join('tbl_peg','tbl_peg.nip=tbl_file.pns','left');
		//$this->db->join('tbl_unit','tbl_unit.id_unit=tbl_file.unit','left');
		$this->db->order_by('id_acara desc');
		$this->db->where('id_unit', $sess_unit);
		$query = $this->db->get('tbl_acara', $limit, $uri);
        	return $query->result();
		}
    }
	
	//Ambil data utk ditampilkan di form edit
	function select($id_acara)  
    {   
		$this->db->select('*');
		$this->db->where('id_acara', $id_acara);
		$query = $this->db->get('tbl_acara');
        return $query->row();
    }
	
	function simpan_acara()   
    {
		
		$sess_unit=$this->session->userdata('id_unit');
		
		$user_id=$this->input->post('tgl_acara');
		$pengacak = 'ABCDEFGHIJKLMNOPQRSTU1234567890';//Pengacak User ID
		$user_id = "";
		$panjang = 8; 
		for($i=0;$i<$panjang;$i++) {
			$user_id .= $pengacak[rand(0, 30)];
		}
		
		$simpan_data=array(
            'jenis_dokumen'  				=>$this->input->post('jenis_dokumen'),
			'nama_acara'  					=>$this->input->post('nama_acara'),
			'no_sertifikat'  				=>$this->input->post('no_sertifikat'),
			'tgl_sertifikat'  				=>$this->input->post('tgl_sertifikat'),
			'metode'  						=>$this->input->post('metode'),
			'tgl_acara'   					=>$this->input->post('tgl_acara'),
			'tgl_acara_akhir'   			=>$this->input->post('tgl_acara_akhir'),
			'waktu_awal'   					=>$this->input->post('jam_awal'),
			'waktu_akhir'   				=>$this->input->post('jam_akhir'),
			'keterangan'   					=>$this->input->post('keterangan'),
			'ket_statis_awal'   			=>$this->input->post('ket_statis_awal'),
			'nama_ttd'   					=>$this->input->post('nama_ttd'),
			'nip_ttd'   					=>$this->input->post('nip_ttd'),
			'link_acara'   					=>$this->input->post('link_acara'),
			'jumlah_peserta'   				=>$this->input->post('jumlah_peserta'),
			'id_unit'   					=>$sess_unit,
			'kode_acara'   					=>$user_id
       );
		$this->db->insert('tbl_acara', $simpan_data);
		
	}
	
	function simpan_edit($id_acara)   
    {
		{
			$data = array(
            'jenis_dokumen'  				=>$this->input->post('jenis_dokumen'),
            'nama_acara'  					=>$this->input->post('nama_acara'),
			'no_sertifikat'  				=>$this->input->post('no_sertifikat'),
			'tgl_sertifikat'  				=>$this->input->post('tgl_sertifikat'),
			'metode'  						=>$this->input->post('metode'),
            'tgl_acara'   					=>$this->input->post('tgl_acara'),
			'tgl_acara_akhir'   			=>$this->input->post('tgl_acara_akhir'),
			'waktu_awal'   					=>$this->input->post('jam_awal'),
			'waktu_akhir'   				=>$this->input->post('jam_akhir'),
			'keterangan'   					=>$this->input->post('keterangan'),
			'ket_statis_awal'   			=>$this->input->post('ket_statis_awal'),
			'nama_ttd'   					=>$this->input->post('nama_ttd'),
			'nip_ttd'   					=>$this->input->post('nip_ttd'),
			'link_acara'   					=>$this->input->post('link_acara'),
			'jumlah_peserta'   				=>$this->input->post('jumlah_peserta')
			);
		}
		$this->db->where('id_acara', $id_acara);
		$this->db->update('tbl_acara', $data);
	
	}
	
	
	
	function delete($id_acara)
	{
		//Hapus Daftar Sertifikat
		$this->db->where('id_acara', $id_acara);
		$this->db->delete('tbl_acara');
		//Hapus Daftar Penerima Sertifikat
		$this->db->where('id_acara', $id_acara);
		$this->db->delete('tbl_registrasi');
	}
	
	function kosongkan_peserta($id_acara)
	{
		$this->db->where('id_acara', $id_acara);
		$this->db->delete('tbl_registrasi');
		$this->db->query('ALTER TABLE tbl_registrasi AUTO_INCREMENT 0');
	}
	
	
	
	
	
	function kirimEMAIL($subject,$nm_tujuan,$isi,$email_tujuan) 
	{
			require_once(APPPATH.'libraries/PHPMailerAutoload.php');//letak library di CI
			$mail = new PHPMailer();
			$mail->IsSMTP(); // we are going to use SMTP
			$mail->SMTPAuth   = true; // enabled SMTP authentication
			$mail->SMTPSecure = "ssl";  // prefix for secure protocol to connect to the server
			$mail->Host       = "ssl://iel-education.org";      // setting GMail as our SMTP server
			$mail->Port       = 465;                   // SMTP port to connect to GMail
			$mail->Username   = "event@iel-education.org";  // user email address 
			$mail->Password   = "TBJ1)GBI16)u";            // password in GMail
			$mail->SetFrom('event@iel-education.org', ''.$subject.'');  //Who is sending the email
			$mail->AddReplyTo("event@iel-education.org","NO REPLAY !!");  //email address that receives the response
			$mail->Subject    = $subject;
			$mail->Body      = "".$nm_tujuan."
			".$isi." ";
			$mail->AltBody    = "";
			$destino = "".$email_tujuan.""; //diisi dengan alamat tujuan diambil dari parameter
			$mail->AddAddress($destino, "");
			
			//$path=$_SERVER["DOCUMENT_ROOT"];
			//$file=$path."/pengaduan/file/".$link_file."";//alamat letak file
			
			//$mail->AddAttachment($file);      // some attached files
			//$mail->AddAttachment(""); // as many as you want
			$mail->Send(); 
	}
	
	function kirimWA($nomor,$pesan)//3355
		{
			$curl = curl_init();
			$token = "";
			$url="";
			curl_setopt($curl, CURLOPT_HTTPHEADER,
				array(
					"Authorization: $token",
				)
			);

			$data = [
				'phone' => $nomor,
				'message' => $pesan,
			];

			curl_setopt($curl, CURLOPT_CUSTOMREQUEST, "POST");
			curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
			curl_setopt($curl, CURLOPT_POSTFIELDS, http_build_query($data));
			curl_setopt($curl, CURLOPT_URL,$url);
			curl_setopt($curl, CURLOPT_SSL_VERIFYHOST, 0);
			curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, 0);
			$result = curl_exec($curl);
			curl_close($curl);	
			
		}
	
	function tot($id_acara)
	{
		$this->db->from('tbl_registrasi');
		$this->db->where('id_acara',$id_acara);
		//$this->db->where('tahun',$tahun);
		$data=$this->db->count_all_results();
		return $data;
	}
	
	function tot_cadangan($id_acara)
	{
		$this->db->from('tbl_cadangan');
		$this->db->where('id_acara',$id_acara);
		//$this->db->where('tahun',$tahun);
		$data=$this->db->count_all_results();
		return $data;
	}
	
	function tot_verifikasi($id_acara)
	{
		$this->db->from('tbl_registrasi');
		$this->db->where('id_acara',$id_acara);
		$this->db->where('sts',1);
		$data=$this->db->count_all_results();
		return $data;
	}
	
	//=================
	
		public function insert($data)
    {
        $this->db->insert_batch('tbl_registrasi', $data);
        return $this->db->insert_id();
    }
    
    function generate_all($id_acara)
	{
		//AMBIL DATA ACARA
		$result = $this->db->query("SELECT * FROM tbl_acara where tbl_acara.id_acara=$id_acara");
		foreach ($result->result_array() as $x)
		$no_sertifikat=$x['no_sertifikat'];

		$no_sertifikat1=str_replace(" ","",$no_sertifikat);

		
		$action = $this->input->post('action');
		$id_acara = $this->input->post('id_acara');
		if ($action == "aksi") {
			$input = $this->input->post('msg');
			for ($i=0; $i < count($input) ; $i++) 
			{ 
			$data=array(
				'unik'   			=>MD5($input[$i]),
				'no_register'   	=>$no_sertifikat1.'_'.$input[$i]
				);
			$this->db->where('id_tamu', $input[$i]);
			$this->db->where('id_acara', $id_acara);
			$this->db->update('tbl_registrasi', $data);
			}
		}
		
	}
	
	function kirim_wa_all($id_acara)
	{
		$action = $this->input->post('action');
		$id_acara = $this->input->post('id_acara');
		//AMBIL DATA ACARA
		$result = $this->db->query("SELECT * FROM tbl_acara where tbl_acara.id_acara=$id_acara");
		foreach ($result->result_array() as $x)
		$nama_acara=$x['nama_acara'];

		if ($action == "aksi") {
			$input = $this->input->post('msg');
			for ($i=0; $i < count($input) ; $i++) 
			{ 
				$result = $this->db->query("SELECT * FROM tbl_registrasi where tbl_registrasi.id_tamu=$input[$i] AND tbl_registrasi.id_acara=$id_acara");
				foreach ($result->result_array() as $id)
				$nama=$id['nama'];
				$id_tamu=$id['id_tamu'];
				$no_hp=$id['no_hp'];
				$email=$id['email'];
				$unik=$id['unik'];
				$link_sertifikat=''.base_url().'verify/v/'.$unik.'/'.$id_acara.'';
				
				$judul='*#Pengiriman LoA Artikel '.$nama_acara.'*';
				$isi_pesan='Berikut Letter of Acceptance (LoA), dapat diunduh melalui tautan berikut '.$link_sertifikat.'';
				$spasi='
';
				
				$nomor=$no_hp;
				$pesan=''.$judul.''.$spasi.'Yth Bpk/Ibu *'.$nama.'*'.$spasi.''.$spasi.''.$isi_pesan.''.$spasi.' *Whatsapp Center IEL Education*'; 
				$this->kirimWA($nomor,$pesan);
			}
		}
		
	}


	function kirim_email_all($id_acara)
	{
		
		$action = $this->input->post('action');
		$id_acara = $this->input->post('id_acara');
		//AMBIL DATA ACARA
		$result = $this->db->query("SELECT * FROM tbl_acara where tbl_acara.id_acara=$id_acara");
		foreach ($result->result_array() as $x)
		$nama_acara=$x['nama_acara'];

		if ($action == "aksi") {
			$input = $this->input->post('msg');
			for ($i=0; $i < count($input) ; $i++) 
			{ 
				$result = $this->db->query("SELECT * FROM tbl_registrasi where tbl_registrasi.id_tamu=$input[$i] AND tbl_registrasi.id_acara=$id_acara");
				foreach ($result->result_array() as $id)
				$nama=$id['nama'];
				$id_tamu=$id['id_tamu'];
				$no_hp=$id['no_hp'];
				$email=$id['email'];
				$unik=$id['unik'];
				$link_sertifikat=''.base_url().'verify/v/'.$unik.'/'.$id_acara.'';
				
				$judul='*#Pengiriman LoA Artikel '.$nama_acara.'*';
				$isi_pesan='Berikut Letter of Acceptance (LoA), dapat diunduh melalui tautan berikut  '.$link_sertifikat.'';
				$spasi='
';
				//KIRIM EMAIL
				$subject= $judul;
				$nm_tujuan= 'Yth. '.$nama.'';
				$isi=$isi_pesan;
				$email_tujuan=$email;
				//$link_file='tes.txt';
				
				$this->kirimEMAIL($subject,$nm_tujuan,$isi,$email_tujuan);//Ke Model kirim Email
			}
		}
		
	}
	
}