// remove names that were blank on the form
for ($i = $count; $i < 5; $i++) {
$cust->personNum($i);
$cust->delete();
}
$meminfo = new MeminfoModel($dbc);
$meminfo->card_no($memNum);
$meminfo->street($MI_FIELDS['street']);
$meminfo->city($MI_FIELDS['city']);
$meminfo->state($MI_FIELDS['state']);
$meminfo->phone($MI_FIELDS['phone']);
$meminfo->email_2($MI_FIELDS['email_2']);
$meminfo->email_1($MI_FIELDS['email_1']);
$meminfo->ads_OK($MI_FIELDS['ads_OK']);
$meminfo->save();
$memdate = new MemDatesModel($dbc);
$memdate->card_no($memNum);
$memdate->start_date($_POST['startDate']);
$memdate->end_date($_POST['endDate']);
$memdate->save();
// FIRE ALL UPDATE
include 'custUpdates.php';
updateCustomerAllLanes($memNum);
/* general note handling */
$notetext = $_POST['notetext'];
$notetext = preg_replace("/\n/", "<br />", $notetext);
$notetext = preg_replace("/\\'/", "''", $notetext);
$checkQ = $sql->prepare("select * from memberNotes where note=? and cardno=?");
$checkR = $sql->execute($checkQ, array($notetext, $memNum));
if ($sql->num_rows($checkR) == 0) {
$noteQ = $sql->prepare("insert into memberNotes (cardno, note, stamp, username) VALUES (?, ?, " . $sql->now() . ", ?)");
/**
Update older tables.
*/
private static function postCustdata($dbc, $id, $json)
{
$config = \FannieConfig::factory();
$ret = array('errors' => 0, 'error-msg' => '');
/** save dates if provided **/
if (isset($json['startDate']) || isset($json['endDate'])) {
$dates = new \MemDatesModel($dbc);
$dates->start_date($json['startDate']);
$dates->end_date($json['endDate']);
$dates->card_no($id);
if (!$dates->save()) {
$ret['errors']++;
$ret['error-msg'] .= 'ErrDates ';
}
}
/** save UPC if provided **/
if (isset($json['idCardUPC'])) {
$cards = new \MemberCardsModel($dbc);
$cards->card_no($id);
if ($json['idCardUPC'] != '') {
$cards->upc(\BarcodeLib::padUPC($json['idCardUPC']));
} else {
$cards->upc('');
}
if (!$cards->save()) {
$ret['errors']++;
}
}
/** save contact method if provided **/
if (isset($json['contactMethod'])) {
$contact = new \MemContactModel($dbc);
$contact->card_no($id);
if (isset($json['contactAllowed']) && !$json['contactAllowed']) {
$contact->pref(0);
} elseif ($json['contactMethod'] == 'email') {
$contact->pref(2);
} elseif ($json['contactMethod'] == 'both') {
$contact->pref(3);
} else {
$contact->pref(1);
}
if (!$contact->save()) {
$ret['errors']++;
$ret['error-msg'] .= 'ErrUPC ';
}
}
/**
Custdata and meminfo are messier. Start with account-level
settings.
*/
$custdata = new \CustdataModel($dbc);
$custdata->CardNo($id);
$custdata_changed = false;
$meminfo = new \MeminfoModel($dbc);
$meminfo->card_no($id);
if (isset($json['addressFirstLine'])) {
$street = $json['addressFirstLine'];
if (isset($json['addressSecondLine'])) {
$street .= "\n" . $json['addressSecondLine'];
}
$meminfo->street($street);
}
if (isset($json['city'])) {
$meminfo->city($json['city']);
}
if (isset($json['state'])) {
$meminfo->state($json['state']);
}
if (isset($json['zip'])) {
$meminfo->zip($json['zip']);
}
if (isset($json['contactAllowed'])) {
$meminfo->ads_OK($json['contactAllowed']);
}
if (isset($json['activeStatus']) && $json['activeStatus'] != '') {
$custdata->Type($json['activeStatus']);
$custdata_changed = true;
} elseif (isset($json['memberStatus'])) {
$custdata->Type($json['memberStatus']);
$custdata_changed = true;
}
if (isset($json['customerTypeID'])) {
$custdata->memType($json['customerTypeID']);
$custdata_changed = true;
}
if (isset($json['chargeLimit'])) {
$custdata->ChargeLimit($json['chargeLimit']);
$custdata->MemDiscountLimit($json['chargeLimit']);
$custdata_changed = true;
}
if (isset($json['chargeBalance'])) {
$custdata->Balance($json['chargeBalance']);
$custdata_changed = true;
}
/**
Now loop through per-person settings. Assign the primary account holder's
email address and phone number to the global meminfo, but save the other
settings using a different per-person custdata instance
*/
if (isset($json['customers']) && is_array($json['customers']) && count($json['customers']) > 0) {
$personNum = 2;
foreach ($json['customers'] as $c_json) {
if (!isset($c_json['accountHolder'])) {
$ret['errors']++;
$ret['error-msg'] .= 'ErrAcctHolder ';
continue;
}
$loopCD = new \CustdataModel($dbc);
$loopCD->CardNo($id);
$loopCD_changed = false;
if ($c_json['accountHolder']) {
$loopCD->personNum(1);
if (isset($c_json['phone'])) {
$meminfo->phone($c_json['phone']);
}
if (isset($c_json['altPhone'])) {
$meminfo->email_2($c_json['altPhone']);
}
if (isset($c_json['email'])) {
$meminfo->email_1($c_json['email']);
}
} elseif (isset($c_json['firstName']) && isset($c_json['lastName']) && $c_json['firstName'] == '' && $c_json['lastName'] == '') {
// blank name fields on non-account holder mean
// the customer was removed from the account
continue;
} else {
$loopCD->personNum($personNum);
$personNum++;
}
if (isset($c_json['firstName'])) {
$loopCD->FirstName($c_json['firstName']);
$loopCD_changed = true;
}
if (isset($c_json['lastName'])) {
$loopCD->LastName($c_json['lastName']);
$loopCD_changed = true;
}
if (isset($c_json['chargeAllowed'])) {
$loopCD->ChargeOk($c_json['chargeAllowed']);
$loopCD_changed = true;
}
if (isset($c_json['checksAllowed'])) {
$loopCD->WriteChecks($c_json['checksAllowed']);
$loopCD_changed = true;
}
if (isset($c_json['staff'])) {
$loopCD->staff($c_json['staff']);
$loopCD_changed = true;
}
if (isset($c_json['discount'])) {
$loopCD->Discount($c_json['discount']);
$loopCD_changed = true;
}
if (isset($c_json['lowIncomeBenefits'])) {
$loopCD->SSI($c_json['lowIncomeBenefits']);
$loopCD_changed = true;
}
if ($loopCD_changed && !$loopCD->save()) {
$ret['errors']++;
$ret['error-msg'] .= 'ErrPerson ';
}
}
$cleanP = $dbc->prepare('DELETE FROM custdata WHERE CardNo=? AND personNum>=?');
$cleanR = $dbc->execute($cleanP, array($id, $personNum));
}
if (!$meminfo->save()) {
$ret['errors']++;
$ret['error-msg'] .= 'ErrMeminfo ';
}
/**
Finally, apply account-level settings to
all custdata records for the account.
*/
if ($custdata_changed) {
$allCD = new \CustdataModel($dbc);
$allCD->CardNo($id);
foreach ($allCD->find() as $c) {
$custdata->personNum($c->personNum());
if (!$custdata->save()) {
$ret['errors']++;
$ret['error-msg'] .= 'ErrGlobal ';
}
}
}
self::setBlueLines($id);
// in classic mode sync changes back to the new table if present
if ($config->get('CUST_SCHEMA') != 1 && $dbc->tableExists('CustomerAccounts')) {
self::postAccount($dbc, $id, $json);
}
$ret['account'] = self::get($id);
return $ret;
}
public function run()
{
$dbc = FannieDB::get($this->config->get('TRANS_DB'));
// build department list
$ret = preg_match_all("/[0-9]+/", $this->config->get('EQUITY_DEPARTMENTS'), $depts);
$depts = array_pop($depts);
$dlist = "(";
$where_args = array();
foreach ($depts as $d) {
$dlist .= "?,";
$where_args[] = $d;
}
$dlist = substr($dlist, 0, strlen($dlist) - 1) . ")";
if ($dlist == ')') {
// no configured departments
return false;
}
// lookup AR transactions from past 15 days
$lookupQ = "SELECT card_no,\n department, total,\n tdate, trans_num, trans_id\n FROM dlog_15\n WHERE department IN {$dlist}";
$lookupP = $dbc->prepare($lookupQ);
$lookupR = $dbc->execute($lookupP, $where_args);
$checkP = $dbc->prepare('SELECT stockPurchase FROM stockpurchases
WHERE tdate=? AND trans_num=? AND card_no=? AND dept=?');
$addP = $dbc->prepare('INSERT INTO stockpurchases (card_no, stockPurchase, tdate, trans_num, dept)
VALUES (?, ?, ?, ?, ?)');
$model = new StockpurchasesModel($dbc);
while ($lookupW = $dbc->fetch_row($lookupR)) {
if ($this->isLogged($dbc, $lookupW)) {
continue;
}
$model->card_no($lookupW['card_no']);
$model->stockPurchase($lookupW['total']);
$model->tdate($lookupW['tdate']);
$model->trans_num($lookupW['trans_num']);
$model->dept($lookupW['department']);
$model->trans_id($lookupW['trans_id']);
if ($model->save() === false) {
$this->cronMsg('Error adding equity entry ' . $lookupW['tdate'] . ' ' . $lookupW['trans_num'], FannieLogger::ERROR);
}
}
// rebuild ar history sum table
$dbc->query("TRUNCATE TABLE equity_history_sum");
$query = "INSERT INTO equity_history_sum\n SELECT card_no, SUM(stockPurchase), MIN(tdate)\n FROM stockpurchases GROUP BY card_no";
$def = $dbc->tableDefinition('equity_history_sum');
if (isset($def['mostRecent'])) {
$query = str_replace('MIN(tdate)', 'MIN(tdate), MAX(tdate)', $query);
}
$try = $dbc->query($query);
if ($try === false) {
$this->cronMsg('Error rebuilding equity_history_sum table', FannieLogger::ERROR);
}
if (isset($def['mostRecent'])) {
/**
Lookup transactions with net equity purchase
of zero. These transactions should not impact
the first/last equity purchase dates
*/
$voidedR = $dbc->query('
SELECT card_no,
trans_num
FROM stockpurchases
GROUP BY card_no,trans_num
HAVING SUM(stockPurchase)=0');
$voids = array();
while ($row = $dbc->fetchRow($voidedR)) {
if (!isset($voids[$row['card_no']])) {
$voids[$row['card_no']] = array();
}
$voids[$row['card_no']][] = $row['trans_num'];
}
/**
For applicable members, lookup min and max
date values again excluding the net-zero
transactions. Update date fields for these
members.
*/
$upP = $dbc->prepare('
UPDATE equity_history_sum
SET startdate=?,
mostRecent=?
WHERE card_no=?');
foreach ($voids as $card_no => $transactions) {
$query = '
SELECT MIN(tdate) AS startdate,
MAX(tdate) AS mostRecent
FROM stockpurchases
WHERE card_no=?
AND trans_num NOT IN (';
$args = array($card_no);
foreach ($transactions as $t) {
$query .= '?,';
$args[] = $t;
}
$query = substr($query, 0, strlen($query) - 1) . ')';
$prep = $dbc->prepare($query);
$res = $dbc->execute($prep, $args);
if ($res && $dbc->numRows($res)) {
$dates = $dbc->fetchRow($res);
$dbc->execute($upP, array($dates['startdate'], $dates['mostRecent'], $card_no));
}
}
}
/**
Update payment plan accounts based on
current payment history
*/
$dbc->selectDB($this->config->get('OP_DB'));
$date = new MemDatesModel($dbc);
$plan = new EquityPaymentPlansModel($dbc);
$plans = array();
foreach ($plan->find() as $p) {
$plans[$p->equityPaymentPlanID()] = $p;
}
$accounts = new EquityPaymentPlanAccountsModel($dbc);
$balP = $dbc->prepare('
SELECT payments,
mostRecent
FROM ' . $this->config->get('TRANS_DB') . $dbc->sep() . 'equity_history_sum
WHERE card_no=?');
$historyP = $dbc->prepare('
SELECT stockPurchase,
tdate
FROM ' . $this->config->get('TRANS_DB') . $dbc->sep() . 'stockpurchases
WHERE card_no=?
ORDER BY tdate');
foreach ($accounts->find() as $account) {
if (!isset($plans[$account->equityPaymentPlanID()])) {
// undefined plan
continue;
}
$myplan = $plans[$account->equityPaymentPlanID()];
$bal = $dbc->getRow($balP, array($account->cardNo()));
if ($bal['payments'] >= $myplan->finalBalance()) {
// account is now paid in full
$account->lastPaymentDate($bal['mostRecent']);
$account->nextPaymentDate(null);
$account->nextPaymentAmount(0);
$account->save();
} else {
/**
Payment plans are really structured into tiers. For a $20 increment, $100 total
plan the tiers are at $20, $40, $60, and $80. I'm not assuming any rigid
enforcement of payment amounts (i.e., someone may make a payment that isn't
exactly $20). So after the current tier is established, I go through
the whole history to figure out when the tier was reached and track
any progress toward tier.
*/
$payment_number = $this->numberOfPayments($myplan, $bal['payments']);
$last_threshold_reached = $myplan->initialPayment() + ($payment_number - 1) * $myplan->recurringPayment();
$historyR = $dbc->execute($historyP, array($account->cardNo()));
list($last_payment, $last_date, $next_payment) = $this->analyzePaymentHistory($dbc, $historyR, $myplan, $last_threshold_reached);
$account->lastPaymentDate($last_date);
$account->lastPaymentAmount($last_payment);
$account->nextPaymentAmount($next_payment);
// finally, figure out the next payment due date
// if due dates are all based on the original join date,
// walk forward through due dates from the beginning
$basis_date = $last_date;
if ($myplan->dueDateBasis() == 0) {
$date->card_no($account->cardNo());
$date->load();
$basis_date = $date->start_date();
for ($i = 1; $i < $payment_number - 1; $i++) {
$basis_date = $this->getNextPaymentDate($myplan, $basis_date);
}
}
$account->nextPaymentDate($this->getNextPaymentDate($myplan, $basis_date));
$account->save();
}
}
}
/**
Update various legacy tables to match an
existing CustomerAccounts record.
@param $card_no [int] member number
@return [boolean] success
*/
public function legacySync($card_no)
{
$dbc = $this->connection;
$custdata = new CustdataModel($dbc);
$custdata->CardNo($card_no);
$meminfo = new MeminfoModel($dbc);
$meminfo->card_no($card_no);
$memDates = new MemDatesModel($dbc);
$memDates->card_no($card_no);
$cards = new MemberCardsModel($dbc);
$cards->card_no($card_no);
$contact = new MemContactModel($dbc);
$contact->card_no($card_no);
$suspensions = new SuspensionsModel($dbc);
$suspensions->cardno($card_no);
$this->reset();
$this->cardNo($card_no);
if (!$this->load()) {
return false;
}
if ($this->activeStatus() != '') {
$suspensions->cardno($card_no);
$suspensions->memtype1($this->customerTypeID());
$suspensions->memtype2($this->memberStatus());
$suspensions->chargelimit($this->chargeLimit());
$suspensions->mailflag($this->contactAllowed());
$suspensions->save();
} else {
$custdata->Type($this->memberStatus());
$custdata->memType($this->customerTypeID());
$custdata->ChargeLimit($this->chargeLimit());
$custdata->MemDiscountLimit($this->chargeLimit());
$meminfo->ads_OK($this->contactAllowed());
}
$custdata->Balance($this->chargeBalance());
$allCustdata = new CustdataModel($dbc);
$allCustdata->CardNo($card_no);
foreach ($allCustdata as $c) {
$custdata->personNum($c->personNum());
$custdata->save();
}
$cards->upc($this->idCardUPC());
$cards->save();
$memDates->start_date($this->startDate());
$memDates->end_date($this->endDate());
$memDates->save();
if ($this->addressSecondLine() != '') {
$meminfo->street($this->addressFirstLine() . "\n" . $this->addressSecondLine());
} else {
$meminfo->street($this->addressFirstLine());
}
$meminfo->city($this->city());
$meminfo->state($this->state());
$meminfo->zip($this->zip());
$meminfo->save();
if ($this->contactAllowed() == 0) {
$contact->pref(0);
} else {
switch ($this->contactMethod()) {
case 'mail':
$contact->pref(1);
break;
case 'email':
$contact->pref(2);
break;
case 'both':
$contact->pref(3);
break;
}
}
$contact->save();
return true;
}
