/**
* This method is a wrapper for parent::collect. Before launching it,
* we will gather information about traffic usage for all given userids.
*
* @param bool Should we fix invoice (means we call self::setLastInvoiced to latest invoiced date).
* @param bool Should we include the setup fee?
* @param bool Should we include the interval fees?
* @return array All invoice rows
*
* @author Former03 GmbH :: Florian Lippert <*****@*****.**>
*/
function collect($fixInvoice = false, $include_setup_fee = false, $include_interval_fee = false)
{
$traffic_result = $this->db->query('SELECT `' . getModeDetails($this->mode, 'TABLE_PANEL_TRAFFIC', 'key') . '`, `year`, `month`, `day`, SUM(`http`+`ftp_down`+`ftp_up`+`mail`) as traffic FROM `' . getModeDetails($this->mode, 'TABLE_PANEL_TRAFFIC', 'table') . '` WHERE `' . getModeDetails($this->mode, 'TABLE_PANEL_TRAFFIC', 'key') . '` IN ( ' . implode(', ', $this->userIds) . ' ) GROUP BY `' . getModeDetails($this->mode, 'TABLE_PANEL_TRAFFIC', 'key') . '`, `year`, `month`, `day`');
while ($traffic_row = $this->db->fetch_array($traffic_result)) {
if (!isset($this->traffic_data[$traffic_row[getModeDetails($this->mode, 'TABLE_PANEL_TRAFFIC', 'key')]]) || !is_array($this->traffic_data[$traffic_row[getModeDetails($this->mode, 'TABLE_PANEL_TRAFFIC', 'key')]])) {
$this->traffic_data[$traffic_row[getModeDetails($this->mode, 'TABLE_PANEL_TRAFFIC', 'key')]] = array();
}
$date = $traffic_row['year'] . '-' . $traffic_row['month'] . '-' . $traffic_row['day'];
if (!isset($this->traffic_data[$traffic_row[getModeDetails($this->mode, 'TABLE_PANEL_TRAFFIC', 'key')]][$date]) && checkDateArray(transferDateToArray($date))) {
$this->traffic_data[$traffic_row[getModeDetails($this->mode, 'TABLE_PANEL_TRAFFIC', 'key')]][$date] = (int) $traffic_row['traffic'];
}
}
reset($this->userIds);
foreach ($this->userIds as $userId) {
// Using fixed values here, because those settings are always the same. interval_fee will be calculated lateron, when traffic consumption is calculated
$this->service_details[$userId]['service_active'] = '1';
$this->service_details[$userId]['interval_fee'] = '0.00';
$this->service_details[$userId]['interval_length'] = '1';
$this->service_details[$userId]['interval_type'] = 'm';
$this->service_details[$userId]['interval_payment'] = '1';
// Always postpaid, we can't invoice this month/payment_term's traffic, if it hasn't finished yet
$this->service_details[$userId]['setup_fee'] = '0.00';
$this->service_details[$userId]['payment_every'] = '1';
$this->service_details[$userId]['lastinvoiced_date'] = $this->service_details[$userId]['lastinvoiced_date_traffic'];
// We still want to be able to calculate traffic usage in case of no service information
if (($this->service_details[$userId]['lastinvoiced_date'] == '0' || $this->service_details[$userId]['lastinvoiced_date'] == '') && ($this->service_details[$userId]['servicestart_date'] == '0' || $this->service_details[$userId]['servicestart_date'] == '') && isset($this->traffic_data[$userId]) && is_array($this->traffic_data[$userId]) && !empty($this->traffic_data[$userId])) {
// Get the date of first appereance of traffic
ksort($this->traffic_data[$userId]);
$dates = array_keys($this->traffic_data[$userId]);
$this->service_details[$userId]['servicestart_date'] = $dates[0];
}
}
return parent::collect($fixInvoice, $include_setup_fee, $include_interval_fee);
}
/**
* Class constructor of hosting. Gets reference for database connection,
* admin mode and service name.
*
* @param db Reference to database handler
* @param int For admin mode set 1, otherwise 0
* @param string The name of the service
*
* @author Former03 GmbH :: Florian Lippert <*****@*****.**>
*/
function __construct($db, $mode = 0, $service_name = '')
{
$this->endServiceImmediately = true;
$this->toInvoiceTableData = array('table' => getModeDetails($mode, 'TABLE_PANEL_USERS', 'table'), 'keyfield' => getModeDetails($mode, 'TABLE_PANEL_USERS', 'key'), 'condfield' => getModeDetails($mode, 'TABLE_PANEL_USERS', 'key'));
if ($service_name == '') {
$service_name = 'hosting';
}
parent::__construct(&$db, $mode, $service_name);
}
/**
* Calculates invoice fees and stores it in panel_users,
* according to details in billing_service_categories.
*
* @param int The mode
* @param int Userid to begin with Subject, eg tablename.
* @param int Number of Users we should handle in this run
* @param int Single userid we should focus on.
* @return array Results like current invoice fees etc.
*
* @author Former03 GmbH :: Florian Lippert <*****@*****.**>
*/
function cacheInvoiceFees($mode = 0, $begin = null, $count = null, $userid = null)
{
global $db;
$returnval = array();
$service_categories_result = $db->query('SELECT * FROM `' . getModeDetails($mode, 'TABLE_BILLING_SERVICE_CATEGORIES', 'table') . '` ORDER BY `id` ASC');
while ($service_categories_row = $db->fetch_array($service_categories_result)) {
$service_categories[$service_categories_row['category_name']] = $service_categories_row;
if ($service_categories_row['category_cachefield'] != '') {
$zeroUpdates[$service_categories_row['category_cachefield']] = 0;
}
}
if ($userid !== null && intval($userid) !== 0) {
$userSelection = " WHERE `" . getModeDetails($mode, 'TABLE_PANEL_USERS', 'key') . "` = '" . $userid . "' ";
} else {
$userSelection = '';
}
if ($begin !== null && intval($count) !== 0) {
$limit = ' LIMIT ' . intval($begin) . ', ' . intval($count);
} else {
$limit = '';
}
$users = $db->query("SELECT * FROM `" . getModeDetails($mode, 'TABLE_PANEL_USERS', 'table') . "` " . $userSelection . ' ' . $limit);
while ($user = $db->fetch_array($users)) {
if (!isset($user['customer_categories_once'])) {
$user['customer_categories_once'] = '';
}
if (!isset($user['customer_categories_period'])) {
$user['customer_categories_period'] = '';
}
$myInvoice = new invoice($db, $mode, explode('-', $user['customer_categories_once']), explode('-', $user['customer_categories_period']));
if ($myInvoice->collect($user[getModeDetails($mode, 'TABLE_PANEL_USERS', 'key')]) === true) {
$total_fee_taxed = 0;
$myUpdates = $zeroUpdates;
$total_fees_array = $myInvoice->getTotalFee($lng);
foreach ($total_fees_array as $service_type => $total_fee_array) {
if (isset($service_categories[$service_type]) && isset($service_categories[$service_type]['category_cachefield']) && $service_categories[$service_type]['category_cachefield'] != '') {
$myUpdates[$service_categories[$service_type]['category_cachefield']] = $total_fee_array['total_fee_taxed'];
$total_fee_taxed += $total_fee_array['total_fee_taxed'];
}
}
$updates = '';
foreach ($myUpdates as $myField => $myValue) {
$updates .= ', `' . $myField . '` = \'' . $myValue . '\' ';
}
$db->query('UPDATE `' . getModeDetails($mode, 'TABLE_PANEL_USERS', 'table') . '` SET `invoice_fee` = \'' . $total_fee_taxed . '\' ' . $updates . ' WHERE `' . getModeDetails($mode, 'TABLE_PANEL_USERS', 'key') . '` = \'' . $user[getModeDetails($mode, 'TABLE_PANEL_USERS', 'key')] . '\' ');
$returnval[$user[getModeDetails($mode, 'TABLE_PANEL_USERS', 'key')]] = $myUpdates;
$returnval[$user[getModeDetails($mode, 'TABLE_PANEL_USERS', 'key')]]['total'] = $total_fee_taxed;
$returnval[$user[getModeDetails($mode, 'TABLE_PANEL_USERS', 'key')]]['loginname'] = $user['loginname'];
}
}
return $returnval;
}
$invoice_states_option = '';
foreach ($lng['invoice']['states'] as $stateid => $statename) {
if ((int) $result['state'] <= (int) $stateid) {
$invoice_states_option .= makeoption($statename, $stateid, $result['state'], true);
}
}
$contact = htmlentities_array($contact);
eval('echo "' . getTemplate('billing/invoices_edit') . '";');
}
}
}
if ($action == 'pdf') {
$result = $db->query_first('SELECT * FROM `' . getModeDetails($mode, 'TABLE_BILLING_INVOICES', 'table') . '` WHERE `id` = \'' . $id . '\' ');
if ($result['id'] == $id && $id != '0') {
$invoice = new pdfInvoice();
if ((int) $result['state'] >= CONST_BILLING_INVOICESTATE_CANCELLED_NO_REINVOICE) {
$invoice->cancellation = true;
}
$invoice->processData($result['xml'], $lng);
$invoice->outputBrowser();
}
}
if ($action == 'reminder') {
$result = $db->query_first('SELECT * FROM `' . getModeDetails($mode, 'TABLE_BILLING_INVOICES', 'table') . '` WHERE `id` = \'' . $id . '\' ');
if ($result['id'] == $id && $id != '0') {
$invoice = new pdfReminder();
$invoice->processData($result['xml'], $lng);
$invoice->outputBrowser();
}
}
}
ask_yesno('billing_invoice_row_reallyreset', $filename, array('id' => $id, 'mode' => $mode, 'page' => $page, 'action' => $action));
}
}
}
if ($action == 'fixinvoice') {
$invoice_number_preset = strtr($lng['invoice']['invoicenumbertemplate'], array('{number}' => (int) $settings['billing']['invoicenumber_count'] + 1, '{year}' => date('Y'), '{month}' => date('m'), '{day}' => date('d')));
if (isset($_POST['send']) && $_POST['send'] == 'send') {
$invoice_number = validate($_POST['invoice_number'], html_entity_decode($lng['billing']['number']));
if (isset($lng['invoice']['states'][$_POST['state']])) {
$state = intval($_POST['state']);
}
$myInvoice = new invoice(&$db, $mode, explode('-', $user['customer_categories_once']), explode('-', $user['customer_categories_period']));
if ($myInvoice->collect($id, true) === true) {
$invoiceXmlString = $myInvoice->exportXml($lng, $invoice_number);
$invoiceXml = new SimpleXMLElement($invoiceXmlString);
$db->query('INSERT INTO `' . getModeDetails($mode, 'TABLE_BILLING_INVOICES', 'table') . '` (`' . getModeDetails($mode, 'TABLE_BILLING_INVOICES', 'key') . '`, `xml`, `invoice_date`, `invoice_number`, `state`, `state_change`, `total_fee`, `total_fee_taxed`) VALUES(\'' . $db->escape($id) . '\', \'' . $db->escape($invoiceXmlString) . '\', \'' . $db->escape(date('Y-m-d')) . '\', \'' . $db->escape($invoice_number) . '\', \'' . $db->escape($state) . '\', \'' . time() . '\', \'' . $db->escape((string) $invoiceXml->total_fee[0]) . '\', \'' . $db->escape((string) $invoiceXml->total_fee_taxed[0]) . '\' ) ');
if (preg_match('/^' . strtr($lng['invoice']['invoicenumbertemplate'], array('/' => '\\/', '{number}' => '(\\d+)', '{year}' => date('Y'), '{month}' => date('m'), '{day}' => date('d'))) . '$/', $invoice_number, $invoicenumber_count)) {
$db->query('UPDATE `' . TABLE_PANEL_SETTINGS . '` SET `value` = \'' . (int) $invoicenumber_count[1] . '\' WHERE `settinggroup` = \'billing\' AND `varname` = \'invoicenumber_count\'');
}
}
cacheInvoiceFees($mode, null, null, $id);
redirectTo($filename, array('s' => $s, 'mode' => $mode, 'page' => 'overview'));
} else {
$invoice_states_option = '';
foreach ($lng['invoice']['states'] as $stateid => $statename) {
$invoice_states_option .= makeoption($statename, $stateid, $result['state'], true);
}
eval("echo \"" . getTemplate("billing/openinvoices_invoice_fix") . "\";");
}
}
if ($action == 'preview') {
/**
* This method returns the cumulated credit note of all reinvoiced invoices.
*
* @return double Cumulated credit note
*
* @author Former03 GmbH :: Florian Lippert <*****@*****.**>
*/
function getCreditNote()
{
if ($this->userId == 0 || !is_array($this->user) || empty($this->user) || $this->user[getModeDetails($this->mode, 'TABLE_PANEL_USERS', 'key')] != $this->userId) {
return false;
}
$returnval = 0;
foreach ($this->cancelledInvoices as $cancelledInvoice_Id => $cancelledInvoice) {
if ($cancelledInvoice['state'] == CONST_BILLING_INVOICESTATE_CANCELLED_REINVOICE_WITH_CREDIT_NOTE) {
$returnval += $cancelledInvoice['total_fee_taxed'];
}
}
return $returnval;
}
