' 20170609 - v2.1.5 - added daily vendor import - 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 VendorImport_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 log
      '2.1 clear stg
      '2.2 import from View
      '3.1 log duplicate
      '3.2 insert/update stg=>dw

      '1 download & log
      Me.LogProcessStart()

      '2.1 clear stg
      '2.2 import from View

      Me.ClearStagingTable()
      Me.ImportView()

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

      '3.1 log duplicate
      Me.LogDuplicates()
     
      '3.2 insert/update stg=>mst
      Me.ProcessMST()

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

      Me.LogProcessCompleted()

   End Sub

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

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

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








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

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

      dr(1) = CleanString(dr(1))
      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(8) = CleanString(dr(8))
      dr(9) = CleanString(dr(9))
      dr(10) = CleanString(dr(10))
      dr(11) = CleanString(dr(11))

      If dr(1) = "" Then Throw New InvalidCastException("Invalid Vendor ID! '" & dr(1) & "'")

      Return "INSERT INTO stgVendor (VendorID, strVendor, strAddress1, strAddress2, strAddress3, strAddress4, strCity, strContact, strPhone, strEmail, strCell) " & _
      "VALUES ('" & 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 Overrides Function sqlInsertStaging() As String
      Dim dr As DataRow

      'get stg insert query
      dr = db.getRow("SELECT * FROM sysQuery WHERE ID = 53")

      Return dr!memQuery

   End Function


End Class

'USE [Superbowl]
'GO

'/****** Object:  Table [dbo].[mstVendor]    Script Date: 2017/06/09 10:46:53 AM ******/
'SET ANSI_NULLS ON
'GO

'SET QUOTED_IDENTIFIER ON
'GO

'SET ANSI_PADDING ON
'GO

'CREATE TABLE [dbo].[mstVendor](
'	[VendorID] [int] NOT NULL,
'	[strVendor] [varchar](50) NOT NULL,
'	[strAddress1] [varchar](50) NULL,
'	[strAddress2] [varchar](50) NULL,
'	[strAddress3] [varchar](50) NULL,
'	[strAddress4] [varchar](50) NULL,
'	[strCity] [varchar](50) NULL,
'	[strContact] [varchar](50) NULL,
'	[strPhone] [varchar](50) NULL,
'	[strEmail] [varchar](50) NULL,
'	[strCell] [varchar](50) NULL,
'	[strBankName] [varchar](50) NULL,
'	[strBankAccountNo] [varchar](50) NULL,
'	[strBankBranchCode] [varchar](50) NULL,
'	[strBankAccountType] [varchar](50) NULL,
'	[blnActive] [bit] NOT NULL,
'	[strLastUser] [varchar](50) NOT NULL,
'	[dtLastEdit] [datetime] NOT NULL,
' CONSTRAINT [PK_mstVendor] PRIMARY KEY CLUSTERED 
'(
'	[VendorID] ASC
')WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
' CONSTRAINT [UX_mstVendor] UNIQUE NONCLUSTERED 
'(
'	[strVendor] 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].[mstVendor] ADD  CONSTRAINT [DF_mstVendor_blnActive]  DEFAULT ((1)) FOR [blnActive]
'GO

'ALTER TABLE [dbo].[mstVendor] ADD  CONSTRAINT [DF_mstVendor_strLastUser]  DEFAULT ('System') FOR [strLastUser]
'GO

'ALTER TABLE [dbo].[mstVendor] ADD  CONSTRAINT [DF_mstVendor_dtLastEdit]  DEFAULT (getdate()) FOR [dtLastEdit]
'GO