Imports Superbowl_Cronjob.Process
Imports Superbowl_Cronjob.SystemFunctions
Imports Superbowl_Cronjob.ProjectFunctions
Imports Superbowl_Cronjob.db

Public Class PurchaseOrderRequisition_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
         '3.3 Archive local file after import

         Me.ClearStagingTable()
         Me.ImportLocalFile()
         Me.ArchiveFile() ' Move file from "Imports" folder to "Imported" folder

      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

      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(12) = "" Then Throw New InvalidCastException("Invalid SKU! '" & dr(12) & "'")

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



      Return "INSERT INTO stgPurchaseOrderRequisition(UniqueID, RepCode, RequestType, Date, Region, RequestDetail, ContactPerson, ContactNumber, SKU, QTY) " & _
         "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) & ")"

   End Function

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

      Return "INSERT INTO porRequisition (refUserID, UniqueID, refRequestTypeID, dtDate, strRegion, txtRequestDetail, strContactPerson, strContactNumber, strStatus) " & _
         "VALUES (" & dr!UserID & ", " & dr!UniqueID & ", " & dr!RequestTypeID & ", CONVERT(DATETIME, '" & dr!Date & "', 120), '" & dr!Region & "', '" & CleanString(dr!RequestDetail) & "', '" & CleanString(dr!ContactPerson) & "', '" & CleanString(dr!ContactNumber) & "', 'Pending')"

   End Function

   Public Sub Process()
      Dim ds, dsItems As DataSet
      Dim dr As DataRow
      Dim sql 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")
      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

                  db.doQuery(sql)


                  'update psn
                  dr = db.getRow("SELECT 'RG' + 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
                  'v1.0.4 - 20151028 - added fsrItem.strFirstUser & dtFirstEdit - pj
                  sql = "INSERT INTO fsrItem ( refRequisitionID, refBrandID, strProdCode, intQuantity, dblCost, dblTotal, strFirstUser, dtFirstEdit ) " & _
                        "SELECT " & RequisitionID & " AS refRequisitionID, dwdProduct.refBrandID, stgFreeStockRequisition.SKU, stgFreeStockRequisition.QTY, dwdProduct.dblPrice, Round([dblPrice]*[QTY],4) AS dblTotal, 'Cronjob', REPLACE(CONVERT(VARCHAR(10), GETDATE(), 102), '.', '-') + ' ' + LEFT(REPLACE(CONVERT(varchar, GETDATE(), 108), '', ''), 8) AS dt " & _
                        "FROM dwdProduct INNER JOIN stgFreeStockRequisition ON dwdProduct.strProdCode = stgFreeStockRequisition.SKU " & _
                        "WHERE stgFreeStockRequisition.UniqueID='" & UniqueID & "' AND stgFreeStockRequisition.UniqueID <> 0"
                  'frmCronjob.txtMessage.Text &= vbCrLf & 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

   End Sub

End Class
