------------------------------ months.cfi ------------------------------
<!--- create an array of the months --->
<cfset months = ListToArray("January,February,March,April,May,June,July,August,September,October,November,December")>
<!--- generate a form element to select a month --->
<cffunction name="selectmonth" output="yes">
<cfargument name="name" type="string" default="month">
<select name="#name#">
<cfloop index="month" from="1" to="12">
<option value="#month#">#months[month]#</option>
</cfloop>
</select>
</cffunction>
------------------------------ hotels.cfm ------------------------------
<!--- generate a form to select a hotel and month for predictions --->
<!--- retrieve hotel information --->
<cfquery name="hotels" datasource="cfhotel1">
SELECT id, name, city, state FROM hotels ORDER BY state, city, name
</cfquery>
<!--- include month helper code --->
<cfinclude template="months.cfi">
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Hotels</title>
</head>
<body>
<form action="prediction.cfm">
Predictions for:<br>
Hotel: <select name="hotel">
<!--- generated a nested/submenu selection, with state > city > hotel --->
<cfoutput query="hotels" group="state">
<optgroup label="#hotels.state#">
<cfoutput group="city">
<optgroup label="#hotels.city#">
<cfoutput>
<option value="#hotels.id#">#hotels.name#</option>
</cfoutput>
</optgroup>
</cfoutput>
</optgroup>
</cfoutput>
</select><br>
<!--- form element to select month --->
<cfoutput>
Month: #selectmonth()#<br>
</cfoutput>
<input type="submit" value="Go">
</form>
</body>
</html>
------------------------------ prediction.cfm ------------------------------
<!--- for the specified hotel and month, show the last 5 years of data, and
generate simple statistics (average and standard deviation) and predict
values for the upcoming year based on a linear regression of past data.
--->
<!--- get the hotel data --->
<cfquery name="hotel_info" datasource="cfhotel1">
SELECT name, city, state, rooms FROM hotels WHERE id = #hotel#
</cfquery>
<!--- get the name of the selected month --->
<cfinclude template="months.cfi">
<cfset monthname = months[month]>
<!--- get the last 5 years of historic data for this hotel and month --->
<cfquery name="history" datasource="cfhotel1">
SELECT TOP 5 year, avg_rate, avg_occupancy, avg_stay
FROM hotel_history WHERE hotel_id = #hotel# AND month = #month# ORDER BY year ASC
</cfquery>
<!--- we need to store the history data in a 2D array to pass to the
linear regression function, and find the year we are predicting
(the year after the most recent entry in the history data)
--->
<cfset prediction_year = 0>
<cfset history_data = ArrayNew(2)>
<cfloop query="history">
<cfscript>
// first entry should be the independent (x) variable; the year in this case.
history_data[CurrentRow][1] = history.year;
// is it the most recent year we've seen yet?
if (history.year GT prediction_year) prediction_year = history.year;
history_data[CurrentRow][2] = history.avg_rate;
history_data[CurrentRow][3] = history.avg_occupancy;
history_data[CurrentRow][4] = history.avg_stay;
</cfscript>
</cfloop>
<!--- we want the year after the latest in the history --->
<cfset prediction_year = prediction_year + 1>
<!--- perform the linear regression on our data, using the statistics component --->
<cfinvoke component="statistics" method="linear_regression" returnVariable="statistics">
<cfinvokeargument name="history_data" value="#history_data#">
<cfinvokeargument name="predictX" value="#prediction_year#">
</cfinvoke>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<cfoutput query="hotel_info" maxRows="1">
<title>#monthname# #prediction_year# predictions for #hotel_info.name#</title>
</cfoutput>
<style>
table { border-collapse: collapse; }
td, th { border: solid 1px; padding: .5em; }
td { text-align: right }
td.label { text-align: center; }
</style>
</head>
<body>
<!--- hotel information --->
<cfoutput query="hotel_info" maxRows="1">
<h1>#hotel_info.name#</h1>
<h2>#hotel_info.city#, #hotel_info.state# (#hotel_info.rooms# rooms)</h2>
<h3>Statistics for #monthname#</h3>
</cfoutput>
<table>
<tr>
<th>Year</th>
<th>Avg. Rate</th>
<th>Avg. Occupancy</th>
<th>Avg. Stay</th>
<th>Est. Revenue</th>
</tr>
<!--- display the historic data for the month --->
<cfoutput query="history">
<tr>
<td class="label">#history.year#</td>
<td>#DollarFormat(history.avg_rate)#</td>
<td>#DecimalFormat(history.avg_occupancy*100)#%</td>
<td>#DecimalFormat(history.avg_stay)#</td>
<!--- estimated revenue is average rate * average occupancy * number of rooms * days in month --->
<td>#DollarFormat(history.avg_rate * history.avg_occupancy * hotel_info.rooms *
DaysInMonth(CreateDate(history.year, month, 1)))#</td>
</tr>
</cfoutput>
<cfoutput>
<!--- show averages for historic data --->
<tr>
<td class="label">Past average</td>
<td>#DollarFormat(statistics[2][1])#</td>
<td>#DecimalFormat(statistics[3][1]*100)#%</td>
<td>#DecimalFormat(statistics[4][1])#</td>
<!--- estimated revenue is average rate * average occupancy * number of rooms * days in month --->
<td>#DollarFormat(statistics[2][1] * statistics[3][1] * hotel_info.rooms *
DaysInMonth(CreateDate(prediction_year, month, 1)))#</td>
</tr>
<!--- standard deviations for averages --->
<tr>
<td></td>
<td>&##177; #DollarFormat(Sqr(statistics[2][2]))#</td>
<td>&##177; #DecimalFormat(Sqr(statistics[3][2])*100)#%</td>
<td>&##177; #DecimalFormat(Sqr(statistics[4][2]))#</td>
<td></td>
</tr>
<!--- predicted values for year based on linear regression --->
<tr>
<td class="label">#prediction_year# prediction</td>
<td>#DollarFormat(statistics[2][6])#</td>
<!--- Cap predicted occupancy at 100% --->
<td>#DecimalFormat(Min(1.0, statistics[3][6])*100)#%</td>
<td>#DecimalFormat(statistics[4][6])#</td>
<!--- estimated revenue is average rate * average occupancy * number of rooms * days in month --->
<td>#DollarFormat(statistics[2][6] * statistics[3][6] * hotel_info.rooms *
DaysInMonth(CreateDate(prediction_year, month, 1)))#</td>
</tr>
<!--- R-squared confidence values for predictions --->
<tr>
<td class="label">Confidence (R<sup>2</sup>)</td>
<td>#DecimalFormat(statistics[2][3] * statistics[2][3])#</td>
<td>#DecimalFormat(statistics[3][3] * statistics[3][3])#</td>
<td>#DecimalFormat(statistics[4][3] * statistics[4][3])#</td>
<td></td>
</tr>
</cfoutput>
</table>
</body>
</html>
------------------------------ statistics.cfc ------------------------------
<!--- a component providing statistical analysis functions --->
<cfcomponent output="no" displayname="Statistical Functions">
<!--- perform a linear regression on an 2D array of (x, y1, y2, ...) data sets --->
<cffunction name="linear_regression" output="no" returnType="array" displayname="Linear Regression">
<cfargument name="history_data" type="array" required="yes" displayname="Historic data as 2D array">
<cfargument name="predictX" type="numeric" default="0" displayname="Optional X value to make predictions">
<cfscript>
// declare local variables
var N = ArrayLen(history_data); // number of datapoints
var terms = ArrayLen(history_data[1]); // terms in data (x, y1, y2, ...)
var sums = ArrayNew(1); // array to accumulate term sums
var sums_sqrd = ArrayNew(1); // sum of terms squared
var sums_cross = ArrayNew(1); // sum of x * term
var statistics = ArrayNew(2); // output statistics
// variables used during calculations
var avgX = 0;
var avgY = 0;
var varX = 0;
var varY = 0;
var covxy = 0;
var r = 0;
var m = 0;
var b = 0;
var prediction = 0;
// loop index variables
var i = 0;
var j = 0;
// do we have any data to process?
if (N EQ 0) return statistics;
if (terms LT 2) return statistics;
// zero sum counters
for (j = 1; j LTE terms; j = j + 1) {
sums[j] = 0;
sums_sqrd[j] = 0;
sums_cross[j] = 0;
}
// loop through each datapoint and term, accumulating sums
for (i = 1; i LTE N; i = i + 1) {
for (j = 1; j LTE terms; j = j + 1) {
// accumulate sums
sums[j] = sums[j] + history_data[i][j];
sums_sqrd[j] = sums_sqrd[j] + (history_data[i][j] * history_data[i][j]);
sums_cross[j] = sums_cross[j] + (history_data[i][1] * history_data[i][j]);
}
}
// calculate avg and variance for x term
avgX = sums[1] / N;
varX = (sums_sqrd[1] / N) - (avgX * avgX);
// store in the output array
statistics[1][1] = avgX; // average
statistics[1][2] = varX; // variance
// process y terms
for (j = 2; j LTE terms; j = j + 1) {
// calculate avg and variance for this term
avgY = sums[j] / N;
varY = (sums_sqrd[j] / N) - (avgY * avgY);
// calculate covariance...
covxy = (sums_cross[j] / N) - (avgX * avgY);
// correlation coefficient...
r = covxy / Sqr(varX * varY);
// and the regression line
m = covxy / varX;
b = avgY - m * avgX;
// make our prediction
prediction = m * predictX + b;
// store in the output array
statistics[j][1] = avgY; // average
statistics[j][2] = varY; // variance
statistics[j][3] = r; // correlation coefficient
statistics[j][4] = m; // regression line slope
statistics[j][5] = b; // regression line intercept
statistics[j][6] = prediction; // predicted value
}
return statistics;
</cfscript>
</cffunction>
</cfcomponent>
