function quotes_render_summarybox($id)
{
log_debug("inc_quotes", "quotes_render_summarybox({$id})");
// fetch quote information
$sql_obj = new sql_query();
$sql_obj->string = "SELECT code_quote, amount_total, date_validtill, date_sent, sentmethod FROM account_quotes WHERE id='{$id}' LIMIT 1";
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
if ($sql_obj->data[0]["amount_total"] == 0) {
print "<table width=\"100%\" class=\"table_highlight_important\">";
print "<tr>";
print "<td>";
print "<b>Quote " . $sql_obj->data[0]["code_quote"] . " has no items on it</b>";
print "<p>This quote needs to have some items added to it using the links in the nav menu above.</p>";
print "</td>";
print "</tr>";
print "</table>";
} else {
if (time_date_to_timestamp($sql_obj->data[0]["date_validtill"]) <= time()) {
print "<table width=\"100%\" class=\"table_highlight_important\">";
print "<tr>";
print "<td>";
print "<p><b>Quote " . $sql_obj->data[0]["code_quote"] . " has now expired and is no longer valid.</b></p>";
print "</td>";
print "</tr>";
print "</table>";
} else {
print "<table width=\"100%\" class=\"table_highlight_important\">";
print "<tr>";
print "<td>";
print "<b>Quote " . $sql_obj->data[0]["code_quote"] . " is currently valid.</b>";
print "<table cellpadding=\"4\">";
print "<tr>";
print "<td>Quote Total:</td>";
print "<td>" . format_money($sql_obj->data[0]["amount_total"]) . "</td>";
print "</tr>";
print "<tr>";
print "<td>Valid Until:</td>";
print "<td>" . $sql_obj->data[0]["date_validtill"] . "</td>";
print "</tr>";
print "<tr>";
print "<td>Date Sent:</td>";
if ($sql_obj->data[0]["sentmethod"] == "") {
print "<td><i>Has not been sent to customer</i></td>";
} else {
print "<td>" . $sql_obj->data[0]["date_sent"] . " (" . $sql_obj->data[0]["sentmethod"] . ")</td>";
}
print "</tr>";
print "</tr></table>";
print "</td>";
print "</tr>";
print "</table>";
}
}
print "<br>";
}
}
function render_html()
{
// title + summary
print "<h3>TIME REGISTRATION - " . date("l d F Y", mktime(0, 0, 0, $this->date_split[1], $this->date_split[2], $this->date_split[0])) . "</h3><br>";
// links
$date_previous = mktime(0, 0, 0, $this->date_split[1], $this->date_split[2] - 1, $this->date_split[0]);
$date_previous = date("Y-m-d", $date_previous);
$date_next = mktime(0, 0, 0, $this->date_split[1], $this->date_split[2] + 1, $this->date_split[0]);
$date_next = date("Y-m-d", $date_next);
print "<p><b>";
print "<a class=\"button\" href=\"index.php?page=timekeeping/timereg-day.php&date={$date_previous}&employeeid=" . $this->employeeid . "\"><< Previous Day</a>";
if ($this->config_timesheet_booktofuture == "disabled") {
if (time_date_to_timestamp($date_next) < time()) {
print " <a class=\"button\" href=\"index.php?page=timekeeping/timereg-day.php&date={$date_next}&employeeid=" . $this->employeeid . "\">Next Day >></a>";
}
} else {
print " <a class=\"button\" href=\"index.php?page=timekeeping/timereg-day.php&date={$date_next}&employeeid=" . $this->employeeid . "\">Next Day >></a>";
}
print "</b></p><br>";
// Employee selection form
//
// we use a custom form display method here, since the normal form
// class will draw a fully styled form in a table.
//
if ($this->employeeid) {
print "<table class=\"table_highlight\" width=\"100%\"><tr><td width=\"100%\">";
} else {
print "<table class=\"table_highlight_important\" width=\"100%\"><tr><td width=\"100%\">";
}
print "<form method=\"get\" action=\"index.php\">";
print "<p><b>Select an employee to view:</b></p>";
$this->obj_form_employee->render_field("employeeid");
$this->obj_form_employee->render_field("date");
$this->obj_form_employee->render_field("page");
$this->obj_form_employee->render_field("submit");
print "</form>";
print "</td></tr></table><br>";
if ($this->employeeid) {
if (!$this->obj_table_day->data_num_rows) {
format_msgbox("info", "<p><b>There is currently no time registered to this day.</b></p>");
} else {
if (user_permissions_staff_get("timereg_write", $this->employeeid)) {
// edit link
$structure = NULL;
$structure["id"]["column"] = "id";
$structure["date"]["value"] = $this->date;
$this->obj_table_day->add_link("edit", "timekeeping/timereg-day-edit.php", $structure);
// delete link
$structure = NULL;
$structure["id"]["column"] = "id";
$structure["date"]["value"] = $this->date;
$structure["full_link"] = "yes";
$this->obj_table_day->add_link("delete", "timekeeping/timereg-day-delete-process.php", $structure);
}
// display table
$this->obj_table_day->render_table_html();
}
print "<table width=\"100%\">";
// add time link
if (user_permissions_staff_get("timereg_write", $this->employeeid)) {
print "<td align=\"left\" valign=\"top\"><p><a class=\"button\" href=\"index.php?page=timekeeping/timereg-day-edit.php&date=" . $this->date . "&employeeid=" . $this->employeeid . "\">Add new time entry</a></p></td>";
} else {
print "<p><i>You have read-only access to this employee and therefore can not add any more time.</i></p>";
}
// display CSV/PDF download link
if ($this->obj_table_day->data_num_rows) {
print "<td align=\"right\">";
print "<p><a class=\"button_export\" href=\"index-export.php?mode=csv&page=timekeeping/timereg-day.php&date=" . $this->date . "&employeeid=" . $this->employeeid . "\">Export as CSV</a></p>";
print "<p><a class=\"button_export\" href=\"index-export.php?mode=pdf&page=timekeeping/timereg-day.php&date=" . $this->date . "&employeeid=" . $this->employeeid . "\">Export as PDF</a></p>";
print "</td>";
}
print "</table>";
}
}
function render_html()
{
// calcuate next/previous week/year
if ($this->date_selected_weekofyear == 1) {
$date_option_previousyear = $this->date_selected_year - 1;
$date_option_previousweek = 52;
$date_option_nextyear = $this->date_selected_year;
$date_option_nextweek = 2;
} elseif ($this->date_selected_weekofyear == 52) {
$date_option_previousyear = $this->date_selected_year;
$date_option_previousweek = 51;
$date_option_nextyear = $this->date_selected_year + 1;
$date_option_nextweek = 1;
} else {
$date_option_previousyear = $this->date_selected_year;
$date_option_previousweek = $this->date_selected_weekofyear - 1;
$date_option_nextyear = $this->date_selected_year;
$date_option_nextweek = $this->date_selected_weekofyear + 1;
}
// Week view header
print "<h3>TIME REGISTRATION</h3><br><br>";
/*
Unbilled Time
*/
if (user_permissions_get("projects_timegroup")) {
/*
Create an array of all unbilled time records. We need to do the following to create this list:
1. Exclude any internal_only projects.
2. Include time which belongs to a time_group, but ONLY if the time group has not been added to an invoice.
*/
$unbilled_ids = array();
// select non-internal projects
$sql_projects_obj = new sql_query();
$sql_projects_obj->string = "SELECT projects.id as projectid, project_phases.id as phaseid FROM project_phases LEFT JOIN projects ON projects.id = project_phases.projectid WHERE projects.internal_only='0'";
$sql_projects_obj->execute();
if ($sql_projects_obj->num_rows()) {
$sql_projects_obj->fetch_array();
foreach ($sql_projects_obj->data as $project_data) {
// select non-group time records
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id FROM timereg WHERE groupid='0' AND phaseid='" . $project_data["phaseid"] . "'";
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
foreach ($sql_obj->data as $data_tmp) {
// we store the ID inside an array key, since they are unique
// and this will prevent us needed to check for the existance of
// the ID already.
$unbilled_ids[$data_tmp["id"]] = "on";
}
}
unset($sql_obj);
// select unpaid group IDs
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id FROM time_groups WHERE projectid='" . $project_data["projectid"] . "' AND invoiceid='0'";
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
foreach ($sql_obj->data as $data_group) {
// fetch all the time reg IDs belonging this group, but only select time entries marked as billable - we
// don't want to report a timegroup with unbillable time as being billed!
$sql_reg_obj = new sql_query();
$sql_reg_obj->string = "SELECT id FROM timereg WHERE groupid='" . $data_group["id"] . "' AND billable='1'";
$sql_reg_obj->execute();
if ($sql_reg_obj->num_rows()) {
$sql_reg_obj->fetch_array();
foreach ($sql_reg_obj->data as $data_tmp) {
// we store the ID inside an array key, since they are unique
// and this will prevent us needed to check for the existance of
// the ID already.
$unbilled_ids[$data_tmp["id"]] = "on";
}
}
unset($sql_reg_obj);
}
}
unset($sql_obj);
}
}
// fetch amount of unbilled time
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("timereg");
$sql_obj->prepare_sql_addfield("timebooked", "SUM(timereg.time_booked)");
if ($this->access_staff_ids) {
$sql_obj->prepare_sql_addwhere("employeeid IN (" . format_arraytocommastring($this->access_staff_ids) . ")");
}
$sql_obj->prepare_sql_addjoin("LEFT JOIN time_groups ON timereg.groupid = time_groups.id");
// provide list of valid IDs
$unbilled_ids_keys = array_keys($unbilled_ids);
$unbilled_ids_count = count($unbilled_ids_keys);
$unbilled_ids_sql = "";
if ($unbilled_ids_count) {
$i = 0;
foreach ($unbilled_ids_keys as $id) {
$i++;
if ($i == $unbilled_ids_count) {
$unbilled_ids_sql .= "timereg.id='{$id}' ";
} else {
$unbilled_ids_sql .= "timereg.id='{$id}' OR ";
}
}
$sql_obj->prepare_sql_addwhere("({$unbilled_ids_sql})");
$sql_obj->generate_sql();
$sql_obj->execute();
$sql_obj->fetch_array();
list($unbilled_time_hours, $unbilled_time_mins) = explode(":", time_format_hourmins($sql_obj->data[0]["timebooked"]));
if ($unbilled_time_hours > 0 && $unbilled_time_mins > 0) {
$message = "There are currently {$unbilled_time_hours} hours and {$unbilled_time_mins} minutes of unbilled time to be processed. Click here to view.";
} elseif ($unbilled_time_hours > 0) {
$message = "There are currently {$unbilled_time_hours} hours of unbilled time to be processed. Click here to view.";
} elseif ($unbilled_time_mins > 0) {
$message = "There are currently {$unbilled_time_mins} minutes of unbilled time to be processed. Click here to view.";
}
} else {
$message = "There is no unbilled time to be processed.";
}
// display
print "<br>";
format_linkbox("default", "index.php?page=timekeeping/unbilled.php", "<p><b>UNBILLED TIME</b></p><p>{$message}</p>");
}
/*end unbilled time*/
print "<br />";
/*
Time booked
*/
// fetch amount of time booked for today
$sql_obj = new sql_query();
$sql_obj->prepare_sql_settable("timereg");
$sql_obj->prepare_sql_addfield("timebooked", "SUM(timereg.time_booked)");
$sql_obj->prepare_sql_addwhere("date='" . date("Y-m-d") . "'");
if ($this->access_staff_ids) {
$sql_obj->prepare_sql_addwhere("employeeid IN (" . format_arraytocommastring($this->access_staff_ids) . ")");
}
$sql_obj->generate_sql();
$sql_obj->execute();
$sql_obj->fetch_array();
list($booked_time_hours, $booked_time_mins) = explode(":", time_format_hourmins($sql_obj->data[0]["timebooked"]));
if ($booked_time_hours > 0 && $booked_time_mins > 0) {
$message = "<b>Time booked for today: {$booked_time_hours} hours and {$booked_time_mins} minutes.</b><br />Click here to add more time.";
} elseif ($booked_time_hours > 0) {
$message = "<b>Time booked for today: {$booked_time_hours} hours.</b><br />Click here to add more time.";
} elseif ($booked_time_mins > 0) {
$message = "<b>Time booked for today: {$booked_time_mins} minutes.</b><br />Click here to add more time.";
} else {
$message = "<b>No time has been booked for today</b><br />Click here to add time.</b>";
}
format_linkbox("default", "index.php?page=timekeeping/timereg-day-edit.php", "<p>{$message}</p>");
print "<br />";
print "<table class=\"table_highlight\" width=\"100%\"><tr>";
// Week selection links
print "<td width=\"70%\">";
print "<b>WEEK " . $this->date_selected_weekofyear . ", " . $this->date_selected_year . "</b><br>";
print "(" . time_format_humandate($this->date_selected_start) . " to " . time_format_humandate($this->date_selected_end) . ")<br>";
print "<br>";
print "<p><b>";
print "<a class=\"button\" href=\"index.php?page=timekeeping/timereg.php&employeeid=" . $this->employeeid . "&weekofyear=" . $date_option_previousweek . "&year=" . $date_option_previousyear . "\"><< Previous Week</a>";
// check for date in the future
if ($this->config_timesheet_booktofuture == "disabled") {
if (time_date_to_timestamp(time_calculate_weekstart($date_option_nextweek, $date_option_nextyear)) < time()) {
// end date is in not in the future
print " <a class=\"button\" href=\"index.php?page=timekeeping/timereg.php&employeeid=" . $this->employeeid . "&weekofyear=" . $date_option_nextweek . "&year=" . $date_option_nextyear . "\">Next Week >></a>";
}
} else {
print " <a class=\"button\" href=\"index.php?page=timekeeping/timereg.php&employeeid=" . $this->employeeid . "&weekofyear=" . $date_option_nextweek . "&year=" . $date_option_nextyear . "\">Next Week >></a>";
}
print "</b></p>";
print "</td>";
// goto date form
print "<td width=\"30%\">";
print "<form method=\"get\" action=\"index.php\" class=\"form_standard\">";
$this->obj_form_goto->render_field("date");
print "<br>";
$this->obj_form_goto->render_field("page");
$this->obj_form_goto->render_field("submit");
print "</form>";
print "</td>";
print "</tr></table><br>";
// Employee selection form
//
// we use a custom form display method here, since the normal form
// class will draw a fully styled form in a table.
//
if ($this->employeeid) {
print "<table class=\"table_highlight\" width=\"100%\"><tr><td width=\"100%\">";
} else {
print "<table class=\"table_highlight_important\" width=\"100%\"><tr><td width=\"100%\">";
}
print "<form method=\"get\" action=\"index.php\" class=\"form_standard\">";
print "<p><b>Select an employee to view:</b></p>";
$this->obj_form_employee->render_field("employeeid");
$this->obj_form_employee->render_field("weekofyear");
$this->obj_form_employee->render_field("year");
$this->obj_form_employee->render_field("page");
$this->obj_form_employee->render_field("submit");
print "</form>";
print "</td></tr></table><br>";
if ($this->employeeid) {
// custom labels and links
if ($this->config_timesheet_booktofuture == "disabled") {
if (time_date_to_timestamp($this->date_selected_daysofweek[0]) < time()) {
$this->obj_table_week->custom_column_link("monday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[0] . "");
}
if (time_date_to_timestamp($this->date_selected_daysofweek[1]) < time()) {
$this->obj_table_week->custom_column_link("tuesday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[1] . "");
}
if (time_date_to_timestamp($this->date_selected_daysofweek[2]) < time()) {
$this->obj_table_week->custom_column_link("wednesday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[2] . "");
}
if (time_date_to_timestamp($this->date_selected_daysofweek[3]) < time()) {
$this->obj_table_week->custom_column_link("thursday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[3] . "");
}
if (time_date_to_timestamp($this->date_selected_daysofweek[4]) < time()) {
$this->obj_table_week->custom_column_link("friday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[4] . "");
}
if (time_date_to_timestamp($this->date_selected_daysofweek[5]) < time()) {
$this->obj_table_week->custom_column_link("saturday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[5] . "");
}
if (time_date_to_timestamp($this->date_selected_daysofweek[6]) < time()) {
$this->obj_table_week->custom_column_link("sunday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[6] . "");
}
} else {
// add links
$this->obj_table_week->custom_column_link("monday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[0] . "");
$this->obj_table_week->custom_column_link("tuesday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[1] . "");
$this->obj_table_week->custom_column_link("wednesday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[2] . "");
$this->obj_table_week->custom_column_link("thursday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[3] . "");
$this->obj_table_week->custom_column_link("friday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[4] . "");
$this->obj_table_week->custom_column_link("saturday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[5] . "");
$this->obj_table_week->custom_column_link("sunday", "index.php?page=timekeeping/timereg-day.php&date=" . $this->date_selected_daysofweek[6] . "");
}
// column labels
$this->obj_table_week->custom_column_label("monday", "Monday<br><font style=\"font-size: 8px;\">(" . time_format_humandate($this->date_selected_daysofweek[0]) . ")</font>");
$this->obj_table_week->custom_column_label("tuesday", "Tuesday<br><font style=\"font-size: 8px;\">(" . time_format_humandate($this->date_selected_daysofweek[1]) . ")</font>");
$this->obj_table_week->custom_column_label("wednesday", "Wednesday<br><font style=\"font-size: 8px;\">(" . time_format_humandate($this->date_selected_daysofweek[2]) . ")</font>");
$this->obj_table_week->custom_column_label("thursday", "Thursday<br><font style=\"font-size: 8px;\">(" . time_format_humandate($this->date_selected_daysofweek[3]) . ")</font>");
$this->obj_table_week->custom_column_label("friday", "Friday<br><font style=\"font-size: 8px;\">(" . time_format_humandate($this->date_selected_daysofweek[4]) . ")</font>");
$this->obj_table_week->custom_column_label("saturday", "Saturday<br><font style=\"font-size: 8px;\">(" . time_format_humandate($this->date_selected_daysofweek[5]) . ")</font>");
$this->obj_table_week->custom_column_label("sunday", "Sunday<br><font style=\"font-size: 8px;\">(" . time_format_humandate($this->date_selected_daysofweek[6]) . ")</font>");
// display week time table
$this->obj_table_week->render_table_html();
print "<table width=\"100%\">";
// add time link
if (user_permissions_staff_get("timereg_write", $this->employeeid)) {
print "<td align=\"left\" valign=\"top\"><p><a class=\"button\" href=\"index.php?page=timekeeping/timereg-day-edit.php\">Add new time entry</a></p></td>";
} else {
print "<p><i>You have read-only access to this employee and therefore can not add any more time.</i></p>";
}
// display CSV/PDF download link
print "<td align=\"right\">";
print "<p><a class=\"button_export\" href=\"index-export.php?mode=csv&page=timekeeping/timereg.php\">Export as CSV</a></p>";
print "<p><a class=\"button_export\" href=\"index-export.php?mode=pdf&page=timekeeping/timereg.php\">Export as PDF</a></p>";
print "</td>";
print "</table>";
}
}
function generate_pdf()
{
log_debug("invoice", "Executing generate_pdf()");
// load data if required
if (!is_array($this->invoice_fields)) {
$this->load_data();
$this->load_data_export();
}
// start the PDF object
//
// note: the & allows decontructors to operate
// Unfortunatly this trick is now deprecated with PHP 5.3.x and creates unsilencable errors ~JC 20100110
//
// get template filename based on currently selected options
$template_data = sql_get_singlerow("SELECT `template_type`, `template_file` FROM templates WHERE template_type IN('" . $this->type . "_invoice_tex', '" . $this->type . "_invoice_htmltopdf') AND active='1' LIMIT 1");
//exit("<pre>".print_r($template_data, true)."</pre>");
switch ($template_data['template_type']) {
case $this->type . '_invoice_htmltopdf':
$this->obj_pdf = new template_engine_htmltopdf();
$template_file = $template_data['template_file'] . "/index.html";
if (is_dir("../../{$template_data['template_file']}")) {
$this->obj_pdf->set_template_directory("../../{$template_data['template_file']}");
} else {
$this->obj_pdf->set_template_directory("../{$template_data['template_file']}");
}
break;
case $this->type . '_invoice_tex':
default:
$this->obj_pdf = new template_engine_latex();
$template_file = $template_data['template_file'] . ".tex";
break;
}
if (!$template_file) {
// fall back to old version
//
// TODO: we can remove this fallback code once the new templating system is fully implemented, this is to
// just make everything work whilst stuff like quote templates are being added.
//
$template_file = "templates/latex/" . $this->type . "_invoice";
}
// load template
if (file_exists("../../{$template_file}")) {
$this->obj_pdf->prepare_load_template("../../{$template_file}");
} elseif (file_exists("../{$template_file}")) {
$this->obj_pdf->prepare_load_template("../{$template_file}");
} else {
// if we can't find the template file, then something is rather wrong.
log_write("error", "invoice", "Unable to find template file {$template_file}, currently running in directory " . getcwd() . ", fatal error.");
return 0;
}
/*
Company Data
*/
// company logo
$this->obj_pdf->prepare_add_file("company_logo", "png", "COMPANY_LOGO", 0);
/*
Previous Activity
Some invoice PDFs include a "previous activity" statement function displaying past account activity - we only display unpaid
invoices.
*/
$structure_pastactivity = array();
$structure_pastactivity[0] = array();
// reserved for past balance
$amount_outstanding = sql_get_singlevalue("SELECT SUM(amount_total - amount_paid) as value FROM account_ar WHERE customerid='" . $this->data["customerid"] . "' AND id!='" . $this->id . "' AND date_trans <= '" . $this->data["date_trans"] . "'");
$amount_outstanding_past = $amount_outstanding;
$sql_past_obj = new sql_query();
$sql_past_obj->string = "SELECT id, code_invoice, date_trans, amount_total, amount_paid, date_trans FROM account_ar WHERE customerid='" . $this->data["customerid"] . "' AND id!='" . $this->id . "' AND date_trans <= '" . $this->data["date_trans"] . "' ORDER BY date_trans DESC LIMIT 2";
$sql_past_obj->execute();
if ($sql_past_obj->num_rows()) {
$sql_past_obj->fetch_array();
foreach ($sql_past_obj->data as $data_row) {
// invoice
$itemdata = array();
$itemdata["item_date_raw"] = time_date_to_timestamp($data_row["date_trans"]) . "." . $data_row["id"] . "00";
// used to sort items
$itemdata["item_date"] = time_format_humandate($data_row["date_trans"]);
$itemdata["item_details"] = "Invoice " . $data_row["code_invoice"] . "";
$itemdata["item_amount"] = format_money($data_row["amount_total"]);
$structure_pastactivity[] = $itemdata;
// payments (if any)
if ($data_row["amount_paid"] > 0) {
$sql_pay_obj = new sql_query();
$sql_pay_obj->string = "SELECT id, amount FROM account_items WHERE invoiceid='" . $data_row["id"] . "' AND type='payment'";
$sql_pay_obj->execute();
$sql_pay_obj->fetch_array();
foreach ($sql_pay_obj->data as $data_pay) {
// update balance
$amount_outstanding_past = $amount_outstanding_past + $data_pay["amount"];
// source & date
$pay_date = sql_get_singlevalue("SELECT option_value as value FROM account_items_options WHERE itemid='" . $data_pay["id"] . "' AND option_name='DATE_TRANS' LIMIT 1");
$pay_credit = sql_get_singlevalue("SELECT option_value as value FROM account_items_options WHERE itemid='" . $data_pay["id"] . "' AND option_name='CREDIT' LIMIT 1");
// add payment item
$itemdata = array();
$itemdata["item_date_raw"] = time_date_to_timestamp($pay_date) . "." . $data_row["id"] . "01";
// used to sort items
$itemdata["item_date"] = time_format_humandate($pay_date);
if ($pay_credit) {
$itemdata["item_details"] = "Credit applied to invoice " . $data_row["code_invoice"] . "";
} else {
$itemdata["item_details"] = "Payment against invoice " . $data_row["code_invoice"] . "";
}
$itemdata["item_amount"] = "-" . format_money($data_pay["amount"]);
$structure_pastactivity[] = $itemdata;
}
unset($sql_pay_obj);
}
$amount_outstanding_past = $amount_outstanding_past - $data_row["amount_total"];
}
// sort by date, to correct payment & invoice ordering
if (!function_exists("cmp_date")) {
function cmp_date($a, $b)
{
if ($a["item_date_raw"] == $b["item_date_raw"]) {
return 0;
}
return $a["item_date_raw"] < $b["item_date_raw"] ? -1 : 1;
}
}
usort($structure_pastactivity, "cmp_date");
// add previous balance item
$structure_pastactivity[0]["item_date"] = "Previous Balance";
$structure_pastactivity[0]["item_details"] = "";
$structure_pastactivity[0]["item_amount"] = format_money($amount_outstanding_past);
} else {
$itemdata = array();
$itemdata["item_date"] = time_format_humandate(date("Y-m-d"));
$itemdata["item_details"] = "No Past Activity";
$itemdata["item_amount"] = "";
$structure_pastactivity[0] = $itemdata;
$amount_outstanding = "0.00";
}
$this->obj_pdf->prepare_add_array("previous_items", $structure_pastactivity);
$this->obj_pdf->prepare_add_field("amount_outstanding", format_money($amount_outstanding));
unset($structure_pastactivity);
unset($sql_past_obj);
/*
Add general invoice details from load_data_export
*/
$this->invoice_fields["amount_total"] = format_money($this->data["amount_total"] - $this->data["amount_paid"]);
$this->invoice_fields["amount_total_final"] = format_money($this->data["amount_total"] - $this->data["amount_paid"] + $amount_outstanding);
foreach ($this->invoice_fields as $invoice_field_key => $invoice_field_value) {
$this->obj_pdf->prepare_add_field($invoice_field_key, $invoice_field_value);
}
/*
Invoice Items
(excluding tax items - these need to be processed in a different way)
*/
// fetch invoice items
$sql_items_obj = new sql_query();
$sql_items_obj->string = "SELECT " . "id, type, chartid, customid, quantity, units, amount, price, description " . "FROM account_items " . "WHERE invoiceid='" . $this->id . "' " . "AND invoicetype='" . $this->type . "' " . "AND type!='tax' " . "AND type!='payment' " . "ORDER BY type, customid, chartid, description";
$sql_items_obj->execute();
$sql_items_obj->fetch_array();
$structure_invoiceitems = array();
$structure_group_summary = array();
foreach ($sql_items_obj->data as $itemdata) {
$structure = array();
$structure["quantity"] = $itemdata["quantity"];
switch ($itemdata["type"]) {
case "product":
/*
Fetch product code
*/
$sql_obj = new sql_query();
$sql_obj->string = "SELECT code_product FROM products WHERE id='" . $itemdata["customid"] . "' LIMIT 1";
$sql_obj->execute();
$sql_obj->fetch_array();
$structure["info"] = $sql_obj->data[0]["code_product"];
unset($sql_obj);
/*
Fetch discount (if any)
*/
$itemdata["discount"] = sql_get_singlevalue("SELECT option_value as value FROM account_items_options WHERE itemid='" . $itemdata["id"] . "' AND option_name='DISCOUNT'");
/*
Calculate Amount
(Amount field already has discount removed, but we can't use this for export, since we want the line item to be the full
amount, with an additional line item for the discount)
*/
$itemdata["amount"] = $itemdata["price"] * $itemdata["quantity"];
$sql_obj = new sql_query();
$sql_obj->string = "SELECT product_groups.group_name " . " FROM products " . " LEFT JOIN product_groups " . " ON product_groups.id = products.id_product_group " . " WHERE products.id = '" . $itemdata["customid"] . "' " . " LIMIT 1";
$sql_obj->execute();
$sql_obj->fetch_array();
if ($sql_obj->data[0]["group_name"] != null) {
$structure["group"] = $sql_obj->data[0]["group_name"];
} else {
$structure["group"] = lang_trans("group_products");
}
break;
case "time":
/*
Fetch time group ID
*/
$groupid = sql_get_singlevalue("SELECT option_value as value FROM account_items_options WHERE itemid='" . $itemdata["id"] . "' AND option_name='TIMEGROUPID'");
$structure["info"] = sql_get_singlevalue("SELECT CONCAT_WS(' -- ', projects.code_project, time_groups.name_group) as value FROM time_groups LEFT JOIN projects ON projects.id = time_groups.projectid WHERE time_groups.id='{$groupid}' LIMIT 1");
/*
Fetch discount (if any)
*/
$itemdata["discount"] = sql_get_singlevalue("SELECT option_value as value FROM account_items_options WHERE itemid='" . $itemdata["id"] . "' AND option_name='DISCOUNT'");
/*
Calculate Amount
(Amount field already has discount removed, but we can't use this for export, since we want the line item to be the full
amount, with an additional line item for the discount)
*/
$itemdata["amount"] = $itemdata["price"] * $itemdata["quantity"];
$structure["group"] = lang_trans("group_time");
break;
case "service":
case "service_usage":
/*
Fetch Service Name
*/
$sql_obj = new sql_query();
$sql_obj->string = "SELECT name_service FROM services WHERE id='" . $itemdata["customid"] . "' LIMIT 1";
$sql_obj->execute();
$sql_obj->fetch_array();
$structure["info"] = $sql_obj->data[0]["name_service"];
unset($sql_obj);
/*
Fetch discount (if any)
*/
$itemdata["discount"] = sql_get_singlevalue("SELECT option_value as value FROM account_items_options WHERE itemid='" . $itemdata["id"] . "' AND option_name='DISCOUNT'");
/*
Calculate Amount
(Amount field already has discount removed, but we can't use this for export, since we want the line item to be the full
amount, with an additional line item for the discount)
*/
$itemdata["amount"] = $itemdata["price"] * $itemdata["quantity"];
/*
Fetch CDR group if any
*/
$itemdata["CDR_BILLGROUP"] = sql_get_singlevalue("SELECT option_value as value FROM account_items_options WHERE itemid='" . $itemdata["id"] . "' AND option_name='CDR_BILLGROUP'");
/*
Set the service group
This is used for layout and titling purposes on the invoice - there are several options, we need to fetch the service group depending
on whether the service item is a plan or usage item and specific service types might have other group conditions, eg CDR_BILLGROUP.
*/
$sql_obj = new sql_query();
if ($itemdata["type"] == "service_usage") {
if ($itemdata["CDR_BILLGROUP"]) {
$sql_obj->string = "SELECT CONCAT_WS(' ', billgroup_name, 'Call Charges') as group_name FROM cdr_rate_billgroups WHERE id='" . $itemdata["CDR_BILLGROUP"] . "' LIMIT 1";
} else {
$sql_obj->string = "SELECT service_groups.group_name FROM services LEFT JOIN service_groups ON service_groups.id = services.id_service_group_usage WHERE services.id = '" . $itemdata["customid"] . "' LIMIT 1";
}
} else {
$sql_obj->string = "SELECT service_groups.group_name FROM services LEFT JOIN service_groups ON service_groups.id = services.id_service_group WHERE services.id = '" . $itemdata["customid"] . "' LIMIT 1";
}
$sql_obj->execute();
$sql_obj->fetch_array();
$structure["group"] = $sql_obj->data[0]["group_name"];
unset($sql_obj);
break;
case "standard":
/*
Fetch account name and blank a few fields
*/
$sql_obj = new sql_query();
$sql_obj->string = "SELECT CONCAT_WS(' -- ',code_chart,description) as name_account FROM account_charts WHERE id='" . $itemdata["chartid"] . "' LIMIT 1";
$sql_obj->execute();
$sql_obj->fetch_array();
$structure["info"] = $sql_obj->data[0]["name_account"];
$structure["quantity"] = " ";
$itemdata["price"] = NULL;
$structure["group"] = lang_trans("group_other");
$structure["group_num"] = "1";
unset($sql_obj);
break;
}
// define group summary values
if (!isset($structure_group_summary[$structure["group"]])) {
$structure_group_summary[$structure["group"]] = 0;
}
$structure_group_summary[$structure["group"]] += $itemdata["amount"];
// finalise item
$structure["description"] = trim($itemdata["description"]);
$structure["units"] = $itemdata["units"];
if ($itemdata["price"]) {
$structure["price"] = format_money($itemdata["price"]);
} else {
$structure["price"] = "";
}
$structure["amount"] = format_money($itemdata["amount"]);
$structure_invoiceitems[] = $structure;
// if a discount exists, then we add an additional item row for the discount
if (!empty($itemdata["discount"])) {
$structure["description"] = "Discount of " . $itemdata["discount"] . "%";
$structure["quantity"] = "";
$structure["units"] = "";
$structure["price"] = "";
// work out the discount amount to remove
$discount_calc = $itemdata["discount"] / 100;
$discount_calc = $itemdata["amount"] * $discount_calc;
$structure["amount"] = "-" . format_money($discount_calc);
// track for summary report
if (!isset($structure_group_summary["group_discount"])) {
$structure_group_summary["group_discount"] = $discount_calc;
} else {
$structure_group_summary["group_discount"] += $discount_calc;
}
// add extra line item
$structure_invoiceitems[] = $structure;
}
}
foreach ($structure_invoiceitems as $invoice_item) {
$invoice_items_by_group[$invoice_item['group']][] = $invoice_item;
}
ksort($invoice_items_by_group);
if (count($invoice_items_by_group) > 1) {
$structure_invoiceitems = array();
foreach ($invoice_items_by_group as $invoice_item_set) {
$structure_invoiceitems = array_merge($structure_invoiceitems, $invoice_item_set);
}
}
//exit("<pre>".print_r($structure_invoiceitems,true)."</pre>");
$this->obj_pdf->prepare_add_array("invoice_items", $structure_invoiceitems);
unset($sql_items_obj);
/*
Tax Items
*/
// fetch tax items
$sql_tax_obj = new sql_query();
$sql_tax_obj->string = "SELECT " . "account_items.amount, " . "account_taxes.name_tax, " . "account_taxes.taxnumber " . "FROM " . "account_items " . "LEFT JOIN account_taxes ON account_taxes.id = account_items.customid " . "WHERE " . "invoiceid='" . $this->id . "' " . "AND invoicetype='" . $this->type . "' " . "AND type='tax'";
$sql_tax_obj->execute();
if ($sql_tax_obj->num_rows()) {
$sql_tax_obj->fetch_array();
$structure_taxitems = array();
foreach ($sql_tax_obj->data as $taxdata) {
$structure = array();
$structure["name_tax"] = $taxdata["name_tax"];
$structure["taxnumber"] = $taxdata["taxnumber"];
$structure["amount"] = format_money($taxdata["amount"]);
$structure_taxitems[] = $structure;
}
}
$this->obj_pdf->prepare_add_array("taxes", $structure_taxitems);
/*
Payment Items
*/
// fetch payment items
$sql_payment_obj = new sql_query();
$sql_payment_obj->string = "SELECT id, amount, description FROM account_items WHERE invoiceid='" . $this->id . "' AND invoicetype='" . $this->type . "' AND type='payment'";
$sql_payment_obj->execute();
$structure_payments = array();
if ($sql_payment_obj->num_rows()) {
$sql_payment_obj->fetch_array();
foreach ($sql_payment_obj->data as $itemdata) {
$structure = array();
if (sql_get_singlevalue("SELECT option_value as value FROM account_items_options WHERE itemid='" . $itemdata["id"] . "' AND option_name='CREDIT' LIMIT 1")) {
$structure["label"] = "Credit Applied";
} else {
$structure["label"] = "Payment";
}
$structure["amount"] = format_money($itemdata["amount"]);
$structure["date"] = time_format_humandate(sql_get_singlevalue("SELECT option_value as value FROM account_items_options WHERE option_name='DATE_TRANS' AND itemid='" . $itemdata["id"] . "' LIMIT 1"));
$structure_payments[] = $structure;
}
}
$this->obj_pdf->prepare_add_array("invoice_payments", $structure_payments);
/*
Group Summaries
Some invoice templates have a summary header page with totals for each group type. Users can
then read through for full item listings and details.
*/
$stucture_group_summary_final = array();
// add all non-discount groups
foreach (array_keys($structure_group_summary) as $group_name) {
if ($group_name != 'group_discount') {
$structure = array();
$structure["group_name"] = $group_name;
$structure["group_amount"] = format_money($structure_group_summary[$group_name]);
$structure_group_summary_final[] = $structure;
}
}
// add discount group last
if (isset($structure_group_summary["group_discount"]) && $structure_group_summary["group_discount"] > 0) {
$structure = array();
$structure["group_name"] = lang_trans('group_discount');
$structure["group_amount"] = "-" . format_money($structure_group_summary['group_discount']);
$structure_group_summary_final[] = $structure;
}
$this->obj_pdf->prepare_add_array("summary_items", $structure_group_summary_final);
// depending on the billing options we may adjust template display, ensure default is set first
$this->obj_pdf->prepare_add_field('billing_default', 1);
$billing_option = sql_get_singlevalue("SELECT billing_method AS value FROM customers WHERE id='" . $this->data["customerid"] . "'");
log_write("debug", "inc_invoice", "Billing option for customer is: " . $billing_option);
switch ($billing_option) {
case 'direct debit':
$this->obj_pdf->prepare_add_field('billing_direct_debit', 1);
$this->obj_pdf->prepare_add_field('billing_default', '');
break;
case 'manual':
default:
$this->obj_pdf->prepare_add_field('billing_default', 1);
break;
}
/*
Output PDF
*/
// perform string escaping for latex
$this->obj_pdf->prepare_escape_fields();
// fillter template data
$this->obj_pdf->fillter_template_data();
// fill template
$this->obj_pdf->prepare_filltemplate();
/*
Debugging Functions
Debugging invoice generation can be tricky, especially when making large number of
development changes. These functions need to be uncommented in code, future releases
should make them a checkable option.
*/
// display invoice in browser, suitable for HTML-based invoices only
//foreach ($this->obj_pdf->processed as $line)
//{
// $line = str_replace('(tmp_filename)', "../../". $template_data['template_file'] ."/", $line);
// print $line;
//}
// output raw HTML
//print "<pre>";
//print_r($this->obj_pdf->processed);
//print "</pre>";
//die("Terminated Generation");
// generate PDF output
$this->obj_pdf->generate_pdf();
}
$obj_customer->obj_service->id = $data["serviceid"];
if (!$obj_customer->obj_service->verify_id()) {
log_write("error", "process", "Unable to find service " . $obj_customer->obj_service->id . "");
} else {
$obj_customer->obj_service->load_data();
}
}
// make sure the last period date is no earlier than the last current service period
if (!empty($data["date_period_last"]) && $data["date_period_last"] != "0000-00-00") {
// check last current service period
$sql_obj = new sql_query();
$sql_obj->string = "SELECT date_end FROM `services_customers_periods` WHERE id_service_customer='" . $obj_customer->id_service_customer . "' ORDER BY date_end DESC LIMIT 1";
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
if (time_date_to_timestamp($sql_obj->data[0]["date_end"]) > time_date_to_timestamp($data["date_period_last"])) {
// period end date is larger than today - we can not delete the service
// until after the period completes.
log_write("error", "process", "Due to active service periods, the last period date can be no later than " . $sql_obj->data[0]["date_end"] . "");
}
}
}
// end of last period date check
/*
Check for any errors
*/
if (error_check()) {
$_SESSION["error"]["form"]["service_view"] = "failed";
header("Location: ../index.php?page=customers/service-edit.php&id_customer=" . $obj_customer->id . "&id_service_customer=" . $obj_customer->id_service_customer);
exit(0);
} else {
function time_calculate_monthdate_last($date = NULL)
{
log_debug("misc", "Executing time_calculate_monthday_last({$date})");
if (!$date) {
$timestamp = time();
$date = date("Y-m-d", $timestamp);
} else {
$timestamp = time_date_to_timestamp($date);
}
// fetch the final day of the month
$lastday = date("t", $timestamp);
// replace the day with the final day
$date = preg_replace("/-[0-9]*\$/", "-{$lastday}", $date);
// done
return $date;
}
function load_options_form()
{
log_debug("journal_base", "Executing load_options_form()");
/*
Form options can be passed in two ways:
1. POST - this occurs when the options have been passed at the last reload
2. SESSION - if the user goes away and returns.
*/
if (isset($_GET["reset"])) {
// reset the option form
$_SESSION["form"][$this->journalname] = NULL;
} else {
if (isset($_GET["journal_display_options"])) {
// flag custom options as active - this is used to adjust the display of the options dropdown
$_SESSION["form"][$this->journalname]["custom_options_active"] = 1;
log_debug("journal_base", "Loading options form from {$_GET}");
// load filterby options
foreach (array_keys($this->filter) as $fieldname) {
// switch to handle the different input types
// TODO: find a good way to merge this code and the code in the security_form_input_predefined
// into a single function to reduce reuse and complexity.
switch ($this->filter[$fieldname]["type"]) {
case "date":
$this->filter[$fieldname]["defaultvalue"] = @security_script_input("/^[0-9]*-[0-9]*-[0-9]*\$/", $_GET[$fieldname . "_yyyy"] . "-" . $_GET[$fieldname . "_mm"] . "-" . $_GET[$fieldname . "_dd"]);
if ($this->filter[$fieldname]["defaultvalue"] == "--") {
$this->filter[$fieldname]["defaultvalue"] = "";
}
break;
// convert date to timestamp
// convert date to timestamp
case "timestamp_date":
$date = @security_script_input("/^[0-9]*-[0-9]*-[0-9]*\$/", $_GET[$fieldname . "_yyyy"] . "-" . $_GET[$fieldname . "_mm"] . "-" . $_GET[$fieldname . "_dd"]);
if ($date == "--") {
$this->filter[$fieldname]["defaultvalue"] = "";
} else {
$this->filter[$fieldname]["defaultvalue"] = time_date_to_timestamp($date);
}
break;
default:
$this->filter[$fieldname]["defaultvalue"] = @@security_script_input("/^\\S*\$/", $_GET[$fieldname]);
break;
}
// just blank input if it's in error
if (isset($this->filter[$fieldname]["defaultvalue"]) && $this->filter[$fieldname]["defaultvalue"] == "error") {
$this->filter[$fieldname]["defaultvalue"] = "";
}
}
} else {
if (isset($_SESSION["form"][$this->journalname]["filters"])) {
log_debug("journal_base", "Loading options form from session data");
// load filterby options
foreach (array_keys($this->filter) as $fieldname) {
if (isset($_SESSION["form"][$this->journalname]["filters"][$fieldname])) {
$this->filter[$fieldname]["defaultvalue"] = $_SESSION["form"][$this->journalname]["filters"][$fieldname];
}
}
}
}
// save options to session data
foreach (array_keys($this->filter) as $fieldname) {
if (isset($this->filter[$fieldname]["defaultvalue"])) {
$_SESSION["form"][$this->journalname]["filters"][$fieldname] = $this->filter[$fieldname]["defaultvalue"];
}
}
}
return 1;
}
function auditlocking_invoices($type)
{
log_debug("auditlocking", "Executing auditlocking_invoices({$type})");
// fetch number of days to perform locking for
$lockdays = sql_get_singlevalue("SELECT value FROM config WHERE name='ACCOUNTS_INVOICE_LOCK'");
if ($lockdays) {
// fetch all fully paid, unlocked invoices
$sql_obj = new sql_query();
$sql_obj->string = "SELECT id, code_invoice FROM account_{$type} WHERE amount_total=amount_paid AND locked='0'";
$sql_obj->execute();
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
foreach ($sql_obj->data as $data_invoice) {
// hold the highest payment timestamp here - we then run through all the payment items to find the one with the most recent date.
$timestamp = 0;
// fetch all the payment items for this invoice
$sql_item_obj = new sql_query();
$sql_item_obj->string = "SELECT id FROM account_items WHERE invoiceid='" . $data_invoice . "' AND invoicetype='{$type}' AND type='payment'";
$sql_item_obj->execute();
if ($sql_item_obj->num_rows()) {
$sql_item_obj->fetch_array();
foreach ($sql_item_obj->data as $data_item) {
// fetch only the latest payment date
$sql_date_obj = new sql_query();
$sql_date_obj->string = "SELECT option_value FROM account_items_options WHERE option_name='DATE_TRANS' AND itemid='" . $data_item["id"] . "' ORDER BY option_value DESC LIMIT 1";
$sql_date_obj->execute();
$sql_date_obj->fetch_array();
// convert date to timestamp
$timestamp_tmp = time_date_to_timestamp($data_date["option_value"]);
if ($timestamp_tmp > $timestamp) {
$timestamp = $timestamp_tmp;
}
}
}
// if the date is older than (today - ACCOUNTS_INVOICE_LOCK), then lock the invoice
if (mktime() - 86400 * $lockdays > $timestamp) {
// lock invoice
print "Locked {$type} invoice " . $data_invoice["code_invoice"] . "\n";
$sql_obj = new sql_query();
$sql_obj->string = "UPDATE account_{$type} SET locked='1' WHERE id='" . $data_invoice["id"] . "' LIMIT 1";
$sql_obj->execute();
}
}
}
// end of loop through invoices
}
// end of lockdays
}
<?php
/*
admin/auditlock-process.php
Access: admin only
Locks various records based on the options provided by auditlock.php
*/
// includes
include_once "../include/config.php";
include_once "../include/amberphplib/main.php";
if (user_permissions_get("admin")) {
////// INPUT PROCESSING ////////////////////////
$data["date_lock"] = @security_form_input_predefined("date", "date_lock", 1, "");
$data["date_lock_timestamp"] = time_date_to_timestamp($data["date_lock"]);
$data["lock_invoices_open"] = @security_form_input_predefined("any", "lock_invoices_open", 0, "");
$data["lock_journals"] = @security_form_input_predefined("any", "lock_journals", 0, "");
$data["lock_timesheets"] = @security_form_input_predefined("any", "lock_timesheets", 0, "");
//// PROCESS DATA ////////////////////////////
if ($_SESSION["error"]["message"]) {
$_SESSION["error"]["form"]["auditlock"] = "failed";
header("Location: ../index.php?page=admin/auditlock.php");
exit(0);
} else {
$_SESSION["error"] = array();
/*
Start Transaction
*/
$sql_obj = new sql_query();
$sql_obj->trans_begin();
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
}
$timetotal = 0;
if ($sql_obj->num_rows()) {
$sql_obj->fetch_array();
foreach ($sql_obj->data as $data_sql) {
$timetotal += $data_sql["time_booked"];
}
}
// add new value of the current item
$timetotal += $data["time_booked"];
// make sure the totals are less than 24 hours
if ($timetotal > 86400) {
log_write("error", "process", "You can not book more than 24 hours of time in one day.");
}
// make sure user is not trying to book time in the future if the option isn't enabled
if (sql_get_singlevalue("SELECT value FROM config WHERE name='TIMESHEET_BOOKTOFUTURE'") == "disabled") {
if (time_date_to_timestamp($data["date"]) > mktime()) {
log_write("error", "timereg_day_edit-process", "You are not permitted to book time in the future. If you wish to change this behaviour, adjust the TIMESHEET_BOOKTOFUTURE option in the configuration.");
$_SESSION["error"]["date-error"] = 1;
}
}
/// if there was an error, go back to the entry page
if ($_SESSION["error"]["message"]) {
if ($mode == "edit") {
$_SESSION["error"]["form"]["timereg_day"] = "failed";
header("Location: ../index.php?page=timekeeping/timereg-day-edit.php&date=" . $data["date"] . "&editid={$id}");
exit(0);
} else {
$_SESSION["error"]["form"]["timereg_day"] = "failed";
header("Location: ../index.php?page=timekeeping/timereg-day-edit.php&date=" . $data["date"] . "");
exit(0);
}
function invoice_date_calc()
{
log_write("debug", "inc_customers", "Executing invoice_date_calc()");
// we add all the dates to the array and choose the latest date
$dates = array();
// next service billing date
if ($GLOBALS["config"]["ORDERS_BILL_ONSERVICE"] == 1) {
log_write("debug", "inc_customers", "Fetching latest service billing dates for customer " . $this->id . "");
// check the next service billing date
$service_ids = sql_get_singlecol("SELECT id as value FROM services_customers WHERE customerid='" . $this->id . "'");
if (is_array($service_ids)) {
foreach ($service_ids as $serviceid) {
$obj_sql = new sql_query();
$obj_sql->string = "SELECT date_billed FROM services_customers_periods WHERE id_service_customer='" . $serviceid . "' ORDER BY id DESC LIMIT 1";
$obj_sql->execute();
if ($obj_sql->num_rows()) {
$obj_sql->fetch_array();
$dates[] = $obj_sql->data[0]["date_billed"];
}
log_write("debug", "inc_customers", "No periods exist for id_service_customer of " . $serviceid . ", perhaps this service has yet to be activated");
}
}
}
// end of month date
if ($GLOBALS["config"]["ORDERS_BILL_ENDOFMONTH"] == 1) {
log_write("debug", "inc_customers", "Fetching end of month date");
$dates[] = time_calculate_monthdate_last(date("Y-m-d"));
}
// determine the latest date
$timestamp_today = time_date_to_timestamp(date("Y-m-d"));
// we use this to avoid hours/mins
$timestamp_nextbill;
foreach ($dates as $date) {
$date_t = explode("-", $date);
$date_t = mktime(0, 0, 0, $date_t[1], $date_t[2], $date_t[0]);
if ($date_t >= $timestamp_today) {
// future date
if (empty($timestamp_nextbill)) {
$timestamp_nextbill = $date_t;
} else {
if ($date_t < $timestamp_nextbill) {
// closer than current date
$timestamp_nextbill = $date_t;
}
}
}
}
if (empty($timestamp_nextbill)) {
$date = "Manual Invoice Only";
} else {
$date = date("Y-m-d", $timestamp_nextbill);
}
log_write("debug", "inc_customers", "Calculated next billing date for customer orders to be \"{$date}\"");
return $date;
}
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;
}