with
_value_attr as NOT MATERIALIZED(
select
av.Object_id as id,
av.Attrib_ID as IdTypeAttr,
coalesce(sv.value,'')||coalesce(sv.value1,'') as sValue,
nv.value as nValue,
dv.value as dValue
from
LSDBO.attrib_value as av left join
LSDBO.value_string as sv on sv.id=av.Value_ID left join
LSDBO.value_datetime as dv on dv.id=av.Value_ID left join
LSDBO.value_numeric as nv on nv.id=av.Value_id
where
av.Attrib_ID in(
3000000000105,100004086600000,3000000000106,100037488300000,3000000000122,
100000019000005,100000648000000,3000000000101,100005428000000,
100004087100000, -33,100004081200000
)
),
_obj as NOT MATERIALIZED(
Select
rw.id,
rw.cd,
tw.mnemo,
rw.description
From
LSDBO.object_reference AS rw left join LSDBO.object_type AS tw ON rw.type_id = tw.id
Where
tw.mnemo in ('Req','tnd','PSft', 'PHrd', 'PExp', 'PSrv', 'pPls','Del')
-- order by rw.id
),
_del as NOT MATERIALIZED(
Select --выборка по заказам по дате закрытия
vv1.sValue as d_Account,
vv2.dValue AS d_dAccount,
rw.id AS d_ID,
vv0.sValue AS d_Status,
vv4.sValue AS d_Currency,
--(select LSDBO.Ric_Get_ShortFIO(vv5.sValue)) as Del_Manager,
vv5.sValue as d_Manager,
Case abs(vv7.nValue) when 10 then 'Клиент' when 20 then 'Дилер' when 30 then 'Клиент дилера' when 40 then 'Частное лицо' else '...' end AS d_Channel--,
-- vv15.nValue as dRequestId
From
_obj as rw left join
_value_attr AS vv0 ON vv0.id= rw.id and vv0.idTypeAttr= 3000000000105 left join
_value_attr AS vv1 ON vv1.id= rw.id and vv1.idTypeAttr= 100004086600000 left join
_value_attr AS vv2 ON vv2.id= rw.id and vv2.idTypeAttr= 3000000000106 left join
_value_attr AS vv4 ON vv4.id= rw.id and vv4.idTypeAttr= 100037488300000 left join
_value_attr AS vv5 ON vv5.id= rw.id and vv5.idTypeAttr= 3000000000122 left join
_value_attr AS vv7 ON vv7.id= rw.id and vv7.idTypeAttr= 100000019000005
Where
rw.mnemo='Del'
and
vv0.sValue in ('Исполнена','Оплата | Отгрузка') --not in ("Аннулирована", "Заказ", "Предложение")
and vv2.dValue between p_dbegin and p_dend
-- order by rw.id
),
_req as NOT MATERIALIZED(
Select
rw.id as req_id,
vv2.svalue AS R_Author,
vv1.nvalue as req_del_id
From
_obj as rw left join
_value_attr AS vv2 ON vv2.id= rw.id and vv2.idTypeAttr= -33 left join
_value_attr AS vv1 ON vv1.id= rw.id and vv1.idTypeAttr=100004081200000
Where
rw.mnemo in ('Req','tnd')
-- and vv1.nvalue>0
and vv1.nvalue in (select d_ID from _del)
--order by vv1.nvalue
),
_prod as NOT MATERIALIZED(
Select
-- rw.Mnemo,
Case rw.Mnemo when 'PSft' then 'Программное обеспечение' when 'PHrd' then 'Оборудование' when 'PExp' then 'Прочие продукты' when 'PSrv' then 'Услуги' when 'pPls' then 'Обучение' else '...' end as prod_Mnemo,
vv1.nValue as prod_del_id, --дополнение позициями спецификации
vv3.svalue as prod_PriceName,
coalesce(vv16.nvalue,0) as prod_dPrice,
vv6.svalue AS prod_Currency,
vv7.svalue AS prod_Xml
from
_obj as rw left join
_value_attr AS vv1 ON vv1.id= rw.id and vv1.idTypeAttr= 100000648000000 left join
_value_attr AS vv3 ON vv3.id= rw.id and vv3.idTypeAttr= 3000000000101 left join
_value_attr AS vv6 ON vv6.id= rw.id and vv6.idTypeAttr= 100005428000000 left join
_value_attr AS vv7 ON vv7.id= rw.id and vv7.idTypeAttr= 100037488300000 left join
_value_attr AS vv16 ON vv16.id= rw.id and vv16.idTypeAttr= 100004087100000
Where
rw.mnemo in ('PSft', 'PHrd', 'PExp', 'PSrv', 'pPls')
and vv1.nValue in (select d_IDfrom _del)
--order by vv1.nValue
),
_res as NOT MATERIALIZED(
select
*
from
_del as d left join _req as r on d.d_ID=r.req_del_id left join _prod as p on p.prod_del_id=d.d_ID
--select * from _del as d, _req as r, _prod as p
)

select
(select LSDBO.Ric_Get_ShortFIO(D_Manager))::text as Del_Manager,
Del_Account::text,
Del_dAccount::timestamp without time zone,
Del_Status::text,
Del_Currency::text,
Del_Channel::text,
prod_Xml::text as pXml,
prod_Mnemo::text as pMnemo,
prod_PriceName::text as pPriceName,
prod_dPrice::double precision as pdPrice,
prod_Currency::text as pCurrency,
(select LSDBO.Ric_Get_ShortFIO(R_Author))::text AS Req_Author,
(select LSDBO.Ric_Get_OccurrenceSource(req_id))::text as Req_ist_CRM,
(select fTrend from lsdbo.Ric_Get_attrUsers(100004487800000) where fFIO =R_Author)::text AS Req_Author_Trend
from _res; -- LIMIT 10;