Hierarchical Data

Recursive queries are typically used to deal with hierarchical or tree-structured data, such as chart of accounts (COA). COA is a listing all the accounts in the general ledger in such a way that forms a hierarchical structure, for example the following:


acc_
acc_id parid acc_code     acc_name                    acc_level
-----------------------------------------------------------------
1           10.0000.0000 AKTIVA                         0
2 1 11.0000.0000 AKTIVA LANCAR                   1
3 2 11.1000.0000 Kas dan Setara Kas                2
4  3 11.1100.0000 Kas                            3
5  4 11.1110.0000 Kas - Program Kemitraan          4
6  4 11.1120.0000 Kas - Bina Lingkungan             4
7  3 11.1200.0000 Bank                            3
8  7 11.1210.0000 Bank - Program Kemitraan        4
....
....
116  97 22.0000.0000 KEWAJIBAN JANGKA PANJANG 1
117  116 22.2000.0000 Hutang Kepada BUMN Pembina Lain  2
119  116 22.9000.0000 Hutang Jangka Panjang Lainnya     2
122          30.0000.0000 AKTIVA BERSIH                   0
123  122 33.0000.0000 PENYISIHAN                      1



The list is generated by running the following SQL command:


WITH RECURSIVE tree AS (SELECT acc_id,acc_code, acc_parid, acc_name ,0 AS acc_level 
FROM tova.coa WHERE acc_parid IS NULL
UNION ALL
SELECT c.acc_id, c.acc_code, c.acc_parid, c.acc_name, acc_level+1 AS acc_level 
FROM tova.coa c JOIN tree t ON t.acc_id  = c.acc_parid)
SELECT acc_id, acc_parid, tova.getakunttk(acc_code), acc_name, acc_level 
FROM tree b order by 3;


I save the transaction only at the lowest level account, of course user still can see reports for each account in a hierarchy. The stored function below will display accounts hierarchy.

CREATE OR REPLACE FUNCTION tova.getcoa_allchildofparentrec(IN pacc_parid integer) 
RETURNS TABLE (acc_id integer,acc_code character varying, acc_name character varying) AS
$$
BEGIN
    RETURN QUERY WITH RECURSIVE tree AS (
            SELECT a.acc_id, a.acc_code as acc_code,a.acc_name 
               FROM tova.coa a WHERE acc_parid = $1
            UNION ALL
            SELECT a.acc_id, a.acc_code as acc_code,a.acc_name 
               FROM tova.coa a, tree t WHERE t.acc_id = a.acc_parid)
    SELECT h.acc_id,h.acc_code, h.acc_name FROM tree h;
END;
$$ LANGUAGE plpgsql;


To display the coa's tree, just call this:
select acc_code, acc_name from ferry.getcoa_allchildofparentrec(34) order by 1;


acc_code acc_name
---------------------------------------------------------------------
11.3610.0000 Piutang Pinjaman Mitra Binaan - Sektor Industri
11.3610.6108 PPMB Sektor Industri - Kab. Landak
11.3610.6103 PPMB Sektor Industri - Kab. Sanggau
11.3610.6301 PPMB Sektor Industri - Kab. Tanah Laut
11.3610.6309 PPMB Sektor Industri - Kab. Tabalong
11.3610.6105 PPMB Sektor Industri - Kab. Sintang
11.3610.6303 PPMB Sektor Industri - Kab. Banjar
11.3610.6100 PPMB Sektor Industri Prov. Kalimantan Barat
11.3610.6171 PPMB Sektor Industri - Kota Pontianak
11.3620.0000 Piutang Pinjaman Mitra Binaan - Sektor Perdagangan
11.3630.0000 Piutang Pinjaman Mitra Binaan - Sektor Pertanian
11.3640.0000 Piutang Pinjaman Mitra Binaan - Sektor Peternakan
11.3650.0000 Piutang Pinjaman Mitra Binaan - Sektor Perkebunan
11.3660.0000 Piutang Pinjaman Mitra Binaan - Sektor Perikanan
11.3670.0000 Piutang Pinjaman Mitra Binaan - Sektor Jasa
11.3680.0000 Piutang Pinjaman Mitra Binaan - Sektor Lain
11.3610.6300 Sektor Industri Prov. Kalimantan Selatan
11.3610.6310 PPMB - Sektor Industri - Kab. Tanah Bumbu
...
...
11.3670.6171 Piutang Pinjaman Mitra Binaan - Sektor Jasa - Kota Pontianak
11.3660.6171 Piutang Pinjaman Mitra Binaan - Sektor Perikanan - Kota Pontianak
11.3680.6171 Piutang Pinjaman Mitra Binaan - Sektor Lain - Kota Pontianak

PostgreSQL: Invalid byte sequence for encoding UTF8

I have a comma delimited file (.csv) contains stock master data and wanto to import them into PostgreSQL table .During importing, I am facing Invalid byte sequence for encoding "UTF8" error message.

C:\>psql -Umyname -dmydb -p5490
--1. do import
mydb=# \copy myshema.stocks from C:\temp\inv.csv with delimiter as ';'csv
ERROR: Invalid byte sequence for encoding "UTF8": 0xa0
CONTEXT:  copy stock, line 26120

--2. check my client encoding
mydb=# show client_encoding;

client_encoding
----------------
UTF8
(1 row)

--3. modify my client encoding
mydb=# \client_encoding LATIN1

--3a. check current client encoding
mydb=# show client_encoding;

client_encoding
----------------
LATIN1
(1 row)
--4. Re do importing
mydb=# \copy myshema.stocks from C:\temp\inv.csv with delimiter as ';'csv

--5. reset client_encoding;

That is it.

Create a Control to act like a Button

System.Windows.Forms.UserControl provides an empty control that can be used to create other controls. We might consider to inherit the class to create a control with custom appearance, feature and behaviour.
I then create a control that functionally same as standard button, but my control could not be be assign to AcceptButton and CancelButton property of a form. To allows the control to act like a button on a form, just implement the IButtonControl interface to the control.

public class FerBtn : System.Windows.Forms.UserControl, System.Windows.Forms.IButtonControl
{
}

After visit this, I copy and paste the DialogResult property, the NotifyDefault and PerformClick methods, and add a definition for IsDefault.

private bool mIsDefault;
[Browsable(false)]
public bool IsDefault 
{
  get { return this.mIsDefault; }
  set { this.mIsDefault = value; }
}

Now this control could be assign to AcceptButton and CancelButton property of a form.

To Get Distinct Rows By specific columns

My purpose today is to get the rows with unique data based on criteria of uniqueness value on the column, including combinations of column.  As you may think, just use the DISTINCT clause. You are right! Let's try with PostgreSQL.

Firt, list the data using this statement:

SELECT a.nik, a.nama, g.ket as job, h.akunno, h.alokasi
FROM ester.maskar a
   INNER JOIN  ester.setnamajob g ON a.jabid=g.idku
   INNER JOIN  ester.setnamajobakun h ON g.idku=h.jobid
   WHERE  a.thn=2012 AND  a.unitid = 16 ORDER BY a.nik, a.nama
=========================================================
nik   nama          job                   akunno  alokasi
=========================================================
100.200  ALBERT    Manajer                400     0.00
100.300  JHONI     Danru Satpam           423     1.00
100.400  ZAENAL    Anggota Satpam         423     1.00
100.500  ROBI      Anggota Satpam         423     1.00
200.400  TEDDY     Operator St. Kempa     603.07  0.40
200.400  TEDDY     Operator St. Kempa     603.08  0.40
200.400  TEDDY     Operator St. Kempa     603.09  0.20
300.500  JOKO      Operator Kolam Limbah  603.11  0.50
300.500  JOKO      Operator Kolam Limbah  603.12  0.50
300.650  SEPTIAN   Operator St. Kempa     603.09  0.20
300.650  SEPTIAN   Operator St. Kempa     603.07  0.40
300.650  SEPTIAN   Operator St. Kempa     603.08  0.40
400.600  OPA OPO   Kerani I TUK           401     1.00
----------------------------------------------------------

DISTINCT 
DISTINCT will remove all duplicate rows from the result set and one row is kept from each group of duplicates. Let's try with DISTINCT clause:

SELECT DISTINCT a.nik, a.nama, g.ket as job, h.akunno, h.alokasi
FROM ester.maskar a
   INNER JOIN  ester.setnamajob g ON a.jabid=g.idku
   INNER JOIN  ester.setnamajobakun h ON g.idku=h.jobid
   WHERE  a.thn=2012 AND  a.unitid = 16 ORDER BY a.nik, a.nama

What is your result?
I've got same result set with previous!


DISTINCT ON
Now try with this command:

 SELECT DISTINCT ON (nik,nama) a.nik, a.nama, g.ket as job,h.akunno, h.alokasi
FROM  ester.maskar a
   INNER JOIN  ester.setnamajob g ON a.jabid=g.idku
   INNER JOIN  ester.setnamajobakun h ON g.idku=h.jobid
   WHERE  a.thn=2012 AND  a.unitid = 16 ORDER BY a.nik, a.nama

=========================================================
nik   nama          job                   akunno  alokasi
=========================================================
100.200  ALBERT    Manajer                400     0.00
100.300  JHONI     Danru Satpam           423     1.00
100.400  ZAENAL    Anggota Satpam         423     1.00
100.500  ROBI      Anggota Satpam         423     1.00
200.400  TEDDY     Operator St. Kempa     603.07  0.40
300.500  JOKO      Operator Kolam Limbah  603.11  0.50
300.650  SEPTIAN   Operator St. Kempa     603.09  0.20
400.600  OPA OPO   Kerani I TUK           401     1.00
---------------------------------------------------------
Those are the result set that I want.

DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY. Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first.

The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s). The ORDER BY clause will normally contain additional expression(s) that determine the desired precedence of rows within each DISTINCT ON group.

Eclipse: Tomcat Server ports are already in use

Working with Eclipse IDE, I use Apache Tomcat to hosting applications on Windows. I forgot what I've done when encounter this problem:"Several ports required by Tomcat  are already in use."

Yes, there must be another process using the ports. Apache Tomcat service was not started on Windows Sevices. Also I've checked using web browser http://localhost:8080/ and I got status report 404 message. Yes Apache Tomcat still run and listening on 8080. I suspect that the problem arises because using Eclipse incorrectly.

To list the listening port using: C:>NETSTAT -na | find "LISTENING"

Yes they were running. The running process could not stopped when exit from Eclipse IDE. Oh yeah, i've terminated the Eclipse IDE abnormally using End Process on Task Manager. So I have to kill the PID:

C:>TASKLIST /FI "PID eq 5740"
C:>TASKKILL /F PID 5740

then re run the server from Eclipse environment.

It works.

Remove Title Atributes of Menu Links from Taxonomy Generated Menu

Drupal 7: To generate menus using Taxonomy menu, we do the following steps:

1. http://mysitedom.com/admin/structure/taxonomy/my-vocabulary/list

2. Click the Edit tab

3. Select MENU location from list.

3. Check the option to "Select to rebuild the menu on submit".

4. Click the Save button, so a menu structure will be generated for the selected Vocabulary.

But I have a problem with the title attribute of URLs that are automatically filled in by the descriptions of vocabulary terms. I thought this is quite annoying, especially against the long description.

Do not hack core. This phrase is commonly heard in the Drupal community.

For my requirement, I've did this:

Modify taxonomy_menu.module file under \myhome\sites\all\modules\taxonomy_menu\ directories, to make changes of the function_taxonomy_menu_save. At line number 568-569 which contains the command:

'options' => array('attributes' => array('title' => trim($item['description'])

? $item['description'] : $item['name'])),

commenting out the line:

/* 'options' => array('attributes' => array('title' => trim($item['description'])

? $item['description'] : $item['name'])), */

After hitting my menu at http://mysitedom.com/admin/structure/menu/manage/my-taxo-menu, click Edit of each generated menu link to clear 'Description' field, I repeat the four steps above to rebuild menu, now Title attribute dissapear from menu link.

Forgot Debian Root Password

Here I tell you what I did

1. Reboot server.
2. Press downarrow key, and grub-boot loader screen appears.
3. I select the line start with "kernel /boot/vmlinuz-2.6.26-2.686 root=/dev/sda1 ro quiet".
4. Press 'e' to edit the argument and modify the line to: "kernel /boot/vmlinuz-2.6.26-2.686 root=/dev/sda1 rw single init=/bin/bash"
5. Press 'b' to boot
6. Bash prompt appears....but I was facing the problem that the keyboard did not work..(I've got error messages something like:"Device not accepting", "Unenumerate", that tell USB port not recognized)....I am using KVM Switch. I did unplug the USB keyboard from KVM Switch then plugging in another USB keyboard.
7. After repeating steps 1-5, I was able to type using the new keyboard.
8. I was trying using mount command such as:

#mount -n -o remount rw /

#mount -rw -o remount /

#mount -t ext3 /dev/sda1 /usr

but passwd command failed. ("bash: passwd: command not found")

(Perhaps it would works if I (re)mount rest of all my partitions in read/write (rw) mode such as /usr /var etc, but i did not do at the time).

What I did then was disabling the root password from the password file using Nano editor. I've check that the second data field in /etc/passwd is “x” for every username, so the system uses shadow passwords, and I must edit /etc/shadow.

10. #nano /etc/shadow

I changed==> root:this_is_the_encrypted_passwd:15401:0:99999:7:::

to this ====> root::15401:0:99999:7:::

(Edit the second data field in the password file so that it is empty.)

Reboot the system and at the login prompt, type root (without password).