Project

General

Profile

Actions
Copy link

Feature #118

closed

DP Profit Batch File

Added by Chooi-Mey about 13 years ago. Updated over 12 years ago.

Status:
Closed
Priority:
Normal
Assignee:
Chee-Hoong
Category:
Batch
Target version:
0.1
Start date:
08/03/2011
Due date:
11/04/2011
% Done:

100%

Estimated time:
Resolution:
Fixed

Description

Requirement:

  1. Construct Currency Hashtable:
    • Query all latest currency info from Mend_CurrConv table, into a hashtable based on below query:
      SELECT CTRY_CODE, CURR_CODE, CURR_EXCH_RATE FROM MEND_CURRCONV A WHERE A.EFFECTIVE_DATE = (SELECT MAX(B.EFFECTIVE_DATE) 
FROM MEND_CURRCONV B 
GROUP BY B.CTRY_CODE HAVING B.CTRY_CODE = A.CTRY_CODE) AND A.STATUS = 'A'
    • Populate the return query result into hashtable, with "CTRY_CODE" as key, while "CURR_CODE|CURR_EXCH_RATE" as value.
  2. Retrieve Member DP Records:
    • Query member's earned DP Profit from VIP_Stat and Member_stat table with below query logic:
      SELECT VIP.VIP_ID AS MEMBER_ID, VIP.DP_EARNED AS DP_EARNED, VIP.COUNTRY_CODE AS COUNTRY_CODE FROM VIP_STAT VIP WHERE VIP.DATA_MONTH = '2011-05-31' AND VIP.DP_EARNED <> 0
  3. COUNTRY (pos 41-43) field:
    If the retrieval country_code = 156 (CHINA), 344 (HK) or 446 (MACAU), COUNTRY field mapped to 344 (HK).
    The rest as it is.
  4. TRAN_TYPE (pos 135-139) field:
    If the retrieval dp_earned value < 0, set it to "20501";
    If the retrieval dp_earned value > 0, set it to "20101"
  5. TRAN_AMT (pos 160-172) field:
    If the retrieval dp_earned value < 0, remove the negative sign.
  6. CURRENCY_FROM (pos 462-464) field:
    Set it default to "USD"
  7. CURRENCY_TO (pos 465-467) field:
    Based on the COUNTRY field value (item 3 above) as key, get the CURRENCY_TO from Currency Hashtable.
  8. EXCHG_RATE (pos 466-474) field:
    Based on the COUNTRY field value (item 3 above) as key, get the EXCHG_RATE from Currency Hashtable.
  9. FINGERPRINT (pos 475-506) field:
    Hashed the message of "CODE^MBR_ID^PIN^TRAN_AMT" by using the HmacMD5 hashing method as below:
    //Constants
String transactionKey    = "cosway88";

KeyGenerator kg = KeyGenerator.getInstance("HmacMD5");
SecretKey key = new SecretKeySpec(transactionKey.getBytes(), "HmacMD5");
// A MAC object is created to generate the hash using the HmacMD5 algorithm
Mac mac = Mac.getInstance("HmacMD5");
mac.init(key);

//Pass in method argument
String inputstring = "CEN001^MEM001^182838^10.50";
byte[] result = mac.doFinal(inputstring.getBytes());
// Convert the result from byte[] to hexadecimal format
StringBuffer strbuf = new StringBuffer(result.length * 2);

for(int i=0; i< result.length; i++)
{
    if(((int) result[i] & 0xff) < 0x10)
        strbuf.append("0");
    strbuf.append(Long.toString((int) result[i] & 0xff, 16));
}

String fingerprint = strbuf.toString().toUpperCase();

Fixed length File Format. Refer to the DP_Batch_File_Format_PA5.xls in DP Batch File Format, column "Profit" for details of field.

Files

Download all files
DP_PROFIT_FINANCE_REFERENCE_072011_Sample.xls (102 KB) DP_PROFIT_FINANCE_REFERENCE_072011_Sample.xls Finance Reference Report Sample Chooi-Mey, 09/05/2011 11:22 AM
nicholasClass.sh (297 Bytes) nicholasClass.sh Chee-Hoong, 09/19/2011 12:38 PM
poi-3.7-20101029.jar (1.6 MB) poi-3.7-20101029.jar Chee-Hoong, 09/19/2011 12:39 PM
Actions
Copy link

Also available in: Atom PDF