* @Author : Erick Wellem (me@erickwellem.com)
* October 2009
* This version: February 2013
*
* @Desc: Process file using Ajax
**************************************************************************************************/
include '../config.php';
require_once '../lib/db.php';
$db = new DB();
if ($_REQUEST['activity_id'] && $_REQUEST['year'] && $_REQUEST['month']) {
$db->dbConnect();
$query = "SELECT * FROM `mbs_stores` ORDER BY `store_name`";
$result = mysql_query($query);
$data = array();
while ($row = mysql_fetch_assoc($result)) {
$arrCheckResult = $db->checkActivityInStoreByDateTime(intval($_REQUEST['activity_id']), $row['store_id'], intval($_REQUEST['year']), intval($_REQUEST['month']));
if (count($arrCheckResult) > 1) {
$data[] = $arrCheckResult;
}
}
#print_r($data);
#echo $data[0]['store_id'];
#echo count($data);
if ($data[0]['store_id']) {
//-- Get other stores
$queryStore = "SELECT * FROM `mbs_stores` WHERE `store_id` NOT IN (" . $data[0]['store_id'] . ") ORDER BY `store_name`";
$resultStore = mysql_query($queryStore);
if ($resultStore) {
$dataStore = array();
while ($rowStore = mysql_fetch_assoc($resultStore)) {
$dataStore[$rowStore['store_id']] = array('store_name' => $rowStore['store_name']);
function showReportGeneralMonthly($intMonth, $intYear)
{
global $STR_URL;
// vars
$intMinBookingTime = 3600 * 24 * 7;
// 3600 seconds * 24 hours * 14 days
DB::dbConnect();
?>
<script type="text/javascript" src="<?php
echo $STR_URL;
?>
js/jqplot/jquery.jqplot.min.js"></script>
<script type="text/javascript" src="<?php
echo $STR_URL;
?>
js/jqplot/plugins/jqplot.pieRenderer.min.js"></script>
<div align="center" style="margin-top:20px;">
<form id="frm_report_date" method="get" action="<?php
echo $_SERVER['PHP_SELF'];
?>
">
<select id="month" name="month" class="input-medium">
<?php
for ($i = 1; $i <= 12; $i++) {
?>
<option value="<?php
echo $i;
?>
"<?php
if ($_REQUEST['month'] && $_REQUEST['month'] == $i) {
?>
selected="selected"<?php
} elseif (!$_REQUEST['month'] && date('n') == $i) {
?>
selected="selected"<?php
}
?>
><?php
echo HTML::getMonthName($i);
?>
</option>
<?php
}
?>
</select>
<select id="year" name="year" class="input-small">
<?php
for ($i = date('Y') - 1; $i < date('Y') + 4; $i++) {
?>
<option value="<?php
echo $i;
?>
"<?php
if ($_REQUEST['year'] && $_REQUEST['year'] == $i) {
?>
selected="selected"<?php
} elseif (!$_REQUEST['year'] && date('Y') == $i) {
?>
selected="selected"<?php
}
?>
><?php
echo $i;
?>
</option>
<?php
}
?>
</select><br />
<input class="btn" type="submit" value="Submit" />
</form>
</div>
<div style="text-align:center;clear:both;"><h2>General Report (<?php
echo HTML::getMonthName($intMonth) . " " . $intYear;
?>
)</h2></div>
<!-- In-Store -->
<div style="clear:both;"><h3 style="font-size:1.3em;">In-Store</h3></div>
<table class="table table-bordered table-hover">
<thead class="well">
<tr>
<th style="text-align:center;width:20%;"><strong>Store</strong></th>
<?php
// Get the In-Store activities
$queryInStore = "SELECT * FROM `mbs_activities` WHERE `activity_category` = 'in-store' AND `activity_store_related` = 'yes' ORDER BY `activity_name`";
$resultInStore = mysql_query($queryInStore);
$intCountIntStore = mysql_num_rows($resultInStore);
$arrDataInStoreAct = array();
while ($rowInStore = mysql_fetch_assoc($resultInStore)) {
$arrDataInStoreAct[] = $rowInStore;
?>
<th style="text-align:center;"><strong><?php
echo htmlspecialchars($rowInStore['activity_name']);
?>
</strong></th>
<?php
}
?>
</tr>
</thead>
<tbody>
<?php
$queryStore = "SELECT * FROM `mbs_stores` ORDER BY `store_name`";
$resultStore = mysql_query($queryStore);
while ($rowStore = mysql_fetch_assoc($resultStore)) {
?>
<tr>
<td><div style="text-align:left;"><?php
echo htmlspecialchars($rowStore['store_name']);
?>
</div></td>
<?php
for ($i = 0; $i < $intCountIntStore; $i++) {
$arrInStoreResult = DB::checkActivityInStoreByDateTime($arrDataInStoreAct[$i]['activity_id'], $rowStore['store_id'], $intYear, $intMonth);
$strBookingCode = DB::dbIDToField('mbs_bookings', 'booking_id', $arrInStoreResult['booking_id'], 'booking_code');
$strBookingName = DB::dbIDToField('mbs_bookings', 'booking_id', $arrInStoreResult['booking_id'], 'booking_name');
?>
<td style="background-color:<?php
if (count($arrInStoreResult) > 1) {
?>
#f2dede<?php
} elseif (mktime(0, 0, 0, $intMonth, 1, $intYear) < time() + $intMinBookingTime) {
?>
#ddd<?php
} else {
?>
#dff0d8<?php
}
?>
;">
<div style="text-align:center;font-size:0.8em;">
<?php
if (count($arrInStoreResult) > 1) {
?>
<a href="booking_view.php?booking_id=<?php
echo $arrInStoreResult['booking_id'];
?>
&action=view#id<?php
echo $arrInStoreResult['booking_activity_id'];
?>
" title="<?php
echo htmlspecialchars($strBookingCode);
?>
/ <?php
echo htmlspecialchars($strBookingName);
?>
"><em><?php
echo htmlspecialchars($strBookingCode);
?>
</em></a>
<?php
} elseif (mktime(0, 0, 0, $intMonth, 1, $intYear) < time() + $intMinBookingTime) {
?>
N/A
<?php
} else {
?>
<a href="booking.php?action=add&frm_activity_id=<?php
echo $arrDataInStoreAct[$i]['activity_id'];
?>
&frm_store_id=<?php
echo $rowStore['store_id'];
?>
" style="color:#468847;">Available</a>
<?php
}
?>
</div>
</td>
<?php
}
?>
</tr>
<?php
}
// while ($rowStore = mysql_fetch_assoc($resultStore))
?>
</tbody>
<tfoot>
<!--Sub Total-->
<tr>
<td><div style="text-align:right;"><strong>Sub-Total</strong></div></td>
<?php
for ($i = 0; $i < $intCountIntStore; $i++) {
$arrInStoreSubTotal = DB::getActivityInStoreTotalByDateTime($arrDataInStoreAct[$i]['activity_id'], $intYear, $intMonth);
$strInStoreSubTotal = $arrInStoreSubTotal['booking_activity_price_total'];
$strInStoreSubTotal = $strInStoreSubTotal;
$strInStoreTotal += $strInStoreSubTotal;
?>
<td><div style="text-align:right;">$<?php
echo htmlspecialchars(number_format($strInStoreSubTotal, 2));
?>
</div></td>
<?php
}
?>
</tr>
<!-- Total -->
<tr>
<td colspan="<?php
echo $intCountIntStore;
?>
"><div style="text-align:right;"><strong>Total</strong></div></td>
<td style="background-color:#ddd;"><div style="text-align:right;"><strong>$<?php
echo htmlspecialchars(number_format($strInStoreTotal, 2));
?>
</strong></div></td>
</tr>
</tfoot>
</table>
<!-- Catalogue -->
<div style="clear:both;"><h3 style="font-size:1.3em;">Catalogue</h3></div>
<table class="table table-bordered table-hover">
<thead class="well">
<tr>
<th style="text-align:center;width:20%;"><strong>Size/Spot</strong></th>
<th style="text-align:center;"><strong>Booking</strong></th>
<th style="text-align:center;width:20%;"><strong>Total</strong></th>
</tr>
</thead>
<tbody>
<?php
$queryCatalogue = "SELECT * FROM `mbs_activities` WHERE `activity_category` = 'catalogue' ORDER BY `activity_name`";
$resultCatalogue = mysql_query($queryCatalogue);
while ($rowCatalogue = mysql_fetch_assoc($resultCatalogue)) {
$arrCatalogue = DB::getActivityByDateTime($rowCatalogue['activity_id'], $intYear, $intMonth);
$strBookingCode = DB::dbIDToField('mbs_bookings', 'booking_id', $arrCatalogue['booking_id'], 'booking_code');
$strBookingName = DB::dbIDToField('mbs_bookings', 'booking_id', $arrCatalogue['booking_id'], 'booking_name');
$strCataloguePrice = $arrCatalogue['booking_activity_price_total'];
$strCataloguePriceTotal += $strCataloguePrice;
?>
<tr>
<td><div style="text-align:left;"><?php
echo htmlspecialchars($rowCatalogue['activity_name']);
?>
</div></td>
<td style="background-color:<?php
if (count($arrCatalogue) > 1) {
?>
#f2dede<?php
} elseif (mktime(0, 0, 0, $intMonth, 1, $intYear) < time() + $intMinBookingTime) {
?>
#ddd<?php
} else {
?>
#dff0d8<?php
}
?>
;">
<div style="text-align:center;">
<?php
if (count($arrCatalogue) > 1) {
?>
<a href="booking_view.php?booking_id=<?php
echo $arrCatalogue['booking_id'];
?>
&action=view#id<?php
echo $arrCatalogue['booking_activity_id'];
?>
" title="<?php
echo htmlspecialchars($strBookingCode);
?>
/ <?php
echo htmlspecialchars($strBookingName);
?>
"><em><?php
echo htmlspecialchars($strBookingCode);
?>
</em></a>
<?php
} elseif (mktime(0, 0, 0, $intMonth, 1, $intYear) < time() + $intMinBookingTime) {
?>
N/A
<?php
} else {
?>
<a href="booking.php?action=add&frm_activity_id=<?php
echo $rowCatalogue['activity_id'];
?>
" style="color:#468847;">Available</a>
<?php
}
?>
</div>
</td>
<td><div style="text-align:right;">$<?php
echo htmlspecialchars(number_format($strCataloguePrice));
?>
</div></td>
</tr>
<?php
}
?>
</tbody>
<tfoot>
<td colspan="2"><div style="text-align:right;"><strong>Total</strong></div></td>
<td style="background-color:#ddd;"><div style="text-align:right;"><strong>$<?php
echo htmlspecialchars(number_format($strCataloguePriceTotal, 2));
?>
</strong></div></td>
</tfoot>
</table>
<!-- Newspaper -->
<div style="clear:both;"><h3 style="font-size:1.3em;">Newspaper</h3></div>
<table class="table table-bordered table-hover">
<thead class="well">
<tr>
<th style="text-align:center;width:20%;"><strong>Size/Spot</strong></th>
<th style="text-align:center;"><strong>Booking</strong></th>
<th style="text-align:center;width:20%;"><strong>Total</strong></th>
</tr>
</thead>
<tbody>
<?php
$queryNewspaper = "SELECT * FROM `mbs_activities` WHERE `activity_category` = 'newspaper' ORDER BY `activity_name`";
$resultNewspaper = mysql_query($queryNewspaper);
while ($rowNewspaper = mysql_fetch_assoc($resultNewspaper)) {
$arrNewspaper = DB::getActivityByDateTime($rowNewspaper['activity_id'], $intYear, $intMonth);
$strBookingCode = DB::dbIDToField('mbs_bookings', 'booking_id', $arrNewspaper['booking_id'], 'booking_code');
$strBookingName = DB::dbIDToField('mbs_bookings', 'booking_id', $arrNewspaper['booking_id'], 'booking_name');
$strNewspaperPrice = $arrNewspaper['booking_activity_price_total'];
$strNewspaperPriceTotal += $strNewspaperPrice;
?>
<tr>
<td><div style="text-align:left;"><?php
echo htmlspecialchars($rowNewspaper['activity_name']);
?>
</div></td>
<td style="background-color:<?php
if (count($arrNewspaper) > 1) {
?>
#f2dede<?php
} elseif (mktime(0, 0, 0, $intMonth, 1, $intYear) < time() + $intMinBookingTime) {
?>
#ddd<?php
} else {
?>
#dff0d8<?php
}
?>
;">
<div style="text-align:center;">
<?php
if (count($arrNewspaper) > 1) {
?>
<a href="booking_view.php?booking_id=<?php
echo $arrNewspaper['booking_id'];
?>
&action=view#id<?php
echo $arrNewspaper['booking_activity_id'];
?>
" title="<?php
echo htmlspecialchars($strBookingCode);
?>
/ <?php
echo htmlspecialchars($strBookingName);
?>
"><em><?php
echo htmlspecialchars($strBookingCode);
?>
</em></a>
<?php
} elseif (mktime(0, 0, 0, $intMonth, 1, $intYear) < time() + $intMinBookingTime) {
?>
N/A
<?php
} else {
?>
<a href="booking.php?action=add&frm_activity_id=<?php
echo $rowNewspaper['activity_id'];
?>
" style="color:#468847;">Available</a>
<?php
}
?>
</div>
</td>
<td><div style="text-align:right;">$<?php
echo htmlspecialchars(number_format($strNewspaperPrice));
?>
</div></td>
</tr>
<?php
}
?>
</tbody>
<tfoot>
<td colspan="2"><div style="text-align:right;"><strong>Total</strong></div></td>
<td style="background-color:#ddd;"><div style="text-align:right;"><strong>$<?php
echo htmlspecialchars(number_format($strNewspaperPriceTotal, 2));
?>
</strong></div></td>
</tfoot>
</table>
<!-- Email -->
<div style="clear:both;"><h3 style="font-size:1.3em;">4 YOU Email</h3></div>
<table class="table table-bordered table-hover">
<thead class="well">
<tr>
<th style="text-align:center;width:20%;"><strong>Size/Spot</strong></th>
<th style="text-align:center;"><strong>Booking</strong></th>
<th style="text-align:center;width:20%;"><strong>Total</strong></th>
</tr>
</thead>
<tbody>
<?php
$queryEmail = "SELECT * FROM `mbs_activities` WHERE `activity_category` = 'email' ORDER BY `activity_name`";
$resultEmail = mysql_query($queryEmail);
while ($rowEmail = mysql_fetch_assoc($resultEmail)) {
$arrEmail = DB::getActivityByDateTime($rowEmail['activity_id'], $intYear, $intMonth);
$strBookingCode = DB::dbIDToField('mbs_bookings', 'booking_id', $arrEmail['booking_id'], 'booking_code');
$strBookingName = DB::dbIDToField('mbs_bookings', 'booking_id', $arrEmail['booking_id'], 'booking_name');
$strEmailPrice = $arrEmail['booking_activity_price_total'];
$strEmailPriceTotal += $strEmailPrice;
?>
<tr>
<td><div style="text-align:left;"><?php
echo htmlspecialchars($rowEmail['activity_name']);
?>
</div></td>
<td style="background-color:<?php
if (count($arrEmail) > 1) {
?>
#f2dede<?php
} elseif (mktime(0, 0, 0, $intMonth, 1, $intYear) < time() + $intMinBookingTime) {
?>
#ddd<?php
} else {
?>
#dff0d8<?php
}
?>
;">
<div style="text-align:center;">
<?php
if (count($arrEmail) > 1) {
?>
<a href="booking_view.php?booking_id=<?php
echo $arrEmail['booking_id'];
?>
&action=view#id<?php
echo $arrEmail['booking_activity_id'];
?>
" title="<?php
echo htmlspecialchars($strBookingCode);
?>
/ <?php
echo htmlspecialchars($strBookingName);
?>
"><em><?php
echo htmlspecialchars($strBookingCode);
?>
</em></a>
<?php
} elseif (mktime(0, 0, 0, $intMonth, 1, $intYear) < time() + $intMinBookingTime) {
?>
N/A
<?php
} else {
?>
<a href="booking.php?action=add&frm_activity_id=<?php
echo $rowEmail['activity_id'];
?>
" style="color:#468847;">Available</a>
<?php
}
?>
</div>
</td>
<td><div style="text-align:right;">$<?php
echo htmlspecialchars(number_format($strEmailPrice));
?>
</div></td>
</tr>
<?php
}
?>
</tbody>
<tfoot>
<td colspan="2"><div style="text-align:right;"><strong>Total</strong></div></td>
<td style="background-color:#ddd;"><div style="text-align:right;"><strong>$<?php
echo htmlspecialchars(number_format($strEmailPriceTotal, 2));
?>
</strong></div></td>
</tfoot>
</table>
<!-- Other -->
<div style="clear:both;"><h3 style="font-size:1.3em;">Other</h3></div>
<table class="table table-bordered table-hover">
<thead class="well">
<tr>
<th style="text-align:center;width:20%;"><strong>Activity</strong></th>
<th style="text-align:center;"><strong>Booking</strong></th>
<th style="text-align:center;width:20%;"><strong>Total</strong></th>
</tr>
</thead>
<tbody>
<?php
$queryOther = "SELECT * FROM `mbs_activities` WHERE `activity_category` = 'other' ORDER BY `activity_name`";
$resultOther = mysql_query($queryOther);
while ($rowOther = mysql_fetch_assoc($resultOther)) {
$arrOther = DB::getActivityByDateTime($rowOther['activity_id'], $intYear, $intMonth);
$strBookingCode = DB::dbIDToField('mbs_bookings', 'booking_id', $arrOther['booking_id'], 'booking_code');
$strBookingName = DB::dbIDToField('mbs_bookings', 'booking_id', $arrOther['booking_id'], 'booking_name');
$strOtherPrice = $arrOther['booking_activity_price_total'];
$strOtherPriceTotal += $strOtherPrice;
?>
<tr>
<td><div style="text-align:left;"><?php
echo htmlspecialchars($rowOther['activity_name']);
?>
</div></td>
<td style="background-color:<?php
if (count($arrOther) > 1) {
?>
#f2dede<?php
} elseif (mktime(0, 0, 0, $intMonth, 1, $intYear) < time() + $intMinBookingTime) {
?>
#ddd<?php
} else {
?>
#dff0d8<?php
}
?>
;">
<div style="text-align:center;">
<?php
if (count($arrOther) > 1) {
?>
<a href="booking_view.php?booking_id=<?php
echo $arrOther['booking_id'];
?>
&action=view#id<?php
echo $arrOther['booking_activity_id'];
?>
" title="<?php
echo htmlspecialchars($strBookingCode);
?>
/ <?php
echo htmlspecialchars($strBookingName);
?>
"><em><?php
echo htmlspecialchars($strBookingCode);
?>
</em></a>
<?php
} elseif (mktime(0, 0, 0, $intMonth, 1, $intYear) < time() + $intMinBookingTime) {
?>
N/A
<?php
} else {
?>
<a href="booking.php?action=add&frm_activity_id=<?php
echo $rowOther['activity_id'];
?>
" style="color:#468847;">Available</a>
<?php
}
?>
</div>
</td>
<td><div style="text-align:right;">$<?php
echo htmlspecialchars(number_format($strOtherPrice));
?>
</div></td>
</tr>
<?php
}
?>
</tbody>
<tfoot>
<td colspan="2"><div style="text-align:right;"><strong>Total</strong></div></td>
<td style="background-color:#ddd;"><div style="text-align:right;"><strong>$<?php
echo htmlspecialchars(number_format($strOtherPriceTotal, 2));
?>
</strong></div></td>
</tfoot>
</table>
<div style="clear:both;"><h3 style="font-size:1.3em;">Summary</h3></div>
<table class="table table-bordered table-hover">
<thead class="well">
<tr>
<th style="text-align:center;width:20%;"><strong>Summary</strong></th>
<th style="text-align:center;width:20%;"><strong>Data</strong></th>
<th style="text-align:center;"><strong>Chart</strong></th>
</tr>
</thead>
<tbody>
<?php
$strTotal = $strInStoreTotal + $strCataloguePriceTotal + $strNewspaperPriceTotal + $strEmailPriceTotal + $strOtherPriceTotal;
?>
<tr>
<td><div style="text-align:left;">In-Store</div></td>
<td><div style="text-align:right;"><strong>$<?php
echo number_format($strInStoreTotal);
?>
</strong></div></td>
<td rowspan="6"><div id="chart"></div></td>
</tr>
<tr>
<td><div style="text-align:left;">Catalogue</div></td>
<td><div style="text-align:right;"><strong>$<?php
echo number_format($strCataloguePriceTotal);
?>
</strong></div></td>
</tr>
<tr>
<td><div style="text-align:left;">Newspaper</div></td>
<td><div style="text-align:right;"><strong>$<?php
echo number_format($strNewspaperPriceTotal);
?>
</strong></div></td>
</tr>
<tr>
<td><div style="text-align:left;">4 YOU Email</div></td>
<td><div style="text-align:right;"><strong>$<?php
echo number_format($strEmailPriceTotal);
?>
</strong></div></td>
</tr>
<tr>
<td><div style="text-align:left;">Other</div></td>
<td><div style="text-align:right;"><strong>$<?php
echo number_format($strOtherPriceTotal);
?>
</strong></div></td>
</tr>
<tr>
<td><div style="text-align:left;"><strong>Total Amount</strong></div></td>
<td style="background-color:#ccc;">
<div style="text-align:right;"><strong>$<?php
echo number_format($strTotal);
?>
</strong></div>
</td>
</tr>
</tbody>
<tfoot>
</tfoot>
</table>
<script>
$(document).ready(function(){
var data = [
['In-Store', <?php
echo $strInStoreTotal;
?>
],['Catalogue', <?php
echo $strCataloguePriceTotal;
?>
], ['Newspaper', <?php
echo $strNewspaperPriceTotal;
?>
],
['4 You Email', <?php
echo $strEmailPriceTotal;
?>
],['Other', <?php
echo $strOtherPriceTotal;
?>
]
];
var plot = jQuery.jqplot ('chart', [data],
{
seriesDefaults: {
// Make this a pie chart.
renderer: jQuery.jqplot.PieRenderer,
rendererOptions: {
// Put data labels on the pie slices.
// By default, labels show the percentage of the slice.
showDataLabels: true
}
},
title: 'Revenue <?php
echo HTML::getMonthName($intMonth) . " " . $intYear;
?>
',
grid: {
drawBorder: false,
drawGridlines: false,
background: '#ffffff',
shadow:false
},
seriesColors:['#f00', '#0f0', '#00f', '#fa0', '#b51be0'],
legend: { show:true, location: 'e' }
}
);
});
</script>
<?php
// The Log
$strLog = "View monthly General Report for \"" . HTML::getMonthName($intMonth) . " " . $intYear . "\"";
$queryLog = "INSERT INTO `logs` (`log_id`, \n\t\t\t\t\t\t\t\t\t\t `log_user`, \n\t\t\t\t\t\t\t\t\t\t `log_action`, \n\t\t\t\t\t\t\t\t\t\t `log_time`, \n\t\t\t\t\t\t\t\t\t\t `log_from`, \n\t\t\t\t\t\t\t\t\t\t `log_logout`)\n\n\t\t\t\t\tVALUES (NULL, \n\t\t\t\t\t\t\t'" . $_SESSION['user']['login_name'] . "',\n\t\t\t\t\t\t\t'" . mysql_real_escape_string($strLog) . "',\n\t\t\t\t\t\t\t'" . date('Y-m-d H:i:s') . "',\n\t\t\t\t\t\t\t'" . $_SESSION['user']['ip_address'] . "', \n\t\t\t\t\t\t\tNULL)";
$resultLog = mysql_query($queryLog);
#echo "<div class='well' style=''>";
#echo "<strong>Debug:</strong><br />";
#print_r($_REQUEST);
#echo "</div>";
}
