<?php
if(count(get_included_files()) ==1) exit("Direct access not permitted.");
header("Content-type: application/json");
if(isset($_POST['ikdata'])){
	if (!isset($_SESSION)) session_start();
	$db=pDatabase::getInstance();
	$datafungsi=[
		"datacari01"=>"datacari01",
		"getTabel"=>"getTabel",
		"getjenissurat"=>"getjenissurat",
		"cekstatusdisposisi"=>"cekstatusdisposisi",
		"getmasakerja"=>"getmasakerja",
		"getstatussurat"=>"getstatussurat",
		"updatestatussurat"=>"updatestatussurat",
		"updatedisposisi"=>"updatedisposisi",
		"hapusData"=>"hapusData",
		"tutupDisposisi"=>"tutupDisposisi",
		"getdataCatatan"=>"getdataCatatan",
		"getcatatansurat"=>"getcatatansurat",
		"inputcatatansurat"=>"inputcatatansurat",
		"editcatatansurat"=>"editcatatansurat",
		"hapusDatactt"=>"hapusDatactt",
		"getdatasuratcuti"=>"getdatasuratcuti",
		"getdatasurattugas"=>"getdatasurattugas",
		"getdatasuratmasuk"=>"getdatasuratmasuk",
		"getdatasuratpkl"=>"getdatasuratpkl",
		"getdatasuratkeluar"=>"getdatasuratkeluar"
	];
	
	if(!$db->ceckmodule($cl)) exit;
	$data=$_POST['ikdata'];
	$ex=false;
	if(isset($datafungsi[$data[0]])) $ex=$datafungsi[$data[0]]($data);
	echo $ex;
}else{
	echo false;
}

function gethalaman($qr,$dt,$jumlah){
	$db=pDatabase::getInstance();
	
	$rs=$db->loadAssoc($qr,$dt);
	
	$total=1;
	if(isset($rs['total'])){
		$total=ceil($rs['total']/$jumlah);
	}
	if($total==0){
		return 1;
	}else{
		return $total;
	}
}

function datacari01($data=array())
{
	$mode=0;
	$kunci=array("nama","kategori");
	$alias=array("Jenis Surat","Kategori Surat");
	$kolom=array("t3.nama","t2.nama");
	$jenis=array();
	
	$hasil=array("kunci"=>$kunci,"kolom"=>$kolom,"alias"=>$alias,"jenis"=>$jenis);
	if(sizeof($data)>0){
		$mode=$data[1];
	}
	if($mode==0){
		return $hasil;
	}else{
		return json_encode($hasil);
	}
}

function getTabel($data){
	$db=pDatabase::getInstance();
	$cari=$data[3];
	$arrcr=explode("&", $cari);
	
	$datapush=array();
	$wh="";
	if(sizeof($arrcr)>0){
		$arrkunci0=explode("=", $arrcr[0]);
		if(sizeof($arrcr)==1 && sizeof($arrkunci0)==1){
			$cr="%$cari%";
			$wh.="AND t3.nama LIKE ? ";
			array_push($datapush,$cr);
		}else{
			$datacari=$db->datacari(datacari01(),$arrcr);
			$datapush=$datacari['datapush'];
			$wh=$datacari['query'];
		}
	}
	
	$sl="";
	$inner="";
	if($data['idkategori']>0){
		$wh.="and t1.idkategori=? ";
		array_push($datapush,$data['idkategori']);
		
		if($data['idjenis']>0){
			$wh.="and t1.idjenissurat=? ";
			array_push($datapush,$data['idjenis']);
		}
	}
	
	array_unshift($datapush,$_SESSION['profile']['idjabatanstruk'],
		$_SESSION['profile']['idjabatanfung']);
		
	$qt="SELECT COUNT(distinct t1.id) as total FROM tb_suratbdk as t1
		inner join tb_master_kategorisurat as t2 on t1.idkategori=t2.id
		inner join tb_master_jenissurat as t3 on t1.idjenissurat=t3.id
		inner join tb_master_status as a1 on t1.status=a1.id
		inner join tb_suratbdk_disposisi as dp1 on t1.id=dp1.idsurat and dp1.statusdisposisi=1
			and ((dp1.idjabatan=? and dp1.modejabatan=1) or (dp1.idjabatan=? and dp1.modejabatan=2))
		WHERE t1.id>0 ".$wh;
		
	$total=gethalaman($qt,$datapush,$data[2]);
	array_push($datapush,$data[1],$data[2]);
	
	$query="SELECT t1.`id`,
			t1.`tanggal_input`,
			t1.`tanggal_surat`,
			t1.`idpegawai`,
			t1.`iduser`,
			t1.`idkategori`,
			t1.`idjenissurat`,
			t1.`datasurat`,
			t1.`iddisposisi`,
			t1.jumlah_catatan,
			t1.`status`,
			t1.`statusdisposisi`,
			t2.nama as kategorisurat,
			t3.nama as jenissurat,
			t3.in_out,
			a1.nama as namastatus,
			a1.bg,a1.warna,
			dp1.view,
			dp1.edit,
			dp1.hapus,
			dp1.nama as namastatusdisposisi,
			$total as halaman FROM tb_suratbdk as t1
		inner join tb_master_kategorisurat as t2 on t1.idkategori=t2.id
		inner join tb_master_jenissurat as t3 on t1.idjenissurat=t3.id
		inner join tb_master_status as a1 on t1.status=a1.id
		inner join tb_suratbdk_disposisi as dp1 on t1.id=dp1.idsurat and dp1.statusdisposisi=1
			and ((dp1.idjabatan=? and dp1.modejabatan=1) or (dp1.idjabatan=? and dp1.modejabatan=2))
		WHERE t1.id>0 ".$wh."
		GROUP BY t1.id
		ORDER BY t1.tanggal_surat DESC LIMIT ?,?";
	$rs=$db->jsonData($query,$datapush);
	
	return $rs;
}

function cekstatusdisposisi($data){
	$db=pDatabase::getInstance();
	$hasil=array("status"=>0,"pesan"=>[["pesan"=>"Terjadi kesalahan"]],"data"=>array());
	$datahasil=$db->cekdatadisposisi($data['idsurat'],1);
	
	if(isset($datahasil['status'])){
		if($datahasil['status']==1) $hasil['status']=1;
	}
	return json_encode($hasil,true);
}

function getmasakerja($data){
	$db=pDatabase::getInstance();
	date_default_timezone_set('Asia/Jakarta');
	
	$wh="";
	$datapush=array($data['idpegawai']);
	
	$hasil=array(
		"tahun"=>0,
		"bulan"=>0,
		"hari"=>0
	);
	$query="select * from tb_pegawai where id=? ".$wh;
	$rs=$db->loadAssoc($query,$datapush);
	if(isset($rs['id'])){
		$datetime1 = new DateTime($rs['tanggal_sk_awal']);
		$datetime2 = new DateTime();
		$interval = $datetime1->diff($datetime2);
		
		$hasil['tahun']=$interval->y;
		$hasil['bulan']=$interval->m;
		$hasil['hari']=$interval->d;
	}
	
	return json_encode($hasil,true);
}

function getjenissurat($data){
	$db=pDatabase::getInstance();
	$query="select * from tb_master_jenissurat where status=1 and idkategori=? ";
	$rs=$db->jsonData($query,array($data['idkategori']));
	return $rs;
}

function updatedisposisi($data){
	$db=pDatabase::getInstance();
	$hasil=array("status"=>0,"pesan"=>[["pesan"=>"Terjadi kesalahan"]],"data"=>array());
	date_default_timezone_set('Asia/Jakarta');
	
	$idsurat=$data['idsurat'];
	$dbhasil=$db->updatedatadisposisi($idsurat);
	if(isset($dbhasil['status'])) $hasil['status']=$dbhasil['status'];
	return json_encode($hasil,true);
}

function hapusData($data){
	$db=pDatabase::getInstance();
	$hasil=array("status"=>0,"pesan"=>[["pesan"=>"Terjadi Kesalahan"]],"data"=>array());
	$query="select t1.id 
		from tb_suratbdk as t1
		inner join tb_master_disposisi_status_jabatan as t2 on t1.iddisposisi=t2.idsubdisposisi
		inner join tb_master_disposisi_status_jabatan_idst as t3 on t2.id=t3.iddisposisijabatan
		where t1.id=? and t1.statusdisposisi=0 and ((t2.idjabatan=? and t2.mode=1) or (t2.idjabatan=? and t2.mode=2)) and t2.hapus=1  ";
	$rss=$db->loadAssoc($query,array($data['idsurat'],$_SESSION['profile']['idjabatanstruk'],
		$_SESSION['profile']['idjabatanfung']));
	
	if(!isset($rss['id'])) {
		$hasil['pesan']=[["pesan"=>"Tidak diijinkan!"]];
		return json_encode($hasil,true);
	}
	
	$query="DELETE FROM tb_suratbdk WHERE id=? ";
	$rs=$db->query($query,array($data['idsurat']));
	if($rs==1){
		$hasil['status']=1;
	}else{
		$hasil['pesan']=[["pesan"=>"Gagal hapus surat!"]];
	}
	return json_encode($hasil,true);
}

function getstatussurat($data){
	$db=pDatabase::getInstance();
	$rs=$db->getstatusdisposisi($data['idsurat']);
	return json_encode($rs,true);
}

function updatestatussurat($data){
	$db=pDatabase::getInstance();
	$hasil=array("status"=>0,"pesan"=>[["pesan"=>"Terjadi Kesalahan"]],"data"=>array());
	error_log("aaaaaaaaaaaaaaaa");
	if(!$db->cekdisposisistatus($data['idsurat'],$data['idstatus'])) {
		$hasil['pesan']=[["pesan"=>"Tidak diijinkan!"]];
		return json_encode($hasil,true);
	}
	
	$dataup=array(
		"status"=>$data['idstatus'],
		"keterangan"=>$data['keterangan']
	);
	$rs=$db->update("tb_suratbdk",$dataup,"id",$data['idsurat']);
	if($rs==1){
		$db->cekemailupdatestatus($data['idsurat']);
		error_log("-------------------------------");
	}
	$hasil['status']=$rs;
	return json_encode($hasil,true);
}

function tutupDisposisi($data){
	$db=pDatabase::getInstance();
	$hasil=array("status"=>0,"pesan"=>[["pesan"=>"Terjadi Kesalahan"]],"data"=>array());
	date_default_timezone_set('Asia/Jakarta');
	$rshasil=$db->tutupdisposisi($data['idsurat']);
	
	if(isset($rshasil['status'])) $hasil['status']=$rshasil['status'];
	return json_encode($hasil,true);
}

function datacarictt($data=array())
{
	$mode=0;
	$kunci=array("jenis");
	$alias=array("Jenis Surat");
	$kolom=array("t4.nama");
	$jenis=array();
	
	$hasil=array("kunci"=>$kunci,"kolom"=>$kolom,"alias"=>$alias,"jenis"=>$jenis);
	if(sizeof($data)>0){
		$mode=$data[1];
	}
	if($mode==0){
		return $hasil;
	}else{
		return json_encode($hasil);
	}
	
}

function getdataCatatan($data){
	$db=pDatabase::getInstance();
	$cari="";
	$arrcr=explode("&", $cari);
	
	$datapush=array();
	$wh="";
	if(sizeof($arrcr)>0){
		$arrkunci0=explode("=", $arrcr[0]);
		if(sizeof($arrcr)==1 && sizeof($arrkunci0)==1){
			$cr="%$cari%";
			$wh.="AND t4.nama LIKE ? ";
			array_push($datapush,$cr);
		}else{
			$datacari=$db->datacari(datacarictt(),$arrcr);
			$datapush=$datacari['datapush'];
			$wh=$datacari['query'];
		}
	}
	
	$wh.="and t1.idsurat=? ";
	array_push($datapush,$data['idsurat']);
	
	$posisihalaman=($data['halaman']-1)*$data['jumlah'];
	array_push($datapush,$posisihalaman,$data['jumlah']);
	
	$query="select SQL_CALC_FOUND_ROWS t1.id,
				t1.`id`,
				t1.`tanggal`,
				t1.`iduser`,
				t1.`idpegawai`,
				t1.`idsurat`,
				t1.`catatan`,
				if(t2.nama is null,'',t2.nama) as namapegawai
			from tb_suratbdk_catatan as t1
			left join tb_pegawai as t2 on t1.idpegawai=t2.id
			inner join tb_suratbdk as t3 on t1.idsurat=t3.id
			inner join tb_master_jenissurat as t4 on t3.idjenissurat=t4.id
			where t1.id>0 ".$wh."
			order by t1.tanggal asc limit ?,?";
	$rs=$db->loadAll($query,$datapush);
	
	$qrow="SELECT FOUND_ROWS() as totalbaris;";
	$rsrow=$db->loadAssoc($qrow);
	$jumlahhalaman=1;
	if(isset($rsrow['totalbaris'])){
		$jumlahhalaman=ceil($rsrow['totalbaris']/$data['jumlah']);
	}
	
	return json_encode(array("data"=>$rs,"totalhalaman"=>$jumlahhalaman));
}

function getcatatansurat($data){
	$db=pDatabase::getInstance();
	$query="select 
				t1.`id`,
				t1.`tanggal`,
				t1.`iduser`,
				t1.`idpegawai`,
				t1.`idsurat`,
				t1.`catatan`,
				if(t2.nama is null,'',t2.nama) as namapegawai
			from tb_suratbdk_catatan as t1
			left join tb_pegawai as t2 on t1.idpegawai=t2.id
			inner join tb_suratbdk as t3 on t1.idsurat=t3.id
			inner join tb_master_jenissurat as t4 on t3.idjenissurat=t4.id
			where t1.id>0 and t1.idsurat=?
			order by t1.tanggal asc";
	$rs=$db->jsonData($query,array($data['idsurat']));
	return $rs;
}

function inputcatatansurat($data){
	$db=pDatabase::getInstance();
	$hasil=array("status"=>0,"pesan"=>[["pesan"=>"Terjadi Kesalahan"]],"data"=>array());
	
	if(!$db->cekdisposisiaksesupdate($data['idsurat'])){
		$hasil['pesan']=[["pesan"=>"Tidak diijinkan!"]];
		return json_encode($hasil,true);
	}
	
	$dataup=array(
		"catatan"=>$data['catatan'],
		"iduser"=>$_SESSION['profile']['id'],
		"idpegawai"=>$_SESSION['profile']['idpegawai'],
		"idsurat"=>$data['idsurat']
	);
	$rs=$db->insert("tb_suratbdk_catatan",$dataup);
	if($rs!=1){
		$hasil['pesan']=[["pesan"=>"Gagal input catatan!"]];
	}
	$hasil['status']=$rs;
	return json_encode($hasil,true);
}

function editcatatansurat($data){
	$db=pDatabase::getInstance();
	$hasil=array("status"=>0,"pesan"=>[["pesan"=>"Terjadi Kesalahan"]],"data"=>array());
	
	$query="select * from tb_suratbdk_catatan where id=? ";
	$rsc=$db->loadAssoc($query,array($data['id']));
	if(!isset($rsc['id'])){
		$hasil['pesan']=[["pesan"=>"Data tidak ditemukan!"]];
		return json_encode($hasil,true);
	}
	if(!$db->cekdisposisiaksesupdate($rsc['idsurat'])){
		$hasil['pesan']=[["pesan"=>"Tidak diijinkan!"]];
		return json_encode($hasil,true);
	}
	
	$dataup=array(
		"catatan"=>$data['catatan'],
		"iduser"=>$_SESSION['profile']['id'],
		"idpegawai"=>$_SESSION['profile']['idpegawai']
	);
	$rs=$db->update("tb_suratbdk_catatan",$dataup,"id",$data['id']);
	if($rs!=1){
		$hasil['pesan']=[["pesan"=>"Gagal edit catatan!"]];
	}
	$hasil['status']=$rs;
	return json_encode($hasil,true);
}

function hapusDatactt($data){
	$db=pDatabase::getInstance();
	$hasil=array("status"=>0,"pesan"=>[["pesan"=>"Terjadi Kesalahan"]],"data"=>array());
	$query="select * from tb_suratbdk_catatan where id=? ";
	$rsc=$db->loadAssoc($query,array($data['id']));
	if(!isset($rsc['id'])){
		$hasil['pesan']=[["pesan"=>"Data tidak ditemukan!"]];
		return json_encode($hasil,true);
	}
	if(!$db->cekdisposisiaksesupdate($rsc['idsurat'])){
		$hasil['pesan']=[["pesan"=>"Tidak diijinkan!"]];
		return json_encode($hasil,true);
	}
	
	$rs=$db->query("delete from tb_suratbdk_catatan where id=? ",array($data['id']));
	if($rs!=1){
		$hasil['pesan']=[["pesan"=>"Gagal hapus catatan!"]];
	}
	$hasil['status']=$rs;
	return json_encode($hasil,true);
}

function getdatasuratcuti($data){
	$db=pDatabase::getInstance();
	$hasil=array("status"=>0,"pesan"=>"Terjadi Kesalahan","data"=>array());
	$query="SELECT t1.`id`,
				t1.`tanggal_input`,
				t1.`tanggal_surat`,
				t1.`idsurat`,
				t1.`idpegawai`,
				t1.`idjeniscuti`,
				t1.`waktu`,
				t1.`idkodewaktu`,
				t1.tanggal_mulai,
				t1.tanggal_selesai,
				t1.`keperluan`,
				t1.alamat_cuti,
				t1.telp_cuti,
				t1.masa_kerja,
				t1.`keterangan`,
				t1.`jumlah_catatan`,
				t3.nama as jeniscuti,
				t7.`status`,
				t7.statusdisposisi,
				t7.nomor_panjang as nomorsurat,
				t8.nama as kodewaktu,
				t2.nama as namapegawai,
				t2.nip as nip,
				if(t4.nama is null,'Tidak ada',t4.nama) as jabatanstruk,
				if(t5.nama is null,'Tidak ada',t5.nama) as golongan,
				t6.nama as jeniskelamin
				from tb_cuti_pegawai as t1
			inner join tb_pegawai as t2 on t1.idpegawai=t2.id
			inner join tb_master_jeniscuti as t3 on t1.idjeniscuti=t3.id 
			left join tb_master_jabatan as t4 on t2.idjabatanstruk=t4.id
			left join tb_master_golongan as t5 on t2.idgolongan=t5.id
			inner join tb_jenis_kelamin as t6 on t2.idjeniskelamin=t6.id
			inner join tb_suratbdk as t7 on t1.idsurat=t7.id
			inner join tb_kodewaktu as t8 on t1.idkodewaktu=t8.id 
			WHERE t1.idsurat=? ";
	$rs=$db->loadAssoc($query,array($data['idsurat']));
	if(isset($rs['id'])){
		$hasil['status']=1;
		$tanggal=$db->sql_date($rs['tanggal_surat']);
		$tanggalmulai=$db->sql_date($rs['tanggal_mulai']);
		$tanggalselesai=$db->sql_date($rs['tanggal_selesai']);
		ob_start();
		?>
		<h3>DATA SURAT CUTI</h3>
		<div class="group_form">
			<title>Tanggal Surat</title>
			<div class="col10">
				<div style="width: 110px;" class="formdata"><?php echo $tanggal;?></div>
			</div>
		</div>
		<div class="group_form">
			<title>Nomor Surat</title>
			<div class="col10">
				<div style="width: 110px;" class="formdata"><?php echo $rs['nomorsurat'];?></div>
			</div>
		</div>
		<div class="group_form">
			<title>Nama Pegawai</title>
			<div class="col10">
				<div class="formdata"><?php echo $rs['namapegawai'];?></div>
			</div>
		</div>
		<div class="group_form">
			<title>NIP</title>
			<div class="col10">
				<div class="formdata"><?php echo $rs['nip'];?></div>
			</div>
		</div>
		<div class="group_form">
			<title>Jabatan</title>
			<div class="col10">
				<div class="formdata"><?php echo $rs['jabatanstruk'];?></div>
			</div>
		</div>
		<div class="group_form">
			<title>Golongan</title>
			<div class="col10">
				<div class="formdata"><?php echo $rs['golongan'];?></div>
			</div>
		</div>
		<div class="group_form">
			<title>Masa Kerja</title>
			<div class="col10">
				<div class="formdata"><?php echo $rs['masa_kerja']." Tahun";?></div>
			</div>
		</div>
		<div class="group_form">
			<title>Jenis Cuti</title>
			<div class="col10">
				<div class="formdata"><?php echo $rs['jeniscuti'];?></div>
			</div>
		</div>
		<div class="group_form">
			<title>Keperluan Cuti</title>
			<div class="col10">
				<div class="formdata"><?php echo $rs['keperluan'];?></div>
			</div>
		</div>
		<div class="group_form">
			<title>Lama Waktu Cuti</title>
			<div class="col10">
				<div class="formdata"><?php echo $rs['waktu']." ".$rs['kodewaktu'];?></div>
			</div>
		</div>
		<div class="group_form">
			<title>Tanggal Mulai Cuti</title>
			<div class="col10">
				<div class="formdata"><?php echo $tanggalmulai;?></div>
			</div>
		</div>
		<div class="group_form">
			<title>Tanggal Selesai Cuti</title>
			<div class="col10">
				<div class="formdata"><?php echo $tanggalselesai;?></div>
			</div>
		</div>
		<div class="group_form">
			<title>Alamat Selama Cuti</title>
			<div class="col10">
				<div class="formdata"><?php echo $rs['alamat_cuti'];?></div>
			</div>
		</div>
		<div class="group_form">
			<title>No Telp Selama Cuti</title>
			<div class="col10">
				<div class="formdata"><?php echo $rs['telp_cuti'];?></div>
			</div>
		</div>
		<div style="clear: both;"></div>
		<?php
		$htmdata=ob_get_clean();
		$hasil['data']=$htmdata;
	}
	return json_encode($hasil,true);
}

function getdatasurattugas($data){
	$db=pDatabase::getInstance();
	$hasil=array("status"=>0,"pesan"=>"Terjadi Kesalahan","data"=>array());
	$query="SELECT t1.`id`,
				t1.`tanggal_input`,
				t1.`tanggal_surat`,
				t1.`nomor`,
				t1.`nomor_panjang`,
				t1.`idsurat`,
				t1.`iduser`,
				t1.`jumlah_pegawai`,
				t1.`tanggal_mulai`,
				t1.`tanggal_selesai`,
				t1.`tugas`,
				t1.lokasi,
				t1.`catatan_dasar`,
				t1.`catatan_menimbang`,
				t1.`catatan_surat`,
				t1.`tempat_tugas`,
				t1.`keterangan`,
				t2.`jumlah_catatan`,
				t2.`status`,
				t2.statusdisposisi,
				t2.nomor_panjang as nomorsurat,
				t3.nama as jenissurat
			FROM tb_surat_tugas as t1
			inner join tb_suratbdk as t2 on t1.idsurat=t2.id
			inner join tb_master_jenissurat as t3 on t2.idjenissurat=t3.id
			inner join tb_surat_tugas_pegawai as t4 on t1.id=t4.idsurattugas
			WHERE t1.idsurat=? ";
	$rs=$db->loadAssoc($query,array($data['idsurat']));
	if(isset($rs['id'])){
		$hasil['status']=1;
		$tanggal=$db->sql_date($rs['tanggal_surat']);
		$tanggalmulai=$db->sql_date($rs['tanggal_mulai']);
		$tanggalselesai=$db->sql_date($rs['tanggal_selesai']);
		ob_start();
		?>
		<h3>DATA SURAT TUGAS</h3>
		<div class="group_form">
			<title>Tanggal Surat</title>
			<div class="col10">
				<div style="width: 110px;" class="formdata"><?php echo $tanggal;?></div>
			</div>
		</div>
		<div class="group_form">
			<title>Nomor Surat</title>
			<div class="col10">
				<div class="formdata"><?php echo $rs['nomorsurat'];?></div>
			</div>
		</div>
		<div class="group_form">
			<title>Tanggal Mulai Tugas</title>
			<div class="col10">
				<div style="width: 110px;" class="formdata"><?php echo $tanggalmulai;?></div>
			</div>
		</div>
		<div class="group_form">
			<title>Tanggal Selesai Tugas</title>
			<div class="col10">
				<div style="width: 110px;" class="formdata"><?php echo $tanggalselesai;?></div>
			</div>
		</div>
		<div class="group_form">
			<title>Nama Tugas</title>
			<div class="col10">
				<div class="formdata"><?php echo $rs['tugas'];?></div>
			</div>
		</div>
		<div class="group_form">
			<title>Lokasi Tugas</title>
			<div class="col10">
				<div class="formdata"><?php echo $rs['lokasi'];?></div>
			</div>
		</div>
		<h3>DATA PEGAWAI</h3>
		<div id="grid_pegawai">
			<table class="table_grid">
				<tr>
					<th width="200px" align="left">NIP</th>
					<th align="left">NAMA</th>
				</tr>
			<?php
			$query="select t1.id,t2.nama,t2.nip from tb_surat_tugas_pegawai as t1
				inner join tb_pegawai as t2 on t1.idpegawai=t2.id
				inner join tb_surat_tugas as t3 on t1.idsurattugas=t3.id
				where t3.idsurat=? ";
			$rsp=$db->loadAll($query,array($data['idsurat']));
			foreach ($rsp as $obp) {
				?>
				<tr>
					<td width="200px"><?php echo $obp['nip'];?></td>
					<td><?php echo $obp['nama'];?></td>
				</tr>
				<?php
			}
			?>
			</table>
		</div>
		<div style="clear: both;"></div>
		<?php
		$htmdata=ob_get_clean();
		$hasil['data']=$htmdata;
	}
	return json_encode($hasil,true);
}

function getdatasuratmasuk($data){
	$db=pDatabase::getInstance();
	$hasil=array("status"=>0,"pesan"=>"Terjadi Kesalahan","data"=>array());
	$query="SELECT t1.`id`,
			t1.`tanggal_input`,
			t1.`tanggal_surat`,
			t1.`idkategori`,
			t1.`idjenissurat`,
			t1.idkategorijenissurat,
			t1.`idsurat`,
			t1.`iduser`,
			t1.`nomor`,
			t1.`tanggal_terima`,
			t1.`pengirim`,
			t1.`alamat_pengirim`,
			t1.`keperluan`,
			t1.`tujuan`,
			t1.`keterangan`,
			t1.`jumlah_dokumen`,
			t2.`jumlah_catatan`,
			t2.`status`,
			t2.statusdisposisi,
			t2.nomor_panjang as nomorsurat,
			t4.lib,
			t4.nama as jenissurat,
			t5.nama as kategorijenissurat,
			a1.nama as namastatus,
			a1.bg,a1.warna
		FROM tb_surat_masuk as t1
		inner join tb_suratbdk as t2 on t1.idsurat=t2.id
		inner join tb_master_kategorisurat as t3 on t1.idkategori=t3.id
		inner join tb_master_jenissurat as t4 on t1.idjenissurat=t4.id
		inner join tb_master_kategorijenissurat as t5 on t1.idkategorijenissurat=t5.id
		inner join tb_master_status as a1 on t2.status=a1.id
		WHERE t1.idsurat=? ";
	$rs=$db->loadAssoc($query,array($data['idsurat']));
	if(isset($rs['id'])){
		$hasil['status']=1;
		$tanggalsurat=$db->fulldate($rs['tanggal_surat']);
		$tanggalterima=$db->fulldate($rs['tanggal_terima']);
		ob_start();
		?>
		<h3>DATA SURAT MASUK</h3>
		<div class="group_form">
			<title>Nomor Surat</title>
			<div class="col10">
				<div class="formdata"><?php echo $rs['nomor'];?></div>
			</div>
		</div>
		<div class="group_form">
			<title>Tanggal Surat</title>
			<div class="col10">
				<div class="formdata"><?php echo $tanggalsurat;?></div>
			</div>
		</div>
		<div class="group_form">
			<title>Tanggal Terima</title>
			<div class="col10">
				<div class="formdata"><?php echo $tanggalterima;?></div>
			</div>
		</div>
		<div class="group_form">
			<title>Pengirim Surat</title>
			<div class="col10">
				<div class="formdata"><?php echo $rs['pengirim'];?></div>
			</div>
		</div>
		<div class="group_form">
			<title>Alamat Pengirim</title>
			<div class="col10">
				<div class="formdata"><?php echo $rs['alamat_pengirim'];?></div>
			</div>
		</div>
		<div class="group_form">
			<title>Tujuan/Kepada Surat</title>
			<div class="col10">
				<div class="formdata"><?php echo $rs['tujuan'];?></div>
			</div>
		</div>
		<div class="group_form">
			<title>Keperluan</title>
			<div class="col10">
				<div class="formdata"><?php echo $rs['keperluan'];?></div>
			</div>
		</div>
		<div class="group_form">
			<title>Keterangan tambahan (jika ada)</title>
			<div class="col10">
				<div class="formdata"><?php echo $rs['keterangan'];?></div>
			</div>
		</div>
		<div class="group_form">
			<title>Kategori Jenis Surat</title>
			<div class="col10">
				<div class="formdata"><?php echo $rs['kategorijenissurat'];?></div>
			</div>
		</div>
		<div style="clear: both;"></div>
		<br />
		<h3>DOKUMEN SURAT</h3>
		<div id="grid_pegawai" style="background: white;">
			<table class="table_grid">
				<tr>
					<th width="40px"></th>
					<th align="left">NAMA DOKUMEN</th>
					<th width="40px" align="left">DOK.</th>
				</tr>
				<?php
				$query="SELECT  
						t1.nama,
						t1.iddokumen
					FROM tb_surat_masuk_dokumen as t1
				inner join tb_dokumen as t2 on t1.iddokumen=t2.id
				where t1.idsuratmasuk=? ";
				$rsdok=$db->loadAll($query,array($rs['id']));
				$nomorurut=1;
				foreach ($rsdok as $obd) {
					?>
					<tr>
						<td width="40px" align="center"><?php echo $nomorurut;?></td>
						<td align="left"><?php echo $obd['nama'];?></td>
						<td align="center" width="40px" align="left"><span onclick="disposisisurat.lihatdokumen(<?php echo $obd['iddokumen'];?>)" class="dok_ya"></span></td>
					</tr>
					<?php
					$nomorurut=$nomorurut+1;
				}
				?>
			</table>
		</div>
		<div style="clear: both;"></div>
		<?php
		$htmdata=ob_get_clean();
		$hasil['data']=$htmdata;
	}
	return json_encode($hasil,true);
}

function getdatasuratpkl($data){
	$db=pDatabase::getInstance();
	$hasil=array("status"=>0,"pesan"=>"Terjadi Kesalahan","data"=>array());
	$query="SELECT t1.`id`,
			t1.`tanggal_update`,
			t1.`tanggal_surat`,
			t1.`iduser`,
			t1.`idsurat`,
			t1.`nomor`,
			t1.nomor_surat_pengajuan,
			t1.`nama_sekolah`,
			t1.`alamat_sekolah`,
			t1.`jumlah_peserta`,
			t1.`jumlah_dokumen`,
			t1.`keterangan`,
			t1.`status_balasan`,
			t2.`status`,
			t2.statusdisposisi,
			t2.nomor_panjang as nomorsurat,
			t3.lib,
			t3.nama as jenissurat,
			t4.propinsi,
			t5.kota,
			a1.nama as namastatus,
			a1.bg,a1.warna
			FROM tb_pengajuan_pkl as t1
		inner join tb_suratbdk as t2 on t1.idsurat=t2.id
		inner join tb_master_jenissurat as t3 on t2.idjenissurat=t3.id
		left join tb_propinsi as t4 on t1.idpropinsi=t4.id 
		left join tb_kota as t5 on t1.idkota=t5.id
		inner join tb_master_status as a1 on t2.status=a1.id
		WHERE t1.idsurat=? ";
	$rs=$db->loadAssoc($query,array($data['idsurat']));
	if(isset($rs['id'])){
		$hasil['status']=1;
		$tanggalsurat=$db->fulldate($rs['tanggal_surat']);
		ob_start();
		?>
		<h3>DATA SURAT PENGAJUAN PKL</h3>
		<div class="group_form">
			<title>Nomor Surat Pengajuan</title>
			<div class="col10">
				<div class="formdata"><?php echo $rs['nomor_surat_pengajuan'];?></div>
			</div>
		</div>
		<div class="group_form">
			<title>Tanggal Surat</title>
			<div class="col10">
				<div class="formdata"><?php echo $tanggalsurat;?></div>
			</div>
		</div>
		
		<div class="group_form">
			<title>Nama Sekolah</title>
			<div class="col10">
				<div class="formdata"><?php echo $rs['nama_sekolah'];?></div>
			</div>
		</div>
		<div class="group_form">
			<title>Propinsi</title>
			<div class="col10">
				<div class="formdata"><?php echo $rs['propinsi'];?></div>
			</div>
		</div>
		<div class="group_form">
			<title>Kota/Kabupaten</title>
			<div class="col10">
				<div class="formdata"><?php echo $rs['kota'];?></div>
			</div>
		</div>
		<div class="group_form">
			<title>Alamat Sekolah</title>
			<div class="col10">
				<div class="formdata"><?php echo $rs['alamat_sekolah'];?></div>
			</div>
		</div>
		
		<div class="group_form">
			<title>Keterangan tambahan (jika ada)</title>
			<div class="col10">
				<div class="formdata"><?php echo $rs['keterangan'];?></div>
			</div>
		</div>
		
		<div style="clear: both;"></div>
		<br />
		<h3>DAFTAR PESERTA</h3>
		<?php
		if($rs['jumlah_peserta']>0){
			?>
			<div style="background: white;">
				<table class="table_grid">
					<tr>
						<th width="40px">No</th>
						<th width="140px" align="left">NAMA PESERTA</th>
						<th width="70px" align="left">NIM/NIS</th>
						<th align="left">ALAMAT</th>
					</tr>
					<?php
					$query="SELECT *
						FROM tb_pengajuan_pkl_peserta
					where idsuratpkl=? ";
					$rsps=$db->loadAll($query,array($rs['id']));
					$nomorurut=1;
					foreach ($rsps as $obps) {
						?>
						<tr>
							<td width="40px" align="center"><?php echo $nomorurut;?></td>
							<td width="140px" align="left"><?php echo $obps['nama'];?></td>
							<td width="70px"><?php echo $obps['nopelajar'];?></td>
							<td align="left"><?php echo $obps['alamat'];?></td>
						</tr>
						<?php
						$nomorurut=$nomorurut+1;
					}
					?>
				</table>
			</div>
			<?php
		}else{
			?>
			<div>TIDAK ADA PESERTA</div>
			<?php
		}
		?>
		<div style="clear: both;"></div>
		<br />
		<h3>DOKUMEN SURAT</h3>
		<?php
		if($rs['jumlah_dokumen']>0){
			?>
			<div style="background: white;">
				<table class="table_grid">
					<tr>
						<th width="40px"></th>
						<th align="left">NAMA DOKUMEN</th>
						<th width="40px" align="left">DOK.</th>
					</tr>
					<?php
					$query="SELECT  
							t1.nama,
							t1.iddokumen
						FROM tb_pengajuan_pkl_dokumen as t1
					inner join tb_dokumen as t2 on t1.iddokumen=t2.id
					where t1.idsuratpkl=? ";
					$rsdok=$db->loadAll($query,array($rs['id']));
					$nomorurut=1;
					foreach ($rsdok as $obd) {
						?>
						<tr>
							<td width="40px" align="center"><?php echo $nomorurut;?></td>
							<td align="left"><?php echo $obd['nama'];?></td>
							<td align="center" width="40px" align="left"><span onclick="disposisisurat.lihatdokumen(<?php echo $obd['iddokumen'];?>)" class="dok_ya"></span></td>
						</tr>
						<?php
						$nomorurut=$nomorurut+1;
					}
					?>
				</table>
			</div>
			<?php
		}else{
			?>
			<div>TIDAK ADA DOKUMEN SURAT</div>
			<?php
		}
		?>
		<div style="clear: both;"></div>
		<?php
		$htmdata=ob_get_clean();
		$hasil['data']=$htmdata;
	}
	return json_encode($hasil,true);
}

function getdatasuratkeluar($data){
	$db=pDatabase::getInstance();
	$hasil=array("status"=>0,"pesan"=>"Terjadi Kesalahan","data"=>array());
	$query="SELECT t1.`id`,
			t1.`tanggal_input`,
			t1.`tanggal_surat`,
			t1.`idkategori`,
			t1.`idjenissurat`,
			t1.`idkategorijenissurat`,
			t1.`idsurat`,
			t1.`iduser`,
			t1.`nomor`,
			t1.`keperluan`,
			t1.`tujuan`,
			t1.`alamat_tujuan`,
			t1.`keterangan`,
			t1.`jumlah_dokumen`,
			t1.`status`,
			t2.jumlah_catatan,
			t5.nama as sifatsurat,
			a1.nama as namastatus,
			a1.bg,a1.warna
			FROM tb_surat_keluar as t1
		inner join tb_suratbdk as t2 on t1.idsurat=t2.id
		inner join tb_master_kategorisurat as t3 on t1.idkategori=t3.id
		inner join tb_master_jenissurat as t4 on t1.idjenissurat=t4.id
		inner join tb_master_kategorijenissurat as t5 on t1.idkategorijenissurat=t5.id
		inner join tb_master_status as a1 on t2.status=a1.id
		WHERE t1.idsurat=? ";
	$rs=$db->loadAssoc($query,array($data['idsurat']));
	if(isset($rs['id'])){
		$hasil['status']=1;
		$tanggalsurat=$db->fulldate($rs['tanggal_surat']);
		ob_start();
		?>
		<h3>DATA SURAT KELUAR</h3>
		<div class="group_form">
			<title>Nomor Surat</title>
			<div class="col10">
				<div class="formdata"><?php echo $rs['nomor'];?></div>
			</div>
		</div>
		<div class="group_form">
			<title>Tanggal Surat</title>
			<div class="col10">
				<div class="formdata"><?php echo $tanggalsurat;?></div>
			</div>
		</div>
		<div class="group_form">
			<title>Sifat Surat</title>
			<div class="col10">
				<div class="formdata"><?php echo $rs['sifatsurat'];?></div>
			</div>
		</div>
		<div class="group_form">
			<title>Tujuan Surat</title>
			<div class="col10">
				<div class="formdata"><?php echo $rs['tujuan'];?></div>
			</div>
		</div>
		<div class="group_form">
			<title>Alamat Tujuan Surat</title>
			<div class="col10">
				<div class="formdata"><?php echo $rs['alamat_tujuan'];?></div>
			</div>
		</div>
		
		<div class="group_form">
			<title>Keperluan</title>
			<div class="col10">
				<div class="formdata"><?php echo $rs['keperluan'];?></div>
			</div>
		</div>
		<div class="group_form">
			<title>Keterangan tambahan (jika ada)</title>
			<div class="col10">
				<div class="formdata"><?php echo $rs['keterangan'];?></div>
			</div>
		</div>
		
		<div style="clear: both;"></div>
		<br />
		<h3>DOKUMEN SURAT</h3>
		<div id="grid_pegawai" style="background: white;">
			<table class="table_grid">
				<tr>
					<th width="40px"></th>
					<th align="left">NAMA DOKUMEN</th>
					<th width="40px" align="left">DOK.</th>
				</tr>
				<?php
				$query="SELECT  
						t1.nama,
						t1.iddokumen
					FROM tb_surat_keluar_dokumen as t1
				inner join tb_dokumen as t2 on t1.iddokumen=t2.id
				where t1.idsuratkeluar=? ";
				$rsdok=$db->loadAll($query,array($rs['id']));
				$nomorurut=1;
				foreach ($rsdok as $obd) {
					?>
					<tr>
						<td width="40px" align="center"><?php echo $nomorurut;?></td>
						<td align="left"><?php echo $obd['nama'];?></td>
						<td align="center" width="40px" align="left"><span onclick="disposisisurat.lihatdokumen(<?php echo $obd['iddokumen'];?>)" class="dok_ya"></span></td>
					</tr>
					<?php
					$nomorurut=$nomorurut+1;
				}
				?>
			</table>
		</div>
		<div style="clear: both;"></div>
		<?php
		$htmdata=ob_get_clean();
		$hasil['data']=$htmdata;
	}
	return json_encode($hasil,true);
}
?>