Imports Snell_Cronjob.SystemFunctions
Imports Snell_Cronjob.ProjectFunctions
Imports Snell_Cronjob.db
'Imports Superbowl_Cronjob.QFGen
Imports System.Net.Mail
Imports System.IO


'v1.0.1 - ?
'v1.0.2 - 20131105 - Remember remember the 5th of November - Added 'ArchiveProduct' job - Christiaan
'v1.0.3 - 20141205 - FSR Process 0: Import Requisition from AdvM. JobType: None, process checks for file and runs
'v1.0.3 - 20141007 - FSR Process 1: Resend Email to alternate Manager if Requisition not approved within 3 days
'v1.0.3 - 20140922 - FSR Process 2: Send New Requisition Emails + PDF. JobType = "FSR_NewRequisition"
'v1.0.3 - 20140922 - FSR Process 3: Send Requisition Approved Emails + PDF. JobType = "FSR_RequisitionApproved"
'v1.0.3 - 20150113 - Manadatory check removed from fsr.strExpenseType [FSR Process 0] - pj [re: RE: rgbc: FSR AM integration [Operations]]

'v1s.0.3 - 20150928 - Project start SNELL
'v1s.0.3 - 20160127 - aptronnics unable to allow default sending, added creds - pj
'v1s.0.3 - 20160204 - changed preprocess.AltMan resend if datediff > 48h [rgbc: >3] - pj
'v1s.1.1 - 20160531 - added fsr import issues logging - maanie/pj
'v1s.1.2 - 20180830 - changed SMTP mail settings - maanie
'v1s.1.3 - 20200901 - changed SMTP mail host settings, as per justin's directive. Removed BCC addresses from FSR_NewRequisition and FSR_ApprovedRequisition
'v1s.1.4 - 20211020 - added cc (SnellEmailAddress3) to mailmessage from configfile - lloyd

Public Class frmCronjob

   Private Const intOpperationTimeMax As Long = 40 * 60 'sec
   Private lngOpperationTimeMax As Long = 10000000 * intOpperationTimeMax 'ticks = 100ns
   Private dtOpperationStart, dtOpp As DateTime
   Private Shared blnStopOpp = False

   Private intJob, intJobs As Integer
   Private strDownloadDir As String

   Private Sub frmCronjob_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

      'set dirs
      My.Settings.dirImports = System.AppDomain.CurrentDomain.BaseDirectory() & "Imports\"


#If DEBUG Then
      Me.strDownloadDir = "D:\Documents\Work Stuff\ESnell\"
#Else
      strDownloadDir = "C:\am_ftpdownload\downloads\" '[re: RE: snell: Paths Requested - PJ Silicone Overdrive]
#End If

      If My.Settings.Debug = False Then
         Run()
         Me.Close()
      End If
   End Sub

  Private Sub btnRun_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRun.Click
    Run()
    'db.doQuery("UPDATE sysCronJob SET Status = 'New'")
  End Sub
  Public Sub PreProcess()


    'v1.0.3 - 20141205 - FSR Process 0: Import Requisition from AdvM. JobType: None, process checks for file and runs

    Dim proFSR = New FreeStockRequisition_AM_Process("Free Stock Requisition", "Free Stock Requisition.csv", Me.strDownloadDir)
    proFSR.Run()

    'v1.0.3 - 20141007 - FSR Process 1: Resend Email to alternate Manager if Requisition not approved within 3 days
    Dim strTo As String
    Dim strBody As String = ""
    Dim strManagerEmail As String = ""
    Dim strManagerName As String = ""
    Dim strFrom As String = ""

#If DEBUG Then
      'Dim smtpC As New System.Net.Mail.SmtpClient("192.168.100.6")
      'v1s.1.2 - 20180830 - changed SMTP mail settings - maanie
      Dim smtpC As New System.Net.Mail.SmtpClient("smtp.office365.com", 587)
      smtpC.EnableSsl = True
      smtpC.UseDefaultCredentials = False
      smtpC.Credentials = New System.Net.NetworkCredential("smtp@overdrive.co.za", "Ram99144")
      strFrom = "FreeStock Requisition <smtp@overdrive.co.za>"
#Else ' Live
    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)) 'v1s.0.3 - 20160127 - aptronnics unable to allow default sending, added creds - pj
        'strFrom = "FreeStock Requisition <freestock@esnell.co.za>"
        strFrom = "FreeStock Requisition <noreply@esnell.co.za>"
#End If
        Dim mm As Net.Mail.MailMessage
    Dim ds As DataSet
    Dim drSysQuery As DataRow
    Dim drRequisition As DataRow
    Dim drManager As DataRow

    If TimeOfDay > #12:00:00 PM# And TimeOfDay < #12:19:00 PM# Then

      'v1s.0.3 - 20160204 - changed preprocess.AltMan resend if datediff > 48h [rgbc: >3] - pj
      'ds = db.doQuery("SELECT mstUser.strEmail, mstUser.strUserName, fsrRequestType.strRequestType, fsrRequisition.* FROM fsrRequisition INNER JOIN mstUser ON fsrRequisition.refUserID = mstUser.UserID INNER JOIN fsrRequestType ON fsrRequisition.refRequestTypeID =  fsrRequestType.RequestTypeID " & _
      '             "WHERE StrStatus = 'Pending' AND DATEDIFF(day, dtDate, getDate()) > 3 AND dtEmailSentToAltManager IS NULL")
      ds = db.doQuery("SELECT mstUser.strEmail, mstUser.strUserName, fsrRequestType.strRequestType, fsrRequisition.* FROM fsrRequisition INNER JOIN mstUser ON fsrRequisition.refUserID = mstUser.UserID INNER JOIN fsrRequestType ON fsrRequisition.refRequestTypeID =  fsrRequestType.RequestTypeID " &
                      "WHERE StrStatus = 'Pending' AND DATEDIFF(day, dtDate, getDate()) > 1 AND dtEmailSentToAltManager IS NULL")

      If ds.Tables.Count <> 0 Then
        For Each drRequisition In ds.Tables(0).Rows

          ' Get the manager, alternative manager, executive manager and the total value of the stock items for this requisition 
          'drManager = db.getRow(" SELECT ISNULL(mstUser.refManagerID,'0') AS refManagerID, ISNULL(tblManager.strEmail,'') AS ManagerEmail, ISNULL(mstUser.refManagerAlternativeID,'0') AS refManagerAlternativeID, ISNULL(tblAltManager.strEmail, '') AS AltManagerEmail, tblAltManager.strUserName AS AltManagerName, ISNULL(tblManager.refManagerExecutiveID, '0') AS refManagerExecutiveID, ISNULL(tblExeManager.strEmail, '') AS ExecutiveManagerEmail, tblExeManager.strUserName AS ExeManagerName, SUM(fsrItem.dblTotal) AS TotalRequisitionValue FROM fsrRequisition INNER JOIN mstUser ON fsrRequisition.refUserID = mstUser.UserID INNER JOIN fsrItem ON fsrRequisition.RequisitionID = fsrItem.refRequisitionID LEFT OUTER JOIN mstUser AS tblExeManager RIGHT OUTER JOIN mstUser AS tblManager ON tblExeManager.UserID = tblManager.refManagerExecutiveID ON mstUser.refManagerID = tblManager.UserID LEFT OUTER JOIN  mstUser AS tblAltManager ON mstUser.refManagerAlternativeID = tblAltManager.UserID WHERE(fsrRequisition.RequisitionID = " & drRequisition!RequisitionID & ") GROUP BY mstUser.refManagerID, tblManager.strEmail, mstUser.refManagerAlternativeID, tblAltManager.strEmail, tblManager.refManagerExecutiveID, tblExeManager.strEmail, tblAltManager.strUserName,tblExeManager.strUserName")
          drManager = db.getRow("SELECT * FROM vieRequsitionManager WHERE RequisitionID = " & drRequisition!RequisitionID)

          If drManager IsNot Nothing Then
            ' Update the requisition with the manager, alternative manager and executive manager for that requisition
            db.doQuery("UPDATE fsrRequisition SET refManagerID = " & drManager!refManagerID & ", refAlternateManagerID = " & drManager!refManagerAlternativeID & ", refExecutiveManagerID = " & drManager!refManagerExecutiveID & " WHERE RequisitionID =" & drRequisition!RequisitionID & "")

            'IF TotalRequisitionValue <= 20 000, email Alternate Manager Else email Executive manager 
            If drManager!TotalRequisitionValue <= My.Settings.SalesManagerMaxValue Then
              If drManager!AltManagerEmail IsNot Nothing And drManager!AltManagerEmail <> "" Then
                strManagerEmail = drManager!AltManagerEmail
                strManagerName = drManager!AltManagerName
              End If
            Else
              If drManager!ExecutiveManagerEmail IsNot Nothing And drManager!ExecutiveManagerEmail <> "" Then
                strManagerEmail = drManager!ExecutiveManagerEmail
                strManagerName = drManager!ExeManagerName
              End If
            End If

            Try
              If strManagerEmail <> "" Then
                drSysQuery = db.getRow("SELECT memQuery FROM sysQuery WHERE ID = 38")

                If drRequisition IsNot Nothing Then
                  'Check if body has content in it
                  If Not drSysQuery.IsNull("memQuery") Then

                    'Perform substitutions for the following fields:[ToName], [RequestedBy], [Date], [PinkSlipNumber], [BillToAccount], [ShipToAccount], [RequestType], [DetailOfRequest], [ContactName], [ContactNo], [Delivery]  
                    strBody &= drSysQuery!memQuery.ToString().Replace("[ToName]", strManagerName).Replace("[RequestedBy]", drRequisition!strUserName).Replace("[Date]", drRequisition!dtDate).Replace("[PinkSlipNumber]", drRequisition!strPinkSlipNumber).Replace("[BillToAccount]", drRequisition!strBillToAccount).Replace("[ShipToAccount]", drRequisition!strShipToAccount).Replace("[RequestType]", drRequisition!strRequestType).Replace("[DetailOfRequest]", drRequisition!txtRequestDetail).Replace("[ContactName]", drRequisition!strContactPerson).Replace("[ContactNo]", drRequisition!strContactNumber).Replace("[Delivery]", drRequisition!blnDelivery).Replace("[RequisitionID]", drRequisition!RequisitionID)

                  End If

                  'User requesting stock and manager of region 
#If DEBUG Then
                           strTo = "exiledbandit@gmail.com"
#Else
                  strTo = strManagerEmail
#End If

                  'Create PDF
                  createPDF(drRequisition!RequisitionID, drRequisition!strPinkSlipNumber, "FSR_NewRequisition")

                  If strTo.Length > 0 Then

                    mm = New Net.Mail.MailMessage(strFrom, strTo, "New Freestock Requisition " & drRequisition!strPinkSlipNumber, strBody)
                    mm.IsBodyHtml = True

                    Dim copy As MailAddress = New MailAddress(drRequisition!strEmail)
                    mm.CC.Add(copy)

                    'Dim copy2 As MailAddress = New MailAddress("maanie@overdrive.co.za")
                    'mm.CC.Add(copy2)

                    'Dim copy3 As MailAddress = New MailAddress("Beyersv@esnell.co.za")
                    'mm.CC.Add(copy3)

                    'Dim copy4 As MailAddress = New MailAddress("exiledbandit@gmail.com")
                    'mm.Bcc.Add(copy4)

                    'Dim arrBCC As String()
                    'arrBCC = My.Settings.BCCAddresses.Split(",")

                    'For Each address In arrBCC
                    '   mm.Bcc.Add(New MailAddress(address))
                    'Next

                    '#If Not Debug Then

                    '                                    mm.Bcc.Add(New MailAddress("Beyersv@esnell.co.za"))
                    '#End If

                    'MsgBox("Before attachment to alternate manager or executive manager")
                    'Attach PDF
                    mm.Attachments.Add(New Attachment(System.AppDomain.CurrentDomain.BaseDirectory() & "\Reports\PDF\SubmittedRequisition_" & drRequisition!strPinkSlipNumber & ".pdf"))
                    'MsgBox("Before send to " & strTo)
                    smtpC.Send(mm)

                    mm.Dispose()

                    'Once requisition is sent to the alternate manager then set dateSent
                    db.doQuery("UPDATE fsrRequisition SET dtEmailSentToAltManager = getdate() WHERE RequisitionID = " & drRequisition!RequisitionID & "")
                  End If
                End If
              End If
            Catch ex As Exception
              WriteLog("error", "CronJob", "FSR_NewRequisition", ex.Message & " :: " & ex.StackTrace)
            Finally
              'Clean strings
              strTo = ""
              strBody = ""
              strManagerEmail = ""
              strManagerName = ""

              Try
                If Not mm Is Nothing Then
                  mm.Dispose()
                End If
              Catch ex As Exception
              End Try

            End Try
          End If
        Next
      End If
    End If 'if time
  End Sub

  Public Sub Run()
      'step 0: Pre process
      'step 1: get number of items in queue
      '      : set opp time
      'step 2: get cronjobs
      '
      '

      'step 0: Pre process
      'v1.0.3 - 20141205 - FSR Process 0: Import Requisition from AdvM. JobType: None, process checks for file and runs
      'v1.0.3 - 20141007 - FSR Process 1: Resend Email to alternate Manager if Requisition not approved within 3 days
      PreProcess()

      Dim ds As DataSet
      Dim drCron As DataRow

      'step 1

      drCron = db.getRow("SELECT COUNT(*) as intJobs FROM sysCronJob WHERE Status = 'New'")
      intJobs = drCron!intJobs
      intJob = 0
      lblQueue.Text = intJob & "/" & intJobs

      dtOpperationStart = Date.Now
      dtOpp = New Date(Date.Now.Ticks - dtOpperationStart.Ticks)
      blnStopOpp = False

      'step 2
      ds = db.doQuery("SELECT * FROM sysCronJob WHERE Status = 'New' ORDER BY dtCronJob DESC")
      For Each drCron In ds.Tables(0).Rows
         'If CInt(dtOpp.ToString("m")) >= intOppertingTime Then
         'PRE
         If blnStopOpp = True Then
            Exit For
         End If

         'PROCESS
         Select Case drCron!Type

            'v1.0.3 - 20140922 - FSR Process 2: Send New Requisition Emails + PDF. JobType = "FSR_NewRequisition" - Maanie
            Case "FSR_NewRequisition"
               Dim param As New Collection
               Dim arrParam, tmp As Array
               Dim strTo As String
               Dim strBody As String = ""
               Dim strManagerEmail As String = ""
               Dim strManagerName As String = ""
               Dim blnSendingEmail = False
               Dim strFrom As String = ""

#If DEBUG Then
               'Dim smtpC As New System.Net.Mail.SmtpClient("192.168.100.6")
               'v1s.1.2 - 20180830 - changed SMTP mail settings - maanie
               Dim smtpC As New System.Net.Mail.SmtpClient("smtp.office365.com", 587)
               smtpC.EnableSsl = True
               smtpC.UseDefaultCredentials = False
               smtpC.Credentials = New System.Net.NetworkCredential("smtp@overdrive.co.za", "Ram99144")
               strFrom = "FreeStock Requisition <smtp@overdrive.co.za>"
#Else ' Live
               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)) 'v1s.0.3 - 20160127 - aptronnics unable to allow default sending, added creds - pj
                    'strFrom = "FreeStock Requisition <freestock@esnell.co.za>"
                    strFrom = "FreeStock Requisition <noreply@esnell.co.za>"
#End If
                    Dim mm As Net.Mail.MailMessage
               Dim drSysQuery As DataRow
               Dim drRequisition As DataRow
               Dim drManager As DataRow

               'MsgBox("Before updating cronjob table query where cronjobID = " & drCron!CronJobID & "")
               db.doQuery("UPDATE sysCronJob SET Status = 'Processing', Result = '[" & Date.Now.ToString("yyyyMMdd HH:mm:ss") & "]' WHERE CronJobID = " & drCron!CronJobID)
               'MsgBox("After updating cronjob table query")

               arrParam = drCron!param.ToString.Split("|")
               For idx = 0 To arrParam.Length - 1
                  tmp = arrParam(idx).ToString.Split("=")
            param.Add(tmp(1), tmp(0))
          Next

               ' Get the manager, alternative manager, executive manager and the total value of the stock items for this requisition 
               'drManager = db.getRow("SELECT ISNULL(mstUser.refManagerID,'0') AS refManagerID, ISNULL(tblManager.strEmail,'') AS ManagerEmail, tblManager.strUserName AS ManagerName, ISNULL(mstUser.refManagerAlternativeID, '0') AS refManagerAlternativeID, ISNULL(tblAltManager.strEmail, '') AS AltManagerEmail, ISNULL(tblManager.refManagerExecutiveID, '0') AS refManagerExecutiveID, ISNULL(tblExeManager.strEmail, '') AS ExecutiveManagerEmail,tblExeManager.strUserName As ExeManagerName, SUM(fsrItem.dblTotal) AS TotalRequisitionValue FROM fsrRequisition INNER JOIN mstUser ON fsrRequisition.refUserID = mstUser.UserID INNER JOIN fsrItem ON fsrRequisition.RequisitionID = fsrItem.refRequisitionID LEFT OUTER JOIN mstUser AS tblExeManager RIGHT OUTER JOIN mstUser AS tblManager ON tblExeManager.UserID = tblManager.refManagerExecutiveID ON mstUser.refManagerID = tblManager.UserID LEFT OUTER JOIN  mstUser AS tblAltManager ON mstUser.refManagerAlternativeID = tblAltManager.UserID WHERE(fsrRequisition.RequisitionID = " & param("RequisitionID") & ") GROUP BY mstUser.refManagerID, tblManager.strEmail, mstUser.refManagerAlternativeID, tblAltManager.strEmail, tblManager.refManagerExecutiveID, tblExeManager.strEmail, tblManager.strUserName,tblExeManager.strUserName")
               drManager = db.getRow("SELECT * FROM vieRequsitionManager WHERE RequisitionID = " & param("RequisitionID"))

               If drManager IsNot Nothing Then
                  ' Update the requisition with the manager, alternative manager and executive manager for that requisition
                  db.doQuery("UPDATE fsrRequisition SET refManagerID = " & drManager!refManagerID & ", refAlternateManagerID = " & drManager!refManagerAlternativeID & ", refExecutiveManagerID = " & drManager!refManagerExecutiveID & " WHERE RequisitionID =" & param("RequisitionID") & "")

                  'IF TotalRequisitionValue <= 5 000, email Manager Else email Executive manager 
                  If drManager!TotalRequisitionValue <= My.Settings.SalesManagerMaxValue Then
                     If drManager!ManagerEmail IsNot Nothing And drManager!ManagerEmail <> "" Then
                        strManagerEmail = drManager!ManagerEmail
                        strManagerName = drManager!ManagerName
                     End If
                  Else
                     If drManager!ExecutiveManagerEmail IsNot Nothing And drManager!ExecutiveManagerEmail <> "" Then
                        strManagerEmail = drManager!ExecutiveManagerEmail
                        strManagerName = drManager!ExeManagerName
                     End If
                  End If
                  'MsgBox("Manager email is: " & strManagerEmail)
                  Try

                     If strManagerEmail <> "" Then

                        drSysQuery = db.getRow("SELECT memQuery FROM sysQuery WHERE ID = 38")

                        'Retrieve Requisition Details
                        drRequisition = db.getRow("SELECT mstUser.strEmail, mstUser.strUserName, fsrRequestType.strRequestType, fsrRequisition.* FROM fsrRequisition INNER JOIN mstUser ON fsrRequisition.refUserID = mstUser.UserID INNER JOIN fsrRequestType ON fsrRequisition.refRequestTypeID = fsrRequestType.RequestTypeID WHERE RequisitionID =" & param("RequisitionID") & "")

                        'MsgBox("RequisitionID is: " & drRequisition!RequisitionID)
                        If drRequisition IsNot Nothing Then
                           'Check if body has content in it
                           If Not drSysQuery.IsNull("memQuery") Then

                              'Perform substitutions for the following fields:[ToName], [RequestedBy], [Date], [PinkSlipNumber], [BillToAccount], [ShipToAccount], [RequestType], [DetailOfRequest], [ContactName], [ContactNo], [Delivery], [TotalValue]  
                              strBody &= drSysQuery!memQuery.ToString().Replace("[ToName]", strManagerName).Replace("[RequestedBy]", drRequisition!strUserName).Replace("[Date]", drRequisition!dtDate).Replace("[PinkSlipNumber]", drRequisition!strPinkSlipNumber).Replace("[BillToAccount]", drRequisition!strBillToAccount).Replace("[ShipToAccount]", drRequisition!strShipToAccount).Replace("[RequestType]", drRequisition!strRequestType).Replace("[DetailOfRequest]", drRequisition!txtRequestDetail).Replace("[ContactName]", drRequisition!strContactPerson).Replace("[ContactNo]", drRequisition!strContactNumber).Replace("[Delivery]", drRequisition!blnDelivery).Replace("[RequisitionID]", drRequisition!RequisitionID).Replace("[TotalValue]", Math.Round(CDec(drManager!TotalRequisitionValue), 2, MidpointRounding.AwayFromZero))

                           End If

                           'User requesting stock and manager of region 
#If DEBUG Then
                           strTo = "exiledbandit@gmail.com"
#Else
                           strTo = strManagerEmail
#End If

                           'MsgBox("Before creating PDF for requisition: " & drRequisition!RequisitionID)
                           'Create PDF
                           createPDF(param("RequisitionID"), drRequisition!strPinkSlipNumber, "FSR_NewRequisition")
                           'MsgBox("After creating PDF for:" & strTo)
                           If strTo.Length > 0 Then

                              mm = New Net.Mail.MailMessage(strFrom, strTo, "New Freestock Requisition " & drRequisition!strPinkSlipNumber, strBody)
                              mm.IsBodyHtml = True
#If Not Debug Then
                              Dim copy As MailAddress = New MailAddress(drRequisition!strEmail)
                              mm.CC.Add(copy)
#End If
                                        'Dim arrBCC As String()
                                        'arrBCC = My.Settings.BCCAddresses.Split(",")

                                        'For Each address In arrBCC
                                        '   mm.Bcc.Add(New MailAddress(address))
                                        'Next

                                        '#If Not Debug Then

                                        '                                        mm.Bcc.Add(New MailAddress("Beyersv@esnell.co.za"))
                                        '#End If

                                        'Attach PDF
                                        mm.Attachments.Add(New Attachment(System.AppDomain.CurrentDomain.BaseDirectory() & "\Reports\PDF\SubmittedRequisition_" & drRequisition!strPinkSlipNumber & ".pdf"))
                              'MsgBox("Before sending email: " & strManagerEmail)
                              blnSendingEmail = True

                              smtpC.Send(mm)
                              'MsgBox("After sending email")

                              mm.Dispose()
                           End If

                           db.doQuery("UPDATE sysCronJob SET Status = 'Processed', Result = 'Successfully Emailed [" & Date.Now.ToString("yyyyMMdd HH:mm:ss") & "]' WHERE CronJobID = " & drCron!CronJobID)
                        End If
                     Else
                        db.doQuery("UPDATE sysCronJob SET Status = 'Warning', Result = 'No manager email address [" & drManager!refManagerID & "|" & drManager!refManagerAlternativeID & "|" & drManager!refManagerExecutiveID & "] [" & Date.Now.ToString("yyyyMMdd HH:mm:ss") & "]' WHERE CronJobID = " & drCron!CronJobID)
                     End If

                  Catch exSMTP As SmtpException
                     WriteLog("error", "CronJob", "FSR_NewRequisition: " & param("RequisitionID"), exSMTP.Message & " :: " & exSMTP.StackTrace)
                     If blnSendingEmail = True Then
                        db.doQuery("UPDATE sysCronJob SET Status = 'Processed_MailError', Result = 'Email sending [" & exSMTP.Message & " " & Date.Now.ToString("yyyyMMdd HH:mm:ss") & "]' WHERE CronJobID = " & drCron!CronJobID)
                     Else
                        db.doQuery("UPDATE sysCronJob SET Status = 'Error', Result = 'Error sending email [" & exSMTP.Message & " " & Date.Now.ToString("yyyyMMdd HH:mm:ss") & "]' WHERE CronJobID = " & drCron!CronJobID)
                     End If
                  Catch ex As Exception
                     WriteLog("error", "CronJob", "FSR_NewRequisition: " & param("RequisitionID"), ex.Message & " :: " & ex.StackTrace)
                     'MsgBox(ex.ToString)
                  Finally
                     'Clean strings
                     strTo = ""
                     strBody = ""
                     strManagerEmail = ""
                     strManagerName = ""

                     Try
                        If Not mm Is Nothing Then
                           mm.Dispose()
                        End If
                     Catch ex As Exception
                     End Try

                  End Try
               Else
                  db.doQuery("UPDATE sysCronJob SET Status = 'Warning', Result = 'No manager found [SELECT * FROM vieRequsitionManager WHERE RequisitionID = " & param("RequisitionID") & "] [" & Date.Now.ToString("yyyyMMdd HH:mm:ss") & "]' WHERE CronJobID = " & drCron!CronJobID)
               End If

               'v1.0.3 - 20140922 - FSR Process 3: Send Requisition Approved Emails + PDF. JobType = "FSR_RequisitionApproved" - Maanie
            Case "FSR_RequisitionApproved"
               Dim param As New Collection
               Dim arrParam, tmp As Array
               Dim strTo As String
               Dim strBody As String = ""
               Dim strManagerEmail As String = ""
               Dim strManagerName As String = ""
               Dim blnSendingEmail = False
               Dim strFrom As String = ""

#If DEBUG Then
               'Dim smtpC As New System.Net.Mail.SmtpClient("192.168.100.6")
               'v1s.1.2 - 20180830 - changed SMTP mail settings - maanie
               Dim smtpC As New System.Net.Mail.SmtpClient("smtp.office365.com", 587)
               smtpC.EnableSsl = True
               smtpC.UseDefaultCredentials = False
               smtpC.Credentials = New System.Net.NetworkCredential("smtp@overdrive.co.za", "Ram99144")
               strFrom = "FreeStock Requisition <smtp@overdrive.co.za>"
#Else ' Live
               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)) 'v1s.0.3 - 20160127 - aptronnics unable to allow default sending, added creds - pj
                    'strFrom = "FreeStock Requisition <freestock@esnell.co.za>"
                    strFrom = "FreeStock Requisition <noreply@esnell.co.za>"
#End If
                    Dim mm As Net.Mail.MailMessage
               Dim drSysQuery As DataRow
               Dim drRequisition As DataRow
               Dim drManager As DataRow

               db.doQuery("UPDATE sysCronJob SET Status = 'Processing', Result = '[" & Date.Now.ToString("yyyyMMdd HH:mm:ss") & "]' WHERE CronJobID = " & drCron!CronJobID)

               arrParam = drCron!param.ToString.Split("|")
               For idx = 0 To arrParam.Length - 1
                  tmp = arrParam(idx).ToString.Split("=")
                  param.Add(tmp(1), tmp(0))
               Next

               ' Get the manager, alternative manager, executive manager and the total value of the stock items for this requisition 
               'drManager = db.getRow(
               '   "SELECT ISNULL(mstUser.refManagerID, '0') AS refManagerID, ISNULL(tblManager.strEmail,'') AS ManagerEmail, tblManager.strUserName AS ManagerName, ISNULL(mstUser.refManagerAlternativeID, '0') AS refManagerAlternativeID, ISNULL(tblAltManager.strEmail, '') AS AltManagerEmail, ISNULL(tblManager.refManagerExecutiveID , '0') AS refManagerExecutiveID, ISNULL(tblExeManager.strEmail, '') AS ExecutiveManagerEmail,tblExeManager.strUserName As ExeManagerName, SUM(fsrItem.dblTotal) AS TotalRequisitionValue " & _
               '   "FROM fsrRequisition INNER JOIN mstUser ON fsrRequisition.refUserID = mstUser.UserID LEFT JOIN fsrItem ON fsrRequisition.RequisitionID = fsrItem.refRequisitionID LEFT OUTER JOIN mstUser AS tblExeManager RIGHT OUTER JOIN mstUser AS tblManager ON tblExeManager.UserID = tblManager.refManagerExecutiveID ON mstUser.refManagerID = tblManager.UserID LEFT OUTER JOIN  mstUser AS tblAltManager ON mstUser.refManagerAlternativeID = tblAltManager.UserID " & _
               '   "WHERE fsrRequisition.RequisitionID = " & param("RequisitionID") & " " & _
               '   "GROUP BY mstUser.refManagerID, tblManager.strEmail, mstUser.refManagerAlternativeID, tblAltManager.strEmail, tblManager.refManagerExecutiveID, tblExeManager.strEmail, tblManager.strUserName,tblExeManager.strUserName")
               drManager = db.getRow("SELECT * FROM vieRequsitionManager WHERE RequisitionID = " & param("RequisitionID"))

               If drManager IsNot Nothing Then
                  ' Update the requisition with the manager, alternative manager and executive manager for that requisition
                  db.doQuery("UPDATE fsrRequisition SET refManagerID = " & drManager!refManagerID & ", refAlternateManagerID = " & drManager!refManagerAlternativeID & ", refExecutiveManagerID = " & drManager!refManagerExecutiveID & " WHERE RequisitionID =" & param("RequisitionID") & "")

                  'IF TotalRequisitionValue <= 20 000, email Manager Else email Executive manager 
                  If drManager!TotalRequisitionValue <= My.Settings.SalesManagerMaxValue Then
                     If drManager!ManagerEmail IsNot Nothing And drManager!ManagerEmail <> "" Then
                        strManagerEmail = drManager!ManagerEmail
                        strManagerName = drManager!ManagerName
                     End If
                  Else
                     If drManager!ExecutiveManagerEmail IsNot Nothing And drManager!ExecutiveManagerEmail <> "" Then
                        strManagerEmail = drManager!ExecutiveManagerEmail
                        strManagerName = drManager!ExeManagerName
                     End If
                  End If
                  Try

                     If strManagerEmail <> "" Then
                        drSysQuery = db.getRow("SELECT memQuery FROM sysQuery WHERE ID = 39")

                        'Retrieve Requisition Details
                        drRequisition = db.getRow("SELECT mstUser.strEmail,mstUser.strUserName, fsrRequestType.strRequestType, fsrRequisition.* FROM fsrRequisition INNER JOIN mstUser ON fsrRequisition.refUserID = mstUser.UserID INNER JOIN fsrRequestType ON fsrRequisition.refRequestTypeID =  fsrRequestType.RequestTypeID WHERE RequisitionID =" & param("RequisitionID") & "")

                        If drRequisition IsNot Nothing Then
                           'Check if body has content in it
                           If Not drSysQuery.IsNull("memQuery") Then

                              'Perform substitutions for the following fields:
                              '[ToName], [RequestedBy], [Date], [PinkSlipNumber], [BillToAccount], [ShipToAccount], [RequestType], [DetailOfRequest], [ContactName], [ContactNo], [Delivery]  
                              strBody &= drSysQuery!memQuery.ToString().Replace("[ToName]", "Snell Clerk").Replace("[RequestedBy]", drRequisition!strUserName).Replace("[Date]", drRequisition!dtDate).Replace("[PinkSlipNumber]", drRequisition!strPinkSlipNumber).Replace("[BillToAccount]", drRequisition!strBillToAccount).Replace("[ShipToAccount]", drRequisition!strShipToAccount).Replace("[RequestType]", drRequisition!strRequestType).Replace("[DetailOfRequest]", drRequisition!txtRequestDetail).Replace("[ContactName]", drRequisition!strContactPerson).Replace("[ContactNo]", drRequisition!strContactNumber).Replace("[Delivery]", drRequisition!blnDelivery).Replace("[RequisitionID]", drRequisition!RequisitionID).Replace("[TotalValue]", Math.Round(CDec(drManager!TotalRequisitionValue), 2, MidpointRounding.AwayFromZero))

                           End If

                           'User requesting stock and the manager of region (Snell to be added later)
#If DEBUG Then
                           strTo = "exiledbandit@gmail.com"
#Else
                           strTo = My.Settings.SnellEmailAddress
#End If

                           'Create PDF
                           createPDF(param("RequisitionID"), drRequisition!strPinkSlipNumber, "FSR_RequisitionApproved")

                           If strTo.Length > 0 Then
                              mm = New Net.Mail.MailMessage(strFrom, strTo, "Approved Freestock Requisition " & drRequisition!strPinkSlipNumber, strBody)
                              mm.IsBodyHtml = True

                              Dim copy As MailAddress = New MailAddress(drRequisition!strEmail)
                              mm.CC.Add(copy)

                              'v1s.0.3 - 20160127 - added another cc - pj
                              copy = New MailAddress(My.Settings.SnellEmailAddress2)
                                        mm.CC.Add(copy)

                                        '20211020 added another cc - lloyd
                                        'Dim copy2 = New MailAddress(My.Settings.SnellEmailAddress3)
                                        'mm.CC.Add(copy2)

                                        Dim bcc = New MailAddress("maanie@overdrive.co.za")
                                        mm.Bcc.Add(bcc)

                                        'Dim arrBCC As String()
                                        'arrBCC = My.Settings.BCCAddresses.Split(",")

                                        'For Each address In arrBCC
                                        '   mm.Bcc.Add(New MailAddress(address))
                                        'Next

                                        '#If Not Debug Then

                                        '                                        mm.Bcc.Add(New MailAddress("Beyersv@esnell.co.za"))
                                        '#End If

                                        'Attach PDF
                                        mm.Attachments.Add(New Attachment(System.AppDomain.CurrentDomain.BaseDirectory() & "\Reports\PDF\ApprovedRequisition_" & drRequisition!strPinkSlipNumber & ".pdf"))

                              'MsgBox(param("RequisitionID") & ": to: " & strTo & ", c: " & drRequisition!strEmail & ", bcc: " & My.Settings.BCCAddresses & " [" & arrBCC.Count & "]")
                              blnSendingEmail = True

                              smtpC.Send(mm)

                              mm.Dispose()
                           End If

                           db.doQuery("UPDATE sysCronJob SET Status = 'Processed', Result = 'Successfully Emailed [" & Date.Now.ToString("yyyyMMdd HH:mm:ss") & "]' WHERE CronJobID = " & drCron!CronJobID)
                        End If
                     Else
                        db.doQuery("UPDATE sysCronJob SET Status = 'Warning', Result = 'No manager email address [" & drManager!refManagerID & "|" & drManager!refManagerAlternativeID & "|" & drManager!refManagerExecutiveID & "] [" & Date.Now.ToString("yyyyMMdd HH:mm:ss") & "]' WHERE CronJobID = " & drCron!CronJobID)
                     End If
                  Catch exSMTP As SmtpException
                     WriteLog("error", "CronJob", "FSR_ApprovedRequisition: " & param("RequisitionID"), exSMTP.Message & " :: " & exSMTP.StackTrace)
                     If blnSendingEmail = True Then
                        db.doQuery("UPDATE sysCronJob SET Status = 'Processed_MailError', Result = 'Email sending [" & exSMTP.Message & " " & Date.Now.ToString("yyyyMMdd HH:mm:ss") & "]' WHERE CronJobID = " & drCron!CronJobID)
                     Else
                        db.doQuery("UPDATE sysCronJob SET Status = 'Error', Result = 'Error sending email [" & exSMTP.Message & " " & Date.Now.ToString("yyyyMMdd HH:mm:ss") & "]' WHERE CronJobID = " & drCron!CronJobID)
                     End If
                  Catch ex As Exception
                     WriteLog("error", "CronJob", "FSR_ApprovedRequisition: " & param("RequisitionID"), ex.Message & " :: " & ex.StackTrace)
                  Finally
                     'Clean strings
                     strTo = ""
                     strBody = ""
                     strManagerEmail = ""
                     strManagerName = ""

                     Try
                        If Not mm Is Nothing Then
                           mm.Dispose()
                        End If
                     Catch ex As Exception
                     End Try

                  End Try
               Else
                  db.doQuery("UPDATE sysCronJob SET Status = 'Warning', Result = 'No manager found [SELECT * FROM vieRequsitionManager WHERE RequisitionID = " & param("RequisitionID") & "] [" & Date.Now.ToString("yyyyMMdd HH:mm:ss") & "]' WHERE CronJobID = " & drCron!CronJobID)
               End If
         End Select

         'POST
         intJob += 1

         UpdateProgress()

         dtOpp = New Date(Date.Now.Ticks - dtOpperationStart.Ticks)
         If dtOpp.Ticks >= lngOpperationTimeMax Then
            blnStopOpp = True
         End If
      Next

      'POST post
      UpdateProgress()

      'eoMain

      'reset jobs
      db.doQuery("UPDATE sysCronJob SET Status = 'New' WHERE Status LIKE ('%Processing%')")

   End Sub

   Private Sub UpdateProgress()

      lblOpperationTime.Text = New Date(Date.Now.Ticks - dtOpperationStart.Ticks).ToString("HH:mm:ss")
      Try
         pbOpperationTime.Value = CInt(dtOpp.Ticks / lngOpperationTimeMax * 100)
      Catch
         pbOpperationTime.Value = 100
      End Try

      lblQueue.Text = intJob & "/" & intJobs
      Try
         pbQueue.Value = CInt(intJob / intJobs * 100)
      Catch
         pbQueue.Value = 100
      End Try


   End Sub

   Private Sub StartTimer()
      dtOpperationStart = Date.Now
      If Not bgwTimer.IsBusy Then
         bgwTimer.RunWorkerAsync()
      Else
         If bgwTimer.WorkerSupportsCancellation Then
            bgwTimer.CancelAsync()
         End If
      End If
   End Sub

   Private Sub bgwTimer_ProgressChanged(ByVal sender As System.Object, ByVal e As System.ComponentModel.ProgressChangedEventArgs) Handles bgwTimer.ProgressChanged

      Try

         lblOpperationTime.Text = New Date(Date.Now.Ticks - dtOpperationStart.Ticks).ToString("HH:mm:ss")
         pbOpperationTime.Value = e.ProgressPercentage

      Catch aoorEx As ArgumentOutOfRangeException
         If My.Settings.Debug = False Then
            Me.Close()
         Else
            pbOpperationTime.Value = pbOpperationTime.Maximum
            dtOpp = New Date(lngOpperationTimeMax)
         End If
      Catch ex As Exception

      End Try

   End Sub

   Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs)
      StartTimer()
   End Sub

End Class