217 lines
9.5 KiB
Plaintext
217 lines
9.5 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:prepare_ls entity="vdc" accessObject="" pageInfoOut="pageInfo" trackOut="tr"/>
|
||
|
||
|
||
|
||
|
||
<cfquery name="qStorage" datasource="billing-vc" ><!--- cachedWithin=#createTimeSpan(0, 0, 25, 0)# --->
|
||
SELECT
|
||
<d:field_set titleMapOut="titleMapStor" lengthOut="fieldCountStor">
|
||
<d:field title="Тенант">t.name AS tenant</d:field>
|
||
<d:field title="WZ">upper(m.clientid) AS wz</d:field>
|
||
<d:field title="status">m.status</d:field>
|
||
<d:field title="End of Test">m.end_test_date</d:field>
|
||
<d:field title="End of Test">null as dt_end_of_test</d:field>
|
||
<d:field title="VDC">v.name AS vdc</d:field>
|
||
<d:field title="Provider VDC">p.name AS provider_vdc</d:field>
|
||
<d:field title="code">CASE
|
||
WHEN v.name LIKE '%-v1cl1%' THEN 'iaas.ngc.i29'
|
||
WHEN v.name LIKE '%-v1cl2%' THEN 'iaas.ngc.i31'
|
||
WHEN v.name LIKE '%-v1cl4%' THEN 'iaas.ngc.a28'
|
||
WHEN v.name LIKE '%-v1cl5%' THEN 'iaas.ngc.a45'
|
||
WHEN v.name LIKE '%-v1cl6%' THEN 'iaas.ngc.i28'
|
||
WHEN v.name LIKE '%-v1cl7%' THEN 'iaas.ngc.a40'
|
||
ELSE '['||v.name||']'
|
||
END AS code</d:field>
|
||
<d:field title="Storage Profile">sp.name AS storage_profile</d:field>
|
||
<d:field title="Limit GB">round(AVG(c."limit")/1024.) AS limit_gb</d:field>
|
||
<d:field title="Allocation GB">round(AVG(c."used")/1024.) AS alloc_gb</d:field>
|
||
<d:field title="Timestamp">tm.ts</d:field>
|
||
</d:field_set>
|
||
FROM ngcloud_ru.capacity_storage c
|
||
JOIN ngcloud_ru.storage_profile_types sp ON c.storage_profile_types_id = sp.id
|
||
JOIN ngcloud_ru.vdc v ON c.vdc_id = v.id
|
||
JOIN ngcloud_ru.provider_vdc p ON v.provider_vdc_id = p.id
|
||
JOIN ngcloud_ru.tenant t ON t.id = v.tenant_id
|
||
LEFT JOIN LATERAL (
|
||
SELECT tm.ts, tm.metadata_id
|
||
FROM ngcloud_ru.tenant_metadata tm
|
||
WHERE tm.tenant_id = t.id
|
||
ORDER BY tm.ts DESC, tm.metadata_id DESC
|
||
LIMIT 1
|
||
) tm ON true
|
||
LEFT JOIN ngcloud_ru.metadata m ON tm.metadata_id = m.id
|
||
WHERE c.timestamp >= <cfqueryparam cfsqltype="cf_sql_timestamp" value=#dateAdd("d",-1,Now())#/>
|
||
GROUP BY
|
||
m.clientid, v.name, p.name, sp.name, t.name,
|
||
m.status, m.end_test_date, tm.ts
|
||
</cfquery>
|
||
|
||
<cfquery name="qResponsible"><!--- cachedWithin=#createTimeSpan(0, 0, 25, 0)# ---><!--- имя колонки __id мы не принимаем почему-то --->
|
||
SELECT upper(k.id_klienta) as wz, k.__id as id, c.__name as responsible, c.email as responsible_email, c.__status_status as responsible_status
|
||
from elma.companies k
|
||
LEFT OUTER JOIN elma.comp_users_ext c on (k.responsible=c.__id)
|
||
</cfquery>
|
||
|
||
<cfquery name="qLastDeal">
|
||
WITH
|
||
status(status, status_id, probability_pc)
|
||
AS (VALUES
|
||
('Закрыта неуспешно',7,0),('Закрыта успешно',6,100),('Договор подписан',5,100),('Договор на подписании',15,90),('Договор на согласовании',12,70),
|
||
('Тестирование',8,50),('Отправлено ТКП',10,20),('Внутреннее согласование',37,10),('Проработка решения',4,10),('Сбор потребностей',1,0)
|
||
)
|
||
select z.id_Klienta as wz, z.__name as company,
|
||
d.__id as deal_uid, d.__status_status as status_id, s.status, d.__index as deal_index, d.__name as deal --, d.kommentarii_k_sdelke, d."__statusComment"
|
||
from elma.companies z
|
||
join (select
|
||
__id, _companies, __status_status, __index, __name, kommentarii_k_sdelke, "__statusComment", "__deletedAt", is_corrective_transaction
|
||
,ROW_NUMBER() OVER (PARTITION BY _companies ORDER BY __index desc) rn
|
||
from elma.deals) d on (z.__id=d._companies AND d.rn=1)
|
||
join status s on (d.__status_status=s.status_id)
|
||
|
||
where z."__deletedAt" IS NULL AND d."__deletedAt" IS NULL
|
||
AND NOT coalesce(d.is_corrective_transaction, false)
|
||
AND NOT d.__status_status=7
|
||
order by z.id_Klienta, d.__index DESC
|
||
</cfquery>
|
||
|
||
<!--- patch date --->
|
||
<cfloop query="qStorage">
|
||
<cftry>
|
||
<cfif len(qStorage.end_test_date)>
|
||
<cfset qStorage.dt_end_of_test = LSParseDateTime(qStorage.end_test_date,"ru","dd.MM.yyyy")/>
|
||
</cfif>
|
||
<cfcatch type="ANY">
|
||
<!--- <cfdump var=#cfcatch.message#> --->
|
||
<!--- <cfset qStorage.dt_end_of_test = javaCast("null", 0)/> --->
|
||
</cfcatch>
|
||
</cftry>
|
||
</cfloop>
|
||
|
||
<cfset db = new MemoryDbHelper()/>
|
||
<cfset db.register("qStorage", qStorage, "inmem")/>
|
||
<cfset db.register("qResponsible", qResponsible, "inmem")/><!--- имя колонки __id мы не принимаем почему-то --->
|
||
<cfset db.register("qLastDeal", qLastDeal, "inmem")/><!--- имя колонки __id мы не принимаем почему-то --->
|
||
|
||
<cfquery name="qRead" datasource="inmem">
|
||
select
|
||
<d:field_set titleMapOut="titleMap" lengthOut="fieldCount">
|
||
<d:field title="Тенант">s.tenant</d:field>
|
||
<d:field title="WZ">s.wz</d:field>
|
||
<d:field title="status">s.status</d:field>
|
||
<d:field title="End of Test">s.end_test_date</d:field>
|
||
<d:field title="End of Test">s.dt_end_of_test</d:field>
|
||
<d:field title="VDC">s.vdc</d:field>
|
||
<d:field title="Provider VDC">s.provider_vdc</d:field>
|
||
<d:field title="code">s.code</d:field>
|
||
<d:field title="Storage Profile">s.storage_profile</d:field>
|
||
<d:field title="Limit GB">s.limit_gb</d:field>
|
||
<d:field title="Allocation GB">s.alloc_gb</d:field>
|
||
<d:field title="Timestamp">s.ts</d:field>
|
||
<d:field title="Ответственный за к/а">r.responsible</d:field>
|
||
<d:field title="Email отв-го">r.responsible_email</d:field>
|
||
<d:field title="Статус отв-го">r.responsible_status</d:field>
|
||
|
||
<d:field title="deal_uid">d.deal_uid</d:field>
|
||
<d:field title="Номер сделки">d.deal_index</d:field>
|
||
<d:field title="Сделка">d.deal</d:field>
|
||
<d:field title="Статус сделки">d.status_id</d:field>
|
||
<d:field title="Статус сделки">d.status as deal_status</d:field>
|
||
</d:field_set>
|
||
from qStorage s
|
||
LEFT OUTER JOIN qResponsible r ON (s.wz=r.wz)
|
||
LEFT OUTER JOIN qLastDeal d ON (s.wz=d.wz)
|
||
ORDER BY
|
||
<m:order_build sortArray=#pageInfo.settings.sort.sortArray# fieldCount=#fieldCount#/>
|
||
</cfquery>
|
||
|
||
|
||
|
||
<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>
|
||
|
||
|
||
|
||
<layout:grid_summary
|
||
recordCount=#qStorage.recordCount#
|
||
recordsPerPage=0
|
||
footerOut="gridFooter"
|
||
excelLink="Yes"
|
||
jsonLink="Yes"
|
||
/>
|
||
|
||
|
||
<table class="worktable">
|
||
<layout:grid_head titleMap=#titleMap# sortArray=#pageInfo.settings.sort.sortArray#>
|
||
<th><layout:column_head name="wz"/></th>
|
||
<th><layout:column_head name="tenant"/></th>
|
||
<th><layout:column_head name="status"/></th>
|
||
<!--- <th><layout:column_head name="end_test_date"/></th> --->
|
||
<th><layout:column_head name="dt_end_of_test"/></th>
|
||
|
||
<th><layout:column_head name="deal"/></th>
|
||
<th><layout:column_head name="deal_status"/></th>
|
||
|
||
<th><layout:column_head name="vdc"/></th>
|
||
<th><layout:column_head name="provider_vdc"/></th>
|
||
<th><layout:column_head name="code"/></th>
|
||
<th><layout:column_head name="storage_profile"/></th>
|
||
<th><layout:column_head name="limit_gb"/></th>
|
||
<th><layout:column_head name="alloc_gb"/></th>
|
||
<th><layout:column_head name="ts"/></th>
|
||
<th><layout:column_head name="responsible"/></th>
|
||
<th><layout:column_head name="responsible_email"/></th>
|
||
<th><layout:column_head name="responsible_status"/></th>
|
||
</layout:grid_head>
|
||
<cfoutput query="qRead">
|
||
<tr>
|
||
<!--- <td><a href="contragent.cfm?__id=#company_uid#&#tr.fwx#">#WZ#</a><a href="contragent_rpt.cfm?__id=#company_uid#&#tr.fwx#" style="float:right"><img src="img/view.gif" title="сводка по контрагенту"/></a></td> --->
|
||
<td class="c"><a href="contragent.cfm?id_klienta=#UCASE(wz)#&#tr.fwx#">#wz#</td>
|
||
<td>#tenant#</td>
|
||
<td<cfif NOT (status EQ "prod" OR status EQ "internal-test")> class="b"</cfif>>#status#</td>
|
||
<!--- <td>#end_test_date#</td> --->
|
||
<td class="c<cfif NOT (status EQ "prod" OR status EQ "internal-test") AND isDate(dt_end_of_test) AND dt_end_of_test LE Now()> b err</cfif>">#dateFormat(dt_end_of_test,"YYYY-MM-DD")#</td>
|
||
|
||
<td><a href="deal.cfm?__id=#deal_uid#&#tr.fwx#">#deal#</a></td>
|
||
<td>#deal_status#</td>
|
||
|
||
<td>#VDC#</td>
|
||
<td>#provider_vdc#</td>
|
||
<td>#code#</td>
|
||
<td>#storage_profile#</td>
|
||
<td class="r<cfif NOT (status EQ "prod" OR status EQ "internal-test") AND isDate(dt_end_of_test) AND dt_end_of_test LE Now()> b err</cfif>">#limit_gb#</td>
|
||
<td class="r<cfif NOT (status EQ "prod" OR status EQ "internal-test") AND isDate(dt_end_of_test) AND dt_end_of_test LE Now()> b err</cfif>">#alloc_gb#</td>
|
||
<td>#dateFormaT(ts,"YYYY-MM-DD")# #timeFormaT(ts,"HH-MM")#</td>
|
||
<td<cfif responsible_status NEQ 2> class="err"</cfif>>#responsible#</td>
|
||
<td<cfif responsible_status NEQ 2> class="err"</cfif>>#responsible_email#</td>
|
||
<td<cfif responsible_status NEQ 2> class="err"</cfif>>#responsible_status#</td>
|
||
</tr>
|
||
</cfoutput>
|
||
|
||
</table>
|
||
|
||
|
||
<layout:page section="footer"/>
|
||
|