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 RepCalls_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(strDate,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 = 29")
      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 = 30")
      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(1) = CleanString(dr(1))
      dr(3) = CleanString(dr(3))
      dr(11) = CleanString(dr(11))
      dr(12) = CleanString(dr(12))

      If dr(0).Length <> 8 Then Throw New InvalidCastException("Invalid Date! '" & dr(0) & "'")
      If dr(2) = "" Then Throw New InvalidCastException("Invalid Rep ID! '" & dr(2) & "'")

      Return "INSERT INTO stgRepCalls(strDate, strRepName, RepID, strRegionDesc2, intAppointmentScheduled, intAppointmentOpen, intAppointmentCompleted, intAppointmentCompletedNoAccount, intAppointmentCancelled, intOutOfOfficeScheduled, intSpecialActivityScheduled, memOutOfOfficeDetails, memSpecialActivityDetails, intSurveyOn, intSurveyOff, intOrders) " & _
      "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) & ")"

   End Function


End Class

'USE [Superbowl]
'GO

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

'SET QUOTED_IDENTIFIER ON
'GO

'SET ANSI_PADDING ON
'GO

'CREATE TABLE [dbo].[dwfRepCalls](
'	[strDate] [nvarchar](10) NOT NULL,
'	[RepID] [nvarchar](10) NOT NULL,
'	[strRepName] [nvarchar](50) NULL,
'	[strRegionDesc2] [nvarchar](50) NULL,
'	[intAppointmentScheduled] [int] NULL,
'	[intAppointmentOpen] [int] NULL,
'	[intAppointmentCompleted] [int] NULL,
'	[intAppointmentCompletedNoAccount] [int] NULL,
'	[intAppointmentCancelled] [int] NULL,
'	[intOutOfOfficeScheduled] [int] NULL,
'	[memOutOfOfficeDetails] [text] NULL,
'	[intSpecialActivityScheduled] [int] NULL,
'	[memSpecialActivityDetails] [text] NULL,
'	[intSurveyOn] [int] NULL,
'	[intSurveyOff] [int] NULL,
'	[intOrders] [int] NULL,
'	[strPeriod] [varchar](6) NOT NULL,
'	[blnDelete] [tinyint] NOT NULL,
' CONSTRAINT [PK_dwfRepCalls] PRIMARY KEY CLUSTERED 
'(
'	[strDate] ASC,
'	[RepID] ASC
')WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
') ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

'GO

'SET ANSI_PADDING OFF
'GO

'ALTER TABLE [dbo].[dwfRepCalls] ADD  CONSTRAINT [DF_dwfRepCalls_intAppointmentScheduled]  DEFAULT ((0)) FOR [intAppointmentScheduled]
'GO

'ALTER TABLE [dbo].[dwfRepCalls] ADD  CONSTRAINT [DF_dwfRepCalls_intAppointmentOpen]  DEFAULT ((0)) FOR [intAppointmentOpen]
'GO

'ALTER TABLE [dbo].[dwfRepCalls] ADD  CONSTRAINT [DF_dwfRepCalls_intAppointmentCompleted]  DEFAULT ((0)) FOR [intAppointmentCompleted]
'GO

'ALTER TABLE [dbo].[dwfRepCalls] ADD  CONSTRAINT [DF_dwfRepCalls_intAppointmentCompletedNoAccount]  DEFAULT ((0)) FOR [intAppointmentCompletedNoAccount]
'GO

'ALTER TABLE [dbo].[dwfRepCalls] ADD  CONSTRAINT [DF_dwfRepCalls_intAppointmentCancelled]  DEFAULT ((0)) FOR [intAppointmentCancelled]
'GO

'ALTER TABLE [dbo].[dwfRepCalls] ADD  CONSTRAINT [DF_dwfRepCalls_intOOFScheduled]  DEFAULT ((0)) FOR [intOutOfOfficeScheduled]
'GO

'ALTER TABLE [dbo].[dwfRepCalls] ADD  CONSTRAINT [DF_dwfRepCalls_intSPEScheduled]  DEFAULT ((0)) FOR [intSpecialActivityScheduled]
'GO

'ALTER TABLE [dbo].[dwfRepCalls] ADD  CONSTRAINT [DF_dwfRepCalls_intSurveyOn]  DEFAULT ((0)) FOR [intSurveyOn]
'GO

'ALTER TABLE [dbo].[dwfRepCalls] ADD  CONSTRAINT [DF_dwfRepCalls_intSurveyOff]  DEFAULT ((0)) FOR [intSurveyOff]
'GO

'ALTER TABLE [dbo].[dwfRepCalls] ADD  CONSTRAINT [DF_dwfRepCalls_intOrders]  DEFAULT ((0)) FOR [intOrders]
'GO

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