
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_RDS_CopyDrugRequestList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_RDS_CopyDrugRequestList]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_RDS_CopyDrugRequestList]
	@DrugApplicationListID uniqueidentifier
,	@BeginDate datetime
,	@EndDate datetime
as
SET NOCOUNT, XACT_ABORT ON

begin

    declare @NewDrugApplicationListID uniqueidentifier = newid()
            ,@Year char(4) = cast(YEAR(@BeginDate) as char(4))
            ,@RequestCodeStart int
            ,@EntityID int
            ,@rc int
    
    set @EntityID = (select top (1) EntityID from VCLib.T_ENTITIES where [Schema] = 'RDS' and EntName = 'DRUG_APPLICATION_LIST_ITEM')
    set @RequestCodeStart = isnull((select top (1) NextNumber from VCLib.T_GEN_NUMBERS with(rowlock,xlock) where EntityID = @EntityID and NumberName = @Year), 0)
    
    if object_id('tempdb..#DALI') is not null
    	drop table #DALI
    select newid() as NewDrugApplicationListItemID, d.DrugApplicationListItemID as OldDrugApplicationListItemID, d.DrugApplicationList, d.Price, d.CMnn
    , d.MedicationForms, d.BenefitType, d.GroupsOfDrugsClassification
    , d.RequestCode, d.DoseView, d.CTrn, d.PackQuantity, d.Comment, d.SectionCode, d.PreparatId, d.PersRequest, d.EndDate, d.EhrRus9a9g09e8kl, d.EhrRus6kiqts8t99, d.DoseCount, d.EhrRusH17F8UTS3N, d.ConsumerUnits, d.ElectronicCertificate
    into #DALI
    from RDS.T_DRUG_APPLICATION_LIST_ITEM d
    where d.DRUGAPPLICATIONLIST = @DrugApplicationListID and (d.EndDate is null or d.EndDate > GETDATE())
    
    insert into rds.T_DRUG_APPLICATION_LIST(DrugApplicationListID, CreateDate, BeginDate, EndDate, IsActive, ListName)
    select @NewDrugApplicationListID, cast(getdate() as date) as CreateDate, @BeginDate, @EndDate, 1, dal.ListName + '_Копия от ' + FORMAT(GETDATE(), 'dd.MM.yyyy')
    from rds.T_DRUG_APPLICATION_LIST dal
	where dal.DrugApplicationListID = @DrugApplicationListID
    
    insert into RDS.T_DRUG_APPLICATION_LIST_ITEM(DrugApplicationListItemID, DrugApplicationList, Price, CMnn, MedicationForms, BenefitType, GroupsOfDrugsClassification
    , RequestCode, DoseView, CTrn, PackQuantity, Comment, SectionCode, PreparatId, PersRequest, EndDate, EhrRus9a9g09e8kl, EhrRus6kiqts8t99, DoseCount, EhrRusH17F8UTS3N, ConsumerUnits,ElectronicCertificate)
    select NewDrugApplicationListItemID, @NewDrugApplicationListID, Price, CMnn, MedicationForms, BenefitType, GroupsOfDrugsClassification
    , @RequestCodeStart + row_number() over (order by (select null)) --RequestCode
    , DoseView, CTrn, PackQuantity, Comment, SectionCode, PreparatId, PersRequest, EndDate, EhrRus9a9g09e8kl, EhrRus6kiqts8t99, DoseCount, EhrRusH17F8UTS3N, ConsumerUnits,ElectronicCertificate
    from #DALI tmp
    
    set @rc = @@ROWCOUNT
    
    ;merge VCLib.T_GEN_NUMBERS as t
    	using (select @EntityID as EntityID, @Year as NumberName, @RequestCodeStart + @rc as NextNumber) as s
    	on t.EntityID = s.EntityId and t.NumberName = s.NumberName
    		when matched
    		then	update
    				set t.NextNumber = @RequestCodeStart + @rc
    		when not matched
    		then	insert (GenNumbersID, EntityID, NumberName, NextNumber)
    				values (newid(), s.EntityID, s.NumberName, @RequestCodeStart + @rc);
    
    insert into RDS.T_ADDITIONAL_FACTORS_DRUG_APPLICATION_ITEM(AdditionalFactorsDrugApplicationItemID, DrugApplicationListItem, Benefits)
    select newid(), tmp.NewDrugApplicationListItemID, afd.Benefits 
    from RDS.T_ADDITIONAL_FACTORS_DRUG_APPLICATION_ITEM afd
    inner join
    	#DALI tmp
    	on tmp.OldDrugApplicationListItemID = afd.DrugApplicationListItem
    
    insert into RDS.T_DOSES_DRUG_APPLICATION_LIST(DosesDrugApplicationListID, Val, OrderNum, DoseView, DrugApplicationListItem, AdditionalSymbol)
    select newid(), dda.Val, dda.OrderNum, dda.DoseView, tmp.NewDrugApplicationListItemID, dda.AdditionalSymbol
    from RDS.T_DOSES_DRUG_APPLICATION_LIST dda
    inner join
    	#DALI tmp
    	on tmp.OldDrugApplicationListItemID = dda.DrugApplicationListItem
    
    insert into RDS.T_LPU_OVER_REQUEST(LpuOverRequestID, DrugApplicationListItem, LpuMain)
    select newid(), tmp.NewDrugApplicationListItemID, lor.LpuMain
    from RDS.T_LPU_OVER_REQUEST lor
    inner join
    	#DALI tmp
    	on tmp.OldDrugApplicationListItemID = lor.DrugApplicationListItem

end
go