public static function download()
{
// Get default currency
$default_currency = config::get_config_item("currency");
// Get list of active currencies
$meta = new meta("currencyType");
$currencies = $meta->get_list();
foreach ((array) $currencies as $code => $currency) {
if ($code == $default_currency) {
continue;
}
if ($ret = exchangeRate::update_rate($code, $default_currency)) {
$rtn[] = $ret;
}
if ($ret = exchangeRate::update_rate($default_currency, $code)) {
$rtn[] = $ret;
}
}
return $rtn;
}
function save()
{
// These need to be in here instead of validate(), because
// validate is called after save() and we need these values set for save().
$this->get_value("currencyTypeID") or $this->set_value("currencyTypeID", config::get_config_item("currency"));
$this->get_value("destCurrencyTypeID") or $this->set_value("destCurrencyTypeID", config::get_config_item("currency"));
// The data prior to the save
$old = $this->all_row_fields;
if ($old["status"] != $this->get_value("status") && $this->get_value("status") == "approved") {
$this->set_value("dateApproved", date("Y-m-d"));
$field_changed = true;
} else {
if ($this->get_value("status") != "approved") {
$this->set_value("dateApproved", "");
}
}
if ($old["currencyTypeID"] != $this->get_value("currencyTypeID")) {
$field_changed = true;
}
if ($old["destCurrencyTypeID"] != $this->get_value("destCurrencyTypeID")) {
$field_changed = true;
}
$db = new db_alloc();
// If there already is an exchange rate set for an approved
// transaction, then there's no need to update the exchange rate
if ($this->get_value("exchangeRate") && $this->get_value("dateApproved") && !$field_changed) {
// Else update the transaction's exchange rate
} else {
$this->get_value("transactionCreatedTime") and $date = format_date("Y-m-d", $this->get_value("transactionCreatedTime"));
$this->get_value("transactionModifiedTime") and $date = format_date("Y-m-d", $this->get_value("transactionModifiedTime"));
$this->get_value("transactionDate") and $date = $this->get_value("transactionDate");
$this->get_value("dateApproved") and $date = $this->get_value("dateApproved");
$er = exchangeRate::get_er($this->get_value("currencyTypeID"), $this->get_value("destCurrencyTypeID"), $date);
if (!$er) {
alloc_error("Unable to determine exchange rate for " . $this->get_value("currencyTypeID") . " to " . $this->get_value("destCurrencyTypeID") . " for date: " . $date);
} else {
$this->set_value("exchangeRate", $er);
}
}
return parent::save();
}
$db->query($q);
$q = prepare("UPDATE productSaleItem SET sellPriceCurrencyTypeID = '%s' WHERE sellPriceCurrencyTypeID IS NULL", $_POST["currency"]);
$db->query($q);
$q = prepare("UPDATE project SET currencyTypeID = '%s' WHERE currencyTypeID IS NULL", $_POST["currency"]);
$db->query($q);
$q = prepare("UPDATE timeSheet SET currencyTypeID = '%s' WHERE currencyTypeID IS NULL", $_POST["currency"]);
$db->query($q);
$q = prepare("UPDATE invoice SET invoice.currencyTypeID = '%s' WHERE invoice.currencyTypeID IS NULL", $_POST["currency"]);
$db->query($q);
// Update currencyType table too
$q = prepare("UPDATE currencyType SET currencyTypeSeq = 1, currencyTypeActive = true WHERE currencyTypeID = '%s'", $_POST["currency"]);
$db->query($q);
$_POST["save"] = true;
}
if ($_POST["fetch_exchange_rates"]) {
$rtn = exchangeRate::download();
$rtn and $TPL["message_good"] = $rtn;
}
if ($_POST["save"]) {
if ($_POST["hoursInDay"]) {
$db = new db_alloc();
$day = $_POST["hoursInDay"] * 60 * 60;
$q = prepare("UPDATE timeUnit SET timeUnitSeconds = '%d' WHERE timeUnitName = 'day'", $day);
$db->query($q);
$q = prepare("UPDATE timeUnit SET timeUnitSeconds = '%d' WHERE timeUnitName = 'week'", $day * 5);
$db->query($q);
$q = prepare("UPDATE timeUnit SET timeUnitSeconds = '%d' WHERE timeUnitName = 'month'", $day * 5 * 4);
$db->query($q);
}
// remove bracketed [Alex Lance <]alla@cyber.com.au[>] bits, leaving just alla@cyber.com.au
if ($_POST["AllocFromEmailAddress"]) {
function get_amounts()
{
$rows = $this->get_productSaleItems();
$rows or $rows = array();
$rtn = array();
foreach ($rows as $row) {
$productSaleItem = new productSaleItem();
$productSaleItem->read_row_record($row);
//$rtn["total_spent"] += $productSaleItem->get_amount_spent();
//$rtn["total_earnt"] += $productSaleItem->get_amount_earnt();
//$rtn["total_other"] += $productSaleItem->get_amount_other();
list($sp, $spcur) = array($productSaleItem->get_value("sellPrice"), $productSaleItem->get_value("sellPriceCurrencyTypeID"));
$sellPriceCurr[$spcur] += page::money($spcur, $sp, "%m");
$total_sellPrice += exchangeRate::convert($spcur, $sp);
$total_margin += $productSaleItem->get_amount_margin();
$total_unallocated += $productSaleItem->get_amount_unallocated();
}
unset($sep, $label, $show);
foreach ((array) $sellPriceCurr as $code => $amount) {
$label .= $sep . page::money($code, $amount, "%s%mo %c");
$sep = " + ";
$code != config::get_config_item("currency") and $show = true;
}
$show && $label and $sellPrice_label = " (" . $label . ")";
$total_sellPrice_plus_gst = add_tax($total_sellPrice);
$rtn["total_sellPrice"] = page::money(config::get_config_item("currency"), $total_sellPrice, "%s%mo %c") . $sellPrice_label;
$rtn["total_sellPrice_plus_gst"] = page::money(config::get_config_item("currency"), $total_sellPrice_plus_gst, "%s%mo %c") . $sellPrice_label;
$rtn["total_margin"] = page::money(config::get_config_item("currency"), $total_margin, "%s%mo %c");
$rtn["total_unallocated"] = page::money(config::get_config_item("currency"), $total_unallocated, "%s%mo %c");
$rtn["total_unallocated_number"] = page::money(config::get_config_item("currency"), $total_unallocated, "%mo");
$rtn["total_sellPrice_value"] = page::money(config::get_config_item("currency"), $total_sellPrice, "%mo");
return $rtn;
}
public static function get_list($_FORM)
{
/*
* This is the definitive method of getting a list of timeSheets that need a sophisticated level of filtering
*
*/
global $TPL;
$current_user =& singleton("current_user");
$_FORM["showShortProjectLink"] and $_FORM["showProjectLink"] = true;
$filter = timeSheet::get_list_filter($_FORM);
// Used in timeSheetListS.tpl
$extra["showFinances"] = $_FORM["showFinances"];
$debug = $_FORM["debug"];
$debug and print "<pre>_FORM: " . print_r($_FORM, 1) . "</pre>";
$debug and print "<pre>filter: " . print_r($filter, 1) . "</pre>";
$_FORM["return"] or $_FORM["return"] = "html";
if (is_array($filter) && count($filter)) {
$filter = " WHERE " . implode(" AND ", $filter);
}
$q = "SELECT timeSheet.*, person.personID, projectName, projectShortName\n FROM timeSheet \n LEFT JOIN person ON timeSheet.personID = person.personID\n LEFT JOIN project ON timeSheet.projectID = project.projectID\n LEFT JOIN timeSheetItem ON timeSheet.timeSheetID = timeSheetItem.timeSheetID \n " . $filter . "\n GROUP BY timeSheet.timeSheetID\n ORDER BY dateFrom,projectName,timeSheet.status,surname";
$debug and print "Query: " . $q;
$db = new db_alloc();
$db->query($q);
$status_array = timeSheet::get_timeSheet_statii();
$people_array =& get_cached_table("person");
while ($row = $db->next_record()) {
$t = new timeSheet();
if (!$t->read_db_record($db)) {
continue;
}
$t->load_pay_info();
if ($_FORM["timeSheetItemHours"] && !parse_operator_comparison($_FORM["timeSheetItemHours"], $t->pay_info["total_duration_hours"])) {
continue;
}
$row["currencyTypeID"] = $t->get_value("currencyTypeID");
$row["amount"] = $t->pay_info["total_dollars"];
$amount_tallies[] = array("amount" => $row["amount"], "currency" => $row["currencyTypeID"]);
$extra["amountTotal"] += exchangeRate::convert($row["currencyTypeID"], $row["amount"]);
$extra["totalHours"] += $t->pay_info["total_duration_hours"];
$row["totalHours"] += $t->pay_info["total_duration_hours"];
$row["duration"] = $t->pay_info["summary_unit_totals"];
if ($t->get_value("status") == "edit" && imp($current_user->prefs["timeSheetHoursWarn"]) && $t->pay_info["total_duration_hours"] >= $current_user->prefs["timeSheetHoursWarn"]) {
$row["hoursWarn"] = page::help("This time sheet has gone over " . $current_user->prefs["timeSheetHoursWarn"] . " hours.", page::warn());
}
if ($t->get_value("status") == "edit" && imp($current_user->prefs["timeSheetDaysWarn"]) && (mktime() - format_date("U", $t->get_value("dateFrom"))) / 60 / 60 / 24 >= $current_user->prefs["timeSheetDaysWarn"]) {
$row["daysWarn"] = page::help("This time sheet is over " . $current_user->prefs["timeSheetDaysWarn"] . " days old.", page::warn());
}
$row["person"] = $people_array[$row["personID"]]["name"];
$row["status"] = $status_array[$row["status"]];
$row["customerBilledDollars"] = $t->pay_info["total_customerBilledDollars"];
$extra["customerBilledDollarsTotal"] += exchangeRate::convert($row["currencyTypeID"], $t->pay_info["total_customerBilledDollars"]);
$billed_tallies[] = array("amount" => $row["customerBilledDollars"], "currency" => $row["currencyTypeID"]);
if ($_FORM["showFinances"]) {
list($pos, $neg) = $t->get_transaction_totals();
$row["transactionsPos"] = page::money_print($pos);
$row["transactionsNeg"] = page::money_print($neg);
foreach ((array) $pos as $v) {
$pos_tallies[] = $v;
}
foreach ((array) $neg as $v) {
$neg_tallies[] = $v;
}
}
$p = new project();
$p->read_db_record($db);
$row["projectLink"] = $t->get_link($p->get_name($_FORM));
$rows[$row["timeSheetID"]] = $row;
}
$extra["amount_tallies"] = page::money_print($amount_tallies);
$extra["billed_tallies"] = page::money_print($billed_tallies);
$extra["positive_tallies"] = page::money_print($pos_tallies);
$extra["negative_tallies"] = page::money_print($neg_tallies);
if (!$_FORM["noextra"]) {
return array("rows" => (array) $rows, "extra" => $extra);
} else {
return (array) $rows;
}
}
public static function money_print($rows = array())
{
$mainCurrency = config::get_config_item("currency");
foreach ((array) $rows as $row) {
$sums[$row["currency"]] += $row["amount"];
$k = $row["currency"];
}
// If there's only one currency, then just return that figure.
if (count($sums) == 1) {
return page::money($k, $sums[$k], "%s%m %c");
}
// Else if there's more than one currency, we'll provide a tooltip of the aggregation.
foreach ((array) $sums as $currency => $amount) {
$str .= $sep . page::money($currency, $amount, "%s%m %c");
$sep = " + ";
if ($mainCurrency == $currency) {
$total += $amount;
} else {
$total += exchangeRate::convert($currency, $amount);
}
}
$total = page::money($mainCurrency, $total, "%s%m %c");
if ($str && $str != $total) {
$rtn = page::help(page::exclaim() . "<b>Approximate currency conversion</b><br>" . $str . " = " . $total, page::exclaim() . $total);
} else {
if ($str) {
$rtn = $str;
}
}
return $rtn;
}
function get_fortnightly_average($personID = false)
{
// Need an array of the past years fortnights
$x = 0;
while ($x < 365) {
if ($x % 14 == 0) {
$fortnight++;
}
$fortnights[date("Y-m-d", mktime(0, 0, 0, date("m"), date("d") - 365 + $x, date("Y")))] = $fortnight;
$x++;
}
$dateTimeSheetItem = date("Y-m-d", mktime(0, 0, 0, date("m"), date("d") - 365, date("Y")));
$personID and $personID_sql = prepare(" AND personID = %d", $personID);
$q = prepare("SELECT DISTINCT dateTimeSheetItem, personID\n FROM timeSheetItem \n WHERE dateTimeSheetItem > '%s'\n " . $personID_sql . "\n GROUP BY dateTimeSheetItem,personID\n ", $dateTimeSheetItem);
$db = new db_alloc();
$db->query($q);
while ($db->next_record()) {
if (!$done[$db->f("personID")][$fortnights[$db->f("dateTimeSheetItem")]]) {
$how_many_fortnights[$db->f("personID")]++;
$done[$db->f("personID")][$fortnights[$db->f("dateTimeSheetItem")]] = true;
}
}
$rtn = array();
list($rows, $rows_dollars) = $this->get_averages($dateTimeSheetItem, $personID);
foreach ($rows as $id => $avg) {
$rtn[$id] = $avg / $how_many_fortnights[$id];
#echo "<br>".$id." ".$how_many_fortnights[$id];
}
// Convert all the monies into native currency
foreach ($rows_dollars as $id => $arr) {
foreach ($arr as $r) {
$alex[$id] += exchangeRate::convert($r["currency"], $r["amount"]);
}
}
// Get the averages for each
foreach ((array) $alex as $id => $sum) {
$rtn_dollars[$id] = $sum / $how_many_fortnights[$id];
}
return array($rtn, $rtn_dollars);
}
function get_rate($projectID, $personID)
{
// Try to get the person's rate from the following sources:
// project.defaultTimeSheetRate
// person.defaultTimeSheetRate
// config.name == defaultTimeSheetRate
// First check the project for a rate
$project = new project($projectID);
$row = array('rate' => $project->get_value("defaultTimeSheetRate"), 'unit' => $project->get_value("defaultTimeSheetRateUnitID"));
if (imp($row['rate']) && $row['unit']) {
return $row;
}
// Next check person, which is in global currency rather than project currency - conversion required
$db = new db_alloc();
$q = prepare("SELECT defaultTimeSheetRate as rate, defaultTimeSheetRateUnitID as unit FROM person WHERE personID = %d", $personID);
$db->query($q);
$row = $db->row();
if (imp($row['rate']) && $row['unit']) {
if ($project->get_value("currencyTypeID") != config::get_config_item("currency")) {
$row['rate'] = exchangeRate::convert(config::get_config_item("currency"), $row["rate"], $project->get_value("currencyTypeID"));
}
return $row;
}
// Lowest priority: global
$rate = config::get_config_item("defaultTimeSheetRate");
$unit = config::get_config_item("defaultTimeSheetUnit");
if (imp($rate) && $unit) {
if (config::get_config_item("currency") && $project->get_value("currencyTypeID")) {
$rate = exchangeRate::convert(config::get_config_item("currency"), $rate, $project->get_value("currencyTypeID"));
}
return array('rate' => $rate, 'unit' => $unit);
}
}
function create_transactions_tax()
{
$db = new db_alloc();
$db2 = new db_alloc();
$product = $this->get_foreign_object("product");
$productSale = $this->get_foreign_object("productSale");
$productName = $product->get_value("productName");
$taxName = config::get_config_item("taxName");
$taxTfID = config::get_config_item("taxTfID");
$mainTfID = $productSale->get_value("tfID");
$taxPercent = config::get_config_item("taxPercent");
// If this price includes tax, then perform a tax transfer
$amount_of_tax = $this->get_value("sellPrice") * ($taxPercent / 100);
$amount_of_tax = exchangeRate::convert($this->get_value("sellPriceCurrencyTypeID"), $amount_of_tax, null, null, "%mo");
$this->create_transaction($mainTfID, $taxTfID, $amount_of_tax, "Product Sale " . $taxName . ": " . $productName, false, false, 'tax');
// Now loop through all the productCosts for the sale items product.
$query = prepare("SELECT productCost.*, product.productName\n FROM productCost \n LEFT JOIN product ON product.productID = productCost.productID\n WHERE productCost.productID = %d \n AND isPercentage != 1\n AND productCostActive = true\n ORDER BY productCostID", $this->get_value("productID"));
$db2->query($query);
while ($productCost_row = $db2->next_record()) {
$amount_of_tax = $productCost_row["amount"] * ($taxPercent / 100);
$amount_of_tax = exchangeRate::convert($productCost_row["currencyTypeID"], $amount_of_tax, null, null, "%mo");
$productCost_row["amount"] = $amount_minus_tax;
$this->create_transaction($mainTfID, $taxTfID, $amount_of_tax * $this->get_value("quantity"), "Product Cost " . $taxName . ": " . $productCost_row["productName"] . " " . $productCost_row["description"], false, $productCost_row["productCostID"], 'tax');
}
}
function get_buy_cost($id = false)
{
$id or $id = $this->get_id();
$db = new db_alloc();
$q = prepare("SELECT amount, currencyTypeID, tax\n FROM productCost\n WHERE isPercentage != 1\n AND productID = %d\n AND productCostActive = true\n ", $id);
$db->query($q);
while ($row = $db->row()) {
if ($row["tax"]) {
list($amount_minus_tax, $amount_of_tax) = tax($row["amount"]);
$row["amount"] = $amount_minus_tax;
}
$amount += exchangeRate::convert($row["currencyTypeID"], $row["amount"]);
}
return $amount;
}
