release 9;
datawindow ( units=0 timer_interval=0 color=31253995 processing=1 print.printername=""  print.documentname=""  print.orientation=0 print.margin.left=110 print.margin.right=110 print.margin.top=96 print.margin.bottom=96 print.paper.size=0 print.paper.source=0 print.canusedefaultprinter=yes grid.lines=0 selected.mouse=yes)
header(height=244 color="536870912" )
summary(height=0 color="536870912" )
footer(height=0 color="536870912" )
detail(height=72 color="536870912" )
table(column=(type=char(12) updatewhereclause=yes name=isin_code dbname="isin_code"  )
column=(type=number updatewhereclause=yes name=price_type dbname="price_type"  )
column=(type=char(10) updatewhereclause=yes name=portfolio_date dbname="portfolio_date"  )
column=(type=number updatewhereclause=yes name=portfolio_price dbname="price"  )
 retrieve="SELECT
(select e.isin_code from ertekpapir e, ertekpapir_plus ep where e.id = ep.id and ep.kibocsato_ref  = pm.partner_id) as isin_code,
1 as price_type,
to_char(t_plus(p.dt,1) ,'dd.mm.yyyy') as portfolio_date,
round(p.assetvalue8,4) as price

FROM 
portfolio p,
portfolio_master pm
WHERE
p.master_id = pm.id and 
p.dt>=:dt_from and p.dt<=:dt_till

UNION all

SELECT
(select e.isin_code from ertekpapir e, ertekpapir_plus ep where e.id = ep.id and ep.kibocsato_ref  = pm.partner_id) as isin_code,
2 as price_type,
to_char(t_plus(p.dt,1) ,'dd.mm.yyyy') as portfolio_date,
case
	when pm.id = 155 and p.assetvalue8>0 then round(round(p.assetvalue8,4)*(1/*+(1/100)*/),4)
	when pm.id = 156 and p.assetvalue8>0 then round( round(p.assetvalue8,4)*(1/*+(0.5/100)*/),4)
	when pm.id = 157 and p.assetvalue8>0 then round(round(p.assetvalue8,4)*(1+(0.25/100)),4)
	when p.assetvalue8=0 then 10
end as price

FROM 
portfolio p,
portfolio_master pm
WHERE
p.master_id = pm.id and 
p.dt>=:dt_from and p.dt<=:dt_till


UNION ALL

SELECT
(select e.isin_code from ertekpapir e, ertekpapir_plus ep where e.id = ep.id and ep.kibocsato_ref  = pm.partner_id) as isin_code,
3 as price_type,
to_char(t_plus(p.dt,1) ,'dd.mm.yyyy') as portfolio_date,
case
	when pm.id = 155 then round(round(p.assetvalue8,4)*(1-(0.5/100)),4)
	when pm.id = 156 then round(round(p.assetvalue8,4)*(1-(0.5/100)),4)
	when pm.id = 157 then round(p.assetvalue8,4)
end as price

FROM 
portfolio p,
portfolio_master pm
WHERE
p.master_id = pm.id and 
p.dt>=:dt_from and p.dt<=:dt_till

"
  arguments=(("dt_from", datetime), ("dt_till", datetime))
 sort="portfolio_date A, isin_code A, price_type A"
)
text(name=isin_code_t visible="0" tag="xls_col=a;" band=header font.charset="0" font.face="Arial" font.family="2" font.height="-10" font.pitch="2" font.weight="700" background.mode="2" background.color="67108864" color="33554432" alignment="2" border="2" x="9" y="172" height="64" width="448" text="ISIN код" )
text(name=portfolio_price_t visible="0" tag="xls_col=d;" band=header font.charset="0" font.face="Arial" font.family="2" font.height="-10" font.pitch="2" font.weight="700" background.mode="2" background.color="67108864" color="33554432" alignment="2" border="2" x="1289" y="172" height="64" width="416" text="Цена" )
text(name=price_type_t visible="0" tag="xls_col=b;" band=header font.charset="0" font.face="Arial" font.family="2" font.height="-10" font.pitch="2" font.weight="700" background.mode="2" background.color="67108864" color="33554432" alignment="2" border="2" x="466" y="172" height="64" width="485" text="Тип на цената" )
text(name=portfolio_date_t visible="0" tag="xls_col=c;" band=header font.charset="0" font.face="Arial" font.family="2" font.height="-10" font.pitch="2" font.weight="700" background.mode="2" background.color="67108864" color="33554432" alignment="2" border="2" x="960" y="172" height="64" width="320" text="Дата" )
column(name=isin_code visible="1" tag="xls_col=a;" band=detail id=1 x="9" y="0" height="64" width="448" color="33554432" border="0" alignment="0" format="[general]" edit.autohscroll=no edit.autoselect=yes edit.autovscroll=no edit.case=any edit.codetable=no edit.displayonly=no edit.hscrollbar=no edit.imemode=0 edit.limit=0 edit.password=no edit.vscrollbar=no edit.validatecode=no edit.nilisnull=no edit.required=no criteria.required=no criteria.override_edit=no crosstab.repeat=no background.mode="1" background.color="553648127" font.charset="204" font.face="arial cyr" font.family="2" font.height="-10" font.pitch="2" font.weight="400" tabsequence=0 )
column(name=portfolio_price visible="1" tag="xls_col=d;" band=detail id=4 x="1289" y="0" height="64" width="416" color="33554432" border="0" alignment="1" format="##0.0000" edit.autohscroll=no edit.autoselect=yes edit.autovscroll=no edit.case=any edit.codetable=no edit.displayonly=no edit.hscrollbar=no edit.imemode=0 edit.limit=0 edit.password=no edit.vscrollbar=no edit.validatecode=no edit.nilisnull=no edit.required=no criteria.required=no criteria.override_edit=no crosstab.repeat=no background.mode="1" background.color="553648127" font.charset="0" font.face="Arial" font.family="2" font.height="-10" font.pitch="2" font.weight="400" tabsequence=0 )
column(name=price_type visible="1" tag="xls_col=b;" band=detail id=2 x="466" y="0" height="64" width="485" color="33554432" border="0" alignment="2" format="[general]" edit.autohscroll=no edit.autoselect=yes edit.autovscroll=no edit.case=any edit.codetable=no edit.displayonly=no edit.hscrollbar=no edit.imemode=0 edit.limit=0 edit.password=no edit.vscrollbar=no edit.validatecode=no edit.nilisnull=no edit.required=no criteria.required=no criteria.override_edit=no crosstab.repeat=no background.mode="1" background.color="553648127" font.charset="0" font.face="Arial" font.family="2" font.height="-10" font.pitch="2" font.weight="400" tabsequence=0 )
column(name=portfolio_date visible="1" tag="xls_col=c;" band=detail id=3 x="960" y="0" height="64" width="320" color="33554432" border="0" alignment="1" format="[general]" edit.autohscroll=no edit.autoselect=yes edit.autovscroll=no edit.case=any edit.codetable=no edit.displayonly=no edit.hscrollbar=no edit.imemode=0 edit.limit=0 edit.password=no edit.vscrollbar=no edit.validatecode=no edit.nilisnull=no edit.required=no criteria.required=no criteria.override_edit=no crosstab.repeat=no background.mode="1" background.color="553648127" font.charset="204" font.face="arial cyr" font.family="2" font.height="-10" font.pitch="2" font.weight="400" tabsequence=0 )
htmltable(border="1" )
htmlgen(clientComputedFields="1" clientEvents="1" clientFormatting="0" clientScriptable="0" clientValidation="1" generateJavaScript="1" netscapeLayers="0" netscapelayers="0" encodeSelfLinkArgs="1" )
export.xml(headGroups="1" includewhitespace="0" metadatatype=0 savemetadata=0 )
import.xml()
export.pdf(method=0distill.customPostScript="0" xslfop.print="0" )