albert's blog

2011/12/22

How to apply 1000 separator in data field

需求
在數字或者是金額等欄位，需要做到千分位隔開，如1000，要顯示1,000

作法
1. 到特定欄位按右鍵，選擇Field Pattern

2. 選擇Number, 將"User 1000 Separator"打勾，再按Apply

3. Check Result

REPORT_COUNT vs. [group name]_COUNT

需求
在產出的報表的每筆資料的第一個column，指定序號

產出結果如下

一般來說，只要將REPORT_COUNT此欄位拉到iReport即可

但是在NIG071此張報表中，若到下一頁，他會把值reset，從1開始

會造成此問題的原因，是因為此報表有用到Report Group

此時，要用Group_Count才行，其命名為則是[group name]_COUNT

結論
1. 如果沒有用到Report Group，請用REPORT_COUNT
2. 如果有用到Report Group，請用[group name]_COUNT

Oracle/PLSQL: Case Statement

Requirement
到NIGT001與NIGT005，根據前端條件輸入結果產出報表

報表內容包含違章編號、統一編號、議程類別等欄位，其中有幾個欄位是經過if-else判斷是得來的，如作業代號、案況、會次等

AS-IS
現有VB/Cobol在NIG335的作法是：
1. 在VB裡頭到NIGT001撈資料出來
2. 根據找出來的資料往後拋到Cobol，在根據撈出來的值來判斷，以下是Cobol程式片段

EVALUATE WK-PRCD-STUS  
 WHEN "33"  
   IF WK-UPD-JDGF-STUS = "50"  
     MOVE "4"     TO RPT-CASE-STUS(RECORD-CNT)  
     MOVE "50"    TO RPT-PRCD-STUS(RECORD-CNT)  
   ELSE   
     MOVE "1"     TO RPT-CASE-STUS(RECORD-CNT)  
     MOVE "33"    TO RPT-PRCD-STUS(RECORD-CNT)  
   END-IF   
    MOVE WK-PRCD-MK(1:4)  
      TO RPT-MTGNO(RECORD-CNT)  
  WHEN "36"  
    IF WK-UPD-JDGF-STUS = "50"  
      MOVE "5"     TO RPT-CASE-STUS(RECORD-CNT)  
      MOVE "50"    TO RPT-PRCD-STUS(RECORD-CNT)  
    ELSE  
      MOVE "2"     TO RPT-CASE-STUS(RECORD-CNT)  
      MOVE "36"    TO RPT-PRCD-STUS(RECORD-CNT)  
    END-IF   
    MOVE WK-PRCD-MK(1:4)  
      TO RPT-MTGNO(RECORD-CNT)      
  WHEN "50"  
    MOVE "6" TO RPT-CASE-STUS(RECORD-CNT)  
    MOVE "50" TO RPT-PRCD-STUS(RECORD-CNT)  
    MOVE WK-PRCD-MK(8:4)  
      TO RPT-MTGNO(RECORD-CNT)      
  WHEN OTHER  
    MOVE "3" TO RPT-CASE-STUS(RECORD-CNT)  
    MOVE "18" TO RPT-PRCD-STUS(RECORD-CNT)  
    MOVE SPACES     TO RPT-MTGNO(RECORD-CNT)      
END-EVALUATE.

TO-BE
以過去的作法來說，挺沒效率的，他需要每筆資料for-loop出來，在按照條件，給予不同的值。以此段程式來說，可以用Oracle內建的CASE statement，如下：

SELECT....,  
CASE WHEN NIGT005.PRCD_STUS = '33' and NIGT001.UPD_JDGF_STUS = '50' THEN '4'       
   WHEN NIGT005.PRCD_STUS = '33' and NIGT001.UPD_JDGF_STUS <> '50' THEN '1'       
   WHEN NIGT005.PRCD_STUS = '36' and NIGT001.UPD_JDGF_STUS = '50' THEN '5'       
   WHEN NIGT005.PRCD_STUS = '36' and NIGT001.UPD_JDGF_STUS <> '50' THEN '2'       
   WHEN NIGT005.PRCD_STUS = '50' THEN '6'       
   ELSE '3'     END as CASE_STUS,   --案況  
CASE WHEN NIGT005.PRCD_STUS = '33' and NIGT001.UPD_JDGF_STUS = '50' THEN '50'       
   WHEN NIGT005.PRCD_STUS = '33' and NIGT001.UPD_JDGF_STUS <> '50' THEN '33'       
    WHEN NIGT005.PRCD_STUS = '36' and NIGT001.UPD_JDGF_STUS = '50' THEN '50'       
    WHEN NIGT005.PRCD_STUS = '36' and NIGT001.UPD_JDGF_STUS <> '50' THEN '36'       
    WHEN NIGT005.PRCD_STUS = '50' THEN '50'       
    ELSE '18'     END as PRCD_STUS,   --作業代號  
 CASE WHEN NIGT005.PRCD_STUS = '33' THEN SUBSTR(NIGT005.PRCD_MK, 1, 4)       
    WHEN NIGT005.PRCD_STUS = '36' THEN SUBSTR(NIGT005.PRCD_MK, 1, 4)       
    WHEN NIGT005.PRCD_STUS = '50' THEN SUBSTR(NIGT005.PRCD_MK, 8, 4)       
    ELSE ''     END AS MTG_NO --會次  
 FROM NIGT001, NIGT005  
 WHERE ....

Benefits
這樣的作法有幾個好處
1. 將這些if-else statement放到SQL中去處理，降低程式複雜度，也減少處理出錯的風險
2. 將這些if-else statement放到SQL中去處理，會得到最好的處理速度與效能(相較於用Java程式處理)

Oracle/PLSQL: Case Statement - http://www.techonthenet.com/oracle/functions/case.php

2011/12/20

HashMap vs. LinkHashMap

Problem

在報表的開發上，剛好遇到一個問題，我同時要餵資料給兩張iReport template(i.e. P1, P2)，然後後端用for loop將裡頭的資料抓出來，原本的方式:

1:  Map dataMap = new HashMap ();  
2:  dataMap.put("NIG071P1", p1Data);  
3:  dataMap.put("NIG071P2", p2Data);

但是此方式，並沒有如預期的依照順序產生P1, P2，反而是先印出P2、再印出P1，這是因為HashMap並不會根據你insert的順序來將資料抓出來

Solution

改用LinkedHashMap (Hash table and linked list implementation of the Map interface, with predictable iteration order. This implementation differs from HashMap in that it maintains a doubly-linked list running through all of its entries. This linked list defines the iteration ordering, which is normally the order in which keys were inserted into the map (insertion-order). )。
這是一個會根據我們insert的順序來把資料抓出來的Map，改寫如下，即可解決上述的問題：

1:  //LinkedHashMap defines the iteration ordering,   
2:  //which is normally the order in which keys were   
3:  //inserted into the map (insertion-order)  
4:  Map dataMap = new LinkedHashMap ();  
5:  dataMap.put("NIG071P1", p1Data);  
6:  dataMap.put("NIG071P2", p2Data);

Reference: http://docs.oracle.com/javase/6/docs/api/java/util/LinkedHashMap.html

2011/11/18

2011/11/12-17 京都大阪六日遊

耶誕節氣氛漸濃

道頓崛

大阪城

梅田夜景

東福寺楓紅

哲學之道

京都某處楓紅

銀閣寺楓紅

八阪神社

從清水寺遠眺kyoto tower

楓紅配藍天

2011/10/30

Top Ten Idea Killers in Software Development

Source:http://www.computer.org/portal/web/buildyourcareer/Nosce-te-Ipsum/-/blogs/top-ten-idea-killers-in-software-development

10. "This is good enough"

9. "This is how it was always done"

8. "There isn't enough time to do it right"

7. "This requires core architectural changes"

6. "Management has not prioritized it"

5. "There is already a lot on our plate"

4. "Our software is very complex; we have to be careful about making changes"

3. "No one is asking for it"

2. "We have to have consensus"

And the #1 idea killer in software development is

1. "It can't be done

Subversion Best Practices: Repository Structure

http://blogs.wandisco.com/2011/10/24/subversion-best-practices-repository-structure/?=kt_tw

2011/10/24

Grouping Data in Reports

Report groups are a flexible way to show grouped data based on one or more certain fields, or even on generic expression, that is, a group can be defined based on the specific column.

Here we will build a report to show the list of 違章案件派查明細表, 審理人員 will be grouped together, that is, the manger (suppose the report is for a manager) will know which violation case is reponsible by which 審理人員.

Follow the listed steps:
1. Go to File | New… | Report, select Simple Blue, and press Launch Report Wizard.
2. Set JavaBean as report's datasource: http://albert-myptc.blogspot.com/2011/06/how-toi-make-java-bean-datasource.html
3. Click Report Name | Right Click | Add Report Group, and New Group Wizard. Fill in Group Name | Choose field name | click Next, and Click Finish

4. You can see group header and group footer in your Report Inspector

5. Click group header | check "Start on a new page" and "Reset page number"

6. Report header and column header should all put into group header

7. JavaBean attriubtes will be dragged and dropped into detail band

8. Drag and drop group count variable to summary field

9. Done.

How to start group data on a new page

Problem
I had some problems as I built a group by report
1. Each group data should start on a new page.
2. page number should be reset

Solution
1. Move report header from title band to group header band

2. Click group header

3. Check two checkboxs, including "Start on a new page" and "Reset page number"

Here has some group options:

Option	Description
Name	This is the name of the group. You can modify the name by just clicking on the button next to the name.
Start on a new page	If this checkbox is checked, then the group starts on a new page.
Start on a new column	If this checkbox is checked, then the group starts on a new column.
Reset page number	When the group changes, the page number is reset if this option is checked.
Reprint header	If you want to reprint the group header on each page, check this option.
Min Height to Start New Page	If the value is greater than 0 (zero), it is considered as the minimum height required to keep the group on the current page.
Footer Position	This option allows us to specify where to place the group footer. The available place options are Normal, Stack at bottom, Force at bottom, and Collate at bottom.
Keep Together	This is a flag that prevents the group from splitting on two separate pages/columns.

Demo
It split to two pages as bellowing:

2011/10/13

How to Prevent a SQL Injection Attack

Reference: http://www.dzone.com/links/r/how_to_prevent_a_sql_injection_attack.html

1. Patch your SQL server regularly
2. Limit the use of dynamic queries
3. Escape user input
4. Store database credentials in a separate file
5. Use the principle of least privilege
6. Turn magic quotes off
7. Disable shells
8. Disable any other DB functionality you don’t need
9. Test your code