256 lines
11 KiB
Plaintext
256 lines
11 KiB
Plaintext
<cfsilent>
|
||
<cfimport prefix="m" taglib="lib"/>
|
||
|
||
<cfimport prefix="d" taglib="lib/data"/>
|
||
<cfimport prefix="layout" taglib="layout"/>
|
||
</cfsilent>
|
||
|
||
|
||
|
||
<!---<cftry>--->
|
||
<!--- "dealId" : "018c687b-3700-5503-ae2a-4dfa85ea2cca",
|
||
"dealName" : "№566 1С и терм.сервер",
|
||
"contragentInn" : "7751265941",
|
||
"contragentId" : "b6c95829-5397-497e-b80f-2b86495a2a54",
|
||
"dealType" : "New sale",
|
||
"arr" : [ {
|
||
"price" : 50000.004,
|
||
"sum" : 50000.004,
|
||
"area" : "Размещение в ЦОД",
|
||
"abstractService" : "Аренда стойко-места",
|
||
"staticId" : "20231214T193205277",
|
||
"code" : "colo.rack.0-f",
|
||
"modifier" : "",
|
||
"isActual" : true,
|
||
"type" : "3",
|
||
"modifierCode" : "none",
|
||
"abstractServiceCode" : "Аренда стойко-места",
|
||
"dateStartProvision" : "",
|
||
"dateEndProvision" : "",
|
||
"componentsName" : "Аренда стойко-места",
|
||
"unit" : "шт.",
|
||
"quantity" : 1,
|
||
"priceWoDiscount" : 50000.004,
|
||
"discount" : 0,
|
||
"change" : "new" --->
|
||
|
||
<cfquery name="qRead" datasource="#request.DS#">
|
||
select
|
||
<d:field_set titleMapOut="qReadTitleMap" lengthOut="qReadFieldCount">
|
||
<d:field title="service_price_id" cfSqlType="CF_SQL_VARCHAR">prc.service_price_id</d:field>
|
||
<d:field title="service_param_price_id" cfSqlType="CF_SQL_VARCHAR">prc.service_param_price_id</d:field>
|
||
<d:field title="Модель цены">prm.pricing_model_id</d:field>
|
||
<d:field title="Модель цены">prm.pricing_model</d:field>
|
||
<d:field title="Модель цены">prm.pricing_model_short</d:field>
|
||
<d:field title="Модель цены">prm.pricing_model_code</d:field>
|
||
<d:field title="Период цены">prc.pricing_period</d:field>
|
||
<d:field title="Период опроса">prc.rating_period</d:field>
|
||
<d:field>prc.dt_from</d:field>
|
||
<d:field>prc.dt_to</d:field>
|
||
<d:field>s.vat_perc</d:field>
|
||
<d:field>s.vat_free</d:field>
|
||
<d:field>case when s.vat_free then 0. else vat_perc/100. end as vat_rate</d:field>
|
||
<d:field title="price" cfSqlType="CF_SQL_DECIMAL">prc.price</d:field>
|
||
<d:field title="Цена со ск., ₽" cfSqlType="CF_SQL_DECIMAL">case when price*.95 > coalesce(min_price,0) then price*.95 else min_price end as discount_price</d:field>/***/
|
||
<d:field title="Дно" cfSqlType="CF_SQL_DECIMAL">min_price</d:field>
|
||
|
||
<d:field title="abstract_service_id" cfSqlType="CF_SQL_INTEGER">a.abstract_service_id</d:field>
|
||
<d:field title="service_id" cfSqlType="CF_SQL_INTEGER">s.service_id</d:field>
|
||
|
||
<d:field title=#i18("Код группы","Group Code")#>g.area_code</d:field>
|
||
<d:field title="Группа">g.area</d:field>
|
||
<d:field title="Группа">g.area_id</d:field>
|
||
<d:field title="Группа">g.analytic_code</d:field>
|
||
|
||
<d:field title="Статус абстрактной услуги">a.status_id as abstract_service_status_id</d:field>
|
||
<d:field title="Статус абстрактной услуги">u.status as abstract_service_status</d:field>
|
||
<d:field title="Статус услуги">s.status_id as service_status_id</d:field>
|
||
<d:field title="Статус услуги">t.status as service_status</d:field>
|
||
<d:field>prc.status as price_status</d:field>
|
||
|
||
<d:field>a.code</d:field>
|
||
<d:field>m.code as modifier_code</d:field>
|
||
<d:field>p.code as param_code</d:field>
|
||
|
||
<d:field title="Абстрактная услуга">a.abstract_service</d:field>
|
||
|
||
<d:field title="modifier_id" cfSqlType="CF_SQL_INTEGER">m.modifier_id</d:field>
|
||
|
||
<d:field title="Класс модификатора">mc.modifier_class</d:field>
|
||
<d:field title="modifier_class_id">mc.modifier_class_id</d:field>
|
||
<d:field title="Модификатор">m.modifier</d:field>
|
||
<d:field title="Сортировка модификатора">m.sort</d:field>
|
||
|
||
<d:field title="service_param_id" cfSqlType="CF_SQL_INTEGER">sp.service_param_id</d:field>
|
||
<d:field cfSqlType="CF_SQL_INTEGER">sp.min_value</d:field>
|
||
<d:field cfSqlType="CF_SQL_INTEGER">sp.max_value</d:field>
|
||
<d:field cfSqlType="CF_SQL_INTEGER">sp.incr</d:field>
|
||
<d:field title="param_class_id" cfSqlType="CF_SQL_INTEGER">pc.param_class_id</d:field>
|
||
<d:field title="param_class">pc.param_class</d:field>
|
||
<d:field title="Сортировка компонента">ac.sort as param_class_sort</d:field><!--- порядок сортировки в списке компонентов, например CPU-RAM-HDD-GPU --->
|
||
<d:field title="Множественный выбор">ac.is_multiple</d:field>
|
||
<d:field title="Сортировка варианта компонента">p.sort as param_sort</d:field><!--- порядок сортировки в списке вариантов компонентов, например SATA-SAS-SSD --->
|
||
<d:field title="Компонент">p.param</d:field>
|
||
<d:field title="param_id" cfSqlType="CF_SQL_INTEGER">p.param_id</d:field>
|
||
<d:field title="Ед.изм.">case when p.param_id IS NULL then se.measure_short else e.measure_short end as measure_short</d:field>
|
||
<d:field>case when p.param_id IS NULL then s.measure_id else p.measure_id end as measure_id</d:field>
|
||
<d:field>s.commercial_note</d:field>
|
||
</d:field_set>
|
||
from (
|
||
select
|
||
t.service_param_price_id, t.price, t.min_price, t.service_param_id, t.pricing_model_id
|
||
, null as service_price_id, sp.service_id, t.pricing_period, t.rating_period, t.dt_from, t.dt_to, t.status
|
||
from service_param_price t join service_param sp on (t.service_param_id=sp.service_param_id)
|
||
union all
|
||
select
|
||
null, st.price, st.min_price, null, st.pricing_model_id, st.service_price_id, st.service_id, st.pricing_period, st.rating_period, st.dt_from, st.dt_to, st.status
|
||
from service_price st
|
||
) prc /*кажется, union в данном отчете бесполезен - все равно эти данные попадают в разные группировки*/
|
||
left outer join service s on (prc.service_id=s.service_id)
|
||
left outer join service_param sp on (prc.service_param_id=sp.service_param_id)
|
||
left outer join abstract_service a on (s.abstract_service_id=a.abstract_service_id)
|
||
left outer join modifier m on (s.modifier_id=m.modifier_id)
|
||
left outer join modifier_class mc on (m.modifier_class_id=mc.modifier_class_id)
|
||
left outer join abstract_service_param_class ac on (sp.abstract_service_param_class_id=ac.abstract_service_param_class_id)
|
||
left outer join param_class pc on (ac.param_class_id=pc.param_class_id)
|
||
left outer join param p on (sp.param_id=p.param_id)
|
||
left outer join measure e on (p.measure_id=e.measure_id)
|
||
left outer join measure se on (s.measure_id=se.measure_id)
|
||
left outer join area g on (a.area_id=g.area_id)
|
||
left outer join status u on (a.status_id=u.status_id)
|
||
left outer join status t on (s.status_id=t.status_id)
|
||
left outer join pricing_model prm on (prc.pricing_model_id=prm.pricing_model_id)
|
||
where 1=1 <!--- <m:filter_build filter=#pageInfo.settings.filter#/> --->
|
||
<!--- AND a.abstract_service_id=19 --->
|
||
order by g.analytic_code
|
||
, a.abstract_service
|
||
, s.service_id
|
||
, coalesce(m.sort,0)
|
||
, m.modifier_id
|
||
, coalesce(ac.sort,0)
|
||
, coalesce(p.sort,0)
|
||
, p.param_id
|
||
--, prc.pricing_model_id
|
||
</cfquery>
|
||
|
||
<!---
|
||
<cfcatch type="database">
|
||
<m:ls_catch catch=#cfcatch# status=#pageInfo.status#/>
|
||
</cfcatch>
|
||
</cftry>--->
|
||
|
||
|
||
<!---<cfdump var=#qRead#/><cfabort/>--->
|
||
|
||
<cffunction name="isoTimeFormat">
|
||
<cfargument name="dt"/>
|
||
<cfreturn len(dt)?"#dateFormat(dt, "yyyy-mm-dd")#T#timeFormat(dt, "HH:mm:ss")#Z":""/>
|
||
</cffunction>
|
||
|
||
|
||
<cfset temp.out=[]/>
|
||
|
||
<!--- using [] instead of {} for struct literals to obtain linked (ordered) structures: keys order preserved --->
|
||
<cfoutput query="qRead" group="area_id">
|
||
|
||
<cfset temp.area=["areaId"="#area_id#"
|
||
,"area"="#area#"
|
||
,"areaCode"="#area_code#"
|
||
,"analyticCode"="#analytic_code#"
|
||
,"services"=[]
|
||
]/>
|
||
<cfset arrayAppend(temp.out,temp.area)/>
|
||
|
||
<cfoutput group="service_id">
|
||
<cfset temp.service=["serviceId"="#service_id#"
|
||
,"abstractServiceId"="#abstract_service_id#"
|
||
,"abstractServiceCode"="#request.skuCode(area_code,code)#"
|
||
,"serviceCode"="#request.skuCode(area_code,code,modifier_code)#"
|
||
,"service"="#abstract_service##(len(modifier))?': ':''##modifier#"
|
||
,"modifierClassId"="#modifier_class_id#"
|
||
,"modifierClass"="#modifier_class#"
|
||
,"modifierId"="#modifier_id#"
|
||
,"modifier"="#modifier#"
|
||
,"modifierCode"="#modifier_code#"
|
||
,"measureId"="#measure_id#"
|
||
,"measureShort"="#measure_short#"
|
||
,"abstractServiceStatusId"="#abstract_service_status_id#"
|
||
,"abstractServiceStatus"="#abstract_service_status#"
|
||
,"abstractService"="#abstract_service#"
|
||
,"serviceStatusId"="#service_status_id#"
|
||
,"serviceStatus"="#service_status#"
|
||
,"sort"="#sort#"
|
||
,"vatPerc"="#vat_perc#"
|
||
,"vatFree"="#vat_free#"
|
||
,"vatRate"="#vat_rate#"
|
||
,"prices"=[]
|
||
,"components"=[]
|
||
]/>
|
||
<cfset arrayAppend(temp.area.services,temp.service)/>
|
||
|
||
<cfoutput group="service_price_id">
|
||
<cfif service_price_id GT 0>
|
||
<cfset temp.price=["servicePriceId"="#service_price_id#"
|
||
,"pricingModel"="#pricing_model#"
|
||
,"pricingModelId"="#pricing_model_id#"
|
||
,"priceGPLwVAT"="#isNumeric(price)?price*(1+vat_rate):''#"
|
||
,"dtFrom"=#isoTimeFormat(dt_from)#
|
||
,"dtTo"="#isoTimeFormat(dt_to)#"
|
||
,"status"="#price_status#"
|
||
,"ratingPeriod"="#rating_period#"
|
||
,"pricingPeriod"="#pricing_period#"
|
||
]/>
|
||
<cfset arrayAppend(temp.service.prices,temp.price)/>
|
||
</cfif>
|
||
</cfoutput>
|
||
|
||
<cfoutput group="service_param_id">
|
||
<cfif service_param_id GT 0>
|
||
<cfset temp.param=["serviceParamId"="#service_param_id#"
|
||
,"component"="#param_class##len(param)?': ':''##param#"
|
||
,"componentClassId"="#param_class_id#"
|
||
,"componentClass"="#param_class#"
|
||
,"paramId"="#param_id#"
|
||
,"param"="#param#"
|
||
,"componentCode"="#request.skuCode(area_code,code,modifier_code,param_code)#"
|
||
,"paramCode"="#param_code#"
|
||
,"measureId"="#measure_id#"
|
||
,"measureShort"="#measure_short#"
|
||
,"minValue"="#min_value#"
|
||
,"maxValue"="#max_value#"
|
||
,"incr"="#incr#"
|
||
,"paramClassSort"="#param_class_sort#"
|
||
,"paramSort"="#param_sort#"
|
||
,"sku"="#abstract_service# #modifier##(len(param_class))? ( ' (' & ((len(param))? param : param_class) & ')' ) : ''#"
|
||
,"isMultiple"="#is_multiple#"
|
||
,"prices"=[]
|
||
]/>
|
||
<cfset arrayAppend(temp.service.components,temp.param)/>
|
||
|
||
<cfoutput group="service_param_price_id">
|
||
<cfif service_param_price_id GT 0>
|
||
<cfset temp.paramPrice=["serviceParamPriceId"="#service_param_price_id#"
|
||
,"pricingModel"="#pricing_model#"
|
||
,"pricingModelId"="#pricing_model_id#"
|
||
,"priceGPLwVAT"="#isNumeric(price)?price*(1+vat_rate):''#"
|
||
,"dtFrom"=#isoTimeFormat(dt_from)#
|
||
,"dtTo"=#isoTimeFormat(dt_to)#
|
||
,"status"="#price_status#"
|
||
,"ratingPeriod"="#rating_period#"
|
||
,"pricingPeriod"="#pricing_period#"
|
||
]/>
|
||
<cfset arrayAppend(temp.param.prices,temp.paramPrice)/>
|
||
</cfif>
|
||
</cfoutput>
|
||
</cfif>
|
||
</cfoutput>
|
||
|
||
</cfoutput>
|
||
|
||
</cfoutput>
|
||
|
||
<!--- <cfdump var=#local.out#/> --->
|
||
<cfcontent
|
||
type="application/json"
|
||
/>
|
||
<cfoutput>#serializeJSON(temp.out)#</cfoutput> |