https://pastein.ru/t/k7S
Загрузка данных
<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.12.2.final using JasperReports Library version 6.12.2-75c5e90a222ab406e416cbf590a5397028a52de3 -->
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="accountStatementSNG" pageWidth="595" pageHeight="840" columnWidth="595" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" isSummaryWithPageHeaderAndFooter="true" isFloatColumnFooter="true" isIgnorePagination="true" uuid="21f4c1ba-5444-4a64-9c74-76ed4110ea24">
<property name="net.sf.jasperreports.export.xls.detect.cell.type" value="true"/>
<property name="net.sf.jasperreports.export.xls.remove.empty.space.between.columns" value="true"/>
<property name="net.sf.jasperreports.export.xls.sheet.names.{arbitrary_name}" value="Выписка по счету"/>
<property name="net.sf.jasperreports.export.xls.print.page.left.margin" value="40"/>
<property name="net.sf.jasperreports.export.xls.print.page.top.margin" value="45"/>
<property name="net.sf.jasperreports.export.xls.white.page.background" value="false"/>
<property name="com.jaspersoft.studio.data.sql.tables" value=""/>
<property name="com.jaspersoft.studio.unit." value="pixel"/>
<property name="com.jaspersoft.studio.unit.pageHeight" value="pixel"/>
<property name="com.jaspersoft.studio.unit.pageWidth" value="pixel"/>
<property name="com.jaspersoft.studio.unit.topMargin" value="pixel"/>
<property name="com.jaspersoft.studio.unit.bottomMargin" value="pixel"/>
<property name="com.jaspersoft.studio.unit.leftMargin" value="pixel"/>
<property name="com.jaspersoft.studio.unit.rightMargin" value="pixel"/>
<property name="com.jaspersoft.studio.unit.columnWidth" value="pixel"/>
<property name="com.jaspersoft.studio.unit.columnSpacing" value="pixel"/>
<property name="com.jaspersoft.studio.data.defaultdataadapter" value="Сургут разработка"/>
<subDataset name="dsIncome" uuid="c8694aaf-6ce2-4c5d-aa06-d4ac79f3cce8">
<property name="com.jaspersoft.studio.data.defaultdataadapter" value="Arena Prod"/>
<property name="com.jaspersoft.studio.data.sql.tables" value=""/>
<property name="com.jaspersoft.studio.data.sql.SQLQueryDesigner.sash.w1" value="321"/>
<property name="com.jaspersoft.studio.data.sql.SQLQueryDesigner.sash.w2" value="674"/>
<parameter name="sharer_id" class="java.lang.Integer"/>
<parameter name="d2" class="java.util.Date"/>
<queryString>
<![CDATA[with deposit(sharer_id, effective_date, value, document) as (
-- Взносы и инвест.доход
select
s.id,
t.effective_date,
t.value,
case
when cat.mnemonics = 'ИПС' then ot.short_name || coalesce(' ПП №' || io.number, '')
else ot.short_name
end
from back_office.transaction t
join back_office.operation o on t.operation_id = o.id
join back_office.operation_type ot on ot.id = o.operation_type_id
join back_office.account ca on ca.id = t.credit_account_id
join back_office.account_type cat on cat.id = ca.account_type_id
join back_office.account_type_group catg on catg.id = cat.account_type_group_id
and catg.mnemonics = 'Н'
join ourpension.sharer s on ca.owner_sharer_id = s.id
and s.id = $P{sharer_id}
join ourpension.individual i on i.id = s.individual_id
left join back_office.incoming_order_operation ioo on o.id = ioo.operation_id
left join back_office.incoming_order io on io.id = ioo.incoming_order_id
where o.approved is not null
and o.deleted is null
and t.effective_date <= $P{d2}
--Взносы и компенсации на Р(С) и Р(П)
union all
select
s.id,
t.effective_date,
t.value,
ot.short_name
from back_office.transaction t
join back_office.operation o on t.operation_id = o.id
join back_office.operation_type ot on ot.id = o.operation_type_id
and ot.code in ('DEPOSIT_ENROLLMENT', 'COMPENSATION_FROM_OWN_FUNDS')
join back_office.account ca on ca.id = t.credit_account_id
join back_office.account_type cat on cat.id = ca.account_type_id
and cat.mnemonics in ('Р(П) ИПС', 'Р(П) ИПС ИД', 'Р(С) ИПС', 'Р(С) ИПС ИД')
join ourpension.sharer s on ca.owner_sharer_id = s.id
and s.id = $P{sharer_id}
join ourpension.individual i on i.id = s.individual_id
left join back_office.incoming_order_operation ioo on o.id = ioo.operation_id
left join back_office.incoming_order io on io.id = ioo.incoming_order_id
where o.approved is not null
and o.deleted is null
and t.effective_date <= $P{d2}
-- Перераспределение на другие СПС и ИПС
union all
select
s.id,
t.effective_date,
- t.value,
ot.short_name
from back_office.transaction t
join back_office.operation o on t.operation_id = o.id
join back_office.operation_type ot on ot.id = o.operation_type_id
join back_office.account ca on ca.id = t.credit_account_id
join back_office.account_type cat on cat.id = ca.account_type_id
and cat.mnemonics in ('ИПС', 'СПС', 'СПС И', 'ИПС ИД', 'СПС ИД', 'СПС И ИД', 'Р С' )
join back_office.account da on da.id = t.debit_account_id
join back_office.account_type dat on dat.id = da.account_type_id
join ourpension.sharer s on s.id = da.owner_sharer_id
and s.id = $P{sharer_id}
where o.approved is not null
and o.deleted is null
and t.effective_date <= $P{d2}
union all
-- Инвест.доход на резервные счета
select
s.id,
t.effective_date,
t.value,
ot.short_name
from back_office.transaction t
join back_office.operation o on t.operation_id = o.id
join back_office.operation_type ot on ot.id = o.operation_type_id
join back_office.account ca on ca.id = t.credit_account_id
join back_office.account_type cat on cat.id = ca.account_type_id
join back_office.account_type_group catg on catg.id = cat.account_type_group_id
and catg.mnemonics in ('Р(С)', 'Р(П)')
join ourpension.sharer s on ca.owner_sharer_id = s.id
and s.id = $P{sharer_id}
join ourpension.individual i on i.id = s.individual_id
where o.approved is not null
and o.deleted is null
and ot.code = 'PENSION_INVESTMENT_REVENUE_ENROLLMENT'
and t.effective_date <= $P{d2}
union all
-- Перевод из страхового резерва в выплатной
select
s.id,
t.effective_date,
t.value,
ot.short_name
from back_office.transaction t
join back_office.operation o on t.operation_id = o.id
join back_office.operation_type ot on ot.id = o.operation_type_id
join back_office.account ca on ca.id = t.credit_account_id
join back_office.account_type cat on cat.id = ca.account_type_id
join back_office.account_type_group catg on catg.id = cat.account_type_group_id
and catg.mnemonics in ('Р(П)', 'Р(С)')
join back_office.account da on da.id = t.debit_account_id
join back_office.account_type dat on dat.id = da.account_type_id
and dat.mnemonics in ('Р С', 'Р С ИД', 'СПС', 'СПС И', 'СПС ИД', 'СПС И ИД')
join ourpension.sharer s on s.id = ca.owner_sharer_id
and s.id = $P{sharer_id}
where o.approved is not null
and o.deleted is null
and t.effective_date <= $P{d2}
union all
-- Возвраты
select
s.id,
t.effective_date,
t.value,
ot.short_name
from back_office.transaction t
join back_office.operation o on t.operation_id = o.id
join back_office.operation_type ot on ot.id = o.operation_type_id
join back_office.account ca on ca.id = t.credit_account_id
join back_office.account_type cat on cat.id = ca.account_type_id
join back_office.account_type_group catg on catg.id = cat.account_type_group_id
join ourpension.sharer s on ca.owner_sharer_id = s.id
and s.id = $P{sharer_id}
join ourpension.individual i on i.id = s.individual_id
where o.approved is not null
and o.deleted is null
and o.operation_type_id in (16, 61, 60)
and o.effective_date <= $P{d2}
union all
-- МГД
select
s.id,
t.effective_date,
t.value,
'Разнесение МГД'
from back_office.transaction t
join back_office.operation o on t.operation_id = o.id
join back_office.operation_type ot on ot.id = o.operation_type_id
join back_office.account ca on ca.id = t.credit_account_id
join back_office.account_type cat on cat.id = ca.account_type_id
and cat.mnemonics in ('НВ ВС ИПС','НВ КС ИПС','НВ ИПС')
join ourpension.sharer s on s.id = ca.owner_sharer_id
and s.id = $P{sharer_id}
where o.approved is not null
and o.deleted is null
and t.debit_account_id is null
and t.effective_date <= $P{d2}
), deposit_lines(sharer_id, effective_date, value, document, num) as (
select
t.*,
row_number() over (partition by sharer_id order by effective_date)
from (
select
sharer_id,
effective_date,
sum(value) as value,
document
from deposit
group by sharer_id, effective_date, document) t
), payment (sharer_id, effective_date, value, document) as (
select
s.id,
t.effective_date,
t.value,
case cat.mnemonics
when 'В НДФЛ' then 'Удержание НДФЛ' || coalesce(' ПП №'|| pr.tax_order_number, '')
else ot.short_name || coalesce(' ПП №'|| pr.payment_order_number, '')
end
from back_office.transaction t
join back_office.account da on da.id = t.debit_account_id
join back_office.account ca on ca.id = t.credit_account_id
join back_office.account_type cat on cat.id = ca.account_type_id
join back_office.account_type_group catg on cat.account_type_group_id = catg.id
and catg.mnemonics = 'В'
join back_office.operation o on o.id = t.operation_id
join back_office.operation_type ot on ot.id = o.operation_type_id
join ourpension.sharer s on da.owner_sharer_id = s.id
and s.id = $P{sharer_id}
left join back_office.payment_register pr on o.payment_register_id = pr.id
where o.approved is not null
and o.deleted is null
and t.effective_date <= $P{d2}
), payment_lines(sharer_id, effective_date, value, document, num) as (
select
sharer_id, effective_date, sum(value), document,
row_number() over (partition by sharer_id order by effective_date)
from payment
group by sharer_id, effective_date, document
), lines(sharer_id, num) as (
select
s.id,
generate_series(1, greatest(max(d.num), max(p.num)))
from ourpension.sharer s
left join deposit_lines d on s.id = d.sharer_id
left join payment_lines p on s.id = p.sharer_id
where s.id = $P{sharer_id}
group by s.id
), report(num, deposit_num, deposit_effective_date, deposit_value, deposit_document,
payment_num, payment_effective_date, payment_value, payment_document) as (
select
l.num,
d.num,
d.effective_date,
d.value,
d.document,
p.num,
p.effective_date,
p.value,
p.document
from ourpension.sharer s
join lines l on l.sharer_id = s.id
left join deposit_lines d on l.sharer_id = d.sharer_id and l.num = d.num
left join payment_lines p on l.sharer_id = p.sharer_id and l.num = p.num
where s.id = $P{sharer_id}
) select * from report order by num;]]>
</queryString>
<field name="num" class="java.lang.Long">
<property name="com.jaspersoft.studio.field.name" value="num"/>
<property name="com.jaspersoft.studio.field.label" value="num"/>
</field>
<field name="deposit_num" class="java.lang.Long">
<property name="com.jaspersoft.studio.field.name" value="deposit_num"/>
<property name="com.jaspersoft.studio.field.label" value="deposit_num"/>
</field>
<field name="deposit_effective_date" class="java.sql.Date">
<property name="com.jaspersoft.studio.field.name" value="deposit_effective_date"/>
<property name="com.jaspersoft.studio.field.label" value="deposit_effective_date"/>
</field>
<field name="deposit_value" class="java.math.BigDecimal">
<property name="com.jaspersoft.studio.field.name" value="deposit_value"/>
<property name="com.jaspersoft.studio.field.label" value="deposit_value"/>
</field>
<field name="deposit_document" class="java.lang.String">
<property name="com.jaspersoft.studio.field.name" value="deposit_document"/>
<property name="com.jaspersoft.studio.field.label" value="deposit_document"/>
</field>
<field name="payment_num" class="java.lang.Long">
<property name="com.jaspersoft.studio.field.name" value="payment_num"/>
<property name="com.jaspersoft.studio.field.label" value="payment_num"/>
</field>
<field name="payment_effective_date" class="java.sql.Date">
<property name="com.jaspersoft.studio.field.name" value="payment_effective_date"/>
<property name="com.jaspersoft.studio.field.label" value="payment_effective_date"/>
<property name="com.jaspersoft.studio.field.tree.path" value="transaction"/>
</field>
<field name="payment_value" class="java.math.BigDecimal">
<property name="com.jaspersoft.studio.field.name" value="payment_value"/>
<property name="com.jaspersoft.studio.field.label" value="payment_value"/>
<property name="com.jaspersoft.studio.field.tree.path" value="transaction"/>
</field>
<field name="payment_document" class="java.lang.String">
<property name="com.jaspersoft.studio.field.name" value="payment_document"/>
<property name="com.jaspersoft.studio.field.label" value="payment_document"/>
</field>
<variable name="sum_income_list" class="java.math.BigDecimal" calculation="Sum">
<variableExpression><![CDATA[$F{deposit_value}]]></variableExpression>
</variable>
<variable name="sum_payment_list" class="java.math.BigDecimal" calculation="Sum">
<variableExpression><![CDATA[$F{payment_value}]]></variableExpression>
</variable>
</subDataset>
<parameter name="sharer_id" class="java.lang.Integer">
<defaultValueExpression><![CDATA[201873]]></defaultValueExpression>
</parameter>
<parameter name="d2" class="java.util.Date"/>
<parameter name="user_id" class="java.lang.Integer">
<defaultValueExpression><![CDATA[5014]]></defaultValueExpression>
</parameter>
<queryString>
<![CDATA[SELECT
c.number as contract_number,
s.number as sharer_number,
initcap(rtrim(i.last_name || ' ' || i.first_name || ' ' || coalesce(i.middle_name, ''))) as client,
coalesce( initcap(rtrim(ii.last_name || ' ' || ii.first_name || ' ' || coalesce(ii.middle_name, ''))),e.short_name) as investor,
to_char(c.date, 'DD.MM.YYYY') as contract_date,
coalesce(ap.position, '') as agent_position,
ap.phone as agent_phone,
a.last_name || ' ' ||substr(a.first_name,1,1) ||'.'||coalesce(substr(a.middle_name,1,1)||'.', '') as agent_fio,
to_char(current_date, 'DD.MM.YYYY') as print_date
FROM ourpension.sharer s
join ourpension.contract c on c.id =s.contract_id
join ourpension.individual i on i.id = s.individual_id
left join ourpension.individual ii on ii.id = c.individual_id
left join ourpension.entity e on e.id = c.entity_id
join ourpension.agent a on a.user_id = $P{user_id}
left join ourpension.agent_position ap on ap.agent_id = a.id
WHERE s.id =$P{sharer_id}]]>
</queryString>
<field name="contract_number" class="java.lang.String">
<property name="com.jaspersoft.studio.field.name" value="contract_number"/>
<property name="com.jaspersoft.studio.field.label" value="contract_number"/>
<property name="com.jaspersoft.studio.field.tree.path" value="contract"/>
</field>
<field name="sharer_number" class="java.lang.String">
<property name="com.jaspersoft.studio.field.name" value="sharer_number"/>
<property name="com.jaspersoft.studio.field.label" value="sharer_number"/>
<property name="com.jaspersoft.studio.field.tree.path" value="sharer"/>
</field>
<field name="client" class="java.lang.String">
<property name="com.jaspersoft.studio.field.name" value="client"/>
<property name="com.jaspersoft.studio.field.label" value="client"/>
</field>
<field name="investor" class="java.lang.String">
<property name="com.jaspersoft.studio.field.name" value="investor"/>
<property name="com.jaspersoft.studio.field.label" value="investor"/>
</field>
<field name="contract_date" class="java.lang.String">
<property name="com.jaspersoft.studio.field.name" value="contract_date"/>
<property name="com.jaspersoft.studio.field.label" value="contract_date"/>
</field>
<field name="agent_position" class="java.lang.String">
<property name="com.jaspersoft.studio.field.name" value="agent_position"/>
<property name="com.jaspersoft.studio.field.label" value="agent_position"/>
<property name="com.jaspersoft.studio.field.tree.path" value="agent_position"/>
</field>
<field name="agent_phone" class="java.lang.String">
<property name="com.jaspersoft.studio.field.name" value="agent_phone"/>
<property name="com.jaspersoft.studio.field.label" value="agent_phone"/>
<property name="com.jaspersoft.studio.field.tree.path" value="agent_position"/>
</field>
<field name="agent_fio" class="java.lang.String">
<property name="com.jaspersoft.studio.field.name" value="agent_fio"/>
<property name="com.jaspersoft.studio.field.label" value="agent_fio"/>
</field>
<field name="print_date" class="java.lang.String">
<property name="com.jaspersoft.studio.field.name" value="print_date"/>
<property name="com.jaspersoft.studio.field.label" value="print_date"/>
</field>
<variable name="sum_income" class="java.math.BigDecimal"/>
<variable name="sum_payment" class="java.math.BigDecimal"/>
<background>
<band splitType="Stretch"/>
</background>
<detail>
<band height="115">
<property name="com.jaspersoft.studio.unit.height" value="px"/>
<staticText>
<reportElement stretchType="ContainerHeight" x="55" y="80" width="160" height="35" uuid="228966bd-2c0a-4c5f-8afc-71a3fa6780c7">
<property name="com.jaspersoft.studio.unit.x" value="px"/>
<property name="com.jaspersoft.studio.unit.width" value="px"/>
<property name="com.jaspersoft.studio.unit.y" value="px"/>
<property name="com.jaspersoft.studio.unit.height" value="px"/>
</reportElement>
<box topPadding="1" bottomPadding="1">
<pen lineWidth="0.5"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle" markup="html">
<font fontName="Arial" size="8" isBold="false"/>
</textElement>
<text><![CDATA[Вид операции/<br>Номер документа]]></text>
</staticText>
<staticText>
<reportElement stretchType="ContainerBottom" x="0" y="80" width="55" height="35" uuid="0c499bca-42f1-45e0-8a56-2e1468539755">
<property name="com.jaspersoft.studio.unit.x" value="px"/>
<property name="com.jaspersoft.studio.unit.y" value="px"/>
<property name="com.jaspersoft.studio.unit.width" value="px"/>
</reportElement>
<box topPadding="2" bottomPadding="2">
<pen lineWidth="0.5"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="Arial" size="8" isBold="false"/>
</textElement>
<text><![CDATA[Дата зачисления]]></text>
</staticText>
<staticText>
<reportElement stretchType="ContainerHeight" x="215" y="80" width="82" height="35" uuid="5ea7aabc-a5f7-490f-ba2c-628324b229da">
<property name="com.jaspersoft.studio.unit.y" value="px"/>
<property name="com.jaspersoft.studio.unit.width" value="px"/>
<property name="com.jaspersoft.studio.unit.x" value="px"/>
</reportElement>
<box topPadding="1" bottomPadding="1">
<pen lineWidth="0.5"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="Arial" size="8" isBold="false"/>
</textElement>
<text><![CDATA[Сумма пенсионного взноса, руб.]]></text>
</staticText>
<textField>
<reportElement x="300" y="20" width="295" height="20" uuid="1bb76198-e983-4b6f-a56f-507c69061545">
<property name="com.jaspersoft.studio.unit.height" value="px"/>
<property name="com.jaspersoft.studio.unit.x" value="px"/>
<property name="com.jaspersoft.studio.unit.width" value="px"/>
</reportElement>
<textElement verticalAlignment="Middle">
<font fontName="Arial" size="10"/>
</textElement>
<textFieldExpression><![CDATA["№ договора: " +$F{contract_number}]]></textFieldExpression>
</textField>
<staticText>
<reportElement x="0" y="0" width="355" height="20" uuid="d7d54385-7f2b-4c93-b607-9be4f9a18db0">
<property name="com.jaspersoft.studio.unit.width" value="px"/>
<property name="com.jaspersoft.studio.unit.y" value="px"/>
</reportElement>
<box rightPadding="2"/>
<textElement textAlignment="Right" verticalAlignment="Middle">
<font fontName="Arial" size="10"/>
</textElement>
<text><![CDATA[Выписка с именного пенсионного счета (ИПС) Участника на]]></text>
</staticText>
<textField pattern="dd.MM.yyyy">
<reportElement x="355" y="0" width="240" height="20" uuid="4aa514a3-e6f9-4711-88f6-5a4e02945471">
<property name="com.jaspersoft.studio.unit.x" value="px"/>
<property name="com.jaspersoft.studio.unit.width" value="px"/>
</reportElement>
<textElement textAlignment="Left" verticalAlignment="Middle">
<font fontName="Arial" size="10"/>
</textElement>
<textFieldExpression><![CDATA[$P{d2}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="0" y="40" width="300" height="20" uuid="1627adba-a1eb-423d-afbe-1e96cc639362">
<property name="com.jaspersoft.studio.unit.height" value="px"/>
<property name="com.jaspersoft.studio.unit.y" value="px"/>
<property name="com.jaspersoft.studio.unit.width" value="px"/>
</reportElement>
<textElement verticalAlignment="Middle">
<font fontName="Arial" size="10"/>
</textElement>
<textFieldExpression><![CDATA[""]]></textFieldExpression>
</textField>
<textField>
<reportElement x="0" y="60" width="595" height="20" uuid="30cae087-9009-48b7-a8c4-acbb3ebb376c">
<property name="com.jaspersoft.studio.unit.y" value="px"/>
<property name="com.jaspersoft.studio.unit.height" value="px"/>
<property name="com.jaspersoft.studio.unit.width" value="px"/>
</reportElement>
<textElement verticalAlignment="Top">
<font fontName="Arial" size="10"/>
</textElement>
<textFieldExpression><![CDATA["ФИО клиента: " +$F{client}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="0" y="20" width="300" height="20" uuid="3a275771-6294-40a3-a213-c10033c9ead7">
<property name="com.jaspersoft.studio.unit.y" value="px"/>
<property name="com.jaspersoft.studio.unit.height" value="px"/>
<property name="com.jaspersoft.studio.unit.width" value="px"/>
</reportElement>
<textElement textAlignment="Left" verticalAlignment="Middle">
<font fontName="Arial" size="10"/>
</textElement>
<textFieldExpression><![CDATA["№ ИПС клиента: " +$F{sharer_number}]]></textFieldExpression>
</textField>
<staticText>
<reportElement x="505" y="80" width="90" height="35" uuid="f2b2ecd7-1718-49e2-a4ad-6b66f5804677">
<property name="com.jaspersoft.studio.unit.x" value="px"/>
<property name="com.jaspersoft.studio.unit.y" value="px"/>
<property name="com.jaspersoft.studio.unit.height" value="px"/>
<property name="com.jaspersoft.studio.unit.width" value="px"/>
</reportElement>
<box topPadding="1" bottomPadding="1">
<pen lineWidth="0.5"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="Arial" size="8" isBold="false"/>
</textElement>
<text><![CDATA[Сумма выплаты, руб.]]></text>
</staticText>
<staticText>
<reportElement x="300" y="80" width="55" height="35" uuid="39c1e0d6-0e72-459d-b728-817ebf50bb41">
<property name="com.jaspersoft.studio.unit.x" value="px"/>
<property name="com.jaspersoft.studio.unit.y" value="px"/>
<property name="com.jaspersoft.studio.unit.width" value="px"/>
</reportElement>
<box topPadding="1" bottomPadding="1">
<pen lineWidth="0.5"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="Arial" size="8" isBold="false"/>
</textElement>
<text><![CDATA[Дата выплаты]]></text>
</staticText>
<staticText>
<reportElement x="355" y="80" width="150" height="35" uuid="ccbc3595-30d2-4215-b061-19bcd080016a">
<property name="com.jaspersoft.studio.unit.x" value="px"/>
<property name="com.jaspersoft.studio.unit.y" value="px"/>
<property name="com.jaspersoft.studio.unit.width" value="px"/>
<property name="com.jaspersoft.studio.unit.height" value="px"/>
</reportElement>
<box topPadding="1" bottomPadding="1">
<pen lineWidth="0.5"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle" markup="html">
<font fontName="Arial" size="8" isBold="false"/>
</textElement>
<text><![CDATA[Вид операции/<br>Номер документа]]></text>
</staticText>
<textField>
<reportElement x="300" y="40" width="295" height="20" uuid="02927780-1fed-4db9-a8b7-9a9b4403f2a5">
<property name="com.jaspersoft.studio.unit.height" value="px"/>
<property name="com.jaspersoft.studio.unit.x" value="px"/>
<property name="com.jaspersoft.studio.unit.width" value="px"/>
</reportElement>
<textElement verticalAlignment="Middle">
<font fontName="Arial" size="10"/>
</textElement>
<textFieldExpression><![CDATA["Дата договора: " +$F{contract_date}]]></textFieldExpression>
</textField>
</band>
<band height="15" splitType="Stretch">
<property name="com.jaspersoft.studio.unit.height" value="px"/>
<componentElement>
<reportElement stretchType="ContainerHeight" x="0" y="0" width="595" height="15" uuid="c2f82b7b-a78c-4d5a-8242-68f1becdc344">
<property name="com.jaspersoft.studio.unit.width" value="px"/>
</reportElement>
<jr:list xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd" printOrder="Vertical">
<datasetRun subDataset="dsIncome" uuid="8f0248fe-a164-44ed-ace2-191743764653">
<datasetParameter name="d2">
<datasetParameterExpression><![CDATA[$P{d2}]]></datasetParameterExpression>
</datasetParameter>
<datasetParameter name="sharer_id">
<datasetParameterExpression><![CDATA[$P{sharer_id}]]></datasetParameterExpression>
</datasetParameter>
<connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
<returnValue fromVariable="sum_income_list" toVariable="sum_income"/>
<returnValue fromVariable="sum_payment_list" toVariable="sum_payment"/>
</datasetRun>
<jr:listContents height="15" width="595">
<textField textAdjust="StretchHeight" isBlankWhenNull="true">
<reportElement stretchType="ContainerHeight" x="55" y="0" width="160" height="15" uuid="ee5e87ff-4504-454f-b5c8-7c2c5c8cf034">
<property name="com.jaspersoft.studio.unit.x" value="px"/>
<property name="com.jaspersoft.studio.unit.width" value="px"/>
</reportElement>
<box topPadding="2" leftPadding="2" bottomPadding="2">
<pen lineWidth="0.5"/>
</box>
<textElement textAlignment="Left" verticalAlignment="Middle">
<font fontName="Arial" size="8" isBold="false"/>
</textElement>
<textFieldExpression><![CDATA[$F{deposit_document}]]></textFieldExpression>
</textField>
<textField pattern="dd.MM.yyyy" isBlankWhenNull="true">
<reportElement stretchType="ContainerHeight" x="0" y="0" width="55" height="15" uuid="f12586a1-da81-4c43-a166-01cfc6b29689">
<property name="com.jaspersoft.studio.unit.x" value="px"/>
<property name="com.jaspersoft.studio.unit.height" value="px"/>
<property name="com.jaspersoft.studio.unit.width" value="px"/>
</reportElement>
<box topPadding="2" bottomPadding="2">
<pen lineWidth="0.5"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="Arial" size="8" isBold="false"/>
</textElement>
<textFieldExpression><![CDATA[$F{deposit_effective_date}]]></textFieldExpression>
</textField>
<textField pattern="#,##0.00" isBlankWhenNull="true">
<reportElement stretchType="ContainerHeight" x="215" y="0" width="82" height="15" uuid="9259a024-f310-47fd-8371-9ab0d2adfe73">
<property name="com.jaspersoft.studio.unit.x" value="px"/>
<property name="com.jaspersoft.studio.unit.width" value="px"/>
</reportElement>
<box topPadding="2" bottomPadding="2" rightPadding="3">
<pen lineWidth="0.5"/>
</box>
<textElement textAlignment="Right" verticalAlignment="Middle">
<font fontName="Arial" size="8" isBold="false"/>
</textElement>
<textFieldExpression><![CDATA[$F{deposit_value}]]></textFieldExpression>
</textField>
<textField textAdjust="StretchHeight" isBlankWhenNull="true">
<reportElement stretchType="ContainerHeight" x="355" y="0" width="150" height="15" uuid="4c0d4308-a21a-4d8a-a9cd-4e93a30b5d89">
<property name="com.jaspersoft.studio.unit.x" value="px"/>
<property name="com.jaspersoft.studio.unit.width" value="px"/>
</reportElement>
<box topPadding="1" leftPadding="2" bottomPadding="1">
<pen lineWidth="0.5"/>
</box>
<textElement textAlignment="Left" verticalAlignment="Middle">
<font fontName="Arial" size="8" isBold="false"/>
</textElement>
<textFieldExpression><![CDATA[$F{payment_document}]]></textFieldExpression>
</textField>
<textField pattern="dd.MM.yyyy" isBlankWhenNull="true">
<reportElement stretchType="ContainerHeight" x="300" y="0" width="55" height="15" uuid="5422a0ef-c136-4db2-9d14-c76af438ebe4">
<property name="com.jaspersoft.studio.unit.x" value="px"/>
<property name="com.jaspersoft.studio.unit.width" value="px"/>
</reportElement>
<box>
<pen lineWidth="0.5"/>
</box>
<textElement textAlignment="Center" verticalAlignment="Middle">
<font fontName="Arial" size="8" isBold="false"/>
</textElement>
<textFieldExpression><![CDATA[$F{payment_effective_date}]]></textFieldExpression>
</textField>
<textField pattern="#,##0.00" isBlankWhenNull="true">
<reportElement stretchType="ContainerHeight" x="505" y="0" width="90" height="15" uuid="7fd1b723-fbf3-4ed8-be35-558f56e091f8">
<property name="com.jaspersoft.studio.unit.x" value="px"/>
<property name="com.jaspersoft.studio.unit.height" value="px"/>
<property name="com.jaspersoft.studio.unit.width" value="px"/>
</reportElement>
<box rightPadding="3">
<pen lineWidth="0.5"/>
</box>
<textElement textAlignment="Right" verticalAlignment="Middle">
<font fontName="Arial" size="8" isBold="false"/>
</textElement>
<textFieldExpression><![CDATA[$F{payment_value}]]></textFieldExpression>
</textField>
</jr:listContents>
</jr:list>
</componentElement>
</band>
<band height="15">
<property name="com.jaspersoft.studio.unit.height" value="px"/>
<textField pattern="#,##0.00">
<reportElement x="215" y="0" width="82" height="15" uuid="6be42204-acf7-4d31-afb1-02aa48a9104b">
<property name="com.jaspersoft.studio.unit.x" value="px"/>
<property name="com.jaspersoft.studio.unit.width" value="px"/>
</reportElement>
<box rightPadding="2">
<pen lineWidth="0.5"/>
</box>
<textElement textAlignment="Right" verticalAlignment="Middle">
<font fontName="Arial" size="8" isBold="false"/>
</textElement>
<textFieldExpression><![CDATA[$V{sum_income}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="0" y="0" width="215" height="15" uuid="d325bb13-a439-43e6-808b-296925ff6734">
<property name="com.jaspersoft.studio.unit.x" value="px"/>
<property name="com.jaspersoft.studio.unit.width" value="px"/>
<property name="com.jaspersoft.studio.unit.height" value="px"/>
</reportElement>
<box leftPadding="3">
<pen lineWidth="0.5"/>
</box>
<textElement textAlignment="Left" verticalAlignment="Middle" markup="html">
<font fontName="Arial" size="8" isBold="false"/>
</textElement>
<textFieldExpression><![CDATA["Всего начислено"]]></textFieldExpression>
</textField>
<textField pattern="#,##0.00" isBlankWhenNull="true">
<reportElement x="505" y="0" width="90" height="15" uuid="6a55ff9f-d1ba-47b5-b062-526e0acc92f3">
<property name="com.jaspersoft.studio.unit.x" value="px"/>
<property name="com.jaspersoft.studio.unit.width" value="px"/>
</reportElement>
<box rightPadding="3">
<pen lineWidth="0.5"/>
</box>
<textElement textAlignment="Right" verticalAlignment="Middle">
<font fontName="Arial" size="8" isBold="false"/>
</textElement>
<textFieldExpression><![CDATA[$V{sum_payment}!=null ? $V{sum_payment}:0]]></textFieldExpression>
</textField>
<textField>
<reportElement x="300" y="0" width="205" height="15" uuid="42ea7e11-aa5d-4ecb-a177-be7d1a97942d">
<property name="com.jaspersoft.studio.unit.x" value="px"/>
<property name="com.jaspersoft.studio.unit.width" value="px"/>
<property name="com.jaspersoft.studio.unit.height" value="px"/>
</reportElement>
<box leftPadding="3">
<pen lineWidth="0.5"/>
</box>
<textElement textAlignment="Left" verticalAlignment="Middle" markup="html">
<font fontName="Arial" size="8" isBold="false"/>
</textElement>
<textFieldExpression><![CDATA["Всего выплачено"]]></textFieldExpression>
</textField>
</band>
<band height="122">
<property name="com.jaspersoft.studio.unit.height" value="px"/>
<textField>
<reportElement x="0" y="0" width="297" height="20" uuid="c3db8de1-1945-47d5-a2fa-7a85988b69b1">
<property name="com.jaspersoft.studio.unit.height" value="px"/>
<property name="com.jaspersoft.studio.unit.y" value="px"/>
<property name="com.jaspersoft.studio.unit.width" value="px"/>
</reportElement>
<textElement textAlignment="Right" verticalAlignment="Middle">
<font fontName="Arial" size="10"/>
</textElement>
<textFieldExpression><![CDATA["Остаток на счете : "]]></textFieldExpression>
</textField>
<textField pattern="#,##0.00">
<reportElement x="300" y="0" width="295" height="20" uuid="e7e5f902-3682-4e22-b0b3-90191d246394">
<property name="com.jaspersoft.studio.unit.height" value="px"/>
<property name="com.jaspersoft.studio.unit.y" value="px"/>
<property name="com.jaspersoft.studio.unit.width" value="px"/>
</reportElement>
<box leftPadding="2"/>
<textElement textAlignment="Left" verticalAlignment="Middle">
<font fontName="Arial" size="10"/>
</textElement>
<textFieldExpression><![CDATA[$V{sum_payment}!=null ? $V{sum_income}.subtract($V{sum_payment}):$V{sum_income}]]></textFieldExpression>
</textField>
<staticText>
<reportElement x="0" y="20" width="595" height="13" uuid="716087e8-6af4-4104-9160-8685fcd6cacf">
<property name="com.jaspersoft.studio.unit.y" value="px"/>
<property name="com.jaspersoft.studio.unit.width" value="px"/>
<property name="com.jaspersoft.studio.unit.height" value="px"/>
</reportElement>
<box rightPadding="7"/>
<textElement textAlignment="Left" verticalAlignment="Middle">
<font fontName="Arial" size="10"/>
</textElement>
<text><![CDATA[Персональный менеджер группы по обслуживанию клиентов]]></text>
</staticText>
<textField>
<reportElement x="0" y="81" width="595" height="20" uuid="a2125461-6ae4-489b-9398-b3655829350e">
<property name="com.jaspersoft.studio.unit.y" value="px"/>
<property name="com.jaspersoft.studio.unit.height" value="px"/>
<property name="com.jaspersoft.studio.unit.width" value="px"/>
</reportElement>
<textElement verticalAlignment="Bottom">
<font fontName="Arial" size="10"/>
</textElement>
<textFieldExpression><![CDATA["Исп. " +$F{agent_position}+" "+$F{agent_fio}]]></textFieldExpression>
</textField>
<textField isBlankWhenNull="true">
<reportElement x="0" y="101" width="595" height="20" uuid="0a919c81-8c8a-4196-9f38-198b851a5486">
<property name="com.jaspersoft.studio.unit.y" value="px"/>
<property name="com.jaspersoft.studio.unit.height" value="px"/>
<property name="com.jaspersoft.studio.unit.width" value="px"/>
</reportElement>
<textElement verticalAlignment="Middle">
<font fontName="Arial" size="10"/>
</textElement>
<textFieldExpression><![CDATA[$F{agent_phone}]]></textFieldExpression>
</textField>
<textField>
<reportElement x="0" y="46" width="595" height="35" uuid="52087be3-23c4-4a8a-88e4-4a930f60143a">
<property name="com.jaspersoft.studio.unit.y" value="px"/>
<property name="com.jaspersoft.studio.unit.height" value="px"/>
<property name="com.jaspersoft.studio.unit.width" value="px"/>
</reportElement>
<textElement verticalAlignment="Bottom">
<font fontName="Arial" size="10"/>
</textElement>
<textFieldExpression><![CDATA["Дата печати: " +$F{print_date}]]></textFieldExpression>
</textField>
<staticText>
<reportElement x="1" y="33" width="595" height="13" uuid="36432511-3556-4c00-8a10-890d2bfbec88">
<property name="com.jaspersoft.studio.unit.y" value="px"/>
<property name="com.jaspersoft.studio.unit.width" value="px"/>
</reportElement>
<box rightPadding="7"/>
<textElement textAlignment="Left" verticalAlignment="Middle">
<font fontName="Arial" size="10"/>
</textElement>
<text><![CDATA[отдела развития и реализации пенсионного обеспечения ___________________ Иванов И.В.]]></text>
</staticText>
</band>
</detail>
</jasperReport>
