<?php
include_once "db/db.php";
class model extends koneksi{
	public function selectprepare($table, $field = null, $params=null, $where=null, $other=null, $other1=null){
		$sql = "SELECT";
		$kolom = null;
		if($field != null){
			foreach($field as $key){
				$kolom .= ", ".$key."";
			}
			$sql .= substr($kolom, 1)."";
		}else{
			$sql .= " * ";
		}
		$sql .= " FROM $table";
		
		if($params != null){
			$sql .= " WHERE $where";
			$sql .= " $other";
			$query = $this->db->prepare($sql);
			foreach ($params as $kolom => &$isian){
				$query->bindParam($kolom, $isian);  //echo "<p>$kolom, $isian</p>";
			}
			$query->execute($params);
		}else{
			$sql .= " $other";
			$sql .= " $other1";
			$query = $this->db->prepare($sql);
			$query->execute();
		}
		//echo "<br/><br/>".$sql;
		return $query;
	}

	public function insertprepare($table, $field = null, $params=null){
		$sql = "INSERT INTO $table ";
		$kolom = null;
		$value = null;
		foreach($field as $key => $nilai){
			$kolom .= ", ".$key."";
			$value .= ", '".$nilai."'";
		}
		$sql .= "(".substr($kolom, 1).")";
		$sql .= " VALUES (".substr($value, 1).")";

		$query = $this->db->prepare($sql);
		foreach ($params as $kolom => &$isian){
			$query->bindParam($kolom, $isian);  //echo "<p>$kolom, $isian</p>";
		}
		$query->execute($params);
		//echo "<br/><br/>".$sql;
		return $query;
	}
	public function insertidprepare($table, $field = null, $params=null){
		$sql = "INSERT INTO $table ";
		$kolom = null;
		$value = null;
		foreach($field as $key => $nilai){
			$kolom .= ", ".$key."";
			$value .= ", '".$nilai."'";
		}
		$sql .= "(".substr($kolom, 1).")";
		$sql .= " VALUES (".substr($value, 1).")";

		$query = $this->db->prepare($sql);
		foreach ($params as $kolom => &$isian){
			$query->bindParam($kolom, $isian);  //echo "<p>$kolom, $isian</p>";
		}
		$query->execute($params);
		if($query->rowCount() >= 1){
			$last_id = $this->db->lastInsertId();
		} else {
			$last_id = 0;
		}
		return $last_id;
	}
	public function hapusprepare($table, $params=null, $where=null){
		$sql = "DELETE FROM $table";
		if($params != null){
			$sql .= " WHERE $where";
			$query = $this->db->prepare($sql);
			foreach ($params as $kolom => &$isian){
				$query->bindParam($kolom, $isian);  //echo "<p>$params, $isian</p>";
			}
			$query->execute($params);
		}else{
			$query = $this->db->prepare($sql);
			$query->execute();
		}
		//echo "<br/><br/>".$sql;
		return $query;
	}
	public function updateprepare($table, $field = null, $params=null, $where=null){
		$sql = "UPDATE $table set ";
			$dump = null;
			foreach($field as $key => $nilai){
				$dump .= ", ".$key." = '".$nilai."'";
			}
			$sql .= substr($dump, 1);
			
		if($params != null){
			$sql .= " WHERE $where";
			$query = $this->db->prepare($sql);
			foreach ($params as $kolom => &$isian){
				$query->bindParam($kolom, $isian);  //echo "<p>$kolom, $isian</p>";
			}
			$query->execute($params);
		}else{
			$query = $this->db->prepare($sql);
			$query->execute();
		}
		//echo "<br/><br/>".$sql;
		return $query;
	}
	public function selectcount($table, $params=null, $where=null, $other=null){
		$sql = "select count(*) as jlh from $table";
		if($params != null){
			$sql .= " WHERE $where";
			$sql .= " $other";
			$query = $this->db->prepare($sql);
			foreach ($params as $kolom => &$isian){
				$query->bindParam($kolom, $isian);  //echo "<p>$params, $isian</p>";
			}
			$query->execute($params);
		}else{
			$sql .= " $other";
			$query = $this->db->prepare($sql);
			$query->execute();
		}
		//echo "<br/><br/>".$sql;
		return $query;
	}
	public function truncate($table){
		$sql = "TRUNCATE TABLE $table";
		$query = $this->db->query($sql);
		$query->execute();
		return $query;
	}

	function noref($id_sk = "", $internal = ""){
		if($internal == "internal"){
			$table = "surat_keluar_internal";
		} else {
			$table = "surat_keluar";
		}
		$params = array(':id_sk' => $id_sk);
		$field	= array('a.no_agenda', 'DATE_FORMAT(a.tgl_agenda, "%Y") AS tahun', 'CONCAT(b.kode_masalah, ".", b.primer, ".", b.sekunder) AS kode_masalah', 'CONCAT(c.kode1, ".", c.kode2) AS unit_kerja');
		$getdata= $this->selectprepare("".$table." a left join kode_masalah b ON b.id = a.kodemasalahid left join unit_kerja c ON c.id = a.unitkerjaid", $field, $params, 'a.id_sk=:id_sk', $order=null);
		$datashow = $getdata->fetch(PDO::FETCH_OBJ);
		$noagenda = sprintf("%05d", $datashow->no_agenda);
		return $datashow->kode_masalah."/".$datashow->unit_kerja.".".$noagenda."/".$datashow->tahun;
	}
	
	function noref_sk($id_sk = ""){
		$table = "surat_keluar";
		$params = array(':id_sk' => $id_sk);
		$field	= array('a.no_agenda', 'DATE_FORMAT(a.tgl_agenda, "%Y") AS tahun', 'CONCAT(b.kode_masalah, ".", b.primer, ".", b.sekunder) AS kode_masalah', 'CONCAT(c.kode1, ".", c.kode2) AS unit_kerja');
		$getdata= $this->selectprepare("".$table." a left join kode_masalah b ON b.id = a.kodemasalahid left join unit_kerja c ON c.id = a.unitkerjaid", $field, $params, 'a.id_sk=:id_sk', $order=null);
		$datashow = $getdata->fetch(PDO::FETCH_OBJ);
		$noagenda = sprintf("%05d", $datashow->no_agenda);
		return $datashow->kode_masalah."/".$datashow->unit_kerja.".".$noagenda."/".$datashow->tahun;
	}

	function noref_ski($id_sk = ""){
		$table = "surat_keluar_internal";
		$params = array(':id_sk' => $id_sk);
		$field	= array('a.no_agenda', 'DATE_FORMAT(a.tgl_agenda, "%Y") AS tahun', 'CONCAT(b.kode_masalah, ".", b.primer, ".", b.sekunder) AS kode_masalah', 'CONCAT(c.kode1, ".", c.kode2) AS unit_kerja');
		$getdata= $this->selectprepare("".$table." a left join kode_masalah b ON b.id = a.kodemasalahid left join unit_kerja c ON c.id = a.unitkerjaid", $field, $params, 'a.id_sk=:id_sk', $order=null);
		$datashow = $getdata->fetch(PDO::FETCH_OBJ);
		$noagenda = sprintf("%05d", $datashow->no_agenda);
		return $datashow->kode_masalah."/".$datashow->unit_kerja.".".$noagenda."/".$datashow->tahun;
	}

	function noref_sm($id_sm = "", $internal = ""){
		if($internal == "internal"){
			$table = "surat_masuk_internal";
		} else {
			$table = "surat_masuk";
		}
		$params = array(':id_sm' => $id_sm);
		$field	= array('a.no_agenda', 'DATE_FORMAT(a.tgl_agenda, "%Y") AS tahun', 'CONCAT(b.kode_masalah, ".", b.primer, ".", b.sekunder) AS kode_masalah', 'CONCAT(c.kode1, ".", c.kode2) AS unit_kerja');
		$getdata= $this->selectprepare("".$table." a left join kode_masalah b ON b.id = a.kodemasalahid left join unit_kerja c ON c.id = a.unitkerjaid", $field, $params, 'a.id_sm=:id_sm', $order=null);
		$datashow = $getdata->fetch(PDO::FETCH_OBJ);
		$noagenda = sprintf("%05d", $datashow->no_agenda);
		return $datashow->kode_masalah."/".$datashow->unit_kerja.".".$noagenda."/".$datashow->tahun;
	}

	function noref_doc($id = ""){
		$params = array(':id_sm' => $id);
		$field	= array('a.no_agenda', 'DATE_FORMAT(a.tgl_agenda, "%Y") AS tahun', 'DATE_FORMAT(a.tgl_agenda, "%m") AS bulan', 'CONCAT(c.kode1, ".", c.kode2) AS unit_kerja');
		$getdata= $this->selectprepare("surat_dokumen a left join unit_kerja c ON c.id = a.unitkerjaid", $field, $params, 'a.id_sm=:id_sm', $order=null);
		$datashow = $getdata->fetch(PDO::FETCH_OBJ);
		$noagenda = sprintf("%05d", $datashow->no_agenda);
		return $noagenda."/".$datashow->unit_kerja."/".$datashow->bulan."/".$datashow->tahun;
	}

	function hari_libur($input = ""){
		//## Cek Data di Pengaturan ##
		$params		= array(':status' => 1);
		$pengaturan	= $this->selectprepare("pengaturan", $field=null, $params, "status=:status", $other=null);
		$data_pengaturan = $pengaturan->fetch(PDO::FETCH_OBJ);
		$tahun_atur 	 = $data_pengaturan->tahun;

		//## Cek data Libur ##
		$data_harilibur	= $this->selectprepare("hari_libur", $field=null, $params=null, "", "WHERE DATE_FORMAT(tgl, '%Y') = '".$tahun_atur."' ");
		$count = $data_harilibur->rowCount();
		$no = 0;
		$show = "";
		while($harilibur = $data_harilibur->fetch(PDO::FETCH_OBJ)){
			$dump_harilibur[] = $harilibur->tgl;
			$no++;
			$tgl = explode("-", $harilibur->tgl);
			$show .= "'".$tgl['0']."-".intval($tgl['1'])."-".intval($tgl['2'])."'";
			if($no < $count){
				$show .= ", ";
			}
		}

		if($input == ""){
			return $show;
		} else {
			return $dump_harilibur;
		}
	}
	
	public function sendSMSMasking($handphone, $text, $no_surat=''){
		$usernamesmsmasking="wika_bsi";
		$passwordsmsmasking="bfdb70970ec8f41e4016f6e341c7596c";
		$sender="WIKA-SURAT";
		$drurl="103.25.196.13";#ip public hcis.wika.co.id
		$subs=substr($handphone,1);
		$depan=substr($handphone,0,1);
		if($depan=="0") $handphone="62".$subs;
		$data_xml="data=
		<bulk_sending>
			<username>".$usernamesmsmasking."</username>
			<password>".$passwordsmsmasking."</password>
			<priority>high</priority>
			<sender>".$sender."</sender>
			<dr_url>".$drurl."</dr_url>
			<allowduplicate>1</allowduplicate>
			<data_packet>
				<packet>
					<msisdn>".$handphone."</msisdn>
					<sms>".$text."</sms>
					<is_long_sms>Y</is_long_sms>
				</packet>
			</data_packet>
		</bulk_sending>
		";
		$URL = "http://webapps.promediautama.com:29003/sms_applications/smsb/api_mt_send_message.php";
		$ch = curl_init($URL);
		curl_setopt($ch, CURLOPT_MUTE, 1);
		curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 0);
		curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
		curl_setopt($ch, CURLOPT_POST, 1);
		curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-Type: application/x-www-form-urlencoded'));
		curl_setopt($ch, CURLOPT_POSTFIELDS, $data_xml);
		curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
		$output = curl_exec($ch);
		curl_close($ch);
		return $output;
	}
	public function hpsupdmemo($id_sm, $id_user, $nip){
		$sql = "DELETE FROM memo where status='0' and id_sm='".$id_sm."' and (id_user='".$id_user."' or disposisi like '%".$nip."%')";
		$query = $this->db->prepare($sql);
		$query->execute();
		$sql = "UPDATE memo a
				inner JOIN (SELECT max(id_status) as jum, id_sm from memo where id_sm='".$id_sm."') b ON a.id_sm = b.id_sm and a.id_status=b.jum
				SET a.status = '0'";
		$query = $this->db->prepare($sql);
		$query->execute();
		
		return $query;
	}

}?>