public function get_view()
 {
     global $FANNIE_OP_DB;
     $dbc = FannieDB::get($FANNIE_OP_DB);
     $ret = '<form action="' . $_SERVER['PHP_SELF'] . '" method="post">';
     $ret .= '<div class="col-sm-4">
         <div class="panel panel-default">
             <div class="panel-heading">Include Types</div>
             <div class="panel-body">';
     $p = $dbc->prepare_statement("SELECT memtype,memDesc FROM memtype ORDER BY memtype");
     $r = $dbc->exec_statement($p);
     while ($w = $dbc->fetch_row($r)) {
         $ret .= sprintf('<label><input type="checkbox" value="%d" name="types[]" /> %s</label><br />', $w['memtype'], $w['memDesc']);
     }
     $ret .= '</div></div></div>';
     $ret .= '<div class="col-sm-4">
             <div class="form-group">
                 <label>
                     <input type="checkbox" name="inactives" />
                     Include Inactive Accounts
                 </label>
             </div>
             <div class="form-group">
                 <select class="form-control" name="all">
                     <option>All Accounts</option>
                     <option>Accounts that prefer Email</option>
                 </select>
             </div>
             <div class="form-group">
                 <button type="submit" class="btn btn-default">Get Emails</button>
             </div>
         </div>';
     return $ret;
 }

 public function post_handler()
 {
     global $FANNIE_OP_DB;
     $dbc = FannieDB::get($FANNIE_OP_DB);
     $amount = FormLib::get('amount');
     $paid = FormLib::get('paid') / 100.0;
     $retained = FormLib::get('retained') / 100.0;
     $netQ = '
         SELECT SUM(p.net_purch) AS ttl
         FROM patronage_workingcopy AS p
             INNER JOIN custdata AS c ON p.cardno=c.CardNo AND c.personNum=1
         WHERE c.Type=\'PC\'';
     $netR = $dbc->query($netQ);
     $netW = $dbc->fetch_row($netR);
     $purchases = $netW['ttl'];
     $personQ = '
         SELECT p.net_purch,
             c.cardno
         FROM patronage_workingcopy AS p
             INNER JOIN custdata AS c ON p.cardno=c.CardNo AND c.personNum=1
         WHERE c.Type=\'PC\'';
     $personR = $dbc->query($personQ);
     $this->insertRecords($dbc, $personR, $purchases, $paid, $retained, $amount);
     $finishQ = '
         INSERT INTO patronage
         (cardno, purchase, discounts, rewards, net_purch, tot_pat, cash_pat, equit_pat, FY)
         SELECT 
             p.cardno, purchase, discounts, rewards, net_purch, tot_pat, cash_pat, equit_pat, FY
         FROM patronage_workingcopy AS p
             INNER JOIN custdata AS c ON p.cardno=c.CardNo AND c.personNum=1
         WHERE c.Type=\'PC\'';
     $dbc->query($finishQ);
     return true;
 }

 public function export_order($id)
 {
     global $FANNIE_OP_DB;
     $dbc = FannieDB::get($FANNIE_OP_DB);
     $order = new PurchaseOrderModel($dbc);
     $order->orderID($id);
     $order->load();
     $items = new PurchaseOrderItemsModel($dbc);
     $items->orderID($id);
     $columns = array('Product Code', 'Inventory Item', 'Invoice Number', 'Date', 'Unit', 'Quantity', 'Cost', 'Description', 'Alt. Unit Indicator', 'Alternate Unit');
     foreach ($items->find() as $obj) {
         list($units, $unit_of_measure) = $this->getUnits($obj);
         echo $obj->sku() . ',';
         echo '"' . $obj->description() . '",';
         echo $order->vendorInvoiceID() . ',';
         echo date('Ymd', strtotime($obj->receivedDate())) . ',';
         printf('%f,', $units * $obj->caseSize() * $obj->quantity());
         echo $unit_of_measure . ',';
         printf('%.2f,', $obj->unitCost() * $obj->caseSize() * $obj->quantity());
         echo '"' . $obj->description() . '",';
         echo '"",';
         // alt. indicator
         echo '"",';
         // alt. unit
         echo "\r\n";
     }
 }

 public function fetch_report_data()
 {
     global $FANNIE_PLUGIN_SETTINGS;
     $dbc = FannieDB::get($FANNIE_PLUGIN_SETTINGS['GiveUsMoneyDB']);
     // compound interest calculation is MySQL-specific
     $query = 'SELECT termInMonths, 
                 SUM(principal) as totalP,
                 AVG(interestRate) as avgR,
                 SUM(principal * POW(1+interestRate, DATEDIFF(DATE_ADD(loanDate, INTERVAL termInMonths MONTH), loanDate)/365.25)) as totalM,
                 MIN(loanDate) AS nearest,
                 MAX(loanDate) as farthest
               FROM GumLoanAccounts
               GROUP BY termInMonths
               ORDER BY termInMonths';
     $result = $dbc->query($query);
     $data = array();
     while ($row = $dbc->fetch_row($result)) {
         $nearest = strtotime($row['nearest']);
         $nearest = mktime(0, 0, 0, date('n', $nearest) + $row['termInMonths'], date('j', $nearest), date('Y', $nearest));
         $farthest = strtotime($row['farthest']);
         $farthest = mktime(0, 0, 0, date('n', $farthest) + $row['termInMonths'], date('j', $farthest), date('Y', $farthest));
         $record = array($row['termInMonths'], sprintf('%.2f', $row['totalP']), sprintf('%.2f', $row['avgR'] * 100), sprintf('%.2f', $row['totalM']), date('Y-m-d', $nearest), date('Y-m-d', $farthest));
         $data[] = $record;
     }
     return $data;
 }

 public function settingChange()
 {
     global $FANNIE_ROOT, $FANNIE_PLUGIN_SETTINGS;
     $db_name = $FANNIE_PLUGIN_SETTINGS['CalendarDatabase'];
     if (empty($db_name)) {
         return;
     }
     $dbc = FannieDB::get($db_name);
     $tables = array('AccountClasses', 'Attendees', 'Calendars', 'CalendarSubscriptions', 'MonthviewEvents', 'Permissions');
     foreach ($tables as $t) {
         $model_class = $t . 'Model';
         if (!class_exists($model_class)) {
             include_once dirname(__FILE__) . '/models/' . $model_class . '.php';
         }
         $instance = new $model_class($dbc);
         $instance->create();
     }
     if ($dbc->table_exists('account_classes')) {
         $model = new AccountClassesModel($dbc);
         /* populate account classes */
         $classes = array(1 => 'VIEWER', 2 => 'CONTRIBUTOR', 3 => 'ADMIN', 4 => 'OWNER');
         foreach ($classes as $id => $desc) {
             $model->classID($id);
             $model->classDesc($desc);
             $model->save();
         }
     }
 }

 function export_order($id)
 {
     global $FANNIE_OP_DB;
     $dbc = FannieDB::get($FANNIE_OP_DB);
     $order = new PurchaseOrderModel($dbc);
     $order->orderID($id);
     $order->load();
     $items = new PurchaseOrderItemsModel($dbc);
     $items->orderID($id);
     $vendor = new VendorsModel($dbc);
     $vendor->vendorID($order->vendorID());
     $vendor->load();
     echo 'Vendor,"' . $vendor->vendorName() . '",Order Date,' . date('Y-m-d') . "\r\n";
     echo "\r\n";
     echo "SKU,\"Order Qty\",Brand,Description,\"Case Size\",\"Est. Cost\"\r\n";
     foreach ($items->find() as $obj) {
         echo $obj->sku() . ',';
         echo $obj->quantity() . ',';
         echo '"' . $obj->brand() . '",';
         echo '"' . $obj->description() . '",';
         echo '"' . $obj->caseSize() . '",';
         printf('%.2f', $obj->unitCost() * $obj->caseSize() * $obj->quantity());
         echo "\r\n";
     }
 }

 function process_file($linedata)
 {
     global $FANNIE_OP_DB, $FANNIE_TRANS_DB;
     $dbc = FannieDB::get($FANNIE_TRANS_DB);
     $mn_index = $this->get_column_index('memnum');
     $amt_index = $this->get_column_index('amt');
     $date_index = $this->get_column_index('date');
     $dept_index = $this->get_column_index('dept');
     $trans_index = $this->get_column_index('transID');
     // prepare statements
     $insP = $dbc->prepare_statement("INSERT INTO stockpurchases (card_no,stockPurchase,\n                tdate,trans_num,dept) VALUES (?,?,?,?,?)");
     foreach ($linedata as $line) {
         // get info from file and member-type default settings
         // if applicable
         $cardno = $line[$mn_index];
         if (!is_numeric($cardno)) {
             continue;
         }
         // skip bad record
         $amt = $line[$amt_index];
         $date = $date_index !== False ? $line[$date_index] : '0000-00-00';
         $dept = $dept_index !== False ? $line[$dept_index] : 0;
         $trans = $trans_index !== False ? $line[$trans_index] : "";
         $insR = $dbc->exec_statement($insP, array($cardno, $amt, $date, $trans, $dept));
         if ($insR === False) {
             $this->stats['errors'][] = "Error importing entry for member {$cardno}";
         } else {
             $this->stats['imported']++;
         }
     }
     return true;
 }

 function process_file($linedata)
 {
     global $FANNIE_OP_DB;
     $dbc = FannieDB::get($FANNIE_OP_DB);
     $mn_index = $this->get_column_index('memnum');
     $gross_index = $this->get_column_index('gross');
     $discount_index = $this->get_column_index('discount');
     $reward_index = $this->get_column_index('reward');
     $net_index = $this->get_column_index('net');
     $pat_index = $this->get_column_index('pat');
     $cash_index = $this->get_column_index('cash');
     $equity_index = $this->get_column_index('equity');
     $fy_index = $this->get_column_index('fy');
     // prepare statements
     $insP = $dbc->prepare_statement("INSERT INTO patronage (cardno,purchase,discounts,rewards,net_purch,tot_pat,\n            cash_pat,equit_pat,FY) VALUES (?,?,?,?,?,?,?,?,?)");
     foreach ($linedata as $line) {
         // get info from file and member-type default settings
         // if applicable
         $cardno = $line[$mn_index];
         if (!is_numeric($cardno)) {
             continue;
         }
         // skip bad record
         $args = array($cardno, $line[$gross_index], $line[$discount_index], $line[$reward_index], $line[$net_index], $line[$pat_index], $line[$cash_index], $line[$equity_index], $line[$fy_index]);
         $insR = $dbc->execute($insP, $args);
         if ($insR) {
             $this->stats['imported']++;
         } else {
             $this->stats['errors'][] = $dbc->error();
         }
     }
     return true;
 }

 public function fetch_report_data()
 {
     global $FANNIE_PLUGIN_SETTINGS, $FANNIE_OP_DB;
     $dbc = FannieDB::get($FANNIE_PLUGIN_SETTINGS['GiveUsMoneyDB']);
     $month = FormLib::get('month', date('n'));
     $year = FormLib::get('year', date('Y'));
     $end_of_last_month = mktime(0, 0, 0, $month, 0, $year);
     $ts = mktime(0, 0, 0, $month, 1, $year);
     $end_of_next_month = mktime(0, 0, 0, $month, date('t', $ts), $year);
     $end_last_dt = new DateTime(date('Y-m-d', $end_of_last_month));
     $end_next_dt = new DateTime(date('Y-m-d', $end_of_next_month));
     $loans = new GumLoanAccountsModel($dbc);
     $data = array();
     foreach ($loans->find('loanDate') as $loan) {
         $record = array($loan->accountNumber(), number_format($loan->principal(), 2), number_format($loan->interestRate() * 100, 2) . '%', $loan->termInMonths(), date('Y-m-d', strtotime($loan->loanDate())));
         $loanDT = new DateTime(date('Y-m-d', strtotime($loan->loanDate())));
         $days1 = $loanDT->diff($end_last_dt)->format('%r%a');
         $days2 = $loanDT->diff($end_next_dt)->format('%r%a');
         $bal_before = $loan->principal() * pow(1.0 + $loan->interestRate(), $days1 / 365.25);
         if ($days1 < 0) {
             $bal_before = $loan->principal();
         }
         $bal_after = $loan->principal() * pow(1.0 + $loan->interestRate(), $days2 / 365.25);
         if ($days2 < 0) {
             $bal_after = $loan->principal();
         }
         $record[] = number_format($bal_before, 2);
         $record[] = number_format($bal_after, 2);
         $record[] = number_format($bal_after - $bal_before, 2);
         $data[] = $record;
     }
     return $data;
 }

 public function fetch_report_data()
 {
     global $FANNIE_PLUGIN_SETTINGS, $FANNIE_OP_DB;
     $dbc = FannieDB::get($FANNIE_PLUGIN_SETTINGS['GiveUsMoneyDB']);
     // compound interest calculation is MySQL-specific
     $query = 'SELECT c.CardNo AS card_no,
                 c.FirstName, 
                 c.LastName,
                 m.street,
                 m.city,
                 m.state,
                 m.zip,
                 m.phone,
                 m.email_1
               FROM ' . $FANNIE_OP_DB . $dbc->sep() . 'custdata AS c
                     LEFT JOIN ' . $FANNIE_OP_DB . $dbc->sep() . 'meminfo AS m ON c.CardNo=m.card_no
               WHERE c.personNum=1
                 AND (
                     c.CardNo IN (SELECT card_no FROM GumLoanAccounts)
                     OR
                     c.CardNo IN (SELECT card_no FROM GumEquityShares)
                 )
               ORDER BY c.CardNo';
     $result = $dbc->query($query);
     $data = array();
     while ($row = $dbc->fetch_row($result)) {
         $record = array($row['card_no'], $row['LastName'], $row['FirstName'], $row['street'], $row['city'], $row['state'], $row['zip'], $row['phone'], $row['email_1']);
         $data[] = $record;
     }
     return $data;
 }

 public function get_view()
 {
     $dbc = FannieDB::getReadOnly($this->config->get('OP_DB'));
     $code = new ReasoncodesModel($dbc);
     $ret = '<form method="post">
         <p><button type="submit" class="btn btn-default">Save Reasons</button></p>
         <table class="table table-bordered">
         <tr>
             <th>#</th>
             <th>Reason</th>
             <th>Current Accounts</th>
         </tr>';
     $countP = $dbc->prepare('
         SELECT COUNT(*)
         FROM suspensions
         WHERE (reasoncode & ?) <> 0
     ');
     for ($i = 0; $i < 30; $i++) {
         $code->mask(1 << $i);
         $count = $dbc->getValue($countP, array(1 << $i));
         $reason = $code->load() ? $code->textStr() : '';
         $ret .= sprintf('<tr>
             <td>%d<input type="hidden" name="mask[]" value="%d" /></td>
             <td><input type="text" class="form-control" name="reason[]" value="%s" />
             <td>%d</td>
             </tr>', $i + 1, 1 << $i, $reason, $count);
     }
     $ret .= '</table>';
     $ret .= '<p><button type="submit" class="btn btn-default">Save Reasons</button></p>';
     $ret .= '</form>';
     return $ret;
 }

 public function fetch_report_data()
 {
     $item = array(array());
     $upc = array();
     $brand = array();
     $desc = array();
     $vendor = array();
     $cost = array();
     $price = array();
     global $FANNIE_OP_DB, $FANNIE_URL;
     $dbc = FannieDB::get($FANNIE_OP_DB);
     $query = "SELECT p.upc, \n                    p.description, \n                    p.brand, \n                    p.normal_price,\n                    p.cost, \n                    v.vendorName, \n                    fs.name\n                FROM products AS p\n                    LEFT JOIN productUser AS pu ON pu.upc=p.upc\n                    LEFT JOIN vendorItems AS vi ON vi.upc=p.upc\n                    LEFT JOIN vendors AS v ON v.vendorID=vi.vendorID\n                    LEFT JOIN prodPhysicalLocation AS pl ON pl.upc=p.upc\n                    LEFT JOIN FloorSections AS fs ON fs.floorSectionID=pl.floorSectionID\n                WHERE p.inUse=1\n                    AND p.wicable=1\n                ;";
     $result = $dbc->query($query);
     while ($row = $dbc->fetch_row($result)) {
         $item[$row['upc']][0] = $row['upc'];
         $item[$row['upc']][1] = $row['brand'];
         $item[$row['upc']][2] = $row['description'];
         if ($row['vendorName'] == NULL) {
             $item[$row['upc']][3] = 'unknown';
         } else {
             $item[$row['upc']][3] = $row['vendorName'];
         }
         $item[$row['upc']][4] = $row['cost'];
         $item[$row['upc']][5] = $row['normal_price'];
         $item[$row['upc']][6] = $row['name'];
     }
     if (mysql_errno() > 0) {
         echo mysql_errno() . ": " . mysql_error() . "<br>";
     }
     echo count($item) . " WIC-able items found in system.<BR>";
     sort($item);
     return $item;
 }

 public function get_view()
 {
     global $FANNIE_PLUGIN_SETTINGS;
     $dbc = FannieDB::get($FANNIE_PLUGIN_SETTINGS['GiveUsMoneyDB']);
     $model = new GumLoanValidTermsModel($dbc);
     $ret = '<table cellspacing="0" cellpadding="4" border="1">';
     $ret .= '<tr><th>Length (Months)</th><th>Limit ($)</th><th>&nbsp;</th></tr>';
     $sum = 0.0;
     foreach ($model->find('termInMonths') as $obj) {
         $ret .= sprintf('<tr>
                         <td>%d</td>
                         <td>%s</td>
                         <td><a href="GumValidTermsPage.php?delete=%d">Delete</a></td>
                         </tr>', $obj->termInMonths(), number_format($obj->totalPrincipalLimit(), 2), $obj->gumLoanValidTermID());
         $sum += $obj->totalPrincipalLimit();
     }
     $ret .= sprintf('<tr><th>Total</th><td>%s</td><td>&nbsp;</td></tr>', number_format($sum, 2));
     $ret .= '</table>';
     $ret .= '<hr />';
     $ret .= '<form action="GumValidTermsPage.php" method="post">';
     $ret .= '<b>Add New</b><br />';
     $ret .= '<b>Length</b>: <input type="text" size="4" name="length" />';
     $ret .= '&nbsp;&nbsp;&nbsp;';
     $ret .= '<b>Limit</b>: <input type="text" size="8" name="limit" value="0" />';
     $ret .= '&nbsp;&nbsp;&nbsp;';
     $ret .= '<input type="submit" value="Add" />';
     $ret .= '</form>';
     return $ret;
 }

 public function get_view()
 {
     global $FANNIE_PLUGIN_SETTINGS;
     $dbc = FannieDB::get($FANNIE_PLUGIN_SETTINGS['GiveUsMoneyDB']);
     $model = new GumLoanDefaultInterestRatesModel($dbc);
     $ret = '<table cellspacing="0" cellpadding="4" border="1">';
     $ret .= '<tr><th>Between</th><th>Rate (%)</th><th>&nbsp;</th></tr>';
     foreach ($model->find('interestRate') as $obj) {
         $ret .= sprintf('<tr>
                         <td>%s and %s</td>
                         <td>%.2f</td>
                         <td><a href="GumDefaultRatesPage.php?delete=%d">Delete</a></td>
                         </tr>', number_format($obj->lowerBound(), 2), number_format($obj->upperBound(), 2), $obj->interestRate() * 100, $obj->gumLoanDefaultInterestRateID());
     }
     $ret .= '</table>';
     $ret .= '<hr />';
     $ret .= '<form action="GumDefaultRatesPage.php" method="post">';
     $ret .= '<b>Add New</b><br />';
     $ret .= '<b>Rate (%)</b>: <input type="text" size="4" name="rate" />';
     $ret .= '&nbsp;&nbsp;&nbsp;';
     $ret .= '<b>Between</b>: <input type="text" size="8" name="lower" value="0" />';
     $ret .= ' and ';
     $ret .= '<input type="text" size="8" name="upper" value="0" />';
     $ret .= '&nbsp;&nbsp;&nbsp;';
     $ret .= '<input type="submit" value="Add" />';
     $ret .= '</form>';
     return $ret;
 }

    public function get_view()
    {
        global $FANNIE_OP_DB, $FANNIE_TRANS_DB;
        $dbc = FannieDB::get($FANNIE_OP_DB);
        ob_start();
        ?>
        <div class="well">
        Step two: calculate totals sales and percent discounts per member for the year
        </div>
        <form action="<?php 
        echo $_SERVER['PHP_SELF'];
        ?>
" method="get">
        <label>Fiscal Year</label>
        <select name="id" class="form-control">
        <?php 
        $q = $dbc->prepare_statement("\n            SELECT min_year,\n                max_year \n            FROM {$FANNIE_TRANS_DB}" . $dbc->sep() . "dlog_patronage");
        $r = $dbc->exec_statement($q);
        $w = $dbc->fetch_row($r);
        printf('<option>%d</option>', $w[0]);
        printf('<option>%d</option>', $w[1]);
        ?>
        </select>
        <p>
            <button type="submit" class="btn btn-default">Calculate Purchases</button>
        </p>
        </form>
        <?php 
        return ob_get_clean();
    }

 public function run()
 {
     $dbc = FannieDB::get($this->config->get('TRANS_DB'));
     // build department list
     $ret = preg_match_all("/[0-9]+/", $this->config->get('AR_DEPARTMENTS'), $depts);
     $depts = array_pop($depts);
     $errors = $this->logActivity($dbc, $depts);
     foreach ($errors as $error) {
         $this->cronMsg($error, FannieLogger::ERROR);
     }
     // rebuild ar history sum table
     if ($this->rebuildCacheTable($dbc) === false) {
         $this->cronMsg('Error rebuilding ar_history_sum table', FannieLogger::ERROR);
     }
     // update custdata balance fields
     if ($this->updateBalances($dbc) === false) {
         $this->cronMsg('Error reloading custdata balances', FannieLogger::ERROR);
     }
     $this->cronMsg('Finished every-day tasks.', FannieLogger::INFO);
     /* turnover view/cache base tables for WFC end-of-month reports */
     if (date('j') == 1) {
         $this->endOfMonthCaches($dbc);
         $this->cronMsg('Finished first-of-month tasks.', FannieLogger::INFO);
     }
 }

 function fetch_report_data()
 {
     global $FANNIE_OP_DB, $FANNIE_PLUGIN_SETTINGS;
     $dbc = FannieDB::get($FANNIE_OP_DB);
     $warehouseDB = $FANNIE_PLUGIN_SETTINGS['WarehouseDatabase'] . $dbc->sep();
     $query = '
         SELECT COUNT(*) AS numTransactions,
             AVG(-tenderTotal) AS avgTenderTotal,
             AVG(retailQty+nonRetailQty) AS avgItemQty,
             AVG(retailTotal+nonRetailtotal) AS avgItemTotal,
             AVG(retailQty) AS avgRetailQty,
             AVG(retailTotal) AS avgRetailTotal,
             m.memDesc,
             m.custdataType
         FROM ' . $warehouseDB . 'transactionSummary AS t
             LEFT JOIN memtype AS m ON t.memType=m.memType
         WHERE date_id BETWEEN ? AND ?
         GROUP BY t.memType,
             m.memDesc,
             m.custdataType';
     $prep = $dbc->prepare($query);
     $date1 = FormLib::getDate('date1', date('Ymd'), 'Ymd');
     $date2 = FormLib::getDate('date2', date('Ymd'), 'Ymd');
     $result = $dbc->execute($prep, array($date1, $date2));
     $report = array();
     while ($w = $dbc->fetch_row($result)) {
         $report[] = array($w['memDesc'], $w['custdataType'] == 'PC' ? 'Yes' : 'No', sprintf('%d', $w['numTransactions']), sprintf('%.2f', $w['avgTenderTotal']), sprintf('%.2f', $w['avgItemQty']), sprintf('%.2f', $w['avgItemTotal']), sprintf('%.2f', $w['avgRetailQty']), sprintf('%.2f', $w['avgRetailTotal']));
     }
     return $report;
 }

 public function fetch_report_data()
 {
     global $FANNIE_PLUGIN_SETTINGS, $FANNIE_OP_DB;
     $dbc = FannieDB::get($FANNIE_PLUGIN_SETTINGS['GiveUsMoneyDB']);
     // compound interest calculation is MySQL-specific
     $query = 'SELECT c.CardNo AS card_no,
                 c.FirstName, 
                 c.LastName,
                 l.loanDate,
                 CASE WHEN l.principal IS NULL THEN 0 ELSE l.principal END as principal,
                 CASE WHEN l.termInMonths IS NULL THEN 0 ELSE l.termInMonths END as termInMonths,
                 CASE WHEN l.termInMonths IS NULL THEN \'\' ELSE DATE_ADD(loanDate, INTERVAL termInMonths MONTH) END as maturityDate,
                 CASE WHEN l.interestRate IS NULL THEN 0 ELSE l.interestRate END as interestRate,
                 CASE WHEN e.shares IS NULL THEN 0 ELSE e.shares END as shares,
                 CASE WHEN l.principal IS NULL THEN 0
                     ELSE principal * POW(1+interestRate, DATEDIFF(DATE_ADD(loanDate, INTERVAL termInMonths MONTH), loanDate)/365.25)
                 END as maturityAmount
               FROM ' . $FANNIE_OP_DB . $dbc->sep() . 'custdata AS c
                     LEFT JOIN GumLoanAccounts AS l 
                         ON l.card_no=c.CardNo AND c.personNum=1
                     LEFT JOIN (
                         SELECT card_no, SUM(shares) as shares
                         FROM GumEquityShares
                         GROUP BY card_no
                     ) AS e ON c.cardNo=e.card_no AND c.personNum=1
               WHERE l.card_no IS NOT NULL OR e.card_no IS NOT NULL
               ORDER BY l.card_no, l.loanDate';
     $result = $dbc->query($query);
     $data = array();
     while ($row = $dbc->fetch_row($result)) {
         $record = array($row['card_no'], $row['LastName'], $row['FirstName'], $row['loanDate'] == '' ? 'n/a' : date('Y-m-d', strtotime($row['loanDate'])), sprintf('%.2f', $row['principal']), sprintf('%.2f', $row['interestRate'] * 100), $row['termInMonths'], $row['maturityDate'] == '' ? 'n/a' : date('Y-m-d', strtotime($row['maturityDate'])), sprintf('%.2f', $row['maturityAmount']), $row['shares']);
         $data[] = $record;
     }
     return $data;
 }

 function get_id_view()
 {
     global $FANNIE_OP_DB;
     $dbc = FannieDB::get($FANNIE_OP_DB);
     $upc = BarcodeLib::padUPC($this->id);
     $itemP = $dbc->prepare_statement('SELECT p.description,p.special_price,
                     CASE WHEN u.likeCode IS NULL THEN -1 ELSE u.likeCode END as lc
                     FROM products AS p LEFT JOIN upcLike AS u
                     ON p.upc=u.upc WHERE p.upc=?');
     $itemR = $dbc->exec_statement($itemP, array($upc));
     if ($dbc->num_rows($itemR) == 0) {
         return '<div class="alert alert-danger">Item not found</div>';
     }
     $itemW = $dbc->fetch_row($itemR);
     $ret = '<form method="post" action="EndItemSale.php">
         <input type="hidden" name="id" value="' . $upc . '" />';
     $ret .= sprintf('<p>%s is currently on sale for $%.2f', $itemW['description'], $itemW['special_price']);
     $batchP = $dbc->prepare_statement("SELECT b.batchName, b.batchID, l.upc FROM batches AS b \n            LEFT JOIN batchList as l\n            on b.batchID=l.batchID WHERE '" . date('Y-m-d') . "' BETWEEN b.startDate\n            AND b.endDate AND (l.upc=? OR l.upc=?)");
     $batchR = $dbc->exec_statement($batchP, array($upc, 'LC' . $itemW['lc']));
     if ($dbc->num_rows($batchR) == 0) {
         $ret .= '<div class="alert alert-warning">The item does not appear to be in an active batch</div>';
     } else {
         $batchW = $dbc->fetch_row($batchR);
         $ret .= '<br />The item will be removed from the batch <strong>' . $batchW['batchName'] . '</strong>';
         $ret .= sprintf('<input type="hidden" name="batchID" value="%d" />
                 <input type="hidden" name="batchUPC" value="%s" />', $batchW['batchID'], $batchW['upc']);
     }
     $ret .= '<br /><button type="submit" class="btn btn-default" id="button">Take item off sale</button>';
     $ret .= '</p>';
     return $ret;
 }

 public function get_id_view()
 {
     $dbc = FannieDB::get($this->config->get('OP_DB'));
     $model = new ProductsModel($dbc);
     $model->upc(BarcodeLib::padUPC($this->id));
     if (!$model->load()) {
         return '<div class="alert alert-danger">Item ' . $this->id . ' does not exist</dv>';
     }
     $ret = '<form action="' . $_SERVER['PHP_SELF'] . '" method="post">
         <input type="hidden" name="id" value="' . $model->upc() . '" />
         <p>
             Create a copy of ' . $model->upc() . ' (' . $model->description() . ')
         </p>
         <div class="form-group">
             <label>New Item UPC</label>
             <input type="text" name="new-upc" class="form-control" id="new-upc" required />
         </div>
         <p>
             <button type="submit" class="btn btn-default">Clone Item</button>
         </p>
         </form>';
     $this->addOnloadCommand("enableLinea('#new-upc');\n");
     $this->addOnloadCommand("\$('#new-upc').focus();\n");
     return $ret;
 }