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; }