Query Dewo Program 2010NextG…Posted by gilang on May 16th, 2008

SELECT result.dcode, result.dname, result.name, result.lcid, SUM(result.product_pl_1+result.product_pl_2+product_pl_3+product_pl_4) AS production, SUM((result.product_pl_1+result.product_pl_2+product_pl_3+product_pl_4) - ((result.premi_treaty+result.premi_facultative+result.premi_xol) - (result.comisi_treaty+result.comisi_facultative))) AS or,  SUM(result.premi_xol) AS premi_xol, SUM(result.premi_treaty) AS premi_treaty, SUM(result.comisi_treaty) AS comisi_treaty, SUM(result.premi_facultative) AS premi_facultative, SUM(result.comisi_facultative) AS comisi_facultative FROM (SELECT gd.dcode, gd.dname, bo.name, ggj.lcid, (CASE WHEN ggj.ttype=’Reinsurance inward facultative proportional premium’ THEN (SELECT COALESCE(SUM( CASE WHEN glj.balance<>git.balance THEN glj.amount2*-1 ELSE glj.amount2 END )) FROM tbl_gl_lines_jem glj, tbl_gl_integration_transaction_types git WHERE git.origin=’Premium revenue’ AND git.ttype=ggj.ttype AND glj.account=git.account AND glj.sercat=gd.dcode AND glj.temp_no=ggj.temp_no ) ELSE 0 END) AS product_pl_1, (CASE WHEN ggj.ttype=’Insurance premium’ THEN (SELECT COALESCE(SUM( CASE WHEN glj.balance<>git.balance THEN glj.amount2*-1 ELSE glj.amount2 END )) FROM tbl_gl_lines_jem glj, tbl_gl_integration_transaction_types git WHERE git.origin=’Premium revenue’ AND git.ttype=ggj.ttype AND glj.account=git.account AND glj.sercat=gd.dcode AND glj.temp_no=ggj.temp_no ) ELSE 0 END) AS product_pl_2, (CASE WHEN ggj.ttype=’Co-insurance in premium’ THEN (SELECT COALESCE(SUM( CASE WHEN glj.balance<>git.balance THEN glj.amount2*-1 ELSE glj.amount2 END )) FROM tbl_gl_lines_jem glj, tbl_gl_integration_transaction_types git WHERE git.origin=’Premium revenue’ AND git.ttype=ggj.ttype AND glj.account=git.account AND glj.sercat=gd.dcode AND glj.temp_no=ggj.temp_no ) ELSE 0 END) AS product_pl_3, (CASE WHEN ggj.ttype=’Co-insurance out premium’ THEN (SELECT COALESCE(SUM( CASE WHEN glj.balance<>git.balance THEN glj.amount2*-1 ELSE glj.amount2 END )) FROM tbl_gl_lines_jem glj, tbl_gl_integration_transaction_types git WHERE git.origin=’Premium revenue’ AND git.ttype=ggj.ttype AND glj.account=git.account AND glj.sercat=gd.dcode AND glj.temp_no=ggj.temp_no ) ELSE 0 END) AS product_pl_4, (CASE WHEN ggj.ttype=’Reinsurance outward treaty non proportional premium’ THEN (SELECT COALESCE(SUM( CASE WHEN glj.balance<>git.balance THEN glj.amount2*-1 ELSE glj.amount2 END )) FROM tbl_gl_lines_jem glj, tbl_gl_integration_transaction_types git WHERE git.origin=’Premium expense’ AND git.ttype=ggj.ttype AND glj.account=git.account AND glj.sercat=gd.dcode AND glj.temp_no=ggj.temp_no ) ELSE 0 END) AS premi_xol, (CASE WHEN ggj.ttype=’Reinsurance outward treaty proportional premium’ THEN (SELECT COALESCE(SUM( CASE WHEN glj.balance<>git.balance THEN glj.amount2*-1 ELSE glj.amount2 END )) FROM tbl_gl_lines_jem glj, tbl_gl_integration_transaction_types git WHERE git.origin=’Premium expense’ AND git.ttype=ggj.ttype AND glj.account=git.account AND glj.sercat=gd.dcode AND glj.temp_no=ggj.temp_no ) ELSE 0 END) AS premi_treaty, (CASE WHEN ggj.ttype=’Reinsurance outward treaty proportional premium’ THEN (SELECT COALESCE(SUM( CASE WHEN glj.balance<>git.balance THEN glj.amount2*-1 ELSE glj.amount2 END )) FROM tbl_gl_lines_jem glj, tbl_gl_integration_transaction_types git WHERE git.origin=’Commission revenue’ AND git.ttype=ggj.ttype AND glj.account=git.account AND glj.sercat=gd.dcode AND glj.temp_no=ggj.temp_no ) ELSE 0 END) AS comisi_treaty, (CASE WHEN ggj.ttype=’Reinsurance outward facultative proportional premium’ THEN (SELECT COALESCE(SUM( CASE WHEN glj.balance<>git.balance THEN glj.amount2*-1 ELSE glj.amount2 END )) FROM tbl_gl_lines_jem glj, tbl_gl_integration_transaction_types git WHERE git.origin=’Premium expense’ AND git.ttype=ggj.ttype AND glj.account=git.account AND glj.sercat=gd.dcode AND glj.temp_no=ggj.temp_no ) ELSE 0 END) AS premi_facultative, (CASE WHEN ggj.ttype=’Reinsurance outward facultative proportional premium’ THEN (SELECT COALESCE(SUM( CASE WHEN glj.balance<>git.balance THEN glj.amount2*-1 ELSE glj.amount2 END )) FROM tbl_gl_lines_jem glj, tbl_gl_integration_transaction_types git WHERE git.origin=’Commission revenue’ AND git.ttype=ggj.ttype AND glj.account=git.account AND glj.sercat=gd.dcode AND gd.dcode != ” AND gd.dname != ” AND glj.temp_no=ggj.temp_no ) ELSE 0 END) AS comisi_facultative FROM tbl_gl_general_jem ggj, tbl_gl_dimensions gd, adm_back_office bo, tbl_gl_lines_jem gljx WHERE ggj.ttype IN (’Reinsurance inward facultative proportional premium’,'Insurance premium’,'Co-insurance in premium’,'Co-insurance out premium’,'Reinsurance outward treaty proportional premium’,'Reinsurance outward facultative proportional premium’,'Reinsurance outward treaty non proportional premium’) AND gljx.sercat=gd.dcode AND gljx.temp_no=ggj.temp_no AND bo.code=ggj.boffice AND ggj.jentry IS NOT NULL AND gd.dimtype=’Dimension type 1′ AND gd.active=’t’ AND entry=’t’ AND ((ggj.period1||ggj.period2)::text::integer>=(?||?)::text::integer AND (ggj.period1||ggj.period2)::text::integer<=(?||?)::text::integer) GROUP BY gd.dcode, gd.dname, bo.name, ggj.lcid, ggj.ttype, ggj.temp_no ORDER BY gd.dcode ) result GROUP BY result.dcode, result.dname, result.name, result.lcid <>0 ORDER BY result.dcode

Query di atas adalah salah satu dari ratusan query super gila lainnya. yah di tempat kerja gw yang baru, gw ditugaskan untuk ngebenerin suatu program yang konon program ini adalah program asuransi tercanggih yang belum ada dan belum pernah diterapkan di perusahaan asuransi manapun hingga sekarang. Program yang memiliki code name “2010NextG” ini dibangun dengan menggunakan PHP dan PostgreSQL sebagai DBMS nya. Menurut gw konsep pemrograman yang digunakan aplikasi ini cukup brilian, karena lebih menekankan pada penggunaan bahasa Query ketimbang scripting PHP.Hanya sayangnnya penggunaan query nya pada berantakan, yang susah jadinya ya yang nerusin… kaya gw ini misalnya… but.. whatever lah, berkat query-query dewo macam ginian, gw rasa ilmu SQL gua jauh meningkat pesat ketimbang sebelum gw masuk kerja disini…

Filed under: Daily | 6 Comments