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

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 = ""
#If DEBUG Then
      Dim smtpC As New System.Net.Mail.SmtpClient("192.168.100.6")
#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
#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("FreeStock Requisition <administrator@esnell.co.za>", 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
#If DEBUG Then
               Dim smtpC As New System.Net.Mail.SmtpClient("192.168.100.6")
#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
#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("FreeStock Requisition <administrator@esnell.co.za>", 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
#If DEBUG Then
               Dim smtpC As New System.Net.Mail.SmtpClient("192.168.100.6")
#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
#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("FreeStock Requisition <administrator@esnell.co.za>", 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)

                              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
