Imports Superbowl_Cronjob.SystemFunctions
Imports Superbowl_Cronjob.ProjectFunctions
Imports Superbowl_Cronjob.db

Imports System.IO
Imports Microsoft.VisualBasic


Public Class Process
   Public Name, ID, Filename, Path, tblStaging, tblMaster, tblDwd, tblDwf As String
   Public doProcess, isValid As Boolean
   Public intRecordsTotal, intRecordsImported, intRecordsIserted, intRecordsUpdated, intRecordsDeleted As Integer
   'Public PK As List(Of KeyValuePair(Of String, String))
   Public PK As String()

   Public srImport As IO.StreamReader
   Public fiImport As IO.FileInfo

   Public Sub New(Name As String, Filename As String, Path As String, Optional strPKs As String = "")
      Me.Name = Name
      Me.ID = Name.Replace(" ", "")
      Me.Filename = Filename
      Me.Path = Path

      Me.tblStaging = "stg" & Me.ID
      Me.tblMaster = "mst" & Me.ID
      Me.tblDwd = "dwd" & Me.ID
#If DEBUG Then
      Me.tblDwf = "dwf" & Me.ID
#Else
      'Me.tblDwf = "tmp" & Me.ID 'switch when live
      Me.tblDwf = "dwf" & Me.ID 'switch when live
#End If

      intRecordsTotal = intRecordsImported = intRecordsIserted = intRecordsUpdated = intRecordsDeleted = 0

      Me.doProcess = True

      'load PK
      'Me.PK = strPKs.Split(",")
      'Me.PK = New List(Of KeyValuePair(Of String, String))
      'If strPKs <> "" Then
      '   For Each key In strPKs.Split(",")
      '      Me.addPK(key)
      '   Next
      'End If

   End Sub

   'Public Sub New(Name As String, Filename As String, Path As String)
   '   Me.New(Name, Filename, Path)
   '   'Me.PK = arrPK

   'End Sub

   'Public Sub addPK(Key As String) 'http://www.dotnetperls.com/keyvaluepair-vbnet
   '   Try
   '      Me.PK.Add(New KeyValuePair(Of String, String)(Key.ToString, ""))
   '   Catch ex As Exception
   '   End Try
   'End Sub


   Public Sub DownloadFile()
      'log "File Download"
      Try

         If File.Exists(Me.Path & Me.Filename) Then
            File.Copy(Me.Path & Me.Filename, My.Settings.dirImports & Me.Filename, True)
            WriteLog("info", Me.Name, "File Download", "File downloaded")
            'Else
            '   WriteLog("warning", Me.Name, "File Download", "File not found! [" & Me.Path & Me.Filename & "]")
         End If
      Catch ex As Exception
         WriteLog("error", Me.Name, "File Download", "Error downloading file! [" & ex.Message & Chr(13) & Chr(10) & ex.StackTrace & "]")
      End Try

   End Sub

   Public Sub CheckLocalFile()
      'log "File Import"
      Try

         If File.Exists(My.Settings.dirImports & Me.Filename) Then
            Dim fiImport As New FileInfo(My.Settings.dirImports & Me.Filename)

            Select Case fiImport.Extension
               Case ".csv"
                  Me.isValid = True
                  Me.fiImport = fiImport
               Case Else
                  Me.isValid = False
                  WriteLog("error", Me.Name, "File Import", "Error reading record! Unsupported file type [" & Me.fiImport.FullName & "]")
                  Exit Sub
            End Select

            Dim srImport As New IO.StreamReader(fiImport.FullName)


            While Not srImport.EndOfStream
               srImport.ReadLine()
               Me.intRecordsTotal += 1
            End While

            srImport.Close()

            'Me.setProgress()

         Else
            Me.isValid = False
         End If
      Catch ex As Exception
         WriteLog("error", Me.Name, "File Import", "Error reading file! [" & ex.Message & Chr(13) & Chr(10) & ex.StackTrace & "]")
      End Try

   End Sub

   Public Sub ClearStagingTable()

      db.doQuery("TRUNCATE TABLE " & Me.tblStaging)

   End Sub

   Public Sub ImportLocalFile()
      'log "File Import"
      'note, this function has to be at the child level, because we need to call the child.sqlInsertStaging()
      'ini
      Dim fileImport As Object
      Dim intRecord As Integer
      Dim sql As String
      Dim blnExit As Boolean = False

      fileImport = New FileIO.TextFieldParser(Me.fiImport.FullName)
      WriteLog("info", Me.Name, "File Import", "Import Start: {0/" & Me.intRecordsTotal & "}")

      'setup csv/text
      Select Case Me.fiImport.Extension
         Case ".csv" 'http://stackoverflow.com/questions/736629/parse-delimited-csv-in-net
            fileImport.TextFieldType = FileIO.FieldType.Delimited
            fileImport.Delimiters = New String() {","}
            fileImport.HasFieldsEnclosedInQuotes = True

         Case Else
            WriteLog("error", Me.Name, "File Import", "Error reading record! Unsupported file type [" & Me.fiImport.FullName & "]")
            Me.isValid = False
            Exit Sub

      End Select

      'read data line by line
      Dim drImport As String()

      'read header:
      drImport = fileImport.ReadFields()
      intRecord += 1

      'read the rest
      While Not fileImport.endOfData And blnExit = False
         Try
            'ini
            drImport = fileImport.ReadFields()
            intRecord += 1

            'process: note that the sqlInsertSTG get overriden in the child entity
            sql = Me.sqlInsertStaging(drImport)
            db.doQuery(sql)

         Catch icEx As InvalidCastException
            WriteLog("warning", Me.Name, "File Import", "Invalid record: [" & icEx.Message & "] {" & intRecord & "/" & Me.intRecordsTotal & "}")
         Catch sqlEx As System.Data.SqlClient.SqlException
            If intRecord > 1 Then 'ln 1 might be the file headings
               WriteLog("warning", Me.Name, "File Import", "Error reading record: [" & sqlEx.Message & " :: " & sql & "] {" & intRecord & "/" & Me.intRecordsTotal & "}")
            End If
         Catch mlEx As FileIO.MalformedLineException
            WriteLog("warning", Me.Name, "File Import", "Error reading record: Malformed data record! [" & mlEx.Message & "] {" & intRecord & "/" & Me.intRecordsTotal & "}")
         Catch ex As Exception
            WriteLog("error", Me.Name, "File Import", "Error reading record! [" & ex.Message & "] {" & intRecord & "/" & Me.intRecordsTotal & "}")

         Finally
            'post
            'Me.Progress()

         End Try

      End While

      Try
         fileImport.Close()
         fileImport = Nothing
      Catch ex As Exception
      End Try

      Dim dr As DataRow = db.getRow("SELECT count(*) as intRecords FROM " & Me.tblStaging)
      WriteLog("info", Me.Name, "File Import", "Import Completed: {" & dr!intRecords & "/" & Me.intRecordsTotal & " records imported}")

   End Sub

   Public Sub ArchiveFile()

      Try
#If DEBUG Then
         File.Copy(My.Settings.dirImports & Me.Filename, My.Settings.dirImported & Me.Filename & "." & Date.Now.ToString("yyyyMMdd_HHmmss") & Me.fiImport.Extension, True)
#Else
         'v1.0.4 - 20151028 - Archiving AM FSR import file - m
         File.Copy(My.Settings.dirImports & Me.Filename, My.Settings.dirImported & Me.Filename & "." & Date.Now.ToString("yyyyMMdd_HHmmss") & Me.fiImport.Extension, True)
#End If
         File.Delete(My.Settings.dirImports & Me.Filename)
      Catch ex As Exception
         WriteLog("warning", Me.Name, "ArchiveFile", "Error archiving file [" & ex.Message & "] ")
      End Try
   End Sub

   'Public Sub setProgress()

   '   If Me.intRecordsTotal > 0 Then
   '      Me.barProcess.Maximum = intRecordsTotal - 10
   '      Me.barProcess.Value = 0
   '   End If
   'End Sub

   'Public Sub Progress()

   '   Try
   '      'If Me.barProcess.Value < Me.barProcess.Maximum Then
   '      Me.barProcess.PerformStep()
   '      If Me.barProcess.Value Mod 10 = 0 Then
   '         Me.barProcess.Refresh()
   '      End If
   '      'End If
   '   Catch ex As Exception
   '      Me.barProcess.Value = Me.barProcess.Maximum
   '   End Try
   'End Sub

   Public Sub LogProcessStart()
      WriteLog("info", Me.Name, "Process Start", "Process Start: " & Date.Now.ToString("u"))
   End Sub

   Public Sub LogProcessCompleted()
      WriteLog("info", Me.Name, "Process Completed", "Process Completed: " & Date.Now.ToString("u"))
   End Sub

   'Public Sub LogDuplicates()
   '   Dim sql As String = ""
   '   Dim strDuplicate As String

   '   Dim ds As DataSet
   '   Dim dr As DataRow

   '   sql = "SELECT " & String.Join(", ", Me.PK) & " FROM " & Me.tblStaging & " GROUP BY " & String.Join(", ", Me.PK) & " HAVING COUNT(ID) > 1 "

   '   ds = db.doQuery(sql)

   '   For Each dr In ds.Tables(0).Rows
   '      strDuplicate = ""

   '      'For Each i In Me.PK
   '      '   strDuplicate &= ""
   '      'Next
   '      strDuplicate = String.Join("', '", dr.ItemArray)
   '      WriteLog("warning", Me.Name, "Duplicate Records", "Duplicate records on PK found: '" & strDuplicate & "' [" & String.Join(", ", Me.PK) & "]")

   '   Next


   'End Sub

   Public Sub SetDeletes(strDateSelector As String, blnPartialImport As Boolean)
      Dim sql As String = ""

      Dim dr As DataRow
      Dim ds As DataSet

      '20140430 - v2.1.0 - Added chkPartialImport [=True] as AdvM can only provide day files vs mounth to date.
      If blnPartialImport = True Then Exit Sub

      ds = db.doQuery("SELECT " & strDateSelector & " as strPeriod FROM " & Me.tblStaging & " GROUP BY " & strDateSelector & " ")

      Dim xdb As New db
      For Each dr In ds.Tables(0).Rows
         xdb.doQuery("UPDATE " & Me.tblDwf & " SET blnDelete = 1 WHERE strPeriod = '" & dr!strPeriod & "' AND blnDelete = 0")
         WriteLog("debug", Me.Name, "DW pre-delete", "blnDelete set for " & dr!strPeriod & " {#" & xdb.intRows & " records}")
      Next

   End Sub

   Public Sub DeleteDW()
      Dim xdb As New db

      xdb.doQuery("DELETE FROM " & Me.tblDwf & " WHERE blnDelete = 1")
      WriteLog("info", Me.Name, "Process DataWarehouse", xdb.intRows & " record(s) Deleted.")

   End Sub


   'child process override this function
   Public Overridable Function sqlInsertStaging(dr As String()) As String
      Return Nothing
   End Function

End Class
