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 OffconSurvey_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() 'moved to parent   

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

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

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








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

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


      dr(1) = CleanString(dr(1))
      dr(3) = CleanString(dr(3))

      If dr(0) = "" Then Throw New InvalidCastException("Invalid Customer Number! '" & dr(0) & "'")
      If dr(2) = "" Then Throw New InvalidCastException("Invalid Product Code! '" & dr(2) & "'")
      If dr(13).Length <> 8 Then Throw New InvalidCastException("Invalid Survey Date! '" & dr(13) & "'")

      '20191118 - added blnDisplay_Pallet, blnDisplay_PDU - maanie
      Return "INSERT INTO stgOffconSurvey (strCustomerNo, strCustomerName, strProdCode, strProductDesc, blnDistribution, dblRSP, blnDisplayBin, blnDisplayGondola, blnDisplayTill, intFacings, blnShelvingAccessible, blnShelvingPlinth, blnShelvingNone, strDateSurveyed, blnLocation_AbsolutBlue_Next_To_White_Spirits, blnLocation_AbsolutFlavours_Next_To_AbsolutBlue, blnLocation_BlackGrouse_Next_To_TFG, blnLocation_Bombay_Next_To_White_Spirits, blnLocation_FamousGrouse_Next_To_Bells, blnLocation_JSDB_Next_To_White_JackDaniels, blnLocation_SocoLiquerCategory, blnLocation_Soco_Next_To_SpicedGold, blnLocation_SocoWhiskeyCategory, blnLocation_TullamoreDew_Next_To_Jameson, blnDisplay_Pallet, blnDisplay_PDU) " &
      "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) & ", " & dr(15) & ", " & dr(16) & ", " & dr(17) & ", " & dr(18) & ", " & dr(19) &
      ", " & dr(20) & ", " & dr(21) & ", " & dr(22) & ", " & dr(23) & ", " & dr(24) & ", " & dr(25) & ")"

   End Function

End Class


'USE [Superbowl]
'GO

'/****** Object:  Table [dbo].[dwfOffconSurvey]    Script Date: 10/01/2012 11:31:05 ******/
'SET ANSI_NULLS ON
'GO

'SET QUOTED_IDENTIFIER ON
'GO

'SET ANSI_PADDING ON
'GO

'CREATE TABLE [dbo].[dwfOffconSurvey](
'	[strCustomerNo] [nvarchar](10) NOT NULL,
'	[strProdCode] [nvarchar](10) NOT NULL,
'	[strDateSurveyed] [nvarchar](10) NOT NULL,
'	[strCustomerName] [nvarchar](80) NULL,
'	[strProductDesc] [nvarchar](80) NOT NULL,
'	[blnDistribution] [bit] NULL,
'	[dblRSP] [float] NULL,
'	[blnDisplayBin] [bit] NULL,
'	[blnDisplayGondola] [bit] NULL,
'	[blnDisplayTill] [bit] NULL,
'	[intFacings] [int] NULL,
'	[blnShelvingAccessible] [bit] NULL,
'	[blnShelvingPlinth] [bit] NULL,
'	[blnShelvingNone] [bit] NULL,
'	[blnLocation_AbsolutBlue_Next_To_White_Spirits] [bit] NULL,
'	[blnLocation_AbsolutFlavours_Next_To_AbsolutBlue] [bit] NULL,
'	[blnLocation_BlackGrouse_Next_To_TFG] [bit] NULL,
'	[blnLocation_Bombay_Next_To_White_Spirits] [bit] NULL,
'	[blnLocation_FamousGrouse_Next_To_Bells] [bit] NULL,
'	[blnLocation_JSDB_Next_To_White_JackDaniels] [bit] NULL,
'	[blnLocation_SocoLiquerCategory] [bit] NULL,
'	[blnLocation_Soco_Next_To_SpicedGold] [bit] NULL,
'	[blnLocation_SocoWhiskeyCategory] [bit] NULL,
'	[blnLocation_TullamoreDew_Next_To_Jameson] [bit] NULL,
'	[strPeriod] [varchar](6) NOT NULL,
'	[blnDelete] [tinyint] NOT NULL,
' CONSTRAINT [PK_dwfOffconSurvey] PRIMARY KEY CLUSTERED 
'(
'	[strCustomerNo] ASC,
'	[strProdCode] 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

'ALTER TABLE [dbo].[dwfOffconSurvey] ADD  CONSTRAINT [DF_dwfOffconSurvey_blnDelete]  DEFAULT ((0)) FOR [blnDelete]
'GO