Custom Web Form: Contract Generator in PHP & MySQL

Project Goal

To improve the process for generating contracts for Eaton Academy’s Independent Studies Program.

The Client

Eaton Academy, private school in metro Atlanta

The Problem

Because of the complexities of Eaton’s Independent Study Program (“ISP”) offerings and because of the limitations of the tools available for creating contracts, the existing process for generating Independent Study Program contracts was complicated, time-consuming and error-prone.

Accordingly, the responsibility for generating these contracts had to fall on the shoulders of the ISP Program Director, who also happens to be the Vice President and CFO of the organization.

Complexities of ISP Offerings

Eaton has three fundamental “flavors” of ISP — Tutorial, Basic, and Distance Learning — each with its own price structure. Tutorial ISP includes On-Site tutorial services and may or may not include Study Periods; Basic and Distance ISP programs include neither. Furthermore, a student can contract with Eaton for a hybrid ISP program: one that has both Tutorial and Basic elements.

Additional factors figure into the financial calculations: international students incur surcharges; “crossover” students – those who are simultaneously enrolled in the school’s Secondary program – enjoy discounted rates; returning ISP students who did not use all of the tutorial sessions they paid for in the prior semester are entitled to credits; and it is also possible that miscellaneous charges not associated with any other line item might need to be included. (You can have a glimpse at a couple other examples of complexities further down the page.)

Limitations of Available Tools

The existing process for generating ISP contracts utilized a free-form word-processing document that required the operator to manually calculate and embed all fees, installment payments, and payment due dates and to manually insert other contract-specific words and phrases in their respective places within the document. Computation errors could slip through unnoticed. And it was easy to forget about special circumstances that are not the norm.

Although Eaton had implemented a School Management System (RenWeb) at the beginning of academic year 2014-2015, one of the few functions RenWeb had difficulty accommodating was contract generation for ISP, because of the above-described complexities.

Solution

Initial Considerations

We initially considered enhancing the existing word-processing method by creating a Microsoft Word template with built-in rates and formulas, protected regions, and automated calculations. We even brought in a partner company that specializes in customized Word templates to consult.

This approach was abandoned when it became clear that we would have been trying to make Microsoft Word work like an automated Web form driven by PHP scripts interacting with MySQL data – a technology space in which nSiteful has considerable experience and expertise.

The Ultimate Solution: Custom Web Form using PHP and MySQL

Ultimately we designed and developed a Web-based contact-generator form as an addition to an Intranet system we built for the school and have been maintaining for several years.

Under the new system, rates are stored in and served from a database. (In fact, the public-facing tuition-related pages of Eaton’s Web site have been referencing those database-driven rate schedules for years.) Other numerical factors, those that are not likely to change often or ever (e.g., returned-check fees; late charge percentages; installment payment due dates and percentages), are registered in the application. All computations are automated.

The form has built-in validation logic that prevents most types of anomalous information from being submitted. In addition to requiring that required fields be filled in (duhhh), the system won’t allow the operator to enter an end date that precedes the start date; to enter a quantity of courses in a category without entering a listing of those courses (or vice versa); or to submit the form without entering a quantity and listing for at least one category of courses. User-friendly error messages appear both at the top of the form and next to the culprit form fields.

The top section of the form is for “Basic Information” — student’s name; whether he or she is an international and/or Crossover student; contract issue date; and start and end dates for his or her program. Next are three sections, one each for the different “flavors” of ISP courses: Tutorial, Basic, and Distance. Note that there are places for Tutorial Periods and Study Periods in the top (“Tutorial”) section.

Finally, there is a section for adjustments (credit and/or debit). At the bottom of this section are the buttons for “Preview” and “Save”.

Once the operator enters appropriate and validated information, clicking the “Preview” button generates a page that displays the printable contract at the top (including a stipulations section of more than twenty paragraphs) and the form, with previously entered data, at the bottom. If changes are needed, the operator can make them in the form and click the “Preview” button again.

Once the operator is satisfied that the resulting contract is accurate, he or she can print the contract, either to the printer or to a PDF file. Special formatting rules for printing, included in an @media print media query in the CSS style sheet, hide everything on the page except the contents of the contact and establish appropriate page breaks and orphan/widow controls.

If desired, the operator can also save the contract to the database, for future reference and/or editing. Because rates can change over time, the database stores a snapshot of everything submitted, including the then-current rates, surcharges, installment payment due dates and percentages, etc. Below is the structure of the database table for saved contracts.

--
-- Table structure for table 'isp_contract'
--

CREATE TABLE IF NOT EXISTS isp_contract (
	id bigint(20) NOT NULL AUTO_INCREMENT,
	student_first_name varchar(128) NOT NULL,
	student_last_name varchar(128) NOT NULL,
	is_international tinyint(1) DEFAULT NULL,
	is_crossover tinyint(1) DEFAULT NULL,
	contract_issue_date date DEFAULT NULL,
	session_week_start date DEFAULT NULL,
	session_week_end date DEFAULT NULL,
	n_half_tutorial tinyint(2) DEFAULT NULL,
	courses_half_tutorial varchar(255) DEFAULT NULL,
	n_full_tutorial tinyint(2) DEFAULT NULL,
	courses_full_tutorial varchar(255) DEFAULT NULL,
	n_tutorial_hours smallint(3) DEFAULT NULL,
	n_study_periods smallint(3) DEFAULT NULL,
	n_half_basic tinyint(2) DEFAULT NULL,
	courses_half_basic varchar(255) DEFAULT NULL,
	n_full_basic tinyint(2) DEFAULT NULL,
	courses_full_basic varchar(255) DEFAULT NULL,
	n_half_distance tinyint(2) DEFAULT NULL,
	courses_half_distance varchar(255) DEFAULT NULL,
	n_full_distance tinyint(2) DEFAULT NULL,
	courses_full_distance varchar(255) DEFAULT NULL,
	fee_half_tutorial decimal(6,2) NOT NULL,
	fee_full_tutorial decimal(6,2) NOT NULL,
	fee_per_hour_onsite decimal(6,2) NOT NULL,
	fee_per_hour_onsite_crossover decimal(6,2) NOT NULL,
	fee_per_study_period decimal(6,2) NOT NULL,
	fee_half_basic decimal(6,2) NOT NULL,
	fee_full_basic decimal(6,2) NOT NULL,
	fee_half_distance decimal(6,2) NOT NULL,
	fee_full_distance decimal(6,2) NOT NULL,
	fee_application decimal(6,2) NOT NULL,
	fee_application_international decimal(6,2) NOT NULL,
	fee_surcharge_international decimal(7,2) NOT NULL,
	late_fee_monthly decimal(5,3) NOT NULL,
	late_fee_annual decimal(6,2) NOT NULL,
	returned_check_fee decimal(6,2) NOT NULL,
	adj_credit_amount decimal(9,2) DEFAULT NULL,
	adj_credit_description varchar(255) DEFAULT NULL,
	adj_debit_amount decimal(9,2) DEFAULT NULL,
	adj_debit_description varchar(255) DEFAULT NULL,
	total_tuition decimal(9,2) NOT NULL,
	pmt_1 decimal(9,2) NOT NULL,
	balance_after_deposit decimal(9,2) NOT NULL,
	pmt_2 decimal(9,2) NOT NULL,
	pmt_2_date date DEFAULT NULL,
	pmt_3 decimal(9,2) NOT NULL,
	pmt_3_date date DEFAULT NULL,
	dt_added datetime DEFAULT NULL,
	PRIMARY KEY (id)
) 

Minutia

Here are brief elaborations on a couple of the complexities I referred to at the outset of this article. If you’re a developer and have other/better ideas about how to perform these computations, feel free to comment below. If you’re not a developer, the code might mean nothing to you, but you might still get a sense of the potential power of custom-developed forms.

Installment Payments

The business rule for installment payments is as follows:

• Amount Due with signed contract: 60% of the total amount due, rounded up to the nearest $5
• Amount of Payment #2: 20% of the remaining amount due, rounded up to the nearest dollar
• Amount of Payment #3: Balance Due

Here, for fellow geeks, is the code I used, where $total is the total fee:

function ea_calc_installments($total) {
	$pmt_1 = round($total * PAYMENT_PERCENT_1, 0);
	while ( fmod($pmt_1, 5) > 0 ) {
		$pmt_1 += 1;
	}
	$balance_after_deposit = $total - $pmt_1;
	$pmt_2 = round($total * PAYMENT_PERCENT_2, 0);
	$pmt_3 = $total - $pmt_1 - $pmt_2;
	$retval = array(
		'pmt_1' => $pmt_1,
		'pmt_2' => $pmt_2,
		'pmt_3' => $pmt_3,
		'balance_after_deposit' => $balance_after_deposit,
	);
	return $retval;
}

Installment Payment Due Dates

The business rules for payment due dates is as follows:

• The first installment payment is due when the signed contract is returned.
• For contracts whose issuance date is between the first and fifteenth day of the month, the second installment payment is due on the first day of the following month, and the third and final installment payment is due on the first day of the second following month.
• For contracts whose issuance date is between the sixteenth day of the month and the end of the month, the second installment payment is due on the first day of the second following month, and the third and final installment payment is due on the first day of the third following month.

Here, for fellow geeks, is the code I used, where $ci_date is the contract issuance date passed from the form:

function ea_compute_due_dates($ci_date) {
	// First, convert the string to timestamp and to Y-m-d
	$ref_ts = strtotime($ci_date);
	$ref_ymd = date('Y-m-d', $ref_ts);
	// Set format:
	$format = 'F j, Y';
	if ( date('j', $ref_ts) <= '15' ) {
		$temp_2 = strtotime("first day of next month", $ref_ts);
		$pmt_2_date = date($format, strtotime("+14 day", $temp_2));
		
		$temp_3 = strtotime("first day of second month", $ref_ts);
		$pmt_3_date = date($format, strtotime("+14 day", $temp_3));
	} else {
		$pmt_2_date = date($format, strtotime("first day of second month", $ref_ts));
		$pmt_3_date = date($format, strtotime("first day of third month", $ref_ts));
	}
	$elements = array('pmt_2_date', 'pmt_3_date');
	foreach ( $elements as $element ) {
		$retval[$element] = $$element;
	}
	return $retval;
}

Summary of Benefits

The primary benefits of the new system are:

• substantially improved efficiency
• substantial reduction of errors
• the potential for moving responsibility for generating ISP contracts to other staff members

And in the words of our client:

Jeff’s contract generator works beautifully and saves me a great deal of time. His insight and attention to the needs of Eaton Academy made the creation of the program a breeze — for me! Jeff knew the questions to ask so that we would get exactly the system we wanted.

Brian L. Uitvlugt, Vice President CEO, Eaton Academy

In Closing

Forms are the engine that drive user interactivity on the Web. We see them everywhere — they are the meat of eCommerce Web sites and online surveys, and simple versions of forms are common on “Contact Us” pages.

Some forms are easy to make, using basic HTML, online form builders, or plugins for platforms like WordPress. But there are other applications for Web forms — including some applications for which you might not initially think a form is the answer — that require a custom solution with custom programming.

If you have a requirement for which a custom Web form might be the solution, please contact us for a free consultation. We’d love to help you.