
                
                
            with curDT as (select localtimestamp as curDate)
            ,SSL as (
            	select
            		SSL.Storage_id
                from
                	dbo.v_StorageStructLevel SSL
                    left join v_MedService MS on MS.MedService_id = SSL.MedService_id
                where
                    (1=1 and SSL.LpuBuilding_id = '3333' and SSL.LpuSection_id = '99560021807') or
                    (1=1 and MS.LpuBuilding_id = '3333' and MS.LpuSection_id = '99560021807')
            )
            
                SELECT 
                DOR.DrugOstatRegistry_id as "DrugOstatRegistry_id",
                DOR.Org_id as "Org_id",
                org_area.area_name as "Org_Area",
                ORG.Org_Nick as "Org_Name",
                DOR.Storage_id as "Storage_id",
                STOR.Storage_Name as "Storage_Name",
                COALESCE(SAT.SubAccountType_Name, '') as "SubAccountType_Name",
                psmc.PrepSeries_MonthCount as "PrepSeries_MonthCount",
                DOR.Drug_id as "Drug_id",
                COALESCE(D.Drug_Name, '') as "Drug_Name",
                case
                    when COALESCE(SER.SumEvnRecept_Kolvo,0) > 0 then

                        ((COALESCE(DOR.DrugOstatRegistry_Sum - DOR.DrugOstatRegistry_Sum/DOR.DrugOstatRegistry_Kolvo*COALESCE(SER.SumEvnRecept_Kolvo, 0.00), 0.00))::numeric(20,2))::text

                    else
                        ((COALESCE(DOR.DrugOstatRegistry_Sum, 0.00))::numeric(20,2))::text

                end as "DrugOstatRegistry_Sum",
                WDS.WhsDocumentUc_Num as "WhsDocumentUc_Num",
                fin_year.yr as "WhsDocumentSupply_Year",
                WDS.WhsDocumentUc_Name as "WhsDocumentUc_Name",
                to_char(WDS.WhsDocumentUc_Date, 'DD.MM.YYYY') as "WhsDocumentUc_Date",

                DOR.DrugFinance_id as "DrugFinance_id",
                DF.DrugFinance_Name as "DrugFinance_Name",
                DOR.WhsDocumentCostItemType_id as "WhsDocumentCostItemType_id",
                WDCIT.WhsDocumentCostItemType_Name as "WhsDocumentCostItemType_Name",
                AM.RUSNAME as "ActMatters_RusName",
                TN.NAME as "Prep_Name",
                DFORM.NAME as "DrugForm_Name",
                Dose.Value as "Drug_Dose",
                Fas.Value as "Drug_Fas",
                F.FULLNAME as "Firm_Name",
                rc.REGNUM as "Reg_Num",
                DN.DrugNomen_Code as "DrugNomen_Code",
                PS.PrepSeries_Ser as "PrepSeries_Ser",
                to_char(PS.PrepSeries_GodnDate, 'DD.MM.YYYY') as "PrepSeries_GodnDate",

                DOR.DrugShipment_id as "DrugShipment_id",
                to_char(DS.DrugShipment_setDT, 'DD.MM.YYYY') as "DrugShipment_setDT",

                DS.DrugShipment_Name as "DrugShipment_Name",
                to_char(DOR.DrugOstatRegistry_insDT, 'DD.MM.YYYY') as "DrugOstatRegistry_insDT",

                DOR.Contragent_id as "Contragent_id",
                GU.GoodsUnit_Nick as "GoodsUnit_Nick",
                coalesce(drug_pack.FULLNAME, '') as "DrugPack_Name"
                
            , COALESCE(O.Okei_Name, '') as "Okei_Name"
            , ((COALESCE(DOR.DrugOstatRegistry_Kolvo, 0) - COALESCE(SER.SumEvnRecept_Kolvo, 0))::numeric(20,6))::text as "DrugOstatRegistry_Kolvo"
            , ((DOR.DrugOstatRegistry_Cost)::numeric(20,2))::text as "DrugOstatRegistry_Price"
            -- Ед. изм лек. форм
            , case when gpc.GoodsUnit_Descr in ('единицы в упаковках', 'единицы количества', 'лекарственная форма') then COALESCE(gpc.GoodsUnit_Nick,'') else COALESCE(DFORM.NAME,'') end as "Okei_NameLek"
            , case when gpc.GoodsUnit_Descr in ('единицы в упаковках', 'единицы количества', 'лекарственная форма') then 1 else 0 end as "IsGoodsUnit"
            -- Кол-во лек. форм
            , case
                when gpc.GoodsPackCount_Count is not null then
                    (((COALESCE(DOR.DrugOstatRegistry_Kolvo, 0) - COALESCE(SER.SumEvnRecept_Kolvo, 0))
                    / case when gpc_dor.GoodsPackCount_Count <> 0 then gpc_dor.GoodsPackCount_Count else 1 end
                    * gpc.GoodsPackCount_Count)::numeric(20,2))::text
                else
                    (((COALESCE(DOR.DrugOstatRegistry_Kolvo, 0) - COALESCE(SER.SumEvnRecept_Kolvo, 0))
                    / case when gpc_dor.GoodsPackCount_Count <> 0 then gpc_dor.GoodsPackCount_Count else 1 end
                    * COALESCE(D.Drug_Fas, 1))::numeric(20,2))::text
            end as "DrugOstatRegistry_KolvoLek"
            , case
                when gpc.GoodsPackCount_Count is not null then
                    ((DOR.DrugOstatRegistry_Cost * gpc_dor.GoodsPackCount_Count) / case when gpc_dor.GoodsPackCount_Count <> 0 then gpc_dor.GoodsPackCount_Count else 1 end)::numeric(20,2)::text
                when COALESCE(D.Drug_Fas, 1) > 0 then
                    ((DOR.DrugOstatRegistry_Cost * gpc_dor.GoodsPackCount_Count) / COALESCE(D.Drug_Fas, 1))::numeric(20,2)::text
                else
                    '0.00'
            end as "DrugOstatRegistry_PriceLek"
        
                
                FROM
                
                dbo.v_DrugOstatRegistry DOR
                inner join dbo.v_Org ORG on ORG.Org_id = DOR.Org_id
                inner join dbo.v_OrgType org_type on org_type.OrgType_id = ORG.OrgType_id
                left join rls.v_Drug D on D.Drug_id = DOR.Drug_id
                left join dbo.v_Okei O  on O.Okei_id = DOR.Okei_id
                left join v_DrugShipment DS on DS.DrugShipment_id = DOR.DrugShipment_id
                left join dbo.v_WhsDocumentSupply WDS on WDS.WhsDocumentSupply_id = DS.WhsDocumentSupply_id
                left join v_DrugFinance DF on DF.DrugFinance_id = DOR.DrugFinance_id
                left join lateral (select WhsDocumentCostItemType_Name from v_WhsDocumentCostItemType WDCIT where WDCIT.WhsDocumentCostItemType_id = DOR.WhsDocumentCostItemType_id limit 1) WDCIT on true
                left join v_Storage STOR  on STOR.Storage_id = DOR.Storage_id
                left join v_SubAccountType SAT  on SAT.SubAccountType_id = DOR.SubAccountType_id
                left join rls.v_DrugComplexMnn DCM on DCM.DrugComplexMnn_id = D.DrugComplexMnn_id
                left join rls.v_DrugComplexMnnName DCMN  on DCMN.DrugComplexMnnName_id = DCM.DrugComplexMnnName_id
                left join rls.v_ACTMATTERS AM on AM.ACTMATTERS_ID = DCMN.ActMatters_id
                left join rls.v_prep P  on P.Prep_id = D.DrugPrep_id
                left join rls.PrepSeries PS  on PS.PrepSeries_id = DOR.PrepSeries_id
                left join rls.v_TRADENAMES TN  on TN.TRADENAMES_ID = P.TRADENAMEID
                left join rls.v_FIRMS F  on F.FIRMS_ID = P.FIRMID
                left join rls.v_CLSDRUGFORMS DFORM  on DFORM.CLSDRUGFORMS_ID = DCM.CLSDRUGFORMS_ID
                left join rls.REGCERT RC  on RC.REGCERT_ID = P.REGCERTID
                left join rls.MASSUNITS DFMU  on DFMU.MASSUNITS_ID = P.DFMASSID
                left join rls.CONCENUNITS DFCU  on DFCU.CONCENUNITS_ID = P.DFCONCID
                left join rls.ACTUNITS DFAU  on DFAU.ACTUNITS_ID = P.DFACTID
                left join rls.SIZEUNITS DFSU  on DFSU.SIZEUNITS_ID = P.DFSIZEID
                left join rls.v_Nomen N  on N.NOMEN_ID = DOR.Drug_id
                left join rls.v_DrugPack dp  on dp.DRUGPACK_ID = N.PPACKID
                left join rls.MASSUNITS MU  on MU.MASSUNITS_ID = N.PPACKMASSUNID
                left join rls.CUBICUNITS CU  on CU.CUBICUNITS_ID = N.PPACKCUBUNID
                left join v_GoodsUnit GU  on GU.GoodsUnit_id = COALESCE(DOR.GoodsUnit_id,
                '57'
                )
                LEFT JOIN LATERAL (
                    select
                        DrugNomen_id,
                        DrugNomen_Code,
                        DrugComplexMnnCode_id
                    from
                        rls.v_DrugNomen DN
                    where
                        Drug_id = DOR.Drug_id
                    order by
                        DrugNomen_id
                    limit 1
                ) DN ON true
                LEFT JOIN LATERAL (
                    select coalesce(
                       cast(cast(P.DFMASS as double precision) as varchar) || ' ' || coalesce(DFMU.SHORTNAME, ''),
                       cast(cast(P.DFCONC as double precision) as varchar) || ' ' || coalesce(DFCU.SHORTNAME, ''),
                       cast(P.DFACT as varchar) || ' ' || coalesce(DFAU.SHORTNAME, ''),
                       cast(P.DFSIZE as varchar) || ' ' || coalesce(DFSU.SHORTNAME, '')
                    ) as Value
                ) Dose ON true
                LEFT JOIN LATERAL(
                    select (
                        (case when D.Drug_Fas is not null then cast(D.Drug_Fas as varchar) else '' end)||
                        (case when D.Drug_Fas is not null and coalesce(D.Drug_Volume,D.Drug_Mass) is not null then ', ' else '' end)||
                        (case when coalesce(D.Drug_Volume,D.Drug_Mass) is not null then coalesce(D.Drug_Volume,D.Drug_Mass) else '' end)||
                        (case when coalesce(N.PPACKVOLUME,N.PPACKMASS) is not null then ' (' || dp.Name || ' ' || cast(cast(coalesce(N.PPACKVOLUME,N.PPACKMASS) as decimal(10,2)) as varchar) || ' ' || coalesce(CU.SHORTNAME,MU.SHORTNAME) || ')' else '' end)
                    ) as Value
                ) Fas ON true
                LEFT JOIN LATERAL (
                    select
                        date_part('year', COALESCE(max(i_wdd.WhsDocumentDelivery_setDT), wds.WhsDocumentUc_Date)) as yr
                    from
                        v_WhsDocumentDelivery i_wdd
                    where
                        i_wdd.WhsDocumentSupply_id = wds.WhsDocumentSupply_id
                ) fin_year ON true
                LEFT JOIN LATERAL (
                    select
                        coalesce(i_kl_t.KLTown_Name, i_kl_c.KLCity_Name, i_kl_sr.KLSubRgn_FullName, i_kl_r.KLRgn_FullName) as area_name,
                        i_ost.KLCountry_id,
                        i_ost.KLRgn_id,
                        i_ost.KLSubRgn_id,
                        i_ost.KLCity_id,
                        i_ost.KLTown_id
                    from
                        v_OrgServiceTerr i_ost
                        left join v_KLRgn i_kl_r  on i_kl_r.KLRgn_id = i_ost.KLRgn_id
                        left join v_KLSubRgn i_kl_sr  on i_kl_sr.KLSubRgn_id = i_ost.KLSubRgn_id
                        left join v_KLCity i_kl_c  on i_kl_c.KLCity_id = i_ost.KLCity_id
                        left join v_KLTown i_kl_t  on i_kl_t.KLTown_id = i_ost.KLTown_id
                    where
                        i_ost.Org_id = DOR.Org_id
                    limit 1
                ) org_area ON true
                
                LEFT JOIN LATERAL ( -- получение ед. списания из партии прихода
                    select
                        i_dus.GoodsUnit_id
                    from
                        v_DrugShipmentLink i_dsl2
                        left join v_DocumentUcStr i_dus on i_dus.DocumentUcStr_id = i_dsl2.DocumentUcStr_id
                    where
                        i_dsl2.DrugShipment_id = COALESCE(ds.DrugShipment_pid, ds.DrugShipment_id) and
                        i_dus.Drug_id = D.Drug_id and
                        i_dus.GoodsUnit_id is not null
                    limit 1
                ) s_gu ON true
                LEFT JOIN LATERAL (
                    select
                        (DATE_PART('year', PS.PrepSeries_GodnDate) - DATE_PART('year', (select curDate from curDT))) * 12 +
                        (DATE_PART('month', PS.PrepSeries_GodnDate) - DATE_PART('month', (select curDate from curDT))) as PrepSeries_MonthCount
                ) psmc ON true
                LEFT JOIN LATERAL (
                    select
                        i_gpc.GoodsPackCount_Count,
                        i_gu.GoodsUnit_Nick,
                        i_gu.GoodsUnit_Descr
                    from
                        v_GoodsPackCount i_gpc
                        inner join v_GoodsUnit i_gu  on i_gu.GoodsUnit_id = i_gpc.GoodsUnit_id
                    where
                        i_gpc.DrugComplexMnn_id = D.DrugComplexMnn_id and
                        i_gpc.GoodsUnit_id = s_gu.GoodsUnit_id
                    limit 1
                ) gpc ON true
                LEFT JOIN LATERAL (
                    select
                        i_gpc.GoodsPackCount_Count
                    from
                        v_GoodsPackCount i_gpc
                        inner join v_GoodsUnit i_gu on i_gu.GoodsUnit_id = i_gpc.GoodsUnit_id
                    where
                        i_gpc.DrugComplexMnn_id = D.DrugComplexMnn_id and
                        i_gpc.GoodsUnit_id = DOR.GoodsUnit_id
                    limit 1
                ) gpc_dor_count ON true
                LEFT JOIN LATERAL (
                    select
                        case
                            when coalesce(dor.GoodsUnit_id, '57') = '57'
                            then 1
                            else gpc_dor_count.GoodsPackCount_Count
                        end as GoodsPackCount_Count
                ) gpc_dor ON true
                left join lateral (
                    select
                        (case
                            when n.SPACKID > 0 then n.SPACKID
                            when n.UPACKID > 0 then n.UPACKID
                            when n.PPACKID > 0 then n.PPACKID
                            else null
                        end) as PACKID
                ) n_pack on true
                left join rls.v_DRUGPACK drug_pack on drug_pack.DRUGPACK_ID = n_pack.PACKID
                
                LEFT JOIN LATERAL(
                    select 0 as SumEvnRecept_Kolvo
                ) SER ON true
            
             WHERE 
                (1=1) and DOR.DrugOstatRegistry_Kolvo > 0 and STOR.Storage_id in (select Storage_id from SSL)
                
                
                        ORDER BY
                            
                DOR.SubAccountType_id
                
                OFFSET 0 ROWS
                FETCH NEXT 100 ROWS ONLY
                
