228 lines
11 KiB
Plaintext
228 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>
|
||
|
||
<cffunction name="myNumFmt">
|
||
<cfargument name="a"/>
|
||
<cfreturn (isNumeric(a))? numberFormat(a,",.00") : ""/>
|
||
</cffunction>
|
||
|
||
<cffunction name="safeNum">
|
||
<cfargument name="a"/>
|
||
<cfreturn (isNumeric(a))? a : 0/>
|
||
</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#/>
|
||
|
||
|
||
<cfquery name="qRead" datasource="#request.DS#">
|
||
select
|
||
<d:field_set titleMapOut="titleMap" lengthOut="fieldCount">
|
||
<d:field title="Месяц" cfSqlType="CF_SQL_TIMESTAMP">m</d:field>
|
||
<d:field title="Инсталл" cfSqlType="CF_SQL_NUMERIC">income_install</d:field>
|
||
<d:field title="Ежемес" cfSqlType="CF_SQL_NUMERIC">income_recurring</d:field>
|
||
<d:field title="Выручка" cfSqlType="CF_SQL_NUMERIC">income_install+income_recurring as income</d:field>
|
||
</d:field_set>
|
||
from (
|
||
select
|
||
--install
|
||
DATE_TRUNC('month', dayscale.dt)::date as m
|
||
,sum(siv.price*siv.quantity*a.probability_perc/100
|
||
+ COALESCE(siv0.price*siv0.quantity*(100-a.probability_perc)/100, 0)
|
||
) as income_install
|
||
,0 as income_recurring
|
||
from
|
||
(SELECT dd::date as dt FROM generate_series (
|
||
'#DAYSCALE_START#'::timestamp
|
||
,'#DAYSCALE_FINISH#'::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)
|
||
left outer join agreement a0 on (s.contract_id=a0.contract_id /*AND siv.agreement_version=a0.agreement_version*/ AND a0.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.agreement_version < a.agreement_version
|
||
AND ia.is_actual
|
||
))
|
||
left outer join specification_item_version siv0 on (siv0.specification_item_uid=si.specification_item_uid
|
||
AND siv0.agreement_version=a0.agreement_version)
|
||
where 1=1 <m:filter_build filter=#pageInfo.settings.filter#/>
|
||
group by DATE_TRUNC('month', dayscale.dt)
|
||
|
||
union all
|
||
|
||
select
|
||
DATE_TRUNC('month', dayscale.dt)::date as m
|
||
,0
|
||
/*,sum(siv.price*siv.quantity*a.probability_perc/100/dayscale.monthdays
|
||
+ COALESCE(siv0.price*siv0.quantity*(100-a.probability_perc)/100/dayscale.monthdays, 0)
|
||
)*/
|
||
<!--- ,COALESCE(sum(
|
||
case when (siv.dt_to > dayscale.dt OR siv.dt_to IS NULL) then
|
||
siv.price*siv.quantity*COALESCE(
|
||
case when a.is_actual then 100 else a.probability_perc end
|
||
,0)/100/dayscale.monthdays
|
||
/*до окончания свежей сделки она суммируется с прошлой актуальной по правилу вероятностей, считая вероятность актуальной за сделки за 1*/
|
||
else 0 /*по окончании свежей сделки она дает нулевой вклад*/
|
||
end
|
||
),0)
|
||
+ COALESCE(sum(
|
||
case when (siv0.dt_to > dayscale.dt OR siv0.dt_to IS NULL) then 0 else
|
||
siv0.price*siv0.quantity*(100-COALESCE(
|
||
case when a.is_actual then 100 else a.probability_perc end
|
||
,0))/100/dayscale.monthdays
|
||
/*по окончании свежей сделки*/
|
||
end
|
||
),0) --->
|
||
,sum(
|
||
COALESCE(case when (siv.dt_to > dayscale.dt OR siv.dt_to IS NULL) then
|
||
siv.price*siv.quantity*(
|
||
case when a.is_actual then 100 else a.probability_perc end
|
||
)/100/dayscale.monthdays
|
||
else 0 /*по окончании рабочей сделки она дает нулевой вклад*/
|
||
end,0)
|
||
+
|
||
COALESCE(case when (siv0.dt_to > dayscale.dt OR siv0.dt_to IS NULL) then
|
||
siv0.price/dayscale.monthdays*siv0.quantity*(100- COALESCE(
|
||
case when a.is_actual then 100 else a.probability_perc end
|
||
,0))/100
|
||
else 0 /*по окончании базовой сделки она дает нулевой вклад*/
|
||
end,0)
|
||
)
|
||
from (SELECT dd::date as dt
|
||
, DATE_PART('days', DATE_TRUNC('month', dd) + '1 MONTH'::INTERVAL - '1 DAY'::INTERVAL) monthdays
|
||
FROM generate_series(
|
||
'#DAYSCALE_START#'::timestamp
|
||
,'#DAYSCALE_FINISH#'::timestamp
|
||
,'1 day'::interval) dd
|
||
) dayscale
|
||
left outer join specification_item_version siv on (dayscale.dt >= siv.dt_from /*AND (siv.dt_to > dayscale.dt OR siv.dt_to IS NULL)*/
|
||
AND siv.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=siv.specification_item_uid
|
||
AND (dayscale.dt >= iiv.dt_from /*AND (iiv.dt_to > dayscale.dt OR iiv.dt_to IS NULL)*/)))
|
||
/*AND ia.is_actual*/ /*свежее соглашение не обязано быть действующим*/
|
||
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 a.agreement_version=siv.agreement_version)
|
||
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)
|
||
-- находим предыдущее действующее соглашение
|
||
-- если свежее соглашение действует, то впр
|
||
-- нам нужно, чтобы в прошлом (до dt_from свежего соглашения) свежак был прозрачен,
|
||
-- а в будущем (после dt_to) факт перетирался свежим соглашением
|
||
left outer join agreement a0 on (s.contract_id=a0.contract_id AND a0.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.agreement_version < siv.agreement_version
|
||
AND ia.is_actual /*именно действующее соглашение, принимаемое за факт*/
|
||
)
|
||
)
|
||
left outer join specification_item_version siv0 on (siv0.specification_item_uid=si.specification_item_uid
|
||
AND siv0.agreement_version=a0.agreement_version AND (dayscale.dt >= siv0.dt_from /*AND (siv0.dt_to > dayscale.dt OR siv0.dt_to IS NULL)*/)
|
||
)
|
||
where 1=1 <m:filter_build filter=#pageInfo.settings.filter#/>
|
||
group by DATE_TRUNC('month', dayscale.dt)
|
||
) install_fix_payg
|
||
order by 1
|
||
<!---
|
||
order by <m:order_build sortArray=#pageInfo.settings.sort.sortArray# fieldCount=#fieldCount#/> --->
|
||
</cfquery>
|
||
|
||
|
||
|
||
|
||
<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>Выручка по месяцам 2023-01-01 .. 2029-12-31</b></cfoutput>
|
||
</layout:attribute>
|
||
|
||
</layout:page>
|
||
|
||
<cfif pageInfo.readPermitted() AND !pageInfo.status.errorState>
|
||
<cfoutput><b>#qRead.recordCount#</b> записей</cfoutput>
|
||
<a href="?output_xls" title="экспорт в Excel" style="margin:.5em; height:100%;" target="_blank"><img src="img/xls.gif" style="vertical-align:text-bottom;"/></a>
|
||
<a href="?output_json" title="экспорт в json" style="margin:.5em; height:100%;" target="_blank"><img src="img/json.svg" style="vertical-align:text-bottom;" width="13" height="13"/></a>
|
||
<br/>
|
||
Месяцы без данных не показаны. Вариант с локальной рабочей версией строки (инстанса)
|
||
<table class="worktable">
|
||
|
||
<thead>
|
||
<tr>
|
||
<th>Месяц</th>
|
||
<th>Инсталл</th>
|
||
<th>Ежемес</th>
|
||
<th>Выручка</th>
|
||
<th>Прирост</th>
|
||
<th>Прирост ежемес</th>
|
||
</tr>
|
||
</thead>
|
||
<cfset income_old=0/>
|
||
<cfset income_recurring_old=0/>
|
||
<cfoutput query="qRead">
|
||
<tr>
|
||
<td class="c">#dateFormat(m,"MM.YYYY")#</td>
|
||
<td class="r"><cfif income_install NEQ 0>#myNumFmt(income_install)#</cfif></td>
|
||
<td class="r"><cfif income_recurring NEQ 0>#myNumFmt(income_recurring)#</cfif></td>
|
||
<td class="r"><cfif income NEQ 0>#myNumFmt(income)#</cfif></td>
|
||
<td class="r"><cfif safeNum(income) - income_old NEQ 0>#myNumFmt(safeNum(income) - income_old)#</cfif></td>
|
||
<cfset income_old = safeNum(income)/>
|
||
<td class="r"><cfif income_recurring-income_recurring_old NEQ 0>#myNumFmt(income_recurring-income_recurring_old)#</cfif></td>
|
||
<cfset income_recurring_old = safeNum(income_recurring)/>
|
||
</tr>
|
||
</cfoutput>
|
||
</table>
|
||
|
||
|
||
</cfif>
|
||
<layout:page section="footer"/>
|