* @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>"; }