public function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$date1 = $this->form->date1;
$date2 = $this->form->date2;
$deptStart = FormLib::get('deptStart');
$deptEnd = FormLib::get('deptEnd');
$deptMulti = FormLib::get('departments', array());
$include_sales = FormLib::get('includeSales', 0);
$buyer = FormLib::get('buyer', '');
// args/parameters differ with super
// vs regular department
$args = array($date1 . ' 00:00:00', $date2 . ' 23:59:59');
$where = ' 1=1 ';
if ($buyer !== '') {
if ($buyer == -2) {
$where .= ' AND s.superID != 0 ';
} elseif ($buyer != -1) {
$where .= ' AND s.superID=? ';
$args[] = $buyer;
}
}
if ($buyer != -1) {
list($conditional, $args) = DTrans::departmentClause($deptStart, $deptEnd, $deptMulti, $args);
$where .= $conditional;
}
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$query = "SELECT d.upc,\n p.brand,\n p.description,\n d.department,\n t.dept_name,\n SUM(total) AS total,\n SUM(d.cost) AS cost," . DTrans::sumQuantity('d') . " AS qty\n FROM {$dlog} AS d " . DTrans::joinProducts('d', 'p', 'inner') . DTrans::joinDepartments('d', 't');
// join only needed with specific buyer
if ($buyer !== '' && $buyer > -1) {
$query .= 'LEFT JOIN superdepts AS s ON d.department=s.dept_ID ';
} elseif ($buyer !== '' && $buyer == -2) {
$query .= 'LEFT JOIN MasterSuperDepts AS s ON d.department=s.dept_ID ';
}
$query .= "WHERE tdate BETWEEN ? AND ?\n AND {$where}\n AND d.cost <> 0 ";
if ($include_sales != 1) {
$query .= "AND d.discounttype=0 ";
}
$query .= "GROUP BY d.upc,p.description,d.department,t.dept_name\n ORDER BY sum(total) DESC";
$prep = $dbc->prepare_statement($query);
$result = $dbc->exec_statement($query, $args);
$data = array();
$sum_total = 0.0;
$sum_cost = 0.0;
while ($row = $dbc->fetch_row($result)) {
$margin = $row['total'] == 0 ? 0 : ($row['total'] - $row['cost']) / $row['total'] * 100;
$record = array($row['upc'], $row['brand'], $row['description'], $row['department'], $row['dept_name'], sprintf('%.2f', $row['cost']), sprintf('%.2f', $row['total']), sprintf('%.2f', $margin), sprintf('%.2f', $row['qty'] == 0 ? 0 : ($row['total'] - $row['cost']) / $row['qty']));
$sum_total += $row['total'];
$sum_cost += $row['cost'];
$data[] = $record;
}
// go through and add a contribution to margin value
for ($i = 0; $i < count($data); $i++) {
// (item_total - item_cost) / total sales
$contrib = ($data[$i][5] - $data[$i][4]) / $sum_total * 100;
$data[$i][] = sprintf('%.2f', $contrib);
}
return $data;
}
public function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$upc = $this->form->upc;
$upc = BarcodeLib::padUPC($upc);
$query = 'SELECT i.sku, i.quantity, i.unitCost, i.caseSize,
i.quantity * i.unitCost * i.caseSize AS ttl,
o.vendorInvoiceID, v.vendorName, o.placedDate
FROM PurchaseOrderItems AS i
LEFT JOIN PurchaseOrder AS o ON i.orderID=o.orderID
LEFT JOIN vendors AS v ON o.vendorID=v.vendorID
WHERE i.internalUPC = ?
AND o.placedDate >= ?
ORDER BY o.placedDate';
$prep = $dbc->prepare($query);
$args = array($upc);
if (FormLib::get('all')) {
$args[] = '1900-01-01 00:00:00';
} else {
$args[] = date('Y-m-d', strtotime('92 days ago'));
}
$result = $dbc->execute($prep, $args);
$data = array();
while ($row = $dbc->fetch_row($result)) {
$record = array($row['placedDate'], $row['vendorName'], $row['vendorInvoiceID'], $row['sku'], $row['quantity'], $row['caseSize'], $row['unitCost'], $row['ttl']);
$data[] = $record;
}
return $data;
}
public function fetch_report_data()
{
global $FANNIE_PLUGIN_SETTINGS, $FANNIE_OP_DB;
$dbc = FannieDB::get($FANNIE_PLUGIN_SETTINGS['GiveUsMoneyDB']);
$dt = FormLib::get('endDate', date('Y-m-d'));
$dividends = new GumDividendsModel($dbc);
$dividends->yearEndDate($dt);
$map = new GumDividendPayoffMapModel($dbc);
$check = new GumPayoffsModel($dbc);
$data = array();
foreach ($dividends->find('card_no') as $dividend) {
$record = array($dividend->card_no(), sprintf('%.2f', $dividend->equityAmount()), $dividend->daysHeld(), sprintf('%.2f%%', $dividend->dividendRate() * 100), sprintf('%.2f', $dividend->dividendAmount()));
$checkID = false;
$map->reset();
$map->gumDividendID($dividend->gumDividendID());
foreach ($map->find('gumPayoffID', true) as $obj) {
$checkID = $obj->gumPayoffID();
}
if (!$checkID) {
$record[] = '?';
} else {
$check->gumPayoffID($checkID);
$check->load();
$record[] = $check->checkNumber();
}
$data[] = $record;
}
return $data;
}
public function fetch_report_data()
{
global $FANNIE_PLUGIN_SETTINGS, $FANNIE_OP_DB;
$dbc = FannieDB::get($FANNIE_PLUGIN_SETTINGS['GiveUsMoneyDB']);
$month = FormLib::get('month', date('n'));
$year = FormLib::get('year', date('Y'));
$end_of_last_month = mktime(0, 0, 0, $month, 0, $year);
$ts = mktime(0, 0, 0, $month, 1, $year);
$end_of_next_month = mktime(0, 0, 0, $month, date('t', $ts), $year);
$end_last_dt = new DateTime(date('Y-m-d', $end_of_last_month));
$end_next_dt = new DateTime(date('Y-m-d', $end_of_next_month));
$loans = new GumLoanAccountsModel($dbc);
$data = array();
foreach ($loans->find('loanDate') as $loan) {
$record = array($loan->accountNumber(), number_format($loan->principal(), 2), number_format($loan->interestRate() * 100, 2) . '%', $loan->termInMonths(), date('Y-m-d', strtotime($loan->loanDate())));
$loanDT = new DateTime(date('Y-m-d', strtotime($loan->loanDate())));
$days1 = $loanDT->diff($end_last_dt)->format('%r%a');
$days2 = $loanDT->diff($end_next_dt)->format('%r%a');
$bal_before = $loan->principal() * pow(1.0 + $loan->interestRate(), $days1 / 365.25);
if ($days1 < 0) {
$bal_before = $loan->principal();
}
$bal_after = $loan->principal() * pow(1.0 + $loan->interestRate(), $days2 / 365.25);
if ($days2 < 0) {
$bal_after = $loan->principal();
}
$record[] = number_format($bal_before, 2);
$record[] = number_format($bal_after, 2);
$record[] = number_format($bal_after - $bal_before, 2);
$data[] = $record;
}
return $data;
}
public function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$query_parts = FormLib::standardItemFromWhere();
$query = '
SELECT t.upc,
t.description,
t.department,
d.dept_name,
' . DTrans::sumQuantity('t') . ' AS qty,
SUM(t.total) AS total ' . $query_parts['query'] . ' AND t.memType=?
GROUP BY t.upc,
t.description,
t.department,
d.dept_name
ORDER BY t.upc';
$args = $query_parts['args'];
$args[] = FormLib::get('memtype');
$data = array();
$prep = $dbc->prepare($query);
$res = $dbc->execute($prep, $args);
while ($w = $dbc->fetchRow($res)) {
$data[] = array($w['upc'], $w['description'], $w['department'] . ' ' . $w['dept_name'], sprintf('%.2f', $w['qty']), sprintf('%.2f', $w['total']));
}
return $data;
}
public function fetch_report_data()
{
$dbc = $this->connection;
$settings = $this->config->get('PLUGIN_SETTINGS');
$dbc->selectDB($settings['ScheduledEmailDB']);
$query = '
SELECT name,
cardNo,
sendDate,
sent,
sentDate,
sentToEmail
FROM ScheduledEmailQueue AS q
LEFT JOIN ScheduledEmailTemplates AS t
ON q.scheduledEmailTemplateID=t.scheduledEmailTemplateID
WHERE 1=1 ';
$args = array();
if (FormLib::get('member') != '') {
$query .= ' AND CardNo=? ';
$args[] = FormLib::get('member');
}
if (FormLib::get('date1') != '' && FormLib::get('date2') != '') {
$query .= ' AND sendDate BETWEEN ? AND ? ';
$args[] = FormLib::get('date1') . ' 00:00:00';
$args[] = FormLib::get('date2') . ' 23:59:59';
}
$prep = $dbc->prepare($query);
$res = $dbc->execute($prep, $args);
$data = array();
while ($w = $dbc->fetchRow($res)) {
$data[] = array($w['sendDate'], $w['name'], $w['cardNo'], $w['sent'] ? 'Yes' : 'No', $w['sentDate'] ? $w['sentDate'] : 'n/a', $w['sentToEmail'] ? $w['sentToEmail'] : 'n/a');
}
return $data;
}
function saveFormData($memNum)
{
$dbc = $this->db();
/**
Use primary member for default column values
*/
$account = self::getAccount();
if (!$account) {
return "Error: Problem saving household members<br />";
}
$json = array('cardNo' => $memNum, 'customerTypeID' => $account['customerTypeID'], 'memberStatus' => $account['memberStatus'], 'activeStatus' => $account['activeStatus'], 'customers' => array());
$primary = array('discount' => 0, 'staff' => 0, 'lowIncomeBenefits' => 0, 'chargeAllowed' => 0, 'checksAllowed' => 0);
foreach ($account['customers'] as $c) {
if ($c['accountHolder']) {
$primary = $c;
break;
}
}
$fns = FormLib::get_form_value('HouseholdMembers_fn', array());
$lns = FormLib::get_form_value('HouseholdMembers_ln', array());
$ids = FormLib::get('HouseholdMembers_ID', array());
for ($i = 0; $i < count($lns); $i++) {
$json['customers'][] = array('customerID' => $ids[$i], 'firstName' => $fns[$i], 'lastName' => $lns[$i], 'accountHolder' => 0, 'discount' => $primary['discount'], 'staff' => $primary['staff'], 'lowIncomeBenefits' => $primary['lowIncomeBenefits'], 'chargeAllowed' => $primary['chargeAllowed'], 'checksAllowed' => $primary['checksAllowed']);
}
$resp = \COREPOS\Fannie\API\member\MemberREST::post($memNum, $json);
if ($resp['errors'] > 0) {
return "Error: Problem saving household members<br />";
}
return '';
}
public function post_id_superID_growth_handler()
{
$lib_class = $this->lib_class;
$dbc = $lib_class::getDB();
$map = $lib_class::getCategoryMap($dbc);
for ($i = 0; $i < count($this->id); $i++) {
if (!isset($this->superID[$i])) {
continue;
}
$map->obfCategoryID($this->id[$i]);
$map->superID($this->superID[$i]);
$map->growthTarget(isset($this->growth[$i]) ? $this->growth[$i] / 100.0 : 0);
$map->save();
}
$delete = FormLib::get('delete', array());
if (is_array($delete)) {
foreach ($delete as $ids) {
list($cat, $super) = explode(':', $ids, 2);
$map->obfCategoryID($cat);
$map->superID($super);
$map->delete();
}
}
header('Location: ' . filter_input(INPUT_SERVER, 'PHP_SELF'));
return false;
}
public function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$FANNIE_URL = $this->config->get('URL');
$super = $this->form->submit == "by_sd" ? 1 : 0;
$join = "";
$where = "";
$args = array();
if ($super == 1) {
$superID = FormLib::get('superdept');
$join = "LEFT JOIN superdepts AS s ON d.dept_no=s.dept_ID\n LEFT JOIN superDeptNames AS m ON s.superID=m.superID ";
$where = "s.superID = ?";
$args[] = $superID;
} else {
$d1 = FormLib::get('dept1');
$d2 = FormLib::get('dept2');
$join = " LEFT JOIN MasterSuperDepts AS m ON d.dept_no=m.dept_ID";
$where = "d.dept_no BETWEEN ? AND ?";
$args = array($d1, $d2);
}
$query = $dbc->prepare_statement("SELECT d.dept_no,d.dept_name,d.salesCode,d.margin,\n CASE WHEN d.dept_tax=0 THEN 'NoTax' ELSE t.description END as tax,\n CASE WHEN d.dept_fs=1 THEN 'Yes' ELSE 'No' END as fs,\n m.super_name\n FROM departments AS d \n LEFT JOIN taxrates AS t ON d.dept_tax = t.id \n {$join}\n WHERE {$where}\n ORDER BY d.dept_no");
$result = $dbc->exec_statement($query, $args);
$data = array();
while ($row = $dbc->fetch_row($result)) {
$record = array($row[0], isset($_REQUEST['excel']) ? $row[1] : "<a href=\"{$FANNIE_URL}item/departments/DepartmentEditor.php?did={$row['0']}\">{$row['1']}</a>", $row['super_name'], $row[2], sprintf('%.2f%%', $row[3] * 100), $row[4], $row[5]);
if (empty($row['super_name'])) {
$record['meta'] = FannieReportPage::META_COLOR;
$record['meta_background'] = '#ff9999';
}
$data[] = $record;
}
return $data;
}
public function draw_page()
{
include dirname(__FILE__) . '/../../config.php';
$dbc = FannieDB::get($FANNIE_TRANS_DB);
$id = FormLib::get('id', 0);
$prep = $dbc->prepare('SELECT filetype, filecontents FROM CapturedSignature WHERE capturedSignatureID=?');
$result = $dbc->execute($prep, array($id));
if ($dbc->num_rows($result) > 0) {
$row = $dbc->fetch_row($result);
switch (strtoupper($row['filetype'])) {
case 'BMP':
header('Content-type: image/bmp');
break;
case 'PNG':
header('Content-type: image/png');
break;
case 'JPG':
header('Content-type: image/jpeg');
break;
case 'GIF':
header('Content-type: image/gif');
break;
default:
// Content-type: application/octet-stream
// may be helpful in this scenario but appears
// to be technically incorrect. in any event
// it really should not occur
break;
}
echo $row['filecontents'];
}
}
public function post_upc_description_department_cost_price_qty_reason_handler()
{
global $FANNIE_TRANS_DB, $FANNIE_EMP_NO, $FANNIE_REGISTER_NO;
$dbc = FannieDB::get($FANNIE_TRANS_DB);
$record = DTrans::$DEFAULTS;
$record['emp_no'] = $FANNIE_EMP_NO;
$record['register_no'] = $FANNIE_REGISTER_NO;
$record['trans_no'] = DTrans::getTransNo($dbc, $FANNIE_EMP_NO, $FANNIE_REGISTER_NO);
$record['trans_id'] = 1;
$record['upc'] = $this->upc;
$record['description'] = $this->description;
$record['department'] = $this->department;
$record['trans_type'] = 'I';
$record['quantity'] = $this->qty;
$record['ItemQtty'] = $this->qty;
$record['unitPrice'] = $this->price;
$record['regPrice'] = $this->price;
$record['total'] = $this->qty * $this->price;
$record['cost'] = $this->qty * $this->cost;
$record['numflag'] = $this->reason;
$record['charflag'] = strlen(FormLib::get('type')) > 0 ? strtoupper(substr(FormLib::get('type'), 0, 1)) : '';
$record['trans_status'] = 'Z';
$info = DTrans::parameterize($record, 'datetime', $dbc->now());
$query = 'INSERT INTO dtransactions
(' . $info['columnString'] . ')
VALUES
(' . $info['valueString'] . ')';
$prep = $dbc->prepare($query);
$result = $dbc->execute($prep, $info['arguments']);
header('Location: ' . $_SERVER['PHP_SELF'] . '?msg=1');
return false;
}
public function post_handler()
{
global $FANNIE_OP_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
$amount = FormLib::get('amount');
$paid = FormLib::get('paid') / 100.0;
$retained = FormLib::get('retained') / 100.0;
$netQ = '
SELECT SUM(p.net_purch) AS ttl
FROM patronage_workingcopy AS p
INNER JOIN custdata AS c ON p.cardno=c.CardNo AND c.personNum=1
WHERE c.Type=\'PC\'';
$netR = $dbc->query($netQ);
$netW = $dbc->fetch_row($netR);
$purchases = $netW['ttl'];
$personQ = '
SELECT p.net_purch,
c.cardno
FROM patronage_workingcopy AS p
INNER JOIN custdata AS c ON p.cardno=c.CardNo AND c.personNum=1
WHERE c.Type=\'PC\'';
$personR = $dbc->query($personQ);
$this->insertRecords($dbc, $personR, $purchases, $paid, $retained, $amount);
$finishQ = '
INSERT INTO patronage
(cardno, purchase, discounts, rewards, net_purch, tot_pat, cash_pat, equit_pat, FY)
SELECT
p.cardno, purchase, discounts, rewards, net_purch, tot_pat, cash_pat, equit_pat, FY
FROM patronage_workingcopy AS p
INNER JOIN custdata AS c ON p.cardno=c.CardNo AND c.personNum=1
WHERE c.Type=\'PC\'';
$dbc->query($finishQ);
return true;
}
public function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$upc = BarcodeLib::padUPC(FormLib::get('upc'));
$date1 = $this->form->date1;
$date2 = $this->form->date2;
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$q = $dbc->prepare("\n SELECT d.card_no,\n sum(quantity) as qty,\n sum(total) as amt\n FROM {$dlog} AS d \n WHERE d.upc=? AND \n tdate BETWEEN ? AND ?\n GROUP BY d.card_no\n ORDER BY d.card_no");
$r = $dbc->exec_statement($q, array($upc, $date1 . ' 00:00:00', $date2 . ' 23:59:59'));
$data = array();
while ($w = $dbc->fetch_row($r)) {
$account = \COREPOS\Fannie\API\member\MemberREST::get($w['card_no']);
if ($account == false) {
continue;
}
$customer = array();
foreach ($account['customers'] as $c) {
if ($c['accountHolder']) {
$customer = $c;
break;
}
}
$record = array($w['card_no'], $customer['lastName'] . ', ' . $customer['firstName'], $account['addressFirstLine'] . ' ' . $account['addressSecondLine'], $account['city'], $account['state'], $account['zip'], $customer['phone'], $customer['altPhone'], $customer['email'], sprintf('%.2f', $w['qty']), sprintf('%.2f', $w['amt']));
$data[] = $record;
}
return $data;
}
public function preprocess()
{
$acct = FormLib::get('id');
$this->header = 'Loan Schedule' . ' : ' . $acct;
$this->title = 'Loan Schedule' . ' : ' . $acct;
return parent::preprocess();
}
public function post_download_handler()
{
header('Content-Type: application/ms-excel');
header('Content-Disposition: attachment; filename="PDFasCSV.csv"');
echo FormLib::get('download');
return false;
}
private function ajaxCallback()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$m = FormLib::get('month', 1);
$y = FormLib::get('year', date('Y'));
if (!is_numeric($y)) {
return "Error: Invalid year";
} elseif (!is_numeric($m)) {
return "Error: Invalid month";
}
$ret = "<form action=\"SalePerformanceReport.php\" method=\"get\">\n <p>\n <button type=submit class=\"btn btn-default\">Get Report</button>\n </p>";
$ret .= sprintf("<input type=hidden name=month value=%d />\n <input type=hidden name=year value=%d />", $m, $y);
$ret .= "<table class=\"table\">";
$ret .= "<tr><th> </th><th>Batch</th><th>Start</th><th>End</th></tr>";
$q = $dbc->prepare_statement("SELECT batchID,batchName,startDate,endDate FROM\n batches WHERE discounttype <> 0 AND (\n (year(startDate)=? and month(startDate)=?) OR\n (year(endDate)=? and month(endDate)=?)\n ) ORDER BY startDate,batchType,batchName");
$r = $dbc->exec_statement($q, array($y, $m, $y, $m));
while ($w = $dbc->fetch_row($r)) {
list($start, $time) = explode(' ', $w[2], 2);
list($end, $time) = explode(' ', $w[3], 2);
$ret .= sprintf("<tr>\n <td><input type=checkbox name=ids[] value=%d id=\"batch-checkbox-%d\" /></td>\n <td><label for=\"batch-checkbox-%d\">%s</label></td>\n <td>%s</td><td>%s</td>\n <input type=hidden name=bnames[] value=\"%s\" /></tr>", $w['batchID'], $w['batchID'], $w['batchID'], $w['batchName'], $start, $end, $w['batchName'] . " (" . $start . " " . $end . ")");
}
$ret .= "</table>\n <p>\n <button type=submit class=\"btn btn-default\">Get Report</button>\n </p>\n </form>";
return $ret;
}
public function preprocess()
{
$acct = FormLib::get('id');
$this->header = 'Promissory Note' . ' : ' . $acct;
$this->title = 'Promissory Note' . ' : ' . $acct;
$this->__routes[] = 'get<id><pdf>';
return parent::preprocess();
}
function preprocess()
{
global $FANNIE_OP_DB;
$id = FormLib::get_form_value('id', 0);
$dbc = FannieDB::get($FANNIE_OP_DB);
$tags = new ShelftagsModel($dbc);
$tags->id($id);
$current_set = $tags->find();
if (count($current_set) == 0) {
$this->messages = '<div class="alert alert-info">
Barcode table is already empty. <a href="ShelfTagIndex.php">Click here to continue</a>
</div>';
return true;
}
if (FormLib::get('submit', false) === '1') {
/**
Shelftags are not actually delete immediately
Instead, the id field is negated so they disappear
from view but can be manually retreived by IT if
someone comes complaining that they accidentally
delete their tags (not that such a thing would
ever occur). They're properly deleted by the
nightly.clipboard cron job.
If the same user deletes the same UPC from tags
multiple times in a day, the above procedure creates
a primary key conflict. So any negative-id records
that will create conflicts must be removed first.
*/
$new_id = -1 * $id;
if ($id == 0) {
$new_id = -999;
}
$clear = new ShelftagsModel($dbc);
$clear->id($new_id);
foreach ($current_set as $tag) {
// delete existing negative id tag for upc
$clear->upc($tag->upc());
$clear->delete();
// save tag as negative id
$old_id = $tag->id();
$tag->id($new_id);
$tag->save();
$tag->id($old_id);
$tag->delete();
}
$this->messages = '<div class="alert alert-success">
Barcode table cleared <a href="ShelfTagIndex.php">Click here to continue</a>
</div>';
return true;
} else {
$this->messages = '<div class="alert alert-danger">
<a href="DeleteShelfTags.php?id=' . $id . '&submit=1">Click
here to clear barcodes</a></div>';
return true;
}
return true;
}
public function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$date1 = $this->form->date1;
$date2 = $this->form->date2;
$deptStart = FormLib::get('deptStart');
$deptEnd = FormLib::get('deptEnd');
$deptMulti = FormLib::get('departments', array());
$buyer = FormLib::get('buyer', '');
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$queryAll = "SELECT YEAR(tdate) AS year, MONTH(tdate) AS month, DAY(tdate) AS day,\n COUNT(DISTINCT trans_num) as trans_count\n FROM {$dlog} AS d \n WHERE tdate BETWEEN ? AND ?\n GROUP BY YEAR(tdate), MONTH(tdate), DAY(tdate)\n ORDER BY YEAR(tdate), MONTH(tdate), DAY(tdate)";
$argsAll = array($date1 . ' 00:00:00', $date2 . ' 23:59:59');
$querySelected = "SELECT YEAR(tdate) AS year, MONTH(tdate) AS month, DAY(tdate) AS day,\n COUNT(DISTINCT trans_num) as trans_count\n FROM {$dlog} AS d ";
if ($buyer !== '' && $buyer > -1) {
$querySelected .= " LEFT JOIN superdepts AS s ON d.department=s.dept_ID ";
} elseif ($buyer !== '' && $buyer == -2) {
$query .= 'LEFT JOIN MasterSuperDepts AS s ON d.department=s.dept_ID ';
}
$querySelected .= " WHERE tdate BETWEEN ? AND ? ";
$argsSel = $argsAll;
if ($buyer !== '') {
if ($buyer == -2) {
$querySelected .= ' AND s.superID != 0 ';
} elseif ($buyer != -1) {
$querySelected .= ' AND s.superID=? ';
$argsSel[] = $buyer;
}
}
if ($buyer != -1) {
list($conditional, $argsSel) = DTrans::departmentClause($deptStart, $deptEnd, $deptMulti, $argsSel);
$querySelected .= $conditional;
}
$querySelected .= " GROUP BY YEAR(tdate), MONTH(tdate), DAY(tdate)";
$dataset = array();
$prep = $dbc->prepare_statement($queryAll);
$result = $dbc->exec_statement($prep, $argsAll);
while ($row = $dbc->fetch_row($result)) {
$datestr = sprintf("%d/%d/%d", $row['month'], $row['day'], $row['year']);
$dataset[$datestr] = array('ttl' => $row['trans_count'], 'sub' => 0);
}
$prep = $dbc->prepare_statement($querySelected);
$result = $dbc->exec_statement($prep, $argsSel);
while ($row = $dbc->fetch_row($result)) {
$datestr = sprintf("%d/%d/%d", $row['month'], $row['day'], $row['year']);
if (isset($dataset[$datestr])) {
$dataset[$datestr]['sub'] = $row['trans_count'];
}
}
$data = array();
foreach ($dataset as $date => $count) {
$record = array($date, $count['sub'], $count['ttl']);
$record[] = sprintf('%.2f%%', $count['sub'] / $count['ttl'] * 100);
$data[] = $record;
}
return $data;
}
public function preprocess()
{
$acct = FormLib::get('id');
$this->header = 'Tax Identification' . ' : ' . $acct;
$this->title = 'Tax Identification' . ' : ' . $acct;
$this->__routes[] = 'post<id><new1><new2>';
$this->__routes[] = 'post<id><key>';
return parent::preprocess();
}
function saveFormData($memNum)
{
$json = array('cardNo' => $memNum, 'startDate' => FormLib::get('MemDates_start'), 'endDate' => FormLib::get('MemDates_end'));
$resp = \COREPOS\Fannie\API\member\MemberREST::post($memNum, $json);
if ($resp['errors'] > 0) {
return "Error: problem saving start/end dates<br />";
} else {
return "";
}
}
public function fetch_report_data()
{
$settings = $this->config->get('PLUGIN_SETTINGS');
$dbc = FannieDB::get($settings['WarehouseDatabase']);
$dates = array(date('Ymd', strtotime(FormLib::get('date1'))), date('Ymd', strtotime(FormLib::get('date2'))));
$query = '
SELECT MIN(t.date_id) AS start,
MAX(t.date_id) AS end,
COUNT(*) AS numTrans,
SUM(retailTotal) AS retailTotal,
SUM(retailQty) AS retailQty,
SUM(nonRetailTotal) AS nonRetailTotal,
SUM(nonRetailQty) AS nonRetailQty,
CASE WHEN custdataType=\'PC\' THEN 1 ELSE 0 END AS isMember
FROM transactionSummary AS t
INNER JOIN WarehouseDates AS w ON t.date_id=w.warehouseDateID
LEFT JOIN ' . $this->config->get('OP_DB') . $dbc->sep() . 'memtype AS m ON m.memtype=t.memType
WHERE t.date_id BETWEEN ? AND ?
GROUP BY custdataType,
w.year,
w.isoWeekNumber
ORDER BY MIN(t.date_id),
CASE WHEN custdataType=\'PC\' THEN 1 ELSE 0 END DESC';
$prep = $dbc->prepare($query);
$result = $dbc->execute($prep, $dates);
$data = array();
while ($w = $dbc->fetchRow($result)) {
if ($w['end'] - $w['start'] < 6) {
continue;
}
$span = date('Y-m-d', strtotime($w['start'])) . ' - ' . date('Y-m-d', strtotime($w['end']));
if (!isset($data[$span])) {
$data[$span] = array('MemCount' => 0, 'NonMemCount' => 0, 'MemRetail' => 0.0, 'NonMemRetail' => 0.0, 'MemRetailQty' => 0.0, 'NonMemRetailQty' => 0.0, 'MemTotal' => 0.0, 'NonMemTotal' => 0.0, 'MemTotalQty' => 0.0, 'NonMemTotalQty' => 0.0);
}
if ($w['isMember']) {
$data[$span]['MemCount'] = $w['numTrans'];
$data[$span]['MemRetail'] = $w['retailTotal'];
$data[$span]['MemRetailQty'] = $w['retailQty'];
$data[$span]['MemTotal'] = $w['retailTotal'] + $w['nonRetailTotal'];
$data[$span]['MemTotalQty'] = $w['retailQty'] + $w['nonRetailQty'];
} else {
$data[$span]['NonMemCount'] = $w['numTrans'];
$data[$span]['NonMemRetail'] = $w['retailTotal'];
$data[$span]['NonMemRetailQty'] = $w['retailQty'];
$data[$span]['NonMemTotal'] = $w['retailTotal'] + $w['nonRetailTotal'];
$data[$span]['NonMemTotalQty'] = $w['retailQty'] + $w['nonRetailQty'];
}
}
$report = array();
foreach ($data as $span => $info) {
$record = array($span, $info['MemCount'], $info['NonMemCount'], $info['MemCount'] + $info['NonMemCount'], sprintf('%.2f', $info['MemRetailQty']), sprintf('%.2f', $info['NonMemRetailQty']), sprintf('%.2f', $info['MemRetailQty'] + $info['NonMemRetailQty']), sprintf('%.2f', $info['MemRetail']), sprintf('%.2f', $info['NonMemRetail']), sprintf('%.2f', $info['MemRetail'] + $info['NonMemRetail']), sprintf('%.2f', $info['MemRetail'] / $info['MemCount']), sprintf('%.2f', $info['NonMemRetail'] / $info['NonMemCount']), sprintf('%.2f', ($info['MemRetail'] + $info['NonMemRetail']) / ($info['MemCount'] + $info['NonMemCount'])));
$report[] = $record;
}
return $report;
}
public function fetch_report_data()
{
$item = array();
$batchID = array();
$upc = array();
$salePrice = array();
$owner = array();
$size = array();
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
//procure batchIDs from 'batches'
$query = "select batchID, owner from batches where startDate='{$this->form->startdate} 00:00:00';";
if (FormLib::get('dept') == 2) {
$query = "select batchID, owner from batches where startDate='{$this->form->startdate} 00:00:00' and (owner='Bulk' or owner='BULK');";
}
if (FormLib::get('dept') == 3) {
$query = "select batchID, owner from batches where startDate='{$this->form->startdate} 00:00:00' and (owner='Cool' or owner='COOL');";
}
if (FormLib::get('dept') == 4) {
$query = "select batchID, owner from batches where startDate='{$this->form->startdate} 00:00:00' and (owner='Grocery' or owner='GROCERY');";
}
if (FormLib::get('dept') == 5) {
$query = "select batchID, owner from batches where startDate='{$this->form->startdate} 00:00:00' and (owner='HBC');";
}
$result = $dbc->query($query);
while ($row = $dbc->fetch_row($result)) {
$batchID[] = $row['batchID'];
$owner[] = $row['owner'];
}
echo count($batchID) . " batches found\n";
//procure upcs from 'batchList' --this is going to pull every upc of every item that is going on sale
for ($i = 0; $i < count($batchID); $i++) {
$query = "SELECT upc, salePrice \n FROM batchList where batchID='{$batchID[$i]}';\n ";
$result = $dbc->query($query);
while ($row = $dbc->fetch_row($result)) {
$upc[] = $row['upc'];
$salePrice[] = $row['salePrice'];
}
}
echo count($upc) . " items found for this sales period <br>";
//procure description of items based on 'upc's, and return their descriptions, organized by department and brand
for ($i = 0; $i < count($upc); $i++) {
$query = "SELECT p.upc, u.brand, u.description, v.size from products as p\n LEFT JOIN productUser as u ON p.upc=u.upc\n LEFT JOIN vendorItems as v ON v.upc=p.upc\n WHERE p.upc = '{$upc[$i]}' order by 'brand';";
$result = $dbc->query($query);
while ($row = $dbc->fetch_row($result)) {
$item[$i][0] = $row['brand'];
$item[$i][1] = $row['description'];
$item[$i][2] = $row['size'];
$item[$i][3] = $salePrice[$i];
$item[$i][4] = $row['upc'];
}
}
sort($item);
return $item;
}
private function writeICal($id, $filename)
{
global $FANNIE_OP_DB;
$dbc = CalendarPluginDB::get();
$cal = new CalendarsModel($dbc);
$cal->calendarID($id);
$cal->load();
$query = '
SELECT m.eventID,
m.eventDate,
m.eventText,
m.uid,
u.real_name,
u.name
FROM monthview_events AS m
LEFT JOIN ' . $FANNIE_OP_DB . $dbc->sep() . 'Users AS u ON m.uid=u.uid
WHERE m.calendarID = ?';
if (FormLib::get('export') != 1) {
$query .= ' AND m.eventDate >= ' . $dbc->curdate();
}
$query .= ' ORDER BY eventDate DESC';
$prep = $dbc->prepare($query);
$res = $dbc->execute($prep, array($id));
$fp = fopen($filename, 'w');
fwrite($fp, "BEGIN:VCALENDAR\r\n");
fwrite($fp, "VERSION:2.0\r\n");
fwrite($fp, "PRODID:-//FannieCalendarPlugin//NONSGML v1.0//EN\r\n");
fwrite($fp, "X-WR-CALNAME:" . $cal->name() . "\r\n");
fwrite($fp, "CALSCALE:GREGORIAN\r\n");
$now = gmdate('Ymd') . 'T' . gmdate('His') . 'Z';
while ($row = $dbc->fetch_row($res)) {
$times = $this->getTime($row['eventText']);
$date_stem = date('Y-m-d', strtotime($row['eventDate']));
fwrite($fp, "BEGIN:VEVENT\r\n");
fwrite($fp, "UID:" . sha1($row['eventID']) . '@' . $_SERVER['HTTP_HOST'] . "\r\n");
if ($times && strtotime($date_stem . ' ' . $times['start'] . ':00') && strtotime($date_stem . ' ' . $times['end'] . ':00')) {
$startTime = strtotime($date_stem . ' ' . $times['start'] . ':00');
$endTime = strtotime($date_stem . ' ' . $times['end'] . ':00');
fwrite($fp, "DTSTART:" . gmdate('Ymd\\THis\\Z', $startTime) . "\r\n");
fwrite($fp, "DTEND:" . gmdate('Ymd\\THis\\Z', $endTime) . "\r\n");
} else {
fwrite($fp, "DTSTART;VALUE=DATE:" . date('Ymd', strtotime($row['eventDate'])) . "\r\n");
}
fwrite($fp, "DTSTAMP:" . $now . "\r\n");
$row['eventText'] = $this->br2nl($row['eventText']);
fwrite($fp, "DESCRIPTION:" . $this->escapeString($row['eventText']) . "\r\n");
$summary = explode("\n", $row['eventText'], 2);
fwrite($fp, "SUMMARY:" . $this->escapeString($summary[0]) . "\r\n");
fwrite($fp, "ORGANIZER;CN=" . $row['real_name'] . ":" . $row['name'] . '@' . $_SERVER['HTTP_HOST'] . "\r\n");
fwrite($fp, "LAST-MODIFIED:" . $now . "\r\n");
fwrite($fp, "END:VEVENT\r\n");
}
fwrite($fp, "END:VCALENDAR\r\n");
}
public function post_id_handler()
{
$dbc = FannieDB::get($this->config->get('TRANS_DB'));
$qty = FormLib::get('qty', array());
$reason = FormLib::get('reason', array());
$loss = FormLib::get('loss', array());
$successes = 0;
for ($i = 0; $i < count($this->id); $i++) {
$args = array();
$json = json_decode(base64_decode($this->id[$i]), true);
$query = 'UPDATE dtransactions SET ';
if (isset($qty[$i])) {
$args[] = $qty[$i];
$args[] = $qty[$i];
$args[] = $qty[$i];
$query .= ' quantity=?, ItemQtty=?, total=unitPrice*?, ';
}
if (isset($reason[$i])) {
$args[] = $reason[$i];
$query .= ' numflag=?, ';
}
if (isset($loss[$i])) {
$args[] = $loss[$i];
$query .= ' charflag=?, ';
}
if (count($args) == 0) {
$this->addOnloadCommand("showBootstrapAlert('#alert-area', 'danger', 'Invalid data for: " . json_encode($json) . "');\n");
continue;
}
// trim trailing space and comma
$query = substr($query, 0, strlen($query) - 2);
$query .= '
WHERE ' . $dbc->datediff('datetime', $dbc->now()) . ' = 0
AND emp_no=?
AND register_no=?
AND trans_no=?
AND trans_id=?
AND store_id=?';
$args[] = $json['emp_no'];
$args[] = $json['register_no'];
$args[] = $json['trans_no'];
$args[] = $json['trans_id'];
$args[] = $json['store_id'];
$prep = $dbc->prepare($query);
$res = $dbc->execute($prep, $args);
if ($res) {
$successes++;
}
}
if ($successes > 0) {
$this->addOnloadCommand("showBootstrapAlert('#alert-area', 'success', 'Updated Entries');\n");
}
return true;
}
public function fetch_report_data()
{
$dbc = $this->connection;
$dbc->selectDB($this->config->get('OP_DB'));
$date1 = $this->form->date1;
$date2 = $this->form->date2;
$deptStart = FormLib::get('deptStart');
$deptEnd = FormLib::get('deptEnd');
$deptMulti = FormLib::get('departments', array());
$buyer = FormLib::get('buyer', '');
// args/parameters differ with super
// vs regular department
$args = array($date1 . ' 00:00:00', $date2 . ' 23:59:59');
$where = ' 1=1 ';
if ($buyer !== '') {
if ($buyer == -2) {
$where .= ' AND s.superID != 0 ';
} elseif ($buyer != -1) {
$where .= ' AND s.superID=? ';
$args[] = $buyer;
}
}
if ($buyer != -1) {
list($conditional, $args) = DTrans::departmentClause($deptStart, $deptEnd, $deptMulti, $args);
$where .= $conditional;
}
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$query = "\n SELECT d.upc,\n p.brand,\n p.description," . DTrans::sumQuantity('d') . " AS qty,\n CASE WHEN memDiscount <> 0 AND memType <> 0 THEN unitPrice - memDiscount ELSE unitPrice END as price,\n d.department, \n t.dept_name, \n SUM(total) AS total\n FROM {$dlog} AS d " . DTrans::joinProducts('d', 'p', 'inner') . DTrans::joinDepartments('d', 't');
// join only needed with specific buyer
if ($buyer !== '' && $buyer > -1) {
$query .= 'LEFT JOIN superdepts AS s ON d.department=s.dept_ID ';
} elseif ($buyer !== '' && $buyer == -2) {
$query .= 'LEFT JOIN MasterSuperDepts AS s ON d.department=s.dept_ID ';
}
$query .= "\n WHERE tdate BETWEEN ? AND ?\n AND {$where}\n GROUP BY d.upc,p.description,price,d.department,t.dept_name\n ORDER BY d.upc";
$prep = $dbc->prepare_statement($query);
$result = $dbc->exec_statement($query, $args);
$data = array();
while ($row = $dbc->fetch_row($result)) {
$record = array($row['upc'], $row['brand'], $row['description'], $row['department'], $row['dept_name'], sprintf('%.2f', $row['price']), sprintf('%.2f', $row['qty']), sprintf('%.2f', $row['total']));
$data[] = $record;
}
// bold items that sold at multiple prices
for ($i = 0; $i < count($data); $i++) {
if (!isset($data[$i + 1])) {
continue;
}
if ($data[$i][0] == $data[$i + 1][0]) {
$data[$i]['meta'] = FannieReportPage::META_BOLD;
$data[$i + 1]['meta'] = FannieReportPage::META_BOLD;
}
}
return $data;
}
function preprocess()
{
if (FormLib::get('selectlist', false) !== false) {
if (FormLib::get('selectlist', false) != '') {
CoreLocal::set('strRemembered', FormLib::get('selectlist'));
CoreLocal::set('msgrepeat', 1);
}
$this->change_page($this->page_url . "gui-modules/pos2.php");
return false;
}
return true;
}
function preprocess()
{
global $FANNIE_OP_DB;
$this->title = 'Fannie - Product Flag Maintenance';
$this->header = 'Product Flag Maintenance';
$this->msgs = array();
$db = FannieDB::get($FANNIE_OP_DB);
if (FormLib::get_form_value('addBtn') !== '') {
$desc = FormLib::get_form_value('new');
if (empty($desc)) {
$this->msgs[] = 'Error: no new description given';
} else {
$bit = 1;
$bit_number = 1;
$chkP = $db->prepare_statement("SELECT bit_number FROM prodFlags WHERE bit_number=?");
for ($i = 0; $i < 30; $i++) {
$chkR = $db->exec_statement($chkP, array($bit_number));
if ($db->num_rows($chkR) == 0) {
break;
}
$bit *= 2;
$bit_number++;
}
if ($bit > 1 << 30) {
$this->msgs[] = 'Error: can\'t add more flags';
} else {
$insP = $db->prepare_statement("INSERT INTO prodFlags \n (bit_number, description) VALUES (?,?)");
$db->exec_statement($insP, array($bit_number, $desc));
}
}
} elseif (FormLib::get_form_value('updateBtn') !== '') {
$ids = FormLib::get_form_value('mask', array());
$descs = FormLib::get_form_value('desc', array());
$active = FormLib::get('active', array());
$upP = $db->prepare_statement("\n UPDATE prodFlags \n SET description=?,\n active=?\n WHERE bit_number=?");
for ($i = 0; $i < count($ids); $i++) {
if (isset($descs[$i]) && !empty($descs[$i])) {
$a = in_array($ids[$i], $active) ? 1 : 0;
$db->exec_statement($upP, array($descs[$i], $a, $ids[$i]));
}
}
} elseif (FormLib::get_form_value('delBtn') !== '') {
$ids = FormLib::get_form_value('del', array());
$delP = $db->prepare_statement("DELETE FROM prodFlags WHERE bit_number=?");
foreach ($ids as $id) {
$db->exec_statement($delP, array($id));
}
}
for ($i = 1; $i <= count($this->msgs); $i++) {
$db->logger($this->msgs[$i - 1]);
}
return True;
}
public function post_id_handler()
{
global $FANNIE_OP_DB;
$dbc = FannieDB::get($FANNIE_OP_DB);
$scale = new ServiceScalesModel($dbc);
if (!is_array($this->id)) {
echo _('Error: invalid input');
return false;
}
$descriptions = FormLib::get('description', array());
$hosts = FormLib::get('host', array());
$types = FormLib::get('type', array());
$dept = FormLib::get('scaleDept', array());
$super = FormLib::get('super', array());
$ep_st = FormLib::get('store-no', array());
$ep_dept = FormLib::get('dept-no', array());
$ep_addr = FormLib::get('address-no', array());
for ($i = 0; $i < count($this->id); $i++) {
$scale->reset();
$scale->serviceScaleID($this->id[$i]);
if (isset($descriptions[$i])) {
$scale->description($descriptions[$i]);
}
if (isset($hosts[$i])) {
$scale->host($hosts[$i]);
}
if (isset($types[$i])) {
$scale->scaleType($types[$i]);
}
if (isset($dept[$i])) {
$scale->scaleDeptName($dept[$i]);
}
if (isset($super[$i])) {
if ($super[$i] === '') {
$super[$i] = null;
}
$scale->superID($super[$i]);
}
if (isset($ep_st[$i])) {
$scale->epStoreNo($ep_st[$i]);
}
if (isset($ep_dept[$i])) {
$scale->epDeptNo($ep_dept[$i]);
}
if (isset($ep_addr[$i])) {
$scale->epScaleAddress($ep_addr[$i]);
}
$scale->save();
}
header('Location: ScaleEditor.php');
return false;
}
protected function post_id_handler()
{
$model = new VendorSpecificMarginsModel($this->connection);
$vendor = FormLib::get('vendorID');
$margins = FormLib::get('margin');
for ($i = 0; $i < count($this->id); $i++) {
$model->deptID($this->id[$i]);
$model->vendorID($vendor);
$model->margin(isset($margins[$i]) ? $margins[$i] / 100.0 : 0);
$model->save();
}
return '?id=' . $vendor;
}
