fannie/classlib2.0/data/DTrans.php
<?php
/*******************************************************************************
Copyright 2013 Whole Foods Co-op
This file is part of CORE-POS.
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
*********************************************************************************/
/**
@class DTrans
Helper class for generating useful bits of
transaction SQL
*/
class DTrans
{
/**
Array of default values for dtransaction-style tables
The column 'datetime' is omitted. Normally an SQL
function like NOW() is used there and cannot be
a parameter
*/
public static function defaults()
{
$ret = self::$DEFAULTS;
$ret['store_id'] = (int)FannieConfig::config('STORE_ID');
return $ret;
}
private static $DEFAULTS = array(
'register_no'=>0,
'emp_no'=>0,
'trans_no'=>0,
'upc'=>'0',
'description'=>'',
'trans_type'=>'',
'trans_subtype'=>'',
'trans_status'=>'',
'department'=>0,
'quantity'=>0,
'scale'=>0,
'cost'=>0,
'unitPrice'=>0,
'total'=>0,
'regPrice'=>0,
'tax'=>0,
'foodstamp'=>0,
'discount'=>0,
'memDiscount'=>0,
'discountable'=>0,
'discounttype'=>0,
'voided'=>0,
'percentDiscount'=>0,
'ItemQtty'=>0,
'volDiscType'=>0,
'volume'=>0,
'VolSpecial'=>0,
'mixMatch'=>'',
'matched'=>0,
'memType'=>0,
'staff'=>0,
'numflag'=>0,
'charflag'=>'',
'card_no'=>0,
'trans_id'=>0
);
/**
Turn an key=>value array into useful SQL bits
@param $arr array of column_name => column_value
@param $datecol [optional] name of datetime column
@param $datefunc [optional] string database function for current datetime
@return keyed array
- columnString => comma separated list of columns
- valueString => comma separated list of ? placeholders
- arguments => array of query parameters
*/
public static function parameterize($arr, $datecol='', $datefunc='')
{
$columns = !empty($datecol) && !empty($datefunc) ? $datecol.',' : '';
$values = !empty($datecol) && !empty($datefunc) ? $datefunc.',' : '';
$args = array();
$defaults = self::defaults();
foreach($arr as $key => $val) {
// validate column names
if (!isset($defaults[$key])) {
continue;
}
$columns .= $key.',';
$values .= '?,';
$args[] = $val;
}
$columns = substr($columns,0,strlen($columns)-1);
$values = substr($values,0,strlen($values)-1);
return array(
'columnString' => $columns,
'valueString' => $values,
'arguments' => $args
);
}
/**
Get SQL condition to select testing transactions
@param $prefix [optional] table alias
@return string SQL snippet
*/
public static function isTesting($prefix='')
{
if (!empty($prefix)) {
$prefix = $prefix . '.';
}
return ' (' . $prefix . 'register_no = 99 OR ' . $prefix . 'emp_no = 9999) ';
}
/**
Get SQL condition to select non-testing transactions
@param $prefix [optional] table alias
@return string SQL snippet
*/
public static function isNotTesting($prefix='')
{
if (!empty($prefix)) {
$prefix = $prefix . '.';
}
return ' (' . $prefix . 'register_no <> 99 AND ' . $prefix . 'emp_no <> 9999)' ;
}
/**
Get SQL condition to select canceled transactions
@param $prefix [optional] table alias
@return string SQL snippet
*/
public static function isCanceled($prefix='')
{
if (!empty($prefix)) {
$prefix = $prefix . '.';
}
return ' (' . $prefix . "trans_status IN ('X', 'Z')) ";
}
/**
Get SQL condition to select valid transactions
This is essentially the opposite of "isCanceled" but
excludes some additional informational rows that
provide commentary but do not impact numeric totals
@param $prefix [optional] table alias
@return string SQL snippet
*/
public static function isValid($prefix='')
{
if (!empty($prefix)) {
$prefix = $prefix . '.';
}
return ' (' . $prefix . "trans_status NOT IN ('D', 'X', 'Z')) ";
}
/**
Get SQL condition to select transactions with
the given store ID. Store ID must be passed to the
resulting prepared statement as an argument
*/
public static function isStoreID($store_id, $prefix='')
{
if (!empty($prefix)) {
$prefix = $prefix . '.';
}
if ($store_id == 0) {
return ' (0 = ?) ';
} else {
return ' (' . $prefix . 'store_id = ?) ';
}
}
/**
Get standard quantity sum. Member-discount line items
are excluded and quasi-scalabe items with a unitPrice
of a penny are counted as one instead of whatever value
is in the quantity field.
@param $prefix [optional] table alias
@param $scaleAsEaches [optional] calculate service-scale items as
units instead of weights
@return string SQL snippet
*/
public static function sumQuantity($prefix='', $scaleAsEaches=false, $table='')
{
if (!empty($prefix)) {
$prefix = $prefix . '.';
}
if (strstr($table, 'sumRingSalesByDay')) {
return "SUM({$prefix}quantity)";
}
return ' SUM(CASE '
. 'WHEN ' . $prefix . "trans_status = 'M' THEN 0 "
. 'WHEN ' . $prefix . "trans_subtype = 'OG' THEN 0 "
. 'WHEN ' . $prefix . "unitPrice = 0.01 THEN 1 "
. ($scaleAsEaches ? "WHEN {$prefix}upc LIKE '002%' AND {$prefix}quantity >= 0 THEN 1 " : '')
. ($scaleAsEaches ? "WHEN {$prefix}upc LIKE '002%' AND {$prefix}quantity < 0 THEN -1 " : '')
. 'ELSE ' . $prefix . 'quantity '
. 'END) ';
}
/**
Get join statement for products table
@param $dlog_alias [optional] alias for the transaction table (default 't')
@param $product_alias [optional] alias for the products table (default 'p')
@return string SQL snippet
*/
public static function joinProducts($dlog_alias='t', $product_alias='p', $join_type='left')
{
$conf = FannieConfig::factory();
$store_id = $conf->get('STORE_ID');
$store_condition = '';
if ($conf->get('STORE_MODE') == 'HQ') {
$store_condition = ' AND ' . $product_alias . '.store_id=' . ((int)$store_id);
}
return ' ' . self::normalizeJoin($join_type) . ' JOIN ' . FannieDB::fqn('products', 'op')
. ' AS ' . $product_alias
. ' ON ' . $product_alias . '.upc = ' . $dlog_alias . '.upc ' . $store_condition;
}
private static function normalizeJoin($join_type)
{
switch (strtoupper($join_type)) {
case 'RIGHT':
return 'RIGHT';
case 'INNER':
return 'INNER';
default:
case 'LEFT':
return 'LEFT';
}
}
/**
Get join statement for departments table
@param $dlog_alias [optional] alias for the transaction table (default 't')
@param $dept_alias [optional] alias for the departments table (default 'd')
@return string SQL snippet
*/
public static function joinDepartments($dlog_alias='t', $dept_alias='d')
{
return ' LEFT JOIN ' . FannieDB::fqn('departments', 'op') . ' AS ' . $dept_alias
. ' ON ' . $dept_alias . '.dept_no = ' . $dlog_alias . '.department ';
}
/**
Get join statement for custdata table
@param $dlog_alias [optional] alias for the transaction table (default 't')
@param $cust_alias [optional] alias for the custdata table (default 'c')
@return string SQL snippet
*/
public static function joinCustomerAccount($dlog_alias='t', $cust_alias='c')
{
return ' LEFT JOIN ' . FannieDB::fqn('custdata', 'op') . ' AS ' . $cust_alias
. ' ON ' . $cust_alias . '.CardNo = ' . $dlog_alias . '.card_no '
. ' AND ' . $cust_alias . '.personNum = 1 ';
}
/**
Get join statement for tenders table
@param $dlog_alias [optional] alias for the transaction table (default 't')
@param $tender_alias [optional] alias for the tenders table (default 'n')
@return string SQL snippet
*/
public static function joinTenders($dlog_alias='t', $tender_alias='n')
{
return ' LEFT JOIN ' . FannieDB::fqn('tenders', 'op') . ' AS ' . $tender_alias
. ' ON ' . $tender_alias . '.TenderCode = ' . $dlog_alias . '.trans_subtype ';
}
/**
Get an available dtransactions.trans_no value
@param $connection [SQLManager] database connection
@param $emp_no [int] employee number
@param $register_no [int] register number
@return [int] trans_no
*/
public static function getTransNo(SQLManager $connection, $emp_no=false, $register_no=false)
{
$config = FannieConfig::factory();
if ($emp_no === false) {
$emp_no = $config->get('EMP_NO');
}
if ($register_no === false) {
$register_no = $config->get('REGISTER_NO');
}
$prep = $connection->prepare('
SELECT MAX(trans_no) AS trans
FROM ' . $config->get('TRANS_DB') . $connection->sep() . 'dtransactions
WHERE emp_no=?
AND register_no=?');
$result = $connection->execute($prep, array($emp_no, $register_no));
if (!$result || $connection->num_rows($result) == 0) {
return 1;
} else {
$row = $connection->fetch_row($result);
if ($row['trans'] == '') {
return 1;
} else {
return $row['trans'] + 1;
}
}
}
/**
Add a transaction record directly to dtransactions on the backend
@param $connection [SQLManager] database connection
@param $trans_no [integer] transaction number (dtransactions.trans_no)
@param $params [array] of column_name => value
If emp_no and register_no values are not specified, the defaults
are the configuration settings FANNIE_EMP_NO and FANNIE_REGISTER_NO.
The following columns are always calculated by addItem() and values
set in $params will be ignored:
- datetime (always current)
- trans_id (assigned based on existing records)
Additionally, the following values are looked up if $params['card_no']
is specified:
- memType
- staff
*/
public static function addItem(SQLManager $connection, $trans_no, $params)
{
$config = FannieConfig::factory();
$model = new DTransactionsModel($connection);
$model->whichDB($config->get('TRANS_DB'));
$model->trans_no($trans_no);
$model->emp_no($config->get('EMP_NO'));
if (isset($params['emp_no'])) {
$model->emp_no($params['emp_no']);
}
$model->register_no($config->get('REGISTER_NO'));
if (isset($params['register_no'])) {
$model->register_no($params['register_no']);
}
$current_records = $model->find('trans_id', true);
if (count($current_records) == 0) {
$model->trans_id(1);
} else {
$last = $current_records[0];
$model->trans_id($last->trans_id() + 1);
}
$model->memType(0);
$model->staff(0);
if (isset($params['card_no'])) {
$account = \COREPOS\Fannie\API\member\MemberREST::get($params['card_no']);
if ($account) {
if (is_numeric($account['customerTypeID'])) {
$model->memType($account['customerTypeID']);
}
if (is_numeric($account['customers'][0]['staff'])) {
$model->staff($account['customers'][0]['staff']);
}
}
}
$defaults = self::defaults();
$skip = array('datetime', 'emp_no', 'register_no', 'trans_no', 'trans_id', 'memType');
foreach ($defaults as $name => $value) {
if (in_array($name, $skip)) {
continue;
}
if (isset($params[$name])) {
$model->$name($params[$name]);
} else {
$model->$name($value);
}
}
$model->datetime(date('Y-m-d H:i:s'));
if ($model->save()) {
return true;
} else {
return false;
}
}
/**
Add an open ring record to dtransactions on the backend
@param $connection [SQLManager] database connection
@param $department [integer] department number
$param $amount [number] ring amount
@param $trans_no [integer] transaction number (dtransactions.trans_no)
@param $params [array] of column_name => value
If emp_no and register_no values are not specified, the defaults
are the configuration settings FANNIE_EMP_NO and FANNIE_REGISTER_NO.
The following columns are automatically calculated based
on department number and amount:
- upc
- description
- trans_type
- trans_status
- unitPrice
- total
- regPrice
- quantity
- ItemQtty
Negative amounts result in a refund trans_status
This method calls DTrans::addItem() so columns datetime and trans_id are
also automatically assigned.
*/
public static function addOpenRing(SQLManager $connection, $department, $amount, $trans_no, $params=array())
{
$config = FannieConfig::factory();
$model = new DepartmentsModel($connection);
$model->whichDB($config->get('OP_DB'));
$model->dept_no($department);
$model->load();
$params['trans_type'] = 'D';
$params['department'] = $department;
$params['unitPrice'] = $amount;
$params['total'] = $amount;
$params['regPrice'] = $amount;
$params['quantity'] = 1;
$params['ItemQtty'] = 1;
if ($amount < 0) {
$params['quantity'] = -1;
$params['trans_status'] = 'R';
}
$params['description'] = $model->dept_name();
$params['upc'] = abs($amount) . 'DP' . $department;
return self::addItem($connection, $trans_no, $params);
}
public static function departmentClause($deptStart, $deptEnd, $deptMulti, $args, $alias='d')
{
if (count($deptMulti) > 0) {
$where = ' AND ' . $alias . '.department IN (';
foreach ($deptMulti as $d) {
$where .= '?,';
$args[] = $d;
}
$where = substr($where, 0, strlen($where)-1) . ')';
} else {
$where = ' AND ' . $alias . '.department BETWEEN ? AND ? ';
$args[] = $deptStart;
$args[] = $deptEnd;
}
return array($where, $args);
}
public static function getView($date1, $date2)
{
$dlog = DTransactionsModel::selectDlog($date1, $date2);
$config = FannieConfig::config('PLUGIN_LIST');
if (in_array('CoreWarehouse', $config) && substr($dlog, -5) != '.dlog' && substr($dlog, -7) != '.dlog_15') {
return FannieDB::fqn('sumRingSalesByDay', 'plugin:WarehouseDatabase');
}
return $dlog;
}
public static function dateBetween($table, $date1, $date2)
{
$ts1 = strtotime($date1);
$ts2 = strtotime($date2);
if ($ts1 === false) {
$ts1 = time();
}
if ($ts2 === false) {
$ts2 = time();
}
if (strstr($table, 'sumRingSalesByDay')) {
return array(' date_id BETWEEN ? AND ? ', array(date('Ymd',$ts1), date('Ymd', $ts2)));
}
$args = array(
date('Y-m-d 00:00:00', $ts1),
date('Y-m-d 23:59:59', $ts2),
);
return array(' tdate BETWEEN ? AND ? ', $args);
}
public static function extractYMD($table)
{
if (strstr($table, 'sumRingSalesByDay')) {
return '
SUBSTRING(date_id, 1, 4),
SUBSTRING(date_id, 5, 2),
SUBSTRING(date_id, 7, 2)';
}
return '
YEAR(tdate),
MONTH(tdate),
DAY(tdate)';
}
public static function memTypeIgnore($dbc)
{
$prep = $dbc->prepare("SELECT memtype FROM " . FannieDB::fqn('memtype', 'op') . " WHERE ignoreSales=1");
$ignores = $dbc->getAllValues($prep, array());
if ($ignores === false || count($ignores) == 0) {
return '(-9999)';
}
$ignores = array_map(function ($i) { return (int)$i; }, $ignores);
return '(' . implode(',', $ignores) . ')';
}
}