<?php
/*
*********************************************************************************************************
* daloRADIUS - RADIUS Web Platform
* Copyright (C) 2007 - Liran Tal <hide@address.com> All Rights Reserved.
*
* This program is free software; you can redistribute it and/or
* modify it under the terms of the GNU General Public License
* as published by the Free Software Foundation; either version 2
* of the License, or (at your option) any later version.
*
* You should have received a copy of the GNU General Public License
* along with this program; if not, write to the Free Software
* Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
*
*********************************************************************************************************
* Description:
* returns user billing information (rates, plans, etc)
*
* Authors: Liran Tal <hide@address.com>
*
*********************************************************************************************************
*/
/*
*********************************************************************************************************
* userBillingRatesSummary
* $username username to provide information of
* $startdate starting date, first accounting session
* $enddate ending date, last accounting session
* $ratename the rate to use for calculations
* $drawTable if set to 1 (enabled) a toggled on/off table will be drawn
*
* returns user connection information: uploads, download, session time, total billed, etc...
*
*********************************************************************************************************
*/
function userBillingRatesSummary($username, $startdate, $enddate, $ratename, $drawTable) {
include_once('include/management/pages_common.php');
include 'library/opendb.php';
$username = $dbSocket->escapeSimple($username); // sanitize variable for sql statement
$startdate = $dbSocket->escapeSimple($startdate);
$enddate = $dbSocket->escapeSimple($enddate);
$ratename = $dbSocket->escapeSimple($ratename);
// get rate type
$sql = "SELECT rateType FROM ".$configValues['CONFIG_DB_TBL_DALOBILLINGRATES']." WHERE ".$configValues['CONFIG_DB_TBL_DALOBILLINGRATES'].".rateName = '$ratename'";
$res = $dbSocket->query($sql);
if ($res->numRows() == 0)
return;
$row = $res->fetchRow();
list($ratetypenum, $ratetypetime) = split("/",$row[0]);
switch ($ratetypetime) { // we need to translate any kind of time into seconds, so a minute is 60 seconds, an hour is 3600,
case "second": // and so on...
$multiplicate = 1;
break;
case "minute":
$multiplicate = 60;
break;
case "hour":
$multiplicate = 3600;
break;
case "day":
$multiplicate = 86400;
break;
case "week":
$multiplicate = 604800;
break;
case "month":
$multiplicate = 187488000; // a month is 31 days
break;
default:
$multiplicate = 0;
break;
}
// then the rate cost would be the amount of seconds times the prefix multiplicator thus:
$rateDivisor = ($ratetypenum * $multiplicate);
$sql = "SELECT distinct(".$configValues['CONFIG_DB_TBL_RADACCT'].".username), ".$configValues['CONFIG_DB_TBL_RADACCT'].".NASIPAddress, ".
$configValues['CONFIG_DB_TBL_RADACCT'].".AcctStartTime, SUM(".$configValues['CONFIG_DB_TBL_RADACCT'].".AcctSessionTime) AS AcctSessionTime, ".
$configValues['CONFIG_DB_TBL_DALOBILLINGRATES'].".rateCost, SUM(".$configValues['CONFIG_DB_TBL_RADACCT'].".AcctInputOctets) AS AcctInputOctets, ".
" SUM(".$configValues['CONFIG_DB_TBL_RADACCT'].".AcctOutputOctets) AS AcctOutputOctets ".
" FROM ".$configValues['CONFIG_DB_TBL_RADACCT'].", ".$configValues['CONFIG_DB_TBL_DALOBILLINGRATES']." WHERE (AcctStartTime >= '$startdate') and (AcctStartTime <= '$enddate') and (UserName = '$username') and (".$configValues['CONFIG_DB_TBL_DALOBILLINGRATES'].".rateName = '$ratename') GROUP BY UserName";
$res = $dbSocket->query($sql);
$row = $res->fetchRow(DB_FETCHMODE_ASSOC);
$rateCost = $row['rateCost'];
$userUpload = toxbyte($row['AcctInputOctets']);
$userDownload = toxbyte($row['AcctOutputOctets']);
$userOnlineTime = time2str($row['AcctSessionTime']);
$sessionTime = $row['AcctSessionTime'];
$sumBilled = (($sessionTime/$rateDivisor)*$rateCost);
include 'library/closedb.php';
if ($drawTable == 1) {
echo "<table border='0' class='table1'>";
echo "
<thead>
<tr>
<th colspan='10' align='left'>
<a class=\"table\" href=\"javascript:toggleShowDiv('divBillingRatesSummary')\">Billing Summary</a>
</th>
</tr>
</thead>
</table>
";
echo "
<div id='divBillingRatesSummary' style='display:none;visibility:visible'>
<table border='0' class='table1'>
<thread>
<tr>
<th scope='col' align='right'>
Username
</th>
<th scope='col' align='left'>
$username
</th>
</tr>
<tr>
<th scope='col' align='right'>
Billing for period of
</th>
<th scope='col' align='left'>
$startdate until $enddate (inclusive)
</th>
</tr>
<tr>
<th scope='col' align='right'>
Online Time
</th>
<th scope='col' align='left'>
$userOnlineTime
</th>
</tr>
<tr>
<th scope='col' align='right'>
User Upload
</th>
<th scope='col' align='left'>
$userUpload
</th>
</tr>
<tr>
<th scope='col' align='right'>
User Download
</th>
<th scope='col' align='left'>
$userDownload
</th>
</tr>
<tr>
<th scope='col' align='right'>
Rate Name
</th>
<th scope='col' align='left'>
$ratename
</th>
</tr>
<tr>
<th scope='col' align='right'>
Total Billed
</th>
<th scope='col' align='left'>
$sumBilled
</th>
</tr>
</table>
</div>
";
}
}
/*
*********************************************************************************************************
* userBillingPayPalSummary
* $startdate starting date, first accounting session
* $enddate ending date, last accounting session
* $drawTable if set to 1 (enabled) a toggled on/off table will be drawn
*
* returns user connection information: uploads, download, session time, total billed, etc...
*
*********************************************************************************************************
*/
function userBillingPayPalSummary($startdate, $enddate, $payer_email, $payment_address_status, $payer_status, $payment_status, $drawTable) {
include_once('include/management/pages_common.php');
include 'library/opendb.php';
$startdate = $dbSocket->escapeSimple($startdate);
$enddate = $dbSocket->escapeSimple($enddate);
$payer_email = $dbSocket->escapeSimple($payer_email);
$payment_address_status = $dbSocket->escapeSimple($payment_address_status);
$payer_status = $dbSocket->escapeSimple($payer_status);
$payment_status = $dbSocket->escapeSimple($payment_status);
$sql = "SELECT ".$configValues['CONFIG_DB_TBL_DALOBILLINGPAYPAL'].".Username AS Username, payer_email, planName, planId, SUM(mc_gross) AS mc_gross, SUM(mc_fee) ".
" AS mc_fee, SUM(tax) AS tax, mc_currency, SUM(AcctSessionTime) AS AcctSessionTime, SUM(AcctInputOctets) AS AcctInputOctets, ".
" SUM(AcctOutputOctets) AS AcctOutputOctets ".
" FROM ".
$configValues['CONFIG_DB_TBL_DALOBILLINGPAYPAL']." LEFT JOIN ".$configValues['CONFIG_DB_TBL_RADACCT']." ON ".
$configValues['CONFIG_DB_TBL_DALOBILLINGPAYPAL'].".Username=".$configValues['CONFIG_DB_TBL_RADACCT'].".Username ".
" WHERE ".
" (payer_email LIKE '$payer_email') AND ".
" (payment_address_status = '$payment_address_status') AND ".
" (payer_status = '$payer_status') AND ".
" (payment_status = '$payment_status') AND ".
" (payment_date>'$startdate' AND payment_date<'$enddate')".
" GROUP BY Username";
$res = $dbSocket->query($sql);
if ($res->numRows() == 0)
return;
$row = $res->fetchRow(DB_FETCHMODE_ASSOC);
$planTotalCost = $row['mc_gross'];
$planTotalTax = $row['tax'];
$planTotalFee = $row['mc_fee'];
$userUpload = toxbyte($row['AcctInputOctets']);
$userDownload = toxbyte($row['AcctOutputOctets']);
$userOnlineTime = time2str($row['AcctSessionTime']);
$sessionTime = $row['AcctSessionTime'];
$planCurrency = $row['mc_currency'];
$planName = $row['planName'];
$planId = $row['planId'];
$payer_email = $row['payer_email'];
$username = $row['Username'];
$grossGain = ($planTotalCost-($planTotalTax+$planTotalFee));
include 'library/closedb.php';
if ($drawTable == 1) {
echo "<table border='0' class='table1'>";
echo "
<thead>
<tr>
<th colspan='10' align='left'>
<a class=\"table\" href=\"javascript:toggleShowDiv('divBillingPayPalSummary')\">Billing Summary</a>
</th>
</tr>
</thead>
</table>
";
echo "
<div id='divBillingPayPalSummary' style='display:none;visibility:visible'>
<table border='0' class='table1'>
<thread>
<tr>
<th scope='col' align='right'>
Username
</th>
<th scope='col' align='left'>
$username (email: $payer_email)
</th>
</tr>
<tr>
<th scope='col' align='right'>
Billing for period of
</th>
<th scope='col' align='left'>
$startdate until $enddate (inclusive)
</th>
</tr>
<tr>
<th scope='col' align='right'>
Online Time
</th>
<th scope='col' align='left'>
$userOnlineTime
</th>
</tr>
<tr>
<th scope='col' align='right'>
User Upload
</th>
<th scope='col' align='left'>
$userUpload
</th>
</tr>
<tr>
<th scope='col' align='right'>
User Download
</th>
<th scope='col' align='left'>
$userDownload
</th>
</tr>
<tr>
<th scope='col' align='right'>
Plan name
</th>
<th scope='col' align='left'>
$planName (planId: $planId)
</th>
</tr>
<tr>
<th scope='col' align='right'>
Total Plans Cost <br/> Total Transaction Fees <br/> Total Transaction Taxs
</th>
<th scope='col' align='left'>
$planTotalCost <br/> $planTotalFee <br/> $planTotalTax
</th>
</tr>
<tr>
<th scope='col' align='right'>
Gross Gain
</th>
<th scope='col' align='left'>
$grossGain $planCurrency
</th>
</tr>
</table>
</div>
";
}
}