动态DataTable-Vb.Net
Partial Public Class SupplierSalesStatsControl
Inherits System.Web.UI.UserControl
Private m_Item As Order
Private m_ItemUser As User
Private m_season As Season
Private m_ItemCustomer As Customer
Private m_ItemDelivery As Delivery
Private m_isHeadOfficeUser As Boolean = False
Private m_userOfficeID As Integer
Private m_UserID As Integer
Private m_UserCategory As User.UserCategories
Private m_SeasonSummary As SeasonSummary
Private m_TotalPieces As Integer = 0I
Private m_hashTableLotsMargin As Hashtable
Private m_hashSupplierCustomer As Hashtable
Private m_hashCustomerOrder As Hashtable
Private m_arrAllDataGrids As New ArrayList
Private m_penaltySupplier As Decimal = 0D
Private m_claimSupplier As Decimal = 0D
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Me.m_UserID = CType(Context.User.Identity.Name, Integer)
Me.m_isHeadOfficeUser = Garzone.User.isHeadOfficeUser(Me.m_UserID)
Me.m_userOfficeID = Garzone.User.getOfficeID(Me.m_UserID)
If Me.m_ItemUser Is Nothing Then
Me.m_ItemUser = New User(True)
End If
If Me.dropSeason.Items.Count = 0 Then
Me.m_season = New Season
Me.dropSeason.DataSource = Me.m_season.DataSetSeason.Seasons
Me.dropSeason.DataTextField = "Name"
Me.dropSeason.DataValueField = "SeasonID"
Me.dropSeason.DataBind()
Season.selectCurrentSeason(Me.dropSeason, Me.m_season.DataSetSeason.Seasons)
BindData()
End If
End Sub
Private Sub BindData()
If Not Me.dropSeason.SelectedItem Is Nothing Then
Try
Dim _vwOrderRow As OrderDataSet.vw_OrderViewRow
Dim _oneLotRow As OrderDataSet.OrderLotRow
Dim _marginCalculator As Garzone.SaleCalculation
Dim _hashSuppliers As New Hashtable
Me.m_hashSupplierCustomer = New Hashtable
Me.m_hashCustomerOrder = New Hashtable
Dim _tableSuppliers As DataTable
Dim _hastable As Hashtable
Dim _BuyingPrice As Decimal = 0D
Dim _SellingPrice As Decimal = 0D
Dim _ExchangeRatePurchase As Decimal = 1
Dim _ExchangeRateSelling As Decimal = 1
Me.m_Item = New Order
Me.m_Item.fillBySeasonID(CType(Me.dropSeason.SelectedItem.Value, Integer))
Me.m_ItemCustomer = New Customer(True)
Me.m_ItemCustomer.FillAll()
Me.m_ItemDelivery = New Delivery
Me.m_ItemDelivery.fillBySeasonID(CType(Me.dropSeason.SelectedItem.Value, Integer))
'initalize the season summary for all season
m_SeasonSummary = New SeasonSummary
m_SeasonSummary.m_Item = Me.m_Item
m_SeasonSummary.m_ItemDelivery = Me.m_ItemDelivery
m_SeasonSummary.m_OfficeID = Me.m_userOfficeID
Me.m_SeasonSummary.isHeaderOfficeUser = Me.m_isHeadOfficeUser
m_SeasonSummary.CreateSummary()
Me.m_hashTableLotsMargin = New Hashtable
For Each _vwOrderRow In Me.m_Item.DatasetOrder.vw_OrderView
Select Case CType(_vwOrderRow.InternalValidationStatusID, Order.InternalValidationStatus)
Case Order.InternalValidationStatus.Authorized, Order.InternalValidationStatus.Completed, Order.InternalValidationStatus.Inproduction, Order.InternalValidationStatus.ProductionDocumentsReceived
If _hashSuppliers.ContainsKey(_vwOrderRow.SupplierID) = False Then
_hashSuppliers.Add(_vwOrderRow.SupplierID, New ArrayList)
End If
If Me.m_hashCustomerOrder.ContainsKey(_vwOrderRow.CustomerID) = False Then
Me.m_hashCustomerOrder.Add(_vwOrderRow.CustomerID, New ArrayList)
End If
If Me.m_hashSupplierCustomer.ContainsKey(_vwOrderRow.SupplierID) = False Then
Me.m_hashSupplierCustomer.Add(_vwOrderRow.SupplierID, New ArrayList)
End If
For Each _oneLotRow In Me.m_Item.DatasetOrder.Orders.FindByOrderID(_vwOrderRow.OrderID).GetOrderLotRows
_marginCalculator = New Garzone.SaleCalculation
If _oneLotRow.IsFinanceTotalDaysNull = False Then
'Apply finance information to the calculator
_marginCalculator.InterestPeriod = _oneLotRow.FinanceTotalDays
_marginCalculator.InterestRate = _oneLotRow.FinanceInterestRate
End If
If Not _oneLotRow.IsBuyingPriceNull Then
If Not _oneLotRow.IsExchangeRatePurchaseNull Then
_ExchangeRatePurchase = _oneLotRow.ExchangeRatePurchase
End If
_BuyingPrice = _oneLotRow.BuyingPrice * _ExchangeRatePurchase
End If
If Not _oneLotRow.IsSellingPriceNull Then
If Not _oneLotRow.IsExchangeRateSellingNull Then
_ExchangeRateSelling = _oneLotRow.ExchangeRateSelling
End If
_SellingPrice = _oneLotRow.SellingPrice * _ExchangeRateSelling
End If
'Check DataValid
_hastable = New Hashtable()
_hastable = _marginCalculator.CheckIsNullValid(_vwOrderRow)
_marginCalculator.CalculateMargin(CType(_hastable("Sale_BC"), Decimal), CType(_hastable("Sale_SC"), Decimal), CType(_hastable("Sale_SCB"), Decimal), CType(_hastable("Sale_WMC"), Decimal), CType(_hastable("Sale_SVAT"), Decimal), CType(_hastable("Sale_IP"), Decimal), CType(_hastable("Sale_LCB"), Decimal), CType(_hastable("Sale_ICB"), Decimal), CType(_hastable("Sale_OC"), Decimal), CType(_hastable("Purchase_FC"), Decimal), CType(_hastable("Purchase_LCM"), Decimal), CType(_hastable("Purchase_AC"), Decimal), CType(_hastable("Purchase_ICM"), Decimal), CType(_hastable("Purchase_BVAT"), Decimal), CType(_hastable("Purchase_ICP"), Decimal), CType(_hastable("Purchase_ICA"), Decimal), CType(_hastable("Purchase_OCP"), Decimal), CType(_hastable("Purchase_OCA"), Decimal), _BuyingPrice, _SellingPrice, _vwOrderRow.OrderSalesCategory)
Me.m_hashTableLotsMargin.Add(_oneLotRow.OrderLotID, _marginCalculator)
Next
'Add the lot information to the hash table
CType(_hashSuppliers.Item(_vwOrderRow.SupplierID), ArrayList).Add(_vwOrderRow.OrderID)
CType(Me.m_hashCustomerOrder.Item(_vwOrderRow.CustomerID), ArrayList).Add(_vwOrderRow.OrderID)
If CType(Me.m_hashSupplierCustomer.Item(_vwOrderRow.SupplierID), ArrayList).Contains(_vwOrderRow.CustomerID) = False Then
CType(Me.m_hashSupplierCustomer.Item(_vwOrderRow.SupplierID), ArrayList).Add(_vwOrderRow.CustomerID)
End If
End Select
'End If
Next
'Bind Suppier
_tableSuppliers = Me.TransmitHashSupplierTable(_hashSuppliers)
Dim _row As DataRow = _tableSuppliers.NewRow
_row.Item("SupplierID") = -1
_row.Item("SupplierName") = "Summary"
_row.Item("TotalPieces") = Me.m_TotalPieces
_row.Item("Orders") = Me.m_SeasonSummary.TotalOrders
_row.Item("PurchaseAmount") = -Me.m_SeasonSummary.TotalSalesBuy
_row.Item("PurchasePercentage") = 1
_row.Item("Claims") = -Me.m_claimSupplier
_row.Item("Penalty") = Me.m_penaltySupplier
_row.Item("InspectionFail") = Me.m_SeasonSummary.TotalInspectionFailed.ToString & "/" & Me.m_SeasonSummary.TotalInspection.ToString
If Me.m_SeasonSummary.TotalInspection <> 0 Then
_row.Item("FailedP") = Me.m_SeasonSummary.TotalInspectionFailed / Me.m_SeasonSummary.TotalInspection
Else
_row.Item("FailedP") = 0
End If
_row.Item("Delivery") = Me.m_SeasonSummary.AverageDelivery
_row.Item("LowMargin") = Me.m_SeasonSummary.LowMargin
_row.Item("AvgMargin") = Me.m_SeasonSummary.AvgMargin
_row.Item("HighMargin") = Me.m_SeasonSummary.HighMargin
_row.Item("SubTotal") = Me.m_SeasonSummary.SubTotal - Me.m_SeasonSummary.TotalClaim + Me.m_SeasonSummary.TotalPenalty
_row.Item("SubTotalOrdered") = Me.m_SeasonSummary.SubTotalOrdered - Me.m_SeasonSummary.TotalClaim + Me.m_SeasonSummary.TotalPenalty
If Me.m_SeasonSummary.SubTotalOrdered - Me.m_SeasonSummary.TotalClaim + Me.m_SeasonSummary.TotalPenalty = 0 Then
_row.Item("SubTotalCompare") = 0
Else
_row.Item("SubTotalCompare") = (Me.m_SeasonSummary.SubTotal - Me.m_SeasonSummary.TotalClaim + Me.m_SeasonSummary.TotalPenalty) / (Me.m_SeasonSummary.SubTotalOrdered - Me.m_SeasonSummary.TotalClaim + Me.m_SeasonSummary.TotalPenalty)
End If
'If no order detect don't add this summary
If m_SeasonSummary.TotalOrders <> 0 Then
_tableSuppliers.Rows.Add(_row)
End If
_tableSuppliers.DefaultView.Sort = "PurchaseAmount DESC"
Me.gvSupplier.DataSource = _tableSuppliers.DefaultView
Me.gvSupplier.DataBind()
Me.gvSupplier.Visible = Me.gvSupplier.Rows.Count > 0
Catch ex As Exception
Garzone.ExceptionManager.HandleException(ex, "Error on supplier sales season stats for " & Garzone.User.getOfficeName(Me.dropSeason.SelectedItem.Value))
Me.gvSupplier.Visible = False
End Try
End If
End Sub
Private Function GenerateCustomerSalesBySupplierID(ByVal _supplierID As Integer) As DataTable
Dim _dataTable As New DataTable
Dim _row As DataRow
Dim _customerID As Integer
Dim _vwOrderRow As OrderDataSet.vw_OrderViewRow
Dim _vwOrderRows As OrderDataSet.vw_OrderViewRow()
Dim _customerRow As CustomerDataSet.CustomersRow
Dim _oneLotRow As OrderDataSet.OrderLotRow
Dim _oneOrderClaimRow As OrderDataSet.OrderClaimRow
Dim _oneInternalInspection As OrderDataSet.OrdersInternalInspectionsRow
Dim _vwDeliveryRow As DeliveryDataSet.vw_DeliveryShipmentViewRow
Dim _oneOrderInspectionRow As OrderDataSet.OrdersInspectionsRow
Dim _rowOrderLotShipment As DeliveryDataSet.OrderLotShipmentRow
Dim _rowsOrderLotShipment As DeliveryDataSet.OrderLotShipmentRow()
Dim _numOfOrders As New ArrayList
Dim _total As Decimal = 0D
Dim _subTotal As Decimal
Dim _subTotalOrdered As Decimal = 0D
Dim _totalPcs As Integer
Dim _totalNet As Decimal = 0D
Dim _totalBuy As Decimal = 0D
Dim _totalClaims As Decimal = 0D
Dim _claimSupplier As Decimal = 0D
Dim _totalPenalty As Decimal = 0D
Dim _penaltySupplier As Decimal = 0D
Dim _totalInspection As Integer = 0I
Dim _totalInspectionFailed As Integer = 0I
Dim _InspectionFailedPercentage As Decimal
Dim _totalDelivery As Integer = 0I
Dim _averageDelivery As Decimal = 0D
Dim _deliveryContainer As ArrayList
Dim _lowMargin As Decimal = 0D
Dim _currentMargin As Decimal = 0D
Dim _avgMargin As Decimal = 0D
Dim _highMargin As Decimal = 0D
Dim _marginCalculator As Garzone.SaleCalculation
_dataTable.Columns.Add(New DataColumn("CustomerName", GetType(String)))
_dataTable.Columns.Add(New DataColumn("Orders", GetType(Integer)))
_dataTable.Columns.Add(New DataColumn("TotalPieces", GetType(Integer)))
_dataTable.Columns.Add(New DataColumn("PurchaseAmount", GetType(Decimal)))
_dataTable.Columns.Add(New DataColumn("PurchasePercentage", GetType(Decimal)))
_dataTable.Columns.Add(New DataColumn("Claims", GetType(Decimal)))
_dataTable.Columns.Add(New DataColumn("Penalty", GetType(Decimal)))
_dataTable.Columns.Add(New DataColumn("InspectionFail", GetType(String)))
_dataTable.Columns.Add(New DataColumn("FailedP", GetType(Decimal)))
_dataTable.Columns.Add(New DataColumn("Delivery", GetType(Integer)))
_dataTable.Columns.Add(New DataColumn("LowMargin", GetType(Decimal)))
_dataTable.Columns.Add(New DataColumn("AvgMargin", GetType(Decimal)))
_dataTable.Columns.Add(New DataColumn("HighMargin", GetType(Decimal)))
_dataTable.Columns.Add(New DataColumn("SubTotal", GetType(Decimal)))
_dataTable.Columns.Add(New DataColumn("SubTotalOrdered", GetType(Decimal)))
_dataTable.Columns.Add(New DataColumn("SubTotalCompare", GetType(Decimal)))
For Each _customerID In Me.m_hashSupplierCustomer.Item(_supplierID)
_total = 0
_subTotal = 0D
_subTotalOrdered = 0D
_totalNet = 0
_totalBuy = 0
_totalClaims = 0
_claimSupplier = 0D
_totalPenalty = 0
_penaltySupplier = 0D
_totalPcs = 0
_totalInspection = 0I
_totalInspectionFailed = 0I
_InspectionFailedPercentage = 0D
_totalDelivery = 0I
_averageDelivery = 0D
_deliveryContainer = New ArrayList
_lowMargin = 0D
_currentMargin = 0D
_avgMargin = 0D
_highMargin = 0D
_numOfOrders = New ArrayList
Dim counter As Integer = 0
Dim _arrMarginContainer As New ArrayList
Dim _totalMargin As Decimal = 0D
_customerRow = Me.m_ItemCustomer.DatasetCustomer.Customers.FindByCustomerID(_customerID)
_vwOrderRows = Me.m_Item.DatasetOrder.vw_OrderView.Select("Supplier and CustomerID=" & _customerID.ToString)
For Each _vwOrderRow In _vwOrderRows
' If Me.m_isHeadOfficeUser = True Or Me.m_userOfficeID = _vwOrderRow.BranchOfficeID Then
Select Case CType(_vwOrderRow.InternalValidationStatusID, Order.InternalValidationStatus)
Case Order.InternalValidationStatus.Authorized, Order.InternalValidationStatus.Completed, Order.InternalValidationStatus.Inproduction, Order.InternalValidationStatus.ProductionDocumentsReceived
For Each _oneLotRow In Me.m_Item.DatasetOrder.Orders.FindByOrderID(_vwOrderRow.OrderID).GetOrderLotRows
If _numOfOrders.Contains(_oneLotRow.OrderID) = False Then
_numOfOrders.Add(_oneLotRow.OrderID)
End If
'Get the calculator already created
_marginCalculator = Me.m_hashTableLotsMargin.Item(_oneLotRow.OrderLotID)
_rowsOrderLotShipment = Me.m_ItemDelivery.DatasetDelivery.OrderLotShipment.Select("OrderLotID=" & _oneLotRow.OrderLotID.ToString)
If _oneLotRow.IsBuyingPriceNull = False Then
For Each _rowOrderLotShipment In _rowsOrderLotShipment
_totalBuy += (_oneLotRow.BuyingPrice * _oneLotRow.ExchangeRatePurchase * _rowOrderLotShipment.QuantityShipped)
Next
End If
If _oneLotRow.IsSellingPriceNull = False Then
_subTotalOrdered += (_marginCalculator.Margin * _oneLotRow.QuantityOrdered * _oneLotRow.ExchangeRateSelling * _oneLotRow.SellingPrice)
For Each _rowOrderLotShipment In _rowsOrderLotShipment
_total += (_rowOrderLotShipment.QuantityShipped * _oneLotRow.SellingPrice * _oneLotRow.ExchangeRateSelling)
_totalNet += (_marginCalculator.GrossSalePrice * _rowOrderLotShipment.QuantityShipped)
_subTotal += (_oneLotRow.SellingPrice * _oneLotRow.ExchangeRateSelling * _rowOrderLotShipment.QuantityShipped * _marginCalculator.Margin)
Next
End If
For Each _rowOrderLotShipment In _rowsOrderLotShipment
_totalPcs += _rowOrderLotShipment.QuantityShipped
Next
'Current margin
If _oneLotRow.SellingPrice <> 0 Then
_currentMargin = _marginCalculator.Margin
Else
_currentMargin = 0D
End If
_arrMarginContainer.Add(_currentMargin)
'Margin Low and high
If counter = 0 Then
_lowMargin = _currentMargin
_highMargin = _currentMargin
End If
_lowMargin = SeasonSummary.PopUpDecimal(_currentMargin, _lowMargin, False)
_highMargin = SeasonSummary.PopUpDecimal(_currentMargin, _highMargin, True)
'Claims calculation
For Each _oneOrderClaimRow In _oneLotRow.GetOrderClaimRows
_totalClaims += (_oneOrderClaimRow.ClaimTotalAmount - _oneOrderClaimRow.VenderAmount)
_claimSupplier += _oneOrderClaimRow.VenderAmount
Next
'Penalty calculation
For Each _rowOrderLotShipment In _rowsOrderLotShipment
If _rowOrderLotShipment.IsMakerPenaltyValueNull = False Then
_totalPenalty += ((_rowOrderLotShipment.MakerPenaltyValue / 100) * _oneLotRow.BuyingPrice * _oneLotRow.ExchangeRatePurchase * _rowOrderLotShipment.QuantityShipped)
_penaltySupplier += ((_rowOrderLotShipment.MakerPenaltyValue / 100) * _oneLotRow.BuyingPrice * _oneLotRow.ExchangeRatePurchase * _rowOrderLotShipment.QuantityShipped)
End If
If _rowOrderLotShipment.IsCustomerPenaltyValueNull = False Then
_totalPenalty -= ((_rowOrderLotShipment.CustomerPenaltyValue / 100) * _oneLotRow.SellingPrice * _oneLotRow.ExchangeRateSelling * _rowOrderLotShipment.QuantityShipped)
End If
Next
'Inspection
'Internal Inspection calculation
For Each _oneInternalInspection In _oneLotRow.GetOrdersInternalInspectionsRows
If _oneInternalInspection.IsInspectionResultIDNull = False Then
Select Case CType(_oneInternalInspection.InspectionResultID, Inspection.InspectionsResults)
Case Inspection.InspectionsResults.Failed
_totalInspectionFailed += 1
End Select
End If
Next
_totalInspection += _oneLotRow.GetOrdersInternalInspectionsRows.Length
'Third party inspections calculation
For Each _oneOrderInspectionRow In _oneLotRow.GetOrdersInspectionsRows
If _oneOrderInspectionRow.IsResultIDNull = False Then
Select Case CType(_oneOrderInspectionRow.ResultID, Inspection.InspectionsResults)
Case Inspection.InspectionsResults.Failed
_totalInspectionFailed += 1
End Select
End If
Next
_totalInspection += _oneLotRow.GetOrdersInspectionsRows.Length
'Increase the counter
counter += 1
Next
'Calculate Delivery
For Each _vwDeliveryRow In Me.m_ItemDelivery.DatasetDelivery.vw_DeliveryShipmentView.Select("OrderID=" & _vwOrderRow.OrderID.ToString)
If Not _vwDeliveryRow.IsDeliveryNull Then
_deliveryContainer.Add(_vwDeliveryRow.Delivery)
End If
Next
End Select
'End If
Next
If _total <> 0 Then
_avgMargin = (_totalNet - _totalBuy) / _total
Else
_avgMargin = 0D
End If
'Inspection failed
If _totalInspection <> 0 Then
_InspectionFailedPercentage = _totalInspectionFailed / _totalInspection
Else
_InspectionFailedPercentage = 0
End If
'Delivery
For Each _oneDelivery As Integer In _deliveryContainer
_totalDelivery += _oneDelivery
Next
If _deliveryContainer.Count = 0 Then
_averageDelivery = 0D
Else
_averageDelivery = _totalDelivery / _deliveryContainer.Count
End If
'Create one row for current customer
_row = _dataTable.NewRow
_row.Item("CustomerName") = _customerRow.Name
_row.Item("Orders") = _numOfOrders.Count
_row.Item("TotalPieces") = _totalPcs
_row.Item("PurchaseAmount") = -_totalBuy
If Me.m_SeasonSummary.TotalSalesBuy <> 0 Then
_row.Item("PurchasePercentage") = (_totalBuy / Me.m_SeasonSummary.TotalSalesBuy)
Else
_row.Item("PurchasePercentage") = 0
End If
_row.Item("Claims") = -_claimSupplier
_row.Item("Penalty") = _penaltySupplier
_row.Item("InspectionFail") = _totalInspectionFailed.ToString & "/" & _totalInspection.ToString
_row.Item("FailedP") = _InspectionFailedPercentage
_row.Item("Delivery") = _averageDelivery
_row.Item("LowMargin") = _lowMargin
_row.Item("AvgMargin") = _avgMargin
_row.Item("HighMargin") = _highMargin
_row.Item("SubTotal") = _subTotal - _totalClaims + _totalPenalty
_row.Item("SubTotalOrdered") = _subTotalOrdered - _totalClaims + _totalPenalty
If _subTotalOrdered - _totalClaims + _totalPenalty = 0 Then
_row.Item("SubTotalCompare") = 0
Else
_row.Item("SubTotalCompare") = (_subTotal - _totalClaims + _totalPenalty) / (_subTotalOrdered - _totalClaims + _totalPenalty)
End If
If _numOfOrders.Count > 0 Then
_dataTable.Rows.Add(_row)
End If
Next
Return _dataTable
End Function
Private Function TransmitHashSupplierTable(ByVal _hashTable As Hashtable) As DataTable
Dim _dataTable As New DataTable
Dim _item As DictionaryEntry
Dim _row As DataRow
Dim _supplierRow As OrderDataSet.SuppliersRow
Dim _oneLotRow As OrderDataSet.OrderLotRow
Dim _oneOrderClaimRow As OrderDataSet.OrderClaimRow
Dim _oneInternalInspection As OrderDataSet.OrdersInternalInspectionsRow
Dim _vwDeliveryRow As DeliveryDataSet.vw_DeliveryShipmentViewRow
Dim _oneOrderInspectionRow As OrderDataSet.OrdersInspectionsRow
Dim _rowOrderLotShipment As DeliveryDataSet.OrderLotShipmentRow
Dim _rowsOrderLotShipment As DeliveryDataSet.OrderLotShipmentRow()
Dim _numOfOrders As New ArrayList
Dim _total As Decimal = 0D
Dim _subTotal As Decimal = 0D
Dim _subTotalOrdered As Decimal = 0D
Dim _totalPieces As Integer = 0I
Dim _totalNet As Decimal = 0D
Dim _totalBuy As Decimal = 0D
Dim _totalClaims As Decimal = 0D
Dim _claimSupplier As Decimal = 0D
Dim _totalPenalty As Decimal = 0D
Dim _penaltySupplier As Decimal = 0D
Dim _totalInspection As Integer = 0I
Dim _totalInspectionFailed As Integer = 0I
Dim _InspectionFailedPercentage As Decimal
Dim _totalDelivery As Integer = 0I
Dim _averageDelivery As Decimal = 0D
Dim _deliveryContainer As ArrayList
Dim _lowMargin As Decimal = 0D
Dim _currentMargin As Decimal = 0D
Dim _avgMargin As Decimal = 0D
Dim _highMargin As Decimal = 0D
Dim _marginCalculator As Garzone.SaleCalculation
_dataTable.Columns.Add(New DataColumn("SupplierID", GetType(Integer)))
_dataTable.Columns.Add(New DataColumn("SupplierName", GetType(String)))
_dataTable.Columns.Add(New DataColumn("Orders", GetType(Integer)))
_dataTable.Columns.Add(New DataColumn("TotalPieces", GetType(Integer)))
_dataTable.Columns.Add(New DataColumn("PurchaseAmount", GetType(Decimal)))
_dataTable.Columns.Add(New DataColumn("PurchasePercentage", GetType(Decimal)))
_dataTable.Columns.Add(New DataColumn("Claims", GetType(Decimal)))
_dataTable.Columns.Add(New DataColumn("Penalty", GetType(Decimal)))
_dataTable.Columns.Add(New DataColumn("InspectionFail", GetType(String)))
_dataTable.Columns.Add(New DataColumn("FailedP", GetType(Decimal)))
_dataTable.Columns.Add(New DataColumn("Delivery", GetType(Integer)))
_dataTable.Columns.Add(New DataColumn("LowMargin", GetType(Decimal)))
_dataTable.Columns.Add(New DataColumn("AvgMargin", GetType(Decimal)))
_dataTable.Columns.Add(New DataColumn("HighMargin", GetType(Decimal)))
_dataTable.Columns.Add(New DataColumn("SubTotal", GetType(Decimal)))
_dataTable.Columns.Add(New DataColumn("SubTotalOrdered", GetType(Decimal)))
_dataTable.Columns.Add(New DataColumn("SubTotalCompare", GetType(Decimal)))
For Each _item In _hashTable
_total = 0
_totalPieces = 0
_totalNet = 0
_subTotal = 0D
_subTotalOrdered = 0D
_totalBuy = 0D
_totalClaims = 0D
_claimSupplier = 0D
_totalPenalty = 0D
_penaltySupplier = 0D
_totalInspection = 0I
_totalInspectionFailed = 0I
_InspectionFailedPercentage = 0D
_totalDelivery = 0I
_averageDelivery = 0D
_deliveryContainer = New ArrayList
_lowMargin = 0D
_currentMargin = 0D
_avgMargin = 0D
_highMargin = 0D
_numOfOrders = New ArrayList
Dim counter As Integer = 0
Dim _arrMarginContainer As New ArrayList
Dim _totalMargin As Decimal = 0D
_supplierRow = Me.m_Item.DatasetOrder.Suppliers.FindBySupplierID(CType(_item.Key, Integer))
For Each _oneOrderID As Integer In CType(_item.Value, ArrayList)
For Each _oneLotRow In Me.m_Item.DatasetOrder.Orders.FindByOrderID(_oneOrderID).GetOrderLotRows
If _numOfOrders.Contains(_oneLotRow.OrderID) = False Then
_numOfOrders.Add(_oneLotRow.OrderID)
End If
'Get the calculator already created
_marginCalculator = Me.m_hashTableLotsMargin.Item(_oneLotRow.OrderLotID)
_rowsOrderLotShipment = Me.m_ItemDelivery.DatasetDelivery.OrderLotShipment.Select("OrderLotID=" & _oneLotRow.OrderLotID.ToString)
If _oneLotRow.IsSellingPriceNull = False Then
_subTotalOrdered += _marginCalculator.Margin * _oneLotRow.QuantityOrdered * _oneLotRow.ExchangeRateSelling * _oneLotRow.SellingPrice
For Each _rowOrderLotShipment In _rowsOrderLotShipment
_total += (_oneLotRow.SellingPrice * _oneLotRow.ExchangeRateSelling * _rowOrderLotShipment.QuantityShipped)
_totalNet += (_marginCalculator.GrossSalePrice * _rowOrderLotShipment.QuantityShipped)
_subTotal += (_oneLotRow.SellingPrice * _oneLotRow.ExchangeRateSelling * _rowOrderLotShipment.QuantityShipped * _marginCalculator.Margin)
Next
End If
If _oneLotRow.IsBuyingPriceNull = False Then
For Each _rowOrderLotShipment In _rowsOrderLotShipment
_totalBuy += (_oneLotRow.BuyingPrice * _oneLotRow.ExchangeRatePurchase * _rowOrderLotShipment.QuantityShipped)
Next
End If
For Each _rowOrderLotShipment In _rowsOrderLotShipment
_totalPieces += _rowOrderLotShipment.QuantityShipped
Me.m_TotalPieces += _rowOrderLotShipment.QuantityShipped
Next
'Current margin
If _oneLotRow.SellingPrice <> 0 Then
_currentMargin = _marginCalculator.Margin
Else
_currentMargin = 0D
End If
_arrMarginContainer.Add(_currentMargin)
'Margin Low and high
If counter = 0 Then
_lowMargin = _currentMargin
_highMargin = _currentMargin
End If
_lowMargin = SeasonSummary.PopUpDecimal(_currentMargin, _lowMargin, False)
_highMargin = SeasonSummary.PopUpDecimal(_currentMargin, _highMargin, True)
'Claims calculation
For Each _oneOrderClaimRow In _oneLotRow.GetOrderClaimRows
_totalClaims += (_oneOrderClaimRow.ClaimTotalAmount - _oneOrderClaimRow.VenderAmount)
_claimSupplier += _oneOrderClaimRow.VenderAmount
Me.m_claimSupplier += _oneOrderClaimRow.VenderAmount
Next
'Internal Inspection calculation
For Each _oneInternalInspection In _oneLotRow.GetOrdersInternalInspectionsRows
If _oneInternalInspection.IsInspectionResultIDNull = False Then
Select Case CType(_oneInternalInspection.InspectionResultID, Inspection.InspectionsResults)
Case Inspection.InspectionsResults.Failed
_totalInspectionFailed += 1
End Select
End If
Next
_totalInspection += _oneLotRow.GetOrdersInternalInspectionsRows.Length
'Third party inspections calculation
For Each _oneOrderInspectionRow In _oneLotRow.GetOrdersInspectionsRows
If _oneOrderInspectionRow.IsResultIDNull = False Then
Select Case CType(_oneOrderInspectionRow.ResultID, Inspection.InspectionsResults)
Case Inspection.InspectionsResults.Failed
_totalInspectionFailed += 1
End Select
End If
Next
_totalInspection += _oneLotRow.GetOrdersInspectionsRows.Length
'Penalty calculation
For Each _rowOrderLotShipment In Me.m_ItemDelivery.DatasetDelivery.OrderLotShipment.Select("OrderLotID=" & _oneLotRow.OrderLotID.ToString)
If _rowOrderLotShipment.IsMakerPenaltyValueNull = False Then
_totalPenalty += ((_rowOrderLotShipment.MakerPenaltyValue / 100) * _oneLotRow.BuyingPrice * _oneLotRow.ExchangeRatePurchase * _rowOrderLotShipment.QuantityShipped)
_penaltySupplier += ((_rowOrderLotShipment.MakerPenaltyValue / 100) * _oneLotRow.BuyingPrice * _oneLotRow.ExchangeRatePurchase * _rowOrderLotShipment.QuantityShipped)
Me.m_penaltySupplier += ((_rowOrderLotShipment.MakerPenaltyValue / 100) * _oneLotRow.BuyingPrice * _oneLotRow.ExchangeRatePurchase * _rowOrderLotShipment.QuantityShipped)
End If
If _rowOrderLotShipment.IsCustomerPenaltyValueNull = False Then
_totalPenalty -= ((_rowOrderLotShipment.CustomerPenaltyValue / 100) * _oneLotRow.SellingPrice * _oneLotRow.ExchangeRateSelling * _rowOrderLotShipment.QuantityShipped)
End If
Next
'Calculate Delivery
For Each _vwDeliveryRow In Me.m_ItemDelivery.DatasetDelivery.vw_DeliveryShipmentView.Select("OrderLotID=" & _oneLotRow.OrderLotID.ToString)
If Not _vwDeliveryRow.IsDeliveryNull Then
_deliveryContainer.Add(_vwDeliveryRow.Delivery)
End If
Next
counter += 1
Next
Next
Me.m_SeasonSummary.TotalOrders += _numOfOrders.Count
If _total <> 0 Then
_avgMargin = (_totalNet - _totalBuy) / _total
Else
_avgMargin = 0D
End If
'Inspection failed
If _totalInspection <> 0 Then
_InspectionFailedPercentage = _totalInspectionFailed / _totalInspection
Else
_InspectionFailedPercentage = 0
End If
'Delivery
For Each _oneDelivery As Integer In _deliveryContainer
_totalDelivery += _oneDelivery
Next
If _deliveryContainer.Count = 0 Then
_averageDelivery = 0D
Else
_averageDelivery = _totalDelivery / _deliveryContainer.Count
End If
'Create one row for current customer
_row = _dataTable.NewRow
_row.Item("SupplierID") = _supplierRow.SupplierID
_row.Item("SupplierName") = _supplierRow.Name
_row.Item("TotalPieces") = _totalPieces
_row.Item("Orders") = _numOfOrders.Count
_row.Item("PurchaseAmount") = -_totalBuy
If Me.m_SeasonSummary.TotalSalesBuy <> 0 Then
_row.Item("PurchasePercentage") = (_totalBuy / Me.m_SeasonSummary.TotalSalesBuy)
Else
_row.Item("PurchasePercentage") = 0
End If
_row.Item("Claims") = -_claimSupplier
_row.Item("Penalty") = _penaltySupplier
_row.Item("InspectionFail") = _totalInspectionFailed.ToString & "/" & _totalInspection.ToString
_row.Item("FailedP") = _InspectionFailedPercentage
_row.Item("Delivery") = _averageDelivery
_row.Item("LowMargin") = _lowMargin
_row.Item("AvgMargin") = _avgMargin
_row.Item("HighMargin") = _highMargin
_row.Item("SubTotal") = _subTotal - _totalClaims + _totalPenalty
_row.Item("SubTotalOrdered") = _subTotalOrdered - _totalClaims + _totalPenalty
If _subTotalOrdered - _totalClaims + _totalPenalty = 0 Then
_row.Item("SubTotalCompare") = 0
Else
_row.Item("SubTotalCompare") = (_subTotal - _totalClaims + _totalPenalty) / (_subTotalOrdered - _totalClaims + _totalPenalty)
End If
_dataTable.Rows.Add(_row)
Next
Return _dataTable
End Function
Private Sub cmdRefresh_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdRefresh.Click
BindData()
End Sub
Public Sub ExportAll(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs)
Dim _fileName As String
_fileName = "Supplier sales stats by customer[" & Me.dropSeason.SelectedItem.Value & "]" & DateTime.Now.ToShortDateString & ".xls"
'Binding data first
BindData()
SeasonSummary.exportExcelFile(Me.m_arrAllDataGrids, _fileName, Me.Response)
End Sub
Private Sub gvSupplier_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles gvSupplier.RowDataBound
If e.Row.RowType = DataControlRowType.DataRow Then
Dim _dataTable As New DataTable
Dim _row As DataRow
Dim _dtgCustomers As DataGrid = e.Row.FindControl("dtgCustomers")
Dim _supplierID As Integer = CType(e.Row.DataItem("SupplierID"), Integer)
Dim _lblSupplierName As Label = CType(e.Row.FindControl("lblSupplierName"), Label)
_lblSupplierName.Text = CType(e.Row.DataItem("SupplierName"), String)
If _supplierID <> -1 Then
'Normal row
_dataTable = Me.GenerateCustomerSalesBySupplierID(_supplierID)
_dtgCustomers.Attributes.Add("Name", e.Row.DataItem("SupplierName"))
_row = _dataTable.NewRow
_row.Item("CustomerName") = "Total"
_row.Item("Orders") = CType(e.Row.DataItem("Orders"), Integer)
_row.Item("TotalPieces") = CType(e.Row.DataItem("TotalPieces"), Integer)
_row.Item("PurchaseAmount") = CType(e.Row.DataItem("PurchaseAmount"), Decimal)
_row.Item("PurchasePercentage") = CType(e.Row.DataItem("PurchasePercentage"), Decimal)
_row.Item("Claims") = CType(e.Row.DataItem("Claims"), Decimal)
_row.Item("Penalty") = CType(e.Row.DataItem("Penalty"), Decimal)
_row.Item("InspectionFail") = CType(e.Row.DataItem("InspectionFail"), String)
_row.Item("FailedP") = CType(e.Row.DataItem("FailedP"), Decimal)
_row.Item("Delivery") = CType(e.Row.DataItem("Delivery"), Integer)
_row.Item("LowMargin") = CType(e.Row.DataItem("LowMargin"), Decimal)
_row.Item("AvgMargin") = CType(e.Row.DataItem("AvgMargin"), Decimal)
_row.Item("HighMargin") = CType(e.Row.DataItem("HighMargin"), Decimal)
_row.Item("SubTotal") = CType(e.Row.DataItem("SubTotal"), Decimal)
_row.Item("SubTotalOrdered") = CType(e.Row.DataItem("SubTotalOrdered"), Decimal)
_row.Item("SubTotalCompare") = CType(e.Row.DataItem("SubTotalCompare"), Decimal)
_dataTable.Rows.Add(_row)
_dataTable.DefaultView.Sort = "PurchaseAmount DESC"
_dtgCustomers.DataSource = _dataTable.DefaultView
_dtgCustomers.DataBind()
Else
'Summary row
If CType(e.Row.DataItem("Orders"), Integer) <> 0 Then
_dataTable.Columns.Add(New DataColumn("CustomerName", GetType(String)))
_dataTable.Columns.Add(New DataColumn("Orders", GetType(Integer)))
_dataTable.Columns.Add(New DataColumn("TotalPieces", GetType(Integer)))
_dataTable.Columns.Add(New DataColumn("PurchaseAmount", GetType(Decimal)))
_dataTable.Columns.Add(New DataColumn("PurchasePercentage", GetType(Decimal)))
_dataTable.Columns.Add(New DataColumn("Claims", GetType(Decimal)))
_dataTable.Columns.Add(New DataColumn("Penalty", GetType(Decimal)))
_dataTable.Columns.Add(New DataColumn("InspectionFail", GetType(String)))
_dataTable.Columns.Add(New DataColumn("FailedP", GetType(Decimal)))
_dataTable.Columns.Add(New DataColumn("Delivery", GetType(Integer)))
_dataTable.Columns.Add(New DataColumn("LowMargin", GetType(Decimal)))
_dataTable.Columns.Add(New DataColumn("AvgMargin", GetType(Decimal)))
_dataTable.Columns.Add(New DataColumn("HighMargin", GetType(Decimal)))
_dataTable.Columns.Add(New DataColumn("SubTotal", GetType(Decimal)))
_dataTable.Columns.Add(New DataColumn("SubTotalOrdered", GetType(Decimal)))
_dataTable.Columns.Add(New DataColumn("SubTotalCompare", GetType(Decimal)))
'Create one row for current customer
_row = _dataTable.NewRow
_row.Item("CustomerName") = "Total"
_row.Item("Orders") = CType(e.Row.DataItem("Orders"), Integer)
_row.Item("TotalPieces") = CType(e.Row.DataItem("TotalPieces"), Integer)
_row.Item("PurchaseAmount") = CType(e.Row.DataItem("PurchaseAmount"), Decimal)
_row.Item("PurchasePercentage") = CType(e.Row.DataItem("PurchasePercentage"), Decimal)
_row.Item("Claims") = CType(e.Row.DataItem("Claims"), Decimal)
_row.Item("Penalty") = CType(e.Row.DataItem("Penalty"), Decimal)
_row.Item("InspectionFail") = CType(e.Row.DataItem("InspectionFail"), String)
_row.Item("FailedP") = CType(e.Row.DataItem("FailedP"), Decimal)
_row.Item("Delivery") = CType(e.Row.DataItem("Delivery"), Integer)
_row.Item("LowMargin") = CType(e.Row.DataItem("LowMargin"), Decimal)
_row.Item("AvgMargin") = CType(e.Row.DataItem("AvgMargin"), Decimal)
_row.Item("HighMargin") = CType(e.Row.DataItem("HighMargin"), Decimal)
_row.Item("SubTotal") = CType(e.Row.DataItem("SubTotal"), Decimal)
_row.Item("SubTotalOrdered") = CType(e.Row.DataItem("SubTotalOrdered"), Decimal)
_row.Item("SubTotalCompare") = CType(e.Row.DataItem("SubTotalCompare"), Decimal)
_dataTable.Rows.Add(_row)
_dataTable.DefaultView.Sort = "PurchaseAmount DESC"
_dtgCustomers.DataSource = _dataTable.DefaultView
_dtgCustomers.Attributes.Add("Name", "Summary")
_dtgCustomers.DataBind()
End If
End If
'Add to arraylist to use in future
Me.m_arrAllDataGrids.Add(_dtgCustomers)
End If
End Sub
End Class