'v1s.1.1 - 20160531 - added fsr import issues logging - maanie/pj

Imports Snell_Cronjob.SystemFunctions
Imports Snell_Cronjob.ProjectFunctions
Imports Snell_Cronjob.db
Imports System.Net.Mail
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 CheckLocalFileUpdatedTime(importFileInfo)
    If Today >= #07:45:00 AM# And Today <= #08:00:00 AM# Then 'Time Frame Check

      Dim exists As Boolean = importFileInfo.Exists
      If exists Then Console.WriteLine("File exists") Else Console.WriteLine("File does not exist")

      Dim dateTimeNow As DateTime = DateTime.Now

      Dim updatedTime As Date = System.IO.File.GetLastWriteTime(importFileInfo.FullName)

      Dim timeDifference As Integer = (dateTimeNow - updatedTime).TotalHours

      Dim notifyPeriod As Integer = My.Settings.StaleFileNotifyPeriodHours

      If timeDifference >= notifyPeriod Then EmailManager()

    End If 'Time Frame Check
  End Sub

  Public Sub EmailManager()
    'Variables used in Email Message
    Dim strFrom As String = ""
    Dim strTo As String = ""
    Dim strCC As String = ""

    Dim strBody As String = ""

    Dim smtpC As New System.Net.Mail.SmtpClient(My.Settings.SMTPHost)
    smtpC.EnableSsl = False
    smtpC.Credentials = New System.Net.NetworkCredential(My.Settings.SMTP_Username, unsalt(My.Settings.SMTP_Password))

        'Not sure what the subject / From message should be
        strFrom = "FreeStock Requisition <noreply@esnell.co.za>"

        strTo = My.Settings.StaleFileNotifyManagerEmail
    strCC = My.Settings.StaleFileNotifyCCEmail

    If strTo <> "" And strTo.Length > 0 Then 'TO Recipient not null check
      Dim mm As Net.Mail.MailMessage

      strBody = "The 'Free Stock Requisition.csv' file has not been updated in the last " + My.Settings.StaleFileNotifyPeriodHours.ToString() + " hours."

      Try 'Send Mail Block

        mm = New Net.Mail.MailMessage(strFrom, strTo, "ATTENTION: Free Stock Requisition File", strBody)
        mm.IsBodyHtml = True

        If strCC.Length > 0 Then
          Dim arrCC As String()
          arrCC = strCC.Split(",")

          For Each address In arrCC
            Dim copy As MailAddress = New MailAddress(strCC)
            mm.CC.Add(copy)
          Next
        End If

        smtpC.Send(mm)

        mm.Dispose()
      Catch ex As Exception
        WriteLog("error", "CronJob", "Snell CronJob Stale File Email ", ex.Message & " :: " & ex.StackTrace)
      Finally
        'Clean strings
        strFrom = ""
        strTo = ""
        strCC = ""
        strBody = ""
        Try
          If Not mm Is Nothing Then
            mm.Dispose()
          End If
        Catch ex As Exception
        End Try
      End Try


    End If 'TO Recipient not null check

  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)

        CheckLocalFileUpdatedTime(fiImport)


        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
            Me.intRecordsTotal = Me.intRecordsTotal - 1 'for some odd reason this is always 1 to many, maybe eof bit?

            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("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
         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