2014/05 Travel

從挑夫古道遠眺望龍埤

望龍埤

基隆外木山

[iReport] Exported Excel Report But Has Missing Data

Problem
I am using iReport 5.5 in my project, the requirement is to use iReport to design report template, and export the report with pdf and xls format.
The problem is as I export pdf format, I can see complete data

but as I export xls format, the xls report has lost data in page header band.

Solution
The missing data problem results from the overlapping elements (even 1 pixel).

After I fixed the overlapping problems (add one more space between elements)

I can see complete data in xls report.

How do I sort an array of files according to their last modified dates?

Apache commons IO provide LastModifiedFileComparator to do file sorting based on its last modified date/time.
It provide two instance:
1. LASTMODIFIED_COMPARATOR: Last modified comparator instance
2. LASTMODIFIED_REVERSE: Reverse last modified comparator instance

Example
1. Read files from old to new

   /**  
    * The main method.  
    *   
    * @param args  
    *      the arguments  
    */  
   public static void main(String args[]) {  
     Collection files =   
       FileUtils.listFiles(new File(FmsBatchConfig.INPUT_FOLDER_PATH), // assign file path  
                 FileFilterUtils.prefixFileFilter("COP0407"), // assign file name  
                 FalseFileFilter.FALSE);// include subdirectories  
     File[] fileArr = files.toArray(new File[files.size()]);  
     Arrays.sort(fileArr, LastModifiedFileComparator.LASTMODIFIED_COMPARATOR);//from old to new  
     // print file name  
     for (File file : fileArr) {  
       System.out.println(file.getName()+"["+new Date(file.lastModified())+"]");  
     }  
   }  

The console will print

 COP0407_01_1030501.txt[Thu May 08 16:45:01 CST 2014]  
 COP0407_01_1030502.txt[Fri May 23 10:31:45 CST 2014]  

2. Read files from new to old

   /**  
    * The main method.  
    *   
    * @param args  
    *      the arguments  
    */  
   public static void main(String args[]) {  
     Collection files =   
       FileUtils.listFiles(new File(FmsBatchConfig.INPUT_FOLDER_PATH), // assign file path  
                 FileFilterUtils.prefixFileFilter("COP0407"), // assign file name  
                 FalseFileFilter.FALSE);// include subdirectories  
     File[] fileArr = files.toArray(new File[files.size()]);  
     Arrays.sort(fileArr, LastModifiedFileComparator.LASTMODIFIED_REVERSE);//from new to old  
     // print file name  
     for (File file : fileArr) {  
       System.out.println(file.getName()+"["+new Date(file.lastModified())+"]");  
     }  
   }  

The console will print

 COP0407_01_1030502.txt[Fri May 23 10:31:45 CST 2014]  
 COP0407_01_1030501.txt[Thu May 08 16:45:01 CST 2014]  

Reference
[1] http://commons.apache.org/proper/commons-io/apidocs/org/apache/commons/io/comparator/LastModifiedFileComparator.html

How to get all files with specific name in a directory(including subdirectories) using Commons IO

Requirement
1. Search specific directory called "COP0407" 
2. Search through all directories called "COP0407" 

Supposed the directory is D:\psrdata\transfer\ftp\tofms, and has one subdirectory D:\psrdata\transfer\ftp\tofms\done

Solution
Apache commons IO package provide FileUtils for general file manipulation, here has two sample code
1. Search specific directory called "COP0407"  (exclude subdirectory)

   public static void main(String args[]) {  
     Collection files=  
     FileUtils.listFiles(new File(FmsBatchConfig.INPUT_FOLDER_PATH), //assign file path  
                    FileFilterUtils.prefixFileFilter("COP0407"), //assign file name  
                    FalseFileFilter.FALSE);//exclude subdirectories  
     //print file name  
     for(File file : files) {  
       System.out.println(file.getName());  
     }  
   }  

The console will print

 COP0407_01_1030501.txt  
 COP0407_01_1030502.txt  

2. Search through all directories called "COP0407" 

   public static void main(String args[]) {  
     Collection files=  
     FileUtils.listFiles(new File(FmsBatchConfig.INPUT_FOLDER_PATH), //assign file path  
                    FileFilterUtils.prefixFileFilter("COP0407"), //assign file name  
                    TrueFileFilter.TRUE);//include subdirectories  
     //print file name  
     for(File file : files) {  
       System.out.println(file.getName());  
     }  
   }  

The console will print

 COP0407_01_1030501.txt  
 COP0407_01_1030502.txt  
 COP0407_01_1030501 (2).txt  

Reference
[1] http://commons.apache.org/proper/commons-io/apidocs/org/apache/commons/io/FileUtils.html#listFiles(java.io.File, org.apache.commons.io.filefilter.IOFileFilter, org.apache.commons.io.filefilter.IOFileFilter)

Create custom display filter in AngularJS

Requirement

Assume we have a drop down list which used to control the currency unit display.

It has two options, the first one is dollar. You can see the amount had been used dollar as its currency unit display.

The second option is a hundred million. You can see the amount had been used hundred million as its currency unit display.

Custom display filter

AngularJS provide some build-in filter, ex. number filter can used to determine If the input is not a number an empty string is returned.

In order to create a new filter, you are going to create a fms421rCurrency module and register your custom filter with this module:

   //根據資料單位下拉單的選項，決定顯示於前端的金額單位是元或億  
   app.filter('fms421rCurrency', ['$filter', function ($filter, $scope) {  
     var fun = function(input, id) {  
       try{  
         input = BigDecimalROUND_HALF_UP(input, id);  
         return input;  
       }catch (e) {  
         return "";  
       }  
     };  
    return fun;  
   }]);  
  //根據資料單位下拉單的選項，決定金額是否要除以100000000  
   var BigDecimalROUND_HALF_UP = function(input, id){  
     if(input == 0){  
       return new BigDecimal("0");  
     }  
     var numb = new BigDecimal(input.toString());  
     var bil = new BigDecimal("100000000");   
     if ("02"==id) {//01:元, 02:億  
       numb = numb.divide(bil,0, BigDecimal.prototype.ROUND_HALF_UP);  
     }  
     return numb;  
   };  

The syntax for using filters in Angular templates is as follows:

{{ expression | filter }}

We can apply the filter in the fms421r tempate page:

 <td class="text-right border-right">   
 {{fms421rDto.dayRslt1|fms421rCurrency:model.dataunit.id|fms421rnative|number}}  
 </td>   

Reference

[1] https://docs.angularjs.org/api/ng/filter/number

[2] https://docs.angularjs.org/tutorial/step_09

[3] http://ithelp.ithome.com.tw/question/10140497

虛擬股市交易系統

如果手上資金不夠，可以利用這個虛擬交易網站做股票進出、練功
順便觀察、參考其他人的進出 http://www.cmoney.tw/

Reference
[1] http://www.cmoney.tw/notes/note-detail.aspx?cid=22814&nid=11548&utm_source=Facebook&utm_medium=Timeline&utm_term=Notes&utm_content=nid%3D11548&utm_campaign=nid%3D11548

[Oracle] LISTAGG function

Requirement
The following SQL statement will retrieve multiple row of data

SELECT PDATE,
       ACC || F_SYS_FIND_ACC_NM(:SCODE, :FYR, ACC, '000') AS ACC_NM
FROM CF0006
WHERE ACC LIKE '24%'
      AND LENGTH(TRIM(ACC))=11

But my requirement is to turn these multiple row of data into a single row per group.

Solution

Oracle provided LISTAGG function to fulfill this kind of requirement.

LISTAGG is an aggregate function that can optionally be used as an analytic (i.e. the optional OVER() clause). The following elements are mandatory:

• the column or expression to be aggregated;
• the WITHIN GROUP keywords;
• the ORDER BY clause within the grouping.

The arguments to the function are subject to the following rules:

• The measure_expr can be any expression. Null values in the measure column are ignored.
• The delimiter_expr designates the string that is to separate the measure values. This clause is optional and defaults to NULL.
• The order_by_clause determines the order in which the concatenated values are returned. The function is deterministic only if the ORDER BY column list achieved unique ordering.

For example. The following group-set aggregate example lists, for each pdate in the CF0006 table, the ACC_NM in that CF0006 in order of their pdate (separate with comma):

SELECT PDATE,
       LISTAGG(ACC || F_SYS_FIND_ACC_NM(:SCODE, :FYR, ACC, '000'), ', ') WITHIN
       GROUP (ORDER BY PDATE) "ACC_NM_LIST"
FROM CF0006
WHERE ACC LIKE '24%'
      AND LENGTH(TRIM(ACC))=11
GROUP BY PDATE;

Reference
[1] http://docs.oracle.com/cd/E11882_01/server.112/e10592/functions089.htm#SQLRF55593
[2] http://www.oracle-developer.net/display.php?id=515

java.lang.NullPointerException: Cannot find parameter "json" from request.

Problem

I found out I fail to execute print functions in every function in my system.

And the console throw this exception message:

 java.lang.NullPointerException: Cannot find parameter "json" from request.  
   at java.util.Objects.requireNonNull(Objects.java:226) ~[na:1.7.0_25]  
   at com.cht.commons.web.resolver.JsonParamResolver.resolveArgument(JsonParamResolver.java:34) ~[cht-commons-web-0.1.0-SNAPSHOT.jar:0.1.0-SNAPSHOT]  

Root Cause
As I check its request header, it submit nothing

It result from my request had been blocked by AdBlock (Chrome Plug-in), which I installed yesterday. That's why I failed to execute every print function today.

As I disabled this Chrome plug-in, AdBlock, and check its request header again. We can see request header had submitted data.

Finally, my report function had recovered.

ORA-01422 Error From Oracle Function

Problem
I wrote an Oracle function on my own as bellows:

CREATE OR REPLACE FUNCTION F_FMS420R_GET_ACCUM_AMTA (S_YYY_MM IN VARCHAR2 --起始年月
 ,E_YYY_MM IN VARCHAR2 --結束年月
 ,ASP IN VARCHAR2 --性質別
) RETURN NUMBER IS AMT_A NUMBER(20, 2) := 0;

 BEGIN
SELECT AMT_A INTO AMT_A FROM
  (SELECT YEAR, ASP, SUM(AMT_A) AS AMT_A
   FROM FMS420FA
   WHERE (YYY_MM BETWEEN S_YYY_MM AND E_YYY_MM)
     AND ASP = ASP
   GROUP BY YEAR, ASP);

 RETURN (AMT_A);

 END F_FMS420R_GET_ACCUM_AMTA;

After I test it, it show this error message as bellows:

 ORA-01422: exact fetch returns more than requested number of rows (精確擷取傳回的列數超過所要求的列數) 
 ORA-06512: 在 "AP_PSR.F_FMS420R_GET_ACCUM_AMTA", line 9  
 ORA-06512: 在 line 11  

But this select SQL statement must return one record, if I provide all search criteria

SELECT YEAR,
       ASP,
       SUM(B.AMT_A) AS AMT_A
FROM FMS420FA B
WHERE B.YYY_MM BETWEEN :S_YYY_MM AND :E_YYY_MM
  AND B.ASP = :ASP
GROUP BY YEAR,
         ASP

Root cause
This error result from the parameter name cannot be equal to database column name. If yes, Oracle will not know what I am doing and ignore this search criteria. That's why it will have ORA-01422 error.

Solution
Just rename input parameter name from ASP to INPUT_ASP, then this problem can be resolved.

CREATE OR REPLACE FUNCTION F_FMS420R_GET_ACCUM_AMTA (S_YYY_MM IN VARCHAR2 --起始年月
 ,E_YYY_MM IN VARCHAR2 --結束年月
 ,INPUT_ASP IN VARCHAR2 --性質別
) RETURN NUMBER IS AMT_A NUMBER(20, 2) := 0;

 BEGIN
SELECT AMT_A INTO AMT_A FROM
  (SELECT YEAR, ASP, SUM(AMT_A) AS AMT_A
   FROM FMS420FA
   WHERE (YYY_MM BETWEEN S_YYY_MM AND E_YYY_MM)
     AND ASP = INPUT_ASP
   GROUP BY YEAR, ASP);

 RETURN (AMT_A);

 END F_FMS420R_GET_ACCUM_AMTA;