Most drawn lottery numbers ABAP program for SAP system
This abap report can be used to upload all previous Uk lottery results including draw number, date draw, ball set used, machine used,
number of winners, jackpot into a SAP database. The structure of this file is based on the lottery data available from
Lottery numbers and has the following structure:
Draw number, Day, DD, MMM, YYYY, Num1, Num2, Num3, Num4, Num5, Num6, Bonus, Jackpot, Wins, Machine, Ball set
Here is lottery numbers file containing all numbers upto draw 1538 made on the 23/01/2013
Once the data has successfully been uploaded the report can then be used to report on this data and display most drawn numbers, numbers drawn
together, how often a set of 6 numbers would have won a prize and the different prizes it would have won.
See here for further information about this report including example screen shots
Creating this report
Step 1 - Creating a Ztable
In-order to create this abap report in your SAP system you first need to create a Ztable to store all the data. This needs to be called
'ZLOTRESULTS' otherwise the below abap code will not work and needs to be created with the following field names and data types.
Field name Data element data type Length Short desc MANDT MANDT CLNT 3 Client DRAWNO ZDRAWNO NUMC 6 Draw number DRAWDATE DATUM DATS 8 Date DRAWDAY ZDRAWDAY CHAR 3 Draw day MACHINE ZMACHINE CHAR 30 Machine BALLSET ZBALLSET CHAR 2 ball set NUM1 NUM2 NUMC 2 2-Digit Numeric Value NUM2 NUM2 NUMC 2 2-Digit Numeric Value NUM3 NUM2 NUMC 2 2-Digit Numeric Value NUM4 NUM2 NUMC 2 2-Digit Numeric Value NUM5 NUM2 NUMC 2 2-Digit Numeric Value NUM6 NUM2 NUMC 2 2-Digit Numeric Value BNUM NUM2 NUMC 2 2-Digit Numeric Value JACKPOT ZJACKPOT NUMC 10 Jackpot WINS ZWINS NUMC 3 No. Wins
Step 2 - Creating the ABAP report
Next you need to create a new z report ZLOTTO(can be any name you like) using the following code:
*............................................................... *: Report: ZLOTTO : *: : *: Authur: SAP Development : *: : *: Description: Displays report of lottery number statistics : *: (i.e. most drawn numbers, numbers drawn : *: together etc etc) : *: : *:.............................................................: REPORT ZLOTTO. TABLES: zlotresults. type-pools: slis. "ALV Declarations SELECTION-SCREEN begin of block block1 with frame title text-001. PARAMETERS: p_infile LIKE rlgrap-filename DEFAULT 'C:/', "upload data (overwrites existing data) p_upload TYPE c radiobutton group rad1, "numbers drawn the most p_dmost type c radiobutton group rad1, "check below nummbers against all draws p_tnums type c RADIOBUTTON GROUP rad1. *checks best results for the following 6 numbers * i.e. date, matched numbers, machine, ballset PARAMETERS: p_num1 type zlotresults-num1. PARAMETERS: p_num2 type zlotresults-num1. PARAMETERS: p_num3 type zlotresults-num1. PARAMETERS: p_num4 type zlotresults-num1. PARAMETERS: p_num5 type zlotresults-num1. PARAMETERS: p_num6 type zlotresults-num1. SELECTION-SCREEN end of block block1. SELECTION-SCREEN begin of block block2 with frame title text-002. *only draws on this date SELECT-OPTIONS: so_date for zlotresults-drawdate. *only draws which use these machines SELECT-OPTIONS: so_mach for zlotresults-machine. *only draws using these ball sets SELECT-OPTIONS: so_bset for zlotresults-ballset. *only draws where these balls where drawn SELECT-OPTIONS: so_BLINK for zlotresults-NUM1. *day of draw (SAT or WED) SELECT-OPTIONS: so_day for zlotresults-drawday. SELECTION-SCREEN end of block block2. *DATA: ld_file LIKE rlgrap-filename. DATA: gd_file type string. *Internal tabe to store upload data TYPES: BEGIN OF t_record, DRAWNO type string, DRAWDAY type string, DD type string, MMM type string, YYYY type string, NUM1 type string, NUM2 type string, NUM3 type string, NUM4 type string, NUM5 type string, NUM6 type string, BNUM type string, JACKPOT type string, WINS type string, MACHINE type string, BALLSET type string, END OF t_record. DATA: it_record TYPE STANDARD TABLE OF t_record INITIAL SIZE 0, wa_record TYPE t_record. data: it_results type STANDARD TABLE OF zlotresults, wa_results type zlotresults. TYPES: begin of t_datatab, row type string, end of t_datatab. data: it_datatab type STANDARD TABLE OF t_datatab, wa_datatab like line of it_datatab. TYPES: begin of t_dmost, bnum type i, timesdrawn type i, end of t_dmost. data: it_dmost type STANDARD TABLE OF t_dmost, wa_dmost like LINE OF it_dmost. TYPES: begin of t_tnums, drawdate type datum, machine type string, ballset type string, numcount type i, end of t_tnums. data: it_tnums type STANDARD TABLE OF t_tnums, wa_tnums like LINE OF it_tnums. *ALV data declarations data: fieldcatalog type slis_t_fieldcat_alv with header line, gd_tab_group type slis_t_sp_group_alv, gd_layout type slis_layout_alv, gd_repid like sy-repid. constants: con_tab type c value cl_abap_char_utilities=>HORIZONTAL_TAB. ************************************************************************ *AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_INFILE. AT SELECTION-SCREEN ON VALUE-REQUEST FOR P_infile. CALL FUNCTION 'WS_FILENAME_GET' EXPORTING def_filename = p_infile mask = ',*.txt.' mode = 'O' title = 'Upload File'(078) IMPORTING filename = p_infile EXCEPTIONS inv_winsys = 1 no_batch = 2 selection_cancel = 3 selection_error = 4 OTHERS = 5. ************************************************************************ *START-OF-SELECTION START-OF-SELECTION. select * from zlotresults into CORRESPONDING FIELDS OF TABLE it_results where machine in so_mach and ballset in so_bset AND DRAWDAY in so_day and drawdate in so_date and ( NUM1 IN so_BLINK OR NUM2 IN so_BLINK OR NUM3 IN so_BLINK OR NUM4 IN so_BLINK OR NUM5 IN so_BLINK OR NUM6 IN so_BLINK OR BNUM IN so_BLINK ). case 'X'. when p_upload. perform upload_data. perform add_data_to_database. when p_dmost. perform balls_drawn_most. perform build_fieldcatalog. perform build_layout. perform display_alv_report. when p_tnums. perform test_number_combo. perform build_fieldcatalog_tnums. perform build_layout. perform display_alv_tnums. endcase. ************************************************************************ *END-OF-SELECTION END-OF-SELECTION. *&---------------------------------------------------------------------* *& Form UPLOAD_DATA *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* FORM UPLOAD_DATA . gd_file = p_infile. CALL FUNCTION 'GUI_UPLOAD' EXPORTING filename = gd_file filetype = 'ASC' TABLES data_tab = it_datatab EXCEPTIONS file_open_error = 1 OTHERS = 2. IF sy-subrc EQ 0. LOOP AT it_datatab into wa_datatab. SPLIT wa_datatab-row AT ',' INTO wa_record-DRAWNO wa_record-DRAWDAY wa_record-DD wa_record-MMM wa_record-YYYY wa_record-NUM1 wa_record-NUM2 wa_record-NUM3 wa_record-NUM4 wa_record-NUM5 wa_record-NUM6 wa_record-BNUM wa_record-JACKPOT wa_record-WINS wa_record-MACHINE wa_record-BALLSET. * MOVE-CORRESPONDING wa_uploadtxt TO wa_record. APPEND wa_record TO it_record. ENDLOOP. ENDIF. ENDFORM. " UPLOAD_DATA *&---------------------------------------------------------------------* *& Form ADD_DATA_TO_DATABASE *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* FORM ADD_DATA_TO_DATABASE . *!! Text data is now contained within the internal table IT_RECORD * Display report data for illustration purposes LOOP AT it_record INTO wa_record. TRANSLATE wa_record-drawday to UPPER CASE. TRANSLATE wa_record-MMM to UPPER CASE. TRANSLATE wa_record-MACHINE to UPPER CASE. check wa_record-drawday ne 'DAY' and wa_record-MMM ne 'MMM'. wa_results-drawno = wa_record-DRAWNO. case wa_record-mmm. when 'JAN'. wa_record-mmm = '01'. when 'FEB'. wa_record-mmm = '02'. when 'MAR'. wa_record-mmm = '03'. when 'APR'. wa_record-mmm = '04'. when 'MAY'. wa_record-mmm = '05'. when 'JUN'. wa_record-mmm = '06'. when 'JUL'. wa_record-mmm = '07'. when 'AUG'. wa_record-mmm = '08'. when 'SEP'. wa_record-mmm = '09'. when 'OCT'. wa_record-mmm = '10'. when 'NOV'. wa_record-mmm = '11'. when 'DEC'. wa_record-mmm = '12'. endcase. CONCATENATE wa_record-YYYY wa_record-MMM wa_record-DD into wa_results-drawdate. shift wa_record-drawday LEFT DELETING LEADING space. wa_results-drawday = wa_record-DRAWDAY. shift wa_record-machine LEFT DELETING LEADING space. wa_results-machine = wa_record-MACHINE. shift wa_record-ballset LEFT DELETING LEADING space. wa_results-ballset = wa_record-BALLSET. wa_results-NUM1 = wa_record-NUM1. wa_results-NUM2 = wa_record-NUM2. wa_results-NUM3 = wa_record-NUM3. wa_results-NUM4 = wa_record-NUM4. wa_results-NUM5 = wa_record-NUM5. wa_results-NUM6 = wa_record-NUM6. wa_results-BNUM = wa_record-BNUM. wa_results-jackpot = wa_record-JACKPOT. wa_results-wins = wa_record-WINS. modify zlotresults from wa_results. ENDLOOP. ENDFORM. " ADD_DATA_TO_DATABASE *&---------------------------------------------------------------------* *& Form BALLS_DRAWN_MOST *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* FORM BALLS_DRAWN_MOST . data: ld_count type i. ld_count = 0. do. if ld_count gt 49. exit. endif. ld_count = ld_count + 1. wa_dmost-bnum = ld_count. loop at it_results into wa_results. if wa_results-num1 = wa_dmost-bnum. wa_dmost-timesdrawn = wa_dmost-timesdrawn + 1. elseif wa_results-num2 = wa_dmost-bnum. wa_dmost-timesdrawn = wa_dmost-timesdrawn + 1. elseif wa_results-num3 = wa_dmost-bnum. wa_dmost-timesdrawn = wa_dmost-timesdrawn + 1. elseif wa_results-num4 = wa_dmost-bnum. wa_dmost-timesdrawn = wa_dmost-timesdrawn + 1. elseif wa_results-num5 = wa_dmost-bnum. wa_dmost-timesdrawn = wa_dmost-timesdrawn + 1. elseif wa_results-num6 = wa_dmost-bnum. wa_dmost-timesdrawn = wa_dmost-timesdrawn + 1. elseif wa_results-bnum = wa_dmost-bnum. wa_dmost-timesdrawn = wa_dmost-timesdrawn + 1. endif. endloop. append wa_dmost to it_dmost. clear: wa_dmost. enddo. sort it_dmost by timesdrawn DESCENDING. ENDFORM. " BALLS_DRAWN_MOST *&---------------------------------------------------------------------* *& Form BUILD_FIELDCATALOG *&---------------------------------------------------------------------* * Build Fieldcatalog for ALV Report *----------------------------------------------------------------------* form build_fieldcatalog. fieldcatalog-fieldname = 'BNUM'. fieldcatalog-seltext_m = 'Ball Number'. fieldcatalog-col_pos = 0. fieldcatalog-outputlen = 10. fieldcatalog-emphasize = 'X'. fieldcatalog-key = 'X'. * fieldcatalog-do_sum = 'X'. * fieldcatalog-no_zero = 'X'. append fieldcatalog to fieldcatalog. clear fieldcatalog. fieldcatalog-fieldname = 'TIMESDRAWN'. fieldcatalog-seltext_m = 'Times Drawn'. fieldcatalog-col_pos = 1. append fieldcatalog to fieldcatalog. clear fieldcatalog. endform. " BUILD_FIELDCATALOG *&---------------------------------------------------------------------* *& Form BUILD_LAYOUT *&---------------------------------------------------------------------* * Build layout for ALV grid report *----------------------------------------------------------------------* form build_layout. gd_layout-no_input = 'X'. gd_layout-colwidth_optimize = 'X'. gd_layout-totals_text = 'Totals'(201). * gd_layout-totals_only = 'X'. * gd_layout-f2code = 'DISP'. "Sets fcode for when double * "click(press f2) * gd_layout-zebra = 'X'. * gd_layout-group_change_edit = 'X'. * gd_layout-header_text = 'helllllo'. endform. " BUILD_LAYOUT *&---------------------------------------------------------------------* *& Form DISPLAY_ALV_REPORT *&---------------------------------------------------------------------* * Display report using ALV grid *----------------------------------------------------------------------* form display_alv_report. gd_repid = sy-repid. call function 'REUSE_ALV_GRID_DISPLAY' exporting i_callback_program = gd_repid * i_callback_top_of_page = 'TOP-OF-PAGE' "see FORM * i_callback_user_command = 'USER_COMMAND' * i_grid_title = outtext is_layout = gd_layout it_fieldcat = fieldcatalog[] * it_special_groups = gd_tabgroup * IT_EVENTS = GT_XEVENTS i_save = 'X' * is_variant = z_template tables t_outtab = it_dmost exceptions program_error = 1 others = 2. if sy-subrc <> 0. * MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO * WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4. endif. endform. " DISPLAY_ALV_REPORT *&---------------------------------------------------------------------* *& Form TEST_NUMBER_COMBO *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * --> p1 text * <-- p2 text *----------------------------------------------------------------------* FORM TEST_NUMBER_COMBO . loop at it_results into wa_results. wa_tnums-drawdate = wa_results-drawdate. wa_tnums-machine = wa_results-machine. wa_tnums-ballset = wa_results-ballset. perform check_number using p_num1 wa_results changing wa_tnums-numcount. perform check_number using p_num2 wa_results changing wa_tnums-numcount. perform check_number using p_num3 wa_results changing wa_tnums-numcount. perform check_number using p_num4 wa_results changing wa_tnums-numcount. perform check_number using p_num5 wa_results changing wa_tnums-numcount. perform check_number using p_num6 wa_results changing wa_tnums-numcount. APPEND wa_tnums to it_tnums. clear: wa_tnums. endloop. SORT it_tnums BY numcount DESCENDING. ENDFORM. " TEST_NUMBER_COMBO *&---------------------------------------------------------------------* *& Form CHECK_NUMBER *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * -->P_P_NUM1 text * -->P_WA_RESULTS text * <--P_WA_TNUMS_NUMCOUNT text *----------------------------------------------------------------------* FORM CHECK_NUMBER USING P_NUM P_RESULTS structure zlotresults CHANGING P_NUMCOUNT. if p_num eq p_results-num1 or p_num eq p_results-num2 or p_num eq p_results-num3 or p_num eq p_results-num4 or p_num eq p_results-num5 or p_num eq p_results-num6 or p_num eq p_results-bnum. p_numcount = p_numcount + 1. endif. ENDFORM. " CHECK_NUMBER *&---------------------------------------------------------------------* *& Form BUILD_FIELDCATALOG_TNUMS *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * --> p1 text * <-- p2 text *----------------------------------------------------------------------* FORM BUILD_FIELDCATALOG_TNUMS . fieldcatalog-fieldname = 'DRAWDATE'. fieldcatalog-seltext_m = 'Drawdate'. fieldcatalog-col_pos = 0. fieldcatalog-outputlen = 10. fieldcatalog-emphasize = 'X'. fieldcatalog-key = 'X'. * fieldcatalog-do_sum = 'X'. * fieldcatalog-no_zero = 'X'. append fieldcatalog to fieldcatalog. clear fieldcatalog. fieldcatalog-fieldname = 'MACHINE'. fieldcatalog-seltext_m = 'Machine'. fieldcatalog-col_pos = 1. append fieldcatalog to fieldcatalog. clear fieldcatalog. fieldcatalog-fieldname = 'BALLSET'. fieldcatalog-seltext_m = 'Ball Set'. fieldcatalog-col_pos = 1. append fieldcatalog to fieldcatalog. clear fieldcatalog. fieldcatalog-fieldname = 'NUMCOUNT'. fieldcatalog-seltext_m = 'Mached Numbers'. fieldcatalog-col_pos = 1. append fieldcatalog to fieldcatalog. clear fieldcatalog. ENDFORM. " BUILD_FIELDCATALOG_TNUMS *&---------------------------------------------------------------------* *& Form DISPLAY_ALV_TNUMS *&---------------------------------------------------------------------* * text *----------------------------------------------------------------------* * --> p1 text * <-- p2 text *----------------------------------------------------------------------* FORM DISPLAY_ALV_TNUMS . gd_repid = sy-repid. call function 'REUSE_ALV_GRID_DISPLAY' exporting i_callback_program = gd_repid * i_callback_top_of_page = 'TOP-OF-PAGE' "see FORM * i_callback_user_command = 'USER_COMMAND' * i_grid_title = outtext is_layout = gd_layout it_fieldcat = fieldcatalog[] * it_special_groups = gd_tabgroup * IT_EVENTS = GT_XEVENTS i_save = 'X' * is_variant = z_template tables t_outtab = it_tnums exceptions program_error = 1 others = 2. if sy-subrc <> 0. * MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO * WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4. endif. ENDFORM. " DISPLAY_ALV_TNUMS
Return to bespoke ABAP programs
Related Articles
Commented CALL FUNCTION Pattern
Improved ABAP Pretty Print program to beautify your code
Bespoke ABAP program (download employee data)
ABAP lottery numbers report for SAP system - User guide
Bespoke ABAP program (select staff by postcode)
Bespoke ABAP program (select staff by postcode)
Execute SQL statements on the fly in Open or Native SQL