1   SUB BAS_SQLM_ZILL_IMPORT_SUB
    OPTION TYPE=EXPLICIT
    !
    !	BAS_SQLM_ZILL_IMPORT_SUB.BAS
    !
    !	Program to import a flat text file
    !   of comma seperated values in the form of:
    !
    !   draw_dt, no_1, no_2, no_3, no_4, no_5, mega_no
    !
    !   creating a newly populated drawing_data table.
    !
    %INCLUDE "LIB$ROUTINES" %FROM %LIBRARY
    %INCLUDE "$LIBDTDEF" %FROM %LIBRARY

    !;;;;;;;;;;
    !	Constants
    !;;;;;;;;;;
    DECLARE BYTE CONSTANT   elm_count% = 52%
    DECLARE BYTE CONSTANT   input_chan% = 5%

    !;;;;;;;;;;
    !	Maps
    !;;;;;;;;;;
    MAP (LOCAL_MAP) STRING  TRANSLATED_NAME$ = 255%

    MAP (DT_MAP)     BASIC$QUADWORD  DRAW_DT
    MAP (DT_MAP)     STRING BINARY_DATE_STR=8%
    !;;;;;;;;;;
    !	Local Variables
    !;;;;;;;;;;
    DECLARE WORD    W_X%
    DECLARE LONG    L_X%, L_ERR%, L_REC_COUNT%, L_SLASH_SUB%, L_DEFAULTS%, &
                    L_DATE_CONTEXT%, L_COMPONENT%, SQLCODE%, L_Z%

    DECLARE STRING  WORK_STR$, NUM_TXT$, LINE_IN_STR$, &
                    INPUT_FILE_NAME_STR$, LOG_TXT$, &
                    MM_STR$, DD_STR$, YYYY_STR$

    DECLARE BYTE            NO_1, NO_2, NO_3, NO_4, NO_5, MEGA_NO
    DECLARE WORD            TIM_BUFF(8)

    !;;;;;;;;;;
    !	Main Logic
    !;;;;;;;;;;
100 L_ERR% = 0%
    L_REC_COUNT% = 0%

    GOSUB A910_OPEN_FILES

    TIM_BUFF(0%) = 1970%
    TIM_BUFF(1%) = 1%
    TIM_BUFF(2%) = 2%

    L_DATE_CONTEXT% = 0%
    L_COMPONENT% = LIB$K_INPUT_FORMAT
    L_DEFAULTS% = 127%

    L_X% = LIB$INIT_DATE_TIME_CONTEXT( L_DATE_CONTEXT%, L_COMPONENT%, '|!Y4!MN0!D0 !H04!M0!S0!C2|')

    CALL DELETE_ALL_DRAW_RECS( SQLCODE%)
    CALL COMMIT_MEGA( SQLCODE%)

    WHILE L_ERR% = 0%
        WHEN ERROR IN
            LINPUT #input_chan%, LINE_IN_STR$
        USE
            L_ERR% = ERR
        END WHEN

        ITERATE IF L_ERR% <> 0%

        !
        !  Extract the date which is in mm/dd/yyyy format
        !  and convert it to YYYYMMDD format with leading zeros.
        !
        L_X% = INSTR( 1%, LINE_IN_STR$, ",")
        WORK_STR$ = LEFT$( LINE_IN_STR$, L_X% - 1%)
        L_SLASH_SUB% = INSTR( 1%, WORK_STR$, "/")
        MM_STR$ = LEFT$( WORK_STR$, L_SLASH_SUB% -1%)
        MM_STR$ = EDIT$( MM_STR$, 2%)

        WORK_STR$ = RIGHT$( WORK_STR$, L_SLASH_SUB% + 1%)
        L_SLASH_SUB% = INSTR( 1%, WORK_STR$, "/")
        DD_STR$ = LEFT$( WORK_STR$, L_SLASH_SUB% - 1%)
        DD_STR$ = EDIT$( DD_STR$, 2%)

        YYYY_STR$ = RIGHT$( WORK_STR$, L_SLASH_SUB% + 1%)
        WORK_STR$ = YYYY_STR$ + FORMAT$( VAL%(MM_STR$), "<0>#") &
                            + FORMAT$( VAL%(DD_STR$), "<0>#")

        L_Z% = LIB$CONVERT_DATE_STRING( WORK_STR$, DRAW_DT, L_DATE_CONTEXT%,L_DEFAULTS%, TIM_BUFF())

        !
        !  Extract the drawing numbers and convert them to integers
        !
        WORK_STR$ = RIGHT$( LINE_IN_STR$, L_X% + 1%)
        L_X% = INSTR( 1%, WORK_STR$, ",")
        NUM_TXT$ = LEFT$( WORK_STR$, L_X% - 1%)
        NUM_TXT$ = EDIT$( NUM_TXT$, 2%)
        NO_1 = VAL%( NUM_TXT$)

        WORK_STR$ = RIGHT$( WORK_STR$, L_X% + 1%)
        L_X% = INSTR( 1%, WORK_STR$, ",")
        NUM_TXT$ = LEFT$( WORK_STR$, L_X% - 1%)
        NUM_TXT$ = EDIT$( NUM_TXT$, 2%)
        NO_2 = VAL%( NUM_TXT$)

        WORK_STR$ = RIGHT$( WORK_STR$, L_X% + 1%)
        L_X% = INSTR( 1%, WORK_STR$, ",")
        NUM_TXT$ = LEFT$( WORK_STR$, L_X% - 1%)
        NUM_TXT$ = EDIT$( NUM_TXT$, 2%)
        NO_3 = VAL%( NUM_TXT$)

        WORK_STR$ = RIGHT$( WORK_STR$, L_X% + 1%)
        L_X% = INSTR( 1%, WORK_STR$, ",")
        NUM_TXT$ = LEFT$( WORK_STR$, L_X% - 1%)
        NUM_TXT$ = EDIT$( NUM_TXT$, 2%)
        NO_4 = VAL%( NUM_TXT$)

        WORK_STR$ = RIGHT$( WORK_STR$, L_X% + 1%)
        L_X% = INSTR( 1%, WORK_STR$, ",")
        NUM_TXT$ = LEFT$( WORK_STR$, L_X% - 1%)
        NUM_TXT$ = EDIT$( NUM_TXT$, 2%)
        NO_5 = VAL%( NUM_TXT$)

        WORK_STR$ = RIGHT$( WORK_STR$, L_X% + 1%)
        WORK_STR$ = EDIT$( WORK_STR$, 2%)
        MEGA_NO = VAL%( WORK_STR$)

        GOSUB A950_WRITE_RECORD

        IF MOD( L_REC_COUNT%, 100%) = 0%
        THEN
            PRINT L_REC_COUNT%;" Records written"
            CALL COMMIT_MEGA( SQLCODE%)
        END IF

    NEXT

    PRINT L_REC_COUNT%;" Records written"

    GOTO PROGRAM_EXIT

910 !;;;;;;;;;;
    !	Subroutine to open indexed files
    !;;;;;;;;;;
 A910_OPEN_FILES:
        !
        !   Must get input file name from user
        !
        INPUT_FILE_NAME_STR$ = " "
        WHILE INPUT_FILE_NAME_STR$ <= " "
        
            INPUT "Name of Input File ";INPUT_FILE_NAME_STR$
        NEXT


        WHEN ERROR IN
            L_ERR% = 0%
            OPEN INPUT_FILE_NAME_STR$ FOR INPUT AS FILE #input_chan%
        USE
            L_ERR% = ERR
            PRINT "Unable to open input file ";INPUT_FILE_NAME_STR$
            PRINT "Error: ";L_ERR%;" ";ERT$( L_ERR%)
        END WHEN

    RETURN  

950 !;;;;;;;;;;
    !
    !;;;;;;;;;;
 A950_WRITE_RECORD:
955     CALL INSERT_DRAW_REC( SQLCODE%, DRAW_DT, NO_1, NO_2, NO_3, NO_4, NO_5, MEGA_NO)

        IF (SQLCODE% <> 0%) 
        THEN
            PRINT "Result of inserting row "; SQLCODE%
        ELSE
            L_REC_COUNT% = L_REC_COUNT% + 1%
        END IF

    RETURN

32767	!  End of module
 PROGRAM_EXIT:
        WHEN ERROR IN
            CLOSE #input_chan%
        USE
            ! ignore error on close
        END WHEN

        L_Z% = LIB$FREE_DATE_TIME_CONTEXT( L_DATE_CONTEXT%)
	END SUB