Imports System.Data.SqlClient Imports ModCommon Imports System.Data.Sql Imports System.Data Partial Class Admin_supp_prod_datewisereport Inherits System.Web.UI.Page Shared Category(10) As String Shared count As Integer Shared cat As String Protected Sub btnReport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnReport.Click Try If RadioButtonList1.SelectedIndex = -1 Then Label1.Visible = True Label1.Text = "First Select Product Approval." Exit Sub End If GetGridData() Catch End Try End Sub Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Try If Session("AdminID") Is Nothing Then Response.Redirect("AdminLogin.aspx?sesn=expire") Exit Sub End If If Page.IsPostBack = False Then If Session("AdminID") = Nothing Then Response.Redirect("AdminLogin.aspx") End If Fill_ComboWithSelect("SELECT Supplier_Kid, Supplier_Name FROM Supplier_Master WHERE (Supplier_IsDeleted = '0') ORDER BY Supplier_Name", ddlSupplier, "Select Supplier") ddlSupplier.DataTextField = "Supplier_Name" ddlSupplier.DataValueField = "Supplier_Kid" End If Label1.Visible = False Catch End Try End Sub Protected Sub GridView1_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles GridView1.PageIndexChanging Try GridView1.PageIndex = e.NewPageIndex GetGridData() Catch End Try End Sub Public Sub GetGridData() Try Dim con As New SqlConnection Dim strCommandString As String Dim supname As String = "" If ddlSupplier.SelectedItem.Text <> "Select Supplier" Then supname = ddlSupplier.SelectedItem.Value.Trim End If Dim str1 As String = TextBox1.Text Dim str2 As String = TextBox2.Text Dim fromDate As Date = Convert.ToDateTime(TextBox1.Text).ToString("MM/dd/yyyy") Dim toDate As Date = Convert.ToDateTime(TextBox2.Text).ToString("MM/dd/yyyy") con.ConnectionString = ConfigurationManager.AppSettings("ConnectionString") con.Open() If RadioButtonList1.SelectedIndex = 0 Then If supname <> "" Then strCommandString = "SELECT Product_Master.Product_Code AS Code, Product_Master.Product_Name AS Name, Product_Master.Product_ModelNo AS [Model No],(Unit_Master.Unit_Name + ' ' + CONVERT(nvarchar(50), Product_Master.Product_MarketPrice)) AS [Market Price],U1.Unit_Name + ' ' + CONVERT(nvarchar(50), Product_Master.Product_AtcoPrice) AS [Atco Price], Supplier_Master.Supplier_Name AS [Supp Name],CONVERT(nvarchar(50), Product_Master.Product_RegDate,100) AS [Reg Date], Product_Master.Product_CategoryId, Product_Master.Product_Image FROM Product_Master INNER JOIN Supplier_Master ON Product_Master.Product_SupplierId = Supplier_Master.Supplier_Kid LEFT OUTER JOIN Unit_Master ON Product_Master.Product_MarketPriceType = Unit_Master.Unit_Kid LEFT OUTER JOIN Unit_Master AS U1 ON Product_Master.Product_AtcoPriceType = U1.Unit_Kid WHERE Product_Master.Product_Approved = 'Y' AND Product_Master.Product_RegDate >= '" & fromDate & "' AND Product_Master.Product_RegDate <= '" & toDate & "' AND Product_Master.Product_SupplierId='" & RemoveLiterals(supname.Trim) & "' AND Product_Master.Product_IsDeleted = '0' order by Product_Master.Product_RegDate desc" If fromDate = toDate Then strCommandString = "SELECT Product_Master.Product_Code AS Code, Product_Master.Product_Name AS Name, Product_Master.Product_ModelNo AS [Model No],(Unit_Master.Unit_Name + ' ' + CONVERT(nvarchar(50), Product_Master.Product_MarketPrice)) AS [Market Price],U1.Unit_Name + ' ' + CONVERT(nvarchar(50), Product_Master.Product_AtcoPrice) AS [Atco Price], Supplier_Master.Supplier_Name AS [Supp Name],CONVERT(nvarchar(50), Product_Master.Product_RegDate,100) AS [Reg Date], Product_Master.Product_CategoryId, Product_Master.Product_Image FROM Product_Master INNER JOIN Supplier_Master ON Product_Master.Product_SupplierId = Supplier_Master.Supplier_Kid LEFT OUTER JOIN Unit_Master ON Product_Master.Product_MarketPriceType = Unit_Master.Unit_Kid LEFT OUTER JOIN Unit_Master AS U1 ON Product_Master.Product_AtcoPriceType = U1.Unit_Kid WHERE Product_Master.Product_Approved = 'Y' AND CONVERT(nvarchar(50), Product_Master.Product_RegDate, 101) = '" & TextBox1.Text & "' AND Product_Master.Product_SupplierId='" & RemoveLiterals(supname.Trim) & "' AND Product_Master.Product_IsDeleted = '0' order by Product_Master.Product_RegDate desc" End If Else strCommandString = "SELECT Product_Master.Product_Code AS Code, Product_Master.Product_Name AS Name, Product_Master.Product_ModelNo AS [Model No],(Unit_Master.Unit_Name + ' ' + CONVERT(nvarchar(50), Product_Master.Product_MarketPrice)) AS [Market Price],U1.Unit_Name + ' ' + CONVERT(nvarchar(50), Product_Master.Product_AtcoPrice) AS [Atco Price], Supplier_Master.Supplier_Name AS [Supp Name],CONVERT(nvarchar(50), Product_Master.Product_RegDate,100) AS [Reg Date], Product_Master.Product_CategoryId, Product_Master.Product_Image FROM Product_Master INNER JOIN Supplier_Master ON Product_Master.Product_SupplierId = Supplier_Master.Supplier_Kid LEFT OUTER JOIN Unit_Master ON Product_Master.Product_MarketPriceType = Unit_Master.Unit_Kid LEFT OUTER JOIN Unit_Master AS U1 ON Product_Master.Product_AtcoPriceType = U1.Unit_Kid WHERE Product_Master.Product_Approved = 'Y' AND Product_Master.Product_RegDate >= '" & fromDate & "' AND Product_Master.Product_RegDate <= '" & toDate & "' AND Product_Master.Product_IsDeleted = '0' order by Product_Master.Product_RegDate desc" If fromDate = toDate Then strCommandString = "SELECT Product_Master.Product_Code AS Code, Product_Master.Product_Name AS Name, Product_Master.Product_ModelNo AS [Model No],(Unit_Master.Unit_Name + ' ' + CONVERT(nvarchar(50), Product_Master.Product_MarketPrice)) AS [Market Price],U1.Unit_Name + ' ' + CONVERT(nvarchar(50), Product_Master.Product_AtcoPrice) AS [Atco Price], Supplier_Master.Supplier_Name AS [Supp Name],CONVERT(nvarchar(50), Product_Master.Product_RegDate,100) AS [Reg Date], Product_Master.Product_CategoryId, Product_Master.Product_Image FROM Product_Master INNER JOIN Supplier_Master ON Product_Master.Product_SupplierId = Supplier_Master.Supplier_Kid LEFT OUTER JOIN Unit_Master ON Product_Master.Product_MarketPriceType = Unit_Master.Unit_Kid LEFT OUTER JOIN Unit_Master AS U1 ON Product_Master.Product_AtcoPriceType = U1.Unit_Kid WHERE Product_Master.Product_Approved = 'Y' AND CONVERT(nvarchar(50), Product_Master.Product_RegDate, 101) = '" & TextBox1.Text & "' AND Product_Master.Product_IsDeleted = '0' order by Product_Master.Product_RegDate desc" End If End If ElseIf RadioButtonList1.SelectedIndex = 1 Then If supname <> "" Then strCommandString = "SELECT Product_Master.Product_Code AS Code, Product_Master.Product_Name AS Name, Product_Master.Product_ModelNo AS [Model No],Unit_Master.Unit_Name + ' ' + CONVERT(nvarchar(50), Product_Master.Product_MarketPrice) AS [Market Price],U1.Unit_Name + ' ' + CONVERT(nvarchar(50), Product_Master.Product_AtcoPrice) AS [Atco Price], Supplier_Master.Supplier_Name AS [Supp Name],CONVERT(nvarchar(50), Product_Master.Product_RegDate,100) AS [Reg Date], Product_Master.Product_CategoryId, Product_Master.Product_Image FROM Product_Master INNER JOIN Supplier_Master ON Product_Master.Product_SupplierId = Supplier_Master.Supplier_Kid LEFT OUTER JOIN Unit_Master ON Product_Master.Product_MarketPriceType = Unit_Master.Unit_Kid LEFT OUTER JOIN Unit_Master AS U1 ON Product_Master.Product_AtcoPriceType = U1.Unit_Kid WHERE Product_Master.Product_Approved = 'N' AND Product_Master.Product_RegDate >= '" & fromDate & "' AND Product_Master.Product_RegDate <= '" & toDate & "' AND Product_Master.Product_SupplierId='" & RemoveLiterals(supname.Trim) & "' AND Product_Master.Product_IsDeleted = '0' order by Product_Master.Product_RegDate desc" If fromDate = toDate Then strCommandString = "SELECT Product_Master.Product_Code AS Code, Product_Master.Product_Name AS Name, Product_Master.Product_ModelNo AS [Model No],Unit_Master.Unit_Name + ' ' + CONVERT(nvarchar(50), Product_Master.Product_MarketPrice) AS [Market Price],U1.Unit_Name + ' ' + CONVERT(nvarchar(50), Product_Master.Product_AtcoPrice) AS [Atco Price], Supplier_Master.Supplier_Name AS [Supp Name],CONVERT(nvarchar(50), Product_Master.Product_RegDate,100) AS [Reg Date], Product_Master.Product_CategoryId, Product_Master.Product_Image FROM Product_Master INNER JOIN Supplier_Master ON Product_Master.Product_SupplierId = Supplier_Master.Supplier_Kid LEFT OUTER JOIN Unit_Master ON Product_Master.Product_MarketPriceType = Unit_Master.Unit_Kid LEFT OUTER JOIN Unit_Master AS U1 ON Product_Master.Product_AtcoPriceType = U1.Unit_Kid WHERE Product_Master.Product_Approved = 'N' AND CONVERT(nvarchar(50), Product_Master.Product_RegDate, 101) = '" & TextBox1.Text & "' AND Product_Master.Product_SupplierId='" & RemoveLiterals(supname.Trim) & "' AND Product_Master.Product_IsDeleted = '0' order by Product_Master.Product_RegDate desc" End If Else strCommandString = "SELECT Product_Master.Product_Code AS Code, Product_Master.Product_Name AS Name, Product_Master.Product_ModelNo AS [Model No],Unit_Master.Unit_Name + ' ' + CONVERT(nvarchar(50), Product_Master.Product_MarketPrice) AS [Market Price],U1.Unit_Name + ' ' + CONVERT(nvarchar(50), Product_Master.Product_AtcoPrice) AS [Atco Price], Supplier_Master.Supplier_Name AS [Supp Name],CONVERT(nvarchar(50), Product_Master.Product_RegDate,100) AS [Reg Date], Product_Master.Product_CategoryId, Product_Master.Product_Image FROM Product_Master INNER JOIN Supplier_Master ON Product_Master.Product_SupplierId = Supplier_Master.Supplier_Kid LEFT OUTER JOIN Unit_Master ON Product_Master.Product_MarketPriceType = Unit_Master.Unit_Kid LEFT OUTER JOIN Unit_Master AS U1 ON Product_Master.Product_AtcoPriceType = U1.Unit_Kid WHERE Product_Master.Product_Approved = 'N' AND Product_Master.Product_RegDate >= '" & fromDate & "' AND Product_Master.Product_RegDate <= '" & toDate & "' AND Product_Master.Product_IsDeleted = '0' order by Product_Master.Product_RegDate desc" If fromDate = toDate Then strCommandString = "SELECT Product_Master.Product_Code AS Code, Product_Master.Product_Name AS Name, Product_Master.Product_ModelNo AS [Model No],Unit_Master.Unit_Name + ' ' + CONVERT(nvarchar(50), Product_Master.Product_MarketPrice) AS [Market Price],U1.Unit_Name + ' ' + CONVERT(nvarchar(50), Product_Master.Product_AtcoPrice) AS [Atco Price], Supplier_Master.Supplier_Name AS [Supp Name],CONVERT(nvarchar(50), Product_Master.Product_RegDate,100) AS [Reg Date], Product_Master.Product_CategoryId, Product_Master.Product_Image FROM Product_Master INNER JOIN Supplier_Master ON Product_Master.Product_SupplierId = Supplier_Master.Supplier_Kid LEFT OUTER JOIN Unit_Master ON Product_Master.Product_MarketPriceType = Unit_Master.Unit_Kid LEFT OUTER JOIN Unit_Master AS U1 ON Product_Master.Product_AtcoPriceType = U1.Unit_Kid WHERE Product_Master.Product_Approved = 'N' AND CONVERT(nvarchar(50), Product_Master.Product_RegDate, 101) = '" & TextBox1.Text & "' AND Product_Master.Product_IsDeleted = '0' order by Product_Master.Product_RegDate desc" End If End If ElseIf RadioButtonList1.SelectedIndex = 2 Then If supname <> "" Then strCommandString = "SELECT Product_Master.Product_Code AS Code, Product_Master.Product_Name AS Name, Product_Master.Product_ModelNo AS [Model No],Unit_Master.Unit_Name + ' ' + CONVERT(nvarchar(50), Product_Master.Product_MarketPrice) AS [Market Price],U1.Unit_Name + ' ' + CONVERT(nvarchar(50), Product_Master.Product_AtcoPrice) AS [Atco Price], Supplier_Master.Supplier_Name AS [Supp Name],CONVERT(nvarchar(50),Product_Master.Product_RegDate,100) AS [Reg Date],Product_Master.Product_CategoryId, Product_Master.Product_Image FROM Product_Master INNER JOIN Supplier_Master ON Product_Master.Product_SupplierId = Supplier_Master.Supplier_Kid LEFT OUTER JOIN Unit_Master ON Product_Master.Product_MarketPriceType = Unit_Master.Unit_Kid LEFT OUTER JOIN Unit_Master AS U1 ON Product_Master.Product_AtcoPriceType = U1.Unit_Kid WHERE Product_Master.Product_RegDate >= '" & fromDate & "' AND Product_Master.Product_RegDate <= '" & toDate & "' AND Product_Master.Product_SupplierId='" & RemoveLiterals(supname.Trim) & "' AND Product_Master.Product_IsDeleted = '0' order by Product_Master.Product_RegDate desc" If fromDate = toDate Then strCommandString = "SELECT Product_Master.Product_Code AS Code, Product_Master.Product_Name AS Name, Product_Master.Product_ModelNo AS [Model No],Unit_Master.Unit_Name + ' ' + CONVERT(nvarchar(50), Product_Master.Product_MarketPrice) AS [Market Price],U1.Unit_Name + ' ' + CONVERT(nvarchar(50), Product_Master.Product_AtcoPrice) AS [Atco Price], Supplier_Master.Supplier_Name AS [Supp Name],CONVERT(nvarchar(50),Product_Master.Product_RegDate,100) AS [Reg Date],Product_Master.Product_CategoryId, Product_Master.Product_Image FROM Product_Master INNER JOIN Supplier_Master ON Product_Master.Product_SupplierId = Supplier_Master.Supplier_Kid LEFT OUTER JOIN Unit_Master ON Product_Master.Product_MarketPriceType = Unit_Master.Unit_Kid LEFT OUTER JOIN Unit_Master AS U1 ON Product_Master.Product_AtcoPriceType = U1.Unit_Kid WHERE CONVERT(nvarchar(50), Product_Master.Product_RegDate, 101) = '" & TextBox1.Text & "' AND Product_Master.Product_SupplierId='" & RemoveLiterals(supname.Trim) & "' AND Product_Master.Product_IsDeleted = '0' order by Product_Master.Product_RegDate desc" End If Else strCommandString = "SELECT Product_Master.Product_Code AS Code, Product_Master.Product_Name AS Name, Product_Master.Product_ModelNo AS [Model No],Unit_Master.Unit_Name + ' ' + CONVERT(nvarchar(50), Product_Master.Product_MarketPrice) AS [Market Price],U1.Unit_Name + ' ' + CONVERT(nvarchar(50), Product_Master.Product_AtcoPrice) AS [Atco Price], Supplier_Master.Supplier_Name AS [Supp Name],CONVERT(nvarchar(50),Product_Master.Product_RegDate,100) AS [Reg Date], Product_Master.Product_CategoryId, Product_Master.Product_Image FROM Product_Master INNER JOIN Supplier_Master ON Product_Master.Product_SupplierId = Supplier_Master.Supplier_Kid LEFT OUTER JOIN Unit_Master ON Product_Master.Product_MarketPriceType = Unit_Master.Unit_Kid LEFT OUTER JOIN Unit_Master AS U1 ON Product_Master.Product_AtcoPriceType = U1.Unit_Kid WHERE Product_Master.Product_RegDate >= '" & fromDate & "' AND Product_Master.Product_RegDate <= '" & toDate & "' AND Product_Master.Product_IsDeleted = '0' order by Product_Master.Product_RegDate desc" If fromDate = toDate Then strCommandString = "SELECT Product_Master.Product_Code AS Code, Product_Master.Product_Name AS Name, Product_Master.Product_ModelNo AS [Model No],Unit_Master.Unit_Name + ' ' + CONVERT(nvarchar(50), Product_Master.Product_MarketPrice) AS [Market Price],U1.Unit_Name + ' ' + CONVERT(nvarchar(50), Product_Master.Product_AtcoPrice) AS [Atco Price], Supplier_Master.Supplier_Name AS [Supp Name],CONVERT(nvarchar(50),Product_Master.Product_RegDate,100) AS [Reg Date], Product_Master.Product_CategoryId, Product_Master.Product_Image FROM Product_Master INNER JOIN Supplier_Master ON Product_Master.Product_SupplierId = Supplier_Master.Supplier_Kid LEFT OUTER JOIN Unit_Master ON Product_Master.Product_MarketPriceType = Unit_Master.Unit_Kid LEFT OUTER JOIN Unit_Master AS U1 ON Product_Master.Product_AtcoPriceType = U1.Unit_Kid WHERE CONVERT(nvarchar(50), Product_Master.Product_RegDate, 101) = '" & TextBox1.Text & "' AND Product_Master.Product_IsDeleted = '0' order by Product_Master.Product_RegDate desc" End If End If End If Dim myCommand As SqlCommand = New SqlCommand(strCommandString, con) Dim myAdapter As New SqlDataAdapter myAdapter.SelectCommand = myCommand Dim SqlDataSet As New Data.DataSet myAdapter.Fill(SqlDataSet, "Table1") Dim myDataTable1 As Data.DataTable = SqlDataSet.Tables("Table1") SqlDataSet.Tables("Table1").Columns.Add("SrNo", GetType(Integer)) Dim i As Integer = 1 For Each dr As DataRow In SqlDataSet.Tables("Table1").Rows dr("SrNo") = i i = i + 1 Next Dim j As Integer = myDataTable1.Rows.Count If j > 0 Then Label2.Visible = True Label2.Text = j & " Product(s) Founds." Else Label2.Text = "" Label2.Visible = False End If GridView1.Columns(7).Visible = True GridView1.Columns(8).Visible = True GridView1.DataSource = SqlDataSet GridView1.DataBind() GridView1.Columns(7).Visible = False GridView1.Columns(8).Visible = False ' GetDataTable(strCommandString) 'Dim dt As Data.DataTable = ReturnDataTable(strCommandString) 'Dim i As Integer 'For i = 0 To dt.Rows.Count - 1 ' Dim str As String = ReturnValue("SELECT Unit_Name FROM Unit_Master WHERE Unit_Kid = '" & dt.Rows(i).Item("Market Type").ToString & "'") ' Dim lblMarket As Label = CType(GridView1.Rows(i).FindControl("lblMarket"), Label) ' lblMarket.Text = str & " " & dt.Rows(i).Item("Market Price") ' str = ReturnValue("SELECT Unit_Name FROM Unit_Master WHERE Unit_Kid = '" & dt.Rows(i).Item("Atco Type").ToString & "'") ' Dim lblAtcomaart As Label = CType(GridView1.Rows(i).FindControl("lblAtcomaart"), Label) ' lblAtcomaart.Text = str & " " & dt.Rows(i).Item("Atco Price") ' 'Dim myTime As DateTime = Convert.ToDateTime(dt.Rows(i).Item("Reg Date")).ToString("dd-MMM-yyyy") ' Dim lblRegDate As Label = CType(GridView1.Rows(i).FindControl("lblRegDate"), Label) ' lblRegDate.Text = Convert.ToDateTime(dt.Rows(i).Item("Reg Date")).ToString("dd-MMM-yyyy") 'Next con.Close() Catch End Try End Sub Public Function FillCategoryNameGrid(ByVal Catid As String) As String ' Fill category herarchy in label Dim dt As DataTable dt = ReturnDataTable("Select * From Category_Master Where Category_Kid ='" & RemoveLiterals(Catid) & "' and Category_Isdeleted = '0'") Dim ParentId As String ParentId = dt.Rows(0).Item(3).ToString() If ParentId = "Root Category" Then Dim j As Integer Category(count) = dt.Rows(0).Item(2).ToString() & " > " For j = 0 To count cat += Category(count) count = count - 1 Next Return cat Exit Function Else Category(count) = dt.Rows(0).Item(2).ToString() & " > " count = count + 1 FillCategoryNameGrid(dt.Rows(0).Item(3).ToString()) End If dt.Clear() Return cat End Function Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound Try If e.Row.RowType = DataControlRowType.DataRow Then Dim Pimg As Image Pimg = CType(e.Row.Cells(1).FindControl("imgprod"), Image) Pimg.ImageUrl = "~/Upload Image/Product Image/UploadImages/" & e.Row.Cells(8).Text.ToString Dim imgpath As String imgpath = Server.MapPath(Pimg.ImageUrl) KeepAspectRatioNew(Pimg, imgpath, 50, 50) If e.Row.Cells(7).Text <> " " Or e.Row.Cells(7).Text <> "" Then e.Row.Cells(3).CssClass = "Help" count = 0 cat = "" e.Row.Cells(3).Attributes.Add("onmouseover", "fixedtooltip('" & FillCategoryNameGrid(RemoveLiterals(e.Row.Cells(7).Text.ToString)) & "',this,event,'450px');") Else e.Row.Cells(3).Attributes.Add("onmouseover", "fixedtooltip('No Category Assign.',this,event,'200px');") End If e.Row.Cells(3).Attributes.Add("onmouseout", "delayhidetip();") End If Catch ex As Exception End Try End Sub End Class