Конструктор отчетов

SUMMA_IN as SUMMA,
OPERATOR_DATE_IN as DATA
from PAY_LOG
where MSG_OUT='ACCEPTED' and ACT_IN='PAY' and PAY_OPERATOR='Qiwiwallet'

from abonents grp

cast(avg(ss) as numeric(18,2)) as "Выручка",
year_number as "Год",
month_number as "Месяц"
from
(select sum(summ) as ss,year_number,month_number from counters where (current_date - cast(year_number || '-' || month_number || '-01' as date) <= :cnt.Количество дней до текущей даты$)
group by ABONENT_ID,year_number,month_number)

A.PARENT_ID,
(select NAME
from ABONENTS
where ID = A.PARENT_ID),
sum(C.SUMM),
'Насчитано на ' || count(C.ABONENT_ID) || ' из ' ||(select count(*)
from ABONENTS
where DELETED = 0 and IS_FOLDER = 0 and PARENT_ID = A.PARENT_ID)
from ABONENTS A
join(select sum(SUMM) as SUMM,
ABONENT_ID,
cast(S_DATE as date) as DT
from COUNTERS
group by 2, 3) as C on C.ABONENT_ID = A.ID
where A.DELETED = 0 and C.SUMM > 0
group by 1, 2, 3

union

select C.DT,
9999999,
'Сумма',
sum(C.SUMM),
'Насчитано на ' || count(C.ABONENT_ID) || ' из ' ||(select count(*)
from ABONENTS
where DELETED = 0 and IS_FOLDER = 0)
from ABONENTS A
join(select sum(SUMM) as SUMM,
ABONENT_ID,
cast(S_DATE as date) as DT
from COUNTERS
group by 2, 3) as C on C.ABONENT_ID = A.ID
where A.DELETED = 0 and C.SUMM > 0
group by 1, 2, 3

h.street as "Улица",
h.s_number as "Номер дома",
a.home_entrance as "Номер подъезда",
a.A_HOME_NUMBER as "Номер квартиры",
a.name as "ФИО",
t.name as "Тариф"
from abonents_block ab
left join abonents a on ab.abonent_id=a.id
left join homes h on a.home_id=h.id
left join tarif t on a.tarif_id=t.id
left join objects_status os on a.id=os.object_id
where ab.b_negbal=1 and a.tarif_id in (21,22,23,24) and (os.status<>5 or os.status is null)
group by 1,2,3,4,5,6

ipp.name as "Pull",
(case when ipp.enabled <> 0 THEN 'Да' ELSE 'Нет' END) as "Включен"
from pull_cache pc
left join ip_pull ipp on ipp.pull_id = pc.pull_id
where
pc.user_id is null

SUMMA_IN as "Сумма"
from pay_log
where (operator_src_ip='213.208.182.172' or pay_operator='Uniteller')
and act_in='pay'
and operator_date_in between ':C_даты|date$' and ':По_дату|date$'

A.CONTRACT_NUMBER,
A.NAME,
count(*)
from USERS_USLUGA UU
left join ABONENTS A on UU.ABONENT_ID = A.ID
where UU.USLUGA_ID = ':ID услуги$' and UU.IS_ZAKAZANO = 1 and UU.CREATE_DATE between ':C_даты|date$' and ':По_дату|date$'
group by A.ID, A.CONTRACT_NUMBER, A.NAME
having count(*)>1

A.CONTRACT_NUMBER as "Номер договора",
C.MONTH_NUMBER as "номер месяца",
C.SUMM as "Прибыть"
from COUNTERS C
left join ABONENTS A on C.ABONENT_ID = A.ID
where (USLUGA_ID = -5)
union
select '#ИТОГО',
'',
'',
round(sum(C.SUMM), 2) as "общая сумма"
from COUNTERS C
where (USLUGA_ID = -5)
union
select '#ИТОГО КОЛ-ВО РАЗ АКТИВИРОВАННА УСЛУГА',
'',
'',
round(count(A.ID), 0) as "общая сумма"
from COUNTERS C
left join ABONENTS A on C.ABONENT_ID = A.ID
where (USLUGA_ID = -5)

C.SUMM as "Прибыть"
from COUNTERS C
left join ABONENTS A on C.ABONENT_ID = A.ID
where (USLUGA_ID = -5)
and MONTH_NUMBER = ':номер месяца(1-12)$'
union
select '#ИТОГО',
'',
round(sum(C.SUMM), 2) as "общая сумма"
from COUNTERS C
where (USLUGA_ID = -5)
and MONTH_NUMBER = ':номер месяца(1-12)$'
union
select '#ИТОГО КОЛ-ВО РАЗ АКТИВИРОВАННА УСЛУГА',
'',
round(count(A.ID), 0) as "общая сумма"
from COUNTERS C
left join ABONENTS A on C.ABONENT_ID = A.ID
where (USLUGA_ID = -5)
and MONTH_NUMBER = ':номер месяца(1-12)$'

cast(H.STREET as varchar(128)) as "Улица",
cast(H.S_NUMBER as varchar(128)) as "№ дома",
cast(AB.A_HOME_NUMBER as varchar(128)) as "№ квартиры",
cast(UF_IP2STRING(U.IP) as varchar(128)) as "IP-адрес",
cast((time '00:00:00' + datediff(second, RS.START_TIME, current_timestamp)) as varchar(128)) as "время в онлайне"
from USERS_RADIUSAUTH UR
left join USERS U on UR.USER_ID = U.ID
left join ABONENTS AB on U.ABONENT_ID = AB.ID
left join HOMES H on AB.HOME_ID = H.ID
left join RADIUS_SESSIONS RS on UR.ACCT_SESSION_ID = RS.ACCT_SESSION_ID
where UR.LOGGED = 1
union
select cast('#ИТОГО ПОЛЬЗОВАТЕЛЕЙ В ОНЛАЙНЕ' as varchar(128)) as "логин",
cast('' as varchar(128)) as "Улица",
cast('' as varchar(128)) as "№ дома",
cast('' as varchar(128)) as "№ квартиры",
cast('' as varchar(128)) as "IP-адрес",
cast(count(UR.LOGGED) as varchar(128)) as "время в онлайне"

from USERS_RADIUSAUTH UR
where UR.LOGGED = 1

left join ADMIN_ACCOUNTS AC on A.ACCOUNT_ID = AC.ID
left join HOMES H on A.HOME_ID = H.ID

(select count(1)
from USERS_USLUGA UUA
left join ABONENTS ABA on UUA.ABONENT_ID = ABA.ID
where (UUA.USLUGA_ID = 1247
or UUA.USLUGA_ID = 1248
or UUA.USLUGA_ID = 1249)
and UUA.DELETED = 0
and ABA.ID = AB.ID) as "Кол-во обещанных платежей"

from USERS_USLUGA UUA
left join ABONENTS ABA on UUA.ABONENT_ID = ABA.ID
where (UUA.USLUGA_ID = 1247
or UUA.USLUGA_ID = 1248
or UUA.USLUGA_ID = 1249)

from
abonents ab left join abonents_block abb
on ab.id = abb.abonent_id
where
abb.id is null and
ab.deleted = 0 and

from
abonents ab inner join abonents_block abb
on ab.id = abb.abonent_id

from
arch_account_stack aas left join abonents ab
on aas.abonent_id = ab.id

where CONST_ID = 1) as numeric(18,5)), 2) as "Баланс"
from (select H.CITY as "CITY",
H.STREET as "STREET",
H.S_NUMBER as "SNUMBER",
CAST(A.A_HOME_NUMBER AS INTEGER) as "AHOMENUMBER",
A.NAME as "FIO",
A.SMS as "PHONE",
T.NAME as "TNAME",
A.ACCOUNT_ID as "AAID"
from ABONENTS A
left join ABONENTS_BLOCK AB on AB.ABONENT_ID = A.ID
left join HOMES H on A.HOME_ID = H.ID
left join TARIF T on A.TARIF_ID = T.ID

a.name "ФИО",
(h.street||' '||h.s_number||' '||a.a_home_number) as "Адрес",
(aa.ostatok + aa.debit - aa.credit)/10000000000.00 as "Баланс"
from abonents a
inner join admin_accounts aa
on a.account_id = aa.id
left join homes h
on a.home_id = h.id

name as "ФИО",
(h.street||' '||h.s_number||' '||a.a_home_number) as "Адрес",
uu.enable_date as "Дата подключения",
(aa.ostatok + aa.debit - aa.credit)/10000000000.00 as "Баланс"
from abonents a
left join admin_accounts aa on aa.id = a.account_id

a.name as "ФИО",
(h.street||' '||h.s_number||' '||a.a_home_number) as "Адрес",
(aa.ostatok + aa.debit - aa.credit)/10000000000.00 as "Баланс"
from abonents a
left join admin_accounts aa on aa.id = a.account_id

list(a.a_home_number) as "Квартиры"
from
abonents a
left join abonents_block ab
on a.id = ab.abonent_id
inner join homes h
on a.home_id = h.id
where
h.street = ':Улица$'
and
a.is_folder = 0
and
(ab.abonent_id is null
or
(ab.b_negbal = 1
and

(':Конец|date$') as "Период окончание",
(
select (sum(aas.credit) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1)) from arch_account_stack aas
left join abonents ab on aas.abonent_id = ab.id
where aas.bill_date between ':Начало|date$' and ':Конец|date$ 23.59.59'
and aas.storno=0
and (upper(aas.descr) like 'Абонентская плата' or upper(aas.descr) like 'Трафик')
and ab.company = 0
) as "Абон. плата физ.",
(
select (sum(aas.credit_adjust) /(select CONST_VALUE from VPN_CONST where CONST_ID = 1))from arch_account_stack aas
left join abonents ab on aas.abonent_id = ab.id
where aas.bill_date between ':Начало|date$' and ':Конец|date$ 23.59.59'
and (upper(aas.descr) like 'Абонентская плата' or upper(aas.descr) like 'Трафик' )
and ab.company = 1
and aas.storno=0
) as "Абон. плата юр.",
(
select (sum(fo.op_summa) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1)) from finance_operations fo
left join abonents ab on fo.abonent_id = ab.id
where fo.op_type = 2 and fo.op_date between ':Начало|date$' and ':Конец|date$ 23.59.59'
and fo.op_summa > 0
and ab.company = 0
) as "Приходы физ.",
(
select (sum(fo.op_summa) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1)) from finance_operations fo
left join abonents ab on fo.abonent_id = ab.id
where fo.op_type = 2 and fo.op_date between ':Начало|date$' and ':Конец|date$ 23.59.59'
and fo.op_summa > 0
and ab.company = 1
) as "Приходы юр.",
(
select (sum(fo.op_summa) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1)) from finance_operations fo
where fo.op_type = 2 and fo.op_date between ':Начало|date$' and ':Конец|date$ 23.59.59'
and fo.op_summa > 0
) as "Итого",
(
select count(os.id) from
objects_status os
left join abonents ab on os.object_id = ab.id
where os.status=31
and upper(os.object_name) = 'Abonents'
and cast(os.apply_date as date) between cast(':Начало|date$' as date) and cast(':Конец|date$' as date)
) as "Подписанные",
(
select count(os.id) from
objects_status os
left join abonents ab on os.object_id = ab.id
where os.status=35
and upper(os.object_name) = 'Abonents'
and cast(os.apply_date as date) between cast(':Начало|date$' as date) and cast(':Конец|date$' as date)

AB.NAME as "ФИО",
AB.SMS as "Телефон",
TP.NAME as "Тариф",
H.STREET as "Улица",
H.S_NUMBER as "№ дома",

left join ADMIN_ACCOUNTS AA on AB.ACCOUNT_ID = AA.ID
left join HOMES H on AB.HOME_ID=H.ID
left join TARIF TP on AB.TARIF_ID = TP.ID

cast('*****ФИЗИЧЕСКИЕ ЛИЦА**********' as varchar(100) ) as "Наименование",
cast('******************************************' as varchar(100)) as "Адрес" ,
cast('******************************************' as varchar(100) ) as "Посл. платеж",
cast('*****************************' as varchar(100)) as "Сумма долга"
from send_type
union all

select
AB.CONTRACT_NUMBER as "№ договора",
AB.NAME as "ФИО",
H.STREET || ', д. ' || H.S_NUMBER || ', кв.' || AB.A_HOME_NUMBER as "Адрес",
(select max(FO.OP_DATE) from FINANCE_OPERATIONS FO where FO.ABONENT_ID = AB.ID) as "Посл. платеж",
(select round(
sum(
cast(
(CNT.SUMM) as float
)
)
, 2) from counters cnt left join usluga u on CNT.USLUGA_ID=U.ID
where cnt.month_number=extract(month FROM cast('NOW' as date))-1
and cnt.year_number=extract(year FROM cast('NOW' as date))
and u.post_pay=1 and cnt.abonent_id=ab.id) as "Баланс"
from ABONENTS AB
left join ADMIN_ACCOUNTS AA on AB.ACCOUNT_ID = AA.ID
left join ABONENTS_CACHE ACCH on AB.ID = ACCH.ID
left join HOMES H on AB.HOME_ID=H.ID
where AA.CREDIT_ADJUST!=0
and AB.COMPANY = 0
and (AA.DEBIT + AA.OSTATOK - AA.CREDIT) / (select CONST_VALUE from VPN_CONST where CONST_ID = 1)<0


union all

select first 1
cast('******************************************' as varchar(100) ) as "№ договора",
cast('*****ЮРИДИЧЕСКИЕ ЛИЦА**********' as varchar(100) ) as "ФИО",
cast('******************************************' as varchar(100)) as "Адрес" ,
cast('******************************************' as varchar(100) ) as "Посл. платеж",
cast('*****************************' as varchar(100)) as "Баланс"
from send_type
union all

select
AB.CONTRACT_NUMBER as "№ договора",
AB.NAME as "ФИО",
H.STREET || ', д. ' || H.S_NUMBER || ', кв.' || AB.A_HOME_NUMBER as "Адрес",
(select max(FO.OP_DATE) from FINANCE_OPERATIONS FO where FO.ABONENT_ID = AB.ID) as "Посл. платеж",
(select round(
sum(
cast(
(CNT.SUMM) as float
)
)
, 2) from counters cnt left join usluga u on CNT.USLUGA_ID=U.ID
where cnt.month_number=extract(month FROM cast('NOW' as date))-1
and cnt.year_number=extract(year FROM cast('NOW' as date))
and u.post_pay=1 and cnt.abonent_id=ab.id) as "Баланс"
from ABONENTS AB
left join ADMIN_ACCOUNTS AA on AB.ACCOUNT_ID = AA.ID
left join ABONENTS_CACHE ACCH on AB.ID = ACCH.ID
left join HOMES H on AB.HOME_ID=H.ID
where AA.CREDIT_ADJUST!=0

A.TARIF_ID as "ID тарифа",
T.NAME as "Тариф",
A.TARIF_NEXT_ID as "ID нового тарифа"
from ABONENTS A

from PAY_LOG PLS
where PLS.DATE_CREATE between ':Дата1|date$' and ':Дата2|date$ 23:59:59')

select
PERIOD.DAT as "Date",
(
select COALESCE(sum(P.SUMMA_IN),0)
from PAY_LOG P
where P.MSG_OUT containing 'ACCEPTED'
and P.ACT_IN containing 'PAY'
and P.PAY_OPERATOR containing upper('Жилищное упр')
and P.DATE_CREATE containing DAT
) as "Жилищное упр",
(
select COALESCE(sum(P.SUMMA_IN),0)
from PAY_LOG P
where P.MSG_OUT containing 'ACCEPTED'
and P.ACT_IN containing 'PAY'
and P.PAY_OPERATOR containing upper('Yandex.Kassa')
and P.DATE_CREATE containing DAT
) as "Яндекс"
from
PAY_LOG PL
inner join PERIOD
on PL.DATE_CREATE containing DAT
where PL.MSG_OUT containing 'ACCEPTED'
and PL.ACT_IN containing 'PAY'

ft.op_name || ', ' || ft.op_descr
from finance_operations fo
left join abonents ab on fo.abonent_id=ab.id
left join fin_types ft on fo.op_type=ft.type_id
where (fo.op_type=5 or fo.op_type=9 or fo.op_type=1)
and fo.abonent_id=ab.id

tarif.name,
usluga.max_mb_in_m,
round(usluga.in_price / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2),
round(traf_counters.v_in / cast((1048576) as numeric(18,5)), 2),
round(traf_counters.v_out / cast((1048576) as numeric(18,5)), 2),
usluga.name,
usluga.id,
tarif.id,
ab.id
from abonents as ab
left join tarif on ab.tarif_id=tarif.id
join tarif_users_usluga as tuu on tuu.tarif_id = tarif.id
join usluga on tuu.usluga_id=usluga.id
join users_usluga as uu on uu.abonent_id = ab.id and uu.usluga_Id = usluga.id and uu.tarif_id = tarif.id

{% load field_type %}
{% block content %}
<form method="POST">
<div id="params">
{% csrf_token %}{% load mathfilters %}
<input type='hidden' name='unique_form_post_id' value='{{ unique_form_post_id }}' />
{% if form %}
<div class="row-fluid">
<div class="span6">
<legend><h2>Заполните поля запроса</h2></legend>
{{ form }}
</div>
</div>
{% endif %}
<div class="row-fluid">
<div class="down_toolbar">
<button type="submit" value="Выполнить запрос" class="default btn btn-success">
<i class="icon-ok icon-white"></i> Выполнить запрос
</button>
{% if data %}
<div class="btn-group">
<a class="default btn dropdown-toggle" data-toggle="dropdown" href="#">
<i class="icon-print icon-white"></i>
Выгрузка
<span class="caret"></span>
</a>
<ul class="dropdown-menu">
<li><button type="submit" name="csv" value="True">CSV</button></li>
<li><button type="submit" name="dbf" value="True">DBF</button></li>
</ul>
</div>
<button class="default btn btn-success" onclick="window.print();" ><i class="icon-print icon-white"></i>Печать</button>
{% endif %}
</div>
</div>
</div>
</form>
{% block subcontent %}
{% if execute %}
<h2>{{inst.name}}</h2>
{% if error %}
{{ error }}<br/><br/>
{% endif %}

{% if data %}

<table id="print" class="mysqldata" border="1" style="text-align: center;">
<tr>
<th>ФИО</th>
<th>Тариф</th>
<th>Услуга</th>
<th>Объем предоплаченного трафика</th>
<th>Цена за МБ</th>
<th>Объем использованного входящего</th>
<th>Объем использованного исходящего</th>
<th>Объем трафика сверх предоплаченного входящего</th>
</tr>
{% for row in data %}
{% if row.2 and row.2 < row.4 %}
<tr>
<td><a href="/admin/Abonents/Abonents/{{ row.9 }}/" target="_blank">{{ row.0 }}</td>
<td><a href="/admin/tarifs/Tarif/{{ row.8 }}/" target="_blank">{{ row.1 }}</a></td>
<td><a href="/admin/tarifs/Usluga/{{ row.7 }}/" target="_blank">{{ row.6 }}</a></td>
<td>{% if row.2 %} {{ row.2|floatformat:2 }} Мб {% else %} --- {% endif %}</td>
<td>{{ row.3 }} руб.</td>
<td>{{ row.4|floatformat:2 }} Мб</td>
<td>{{ row.5|floatformat:2 }} Мб</td>
<td>{{ row.4|sub:row.2 }} Мб</td>
</tr>
{% endif %}
{% endfor %}
</table>
{% else %}
<h3>Ничего не найдено.</h3>
{% endif %}
{% endif %}
{% endblock %}
{% endblock %}
{% block js_addon %}
<link href="/static/css/jqueryui/smoothness/jquery-ui-1.8.23.custom.css" type="text/css" media="all" rel="stylesheet" />
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-1.8.23.custom.min.js"></script>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-timepicker-addon.js"></script>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-sliderAccess.js"></script>
<script type="text/javascript" src="/static/js/makedatetime.js"></script>
<script type="text/javascript" src="/static/js/print.js"></script>

u.login as "Логин",
a.contract_number as "Номер договора",
a.name as "ФИО",
round(fo.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Сумма прихода",
round(fo.balance_buh / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Баланс",
a.tarif_id as "ID тарифа",
t.name,
ft.op_name
from abonents as a join users as u on a.id=u.abonent_id
join finance_operations as fo on fo.ABONENT_ID=a.id
join fin_types as ft on fo.op_type = ft.type_id
join tarif as t on t.id = a.tarif_id
where
fo.SYSTEM_DATE between (':1 Дата|date$') and (':2 Дата|date$')

{% block addonhead %}
<style>
.btn-container {
padding: 10px;
}
</style>
<script>
$(function () {
{# debugger;#}
var $select = $('select');
{# $select.wrap('<div class="span12"></div>');#}
$select.select2({
containerCssClass : "span6"
});
})
</script>
{% endblock %}
{% load field_type %}
{% block content %}
<form method="POST">
<div id="params">
{% csrf_token %}
<input type='hidden' name='unique_form_post_id' value='{{ unique_form_post_id }}'/>
{% if form %}
<div class="row-fluid">
<div class="span6">
<legend><b>Заполните поля запроса</b></legend>
{% for field in form %}
<div class="control-group clearfix">
{{ field.label_tag }}
<div class="controls">
{{ field }}
</div>
</div>

{% endfor %}

</div>
</div>
{% endif %}
<div class="row-fluid">
<div class="down_toolbar">
<button type="submit" value="Выполнить запрос" class="default btn btn-success">
<i class="icon-ok icon-white"></i> Выполнить запрос
</button>
{% if data %}
<button class="default btn btn-success" onclick="window.print();"><i
class="icon-print icon-white"></i>Печать
</button>
{% endif %}
</div>
</div>
</div>
</form>
{% block subcontent %}
{% if execute %}
<h2>{{ inst.name }}</h2>
{% if error %}
{{ error }}<br/><br/>
{% endif %}

{% if data %}

<table id="print" class="mysqldata" border="1" width="100%">
<tr>
<th>ДАТА ПРИХОДА</th>
<th>ЛОГИН</th>
<th>НОМЕР ДОГОВОРА</th>
<th>ФИО</th>
<th>СУММА</th>
<th>ТИП</th>
<th>БАЛАНС</th>
<th>ТАРИФ</th>
</tr>
{% for row in data %}
<tr>
<td class="date">{{ row.0|date:"d.m.Y G:i:s" }}</td>
<td>{{ row.1 }}</td>
<td class="contract">{{ row.2 }}</td>
<td>{{ row.3 }}</td>
<td class="fio">{{ row.4 }}</td>
<td>{{ row.8 }}</td>
<td>{{ row.5 }}</td>
<td><a href="/admin/tarifs/Tarif/{{ row.6 }}/" target="_blank">{{ row.7 }}</a></td>
</tr>
{% endfor %}
</table>
{% else %}
<h3>Ничего не найдено.</h3>
{% endif %}
{% endif %}
{% endblock %}
{% endblock %}
{% block js_addon %}
<link href="/static/css/jqueryui/smoothness/jquery-ui-1.8.23.custom.css" type="text/css" media="all"
rel="stylesheet"/>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-1.8.23.custom.min.js"></script>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-timepicker-addon.js"></script>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-sliderAccess.js"></script>
<script type="text/javascript" src="/static/js/makedatetime.js"></script>
<script type="text/javascript" src="/static/js/print.js"></script>

uf_ip2string(ip) as "IP",
a.own_disabled_start as "Дата начала",
a.own_disabled_end as "Дата конца"
from abonents as a
left join users as u on a.id=u.abonent_id

A.NAME as FIO,
U.LOGIN as LOGIN,
UF_IP2STRING(U.IP) as IP,
H.city as CITY,
H.street as STREET,
H.s_number as HOME
from ABONENTS A
left join USERS U on A.ID = U.ABONENT_ID
left join HOMES H on A.HOME_ID = H.ID

users.login as "Логин",
tarif.name as "Тариф",
round(SUM_BYTE_IN_A/cast((1048576) as numeric(18,5)), 2) as "В год",
round(SUM_BYTE_IN_M/cast((1048576) as numeric(18,5)), 2) as "В месяц",
round(SUM_BYTE_IN_D/cast((1048576) as numeric(18,5)), 2) as "В день",
abonents.create_date as "Подключен"
from abonents
left join traf_counters as tc on abonents.id=tc.ABONENT_ID
left join tarif on tarif.id=abonents.TARIF_ID
left join users on users.abonent_id=abonents.id
where
tc.month_number=extract(month from current_timestamp)
and tc.year_number=extract(year from current_timestamp)

A.NAME as "ФИО",
(H.CITY || ' ' || H.STREET || ' ' || H.S_NUMBER || ' ' || H.S_LITER) as "Дом",
A.SMS as "Телефон",
(AC.OSTATOK + AC.DEBIT - AC.CREDIT) / (select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as "Баланс",
cast(((select sum(ABONENTS_BLOCK.B_SUMM)
from ABONENTS_BLOCK
where ABONENTS_BLOCK.ABONENT_ID = A.ID) - (AC.OSTATOK)) / cast((select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as numeric(18,5)) as numeric(18,2)) as "Долг"
from ABONENTS A
inner join ABONENTS_BLOCK AB on A.ID = AB.ABONENT_ID and AB.B_NEGBAL = 1 and (A.DELETED <> 1)
left join HOMES H on H.ID = A.HOME_ID
left join ADMIN_ACCOUNTS AC on A.ACCOUNT_ID = AC.ID

cast(rdb$get_context('USER_TRANSACTION', 'row#') as integer) as "№",
tmp.*
from (select distinct
(select aa.name from abonents aa where aa.id=a.parent_id) as "Папка",
A.NAME as "ФИО",
PHONE_PULL_CHACHE.Phone as "Телефон",
(select max(FO.OP_DATE) from FINANCE_OPERATIONS FO where FO.ABONENT_ID = A.ID) as "Дата посл.",
cast(((select sum(ABONENTS_BLOCK.B_SUMM)
from ABONENTS_BLOCK
where ABONENTS_BLOCK.ABONENT_ID = A.ID) - (AC.OSTATOK)) / cast((select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as numeric(18,5)) as numeric(18,2)) as "Долг"
from ABONENTS A
inner join ABONENTS_BLOCK AB on A.ID = AB.ABONENT_ID and AB.B_NEGBAL = 1 and (A.DELETED <> 1)
left join HOMES H on H.ID = A.HOME_ID
left join ADMIN_ACCOUNTS AC on A.ACCOUNT_ID = AC.ID
left join users u on u.abonent_id=A.ID
left join PHONE_PULL_CHACHE on PHONE_PULL_CHACHE.ID=u.PHONE)
as tmp

users.login as "Логин",
tarif.name as "Тариф",
sum(round(tc.SUM_BYTE_OUT_M/cast((1048576) as numeric(18,5)), 2)) as "Объем исх.",
sum(round(tc.SUM_BYTE_IN_M/cast((1048576) as numeric(18,5)), 2)) as "Объем вх."
from abonents
left join traf_counters as tc on abonents.id=tc.ABONENT_ID
left join tarif on tarif.id=abonents.TARIF_ID
left join users on users.abonent_id=abonents.id
where
tc.month_number between (':1 Месяц$') and (':2 Месяц$')
and tc.year_number=(':Год$')
and tc.currentt=1

round(fo.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Сумма прихода",
fo.OP_DATE as "Дата прихода"
from abonents as a
left join finance_operations as fo
on a.id=fo.abonent_id where
OP_TYPE=2 and
fo.OP_DATE between (':1 Дата|date$') and (':2 Дата|date$')

select '#FILESUM' || ' ' || round(sum(aa.ostatok / cast((10000000000) as numeric(18,5))), 2)
from abonents as a
right join homes as h on a.home_id=h.id
right join admin_accounts as aa on a.account_id=aa.id
where company=0
and is_folder=0
UNION ALL
select first 1 '#TYPE 7' from users
UNION ALL
select first 1 '#SERVICE 10240' from users
UNION ALL
select a.name || ';' || h.city || ',' || h.street || ',' || h.s_number || ',' || a.A_HOME_NUMBER||';'||a.account_id||';'||round(aa.ostatok / cast((10000000000) as numeric(18,5)), 2)|| ';;;;'
from abonents as a
right join homes as h on a.home_id=h.id
right join admin_accounts as aa on a.account_id=aa.id
where company=0
and is_folder=0
{code}
Тот же отчет, выводящий текущий баланс.
{code:lang=sql}
select '#FILESUM' || ' ' || round(sum((aa.ostatok+aa.debit-aa.credit) / cast((10000000000) as numeric(18,5))), 2)
from abonents as a
right join homes as h on a.home_id=h.id
right join admin_accounts as aa on a.account_id=aa.id
where company=0
and is_folder=0
and parent_id not in ( '244', '2', '4' , '1499')
UNION ALL
select first 1 '#TYPE 7' from users
UNION ALL
select first 1 '#SERVICE 10240' from users
UNION ALL
select a.name || ';' || h.city || ',' || h.street || ',' || h.s_number || ',' || a.A_HOME_NUMBER||';'||a.account_id||';'||round((aa.ostatok+aa.debit-aa.credit) / cast((10000000000) as numeric(18,5)), 2)|| ';;;;'
from abonents as a
right join homes as h on a.home_id=h.id
right join admin_accounts as aa on a.account_id=aa.id
where company=0

{% block addonhead %}
<style>
.btn-container {
padding: 10px;
}
</style>
<script>
$(function () {
{# debugger;#}
var $select = $('select');
{# $select.wrap('<div class="span12"></div>');#}
$select.select2({
containerCssClass : "span6"
});
})
</script>
{% endblock %}
{% load field_type %}
{% block content %}
<form method="POST">
<div id="params">
{% csrf_token %}
<input type='hidden' name='unique_form_post_id' value='{{ unique_form_post_id }}'/>
{% if form %}
<div class="row-fluid">
<div class="span6">
<legend><b>Заполните поля запроса</b></legend>
{% for field in form %}
<div class="control-group clearfix">
{{ field.label_tag }}
<div class="controls">
{{ field }}
</div>
</div>

{% endfor %}

</div>
</div>
{% endif %}
<div class="row-fluid">
<div class="down_toolbar">
<button type="submit" value="Выполнить запрос" class="default btn btn-success">
<i class="icon-ok icon-white"></i> Выполнить запрос
</button>
{% if data %}
<button class="default btn btn-success" onclick="window.print();"><i
class="icon-print icon-white"></i>Печать
</button>
{% endif %}
</div>
</div>
</div>
</form>
{% block subcontent %}
{% if execute %}
<h2>{{ inst.name }}</h2>
{% if error %}
{{ error }}<br/><br/>
{% endif %}

{% if data %}

<table id="print" class="mysqldata" border="1" width="100%">
<tr>
<th>№</th>
{% for field in field_desc %}
<th>{{ field|get_zero }}</th>
{% endfor %}
</tr>
{% for row in data %}
<tr>
<td>{{ forloop.counter }}</td>
{% for cell in row %}
<td>{{ cell }}</td>
{% endfor %}
</tr>
{% endfor %}
</table>
{% else %}
<h3>Ничего не найдено.</h3>
{% endif %}
{% endif %}
{% endblock %}
{% endblock %}
{% block js_addon %}
<link href="/static/css/jqueryui/smoothness/jquery-ui-1.8.23.custom.css" type="text/css" media="all"
rel="stylesheet"/>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-1.8.23.custom.min.js"></script>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-timepicker-addon.js"></script>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-sliderAccess.js"></script>
<script type="text/javascript" src="/static/js/makedatetime.js"></script>
<script type="text/javascript" src="/static/js/print.js"></script>
<link href="/static/css/for_printer.css" type="text/css" media="all" rel="stylesheet"/>

ABONENTS.name as "ФИО",
(ADMIN_ACCOUNTS.OSTATOK + ADMIN_ACCOUNTS.DEBIT - ADMIN_ACCOUNTS.CREDIT)/10000000000 as "Баланс",
USERS_USLUGA.END_TIME as "Конец"
from USERS_USLUGA left join ABONENTS on ABONENTS.ID = USERS_USLUGA.ABONENT_ID
left join ADMIN_ACCOUNTS on ABONENTS.ACCOUNT_ID = ADMIN_ACCOUNTS.ID
left join USERS on ABONENTS.ID = USERS.ABONENT_ID
where USERS_USLUGA.USLUGA_ID = (':ID нужной услуги$')
and USERS_USLUGA.DELETED != 1

A.NAME as "ФИО",
U.LOGIN as "Логин",
UF_IP2STRING(U.IP) as "IP",
U.MAC as "MAC",
A.SMS as "Телефон",
h.STREET || ', д. ' || h.S_NUMBER || ', кв.' || a.A_HOME_NUMBER as "Адрес",
T.name as "Тариф",
round(aa.ostatok / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Баланс"
from ABONENTS A
left join USERS U on A.ID = U.ABONENT_ID
left join HOMES H on A.HOME_ID = H.ID
left join TARIF T on A.TARIF_ID=T.ID
left join ADMIN_ACCOUNTS AA on AA.ID=A.ACCOUNT_ID
where A.IS_FOLDER=0

from finance_operations
where op_type=1

a.CONTRACT_NUMBER as "№",
a.NAME as "ФИО",
t.NAME as "ТАРИФ",
round(u.SUMMA / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "АБОН.ПЛАТА",
round(ac.RECOMEND_PAY_SUM_CACHE / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "РЕКОМЕНД.",
st.name as "СТАТУС",
subst.name as "ПОДСТАТУС",
cast(obs.apply_date as date) as "ДАТА"

from ABONENTS a
LEFT JOIN ABONENTS_CACHE ac ON ac.ID=a.ID
LEFT JOIN TARIF t ON a.TARIF_ID=t.ID
LEFT JOIN TARIF_USERS_USLUGA tus ON tus.TARIF_ID=t.ID
LEFT JOIN USLUGA u ON u.ID=tus.USLUGA_ID
LEFT JOIN OBJECTS_STATUS obs on obs.OBJECT_ID = a.ID
LEFT JOIN STATUS st on st.id = obs.STATUS
LEFT JOIN STATUS subst on subst.id = obs.SUBSTATUS

where obs.OBJECT_NAME='Abonents'
and a.is_folder = 0
and a.parent_id != 244

FO.op_date as "Дата платежа",
A.name as "ФИО",
A.contract_number as "Договор",
FO.op_summa/(select CONST_VALUE from VPN_CONST where CONST_ID = 1) as "Сумма"
from finance_operations FO
left join AUTH_USER AU on FO.owner_id = AU.ID
left join Abonents a on A.id=FO.abonent_id

a.ID as "ID",
a.NAME as "ФИО",
aa.UNLIMITED as "Не отключать"
from ABONENTS a
left join ADMIN_ACCOUNTS aa on a.ACCOUNT_ID=aa.ID
where a.IS_FOLDER=0

a.CREATE_DATE as "Дата договора",
a.CONTRACT_NUMBER as "№ договора",
t.NAME as "Тариф",
st.NAME as "Cтатус",
a.NAME as "ФИО абонента",
h.STREET || ', д. ' || h.S_NUMBER || ', кв.' || a.A_HOME_NUMBER as "Адрес",
a.ACTIVATE_DATE as "Дата активации",
n.NAME as "NAS"
from ABONENTS a

LEFT JOIN TARIF t on a.TARIF_ID=t.ID
LEFT JOIN USERS u on u.ABONENT_ID=a.ID
LEFT JOIN OBJECTS_STATUS obs on obs.OBJECT_ID = a.ID
LEFT JOIN STATUS st on st.id = obs.STATUS
LEFT JOIN NAS n on u.nas_id=n.ID
left join HOMES h on a.HOME_ID=h.ID

UF_IP2STRING(END_IP) as "Конечный IP",
(select count(1)

where ID = USERS.ABONENT_ID) = 0) as "Занято",
(END_IP - START_IP +1 - (select count(1)
from USERS
where ((IP >= START_IP and IP <= END_IP)
or (IP <= START_IP and IP >= END_IP))
or ((HOST_IP >= START_IP and HOST_IP <= END_IP)
or (HOST_IP <= START_IP and HOST_IP >= END_IP))
and (select first 1 DELETED
from ABONENTS
where ID = USERS.ABONENT_ID) = 0)) as "Свободно"
from IP_PULL

FO.op_date as "Дата платежа",
A.name as "ФИО",
A.contract_number as "Договор",
FO.DESCR as "Описание",
round(fo.aa_ostatok/ cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Остаток",
round(fo.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Сумма"
from finance_operations FO
left join AUTH_USER AU on FO.owner_id = AU.ID
left join Abonents a on A.id=FO.abonent_id
where 1=1 and (UPPER(FO.DESCR) LIKE UPPER('%Альфа%'))

from FINANCE_OPERATIONS FO
left join ABONENTS AB on FO.ABONENT_ID = AB.ID
left join FIN_TYPES FT on FO.OP_TYPE = FT.TYPE_ID
where (FO.OP_TYPE = 5
or FO.OP_TYPE = 9
or FO.OP_TYPE = 1)

where a.is_folder=0

cast(uu.CREATE_DATE as date) as "ДАТА АКТИВАЦИИ",
c.SUMM as "Прибыль"
from USERS_USLUGA uu
left join ABONENTS a on uu.ABONENT_ID = a.ID
left join COUNTERS c on c.ABONENT_ID = a.ID
where uu.USLUGA_ID=':Обещанный платеж|choices[ID_Услуга1^]Услуга1^[ID_Услуга2^]Услуга2^[ID_Услуга3^]Услуга3^[ID_Услуга4^]Услуга4^[ID_Услуга5^]Услуга5^[ID_Услуга6^]Услуга6^$'
and ENABLE_DATE between ':1 Дата|date$' and ':2 Дата|date$'
and c.USLUGA_ID=uu.USLUGA_ID
and c.SUMM>0

union all

SELECT
cast('' as varchar(32)),
cast('' as varchar(32)),
cast('Итого' as varchar(32)),
ROUND (sum(c.summ))
from USERS_USLUGA uu
left join ABONENTS a on uu.ABONENT_ID = a.ID
left join COUNTERS c on c.ABONENT_ID = a.ID
where uu.USLUGA_ID=':Обещанный платеж|choices[ID_Услуга1^]Услуга1^[ID_Услуга2^]Услуга2^[ID_Услуга3^]Услуга3^[ID_Услуга4^]Услуга4^[ID_Услуга5^]Услуга5^[ID_Услуга6^]Услуга6^$'
and ENABLE_DATE between ':1 Дата|date$' and ':2 Дата|date$'
and c.USLUGA_ID=uu.USLUGA_ID

round(aa.ostatok / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Остаток",
u.login,
u.GEN_PWD,
u.auth_type,
uf_ip2string(s.ip) as "switch_ip",
abs.RATE_IN,
abs.RATE_OUT,
abs.CEIL_IN,

left join admin_accounts as aa on aa.id=a.ACCOUNT_ID
left join switch as s on s.id=u.switch_id
left join ABONENTS_SPEED as ABS on abs.abonent_id=a.id
left join NAS on nas.id=u.nas_id
where a.is_folder=0
and a.deleted=0

('г. ' || h.city || ' ' || h.street || ', д.' || h.s_number || ', кв.' || a.a_home_number) as "Адрес",
(select av.attribute_value from attribute_values av where av.attribute_id = 1 and av.abonent_id = a.id) as "Телефон",

A.CONTRACT_NUMBER as "Договор",
T.name as "Тариф",
USL.NAME as "Имя услуги",
UU.ACTIVATED as "Активна",
round(aa.ostatok / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Баланс"
from ABONENTS A
left join USERS U on A.ID = U.ABONENT_ID
left join HOMES H on A.HOME_ID = H.ID
left join TARIF T on A.TARIF_ID=T.ID
left join ADMIN_ACCOUNTS AA on AA.ID=A.ACCOUNT_ID
left join USERS_USLUGA UU on UU.abonent_id=A.id
left join USLUGA USL on USL.ID=UU.USLUGA_ID
where A.IS_FOLDER=0
AND A.DELETED=0

AB.NAME as "ФИО",
(select name from abonents where abonents.id=ab.parent_id) as "Группа",
FO.SYSTEM_DATE as "Дата",
FO.DESCR as "Описание",
cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as "Сумма"
from FINANCE_OPERATIONS FO
left join ABONENTS AB on FO.ABONENT_ID = AB.ID
where OP_TYPE = 2
and ab.parent_id=(':ID группы$')
and (FO.SYSTEM_DATE between ':1.C_даты|date$' and ':2.По_дату|date$')
and (AB.DELETED = 0
or AB.DELETED is null)
group by AB.CONTRACT_NUMBER, AB.NAME, FO.DESCR, FO.SYSTEM_DATE, AB.PARENT_ID
union all
select '"Итого"',
null,
null,
null,
null,
sum(SUMOP)
from (select cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as SUMOP
from FINANCE_OPERATIONS FO
left join ABONENTS AB on FO.ABONENT_ID = AB.ID
where OP_TYPE = 2
and ab.parent_id=(':ID группы$')
and (FO.SYSTEM_DATE between ':1.C_даты|date$' and ':2.По_дату|date$')
and (AB.DELETED = 0
or AB.DELETED is null))
order by 4
{code}
Тот же отчёт по всем абонентам
{code}
select AB.CONTRACT_NUMBER as "Номер договора",
AB.NAME as "ФИО",
(select name from abonents where abonents.id=ab.parent_id) as "Группа",
FO.SYSTEM_DATE as "Дата",
FO.DESCR as "Описание",
cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as "Сумма"
from FINANCE_OPERATIONS FO
left join ABONENTS AB on FO.ABONENT_ID = AB.ID
where OP_TYPE = 2
and (FO.SYSTEM_DATE between ':1.C_даты|date$' and ':2.По_дату|date$')
and (AB.DELETED = 0
or AB.DELETED is null)
group by AB.CONTRACT_NUMBER, AB.NAME, FO.DESCR, FO.SYSTEM_DATE, AB.PARENT_ID
union
select '"Итого"',
null,
null,
null,
null,
sum(SUMOP)
from (select cast(sum(FO.OP_SUMMA) as double precision) / (select CONST_VALUE
from VPN_CONST
where CONST_ID = 1) as SUMOP
from FINANCE_OPERATIONS FO
left join ABONENTS AB on FO.ABONENT_ID = AB.ID
where OP_TYPE = 2
and (FO.SYSTEM_DATE between ':1.C_даты|date$' and ':2.По_дату|date$')
and (AB.DELETED = 0

T.NAME as "Package",
AB.CREATE_DATE as "Creation date",
AB.ACTIVATE_DATE as "Activation date",
SMS as "Phone",
EMAIL as "E-mail",
H.STREET,
H.S_NUMBER,
H.S_LITER,
AB.A_HOME_NUMBER,
S.NAME as "Status",
extract(day from OS.APPLY_DATE)||'.'||extract(month from OS.APPLY_DATE)||'.'||extract(year from OS.APPLY_DATE) as "Change date",
(case when ab.id in (select abonent_id from abonents_block where B_ADMIN = 1) then 'Blocked' else '' end) as "Admin",
(case when ab.id in (select abonent_id from abonents_block where B_OWN = 1) or ab.id in (select abonent_id from abonents_block where B_OWN2 = 1) then 'Blocked' else '' end) as "Own",
coalesce(cast(OWN_DISABLED_END as varchar(100)), '') as "Block end"
from ABONENTS AB
left join HOMES H on H.ID = AB.HOME_ID
left join OBJECTS_STATUS OS on OS.OBJECT_ID = AB.ID
left join TARIF T on AB.TARIF_ID = T.ID

fo.op_date "Дата",
au.username "Ответстывенный",
ft.op_name || ' № ' || fo.number || ' от ' || extract(day from op_date) || '-' || extract(month from op_date) || '-' || extract(year from op_date) || ' за период ' || extract(day from period_end_date) || '-' || extract(month from period_end_date) || '-' || extract(year from period_end_date) as "Наименование",
descr "Описание",
(case when fo.OPERATION_SIGN <> 0 then cast(fo.OP_SUMMA/10000000000.00*OPERATION_SIGN as varchar(100)) else '' end)
from finance_operations fo
join abonents a on fo.abonent_id = a.id

a.name as "ФИО",
u.login as "LOGIN",
a.contract_number as "№ ДОГОВОРА",
round((aa.ostatok+aa.debit-aa.credit) / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "БАЛАНС"
from abonents a
left join finance_operations fo on fo.abonent_id=a.id
left join users u on u.abonent_id=a.id
left join admin_accounts aa on aa.id=a.account_id
where a.id not in (select fo.abonent_id from finance_operations fo where fo.op_date >= cast(':Дата|date$' as timestamp) and fo.op_type=2)
and a.parent_id = ':Группа|select[Abonents,is_folder=1]$'
and a.is_folder=0

and a.deleted=0

distinct
A.CONTRACT_NUMBER as "Договор",
T.name as "Тариф",
usl.NAME as "Услуга",
round((aa.ostatok+aa.debit-aa.credit) / cast((10000000000) as numeric(18,5)), 2) as "Текущий баланс"
from ABONENTS A
left join USERS U on A.ID = U.ABONENT_ID
left join TARIF T on A.TARIF_ID=T.ID
left join ADMIN_ACCOUNTS AA on AA.ID=A.ACCOUNT_ID
left join users_usluga uu on uu.ABONENT_ID=A.ID
left join usluga usl on usl.id=uu.USLUGA_ID
left join abonents_block ab on ab.abonent_id=a.id
where A.IS_FOLDER=0
and a.deleted=0
and ab.B_NEGBAL=1

A.NAME as "ФИО",
A.CONTRACT_NUMBER as "Номер договора",
A.SMS as "Телефон",
h.STREET || ', д. ' || h.S_NUMBER || ', кв.' || a.A_HOME_NUMBER as "Адрес"
from ABONENTS A
left join USERS U on A.ID = U.ABONENT_ID
left join HOMES H on A.HOME_ID = H.ID
left join TARIF T on A.TARIF_ID=T.ID
left join ADMIN_ACCOUNTS AA on AA.ID=A.ACCOUNT_ID
where A.IS_FOLDER=0

from finance_operations fo
left join abonents a on a.id=fo.abonent_id
where op_type=1
and op_date between (':1 Дата|date$') and (':2 Дата|date$')

left join abonents_history ah on ah.abonents_id=a.id
where COALESCE(A.IS_FOLDER, 0)=0 and A.CATEGORY_ID=1
and PPC.PHONE like '7496%'

left join abonents_history ah on ah.abonents_id=a.id
where COALESCE(A.IS_FOLDER, 0)=0 and A.CATEGORY_ID=1
and PPC.PHONE like '7496%'
and a.company<>':Абонент|choices[0^]Юр.Лица^[1^]Физ.лица^[2^]Все]$'

{% block addonhead %}
<style>
.btn-container {
padding: 10px;
}
</style>
<script>
$(function () {
{# debugger;#}
var $select = $('select');
{# $select.wrap('<div class="span12"></div>');#}
$select.select2({
containerCssClass : "span6"
});
})
</script>
{% endblock %}
{% load field_type %}
{% block content %}
<form method="POST">
<div id="params">
{% csrf_token %}
<input type='hidden' name='unique_form_post_id' value='{{ unique_form_post_id }}'/>
{% if form %}
<div class="row-fluid">
<div class="span6">
<legend><b>Заполните поля запроса</b></legend>
{% for field in form %}
<div class="control-group clearfix">
{{ field.label_tag }}
<div class="controls">
{{ field }}
</div>
</div>

{% endfor %}

</div>
</div>
{% endif %}
<div class="row-fluid">
<div class="down_toolbar">
<button type="submit" value="Выполнить запрос" class="default btn btn-success">
<i class="icon-ok icon-white"></i> Выполнить запрос
</button>
{% if data %}
<button class="default btn btn-success" onclick="window.print();"><i
class="icon-print icon-white"></i>Печать
</button>
{% endif %}
</div>
</div>
</div>
</form>
{% block subcontent %}
{% if execute %}
<h2>{{ inst.name }}</h2>
{% if error %}
{{ error }}<br/><br/>
{% endif %}

{% if data %}

<table id="print" class="mysqldata" border="1" width="100%">
<tr>
<th>№</th>
{% for field in field_desc %}
<th>{{ field|get_zero }}</th>
{% endfor %}
</tr>
{% for row in data %}
<tr>
<td>{{ forloop.counter }}</td>
{% for cell in row %}
<td>{{ cell }}</td>
{% endfor %}
</tr>
{% endfor %}
</table>
{% else %}
<h3>Ничего не найдено.</h3>
{% endif %}
{% endif %}
{% endblock %}
{% endblock %}
{% block js_addon %}
<link href="/static/css/jqueryui/smoothness/jquery-ui-1.8.23.custom.css" type="text/css" media="all"
rel="stylesheet"/>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-1.8.23.custom.min.js"></script>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-timepicker-addon.js"></script>
<script type="text/javascript" src="/static/js/jqueryui/jquery-ui-sliderAccess.js"></script>
<script type="text/javascript" src="/static/js/makedatetime.js"></script>
<script type="text/javascript" src="/static/js/print.js"></script>
<link href="/static/css/for_printer.css" type="text/css" media="all" rel="stylesheet"/>

FROM counters
WHERE YEAR_NUMBER = (':Год|choices[2017^]2017^[2016^]2016^[2015^]2015^[2014^]2014]$')
AND MONTH_NUMBER = (':Месяц|choices[1^]Январь^[2^]Февраль^[3^]Март^[4^]Апрель^[5^]Май^[6^]Июнь^[7^]Июль^[8^]Август^[9^]Сентябрь^[10^]Октябрь^[11^]Ноябрь^[12^]Декабрь]$')
AND UNIT_ID = 2
AND V_TYPE_ID = 1),
(SELECT cast(sum(v) AS numeric(18,2)) AS "Исходящий"
FROM counters
WHERE YEAR_NUMBER = (':Год|choices[2017^]2017^[2016^]2016^[2015^]2015^[2014^]2014]$')
AND MONTH_NUMBER = (':Месяц|choices[1^]Январь^[2^]Февраль^[3^]Март^[4^]Апрель^[5^]Май^[6^]Июнь^[7^]Июль^[8^]Август^[9^]Сентябрь^[10^]Октябрь^[11^]Ноябрь^[12^]Декабрь]$')
AND UNIT_ID = 2

join HOMES h on h.ID = A.HOME_ID
where op_type=1
and a.deleted=0
and a.is_folder=0
and op_date between (':1 Дата|date$') and (':2 Дата|date$')

FO.op_date as "Дата операции",
FT.OP_NAME as "Тип операции",
A.name as "ФИО",
A.contract_number as "Договор",
FO.DESCR as "Описание",
round(fo.aa_ostatok/ cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Остаток",
round(fo.op_summa / cast((select CONST_VALUE from VPN_CONST where CONST_ID = 1) as numeric(18,5)), 2) as "Сумма"
from finance_operations FO
left join AUTH_USER AU on FO.owner_id = AU.ID
left join Abonents a on A.id=FO.abonent_id
left join FIN_TYPES FT on FT.TYPE_ID=FO.op_type
where (FO.op_date between ':1 Дата|date$' and ':2 Дата|date$')

cast(sum(vl.bill_sec_round) as numeric(18,2))/60 as "Длительность",
a.name as "Название/ФИО"
from
voip_log vl
join usluga u on vl.usluga_id=u.id
join usluga_voip uv on u.id=uv.usluga_id
join category_directions cd on uv.voipcategory_id=cd.category_id

vl.s_time between ':Начало|date$' and ':Конец|date$' and
vl.V_TYPE_ID = 2 and
vl.error_code is null
group by 1, a.contract_number, a.name