'NOTES: 'c1 - For every control added logically before the main grid, increse the control index to get to the Content Placeholder. currently at 19 (20091006) ' ' 'VERSIONS: 'v1.2.8a - 20091006 - added chkUseQF for the current month 'v1.2.8b - 20091006 - added Pivot Export 'v1.2.8c - 20091006 - added Varietal and Category filters 'v1.2.9 - 20100615 - changed BMA security: BM's can now adjust from current month + 1 (ln 234) ' - - additions to the variance report 'v1.3.0 - 20180108 - fixed issue with Zero Opening stock balances - maanie ' -20210308 - added direct sales for Grid period Imports System.Web.Configuration Imports Stock_Control.sqlDB Partial Public Class ProductPeriod Inherits System.Web.UI.Page Private Shared strProdCode As String Private Shared strPeriod As String Private Shared strStartDate, strEndDate, strDate, strVarietal, strCatagory As String 'v1.2.8c Private Shared intBrandID As Integer Private Const INTROWS As Integer = 15 Private Shared strColumn() As String = {"lblP", "lblOpening", "lblQF", "txtBMA", "lblActual", "txtOrders", "txtReceipts", "lblExports", "lblDamages", "lblRepack", "lblClosing", "lblMC", "lblLastUser", "lblDT", "lblLastUserSM", "lblLastEditSM"} Private Shared arrGrid(INTROWS, 14) '15-1 y * 15-1 x Private Shared arrDec(INTROWS - 4, 14) Private Shared arrAdditional(1, 14) Dim closingDifference(14) As Double Private db As New sqlDB Private Shared blnLoad = False Private Shared dblVariance As Double = 2.5 Private Shared dsExport, dsExportPivot As DataSet Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load blnLoad = False If Not Page.IsPostBack Then WebConfigurationManager.AppSettings("strPage") = "Home" strDate = Date.Now.ToString("yyyyMM") sdsProduct.FilterExpression = "refBrandID = 0" btnEmail.Text = "Confirm [" & strDate & "]" If WebConfigurationManager.AppSettings("UGROUP").ToLower.Contains("brand") Then sdsBrand.SelectCommand = "SELECT DISTINCT mstBrand.BrandID, mstBrand.strBrand FROM mstBrand INNER JOIN mstProduct ON mstBrand.BrandID = mstProduct.refBrandID WHERE (mstBrand.refBrandManagerID = " & WebConfigurationManager.AppSettings("UID") & " OR mstBrand.refBrandManager2ID = " & WebConfigurationManager.AppSettings("UID") & ") AND mstBrand.blnActive = 1 AND mstProduct.blnActive = 1 AND (mstProduct.blnCompProduct = 0 OR mstProduct.blnCompProduct IS NULL) ORDER BY mstBrand.strBrand" End If Try strProdCode = Request.QueryString("PC") strPeriod = Request.QueryString("P") intBrandID = CInt(Request.QueryString("B")) If strProdCode <> "" And strPeriod <> "" Then blnLoad = True sdsProduct.FilterExpression = "refBrandID = " & intBrandID 'LoadGrid() Else blnLoad = False End If Catch ex As Exception End Try End If End Sub Private Sub ProductPeriod_PreRenderComplete(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreRenderComplete If blnLoad = True Then 'only if nav from other window using the $_get LoadGrid() End If End Sub Private Function FindControls(ByVal strID As String) As Web.UI.Control Dim webControls As Web.UI.Control '! Look for cellPP inside rowPP inside tblPP inside the content placeholder!! 'v1.2.8c - 20091006 - inc Controls from 19 to 23 'c1 'webControls = DirectCast(DirectCast(Master.Controls(3), System.Web.UI.Control).Controls(7), System.Web.UI.Control) webControls = DirectCast(DirectCast(DirectCast(DirectCast(Master.Controls(3), System.Web.UI.Control).Controls(7), System.Web.UI.Control), System.Web.UI.Control).Controls(23).Controls(0).Controls(0), System.Web.UI.Control) For Each c As Web.UI.Control In webControls.Controls If c.ID = strID Then Return c Exit For End If Next Return Nothing End Function Private Sub ClearGrid() Dim obj Dim x, y As Integer 'Dim strID As String 'arrGrid(0, i - 1) = ddlPeriod.Items(ddlPeriod.SelectedIndex - 4 + i) ' "lblP", "lblOpening", "lblQF", "txtBMA", "lblActual", "txtOrders", "txtReceipts", "lblDamages", "lblClosing", "lblMC", "lblLastUser", "lblDT", "lblLastUserSM", "lblLastEditSM" lblPS2.Text = "Previous Sales" lblPS1.Text = "Previous Sales" lblGrowth.Text = "" lblSKU.Text = "- Select a SKU -" lblPrinciple.Text = "" obj = New Web.UI.WebControls.Label obj = FindControls("lblTS2") obj.text = "" obj = FindControls("lblTS1") obj.text = "" obj = FindControls("lblTS0") obj.text = "" For x = 1 To 15 '15 period cols obj = FindControls("lblPS2" & x) obj.text = "" obj = FindControls("lblPS1" & x) obj.text = "" obj = FindControls("lblPS0" & x) obj.text = "" 'new 13 Nov 2008 arrGrid(0, x - 1) = 0 arrGrid(1, x - 1) = 0 Try obj = FindControls("lblT" & x) obj.text = "" Catch ex As Exception ' this should bomb out when x > 10 End Try obj = Nothing For y = 1 To INTROWS If y = 3 Or y = 5 Then obj = New Web.UI.WebControls.TextBox Else obj = New Web.UI.WebControls.Label End If 'strID = strColumn(y) & x.ToString obj = FindControls(strColumn(y) & x) obj.text = "" obj.forecolor = Drawing.Color.Black arrGrid(y - 1, x - 1) = 0 Next Next obj = Nothing End Sub Public Sub setSecurity(ByVal UGroup As String) Dim dr As DataRow Dim i(2), j(2) As Integer Dim intLead, k As Integer Dim dtLead, d, dtPrevious As Date Dim obj As Web.UI.WebControls.TextBox Dim strID As String d = Date.Today With UGroup.ToLower Try dr = db.getRow(db.sql_get_Product(strProdCode)) intLead = db.nz(dr!intLeadMonths, 0) If .Contains("admin") Then 'dtLead = New Date(d.Year, d.Month + intLead + 1, d.Day) If d.Month - 1 < 1 Then dtPrevious = New Date(d.Year - 1, d.Month - 1 + 12, 1) Else dtPrevious = New Date(d.Year, d.Month - 1, 1) End If i = arrayFindValue(arrGrid, d.ToString("yyyyMM")) 'j = arrayFindValue(arrGrid, dtLead.ToString("yyyyMM")) If d.ToString("yyyyMM") < arrGrid(0, 14) Then j(1) = -1 Else j(1) = 14 End If If i(1) > -1 And j(1) < 0 Then j(1) = 14 End If If i(1) < 0 And j(1) > -1 Then i(1) = 0 End If If i(1) > -1 Or j(1) > -1 Then For k = i(1) To j(1) '5 - Orders strID = strColumn(5) & k + 1 obj = FindControls(strID) obj.ForeColor = Drawing.Color.Blue obj.ReadOnly = False Next End If 'If (d.Month + intLead + 1) > 12 Then ' dtLead = New Date(d.Year + 1, (d.Month + intLead + 1) - 12, 1) 'Else ' dtLead = New Date(d.Year, d.Month + intLead + 1, 1) 'End If dtLead = New Date(d.Year, d.Month, 1) i = arrayFindValue(arrGrid, dtLead.ToString("yyyyMM")) 'j = arrayFindValue(arrGrid, dtLast.ToString("yyyyMM")) If d.ToString("yyyyMM") < arrGrid(0, 14) Then j(1) = -1 Else j(1) = 14 End If If i(1) > -1 And j(1) < 0 Then j(1) = 14 End If If i(1) < 0 And j(1) > -1 Then i(1) = 0 End If If i(1) > -1 Or j(1) > -1 Then For k = i(1) To j(1) '3 - BMA strID = strColumn(3) & k + 1 obj = FindControls(strID) obj.ForeColor = Drawing.Color.Blue obj.ReadOnly = False Next End If 'removed: Receipts are not editable 'i = arrayFindValue(arrGrid, dtPrevious.ToString("yyyyMM")) 'j = arrayFindValue(arrGrid, d.ToString("yyyyMM")) 'If i(1) > -1 And j(1) < 0 Then ' j(1) = 14 'End If 'If i(1) < 0 And j(1) > -1 Then ' i(1) = 0 'End If 'If i(1) > -1 Or j(1) > -1 Then ' For k = i(1) To j(1) ' '6 - Receipts ' strID = strColumn(6) & k + 1 ' obj = FindControls(strID) ' obj.ForeColor = Drawing.Color.Black 'was Blue ' obj.ReadOnly = True 'was False ' Next 'End If 'v1.2.9 - 20100615 - changed BMA security: BM's can now adjust from current month + 1 (ln 234) ElseIf .Contains("brand") Then 'BM can only edit BMA from currentMonth + Lead + 1 onwards // old/ori If (d.Month + 1) > 12 Then 'If (d.Month + intLead + 1) > 12 Then dtLead = New Date(d.Year + 1, (d.Month + 1) - 12, 1) 'dtLead = New Date(d.Year + 1, (d.Month + Lead + 1) - 12, 1) Else dtLead = New Date(d.Year, d.Month + 1, 1) 'dtLead = New Date(d.Year, d.Month + Lead + 1, 1) End If 'dtLast = New Date(arrGrid(0, 14).ToString.Substring(0, 4), arrGrid(0, 14).ToString.Substring(4, 2), 1) If WebConfigurationManager.AppSettings("UID") = dr!UserID Then i = arrayFindValue(arrGrid, dtLead.ToString("yyyyMM")) 'j = arrayFindValue(arrGrid, dtLast.ToString("yyyyMM")) If d.ToString("yyyyMM") < arrGrid(0, 14) Then j(1) = -1 Else j(1) = 14 End If If i(1) > -1 And j(1) < 0 Then j(1) = 14 End If If i(1) < 0 And j(1) > -1 Then i(1) = 0 End If If i(1) > -1 Or j(1) > -1 Then For k = i(1) To j(1) '3 - BMA strID = strColumn(3) & k + 1 obj = FindControls(strID) obj.ForeColor = Drawing.Color.Blue obj.ReadOnly = False Next End If End If ElseIf .Contains("shipping") Then 'dtLead = New Date(arrGrid(0, 14).ToString.Substring(0, 4), arrGrid(0, 14).ToString.Substring(4, 2), d.Day) If d.Month - 1 < 1 Then dtPrevious = New Date(d.Year - 1, d.Month - 1 + 12, 1) Else dtPrevious = New Date(d.Year, d.Month - 1, 1) End If i = arrayFindValue(arrGrid, d.ToString("yyyyMM")) 'j = arrayFindValue(arrGrid, dtLead.ToString("yyyyMM")) If d.ToString("yyyyMM") < arrGrid(0, 14) Then j(1) = -1 Else j(1) = 14 End If If i(1) > -1 And j(1) < 0 Then j(1) = 14 End If If i(1) < 0 And j(1) > -1 Then i(1) = 0 End If If i(1) > -1 Or j(1) > -1 Then For k = i(1) To j(1) '5 - Orders strID = strColumn(5) & k + 1 obj = FindControls(strID) obj.ForeColor = Drawing.Color.Blue obj.ReadOnly = False Next End If 'cut enable "txtReceipts. see [if "admin"] for code copy ElseIf .Contains("orders") Then 'dtLead = New Date(arrGrid(0, 14).ToString.Substring(0, 4), arrGrid(0, 14).ToString.Substring(4, 2), d.Day) 'dtPrevious = New Date(d.Year, d.Month, d.Day) i = arrayFindValue(arrGrid, d.ToString("yyyyMM")) 'j = arrayFindValue(arrGrid, dtLead.ToString("yyyyMM")) If d.ToString("yyyyMM") < arrGrid(0, 14) Then j(1) = -1 Else j(1) = 14 End If If i(1) > -1 And j(1) < 0 Then j(1) = 14 End If If i(1) < 0 And j(1) > -1 Then i(1) = 0 End If If i(1) > -1 Or j(1) > -1 Then For k = i(1) To j(1) '5 - Orders strID = strColumn(5) & k + 1 obj = FindControls(strID) obj.ForeColor = Drawing.Color.Blue obj.ReadOnly = False Next End If Else 'no security := 9Litre For k = 0 To 14 '5 - Orders and 3 - BMA strID = strColumn(3) & k + 1 obj = FindControls(strID) obj.ForeColor = Drawing.Color.Black obj.ReadOnly = True strID = strColumn(5) & k + 1 obj = FindControls(strID) obj.ForeColor = Drawing.Color.Black obj.ReadOnly = True Next End If 'eoIF(type) Catch ex As Exception If strProdCode = "0" Then For k = 0 To 14 '5 - Orders and 3 - BMA strID = strColumn(3) & k + 1 obj = FindControls(strID) obj.ForeColor = Drawing.Color.Black obj.ReadOnly = True strID = strColumn(5) & k + 1 obj = FindControls(strID) obj.ForeColor = Drawing.Color.Black obj.ReadOnly = True Next End If End Try End With obj = Nothing End Sub Public Function arrayFindValue(ByVal arr As Object, ByVal value As Object) As Integer() Dim index(2) As Integer Try For x As Integer = 0 To 14 Try For y As Integer = 0 To 14 Try If arr(x, y).ToString = value.ToString Then index(0) = x index(1) = y Return index End If Catch ex As Exception index(0) = -1 index(1) = -1 Return index End Try Next Catch ex As Exception End Try Next Catch ex As Exception End Try index(0) = -1 index(1) = -1 Return index End Function Protected Sub LoadGrid() 'load PP Dim ds, dsPivot1, dsPivot2, salesData, deductData As DataSet Dim dr, drPivot, salesDataRow As DataRow Dim s, m, y, strID, strFinYearStart, strFinYearEnd, strFinMonthStart, strFinMonthEnd, strStartDate1, strStartDate2, strEndDate1, strEndDate2 As String Dim strBM As String = "" Dim strLongProdDesc As String = "" Dim i, j As Integer Dim intPack As Integer = 0 Dim index As Integer = 0 Dim dblAvg, tmp, tmp2, tmp3, dblGrowth3, dblGrowth6, dblGrowth12, dblGrowthPS3, dblGrowthPS6, dblGrowthPS12, total As Double Dim obj Dim dblTotal(10) As Double 'set CONTROLS btnSave.Enabled = True btnEmail.Enabled = True lblPrinciple.Text = "" lblGrowth.Text = "" 'get start and end dates Try Try y = strPeriod.Substring(0, 4) m = strPeriod.Substring(4) dr = db.getRow("SELECT bm.*, b.dblVariance, mstPrincipal.PrincipalID, mstPrincipal.strFYStart, mstPrincipal.strFYEnd, strPrincipal FROM mstPrincipal INNER JOIN (mstUser AS bm INNER JOIN mstBrand AS b ON bm.UserID = b.refBrandManagerID) ON mstPrincipal.PrincipalID = b.refPrincipalID WHERE b.BrandID=" & intBrandID) strBM = " - " & db.nz(dr!strUserName, "") & " : " & db.nz(dr!strEmail, "") & ".       Target Months Cover: " & dblVariance dblVariance = dr!dblVariance lblPrinciple.Text = "Principal [" & dr!strPrincipal & "]" 'strFinYearStart: Select Case dr!strFYStart Case "01" strFinYearStart = y strFinYearEnd = y Case Is >= m strFinYearStart = CStr(CInt(y) - 1) strFinYearEnd = y Case Is < m strFinYearStart = y strFinYearEnd = CStr(CInt(y) + 1) End Select strFinMonthStart = dr!strFYStart strFinMonthEnd = dr!strFYEnd 'get Princ Fiscal sales to date in 9L db.doQueryDS("DELETE FROM stk9L") db.doQueryDS(db.sql_insert_9L(intBrandID, strFinYearStart & strFinMonthStart, strFinYearEnd & strFinMonthEnd)) dr = db.getRow(db.sql_get_principle_9L(intBrandID, strFinYearStart & strFinMonthStart, strFinYearEnd & strFinMonthEnd)) lblPrinciple.Text &= " sales for " & ddlBrand.SelectedItem.Text & " (total brand 9L cases) for principal fiscal YTD : " & strFinYearStart & strFinMonthStart & " to " & strFinYearEnd & strFinMonthEnd & ": " & FormatNumber(dr!dblActual, 3) & " [9L cases]" Catch ex As Exception 'strBM = "" End Try If strProdCode = "0" Then lblSKU.Text = "All " & ddlBrand.SelectedItem.Text & " [9L cases]" & strBM 'lblSKU.Text = ddlProduct.SelectedItem.Text & " : [9L cases]" & strBM OLD Else dr = db.getRow("SELECT LEFT(strProdCode,5) as MyProdCode, MAX(intPack) as MyPack, strLongProdDesc FROM mstProduct GROUP BY strLongProdDesc, strProdCode HAVING LEFT(strProdCode,5) = " & ddlProduct.SelectedValue()) strLongProdDesc = dr!strLongProdDesc intPack = dr!MyPack strProdCode = dr!MyProdCode lblSKU.Text = strLongProdDesc & " x " & intPack & " - " & strProdCode & " : [Flat cases]" & strBM 'lblSKU.Text = ddlProduct.SelectedItem.Text & " : [Flat cases]" & strBM OLD End If strStartDate = sqlDB.getGridStartDate(strPeriod) strEndDate = sqlDB.getGridEndDate(strPeriod) 'run sql for previous 2 years sales Try strStartDate2 = sqlDB.getGridStartDate(CInt(strPeriod) - 200) strEndDate2 = sqlDB.getGridEndDate(CInt(strPeriod) - 200) lblPS2.Text = "Sales " & strStartDate2 & "+" If strProdCode = "0" Then 'if 9L dsPivot2 = db.doQueryDS(db.sql_get_PreviousPP_9L(intBrandID, strStartDate2, strEndDate2, strVarietal, strCatagory)) 'v1.2.8c Else dsPivot2 = db.doQueryDS(db.sql_get_PreviousPP(strProdCode, strStartDate2, strEndDate2)) End If Catch ex As Exception End Try Try strStartDate1 = sqlDB.getGridStartDate(CInt(strPeriod) - 100) strEndDate1 = sqlDB.getGridEndDate(CInt(strPeriod) - 100) lblPS1.Text = "Sales " & strStartDate1 & "+" If strProdCode = "0" Then 'if 9L dsPivot1 = db.doQueryDS(db.sql_get_PreviousPP_9L(intBrandID, strStartDate1, strEndDate1, strVarietal, strCatagory)) 'v1.2.8c Else dsPivot1 = db.doQueryDS(db.sql_get_PreviousPP(strProdCode, strStartDate1, strEndDate1)) End If Catch ex As Exception End Try 'get 15 periods i = 0 ds = db.doQueryDS(db.sql_get_Period(strProdCode, strStartDate, strEndDate)) For Each dr In ds.Tables(0).Rows i += 1 dblAvg = 0 obj = New Web.UI.WebControls.Label obj = FindControls("lblP" & i) obj.text = db.nz(db.sqlDate(dr!strPeriod & "010101", "MMM yy"), db.sqlDate()) 'match i to the records from pervious 2 years sales ds obj = FindControls("lblPS2" & i) Try drPivot = sqlDB.findRow(dsPivot2, 0, CStr(dr!intCalendarYear - 2) & dr!strPeriod.ToString.Substring(4)) If drPivot IsNot Nothing Then tmp = Math.Round(CDbl(db.nz(drPivot!dblActual, 0)), 0, MidpointRounding.AwayFromZero) obj.text = tmp dblAvg += tmp If i > 3 Then tmp2 += tmp dblGrowthPS12 += tmp End If If i > 9 Then dblGrowthPS6 += tmp If i > 12 Then dblGrowthPS3 += tmp Else obj.text = "0" End If Catch ex As Exception obj.text = "0" End Try obj = FindControls("lblPS1" & i) Try drPivot = sqlDB.findRow(dsPivot1, 0, CStr(dr!intCalendarYear - 1) & dr!strPeriod.ToString.Substring(4)) If drPivot IsNot Nothing Then tmp = Math.Round(CDbl(db.nz(drPivot!dblActual, 0)), 0, MidpointRounding.AwayFromZero) obj.text = tmp dblAvg += tmp If i > 3 Then tmp3 += tmp dblGrowth12 += tmp End If If i > 9 Then dblGrowth6 += tmp If i > 12 Then dblGrowth3 += tmp Else obj.text = "0" End If Catch ex As Exception obj.text = "0" End Try obj = FindControls("lblPS0" & i) obj.text = Math.Round(dblAvg / 2, 0, MidpointRounding.AwayFromZero) obj = Nothing arrGrid(0, i - 1) = dr!strPeriod If dr!intPP = 0 And strProdCode <> "" And strProdCode <> "0" Then db.doQueryDS(db.sql_insert_PP(strProdCode, dr!strPeriod, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)) End If Next 'lblGrowth - new 20081029 'added DirectSales -lloyd 20210305 ref lblDSales Try If strProdCode = "0" Then salesData = db.doQueryDS(db.sql_get_DirectSalesAll(intBrandID, strStartDate, strEndDate, strVarietal, strCatagory)) Else salesData = db.doQueryDS(db.sql_get_DirectSales(strProdCode, strStartDate, strEndDate)) End If Catch ex As Exception End Try For Each dRow In ds.Tables(0).Rows index += 1 obj = New Web.UI.WebControls.Label obj = FindControls("lblDSales" & index) Try salesDataRow = sqlDB.findRow(salesData, 0, CStr(dRow!intCalendarYear) & dRow!strPeriod.ToString.Substring(4)) If salesDataRow IsNot Nothing Then tmp = Math.Round(CDbl(db.nz(salesDataRow!dblActual, 0)), 0, MidpointRounding.AwayFromZero) closingDifference(index - 1) = tmp 'reference array closing balance -20210309 obj.text = tmp total = total + tmp Else obj.text = 0 closingDifference(index - 1) = 0 End If 'pre condition Catch ex As Exception obj.text = "0" End Try obj = Nothing Next Dim str3, str6, str12 As String dblGrowth3 = Math.Round(((dblGrowth3 / dblGrowthPS3) - 1) * 100, 0, MidpointRounding.AwayFromZero) dblGrowth6 = Math.Round(((dblGrowth6 / dblGrowthPS6) - 1) * 100, 0, MidpointRounding.AwayFromZero) dblGrowth12 = Math.Round(((dblGrowth12 / dblGrowthPS12) - 1) * 100, 0, MidpointRounding.AwayFromZero) If Double.IsNaN(dblGrowth3) Or Double.IsInfinity(dblGrowth3) Then str3 = "n/a" Else str3 = dblGrowth3 & "%" If Double.IsNaN(dblGrowth6) Or Double.IsInfinity(dblGrowth6) Then str6 = "n/a" Else str6 = dblGrowth6 & "%" If Double.IsNaN(dblGrowth12) Or Double.IsInfinity(dblGrowth12) Then str12 = "n/a" Else str12 = dblGrowth12 & "%" lblGrowth.Text = "Growth Rate: 3 Months: " & str3 & ",   6 Months: " & str6 & ",   12 Months: " & str12 'LOAD GRID main Q If strProdCode = "0" Then 'if 9L 'db.doQueryDS( btnSave.Enabled = False btnEmail.Enabled = False db.doQueryDS("DELETE FROM stk9L") db.doQueryDS(db.sql_insert_9L(intBrandID, strStartDate, strEndDate)) ds = db.doQueryDS(db.sql_get_9L(intBrandID, strStartDate, strEndDate, strVarietal, strCatagory)) 'v1.2.8c Else ds = db.doQueryDS(db.sql_get_PP(strProdCode, strStartDate, strEndDate)) End If dsExport = ds 'needed for export? For i = 1 To 15 'NOTE i = 1..15 x:columns 'new 20081113 - set arrAdd() and change ds for the export If chkDecimals.Checked = False Then 'arrAdditional(0, i - 1) = Math.Truncate(ds.Tables(0).Rows(i - 1)!dblOpeningImports) arrAdditional(0, i - 1) = Math.Truncate(db.nz(ds.Tables(0).Rows(i - 1)!dblOpeningImports, 0)) Else 'arrAdditional(0, i - 1) = Math.Round(ds.Tables(0).Rows(i - 1)!dblOpeningImports, 3, MidpointRounding.AwayFromZero) arrAdditional(0, i - 1) = Math.Round(db.nz(ds.Tables(0).Rows(i - 1)!dblOpeningImports, 0), 3, MidpointRounding.AwayFromZero) End If 'v1.3.0 - 20180108 - fixed issue with Zero Opening stock balances - maanie ' * Alex - Had to first do IsDBNull Check, ' then convert to integer And ensure the openingImportValue Is greater than 0, ' Finally If the values are different, use the opening Imports value * If Not IsDBNull(ds.Tables(0).Rows(i - 1)!dblOpeningImports) Then If db.nz(ds.Tables(0).Rows(i - 1)!dblOpeningImports, 0) > 0 Then If ds.Tables(0).Rows(i - 1)!dblOpening <> ds.Tables(0).Rows(i - 1)!dblOpeningImports Then ds.Tables(0).Rows(i - 1)!dblOpening = ds.Tables(0).Rows(i - 1)!dblOpeningImports End If End If End If dr = ds.Tables(0).Rows(i - 1) Try '0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 For j = 1 To INTROWS ' "lblP", "lblOpening", "lblQF", "txtBMA", "lblActual", "txtOrders", "txtReceipts", "lblExports", "lblDamages", "lblRepack","lblClosing", "lblMC", "lblLastUser", "lblDT", "lblLastUserSM", "lblLastEditSM" Try Try 'arrGrid(j, i - 1) = Math.Round(dr.Item(j), 3, MidpointRounding.AwayFromZero) arrGrid(j, i - 1) = Math.Round(dr.Item(j), 3, MidpointRounding.AwayFromZero) If chkDecimals.Checked = False Then arrDec(j, i - 1) = Math.Round(arrGrid(j, i - 1) - Math.Truncate(arrGrid(j, i - 1)), 3, MidpointRounding.AwayFromZero) arrGrid(j, i - 1) = Math.Truncate(arrGrid(j, i - 1)) Else arrDec(j, i - 1) = 0.0 End If Catch ex As Exception arrGrid(j, i - 1) = dr.Item(j) End Try strID = strColumn(j) & i If strID.Contains("txt") Then obj = New Web.UI.WebControls.TextBox obj = FindControls(strID) obj.ReadOnly = True Else obj = New Web.UI.WebControls.Label obj = FindControls(strID) End If obj.ForeColor = Drawing.Color.Black Select Case ds.Tables(0).Columns(j).ColumnName 'Case "mstPeriod" ' obj.text = db.nz(db.sqlDate(dr.Item(j) & "010101", "MMM yy"), db.sqlDate()) 'converts "yyyyMMddhhmm" + "01" to "MMM yy" Case "dtStamp", "dtStampSM" obj.text = db.nz(db.sqlDate(dr.Item(j), True), "-") Case "strLastUser", "strLastUserSM" obj.text = db.nz(dr.Item(j), "") Case "dblClosing" obj.text = Math.Truncate(CDbl(db.nz(dr.Item(j), 0))) 'alternative Case Else If chkDecimals.Checked = False Then obj.text = Math.Truncate(CDbl(db.nz(dr.Item(j), 0))) Else obj.text = Math.Round(CDbl(db.nz(dr.Item(j), 0)), 3, MidpointRounding.AwayFromZero) End If If i > 3 Then dblTotal(j) += CDbl(db.nz(dr.Item(j), 0)) 'row total column End Select obj = Nothing Catch ex As Exception If Not arrGrid(j, i - 1) > 0 Then arrGrid(j, i - 1) = CDbl(0.0) End If End Try Next j Catch ex As Exception s = "" End Try Next i Catch ex As Exception s = "" Finally 'set Totals obj = New Web.UI.WebControls.Label obj = FindControls("lblTS2") obj.text = tmp2 obj = FindControls("lblTS1") obj.text = tmp3 obj = FindControls("lblTS0") obj.text = Math.Round((tmp2 + tmp3) / 2, 0, MidpointRounding.AwayFromZero) obj = FindControls("lblT16") 'total sales column - lloyd 20210305 obj.text = total 'obj = FindControls("lblT1") 'obj.text = Math.Round(dblTotal(1) / 15, 0, MidpointRounding.AwayFromZero) For i = 2 To 9 obj = FindControls("lblT" & i) obj.text = Math.Round(dblTotal(i), 0, MidpointRounding.AwayFromZero) Next 'obj = FindControls("lblT9") 'obj.text = Math.Round(dblTotal(9) / 15, 0, MidpointRounding.AwayFromZero) 'obj = FindControls("lblT10") 'obj.text = Math.Round(dblTotal(10) / 15, 0, MidpointRounding.AwayFromZero) obj = Nothing 'set security/editability 'If ddlBrand.SelectedItem.Text.Contains("Southern") Then 'old soco model ' If strProdCode = "05016" Then 'Or strProdCode = "0" Then ' 'strProdCode = "05016" ' setSecurity(WebConfigurationManager.AppSettings("UGROUP")) ' Else : setSecurity("Viewer") ' End If 'Else ' If strProdCode = "0" Then setSecurity("Viewer") Else setSecurity(WebConfigurationManager.AppSettings("UGROUP")) 'End If If strProdCode = "0" Then setSecurity("Viewer") Else setSecurity(WebConfigurationManager.AppSettings("UGROUP")) ' new line below! reTotal(txtBMA1, 1, 1) ' Unsure how to use function ' new line above! End Try End Sub Protected Sub btnLoad_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnLoad.Click If Page.IsValid Then strProdCode = ddlProduct.SelectedValue strPeriod = ddlPeriod.SelectedValue intBrandID = ddlBrand.SelectedValue 'v1.2.8c strVarietal = Trim(ddlVarietal.SelectedValue) strCatagory = Trim(ddlCategory.SelectedValue) LoadGrid() LoadSKUReport() End If End Sub Protected Sub btnPrint_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnPrint.Click ExportToExcel(dsExport, False) End Sub Protected Sub btnPrintPivot_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnPrintPivot.Click ExportToExcel(dsExportPivot, True) End Sub Public Sub ExportToExcel(ByVal exDS As DataSet, ByVal blnPivot As Boolean) Dim dgExport As New DataGrid() Dim ds As New DataSet("dsExport") Dim filename As String = "" If blnPivot Then filename = ddlBrand.SelectedItem.Text.Replace(" ", "") & "_" & strProdCode & "_" & strPeriod & "_pivot.xls" ds.Tables.Add(exDS.Tables(0).Copy()) Else filename = ddlBrand.SelectedItem.Text.Replace(" ", "") & "_" & strProdCode & "_" & strPeriod & "_normal.xls" ds.Tables.Add(exDS.Tables(0).Copy()) For i As Integer = 0 To ds.Tables(0).Columns.Count - 1 ds.Tables(0).Columns.Item(i).ColumnName = ds.Tables(0).Columns.Item(i).ColumnName.Substring(3) Next ds.Tables(0).Columns.Item(12).ColumnName = "BM" ds.Tables(0).Columns.Item(13).ColumnName = "Date BM" ds.Tables(0).Columns.Item(14).ColumnName = "SM" ds.Tables(0).Columns.Item(15).ColumnName = "Date SM" ds.Tables(0).Columns.Item(16).ColumnName = "Product Code" End If If Page.IsValid Then dgExport.DataSource = ds.Tables(0) dgExport.DataBind() dgExport.ShowHeader = True dgExport.CellPadding = 2 dgExport.CellSpacing = 1 Response.Clear() Response.Buffer = True Response.ContentType = "application/vnd.ms-excel" Response.Charset = "" Response.AddHeader("Content-Disposition", "attachment;filename=" & filename) Me.EnableViewState = False Dim oStringWriter As New System.IO.StringWriter() Dim oHtmlTextWriter As New System.Web.UI.HtmlTextWriter(oStringWriter) 'this.ClearControls(dg) dgExport.RenderControl(oHtmlTextWriter) Response.Write(oStringWriter.ToString()) Response.End() End If ds = Nothing dgExport = Nothing End Sub Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSave.Click Save() End Sub Private Function Save() As Boolean Dim i, j, d, intStartSave As Integer Dim ds As DataSet Dim dr As DataRow Dim newGrid(INTROWS, 14) Dim oldGrid(INTROWS, 14) Dim strID As String Dim obj Dim blnMessage, blnUpdate As Boolean strProdCode = ddlProduct.SelectedValue strPeriod = ddlPeriod.SelectedValue intBrandID = ddlBrand.SelectedValue 'oldGrid = arrGrid reTotal(FindControls("txtBMA1"), 1, 1) 'onSave do reTotal() the earliest value that could have changed is BMA1 For i = 0 To 14 newGrid(0, i) = arrGrid(0, i) oldGrid(0, i) = arrGrid(0, i) newGrid(1, i) = 0.0 newGrid(2, i) = 0.0 newGrid(3, i) = 0.0 newGrid(4, i) = 0.0 newGrid(5, i) = 0.0 newGrid(6, i) = 0.0 newGrid(7, i) = 0.0 newGrid(8, i) = 0.0 newGrid(9, i) = 0.0 newGrid(10, i) = 0.0 newGrid(11, i) = 0.0 'newGrid(11, i) = "" 'newGrid(12, i) = "" 'newGrid(13, i) = "" 'newGrid(14, i) = "" Next 'load newGrid ds = db.doQueryDS(db.sql_get_PP(strProdCode, strStartDate, strEndDate)) For i = 1 To 15 'NOTE i = 1..15 'new import-opening balance 20081113 'arrAdditional(0, i - 1) = Math.Round(ds.Tables(0).Rows(i - 1)!dblOpeningImports, 3, MidpointRounding.AwayFromZero) 'If db.nz(ds.Tables(0).Rows(i - 1)!dblOpeningImports, 0) > 0 Then ' ds.Tables(0).Rows(i - 1)!dblOpening = ds.Tables(0).Rows(i - 1)!dblOpeningImports 'End If dr = ds.Tables(0).Rows(i - 1) For j = 1 To 10 ' "lblP", "lblOpening", "lblQF", "txtBMA", "lblActual", "txtOrders", "txtReceipts", "lblExports", "lblDamages", "lblRepack", "lblClosing", "lblMC", "lblLastUser", "lblDT", "lblLastUserSM", "lblLastEditSM" Try newGrid(j, i - 1) = Math.Round(dr.Item(j), 3, MidpointRounding.AwayFromZero) If chkDecimals.Checked = False Then newGrid(j, i - 1) = Math.Truncate(newGrid(j, i - 1)) End If Catch ex As Exception newGrid(j, i - 1) = (dr.Item(j)) End Try Try oldGrid(j, i - 1) = Math.Round(arrGrid(j, i - 1), 3, MidpointRounding.AwayFromZero) Catch ex As Exception oldGrid(j, i - 1) = arrGrid(j, i - 1) End Try Next Next 'still need to run the newGrid through the reTotal newGrid = reTotalArr(newGrid) oldGrid = reTotalArr(oldGrid) 'highlight changed controls '"lblP", "lblOpening", "lblQF", "txtBMA", "lblActual", "txtOrders", "txtReceipts", "lblDamages", "lblRepack", "lblClosing", "lblMC", "lblLastUser", "lblDT", "lblLastUserSM", "lblLastEditSM" Try For i = 1 To 10 'Opening to Closing For j = 0 To 14 'Month 1 to 15 Try 'asign values back to arrGrid strID = strColumn(i) & j + 1 If strID.Contains("txt") Then obj = New Web.UI.WebControls.TextBox Else obj = New Web.UI.WebControls.Label End If obj = FindControls(strID) Try arrGrid(i, j) = CDbl(db.nz(obj.text, 0)) Catch ex As Exception arrGrid(i, j) = 0.0 End Try 'If oldGrid(i, j) <> newGrid(i, j) Then 'And i < 10 Then 'check if db values have changed ' obj.ForeColor = Drawing.Color.Red ' blnMessage = True 'End If Catch ex As Exception 'blnMessage = True Finally obj = Nothing End Try Next Next 'If blnMessage = True Then 'if the pp values have changed since your last save... ' Dim strScript As New StringBuilder() ' strScript.Append("") ' RegisterClientScriptBlock("subscribescript", strScript.ToString()) ' btnSave.Enabled = False ' Return False 'Else 'db.doQueryDS(db.sql_delete_PP(strProdCode, strStartDate, strEndDate)) 'array's y = 1 PP For i = 3 To 14 'If blnUpdate = False Then If Math.Truncate(oldGrid(3, i)) <> Math.Truncate(arrGrid(3, i)) Or Math.Truncate(oldGrid(5, i)) <> Math.Truncate(arrGrid(5, i)) Then 'Response.Write("
" & j & ":" & i & " _ " & arrGrid(1, i) & " + " & arrDec(1, i) & "
" & db.sql_Update_PP(strProdCode, arrGrid(0, i), arrGrid(3, i), arrGrid(5, i))) db.doQueryDS(db.sql_Update_PP(strProdCode, arrGrid(0, i), arrGrid(3, i), arrGrid(5, i))) End If Next Global.Stock_Control.Import.updatePPBalance(strStartDate, strProdCode) LoadGrid() LoadSKUReport() 'End If Catch ex As Exception newGrid = Nothing oldGrid = Nothing Return False Finally newGrid = Nothing oldGrid = Nothing End Try Return True End Function Private Sub ddlDataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlBrand.DataBound, ddlPeriod.DataBound, ddlProduct.DataBound If Not Page.IsPostBack Then Dim ddl As DropDownList Dim item As New ListItem("-Please Select-", -1) ddl = sender ddl.Items.Insert(0, item) 'Dim item1 As New ListItem("-All Products-", 0) End If End Sub Private Sub ddlBrand_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlBrand.SelectedIndexChanged sdsProduct.FilterExpression = "refBrandID = " & ddlBrand.SelectedValue btnLoad.Enabled = False 'ddlProduct.Items.Insert(0, "-All Products-") intBrandID = ddlBrand.SelectedValue ClearGrid() LoadSKUReport() btnLoad.Enabled = True End Sub Private Sub ddlPeriod_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlPeriod.PreRender, ddlProduct.PreRender, ddlBrand.PreRender If Not Page.IsPostBack Then Dim ddl As DropDownList ddl = sender Try Select Case ddl.ID Case "ddlBrand" ddlBrand.Items.FindByValue(intBrandID).Selected = True Case "ddlPeriod" If strPeriod <> "" Then ddlPeriod.Items.FindByValue(strPeriod).Selected = True Else ddlPeriod.Items.FindByValue(Date.Today.ToString("yyyyMM")).Selected = True End If Case "ddlProduct" ddlProduct.Items.FindByValue(strProdCode).Selected = True End Select Catch ex As Exception ddl.ClearSelection() 'ddlPeriod.SelectedIndex = -1 End Try Else Try If ddlProduct.Items.FindByValue(0).Selected = False Then 'do nothing End If Catch ex As Exception Dim item As New ListItem("-All Products-", 0) ddlProduct.Items.Insert(0, item) End Try End If End Sub Public Function reTotalArr(ByVal arr As Object(,)) As Object(,) Dim x As Integer Dim dblBalance, dblSales, dblOrders As Double For x = 0 To 14 Try If arrAdditional(0, x) > 0 Then dblBalance = arrAdditional(0, x) Else dblBalance = arr(1, x) End If dblSales = 0 dblOrders = 0 Select Case arr(0, x) Case Is < strDate dblSales = arr(4, x) dblOrders = arr(6, x) Case Is = strDate If CDbl(arr(4, x)) <> CDbl(0) Then dblSales = arr(4, x) Else dblSales = arr(2, x) + arr(3, x) End If If CDbl(arr(6, x)) <> CDbl(0) Then dblOrders = arr(6, x) Else dblOrders = arr(5, x) End If Case Is > strDate dblSales = arr(2, x) + arr(3, x) dblOrders = arr(5, x) End Select dblBalance = Math.Round(dblBalance - arr(7, x) + arr(8, x) + arrGrid(9, x) - dblSales + dblOrders, 3, MidpointRounding.AwayFromZero) arr(10, x) = dblBalance If CDbl(arr(10, x)) = 0.0 And CDbl(arr(1, x + 1)) <> 0.0 Then 'And arrGrid(0, i - 1) < strDate Then Else arr(1, x + 1) = dblBalance If arrAdditional(0, x + 1) > 0 Then arr(1, x + 1) = arrAdditional(0, x + 1) Else arr(1, x + 1) = dblBalance End If End If Catch ioorEx As IndexOutOfRangeException Catch ex As Exception End Try Next Return arr End Function Public Sub reTotal(ByVal obj As Web.UI.WebControls.TextBox, ByVal x As Integer, ByVal y As Integer) Dim opening, closing, mc, objA, objQF As Web.UI.WebControls.Label Dim objO, objR, objBMA As Web.UI.WebControls.TextBox Dim strID As String Dim i As Integer Dim value, dblMC, dblBalance, decBalance, tmp As Double Dim dr As DataRow Dim dt As New DataTable("ExportPivot") 'Dim dblTotal(10) As Double decBalance = 0.0 'v1.2.8b - 20091006 - new export pivoted dt.Columns.Add("Headings") dr = dt.NewRow() dr.Item("Headings") = "Opening" dt.Rows.Add(dr) dr = dt.NewRow() dr.Item("Headings") = "Forecast" dt.Rows.Add(dr) dr = dt.NewRow() dr.Item("Headings") = "BMA" dt.Rows.Add(dr) dr = dt.NewRow() dr.Item("Headings") = "Actual" dt.Rows.Add(dr) dr = dt.NewRow() dr.Item("Headings") = "Orders" dt.Rows.Add(dr) dr = dt.NewRow() dr.Item("Headings") = "Receipts" dt.Rows.Add(dr) dr = dt.NewRow() dr.Item("Headings") = "Exports" dt.Rows.Add(dr) dr = dt.NewRow() dr.Item("Headings") = "Damages" dt.Rows.Add(dr) dr = dt.NewRow() dr.Item("Headings") = "Repack" dt.Rows.Add(dr) dr = dt.NewRow() dr.Item("Headings") = "Closing" dt.Rows.Add(dr) dr = dt.NewRow() dr.Item("Headings") = "Months" dt.Rows.Add(dr) dr = Nothing 'dsExportPivot.Tables(0).Rows.Add(new Object() = {"Opening", "Forecast", "BM Adj","Actual", "Orders", "Receipts", "Exports", "Damages", "Repack", "Closing", "Months"}]) Try For i = y To 15 Try objQF = FindControls(strColumn(2) & i) objBMA = FindControls(strColumn(3) & i) objA = FindControls(strColumn(4) & i) objO = FindControls(strColumn(5) & i) objR = FindControls(strColumn(6) & i) strID = strColumn(1) & i opening = FindControls(strID) If arrAdditional(0, i - 1) > 0 Then opening.Text = arrAdditional(0, i - 1) opening.ForeColor = Drawing.Color.DarkViolet End If dblBalance = CDbl(sqlDB.nz(opening.Text, 0)) If arrGrid(0, i - 1) < strDate Then 'if gridDate < currentDate then -Actual else if Actual > 0 : -Actual else -QF - BMA dblBalance -= (arrGrid(4, i - 1) + closingDifference(i - 1)) value = CDbl(objR.Text) Else 'v1.2.8a - 20091006: And chkUseQF.Checked = False If (arrGrid(4, i - 1) + closingDifference(i - 1)) > 0 And chkUseQF.Checked = False Then ' if actuals > 0 use actuals else use QF dblBalance -= (arrGrid(4, i - 1) + closingDifference(i - 1)) Else dblBalance -= (arrGrid(2, i - 1) + CDbl(objBMA.Text)) End If 'v1.2.8a - 20091006: try{} Try If arrGrid(0, i - 1) = strDate Then If chkUseQF.Checked = False Then objA.Font.Underline = True objR.Font.Underline = True objQF.Font.Underline = False objBMA.Font.Underline = False objO.Font.Underline = False Else objA.Font.Underline = False objR.Font.Underline = False objQF.Font.Underline = True objBMA.Font.Underline = True objO.Font.Underline = True End If End If Catch ex As Exception End Try If objR.Text <> "" And objR.Text <> "0" And chkUseQF.Checked = False Then 'same .. if Receipts use Receipts else use Orders 'v1.2.8a - 20091008 value = CDbl(objR.Text) Else value = CDbl(objO.Text) End If End If 'v1.2.8a - 20091008 If chkUseQF.Checked = True And arrGrid(0, i - 1) = strDate Then dblBalance += value Else dblBalance += value - arrGrid(7, i - 1) + arrGrid(8, i - 1) + arrGrid(9, i - 1) End If strID = strColumn(10) & i closing = FindControls(strID) If chkDecimals.Checked = False Then closing.Text = Math.Truncate(dblBalance) arrDec(10, i - 1) += Math.Round(dblBalance - Math.Truncate(dblBalance)) Else closing.Text = Math.Round(dblBalance, 3, MidpointRounding.AwayFromZero) End If 'v1.2.8b - 20091006 dt.Columns.Add(arrGrid(0, i - 1), System.Type.GetType("System.Double")) dt.Rows(0).Item(arrGrid(0, i - 1)) = opening.Text dt.Rows(1).Item(arrGrid(0, i - 1)) = objQF.Text dt.Rows(2).Item(arrGrid(0, i - 1)) = objBMA.Text dt.Rows(3).Item(arrGrid(0, i - 1)) = objA.Text dt.Rows(4).Item(arrGrid(0, i - 1)) = objO.Text dt.Rows(5).Item(arrGrid(0, i - 1)) = objR.Text dt.Rows(6).Item(arrGrid(0, i - 1)) = arrGrid(7, i - 1) dt.Rows(7).Item(arrGrid(0, i - 1)) = arrGrid(8, i - 1) dt.Rows(8).Item(arrGrid(0, i - 1)) = arrGrid(9, i - 1) dt.Rows(9).Item(arrGrid(0, i - 1)) = closing.Text 'MC! Try strID = strColumn(11) & i mc = FindControls(strID) objBMA = FindControls("txtBMA" & i + 1) 'get next BMA from txt While dblBalance > (arrGrid(2, i + dblMC) + objBMA.Text) And dblMC < 15 - y - 1 dblBalance = dblBalance - CDbl(arrGrid(2, i + dblMC) + objBMA.Text) dblMC += 1 objBMA = FindControls("txtBMA" & i + 1 + dblMC) 'get next + mc End While If CDbl(arrGrid(2, i + dblMC) + objBMA.Text) = 0 Then dblMC = "15" Else dblMC = dblMC + dblBalance / CDbl(arrGrid(2, i + dblMC) + objBMA.Text) 'mc.Text = Math.Round(dblMC, 2, MidpointRounding.AwayFromZero) End If Catch ex As DivideByZeroException dblMC = "15" Catch ex As IndexOutOfRangeException If CDbl(arrGrid(2, i + dblMC - 1) + txtBMA15.Text) > 0 Then dblMC = dblMC + Math.Round(dblBalance / (CDbl(arrGrid(2, i + dblMC - 1) + txtBMA15.Text)), 2, MidpointRounding.AwayFromZero) Else dblMC = "15" End If If dblMC > 15 Then dblMC = 15 If dblMC < -15 Then dblMC = -15 'mc.Text = dblMC Catch ex As Exception ex = ex 'mc.Text = dblMC Finally mc.Text = Math.Round(dblMC, 2, MidpointRounding.AwayFromZero) End Try Select Case CDbl(mc.Text) Case Is > dblVariance + 0.5 ' mc.ForeColor = Drawing.Color.DarkCyan mc.ForeColor = Drawing.Color.Red Case Is < dblVariance - 0.5 ' mc.ForeColor = Drawing.Color.DarkOrange mc.ForeColor = Drawing.Color.Green Case Else mc.ForeColor = Drawing.Color.Black End Select 'v1.2.8b dt.Rows(10).Item(arrGrid(0, i - 1)) = mc.Text strID = strColumn(1) & i + 1 opening = FindControls(strID) If CDbl(closing.Text) = 0.0 And CDbl(arrGrid(1, i)) <> 0.0 Then 'And arrGrid(0, i - 1) < strDate Then 'strID = strColumn(1) & i + 1 Else If arrAdditional(0, i) > 0 Then 'new import-opening B 20081113 opening.Text = arrAdditional(0, i) Else opening.Text = closing.Text arrDec(1, i) = arrDec(10, i - 1) End If End If Catch ex As NullReferenceException Catch ex As Exception ex = ex End Try dblMC = 0 value = 0 Next Catch ex As Exception ex = ex 'Finally ' For i = 2 To 8 ' obj = FindControls("lblT" & i) ' obj.Text = Math.Round(dblTotal(i), 0, MidpointRounding.AwayFromZero) ' Next End Try 'v1.2.8b dt.Columns.Add("Totals", System.Type.GetType("System.String")) dt.Columns.Add("Averages", System.Type.GetType("System.String")) For i = 0 To dt.Rows.Count - 1 dt.Rows(i).Item(dt.Columns.Count - 2) = "=ROUND(SUM(B" & i + 2 & ":P" & i + 2 & "),2)" dt.Rows(i).Item(dt.Columns.Count - 1) = "=ROUND(AVERAGE(B" & i + 2 & ":P" & i + 2 & "),2)" Next dsExportPivot = New DataSet dsExportPivot.Tables.Add(dt) End Sub 'Protected Sub txt_TextChanged(ByVal sender As Object, ByVal e As EventArgs) Handles txtOrders1.TextChanged, txtOrders2.TextChanged, txtOrders3.TextChanged, txtOrders4.TextChanged, txtOrders5.TextChanged, txtOrders6.TextChanged, txtOrders7.TextChanged, txtOrders8.TextChanged, txtOrders9.TextChanged, txtOrders10.TextChanged, txtOrders11.TextChanged, txtOrders12.TextChanged, txtOrders13.TextChanged, txtOrders14.TextChanged, txtOrders15.TextChanged, txtBMA1.TextChanged, txtBMA2.TextChanged, txtBMA3.TextChanged, txtBMA4.TextChanged, txtBMA5.TextChanged, txtBMA6.TextChanged, txtBMA7.TextChanged, txtBMA8.TextChanged, txtBMA9.TextChanged, txtBMA10.TextChanged, txtBMA11.TextChanged, txtBMA12.TextChanged, txtBMA13.TextChanged, txtBMA14.TextChanged, txtBMA15.TextChanged ' Dim y, x As Integer ' If sender.id.ToString.Contains("Orders") Then ' y = CInt(sender.id.ToString.Substring(9)) ' x = 5 ' Else ' y = CInt(sender.id.ToString.Substring(6)) ' x = 3 ' End If 'reTotal(sender, x, y) 'End Sub Protected Sub ddlProduct_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles ddlProduct.SelectedIndexChanged, ddlPeriod.SelectedIndexChanged 'ClearGrid() Page.Validate() btnLoad.Enabled = False If Page.IsValid Then strProdCode = ddlProduct.SelectedValue strPeriod = ddlPeriod.SelectedValue intBrandID = ddlBrand.SelectedValue 'v1.2.8c strVarietal = ddlVarietal.SelectedValue strCatagory = ddlCategory.SelectedValue LoadGrid() End If btnLoad.Enabled = True End Sub Protected Sub btnCalculate_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnCalculate.Click, btnCalculate0.Click reTotal(txtBMA1, 1, 1) End Sub Protected Sub btnEmail_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnEmail.Click If Page.IsValid And ddlProduct.SelectedIndex > 0 Then If Save() Then If WebConfigurationManager.AppSettings("UGROUP").ToLower.Contains("brand") Then db.doQueryDS("DELETE FROM stkConfirm WHERE mstProdCode = '" & strProdCode & "' AND mstPeriod = '" & strPeriod & "'") db.doQueryDS(db.sql_insert_confirmBM(strProdCode, strDate, txtBMA4.Text, txtBMA5.Text, txtBMA6.Text, txtBMA7.Text, txtBMA8.Text, txtBMA9.Text, txtBMA10.Text, txtBMA11.Text, txtBMA12.Text, txtBMA13.Text, txtBMA14.Text, txtBMA15.Text)) LoadSKUReport() End If 'else if If WebConfigurationManager.AppSettings("UGROUP").ToLower.Contains("shipping") Then db.doQueryDS("DELETE FROM stkConfirmSM WHERE mstProdCode = '" & strProdCode & "' AND mstPeriod = '" & strPeriod & "'") db.doQueryDS(db.sql_insert_confirmSM(strProdCode, strDate, txtOrders4.Text, txtOrders5.Text, txtOrders6.Text, txtOrders7.Text, txtOrders8.Text, txtOrders9.Text, txtOrders10.Text, txtOrders11.Text, txtOrders12.Text, txtOrders13.Text, txtOrders14.Text, txtOrders15.Text)) LoadSKUReport() End If End If 'if save End If End Sub Private Sub LoadSKUReport() Dim ds As DataSet Dim dr, drC As DataRow Dim i As Integer Dim s, d, y, strEndDate, strHeading, strBMA1, strBMA2, strTR As String s = "white" strHeading = "SKU" lblReport.Text = "" 'get start and end dates Try y = strDate.Substring(0, 4) d = strDate.Substring(4, 2) If CInt(d) = 1 Or CInt(d) = 2 Then strEndDate = CInt(strDate) + 11 Else strEndDate = CInt(strDate) + 99 End If ds = db.doQueryDS("SELECT * FROM mstPeriod WHERE strPeriod Between '" & strDate & "' AND '" & strEndDate & "' ORDER BY strPeriod") For Each dr In ds.Tables(0).Rows strHeading &= "" & db.nz(db.sqlDate(dr!strPeriod & "010101", "MMM yy"), db.sqlDate()) & "" Next lblReport.Text = "" & strHeading & "" ds = db.doQueryDS(db.sql_get_brand_all_bma(intBrandID, strDate, strEndDate)) For i = 0 To ds.Tables(0).Rows.Count - 1 Try dr = ds.Tables(0).Rows(i) strBMA1 &= "" If dr!mstProdCode <> ds.Tables(0).Rows(i + 1)!mstProdCode Then strBMA1 = strTR & "" & strBMA1 & "" Try drC = db.getRow(db.sql_get_product_bma(dr!mstProdCode, strEndDate)) strBMA2 = getBMARow(drC, s) Catch ex As Exception strBMA2 = "" End Try lblReport.Text &= strBMA1 & strBMA2 strBMA1 = "" If s = "lightergrey" Then s = "white" Else s = "lightergrey" End If Catch ioorEx As IndexOutOfRangeException strBMA1 = "" & strBMA1 & "" Try drC = db.getRow(db.sql_get_product_bma(dr!mstProdCode, strEndDate)) strBMA2 = getBMARow(drC, s) Catch ex As Exception strBMA2 = "" End Try lblReport.Text &= strBMA1 & strBMA2 Catch ex As Exception End Try Next lblReport.Text &= "
" & Math.Round(dr!dblBMA, 3, MidpointRounding.AwayFromZero) & "
" & dr!strLongProdDesc & " x " & dr!intPack & "
   - No BMA records found -
" & dr!strLongProdDesc & " x " & dr!intPack & "
   - No BMA records found -
" Catch End Try End Sub Public Shared Function monthDiff(ByVal pDate) As Integer Dim y, d As String y = pDate.Substring(0, 4) d = pDate.Substring(4, 2) Select Case strDate.Substring(0, 4) Case Is = y Return CInt(strDate.Substring(4, 2)) - CInt(d) Case Is > y y = CInt(strDate.Substring(0, 4)) - CInt(y) Return (CInt(strDate.Substring(4, 2)) + (y * 12)) - CInt(d) Case Is < y Return -1 End Select End Function Public Shared Function monthDiff(ByVal pDate, ByVal compareDate) As Integer Dim y, d As String y = pDate.Substring(0, 4) d = pDate.Substring(4, 2) Select Case compareDate.Substring(0, 4) Case Is = y Return CInt(compareDate.Substring(4, 2)) - CInt(d) Case Is > y y = CInt(compareDate.Substring(0, 4)) - CInt(y) Return (CInt(compareDate.Substring(4, 2)) + (y * 12)) - CInt(d) Case Is < y Return -1 End Select End Function Private Function getBMARow(ByVal drC As DataRow, ByVal s As String) As String getBMARow = "   " & drC!strLastUser & " - " & db.sqlDate(drC!dtStamp, "d MMM yy") & "" 'check diff in dates Dim x As Integer = monthDiff(drC!mstPeriod) For j = x To 11 getBMARow &= "" & Math.Round(drC.Item(j), 3, MidpointRounding.AwayFromZero) & "" Next For j = 11 - x + 1 To 11 getBMARow &= " " Next getBMARow &= "" Return getBMARow End Function End Class