Imports Superbowl_Daily_2.SystemFunctions
Imports Superbowl_Daily_2.ProjectFunctions
Imports Superbowl_Daily_2.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 barProcess As ProgressBar

   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, arrPK As String()) 'PK As string()(Of KeyValuePair(Of String, 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 ImportView()
      Dim sql As String

      Me.intRecordsTotal = db_trgbc.countRows("SELECT * FROM " & Me.Path) 'get record count in view (Path = view name)

      Try
         'process: note that the sqlInsertSTG get overriden in the child entity
         sql = Me.sqlInsertStaging()
         db_trgbc.doQuery(sql)
      Catch ex As Exception
         WriteLog("error", Me.Name, "View Import", "Import Error! [" & ex.Message & "]")
      Finally
         'post
         Me.Progress()
      End Try

      Dim dr As DataRow = db.getRow("SELECT count(*) as intRecords FROM " & Me.tblStaging)
      WriteLog("info", Me.Name, "View 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("yyyyMMddHHmmss") & Me.fiImport.Extension, True)
#Else
         File.Move(My.Settings.dirImports & Me.Filename, My.Settings.dirImported & Me.Filename & "." & Date.Now.ToString("yyyyMMddHHmmss") & Me.fiImport.Extension)
#End If
      Catch ex As Exception
         WriteLog("warning", Me.Name, "ArchiveFile", "Error archiving file [" & ex.Message & "] ")
      End Try
   End Sub

   Public Sub setProgress()
        Dim numberOfRecords As Integer = Me.intRecordsTotal

        If numberOfRecords > 0 Then
            Me.barProcess.Maximum = numberOfRecords ' 20200206 - Oncon Promo - fixed import progress bar - Alex
            Me.barProcess.Step = 1
            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

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

End Class