/**
Get account using older tables
*/
private static function getCustdata($dbc, $id)
{
if ($id == 0) {
return self::getAllCustdata($dbc);
}
$query = '
SELECT c.CardNo,
CASE WHEN s.memtype2 IS NOT NULL THEN s.memtype2 ELSE c.Type END AS memberStatus,
CASE WHEN s.memtype2 IS NOT NULL THEN c.Type ELSE \'\' END AS activeStatus,
c.memType AS customerTypeID,
c.Balance,
c.ChargeLimit,
u.upc,
d.start_date,
d.end_date,
m.street,
m.city,
m.state,
m.zip,
m.ads_OK,
z.pref,
t.memDesc,
CASE WHEN c.LastChange > m.modified THEN c.LastChange ELSE m.modified END AS modified
FROM custdata AS c
LEFT JOIN meminfo AS m ON c.CardNo=m.card_no
LEFT JOIN memContact AS z ON c.CardNo=z.card_no
LEFT JOIN memDates AS d ON c.CardNo=d.card_no
LEFT JOIN memberCards AS u ON c.CardNo=u.card_no
LEFT JOIN suspensions AS s ON c.CardNo=s.cardno
LEFT JOIN memtype AS t ON c.memType=t.memtype
WHERE c.CardNo=?
AND c.personNum=1';
$prep = $dbc->prepare($query);
$res = $dbc->execute($prep, array($id));
if ($res === false || $dbc->numRows($res) == 0) {
return false;
}
$row = $dbc->fetchRow($res);
$ret = array('cardNo' => $id, 'memberStatus' => $row['memberStatus'], 'activeStatus' => $row['activeStatus'], 'customerTypeID' => $row['customerTypeID'], 'customerType' => $row['memDesc'], 'chargeLimit' => $row['ChargeLimit'], 'chargeBalance' => $row['Balance'], 'idCardUPC' => $row['upc'] === null ? '' : $row['upc'], 'startDate' => $row['start_date'] === null ? '0000-00-00 00:00:00' : $row['start_date'], 'endDate' => $row['end_date'] === null ? '0000-00-00 00:00:00' : $row['end_date'], 'city' => $row['city'] === null ? '' : $row['city'], 'state' => $row['state'] === null ? '' : $row['state'], 'zip' => $row['zip'] === null ? '' : $row['zip'], 'contactAllowed' => $row['ads_OK'] === null ? 1 : $row['ads_OK'], 'contactMethod' => 'mail', 'addressFirstLine' => $row['street'] === null ? '' : $row['street'], 'addressSecondLine' => '', 'customers' => array(), 'modified' => $row['modified']);
if (strstr($row['street'], "\n")) {
list($one, $two) = explode("\n", $row['street'], 2);
$ret['addressFirstLine'] = $one;
$ret['addressSecondLine'] = $two;
}
if ($row['pref'] == 2) {
$ret['contactMethod'] == 'email';
} elseif ($row['pref'] == 3) {
$ret['contactMethod'] == 'both';
}
$query = '
SELECT c.personNum,
c.FirstName,
c.LastName,
c.chargeOk,
c.writeChecks,
c.Discount,
c.staff,
m.phone,
m.email_1,
m.email_2,
CASE WHEN s.memtype2 IS NOT NULL THEN s.memtype2 ELSE c.Type END AS memberStatus,
c.SSI,
CASE WHEN c.LastChange > m.modified THEN c.LastChange ELSE m.modified END AS modified
FROM custdata AS c
LEFT JOIN meminfo AS m ON c.CardNo=m.card_no
LEFT JOIN suspensions AS s ON c.CardNo=s.cardno
WHERE c.CardNo=?
ORDER BY c.personNum';
$prep = $dbc->prepare($query);
$res = $dbc->execute($prep, array($id));
while ($row = $dbc->fetchRow($res)) {
$customer = array('customerID' => 0, 'firstName' => $row['FirstName'], 'lastName' => $row['LastName'], 'chargeAllowed' => $row['chargeOk'], 'checksAllowed' => $row['writeChecks'], 'discount' => $row['Discount'], 'staff' => $row['staff'], 'lowIncomeBenefits' => $row['SSI'], 'modified' => $row['modified']);
if ($row['personNum'] == 1) {
$customer['accountHolder'] = 1;
$customer['phone'] = $row['phone'] === null ? '' : $row['phone'];
$customer['email'] = $row['email_1'] === null ? '' : $row['email_1'];
$customer['altPhone'] = $row['email_2'] === null ? '' : $row['email_2'];
} else {
$customer['accountHolder'] = 0;
$customer['phone'] = '';
$customer['email'] = '';
$customer['altPhone'] = '';
}
if ($row['memberStatus'] == 'PC') {
$customer['memberPricingAllowed'] = 1;
$customer['memberCouponsAllowed'] = 1;
}
$ret['customers'][] = $customer;
}
// if the new tables are present in classic mode,
// migrate the account if needed and include the new style
// record ID in the response
if ($dbc->tableExists('CustomerAccounts') && $dbc->tableExists('Customers')) {
$account = new \CustomerAccountsModel($dbc);
$account->cardNo($id);
if ($account->load()) {
$ret['customerAccountID'] = $account->customerAccountID();
} else {
$ret['customerAccountID'] = $account->migrateAccount($id);
}
// customers tables is more complicated
// try to match IDs by names
$customers = new \CustomersModel($dbc);
$customers->cardNo($id);
$current = $customers->find();
if (count($current) != count($ret['customers'])) {
foreach ($customers as $c) {
$c->delete();
}
$customers->migrateAccount($id);
$customers->reset();
$customers->cardNo($id);
}
foreach ($customers->find() as $c) {
for ($i = 0; $i < count($ret['customers']); $i++) {
if ($ret['customers'][$i]['firstName'] == $c->firstName() && $ret['customers'][$i]['lastName'] == $c->lastName()) {
$ret['customers'][$i]['customerID'] = $c->customerID();
$ret['customers'][$i]['customerAccountID'] = $ret['customerAccountID'];
break;
}
}
}
} else {
// plug a value so the returned structure is complete
$ret['customerAccountID'] = 0;
}
return $ret;
}
/**
Copy information from existing tables to
a new customer records. This will NOT
update an existing records.
@param $card_no [int] member number
@return [boolean] success
*/
public function migrateAccount($card_no)
{
$dbc = $this->connection;
$query = '
SELECT c.personNum,
c.FirstName,
c.LastName,
c.chargeOk,
c.writeChecks,
c.Discount,
c.staff,
m.phone,
m.email_1,
m.email_2,
CASE WHEN s.memtype2 IS NOT NULL THEN s.memtype2 ELSE c.Type END AS memberStatus,
c.SSI,
CASE WHEN c.LastChange > m.modified THEN c.LastChange ELSE m.modified END AS modified
FROM custdata AS c
LEFT JOIN meminfo AS m ON c.CardNo=m.card_no
LEFT JOIN suspensions AS s ON c.CardNo=s.cardno
WHERE c.CardNo=?';
$prep = $dbc->prepare($query);
$res = $dbc->execute($prep, array($card_no));
if ($res === false || $dbc->numRows($res) == 0) {
return false;
}
$this->reset();
$this->cardNo($card_no);
if (count($this->find()) > 0) {
return false;
// record(s) already exist
}
/**
Get the related account ID, migrating it too
if needed
*/
$account = new CustomerAccountsModel($dbc);
$account->cardNo($card_no);
if (!$account->load()) {
$id = $account->migrateAccount($card_no);
if ($id === false) {
return false;
} else {
$this->customerAccountID($id);
}
} else {
$this->customerAccountID($account->customerAccountID());
}
while ($w = $dbc->fetchRow($res)) {
$this->firstName($w['FirstName']);
$this->lastName($w['LastName']);
$this->chargeAllowed($w['chargeOk']);
$this->checksAllowed($w['writeChecks']);
$this->discount($w['Discount']);
$this->staff($w['staff']);
$this->lowIncomeBenefits($w['SSI']);
if ($w['personNum'] == 1) {
$this->accountHolder(1);
$this->phone($w['phone']);
$this->altPhone($w['email_2']);
$this->email($w['email_1']);
} else {
$this->accountHolder(0);
$this->phone('');
$this->email('');
}
if ($w['memberStatus'] == 'PC') {
$this->memberPricingAllowed(1);
$this->memberCouponsAllowed(1);
}
$this->modified($w['modified']);
// preserve change timestamp rather than adding a new one for "now"
$this->record_changed = false;
$this->save();
}
return true;
}
