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

Imports Snell_Cronjob.Process
Imports Snell_Cronjob.SystemFunctions
Imports Snell_Cronjob.ProjectFunctions
Imports Snell_Cronjob.db

Public Class FreeStockRequisition_AM_Process : Inherits Process
   Sub New(Process As Process)
      MyBase.New(Process.Name, Process.Filename, Process.Path)
   End Sub

   Sub New(Name As String, Filename As String, Path As String)
      MyBase.New(Name, Filename, Path)
   End Sub

   Public Sub Run()
      '1 download & log
      '2 check file, set pgbar
      '3.1 clear stg
      '3.2 import from local file
      '4.1 log duplicate
      '4.2 set deletes 'n/a
      '4.3 insert/update stg=>dw
      '4.4 delete deletes 'n/a


      '1 download & log
      Me.DownloadFile()

      '2 check file
      Me.CheckLocalFile()


      If Me.isValid = True Then
         '3.1 clear stg
         '3.2 import from local file

         Me.ClearStagingTable()
         Me.ImportLocalFile()
         Me.ArchiveFile()

      End If

      If Me.isValid = True Then

         'Me.barProcess.BackColor = Color.DarkOrchid
         'Me.barProcess.Style = ProgressBarStyle.Continuous
         'Me.barProcess.Refresh()

         '4.1 log duplicate
         'Me.LogDuplicates() 'n/a
         '4.2 set deletes

         'Me.SetDeletes("LEFT(strPromoDate,6)", frmMain.chkPartialImport.Checked)

         '4.3 insert/update stg=>dw
         Me.Process()
         '4.4 delete deletes
         'Me.DeleteDW()

         'Me.barProcess.Style = ProgressBarStyle.Blocks
         'Me.barProcess.Refresh()
      End If


      Me.LogProcessCompleted()

   End Sub

   'v1.0.3 - 20150113 - Manadatory check removed from fsr.strExpenseType [FSR Process 0] - pj [re: RE: rgbc: FSR AM integration [Operations]]
   Public Overrides Function sqlInsertStaging(dr As String()) As String
      'UniqueID 	Rep Code	RequestType	Date	Region	ExpenseType	BillToAccount	ShipToAccount	RequestDetail	ContactPerson	ContactNumber	Delivery	SKU	QTY_CASE QTY_BOTTLE

      dr(0) = CleanString(dr(0))
      dr(1) = CleanString(dr(1))
      dr(2) = CleanString(dr(2))
      dr(3) = CleanString(dr(3))
      dr(4) = CleanString(dr(4))
      dr(5) = CleanString(dr(5))
      dr(6) = CleanString(dr(6))
      dr(7) = CleanString(dr(7))
      dr(8) = CleanString(dr(8))
      dr(9) = CleanString(dr(9))
      dr(10) = CleanString(dr(10))
      dr(11) = CleanString(dr(11))
      dr(12) = CleanString(dr(12))
      'dr(13) = CleanString(dr(13))

      If dr(0) = "" Then Throw New InvalidCastException("Invalid UniqueID! '" & dr(0) & "'")
      If dr(1) = "" Then Throw New InvalidCastException("Invalid Rep Code! '" & dr(1) & "'")
      If dr(2) = "" Then Throw New InvalidCastException("Invalid Request Type! '" & dr(2) & "'")
      If dr(3).Length <> 10 Then Throw New InvalidCastException("Invalid Date! '" & dr(3) & "'")
      If dr(4) = "" Then Throw New InvalidCastException("Invalid Region! '" & dr(4) & "'")
      'If dr(5) = "" Then Throw New InvalidCastException("Invalid Expense Type! '" & dr(5) & "'")
      If dr(6) = "" Then Throw New InvalidCastException("Invalid Bill to Account! '" & dr(6) & "'")
      If dr(7) = "" Then Throw New InvalidCastException("Invalid Ship to Account! '" & dr(7) & "'")
      If dr(12) = "" Then Throw New InvalidCastException("Invalid SKU! '" & dr(12) & "'")

      If dr(13) = "" Then Throw New InvalidCastException("Invalid CASE Quantity! '" & dr(13) & "'")
      If dr(14) = "" Then Throw New InvalidCastException("Invalid BOTTLE Quantity! '" & dr(14) & "'")

      If dr(13).ToString = "0" And dr(14).ToString = "0" Then Throw New InvalidCastException("Quantity is 0! '" & dr(13) & " & " & dr(14) & "'")



      Return "INSERT INTO stgFreeStockRequisition(UniqueID, RepCode, RequestType, Date, Region, ExpenseType, BillToAccount, ShipToAccount, RequestDetail, ContactPerson, ContactNumber, Delivery, SKU, QTY_CASE, QTY_BOTTLE) " & _
         "VALUES ('" & dr(0) & "', '" & dr(1) & "', '" & dr(2) & "', '" & dr(3) & "', '" & dr(4) & "', '" & dr(5) & "', '" & dr(6) & "', '" & dr(7) & "', '" & dr(8) & "', '" & dr(9) & "', '" & dr(10) & "', '" & dr(11) & "', '" & dr(12) & "', " & dr(13) & ", " & dr(14) & ")"

   End Function

   Public Function sqlInsertFSR(dr As DataRow) As String
      'UniqueID 	Rep Code	RequestType	Date	Region	ExpenseType	BillToAccount	ShipToAccount	RequestDetail	ContactPerson	ContactNumber	Delivery	SKU	QTY

      Return "INSERT INTO fsrRequisition (refUserID, UniqueID, refRequestTypeID, dtDate, strRegion, strExpense, strBillToAccount, strShipToAccount, txtRequestDetail, strContactPerson, strContactNumber, blnDelivery, strStatus) " & _
         "VALUES (" & dr!UserID & ", " & dr!UniqueID & ", " & dr!RequestTypeID & ", CONVERT(DATETIME, '" & dr!Date & "', 120), '" & dr!Region & "', '" & dr!ExpenseType & "', '" & dr!BillToAccount & "', '" & dr!ShipToAccount & "', '" & CleanString(dr!RequestDetail) & "', '" & CleanString(dr!ContactPerson) & "', '" & CleanString(dr!ContactNumber) & "', " & CInt(dr!Delivery) & ", 'Pending')"

   End Function

   Public Sub Process()
      Dim ds, dsMissingFSRs, dsItems As DataSet
      Dim dr, drMissingFSRs As DataRow
      Dim sql, sqlMissingFSRs As String
      Dim intCreated = 0


      'get all requests from stg
      'if pinkslipnumber is null, insert fsr, update psn, insert items, create cron job record


      'v1.0.3 - 20150120 - check if RequestType exists, if not insert new RequestType - pj
      db.doQuery(db.getRow("SELECT * FROM sysQuery WHERE ID = 41")!memQuery)
      'INSERT INTO fsrRequestType (strRequestType, blnActive, strLastUser) 
      'SELECT stgFreeStockRequisition.RequestType, 1, 'SB CRON: AM Import'
      'FROM fsrRequestType AS fsrRequestType_1 RIGHT JOIN stgFreeStockRequisition ON fsrRequestType_1.strRequestType = stgFreeStockRequisition.RequestType
      'WHERE (fsrRequestType_1.RequestTypeID Is NULL)
      'GROUP BY stgFreeStockRequisition.RequestType



      'get all requests from stg
      dr = db.getRow("SELECT * FROM sysQuery WHERE ID = 40")
      If My.Settings.Debug = True Then WriteLog("dev", Me.Name, "FSR AM Import", dr!memQuery)

      Try
         ds = db.doQuery(dr!memQuery)
         'frmCronjob.txtMessage.Text = dr!memQuery
         For Each dr In ds.Tables(0).Rows
            Dim UniqueID As Integer = 0
            Dim RequisitionID As Integer = 0
            Dim strPinkSlipNumber As String = ""


            If dr!strPinkSlipNumber.ToString = "" Then
               Try
                  UniqueID = dr!UniqueID

                  'insert fsr
                  sql = Me.sqlInsertFSR(dr)
                  'frmCronjob.txtMessage.Text &= vbCrLf & vbCrLf & sql

                  If My.Settings.Debug = True Then WriteLog("dev", Me.Name, "FSR AM Import", sql)
                  db.doQuery(sql)


                  'update psn
                  dr = db.getRow("SELECT 'ES' + RIGHT('0000000' + CAST(MAX(RequisitionID) AS varchar(10)), 7) AS strPinkSlipNumber, MAX(RequisitionID) As RequisitionID FROM fsrRequisition")
                  RequisitionID = dr!RequisitionID
                  strPinkSlipNumber = dr!strPinkSlipNumber
                  db.doQuery("UPDATE fsrRequisition SET strPinkSlipNumber = '" & strPinkSlipNumber & "' WHERE RequisitionID = " & dr!RequisitionID & "")

                  'insert items
                  sql = "INSERT INTO fsrItem ( refRequisitionID, strBrand, strProdCode, intQuantityCase, dblCostCase, intQuantityBottle, dblCostBottle, dblTotal ) " & _
                        "SELECT " & RequisitionID & " AS refRequisitionID, dwdProduct.strBrand, stgFreeStockRequisition.SKU, stgFreeStockRequisition.QTY_CASE, dwdProduct.dblPriceCase, stgFreeStockRequisition.QTY_BOTTLE, dwdProduct.dblPriceBottle, Round(([dblPriceCase]*[QTY_CASE])+([dblPriceBottle]*[QTY_BOTTLE]),4) AS dblTotal " & _
                        "FROM dwdProduct INNER JOIN stgFreeStockRequisition ON dwdProduct.strProdCode = stgFreeStockRequisition.SKU " & _
                        "WHERE stgFreeStockRequisition.UniqueID='" & UniqueID & "'"
                  'frmCronjob.txtMessage.Text &= vbCrLf & sql

                  If My.Settings.Debug = True Then WriteLog("dev", Me.Name, "FSR AM Import", sql)

                  db.doQuery(sql)

                  'create cron job record

                  db.doQuery("INSERT INTO sysCronJob(dtCronJob, Type, param, Status) VALUES (CONVERT(VARCHAR(8), GETDATE(), 112) + LEFT(REPLACE(CONVERT(varchar, GETDATE(), 108), ':',''),4) ,'FSR_NewRequisition','RequisitionID=" & RequisitionID & "', 'New')")

                  intCreated += 1

               Catch exFSR As Exception
                  WriteLog("error", Me.Name, "FSR AM Import", exFSR.Message & " :: " & exFSR.StackTrace)
               End Try

            End If
         Next
         WriteLog("info", Me.Name, "FSR AM Import", intCreated & " record(s) Inserted.")
      Catch ex As Exception
         WriteLog("error", Me.Name, "FSR AM Import", ex.Message & " :: " & ex.StackTrace)
      End Try

      'v1s.1.1 - 20160531 - added fsr import issues logging - maanie/pj
      dr = db.getRow("SELECT * FROM sysQuery WHERE ID = 42")
      'SELECT UniqueID, RepCode, Date, Region, BillToAccount, ShipToAccount
      'FROM stgFreeStockRequisition LEFT JOIN fsrRequisition ON UniqueID = fsrRequisition.UniqueID
      'WHERE (((fsrRequisition.UniqueID) Is Null))
      'GROUP BY UniqueID, RepCode, Date, Region, BillToAccount, ShipToAccount
      'ORDER BY UniqueID
      ds = db.doQuery(dr!memQuery)

      For Each dr In ds.Tables(0).Rows
         WriteLog("warning", Me.Name, "FSR AM Import", "Record not imported: [UID:" & dr!UniqueID & "; RepID:" & dr!RepCode & "; Date: " & dr!Date & "]")
      Next

   End Sub

End Class