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