Customers.aspx - sorting

Hi,

can I change default sorting (by AccountID) on Customers.apsx page? Our customer wants to sort by Account Name by default. Is any way to do it?

Thanks

Regards

Milos Vich

SAP Managed Tags:
SAP Business One

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Accepted Solutions (0)

Answers (2)

hello milos.

i had kind of the same problem, i have added a textbox on the customers page so people could type a customer name and then only that customer belonging to the right salesperson would be visible in a new window with the same stylesheet as netpoint and all functionality.

i just made a redirect from the customers.aspx to another aspx page, this way i can modify it every way i want with query's. i dont know if thats what you want, its kind of lot of work but this way you can manipulate the rigid code of netpoint. and you can get more functionality.

if your maybe interrested in the code let me know.

greetz

C. Oude Luttikhuis

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Hello Oude,

thanks for help. Can you please send me this code to modifying customers page? We have problems with it in our customer installation, because they need to retrict view only customers associated for user as sales person and therefore they do not use standard customer searching.

Regards

Milos Vich

hello milos,

first you have to modify the existing customers.aspx with following code.


<%@ register tagprefix="np" TagName="customers" src="controls/customers.ascx"%>
<%@ Register TagPrefix="np" TagName="DashboardTab" Src="controls/DashboardTab.ascx" %>
<%@ Page language="c#" Codebehind="Customers.aspx.cs" AutoEventWireup="false" Inherits="netpoint.dashboard.Customers" %>

<form id="Form1" method="post" runat="server">
  <HEAD>  
   <scri  pt runat="server" language="C#">  /* remove spaces from the word script, had to do this to get this code on the forum */

		private void Page_Load(object sender, System.EventArgs e)
		{
		}

		override protected void OnInit(EventArgs e)
		{
			InitializeComponent();
			base.OnInit(e);
		}
		
		private void InitializeComponent()
		{    
			this.btnSubmit4.Click += new System.EventHandler(this.btnSubmit_Click);
			this.Load += new System.EventHandler(this.Page_Load);


            
		}

		private void btnSubmit_Click(object sender, System.EventArgs e)
		{
			netpoint.classes.NPBasePageDashboard page = (netpoint.classes.NPBasePageDashboard) this.Page;
			Session["ConnString"] = page.ConnectionString;
            Session["UserId"] = page.UserID;
            Response.Redirect(string.Format("NPsearchResults.aspx?UserName={0}", txtAccounts.Text)); 
		}





</script>
</HEAD> 
	<asp:Label Runat="server" ID="lblBreadCrumbs" CssClass="npbody" Text="Focus > Customers" />
	<np:DashboardTab id="tab" visible="false" runat="server"></np:DashboardTab>
	<table cellSpacing="0" cellPadding="0" width="100%" border="0" class="npadminbody">
		<tr>
	
		
			<td class="npadminsubheader">
				<asp:Image runat="server" id="imgIndicator" ImageUrl="~/assets/common/icons/indicator.gif"></asp:Image>
				<asp:Literal runat="server" id="ltlCustomersHeader" Text="Customers"></asp:Literal>
			</td>
			<td align="right" class="npadminsubheader">
			
				<asp:TextBox id="txtAccounts" visible="true" Runat="server" Columns="10" ></asp:TextBox>
				
				<asp:Button id="btnSubmit4" width="90px" Text="Business Partner"  runat="server"></asp:Button>

				<asp:CheckBox id="chkIncludeInactive" runat="server" Text="Include Inactive" TextAlign="Left"
					AutoPostBack="True"></asp:CheckBox> 
				<asp:HyperLink id="lnkAddAccount" runat="server" ToolTip="Add An Account" ImageUrl="~/assets/common/icons/add.gif"
					NavigateUrl="~/admin/common/accounts/AccountNew.aspx">HyperLink</asp:HyperLink>
				 
			</td>
		</tr>
		<tr>
			<td valign="top" colspan="2">
				<np:customers id="customers" runat="server"></np:customers>
			</td>
		</tr>
		
	</table>
</form>

the next code is the new page, it uses the same stylesheet as netpoint and in there its possible to alter the query any way you want.



<%@ Page language="c#" AutoEventWireup="false"%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >

<scri pt runat="server"> /* remove spaces from script to make it work */

	override protected void OnInit(EventArgs e)
	{
		InitializeComponent();
		base.OnInit(e);
	}
	
	
	private void InitializeComponent()
	{    
		this.Load += new System.EventHandler(this.Page_Load);
	}

	private void Page_Load(object sender, System.EventArgs e)
	{
		GetAccountsBySalesPerson();
	}

		private DataTable GetAccountsBySalesPerson()
		{
			try
			{
				using(SqlConnection conn = new SqlConnection((string)Session["ConnString"]))
				{
                    string query = CreateQuery(Request.QueryString["UserName"], (string)Session["UserId"]);
					SqlDataAdapter adapter = new SqlDataAdapter(query, conn);

					DataTable table = new DataTable();
					adapter.Fill(table);
					DataGrid1.DataSource = table;
					DataGrid1.DataBind();
				}
			}
			catch (Exception ex)
			{
				Response.Write("Exception!!!!! n" + ex);
			}
			return null;
		}

		private string CreateQuery(string userName, string userId)
		{
            //Response.Write("Name: " + userName);
            //Response.Write("NameId: " + userId);
            
			StringBuilder builder = new StringBuilder();
			//builder.Append("SELECT * FROM UsersAccount");
			

			builder.Append("SELECT DISTINCT ua.AccountID, ua.AccountName, ua.Phone1, ua.EmailAddress, ua.Website, ua.MainContactID, ua.DefaultBillAddressID, uad.Zip, ");
			builder.Append("(SELECT COUNT(EventID) FROM Events WHERE AccountID = ua.AccountID AND StatusCode = 'O') OpenActivitiesCount, ");
			builder.Append("(SELECT COUNT(TicketID) FROM SupportTickets WHERE AccountID = ua.AccountID AND TicketStatus = 'open' AND ParentID IS NULL) OpenTicketsCount, ");
			builder.Append("(SELECT COUNT(OpportunityID) FROM Opportunity WHERE AccountID = ua.AccountID AND ClosedDate IS NULL) OpenOpportunitiesCount, ");
			builder.Append("(SELECT COUNT(GrandTotal) FROM OrderMaster WHERE AccountID = ua.AccountID AND CartType = 'Q') OpenQuotesCount, ");
			builder.Append("(SELECT SUM(GrandTotal) FROM OrderMaster WHERE AccountID = ua.AccountID AND CartType = 'O' AND PurchaseDate BETWEEN dateadd(d, -30, getdate()) AND getdate()) Orders30, ");
			builder.Append("(SELECT SUM(GrandTotal) FROM OrderMaster WHERE AccountID = ua.AccountID AND CartType = 'O' AND PurchaseDate BETWEEN dateadd(d, -60, getdate()) AND dateadd(ms, -1, dateadd(d, -30, getdate()))) Orders60, ");
			builder.Append("(SELECT SUM(GrandTotal) FROM OrderMaster WHERE AccountID = ua.AccountID AND CartType = 'O' AND PurchaseDate BETWEEN dateadd(d, -90, getdate()) AND dateadd(ms, -1, dateadd(d, -60, getdate()))) Orders90, ");
			builder.Append("(SELECT SUM(GrandTotal) FROM OrderMaster WHERE AccountID = ua.AccountID AND CartType = 'O' AND PurchaseDate < dateadd(d, -90, getdate())) Orders90Plus ");
			builder.Append("FROM UsersAccount ua LEFT JOIN UsersAccountAddress uad ON ua.AccountID = uad.AccountID ");
			builder.Append("WHERE AccountName like '%");
			builder.Append(userName);
			builder.Append("%' AND SalesManager = '");
            builder.Append(userId);
            builder.Append("'");
			//Response.Write(userName);
			return builder.ToString();
		}
	
</script>

<HTML>
	<HEAD>
		<title>WebForm1</title>
	
		<LINK HREF="../assets/common/themes/Focus5/theme.css" REL="stylesheet" TYPE="text/css">
		<me   ta name="GENERATOR" Content="Microsoft Visual Studio .NET 7.1"> /* remove spaces from the all words Meta to make it work */
		<me   ta name="CODE_LANGUAGE" Content="C#">
		<me   ta name="vs_defaultClientScript" content="JavaScript">
		<me   ta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
	</HEAD>
	<body MS_POSITIONING="GridLayout">
		<form id="Form1" method="post" runat="server">
			<asp:datagrid id="DataGrid1" PageSize="50" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False"
				Width="100%" runat="server">
				<ItemStyle CssClass="npadminbody"></ItemStyle>
				<AlternatingItemStyle CssClass="npadminbodyalt"></AlternatingItemStyle>
				<HeaderStyle CssClass="npadminsubheader"></HeaderStyle>
				<Columns>
					<asp:HyperLinkColumn DataTextField="AccountName" HeaderText="Zakenpartner" SortExpression="AccountName" DataNavigateUrlField="AccountID"
						DataNavigateUrlFormatString="~/admin/common/accounts/AccountInfo.aspx?accountid={0}"></asp:HyperLinkColumn>
					<asp:BoundColumn DataField="Phone1" HeaderText="Telefoon" SortExpression="Phone1"></asp:BoundColumn>
					<asp:BoundColumn DataField="Zip" HeaderText="Postcode" SortExpression="Zip"></asp:BoundColumn>
					<asp:HyperLinkColumn Text="<img src=../assets/common/icons/email.gif border=0>" DataNavigateUrlField="AccountID"
						DataNavigateUrlFormatString="~/dashboard/EmailDetail.aspx?accountid={0}">
						<HeaderStyle HorizontalAlign="Center"></HeaderStyle>
						<ItemStyle HorizontalAlign="Center"></ItemStyle>
					</asp:HyperLinkColumn>
					<asp:HyperLinkColumn DataTextField="OpenActivitiesCount" HeaderText="colActivities" SortExpression="OpenActivitiesCount"
						DataTextFormatString="{0:#}" DataNavigateUrlField="AccountID" DataNavigateUrlFormatString="~/admin/common/accounts/AccountActivities.aspx?section=EV&accountid={0}">
						<HeaderStyle HorizontalAlign="Center"></HeaderStyle>
						<ItemStyle HorizontalAlign="Center"></ItemStyle>
					</asp:HyperLinkColumn>
					<asp:HyperLinkColumn DataTextField="OpenTicketsCount" HeaderText="colTickets" SortExpression="OpenTicketsCount"
						DataTextFormatString="{0:#}" DataNavigateUrlField="AccountID" DataNavigateUrlFormatString="~/admin/common/support/tickets.aspx?status=open&accountid={0}">
						<HeaderStyle HorizontalAlign="Center"></HeaderStyle>
						<ItemStyle HorizontalAlign="Center"></ItemStyle>
					</asp:HyperLinkColumn>
					<asp:HyperLinkColumn DataTextField="OpenOpportunitiesCount" HeaderText="colOpportunities" DataTextFormatString="{0:#}"
						SortExpression="OpenOpportunitiesCount" DataNavigateUrlField="AccountID" DataNavigateUrlFormatString="~/admin/common/accounts/AccountTransactions.aspx?section=OP&accountid={0}">
						<HeaderStyle HorizontalAlign="Center"></HeaderStyle>
						<ItemStyle HorizontalAlign="Center"></ItemStyle>
					</asp:HyperLinkColumn>
					<asp:HyperLinkColumn DataTextField="OpenQuotesCount" HeaderText="colQuotes" DataTextFormatString="{0:#}"
						SortExpression="OpenQuotesCount" DataNavigateUrlField="AccountID" DataNavigateUrlFormatString="~/admin/common/accounts/AccountTransactions.aspx?section=QU&accountid={0}">
						<HeaderStyle HorizontalAlign="Center"></HeaderStyle>
						<ItemStyle HorizontalAlign="Center"></ItemStyle>
					</asp:HyperLinkColumn>
					<asp:TemplateColumn SortExpression="Orders30" HeaderText="colOrders30">
						<HeaderStyle HorizontalAlign="Right"></HeaderStyle>
						<ItemStyle HorizontalAlign="Right"></ItemStyle>
						<ItemTemplate>
							<asp:Literal ID="ltlOrders30" Runat="server"></asp:Literal>
						</ItemTemplate>
					</asp:TemplateColumn>
					<asp:TemplateColumn SortExpression="Orders60" HeaderText="colOrders60">
						<HeaderStyle HorizontalAlign="Right"></HeaderStyle>
						<ItemStyle HorizontalAlign="Right"></ItemStyle>
						<ItemTemplate>
							<asp:Literal ID="ltlOrders60" Runat="server"></asp:Literal>
						</ItemTemplate>
					</asp:TemplateColumn>
					<asp:TemplateColumn SortExpression="Orders90" HeaderText="colOrders90">
						<HeaderStyle HorizontalAlign="Right"></HeaderStyle>
						<ItemStyle HorizontalAlign="Right"></ItemStyle>
						<ItemTemplate>
							<asp:Literal ID="ltlOrders90" Runat="server"></asp:Literal>
						</ItemTemplate>
					</asp:TemplateColumn>
				</Columns>
				<PagerStyle BorderStyle="None" HorizontalAlign="Right" Position="Bottom" CssClass="npadminbody"
					Mode="NumericPages"></PagerStyle>
			</asp:datagrid>
		</form>
	</body>
</HTML>

i think this is something you need. cause ive had the same problem. if something is not clear please feel free to ask.

kind regards,

C. Oude Luttikhuis

Hi Milos,

This search is hard coded to "ORDER BY a.AccountID, u.UserID".

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

Comment

Ask a Question

Top Q&A Solution Author

User

Count