Feature #118
closedDP Profit Batch File
100%
Description
Requirement:
- 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.
- Query all latest currency info from Mend_CurrConv table, into a hashtable based on below query:
- 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
- Query member's earned DP Profit from VIP_Stat and Member_stat table with below query logic:
- 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. - 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" - TRAN_AMT (pos 160-172) field:
If the retrieval dp_earned value < 0, remove the negative sign. - CURRENCY_FROM (pos 462-464) field:
Set it default to "USD" - CURRENCY_TO (pos 465-467) field:
Based on the COUNTRY field value (item 3 above) as key, get the CURRENCY_TO from Currency Hashtable. - EXCHG_RATE (pos 466-474) field:
Based on the COUNTRY field value (item 3 above) as key, get the EXCHG_RATE from Currency Hashtable. - 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
Updated by Chooi-Mey over 13 years ago
Amendment on item (5) TRAN_AMT:
TRAN_AMT = dp_earned (retrieval) * EXCHG_RATE
Note: dp_earned is in USD currency value. Take the absolute value via Math.abs method.
Updated by Chooi-Mey over 13 years ago
Add in:
item 11) TRAN_ID, set it to 'PROFITmmyyyy', where mmyyyy is the profit month.
Updated by Chooi-Mey over 13 years ago
For the batch file format, please refer to DP Batch File Format, for the latest version.
Updated by chin-yeh over 13 years ago
removed all old attachments. refer to the DP Batch File Format for the latest documents.
Updated by chin-yeh over 13 years ago
- File deleted (
DP_Batch_File_Format_PA3.xls)
Updated by chin-yeh over 13 years ago
- File deleted (
DP_Batch_File_Format_PA3.xls)
Updated by Chooi-Mey over 13 years ago
Changes:
Do not need to retrieve records from MEMBER_STAT, as BO earned DP profit has already been converted into cash pay out via eAccount, therefore BO will not able to utilize DP during purchase.
Only need to retrieve data from VIP_STAT.
Updated by Chee-Hoong over 13 years ago
means the the SQL to retrive VIP_STAT and MEMBER_STAT table did not need to execute? ty
Updated by Chee-Hoong over 13 years ago
or remove the UNION SELECT MEMBER_STAT?
Updated by Chooi-Mey over 13 years ago
Only remove the UNION SELECT MEMBER_STAT from SQL query.
Updated by chin-yeh over 13 years ago
refer to the issue description for the latest data retrievin SQL
Updated by chin-yeh over 13 years ago
Please send the sample generated file to Benny Kuok Chan Weng [CWKuok@cosway.com.my] by today, do CC me and chooi-mey. If there's any issue, do update here. Thanks.
Updated by Chee-Hoong over 13 years ago
to chin yeh, the sample file is gen-ed.
u wan to hav a look first or i direct email to the receipient?
Updated by chin-yeh over 13 years ago
work with Benny to make sure the system can process the generated file.
do update the issue to reflect the current progress.
Updated by chin-yeh over 13 years ago
- TRAN_ID column - the data month will be the previous month of the report generation date
- e.g. if report generation month is August 2011, then the value of the column would be PROFIT072011
- able to accept input parameter to generate batch file for different month, for example, 092011, 102011
Updated by Chooi-Mey over 13 years ago
Need to apply the logic (same as bank file generation checking) that check whether is ready to generate not.
Refer to GenProfitPaymentForUkBank, method "getUserInputOrSystemParam" logic checking, to check whether is the publish flag (MEND_PROC_IND) is set to 'Y' in table MTH_END_CALC, based on pass in month and year param/sys date.
Updated by Chee-Hoong over 13 years ago
to chooi mey, is it if date not null then READY_TO_GENERATE set to true value in hashtable?
Updated by Chee-Hoong over 13 years ago
sample file sent to benny and waiting for feedback
Updated by chin-yeh over 13 years ago
The Profit from member of country Taiwan which belongs to Foreigner.- The Profit from member of country Taiwan which not belongs to Foreigner.
- The Profit from member of country apart from Taiwan.
Remember to refer to the latest batch file format, DP Batch File Format
Suggestion:- populate the data to a new table, vip_stat_dummy and point your batch program to the new table
- below is the sample SQL you could use to populate part of the table's columns:
select a.sacntry, b.shlogid from shaddr a,shopper b where b.shrfnbr=a.sashnbr and b.shshtyp = 'S' and b.shopper_grp = '9' and b.status ='A' and a.saadrflg='P' and a.sacntry in ('036')
- replace the sacntry with the country code you want to query
- populate the dp_earned column with dummy values
- populate the table with at least 5-7 country codes, each country code has 15-20 member ID
- try to only populate the columns that needed by batch file, e.g. dp_earned
- assume all Taiwan ID is not foreigner
Updated by chin-yeh over 13 years ago
- output filename convention is DP_PROFITMMyyyy.txt
- MMyyyy - profit month, e.g. 092011 for September 2011
- Example: DP_PROFIT092011.txt, DP_PROFIT102011.txt
- output folder is
/usr/ecosway/offlinefiles/etransfer
- no need to specify any value for the IS_FORE column. Just leave it blank
Updated by chin-yeh over 13 years ago
- how to deploy the batch program
- how to execute the batch program
- how to configure the application properties if any
It would be great if the guides are written in wiki so everyone can view or edit it when necessary.
Updated by Chee-Hoong over 13 years ago
•how to deploy the batch program
•how to execute the batch program
•how to configure the application properties if any
for these purposes
is it different from other batch program as need to be planned?
Updated by chin-yeh over 13 years ago
Just prepare for the profit batch program first.
Besides that, here are the latest changes needed:- change the PRO_CODE for profit to Profit, originally is Bonus
- refer to the latest DP Batch File Format
Updated by Chee-Hoong over 13 years ago
neeed to gen and send sample file again?
Updated by chin-yeh over 13 years ago
yes, please generate again and send to Benny.
Updated by Chooi-Mey over 13 years ago
Bugs:
1) Those VIP residing in China, Macau and Hong Kong, should mapped into HKD currency (Currency_To), instead of RMB/MC.
Updated by Chee-Hoong over 13 years ago
the macau and china residing country is mapped to hong kong residing country in program and file generated.
only data inside database not changed.
Updated by Chooi-Mey over 13 years ago
The Currency_to should mapped to HKD as well, besides the residing country. Same to exchange rate.
It means when system detect the actual residing country of the VIP is 156/446, when generate the profit batch file, system need to treat them based at HK, instead of their originated country. This is because those VIP is actually earned profit/utilized DP balance in HKD currency, instead of RMB/MC.
Updated by Chee-Hoong over 13 years ago
ok i get wat u mean. now the file regenerated
Updated by Chooi-Mey over 13 years ago
For DP Amount in profit file, the value need to be in Local currency of the mapped country.
The retrieval value from VIP_STAT is defaulted in USD value. So need to perform currency conversion based on the latest exchange rate (from MEND_CURRCONV table) and its mapped country/currency.
DP AMOUNT = dp_earned * exchange rate (based on its mapped currency)
For example, for China VIP, the dp_earned need to be converted into HKD currency as the DP amount in profit file.
Updated by Chooi-Mey over 13 years ago
Add requirements:
1) Finance Report
- Filename Format: DP_PROFIT_FINANCE_REFERENCE_MMyyyy.csv
- File Encoding: ISO-8859-1
- Comma Delimited
- Header: MEMBER ID,COUNTRY CODE,MAPPED COUNTRY CODE,DP EARNED (USD),DP EARNED,EXCHANGE RATE
- Details: Example
VP123456,156,344,2.00(USD),15.60(HKD),7.80
VP123457,826,826,-1.00(USD),-0.62(GBP),0.62
2) Exception Report
- Filename Format: DP_PROFIT_EXCEPTION_MMyyyy.csv
- File Encoding: ISO-8859-1
- Comma Delimited
- Header: MEMBER ID,DP EARNED (USD),REASON
- Details: Example
VP123456,2.00,EXCHANGE RATE OF 344 NOT FOUND
VP123457,-1.00,EXCHANGE RATE OF 826 NOT FOUND
- Exception is catch when hashExchangeRateValue is null (due to fail get the match record from MEND_CURRCONV by country code), and skipped the record to be written into profit file.
Updated by chin-yeh over 13 years ago
- Refer to the format2Decimal method in my.com.eCosway.util.MyUtil class in usa-mall repository about how to round the monetary value such as DP amount. This is the common method we use to round the value to 2 decimal places.
Updated by chin-yeh over 13 years ago
The file encoding of the profit batch file will be UTF-8.
FYI, the file encoding for all of the VP/DP batch files will be UTF-8 as well.
Updated by Chee-Hoong over 13 years ago
format dp earned to 2 decimal is after currency conversion? for calculation purpose?
Updated by chin-yeh over 13 years ago
Use the util to format all monetary value to 2 decimal places after calculation
Updated by Chee-Hoong over 13 years ago
wat is the formula or how to calculate the dp that need to convert 2 decimal?
Updated by chin-yeh over 13 years ago
I've imported the live VIP_STAT & mend_currconv tables into development STORE db. Can you generate a copy and pass it to Ting-Ting for verification asap?
As per discussion, this batch program will be deployed to production by tomorrow morning. After deployed, we will generate the batch file and pass it to Benny for final verification.
Updated by Chee-Hoong over 13 years ago
i just gen the file and send to ting ting?
Updated by chin-yeh over 13 years ago
As discussed, do provide the profit batch file, which generated in live environment, to Ting-Ting & Benny for final verification.
After that, try to fix all of the bug(s) you have encountered by today.
Updated by Chee-Hoong over 13 years ago
production generated file sent to ting ting and benny.
bug fixed
Updated by Chooi-Mey over 13 years ago
Changes:
1) Add in USD_DP_AMT field at last column (position 531-543), with length 13N (13,2). Pass the DP amount in USD currency (before convert) into the field.
2) For INV_NO field (position 140-159), put in value "BONUSyyyyMM" to show the profit month remarks. For example: BONUS201107.
This changes will only take effect for next month profit run. The updated batch file format will be uploaded later.
Updated by Chee-Hoong over 13 years ago
INV_NO field refers to current date time?
Updated by Chooi-Mey over 13 years ago
INV_NO field refer to profit month, for example, if you are generating for May 2011 profit, then it will be BONUS201105. Not the generated date time. Same as the filename MMyyyy logic.
Updated by Chee-Hoong over 13 years ago
finding test data to enable verification
Updated by Chooi-Mey over 13 years ago
- File DP_PROFIT_FINANCE_REFERENCE_072011_Sample.xls DP_PROFIT_FINANCE_REFERENCE_072011_Sample.xls added
Changes:
1) Generate the Finance Reference Report into excel(xls) format, with VIP records group by country as tab(sheet) and show the total amount (USD) and total amount (local currency) as footer. Refer to attached sample format (DP_PROFIT_FINANCE_REFERENCE_072011_Sample.xls).
2) Perform load test on it, to evaluate the impact on memory usage.
Updated by chin-yeh over 13 years ago
Use the Apache POI to generate the excel file.
Regarding to the load test, I would suggest you to capture the result as:- memory usage on writing 1 million records to excel file
- memory usage on writing 100,000 records to excel file
- memory usage on writing 5 millions records (5 mapped country code in total, each contains 1 million) to excel file
- memory usage on writing 500,000 records (5 mapped country code in total, each contains 100,000) to excel file
Also, do update the Due date when this changes are ready to be deployed to production environment for final testing.
Updated by Chee-Hoong over 13 years ago
is the excel API (APACHE POI) already exist in our eclipse or need to download?
Updated by chin-yeh over 13 years ago
get the latest stable version from the official website
Updated by chin-yeh over 13 years ago
- prior office 2007 - 65,536 rows by 256 columns
- office 2007 or above - 1,048,576 rows by 16,384 columns
Click here to see the more details of the excel specification.
Because of this, your program must be able to split out the record to different sheets when reach the limit. However, the size of the workbook is limited by the available memory resource on the client machine.
At the moment, we will use xls format to have better compatibility.
Updated by chin-yeh over 13 years ago
you could use this code snippet to capture the memory usage:
long used = Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory();
Updated by Chee-Hoong over 13 years ago
is the Runtime under java.lang? if is i try it now
Updated by chin-yeh over 13 years ago
Just a reminder. The total amount in the sheet's footer is referring to total amount (USD & local currency) on the particular sheet, not mapped country code.
Besides that, do update the Due Date.
Updated by Chee-Hoong over 13 years ago
load test still in progress
latest code commited to CVS
Updated by chin-yeh over 13 years ago
- Due date changed from 08/12/2011 to 09/15/2011
The load test result and the changes should be ready by this Thursday as we will calculate profit next week.
Updated by Chee-Hoong over 13 years ago
• memory usage on writing 100,000 records to excel file - 144212200 bytes (144MB)
• memory usage on writing 500,000 records (5 mapped country code in total, each contains 100,000) to excel file - 759753320 bytes (759MB)
• memory usage on writing 1 million records to excel file - 1385801560 bytes (1.39GB)
Updated by Chee-Hoong over 13 years ago
the following is settings for JVM arg
100 k and 500 k use default (no need to set)
for 1m record with arg -Xmx1536m
Updated by chin-yeh over 13 years ago
Attach the latest shell scripts and the required libraries here
Updated by Chee-Hoong over 13 years ago
- File nicholasClass.sh nicholasClass.sh added
here is the new script file to run the program.
and only 1 new library added
Updated by Chee-Hoong over 13 years ago
- File poi-3.7-20101029.jar poi-3.7-20101029.jar added
Updated by chin-yeh over 13 years ago
This batch program has already been scheduled to run daily since yesterday.
Since this is a new program, you are required to enter the details of the program into the runHK.txt sheet of DailyBatchNew.xls, the file are located at:
<<src shared folder>>/schedule batch
Updated by Chee-Hoong over 13 years ago
direct profit details added into into the runHK.txt sheet of DailyBatchNew.xls
Updated by Chooi-Mey over 13 years ago
Pls provide guideline to Chee Wei on how to manual run the DP Profit program.
Updated by Chee-Hoong over 13 years ago
i've added in the param needed in the force run column
Updated by chin-yeh over 13 years ago
Please fill up the related info. for Date Generated and Data Date columns in the excel file.
Updated by Chee-Hoong over 13 years ago
Date Generated and Date Date is refer to date of?
Updated by Chooi-Mey about 13 years ago
[Changes]
1) Change the Finance Reference Report Excel sheet(tab) from country code to country name (with max length 20 chars).
2) Add new param argument to allow pre-run without checking on the publish flag.
Updated by Chooi-Mey about 13 years ago
Both items (country name & force run) tested.
Updated by chin-yeh about 13 years ago
do update the DailyBatchNew.xls if there's any changes made.
Updated by Chooi-Mey about 13 years ago
[Changes]
- Remove the 2 columns: Country Code and Mapped Country Code, from displaying on sheet.
Note: Comment out the code with remarks, for future enable back if need to.
Updated by Chee-Hoong about 13 years ago
changes deployed to live and code commited into CVS
Updated by Chooi-Mey about 13 years ago
- Due date changed from 09/15/2011 to 11/04/2011
[Changes/Bug]
1) In DP_Profit_Finance_Reference, show the value of the amount, include the minus sign, -
if < 0.
2) Total is wrongly calculated due to -
value of the amount is not being take into calculation. Total is sum of amount (with 2 decimal point).
Updated by Chooi-Mey about 13 years ago
Chooi-Mey wrote:
[Changes/Bug]
- In DP_Profit_Finance_Reference, show the value of the amount, include the negative sign if < 0.
- Total is wrongly calculated due to negative value of the amount is not being take into calculation. Total is sum of amount (with 2 decimal point).
Updated by Chee-Hoong about 13 years ago
calculation corrected and deployed to production env
Updated by chin-yeh almost 13 years ago
- Status changed from Feedback to Closed
- % Done changed from 0 to 100
- Resolution set to Fixed
fixed and deployed