function show_filter()
{
global $TPL;
global $defaults;
$_FORM = productSale::load_form_data($defaults);
$arr = productSale::load_productSale_filter($_FORM);
is_array($arr) and $TPL = array_merge($TPL, $arr);
include_template("templates/productSaleListFilterS.tpl");
}
function render()
{
$current_user =& singleton("current_user");
global $TPL;
$ops["return"] = "array";
$ops["status"] = array("admin");
$rows = productSale::get_list($ops);
$TPL["saleListRows"] = $rows;
if ($TPL["saleListRows"]) {
return true;
}
}
function show_productSale_list($productSaleID, $template)
{
global $TPL;
global $productSaleItemsDoExist;
$productSale = new productSale();
$productSale->set_id($productSaleID);
$productSale->select();
$productSale->set_tpl_values();
$taxName = config::get_config_item("taxName");
$product = new product();
$ops = $product->get_assoc_array("productID", "productName");
$query = prepare("SELECT *\n FROM productSaleItem \n WHERE productSaleID = %d", $productSaleID);
$db = new db_alloc();
$db->query($query);
while ($db->next_record()) {
$productSaleItemsDoExist = true;
$productSaleItem = new productSaleItem();
$productSaleItem->read_db_record($db);
$productSaleItem->set_tpl_values();
$TPL["itemSellPrice"] = $productSaleItem->get_value("sellPrice");
$TPL["itemMargin"] = $productSaleItem->get_amount_margin();
$TPL["itemSpent"] = $productSaleItem->get_amount_spent();
$TPL["itemEarnt"] = $productSaleItem->get_amount_earnt();
$TPL["itemOther"] = $productSaleItem->get_amount_other();
$TPL["itemCosts"] = page::money(config::get_config_item("currency"), product::get_buy_cost($productSaleItem->get_value("productID")) * $productSaleItem->get_value("quantity"), "%s%mo %c");
$TPL["itemTotalUnallocated"] = $productSaleItem->get_amount_unallocated();
$TPL["productList_dropdown"] = page::select_options($ops, $productSaleItem->get_value("productID"));
$TPL["productLink"] = "<a href=\"" . $TPL["url_alloc_product"] . "productID=" . $productSaleItem->get_value("productID") . "\">" . page::htmlentities($ops[$productSaleItem->get_value("productID")]) . "</a>";
$TPL["transactions"] = $productSale->get_transactions($productSaleItem->get_id());
if ($taxName) {
$TPL["sellPriceTax_check"] = sprintf(" <input type='checkbox' name='sellPriceIncTax[]' value='%d'%s> inc %s", $productSaleItem->get_id(), $productSaleItem->get_value("sellPriceIncTax") ? ' checked' : '', $taxName);
$TPL["sellPriceTax_label"] = $productSaleItem->get_value("sellPriceIncTax") ? " inc " . $taxName : " ex " . $taxName;
}
include_template($template);
}
}
function load_productSale_filter($_FORM)
{
$current_user =& singleton("current_user");
// display the list of project name.
$db = new db_alloc();
if (!$_FORM['showAllProjects']) {
$filter = "WHERE projectStatus = 'Current' ";
}
$query = prepare("SELECT projectID AS value, projectName AS label FROM project {$filter} ORDER by projectName");
$rtn["show_project_options"] = page::select_options($query, $_FORM["projectID"], 70);
// display the list of user name.
if (have_entity_perm("productSale", PERM_READ, $current_user, false)) {
$rtn["show_userID_options"] = page::select_options(person::get_username_list(), $_FORM["personID"]);
} else {
$person = new person();
$person->set_id($current_user->get_id());
$person->select();
$person_array = array($current_user->get_id() => $person->get_name());
$rtn["show_userID_options"] = page::select_options($person_array, $_FORM["personID"]);
}
// display a list of status
$status_array = productSale::get_statii();
unset($status_array["create"]);
$rtn["show_status_options"] = page::select_options($status_array, $_FORM["status"]);
// display the date from filter value
$rtn["showAllProjects"] = $_FORM["showAllProjects"];
$options["clientStatus"] = array("Current");
$options["return"] = "dropdown_options";
$ops = client::get_list($options);
$ops = array_kv($ops, "clientID", "clientName");
$rtn["clientOptions"] = page::select_options($ops, $_FORM["clientID"]);
// Get
$rtn["FORM"] = "FORM=" . urlencode(serialize($_FORM));
return $rtn;
}
function save_invoice_productSaleItems($invoiceID, $productSaleID)
{
$productSale = new productSale();
$productSale->set_id($productSaleID);
$productSale->select();
$db = new db_alloc();
$q = prepare("SELECT * FROM productSaleItem WHERE productSaleID = %d", $productSale->get_id());
$q1 = $db->query($q);
while ($row = $db->row($q1)) {
$q = prepare("SELECT * FROM invoiceItem WHERE productSaleID = %d AND productSaleItemID = %d", $productSaleID, $row["productSaleItemID"]);
$db = new db_alloc();
$q2 = $db->query($q);
$r2 = $db->row($q2);
$ii = new invoiceItem();
if ($r2) {
$ii->set_id($r2["invoiceItemID"]);
}
$ii->currency = $row["sellPriceCurrencyTypeID"];
$ii->set_value("invoiceID", $invoiceID);
$ii->set_value("productSaleID", $productSale->get_id());
$ii->set_value("productSaleItemID", $row["productSaleItemID"]);
$ii->set_value("iiMemo", "Sale (" . $productSale->get_id() . ") item for " . person::get_fullname($productSale->get_value("personID")) . ", " . $row["description"]);
$ii->set_value("iiQuantity", $row["quantity"]);
$row["sellPrice"] = page::money($ii->currency, $row["sellPrice"] / $row["quantity"], "%mo");
$ii->set_value("iiUnitPrice", $row["sellPrice"]);
$ii->set_value("iiAmount", $row["sellPrice"] * $row["quantity"]);
$d = $productSale->get_value("productSaleDate") or $d = $productSale->get_value("productSaleModifiedTime") or $d = $productSale->get_value("productSaleCreatedTime");
$ii->set_value("iiDate", $d);
//$ii->set_value("iiTax",config::get_config_item("taxPercent")); // product sale items are always excl GST
$ii->save();
}
}
function show_new_invoiceItem($template)
{
global $TPL;
global $invoice;
global $invoiceID;
$current_user =& singleton("current_user");
// Don't show entry form if no ID
if (!$invoiceID) {
return;
}
$TPL["div1"] = "";
$TPL["div2"] = " class=\"hidden\"";
$TPL["div3"] = " class=\"hidden\"";
$TPL["div4"] = " class=\"hidden\"";
if (is_object($invoice) && $invoice->get_value("invoiceStatus") == 'edit' && $current_user->have_role('admin')) {
// If we are editing an existing invoiceItem
if (is_array($_POST["invoiceItem_edit"])) {
$invoiceItemID = key($_POST["invoiceItem_edit"]);
$invoiceItem = new invoiceItem();
$invoiceItem->currency = $invoice->get_value("currencyTypeID");
$invoiceItem->set_id($invoiceItemID);
$invoiceItem->select();
$invoiceItem->set_tpl_values("invoiceItem_");
$TPL["invoiceItem_buttons"] = '
<button type="submit" name="invoiceItem_delete[' . $invoiceItemID . ']" value="1" class="delete_button">Delete<i class="icon-trash"></i></button>
<button type="submit" name="invoiceItem_save[' . $invoiceItemID . ']" value="1" class="save_button">Save Item<i class="icon-edit"></i></button>
';
if ($invoiceItem->get_value("timeSheetID")) {
unset($TPL["div2"]);
$TPL["div1"] = " class=\"hidden\"";
$TPL["sbs_link"] = "timeSheet_ii";
} else {
if ($invoiceItem->get_value("expenseFormID")) {
unset($TPL["div3"]);
$TPL["div1"] = " class=\"hidden\"";
$TPL["sbs_link"] = "expenseForm_ii";
} else {
if ($invoiceItem->get_value("productSaleID")) {
unset($TPL["div4"]);
$TPL["div1"] = " class=\"hidden\"";
$TPL["sbs_link"] = "productSale_ii";
}
}
}
// Else default values for creating a new invoiceItem
} else {
$invoiceItem = new invoiceItem();
$invoiceItem->set_values("invoiceItem_");
$TPL["invoiceItem_buttons"] = '
<button type="submit" name="invoiceItem_save" value="1" class="save_button">Add Item<i class="icon-plus-sign"></i></button>
';
}
// Build dropdown lists for timeSheet and expenseForm options.
if ($invoice->get_value("clientID")) {
// Time Sheet dropdown
$db = new db_alloc();
$q = prepare("SELECT projectID FROM project WHERE clientID = %d", $invoice->get_value("clientID"));
$db->query($q);
$projectIDs = array();
while ($row = $db->row()) {
$projectIDs[] = $row["projectID"];
}
if ($projectIDs) {
$q = prepare("SELECT timeSheet.*, project.projectName \n FROM timeSheet\n LEFT JOIN project ON project.projectID = timeSheet.projectID \n WHERE timeSheet.projectID IN (%s) \n AND timeSheet.status != 'finished'\n GROUP BY timeSheet.timeSheetID\n ORDER BY timeSheetID\n ", $projectIDs);
$db->query($q);
$timeSheetStatii = timeSheet::get_timeSheet_statii();
while ($row = $db->row()) {
$t = new timeSheet();
$t->read_db_record($db);
$t->load_pay_info();
$dollars = $t->pay_info["total_customerBilledDollars"] or $dollars = $t->pay_info["total_dollars"];
$timeSheetOptions[$row["timeSheetID"]] = "Time Sheet #" . $t->get_id() . " " . $row["dateFrom"] . " " . $dollars . " for " . person::get_fullname($row["personID"]) . ", Project: " . $row["projectName"] . " [" . $timeSheetStatii[$t->get_value("status")] . "]";
}
$TPL["timeSheetOptions"] = page::select_options($timeSheetOptions, $invoiceItem->get_value("timeSheetID"), 150);
}
// Expense Form dropdown
$db = new db_alloc();
$q = prepare("SELECT expenseFormID, expenseFormCreatedUser\n FROM expenseForm \n WHERE expenseFormFinalised = 1 \n AND seekClientReimbursement = 1\n AND clientID = %d\n ORDER BY expenseForm.expenseFormCreatedTime", $invoice->get_value("clientID"));
$db->query($q);
while ($row = $db->row()) {
$expenseFormOptions[$row["expenseFormID"]] = "Expense Form #" . $row["expenseFormID"] . " " . page::money(config::get_config_item("currency"), expenseForm::get_abs_sum_transactions($row["expenseFormID"]), "%s%m %c") . " " . person::get_fullname($row["expenseFormCreatedUser"]);
}
if ($invoiceItem->get_value("expenseFormID")) {
$id = $invoiceItem->get_value("expenseFormID");
}
$TPL["expenseFormOptions"] = page::select_options($expenseFormOptions, $id, 90);
$q = prepare("SELECT *\n FROM productSale\n WHERE clientID = %d\n AND status = 'admin'\n ", $invoice->get_value("clientID"));
$invoice->get_value("projectID") and $q .= prepare(" AND projectID = %d", $invoice->get_value("projectID"));
$db->query($q);
while ($row = $db->row()) {
$productSale = new productSale();
$productSale->set_id($row["productSaleID"]);
$productSale->select();
$ps_row = $productSale->get_amounts();
$productSaleOptions[$row["productSaleID"]] = "Sale #" . $row["productSaleID"] . " " . $ps_row["total_sellPrice"] . " " . person::get_fullname($row["personID"]);
}
if ($invoiceItem->get_value("productSaleID")) {
$id = $invoiceItem->get_value("productSaleID");
}
$TPL["productSaleOptions"] = page::select_options($productSaleOptions, $id, 90);
}
$TPL["invoiceItem_iiQuantity"] or $TPL["invoiceItem_iiQuantity"] = 1;
$TPL["invoiceItem_invoiceID"] = $invoice->get_id();
include_template($template);
}
}
public static function get_list($_FORM)
{
$current_user =& singleton("current_user");
global $TPL;
/*
* This is the definitive method of getting a list of transactions that need a sophisticated level of filtering
*
*/
$_FORM["tfIDs"] = transaction::reduce_tfs($_FORM);
// Non-admin users must specify a valid TF
if (!$current_user->have_role("admin") && !$_FORM["tfIDs"]) {
return;
}
$filter = transaction::get_list_filter($_FORM);
$debug = $_FORM["debug"];
$debug and print "\n<pre>_FORM: " . print_r($_FORM, 1) . "</pre>";
$debug and print "\n<pre>filter: " . print_r($filter, 1) . "</pre>";
$_FORM["return"] or $_FORM["return"] = "html";
$filter["prevBalance"] and $filter2[] = $filter["prevBalance"];
$filter["tfIDs"] and $filter2[] = $filter["tfIDs"];
$filter2 and $filter2[] = " (status = 'approved') ";
unset($filter["prevBalance"]);
if (is_array($filter2) && count($filter2)) {
$filter2 = " WHERE " . implode(" AND ", $filter2);
}
if (is_array($filter) && count($filter)) {
$filter = " WHERE " . implode(" AND ", $filter);
}
$_FORM["sortTransactions"] or $_FORM["sortTransactions"] = "transactionDate";
$order_by = "ORDER BY " . $_FORM["sortTransactions"];
// Determine opening balance
if (is_array($_FORM['tfIDs']) && count($_FORM['tfIDs'])) {
$q = prepare("SELECT SUM( IF(fromTfID IN (%s),-amount,amount) * pow(10,-currencyType.numberToBasic) * exchangeRate) AS balance\n FROM transaction \n LEFT JOIN currencyType ON currencyType.currencyTypeID = transaction.currencyTypeID\n " . $filter2, $_FORM['tfIDs']);
$debug and print "\n<br>QUERY: " . $q;
$db = new db_alloc();
$db->query($q);
$db->row();
$_FORM["opening_balance"] = $db->f("balance");
$running_balance = $db->f("balance");
}
$q = "SELECT *, \n (amount * pow(10,-currencyType.numberToBasic)) as amount1,\n (amount * pow(10,-currencyType.numberToBasic) * exchangeRate) as amount2,\n if(transactionModifiedTime,transactionModifiedTime,transactionCreatedTime) AS transactionSortDate,\n tf1.tfName as fromTfName,\n tf2.tfName as tfName\n FROM transaction \n LEFT JOIN currencyType ON currencyType.currencyTypeID = transaction.currencyTypeID\n LEFT JOIN tf tf1 ON transaction.fromTfID = tf1.tfID\n LEFT JOIN tf tf2 ON transaction.tfID = tf2.tfID\n " . $filter . " \n " . $order_by;
$debug and print "\n<br>QUERY2: " . $q;
$db = new db_alloc();
$db->query($q);
$for_cyber = config::for_cyber();
while ($row = $db->next_record()) {
#echo "<pre>".print_r($row,1)."</pre>";
$i++;
$t = new transaction();
if (!$t->read_db_record($db)) {
continue;
}
$print = true;
// If the destination of this TF is not the current TfID, then invert the $amount
$amount = $row["amount2"];
if (!in_array($row["tfID"], (array) $_FORM["tfIDs"])) {
$amount = -$amount;
$row["amount1"] = -$row["amount1"];
}
$row["amount"] = $amount;
$row["transactionURL"] = $t->get_url();
$row["transactionName"] = $t->get_name($_FORM);
$row["transactionLink"] = $t->get_transaction_link($_FORM);
$row["transactionTypeLink"] = $t->get_transaction_type_link() or $row["transactionTypeLink"] = $row["transactionType"];
$row["transactionSortDate"] = format_date("Y-m-d", $row["transactionSortDate"]);
$row["fromTfIDLink"] = "<a href=\"" . $TPL["url_alloc_transactionList"] . "tfID=" . $row["fromTfID"] . "\">" . page::htmlentities($row["fromTfName"]) . "</a>";
$row["tfIDLink"] = "<a href=\"" . $TPL["url_alloc_transactionList"] . "tfID=" . $row["tfID"] . "\">" . page::htmlentities($row["tfName"]) . "</a>";
if ($t->get_value("status") == "approved") {
$running_balance += $amount;
$row["running_balance"] = page::money(config::get_config_item("currency"), $running_balance, "%m %c");
}
if ($amount > 0) {
$row["amount_positive"] = page::money($row["currencyTypeID"], $row["amount1"], "%m %c");
$total_amount_positive += $amount;
} else {
$row["amount_negative"] = page::money($row["currencyTypeID"], $row["amount1"], "%m %c");
$total_amount_negative += $amount;
}
// Cyber only hackery for ext ref field on product sales
if ($for_cyber && $row["productSaleID"]) {
$ps = new productSale();
$ps->set_id($row["productSaleID"]);
if ($ps->select()) {
$ps->get_value("extRef") and $row["product"] .= " (Ext ref: " . $ps->get_value("extRef") . ")";
}
}
$transactions[$row["transactionID"]] = $row;
}
$_FORM["total_amount_positive"] = page::money(config::get_config_item("currency"), $total_amount_positive, "%s%m %c");
$_FORM["total_amount_negative"] = page::money(config::get_config_item("currency"), $total_amount_negative, "%s%m %c");
$_FORM["running_balance"] = page::money(config::get_config_item("currency"), $running_balance, "%s%m %c");
return array("totals" => $_FORM, "rows" => (array) $transactions);
}
