function show_filter()
{
global $TPL;
global $defaults;
$_FORM = invoice::load_form_data($defaults);
$arr = invoice::load_invoice_filter($_FORM);
is_array($arr) and $TPL = array_merge($TPL, $arr);
$payment_statii = invoice::get_invoice_statii_payment();
foreach ($payment_statii as $payment_status => $label) {
$summary .= "\n" . $nbsp . invoice::get_invoice_statii_payment_image($payment_status) . " " . $label;
$nbsp = " ";
}
$TPL["status_legend"] = $summary;
include_template("templates/invoiceListFilterS.tpl");
}
public static function get_list($_FORM)
{
/*
* This is the definitive method of getting a list of invoices that need a sophisticated level of filtering
*
*/
global $TPL;
$filter1_where = invoice::get_list_filter($_FORM);
$filter2_having = invoice::get_list_filter2($_FORM);
$debug = $_FORM["debug"];
$debug and print "<pre>_FORM: " . print_r($_FORM, 1) . "</pre>";
$debug and print "<pre>filter1_where: " . print_r($filter1_where, 1) . "</pre>";
$debug and print "<pre>filter2_having: " . print_r($filter2_having, 1) . "</pre>";
$_FORM["return"] or $_FORM["return"] = "html";
is_array($filter1_where) && count($filter1_where) and $f1_where = " WHERE " . implode(" AND ", $filter1_where);
is_array($filter2_having) && count($filter2_having) and $f2_having = " HAVING " . implode(" AND ", $filter2_having);
$q1 = "CREATE TEMPORARY TABLE invoice_details\n SELECT SUM(invoiceItem.iiAmount * pow(10,-currencyType.numberToBasic)) as iiAmountSum\n , invoice.*\n , client.clientName\n FROM invoice\n LEFT JOIN invoiceItem on invoiceItem.invoiceID = invoice.invoiceID\n LEFT JOIN client ON invoice.clientID = client.clientID\n LEFT JOIN currencyType on invoice.currencyTypeID = currencyType.currencyTypeID\n {$f1_where}\n GROUP BY invoice.invoiceID\n ORDER BY invoiceDateFrom";
$db = new db_alloc();
#$db->query("DROP TABLE IF EXISTS invoice_details");
$db->query($q1);
$q2 = "SELECT invoice_details.*\n , SUM(transaction_approved.amount) as amountPaidApproved\n , SUM(transaction_pending.amount) as amountPaidPending\n , SUM(transaction_rejected.amount) as amountPaidRejected\n FROM invoice_details\n LEFT JOIN invoiceItem on invoiceItem.invoiceID = invoice_details.invoiceID\n LEFT JOIN transaction transaction_approved on invoiceItem.invoiceItemID = transaction_approved.invoiceItemID AND transaction_approved.status='approved'\n LEFT JOIN transaction transaction_pending on invoiceItem.invoiceItemID = transaction_pending.invoiceItemID AND transaction_pending.status='pending'\n LEFT JOIN transaction transaction_rejected on invoiceItem.invoiceItemID = transaction_rejected.invoiceItemID AND transaction_rejected.status='rejected'\n GROUP BY invoice_details.invoiceID\n {$f2_having}\n ORDER BY invoiceDateFrom";
// Don't do this! It doubles the totals!
//LEFT JOIN tfPerson ON tfPerson.tfID = transaction_approved.tfID OR tfPerson.tfID = transaction_pending.tfID OR tfPerson.tfID = transaction_rejected.tfID
$debug and print "<pre>Query1: " . $q1 . "</pre>";
$debug and print "<pre>Query2: " . $q2 . "</pre>";
$db->query($q2);
while ($row = $db->next_record()) {
$print = true;
$i = new invoice();
$i->read_db_record($db);
$row["amountPaidApproved"] = page::money($row["currencyTypeID"], $row["amountPaidApproved"], "%mo");
$row["amountPaidPending"] = page::money($row["currencyTypeID"], $row["amountPaidPending"], "%mo");
$row["amountPaidRejected"] = page::money($row["currencyTypeID"], $row["amountPaidRejected"], "%mo");
$row["invoiceLink"] = $i->get_invoice_link();
$payment_status = array();
$row["statii"] = invoice::get_invoice_statii();
$row["payment_statii"] = invoice::get_invoice_statii_payment();
$row["amountPaidApproved"] == $row["iiAmountSum"] and $payment_status[] = "fully_paid";
$row["amountPaidApproved"] > $row["iiAmountSum"] and $payment_status[] = "over_paid";
$row["amountPaidRejected"] > 0 and $payment_status[] = "rejected";
#$row["amountPaidApproved"] > 0 && $row["amountPaidApproved"] < $row["iiAmountSum"] and $payment_status[] = "partly_paid";
$row["amountPaidApproved"] < $row["iiAmountSum"] and $payment_status[] = "pending";
foreach ((array) $payment_status as $ps) {
$row["image"] .= invoice::get_invoice_statii_payment_image($ps);
$row["status_label"] .= $ps;
}
$row["_FORM"] = $_FORM;
$row = array_merge($TPL, (array) $row);
$rows[$row["invoiceID"]] = $row;
}
return $rows;
}
