164 lines
7.2 KiB
Plaintext
164 lines
7.2 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="vm_gpu_rpt" accessObject="" pageInfoOut="pageInfo" trackOut="tr"/>
|
||
|
||
|
||
<cfparam name="dt_finish" type="date" default=#dateAdd('d',-1,createDateTime(year(Now()),month(Now()),1,0,0,0))#/>
|
||
<cfparam name="dt_start" type="date" default=#dateAdd('m',-1,createDateTime(year(Now()),month(Now()),1,0,0,0))#/>
|
||
<cfset hours=dateDiff('h', dt_start, dateAdd('d', 1, dt_finish))/>
|
||
<!--- <cfparam name="only_articles_in_spec" type="boolean" default=false/> --->
|
||
<cfparam name="wz" default="WZ01516"/>
|
||
<cfparam name="code" default=""/>
|
||
|
||
|
||
|
||
|
||
|
||
<cfquery name="qRead" datasource="billing-s3">
|
||
select
|
||
<d:field_set titleMapOut="titleMap" lengthOut="fieldCount">
|
||
<d:field title="Час" cfSqlType="CF_SQL_TIMESTAMP">DATE_TRUNC('hour',p.ts) as ts_hour</d:field>
|
||
<d:field title="Уникальных ВМ" cfSqlType="CF_SQL_INTEGER">count(distinct p.obj_uuid) as unique_vm_cnt</d:field> <!--- -- все ВМ, существовавшие в данный час --->
|
||
<d:field title="ВМ вкл." cfSqlType="CF_SQL_INTEGER">count(distinct p.obj_uuid) FILTER (WHERE p.value=1) as unique_vm_cnt_on</d:field> <!--- -- все ВМ, включавшиеся за данный час --->
|
||
<d:field title="ВМ-часов" cfSqlType="CF_SQL_NUMERIC">round((sum(p.value)/12.)::numeric,1) as vm_h</d:field> <!--- -- 5 minute sampling, so /12 --->
|
||
<d:field title="Стоимость без диска" cfSqlType="CF_SQL_NUMERIC">round((sum(p.value)/12.*24.19)::numeric,2) as cost_wo_disk</d:field>
|
||
<d:field title="Стоимость диска" cfSqlType="CF_SQL_NUMERIC">round((count(distinct obj_id)*8.00)::numeric,2) as disk_cost</d:field> <!--- -- считаем все ВМ, существовавшие в данный час --->
|
||
<d:field title="Круглосуточных ВМ" cfSqlType="CF_SQL_NUMERIC">round((sum(p.value)/12.)::numeric, 1) as avg_roundclock_vm</d:field>
|
||
</d:field_set>
|
||
from gpu.vm
|
||
JOIN gpu.vm_power_state p on (vm.obj_uuid=p.obj_uuid)
|
||
where 1=1
|
||
/*watch time zone, accounting by MSK*/
|
||
AND p.ts >= <cfqueryparam cfsqltype="cf_sql_timestamp" value=#dt_start#/>
|
||
AND p.ts < <cfqueryparam cfsqltype="cf_sql_timestamp" value=#dateAdd("d",1,dt_finish)#/>
|
||
AND vm.client_id='WZ01516'
|
||
GROUP BY DATE_TRUNC('hour',p.ts)
|
||
ORDER BY 1;
|
||
</cfquery>
|
||
|
||
|
||
<cfif isDefined("DEBUG")>
|
||
<cfdump var=#qRead#/>
|
||
<cfif !isDefined("output_xls") AND !isDefined("output_json")><cfflush/></cfif>
|
||
</cfif>
|
||
|
||
<cfquery name="qGpuAge" datasource="billing-s3"><!--- cachedWithin=#createTimeSpan(0, 0, 25, 0)# --->
|
||
select max(ts) as dt_load from gpu.vm_power_state
|
||
</cfquery>
|
||
|
||
<cfoutput>qGpuAge:#getTickCount()-request.startTickCount#</cfoutput><cfif !isDefined("output_xls") AND !isDefined("output_json")><cfflush/></cfif>
|
||
|
||
|
||
<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>Отчет по включенным ВМ с GPU</b></cfoutput>
|
||
</layout:attribute>
|
||
<layout:attribute name="controls">
|
||
<!---skip filter link, filter is not implemented--->
|
||
<!---<layout:language_switch/>--->
|
||
</layout:attribute>
|
||
</layout:page>
|
||
|
||
|
||
<cfoutput>
|
||
<form method="post" action="">
|
||
Период с <input type="text" name="dt_start" value="#dateFormat(dt_start,'YYYY-MM-DD')#"/>
|
||
по <input type="text" name="dt_finish" value="#dateFormat(dt_finish,'YYYY-MM-DD')#"/>
|
||
|
||
<!--- WZ <input type="text" name="wz" value="#wz#"/>по вхождению, с регистром
|
||
Код <input type="text" name="code" value="#code#"/>по вхождению --->
|
||
<input type="submit" style="cursor:pointer;"/>
|
||
<input type="submit" name="DEBUG" value="DEBUG" style="cursor:pointer;"/><!--- #dateFormat( input, "yyyy-mm-dd" )#T#timeFormat( input, "HH:mm:ss.lXX" )# --->
|
||
</form>
|
||
Часов в периоде: <b>#hours#</b> Строк в отчете: <b>#qRead.recordCount#</b><br/>
|
||
Актуальность данных:
|
||
GPU-VM Power State: <b<cfif datediff("h",qGpuAge.dt_load, Now()) GT 24> class="err"</cfif>>#dateFormat(qGpuAge.dt_load,'YYYY-MM-DD')# #timeFormat(qGpuAge.dt_load,'HH:mm:ss.lXX')#</b>
|
||
</b>
|
||
|
||
<br/>
|
||
<a href="#request.thisPage#?output_xls" title="экспорт в Excel" style="margin-left:.5em; height:100%;" target="_blank"><img src="img/xls.gif" style="vertical-align:text-bottom;"/></a>
|
||
|
||
<a href="#request.thisPage#?output_json" title="экспорт в json" style="margin-left:.5em; height:100%;" target="_blank"><img src="img/json.svg" style="vertical-align:text-bottom;" width="13" height="13"/></a>
|
||
</cfoutput>
|
||
<table class="worktable">
|
||
<thead>
|
||
<th width="10%">Час</th>
|
||
<th width="10%">Уникальных ВМ</th>
|
||
<th width="10%">ВМ вкл</th>
|
||
<th width="10%">ВМ-часов</th>
|
||
<th width="10%">Стоимость без диска</th>
|
||
<th width="10%">Стоимость диска</th>
|
||
<th width="10%">Круглосуточных ВМ</th>
|
||
</thead>
|
||
|
||
<cfoutput query="qRead">
|
||
<tr>
|
||
<td class="c">#dateFormat(ts_hour,"YYYY-MM-DD")# #timeFormat(ts_hour,"HH:MM")#</td>
|
||
<td class="r">#unique_vm_cnt#</td>
|
||
<td class="r">#unique_vm_cnt_on#</td>
|
||
<td class="r">#vm_h#</td>
|
||
<td class="r">#cost_wo_disk#</td>
|
||
<td class="r">#disk_cost#</td>
|
||
<td class="r">#avg_roundclock_vm#</td>
|
||
</tr>
|
||
</cfoutput>
|
||
</table>
|
||
<!--- <cfdump var=#qCharge#/> --->
|
||
<!---
|
||
<cfdump var=#this.datasources[request.ds].connectionString#/>
|
||
<cfdump var=#this.datasources["billing-vc"].connectionString#/> --->
|
||
<cfdump var=#this.datasources["billing-s3"].connectionString#/>
|
||
|
||
<layout:page section="footer"/>
|
||
|
||
<!--- select * from s3billing.billing_per_user_fix_pl('2025-05-01 00:00:00','2025-06-01 00:00:00','1395'); --->
|
||
|
||
<!--- select t.id as tenant_id,
|
||
from ngcloud_ru.tenant t
|
||
left outer join
|
||
(select tenant_id, ts, metadata_id,
|
||
ROW_NUMBER() OVER (PARTITION BY tenant_id ORDER BY ts desc, metadata_id desc)
|
||
as rn
|
||
from ngcloud_ru.tenant_metadata) tm
|
||
on (t.id = tm.tenant_id AND tm.rn = 1)
|
||
left outer join ngcloud_ru.metadata m on (tm.metadata_id=m.id) --->
|
||
<cfscript>
|
||
string function gaussianRoundSafe(any num, numeric precision = 0, string default="") {
|
||
return (isNumeric(arguments.num)) ? gaussianRound(arguments.num, arguments.precision) : arguments.default;
|
||
}
|
||
|
||
function gaussianRound(numeric number, numeric precision = 0) {
|
||
var factor = 10 ^ precision;
|
||
var scaledNumber = number * factor;
|
||
var absScaled = abs(scaledNumber);
|
||
var sign = sgn(scaledNumber);
|
||
var floored = int(absScaled);
|
||
|
||
// If it's not ending in exactly .5, use standard rounding or precision scaling
|
||
if (absScaled - floored != 0.5) {
|
||
return round(number * factor) / factor;
|
||
}
|
||
|
||
// If it is exactly .5, round towards the nearest even number
|
||
if (floored % 2 != 0) {
|
||
// Nearest even is UP
|
||
return (floored + 1) * sign / factor;
|
||
} else {
|
||
// Nearest even is DOWN
|
||
return floored * sign / factor;
|
||
}
|
||
}
|
||
</cfscript> |