"monitoring_url"={prefix="inr", type="string"}, "instance_state_uid"={prefix="inr", type="guid"}, "is_deleted"={prefix="inr", type="string"}, "is_suspended"={prefix="inr", type="string"}, "job_is_pending"={prefix="inr", type="string"}, "resource_realm"={prefix="inr", type="string"}, "resource_realm_type_id"={prefix="inr", type="integer"}, "operation_is_in_progress"={prefix="inr", type="boolean"}, "operation_is_pending"={prefix="inr", type="boolean"}, "is_test"={prefix="inr", type="boolean"}, "is_auxiliary"={prefix="inr", type="boolean"}, "explained_status"={prefix="inr", type="string"}, "dt_state"={prefix="inr", type="date"}, "version"={prefix="inr", type="integer"} } /> to_char(e.dt_created, 'YYYY-MM-DD"T"HH24:MI:SS.FF3TZHTZM') as instance_config_dt_created to_char(e.dt_updated, 'YYYY-MM-DD"T"HH24:MI:SS.FF3TZHTZM') as instance_config_dt_updated e.instance_uid::text as instance_uid e.display_name e.service_id e.specification_item_id e.is_auxiliary d.contract_id i.specification_id s.specification i.quantity i.price v.svc v.code v.resource_realm_type_id e.updater_id u.login as updater_login u.shortname as updater_shortname io.operation as last_operation io.instance_operation_uid::text as last_operation_uid io.submit_result as submit_result coalesce((io.dt_start IS NOT NULL AND io.dt_finish IS NULL AND io.submit_result='201'),false) as operation_is_in_progress coalesce((io.dt_start IS NULL AND io.submit_result='201'),false) as operation_is_pending to_char(io.dt_start, 'YYYY-MM-DD"T"HH24:MI:SS.FF3TZHTZM') as operation_dt_start to_char(io.dt_finish, 'YYYY-MM-DD"T"HH24:MI:SS.FF3TZHTZM') as operation_dt_finish round(extract('epoch' from coalesce(io.dt_finish,CURRENT_TIMESTAMP)- io.dt_start),1) as duration case when io.operation IN ('delete', 'suspend') AND io.is_successful then 0 /*null*/ when io.is_successful then extract('epoch' from CURRENT_TIMESTAMP - coalesce(io.dt_finish,CURRENT_TIMESTAMP)) else 0 end as uptime to_char(io.dt_finish, 'YYYY-MM-DD"T"HH24:MI:SS.FF3TZHTZM') as dt_deploy st.instance_state_uid::text as instance_state_uid st.is_test as is_test coalesce(st.instance_data->>'isDeleted','false') as is_deleted st.instance_data->>'isSuspended' as is_suspended st.instance_data->'job'->>'pending' as job_is_pending st.instance_data->'out'->'monitoring'->>'resourceMetrics' as monitoring_url st.instance_data->'params'->>'resourceRealm' as resource_realm to_char(st.dt_state, 'YYYY-MM-DD"T"HH24:MI:SS.FF3TZHTZM') as dt_state (case when st.instance_uid IS NULL then 'not created' when job_is_pending = 'true' then 'pending' when st.is_deleted = 'true' then 'deleted' when st.is_suspended = 'true' then 'suspended' when coalesce(st.is_deleted,'false') = 'false' AND coalesce(st.is_suspended, 'false') = 'false' then 'running' when io.instance_uid IS NULL then 'not configured' else null end ) as explained_status st.version select count(*) as cnt from ( select '' as placeholder #preserveSingleQuotes(definedQueryFields)# from instance e join specification_item i on (e.specification_item_id=i.specification_item_id) join specification s on (i.specification_id=s.specification_id) join contract d on (s.contract_id=d.contract_id) join contragent k on (d.contragent_id=k.contragent_id) join svc v on (e.service_id=v.svc_id) left outer join usr u on (e.updater_id=u.usr_id) left outer join (select ist.instance_state_uid, ist.instance_uid, ist.version, ist.dt_state, ist.is_test ,(ist.instance_data->>'isSuspended') as is_suspended ,(ist.instance_data->>'isDeleted') as is_deleted ,(ist.instance_data->'job'->>'pending') as job_is_pending ,(ist.instance_data->'out'->'monitoring'->>'resourceMetrics') as monitoring_url ,ist.instance_data from instance_state ist join (select instance_uid, max(version) as version from instance_state group by 1) lastv on (ist.instance_uid=lastv.instance_uid AND ist.version=lastv.version) ) st on (e.instance_uid=st.instance_uid) left outer join (select o.instance_operation_uid, o.instance_uid, o.operation, o.dt_submit, o.submit_result, o.dt_start, o.dt_finish, o.is_successful from instance_operation o join (select instance_uid, max(dt_submit) as dt_submit from instance_operation group by 1 ) lastv on (o.instance_uid=lastv.instance_uid AND o.dt_submit=lastv.dt_submit) ) io on (e.instance_uid=io.instance_uid) where s.specification_id= AND ( (e.display_name) ilike () OR ((coalesce(v.svc,'') ||' '|| coalesce(v.svc_short,'') ||' '|| coalesce(v.synonyms,''))) ilike () ) ) inr where 1=1 select #local.fieldsToOutput#* from ( select #preserveSingleQuotes(definedQueryFields)# from instance e join specification_item i on (e.specification_item_id=i.specification_item_id) join specification s on (i.specification_id=s.specification_id) join contract d on (s.contract_id=d.contract_id) join contragent k on (d.contragent_id=k.contragent_id) join svc v on (e.service_id=v.svc_id) left outer join usr u on (e.updater_id=u.usr_id) left outer join (select ist.instance_state_uid, ist.instance_uid, ist.version, ist.dt_state, ist.is_test ,(ist.instance_data->>'isSuspended') as is_suspended ,(ist.instance_data->>'isDeleted') as is_deleted ,(ist.instance_data->'job'->>'pending') as job_is_pending ,ist.instance_data from instance_state ist join (select instance_uid, max(version) as version from instance_state group by 1) lastv on (ist.instance_uid=lastv.instance_uid AND ist.version=lastv.version) ) st on (e.instance_uid=st.instance_uid) left outer join (select o.instance_operation_uid, o.instance_uid, o.operation, o.dt_submit, o.submit_result, o.dt_start, o.dt_finish, o.is_successful from instance_operation o join (select instance_uid, max(dt_submit) as dt_submit from instance_operation group by 1) lastv on (o.instance_uid=lastv.instance_uid AND o.dt_submit=lastv.dt_submit) ) io on (e.instance_uid=io.instance_uid) where s.specification_id= AND ( (e.display_name) ilike () OR ((coalesce(v.svc,'') ||' '|| coalesce(v.svc_short,'') ||' '|| coalesce(v.synonyms,''))) ilike () ) ) inr where 1=1 order by limit #arguments.pageSize# offset #arguments.pageSize*(arguments.page-1)# select count(*) as cnt from instance e join specification_item i on (e.specification_item_id=i.specification_item_id) join specification s on (i.specification_id=s.specification_id) join contract d on (s.contract_id=d.contract_id) join contragent k on (d.contragent_id=k.contragent_id) join svc v on (e.service_id=v.svc_id) left outer join (select ist.instance_state_uid, ist.instance_uid, ist.version, ist.dt_state, ist.is_test ,(ist.instance_data->>'isSuspended') as is_suspended ,(ist.instance_data->>'isDeleted') as is_deleted ,ist.instance_data from instance_state ist join (select instance_uid, max(version) as version from instance_state group by 1) lastv on (ist.instance_uid=lastv.instance_uid AND ist.version=lastv.version) ) st on (st.instance_uid=e.instance_uid) left outer join (select o.instance_operation_uid, o.instance_uid, o.operation, o.dt_submit, o.submit_result, o.dt_start, o.dt_finish, o.is_successful from instance_operation o join (select instance_uid, max(dt_submit) as dt_submit from instance_operation group by 1) lastv on (o.instance_uid=lastv.instance_uid AND o.dt_submit=lastv.dt_submit) ) io on (e.instance_uid=io.instance_uid) where s.specification_id= > insert into specification_item (specification_id, specification_item, svc_id, quantity, price, dt_created, creator_id, dt_updated, updater_id) values ( , , , , , , , , ) returning specification_item_id; insert into instance (instance_uid, display_name, service_id, specification_item_id, descr, dt_created, creator_id, dt_updated, updater_id) values ( , , , , , , , , ); select count(*) as cnt from instance e join specification_item i on (e.specification_item_id=i.specification_item_id) join specification s on (i.specification_id=s.specification_id) join contract d on (s.contract_id=d.contract_id) join contragent k on (d.contragent_id=k.contragent_id) join usr u on (k.contragent_id=u.contragent_id) where u.usr_id= AND lower(e.display_name)=lower() AND e.instance_uid <> AND (select cast(st.instance_data->>'isDeleted' as boolean) from instance_state st where st.instance_uid=e.instance_uid order by version desc limit 1) IS NOT TRUE /*postgre specific. should take into account instances without state, ignoring explicitly deleted only*/ select coalesce(svc_short,svc) as svc_short from svc where svc_id= select display_name from instance e join specification_item i on (e.specification_item_id=i.specification_item_id) join specification s on (i.specification_id=s.specification_id) join contract d on (s.contract_id=d.contract_id) join contragent k on (d.contragent_id=k.contragent_id) join usr u on (k.contragent_id=u.contragent_id) where u.usr_id= AND lower(e.display_name) like lower() AND service_id=