public function showEditForm($memNum, $country = "US")
{
global $FANNIE_URL, $FANNIE_TRANS_DB;
$dbc = $this->db();
$trans = $FANNIE_TRANS_DB . $dbc->sep();
$infoQ = $dbc->prepare_statement("SELECT payments\n FROM {$trans}equity_live_balance\n WHERE memnum=?");
$infoR = $dbc->exec_statement($infoQ, array($memNum));
$equity = 0;
if ($dbc->num_rows($infoR) > 0) {
$w = $dbc->fetch_row($infoR);
$equity = $w['payments'];
}
$plan = new EquityPaymentPlanAccountsModel($dbc);
$plan->cardNo($memNum);
foreach ($plan->find() as $p) {
$plan = $p;
break;
}
$allplans = new EquityPaymentPlansModel($dbc);
$ret = "<div class=\"panel panel-default\">\n <div class=\"panel-heading\">Equity</div>\n <div class=\"panel-body\">";
$ret .= '<div class="form-group">';
$ret .= '<span class="label primaryBackground">Stock Purchased</span> ';
$ret .= sprintf('%.2f', $equity);
$ret .= " <a href=\"{$FANNIE_URL}reports/Equity/index.php?memNum={$memNum}\">History</a>";
$ret .= '</div>';
$ret .= '<div class="form-group form-inline">';
$ret .= '<span class="label primaryBackground">Payment Plan</span> ';
$ret .= '<select name="payment-plan" class="form-control input-sm">
<option value="0">None</option>';
$ret .= $allplans->toOptions($plan->equityPaymentPlanID());
$ret .= '</select>
</div>';
$ret .= '<div class="form-group">';
$ret .= '<span class="label primaryBackground">Last Payment</span> ';
$ret .= sprintf('%s $%.2f', $plan->lastPaymentDate(), $plan->lastPaymentAmount());
$ret .= '</div>';
$ret .= '<div class="form-group">';
$ret .= '<span class="label primaryBackground">Next Payment</span> ';
$ret .= sprintf('%s $%.2f', $plan->nextPaymentDate(), $plan->nextPaymentAmount());
$ret .= '</div>';
$ret .= '<div class="form-group">';
$ret .= "<a href=\"{$FANNIE_URL}mem/correction_pages/MemEquityTransferTool.php?memIN={$memNum}\">Transfer Equity</a>";
$ret .= ' | ';
$ret .= "<a href=\"{$FANNIE_URL}mem/correction_pages/MemArEquitySwapTool.php?memIN={$memNum}\">Convert Equity</a>";
$ret .= '</div>';
$ret .= "</div>";
$ret .= "</div>";
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();
}
}
}
public function unitTest($phpunit)
{
$values = new \COREPOS\common\mvc\ValueContainer();
$values->_method = 'get';
$this->setForm($values);
$this->readRoutes();
$page = $this->get_view();
$phpunit->assertNotEquals(0, strlen($page));
$values->_method = 'put';
$this->setForm($values);
$this->readRoutes();
$this->put_handler();
$values->_method = 'get';
$this->setForm($values);
$this->readRoutes();
$newpage = $this->get_view();
$phpunit->assertNotEquals($newpage, $page);
$model = new EquityPaymentPlansModel($this->connection);
$model->equityPaymentPlanID(1);
$phpunit->assertEquals(true, $model->load());
$values->_method = 'get';
$values->id = 1;
$this->setForm($values);
$this->readRoutes();
$page = $this->get_id_view();
$phpunit->assertEquals(false, strstr($page, 'plan does not exist'));
$values->_method = 'post';
$values->id = 1;
$values->name = 'Test';
$values->final = '123';
$values->initial = '12';
$values->recurring = '23';
$values->cycle = '5W';
$values->basis = 1;
$values->overdue = 90;
$values->reason = 4;
$this->setForm($values);
$this->readRoutes();
$this->post_id_name_final_initial_recurring_cycle_basis_overdue_reason_handler();
$model->reset();
$model->equityPaymentPlanID(1);
$model->load();
$phpunit->assertEquals($values->name, $model->name());
$phpunit->assertEquals($values->final, $model->finalBalance());
$phpunit->assertEquals($values->initial, $model->initialPayment());
$phpunit->assertEquals($values->recurring, $model->recurringPayment());
$phpunit->assertEquals($values->cycle, $model->billingCycle());
$phpunit->assertEquals($values->basis, $model->dueDateBasis());
$phpunit->assertEquals($values->overdue, $model->overDueLimit());
$phpunit->assertEquals($values->reason, $model->reasonMask());
}
