drop procedure if exists sp_RDS_CopyDrugRequestList;
CREATE PROCEDURE sp_RDS_CopyDrugRequestList
(
	"DrugApplicationListID" uuid
	,"BeginDate" timestamp without time zone DEFAULT NULL::timestamp without time zone
	,"EndDate" timestamp without time zone DEFAULT NULL::timestamp without time zone
)
language plpgsql
as
$$
Begin
	declare
         _NewDrugApplicationListID uuid := gen_random_uuid();
         _Year char(4) := TO_CHAR("BeginDate", 'YYYY');
         _RequestCodeStart int;
         _EntityID int;
         _rc int;
	Begin
        _EntityID := select EntityID from VCLib.T_ENTITIES where Schema = 'RDS' AND EntName = 'DRUG_APPLICATION_LIST_ITEM' limit 1;
        _RequestCodeStart := coalesce((select NextNumber from VCLib.T_GEN_NUMBERS where EntityID = _EntityID and NumberName = _Year), 0);

        drop table if exists temp_dali;
        create temp table temp_dali as
        select 
            gen_random_uuid() as NewDrugApplicationListItemID,
            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

        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,
            CURRENT_DATE,
            "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 temp_dali;

        get diagnostics _rc = row_count; --set @rc = @@ROWCOUNT

        merge into VCLib.T_GEN_NUMBERS 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 (gen_random_uuid(), s.EntityID, s.NumberName, _RequestCodeStart + _rc);


    insert into RDS.T_ADDITIONAL_FACTORS_DRUG_APPLICATION_ITEM
    (
        AdditionalFactorsDrugApplicationItemID,
        DrugApplicationListItem,
        AdditionalFinanc,
        Palliative,
        Benefits
    )
    select
    (
        gen_random_uuid(),
        tmp.NewDrugApplicationListItemID, 
        afd.AdditionalFinanc, 
        afd.Palliative, 
        afd.Benefits 
    )
    from RDS.T_ADDITIONAL_FACTORS_DRUG_APPLICATION_ITEM afd
    inner join temp_dali tmp 
    on tmp.old_drug_application_list_item_id = afd.DrugApplicationListItem;
        
    insert into RDS.T_DOSES_DRUG_APPLICATION_LIST
    (
        DosesDrugApplicationListID, 
        Val, 
        OrderNum, 
        DoseView, 
        DrugApplicationListItem, 
        AdditionalSymbol
    )
    select 
    (
        gen_random_uuid(),
        dda.Val, 
        dda.OrderNum,
        dda.DoseView, 
        tmp.NewDrugApplicationListItemID, 
        dda.AdditionalSymbol
    )
    from RDS.T_DOSES_DRUG_APPLICATION_LIST dda
    inner join temp_dali tmp 
    on tmp.OldDrugApplicationListItemID = dda.DrugApplicationListItem;

    insert into RDS.T_LPU_OVER_REQUEST
    (
        LpuOverRequestID, 
        DrugApplicationListItem, 
        LpuMain
    )
    select 
    (
        gen_random_uuid(),
        tmp.NewDrugApplicationListItemID, 
        lor.LpuMain
    )
    from RDS.T_LPU_OVER_REQUEST lor
    inner join temp_dali tmpû
	on tmp.OldDrugApplicationListItemID = lor.DrugApplicationListItem;
	End;
End;
$$
