function execute()
{
/*
Define form structure
*/
$this->obj_form = new form_input();
$this->obj_form->formname = "service_view";
$this->obj_form->language = $_SESSION["user"]["lang"];
$this->obj_form->action = "customers/service-edit-process.php";
$this->obj_form->method = "post";
// general
if ($this->obj_customer->id_service_customer) {
/*
An existing service is being adjusted
*/
// general
$structure = NULL;
$structure["fieldname"] = "id_service_customer";
$structure["type"] = "text";
$structure["defaultvalue"] = $this->obj_customer->id_service_customer;
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "service_parent";
$structure["type"] = "text";
$structure["options"]["nohidden"] = 1;
$structure["defaultvalue"] = "<a href=\"index.php?page=services/view.php&id=" . $this->obj_customer->obj_service->id . "\">" . sql_get_singlevalue("SELECT name_service as value FROM services WHERE id='" . $this->obj_customer->obj_service->id . "' LIMIT 1") . "</a>";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "name_service";
$structure["type"] = "input";
$structure["options"]["req"] = "yes";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "description";
$structure["type"] = "textarea";
$this->obj_form->add_input($structure);
$this->obj_form->subforms["service_edit"] = array("id_service_customer", "service_parent", "name_service", "description");
// service controls
$structure = NULL;
$structure["fieldname"] = "control_help";
$structure["type"] = "message";
$structure["defaultvalue"] = "When disabling services, the best approach is to set the last period date, and ABS will correctly bill to that final date, handle usage/partial periods and issue a final invoice, before disabling the service automatically - however it is possible to disable a service immediently if so desired by using the active checkbox.";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "active";
$structure["type"] = "checkbox";
$structure["options"]["label"] = "Service is enabled";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "date_period_last";
$structure["type"] = "date";
$structure["options"]["label"] = " Earliest termination date is: " . time_format_humandate(sql_get_singlevalue("SELECT date_end as value FROM services_customers_periods WHERE id_service_customer='" . $this->obj_customer->id_service_customer . "' ORDER BY date_end DESC LIMIT 1"));
$this->obj_form->add_input($structure);
$this->obj_form->subforms["service_controls"] = array("control_help", "active", "date_period_last");
// billing
$structure = NULL;
$structure["fieldname"] = "billing_cycle_string";
$structure["type"] = "text";
$this->obj_form->add_input($structure);
if ($this->locked_datechange) {
// the service has been billed, the start date is fixed.
$structure = NULL;
$structure["fieldname"] = "date_period_first";
$structure["type"] = "text";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "date_period_next";
$structure["type"] = "text";
$this->obj_form->add_input($structure);
} else {
// service has not yet been billed, so the dates can still be adjusted
$structure = NULL;
$structure["fieldname"] = "date_period_first";
$structure["type"] = "date";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "date_period_next";
$structure["type"] = "text";
$this->obj_form->add_input($structure);
}
$this->obj_form->subforms["service_billing"] = array("billing_cycle_string", "date_period_first", "date_period_next");
$pos = stristr($this->obj_customer->obj_service->data["typeid_string"], "phone_");
if ($pos !== FALSE && $pos == 0) {
$structure = NULL;
$structure["fieldname"] = "billing_cdr_csv_output";
$structure["options"]["label"] = " " . lang_trans("billing_cdr_csv_output_help");
$structure["type"] = "checkbox";
$this->obj_form->add_input($structure);
$this->obj_form->subforms["service_billing"][] = "billing_cdr_csv_output";
}
if (!$this->obj_customer->service_get_is_bundle_item()) {
// price customisation
$structure = NULL;
$structure["fieldname"] = "price";
$structure["type"] = "money";
$structure["options"]["req"] = "yes";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "discount";
$structure["type"] = "input";
$structure["options"]["width"] = 50;
$structure["options"]["label"] = " %";
$structure["options"]["max_length"] = "6";
$this->obj_form->add_input($structure);
$this->obj_form->subforms["service_price"] = array("price", "discount");
// setup charges - only display if the service is inactive
if (!sql_get_singlevalue("SELECT active as value FROM services_customers WHERE id='" . $this->obj_customer->id_service_customer . "' LIMIT 1")) {
$structure = NULL;
$structure["fieldname"] = "info_setup_help";
$structure["type"] = "message";
$structure["defaultvalue"] = "<p>" . lang_trans("info_setup_help") . "</p>";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "price_setup";
$structure["type"] = "money";
$structure["options"]["req"] = "yes";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "discount_setup";
$structure["type"] = "input";
$structure["options"]["width"] = 50;
$structure["options"]["label"] = " %";
$structure["options"]["max_length"] = "6";
$structure["defaultvalue"] = $this->obj_customer->obj_service->data["discount"];
$this->obj_form->add_input($structure);
$this->obj_form->subforms["service_setup"] = array("info_setup_help", "price_setup", "discount_setup");
} else {
$structure = NULL;
$structure["fieldname"] = "info_setup_help";
$structure["type"] = "message";
$structure["defaultvalue"] = "<p>A setup fee of " . format_money($this->obj_customer->obj_service->data["price_setup"]) . " was charged for this service.</p>";
$this->obj_form->add_input($structure);
$this->obj_form->subforms["service_setup"] = array("info_setup_help");
}
}
// end if not bundle
// service-type specific sections
switch ($this->obj_customer->obj_service->data["typeid_string"]) {
case "bundle":
/*
Bundle Service
Display a hyperlinked list of all the component services belonging to
the bundle.
*/
$structure = NULL;
$structure["fieldname"] = "bundle_msg";
$structure["type"] = "message";
$structure["defaultvalue"] = "<p>This service is a bundle, containing a number of other services. Note that enabling/disabling or deleting this bundle service will affect all the component services below.</p>";
$this->obj_form->add_input($structure);
// fetch all the items for the bundle that have been setup for this customer and
// display some details in a table inside of a form field. (kinda ugly rendering hack, but works OK)
$structure = NULL;
$structure["fieldname"] = "bundle_components";
$structure["type"] = "message";
$structure["defaultvalue"] = "<table class=\"table_highlight\">";
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id, serviceid as id_service FROM services_customers WHERE bundleid='" . $this->obj_customer->id_service_customer . "'";
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
foreach ($sql_obj->data as $data_component) {
$obj_component = new service();
$obj_component->id = $data_component["id_service"];
$obj_component->option_type = "customer";
$obj_component->option_type_id = $data_component["id"];
$obj_component->load_data();
$obj_component->load_data_options();
if (sql_get_singlevalue("SELECT active as value FROM services_customers WHERE id='" . $data_component["id"] . "' LIMIT 1")) {
$obj_component->active_status_string = "<td class=\"table_highlight_info\">active</td>";
} else {
$obj_component->active_status_string = "<td class=\"table_highlight_important\">disabled</td>";
}
$structure["defaultvalue"] .= "<tr>" . "<td>Bundle Component: <b>" . $obj_component->data["name_service"] . "</b></td>" . $obj_component->active_status_string . "<td>" . $obj_component->data["description"] . "</td>" . "<td><a class=\"button_small\" href=\"index.php?page=customers/service-edit.php&id_customer=" . $this->obj_customer->id . "&id_service_customer=" . $obj_component->option_type_id . "\">View Service</a></td>" . "</tr>";
}
}
$structure["defaultvalue"] .= "</table>";
$this->obj_form->add_input($structure);
$this->obj_form->subforms["service_bundle"] = array("bundle_msg", "bundle_components");
break;
case "licenses":
$structure = NULL;
$structure["fieldname"] = "quantity_msg";
$structure["type"] = "message";
$structure["defaultvalue"] = "<i>Because this is a license service, you need to specifiy how many license in the box below. Note that this will only affect billing from the next invoice. If you wish to charge for usage between now and the next invoice, you will need to generate a manual invoice.</i>";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "quantity";
$structure["type"] = "input";
$structure["options"]["req"] = "yes";
$this->obj_form->add_input($structure);
$this->obj_form->subforms["service_options_licenses"] = array("quantity_msg", "quantity");
break;
case "data_traffic":
// help info
$structure = NULL;
$structure["fieldname"] = "traffic_cap_help";
$structure["type"] = "message";
$structure["defaultvalue"] = "<p>If desired, traffic types, data caps and overage changes can be overridden here to customise a service for a particular customer.</p>";
$this->obj_form->add_input($structure);
$this->obj_form->subforms["traffic_caps"][] = "traffic_cap_help";
// header
$structure = NULL;
$structure["fieldname"] = "traffic_cap_header_name";
$structure["type"] = "text";
$structure["defaultvalue"] = lang_trans("header_traffic_cap_name");
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "traffic_cap_header_mode";
$structure["type"] = "text";
$structure["defaultvalue"] = lang_trans("header_traffic_cap_mode");
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "traffic_cap_header_units_included";
$structure["type"] = "text";
$structure["defaultvalue"] = lang_trans("header_traffic_units_included");
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "traffic_cap_header_units_price";
$structure["type"] = "text";
$structure["defaultvalue"] = lang_trans("header_traffic_units_price");
$this->obj_form->add_input($structure);
$this->obj_form->subforms_grouped["traffic_caps"]["traffic_cap_header"][] = "traffic_cap_header_name";
$this->obj_form->subforms_grouped["traffic_caps"]["traffic_cap_header"][] = "traffic_cap_header_mode";
$this->obj_form->subforms_grouped["traffic_caps"]["traffic_cap_header"][] = "traffic_cap_header_units_included";
$this->obj_form->subforms_grouped["traffic_caps"]["traffic_cap_header"][] = "traffic_cap_header_units_price";
$this->obj_form->subforms["traffic_caps"][] = "traffic_cap_header";
// fetch service unitname
$unitname = sql_get_singlevalue("SELECT name as value FROM service_units WHERE id='" . $this->obj_customer->obj_service->data["units"] . "'");
// manual load of override values for data cap services
$data_traffic_overrides = new traffic_caps();
$data_traffic_overrides->id_service = $this->obj_customer->obj_service->id;
$data_traffic_overrides->id_service_customer = $this->obj_customer->id_service_customer;
$data_traffic_overrides->load_data_traffic_caps();
$data_traffic_overrides->load_data_override_caps();
for ($i = 0; $i < $data_traffic_overrides->data_num_rows; $i++) {
// define form fields
$structure = NULL;
$structure["fieldname"] = "traffic_cap_" . $i . "_id";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $data_traffic_overrides->data[$i]["id_type"];
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "traffic_cap_" . $i . "_name";
$structure["type"] = "text";
$structure["defaultvalue"] = $data_traffic_overrides->data[$i]["type_name"];
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "traffic_cap_" . $i . "_mode";
$structure["type"] = "dropdown";
$structure["values"][0] = "unlimited";
$structure["values"][1] = "capped";
$structure["defaultvalue"] = $data_traffic_overrides->data[$i]["cap_mode"];
$structure["options"]["width"] = "100";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "traffic_cap_" . $i . "_units_included";
$structure["type"] = "input";
$structure["options"]["width"] = "100";
$structure["options"]["label"] = " {$unitname}";
$structure["defaultvalue"] = $data_traffic_overrides->data[$i]["cap_units_included"];
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "traffic_cap_" . $i . "_units_price";
$structure["type"] = "money";
$structure["options"]["label"] = " per {$unitname} additional usage.";
$structure["defaultvalue"] = $data_traffic_overrides->data[$i]["cap_units_price"];
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "traffic_cap_" . $i . "_override";
$structure["type"] = "text";
$structure["options"]["nohidden"] = 1;
if (!empty($data_traffic_overrides->data[$i]["override"])) {
$structure["defaultvalue"] = "<span class=\"table_highlight_important\">SERVICE OVERRIDE</span>";
}
$this->obj_form->add_input($structure);
$this->obj_form->subforms_grouped["traffic_caps"]["traffic_cap_" . $i][] = "traffic_cap_" . $i . "_name";
$this->obj_form->subforms_grouped["traffic_caps"]["traffic_cap_" . $i][] = "traffic_cap_" . $i . "_mode";
$this->obj_form->subforms_grouped["traffic_caps"]["traffic_cap_" . $i][] = "traffic_cap_" . $i . "_units_included";
$this->obj_form->subforms_grouped["traffic_caps"]["traffic_cap_" . $i][] = "traffic_cap_" . $i . "_units_price";
$this->obj_form->subforms_grouped["traffic_caps"]["traffic_cap_" . $i][] = "traffic_cap_" . $i . "_override";
$this->obj_form->subforms_grouped["traffic_caps"]["traffic_cap_" . $i][] = "traffic_cap_" . $i . "_id";
$this->obj_form->subforms["traffic_caps"][] = "traffic_cap_" . $i;
}
unset($data_traffic_overrides);
break;
case "phone_single":
// single DDI
$structure = NULL;
$structure["fieldname"] = "phone_ddi_info";
$structure["type"] = "message";
$structure["defaultvalue"] = "<i>You must set the DDI of the phone here for billing purposes</i>";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "phone_ddi_single";
$structure["type"] = "input";
$structure["options"]["req"] = "yes";
$structure["options"]["help"] = "eg: 6412345678";
$this->obj_form->add_input($structure);
if ($GLOBALS["config"]["SERVICE_CDR_LOCAL"] == "prefix") {
/*
Prefix-based local rates are easy, we define the prefix number and
from that we match when doing the rate billing.
*/
$structure = NULL;
$structure["fieldname"] = "phone_local_prefix";
$structure["type"] = "input";
$structure["options"]["req"] = "yes";
$structure["options"]["help"] = "eg: 64123";
$structure["options"]["label"] = " Any calls to numbers matching this prefix will be charged at LOCAL rate.";
$this->obj_form->add_input($structure);
} else {
/*
Handling destination based local calling rates is complex, since we need to:
- fetch a list of all destinations
- include overrides for the service
- include overrides for the customer
- include base zones
- display the label instructing on use
- handle regions that have no destination/description
*/
// fetch all rates, including override rates
$obj_local_rates = new cdr_rate_table_rates_override();
$obj_local_rates->id = $this->obj_customer->obj_service->data["id_rate_table"];
$obj_local_rates->option_type = "customer";
$obj_local_rates->option_type_id = $this->obj_customer->id_service_customer;
$obj_local_rates->option_type_serviceid = $this->obj_customer->obj_service->id;
$obj_local_rates->load_data_rate_all();
$obj_local_rates->load_data_rate_all_override();
// aggregate the destination
$cdr_destinations = array();
$cdr_destinations["NONE"] = 1;
// placeholder for no local region
foreach ($obj_local_rates->data["rates"] as $rate) {
if (!empty($rate["rate_description"])) {
$cdr_destinations[$rate["rate_description"]] = 1;
}
}
$cdr_destinations = array_keys($cdr_destinations);
sort($cdr_destinations);
// generate dropdown object
$structure = NULL;
$structure["fieldname"] = "phone_local_prefix";
$structure["type"] = "dropdown";
$structure["values"] = $cdr_destinations;
$structure["options"]["req"] = "yes";
$structure["options"]["label"] = " Charge calls to any prefix in this region as \"LOCAL\" call rates.";
$this->obj_form->add_input($structure);
}
$this->obj_form->subforms["service_options_ddi"] = array("phone_ddi_info", "phone_ddi_single", "phone_local_prefix");
break;
case "phone_tollfree":
// single DDI
$structure = NULL;
$structure["fieldname"] = "phone_ddi_info";
$structure["type"] = "message";
$structure["defaultvalue"] = "<i>You must set the DDI of the tollfree number here for billing purposes.</i>";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "phone_ddi_single";
$structure["type"] = "input";
$structure["options"]["req"] = "yes";
$this->obj_form->add_input($structure);
$this->obj_form->subforms["service_options_ddi"] = array("phone_ddi_info", "phone_ddi_single");
// trunk options
$structure = NULL;
$structure["fieldname"] = "phone_trunk_info";
$structure["type"] = "message";
$structure["defaultvalue"] = "<i>Define the number of trunks (concurrent calls) that are included in the service, depending on the service plan, there may be additional charges concurred.</i>";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "phone_trunk_included_units";
$structure["type"] = "input";
$structure["options"]["req"] = "yes";
$structure["options"]["width"] = "100";
$structure["options"]["label"] = " trunks included in service base fee.";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "phone_trunk_quantity";
$structure["type"] = "input";
$structure["options"]["req"] = "yes";
$structure["options"]["width"] = "100";
$structure["options"]["label"] = " trunks assigned to customer (any more than included units will be charged at price per additional trunk).";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "phone_trunk_price_extra_units";
$structure["type"] = "money";
$this->obj_form->add_input($structure);
$this->obj_form->subforms["service_options_trunks"] = array("phone_trunk_info", "phone_trunk_included_units", "phone_trunk_quantity", "phone_trunk_price_extra_units");
break;
case "phone_trunk":
/*
TODO: Javascript-based DDI Configuration
//create html string to input into message field to show DDIs
$html_string = "<div id=\"ddi_form\"><table id=\"ddi_table\" cellspacing=\"0\"><tr class=\"table_highlight\">
<td><b>" .lang_trans("ddi_start"). "</b></td>
<td><b>" .lang_trans("ddi_finish"). "</b></td>
<td><b>" .lang_trans("description"). "</b></td>
<td> </td></tr>";
//work out the number of DDI rows needed
if (!isset($_SESSION["error"]["form"][$this->obj_form->formname]))
{
$sql_obj = New sql_query;
$sql_obj->string = "SELECT * FROM services_customers_ddi WHERE id_service_customer = '" .$this->obj_customer->id_service_customer. "'";
$sql_obj->execute();
if ($sql_obj->num_rows())
{
$sql_obj->fetch_array();
if ($sql_obj->data_num_rows < 2)
{
$this->num_ddi_rows = 2;
}
else
{
$this->num_ddi_rows = $sql_obj->data_num_rows+1;
}
}
}
else
{
$this->num_ddi_rows = @security_script_input('/^[0-9]*$/', $_SESSION["error"]["num_ddi_rows"])+1;
}
$structure = NULL;
$structure["fieldname"] = "num_ddi_rows";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $this->num_ddi_rows;
$this->obj_form->add_input($structure);
$this->obj_form->subforms["hidden"][] = "num_ddi_rows";
for ($i= 0; $i < $this->num_ddi_rows; $i++)
{
$html_string .= "<tr class=\"table_highlight\">
<td><input type=\"text\" name=\"ddi_start_$i\" ";
if (isset($sql_obj->data[$i]["ddi_start"]))
{
$html_string .= " value=\"" .$sql_obj->data[$i]["ddi_start"]. "\" /></td>";
}
else
{
$html_string .= " value=\"\" /></td>";
}
$html_string .= "<td><input type=\"text\" name=\"ddi_finish_$i\" ";
if (isset($sql_obj->data[$i]["ddi_finish"]))
{
$html_string .= " value=\"" .$sql_obj->data[$i]["ddi_finish"]. "\" /></td>";
}
else
{
$html_string .= " value=\"\" /></td>";
}
$html_string .= "<td><textarea name=\"description_$i\">";
if (isset($sql_obj->data[$i]["description"]))
{
$html_string .= $sql_obj->data[$i]["description"]. "</textarea></td>";
}
else
{
$html_string .= "</textarea></td>";
}
$html_string .= "<td><input type=\"hidden\" name=\"delete_$i\" ";
if (isset($_SESSION["error"]["form"][$this->obj_form->formname]))
{
$html_string .= " value=\"" .security_script_input_predefined("any",$_SESSION["error"]["delete_$i"]). "\" />";
}
else
{
$html_string .= " value=\"false\" />";
}
$html_string .= "<input type=\"hidden\" name=\"id_$i\" ";
if (isset($_SESSION["error"]["form"][$this->obj_form->formname]))
{
$html_string .= " value=\"" .security_script_input_predefined("any",$_SESSION["error"]["id_$i"]). "\" />";
}
else
{
$html_string .= " value=\"\" />";
}
$html_string .= "<a href=\"\" id=\"delete_link_$i\">delete</a></td></tr>";
}
$html_string .= "</table></div>";
*/
// DDI options
$structure = NULL;
$structure["fieldname"] = "phone_ddi_info";
$structure["type"] = "message";
$structure["defaultvalue"] = "<p>This is a phone trunk service - with this service you are able to have multiple individual DDIs and DDI ranges. Note that it is important to define all the DDIs belonging to this customer, otherwise they may be able to make calls without being charged.<br><br><a class=\"button_small\" href=\"index.php?page=customers/service-ddi.php&id_customer=" . $this->obj_customer->id . "&id_service_customer=" . $this->obj_customer->id_service_customer . "\">Configure Customer's DDIs</a></p>";
// $structure["defaultvalue"] = "<p>This is a phone trunk service - with this service you are able to have multiple individual DDIs and DDI ranges. Note that it is important to define all the DDIs belonging to this customer, otherwise they may be able to make calls without being charged.<br><br>" .$html_string. "</p>";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "phone_ddi_included_units";
$structure["type"] = "input";
$structure["options"]["req"] = "yes";
$structure["options"]["width"] = "100";
$structure["options"]["label"] = " DDI numbers included in service plan fee";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "phone_ddi_price_extra_units";
$structure["type"] = "money";
$this->obj_form->add_input($structure);
// trunk options
$structure = NULL;
$structure["fieldname"] = "phone_trunk_info";
$structure["type"] = "message";
$structure["defaultvalue"] = "<p>Define the number of trunks (concurrent calls) that are included in the service, depending on the service plan, there may be additional charges concurred.</p>";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "phone_trunk_included_units";
$structure["type"] = "input";
$structure["options"]["req"] = "yes";
$structure["options"]["width"] = "100";
$structure["options"]["label"] = " trunks included in service base fee.";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "phone_trunk_quantity";
$structure["type"] = "input";
$structure["options"]["req"] = "yes";
$structure["options"]["width"] = "100";
$structure["options"]["label"] = " trunks assigned to customer (any more than included units will be charged at price per additional trunk).";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "phone_trunk_price_extra_units";
$structure["type"] = "money";
$this->obj_form->add_input($structure);
$this->obj_form->subforms["service_options_ddi"] = array("phone_ddi_info", "phone_ddi_included_units", "phone_ddi_price_extra_units");
$this->obj_form->subforms["service_options_trunks"] = array("phone_trunk_info", "phone_trunk_included_units", "phone_trunk_quantity", "phone_trunk_price_extra_units");
break;
}
/*
Check if item belongs to a bundle - if it does, display
additional information fields.
*/
if ($parentid = $this->obj_customer->service_get_is_bundle_item()) {
// info about bundle
$structure = NULL;
$structure["fieldname"] = "bundle_item_msg";
$structure["type"] = "message";
$structure["defaultvalue"] = "<p>This service is a part of a bundle assigned to this customer - you can enable/disable this service independently, but the customer will still be billed the same base bundle plan fee.</p>";
$this->obj_form->add_input($structure);
// link to parent item
$obj_component = new service();
$obj_component->option_type = "customer";
$obj_component->option_type_id = $parentid;
$obj_component->verify_id_options();
$obj_component->load_data();
$obj_component->load_data_options();
$structure = NULL;
$structure["fieldname"] = "bundle_item_parent";
$structure["type"] = "message";
$structure["defaultvalue"] = "<table class=\"table_highlight\">" . "<tr>" . "<td>Bundle Parent: <b>" . $obj_component->data["name_service"] . "</b></td>" . "<td>" . $obj_component->data["description"] . "</td>" . "<td><a class=\"button_small\" href=\"index.php?page=customers/service-edit.php&id_customer=" . $this->obj_customer->id . "&id_service_customer=" . $obj_component->option_type_id . "\">View Service</a></td>" . "</tr>" . "</table>";
$this->obj_form->add_input($structure);
$this->obj_form->subforms["service_bundle_item"] = array("bundle_item_msg", "bundle_item_parent");
}
} else {
/*
A new service is being added
*/
// basic attributes
$structure = form_helper_prepare_dropdownfromdb("serviceid", "SELECT id, name_service as label FROM services WHERE active='1' ORDER BY name_service");
$structure["options"]["req"] = "yes";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "date_period_first";
$structure["type"] = "date";
$structure["options"]["req"] = "yes";
$structure["defaultvalue"] = date("Y-m-d");
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "description";
$structure["type"] = "textarea";
$this->obj_form->add_input($structure);
$this->obj_form->subforms["service_add"] = array("serviceid", "date_period_first", "description");
// migration mode options - these allow some nifty tricks like creating
// a service period in the previous month to be able to bill for past usage
if ($GLOBALS['config']['SERVICE_MIGRATION_MODE'] == 1) {
$structure = NULL;
$structure["fieldname"] = "migration_date_period_usage_override";
$structure["type"] = "radio";
$structure["values"] = array("migration_use_period_date", "migration_use_usage_date");
$structure["defaultvalue"] = "migration_use_period_date";
$this->obj_form->add_input($structure);
$structure = NULL;
$structure["fieldname"] = "migration_date_period_usage_first";
$structure["type"] = "date";
$this->obj_form->add_input($structure);
$this->obj_form->add_action("migration_date_period_usage_override", "default", "migration_date_period_usage_first", "hide");
$this->obj_form->add_action("migration_date_period_usage_override", "migration_use_usage_date", "migration_date_period_usage_first", "show");
$this->obj_form->subforms["service_migration"] = array("migration_date_period_usage_override", "migration_date_period_usage_first");
}
}
// hidden values
$structure = NULL;
$structure["fieldname"] = "id_customer";
$structure["type"] = "hidden";
$structure["defaultvalue"] = $this->obj_customer->id;
$this->obj_form->add_input($structure);
// submit button
$structure = NULL;
$structure["fieldname"] = "submit";
$structure["type"] = "submit";
if ($this->obj_customer->id_service_customer) {
$structure["defaultvalue"] = "Save Changes";
} else {
$structure["defaultvalue"] = "Add Service";
}
$this->obj_form->add_input($structure);
// define base subforms
$this->obj_form->subforms["hidden"][] = "id_customer";
if (user_permissions_get("customers_write")) {
$this->obj_form->subforms["submit"] = array("submit");
} else {
$this->obj_form->subforms["submit"] = array();
}
// fetch the form data if editing
if ($this->obj_customer->id_service_customer) {
// fetch service data
$this->obj_form->structure["description"]["defaultvalue"] = $this->obj_customer->obj_service->data["description"];
$this->obj_form->structure["name_service"]["defaultvalue"] = $this->obj_customer->obj_service->data["name_service"];
foreach (array_keys($this->obj_customer->obj_service->data) as $option_name) {
if (isset($this->obj_form->structure[$option_name])) {
$this->obj_form->structure[$option_name]["defaultvalue"] = $this->obj_customer->obj_service->data[$option_name];
}
}
// fetch DB data
$this->obj_form->sql_query = "SELECT active, date_period_first, date_period_next, date_period_last FROM `services_customers` WHERE id='" . $this->obj_customer->id_service_customer . "' LIMIT 1";
$this->obj_form->load_data();
}
if (error_check()) {
// load any data returned due to errors
$this->obj_form->load_data_error();
}
}
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 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;
}