212 lines
11 KiB
Plaintext
212 lines
11 KiB
Plaintext
<cfsilent>
|
||
<cfimport prefix="m" taglib="lib"/>
|
||
<cfimport prefix="c" taglib="lib/controls"/>
|
||
<cfimport prefix="d" taglib="lib/data"/>
|
||
<cfimport prefix="layout" taglib="layout"/>
|
||
</cfsilent><m:silent silent="No">
|
||
|
||
<cffunction name="hideNonPositive">
|
||
<cfargument name="a"/>
|
||
<cfreturn (a GT 0)? a : ""/>
|
||
</cffunction>
|
||
|
||
<m:prepare_ls entity="specification_item" accessObject="" pageInfoOut="pageInfo" trackOut="tr"/>
|
||
|
||
<!--- <m:filter_settings target="#pageInfo.entity#_ls">
|
||
<m:filterparam filter=#filter# param="quickfilter" ftype="string" prefix="%" suffix="%" expression="((p.project like ?) OR (p.customer like ?) OR (p.customer_alias like ?) OR (p.specification_item_class_type like ?) OR (p.division like ?) OR (p.performer_short like ?))" default=""/>
|
||
</m:filter_settings> --->
|
||
<!--- <cfset pageInfo.settings.filter=#filter#/> --->
|
||
|
||
<cftry>
|
||
<cfquery name="qRead" datasource="#request.DS#">
|
||
select
|
||
DATE_TRUNC('month', siv.dt_from)::date AS month_start
|
||
,sum(siv.price*siv.quantity*a.probability_perc/100) as income
|
||
|
||
from
|
||
(SELECT dd::date as dt FROM generate_series
|
||
( '2024-01-01'::timestamp
|
||
, '2029-12-31'::timestamp
|
||
, '1 day'::interval) dd) dayscale
|
||
join specification_item_version siv on (dayscale.dt=siv.dt_from)
|
||
join specification_item si on (siv.specification_item_uid=si.specification_item_uid AND si.pricing_model_id in (1))
|
||
join specification s on (si.specification_id=s.specification_id)
|
||
join agreement a on (s.contract_id=a.contract_id AND siv.agreement_version=a.agreement_version AND a.agreement_version=(select max(iiv.agreement_version) from specification_item_version iiv
|
||
join specification_item ii on (iiv.specification_item_uid=ii.specification_item_uid)
|
||
join specification isp on (ii.specification_id=isp.specification_id)
|
||
join agreement ia on (isp.contract_id=ia.contract_id AND iiv.agreement_version=ia.agreement_version)
|
||
where iiv.specification_item_uid=si.specification_item_uid
|
||
AND ia.is_actual
|
||
))
|
||
join contract d on s.contract_id=d.contract_id
|
||
join contragent k on d.contragent_id=k.contragent_id
|
||
join svc on (si.svc_id=svc.svc_id)
|
||
where si.pricing_model_id=1
|
||
group by DATE_TRUNC('month', dayscale.dt)
|
||
|
||
union all
|
||
|
||
select
|
||
|
||
DATE_TRUNC('month', dayscale.dt)::date
|
||
,sum(siv.price*siv.quantity*a.probability_perc/100/dayscale.days)
|
||
|
||
|
||
from
|
||
(SELECT dd::date as dt, DATE_PART('days',
|
||
DATE_TRUNC('month', dd) + '1 MONTH'::INTERVAL - '1 DAY'::INTERVAL
|
||
) days FROM generate_series
|
||
( '2025-01-01'::timestamp
|
||
, '2025-12-31'::timestamp
|
||
, '1 day'::interval) dd) dayscale
|
||
join specification_item_version siv on (dayscale.dt >= siv.dt_from AND (siv.dt_to >= dayscale.dt OR siv.dt_to IS NULL))
|
||
join specification_item si on (siv.specification_item_uid=si.specification_item_uid AND si.pricing_model_id in (2,3))
|
||
join specification s on (si.specification_id=s.specification_id )
|
||
join agreement a on (s.contract_id=a.contract_id AND siv.agreement_version=a.agreement_version AND a.agreement_version=(select max(iiv.agreement_version) from specification_item_version iiv
|
||
join specification_item ii on (iiv.specification_item_uid=ii.specification_item_uid)
|
||
join specification isp on (ii.specification_id=isp.specification_id)
|
||
join agreement ia on (isp.contract_id=ia.contract_id AND iiv.agreement_version=ia.agreement_version)
|
||
where iiv.specification_item_uid=si.specification_item_uid
|
||
AND ia.is_actual
|
||
))
|
||
join contract d on s.contract_id=d.contract_id
|
||
join contragent k on d.contragent_id=k.contragent_id
|
||
join svc on (si.svc_id=svc.svc_id)
|
||
|
||
group by DATE_TRUNC('month', dayscale.dt)
|
||
order by DATE_TRUNC('month', dayscale.dt)
|
||
<!--- where 1=1 <m:filter_build filter=#pageInfo.settings.filter#/>
|
||
order by <m:order_build sortArray=#pageInfo.settings.sort.sortArray# fieldCount=#fieldCount#/> --->
|
||
</cfquery>
|
||
|
||
|
||
|
||
<cfcatch type="database">
|
||
<m:ls_catch catch=#cfcatch# status=#pageInfo.status#/>
|
||
</cfcatch>
|
||
</cftry>
|
||
|
||
<cfquery name="qCountTotal" datasource="#request.DS#">
|
||
select count(*) as cnt from specification_item where 1=1
|
||
</cfquery>
|
||
|
||
</m:silent><!---
|
||
-----------------------------------------------------------------------------------------
|
||
-----------------------------------------------------------------------------------------
|
||
-----------------------------------------------------------------------------------------
|
||
---><cfif isDefined("output_xls")>
|
||
<layout:xml qRead=#qRead# titleMap=#titleMap# filename="#pageInfo.entity#.xml"/>
|
||
<cfabort/>
|
||
</cfif><cfif isDefined("output_json")>
|
||
<layout:json qRead=#qRead# titleMap=#titleMap# filename="#pageInfo.entity#.json"/>
|
||
<cfabort/>
|
||
</cfif><!---
|
||
---><layout:page section="header" pageInfo=#pageInfo#>
|
||
|
||
<layout:attribute name="title">
|
||
<cfoutput><b>Экземпляры услуг (текущее состояние)</b></cfoutput>
|
||
</layout:attribute>
|
||
<layout:attribute name="controls">
|
||
<!---skip filter link, filter is not implemented--->
|
||
<!---<layout:language_switch/>--->
|
||
</layout:attribute>
|
||
</layout:page>
|
||
|
||
<cfif pageInfo.readPermitted() AND !pageInfo.status.errorState>
|
||
|
||
<layout:grid_summary
|
||
recordCount=#qRead.recordCount#
|
||
totalCount=#qCountTotal.cnt#
|
||
footerOut="gridFooter"
|
||
excelLink="Yes"
|
||
jsonLink="Yes"
|
||
/>
|
||
|
||
|
||
<!--- так довольно громоздко, но можно обойтись без Dynamic Evaluation --->
|
||
<cfset queryAddColumn(qRead,'f_link_view_edit')/>
|
||
<cfset titleMap.f_link_view_edit={ordinal=#StructCount(titleMap)+1#}/><!--- *** Это легко забыть, потому что TitleMap никак не сцеплен с qRead. Напрашивается: упаковать их в одну обертку (но тогда обращение станет длиннее). Еще идея - назвать qRead и TitleMap так, чтобы была видна их связь. --->
|
||
<!--- <cfdump var=#titleMap#/> --->
|
||
<cfset queryAddColumn(qRead,'f_deal_uid')/>
|
||
<cfset titleMap.f_deal_uid=titleMap.deal_uid/>
|
||
|
||
<cfset queryAddColumn(qRead,'f_specification_item_uid')/>
|
||
<cfset titleMap.f_specification_item_uid=titleMap.specification_item_uid/>
|
||
|
||
<cfset queryAddColumn(qRead,'f_contract')/>
|
||
<cfset titleMap.f_contract=titleMap.contract/>
|
||
|
||
<cfset queryAddColumn(qRead,'f_specification')/>
|
||
<cfset titleMap.f_specification=titleMap.specification/>
|
||
|
||
<cfset queryAddColumn(qRead,'f_version_prev')/>
|
||
<cfset titleMap.f_version_prev=titleMap.version_prev/>
|
||
|
||
<cfset queryAddColumn(qRead,'f_link_del')/>
|
||
<cfset titleMap.f_link_del={ordinal=0}/>
|
||
|
||
<!--- <cfdump var=#titleMap#/> --->
|
||
<cfoutput query=#qRead# startRow=#pageInfo.nStart# maxRows=#pageInfo.recordsPerPage#><!--- *** Здесь некомфортное дублирование startRow, maxRows с вызовом c:table ниже, причем в c:table оно закопано в модуль, и аргументы называются по-разному... но мы хотим немного сэкономить и не обрабатывать весь резалтсет --->
|
||
<!--- *** тут можно было бы спокойно использовать суррогатный альтернативный ключ, но хочется проверить, как будет работать track на составном ключе. Кстати, я все равно его использую как ancor name --->
|
||
<cfsavecontent variable="qRead.f_link_view_edit">
|
||
<a href="specification_item_version.cfm?specification_item_uid=#specification_item_uid#&agreement_version=#agreement_version#&#tr.fwx#" name="#specification_item_uid#" <cfif pageInfo.writePermitted()>title="редактировать" class="edit"<cfelse>title="просмотр" class="view"</cfif>></a>
|
||
</cfsavecontent>
|
||
<cfsavecontent variable="qRead.f_deal_uid">
|
||
<a href="agreement.cfm?contract_id=#contract_id#&agreement_version=#agreement_version#&#tr.fwx#">#deal_uid#</a>
|
||
</cfsavecontent>
|
||
<cfsavecontent variable="qRead.f_specification_item_uid">
|
||
<a href="specification_item.cfm?specification_item_uid=#specification_item_uid#&#tr.fwx#">#specification_item_uid#</a>
|
||
</cfsavecontent>
|
||
<cfsavecontent variable="qRead.f_contract">
|
||
<a href="contract.cfm?contract_id=#contract_id#&#tr.fwx#">#contract# #dateformat(dt_contract,"YYYY-MM-DD")#</a>
|
||
</cfsavecontent>
|
||
<cfsavecontent variable="qRead.f_specification">
|
||
<a href="specification.cfm?specification_id=#specification_id#&#tr.fwx#">#specification#</a>
|
||
</cfsavecontent>
|
||
<cfsavecontent variable="qRead.f_version_prev">
|
||
<a href="specification_item_version.cfm?specification_item_uid=#specification_item_uid#&agreement_version=#agreement_version#&#tr.fwx#">#version_prev#</a>
|
||
</cfsavecontent>
|
||
<cfsavecontent variable="qRead.f_link_del">
|
||
<cfif pageInfo.writePermitted()><a href="specification_item_version_del.cfm?specification_item_uid=#specification_item_uid#&agreement_version=#agreement_version#&#tr.fwx#" class="del" title="удалить"></a></cfif>
|
||
</cfsavecontent>
|
||
</cfoutput>
|
||
|
||
<c:table query=#qRead# recordsPerPage=#pageInfo.recordsPerPage# nStart=#pageInfo.nStart# titleMap=#titleMap# sortArray=#pageInfo.settings.sort.sortArray# class="worktable wide">
|
||
<c:column width="1%" sortable="false"><!---*** class="c" не пробрасывается --->
|
||
<c:th><!--- <a href="specification_item.cfm?contract_id=&specification_item_version="><img src="img/add.gif"/></a> ---></c:th>
|
||
<c:td field="f_link_view_edit" class="c"/>
|
||
</c:column>
|
||
|
||
<c:column width="12%" field="f_deal_uid"/>
|
||
<c:column width="12%" field="f_specification_item_uid"/>
|
||
<c:column width="3%" field="probability_perc"><c:td class="r"/></c:column>
|
||
<c:column width="2%" field="f_specification"/>
|
||
<c:column width="5%" field="f_contract"/>
|
||
<!--- <c:column width="5%" field="dt_contract" formatter=#function(dt){return dateformat(dt,"YYYY-MM-DD");}#><c:td class="c"/></c:column> --->
|
||
<c:column width="7%" field="contragent"/>
|
||
<c:column width="7%" field="svc"/>
|
||
<c:column width="1%" field="pricing_model_short"/>
|
||
<c:column width="5%" field="dt_from" formatter=#function(dt){return dateformat(dt,"YYYY-MM-DD");}#><c:td class="c"/></c:column>
|
||
<c:column width="5%" field="dt_to" formatter=#function(dt){return dateformat(dt,"YYYY-MM-DD");}#><c:td class="c"/></c:column>
|
||
<c:column width="3%" field="agreement_version"><c:td class="c"/></c:column>
|
||
<c:column width="3%" field="quantity"><c:td class="r"/></c:column>
|
||
<c:column width="3%" field="price"><c:td class="r"/></c:column>
|
||
<c:column width="3%" field="cost"><c:td class="r"/></c:column>
|
||
<c:column width="3%" field="f_version_prev"><c:td class="c"/></c:column>
|
||
<c:column width="3%" field="quantity_prev"><c:td class="r"/></c:column>
|
||
<c:column width="3%" field="price_prev"><c:td class="r"/></c:column>
|
||
<c:column width="3%" field="cost_prev"><c:td class="r"/></c:column>
|
||
<c:column width="3%" field="cost_diff"><c:td class="r"/></c:column>
|
||
<c:column width="3%" field="cost_diff_prob"><c:td class="r"/></c:column>
|
||
<c:column width="1%" sortable="false">
|
||
<c:td field="f_link_del" class="c"/>
|
||
</c:column>
|
||
</c:table>
|
||
<cfoutput>#gridFooter#</cfoutput>
|
||
Расчета по дням тут нет.
|
||
Дата по на самом деле нигде не используется.
|
||
Важное допущение: вероятность прошлой сделки принимается за 100% (то есть изменения имеют смысл только к действующему договору)
|
||
|
||
</cfif>
|
||
<layout:page section="footer"/>
|