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