Нашел поиском два запроса.
SELECT id, uname, INET_NTOA(ip & 4294967295) as ip FROM ip_groups;
И собственно второй отчет по ip:
SELECT account_id, slink_id, t_class, SUM(bytes), base_cost, SUM(discount), ipid FROM discount_transactions_iptraffic_all WHERE discount_date>='1246406400' AND discount_date <='1249084800'
GROUP BY t_class,base_cost,account_id,slink_id,ipid order by account_id, ipid, t_class, base_cost;
Как объединить эти 2 запроса чтобы;
1. Вывелась информация о uname, IP адресе и кол-ве потребленного траффика.
2. Данные брались из группы безлимитчиков (в нашем случае '104')
Запрос по траффику по ip, group, bytes
Посмотри мож что выбереш для себя, я там делаю вначале запрос на все активные аккаунты а потом ещё одним запросом вытаскиваю трафик на каждого.. Увы лучче не придумал..
Код: Выделить всё
<?php
function addopt ($str, $val, $select)
{
if ($select)
{
$ret='<OPTION value='.$val.' selected>'.$str.'</OPTION>';
} else
{
$ret='<OPTION value='.$val.'>'.$str.'</OPTION>';
}
return $ret;
}
$btn=$_GET['btn'];
if (isset($btn))
{
$d1=$_GET['daystart'];
$d2=$_GET['dayend'];
$m1=$_GET['monthstart'];
$m2=$_GET['monthend'];
$y1=$_GET['yearstart'];
$y2=$_GET['yearend'];
$daily=$_GET['daily'];
} else
{
$d2=date("d");
$m1=date("m");
$m2=$m1;
$y1=$y2=date("y");
// echo date("y");
$d1=mktime(0, 0, 0, $m1, 1, $y);
$d1=date("d",$d1);
}
print " <form method=GET action=./>
<table width=500 border=1 bgcolor=#739CDB bordercolor=#5283CF cellspacing=0 cellpadding=5>
<tr>
<td colspan=2><font color=#880000><b>Внимание, данная операция ресурсоемкая, поэтому в зависимости от заданного интервала времени занимает от 5 до 60 сек.</b></font>
</td>
</tr>
<tr>
<td>Дата начала:<br>
<SELECT class=text_i name=day1 ONCHANGE=\"form.daystart.value=options[this.selectedIndex].value\">";
$x=1;
while ($x<32)
{
if ($x<10)
{
$y='0';
} else
{
$y='';
}
if ($x==$d1)
{
print addopt ($y.$x,$x,1);
} else
{
print addopt ($y.$x,$x,0);
}
$x++;
}
print '</SELECT>
<SELECT class=text_i name=month1 ONCHANGE="form.monthstart.value=options[this.selectedIndex].value">';
if ($m1==1) {print addopt ('январь',1,1);} else {print addopt ('январь',1,0);}
if ($m1==2) {print addopt ('февраль',2,1);} else {print addopt ('февраль',2,0);}
if ($m1==3) {print addopt ('март',3,1);} else {print addopt ('март',3,0);}
if ($m1==4) {print addopt ('апрель',4,1);} else {print addopt ('апрель',4,0);}
if ($m1==5) {print addopt ('май',5,1);} else {print addopt ('май',5,0);}
if ($m1==6) {print addopt ('июнь',6,1);} else {print addopt ('июнь',6,0);}
if ($m1==7) {print addopt ('июль',7,1);} else {print addopt ('июль',7,0);}
if ($m1==8) {print addopt ('август',8,1);} else {print addopt ('август',8,0);}
if ($m1==9) {print addopt ('сентябрь',9,1);} else {print addopt ('сентябрь',9,0);}
if ($m1==10) {print addopt ('октябрь',10,1);} else {print addopt ('октябрь',10,0);}
if ($m1==11) {print addopt ('ноябрь',11,1);} else {print addopt ('ноябрь',11,0);}
if ($m1==12) {print addopt ('декабрь',12,1);} else {print addopt ('декабрь',12,0);}
print '</SELECT>
<SELECT class=text_i name=year1 ONCHANGE="form.yearstart.value=options[this.selectedIndex].value">';
if ($y1=="09") {print addopt (2009,2009,1);} else {print addopt (2009,2009,0);}
if ($y1=="08") {print addopt (2008,2008,1);} else {print addopt (2008,2008,0);}
// if ($y1==2007) {print addopt (2007,2007,1);} else {print addopt (2007,2007,0);}
// if ($y1==2006) {print addopt (2006,2006,1);} else {print addopt (2006,2006,0);}
print '</SELECT>
<td class=text2c>Дата окончания:<br>
<SELECT class=text_i name=day2 ONCHANGE="form.dayend.value=options[this.selectedIndex].value">';
$x=1;
while ($x<32)
{
if ($x<10)
{
$y='0';
} else
{
$y='';
}
if ($x==$d2)
{
print addopt ($y.$x,$x,1);
} else
{
print addopt ($y.$x,$x,0);
}
$x++;
}
print '</SELECT>
<SELECT class=text_i name=month2 ONCHANGE="form.monthend.value=options[this.selectedIndex].value">';
if ($m2==1) {print addopt ('январь',1,1);} else {print addopt ('январь',1,0);}
if ($m2==2) {print addopt ('февраль',2,1);} else {print addopt ('февраль',2,0);}
if ($m2==3) {print addopt ('март',3,1);} else {print addopt ('март',3,0);}
if ($m2==4) {print addopt ('апрель',4,1);} else {print addopt ('апрель',4,0);}
if ($m2==5) {print addopt ('май',5,1);} else {print addopt ('май',5,0);}
if ($m2==6) {print addopt ('июнь',6,1);} else {print addopt ('июнь',6,0);}
if ($m2==7) {print addopt ('июль',7,1);} else {print addopt ('июль',7,0);}
if ($m2==8) {print addopt ('август',8,1);} else {print addopt ('август',8,0);}
if ($m2==9) {print addopt ('сентябрь',9,1);} else {print addopt ('сентябрь',9,0);}
if ($m2==10) {print addopt ('октябрь',10,1);} else {print addopt ('октябрь',10,0);}
if ($m2==11) {print addopt ('ноябрь',11,1);} else {print addopt ('ноябрь',11,0);}
if ($m2==12) {print addopt ('декабрь',12,1);} else {print addopt ('декабрь',12,0);}
print '</SELECT>
<SELECT class=text_i name=year2 ONCHANGE="form.yearend.value=options[this.selectedIndex].value">';
if ($y2=="09") {print addopt (2009,2009,1);} else {print addopt (2009,2009,0);}
if ($y2=="08") {print addopt (2008,2008,1);} else {print addopt (2008,2008,0);}
print '</SELECT>
</td></tr>
<tr>
<td colspan=2 align=center class=text2c>
<input type=hidden value="'.$d1.'" name=daystart>
<input type=hidden value="'.$m1.'" name=monthstart>
<input type=hidden value="'.$y1.'" name=yearstart>
<input type=hidden value="'.$d2.'" name=dayend>
<input type=hidden value="'.$m2.'" name=monthend>
<input type=hidden value="'.$y2.'" name=yearend>
<input type=hidden value="1" name=btn>
<input type=hidden value="2" name=mode>
<input type=hidden value="22" name=type>';
print ' <input type=submit value="Показать"></td>
</tr>
</table>
</form>';
$data_1=mktime(0, 0, 0, $m1, $d1, $y1);
$data_2=mktime(23, 59, 59, $m2, $d2, $y2);
if ($btn == "1")
{
$d=$data_2-$data_1;
if ($d > 2681999)
{
echo $data_1;
echo "<br>";
echo $data_2;
echo "<br>";
echo "Промежуток выбора дат должен быть не более 1 месяца.";
die;
}
$q='
SELECT
users.id AS uid,
users.basic_account AS aid,
users.full_name AS name,
users.actual_address AS address,
users.building as dom,
users.flat_number as kv,
ip_groups.ip,
accounts.int_status as status,
accounts.is_blocked as block
FROM
users
Inner Join accounts ON users.basic_account = accounts.id
Inner Join service_links ON accounts.id = service_links.account_id
Inner Join iptraffic_service_links ON service_links.id = iptraffic_service_links.id
Inner Join ip_groups ON ip_groups.ip_group_id = iptraffic_service_links.ip_group_id
WHERE
users.is_deleted = 0 AND
accounts.is_deleted = 0 AND
service_links.is_deleted = 0 AND
iptraffic_service_links.is_deleted = 0 AND
ip_groups.is_deleted = 0 AND NOT
users.id IN '.$SkipIDList.'
ORDER BY
users.id
';
$result=pg_query ($q);
print "<table width=95% border=1 bgcolor=#739CDB bordercolor=#5283CF cellspacing=0 cellpadding=5>
<tr>
<td width=30 align=center>id</td>
<td align=center>Наименование</td>
<td align=center>Адрес</td>
<td align=center>IP</td>
<td align=center>Траффик</td>
<td align=center>Статус</td>
</tr>";
while ($row = pg_fetch_assoc($result))
{
$uid=$row['uid'];
$name=iconv("UTF-8","windows-1251", ($row['name']));
$address=iconv("UTF-8","windows-1251", ($row['address']));
$dom=iconv("UTF-8","windows-1251", ($row['dom']));
$kv=iconv("UTF-8","windows-1251", ($row['kv']));
$status=$row['status'];
$block=$row['block'];
//Ссылка на профиль
$linkprofile = "<a href=./?mode=4&uid=".$row[uid]."&aid=".$row[aid]."&type=41 title='Просмотр профиля'>";
$q1 = '
SELECT
discount_transactions_iptraffic_all.account_id,
Sum(discount_transactions_iptraffic_all.bytes) AS bytes,
discount_transactions_iptraffic_all.t_class
FROM
discount_transactions_iptraffic_all
WHERE
discount_transactions_iptraffic_all.account_id = '.$row[aid].' AND
discount_transactions_iptraffic_all.discount_date >= '.$data_1.' AND
discount_transactions_iptraffic_all.discount_date <= '.$data_2.' AND
(discount_transactions_iptraffic_all.t_class = 10 OR
discount_transactions_iptraffic_all.t_class = 0)
GROUP BY
discount_transactions_iptraffic_all.account_id,
discount_transactions_iptraffic_all.t_class
';
$result1=pg_query ($q1);
$traff = 0;
while ($row1 = pg_fetch_assoc($result1))
{
$traff1 = round($row1['bytes']/1024/1024, 2);
$traff = $traff + $traff1;
}
$traff = str_replace("." , "," , $traff);
if ($status == "1")
{
print "<tr>
<td width=30 align=center> ".$uid."</td>
<td align=left>".$linkprofile."<font color=#000000>".$name."</font></a></td>
<td align=left> ". iconv("UTF-8","windows-1251", ($row['address']))." ".
iconv("UTF-8","windows-1251", ($row['dom']))." ".
iconv("UTF-8","windows-1251", ($row['kv']))."</td>
<td align=left> ".long2ip($row['ip'])."</td>
<td align=right> ".$traff."</td>
<td align=right>Вкл</td>
</tr>";
}else{
print "<tr>
<td bgcolor=#746E99 width=30 align=center> ".$uid."</td>
<td bgcolor=#746E99 align=left>".$linkprofile."<font color=#000000>".$name."</font></a></td>
<td bgcolor=#746E99 align=left><font color=#000000> ". iconv("UTF-8","windows-1251", ($row['address']))." ".
iconv("UTF-8","windows-1251", ($row['dom']))." ".
iconv("UTF-8","windows-1251", ($row['kv']))."</font></td>
<td bgcolor=#746E99 align=left> ".long2ip($row['ip'])."</td>
<td bgcolor=#746E99 align=right> ".$traff."</td>
<td bgcolor=#746E99 align=right>Выкл</td>
</tr>";
}
}
print "</table>";
}
?>Спасиба за помощь, я вот так сделал.
Код: Выделить всё
SELECT discount_transactions_iptraffic_all.account_id, SUM(bytes) AS bytes,
inet_ntoa(ip_groups.ip & 0xFFFFFFFF) AS ip, inet_ntoa(ip_groups.mask & 0xFFFFFFFF) AS mask
FROM discount_transactions_iptraffic_all
INNER JOIN account_tariff_link INNER JOIN users INNER JOIN ip_groups
WHERE discount_transactions_iptraffic_all.discount_date>=UNIX_TIMESTAMP('2009-06-01 00:00:00')
AND discount_transactions_iptraffic_all.discount_date<=UNIX_TIMESTAMP('2009-07-01 00:00:00')
AND discount_transactions_iptraffic_all.account_id=account_tariff_link.account_id AND
discount_transactions_iptraffic_all.t_class='10'
AND users.id=account_tariff_link.account_id AND ip_groups.uname=users.login
GROUP BY ip ORDER by bytes;