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 run()
{
global $FANNIE_OP_DB, $FANNIE_TRANS_DB, $FANNIE_AR_DEPARTMENTS, $FANNIE_SERVER_DBMS;
$dbc = FannieDB::get($FANNIE_TRANS_DB);
$date = date('Y-m-t 23:59:59', mktime(0, 0, 0, date('n') - 1));
$cn = 'SELECT CardNo FROM ' . $FANNIE_OP_DB . $dbc->sep() . 'custdata
WHERE memType=4 and personNum=1';
$r = $dbc->query($cn);
$balQ = 'SELECT balance FROM ar_live_balance where card_no=?';
$balP = $dbc->prepare($balQ);
$tn = 1;
while ($w = $dbc->fetch_row($r)) {
$balR = $dbc->execute($balP, array($w['CardNo']));
if ($balW = $dbc->fetch_row($balR)) {
if ($balW[0] > 0) {
$record = DTrans::$DEFAULTS;
$datetime = date('\'Y-m-t 00:00:00\'', mktime(0, 0, 0, date('n') - 1));
$record['emp_no'] = 1001;
$record['register_no'] = 20;
$record['upc'] = $balW[0] . 'DP990';
$record['description'] = 'AR Payment';
$record['department'] = 990;
$record['quantity'] = 1;
$record['ItemQtty'] = 1;
$record['card_no'] = $w['CardNo'];
$record['regPrice'] = $balW[0];
$record['total'] = $balW[0];
$record['unitPrice'] = $balW[0];
$record['trans_no'] = $tn;
$record['trans_id'] = 1;
$info = DTrans::parameterize($record, 'datetime', $datetime);
$query = 'INSERT INTO dtransactions (' . $info['columnString'] . ') VALUES (' . $info['valueString'] . ')';
$prep = $dbc->prepare($query);
$result = $dbc->execute($prep, $info['arguments']);
$tn++;
}
}
}
}
/**
Create a POS transaction
@param $emp_no [int] employee ID
@param $register_no [int] lane ID
@param $lines [array] of records
Each record is a set of key/value pairs
with the following keys:
amount => purchase amount
department => department ID#
description => text description
card_no => member ID#
*/
public static function writeTransaction($emp_no, $register_no, $lines)
{
global $FANNIE_TRANS_DB;
$dbc = FannieDB::get($FANNIE_TRANS_DB);
$prep = $dbc->prepare('SELECT MAX(trans_no) FROM dtransactions
WHERE emp_no=? AND register_no=?');
$result = $dbc->execute($prep, array($emp_no, $register_no));
$trans_no = 1;
if ($dbc->num_rows($result) > 0) {
$row = $dbc->fetch_row($result);
if ($row[0] != '') {
$trans_no = $row[0] + 1;
}
}
$record = DTrans::$DEFAULTS;
$record['register_no'] = $register_no;
$record['emp_no'] = $emp_no;
$record['trans_no'] = $trans_no;
$record['trans_id'] = 1;
$record['trans_type'] = 'D';
$record['quantity'] = 1.0;
$record['ItemQtty'] = 1.0;
$record['memType'] = 1;
foreach ($lines as $line) {
$record['total'] = sprintf('%.2f', $line['amount']);
$record['unitPrice'] = sprintf('%.2f', $line['amount']);
$record['regPrice'] = sprintf('%.2f', $line['amount']);
$record['department'] = $line['department'];
$record['description'] = substr($line['description'], 0, 30);
$record['card_no'] = $line['card_no'];
$record['upc'] = sprintf('%.2fDP%d', $line['amount'], $line['department']);
$p = DTrans::parameterize($record, 'datetime', $dbc->now());
$query = "INSERT INTO dtransactions ({$p['columnString']}) VALUES ({$p['valueString']})";
$prep = $dbc->prepare($query);
$write = $dbc->execute($prep, $p['arguments']);
$record['trans_id']++;
}
return $emp_no . '-' . $register_no . '-' . $trans_no;
}
function process_file($linedata)
{
global $FANNIE_OP_DB, $FANNIE_TRANS_DB;
$EMP_NO = $this->config->get('EMP_NO');
$LANE_NO = $this->config->get('REGISTER_NO');
$OFFSET_DEPT = $this->config->get('MISC_DEPT');
$card_no = $this->get_column_index('card_no');
$classA = $this->get_column_index('classA');
$classB = $this->get_column_index('classB');
$note = $this->get_column_index('note');
$dbc = FannieDB::get($FANNIE_OP_DB);
$dtrans_table = $FANNIE_TRANS_DB . $dbc->sep() . 'dtransactions';
$prep = $dbc->prepare('SELECT MAX(trans_no) as tn FROM ' . $dtrans_table . '
WHERE emp_no=? AND register_no=?');
$result = $dbc->execute($prep, array($EMP_NO, $LANE_NO));
$trans = 1;
if ($dbc->num_rows($result) > 0) {
$row = $dbc->fetch_row($result);
if ($row['tn'] != '') {
$trans = $row['tn'] + 1;
}
}
foreach ($linedata as $data) {
if (!isset($data[$card_no])) {
continue;
} elseif (!is_numeric($data[$card_no])) {
continue;
}
$cn = trim($data[$card_no]);
$a_amt = trim($data[$classA], '$ ');
$b_amt = trim($data[$classB], '$ ');
$offset_amt = $a_amt + $b_amt;
if ($offset_amt == 0) {
continue;
}
$now = date('Y-m-d H:i:s');
$custdata = new CustdataModel($dbc);
$custdata->CardNo($cn);
$custdata->personNum(1);
$custdata->load();
$meminfo = new MeminfoModel($dbc);
$meminfo->card_no($cn);
$meminfo->load();
$susp = new SuspensionsModel($dbc);
$susp->cardno($cn);
$susp->load();
$susp->type('T');
$susp->memtype1($custdata->memType());
$susp->memtype2($custdata->Type());
$susp->suspDate($now);
$susp->discount($custdata->Discount());
$susp->chargelimit($custdata->ChargeLimit());
$susp->mailflag($meminfo->ads_OK());
$susp->reasoncode(64);
$susp->save();
$suspHistory = new SuspensionHistoryModel($dbc);
$suspHistory->username('abandon-import');
$suspHistory->postdate($now);
$suspHistory->cardno($cn);
$suspHistory->reasoncode(64);
$suspHistory->save();
$meminfo->ads_OK(0);
$meminfo->save();
$custdata->reset();
$custdata->CardNo($cn);
foreach ($custdata->find() as $obj) {
$obj->Type('TERM');
$obj->memType(0);
$obj->Discount(0);
$obj->ChargeLimit(0);
$obj->MemDiscountLimit(0);
$obj->save();
}
if (isset($data[$note]) && !empty($data[$note])) {
$memNote = new MemberNotesModel($dbc);
$memNote->cardno($cn);
$memNote->note($data[$note]);
$memNote->stamp($now);
$memNote->username('abandon-import');
$memNote->save();
}
$trans_id = 1;
if ($a_amt > 0) {
$record = DTrans::$DEFAULTS;
$record['register_no'] = $LANE_NO;
$record['emp_no'] = $EMP_NO;
$record['trans_no'] = $trans;
$record['upc'] = $a_amt . 'DP992';
$record['description'] = 'Class A Equity';
$record['trans_type'] = 'D';
$record['department'] = 992;
$record['unitPrice'] = -1 * $a_amt;
$record['total'] = -1 * $a_amt;
$record['regPrice'] = -1 * $a_amt;
$record['card_no'] = $cn;
$record['trans_id'] = $trans_id;
$trans_id++;
$info = DTrans::parameterize($record, 'datetime', $dbc->now());
$prep = $dbc->prepare("INSERT INTO {$dtrans_table} ({$info['columnString']}) VALUES ({$info['valueString']})");
$dbc->execute($prep, $info['arguments']);
}
if ($b_amt > 0) {
$record = DTrans::$DEFAULTS;
$record['register_no'] = $LANE_NO;
$record['emp_no'] = $EMP_NO;
$record['trans_no'] = $trans;
$record['upc'] = $a_amt . 'DP991';
$record['description'] = 'Class B Equity';
$record['trans_type'] = 'D';
$record['department'] = 991;
$record['unitPrice'] = -1 * $b_amt;
$record['total'] = -1 * $b_amt;
$record['regPrice'] = -1 * $b_amt;
$record['card_no'] = $cn;
$record['trans_id'] = $trans_id;
$trans_id++;
$info = DTrans::parameterize($record, 'datetime', $dbc->now());
$prep = $dbc->prepare("INSERT INTO {$dtrans_table} ({$info['columnString']}) VALUES ({$info['valueString']})");
$dbc->execute($prep, $info['arguments']);
}
$record = DTrans::$DEFAULTS;
$record['register_no'] = $LANE_NO;
$record['emp_no'] = $EMP_NO;
$record['trans_no'] = $trans;
$record['upc'] = $offset_amt . 'DP' . $OFFSET_DEPT;
$record['description'] = 'Abandon Equity';
$record['trans_type'] = 'D';
$record['department'] = $OFFSET_DEPT;
$record['unitPrice'] = $offset_amt;
$record['total'] = $offset_amt;
$record['regPrice'] = $offset_amt;
$record['card_no'] = $cn;
$record['trans_id'] = $trans_id;
$trans_id++;
$info = DTrans::parameterize($record, 'datetime', $dbc->now());
$prep = $dbc->prepare("INSERT INTO {$dtrans_table} ({$info['columnString']}) VALUES ({$info['valueString']})");
$dbc->execute($prep, $info['arguments']);
$record = DTrans::$DEFAULTS;
$record['register_no'] = $LANE_NO;
$record['emp_no'] = $EMP_NO;
$record['trans_no'] = $trans;
$record['upc'] = '0';
$record['description'] = '63350';
$record['trans_type'] = 'C';
$record['trans_subtype'] = 'CM';
$record['card_no'] = $cn;
$record['trans_id'] = $trans_id;
$info = DTrans::parameterize($record, 'datetime', $dbc->now());
$prep = $dbc->prepare("INSERT INTO {$dtrans_table} ({$info['columnString']}) VALUES ({$info['valueString']})");
$dbc->execute($prep, $info['arguments']);
$trans++;
}
return true;
}
function post_date_trans_handler()
{
global $FANNIE_OP_DB, $FANNIE_TRANS_DB, $FANNIE_PLUGIN_SETTINGS;
$dbc = FannieDB::get($FANNIE_OP_DB);
$dlog = DTransactionsModel::selectDtrans($this->date);
$emp_no = $FANNIE_PLUGIN_SETTINGS['ReversalEmployee'];
$register_no = $FANNIE_PLUGIN_SETTINGS['ReversalLane'];
$trans_no = 1;
$transP = $dbc->prepare_statement('SELECT MAX(trans_no) FROM
' . $FANNIE_TRANS_DB . $dbc->sep() . 'dlog WHERE
emp_no=? AND register_no=?');
$transR = $dbc->exec_statement($transP, array($emp_no, $register_no));
while ($transW = $dbc->fetch_row($transR)) {
$trans_no = $transW[0] + 1;
}
list($old_emp, $old_reg, $old_trans) = explode("-", $this->trans);
$query = "select upc, description, trans_type, trans_subtype,\n trans_status, department, quantity, Scale, unitPrice,\n total, regPrice, tax, foodstamp, discount, memDiscount,\n discountable, discounttype, voided, PercentDiscount,\n ItemQtty, volDiscType, volume, volSpecial, mixMatch,\n matched, memType, staff, card_no, numflag, charflag, \n trans_id \n from {$dlog} where register_no = ?\n and emp_no = ? and trans_no = ?\n and datetime BETWEEN ? AND ?\n and trans_status <> 'X'\n order by trans_id";
$args = array($old_reg, $old_emp, $old_trans, $this->date . ' 00:00:00', $this->date . ' 23:59:59');
$prep = $dbc->prepare_statement($query);
$result = $dbc->exec_statement($prep, $args);
$trans_id = 1;
$record = DTrans::$DEFAULTS;
$record['emp_no'] = $emp_no;
$record['register_no'] = $register_no;
$record['trans_no'] = $trans_no;
$record['trans_id'] = $trans_id;
$comment = $record;
$comment['description'] = 'VOIDING TRANSACTION ' . $this->trans;
$comment['trans_type'] = 'C';
$comment['trans_subtype'] = 'CM';
$comment['trans_status'] = 'D';
$params = DTrans::parameterize($comment, 'datetime', $dbc->now());
$table = $FANNIE_TRANS_DB . $dbc->sep() . 'dtransactions';
$prep = $dbc->prepare_statement("INSERT INTO {$table} ({$params['columnString']})\n VALUES ({$params['valueString']})");
$dbc->exec_statement($prep, $params['arguments']);
$record['trans_id'] += 1;
while ($w = $dbc->fetch_row($result)) {
$next = $record;
// copy base record
$next['upc'] = $w['upc'];
$next['description'] = $w['description'];
$next['trans_type'] = $w['trans_type'];
$next['trans_subtype'] = $w['trans_subtype'];
$next['trans_status'] = $w['trans_status'];
$next['department'] = $w['department'];
$next['quantity'] = -1 * $w['quantity'];
$next['Scale'] = $w['Scale'];
$next['unitPrice'] = -1 * $w['unitPrice'];
$next['total'] = -1 * $w['total'];
$next['regPrice'] = -1 * $w['regPrice'];
$next['tax'] = $w['tax'];
$next['foodstamp'] = $w['foodstamp'];
$next['discount'] = -1 * $w['discount'];
$next['memDiscount'] = -1 * $w['memDiscount'];
$next['discountable'] = $w['discountable'];
$next['discounttype'] = $w['discounttype'];
$next['voided'] = $w['voided'];
$next['PercentDiscount'] = $w['PercentDiscount'];
$next['ItemQtty'] = -1 * $w['ItemQtty'];
$next['volDiscType'] = $w['volDiscType'];
$next['volume'] = -1 * $w['volume'];
$next['volSpecial'] = -1 * $w['volSpecial'];
$next['mixMatch'] = $w['mixMatch'];
$next['matched'] = $w['matched'];
$next['memType'] = $w['memType'];
$next['staff'] = $w['staff'];
$next['numflag'] = $w['numflag'];
$next['charflag'] = $w['charflag'];
$next['card_no'] = $w['card_no'];
$params = DTrans::parameterize($next, 'datetime', $dbc->now());
$prep = $dbc->prepare_statement("INSERT INTO {$table} ({$params['columnString']})\n VALUES ({$params['valueString']})");
$dbc->exec_statement($prep, $params['arguments']);
$record['trans_id'] += 1;
}
// return a listing of the new, reversal transaction
$this->trans = $emp_no . '-' . $register_no . '-' . $trans_no;
$this->date = date('Y-m-d');
return $this->get_date_trans_handler();
}
public function run()
{
global $FANNIE_PLUGIN_SETTINGS, $FANNIE_AR_DEPARTMENTS, $FANNIE_TRANS_DB, $FANNIE_OP_DB;
$dbc = FannieDB::get($FANNIE_PLUGIN_SETTINGS['StaffArPayrollDB']);
$chkQ = 'SELECT staffArDateID FROM StaffArDates WHERE ' . $dbc->datediff($dbc->now(), 'tdate') . ' = 0 ';
$chkR = $dbc->query($chkQ);
if ($dbc->num_rows($chkR) == 0) {
// not scheduled for today
return true;
}
/**
Update plugin's table from legacy table, if present.
Can go away once WFC transistions away from legacy
table.
*/
$legacy_table = $FANNIE_TRANS_DB . $dbc->sep() . 'staffAR';
if ($dbc->tableExists($legacy_table)) {
$query = 'SELECT cardNo, adjust FROM ' . $legacy_table;
$result = $dbc->query($query);
$cards = '';
$args = array();
while ($row = $dbc->fetch_row($result)) {
$model = new StaffArAccountsModel($dbc);
$model->card_no($row['cardNo']);
$model->nextPayment($row['adjust']);
$model->save();
$cards .= '?,';
$args[] = $row['cardNo'];
}
// remove records that aren't in legacy table
if (count($args) > 0) {
$cards = substr($cards, 0, strlen($cards) - 1);
$query = "DELETE FROM StaffArAccounts WHERE card_no NOT IN ({$cards})";
$prep = $dbc->prepare($query);
$dbc->execute($prep, $args);
}
}
// end legacy table handling
// build department list
$ar_dept = 0;
$ret = preg_match_all("/[0-9]+/", $FANNIE_AR_DEPARTMENTS, $depts);
$depts = array_pop($depts);
if (!is_array($depts) || count($depts) == 0) {
$this->cronMsg('Could not locate any AR departments in Fannie configuration', FannieLogger::NOTICE);
return false;
} else {
$ar_dept = $depts[0];
}
$dept_desc = '';
$dept_model = new DepartmentsModel($dbc);
$dept_model->whichDB($FANNIE_OP_DB);
$dept_model->dept_no($ar_dept);
if ($dept_model->load()) {
$dept_desc = $dept_model->dept_name();
}
$dtrans = $FANNIE_TRANS_DB . $dbc->sep() . 'dtransactions';
$emp = isset($FANNIE_PLUGIN_SETTINGS['StaffArPayrollEmpNo']) ? $FANNIE_PLUGIN_SETTINGS['StaffArPayrollEmpNo'] : 1001;
$reg = isset($FANNIE_PLUGIN_SETTINGS['StaffArPayrollRegNo']) ? $FANNIE_PLUGIN_SETTINGS['StaffArPayrollRegNo'] : 20;
$query = 'SELECT MAX(trans_no) as maxt
FROM ' . $dtrans . '
WHERE emp_no=? AND register_no=?';
$prep = $dbc->prepare($query);
$result = $dbc->execute($prep, array($emp, $reg));
$trans_no = 1;
if ($dbc->num_rows($result) > 0) {
$row = $dbc->fetch_row($result);
if ($row['maxt'] != '') {
$trans_no = $row['maxt'] + 1;
}
}
$model = new StaffArAccountsModel($dbc);
foreach ($model->find() as $obj) {
if ($obj->nextPayment() == 0) {
// no need to write empty records
continue;
}
$record = DTrans::$DEFAULTS;
$record['emp_no'] = $emp;
$record['register_no'] = $reg;
$record['trans_no'] = $trans_no;
$record['trans_id'] = 1;
$record['trans_type'] = 'D';
$record['card_no'] = $obj->card_no();
$record['department'] = $ar_dept;
$record['description'] = $dept_desc;
$record['upc'] = sprintf('%.2fDP%d', $obj->nextPayment(), $ar_dept);
$record['total'] = sprintf('%.2f', $obj->nextPayment());
$record['unitPrice'] = sprintf('%.2f', $obj->nextPayment());
$record['regPrice'] = sprintf('%.2f', $obj->nextPayment());
$p = DTrans::parameterize($record, 'datetime', date("'Y-m-d 23:59:59'", strtotime('yesterday')));
$query = "INSERT INTO {$dtrans} ({$p['columnString']}) VALUES ({$p['valueString']})";
$prep = $dbc->prepare($query);
$write = $dbc->execute($prep, $p['arguments']);
if ($write === false) {
$this->cronMsg('Error making staff AR deduction for #' . $obj->card_no(), FannieLogger::ERROR);
}
$trans_no++;
}
}
function post_charges()
{
global $FANNIE_TRANS_DB;
$EMP_NO = $this->config->get('EMP_NO');
$LANE_NO = $this->config->get('REGISTER_NO');
$ret = "<b>Date</b>: " . date("m/d/Y") . "<br />\n <i>Summary of charges</i><br />\n <table class=\"table\">\n <tr><th>Account</th><th>Charge</th><th>Receipt #</th></tr>";
$sql = FannieDB::get($FANNIE_TRANS_DB);
$dRecord = DTrans::$DEFAULTS;
$dRecord['emp_no'] = $EMP_NO;
$dRecord['register_no'] = $LANE_NO;
$dRecord['trans_type'] = 'D';
$dRecord['department'] = 703;
$dRecord['quantity'] = 1;
$dRecord['ItemQtty'] = 1;
$dRecord['trans_id'] = 1;
$dParam = DTrans::parameterize($dRecord, 'datetime', $sql->now());
$insD = $sql->prepare_statement("INSERT INTO dtransactions\n ({$dParam['columnString']}) VALUES ({$dParam['valueString']})");
$tRecord = DTrans::$DEFAULTS;
$tRecord['emp_no'] = $EMP_NO;
$tRecord['register_no'] = $LANE_NO;
$tRecord['upc'] = '0';
$tRecord['description'] = 'InStore Charges';
$tRecord['trans_type'] = 'T';
$tRecord['trans_subtype'] = 'MI';
$tRecord['quantity'] = 0;
$tRecord['ItemQtty'] = 0;
$tRecord['trans_id'] = 2;
$tParam = DTrans::parameterize($tRecord, 'datetime', $sql->now());
$insT = $sql->prepare_statement("INSERT INTO dtransactions\n ({$tParam['columnString']}) VALUES ({$tParam['valueString']})");
$transQ = $sql->prepare_statement("SELECT MAX(trans_no) FROM dtransactions\n WHERE emp_no=? AND register_no=?");
foreach (FormLib::get_form_value('cardnos', array()) as $cardno) {
$amt = FormLib::get_form_value('billable' . $cardno);
$transR = $sql->exec_statement($transQ, array($EMP_NO, $LANE_NO));
$t_no = '';
if ($sql->num_rows($transR) > 0) {
$row = $sql->fetch_row($transR);
$t_no = $row[0];
}
if ($t_no == "") {
$t_no = 1;
} else {
$t_no++;
}
$desc = FormLib::get_form_value('desc' . $cardno);
$desc = substr($desc, 0, 24);
$dRecord['trans_no'] = $t_no;
$dRecord['upc'] = $amt . 'DP703';
$dRecord['description'] = 'Gazette Ad ' . $desc;
$dRecord['unitPrice'] = $amt;
$dRecord['total'] = $amt;
$dRecord['regPrice'] = $amt;
$dRecord['card_no'] = $cardno;
$dParam = DTrans::parameterize($dRecord);
$ins = $sql->exec_statement($insD, $dParam['arguments']);
$tRecord['trans_no'] = $t_no;
$tRecord['total'] = -1 * $amt;
$tRecord['card_no'] = $cardno;
$tParam = DTrans::parameterize($tRecord);
$sql->exec_statement($insT, $tParam['arguments']);
$ret .= sprintf("<tr><td>%d</td><td>\$%.2f</td><td>%s</td></tr>", $cardno, $amt, $EMP_NO . "-" . $LANE_NO . "-" . $t_no);
}
$ret .= '</table>';
return $ret;
}
