Imports Superbowl_Daily_2.SystemFunctions
Imports Superbowl_Daily_2.ProjectFunctions
Imports Superbowl_Daily_2.db
Imports Superbowl_Daily_2.Process

Imports System.IO
Imports Microsoft.VisualBasic

Public Class OnconSurvey_Process : Inherits Process

   Sub New(Process As Process)
      MyBase.New(Process.Name, Process.Filename, Process.Path, Process.PK)

      Me.barProcess = Process.barProcess

   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
      '4.3 insert/update stg=>dw
      '4.4 delete deletes


      '1 download & log
      Me.LogProcessStart()
      Me.DownloadFile()

      '2 check file, set pgbar
      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()
         '4.2 set deletes
         Me.SetDeletes("LEFT(strDateSurveyed,6)", frmMain.chkPartialImport.Checked)
         '4.3 insert/update stg=>dw
         Me.ProcessDW()
         '4.4 delete deletes
         Me.DeleteDW()

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


      Me.LogProcessCompleted()

   End Sub

   'Public Sub DownloadFile() 'moved to parent

   'Public Sub ImportLocalFile()
   '   'log "File Import"

   '   'ini
   '   Dim fileImport As Object
   '   Dim intRecord As Integer
   '   Dim sql As String


   '   fileImport = New FileIO.TextFieldParser(Me.fiImport.FullName)
   '   WriteLog("info", Me.Name, "File Import", "Import Start: {0/" & Me.intRecordsTotal & "}")

   '   'setup csv/text
   '   Select Case Me.fiImport.Extension
   '      Case ".csv" 'http://stackoverflow.com/questions/736629/parse-delimited-csv-in-net
   '         fileImport.TextFieldType = FileIO.FieldType.Delimited
   '         fileImport.Delimiters = New String() {","}
   '         fileImport.HasFieldsEnclosedInQuotes = False


   '      Case Else
   '         WriteLog("error", Me.Name, "File Import", "Error reading record! Unsupported file type [" & Me.fiImport.FullName & "]")
   '         Me.isValid = False
   '         Exit Sub

   '   End Select

   '   'read data line by line
   '   Dim drImport As String()


   '   While Not fileImport.endOfData
   '      Try
   '         'ini
   '         drImport = fileImport.ReadFields()
   '         intRecord += 1

   '         'process
   '         sql = sqlInsertStaging(drImport)
   '         db.doQuery(sql)

   '      Catch icEx As InvalidCastException
   '         WriteLog("warning", Me.Name, "File Import", "Invalid record: {" & intRecord & "/" & Me.intRecordsTotal & "} [" & icEx.Message & "]")
   '      Catch sqlEx As System.Data.SqlClient.SqlException
   '         If intRecord > 1 Then 'ln 1 might be the file headings
   '            WriteLog("warning", Me.Name, "File Import", "Error reading record: {" & intRecord & "/" & Me.intRecordsTotal & "} [" & sqlEx.Message & " :: " & sql & "]")
   '         End If
   '      Catch mlEx As FileIO.MalformedLineException
   '         WriteLog("warning", Me.Name, "File Import", "Error reading record: Malformed data record! {" & intRecord & "/" & Me.intRecordsTotal & "} [" & mlEx.Message & "]")
   '      Catch ex As Exception
   '         WriteLog("error", Me.Name, "File Import", "Error reading record! {" & intRecord & "/" & Me.intRecordsTotal & "} [" & ex.Message & "]")

   '      Finally
   '         'post
   '         Me.Progress()

   '      End Try


   '   End While

   '   Dim dr As DataRow = db.getRow("SELECT count(*) as intRecords FROM " & Me.tblStaging)
   '   WriteLog("info", Me.Name, "File Import", "Import Completed: {" & dr!intRecords & "/" & Me.intRecordsTotal & " records imported}")


   'End Sub

   Public Sub ProcessDW()
      Dim dr As DataRow
      Dim xdb As New db

      'update stg <=> dwf
      dr = db.getRow("SELECT * FROM sysQuery WHERE ID = 23")
      Try
         xdb.doQuery(dr!memQuery)
         WriteLog("info", Me.Name, "Process DataWarehouse", xdb.intRows & " record(s) Updated.")
      Catch ex As Exception
      End Try

      'insert stg => dwf
      dr = db.getRow("SELECT * FROM sysQuery WHERE ID = 24")
      Try
         xdb.doQuery(dr!memQuery)
         WriteLog("info", Me.Name, "Process DataWarehouse", xdb.intRows & " record(s) Inserted.")
      Catch ex As Exception
      End Try
   End Sub








    '************************
    '*** sql ***
    '************************

    ' 20140221 - v2.0.1 - change OnconSurvey process to include new survey questions (update stg insert)

    Public Overrides Function sqlInsertStaging(dr As String()) As String

        dr(1) = CleanString(dr(1))
        dr(2) = CleanString(dr(2))

        If dr(0) = "" Then Throw New InvalidCastException("Invalid Customer Number! '" & dr(0) & "'")
        If dr(2) = "" Then Throw New InvalidCastException("Invalid SKU! '" & dr(2) & "'")
      If dr(7).Length <> 8 Then Throw New InvalidCastException("Invalid Survey Date! '" & dr(7) & "'") ' 20200218 - Oncon Survey - Import Column change - Alex

        'Dim sql As String
        'Sql = "INSERT INTO stgOnconSurvey (strCustomerNo, strCustomerName, strBrand, blnDistribution, dblRSP, blnDisplayBar, blnDisplayOptic, blnDisplaySpeedRail, blnVisibleAccessories, blnVisibleClothing, blnVisibleMenu, blnVisibleSignage, strDateSurveyed, blnBrandedWallsMirrors, blnLightBoxes, blnFridges, blnBrandArea, blnPlinth, blnPerfectServeTools, blnPDULounge, blnGeneralVisibility) " &
        '"VALUES ('" & CleanString(dr(0)) & "', '" & CleanString(dr(1)) & "', '" & CleanString(dr(2)) & "', " & dr(3) & ", " & dr(4) & ", " & dr(5) & ", 0, 0, 0, 0, 0, 0, '" & CleanString(dr(8)) & "', 0, 0, 0, 0, 0, 0, " & dr(6) & ", " & dr(7) & ")"

        ' 20200218 - Oncon Survey - Import Column change - Alex
        ' 20200527 - Oncon Survey - Column Changes - maanie
        Return "INSERT INTO stgOnconSurvey (strCustomerNo, strCustomerName, strBrand, blnDistribution, dblRSP, blnPermanentVisibility, blnTemporaryVisibility, blnDisplayOptic, blnDisplaySpeedRail, blnVisibleAccessories, blnVisibleClothing, blnVisibleMenu, blnVisibleSignage, strDateSurveyed, blnBrandedWallsMirrors, blnLightBoxes, blnFridges, blnBrandArea, blnPlinth, blnPerfectServeTools, blnGeneralVisibility, blnBobVisibility) " &
        "VALUES ('" & CleanString(dr(0)) & "', '" & CleanString(dr(1)) & "', '" & CleanString(dr(2)) & "', " & dr(3) & ", " & dr(4) & ", " & dr(5) & ", " & dr(6) & ", 0, 0, 0, 0, 0, 0, '" & CleanString(dr(7)) & "', 0, 0, 0, 0, 0, 0, 0, " & dr(8) & ")"


        'Return "INSERT INTO stgOnconSurvey (strCustomerNo, strCustomerName, strBrand, blnDistribution, dblRSP, blnDisplayBar, blnDisplayOptic, blnDisplaySpeedRail, blnVisibleAccessories, blnVisibleClothing, blnVisibleMenu, blnVisibleSignage, strDateSurveyed, blnBrandedWallsMirrors, blnLightBoxes, blnFridges, blnBrandArea, blnPlinth, blnPerfectServeTools, blnPDULounge, blnGeneralVisibility) " &
        '"VALUES ('" & CleanString(dr(0)) & "', '" & CleanString(dr(1)) & "', '" & CleanString(dr(2)) & "', " & dr(3) & ", " & dr(4) & ", " & dr(5) & ", 0, 0, 0, 0, 0, 0, '" & CleanString(dr(8)) & "', 0, 0, 0, 0, 0, 0, " & dr(6) & ", " & dr(7) & ")"

        'v2.0.1
        'Return "INSERT INTO stgOnconSurvey (strCustomerNo, strCustomerName, strBrand, blnDistribution, dblRSP, blnDisplayBar, blnDisplayOptic, blnDisplaySpeedRail, blnVisibleAccessories, blnVisibleClothing, blnVisibleMenu, blnVisibleSignage, strDateSurveyed, blnBrandedWallsMirrors, blnLightBoxes, blnFridges, blnBrandArea, blnPlinth, blnPerfectServeTools) " &
        '"VALUES ('" & CleanString(dr(0)) & "', '" & CleanString(dr(1)) & "', '" & CleanString(dr(2)) & "', " & dr(3) & ", " & dr(4) & ", " & dr(5) & ", 0, 0, 0, " & dr(6) & ", 0, 0, '" & CleanString(dr(13)) & "', " & dr(7) & ", " & dr(8) & ", " & dr(9) & ", " & dr(10) & ", " & dr(11) & ", " & dr(12) & ")"

        'v2.0.0
        'Return "INSERT INTO stgOnconSurvey (strCustomerNo, strCustomerName, strBrand, blnDistribution, dblRSP, blnDisplayBar, blnDisplayOptic, blnDisplaySpeedRail, blnVisibleAccessories, blnVisibleClothing, blnVisibleMenu, blnVisibleSignage, strDateSurveyed) " & _
        '"VALUES ('" & CleanString(dr(0)) & "', '" & CleanString(dr(1)) & "', '" & CleanString(dr(2)) & "', " & dr(3) & ", " & dr(4) & ", " & dr(5) & ", " & dr(6) & ", " & dr(7) & ", " & dr(8) & ", " & dr(9) & ", " & dr(10) & ", " & dr(11) & ", '" & CleanString(dr(12)) & "')"

    End Function

End Class


'USE [Superbowl]
'GO

'/****** Object:  Table [dbo].[dwfOnconSurvey]    Script Date: 2014/07/16 1:49:28 PM ******/
'SET ANSI_NULLS ON
'GO

'SET QUOTED_IDENTIFIER ON
'GO

'SET ANSI_PADDING ON
'GO

'CREATE TABLE [dbo].[dwfOnconSurvey](
'	[strCustomerNo] [nvarchar](10) NOT NULL,
'	[strBrand] [nvarchar](30) NOT NULL,
'	[strDateSurveyed] [nvarchar](10) NOT NULL,
'	[strCustomerName] [nvarchar](80) NULL,
'	[blnDistribution] [bit] NULL,
'	[dblRSP] [float] NULL,
'	[blnDisplayBar] [bit] NULL,
'	[blnDisplayOptic] [bit] NULL,
'	[blnDisplaySpeedRail] [bit] NULL,
'	[blnVisibleAccessories] [bit] NULL,
'	[blnVisibleClothing] [bit] NULL,
'	[blnVisibleMenu] [bit] NULL,
'	[blnVisibleSignage] [bit] NULL,
'	[blnBrandedWallsMirrors] [bit] NULL,
'	[blnLightBoxes] [bit] NULL,
'	[blnFridges] [bit] NULL,
'	[blnBrandArea] [bit] NULL,
'	[blnPlinth] [bit] NULL,
'	[blnPerfectServeTools] [bit] NULL,
'	[strPeriod] [varchar](6) NOT NULL,
'	[blnDelete] [tinyint] NOT NULL,
' CONSTRAINT [PK_dwfOnconSurvey] PRIMARY KEY CLUSTERED 
'(
'	[strCustomerNo] ASC,
'	[strBrand] ASC,
'	[strDateSurveyed] ASC
')WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
') ON [PRIMARY]

'GO

'SET ANSI_PADDING OFF
'GO