function action_create_contact($index)
{
log_debug("inc_vendors", "Executing action_create_contact({$index})");
$sql_obj = new sql_query();
$sql_obj->string = "INSERT INTO vendor_contacts(vendor_id, contact, description, role)\n\t\t\t\t\t\tVALUES ('" . $this->id . "', '" . $this->data["contacts"][$index]["contact"] . "', '" . $this->data["contacts"][$index]["description"] . "', '" . $this->data["contacts"][$index]["role"] . "')";
$sql_obj->execute();
$this->data["contacts"][$index]["contact_id"] = $sql_obj->fetch_insert_id();
for ($i = 0; $i < $this->data["contacts"][$index]["num_records"]; $i++) {
if ($this->data["contacts"][$index]["records"][$i]["delete"] == "false") {
$this->action_create_record($index, $i);
}
}
}
<?php
/*
projects/ajax/insert_new_project.php
Inserts a new project.
*/
require "../../include/config.php";
require "../../include/amberphplib/main.php";
if (user_permissions_get('projects_write')) {
$name_project = @security_script_input_predefined("any", $_GET['name_project']);
$code_project = config_generate_uniqueid("code_project", "SELECT id FROM projects WHERE code_project='VALUE'");
$sql_obj = new sql_query();
$sql_obj->string = "INSERT INTO projects (name_project, code_project) VALUES (\"" . $name_project . "\", \"" . $code_project . "\")";
$sql_obj->execute();
$projectid = $sql_obj->fetch_insert_id();
echo $projectid;
exit(0);
}
$sql_obj->trans_begin();
if ($structure_id > 0) {
$sql_obj->string = "UPDATE `input_structures` SET `name`= '{$structure_name}', `description` = '{$structure_description}' WHERE `id` = {$structure_id};";
if (!$sql_obj->execute()) {
log_debug("csv_import", "Failure to update input_structure entry.");
}
$sql_obj->string = "SELECT `id`, `id_structure` , `field_src`, `field_dest`, `data_format` FROM `input_structure_items` WHERE id_structure='{$structure_id}' ORDER BY `field_src` ASC";
$sql_obj->execute();
$sql_obj->fetch_array();
$existing_input_structure_items = (array) $sql_obj->data;
} else {
$sql_obj->string = "INSERT INTO input_structures ( name, description, type_input, type_file ) VALUES ('" . $structure_name . "', '" . $structure_description . "', 'bank_statement', 'csv');";
if (!$sql_obj->execute()) {
log_debug("csv_import", "Failure whilst creating initial input_structure entry.");
}
$structure_id = $sql_obj->fetch_insert_id();
$existing_input_structure_items = array();
}
// Add or edit the sub items of the input structure.
if ($structure_id > 0) {
$sql_parts = array();
if (count($existing_input_structure_items) > 0) {
$reindexed_input_structure_items = array();
foreach ($existing_input_structure_items as $structure_row) {
$reindexed_input_structure_items[$structure_row['field_src']] = $structure_row;
}
foreach ($new_input_structure as $input_structure_key => $input_structure_data) {
$row = $reindexed_input_structure_items[$input_structure_key];
if ($row['field_dest'] != $input_structure_data['field_dest'] || $input_structure_data['data_format'] != $row['data_format']) {
$sql_obj->string = "UPDATE `input_structure_items` SET `field_src` = '{$input_structure_key}', `field_dest` = '{$input_structure_data['field_dest']}', `data_format` = '{$input_structure_data['data_format']}' WHERE `id` = {$row['id']};";
if (!$sql_obj->execute()) {
function action_create()
{
log_debug("service_groups", "Executing action_create()");
// create a new service group
$sql_obj = new sql_query();
$sql_obj->string = "INSERT INTO `service_groups` (group_name) VALUES ('" . $this->data["group_name"] . "')";
$sql_obj->execute();
$this->id = $sql_obj->fetch_insert_id();
return $this->id;
}
function user_newuser($username, $password, $realname, $email)
{
log_debug("inc_user", "Executing user_newuser({$username}, {$password}, {$realname}, {$email})");
// make sure that the user running this command is an admin
if (user_permissions_get("admin")) {
// verify data
if ($username && $password && $realname && $email) {
// TODO: Fix ACID compliance here
// create the user account
$sql_obj = new sql_query();
$sql_obj->string = "INSERT INTO `users` (username, realname, contact_email) VALUES ('{$username}', '{$realname}', '{$email}')";
$sql_obj->execute();
$userid = $sql_obj->fetch_insert_id();
// set the password
user_changepwd($userid, $password);
return $userid;
}
// if data is valid
}
// if user is an admin
return 0;
}
function service_form_details_process()
{
log_debug("inc_services_process", "Executing service_form_details_process()");
/*
Fetch all form data
*/
// get the ID for an edit
if ($_POST["id_service"]) {
$id = @security_form_input_predefined("int", "id_service", 1, "");
$mode = "edit";
} else {
$id = NULL;
$mode = "add";
}
// general details
$data["name_service"] = @security_form_input_predefined("any", "name_service", 1, "");
$data["chartid"] = @security_form_input_predefined("int", "chartid", 1, "");
$data["id_service_group"] = @security_form_input_predefined("int", "id_service_group", 1, "");
$data["description"] = @security_form_input_predefined("any", "description", 0, "");
$data["upstream_id"] = @security_form_input_predefined("any", "upstream_id", 0, "");
$data["upstream_notes"] = @security_form_input_predefined("any", "upstream_notes", 0, "");
// fetch information for all tax checkboxes from form
$sql_tax_obj = new sql_query();
$sql_tax_obj->string = "SELECT id FROM account_taxes";
$sql_tax_obj->execute();
if ($sql_tax_obj->num_rows()) {
$sql_tax_obj->fetch_array();
foreach ($sql_tax_obj->data as $data_tax) {
$data["tax_" . $data_tax["id"]] = @security_form_input_predefined("any", "tax_" . $data_tax["id"], 0, "");
}
}
// end of loop through taxes
// are we editing an existing service or adding a new one?
if ($id) {
$mode = "edit";
// make sure that the service actually exists
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id, typeid FROM services WHERE id='{$id}'";
$sql_obj->execute();
if (!$sql_obj->num_rows()) {
$_SESSION["error"]["message"][] = "The service you have attempted to edit - {$id} - does not exist in this system.";
} else {
$sql_obj->fetch_array();
$data["typeid_string"] = sql_get_singlevalue("SELECT name as value FROM service_types WHERE id='" . $sql_obj->data[0]["typeid"] . "'");
}
} else {
$mode = "add";
// only fetch the type ID when adding new services
$data["typeid"] = @security_form_input_predefined("int", "typeid", 1, "");
$data["typeid_string"] = sql_get_singlevalue("SELECT name as value FROM service_types WHERE id='" . $data["typeid"] . "' LIMIT 1");
}
// type-specific details
switch ($data["typeid_string"]) {
case "data_traffic":
case "generic_with_usage":
case "phone_single":
case "phone_trunk":
case "phone_tollfree":
case "time":
$data["id_service_group_usage"] = @security_form_input_predefined("int", "id_service_group_usage", 1, "");
break;
case "bundle":
case "generic_no_usage":
case "licenses":
$data["id_service_group_usage"] = 0;
break;
}
//// ERROR CHECKING ///////////////////////
// make sure we don't choose a service name that is already in use
if ($data["code_service"]) {
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id FROM services WHERE name_service='" . $data["name_service"] . "'";
if ($id) {
$sql_obj->string .= " AND id!='{$id}'";
}
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$_SESSION["error"]["message"][] = "This service name is already in use by another service. Please choose a unique name.";
$_SESSION["error"]["name_service-error"] = 1;
}
}
/// if there was an error, go back to the entry page
if ($_SESSION["error"]["message"]) {
$_SESSION["error"]["form"]["service_{$mode}"] = "failed";
if ($mode == "add") {
header("Location: ../index.php?page=services/add.php");
} else {
header("Location: ../index.php?page=services/view.php&id={$id}");
}
exit(0);
} else {
// start transaction
$sql_obj = new sql_query();
$sql_obj->trans_begin();
if ($mode == "add") {
/*
Create new service
*/
$sql_obj->string = "INSERT INTO services (name_service, typeid) VALUES ('" . $data["name_service"] . "', '" . $data["typeid"] . "')";
$sql_obj->execute();
$id = $sql_obj->fetch_insert_id();
}
if ($id) {
/*
Update general service details
*/
$sql_obj->string = "UPDATE services SET " . "name_service='" . $data["name_service"] . "', " . "chartid='" . $data["chartid"] . "', " . "id_service_group='" . $data["id_service_group"] . "', " . "id_service_group_usage='" . $data["id_service_group_usage"] . "', " . "description='" . $data["description"] . "', " . "upstream_id='" . $data["upstream_id"] . "', " . "upstream_notes='" . $data["upstream_notes"] . "' " . "WHERE id='{$id}' LIMIT 1";
$sql_obj->execute();
/*
Update service tax options
*/
// delete existing tax options for this service (if any)
$sql_obj->string = "DELETE FROM services_taxes WHERE serviceid='{$id}'";
$sql_obj->execute();
// fetch list of tax IDs
$sql_tax_obj = new sql_query();
$sql_tax_obj->string = "SELECT id FROM account_taxes";
$sql_tax_obj->execute();
if ($sql_tax_obj->num_rows()) {
$sql_tax_obj->fetch_array();
foreach ($sql_tax_obj->data as $data_tax) {
if ($data["tax_" . $data_tax["id"]] == "on") {
// enable selected tax options
$sql_obj->string = "INSERT INTO services_taxes (serviceid, taxid) VALUES ('{$id}', '" . $data_tax["id"] . "')";
$sql_obj->execute();
}
}
}
// end of loop through taxes
/*
Update Journal
*/
if ($mode == "add") {
journal_quickadd_event("services", $id, "Service successfully created");
} else {
journal_quickadd_event("services", $id, "Service successfully updated");
}
/*
Success! :-)
*/
if (error_check()) {
$sql_obj->trans_rollback();
log_write("error", "process", "An error occured whilst attempting to update the service - no changes have been made.");
if ($mode == "add") {
header("Location: ../index.php?page=services/add.php");
} else {
header("Location: ../index.php?page=services/view.php&id={$id}");
}
} else {
$sql_obj->trans_commit();
if ($mode == "add") {
log_write("notification", "process", "Service successfully created.");
// take user to plan configuration page
header("Location: ../index.php?page=services/plan.php&id={$id}");
exit(0);
} else {
log_write("notification", "process", "Service successfully updated.");
// display updated details
header("Location: ../index.php?page=services/view.php&id={$id}");
exit(0);
}
}
}
// end if ID
}
// end if passed tests
}
function action_create()
{
log_debug("inc_staff", "Executing action_create()");
// create a new employee record
$sql_obj = new sql_query();
$sql_obj->string = "INSERT INTO `staff` (name_staff) VALUES ('" . $this->data["name_staff"] . "')";
$sql_obj->execute();
$this->id = $sql_obj->fetch_insert_id();
return $this->id;
}
function action_create()
{
log_debug("journal_process", "Executing action_create()");
// insert place holder into DB
$sql_obj = new sql_query();
$sql_obj->string = "INSERT INTO `journal` (journalname, type, timestamp) VALUES ('" . $this->journalname . "', '" . $this->structure["type"] . "', '" . $this->structure["timestamp"] . "')";
if ($sql_obj->execute()) {
$this->structure["id"] = $sql_obj->fetch_insert_id();
return $this->structure["id"];
}
return 0;
}
function action_create()
{
log_debug("name_server_group", "Executing action_create()");
// create a new server group
$sql_obj = new sql_query();
$sql_obj->string = "INSERT INTO `name_servers_groups` (group_name, group_description) VALUES ('" . $this->data["group_name"] . "', '" . $this->data["group_description"] . "')";
$sql_obj->execute();
$this->id = $sql_obj->fetch_insert_id();
return $this->id;
}
function action_create()
{
log_debug("inc_charts", "Executing action_create()");
// create a new chart
$sql_obj = new sql_query();
$sql_obj->string = "INSERT INTO `account_charts` (chart_type) VALUES ('" . $this->data["chart_type"] . "')";
$sql_obj->execute();
$this->id = $sql_obj->fetch_insert_id();
return $this->id;
}
function action_create()
{
log_debug("attributes", "Executing action_create()");
// create a new attribute
$sql_obj = new sql_query();
$sql_obj->string = "INSERT INTO `attributes` ( id, type, id_owner, id_group, `key`, value) \n\t\t\t\t\t\tVALUES ('" . $this->id . "', '" . $this->type . "', '" . $this->id_owner . "', '" . $this->id_group . "', '" . $this->data["key"] . "', '" . $this->data["value"] . "')";
$sql_obj->execute();
$this->id = $sql_obj->fetch_insert_id();
// return $this->id;
return 1;
}
function action_create()
{
log_debug("inc_gl", "Executing action_create()");
// create a new transaction
$sql_obj = new sql_query();
$sql_obj->string = "INSERT INTO `account_gl` (description) VALUES ('" . $this->data["description"] . "')";
$sql_obj->execute();
$this->id = $sql_obj->fetch_insert_id();
return $this->id;
}
function action_create()
{
log_debug("name_server", "Executing action_create()");
// create a new server
$sql_obj = new sql_query();
$sql_obj->string = "INSERT INTO `name_servers` (server_name, server_description, api_sync_config, api_sync_log) VALUES ('" . $this->data["server_name"] . "', '', '1', '1')";
$sql_obj->execute();
$this->id = $sql_obj->fetch_insert_id();
// assign the server to the domains
return $this->id;
}
$_SESSION["error"]["form"]["timebilled_view"] = "failed";
header("Location: ../index.php?page=projects/timebilled-edit.php&id={$projectid}&groupid={$groupid}");
exit(0);
} else {
/*
Start Transaction
*/
$sql_obj = new sql_query();
$sql_obj->trans_begin();
/*
Add a new group (if required)
*/
if ($mode == "add") {
$sql_obj->string = "INSERT INTO `time_groups` (projectid) VALUES ('{$projectid}')";
$sql_obj->execute();
$groupid = $sql_obj->fetch_insert_id();
}
if ($groupid) {
/*
Update details
*/
$sql_obj->string = "UPDATE time_groups SET " . "name_group='" . $data["name_group"] . "', " . "customerid='" . $data["customerid"] . "', " . "description='" . $data["description"] . "' " . "WHERE id='{$groupid}'";
$sql_obj->execute();
/*
Update time entries
Here we run though all the entries returned from the database, and
then compare them to the entries that the user has selected.
This will allow us to work out if there have been any changes - eg: changed
from billable to non-billable, or removed from the group.
function action_create()
{
log_write("debug", "file_storage", "Executing action_create()");
$sql_obj = new sql_query();
$sql_obj->string = "INSERT INTO file_uploads (customid, type) VALUES ('" . $this->data["customid"] . "', '" . $this->data["type"] . "')";
if (!$sql_obj->execute()) {
return 0;
}
$this->id = $sql_obj->fetch_insert_id();
return $this->id;
}
function action_create()
{
log_debug("cdr_customer_services_ddi", "Executing action_create()");
$sql_obj = new sql_query();
$sql_obj->string = "INSERT INTO `services_customers_ddi` (id_service_customer) VALUES ('" . $this->id_service_customer . "')";
$sql_obj->execute();
$this->id = $sql_obj->fetch_insert_id();
return $this->id;
}
function quotes_form_convert_process($returnpage_error, $returnpage_success)
{
log_debug("inc_quotes_forms", "Executing quotes_form_convert_process({$mode}, {$returnpage_error}, {$returnpage_success})");
/*
Fetch all form data
*/
$id = @security_form_input_predefined("int", "id_quote", 1, "");
// general data
$data["code_invoice"] = @security_form_input_predefined("any", "code_invoice", 0, "");
$data["code_ordernumber"] = @security_form_input_predefined("any", "code_ordernumber", 0, "");
$data["code_ponumber"] = @security_form_input_predefined("any", "code_ponumber", 0, "");
$data["date_trans"] = @security_form_input_predefined("date", "date_trans", 1, "");
$data["date_due"] = @security_form_input_predefined("date", "date_due", 1, "");
// other
$data["dest_account"] = @security_form_input_predefined("int", "dest_account", 1, "");
//// ERROR CHECKING ///////////////////////
// make sure the quote actually exists, and fetch various fields that we need to create the invoice.
$sql_quote_obj = new sql_query();
$sql_quote_obj->string = "SELECT id, employeeid, customerid, amount_total, amount_tax, amount, notes FROM `account_quotes` WHERE id='{$id}' LIMIT 1";
$sql_quote_obj->execute();
if (!$sql_quote_obj->num_rows()) {
$_SESSION["error"]["message"][] = "The quote you have attempted to edit - {$id} - does not exist in this system.";
} else {
$sql_quote_obj->fetch_array();
}
/// if there was an error, go back to the entry page
if ($_SESSION["error"]["message"]) {
$_SESSION["error"]["form"]["quote_convert"] = "failed";
header("Location: ../../index.php?page={$returnpage_error}&id={$id}");
exit(0);
} else {
/*
Start SQL Transaction
*/
$sql_obj = new sql_query();
$sql_obj->trans_begin();
// make an invoice ID if one is not supplied by the user
if (!$data["code_invoice"]) {
$data["code_invoice"] = config_generate_uniqueid("ACCOUNTS_AR_INVOICENUM", "SELECT id FROM account_ar WHERE code_invoice='VALUE'");
}
/*
Create new invoice
*/
$sql_obj->string = "INSERT INTO `account_ar` (code_invoice, date_create) VALUES ('" . $data["code_invoice"] . "', '" . date("Y-m-d") . "')";
$sql_obj->execute();
$invoiceid = $sql_obj->fetch_insert_id();
if ($invoiceid) {
/*
Update general invoice details
*/
$sql_obj->string = "UPDATE `account_ar` SET " . "customerid='" . $sql_quote_obj->data[0]["customerid"] . "', " . "employeeid='" . $sql_quote_obj->data[0]["employeeid"] . "', " . "notes='" . $sql_quote_obj->data[0]["notes"] . "', " . "code_invoice='" . $data["code_invoice"] . "', " . "code_ordernumber='" . $data["code_ordernumber"] . "', " . "code_ponumber='" . $data["code_ponumber"] . "', " . "date_trans='" . $data["date_trans"] . "', " . "date_due='" . $data["date_due"] . "', " . "dest_account='" . $data["dest_account"] . "', " . "amount='" . $sql_quote_obj->data[0]["amount"] . "', " . "amount_tax='" . $sql_quote_obj->data[0]["amount_tax"] . "', " . "amount_total='" . $sql_quote_obj->data[0]["amount_total"] . "' " . "WHERE id='{$invoiceid}' LIMIT 1";
$sql_obj->execute();
/*
Migrate all the items from the quote to the invoice
*/
$sql_obj->string = "UPDATE account_items SET invoiceid='{$invoiceid}', invoicetype='ar' WHERE invoiceid='{$id}' AND invoicetype='quotes'";
$sql_obj->execute();
/*
Call functions to create transaction entries for all the items.
(remember that the quote had nothing in account_trans for the items)
*/
$invoice_item = new invoice_items();
$invoice_item->id_invoice = $invoiceid;
$invoice_item->type_invoice = "ar";
$invoice_item->action_update_ledger();
unset($invoice_item);
/*
Migrate the journal
*/
$sql_obj->string = "UPDATE journal SET customid='{$invoiceid}', journalname='account_ar' WHERE customid='{$id}' AND journalname='account_quotes'";
$sql_obj->execute();
/*
Delete the quote
*/
$sql_obj->string = "DELETE FROM account_quotes WHERE id='{$id}' LIMIT 1";
$sql_obj->execute();
}
/*
Update the Journal
*/
journal_quickadd_event("account_ar", $invoiceid, "Converted quotation into invoice");
/*
Commit
*/
if (error_check()) {
$sql_obj->trans_rollback();
log_write("error", "inc_quotes_forms", "An error occured whilst attempting to convert the quote into an invoice. No changes have been made.");
$_SESSION["error"]["form"]["quote_convert"] = "failed";
header("Location: ../../index.php?page={$returnpage_error}&id={$id}");
exit(0);
} else {
$sql_obj->trans_commit();
log_write("notification", "inc_quotes_forms", "Quotation has been converted to an invoice successfully.");
header("Location: ../../index.php?page={$returnpage_success}&id={$invoiceid}");
exit(0);
}
}
// end if passed tests
}
function action_create()
{
log_debug("inc_credit_refund", "Executing action_create()");
// create new credit table entry
$sql_obj = new sql_query();
$sql_obj->string = "INSERT INTO " . $this->type . "s_credits (date_trans, type, id_" . $this->type . ") VALUES ('" . $this->data["date_trans"] . "', 'refund', '" . $this->data["id_customer"] . "')";
if (!$sql_obj->execute()) {
log_debug("inc_credit", "Failure whilst creating credit refund entry.");
return 0;
}
$this->id = $sql_obj->fetch_insert_id();
unset($sql_obj);
// call the update function to process the refund fully and generate ledger fields.
if (!$this->action_update()) {
return 0;
}
log_debug("inc_credit_refund", "Successfully created new credit refund " . $this->id . "");
return $this->id;
}
function action_create()
{
log_write("debug", "traffic_types", "Executing action_create()");
/*
Start Transaction
*/
$sql_obj = new sql_query();
$sql_obj->trans_begin();
/*
Create CDR Rate Table
*/
$sql_obj->string = "INSERT INTO `traffic_types` (type_name) VALUES ('" . $this->data["type_name"] . "')";
$sql_obj->execute();
$this->id = $sql_obj->fetch_insert_id();
/*
Commit
*/
if (error_check()) {
$sql_obj->trans_rollback();
log_write("error", "traffic_types", "An error occured when attemping to define a new traffic type.");
return 0;
} else {
$sql_obj->trans_commit();
return $this->id;
}
}
function action_create()
{
log_debug("inc_taxes", "Executing action_create()");
$sql_obj = new sql_query();
$sql_obj->string = "INSERT INTO `account_taxes` (name_tax) VALUES ('" . $this->data["name_tax"] . "')";
if (!$sql_obj->execute()) {
log_write("error", "inc_taxes", "Unexpected DB error whilst attempting to create a new tax");
return 0;
}
$this->id = $sql_obj->fetch_insert_id();
return $this->id;
}
function service_usage_alerts_generate($customerid = NULL)
{
log_debug("inc_services_usage", "Executing service_usage_alerts_generate({$customerid})");
/*
Fetch configuration Options
*/
// check that email is enabled
if (sql_get_singlevalue("SELECT value FROM config WHERE name='EMAIL_ENABLE' LIMIT 1") != "enabled") {
log_write("error", "inc_services_usage", "Unable to email customer usage alerts, due to EMAIL_ENABLE being disabled");
return -1;
}
// fetch email address to send as.
$email_sender = sql_get_singlevalue("SELECT value FROM config WHERE name='COMPANY_NAME'") . " <" . sql_get_singlevalue("SELECT value FROM config WHERE name='COMPANY_CONTACT_EMAIL'") . ">";
/*
Run through all the active services
*/
$sql_custserv_obj = new sql_query();
$sql_custserv_obj->string = "SELECT id, customerid, serviceid, description, date_period_first, date_period_next, date_period_last FROM services_customers WHERE services_customers.active='1'";
if ($customerid) {
$sql_custserv_obj->string .= " AND customerid='{$customerid}'";
}
$sql_custserv_obj->execute();
if ($sql_custserv_obj->num_rows()) {
$sql_custserv_obj->fetch_array();
foreach ($sql_custserv_obj->data as $customer_data) {
/*
Process each service that the customer has, provided that it is a usage service. Any non-usage services we
can simply skip.
*/
// fetch service details
$obj_service = new service_bundle();
$obj_service->option_type = "customer";
$obj_service->option_type_id = $customer_data["id"];
if (!$obj_service->verify_id_options()) {
log_write("error", "customers_services", "Unable to verify service ID of " . $customer_data["id"] . " as being valid.");
return 0;
}
$obj_service->load_data();
$obj_service->load_data_options();
// fetch customer details
$sql_customer_obj = sql_get_singlerow("SELECT name_customer FROM customers WHERE id='" . $customer_data["customerid"] . "' LIMIT 1");
$arr_sql_contact = sql_get_singlerow("SELECT id, contact FROM customer_contacts WHERE customer_id = '" . $customer_data["customerid"] . "' AND role = 'accounts' LIMIT 1");
$arr_sql_contact_details = sql_get_singlerow("SELECT detail AS contact_email FROM customer_contact_records WHERE contact_id = '" . $arr_sql_contact["id"] . "' AND type = 'email' LIMIT 1");
// place the contact details into the customer details array.
$sql_customer_obj["name_contact"] = $arr_sql_contact["contact"];
$sql_customer_obj["contact_email"] = $arr_sql_contact_details["contact_email"];
// check the service type
$service_type = sql_get_singlevalue("SELECT name as value FROM service_types WHERE id='" . $obj_service->data["typeid"] . "'");
// only process data_traffic, time or generic_usage services
//
// (call services have usage, but we don't currently alert for those)
//
if ($service_type == "generic_with_usage" || $service_type == "time" || $service_type == "data_traffic") {
log_debug("inc_services_usage", "Processing service " . $customer_data["id"] . " for customer " . $customer_data["customerid"] . "");
/*
Fetch the customer's currently active period.
*/
$sql_periods_obj = new sql_query();
$sql_periods_obj->string = "SELECT " . "id, " . "date_start, " . "date_end, " . "usage_summary, " . "usage_alerted " . "FROM services_customers_periods " . "WHERE " . "id_service_customer='" . $customer_data["id"] . "' " . "AND invoiceid_usage = '0' " . "AND date_end >= '" . date("Y-m-d") . "' LIMIT 1";
$sql_periods_obj->execute();
if ($sql_periods_obj->num_rows()) {
$sql_periods_obj->fetch_array();
$period_data = $sql_periods_obj->data[0];
// fetch billing mode
$billing_mode = sql_get_singlevalue("SELECT name as value FROM billing_modes WHERE id='" . $obj_service->data["billing_mode"] . "'");
// fetch unit naming
if ($service_type == "generic_with_usage") {
$unitname = $obj_service->data["units"];
} else {
$unitname = sql_get_singlevalue("SELECT name as value FROM service_units WHERE id='" . $obj_service->data["units"] . "'");
}
/*
Calculate number of included units
TODO: This code is a replicant of the source in include/services/inc_service_invoicegen.php used for calculating
partial periods and should really be functionalised as part of service usage continual improvements.
Part of the issue is lack of service period OO handling functions - migrating the code to such a setup will make
such ratio calculation functions far, far easier.
*/
// default ratio
$ratio = 1;
// 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("inc_services_usage", "Usage period service bills by month date");
if (time_calculate_daynum($period_data["date_start"]) != "01") {
// very first billing month
log_write("debug", "inc_services_usage", "First billing month for this usage period, adjusting pricing to suit days.");
if ($GLOBALS["config"]["SERVICE_PARTPERIOD_MODE"] == "seporate") {
log_write("debug", "inc_services_usage", "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_data["date_start"]));
$short_month_days_short = $short_month_days_total - time_calculate_daynum($period_data["date_start"]);
log_write("debug", "inc_services_usage", "Short initial billing period of {$short_month_days_short} days");
// calculate ratio
$ratio = $short_month_days_short / $short_month_days_total;
log_write("debug", "inc_services_usage", "Calculated service bill ratio of {$ratio} to handle short period.");
} else {
log_write("debug", "inc_services_usage", "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("inc_services_usage", "{$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", "inc_services_usage", "Calculated service bill ratio of {$ratio} to handle extended period.");
}
}
}
// end of calculate usage abnormal period
// calculate a final period
if ($period_data["date_period_last"] != "0000-00-00") {
log_write("debug", "inc_services_usage", "Service has a final period date set (" . $customer_data["date_period_last"] . ")");
if ($customer_data["date_period_last"] == $period_data["date_end"] || time_date_to_timestamp($customer_data["date_period_last"]) < time_date_to_timestamp($period_data["date_end"])) {
log_write("debug", "inc_services_usage", "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", "inc_services_usage", "Short initial billing period of " . $time["new_days"] . " days rather than expected " . $time["orig_days"] . "");
// calculate ratio
$ratio = $time["new_days"] / $time["orig_days"];
log_write("debug", "inc_services_usage", "Calculated service bill ratio of {$ratio} to handle short period.");
unset($time);
} else {
log_write("debug", "inc_services_usage", "Final service period is regular size, no adjustment required.");
}
}
}
if ($billing_mode == "monthend" || $billing_mode == "monthadvance") {
log_debug("inc_services_usage", "Service is billed by calender month");
/*
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.
Eg: 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.
To handle this, we increase the number of included units by the following method:
( standard_cost / normal_month_num_days ) * num_days_in_partial_month == extra_amount
total_amount = (extra_amount + normal_amount)
Note: This code is based off the section found for inc_services_usage.php. Could be worth creating a function?
*/
// check if the period is the very first period - the start and end dates will be in different months.
if (time_calculate_monthnum($period_data["date_start"]) != time_calculate_monthnum($period_data["date_end"])) {
// very first billing month
log_debug("inc_services_usage", "Very first billing month - adjusting included units to suit the extra time included.");
// 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("inc_services_usage", "{$extra_month_days_extra} additional days ontop of started billing period");
// calculate number of included units - round up to nearest full unit
$ratio = ($extra_month_days_total + $extra_month_days_extra) / $extra_month_days_total;
}
}
log_write("debug", "inc_services_usage", "Usage period ratio calculated as {$ratio}");
/*
^ End of Ratio Calculation - object orientation required around this
*/
/*
Process usage for each service type - there are differences between particular platforms.
*/
switch ($service_type) {
case "generic_with_usage":
case "time":
/*
Fetch the amount of usage
*/
$usage_obj = new service_usage_generic();
$usage_obj->id_service_customer = $customer_data["id"];
$usage_obj->date_start = $period_data["date_start"];
$usage_obj->date_end = $period_data["date_end"];
if ($usage_obj->load_data_service()) {
$usage_obj->fetch_usagedata();
if (isset($usage_obj->data["total_byunits"])) {
$usage = $usage_obj->data["total_byunits"];
} else {
$usage = $usage_obj->data["total"];
}
}
unset($usage_obj);
/*
Apply Ratio
*/
if ($ratio != "1") {
$obj_service->data["included_units"] = sprintf("%d", $obj_service->data["included_units"] * $ratio);
}
/*
Run usage notification logic
*/
if ($GLOBALS["config"]["SERVICES_USAGEALERTS_ENABLE"]) {
$message = "";
if ($usage > $obj_service->data["included_units"]) {
// usage is over 100% - check if we should report this
log_debug("inc_service_usage", "Usage is over 100%");
if ($obj_service->data["alert_extraunits"]) {
// check at what usage amount we last reported, and if
// we have used alert_extraunits more usage since then, send
// an alert to the customer.
if ($usage - $period_data["usage_alerted"] >= $obj_service->data["alert_extraunits"]) {
log_write("notification", "inc_service_usage", "Sending excess usage notification (over 100%)");
/*
Send excess usage notification (over 100%)
Message Example:
This email has been sent to advise you that you have gone over the
included usage on your plan.
You have now used 70 excess ZZ on your Example Service plan.
Used 120 ZZ out of 50 ZZ included in plan
Excess usage of 70 ZZ charged at $5.00 per ZZ (exc taxes)
Your current billing period ends on YYYY-MM-DD.
*/
// there is excess usage
$usage_excess = $usage - $obj_service->data["included_units"];
// prepare message
$message .= "This email has been sent to advise you that you have gone over the included usage on your plan\n";
$message .= "\n";
$message .= "You have now used {$usage_excess} excess {$unitname} on your " . $obj_service->data["name_service"] . " plan.\n";
$message .= "\n";
$message .= "Used {$usage} {$unitname} out of " . $obj_service->data["included_units"] . " {$unitname} included in plan.\n";
$message .= "Excess usage of {$usage_excess} {$unitname} charged at " . $obj_service->data["price_extraunits"] . " per {$unitname} (exc taxes).\n";
$message .= "\n";
$message .= "Your current billing period ends on " . $period_data["date_end"] . "\n";
$message .= "\n";
// send email
if ($sql_customer_obj["contact_email"]) {
$headers = "From: {$email_sender}\r\n";
mail($sql_customer_obj["name_contact"] . "<" . $sql_customer_obj["contact_email"] . ">", "Excess usage notification", $message, $headers);
} else {
log_write("error", "inc_service_usage", "Customer " . $sql_customer_obj["name_customer"] . " does not have an email address, unable to send usage notifications.");
}
// update alerted amount
$sql_obj = new sql_query();
$sql_obj->string = "UPDATE services_customers_periods SET usage_alerted='{$usage}' WHERE id='" . $period_data["id"] . "' LIMIT 1";
$sql_obj->execute();
}
}
} else {
// calculate 80% of the included usage
$included_usage_80pc = $obj_service->data["included_units"] * 0.8;
if ($usage == $obj_service->data["included_units"]) {
log_debug("inc_service_usage", "Usage is at 100%");
// usage is at 100%
//
// make sure that:
// 1. 100% usage alerting is enabled
// 2. that we have not already sent this alert (by checking period_data["usage_summary"])
//
if ($obj_service->data["alert_100pc"] && $period_data["usage_summary"] < $obj_service->data["included_units"]) {
log_write("notification", "inc_service_usage", "Sending excess usage notification (100% reached)");
/*
Send 100% usage notification
Message Example:
This email has been sent to advise you that you have used 100% of
your included usage on your Example Service plan.
Used 50 ZZ out of 50 ZZ included in plan
Any excess usage will be charged at $5.00 per ZZ (exc taxes)
Your current billing period ends on YYYY-MM-DD.
*/
// prepare message
$message .= "This email has been sent to advise you that you have used 100% of your included usage on your " . $obj_service->data["name_service"] . " plan.\n";
$message .= "\n";
$message .= "Used {$usage} {$unitname} out of " . $obj_service->data["included_units"] . " {$unitname} included in plan.\n";
$message .= "Any excess usage will be charged at " . $obj_service->data["price_extraunits"] . " per {$unitname} (exc taxes).\n";
$message .= "\n";
$message .= "Your current billing period ends on " . $period_data["date_end"] . "\n";
$message .= "\n";
// send email
if ($sql_customer_obj["contact_email"]) {
$headers = "From: {$email_sender}\r\n";
mail($sql_customer_obj["name_contact"] . "<" . $sql_customer_obj["contact_email"] . ">", "100% usage notification", $message, $headers);
} else {
log_write("error", "inc_service_usage", "Customer " . $sql_customer_obj["name_customer"] . " does not have an email address, unable to send usage notifications.");
}
// update alerted amount
$sql_obj = new sql_query();
$sql_obj->string = "UPDATE services_customers_periods SET usage_alerted='{$usage}' WHERE id='" . $period_data["id"] . "' LIMIT 1";
$sql_obj->execute();
}
} elseif ($usage > $included_usage_80pc) {
log_debug("inc_service_usage", "Usage is between 80% & 100%");
// usage is between 80 and 100%
//
// make sure that:
// 1. 80% usage alerting is enabled
// 2. that we have not already sent this alert (by checking period_data["usage_summary"])
//
if ($obj_service->data["alert_80pc"] && $period_data["usage_summary"] < $included_usage_80pc) {
log_write("notification", "inc_service_usage", "Sending excess usage notification (80% - 100%)");
/*
Send 80% usage notification
Message Example:
This email has been sent to advise you that you have used over 80% of
your included usage on your Example Service plan.
Used 50 ZZ out of 50 ZZ included in plan
Any excess usage will be charged at $5.00 per ZZ (exc taxes)
Your current billing period ends on YYYY-MM-DD.
*/
// prepare message
$message .= "This email has been sent to advise you that you have used over 80% of your included usage on your " . $obj_service->data["name_service"] . " plan.\n";
$message .= "\n";
$message .= "Used {$usage} {$unitname} out of " . $obj_service->data["included_units"] . " {$unitname} included in plan.\n";
$message .= "Any excess usage will be charged at " . $obj_service->data["price_extraunits"] . " per {$unitname} (exc taxes).\n";
$message .= "\n";
$message .= "Your current billing period ends on " . $period_data["date_end"] . "\n";
$message .= "\n";
// fetch email
// send email
if ($sql_customer_obj["contact_email"]) {
$headers = "From: {$email_sender}\r\n";
mail($sql_customer_obj["name_contact"] . "<" . $sql_customer_obj["contact_email"] . ">", "80% usage notification", $message, $headers);
} else {
log_write("error", "inc_service_usage", "Customer " . $sql_customer_obj["name_customer"] . " does not have an email address, unable to send usage notifications.");
}
// update alerted amount
$sql_obj = new sql_query();
$sql_obj->string = "UPDATE services_customers_periods SET usage_alerted='{$usage}' WHERE id='" . $period_data["id"] . "' LIMIT 1";
$sql_obj->execute();
}
}
}
} else {
log_write("notification", "inc_service_usage", "Not sending usage notification/reminder due to SERVICES_USAGEALERTS_ENABLE being disabled");
}
/*
Update Usage Summary - this used for various user interfaces and is the *total* transfer usage report.
*/
$sql_obj = new sql_query();
$sql_obj->string = "UPDATE services_customers_periods SET usage_summary='{$usage}' WHERE id='" . $period_data["id"] . "' LIMIT 1";
$sql_obj->execute();
break;
case "data_traffic":
/*
DATA_TRAFFIC
Data traffic services are more complex for usage checks and notifications than other services, due to the need to
fetch usage amounts for each cap type and notify as appropiate.
Some data services may also be uncapped/unlimited, in which case we want to record their current usage amount but
won't need to ever send usage notifications.
*/
/*
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 = $customer_data["serviceid"];
$traffic_types_obj->id_service_customer = $customer_data["id"];
$traffic_types_obj->load_data_traffic_caps();
$traffic_types_obj->load_data_override_caps();
/*
Fetch the amount of usage
*/
$usage_obj = new service_usage_traffic();
$usage_obj->id_service_customer = $customer_data["id"];
$usage_obj->date_start = $period_data["date_start"];
$usage_obj->date_end = $period_data["date_end"];
if ($usage_obj->load_data_service()) {
$usage_obj->fetch_usage_traffic();
}
/*
Update service usage database record for each cap
Create a new usage alert summary record - this record defines the usage as at a certain date
and tracks whether usage alerts were sent or not.
*/
$usage_alert_id = array();
// holds IDs of inserted rows
foreach ($traffic_types_obj->data as $traffic_cap) {
log_write("debug", "inc_service_usage", "Service " . $customer_data["id"] . " data usage for traffic type " . $traffic_type["type_name"] . " is " . $usage_obj->data["total_byunits"][$traffic_cap["type_label"]] . " units");
// create new record
$sql_obj = new sql_query();
$sql_obj->string = "INSERT INTO service_usage_alerts\n\t\t\t\t\t\t\t\t\t\t\t\t(id_service_customer,\n\t\t\t\t\t\t\t\t\t\t\t\t id_service_period,\n\t\t\t\t\t\t\t\t\t\t\t\t id_type,\n\t\t\t\t\t\t\t\t\t\t\t\t date_update,\n\t\t\t\t\t\t\t\t\t\t\t\t usage_current)\n\t\t\t\t\t\t\t\t\t\t\t\tVALUES\n\t\t\t\t\t\t\t\t\t\t\t\t('" . $customer_data["id"] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . $period_data["id"] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . $traffic_cap["id_type"] . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . date("Y-m-d") . "',\n\t\t\t\t\t\t\t\t\t\t\t\t '" . $usage_obj->data["total_byunits"][$traffic_cap["type_label"]] . "')";
$sql_obj->execute();
// record type label ID
$usage_alert_id[$traffic_cap["type_label"]] = $sql_obj->fetch_insert_id();
}
// Update Usage Summary - this used for various user interfaces and is the *total* transfer usage report.
log_write("notification", "inc_service_usage", "Customer " . $sql_customer_obj["name_customer"] . " has used a total of " . $usage_obj->data["total_byunits"]["total"] . " {$unitname} traffic on service \"" . $obj_service->data["name_service"] . "\"");
$sql_obj = new sql_query();
$sql_obj->string = "UPDATE services_customers_periods SET usage_summary='" . $usage_obj->data["total_byunits"]["total"] . "' WHERE id='" . $period_data["id"] . "' LIMIT 1";
$sql_obj->execute();
/*
Run usage notification logic
*/
if ($GLOBALS["config"]["SERVICES_USAGEALERTS_ENABLE"]) {
// fetch usage - in particular, the last usage amount that we alerted for.
$usage = $usage_obj->data["total_byunits"][$traffic_cap["type_label"]];
$usage_alerted = 0;
$sql_obj->string = "SELECT date_sent, usage_alerted FROM service_usage_alerts WHERE id_service_customer='" . $customer_data["id"] . "' AND id_service_period='" . $period_data["id"] . "' AND id_type='" . $traffic_cap["id_type"] . "' AND id!='" . $usage_alert_id[$traffic_cap["type_label"]] . "' ORDER BY date_sent DESC, id DESC LIMIT 1";
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
if ($sql_obj->data[0]["date_sent"] != "0000-00-00") {
$usage_alerted = $sql_obj->data[0]["usage_alerted"];
}
}
// used to flag the usage caps that need alerting
$alert_80 = array();
$alert_100 = array();
$alert_extra = array();
$alert_none = array();
// run through current caps, flag all which need notifications.
$j = 0;
foreach ($traffic_types_obj->data as $traffic_cap) {
// we don't care about unlimited traffic
if ($traffic_cap["cap_mode"] != "capped") {
// skip
log_write("debug", "inc_service_usage", "Skipping traffic cap due to mode of " . $traffic_cap["cap_mode"] . "");
$alert_none[] = $traffic_cap["type_label"];
continue;
}
// determine caps
if ($ratio != "1") {
// recalculate for short or long months
$cap_units_included = sprintf("%d", $traffic_cap["cap_units_included"] * $ratio);
// save for rest of session
$traffic_types_obj->data[$j]["cap_units_included"] = $cap_units_included;
} else {
// no changes
$cap_units_included = $traffic_cap["cap_units_included"];
}
// determine threshholds
$cap_100 = $cap_units_included;
$cap_80 = $cap_units_included * 0.8;
if ($usage >= $cap_100) {
// usage is at or over 100%
if ($usage < $cap_100 + $obj_service->data["alert_extraunits"]) {
// just over 100%, but less than the excess alert count - consider as 100%
if ($usage_alerted < $cap_100) {
$alert_100[] = $traffic_cap["type_label"];
} else {
$alert_none[] = $traffic_cap["type_label"];
}
} else {
// well over 100%
if ($obj_service->data["alert_extraunits"]) {
// check at what usage amount we last reported, and if
// we have used alert_extraunits+ more usage since then, send
// an alert to the customer.
if ($usage - $usage_alerted >= $obj_service->data["alert_extraunits"]) {
// excess usage
$alert_extra[] = $traffic_cap["type_label"];
} else {
$alert_none[] = $traffic_cap["type_label"];
}
} else {
// no extra unit alerts configured, so we should not alert to excess
// usage.
$alert_none[] = $traffic_cap["type_label"];
}
}
} elseif ($usage >= $cap_80 && $usage < $cap_100) {
// usage between 80% and 100%
if ($obj_service->data["alert_80pc"] && $usage_alerted < $cap_80) {
// we haven't alerted for this yet, so flag it
$alert_80[] = $traffic_cap["type_label"];
} else {
$alert_none[] = $traffic_cap["type_label"];
}
} else {
// usage is below 80% mark
$alert_none[] = $traffic_cap["type_label"];
}
$j++;
}
// end of traffic loops
log_write("debug", "inc_service_usage", "Following data caps are at 80% alert: " . format_arraytocommastring($alert_80) . "");
log_write("debug", "inc_service_usage", "Following data caps are at 100% alert: " . format_arraytocommastring($alert_100) . "");
log_write("debug", "inc_service_usage", "Following data caps are at 100% + extra blocks alert: " . format_arraytocommastring($alert_extra) . "");
log_write("debug", "inc_service_usage", "Following data caps do not require alerting: " . format_arraytocommastring($alert_none) . "");
/*
Process Usage Notifications
Here we need to loop through all caps flagged for notifications and write a message to the customer
for all overage caps
*/
if (!empty($alert_80) || !empty($alert_100) || !empty($alert_extra)) {
log_write("debug", "inc_service_usage", "Alerting for service, preparing email message.");
/*
Now we run through all the alert flagged data caps and use it to assemble a usage notification warning email.
Example Message
---------------
DATA USAGE ADVISORY
This email has been sent to advise you about your data service usage as of 18-05-2011.
Service "My Example Internet Service"
NATIONAL
You have used 150% of your National data cap.
Used 15GB out of 10GB included in plan.
Excess usage of 5GB charged at $5.00 per GB (exc taxes)
INTERNATIONAL
You have used 84% of your International data cap.
Used 84GB out of 100GB included in plan.
Any future excess usage will be charged at $8.00 per GB (exc taxes)
BILLING PERIOD
Your current billing period ends on YYYY-MM-DD.
{optional} Note:
This billing period is longer/shorter than your regular billing period, this may mean
your data cap allocations appear different to normal to refect the longer/shorter period.
This typically occurs when you first signup to a service, upgrade a service or cancel a service.
*/
$message = "\n";
$message .= "DATA USAGE ADVISORY\n";
$message .= "\n";
$message .= "This email has been sent to advise you about your data service usage as of " . time_format_humandate() . "\n";
$message .= "\n";
$message .= "Service \"" . $obj_service->data["name_service"] . "\"\n";
$message .= "\n";
$message .= "\n";
$message .= "\n";
foreach ($traffic_types_obj->data as $traffic_cap) {
log_write("debug", "inc_service_usage", "Preparing usage warning email messages for: \"" . $traffic_cap["type_label"] . "\"");
// determine percentage used
$percentage = sprintf("%d", $usage_obj->data["total_byunits"][$traffic_cap["type_label"]] / $traffic_cap["cap_units_included"] * 100);
// if there is only one cap, adjust label
if ($traffic_types_obj->data_num_rows == 1) {
$traffic_cap["type_name"] = "All Traffic";
}
// 80%-100%
if (in_array($traffic_cap["type_label"], $alert_80)) {
$message .= strtoupper($traffic_cap["type_name"]) . "\n";
$message .= "\n";
$message .= "You have used {$percentage}% of your " . $traffic_cap["type_name"] . " data cap\n";
$message .= "\n";
$message .= "Used " . $usage_obj->data["total_byunits"][$traffic_cap["type_label"]] . " {$unitname} out of " . $traffic_cap["cap_units_included"] . " {$unitname} included in plan.\n";
$message .= "Any future excess usage will be charged at " . format_money($traffic_cap["cap_units_price"]) . " per {$unitname} (exc taxes)\n";
$message .= "\n\n";
}
// 100%
if (in_array($traffic_cap["type_label"], $alert_100)) {
$message .= strtoupper($traffic_cap["type_name"]) . "\n";
$message .= "\n";
$message .= "You have used {$percentage}% of your " . $traffic_cap["type_name"] . " data cap\n";
$message .= "\n";
$message .= "Used " . $usage_obj->data["total_byunits"][$traffic_cap["type_label"]] . " {$unitname} out of " . $traffic_cap["cap_units_included"] . " {$unitname} included in plan.\n";
$message .= "Any future excess usage will be charged at " . format_money($traffic_cap["cap_units_price"]) . " per {$unitname} (exc taxes)\n";
$message .= "\n\n";
}
// 100% ++ excess
if (in_array($traffic_cap["type_label"], $alert_extra)) {
$usage_excess = $usage_obj->data["total_byunits"][$traffic_cap["type_label"]] - $traffic_cap["cap_units_included"];
$message .= strtoupper($traffic_cap["type_name"]) . "\n";
$message .= "\n";
$message .= "You have used {$percentage}% of your " . $traffic_cap["type_name"] . " data cap\n";
$message .= "\n";
$message .= "Used " . $usage_obj->data["total_byunits"][$traffic_cap["type_label"]] . " out of " . $traffic_cap["cap_units_included"] . " {$unitname} included in plan.\n";
$message .= "Excess usage of " . $usage_excess . " {$unitname} charged at " . format_money($traffic_cap["cap_units_price"]) . " per {$unitname} (exc taxes)\n";
$message .= "\n\n";
}
}
/*
Footer
*/
$message .= "BILLING PERIOD\n";
$message .= "\n";
$message .= "Your current billing period ends on " . time_format_humandate($period_data["date_end"]) . "\n";
$message .= "\n";
/*
Tell user about long/short periods to avoid inevidable accounts enquires relating to their usage
*/
if ($ratio < 1) {
// shorter period
$message .= "\n";
$message .= "Important Note: This billing period is shorter than your regular billing period, this may mean your data cap allocations appear smaller than normal to refect the shorter period. This typically occurs when you first signup to a service, upgrade a service or cancel a service.\n";
} elseif ($ratio > 1) {
// longer period
$message .= "\n";
$message .= "Important Note: This billing period is longer than your regular billing period, this may mean your data cap allocations appear larger than normal to refect the longer period. This typically occurs when you first signup to a service, upgrade a service or cancel a service.\n";
}
/*
Issue Email
TODO: Future enhancements will include the capability to select a usage contact email address first, before
falling back to the regular accounts email address. This will make it easier for companies to send bills
to AR but usage alerts to staff.
*/
if ($sql_customer_obj["contact_email"]) {
$headers = "From: {$email_sender}\r\n";
mail($sql_customer_obj["name_contact"] . "<" . $sql_customer_obj["contact_email"] . ">", "Service usage notification", $message, $headers);
log_write("notification", "inc_service_usage", "Issuing usage notification email to " . $sql_customer_obj["name_contact"] . " at " . $sql_customer_obj["contact_email"] . "");
} else {
log_write("error", "inc_service_usage", "Customer " . $sql_customer_obj["name_customer"] . " does not have an email address, unable to send usage notifications.");
}
/*
Update alerted amount tracker
*/
$sql_obj = new sql_query();
$sql_obj->string = "UPDATE service_usage_alerts SET usage_alerted='" . $usage_obj->data["total_byunits"][$traffic_cap["type_label"]] . "', date_sent='" . date("Y-m-d") . "' WHERE id='" . $usage_alert_id[$traffic_cap["type_label"]] . "' LIMIT 1";
$sql_obj->execute();
}
// end if usage notifications to process
}
// end if alerts enabled
unset($traffic_types_obj);
unset($usage_obj);
break;
}
// end of switch service type
}
// end if usage periods exist
}
// end if a usage service
}
// end of loop through customer services
}
// end if customer(s) services exist
}
function action_create()
{
log_debug("invoice_items", "Executing action_create");
/*
Start SQL Transaction
*/
$sql_obj = new sql_query();
$sql_obj->trans_begin();
/*
Create new invoice item
*/
$sql_obj->string = "INSERT INTO `account_items` (invoiceid, invoicetype) VALUES ('" . $this->id_invoice . "', '" . $this->type_invoice . "')";
$sql_obj->execute();
$this->id_item = $sql_obj->fetch_insert_id();
/*
Update Journal
*/
journal_quickadd_event("account_" . $this->type_invoice . "", $this->id_invoice, "Item successfully created");
/*
Commit
*/
if (error_check() || $this->id_item == 0) {
$sql_obj->trans_rollback();
log_write("error", "invoice_items", "An error occured preventing the creation of the invoice item");
return 0;
} else {
$sql_obj->trans_commit();
log_write("notification", "invoice_items", "Successfully created new invoice item");
return $this->id_item;
}
}
function execute()
{
/*
Load attribute data
*/
$this->obj_attributes->type = "customer";
$this->obj_attributes->id_owner = $this->obj_customer->id;
$this->obj_attributes->load_data_all();
/*
Define form structure
*/
$this->obj_form->formname = "attributes_customer";
$this->obj_form->language = $_SESSION["user"]["lang"];
$this->obj_form->action = "customers/attributes-process.php";
$this->obj_form->method = "post";
/*
* Create variables to track number of attributes and their groups
*/
$this->group_arrays = array();
$this->last_row_in_group = array();
$this->highest_attr_id = sql_get_singlevalue("SELECT id AS value FROM attributes ORDER BY id DESC LIMIT 1");
/*
* Assign attributes to groups by group name for sorting
*/
$group_arrays_by_name = array();
foreach ((array) $this->obj_attributes->data as $attribute) {
$group_arrays_by_name[$attribute["group_name"]][] = $attribute["id"];
$group_arrays_by_name[$attribute["group_name"]]["name"] = $attribute["group_name"];
$group_arrays_by_name[$attribute["group_name"]]["group_id"] = $attribute["id_group"];
}
// sort attribute groups by key, use strnatcasecmp as ksort is capital sensitive
uksort($group_arrays_by_name, "strnatcasecmp");
/*
* Assign attributes to correct group arrays indexed by ID after sorting.
*/
foreach ($group_arrays_by_name as $array_grouped_by_name) {
// Copy the group ID into a variable so we can unset it in the array.
$array_group_id = $array_grouped_by_name["group_id"];
unset($array_grouped_by_name["group_id"]);
// Place the attributes into the correct array by ID number
$this->group_arrays[$array_group_id] = $array_grouped_by_name;
}
/*
* Add one (empty) attribute row to each group
* Add the dynamically created attribute rows to each group
*/
foreach ($this->group_arrays as $group_id => $attributes) {
$this->highest_attr_id++;
$this->group_arrays[$group_id][] = $this->highest_attr_id;
$this->last_row_in_group[$group_id] = $this->highest_attr_id;
$new_attr_list = @security_script_input_predefined("any", $_GET["group_" . $group_id . "_new_attributes"]);
if ($new_attr_list != "") {
$new_attr_array = explode(",", $new_attr_list);
for ($i = 0; $i < count($new_attr_array); $i++) {
if (!empty($new_attr_array[$i])) {
$this->group_arrays[$group_id][] = $new_attr_array[$i];
$this->last_row_in_group[$group_id] = $new_attr_array[$i];
}
}
}
}
/*
* Add new groups to the group array
* This ensures dynamically added groups will display when an error sends user back to the form
*/
for ($i = 0; $i < count($this->new_groups_array); $i++) {
if (!empty($this->new_groups_array[$i])) {
//get attribute list
$attr_list = @security_script_input_predefined("any", $_GET["group_" . $this->new_groups_array[$i] . "_attributes_list"]);
$attr_array = explode(",", $attr_list);
for ($j = 0; $j < count($attr_array); $j++) {
if (!empty($attr_array[$j])) {
$this->group_arrays[$this->new_groups_array[$i]][] = $attr_array[$j];
$this->last_row_in_group[$this->new_groups_array[$i]] = $attr_array[$j];
}
}
//record group name
$group_name = sql_get_singlevalue("SELECT group_name AS value FROM attributes_group WHERE id = " . $this->new_groups_array[$i]);
$this->group_arrays[$this->new_groups_array[$i]]["name"] = $group_name;
}
}
/*
* If no attributes currently exist, create a default group in the database
* Name is "Default Group [id]" so that no others are overwritten
* Id is obtained by finding highest in the DB and adding one
* This takes into account multiple customers, possibility of unchanged names, etc
*/
if (!count($this->group_arrays)) {
$this->no_attributes = "true";
$add_group = new sql_query();
$add_group->string = "INSERT INTO attributes_group(group_name) VALUES(\"Default Group\")";
$add_group->execute();
$new_group_id = $add_group->fetch_insert_id();
$this->group_arrays[$new_group_id]["name"] = "Default Group";
$this->group_arrays[$new_group_id][] = ++$this->highest_attr_id;
$this->group_arrays[$new_group_id][] = ++$this->highest_attr_id;
$this->last_row_in_group[$group_id] = $this->highest_attr_id;
}
/*
* Create a list of group ids and names
*/
$this->group_list = "";
foreach ($this->group_arrays as $group_id => $data) {
$this->group_list .= $group_id . "," . $this->group_arrays[$group_id]["name"] . ",";
}
/*
* Generate form fields
*/
foreach ($this->group_arrays as $group_id => $attributes) {
$structure = NULL;
$structure["fieldname"] = "group_" . $group_id . "_new_attributes";
$structure["type"] = "hidden";
$this->obj_form->add_input($structure);
foreach ($attributes as $key => $id) {
if ((string) $key != "name") {
$structure = NULL;
$structure["fieldname"] = "attribute_" . $id . "_id";
$structure["defaultvalue"] = $id;
$structure["type"] = "hidden";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "attribute_" . $id . "_key";
$structure["type"] = "input";
$structure["options"]["width"] = "300";
$structure["options"]["max_length"] = "80";
$structure["options"]["autocomplete"] = "sql";
$structure["options"]["autocomplete_sql"] = "SELECT DISTINCT `key` as label FROM attributes";
$structure["options"]["help"] = "Key/Label for attribute (with autocomplete)";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "attribute_" . $id . "_value";
$structure["type"] = "input";
$structure["options"]["width"] = "500";
$structure["options"]["help"] = "Text field to store any data";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "attribute_" . $id . "_delete_undo";
$structure["type"] = "hidden";
$structure["defaultvalue"] = "false";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "attribute_" . $id . "_group";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $group_id;
$this->obj_form->add_input($structure);
}
}
}
foreach ($this->last_row_in_group as $groupid => $attribute) {
$this->obj_form->structure["attribute_" . $attribute . "_key"]["options"]["css_field_class"] = "last_row";
$this->obj_form->structure["attribute_" . $attribute . "_value"]["options"]["css_field_class"] = "last_row";
}
// load in what data we have
if (is_array($this->obj_attributes->data)) {
foreach ($this->obj_attributes->data as $record) {
// fetch data
$this->obj_form->structure["attribute_" . $record["id"] . "_key"]["defaultvalue"] = $record["key"];
$this->obj_form->structure["attribute_" . $record["id"] . "_value"]["defaultvalue"] = $record["value"];
}
}
// hidden fields
$structure = NULL;
$structure["fieldname"] = "id_customer";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $this->obj_customer->id;
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "highest_attr_id";
$structure["type"] = "hidden";
$structure["defaultvalue"] = "{$this->highest_attr_id}";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "new_groups";
$structure["type"] = "hidden";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "group_list";
$structure["defaultvalue"] = $this->group_list;
$structure["type"] = "hidden";
$this->obj_form->add_input($structure);
for ($i = 0; $i < count($this->new_groups_array); $i++) {
if (!empty($this->new_groups_array[$i])) {
$structure = NULL;
$structure["fieldname"] = "group_" . $this->new_groups_array[$i] . "_attribute_list";
$structure["type"] = "hidden";
$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 data in event of an error
if (error_check()) {
$this->obj_form->load_data_error();
}
return 1;
}
header("Location: ../index.php?page=projects/phase-edit.php&id={$projectid}&phaseid={$phaseid}");
exit(0);
} else {
/*
Start Transaction
*/
$sql_obj = new sql_query();
$sql_obj->trans_begin();
/*
Create a new phase (if required)
*/
if ($mode == "add") {
// create a new entry in the DB
$sql_obj->string = "INSERT INTO `project_phases` (projectid) VALUES ('{$projectid}')";
$sql_obj->execute();
$phaseid = $sql_obj->fetch_insert_id();
}
/*
Update Phase Details
*/
if ($phaseid) {
$sql_obj->string = "UPDATE `project_phases` SET " . "name_phase='" . $data["name_phase"] . "', " . "description='" . $data["description"] . "' " . "WHERE id='{$phaseid}' LIMIT 1";
$sql_obj->execute();
}
/*
Commit
*/
if (error_check()) {
$sql_obj->trans_rollback();
log_write("error", "process", "A fatal error occuring whilst attempting to update the phase. No changes were made.");
} else {
function action_create_orders()
{
log_debug("inc_customers", "Executing action_create_orders()");
// create a new customer
$sql_obj = new sql_query();
$sql_obj->string = "INSERT INTO `customers_orders` (id_customer) VALUES ('" . $this->id . "')";
$sql_obj->execute();
$this->id_order = $sql_obj->fetch_insert_id();
return $this->id_order;
}