pos/is4c-nf/lib/Database.php
<?php
/*******************************************************************************
Copyright 2012 Whole Foods Co-op
This file is part of IT CORE.
IT CORE 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.
IT CORE 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
*********************************************************************************/
namespace COREPOS\pos\lib;
use COREPOS\pos\lib\LocalStorage\LaneCache;
use COREPOS\pos\lib\MiscLib;
use \CoreLocal;
use \Exception;
/* --COMMENTS - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
* 3Feb2015 Eric Lee New function logger(), anticipate change to uploadCC().
* 27Feb2013 Andy Theuninck singleton connection for local databases
* 13Jan2013 Eric Lee Added changeLttTaxCode(From, To);
*/
/**
@class Database
Functions related to the database
*/
class Database
{
/***********************************************************************************************
Functions transcribed from connect.asp on 07.13.03 by Brandon.
***********************************************************************************************/
/**
Singleton connection
*/
static private $SQL_CONNECTION = null;
/**
Connect to the transaction database (local)
@return a SQLManager object
*/
static public function tDataConnect()
{
return self::getLocalConnection(CoreLocal::get('tDatabase'), CoreLocal::get('pDatabase'));
}
/**
Connect to the operational database (local)
@return a SQLManager object
*/
static public function pDataConnect()
{
return self::getLocalConnection(CoreLocal::get('pDatabase'), CoreLocal::get('tDatabase'));
}
static private function getLocalConnection($database1, $database2)
{
if (self::$SQL_CONNECTION === null){
/**
Add both local databases to the connection object
*/
try {
self::$SQL_CONNECTION = new \COREPOS\pos\lib\SQLManager(
CoreLocal::get("localhost"),
CoreLocal::get("DBMS"),
$database1,
CoreLocal::get("localUser"),
CoreLocal::get("localPass"),
false);
if (isset(self::$SQL_CONNECTION->connections[$database1])) {
self::$SQL_CONNECTION->connections[$database2] = self::$SQL_CONNECTION->connections[$database1];
if (CoreLocal::get('CoreCharSet') != '') {
self::$SQL_CONNECTION->setCharSet(CoreLocal::get('CoreCharSet'), $database1);
}
}
} catch (Exception $ex) {
}
} else {
/**
Switch connection object to the requested database
*/
self::$SQL_CONNECTION->selectDB($database1);
}
return self::$SQL_CONNECTION;
}
/**
Connect to the remote server database
@return a SQLManager object
*/
static public function mDataConnect()
{
try {
$sql = new \COREPOS\pos\lib\SQLManager(CoreLocal::get("mServer"),CoreLocal::get("mDBMS"),CoreLocal::get("mDatabase"),
CoreLocal::get("mUser"),CoreLocal::get("mPass"),false,true);
if ($sql->isConnected(CoreLocal::get('mDatabase')) && CoreLocal::get('CoreCharSet') != '') {
$sql->setCharSet(CoreLocal::get('CoreCharSet'), CoreLocal::get('mDatabase'));
}
return $sql;
} catch (Exception $ex) {
}
return false;
}
/**
Get the name of the primary server database
This is only relevant in multi-store setups where the lane
is shipping data to a temporary holding database to be
relayed to the master HQ server later. Most operations can
work off this holding database but a few may need to reference
the some overarching, all-location data.
@return database name w/ . separator or an empty string if
no alternate is defined.
*/
static public function mAltName()
{
$ret = CoreLocal::get('mAlternative');
if ($ret) {
return $ret . '.';
}
return '';
}
// ----------getsubtotals()----------
// getsubtotals() updates the values held in our session variables.
/**
Load values from subtotals view into session.
Essentially refreshes totals in the session.
*/
static public function getsubtotals()
{
$query = "SELECT * FROM subtotals";
$connection = self::tDataConnect();
$result = $connection->query($query);
$row = $connection->fetchRow($result);
// reset a few variables
if (!$row || $row["LastID"] == 0) {
CoreLocal::set("ttlflag",0);
CoreLocal::set("fntlflag",0);
self::setglobalflags(0);
}
// LastID => MAX(localtemptrans.trans_id) or zero table is empty
CoreLocal::set("LastID", (!$row || !isset($row['LastID'])) ? 0 : (double)$row["LastID"] );
// card_no => MAX(localtemptrans.card_no)
$cardno = (!$row || !isset($row['card_no'])) ? "0" : trim($row["card_no"]);
if ($cardno != "0" || CoreLocal::get("memberID") == "") {
CoreLocal::set("memberID",$cardno);
}
// runningTotal => SUM(localtemptrans.total)
CoreLocal::set("runningTotal", (!$row || !isset($row['runningTotal'])) ? 0 : (double)$row["runningTotal"] );
// discountTTL => SUM(localtemptrans.total) where discounttype=1
// probably not necessary
CoreLocal::set("discounttotal", (!$row || !isset($row['discountTTL'])) ? 0 : (double)$row["discountTTL"] );
// tenderTotal => SUM(localtemptrans.total) where trans_type=T
CoreLocal::set("tenderTotal", (!$row || !isset($row['tenderTotal'])) ? 0 : (double)$row["tenderTotal"] );
// memSpecial => SUM(localtemptrans.total) where discounttype=2,3
CoreLocal::set("memSpecial", (!$row || !isset($row['memSpecial'])) ? 0 : (double)$row["memSpecial"] );
// staffSpecial => SUM(localtemptrans.total) where discounttype=4
CoreLocal::set("staffSpecial", (!$row || !isset($row['staffSpecial'])) ? 0 : (double)$row["staffSpecial"] );
if (CoreLocal::get('member_subtotal') !== 0 && CoreLocal::get('member_subtotal') !== '0') {
// percentDiscount => MAX(localtemptrans.percentDiscount)
CoreLocal::set("percentDiscount", (!$row || !isset($row['percentDiscount'])) ? 0 : (double)$row["percentDiscount"] );
}
// transDiscount => lttsummary.discountableTTL * lttsummary.percentDiscount
CoreLocal::set("transDiscount", (!$row || !isset($row['transDiscount'])) ? 0 : (double)$row["transDiscount"] );
// foodstamp total net percentdiscount minus previous foodstamp tenders
CoreLocal::set("fsEligible", (!$row || !isset($row['fsEligible'])) ? 0 : (double)$row["fsEligible"] );
// chargeTotal => hardcoded to localtemptrans.trans_subtype MI or CX
CoreLocal::set("chargeTotal", (!$row || !isset($row['chargeTotal'])) ? 0 : (double)$row["chargeTotal"] );
// paymentTotal => hardcoded to localtemptrans.department = 990
CoreLocal::set("paymentTotal", (!$row || !isset($row['paymentTotal'])) ? 0 : (double)$row["paymentTotal"] );
CoreLocal::set("memChargeTotal", CoreLocal::get("chargeTotal") + CoreLocal::get("paymentTotal") );
// discountableTotal => SUM(localtemptrans.total) where discountable > 0
CoreLocal::set("discountableTotal", (!$row || !isset($row['discountableTotal'])) ? 0 : (double)$row["discountableTotal"] );
// voidTotal => SUM(localtemptrans.total) where trans_status=V
CoreLocal::set("voidTotal", (!$row || !isset($row['voidTotal'])) ? 0 : (double)$row["voidTotal"] );
/**
9May14 Andy
I belive this query is equivalent to the
old subtotals => lttsubtotals => lttsummary
I've omitted tax since those are already calculated
separately. A few conditions here should obviously
be more configurable, but first I want to get
rid of or simply the old nested views
fsEligible is the complicated one. That's:
1. Total foodstampable items
2. Minus transaction-level discount on those items
3. Minus any foodstamp tenders already applied.
localtemptrans.total is negative on tenders
so the query uses an addition sign but in
effect it's subracting.
$replacementQ = "
SELECT
CASE WHEN MAX(trans_id) IS NULL THEN 0 ELSE MAX(trans_id) END AS LastID,
MAX(card_no) AS card_no,
SUM(total) AS runningTotal,
SUM(CASE WHEN discounttype=1 THEN total ELSE 0 END) AS discountTTL,
SUM(CASE WHEN discounttype IN (2,3) THEN total ELSE 0 END) AS staffSpecial,
SUM(CASE WHEN discounttype=4 THEN total ELSE 0 END) AS discountTTL,
SUM(CASE WHEN trans_type='T' THEN total ELSE 0 END) AS tenderTotal,
MAX(percentDiscount) AS percentDiscount,
SUM(CASE WHEN discountable=0 THEN 0 ELSE total END) as discountableTotal,
SUM(CASE WHEN discountable=0 THEN 0 ELSE total END) * (MAX(percentDiscount)/100.00) AS transDiscount,
SUM(CASE WHEN trans_subtype IN ('MI', 'CX') THEN total ELSE 0 END) AS chargeTotal,
SUM(CASE WHEN department=990 THEN total ELSE 0 END) as paymentTotal,
SUM(CASE WHEN trans_status='V' THEN total ELSE 0 END) as voidTotal,
(
SUM(CASE WHEN foodstamp=1 THEN total ELSE 0 END)
-
((MAX(percentDiscount)/100.00)
* SUM(CASE WHEN foodstamp=1 AND discountable=1 THEN total ELSE 0 END))
+
SUM(CASE WHEN trans_subtype IN ('EF','FS') THEN total ELSE 0 END)
) AS fsEligble
FROM localtemptrans AS l
WHERE trans_type <> 'L'
";
*/
/* ENABLED LIVE 15Aug2013
Calculate taxes & exemptions separately from
the subtotals view.
Adding the exemption amount back on is a bit
silly but the goal for the moment is to keep
this function behaving the same. Once the subtotals
view is deprecated we can revisit how these two
session variables should behave.
*/
$taxes = self::lineItemTaxes();
$taxTTL = 0.00;
$exemptTTL = 0.00;
foreach($taxes as $tax) {
$taxTTL += $tax['amount'];
$exemptTTL += $tax['exempt'];
}
CoreLocal::set('taxTotal', number_format($taxTTL,2));
CoreLocal::set('fsTaxExempt', number_format(-1*$exemptTTL,2));
if (CoreLocal::get("TaxExempt") == 1) {
CoreLocal::set("taxable",0);
CoreLocal::set("taxTotal",0);
CoreLocal::set("fsTaxable",0);
CoreLocal::set("fsTaxExempt",0);
}
CoreLocal::set("subtotal",number_format(CoreLocal::get("runningTotal") - CoreLocal::get("transDiscount"), 2));
/* using a string for amtdue behaves strangely for
* values > 1000, so use floating point */
CoreLocal::set("amtdue",(double)round(CoreLocal::get("runningTotal") - CoreLocal::get("transDiscount") + CoreLocal::get("taxTotal"), 2));
/**
If FS eligible amount is greater than the current transaction total
and total is positive, limit the eligible amount to the current total.
This may not be technically correct but the resulting change causes a lot
of headaches depending what kind of change is allowed for earlier tenders,
if change is allowed for those tenders at all.
The other case is a refund to FS. Over-tendering on a refund doesn't make
any sense.
*/
if (CoreLocal::get("fsEligible") > CoreLocal::get("subtotal") && CoreLocal::get('subtotal') >= -0.005) {
CoreLocal::set("fsEligible",CoreLocal::get("subtotal"));
} elseif (CoreLocal::get("fsEligible") < CoreLocal::get("subtotal") && CoreLocal::get('subtotal') < 0) {
CoreLocal::set("fsEligible",CoreLocal::get("subtotal"));
}
}
/**
Calculate taxes using new-style taxView.
@return an array of records each containing:
- id
- description
- amount (taxes actually due)
- exempt (taxes exempted because of foodstamps)
There will always be one record for each existing tax rate.
*/
static public function lineItemTaxes()
{
$dbc = self::tDataConnect();
$taxQ = "SELECT id, description, taxTotal, fsTaxable, fsTaxTotal, foodstampTender, taxrate
FROM taxView ORDER BY taxrate DESC";
$taxR = $dbc->query($taxQ);
$taxRows = array();
$fsTenderTTL = 0.00;
while ($row = $dbc->fetch_row($taxR)) {
$row['fsExempt'] = 0.00;
$taxRows[] = $row;
$fsTenderTTL = $row['foodstampTender'];
}
// loop through line items and deal with
// foodstamp tax exemptions
for($i=0;$i<count($taxRows);$i++) {
if (abs($fsTenderTTL) <= 0.005) {
continue;
}
if (abs($fsTenderTTL - $taxRows[$i]['fsTaxable']) < 0.005) {
// CASE 1:
// Available foodstamp tender matches foodstamp taxable total
// Decrement line item tax by foodstamp tax total
// No FS tender left, so exemption ends
$taxRows[$i]['taxTotal'] = MiscLib::truncate2($taxRows[$i]['taxTotal'] - $taxRows[$i]['fsTaxTotal']);
$taxRows[$i]['fsExempt'] = $taxRows[$i]['fsTaxTotal'];
$fsTenderTTL = 0;
} elseif ($fsTenderTTL > $taxRows[$i]['fsTaxable']){
// CASE 2:
// Available foodstamp tender exeeds foodstamp taxable total
// Decrement line item tax by foodstamp tax total
// Decrement foodstamp tender total to reflect amount not yet applied
$taxRows[$i]['taxTotal'] = MiscLib::truncate2($taxRows[$i]['taxTotal'] - $taxRows[$i]['fsTaxTotal']);
$taxRows[$i]['fsExempt'] = $taxRows[$i]['fsTaxTotal'];
$fsTenderTTL = MiscLib::truncate2($fsTenderTTL - $taxRows[$i]['fsTaxable']);;
} else {
// CASE 3:
// Available foodstamp tender is less than foodstamp taxable total
// Decrement line item tax proprotionally to foodstamp tender available
// No FS tender left, so exemption ends
$percentageApplied = $fsTenderTTL / $taxRows[$i]['fsTaxable'];
$exemption = MiscLib::truncate2($taxRows[$i]['fsTaxTotal'] * $percentageApplied);
$taxRows[$i]['taxTotal'] = MiscLib::truncate2($taxRows[$i]['taxTotal'] - $exemption);
$taxRows[$i]['fsExempt'] = $exemption;
$fsTenderTTL = 0;
}
}
$ret = array();
foreach ($taxRows as $tr) {
$ret[] = array(
'rate_id' => $tr['id'],
'description' => $tr['description'],
'amount' => $tr['taxTotal'],
'exempt' => $tr['fsExempt'],
'rate' => $tr['taxrate'],
);
}
return $ret;
}
/**
Get the next transaction number for a given cashier
@param $cashierNo cashier number (emp_no in tables)
@return integer transaction number
*/
static public function gettransno($cashierNo)
{
$connection = self::tDataConnect();
$registerNo = CoreLocal::get("laneno");
$query = "SELECT max(trans_no) as maxtransno from localtranstoday where emp_no = "
.((int)$cashierNo)." and register_no = "
.((int)$registerNo).' AND datetime >= ' . $connection->curdate();
$result = $connection->query($query);
$row = $connection->fetchRow($result);
if (!$row || !$row["maxtransno"]) {
$transNo = 1;
// automatically trim the relevant table
// on some installs localtranstoday might be
// a view pointed at localtrans_today
$cleanQ = 'DELETE FROM localtranstoday WHERE datetime < ' . $connection->curdate();
if (CoreLocal::get('NoCompat') != 1 && $connection->isView('localtranstoday')) {
$cleanQ = str_replace('localtranstoday', 'localtrans_today', $cleanQ);
}
$connection->query($cleanQ);
} else {
$transNo = $row["maxtransno"] + 1;
}
return $transNo;
}
/**
See if the remote database is available
This function calls uploadtoServer() if
the initial test works.
@return integer
- 1 server available
- 0 server down
*/
static public function testremote()
{
$intConnected = MiscLib::pingport(CoreLocal::get("mServer"), CoreLocal::get("mDBMS"));
if ($intConnected == 1) {
self::uploadtoServer();
} else {
CoreLocal::set("standalone",1);
}
return (CoreLocal::get("standalone") + 1) % 2;
}
/**
Copy tables from the lane to the remote server
The following tables are copied:
- dtransactions
- suspended
- PaycardTransactions
- CapturedSignature
On success the local tables are truncated. The Paycards tables
are copied in the uploadCCdata() function but that gets called
automatically.
@return
- 1 upload succeeded
- 0 upload failed
*/
static private function uploadtoServer()
{
$uploaded = 0;
CoreLocal::set("standalone",1);
// new upload method makes use of SQLManager's transfer method
// to simulate cross-server queries
$connect = self::tDataConnect();
try {
$connect->addConnection(CoreLocal::get("mServer"),
CoreLocal::get("mDBMS"),
CoreLocal::get("mDatabase"),
CoreLocal::get("mUser"),
CoreLocal::get("mPass"),
False);
} catch (Exception $ex) {
$connect->connections[CoreLocal::get('mDatabase')] = false;
}
if (!isset($connect->connections[CoreLocal::get("mDatabase")]) ||
$connect->connections[CoreLocal::get("mDatabase")] === False){
CoreLocal::set("standalone",1);
return 0;
} elseif (CoreLocal::get('CoreCharSet') != '') {
$connect->setCharSet(CoreLocal::get('CoreCharSet'), CoreLocal::get('mDatabase'));
}
$dtMatches = self::getMatchingColumns($connect,"dtransactions");
if ($connect->transfer(CoreLocal::get("tDatabase"),
"select {$dtMatches} from dtransactions",
CoreLocal::get("mDatabase"),"insert into dtransactions ({$dtMatches})")) {
// Moved up
// DO NOT TRUNCATE; that resets AUTO_INCREMENT
$connect->query("DELETE FROM dtransactions",
CoreLocal::get("tDatabase"));
$suMatches = self::getMatchingColumns($connect,"suspended");
$suSuccess = $connect->transfer(CoreLocal::get("tDatabase"),
"select {$suMatches} from suspended",
CoreLocal::get("mDatabase"),
"insert into suspended ({$suMatches})");
if ($suSuccess) {
$connect->query("truncate table suspended",
CoreLocal::get("tDatabase"));
$uploaded = 1;
CoreLocal::set("standalone",0);
}
}
if (!self::uploadCCdata()) {
$uploaded = 0;
CoreLocal::set("standalone",1);
}
return $uploaded;
}
/**
Get a list of columns that exist on the local db
and the server db for the given table.
@param $connection a SQLManager object that's
already connected
@param $tableName the table
@param $table2 is provided, it match columns from
local.tableName against remote.table2
@return [string] comma separated list of column names
*/
// @hintable
static public function getMatchingColumns($connection,$tableName,$table2="")
{
/**
Cache column information by table in the session
In standalone mode, a transfer query likely failed
and the cache may be wrong so always requery in
that case.
*/
$cacheItem = LaneCache::get('MatchingColumnCache');
$cache = $cacheItem->get();
if (!is_array($cache)) {
$cache = array();
}
if (isset($cache[$tableName]) && CoreLocal::get('standalone') == 0) {
return $cache[$tableName];
}
$localPoll = $connection->tableDefinition($tableName,CoreLocal::get("tDatabase"));
if ($localPoll === false) {
return '';
}
$localCols = array();
foreach($localPoll as $name=>$v) {
$localCols[$name] = true;
}
$remotePoll = $connection->tableDefinition((!empty($table2)?$table2:$tableName),
CoreLocal::get("mDatabase"));
if ($remotePoll === false) {
return '';
}
$matchingCols = array();
foreach($remotePoll as $name=>$v) {
if (isset($localCols[$name])) {
$matchingCols[] = $name;
}
}
$ret = "";
foreach($matchingCols as $col) {
$ret .= $col.",";
}
$ret = rtrim($ret,",");
$cache[$tableName] = $ret;
$cacheItem->set($cache);
LaneCache::set($cacheItem);
return $ret;
}
/** Get a list of columns in both tables.
@param $connection a SQLManager object that's
already connected
@param $table1 a database table
@param $table2 a database table
@return [string] comma separated list of column names
*/
// @hintable
static public function localMatchingColumns($connection,$table1,$table2)
{
$poll1 = $connection->tableDefinition($table1);
$cols1 = array();
foreach($poll1 as $name=>$v) {
$cols1[$name] = true;
}
$poll2 = $connection->tableDefinition($table2);
$matchingCols = array();
foreach($poll2 as $name=>$v) {
if (isset($cols1[$name])) {
$matchingCols[] = $name;
}
}
$ret = "";
foreach($matchingCols as $col) {
$ret .= $col.",";
}
return rtrim($ret,",");
}
/**
Transfer credit card tables to the server.
See uploadtoServer().
@return boolean success / failure
*/
static private function uploadCCdata()
{
if (!in_array("Paycards",CoreLocal::get("PluginList"))) {
// plugin not enabled; nothing to upload
return true;
}
$sql = self::tDataConnect();
$sql->addConnection(CoreLocal::get("mServer"),
CoreLocal::get("mDBMS"),
CoreLocal::get("mDatabase"),
CoreLocal::get("mUser"),
CoreLocal::get("mPass"),
False);
if (CoreLocal::get('CoreCharSet') != '') {
$sql->setCharSet(CoreLocal::get('CoreCharSet'), CoreLocal::get('mDatabase'));
}
// test for success
$ret = true;
$tables = array('PaycardTransactions', 'CapturedSignature');
foreach ($tables as $table) {
if (CoreLocal::get('NoCompat') == 1 || $sql->tableExists($table)) {
$cols = self::getMatchingColumns($sql, $table);
$success = $sql->transfer(CoreLocal::get('tDatabase'),
"SELECT {$cols} FROM {$table}",
CoreLocal::get('mDatabase'),
"INSERT INTO {$table} ({$cols})"
);
if ($success) {
$sql->query('DELETE FROM ' . $table, CoreLocal::get('tDatabase'));
}
$ret = $ret & $success;
}
}
return $ret;
}
/**
Read globalvalues settings into session.
*/
static public function loadglobalvalues()
{
$query = "select CashierNo,Cashier,LoggedIn,TransNo,TTLFlag,
FntlFlag,TaxExempt from globalvalues";
$dbc = self::pDataConnect();
$result = $dbc->query($query);
$row = $dbc->fetchRow($result);
CoreLocal::set("CashierNo",$row["CashierNo"]);
CoreLocal::set("cashier",$row["Cashier"]);
CoreLocal::set("LoggedIn",$row["LoggedIn"]);
CoreLocal::set("transno",$row["TransNo"]);
CoreLocal::set("ttlflag",$row["TTLFlag"]);
CoreLocal::set("fntlflag",$row["FntlFlag"]);
CoreLocal::set("TaxExempt",$row["TaxExempt"]);
}
/**
Set new value in session.
@param $param keycode
@param $val new value
*/
static private function loadglobalvalue($param,$val)
{
switch (strtoupper($param)) {
case 'CASHIERNO':
CoreLocal::set("CashierNo",$val);
break;
case 'CASHIER':
CoreLocal::set("cashier",$val);
break;
case 'LOGGEDIN':
CoreLocal::set("LoggedIn",$val);
break;
case 'TRANSNO':
CoreLocal::set("transno",$val);
break;
case 'TTLFLAG':
CoreLocal::set("ttlflag",$val);
break;
case 'FNTLFLAG':
CoreLocal::set("fntlflag",$val);
break;
case 'TAXEXEMPT':
CoreLocal::set("TaxExempt",$val);
break;
}
}
/**
Update setting in globalvalues table.
@param $param keycode
@param $value new value
*/
static public function setglobalvalue($param, $value)
{
$dbc = self::pDataConnect();
if (!is_numeric($value)) {
$value = "'".$value."'";
}
$strUpdate = "update globalvalues set ".$param." = ".$value;
$dbc->query($strUpdate);
}
/**
Update many settings in globalvalues table
and in session
@param $arr An array of keys and values
*/
static public function setglobalvalues(array $arr)
{
$setStr = "";
foreach($arr as $param => $value) {
$setStr .= $param." = ";
$setStr .= !is_numeric($value) ? "'{$value}'," : $value . ',';
self::loadglobalvalue($param,$value);
}
$setStr = rtrim($setStr,",");
$dbc = self::pDataConnect();
$upQ = "UPDATE globalvalues SET ".$setStr;
$dbc->query($upQ);
}
/**
Sets TTLFlag and FntlFlag in globalvalues table
@param $value value for both fields.
*/
static public function setglobalflags($value)
{
$dbc = self::pDataConnect();
$dbc->query("update globalvalues set TTLFlag = ".$value.", FntlFlag = ".$value);
}
static private function getTaxByName($name)
{
$dbc = self::tDataConnect();
// Get the codes for the names provided.
$query = "SELECT id FROM taxrates WHERE description = '$name'";
$result = $dbc->query($query);
$row = $dbc->fetch_row($result);
if ($row) {
return $row['id'];
}
throw new Exception('name: >' . $name . '< not known.');
}
/**
Change one tax code in all items of localtemptrans to a different one.
Parameters are the names of the taxes, as in taxrates.description
@param $fromName The name of the tax changed from.
@param $fromName The name of the tax changed to.
*/
static public function changeLttTaxCode($fromName, $toName)
{
$pfx = "changeLttTaxCode ";
$pfx = "";
if ( $fromName == "" ) {
return "{$pfx}fromName is empty";
} elseif ( $toName == "" ) {
return "{$pfx}toName is empty";
}
$dbc = self::tDataConnect();
// Get the codes for the names provided.
try {
$fromId = self::getTaxByName($fromName);
$toId = self::getTaxByName($toName);
} catch (Exception $ex) {
return $pfx . $ex->getMessage();
}
// Change the values.
$query = "UPDATE localtemptrans set tax = $toId WHERE tax = $fromId";
$result = $dbc->query($query);
if ( !$result ) {
return "UPDATE false";
}
return true;
// changeLttTaxCode
}
/**
Rotate current transaction data
Current data in translog.localtemptrans is inserted into:
- translog.dtransactions
- translog.localtrans
- translog.localtranstoday (if not a view)
- translog.localtrans_today (if present)
@return [boolean] success or failure
Success or failure is based on whether or not
the insert into translog.dtransactions succeeds. That's
the most important query in terms of ensuring data
flows properly to the server.
*/
static public function rotateTempData()
{
$connection = Database::tDataConnect();
// LEGACY.
// these records should be written correctly from the start
// could go away with verification of above.
$connection->query("update localtemptrans set trans_type = 'T' where trans_subtype IN ('CP','IC')");
$connection->query("insert into localtrans select * from localtemptrans");
// localtranstoday converted from view to table
if (CoreLocal::get('NoCompat') == 1 || !$connection->isView('localtranstoday')) {
$connection->query("insert into localtranstoday select * from localtemptrans");
}
// legacy table when localtranstoday is still a view
if (CoreLocal::get('NoCompat') != 1 && $connection->table_exists('localtrans_today')) {
$connection->query("insert into localtrans_today select * from localtemptrans");
}
$cols = self::localMatchingColumns($connection, 'dtransactions', 'localtemptrans');
$ret = $connection->query("insert into dtransactions ($cols) select $cols from localtemptrans");
/**
If store_id column is present in lane dtransactions table
and the lane's store_id has been configured, assign that
value to the column. Otherwise it may be handled but some
other mechanism such as triggers or column default values.
*/
$tableDef = $connection->tableDefinition('dtransactions');
if (isset($tableDef['store_id']) && CoreLocal::get('store_id') !== '') {
$assignQ = sprintf('
UPDATE dtransactions
SET store_id = %d',
CoreLocal::get('store_id')
);
$connection->query($assignQ);
}
return ($ret) ? true : false;
}
/**
Truncate current transaction tables.
Clears data from:
- translog.localtemptrans
- translog.couponApplied
@return [boolean] success or failure
Success or failure is based on whether
translog.localtemptrans is cleared correctly.
*/
static public function clearTempTables()
{
$connection = Database::tDataConnect();
$query1 = "truncate table localtemptrans";
$ret = $connection->query($query1);
$query2 = "truncate table couponApplied";
$connection->query($query2);
return ($ret) ? true : false;
}
/**
Log a message to the lane log
@param $msg A string containing the message to log.
@return True on success, False on failure
*/
static public function logger($msg="")
{
$connection = self::tDataConnect();
$ret = false;
if (method_exists($connection, 'logger')) {
$ret = $connection->logger($msg);
}
return $ret;
}
static public function queueJob($job)
{
$jobs = CoreLocal::get('QueuedRedisJobs');
if (!is_array($jobs)) {
$jobs = array();
}
$jobs[] = $job;
CoreLocal::set('QueuedRedisJobs', $jobs);
}
static public function flushJobs($send=true)
{
$jobs = CoreLocal::get('QueuedRedisJobs');
CoreLocal::set('QueuedRedisJobs', array());
if (!is_array($jobs) || !$send) {
return;
}
foreach ($jobs as $job) {
self::addToRedis($job);
}
}
/**
* Push a job into Redis
* @param $job [array] with keys 'class' and 'data'
* @param $highPriority [boolean, default false]
* @return [boolean] success
*/
static private function addToRedis($job, $highPriority=false)
{
$host = CoreLocal::get('redisHost');
if ($host && class_exists('\\Predis\\Client')) {
try {
$redis = new \Predis\Client($host);
$queue = $highPriority ? 'jobHigh' : 'jobLow';
$redis->lpush($queue, json_encode($job));
return true;
} catch (Exception $ex) {
}
}
return false;
}
} // end Database class