function check_requirements()
{
// verify that the service exists
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id, id_rate_table FROM services WHERE id='" . $this->id . "' LIMIT 1";
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
// verify the rate is valid
if ($sql_obj->data[0]["id_rate_table"]) {
$this->obj_cdr_rate_table->id = $sql_obj->data[0]["id_rate_table"];
if (!$this->obj_cdr_rate_table->verify_id()) {
log_write("error", "page_output", "The requested CDR rate table is invalid, there may be some problems with the information in the database.");
return 0;
}
} else {
log_write("error", "page_output", "You have yet to set a CDR Rate Table for this service to use - please do so using the plan page before attempting to override the rates");
return 0;
}
} else {
log_write("error", "page_output", "The requested service (" . $this->id . ") does not exist - possibly the service has been deleted.");
return 0;
}
unset($sql_obj);
// verify that this is a phone service
if ($this->service_type != ("phone_single" || "phone_trunk" || "phone_tollfree")) {
log_write("error", "page_output", "The requested service is not a phone service.");
return 0;
}
return 1;
}
function check_requirements()
{
// verify that the invoice exists
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id FROM account_ar WHERE id='" . $this->id . "' LIMIT 1";
$sql_obj->execute();
if (!$sql_obj->num_rows()) {
log_write("error", "page_output", "The requested invoice (" . $this->id . ") does not exist - possibly the invoice has been deleted.");
return 0;
}
unset($sql_obj);
// verify that the item id supplied exists and fetch required information
if ($this->itemid) {
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id, type FROM account_items WHERE id='" . $this->itemid . "' AND invoiceid='" . $this->id . "' LIMIT 1";
$sql_obj->execute();
if (!$sql_obj->num_rows()) {
log_write("error", "page_output", "The requested payment/invoice combination does not exist. Are you trying to use a link to a deleted payment?");
return 0;
} else {
$sql_obj->fetch_array();
$this->item_type = $sql_obj->data[0]["type"];
}
}
return 1;
}
function check_requirements()
{
// verify that project exists
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id FROM projects WHERE id='" . $this->id . "' LIMIT 1";
$sql_obj->execute();
if (!$sql_obj->num_rows()) {
log_write("error", "page_output", "The requested project (" . $this->id . ") does not exist - possibly the project has been deleted.");
return 0;
}
unset($sql_obj);
// verify that the time group exists and belongs to this project
if ($this->groupid) {
$sql_obj = new sql_query();
$sql_obj->string = "SELECT projectid, locked FROM time_groups WHERE id='" . $this->groupid . "' LIMIT 1";
$sql_obj->execute();
if (!$sql_obj->num_rows()) {
log_write("error", "page_output", "The requested time group (" . $this->groupid . ") does not exist - possibly the time group has been deleted.");
return 0;
} else {
$sql_obj->fetch_array();
$this->locked = $sql_obj->data[0]["locked"];
if ($sql_obj->data[0]["projectid"] != $this->id) {
log_write("error", "page_output", "The requested time group (" . $this->groupid . ") does not belong to the selected project (" . $this->id . ")");
return 0;
}
}
unset($sql_obj);
}
return 1;
}
function load_data()
{
log_debug("name_server", "Executing load_data()");
$sql_obj = new sql_query();
$sql_obj->string = "SELECT * FROM name_servers WHERE id='" . $this->id . "' LIMIT 1";
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
// set attributes
$this->data = $sql_obj->data[0];
// fetch sync statuses
if ($this->data["server_type"] == "api") {
if (sql_get_singlevalue("SELECT value FROM config WHERE name='SYNC_STATUS_CONFIG'") != $sql_obj->data[0]["api_sync_config"]) {
// out of sync, set to date
$this->data["sync_status_config"] = $sql_obj->data[0]["api_sync_config"];
}
if (time() - $sql_obj->data[0]["api_sync_log"] > 86400) {
// logging hasn't happened for at least 24 hours, flag logging as failed
$this->data["sync_status_log"] = $sql_obj->data[0]["api_sync_log"];
}
} else {
$this->data["sync_status_config"] = time();
$this->data["sync_status_log"] = time();
}
return 1;
}
// failure
return 0;
}
function execute()
{
/*
Define form structure
*/
$this->obj_form = new form_input();
$this->obj_form->formname = "users_permissions_staff";
$this->obj_form->language = $_SESSION["user"]["lang"];
$this->obj_form->action = "user/user-staffaccess-edit-process.php";
$this->obj_form->method = "post";
// run through all the avaliable permissions
$sql_perms_obj = new sql_query();
$sql_perms_obj->string = "SELECT * FROM `permissions_staff`";
$sql_perms_obj->execute();
if ($sql_perms_obj->num_rows()) {
$sql_perms_obj->fetch_array();
foreach ($sql_perms_obj->data as $data_perms) {
// define the checkbox
$structure = NULL;
$structure["fieldname"] = $data_perms["value"];
$structure["type"] = "checkbox";
$structure["options"]["label"] = $data_perms["description"];
// check the database to see if this checkbox is selected
$sql_obj = new sql_query();
$sql_obj->string = "SELECT " . "id " . "FROM `users_permissions_staff` " . "WHERE " . "userid='" . $this->id . "' " . "AND permid='" . $data_perms["id"] . "' " . "AND staffid='" . $this->staffid . "'";
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$structure["defaultvalue"] = "on";
}
// add checkbox
$this->obj_form->add_input($structure);
// add checkbox to subforms
$this->obj_form->subforms["user_permissions_staff"][] = $data_perms["value"];
}
}
// hidden fields
$structure = NULL;
$structure["fieldname"] = "id_user";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $this->id;
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "id_staff";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $this->staffid;
$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
$this->obj_form->subforms["hidden"] = array("id_user", "id_staff");
$this->obj_form->subforms["submit"] = array("submit");
/*
Note: We don't load from error data, since there should never
be any errors when using this form.
*/
}
function list_taxes()
{
log_debug("accounts_taxes_manage_soap", "Executing list_taxes()");
if (user_permissions_get("accounts_taxes_view")) {
// fetch taxes
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id, name_tax, taxrate, chartid, taxnumber, description FROM account_taxes";
$sql_obj->execute();
$return = NULL;
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
// package data into array for passing back to SOAP client
foreach ($sql_obj->data as $data) {
$return_tmp = NULL;
$return_tmp["id"] = $data["id"];
$return_tmp["name_tax"] = $data["name_tax"];
$return_tmp["taxrate"] = $data["taxrate"];
$return_tmp["chartid"] = $data["chartid"];
$return_tmp["chartid_label"] = sql_get_singlevalue("SELECT CONCAT_WS('--', code_chart, description) as value FROM account_charts WHERE id='" . $data["chartid"] . "'");
$return_tmp["taxnumber"] = $data["taxnumber"];
$return_tmp["description"] = $data["description"];
$return[] = $return_tmp;
}
}
return $return;
} else {
throw new SoapFault("Sender", "ACCESS_DENIED");
}
}
function quotes_render_summarybox($id)
{
log_debug("inc_quotes", "quotes_render_summarybox({$id})");
// fetch quote information
$sql_obj = new sql_query();
$sql_obj->string = "SELECT code_quote, amount_total, date_validtill, date_sent, sentmethod FROM account_quotes WHERE id='{$id}' LIMIT 1";
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
if ($sql_obj->data[0]["amount_total"] == 0) {
print "<table width=\"100%\" class=\"table_highlight_important\">";
print "<tr>";
print "<td>";
print "<b>Quote " . $sql_obj->data[0]["code_quote"] . " has no items on it</b>";
print "<p>This quote needs to have some items added to it using the links in the nav menu above.</p>";
print "</td>";
print "</tr>";
print "</table>";
} else {
if (time_date_to_timestamp($sql_obj->data[0]["date_validtill"]) <= time()) {
print "<table width=\"100%\" class=\"table_highlight_important\">";
print "<tr>";
print "<td>";
print "<p><b>Quote " . $sql_obj->data[0]["code_quote"] . " has now expired and is no longer valid.</b></p>";
print "</td>";
print "</tr>";
print "</table>";
} else {
print "<table width=\"100%\" class=\"table_highlight_important\">";
print "<tr>";
print "<td>";
print "<b>Quote " . $sql_obj->data[0]["code_quote"] . " is currently valid.</b>";
print "<table cellpadding=\"4\">";
print "<tr>";
print "<td>Quote Total:</td>";
print "<td>" . format_money($sql_obj->data[0]["amount_total"]) . "</td>";
print "</tr>";
print "<tr>";
print "<td>Valid Until:</td>";
print "<td>" . $sql_obj->data[0]["date_validtill"] . "</td>";
print "</tr>";
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>";
}
}
function execute()
{
/*
Define form structure
*/
$this->obj_form = new form_input();
$this->obj_form->formname = "users_permissions_staff";
$this->obj_form->language = $_SESSION["user"]["lang"];
$this->obj_form->action = "user/user-staffaccess-edit-process.php";
$this->obj_form->method = "post";
// staff member dropdown
$structure = form_helper_prepare_dropdownfromdb("id_staff", "SELECT id, staff_code as label, name_staff as label1 FROM `staff` ORDER BY name_staff");
$this->obj_form->add_input($structure);
$this->obj_form->subforms["user_permissions_selectstaff"] = array("id_staff");
/*
Permissions sub-form
*/
// run through all the avaliable permissions
$sql_perms_obj = new sql_query();
$sql_perms_obj->string = "SELECT * FROM `permissions_staff`";
$sql_perms_obj->execute();
if ($sql_perms_obj->num_rows()) {
$sql_perms_obj->fetch_array();
foreach ($sql_perms_obj->data as $data_perms) {
// define the checkbox
$structure = NULL;
$structure["fieldname"] = $data_perms["value"];
$structure["type"] = "checkbox";
$structure["options"]["label"] = $data_perms["description"];
// add checkbox
$this->obj_form->add_input($structure);
// add checkbox to subforms
$this->obj_form->subforms["user_permissions_staff"][] = $data_perms["value"];
}
}
// hidden fields
$structure = NULL;
$structure["fieldname"] = "id_user";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $this->id;
$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
$this->obj_form->subforms["hidden"] = array("id_user");
$this->obj_form->subforms["submit"] = array("submit");
/*
Note: We don't load from error data, since there should never
be any errors when using this form.
*/
}
function execute()
{
/*
Define form structure
*/
$this->obj_form = new form_input();
$this->obj_form->formname = "user_permissions";
$this->obj_form->language = $_SESSION["user"]["lang"];
$this->obj_form->action = "user/user-permissions-process.php";
$this->obj_form->method = "post";
$sql_perms_obj = new sql_query();
$sql_perms_obj->string = "SELECT * FROM `permissions` ORDER BY value='disabled' DESC, value='admin' DESC, value";
$sql_perms_obj->execute();
$sql_perms_obj->fetch_array();
foreach ($sql_perms_obj->data as $data_perms) {
// define the checkbox
$structure = NULL;
$structure["fieldname"] = $data_perms["value"];
$structure["type"] = "checkbox";
$structure["options"]["label"] = $data_perms["description"];
$structure["options"]["no_translate_fieldname"] = "yes";
// check if the user has this permission
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id FROM `users_permissions` WHERE userid='" . $this->id . "' AND permid='" . $data_perms["id"] . "'";
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$structure["defaultvalue"] = "on";
}
// add checkbox
$this->obj_form->add_input($structure);
// add checkbox to subforms
$this->obj_form->subforms["user_permissions"][] = $data_perms["value"];
}
// user ID (hidden field)
$structure = NULL;
$structure["fieldname"] = "id_user";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $this->id;
$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
$this->obj_form->subforms["hidden"] = array("id_user");
$this->obj_form->subforms["submit"] = array("submit");
/*
Note: We don't load from error data, since there should never
be any errors when using this form.
*/
}
function execute()
{
// make sure tax does not belong to any invoices
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id FROM account_items WHERE type='tax' AND customid='" . $this->id . "'";
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$this->locked = 1;
}
/*
Define form structure
*/
$this->obj_form = new form_input();
$this->obj_form->formname = "tax_delete";
$this->obj_form->language = $_SESSION["user"]["lang"];
$this->obj_form->action = "accounts/taxes/delete-process.php";
$this->obj_form->method = "post";
// general
$structure = NULL;
$structure["fieldname"] = "name_tax";
$structure["type"] = "text";
$this->obj_form->add_input($structure);
// hidden
$structure = NULL;
$structure["fieldname"] = "id_tax";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $this->id;
$this->obj_form->add_input($structure);
// confirm delete
$structure = NULL;
$structure["fieldname"] = "delete_confirm";
$structure["type"] = "checkbox";
$structure["options"]["label"] = "Yes, I wish to delete this tax and realise that once deleted the data can not be recovered.";
$this->obj_form->add_input($structure);
// define submit field
$structure = NULL;
$structure["fieldname"] = "submit";
$structure["type"] = "submit";
$structure["defaultvalue"] = "delete";
$this->obj_form->add_input($structure);
// define subforms
$this->obj_form->subforms["tax_delete"] = array("name_tax");
$this->obj_form->subforms["hidden"] = array("id_tax");
if ($this->locked) {
$this->obj_form->subforms["submit"] = array();
} else {
$this->obj_form->subforms["submit"] = array("delete_confirm", "submit");
}
// fetch the form data
$this->obj_form->sql_query = "SELECT name_tax FROM `account_taxes` WHERE id='" . $this->id . "' LIMIT 1";
$this->obj_form->load_data();
}
function check_requirements()
{
// verify that the quote exists
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id FROM account_quotes WHERE id='" . $this->id . "' LIMIT 1";
$sql_obj->execute();
if (!$sql_obj->num_rows()) {
log_write("error", "page_output", "The requested quote (" . $this->id . ") does not exist - possibly the quote has been deleted or converted into an invoice.");
return 0;
}
unset($sql_obj);
return 1;
}
function check_requirements()
{
// verify that the product exists
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id FROM products WHERE id='" . $this->id . "' LIMIT 1";
$sql_obj->execute();
if (!$sql_obj->num_rows()) {
log_write("error", "page_output", "The requested product (" . $this->id . ") does not exist - possibly the product has been deleted.");
return 0;
}
unset($sql_obj);
return 1;
}
function check_requirements()
{
// verify that the account exists
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id, locked FROM account_gl WHERE id='" . $this->id . "' LIMIT 1";
$sql_obj->execute();
if (!$sql_obj->num_rows()) {
log_write("error", "page_output", "The requested transaction (" . $this->id . ") does not exist - possibly the transaction has been deleted.");
return 0;
} else {
$sql_obj->fetch_array();
$this->locked = $sql_obj->data[0]["locked"];
}
unset($sql_obj);
return 1;
}
function check_requirements()
{
// verify that the service exists
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id FROM services WHERE id='" . $this->id . "' LIMIT 1";
$sql_obj->execute();
if (!$sql_obj->num_rows()) {
log_write("error", "page_output", "The requested service (" . $this->id . ") does not exist - possibly the service has been deleted.");
return 0;
}
unset($sql_obj);
// verify that this is a bundle service
if ($this->service_type != "bundle") {
log_write("error", "page_output", "The requested service is not a bundle service.");
return 0;
}
return 1;
}
function config_generate_uniqueid($config_name, $check_sql)
{
log_debug("inc_misc", "Executing config_generate_uniqueid({$config_name})");
$config_name = strtoupper($config_name);
$returnvalue = 0;
$uniqueid = 0;
// fetch the starting ID from the config DB
$uniqueid = sql_get_singlevalue("SELECT value FROM config WHERE name='{$config_name}'");
if (!$uniqueid) {
die("Unable to fetch {$config_name} value from config database");
}
// first set the uniqueid prefix to an empty string, in case the following tests fail
$uniqueid_prefix = '';
if (!is_numeric($uniqueid)) {
preg_match("/^(\\S*?)([0-9]*)\$/", $uniqueid, $matches);
$uniqueid_prefix = $matches[1];
$uniqueid = (int) $matches[2];
}
if ($check_sql) {
// we will use the supplied SQL query to make sure this value is not currently used
while ($returnvalue == 0) {
$sql_obj = new sql_query();
$sql_obj->string = str_replace("VALUE", $uniqueid_prefix . $uniqueid, $check_sql);
$sql_obj->execute();
if ($sql_obj->num_rows()) {
// the ID has already been used, try incrementing
$uniqueid++;
} else {
// found an avaliable ID
$returnvalue = $uniqueid;
}
}
$returnvalue = $uniqueid_prefix . $returnvalue;
} else {
// conducting no DB checks.
$returnvalue = $uniqueid_prefix . $uniqueid;
}
// update the DB with the new value + 1
$uniqueid++;
$sql_obj = new sql_query();
$sql_obj->string = "UPDATE config SET value='{$uniqueid_prefix}{$uniqueid}' WHERE name='{$config_name}'";
$sql_obj->execute();
return $returnvalue;
}
function set_usage_record($collector, $id_service_customer, $date, $usage1, $usage2 = NULL)
{
log_debug("services_usage", "Executing set_usage_record");
if (user_permissions_get("services_write_usage")) {
// sanitise input
$data["collector"] = @security_script_input_predefined("any", $collector);
$data["id_service_customer"] = @security_script_input_predefined("int", $id_service_customer);
$data["date"] = @security_script_input_predefined("date", $date);
$data["usage1"] = @security_script_input_predefined("int", $usage1);
$data["usage2"] = @security_script_input_predefined("int", $usage2);
foreach (array_keys($data) as $key) {
if ($data[$key] == "error") {
throw new SoapFault("Sender", "INVALID_INPUT");
}
}
/*
Verify that id_service_customer exists - this may seem unnessacary, but should be done
to prevent data being inserted to IDs that don't yet belong - but may do in future.
Would be nasty to have a lot of data sitting in the table waiting for a new customer to
appear whom the ID matches too.
Of course, this check does nothing to prevent data for one customer being accidently filed
against another customer due to an incorrect ID.
*/
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id FROM services_customers WHERE id='" . $data["id_service_customer"] . "' LIMIT 1";
$sql_obj->execute();
if (!$sql_obj->num_rows()) {
throw new SoapFault("Sender", "INVALID_SERVICES_CUSTOMERS_ID");
}
unset($sql_obj);
// add new row to DB
$sql_obj = new sql_query();
$sql_obj->string = "INSERT INTO service_usage_records (" . "id_service_customer, " . "date, " . "usage1, " . "usage2" . ") VALUES (" . "'" . $data["id_service_customer"] . "', " . "'" . $data["date"] . "', " . "'" . $data["usage1"] . "', " . "'" . $data["usage2"] . "'" . ")";
if (!$sql_obj->execute()) {
throw new SoapFault("Sender", "UNEXPECTED_DB_ERROR");
}
return 1;
} else {
throw new SoapFault("Sender", "ACCESS_DENIED");
}
}
function render_html()
{
// Title + Summary
print "<h3>DATABASE BACKUP</h3><br>";
print "<p>This page allows an administrator to perform an export of the entire MySQL database and download it as a file. This feature\n\t\t\tensures that no matter who runs your instance of the Amberdms Billing System, your data can always be retrieved.</p>";
print "<p>The file generated is a standard SQL file compressed with gzip, it can be easily restored using the MySQL command line or\n\t\t\tvia a utility such as phpmyadmin.</p>";
// report on usage
$sql_obj = new sql_query();
$usage = $sql_obj->stats_diskusage();
format_msgbox("info", "<p>Estimated download size: " . format_size_human($usage) . " (before compression)</p>");
// run check for file-system based journal files
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id FROM file_uploads WHERE file_location != 'db' LIMIT 1";
$sql_obj->execute();
if ($sql_obj->num_rows()) {
format_msgbox("important", "<p>Some of the files stored in the journal have been saved to the filesystem rather than the MySQL database. This backup will provide a copy of the database, but you will also need to download the contents of the data/ directory.</p>");
}
// export link
print "<br>";
print "<a class=\"button\" href=\"admin/db_backup-process.php\">Export Database</a>";
}
function get_gl_trans($id)
{
log_debug("gl_manage_soap", "Executing get_gl_trans()");
if (user_permissions_get("accounts_gl_view")) {
$obj_gl = new gl_transaction();
// sanitise input
$obj_gl->id = @security_script_input_predefined("int", $id);
if (!$obj_gl->id || $obj_gl->id == "error") {
throw new SoapFault("Sender", "INVALID_INPUT");
}
// verify that the ID is valid
if (!$obj_gl->verify_id()) {
throw new SoapFault("Sender", "INVALID_ID");
}
// fetch list of all transactions
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id, amount_debit, amount_credit, chartid, source, memo FROM `account_trans` WHERE type='gl' AND customid='" . $obj_gl->id . "'";
$sql_obj->execute();
// package up for sending to the client
$return = NULL;
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
foreach ($sql_obj->data as $data) {
$return_tmp = NULL;
$return_tmp["id"] = $data["id"];
$return_tmp["chartid"] = $data["chartid"];
$return_tmp["chartid_label"] = sql_get_singlevalue("SELECT CONCAT_WS('--', code_chart, description) as value FROM account_charts WHERE id='" . $data["chartid"] . "'");
$return_tmp["debit"] = $data["amount_debit"];
$return_tmp["credit"] = $data["amount_credit"];
$return_tmp["source"] = $data["source"];
$return_tmp["description"] = $data["memo"];
$return[] = $return_tmp;
}
}
return $return;
} else {
throw new SoapFault("Sender", "ACCESS_DENIED");
}
}
function execute()
{
/*
Check if the project can be deleted or not
*/
// see if any time has been booked to any of the phases
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id FROM project_phases WHERE projectid='" . $this->id . "'";
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
foreach ($sql_obj->data as $phase_data) {
$sql_phase_obj = new sql_query();
$sql_phase_obj->string = "SELECT id FROM timereg WHERE phaseid='" . $phase_data["id"] . "' LIMIT 1";
$sql_phase_obj->execute();
if ($sql_phase_obj->num_rows()) {
$this->locked = 1;
}
}
}
/*
Define form structure
*/
$this->obj_form = new form_input();
$this->obj_form->formname = "project_delete";
$this->obj_form->language = $_SESSION["user"]["lang"];
$this->obj_form->action = "projects/delete-process.php";
$this->obj_form->method = "post";
// general
$structure = NULL;
$structure["fieldname"] = "name_project";
$structure["type"] = "text";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "code_project";
$structure["type"] = "text";
$this->obj_form->add_input($structure);
// hidden
$structure = NULL;
$structure["fieldname"] = "id_project";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $this->id;
$this->obj_form->add_input($structure);
// confirm delete
$structure = NULL;
$structure["fieldname"] = "delete_confirm";
$structure["type"] = "checkbox";
$structure["options"]["label"] = "Yes, I wish to delete this project and realise that once deleted the data can not be recovered.";
$this->obj_form->add_input($structure);
// submit button
$structure = NULL;
$structure["fieldname"] = "submit";
$structure["type"] = "submit";
$structure["defaultvalue"] = "delete";
$this->obj_form->add_input($structure);
// define subforms
$this->obj_form->subforms["project_delete"] = array("code_project", "name_project");
$this->obj_form->subforms["hidden"] = array("id_project");
if ($this->locked) {
$this->obj_form->subforms["submit"] = array();
} else {
$this->obj_form->subforms["submit"] = array("delete_confirm", "submit");
}
// fetch the form data
$this->obj_form->sql_query = "SELECT * FROM `projects` WHERE id='" . $this->id . "' LIMIT 1";
$this->obj_form->load_data();
}
function action_sync_records()
{
log_write("debug", "cloud_route53", "Executing action_sync_records()");
/*
Amazon has an annoying fashion of merging records when the name/type are the
same, but the contents differ. We need need to apply merging logic here,
otherwise the change will fail due to us trying to create multiple records
that are the same.
This works by us running through the ID lists, generating a hash of the
unique record fields (name, type, etc) with all the IDs that match. We can
then generate a single record with multiple values accurately that can then
be used to compare for diffs.
It's also worth noting that NamedManager and Bind technically allow different
*/
log_write("debug", "cloud_route53", "Applying merge of records to match Route53 record design");
// Current Local Records
$merge_hash_local = array();
$data_records_local = array();
for ($i = 0; $i < count($this->obj_domain->data["records"]); $i++) {
// Of all the subdomain records, we should remove any which belong to nameservers that exist in
// other domain groups, we should exclude it to avoid contaminating across groups.
//
// However if the nameserver does *not* exist in NamedManager, then it must be an
// NS record for an external domain, so we should include it, so that external
// delegation works correctly.
//
// We need to do this per-merge, incase a single subdomain record has both ones
// we need to keep and ones we need to remove.
if ($this->obj_domain->data["records"][$i]["type"] == "NS") {
$obj_ns_sql = new sql_query();
$obj_ns_sql->string = "SELECT id FROM name_servers WHERE server_name='" . $this->obj_domain->data["records"][$i]["content"] . "' LIMIT 1";
$obj_ns_sql->execute();
if ($obj_ns_sql->num_rows()) {
// nameserver exists in other groups, we should exclude this NS record.
continue;
}
}
$merge_hash_local[$this->obj_domain->data["records"][$i]["type"] . "_" . $this->obj_domain->data["records"][$i]["name"]][] = $i;
}
foreach ($merge_hash_local as $ids) {
// For every hash entry, all the record details are the same EXCEPT
// for the contents. Take the values from the first matched record, then
// grab each content value and assume an array record.
$tmp = array();
$id = $ids[0];
// Exclude any NS records for the domain itself (but still include subdomains)
// AWS manages NS records for us, so we don't want to mess with them.
if ($this->obj_domain->data["records"][$id]["type"] == "NS" && $this->obj_domain->data["records"][$id]["name"] == $this->obj_domain->data["domain_name"]) {
continue;
}
if ($this->obj_domain->data["records"][$id]["type"] == "NS" && $this->obj_domain->data["records"][$id]["name"] == "@") {
continue;
}
$tmp["Name"] = $this->obj_domain->data["records"][$id]["name"] . ".";
$tmp["Type"] = $this->obj_domain->data["records"][$id]["type"];
$tmp["TTL"] = $this->obj_domain->data["records"][$id]["ttl"];
$tmp["ResourceRecords"] = array();
foreach ($ids as $id) {
$tmp2 = array();
switch ($tmp["Type"]) {
case "MX":
// MX records are special - need to re-merge priority and content together
$tmp2["Value"] = $this->obj_domain->data["records"][$id]["prio"] . " " . $this->obj_domain->data["records"][$id]["content"];
if (preg_match("/\\./", $tmp2["Value"])) {
// already a FQDN
$tmp2["Value"] .= ".";
} else {
// add local domain to make CNAME FQDN
$tmp2["Value"] .= "." . $this->obj_domain->data["domain_name"] . ".";
}
break;
case "NS":
case "SRV":
case "CNAME":
// ensure the value is a FQDN.
$tmp2["Value"] = $this->obj_domain->data["records"][$id]["content"];
if (preg_match("/\\./", $tmp2["Value"])) {
// already a FQDN
$tmp2["Value"] .= ".";
} else {
// add local domain to make CNAME FQDN
$tmp2["Value"] .= "." . $this->obj_domain->data["domain_name"] . ".";
}
break;
case "PTR":
// These record types will always to point to FQDNs, so need the trailing .
$tmp2["Value"] = $this->obj_domain->data["records"][$id]["content"] . ".";
break;
case "A":
case "AAAA":
case "TXT":
case "SPF":
default:
// Point to IPs or quoted strings. Don't touch.
$tmp2["Value"] = $this->obj_domain->data["records"][$id]["content"];
break;
}
$tmp["ResourceRecords"][] = $tmp2;
}
// Adjust the record name to FQDN - AWS won't accept anything else
if (strpos($tmp["Name"], $this->obj_domain->data["domain_name"]) === FALSE) {
if ($tmp["Name"] == "@.") {
$tmp["Name"] = $this->obj_domain->data["domain_name"] . ".";
} else {
$tmp["Name"] .= $this->obj_domain->data["domain_name"] . ".";
}
}
$data_records_local[] = $tmp;
}
unset($merge_hash_local);
// Existing Route53 Records
$soa_record = array();
// we need to fetch and adjust the SOA record.
$merge_hash_route53 = array();
$data_records_route53 = array();
for ($i = 0; $i < count($this->aws_records); $i++) {
$merge_hash_route53[$this->aws_records[$i]["type"] . "_" . $this->aws_records[$i]["name"]][] = $i;
}
foreach ($merge_hash_route53 as $ids) {
// For every hash entry, all the record details are the same EXCEPT
// for the contents. Take the values from the first matched record, then
// grab each content value and assume an array record.
$tmp = array();
$id = $ids[0];
// Exclude any NS records for the domain itself (but still include subdomains)
// AWS manages NS records for us, so we don't want to mess with them.
if ($this->aws_records[$id]["type"] == "NS" && $this->aws_records[$id]["name"] == $this->obj_domain->data["domain_name"]) {
continue;
}
if ($this->aws_records[$id]["type"] == "NS" && $this->aws_records[$id]["name"] == "@") {
continue;
}
$tmp["Name"] = $this->aws_records[$id]["name"] . ".";
$tmp["Type"] = $this->aws_records[$id]["type"];
$tmp["TTL"] = $this->aws_records[$id]["ttl"];
$tmp["ResourceRecords"] = array();
foreach ($ids as $id) {
$tmp2 = array();
switch ($tmp["Type"]) {
case "MX":
// MX records are special - need to re-merge priority and content together
$tmp2["Value"] = $this->aws_records[$id]["prio"] . " " . $this->aws_records[$id]["content"] . ".";
break;
case "NS":
case "CNAME":
case "SRV":
case "PTR":
// These record types need to point to FQDNs, so need the trailing .
$tmp2["Value"] = $this->aws_records[$id]["content"] . ".";
break;
case "A":
case "AAAA":
case "TXT":
case "SPF":
default:
// Point to IPs or quoted strings. Don't touch.
$tmp2["Value"] = $this->aws_records[$id]["content"];
break;
}
$tmp["ResourceRecords"][] = $tmp2;
}
// Adjust the record name to FQDN - AWS won't accept anything else
if (strpos($tmp["Name"], $this->obj_domain->data["domain_name"])) {
// A record from AWS already includes the domain name... but we already
// strip this at import, which means the AWS record is messed up and includes
// multple domain records (eg something.example.com.example.com) probably
// from a buggy import.
//
// To ensure we can delete and correct this bad record, we append our domain
// that we originally stripped.
$tmp["Name"] .= $this->obj_domain->data["domain_name"] . ".";
} else {
if ($tmp["Name"] == "@.") {
$tmp["Name"] = $this->obj_domain->data["domain_name"] . ".";
} else {
$tmp["Name"] .= $this->obj_domain->data["domain_name"] . ".";
}
}
// Capture a copy of the SOA record so we can update the SOA serial.
if ($this->aws_records[$id]["type"] == "SOA") {
$soa_record = $tmp;
// skip adding this to the change request, we do that manually.
continue;
}
$data_records_route53[] = $tmp;
}
unset($merge_hash_route53);
/*
We now have our two array structures in a format that will suite Route53. We need
to loop through and compare them and generate an array of additions/deletions.
(note that changes are both a deletion and addition).
*/
log_write("debug", "cloud_route53", "Generating Change Batch diff data)");
$ids_local_created = array();
// array of Local IDs to create in Route53
$ids_route53_deleted = array();
// array of Route53 IDs to delete from Route53 (has changed)
$ids_route53_nochange = array();
// records with no change.
for ($il = 0; $il < count($data_records_local); $il++) {
$match = 0;
// check if the local record exists in Route53
for ($ir = 0; $ir < count($data_records_route53); $ir++) {
if ($data_records_route53[$ir]["Type"] == $data_records_local[$il]["Type"] && $data_records_route53[$ir]["Name"] == $data_records_local[$il]["Name"] && $data_records_route53[$ir]["ResourceRecords"] == $data_records_local[$il]["ResourceRecords"]) {
$match = 1;
$diff = array_diff($data_records_route53[$ir], $data_records_local[$il]);
if (!empty($diff)) {
// the record exists, but has changed some of it's attributes
$ids_route53_deleted[] = $ir;
// delete existing record.
$ids_local_created[] = $il;
// create a new proper record.
} else {
// record exists and is unchanged - nothing needs to be done for this one.
$ids_route53_nochange[] = $ir;
}
}
}
if (!$match) {
// Doesn't exist in Route53 - mark it as a new addition.
$ids_local_created[] = $il;
}
}
// Each unmatched ID is a record that doesn't exist in Route53, we need to
// add each of these records to the deletion list.
for ($i = 0; $i < count($data_records_route53); $i++) {
if (!in_array($i, $ids_route53_deleted) && !in_array($i, $ids_route53_nochange)) {
// Record exists in Route53, but isn't in the unchanged list or the
// deleted list when compared with local NamedManager. This record must
// have been deleted, we now need to purge it.
$ids_route53_deleted[] = $i;
}
}
/*
Verify that there has actually been a change!
*/
if (empty($ids_route53_deleted) && empty($ids_local_created)) {
log_write("debug", "cloud_route53", "No changes between Route53 and NamedManager");
return 2;
} else {
log_write("debug", "cloud_route53", "There are differences between Route53 and NamedManager. Applying...");
}
/*
Now we have the lists of IDs that have been added, deleted or unchanged, we can generate our diff.
*/
$data_change_batch = array();
// Firstly, let's delete the existing SOA and add a new SOA. We need to do
// this first to ensure the delete and creates don't get split across requests.
$tmp = array();
$tmp["Action"] = "DELETE";
$tmp["ResourceRecordSet"] = $soa_record;
$data_change_batch[] = $tmp;
$tmp = array();
$tmp["Action"] = "CREATE";
$tmp["ResourceRecordSet"] = $soa_record;
$soa_record_tmp = explode(' ', $soa_record["ResourceRecords"][0]["Value"]);
$soa_record_tmp[2] = $this->obj_domain->data["soa_serial"];
$tmp["ResourceRecordSet"]["ResourceRecords"][0]["Value"] = implode(' ', $soa_record_tmp);
$data_change_batch[] = $tmp;
// Add all the other records to the change batch job. We do DELETE first,
// since a CREATE will always fail if the old record hasn't been DELETEd
// first.
foreach ($ids_route53_deleted as $id) {
$tmp = array();
$tmp["Action"] = "DELETE";
$tmp["ResourceRecordSet"] = $data_records_route53[$id];
$data_change_batch[] = $tmp;
}
foreach ($ids_local_created as $id) {
$tmp = array();
$tmp["Action"] = "CREATE";
$tmp["ResourceRecordSet"] = $data_records_local[$id];
$data_change_batch[] = $tmp;
}
unset($data_records_local);
unset($data_records_route53);
/*
Submit Change Batch request to Amazon
*/
log_write("debug", "cloud_route53", "Submitting batch change request to Amazon AWS.");
// We need to handle Amazon limits - slice up the change into no more than 100
// requests per call, with DELETES before CREATES
$max_records = count(array_keys($data_change_batch));
$chunk = ceil($max_records / 100);
$count = 1;
foreach (array_chunk($data_change_batch, 100) as $data_change_batch2) {
log_write("debug", "cloud_route53", "Uploading batch change request number {$count}/{$chunk}");
try {
$change = NULL;
$change["HostedZoneId"] = $this->aws_zone_id;
$change["ChangeBatch"]["Changes"] = $data_change_batch2;
// If debugging, uncommenting the following will dump out the entire change
// object before it's sent to AWS.
// print_r($change);
// die("debug");
$query = $this->obj_route53->changeResourceRecordSets($change);
if (empty($query["ChangeInfo"])) {
log_write("error", "cloud_route53", "Invalid change response returned from Route53");
$this->changelog->log_post('server', "An error occured updating domain \"" . $this->obj_domain->data["domain_name"] . "\" in Route53");
return 0;
} else {
log_write("debug", "cloud_route53", "Batch change submitted successfully.");
}
} catch (Route53Exception $e) {
log_write("error", "process", "A failure occured whilst trying to submit a batch change from AWS/Route53.");
log_write("error", "process", "Failure returned: " . $e->getExceptionCode() . "");
$this->changelog->log_post('server', "An error occured updating domain \"" . $this->obj_domain->data["domain_name"] . "\" in Route53");
return 0;
}
}
// end of foreach loop for uploads
return 1;
}
function execute()
{
if ($this->mode == 1) {
/*
MODE 1: INITAL FILE UPLOAD
*/
$this->obj_form = new form_input();
$this->obj_form->formname = "domain_import";
$this->obj_form->language = $_SESSION["user"]["lang"];
$this->obj_form->action = "domains/import-process.php";
$this->obj_form->method = "post";
// import type
$structure = NULL;
$structure["fieldname"] = "import_upload_type";
$structure["type"] = "radio";
$structure["values"] = array("file_bind_8");
$structure["defaultvalue"] = "file_bind_8";
$this->obj_form->add_input($structure);
// file upload
$structure = NULL;
$structure["fieldname"] = "import_upload_file";
$structure["type"] = "file";
$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);
$structure = NULL;
$structure["fieldname"] = "mode";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $this->mode;
$this->obj_form->add_input($structure);
// define subforms
$this->obj_form->subforms["upload"] = array("import_upload_type", "import_upload_file");
$this->obj_form->subforms["hidden"] = array("mode");
$this->obj_form->subforms["submit"] = array("submit");
// import data
if (error_check()) {
$this->obj_form->load_data_error();
}
} elseif ($this->mode == 2) {
/*
MODE 2: DOMAIN RECORD ASSIGNMENT
Information from the imported zone file under mode 1 has been converted and loaded into
the session variables, from here we can now enter all that information into a form and
the user can correct/complete before we push through to the database.
We also need to address issues like over-writing of existing domains here.
*/
/*
Define form structure
*/
$this->obj_form = new form_input();
$this->obj_form->formname = "domain_import";
$this->obj_form->language = $_SESSION["user"]["lang"];
$this->obj_form->action = "domains/import-process.php";
$this->obj_form->method = "post";
/*
General domain & SOA information
*/
$structure = NULL;
$structure["fieldname"] = "domain_type";
$structure["type"] = "radio";
$structure["values"] = array("domain_standard", "domain_reverse_ipv4", "domain_reverse_ipv6");
$structure["options"]["req"] = "yes";
$structure["defaultvalue"] = "domain_standard";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "domain_name";
$structure["type"] = "input";
$structure["options"]["req"] = "yes";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "ipv4_help";
$structure["type"] = "text";
$structure["options"]["req"] = "yes";
$structure["defaultvalue"] = "help_ipv4_help";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "ipv6_help";
$structure["type"] = "text";
$structure["options"]["req"] = "yes";
$structure["defaultvalue"] = "help_ipv6_help";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "ipv4_network";
$structure["type"] = "input";
$structure["options"]["help"] = "eg: 192.168.0.0";
$structure["options"]["label"] = " /24";
$structure["options"]["req"] = "yes";
$this->obj_form->add_input($structure);
/*
$structure = NULL;
$structure["fieldname"] = "ipv4_subnet";
$structure["type"] = "radio";
$structure["values"] = array("24", "16", "8");
$structure["options"]["req"] = "yes";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "ipv4_autofill";
$structure["type"] = "checkbox";
$structure["options"]["label"] = lang_trans("help_ipv4_autofill");
$structure["options"]["req"] = "yes";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "ipv4_autofill_domain";
$structure["type"] = "input";
$structure["options"]["help"] = "eg: static.example.com";
$structure["options"]["req"] = "yes";
$this->obj_form->add_input($structure);
*/
$structure = NULL;
$structure["fieldname"] = "ipv6_network";
$structure["type"] = "input";
$structure["options"]["help"] = "eg: 2001:db8::/48";
$structure["options"]["label"] = " always include a /cidr value (/1 though to /64)";
$structure["options"]["req"] = "yes";
$this->obj_form->add_input($structure);
/*
$structure = NULL;
$structure["fieldname"] = "ipv6_autofill";
$structure["type"] = "checkbox";
$structure["options"]["label"] = lang_trans("help_ipv6_autofill");
$structure["options"]["req"] = "yes";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "ipv6_autofill_forward";
$structure["type"] = "checkbox";
$structure["options"]["label"] = lang_trans("help_ipv6_autofill_forward");
$structure["options"]["req"] = "yes";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "ipv6_autofill_reverse_from_forward";
$structure["type"] = "checkbox";
$structure["options"]["label"] = lang_trans("help_ipv6_autofill_reverse_from_forward");
$structure["options"]["req"] = "yes";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "ipv6_autofill_domain";
$structure["type"] = "input";
$structure["options"]["help"] = "eg: static.example.com";
$structure["options"]["req"] = "yes";
$this->obj_form->add_input($structure);
*/
$this->obj_form->add_action("domain_type", "default", "domain_name", "show");
$this->obj_form->add_action("domain_type", "default", "ipv4_help", "hide");
$this->obj_form->add_action("domain_type", "default", "ipv4_network", "hide");
// $this->obj_form->add_action("domain_type", "default", "ipv4_subnet", "hide");
// $this->obj_form->add_action("domain_type", "default", "ipv4_autofill", "hide");
$this->obj_form->add_action("domain_type", "default", "ipv6_help", "hide");
$this->obj_form->add_action("domain_type", "default", "ipv6_network", "hide");
// $this->obj_form->add_action("domain_type", "default", "ipv4_subnet", "hide");
// $this->obj_form->add_action("domain_type", "default", "ipv4_autofill", "hide");
$this->obj_form->add_action("domain_type", "domain_standard", "domain_name", "show");
$this->obj_form->add_action("domain_type", "domain_reverse_ipv4", "domain_name", "hide");
$this->obj_form->add_action("domain_type", "domain_reverse_ipv4", "ipv4_help", "show");
$this->obj_form->add_action("domain_type", "domain_reverse_ipv4", "ipv4_network", "show");
// $this->obj_form->add_action("domain_type", "domain_reverse_ipv4", "ipv4_subnet", "show");
// $this->obj_form->add_action("domain_type", "domain_reverse_ipv4", "ipv4_autofill", "show");
$this->obj_form->add_action("domain_type", "domain_reverse_ipv4", "ipv6_help", "hide");
$this->obj_form->add_action("domain_type", "domain_reverse_ipv4", "ipv6_network", "hide");
// $this->obj_form->add_action("domain_type", "domain_reverse_ipv4", "ipv6_subnet", "hide");
// $this->obj_form->add_action("domain_type", "domain_reverse_ipv4", "ipv6_autofill", "hide");
$this->obj_form->add_action("domain_type", "domain_reverse_ipv6", "domain_name", "hide");
$this->obj_form->add_action("domain_type", "domain_reverse_ipv6", "ipv4_help", "hide");
$this->obj_form->add_action("domain_type", "domain_reverse_ipv6", "ipv4_network", "hide");
// $this->obj_form->add_action("domain_type", "domain_reverse_ipv6", "ipv4_subnet", "hide");
// $this->obj_form->add_action("domain_type", "domain_reverse_ipv6", "ipv4_autofill", "hide");
$this->obj_form->add_action("domain_type", "domain_reverse_ipv6", "ipv6_help", "show");
$this->obj_form->add_action("domain_type", "domain_reverse_ipv6", "ipv6_network", "show");
// $this->obj_form->add_action("domain_type", "domain_reverse_ipv6", "ipv6_subnet", "hide");
// $this->obj_form->add_action("domain_type", "domain_reverse_ipv6", "ipv6_autofill", "hide");
// $this->obj_form->add_action("ipv4_autofill", "default", "ipv4_autofill_domain", "hide");
// $this->obj_form->add_action("ipv4_autofill", "1", "ipv4_autofill_domain", "show");
$structure = NULL;
$structure["fieldname"] = "domain_description";
$structure["type"] = "textarea";
$this->obj_form->add_input($structure);
// SOA configuration
$structure = NULL;
$structure["fieldname"] = "soa_hostmaster";
$structure["type"] = "input";
$structure["options"]["req"] = "yes";
$structure["defaultvalue"] = $GLOBALS["config"]["DEFAULT_HOSTMASTER"];
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "soa_serial";
$structure["type"] = "input";
$structure["options"]["req"] = "yes";
$structure["defaultvalue"] = date("Ymd") . "01";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "soa_refresh";
$structure["type"] = "input";
$structure["options"]["req"] = "yes";
$structure["defaultvalue"] = "21600";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "soa_retry";
$structure["type"] = "input";
$structure["options"]["req"] = "yes";
$structure["defaultvalue"] = "3600";
$this->obj_form->add_input($structure);
$structure["fieldname"] = "soa_expire";
$structure["type"] = "input";
$structure["options"]["req"] = "yes";
$structure["defaultvalue"] = "604800";
$this->obj_form->add_input($structure);
$structure["fieldname"] = "soa_default_ttl";
$structure["type"] = "input";
$structure["options"]["req"] = "yes";
$structure["defaultvalue"] = $GLOBALS["config"]["DEFAULT_TTL_SOA"];
$this->obj_form->add_input($structure);
// define main domain subforms
$this->obj_form->subforms["domain_details"] = array("domain_type", "domain_name", "ipv4_help", "ipv4_network", "ipv6_help", "ipv6_network", "domain_description");
$this->obj_form->subforms["domain_soa"] = array("soa_hostmaster", "soa_serial", "soa_refresh", "soa_retry", "soa_expire", "soa_default_ttl");
/*
Imported Records
The record import logic is not as advanced as the regular record handling
page, it's primarily intended to display the import and allow correction
before submission.
For more advanced configuration and addition of rows, the user should
import the domain and then adjust like normal.
*/
// subform header
$this->obj_form->subforms["domain_records"] = array("record_import_guide");
$structure = NULL;
$structure["fieldname"] = "record_import_guide";
$structure["type"] = "message";
$structure["defaultvalue"] = "<p>" . lang_trans("record_import_guide") . "</p>";
$this->obj_form->add_input($structure);
if (empty($_SESSION["error"]["num_records"])) {
// no records returned
$structure = NULL;
$structure["fieldname"] = "record_import_notice";
$structure["type"] = "message";
$structure["defaultvalue"] = "<p>" . lang_trans("records_not_imported") . "</p>";
$structure["options"]["css_row_class"] = "table_highlight_important";
$this->obj_form->add_input($structure);
$this->obj_form->subforms["domain_records"][] = "record_import_notice";
} else {
// headers
$this->obj_form->subforms["domain_records"][] = "record_header";
$this->obj_form->subforms_grouped["domain_records"]["record_header"][] = "record_header_type";
$this->obj_form->subforms_grouped["domain_records"]["record_header"][] = "record_header_ttl";
$this->obj_form->subforms_grouped["domain_records"]["record_header"][] = "record_header_prio";
$this->obj_form->subforms_grouped["domain_records"]["record_header"][] = "record_header_name";
$this->obj_form->subforms_grouped["domain_records"]["record_header"][] = "record_header_content";
$this->obj_form->subforms_grouped["domain_records"]["record_header"][] = "record_header_import";
$structure = NULL;
$structure["fieldname"] = "record_header_type";
$structure["type"] = "text";
$structure["defaultvalue"] = lang_trans("record_header_type");
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "record_header_ttl";
$structure["type"] = "text";
$structure["defaultvalue"] = lang_trans("record_header_ttl");
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "record_header_prio";
$structure["type"] = "text";
$structure["defaultvalue"] = lang_trans("record_header_prio");
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "record_header_name";
$structure["type"] = "text";
$structure["defaultvalue"] = lang_trans("record_header_name");
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "record_header_content";
$structure["type"] = "text";
$structure["defaultvalue"] = lang_trans("record_header_content");
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "record_header_import";
$structure["type"] = "text";
$structure["defaultvalue"] = lang_trans("record_header_import");
$this->obj_form->add_input($structure);
// draw pre-defined nameserver records
$obj_sql = new sql_query();
$obj_sql->string = "SELECT server_name FROM name_servers";
$obj_sql->execute();
if ($obj_sql->num_rows()) {
$obj_sql->fetch_array();
$i = 0;
foreach ($obj_sql->data as $data_ns) {
$i++;
// record form items
$structure = NULL;
$structure["fieldname"] = "ns_" . $i . "_type";
$structure["type"] = "text";
$structure["defaultvalue"] = "NS";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "ns_" . $i . "_ttl";
$structure["type"] = "text";
$structure["defaultvalue"] = $GLOBALS["config"]["DEFAULT_TTL_NS"];
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "ns_" . $i . "_prio";
$structure["type"] = "text";
$structure["defaultvalue"] = "";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "ns_" . $i . "_name";
$structure["type"] = "text";
$structure["defaultvalue"] = "@";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "ns_" . $i . "_content";
$structure["type"] = "text";
$structure["defaultvalue"] = $data_ns["server_name"];
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "ns_" . $i . "_import";
$structure["type"] = "checkbox";
$structure["defaultvalue"] = "on";
$structure["options"]["disabled"] = "yes";
$structure["options"]["label"] = "Import";
$this->obj_form->add_input($structure);
// domain records
$this->obj_form->subforms["domain_records"][] = "ns_" . $i;
$this->obj_form->subforms_grouped["domain_records"]["ns_" . $i][] = "ns_" . $i . "_type";
$this->obj_form->subforms_grouped["domain_records"]["ns_" . $i][] = "ns_" . $i . "_ttl";
$this->obj_form->subforms_grouped["domain_records"]["ns_" . $i][] = "ns_" . $i . "_prio";
$this->obj_form->subforms_grouped["domain_records"]["ns_" . $i][] = "ns_" . $i . "_name";
$this->obj_form->subforms_grouped["domain_records"]["ns_" . $i][] = "ns_" . $i . "_content";
$this->obj_form->subforms_grouped["domain_records"]["ns_" . $i][] = "ns_" . $i . "_import";
}
}
// end of pre-defined nameserver loop
// loop through imported records and create form structure
for ($i = 0; $i < $_SESSION["error"]["num_records"]; $i++) {
$record = $_SESSION["error"]["records"][$i];
// record form items
$structure = form_helper_prepare_dropdownfromdb("record_" . $i . "_type", "SELECT type as label, type as id FROM `dns_record_types` WHERE type!='SOA'");
$structure["options"]["width"] = "100";
$structure["defaultvalue"] = $record["type"];
$this->obj_form->add_input($structure);
if (!$record["ttl"]) {
$record["ttl"] = $GLOBALS["config"]["DEFAULT_TTL_OTHER"];
}
$structure = NULL;
$structure["fieldname"] = "record_" . $i . "_ttl";
$structure["type"] = "input";
$structure["options"]["width"] = "100";
$structure["defaultvalue"] = $record["ttl"];
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "record_" . $i . "_prio";
$structure["type"] = "input";
$structure["options"]["width"] = "100";
$structure["defaultvalue"] = $record["prio"];
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "record_" . $i . "_name";
$structure["type"] = "input";
$structure["defaultvalue"] = $record["name"];
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "record_" . $i . "_content";
$structure["type"] = "input";
$structure["defaultvalue"] = $record["content"];
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "record_" . $i . "_import";
$structure["type"] = "checkbox";
$structure["defaultvalue"] = "on";
$structure["options"]["label"] = "Import";
$this->obj_form->add_input($structure);
// domain records
$this->obj_form->subforms["domain_records"][] = "record_" . $i;
$this->obj_form->subforms_grouped["domain_records"]["record_" . $i][] = "record_" . $i . "_type";
$this->obj_form->subforms_grouped["domain_records"]["record_" . $i][] = "record_" . $i . "_ttl";
$this->obj_form->subforms_grouped["domain_records"]["record_" . $i][] = "record_" . $i . "_prio";
$this->obj_form->subforms_grouped["domain_records"]["record_" . $i][] = "record_" . $i . "_name";
$this->obj_form->subforms_grouped["domain_records"]["record_" . $i][] = "record_" . $i . "_content";
$this->obj_form->subforms_grouped["domain_records"]["record_" . $i][] = "record_" . $i . "_import";
}
}
/*
Unmatched Lines Report
Sadly it's not always possible to import *every* line of ever zone file out there - the styles can vary
by far too much to match at times.
We have a section of the form to display the records which do not match so that users are notified and thus
able to make corrections if needed.
*/
// subform header
$this->obj_form->subforms["unmatched_import"] = array("unmatched_import_help", "unmatched_import_notice");
$structure = NULL;
$structure["fieldname"] = "unmatched_import_help";
$structure["type"] = "message";
$structure["defaultvalue"] = "<p>" . lang_trans("unmatched_import_help") . "</p>";
$this->obj_form->add_input($structure);
if (empty($_SESSION["error"]["unmatched"])) {
// no unmatched rows
$structure = NULL;
$structure["fieldname"] = "unmatched_import_notice";
$structure["type"] = "message";
$structure["defaultvalue"] = "<p>" . lang_trans("import_notice_no_unmatched_rows") . "</p>";
$structure["options"]["css_row_class"] = "table_highlight_open";
$this->obj_form->add_input($structure);
} else {
// import notice
$structure = NULL;
$structure["fieldname"] = "unmatched_import_notice";
$structure["type"] = "message";
$structure["defaultvalue"] = "<p>" . lang_trans("import_notice_unmatched_rows") . "</p>";
$structure["options"]["css_row_class"] = "table_highlight_important";
$this->obj_form->add_input($structure);
// add all the unmatched rows
for ($i = 0; $i < count($_SESSION["error"]["unmatched"]); $i++) {
$this->obj_form->subforms["unmatched_import"][] = "unmatched_row_{$i}";
$structure = NULL;
$structure["fieldname"] = "unmatched_row_{$i}";
$structure["type"] = "message";
$structure["defaultvalue"] = "\"" . $_SESSION["error"]["unmatched"][$i] . "\"";
$this->obj_form->add_input($structure);
}
}
// end of unmatched lines loop
/*
Submission
*/
// submit section
$structure = NULL;
$structure["fieldname"] = "submit";
$structure["type"] = "submit";
$structure["defaultvalue"] = "Save Changes";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "mode";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $this->mode;
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "num_records";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $_SESSION["error"]["num_records"];
$this->obj_form->add_input($structure);
// define submit subforms
$this->obj_form->subforms["hidden"] = array("mode", "num_records");
$this->obj_form->subforms["submit"] = array("submit");
// import data
// if (error_check())
// {
// $_SESSION["error"]["form"]["domain_import"] = "error";
// $this->obj_form->load_data_error();
// }
foreach (array_keys($this->obj_form->structure) as $fieldname) {
if (isset($_SESSION["error"][$fieldname])) {
$this->obj_form->structure[$fieldname]["defaultvalue"] = stripslashes($_SESSION["error"][$fieldname]);
}
}
}
// end of mode
}
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 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 invoice_form_export_process($type, $returnpage_error, $returnpage_success)
{
log_debug("inc_invoices_forms", "Executing invoice_form_export_process({$type}, {$returnpage_error}, {$returnpage_success})");
/*
Start the invoice
*/
$invoice = new invoice();
$invoice->type = $type;
/*
Fetch all form data
*/
// get the ID for an edit
$invoice->id = @security_form_input_predefined("int", "id_invoice", 1, "");
// general details
$data["formname"] = @security_form_input_predefined("any", "formname", 1, "");
if ($data["formname"] == "invoice_export_email") {
// send email
$data["sender"] = @security_form_input_predefined("any", "sender", 1, "");
$data["subject"] = @security_form_input_predefined("any", "subject", 1, "");
$data["email_to"] = @security_form_input_predefined("multiple_email", "email_to", 1, "");
$data["email_cc"] = @security_form_input_predefined("multiple_email", "email_cc", 0, "");
$data["email_bcc"] = @security_form_input_predefined("multiple_email", "email_bcc", 0, "");
$data["message"] = @security_form_input_predefined("any", "email_message", 1, "");
// check if email sending is permitted
if (sql_get_singlevalue("SELECT value FROM config WHERE name='EMAIL_ENABLE'") != "enabled") {
log_write("error", "inc_invoices_process", "Sorry, the ability to email invoices has been disabled. Please contact your system administrator if you require this feature to be enabled.");
}
} else {
// PDF download
$data["invoice_mark_as_sent"] = @security_form_input_predefined("any", "invoice_mark_as_sent", 0, "");
}
// make sure that the invoice exists
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id FROM `account_" . $invoice->type . "` WHERE id='" . $invoice->id . "'";
$sql_obj->execute();
if (!$sql_obj->num_rows()) {
$_SESSION["error"]["message"][] = "The invoice you have attempted to edit - " . $invoice->id . " - does not exist in this system.";
}
//// ERROR CHECKING ///////////////////////
/// if there was an error, go back to the entry page
if (!empty($_SESSION["error"]["message"])) {
header("Location: ../../index.php?page={$returnpage_error}&id=" . $invoice->id . "");
exit(0);
} else {
if ($data["formname"] == "invoice_export_email") {
/*
Generate a PDF of the invoice and email it to the customer
*/
// stripslashes from the variables - by default all input variables are quoted for security reasons but
// we don't want this going through to the email.
$data["subject"] = stripslashes($data["subject"]);
$data["message"] = stripslashes($data["message"]);
// send email
$invoice->load_data();
$invoice->email_invoice($data["sender"], $data["email_to"], $data["email_cc"], $data["email_bcc"], $data["subject"], $data["message"]);
$_SESSION["notification"]["message"][] = "Email sent successfully.";
} else {
/*
Mark invoice as being sent if user requests it
*/
if ($data["invoice_mark_as_sent"]) {
$sql_obj = new sql_query();
$sql_obj->string = "UPDATE account_" . $invoice->type . " SET date_sent='" . date("Y-m-d") . "', sentmethod='manual' WHERE id='" . $invoice->id . "'";
$sql_obj->execute();
}
/*
Provide PDF to user's browser
*/
// generate PDF
$invoice->load_data();
$invoice->generate_pdf();
// PDF headers
if ($type == "quotes") {
$filename = "/tmp/quote_" . $invoice->data["code_quote"] . ".pdf";
} else {
$filename = "/tmp/invoice_" . $invoice->data["code_invoice"] . ".pdf";
}
// required for IE, otherwise Content-disposition is ignored
if (ini_get('zlib.output_compression')) {
ini_set('zlib.output_compression', 'Off');
}
header("Pragma: public");
// required
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private", false);
// required for certain browsers
header("Content-Type: application/pdf");
header("Content-Disposition: attachment; filename=\"" . basename($filename) . "\";");
header("Content-Transfer-Encoding: binary");
// output the PDF
print $invoice->obj_pdf->output;
exit(0);
}
// display updated details
header("Location: ../../index.php?page={$returnpage_success}&id=" . $invoice->id . "");
exit(0);
}
// end if passed tests
}
function action_update_ledger()
{
log_debug("invoice_items", "Executing action_update_ledger()");
/*
Start SQL Transaction
*/
$sql_obj = new sql_query();
$sql_obj->trans_begin();
// fetch key information from invoice
$sql_inv_obj = new sql_query();
$sql_inv_obj->string = "SELECT id, dest_account, date_trans FROM account_" . $this->type_invoice . " WHERE id='" . $this->id_invoice . "' LIMIT 1";
$sql_inv_obj->execute();
$sql_inv_obj->fetch_array();
// remove all the old ledger entries belonging to this invoice
$sql_obj->string = "DELETE FROM `account_trans` WHERE customid='" . $this->id_invoice . "' AND (type='" . $this->type_invoice . "' || type='" . $this->type_invoice . "_pay' || type='" . $this->type_invoice . "_tax')";
$sql_obj->execute();
/*
PROCESS NON-PAYMENT ITEMS
For all normal items, we want to aggregate the totals per chart then add ledger entries
per-invoice, not per-item.
Then we create the following in the ledger:
AR INVOICES
* A single debit from the AR account
* A single credit to each different account for the items.
AP INVOICES
* A single credit to the AP account
* A single debit to each different account for the items
Payment items need to be handled differently - see code further down.
*/
// add up the total for the AR entry
$amount = 0;
// Fetch totals per chart from the items table.
$sql_obj = new sql_query();
$sql_obj->string = "SELECT chartid, type, SUM(amount) as amount FROM `account_items` WHERE invoicetype='" . $this->type_invoice . "' AND invoiceid='" . $this->id_invoice . "' AND type!='payment' GROUP BY chartid";
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
foreach ($sql_obj->data as $item_data) {
// set trans type
if ($item_data["type"] == "tax") {
$trans_type = $this->type_invoice . "_tax";
} else {
$trans_type = $this->type_invoice;
}
// create ledger entry for this account
switch ($this->type_invoice) {
case "ar_credit":
ledger_trans_add("debit", $trans_type, $this->id_invoice, $sql_inv_obj->data[0]["date_trans"], $item_data["chartid"], $item_data["amount"], "", "");
break;
case "ap_credit":
ledger_trans_add("credit", $trans_type, $this->id_invoice, $sql_inv_obj->data[0]["date_trans"], $item_data["chartid"], $item_data["amount"], "", "");
break;
case "ap":
ledger_trans_add("debit", $trans_type, $this->id_invoice, $sql_inv_obj->data[0]["date_trans"], $item_data["chartid"], $item_data["amount"], "", "");
break;
case "ar":
default:
ledger_trans_add("credit", $trans_type, $this->id_invoice, $sql_inv_obj->data[0]["date_trans"], $item_data["chartid"], $item_data["amount"], "", "");
break;
}
// add up the total for the AR entry.
$amount += $item_data["amount"];
}
switch ($this->type_invoice) {
case "ap":
// create credit from AP account
ledger_trans_add("credit", $this->type_invoice, $this->id_invoice, $sql_inv_obj->data[0]["date_trans"], $sql_inv_obj->data[0]["dest_account"], $amount, "", "");
break;
case "ap_credit":
// create debit to AP account
ledger_trans_add("debit", $this->type_invoice, $this->id_invoice, $sql_inv_obj->data[0]["date_trans"], $sql_inv_obj->data[0]["dest_account"], $amount, "", "");
break;
case "ar_credit":
// create credit from AR account
ledger_trans_add("credit", $this->type_invoice, $this->id_invoice, $sql_inv_obj->data[0]["date_trans"], $sql_inv_obj->data[0]["dest_account"], $amount, "", "");
break;
case "ar":
default:
// create debit to AR account
ledger_trans_add("debit", $this->type_invoice, $this->id_invoice, $sql_inv_obj->data[0]["date_trans"], $sql_inv_obj->data[0]["dest_account"], $amount, "", "");
break;
}
}
/*
PROCESS PAYMENT ITEMS
Payment entries are different to other items, in that we need to add stand alone
entries for each payment item, since payments can be made on different dates, so therefore
can not be aggregated.
*/
// run though each payment item
$sql_item_obj = new sql_query();
$sql_item_obj->string = "SELECT id, chartid, amount, description FROM `account_items` WHERE invoicetype='" . $this->type_invoice . "' AND invoiceid='" . $this->id_invoice . "' AND type='payment'";
$sql_item_obj->execute();
if ($sql_item_obj->num_rows()) {
$sql_item_obj->fetch_array();
foreach ($sql_item_obj->data as $data) {
// fetch information from options
$sql_option_obj = new sql_query();
$sql_option_obj->string = "SELECT option_name, option_value FROM account_items_options WHERE itemid='" . $data["id"] . "'";
$sql_option_obj->execute();
$sql_option_obj->fetch_array();
foreach ($sql_option_obj->data as $option_data) {
if ($option_data["option_name"] == "SOURCE") {
$data["source"] = $option_data["option_value"];
}
if ($option_data["option_name"] == "DATE_TRANS") {
$data["date_trans"] = $option_data["option_value"];
}
if ($option_data["option_name"] == "CREDIT") {
$data["credit"] = $option_data["option_value"];
}
}
// addslashes to memo & source fields - since we have pulled data from the DB, that data could
// contains quotation marks or other unacceptable input, so we must process it
$data["description"] = addslashes($data["description"]);
$data["source"] = addslashes($data["source"]);
if ($this->type_invoice == "ap") {
// we need to credit the destination account for the payment to come from and debit the AP account
ledger_trans_add("credit", $this->type_invoice . "_pay", $this->id_invoice, $data["date_trans"], $data["chartid"], $data["amount"], $data["source"], $data["description"]);
ledger_trans_add("debit", $this->type_invoice . "_pay", $this->id_invoice, $data["date_trans"], $sql_inv_obj->data[0]["dest_account"], $data["amount"], $data["source"], $data["description"]);
// if a credit, we need to subtract from vendor credit pool
if (!empty($data["credit"])) {
$id_vendor = sql_get_singlevalue("SELECT vendorid as value FROM account_ar WHERE id='" . $this->id_invoice . "' LIMIT 1");
$id_employee = sql_get_singlevalue("SELECT employeeid as value FROM account_ar WHERE id='" . $this->id_invoice . "' LIMIT 1");
$sql_obj->string = "DELETE FROM vendors_credits WHERE id_vendor='" . $id_vendor . "' AND type='payment' AND id_custom='" . $data["id"] . "' LIMIT 1";
$sql_obj->execute();
$sql_obj->string = "INSERT INTO vendors_credits (date_trans, type, amount_total, id_custom, id_employee, id_vendor, description) VALUES ('" . $data["date_trans"] . "', 'payment', '-" . $data["amount"] . "', '" . $data["id"] . "', '" . $id_employee . "', '" . $id_vendor . "', '" . $data["description"] . "')";
$sql_obj->execute();
}
} else {
// we need to debit the destination account for the payment to go into and credit the AR account
ledger_trans_add("debit", $this->type_invoice . "_pay", $this->id_invoice, $data["date_trans"], $data["chartid"], $data["amount"], $data["source"], $data["description"]);
ledger_trans_add("credit", $this->type_invoice . "_pay", $this->id_invoice, $data["date_trans"], $sql_inv_obj->data[0]["dest_account"], $data["amount"], $data["source"], $data["description"]);
// if a credit, we need to subtract from customer credit pool
if (!empty($data["credit"])) {
$id_customer = sql_get_singlevalue("SELECT customerid as value FROM account_ar WHERE id='" . $this->id_invoice . "' LIMIT 1");
$id_employee = sql_get_singlevalue("SELECT employeeid as value FROM account_ar WHERE id='" . $this->id_invoice . "' LIMIT 1");
$sql_obj->string = "DELETE FROM customers_credits WHERE id_customer='" . $id_customer . "' AND type='payment' AND id_custom='" . $data["id"] . "' LIMIT 1";
$sql_obj->execute();
$sql_obj->string = "INSERT INTO customers_credits (date_trans, type, amount_total, id_custom, id_employee, id_customer, description) VALUES ('" . $data["date_trans"] . "', 'payment', '-" . $data["amount"] . "', '" . $data["id"] . "', '" . $id_employee . "', '" . $id_customer . "', '" . $data["description"] . "')";
$sql_obj->execute();
}
}
}
}
/*
Commit
*/
$sql_obj = new sql_query();
if (error_check()) {
$sql_obj->trans_rollback();
log_write("error", "invoice_items", "An error occured whilst attempting to update ledger for invoice. No changes have been made.");
return 0;
} else {
$sql_obj->trans_commit();
return 1;
}
}
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()
{
/*
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"]);
}
}
// includes
require "../../include/config.php";
require "../../include/amberphplib/main.php";
if (user_permissions_get('accounts_quotes_write')) {
/////////////////////////
// start the journal processing
$journal = new journal_process();
$journal->prepare_set_journalname("account_quotes");
// import form data
$journal->process_form_input();
//// ERROR CHECKING ///////////////////////
// make sure the quote ID submitted really exists
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id FROM account_quotes WHERE id='" . $journal->structure["customid"] . "'";
$sql_obj->execute();
if (!$sql_obj->num_rows()) {
$_SESSION["error"]["message"][] = "Unable to find requested quote/transaction to modify journal for.";
}
/// if there was an error, go back to the entry page
if ($_SESSION["error"]["message"]) {
$_SESSION["error"]["form"]["journal_edit"] = "failed";
header("Location: ../../index.php?page=accounts/quotes/journal.php&id=" . $journal->structure["customid"] . "&journalid=" . $journal->structure["id"] . "&action=" . $journal->structure["action"] . "");
exit(0);
} else {
if ($journal->structure["action"] == "delete") {
$journal->action_delete();
} else {
// update or create
$journal->action_update();
}
// display updated details
function execute()
{
// define customer list table
$this->obj_table_list = new table();
$this->obj_table_list->language = $_SESSION["user"]["lang"];
$this->obj_table_list->tablename = "customer_list_billing";
// define all the columns and structure
$this->obj_table_list->add_column("standard", "code_customer", "");
$this->obj_table_list->add_column("standard", "name_customer", "");
$this->obj_table_list->add_column("standard", "billing_direct_debit", "");
$this->obj_table_list->add_column("money", "balance_owed", "NONE");
// defaults
$this->obj_table_list->columns = array("code_customer", "name_customer", 'billing_direct_debit', 'balance_owed');
$this->obj_table_list->columns_order = array("name_customer");
$this->obj_table_list->columns_order_options = array("code_customer", "name_customer", "name_contact", "contact_phone", "contact_mobile", "contact_email", "contact_fax", "date_start", "date_end", "tax_number", "address1_city", "address1_state", "address1_country");
// define SQL structure
$this->obj_table_list->sql_obj->prepare_sql_settable("customers");
$this->obj_table_list->sql_obj->prepare_sql_addfield("id", "");
/*
// define SQL structure
$this->obj_table->sql_obj->prepare_sql_settable("account_ar");
$this->obj_table->sql_obj->prepare_sql_addfield("id", "account_ar.id");
$this->obj_table->sql_obj->prepare_sql_addjoin("LEFT JOIN customers ON customers.id = account_ar.customerid");
$this->obj_table->sql_obj->prepare_sql_addjoin("LEFT JOIN staff ON staff.id = account_ar.employeeid");
*/
// acceptable filter options
$structure = NULL;
$structure["fieldname"] = "date_start";
$structure["type"] = "date";
$structure["sql"] = "date_start >= 'value'";
$this->obj_table_list->add_filter($structure);
$structure = NULL;
$structure["fieldname"] = "date_end";
$structure["type"] = "date";
$structure["sql"] = "date_end <= 'value' AND date_end != '0000-00-00'";
$this->obj_table_list->add_filter($structure);
/*
$structure = NULL;
$structure["fieldname"] = "searchbox";
$structure["type"] = "input";
$structure["sql"] = "(code_customer LIKE '%value%' OR name_customer LIKE '%value%')";
$this->obj_table_list->add_filter($structure);
*/
$structure = NULL;
$structure["fieldname"] = "billing_method";
$structure["type"] = "checkbox";
$structure["options"]["label"] = "Billing method Direct Debit";
$structure["defaultvalue"] = "1";
$structure["sql"] = "billing_method = 'direct debit'";
$this->obj_table_list->add_filter($structure);
// load settings from options form
$this->obj_table_list->load_options_form();
// fetch all the customer information
$this->obj_table_list->generate_sql();
$this->obj_table_list->load_data_sql();
// handle balance owed
if (in_array('balance_owed', $this->obj_table_list->columns)) {
$obj_balance_owed_sql = new sql_query();
$obj_balance_owed_sql->string = "SELECT customerid, sum(bal) AS balance_owed FROM (\n\t\t\t\tSELECT ar.customerid, sum(ar.amount_total - ar.amount_paid) as bal \n\t\t\t\tFROM account_ar AS ar \n\t\t\t\tWHERE 1 GROUP BY ar.customerid\n\t\t\t\tUNION\n\t\t\t\tSELECT arc.customerid, sum(arc.amount_total) as bal\n\t\t\t\tFROM account_ar_credit AS arc\n\t\t\t\tWHERE 1 GROUP BY arc.customerid\n\t\t\t\t) as tbl GROUP by customerid";
$obj_balance_owed_sql->execute();
if ($obj_balance_owed_sql->num_rows()) {
$obj_balance_owed_sql->fetch_array();
foreach ($obj_balance_owed_sql->data as $data_balance_owed) {
$map_balance_owed[$data_balance_owed['customerid']] = $data_balance_owed['balance_owed'];
}
}
// replace with 0.00 or the calculated balance value
for ($i = 0; $i < $this->obj_table_list->data_num_rows; $i++) {
$this->obj_table_list->data[$i]["balance_owed"] = "0.00";
if (isset($map_balance_owed[$this->obj_table_list->data[$i]['id']])) {
$this->obj_table_list->data[$i]["balance_owed"] = $map_balance_owed[$this->obj_table_list->data[$i]['id']];
}
// we dont want 0 balance (or credit) records here
if ($this->obj_table_list->data[$i]["balance_owed"] <= 0) {
unset($this->obj_table_list->data[$i]);
}
}
// re index after the potential unsets
$this->obj_table_list->data = @array_values($this->obj_table_list->data);
$this->obj_table_list->data_num_rows = count($this->obj_table_list->data);
unset($map_balance_owed);
unset($obj_balance_owed_sql);
}
}
function service_invoices_generate($customerid = NULL)
{
log_debug("inc_services_invoicegen", "Executing service_invoices_generate({$customerid})");
/*
Invoice Report Statistics
*/
$invoice_stats = array();
$invoice_stats["time_start"] = time();
$invoice_stats["total"] = 0;
$invoice_stats["total_failed"] = 0;
/*
Run through all the customers
*/
$sql_customers_obj = new sql_query();
$sql_customers_obj->string = "SELECT id, code_customer, name_customer FROM customers";
if ($customerid) {
$sql_customers_obj->string .= " WHERE id='{$customerid}' LIMIT 1";
}
$sql_customers_obj->execute();
if ($sql_customers_obj->num_rows()) {
$sql_customers_obj->fetch_array();
foreach ($sql_customers_obj->data as $customer_data) {
/*
Fetch all periods belonging to this customer which need to be billed.
*/
$sql_periods_obj = new sql_query();
$sql_periods_obj->string = "SELECT " . "services_customers_periods.id, " . "services_customers_periods.rebill, " . "services_customers_periods.invoiceid, " . "services_customers_periods.invoiceid_usage, " . "services_customers_periods.date_start, " . "services_customers_periods.date_end, " . "services_customers.date_period_first, " . "services_customers.date_period_next, " . "services_customers.date_period_last, " . "services_customers.id as id_service_customer, " . "services_customers.serviceid " . "FROM services_customers_periods " . "LEFT JOIN services_customers ON services_customers.id = services_customers_periods.id_service_customer " . "WHERE " . "services_customers.customerid='" . $customer_data["id"] . "' " . "AND (invoiceid = '0' OR rebill = '1')" . "AND date_billed <= '" . date("Y-m-d") . "'";
$sql_periods_obj->execute();
if ($sql_periods_obj->num_rows()) {
$sql_periods_obj->fetch_array();
/*
BILL CUSTOMER
This customer has at least one service that needs to be billed. We need to create
a new invoice, and then process each service, adding the services to the invoice as
items.
*/
/*
Start Transaction
(one transaction per invoice)
*/
$sql_obj = new sql_query();
$sql_obj->trans_begin();
/*
Create new invoice
*/
$invoice = new invoice();
$invoice->type = "ar";
$invoice->prepare_code_invoice();
$invoice->data["customerid"] = $customer_data["id"];
$invoice->data["employeeid"] = 1;
// set employee to the default internal user
$invoice->prepare_date_shift();
if (!$invoice->action_create()) {
log_write("error", "services_invoicegen", "Unexpected problem occured whilst attempting to create invoice.");
$sql_obj->trans_rollback();
return 0;
}
$invoiceid = $invoice->id;
$invoicecode = $invoice->data["code_invoice"];
unset($invoice);
/*
Create Service Items
We need to create an item for basic service plan - IE: the regular fixed fee, and another item for any
excess usage.
*/
foreach ($sql_periods_obj->data as $period_data) {
/*
TODO:
We should be able to re-bill usage here when needed with a clever cheat - if we load the periods to be billed,
we can then ignore the plan item, and rebill the usage item.
*/
$period_data["mode"] = "standard";
if ($period_data["rebill"]) {
if (!$period_data["invoiceid_usage"] && $period_data["invoiceid"]) {
// the selected period has been billed, but the usage has been flagged for rebilling - we need to *ignore* the base plan
// item and only bill for the usage range.
$period_data["mode"] = "rebill_usage";
}
}
// fetch service details
$obj_service = new service_bundle();
$obj_service->option_type = "customer";
$obj_service->option_type_id = $period_data["id_service_customer"];
if (!$obj_service->verify_id_options()) {
log_write("error", "customers_services", "Unable to verify service ID of " . $period_data["id_service_customer"] . " as being valid.");
return 0;
}
$obj_service->load_data();
$obj_service->load_data_options();
// ratio is used to adjust prices for partial periods
$ratio = 1;
if ($obj_service->data["billing_mode_string"] == "monthend" || $obj_service->data["billing_mode_string"] == "monthadvance" || $obj_service->data["billing_mode_string"] == "monthtelco") {
log_debug("services_invoicegen", "Invoice bills by month date");
/*
Handle monthly billing
Normally, monthly billing is easy, however the very first billing period is special, as it may span a more than 1 month, or
consist of less than the full month, depending on the operational mode.
SERVICE_PARTPERIOD_MODE == seporate
If a service is started on 2008-01-09, the end date of the billing period will be 2008-01-31, which is less
than one month - 22 days.
We can calculate with:
( standard_cost / normal_month_num_days ) * num_days_in_partial_month == total_amount
SERVICE_PARTPERIOD_MODE == merge
If a service is started on 2008-01-09, the end of the billing period will be 2008-02-29, which is 1 month + 21 day.
We can calculate with:
( standard_cost / normal_month_num_days ) * num_days_in_partial_month == extra_amount
total_amount = (extra_amount + normal_amount)
Note: we do not increase included units for licenses service types, since these need to remain fixed and do not
scale with the month.
*/
// check if the period start date is not the first of the month - this means that the period
// is an inital partial period.
if (time_calculate_daynum($period_data["date_start"]) != "01") {
// very first billing month
log_write("debug", "services_invoicegen", "First billing month for this service, adjusting pricing to suit days.");
// figure out normal period length - rememeber, whilst service may be configured to align monthly, it needs to
// handle pricing calculations for variations such as month, year, etc.
// get the number of days of a regular period - this correctly handles, month, year, etc
// 1. generate the next period dates, this will be of regular length
// 2. calculate number of days
$tmp_dates = service_period_dates_generate($period_data["date_period_next"], $obj_service->data["billing_cycle_string"], $obj_service->data["billing_mode_string"]);
$regular_period_num_days = sql_get_singlevalue("SELECT DATEDIFF('" . $tmp_dates["end"] . "', '" . $tmp_dates["start"] . "') as value");
// process for short/long periods
//
if ($GLOBALS["config"]["SERVICE_PARTPERIOD_MODE"] == "seporate") {
log_write("debug", "services_invoicegen", "Adjusting for partial month period (SERVICE_PARTPERIOD_MODE == seporate)");
// work out the total number of days in partial month
$short_month_days_total = time_calculate_daynum(time_calculate_monthdate_last($period_data["date_start"]));
$short_month_days_short = $short_month_days_total - time_calculate_daynum($period_data["date_start"]);
log_write("debug", "services_invoicegen", "Short initial billing period of {$short_month_days_short} days");
// calculate correct base fee
$obj_service->data["price"] = $obj_service->data["price"] / $regular_period_num_days * $short_month_days_short;
// calculate ratio
$ratio = $short_month_days_short / $regular_period_num_days;
log_write("debug", "services_invoicegen", "Calculated service bill ratio of {$ratio} to handle short period.");
} else {
log_write("debug", "services_invoicegen", "Adjusting for extended month period (SERVICE_PARTPERIOD_MODE == merge");
// work out the number of days extra
$extra_month_days_total = time_calculate_daynum(time_calculate_monthdate_last($period_data["date_start"]));
$extra_month_days_extra = $extra_month_days_total - time_calculate_daynum($period_data["date_start"]);
log_debug("services_invoicegen", "{$extra_month_days_extra} additional days ontop of started billing period");
// calculate correct base fee
$obj_service->data["price"] = $obj_service->data["price"] / $regular_period_num_days * $extra_month_days_extra + $obj_service->data["price"];
// calculate ratio
$ratio = ($extra_month_days_extra + $extra_month_days_total) / $regular_period_num_days;
log_write("debug", "services_invoicegen", "Calculated service bill ratio of {$ratio} to handle extended period.");
}
}
}
/*
Service Last Period Handling
If this is the last period for a service, it may be of a different link to the regular full period term
- this effects both month and period based services.
*/
if ($period_data["date_period_last"] != "0000-00-00") {
log_write("debug", "services_invoicegen", "Service has a final period date set (" . $period_data["date_period_last"] . ")");
if ($period_data["date_period_last"] == $period_data["date_end"] || time_date_to_timestamp($period_data["date_period_last"]) < time_date_to_timestamp($period_data["date_end"])) {
log_write("debug", "services_invoicegen", "Service is a final period, checking for time adjustment (if any)");
// fetch the regular end date
$orig_dates = service_period_dates_generate($period_data["date_start"], $obj_service->data["billing_cycle_string"], $obj_service->data["billing_mode_string"]);
if ($orig_dates["end"] != $period_data["date_end"]) {
// work out the total number of days
$time = NULL;
$time["start"] = time_date_to_timestamp($period_data["date_start"]);
$time["end_orig"] = time_date_to_timestamp($orig_dates["end"]);
$time["end_new"] = time_date_to_timestamp($period_data["date_end"]);
$time["orig_days"] = sprintf("%d", ($time["end_orig"] - $time["start"]) / 86400);
$time["new_days"] = sprintf("%d", ($time["end_new"] - $time["start"]) / 86400);
log_write("debug", "services_invoicegen", "Short initial billing period of " . $time["new_days"] . " days rather than expected " . $time["orig_days"] . "");
// calculate correct base fee
$obj_service->data["price"] = $obj_service->data["price"] / $time["orig_days"] * $time["new_days"];
// calculate ratio
$ratio = $time["new_days"] / $time["orig_days"];
log_write("debug", "services_invoicegen", "Calculated service bill ratio of {$ratio} to handle short period.");
unset($time);
} else {
log_write("debug", "services_invoicegen", "Final service period is regular size, no adjustment required.");
}
}
}
/*
Service Base Plan Item
Note: There can be a suitation where we shouldn't charge for the base plan
fee, when the period end date is older than the service first start date.
This can happen due to the migration mode, which creates a usage-only period
before the actual plan starts properly.
*/
if (time_date_to_timestamp($period_data["date_period_first"]) < time_date_to_timestamp($period_data["date_end"]) && $period_data["mode"] == "standard") {
// date of the period is newer than the start date
log_write("debug", "inc_service_invoicegen", "Generating base plan item for period " . $period_data["date_start"] . " to " . $period_data["date_end"] . "");
// start the item
$invoice_item = new invoice_items();
$invoice_item->id_invoice = $invoiceid;
$invoice_item->type_invoice = "ar";
$invoice_item->type_item = "service";
$itemdata = array();
// chart ID
$itemdata["chartid"] = $obj_service->data["chartid"];
// service ID
$itemdata["customid"] = $obj_service->id;
// no units apply
$itemdata["units"] = "";
// description
switch ($obj_service->data["typeid_string"]) {
case "phone_single":
$itemdata["description"] = addslashes($obj_service->data["name_service"]) . " from " . $period_data["date_start"] . " to " . $period_data["date_end"] . " (" . $obj_service->data["phone_ddi_single"] . ")";
if ($obj_service->data["description"]) {
$itemdata["description"] .= "\n\n";
$itemdata["description"] .= addslashes($obj_service->data["description"]);
}
break;
default:
$itemdata["description"] = addslashes($obj_service->data["name_service"]) . " from " . $period_data["date_start"] . " to " . $period_data["date_end"];
if ($obj_service->data["description"]) {
$itemdata["description"] .= "\n\n";
$itemdata["description"] .= addslashes($obj_service->data["description"]);
}
break;
}
// handle final periods
if ($period_data["date_period_last"] != "0000-00-00" && $period_data["date_start"] == $period_data["date_end"]) {
$itemdata["description"] = addslashes($obj_service->data["name_service"]) . " service terminated as of " . $period_data["date_end"] . "";
}
// is this service item part of a bundle? if it is, we shouldn't charge for the plan
if (!empty($obj_service->data["id_bundle_component"])) {
// no charge for the base plan, since this is handled by
// the bundle item itself
$itemdata["price"] = "0.00";
$itemdata["quantity"] = 1;
$itemdata["discount"] = "0";
// append description
$itemdata["description"] .= " (part of bundle)";
} else {
// amount
$itemdata["price"] = $obj_service->data["price"];
$itemdata["quantity"] = 1;
$itemdata["discount"] = $obj_service->data["discount"];
}
// create item
$invoice_item->prepare_data($itemdata);
$invoice_item->action_update();
unset($invoice_item);
} else {
log_write("debug", "inc_service_invoicegen", "Skipping base plan item generation, due to migration or rebill mode operation");
}
/*
Service Usage Items
Create another item on the invoice for any usage, provided that the service type is a usage service
*/
$period_usage_data = array();
/*
Depending on the service type, we need to handle the usage period in one of two ways:
1. Invoice usage for the current period that has just ended (periodend/monthend)
2. Invoice usage for the period BEFORE the current period. (periodtelco/monthtelco)
*/
if ($obj_service->data["billing_mode_string"] == "periodtelco" || $obj_service->data["billing_mode_string"] == "monthtelco") {
log_write("debug", "service_invoicegen", "Determining previous period for telco-style usage billing (if any)");
if ($period_data["mode"] == "standard") {
// fetch previous period (if any) - we can determine the previous by subtracting one day from the current period start date.
$tmp_date = explode("-", $period_data["date_start"]);
$period_usage_data["date_end"] = date("Y-m-d", mktime(0, 0, 0, $tmp_date[1], $tmp_date[2] - 1, $tmp_date[0]));
} elseif ($period_data["mode"] == "rebill_usage") {
// use the period as the usage period
log_write("debug", "service_invoicegen", "Using the selected period " . $period_usage_data["date_end"] . " for rebill_usage mode");
$period_usage_data["date_end"] = $period_data["date_end"];
}
// fetch period data to confirm previous period
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id, date_start, date_end FROM services_customers_periods WHERE id_service_customer='" . $period_data["id_service_customer"] . "' AND date_end='" . $period_usage_data["date_end"] . "' LIMIT 1";
$sql_obj->execute();
if ($sql_obj->num_rows()) {
log_write("debug", "service_invoicegen", "Billing for seporate past usage period");
// there is a valid usage period
$period_usage_data["active"] = "yes";
// fetch dates
$sql_obj->fetch_array();
$period_usage_data["id_service_customer"] = $period_data["id_service_customer"];
$period_usage_data["id"] = $sql_obj->data[0]["id"];
$period_usage_data["date_start"] = $sql_obj->data[0]["date_start"];
$period_usage_data["date_end"] = $sql_obj->data[0]["date_end"];
// tracing
log_write("debug", "service_invoicegen", "Current period is (" . $period_data["date_start"] . " to " . $period_data["date_end"] . "), usage period is (" . $period_usage_data["date_start"] . " to " . $period_usage_data["date_end"] . ")");
// reset ratio
$ratio = 1;
/*
TODO: This code is a replicant of the section further above for calculating partial
periods and should really be functionalised as part of service usage continual improvements
*/
// calculate usage abnormal period
if ($obj_service->data["billing_mode_string"] == "monthend" || $obj_service->data["billing_mode_string"] == "monthadvance" || $obj_service->data["billing_mode_string"] == "monthtelco") {
log_debug("services_invoicegen", "Usage period service bills by month date");
if (time_calculate_daynum($period_usage_data["date_start"]) != "01") {
// very first billing month
log_write("debug", "services_invoicegen", "First billing month for this usage period, adjusting pricing to suit days.");
if ($GLOBALS["config"]["SERVICE_PARTPERIOD_MODE"] == "seporate") {
log_write("debug", "services_invoicegen", "Adjusting for partial month period (SERVICE_PARTPERIOD_MODE == seporate)");
// work out the total number of days
$short_month_days_total = time_calculate_daynum(time_calculate_monthdate_last($period_usage_data["date_start"]));
$short_month_days_short = $short_month_days_total - time_calculate_daynum($period_usage_data["date_start"]);
log_write("debug", "services_invoicegen", "Short initial billing period of {$short_month_days_short} days");
// calculate ratio
$ratio = $short_month_days_short / $short_month_days_total;
log_write("debug", "services_invoicegen", "Calculated service bill ratio of {$ratio} to handle short period.");
} else {
log_write("debug", "services_invoicegen", "Adjusting for extended month period (SERVICE_PARTPERIOD_MODE == merge");
// work out the number of days extra
$extra_month_days_total = time_calculate_daynum(time_calculate_monthdate_last($period_usage_data["date_start"]));
$extra_month_days_extra = $extra_month_days_total - time_calculate_daynum($period_usage_data["date_start"]);
log_debug("services_invoicegen", "{$extra_month_days_extra} additional days ontop of started billing period");
// calculate ratio
$ratio = ($extra_month_days_extra + $extra_month_days_total) / $extra_month_days_total;
log_write("debug", "services_invoicegen", "Calculated service bill ratio of {$ratio} to handle extended period.");
}
}
}
// end of calculate usage abnormal period
if ($period_data["date_period_last"] != "0000-00-00") {
log_write("debug", "services_invoicegen", "Service has a final period date set (" . $period_data["date_period_last"] . ")");
if ($period_data["date_period_last"] == $period_usage_data["date_end"] || time_date_to_timestamp($period_data["date_period_last"]) < time_date_to_timestamp($period_usage_data["date_end"])) {
log_write("debug", "services_invoicegen", "Service is a final period, checking for time adjustment (if any)");
// fetch the regular end date
$orig_dates = service_period_dates_generate($period_usage_data["date_start"], $obj_service->data["billing_cycle_string"], $obj_service->data["billing_mode_string"]);
if ($orig_dates["end"] != $period_usage_data["date_end"]) {
// work out the total number of days
$time = NULL;
$time["start"] = time_date_to_timestamp($period_usage_data["date_start"]);
$time["end_orig"] = time_date_to_timestamp($orig_dates["end"]);
$time["end_new"] = time_date_to_timestamp($period_usage_data["date_end"]);
$time["orig_days"] = sprintf("%d", ($time["end_orig"] - $time["start"]) / 86400);
$time["new_days"] = sprintf("%d", ($time["end_new"] - $time["start"]) / 86400);
log_write("debug", "services_invoicegen", "Short initial billing period of " . $time["new_days"] . " days rather than expected " . $time["orig_days"] . "");
// calculate correct base fee
$obj_service->data["price"] = $obj_service->data["price"] / $time["orig_days"] * $time["new_days"];
// calculate ratio
$ratio = $time["new_days"] / $time["orig_days"];
log_write("debug", "services_invoicegen", "Calculated service bill ratio of {$ratio} to handle short period.");
unset($time);
} else {
log_write("debug", "services_invoicegen", "Final service period is regular size, no adjustment required.");
}
}
}
} else {
log_write("debug", "service_invoicegen", "Not billing for past usage, as this appears to be the first plan period so no usage can exist yet");
}
} else {
log_write("debug", "service_invoicegen", "Using plan period as data usage period (" . $period_data["date_start"] . " to " . $period_data["date_end"] . "");
// use current period
$period_usage_data["active"] = "yes";
$period_usage_data["id_service_customer"] = $period_data["id_service_customer"];
$period_usage_data["id"] = $period_data["id"];
$period_usage_data["date_start"] = $period_data["date_start"];
$period_usage_data["date_end"] = $period_data["date_end"];
}
/*
Create usage items if there is a valid usage period
*/
if (!empty($period_usage_data["active"])) {
log_write("debug", "service_invoicegen", "Creating usage items due to active usage period");
switch ($obj_service->data["typeid_string"]) {
case "generic_with_usage":
/*
GENERIC_WITH_USAGE
This service is to be used for any non-traffic, non-time accounting service that needs to track usage. Examples of this
could be counting the number of API requests, size of disk usage on a vhost, etc.
*/
log_write("debug", "service_invoicegen", "Processing usage items for generic_with_usage");
/*
Usage Item Basics
*/
// start the item
$invoice_item = new invoice_items();
$invoice_item->id_invoice = $invoiceid;
$invoice_item->type_invoice = "ar";
$invoice_item->type_item = "service_usage";
$itemdata = array();
$itemdata["chartid"] = $obj_service->data["chartid"];
$itemdata["description"] = addslashes($obj_service->data["name_service"]) . " usage from " . $period_usage_data["date_start"] . " to " . $period_usage_data["date_end"];
$itemdata["customid"] = $obj_service->id;
$itemdata["discount"] = 0;
/*
Adjust Included Units to handle partial or extended periods
*/
if ($ratio != "1") {
$obj_service->data["included_units"] = sprintf("%d", $obj_service->data["included_units"] * $ratio);
}
/*
Fetch usage amount
*/
$usage_obj = new service_usage_generic();
$usage_obj->id_service_customer = $period_usage_data["id_service_customer"];
$usage_obj->date_start = $period_usage_data["date_start"];
$usage_obj->date_end = $period_usage_data["date_end"];
if ($usage_obj->load_data_service()) {
$usage_obj->fetch_usagedata();
if ($usage_obj->data["total_byunits"]) {
$usage = $usage_obj->data["total_byunits"];
} else {
$usage = $usage_obj->data["total"];
}
}
unset($usage_obj);
/*
Charge for the usage in units
*/
$unitname = addslashes($obj_service->data["units"]);
if ($usage > $obj_service->data["included_units"]) {
// there is excess usage that we can bill for.
$usage_excess = $usage - $obj_service->data["included_units"];
// set item attributes
$itemdata["price"] = $obj_service->data["price_extraunits"];
$itemdata["quantity"] = $usage_excess;
$itemdata["units"] = $unitname;
// description example: Used 120 ZZ out of 50 ZZ included in plan
// Excess usage of 70 ZZ charged at $5.00 per ZZ
$itemdata["description"] .= "\nUsed {$usage} {$unitname} out of " . $obj_service->data["included_units"] . " {$unitname} included in plan.";
$itemdata["description"] .= "\nExcess usage of {$usage_excess} {$unitname} charged at " . $obj_service->data["price_extraunits"] . " per {$unitname}.";
} else {
// description example: Used 120 ZZ out of 50 ZZ included in plan
$itemdata["description"] .= "\nUsed {$usage} {$unitname} out of " . $obj_service->data["included_units"] . " {$unitname} included in plan.";
}
/*
Add the item to the invoice
*/
$invoice_item->prepare_data($itemdata);
$invoice_item->action_update();
unset($invoice_item);
/*
Update usage value for period - this summary value is visable on the service
history page and saves having to query lots of records to generate period totals.
*/
$sql_obj = new sql_query();
$sql_obj->string = "UPDATE services_customers_periods SET usage_summary='{$usage}' WHERE id='" . $period_usage_data["id"] . "' LIMIT 1";
$sql_obj->execute();
break;
case "licenses":
/*
LICENSES
No data usage, but there is a quantity field for the customer's account to specify the
quantity of licenses that they have.
*/
log_write("debug", "service_invoicegen", "Processing usage items for licenses");
/*
Usage Item Basics
*/
// start the item
$invoice_item = new invoice_items();
$invoice_item->id_invoice = $invoiceid;
$invoice_item->type_invoice = "ar";
$invoice_item->type_item = "service_usage";
$itemdata = array();
$itemdata["chartid"] = $obj_service->data["chartid"];
$itemdata["description"] = addslashes($obj_service->data["name_service"]) . " usage from " . $period_usage_data["date_start"] . " to " . $period_usage_data["date_end"];
$itemdata["customid"] = $obj_service->id;
$itemdata["discount"] = 0;
/*
Determine Additional Charges
*/
// charge for any extra licenses
if ($obj_service->data["quantity"] > $obj_service->data["included_units"]) {
// there is excess usage that we can bill for.
$licenses_excess = $obj_service->data["quantity"] - $obj_service->data["included_units"];
// set item attributes
$itemdata["price"] = $obj_service->data["price_extraunits"] * $ratio;
$itemdata["quantity"] = $licenses_excess;
$itemdata["units"] = addslashes($obj_service->data["units"]);
// description example: 10 licences included
// 2 additional licenses charged at $24.00 each
$itemdata["description"] .= "\n" . $obj_service->data["included_units"] . " " . $obj_service->data["units"] . " included";
$itemdata["description"] .= "\n{$licenses_excess} additional " . $obj_service->data["units"] . " charged at " . $obj_service->data["price_extraunits"] . " each.";
} else {
// description example: 10 licenses
$itemdata["description"] .= "\n" . $period_data["quantity"] . " " . $period_data["units"] . ".";
}
/*
Add the item to the invoice
*/
$invoice_item->prepare_data($itemdata);
$invoice_item->action_update();
unset($invoice_item);
break;
case "time":
/*
TIME
Simular to the generic usage type, but instead of units being a text field, units
is an ID to the service_units table.
*/
log_write("debug", "service_invoicegen", "Processing usage items for time traffic");
/*
Usage Item Basics
*/
// start the item
$invoice_item = new invoice_items();
$invoice_item->id_invoice = $invoiceid;
$invoice_item->type_invoice = "ar";
$invoice_item->type_item = "service_usage";
$itemdata = array();
$itemdata["chartid"] = $obj_service->data["chartid"];
$itemdata["description"] = addslashes($obj_service->data["name_service"]) . " usage from " . $period_usage_data["date_start"] . " to " . $period_usage_data["date_end"];
$itemdata["customid"] = $obj_service->id;
$itemdata["discount"] = 0;
/*
Adjust Included Units to handle partial or extended periods
*/
if ($ratio != "1") {
$obj_service->data["included_units"] = sprintf("%d", $obj_service->data["included_units"] * $ratio);
}
/*
Fetch usage amount
*/
$usage_obj = new service_usage_generic();
$usage_obj->id_service_customer = $period_usage_data["id_service_customer"];
$usage_obj->date_start = $period_usage_data["date_start"];
$usage_obj->date_end = $period_usage_data["date_end"];
if ($usage_obj->load_data_service()) {
$usage_obj->fetch_usagedata();
if ($usage_obj->data["total_byunits"]) {
$usage = $usage_obj->data["total_byunits"];
} else {
$usage = $usage_obj->data["total"];
}
}
unset($usage_obj);
/*
Charge for the usage in units
*/
$unitname = sql_get_singlevalue("SELECT name as value FROM service_units WHERE id='" . $obj_service->data["units"] . "'");
if ($usage > $obj_service->data["included_units"]) {
// there is excess usage that we can bill for.
$usage_excess = $usage - $obj_service->data["included_units"];
// set item attributes
$itemdata["price"] = $obj_service->data["price_extraunits"];
$itemdata["quantity"] = $usage_excess;
$itemdata["units"] = $unitname;
// description example: Used 120 GB out of 50 GB included in plan
// Excess usage of 70 GB charged at $5.00 per GB
$itemdata["description"] .= "\nUsed {$usage} {$unitname} out of " . $obj_service->data["included_units"] . " {$unitname} included in plan.";
$itemdata["description"] .= "\nExcess usage of {$usage_excess} {$unitname} charged at " . $obj_service->data["price_extraunits"] . " per {$unitname}.";
} else {
// description example: Used 10 out of 50 included units
$itemdata["description"] .= "\nUsed {$usage} {$unitname} out of " . $obj_service->data["included_units"] . " {$unitname} included in plan.";
}
/*
Add the item to the invoice
*/
$invoice_item->prepare_data($itemdata);
$invoice_item->action_update();
unset($invoice_item);
/*
Update usage value for period - this summary value is visable on the service
history page and saves having to query lots of records to generate period totals.
*/
$sql_obj = new sql_query();
$sql_obj->string = "UPDATE services_customers_periods SET usage_summary='{$usage}' WHERE id='" . $period_usage_data["id"] . "' LIMIT 1";
$sql_obj->execute();
break;
case "data_traffic":
/*
DATA_TRAFFIC
We make use of the service_usage_traffic logic to determine the usage across all IP
addressess assigned to this customer and then bill accordingly.
*/
log_write("debug", "service_invoicegen", "Processing usage items for time/data_traffic");
/*
Fetch data traffic plan usage type
*/
$unitname = sql_get_singlevalue("SELECT name as value FROM service_units WHERE id='" . $obj_service->data["units"] . "'");
/*
Fetch usage amount - the returned usage structure will include breakdown of traffic
by configured types.
*/
$usage_obj = new service_usage_traffic();
$usage_obj->id_service_customer = $period_usage_data["id_service_customer"];
$usage_obj->date_start = $period_usage_data["date_start"];
$usage_obj->date_end = $period_usage_data["date_end"];
/*
Fetch Traffic Caps & Details
Returns all the traffic cap types including overrides.
id_type,
id_cap,
type_name,
type_label,
cap_mode,
cap_units_included,
cap_units_price
*/
$traffic_types_obj = new traffic_caps();
$traffic_types_obj->id_service = $obj_service->id;
$traffic_types_obj->id_service_customer = $period_usage_data["id_service_customer"];
$traffic_types_obj->load_data_traffic_caps();
$traffic_types_obj->load_data_override_caps();
/*
Generate Traffic Bills
*/
if ($usage_obj->load_data_service()) {
$usage_obj->fetch_usage_traffic();
foreach ($traffic_types_obj->data as $data_traffic_cap) {
// Adjust Included Units to handle partial or extended periods
if ($ratio != "1") {
$data_traffic_cap["cap_units_included"] = sprintf("%d", $data_traffic_cap["cap_units_included"] * $ratio);
}
// if there is only a single traffic cap, we should make the traffic type name blank, since there's only going to be
// one line item anyway.
if ($traffic_types_obj->data_num_rows == 1) {
$data_traffic_cap["type_name"] = "";
}
// if the any traffic type is zero and there are other traffic types, we should skip it, since most likely
// the other traffic types provide everything expected.
//
if ($traffic_types_obj->data_num_rows > 1 && $data_traffic_cap["type_label"] == "*" && $usage_obj->data["total_byunits"]["*"] == 0) {
continue;
}
// start service item
$invoice_item = new invoice_items();
$invoice_item->id_invoice = $invoiceid;
$invoice_item->type_invoice = "ar";
$invoice_item->type_item = "service_usage";
$itemdata = array();
$itemdata["chartid"] = $obj_service->data["chartid"];
$itemdata["customid"] = $obj_service->id;
// base details
$itemdata["price"] = 0;
$itemdata["quantity"] = 0;
$itemdata["discount"] = 0;
$itemdata["units"] = "";
$itemdata["description"] = addslashes($obj_service->data["name_service"]) . " usage from " . $period_usage_data["date_start"] . " to " . $period_usage_data["date_end"];
if ($data_traffic_cap["cap_mode"] == "unlimited") {
// unlimited data cap, there will never be any excess traffic charges, so the line item
// description should be purely for informative purposes.
$itemdata["description"] .= "\nUnlimited " . addslashes($data_traffic_cap["type_name"]) . " traffic, total of " . $usage_obj->data["total_byunits"][$data_traffic_cap["type_label"]] . " {$unitname} used\n";
} else {
// capped traffic - check for excess changes, otherwise just report on how much traffic
// that the customer used.
// description example: Used 10 GB out of 50 GB included in plan
$itemdata["description"] .= "\nCapped " . addslashes($data_traffic_cap["type_name"]) . " traffic, used " . $usage_obj->data["total_byunits"][$data_traffic_cap["type_label"]] . " {$unitname} out of " . $data_traffic_cap["cap_units_included"] . " {$unitname} in plan.";
// handle excess charges
//
if ($usage_obj->data["total_byunits"][$data_traffic_cap["type_label"]] > $data_traffic_cap["cap_units_included"]) {
// there is excess usage that we can bill for.
$usage_excess = $usage_obj->data["total_byunits"][$data_traffic_cap["type_label"]] - $data_traffic_cap["cap_units_included"];
// set item attributes
$itemdata["price"] = $data_traffic_cap["cap_units_price"];
$itemdata["quantity"] = $usage_excess;
$itemdata["units"] = $unitname;
// description example: Excess usage of 70 GB charged at $5.00 per GB
$itemdata["description"] .= "\nExcess usage of {$usage_excess} {$unitname} charged at " . format_money($data_traffic_cap["cap_units_price"]) . " per {$unitname}.";
}
}
// end of traffic cap mode
// add trunk usage item
//
$invoice_item->prepare_data($itemdata);
$invoice_item->action_update();
unset($invoice_item);
}
}
/*
Update usage value for period - this summary value is visable on the service
history page and saves having to query lots of records to generate period totals.
*/
$sql_obj = new sql_query();
$sql_obj->string = "UPDATE services_customers_periods SET usage_summary='" . $usage_obj->data["total_byunits"]["total"] . "' WHERE id='" . $period_usage_data["id"] . "' LIMIT 1";
$sql_obj->execute();
unset($usage_obj);
unset($traffic_types_obj);
break;
case "phone_single":
case "phone_tollfree":
case "phone_trunk":
/*
PHONE_* SERVICES
The phone services are special and contain multiple usage items, for:
* Additional DDI numbers
* Additional Trunks
There are also multiple items for the call charges, grouped into one
item for each DDI.
*/
log_write("debug", "service_invoicegen", "Processing usage items for phone_single/phone_tollfree/phone_trunk");
// setup usage object
$usage_obj = new service_usage_cdr();
$usage_obj->id_service_customer = $period_usage_data["id_service_customer"];
$usage_obj->date_start = $period_usage_data["date_start"];
$usage_obj->date_end = $period_usage_data["date_end"];
$usage_obj->load_data_service();
/*
1. DDI CHARGES
We need to fetch the total number of DDIs and see if there are any excess
charges due to overage of the allocated amount in the plan.
*/
if ($obj_service->data["typeid_string"] == "phone_trunk" && $period_data["mode"] == "standard") {
// start service item
$invoice_item = new invoice_items();
$invoice_item->id_invoice = $invoiceid;
$invoice_item->type_invoice = "ar";
$invoice_item->type_item = "service_usage";
$itemdata = array();
$itemdata["chartid"] = $obj_service->data["chartid"];
$itemdata["customid"] = $obj_service->id;
$itemdata["discount"] = 0;
// fetch DDI usage
$usage = $usage_obj->load_data_ddi();
// determine excess usage charges
if ($usage > $obj_service->data["phone_ddi_included_units"]) {
// there is excess usage that we can bill for.
$usage_excess = $usage - $obj_service->data["phone_ddi_included_units"];
// set item attributes
$itemdata["price"] = $obj_service->data["phone_ddi_price_extra_units"] * $ratio;
log_write("debug", "DEBUG", "Ratio is {$ratio}, price is " . $itemdata["price"] . "");
$itemdata["quantity"] = $usage_excess;
$itemdata["units"] = "DDIs";
if ($obj_service->data["phone_ddi_included_units"]) {
$itemdata["description"] = $obj_service->data["phone_ddi_included_units"] . "x DDI numbers included in service plan plus additional " . $usage_excess . "x numbers from " . $period_usage_data["date_start"] . " to " . $period_usage_data["date_end"] . "";
} else {
// no included units, we use an alternative string format
$itemdata["description"] = $usage_excess . "x DDI numbers from " . $period_usage_data["date_start"] . " to " . $period_usage_data["date_end"] . "";
}
} else {
// no charge for this item
$itemdata["description"] = $obj_service->data["phone_ddi_included_units"] . "x DDI numbers included in service plan from " . $period_usage_data["date_start"] . " to " . $period_usage_data["date_end"] . "";
}
// add trunk usage item
$invoice_item->prepare_data($itemdata);
$invoice_item->action_update();
unset($invoice_item);
}
/*
2. Trunk Charges
*/
if (($obj_service->data["typeid_string"] == "phone_trunk" || $obj_service->data["typeid_string"] == "phone_tollfree") && $period_data["mode"] == "standard") {
// fetch the number of trunks included in the plan, along with the number provided
// we can then see if there are any excess charges for these
// start service item
$invoice_item = new invoice_items();
$invoice_item->id_invoice = $invoiceid;
$invoice_item->type_invoice = "ar";
$invoice_item->type_item = "service_usage";
$itemdata = array();
$itemdata["chartid"] = $obj_service->data["chartid"];
$itemdata["customid"] = $obj_service->id;
$itemdata["discount"] = 0;
// determine excess usage charges
if ($obj_service->data["phone_trunk_quantity"] > $obj_service->data["phone_trunk_included_units"]) {
// there is excess usage that we can bill for.
$usage_excess = $obj_service->data["phone_trunk_quantity"] - $obj_service->data["phone_trunk_included_units"];
// set item attributes
$itemdata["price"] = $obj_service->data["phone_trunk_price_extra_units"] * $ratio;
$itemdata["quantity"] = $usage_excess;
$itemdata["units"] = "trunks";
if ($obj_service->data["phone_trunk_included_units"]) {
$itemdata["description"] = $obj_service->data["phone_trunk_included_units"] . "x trunks included in service plan plus additional " . $usage_excess . "x trunks from " . $period_usage_data["date_start"] . " to " . $period_usage_data["date_end"] . "";
} else {
// no included trunks, adjust string to suit.
$itemdata["description"] = $usage_excess . "x trunks from " . $period_usage_data["date_start"] . " to " . $period_usage_data["date_end"] . "";
}
} else {
// no charge for this item
$itemdata["description"] = $obj_service->data["phone_trunk_included_units"] . "x trunks included in service plan from " . $period_usage_data["date_start"] . " to " . $period_usage_data["date_end"] . "";
}
// add trunk usage item
$invoice_item->prepare_data($itemdata);
$invoice_item->action_update();
unset($invoice_item);
}
/*
Call Charges
Use CDR usage billing module to handle call charges.
*/
$usage_obj = new service_usage_cdr();
$usage_obj->id_service_customer = $period_usage_data["id_service_customer"];
$usage_obj->date_start = $period_usage_data["date_start"];
$usage_obj->date_end = $period_usage_data["date_end"];
$billgroup_obj = new sql_query();
$billgroup_obj->string = "SELECT id, billgroup_name FROM cdr_rate_billgroups";
$billgroup_obj->execute();
$billgroup_obj->fetch_array();
if ($usage_obj->load_data_service()) {
$usage_obj->fetch_usage_calls();
foreach ($billgroup_obj->data as $data_billgroup) {
foreach ($usage_obj->data_ddi as $ddi) {
if ($usage_obj->data[$ddi][$data_billgroup["id"]]["charges"] > 0) {
// start service item
$invoice_item = new invoice_items();
$invoice_item->id_invoice = $invoiceid;
$invoice_item->type_invoice = "ar";
$invoice_item->type_item = "service_usage";
$itemdata = array();
$itemdata["chartid"] = $obj_service->data["chartid"];
$itemdata["customid"] = $obj_service->id;
// extra service details
$itemdata["id_service_customer"] = $period_usage_data["id_service_customer"];
$itemdata["id_period"] = $period_usage_data["id"];
// determine excess usage charges
$itemdata["discount"] = 0;
$itemdata["price"] = $usage_obj->data[$ddi][$data_billgroup["id"]]["charges"];
$itemdata["quantity"] = "1";
$itemdata["units"] = "";
$itemdata["description"] = $data_billgroup["billgroup_name"] . " call charges for {$ddi} from " . $period_usage_data["date_start"] . " to " . $period_usage_data["date_end"] . "";
$itemdata["cdr_billgroup"] = $data_billgroup["id"];
// add trunk usage item
$invoice_item->prepare_data($itemdata);
$invoice_item->action_update();
unset($invoice_item);
} else {
log_write("debug", "inc_service_invoicegen", "Excluding DDI {$ddi} from " . $data_billgroup["billgroup_name"] . " due to no charges for the current period");
}
}
}
}
unset($usage_obj);
/*
If enabled, generate the CDR output format for the current service usage and attach
it to the invoice via the invoice journal.
This feature can be enabled/disabled on a per-customer per-service basis.
*/
if ($obj_service->data["billing_cdr_csv_output"]) {
log_write("debug", "inc_service_invoicegen", "Generating CDR export file and attaching to invoice journal");
// generate the CSV formatted export.
$cdr_options = array('id_customer' => $customer_data["id"], 'id_service_customer' => $period_usage_data["id_service_customer"], 'period_start' => $period_usage_data["date_start"], 'period_end' => $period_usage_data["date_end"]);
$csv = new cdr_csv($cdr_options);
if (!($cdr_output = $csv->getCSV())) {
log_write("error", "inc_service_invoicegen", "Unable to generate CSV ouput for the configured range");
return 0;
}
// create journal entry
$journal = new journal_process();
$journal->prepare_set_journalname("account_ar");
$journal->prepare_set_customid($invoiceid);
$journal->prepare_set_type("file");
// we use the prefix "SERVICE:" to find the journal at invoice time
$journal->prepare_set_title("SERVICE: Service CDR Export Attachment");
// details can be anything (just a text block)
$data["content"] = NULL;
$data["content"] .= "Automatically exported CDR for service " . addslashes($obj_service->data["name_service"]) . "\n";
$data["content"] .= "\n";
$journal->prepare_set_content($data["content"]);
$journal->action_update();
// create journal entry
$journal->action_lock();
// lock entry to avoid users deleting it or breaking it
// upload file as an attachment for the journal
$file_obj = new file_storage();
$file_obj->data["type"] = "journal";
$file_obj->data["customid"] = $journal->structure["id"];
$file_obj->data["file_name"] = "invoice_" . $invoicecode . "_service_CDR_export.csv";
if (!$file_obj->action_update_var($cdr_output)) {
log_write("error", "inc_service_invoicegen", "Unable to upload export CDR invoice to journal.");
}
unset($csv);
unset($journal);
unset($file_obj);
unset($cdr_output);
}
break;
case "generic_no_usage":
case "bundle":
// nothing todo for these service types
log_write("debug", "service_invoicegen", "Not processing usage, this is a non-usage service type");
break;
default:
// we should always match all service types, even if we don't need to do anything
// in particular for that type.
die("Unable to process unknown service type: " . $obj_service->data["typeid_string"] . "");
break;
}
// end of processing usage
} else {
log_write("debug", "service_invoicegen", "Not billing for current usage, as this appears to be the first plan period so no usage can exist yet");
}
/*
Set invoice ID for period - this prevents the period from being added to
any other invoices and allows users to see which invoice it was billed under
*/
// set for plan period
$sql_obj = new sql_query();
$sql_obj->string = "UPDATE services_customers_periods SET invoiceid='{$invoiceid}', rebill='0' WHERE id='" . $period_data["id"] . "' LIMIT 1";
$sql_obj->execute();
// set for usage period
if (!empty($period_usage_data["active"])) {
$sql_obj = new sql_query();
$sql_obj->string = "UPDATE services_customers_periods SET invoiceid_usage='{$invoiceid}' WHERE id='" . $period_usage_data["id"] . "' LIMIT 1";
$sql_obj->execute();
}
}
// end of processing periods
/*
Only process orders and invoice
summary details if we had no errors above.
*/
if (!error_check()) {
/*
Process any customer orders
*/
if ($GLOBALS["config"]["ORDERS_BILL_ONSERVICE"]) {
log_write("debug", "inc_service_invoicegen", "Checking for customer orders to add to service invoice");
$obj_customer_orders = new customer_orders();
$obj_customer_orders->id = $customer_data["id"];
if ($obj_customer_orders->check_orders_num()) {
log_write("debug", "inc_service_invoicegen", "Order items exist, adding them to service invoice");
$obj_customer_orders->invoice_generate($invoiceid);
}
} else {
log_write("debug", "inc_service_invoicegen", "Not checking for customer orders, ORDERS_BILL_ONSERVICE is disabled currently");
}
/*
Update the invoice details + Ledger
Processes:
- taxes
- ledger
- invoice summary
We use the invoice_items class to perform these tasks, but we don't need
to define an item ID for the functions being used to work.
*/
$invoice = new invoice_items();
$invoice->id_invoice = $invoiceid;
$invoice->type_invoice = "ar";
$invoice->action_update_tax();
$invoice->action_update_ledger();
$invoice->action_update_total();
unset($invoice);
/*
Update period information with invoiceid
*/
$sql_obj = new sql_query();
$sql_obj->string = "UPDATE services_customers_periods SET invoiceid='{$invoiceid}' WHERE id='" . $period_data["id"] . "'";
$sql_obj->execute();
}
// end if error check
/*
Automatic Payments
Makes automatic invoice payments using sources such as customer credit pools, reoccuring credit card transactions
and other sources.
*/
if ($GLOBALS["config"]["ACCOUNTS_AUTOPAY"]) {
log_write("debug", "inc_services_invoicegen", "Autopay Functionality Enabled, running appropiate functions for invoice ID {$invoiceid}");
$obj_autopay = new invoice_autopay();
$obj_autopay->id_invoice = $invoiceid;
$obj_autopay->type_invoice = "ar";
$obj_autopay->autopay();
unset($obj_autopay);
}
/*
Commit
Conduct final error check, before commiting the new invoice and sending the customer an email
if appropiate.
(we obviously don't want to email them if the invoice is getting rolled back!)
*/
$sql_obj = new sql_query();
if (error_check()) {
$sql_obj->trans_rollback();
log_write("error", "inc_services_invoicegen", "An error occured whilst creating service invoice. No changes have been made.");
} else {
$sql_obj->trans_commit();
// invoice creation complete - remove any notifications made by the invoice functions and return
// our own notification
$_SESSION["notification"]["message"] = array();
log_write("notification", "inc_services_invoicegen", "New invoice {$invoicecode} for customer " . $customer_data["code_customer"] . " created");
/*
Send the invoice to the customer as a PDF via email
*/
$emailed = "unsent";
if (sql_get_singlevalue("SELECT value FROM config WHERE name='EMAIL_ENABLE'") == "enabled") {
if (sql_get_singlevalue("SELECT value FROM config WHERE name='ACCOUNTS_INVOICE_AUTOEMAIL'") == "enabled") {
// load completed invoice data
$invoice = new invoice();
$invoice->id = $invoiceid;
$invoice->type = "ar";
$invoice->load_data();
$invoice->load_data_export();
if ($invoice->data["amount_total"] > 0) {
// generate an email
$email = $invoice->generate_email();
// send email
$invoice->email_invoice("system", $email["to"], $email["cc"], $email["bcc"], $email["subject"], $email["message"]);
// complete
log_write("notification", "inc_services_invoicegen", "Invoice {$invoicecode} has been emailed to customer (" . $email["to"] . ")");
} else {
// complete - invoice is for $0, so don't want to email out
log_write("notification", "inc_services_invoicegen", "Invoice {$invoicecode} has not been emailed to the customer due to invoice being for \$0.");
}
$emailed = "emailed - " . $email["to"];
unset($invoice);
}
}
// end if email enabled
}
// end if commit successful
/*
Review Status
Here we need to check whether invoicing succeded/failed for the selected customer and process accordingly - we
also want to re-set the error flag if running a batch mode, to enable other customers to still be invoiced.
*/
if (error_check()) {
// an error occured
$invoice_stats["total_failed"]++;
$invoice_stats["failed"][] = array("code_customer" => $customer_data["code_customer"], "name_customer" => $customer_data["name_customer"], "code_invoice" => $invoicecode);
// clear the error strings if we are processing from CLI this will allow us to continue on
// with additional invoices.
if (!empty($_SESSION["mode"])) {
if ($_SESSION["mode"] == "cli") {
log_write("debug", "inc_services_invoicegen", "Processing from CLI, clearing error flag and continuing with additional invoices");
error_clear();
}
}
} else {
// successful
$invoice_stats["total"]++;
$invoice_stats["generated"][] = array("code_customer" => $customer_data["code_customer"], "name_customer" => $customer_data["name_customer"], "code_invoice" => $invoicecode, "sent" => $emailed);
}
// end of success/stats review
}
// end of processing customers
}
// end of if customers exist
} else {
log_debug("inc_services_invoicegen", "No services assigned to customer {$customerid}");
}
/*
Write Invoicing Report
This only takes place if no customer ID is provided, eg we have run a full automatic invoice generation report.
*/
if ($customerid == NULL) {
log_write("debug", "inc_service_invoicegen", "Generating invoice report for invoice generation process");
/*
Invoice Stats Calculations
*/
$invoice_stats["time"] = time() - $invoice_stats["time_start"];
if ($invoice_stats["time"] == 0) {
$invoice_stats["time"] = 1;
}
/*
Write Invoice Report
*/
$invoice_report = array();
$invoice_report[] = "Complete Invoicing Run Time:\t" . $invoice_stats["time"] . " seconds";
$invoice_report[] = "Total Invoices Generated:\t" . $invoice_stats["total"];
$invoice_report[] = "Failed Invoices/Customers:\t" . $invoice_stats["total_failed"];
if (isset($invoice_stats["generated"])) {
$invoice_report[] = "";
$invoice_report[] = "Customers / Invoices Generated";
foreach (array_keys($invoice_stats["generated"]) as $id) {
$invoice_report[] = " * " . $invoice_stats["generated"][$id]["code_invoice"] . ": " . $invoice_stats["generated"][$id]["code_customer"] . " -- " . $invoice_stats["generated"][$id]["name_customer"];
$invoice_report[] = " [" . $invoice_stats["generated"][$id]["sent"] . "]";
}
$invoice_report[] = "";
}
if (isset($invoice_stats["failed"])) {
$invoice_report[] = "";
$invoice_report[] = "Failed Customers / Invoices";
foreach (array_keys($invoice_stats["failed"]) as $id) {
$invoice_report[] = " * " . $invoice_stats["failed"][$id]["code_customer"] . " -- " . $invoice_stats["failed"][$id]["name_customer"];
}
$invoice_report[] = "";
$invoice_report[] = "Failed invoices will be attempted again on the following billing run.";
$invoice_report[] = "";
}
$invoice_report[] = "Invoicing Run Complete";
// display to debug log
log_write("debug", "inc_service_invoicegen", "----");
foreach ($invoice_report as $line) {
// loop through invoice report lines
log_write("debug", "inc_service_invoicegen", $line);
}
log_write("debug", "inc_service_invoicegen", "----");
// email if appropiate
if ($GLOBALS["config"]["ACCOUNTS_INVOICE_BATCHREPORT"] && ($invoice_stats["total"] > 0 || $invoice_stats["total_failed"] > 0)) {
log_write("debug", "inc_service_invoicegen", "Emailing invoice generation report to " . $GLOBALS["config"]["ACCOUNTS_EMAIL_ADDRESS"] . "");
/*
External dependency of Mail_Mime
*/
if (!@(include_once 'Mail.php')) {
log_write("error", "invoice", "Unable to find Mail module required for sending email");
break;
}
if (!@(include_once 'Mail/mime.php')) {
log_write("error", "invoice", "Unable to find Mail::Mime module required for sending email");
break;
}
/*
Email the Report
*/
$email_sender = $GLOBALS["config"]["ACCOUNTS_EMAIL_ADDRESS"];
$email_to = $GLOBALS["config"]["ACCOUNTS_EMAIL_ADDRESS"];
$email_subject = "Invoice Batch Process Report";
$email_message = $GLOBALS["config"]["COMPANY_NAME"] . "\n\nInvoice Batch Process Report for " . time_format_humandate() . "\n\n";
foreach ($invoice_report as $line) {
$email_message .= $line . "\n";
}
// prepare headers
$mail_headers = array('From' => $email_sender, 'Subject' => $email_subject);
$mail_mime = new Mail_mime("\n");
$mail_mime->setTXTBody($email_message);
$mail_body = $mail_mime->get();
$mail_headers = $mail_mime->headers($mail_headers);
$mail =& Mail::factory('mail');
$status = $mail->send($email_to, $mail_headers, $mail_body);
if (PEAR::isError($status)) {
log_write("error", "inc_service_invoicegen", "An error occured whilst attempting to send the batch report email: " . $status->getMessage() . "");
} else {
log_write("debug", "inc_service_invoicegen", "Successfully sent batch report email.");
}
}
// end if email
}
// end if report
return 1;
}
