'v1.2.7 - changed c14 colomn definitions (Fank you SAP)
'v1.2.10 - 20161109 - added section for importing BMA - maanie
'v1.3.0 - 20180108 - fixed issue with Zero Opening stock balances - maanie
'v1.3.1 - 20251209 - fixed bug preventing zero opening stock from being uploaded (line 1583: changed > 0 to IsNot DBNull.Value check) - Renier
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 arrBMAdj 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 Shared msgBMAdj 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 = ""
msgBMAdj = ""
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') " &
"BM Adjustments: 'BM AdjustmentsYYYYMM.xls' (eg, 'BM Adjustments200807.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 &= "
BM Adjustments
"
msg &= msgBMAdj
msg &= "
"
'msg &= "
Cognos 24
"
'msg &= msgCognos24
msg &= "
"
End If
Catch ex As Exception
lblTable.Text = ex.Message & " " & ex.StackTrace
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, 14) = "BM Adjustments" Then
CheckValidFile2(sFileName, 8)
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
'msg &= intTarget & " = " & intColumns & " "
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
Case 8 'BM Adjustments 20161109
NoColumns = 3
If CheckColumns(xds, NoColumns) Then
arrBMAdj.Add(sFileName)
msgBMAdj &= "
" & sFileName & "
"
msgBMAdj &= "
"
For x = 0 To NoColumns - 1
msgBMAdj &= "
" & xds.Tables(0).Columns(x).ColumnName & "
"
Next
msgBMAdj &= "
"
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 &= "
"
Case 8
msgBMAdj &= "
"
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
'Console.WriteLine(ex.Message)
msg = ex.Message
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
msg = ex.Message
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 WHERE (blnCompProduct = 0 OR blnCompProduct IS NULL) 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 stkProductPeriod.mstProdCode, stkProductPeriod.mstPeriod FROM stkProductPeriod INNER JOIN mstProduct ON stkProductPeriod.mstProdCode = left(mstProduct.strProdCode,5) WHERE (mstProduct.blnCompProduct = 0 OR mstProduct.blnCompProduct IS NULL) ORDER BY stkProductPeriod.mstProdCode, stkProductPeriod.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 = NULL WHERE mstPeriod = '" & lPeriod & "' ") 'v1.3.0 - 20180108 - fixed issue with Zero Opening stock balances - maanie
For Each xdr As DataRow In xds.Tables(0).Rows
If IsCompProductCode(xdr.Item(0)) Then Continue For
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)) 'v1.3.0 - 20180108 - fixed issue with Zero Opening stock balances - maanie
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
If IsCompProductCode(xdr.Item(0)) Then Continue For
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 & " "
'new: added FinQF. 01/04/2009 & removed
'db.doQueryDS("DELETE FROM mstQFFin where strPeriod > '" & lPeriod & "' ")
'db.doQueryDS("INSERT INTO mstQFFin (strPeriod, refBrandID, refProdCode, dblBudget9l, dblFlat) " & _
' "SELECT stkProductPeriod.mstPeriod, mstProduct.refBrandID, stkProductPeriod.mstProdCode + 'F' AS mstProdCode, ROUND(SUM(stkProductPeriod.dblQF * mstProduct.intSize * mstProduct.intPack) / 9000, 4) AS dbl9L, SUM(stkProductPeriod.dblQF) AS dblFlat " & _
' "FROM stkProductPeriod INNER JOIN mstProduct ON stkProductPeriod.mstProdCode + 'F' = mstProduct.strProdCode " & _
' "GROUP BY stkProductPeriod.mstPeriod, mstProduct.refBrandID, stkProductPeriod.mstProdCode " & _
' "HAVING (stkProductPeriod.mstPeriod > '" & lPeriod & "') AND (SUM(stkProductPeriod.dblQF) > 0) " & _
' "ORDER BY stkProductPeriod.mstPeriod")
Catch ex As Exception
End Try
If intFail = 0 Then
#If DEBUG Then
File.Copy(strPathSource & "\" & arrF(x), strPathDest & "\" & arrF(x) & curDateTime)
#Else
File.Move(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 &= "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
If IsCompProductCode(xdr.Item(0)) Then Continue For
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
If IsCompProductCode(xdr.Item(0)) Then Continue For
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
If IsCompProductCode(xdr.Item(0)) Then Continue For
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
'v1.2.7 - swopped cols 14 & 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(17), xdr(15), xdr(16), xdr(14)))
'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 And WebConfigurationManager.AppSettings("blnDebug") = "True" 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
'c1
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 = '-' OR doctype = 'C' ")
'ini existing PP
db.doQueryDS("UPDATE stkProductPeriod SET dblActual = 0 WHERE mstPeriod IN (" & strPeriodsIN & ") ")
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
'WriteLog(PostProcess, "Post Process Start: [Data Warehouse]", "")
s = db.getRow("SELECT MAX(dtStamp) AS dtStamp FROM dlyImportLog")!dtStamp
'>> strSalesDataDownload 'v1.10
'Process(intType).Reset(db.countRows("SELECT * FROM stgCognos14"))
db.doQueryDS("INSERT INTO stgSalesDataDownload (docno, [cust code], period, [prod code], units, [net sales], gsv, dblNettEx, strDocumentType, ordernum) " &
"SELECT docno, cusno, LEFT(period, 8) 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, 8), docno, doctype, ordernum")
'Process(intType).Count -= db.countRows("SELECT * FROM stgSalesDataDownload WHERE (units=0) AND ([Net sales]=0)")
db.doQueryDS("DELETE FROM stgSalesDataDownload WHERE (units=0) AND ([Net sales]=0)")
'PostProcess.Index += 1
'>> 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]")
'PostProcess.Index += 1 'stgSalesDataDownload.units * mstProduct.intPack * mstProduct.intSize / 9000.0 AS ninelTot
'freestock
'IF Customer (ShipTo) != Ordernum (BillTo) AND Ordernum's customer is PromotionalAccount then
' - Update records set freestock = units AND units = 0
' - Duplicate records, with Ordernum as the Customer and freestock values as negative
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")
'PostProcess.Index += 1
'd1 >> dwfSales: Delete, but Not export documents
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, strDate) " &
"SELECT LEFT(stgSalesData.period,6), stgSalesData.cusno, stgSalesData.prodcode, stgSalesData.docno, SUM(stgSalesData.dblNSV) AS dblNSV, SUM(stgSalesData.dblGSV) AS dblGSV, SUM(stgSalesData.lngUnits) AS lngUnits, SUM(stgSalesData.lngUnitsFreeStock) AS lngUnitsFreeStock, SUM(stgSalesData.lngUnits + stgSalesData.lngUnitsFreeStock) AS dblUnitsTotal, SUM(stgSalesData.dbl9l) AS dbl9l, SUM(stgSalesData.dbl9lFreeStock) AS dbl9lFreeStock, SUM(stgSalesData.dbl9l + stgSalesData.dbl9lFreeStock) AS dbl9ltotal, SUM(stgSalesData.dblNettEx) AS dblNettEx, stgSalesData.strDocumentType, stgSalesData.period " &
"FROM stgSalesData INNER JOIN mstProduct ON stgSalesData.prodcode = mstProduct.strProdCode INNER JOIN mstCustomer ON stgSalesData.cusno = mstCustomer.strCustomerNo INNER JOIN mstPeriod ON LEFT(stgSalesData.period, 6) = mstPeriod.strPeriod " &
"GROUP BY LEFT(stgSalesData.period,6), stgSalesData.cusno, stgSalesData.prodcode, stgSalesData.docno, stgSalesData.strDocumentType, stgSalesData.period")
'Process(intType).Insert = db.countRows("SELECT stgSalesData.period, stgSalesData.cusno, stgSalesData.prodcode, stgSalesData.docno, stgSalesData.dblNSV, stgSalesData.dblGSV, " & _
'"stgSalesData.lngUnits, stgSalesData.lngUnitsFreeStock, stgSalesData.lngUnits + stgSalesData.lngUnitsFreeStock AS Expr1, stgSalesData.dbl9l, " & _
'"stgSalesData.dbl9lFreeStock, stgSalesData.dbl9l + stgSalesData.dbl9lFreeStock AS Expr2, stgSalesData.dblNettEx " & _
'"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")
'Process(intType).Fail = Process(intType).Count - Process(intType).Insert
'PostProcess.Index += 1
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
If IsCompProductCode(xdr(0)) Then Continue For
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
'msg &= arrCRep(0)
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! ")
'msg &= " UPDATE mstCustomer SET refRepID = '" & xdr(1) & "', refRepBBID = '" & xdr(2) & "', refRepDlxID = '" & xdr(3) & "', refRepLuxID = '" & xdr(4) & "', strLastUser = '" & WebConfigurationManager.AppSettings("UNAME") & "', dtStamp = '" & db.dbDate() & "' " & _
' "WHERE strCustomerNo = '" & xdr(0) & "'"
db.doQueryDS("UPDATE mstCustomer SET refRepID = '" & xdr(1) & "', refRepBBID = '" & xdr(2) & "', refRepDlxID = '" & xdr(3) & "', refRepLuxID = '" & xdr(4) & "', strLastUser = '" & WebConfigurationManager.AppSettings("UNAME") & "', 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
'v1.2.10 - 20161109 - added section for importing BMA - maanie
For x = 0 To arrBMAdj.Count - 1 '*** BM Adj
If Request.Form(arrBMAdj(x)) = "1" Then
intInsert = 0
intUpdate = 0
intFail = 0
i = 0
strFail = ""
Try
xds = ConnectExcel(arrBMAdj(x))
intTotals = xds.Tables(0).Rows.Count
lPeriod = arrBMAdj(x).Substring(14, 6)
If lPeriod < StartPeriod Then
StartPeriod = lPeriod
End If
UpdateLog(lPeriod, "blnBMA")
msg &= " " & arrBMAdj(x) & " "
'db.doQueryDS("UPDATE stkProductPeriod SET dblDamages = 0 WHERE mstPeriod = '" & lPeriod & "' ")
For Each xdr As DataRow In xds.Tables(0).Rows
If IsCompProductCode(xdr.Item(0)) Then Continue For
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, Math.Round(CDbl(sqlDB.nz(xdr.Item(2), 0)), 4, MidpointRounding.AwayFromZero), 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_Import(ProdCode5Chars(xdr.Item(0)), lPeriod, "dblBMA", Math.Round(CDbl(sqlDB.nz(xdr.Item(2), 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 & "\" & arrBMAdj(x)) Then
File.Move(strPathSource & "\" & arrBMAdj(x), strPathDest & "\" & arrBMAdj(x) & curDateTime)
Else
File.Move(strPathSource & "\" & arrBMAdj(x), strPathDest & "\" & arrBMAdj(x) & curDateTime)
End If
End If
Else
sNotImported &= arrBMAdj(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 = "")
'changed v1.2.5 from currentPeriod to currentProduct. 20090416
Dim dOp, dC, mC, tmp, tmp2 As Double
Dim strDate, currentProduct As String
Dim sProdCode As String
Dim i, j, y, x As Integer
Dim ds, directData As DataSet
Dim db As New sqlDB
Try
'INI
strDate = Date.Now.ToString("yyyyMM")
currentProduct = ""
'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")
'directData = db.doQueryDS()
Else
ds = db.doQueryDS("SELECT * FROM stkProductPeriod WHERE mstPeriod >= '" & StartPeriod & "' AND mstProdCode = '" & strProdCode & "' ORDER BY mstPeriod ASC")
End If
'Main loop
y = ds.Tables(0).Rows.Count - 1
Dim dr As DataRow
For x = 0 To y
'- 20251209 - Fixed bug: Check for NOT NULL instead of > 0 to allow zero opening stock values
'If db.nz(ds.Tables(0).Rows(x)!dblOpeningImports, 0) > 0 Then
If ds.Tables(0).Rows(x)!dblOpeningImports IsNot DBNull.Value 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 currentProduct <> dr!mstProdCode Then ' v1.2.5. old: `StartPeriod = dr!mstPeriod`
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)
currentProduct = dr!mstProdCode
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) & ", strLastUserUpdate = 'SC Import', dtStampUpdate='" & Date.Now.ToString("yyyyMMddhhmm") & "' 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
''' Returns True if the product code (first 5 chars) is a comp product (e.g. COMP1, COMP2). Used to skip comp product rows during import.
Private Function IsCompProductCode(ByVal prodCode As Object) As Boolean
If prodCode Is Nothing OrElse prodCode Is DBNull.Value Then Return False
Dim s As String = prodCode.ToString().Trim()
If s.Length < 4 Then Return False
s = s.PadRight(5).Substring(0, 5)
Return s.ToUpperInvariant().StartsWith("COMP")
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