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); }