Missing To Write Again

All of sudden a lot of things going back and forth across my mind. And it somehow reviving my interest to write again. When I go to wordpress site and trying to creating a new blog. Undeliberately I found this old blog which is has been a long long long time not updated.

From now on I’ll try to update this maintain this blog again. But with new protocols that I defined. Which are :

  • I’ll use English as my primary language
  • Sometimes I’ll randomly write articles in different languages, but I’ll keep in mind that point 1 is priority
  • My themes will be related about science, knowledge, and of course my personal thought and opinion about something.

What the hell. Let’s start on writing again.

Advertisements

Integrasi CodeIgniter, Adodb, dan PostgreSQL

Apa ya jadinya kalau kehebatan framework CodeIgniter (CI) digabungkan dengan kemudahan pemrograman ala Adodb dan dipasangkan dengan DBMS yang super powerful seperti Postgres. Hmm… pasti maknyuss tuh. Pertanyaannya adalah gimana caranya ya biar tiga mahluk ini bisa saling berkomunikasi dan menjalin hubungan silaturahmi yang erat (bahasa lain integrasi xixixxixi).

Setelah ngutak-atik seharian akhirnya ketemu juga caranya. Bagi yang pengen nyoba bisa berikut caranya :

  • Jika belum memiliki ketiga komponen ini, maka download terlebih dahulu.  CodeIgniter bisa di download disini, Adodb disini, dan PostgreSQL disini
  • Lakukan instalasi CodeIgniter terlebih dahulu. Caranya tinggal extract saja file hasil download tadi ke dalam htdocs webserver. Bagi yang telah memiliki CI bisa melewati langkah ini.
  • Instalasi PostgreSQL. Bagi yang telah memiliki PostgreSQL juga bisa melewati langkah ini.
  • Ekstrak Adodb kedalam direktori CI_DIR/system/application/libraries
  • Lakukan konfigurasi CI agar untuk dapat terkoneksi ke PostgreSQL. Dapat dilakukan dengan memodifikasi file CI_DIR/system/application/config/database.php.
  • Lakukan modifikasi pada file CI_DIR/system/application/config/autoload.php.

$autoload[‘libraries’] = array(‘database’,’adodbloader’);

  • Buat loader untuk Adodbnya. Caranya buat file dengan nama Adodbloader.php di dalam direktori CI_DIR/system/application/libraries (satu direktori dengan hasil ekstrak Adodb). Gunakan code berikut.

<?php if(!defined(‘BASEPATH’)) exit(‘No direct script access allowed’);

class Adodbloader{
function Adodbloader(){
if(!class_exists(‘ADONewConnection’))
require_once(APPPATH.’libraries/adodb/adodb.inc’.EXT);

$obj =& get_instance();
$this->_init_adodb_library($obj);
}

function _init_adodb_library(&$ci){
$db_var = false;
$debug = false;

if(!isset($dsn)){
//using the CI database configuration
include(APPPATH.’config/database’.EXT);
$group = ‘default’;

$dsn = ‘postgres’.’://’.$db[$group][‘username’].’:’.$db[$group][‘password’].’@’.$db[$group][‘hostname’].’/’.$db[$group][‘database’];
}

//creating the instance
$ci->adodb = & ADONewConnection($dsn);

if($db_var){
//set the CI database object to use adodb instance
$ci->db =& $ci->adodb;
}

if($debug){
$ci->adodb->debug = true;
}
}
}

?>

  • Pastikan konfigurasi pg_hba.conf telah mengizinkan untuk menerima koneksi dari localhost ke DBMS PostgreSQL. Jika belum silahkah buka pg_hba.conf kemudian tambahkan baris beikut

host    all         all         127.0.0.1/32          trust

Okay, setelah semuanya selesai saatnya mencoba hasil integrasi ini apakah berhasil atau tidak. Cara paling mudah edit file CI_DIR/system/application/controllers/welcome.php. Modifikasi fungsi indexnya sebagai berikut :

function index(){

$this->adodb->debug=1;
$data=$this->adodb->GetAll(“SELECT field_yang_ingin_di_select FROM nama_tabel”);
print_r($data);
//$this->load->view(‘welcome_message’);
}

Jika hasilnya keluar, berarti konfigurasi yang dilakukan sudah betul. Jika terdapat error maka cermati error tersebut lalu lihat kembali dimana kira-kira yang salah. Untuk lebih jelasnya lihat note dibawah.

Note:

  1. Perhatikan konfigurasi pg_hba.conf, karena apabila ini salah hampir dapat dipastikan anda akan mendapatkan error BSoD (Blank Screen of Death)
  2. Untuk pengisian driver name pada DSN yang terdapat dalam Adodbloader.php gunakan “postgres”.  Ada huruf “s” nya. Karena biasanya kalau menggunakan konfigurasi internal CI nama drivernya “postgre”

Dilema Ayam dan Telur Ayam

Yang manakah yang duluan ada. Ayam atau Telur Ayam ? Sebuah pertanyaan klasik yang sering didengar namun hingga sekarang tidak ada jawaban pastinya. Ayam berasal dari telur, namun telur tidak akan mungkin ada jika tidak ada ayam yang menelurkannya. Lalu manakah yang benar ?

Selama ini banyak orang berspekulasi bahwa telurlah yang dahulu ada, namun kini telah ditemukan bukti secara ilmiah bahwa ayamlah yang dahulu ada. Paling tidak “protein ayam” hehehe…

Ilmuwan Inggris, Professor Mark Rodger dan Dr. David Quigley dari Universitas Warwick telah berhasil mengamati dan mengetahui cara kerja protein yang disebut Ovocledidin-17 (OC-17). Para ilmuwan telah lama mengetahui keberadaan protein ini dalam cangkang telur dan berspekulasi bahwa protein ini memainkan peranan penting dalam proses pembentukan telur.

Untuk menemukan protein ini, para ilmuwan membangun sebuah simulasi pembentukan protein  menggunakan sebuah metode pengolahan data tingkat tinggi yang disebut metadynamics pada super komputer HECToR yang berlokasi di Edinburgh.

Dalam penelitiannya diketahui bahwa OC-17 merupakan sebuah katalis yang berperan dalam proses kristalisasi pembentukan cangkang telur. Protein ini akan merubah kalsium karbonat (CaCO3) menjadi kristal calcite yang merupakan komponen dasar dari cangkang telur. Berdasarkan kalkulasi dari penelitian ini juga diketahui bahwa laju pembentukan cangkang telur adalah sebesar 6 gram per 24 jam.


Karena protein ini diproduksi di dalam tubuh ayam, maka tidak mungkin akan terbentuk telur tanpa adanya ayam terlebih dahulu.

Courtesy: University of Warwick

Chatting Darurat Via DxDiag

Ada kalanya saat kita berada di suatu tempat entah itu kantor, kampus, warnet, or sejenisnya keperluan untuk melakukan komunikasi pribadi sangat diperlukan. Disini kasusnya adalah chatting. Pengalaman pribadi saya, ada banyak kantor yang memberlakukan aturan ketat kepada karyawannya terhadap penggunaan komputer. Misal pembatasan akses terhadap situs-situs chatting, larangan instalasi program Instant Messenger, dll.

Lalu bagaimanakah cara agar tetap bisa berkomunikasi secara diam-diam dengan teman kita ?… Salah satu caranya dengan memanfaatkan tool diagonisitic dxdiag.  Berikut caranya.

  • Buka menu dxdiag dengan cara Start->Run lalu ketikkan dxdiag
  • Pilin tab Network kemudian klik tombol Test DirectPlay
  • Buat server dulu dengan mengisikan parameter yang diminta

  • Tekan ok, kemudian tunggu hingga room terbuat
  • Lakukan hal yang sama di komputer rekan anda, namun ubah pilihan dari Create New Session menjadi Join Existing Session
  • Masukkan IP komputer yang menjadi server

  • Pilih nama chatroom yang tersedia

  • Happy chatting 🙂

Terahertz. Detektor Tembus Pandang Jarak Jauh

Terahertz. Sebuah teknologi yang akan memungkinkan kita untuk mendeteksi dan melihat material apa yang tersimpan dibalik pakaian yang kita kenakan. Bahkan dari jarah ratusan meter jauhnya. Dengan kata lain dengan teknologi ini kita akan memiliki kemampuan melihat tembus pandang dari jarak jauh.

Dengan menggunakan terahertz, detektor akan dapat melihat menembus berbagai material seperti tembok, pakaian, pembungkus paket, dll. Awalnya teknologi ini dikembangkan untuk mendeteksi bahan peledak ataupun obat-obatan.

Yang dimaksud dengan gelombang terahertz adalah bagian dari spektrum elektromagnetik  dengan panjang gelombang antara infrared dan microwave. Hingga saat ini pendeteksian sinyal ini hanya dapat dimungkinkan dari jarak beberapa inci dari sumber gelombang karena apabila jarak semakin jauh, gelombang tersebut akan diserap oleh udara dan menghilang.

“Banyak ilmuwan yang berpendapat bahwa melakukan terahertz sensing adalah “mission impossible” kata seorang Fisikawan Jingle Liu dari Rensselaer Polytechnic Institute.

Liu dan timnya memiliki solusi tersendiri terhadap permasalahan ini, yaitu dengan tidak bergantung terhadap gelombang terahertz itu sendiri. Melainkan mereka menggunakan pantulan  dari laser yang ditembakkan ke arah objek.

Dua buah laser dengan frekuensi yang berbeda diarahkan secara bersamaan kearah target untuk menghasilkan plasma (pada dasarnya plasma adalah udara yang terionisasi). Plasma yang dihasilkan ini akan memancarkan cahaya yang akan tersebar dengan karakteristik yang berbeda-beda berdasarkan radiasi terahertz dari material yang ditembaknya. Refleksi dari cahaya yang dihasilkan plasma inilah yang dapat dideteksi dari jarak jauh.

Para ilmuwan telah melakukan percobaan ini menggunakan berbagai macam materi dan mereka pun telah membuat suatu library terahertz spectra yang di kategorisasikan berdasarkan materi yang diujicobakan. Library ini dapat mempercepat identifikasi materi saat dilakukan terahertz sensing.

Hingga saat ini para ilmuwan telah berhasil mendeteksi sinyal dari jarak lebih dari 30 meter. Jarak ini merupakan jarak ruangan laboratorium yang mereka gunakan. Namun secara teori mereka dapat mendeteksi suatu material jarak ratusan kilometer jauhnya.

Terahertz detektor dapat juga digunakan sebagai pelengkap keamanan airport untuk mendeteksi materi berbahaya/terlarang yang dibawa oleh penumpang dalam pakaian mereka. Setidaknya teknologi ini jauh lebih ramah jika dibandingkan dengan X-Ray karena gelombang terahertz hanya menggunakan sedikit energy. Terahertz juga tidak dapat menembus tubuh manusia karena sinyal terahertz tidak dapat melewati air ataupun metal.

Kata Liu, Secara teoritis remote sensing dengan menggunakan terahertz dapat pula digunakan untuk mengidentifikasi komposisi dari material beracun yang belum diketahui yang tersebar di lingkungan ataupun komposisi dari sebuah objek yang berada di luar angkasa.

Courtesy : Wired

Mengenal Memcached

Alkisah dua orang praktisi IT, seorang Programmer dan Sysadmin sepakat untuk membangun sebuah website. Website yang dibangung adalah website yang menggunakan webserver dan database. User dari seluruh penjuru internet akan berkomunikasi dengan webserver dan meminta webserver untuk menampilkan halaman yang mereka inginkan. Selanjutnya webserver akan meminta kepada database data-data yang dibutuhkan untuk menghasilkan halaman yang dimaksud. Si Programmer bertugas melakukan coding, dan Sysadmin menyiapkan webserver dan database server.

Suatu saat Sysadmin menyadari bahwa database mereka sakit parah. Sistem monitoring terus menerus menampilkan pesan berwarna merah yang akhirnya mereka pun memvonis bahwa database mereka terkena demam tinggi. Beban rata-rata = 20. Kemudian Programmer bertanya pada Sysadmin. “Well, apa yang bisa kita lakukan ?”, Sysadmin menjawab “Sepertinya aku pernah mendengar sebuah program bernama Memcached, konon katanya program inilah yang membantu mengatasi demam pada database server Livejournal”

Si Sysadmin kemudian melirik mesin webservernya, disana terdapat 6 buah mesin yang sedang running dan ia memutuskan menggunakan tiga server untuk menjalankan Memcached server. Sysadmin kemudian menambahkan RAM 1 gigabyte ke setiap server dan mulai menjalankan Memcached dengan batas konfigurasi masing-masing adalah 1 gigabyte. Dengan ini Sysadmin akhirnya memiliki tiga Memcached instance, dimana setiap instance nya dapat menampung data hingga 1 gigabyte.  Kata Programmer dan Sysadmin “Behold the Glorious Memcached” .

Dan sekarang apa yang terjadi ?, Benda ini tidak melakukan apapun. Memcached bahkan tidak berkomunikasi kemanapun dan tidak menampung data sama sekali. Hal terburuk, kini bahkan aktifitas rata-rata database server meningkat menjadi 25!

Tanpa ragu-ragu sang Programmer langsung mengambil manual pecl/memcache client library. RTFM dan RTFM… dan kemudian berkata, Aha, tidak usah takut kawan, aku punya ide. Ia kemudia mencatat alamat IP dan port yang digunakan oleh ketiga Memcached tadi, dan memasukkannya kedalam Array PHP

$MEMCACHE_SERVERS = array(
“10.1.1.1”, //web1
“10.1.1.2”, //web2
“10.1.1.3”, //web3
);

Kemudian ia membuat objek, sebut saja ‘$memcache’

$memcache = new Memcache();
foreach($MEMCACHE_SERVERS as $server){
$memcache->addServer ( $server );
}

Yup, selanjutnya tinggal implementasi. Hmm.. pertama dicoba dimana ya ?. Setelah berpikir sejenak si Programmer berkata “aha, aku tahu”, Kalau tidak salah dihalaman depan website kita itu ada query “SELECT * FROM hugetable WHERE timestamp > lastweek ORDER BY timestamp ASC LIMIT 50000;” dan butuh waktu sekitar 5 detik buat dijalankan. Gimana kalau ini saja dulu yang dicoba dimasukkan kedalam Memcached. Tanpa pikir panjang si Programmer lalu melakukan sedikit modifikasi untuk menggunakan objek $memcache yang telah dibuat sebelumnya. Jadilah potongan code seperti ini

$huge_data_for_frong_page = $memcache->get(“huge_data_for_frong_page”);
if($huge_data_for_frong_page === false){
$huge_data_for_frong_page = array();
$sql = “SELECT * FROM hugetable WHERE timestamp > lastweek ORDER BY timestamp ASC LIMIT 50000”;
$res = mysql_query($sql, $mysql_connection);
while($rec = mysql_fetch_assoc($res)){
$huge_data_for_frong_page[] = $rec;
}
// cache for 10 minutes
$memcache->set(“huge_data_for_frong_page”, $huge_data_for_frong_page, 600);
}

// use $huge_data_for_frong_page how you please

BAM!. Database load tiba-tiba turun menjadi 10! Kecepatan akses ke website juga meningkat. Muncul pertanyaan di benak si Sysadmin. Apa yang sebenarnya terjadi ?, Kok bisa ?. Segera ia mengecek grafik monitoring Memcached. disini ia menggunakan cacti, dan lihat ada trafik mengarah ke salah satu instance Memcached. Hmm.. “tapi bukannya aku menyediakan tiga instance Memcached” pikirnya. Karena penasaran ia kemudian telnet ke port 11211 di setiap instance Memcached dan bertanyalah dia :

Hei, “get huge_data_for_front_page” dirimu ada disitu atau tidak ?

Instance Memcached pertama tidak memberikan respon…

Instance Memcached kedua tidak memberikan respon…

Namun tiba-tiba ada respon dari Instance Memcached ketiga, “Iya gw ada disini, nih buktinya (sambil melakukan dumping data yang dimilikinya ke dalam sesi telnet)”

Nah ini dia datanya, tapi kenapa hanya ada di satu Memcached saja ya ?

Sedikit bingung ia kemudian bertanya kedalam milis namun jawaban yang diterimanya selalu sama “Itu dikarenakan sistem cache terdistribusi (distributed cache)”. Sekarang ia tahu penyebabnya namun masih sedikit bingung dengan maksud dari “distributed cache” tadi. Lalu ia meminta si Programmer untuk melakukan cache beberapa data tambahan. Mari kita pecahkan misteri ini, aku yakin kita bisa.

Oke, selanjutnya ada query yang bisa dibilang masih cukup cepat, tapi query ini dijalankan 100 kali per detiknya. Mungkin ini bisa digunakan. Lalu si Programmer melakukan penyesuaian code untuk menggunakan Memcahed terhadap query ini.

Voila, sekarang database load berkurang menjadi 8!.

Melihat ini si Programmer kemudian melakukan banyak penyesuaian tambahan. Kali ini ia menggunakan teknik baru yang ia temukan di milis dan FAQ. Wohoo.. database load berkurang secara drastis menjadi 7, 5, 3, 2, 1!

Sysadmin terus mengamati grafik lalu lintas data seiring dengan turunnya load ke database. Lihat ketiganya kini bekerja, dan ketiganya mendapatkan request. Ini hebat.

eits, tunggu dulu kalau aku memberikan setiap instance Memcached 1 gigabyte memori, artinya total aku bisa melakukan caching sebanyak 3 gigabyte. Ini luar biasa sekali. Benda ini bisa menghemat pengeluaran kita. Oh Brad Fitzpatrick, I love your ass!

Masalah selanjutnya. Salah satu mesin yang digunakan untuk menjalankan Memcached sudah tua dah membutuhkan perawatan. Mau tidak mau aku harus mematikannya. Kalau demikian apa yang akan terjadi pada cluster Memcached yang sedang berjalan ?. Hmm.. mari dicoba. Kemudian ia mematikan salah satu mesin sembari mengamati grafik lalu lintas data, Owh, load database melonjak. Kini bukan lagi 1 melainkan 2. Tapi sepertinya masih dalam batas toleransi. Toh kedua Memcached masih berjalan. Aku akan cuma kehilangan sedikit data dalam cache, tapi pekerjaan maintenance tetap bisa dilakukan. Not bad lah.

Setelah selesai melakukan maintenance ia kemudian menghidupkan kembali mesin memcache tersebut, dah kini load ke database turun kembali menjadi 1. Hoho… great..

Si Programmer dan Sysadmin akhirnya terus melanjutkan pekerjaannya dalam membangun website, sambil tetap menggunakan teknik cache. Disaat mereka memiliki pertanyaan mereka tak segan-segan bertanya ke milis atau membaca FAQ. Dan mereka pun hidup bahagia seterusnya (apa coba) 😛

Courtesy :
http://code.google.com/p/memcached/wiki/TutorialCachingStory
Author: Dormando via IRC. Edited by Brian Moon for fun. Further fun editing by Emufarmers . Editted and translated again into Indonesian by me.

Another Bloody Hell Query

This is insane…. super ultra long query. Definitely will kill your DBMS in 5 minutes…

select distinct production_year,
production_month,
cust.sales_number,
cust.revision_number,
cust.status,
cust.customer_code_orig,
customer_name,
boffice,
insurance_periode_from_awal,
cust.insurance_period_from,
cust.insurance_period_to,
cust.sales_status,
cast(‘ ‘ as text) as occupation_code,
cast(‘ ‘ as text) as building_category,
cast(‘ ‘ as text) as construction_class,
cast(‘ ‘ as text) as construction_year,
cast(‘ ‘ as text) as jumlah_tingkat,
H_BUILDING,
H_STOCK,
H_MACHINERY,
H_CONTENT,
H_OTHERS,
0.0 as OLD_H_BUILDING,
0.0 as OLD_H_STOCK,
0.0 as OLD_H_MACHINERY,
0.0 as OLD_H_CONTENT,
0.0 as OLD_H_OTHERS,
cust.reference,
obj.location_id,
risk.risk_number,risk_definition,
kel.str_kelurahan_name as kelurahan,
kab.str_kabupaten_name  as kabupaten,
prov.str_province_name as province,
city.str_city_name as city,
country.str_country_name as country,
alm.street_address || ‘. ‘ || coalesce(kel.str_kelurahan_name,’-NO KELURAHAN-‘) || ‘. ‘ || coalesce(city.str_city_name,’-NO CITY-‘) || ‘. ‘ ||  coalesce(kab.str_kabupaten_name,’-NO KABUPATEN-‘) || ‘. ‘ || coalesce(str_province_name,’-NO PROVINCE-‘) || ‘. ‘ || alm.postal_code || ‘. ‘ || COALESCE(country.str_country_name,’-NO COUNTRY-‘) || ‘. ‘ as  obj_location,
alm.postal_code as obj_postal_code,
cust.revision_flag,
cust.insurance_product_code,
insurance_product_name,
cust.coins_premium,
cust.own_share
into temporary tbl_rpt_bppdan_customer
from (
select     period1 as production_year,period2 as production_month,
det.sales_number,det.revision_number,
cast(det.revision_number as numeric) as status,
customer_code_orig,
cco_name as customer_name,
front_office_code_handling as boffice,
cast(null as date) as insurance_periode_from_awal,
insurance_period_from,
insurance_period_to,
sales_status,
coalesce(H_BUILDING,0) as H_BUILDING,
coalesce(H_STOCK,0) as H_STOCK,
coalesce(H_MACHINERY,0) as H_MACHINERY,
coalesce(H_CONTENT,0) as H_CONTENT,
coalesce(H_OTHERS,0) as H_OTHERS,
det.reference,
det.revision_flag,
det.insurance_product_code,
insurance_product_name,
det.coins_premium,
det.own_share
from “tbl_so_sales_details” as det
left join (
select obj.sales_number,obj.revision_number,sum(declared_value) as H_CONTENT
from tbl_so_sales_objects as obj
join “tbl_so_object_attributes”as atr
on atr.sales_number = obj.sales_number and atr.revision_number = obj.revision_number and atr.object_number = obj.object_number and atr.attribute_code = ‘OBJ’ and attribute_value = ‘CNT’
group by obj.sales_number,obj.revision_number
) as cnt on cnt.sales_number = det.sales_number and cnt.revision_number = det.revision_number
left join (
select obj.sales_number,obj.revision_number,sum(declared_value) as H_STOCK
from tbl_so_sales_objects as obj
join “tbl_so_object_attributes”as atr
on atr.sales_number = obj.sales_number and atr.revision_number = obj.revision_number and atr.object_number = obj.object_number and atr.attribute_code = ‘OBJ’ and attribute_value = ‘STK’
group by obj.sales_number,obj.revision_number
) as stk on stk.sales_number = det.sales_number and stk.revision_number = det.revision_number
left join (
select obj.sales_number,obj.revision_number,sum(declared_value) as H_MACHINERY
from tbl_so_sales_objects as obj
join “tbl_so_object_attributes”as atr
on atr.sales_number = obj.sales_number and atr.revision_number = obj.revision_number and atr.object_number = obj.object_number and atr.attribute_code = ‘OBJ’ and attribute_value = ‘MCH’
group by obj.sales_number,obj.revision_number
) as mch on mch.sales_number = det.sales_number and mch.revision_number = det.revision_number
left join (
select obj.sales_number,obj.revision_number,sum(declared_value) as H_BUILDING
from tbl_so_sales_objects as obj
join “tbl_so_object_attributes”as atr
on atr.sales_number = obj.sales_number and atr.revision_number = obj.revision_number and atr.object_number = obj.object_number and atr.attribute_code = ‘OBJ’ and attribute_value = ‘BLD’
group by obj.sales_number,obj.revision_number
) as bld on bld.sales_number = det.sales_number and bld.revision_number = det.revision_number
left join (
select obj.sales_number,obj.revision_number,sum(declared_value)  as H_OTHERS
from tbl_so_sales_objects as obj
join “tbl_so_object_attributes”as atr
on atr.sales_number = obj.sales_number and atr.revision_number = obj.revision_number and atr.object_number = obj.object_number and atr.attribute_code = ‘OBJ’ and attribute_value = ‘OTH’
group by obj.sales_number,obj.revision_number
) as oth on oth.sales_number = det.sales_number and oth.revision_number = det.revision_number
join “tbl_gl_general_jem” as g on split_part(g.reference,’;’,1) = det.sales_number and split_part(g.reference,’;’,2) = det.revision_number
inner join  “tbl_pd_insurance_products” as prod on prod.insurance_product_code = det.insurance_product_code
where g.period1 = ‘2009’ and cast(g.period2 as integer) between ‘4’ and ‘9’ and ttype = ‘Insurance premium’
and det.insurance_product_code in (‘115’)
) as cust
join (select sales_number,revision_number,location_id from “tbl_so_sales_objects” group by sales_number,revision_number,location_id) as obj on obj.sales_number || obj.revision_number = cust.sales_number || cust.revision_number
join (select sales_number,sales_rev_number,location_id,risk_number,risk_definition from “tbl_ra_object_risk_relations” group by sales_number,sales_rev_number,location_id,risk_number,risk_definition)as risk on obj.sales_number = risk.sales_number and obj.revision_number = risk.sales_rev_number  and obj.location_id = risk.location_id
join “tbl_so_sales_locations” as alm on alm.sales_number = obj.sales_number and alm.revision_number = obj.revision_number and alm.location_number = obj.location_id
left join tbl_mstr_kelurahan as kel on kel.num_kelurahan_id = alm.kel_code
left join “tbl_mstr_kabupaten” as kab on kab.num_kabupaten_id = alm.kab_kodya_code and kab.num_province_id = alm.province_code
left join “tbl_mstr_province” as prov on  prov.num_province_id = alm.province_code
left join “tbl_mstr_city” as city on city.num_city_id = alm.kec_code
left join “tbl_mstr_country” as country on country.str_country_code = alm.country_code;

–===========================================================================================

update tbl_rpt_bppdan_customer
set insurance_periode_from_awal = b.insurance_period_from
from “tbl_so_sales_details” as b
where b.sales_number = tbl_rpt_bppdan_customer.sales_number and b.revision_number = ‘000’;

–===========================================================================================

–select set_occupation_code_bppdan(‘Andi’);
update “tbl_rpt_bppdan_customer” set occupation_code = attribute_value
from (
select sales_number,revision_number,product_attribute_name,attribute_value
from tbl_so_object_attributes as atr
join tbl_pd_attributes as pd on pd.product_attribute_code = atr.attribute_code
where product_attribute_name like ‘%occup%’ –and sales_number || revision_number in (select sales_number || revision_number from “tbl_rpt_bppdan_customer”)
group by sales_number,revision_number,product_attribute_name,attribute_value
order by sales_number,revision_number
) as obj
where “tbl_rpt_bppdan_customer”.sales_number = obj.sales_number and “tbl_rpt_bppdan_customer”.revision_number = obj.revision_number;

–===========================================================================================

select set_eq_extention(‘construction_year’);
select set_eq_extention(‘construction_class’);
select set_eq_extention(‘building_category’);
select set_eq_extention(‘jumlah_tingkat’);

–============================================================================================================================

select     gen.boffice,
split_part(gen.reference,’;’,1) as sales_number,
split_part(gen.reference,’;’,2) as revision_number,
split_part(gen.reference,’;’,3) as risk_number,
split_part(gen.reference,’;’,4) as treaty_name,
split_part(gen.reference,’;’,6) as treaty_code,
cid,
cerates,
l.amount,
l.amount2,
gen.temp_no
into temporary tbl_rpt_bppdan_gl_eq
from tbl_gl_general_jem as gen
join tbl_gl_lines_jem as l on l.temp_no = gen.temp_no
where account in (‘6350000′,’6331000′,’6341000′) and split_part(gen.reference,’;’,4) in (‘Indonesian standard earthquake reinsurance (MAIPARK)’,’Indonesian standard earthquake reinsurance (MAIPARK) 09H2′)
and split_part(gen.reference,’;’,1) || ‘;’ || split_part(gen.reference,’;’,2) || ‘;’ || split_part(gen.reference,’;’,3)  in (select  obj.sales_number || ‘;’ || obj.revision_number || ‘;’ || obj.risk_number from “tbl_rpt_bppdan_customer” as obj where insurance_product_code = ‘115’);

–=============================================================================================================================

select     distinct b.risk_number,
b.revision_number,
(
select sum(amount) from tbl_rpt_bppdan_gl_eq as gl where upper(treaty_code) in (select upper(ext_code) from “adm_extention_code” where ext_id = ‘Fire’)
and gl.sales_number = b.sales_number and gl.revision_number = b.revision_number and gl.risk_number = b.risk_number and gl.boffice = b.boffice
) as premium_rate_bppdan_fire,
(
select sum(amount) from tbl_rpt_bppdan_gl_eq as gl where upper(treaty_code) in (select upper(ext_code) from “adm_extention_code” where ext_id = ‘Flood’)
and gl.sales_number = b.sales_number and gl.revision_number = b.revision_number and gl.risk_number = b.risk_number and gl.boffice = b.boffice
) as premium_rate_bppdan_flood,
(
select sum(amount) from tbl_rpt_bppdan_gl_eq as gl where upper(treaty_code) in (select upper(ext_code) from “adm_extention_code” where ext_id = ‘RSMD’)
and gl.sales_number = b.sales_number and gl.revision_number = b.revision_number and gl.risk_number = b.risk_number and gl.boffice = b.boffice
) as premium_rate_bppdan_rsmd,
(
select sum(amount) from tbl_rpt_bppdan_gl_eq as gl where upper(treaty_code) in (select upper(ext_code) from “adm_extention_code” where ext_id = ‘EQ’)
and gl.sales_number = b.sales_number and gl.revision_number = b.revision_number and gl.risk_number = b.risk_number and gl.boffice = b.boffice
) as premium_rate_bppdan_eq,
total_tsi, c.total_premium, prgbi_tsi.cession_amount as prgbi_tsi, prgbi_premium.premium_amount as prgbi_premium,
(
select sum(total_premi)
from(
select risk_number,revision_number,sum(premium_amount) as total_premi
from tbl_ra_risk_premium
where outward_type in (select ext_code from adm_extention_code where ext_id = ‘Fire’)
group by risk_number,revision_number
union
select risk_number,revision_number,sum(premium_amount) as total_premi
from tbl_or_risk_premium
where outward_type in (select ext_code from adm_extention_code where ext_id = ‘Fire’)
group by risk_number,revision_number
) as a
where a.risk_number = b.risk_number –and a.revision_number = cast(b.revision_number as numeric) + 9
) as total_premi_fire,
(
select sum(total_premi)
from(
select risk_number,max(revision_number),sum(premium_amount) as total_premi
from tbl_ra_risk_premium
where outward_type in (select ext_code from adm_extention_code where ext_id = ‘Flood’)
group by risk_number,revision_number
union
select risk_number,max(revision_number),sum(premium_amount) as total_premi
from tbl_or_risk_premium
where outward_type in (select ext_code from adm_extention_code where ext_id = ‘Flood’)
group by risk_number,revision_number
) as a
where a.risk_number = b.risk_number –and a.revision_number = cast(b.revision_number as numeric) + 9
) as total_premi_flood,
(
select sum(total_premi)
from(
select risk_number,max(revision_number),sum(premium_amount) as total_premi
from tbl_ra_risk_premium
where outward_type in (select ext_code from adm_extention_code where ext_id = ‘RSMD’)
group by risk_number,revision_number
union
select risk_number,max(revision_number),sum(premium_amount) as total_premi
from tbl_or_risk_premium
where outward_type in (select ext_code from adm_extention_code where ext_id = ‘RSMD’)
group by risk_number,revision_number
) as a
where a.risk_number = b.risk_number –and a.revision_number = cast(b.revision_number as numeric) + 9
) as total_premi_rsmd,
(
select sum(total_premi)
from(
select risk_number,max(revision_number),sum(premium_amount) as total_premi
from tbl_ra_risk_premium
where outward_type in (select ext_code from adm_extention_code where ext_id = ‘EQ’)
group by risk_number,revision_number
union
select risk_number,max(revision_number),sum(premium_amount) as total_premi
from tbl_or_risk_premium
where outward_type in (select ext_code from adm_extention_code where ext_id = ‘EQ’)
group by risk_number,revision_number
) as a
where a.risk_number = b.risk_number –and a.revision_number = cast(b.revision_number as numeric) + 9
) as total_premi_eq
into temporary tbl_rpt_bppdan_detail_eq
from tbl_rpt_bppdan_customer as b
left join (
select risk_number,max(revision_number) as revision_number–,base_sum as total_tsi
from tbl_ra_risk_cession
group by risk_number
) as link on link.risk_number = b.risk_number
join (
select distinct risk_number,revision_number,base_sum as total_tsi
from “tbl_ra_risk_cession”
) as a on a.risk_number = b.risk_number and a.revision_number = link.revision_number
left join (
select risk_number,max(revision_number) as revision_number –,base_premium as total_premium
from “tbl_ra_risk_premium”
group by risk_number
) as link_b on link_b.risk_number = b.risk_number
join (
select distinct risk_number,revision_number,base_premium as total_premium
from “tbl_ra_risk_premium”
) as c on c.risk_number = b.risk_number and c.revision_number = link_b.revision_number
join (
select risk_number,revision_number,cession_amount
from “tbl_ra_risk_cession”
where outward_name in (‘Indonesian standard earthquake reinsurance (MAIPARK)’,’Indonesian standard earthquake reinsurance (MAIPARK) 09H2′)
) as prgbi_tsi on prgbi_tsi.risk_number = b.risk_number and prgbi_tsi.revision_number = link.revision_number
join (
select risk_number,revision_number,premium_amount
from “tbl_ra_risk_premium”
where outward_name in (‘Indonesian standard earthquake reinsurance (MAIPARK)’,’Indonesian standard earthquake reinsurance (MAIPARK) 09H2′)
) prgbi_premium on prgbi_premium.risk_number = b.risk_number and prgbi_premium.revision_number = link_b.revision_number
where b.insurance_product_code = ‘115’;

–Download Report
–=============================================================================================================================

select      cust.sales_number,
cust.revision_number,
–obj.object_number,
yr.outward_year,
line.period2 as produksi,
cust.customer_name,
cust.insurance_periode_from_awal,
cust.insurance_period_from,
cust.insurance_period_to,
cust.insurance_product_code,
cust.insurance_product_name,
cust.revision_flag as policy_status,
cust.occupation_code,
cust.reference,
cust.risk_number,
cust.revision_flag,
–declared_value_new as tsi_new,
–declared_value_old as tsi_old,
prgbi_tsi,
prgbi_premium,
OLD_H_BUILDING,
OLD_H_STOCK,
OLD_H_MACHINERY,
OLD_H_CONTENT,
OLD_H_OTHERS,
H_BUILDING,
H_STOCK,
H_MACHINERY,
H_CONTENT,
H_OTHERS,
–obj.interest_name,
cust.obj_location,
cust.city,
cust.obj_postal_code,
cust.location_id,
cust.building_category,
cust.construction_class,
cust.construction_year,
cust.jumlah_tingkat,
line.cerates as kurs,
line.cid as currency,
cust.own_share,
cust.coins_premium,
cust.sales_status,
total_premi as premi100,
0.025 * total_premi as premi25
from tbl_rpt_bppdan_customer as cust
join (
select     risk_number,total_premium as total_premi,case coalesce(total_premium,0) when 0 then 0 else round((total_premium/total_tsi),6) end as total_rate,
total_premi_fire,total_premi_flood,total_premi_rsmd,total_premi_eq,total_tsi,
premium_rate_bppdan_fire,premium_rate_bppdan_flood,premium_rate_bppdan_rsmd,premium_rate_bppdan_eq,
prgbi_tsi,prgbi_premium
from tbl_rpt_bppdan_detail_eq
) as det on det.risk_number = cust.risk_number
join (
select risk_number, outward_year
from “tbl_ra_risk_cession”
group by risk_number,outward_year
) as yr on yr.risk_number = cust.risk_number
inner join  (
select      cid,
cerates,period2,period1,
split_part(foo.reference,’;’,1) as gl_sales_number,
split_part(foo.reference,’;’,2) as gl_revision_number,
split_part(foo.reference,’;’,3) as gl_risk_number,
sum(amount) as gl_premi
from(
select     gen.reference,cerates,period2,period1,
cid,
amount
from (
select gen.reference,cid,temp_no,cerates,period2,period1
from “tbl_gl_general_jem” as gen
where substring(temp_no,1,3) = ‘OTP’
) as gen
join (
select temp_no,amount
from “tbl_gl_lines_jem”
where  account in (‘6350000′,’6331000′,’6341000′)
) as foo on foo.temp_no = gen.temp_no
) as foo
group by cid,split_part(foo.reference,’;’,1),split_part(foo.reference,’;’,2),split_part(foo.reference,’;’,3),cerates,period2,period1
) as line on gl_sales_number = cust.sales_number and gl_revision_number = cust.revision_number and gl_risk_number = cust.risk_number and line.period1 = cust.production_year and line.period2 = cust.production_month
–where total_premi notnull and total_premi <> 0
where cust.insurance_product_code = ‘115’
order by cust.sales_number,cust.revision_number

Ini satu query lho… @_@

select distinct production_year,
production_month,
cust.sales_number,
cust.revision_number,
cust.status,
cust.customer_code_orig,
customer_name,
boffice,
insurance_periode_from_awal,
cust.insurance_period_from,
cust.insurance_period_to,
cust.sales_status,
cast(‘ ‘ as text) as occupation_code,
cast(‘ ‘ as text) as building_category,
cast(‘ ‘ as text) as construction_class,
cast(‘ ‘ as text) as construction_year,
cast(‘ ‘ as text) as jumlah_tingkat,
H_BUILDING,
H_STOCK,
H_MACHINERY,
H_CONTENT,
H_OTHERS,
0.0 as OLD_H_BUILDING,
0.0 as OLD_H_STOCK,
0.0 as OLD_H_MACHINERY,
0.0 as OLD_H_CONTENT,
0.0 as OLD_H_OTHERS,
cust.reference,
obj.location_id,
risk.risk_number,risk_definition,
kel.str_kelurahan_name as kelurahan,
kab.str_kabupaten_name  as kabupaten,
prov.str_province_name as province,
city.str_city_name as city,
country.str_country_name as country,
alm.street_address || ‘. ‘ || coalesce(kel.str_kelurahan_name,’-NO KELURAHAN-‘) || ‘. ‘ || coalesce(city.str_city_name,’-NO CITY-‘) || ‘. ‘ ||  coalesce(kab.str_kabupaten_name,’-NO KABUPATEN-‘) || ‘. ‘ || coalesce(str_province_name,’-NO PROVINCE-‘) || ‘. ‘ || alm.postal_code || ‘. ‘ || COALESCE(country.str_country_name,’-NO COUNTRY-‘) || ‘. ‘ as  obj_location,
alm.postal_code as obj_postal_code,
cust.revision_flag,
cust.insurance_product_code,
insurance_product_name,
cust.coins_premium,
cust.own_share
into temporary tbl_rpt_bppdan_customer
from (
select     period1 as production_year,period2 as production_month,
det.sales_number,det.revision_number,
cast(det.revision_number as numeric) as status,
customer_code_orig,
cco_name as customer_name,
front_office_code_handling as boffice,
cast(null as date) as insurance_periode_from_awal,
insurance_period_from,
insurance_period_to,
sales_status,
coalesce(H_BUILDING,0) as H_BUILDING,
coalesce(H_STOCK,0) as H_STOCK,
coalesce(H_MACHINERY,0) as H_MACHINERY,
coalesce(H_CONTENT,0) as H_CONTENT,
coalesce(H_OTHERS,0) as H_OTHERS,
det.reference,
det.revision_flag,
det.insurance_product_code,
insurance_product_name,
det.coins_premium,
det.own_share
from “tbl_so_sales_details” as det
left join (
select obj.sales_number,obj.revision_number,sum(declared_value) as H_CONTENT
from tbl_so_sales_objects as obj
join “tbl_so_object_attributes”as atr
on atr.sales_number = obj.sales_number and atr.revision_number = obj.revision_number and atr.object_number = obj.object_number and atr.attribute_code = ‘OBJ’ and attribute_value = ‘CNT’
group by obj.sales_number,obj.revision_number
) as cnt on cnt.sales_number = det.sales_number and cnt.revision_number = det.revision_number
left join (
select obj.sales_number,obj.revision_number,sum(declared_value) as H_STOCK
from tbl_so_sales_objects as obj
join “tbl_so_object_attributes”as atr
on atr.sales_number = obj.sales_number and atr.revision_number = obj.revision_number and atr.object_number = obj.object_number and atr.attribute_code = ‘OBJ’ and attribute_value = ‘STK’
group by obj.sales_number,obj.revision_number
) as stk on stk.sales_number = det.sales_number and stk.revision_number = det.revision_number
left join (
select obj.sales_number,obj.revision_number,sum(declared_value) as H_MACHINERY
from tbl_so_sales_objects as obj
join “tbl_so_object_attributes”as atr
on atr.sales_number = obj.sales_number and atr.revision_number = obj.revision_number and atr.object_number = obj.object_number and atr.attribute_code = ‘OBJ’ and attribute_value = ‘MCH’
group by obj.sales_number,obj.revision_number
) as mch on mch.sales_number = det.sales_number and mch.revision_number = det.revision_number
left join (
select obj.sales_number,obj.revision_number,sum(declared_value) as H_BUILDING
from tbl_so_sales_objects as obj
join “tbl_so_object_attributes”as atr
on atr.sales_number = obj.sales_number and atr.revision_number = obj.revision_number and atr.object_number = obj.object_number and atr.attribute_code = ‘OBJ’ and attribute_value = ‘BLD’
group by obj.sales_number,obj.revision_number
) as bld on bld.sales_number = det.sales_number and bld.revision_number = det.revision_number
left join (
select obj.sales_number,obj.revision_number,sum(declared_value)  as H_OTHERS
from tbl_so_sales_objects as obj
join “tbl_so_object_attributes”as atr
on atr.sales_number = obj.sales_number and atr.revision_number = obj.revision_number and atr.object_number = obj.object_number and atr.attribute_code = ‘OBJ’ and attribute_value = ‘OTH’
group by obj.sales_number,obj.revision_number
) as oth on oth.sales_number = det.sales_number and oth.revision_number = det.revision_number
join “tbl_gl_general_jem” as g on split_part(g.reference,’;’,1) = det.sales_number and split_part(g.reference,’;’,2) = det.revision_number
inner join  “tbl_pd_insurance_products” as prod on prod.insurance_product_code = det.insurance_product_code
where g.period1 = ‘2009’ and cast(g.period2 as integer) between ‘4’ and ‘9’ and ttype = ‘Insurance premium’
and det.insurance_product_code in (‘115’)
) as cust
join (select sales_number,revision_number,location_id from “tbl_so_sales_objects” group by sales_number,revision_number,location_id) as obj on obj.sales_number || obj.revision_number = cust.sales_number || cust.revision_number
join (select sales_number,sales_rev_number,location_id,risk_number,risk_definition from “tbl_ra_object_risk_relations” group by sales_number,sales_rev_number,location_id,risk_number,risk_definition)as risk on obj.sales_number = risk.sales_number and obj.revision_number = risk.sales_rev_number  and obj.location_id = risk.location_id
join “tbl_so_sales_locations” as alm on alm.sales_number = obj.sales_number and alm.revision_number = obj.revision_number and alm.location_number = obj.location_id
left join tbl_mstr_kelurahan as kel on kel.num_kelurahan_id = alm.kel_code
left join “tbl_mstr_kabupaten” as kab on kab.num_kabupaten_id = alm.kab_kodya_code and kab.num_province_id = alm.province_code
left join “tbl_mstr_province” as prov on  prov.num_province_id = alm.province_code
left join “tbl_mstr_city” as city on city.num_city_id = alm.kec_code
left join “tbl_mstr_country” as country on country.str_country_code = alm.country_code;

–===========================================================================================

update tbl_rpt_bppdan_customer
set insurance_periode_from_awal = b.insurance_period_from
from “tbl_so_sales_details” as b
where b.sales_number = tbl_rpt_bppdan_customer.sales_number and b.revision_number = ‘000’;

–===========================================================================================

–select set_occupation_code_bppdan(‘Andi’);
update “tbl_rpt_bppdan_customer” set occupation_code = attribute_value
from (
select sales_number,revision_number,product_attribute_name,attribute_value
from tbl_so_object_attributes as atr
join tbl_pd_attributes as pd on pd.product_attribute_code = atr.attribute_code
where product_attribute_name like ‘%occup%’ –and sales_number || revision_number in (select sales_number || revision_number from “tbl_rpt_bppdan_customer”)
group by sales_number,revision_number,product_attribute_name,attribute_value
order by sales_number,revision_number
) as obj
where “tbl_rpt_bppdan_customer”.sales_number = obj.sales_number and “tbl_rpt_bppdan_customer”.revision_number = obj.revision_number;

–===========================================================================================

select set_eq_extention(‘construction_year’);
select set_eq_extention(‘construction_class’);
select set_eq_extention(‘building_category’);
select set_eq_extention(‘jumlah_tingkat’);

–============================================================================================================================

select     gen.boffice,
split_part(gen.reference,’;’,1) as sales_number,
split_part(gen.reference,’;’,2) as revision_number,
split_part(gen.reference,’;’,3) as risk_number,
split_part(gen.reference,’;’,4) as treaty_name,
split_part(gen.reference,’;’,6) as treaty_code,
cid,
cerates,
l.amount,
l.amount2,
gen.temp_no
into temporary tbl_rpt_bppdan_gl_eq
from tbl_gl_general_jem as gen
join tbl_gl_lines_jem as l on l.temp_no = gen.temp_no
where account in (‘6350000′,’6331000′,’6341000′) and split_part(gen.reference,’;’,4) in (‘Indonesian standard earthquake reinsurance (MAIPARK)’,’Indonesian standard earthquake reinsurance (MAIPARK) 09H2′)
and split_part(gen.reference,’;’,1) || ‘;’ || split_part(gen.reference,’;’,2) || ‘;’ || split_part(gen.reference,’;’,3)  in (select  obj.sales_number || ‘;’ || obj.revision_number || ‘;’ || obj.risk_number from “tbl_rpt_bppdan_customer” as obj where insurance_product_code = ‘115’);

–=============================================================================================================================

select     distinct b.risk_number,
b.revision_number,
(
select sum(amount) from tbl_rpt_bppdan_gl_eq as gl where upper(treaty_code) in (select upper(ext_code) from “adm_extention_code” where ext_id = ‘Fire’)
and gl.sales_number = b.sales_number and gl.revision_number = b.revision_number and gl.risk_number = b.risk_number and gl.boffice = b.boffice
) as premium_rate_bppdan_fire,
(
select sum(amount) from tbl_rpt_bppdan_gl_eq as gl where upper(treaty_code) in (select upper(ext_code) from “adm_extention_code” where ext_id = ‘Flood’)
and gl.sales_number = b.sales_number and gl.revision_number = b.revision_number and gl.risk_number = b.risk_number and gl.boffice = b.boffice
) as premium_rate_bppdan_flood,
(
select sum(amount) from tbl_rpt_bppdan_gl_eq as gl where upper(treaty_code) in (select upper(ext_code) from “adm_extention_code” where ext_id = ‘RSMD’)
and gl.sales_number = b.sales_number and gl.revision_number = b.revision_number and gl.risk_number = b.risk_number and gl.boffice = b.boffice
) as premium_rate_bppdan_rsmd,
(
select sum(amount) from tbl_rpt_bppdan_gl_eq as gl where upper(treaty_code) in (select upper(ext_code) from “adm_extention_code” where ext_id = ‘EQ’)
and gl.sales_number = b.sales_number and gl.revision_number = b.revision_number and gl.risk_number = b.risk_number and gl.boffice = b.boffice
) as premium_rate_bppdan_eq,
total_tsi, c.total_premium, prgbi_tsi.cession_amount as prgbi_tsi, prgbi_premium.premium_amount as prgbi_premium,
(
select sum(total_premi)
from(
select risk_number,revision_number,sum(premium_amount) as total_premi
from tbl_ra_risk_premium
where outward_type in (select ext_code from adm_extention_code where ext_id = ‘Fire’)
group by risk_number,revision_number
union
select risk_number,revision_number,sum(premium_amount) as total_premi
from tbl_or_risk_premium
where outward_type in (select ext_code from adm_extention_code where ext_id = ‘Fire’)
group by risk_number,revision_number
) as a
where a.risk_number = b.risk_number –and a.revision_number = cast(b.revision_number as numeric) + 9
) as total_premi_fire,
(
select sum(total_premi)
from(
select risk_number,max(revision_number),sum(premium_amount) as total_premi
from tbl_ra_risk_premium
where outward_type in (select ext_code from adm_extention_code where ext_id = ‘Flood’)
group by risk_number,revision_number
union
select risk_number,max(revision_number),sum(premium_amount) as total_premi
from tbl_or_risk_premium
where outward_type in (select ext_code from adm_extention_code where ext_id = ‘Flood’)
group by risk_number,revision_number
) as a
where a.risk_number = b.risk_number –and a.revision_number = cast(b.revision_number as numeric) + 9
) as total_premi_flood,
(
select sum(total_premi)
from(
select risk_number,max(revision_number),sum(premium_amount) as total_premi
from tbl_ra_risk_premium
where outward_type in (select ext_code from adm_extention_code where ext_id = ‘RSMD’)
group by risk_number,revision_number
union
select risk_number,max(revision_number),sum(premium_amount) as total_premi
from tbl_or_risk_premium
where outward_type in (select ext_code from adm_extention_code where ext_id = ‘RSMD’)
group by risk_number,revision_number
) as a
where a.risk_number = b.risk_number –and a.revision_number = cast(b.revision_number as numeric) + 9
) as total_premi_rsmd,
(
select sum(total_premi)
from(
select risk_number,max(revision_number),sum(premium_amount) as total_premi
from tbl_ra_risk_premium
where outward_type in (select ext_code from adm_extention_code where ext_id = ‘EQ’)
group by risk_number,revision_number
union
select risk_number,max(revision_number),sum(premium_amount) as total_premi
from tbl_or_risk_premium
where outward_type in (select ext_code from adm_extention_code where ext_id = ‘EQ’)
group by risk_number,revision_number
) as a
where a.risk_number = b.risk_number –and a.revision_number = cast(b.revision_number as numeric) + 9
) as total_premi_eq
into temporary tbl_rpt_bppdan_detail_eq
from tbl_rpt_bppdan_customer as b
left join (
select risk_number,max(revision_number) as revision_number–,base_sum as total_tsi
from tbl_ra_risk_cession
group by risk_number
) as link on link.risk_number = b.risk_number
join (
select distinct risk_number,revision_number,base_sum as total_tsi
from “tbl_ra_risk_cession”
) as a on a.risk_number = b.risk_number and a.revision_number = link.revision_number
left join (
select risk_number,max(revision_number) as revision_number –,base_premium as total_premium
from “tbl_ra_risk_premium”
group by risk_number
) as link_b on link_b.risk_number = b.risk_number
join (
select distinct risk_number,revision_number,base_premium as total_premium
from “tbl_ra_risk_premium”
) as c on c.risk_number = b.risk_number and c.revision_number = link_b.revision_number
join (
select risk_number,revision_number,cession_amount
from “tbl_ra_risk_cession”
where outward_name in (‘Indonesian standard earthquake reinsurance (MAIPARK)’,’Indonesian standard earthquake reinsurance (MAIPARK) 09H2′)
) as prgbi_tsi on prgbi_tsi.risk_number = b.risk_number and prgbi_tsi.revision_number = link.revision_number
join (
select risk_number,revision_number,premium_amount
from “tbl_ra_risk_premium”
where outward_name in (‘Indonesian standard earthquake reinsurance (MAIPARK)’,’Indonesian standard earthquake reinsurance (MAIPARK) 09H2′)
) prgbi_premium on prgbi_premium.risk_number = b.risk_number and prgbi_premium.revision_number = link_b.revision_number
where b.insurance_product_code = ‘115’;

–Download Report
–=============================================================================================================================

select      cust.sales_number,
cust.revision_number,
–obj.object_number,
yr.outward_year,
line.period2 as produksi,
cust.customer_name,
cust.insurance_periode_from_awal,
cust.insurance_period_from,
cust.insurance_period_to,
cust.insurance_product_code,
cust.insurance_product_name,
cust.revision_flag as policy_status,
cust.occupation_code,
cust.reference,
cust.risk_number,
cust.revision_flag,
–declared_value_new as tsi_new,
–declared_value_old as tsi_old,
prgbi_tsi,
prgbi_premium,
OLD_H_BUILDING,
OLD_H_STOCK,
OLD_H_MACHINERY,
OLD_H_CONTENT,
OLD_H_OTHERS,
H_BUILDING,
H_STOCK,
H_MACHINERY,
H_CONTENT,
H_OTHERS,
–obj.interest_name,
cust.obj_location,
cust.city,
cust.obj_postal_code,
cust.location_id,
cust.building_category,
cust.construction_class,
cust.construction_year,
cust.jumlah_tingkat,
line.cerates as kurs,
line.cid as currency,
cust.own_share,
cust.coins_premium,
cust.sales_status,
total_premi as premi100,
0.025 * total_premi as premi25
from tbl_rpt_bppdan_customer as cust
join (
select     risk_number,total_premium as total_premi,case coalesce(total_premium,0) when 0 then 0 else round((total_premium/total_tsi),6) end as total_rate,
total_premi_fire,total_premi_flood,total_premi_rsmd,total_premi_eq,total_tsi,
premium_rate_bppdan_fire,premium_rate_bppdan_flood,premium_rate_bppdan_rsmd,premium_rate_bppdan_eq,
prgbi_tsi,prgbi_premium
from tbl_rpt_bppdan_detail_eq
) as det on det.risk_number = cust.risk_number
join (
select risk_number, outward_year
from “tbl_ra_risk_cession”
group by risk_number,outward_year
) as yr on yr.risk_number = cust.risk_number
inner join  (
select      cid,
cerates,period2,period1,
split_part(foo.reference,’;’,1) as gl_sales_number,
split_part(foo.reference,’;’,2) as gl_revision_number,
split_part(foo.reference,’;’,3) as gl_risk_number,
sum(amount) as gl_premi
from(
select     gen.reference,cerates,period2,period1,
cid,
amount
from (
select gen.reference,cid,temp_no,cerates,period2,period1
from “tbl_gl_general_jem” as gen
where substring(temp_no,1,3) = ‘OTP’
) as gen
join (
select temp_no,amount
from “tbl_gl_lines_jem”
where  account in (‘6350000′,’6331000′,’6341000′)
) as foo on foo.temp_no = gen.temp_no
) as foo
group by cid,split_part(foo.reference,’;’,1),split_part(foo.reference,’;’,2),split_part(foo.reference,’;’,3),cerates,period2,period1
) as line on gl_sales_number = cust.sales_number and gl_revision_number = cust.revision_number and gl_risk_number = cust.risk_number and line.period1 = cust.production_year and line.period2 = cust.production_month
–where total_premi notnull and total_premi <> 0
where cust.insurance_product_code = ‘115’
order by cust.sales_number,cust.revision_number

Naruto Belajar “Genkidama (Bola Semangat)”

Dapet dari blog sebelah, lucu sih, ini aku coba bikin versi bahasa indonesianya.

Lucu juga kayanya ya ngebayangin Naruto kalau bergabung dalam keluarga super saiya. Oke, ceritanya dimulai disaat Naruto datang ke rumah Songoku. Ia ingin sekali mempelajadi jurus Genkidama (Bola Semangat) dari Songoku. Kita tahu bersama bukan, kalau Naruto sangan ingin sekali menjadi lebih kuat dari sasuke, makanya dia nyari strategi baru buat nanti kalau ngelawan sasuke, nah salah satunya dengan berguru ama Songoku ini. Padahal sebelomnya si Naruto udah nguasain jutsu baru (Rasens Shuriken) + Sage Chakra. Tapi tetep aja sepertinya Naruto masih belom PD dengan kemampuan yang dipunyain sekarang.

Akankan Songoku menerima Naruto menjadi bagian dari keluarga super saiya ???

Sebelum Songoku menerima Naruto sebagai anggota keluarga saiya dan mengajarinya Genkidama, Songoku bertanya pada anggota keluarganya, Apa yang harus kulakukan kepada Naruto ini ?, terlihat bahwa Vegeta dan Songohan tidak ingin begitu saja menerima Naruto. Tidak lama kemudian Vegeta dan Songohan menunjukkan kekuatan super saiya kepada Naruto, tubuh keduanya penuh dilapisi oleh energi keemasan. Lalu bertanyalah keduanya kepada Naruto, “apakah kau bisa melakukan seperti yang kami lakukan ini ?”. Tanpa berpikir panjang Naruto kemudian mengeluarkan Chakra Kyuubi yang tersegel di dalam tubuhnya dan tak lama kemudian tubuh Naruto pun dipenuhi oleh cahaya keemasan, sama seperti yang dimiliki para Super Saiya.

Para Saiya pun terkesan dengan kemampuan Naruto dan akhirnya menerimanya sebagai anggota keluarga Saiya. Selanjutnya sesuai permintaan Naruto, Songoku mengajaknya ke suatu tempat dimana para Saiya biasa berlatih. Yaitu sebuah ruangan dimana 1 hari di dalam ruangan itu sama dengan 1 tahun. Setelah melewati 3 hari di dalam ruangan tersebut, akhirnya Naruto berhasil mempelajari Genkidama dari Songoku.

Naruto telah menjadi kuat hanya dalam 3 hari. Tidak hanya itu, Ia bukan saja hanya menguasai Genkidama. Dengan kemampuannya memanipulasi chakra ditambah dengan kemampuan chakra sage yang dimilikinya, Ia kemudian memodifikasi Genkidama yang diajarkan oleh Songoku menjadi jurus baru. Jurus ini diberi nama RASENDAMA (Rasengan and Genkidama).

Tidak hanya jurus Rasendama, perhatikan rambut Naruto. Kini penampilan Naruto pun benar-benar seperti Super Saiya.

Translated from:

http://mangamovie-review.blogspot.com/2008/09/naruto-learn-genkidama.html

Credit goes to original poster.

Query Macam Apa Ini ?

Query yang gw ketemuin tadi siang pas lagi bersih-bersih database. Ini cuman “satu query” lho. Bloody hell… liatnya aja udah eneg -_-!. Kayanya ini sementara bakalan menjadi query terpanjang yang gw temuin semenjak in charge di permbuatan sistem 2010 NextG. Kali aja besok-besok dapet query yang lebih puanjaaaang lagi… hohoho….

SELECT t.sales_number, sum(t.badan_pusat_pengelolaan_data_asuransi_nasional) AS badan_pusat_pengelolaan_data_asuransi_nasional, sum(t.combined_non_marine_surplus_treaty) AS combined_non_marine_surplus_treaty, sum(t.fire_statistical_cession_reinsurance_treaty__bppdan) AS fire_statistical_cession_reinsurance_treaty__bppdan, sum(t.indonesian_standard_earthquake_reinsurance__maipark) AS indonesian_standard_earthquake_reinsurance__maipark, sum(t.pt_asuransi_maipark_indonesia) AS pt_asuransi_maipark_indonesia, sum(t.non_marine_surplus_reinsurance_agreement) AS non_marine_surplus_reinsurance_agreement, sum(t.non_marine_surplus_reinsurance_agreement__extention_of_2008) AS non_marine_surplus_reinsurance_agreement__extention_of_2008, sum(t.marine_cargo_excess_of_loss_reinsurance_contract) AS marine_cargo_excess_of_loss_reinsurance_contract, sum(t.marinecargoxolreinsurancecontractextentionof2008) AS marinecargoxolreinsurancecontractextentionof2008, sum(t.non_marine_quota_share_reinsurance_agreement) AS non_marine_quota_share_reinsurance_agreement, sum(t.non_marine_quota_share_reinsurance_agreement__extention_of_2007) AS non_marine_quota_share_reinsurance_agreement__extention_of_2007, sum(t.non_marine_quota_share_reinsurance_agreement__extention_of_2008) AS non_marine_quota_share_reinsurance_agreement__extention_of_2008, sum(t.riotsstrikesandmaliciousdamageandnaturalperilsxolra) AS riotsstrikesandmaliciousdamageandnaturalperilsxolra, sum(t.riotsstrikesandmaliciousdamageandnaturalperilsxolraextof2008) AS riotsstrikesandmaliciousdamageandnaturalperilsxolraextof2008, sum(t.riotsstrikesandmaliciousdamagexolra) AS riotsstrikesandmaliciousdamagexolra, sum(t.riotsstrikesandmaliciousdamagexolramainlayer) AS riotsstrikesandmaliciousdamagexolramainlayer, sum(t.riotsstrikesandmaliciousdamagexolramainlayerextentionof2008) AS riotsstrikesandmaliciousdamagexolramainlayerextentionof2008, sum(t.riskandcatastrophexolra) AS riskandcatastrophexolra, sum(t.riskandcatastrophexolraextentionof2008) AS riskandcatastrophexolraextentionof2008, sum(t.rsmdandnaturalperilsxolra) AS rsmdandnaturalperilsxolra, sum(t.terrorismandsabotagexolrasublayer) AS terrorismandsabotagexolrasublayer FROM (SELECT DISTINCT “maintenance”.reassraw.sales_number, “maintenance”.reassraw.treaty_type, “maintenance”.reassraw.treaty_name, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Badan Pusat Pengelolaan Data Asuransi Nasional’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END AS badan_pusat_pengelolaan_data_asuransi_nasional, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Combined non marine surplus treaty’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END AS combined_non_marine_surplus_treaty, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Fire statistical cession reinsurance treaty (BPPDAN)’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END AS fire_statistical_cession_reinsurance_treaty__bppdan, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Indonesian standard earthquake reinsurance (MAIPARK)’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END AS indonesian_standard_earthquake_reinsurance__maipark, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘PT. Asuransi MAIPARK Indonesia’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END AS pt_asuransi_maipark_indonesia, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Non Marine Surplus Reinsurance Agreement’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END AS non_marine_surplus_reinsurance_agreement, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Non Marine Surplus Reinsurance Agreement (Extention of 2008)’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END AS non_marine_surplus_reinsurance_agreement__extention_of_2008, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Marine Cargo Excess of Loss Reinsurance Contract’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END AS marine_cargo_excess_of_loss_reinsurance_contract, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Marine Cargo Excess of Loss Reinsurance Contract (Extention of 2008)’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END AS marinecargoxolreinsurancecontractextentionof2008, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Non Marine Quota Share Reinsurance Agreement’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END AS non_marine_quota_share_reinsurance_agreement, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Non Marine Quota Share Reinsurance Agreement ( Extention of 2007)’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END AS non_marine_quota_share_reinsurance_agreement__extention_of_2007, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Non Marine Quota Share Reinsurance Agreement (Extention of 2008)’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END AS non_marine_quota_share_reinsurance_agreement__extention_of_2008, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Riots, Strikes and Malicious Damage and Natural Perils Excess of Loss Reinsurance Agreement’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END AS riotsstrikesandmaliciousdamageandnaturalperilsxolra, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Riots, Strikes and Malicious Damage and Natural Perils Excess of Loss Reinsurance Agreement (Extention of 2008)’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END AS riotsstrikesandmaliciousdamageandnaturalperilsxolraextof2008, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Riots, Strikes and Malicious Damage Excess of Loss Reinsurance Agreement’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END AS riotsstrikesandmaliciousdamagexolra, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Riots, Strikes and Malicious Damage Excess of Loss Reinsurance Agreement (main layer)’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END AS riotsstrikesandmaliciousdamagexolramainlayer, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Riots, Strikes and Malicious Damage Excess of Loss Reinsurance Agreement (main layer) (Extention of 2008)’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END AS riotsstrikesandmaliciousdamagexolramainlayerextentionof2008, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Risk and Catastrophe Excess of Loss Reinsurance Agreement’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END AS riskandcatastrophexolra, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Risk and Catastrophe Excess of Loss Reinsurance Agreement (Extention of 2008)’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END AS riskandcatastrophexolraextentionof2008, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘RSMD and Natural Perils Excess of Loss Reinsurance Agreement’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END AS rsmdandnaturalperilsxolra, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Terrorism and Sabotage Excess of Loss Reinsurance Agreement (sub layer)’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END AS terrorismandsabotagexolrasublayer FROM “maintenance”.reassraw ORDER BY “maintenance”.reassraw.sales_number, “maintenance”.reassraw.treaty_type, “maintenance”.reassraw.treaty_name, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Badan Pusat Pengelolaan Data Asuransi Nasional’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Combined non marine surplus treaty’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Fire statistical cession reinsurance treaty (BPPDAN)’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Indonesian standard earthquake reinsurance (MAIPARK)’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘PT. Asuransi MAIPARK Indonesia’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Non Marine Surplus Reinsurance Agreement’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Non Marine Surplus Reinsurance Agreement (Extention of 2008)’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Marine Cargo Excess of Loss Reinsurance Contract’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Marine Cargo Excess of Loss Reinsurance Contract (Extention of 2008)’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Non Marine Quota Share Reinsurance Agreement’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Non Marine Quota Share Reinsurance Agreement ( Extention of 2007)’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Non Marine Quota Share Reinsurance Agreement (Extention of 2008)’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Riots, Strikes and Malicious Damage and Natural Perils Excess of Loss Reinsurance Agreement’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Riots, Strikes and Malicious Damage and Natural Perils Excess of Loss Reinsurance Agreement (Extention of 2008)’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Riots, Strikes and Malicious Damage Excess of Loss Reinsurance Agreement’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Riots, Strikes and Malicious Damage Excess of Loss Reinsurance Agreement (main layer)’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Riots, Strikes and Malicious Damage Excess of Loss Reinsurance Agreement (main layer) (Extention of 2008)’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Risk and Catastrophe Excess of Loss Reinsurance Agreement’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Risk and Catastrophe Excess of Loss Reinsurance Agreement (Extention of 2008)’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘RSMD and Natural Perils Excess of Loss Reinsurance Agreement’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END, CASE WHEN (“maintenance”.reassraw.treaty_name = ‘Terrorism and Sabotage Excess of Loss Reinsurance Agreement (sub layer)’::text) THEN “maintenance”.reassraw.a_rp ELSE (0)::numeric END) t GROUP BY t.sales_number;

Kirim Nama ke Planet Mars

Satu lagi event yang menurutku spektakuler. “Kirim nama ke planet Mars”. hehehe…

Event ini diselenggarakan NASA dalam rangka rencana peluncuran Mars Rover yang ke-4 dengan nama “Curiosity”. Peluncuran rover ini direncanakan akan dilakukan di tahun 2011.  Melalui event ini NASA akan menampung nama-nama kita dan menyimpannya kedalam sebuah microchip, yang kemudian akan disertakan dalam modul curiosity lander.

Bagi yang pengen ikutan, silahkan langsung menuju TKP sajah…

Yu, ikut serta ambil bagian dari sejarah. Jadikan namamu nama pertama yang sampai ke planet Mars 🙂