1 SUB BAS_SQLM_CREATE_STATS_SUB OPTION TYPE=EXPLICIT ! BAS_SQLM_CREATE_STATS_SUB.BAS ! ! Program to process the drawing data table ! and generate 2 statistics tables ! ! %INCLUDE "LIB$ROUTINES" %FROM %LIBRARY %INCLUDE "SYS$LIBRARY:SQL_LITERALS.BAS" !;;;;;;;;;; ! Constants !;;;;;;;;;; DECLARE BYTE CONSTANT elm_count% = 52% !;;;;;;;;;; ! Maps !;;;;;;;;;; MAP (LOCAL_MAP) STRING TRANSLATED_NAME$ = 255%, & FILE_NAME$ = 255% RECORD Z_REC BYTE ELM_NO LONG HIT_COUNT LONG LAST_DRAW_NO LONG SINCE_LAST LONG CURR_SEQ LONG LONGEST_SEQ GFLOAT PCT_HITS LONG MAX_BTWN GFLOAT AVE_BTWN END RECORD Z_REC !;;;;;;;;;; ! Local Variables !;;;;;;;;;; DIM Z_REC D_STATS( elm_count%) DIM Z_REC M_STATS( elm_count%) DECLARE BYTE B_EOF% DECLARE WORD W_X% DECLARE LONG L_X%, L_ERR%, L_DRAW_NO%, L_MISSED%, L_SUB%, SQLCODE% DECLARE STRING WORK_STR$, LOG_TXT$ DECLARE BASIC$QUADWORD DRAW_DT DECLARE BYTE NO_1, NO_2, NO_3, NO_4, NO_5, MEGA_NO !;;;;;;;;;; ! Main Logic !;;;;;;;;;; L_ERR% = 0% GOSUB A910_OPEN_FILES IF SQLCODE% = 0% THEN GOSUB A930_READ_TO_EOF GOSUB A940_GENERATE_STATS GOSUB A950_WRITE_STATS END IF GOTO PROGRAM_EXIT 910 !;;;;;;;;;; ! Subroutine to open indexed files !;;;;;;;;;; A910_OPEN_FILES: CALL DELETE_ALL_DRAW_STATS( SQLCODE%) CALL COMMIT_MEGA( SQLCODE%) CALL DELETE_ALL_MEGA_STATS( SQLCODE%) CALL COMMIT_MEGA( SQLCODE%) CALL OPEN_ALL_DRAW( SQLCODE%) RETURN 930 !;;;;;;;;;; ! Subroutine to load up the basic stats of hit counts !;;;;;;;;;; A930_READ_TO_EOF: L_DRAW_NO% = 0% B_EOF% = 0% !;;;;; ! Sequentially loop until end of file !;;;;; WHILE B_EOF% = 0% CALL FETCH_ALL_DRAW( SQLCODE%, DRAW_DT, NO_1, NO_2, NO_3, NO_4, NO_5, MEGA_NO) SELECT (SQLCODE%) CASE SQLCODE_SUCCESS L_DRAW_NO% = L_DRAW_NO% + 1% IF MOD(L_DRAW_NO%,100%) = 0% THEN PRINT "Processed ";L_DRAW_NO%;" records" END IF GOSUB B1000_UPDATE_DRAW_STATS GOSUB B1100_UPDATE_MEGA_STATS CASE SQLCODE_EOS B_EOF% = -1% CASE ELSE PRINT "Result of reading cursor ";SQLCODE% B_EOF% = -1% END SELECT NEXT ! end while b_eof loop PRINT "Processed ";L_DRAW_NO%;" records" CALL CLOSE_ALL_DRAW( SQLCODE%) CALL COMMIT_MEGA( SQLCODE%) RETURN 940 !;;;;;;;;;; ! Subroutine to generate over all stats on both arrays !;;;;;;;;;; A940_GENERATE_STATS: FOR L_X%=1% TO elm_count% ! ! Drawing stats ! D_STATS( L_X%)::PCT_HITS = REAL( D_STATS( L_X%)::HIT_COUNT) & / REAL( L_DRAW_NO%) L_MISSED% = L_DRAW_NO% - D_STATS( L_X%)::HIT_COUNT D_STATS( L_X%)::AVE_BTWN = REAL( L_MISSED%) & / REAL( D_STATS( L_X%)::HIT_COUNT) D_STATS( L_X%)::SINCE_LAST = L_DRAW_NO% - D_STATS( L_X%)::LAST_DRAW_NO ! ! Mega Stats ! M_STATS( L_X%)::PCT_HITS = REAL( M_STATS( L_X%)::HIT_COUNT) & / REAL( L_DRAW_NO%) L_MISSED% = L_DRAW_NO% - M_STATS( L_X%)::HIT_COUNT M_STATS( L_X%)::AVE_BTWN = REAL( L_MISSED%) & / REAL( M_STATS( L_X%)::HIT_COUNT) M_STATS( L_X%)::SINCE_LAST = L_DRAW_NO% - M_STATS( L_X%)::LAST_DRAW_NO NEXT L_X% RETURN 950 !;;;;;;;;;; ! Subroutine to write out stats records to files !;;;;;;;;;; A950_WRITE_STATS: FOR L_X%=1% TO elm_count% D_STATS( L_X%)::ELM_NO = L_X% CALL INSERT_DRAW_STATS( SQLCODE%, D_STATS( L_X%)::ELM_NO, & D_STATS( L_X%)::HIT_COUNT, & D_STATS( L_X%)::LAST_DRAW_NO, & D_STATS( L_X%)::SINCE_LAST, & D_STATS( L_X%)::CURR_SEQ, & D_STATS( L_X%)::LONGEST_SEQ, & D_STATS( L_X%)::PCT_HITS, & D_STATS( L_X%)::MAX_BTWN, & D_STATS( L_X%)::AVE_BTWN) M_STATS( L_X%)::ELM_NO = L_X% CALL INSERT_MEGA_STATS( SQLCODE%, M_STATS( L_X%)::ELM_NO, & M_STATS( L_X%)::HIT_COUNT, & M_STATS( L_X%)::LAST_DRAW_NO, & M_STATS( L_X%)::SINCE_LAST, & M_STATS( L_X%)::CURR_SEQ, & M_STATS( L_X%)::LONGEST_SEQ, & M_STATS( L_X%)::PCT_HITS, & M_STATS( L_X%)::MAX_BTWN, & M_STATS( L_X%)::AVE_BTWN) NEXT L_X% CALL COMMIT_MEGA( SQLCODE%) RETURN 1000 !;;;;;;;;;; ! Subroutine to update the drawing stats records !;;;;;;;;;; B1000_UPDATE_DRAW_STATS: L_SUB% = NO_1 GOSUB C2000_DSTAT_UPDATE L_SUB% = NO_2 GOSUB C2000_DSTAT_UPDATE L_SUB% = NO_3 GOSUB C2000_DSTAT_UPDATE L_SUB% = NO_4 GOSUB C2000_DSTAT_UPDATE L_SUB% = NO_5 GOSUB C2000_DSTAT_UPDATE RETURN 1100 !;;;;;;;;;; ! Subroutine to update Mega Number stats ! ! Only one Mega Number per drawing so no need for a subroutine !;;;;;;;;;; B1100_UPDATE_MEGA_STATS: L_SUB% = MEGA_NO L_X% = L_DRAW_NO% - M_STATS( L_SUB%)::LAST_DRAW_NO !;;;;; ! Are we in a sequence of hits or just a random hit? !;;;;; IF L_X% = 1% THEN M_STATS( L_SUB%)::CURR_SEQ = M_STATS( L_SUB%)::CURR_SEQ + 1% IF M_STATS( L_SUB%)::CURR_SEQ > M_STATS( L_SUB%)::LONGEST_SEQ THEN M_STATS( L_SUB%)::LONGEST_SEQ = M_STATS( L_SUB%)::CURR_SEQ END IF ELSE M_STATS( L_SUB%)::CURR_SEQ = 0% IF L_X% > M_STATS( L_SUB%)::MAX_BTWN THEN M_STATS( L_SUB%)::MAX_BTWN = L_X% END IF END IF M_STATS( L_SUB%)::HIT_COUNT = M_STATS( L_SUB%)::HIT_COUNT + 1% M_STATS( L_SUB%)::LAST_DRAW_NO = L_DRAW_NO% M_STATS( L_SUB%)::SINCE_LAST = L_X% RETURN 2000 !;;;;;;;;;; ! Subroutine to update d_stats array from single value !;;;;;;;;;; C2000_DSTAT_UPDATE: L_X% = L_DRAW_NO% - D_STATS( L_SUB%)::LAST_DRAW_NO !;;;;; ! Are we in a sequence of hits or just a random hit? !;;;;; IF L_X% = 1% THEN D_STATS( L_SUB%)::CURR_SEQ = D_STATS( L_SUB%)::CURR_SEQ + 1% IF D_STATS( L_SUB%)::CURR_SEQ > D_STATS( L_SUB%)::LONGEST_SEQ THEN D_STATS( L_SUB%)::LONGEST_SEQ = D_STATS( L_SUB%)::CURR_SEQ END IF ELSE D_STATS( L_SUB%)::CURR_SEQ = 0% IF L_X% > D_STATS( L_SUB%)::MAX_BTWN THEN D_STATS( L_SUB%)::MAX_BTWN = L_X% END IF END IF D_STATS( L_SUB%)::HIT_COUNT = D_STATS( L_SUB%)::HIT_COUNT + 1% D_STATS( L_SUB%)::LAST_DRAW_NO = L_DRAW_NO% D_STATS( L_SUB%)::SINCE_LAST = L_X% RETURN 32767 ! End of module PROGRAM_EXIT: CALL COMMIT_MEGA( SQLCODE%) END SUB