Option Compare Database
Option Explicit
    
' Constants used for file extensions
Const MSG_FILEXTCHR = "."
Const MSG_FILTRDEXT = ".TDE"
Const MSG_FILSHTEXT = ".NE"

Function ImportDailyTradeFiles()
    Dim bWarningState As Boolean
    On Error GoTo ErrHandler
    ' This is the main routine for getting the Daily Trade Files
    Dim sFileRoot As String
    Dim sDailyTrade As String
    Dim sDailySName As String
    Dim bPrevious As Boolean
   
    ' First, make sure that global variables are initialized, since Access will reset them
    ' to zero if an error occurs anywhere
    InitializeGlobals
    
    ' Ask the user for the file name
    sFileRoot = GetImportFileName("Daily Trade Files (*.tde)|*.tde")
     
    ' Separate file name from extension, get root name
    sFileRoot = StrTokStr(sFileRoot, MSG_FILEXTCHR)

    ' Cancel button or ill-formed file name?
    If (Len(sFileRoot) = 0) Then Exit Function
 
    ' Has this set of files been imported already?
    bPrevious = CheckQueryRecords("SELECT * FROM tbMasterReportFile WHERE arFileDate = #" & CovertDate(sFileRoot) & "#")
    If (bPrevious) Then
        Dim iResponse
        iResponse = MsgBox("You have already imported " & sFileRoot & "." & CRLF() & "This will remove any previous overrides for this date." & CRLF() & "Do you want to continue?", vbOKCancel)
        If (iResponse = vbCancel) Then Exit Function
    End If
   
    ' Close any dependant forms
    CloseDependantForms
    
    ' Force extension convention
    sDailyTrade = sFileRoot & MSG_FILTRDEXT
    
    ' Make sure both files are present
    sDailySName = GetShortNameFile(sFileRoot)
    If (Len(sDailySName) = 0) Then Exit Function
        
    ' Import Daily Trade File
    TableDelete ("itbDailyTrade-TDE")
    DoCmd.TransferText acImportFixed, "DailyTradeImport-TDE", "itbDailyTrade-TDE", sDailyTrade, False
    
    ' Import Daily Short Name List
    TableDelete ("itbDailyShortName-NE")
    DoCmd.TransferText acImportFixed, "DailyShortNameImport-NE", "itbDailyShortName-NE", sDailySName, False
    
    ' Do not warn user about upcoming deletes, appends, etc
    bWarningState = ActionWarning(False)
   
    ' Update Master Short Name List
    DoCmd.OpenQuery ("qryShortNameUpdateAppend")
    DoCmd.OpenQuery ("qryShortNameUpdateMismatchAsk")
   
    ' Delete any old override records
    RunQuery ("DELETE * FROM tbAttributionOverrideType;")
    RunQuery ("DELETE * FROM tbAttributionOverrideAnalyst;")
    
    ' Create today's master tables; save previous copies if first time through
    If (bPrevious) Then
        TableDelete ("tbMasterTradeTable")
        TableDelete ("tbAttributionOutputFile")
    Else
        TableBackupDelete ("tbMasterTradeTable")
        TableBackupDelete ("tbAttributionOutputFile")
    End If
    DoCmd.OpenQuery ("qryMakeMasterTradeTable")
    DoCmd.OpenQuery ("qryMakeAttributionOutputFile")
    
    ' Indicate current table import name (and indirectly, date)
    SystemSettingSet "PreviousImport", sFileRoot
    GetImportDate True

    ' Delete any records for the import date, append new records
    UpdateMasterReportFile
    
    ' Turn warnings back on
    ActionWarning bWarningState

    ' Operation successfull
    MsgBox "Daily Trade Files for " & GetImportDate() & " were successfully imported."
    Exit Function
    
ErrHandler:
    ' Unexpected error
    ErrorReport "during file import."
    ActionWarning bWarningState
    Exit Function
End Function

Function TableDelete(sTableName)
    ' Ignore error if previous non-existant
    On Error Resume Next
    DoCmd.DeleteObject acTable, sTableName
End Function

Function TableBackupDelete(sTableName)
    ' Backup old table, save latest. Ignore error if previous non-existent
    On Error Resume Next
    TableDelete ("x1-" & sTableName)
    DoCmd.Rename "x1-" & sTableName, acTable, sTableName
End Function

Private Function CovertDate(sFileName) As Date
    ' This function converts the Daily Trade file name into a Y2000 compliant date.
    ' In the future, the file name convention may change, at which time this function will change.
    ' Note: Tested this module with sFileName = "071000" for Y2000
    ' MS Documentation: The Short Date setting assumes that dates between 1/1/00 and 12/31/29 are twenty-first century dates (that is, the years are assumed to be 2000 to 2029). Dates between 1/1/30 and 12/31/99 are assumed to be twentieth century dates (that is, the years are assumed to be 1930 to 1999).
    Dim sDate
    On Error GoTo ErrHandler
    sDate = FilRemPth(sFileName)
    CovertDate = CDate(Format(sDate, "@@/@@/@@"))
    Exit Function

ErrHandler:
    MsgBox ("An unexpected error occurred during date conversion.")
    Exit Function
End Function

Function GetImportDate(Optional bForceUpdate As Boolean = False) As Date
    ' Convert the file name to a Y2000 compliant date
    ' Set a global variable to speed up date assignment in the table generation
    ' Insure that the date is re-initialized if Access reset variables
    Static dsImportFileDate As Date
    If (bForceUpdate Or (dsImportFileDate = CDate(0))) Then
        dsImportFileDate = CovertDate(SystemSettingGet("PreviousImport"))
    End If
    GetImportDate = dsImportFileDate
End Function

Function GetSettlementDate(sSettlementYear, dtSettlementMonth, dtSettlementDay) As Date
    On Error Resume Next
    GetSettlementDate = CDate(dtSettlementMonth & "/" & dtSettlementDay & "/" & sSettlementYear)
End Function

Private Function GetImportFileName(sFilter) As String
    On Error GoTo ErrHandler
   ' Ask the user for the file name
    Forms!Switchboard!filImportList.CancelError = True
    ' Set Initial File Name
    Forms!Switchboard!filImportList.FileName = SystemSettingGet("PreviousImport")
    ' Set flags
    Forms!Switchboard!filImportList.Flags = cdlOFNFileMustExist
    ' Set filters
    Forms!Switchboard!filImportList.Filter = "All Files (*.*)|*.*|" & sFilter
    ' Specify default filter
    Forms!Switchboard!filImportList.FilterIndex = 2
    ' Display the Open dialog box
    Forms!Switchboard!filImportList.ShowOpen
    ' Return name of selected file
    GetImportFileName = Forms!Switchboard!filImportList.FileName
    Exit Function

ErrHandler:
    GetImportFileName = ""
    
    ' Cancel button?
    If (Err.Number = cdlCancel) Then Exit Function
     
    ' Invalid file name set earlier
    ' Force old file name to null to prevent initial invalid filename error
    If (Err.Number = cdlInvalidFileName) Then
        SystemSettingSet "PreviousImport", ""
        Exit Function
    End If
     
    ' Unexpected error
    ErrorReport "when trying to open a file."
    Exit Function
End Function

Function GetShortNameFile(sFileRoot As String)
    Dim sFileName As String
    
    'Look in current directory; Force extension convention
    sFileName = sFileRoot & MSG_FILSHTEXT
    If SafeFileExist(sFileName) Then
        GetShortNameFile = sFileName
        Exit Function
    End If
    
    ' ajm temp
    sFileName = Left(sFileRoot, Len(sFileRoot) - 6) & "..\nameadd\" & FilRemPth(sFileRoot) & MSG_FILSHTEXT
    If SafeFileExist(sFileName) Then
        GetShortNameFile = sFileName
        Exit Function
    End If

    ' Ask user for explicit location
    GetShortNameFile = GetImportFileName("Daily Short Name Files (*.ne)|*.ne")

End Function

Function ShortNameUpdateCheck(sAccountNumber, sMasterShortName, sDailyShortName) As Boolean
    Dim iResponse As Integer
    ' Note that the queries should eliminate mismatches before reaching this
    ' point, but, alas, they don't. So this checks for a mismatch explicitly.
    If (sDailyShortName = sMasterShortName) Then Exit Function
    
    ' Master and update mismatch - do we really want to change?
    iResponse = MsgBox("The Daily Short Name List contains a new name for account #" & sAccountNumber & "." & CRLF() & "Do you want to update the Master Short Name '" & sMasterShortName & "' to the new name '" & sDailyShortName & "' ?", vbYesNo)
    ShortNameUpdateCheck = (vbYes = iResponse)
End Function

Function PurgeOldTradingRecords()
    Dim iResponse As Integer
    Dim dOldDate As Date
    
    ' Calculate "old" date
    dOldDate = DateAdd("m", -18, Date)
    iResponse = MsgBox("Do you really want to delete records older than " & dOldDate & "?", vbYesNo)
    
    ' Delete old trading records
    If (vbYes = iResponse) Then
        RunQuery ("DELETE * FROM tbMasterReportFile WHERE arFileDate < #" & dOldDate & "#;")
    End If
End Function

Sub CloseDependantForms()
    ' Force dependant forms closed
    DoCmd.Close acForm, "frmOverrideByInstitution"
    DoCmd.Close acForm, "frmOverrideByTradeID"
End Sub