function display()
{
extract($_REQUEST);
$fields = array();
$fields["from_year"] = date("Y");
$fields["from_month"] = date("m");
$fields["from_day"] = "01";
$fields["to_year"] = date("Y");
$fields["to_month"] = date("m");
$fields["to_day"] = date("d");
$fields["inc_perc"] = 0;
$fields["dec_perc"] = 0;
extract($fields, EXTR_SKIP);
$from_date = "{$from_year}-{$from_month}-{$from_day}";
$to_date = "{$to_year}-{$to_month}-{$to_day}";
$OUTPUT = "<center>\n\t<h3>Point in Time Sales Forecast</h3>\n\t<form method='post' action='" . SELF . "'>\n\t<table " . TMPL_tblDflts . ">\n\t\t<tr>\n\t\t\t<th colspan='3'>Date Range</th>\n\t\t</tr>\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td>" . mkDateSelect("from", $from_year, $from_month, $from_day) . "</td>\n\t\t\t<td> <b>To</b> </td>\n\t\t\t<td>" . mkDateSelect("to", $to_year, $to_month, $to_day) . "</td>\n\t\t</tr>\n\t\t<tr>\n\t\t\t<th>Increase</th>\n\t\t\t<th> </th>\n\t\t\t<th>Decrease</th>\n\t\t</tr>\n\t\t<tr class='" . bg_class() . "'>\n\t\t\t<td align='center'>\n\t\t\t\t<span style='font-weight: bold'>+</span>\n\t\t\t\t<input type='text' name='inc_perc' value='{$inc_perc}' size='4'\n\t\t\t\tstyle='text-align: center' />\n\t\t\t\t<span style='font-weight: bold'>%</span>\n\t\t\t</td>\n\t\t\t<td> </td>\n\t\t\t<td align='center'>\n\t\t\t\t<span style='font-weight: bold'>-</span>\n\t\t\t\t<input type='text' name='dec_perc' value='{$dec_perc}' size='4'\n\t\t\t\tstyle='text-align: center' />\n\t\t\t\t<span style='font-weight: bold'>%</span>\n\t\t\t</td>\n\t\t</tr>\n\t\t<tr>\n\t\t\t<td colspan='3' align='center'>\n\t\t\t\t<input type='submit' value='Apply' />\n\t\t\t</td>\n\t\t</tr>\n\t</table>\n\t</form>";
$sql = "SELECT whid, whname FROM exten.warehouses ORDER BY whname ASC";
$wh_rslt = db_exec($sql) or errDie("Unable to retrieve stores.");
$stores_th_lv1 = $stores_th_lv2 = "";
while ($wh_data = pg_fetch_array($wh_rslt)) {
$stores_th_lv1 .= "<th colspan='2'>{$wh_data['whname']}</th>";
$stores_th_lv2 .= "<th>Actual</th><th>Average<br>per Week</th>";
}
// Retrieve unique stock
$sql = "SELECT DISTINCT(stkcod) FROM cubit.stock ORDER BY stkcod ASC";
$stkcod_rslt = db_exec($sql) or errDie("Unable to retrieve stock codes.");
$stock_out = "";
while ($stkcod = pg_fetch_array($stkcod_rslt)) {
$stkcod = $stkcod["stkcod"];
$sql = "SELECT stkdes FROM cubit.stock\n\t\t\t\tWHERE stkcod='{$stkcod}'";
$stkdes_rslt = db_exec($sql) or errDie("Unable to retrieve stock.");
$stkdes = pg_fetch_result($stkdes_rslt, 0);
$stock_out .= "<tr class='" . bg_class() . "'>\n\t\t\t<td>{$stkcod}</td>\n\t\t\t<td>{$stkdes}</td>\n\t\t\t<td>\n\t\t\t\t" . totalActual($stkcod, $from_date, $to_date) . "\n\t\t\t</td>\n\t\t\t<td>\n\t\t\t\t<!--" . totalProjected($stkcod, $inc_perc, $dec_perc, $from_date, $to_date) . "-->\n\t\t\t\t" . totalWeekAverages($stkcod, $from_date, $to_date) . "\n\t\t\t</td>";
pg_result_seek($wh_rslt, 0);
while ($wh_data = pg_fetch_array($wh_rslt)) {
$sql = "SELECT stkid, units FROM cubit.stock\n\t\t\t\t\tWHERE stkcod='{$stkcod}' AND whid='{$wh_data['whid']}'";
$stock_rslt = db_exec($sql) or errDie("Unable to retrieve stock.");
$stock_data = pg_fetch_array($stock_rslt);
$stkid = $stock_data["stkid"];
// Don't go beyond this point unless the stock exists in this store
if (empty($stkid)) {
$stock_out .= "<td>0.00</td><td>0.00</td>";
continue;
}
// Total sales for the selected period
$actual_sales = actualSales($stkid, $from_date, $to_date);
$projected_sales = projectedSales($stkid, $inc_perc, $dec_perc, $from_date, $to_date);
$stock_out .= "\n\t\t\t\t<input type='hidden' name='stkid[]' value='{$stkid}' />\n\t\t\t\t<td>\n\t\t\t\t\t<input type='hidden' name='actual_sales[{$stkid}]'\n\t\t\t\t\tvalue='{$actual_sales}' />\n\t\t\t\t\t{$actual_sales}\n\t\t\t\t</td>\n\t\t\t\t<td>\n\t\t\t\t\t<input type='hidden' name='projected_sales[{$stkid}]'\n\t\t\t\t\tvalue='{$projected_sales}' />\n\t\t\t\t\t<!--{$projected_sales}-->\n\t\t\t\t\t" . weekAverages($stkid, $from_date, $to_date) . "\n\t\t\t\t</td>";
}
}
$OUTPUT .= "\n\t<form method='post' action='" . SELF . "'>\n\t<table " . TMPL_tblDflts . ">\n\t\t<tr>\n\t\t\t<th rowspan='2'>Stock Code</th>\n\t\t\t<th rowspan='2'>Stock Item</th>\n\t\t\t<th colspan='2'>Total</th>\n\t\t\t{$stores_th_lv1}\n\t\t</tr>\n\t\t<tr>\n\t\t\t<th>Actual</th>\n\t\t\t<th>Average<br>per Week</th>\n\t\t\t{$stores_th_lv2}\n\t\t</tr>\n\t\t{$stock_out}\n\t\t<tr>\n\t</table>\n\t</form>";
return $OUTPUT;
}
function compare()
{
extract($_REQUEST);
if ($prd == "week") {
$dates = lastWeekDates();
} else {
$dates = lastMonthDates();
}
$start_date = $dates["start_date"];
$end_date = $dates["end_date"];
// Store headings
$sql = "SELECT whid, whname FROM exten.warehouses ORDER BY whname ASC";
$wh_rslt = db_exec($sql) or errDie("Unable to retrieve stores.");
$stores_th_lv1 = $stores_th_lv2 = "";
while ($wh_data = pg_fetch_array($wh_rslt)) {
$stores_th_lv1 .= "<th colspan='2'>{$wh_data['whname']}</th>";
$stores_th_lv2 .= "<th>Actual</th><th>Projected</th>";
}
// Retrieve unique stock
$sql = "SELECT DISTINCT(stkcod) FROM cubit.stock ORDER BY stkcod ASC";
$stkcod_rslt = db_exec($sql) or errDie("Unable to retrieve stock codes.");
$stock_out = "";
while ($stkcod = pg_fetch_array($stkcod_rslt)) {
$stkcod = $stkcod["stkcod"];
$sql = "SELECT stkdes FROM cubit.stock\n\t\t\t\tWHERE stkcod='{$stkcod}'";
$stkdes_rslt = db_exec($sql) or errDie("Unable to retrieve stock.");
$stkdes = pg_fetch_result($stkdes_rslt, 0);
$stock_out .= "<tr class='" . bg_class() . "'>\n\t\t\t<td>{$stkcod}</td>\n\t\t\t<td>{$stkdes}</td>\n\t\t\t<td>\n\t\t\t\t" . totalActual($stkcod, $start_date, $end_date, $forecast_id) . "\n\t\t\t</td>\n\t\t\t<td>\n\t\t\t\t" . totalProjected($stkcod, $inc_perc, $dec_perc, $start_date, $end_date, $forecast_id) . "\n\t\t\t</td>";
pg_result_seek($wh_rslt, 0);
while ($wh_data = pg_fetch_array($wh_rslt)) {
$sql = "SELECT stkid, units FROM cubit.stock\n\t\t\t\t\tWHERE stkcod='{$stkcod}' AND whid='{$wh_data['whid']}'";
$stock_rslt = db_exec($sql) or errDie("Unable to retrieve stock.");
$stock_data = pg_fetch_array($stock_rslt);
$stkid = $stock_data["stkid"];
// Don't go beyond this point unless the stock exists in this store
if (empty($stkid)) {
$stock_out .= "<td>0.00</td><td>0.00</td>";
continue;
}
$sql = "SELECT actual, projected FROM cubit.forecast_items\n\t\t\t\t\tWHERE stkid='{$stkid}' AND forecast_id='{$forecast_id}'";
$fci_rslt = db_exec($sql) or errDie("Unable to retrieve items.");
$fci_data = pg_fetch_array($fci_rslt);
// Total sales for the selected period
$current_actual = actualSales($stkid, $start_date, $end_date, $forecast_id);
$current_projected = projectedSales($stkid, $inc_perc, $dec_perc, $start_date, $end_date, $forecast_id);
if (empty($current_actual)) {
$current_actual = "0.00";
}
if (empty($current_projected)) {
$current_projected = "0.00";
}
if (empty($fci_data["actual"])) {
$fci_data["actual"] = "0.00";
}
if (empty($fci_data["projected"])) {
$fci_data["projected"] = "0.00";
}
$actual_sales = sprint($current_actual - $fci_data["actual"]);
$projected_sales = sprint($current_projected - $fci_data["projected"]);
if ($actual_sales > 0) {
$actual_color = "green";
} elseif ($actual_sales < 0) {
$actual_color = "red";
} else {
$actual_color = "black";
}
if ($projected_sales > 0) {
$projected_color = "green";
} elseif ($projected_sales < 0) {
$projected_color = "red";
} else {
$projected_color = "black";
}
$stock_out .= "\n\t\t\t\t<input type='hidden' name='stkid[]' value='{$stkid}' />\n\t\t\t\t<td>\n\t\t\t\t\t<input type='hidden' name='actual_sales[{$stkid}]'\n\t\t\t\t\tvalue='{$actual_sales}' />\n\t\t\t\t\t<span style='color: {$actual_color}'>{$actual_sales}</span>\n\t\t\t\t</td>\n\t\t\t\t<td>\n\t\t\t\t\t<input type='hidden' name='projected_sales[{$stkid}]'\n\t\t\t\t\tvalue='{$projected_sales}' />\n\t\t\t\t\t<span style='color: {$projected_color}'>{$projected_sales}</span>\n\t\t\t\t</td>";
}
}
$OUTPUT = "\n\t<center>\n\t<h3>Compare Sales Forecast</h3>\n\t<table " . TMPL_tblDflts . ">\n\t\t<tr>\n\t\t\t<th rowspan='2'>Stock Code</th>\n\t\t\t<th rowspan='2'>Stock Item</th>\n\t\t\t<th colspan='2'>Total</th>\n\t\t\t{$stores_th_lv1}\n\t\t</tr>\n\t\t<tr>\n\t\t\t<th>Actual</th>\n\t\t\t<th>Projected</th>\n\t\t\t{$stores_th_lv2}\n\t\t</tr>\n\t\t{$stock_out}\n\t\t<tr>\n\t</table>\n\t</center>";
return $OUTPUT;
}
function totalWeekAverages($stkcod, $from_date, $to_date)
{
$amount = totalActual($stkcod, $from_date, $to_date);
$weeks = weekCount($from_date, $to_date);
if ($amount && $weeks > 1) {
$average = $amount / $weeks;
} else {
$average = $amount;
}
return sprint($average);
}
function display()
{
extract($_REQUEST);
$fields = array();
$fields["prd"] = "monthly";
$fields["prd_val"] = date("m");
$fields["inc_perc"] = "0";
$fields["dec_perc"] = "0";
$fields["forecast_id"] = "0";
extract($fields, EXTR_SKIP);
// Report settings --------------------------------------------------------
$sql = "SELECT *, extract('epoch' FROM timestamp) AS e_time FROM cubit.forecasts ORDER BY timestamp DESC";
$fc_rslt = db_exec($sql) or errDie("Unable to retrieve forecast.");
$forecast_sel = "<select name='forecast_id'\n\t\t\t\t\t onchange='javascript:document.form.submit()'\n\t\t\t\t\t style='width: 100%'>\n\t\t\t\t\t\t<option value='0'>[None]</option>";
while ($fc_data = pg_fetch_array($fc_rslt)) {
if ($fc_data["id"] == $forecast_id) {
$sel = "selected='selected'";
} else {
$sel = "";
}
if ($fc_data["prd"] == "monthly") {
$fc_prd = "Month";
} else {
$fc_prd = "Week";
}
$forecast_sel .= "\n\t\t\t<option value='{$fc_data['id']}' {$sel}>\n\t\t\t\t[Created: " . date("d-m-Y G:i:s", $fc_data["e_time"]) . "]\n\t\t\t\t[{$fc_prd}: {$fc_data['prd_val']}]\n\t\t\t\t[Increase: {$fc_data['inc_perc']}%]\n\t\t\t\t[Decrease: {$fc_data['dec_perc']}%]\n\t\t\t</option>";
}
if (!$forecast_id) {
$forecast_id = "new";
// Months
if ($prd == "monthly") {
$prd_monthly = "checked='checked'";
$prd_weekly = "";
// Months dropdown
$prd_sel = "Month:\n\t\t\t<select name='prd_val' onchange='javascript:document.form.submit()'>";
for ($i = 1; $i <= 12; $i++) {
if ($prd_val == $i) {
$sel = "selected='selected'";
} else {
$sel = "";
}
$prd_sel .= "<option value='{$i}' {$sel}>{$i}</option>";
}
$prd_sel .= "</select>";
$dates = monthToDates($prd_val);
// Weeks
} else {
$prd_monthly = "";
$prd_weekly = "checked='checked'";
// Weeks dropdown
$prd_sel = "Week:\n\t\t\t<select name='prd_val' onchange='javascript:document.form.submit()'>";
for ($i = 1; $i <= date("W", mktime(0, 0, 0, 12, 31, date("Y"))); $i++) {
if ($prd_val == $i) {
$sel = "selected='selected'";
} else {
$sel = "";
}
$prd_sel .= "<option value='{$i}' {$sel}>{$i}</option>";
}
$prd_sel .= "</select>";
$dates = weekToDates($prd_val);
}
// $start_date = lastYear($dates["start"]);
// $end_date = lastYear($dates["end"]);
$start_date = $dates["start"];
$end_date = $dates["end"];
$OUTPUT = "<center>\n\t\t<h3>Sales Forecast</h3>\n\t\t<form method='post' action='" . SELF . "' name='form'>\n\t\t<table " . TMPL_tblDflts . ">\n\t\t\t<tr>\n\t\t\t\t<th colspan='7'>Report Settings</th>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<!-- COL 1 -->\n\t\t\t\t<td>\n\t\t\t\t\t<input type='radio' name='prd' value='weekly' {$prd_weekly}\n\t\t\t\t\tonchange='javascript:document.form.submit()' />\n\t\t\t\t</td>\n\t\t\t\t<td>Weekly</td>\n\n\t\t\t\t<td>\n\t\t\t\t\t<input type='radio' name='prd' value='monthly' {$prd_monthly}\n\t\t\t\t\tonchange='javascript:document.form.submit()' />\n\t\t\t\t</td>\n\t\t\t\t<td>Monthly</td>\n\n\t\t\t\t<!-- COL 2 -->\n\t\t\t\t<td class='" . bg_class() . "'> </td>\n\n\t\t\t\t<!-- COL 3 -->\n\t\t\t\t<th>Increase</th>\n\t\t\t\t<th>Decrease</th>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<!-- COL 1 -->\n\t\t\t\t<td colspan='4' align='center'>{$prd_sel}</td>\n\n\t\t\t\t<!-- COL 2 -->\n\t\t\t\t<td class='" . bg_class() . "'> </td>\n\n\t\t\t\t<!-- COL 3 -->\n\t\t\t\t<td>\n\t\t\t\t\t<span style='font-weight: bold'>+</span>\n\t\t\t\t\t<input type='text' name='inc_perc' value='{$inc_perc}' size='4'\n\t\t\t\t\tstyle='text-align: center' />\n\t\t\t\t\t<span style='font-weight: bold'>%</span>\n\t\t\t\t</td>\n\t\t\t\t<td>\n\t\t\t\t\t<span style='font-weight: bold'>-</span>\n\t\t\t\t\t<input type='text' name='dec_perc' value='{$dec_perc}' size='4'\n\t\t\t\t\tstyle='text-align: center' />\n\t\t\t\t\t<span style='font-weight: bold'>%</span>\n\t\t\t\t</td>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<!-- COL 1 -->\n\t\t\t\t<td colspan='4'>{$start_date} <b> To </b> {$end_date}</td>\n\n\t\t\t\t<!-- COL 2 -->\n\t\t\t\t<td class='" . bg_class() . "'> </td>\n\n\t\t\t\t<!-- COL 3 -->\n\t\t\t\t<td colspan='2' align='center'>\n\t\t\t\t\t<input type='submit' value='Apply »'\n\t\t\t\t\tstyle='width: 100%' />\n\t\t\t\t</td>\n\t\t\t</tr>\n\t\t\t<tr><th colspan='7'>Saved Sales Forecast</th></tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td colspan='7'>\n\t\t\t\t\tIf a saved sales forecast has been chosen, all other<br />\n\t\t\t\t\tsettings for this report will be ignored.\n\t\t\t\t</td>\n\t\t\t</tr>\n\t\t\t<tr class='" . bg_class() . "'>\n\t\t\t\t<td colspan='7'>{$forecast_sel}</td>\n\t\t\t</tr>\n\t\t</table>\n\t\t</form>";
$save_btn = "<input type='submit' value='Save' />";
} else {
$start_date = NULL;
$end_date = NULL;
$OUTPUT = "<center>";
$save_btn = "";
}
$sql = "SELECT whid, whname FROM exten.warehouses ORDER BY whname ASC";
$wh_rslt = db_exec($sql) or errDie("Unable to retrieve stores.");
$stores_th_lv1 = $stores_th_lv2 = "";
while ($wh_data = pg_fetch_array($wh_rslt)) {
$stores_th_lv1 .= "<th colspan='2'>{$wh_data['whname']}</th>";
$stores_th_lv2 .= "<th>Actual</th><th>Projected</th>";
}
// Retrieve unique stock
$sql = "SELECT DISTINCT(stkcod) FROM cubit.stock ORDER BY stkcod ASC";
$stkcod_rslt = db_exec($sql) or errDie("Unable to retrieve stock codes.");
$stock_out = "";
while ($stkcod = pg_fetch_array($stkcod_rslt)) {
$stkcod = $stkcod["stkcod"];
$sql = "SELECT stkdes FROM cubit.stock\n\t\t\t\tWHERE stkcod='{$stkcod}'";
$stkdes_rslt = db_exec($sql) or errDie("Unable to retrieve stock.");
$stkdes = pg_fetch_result($stkdes_rslt, 0);
$stock_out .= "<tr class='" . bg_class() . "'>\n\t\t\t<td>{$stkcod}</td>\n\t\t\t<td>{$stkdes}</td>\n\t\t\t<td>\n\t\t\t\t" . totalActual($stkcod, $start_date, $end_date, $forecast_id) . "\n\t\t\t</td>\n\t\t\t<td>\n\t\t\t\t" . totalProjected($stkcod, $inc_perc, $dec_perc, $start_date, $end_date, $forecast_id) . "\n\t\t\t</td>";
pg_result_seek($wh_rslt, 0);
while ($wh_data = pg_fetch_array($wh_rslt)) {
$sql = "SELECT stkid, units FROM cubit.stock\n\t\t\t\t\tWHERE stkcod='{$stkcod}' AND whid='{$wh_data['whid']}'";
$stock_rslt = db_exec($sql) or errDie("Unable to retrieve stock.");
$stock_data = pg_fetch_array($stock_rslt);
$stkid = $stock_data["stkid"];
// Don't go beyond this point unless the stock exists in this store
if (empty($stkid)) {
$stock_out .= "<td>0.00</td><td>0.00</td>";
continue;
}
// Total sales for the selected period
$actual_sales = actualSales($stkid, $start_date, $end_date, $forecast_id);
$projected_sales = projectedSales($stkid, $inc_perc, $dec_perc, $start_date, $end_date, $forecast_id);
$stock_out .= "\n\t\t\t\t<input type='hidden' name='stkid[]' value='{$stkid}' />\n\t\t\t\t<td>\n\t\t\t\t\t<input type='hidden' name='actual_sales[{$stkid}]'\n\t\t\t\t\tvalue='{$actual_sales}' />\n\t\t\t\t\t{$actual_sales}\n\t\t\t\t</td>\n\t\t\t\t<td>\n\t\t\t\t\t<input type='hidden' name='projected_sales[{$stkid}]'\n\t\t\t\t\tvalue='{$projected_sales}' />\n\t\t\t\t\t{$projected_sales}\n\t\t\t\t</td>";
}
}
$OUTPUT .= "\n\t<form method='post' action='" . SELF . "'>\n\t<input type='hidden' name='key' value='save' />\n\t<input type='hidden' name='prd' value='{$prd}' />\n\t<input type='hidden' name='prd_val' value='{$prd_val}' />\n\t<input type='hidden' name='inc_perc' value='{$inc_perc}' />\n\t<input type='hidden' name='dec_perc' value='{$dec_perc}' />\n\t{$save_btn}\n\t<table " . TMPL_tblDflts . ">\n\t\t<tr>\n\t\t\t<th rowspan='2'>Stock Code</th>\n\t\t\t<th rowspan='2'>Stock Item</th>\n\t\t\t<th colspan='2'>Total</th>\n\t\t\t{$stores_th_lv1}\n\t\t</tr>\n\t\t<tr>\n\t\t\t<th>Actual</th>\n\t\t\t<th>Projected</th>\n\t\t\t{$stores_th_lv2}\n\t\t</tr>\n\t\t{$stock_out}\n\t\t<tr>\n\t</table>\n\t{$save_btn}\n\t</form>";
return $OUTPUT;
}
