function view($VAR)
{
global $smarty, $C_auth;
$this->construct();
$type = "view";
$this->method["{$type}"] = explode(",", $this->method["{$type}"]);
$db = new CORE_database();
$smart = $db->view($VAR, $this, $type);
$dbm = new CORE_database();
$db =& DB();
# Add the change recur schedule options to the array:
for ($i = 0; $i < count($smart); $i++) {
# get recent invoice details for this service
$p = AGILE_DB_PREFIX;
$sql = "SELECT A.id, A.date_orig, A.total_amt, A.billed_amt, A.process_status \n \t\t\t\t\t\tFROM {$p}invoice A\n \t\t\t\t\t\tWHERE A.site_id = " . DEFAULT_SITE . "\n \t\t\t\t\t\tAND\n \t\t\t\t\t\t( \n \t\t\t\t\t\t A.id={$smart[$i]['invoice_id']}\n \t\t\t\t\t\tOR\n \t\t\t\t\t\t A.id in (select distinct invoice_id from {$p}invoice_item where \n \t\t\t\t\t\t service_id={$smart[$i]['id']} )\n \t\t\t\t\t\t) \t\t\n \t\tORDER BY A.id DESC ";
# Joe rewrote the query, its dog slow
$sql = "SELECT A.id, A.date_orig, A.total_amt, A.billed_amt, A.process_status \n\t\t\t\tFROM {$p}invoice_item B inner join {$p}invoice A on\n\t\t\t\t(B.invoice_id=A.id and service_id={$smart[$i]['id']}) \n\t\t\t\tWHERE A.site_id = " . DEFAULT_SITE . " AND B.site_id = " . DEFAULT_SITE . " \n\t\t\t\tORDER BY A.id DESC";
$inv = $db->SelectLimit($sql, 5);
if ($inv != false && $inv->RecordCount() > 0) {
while (!$inv->EOF) {
if ($inv->fields['total_amt'] > $inv->fields['billed_amt'] && $inv->fields['suspend_billing'] != 1) {
$inv->fields['due'] = $inv->fields['total_amt'] - $inv->fields['billed_amt'];
}
$smart[$i]["invoice"][] = $inv->fields;
$inv->MoveNext();
}
}
# allow modification of service plan?
if (!empty($VAR['user']) && !empty($smart[$i]['product_id'])) {
} elseif (empty($VAR['user'])) {
} else {
$smart[$i]['recur_modify'] = "0";
}
# get recurring details?
if (!empty($VAR['user']) && $smart[$i]['recur_schedule_change'] == 1 && !empty($smart[$i]['product_id'])) {
$do = true;
} elseif (empty($VAR['user']) && !empty($smart[$i]['product_id'])) {
$do = true;
} else {
$do = false;
}
if ($do && $smart[$i]['date_next_invoice'] > 0 && !empty($smart[$i]['product_id'])) {
# Get the product details:
$sql = $dbm->sql_select('product', '*', "id = {$smart[$i]['product_id']}", '', $db);
$prod = $db->Execute($sql);
$fields = $prod->fields;
global $C_auth;
$g_ar = unserialize($fields["price_group"]);
if (is_array($g_arr)) {
foreach ($g_ar as $period => $price_arr) {
foreach ($price_arr as $group => $vals) {
if (@$price_arr["show"] == "1") {
if (is_numeric($group) && $C_auth->auth_group_by_account_id($smart[$i]['account_id'], $group)) {
if ($vals["price_base"] != "" && $vals["price_base"] > 0) {
if (empty($ret[$period]['base']) || $vals["price_base"] < $ret[$period]['base']) {
$ret[$period]['base'] = $vals["price_base"];
}
}
}
}
}
}
}
if (!is_array($ret)) {
if (!empty($VAR['user'])) {
$ret["{$smart[$i]["recur_schedule"]}"]["base"] = $smart[$i]["price"];
$smarty->assign('recur_price', $ret);
} else {
$smarty->assign('recur_price', false);
}
} else {
$smarty->assign('recur_price', $ret);
}
} else {
$smarty->assign('recur_price', false);
}
}
$smarty->clear_assign('service');
$smarty->assign('service', $smart);
}
