fannie/reports/FloorSectionSales/floorSectionSalesReport.php
<?php
/*******************************************************************************
Copyright 2012 Whole Foods Co-op
This file is part of CORE-POS.
CORE-POS is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.
CORE-POS is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
in the file license.txt along with IT CORE; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
*********************************************************************************/
include(dirname(__FILE__) . '/../../config.php');
if (!class_exists('FannieAPI')) {
include_once(__DIR__ . '/../../classlib2.0/FannieAPI.php');
}
class floorSectionSalesReport extends FannieReportPage
{
protected $header = "Floor Sections Report";
protected $title = "Fannie :: Sales Report by Floor Sections";
protected $report_cache = 'none';
protected $report_headers = array('UPC','Brand','Description','Auto Par','On Sale','Sale Price','Last on Sale');
protected $required_fields = array('floorsection');
public $description = '[Sales Report by Floor Sections] lists sales/non-sales items in respect to physical
product locations.';
public $report_set = 'Sales';
protected $new_tablesorter = true;
function getSaleItems()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$start = FormLib::get_form_value('date1','');
$end = FormLib::get_form_value('date2','');
$batchesModel = new BatchesModel($dbc);
$batchListModel = new BatchListModel($dbc);
$batchesModel->startDate($start);
$batchesModel->endDate($end);
$batches = array();
$items = array();
foreach ($batchesModel->find() as $dataA) {
$batchListModel->batchID($data->batchID());
foreach ($batchListModel()->find() as $dataB) {
$upc = $dataB->upc();
$items[$upc]['salePrice'] = $dataB->salePrice;
}
}
return $items;
}
private function getFloorSections()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$ret = '';
$prepQ = $dbc->prepare("SELECT * FROM FloorSections");
$prepR = $dbc->execute($prepQ);
$ret .= '<select name="floorsection" class="form-control">';
$ret .= '<option value="0">Select A Section</option>';
while ($row = $dbc->fetchRow($prepR)) {
$ret .= sprintf('<option class="store'.$row['storeID'].'"
value="%d">%s</option>',$row['floorSectionID'],$row['name']);
}
$ret .= '</select>';
return $ret;
}
function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$start = FormLib::get_form_value('date1','');
$end = FormLib::get_form_value('date2','');
$floorsection = FormLib::get('floorsection');
$data = array();
$store = FormLib::get('store');
$fsModel = new FloorSectionsModel($dbc);
$fsModel->floorSectionID($floorsection);
$fsModel->load();
//deletme
echo '<div class="alert alert-danger" align="center">This page is currently under development.
If you are seeineg this message, please do not use this report.</div>';
echo '<h4>Viewing products in <strong>'.$fsModel->name().'</strong></h4>';
$inSectionA = array($floorsection);
$inSectionP = $dbc->prepare("
SELECT upc
FROM FloorSectionProductMap
WHERE floorSectionID = ?
");
$inSectionR = $dbc->execute($inSectionP,$inSectionA);
$upcs = array();
while ($row = $dbc->fetchRow($inSectionR)) {
$upcs[] = $row['upc'];
}
$onSaleA = array($start, $store);
list($inClause,$onSaleA) = $dbc->safeInClause($upcs, $onSaleA);
//$onSaleA = array($start,$store);
$onSaleQ = "
SELECT
p.upc,p.brand,p.description,p.auto_par,
b.batchID,
l.salePrice,
v.vendorID
FROM products AS p
INNER JOIN batchList AS l ON p.upc=l.upc
INNER JOIN batches AS b ON l.batchID=b.batchID AND b.startDate = ?
INNER JOIN vendorSKUtoPLU AS v ON p.upc=v.upc
WHERE p.inUse = 1
AND p.store_id = ?
AND p.upc IN (".$inClause.")
";
$onSaleP = $dbc->prepare($onSaleQ);
$onSaleR = $dbc->execute($onSaleP,$onSaleA);
$i = 0;
while ($row = $dbc->fetchRow($onSaleR)) {
$upc = $row['upc'];
$data[$i][0] = $upc;
$data[$i][1] = $row['brand'];
$data[$i][2] = $row['description'];
$data[$i][3] = round($row['auto_par'],2);
$data[$i][4] = 'Yes';
$data[$i][5] = $row['salePrice'];
$data[$i][6] = 'currently on sale';
$i++;
}
if ($err = $dbc->error()) {
echo '<div class="alert alert-danger">'.$err.'</div>';
}
$model = new ProductsModel($dbc);
foreach ($upcs as $upc) {
//$data[$i][0] = $upc;
$args = array($upc,'1');
$prep = $dbc->prepare("
SELECT
*
FROM products AS p
WHERE p.upc = ?
AND p.store_id = ?
AND p.inUse = 1
");
$res = $dbc->execute($prep,$args);
while ($row = $dbc->fetchRow($res)) {
$data[$i][0] = $upc;
$data[$i][1] = $row['brand'];
$data[$i][2] = $row['description'];
$data[$i][3] = round($row['auto_par'],2);
if (empty($data[$i][4])) {
$data[$i][4] = 'No';
}
$data[$i][5] = 'n/a';
$data[$i][6] = 'n/a';
$a = array($upc);
$p = $dbc->prepare("
SELECT
b.startDate, b.endDate
FROM batches AS b
LEFT JOIN batchList AS l ON b.batchID=l.batchID
WHERE l.upc = ?
ORDER BY b.startDate DESC
LIMIT 1;
");
$r = $dbc->execute($p,$a);
while ($row = $dbc->fetchRow($r)) {
if (substr($row['startDate'],0,4) < 2000) {
$data[$i][6] = 'n/a';
} else {
$data[$i][6] = substr($row['startDate'],0,10);
}
}
$i++;
}
if ($er = $dbc->error()) {
echo '<div class="alert alert-danger">'.$er.'</div>';
}
}
return $data;
}
function form_content()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
ob_start();
echo '<hr />';
echo '<form method="get" class="form-group">';
echo '<div class="row">';
$test = 'abc';
$rightCol = <<<HTML
<div class="col-sm-7">
<p>
<div class="row">
<div class="col-sm-3">
<label>Start Date</label>
</div>
<div class="col-sm-4">
<input class="form-control date-field" name="date1" id="date1" />
</div>
</div>
</p>
<p>
<div class="row">
<div class="col-sm-3">
<label>End Date</label>
</div>
<div class="col-sm-4">
<input class="form-control date-field" name="date2" id="date2" />
</div>
</div>
</p>
<p>
<div class="row">
<div class="col-sm-3">
<label>Store(s)</label>
</div>
<div class="col-sm-4">
{{STORES}}
</div>
</div>
</p>
<p>
<div class="row">
<div class="col-sm-3">
<label>Floor Section</label>
</div>
<div class="col-sm-4">
{{FLOORSECTIONS}}
</div>
</div>
</p>
<p>
<div class="row">
<div class="col-sm-3">
<label>Excel
</div>
<div class="col-sm-4">
<input type="checkbox" name="excel" value="xls" />
</label>
</div>
</div>
</p>
<p>
<div class="row">
<div class="col-sm-3">
</div>
<div class="col-sm-4">
<button type="submit" class="btn btn-default form-control">Run Report</button>
</div>
</div>
</p>
</div>
</div>
HTML;
$stores = FormLib::storePicker();
$html = str_replace('{{STORES}}', $stores['html'], $rightCol);
$sections = $this->getFloorSections();
$html = str_replace('{{FLOORSECTIONS}}', $sections, $html);
echo $html;
echo '</form>';
return ob_get_clean();
}
public function javascriptContent()
{
ob_start();
?>
$(document).ready(function () {
$('select[name=store]').change(function () {
var storeSelected = 'store'+$(this).val();
$('select[name=floorsection]').find('option').each(function () {
$(this).show();
optName = $(this).attr('class');
if (optName != storeSelected ) {
$(this).hide();
}
});
});
});
<?php
return ob_get_clean();
}
public function helpContent()
{
return '<p>Show sales information for products in respect to physical product
locations.</p>';
}
}
FannieDispatch::conditionalExec();