%{----------------------------------------------------------------------------%}
%{ Table4.ndm -- Net.Data table processing, example 4                         %}
%{ Create link from customer number to display detail form.                   %}
%{                                                                            %}
%{ Copyright (c) 2000, Craig Pelkie                                           %}
%{ ALL RIGHTS RESERVED                                                        %}
%{----------------------------------------------------------------------------%}

%{----------------------------------------------------------------------------%}
%{ Define section for the macro                                               %}
%{----------------------------------------------------------------------------%}
%define {
    DATABASE           = "*LOCAL"
    DTW_DEFAULT_REPORT = "NO"
    sqlTable           = %TABLE
%}

%{----------------------------------------------------------------------------%}
%{ RUNSQL - run the SQL statement, return %TABLE variable                     %}
%{----------------------------------------------------------------------------%}
%function(DTW_SQL) RUNSQL(IN  sqlString,
                          OUT sqlTable) {
    $(sqlString)
%}

%{----------------------------------------------------------------------------%}
%{ OUTPUT_TABLE - macro function to work with %TABLE variable                 %}
%{----------------------------------------------------------------------------%}
%macro_function OUTPUT_TABLE (IN sqlTable) {
	
    <!---------------------------------------------------
	Table headers
    ---------------------------------------------------->
    <br>

    <table  border="0"
            cellpadding="1"
            bgcolor="lightcyan">

	<tr  bgcolor="lightsteelblue">

	    <th><font size="-2">
		@dtw_tb_rgetn(sqlTable, "1")<br>Customer Number
	    </font></th>
			
	    <th><font size="-2">
		@dtw_tb_rgetn(sqlTable, "2")<br>Last Name
	    </font></th>
			
	    <th><font size="-2">
		@dtw_tb_rgetn(sqlTable, "3")<br>Initials
	    </font></th>
			
	    <th><font size="-2">
		@dtw_tb_rgetn(sqlTable, "4")<br>Street
	    </font></th>
			
	    <th><font size="-2">
		@dtw_tb_rgetn(sqlTable, "5")<br>City
	    </font></th>
			
	    <th><font size="-2">
		@dtw_tb_rgetn(sqlTable, "6")<br>State
	    </font></th>
			
	    <th><font size="-2">
		@dtw_tb_rgetn(sqlTable, "7")<br>Zip Code
	    </font></th>
			
	    <th><font size="-2">
		@dtw_tb_rgetn(sqlTable, "8")<br>Credit Limit
	    </font></th>
			
	    <th><font size="-2">
		@dtw_tb_rgetn(sqlTable, "9")<br>Charge Code
	    </font></th>
			
	    <th><font size="-2">
		@dtw_tb_rgetn(sqlTable, "10")<br>Balance Due
	    </font></th>
			
	    <th><font size="-2">
		@dtw_tb_rgetn(sqlTable, "11")<br>Credit Due
	    </font></th>

	</tr>
		
	<!---------------------------------------------------
		Table rows
	---------------------------------------------------->
	@dtw_tb_rows(sqlTable, numRows)
	@dtw_assign(rowIndex, "1")
	@dtw_assign(color, "lightcyan")		
	
	%while (rowIndex <= numRows) {

	    <tr	bgcolor=$(color)>

		@dtw_tb_getv(sqlTable, rowIndex, "1", cusnum)
							
		<td align="center"><font size="-2">
		    <a href="CUSTOMER_DETAIL?cusnum=$(cusnum)">$(cusnum)</a>
		</font></td>
			
		<td><font size="-2">
		    @dtw_tb_rgetv(sqlTable, rowIndex, "2")
		</font></td>
			
		<td><font size="-2">
		    @dtw_tb_rgetv(sqlTable, rowIndex, "3")
		</font></td>
			
		<td><font size="-2">
		    @dtw_tb_rgetv(sqlTable, rowIndex, "4")
		</font></td>
			
		<td><font size="-2">
		    @dtw_tb_rgetv(sqlTable, rowIndex, "5")
		</font></td>
			
		<td><font size="-2">
		    @dtw_tb_rgetv(sqlTable, rowIndex, "6")
		</font></td>
			
		<td><font size="-2">
		    @dtw_tb_rgetv(sqlTable, rowIndex, "7")
		</font></td>
			
		<td align="right"><font size="-2">
		    @dtw_tb_rgetv(sqlTable, rowIndex, "8")
		</font></td>
			
		<td align="center"><font size="-2">
		    @dtw_tb_rgetv(sqlTable, rowIndex, "9")
		</font></td>
			
		<td align="right"><font size="-2">
		    @dtw_tb_rgetv(sqlTable, rowIndex, "10")
		</font></td>
			
		<td align="right"><font size="-2">
		    @dtw_tb_rgetv(sqlTable, rowIndex, "11")
		</font></td>																				
	    </tr>

	    %if (color == "lightcyan")
		@dtw_assign(color, "silver")
	    %else
		@dtw_assign(color, "lightcyan")
	    %endif
		
	    @dtw_add(rowIndex, "1", rowIndex)
	%}

    </table>
%}

%{----------------------------------------------------------------------------%}
%{ INPUT - initial section called, calls RUNSQL macro function                %}
%{----------------------------------------------------------------------------%}
%html(INPUT) {

    <html>
	<head>
	    <title>Net.Data macro Table4.ndm</title>
	</head>

	<body>
	    <center>
		<h1>Net.Data macro Table4.ndm</h1>
		@dtw_assign(sqlString, "select * from qiws.qcustcdt order by cusnum")
		@RUNSQL(sqlString, sqlTable)
		@OUTPUT_TABLE(sqlTable)
	    </center>
	</body>
    </html>

%}

%{----------------------------------------------------------------------------%}
%{ CUSTOMER_DETAIL - display detail record for a customer                     %}
%{----------------------------------------------------------------------------%}
%html(CUSTOMER_DETAIL) {

	%{------------------------------------------------------------------------%}
	%{ Extract customer number from QUERY_STRING environment variable         %}
	%{------------------------------------------------------------------------%}	
	@dtw_assign(queryString, @dtw_rgetenv("QUERY_STRING"))
	@dtw_assign(eqPos,       @dtw_rpos("=", queryString))
	@dtw_assign(qsCusnum,    @dtw_rsubstr(queryString, @dtw_radd(eqPos, "1")))
	@dtw_assign(sqlString,   "select * from qiws.qcustcdt where cusnum = $(qsCusnum)")

	@RUNSQL(sqlString, sqlTable)

	<head>
	    <title>Net.Data macro Table4</title>
	</head>
	
	<body>
	    <center>
		<h1>Detail for Customer</h1>
	
		<!---------------------------------------------------------------------
		    Customer Detail table
		---------------------------------------------------------------------->
		<table	bgcolor="lightcyan"
			border="2"
			cellpadding="3">
	
			<!-------------------------------------------------------------
			    Table heading
			-------------------------------------------------------------->
			<tr>
			    <td	colspan="2"
				align="middle"
				bgcolor="lightblue">
				
				<font color="navy">Customer Details</font>
			    </td>
			</tr>
	
			<!------------------------------------------------
				Customer number
			------------------------------------------------->
			<tr>
			    <td	width="40%"
				bgcolor="linen">

				<font size="-2">Customer number</font>
			    </td>
				
			    <td>
				<font size="-2">@dtw_tb_rgetv(sqlTable, "1", "1")</font>
			    </td> 
			</tr>


			<!------------------------------------------------
				Name (Initials Lastname)
			------------------------------------------------->
			<tr>
			    <td	bgcolor="linen">
				<font size="-2">Name</font>
			    </td>
				
			    <td>
				<font size="-2">@dtw_tb_rgetv(sqlTable, "1", "3")&nbsp;
				                @dtw_tb_rgetv(sqlTable, "1", "2")
				</font>
			    </td>  
			</tr>

    			<!------------------------------------------------
				Street
			------------------------------------------------->
			<tr>
			    <td	bgcolor="linen">
				<font size="-2">Street</font>
			    </td>
				
			    <td>
				<font size="-2">@dtw_tb_rgetv(sqlTable, "1", "4")</font>
			    </td>  
			</tr>

			<!------------------------------------------------
				City State Zipcode
			------------------------------------------------->
			<tr>
			    <td	bgcolor="linen">
				<font size="-2">City, State Zip</font>
			    </td>
				
			    <td>
				<font size="-2">@dtw_tb_rgetv(sqlTable, "1", "5"), 
					       @dtw_tb_rgetv(sqlTable, "1", "6")&nbsp;
					       @dtw_tb_rgetv(sqlTable, "1", "7")</font>
			    </td> 
			</tr>

	    		<!------------------------------------------------
				Credit Limit
			------------------------------------------------->
			<tr>
			    <td bgcolor="linen">
				<font size="-2">Credit Limit</font>
			    </td>
				
			    <td>
				<font size="-2">@dtw_tb_rgetv(sqlTable, "1", "8")</font>
			    </td> 
			</tr>
	
			<!---------------------------------------------------------------------
				"Back" button
			----------------------------------------------------------------------->		
			<tr bgcolor="lightblue">
			   <td	align="center"
			   	colspan="2">

			      <input type="button"
				    value="Return to Query"
				    onClick="history.back()">
			    </td>
			</tr>
		</table>
	    </center>
	</body>
%}