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 get_applied_patches()
{
$rows = array();
$db = new db_alloc();
$db->query("SELECT patchName FROM patchLog ORDER BY patchDate DESC,patchName DESC");
while ($row = $db->row()) {
$rows[] = $row["patchName"];
}
return $rows;
}
function set_values($prefix)
{
global $TPL;
$db = new db_alloc();
$db->query("SELECT * FROM invoiceRepeatDate WHERE invoiceRepeatID = %d", $this->get_id());
while ($row = $db->row()) {
$rows[] = $row["invoiceDate"];
}
$TPL[$prefix . "frequency"] = implode(" ", (array) $rows);
return parent::set_values($prefix);
}
function get_roles_array($level = "person")
{
$rows = array();
$db = new db_alloc();
$q = prepare("SELECT * FROM role WHERE roleLevel = '%s' ORDER BY roleSequence", $level);
$db->query($q);
while ($row = $db->row()) {
$rows[$row["roleHandle"]] = $row["roleName"];
}
return $rows;
}
public static function get_er($from, $to, $date = "")
{
static $cache;
if (imp($cache[$from][$to][$date])) {
return $cache[$from][$to][$date];
}
$db = new db_alloc();
if ($date) {
$q = prepare("SELECT *\n FROM exchangeRate \n WHERE exchangeRateCreatedDate = '%s'\n AND fromCurrency = '%s'\n AND toCurrency = '%s'\n ", $date, $from, $to);
$db->query($q);
$row = $db->row();
}
if (!$row) {
$q = prepare("SELECT *\n FROM exchangeRate \n WHERE fromCurrency = '%s'\n AND toCurrency = '%s'\n ORDER BY exchangeRateCreatedTime DESC\n LIMIT 1\n ", $from, $to);
$db->query($q);
$row = $db->row();
}
$cache[$from][$to][$date] = $row["exchangeRate"];
return $row["exchangeRate"];
}
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;
}
function show_transaction_list($template)
{
global $TPL;
global $tflist;
global $transactionGroupID;
$q = prepare("SELECT *, amount * pow(10,-currencyType.numberToBasic) as amount\n FROM transaction\n LEFT JOIN currencyType on transaction.currencyTypeID = currencyType.currencyTypeID\n WHERE transactionGroupID = %d\n ORDER BY transactionID\n ", $transactionGroupID);
$db = new db_alloc();
$db->query($q);
while ($row = $db->row()) {
$transaction = new transaction();
$transaction->read_array($row);
$transaction->set_values();
$tflist = add_inactive_tf($transaction->get_value("tfID"), $tflist);
$tflist = add_inactive_tf($transaction->get_value("fromTfID"), $tflist);
$TPL["display"] = "";
$TPL["tfList_dropdown"] = page::select_options($tflist, $transaction->get_value("tfID"), 500);
$TPL["fromTfList_dropdown"] = page::select_options($tflist, $transaction->get_value("fromTfID"), 500);
$TPL["transactionType_dropdown"] = page::select_options(transaction::get_transactionTypes(), $transaction->get_value("transactionType"));
$TPL["status_dropdown"] = page::select_options(transaction::get_transactionStatii(), $transaction->get_value("status"));
$TPL["link"] = $transaction->get_link("transactionID");
include_template($template);
}
}
function e($str)
{
echo "<br>" . htmlentities($str);
}
$db = new db_alloc();
$row = $db->qr("SELECT count(*) AS sum FROM sentEmailLog WHERE sentEmailLogCreatedTime >= '2012-05-12 00:00:00'");
//e(sprintf("Found %d emails sent.",$row["sum"]));
$row = $db->qr("SELECT count(*) AS sum FROM comment WHERE commentCreatedTime >= '2012-05-12 00:00:00'");
//e(sprintf("Found %d comments created.",$row["sum"]));
$info["host"] = config::get_config_item("allocEmailHost");
$info["port"] = config::get_config_item("allocEmailPort");
$info["username"] = config::get_config_item("allocEmailUsername");
$info["password"] = config::get_config_item("allocEmailPassword");
$info["protocol"] = config::get_config_item("allocEmailProtocol");
$qid = $db->query("SELECT * FROM comment WHERE commentCreatedTime >= '2012-05-12 00:00:00'");
while ($row = $db->row($qid)) {
// For each comment, ascertain the taskID
$entity = $row["commentMaster"];
$entityID = $row["commentMasterID"];
// Use the taskID to query the mbox.taskID for all its emails
$mail = new email_receive($info);
$mail->open_mailbox("INBOX");
$created = $mail->create_mailbox("INBOX/" . $entity . $entityID);
$created or $created = $mail->create_mailbox("INBOX." . $entity . $entityID);
$opened = $mail->open_mailbox("INBOX/" . $entity . $entityID);
$uids = $mail->get_all_email_msg_uids();
//e("Entity: ".$entity.$entityID." comment: ".$row["commentID"]." uids: ".print_r($uids,1));
$found = false;
foreach ((array) $uids as $uid) {
// If there's one that looks like the current one, forgeddaboutit
list($header, $body) = $mail->get_raw_email_by_msg_uid($uid);
function can_be_deleted()
{
if (is_object($this) && $this->get_id()) {
$db = new db_alloc();
$q = prepare("SELECT can_delete_task(%d) as rtn", $this->get_id());
$db->query($q);
$row = $db->row();
return $row['rtn'];
}
}
<?php
$q = prepare("SELECT * FROM person WHERE dailyTaskEmail = 'yes'");
$db = new db_alloc();
$db->query($q);
while ($r = $db->row()) {
$person = new person();
$person->set_id($r["personID"]);
$person->select();
$person->load_prefs();
$person->prefs["dailyTaskEmail"] = 'yes';
$person->store_prefs();
}
<?php
// Create a search index for Time Sheets. This patch may take a long time to apply.
ini_set('max_execution_time', 180000);
ini_set('memory_limit', "256M");
$index = Zend_Search_Lucene::create(ATTACHMENTS_DIR . 'search/timeSheet');
$db = new db_alloc();
$q = prepare("SELECT * FROM timeSheet");
$db->query($q);
while ($db->row()) {
$timeSheet = new timeSheet();
$timeSheet->read_db_record($db);
$timeSheet->update_search_index_doc($index);
}
$index->commit();
function add_interested_party($data)
{
static $people;
$data["emailAddress"] = str_replace(array("<", ">"), "", $data["emailAddress"]);
// Add new entry
$ip = new interestedParty();
$existing = interestedParty::exists($data["entity"], $data["entityID"], $data["emailAddress"]);
if ($existing) {
$ip->set_id($existing["interestedPartyID"]);
$ip->select();
}
$ip->set_value("entity", $data["entity"]);
$ip->set_value("entityID", $data["entityID"]);
$ip->set_value("fullName", $data["name"]);
$ip->set_value("emailAddress", $data["emailAddress"]);
$ip->set_value("interestedPartyActive", 1);
if ($data["personID"]) {
$ip->set_value("personID", $data["personID"]);
$ip->set_value("fullName", person::get_fullname($data["personID"]));
} else {
$people or $people =& get_cached_table("person");
foreach ($people as $personID => $p) {
if ($data["emailAddress"] && same_email_address($p["emailAddress"], $data["emailAddress"])) {
$ip->set_value("personID", $personID);
$ip->set_value("fullName", $p["name"]);
}
}
}
$extra_interested_parties = config::get_config_item("defaultInterestedParties");
if (!$ip->get_value("personID") && !in_array($data["emailAddress"], (array) $extra_interested_parties)) {
$ip->set_value("external", 1);
$q = prepare("SELECT * FROM clientContact WHERE clientContactEmail = '%s'", $data["emailAddress"]);
$db = new db_alloc();
$db->query($q);
if ($row = $db->row()) {
$ip->set_value("clientContactID", $row["clientContactID"]);
$ip->set_value("fullName", $row["clientContactName"]);
}
}
$ip->save();
return $ip->get_id();
}
function update_search_index_doc(&$index)
{
$person =& get_cached_table("person");
$clientModifiedUser = $this->get_value("clientModifiedUser");
$clientModifiedUser_field = $clientModifiedUser . " " . $person[$clientModifiedUser]["username"] . " " . $person[$clientModifiedUser]["name"];
$this->get_value("clientStreetAddressOne") and $postal[] = $this->get_value("clientStreetAddressOne");
$this->get_value("clientSuburbOne") and $postal[] = $this->get_value("clientSuburbOne");
$this->get_value("clientStateOne") and $postal[] = $this->get_value("clientStateOne");
$this->get_value("clientPostcodeOne") and $postal[] = $this->get_value("clientPostcodeOne");
$this->get_value("clientCountryOne") and $postal[] = $this->get_value("clientCountryOne");
$p = implode("\n", (array) $postal);
$p and $p = "Postal Address:\n" . $p;
$this->get_value("clientStreetAddressTwo") and $street[] = $this->get_value("clientStreetAddressTwo");
$this->get_value("clientSuburbTwo") and $street[] = $this->get_value("clientSuburbTwo");
$this->get_value("clientStateTwo") and $street[] = $this->get_value("clientStateTwo");
$this->get_value("clientPostcodeTwo") and $street[] = $this->get_value("clientPostcodeTwo");
$this->get_value("clientCountryTwo") and $street[] = $this->get_value("clientCountryTwo");
$s = implode("\n", (array) $street);
$s and $s = "Street Address:\n" . $s;
$p && $s and $p .= "\n\n";
$addresses = $p . $s;
$this->get_value("clientPhoneOne") and $ph = "Ph: " . $this->get_value("clientPhoneOne");
$this->get_value("clientFaxOne") and $fx = "Fax: " . $this->get_value("clientFaxOne");
$ph and $ph = " " . $ph;
$fx and $fx = " " . $fx;
$name = $this->get_name() . $ph . $fx;
$q = prepare("SELECT * FROM clientContact WHERE clientID = %d", $this->get_id());
$db = new db_alloc();
$db->query($q);
while ($row = $db->row()) {
$c .= $nl . $row["clientContactName"];
$row["clientContactEmail"] and $c .= " <" . $row["clientContactEmail"] . ">";
$c .= " | ";
$row["clientContactStreetAddress"] and $c .= " " . $row["clientContactStreetAddress"];
$row["clientContactSuburb"] and $c .= " " . $row["clientContactSuburb"];
$row["clientContactState"] and $c .= " " . $row["clientContactState"];
$row["clientContactPostcode"] and $c .= " " . $row["clientContactPostcode"];
$row["clientContactCountry"] and $c .= " " . $row["clientContactCountry"];
$c .= " | ";
$row["clientContactPhone"] and $c .= " Ph: " . $row["clientContactPhone"];
$row["clientContactMobile"] and $c .= " Mob: " . $row["clientContactMobile"];
$row["clientContactFax"] and $c .= " Fax: " . $row["clientContactFax"];
$row["primaryContact"] and $c .= " Primary contact";
$c .= " | ";
$row["clientContactOther"] and $c .= " " . $row["clientContactOther"];
$nl = "|+|=|";
}
$c and $contacts = $c;
$doc = new Zend_Search_Lucene_Document();
$doc->addField(Zend_Search_Lucene_Field::Keyword('id', $this->get_id()));
$doc->addField(Zend_Search_Lucene_Field::Text('name', $name, "utf-8"));
$doc->addField(Zend_Search_Lucene_Field::Text('desc', $addresses, "utf-8"));
$doc->addField(Zend_Search_Lucene_Field::Text('contact', $contacts, "utf-8"));
$doc->addField(Zend_Search_Lucene_Field::Text('status', $this->get_value("clientStatus"), "utf-8"));
$doc->addField(Zend_Search_Lucene_Field::Text('modifier', $clientModifiedUser_field, "utf-8"));
$doc->addField(Zend_Search_Lucene_Field::Text('dateModified', str_replace("-", "", $this->get_value("clientModifiedTime")), "utf-8"));
$doc->addField(Zend_Search_Lucene_Field::Text('category', $this->get_value("clientCategory"), "utf-8"));
$doc->addField(Zend_Search_Lucene_Field::Text('dateCreated', str_replace("-", "", $this->get_value("clientCreatedTime")), "utf-8"));
$index->addDocument($doc);
}
<?php
// This patch removes patch 162 from the patchLog table.
// Which should enable patch 162 to be run again.
// patch-00162-alla.php simply rebuilds the search index for clients.
// The rebuild of the client search index may be necessary for users that had the
// functionality deployed before the index was feature-complete.
// If the patch 162 isn't found in the patchLog table then it has yet to be applied.
$q = prepare("SELECT * FROM patchLog WHERE patchName = 'patch-00162-alla.php'");
$db = new db_alloc();
$db->query($q);
if ($db->row()) {
$q = prepare("DELETE FROM patchLog WHERE patchName = 'patch-00162-alla.php'");
$db->query($q);
}
function get_num_alloc_users()
{
$db = new db_alloc();
$db->query("SELECT COUNT(*) AS total FROM person WHERE personActive = 1");
$row = $db->row();
return $row["total"];
}
function find_by_email($email = false)
{
$email = str_replace(array("<", ">"), "", $email);
if ($email) {
$q = prepare("SELECT clientContactID\n FROM clientContact\n WHERE replace(replace(clientContactEmail,'<',''),'>','') = '%s'\n ", $email);
$db = new db_alloc();
$db->query($q);
$row = $db->row();
return $row["clientContactID"];
}
}
function save_history()
{
$current_user =& singleton("current_user");
global $TPL;
// Delete old items if they have too many.
if (!is_object($current_user) || !$current_user->get_id()) {
return;
}
$db = new db_alloc();
$query = prepare("SELECT count(*) AS total FROM history WHERE personID = %d", $current_user->get_id());
$db->query($query);
$row = $db->row();
if ($row["total"] >= 3 * $this->max_to_display) {
$query = prepare("DELETE FROM history WHERE personID = %d ORDER BY the_time LIMIT %d", $current_user->get_id(), $this->max_to_display, 2 * $this->max_to_display);
$db->query($query);
}
$ignored_files = $this->get_ignored_files();
if ($_SERVER["QUERY_STRING"]) {
$qs = $this->strip_get_session($_SERVER["QUERY_STRING"]);
$qs = preg_replace("[&\$]", "", $qs);
$qs = preg_replace("[^&]", "", $qs);
$qs = preg_replace("[^\\?]", "", $qs);
}
$file = end(explode("/", $_SERVER["SCRIPT_NAME"])) . "?" . $qs;
if (is_object($current_user) && !in_array($file, $ignored_files) && !$_GET["historyID"] && !$_POST["historyID"] && ($the_label = $this->get_history_label($_SERVER["SCRIPT_NAME"], $qs))) {
$the_place = basename($_SERVER["SCRIPT_NAME"]);
foreach ($TPL as $k => $v) {
$key = basename($this->strip_get_session($v));
$key = preg_replace("[&\$]", "", $key);
$key = preg_replace("[\\?\$]", "", $key);
$arr[$key] = $k;
}
if ($arr[$the_place]) {
$this->set_value("personID", $current_user->get_id());
$this->set_value("the_place", $arr[$the_place]);
$this->set_value("the_args", $qs);
$this->set_value("the_label", $the_label);
$this->set_value("the_time", date("Y-m-d H:i:s"));
$this->save();
}
}
}
public static function select_options($options, $selected_value = NULL, $max_length = 45, $escape = true)
{
/**
* Builds up options for use in a html select widget (works with multiple selected too)
*
* @param $options mixed An sql query or an array of options
* @param $selected_value string The current selected element
* @param $max_length int The maximum string length of the label
* @return string The string of options
*/
// Build options from an SQL query: "SELECT col_a as value, col_b as label FROM"
if (is_string($options)) {
$db = new db_alloc();
$db->query($options);
while ($row = $db->row()) {
$rows[$row["value"]] = $row["label"];
}
// Build options from an array: array(value1=>label1, value2=>label2)
} else {
if (is_array($options)) {
foreach ($options as $k => $v) {
$rows[$k] = $v;
}
}
}
if (is_array($rows)) {
// Coerce selected options into an array
if (is_array($selected_value)) {
$selected_values = $selected_value;
} else {
if ($selected_value !== NULL) {
$selected_values[] = $selected_value;
}
}
foreach ($rows as $value => $label) {
$sel = "";
if ($value && !$label) {
$label = $value;
}
// If an array of selected values!
if (is_array($selected_values)) {
foreach ($selected_values as $selected_value) {
if ($selected_value === "" && $value === 0) {
// continue
} else {
if ($selected_value == $value) {
$sel = " selected";
}
}
}
}
$label = str_replace(" ", " ", $label);
if (strlen((string) $label) > $max_length) {
$label = substr($label, 0, $max_length - 3) . "...";
}
$escape and $label = page::htmlentities($label);
$label = str_replace(" ", " ", $label);
$str .= "\n<option value=\"" . $value . "\"" . $sel . ">" . $label . "</option>";
}
}
return $str;
}
public static function get_list($_FORM)
{
$filter = invoiceItem::get_list_filter($_FORM);
if (is_array($filter) && count($filter)) {
$f = " WHERE " . implode(" AND ", $filter);
}
$q = prepare("SELECT * FROM invoiceItem \n LEFT JOIN invoice ON invoice.invoiceID = invoiceItem.invoiceID\n LEFT JOIN client ON client.clientID = invoice.clientID\n " . $f);
$db = new db_alloc();
$db->query($q);
while ($row = $db->row()) {
$row["iiAmount"] = page::money($row["currencyTypeID"], $row["iiAmount"], "%mo");
$row["iiUnitPrice"] = page::money($row["currencyTypeID"], $row["iiUnitPrice"], "%mo");
$rows[$row["invoiceItemID"]] = $row;
}
return (array) $rows;
}
function fix_fk($c, $p)
{
list($child, $child_fk) = explode(".", $c);
list($parent, $parent_fk) = explode(".", $p);
$child_pk = $child . "." . $child . "ID";
$parent_pk = $parent . "." . $parent . "ID";
$q = "select " . $child_pk . "," . $c . "\n from " . $child . "\n left join " . $parent . " on " . $c . " = " . $p . "\n where " . $c . " is not null and " . $p . " is null;";
###echo "<br><hr>".$q;
$db = new db_alloc();
$db2 = new db_alloc();
$db->query($q);
while ($row = $db->row()) {
$q = prepare("UPDATE " . $child . " SET " . $c . " = NULL WHERE " . $child_pk . " = " . $row[$child . "ID"]);
debug_echo("<br> " . $q);
$db2->query($q);
}
}
$db = new db_alloc();
$db->query(prepare("SELECT SUM(amount * pow(10,-currencyType.numberToBasic)) AS amount, \n transaction.currencyTypeID as currency\n FROM transaction\n LEFT JOIN currencyType on transaction.currencyTypeID = currencyType.currencyTypeID\n WHERE expenseFormID = %d\n GROUP BY transaction.currencyTypeID\n ", $expenseForm->get_id()));
while ($row = $db->row()) {
$rows[] = $row;
}
$TPL["formTotal"] = page::money_print($rows);
}
if (is_object($expenseForm) && $current_user->have_role("admin") && !$expenseForm->get_invoice_link() && $expenseForm->get_value("expenseFormFinalised") && $expenseForm->get_value("seekClientReimbursement")) {
$ops["invoiceStatus"] = "edit";
$ops["clientID"] = $expenseForm->get_value("clientID");
$invoice_list = invoice::get_list($ops);
$invoice_list = array_kv($invoice_list, "invoiceID", array("invoiceNum", "invoiceName"));
$q = prepare("SELECT * FROM invoiceItem WHERE expenseFormID = %d", $expenseForm->get_id());
$db = new db_alloc();
$db->query($q);
$row = $db->row();
$sel_invoice = $row["invoiceID"];
$TPL["attach_to_invoice_button"] = "<select name=\"attach_to_invoiceID\">";
$TPL["attach_to_invoice_button"] .= "<option value=\"create_new\">Create New Invoice</option>";
$TPL["attach_to_invoice_button"] .= page::select_options($invoice_list, $sel_invoice) . "</select>";
$TPL["attach_to_invoice_button"] .= "<input type=\"submit\" name=\"attach_transactions_to_invoice\" value=\"Add to Invoice\"> ";
$TPL["invoice_label"] = "Invoice:";
}
if (is_object($expenseForm)) {
$invoice_link = $expenseForm->get_invoice_link();
if ($invoice_link) {
$TPL["invoice_label"] = "Invoice:";
$TPL["invoice_link"] = $invoice_link;
}
}
$TPL["taxName"] = config::get_config_item("taxName");
function get_prepaid_invoice()
{
$db = new db_alloc();
$q = prepare("SELECT *\n FROM invoice \n WHERE projectID = %d\n AND invoiceStatus != 'finished' \n ORDER BY invoiceDateFrom ASC \n LIMIT 1", $this->get_id());
$db->query($q);
if ($row = $db->row()) {
$invoiceID = $row["invoiceID"];
} else {
if ($this->get_value("clientID")) {
$q = prepare("SELECT *\n FROM invoice \n WHERE clientID = %d \n AND (projectID IS NULL OR projectID = 0 OR projectID = '')\n AND invoiceStatus != 'finished' \n ORDER BY invoiceDateFrom ASC \n LIMIT 1", $this->get_value("clientID"));
$db->query($q);
if ($row = $db->row()) {
$invoiceID = $row["invoiceID"];
}
}
}
return $invoiceID;
}
{
$nl = "<br>";
$nl = "\n";
echo $nl . $str;
}
foreach (array("client", "comment", "item", "project", "task", "timeSheet", "wiki") as $i) {
if (!is_dir(ATTACHMENTS_DIR . 'search/' . $i)) {
$index = Zend_Search_Lucene::create(ATTACHMENTS_DIR . 'search/' . $i);
$index->commit();
}
}
$q = "SELECT * FROM indexQueue ORDER BY entity";
$db = new db_alloc();
$q1 = $db->query($q);
echoo("Beginning ...");
while ($row = $db->row($q1)) {
$z++;
if ($z % 1000 == 0 && is_object($index)) {
echoo($z . " Committing index: " . $current_index);
$index->commit();
flush();
}
if (!$current_index || $current_index != $row["entity"]) {
// commit previous index
if (is_object($index)) {
echoo("Committing index: " . $current_index);
$index->commit();
}
// start a new index
echoo("New \$index: " . $row["entity"]);
$index = Zend_Search_Lucene::open(ATTACHMENTS_DIR . 'search/' . $row["entity"]);
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 get_list_summary($_FORM = array())
{
//$_FORM["fromDate"] = "2010-08-20";
//$_FORM["projectID"] = "22";
$_FORM["maxCommentLength"] or $_FORM["maxCommentLength"] = 500;
list($filter1, $filter2, $filter3) = comment::get_list_summary_filter($_FORM);
is_array($filter1) && count($filter1) and $filter1 = " AND " . implode(" AND ", $filter1);
is_array($filter2) && count($filter2) and $filter2 = " AND " . implode(" AND ", $filter2);
is_array($filter3) && count($filter3) and $filter3 = " AND " . implode(" AND ", $filter3);
if ($_FORM["clients"]) {
$client_join = " LEFT JOIN clientContact on comment.commentCreatedUserClientContactID = clientContact.clientContactID";
$client_fields = " , clientContact.clientContactName";
}
$q = prepare("SELECT commentID as id\n , commentCreatedUser as personID\n , UNIX_TIMESTAMP(commentCreatedTime) as sortDate\n , date(commentCreatedTime) as date\n , commentCreatedTime as displayDate\n , commentMasterID as taskID\n , task.taskName\n , SUBSTRING(comment.comment,1,%d) AS comment_text\n , commentCreatedUserText\n " . $client_fields . "\n FROM comment\n LEFT JOIN task on comment.commentMasterID = task.taskID\n " . $client_join . "\n WHERE commentMaster = 'task'\n " . $filter1 . "\n ORDER BY commentCreatedTime, commentCreatedUser", $_FORM["maxCommentLength"]);
$q .= " ";
$people =& get_cached_table("person");
$db = new db_alloc();
$db->query($q);
while ($row = $db->row()) {
$row["icon"] = 'icon-comments-alt';
$row["id"] = "comment_" . $row["id"];
$row["personID"] and $row["person"] = $people[$row["personID"]]["name"];
$row["clientContactName"] and $row["person"] = $row["clientContactName"];
$row["person"] or list($e, $row["person"]) = parse_email_address($row["commentCreatedUserText"]);
$row["displayDate"] = format_date("Y-m-d g:ia", $row["displayDate"]);
if (!$tasks[$row["taskID"]]) {
$t = new task();
$t->set_id($row["taskID"]);
$t->set_value("taskName", $row["taskName"]);
$tasks[$row["taskID"]] = $t->get_task_link(array("prefixTaskID" => true));
}
$rows[$row["taskID"]][$row["sortDate"]][] = $row;
}
// Note that timeSheetItemID is selected twice so that the perms checking can work
// timeSheetID is also used by the perms checking.
$q2 = prepare("SELECT timeSheetItemID as id\n ,timeSheetItemID\n ,timeSheetID\n ,timeSheetItem.personID\n ,dateTimeSheetItem as date\n ,UNIX_TIMESTAMP(CONCAT(dateTimeSheetItem, ' 23:59:58')) as sortDate\n ,dateTimeSheetItem as displayDate\n ,timeSheetItem.taskID\n ,task.taskName\n ,timeSheetItemDuration as duration\n ,SUBSTRING(timeSheetItem.comment,1,%d) AS comment_text\n FROM timeSheetItem\n LEFT JOIN task on timeSheetItem.taskID = task.taskID\n WHERE 1\n " . $filter2 . "\n ORDER BY dateTimeSheetItem", $_FORM["maxCommentLength"]);
$db->query($q2);
while ($row = $db->row()) {
$timeSheetItem = new timeSheetItem();
if (!$timeSheetItem->read_row_record($row)) {
continue;
}
$row["icon"] = 'icon-time';
$row["id"] = "timeitem_" . $row["id"];
$row["person"] = $people[$row["personID"]]["name"];
if (!$tasks[$row["taskID"]]) {
$t = new task();
$t->set_id($row["taskID"]);
$t->set_value("taskName", $row["taskName"]);
$tasks[$row["taskID"]] = $t->get_task_link(array("prefixTaskID" => true));
}
$totals[$row["taskID"]] += $row["duration"];
$rows[$row["taskID"]][$row["sortDate"]][] = $row;
}
// get manager's guestimates about time worked from tsiHint table
$q3 = prepare("SELECT tsiHintID as id\n ,tsiHintID\n ,tsiHint.personID\n ,tsiHint.date\n ,UNIX_TIMESTAMP(CONCAT(tsiHint.date,' 23:59:59')) as sortDate\n ,tsiHint.date as displayDate\n ,tsiHint.taskID\n ,tsiHint.duration\n ,tsiHint.tsiHintCreatedUser\n ,SUBSTRING(tsiHint.comment,1,%d) AS comment_text\n ,task.taskName\n FROM tsiHint\n LEFT JOIN task on tsiHint.taskID = task.taskID\n WHERE 1\n " . $filter3 . "\n ORDER BY tsiHint.date", $_FORM["maxCommentLength"]);
$db->query($q3);
while ($row = $db->row()) {
//$tsiHint = new tsiHint();
//if (!$tsiHint->read_row_record($row))
// continue;
$row["icon"] = 'icon-bookmark-empty';
$row["id"] = "tsihint_" . $row["id"];
$row["person"] = $people[$row["personID"]]["name"];
$row["comment_text"] .= ' [by ' . $people[$row["tsiHintCreatedUser"]]["name"] . ']';
if (!$tasks[$row["taskID"]]) {
$t = new task();
$t->set_id($row["taskID"]);
$t->set_value("taskName", $row["taskName"]);
$tasks[$row["taskID"]] = $t->get_task_link(array("prefixTaskID" => true));
}
$totals_tsiHint[$row["taskID"]] += $row["duration"];
$rows[$row["taskID"]][$row["sortDate"]][] = $row;
}
// If there is a time sheet entry for 2010-10-10 but there is no comment entry
// for that date, then the time sheet entry will appear out of sequence i.e. at
// the very end of the whole list. So we need to manually sort them.
foreach ((array) $rows as $tid => $arr) {
ksort($arr, SORT_NUMERIC);
$rows[$tid] = $arr;
}
foreach ((array) $rows as $taskID => $dates) {
$rtn .= comment::get_list_summary_header($tasks[$taskID], $totals[$taskID], $totals_tsiHint[$taskID], $_FORM);
foreach ($dates as $date => $more_rows) {
foreach ($more_rows as $row) {
$rtn .= comment::get_list_summary_body($row);
}
}
$rtn .= comment::get_list_summary_footer($rows, $tasks);
}
return $rtn;
}
function update_search_index_doc(&$index)
{
$p =& get_cached_table("person");
$personID = $this->get_value("personID");
$person_field = $personID . " " . $p[$personID]["username"] . " " . $p[$personID]["name"];
$managerID = $this->get_value("approvedByManagerPersonID");
$manager_field = $managerID . " " . $p[$managerID]["username"] . " " . $p[$managerID]["name"];
$adminID = $this->get_value("approvedByAdminPersonID");
$admin_field = $adminID . " " . $p[$adminID]["username"] . " " . $p[$adminID]["name"];
$tf_field = $this->get_value("recipient_tfID") . " " . tf::get_name($this->get_value("recipient_tfID"));
if ($this->get_value("projectID")) {
$project = new project();
$project->set_id($this->get_value("projectID"));
$project->select();
$projectName = $project->get_name();
$projectShortName = $project->get_name(array("showShortProjectLink" => true));
$projectShortName && $projectShortName != $projectName and $projectName .= " " . $projectShortName;
}
$q = prepare("SELECT dateTimeSheetItem, taskID, description, comment, commentPrivate \n FROM timeSheetItem \n WHERE timeSheetID = %d \n ORDER BY dateTimeSheetItem ASC", $this->get_id());
$db = new db_alloc();
$db->query($q);
while ($r = $db->row()) {
$desc .= $br . $r["dateTimeSheetItem"] . " " . $r["taskID"] . " " . $r["description"] . "\n";
$r["comment"] && $r["commentPrivate"] or $desc .= $r["comment"] . "\n";
$br = "\n";
}
$doc = new Zend_Search_Lucene_Document();
$doc->addField(Zend_Search_Lucene_Field::Keyword('id', $this->get_id()));
$doc->addField(Zend_Search_Lucene_Field::Text('project', $projectName, "utf-8"));
$doc->addField(Zend_Search_Lucene_Field::Text('pid', $this->get_value("projectID"), "utf-8"));
$doc->addField(Zend_Search_Lucene_Field::Text('creator', $person_field, "utf-8"));
$doc->addField(Zend_Search_Lucene_Field::Text('desc', $desc, "utf-8"));
$doc->addField(Zend_Search_Lucene_Field::Text('status', $this->get_value("status"), "utf-8"));
$doc->addField(Zend_Search_Lucene_Field::Text('tf', $tf_field, "utf-8"));
$doc->addField(Zend_Search_Lucene_Field::Text('manager', $manager_field, "utf-8"));
$doc->addField(Zend_Search_Lucene_Field::Text('admin', $admin_field, "utf-8"));
$doc->addField(Zend_Search_Lucene_Field::Text('dateManager', str_replace("-", "", $this->get_value("dateSubmittedToManager")), "utf-8"));
$doc->addField(Zend_Search_Lucene_Field::Text('dateAdmin', str_replace("-", "", $this->get_value("dateSubmittedToAdmin")), "utf-8"));
$doc->addField(Zend_Search_Lucene_Field::Text('dateFrom', str_replace("-", "", $this->get_value("dateFrom")), "utf-8"));
$doc->addField(Zend_Search_Lucene_Field::Text('dateTo', str_replace("-", "", $this->get_value("dateTo")), "utf-8"));
$index->addDocument($doc);
}
<?php
// We're dropping the product.buyCost fields, so we need to transfer them over to productCosts.
$db = new db_alloc();
$db2 = new db_alloc();
$q = "SELECT * FROM product";
$db->query($q);
while ($row = $db->row()) {
$q = prepare("INSERT INTO productCost (productID,fromTfID,tfID,amount,currencyTypeID,description) VALUES ('%d','%d','%d','%d','%s','%s')", $row["productID"], config::get_config_item("mainTfID"), config::get_config_item("outTfID"), $row["buyCost"], $row["buyCostCurrencyTypeID"], "Product Acquisition");
$db2->query($q);
}
function get_transactions($productSaleItemID = false)
{
$rows = array();
$query = prepare("SELECT transaction.*\n ,productCost.productCostID as pc_productCostID\n ,productCost.amount as pc_amount\n ,productCost.isPercentage as pc_isPercentage\n ,productCost.currencyTypeID as pc_currency\n FROM transaction \n LEFT JOIN productCost on transaction.productCostID = productCost.productCostID\n WHERE productSaleID = %d\n AND productSaleItemID = %d\n ORDER BY transactionID", $this->get_id(), $productSaleItemID);
$db = new db_alloc();
$db->query($query);
while ($row = $db->row()) {
if ($row["transactionType"] == "tax") {
$row["saleTransactionType"] = "tax";
} else {
if ($row["pc_productCostID"]) {
$row["saleTransactionType"] = $row["pc_isPercentage"] ? "aPerc" : "aCost";
} else {
if (!$done && $row["transactionType"] == "sale" && !$row["productCostID"]) {
$done = true;
$row["saleTransactionType"] = "sellPrice";
}
}
}
$rows[] = $row;
}
return $rows;
}
function get_assoc_array($key = false, $value = false, $sel = false, $where = array())
{
$key or $key = $this->key_field->get_name();
$value or $value = "*";
$value != "*" and $key_sql = $key . ",";
$q = sprintf('SELECT %s %s FROM %s WHERE 1=1 ', db_esc($key_sql), db_esc($value), db_esc($this->data_table));
$pkey_sql = " OR " . $this->key_field->get_name() . " = ";
if (is_array($sel) && count($sel)) {
foreach ($sel as $s) {
$extra .= $pkey_sql . sprintf("%d", $s);
}
} else {
if ($sel) {
$extra = $pkey_sql . db_esc($sel);
}
}
// If they haven't specifically asked for inactive or all
// records, we default to giving them only active records.
if (is_object($this->data_fields[$this->data_table . "Active"]) && !isset($where[$this->data_table . "Active"])) {
$where[$this->data_table . "Active"] = 1;
// Else get all records
} else {
if ($where[$this->data_table . "Active"] == "all") {
unset($where[$this->data_table . "Active"]);
}
}
if (is_array($where) && count($where)) {
foreach ($where as $colname => $colvalue) {
$q .= " AND " . $colname . " = '" . db_esc($colvalue) . "'";
}
}
$q .= $extra;
if (is_object($this->data_fields[$this->data_table . "Sequence"])) {
$q .= " ORDER BY " . db_esc($this->data_table) . "Sequence";
} else {
if (is_object($this->data_fields[$this->data_table . "Seq"])) {
$q .= " ORDER BY " . db_esc($this->data_table) . "Seq";
} else {
if ($value != "*") {
$q .= " ORDER BY " . db_esc($value);
}
}
}
$db = new db_alloc();
$db->query($q);
$rows = array();
while ($row = $db->row()) {
if ($this->read_db_record($db)) {
if ($value && $value != "*") {
$v = $row[$value];
} else {
$v = $row;
}
$rows[$row[$key]] = $v;
}
}
return $rows;
}
function show_transaction_listR($template_name)
{
global $timeSheet;
global $TPL;
$current_user =& singleton("current_user");
global $percent_array;
$db = new db_alloc();
$db->query("SELECT * FROM transaction WHERE timeSheetID = %d", $timeSheet->get_id());
if ($db->next_record() || $timeSheet->get_value("status") == "invoiced" || $timeSheet->get_value("status") == "finished") {
$db->query("SELECT * \n FROM tf \n WHERE tfActive = 1\n OR tfID = %d \n OR tfID = %d \n ORDER BY tfName", $db->f("tfID"), $db->f("fromTfID"));
while ($db->row()) {
$tf_array[$db->f("tfID")] = $db->f("tfName");
}
$status_options = array("pending" => "Pending", "approved" => "Approved", "rejected" => "Rejected");
$transactionType_options = transaction::get_transactionTypes();
if ($timeSheet->have_perm(PERM_TIME_INVOICE_TIMESHEETS) && $timeSheet->get_value("status") == "invoiced") {
$db->query("SELECT * FROM transaction WHERE timeSheetID = %d ORDER BY transactionID", $timeSheet->get_id());
while ($db->next_record()) {
$transaction = new transaction();
$transaction->read_db_record($db);
$transaction->set_tpl_values("transaction_");
$TPL["currency"] = page::money($transaction->get_value("currencyTypeID"), '', "%S");
$TPL["currency_code"] = page::money($transaction->get_value("currencyTypeID"), '', "%C");
$TPL["tf_options"] = page::select_options($tf_array, $TPL["transaction_tfID"]);
$TPL["from_tf_options"] = page::select_options($tf_array, $TPL["transaction_fromTfID"]);
$TPL["status_options"] = page::select_options($status_options, $transaction->get_value("status"));
$TPL["transactionType_options"] = page::select_options($transactionType_options, $transaction->get_value("transactionType"));
$TPL["percent_dropdown"] = page::select_options($percent_array, $empty);
$TPL["transaction_buttons"] = '
<button type="submit" name="transaction_delete" value="1" class="delete_button">Delete<i class="icon-trash"></i></button>
<button type="submit" name="transaction_save" value="1" class="save_button">Save<i class="icon-ok-sign"></i></button>
';
if ($transaction->get_value("transactionType") == "invoice") {
$TPL["transaction_transactionType"] = $transaction->get_transaction_type_link();
$TPL["transaction_fromTfID"] = tf::get_name($transaction->get_value("fromTfID"));
$TPL["transaction_tfID"] = tf::get_name($transaction->get_value("tfID"));
$TPL["currency_amount"] = page::money($transaction->get_value("currencyTypeID"), $transaction->get_value("amount"), "%S%mo %c");
include_template("templates/timeSheetTransactionListViewR.tpl");
} else {
include_template($template_name);
}
}
} else {
// If you don't have perm INVOICE TIMESHEETS then only select
// transactions which you have permissions to see.
$query = prepare("SELECT * \n FROM transaction \n WHERE timeSheetID = %d\n ORDER BY transactionID", $timeSheet->get_id());
$db->query($query);
while ($db->next_record()) {
$transaction = new transaction();
$transaction->read_db_record($db);
$transaction->set_tpl_values("transaction_");
unset($TPL["transaction_amount_pos"]);
unset($TPL["transaction_amount_neg"]);
$TPL["currency_amount"] = page::money($transaction->get_value("currencyTypeID"), $transaction->get_value("amount"), "%S%mo %c");
$TPL["transaction_fromTfID"] = tf::get_name($transaction->get_value("fromTfID"));
$TPL["transaction_tfID"] = tf::get_name($transaction->get_value("tfID"));
$TPL["transaction_transactionType"] = $transactionType_options[$transaction->get_value("transactionType")];
include_template("templates/timeSheetTransactionListViewR.tpl");
}
}
}
}
