function execute()
{
$this->obj_form = new form_input();
$this->obj_form->formname = "bankstatementimport";
$this->obj_form->language = $_SESSION["user"]["lang"];
$this->obj_form->action = "accounts/import/bankstatement-process.php";
$this->obj_form->method = "post";
$structure = NULL;
$structure["fieldname"] = "BANK_STATEMENT";
$structure["type"] = "file";
$this->obj_form->add_input($structure);
$structure = charts_form_prepare_acccountdropdown("dest_account", "ar_payment");
$structure["options"]["req"] = "yes";
$structure["options"]["autoselect"] = "yes";
$structure["options"]["search_filter"] = "enabled";
$structure["options"]["width"] = "600";
$this->obj_form->add_input($structure);
$sql_struct_obj = new sql_query();
$sql_struct_obj->prepare_sql_settable("staff");
$sql_struct_obj->prepare_sql_addfield("id", "staff.id");
$sql_struct_obj->prepare_sql_addfield("label", "staff.staff_code");
$sql_struct_obj->prepare_sql_addfield("label1", "staff.name_staff");
$sql_struct_obj->prepare_sql_addorderby("staff_code");
$sql_struct_obj->prepare_sql_addwhere("id = 'CURRENTID' OR date_end = '0000-00-00'");
$structure = form_helper_prepare_dropdownfromobj("employeeid", $sql_struct_obj);
$structure["options"]["req"] = "yes";
$structure["options"]["width"] = "600";
$structure["options"]["search_filter"] = "enabled";
$structure["defaultvalue"] = @$_SESSION["user"]["default_employeeid"];
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "submit";
$structure["type"] = "submit";
$structure["defaultvalue"] = "Import";
$this->obj_form->add_input($structure);
$this->obj_form->subforms["upload_bank_statement"] = array("BANK_STATEMENT", "dest_account", "employeeid");
$this->obj_form->subforms["import"] = array("submit");
}
function execute()
{
/*
Create an array of all unbilled time records. We need to do the following to create this list:
1. Exclude any internal_only projects.
2. Include time which belongs to a time_group, but ONLY if the time group has not been added to an invoice.
*/
$unbilled_ids = array();
// select non-internal projects
$sql_projects_obj = new sql_query();
$sql_projects_obj->string = "SELECT projects.id as projectid, project_phases.id as phaseid FROM project_phases LEFT JOIN projects ON projects.id = project_phases.projectid WHERE projects.internal_only='0'";
$sql_projects_obj->execute();
if ($sql_projects_obj->num_rows()) {
$sql_projects_obj->fetch_array();
foreach ($sql_projects_obj->data as $project_data) {
// select non-group time records
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id FROM timereg WHERE groupid='0' AND phaseid='" . $project_data["phaseid"] . "'";
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
foreach ($sql_obj->data as $data_tmp) {
// we store the ID inside an array key, since they are unique
// and this will prevent us needed to check for the existance of
// the ID already.
$unbilled_ids[$data_tmp["id"]] = "on";
}
}
unset($sql_obj);
// select unpaid group IDs
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id FROM time_groups WHERE projectid='" . $project_data["projectid"] . "' AND invoiceid='0'";
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
foreach ($sql_obj->data as $data_group) {
// fetch all the time reg IDs belonging this group, but only select time entries marked as billable - we
// don't want to report a timegroup with unbillable time as being billed!
$sql_reg_obj = new sql_query();
$sql_reg_obj->string = "SELECT id FROM timereg WHERE groupid='" . $data_group["id"] . "' AND billable='1'";
$sql_reg_obj->execute();
if ($sql_reg_obj->num_rows()) {
$sql_reg_obj->fetch_array();
foreach ($sql_reg_obj->data as $data_tmp) {
// we store the ID inside an array key, since they are unique
// and this will prevent us needed to check for the existance of
// the ID already.
$unbilled_ids[$data_tmp["id"]] = "on";
}
}
unset($sql_reg_obj);
}
}
unset($sql_obj);
}
}
/*
Define table
*/
// establish a new table object
$this->obj_table = new table();
$this->obj_table->language = $_SESSION["user"]["lang"];
$this->obj_table->tablename = "timereg_unbilled";
// define all the columns and structure
$this->obj_table->add_column("date", "date", "timereg.date");
$this->obj_table->add_column("standard", "name_phase", "CONCAT_WS(' -- ', projects.code_project, projects.name_project, project_phases.name_phase)");
$this->obj_table->add_column("standard", "name_staff", "CONCAT_WS(' -- ', staff.staff_code, staff.name_staff)");
$this->obj_table->add_column("standard", "time_group", "time_groups.name_group");
$this->obj_table->add_column("standard", "description", "timereg.description");
$this->obj_table->add_column("hourmins", "time_booked", "timereg.time_booked");
// defaults
$this->obj_table->columns = array("date", "name_phase", "name_staff", "time_group", "description", "time_booked");
$this->obj_table->columns_order = array("date", "name_phase");
$this->obj_table->columns_order_options = array("date", "name_phase", "name_staff", "time_group", "description");
// define SQL structure
$this->obj_table->sql_obj->prepare_sql_settable("timereg");
$this->obj_table->sql_obj->prepare_sql_addfield("id", "timereg.id");
$this->obj_table->sql_obj->prepare_sql_addfield("projectid", "projects.id");
$this->obj_table->sql_obj->prepare_sql_addfield("employeeid", "timereg.employeeid");
$this->obj_table->sql_obj->prepare_sql_addfield("timegroupid", "time_groups.id");
$this->obj_table->sql_obj->prepare_sql_addfield("timegroupinvoiceid", "time_groups.invoiceid");
$this->obj_table->sql_obj->prepare_sql_addjoin("LEFT JOIN staff ON timereg.employeeid = staff.id");
$this->obj_table->sql_obj->prepare_sql_addjoin("LEFT JOIN time_groups ON timereg.groupid = time_groups.id");
$this->obj_table->sql_obj->prepare_sql_addjoin("LEFT JOIN project_phases ON timereg.phaseid = project_phases.id");
$this->obj_table->sql_obj->prepare_sql_addjoin("LEFT JOIN projects ON project_phases.projectid = projects.id");
// provide list of valid IDs
$unbilled_ids_keys = array_keys($unbilled_ids);
$unbilled_ids_count = count($unbilled_ids_keys);
$unbilled_ids_sql = "";
if ($unbilled_ids_count) {
$this->obj_table->sql_obj->prepare_sql_addwhere("timereg.id IN (" . format_arraytocommastring($unbilled_ids_keys) . ")");
}
// if the user only has access to specific staff, filter to these staff members
if ($this->access_staff_ids) {
$this->obj_table->sql_obj->prepare_sql_addwhere("timereg.employeeid IN (" . format_arraytocommastring($this->access_staff_ids) . ")");
}
/// Filtering/Display Options
// fixed options
$this->obj_table->add_fixed_option("id", $this->id);
// acceptable filter options
$structure = NULL;
$structure["fieldname"] = "date_start";
$structure["type"] = "date";
$structure["sql"] = "date >= 'value'";
$this->obj_table->add_filter($structure);
$structure = NULL;
$structure["fieldname"] = "date_end";
$structure["type"] = "date";
$structure["sql"] = "date <= 'value'";
$this->obj_table->add_filter($structure);
$structure = form_helper_prepare_dropdownfromdb("phaseid", "SELECT \n\t\t\t\t\t\t\t\t\t\t\tprojects.code_project as label,\n\t\t\t\t\t\t\t\t\t\t\tprojects.name_project as label1,\n\t\t\t\t\t\t\t\t\t\t\tproject_phases.id as id, \n\t\t\t\t\t\t\t\t\t\t\tproject_phases.name_phase as label1\n\t\t\t\t\t\t\t\t\t\tFROM `projects` \n\t\t\t\t\t\t\t\t\t\tLEFT JOIN project_phases ON project_phases.projectid = projects.id\n\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\tprojects.internal_only='0'\n\t\t\t\t\t\t\t\t\t\tORDER BY\n\t\t\t\t\t\t\t\t\t\t\tprojects.name_project,\n\t\t\t\t\t\t\t\t\t\t\tproject_phases.name_phase");
$structure["sql"] = "project_phases.id='value'";
$structure["options"]["search_filter"] = "yes";
$this->obj_table->add_filter($structure);
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("staff");
$sql_obj->prepare_sql_addfield("id", "id");
$sql_obj->prepare_sql_addfield("label", "staff_code");
$sql_obj->prepare_sql_addfield("label1", "name_staff");
if ($this->access_staff_ids) {
$sql_obj->prepare_sql_addwhere("id IN (" . format_arraytocommastring($this->access_staff_ids) . ")");
}
$sql_obj->generate_sql();
$structure = form_helper_prepare_dropdownfromdb("employeeid", $sql_obj->string);
$structure["sql"] = "timereg.employeeid='value'";
$structure["options"]["search_filter"] = "yes";
$this->obj_table->add_filter($structure);
$structure = NULL;
$structure["fieldname"] = "searchbox";
$structure["type"] = "input";
$structure["sql"] = "(timereg.description LIKE '%value%' OR project_phases.name_phase LIKE '%value%' OR staff.name_staff LIKE '%value%')";
$this->obj_table->add_filter($structure);
$structure = NULL;
$structure["fieldname"] = "groupby";
$structure["type"] = "radio";
$structure["values"] = array("none", "name_phase", "name_staff");
$structure["defaultvalue"] = "none";
$this->obj_table->add_filter($structure);
// create totals
$this->obj_table->total_columns = array("time_booked");
// load options form
$this->obj_table->load_options_form();
// add group by options
if ($this->obj_table->filter["filter_groupby"]["defaultvalue"] != "none") {
$this->obj_table->sql_obj->prepare_sql_addgroupby($this->obj_table->filter["filter_groupby"]["defaultvalue"]);
// replace timereg value with SUM query
$this->obj_table->structure["time_booked"]["dbname"] = "SUM(timereg.time_booked)";
switch ($this->obj_table->filter["filter_groupby"]["defaultvalue"]) {
case "name_staff":
$this->obj_table->columns = array("name_staff", "time_booked");
$this->obj_table->columns_order = array();
$this->obj_table->columns_order_options = array("name_staff");
break;
case "name_phase":
$this->obj_table->columns = array("name_phase", "time_booked");
$this->obj_table->columns_order = array();
$this->obj_table->columns_order_options = array("name_phase");
break;
}
}
// generate & execute SQL query (only if time entries exist)
$this->obj_table->generate_sql();
if ($unbilled_ids_count) {
$this->obj_table->load_data_sql();
}
// delete any rows which belong to processed time groups
for ($i = 0; $i < $this->obj_table->data_num_rows; $i++) {
if ($this->obj_table->data[$i]["timegroupinvoiceid"]) {
$this->obj_table->data[$i] = NULL;
}
}
}
function credit_render_invoiceselect($type, $id, $processpage)
{
log_debug("inc_credits", "credit_render_summarybox({$type}, {$id})");
// fetch credit information
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("account_{$type}");
if ($type == "ar_credit") {
$sql_obj->prepare_sql_addfield("date_sent");
$sql_obj->prepare_sql_addfield("sentmethod");
}
$sql_obj->prepare_sql_addfield("code_credit");
$sql_obj->prepare_sql_addfield("amount_total");
$sql_obj->prepare_sql_addfield("invoiceid");
$sql_obj->prepare_sql_addfield("locked");
$sql_obj->prepare_sql_addwhere("id='{$id}'");
$sql_obj->prepare_sql_setlimit("1");
$sql_obj->generate_sql();
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
if ($sql_obj->data[0]["locked"]) {
// credit note is locked, nothing todo
return 1;
}
/*
Select Invoice Items
*/
$invoice_type = "unknown";
if ($type == "ar_credit") {
$invoice_type = "ar";
} elseif ($type == "ap_credit") {
$invoice_type = "ap";
}
$sql_invoice_obj = new sql_query();
$sql_invoice_obj->string = "SELECT id as itemid, type, customid, chartid, quantity, units, price, amount, description FROM account_items WHERE invoiceid='" . $sql_obj->data[0]["invoiceid"] . "' AND invoicetype='" . $invoice_type . "' AND type!='payment' AND type!='tax'";
$sql_invoice_obj->execute();
if ($sql_invoice_obj->num_rows()) {
$sql_invoice_obj->fetch_array();
}
/*
Create Form
*/
$obj_invoice_form = new form_input();
$obj_invoice_form->formname = $type . "_invoiceselect";
$obj_invoice_form->language = $_SESSION["user"]["lang"];
$obj_invoice_form->action = "index.php";
$obj_invoice_form->method = "GET";
// ID
$structure = NULL;
$structure["fieldname"] = "id";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $id;
$obj_invoice_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "page";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $processpage;
$obj_invoice_form->add_input($structure);
// submit
$structure = NULL;
$structure["fieldname"] = "submit";
$structure["type"] = "submit";
$structure["defaultvalue"] = "submit_add_credit_item";
$obj_invoice_form->add_input($structure);
/*
Generate Items Radio Array
*/
if ($sql_invoice_obj->num_rows()) {
$structure = NULL;
$structure["fieldname"] = "invoice_item";
$structure["type"] = "radio";
foreach ($sql_invoice_obj->data as $data_invoice) {
$description = $data_invoice["description"];
switch ($data_invoice["type"]) {
case "standard":
$description = sql_get_singlevalue("SELECT CONCAT_WS('--', code_chart, description) as value FROM account_charts WHERE id='" . $data_invoice["chartid"] . "' LIMIT 1");
break;
case "product":
$description = sql_get_singlevalue("SELECT CONCAT_WS('--', code_product, name_product) as value FROM products WHERE id='" . $data_invoice["customid"] . "' LIMIT 1");
break;
case "service":
case "service_usage":
$description = sql_get_singlevalue("SELECT name_service as value FROM services WHERE id='" . $data_invoice["customid"] . "' LIMIT 1");
break;
default:
$description = "unknown item";
break;
}
$description .= " <i>" . $data_invoice["description"] . "</i>";
$description .= " [" . format_money($data_invoice["amount"]) . " exc tax]";
$structure["values"][] = $data_invoice["itemid"];
$structure["translations"][$data_invoice["itemid"]] = $description;
}
$obj_invoice_form->add_input($structure);
}
/*
Render Form
*/
if ($sql_invoice_obj->num_rows()) {
print "<table width=\"100%\" class=\"table_highlight_info\">";
print "<tr>";
print "<td>";
print "<p><b>Select an item to be credited from the selected invoice - note that amounts can be varied once selected:</b></p>";
print "<form method=\"" . $obj_invoice_form->method . "\" action=\"" . $obj_invoice_form->action . "\">";
$obj_invoice_form->render_field("invoice_item");
print "<br>";
$obj_invoice_form->render_field("id");
$obj_invoice_form->render_field("page");
$obj_invoice_form->render_field("submit");
print "</form>";
print "</td>";
print "</tr>";
print "</table>";
} else {
/*
No invoice items!
*/
format_msgbox("important", "<p>Unable to add any items to this credit note - the selected invoice has no items on it.</p>");
}
print "<br>";
}
}
function execute()
{
/*
Filter selection form
*/
// fetch existing values
$this->date_end = @security_script_input("/^[0-9]*-[0-9]*-[0-9]*\$/", $_GET["date_as_of_yyyy"] . "-" . $_GET["date_as_of_mm"] . "-" . $_GET["date_as_of_dd"]);
$this->mode = @security_script_input("/^\\S*\$/", $_GET["mode"]);
if (!$this->mode) {
if ($_SESSION["account_reports"]["mode"]) {
$this->mode = $_SESSION["account_reports"]["mode"];
} else {
$this->mode = "Accrual/Invoice";
}
}
if (!$this->date_end || $this->date_end == "--") {
if ($_SESSION["account_reports"]["date_end"]) {
$this->date_end = $_SESSION["account_reports"]["date_end"];
} else {
$this->date_end = date("Y-m-d");
}
}
// save to session vars
$_SESSION["account_reports"]["date_end"] = $this->date_end;
$_SESSION["account_reports"]["mode"] = $this->mode;
// define form
$this->obj_form = new form_input();
$this->obj_form->method = "get";
$this->obj_form->action = "index.php";
$this->obj_form->formname = "accounts_report_incomestatement";
$this->obj_form->language = $_SESSION["user"]["lang"];
// hidden values
$structure = NULL;
$structure["fieldname"] = "page";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $_GET["page"];
$this->obj_form->add_input($structure);
// date selection
$structure = NULL;
$structure["fieldname"] = "date_as_of";
$structure["type"] = "date";
$structure["defaultvalue"] = $this->date_end;
$this->obj_form->add_input($structure);
// mode selection
$structure = NULL;
$structure["fieldname"] = "mode";
$structure["type"] = "radio";
$structure["values"] = array("Accrual/Invoice", "Cash");
$structure["defaultvalue"] = $this->mode;
$this->obj_form->add_input($structure);
// submit
$structure = NULL;
$structure["fieldname"] = "submit";
$structure["type"] = "submit";
$structure["defaultvalue"] = "Apply Filter Options";
$this->obj_form->add_input($structure);
/*
Asset Accounts
*/
// chart details
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("account_charts");
$sql_obj->prepare_sql_addfield("id");
$sql_obj->prepare_sql_addfield("code_chart");
$sql_obj->prepare_sql_addfield("description");
$sql_obj->prepare_sql_addwhere("chart_type='2'");
$sql_obj->generate_sql();
$sql_obj->execute();
$sql_obj->fetch_array();
$this->data_assets = $sql_obj->data;
unset($sql_obj);
/*
Liability Accounts
*/
// chart details
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("account_charts");
$sql_obj->prepare_sql_addfield("id");
$sql_obj->prepare_sql_addfield("code_chart");
$sql_obj->prepare_sql_addfield("description");
$sql_obj->prepare_sql_addwhere("chart_type='3'");
$sql_obj->generate_sql();
$sql_obj->execute();
$sql_obj->fetch_array();
$this->data_liabilities = $sql_obj->data;
unset($sql_obj);
/*
Equitity Accounts
*/
// chart details
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("account_charts");
$sql_obj->prepare_sql_addfield("id");
$sql_obj->prepare_sql_addfield("code_chart");
$sql_obj->prepare_sql_addfield("description");
$sql_obj->prepare_sql_addwhere("chart_type='4'");
$sql_obj->generate_sql();
$sql_obj->execute();
$sql_obj->fetch_array();
$this->data_equity = $sql_obj->data;
unset($sql_obj);
/*
Income Charts
*/
// chart details
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("account_charts");
$sql_obj->prepare_sql_addfield("id");
$sql_obj->prepare_sql_addfield("code_chart");
$sql_obj->prepare_sql_addfield("description");
$sql_obj->prepare_sql_addwhere("chart_type='5'");
$sql_obj->generate_sql();
$sql_obj->execute();
$sql_obj->fetch_array();
$this->data_income = $sql_obj->data;
unset($sql_obj);
/*
Expense Charts
*/
// chart details
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("account_charts");
$sql_obj->prepare_sql_addfield("id");
$sql_obj->prepare_sql_addfield("code_chart");
$sql_obj->prepare_sql_addfield("description");
$sql_obj->prepare_sql_addwhere("chart_type='6'");
$sql_obj->generate_sql();
$sql_obj->execute();
$sql_obj->fetch_array();
$this->data_expense = $sql_obj->data;
unset($sql_obj);
/*
Amounts
This section fetches the total amounts for the different accounts. This code is a bit different to the invoicestatement code
and instead of working on an invoice basis, works on a transaction basis.
Accural/Invoice:
1. Fetch all transactions from account_trans
2. Total up credits+debits for each account
Cash:
1. Fetch all transactions from account_trans.
2. Total up any ar_pay, ap_pay or gl transactions.
2. For all other transactions, do a lookup against the invoice - if the invoice has been paid at all, (either partially
or fully) then include the transaction.
Note: The behaviour of including partically paid invoces is different to how all the other application features (such as tax
collected/paid) work, however it is required in order to have the balance sheet showing correct tax/income amounts.
This behaviour is also the same as how SQL-Ledger generates balance sheets, which will not confuse users whom have migrated.
*/
// Run through all the transactions
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("account_trans");
$sql_obj->prepare_sql_addfield("id");
$sql_obj->prepare_sql_addfield("type");
$sql_obj->prepare_sql_addfield("customid");
$sql_obj->prepare_sql_addfield("chartid");
$sql_obj->prepare_sql_addfield("amount_debit");
$sql_obj->prepare_sql_addfield("amount_credit");
// date options
if ($this->date_end) {
$sql_obj->prepare_sql_addwhere("date_trans <= '" . $this->date_end . "'");
}
// run through transaction entries
$sql_obj->generate_sql();
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
foreach ($sql_obj->data as $data_trans) {
log_debug("balancesheet", "Processing transaction " . $data_trans["id"] . " with type " . $data_trans["type"] . "");
$valid = 0;
if ($this->mode == "Cash") {
// CASH
if ($data_trans["type"] == "ar_pay" || $data_trans["type"] == "ap_pay" || $data_trans["type"] == "gl") {
$valid = 1;
} else {
// check if the transaction invoice has any payments or not
$sql_invoice_obj = new sql_query();
if ($data_trans["type"] == "ap" || $data_trans["type"] == "ap_tax") {
$sql_invoice_obj->prepare_sql_settable("account_ap");
} else {
$sql_invoice_obj->prepare_sql_settable("account_ar");
}
$sql_invoice_obj->prepare_sql_addfield("amount_paid");
$sql_invoice_obj->prepare_sql_addwhere("id='" . $data_trans["customid"] . "'");
$sql_invoice_obj->prepare_sql_setlimit("1");
$sql_invoice_obj->generate_sql();
$sql_invoice_obj->execute();
if ($sql_invoice_obj->num_rows()) {
$sql_invoice_obj->fetch_array();
if ($sql_invoice_obj->data[0]["amount_paid"] > 0) {
// invoice has some amount of payment against it, and should therefore be displayed.
$valid = 1;
}
} else {
log_write("error", "balancesheet", "Unable to find parent invoice (" . $data_trans["customid"] . ") for transaction " . $data_trans["id"] . " - Database might be damanged.");
}
unset($sql_invoice_obj);
}
} else {
// ACCURAL/INVOICE
$valid = 1;
}
if ($valid) {
log_debug("balancesheet", "Transaction is valid - chartid: " . $data_trans["chartid"] . ", credit: " . $data_trans["amount_credit"] . ", debit: " . $data_trans["amount_debit"] . "");
// run through asset charts
for ($i = 0; $i < count(array_keys($this->data_assets)); $i++) {
if ($data_trans["chartid"] == $this->data_assets[$i]["id"]) {
@($this->data_assets[$i]["amount"] += $data_trans["amount_debit"]);
@($this->data_assets[$i]["amount"] -= $data_trans["amount_credit"]);
}
}
// end of loop through asset charts
// run through liability charts
for ($i = 0; $i < count(array_keys($this->data_liabilities)); $i++) {
if ($data_trans["chartid"] == $this->data_liabilities[$i]["id"]) {
@($this->data_liabilities[$i]["amount"] -= $data_trans["amount_debit"]);
@($this->data_liabilities[$i]["amount"] += $data_trans["amount_credit"]);
}
}
// end of loop through liability charts
// run through equity charts
if (isset($this->data_equity)) {
for ($i = 0; $i < count(array_keys($this->data_equity)); $i++) {
if ($data_trans["chartid"] == $this->data_equity[$i]["id"]) {
@($this->data_equity[$i]["amount"] -= $data_trans["amount_debit"]);
@($this->data_equity[$i]["amount"] += $data_trans["amount_credit"]);
}
}
}
// end of loop through equity charts
// run through income charts
for ($i = 0; $i < count(array_keys($this->data_income)); $i++) {
if ($data_trans["chartid"] == $this->data_income[$i]["id"]) {
@($this->data_income[$i]["amount"] -= $data_trans["amount_debit"]);
@($this->data_income[$i]["amount"] += $data_trans["amount_credit"]);
}
}
// end of loop through income charts
// run through expense charts
for ($i = 0; $i < count(array_keys($this->data_expense)); $i++) {
if ($data_trans["chartid"] == $this->data_expense[$i]["id"]) {
@($this->data_expense[$i]["amount"] += $data_trans["amount_debit"]);
@($this->data_expense[$i]["amount"] -= $data_trans["amount_credit"]);
}
}
// end of loop through expense charts
}
// end if valid
}
// end of transaction loop
}
// end if transaction exist
/*
Totals
*/
// assets
if ($this->data_assets) {
for ($i = 0; $i < count(array_keys($this->data_assets)); $i++) {
@($this->data_totals["assets"] += $this->data_assets[$i]["amount"]);
}
}
// liabilities
if ($this->data_liabilities) {
for ($i = 0; $i < count(array_keys($this->data_liabilities)); $i++) {
@($this->data_totals["liabilities"] += $this->data_liabilities[$i]["amount"]);
}
}
// equity
if ($this->data_equity) {
for ($i = 0; $i < count(array_keys($this->data_equity)); $i++) {
@($this->data_totals["equity"] += $this->data_equity[$i]["amount"]);
}
}
// income
if ($this->data_income) {
for ($i = 0; $i < count(array_keys($this->data_income)); $i++) {
@($this->data_totals["income"] += $this->data_income[$i]["amount"]);
}
}
// expense
if ($this->data_expense) {
for ($i = 0; $i < count(array_keys($this->data_expense)); $i++) {
@($this->data_totals["expense"] += $this->data_expense[$i]["amount"]);
}
}
// final
$this->data_totals["current_earnings"] = $this->data_totals["income"] - $this->data_totals["expense"];
$this->data_totals["equity"] += $this->data_totals["current_earnings"];
$this->data_totals["liabilities_and_equity"] = $this->data_totals["liabilities"] + $this->data_totals["equity"];
// formatting
$this->data_totals["liabilities"] = format_money($this->data_totals["liabilities"]);
$this->data_totals["assets"] = format_money($this->data_totals["assets"]);
$this->data_totals["equity"] = format_money($this->data_totals["equity"]);
$this->data_totals["current_earnings"] = format_money($this->data_totals["current_earnings"]);
$this->data_totals["liabilities_and_equity"] = format_money($this->data_totals["liabilities_and_equity"]);
}
function execute()
{
log_debug("quote_form_details", "Executing execute()");
if ($this->quoteid) {
$this->mode = "edit";
} else {
$this->mode = "add";
}
/*
Start Form
*/
$this->obj_form = new form_input();
$this->obj_form->formname = "quote_" . $this->mode;
$this->obj_form->language = $_SESSION["user"]["lang"];
$this->obj_form->action = $this->processpage;
$this->obj_form->method = "POST";
/*
Define form structure
*/
// basic details
$sql_struct_obj = new sql_query();
$sql_struct_obj->prepare_sql_settable("customers");
$sql_struct_obj->prepare_sql_addfield("id", "customers.id");
$sql_struct_obj->prepare_sql_addfield("label", "customers.code_customer");
$sql_struct_obj->prepare_sql_addfield("label1", "customers.name_customer");
$sql_struct_obj->prepare_sql_addorderby("code_customer");
$sql_struct_obj->prepare_sql_addwhere("id = 'CURRENTID' OR date_end = '0000-00-00'");
$structure = form_helper_prepare_dropdownfromobj("customerid", $sql_struct_obj);
$structure["options"]["req"] = "yes";
$structure["options"]["width"] = "600";
$this->obj_form->add_input($structure);
$sql_struct_obj = new sql_query();
$sql_struct_obj->prepare_sql_settable("staff");
$sql_struct_obj->prepare_sql_addfield("id", "staff.id");
$sql_struct_obj->prepare_sql_addfield("label", "staff.staff_code");
$sql_struct_obj->prepare_sql_addfield("label1", "staff.name_staff");
$sql_struct_obj->prepare_sql_addorderby("staff_code");
$sql_struct_obj->prepare_sql_addwhere("id = 'CURRENTID' OR date_end = '0000-00-00'");
$structure = form_helper_prepare_dropdownfromobj("employeeid", $sql_struct_obj);
$structure["options"]["req"] = "yes";
$structure["options"]["autoselect"] = "yes";
$structure["options"]["width"] = "600";
$structure["defaultvalue"] = $_SESSION["user"]["default_employeeid"];
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "code_quote";
$structure["type"] = "input";
if ($this->mode == "edit") {
$structure["options"]["req"] = "yes";
}
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "notes";
$structure["type"] = "textarea";
$structure["options"]["height"] = "100";
$structure["options"]["width"] = 500;
$this->obj_form->add_input($structure);
// dates
$structure = NULL;
$structure["fieldname"] = "date_trans";
$structure["type"] = "date";
$structure["defaultvalue"] = date("Y-m-d");
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "date_validtill";
$structure["type"] = "date";
$structure["defaultvalue"] = quotes_calc_duedate(date("Y-m-d"));
$this->obj_form->add_input($structure);
// ID
$structure = NULL;
$structure["fieldname"] = "id_quote";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $this->quoteid;
$this->obj_form->add_input($structure);
// submit
$structure = NULL;
$structure["fieldname"] = "submit";
$structure["type"] = "submit";
$structure["defaultvalue"] = "Save Changes";
$this->obj_form->add_input($structure);
// load data
$this->obj_form->sql_query = "SELECT customerid, employeeid, code_quote, notes, date_trans, date_validtill FROM account_quotes WHERE id='" . $this->quoteid . "'";
$this->obj_form->load_data();
// define subforms
$this->obj_form->subforms["quote_details"] = array("customerid", "employeeid", "code_quote", "date_trans", "date_validtill");
$this->obj_form->subforms["quote_other"] = array("notes");
$this->obj_form->subforms["hidden"] = array("id_quote");
$this->obj_form->subforms["submit"] = array("submit");
}
function execute()
{
/*
Define form structure
*/
$this->obj_form = new form_input();
$this->obj_form->formname = "timebilled_view";
$this->obj_form->language = $_SESSION["user"]["lang"];
$this->obj_form->action = "projects/timebilled-edit-process.php";
$this->obj_form->method = "post";
// general
$structure = NULL;
$structure["fieldname"] = "name_group";
$structure["type"] = "input";
$structure["options"]["req"] = "yes";
$structure["defaultvalue"] = date("Y-m");
$this->obj_form->add_input($structure);
$sql_struct_obj = new sql_query();
$sql_struct_obj->prepare_sql_settable("customers");
$sql_struct_obj->prepare_sql_addfield("id", "customers.id");
$sql_struct_obj->prepare_sql_addfield("label", "customers.code_customer");
$sql_struct_obj->prepare_sql_addfield("label1", "customers.name_customer");
$sql_struct_obj->prepare_sql_addorderby("code_customer");
$sql_struct_obj->prepare_sql_addwhere("id = 'CURRENTID' OR date_end = '0000-00-00'");
$structure = form_helper_prepare_dropdownfromobj("customerid", $sql_struct_obj);
// $structure = form_helper_prepare_dropdownfromdb("customerid", "SELECT id, code_customer as label, name_customer as label1 FROM customers ORDER BY name_customer");
$structure["options"]["req"] = "yes";
$structure["options"]["width"] = "600";
$this->obj_form->add_input($structure);
if ($this->groupid) {
$structure = NULL;
$structure["fieldname"] = "code_invoice";
$structure["type"] = "text";
$this->obj_form->add_input($structure);
}
$structure = NULL;
$structure["fieldname"] = "description";
$structure["type"] = "textarea";
$structure["options"]["width"] = "600";
$structure["options"]["height"] = "60";
$this->obj_form->add_input($structure);
// hidden values
$structure = NULL;
$structure["fieldname"] = "projectid";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $this->id;
$this->obj_form->add_input($structure);
$structure = null;
$structure["fieldname"] = "groupid";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $this->groupid;
$this->obj_form->add_input($structure);
/*
Define checkboxes for all unassigned time entries
*/
$this->obj_sql_entries = new sql_query();
$this->obj_sql_entries->prepare_sql_settable("timereg");
$this->obj_sql_entries->prepare_sql_addfield("id", "timereg.id");
$this->obj_sql_entries->prepare_sql_addfield("date", "timereg.date");
$this->obj_sql_entries->prepare_sql_addfield("name_phase", "project_phases.name_phase");
$this->obj_sql_entries->prepare_sql_addfield("name_staff", "CONCAT_WS(' -- ', staff.staff_code, staff.name_staff)");
$this->obj_sql_entries->prepare_sql_addfield("description", "timereg.description");
$this->obj_sql_entries->prepare_sql_addfield("time_booked", "timereg.time_booked");
$this->obj_sql_entries->prepare_sql_addfield("groupid", "timereg.groupid");
$this->obj_sql_entries->prepare_sql_addfield("billable", "timereg.billable");
$this->obj_sql_entries->prepare_sql_addjoin("LEFT JOIN staff ON timereg.employeeid = staff.id");
$this->obj_sql_entries->prepare_sql_addjoin("LEFT JOIN project_phases ON timereg.phaseid = project_phases.id");
$this->obj_sql_entries->prepare_sql_addwhere("project_phases.projectid='" . $this->id . "'");
if ($this->groupid) {
$this->obj_sql_entries->prepare_sql_addwhere("(groupid='" . $this->groupid . "' OR !groupid)");
} else {
$this->obj_sql_entries->prepare_sql_addwhere("!groupid");
}
if ($this->access_staff_ids) {
$this->obj_sql_entries->prepare_sql_addwhere("timereg.employeeid IN (" . format_arraytocommastring($this->access_staff_ids) . ")");
}
$this->obj_sql_entries->generate_sql();
$this->obj_sql_entries->execute();
if ($this->obj_sql_entries->num_rows()) {
$this->obj_sql_entries->fetch_array();
foreach ($this->obj_sql_entries->data as $data) {
// define the billable check box
$structure = NULL;
$structure["fieldname"] = "time_" . $data["id"] . "_bill";
$structure["type"] = "checkbox";
$structure["options"]["label"] = " ";
if ($data["groupid"] == $this->groupid && $data["billable"] == "1") {
$structure["defaultvalue"] = "on";
}
$this->obj_form->add_input($structure);
// define the nobill check box
$structure = NULL;
$structure["fieldname"] = "time_" . $data["id"] . "_nobill";
$structure["type"] = "checkbox";
$structure["options"]["label"] = " ";
if ($data["groupid"] == $this->groupid && $data["billable"] == "0") {
$structure["defaultvalue"] = "on";
}
$this->obj_form->add_input($structure);
}
}
// submit button
$structure = NULL;
$structure["fieldname"] = "submit";
$structure["type"] = "submit";
if ($this->groupid) {
$structure["defaultvalue"] = "Save Changes";
} else {
$structure["defaultvalue"] = "Create Time Group";
}
$this->obj_form->add_input($structure);
// fetch the form data if editing
if ($this->groupid) {
$this->obj_form->sql_query = "SELECT time_groups.name_group, time_groups.customerid, time_groups.description, account_ar.code_invoice FROM time_groups LEFT JOIN account_ar ON account_ar.id = time_groups.invoiceid WHERE time_groups.id='" . $this->groupid . "' LIMIT 1";
$this->obj_form->load_data();
} else {
// load any data returned due to errors
$this->obj_form->load_data_error();
}
}
function execute()
{
log_debug("invoice_form_details", "Executing execute()");
if ($this->invoiceid) {
$this->mode = "edit";
} else {
$this->mode = "add";
}
/*
Make sure invoice does exist and fetch locked status
*/
if ($this->mode == "edit") {
$sql_invoice_obj = new sql_query();
$sql_invoice_obj->string = "SELECT id, locked FROM account_" . $this->type . " WHERE id='" . $this->invoiceid . "' LIMIT 1";
$sql_invoice_obj->execute();
if (!$sql_invoice_obj->num_rows()) {
print "<p><b>Error: The requested invoice does not exist. <a href=\"index.php?page=accounts/" . $this->type . "/" . $this->type . ".php\">Try looking on the invoice/invoice list page.</a></b></p>";
return 0;
} else {
$sql_invoice_obj->fetch_array();
$this->locked = $sql_invoice_obj->data[0]["locked"];
}
}
/*
Start Form
*/
$this->obj_form = new form_input();
$this->obj_form->formname = $this->type . "_invoice_" . $this->mode;
$this->obj_form->language = $_SESSION["user"]["lang"];
$this->obj_form->action = $this->processpage;
$this->obj_form->method = "POST";
/*
Define form structure
*/
// basic details
if ($this->type == "ap") {
$sql_struct_obj = new sql_query();
$sql_struct_obj->prepare_sql_settable("vendors");
$sql_struct_obj->prepare_sql_addfield("id", "vendors.id");
$sql_struct_obj->prepare_sql_addfield("label", "vendors.code_vendor");
$sql_struct_obj->prepare_sql_addfield("label1", "vendors.name_vendor");
$sql_struct_obj->prepare_sql_addorderby("code_vendor");
$sql_struct_obj->prepare_sql_addwhere("id = 'CURRENTID' OR date_end = '0000-00-00'");
$structure = form_helper_prepare_dropdownfromobj("vendorid", $sql_struct_obj);
$structure["options"]["req"] = "yes";
$structure["options"]["width"] = "600";
$structure["options"]["search_filter"] = "enabled";
$structure["defaultvalue"] = $this->vendor_id;
$this->obj_form->add_input($structure);
} else {
// load customer dropdown
$sql_struct_obj = new sql_query();
$sql_struct_obj->prepare_sql_settable("customers");
$sql_struct_obj->prepare_sql_addfield("id", "customers.id");
$sql_struct_obj->prepare_sql_addfield("label", "customers.code_customer");
$sql_struct_obj->prepare_sql_addfield("label1", "customers.name_customer");
$sql_struct_obj->prepare_sql_addorderby("code_customer");
$sql_struct_obj->prepare_sql_addwhere("id = 'CURRENTID' OR date_end = '0000-00-00'");
$structure = form_helper_prepare_dropdownfromobj("customerid", $sql_struct_obj);
$structure["options"]["req"] = "yes";
$structure["options"]["width"] = "600";
$structure["options"]["search_filter"] = "enabled";
$structure["defaultvalue"] = $this->customer_id;
$this->obj_form->add_input($structure);
}
$sql_struct_obj = new sql_query();
$sql_struct_obj->prepare_sql_settable("staff");
$sql_struct_obj->prepare_sql_addfield("id", "staff.id");
$sql_struct_obj->prepare_sql_addfield("label", "staff.staff_code");
$sql_struct_obj->prepare_sql_addfield("label1", "staff.name_staff");
$sql_struct_obj->prepare_sql_addorderby("staff_code");
$sql_struct_obj->prepare_sql_addwhere("id = 'CURRENTID' OR date_end = '0000-00-00'");
$structure = form_helper_prepare_dropdownfromobj("employeeid", $sql_struct_obj);
$structure["options"]["req"] = "yes";
$structure["options"]["autoselect"] = "yes";
$structure["options"]["width"] = "600";
$structure["options"]["search_filter"] = "enabled";
$structure["defaultvalue"] = @$_SESSION["user"]["default_employeeid"];
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "code_invoice";
$structure["type"] = "input";
if ($this->mode == "edit") {
$structure["options"]["req"] = "yes";
}
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "code_ordernumber";
$structure["type"] = "input";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "code_ponumber";
$structure["type"] = "input";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "notes";
$structure["type"] = "textarea";
$structure["options"]["height"] = "100";
$structure["options"]["width"] = 500;
$this->obj_form->add_input($structure);
// dates
$structure = NULL;
$structure["fieldname"] = "date_trans";
$structure["type"] = "date";
$structure["defaultvalue"] = date("Y-m-d");
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "date_due";
$structure["type"] = "date";
$structure["defaultvalue"] = invoice_calc_duedate(date("Y-m-d"));
$this->obj_form->add_input($structure);
// destination account
if ($this->type == "ap") {
$structure = charts_form_prepare_acccountdropdown("dest_account", "ap_summary_account");
} else {
$structure = charts_form_prepare_acccountdropdown("dest_account", "ar_summary_account");
}
$structure["options"]["req"] = "yes";
$structure["options"]["autoselect"] = "yes";
$structure["options"]["search_filter"] = "enabled";
$structure["options"]["width"] = "600";
$this->obj_form->add_input($structure);
// ID
$structure = NULL;
$structure["fieldname"] = "id_invoice";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $this->invoiceid;
$this->obj_form->add_input($structure);
// submit
$structure = NULL;
$structure["fieldname"] = "submit";
$structure["type"] = "submit";
$structure["defaultvalue"] = "Save Changes";
$this->obj_form->add_input($structure);
// load data
if ($this->type == "ap") {
$this->obj_form->sql_query = "SELECT vendorid, employeeid, code_invoice, code_ordernumber, code_ponumber, notes, date_trans, date_due, dest_account FROM account_" . $this->type . " WHERE id='" . $this->invoiceid . "'";
} else {
$this->obj_form->sql_query = "SELECT customerid, employeeid, code_invoice, code_ordernumber, code_ponumber, notes, date_trans, date_due, dest_account FROM account_" . $this->type . " WHERE id='" . $this->invoiceid . "'";
}
$this->obj_form->load_data();
/*
Fetch any provided values from $_GET if adding a new invoice and no error data provided
*/
if ($this->mode == "add" && error_check()) {
$this->obj_form->structure["customerid"]["defaultvalue"] = @security_script_input('/^[0-9]*$/', $_GET["customerid"]);
$this->obj_form->structure["vendorid"]["defaultvalue"] = @security_script_input('/^[0-9]*$/', $_GET["vendorid"]);
}
// define subforms
if ($this->type == "ap") {
$this->obj_form->subforms[$this->type . "_invoice_details"] = array("vendorid", "employeeid", "code_invoice", "code_ordernumber", "code_ponumber", "date_trans", "date_due");
} else {
$this->obj_form->subforms[$this->type . "_invoice_details"] = array("customerid", "employeeid", "code_invoice", "code_ordernumber", "code_ponumber", "date_trans", "date_due");
}
$this->obj_form->subforms[$this->type . "_invoice_financials"] = array("dest_account");
$this->obj_form->subforms[$this->type . "_invoice_other"] = array("notes");
$this->obj_form->subforms["hidden"] = array("id_invoice");
if ($this->locked) {
$this->obj_form->subforms["submit"] = array();
} else {
$this->obj_form->subforms["submit"] = array("submit");
}
return 1;
}
function invoice_render_summarybox($type, $id)
{
log_debug("inc_invoices", "invoice_render_summarybox({$type}, {$id})");
// fetch invoice information
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("account_{$type}");
if ($type == "ar") {
$sql_obj->prepare_sql_addfield("date_sent");
$sql_obj->prepare_sql_addfield("sentmethod");
}
$sql_obj->prepare_sql_addfield("code_invoice");
$sql_obj->prepare_sql_addfield("amount_total");
$sql_obj->prepare_sql_addfield("amount_paid");
$sql_obj->prepare_sql_addwhere("id='{$id}'");
$sql_obj->prepare_sql_setlimit("1");
$sql_obj->generate_sql();
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
// check for presence of invoice items
$sql_item_obj = new sql_query();
$sql_item_obj->string = "SELECT id FROM account_items WHERE invoicetype='{$type}' AND invoiceid='{$id}' LIMIT 1";
$sql_item_obj->execute();
if (!$sql_item_obj->num_rows()) {
print "<table width=\"100%\" class=\"table_highlight_important\">";
print "<tr>";
print "<td>";
print "<b>Invoice " . $sql_obj->data[0]["code_invoice"] . " has no items on it</b>";
print "<p>This invoice is currently empty, add some items to it using the Invoice Items page.</p>";
print "</td>";
print "</tr>";
print "</table>";
} else {
if ($sql_obj->data[0]["amount_paid"] == $sql_obj->data[0]["amount_total"]) {
print "<table width=\"100%\" class=\"table_highlight_open\">";
print "<tr>";
print "<td>";
print "<b>Invoice " . $sql_obj->data[0]["code_invoice"] . " is closed (fully paid).</b>";
print "<p>This invoice has been fully paid and no further action is required.</p>";
print "</td>";
print "</tr>";
print "</table>";
} else {
print "<table width=\"100%\" class=\"table_highlight_important\">";
print "<tr>";
print "<td>";
print "<b>Invoice " . $sql_obj->data[0]["code_invoice"] . " is open (unpaid).</b>";
print "<table cellpadding=\"4\">";
print "<tr>";
print "<td>Total Due:</td>";
print "<td>" . format_money($sql_obj->data[0]["amount_total"]) . "</td>";
print "</tr>";
print "<tr>";
print "<td>Total Paid:</td>";
print "<td>" . format_money($sql_obj->data[0]["amount_paid"]) . "</td>";
print "</tr>";
$amount_due = $sql_obj->data[0]["amount_total"] - $sql_obj->data[0]["amount_paid"];
print "<tr>";
print "<td>Amount Due:</td>";
print "<td>" . format_money($amount_due) . "</td>";
print "</tr>";
if ($type == "ar") {
print "<tr>";
print "<td>Date Sent:</td>";
if ($sql_obj->data[0]["sentmethod"] == "") {
print "<td><i>Has not been sent to customer</i></td>";
} else {
print "<td>" . $sql_obj->data[0]["date_sent"] . " (" . $sql_obj->data[0]["sentmethod"] . ")</td>";
}
print "</tr>";
}
print "</tr></table>";
print "</td>";
print "</tr>";
print "</table>";
}
}
print "<br>";
}
}
log_write("error", "process", "The requested time group does not match the provided project ID. Potential application bug?");
}
if ($sql_obj->data[0]["locked"]) {
log_write("error", "process", "This time group can not be adjusted since it has now been locked.");
}
}
} else {
$mode = "add";
}
}
/*
Fetch all the information for the time entries.
*/
$sql_entries_obj = new sql_query();
$sql_entries_obj->prepare_sql_settable("timereg");
$sql_entries_obj->prepare_sql_addfield("id", "");
$sql_entries_obj->prepare_sql_addfield("locked", "");
$sql_entries_obj->prepare_sql_addfield("groupid", "");
$sql_entries_obj->prepare_sql_addfield("billable", "");
$sql_entries_obj->prepare_sql_addfield("time_booked", "");
if ($groupid) {
$sql_entries_obj->prepare_sql_addwhere("(groupid='{$groupid}' OR !groupid)");
} else {
$sql_entries_obj->prepare_sql_addwhere("!groupid");
}
// if user has limited employee access, only process time records for those employees
if ($access_staff_ids) {
$sql_entries_obj->prepare_sql_addwhere("employeeid IN (" . format_arraytocommastring($access_staff_ids) . ")");
}
$sql_entries_obj->generate_sql();
$sql_entries_obj->execute();
function execute()
{
/*
Define form structure
*/
$this->obj_form = new form_input();
$this->obj_form->formname = "orders_view";
$this->obj_form->language = $_SESSION["user"]["lang"];
$this->obj_form->action = "customers/orders-edit-process.php";
$this->obj_form->method = "post";
/*
Define Orders (products-style)
*/
// basic details
$structure = NULL;
$structure["fieldname"] = "date_ordered";
$structure["type"] = "date";
$structure["defaultvalue"] = date("Y-m-d");
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "type";
$structure["type"] = "text";
$structure["defaultvalue"] = $this->obj_customer->data_orders["type"];
$this->obj_form->add_input($structure);
$this->obj_form->subforms["order_basic"] = array("date_ordered", "type");
/*
Item Specifics
*/
switch ($this->obj_customer->data_orders["type"]) {
case "product":
// price
$structure = null;
$structure["fieldname"] = "price";
$structure["type"] = "money";
$this->obj_form->add_input($structure);
// quantity
$structure = null;
$structure["fieldname"] = "quantity";
$structure["type"] = "input";
$structure["options"]["width"] = 50;
$this->obj_form->add_input($structure);
// units
$structure = null;
$structure["fieldname"] = "units";
$structure["type"] = "input";
$structure["options"]["width"] = 50;
$structure["options"]["max_length"] = 10;
$this->obj_form->add_input($structure);
// product id
$sql_struct_obj = new sql_query();
$sql_struct_obj->prepare_sql_settable("products");
$sql_struct_obj->prepare_sql_addfield("id", "products.id");
$sql_struct_obj->prepare_sql_addfield("label", "products.code_product");
$sql_struct_obj->prepare_sql_addfield("label1", "products.name_product");
$sql_struct_obj->prepare_sql_addorderby("code_product");
$sql_struct_obj->prepare_sql_addwhere("id = 'currentid' or date_end = '0000-00-00'");
$structure = form_helper_prepare_dropdownfromobj("productid", $sql_struct_obj);
$structure["options"]["search_filter"] = "enabled";
$structure["options"]["width"] = "600";
$this->obj_form->add_input($structure);
// description
$structure = null;
$structure["fieldname"] = "description";
$structure["type"] = "textarea";
$structure["options"]["height"] = "50";
$structure["options"]["width"] = 500;
$this->obj_form->add_input($structure);
// discount
$structure = null;
$structure["fieldname"] = "discount";
$structure["type"] = "input";
$structure["options"]["width"] = 50;
$structure["options"]["label"] = " %";
$structure["options"]["max_length"] = "2";
$this->obj_form->add_input($structure);
// subform
$this->obj_form->subforms["order_product"] = array("productid", "price", "quantity", "units", "description", "discount");
break;
case "service":
// price
$structure = null;
$structure["fieldname"] = "price";
$structure["type"] = "money";
$this->obj_form->add_input($structure);
// discount
$structure = null;
$structure["fieldname"] = "discount";
$structure["type"] = "input";
$structure["options"]["width"] = 50;
$structure["options"]["label"] = " %";
$structure["options"]["max_length"] = "2";
$this->obj_form->add_input($structure);
// service id
$sql_struct_obj = new sql_query();
$sql_struct_obj->prepare_sql_settable("services");
$sql_struct_obj->prepare_sql_addfield("id", "services.id");
$sql_struct_obj->prepare_sql_addfield("label", "services.name_service");
$sql_struct_obj->prepare_sql_addorderby("name_service");
$structure = form_helper_prepare_dropdownfromobj("serviceid", $sql_struct_obj);
$structure["options"]["search_filter"] = "enabled";
$structure["options"]["width"] = "600";
$this->obj_form->add_input($structure);
// description
$structure = null;
$structure["fieldname"] = "description";
$structure["type"] = "textarea";
$structure["options"]["height"] = "50";
$structure["options"]["width"] = 500;
$this->obj_form->add_input($structure);
// subform
$this->obj_form->subforms["order_serice"] = array("serviceid", "price", "discount", "description");
break;
}
// end of type
// hidden values
$structure = NULL;
$structure["fieldname"] = "id_customer";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $this->obj_customer->id;
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "id_order";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $this->obj_customer->id_order;
$this->obj_form->add_input($structure);
// submit button
$structure = NULL;
$structure["fieldname"] = "submit";
$structure["type"] = "submit";
$structure["defaultvalue"] = "Save Changes";
$this->obj_form->add_input($structure);
// define base subforms
$this->obj_form->subforms["hidden"] = array("id_customer", "id_order");
if (user_permissions_get("customers_orders")) {
$this->obj_form->subforms["submit"] = array("submit");
} else {
$this->obj_form->subforms["submit"] = array();
}
// fetch the form data if editing
if ($this->obj_customer->id_order) {
// data already loaded with $this->obj_customers->load_data_order(), now
// we need to fetch each order item detail.
$this->obj_customer->data_orders["productid"] = $this->obj_customer->data_orders["customid"];
$this->obj_customer->data_orders["serviceid"] = $this->obj_customer->data_orders["customid"];
$this->obj_form->load_data_object($this->obj_customer->data_orders);
} else {
// set defaults
$this->obj_form->structure["quantity"]["defaultvalue"] = 1;
}
if (error_check()) {
// load any data returned due to errors
$this->obj_form->load_data_error();
}
}
function execute()
{
log_debug("invoice_form_item", "Executing execute()");
// determine the mode
if ($this->itemid) {
$this->mode = "edit";
} else {
$this->mode = "add";
}
/*
Start Form
*/
$this->obj_form = new form_input();
$this->obj_form->formname = $this->type . "_invoice_" . $this->mode;
$this->obj_form->language = $_SESSION["user"]["lang"];
$this->obj_form->action = $this->processpage;
$this->obj_form->method = "POST";
/*
Fetch customer ID
*/
if ($this->type == "ap") {
// fetch the vendorid for this invoice
$orgid = sql_get_singlevalue("SELECT vendorid as value FROM account_" . $this->type . " WHERE id='" . $this->invoiceid . "' LIMIT 1");
} else {
// fetch the customer ID for this invoice
$orgid = sql_get_singlevalue("SELECT customerid as value FROM account_" . $this->type . " WHERE id='" . $this->invoiceid . "' LIMIT 1");
}
/*
Define form structure, depending on the type of the item
*/
switch ($this->item_type) {
case "standard":
/*
STANDARD
simple transaction item which allows the user to specifiy a value only.
*/
// basic details
$structure = NULL;
$structure["fieldname"] = "amount";
$structure["type"] = "money";
$this->obj_form->add_input($structure);
$structure = NULL;
if ($this->type == "ap") {
$structure = charts_form_prepare_acccountdropdown("chartid", "ap_expense");
} else {
$structure = charts_form_prepare_acccountdropdown("chartid", "ar_income");
}
$structure["options"]["search_filter"] = "enabled";
$structure["options"]["width"] = "500";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "description";
$structure["type"] = "textarea";
$structure["options"]["height"] = "50";
$structure["options"]["width"] = 500;
$this->obj_form->add_input($structure);
// define form layout
$this->obj_form->subforms[$this->type . "_invoice_item"] = array("amount", "chartid", "description");
// SQL query
if ($this->itemid) {
$this->obj_form->sql_query = "SELECT amount, description, chartid FROM account_items WHERE id='" . $this->itemid . "'";
}
/*
List all the taxes, so that the user can select the tax(es) that apply for the transaction.
*/
$sql_tax_obj = new sql_query();
$sql_tax_obj->string = "SELECT id, name_tax, description FROM account_taxes ORDER BY name_tax";
$sql_tax_obj->execute();
if ($sql_tax_obj->num_rows()) {
// user note
$structure = NULL;
$structure["fieldname"] = "tax_message";
$structure["type"] = "message";
$structure["defaultvalue"] = "<p>Check all taxes that apply to this transaction below. If you want more advanced tax control (eg: fixed amounts of tax) then define a product and add it to the invoice.</p>";
$this->obj_form->add_input($structure);
$this->obj_form->subforms[$this->type . "_invoice_item_tax"][] = "tax_message";
// fetch customer/vendor tax defaults
if ($this->type == "ap") {
$defaulttax = sql_get_singlevalue("SELECT tax_default as value FROM vendors WHERE id='" . $orgid . "'");
} else {
$defaulttax = sql_get_singlevalue("SELECT tax_default as value FROM customers WHERE id='" . $orgid . "'");
}
// run through all the taxes
$sql_tax_obj->fetch_array();
foreach ($sql_tax_obj->data as $data_tax) {
// define tax checkbox
$structure = NULL;
$structure["fieldname"] = "tax_" . $data_tax["id"];
$structure["type"] = "checkbox";
$structure["options"]["label"] = $data_tax["name_tax"] . " -- " . $data_tax["description"];
$structure["options"]["no_fieldname"] = "enable";
if ($this->itemid) {
// see if this tax is currently inuse for the item
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id FROM account_items_options WHERE itemid='" . $this->itemid . "' AND option_name='TAX_CHECKED' AND option_value='" . $data_tax["id"] . "'";
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$structure["defaultvalue"] = "on";
}
} else {
// is this tax a customer/vendor default? If so, it should be checked automatically.
if ($data_tax["id"] == $defaulttax) {
$structure["defaultvalue"] = "on";
}
}
// add to form
$this->obj_form->add_input($structure);
$this->obj_form->subforms[$this->type . "_invoice_item_tax"][] = "tax_" . $data_tax["id"];
}
}
break;
/*
PRODUCT
Product item - selection of a product from the DB, and specify quantity, unit and amount.
*/
/*
PRODUCT
Product item - selection of a product from the DB, and specify quantity, unit and amount.
*/
case "product":
// basic details
$structure = NULL;
$structure["fieldname"] = "price";
$structure["type"] = "money";
$this->obj_form->add_input($structure);
// quantity
$structure = NULL;
$structure["fieldname"] = "quantity";
$structure["type"] = "input";
$structure["options"]["width"] = 50;
$this->obj_form->add_input($structure);
// units
$structure = NULL;
$structure["fieldname"] = "units";
$structure["type"] = "input";
$structure["options"]["width"] = 50;
$structure["options"]["max_length"] = 10;
$this->obj_form->add_input($structure);
// product id
$sql_struct_obj = new sql_query();
$sql_struct_obj->prepare_sql_settable("products");
$sql_struct_obj->prepare_sql_addfield("id", "products.id");
$sql_struct_obj->prepare_sql_addfield("label", "products.code_product");
$sql_struct_obj->prepare_sql_addfield("label1", "products.name_product");
$sql_struct_obj->prepare_sql_addorderby("code_product");
$sql_struct_obj->prepare_sql_addwhere("id = 'CURRENTID' OR date_end = '0000-00-00'");
$structure = form_helper_prepare_dropdownfromobj("productid", $sql_struct_obj);
$structure["options"]["search_filter"] = "enabled";
$structure["options"]["width"] = "600";
$this->obj_form->add_input($structure);
// description
$structure = NULL;
$structure["fieldname"] = "description";
$structure["type"] = "textarea";
$structure["options"]["height"] = "50";
$structure["options"]["width"] = 500;
$this->obj_form->add_input($structure);
// discount
$structure = NULL;
$structure["fieldname"] = "discount";
$structure["type"] = "input";
$structure["options"]["width"] = 50;
$structure["options"]["label"] = " %";
$structure["options"]["max_length"] = "2";
$this->obj_form->add_input($structure);
// define form layout
$this->obj_form->subforms[$this->type . "_invoice_item"] = array("productid", "price", "quantity", "units", "description", "discount");
// fetch data
//
// if the item is new, use the this->item field to fetch the default product details, otherwise
// fetch the details for the existing item
//
if ($this->itemid) {
$this->obj_form->sql_query = "SELECT price, description, customid as productid, quantity, units FROM account_items WHERE id='" . $this->itemid . "'";
} else {
if ($this->type == "ar" || $this->type == "quotes") {
$this->obj_form->sql_query = "SELECT id as productid, price_sale as price, units, details as description FROM products WHERE id='" . $this->productid . "'";
} else {
$this->obj_form->sql_query = "SELECT id as productid, price_cost as price, units, details as description FROM products WHERE id='" . $this->productid . "'";
}
$this->obj_form->structure["quantity"]["defaultvalue"] = 1;
}
// fetch discount (if any) from customer/vendor
if ($this->type == "ap") {
$discount_org = sql_get_singlevalue("SELECT discount as value FROM vendors WHERE id='" . $orgid . "' LIMIT 1");
} else {
$discount_org = sql_get_singlevalue("SELECT discount as value FROM customers WHERE id='" . $orgid . "' LIMIT 1");
}
// fetch discount (if any) from product
$discount_product = sql_get_singlevalue("SELECT discount as value FROM products WHERE id='" . $this->productid . "' LIMIT 1");
// choose the largest discount
if ($discount_org || $discount_product) {
if ($discount_org > $discount_product) {
$this->obj_form->structure["discount"]["defaultvalue"] = $discount_org;
} else {
$this->obj_form->structure["discount"]["defaultvalue"] = $discount_product;
}
}
break;
/*
TIME (AR only)
Before time can be added to an invoice, the time entries need to be grouped together
using the form under projects.
The user can then select a group of time below to add to the invoice. This methods makes
it easier to add time to invoices, and also means that the time grouping could be done
by someone without access to invoicing itself.
*/
/*
TIME (AR only)
Before time can be added to an invoice, the time entries need to be grouped together
using the form under projects.
The user can then select a group of time below to add to the invoice. This methods makes
it easier to add time to invoices, and also means that the time grouping could be done
by someone without access to invoicing itself.
*/
case "time":
if ($this->type == "ar") {
// list of avaliable time groups
$structure = form_helper_prepare_dropdownfromdb("timegroupid", "SELECT time_groups.id, projects.name_project as label, time_groups.name_group as label1 FROM time_groups LEFT JOIN projects ON projects.id = time_groups.projectid WHERE customerid='{$orgid}' AND (invoiceitemid='0' OR invoiceitemid='" . $this->itemid . "') ORDER BY name_group");
$structure["options"]["width"] = "600";
$structure["options"]["autoselect"] = "yes";
$structure["options"]["search_filter"] = "enabled";
$this->obj_form->add_input($structure);
// price field
// TODO: this should auto-update from the product price
$structure = NULL;
$structure["fieldname"] = "price";
$structure["type"] = "money";
$this->obj_form->add_input($structure);
// product id
$sql_struct_obj = new sql_query();
$sql_struct_obj->prepare_sql_settable("products");
$sql_struct_obj->prepare_sql_addfield("id", "products.id");
$sql_struct_obj->prepare_sql_addfield("label", "products.code_product");
$sql_struct_obj->prepare_sql_addfield("label1", "products.name_product");
$sql_struct_obj->prepare_sql_addorderby("code_product");
$sql_struct_obj->prepare_sql_addwhere("id = 'CURRENTID' OR date_end = '0000-00-00'");
$structure = form_helper_prepare_dropdownfromobj("productid", $sql_struct_obj);
$structure["options"]["width"] = "600";
$structure["options"]["search_filter"] = "enabled";
$this->obj_form->add_input($structure);
// description
$structure = NULL;
$structure["fieldname"] = "description";
$structure["type"] = "textarea";
$structure["options"]["height"] = "50";
$structure["options"]["width"] = 500;
$this->obj_form->add_input($structure);
// discount
$structure = NULL;
$structure["fieldname"] = "discount";
$structure["type"] = "input";
$structure["options"]["width"] = 50;
$structure["options"]["label"] = " %";
$structure["options"]["max_length"] = "2";
$this->obj_form->add_input($structure);
// define form layout
$this->obj_form->subforms[$this->type . "_invoice_item"] = array("timegroupid", "productid", "price", "description", "discount");
// SQL query
$this->obj_form->sql_query = "SELECT price, description, customid as productid, quantity, units FROM account_items WHERE id='" . $this->itemid . "'";
// fetch discount (if any) from customer/vendor
if ($this->type == "ap") {
$discount_org = sql_get_singlevalue("SELECT discount as value FROM vendors WHERE id='" . $orgid . "' LIMIT 1");
} else {
$discount_org = sql_get_singlevalue("SELECT discount as value FROM customers WHERE id='" . $orgid . "' LIMIT 1");
}
// TODO: need to look at improving time <-> product relationships
// fetch discount (if any) from product
// $discount_product = sql_get_singlevalue("SELECT discount FROM products WHERE id='". $this->productid ."' LIMIT 1");
// choose the largest discount
if ($discount_org || $discount_product) {
if ($discount_org > $discount_product) {
$this->obj_form->structure["discount"]["defaultvalue"] = $discount_org;
} else {
$this->obj_form->structure["discount"]["defaultvalue"] = $discount_product;
}
}
} else {
log_write("error", "inc_invoice_items", "Time items are only avaliable for AR invoices, please report the steps to access this page as an application bug.");
}
break;
/*
SERVICE (AR only)
Service items can only be added via the automated invoicing capabilities, however we do
allow users to adjust the description, price and discount once an item has been created.
*/
/*
SERVICE (AR only)
Service items can only be added via the automated invoicing capabilities, however we do
allow users to adjust the description, price and discount once an item has been created.
*/
case "service":
case "service_usage":
if ($this->type == "ar") {
// service group name
$structure = NULL;
$structure["fieldname"] = "id_service";
$structure["type"] = "text";
$this->obj_form->add_input($structure);
// price field
$structure = NULL;
$structure["fieldname"] = "price";
$structure["type"] = "money";
$this->obj_form->add_input($structure);
// quantity
$structure = NULL;
$structure["fieldname"] = "quantity";
$structure["type"] = "input";
$structure["options"]["width"] = 50;
$this->obj_form->add_input($structure);
// description
$structure = NULL;
$structure["fieldname"] = "description";
$structure["type"] = "textarea";
$structure["options"]["height"] = "50";
$structure["options"]["width"] = 500;
$this->obj_form->add_input($structure);
// discount
$structure = NULL;
$structure["fieldname"] = "discount";
$structure["type"] = "input";
$structure["options"]["width"] = 50;
$structure["options"]["label"] = " %";
$structure["options"]["max_length"] = "2";
$this->obj_form->add_input($structure);
// define form layout
$this->obj_form->subforms[$this->type . "_invoice_item"] = array("id_service", "price", "quantity", "description", "discount");
// SQL query
$this->obj_form->sql_query = "SELECT price, description, customid as id_service, quantity, units FROM account_items WHERE id='" . $this->itemid . "'";
/*
// fetch discount (if any) from customer/vendor
$discount_org = sql_get_singlevalue("SELECT discount as value FROM customers WHERE id='". $orgid ."' LIMIT 1");
// fetch discount (if any) from
// $discount_product = sql_get_singlevalue("SELECT discount FROM products WHERE id='". $this->productid ."' LIMIT 1");
// choose the largest discount
if ($discount_org || $discount_product)
{
if ($discount_org > $discount_product)
{
$this->obj_form->structure["discount"]["defaultvalue"] = $discount_org;
}
else
{
$this->obj_form->structure["discount"]["defaultvalue"] = $discount_product;
}
}
*/
} else {
log_write("error", "inc_invoice_items", "Service items are only avaliable for AR invoices.");
}
break;
case "payment":
/*
PAYMENT
Payments against invoices are also items which credit/subtract funds from a selected account. Note that payments
typically come out of an asset account, but if the customer/vendor has credit and the payment is made from
that credit, the account will be the AR/AP account
*/
$structure = NULL;
$structure["fieldname"] = "date_trans";
$structure["type"] = "date";
$structure["defaultvalue"] = date("Y-m-d");
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "amount";
$structure["type"] = "money";
$this->obj_form->add_input($structure);
$structure = NULL;
if ($this->type == "ap") {
$structure = charts_form_prepare_acccountdropdown("chartid", "ap_payment");
} else {
$structure = charts_form_prepare_acccountdropdown("chartid", "ar_payment");
}
$structure["options"]["search_filter"] = "enabled";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "source";
$structure["type"] = "input";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "description";
$structure["type"] = "textarea";
$structure["options"]["height"] = "50";
$structure["options"]["width"] = 500;
$this->obj_form->add_input($structure);
// define form layout
$this->obj_form->subforms[$this->type . "_invoice_item"] = array("date_trans", "amount", "chartid", "source", "description");
// load data
if ($this->itemid) {
// credit details (if applicable)
$credit = sql_get_singlevalue("SELECT option_value AS value FROM account_items_options WHERE itemid='" . $this->itemid . "' AND option_name='CREDIT' LIMIT 1");
// standard payment item data
if ($credit) {
$this->obj_form->sql_query = "SELECT amount as amount, description, 'credit' as chartid FROM account_items WHERE id='" . $this->itemid . "'";
} else {
$this->obj_form->sql_query = "SELECT amount as amount, description, chartid FROM account_items WHERE id='" . $this->itemid . "'";
}
} else {
// set defaults
$this->obj_form->structure["amount"]["defaultvalue"] = sql_get_singlevalue("SELECT SUM(amount_total - amount_paid) as value FROM account_" . $this->type . " WHERE id='" . $this->invoiceid . "' LIMIT 1");
/*
Fetch credit information (if any)
We handle credit information, by determining the maximum available credit and then
overwriting values on the item's information including:
- amount (equal to invoice or to max credit amout if less than invoice max)
- account (set to AR/AP)
- date (today's date)
*/
if ($this->type == "ap") {
$credit = sql_get_singlevalue("SELECT SUM(amount_total) as value FROM vendors_credits WHERE id_vendor='" . $orgid . "'");
} else {
$credit = sql_get_singlevalue("SELECT SUM(amount_total) as value FROM customers_credits WHERE id_customer='" . $orgid . "'");
}
if ($credit > 0) {
// customer/vendor has credit
if ($credit > $this->obj_form->structure["amount"]["defaultvalue"]) {
// credit is more than the invoice amount, set to mac
$credit = $this->obj_form->structure["amount"]["defaultvalue"];
}
// set default value
$this->obj_form->structure["amount"]["defaultvalue"] = $credit;
// set source
$this->obj_form->structure["source"]["defaultvalue"] = "CREDITED FUNDS";
}
}
// overwrite account settings for credits
if ($credit > 0 || $credit == "CREDIT") {
if ($this->type == "ap") {
$this->obj_form->structure["chartid"]["values"][] = "credit";
$this->obj_form->structure["chartid"]["translations"]["credit"] = "Vendor Credit";
} else {
$this->obj_form->structure["chartid"]["values"][] = "credit";
$this->obj_form->structure["chartid"]["translations"]["credit"] = "Customer Credit";
}
$this->obj_form->structure["chartid"]["defaultvalue"] = "credit";
}
break;
case "credit":
/*
Credit
ar_credit or ap_credit only
This item type only applies to credit notes and acts simular to a standard item but inherits pricing
account and tax information from the original item. (inheritance done on item edit page).
*/
// basic details
$structure = NULL;
$structure["fieldname"] = "amount";
$structure["type"] = "money";
$structure["options"]["prelabel"] = "CREDIT ";
$this->obj_form->add_input($structure);
$structure = NULL;
if ($this->type == "ap") {
$structure = charts_form_prepare_acccountdropdown("chartid", "ap_expense");
} else {
$structure = charts_form_prepare_acccountdropdown("chartid", "ar_income");
}
$structure["options"]["search_filter"] = "enabled";
$structure["options"]["width"] = "500";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "description";
$structure["type"] = "textarea";
$structure["options"]["height"] = "50";
$structure["options"]["width"] = 500;
$this->obj_form->add_input($structure);
// define form layout
$this->obj_form->subforms[$this->type . "_invoice_item"] = array("amount", "chartid", "description");
// SQL query
if ($this->itemid) {
$this->obj_form->sql_query = "SELECT amount, description, chartid FROM account_items WHERE id='" . $this->itemid . "'";
}
/*
List all the taxes, so that the user can select the tax(es) that apply for the transaction.
*/
$sql_tax_obj = new sql_query();
$sql_tax_obj->string = "SELECT id, name_tax, description FROM account_taxes ORDER BY name_tax";
$sql_tax_obj->execute();
if ($sql_tax_obj->num_rows()) {
// user note
$structure = NULL;
$structure["fieldname"] = "tax_message";
$structure["type"] = "message";
$structure["defaultvalue"] = "<p>Taxes have automatically been determed based on the options of the selected invoice item.</p>";
$this->obj_form->add_input($structure);
$this->obj_form->subforms[$this->type . "_invoice_item_tax"][] = "tax_message";
// fetch customer/vendor tax defaults
if ($this->type == "ap") {
$defaulttax = sql_get_singlevalue("SELECT tax_default as value FROM vendors WHERE id='" . $orgid . "'");
} else {
$defaulttax = sql_get_singlevalue("SELECT tax_default as value FROM customers WHERE id='" . $orgid . "'");
}
// run through all the taxes
$sql_tax_obj->fetch_array();
foreach ($sql_tax_obj->data as $data_tax) {
// define tax checkbox
$structure = NULL;
$structure["fieldname"] = "tax_" . $data_tax["id"];
$structure["type"] = "checkbox";
$structure["options"]["label"] = $data_tax["name_tax"] . " -- " . $data_tax["description"];
$structure["options"]["no_fieldname"] = "enable";
if ($this->itemid) {
// see if this tax is currently inuse for the item
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id FROM account_items_options WHERE itemid='" . $this->itemid . "' AND option_name='TAX_CHECKED' AND option_value='" . $data_tax["id"] . "'";
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$structure["defaultvalue"] = "on";
}
}
// add to form
$this->obj_form->add_input($structure);
$this->obj_form->subforms[$this->type . "_invoice_item_tax"][] = "tax_" . $data_tax["id"];
}
}
break;
default:
log_write("error", "inc_invoice_items", "Unknown type passed to render form.");
break;
}
// IDs
$structure = NULL;
$structure["fieldname"] = "id_invoice";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $this->invoiceid;
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "id_item";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $this->itemid;
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "item_type";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $this->item_type;
$this->obj_form->add_input($structure);
// submit
$structure = NULL;
$structure["fieldname"] = "submit";
$structure["type"] = "submit";
$structure["defaultvalue"] = "Save Changes";
$this->obj_form->add_input($structure);
// load data
$this->obj_form->load_data();
// custom loads for different item type
if ($this->itemid) {
switch ($this->item_type) {
case "time":
// fetch the time group ID
$this->obj_form->structure["timegroupid"]["defaultvalue"] = sql_get_singlevalue("SELECT option_value AS value FROM account_items_options WHERE itemid='" . $this->itemid . "' AND option_name='TIMEGROUPID' LIMIT 1");
// fetch discount (if any) from item
$this->obj_form->structure["discount"]["defaultvalue"] = sql_get_singlevalue("SELECT option_value as value FROM account_items_options WHERE itemid='" . $this->itemid . "' AND option_name='DISCOUNT'");
break;
case "payment":
// fetch payment date_trans and source fields.
$this->obj_form->structure["date_trans"]["defaultvalue"] = sql_get_singlevalue("SELECT option_value AS value FROM account_items_options WHERE itemid='" . $this->itemid . "' AND option_name='DATE_TRANS' LIMIT 1");
$this->obj_form->structure["source"]["defaultvalue"] = sql_get_singlevalue("SELECT option_value AS value FROM account_items_options WHERE itemid='" . $this->itemid . "' AND option_name='SOURCE' LIMIT 1");
break;
case "product":
// fetch discount (if any) from item
$this->obj_form->structure["discount"]["defaultvalue"] = sql_get_singlevalue("SELECT option_value as value FROM account_items_options WHERE itemid='" . $this->itemid . "' AND option_name='DISCOUNT'");
break;
case "service":
case "service_usage":
// fetch discount (if any) from item
$this->obj_form->structure["discount"]["defaultvalue"] = sql_get_singlevalue("SELECT option_value as value FROM account_items_options WHERE itemid='" . $this->itemid . "' AND option_name='DISCOUNT'");
break;
}
}
/*
Display Form
*/
$this->obj_form->subforms["hidden"] = array("id_invoice", "id_item", "item_type");
if ($this->item_type == "time" && count($this->obj_form->structure["timegroupid"]["values"]) == 0) {
$this->obj_form->subforms["submit"] = array();
} else {
$this->obj_form->subforms["submit"] = array("submit");
}
}
function execute()
{
/*
Define form structure
*/
$this->obj_form = new form_input();
$this->obj_form->formname = "credit_refund";
$this->obj_form->language = $_SESSION["user"]["lang"];
$this->obj_form->action = "customers/credit-refund-edit-process.php";
$this->obj_form->method = "post";
// basic details
$structure = NULL;
$structure["fieldname"] = "date_trans";
$structure["type"] = "date";
$structure["defaultvalue"] = date("Y-m-d");
$structure["options"]["req"] = "yes";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "type";
$structure["type"] = "text";
$structure["defaultvalue"] = "refund";
$this->obj_form->add_input($structure);
$sql_struct_obj = new sql_query();
$sql_struct_obj->prepare_sql_settable("staff");
$sql_struct_obj->prepare_sql_addfield("id", "staff.id");
$sql_struct_obj->prepare_sql_addfield("label", "staff.staff_code");
$sql_struct_obj->prepare_sql_addfield("label1", "staff.name_staff");
$sql_struct_obj->prepare_sql_addorderby("staff_code");
$sql_struct_obj->prepare_sql_addwhere("id = 'CURRENTID' OR date_end = '0000-00-00'");
$structure = form_helper_prepare_dropdownfromobj("id_employee", $sql_struct_obj);
$structure["options"]["req"] = "yes";
$structure["options"]["autoselect"] = "yes";
$structure["options"]["width"] = "600";
$structure["options"]["search_filter"] = "enabled";
$structure["defaultvalue"] = @$_SESSION["user"]["default_employeeid"];
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "description";
$structure["type"] = "textarea";
$structure["defaultvalue"] = "";
$structure["options"]["req"] = "yes";
$structure["options"]["width"] = "600";
$this->obj_form->add_input($structure);
// amount
$structure = NULL;
$structure["fieldname"] = "amount";
$structure["type"] = "money";
$structure["options"]["req"] = "yes";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure = charts_form_prepare_acccountdropdown("account_asset", "ap_payment");
$structure["options"]["search_filter"] = "enabled";
$structure["options"]["autoselect"] = "enabled";
$structure["options"]["width"] = "600";
$structure["options"]["req"] = "yes";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure = charts_form_prepare_acccountdropdown("account_dest", "ar_summary_account");
$structure["options"]["search_filter"] = "enabled";
$structure["options"]["autoselect"] = "enabled";
$structure["options"]["width"] = "600";
$structure["options"]["req"] = "yes";
$this->obj_form->add_input($structure);
// hidden values
$structure = NULL;
$structure["fieldname"] = "id_customer";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $this->obj_customer->id;
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "id_refund";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $this->obj_refund->id;
$this->obj_form->add_input($structure);
// submit button
$structure = NULL;
$structure["fieldname"] = "submit";
$structure["type"] = "submit";
$structure["defaultvalue"] = "Save Changes";
$this->obj_form->add_input($structure);
// define base subforms
$this->obj_form->subforms["credit_refund_details"] = array("date_trans", "type", "id_employee", "description");
$this->obj_form->subforms["credit_refund_amount"] = array("amount", "account_asset", "account_dest");
$this->obj_form->subforms["hidden"] = array("id_customer", "id_refund");
$this->obj_form->subforms["submit"] = array("submit");
// fetch the form data if editing
if ($this->obj_refund->id) {
// load existing information
$this->obj_refund->load_data();
$this->obj_form->structure["date_trans"]["defaultvalue"] = $this->obj_refund->data["date_trans"];
$this->obj_form->structure["amount"]["defaultvalue"] = $this->obj_refund->data["amount_total"];
$this->obj_form->structure["id_employee"]["defaultvalue"] = $this->obj_refund->data["id_employee"];
$this->obj_form->structure["description"]["defaultvalue"] = $this->obj_refund->data["description"];
} else {
// set defaults
$this->obj_form->structure["date_trans"]["defaultvalue"] = date("Y-m-d");
$this->obj_form->structure["amount"]["defaultvalue"] = sql_get_singlevalue("SELECT SUM(amount_total) as value FROM customers_credits WHERE id_customer='" . $this->obj_customer->id . "' AND id!='" . $this->obj_refund->id . "'");
}
if (error_check()) {
// load any data returned due to errors
$this->obj_form->load_data_error();
}
}
function execute()
{
log_debug("products_form_details", "Executing execute()");
/*
Define form structure
*/
$this->obj_form = new form_input();
$this->obj_form->formname = "product_" . $this->mode;
$this->obj_form->language = $_SESSION["user"]["lang"];
$this->obj_form->action = "products/edit-process.php";
$this->obj_form->method = "post";
// general
$structure = NULL;
$structure["fieldname"] = "code_product";
$structure["type"] = "input";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "name_product";
$structure["type"] = "input";
$structure["options"]["req"] = "yes";
$this->obj_form->add_input($structure);
$structure = form_helper_prepare_dropdownfromgroup("id_product_group", "SELECT id as value_id, group_name as value_key, id_parent as value_parent FROM product_groups");
$structure["options"]["req"] = "yes";
$structure["options"]["search_filter"] = "yes";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "units";
$structure["type"] = "input";
$structure["options"]["width"] = 50;
$structure["options"]["max_length"] = 10;
$structure["options"]["req"] = "yes";
$this->obj_form->add_input($structure);
$structure = charts_form_prepare_acccountdropdown("account_sales", "ar_income");
$structure["options"]["req"] = "yes";
$structure["options"]["search_filter"] = "yes";
$this->obj_form->add_input($structure);
$structure = charts_form_prepare_acccountdropdown("account_purchase", "ap_expense");
$structure["options"]["req"] = "yes";
$structure["options"]["search_filter"] = "yes";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "details";
$structure["type"] = "textarea";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "date_start";
$structure["type"] = "date";
$structure["options"]["req"] = "yes";
$structure["defaultvalue"] = date("Y-m-d");
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "date_end";
$structure["type"] = "date";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "date_current";
$structure["type"] = "date";
$this->obj_form->add_input($structure);
$this->obj_form->subforms["product_view"] = array("code_product", "name_product", "id_product_group", "units", "account_sales", "account_purchase", "date_start", "date_end", "date_current", "details");
// pricing
$structure = NULL;
$structure["fieldname"] = "price_cost";
$structure["type"] = "money";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "price_sale";
$structure["type"] = "money";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "discount";
$structure["type"] = "input";
$structure["options"]["width"] = 50;
$structure["options"]["label"] = " %";
$structure["options"]["max_length"] = "6";
$this->obj_form->add_input($structure);
$this->obj_form->subforms["product_pricing"] = array("price_cost", "price_sale", "discount");
/*
List all the taxes, so that the user can select the tax(es) that apply to the product
*/
$sql_tax_obj = new sql_query();
$sql_tax_obj->string = "SELECT id, name_tax, description, default_products FROM account_taxes ORDER BY name_tax";
$sql_tax_obj->execute();
if ($sql_tax_obj->num_rows()) {
// user note
$structure = NULL;
$structure["fieldname"] = "tax_message";
$structure["type"] = "message";
$structure["defaultvalue"] = "<p>Check all taxes that apply to this product below.</p>";
$this->obj_form->add_input($structure);
$this->obj_form->subforms["product_tax"][] = "tax_message";
// run through all the taxes
$sql_tax_obj->fetch_array();
foreach ($sql_tax_obj->data as $data_tax) {
// define tax checkbox
$structure = NULL;
$structure["fieldname"] = "tax_" . $data_tax["id"];
$structure["type"] = "checkbox";
$structure["options"]["label"] = $data_tax["name_tax"] . " -- " . $data_tax["description"];
$structure["options"]["no_fieldname"] = "enable";
// see if this tax is currently enabled for this product
if ($this->productid) {
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id FROM products_taxes WHERE productid='" . $this->productid . "' AND taxid='" . $data_tax["id"] . "' LIMIT 1";
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$structure["defaultvalue"] = "on";
}
} else {
if ($data_tax["default_products"]) {
$structure["defaultvalue"] = "on";
}
}
// add to form
$this->obj_form->add_input($structure);
$this->obj_form->subforms["product_tax"][] = "tax_" . $data_tax["id"];
}
}
// quantity
$structure = NULL;
$structure["fieldname"] = "quantity_instock";
$structure["type"] = "input";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "quantity_vendor";
$structure["type"] = "input";
$this->obj_form->add_input($structure);
$this->obj_form->subforms["product_quantity"] = array("quantity_instock", "quantity_vendor");
// supplier details
$sql_struct_obj = new sql_query();
$sql_struct_obj->prepare_sql_settable("vendors");
$sql_struct_obj->prepare_sql_addfield("id", "vendors.id");
$sql_struct_obj->prepare_sql_addfield("label", "vendors.name_vendor");
$sql_struct_obj->prepare_sql_addwhere("id = 'CURRENTID' OR date_end = '0000-00-00'");
$structure = form_helper_prepare_dropdownfromobj("vendorid", $sql_struct_obj);
$structure["options"]["search_filter"] = "yes";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "code_product_vendor";
$structure["type"] = "input";
$this->obj_form->add_input($structure);
$this->obj_form->subforms["product_supplier"] = array("vendorid", "code_product_vendor");
// define remaining subforms
if (user_permissions_get("products_write")) {
$this->obj_form->subforms["submit"] = array("submit");
} else {
$this->obj_form->subforms["submit"] = array();
}
/*
Mode dependent options
*/
if ($this->mode == "add") {
// submit button
$structure = NULL;
$structure["fieldname"] = "submit";
$structure["type"] = "submit";
$structure["defaultvalue"] = "Create Product";
$this->obj_form->add_input($structure);
} else {
// submit button
$structure = NULL;
$structure["fieldname"] = "submit";
$structure["type"] = "submit";
$structure["defaultvalue"] = "Save Changes";
$this->obj_form->add_input($structure);
// hidden data
$structure = NULL;
$structure["fieldname"] = "id_product";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $this->productid;
$this->obj_form->add_input($structure);
$this->obj_form->subforms["hidden"] = array("id_product");
}
/*
Load Data
*/
if ($this->mode == "add") {
$this->obj_form->load_data_error();
} else {
$this->obj_form->sql_query = "SELECT * FROM `products` WHERE id='" . $this->productid . "' LIMIT 1";
$this->obj_form->load_data();
}
}
function execute()
{
/*
Check if time entry can be adjusted
*/
if ($this->id) {
$sql_obj = new sql_query();
$sql_obj->string = "SELECT locked, groupid FROM `timereg` WHERE id='" . $this->id . "' LIMIT 1";
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
$this->locked = $sql_obj->data[0]["locked"];
// so we can tell if the time is locked
$this->groupid = $sql_obj->data[0]["groupid"];
// tells us what group id the time belongs to
}
unset($sql_obj);
}
/*
Input Form
Allows the creation of a new entry for the day, or the adjustment of an existing one.
*/
$this->obj_form = new form_input();
$this->obj_form->formname = "timereg_day";
$this->obj_form->language = $_SESSION["user"]["lang"];
$this->obj_form->action = "timekeeping/timereg-day-edit-process.php";
$this->obj_form->method = "post";
// hidden stuff
$structure = NULL;
$structure["fieldname"] = "id_timereg";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $this->id;
$this->obj_form->add_input($structure);
// employee selection box
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("staff");
$sql_obj->prepare_sql_addfield("id", "id");
$sql_obj->prepare_sql_addfield("label", "staff_code");
$sql_obj->prepare_sql_addfield("label1", "name_staff");
if ($this->access_staff_ids_write) {
$sql_obj->prepare_sql_addwhere("id IN (" . format_arraytocommastring($this->access_staff_ids_write) . ")");
}
$sql_obj->generate_sql();
$structure = form_helper_prepare_dropdownfromdb("employeeid", $sql_obj->string);
// if there is currently no employee set, and there is only one
// employee in the selection box, automatically select it and update
// the session variables.
if (!$this->employeeid && count($structure["values"]) == 1) {
$this->employeeid = $structure["values"][0];
$_SESSION["form"]["timereg"]["employeeid"] = $structure["values"][0];
}
$structure["options"]["autoselect"] = "on";
$structure["options"]["width"] = "600";
$structure["options"]["search_filter"] = "yes";
$structure["defaultvalue"] = $this->employeeid;
$this->obj_form->add_input($structure);
// general
$structure = NULL;
$structure["fieldname"] = "date";
$structure["type"] = "date";
$structure["defaultvalue"] = $this->date;
$structure["options"]["req"] = "yes";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "time_booked";
$structure["type"] = "hourmins";
$structure["options"]["req"] = "yes";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "description";
$structure["type"] = "textarea";
$structure["options"]["req"] = "yes";
$structure["options"]["width"] = "600";
$structure["options"]["height"] = "60";
$this->obj_form->add_input($structure);
//project dropdown
$sql_struct_obj = new sql_query();
$sql_struct_obj->prepare_sql_settable("projects");
$sql_struct_obj->prepare_sql_addfield("id", "projects.id");
$sql_struct_obj->prepare_sql_addfield("label", "projects.code_project");
$sql_struct_obj->prepare_sql_addfield("label1", "projects.name_project");
$sql_struct_obj->prepare_sql_addorderby("code_project");
$sql_struct_obj->prepare_sql_addwhere("id = 'CURRENTID' OR date_end = '0000-00-00'");
$structure = form_helper_prepare_dropdownfromobj("projectid", $sql_struct_obj);
$structure["options"]["autoselect"] = "on";
$structure["options"]["width"] = "600";
$structure["options"]["search_filter"] = "yes";
if (count($structure["values"]) == 0) {
$structure["defaultvalue"] = "You need to create a project and add a phase to it in order to be able to book time.";
$_SESSION["error"]["phaseid-error"] = 1;
}
$this->obj_form->add_input($structure);
//phase dropdown
$structure = NULL;
$structure["fieldname"] = "phaseid";
$structure["type"] = "dropdown";
$structure["values"] = array("");
$structure["options"]["width"] = "600";
$structure["options"]["disabled"] = "yes";
$structure["options"]["search_filter"] = "yes";
$this->obj_form->add_input($structure);
//add project field
$structure = NULL;
$structure["fieldname"] = "add_project";
$structure["type"] = "input";
$structure["options"]["no_fieldname"] = "yes";
$structure["options"]["no_shift"] = "yes";
$structure["options"]["prelabel"] = "<div id=\"add_project_box\"><span id=\"toggle_add_project\">\n\t\t\t\t\t\t\t\t<strong>Add New Project</strong>\n\t\t\t\t\t\t\t\t<div class=\"half_sized_break_line\"><br/></div>\n\t\t\t\t\t\t\t\tNew Project: ";
$structure["options"]["label"] = " <a class=\"insert_project_phase button_small\" id=\"insert_project\" href=\"\">Add</a>\n\t\t\t\t\t\t\t\t</span><div class=\"half_sized_break_line\"><br/></div>\n\t\t\t\t\t\t\t\t<strong><a id=\"project_add_cancel\" class=\"add_link\" href=\"\">Add New Project</a></strong></div>";
$this->obj_form->add_input($structure);
//add phase field
$structure = NULL;
$structure["fieldname"] = "add_phase";
$structure["type"] = "input";
$structure["options"]["no_fieldname"] = "yes";
$structure["options"]["no_shift"] = "yes";
$structure["options"]["prelabel"] = "<div id=\"add_phase_box\"><span id=\"toggle_add_phase\">\n\t\t\t\t\t\t\t\t<strong>Add Phase to Current Project</strong>\n\t\t\t\t\t\t\t\t<div class=\"half_sized_break_line\"><br/></div>\n\t\t\t\t\t\t\t\tNew Phase: ";
$structure["options"]["label"] = " <a class=\"insert_project_phase button_small\" id=\"insert_phase\" href=\"\">Add</a>\n\t\t\t\t\t\t\t\t</span><div class=\"half_sized_break_line\"><br/></div>\n\t\t\t\t\t\t\t\t<strong><a id=\"phase_add_cancel\" class=\"add_link\" href=\"\">Add Phase to Current Project</a></strong></div>";
$this->obj_form->add_input($structure);
// submit section
$structure = NULL;
$structure["fieldname"] = "submit";
$structure["type"] = "submit";
$structure["defaultvalue"] = "Save Changes";
$this->obj_form->add_input($structure);
// define subforms
if (user_permissions_get("projects_write")) {
$this->obj_form->subforms["timereg_day"] = array("employeeid", "projectid", "phaseid", "add_project", "add_phase", "date", "time_booked", "description");
} else {
$this->obj_form->subforms["timereg_day"] = array("employeeid", "projectid", "phaseid", "date", "time_booked", "description");
}
$this->obj_form->subforms["hidden"] = array("id_timereg");
if ($this->locked) {
$this->obj_form->subforms["submit"] = array();
} else {
$this->obj_form->subforms["submit"] = array("submit");
}
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id FROM `timereg` WHERE id='" . $this->id . "' LIMIT 1";
$sql_obj->execute();
if ($sql_obj->num_rows()) {
// fetch the form data
$this->obj_form->sql_query = "SELECT * FROM `timereg` WHERE id='" . $this->id . "' LIMIT 1";
$this->obj_form->load_data();
$this->obj_form->structure['projectid']['defaultvalue'] = sql_get_singlevalue("SELECT project_phases.projectid AS value FROM timereg LEFT JOIN project_phases ON project_phases.id = timereg.phaseid WHERE timereg.id='" . $this->id . "'");
} else {
// load any data returned due to errors
$this->obj_form->load_data_error();
}
}
function execute()
{
// establish a new table object
$this->obj_table = new table();
$this->obj_table->language = $_SESSION["user"]["lang"];
$this->obj_table->tablename = "timereg_table";
// define all the columns and structure
$this->obj_table->add_column("date", "date", "timereg.date");
$this->obj_table->add_column("standard", "name_phase", "project_phases.name_phase");
$this->obj_table->add_column("standard", "name_staff", "CONCAT_WS(' -- ', staff.staff_code, staff.name_staff)");
$this->obj_table->add_column("standard", "time_group", "time_groups.name_group");
$this->obj_table->add_column("standard", "description", "timereg.description");
$this->obj_table->add_column("hourmins", "time_booked", "timereg.time_booked");
// defaults
$this->obj_table->columns = array("date", "name_phase", "name_staff", "time_group", "description", "time_booked");
$this->obj_table->columns_order = array("date", "name_phase");
$this->obj_table->columns_order_options = array("date", "name_phase", "name_staff", "time_group", "description", "time_booked");
// define SQL structure
$this->obj_table->sql_obj->prepare_sql_settable("timereg");
$this->obj_table->sql_obj->prepare_sql_addfield("id", "timereg.id");
$this->obj_table->sql_obj->prepare_sql_addfield("employeeid", "timereg.employeeid");
$this->obj_table->sql_obj->prepare_sql_addjoin("LEFT JOIN staff ON timereg.employeeid = staff.id");
$this->obj_table->sql_obj->prepare_sql_addjoin("LEFT JOIN time_groups ON timereg.groupid = time_groups.id");
$this->obj_table->sql_obj->prepare_sql_addjoin("LEFT JOIN project_phases ON timereg.phaseid = project_phases.id");
$this->obj_table->sql_obj->prepare_sql_addjoin("LEFT JOIN projects ON project_phases.projectid = projects.id");
$this->obj_table->sql_obj->prepare_sql_addwhere("projects.id = '" . $this->id . "'");
if ($this->access_staff_ids) {
$this->obj_table->sql_obj->prepare_sql_addwhere("timereg.employeeid IN (" . format_arraytocommastring($this->access_staff_ids) . ")");
}
/// Filtering/Display Options
// fixed options
$this->obj_table->add_fixed_option("id", $this->id);
// acceptable filter options
$structure = NULL;
$structure["fieldname"] = "date_start";
$structure["type"] = "date";
$structure["sql"] = "date >= 'value'";
$this->obj_table->add_filter($structure);
$structure = NULL;
$structure["fieldname"] = "date_end";
$structure["type"] = "date";
$structure["sql"] = "date <= 'value'";
$this->obj_table->add_filter($structure);
$structure = form_helper_prepare_dropdownfromdb("phaseid", "SELECT id, name_phase as label FROM project_phases WHERE projectid='" . $this->id . "' ORDER BY name_phase ASC");
$structure["sql"] = "project_phases.id='value'";
$structure["options"]["search_filter"] = "yes";
$this->obj_table->add_filter($structure);
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("staff");
$sql_obj->prepare_sql_addfield("id", "id");
$sql_obj->prepare_sql_addfield("label", "staff_code");
$sql_obj->prepare_sql_addfield("label1", "name_staff");
if ($this->access_staff_ids) {
$sql_obj->prepare_sql_addwhere("id IN (" . format_arraytocommastring($this->access_staff_ids) . ")");
}
$sql_obj->generate_sql();
$structure = form_helper_prepare_dropdownfromdb("employeeid", $sql_obj->string);
$structure["sql"] = "timereg.employeeid='value'";
$structure["options"]["search_filter"] = "yes";
$this->obj_table->add_filter($structure);
$structure = NULL;
$structure["fieldname"] = "no_group";
$structure["type"] = "checkbox";
$structure["sql"] = "groupid='0'";
$structure["options"]["label"] = "Only show unprocessed time";
$this->obj_table->add_filter($structure);
$structure = NULL;
$structure["fieldname"] = "searchbox";
$structure["type"] = "input";
$structure["sql"] = "(timereg.description LIKE '%value%' OR project_phases.name_phase LIKE '%value%' OR staff.name_staff LIKE '%value%')";
$this->obj_table->add_filter($structure);
// create totals
$this->obj_table->total_columns = array("time_booked");
// load options form
$this->obj_table->load_options_form();
// generate & execute SQL query
$this->obj_table->generate_sql();
$this->obj_table->load_data_sql();
}
function execute()
{
/*
Define form structure
*/
$this->obj_form = new form_input();
$this->obj_form->formname = "transaction_view";
$this->obj_form->language = $_SESSION["user"]["lang"];
$this->obj_form->action = "accounts/gl/edit-process.php";
$this->obj_form->method = "post";
// general
$structure = NULL;
$structure["fieldname"] = "code_gl";
$structure["type"] = "input";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "date_trans";
$structure["type"] = "date";
$structure["defaultvalue"] = date("Y-m-d");
$structure["options"]["req"] = "yes";
$this->obj_form->add_input($structure);
$sql_struct_obj = new sql_query();
$sql_struct_obj->prepare_sql_settable("staff");
$sql_struct_obj->prepare_sql_addfield("id", "staff.id");
$sql_struct_obj->prepare_sql_addfield("label", "staff.staff_code");
$sql_struct_obj->prepare_sql_addfield("label1", "staff.name_staff");
$sql_struct_obj->prepare_sql_addorderby("staff_code");
$sql_struct_obj->prepare_sql_addwhere("id = 'CURRENTID' OR date_end = '0000-00-00'");
$structure = form_helper_prepare_dropdownfromobj("employeeid", $sql_struct_obj);
$structure["options"]["req"] = "yes";
$structure["options"]["autoselect"] = "yes";
$structure["options"]["width"] = "600";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "description";
$structure["type"] = "input";
$structure["options"]["width"] = "600";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "description_useall";
$structure["type"] = "checkbox";
$structure["options"]["label"] = "Check this to use the description above as the description in all the rows below. Untick if you wish to have different messages for each transaction item.";
$structure["defaultvalue"] = "on";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "notes";
$structure["type"] = "textarea";
$structure["options"]["width"] = "600";
$structure["options"]["height"] = "50";
$this->obj_form->add_input($structure);
/*
Define transaction form structure
*/
// unless there has been error data returned, fetch all the transactions
// from the DB, and work out the number of rows
if (!isset($_SESSION["error"]["form"][$this->obj_form->formname])) {
$sql_trans_obj = new sql_query();
$sql_trans_obj->string = "SELECT date_trans, amount_debit, amount_credit, chartid, source, memo FROM `account_trans` WHERE type='gl' AND customid='" . $this->id . "'";
$sql_trans_obj->execute();
if ($sql_trans_obj->num_rows()) {
$sql_trans_obj->fetch_array();
$this->num_trans = $sql_trans_obj->data_num_rows + 1;
}
} else {
$this->num_trans = @security_script_input('/^[0-9]*$/', $_SESSION["error"]["num_trans"]) + 1;
}
// ensure there are always 2 rows at least, additional rows are added if required (ie viewing
// an existing transaction) or on the fly when needed by javascript UI.
if ($this->num_trans < 2) {
$this->num_trans = 2;
}
// transaction rows
for ($i = 0; $i < $this->num_trans; $i++) {
// account
$structure = form_helper_prepare_dropdownfromdb("trans_" . $i . "_account", "SELECT id, code_chart as label, description as label1 FROM account_charts WHERE chart_type!='1' ORDER BY code_chart");
$structure["options"]["width"] = "200";
$this->obj_form->add_input($structure);
// debit field
$structure = NULL;
$structure["fieldname"] = "trans_" . $i . "_debit";
$structure["type"] = "input";
$structure["options"]["width"] = "80";
$this->obj_form->add_input($structure);
// credit field
$structure = NULL;
$structure["fieldname"] = "trans_" . $i . "_credit";
$structure["type"] = "input";
$structure["options"]["width"] = "80";
$this->obj_form->add_input($structure);
// source
$structure = NULL;
$structure["fieldname"] = "trans_" . $i . "_source";
$structure["type"] = "input";
$structure["options"]["width"] = "100";
$this->obj_form->add_input($structure);
// description
$structure = NULL;
$structure["fieldname"] = "trans_" . $i . "_description";
$structure["type"] = "textarea";
$this->obj_form->add_input($structure);
// if we have data from a sql query, load it in
if ($sql_trans_obj->data_num_rows) {
if (isset($sql_trans_obj->data[$i]["chartid"])) {
$this->obj_form->structure["trans_" . $i . "_debit"]["defaultvalue"] = $sql_trans_obj->data[$i]["amount_debit"];
$this->obj_form->structure["trans_" . $i . "_credit"]["defaultvalue"] = $sql_trans_obj->data[$i]["amount_credit"];
$this->obj_form->structure["trans_" . $i . "_account"]["defaultvalue"] = $sql_trans_obj->data[$i]["chartid"];
$this->obj_form->structure["trans_" . $i . "_source"]["defaultvalue"] = $sql_trans_obj->data[$i]["source"];
$this->obj_form->structure["trans_" . $i . "_description"]["defaultvalue"] = $sql_trans_obj->data[$i]["memo"];
}
}
}
// total fields
$structure = NULL;
$structure["fieldname"] = "total_debit";
$structure["type"] = "hidden";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "total_credit";
$structure["type"] = "hidden";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "money_format";
$structure["type"] = "hidden";
$structure["defaultvalue"] = format_money(0);
$this->obj_form->add_input($structure);
// hidden
$structure = NULL;
$structure["fieldname"] = "id_transaction";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $this->id;
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "num_trans";
$structure["type"] = "hidden";
$structure["defaultvalue"] = "{$this->num_trans}";
$this->obj_form->add_input($structure);
// submit section
$structure = NULL;
$structure["fieldname"] = "submit";
$structure["type"] = "submit";
$structure["defaultvalue"] = "Save Changes";
$this->obj_form->add_input($structure);
// fetch the general form data
$this->obj_form->sql_query = "SELECT * FROM `account_gl` WHERE id='" . $this->id . "' LIMIT 1";
$this->obj_form->load_data();
// calculate totals
for ($i = 0; $i < $this->num_trans; $i++) {
@($this->obj_form->structure["total_debit"]["defaultvalue"] += $this->obj_form->structure["trans_" . $i . "_debit"]["defaultvalue"]);
@($this->obj_form->structure["total_credit"]["defaultvalue"] += $this->obj_form->structure["trans_" . $i . "_credit"]["defaultvalue"]);
}
}
function execute()
{
/*
Filter selection form
*/
// fetch existing values
$this->date_start = @security_script_input("/^[0-9]*-[0-9]*-[0-9]*\$/", $_GET["date_start_yyyy"] . "-" . $_GET["date_start_mm"] . "-" . $_GET["date_start_dd"]);
$this->date_end = @security_script_input("/^[0-9]*-[0-9]*-[0-9]*\$/", $_GET["date_end_yyyy"] . "-" . $_GET["date_end_mm"] . "-" . $_GET["date_end_dd"]);
$this->mode = @security_script_input("/^\\S*\$/", $_GET["mode"]);
if (!$this->mode) {
if ($_SESSION["account_reports"]["mode"]) {
$this->mode = $_SESSION["account_reports"]["mode"];
} else {
$this->mode = "Accrual/Invoice";
}
}
if (!$this->date_start || $this->date_start == "--") {
if ($_SESSION["account_reports"]["date_start"]) {
$this->date_start = $_SESSION["account_reports"]["date_start"];
} else {
$this->date_start = NULL;
}
}
if (!$this->date_end || $this->date_end == "--") {
if ($_SESSION["account_reports"]["date_end"]) {
$this->date_end = $_SESSION["account_reports"]["date_end"];
} else {
$this->date_end = NULL;
}
}
// save to session vars
$_SESSION["account_reports"]["date_start"] = $this->date_start;
$_SESSION["account_reports"]["date_end"] = $this->date_end;
$_SESSION["account_reports"]["mode"] = $this->mode;
// define form
$this->obj_form = new form_input();
$this->obj_form->method = "get";
$this->obj_form->action = "index.php";
$this->obj_form->formname = "accounts_report_incomestatement";
$this->obj_form->language = $_SESSION["user"]["lang"];
// hidden values
$structure = NULL;
$structure["fieldname"] = "page";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $_GET["page"];
$this->obj_form->add_input($structure);
// date selection
$structure = NULL;
$structure["fieldname"] = "date_start";
$structure["type"] = "date";
$structure["defaultvalue"] = $this->date_start;
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "date_end";
$structure["type"] = "date";
$structure["defaultvalue"] = $this->date_end;
$this->obj_form->add_input($structure);
// mode selection
$structure = NULL;
$structure["fieldname"] = "mode";
$structure["type"] = "radio";
$structure["values"] = array("Accrual/Invoice", "Cash");
$structure["defaultvalue"] = $this->mode;
$this->obj_form->add_input($structure);
// submit
$structure = NULL;
$structure["fieldname"] = "submit";
$structure["type"] = "submit";
$structure["defaultvalue"] = "Apply Filter Options";
$this->obj_form->add_input($structure);
/*
Income Charts
*/
// chart details
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("account_charts");
$sql_obj->prepare_sql_addfield("id");
$sql_obj->prepare_sql_addfield("code_chart");
$sql_obj->prepare_sql_addfield("description");
$sql_obj->prepare_sql_addwhere("chart_type='5'");
$sql_obj->generate_sql();
$sql_obj->execute();
$sql_obj->fetch_array();
$this->data_income = $sql_obj->data;
unset($sql_obj);
/*
Expense Charts
*/
// chart details
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("account_charts");
$sql_obj->prepare_sql_addfield("id");
$sql_obj->prepare_sql_addfield("code_chart");
$sql_obj->prepare_sql_addfield("description");
$sql_obj->prepare_sql_addwhere("chart_type='6'");
$sql_obj->generate_sql();
$sql_obj->execute();
$sql_obj->fetch_array();
$this->data_expense = $sql_obj->data;
unset($sql_obj);
/*
Amounts
This section needs to total up the the amounts in each account - however, we are unable to just
pull the information from account_trans, because we need to be able to fetch either the invoiced/accural
amount OR the cash (ie: paid) amount.
The reports for taxes can handle it simpler, by just calcuating the invoice item total, but we also have
to include general ledger transactions.
Credit notes add additional complexity, however the solution is the same for both accural/invoice and cash
basis - read all the items for the credit notes for the selected date range, and subtract from the appropiate
account.
Note that it is entirely possible for the credit notes to end up being more than the invoice amount for a selected
time period, in which case, the account may go into negative, which is acceptable.
Accural/Invoice:
1. Run though all invoices
2. Add item amounts to accounts
3. Run through all credit notes during period
4. Subtract credit notes falling during period
5. Run through all general ledger transactions
6. Add GL amounts to accounts
Cash:
1. Run through all invoices
2. If invoices are fully paid, then add item amounts to accounts.
3. Impossible to handle partially paid invoices properly, so we ignore them.
4. Run through all credit notes during period
5. Subtract credit notes falling during period
6. Run through all general ledger transactions
7. Add GL amounts to accounts.
Note: The date checks are made against the invoice date, not the payment date.
*/
//
// AR INVOICES
//
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("account_ar");
$sql_obj->prepare_sql_addfield("id");
// date options
if ($this->date_start) {
$sql_obj->prepare_sql_addwhere("date_trans >= '" . $this->date_start . "'");
}
if ($this->date_end) {
$sql_obj->prepare_sql_addwhere("date_trans <= '" . $this->date_end . "'");
}
// paid invoices only
if ($this->mode == "Cash") {
$sql_obj->prepare_sql_addwhere("amount_total=amount_paid");
}
// run through invoices
$sql_obj->generate_sql();
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
foreach ($sql_obj->data as $data_invoice) {
// fetch all items for this invoice type
$sql_item_obj = new sql_query();
$sql_item_obj->string = "SELECT chartid, amount FROM account_items WHERE invoiceid='" . $data_invoice["id"] . "' AND invoicetype='ar'";
$sql_item_obj->execute();
if ($sql_item_obj->num_rows()) {
$sql_item_obj->fetch_array();
foreach ($sql_item_obj->data as $data_item) {
// run through income charts
for ($i = 0; $i < count(array_keys($this->data_income)); $i++) {
if ($data_item["chartid"] == $this->data_income[$i]["id"]) {
@($this->data_income[$i]["amount"] += $data_item["amount"]);
}
}
// end of loop through charts
}
// end of invoice item loop
}
// end if invoice items
}
// end of invoice loop
}
// end if invoices
unset($sql_obj);
//
// AR CREDIT NOTES
//
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("account_ar_credit");
$sql_obj->prepare_sql_addfield("id");
// date options
if ($this->date_start) {
$sql_obj->prepare_sql_addwhere("date_trans >= '" . $this->date_start . "'");
}
if ($this->date_end) {
$sql_obj->prepare_sql_addwhere("date_trans <= '" . $this->date_end . "'");
}
// run through credit notes
$sql_obj->generate_sql();
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
foreach ($sql_obj->data as $data_credit) {
// fetch all items for this invoice type
$sql_item_obj = new sql_query();
$sql_item_obj->string = "SELECT chartid, amount FROM account_items WHERE invoiceid='" . $data_credit["id"] . "' AND invoicetype='ar_credit'";
$sql_item_obj->execute();
if ($sql_item_obj->num_rows()) {
$sql_item_obj->fetch_array();
foreach ($sql_item_obj->data as $data_item) {
// run through income charts
for ($i = 0; $i < count(array_keys($this->data_income)); $i++) {
if ($data_item["chartid"] == $this->data_income[$i]["id"]) {
@($this->data_income[$i]["amount"] -= $data_item["amount"]);
}
}
// end of loop through charts
}
// end of credit item loop
}
// end of credit items
}
// end of credit loop
}
// end of credits
unset($sql_obj);
//
// AP INVOICES
//
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("account_ap");
$sql_obj->prepare_sql_addfield("id");
// date options
if ($this->date_start) {
$sql_obj->prepare_sql_addwhere("date_trans >= '" . $this->date_start . "'");
}
if ($this->date_end) {
$sql_obj->prepare_sql_addwhere("date_trans <= '" . $this->date_end . "'");
}
// paid invoices only
if ($this->mode == "Cash") {
$sql_obj->prepare_sql_addwhere("amount_total=amount_paid");
}
// run through invoices
$sql_obj->generate_sql();
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
foreach ($sql_obj->data as $data_invoice) {
// fetch all items for this invoice type
$sql_item_obj = new sql_query();
$sql_item_obj->string = "SELECT chartid, amount FROM account_items WHERE invoiceid='" . $data_invoice["id"] . "' AND invoicetype='ap'";
$sql_item_obj->execute();
if ($sql_item_obj->num_rows()) {
$sql_item_obj->fetch_array();
foreach ($sql_item_obj->data as $data_item) {
// run through expense charts
for ($i = 0; $i < count(array_keys($this->data_expense)); $i++) {
if ($data_item["chartid"] == $this->data_expense[$i]["id"]) {
@($this->data_expense[$i]["amount"] += $data_item["amount"]);
}
}
// end of loop through charts
}
// end of invoice item loop
}
// end if invoice items
}
// end of invoice loop
}
// end if invoices
unset($sql_obj);
//
// AP CREDIT NOTES
//
/*
TODO: AP credit notes have not been implemented at this stage - when they are, an appropiate section will need to be added
here to support them.
*/
//
// GL TRANSACTIONS
//
// Fetch all the GL transactions during this period and add to totals.
//
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("account_trans");
$sql_obj->prepare_sql_addfield("chartid");
$sql_obj->prepare_sql_addfield("amount_debit");
$sql_obj->prepare_sql_addfield("amount_credit");
$sql_obj->prepare_sql_addwhere("type='gl'");
// date options
if ($this->date_start) {
$sql_obj->prepare_sql_addwhere("date_trans >= '" . $this->date_start . "'");
}
if ($this->date_end) {
$sql_obj->prepare_sql_addwhere("date_trans <= '" . $this->date_end . "'");
}
// run through GL entries
$sql_obj->generate_sql();
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
foreach ($sql_obj->data as $data_trans) {
// run through income charts
for ($i = 0; $i < count(array_keys($this->data_income)); $i++) {
if ($data_trans["chartid"] == $this->data_income[$i]["id"]) {
$this->data_income[$i]["amount"] += $data_trans["amount_credit"];
}
}
// end of loop through income charts
// run through expense charts
for ($i = 0; $i < count(array_keys($this->data_expense)); $i++) {
if ($data_trans["chartid"] == $this->data_expense[$i]["id"]) {
$this->data_expense[$i]["amount"] += $data_trans["amount_debit"];
}
}
// end of loop through expense charts
}
// end of transaction loop
}
// end if transaction exist
/*
Totals
*/
// income
$this->data_totals["income"] = 0;
for ($i = 0; $i < count(array_keys($this->data_income)); $i++) {
@($this->data_totals["income"] += $this->data_income[$i]["amount"]);
}
// expense
for ($i = 0; $i < count(array_keys($this->data_expense)); $i++) {
@($this->data_totals["expense"] += $this->data_expense[$i]["amount"]);
}
// final
$this->data_totals["final"] = @$this->data_totals["income"] - $this->data_totals["expense"];
$this->data_totals["income"] = @format_money($this->data_totals["income"]);
$this->data_totals["expense"] = @format_money($this->data_totals["expense"]);
$this->data_totals["final"] = @format_money($this->data_totals["final"]);
}
function execute()
{
/*
Date selection form
*/
// fetch existing dates
$this->date_start = @security_script_input("/^[0-9]*-[0-9]*-[0-9]*\$/", $_GET["date_start_yyyy"] . "-" . $_GET["date_start_mm"] . "-" . $_GET["date_start_dd"]);
$this->date_end = @security_script_input("/^[0-9]*-[0-9]*-[0-9]*\$/", $_GET["date_end_yyyy"] . "-" . $_GET["date_end_mm"] . "-" . $_GET["date_end_dd"]);
if (!$this->date_start || $this->date_start == "--") {
if ($_SESSION["account_reports"]["date_start"]) {
$this->date_start = $_SESSION["account_reports"]["date_start"];
} else {
$this->date_start = NULL;
}
}
if (!$this->date_end || $this->date_end == "--") {
if ($_SESSION["account_reports"]["date_end"]) {
$this->date_end = $_SESSION["account_reports"]["date_end"];
} else {
$this->date_end = NULL;
}
}
// save to session vars
$_SESSION["account_reports"]["date_start"] = $this->date_start;
$_SESSION["account_reports"]["date_end"] = $this->date_end;
// define form
$this->obj_form = new form_input();
$this->obj_form->method = "get";
$this->obj_form->action = "index.php";
$this->obj_form->formname = "accounts_report_trialbalance";
$this->obj_form->language = $_SESSION["user"]["lang"];
// hidden values
$structure = NULL;
$structure["fieldname"] = "page";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $_GET["page"];
$this->obj_form->add_input($structure);
// date selection
$structure = NULL;
$structure["fieldname"] = "date_start";
$structure["type"] = "date";
$structure["defaultvalue"] = $this->date_start;
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "date_end";
$structure["type"] = "date";
$structure["defaultvalue"] = $this->date_end;
$this->obj_form->add_input($structure);
// submit
$structure = NULL;
$structure["fieldname"] = "submit";
$structure["type"] = "submit";
$structure["defaultvalue"] = "Apply Filter Options";
$this->obj_form->add_input($structure);
// establish a new table object
$this->obj_table = new table();
$this->obj_table->language = $_SESSION["user"]["lang"];
$this->obj_table->tablename = "accounts_reports_trialbalance";
// define all the columns and structure
$this->obj_table->add_column("standard", "code_chart", "account_charts.code_chart");
$this->obj_table->add_column("standard", "description", "account_charts.description");
$this->obj_table->add_column("standard", "chart_type", "account_chart_type.value");
// the debit and credit columns need to be calculated by a seporate query
$this->obj_table->add_column("price", "debit", "NONE");
$this->obj_table->add_column("price", "credit", "NONE");
// defaults
$this->obj_table->columns = array("code_chart", "description", "chart_type", "debit", "credit");
$this->obj_table->columns_order = array("code_chart");
// totals
$this->obj_table->total_columns = array("debit", "credit");
$this->obj_table->total_rows = array("debit", "credit");
$this->obj_table->total_rows_mode = "subtotal_nofinal";
// this is actually re-calculated based on chart type
// define SQL structure
$this->obj_table->sql_obj->prepare_sql_settable("account_charts");
$this->obj_table->sql_obj->prepare_sql_addfield("id", "account_charts.id");
$this->obj_table->sql_obj->prepare_sql_addfield("chart_total_mode", "account_chart_type.total_mode");
$this->obj_table->sql_obj->prepare_sql_addjoin("LEFT JOIN account_chart_type ON account_chart_type.id = account_charts.chart_type");
$this->obj_table->sql_obj->prepare_sql_addwhere("account_charts.chart_type != '1'");
// fetch all the chart information
$this->obj_table->generate_sql();
$this->obj_table->load_data_sql();
// fetch debit and credit summaries for all charts in advance - this
// is better than running a query per chart just to get all the totals
$sql_amount_obj = new sql_query();
$sql_amount_obj->prepare_sql_settable("account_trans");
$sql_amount_obj->prepare_sql_addfield("chartid");
$sql_amount_obj->prepare_sql_addfield("credit", "SUM(amount_credit)");
$sql_amount_obj->prepare_sql_addfield("debit", "SUM(amount_debit)");
if ($this->date_start) {
$sql_amount_obj->prepare_sql_addwhere("date_trans >= '" . $this->date_start . "'");
}
if ($this->date_end) {
$sql_amount_obj->prepare_sql_addwhere("date_trans <= '" . $this->date_end . "'");
}
$sql_amount_obj->prepare_sql_addgroupby("chartid");
$sql_amount_obj->generate_sql();
$sql_amount_obj->execute();
if ($sql_amount_obj->num_rows()) {
$sql_amount_obj->fetch_array();
// run through all the chart rows and fill in the credit/debit fields
for ($i = 0; $i < count(array_keys($this->obj_table->data)); $i++) {
foreach ($sql_amount_obj->data as $data_amount) {
if ($data_amount["chartid"] == $this->obj_table->data[$i]["id"]) {
$this->obj_table->data[$i]["debit"] = $data_amount["debit"];
$this->obj_table->data[$i]["credit"] = $data_amount["credit"];
}
}
}
}
// process table data
$this->obj_table->render_table_prepare();
// correctly generate the total column
for ($i = 0; $i < count(array_keys($this->obj_table->data)); $i++) {
if (!empty($this->obj_table->data[$i]["debit"]) && !empty($this->obj_table->data[$i]["debit"])) {
if ($this->obj_table->data[$i]["chart_total_mode"] == "credit") {
$this->obj_table->data[$i]["total"] = $this->obj_table->data[$i]["credit"] - $this->obj_table->data[$i]["debit"];
} else {
$this->obj_table->data[$i]["total"] = $this->obj_table->data[$i]["debit"] - $this->obj_table->data[$i]["credit"];
}
$this->obj_table->data_render[$i]["total"] = format_money($this->obj_table->data[$i]["total"]);
}
}
$this->obj_table->data["total"]["total"] = $this->obj_table->data["total"]["debit"] - $this->obj_table->data["total"]["credit"];
$this->obj_table->data_render["total"]["total"] = format_money($this->obj_table->data["total"]["total"]);
}
function render_html()
{
// calcuate next/previous week/year
if ($this->date_selected_weekofyear == 1) {
$date_option_previousyear = $this->date_selected_year - 1;
$date_option_previousweek = 52;
$date_option_nextyear = $this->date_selected_year;
$date_option_nextweek = 2;
} elseif ($this->date_selected_weekofyear == 52) {
$date_option_previousyear = $this->date_selected_year;
$date_option_previousweek = 51;
$date_option_nextyear = $this->date_selected_year + 1;
$date_option_nextweek = 1;
} else {
$date_option_previousyear = $this->date_selected_year;
$date_option_previousweek = $this->date_selected_weekofyear - 1;
$date_option_nextyear = $this->date_selected_year;
$date_option_nextweek = $this->date_selected_weekofyear + 1;
}
// Week view header
print "<h3>TIME REGISTRATION</h3><br><br>";
/*
Unbilled Time
*/
if (user_permissions_get("projects_timegroup")) {
/*
Create an array of all unbilled time records. We need to do the following to create this list:
1. Exclude any internal_only projects.
2. Include time which belongs to a time_group, but ONLY if the time group has not been added to an invoice.
*/
$unbilled_ids = array();
// select non-internal projects
$sql_projects_obj = new sql_query();
$sql_projects_obj->string = "SELECT projects.id as projectid, project_phases.id as phaseid FROM project_phases LEFT JOIN projects ON projects.id = project_phases.projectid WHERE projects.internal_only='0'";
$sql_projects_obj->execute();
if ($sql_projects_obj->num_rows()) {
$sql_projects_obj->fetch_array();
foreach ($sql_projects_obj->data as $project_data) {
// select non-group time records
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id FROM timereg WHERE groupid='0' AND phaseid='" . $project_data["phaseid"] . "'";
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
foreach ($sql_obj->data as $data_tmp) {
// we store the ID inside an array key, since they are unique
// and this will prevent us needed to check for the existance of
// the ID already.
$unbilled_ids[$data_tmp["id"]] = "on";
}
}
unset($sql_obj);
// select unpaid group IDs
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id FROM time_groups WHERE projectid='" . $project_data["projectid"] . "' AND invoiceid='0'";
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
foreach ($sql_obj->data as $data_group) {
// fetch all the time reg IDs belonging this group, but only select time entries marked as billable - we
// don't want to report a timegroup with unbillable time as being billed!
$sql_reg_obj = new sql_query();
$sql_reg_obj->string = "SELECT id FROM timereg WHERE groupid='" . $data_group["id"] . "' AND billable='1'";
$sql_reg_obj->execute();
if ($sql_reg_obj->num_rows()) {
$sql_reg_obj->fetch_array();
foreach ($sql_reg_obj->data as $data_tmp) {
// we store the ID inside an array key, since they are unique
// and this will prevent us needed to check for the existance of
// the ID already.
$unbilled_ids[$data_tmp["id"]] = "on";
}
}
unset($sql_reg_obj);
}
}
unset($sql_obj);
}
}
// fetch amount of unbilled time
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("timereg");
$sql_obj->prepare_sql_addfield("timebooked", "SUM(timereg.time_booked)");
if ($this->access_staff_ids) {
$sql_obj->prepare_sql_addwhere("employeeid IN (" . format_arraytocommastring($this->access_staff_ids) . ")");
}
$sql_obj->prepare_sql_addjoin("LEFT JOIN time_groups ON timereg.groupid = time_groups.id");
// provide list of valid IDs
$unbilled_ids_keys = array_keys($unbilled_ids);
$unbilled_ids_count = count($unbilled_ids_keys);
$unbilled_ids_sql = "";
if ($unbilled_ids_count) {
$i = 0;
foreach ($unbilled_ids_keys as $id) {
$i++;
if ($i == $unbilled_ids_count) {
$unbilled_ids_sql .= "timereg.id='{$id}' ";
} else {
$unbilled_ids_sql .= "timereg.id='{$id}' OR ";
}
}
$sql_obj->prepare_sql_addwhere("({$unbilled_ids_sql})");
$sql_obj->generate_sql();
$sql_obj->execute();
$sql_obj->fetch_array();
list($unbilled_time_hours, $unbilled_time_mins) = explode(":", time_format_hourmins($sql_obj->data[0]["timebooked"]));
if ($unbilled_time_hours > 0 && $unbilled_time_mins > 0) {
$message = "There are currently {$unbilled_time_hours} hours and {$unbilled_time_mins} minutes of unbilled time to be processed. Click here to view.";
} elseif ($unbilled_time_hours > 0) {
$message = "There are currently {$unbilled_time_hours} hours of unbilled time to be processed. Click here to view.";
} elseif ($unbilled_time_mins > 0) {
$message = "There are currently {$unbilled_time_mins} minutes of unbilled time to be processed. Click here to view.";
}
} else {
$message = "There is no unbilled time to be processed.";
}
// display
print "<br>";
format_linkbox("default", "index.php?page=timekeeping/unbilled.php", "<p><b>UNBILLED TIME</b></p><p>{$message}</p>");
}
/*end unbilled time*/
print "<br />";
/*
Time booked
*/
// fetch amount of time booked for today
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("timereg");
$sql_obj->prepare_sql_addfield("timebooked", "SUM(timereg.time_booked)");
$sql_obj->prepare_sql_addwhere("date='" . date("Y-m-d") . "'");
if ($this->access_staff_ids) {
$sql_obj->prepare_sql_addwhere("employeeid IN (" . format_arraytocommastring($this->access_staff_ids) . ")");
}
$sql_obj->generate_sql();
$sql_obj->execute();
$sql_obj->fetch_array();
list($booked_time_hours, $booked_time_mins) = explode(":", time_format_hourmins($sql_obj->data[0]["timebooked"]));
if ($booked_time_hours > 0 && $booked_time_mins > 0) {
$message = "<b>Time booked for today: {$booked_time_hours} hours and {$booked_time_mins} minutes.</b><br />Click here to add more time.";
} elseif ($booked_time_hours > 0) {
$message = "<b>Time booked for today: {$booked_time_hours} hours.</b><br />Click here to add more time.";
} elseif ($booked_time_mins > 0) {
$message = "<b>Time booked for today: {$booked_time_mins} minutes.</b><br />Click here to add more time.";
} else {
$message = "<b>No time has been booked for today</b><br />Click here to add time.</b>";
}
format_linkbox("default", "index.php?page=timekeeping/timereg-day-edit.php", "<p>{$message}</p>");
print "<br />";
print "<table class=\"table_highlight\" width=\"100%\"><tr>";
// Week selection links
print "<td width=\"70%\">";
print "<b>WEEK " . $this->date_selected_weekofyear . ", " . $this->date_selected_year . "</b><br>";
print "(" . time_format_humandate($this->date_selected_start) . " to " . time_format_humandate($this->date_selected_end) . ")<br>";
print "<br>";
print "<p><b>";
print "<a class=\"button\" href=\"index.php?page=timekeeping/timereg.php&employeeid=" . $this->employeeid . "&weekofyear=" . $date_option_previousweek . "&year=" . $date_option_previousyear . "\"><< Previous Week</a>";
// check for date in the future
if ($this->config_timesheet_booktofuture == "disabled") {
if (time_date_to_timestamp(time_calculate_weekstart($date_option_nextweek, $date_option_nextyear)) < time()) {
// end date is in not in the future
print " <a class=\"button\" href=\"index.php?page=timekeeping/timereg.php&employeeid=" . $this->employeeid . "&weekofyear=" . $date_option_nextweek . "&year=" . $date_option_nextyear . "\">Next Week >></a>";
}
} else {
print " <a class=\"button\" href=\"index.php?page=timekeeping/timereg.php&employeeid=" . $this->employeeid . "&weekofyear=" . $date_option_nextweek . "&year=" . $date_option_nextyear . "\">Next Week >></a>";
}
print "</b></p>";
print "</td>";
// goto date form
print "<td width=\"30%\">";
print "<form method=\"get\" action=\"index.php\" class=\"form_standard\">";
$this->obj_form_goto->render_field("date");
print "<br>";
$this->obj_form_goto->render_field("page");
$this->obj_form_goto->render_field("submit");
print "</form>";
print "</td>";
print "</tr></table><br>";
// Employee selection form
//
// we use a custom form display method here, since the normal form
// class will draw a fully styled form in a table.
//
if ($this->employeeid) {
print "<table class=\"table_highlight\" width=\"100%\"><tr><td width=\"100%\">";
} else {
print "<table class=\"table_highlight_important\" width=\"100%\"><tr><td width=\"100%\">";
}
print "<form method=\"get\" action=\"index.php\" class=\"form_standard\">";
print "<p><b>Select an employee to view:</b></p>";
$this->obj_form_employee->render_field("employeeid");
$this->obj_form_employee->render_field("weekofyear");
$this->obj_form_employee->render_field("year");
$this->obj_form_employee->render_field("page");
$this->obj_form_employee->render_field("submit");
print "</form>";
print "</td></tr></table><br>";
if ($this->employeeid) {
// custom labels and links
if ($this->config_timesheet_booktofuture == "disabled") {
if (time_date_to_timestamp($this->date_selected_daysofweek[0]) < time()) {
$this->obj_table_week->custom_column_link("monday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[0] . "");
}
if (time_date_to_timestamp($this->date_selected_daysofweek[1]) < time()) {
$this->obj_table_week->custom_column_link("tuesday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[1] . "");
}
if (time_date_to_timestamp($this->date_selected_daysofweek[2]) < time()) {
$this->obj_table_week->custom_column_link("wednesday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[2] . "");
}
if (time_date_to_timestamp($this->date_selected_daysofweek[3]) < time()) {
$this->obj_table_week->custom_column_link("thursday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[3] . "");
}
if (time_date_to_timestamp($this->date_selected_daysofweek[4]) < time()) {
$this->obj_table_week->custom_column_link("friday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[4] . "");
}
if (time_date_to_timestamp($this->date_selected_daysofweek[5]) < time()) {
$this->obj_table_week->custom_column_link("saturday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[5] . "");
}
if (time_date_to_timestamp($this->date_selected_daysofweek[6]) < time()) {
$this->obj_table_week->custom_column_link("sunday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[6] . "");
}
} else {
// add links
$this->obj_table_week->custom_column_link("monday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[0] . "");
$this->obj_table_week->custom_column_link("tuesday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[1] . "");
$this->obj_table_week->custom_column_link("wednesday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[2] . "");
$this->obj_table_week->custom_column_link("thursday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[3] . "");
$this->obj_table_week->custom_column_link("friday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[4] . "");
$this->obj_table_week->custom_column_link("saturday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[5] . "");
$this->obj_table_week->custom_column_link("sunday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[6] . "");
}
// column labels
$this->obj_table_week->custom_column_label("monday", "Monday<br><font style=\"font-size: 8px;\">(" . time_format_humandate($this->date_selected_daysofweek[0]) . ")</font>");
$this->obj_table_week->custom_column_label("tuesday", "Tuesday<br><font style=\"font-size: 8px;\">(" . time_format_humandate($this->date_selected_daysofweek[1]) . ")</font>");
$this->obj_table_week->custom_column_label("wednesday", "Wednesday<br><font style=\"font-size: 8px;\">(" . time_format_humandate($this->date_selected_daysofweek[2]) . ")</font>");
$this->obj_table_week->custom_column_label("thursday", "Thursday<br><font style=\"font-size: 8px;\">(" . time_format_humandate($this->date_selected_daysofweek[3]) . ")</font>");
$this->obj_table_week->custom_column_label("friday", "Friday<br><font style=\"font-size: 8px;\">(" . time_format_humandate($this->date_selected_daysofweek[4]) . ")</font>");
$this->obj_table_week->custom_column_label("saturday", "Saturday<br><font style=\"font-size: 8px;\">(" . time_format_humandate($this->date_selected_daysofweek[5]) . ")</font>");
$this->obj_table_week->custom_column_label("sunday", "Sunday<br><font style=\"font-size: 8px;\">(" . time_format_humandate($this->date_selected_daysofweek[6]) . ")</font>");
// display week time table
$this->obj_table_week->render_table_html();
print "<table width=\"100%\">";
// add time link
if (user_permissions_staff_get("timereg_write", $this->employeeid)) {
print "<td align=\"left\" valign=\"top\"><p><a class=\"button\" href=\"index.php?page=timekeeping/timereg-day-edit.php\">Add new time entry</a></p></td>";
} else {
print "<p><i>You have read-only access to this employee and therefore can not add any more time.</i></p>";
}
// display CSV/PDF download link
print "<td align=\"right\">";
print "<p><a class=\"button_export\" href=\"index-export.php?mode=csv&page=timekeeping/timereg.php\">Export as CSV</a></p>";
print "<p><a class=\"button_export\" href=\"index-export.php?mode=pdf&page=timekeeping/timereg.php\">Export as PDF</a></p>";
print "</td>";
print "</table>";
}
}
function execute()
{
log_debug("services_form_details", "Executing execute()");
/*
Define form structure
*/
$this->obj_form = new form_input();
$this->obj_form->formname = "service_" . $this->mode;
$this->obj_form->language = $_SESSION["user"]["lang"];
$this->obj_form->action = "services/edit-process.php";
$this->obj_form->method = "post";
// general
$structure = NULL;
$structure["fieldname"] = "name_service";
$structure["type"] = "input";
$structure["options"]["req"] = "yes";
$this->obj_form->add_input($structure);
$structure = charts_form_prepare_acccountdropdown("chartid", "ar_income");
$structure["options"]["search_filter"] = "yes";
$structure["options"]["width"] = "400";
$structure["options"]["req"] = "yes";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "description";
$structure["type"] = "textarea";
$structure["options"]["width"] = "600";
$structure["options"]["height"] = "100";
$this->obj_form->add_input($structure);
// the service type can only be set at creation time.
if ($this->mode == "add") {
$structure = form_helper_prepare_radiofromdb("typeid", "SELECT id, name as label, description as label1 FROM service_types WHERE active='1' ORDER BY name");
$structure["options"]["req"] = "yes";
// replace all the -- joiners with <br> for clarity
for ($i = 0; $i < count($structure["values"]); $i++) {
$structure["translations"][$structure["values"][$i]] = str_replace("--", "<br><i>", $structure["translations"][$structure["values"][$i]]);
$structure["translations"][$structure["values"][$i]] .= "</i><br>";
}
// handle misconfiguration gracefully
if (empty($this->obj_form->structure["typeid"]["values"])) {
$this->obj_form->structure["typeid"]["type"] = "text";
$this->obj_form->structure["typeid"]["defaultvalue"] = "error_no_types_available";
}
$this->obj_form->add_input($structure);
} else {
$structure = NULL;
$structure["fieldname"] = "typeid";
$structure["type"] = "text";
$this->obj_form->add_input($structure);
}
// service grouping
$structure = form_helper_prepare_dropdownfromgroup("id_service_group", "SELECT id as value_id, group_name as value_key, id_parent as value_parent FROM service_groups");
$structure["options"]["req"] = "yes";
$structure["options"]["search_filter"] = "yes";
$structure["options"]["width"] = "400";
$this->obj_form->add_input($structure);
$structure = form_helper_prepare_dropdownfromgroup("id_service_group_usage", "SELECT id as value_id, group_name as value_key, id_parent as value_parent FROM service_groups");
$structure["options"]["req"] = "yes";
$structure["options"]["search_filter"] = "yes";
$structure["options"]["width"] = "400";
$this->obj_form->add_input($structure);
// write service usage grouping javascript UI logic - we need to get all the options
// and write actions for each ID
$this->obj_form->add_action("typeid", "default", "id_service_group_usage", "hide");
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id, name as label FROM service_types";
$sql_obj->execute();
$sql_obj->fetch_array();
foreach ($sql_obj->data as $data_row) {
switch ($data_row["label"]) {
case "data_traffic":
case "generic_with_usage":
case "phone_single":
case "phone_trunk":
case "phone_tollfree":
case "time":
$this->obj_form->add_action("typeid", $data_row["id"], "id_service_group_usage", "show");
// for add mode
$this->obj_form->add_action("typeid", $data_row["label"], "id_service_group_usage", "show");
// for view mode
break;
case "bundle":
case "generic_no_usage":
case "licenses":
$this->obj_form->add_action("typeid", $data_row["id"], "id_service_group_usage", "hide");
// for add mode
$this->obj_form->add_action("typeid", $data_row["label"], "id_service_group_usage", "hide");
// for view mode
break;
}
}
// define service_details subform
$this->obj_form->subforms["service_details"] = array("name_service", "chartid", "typeid", "id_service_group", "id_service_group_usage", "description");
/*
List all the taxes, so that the user can select the tax(es) that apply to the service
*/
$sql_tax_obj = new sql_query();
$sql_tax_obj->string = "SELECT id, name_tax, description, default_services FROM account_taxes ORDER BY name_tax";
$sql_tax_obj->execute();
if ($sql_tax_obj->num_rows()) {
// user note
$structure = NULL;
$structure["fieldname"] = "tax_message";
$structure["type"] = "message";
$structure["defaultvalue"] = "<p>Check all taxes that apply to this service below.</p>";
$this->obj_form->add_input($structure);
$this->obj_form->subforms["service_tax"][] = "tax_message";
// run through all the taxes
$sql_tax_obj->fetch_array();
foreach ($sql_tax_obj->data as $data_tax) {
// define tax checkbox
$structure = NULL;
$structure["fieldname"] = "tax_" . $data_tax["id"];
$structure["type"] = "checkbox";
$structure["options"]["label"] = $data_tax["name_tax"] . " -- " . $data_tax["description"];
$structure["options"]["no_fieldname"] = "enable";
if ($this->serviceid) {
// see if this tax is currently enabled for this service
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id FROM services_taxes WHERE serviceid='" . $this->serviceid . "' AND taxid='" . $data_tax["id"] . "' LIMIT 1";
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$structure["defaultvalue"] = "on";
}
} else {
if ($data_tax["default_services"]) {
$structure["defaultvalue"] = "on";
}
}
// add to form
$this->obj_form->add_input($structure);
$this->obj_form->subforms["service_tax"][] = "tax_" . $data_tax["id"];
}
}
/*
Upstream Vendor Information
These fields are purely for users notes/record keeping purposes.
*/
$structure = NULL;
$structure["fieldname"] = "upstream_help_message";
$structure["type"] = "message";
$structure["defaultvalue"] = "<p>" . lang_trans("upstream_help_message") . "</p>";
$this->obj_form->add_input($structure);
$sql_struct_obj = new sql_query();
$sql_struct_obj->prepare_sql_settable("vendors");
$sql_struct_obj->prepare_sql_addfield("id", "vendors.id");
$sql_struct_obj->prepare_sql_addfield("label", "vendors.code_vendor");
$sql_struct_obj->prepare_sql_addfield("label1", "vendors.name_vendor");
$sql_struct_obj->prepare_sql_addorderby("code_vendor");
$sql_struct_obj->prepare_sql_addwhere("id = 'CURRENTID' OR date_end = '0000-00-00'");
$structure = form_helper_prepare_dropdownfromobj("upstream_id", $sql_struct_obj);
$structure["options"]["search_filter"] = "yes";
$structure["options"]["width"] = "400";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "upstream_notes";
$structure["type"] = "textarea";
$structure["options"]["width"] = "600";
$structure["options"]["height"] = "100";
$this->obj_form->add_input($structure);
$this->obj_form->subforms["service_upstream"] = array("upstream_help_message", "upstream_id", "upstream_notes");
// define subforms
if (user_permissions_get("services_write")) {
$this->obj_form->subforms["submit"] = array("submit");
} else {
$this->obj_form->subforms["submit"] = array("");
}
/*
Mode dependent options
*/
if ($this->mode == "add") {
// submit button
$structure = NULL;
$structure["fieldname"] = "submit";
$structure["type"] = "submit";
$structure["defaultvalue"] = "Create Service";
$this->obj_form->add_input($structure);
} else {
// submit button
$structure = NULL;
$structure["fieldname"] = "submit";
$structure["type"] = "submit";
$structure["defaultvalue"] = "Save Changes";
$this->obj_form->add_input($structure);
// hidden data
$structure = NULL;
$structure["fieldname"] = "id_service";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $this->serviceid;
$this->obj_form->add_input($structure);
$this->obj_form->subforms["hidden"] = array("id_service");
}
/*
Load Data
*/
if ($this->mode == "add") {
$this->obj_form->load_data_error();
} else {
// load details data
$this->obj_form->sql_query = "SELECT \n\t\t\t\t\t\t\t\tservices.name_service, \n\t\t\t\t\t\t\t\tservices.chartid, \n\t\t\t\t\t\t\t\tservices.id_service_group,\n\t\t\t\t\t\t\t\tservices.id_service_group_usage,\n\t\t\t\t\t\t\t\tservices.description, \n\t\t\t\t\t\t\t\tservice_types.name as typeid,\n\t\t\t\t\t\t\t\tservices.upstream_id as upstream_id,\n\t\t\t\t\t\t\t\tservices.upstream_notes as upstream_notes\n\t\t\t\t\t\t\tFROM `services`\n\t\t\t\t\t\t\tLEFT JOIN service_types ON service_types.id = services.typeid\n\t\t\t\t\t\t\tWHERE services.id='" . $this->serviceid . "' LIMIT 1";
$this->obj_form->load_data();
}
}
function execute()
{
/*
Employee Selection Form
*/
$this->obj_form_employee = new form_input();
$this->obj_form_employee->formname = "timereg_employee";
$this->obj_form_employee->language = $_SESSION["user"]["lang"];
// employee selection box
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("staff");
$sql_obj->prepare_sql_addfield("id", "id");
$sql_obj->prepare_sql_addfield("label", "staff_code");
$sql_obj->prepare_sql_addfield("label1", "name_staff");
if ($this->access_staff_ids) {
$sql_obj->prepare_sql_addwhere("id IN (" . format_arraytocommastring($this->access_staff_ids) . ")");
}
$sql_obj->generate_sql();
$structure = form_helper_prepare_dropdownfromdb("employeeid", $sql_obj->string);
// if there is currently no employee set, and there is only one
// employee in the selection box, automatically select it and update
// the session variables.
if (!$this->employeeid && count($structure["values"]) == 1) {
$this->employeeid = $structure["values"][0];
$_SESSION["form"]["timereg"]["employeeid"] = $structure["values"][0];
}
$structure["options"]["autoselect"] = "on";
$structure["options"]["width"] = "600";
$structure["defaultvalue"] = $this->employeeid;
$this->obj_form_employee->add_input($structure);
// hidden values
$structure = NULL;
$structure["fieldname"] = "page";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $_GET["page"];
$this->obj_form_employee->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "date";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $this->date;
$this->obj_form_employee->add_input($structure);
// submit button
$structure = NULL;
$structure["fieldname"] = "submit";
$structure["type"] = "submit";
$structure["defaultvalue"] = "Display";
$this->obj_form_employee->add_input($structure);
if ($this->employeeid) {
/*
DRAW DAY TABLE
We need to display a table showing all time booked for the currently
selected day.
*/
// establish a new table object
$this->obj_table_day = new table();
$this->obj_table_day->language = $_SESSION["user"]["lang"];
$this->obj_table_day->tablename = "timereg_table";
// define all the columns and structure
$this->obj_table_day->add_column("standard", "name_project", "CONCAT_WS(' -- ', projects.code_project, projects.name_project)");
$this->obj_table_day->add_column("standard", "name_phase", "project_phases.name_phase");
$this->obj_table_day->add_column("hourmins", "time_booked", "timereg.time_booked");
$this->obj_table_day->add_column("standard", "description", "timereg.description");
// defaults
$this->obj_table_day->columns = array("name_project", "name_phase", "description", "time_booked");
$this->obj_table_day->columns_order = array("name_project", "name_phase");
// create totals
$this->obj_table_day->total_columns = array("time_booked");
// define SQL
$this->obj_table_day->sql_obj->prepare_sql_settable("timereg");
$this->obj_table_day->sql_obj->prepare_sql_addfield("id", "timereg.id");
$this->obj_table_day->sql_obj->prepare_sql_addjoin("LEFT JOIN project_phases ON timereg.phaseid = project_phases.id");
$this->obj_table_day->sql_obj->prepare_sql_addjoin("LEFT JOIN projects ON project_phases.projectid = projects.id");
$this->obj_table_day->sql_obj->prepare_sql_addwhere("timereg.employeeid = '" . $this->employeeid . "'");
$this->obj_table_day->sql_obj->prepare_sql_addwhere("timereg.date = '" . $this->date . "'");
// execute SQL statement
$this->obj_table_day->generate_sql();
$this->obj_table_day->load_data_sql();
}
}
function load_data_all()
{
log_debug("attributes", "Executing load_data_all()");
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("attributes");
$sql_obj->prepare_sql_addjoin("left join attributes_group on attributes.id_group = attributes_group.id");
$sql_obj->prepare_sql_addfield("attributes.id");
$sql_obj->prepare_sql_addfield("id_owner");
$sql_obj->prepare_sql_addfield("id_group");
$sql_obj->prepare_sql_addfield("group_name");
$sql_obj->prepare_sql_addfield("type");
$sql_obj->prepare_sql_addfield("`key`");
$sql_obj->prepare_sql_addfield("value");
if ($this->id_owner) {
$sql_obj->prepare_sql_addwhere("id_owner='" . $this->id_owner . "'");
}
if ($this->id_group) {
$sql_obj->prepare_sql_addwhere("id_owner='" . $this->id_group . "'");
}
if ($this->type) {
$sql_obj->prepare_sql_addwhere("type='" . $this->type . "'");
}
$sql_obj->generate_sql();
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
$this->data = $sql_obj->data;
return 1;
}
// failure
return 0;
}
