function show_time_sheets_list_for_classes($template_name, $doAdmin = false)
{
$current_user =& singleton("current_user");
global $TPL;
if ($doAdmin) {
$db = get_pending_admin_timesheet_db();
} else {
$db = get_pending_timesheet_db();
}
$people =& get_cached_table("person");
while ($db->next_record()) {
$timeSheet = new timeSheet();
$timeSheet->read_db_record($db);
$timeSheet->set_values();
unset($date);
if ($timeSheet->get_value("status") == "manager") {
$date = $timeSheet->get_value("dateSubmittedToManager");
} else {
if ($timeSheet->get_value("status") == "admin") {
$date = $timeSheet->get_value("dateSubmittedToAdmin");
}
}
unset($TPL["warning"]);
// older than $current_user->prefs["timeSheetDaysWarn"] days
if ($date && imp($current_user->prefs["timeSheetDaysWarn"]) && (mktime() - format_date("U", $date)) / 60 / 60 / 24 > $current_user->prefs["timeSheetDaysWarn"]) {
$TPL["warning"] = page::help("This time sheet was submitted to you over " . $current_user->prefs["timeSheetDaysWarn"] . " days ago.", page::warn());
}
$TPL["date"] = "<a href=\"" . $TPL["url_alloc_timeSheet"] . "timeSheetID=" . $timeSheet->get_id() . "\">" . $date . "</a>";
$TPL["user"] = $people[$timeSheet->get_value("personID")]["name"];
$TPL["projectName"] = $db->f("projectName");
include_template("../time/templates/" . $template_name);
}
}
function get_value($dest = DST_VARIABLE, $parent = null)
{
if ($dest == DST_DATABASE) {
if (isset($this->value) && imp($this->value) || $this->empty_to_null == false) {
return "'" . db_esc($this->value) . "'";
} else {
return "NULL";
}
} else {
if ($dest == DST_HTML_DISPLAY) {
if ($this->type == "money" && imp($this->value)) {
$c = $parent->currency;
if ($this->currency && isset($parent->data_fields[$this->currency])) {
$c = $parent->get_value($this->currency);
}
if (!$c) {
alloc_error("db_field::get_value(): No currency specified for " . $parent->classname . "." . $this->name . " (currency:" . $c . ")");
} else {
if ($this->value == $parent->all_row_fields[$this->name]) {
return page::money($c, $this->value, "%mo");
}
}
}
return page::htmlentities($this->value);
} else {
return $this->value;
}
}
}
function save()
{
if (!imp($this->get_value("iiAmount"))) {
$this->set_value("iiAmount", $this->get_value("iiQuantity") * $this->get_value("iiUnitPrice"));
}
$status = parent::save();
$status2 = invoice::update_invoice_dates($this->get_value("invoiceID"));
return $status && $status2;
}
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 save()
{
if (!$this->get_value("currencyTypeID")) {
if ($this->get_value("projectID")) {
$project = $this->get_foreign_object("project");
$currencyTypeID = $project->get_value("currencyTypeID");
} else {
if (config::get_config_item("currency")) {
$currencyTypeID = config::get_config_item("currency");
}
}
if (!imp($this->get_value("maxAmount"))) {
$this->set_value("maxAmount", '');
}
if ($currencyTypeID) {
$this->set_value("currencyTypeID", $currencyTypeID);
} else {
alloc_error("Unable to save invoice. No currency is able to be determined. Either attach this invoice to a project, or set a Main Currency on the Setup -> Finance screen.");
}
}
return parent::save();
}
public function preparein()
{
$this->autoRender = false;
$quo_id = $this->request->data['id'];
$quo_list = $this->Quotation->find('first', array('conditions' => array('Quotation.is_approved' => 1, 'Quotation.is_deleted' => 0, 'Quotation.status' => 1), 'recursive' => 4));
//pr($quo_list);exit;
$del_list = $this->Deliveryorder->find('all', array('conditions' => array('Deliveryorder.is_approved' => 1, 'Deliveryorder.status' => 1, 'Deliveryorder.is_deleted' => 0, 'Deliveryorder.quotationno' => $quo_id), 'recursive' => 3));
//pr($del_list);exit;
function imp($imp)
{
return implode(",", $imp);
}
foreach ($del_list as $del) {
$del_id[] = $del['Deliveryorder']['id'];
$del_no[] = $del['Deliveryorder']['delivery_order_no'];
}
$deliveryorder_no = imp($del_no);
//pr($deliveryorder_no);exit;
$deliveryorder_id = imp($del_id);
//pr($quo_list);exit;
$counter = 0;
foreach ($quo_list['Customer']['Contactpersoninfo'] as $contact) {
if ($counter == 0) {
$contact_id = $contact['id'];
$contact_name = $contact['name'];
$contact_phone = $contact['phone'];
$contact_email = $contact['email'];
$counter++;
}
}
foreach ($del_list['Salesorder']['Description'] as $salesamount) {
$del_id[] = $del['Deliveryorder']['id'];
$del_no[] = $del['Deliveryorder']['delivery_order_no'];
}
//echo $contact_id;
//exit;
//pr($del_list);exit;
//pr($quo_list);exit;
$dmt = $this->random('invoice');
$this->request->data['Invoice']['invoiceno'] = $dmt;
$this->request->data['Invoice']['branch_id'] = $quo_list['Quotation']['branch_id'];
$this->request->data['Invoice']['deliveryorder_id'] = $deliveryorder_no;
$this->request->data['Invoice']['quotation_id'] = $quo_list['Quotation']['id'];
$this->request->data['Invoice']['customer_id'] = $quo_list['Quotation']['customer_id'];
$this->request->data['Invoice']['customername'] = $quo_list['Quotation']['customername'];
$this->request->data['Invoice']['regaddress'] = $quo_list['Quotation']['address'];
$this->request->data['Invoice']['contactperson_id'] = $del['Deliveryorder']['attn'];
$this->request->data['Invoice']['contactpersonname'] = $contact_name;
$this->request->data['Invoice']['phone'] = $del['Deliveryorder']['phone'];
$this->request->data['Invoice']['fax'] = $del['Deliveryorder']['fax'];
$this->request->data['Invoice']['email'] = $del['Deliveryorder']['email'];
$this->request->data['Invoice']['totalprice'] = $contact_email;
$this->request->data['Invoice']['jobstatus'] = $quo_list['Quotation']['is_jobcompleted'];
$this->request->data['Invoice']['paymentterms_id'] = $quo_list['Quotation']['paymentterm_id'];
$this->request->data['Invoice']['track_id'] = $quo_list['Quotation']['track_id'];
$this->request->data['Invoice']['ourrefno'] = $quo_list['Quotation']['quotationno'];
$this->request->data['Invoice']['regaddress'] = $quo_list['Quotation']['address'];
$this->request->data['Invoice']['acknowledgement_type_id'] = $quo_list['Customer']['acknowledgement_type_id'];
$this->request->data['Invoice']['instrument_type_id'] = $quo_list['Quotation']['instrument_type_id'];
$this->request->data['Invoice']['salesperson_id'] = $quo_list['Quotation']['instrument_type_id'];
$this->request->data['Invoice']['gst'] = $quo_list['Customerspecialneed']['gst'];
$this->request->data['Invoice']['currency_id'] = $quo_list['Customerspecialneed']['currency_id'];
$this->request->data['Invoice']['remarks'] = $quo_list['Customerspecialneed']['remarks'];
$this->request->data['Invoice']['service_type'] = $quo_list['Customerspecialneed']['Service']['servicetype'];
$this->request->data['Invoice']['currencyconversionrate'] = $quo_list['branch']['Currency']['exchangerate'];
$this->request->data['Invoice']['discount'] = $quo_list['Quotation']['discount'];
$this->request->data['Invoice']['salesorder_id'] = $del['Deliveryorder']['salesorder_id'];
$this->request->data['Invoice']['po_generate_type'] = $quo_list['Quotation']['po_generate_type'];
$this->request->data['Invoice']['is_assign_po'] = $quo_list['Quotation']['is_assign_po'];
$this->request->data['Invoice']['is_approved'] = 0;
$this->request->data['Invoice']['approved_date'] = '';
$this->Invoice->create();
$this->Invoice->save($this->request->data['Invoice']);
$this->Quotation->updateAll(array('Quotation.is_invoice_created' => 1), array('Quotation.quotationno' => $quo_list['Quotation']['quotationno']));
foreach ($del_list as $del) {
$this->Deliveryorder->updateAll(array('Deliveryorder.is_invoice_created' => 1), array('Deliveryorder.id' => $del['Deliveryorder']['id']));
}
/******************
* Data Log Activity
*/
$this->request->data['Datalog']['logname'] = 'Invoice';
$this->request->data['Datalog']['logactivity'] = 'Created';
$this->request->data['Datalog']['logid'] = $dmt;
$this->request->data['Datalog']['user_id'] = $this->Session->read('sess_userid');
$a = $this->Datalog->save($this->request->data['Datalog']);
/******************/
$this->Session->setFlash(__('The Invoice has been created', h($dmt)));
}
function get_value($field_name, $dest = DST_VARIABLE)
{
$field = $this->data_fields[$field_name];
if (!is_object($field)) {
$msg = "Field {$field_name} does not exist in " . $this->data_table;
alloc_error($msg);
return $msg;
}
if (!$this->can_read_field($field_name)) {
return "Permission denied to " . $this->permissions[$this->data_fields[$field_name]->read_perm_name] . " of " . $this->data_table . "." . $field_name;
}
$c = $this->currency;
if (isset($this->data_fields["currencyTypeID"]) && imp($this->data_fields["currencyTypeID"]->get_value())) {
$c = $this->data_fields["currencyTypeID"]->get_value();
}
$c and $this->currency = $c;
return $field->get_value($dest, $this);
}
function tax($amount, $taxPercent = null)
{
// take a tax included amount and return the untaxed amount, and the amount of tax
// eg: 500 including 10% tax, returns array(454.54, 45.45)
imp($taxPercent) or $taxPercent = config::get_config_item("taxPercent");
$amount_minus_tax = $amount / ($taxPercent / 100 + 1);
$amount_of_tax = $amount / (100 / $taxPercent + 1);
return array($amount_minus_tax, $amount_of_tax);
}
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;
}
}
function save_invoice_timeSheetItems($invoiceID, $timeSheetID)
{
$timeSheet = new timeSheet();
$timeSheet->set_id($timeSheetID);
$timeSheet->select();
$currency = $timeSheet->get_value("currencyTypeID");
$timeSheet->load_pay_info();
$amount = $timeSheet->pay_info["total_customerBilledDollars"] or $amount = $timeSheet->pay_info["total_dollars"];
$project = $timeSheet->get_foreign_object("project");
$client = $project->get_foreign_object("client");
$db = new db_alloc();
$q1 = $db->query(prepare("SELECT * FROM timeSheetItem WHERE timeSheetID = %d", $timeSheetID));
while ($row = $db->row($q1)) {
if (imp($timeSheet->pay_info["customerBilledDollars"])) {
$iiUnitPrice = $timeSheet->pay_info["customerBilledDollars"];
} else {
$iiUnitPrice = page::money($currency, $row["rate"], "%mo");
}
unset($str);
if ($row["comment"] && !$row["commentPrivate"]) {
$str = $row["comment"];
}
// Look for an existing invoiceItem
$q = prepare("SELECT invoiceItem.invoiceItemID\n FROM invoiceItem\n LEFT JOIN invoice ON invoiceItem.invoiceID = invoice.invoiceID\n WHERE invoiceItem.timeSheetID = %d\n AND invoiceItem.timeSheetItemID = %d\n AND invoiceItem.invoiceID = %d\n AND invoice.invoiceStatus != 'finished'\n ORDER BY iiDate DESC LIMIT 1\n ", $timeSheet->get_id(), $row["timeSheetItemID"], $invoiceID);
$q2 = $db->query($q);
$r2 = $db->row($q2);
$ii = new invoiceItem();
if ($r2["invoiceItemID"]) {
$ii->set_id($r2["invoiceItemID"]);
}
$ii->currency = $currency;
$ii->set_value("invoiceID", $invoiceID);
$ii->set_value("timeSheetID", $timeSheet->get_id());
$ii->set_value("timeSheetItemID", $row["timeSheetItemID"]);
$ii->set_value("iiMemo", "Time Sheet for " . person::get_fullname($timeSheet->get_value("personID")) . ", Project: " . $project->get_value("projectName") . ", " . $row["description"] . "\n" . $str);
$ii->set_value("iiQuantity", $row["timeSheetItemDuration"] * $row["multiplier"]);
$ii->set_value("iiUnitPrice", $iiUnitPrice);
$ii->set_value("iiAmount", $iiUnitPrice * $row["timeSheetItemDuration"] * $row["multiplier"]);
$ii->set_value("iiDate", $row["dateTimeSheetItem"]);
$ii->set_value("iiTax", config::get_config_item("taxPercent"));
$ii->save();
}
}
public static function money($c, $amount = null, $fmt = "%s%mo")
{
// Money print
$c or $c = config::get_config_item('currency');
$currencies =& get_cached_table("currencyType");
$fmt = str_replace("%mo", page::money_out($c, $amount), $fmt);
//%mo = money_out eg: 150.21
$fmt = str_replace("%mi", page::money_in($c, $amount), $fmt);
//%mi = money_in eg: 15021
$fmt = str_replace("%m", page::money_fmt($c, $amount), $fmt);
// %m = format eg: 150.2 => 150.20
$fmt = str_replace("%S", $currencies[$c]["currencyTypeLabel"], $fmt);
// %S = mandatory symbol eg: $
imp($amount) and $fmt = str_replace("%s", $currencies[$c]["currencyTypeLabel"], $fmt);
// %s = optional symbol eg: $
$fmt = str_replace("%C", $c, $fmt);
// %C = mandatory code eg: AUD
imp($amount) and $fmt = str_replace("%c", $c, $fmt);
// %c = optional code eg: AUD
$fmt = str_replace("%N", $currencies[$c]["currencyTypeName"], $fmt);
// %N = mandatory name eg: Australian dollars
imp($amount) and $fmt = str_replace("%n", $currencies[$c]["currencyTypeName"], $fmt);
// %n = optional name eg: Australian dollars
$fmt = str_replace(array("%mo", "%mi", "%m", "%S", "%s", "%C", "%c", "%N", "%n"), "", $fmt);
// strip leftovers away
return $fmt;
}
$product->delete();
alloc_redirect($TPL["url_alloc_productList"]);
}
}
# Fixed costs
if ($_POST["save_costs"] || $_POST["save_commissions"]) {
foreach ((array) $_POST["productCostID"] as $k => $productCostID) {
// Delete
if (in_array($productCostID, (array) $_POST["deleteCost"])) {
$productCost = new productCost();
$productCost->set_id($productCostID);
$productCost->select();
$productCost->delete();
// Save
} else {
if (imp($_POST["amount"][$k])) {
$a = array("productCostID" => $productCostID, "productID" => $productID, "tfID" => $_POST["tfID"][$k], "amount" => $_POST["amount"][$k], "isPercentage" => $_POST["save_commissions"] ? 1 : 0, "description" => $_POST["description"][$k], "currencyTypeID" => $_POST["currencyTypeID"][$k] ? $_POST["currencyTypeID"][$k] : config::get_config_item("currency"), "tax" => $_POST["tax"][$k], "productCostActive" => 1);
// Hardcode AUD for commissions because productCost table uses percent and dollars in same field
$_POST["save_commissions"] and $a["currencyTypeID"] = "AUD";
$productCost = new productCost();
$productCost->read_array($a);
//$errs = $productCost->validate();
if (!$errs) {
$productCost->save();
}
}
}
}
alloc_redirect($TPL["url_alloc_product"] . "productID=" . $product->get_id());
}
$m = new meta("currencyType");
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_list_filter($filter = array())
{
$filter["type"] and $sql[] = prepare("reminderType='%s'", $filter["type"]);
$filter["id"] and $sql[] = prepare("reminderLinkID=%d", $filter["id"]);
$filter["reminderID"] and $sql[] = prepare("reminder.reminderID=%d", $filter["reminderID"]);
$filter["filter_recipient"] and $sql[] = prepare("personID = %d", $filter["filter_recipient"]);
imp($filter["filter_reminderActive"]) and $sql[] = prepare("reminderActive = %d", $filter["filter_reminderActive"]);
return $sql;
}
