' 20180313 - v2.1.6 - added daily GL Account Code 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 GLCodeImport_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 = 50")
      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 = 51")
      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))

      If dr(1) = "" Then Throw New InvalidCastException("Invalid GL Code! '" & dr(1) & "'")

      Return "INSERT INTO stgMainFullGLCode (MainFullGLCode, strMainFullGLCode) " & _
      "VALUES ('" & dr(1) & "', '" & dr(2) & "')"

   End Function


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

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

      Return dr!memQuery

   End Function

End Class

'USE [Superbowl]
'GO

'/****** Object:  Table [dbo].[mstMainFullGLCode]    Script Date: 2018/03/14 01:35:49 PM ******/
'SET ANSI_NULLS ON
'GO

'SET QUOTED_IDENTIFIER ON
'GO

'SET ANSI_PADDING ON
'GO

'CREATE TABLE [dbo].[mstMainFullGLCode](
'	[ID] [int] IDENTITY(1,1) NOT NULL,
'	[MainFullGLCode] [varchar](50) NOT NULL,
'	[strMainFullGLCode] [varchar](100) NOT NULL,
'	[blnActive] [bit] NOT NULL,
'	[strLastUser] [varchar](50) NOT NULL,
'	[dtLastEdit] [datetime] NOT NULL,
' CONSTRAINT [PK_mstMainFullGLCode1] PRIMARY KEY CLUSTERED 
'(
'	[ID] ASC
')WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
') ON [PRIMARY]

'GO

'SET ANSI_PADDING OFF
'GO

'ALTER TABLE [dbo].[mstMainFullGLCode] ADD  CONSTRAINT [DF_mstMainFullGLCode1_blnActive]  DEFAULT ((1)) FOR [blnActive]
'GO

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

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