Oracle Magazine March 2013

superadmin April 19, 2014 No Comments
Oracle Magazine March 2013

Cloud Documentaries | In this issue of Oracle Magazine, real organizations use Oracle public and private cloud solutions—including Oracle Cloud, Oracle Database, and Oracle Enterprise Manager 12c—to put their own customers first, integrate cloud and legacy information systems, move their standards-based cloud solutions between different cloud service providers, deploy solutions in Oracle Cloud and private cloud environments, and more. See “Utility Computing in the Cloud” (page 24) and “Banking on a Private Cloud” (page 30) in this issue to learn more about how three organizations use and benefit from Oracle cloud solutions.

The Oracle Cloud and Oracle private cloud solutions featured in this issue also rely on Oracle engineered systems, including Oracle Exadata and Oracle Exalogic. And because these are real-world stories, no mainframes of any kind were used in the successful deployment of and access

In movies and television shows, information technology is often scaled down to fit the story. That magically computed result, that missing electronic file, and that unknown password that will stop something bad or start something good can go from nonexistent to resolved in one brief scene. And for historical reasons, I suppose, the significant computing power in most screen fiction seems to come from a vintage mainframe— rather than a modern supercomputer or engineered system.

I haven’t seen too many references to cloud computing in movies or on television, but those that I have seen seem limited to the idea that a treasured file or information the hero or villain may be looking for is safe from local mayhem because it is safely stored “in the cloud.” Fortunately, the fact that a movie or television show delivers a very narrow definition of cloud computing as internet-located storage isn’t likely to break the on-screen story or reduce the entertainment value of a production.

Cloud Business Stories

At the same time, the fact that enterprise cloud computing is much bigger than internet storage isn’t guaranteed to give cloud a bigger or more detailed definition on the big and small screens. To start, enterprise cloud computing is about the infrastructure, platform, and application services deployed in public, private, and hybrid clouds that support the business. That big beginning could also be part of why it’s easier in a movie plot to point to the internet and call it the cloud.

But if a movie wanted to show what enterprise cloud computing means for business, it would have to go big. A comprehensive look at cloud would have to include the aforementioned themes of infrastructure, platform, and application services along with cloud management, security, integration, development, social computing, mobility, business intelligence, big data, and more. And a discussion about potential cloud challenges would include siloed information, control and visibility, data privacy and regulatory compliance, proprietary architectures, and vendor lock-in.

Download Magazine Here.

Oracle Magazine January 2013

superadmin April 19, 2014 No Comments
Oracle Magazine January 2013

This issue of Oracle Magazine presents and celebrates the winners in all of the 2012 Oracle Excellence Awards programs, including the Oracle Excellence Awards for CIO of the Year, Technologist of the Year, and Database Administrator of the Year.

Managing nominations, judging, trophy design and delivery, events, publicity, and more across 12 awards programs is no small collection of responsibilities. Oracle Global Customer Programs oversees the 12 Oracle Excellence Awards programs, coordinates many of these activities, and deserves much congratulations and thanks for all of its efforts. The judging processes vary across the different awards categories, but several programs — including Technologist of the Year — use Oracle user group leaders as judges. Thanks to these community leaders for volunteering their time and expertise. Thanks also to the nominators, designers, and everyone else who worked behind the scenes.

Finally, on behalf of Oracle Magazine, congratulations and thanks to all of the Oracle Excellence Awards winners.

Download magazine here.

Aplikasi Inventory Sederhana – Desain User Interface

superadmin March 19, 2014 No Comments
Aplikasi Inventory Sederhana Desain User Interface Visual Inheritance BiruniLabs

Desain User Interface | Untuk memudahkan penggunaan aplikasi atau program yang kita buat, makan user interface atau antarmuka pengguna haruslah cukup intuitif dan konsisten. Misalkan sebuah aplikasi inventory pastinya akan mempunya form untuk menampilkan daftar gudang, daftar barang, daftar transaksi barang dan sebagainya. Form-form yang memiliki tujuan serupa harus memiliki layout yang sama, dalam artian memiliki posisi button yang sama, posisi grid juga sama, dan sebagainya. Untuk itu kita bisa saja membuat beberapa form dan menambahkan control ke dalamnya satu persatu, kemudian kita ulangi kembali langkah2 tersebut untuk semua form yang diperlukan…. Sungguh kerja keras yang luar biasa 🙂 …. Namun sebagai programmer kita harus juga kerja cerdas. Di sinilah Visual Inheritance akan membantu kita mempermudah hal tersebut.

Artikel ini adalah lanjutan dari artikel berseri tentang Tutorial Membuat Aplikasi Inventory Sederhana Menggunakan VB.net dan Database MySQL. 

Masih ingat dengan konsep Inheritance di OOP? Dengan menggunakan inheritance kita bisa membuat sebuah class yang merupakan turunan dari class yang lain dengan class hasil turunannya mewariskan (inherit) semua sifat (properties dan methods) dari superclass-nya. Dan kalau kita perhatikan Form juga adalah sebuah class, artinya kita juga bisa membuat sebuah class turunannya. Di sinilah istilah Visual Inheritance menjadi relevan, karena seluruh tampilan visual, berupa layout dan posisi control, seperti Button, Panel, Label, dan sebagainya otomatis akan diwariskan kepada Form turunannya, sehingga keinginan kita membuat sebuah User Interface yang konsisten bisa dicapai dengan coding seminimal mungkin.

Untuk Aplikasi Inventory Sederhana yang sedang kita buat ini, kita memerlukan 2 (dua) buah form dengan tampilan layout Button yang konsisten, untuk mempermudah pengguna, yaitu

  • Pertama, form untuk melihat daftar barang dan daftar transaksi
  • Kedua, form untuk proses tambah atau edit barang dan transaksi

Sesuai desain aplikasi yang sudah kita buat sebelumnya, desain form untuk menampilkan daftar barang dan daftar transaksi harus memiliki sejumlah Button untuk proses Tambah, Ubah, dan Hapus. Action tambahan lainnya adalah Button untuk Refresh dan Cetak Data. Maka desainnya kita buat seperti ini.

[collapsed title=Spoiler: Desain Form List] Aplikasi Inventory Sederhana Desain User Interface Visual Inheritance BiruniLabs [/collapsed]

Sedangkan form untuk proses penambahan dan pengubahan data cukup memiliki Button untuk Simpan saja, seperti ini.

[collapsed title=Spoiler: Desain Form Edit Data] Aplikasi Inventory Sederhana Desain User Interface Visual Inheritance BiruniLabs [/collapsed]

Seluruh control dalam template tersebut harus kita set property Modifer sebagai Protected, agar kita bisa mengubah property masing-masing control tersebut di dalam form turunannya.

[collapsed title=Spoiler: Ubah Modifier Semua Control] Aplikasi Inventory Sederhana Desain User Interface Visual Inheritance BiruniLabs [/collapsed]

Untuk menambahkan form turunan dari template yang sudah kita buat di atas maka, maka silakan buka Form Dialog Add New Item, dan pilih Inherited Form seperti gambar di bawah ini, kemudian berikan nama form turunan dan klik tombol [Add]

[collapsed title=Spoiler: Add New Item Dialog] Aplikasi Inventory Sederhana Desain User Interface Visual Inheritance BiruniLabs [/collapsed]

Berikutnya akan muncul Dialog Box yang meminta kita untuk memilih Form superclass, atau Form template layout yang akan kita pakai sebagai parent, seperti tampak pada gambar di bawah ini.

[collapsed title=Spoiler: Select Parent Form] Aplikasi Inventory Sederhana Desain User Interface Visual Inheritance BiruniLabs [/collapsed]

Jika kita kita buka file frmItemList.Designer.vb, maka ada kata kunci penting yang menandakan bahwa frmItemList adalah turunan (inherit) dari frmList. Seperti terlihat di gambar ini.

[collapsed title=Spoiler: Inheritance – Designer Code] Aplikasi Inventory Sederhana Desain User Interface Visual Inheritance BiruniLabs [/collapsed]

Tambahkan semua form yang kita perlukan, meliputi Form Daftar Barang, Form Edit Barang, Form Daftar Barang Masuk, Form Edit Barang Masuk, dan seterusnya, sehingga keseluruhan Solution kita menjadi seperti gambar di bawah ini.

[collapsed title=Spoiler: Struktur File Solution] Aplikasi Inventory Sederhana Desain User Interface Visual Inheritance BiruniLabs [/collapsed]

Setelah selesai, maka kita dapat mengubah dan menambahkan control yang kita inginkan ke Form Turunannya, misalkan contoh untuk Form Entry Data Master Barang seperti gambar di bawah ini.

[collapsed title=Spoiler: Contoh Desain Form Edit Barang] Aplikasi Inventory Sederhana Desain User Interface Visual Inheritance BiruniLabs [/collapsed]

Mudah-mudahan penjelasan tentang Visual Inheritance dapat dipahami dengan mudah, dan silakan kunjungi link di bawah ini untuk mengunduh source-code sementara.

[collapsed title=Spoiler: Download Source Code Di Sini] Dibuat menggunakan Visual Studio 2013, Silakan Download Source Code Di Sini [/collapsed]

Salam PojokProgrammer ^_^

 

.

Aplikasi Inventory Sederhana – Penggunaan Trigger untuk Tracking Stock Barang

superadmin January 7, 2014 No Comments
Aplikasi Inventory Sederhana - Penggunaan Trigger untuk Tracking Stock Barang

Trigger bagaikan pisau bermata dua. Salah penggunaan bisa berakibat fatal, dan tidak sedikit developer software antipati dengan trigger karena sering terjadi hal-hal yang tidak disangka-sangka dan bahkan di luar nalar, berhari-hari melakukan debugging, pada akhirnya problem ditemukan karena adanya sebuat trigger yang tidak terdokumentasi dengan baik. Walaupun sebuah pisau bisa membunuh kita, bukan berarti kita harus membuangnya jauh-jauh dan tidak mau menggunakannya lagi, karena penggunaan yang benar tentunya akan sangat membantu mempermudah dan meningkatkan kualitas hidup kita 🙂

Dalam artikel ini, akan kita mempelajari cara penggunaan trigger yang benar, dalam rangka membantu mempermudah kita membuat sebuah Aplikasi Inventory Sederhana Aplikasi Stok Barang ini. Trigger akan kita gunakan untuk melakukan update status kuantitas stok barang setiap kali terjadi transaksi penambahan dan pengurangan barang, atau transaksi barang masuk atau barang keluar. Jika kita sederhanakan sampai level database, maka akan ada perintah SQL INSERT, UPDATE, dan DELETE di table transaksi yang secara otomatis, mengubah status stok barang bersangkutan.

Desain database sudah kita buat pada langkah analisa dan desain database pada artikel sebelumnya. Maka langkah selanjutnya adalah membuat trigger untuk setiap jenis SQL DML. Trigger akan kita tempelkan ke table transaction_details, karena di table itulah informasi ID dan jumlah kuantitas barang tersedia. Untuk detail sintaks CREATE TRIGGER dapat mengacu ke dokumentasi MySQL CREATE TRIGGER di sini.

Referensi Data

Untuk tutorial ini, diasumsikan sudah tersedia data untuk transaction_types dan items seperti berikut ini,

mysql> -- -----------------------------
mysql> -- data jenis transaksi
mysql> -- -----------------------------
mysql> select * from transaction_types ;
+----+------+---------------+
| id | code | name          |
+----+------+---------------+
|  1 | IN   | BARANG MASUK  |
|  2 | OUT  | BARANG KELUAR |
+----+------+---------------+
2 rows in set (0.00 sec)

mysql> -- -----------------------------
mysql> -- data master barang
mysql> -- -----------------------------
mysql> select * from items ;
+----+--------+-------------------------+------------------+---------+
| id | code   | name                    | quantity_on_hand | remarks |
+----+--------+-------------------------+------------------+---------+
|  1 | PC.001 | PC Jangkrik Tipe 1      |             0.00 | NULL    |
|  2 | PC.002 | PC Jangkrik Tipe 2      |             0.00 | NULL    |
|  3 | SV.001 | Server Rainer Tipe 1    |             0.00 | NULL    |
|  4 | SV.002 | Server Rainer Rackmount |             0.00 | NULL    |
+----+--------+-------------------------+------------------+---------+
4 rows in set (0.00 sec)

INSERT Data Transaksi

Untuk proses INSERT ini akan kita set berjalan setiap kali ada sebuah row dimasukkan ke dalam database, diindikasikan dengan keywork FOR EACH ROW, dan setiap row yang baru masuk bisa kita ketahui di variable NEW. Logikanya adalah seperti ini,

  • Setiap kali ada transaksi barang masuk, maka field stok barang di table items akan kita tambahkan,
  • Sebaliknya, setiap kali ada transaksi barang keluar, maka field stok barang di table items akan kita kurangi,

maka trigger INSERT akan seperti ini.

mysql> delimiter $$

mysql> drop trigger if exists trg_transaction_details_after_insert$$
Query OK, 0 rows affected (0.00 sec)

mysql> -- -----------------------------
mysql> -- after insert trigger
mysql> -- -----------------------------
mysql> create trigger trg_transaction_details_after_insert
    -> after insert on transaction_details
    -> for each row
    -> begin
    ->   -- -----------------------------
    ->   -- ambil jenis transaksi
    ->   -- -----------------------------
    ->   declare tipe varchar(20);
    ->   set tipe = (
    ->     select tt.code
    ->       from transactions tr
    ->       join transaction_types tt on tr.type_id=tt.id
    ->       where tr.id=new.trans_id
    ->   ) ;
    ->   -- -----------------------------
    ->   -- update sesuai jenis transaksi
    ->   -- -----------------------------
    ->   update items
    ->     set quantity_on_hand
    ->         = quantity_on_hand
    ->         + case when tipe='IN' then new.quantity
    ->                when tipe='OUT' then -new.quantity
    ->                else 0 end
    ->     where items.id=new.item_id ;
    -> end$$
Query OK, 0 rows affected (0.12 sec)

mysql> delimiter ;
mysql>

[collapsed title=Spoiler: Hasil Test INSERT Transaksi Barang Masuk dan Barang Keluar]

Test INSERT data transaksi barang masuk.

mysql> -----------------------------
mysql> -- insert header transaksi barang masuk
mysql> -----------------------------
mysql> insert into transactions (type_id, trans_code, trans_date, remarks)
    -> values (1, 'BM.00001/2014', '2014-01-10', 'Penerimaan Barang') ;
Query OK, 1 row affected (0.05 sec)

mysql> -----------------------------
mysql> -- lihat data transaksi
mysql> -- pastikan data sudah masuk
mysql> -----------------------------
mysql> select * from transactions;
+----+---------+---------------+------------+-------------------+
| id | type_id | trans_code    | trans_date | remarks           |
+----+---------+---------------+------------+-------------------+
|  1 |       1 | BM.00001/2014 | 2014-01-10 | Penerimaan Barang |
+----+---------+---------------+------------+-------------------+
1 row in set (0.00 sec)

mysql> -----------------------------
mysql> -- insert detail transaksi barang masuk
mysql> -----------------------------
mysql> insert into transaction_details (trans_id, item_id, quantity, remarks)
    -> values (1, 1, 20, 'Penerimaan Barang 1')
    ->      , (1, 2, 30, 'Penerimaan Barang 2')
    ->      , (1, 3, 10, 'Penerimaan Barang 3')
    ->      , (1, 4, 15, 'Penerimaan Barang 4') ;
Query OK, 4 rows affected (0.07 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> -----------------------------
mysql> -- lihat detail transaksi
mysql> -- pastikan data sudah masuk
mysql> -----------------------------
mysql> select * from transaction_details where trans_id=1 ;
+----+----------+---------+----------+---------------------+
| id | trans_id | item_id | quantity | remarks             |
+----+----------+---------+----------+---------------------+
| 22 |        1 |       1 |       20 | Penerimaan Barang 1 |
| 23 |        1 |       2 |       30 | Penerimaan Barang 2 |
| 24 |        1 |       3 |       10 | Penerimaan Barang 3 |
| 25 |        1 |       4 |       15 | Penerimaan Barang 4 |
+----+----------+---------+----------+---------------------+
4 rows in set (0.00 sec)

mysql> -----------------------------
mysql> -- dengan asumsi awal semua stok barang masih kosong
mysql> -- maka saat ini quantity on hand pasti sudah berubah
mysql> -----------------------------
mysql> select * from items ;
+----+--------+-------------------------+------------------+---------+
| id | code   | name                    | quantity_on_hand | remarks |
+----+--------+-------------------------+------------------+---------+
|  1 | PC.001 | PC Jangkrik Tipe 1      |            20.00 | NULL    |
|  2 | PC.002 | PC Jangkrik Tipe 2      |            30.00 | NULL    |
|  3 | SV.001 | Server Rainer Tipe 1    |            10.00 | NULL    |
|  4 | SV.002 | Server Rainer Rackmount |            15.00 | NULL    |
+----+--------+-------------------------+------------------+---------+
4 rows in set (0.00 sec)

mysql> -----------------------------
mysql> -- YAY!!! Berhasil!!!
mysql> -----------------------------
mysql>

Test INSERT data transaksi barang keluar.

mysql> -----------------------------
mysql> -- insert header transaksi barang keluar
mysql> -----------------------------
mysql> insert into transactions (type_id, trans_code, trans_date, remarks)
    -> values (2, 'BK.00001/2014', '2014-01-11', 'Pengeluaran Barang') ;
Query OK, 1 row affected (0.05 sec)

mysql> -----------------------------
mysql> -- lihat data transaksi
mysql> -- pastikan data sudah masuk
mysql> -----------------------------
mysql> select * from transactions ;
+----+---------+---------------+------------+--------------------+
| id | type_id | trans_code    | trans_date | remarks            |
+----+---------+---------------+------------+--------------------+
|  1 |       1 | BM.00001/2014 | 2014-01-10 | Penerimaan Barang  |
|  2 |       2 | BK.00001/2014 | 2014-01-11 | Pengeluaran Barang |
+----+---------+---------------+------------+--------------------+
2 rows in set (0.00 sec)

mysql> -----------------------------
mysql> -- insert detail transaksi barang keluar
mysql> -----------------------------
mysql> insert into transaction_details (trans_id, item_id, quantity, remarks)
    -> values (2, 1, 4, 'Pengeluaran Barang 1')
    ->      , (2, 4, 5, 'Pengeluaran Barang 4') ;
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> -----------------------------
mysql> -- lihat detail transaksi
mysql> -- pastikan data sudah masuk
mysql> -----------------------------
mysql> select * from transaction_details where trans_id=2 ;
+----+----------+---------+----------+----------------------+
| id | trans_id | item_id | quantity | remarks              |
+----+----------+---------+----------+----------------------+
| 29 |        2 |       1 |        4 | Pengeluaran Barang 1 |
| 30 |        2 |       4 |        5 | Pengeluaran Barang 4 |
+----+----------+---------+----------+----------------------+
2 rows in set (0.00 sec)

mysql> -----------------------------
mysql> -- dengan asumsi awal semua stok barang masih kosong
mysql> -- maka saat ini quantity on hand pasti sudah berubah
mysql> -- item_id=1 yang semula 20, dikurangi 4 menjadi 16
mysql> -- item_id=4 yang semula 15, dikurangi 5 menjadi 10
mysql> -----------------------------
mysql> select * from items ;
+----+--------+-------------------------+------------------+---------+
| id | code   | name                    | quantity_on_hand | remarks |
+----+--------+-------------------------+------------------+---------+
|  1 | PC.001 | PC Jangkrik Tipe 1      |            16.00 | NULL    |
|  2 | PC.002 | PC Jangkrik Tipe 2      |            30.00 | NULL    |
|  3 | SV.001 | Server Rainer Tipe 1    |            10.00 | NULL    |
|  4 | SV.002 | Server Rainer Rackmount |            10.00 | NULL    |
+----+--------+-------------------------+------------------+---------+
4 rows in set (0.00 sec)

mysql> -----------------------------
mysql> -- YAY!!! Berhasil Lagi!!!
mysql> -----------------------------
mysql>

[/collapsed]

UPDATE (Edit) Data Transaksi

Untuk proses UPDATE, sama seperti proses INSERT dan DELETE, trigger akan berjalan FOR EACH ROW, namun sedikit lebih rumit, row ebelum proses update, atau data original tersimpan di variable OLD, dan row yang baru tersimpan di variable NEW. Logikanya adalah seperti ini,
  • Setiap kali ada update data transaksi barang masuk, maka field stok barang di table items akan kita kurangi dengan kuantitas lama kemudian kita tambahkan dengan kuantitas yang baru, 
  • Sebaliknya, setiap kali ada update data transaksi barang keluar, maka field stok barang di table items akan kita tambahkan dengan kuantitas lama kemudian kita kurangi dengan kuantitas yang baru,

maka trigger UPDATE akan seperti ini,

mysql> delimiter $$
mysql>

mysql> drop trigger if exists trg_transaction_details_after_update$$
Query OK, 0 rows affected (0.06 sec)

mysql> -- -----------------------------
mysql> -- after update trigger
mysql> -- -----------------------------
mysql> create trigger trg_transaction_details_after_update
    -> after update on transaction_details
    -> for each row
    -> begin
    ->   -- -----------------------------
    ->   -- ambil jenis transaksi
    ->   -- -----------------------------
    ->   declare tipe varchar(20);
    ->   set tipe = (
    ->     select tt.code
    ->       from transactions tr
    ->       join transaction_types tt on tr.type_id=tt.id
    ->       where tr.id=old.trans_id
    ->   ) ;
    ->   -- -----------------------------
    ->   -- update sesuai jenis transaksi
    ->   -- => kurangi dengan old quantity
    ->   -- -----------------------------
    ->   update items
    ->     set quantity_on_hand
    ->         = quantity_on_hand
    ->         + case when tipe='IN' then -old.quantity
    ->                when tipe='OUT' then old.quantity
    ->                else 0 end
    ->     where items.id=old.item_id ;
    ->   -- -----------------------------
    ->   -- update sesuai jenis transaksi
    ->   -- => tambahkan dengan new quantity
    ->   -- -----------------------------
    ->   update items
    ->     set quantity_on_hand
    ->         = quantity_on_hand
    ->         + case when tipe='IN' then new.quantity
    ->                when tipe='OUT' then -new.quantity
    ->                else 0 end
    ->     where items.id=new.item_id ;
    -> end$$
Query OK, 0 rows affected (0.14 sec)

mysql> delimiter ;
mysql>

[collapsed title=Spoiler: Hasil Test UPDATE Transaksi Barang Masuk dan Barang Keluar]

Status data sebelum Test UPDATE.

mysql> -----------------------------
mysql> -- status stok 
mysql> -- sebelum proses UPDATE
mysql> -----------------------------
mysql> select * from items;
+----+--------+-------------------------+------------------+---------+
| id | code   | name                    | quantity_on_hand | remarks |
+----+--------+-------------------------+------------------+---------+
|  1 | PC.001 | PC Jangkrik Tipe 1      |            16.00 | NULL    |
|  2 | PC.002 | PC Jangkrik Tipe 2      |            30.00 | NULL    |
|  3 | SV.001 | Server Rainer Tipe 1    |            10.00 | NULL    |
|  4 | SV.002 | Server Rainer Rackmount |            10.00 | NULL    |
+----+--------+-------------------------+------------------+---------+
4 rows in set (0.00 sec)

mysql> -----------------------------
mysql> -- status detil transaksi
mysql> -- sebelum proses UPDATE
mysql> -----------------------------
mysql> select * from transaction_details ;
+----+----------+---------+----------+----------------------+
| id | trans_id | item_id | quantity | remarks              |
+----+----------+---------+----------+----------------------+
| 22 |        1 |       1 |       20 | Penerimaan Barang 1  |
| 23 |        1 |       2 |       30 | Penerimaan Barang 2  |
| 24 |        1 |       3 |       10 | Penerimaan Barang 3  |
| 25 |        1 |       4 |       15 | Penerimaan Barang 4  |
| 29 |        2 |       1 |        4 | Pengeluaran Barang 1 |
| 30 |        2 |       4 |        5 | Pengeluaran Barang 4 |
+----+----------+---------+----------+----------------------+
6 rows in set (0.00 sec)

Test UPDATE data transaksi barang masuk.

mysql> -----------------------------
mysql> -- ubah detil transaksi masuk
mysql> -- untuk id transaksi 22,
mysql> -- dari quantity awal 20 menjadi 50
mysql> -----------------------------
mysql> update transaction_details
    -> set quantity = 50
    -> where id = 22 ;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> -----------------------------
mysql> -- maka untuk item_id 1 (dari transaction detail id 22)
mysql> -- ada update quantity dari 20 menjadi 50 ada selisih +30
mysql> -- maka stok barang berubah, 16 -20 +30 = 46
mysql> -----------------------------
mysql> select * from items ;
+----+--------+-------------------------+------------------+---------+
| id | code   | name                    | quantity_on_hand | remarks |
+----+--------+-------------------------+------------------+---------+
|  1 | PC.001 | PC Jangkrik Tipe 1      |            46.00 | NULL    |
|  2 | PC.002 | PC Jangkrik Tipe 2      |            30.00 | NULL    |
|  3 | SV.001 | Server Rainer Tipe 1    |            10.00 | NULL    |
|  4 | SV.002 | Server Rainer Rackmount |            10.00 | NULL    |
+----+--------+-------------------------+------------------+---------+
4 rows in set (0.00 sec)
Query OK, 0 rows affected (0.04 sec)

mysql> -----------------------------
mysql> -- YAY!!! Sukses!!!
mysql> -----------------------------
mysql>

Test UPDATE data transaksi barang keluar.

mysql> -----------------------------
mysql> -- ubah detil transaksi keluar
mysql> -- untuk id transaksi 29,
mysql> -- dari quantity awal 4 menjadi 25
mysql> -----------------------------
mysql> update transaction_details
    -> set quantity = 25
    -> where id = 29 ;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> -----------------------------
mysql> -- maka untuk item_id 1 (dari transaction detail id 29)
mysql> -- ada update quantity keluar dari 4 menjadi 25 ada selisih 21
mysql> -- dari transaksi sebelumnya saldo akhir item_id 1 adalah 46
mysql> -- maka saldo stok barang berubah, 46 +4 -25 = 25
mysql> -----------------------------
mysql> select * from items ;
+----+--------+-------------------------+------------------+---------+
| id | code   | name                    | quantity_on_hand | remarks |
+----+--------+-------------------------+------------------+---------+
|  1 | PC.001 | PC Jangkrik Tipe 1      |            25.00 | NULL    |
|  2 | PC.002 | PC Jangkrik Tipe 2      |            30.00 | NULL    |
|  3 | SV.001 | Server Rainer Tipe 1    |            10.00 | NULL    |
|  4 | SV.002 | Server Rainer Rackmount |            10.00 | NULL    |
+----+--------+-------------------------+------------------+---------+
4 rows in set (0.00 sec)

mysql> -----------------------------
mysql> -- YAY!!! Awesome!!!
mysql> -----------------------------
mysql>

[/collapsed]

DELETE Data Transaksi

Untuk proses DELETE, sama seperti proses INSERT, trigger akan berjalan FOR EACH ROW, namun row yang sedang kita hapus ada di variable OLD. Logikanya adalah seperti ini,
  • Setiap kali ada penghapusan data transaksi barang masuk, maka field stok barang di table items akan kita kurangi, 
  • Sebaliknya, setiap kali ada penghapusan data transaksi barang keluar, maka field stok barang di table items akan kita tambahkan,

maka trigger DELETE akan seperti ini,

mysql> delimiter $$

mysql> drop trigger if exists trg_transaction_details_after_delete$$
Query OK, 0 rows affected (0.06 sec)

mysql> -- -----------------------------
mysql> -- after delete trigger
mysql> -- -----------------------------
mysql> create trigger trg_transaction_details_after_delete
    -> after delete on transaction_details
    -> for each row
    -> begin
    ->   -- -----------------------------
    ->   -- ambil jenis transaksi
    ->   -- -----------------------------
    ->   declare tipe varchar(20);
    ->   set tipe = (
    ->     select tt.code
    ->       from transactions tr
    ->       join transaction_types tt on tr.type_id=tt.id
    ->       where tr.id=old.trans_id
    ->   ) ;
    ->   -- -----------------------------
    ->   -- update sesuai jenis transaksi
    ->   -- -----------------------------
    ->   update items
    ->     set quantity_on_hand
    ->         = quantity_on_hand
    ->         + case when tipe='IN' then -old.quantity
    ->                when tipe='OUT' then old.quantity
    ->                else 0 end
    ->     where items.id=old.item_id ;
    -> end$$
Query OK, 0 rows affected (0.23 sec)

mysql> delimiter ;
mysql>

[collapsed title=Spoiler: Hasil Test DELETE Transaksi Barang Masuk dan Barang Keluar]

Status database sebelum proses DELETE.

mysql> ----------------------------
mysql> -- status stok 
mysql> -- sebelum proses DELETE
mysql> -----------------------------
mysql> select * from items ;
+----+--------+-------------------------+------------------+---------+
| id | code   | name                    | quantity_on_hand | remarks |
+----+--------+-------------------------+------------------+---------+
|  1 | PC.001 | PC Jangkrik Tipe 1      |            25.00 | NULL    |
|  2 | PC.002 | PC Jangkrik Tipe 2      |            30.00 | NULL    |
|  3 | SV.001 | Server Rainer Tipe 1    |            10.00 | NULL    |
|  4 | SV.002 | Server Rainer Rackmount |            10.00 | NULL    |
+----+--------+-------------------------+------------------+---------+
4 rows in set (0.00 sec)

mysql> -----------------------------
mysql> -- status detil transaksi
mysql> -- sebelum proses DELETE
mysql> -----------------------------
mysql> select * from transaction_details;
+----+----------+---------+----------+----------------------+
| id | trans_id | item_id | quantity | remarks              |
+----+----------+---------+----------+----------------------+
| 22 |        1 |       1 |       50 | Penerimaan Barang 1  |
| 23 |        1 |       2 |       30 | Penerimaan Barang 2  |
| 24 |        1 |       3 |       10 | Penerimaan Barang 3  |
| 25 |        1 |       4 |       15 | Penerimaan Barang 4  |
| 29 |        2 |       1 |       25 | Pengeluaran Barang 1 |
| 30 |        2 |       4 |        5 | Pengeluaran Barang 4 |
+----+----------+---------+----------+----------------------+
6 rows in set (0.00 sec)

Test DELETE data transaksi.

Untuk test delete, kita lakukan sederhanan saja, yaitu hapus semua data transactions_details sehingga seluruh data transaksi akaterhapus, maka status stok seharusnya berubah menjadi seperti awal lagi yaitu seluruh saldo stok bernilai 0 (nol)

mysql> -----------------------------
mysql> -- hapus detil transaksi
mysql> -----------------------------
mysql> delete from transaction_details ;
Query OK, 4 rows affected (0.03 sec)

mysql> -----------------------------
mysql> -- pastikan table detil transaksi kososng
mysql> -----------------------------
mysql> select * from transaction_details;
Empty set (0.00 sec)

mysql> -----------------------------
mysql> -- status saldo stok kembali nol
mysql> -----------------------------
mysql> select * from items;
+----+--------+-------------------------+------------------+---------+
| id | code   | name                    | quantity_on_hand | remarks |
+----+--------+-------------------------+------------------+---------+
|  1 | PC.001 | PC Jangkrik Tipe 1      |             0.00 | NULL    |
|  2 | PC.002 | PC Jangkrik Tipe 2      |             0.00 | NULL    |
|  3 | SV.001 | Server Rainer Tipe 1    |             0.00 | NULL    |
|  4 | SV.002 | Server Rainer Rackmount |             0.00 | NULL    |
+----+--------+-------------------------+------------------+---------+
4 rows in set (0.00 sec)

mysql> -----------------------------
mysql> -- YAY!!! I'm Invincible!!!
mysql> -----------------------------
mysql>

[/collapsed]

[collapsed title=Spoiler: Source Code Trigger Lengkap]

delimiter $$

drop trigger if exists trg_transaction_details_after_insert$$
-- -----------------------------
-- after insert trigger
-- -----------------------------
create trigger trg_transaction_details_after_insert
after insert on transaction_details
for each row
begin
  -- -----------------------------
  -- ambil jenis transaksi
  -- -----------------------------
  declare tipe varchar(20);
  set tipe = (
    select tt.code
      from transactions tr
      join transaction_types tt on tr.type_id=tt.id
      where tr.id=new.trans_id
  ) ;
  -- -----------------------------
  -- update sesuai jenis transaksi
  -- -----------------------------
  update items
    set quantity_on_hand
        = quantity_on_hand
        + case when tipe='IN' then new.quantity
               when tipe='OUT' then -new.quantity
               else 0 end
    where items.id=new.item_id ;
end$$
 
drop trigger if exists trg_transaction_details_after_update$$
-- -----------------------------
-- after update trigger
-- -----------------------------
create trigger trg_transaction_details_after_update
after update on transaction_details
for each row
begin
  -- -----------------------------
  -- ambil jenis transaksi
  -- -----------------------------
  declare tipe varchar(20);
  set tipe = (
    select tt.code
      from transactions tr
      join transaction_types tt on tr.type_id=tt.id
      where tr.id=old.trans_id
  ) ;
  -- -----------------------------
  -- update sesuai jenis transaksi
  -- => kurangi dengan old quantity
  -- -----------------------------
  update items
    set quantity_on_hand
        = quantity_on_hand
        + case when tipe='IN' then -old.quantity
               when tipe='OUT' then old.quantity
               else 0 end
    where items.id=old.item_id ;
  -- -----------------------------
  -- update sesuai jenis transaksi
  -- => tambahkan dengan new quantity
  -- -----------------------------
  update items
    set quantity_on_hand
        = quantity_on_hand
        + case when tipe='IN' then new.quantity
               when tipe='OUT' then -new.quantity
               else 0 end
    where items.id=new.item_id ;
end$$
 
drop trigger if exists trg_transaction_details_after_delete$$
-- -----------------------------
-- after delete trigger
-- -----------------------------
create trigger trg_transaction_details_after_delete
after delete on transaction_details
for each row
begin
  -- -----------------------------
  -- ambil jenis transaksi
  -- -----------------------------
  declare tipe varchar(20);
  set tipe = (
    select tt.code
      from transactions tr
      join transaction_types tt on tr.type_id=tt.id
      where tr.id=old.trans_id
  ) ;
  -- -----------------------------
  -- update sesuai jenis transaksi
  -- -----------------------------
  update items
    set quantity_on_hand
        = quantity_on_hand
        + case when tipe='IN' then -old.quantity
               when tipe='OUT' then old.quantity
               else 0 end
    where items.id=old.item_id ;
end$$
 
delimiter ;

[/collapsed]

Simpulan

Setelah kita perhatikan hasil test di atas, maka proses pengerjaan coding di aplikasi akan jauh lebih mudah karena tidak perlu lagi memikirkan code untuk mengubah status stok barang, karena perubahan status stok barang seluruhnya sudah ditangani oleh trigger yang kita buat ini.

Salam PojokProgrammer

^_^

Belajar SQL: Perhitungan Stok dan Harga Rata-Rata Barang untuk Aplikasi Inventory

superadmin December 29, 2013 No Comments
Perhitungan Stok dan Harga Rata-Rata Barang untuk Aplikasi Inventory Satu Perintah Single SQL Statement

Belajar SQL | Semua programmer atau software developer aplikasi perkantoran pasti pernah menggunakan SQL. minimal untuk proses CRUD (Create, Read, Update, Delete) sederhana. Namun hanya sedikit yang memanfaatkan SQL untuk proses perhitungan rumit seperti penggajian, perhitungan average harga barang, status inventory, dan sebagainya hanya dengan menggunakan Single SQL Statement, alias dengan satu perintah SQL saja. Beberapa waktu lalu saat diskusi online dengan kawan-kawan di Facebook, saya tergelitik untuk membuat contoh SQL beraksi untuk menghitung nilai stok dan harga rata-rata barang setelah terjadi proses keluar masuk barang pada bulan tertentu. Contoh kasusnya adalah seperti ini…..

Di Ketahui ;

QOB := 1.670,28 Kg ( Opening Balance )
vOB := Rp. 8.397.476,7 ( Value Opening Balance 

Akumulasi selama 1 bulan dari pembelian ( Harus Proses seluruh transaksi masuk )
SQB := 1.600,Kg ( Supply quantity Barang )
vSQB := Rp. 8.825.600 ( Value Quantity Barang )

Akumulasi pemakain material selam 1 bulan ( Harus Proses seluruh Transaksi Keluar )
uQB := 973,83 ( Used Quantity Balance )

Di Tanya ;

vuQB : …..?? ( Value used Quatity Balance )
SAB : …… ( Saldo akhir balance )
vSAB : ……..( Value saldo akhir balance )

Jawab ;

Average Bahan Baku := (( vSQB+vOB) / (QOB+SQB) )
vuQB := uQB * average bahan baku 
SAB := (( QOB+SQB)-UQB)
vSAB := SAB * average bahan baku

Ketika kita berinteraksi dengan database, maka ada Golden Rule yang harus selalu dipegang. Golden Rule ini disampaikan oleh Tom Kyte, Evangelist Oracle, dalam salah satu artikel di blog-nya

  1. Sebisa mungkin, selalu gunakan satu perintah SQL.
  2. Jika tidak bisa dalam satu perintah SQL, maka gunakan Stored Procedure
  3. Jika tidak bisa menggunakan Stored Procedure, lakukan di aplikasi kita
  4. Jika masih tidak bisa juga, mungkin kita perlu memikirkan masak-masak, sebenarnya perlu gak sih kita lakukan hal ini? 🙂

Untuk itu saya akan coba menyelesaikan problem di atas hanya menggunakan satu perintah SQL saja. Namun saya akan jelaskan langkah-demi-langkah dalam membangun "satu perintah SQL" tersebut, agar teman-teman bisa memahami algoritma dan logika yang ada di dalamnya. Untuk kasus ini saya akan menggunakan PostgreSQL untuk menyelesaikannya, namun hasil akhir adalah perintah SQL standar yang bisa digunakan di RDBMS lain. Mari kita mulai dengan membuka command line PostgreSQL ………………..

C:\Program Files\PostgreSQL\9.3\bin>psql -U postgres -d inventory
psql (9.3.1)
WARNING: Console code page (850) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

Untuk menyelesaikan kasus di atas kita memerlukan beberapa buah table, yaitu

  • table items untuk menampung data master barang.
  • table item_balances untuk menampung data saldo awal bulanan tiap-tiap barang.
  • table transactions untuk mencatat transaksi harian, keluar dan masuk barang.

Pertama-tama kita buat struktur table items.

inventory=# -- -----------------------
inventory=# -- buat table items
inventory=# -- -----------------------
inventory=# create table items (
inventory(#   id serial primary key,
inventory(#   code varchar(20) not null,
inventory(#   name varchar(200) null
inventory(# ) ;
CREATE TABLE

Kemudian isikan beberapa data menggunakan perintah insert into. dan tampilkan hasilnya untuk memastikan.

inventory=# -- -----------------------
inventory=# -- masukkan beberapa data
inventory=# -- -----------------------
inventory=# insert into items (code, name)
inventory-# select 'ITEM01', 'Barang Pertama' union all
inventory-# select 'ITEM02', 'Barang Kedua' union all
inventory-# select 'ITEM03', 'Barang Ketiga' 
inventory-# ;
INSERT 0 3

inventory=# -- -----------------------
inventory=# -- lihat hasilnya
inventory=# -- -----------------------
inventory=# select * from items 
inventory-# ;

 id |  code  |      name
----+--------+----------------
  1 | ITEM01 | Barang Pertama
  2 | ITEM02 | Barang Kedua
  3 | ITEM03 | Barang Ketiga
(3 rows)

Berikutnya kita siapkan table item_balances yang digunakan untuk menyimpan saldo awal dan harga barang setiap bulannya. untuk itu perlu field periode untuk identifikasi bulan.

inventory=# -- -----------------------
inventory=# -- buat table balance
inventory=# -- -----------------------
inventory=# create table item_balances (
inventory(#   id serial primary key,
inventory(#   item_id integer not null,
inventory(#   period date not null,
inventory(#   quantity numeric(15,2),
inventory(#   unit_price numeric(15,2),
inventory(#   foreign key (item_id) references items(id)
inventory(# ) ;
CREATE TABLE

Kemudian isikan beberapa data menggunakan perintah insert into. dan tampilkan hasilnya untuk memastikan.

inventory=# -- -----------------------
inventory=# -- masukkan beberapa data
inventory=# -- -----------------------
inventory=# insert into item_balances (item_id, period, quantity, unit_price)
inventory-# select 1, to_date('2013-12-01','YYYY-MM-DD'), 1600, 23125 union all
inventory-# select 2, to_date('2013-12-01','YYYY-MM-DD'), 1250, 13100 union all
inventory-# select 3, to_date('2013-12-01','YYYY-MM-DD'), 3250, 3100 
inventory-# ;
INSERT 0 3

inventory=# -- -----------------------
inventory=# -- lihat hasilnya
inventory=# -- -----------------------
inventory=# select * from item_balances 
inventory-# ;

 id | item_id |   period   | quantity | unit_price
----+---------+------------+----------+------------
  1 |       1 | 2013-12-01 |  1600.00 |   23125.00
  2 |       2 | 2013-12-01 |  1250.00 |   13100.00
  3 |       3 | 2013-12-01 |  3250.00 |    3100.00
(3 rows)

Karena desain database kita sudah mengikuti kaidah normalisasi, maka yang tersimpan di table item_balances hanya id barang saja, Untuk mengetahui nama barang bersangkutan, kita gunakan klausa JOIN seperti di bawah ini…

inventory=# -- -----------------------
inventory=# -- tambahkan klausa JOIN
inventory=# -- -----------------------
inventory=# select a.*, b.period, b.quantity, b.unit_price
inventory-# , b.quantity*b.unit_price as item_value
inventory-# from items a join item_balances b on a.id=b.item_id 
inventory-# ;

 id |  code  |      name      |   period   | quantity | unit_price |  item_value
----+--------+----------------+------------+----------+------------+---------------
  1 | ITEM01 | Barang Pertama | 2013-12-01 |  1600.00 |   23125.00 | 37000000.00
  2 | ITEM02 | Barang Kedua   | 2013-12-01 |  1250.00 |   13100.00 | 16375000.00
  3 | ITEM03 | Barang Ketiga  | 2013-12-01 |  3250.00 |    3100.00 | 10075000.00
(3 rows)

Sekarang buatkan table transactions untuk menampung data transaksi harian, meliputi transaksi masuk dan keluar. Untuk membedakan transaksi masuk atau keluar, kita sediakan satu field tx_type untuk membedakannya.

inventory=# -- -----------------------
inventory=# -- buat table transaksi
inventory=# -- -----------------------
inventory=# create table transactions (
inventory(#   id serial primary key,
inventory(#   item_id integer not null,
inventory(#   tx_date date not null,
inventory(#   tx_type varchar(10) not null,
inventory(#   quantity numeric(15,2),
inventory(#   unit_price numeric(15,2),
inventory(#   foreign key (item_id) references items(id)
inventory(# ) ;
CREATE TABLE

Kemudian isikan beberapa data menggunakan perintah insert into. dan tampilkan hasilnya untuk memastikan. Kita tambahkan klausa JOIN untuk mengetahui informasi lebih jelas tentang barang yang terlibat dalam transaksi yang kita masukkan

inventory=# -- -----------------------
inventory=# -- masukkan beberapa data
inventory=# -- -----------------------
inventory=# insert into transactions (item_id, tx_date, tx_type, quantity, unit_price)
inventory-# select 1, to_date('2013-12-10','YYYY-MM-DD'), 'IN', 100, 24125 union all
inventory-# select 1, to_date('2013-12-20','YYYY-MM-DD'), 'IN', 200, 25125 union all
inventory-# select 1, to_date('2013-12-05','YYYY-MM-DD'), 'OUT', 100, 0 union all
inventory-# select 1, to_date('2013-12-08','YYYY-MM-DD'), 'OUT', 200, 0 union all
inventory-# select 1, to_date('2013-12-18','YYYY-MM-DD'), 'OUT', 400, 0 union all
inventory-# select 2, to_date('2013-12-02','YYYY-MM-DD'), 'IN', 1000, 15125 union all
inventory-# select 2, to_date('2013-12-23','YYYY-MM-DD'), 'IN', 2000, 14100 union all
inventory-# select 2, to_date('2013-12-04','YYYY-MM-DD'), 'OUT', 1100, 0 union all
inventory-# select 2, to_date('2013-12-05','YYYY-MM-DD'), 'OUT', 1200, 0 union all
inventory-# select 2, to_date('2013-12-15','YYYY-MM-DD'), 'OUT', 400, 0 union all
inventory-# select 3, to_date('2013-12-12','YYYY-MM-DD'), 'IN', 1000, 3500 union all
inventory-# select 3, to_date('2013-12-24','YYYY-MM-DD'), 'IN', 2000, 3700 union all
inventory-# select 3, to_date('2013-12-04','YYYY-MM-DD'), 'OUT', 1100, 0 union all
inventory-# select 3, to_date('2013-12-15','YYYY-MM-DD'), 'OUT', 1200, 0 union all
inventory-# select 3, to_date('2013-12-25','YYYY-MM-DD'), 'OUT', 2400, 0 
inventory-# ;
INSERT 0 15

inventory=# -- -----------------------
inventory=# -- lihat hasilnya, pakai JOIN
inventory=# -- -----------------------
inventory=# select a.*, b.code, b.name
inventory-# from transactions a
inventory-# join items b on a.item_id=b.id
inventory-# order by tx_date, tx_type 
inventory-# ;

 id | item_id |  tx_date   | tx_type | quantity | unit_price |  code  |      name
----+---------+------------+---------+----------+------------+--------+----------------
  6 |       2 | 2013-12-02 | IN      |  1000.00 |   15125.00 | ITEM02 | Barang Kedua
  8 |       2 | 2013-12-04 | OUT     |  1100.00 |       0.00 | ITEM02 | Barang Kedua
 13 |       3 | 2013-12-04 | OUT     |  1100.00 |       0.00 | ITEM03 | Barang Ketiga
  3 |       1 | 2013-12-05 | OUT     |   100.00 |       0.00 | ITEM01 | Barang Pertama
  9 |       2 | 2013-12-05 | OUT     |  1200.00 |       0.00 | ITEM02 | Barang Kedua
  4 |       1 | 2013-12-08 | OUT     |   200.00 |       0.00 | ITEM01 | Barang Pertama
  1 |       1 | 2013-12-10 | IN      |   100.00 |   24125.00 | ITEM01 | Barang Pertama
 11 |       3 | 2013-12-12 | IN      |  1000.00 |    3500.00 | ITEM03 | Barang Ketiga
 14 |       3 | 2013-12-15 | OUT     |  1200.00 |       0.00 | ITEM03 | Barang Ketiga
 10 |       2 | 2013-12-15 | OUT     |   400.00 |       0.00 | ITEM02 | Barang Kedua
  5 |       1 | 2013-12-18 | OUT     |   400.00 |       0.00 | ITEM01 | Barang Pertama
  2 |       1 | 2013-12-20 | IN      |   200.00 |   25125.00 | ITEM01 | Barang Pertama
  7 |       2 | 2013-12-23 | IN      |  2000.00 |   14100.00 | ITEM02 | Barang Kedua
 12 |       3 | 2013-12-24 | IN      |  2000.00 |    3700.00 | ITEM03 | Barang Ketiga
 15 |       3 | 2013-12-25 | OUT     |  2400.00 |       0.00 | ITEM03 | Barang Ketiga
(15 rows)

Langkah berikutnya adalah membuat query untuk emnghitung summary transaksi. Kita akan menggunakan teknik crosstab untuk memindahkan row transaksi IN dan OUT menjadi kolom.

inventory=# -- -----------------------
inventory=# -- crosstab data transaksi
inventory=# -- -----------------------
inventory=# select item_id
inventory-# , date_trunc('month',tx_date) tx_date
inventory-# , sum(case when tx_type='IN' then quantity else 0 end) qty_in
inventory-# , sum(case when tx_type='IN' then quantity*unit_price else 0 end) value_in
inventory-# , sum(case when tx_type='OUT' then quantity else 0 end) qty_out
inventory-# from transactions
inventory-# group by item_id
inventory-# , date_trunc('month',tx_date) ;

 item_id |        tx_date         | qty_in  |   value_in    | qty_out
---------+------------------------+---------+---------------+---------
       2 | 2013-12-01 00:00:00+07 | 3000.00 | 43325000.0000 | 2700.00
       1 | 2013-12-01 00:00:00+07 |  300.00 |  7437500.0000 |  700.00
       3 | 2013-12-01 00:00:00+07 | 3000.00 | 10900000.0000 | 4700.00
(3 rows)

Terakhir…… lakukan JOIN terhadal table items, item_balance dan hasil crosstab transactions untuk proses perhitungan sesuai dengan rumus yang diberikan di bagian awal artikel ini.

inventory=# -- -----------------------
inventory=# -- perhitungan akhir
inventory=# -- -----------------------
inventory=# select it.id, it.code, it.name
inventory-# , bl.period, bl.quantity QOB, bl.unit_price Avg1
inventory-# , ( bl.quantity *bl.unit_price ) vOB
inventory-# , tx.qty_in SQB, tx.value_in vSQB, tx.qty_out uQB
inventory-# , ( (tx.value_in +bl.quantity *bl.unit_price) / (bl.quantity +tx.qty_in) ) Avg2
inventory-# , tx.qty_out
inventory-# * ( (tx.value_in+bl.quantity*bl.unit_price) / (bl.quantity +tx.qty_in) ) vuQB
inventory-# , ( bl.quantity +tx.qty_in ) -tx.qty_out SAB
inventory-# , ( (bl.quantity +tx.qty_in) -tx.qty_out )
inventory-# * ( (tx.value_in +bl.quantity *bl.unit_price) / (bl.quantity +tx.qty_in) ) vSAB
inventory-# from items it
inventory-# join item_balances bl on it.id = bl.item_id
inventory-# left join (
inventory(#   select item_id
inventory(#   , date_trunc('month',tx_date) tx_date
inventory(#   , sum(case when tx_type='IN' then quantity else 0 end) qty_in
inventory(#   , sum(case when tx_type='IN' then quantity*unit_price else 0 end) value_in
inventory(#   , sum(case when tx_type='OUT' then quantity else 0 end) qty_out
inventory(#   from transactions
inventory(#   group by item_id
inventory(#   , date_trunc('month',tx_date)
inventory(# ) tx
inventory-# on bl.item_id = tx.item_id
inventory-# and bl.period = tx.tx_date
inventory-# ;

 id |  code  |      name      |   period   |   qob   |   avg1   |     vob     |   sqb   |     vsqb    |   uqb   |   avg2   |    vuqb     |   sab   |   vsab
----+--------+----------------+------------+---------+----------+-------------+---------+-------------+---------+----------+-------------+---------+-------------
  1 | ITEM01 | Barang Pertama | 2013-12-01 | 1600.00 | 23125.00 | 37000000.00 |  300.00 |  7437500.00 |  700.00 | 23388.16 | 16371712.00 | 1200.00 | 28065792.00
  2 | ITEM02 | Barang Kedua   | 2013-12-01 | 1250.00 | 13100.00 | 16375000.00 | 3000.00 | 43325000.00 | 2700.00 | 14047.06 | 37927062.00 | 1550.00 | 21772943.00
  3 | ITEM03 | Barang Ketiga  | 2013-12-01 | 3250.00 |  3100.00 | 10075000.00 | 3000.00 | 10900000.00 | 4700.00 |  3356.00 | 15773200.00 | 1550.00 |  5201800.00
(3 rows)

BERHASIL!!!!!!

Kita berhasil membuat satu perintah SQL untuk perhitungan status stok akhir bulan dan menghitung harga rata-ratanya. hasil dari perintah SQL ini sudah matang, sehingga aplikasi kita – PHP atau VB.net atau lainnya – bisa langsung menampilkan hasilnya tanpa perlu melakukan proses perhitungan lagi……..

Semoga berguna….

^_^

 

Belajar SQL: Query Sederhana Menghitung Status Dinamis

superadmin December 17, 2013 No Comments
Belajar SQL: Query Sederhana untuk Field Status Dinamis

Belajar SQL: | Kadangkala kita memerlukan sebuah field berubah nilainya seiring waktu berjalan. sebagai contoh kasus adalah untuk mengetahui apakah garansi suatu barang masih berlaku atau tidak. Salah satu caranya adalah dengan menyediakan field "status" untuk menyimpan nilai berlaku atau habis untuk mengetahui status garansi bersangkutan. Cara ini punya kerumitan karena kita perlu mengubah isinya. Maka, alih-alih menyimpan data status garansi dalam sebuah field khusus, maka akan lebih mudah jika kita cukup menyimpan akhir masa garansi dan menentukan status garansi menggunakan query. Metode ini dinamakan juga sebagai Computed Field.

Penggunaan Computed Field seperti ini akan jauh lebih mudah dan jauh lebih fleksible karena hasil query akan berubah sesuai perubahan hari. Silahkan pelajari langkah-langkah berikut ini untuk membangun query status garansi tersebut.

mysql> use test ;
Database changed
 
mysql> -- --------------------------------------
mysql> -- create table contoh
mysql> -- --------------------------------------
mysql> create table garansi (
    ->   id int auto_increment primary key,
    ->   awal_garansi date null,
    ->   akhir_garansi date null
    -> ) ;
Query OK, 0 rows affected (0.11 sec)
 
mysql> -- --------------------------------------
mysql> -- insert sample data
mysql> -- --------------------------------------
mysql> insert into
    -> garansi (awal_garansi, akhir_garansi)
    -> values ('2010-07-17', '2013-07-16')
    ->      , ('2011-09-24', '2014-09-23')
    ->      , ('2010-10-14', '2013-10-23')
    ->      , ('2012-01-14', '2015-01-23')
    -> ;
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0
 
mysql> -- --------------------------------------
mysql> -- lihat isi sample data
mysql> -- --------------------------------------
mysql> select * from garansi ;
+----+--------------+---------------+
| id | awal_garansi | akhir_garansi |
+----+--------------+---------------+
|  1 | 2010-07-17   | 2013-07-16    |
|  2 | 2011-09-24   | 2014-09-23    |
|  3 | 2010-10-14   | 2013-10-23    |
|  4 | 2012-01-14   | 2015-01-23    |
+----+--------------+---------------+
4 rows in set (0.00 sec)
 
mysql> -- --------------------------------------
mysql> -- lakukan query
mysql> -- - tampilkan tanggal hari ini
mysql> -- - hitung selisih tanggal hari ini dengan akhir garansi
mysql> -- - dengan logika percabangan, 
mysql> --   => jika selisih positif artinya garansi sudah habis, 
mysql> --   => sebaliknya jika minus, maka garansi masih berlaku
mysql> -- --------------------------------------
mysql> select garansi.*
    -> , current_date tanggal
    -> , datediff(current_date,akhir_garansi) selisih
    -> , case
    ->   when datediff(current_date,akhir_garansi)>0
    ->        then 'habis'
    ->   else 'aktif' end status
    -> from garansi ;
+----+--------------+---------------+------------+---------+--------+
| id | awal_garansi | akhir_garansi | tanggal    | selisih | status |
+----+--------------+---------------+------------+---------+--------+
|  1 | 2010-07-17   | 2013-07-16    | 2013-12-17 |     154 | habis  |
|  2 | 2011-09-24   | 2014-09-23    | 2013-12-17 |    -280 | aktif  |
|  3 | 2010-10-14   | 2013-10-23    | 2013-12-17 |      55 | habis  |
|  4 | 2012-01-14   | 2015-01-23    | 2013-12-17 |    -402 | aktif  |
+----+--------------+---------------+------------+---------+--------+
4 rows in set (0.00 sec)
 
mysql> -- --------------------------------------
mysql> -- YAAAYYY !!! BERHASIL !!!
mysql> -- Silakan gunakan query terakhir dalam program php Anda
mysql> -- --------------------------------------

Walaupun contoh di atas menggunakan database MySQL, namun SQL yang ada bisa juga diterapkan di database lain seperti database PostgreSQL.

Semoga berguna………… (y)

 

Belajar SQL: Penggunaan SUM dan JOIN untuk membuat Laporan Summary

superadmin November 16, 2013 No Comments
Belajar SQL: Penggunaan SUM dan JOIN untuk membuat Laporan Summary

Belajar SQL | Membuat laporan summary data dari table di database bisa dibilang gampang-gampang susah. Gampang ketika hanya membuat summary dengan sumber data satu buah table saja. Namun jadi makin rumit ketika summary melibatkan 2 (dua) table atau lebih. Silakan pelajari TUTORIAL berikut ini sebagai salah satu contoh kasus untuk membuat summary data dari 3 buah table terpisah.

Pertama-tama kita buat Table MASTER untuk sample data menggunakan perintah SQL CREATE TABLE … AS SELECT … kemudian tampilkan isinya untuk memastikan.

mysql> create table t_master as
    -> select 1 id, 'A' data union all
    -> select 2 id, 'B' data union all
    -> select 3 id, 'C' data ;
Query OK, 3 rows affected (0.19 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t_master ;
+----+------+
| id | data |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
+----+------+
3 rows in set (0.00 sec)

Kemudian buat table TRANSAKSI pertama juga menggunakan perintah SQL CREATE TABLE … AS SELECT … kemudian tampilkan isinya untuk memastikan.

mysql> create table t_transaksi_1 as
    -> select 1 id, 'DOC 1' doc, 2000 value union all
    -> select 1 id, 'DOC 1' doc, 1000 value union all
    -> select 2 id, 'DOC 2' doc, 5000 value union all
    -> select 3 id, 'DOC 3' doc, 3000 value union all
    -> select 3 id, 'DOC 4' doc, 3000 value union all
    -> select 3 id, 'DOC 4' doc, 2000 value ;
Query OK, 6 rows affected (0.30 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from t_transaksi_1 ;
+----+-------+-------+
| id | doc   | value |
+----+-------+-------+
|  1 | DOC 1 |  2000 |
|  1 | DOC 1 |  1000 |
|  2 | DOC 2 |  5000 |
|  3 | DOC 3 |  3000 |
|  3 | DOC 4 |  3000 |
|  3 | DOC 4 |  2000 |
+----+-------+-------+
6 rows in set (0.00 sec)

Berikutnya buat table TRANSAKSI kedua juga menggunakan perintah SQL CREATE TABLE … AS SELECT … kemudian tampilkan isinya untuk memastikan.

mysql> create table t_transaksi_2 as
    -> select 2 id, 'DOC 2' doc, 2000 value union all
    -> select 2 id, 'DOC 2' doc, 2000 value union all
    -> select 3 id, 'DOC 3' doc, 4000 value union all
    -> select 3 id, 'DOC 4' doc, 5000 value union all
    -> select 3 id, 'DOC 4' doc, 1000 value ;
Query OK, 5 rows affected (0.15 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from t_transaksi_2 ;
+----+-------+-------+
| id | doc   | value |
+----+-------+-------+
|  2 | DOC 2 |  2000 |
|  2 | DOC 2 |  2000 |
|  3 | DOC 3 |  4000 |
|  3 | DOC 4 |  5000 |
|  3 | DOC 4 |  1000 |
+----+-------+-------+
5 rows in set (0.00 sec)

Sekarang kita buatkan summary untuk table TRANSAKSI pertama, menggunakan GROUP BY dan fungsi aggregat SUM()

mysql>    select tm.id, data, doc, sum(value) value1
    ->    from t_master tm
    ->    join t_transaksi_1 tr on tm.id=tr.id
    ->    group by id, doc
    -> ;
+----+------+-------+---------+
| id | data | doc   | value11 |
+----+------+-------+---------+
|  1 | A    | DOC 1 |    3000 |
|  2 | B    | DOC 2 |    5000 |
|  3 | C    | DOC 3 |    3000 |
|  3 | C    | DOC 4 |    5000 |
+----+------+-------+---------+
4 rows in set (0.00 sec)

Sekarang kita buatkan summary untuk table TRANSAKSI kedua, menggunakan GROUP BY dan fungsi aggregat SUM().

mysql> select tm.id, data, doc, sum(value) value2
    ->    from t_master tm
    ->    join t_transaksi_2 tr on tm.id=tr.id
    ->    group by id, doc  ;
+----+------+-------+---------+
| id | data | doc   | value12 |
+----+------+-------+---------+
|  2 | B    | DOC 2 |    4000 |
|  3 | C    | DOC 3 |    4000 |
|  3 | C    | DOC 4 |    6000 |
+----+------+-------+---------+
3 rows in set (0.00 sec)

Sekarang gabungkan SUMMARY dari TRANSAKSI pertama dan kedua, menggunakan LEFT JOIN menjadi seperti ini.


mysql> select res1.id, res1.data, res1.doc
    -> , value1
    -> , value2
    -> from (
    ->    select tm.id, data, doc, sum(value) value1
    ->    from t_master tm
    ->    join t_transaksi_1 tr on tm.id=tr.id
    ->    group by id, doc
    -> ) res1
    -> left join (
    ->    select tm.id, data, doc, sum(value) value2
    ->    from t_master tm
    ->    join t_transaksi_2 tr on tm.id=tr.id
    ->    group by id, doc
    -> ) res2 on res1.id=res2.id and res1.doc=res2.doc ;
+----+------+-------+--------+--------+
| id | data | doc   | value1 | value2 |
+----+------+-------+--------+--------+
|  1 | A    | DOC 1 |   3000 |   NULL |
|  2 | B    | DOC 2 |   5000 |   4000 |
|  3 | C    | DOC 3 |   3000 |   4000 |
|  3 | C    | DOC 4 |   5000 |   6000 |
+----+------+-------+--------+--------+
4 rows in set (0.00 sec)

Namun masih ada yang sedikit aneh, karena ada nilai NULL di sana, maka kita gunakan perintah IFNULL() untuk mengunbahknya menjadi angka 0 (nol).


mysql> select res1.id, res1.data, res1.doc
    -> , ifnull(value1,0) value1
    -> , ifnull(value2,0) value2
    -> from (
    ->    select tm.id, data, doc, sum(value) value1
    ->    from t_master tm
    ->    join t_transaksi_1 tr on tm.id=tr.id
    ->    group by id, doc
    -> ) res1
    -> left join (
    ->    select tm.id, data, doc, sum(value) value2
    ->    from t_master tm
    ->    join t_transaksi_2 tr on tm.id=tr.id
    ->    group by id, doc
    -> ) res2 on res1.id=res2.id and res1.doc=res2.doc ;
+----+------+-------+--------+--------+
| id | data | doc   | value1 | value2 |
+----+------+-------+--------+--------+
|  1 | A    | DOC 1 |   3000 |      0 |
|  2 | B    | DOC 2 |   5000 |   4000 |
|  3 | C    | DOC 3 |   3000 |   4000 |
|  3 | C    | DOC 4 |   5000 |   6000 |
+----+------+-------+--------+--------+
4 rows in set (0.00 sec)

Demikian TUTORIAL ini semoga membantu memahami penggunaan SUBQUERY, JOIN, GROUP BY, dan fungsi agregate SUM.

Oracle Magazine November 2012

superadmin August 28, 2013 No Comments
oracle magazine free download dunia database programmer

oracle magazine free download dunia database programmerI didn’t count the number of conversa-tions I had or heard that included the word, but I’m quite comfortable saying that cloudwas the word of the week at Oracle’s conferences and events in San Francisco, California, September 29 through October 5, 2012. There were announcements focused explicitly on Oracle cloud technologies and solutions, but cloud was also a big part of every Oracle product, technology, and service announcement and discussion.

Oracle CEO Larry Ellison’s Sunday keynote at Oracle OpenWorld 2012 was about cloud computing, including the history of cloud computing going back to NetSuite in 1998 and the beginnings of Oracle Fusion Applications development in 2004. Ellison discussed public and private clouds, the levels of cloud services—software as a service (SaaS), platform as a service (PaaS), and infrastructure as a service (IaaS)—and their key components, and Oracle’s latest offerings in each of these service areas.

Ellison talked about Oracle technologies designed specifically for better cloud com-puting, from infrastructure and private cloud services to database multitenancy and engi-neered systems. He also unveiled the latest Oracle engineered systems releases and discussed their infrastructure and platform support for cloud computing.

Download magazine here

Oracle Magazine on Pojok Programmer

Oracle Magazine September 2012

superadmin August 28, 2013 No Comments
oracle magazine free download dunia database programmer

oracle magazine free download dunia database programmerOracle Magazinepublishes some small amount of Oracle news every 60 days, and that’s not an apology or false modesty on behalf of the magazine. Oracle generates far more news about products, strategy, and tech-nology in a two-month period than we could possibly fit in the pages of this publication. (Take a look at the Oracle press release feed at pressroom.oracle.com to see what I mean—and to search for the latest Oracle news.)

Information technology news is also the kind of news that benefits from several dif-ferent approaches, formats, and presenta-tions. Oracle Magazinestrives to present Oracle news in news briefs and interviews, community briefs, customer reference stories, technology how-to content, and anal-ysis. But even these variations are not enough to guarantee that organizations have the news they need to see the potential impact the latest Oracle products, strategy, and tech-nology could have on their own operations.

Fortunately, as the volume of news about Oracle and Oracle technology has continued to expand, it has gotten easier to sepa-rate into components and address with a tighter focus. The aforementioned Oracle press releases are now broken into bite-size bullets, suitable as launch points for social media conversations. Oracle Web pages, data sheets, white papers, customer case studies, and newer-media assets including podcasts, Webcasts, and videos present new infor-mation in all sizes, to fit the time you have and the approach you’re looking for when exploring any area of Oracle technology.

Download magazine here

Oracle Magazine on Pojok Programmer

Oracle Magazine July 2012

superadmin August 28, 2013 No Comments
oracle magazine free download dunia database programmer

oracle magazine free download dunia database programmerThe ComputerWorld article gives examples of the many types of information contained in tables on the Web and points out that pro-gramming languages have developed features specifically for working with information in tables. The hard part of the “data stored in tables” truth presented in the article appears to be that tables are not new, but rather are years-old constructs that have organized the information of the internet in clearly defined structures. The article also points out that NoSQL isnew, but the hard truth there is that NoSQL is also about data in tables.

To me, the fact that most of the Web is data stored in tables is fantastic. The down-side to that truth is that only mostof the Web is in tables. And as for the hard truth that even a newer technology like NoSQL works with data in tables, I see only benefit in adding new capabilities for the structures that already contain most of the information of the Web.

Like many people, I’ve counted on tables—on the Web and in intranet applica-tions—for Web transactions and interactions as well as for storage and access to various types of information for years. For internal projects, I’ve used relational database tables in situations where I first did not understand how using tables would help, but in each case the truth was that using the tables was convenient and definitely added value to both the process and the result.

Download magazine here

Oracle Magazine on Pojok Programmer