Syteview - Web Templates

Syteview™ - Web Templates

Overview

Syteview provides a set of web page templates that you can use to build your own data-enabled web applications. Over 75 web templates preconfigured for your Symix™ and Syteline™ ERP systems quickly add data lookup and reporting capabilities to your web sites. [view templates]

URL accessible from any web-enabled program and built with standard HTML and Javascript, our templates can be easily customized and integrated into your existing applications. Powered by our Data Replication Toolset, Syteview’s Web Templates allow you to quickly add live ERP data to your web pages, Word documents, Excel spreadsheets, or any other web enabled application.

Syteview provides two basic types of templates.

The first type of template, the Lookup page, is used to retrieve a list of records. The Lookup page is an HTML frameset with a web page in a frame at the top of the screen where you specify the records you wish to retrieve, and a web page in a frame at the bottom of the screen that displays the records that are retrieved. In between is an ASP Data Control that takes your data request, checks it for various types of malicious content and then passes the request over to the SQL Server which looks up your data and returns it to the Data Control. On receiving the data from the SQL Server the Data Control formats the data into an HTML table (that you specify) and returns it to the original client where it is displayed in the bottom frame.

The web page at the top of the screen, where you enter the parameters that specify the records that you want to retrieve, is named xxx-Lookup-Fields.asp - where the xxx is replaced by an abbreviation of the web site directory you are in. For example, in the Customer lookup directory of your web site this web page would be named CUST-Lookup-Fields.asp and in the Item lookup directory this web page would be named ITEM-Lookup-Fields.

The web page at the bottom of the screen which displays the records retrieved is named xxx-Lookup.asp where the xxx is replaced as above. For example, in the Customer lookup directory this web page would be named CUST-Lookup.asp and in the Item lookup directory this web page would be named ITEM-Lookup.asp.

Syteview provides over 45 Data Lookup templates modeled on your Symix™ and Syteline™ ERP data.

The second type of template, the Detail page, is used to display the fields of one record.

This detail page can be displayed in a browser window of its own as above, or in the frame at the bottom of the lookup frameset.

Syteview provides over 75 Detail templates for your Symix™ and Syteline™ ERP data. Each of these Detail templates is URL accessible and can easily be included in any web-enabled document, spreadsheet or web page.

Modifying Web Templates

The CUST-Lookup-Field.asp and the CUST-Lookup.asp web pages will be used for demonstration purposes in this example.   These two web pages together with their containing frameset make up the first type of Syteview template mentioned above, the Lookup page.  The second type of Syteview template, the Detail page, will be described later.

CUST-Lookup-Field.asp

The first line of the CUST-Lookup-Field web page pulls in the include file, Startup.inc, from the /INC directory of your web site.

This include file does three things.  First, it pulls in another include file, BlockXSS.inc , which is used to block cross-site scripting attacks.  Second, it adds the <HTML> and <HEAD> tags to the web page, and third, it adds a <META> tag which specifies the character set, ISO-8859-1, that will be used by the web page.

After pulling in the two include files and adding the <HTML>,<HEAD> and <META> tags the web page code looks like this:

<% dim qs
    dim qs2
    dim offset
    dim dest
    dest = "http://www1.ifccfbi.gov/index.asp"
    qs = unescape(Request.QueryString())
    ...
    ...BlockXSS code removed from public view...
    ...
    ...
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">

Note: We don't recommend changing anything in the BlockXSS.inc include file or leaving out the <META> tag that defines the character set as these are both used to prevent a type of hack attack known as cross-site scripting.

Next in the CUST-Lookup-Fields.asp web page is the Multi-CAD Corporation Copyright Notice.  Leave it alone.   Don't remove or modify it or we will revoke your license and confiscate your first-born.

Next is a <SCRIPT> tag:

   <script language='JAVASCRIPT'>

Followed by several javascript functions and several include files:

The Load() function which resizes the frameset to full-screen and fills in the Item field if an Item number has been passed in on the URL.
The Keycodes.inc include file which adds an OnKeyPress event to the web page and the keypress() function which processes the Enter and Escape keys.
The Escapekey() function which calls window.close() when the Escape key is pressed
The Enterkey() function which calls btnLookup_click() function when the Enter key is pressed
The btnLookup_click() function which is called when the user clicks the Lookup button or presses the Enter key. This function retrieves the lookup parameters from the various fields on the page and forms them into a query string.
The btnLookup_click() function also pulls in another include file - Lookup.inc - which adds the code which opens the CUST-Lookup.asp web page (passing it the query string created above) in the bottom frame in order to retrieve and then display the specified records.
Next, the GetRanges.inc include file is pulled in which adds the GetBeginningRange() and GetEndingRange() functions to the web page. These two functions are utility functions that simply separate the users' input into beginning and ending parameters when a range is entered into one of the lookup fields.

Finally, there is the closing </SCRIPT> tag and then the closing </HEAD> tag.

The web page BODY is next. Nothing special here, it is straight HTML code that lays out the fields that collect the lookup parameters.

The CUST-Lookup-Fields.asp web page includes the following lookup fields:

txtCustomer - specifies a Customer # or range of Customer #'s to lookup
txtCustName - specifies a Customer Name or range of Customer Names to lookup
txtContact - specifies a Contact Name or range of Contact Names to lookup
txtCity - specifies a City or range of Cities to lookup
txtState - specifies a State or range of States to lookup
txtZip - specifies a Zipcode or range of Zipcodes to lookup
txtCustType - specifies a Customer Type or range of Customer Types to lookup
txtCountry - specifies a Country or range of Countries to lookup
chkInternational - checked to lookup only customers in the United States and Canada
chkPattern - checked if you want to search for a string in the Customer Name field
txtRecs - specifies the maximum number of records to return

The last part of the CUST-Lookup-Fields.asp web page is the FooterParameter.inc include file.  This include file simply adds the closing </BODY> and </HTML> tags to the web page.

All the above has only one purpose, to collect the parameters that will identify the records you want to see.   After you have entered these parameters in the fields on the CUST-Lookup-Fields.asp web page clicking the Lookup button will open the CUST-Lookup.asp web page in the bottom frame passing it the parameters you specified in the CUST-Lookup-Fields.asp web page.

Modifying CUST-Lookup-Field.asp

You may wish to modify the CUST-Lookup-Field.asp web page to add additional lookup parameters.  The basic procedure to follow would be:

Add the text field that will collect the lookup parameter(s) to the web page using <INPUT> tag(s).

Example:
Ship-code: <INPUT TYPE="TEXT" NAME="txtShipCode" ID="txtShipCode" SIZE="20">

Add the code to the btnLookup_click() function that will collect the new parameter(s) from the <INPUT> tag, separating the input into beginning and ending parameters if necessary with the GetBeginningRange() and GetEndingRange() functions.

          Example:
               begshipcode = GetBeginningRange(txtShipCode.value);
               endshipcode = GetEndingRange(txtShipCode.value);

               if (begshipcode != '') locstring += '&begship=' + begshipcode;
               if (endshipcode != '') locstring += '&endship=' + endship;

            Note: you can specify a range of parameters by separating two values by a double slash when entering parameters.

Now, when the btnLookup_click() function opens the CUST-Lookup.asp web page in the bottom frame the &begship and &endship parameters will be passed to it in the query string. CUST-Lookup.asp will of course have to be modified to retrieve these parameters and pass them to the SQL Server to use during the lookup.

CUST-Lookup.asp

The first line of the CUST-Lookup web page pulls in the include file, StartupASP.inc, from the /INC directory of your web site.

This include file does quite a few things. First, it sets several ASP variables:

<% Response.Buffer = TRUE %> - buffers output to the page
<% Response.Expires = 0 %> - prevents the page from being cached
<% Server.ScriptTimeout = 120 %> - sets the script timeout to 2 minutes

Next it checks to see if you are pulling the retrieved records into an Excel spreadsheet instead of a standard web page and if you are it sets the ContentType to application/vnd.ms-excel

<%
    if ucase(trim(Request.QueryString("fmt"))) = "EXCEL" then
        Response.ContentType = "application/vnd.ms-excel"
    end if
%>

Next the StartupASP.inc include file pulls in the BlockXSS.inc include file which prevents cross-site scripting attacks.

Then it creates the Syteview Data Control which is used to request data from the SQL Server and then format the returned records into an HTML table which will be displayed on this page.

     <% set syte = CreateObject("ASP2SQL.SQLConnect") %>

Next the StartupASP.inc include file pulls in the SQLInject.inc include file which calls the Data Control's RejectSQL() function passing it the location of the SQL_Injection_Strings.txt file which lists illegal SQL strings that will not be allowed to into your SQL Server thereby deflecting SQL Injection attacks.

     <% syte.RejectSqlInject("I:\Syteview_DataControls\ASP2SQL\SQL_Injection_Strings.txt") %>

Next the ServerIPs.inc include file is pulled in.  The code in this include file sets two variables BaseIP and UserIP which hold the IP addresses of the web server and the user's workstartion.

   <% Dim BaseIP %>
   <% BaseIP ="http://" & Request.Servervariables("SERVER_NAME") %>
   <% Dim UserIP %>
   <% UserIP ="http://" & Request.Servervariables("REMOTE_ADDR") %>

Then the StartupASP.inc include file calls the Data Control's LogPage() function which logs the data request (username, servername, url, querystring, workstation and data source name) to the SessionLogs table on SQL Server.

<%=syte.LogPage(Request.ServerVariables("LOGON_USER"), Request.ServerVariables("SERVER_NAME"), Request.ServerVariables("URL") & "?" & Request.ServerVariables("QUERY_STRING"), Request.ServerVariables("REMOTE_ADDR"), trim(dsn)) %>

Finally the StartupASP.inc include file adds the <HTML> and <HEAD> tags to the web page, and then adds a <META> tag which specifies the character set, ISO-8859-1, that will be used by the web page.

   <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">

After pulling in the StartupASP.inc include file (and its includes) the web page code will look like this:

<% Response.Buffer = TRUE %>
<% Response.Expires = 0 %>
<% Server.ScriptTimeout = 120 %>
<%
    if ucase(trim(Request.QueryString("fmt"))) = "EXCEL" then
        Response.ContentType = "application/vnd.ms-excel"
    end if
%>
<% dim qs
    dim qs2
    dim offset
    dim dest
    dest = "http://www1.ifccfbi.gov/index.asp"
    qs = unescape(Request.QueryString())
    ...
    ...BlockXSS code removed from public view...
    ...
    ...
%>
<% set syte = CreateObject("ASP2SQL.SQLConnect") %>
<% syte.RejectSqlInject("I:\Syteview_DataControls\ASP2SQL\SQL_Injection_Strings.txt") %>
<% Dim BaseIP %>
<% BaseIP ="http://" & Request.Servervariables("SERVER_NAME") %>
<% Dim UserIP %>
<% UserIP ="http://" & Request.Servervariables("REMOTE_ADDR") %>
<%=syte.LogPage(Request.ServerVariables("LOGON_USER"), Request.ServerVariables("SERVER_NAME"), Request.ServerVariables("URL") & "?" & Request.ServerVariables("QUERY_STRING"), Request.ServerVariables("REMOTE_ADDR"), trim(dsn)) %>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">

Next the CUST-Lookup.asp web page displays the Multi-CAD Corporation Copyright Notice.  Do not remove or modify this notice.

Next is a <SCRIPT> tag:

   <script language='JAVASCRIPT'>

Followed by several javascript functions:

The Load() function - not used in the CUST-Lookup.asp web page, but it would be the place to place any code that you wished to run AFTER the page has been loaded.
btnOptions_click() function - used to open the Customer Detail page for a particular record.

         function btnOptions_click(custnum,custseq) {
           <% optWinName = 'Customer Details';
           <% optWindow = '' + '/CUSTOMER/CUST-DetailCredit.asp?custnum=' + custnum + '&custseq=' + custseq;
           <% window.open(optWindow,'','location=yes,resizable=yes,scrollbars=yes,status=yes,toolbar=yes,menubar=yes');
         }

Finally, there is the closing </SCRIPT> tag and then the closing </HEAD> tag.

The web page BODY is next, beginning with the <BODY> tag:

         <body bgcolor="#BCDCC8" language='JAVASCRIPT' topmargin="0" leftmargin="0" onload='Load()'>

Next the DimVars.inc include file is pulled in which simply declares some VBScript variables that are about to be used.

Then the Data Source Name variable dsn is set to "SQL-Server"

         <% dsn = "DSN=SQL-Server" %>

Next the parts of a table that will be used to display the returned records is built using string variables.  These string variables will be passed to the Syteview Data Control which will use them to create an HTML table to display the returned records in.  These variables give you complete control over how the records will be displayed.

         <% tblname = "tblCustomers" %>
         <% tblrows = "2000" %>
         <% tblattribs = "border='0' style='table-layout:fixed' width='140%'" %>

         Note: The table rows and table cells of this table will be defined later.

If the records are being displayed in an Excel spreadsheet instead of a web page then column headers are created:

         <% if ucase(trim(Request.QueryString("fmt"))) = "EXCEL" then %>

<% tblattribs =

tblattribs & "><tr>
<td>Cust #</td>
<td>Seq</td>
<td><u><b>Customer #</b></u></td>
<td>Address</td>
<td>City</td>
<td>State</td>
<td>zip</td>
<td>Country</td>
<td>Contact</td>
<td>Phone</td>
<td>Fax</td>
<td>Sales YTD</td>
<td>Sales PTD</td>
<td>Avg Days OS</td>
<td>Bal OS</td>
<td>Email</td>
<td>Cust Type</td>
</tr>"%>

         <% end if %>

Next the Stored Procedure that will retrieve the records is defined:

         <% storedprocedure = "GetCUSTOMERS" %>

And then the Stored Procedure parameters that were specified in the CUST-Lookup-Fields.asp web page are retrieved from the URL QueryString:

         <% sp_params = "'" & trim(Request.QueryString("recs")) & "','" %>

         <% sp_params = sp_params & trim(Request.QueryString("begcust")) & "','" %>
         <% sp_params = sp_params & trim(Request.QueryString("endcust")) & "','" %>

         <% sp_params = sp_params & trim(Request.QueryString("begcustseq")) & "','" %>
         <% sp_params = sp_params & trim(Request.QueryString("endcustseq")) & "','" %>

         <% sp_params = sp_params & trim(Request.QueryString("begname")) & "','" %>
         <% sp_params = sp_params & trim(Request.QueryString("endname")) & "','" %>

         <% sp_params = sp_params & trim(Request.QueryString("begcontact")) & "','" %>
         <% sp_params = sp_params & trim(Request.QueryString("endcontact")) & "','" %>

         <% sp_params = sp_params & trim(Request.QueryString("begcity")) & "','" %>
         <% sp_params = sp_params & trim(Request.QueryString("endcity")) & "','" %>

         <% sp_params = sp_params & trim(Request.QueryString("begstate")) & "','" %>
         <% sp_params = sp_params & trim(Request.QueryString("endstate")) & "','" %>

         <% sp_params = sp_params & trim(Request.QueryString("begzip")) & "','" %>
         <% sp_params = sp_params & trim(Request.QueryString("endzip")) & "','" %>

         <% sp_params = sp_params & trim(Request.QueryString("begzip")) & "','" %>
         <% sp_params = sp_params & trim(Request.QueryString("endzip")) & "','" %>

         <% sp_params = sp_params & trim(Request.QueryString("begcountry")) & "','" %>
         <% sp_params = sp_params & trim(Request.QueryString("endcountry")) & "','" %>

         <% sp_params = sp_params & trim(Request.QueryString("intl")) & "','" %>

         <% sp_params = sp_params & trim(Request.QueryString("begtype")) & "','" %>
         <% sp_params = sp_params & trim(Request.QueryString("endtype")) & "','" %>

         <% sp_params = sp_params & trim(Request.QueryString("pattern")) & "','" %>

         <% sp_params = sp_params & trim(Request.QueryString("wherestring")) & "','" %>
         <% sp_params = sp_params & trim(Request.QueryString("orderbystring")) & "','" %>
         <% sp_params = sp_params & trim(Request.QueryString("sqlstring")) & "','" %>

Next the table rows and cells that will display the returned records are defined in the rowattribs string variable:

         <% rowattribs = "<tr valign='top' height='44'>" %>

   If records are being retrieved into an Excel spreadsheet then they are not formatted, they are just put into table cells:

Note:

The record fields are replaced by #FIELDx# tokens in the rowattribs variable. The Syteview Data Control will replace the #FIELDx# tokens with the actual record fields after retrieving the records from the SQL Server.

         <% if ucase(trim(Request.QueryString("fmt"))) = "EXCEL" then %>
         <%   rowattribs = rowattribs & "<td>#FIELD0#</td>" %>
         <%   rowattribs = rowattribs & "<td>#FIELD1#</td>" %>
         <%   rowattribs = rowattribs & "<td>#FIELD2#</td>" %>
         <%   rowattribs = rowattribs & "<td>#FIELD3#</td>" %>
         <%   rowattribs = rowattribs & "<td>#FIELD4#</td>" %>
         <%   rowattribs = rowattribs & "<td>#FIELD5#</td>" %>
         <%   rowattribs = rowattribs & "<td>#FIELD6#</td>" %>
         <%   rowattribs = rowattribs & "<td>#FIELD15#</td>" %>
         <%   rowattribs = rowattribs & "<td>#FIELD7#</td>" %>
         <%   rowattribs = rowattribs & "<td>#FIELD8#</td>" %>
         <%   rowattribs = rowattribs & "<td>#FIELD9#</td>" %>
         <%   rowattribs = rowattribs & "<td>#FIELD10#</td>" %>
         <%   rowattribs = rowattribs & "<td>#FIELD11#</td>" %>
         <%   rowattribs = rowattribs & "<td>#FIELD12#</td>" %>
         <%   rowattribs = rowattribs & "<td>#FIELD13#</td>" %>
         <%   rowattribs = rowattribs & "<td>#FIELD14#</td>" %>
         <%   rowattribs = rowattribs & "<td>#FIELD16#</td>" %>

   But if the records are being retrieved into a web page then they are formatted here:
         <% else %>

         <%   rowattribs = rowattribs & "<td width='2%'><input type='BUTTON' name='btnOptions#ROW#' id='btnOptions#ROW#' value='>' onclick='btnOptions_click(""#FIELD0#"",""#FIELD1#"")'></td>" %>

         <%   rowattribs = rowattribs & "<td width='6%' align='LEFT'><FONT color='#B04040' size='2'> <a href='" & BaseIP & "/CUSTOMER/CUST-DetailCredit.asp?custnum=#FIELD0#&custseq=#FIELD1#' title='#REPLACE039096##FIELD7# #FIELD8##ENDREPLACE#'>#FIELD0#-#FIELD1#</a></font></td>" %>

         <%   rowattribs = rowattribs & "<td width='18%' align='LEFT'><font color='#E84030' size='2'>#FIELD2#</font></td>" %>

         <%   rowattribs = rowattribs & "<td width='17%' align='LEFT'><font size='2'>#FIELD4#, #FIELD5#  #FIELD6#" & chr(13) & "#FIELD15#</font></td>" %>

         <%   rowattribs = rowattribs & "<td width='7%' align='RIGHT'><font color='#3040E8' size='2'>#FIELD10#</font></td>" %>

         <%   rowattribs = rowattribs & "<td width='7%' align='RIGHT'><font color='#2030D0' size='2'>#FIELD11#</font></td>" %>

         <%   rowattribs = rowattribs & "<td width='6%' align='RIGHT'><font color='#F02020' size='2'>#FIELD12# days</font></td>" %>

         <%   rowattribs = rowattribs & "<td width='7%' align='RIGHT'><font color='#F01000' size='2'>#FIELD13#</font></td>" %>

         <%   rowattribs = rowattribs & "<td width='1%' align='RIGHT'> </td>" %>

         <%   rowattribs = rowattribs & "<td width='20%' align='LEFT'><font size='2'>#FIELD16#</font></td>" %>

         <%   rowattribs = rowattribs & "<td width='*' align='RIGHT'><font size='2'>#FIELD14#</font></td>" %>

         <% endif %>

Retrieving Records with the Data Control

Finally, after the Stored Procedure and stored procedure parameters have been put in the storedprocedure and sp_params variables and the table that will display the returned records has been defined with the tblname, tblattribs and rowattribs variables, the variables are passed to the Syteview Data Control with the GetTableRows() function which will retrieve the records from the SQL Server and then put them into the HTML table that you have defined.

The code that does this is pulled in by the GetTableRows.inc include file:

<%= syte.GetTableRows("","",trim(dsn),trim(tblname),trim(tblcolumns),trim(tblrows),trim(pretblattribs),trim(tblattribs),trim(posttblattribs),trim(prerowattribs),trim(rowattribs),trim(postrowattribs),trim(precellattribs),trim(cellattribs),trim(postcellattribs),trim(storedprocedure),trim(sp_params),trim(closingattribs)) %>

Finally, the CUST-Lookup.asp web page is closed by pulling in the FooterDataTable.inc include file which adds the closing </BODY> and </HTML> tags.

Modifying CUST-Lookup.asp There are at least two ways you may wish to modify the CUST-Lookup.asp web page.

First, in the CUST-Lookup-Fields.asp example we added the begship and endship parameters to allow a user to lookup Customers by specifying a shipping code, or a range of shipping codes.  In order to retrieve the two new parameters you need to add the following two lines to CUST-Lookup.asp:

         <% sp_params = sp_params & trim(Request.QueryString("begship")) & "','" %>
         <% sp_params = sp_params & trim(Request.QueryString("endship")) & "','" %>

Note: The GetCUSTOMERs stored procedure is also going to have to be modified to accept the two new parameters.

Another way you may wish to change the CUST-Lookup.asp web page is to change the fields that are retrieved or how they are displayed.

To do this, you simply modify the rowattribs variable, adding or removing any #FIELDx# tokens that represent the fields you want to add or remove from the HTML table.  Any HTML table that you care to define can be displayed.   You can choose from any of the fields returned by the GetCUSTOMERs stored procedure.  Hyperlinks, onclick events, mouseovers, or any other DHTML can be included in the rowattribs variable and will then be included in the HTML table returned by the Syteview Data Control.  The only thing to remember is that the table defining string variables that are passed to the Data Control must define a complete and valid HTML table.  Other than that, you can display the returned records any way you wish.

Summary

The CUST-Lookup-Fields.asp web page is used to collect the parameters that will be passed to the CUST-Lookup.asp web page which will format an HTML table and pass it and the parameters to the Syteview Data Control which will retrieve the records from the SQL Server and pass them back to the CUST-Lookup.asp web page where they are displayed as an HTML table.