'20140509 - v2.1.1 - Changes to On/OffconPromo import files and DB - maanie 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 OnconPromo_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(strDatePromo,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 ProcessDW() Dim dr As DataRow Dim xdb As New db 'update stg <=> dwf dr = db.getRow("SELECT * FROM sysQuery WHERE ID = 27") 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 = 28") 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 *** '************************ Public Overrides Function sqlInsertStaging(dr As String()) As String 'dr(0) = CleanString(dr(0)) 'dr(2) = CleanString(dr(2)) 'dr(3) = CleanString(dr(3)) 'dr(5) = CleanString(dr(5)) 'dr(6) = CleanString(dr(6)) 'dr(7) = CleanString(dr(7)) 'dr(10) = CleanString(dr(10)) 'dr(13) = CleanString(dr(13)) 'If dr(1) = "" Then Throw New InvalidCastException("Invalid Customer Number! '" & dr(1) & "'") 'If dr(3) = "" Then Throw New InvalidCastException("Invalid Brand! '" & dr(3) & "'") 'If dr(13).Length <> 8 Then Throw New InvalidCastException("Invalid Promotion Date! '" & dr(13) & "'") ' Return "INSERT INTO stgOnconPromo (strCustomerName, strCustomerNo, strRegionDesc2, strBrand, intTots, strType, strPromoKit, intPromoKit, dblRSP, intDataCards, strResponseBranding, strDisplayBar, strPOS, strDatePromo) " & _ '"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(0) = CleanString(dr(0)) dr(1) = CleanString(dr(1)) dr(3) = CleanString(dr(3)) dr(4) = CleanString(dr(4)) dr(17) = CleanString(dr(17)) dr(18) = CleanString(dr(18)) If dr(2) = "" Then Throw New InvalidCastException("Invalid Customer Number! '" & dr(2) & "'") If dr(4) = "" Then Throw New InvalidCastException("Invalid Brand! '" & dr(4) & "'") If dr(5) = "" Then Throw New InvalidCastException("Invalid Record ID! '" & dr(5) & "'") If dr(6).Length <> 8 Then Throw New InvalidCastException("Invalid Promotion Date! '" & dr(6) & "'") ' 20200206 - Oncon Promo - removed 'what helped you sell' column from import process - Alex Return "INSERT INTO stgOnconPromo (strProvince, strUserName, strEntityCode, strEntityName, strItemName, strRecordID, strDatePromo, strStartTime, strEndTime, strPromotionalPOS, strBackOfBarDisplay, " & "intNoOfPromoters, strKitsUsed, intKits, dblTotPrice, dblBottlePrice, intStockSold, strResponseBranding, strNoAttended, intBottlesSold) " & "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) & "')" End Function End Class 'USE [Superbowl] 'GO '/****** Object: Table [dbo].[dwfOnconPromo] Script Date: 2014/05/09 10:55:54 AM ******/ 'SET ANSI_NULLS ON 'GO 'SET QUOTED_IDENTIFIER ON 'GO 'SET ANSI_PADDING ON 'GO 'CREATE TABLE [dbo].[dwfOnconPromo]( ' [strCustomerNo] [nvarchar](10) NOT NULL, ' [strBrand] [nvarchar](30) NOT NULL, ' [strDatePromo] [nvarchar](10) NOT NULL, ' [strCustomerName] [nvarchar](80) NULL, ' [strRegionDesc2] [nvarchar](50) NULL, ' [intTots] [int] NULL, ' [strType] [nvarchar](50) NULL, ' [strPromoKit] [nvarchar](50) NULL, ' [intPromoKit] [int] NULL, ' [dblRSP] [float] NULL, ' [intDataCards] [int] NULL, ' [strResponseBranding] [nvarchar](50) NULL, ' [strDisplayBar] [nvarchar](50) NULL, ' [strPOS] [nvarchar](50) NULL, ' [strPeriod] [varchar](6) NULL, ' [blnDelete] [tinyint] NOT NULL, ' CONSTRAINT [PK_dwfOnconPromo] PRIMARY KEY CLUSTERED '( ' [strCustomerNo] ASC, ' [strBrand] ASC, ' [strDatePromo] 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].[dwfOnconPromo] ADD CONSTRAINT [DF_dwfOnconPromo_intTots] DEFAULT ((0)) FOR [intTots] 'GO 'ALTER TABLE [dbo].[dwfOnconPromo] ADD CONSTRAINT [DF_dwfOnconPromo_intPromoKit] DEFAULT ((0)) FOR [intPromoKit] 'GO 'ALTER TABLE [dbo].[dwfOnconPromo] ADD CONSTRAINT [DF_dwfOnconPromo_dblRSP] DEFAULT ((0.0)) FOR [dblRSP] 'GO 'ALTER TABLE [dbo].[dwfOnconPromo] ADD CONSTRAINT [DF_dwfOnconPromo_intDataCards] DEFAULT ((0)) FOR [intDataCards] 'GO 'ALTER TABLE [dbo].[dwfOnconPromo] ADD CONSTRAINT [DF_dwfOnconPromo_blnDelete] DEFAULT ((0)) FOR [blnDelete] 'GO