Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <html>
- <head>
- <link rel="stylesheet" href="style.css" type="text/css" />
- <link type="text/css" href="../css/jquery-ui-1.8.16.custom.css" rel="stylesheet" />
- <script src="awesomechart.js"></script>
- <script type="text/javascript" src="../js/jquery-1.6.2.min.js"></script>
- <script type="text/javascript" src="../js/jquery-ui-1.8.16.custom.min.js"></script>
- <script type="text/javascript">
- $(function(){
- $('#tabs').tabs();
- });
- </script>
- <style type="text/css">
- /*demo page css*/
- body{ font: 62.5% "Trebuchet MS", sans-serif; margin: 10px;}
- #data { font: 12px "Trebuchet MS", sans-serif; margin: 10px;}
- .demoHeaders { margin-top: 2em; }
- #dialog_link {padding: .4em 1em .4em 20px;text-decoration: none;position: relative;}
- #dialog_link span.ui-icon {margin: 0 5px 0 0;position: absolute;left: .2em;top: 50%;margin-top: -8px;}
- ul#icons {margin: 0; padding: 0;}
- ul#icons li {margin: 2px; position: relative; padding: 4px 0; cursor: pointer; float: left; list-style: none;}
- ul#icons span.ui-icon {float: left; margin: 0 4px;}
- </style>
- </head>
- <body>
- <?php
- include('../lib/conn.php');
- $table = $_GET["table"];
- $lon = $_GET["lon"];
- $lat = $_GET["lat"];
- $par = $_GET["par"];
- $perulangan = explode(',', $par);
- $data_repeat = count($perulangan);
- // print_r($perulangan);
- $check = 1;
- $nilai = "( SELECT satker.inst_satkerkd AS kode
- FROM provinsi prop
- LEFT JOIN kp_inst_satker satker on substring(trim(to_char(prop.kode_bps, '9999')) from 1 for 2) = trim(substring(satker.inst_lokprov from 1 for 2))
- WHERE
- satker.inst_satkerinduk ='00' AND
- st_contains( the_geom, st_setsrid( st_makepoint($lon, $lat),-1)))";
- $sql = "SELECT rekap_laporan.index,
- rekap_laporan.Nama,
- rekap_laporan.thn_skrng,
- rekap_laporan.thn_1,
- rekap_laporan.thn FROM(";
- for($i=0; $i<$data_repeat; $i++)
- {
- if ($check != 1) {
- $sql .=" UNION ";
- }
- $check++;
- if ( $perulangan[$i] == 1) {
- $sql .= " SELECT 1 AS index,'Dimulainya Penyidikan(SPDP)' AS Nama,
- (SELECT SUM(total) FROM vw_pidum_spdp WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-1) AS thn_skrng,
- (SELECT SUM(total) FROM vw_pidum_spdp WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-2) AS thn_1,
- (SELECT SUM(total) FROM vw_pidum_spdp WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-3) AS thn ";
- }
- if ( $perulangan[$i] == 2) {
- $sql .= " SELECT 2 AS index,'Penerimaan Berkas Tahap Pertama' AS Nama,
- (SELECT SUM(total) FROM vw_pidum_terimaberkas WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-1) AS thn_skrng,
- (SELECT SUM(total) FROM vw_pidum_terimaberkas WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-2) AS thn_1,
- (SELECT SUM(total) FROM vw_pidum_terimaberkas WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-3) AS thn ";
- }
- if ( $perulangan[$i] == 3) {
- $sql .= " SELECT 3 AS index, 'Pemeriksaan Tambahan' AS Nama,
- (SELECT SUM(total) FROM vw_pidum_periksatambahan WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-1) AS thn_skrng,
- (SELECT SUM(total) FROM vw_pidum_periksatambahan WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-2) AS thn_1,
- (SELECT SUM(total) FROM vw_pidum_periksatambahan WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-3) AS thn ";
- }
- if ( $perulangan[$i] == 4) {
- $sql .= " SELECT 4 AS index,'APB / APS' AS Nama,
- (SELECT SUM(total) FROM vw_pidum_apbaps WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-1) AS thn_skrng,
- (SELECT SUM(total) FROM vw_pidum_apbaps WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-2) AS thn_1,
- (SELECT SUM(total) FROM vw_pidum_apbaps WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-3) AS thn ";
- }
- if ( $perulangan[$i] == 5) {
- $sql .= " SELECT 5 AS index,'APC' AS Nama,
- (SELECT SUM(total) FROM vw_pidum_apc WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-1) AS thn_skrng,
- (SELECT SUM(total) FROM vw_pidum_apc WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-2) AS thn_1,
- (SELECT SUM(total) FROM vw_pidum_apc WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-3) AS thn ";
- }
- if ( $perulangan[$i] == 6) {
- $sql .= " SELECT 6 AS index,'Upaya Hukum' AS Nama,
- (SELECT SUM(total) FROM vw_pidum_upayahukum WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-1) AS thn_skrng,
- (SELECT SUM(total) FROM vw_pidum_upayahukum WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-2) AS thn_1,
- (SELECT SUM(total) FROM vw_pidum_upayahukum WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-3) AS thn ";
- }
- if ( $perulangan[$i] == 7) {
- $sql .= " SELECT 7 AS index,'Perkara Penting' AS Nama,
- (SELECT SUM(total) FROM vw_pidum_parting WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-1) AS thn_skrng,
- (SELECT SUM(total) FROM vw_pidum_parting WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-2) AS thn_1,
- (SELECT SUM(total) FROM vw_pidum_parting WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-3) AS thn ";
- }
- if ( $perulangan[$i] == 8) {
- $sql .= " SELECT 8 AS index,'Perkara Penting' AS Nama,
- (SELECT SUM(total) FROM vw_pidum_tuntut_tahanan WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-1) AS thn_skrng,
- (SELECT SUM(total) FROM vw_pidum_tuntut_tahanan WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-2) AS thn_1,
- (SELECT SUM(total) FROM vw_pidum_tuntut_tahanan WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-3) AS thn ";
- }
- if ( $perulangan[$i] == 9) {
- $sql .= " SELECT 9 AS index,'Serah Berkas Tahap II' AS Nama,
- (SELECT SUM(total) FROM vw_pidum_tahap2 WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-1) AS thn_skrng,
- (SELECT SUM(total) FROM vw_pidum_tahap2 WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-2) AS thn_1,
- (SELECT SUM(total) FROM vw_pidum_tahap2 WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-3) AS thn ";
- }
- if ( $perulangan[$i] == 10) {
- $sql .= " SELECT 10 AS index,'Kegiatan Penyelidikan' AS Nama,
- (SELECT SUM(total) FROM vw_pidsus_kor_selidik WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-1) AS thn_skrng,
- (SELECT SUM(total) FROM vw_pidsus_kor_selidik WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-2) AS thn_1,
- (SELECT SUM(total) FROM vw_pidsus_kor_selidik WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-3) AS thn ";
- }
- if ( $perulangan[$i] == 11) {
- $sql .= " SELECT 11 AS index,'Kegiatan Penyidikan' AS Nama,
- (SELECT SUM(total) FROM vw_pidsus_kor_penyidik WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-1) AS thn_skrng,
- (SELECT SUM(total) FROM vw_pidsus_kor_penyidik WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-2) AS thn_1,
- (SELECT SUM(total) FROM vw_pidsus_kor_penyidik WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-3) AS thn ";
- }
- if ( $perulangan[$i] == 12) {
- $sql .= " SELECT 12 AS index,'Dimulainya Penyidikan' AS Nama,
- (SELECT SUM(total) FROM vw_pidsus_kor_mlsidik WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-1) AS thn_skrng,
- (SELECT SUM(total) FROM vw_pidsus_kor_mlsidik WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-2) AS thn_1,
- (SELECT SUM(total) FROM vw_pidsus_kor_mlsidik WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-3) AS thn ";
- }
- if ( $perulangan[$i] == 13) {
- $sql .= " SELECT 13 AS index,'Terima Berkas Tahap Perkara' AS Nama,
- (SELECT SUM(total) FROM vw_pidsus_kor_terimaberkas WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-1) AS thn_skrng,
- (SELECT SUM(total) FROM vw_pidsus_kor_terimaberkas WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-2) AS thn_1,
- (SELECT SUM(total) FROM vw_pidsus_kor_terimaberkas WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-3) AS thn ";
- }
- if ( $perulangan[$i] == 14) {
- $sql .= " SELECT 14 AS index,'Kegiatan Penuntutan' AS Nama,
- (SELECT SUM(total) FROM vw_pidsus_kor_kegtuntut WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-1) AS thn_skrng,
- (SELECT SUM(total) FROM vw_pidsus_kor_kegtuntut WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-2) AS thn_1,
- (SELECT SUM(total) FROM vw_pidsus_kor_kegtuntut WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-3) AS thn ";
- }
- if ( $perulangan[$i] == 15) {
- $sql .= " SELECT 15 AS index,'Upaya Hukum' AS Nama,
- (SELECT SUM(total) FROM vw_pidsus_kor_updayahukum WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-1) AS thn_skrng,
- (SELECT SUM(total) FROM vw_pidsus_kor_updayahukum WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-2) AS thn_1,
- (SELECT SUM(total) FROM vw_pidsus_kor_updayahukum WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-3) AS thn ";
- }
- if ( $perulangan[$i] == 16) {
- $sql .= " SELECT 16 AS index,'Dimulainya Penyidikan (SPDP)' AS Nama,
- (SELECT SUM(total) FROM vw_pidsus_eko_spdp WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-1) AS thn_skrng,
- (SELECT SUM(total) FROM vw_pidsus_eko_spdp WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-2) AS thn_1,
- (SELECT SUM(total) FROM vw_pidsus_eko_spdp WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-3) AS thn ";
- }
- if ( $perulangan[$i] == 17) {
- $sql .= " SELECT 17 AS index,'Terima Berkas Perkara' AS Nama,
- (SELECT SUM(total) FROM vw_pidsus_eko_terimaberkas WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-1) AS thn_skrng,
- (SELECT SUM(total) FROM vw_pidsus_eko_terimaberkas WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-2) AS thn_1,
- (SELECT SUM(total) FROM vw_pidsus_eko_terimaberkas WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-3) AS thn ";
- }
- if ( $perulangan[$i] == 18) {
- $sql .= " SELECT 18 AS index,'Kegiatan Penuntutan' AS Nama,
- (SELECT SUM(total) FROM vw_pidsus_eko_kegtuntut WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-1) AS thn_skrng,
- (SELECT SUM(total) FROM vw_pidsus_eko_kegtuntut WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-2) AS thn_1,
- (SELECT SUM(total) FROM vw_pidsus_eko_kegtuntut WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-3) AS thn ";
- }
- if ( $perulangan[$i] == 19) {
- $sql .= " SELECT 19 AS index,'Upaya Hukum' AS Nama,
- (SELECT SUM(total) FROM vw_pidsus_eko_upayahukum WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-1) AS thn_skrng,
- (SELECT SUM(total) FROM vw_pidsus_eko_upayahukum WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-2) AS thn_1,
- (SELECT SUM(total) FROM vw_pidsus_eko_upayahukum WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-3) AS thn ";
- }
- if ( $perulangan[$i] == 20) {
- $sql .= " SELECT 20 AS index,'Kegiatan Penyidikan' AS Nama,
- (SELECT SUM(total) FROM vw_pidsus_eko_sidik WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-1) AS thn_skrng,
- (SELECT SUM(total) FROM vw_pidsus_eko_sidik WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-2) AS thn_1,
- (SELECT SUM(total) FROM vw_pidsus_eko_sidik WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-3) AS thn ";
- }
- if ( $perulangan[$i] == 21) {
- $sql .= " SELECT 21 AS index,'Hasil Dinas' AS Nama,
- (SELECT SUM(total) FROM vw_pidum_hasil_dinas WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-1) AS thn_skrng,
- (SELECT SUM(total) FROM vw_pidum_hasil_dinas WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-2) AS thn_1,
- (SELECT SUM(total) FROM vw_pidum_hasil_dinas WHERE satker=$nilai AND tahun = EXTRACT(YEAR FROM CURRENT_DATE)-3) AS thn ";
- }
- }
- $sql .=" ) AS rekap_laporan
- WHERE TRUE
- ORDER BY index ";
- // echo $sql;
- $sql2 = "select
- st_geometrytype(the_geom) as geometrytype,
- st_area(the_geom) as area, AsEWKT(ST_MakePoint($lon, $lat)) as lon_lat, ST_XMin(the_geom) as minx, ST_YMin(the_geom) miny,
- ST_XMax(the_geom) as maxx, ST_XMin(the_geom) as maxy, provinsi as nama, luas_prov as luas_prov, spdp.masuk as spdp_masuk
- from provinsi
- left join (
- select a.inst_lokprov as lok_prov, sum(b.masuk) as masuk
- from kp_inst_satker a, es_pidum_spdpbr b
- where a.inst_satkerkd = b.satker group by a.inst_lokprov
- ) as spdp
- on substring(trim(to_char(kode_bps, '9999')) from 1 for 2) = trim(substring(spdp.lok_prov from 1 for 2))
- where
- st_contains(
- the_geom,
- st_setsrid(
- st_makepoint($lon, $lat),
- -1))";
- // echo $sql2;
- $db = pg_connect("user=$user dbname=$dbname host=$host port=$port password=$password");
- $result = pg_query($db, $sql);
- $result2 = pg_query($db, $sql2);
- ?>
- <div id="tabs">
- <?php
- while ($row2 = pg_fetch_array($result2)) {
- echo '<center><h2> KEJATI '.$row2['nama'].'</h2></center>'."";
- echo "<center><h3>Detil Informasi</h3></center>";
- }
- ?>
- <ul>
- <li><a href="#tabs-1">Rekap Laporan Tahunan</a></li>
- <li><a href="#tabs-2">Rekap Laporan Bulanan</a></li>
- </ul>
- <div id="tabs-1">
- <?php
- //$row = pg_fetch_row($result);
- echo "<table id ='data'>";
- echo "<th> No. </th>
- <th> Nama </th>
- <th>".date('Y', strtotime('-3 year'))."</th>
- <th>".date('Y', strtotime('-2 year'))."</th>
- <th>".date('Y', strtotime('-1 year'))."</th>
- ";
- while ($row = pg_fetch_array($result)) {
- /*
- echo '<center><h5> KEJATI '.$row['nama'].'</h5></center>'."";
- echo "<center><h6>Detil Informasi</h6></center>";
- echo 'Nama Kabupaten: ', $row['kabupaten']."<br />";
- echo 'luas kabupaten: ', $row['luas_kabu']."<br />";
- echo 'luas provinsi: ', $row['luas_prov']."<br />";
- echo 'spdp :', $row['spdp_masuk']."<br />";
- */
- echo "<tr>";
- echo "<td>", $row['index']."</td>";
- echo "<td>", $row['nama']."</td>";
- echo "<td>", $row['thn']."</td>";
- echo "<td>", $row['thn_1']."</td>";
- echo "<td> ", $row['thn_skrng']."</td>";
- echo "</tr>";
- $conn = oci_connect('simkari', 'bandung', '10.1.4.54/simkari');
- $oracle = "select inst_satkerkd from kp_inst_satker where inst_nama like '%".$row['nama']."%'";
- //echo $oracle;
- $q = oci_parse($conn,$oracle);
- oci_execute($q);
- while (oci_fetch_array($q)){
- echo "isnt_satkerkd = ".ociresult($q,"INST_SATKERKD");
- }
- oci_close($conn);
- }
- echo "</table>";
- $i=0;
- while ($obj = pg_fetch_row($result)) {
- $lon = floatval(trim(pg_result($result,1)));
- $lat = floatval(trim(pg_result($result,2)));
- $latlon = array($lon,$lat);
- $arr[] = array(
- "geometry" => array("type" => "Point", "coordinates" => $latlon),
- "type" => "Feature",
- "properties" => array(
- "prop1" => trim(pg_result($result,3)),
- "prop2" => trim(pg_result($result,4)),
- "prop3" => trim(pg_result($result,5))
- ),
- "id" => $i
- );
- // end of array
- $i++;
- }
- //echo $geojson = '{"type":"FeatureCollection","features":'.json_encode($arr).'}';
- ?>
- </div>
- <div id="tabs-2"> Tahun
- <select name="sweets">
- <option><?php echo date('Y', strtotime('-1 year')); ?></option>
- <option><?php echo date('Y', strtotime('-2 year')); ?></option>
- <option><?php echo date('Y', strtotime('-3 year')); ?></option>
- <option><?php echo date('Y', strtotime('-4 year')); ?></option>
- <option><?php echo date('Y', strtotime('-5 year')); ?></option>
- </select>
- <div id='query_tes'>
- <?php //$ganda = echo "<div id='hh1'></div>";
- //echo "campuran".$ganda;
- ?>
- <div id='hh1'></div>
- </div>
- <script type="text/javascript">
- $("select").change(function(){
- var str = "";
- $("select option:selected").each(function () {
- str += $(this).text() + " ";
- });
- // $("#query_tes").text(str);
- $("#hh1").text(str);
- })
- .change();
- </script>
- </div>
- </div>
- <br>
- <center><span style=" color:#000000; font-size:13px;">Copyright© 2011, Kejaksaan Agung RI</span></center>
- </body>
- </html>
Add Comment
Please, Sign In to add comment