Published online 3 August 2006
Published in Agron J 98:1181-1186 (2006)
DOI: 10.2134/agronj2005.0142
© 2006 American Society of Agronomy
677 S. Segoe Rd., Madison, WI 53711 USA
Computers in Agriculture
An Automated Template Approach for Generating Web-Based Conservation Planning Worksheets
Jeffrey J. Steinera,*,
Toshimi Minourab and
Mariko Imaedab
a USDA-ARS, National Forage Seed Production Research Center, 3450 S.W. Campus Way, Corvallis, OR 97331
b College of Electrical Engineering and Computer Sci., Oregon State Univ., Corvallis, OR 97331
* Corresponding author (jjs{at}ars.usda.gov)
Received for publication May 12, 2005.
 |
ABSTRACT
|
|---|
An automated web-form generator system was created to rapidly produce dynamic web-based inventory and assessment worksheets that are used by the USDA Natural Resources Conservation Service (NRCS) to describe the condition of natural resources on farms and ranches. These worksheets are used by NRCS as part of the criteria for creating a conservation plan to qualify landowners for payments under the USDA Farm Bill Conservation Title. Presently, most worksheets are filled out by hand or in personal computer spreadsheets. If worksheets such as these were available over the web, landowners could also self-assess the conditions on their farms or ranches before requiring assistance of NRCS conservation planners or technical service providers. Because many conservation worksheets have a similar style and behavior, we created an automated web-script generator that produces a dynamic web-page worksheet from a common template file and a configuration file. A worksheet-specific configuration file provides the details for the worksheet. The template, configuration files, and web scripts are all in PHP (PHP hypertext processor). Each configuration file required approximately 5 h to program, a savings of more than 4 d compared with a web-based worksheet programmed individually with a scripting language. In this way, various conservation worksheets can be quickly created for the web, as well as be easily maintained. As an example, we produced a suite of assessment worksheets used by Oregon NRCS with our automated web-form conservation worksheet system. Those worksheets are accessible at: http://yukon.een.orst.edu/ms_apps/wq_indexes/forms/index_menu.html (verified 5 May 2006).
Abbreviations: N-index, nitrogen index • NRCS, United States Department of Agriculture, Natural Resources Conservation Service • PHP, PHP hypertext processor
|
INTRODUCTION
|
|---|
THE web is becoming increasingly accessible to everyone and could provide a platform for landowner self-assessment and delivery of conservation planning and program services over the internet (R. Hatz, personal communication, 2004). Presently, NRCS conservation planners or technical service providers visit the farm or ranch to collect the information needed to complete a conservation plan for the landowner. The conservation plan is then used to determine eligibility for payments under the Conservation Title of the USDA Farm Bill. To expedite the planning process, it would be advantageous for landowners to contribute as much information as possible to the individuals who provide conservation planning services. This would reduce the total time needed by conservation planners to service a client, so each planner could service more clients. This is critical because of recent funding increases of Farm Bill conservation payments and greater accessibility of conservation programs to more farmers.
The NRCS uses various inventory and assessment worksheets as indicator tools to describe current, past, or future conditions of natural resources on farms and ranches (USDA-NRCS, 2000). Examples of these from the Oregon Water Quality Technical Note No. 1 include the nitrate index, phosphorus index, and nutrients, organics, and pathogens—livestock yard management worksheets (Table 1). Tools such as these can also be used to help educate landowners of water quality or other natural resources considerations, determine whether problems exist, set benchmark conditions, guide natural resources inventories, and understand how different conservation practices can reduce or eliminate pollution risks.
View this table:
[in this window]
[in a new window]
|
Table 1. The conservation worksheet tools presented in Oregon Water Quality Technical Note No. 1 (USDA-NRCS, 2000) that were implemented over the internet using the automated worksheet web-form generator tool.
|
|
Presently, worksheets are available as printed pages or as spreadsheets on personal computers. Problematic to web delivery of worksheets is the great number of kinds of conservation indices and variations of worksheets specific for different states. A significant amount of time would be required to individually program each worksheet as a dynamic web-based worksheet in a scripting language (e.g., ASP Visual Basic [Microsoft, Redmond, WA], PHP [www.PHP.org], ASP.NET Visual Basic [Microsoft, Redmond, WA] or C# [Microsoft, Redmond, WA], JSP [Sun, Mountain View, CA], and perl [www.perl.org]).
Writing a typical web script for resource conservation is simpler than writing a program in an ordinary programming language, but still requires approximately 5 d of coding. Since most NRCS conservation worksheets have a similar style and operational function, we were able to implement a variety of worksheets with an automated worksheet web-form generator tool that encodes the common style and behavior of worksheets as a template. The specific content of each different worksheet is specified as a configuration file.
Another advantage of a template-based system is that a programming error need be fixed only once in the common template. Changes are then automatically propagated to all the worksheets that are produced from that template. Web-based delivery also allows updates to be made seamlessly without need for user updates.
This article is organized as follows: An overview of web-based worksheets, the N-index worksheet in particular, is given first. The details about implementation of the automatic worksheet generation system are then described. Finally, some remarks about the system are given.
|
WEB-BASED WORKSHEETS OVERVIEW
|
|---|
Our automated web-form conservation worksheet system for creating web-based inventory and assessment worksheets was demonstrated by implementing 15 water quality indicator tools presented in Oregon Water Quality Technical Note No. 1 (USDA-NRCS, 2000) (Table 1). One of these, the Oregon Nitrogen-Index (N-index), is used to demonstrate our template-based process for implementing conservation worksheets as dynamic web pages. A slight modification to the Oregon NRCS N-index includes N loss to surface water by irrigation water-caused soil erosion (USDA-NRCS, 2002) (Fig. 1
). The Portland NRCS State Office recommended these worksheets because they are commonly used by their staff. Only the specific code for the N-index is presented in this article.
View larger version (66K):
[in this window]
[in a new window]
|
Fig. 1. Example of a worksheet created by the automated web-form generator. Shown is the Oregon USDA-NRCS nitrogen index calculation with a modification using the calculation for the New Mexico USDA-NRCS surface irrigation erosion loss.
|
|
Web-Based Worksheet
In our approach, a single template is shared by different worksheets having similar organizational structure with the uniqueness of each realized by its configuration file. The template, configuration files, and web scripts are all coded in PHP (http://www.php.net; verified 5 May 2006), a widely used scripting language for creating dynamic web pages that can generate HTML (hyper text markup language) statements. Since PHP is a good tool for text manipulation, it can be used to generate a web script from the template and a configuration file. Once a web script is generated, it is loaded on a web server. Each time a user requests a specified conservation index calculation, the web script is executed by the server-side PHP interpreter.
Nitrogen-Index as an Example
The N-index worksheet is a matrix with 13 field characteristics and five value factors. Each field characteristic has a weighting factor. Each characteristic's weighting factor is multiplied by a value factor. The sum of the values gives the N-index result. Source and transport factors define the field characteristics. Source factors include N application rate, N form, commercial or organic fertilizer timing, and application method. Transport factors include runoff class, permeability class, annual precipitation amount, irrigation water management, water runoff management, irrigation erosion amount, distance to a surface water body, and distance to an aquifer. Surface and leaching N losses are both calculated by this indicator tool. The other 14 worksheets implemented by our system are similar to the N-index and also described in NRCS (2000). These worksheet examples can be accessed on the demonstration site to demonstrate the utility of our automated worksheet generation approach.
|
WORKSHEET IMPLEMENTATION
|
|---|
The N-index web-based worksheet (Fig. 1) was created following the template-based web script generation process (Fig. 2
). The unique feature of our implementation method is that the PHP processor is used twice. In addition to being used to process web scripts on the web server, it is used to generate those web scripts from the template and configuration files. The online PHP processor on the server is activated each time a request for a worksheet is received from a web browser. On the other hand, the offline PHP processor is used only to prepare the web scripts. Once prepared, the web scripts can be activated repeatedly on the web server. The detailed steps are as follows:- The template file indexgen.tmpl (Fig. 3
and 4)
is read by an offline PHP processor.
- When the interpretation of the template is started, the configuration file n_index.config (Fig. 5
) that specifies uniqueness characteristics of the N-index worksheet is read by the template. The template is an executable code that is interpreted by the offline PHP processor.
- The web script (Fig. 6
) is generated from the template and the configuration file by the offline PHP processor.
- The PHP processor on the web server reads the PHP script. This PHP processor is different from the offline PHP processor.
- The online PHP processor generates an HTML page (Fig. 7
). A web browser receives the HTML page generated by the web script.
- When the submit button on the worksheet is clicked, the user inputs are sent back to the web server.
- The PHP processor interprets the same PHP script by using the user inputs and generates the HTML page including the calculation result.
- The calculation result is sent back to the web browser.
View larger version (22K):
[in this window]
[in a new window]
|
Fig. 2. The worksheet creation process by the automated template. Each process step is described in the text.
|
|
View larger version (26K):
[in this window]
[in a new window]
|
Fig. 3. A part in the template file indexgen.tmpl that generates the web script given in Fig. 6 according to the configuration file shown in Fig. 5. This part handles the processing of each factor in the worksheet.
|
|
View larger version (32K):
[in this window]
[in a new window]
|
Fig. 4. The part of the template file indexgen.tmpl that generates the code for the index calculation part of the web script given in Fig. 6.
|
|
Configuration File
A configuration file specifies the content of a worksheet by using the elements described in Table 2. The actual worksheet program can be generated from it without any human intervention. The configuration file n_index.config (Fig. 5) is for the N-index worksheet (Fig. 1). This configuration file makes the N-index worksheet unique from the others listed in Table 1. Each unique worksheet must have its own unique configuration file. The syntax of the configuration file is that of PHP. Lists of items are represented as arrays, some as associative arrays. An associative array stores each array element as a pair of a keyword and a value such as label = > ‘Surface Loss Rating’. The PHP interpreter accepts this representation of data as PHP statements without any processing.
Web Form
A web form is the final worksheet displayed by a web browser. This worksheet consists of HTML statements. The web form (Fig. 7) is for the N-index worksheet (Fig. 1). The data are displayed as a table specified by HTML statements, and the user interaction is handled by JavaScript statements. One web form is used to accept the inputs from the user and to display the calculation result. Figure 7 shows how the row for the Runoff Class factor in Fig. 1 is represented in HTML.
Web Script
A web script generates the initial worksheet, performs calculation, and generates the worksheet containing the calculated result. A web script is executed on a server, and the web forms generated are sent to a web browser. The web script n_index_calc.phtml (Fig. 6) written in PHP generates the N-index web form in HTML (Fig. 7). The statements inside a pair of <? and ?> are PHP statements that are executed (interpreted) by the online PHP processor.
Template File
The template file indexgen.tmpl (Fig. 3 and 4) specifies the style of a worksheet and its parameters. In the template code, some code is enclosed within <? and ?> or <% and %>. The code within <? and ?> is executed by the offline PHP processor when the web script is generated. The code within <% and %> is passed to the web script without any change, except <% is changed to <? and %> to ?> so that the code can be executed by the PHP interpreter on the web server. The rest of the code is also passed to the web script without any change.
The code used to generate the statements that process each factor (row) in the worksheet is shown in Fig. 3. Within the for loop, the following steps are taken for each factor:
- Two variables are retrieved from the configuration file (Lines 3–4).
- $_label The label for the ith factor
- $_weight The weight for the ith factor
- Variable $_level indicates the level selected by the user (Line 5).
- The label column is generated with $_label, and the weight column with $_weight (Lines 7–19).
- To annotate the levels of the current factor, $descriptions is retrieved from the ith factor in $factors (Line 21).
- The cells for the descriptions for the ith factor are filled (Lines 23–32).
- The subtotal column(s) will be generated. If there is only one subtotal column defined in the configuration file, then one subtotal column is generated (Lines 33–39). Otherwise, multiple subtotal columns are generated. In this case, a cell is shaded if $_hide is true (Lines 40–57) and then the code for calculating the subtotals is generated (Lines 36–37, 52–53).
- Finally, the code that displays an error flag is generated when an error is found for the ith factor (Lines 59–62).
The Calculate button displayed on the worksheet (Fig. 1) is used to submit a request to calculate the index rating. The steps that handle index calculation (Fig. 4) for one subtotal column are as follows:
- Label total weighted rating is displayed (Lines 3–6).
- The total weighted rating $total_rating is calculated as the sum of subtotal (Lines 8–11).
- The result of Step 2 is displayed in a cell (Lines 13–16).
- The label Site Vulnerability is displayed (Lines 19–23).
- $total_rating is compared with each of the values in $range_upper_bounds to determine the vulnerability range (Lines 24–28).
- The range is displayed in a cell (Lines 29–32).
|
REMARKS
|
|---|
We have used our automated web-form conservation worksheet generator to demonstrate rapid implementation of 15 Oregon NRCS water quality indicator tools as dynamic web forms that can easily be used by landowners, NRCS conservation planners, and technical service providers. We estimate the amount of time required to manually program each individual worksheet would be 5 d. Once our automated web-page generator was created, each of the worksheets required 5 h to produce. This was the time needed to write the configuration file for each specific conservation worksheet.
Our automated web-form generator provides a relatively simple and economic way to produce customized conservation index worksheets that can be readily accessed and used over the internet. Web-based worksheets are easy-to-use and should help increase landowner participation in the conservation planning process and increase conservation awareness. It is estimated that with this initial implementation of Oregon conservation worksheets, NRCS conservation planning staff will save 200 h in time spent assisting with preparation of landowner conservation plans (R. Hatz, personal communication, 2004). The web-based worksheet tools could also be adapted for use in a geographic information system (GIS) web database to organize information about fields within farms or ranches and help develop farm or ranch-wide conservation plans.
The suite of conservation worksheets described in Water Quality Technical Note No. 1–Water Quality Indicator Tools (USDA-NRCS, 2000) is demonstrated at: http://yukon.een.orst.edu/ms_apps/wq_indexes/forms/index_menu.html (verified 5 May 2006). Additional documentation for the automated web-form generator conservation worksheet system is available free on request from the corresponding author. The authors ask that when any component parts of our system are used, that citation credit be given.
|
ACKNOWLEDGMENTS
|
|---|
This research was supported in part by a grant from the CALFED Bay-Delta Ecosystem Restoration Program (ERP 01-N25), in cooperation with P. Robins, executive director of the Yolo County Resource Conservation District. We also thank the Oregon USDA-NRCS state office for input about and testing of the conservation worksheet tools implemented. Particular thanks goes to Russ Hatz, leader for technology, and the staff of the USDA-NRCS state office in Portland, OR, for their guidance and assistance with testing of the web-based worksheets.
|
NOTES
|
|---|
The use of trade, firm, or corporation names in this publication is for the information and convenience of the reader. Such use does not constitute an official endorsement or approval by the USDA or the Agricultural Research Service of any product or service to the exclusion of others that may be suitable.
|
REFERENCES
|
|---|
- USDA-NRCS. 2000. Water quality technical note no. 1. Water quality indicator tools. USDA, Natural Resources Conservation Service, Portland OR.
- USDA-NRCS. 2002. Water quality technical note no. 10. Water quality indicator tools. USDA, Natural Resources Conservation Service, Albuquerque, NM.
TOP
ABSTRACT
INTRODUCTION
