Source code some are asking for
Imports System.Data.OleDb
Imports System.Data.SqlTypes
Imports System.Data.SqlDbType
Imports system.Data.SqlClient
Imports system.Data
Imports System.ComponentModel
Imports System.Collections
Partial Class _Default
Inherits System.Web.UI.Page
Dim mdofeb As String = CType(Session.Item("mbdate"), String)
Dim mdofee As String = CType(Session.Item("medate"), String)
Dim mcountry As String = CType(Session.Item("mcountry"), String)
Dim mbrand As String = CType(Session.Item("mbrand"), String)
Dim mproduct As String = CType(Session.Item("mproduct"), String)
Dim mrpttype As String = CType(Session.Item("mrpttype"), String)
'
Dim x2, _
mqty, _
mshipped, _
mback, _
mreturned, _
mordered, _
mdisc_per As Integer
Dim mucost, _
mextcost, _
mroyalty, _
muprice, _
mextprice, _
mdiscount, _
mrcost, _
mprofit, _
mqty_back_ordered, _
TDATA As Decimal
Dim _mextprice As Decimal = 0
Dim mitem As String
Dim mcriteria, _
grabline As String
Dim itemlist As New DataTable
Dim invitems As New DataSet
Public GRAB As New SqlConnection("data source = sqlprod; user id = FOXPRO; password = $SBT@123")
Dim grab1 As New SqlCommand
'****************************************
'* initialize the table for the results *
'****************************************
Public rs1 As New DataTable
Dim G2 As New DataSet
Dim dr As DataRow
Dim item As DataColumn
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Me.lblbdate.Text = mdofeb
Me.lbledate.Text = mdofee
Me.lblbrand.Text = mbrand
Me.lblcountry.Text = mcountry
Me.lblsku.Text = mproduct
Me.lblreport_type.Text = mrpttype
'
Call initvar()
mitem = ""
'Call getitems()
Me.getitems()
Call create_report()
'
mcriteria = " ecommerce.dbo.orders.order_date >= "
mcriteria = mcriteria + " convert(datetime, '" + mdofeb + " 00:00:00') "
mcriteria = mcriteria + " and "
mcriteria = mcriteria + " ecommerce.dbo.orders.order_date <= "
mcriteria = mcriteria + " convert(datetime, '" + mdofee + " 23:59:59') "
If mcountry <> "" Then
mcriteria = mcriteria + " and "
mcriteria = mcriteria + " ecommerce.dbo.orders.ship_country = '" + mcountry + "' "
End If
If mproduct <> "" Then
mcriteria = mcriteria + " and "
mcriteria = mcriteria + " ecommerce.dbo.order_line_items.item_number like '%" + mproduct + "%' "
End If
mcriteria = mcriteria + " and "
Select Case mrpttype
Case "RMA'"
mcriteria = mcriteria + " ecommerce.dbo.orders.ordertypeid = 3 "
Case "Back Order"
mcriteria = mcriteria + " ecommerce.dbo.orders.ordertypeid = 4 "
Case Else
mcriteria = mcriteria + " (ecommerce.dbo.orders.ordertypeid = 1 or "
mcriteria = mcriteria + " ecommerce.dbo.orders.ordertypeid = 8 or "
mcriteria = mcriteria + " ecommerce.dbo.orders.ordertypeid = 11 or "
mcriteria = mcriteria + " ecommerce.dbo.orders.ordertypeid = 12 or "
mcriteria = mcriteria + " ecommerce.dbo.orders.ordertypeid = 13 or "
mcriteria = mcriteria + " ecommerce.dbo.orders.ordertypeid = 15) "
End Select
mcriteria = mcriteria + " and "
mcriteria = mcriteria + " ((ecommerce.dbo.order_status.shipping_status_id >= 3 and "
mcriteria = mcriteria + " ecommerce.dbo.order_status.shipping_status_id <= 7) or "
mcriteria = mcriteria + " (ecommerce.dbo.order_status.shipping_status_id = 9 or "
mcriteria = mcriteria + " ecommerce.dbo.order_status.shipping_status_id = 11)) "
'
grabline = "Select ecommerce.dbo.order_line_items.item_number, "
grabline = grabline + " sum(ecommerce.dbo.order_line_items.quantity) as tquantity, "
grabline = grabline + " ecommerce.dbo.order_line_items.cost as price, "
grabline = grabline + " sum(ecommerce.dbo.order_line_items.cost_subtotal) as extprice, "
grabline = grabline + " ecommerce.dbo.order_line_items.pv_value, "
grabline = grabline + " sum(ecommerce.dbo.order_line_items.pv_subtotal) as total_pv, "
grabline = grabline + " sum(ecommerce.dbo.order_line_items.qty_shipped) as shipped, "
grabline = grabline + " sum(ecommerce.dbo.order_line_items.qty_bo) as back_ordered, "
grabline = grabline + " sum(ecommerce.dbo.order_line_items.qty_received) as returned, "
grabline = grabline + " sum(ecommerce.dbo.order_line_items.RoyaltyValue) as total_royalty, "
grabline = grabline + " ecommerce.dbo.orders.ordertypeid, "
grabline = grabline + " ecommerce.dbo.orders.discount_percent "
grabline = grabline + " from ecommerce.dbo.order_line_items "
grabline = grabline + " left outer join "
grabline = grabline + " ecommerce.dbo.orders "
grabline = grabline + " on "
grabline = grabline + " ecommerce.dbo.orders.order_id = "
grabline = grabline + " ecommerce.dbo.order_line_items.order_id "
grabline = grabline + " left outer join "
grabline = grabline + " ecommerce.dbo.order_status "
grabline = grabline + " on "
grabline = grabline + " ecommerce.dbo.orders.order_id = "
grabline = grabline + " ecommerce.dbo.order_status.order_id "
grabline = grabline + " where " + mcriteria
grabline = grabline + " group by item_number, cost, pv_value, ordertypeid, discount_percent"
grabline = grabline + " order by ecommerce.dbo.order_line_items.item_number "
'
GRAB.Open()
Dim grab2 As New SqlCommand(grabline, GRAB)
'
Dim G1 As New SqlDataAdapter(grab2)
'
G1.Fill(G2, "THEREPORT")
GRAB.Close()
'
mitem = G2.Tables("thereport").Rows(1).Item("Item_number").ToString
mdisc_per = G2.Tables("thereport").Rows(1).Item("discount_percent")
Dim mtunits As Integer = 0
Dim x3 = G2.Tables("thereport").Rows.Count
For x2 = 0 To x3 - 1
If mitem <> G2.Tables("thereport").Rows(x2).Item("Item_number").ToString Then
Me.storerow()
mitem = G2.Tables("thereport").Rows(x2).Item("Item_number").ToString
mdisc_per = G2.Tables("thereport").Rows(x2).Item("discount_percent")
Me.get_muscost(mucost)
End If
mordered = mordered + G2.Tables("thereport").Rows(x2).Item("tquantity")
mshipped = mshipped + G2.Tables("thereport").Rows(x2).Item("shipped")
Try
mqty_back_ordered = mqty_back_ordered + G2.Tables("thereports").Rows(x2).Item("back_ordered")
Catch
End Try
Try
mreturned = mreturned + G2.Tables("thereports").Rows(x2).Item("returned")
Catch
End Try
Try
mroyalty = mroyalty + G2.Tables("thereport").Rows(x2).Item("royaltyvalue")
Catch
End Try
Try
muprice = muprice + G2.Tables("thereport").Rows(x2).Item("price")
Catch
End Try
Try
mextprice = mextprice + G2.Tables("thereport").Rows(x2).Item("extprice")
Catch
End Try
Call storerow()
Call initvar()
mtunits = mtunits + 1
LBLTotal_Units.Text = mtunits
Next
Me.lbltotal_pv.Text = Me._mextprice
GRDSales.DataSource = rs1
GRDSales.DataBind()
'
GRDSales.Visible = True
Me.txtmsg.Text = grabline
End Sub
Public Sub create_report()
With rs1.Columns
.Add("Item", Type.GetType("System.String"))
.Add("Qty_Ordered", Type.GetType("System.Int16"))
.Add("Qty_Shipped", Type.GetType("System.Int16"))
.Add("Qty_Back_Ordered", Type.GetType("System.Int16"))
.Add("Qty_Returned", Type.GetType("System.Int16"))
.Add("Unit_Cost", Type.GetType("System.Decimal"))
.Add("Extended_Cost", Type.GetType("System.Decimal"))
.Add("Royalty", Type.GetType("System.Decimal"))
.Add("Unit_Price", Type.GetType("System.Decimal"))
.Add("Extended_Price", Type.GetType("System.Decimal"))
.Add("Est_Discount", Type.GetType("System.Decimal"))
.Add("Discount_percent", Type.GetType("System.Decimal"))
.Add("Return_Cost", Type.GetType("System.Decimal"))
.Add("Est_Profit", Type.GetType("System.Decimal"))
End With
rs1.PrimaryKey = New DataColumn() {rs1.Columns("item")}
End Sub
Public Sub initvar()
mqty = 0
mdisc_per = 0
mshipped = 0
mback = 0
mreturned = 0
mucost = 0.0
mextcost = 0.0
mroyalty = 0.0
muprice = 0.0
mextprice = 0.0
mdiscount = 0.0
mrcost = 0.0
mprofit = 0.0
mordered = 0.0
mqty_back_ordered = 0.0
End Sub
Private Sub storerow()
Try
Dim mtdiscount As Decimal
mtdiscount = Math.Round(mextprice * mdisc_per, 2)
dr = rs1.NewRow
dr("item") = mitem
dr("qty_ordered") = mordered
dr("qty_shipped") = mshipped
dr("qty_back_ordered") = mqty_back_ordered
dr("qty_returned") = mreturned
dr("Royalty") = mroyalty
dr("Unit_price") = muprice
dr("extended_price") = mextprice
dr("unit_cost") = mucost
dr("extended_cost") = Math.Round(mucost * mordered, 2)
dr("est_discount") = mtdiscount
dr("discount_percent") = mdisc_per
dr("unit_cost") = mucost
dr("extended_cost") = mextcost
dr("est_profit") = Math.Round(mextprice - (mextcost + mroyalty + mtdiscount), 2)
_mextprice = _mextprice + mextprice
rs1.Rows.Add(dr)
rs1.AcceptChanges()
Catch
End Try
End Sub
Private Sub getitems()
itemlist.Columns.Add(New DataColumn("Item", Type.GetType("System.String")))
itemlist.Columns.Add(New DataColumn("Description", Type.GetType("System.String")))
itemlist.Columns.Add(New DataColumn("Cost", Type.GetType("System.String")))
'
grabline = "Select item, "
grabline = grabline + " itmdesc, "
grabline = grabline + " stdcost "
grabline = grabline + " from s:\pro60\data01\icitem01 "
'
Dim g3B As New Odbc.OdbcCommand
Dim Gab As New Odbc.OdbcDataAdapter
Dim sbtcon As New Odbc.OdbcConnection
'
sbtcon.ConnectionString = "Dsn=Visual FoxPro Tables;source=s:\PRO60\data01;sourcetype=DBF;exclusive=No;backgroundfetch=Yes;collate=Machine"
sbtcon.Open()
g3B.CommandText = grabline
g3B.Connection = sbtcon
Gab.SelectCommand = g3B
Gab.Fill(invitems, "TheItems")
Dim x3b = invitems.Tables("TheItems").Rows.Count
Dim xi As Integer
For xi = 1 To x3b - 1
dr = itemlist.NewRow
dr("item") = invitems.Tables("TheItems").Rows(xi).Item("Item").ToString
dr("description") = invitems.Tables("TheItems").Rows(xi).Item("Itmdesc").ToString
dr("cost") = invitems.Tables("TheItems").Rows(xi).Item("stdcost").ToString
Next
sbtcon.Close()
End Sub
Private Sub get_muscost(ByVal muscost)
'Dim s As String = "primaryKeyValue"
Try
Dim foundRow As DataRow = invitems.Tables("TheItems").Rows.Find(mitem)
If foundRow IsNot Nothing Then
muscost = foundRow.Item("cost")
Else
muscost = 0.01
End If
Catch
muscost = -100
End Try
End Sub
End Class