201404 Travel

烏來

201404 Kyoto

京都某處櫻花

金閣寺

彥根城

平等院

奈良

平野神社

平安神宮

CronMaker - a utility which helps you to build cron expressions

It's a good utility to build cron expression as I use Quartz to write batch program.
http://www.cronmaker.com/

SVN error on Eclipse: org.tigris.subversion.javahl.ClientException: Attempted to lock an already-locked dir

Problem
As I commit my source code to SVN in eclipse, but Eclipse shut down unexpectedly. After I launch Eclipse again, and try to commit again, it show this error message:

 org.apache.subversion.javahl.ClientException: Attempted to lock an already-locked dir  
 svn: Commit failed (details follow):  
 svn: Working copy 'D:\workspace\fms\fms-webapp\src\main\java\gov\nta\fms\web\controller' locked.  
 svn: 'D:\workspace\fms\fms-webapp\src\main\java\gov\nta\fms\web\controller' is already locked. 

Solution
Right click on the offending project, click Team and then select Refresh/Cleanup. SVN gets the offending .lock files and deletes them. 

cleanup D:/workspace/fms/fms-webapp/src/main/java  

After Refresh/Cleanup, I can commit source code successfully

commit -m "add 4 spaces for rptInfo parameter" D:/workspace/fms/fms-webapp/src/main/java/gov/nta/fms/web/controller/Fms406rReportController.java  
   Sending    D:/workspace/fms/fms-webapp/src/main/java/gov/nta/fms/web/controller/Fms406rReportController.java  
   Transmitting file data ...  
   Committed revision 1264.  

Reference

[1] http://sherk.wordpress.com/2008/09/15/svn-error-on-eclipse-orgtigrissubversionjavahlclientexception-attempted-to-lock-an-already-locked-dir/

How to set Static IP Address from command line

Requirement
Owing I need to move workplace from office to customer site lately, I need to change my laptop's IP address as I change workplace. So I edit a batch file to set my static IP address automatically.

Syntax
Let’s say I want to give my Windows 7 system an IP address of 10.144.38.195, a subnet mask of 255.255.255.0, a default gateway of 10.144.38.254. Type the following command and save as a batch file with encoding with ANSI

 netsh interface ipv4 set address name="區域連線" source=static address=10.144.38.195 mask=255.255.255.0 gateway=10.144.38.254  

The address will be found via netsh interface ipv4 show interfaces command.

Idx   Met     MTU     狀態         名稱  
 --- ---------- ---------- ------------ ---------------------------  
  1     50 4294967295 connected   Loopback Pseudo-Interface 1  
  13     50    1500 disconnected 無線網路連線  
  12      1    1500 disconnected 區域連線  
  19     30    1500 disconnected 區域連線 2  
  16     40    1500 disconnected Bluetooth 網路連線  
  20     20    1500 connected   區域連線 3  

In this case, I would like to configure 區域連線. So the address name="區域連線"

Step by step
Step1. open command prompt.
Step2. execute the batch script file, i.e.SetIP4Office.bat

 C:\Users\albert\Desktop>SetIP4Office.bat  
 C:\Users\albert\Desktop>netsh interface ipv4 set address name="區域連線" source=static address=10.14  
 4.38.195 mask=255.255.255.0 gateway=10.144.38.254  

Well Done!

So you can edit different batch files to set different IP address for different workplace.

Reference
[1] http://www.windowsreference.com/networking/how-to-set-staticdhcp-ip-address-from-command-line/

Bat(Batch) script 執行出現出現中文亂碼問題

Problem
I edit a batch script which be used to set IP address

netsh interface ipv4 set address name="區域連線" source=static address=10.144.38.195 mask=255.255.255.0 gateway=10.144.38.254 

But when I execute this batch script in command prompt, it seems that the Traditional Chinese characters are broken.

C:\Users\albert\Desktop>netsh interface ipv4 set address name="?€?€??" source=static address=10.14  
 4.38.195 mask=255.255.255.0 gateway=10.144.38.254  
 檔案名稱、目錄名稱或磁碟區標籤語法錯誤。  

Solution
This problem results from the encoding of the batch script file. You need to change its encoding from UTF-8 to ANSI.

Reference
[1] http://www.bathome.net/viewthread.php?tid=24088

投資的最高境界是「不交易」！

這篇文章不錯

[quote 1]「掌握波段，比殺進殺出要賺得多。」

[quote 2]大盤K值從高處滑落到20附近，就開始買，一路往下買，回彈到80以上，就一路往上賣。

[quote 3]「停損」當然是最重要的一招。只要持股跌了10%到15%，就該執行停損，認賠出場。

[quote 4]如果你忘了當初買進的價格，或許你在面對它反彈到相對高價時，你會捨得去賣它。什麼叫「相對高價」？就是技術指標呈現過熱訊號，或是股價來到重要壓力區。

[quote 5]股市還有一句名言：「會買股票不稀奇，會賣股票才是師父。」就算是手上的股票處於獲利狀態，也要懂得「停利」。KD值都來到95以上，這時你若還奢望持股會繼續漲不停，就很可能只是一場「紙上富貴」

http://www.businesstoday.com.tw/article-content-80494-106484?page=1

How do I insert a record if it does not exist in Oracle?

Requirement

Here has two report items as following:

Each year has its own report items, ex. 102年度--未滿一年之短期融資, 103年度--未滿一年之短期融資, 102年度--公債及賒借收入, 103年度--公債及賒借收入, etc.

This sort of information had been stored in this table.

Our requirement is to generate the two report items automatically next year.

Solution
We can use merge statement to accomplish this requirement.
Step1: Find record(s) in FMS405FA based on our defined search criteria
Step2: Result
2.1: If does not find any record, then insert data into FMS405FA
2.2: If found record(s), then skip it.

MERGE INTO FMS405FA T1 
USING DUAL ON(T1.ACCYR =
     (SELECT TRIM(TO_CHAR(EXTRACT(YEAR FROM SYSDATE)-1911, '000'))  FROM DUAL)
      AND T1.RPT_TYPE='FMS434R'
      AND T1.RPT_NO='0001') 
WHEN NOT MATCHED THEN 
  INSERT(T1.ACCYR, T1.RPT_TYPE, T1.RPT_NO, T1.RPT_NM)
  VALUES(
         (SELECT TRIM(TO_CHAR(EXTRACT(YEAR FROM SYSDATE)-1911, '000'))
          FROM DUAL), 'FMS434R', '0001',

         (SELECT TRIM(TO_CHAR(EXTRACT(YEAR FROM SYSDATE)-1911, '000'))
          FROM DUAL)||'年度--未滿一年之短期融資')

MERGE INTO FMS405FA T1 
USING DUAL 
ON(T1.ACCYR =
   (SELECT TRIM(TO_CHAR(EXTRACT(YEAR FROM SYSDATE)-1911, '000')) FROM DUAL)
   AND T1.RPT_TYPE='FMS434R'
   AND T1.RPT_NO='0002') 
WHEN NOT MATCHED THEN 
  INSERT(T1.ACCYR, T1.RPT_TYPE, T1.RPT_NO, T1.RPT_NM)
  VALUES(
         (SELECT TRIM(TO_CHAR(EXTRACT(YEAR FROM SYSDATE)-1911, '000'))
          FROM DUAL), 'FMS434R', '0002',

         (SELECT TRIM(TO_CHAR(EXTRACT(YEAR FROM SYSDATE)-1911, '000'))
          FROM DUAL)||'年度--公債及賒借收入')

Reference
[1] http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm#SQLRF01606

How to create a Boolean field in Oracle

Problem
If we would like to create a field, it will store the information if this datum had create by external system or be added by manual. 
Originally, I would like to crate a field with Boolean data type. But it seems that Oracle does not support Boolean data type.

Solution
We can use the check constraint to limit its value, i.e. 'Y' means adding by manual, 'N' means adding by external system.
ex.

ALTER TABLE FMS435FB ADD CONSTRAINT SELF_ADDED_CHECK CHECK (SELF_ADDED IN ('N',
                                                                           'Y'));

Reference
[1] http://www.techonthenet.com/oracle/check.php

How to set the width of modal dialog

Problem

User claimed they do not want to see the scroll bar in the modal dialog.

How do I set the width of dialog manually?

Solution

We can set the modal dialog thru style.

Step1.  Defined a css style for the modal dialog in the head section of an HTML page, and using the style tag.

 <style type="text/css">  
 .fms435rTab3Dialog {  
   width: 900px;  
   left: 40%;  
 }  
 </style>  

Step2. Assign css into windowClass which is one of modal dialog's parameter

1:  $scope.create = function() {  
2:        openDialog();  
3:      };  
4:      var openDialog = function(){  
5:        var options = {  
6:            templateUrl: 'fms435rTab3Dialog.html',  
7:            controller: 'fms435rTab3Controller',  
8:            windowClass:"fms435rTab3Dialog",  
9:            backdrop: true,  
10:            keyboard: true,  
11:            backdropClick: true  
12:        };  
13:        $modal.open(options);  
14:      };  

See...the width had been adjusted.

Reference

[1] https://github.com/angular-ui/bootstrap/blob/master/src/modal/docs/readme.md

Error: ng:areq Bad Argument

Problem
I have a html page with 2 tabs.

As I entered this page, it occurred javascript errors with "Error: ng:areq" and link to this page: http://docs.angularjs.org/error/ng/areq?p0=fms435rTab1Controller&p1=not%20a%20function,%20got%20undefined

Trace the Problem
There are three html files to consist of this page. Each html file have its own JavaScript file, and will be imported by mail page.

fms435r.js

 (function() {  
   var app = angular.module("fms435rApp", [ 'ntaCommonModule' ]);  
 })();  

fms435rTab1.js

(function() {  
   var app = angular.module("fms435rApp");  
   app.factory('fms435rService', function(cResource) {  
     return {};  
   });  
   app.controller('fms435rTab1Controller', function($scope, fms435rService,  
       stateManager, alerter, userHolder) {      
   });  
 })(); 

fms435rTab2.js

 (function() {  
   var app = angular.module("fms435rApp", [ 'ntaCommonModule' ]);  
   app.factory('fms435rService', function(cResource) {  
     return {};  
   });  
   app.controller('fms435rTab2Controller', function($scope, fms435rService,  
       stateManager, alerter, userHolder) {  
   });  
 })();  

Root Cause
The problem result from fms435rTab2.js. 
It misuse angular.module, because it create a new module, not to retrieve.
Therefore, you should fix it as bellows:

 (function() {  
   var app = angular.module("fms435rApp");  
   app.factory('fms435rService', function(cResource) {  
     return {};  
   });  
   app.controller('fms435rTab2Controller', function($scope, fms435rService,  
       stateManager, alerter, userHolder) {  
   });  
 })(); 

Here is the syntax

Param	Type	Details
name	string	The name of the module to create or retrieve.
requires (optional)	Array.=	If specified then new module is being created. If unspecified then the module is being retrieved for further configuration.
configFn	Function	Optional configuration function for the module.  Same as Module#config().

Reference
[1] http://docs.angularjs.org/api/ng/function/angular.module

ORACLE/PLSQL: ADD_MONTHS FUNCTION

Requirement
If we would like to do month calculation in SQL, how to do it?

Solution
Oracle provided a build-in ADD_MONTHs function for us, the syntax is as bellow:

For example, if I would like to get last month and in Chinese calendar system format, the SQL statement will be looking like this:

SELECT TRIM(TO_CHAR(EXTRACT(YEAR
                            FROM ADD_MONTHS(SYSDATE, -1))-1911, '000'))|| TRIM(TO_CHAR(EXTRACT(MONTH
                                                                                             FROM ADD_MONTHS(SYSDATE, -1)), '00')) AS LAST_MONTH
FROM DUAL

Reference
[1] http://www.techonthenet.com/oracle/functions/add_months.php
[2] http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions004.htm

How to resolve ORA-01790

Problem
When I use UNION ALL syntax to union two select SQL statement, Oracle throw "ORA-01790 expression must have same datatype as corresponding expression" error message.

This error result from I would like to union two result with different data type.

Solution
You can make good of CAST function to cast into the same data type and do union all.

For example. 

SELECT CAST(NVL(FMS406FB.DAY_RSLT, 0) AS NVARCHAR2(30)) DAY_RSLT --每日結存數
FROM .....

Reference
[1] http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions016.htm

2014/02 Travel

自由廣場

Oracle: how to do insert if data doesn't exist

Here is my database schema:

Requirement
If we cannot find data via ROC_YM, primary key, in FMS415FA, then it should insert specific ROC_YM data into this table

How to do to fulfill this requirement

INSERT INTO FMS451FA(ROC_YM)
SELECT :ROC_YM
FROM dual
WHERE NOT EXISTS
    (SELECT ROC_YM
     FROM FMS451FA
     WHERE ROC_YM = :ROC_YM)

Oracle: how to UPSERT (Update or Insert)

Assume we have a requirement to read text file which sent by external system, and the UPSERT operation either updates or inserts a row in a table, depending if the table already has a row that matches the data.

Oracle 9i introduced the MERGE statement. The MERGE statement takes a list of records which are usually in a staging table, and adds them to a master table. If the record exists in the master table, it should be updated with the new values in the staging table, otherwise insert the record from the staging table.

MERGE statement doesn't always need "multiple tables", but it does need a query as the source. Something like this should work:

MERGE INTO FMS420FB T1 USING DUAL ON (T1.YEAR = :YEAR
                                      AND T1.MONTH= :MONTH
                                      AND T1.SUBJECT_ID=:SUBJECT_ID) WHEN MATCHED THEN
UPDATE
SET T1.SUBJECT=:SUBJECT,
               T1.AMT=:AMT WHEN NOT MATCHED THEN
INSERT (T1.YEAR,
        T1.MONTH,
        T1.SUBJECT_ID,
        T1.SUBJECT,
        T1.AMT)
VALUES(:YEAR,
       :MONTH,
       :SUBJECT_ID,
       :SUBJECT,
       :AMT)

Reference
[1] http://www.oracle-base.com/articles/10g/merge-enhancements-10g.php
[2] http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_9016.htm#SQLRF55128

Create Oracle Function

Here is a SQL statement:

SELECT ROC_YM
FROM FMS451FA

The result is 

Owning to client's requirement, we need to add slash between Chinese year and month. Therefore, the SQL statement will adjust as bellows:

SELECT SUBSTR(ROC_YM, 1, 3)||'/'||SUBSTR(ROC_YM, 4, 2) AS ROC_YM
FROM FMS451FA

The result will be converted into:

   

Because it is a common requirement, and apply to all functions. So we can create an Oracle function to increase reusability.

Here is the syntax to create Oracle function

Hence, we create an Oracle function to add slash between Chinese year and month

CREATE OR REPLACE FUNCTION FORMAT_YYYMM(INPUT VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN SUBSTR(INPUT, 1, 3)||'/'||SUBSTR(INPUT, 4, 2); END;

Whenever we encounter this date formatting requirement, we can reuse this function as bellows:

SELECT FORMAT_YYYMM(ROC_YM)
FROM FMS451FA

Reference

[1] http://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_5009.htm

[2] http://www.techonthenet.com/oracle/functions.php

Online SQL Formatting Tool

In some situations, we may need to figure out the report bugs via extracting SQL statement from console or log file. But the SQL statement normally does not have any formatting, and hard to read. It may lead to more debug time to spend. 

Here is a helpful SQL formatting tool: http://sqlformat.appspot.com/

Copy your SQL statement into this text area

Click Format SQL button or use Ctrl+F hotkey

The formatting SQL statement will show in the button of page

常用JavaScript function整理

 //取得今年民國年  
 function getCurrentYear(){  
    var date = new Date();  
    return date.getFullYear() - 1911;    
 }  
 //取得當下月份  
 function getCurrentMonth(){  
   var date = new Date();  
   return date.getMonth()+1;  
 }  
 //取得當下日期  
 function getCurrentDate(){  
   var date = new Date();  
   return date.getDate();  
 }  
 //取得當月的第一天(民國年+月+日)  
 function getFirstDateOfCurrentMonth(){  
   var date = new Date();  
   var currentYear = date.getFullYear() - 1911;  
   var currentMonth = date.getMonth()+1;  
   return leftPad(String(currentYear), 3) +  
       leftPad(String(currentMonth), 2)+  
       leftPad('1', 2);  
 }  
 //取得當天日期  
 function getCurrentDate(){  
   var date = new Date();  
   var currentYear = date.getFullYear() - 1911;  
   var currentMonth = date.getMonth()+1;  
   var currentDate = date.getDate();  
   return leftPad(String(currentYear), 3) +  
       leftPad(String(currentMonth), 2)+  
       leftPad(currentDate, 2);  
 }  
 //取得當月最後一天  
 function getEndDayInMonth(year, month){  
   return XDate.getDaysInMonth(year, parseInt(month)-1);  
 }  
 //減去指定年，如20141104減去2年，變成20121104  
 function minusYears(date, year){  
   return date.addYears(0 - parseInt(year), true).toString('yyyyMMdd');  
 }  
 //減去指定月，如20141104減去2個月，變成20140904  
 function minusMonths(date, month){  
   return date.addMonths(0 - parseInt(month), true).toString('yyyyMMdd');  
 }  
 //確認是否為數字  
 function isNumber(n) {  
    return !isNaN(parseFloat(n)) && isFinite(n);  
 }  
 //檢查是串是否不為空值且不為undefined  
 function isNotEmptyOrUndefined(str){  
  return str != '' && !angular.isUndefined(str) && str != null;    
 }  
 //檢查是否為空值且為undefined  
 function isEmptyOrUndefined(str){  
  return str == '' || angular.isUndefined(str) || str == null;    
 }  
 //檢查日期起迄，如起始日期為1021101，結束日期為1021103，其會回傳true;  
 //如起始日期為1021103，結束入其為1021101，其會回傳false  
 function isValidStartAndEndDate(startDate, endDate){  
   var isValid = false;  
   var startYear = parseInt(startDate.substring(0, 3))+1911;  
   var startMonth = parseInt(startDate.substring(3, 5)-1);  
   var startDay  = parseInt(startDate.substring(5, 7));  
   var endYear  = parseInt(endDate.substring(0, 3))+1911;  
   var endMonth  = parseInt(endDate.substring(3, 5)-1);  
   var endDay   = parseInt(endDate.substring(5, 7));  
   var sXdate = new XDate(startYear, startMonth, startDay);  
   var eXdate = new XDate(endYear, endMonth, endDay);  
   var diffDays = sXdate.diffDays(eXdate);  
   if(diffDays < 0){  
     isValid = false;  
   }else{  
     isValid = true;  
   }  
   return isValid;  
 }  
 //檢查日期起迄，如起始日期為1020101，結束日期為1020131，其會回傳true;  
 //如起始日期為1031103，結束入其為1021101，其會回傳false  
 function isValidStartAndEndYYYMM(startDate, endDate){  
   var isValid = false;  
   var startYear = parseInt(startDate.substring(0, 3))+1911;  
   var startMonth = parseInt(startDate.substring(3, 5)-1);  
   var startDay  = parseInt(startDate.substring(5, 7));  
   var endYear  = parseInt(endDate.substring(0, 3))+1911;  
   var endMonth  = parseInt(endDate.substring(3, 5)-1);  
   var endDay   = parseInt(endDate.substring(5, 7));  
   var sXdate = new XDate(startYear, startMonth, startDay);  
   var eXdate = new XDate(endYear, endMonth, endDay);  
   var diffDays = sXdate.diffMonths(eXdate);  
   if(diffDays < 0){  
     isValid = false;  
   }else{  
     isValid = true;  
   }  
   return isValid;  
 }  
 //西元轉民國，如20141104轉1021104  
 function convertFromWesternToChinse(western){  
   var year = parseInt(western.substring(0, 4))-1911;  
   var monthDate = western.substring(4, western.length);  
   return leftPad(String(year), 3)+monthDate;  
 }  
 //若未滿位，左邊補0  
 function leftPad(val, length) {  
   var str = '' + val;  
   while (str.length < length) {  
     str = '0' + str;  
   }  
   return str;  
 }  

註: 有用到xdate字眼的部分，要到 http://arshaw.com/xdate/ 這裡下載js檔

Oracle MERGE statement

If you would like to select rows from one or more sources for update or insertion into a table, you can make good use of MERGE statement. 
You can specify conditions to determine whether to update or insert into the target table.
Semantics

• INTO Clause
• your target table
• USING Clause
• your data source
• ON Clause
• set your conditions in on clause
• merge_update_clause
• specifies the new column values of the target table
• merge_insert_clause
•  specifies values to insert into the column of the target table if the condition of the ON clause is false

Here is a practical example:

 MERGE INTO FMS451FA T1  
 USING(  
  SELECT SUBSTR(ROC_YM,1,3) AS ROC_YR   
     ,SUBSTR(ROC_YM,4,2) AS ROC_MM  
     ,CUM_INCM_TAX --當月收入(2)-稅課收入   
     ,CUM_NON_INCM_TAX --當月收入(2)-非稅課收入(含其他)   
     ,CUM_INCM_LOAN --當月收入(2)-融資財源   
     ,CUM_TTL_INCM --當月收入(2)-合計   
     ,CUM_TTL_PAY_AMT --當月支出(3)   
     FROM FMS406VE  
 ) T2  
 ON (T1.ROC_YM = T2.ROC_YR||T2.ROC_MM)  
 WHEN MATCHED THEN UPDATE SET T1.CUM_INCM_TAX = T2.CUM_INCM_TAX, --稅課收入  
                T1.CUM_NON_INCM_TAX = T2.CUM_NON_INCM_TAX,--非稅課收入(含其他)  
                T1.CUM_INCM_LOAN = T2.CUM_INCM_LOAN,--融資財源  
                T1.CUM_TTL_INCM = T2.CUM_TTL_INCM,--合計   
                T1.CUM_TTL_PAY_AMT = T2.CUM_TTL_PAY_AMT--當月支出(3)   
 WHEN NOT MATCHED THEN INSERT (T1.ROC_YM,   
                T1.CUM_INCM_TAX,   
                T1.CUM_NON_INCM_TAX,   
                T1.CUM_INCM_LOAN,   
                T1.CUM_TTL_INCM,   
                T1.CUM_TTL_PAY_AMT)   
 VALUES(T2.ROC_YR||T2.ROC_MM,   
     T2.CUM_INCM_TAX,   
     T2.CUM_NON_INCM_TAX,   
     T2.CUM_INCM_LOAN,   
     T2.CUM_TTL_INCM,   
     T2.CUM_TTL_PAY_AMT);