''COPIED * FROM SC IMPORT.ASPX.VB
'Imports System.IO
'Imports System.Web.Configuration
'Partial Public Class Import
' Inherits System.Web.UI.Page
' Private Shared strPathSource As String
' Private Shared strPathDest As String
' Private Shared arrO As New ArrayList(1)
' Private Shared arrF As New ArrayList(1)
' Private Shared arrR As New ArrayList(1)
' Private Shared arrDR As New ArrayList(1)
' Private Shared arrD As New ArrayList(1)
' Private Shared arrRPK As New ArrayList(1)
' Private Shared arrCRep As New ArrayList(1)
' Private Shared msg As String
' Private Shared msgO As String
' Private Shared msgF As String
' Private Shared msgR As String
' Private Shared msgDR As String
' Private Shared msgD As String
' Private Shared msgRPK As String
' Private Shared msgCognos14 As String
' Private Shared msgCognos24 As String
' Private Shared msgCRep As String
' Private dtProductPeriod As DataTable
' Private blnForecast As Boolean = False
' ' Dim arrFiles As String() = {"Opening", "Forecast", "Receipts", "Damages"}
' Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
' If Not Page.IsPostBack Then
' MyLoadPage()
' End If
' End Sub
' Private Sub MyLoadPage()
' msg = ""
' msgO = ""
' msgF = ""
' msgR = ""
' msgDR = ""
' msgD = ""
' msgRPK = ""
' msgCognos14 = ""
' msgCognos24 = ""
' msgCRep = ""
' strPathSource = System.AppDomain.CurrentDomain.BaseDirectory()
' strPathDest = strPathSource & "Imported"
' strPathSource = strPathSource & "Imports"
' lblHeading.Text = "Files Found to be Imported"
' lblPath.Text = "Files to be Imported : " & strPathSource & " Files after Import : " & strPathDest & _
' " File Names: " & _
' "Opening : 'OpeningYYYYMM.xls' (eg, 'Opening200807.xls') " & _
' "Forecast: 'ForecastYYYYMM.xls' (eg, 'Forecast200807.xls') " & _
' "Direct Receipts: 'Direct ReceiptsYYYYMM.xls' (eg, 'Direct Receipts200807.xls') " & _
' "Customer Reps: 'CustomerReps.xls' (eg, 'CustomerReps.xls') " & _
' " Worksheet name must be 'Import'"
' '"Receipts: 'ReceiptsYYYYMM.xls' (eg, 'Receipts200807.xls') " & _
' '"Damages: 'DamagesYYYYMM.xls' (eg, 'Damages200807.xls') " & _
' '"Repack: 'RepackYYYYMM.xls' (eg, 'Repack200807.xls') " & _
' Try
' msg = ""
' arrO.Clear()
' arrF.Clear()
' arrR.Clear()
' arrDR.Clear()
' arrD.Clear()
' arrRPK.Clear()
' arrCRep.Clear()
' If (System.IO.Directory.Exists(strPathSource)) Then
' If (System.IO.Directory.Exists(strPathDest)) Then
' Dim dirsource As New System.IO.DirectoryInfo(strPathSource)
' Dim f As System.IO.FileInfo
' If dirsource.GetFiles.Count > 0 Then
' msg = ""
' For Each f In dirsource.GetFiles("*.xls")
' CheckValidFile(f.FullName.Substring(strPathSource.Length + 1))
' Next
' For Each f In dirsource.GetFiles("*.txt")
' CheckValidFileTXT(f.FullName.Substring(strPathSource.Length + 1))
' Next
' Else
' msg = "No files found in folder : " & strPathSource
' End If
' Else
' msg = "Folder not found : " & strPathDest
' End If
' Else
' msg = "Folder not found : " & strPathSource
' End If
' If msg.Length = 0 Then
' msg &= "
"
' msg &= "
Opening
"
' msg &= msgO
' msg &= "
"
' msg &= "
Forecast
"
' msg &= msgF
' msg &= "
"
' msg &= "
Direct Receipts
"
' msg &= msgDR
' msg &= "
"
' 'msg &= "
Receipts
"
' 'msg &= msgR
' 'msg &= "
"
' 'msg &= "
Damages
"
' 'msg &= msgD
' 'msg &= "
"
' 'msg &= "
Repack
"
' 'msg &= msgRPK
' 'msg &= "
"
' msg &= "
Cognos 14
"
' msg &= msgCognos14
' msg &= "
"
' msg &= "
Customer Reps
"
' msg &= msgCRep
' msg &= "
"
' 'msg &= "
Cognos 24
"
' 'msg &= msgCognos24
' msg &= "
"
' End If
' Catch ex As Exception
' lblTable.Text = ex.Message
' End Try
' lblTable.Text &= msg
' End Sub
' Private Sub CheckValidFile(ByVal sFileName As String)
' If sFileName.Length > 13 Then
' If sFileName.Substring(0, 7) = "Opening" Then
' If CheckValidPeriod(sFileName.Substring(7, 6)) Then
' CheckValidFile2(sFileName, 1)
' End If
' End If
' If sFileName.Substring(0, 8) = "Forecast" Then
' If CheckValidPeriod(sFileName.Substring(8, 6)) Then
' CheckValidFile2(sFileName, 2)
' End If
' End If
' If sFileName.Substring(0, 15) = "Direct Receipts" Then
' If CheckValidPeriod(sFileName.Substring(15, 6)) Then
' CheckValidFile2(sFileName, 6)
' End If
' End If
' If sFileName.Substring(0, 12) = "CustomerReps" Then
' CheckValidFile2(sFileName, 7)
' End If
' 'If sFileName.Substring(0, 8) = "Receipts" Then
' ' If CheckValidPeriod(sFileName.Substring(8, 6)) Then
' ' CheckValidFile2(sFileName, 3)
' ' End If
' 'End If
' 'If sFileName.Substring(0, 7) = "Damages" Then
' ' If CheckValidPeriod(sFileName.Substring(7, 6)) Then
' ' CheckValidFile2(sFileName, 4)
' ' End If
' 'End If
' 'If sFileName.Substring(0, 6) = "Repack" Then
' ' If CheckValidPeriod(sFileName.Substring(6, 6)) Then
' ' CheckValidFile2(sFileName, 5)
' ' End If
' 'End If
' End If
' End Sub
' Private Sub CheckValidFileTXT(ByVal sFileName As String)
' If sFileName = "cognos14.txt" Then
' CheckValidCognos(sFileName)
' End If
' 'If sFileName = "cognos24.txt" Then
' ' CheckValidCognos(sFileName)
' 'End If
' End Sub
' Private Function CheckValidPeriod(ByVal pPeriod) As Boolean
' Dim x As Integer
' x = sqlDB.countRows("SELECT * FROM mstPeriod WHERE strPeriod = '" & pPeriod & "' ")
' If x > 0 Then
' Return True
' Else
' Return False
' End If
' End Function
' Public Function CheckColumns(ByVal xds As DataSet, ByVal intTarget As Integer) As Boolean
' Dim i, intColumns As Integer
' For i = 0 To xds.Tables(0).Columns.Count - 1
' Try
' If xds.Tables(0).Rows(0).Item(i).ToString <> "" Then
' intColumns += 1
' End If
' 'Catch ex As inva
' Catch ex As InvalidCastException
' Catch ex As Exception
' End Try
' Next
' If intTarget = intColumns Then
' Return True
' End If
' Return False
' End Function
' Private Sub CheckValidFile2(ByVal sFileName As String, ByVal pType As Integer)
' Dim i As Integer
' Dim x As Integer
' Dim NoColumns As Integer = 0
' Dim xds As DataSet
' i = 0
' xds = ConnectExcel(sFileName)
' If xds Is Nothing Then
' Else
' If xds.Tables(0).Rows.Count > 0 Then
' Select Case pType 'add filename to various arrays and items to strings
' Case 1
' NoColumns = 3
' If CheckColumns(xds, NoColumns) Then
' arrO.Add(sFileName)
' msgO &= "
" & sFileName & "
"
' msgO &= "
"
' For x = 0 To NoColumns - 1
' msgO &= "
" & xds.Tables(0).Columns(x).ColumnName & "
"
' Next
' msgO &= "
"
' Else
' xds.Clear()
' Exit Sub
' End If
' Case 2
' NoColumns = 13
' If CheckColumns(xds, NoColumns) Then
' arrF.Add(sFileName)
' msgF &= "
" & sFileName & "
"
' msgF &= "
"
' For x = 0 To NoColumns - 1
' msgF &= "
" & xds.Tables(0).Columns(x).ColumnName & "
"
' Next
' msgF &= "
"
' Else
' xds.Clear()
' Exit Sub
' End If
' Case 3
' NoColumns = 9
' If CheckColumns(xds, NoColumns) Then
' arrR.Add(sFileName)
' msgR &= "
" & sFileName & "
"
' msgR &= "
"
' For x = 0 To NoColumns - 1
' msgR &= "
" & xds.Tables(0).Columns(x).ColumnName & "
"
' Next
' msgR &= "
"
' Else
' xds.Clear()
' Exit Sub
' End If
' Case 4
' NoColumns = 8
' If CheckColumns(xds, NoColumns) Then
' arrD.Add(sFileName)
' msgD &= "
" & sFileName & "
"
' msgD &= "
"
' For x = 0 To NoColumns - 1
' msgD &= "
" & xds.Tables(0).Columns(x).ColumnName & "
"
' Next
' msgD &= "
"
' Else
' xds.Clear()
' Exit Sub
' End If
' Case 5 'repack 20081030
' NoColumns = 9
' If CheckColumns(xds, NoColumns) Or CheckColumns(xds, NoColumns - 1) Then
' arrRPK.Add(sFileName)
' msgRPK &= "
" & sFileName & "
"
' msgRPK &= "
"
' For x = 0 To NoColumns - 1
' msgRPK &= "
" & xds.Tables(0).Columns(x).ColumnName & "
"
' Next
' msgRPK &= "
"
' Else
' xds.Clear()
' Exit Sub
' End If
' Case 6 'Direct Receipts 20090218
' NoColumns = 5
' If CheckColumns(xds, NoColumns) Then
' arrDR.Add(sFileName)
' msgDR &= "
" & sFileName & "
"
' msgDR &= "
"
' For x = 0 To NoColumns - 1
' msgDR &= "
" & xds.Tables(0).Columns(x).ColumnName & "
"
' Next
' msgDR &= "
"
' Else
' xds.Clear()
' Exit Sub
' End If
' Case 7 'CustomerReps 20090324
' NoColumns = 5
' If CheckColumns(xds, NoColumns) Then
' arrCRep.Add(sFileName)
' msgCRep &= "
" & sFileName & "
"
' msgCRep &= "
"
' For x = 0 To NoColumns - 1
' msgCRep &= "
" & xds.Tables(0).Columns(x).ColumnName & "
"
' Next
' msgCRep &= "
"
' Else
' xds.Clear()
' Exit Sub
' End If
' End Select
' For Each xdr As DataRow In xds.Tables(0).Rows 'output first 3 records from spreadsheet
' Try
' i = i + 1
' Select Case pType
' Case 1
' msgO &= "
"
' For x = 0 To NoColumns - 1
' msgO &= "
" & xdr.Item(x) & "
"
' Next
' msgO &= "
"
' Case 2
' msgF &= "
"
' For x = 0 To NoColumns - 1
' If x > 1 Then
' msgF &= "
"
' End Select
' If i >= 3 Then
' Exit For
' End If
' Catch ex As Exception
' End Try
' Next
' Select Case pType 'add spaces to end
' Case 1
' msgO &= "
"
' Case 2
' msgF &= "
"
' Case 3
' msgR &= "
"
' Case 4
' msgD &= "
"
' Case 5
' msgRPK &= "
"
' Case 6
' msgDR &= "
"
' Case 7
' msgCRep &= "
"
' End Select
' End If
' xds.Clear()
' End If
' End Sub
' Private Sub CheckValidCognos(ByVal sFileName As String)
' Dim xds As DataSet
' Dim NoColumns As Integer = 0
' Dim s, strCognos As String
' Try
' strCognos = ""
' 'NoColumns = 16: Cognos24, = 17: Cognos14
' xds = ConnectTxt(sFileName)
' If xds Is Nothing Then
' Else
' If (xds.Tables(0).Columns.Count = 16) _
' Or (xds.Tables(0).Columns.Count = 17) Then
' strCognos &= "
"
' Dim i As Integer = 0
' For Each xdr As DataRow In xds.Tables(0).Rows
' i = i + 1
' strCognos &= "
"
' For x = 0 To xds.Tables(0).Columns.Count - 1
' strCognos &= "
" & xdr.Item(x) & "
"
' Next
' strCognos &= "
"
' If i >= 3 Then
' Exit For
' End If
' Next
' strCognos &= "
"
' End If
' End If
' If sFileName = "cognos14.txt" Then
' s = xds.Tables(0).Rows(0).Item(5)
' strCognos = "
" & sFileName & " : " & s.Substring(0, 6) & "
" & _
' strCognos
' msgCognos14 = strCognos
' End If
' If sFileName = "cognos24.txt" Then
' s = xds.Tables(0).Rows(0).Item(4)
' strCognos = "
" & sFileName & " : " & s.Substring(0, 6) & "
" & _
' strCognos
' msgCognos24 = strCognos
' End If
' Catch ex As Exception
' Finally
' xds.Clear()
' xds = Nothing
' End Try
' End Sub
' Private Function ConnectExcel(ByVal sFileName As String) As DataSet
' Dim conn As System.Data.OleDb.OleDbConnection
' Dim da As System.Data.OleDb.OleDbDataAdapter
' Dim xds As DataSet
' Try
' conn = New System.Data.OleDb.OleDbConnection( _
' "provider=Microsoft.Jet.OLEDB.4.0; " & _
' "data source=" & strPathSource & "\" & sFileName & "; " & _
' "Extended Properties=Excel 8.0")
' ' Select the data from sheet [import$] of the workbook.
' da = New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [Import$]", conn)
' xds = New System.Data.DataSet
' da.Fill(xds)
' Return xds
' Catch ex As Exception
' End Try
' End Function
' Private Function ConnectTxt(ByVal sFileName As String) As DataSet
' Dim conn As System.Data.OleDb.OleDbConnection
' Dim da As System.Data.OleDb.OleDbDataAdapter
' Dim xds As DataSet
' Try
' conn = New System.Data.OleDb.OleDbConnection( _
' "provider=Microsoft.Jet.OLEDB.4.0; " & _
' "data source=" & strPathSource & "\; " & _
' "Extended Properties='text;HDR=No;FMT=Delimited;'")
' da = New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM " & sFileName & " ", conn)
' xds = New System.Data.DataSet
' da.Fill(xds)
' da.Dispose()
' da = Nothing
' Return xds
' Catch ex As Exception
' End Try
' End Function
' Protected Sub btnImport_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnImport.Click
' Dim sNotImported, strPeriodsIN As String
' Dim i, x, y, z, cnt As Integer
' Dim strFail, lPeriod, lPeriod2, StartPeriod, s As String
' Dim ds, xds As DataSet
' Dim db As New sqlDB
' Dim intTotals, intInsert, intUpdate, intFail As Integer
' Dim dVal As Double
' Dim curDateTime As String
' blnForecast = False
' msg = ""
' lblHeading.Text = "Files Processed"
' lblTable.Text = ""
' sNotImported = ""
' StartPeriod = Date.Today.ToString("yyyyMM")
' ' load PP
' i = 0
' ds = db.doQueryDS("SELECT left(strProdCode,5) AS ProdCode FROM mstProduct GROUP BY left(strProdCode,5) ORDER BY left(strProdCode,5) ")
' Dim arrMP(ds.Tables(0).Rows.Count) As Integer
' For Each dr As DataRow In ds.Tables(0).Rows
' arrMP(i) = CInt(dr!ProdCode)
' i += 1
' Next
' ds.Clear()
' i = 0
' ds = db.doQueryDS("SELECT mstProdCode, mstPeriod FROM stkProductPeriod ORDER BY mstProdCode, mstPeriod")
' Dim arrPP(1, ds.Tables(0).Rows.Count) As Integer
' cnt = ds.Tables(0).Rows.Count
' For Each dr As DataRow In ds.Tables(0).Rows
' arrPP(0, i) = CInt(dr!mstProdCode)
' arrPP(1, i) = CInt(dr!mstPeriod)
' i += 1
' Next
' ds.Clear()
' curDateTime = Date.Now.ToString("yyyyMMddHHmm")
' '****NOTES ***** insert is currently commented out and then fails due to arrPP add so item is added to the fail string
' For x = 0 To arrO.Count - 1 '*** opening
' If Request.Form(arrO(x)) = "1" Then
' intInsert = 0
' intUpdate = 0
' intFail = 0
' i = 0
' strFail = ""
' Try
' xds = ConnectExcel(arrO(x))
' intTotals = xds.Tables(0).Rows.Count
' lPeriod = arrO(x).Substring(7, 6)
' If lPeriod < StartPeriod Then
' StartPeriod = lPeriod
' End If
' UpdateLog(lPeriod, "blnOpening")
' msg &= " " & arrO(x) & " "
' db.doQueryDS("UPDATE stkProductPeriod SET dblOpeningImports = 0 WHERE mstPeriod = '" & lPeriod & "' ")
' For Each xdr As DataRow In xds.Tables(0).Rows
' Try
' i += 1
' If findMP(arrMP, xdr.Item(0)) Then
' 'new test to multiple SoCo essential oils by 9 to get the amount of litres it can make
' dVal = Math.Round(CDbl(sqlDB.nz(xdr.Item(2), 0)), 4, MidpointRounding.AwayFromZero)
' If ProdCode5Chars(xdr.Item(0)) = "05016" Then
' dVal = dVal * 9
' End If
' If Not findPP(arrPP, xdr.Item(0), lPeriod) Then
' db.doQueryDS(db.sql_insert_PP(xdr.Item(0).ToString.Substring(0, 5), lPeriod, dVal, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, dVal))
' intInsert += 1
' 'add new product to array
' cnt += 1
' ReDim Preserve arrPP(1, cnt)
' arrPP(0, cnt - 1) = CInt(xdr.Item(0))
' arrPP(1, cnt - 1) = CInt(lPeriod)
' Else
' db.doQueryDS(db.sql_Update_PP(xdr.Item(0).ToString.Substring(0, 5), lPeriod, "dblOpening", dVal))
' db.doQueryDS(db.sql_Update_PP(xdr.Item(0).ToString.Substring(0, 5), lPeriod, "dblOpeningImports", dVal))
' intUpdate += 1
' End If
' Else
' arrPP(1, arrPP.Length) = CInt(lPeriod)
' End If
' Catch ex As Exception
' s = ""
' intFail += 1
' strFail &= " " & i & " """
' For Each item In xdr.ItemArray
' strFail &= s & item
' s = " _ "
' Next
' strFail &= """ "
' End Try
' Next
' msg &= "Total Records: " & intTotals & " New Products Periods: " & intInsert & _
' " Existing Product Periods: " & intUpdate & " Records Failed: " & intFail & strFail & " "
' Catch ex As Exception
' End Try
' If intFail = 0 Then
'#If DEBUG Then
' File.Copy(strPathSource & "\" & arrO(x), strPathDest & "\" & arrO(x) & curDateTime)
'#Else
' File.Move(strPathSource & "\" & arrO(x), strPathDest & "\" & arrO(x) & curDateTime)
'#End If
' End If
' Else
' sNotImported &= arrO(x) & " "
' End If
' Next
' For x = 0 To arrF.Count - 1 '*** forecast
' If Request.Form(arrF(x)) = "1" Then
' blnForecast = True
' intInsert = 0
' intUpdate = 0
' intFail = 0
' i = 0
' strFail = ""
' Try
' 'Forecast is different as has 12 month forecast
' ' 0 1 2 3 4 5 6 7 .. 12 13
' 'Item Extended Description 1 Jun-08 Jul-08 Aug-08 Sep-08 Oct-08 Nov-08 Dec-08 Jan-09 Feb-09 Mar-09 Apr-09 May-09
' xds = ConnectExcel(arrF(x))
' intTotals = xds.Tables(0).Rows.Count
' lPeriod = arrF(x).Substring(8, 6)
' If lPeriod < StartPeriod Then
' StartPeriod = lPeriod
' End If
' UpdateLog(lPeriod, "blnForecast")
' msg &= " " & arrF(x) & " "
' db.doQueryDS("UPDATE stkProductPeriod SET dblQF = 0 WHERE mstPeriod > '" & lPeriod & "' ")
' For Each xdr As DataRow In xds.Tables(0).Rows
' For y = 1 To 11
' Try
' lPeriod2 = GetPeriod(lPeriod, y)
' i += 1
' If findMP(arrMP, xdr.Item(0)) Then
' If Not findPP(arrPP, xdr.Item(0), lPeriod2) Then
' db.doQueryDS(db.sql_insert_PP(ProdCode5Chars(xdr.Item(0)), lPeriod2, 0, Math.Round(CDbl(sqlDB.nz(xdr.Item(1 + y), 0)), 0, MidpointRounding.AwayFromZero), 0, 0, 0, 0, 0, 0, 0, 0, 0, 0))
' intInsert += 1
' 'add new product to array
' cnt += 1
' ReDim Preserve arrPP(1, cnt)
' arrPP(0, cnt - 1) = CInt(xdr.Item(0))
' arrPP(1, cnt - 1) = CInt(lPeriod)
' Else
' db.doQueryDS(db.sql_Update_PP(ProdCode5Chars(xdr.Item(0)), lPeriod2, "dblQF", Math.Round(CDbl(sqlDB.nz(xdr.Item(1 + y), 0)), 0, MidpointRounding.AwayFromZero)))
' intUpdate += 1
' End If
' Else
' arrPP(1, arrPP.Length) = CInt(lPeriod)
' End If
' Catch ex As Exception
' s = ""
' intFail += 1
' strFail &= " " & i & " """
' For Each item In xdr.ItemArray
' strFail &= s & item
' s = " _ "
' Next
' strFail &= """ "
' End Try
' Next
' Next
' msg &= "Total Records: " & intTotals & " New Products Periods: " & intInsert & _
' " Existing Product Periods: " & intUpdate & " Records Failed: " & intFail & strFail & " "
' Catch ex As Exception
' End Try
' If intFail = 0 Then
'#If DEBUG Then
' File.Copy(strPathSource & "\" & arrF(x), strPathDest & "\" & arrF(x) & curDateTime)
'#Else
' File.Copy(strPathSource & "\" & arrF(x), strPathDest & "\" & arrF(x) & curDateTime)
'#End If
' Else
' sNotImported &= arrF(x) & " "
' End If
' Dim rep As New RepQF()
' Try
' If Not rep.Run(lPeriod, lPeriod2) Then
' msg &= rep.Message.Replace("||", " ")
' End If
' Catch ex As Exception
' msg &= "Rep QF Update error: " & ex.Message
' msg &= rep.Message.Replace("||", " ")
' End Try
' End If 'eoIf arrF(x)
' Next
' For x = 0 To arrR.Count - 1 '*** receipts
' If Request.Form(arrR(x)) = "1" Then
' intInsert = 0
' intUpdate = 0
' intFail = 0
' i = 0
' strFail = ""
' Try
' xds = ConnectExcel(arrR(x))
' intTotals = xds.Tables(0).Rows.Count
' lPeriod = arrR(x).Substring(8, 6)
' If lPeriod < StartPeriod Then
' StartPeriod = lPeriod
' End If
' UpdateLog(lPeriod, "blnReceipts")
' msg &= " " & arrR(x) & " "
' db.doQueryDS("UPDATE stkProductPeriod SET dblReceipts = 0, dblExports = 0 WHERE mstPeriod = '" & lPeriod & "' ")
' For Each xdr As DataRow In xds.Tables(0).Rows
' Try
' i += 1
' If findMP(arrMP, xdr.Item(0)) Then
' dVal = Math.Round(CDbl(sqlDB.nz(xdr.Item(7), 0)), 4, MidpointRounding.AwayFromZero)
' If ProdCode5Chars(xdr.Item(0)) = "05016" Then
' dVal = dVal * 9
' End If
' If Not findPP(arrPP, xdr.Item(0), lPeriod) Then
' If dVal > 0 Then
' db.doQueryDS(db.sql_insert_PP(ProdCode5Chars(xdr.Item(0)), lPeriod, 0, 0, 0, 0, 0, dVal, 0, 0, 0, 0, 0, 0))
' Else
' dVal = dVal * -1
' db.doQueryDS(db.sql_insert_PP(ProdCode5Chars(xdr.Item(0)), lPeriod, 0, 0, 0, 0, 0, 0, dVal, 0, 0, 0, 0, 0))
' End If
' intInsert += 1
' 'add new product to array
' cnt += 1
' ReDim Preserve arrPP(1, cnt)
' arrPP(0, cnt - 1) = CInt(xdr.Item(0))
' arrPP(1, cnt - 1) = CInt(lPeriod)
' Else
' If dVal > 0 Then
' db.doQueryDS(db.sql_Update_PP_Import(ProdCode5Chars(xdr.Item(0)), lPeriod, "dblReceipts", dVal))
' Else
' dVal = dVal * -1
' db.doQueryDS(db.sql_Update_PP_Import(ProdCode5Chars(xdr.Item(0)), lPeriod, "dblExports", dVal))
' End If
' intUpdate += 1
' End If
' Else
' arrPP(1, arrPP.Length) = CInt(lPeriod)
' End If
' Catch ex As Exception
' s = ""
' intFail += 1
' strFail &= " " & i & " """
' For Each item In xdr.ItemArray
' strFail &= s & item
' s = " _ "
' Next
' strFail &= """ "
' End Try
' Next
' msg &= "Total Records: " & intTotals & " New Products Periods: " & intInsert & _
' " Existing Product Periods: " & intUpdate & " Records Failed: " & intFail & strFail & " "
' Catch ex As Exception
' End Try
' If intFail = 0 Then
' If File.Exists(strPathDest & "\" & arrR(x)) Then
' File.Move(strPathSource & "\" & arrR(x), strPathDest & "\" & arrR(x) & curDateTime)
' Else
' File.Move(strPathSource & "\" & arrR(x), strPathDest & "\" & arrR(x) & curDateTime)
' End If
' End If
' Else
' sNotImported &= arrR(x) & " "
' End If
' Next
' For x = 0 To arrD.Count - 1 '*** damages
' If Request.Form(arrD(x)) = "1" Then
' intInsert = 0
' intUpdate = 0
' intFail = 0
' i = 0
' strFail = ""
' Try
' xds = ConnectExcel(arrD(x))
' intTotals = xds.Tables(0).Rows.Count
' lPeriod = arrD(x).Substring(7, 6)
' If lPeriod < StartPeriod Then
' StartPeriod = lPeriod
' End If
' UpdateLog(lPeriod, "blnDamages")
' msg &= " " & arrD(x) & " "
' db.doQueryDS("UPDATE stkProductPeriod SET dblDamages = 0 WHERE mstPeriod = '" & lPeriod & "' ")
' For Each xdr As DataRow In xds.Tables(0).Rows
' Try
' i += 1
' If findMP(arrMP, xdr.Item(0)) Then
' If Not findPP(arrPP, xdr.Item(0), lPeriod) Then
' db.doQueryDS(db.sql_insert_PP(ProdCode5Chars(xdr.Item(0)), lPeriod, 0, 0, 0, 0, 0, 0, 0, Math.Round(CDbl(sqlDB.nz(xdr.Item(6), 0)), 4, MidpointRounding.AwayFromZero), 0, 0, 0, 0))
' intInsert += 1
' 'add new product to array
' cnt += 1
' ReDim Preserve arrPP(1, cnt)
' arrPP(0, cnt - 1) = CInt(xdr.Item(0))
' arrPP(1, cnt - 1) = CInt(lPeriod)
' Else
' db.doQueryDS(db.sql_Update_PP_Import(ProdCode5Chars(xdr.Item(0)), lPeriod, "dblDamages", Math.Round(CDbl(sqlDB.nz(xdr.Item(6), 0)), 4, MidpointRounding.AwayFromZero)))
' intUpdate += 1
' End If
' Else
' arrPP(1, arrPP.Length) = CInt(lPeriod)
' End If
' Catch ex As Exception
' s = ""
' intFail += 1
' strFail &= " " & i & " """
' For Each item In xdr.ItemArray
' strFail &= s & item
' s = " _ "
' Next
' strFail &= """ "
' End Try
' Next
' msg &= "Total Records: " & intTotals & " New Products Periods: " & intInsert & _
' " Existing Product Periods: " & intUpdate & " Records Failed: " & intFail & strFail & " "
' Catch ex As Exception
' End Try
' If intFail = 0 Then
' If File.Exists(strPathDest & "\" & arrD(x)) Then
' File.Move(strPathSource & "\" & arrD(x), strPathDest & "\" & arrD(x) & curDateTime)
' Else
' File.Move(strPathSource & "\" & arrD(x), strPathDest & "\" & arrD(x) & curDateTime)
' End If
' End If
' Else
' sNotImported &= arrD(x) & " "
' End If
' Next
' For x = 0 To arrRPK.Count - 1 '*** Repack
' If Request.Form(arrRPK(x)) = "1" Then
' intInsert = 0
' intUpdate = 0
' intFail = 0
' i = 0
' strFail = ""
' Try
' xds = ConnectExcel(arrRPK(x))
' intTotals = xds.Tables(0).Rows.Count
' lPeriod = arrRPK(x).Substring(6, 6)
' If lPeriod < StartPeriod Then
' StartPeriod = lPeriod
' End If
' UpdateLog(lPeriod, "blnRepack")
' msg &= " " & arrRPK(x) & " "
' db.doQueryDS("UPDATE stkProductPeriod SET dblRepack = 0 WHERE mstPeriod = '" & lPeriod & "' ")
' For Each xdr As DataRow In xds.Tables(0).Rows
' Try
' i += 1
' If findMP(arrMP, xdr.Item(0)) Then
' If Not findPP(arrPP, xdr.Item(0), lPeriod) Then
' db.doQueryDS(db.sql_insert_PP(ProdCode5Chars(xdr.Item(0)), lPeriod, 0, 0, 0, 0, 0, 0, 0, 0, Math.Round(CDbl(sqlDB.nz(xdr.Item(7), 0)), 4, MidpointRounding.AwayFromZero), 0, 0, 0))
' intInsert += 1
' 'add new product to array
' cnt += 1
' ReDim Preserve arrPP(1, cnt)
' arrPP(0, cnt - 1) = CInt(xdr.Item(0))
' arrPP(1, cnt - 1) = CInt(lPeriod)
' Else
' db.doQueryDS(db.sql_Update_PP_Import(ProdCode5Chars(xdr.Item(0)), lPeriod, "dblRepack", Math.Round(CDbl(sqlDB.nz(xdr.Item(7), 0)), 4, MidpointRounding.AwayFromZero)))
' intUpdate += 1
' End If
' Else
' arrPP(1, arrPP.Length) = CInt(lPeriod)
' End If
' Catch ex As Exception
' s = ""
' intFail += 1
' strFail &= " " & i & " """
' For Each item In xdr.ItemArray
' strFail &= s & item
' s = " _ "
' Next
' strFail &= """ "
' End Try
' Next
' msg &= "Total Records: " & intTotals & " New Products Periods: " & intInsert & _
' " Existing Product Periods: " & intUpdate & " Records Failed: " & intFail & strFail & " "
' Catch ex As Exception
' End Try
' If intFail = 0 Then
' If File.Exists(strPathDest & "\" & arrRPK(x)) Then
' File.Move(strPathSource & "\" & arrRPK(x), strPathDest & "\" & arrRPK(x) & curDateTime)
' Else
' File.Move(strPathSource & "\" & arrRPK(x), strPathDest & "\" & arrRPK(x) & curDateTime)
' End If
' End If
' Else
' sNotImported &= arrRPK(x) & " "
' End If
' Next
' 'COGNOS 14
' If msgCognos14.Length > 0 Then
' If (Request.Form("cognos14.txt") = "1") Then
' intInsert = 0
' intUpdate = 0
' intFail = 0
' i = 0
' strFail = ""
' Try
' xds = ConnectTxt("cognos14.txt")
' intTotals = xds.Tables(0).Rows.Count
' s = xds.Tables(0).Rows(0).Item(4)
' lPeriod = s.Substring(0, 6)
' If lPeriod < StartPeriod Then StartPeriod = lPeriod
' msg &= " Cognos14 "
' db.doQueryDS("DELETE FROM stgCognos14")
' db.doQueryDS("DELETE FROM stgSalesDataDownload")
' db.doQueryDS("DELETE FROM stgSalesData")
' For Each xdr As DataRow In xds.Tables(0).Rows
' Try
' For i = 0 To xdr.ItemArray.Length - 1
' Try
' xdr(i) = db.nz(xdr(i), "")
' Catch ex As Exception
' xdr(i) = db.nz(xdr(i), 0)
' End Try
' Next
' i += 1
' If xdr(1).ToString.Length < 1 Then Throw New InvalidCastException("Invalid document number") '<> 8 Or xdr(1).ToString.Length <> 9 Then Throw New InvalidCastException("Invalid document number")
' If Double.IsNaN(xdr(2)) Then Throw New InvalidCastException("Invalid customer number")
' If xdr(5).ToString.Length < 8 Then Throw New InvalidCastException("Invalid period")
' If xdr(7).ToString.Length < 5 Then Throw New InvalidCastException("Invalid product code")
' If db.nz(xdr(0), "") = "" Then xdr(0) = "R"
' Select Case xdr(15).ToString.ToUpper
' Case "BOT", "ST"
' xdr(7) = xdr(7) & "S"
' Case "CS"
' xdr(7) = xdr(7) & "F"
' End Select
' If xdr(13).ToString.Length > 0 Then xdr(13) = CDbl(xdr(13)) 'Order Number: Removing leading zeros
' 'a1
' db.doQueryDS(db.sql_insert_stgCognos14(xdr(0), xdr(1), xdr(2), xdr(3), xdr(4), xdr(5), xdr(6), xdr(7), xdr(8), xdr(9), xdr(10), xdr(11), xdr(12), xdr(13), xdr(14), xdr(15), xdr(16), xdr(17)))
' 'db.doQueryDS(db.sql_insert_stgCognos14(xdr(0), xdr(1), xdr(2), xdr(3), xdr(4), xdr(5), xdr(6), xdr(7), xdr(8), xdr(9), xdr(10), xdr(11), xdr(12), xdr(13), xdr(14), xdr(15), xdr(16), 0))
' Catch ex As Exception
' s = ""
' intFail += 1
' strFail &= " " & i & " """
' For Each item In xdr.ItemArray
' strFail &= s & item
' s = " _ "
' Next
' strFail &= """ " & ex.Message & " "
' End Try
' Next
' Catch ex As Exception
' s = ""
' intFail += 1
' strFail &= " " & i & " " & ex.Message & " " 'eoRead
' End Try
' Dim SFile As String
' SFile = "\cognos14.txt." & Date.Now.ToString("yyyyMMddHHmmss")
' If intFail = 0 Then
' File.Move(strPathSource & "\cognos14.txt", strPathDest & SFile)
' Else
' File.Copy(strPathSource & "\cognos14.txt", strPathDest & SFile)
' End If
' 'start post processing
' strPeriodsIN = ""
' Dim comma As String = ""
' Dim dr As DataRow
' intInsert = 0
' intUpdate = 0
' intFail = 0
' i = 1
' strFail = ""
' 'get file periods (note multiple periods)
' ds = db.doQueryDS("SELECT LEFT(period, 6) AS strPeriod FROM stgCognos14 GROUP BY LEFT(period, 6) ORDER BY strPeriod")
' For Each dr In ds.Tables(0).Rows
' strPeriodsIN &= comma & "'" & dr!strPeriod & "'"
' comma = ","
' UpdateLog(dr!strPeriod, "blnCognos14")
' Next
' If strPeriodsIN.Length <> 0 Then
' 'check for reversals
' db.doQueryDS("UPDATE stgCognos14 SET units = units*-1, ninelce = ninelce*-1, nett= nett*-1, gsv = gsv*-1, dblNettEx = dblNettEx*-1 WHERE doctype IS NULL OR doctype = '' OR doctype = '-'")
' 'ini existing PP
' db.doQueryDS("UPDATE stkProductPeriod SET dblActual = 0 WHERE mstPeriod IN (" & strPeriodsIN & ") ")
' 'get existing PP
' dtProductPeriod = db.doQueryDS("SELECT mstProdCode, mstPeriod, 'True' as blnFound FROM stkProductPeriod WHERE mstPeriod IN (" & strPeriodsIN & ") ").Tables(0)
' 'get sum(_) from stgC14
' ds = db.doQueryDS("SELECT LEFT(stgCognos14.prodcode,5) AS strProdCode, LEFT(stgCognos14.period, 6) AS strPeriod, mstProduct.refBrandID, SUM(stgCognos14.units * mstProduct.dblFlat) AS dblFlat " & _
' "FROM stgCognos14 LEFT OUTER JOIN mstProduct ON stgCognos14.prodcode = mstProduct.strProdCode " & _
' "GROUP BY LEFT(stgCognos14.prodcode,5), LEFT(stgCognos14.period, 6), mstProduct.refBrandID")
' 'ds.Tables(0).Rows.Count
' 'insert/update PP + Insert into stgSDD
' For Each xdr In ds.Tables(0).Rows
' Try
' 'check if product exists
' If db.nz(xdr!refBrandID, 0) = 0 Then Throw New DataException("Missing or incomplete product information: ProdCode = '" & xdr!strProdCode & "'")
' 'check if pp exists
' dr = FindProductPeriod(xdr!strProdCode.ToString.Substring(0, 5), xdr!strPeriod)
' xdr!dblFlat = Math.Round(xdr!dblFlat, 4, MidpointRounding.AwayFromZero)
' If Not dr Is Nothing Then
' 'update
' db.doQueryDS(db.sql_Update_PP_Import(xdr!strProdCode, xdr!strPeriod, "dblActual", xdr!dblFlat))
' intUpdate += 1
' Else
' 'insert
' db.doQueryDS(db.sql_insert_PP(xdr!strProdCode, xdr!strPeriod, 0, 0, 0, xdr!dblFlat, 0, 0, 0, 0, 0, 0, 0, 0))
' intInsert += 1
' End If
' Catch ex As Exception
' s = ""
' intFail += 1
' strFail &= " " & i & " """
' For Each item In xdr.ItemArray
' strFail &= s & item
' s = " _ "
' Next
' strFail &= """ " & ex.Message & " "
' Finally
' i += 1
' End Try
' Next
' Try
' '>> strSalesDataDownload
' db.doQueryDS("INSERT INTO stgSalesDataDownload (docno, [cust code], period, [prod code], units, [net sales], gsv, dblNettEx, strDocumentType, ordernum) " & _
' "SELECT docno, cusno, LEFT(period, 6) AS strPeriod, prodcode, SUM(units) AS units, SUM(nett) AS nett, SUM(gsv) AS GSV, SUM(dblNettEx) AS dblNettEx, doctype, ordernum " & _
' "FROM stgCognos14 GROUP BY cusno, prodcode, LEFT(period, 6), docno, doctype, ordernum")
' db.doQueryDS("DELETE FROM stgSalesDataDownload WHERE (units=0) AND ([Net sales]=0)")
' '>> strSalesData
' db.doQueryDS("INSERT INTO stgSalesData(cusno, prodcode, units, netsales, period, gsv, dblNSV, dblGSV, lngUnits, dbl9l, lngUnitsFreeStock, lngUnitsTotal, dbl9lFreeStock, dbl9lTotal, docno, brand, dblNettEx, strDocumentType, ordernum) " & _
' "SELECT stgSalesDataDownload.[cust code], stgSalesDataDownload.[prod code], stgSalesDataDownload.units, stgSalesDataDownload.[net sales], " & _
' "stgSalesDataDownload.period, stgSalesDataDownload.gsv, stgSalesDataDownload.[net sales] AS Expr1, stgSalesDataDownload.gsv AS Expr2, " & _
' "stgSalesDataDownload.units AS Expr3, stgSalesDataDownload.units * mstProduct.intPack * mstProduct.intSize / 9000.0 AS ninel, 0 AS FS, " & _
' "0 AS unitsTotal, 0 AS lFS, 0.0 AS ninelTot, " & _
' "stgSalesDataDownload.docno, mstProduct.refBrandID, stgSalesDataDownload.dblNettEx, stgSalesDataDownload.strDocumentType, ordernum " & _
' "FROM mstProduct RIGHT OUTER JOIN stgSalesDataDownload ON mstProduct.strProdCode = stgSalesDataDownload.[prod code]")
' 'freestock
' db.doQueryDS("UPDATE stgSalesData SET lngUnits = 0, lngUnitsFreeStock = stgSalesData.lngUnits, dbl9l = 0, dbl9lFreeStock = stgSalesData.dbl9l " & _
' "FROM mstCustomer INNER JOIN stgSalesData ON mstCustomer.strCustomerNo = stgSalesData.ordernum " & _
' "WHERE mstCustomer.blnPromotionalAccounts = 1 AND ordernum <> cusno")
' db.doQueryDS("INSERT INTO stgSalesData (cusno, prodcode, units, netsales, period, gsv, dblNSV, dblGSV, lngUnits, dbl9l, lngUnitsFreeStock, lngUnitsTotal, dbl9lFreeStock, dbl9lTotal, docno, brand, dblNettEx, strDocumentType, ordernum) " & _
' "SELECT stgSalesData_1.ordernum, stgSalesData_1.prodcode, stgSalesData_1.units, stgSalesData_1.netsales, stgSalesData_1.period, stgSalesData_1.gsv, " & _
' "stgSalesData_1.dblNSV, stgSalesData_1.dblGSV, 0 AS units, 0 AS dbl9l, stgSalesData_1.lngUnitsFreeStock * - 1 AS fs, 0 AS lngUnitsTotal, " & _
' "stgSalesData_1.dbl9lFreeStock * - 1 AS dbl9lFreeStock, 0 AS dbl9lTotal, stgSalesData_1.docno, stgSalesData_1.brand, stgSalesData_1.dblNettEx, " & _
' "stgSalesData_1.strDocumentType, '' AS ordernum " & _
' "FROM stgSalesData AS stgSalesData_1 INNER JOIN mstCustomer ON stgSalesData_1.ordernum = mstCustomer.strCustomerNo " & _
' "WHERE mstCustomer.blnPromotionalAccounts = 1 AND ordernum <> cusno")
' '>> dwfSales
' db.doQueryDS("DELETE FROM dwfSales WHERE refPeriod IN (" & strPeriodsIN & ") AND NOT refDocumentNo LIKE ('%X%') AND NOT refDocumentNo LIKE ('%Y%')")
' db.doQueryDS("INSERT INTO dwfSales(refPeriod, refCustomerNo, refProdCode, RefDocumentNo, dblNSV, dblGSV, lngUnits, lngUnitsFreeStock, lngUnitsTotal, dbl9l, dbl9lFreeStock, dbl9lTotal, dblNettEx, strDocumentType) " & _
' "SELECT stgSalesData.period, stgSalesData.cusno, stgSalesData.prodcode, stgSalesData.docno, stgSalesData.dblNSV, stgSalesData.dblGSV, " & _
' "stgSalesData.lngUnits, stgSalesData.lngUnitsFreeStock, stgSalesData.lngUnits + stgSalesData.lngUnitsFreeStock AS dblUnitstotal, stgSalesData.dbl9l, " & _
' "stgSalesData.dbl9lFreeStock, stgSalesData.dbl9l + stgSalesData.dbl9lFreeStock AS dbl9ltotal, stgSalesData.dblNettEx, stgSalesData.strDocumentType " & _
' "FROM stgSalesData INNER JOIN mstProduct ON stgSalesData.prodcode = mstProduct.strProdCode INNER JOIN mstCustomer ON stgSalesData.cusno = mstCustomer.strCustomerNo INNER JOIN mstPeriod ON stgSalesData.period = mstPeriod.strPeriod")
' Catch ex As Exception
' s = ""
' intFail += 1
' strFail &= " " & i & " """
' strFail &= """ " & ex.Message & " "
' End Try
' End If
' 'eoPostProcess
' End If
' End If
' ' If msgCognos24.Length > 0 Then
' ' If (Request.Form("cognos24.txt") = "1") Then
' ' 'new check to update customers - does not need to be here once superbowl up and running
' ' If File.Exists(strPathSource & "\customers.xls") Then
' ' LoadCustomers()
' ' End If
' ' Dim lCusName As String
' ' Dim lProdName As String
' ' 'Dim lUnits As Double
' ' Dim l9l As Double
' ' Dim lGSV As Double
' ' Dim lNSV As Double
' ' intInsert = 0
' ' intUpdate = 0
' ' intFail = 0
' ' i = 0
' ' strFail = ""
' ' Try
' ' xds = ConnectTxt("cognos24.txt")
' ' intTotals = xds.Tables(0).Rows.Count
' ' s = xds.Tables(0).Rows(0).Item(4)
' ' lPeriod = s.Substring(0, 6)
' ' If lPeriod < StartPeriod Then
' ' StartPeriod = lPeriod
' ' End If
' ' UpdateLog(lPeriod, "blnCognos24")
' ' msg &= " Cognos24 "
' ' db.doQueryDS("UPDATE stkProductPeriod SET dblActual = 0 WHERE mstPeriod = '" & lPeriod & "' ")
' ' '*** writing to data warehouse
' ' db.doQueryDS("DELETE FROM stgCognos24")
' ' db.doQueryDS("DELETE FROM stgSalesDataDownload")
' ' db.doQueryDS("DELETE FROM stgSalesData")
' ' For Each xdr As DataRow In xds.Tables(0).Rows
' ' Try
' ' i += 1
' ' lCusName = xdr.Item(3)
' ' lProdName = xdr.Item(7)
' ' lCusName = sqlDB.CleanString(lCusName)
' ' lProdName = sqlDB.CleanString(lProdName)
' ' 'lUnits = xdr.Item(10)
' ' l9l = xdr.Item(11)
' ' lNSV = xdr.Item(13)
' ' lGSV = xdr.Item(15)
' ' If db.nz(xdr(0), "") = "" Then 'reversals
' ' xdr.Item(10) = xdr.Item(10) * -1
' ' l9l = l9l * -1
' ' lNSV = lNSV * -1
' ' lGSV = lGSV * -1
' ' End If
' ' db.doQueryDS("Insert Into stgCognos24 (doctype, docno, cusno, cusname, period, branch, prodcode, proddesc, packs, packsize, units, ninelce, ordernum, nett, measure, GSV) VALUES ( " & _
' ' " '" & xdr.Item(0) & "', " & xdr.Item(1) & ", " & xdr.Item(2) & ", '" & lCusName & "', '" & xdr.Item(4) & "', '" & xdr.Item(5) & "', '" & xdr.Item(6) & "', '" & lProdName & "', " & xdr.Item(8) & ", " & xdr.Item(9) & ", " & xdr.Item(10) & ", " & l9l & ", " & xdr.Item(12) & ", " & lNSV & ", '" & xdr.Item(14) & "', " & lGSV & ") ")
' ' If (sqlDB.nz(xdr.Item(8), 0)) > 0 And (sqlDB.nz(xdr.Item(9), 0) > 0) Then
' ' If xdr.Item(14) = "CS" Then
' ' dVal = xdr.Item(10)
' ' Else
' ' dVal = (xdr.Item(10) / xdr.Item(8))
' ' End If
' ' ' dVal = ((sqlDB.nz(xdr.Item(11), 0) * 9000) / sqlDB.nz(xdr.Item(8), 0)) / sqlDB.nz(xdr.Item(9), 0)
' ' dVal = Math.Round(CDbl(sqlDB.nz(dVal, 0)), 4, MidpointRounding.AwayFromZero)
' ' Else
' ' dVal = 0
' ' End If
' ' If findMP(arrMP, xdr.Item(6)) Then
' ' If Not findPP(arrPP, xdr.Item(6), lPeriod) Then
' ' db.doQueryDS(db.sql_insert_PP(ProdCode5Chars(xdr.Item(6)), lPeriod, 0, 0, 0, Math.Round(dVal, 4, MidpointRounding.AwayFromZero), 0, 0, 0, 0, 0, 0, 0, 0))
' ' intInsert += 1
' ' 'add new product to array
' ' cnt += 1
' ' ReDim Preserve arrPP(1, cnt)
' ' arrPP(0, cnt - 1) = CInt(xdr.Item(6))
' ' arrPP(1, cnt - 1) = CInt(lPeriod)
' ' Else
' ' db.doQueryDS(db.sql_Update_PP_Import(ProdCode5Chars(xdr.Item(6)), lPeriod, "dblActual", Math.Round(dVal, 4, MidpointRounding.AwayFromZero)))
' ' intUpdate += 1
' ' End If
' ' Else
' ' arrPP(1, arrPP.Length) = CInt(lPeriod)
' ' End If
' ' Catch ex As Exception
' ' s = ""
' ' intFail += 1
' ' strFail &= " " & i & " """
' ' For Each item In xdr.ItemArray
' ' strFail &= s & item
' ' s = " _ "
' ' Next
' ' strFail &= """ "
' ' End Try
' ' Next
' ' msg &= "Total Records: " & intTotals & " New Products Periods: " & intInsert & _
' ' " Existing Product Periods: " & intUpdate & " Records Failed: " & intFail & strFail & " "
' ' Catch ex As Exception
' ' End Try
' ' If intFail = 0 Then
' '#If Not Debug Then
' ' Dim SFile As String
' ' SFile = "\cognos24_" & lPeriod & ".txt"
' ' If File.Exists(strPathDest & SFile) Then
' ' File.Move(strPathSource & "\cognos24.txt", strPathDest & Mid(SFile, 1, SFile.Length - 4) & curDateTime & ".txt")
' ' Else
' ' File.Move(strPathSource & "\cognos24.txt", strPathDest & SFile)
' ' End If
' '#End If
' ' 'clean and stage data
' ' 'reversals: already included above' db.doQueryDS("UPDATE stgCognos24 SET units = units*-1, ninelce = ninelce*-1, nett= nett*-1, gsv = gsv*-1 WHERE doctype IS NULL")
' ' db.doQueryDS("INSERT INTO stgSalesDataDownload ( [cust code], [prod code], units, [net sales], period, GSV, docno ) " & _
' ' "SELECT stgCognos24.cusno, CASE WHEN ([measure] = 'BOT') THEN ([prodcode] + 'S') ELSE ([prodcode] + 'F') END AS Prod, Sum(stgCognos24.units) AS SumOfunits, Sum(stgCognos24.nett) AS SumOfnett, Left([period],6) AS strperiod, Sum(stgCognos24.GSV) AS sumofGSV, docno " & _
' ' "FROM stgCognos24 " & _
' ' "GROUP BY stgCognos24.cusno, stgCognos24.prodcode, stgCognos24.measure, Left([period],6), docno ")
' ' db.doQueryDS("DELETE FROM stgSalesDataDownload WHERE units=0 AND [Net sales]=0 ")
' ' db.doQueryDS("INSERT INTO stgSalesData (cusno, brand, prodcode, units, netsales, period, GSV, dblNSV, dblGSV, lngUnits, dbl9l, " & _
' ' "lngUnitsFreeStock, lngUnitsTotal, dbl9lFreeStock, dbl9lTotal, docno) " & _
' ' "SELECT stgSalesDataDownload.[cust code], stgSalesDataDownload.brand, stgSalesDataDownload.[prod code], stgSalesDataDownload.units, " & _
' ' "stgSalesDataDownload.[net sales], stgSalesDataDownload.period, stgSalesDataDownload.GSV, stgSalesDataDownload.[net sales], stgSalesDataDownload.GSV, stgSalesDataDownload.units, ([units]*[intPack]*[intSize])/9000.0 AS ninel, " & _
' ' "0 AS FS, stgSalesDataDownload.units, 0 AS lFS, ([units]*[intPack]*[intSize])/9000.0 AS ninelTot, stgSalesDataDownload.docno " & _
' ' "FROM stgSalesDataDownload LEFT JOIN dwdProduct ON stgSalesDataDownload.[prod code] = dwdProduct.strProdCode") ' refProductID, 'dwdProduct.ProductID,
' ' 'check if any records in stgCognos24 not in mst tables
' ' ds = db.doQueryDS("SELECT stgSalesData.cusno, dwdCustomer.strCustomerNo " & _
' ' "FROM dwdCustomer RIGHT OUTER JOIN stgSalesData ON dwdCustomer.strCustomerNo = stgSalesData.cusno " & _
' ' "GROUP BY stgSalesData.cusno, dwdCustomer.strCustomerNo " & _
' ' "HAVING (dwdCustomer.strCustomerNo IS NULL) OR (dwdCustomer.strCustomerNo = '') ")
' ' For Each dr As DataRow In ds.Tables(0).Rows
' ' intFail = intFail + 1
' ' msg &= "Import into Data Warehouse failed - Customer not found in master table : " & dr.Item(0) & " "
' ' Next
' ' ds.Clear()
' ' 'check salesdata vs mst
' ' ds = db.doQueryDS("SELECT stgSalesData.prodcode, dwdProduct.strProdCode " & _
' ' "FROM dwdProduct RIGHT OUTER JOIN stgSalesData ON dwdProduct.strProdCode = stgSalesData.prodcode " & _
' ' "GROUP BY stgSalesData.prodcode, dwdProduct.strProdCode " & _
' ' "HAVING (dwdProduct.strProdCode = '') OR (dwdProduct.strProdCode IS NULL)")
' ' For Each dr As DataRow In ds.Tables(0).Rows
' ' intFail = intFail + 1
' ' msg &= "Import into Data Warehouse failed - Product not found in master table : " & dr.Item(0) & " "
' ' Next
' ' ds.Clear()
' ' ds = db.doQueryDS("SELECT stgSalesData.Period, mstPeriod.strPeriod " & _
' ' "FROM stgSalesData FULL OUTER JOIN mstPeriod ON stgSalesData.period = mstPeriod.strPeriod " & _
' ' "GROUP BY stgSalesData.Period, mstPeriod.strPeriod " & _
' ' "HAVING (mstPeriod.strPeriod = '') OR (mstPeriod.strPeriod IS NULL)")
' ' For Each dr As DataRow In ds.Tables(0).Rows
' ' intFail = intFail + 1
' ' msg &= "Import into Data Warehouse failed - Period not found in master table : " & dr.Item(0) & " "
' ' Next
' ' ds.Clear()
' ' 'delete from dw and write in values
' ' If intFail = 0 Then
' ' db.doQueryDS("DELETE FROM dwfSales WHERE refPeriod = '" & lPeriod & "' ") ' stgSalesData.refProductID,
' ' db.doQueryDS("INSERT INTO dwfSales ( refPeriod, refCustomerNo, refProdCode, refDocumentNo, dblNSV, dblGSV, lngUnits, lngUnitsFreeStock, lngUnitsTotal, dbl9l, dbl9lFreeStock, dbl9lTotal ) " & _
' ' "SELECT stgSalesData.period, stgSalesData.cusno, prodcode, docno, stgSalesData.dblNSV, stgSalesData.dblGSV, stgSalesData.lngUnits, stgSalesData.lngUnitsFreeStock, stgSalesData.lngUnitsTotal, stgSalesData.dbl9l, stgSalesData.dbl9lFreeStock, stgSalesData.dbl9lTotal " & _
' ' "FROM stgSalesData")
' ' db.doQueryDS("UPDATE dwfSales SET lngUnitsTotal=(lngUnits + lngUnitsFreeStock), dbl9lTotal=(dbl9l + dbl9lFreeStock) " & _
' ' "WHERE refPeriod = '" & lPeriod & "' ")
' ' End If
' ' End If
' ' End If
' ' End If
' For x = 0 To arrDR.Count - 1 '*** direct receipts
' If Request.Form(arrDR(x)) = "1" Then
' intInsert = 0
' intUpdate = 0
' intFail = 0
' i = 0
' strFail = ""
' Try
' xds = ConnectExcel(arrDR(x))
' intTotals = xds.Tables(0).Rows.Count
' lPeriod = arrDR(x).Substring(15, 6)
' If lPeriod < StartPeriod Then
' StartPeriod = lPeriod
' End If
' UpdateLog(lPeriod, "blnDirectReceipts")
' msg &= " " & arrDR(x) & " "
' For Each xdr As DataRow In xds.Tables(0).Rows
' Try
' i += 1
' If findMP(arrMP, xdr(0)) Then
' xdr(0) = db.CleanString(xdr(0))
' xdr(3) = db.CleanString(xdr(3).ToString.Substring(0, 6))
' xdr(4) = Math.Round(CDbl(xdr(4)), 4, MidpointRounding.AwayFromZero)
' If xdr(0) = "05016" Then xdr(4) = xdr(4) * 9
' Dim dr As DataRow
' ds = db.doQueryDS("SELECT * FROM logProductPeriod WHERE mstProdCode = '" & xdr(0) & "' AND mstPeriod = '" & xdr(3) & "'")
' If ds.Tables(0).Rows.Count = 0 Then
' 'insert
' db.doQueryDS("INSERT INTO logProductPeriod(mstProdCode, mstPeriod, dblReceipts) " & _
' "VALUES ('" & xdr(0) & "', '" & xdr(3) & "', " & xdr(4) & ")")
' Try
' db.doQueryDS(db.sql_insert_PP(xdr(0), lPeriod, 0, 0, 0, 0, 0, xdr(4), 0, 0, 0, 0, 0, 0))
' intInsert += 1
' Catch sdex As Exception
' Try
' db.doQueryDS(db.sql_Update_PP_Import(xdr(0), xdr(3), "dblReceipts", xdr(4)))
' intInsert += 1
' Catch ex As Exception
' s = ""
' intFail += 1
' strFail &= " " & i & " """
' For Each item In xdr.ItemArray
' strFail &= s & item
' s = " _ "
' Next
' strFail &= """ "
' End Try
' End Try
' cnt += 1
' Else
' 'update
' '!! dVal is the diif between the New Value and the Old Value. the logTable get set to the New Value, but the PP get Incremented to the Diff
' dr = ds.Tables(0).Rows(0)
' dVal = 0
' dVal = xdr(4) - dr!dblReceipts
' db.doQueryDS("UPDATE logProductPeriod SET dblReceipts = " & xdr(4) & " WHERE mstProdCode = '" & xdr(0) & "' AND mstPeriod = '" & xdr(3) & "'")
' Try
' db.doQueryDS(db.sql_insert_PP(xdr(0), lPeriod, 0, 0, 0, 0, 0, xdr(4), 0, 0, 0, 0, 0, 0))
' intUpdate += 1
' Catch sdex As Exception
' Try
' db.doQueryDS(db.sql_Update_PP_Import(xdr(0), xdr(3), "dblReceipts", dVal))
' intUpdate += 1
' Catch ex As Exception
' s = ""
' intFail += 1
' strFail &= " " & i & " """
' For Each item In xdr.ItemArray
' strFail &= s & item
' s = " _ "
' Next
' strFail &= """ "
' End Try
' End Try
' End If 'record exists
' End If 'prod exists
' Catch ex As Exception
' s = ""
' intFail += 1
' strFail &= " " & i & " """
' For Each item In xdr.ItemArray
' strFail &= s & item
' s = " _ "
' Next
' strFail &= """ "
' End Try
' Next
' msg &= "Total Records: " & intTotals & " New Products Periods: " & intInsert & _
' " Existing Product Periods: " & intUpdate & " Records Failed: " & intFail & strFail & " "
' Catch ex As Exception
' End Try
' If intFail = 0 Then
'#If DEBUG Then
' File.Copy(strPathSource & "\" & arrDR(x), strPathDest & "\" & arrDR(x) & curDateTime)
'#Else
' File.Move(strPathSource & "\" & arrDR(x), strPathDest & "\" & arrDR(x) & curDateTime)
'#End If
' End If
' Else
' sNotImported &= arrDR(x) & " "
' End If
' Next
' Dim strCustomerNo As String = ""
' Dim comma2 As String = ""
' For x = 0 To arrCRep.Count - 1 '*** Customer Reps
' If Request.Form(arrCRep(x)) = "1" Then
' intInsert = 0
' intUpdate = 0
' intFail = 0
' i = 0
' strFail = ""
' Try
' xds = ConnectExcel(arrCRep(x))
' intTotals = xds.Tables(0).Rows.Count
' UpdateLog(Date.Now.ToString("yyyyMM"), "blnCustomerRep")
' msg &= " " & arrCRep(x) & " "
' For Each xdr As DataRow In xds.Tables(0).Rows
' i += 1
' 'update mst & dwd Customer
' Try
' For j = 0 To xds.Tables(0).Columns.Count - 1
' xdr(j) = db.nz(xdr(j), "")
' xdr(j) = db.CleanString(xdr(j))
' Next
' If xdr(0) = "" Then Throw New Exception(" Missing Customer Number! ")
' db.doQueryDS("UPDATE mstCustomer SET refRepID = '" & xdr(1) & "', refRepBBID = '" & xdr(2) & "', refRepDlxID = '" & xdr(3) & "', refRepLuxID = '" & xdr(4) & "', strLastUser = '" & Session.Item("User") & "', dtStamp = '" & db.dbDate() & "' " & _
' "WHERE strCustomerNo = '" & xdr(0) & "'")
' strCustomerNo &= comma2 & "'" & xdr(0) & "'"
' comma2 = ","
' intUpdate += 1
' Catch ex As Exception
' s = ""
' intFail += 1
' strFail &= " " & i & " """
' For Each item In xdr.ItemArray
' strFail &= s & item
' s = " _ "
' Next
' strFail &= """ -" & ex.Message
' End Try
' cnt += 1
' Next
' db.doQueryDS(db.sql_update_Customer_dw_IN(strCustomerNo))
' msg &= "Total Records: " & intTotals & " Customers Updated: " & intUpdate & " Records Failed: " & intFail & strFail & " "
' Catch ex As Exception
' End Try
' If intFail = 0 Then
'#If DEBUG Then
' File.Copy(strPathSource & "\" & arrCRep(x), strPathDest & "\" & arrCRep(x) & curDateTime)
'#Else
' File.Move(strPathSource & "\" & arrCRep(x), strPathDest & "\" & arrCRep(x) & curDateTime)
'#End If
' End If
' Else
' sNotImported &= arrCRep(x) & " "
' End If
' Next
' lblTable.Text = msg & "
Files Not Processed
" & sNotImported
' If StartPeriod.Length > 0 Then
' 'update all products from this period on
' 'get all productperiods where period >= order by product, period asc
' updatePPBalance(StartPeriod)
' End If
' End Sub
' Public Shared Sub updatePPBalance(ByVal StartPeriod As String, Optional ByVal strProdCode As String = "")
' Dim dOp, dC, mC, tmp, tmp2 As Double
' Dim strDate As String
' Dim sProdCode As String
' Dim i, j, y, x As Integer
' Dim db As New sqlDB
' Dim ds As DataSet
' Try
' strDate = Date.Now.ToString("yyyyMM")
' 'ds = db.doQueryDS("SELECT * FROM stkProductPeriod WHERE mstProdCode = '10141' AND mstPeriod >= '" & StartPeriod & "' ORDER BY mstProdCode ASC, mstPeriod ASC")
' If strProdCode = "" Then
' ds = db.doQueryDS("SELECT * FROM stkProductPeriod WHERE mstPeriod >= '" & StartPeriod & "' ORDER BY mstProdCode ASC, mstPeriod ASC")
' Else
' ds = db.doQueryDS("SELECT * FROM stkProductPeriod WHERE mstPeriod >= '" & StartPeriod & "' AND mstProdCode = '" & strProdCode & "' ORDER BY mstPeriod ASC")
' End If
' y = ds.Tables(0).Rows.Count - 1
' Dim dr As DataRow
' For x = 0 To y
' If db.nz(ds.Tables(0).Rows(x)!dblOpeningImports, 0) > 0 Then
' ds.Tables(0).Rows(x)!dblOpening = ds.Tables(0).Rows(x)!dblOpeningImports
' dC = ds.Tables(0).Rows(x)!dblOpeningImports
' End If
' dr = ds.Tables(0).Rows(x)
' If StartPeriod = dr!mstPeriod Then
' dOp = dr!dblOpening
' Else
' dOp = dC
' End If
' If strDate >= dr!mstPeriod Then
' dC = dOp - db.nz(dr!dblActual, 0) + db.nz(dr!dblReceipts, 0) - db.nz(dr!dblExports, 0) + db.nz(dr!dblDamages, 0) + db.nz(dr!dblRepack, 0)
' If (strDate = dr!mstPeriod) And (db.nz(dr!dblActual, 0) = 0) Then
' dC = dOp - db.nz(dr!dblQF, 0) + db.nz(dr!dblOrders, 0) - db.nz(dr!dblExports, 0) + db.nz(dr!dblDamages, 0) - db.nz(dr!dblBMA, 0) + db.nz(dr!dblRepack, 0)
' End If
' Else
' dC = dOp - db.nz(dr!dblQF, 0) + db.nz(dr!dblOrders, 0) - db.nz(dr!dblExports, 0) + db.nz(dr!dblDamages, 0) - db.nz(dr!dblBMA, 0) + db.nz(dr!dblRepack, 0)
' End If
' mC = 0
' tmp = dC
' i = 1
' sProdCode = dr!mstProdCode
' While (tmp > 0) And (mC < 15) And ((x + i) <= y)
' dr = ds.Tables(0).Rows(x + i)
' If sProdCode = dr!mstProdCode Then
' tmp2 = db.nz(dr!dblQF, 0) + db.nz(dr!dblBMA, 0)
' Select Case tmp2
' Case Is = 0
' mC = 15
' Case Is > tmp
' mC = mC + (tmp / tmp2)
' Case Else
' mC += 1
' End Select
' tmp -= tmp2
' i += 1
' Else
' mC = 15
' End If
' End While
' If (tmp < 0) And (mC < 1) Then
' dr = ds.Tables(0).Rows(x + 1)
' If sProdCode = dr!mstProdCode Then
' tmp2 = db.nz(dr!dblQF, 0) + db.nz(dr!dblBMA, 0)
' Select Case tmp2
' Case Is = 0
' mC = 15
' Case Else
' mC = mC + (tmp / tmp2)
' End Select
' End If
' End If
' dr = ds.Tables(0).Rows(x)
' db.doQueryDS("UPDATE stkProductPeriod SET dblOpening = " & Math.Round(dOp, 4, MidpointRounding.AwayFromZero) & ", dblClosing = " & Math.Round(dC, 4, MidpointRounding.AwayFromZero) & ", dblMonths = " & Math.Round(mC, 2, MidpointRounding.AwayFromZero) & " WHERE mstPeriod = '" & dr!mstPeriod & "' AND mstProdCode = '" & dr!mstProdCode & "' ")
' Next
' Catch ex As Exception
' End Try
' End Sub
' Public Function findPP(ByVal arr(,) As Integer, ByVal ProdCode As Integer, ByVal Period As Integer) As Boolean
' For i As Integer = 0 To (arr.Length / 2) - 1
' If arr(0, i) = ProdCode And arr(1, i) = Period Then
' Return True
' Exit For
' End If
' Next
' Return False
' End Function
' Public Function findMP(ByVal arr() As Integer, ByVal ProdCode As Integer) As Boolean
' For i As Integer = 0 To (arr.Length) - 1
' If arr(i) = ProdCode Then
' Return True
' Exit For
' End If
' Next
' Return False
' End Function
' Private Function GetPeriod(ByVal pPeriod As String, ByVal cnt As Integer) As String
' Dim m, y As Integer
' Dim s As String
' m = pPeriod.Substring(4, 2)
' y = pPeriod.Substring(0, 4)
' If (m + cnt) >= 13 Then
' y = y + 1
' m = (m + cnt) - 12
' Else
' m = m + cnt
' End If
' If m < 10 Then
' s = y & "0" & m
' Else
' s = y & m
' End If
' Return s
' End Function
' Private Sub UpdateLog(ByVal pPeriod As String, ByVal pCol As String)
' Dim db As New sqlDB
' Dim ds As DataSet
' ds = db.doQueryDS("SELECT mstPeriod FROM stkImportLog WHERE mstPeriod = '" & pPeriod & "' ")
' If ds.Tables(0).Rows.Count = 0 Then
' db.doQueryDS(db.sql_Insert_ImportLog(pPeriod))
' End If
' db.doQueryDS(db.sql_Update_ImportLog(pPeriod, pCol))
' ds.Clear()
' End Sub
' Private Function ProdCode5Chars(ByVal pProdCode As String) As String
' Dim s As String
' s = pProdCode
' While s.Length < 5
' s = "0" & s
' End While
' ProdCode5Chars = s
' End Function
' Private Sub LoadCustomers()
' Dim ds, xds As DataSet
' Dim db As New sqlDB
' Dim lCusName As String
' Dim lBranchID As Long
' Dim lRepName As String
' Dim lCode As String
' Dim lBranch As String
' Dim lSalesOffice As String
' Dim lRegion As String
' Dim lSAPCode As String
' Dim lRegionCode As String
' Dim lRegionDesc As String
' Dim lRegionAccpacCode As String
' Dim lRegionDesc2 As String
' xds = ConnectExcel("Customers.xls")
' For Each xdr As DataRow In xds.Tables(0).Rows
' lCusName = xdr.Item(1)
' lCusName = lCusName.Replace("'", "''")
' ds = db.doQueryDS("SELECT strCustomerNo FROM mstCustomer WHERE strCustomerNo = '" & xdr.Item(0) & "' ")
' If ds.Tables(0).Rows.Count = 0 Then
' lBranchID = 0
' ds = db.doQueryDS("SELECT * FROM mstBranch WHERE strRegionDesc = '" & xdr.Item(6) & "' ")
' If ds.Tables(0).Rows.Count > 0 Then
' lBranchID = ds.Tables(0).Rows(0).Item("BranchID")
' End If
' ds.Clear()
' db.doQueryDS("INSERT INTO mstCustomer (strCustomerNo, strCustomerName, " & _
' "strSnellChannel, strSnellArea, strSnellcwsireg, strSnellrepno, strSnellsupergroup, " & _
' "strSnellgrading, refRepID, refRepBBID, refRepDlxID, refRepLuxID, refBranchID, " & _
' "strBranch, refKeyAccount_GroupID, refChannelID, refType1ID, refType2ID, " & _
' "refSegmentID, blnActive, strLastUser, dtStamp) VALUES ( " & _
' "'" & xdr.Item(0) & "', '" & lCusName & "', '" & xdr.Item(2) & "', " & _
' "'" & xdr.Item(3) & "', '" & xdr.Item(4) & "', '" & xdr.Item(5) & "', " & _
' "'" & xdr.Item(7) & "', '" & xdr.Item(8) & "', " & _
' " 0, 0, 0 , 0, " & lBranchID & ", '" & xdr.Item(6) & "', " & _
' " 0, 0, 0, 0, 0, 1, 'Import', '200811061422')")
' End If
' ds.Clear()
' ds = db.doQueryDS("SELECT strCustomerNo FROM dwdCustomer WHERE strCustomerNo = '" & xdr.Item(0) & "' ")
' If ds.Tables(0).Rows.Count = 0 Then
' lBranchID = 0
' lCode = ""
' lBranch = ""
' lSalesOffice = ""
' lRegion = ""
' lSAPCode = ""
' lRegionCode = ""
' lRegionDesc = ""
' lRegionAccpacCode = ""
' lRegionDesc2 = ""
' ds = db.doQueryDS("SELECT * FROM mstBranch WHERE strRegionDesc = '" & xdr.Item(6) & "' ")
' If ds.Tables(0).Rows.Count > 0 Then
' lBranchID = ds.Tables(0).Rows(0).Item("BranchID")
' lCode = ds.Tables(0).Rows(0).Item("strCode")
' lBranch = ds.Tables(0).Rows(0).Item("strBranch")
' lSalesOffice = ds.Tables(0).Rows(0).Item("strSalesOffice")
' lRegion = ds.Tables(0).Rows(0).Item("strRegion")
' lSAPCode = ds.Tables(0).Rows(0).Item("strSAPCode")
' lRegionCode = ds.Tables(0).Rows(0).Item("strRegionCode")
' lRegionDesc = ds.Tables(0).Rows(0).Item("strRegionDesc")
' lRegionAccpacCode = ds.Tables(0).Rows(0).Item("strRegionAccpacCode")
' lRegionDesc2 = ds.Tables(0).Rows(0).Item("strRegionDesc2")
' lCode = lCode.Replace("'", "''")
' lBranch = lBranch.Replace("'", "''")
' lSalesOffice = lSalesOffice.Replace("'", "''")
' lRegion = lRegion.Replace("'", "''")
' lSAPCode = lSAPCode.Replace("'", "''")
' lRegionCode = lRegionCode.Replace("'", "''")
' lRegionDesc = lRegionDesc.Replace("'", "''")
' lRegionAccpacCode = lRegionAccpacCode.Replace("'", "''")
' lRegionDesc2 = lRegionDesc2.Replace("'", "''")
' End If
' ds.Clear()
' lRepName = ""
' ds = db.doQueryDS("SELECT strRepName FROM mstRep WHERE RepID = '" & xdr.Item(5) & "' ")
' If ds.Tables(0).Rows.Count > 0 Then
' lRepName = ds.Tables(0).Rows(0).Item("strRepName")
' End If
' ds.Clear()
' db.doQueryDS("INSERT INTO dwdCustomer (strCustomerNo, strCustomerName, " & _
' "strSnellChannel, strSnellArea, strSnellcwsireg, strSnellrepno, strSnellsupergroup, " & _
' "strSnellgrading, refRepID, refRepBBID, refRepDlxID, refRepLuxID, strRepName, " & _
' "strRepNameBB, strRepNameDlx, strRepNameLux, strChannel, strSegment, strType1, " & _
' "strType2, strGroupName, strKeyAccount, blnPromotionalAccounts, blnActive, " & _
' "strCode, strBranch, strSalesOffice, strRegion, strSAPCode, strRegionCode, " & _
' "strRegionDesc, strRegionAccpacCode, strRegionDesc2) VALUES ( " & _
' "'" & xdr.Item(0) & "', '" & lCusName & "', '" & xdr.Item(2) & "', " & _
' "'" & xdr.Item(3) & "', '" & xdr.Item(4) & "', '" & xdr.Item(5) & "', " & _
' "'" & xdr.Item(7) & "', '" & xdr.Item(8) & "', 0, 0, 0, 0, " & _
' " '" & lRepName & "', '', '', '', '', '', '', '', '', '', 0, 1, " & _
' " '" & lCode & "', '" & lBranch & "', '" & lSalesOffice & "', '" & lRegion & "', " & _
' " '" & lSAPCode & "', '" & lRegionCode & "', '" & lRegionDesc & "', " & _
' " '" & lRegionAccpacCode & "', '" & lRegionDesc2 & "')")
' End If
' ds.Clear()
' Next
' End Sub
' Public Function FindProductPeriod(ByVal strProdCode As String, ByVal strPeriod As String) As DataRow
' '"SELECT mstProdCode, mstPeriod, 'True' as blnFound
' For Each dr In dtProductPeriod.Rows
' If dr!mstProdCode = strProdCode And dr!mstPeriod = strPeriod Then
' Return dr
' Exit For
' End If
' Next
' Return Nothing
' End Function
'End Class