Bayu Kurniawan R

This is script that export Oracle Database, archive it automatically and delete archive automatically where archive older than 20 day

####################
# Oracle Backup #
# Bayu Kurniawan R #
# 17 April 2008 #
####################

# Buat environment
PATH=$PATH:$HOME/bin
export PATH
unset USERNAME
ORACLE_HOME=/opt/oracle/product/9ir2/
ORACLE_BASE=/opt/oracle
ORACLE_SID=orcl
export ORACLE_HOME ORACLE_BASE ORACLE_SID
PATH=$PATH:$HOME/bin:$ORACLE_HOME:$ORACLE_HOME/bin

# File naming : year.month.day
tanggal=$(date +%y%m%d)

# Path to save file
dir=/home/bayu

# Daily Backup DB Oracle
echo Exporting DB Oracle ….
su - oracle -c “exp user/password@orcl file=$dir/file_$tanggal.dmp log=$dir/file_$tanggal.log”

echo Archiving Oracle Backup…
tar -zcf $dir/file_$tanggal.tar.gz $dir/file_$tanggal.dmp $dir/file_$tanggal.log

echo Removing Oracle .dmp and .log…
rm $dir/file_$tanggal.dmp $dir/file_$tanggal.log

echo Removing Dump Oracle which older than 20 day…
find /home/bayu -name file*tar.gz -mtime +20 -exec rm -f {} \

# End #

Save it as backup.sh

Edit the Crontab
crontab -e

Add following line

0 22 * * * /opt/oracle/product/9ir2/bin/backup.sh

This script will execute backup.sh everyday at 22.00

Share on Facebook

5 comments

Google Maps API

Google Maps API menyajikan Google Maps di halaman web dengan JavaScript. API menyediakan sejumlah utility untuk memanipulasi peta (seperti pada http://maps.google.com) dan menambahkan content pada peta melalui bermacam layanan, memungkinkan untuk membuat aplikasi peta yang sempurna di website.

Gimana sih memulainya :

Contoh Syntaks HTML (jangan lupa ubah key dan settingan lainnya) :

<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Strict//EN”"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd”><!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Strict//EN”
“http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd”>
<html xmlns=“http://www.w3.org/1999/xhtml”>
<head>
<meta http-equiv=“Content-Type” content=“text/html; charset=iso-8859-1″ />
<title>Bayu Kurniawan R Google Maps</title>
<script src=“http://maps.google.com/maps?file=api&v=2&key=ABQIAAAAbta1OAo0jfu19DOUPNVgsBS-Yhw0H7RwDq-hPQhHch723wUepBTSSnNF-1G3nDm-wpXKKMpVQHzIdg” type=“text/javascript”></script>
<script type=“text/javascript”>

//<![CDATA[

function load() {
if (GBrowserIsCompatible()) {
var map = new GMap2(document.getElementById("map"));
map.addControl(new GLargeMapControl());
map.addControl(new GMapTypeControl());
map.setCenter(new GLatLng(-6.211551, 106.883926), 13);
map.setMapType(G_SATELLITE_MAP);
}
}

//]]>
</script>
</head>
<body onload=“load()” onunload=“GUnload()”>
<div id=“map” style=“width: 800px; height: 600px“></div></body>
</html>

Hasilnya bisa dilihat di http://map.bkurniawan.com/

Share on Facebook

1 comment

Compile PostgreSQL/PostGIS

PostgreSQL/PostGIS

PostGIS (http://postgis.refractions.net)

PostGIS merupakan plugin untuk database PostgreSQL yang berfungsi untuk menyimpan dan melakukan analisis data geospasial. Fungsi dan kegunaannya sama dengan SDE (Spatial Data Engine) ESRI dan Oracle Spasial.

Untuk menginstall database PostgreSQL/PostGIS terlebih dahulu kita harus mengcompile komponen pendukungnya yaitu : PROJ dan GEOS.

Proj

Proj4 (http://proj.maptools.org) – Cartographic Projections Library

Library untuk proyeksi peta, menyediakan lebih dari 100 macam proyeksi.

Geos

GEOS (http://geos.refractions.net) – Geometry Engine Open source

Library untuk topology data vektor.

Compile Proj

Buka console/terminal untuk memulai installasi

Pindah ke direktori dimana file aplikasi disimpan

Ekstract file PROJ dengan mengetikkan

tar -xvf proj-[versi aplikasi].tar.gz

Pindah ke dalam folder aplikasi proj dengan mengetikkan perintah

cd proj-[versi aplikasi]/

Compile aplikasi dengan mengetikkan perintah

./configure

make

make install

Compile Geos

Buka console/terminal untuk memulai installasi

Pindah ke direktori dimana file aplikasi disimpan

Ekstract file Geos dengan mengetikkan

tar -xvf geos-[versi aplikasi].tar.gz

Pindah ke dalam folder aplikasi Geos dengan mengetikkan perintah

cd geos-[versi aplikasi]/

Compile aplikasi dengan mengetikkan perintah

./configure

make

make install

Tambahkan baris /usr/local/lib pada file /etc/ld.so.conf

/sbin/ldconfig

Compile PostgreSQL

Buka console/terminal untuk memulai installasi

Pindah ke direktori dimana file aplikasi disimpan

Ekstract file PostgreSQL dengan mengetikkan

tar -xvf postgresql-[versi aplikasi].tar.gz

Pindah ke dalam folder aplikasi PostgreSQL dengan mengetikkan perintah

cd postgresql-[versi aplikasi]/

Compile aplikasi dengan mengetikkan perintah

./configure
gmake
su
gmake install
adduser postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &
/usr/local/pgsql/bin/createdb test
/usr/local/pgsql/bin/psql test

/usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data

createlang plpgsql [yourdatabase]
psql -d [yourdatabase] -f lwpostgis.sql
psql -d [yourdatabase] -f spatial_ref_sys.sql

Share on Facebook

6 comments

Mapserver dengan menggunakan data Oracle Spatial

Konversi data ESRI Shapefile (.shp) ke Oracle Spatial

D:\BAYU\data\fix\shp>shp2sdo

shp2sdo - Shapefile(r) To Oracle Spatial Converter

Version 2.13 24-Apr-2002

For use with Oracle Spatial.

Input shapefile (no extension): kecamatan

Shape file kecamatan.shp contains 1555 polygons

Output table [kecamatan]:

Output data model [O]:

Geometry column [GEOM]:

ID column []: id

Starting number [1]:

Use a spatial reference system ID (SRID) ? [N]: y

Please enter an SRID value: 4326

Change tolerance value from the default (0.00000005) ? [N]:

Generate data inside control files ? [N]:

Target database Oracle8i? [N]:

Spatial Data requires more than 6 digits precision? [N]:

Bounds: X=[108.594556,114.556928] Y=[-8.776988,-6.398623]

Override ? [N]:

Processing shapefile kecamatan into spatial table KECAMATAN

Data model is object-relational

Geometry column is GEOM

Id column is ID

Numbered from 1

Points stored in SDO_POINT attributes

Data is in a separate file(s)

Control file generation for Oracle9i or higher

Spatial data loaded with 6 digits of precision

SRID set to 4326

Conversion complete : 1555 polygons processed

The following files have been created:

kecamatan.sql : SQL script to create the table

kecamatan.ctl : Control file for loading the table

kecamatan.dat : Data file

Buat table di Oracle dari hasil konversi (.sql)

– kecamatan.sql

–

– This script creates the spatial table.

–

– Execute this script before attempting to use SQL*Loader

– to load the kecamatan.ctl file.

–

– This script will also populate the USER_SDO_GEOM_METADATA table.

– Loading the .ctl file will populate the KECAMATAN table.

–

– To load the .ctl file, run SQL*Loader

– with these parameters:

– USERID=username/password CONTROL=kecamatan.ctl

–

– After the data is loaded in the KECAMATAN table, you should

– tune the dataset and create the spatial index

–

– Creation Date : Sat Dec 22 18:09:02 2007

–

DROP TABLE KECAMATAN;

CREATE TABLE KECAMATAN (

ID NUMBER(38)

PRIMARY KEY,

KECAMATAN VARCHAR2(50),

DISTRICT VARCHAR2(50),

PROVINCE VARCHAR2(50),

J_RW VARCHAR2(2),

J_RT VARCHAR2(3),

J_KK NUMBER,

J_LAKI2 NUMBER,

J_PEREM NUMBER,

P_BBM VARCHAR2(7),

K_LOGAM NUMBER,

K_GERABAH NUMBER,

MAKANAN NUMBER,

KEDAI NUMBER,

SMB_PENGHA VARCHAR2(15),

LETAKGEOGR VARCHAR2(13),

KELOMPOK NUMBER,

KODE_KECAM NUMBER,

KODE_KABUP NUMBER,

KODE_PROPI NUMBER,

GEOM MDSYS.SDO_GEOMETRY);

DELETE FROM USER_SDO_GEOM_METADATA

WHERE TABLE_NAME = ‘KECAMATAN’ AND COLUMN_NAME = ‘GEOM’ ;

INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)

VALUES (’KECAMATAN’, ‘GEOM’,

MDSYS.SDO_DIM_ARRAY

(MDSYS.SDO_DIM_ELEMENT(’X’, 108.594555840, 114.556928040, 0.000000050),

MDSYS.SDO_DIM_ELEMENT(’Y’, -8.776988010, -6.398622990, 0.000000050)

),4326

);

COMMIT;

Insert data menggunakan sql loader

D:\BAYU\data\fix\shp>sqlldr user/password

control = kecamatan

SQL*Loader: Release 10.2.0.1.0 - Production on Sat Dec 22 18:10:44 2007

Commit point reached - logical record count 41

Commit point reached - logical record count 82

Commit point reached - logical record count 123

Commit point reached - logical record count 164

Commit point reached - logical record count 205

Commit point reached - logical record count 246

Commit point reached - logical record count 287

Commit point reached - logical record count 328

Commit point reached - logical record count 369

Commit point reached - logical record count 410

Commit point reached - logical record count 451

Commit point reached - logical record count 492

Commit point reached - logical record count 533

Commit point reached - logical record count 574

Commit point reached - logical record count 615

Commit point reached - logical record count 656

Commit point reached - logical record count 697

Commit point reached - logical record count 738

Commit point reached - logical record count 779

Commit point reached - logical record count 820

Commit point reached - logical record count 861

Commit point reached - logical record count 875

Commit point reached - logical record count 916

Commit point reached - logical record count 957

Commit point reached - logical record count 998

Commit point reached - logical record count 1039

Commit point reached - logical record count 1080

Commit point reached - logical record count 1121

Commit point reached - logical record count 1162

Commit point reached - logical record count 1165

Commit point reached - logical record count 1206

Commit point reached - logical record count 1247

Commit point reached - logical record count 1288

Commit point reached - logical record count 1329

Commit point reached - logical record count 1370

Commit point reached - logical record count 1411

Commit point reached - logical record count 1452

Commit point reached - logical record count 1493

Commit point reached - logical record count 1534

Commit point reached - logical record count 1554

Commit point reached - logical record count 1555

Copy libmap.dll dari D:\ms4w\Apache\cgi-bin\ignored-libmap\oracle10g\ ke D:\ms4w\Apache\cgi-bin

Edit httpd.conf, tambahkan baris

SetEnv TNS_ADMIN “D:/oracle/product/10.2.0/client_2/NETWORK/ADMIN/”

Folder where the tnsnames.ora exist

SetEnv PATH “D:/oracle/product/10.2.0/client_2/BIN/”

SetEnv ORACLE_HOME “D:/oracle/product/10.2.0/client_2?

Pada mapfile :

CONNECTIONTYPE oraclespatial

CONNECTION “username/password@service”

DATA “GEOM FROM (SELECT ID, KECAMATAN, DISTRICT, PROVINCE, GEOM FROM KECAMATAN) USING UNIQUE ID SRID 4326?

Kalo pake OGR, koneksinya ya begini :

CONNECTIONTYPE OGR

CONNECTION “OCI:username/password@service”

DATA “tablename”

Jadi deh….

Share on Facebook

5 comments

php code : generate excel using mysql and php

just put the header in your php code

<?php
header(”Content-Type: application/vnd.ms-excel”);
header(”Expires: 0″);
header(”Cache-Control: must-revalidate, post-check=0, pre-check=0″);
header(”content-disposition: attachment;filename=form_k01.xls”);

// Make a MySQL Connection
mysql_connect(”172.27.1.245″, “root”, “”) or die(mysql_error());
mysql_select_db(”lp2tk”) or die(mysql_error());

// Get all the data from the “example” table
$result = mysql_query(”SELECT * FROM dat_k01_detail where id_dat=”.$_GET['id_dat'])
or die(mysql_error());

echo “<table border=’1′>”;
echo “<TR><TH rowspan=3>NO.</TH><TH rowspan=3>JABATAN PENGAWAS<BR> KETENAGAKERJAAN</TH><TH colspan=14>JUMLAH</TH><TH rowspan=3>KETERANGAN</TH></TR>
<TR><TH rowspan=2>UMUM</TH><TH colspan=11>SPESIALIS</TH><TH rowspan=2>PPNS</TH><TH rowspan=2>JUMLAH</TH></TR>
<TR><TH>1</TH><TH>2</TH><TH>3</TH><TH>4</TH><TH>5</TH><TH>6</TH><TH>7</TH><TH>8</TH><TH>9</TH><TH>10</TH><TH>11</TH></TR>”;

// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo “<tr><td>”;
echo $row['nomor'];
echo “</td><td>”;
echo $row['jabatan'];
echo “</td><td>”;
echo $row['umum'];
echo “</td><td>”;
echo $row['sp1'];
echo “</td><td>”;
echo $row['sp2'];
echo “</td><td>”;
echo $row['sp3'];
echo “</td><td>”;
echo $row['sp4'];
echo “</td><td>”;
echo $row['sp5'];
echo “</td><td>”;
echo $row['sp6'];
echo “</td><td>”;
echo $row['sp7'];
echo “</td><td>”;
echo $row['sp8'];
echo “</td><td>”;
echo $row['sp9'];
echo “</td><td>”;
echo $row['sp10'];
echo “</td><td>”;
echo $row['sp11'];
echo “</td><td>”;
echo $row['ppns'];
echo “</td><td>”;
echo $row['jumlah'];
echo “</td><td>”;
echo $row['keterangan'];
echo “</td></tr>”;

}
/*
$sum = mysql_query(”SELECT SUM(umum) AS sum_umum,SUM(sp1) AS sum_1,SUM(sp2) AS sum_2,SUM(sp3) AS sum_3,SUM(sp4) AS sum_4,SUM(sp5) AS sum_5,SUM(sp6) AS sum_6,SUM(sp7) AS sum_7,SUM(sp8) AS sum_8,SUM(sp9) AS sum_9,SUM(sp10) AS sum_10,SUM(sp11) AS sum_11,SUM(ppns) AS sum_ppns,SUM(jumlah) AS sum_jumlah FROM dat_k01_detail”);

while($row = mysql_fetch_array( $sum )) {
echo “<tr><td>”;
echo “”;
echo “</td><td>”;
echo “Jumlah”;
echo “</td><td>”;
echo $row['sum_umum'];
echo “</td><td>”;
echo $row['sum_1'];
echo “</td><td>”;
echo $row['sum_2'];
echo “</td><td>”;
echo $row['sum_3'];
echo “</td><td>”;
echo $row['sum_4'];
echo “</td><td>”;
echo $row['sum_5'];
echo “</td><td>”;
echo $row['sum_6'];
echo “</td><td>”;
echo $row['sum_7'];
echo “</td><td>”;
echo $row['sum_8'];
echo “</td><td>”;
echo $row['sum_9'];
echo “</td><td>”;
echo $row['sum_10'];
echo “</td><td>”;
echo $row['sum_11'];
echo “</td><td>”;
echo $row['sum_ppns'];
echo “</td><td>”;
echo $row['sum_jumlah'];
echo “</td><td>”;
echo “”;
echo “</td></tr>”;
}
*/
echo “</table>”;
?>

Share on Facebook

3 comments

php code : generate pdf using mysql and php

From http://www.ros.co.nz/pdf/

<?php
// test the table functions
error_reporting(E_ALL);
include(’class.ezpdf.php’);
$pdf =& new Cezpdf(’a4′,’portrait’);
$pdf->selectFont(’./fonts/Helvetica’);
$pdf->ezSetCmMargins(1.5,2,1.5,1.5);

$pdf->addText(370,790,8,”LAMPIRAN I : Keputusan Menteri Tenaga Kerja dan”,0);
$pdf->addText(422,780,8,”Transmigrasi Republik Indonesia”,0);
$pdf->addText(422,770,8,”Nomor : PER.09/MEN/V/2005″,0);
$pdf->addText(422,760,8,”Tanggal : 25-5-2005″,0);
$pdf->line(422,758,555,758);
$pdf->ezSetDy(-50, ‘makeSpace’);

$pdf->ezText(’DATA PEGAWAI PENGAWAS KETENAGAKERJAAN’,10, array(’justification’ => ‘center’));

//————————————————–
// you will have to change these to your settings
$host = ‘172.27.1.28′;
$user = ‘root’;
$password = ’server’;
$database = ‘lp2tk’;
$query = ’select * from dat_k01_detail’;
//————————————————–
// open the connection to the db server
$link = mysql_connect($host,$user,$password);
// change to the right database
mysql_select_db($database);
// initialize the array
$data = array();
// do the SQL query
$result = mysql_query($query);
// step through the result set, populating the array, note that this could also have been written:
// while($data[] = mysql_fetch_assoc($result)) {}
while($data[] = mysql_fetch_array($result, MYSQL_ASSOC)) {}
// make the table

$pdf->ezText(’Triwulan’, 8);
$pdf->ezText(’Tahun’, 8);
$pdf->ezText(’Kab/Kota’, 8);
$pdf->ezText(’Kode’, 8);
$pdf->addText(80,729,8,”:”,0);
$pdf->addText(80,719,8,”:”,0);
$pdf->addText(80,710,8,”:”,0);
$pdf->addText(80,701,8,”: K1″,0);

$pdf->ezSetDy(-10, ‘makeSpace’);
$pdf->ezTable($data,array(’nomor’=>’NO.’, ‘jabatan’=>’JABATAN PENGAWAS KETENAGAKERJAAN’, ‘umum’=>’UMUM’, ’sp1′=>’1′, ’sp2′=>’2′, ’sp3′=>’3′, ’sp4′=>’4′, ’sp5′=>’5′, ’sp6′=>’6′, ’sp7′=>’7′, ’sp8′=>’8′, ’sp9′=>’9′, ’sp10′=>’10′, ’sp11′=>’11′, ‘ppns’=>’PPNS’, ‘jumlah’=>’JUMLAH’, ‘keterangan’=>’KETERANGAN’)
,”, array(’innerLineThickness’ => 1,’outerLineThickness’ => 1.2,’showHeadings’=>1,’shaded’=>1,’showLines’=>1, ‘xPos’=>’center’ ,’xOrientation’=>’center’, ‘width’=>535));
$pdf->ezSetDy(-10, ‘makeSpace’);

$pdf->ezText(’<b>Keterangan Kolom Spesialis</b>
1 = Pesawat Uang Bejana Tekan
2 = Pesawat Angkat Angkut
3 = Pesawat Tenaga dan Produksi
4 = Instalasi Listrik, Lift
5 = Penanggulangan Kebakaran
6 = Konstruksi Bangunan
‘,8, array(’justification’ => ‘left’));

$pdf->addText(200,570,8,”7 = Kesehatan Kerja”,0);
$pdf->addText(200,561,8,”8 = Lingkungan Kerja”,0);
$pdf->addText(200,552,8,”9 = Jamsostek”,0);
$pdf->addText(200,543,8,”10 = Pengupahan dan Waktu Kerja”,0);
$pdf->addText(200,534,8,”11 = Keselamatan Kerja Kimia”,0);

$pdf->addText(395,570,8,”(Wilayah/Daerah, Tanggal, Bulan, Tahun)”,0);
$pdf->addText(375,561,8,”Kepala Instansi atau Kepala Unit Kerja Pengawasan”,0);
$pdf->addText(410,552,8,”Ketenagakerjaan Kabupaten/Kota”,0);
$pdf->addText(435,530,8,”(……………………….)”,0);

//$pdf->ezTable($data,$cols,”, array(’xPos’=>90,’xOrientation’=>’right’,'width’=>300 ,’cols’=>array(’nomor’=>array(’justification’=>’right’),’jabatan’=>array(’width’=>400))));
// do the output, this is my standard testing output code, adding ?d=1
// to the url puts the pdf code to the screen in raw form, good for checking
// for parse errors before you actually try to generate the pdf file.
if (isset($d) && $d){
$pdfcode = $pdf->output(1);
$pdfcode = str_replace(”\n”,”\n<br>”,htmlspecialchars($pdfcode));
echo ‘<html><body>’;
echo trim($pdfcode);
echo ‘</body></html>’;
} else {
$pdf->stream();
$pdf->ezStartPageNumbers(30,50,20,”,”,1);
$pdf->ezStopPageNumbers();
}
ezNewPage()
?>

ros.pdf