MySQL - ArungRuang

Contoh Penggunaan JOIN dalam Perintah SQL SELECT

superadmin January 15, 2017 No Comments

[Belajar SQL](http://pojokprogrammer.net/tags/belajar-sql) | Setelah sebelumnya kita anatomi dan penggunaan perintah SQL SELECT untuk melakukan proses temu kembali data (*data retrieval*), maka langkah selanjutnya kita harus mempertajam ***Skill SQL***, dengan memperbanyak latihan, dan salah satu cara untuk melatih *skill SQL* adalah dengan cara mencoba membantu menyelesaikan problem SQL yang dihadapi orang lain. Beberapa situs yang bisa kita kunjungi untuk melatih *skill SQL* adalah [Oracle Community Forum](https://community.oracle.com/community/database/developer-tools/sql_and_pl_sql), namun berbahasa Inggris. Sedangkan untuk yang berbahasa Indonesia bisa mengunjungi [Programmer Forum di Kaskus](https://www.kaskus.co.id/thread/000000000000000007584619/sql-yang-punya-problem-sql-kumpul-disini-gan-no-urgent-please/). Dalam artikel ini kita akan membahas beberapa kasus berkaitan dengan SQL JOIN.
(more…)

Mengelola Data Hirarki di MySQL

superadmin August 13, 2016 No Comments

[**Database**](http://pojokprogrammer.net/tags/database) [**Tips**](http://pojokprogrammer.net/tags/tips-dan-trik) | Semua programmer yang berkonsentrasi di pengembangan aplikasi perkantoran (*office application*) pasti pernah berhadapan dengan data hirarki (***hierarchical data***) ke dalam database relasional (*relational database*) seperti MySQL. RDBMS sendiri tidak diperuntukkan untuk menyimpan data hirarki karena relasi antar table bukanlah relasi hirarki. Namun kita bisa merepresentasikan data hirarki menggunakan relasi ***self-reference***, atau relasi *foreign key* ke table yang sama.

**Data Hirarki** adalah sekumpulan data yang masing-masingnya memiliki satu ***parent*** dengan pengecualian untuk data ***root***. Masing-saing data dalam hirarki juga bisa memilik satu atau beberapa ***child***, atau tidak memiliki child untuk data pada level terendah, yang biasa disebut ***leaf***. Data hirarki yang umumnya terdapat dalam aplikasi perkantoran, seperti data departemen, cabang, kategori barang, chart of accounts, dan sebagainya. Dalam artikel ini kita akan mencoba menggunakan data ***Chart of Accounts*** atau ***Perkiraan*** yang sangat diperlukan dalam sebuah aplikasi akuntansi (*Accounting System*), seperti tampak pada gambar di bawah ini,

![Hirarki Perkiraan (Chart of Account COA Hierarchy](http://pojokprogrammer.net/sites/default/files/pictures/database/hirarki-01.png)
![Hirarki Perkiraan (Chart of Account COA Hierarchy](http://pojokprogrammer.net/sites/default/files/pictures/database/hirarki-02.png)

### Adjacency List Model

**Cara termudah** merepresentasikan data hirarki di dalam database relasional adalah dengan menggunakan model ***Senarai Ketetanggaan*** (***Adjacency List***). dalam model senarai ketetanggaan ini, setiap record memiliki pointer ke *parent* dari record tersebut. Khusus untuk *root* atau data paling atas tidak memiliki parent maka kita set NULL untuk *parent* dari *root*. Untuk contoh kasus kita, maka yang menjadi ***root*** adalah record ***Assets***, ***Liabilities***, dan ***Equities***.

**Keuntungan** penggunaan senarai ketetanggaan adalah kesederhanaannya, karena kita bisa dengan mudah melihat *parent* dari *Petty Cash* adalah *Current Assets*, yang sekaligus merupakan *child* dari *Assets*. Berikut ini adalah DDL dan DML yang kita gunakan sebagai contoh kasus.

CREATE TABLE accounts
(
  id integer  auto_increment,
  code varchar(255) NOT NULL,
  name varchar(255) NOT NULL,
  parent_id integer,
  PRIMARY KEY (id),
  FOREIGN KEY (parent_id)
      REFERENCES accounts (id) 
      ON UPDATE CASCADE ON DELETE RESTRICT
);

INSERT INTO accounts (id, code, name, parent_id) VALUES(1000, '1000', 'Assets', NULL);
INSERT INTO accounts (id, code, name, parent_id) VALUES(1100, '1100', 'Current Assets', 1000);
INSERT INTO accounts (id, code, name, parent_id) VALUES(1110, '1110', 'Petty Cash', 1100);
INSERT INTO accounts (id, code, name, parent_id) VALUES(1120, '1120', 'Savings Account', 1100);
INSERT INTO accounts (id, code, name, parent_id) VALUES(1130, '1130', 'Deposits Account', 1100);
INSERT INTO accounts (id, code, name, parent_id) VALUES(1150, '1150', 'Accounts Receivables', 1100);
INSERT INTO accounts (id, code, name, parent_id) VALUES(1200, '1200', 'Fixed Assets', 1000);
INSERT INTO accounts (id, code, name, parent_id) VALUES(1210, '1210', 'Furnitures', 1200);
INSERT INTO accounts (id, code, name, parent_id) VALUES(1220, '1220', 'Equipments', 1200);
INSERT INTO accounts (id, code, name, parent_id) VALUES(1230, '1230', 'Vehicles', 1200);
INSERT INTO accounts (id, code, name, parent_id) VALUES(1250, '1250', 'Buildings', 1200);
INSERT INTO accounts (id, code, name, parent_id) VALUES(1900, '1900', 'Other Assets', 1000);
INSERT INTO accounts (id, code, name, parent_id) VALUES(1910, '1910', 'Patents', 1900);
INSERT INTO accounts (id, code, name, parent_id) VALUES(1990, '1990', 'Other Assets', 1900);
INSERT INTO accounts (id, code, name, parent_id) VALUES(2000, '2000', 'Liabilities', NULL);
INSERT INTO accounts (id, code, name, parent_id) VALUES(2100, '2100', 'Current Liabilities', 2000);
INSERT INTO accounts (id, code, name, parent_id) VALUES(2110, '2110', 'Accrued Expenses', 2100);
INSERT INTO accounts (id, code, name, parent_id) VALUES(2120, '2120', 'Accounts Payable', 2100);
INSERT INTO accounts (id, code, name, parent_id) VALUES(2130, '2130', 'Tax Payable', 2100);
INSERT INTO accounts (id, code, name, parent_id) VALUES(2200, '2200', 'Long-Term Liabilities', 2000);
INSERT INTO accounts (id, code, name, parent_id) VALUES(2210, '2210', 'Equipments Payable', 2200);
INSERT INTO accounts (id, code, name, parent_id) VALUES(2220, '2220', 'Vehicles Payable', 2200);
INSERT INTO accounts (id, code, name, parent_id) VALUES(2240, '2240', 'Buildings Payable', 2200);
INSERT INTO accounts (id, code, name, parent_id) VALUES(2250, '2250', 'Loans Payable', 2200);
INSERT INTO accounts (id, code, name, parent_id) VALUES(3000, '3000', 'Equity', NULL);
INSERT INTO accounts (id, code, name, parent_id) VALUES(3100, '3100', 'Capital', 3000);
INSERT INTO accounts (id, code, name, parent_id) VALUES(3110, '3110', 'Founders Capital', 3100);
INSERT INTO accounts (id, code, name, parent_id) VALUES(3120, '3120', 'Invenstors Capital', 3100);
INSERT INTO accounts (id, code, name, parent_id) VALUES(3190, '3190', 'Others Capital', 3100);
INSERT INTO accounts (id, code, name, parent_id) VALUES(3200, '3200', 'Earnings', 3000);
INSERT INTO accounts (id, code, name, parent_id) VALUES(3210, '3210', 'Retained Earnings', 3200);
INSERT INTO accounts (id, code, name, parent_id) VALUES(3220, '3220', 'Current Earnings', 3200);

### Membaca Keseluruhan Hirarki

Hal **yang pasti kita hadapi** saat mengelola data hirarki adalah menampilkan keseluruhan data, biasanya berbentuk ***tree*** dengan tambahan indentasi untuk level yang lebih rendah. Cara paling mudah untuk mencapai hal ini di MySQL adalah dengan menggunakan ***self-join***. Berikut ini **query hirarki** (***hierarchical query***) untuk mendapatkan *tree* ***Assets***

SELECT t1.id, t1.code
, t1.name AS name_level1, t2.name as name_level2
, t3.name AS name_level3, t4.name as name_level4
FROM accounts AS t1
LEFT JOIN accounts AS t2 ON t2.parent_id = t1.id
LEFT JOIN accounts AS t3 ON t3.parent_id = t2.id
LEFT JOIN accounts AS t4 ON t4.parent_id = t3.id
WHERE t1.name = 'Assets'
ORDER BY t1.code, t2.code, t3.code, t4.code

Hasilnya akan tampak seperti ini

+------+------+-------------+----------------+----------------------+-------------+
| id   | code | name_level1 | name_level2    | name_level3          | name_level4 |
+------+------+-------------+----------------+----------------------+-------------+
| 1000 | 1000 | Assets      | Current Assets | Petty Cash           | NULL        |
| 1000 | 1000 | Assets      | Current Assets | Savings Account      | NULL        |
| 1000 | 1000 | Assets      | Current Assets | Deposits Account     | NULL        |
| 1000 | 1000 | Assets      | Current Assets | Accounts Receivables | NULL        |
| 1000 | 1000 | Assets      | Fixed Assets   | Furnitures           | NULL        |
| 1000 | 1000 | Assets      | Fixed Assets   | Equipments           | NULL        |
| 1000 | 1000 | Assets      | Fixed Assets   | Vehicles             | NULL        |
| 1000 | 1000 | Assets      | Fixed Assets   | Buildings            | NULL        |
| 1000 | 1000 | Assets      | Other Assets   | Patents              | NULL        |
| 1000 | 1000 | Assets      | Other Assets   | Other Assets         | NULL        |
+------+------+-------------+----------------+----------------------+-------------+
10 rows in set (0.00 sec)

### Menambahkan *Level* dan *Path*

Jika kita ingin menambahkan kolom yang berisikan level dari masing-masing record, maka kita harus lebih kreatif dalam membangun ***Hierarchical Query*** ini. Karena MySQL belum mendukung ***Common Table Expression*** untuk melakukan ***Recursive Query***, maka yang perlu kita lakukan adalah melakukan query untuk masing-masing level dan kemudian menggabungkannya menggunakan **UNION**. Perhatikan dalam query kali ini kita menggunakan **INNER JOIN**

-- Level 1
SELECT t1.id, t1.code, t1.name, 1 AS level
, t1.name AS name_level1, null as name_level2
, null as name_level3, null as name_level4
, t1.code AS path
FROM accounts AS t1
WHERE t1.parent_id is null
-- Level 2
UNION ALL
SELECT t2.id, t2.code, t2.name, 2 AS level
, t1.name AS name_level1, t2.name as name_level2
, null as name_level3, null as name_level4
, concat(t1.code,'/',t2.code) AS path
FROM accounts AS t1
JOIN accounts AS t2 ON t2.parent_id = t1.id
WHERE t1.parent_id is null
-- Level 3
UNION ALL
SELECT t3.id, t3.code, t3.name, 3 AS level
, t1.name AS name_level1, t2.name as name_level2
, t3.name AS name_level3, null as name_level4
, concat(t1.code,'/',t2.code,'/',t3.code) AS path
FROM accounts AS t1
JOIN accounts AS t2 ON t2.parent_id = t1.id
JOIN accounts AS t3 ON t3.parent_id = t2.id
WHERE t1.parent_id is null
-- Level 3
UNION ALL
SELECT t4.id, t4.code, t4.name, 4 AS level
, t1.name AS name_level1, t2.name as name_level2
, t3.name AS name_level3, t4.name as name_level4
, concat(t1.code,'/',t2.code,'/',t3.code,'/',t4.code) AS path
FROM accounts AS t1
JOIN accounts AS t2 ON t2.parent_id = t1.id
JOIN accounts AS t3 ON t3.parent_id = t2.id
JOIN accounts AS t4 ON t4.parent_id = t3.id
WHERE t1.parent_id is null
-- Ordering Result
ORDER BY path

Hasilnya akan tampak seperti ini

+------+------+-----------------------+-------+-------------+-----------------------+----------------------+-------------+----------------+
| id   | code | name                  | level | name_level1 | name_level2           | name_level3          | name_level4 | path           |
+------+------+-----------------------+-------+-------------+-----------------------+----------------------+-------------+----------------+
| 1000 | 1000 | Assets                |     1 | Assets      | NULL                  | NULL                 | NULL        | 1000           |
| 1100 | 1100 | Current Assets        |     2 | Assets      | Current Assets        | NULL                 | NULL        | 1000/1100      |
| 1110 | 1110 | Petty Cash            |     3 | Assets      | Current Assets        | Petty Cash           | NULL        | 1000/1100/1110 |
| 1120 | 1120 | Savings Account       |     3 | Assets      | Current Assets        | Savings Account      | NULL        | 1000/1100/1120 |
| 1130 | 1130 | Deposits Account      |     3 | Assets      | Current Assets        | Deposits Account     | NULL        | 1000/1100/1130 |
| 1150 | 1150 | Accounts Receivables  |     3 | Assets      | Current Assets        | Accounts Receivables | NULL        | 1000/1100/1150 |
| 1200 | 1200 | Fixed Assets          |     2 | Assets      | Fixed Assets          | NULL                 | NULL        | 1000/1200      |
| 1210 | 1210 | Furnitures            |     3 | Assets      | Fixed Assets          | Furnitures           | NULL        | 1000/1200/1210 |
| 1220 | 1220 | Equipments            |     3 | Assets      | Fixed Assets          | Equipments           | NULL        | 1000/1200/1220 |
| 1230 | 1230 | Vehicles              |     3 | Assets      | Fixed Assets          | Vehicles             | NULL        | 1000/1200/1230 |
| 1250 | 1250 | Buildings             |     3 | Assets      | Fixed Assets          | Buildings            | NULL        | 1000/1200/1250 |
| 1900 | 1900 | Other Assets          |     2 | Assets      | Other Assets          | NULL                 | NULL        | 1000/1900      |
| 1910 | 1910 | Patents               |     3 | Assets      | Other Assets          | Patents              | NULL        | 1000/1900/1910 |
| 1990 | 1990 | Other Assets          |     3 | Assets      | Other Assets          | Other Assets         | NULL        | 1000/1900/1990 |
| 2000 | 2000 | Liabilities           |     1 | Liabilities | NULL                  | NULL                 | NULL        | 2000           |
| 2100 | 2100 | Current Liabilities   |     2 | Liabilities | Current Liabilities   | NULL                 | NULL        | 2000/2100      |
| 2110 | 2110 | Accrued Expenses      |     3 | Liabilities | Current Liabilities   | Accrued Expenses     | NULL        | 2000/2100/2110 |
| 2120 | 2120 | Accounts Payable      |     3 | Liabilities | Current Liabilities   | Accounts Payable     | NULL        | 2000/2100/2120 |
| 2130 | 2130 | Tax Payable           |     3 | Liabilities | Current Liabilities   | Tax Payable          | NULL        | 2000/2100/2130 |
| 2200 | 2200 | Long-Term Liabilities |     2 | Liabilities | Long-Term Liabilities | NULL                 | NULL        | 2000/2200      |
| 2210 | 2210 | Equipments Payable    |     3 | Liabilities | Long-Term Liabilities | Equipments Payable   | NULL        | 2000/2200/2210 |
| 2220 | 2220 | Vehicles Payable      |     3 | Liabilities | Long-Term Liabilities | Vehicles Payable     | NULL        | 2000/2200/2220 |
| 2240 | 2240 | Buildings Payable     |     3 | Liabilities | Long-Term Liabilities | Buildings Payable    | NULL        | 2000/2200/2240 |
| 2250 | 2250 | Loans Payable         |     3 | Liabilities | Long-Term Liabilities | Loans Payable        | NULL        | 2000/2200/2250 |
| 3000 | 3000 | Equity                |     1 | Equity      | NULL                  | NULL                 | NULL        | 3000           |
| 3100 | 3100 | Capital               |     2 | Equity      | Capital               | NULL                 | NULL        | 3000/3100      |
| 3110 | 3110 | Founders Capital      |     3 | Equity      | Capital               | Founders Capital     | NULL        | 3000/3100/3110 |
| 3120 | 3120 | Invenstors Capital    |     3 | Equity      | Capital               | Invenstors Capital   | NULL        | 3000/3100/3120 |
| 3190 | 3190 | Others Capital        |     3 | Equity      | Capital               | Others Capital       | NULL        | 3000/3100/3190 |
| 3200 | 3200 | Earnings              |     2 | Equity      | Earnings              | NULL                 | NULL        | 3000/3200      |
| 3210 | 3210 | Retained Earnings     |     3 | Equity      | Earnings              | Retained Earnings    | NULL        | 3000/3200/3210 |
| 3220 | 3220 | Current Earnings      |     3 | Equity      | Earnings              | Current Earnings     | NULL        | 3000/3200/3220 |
+------+------+-----------------------+-------+-------------+-----------------------+----------------------+-------------+----------------+
32 rows in set (0.01 sec)

### Mencari Semua *Leaf Node*

Kita juga bisa mendapatkan semua ***leaf node***, yaitu node pada level terbawah dan tidak memiliki **child**, dengan menggunakan **LEFT JOIN**

-- Level 1
SELECT t1.id, t1.code
, t1.name AS name_level1, t2.name as name_level2
, t3.name AS name_level3, t4.name as name_level4
FROM accounts AS t1
LEFT JOIN accounts AS t2 ON t2.parent_id = t1.id
LEFT JOIN accounts AS t3 ON t3.parent_id = t2.id
LEFT JOIN accounts AS t4 ON t4.parent_id = t3.id
WHERE t1.parent_id is null
ORDER BY t1.code, t2.code, t3.code, t4.code

Hasilnya adaah seperti ini.

+------+------+-------------+-----------------------+----------------------+-------------+
| id   | code | name_level1 | name_level2           | name_level3          | name_level4 |
+------+------+-------------+-----------------------+----------------------+-------------+
| 1000 | 1000 | Assets      | Current Assets        | Petty Cash           | NULL        |
| 1000 | 1000 | Assets      | Current Assets        | Savings Account      | NULL        |
| 1000 | 1000 | Assets      | Current Assets        | Deposits Account     | NULL        |
| 1000 | 1000 | Assets      | Current Assets        | Accounts Receivables | NULL        |
| 1000 | 1000 | Assets      | Fixed Assets          | Furnitures           | NULL        |
| 1000 | 1000 | Assets      | Fixed Assets          | Equipments           | NULL        |
| 1000 | 1000 | Assets      | Fixed Assets          | Vehicles             | NULL        |
| 1000 | 1000 | Assets      | Fixed Assets          | Buildings            | NULL        |
| 1000 | 1000 | Assets      | Other Assets          | Patents              | NULL        |
| 1000 | 1000 | Assets      | Other Assets          | Other Assets         | NULL        |
| 2000 | 2000 | Liabilities | Current Liabilities   | Accrued Expenses     | NULL        |
| 2000 | 2000 | Liabilities | Current Liabilities   | Accounts Payable     | NULL        |
| 2000 | 2000 | Liabilities | Current Liabilities   | Tax Payable          | NULL        |
| 2000 | 2000 | Liabilities | Long-Term Liabilities | Equipments Payable   | NULL        |
| 2000 | 2000 | Liabilities | Long-Term Liabilities | Vehicles Payable     | NULL        |
| 2000 | 2000 | Liabilities | Long-Term Liabilities | Buildings Payable    | NULL        |
| 2000 | 2000 | Liabilities | Long-Term Liabilities | Loans Payable        | NULL        |
| 3000 | 3000 | Equity      | Capital               | Founders Capital     | NULL        |
| 3000 | 3000 | Equity      | Capital               | Invenstors Capital   | NULL        |
| 3000 | 3000 | Equity      | Capital               | Others Capital       | NULL        |
| 3000 | 3000 | Equity      | Earnings              | Retained Earnings    | NULL        |
| 3000 | 3000 | Equity      | Earnings              | Current Earnings     | NULL        |
+------+------+-------------+-----------------------+----------------------+-------------+
22 rows in set (0.00 sec)

### Kesimpulan

Meskipun **terlihat mudah**, ada beberapa hal yang perlu kita perhatikan dalam mengelola data hirarki di MySQL ini,

– Kita harus mengetahui jumlah level sejak awal
– Setiap ada penambahan level, maka kita juga harus menambahkan ***left join*** lagi ke dalam query. Perlu diketahui bahwa semakin banyak *left join* maka kinerja (*performance*) mysql akan menurun.
– Penghapusan record (*node*) tidak boleh dilakukan secara sembarangan karena ada resiko tree akan berantakan jika ada child yang mengacu ke node yang dihapus. Namun hal ini bisa diatasi dengan ***Foreign Key Constraint***.

Cara lain untuk mengembangkan query hirarki ini adalah dengan menggunakan ***stored procedure*** untuk membangun query secara dinamis sehingga bisa mengantisipasi jumlah level yang lebeih banyak dan bahkan tidak terbatas.

Semoga berguna!

Aplikasi Inventory Sederhana – Form Transaksi Barang Masuk

superadmin July 20, 2015 No Comments

Form Transaksi Barang Masuk | Setelah sebelumnya kita berhasil membuat form entri master barang, berikutnya kita akan membuat form untuk mengelola proses CRUD (Create, Read, Update, Delete) data Transaksi Barang Masuk di Aplikasi Inventory ini. Untuk mengelola data master barang ini, kita memerlukan 2 (dua) buah form, yaitu Form List Transaksi Barang Masuk, dan Form Entri Transaksi Barang Masuk. Sama seperti form untuk mengelola data master barang, kedua form transaksi ini juga akan menggunakan metode data-binding untuk menampilkan data yang berhasil diambil menggunakan library data access layer yang kita buat sebelumnya.

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

Persiapkan Data Access Layer

Form transaksi ini merupakan form master-detail yang melibatkan 2 (dua) buah table, yaitu transactions sebagai master table, dan transaction_details sebagai detail table. Desain dari kedua table ini dapat di lihat dalam artikel ini. Untuk itu kita perlu mendefinisikan relasi foreign key antar DataTable yang ada di dalam DataSet dsInventory. Relasi ini yang nantinya akan sangat membantu kita dalam proses data-binding ke form transaksi.

[collapsed title=Setting Relasi Foreign Key]Pertama-tama bukalah desain DataSet dsInventory, kemudian cari link relasi antara table transactions dengan table transaction_details, setelah itu klik tombol kanan mouse untuk menampilkan context menu seperti gambar di bawah ini.

Setelah ini pilihlah menu Edit Relation, dan kemudian ubahlah setting-nya menjadi “Both Relation and Foreign Key Contraints” dan ubah juga setting Update Rule dan Delete Rule menjadi Cascade, seperti tampak pada gambar di bawah ini.

[/collapsed] [collapsed title=Setting Query Berdasarkan Jenis Transaksi]

Berikutnya kita tambahkan Query untuk memudahkan proses data retrieval (temu kembali data) di form list transaksi barang masuk, Caranya dengan membuka DataSet dsInventory, kemudian lakukan klik kanan pada table transactions dan pilih menu “Add Query” seperti tampak pada gambar di bawah ini.

Setelah itu berikan nama method sebagai FillByType dan GetDataByType, karena method ini kita gunakan untuk mengambil data berdasarkan tipe atau jenis transaksinya. Hasilnya akan tampak seperti gambar di bawah ini.

[/collapsed]

Desain Form Entri Transaksi Barang Masuk

Berikutnya kita buat desain form transaksi barang masuk. Desain form transaksi ini akan menggunakan Visual Inheritance seperti yang sudah dibahas sebelumnya di artikel desain user interface dan desain form master barang, jadi silakan pelajari kedua artikel tersebut sebelum melanjutkan membaca artikel ini.

[collapsed title=Desain Form Entri Barang Masuk]Silakan buat sebuah Inherited Form dari template form List dan kemudian tambahkan semua kontrol yang diperlukan antara lain

TextBox untuk Kode Transaksi
DateTimePicker untuk Tanggal Transaksi
Multiline TextBox untuk field Remarks atau Keterangan
DataGridView untuk detail transaksi

Tambahkan juga beberapa komponen

DataSet DsInventory
TransactionsTableAdapter untuk proses retrieval dan saving data di table transactions.
Transaction_detailsTableAdapter untuk proses retrieval dan saving data di table transaction_details.
ItemsTableAdapter untuk proses retrieval lookup data items

Hasilnya akan tampak seperti gambar di bawah ini.

Setelah desain form selesai kita buat, lanjutkan dengan setting data-binding pada property Text untuk field Transaction Code seperti tampak pada gambar di bawah ini. Lakukan juga hal yang sama untuk field Transaction Date, Remarks dan DataGridView. Gambar di bawah adalah contoh setting data-binding property Text untuk Transaction Code.

[/collapsed]

Berikutnya yang perlu kita lakukan adalah mengkonfigurasikan binding DataGridView agar memudahkan kita menambahkan detail barang yang terlibat dalam transaksi bersangkutan. Khusus untuk binding DataGridView, yang kita lakukan adalah setting property DataMember dari DataGridView ke relasi yang sudah kita buat sebelumnya, yaitu transaction_details_ibfk_1, seperti tampak pada gambar di bawah ini.

[collapsed title=Setting DataGridView untuk Master-Detail]

[/collapsed]

Setelah setting binding kita lakukan, selanjutnya ubah property Visible untuk beberapa kolom yang tidak perlu dilihat oleh user atau pengguna, misalkan kolom id, trans_id, item_code, dan item_name. Caranya adalah dengan melakukan klik kanan mouse di atas DataGridView, kemudian pilih menu “Edit Columns” setelah itu silakan ubah property visible menjadi false, seperti tampak pada gambar di bawah ini.

[collapsed title=Setting DataGridView untuk Master-Detail]

[/collapsed]

Untuk memudahkan pengguna memilih barang yang akan masuk dalam transaksi ini, maka kita buatkan sebuah ComboBox berisikan list Items sehingga user dapat langsung memilih dari daftar yang tersedia. Caranya adalah buka menu “Edit Columns” kemudian pilih field item_id dan set ColumnType sebagai ComboBoxColumn, seperti tampak pada gambar di bawah ini.

[collapsed title=Setting ComboBox Items di DataGridView untuk Master-Detail]

[/collapsed]

Berikutnya kita beritahukan vb.net untuk mengambil data Items dari DataSet DsInventory dengan cara mengkonfigurasikan property DataSource, DisplayMember, dan ValueMember menjadi seperti tampak pada gambar di bawah ini.

[collapsed title=Setting ComboBox Items di DataGridView untuk Master-Detail]

[/collapsed]

Hasil akhirnya akan tampak seperti pada gambar di bawah ini.

[collapsed title=Setting ComboBox Items di DataGridView untuk Master-Detail]

[/collapsed]

Kode Sumber Form Entri Transaksi Barang Masuk

Kode sumber untuk proses penyimpanan data dalam model master-detail agak sedikit sulit dikarenakan setelah menyimpan data baru ke master table, kita harus mengambil id auto_increment yang dihasilkan dari database kemudian menyesuaikannya ke data di detail table agar referential integrity tetap terjaga.

[collapsed title=Kode Sumber Constructor]

Constructur New kita buat dua versi untuk membedakan proses Add atau Edit, karena perlakuannya berbeda pada saat penyimpanan data, asumsinya adalah jika TransID yang diberikan bernilai kurang dari 0 (nol) maka kita anggap menambahkan data baru.

    Public Sub New()
        ' This call is required by the designer.
        InitializeComponent()
        ' panggil inisialisasi database
        InitializeDatabase(-1)
    End Sub

    Public Sub New(TransID As Integer)
        ' This call is required by the designer.
        InitializeComponent()
        ' panggil inisialisasi database
        InitializeDatabase(TransID)
    End Sub

[/collapsed]

[collapsed title=Kode Sumber Inisialisasi Database]

Inisialisasi database membedakan proses add new dan edit data seperti tampak pada potongan kode berikut ini.

    Private Sub InitializeDatabase(TransID As Integer)
        ' load lookup/reference table
        Me.ItemsTableAdapter.Fill(Me.DsInventory.items)
        ' try retrieve data
        Try
            ' periksa nilai ItemID
            ' jika kurang dari 0 maka kita asumsikan mau bikin data baru
            ' jika lebih dari 0 maka kita asumsikan ambil bikin data lama
            If TransID < 0 Then
                IsNewData = True
                ' tambahkan row baru
                Me.BindingContext(Me.DsInventory.transactions).AddNew()
                ' nilai default untuk beberapa field
                Dim NewID As String = Helper.GenerateNewID(TransCode)
                Dim dv As DataRowView = Me.BindingContext(Me.DsInventory.transactions).Current
                dv("type_id") = 1 ' IN: barang masuk
                dv("trans_code") = NewID
                dv("trans_date") = DateTime.Today
                TextBox1.Text = NewID
                DateTimePicker1.Value = DateTime.Today
            Else
                IsNewData = False
                ' retrieve data dari database
                Me.TransactionsTableAdapter.FillByID(Me.DsInventory.transactions, TransID)
                Me.Transaction_detailsTableAdapter.FillByTransID(Me.DsInventory.transaction_details, TransID)
            End If
        Catch ex As Exception
            MessageBox.Show("Error retrieving data from database\nDetails: " + ex.Message,
                Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        End Try
    End Sub

[/collapsed]

[collapsed title=Kode Sumber Simpan Data]

Proses penyimpanan data melibatkan dua table seperti pada potongan kode di bawah ini, silakan perhatikan komentar pada tiap baris kode yang menjelaskan maksud dari masing-masing baris kode yang ada.

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        ' try saving data
        Try
            ' akhiri proses editing
            Me.BindingContext(Me.DsInventory.transactions).EndCurrentEdit()
            Me.BindingContext(Me.DsInventory.transaction_details).EndCurrentEdit()
            ' cek apakah add new atau edit data
            If (IsNewData) Then
                Dim dsChanges As New dsInventory
                ' copy master record dari main dataset
                ' harus dilakukan krena main dataset sebelumnya sudah 
                ' AcceptChanges padahal belum diupdate ke database 
                dsChanges.transactions.Rows.Add(Me.DsInventory.transactions(0).ItemArray)
                ' copy juga detail record dari main dataset
                For i = 0 To Me.DsInventory.transaction_details.Rows.Count - 1
                    dsChanges.transaction_details.Rows.Add(Me.DsInventory.transaction_details(i).ItemArray)
                Next

                ' simpan perubahan ke master table
                Me.TransactionsTableAdapter.Update(dsChanges.transactions)

                ' retrieve generated identity field
                Dim id As Long = 0
                Dim sql As String = "select last_insert_id()"
                Using cn As New MySqlConnection(My.Settings.inventoryConnectionString)
                    Using cm As New MySqlCommand(sql, cn)
                        cn.Open()
                        id = cm.ExecuteScalar
                        cn.Close()
                    End Using
                End Using

                ' assign retrieved id to master table
                dsChanges.transactions(0).id = id
                dsChanges.transactions.AcceptChanges()
                ' simpan perubahan ke details table
                Me.Transaction_detailsTableAdapter.Update(dsChanges.transaction_details)

            Else
                ' simpan perubahan ke database
                Me.TransactionsTableAdapter.Update(Me.DsInventory.transactions)
                Me.Transaction_detailsTableAdapter.Update(Me.DsInventory.transaction_details)
            End If
            ' informasikan data berhasil disimpan
            MessageBox.Show("Data BERHASIL disimpan!",
                Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information)
            ' tutup form
            Me.Close()
        Catch ex As Exception
            MessageBox.Show("Error saving data to database " + vbCrLf + "Details: " + ex.Message,
                Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        End Try
    End Sub

[/collapsed]

Desain Form List Transaksi Barang Masuk

Berikutnya kita buat desain form list transaksi barang masuk. Desain form list transaksi ini juga akan menggunakan Visual Inheritance seperti yang sudah dibahas sebelumnya di artikel desain user interface dan cara melakukan setting data-binding untuk grid list transaksi bisa di lihat di artikel desain form master barang, jadi silakan pelajari kedua artikel tersebut sebelum melanjutkan membaca artikel ini.

[collapsed title=Desain Form List Transaksi Barang Masuk]

[/collapsed]

Kode Sumber Form List Transaksi Barang Masuk

Secara umum hampir tidak ada perbedaan pada code di artikel sebelumnya, hanya ada penambahan variable TransCode untuk membedakan jenis transaksi yang akan dimuat dari dalam database. Silakan perhatikan dengan seksama komentar yang terdapat dalam baris kode sumber, karena komentar-komentar tersebut menerangkan tujuan dari baris-baris kode yang ada

[collapsed title=Kode Sumber Constructor dan Refresh Data]

    ' kode jenis transaksi
    Private TransCode As String = "IN"

    Private Sub frmTransactionIn_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ' retrieve data from database
        Me.TransactionsTableAdapter.FillByType(Me.DsInventory.transactions, TransCode)
    End Sub

    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        Me.TransactionsTableAdapter.ClearBeforeFill = True
        Me.TransactionsTableAdapter.FillByType(Me.DsInventory.transactions, TransCode)
    End Sub

[/collapsed]

[collapsed title=Kode Sumber Loading Form Entri Data]Perbedaan Add dan Edit data adalah kita melempar parameter id transaksi ke form entri sebagai kondisi kita melakukan edit data

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        ' start process
        Cursor = Cursors.AppStarting
        ' prepare variables
        Dim fx As frmTransInEntry
        fx = New frmTransInEntry
        ' tampilkan form
        fx.ShowDialog()
        ' refresh data, ambil data yang berubah
        Button4.PerformClick()
        ' finish process
        Cursor = Cursors.Default
    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        ' start process
        Cursor = Cursors.AppStarting
        ' prepare variables
        Dim id As Integer
        Dim fx As frmTransInEntry
        ' ambil id data yg akan diedit
        id = DataGridView1.SelectedRows(0).Cells("id").Value
        ' create form entry berdasarkan id yang dipilih
        fx = New frmTransInEntry(id)
        ' refresh data, ambil data yang berubah
        Button4.PerformClick()
        ' tampilkan form
        fx.ShowDialog()
        ' finish process
        Cursor = Cursors.Default
    End Sub

[/collapsed]

[collapsed title=Kode Sumber Hapus Data Transaksi]

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        ' start process
        Cursor = Cursors.AppStarting
        ' prepare variables
        Dim dr As DialogResult
        Dim id As Integer
        Dim code As String
        Dim name As String
        Dim message As String
        Dim pos As Integer
        ' ambil id dan code dari data yg akan diedit
        Dim dv As DataRowView
        dv = Me.BindingContext(Me.TransactionsBindingSource).Current
        id = Convert.ToInt32(dv("id"))
        code = Convert.ToString(dv("code"))
        name = Convert.ToString(dv("name"))
        ' buat pesan konfirmasi
        message = String.Format("Are you sure to delete this [{0}] {1} data ? ", code, name)
        ' try deleting
        Try
            dr = MessageBox.Show(message,
                Application.ProductName, MessageBoxButtons.YesNo, MessageBoxIcon.Question)
            If dr = Windows.Forms.DialogResult.Yes Then
                pos = Me.BindingContext(Me.TransactionsBindingSource).Position
                Me.BindingContext(Me.TransactionsBindingSource).RemoveAt(pos)
                Me.TransactionsTableAdapter.Update(Me.DsInventory)
                Me.DsInventory.AcceptChanges()
            End If
        Catch ex As Exception
            MessageBox.Show("Error deleting data from database\nDetails: " + ex.Message,
                Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Me.DsInventory.RejectChanges()
        End Try
        ' finish up
        Cursor = Cursors.Default
    End Sub

[/collapsed]

Simpulan

Jika kita perhatikan kode sumber yang kita tuliskan di aplikasi ini sangat sedikit sekali dikarenakan kita menggunakan metode data-binding. Kita juga tidak perlu lagi repot-repot membuat perintah SQL untuk proses CRUD, karena proses CRUD ini sudah ditangani di data access layer. Dengan makin sedikit-nya kode yang harus kita tuliskan tentunya akan semakin meningkatkan produktifitas kita semua sebagai programmer dan semakin banyak karya-karya fenomenal yang bisa kita buat.

Source Code

Untuk memudahkan teman-teman mempelajari hal ini, silakan Download Source Code Di Sini. Dibuat menggunakan Visual Studio 2013,

Salam PojokProgrammer ^_^

Aplikasi Inventory Sederhana – Data Access Layer

superadmin June 20, 2014 No Comments

Aplikasi Inventory | Untuk memudahkan proses pembuatan Aplikasi Inventory sederhana atau Aplikasi Stok Barang ini, kita memerlukan sebuah Data Access Layer untuk proses pengambilan (retrieval) dan penyimpanan (persisting) data dari dan ke database yang kita gunakan, yaitu MySQL. Teknologi yang kita gunakan adalah ADO.net menggunakan DataSet dan DataAdapter. Dan untuk memudahkan kerja kita, maka kita akan menggunakan MySQL Installer for Windows untuk proses instalasi driver MySQL Connector/NET dan plugin MySQL for Visual Studio. Kekuatan ADO.net salah satunya ada di DataSet, dimana kita bisa membuat replika dari database fisik dan kemudian kita bisa menggunakannya secara object-oriented.

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

Siapkan Koneksi Database

Langkah pertama adalah dengan membuka Server Explorer kemudian menambahkan koneksi baru ke database MySQL yang sudah kita siapkan di artikel sebelumnya. Perlu dicatat bahwa kita memerlukan plugin MySQL for Visual Studio agar dapat melakukan langkah-langkah tutorial di bawah ini, dan plugin ini hanya bisa digunakan di Visual Studio Professional ke atas, pada versi Express plugin ini tidak bisa digunakan.

Silakan tambahkan koneksi baru dan isikanlah form konfigurasi database, seperti contoh pada gambar di bawah ini

[collapsed title=Membuat Koneksi Baru di Server Explorer]

Pada Server Explorer, click icon Add New Connection sehingga muncul dialog box seperti di bawah ini

[collapsed title=Tentukan Data Source]

[/collapsed]

Kemudian isikan credential yang diperlukan untuk mengakses database mysql Anda, jika menggunakan paket xampp di localhost maka cukup isikan root sebagai user name dan kosongkan password.

[collapsed title=Isikan Credential Database]

[/collapsed]

Jika sukses, Anda dapat melihat seluruh tables, viwes, dan stored procedure yang ada di database.

[collapsed title=Penampakan Database di Server Explorer]

[/collapsed]

Membuat Typed-DataSet

Kekuatan ADO.net salah satunya ada di DataSet, dimana kita bisa membuat replika dari database fisik dan kemudian kita bisa menggunakannya secara object-oriented. Sedangkan DataSet sendiri ada 2 (dua) jenis, untyped-dataset dan typed-dataset. Disebut typed-dataset karena DataSet yang kita gunakan bukan dataset generic namun sudah dikonfigurasikan sesuai dengan schema database tertentu. Dengan menggunakan plugin MySQL for Visual Studio, kita bisa membuat Typed DataSet dengan cepat hanya dengan melakukan drag-and-drop dari Server Explorer.

[collapsed title=Membuat DataSet dengan Cara Drag-and-Drop dari Server Explorer]

Untuk membuat Typed DataSet pertama-tama buat sebuah Folder bernama Data di Solution Explorer, kemudian klik-kanan mouse pada folder Data tersebut dan pilihlah menu Add New Item

[collapsed title=Membuat DataSet kosong]

[/collapsed]

Berikan nama DataSet tersebut sebagai dsInventory, cukup logis karena kita sedang membuat sebuah Aplikasi Inventory. DataSet Designer kosong akan muncul seperti tampak pada gambar di screenshot ini.

[collapsed title=DataSet Designer kosong dengan nama dsInventory]

[/collapsed]

Drag-And-Drop. Langkah selanjutnya adalah membuka Server Explorer, kemudian lakukan Drag-And-Drop semua table yang ada di database ke atas DataSet Designer, sehingga akan terbentuk seperti gambar di bawah ini.

[collapsed title=Hasil setelah proses Drag-And-Drop dari Server Explorer]

[/collapsed]

Perlu diperhatikan bahwa Visual Studio secara otomatis membantu kita membuatkan semua fungsi SELECT, INSERT, UPDATE, dan DELETE dari semua table di atas pada saat proses Drag-And-Drop sehingga kita tidak perlu membuat semua query tersebut secara manual. Pastinya cara ini sangat meningkatkan produktifitas para programmer karena hal-hal seperti ini bisa kita otomasikan menggunakan Visual Studio.

[/collapsed]

Enhancing DataSet – Field Tambahan

Setelah kita berhasil membuat Typed-DataSet sesuai langkah-langkah di atas, langkah selanjutnya adalah melakukan enhancing, atau memberikan fungsi tambahan, terhadap DataSet yang sudah dibuatkan oleh Visual Studio. Tambahan fungsi ini meliputi field dan query untuk mempermudah kita melakukan pengambilan data dari database

[collapsed title=Enhancing DataSet – Field]

Membuat field tambahan ke table transactions, dengan cara meletakkan cursor pada posisi field yang mau kita tambahkan, kemudian klik-kanan mouse dan pilih menu Insert Column.

[collapsed title=Enhancing DataSet – Table Transactions]

Membuat field tambahan ke table transactions, dengan cara meletakkan cursor pada posisi field yang mau kita tambahkan, kemudian klik-kanan mouse dan pilih menu Insert Column.

[/collapsed]

Buatlah semua field tambahan untuk table transactions sehingga menjadi seperti tampak di gambar di bawah ini.

[collapsed title=Enhancing DataSet – Table Transactions]

[/collapsed]

Buatlah semua field tambahan untuk table transaction_details sehingga menjadi seperti tampak di gambar di bawah ini.

[collapsed title=Enhancing DataSet – Table Transactions Details]

[/collapsed]

Perlu diperhatikan bahwa penambahan field diluar yang ada di table fisik nantinya akan sangat berguna ketika kita melakukan proses data binding data ke control seperti DataGridView, sehingga kita tidak perlu membuat kolom tambahan lagi di DataGridView karena sudah tersedia semua di DataSet.

[/collapsed]

Enhancing DataSet – Query Tambahan

Berikutnya yang kita lakukan menambahkan query ke DataSet. Jika kita perhatikan di DataSet Designer, ada komponen khusus dibawah bable dengan nama xxxTableAdapter. Komponen TableAdapter ini adalah DataAdapter yang khusus dibuatkan untuk menangani proses CRUD dari tabel bersangkutan, jadi proses CRUD untuk table transactions ditangani oleh transactionsTableAdapter. Secara default, sudah tersedia method Fill dan GetData, namun kedua methos tersebut masih bersifat generic pada saat proses pengambilan data dari database akan mengambil keseluruhan data tanpa filter.

Untuk itu kita perlu menambahkan sejumlah query untuk memudahkan kita mengambil data dari database berdasarkan kriteria tersentu, pastinya kita akan memerlukan query untuk mendapatkan data transaksi dengan ID tertentu, atau nomor transaksi tertentu, atau bisa juga berdasarkan tanggal transaksi.

[collapsed title=Enhancing DataSet – Add Query]

Membuat query tambahan ke table items, dengan cara meletakkan cursor pada posisi header itemsTableAdapter yang mau kita tambahkan, kemudian klik-kanan mouse dan pilih menu Add Query. Seperti tampak di bawah ini.

[collapsed title=Enhancing DataSet – Add Items Query]

[/collapsed]

Setelah itu akan muncul dialog box wizard proses pembuatan TableAdapter Query seperti ini, pilih Use SQL Statement karena kita akan mengambil data menggunakan perintah SQL.

[collapsed title=Enhancing DataSet – Add Query Wizard]

[/collapsed]

Kemudian pilih SELECT which returns rows karena query kita menghasilkan rows dari table items, untuk option lain silakan teman-teman coba sendiri.

[collapsed title=Enhancing DataSet – Add Query Wizard]

[/collapsed]

Beginilah penampakan query terhadap table items berdasarkan field ID, perhatikan bahwa query di bawah ini memasukkan sebuah parameter @id yang harus kita berikan nanti ketika memanggil query ini.

[collapsed title=Enhancing DataSet – Add Query Wizard]

[/collapsed]

Berikan nama untuk query ini, seperti tampak pada gambar di bawah ini.

[collapsed title=Enhancing DataSet – Add Query Wizard]

[/collapsed]

Selesai! Visual Studio sudah membuatkan fungsi untuk membantu kita melakukan query ke table items di database berdasarkan field ID.

[collapsed title=Enhancing DataSet – Add Query Wizard]

[/collapsed]

Setelah menambahkan query pengambilan data items berdasarkan ID, maka penampakan DataSet akan menjadi seperti pada gambar di bawah ini.

[collapsed title=Enhancing DataSet – Add Items Query]

[/collapsed]

Berikutnya yang perlu kita lakukan adalah menambahkan semua query berdasarkan kriteria yang kita inginkan ke seluruh table yang ada dalam DataSet kita ini. Berikut ini penampakan query terhadap table items berdasarkan field Code.

[collapsed title=Enhancing DataSet – Add Query]

[/collapsed]

Penampakan DataSet setelah menambahkan query berdasarkan field ID dan dan field Code pada table items dan transaction_types seperti pada gambar di bawah ini.

[collapsed title=Enhancing DataSet – Add Query]

[/collapsed]

Berikut ini penampakan query terhadap table transactions berdasarkan field ID. Perhatikan bahwa pada query ini kita melakukan JOIN terhadap table transactions dan transaction_types. Lebih jauh tentang JOIN silakan pelajari artikel ini.

[collapsed title=Enhancing DataSet – Add Query]

[/collapsed]

Berikut ini penampakan query terhadap table transactions berdasarkan field Code. Untuk query ini kita juga lakukan JOIN dengan transaction_types.

[collapsed title=Enhancing DataSet – Add Query]

[/collapsed]

Berikut ini penampakan query terhadap table transactions berdasarkan field Date. Perhatikan bahwa kita menggunakan klausa BETWEEN di dalam klausa WHERE.

[collapsed title=Enhancing DataSet – Add Query]

[/collapsed]

Penampakan DataSet setelah menambahkan query berdasarkan field ID dan, field Code, dan field Date pada table transactions seperti pada gambar di bawah ini.

[collapsed title=Enhancing DataSet – Add Query]

[/collapsed]

Berikut ini penampakan query terhadap table transaction_details berdasarkan field Code.

[collapsed title=Enhancing DataSet – Add Query – Transactions Details by ID]

[/collapsed]

Berikut ini penampakan query terhadap table transaction_details berdasarkan field Trans_ID.

[collapsed title=Enhancing DataSet – Add Query – Transactions Details by Transaction ID]

[/collapsed]

Penampakan DataSet setelah menambahkan query berdasarkan field ID dan dan field Transaction ID pada table transaction_details seperti pada gambar di bawah ini.

[collapsed title=Enhancing DataSet – Add Query]

[/collapsed]

Dengan tersedianya fungsi-fungsi query tambahan pada DataSet yang kita buat di atas, maka pada saat proses pengambilan data, kita hanya perlu meamanggil fungsi-fungsi tersebut dan tidap perlu lagi membuat query secara manual. Sekali lagi, produktifitas programmer semakin meningkat dengan cara ini.

[/collapsed]

Denormalisasi

Apakah denormalisasi database itu? denormalisasi database adalah pelanggaran aturan normalisasi atau menjabarkan suatu desain database yang telah normal untuk meningkatkan performa pengaksesan data pada database. Database yang telah normal disini dimaksudkan database yang redundansi datanya minim sehingga data yang disimpan tidak mengalami kerancuan dalam proses pengaksesan. Namun denormalisasi yang kita lakukan di sini tidak secara fisik, namun hanya menggunakan fasilitas view yang ada di database.

[collapsed title=Denormalisasi]

Denormalisasi kita lakukan dengan melakukan JOIN terhadap seluruh tabel yang ada di database inventory kita. Nantinya view denormalisasi ini akan berguna saat membuat laporan. Dengan bantuan plugin MySQL for Visual Studio, kita bisa membuat sebuah View langsung dari IDE Visual Studio sehingga tidak perlu berpindah ke phpMyAdmin atau HeidiSQL untuk membuat view denormalisasi ini.

[collapsed title=Denormalisasi – Membuat View]

[/collapsed]

Setelah View berhasil kita buat, langkah berikutnya adalah menambahkan view tersebut ke dalam Data Access Layer yang kita buat, caranya sama cukup dengan melakukan drag-drop dari Server Explorer ke DataSet Designer, sehingga tampak seperti gambar di bawah ini.

[collapsed title=Denormalisasi – Drag View ke DataSet Designer]

[/collapsed]

Berikut ini penampakan query terhadap view transaction_denormalized berdasarkan field Trans_ID.

[collapsed title=Denormaslisasi – Add Query – untuk view]

[/collapsed]

Berikut ini penampakan query terhadap view transaction_denormalized berdasarkan field Trans_Code.

[collapsed title=Denormaslisasi – Add Query – untuk view]

[/collapsed]

Berikut ini penampakan query terhadap view transaction_denormalized berdasarkan field Item_ID.

[collapsed title=Denormaslisasi – Add Query – untuk view]

[/collapsed]

Berikut ini penampakan query terhadap view transaction_denormalized berdasarkan field Item_Code.

[collapsed title=Denormaslisasi – Add Query – untuk view]

[/collapsed]

Penampakan Data Access Layer setelah kita melengkapi query untuk View transaction_denormalized.

[collapsed title=Denormalisasi – Hasil Query Tambahan untuk View]

[/collapsed]

Hasil Akhir

Setelah seluruh langkah di atas kita selesaikan, maka langkah pamungkas dalam pembuatan Data Access Layer ini adalah dengan melakukan compile atau build project ini. Setelah proses build selesai dilakukan maka kita akan mendapatkan sejumlah komponen baru tampil di toolbox seperti tampak pada gambar di bawah ini. Visual Studio otomatis akan mengenali komponen Data Access Layer buatan kita dan menambahkannya ke dalam toolbox, dan kita bisa melakukan drag-and-drop komponen-komponen tersebut ke dalam form-form buatan kita nantinya. Komponen tambahan ini nantinya akan kita pergunakan dalam pembuatan Aplikasi Inventory ini.

Penutup

Mudah-mudahan penjelasan tentang pembuatan Data Access Layer 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 Pojok Programmer !

–

Performance Tuning Sederhana di MySQL Menggunakan Index

superadmin May 19, 2014 No Comments

Performace Tuning Sederhana di MySQL Menggunakan Index

Database Tuning | Database Tuning adalah sejumlah aktifitas yang dilakukan untuk memperbaiki atau meningkatkan kinerja atau performance sebuah database. Aktifitas tuning ini meliputi banyak aspek dari software hingga hardware, antara lain I/O Tuning, DBMS Tuning, Query Tuning, dan Database Maintenance. Masing-masing memiliki tekniknya sendiri-sendiri, dan membutuhkan skill yang mumpuni. Namun kita tetap bisa mempelajari teknik-teknik dasarnya. Dalam artikel ini, kita akan mencoba melakukan Query Tuning dengan bantuan Database Index.

Umumnya programmer, pada saat mengembangkan sebuah aplikasi database, pasti akan membuat sebuah database dan memasukkan beberapa data contoh ke dalamnya. Pada saat pengembangan, tidak ada masalah dan kelihatannya semua baik-baik saja, namun seiring berjalannya waktu, aplikasi mulai terkesan lambat dan bisa jadi sampai hang. Hal ini terjadi karena biasanya programmer tidak pernah melakukan load testing menggunakan data besar, dalam artian, jumlah row yang buanyak.

Nah… untuk contoh kasus, kita akan menggunakan database MySQL dan membuat sebuah table sederhana saja berisikan 9 (sembilan) juta rows, caranya seperti terlihat di bawah ini… perlu waktu sekitar 2 menit untuk men-generate table contoh tersebut. Perlu diperhatikan, setelah perintah di bawah ini selesai, maka baru table saja yang dibuat, belum ada index.

mysql> -- --------------------------------------------------
mysql> -- coba bikin sebuah table raksasa dengan 9 juta rows
mysql> -- setelah selesai dibuat, table ini belum memiliki index
mysql> -- --------------------------------------------------
mysql> create table big_table as
    -> select @baris := @baris+1 as baris
    -> from ( select @baris := 0 ) x
    -> join ( select 1 kolom from information_schema.tables ) a
    -> join ( select 1 kolom from information_schema.tables ) b
    -> join ( select 1 kolom from information_schema.tables ) c ;
Query OK, 9938375 rows affected (2 min 25.70 sec)
Records: 9938375  Duplicates: 0  Warnings: 0

Salah satu teknik yang banyak digunakan dalam aplikasi database adalah pagination, dimana teknik ini mengharuskan kita mengetahui total rows yang ada dalam sebuah table untuk menentukan jumlah page yang ada. Coba kita lakukan query count(*) untuk mengetahui total rows dalam table tersebut, dan lihat waktu yang diperlukan untuk mendapatkan hasilnya 🙂

mysql> -- --------------------------------------------------
mysql> -- coba hitung jumlah row yang ada di dalam big_table
mysql> -- terlihat mysql memerlukan waktu 19 detik 
mysql> -- untuk mendapatkan hasil yang kita inginkan
mysql> -- --------------------------------------------------
mysql> select count(*) from big_table ;
+----------+
| count(*) |
+----------+
|  9938375 |
+----------+
1 row in set (18.82 sec)

Rupanya perlu waktu 18 detik untuk mendapatkan hasilnya…. untuk user yang penyabar, mungkin hal ini tidak menjadi masalah besar. Sekarang kita coba lihat execution plan yang dipilih oleh mysql saat mengeksekusi perintah count(*) yang kita berikan, maka terlihat seperti di bawah ini, bahwa mysql melakukan full table scan, ditandai dengan type=ALL pada execution plan yang muncul. Full table scan artinya, mysql meload seluruh table ke memory dan menghitung row yang ada di dalamnya satu persatu…. wow….

mysql> -- --------------------------------------------------
mysql> -- jika kita lihat execution plan query tersebut
mysql> -- terlihat bahwa mysql melakukan full table scan 
mysql> -- ditandai dengan type=ALL ke seluruh 9 juta rows
mysql> -- --------------------------------------------------
mysql> explain
    -> select count(*) from big_table ;
+----+-------------+-----------+------+---------------+------+---------+------+---------+-------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows    | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+---------+-------+
|  1 | SIMPLE      | big_table | ALL  | NULL          | NULL | NULL    | NULL | 9938689 |       |
+----+-------------+-----------+------+---------------+------+---------+------+---------+-------+
1 row in set (0.00 sec)

Sekarang kita coba lagi dengan model query lain yang sangat umum, yaitu query searching dengan menggunakan WHERE clause. Kita coba mencari sebuah row tertentu yang kira-kira lokasinya ada di tengah-tengah table. Pada contoh di bawah ini bisa terlihat bahwa mysql tetap melakukan full table scan ke seluruh rows, padahal row yang kita inginkan sudah ditemukan di tengah-tengah table. Dan rupanya perlu waktu lebih lama, yaitu 19 detik, dibandingkan dengan proses count(*) yang hanya 18 detik.

mysql> -- --------------------------------------------------
mysql> -- coba kita test dengan query yang lain
mysql> -- yaitu mendapatkan sebuah row yang kira2 
mysql> -- berada di tengah2 table besar ini
mysql> -- lagi2 terlihat mysql memerlukan waktu 19 detik 
mysql> -- untuk mendapatkan hasil yang kita inginkan
mysql> -- --------------------------------------------------
mysql> select * from big_table
    -> where baris = 5346781 ;
+---------+
| baris   |
+---------+
| 5346781 |
+---------+
1 row in set (19.03 sec)

Execution plan mengkonfirmasikan bahwa mysql melakukan full table scan dari row pertama sampai row terakhir, padahal kita hanya menginginkan satu row saja… 🙂

mysql> -- --------------------------------------------------
mysql> -- jika kita lihat execution plan query tersebut
mysql> -- terlihat juga bahwa mysql melakukan full table scan 
mysql> -- ditandai dengan type=ALL ke seluruh 9 juta rows
mysql> -- *padahal* kita hanya perlu 1 row saja yang ada di tengah
mysql> -- --------------------------------------------------
mysql> explain
    -> select * from big_table
    -> where baris = 5346781 ;
+----+-------------+-----------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | big_table | ALL  | NULL          | NULL | NULL    | NULL | 9938977 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

Mari kita buat index….. sabar….. 2 menit…. 🙂

mysql> -- --------------------------------------------------
mysql> -- sekarang coba kita buat INDEX berdasarkan field baris
mysql> -- harap bersabar... karena cukup lama juga :)
mysql> -- --------------------------------------------------
mysql> create index big_table_pk on big_table(baris) ;
Query OK, 0 rows affected (2 min 4.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

Index selesai dibuat, dan kita coba lakukan proses penghitungan jumlah rows menggunakan query yang sama seperti sebelumnya… dan hasilnya muncul dalam 10 detik, sebuah peningkatan yang luar biasa dari 18 detik menjadi 10 detik saja (y)

mysql> -- --------------------------------------------------
mysql> -- sekarang kita hitung  row yang ada di dalam big_table
mysql> -- rupanya terlihat ada peningkatan performance 
mysql> -- dari awalya perlu 19 detik, menjadi 10 detik saja
mysql> -- LUAR BIASA!
mysql> -- --------------------------------------------------
mysql> select count(*) from big_table ;
+----------+
| count(*) |
+----------+
|  9938375 |
+----------+
1 row in set (10.91 sec)

Jika kita lihat execution plan, maka mysql tidak lagi menggunakan full table scan, namun sekarang menggunakan index scan. walaupun masih melakukan scan ke seluruh rows sebanyak 9 juta, namun index scan jauh leih cepat dibandingkan full table scan.

mysql> -- --------------------------------------------------
mysql> -- jika kita lihat execution plan query tersebut
mysql> -- terlihat bahwa mysql tidak lagi melakukan full table scan 
mysql> -- namun melakukan index scan, jauh lebih cepat dari full table scan
mysql> -- --------------------------------------------------
mysql> explain
    -> select count(*) from big_table ;
+----+-------------+-----------+-------+---------------+--------------+---------+------+---------+-------------+
| id | select_type | table     | type  | possible_keys | key          | key_len | ref  | rows    | Extra       |
+----+-------------+-----------+-------+---------------+--------------+---------+------+---------+-------------+
|  1 | SIMPLE      | big_table | index | NULL          | big_table_pk | 9       | NULL | 9938977 | Using index |
+----+-------------+-----------+-------+---------------+--------------+---------+------+---------+-------------+
1 row in set (0.00 sec)

Sekarang kita coba query searching….. dan hasilnya muncul sangat cepat, hanya 0.05 detik …. LUAR BIASA!

mysql> -- --------------------------------------------------
mysql> -- sekarang kita test dengan query kedua
mysql> -- dan hasilnya sangat LUAR BIASA!
mysql> -- hanya perlu 0.05 detik dari sebelumnya 19 detik !!!
mysql> -- --------------------------------------------------
mysql> select * from big_table
    -> where baris = 5346781 ;
+---------+
| baris   |
+---------+
| 5346781 |
+---------+
1 row in set (0.05 sec)

Jika kita lihat execution plan, maka mysql tidak lagi melakukan scan ke seluruh rows, namun langsung menuju satu row yang kita inginkan. Peningkatan kinerja pencarian dari 19 detik menjadi 0.05 detik adalah peningkatan yang sangat LUAR BIASA!

mysql> -- --------------------------------------------------
mysql> -- jika kita lihat execution plan query tersebut
mysql> -- terlihat bahwa mysql melakukan index "big_table_pk" 
mysql> -- dan langsung menuju rows yang kita inginkan
mysql> -- --------------------------------------------------
mysql> explain
    -> select * from big_table
    -> where baris = 5346781 ;
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+--------------------------+
| id | select_type | table     | type | possible_keys | key          | key_len | ref   | rows | Extra                    |
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | big_table | ref  | big_table_pk  | big_table_pk | 9       | const |    1 | Using where; Using index |
+----+-------------+-----------+------+---------------+--------------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

Perlu diperhatikan bahwa INDEX yang kita buat berdasarkan FIELD yang terlibat dalam WHERE clause, sehingga Query Optimizer di MySQL otomatis akan mengenali index tersebut dan menggunakannya untuk proses pencarian. Jadi untuk teman-teman programmer yang belum membuat index di table yang mereka buat, mulailah buat dari sekarang. SEMOGA BERGUNA!

mysql> -- --------------------------------------------------
mysql> -- SEMOGA BERGUNA!
mysql> -- --------------------------------------------------
mysql>

(y)

Aplikasi Inventory Sederhana – Penggunaan Trigger untuk Tracking Stock Barang

superadmin January 7, 2014 No Comments

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: 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 | 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.

Aplikasi Inventory Sederhana – Analisa dan Desain Database

superadmin August 22, 2013 No Comments

Design Database adalah salah satu hal penting yang perlu diperhatikan dalam membuat aplikasi perkantoran. Dalam membuat design database, ikutilah kaidah-kaidah yang pernah kita dapatkan semasa kuliah, terutama Normalisasi, minimal sampai dengan bentuk 3NF. Kesalahan dalam membuat design database dapat berakibat fatal, misalnya sulit membuat coding dan men-generate report yang kita inginkan. Dan jika sampai harus mengubah design database maka akan banyak waktu terbuang karena kita harus menyesuaikan kembali coding yang sudah dibuat sebelumnya. Oleh karena itu sediakanlah waktu dan pikiran yang cukup dalam men-design database ini.

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

Dalam membuat design database Aplikasi Inventory atau Aplikasi Stok Barang ini, kami juga menyengajakan diri untuk membuat design database menggunakan bahasa inggris, tujuannya selain melatih skill bahasa inggris kita, juga untuk melatih diri kita, siapa tahu aplikasi ini akan di-publish menjadi open source, atau kita berkesempatan berkontribusi dalam project open source lain.

Berdasarkan analisa dan design aplikasi yang tertuang dalam artikel sebelumnya, maka kita memerlukan sejumlah table untuk menyimpan data-data Barang dan Transaksi Keluar Masuk Barang. Untuk itu, sejumlah table yang diperlukan antara lain:

Tabel Barang

Table untuk menampung data master barang ini akan kita beri nama items. Data dictionary lengkapnya adalah sebagai berikut:

No.	Nama Field	Tipe Data	Keterangan
1.	id	integer	Primary Key, Auto Increment
2.	code	varchar(20)	Kode barang berupa text bebas maksimal 20 karakter, bisa digunakan sebagai barcode
3.	name	varchar(100)	Nama barang maksimal 100 karakter
4.	quantity_on_hand	decimal(10,2)	Quantity barang terakhir yang saat ini ada di gudang
5.	remarks	text	Keterangan lebih lengkap tentang barang bersangkutan

Tabel Jenis Transaksi

Tabel ini akan berisi kode untuk menentukan jenis transaksi dan kita beri nama transaction_types. Untuk aplikasi ini akan ada 2 (dua) jenis transaksi yaitu Transaksi Barang Masuk (kita beri kode BM) dan Transaksi Barang Keluar (kita beri kode BK). Data dictionary lengkapnya adalah sebagai berikut:

No.	Nama Field	Tipe Data	Keterangan
1.	id	integer	Primary Key, Auto Increment
2.	code	varchar(20)	Kode jenis transaksi berupa text bebas maksimal 20 karakter
3.	name	varchar(100)	Nama panjang yang lebih deskriptif, maksimal 100 karakter

Tabel Transaksi

Tabel ini akan berisi seluruh transaksi inventory dari aplikasi yang akan kita buat. Untuk membedakan Transaksi Barang Masuk dengan Transaksi Barang Keluar kita sediakan field type_id yang merupakan Foreign Key yang me-refer ke . Data dictionary lengkapnya adalah sebagai berikut:

No.	Nama Field	Tipe Data	Keterangan
1.	id	integer	Primary Key, Auto Increment
2.	type_id	integer	Foreign Key refer ke tabel transaction_types, untuk membedakan Transaksi Barang Masuk atau Keluar
3.	trans_code	varchar(100)	Nama panjang yang lebih deskriptif, maksimal 100 karakter
4.	trans_date	date	Tanggal transaksi
5.	remarks	text	Teks bebas untuk keterangan, silakan mengarang di field ini juga boleh

Tabel Detil Transaksi

No.	Nama Field	Tipe Data	Keterangan
1.	id	integer	Primary Key, Auto Increment
2.	trans_id	integer	Foreign Key refer ke tabel transactions, untuk record detil transaksi ini milik transaksi yang mana
3.	item_id	integer	Foreign Key refer ke tabel items, untuk menentukan barang mana yang harus diubah status stock-nya
4.	quantity	decimal(10,2)	Jumlah barang yang masuk atau keluar
5.	remarks	text	Teks bebas untuk keterangan, silakan mengarang di field ini juga boleh

Diagram ER

Diagram ER menggunakan notasi IDEF1X dari desain database untuk aplikasi inventory sederhana ini adalah sebagai berikut.

Script Database

Sedangkan script sql create table-nya adalah sebagai berikut.

create table items (
  id int auto_increment primary key,
  code varchar(20) not null,
  name varchar(100) not null,
  quantity_on_hand decimal(10,2) not null default 0,
  remarks text null
) ;

create table transaction_types (
  id int auto_increment primary key,
  code varchar(20) not null,
  name varchar(100) not null
) ;

create table transactions (
  id int auto_increment primary key,
  type_id int not null,
  trans_code varchar(20) not null,
  trans_date varchar(100) not null,
  remarks text null,
  foreign key(type_id) references transaction_types(id)
) ;

create table transaction_details (
  id int auto_increment primary key,
  trans_id int not null,
  item_id int not null,
  quantity decimal(10,2) not null default 0,
  remarks text null, 
  foreign key (trans_id) references transactions(id), 
  foreign key (item_id) references items(id) 
) ;

Simpulan

Dengan demikian selesailah sudah proses analisa dan desain kita. Dengan tersedianya Design Aplikasi dan Design Database, maka langkah selanjutnya kita bisa membuat Aplikasi-nya dengan lebih mudah dan terarah.

^_^

Aplikasi Inventory Sederhana – Analisa dan Desain Aplikasi

superadmin August 12, 2013 No Comments

Analisa dan Desain biasanya merupakan salah satu langkah yang kadang disepelekan kebanyakan programmer, terutama programmer pemula dan single fighter, karena dianggap menghabiskan waktu saja, Kebanyakan programmer lebih memilih untuk langsung duduk di depan komputer dan melakukan coding, hasilnya biasanya adalah kebingungan yang sangat tentang apa yang harus mereka lakukan pertama kali. Padahal analisa dan desain sesungguhnya akan membantu kita mengarahkan dan mengetahui fitur apa saja yang harus dibuat dalam aplikasi yang sedang dikembangkan ini. Dalam membuat Aplikasi Inventory Sederhana atau Aplikasi Stok Barang ini, kita akan memanfaatkan software StarUML untuk membuat Use Case Diagram aplikasi ini. Use Case Diagram menggambarkan “apa” saja, atau fitur yang harus ada dalam aplikasi yang sedang kita buat ini. Dan untuk memudahkan, kita akan membatasi lingkup tutorial ini sesuai dengan apa yang tercantum dalam Use Case Diagram yang kita buat sekarang.

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

Aplikasi ini akan kita bagi menjadi beberapa module meliputi

Modul Master Barang
Modul Transaksi Inventory
Modul Laporan

Pembagian modul ini nantinya juga harus tercermin dalam code vb.net yang kita buat. Jika kita perhatikan dalam gambar, terlihat bahwa Modul Transaksi dan Modul Laporan bergantung pada Modul Master, pada saat yang sama Modul Laporan juga bergantu pada Modul Transaksi. Hal ini sangatlah wajar karena Transaksi tidak bisa dilakukan tanpa adanya data Master, dan Laporan tidak bisa dibuat tanpa tersedianya data dari Modul Master dan Modul Transaksi. Setelah kita tahu module apa saja yang perlu dibuat, maka kita harus mendetilkan masing-masing modul dengan diagram use case-nya.

Modul Master Barang

Modul Master Barang akan berisikan use-case berikut ini dengan fungsinya masing-masing antara lain,

Mengelola Data Master Barang. Use Case ini menggambarkan proses pengelolaan data Master Barang, skenario utama use case ini adalah melihat list barang yang tersimpan dalam database, pengguna kemudian dapat melakukan filtering untuk mencari data barang yang diinginkan. Extension, atau skenario alternatif, dari use case ini meliputi,
- Menambah Barang Baru. Pengguna akan menekan tombol tertentu yang akan menampilkan form entri data barang agar pengguna bisa mengisikan data barang yang akan dibuatnya
- Mengubah Data Barang. Pengguna akan memilih data barang yang akan diubah datanya kemudian menekan tombol tertentu, atau melakukan klik ganda pada data bersangkutan dan sistem akan menampilkan form berisikan data barang yang ingin diubah.
- Menghapus Data Barang. Pengguna akan memilih data barang yang akan dihapus, kemudian menekan tombol tertentu. Sistem kemudian akan memberikan konfirmasi apakah pengguna benar-benar akan melakukan penghapusan data.

Modul Transaksi

Modul Transaksi akan berisikan use-case berikut ini dengan fungsinya masing-masing antara lain,

Mengelola Transaksi Barang Masuk. Use Case ini menggambarkan proses pengelolaan data Transaksi Barang Masuk, skenario utama use case ini adalah melihat list transaksi yang tersimpan dalam database, pengguna kemudian dapat melakukan filtering untuk mencari data transaksi yang diinginkan. Extension, atau skenario alternatif, dari use case ini meliputi,
- Menambah Transaksi Barang Masuk Baru. Pengguna akan menekan tombol tertentu yang akan menampilkan form entri data transaksi agar pengguna bisa mengisikan transaksi yang akan dibuatnya
- Mengubah Data Transaksi Barang Masuk. Pengguna akan memilih data transaksi yang akan diubah datanya kemudian menekan tombol tertentu, atau melakukan klik ganda pada data bersangkutan dan sistem akan menampilkan form berisikan data transaksi yang ingin diubah.
- Menghapus Data Transaksi Barang Masuk. Pengguna akan memilih data transaksi yang akan dihapus, kemudian menekan tombol tertentu. Sistem kemudian akan memberikan konfirmasi apakah pengguna benar-benar akan melakukan penghapusan data.

Mengelola Transaksi Barang Keluar. Use Case ini menggambarkan proses pengelolaan data Transaksi Barang Keluar, skenario utama use case ini adalah melihat list transaksi yang tersimpan dalam database, pengguna kemudian dapat melakukan filtering untuk mencari data transaksi yang diinginkan. Extension, atau skenario alternatif, dari use case ini meliputi,
- Menambah Transaksi Barang Keluar Baru. Pengguna akan menekan tombol tertentu yang akan menampilkan form entri data transaksi agar pengguna bisa mengisikan transaksi yang akan dibuatnya
- Mengubah Data Transaksi Barang Keluar. Pengguna akan memilih data transaksi yang akan diubah datanya kemudian menekan tombol tertentu, atau melakukan klik ganda pada data bersangkutan dan sistem akan menampilkan form berisikan data transaksi yang ingin diubah.
- Menghapus Data Transaksi Barang Keluar. Pengguna akan memilih data transaksi yang akan dihapus, kemudian menekan tombol tertentu. Sistem kemudian akan memberikan konfirmasi apakah pengguna benar-benar akan melakukan penghapusan data.

Modul Laporan

Modul Laporan akan berisikan use-case berikut ini dengan fungsinya masing-masing antara lain,

Melihat Laporan Kartu Stok. Sederhana saja, aplikasi akan menampilkan kronolagi keluar masuk barang sesuai dengan kriteria kode barang yang diberikan oleh pengguna.
Melihat Laporan Mutasi Stok. Berdasarkan periode tertentu sesuai input dari pengguna, Aplikasi akan menampilkan summary dari total keluar dan masuk dari masing-masing barang.

Simpulan

Selesailah kita mendefinisikan semua fitur dari Aplikasi yang akan kita bangun. Teman-teman mungkin bertanya-tanya, “Kenapa tidak ada modul Security? Apakah pengguna dapat menggunakan Aplikasi ini tanpa Login terlebih dahulu?” Jawabannya adalah, kita dengaja membatasi lingkup tutorial ini. Mengenai Modul security yang mengatur hak akses pengguna silakan dikembangkan sendiri, atau…. jika ada kesempatan, mungkin akan kita buatkan tutorialnya tersendiri.

Tetap Semangat!

–