DROP PROCEDURE IF EXISTS public.sp_rds_copydrugrequestlist;

CREATE OR REPLACE PROCEDURE public.sp_rds_copydrugrequestlist(
    "DrugApplicationListID" uuid,
    "BeginDate" timestamp,
    "EndDate" timestamp
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_drugapplicationlistid    uuid       := "DrugApplicationListID";
    p_begindate                timestamp  := "BeginDate";
    p_enddate                  timestamp  := "EndDate";
    v_newdrugappliactionlistid uuid       := gen_random_uuid();
    v_year                     varchar(4) := TO_CHAR(p_begindate, 'YYYY');
    v_requestcodestart         int;
    v_entityid                 int;
    v_rc                       int;
    v_count                    int;
BEGIN
    
    SELECT entityid
    INTO v_entityid
    FROM vclib.t_entities
    WHERE schema = 'RDS'
      AND entname = 'DRUG_APPLICATION_LIST_ITEM'
    LIMIT 1;
    
    DROP TABLE IF EXISTS temp_dali;
    CREATE TEMP TABLE temp_dali AS
        SELECT gen_random_uuid()           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,
               d.preparation
        FROM rds.t_drug_application_list_item d
        WHERE d.drugapplicationlist = p_drugapplicationlistid
          AND (d.enddate IS NULL OR d.enddate > CURRENT_TIMESTAMP);
    
    SELECT COUNT(*)
    INTO v_count
    FROM temp_dali;
    
    SELECT vclib.sp_generatenumber(1936, v_year, v_count)
    INTO v_requestcodestart;
    
    INSERT INTO
        rds.t_drug_application_list(drugapplicationlistid, createdate, begindate, enddate, isactive, listname)
    SELECT v_newdrugappliactionlistid,
           CURRENT_TIMESTAMP,
           p_begindate,
           p_enddate,
           TRUE,
           dal.listname || '_Копия от ' || TO_CHAR(CURRENT_TIMESTAMP, 'dd.MM.yyyy')
    FROM rds.t_drug_application_list dal
    WHERE dal.drugapplicationlistid = p_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,
                                         preparation)
    SELECT newdrugapplicationlistitemid,
           v_newdrugappliactionlistid,
           price,
           cmnn,
           medicationforms,
           benefittype,
           groupsofdrugsclassification,
           v_requestcodestart + ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), --RequestCode
           doseview,
           ctrn,
           packquantity,
           comment,
           sectioncode,
           preparatid,
           persrequest,
           enddate,
           ehrrus9a9g09e8kl,
           ehrrus6kiqts8t99,
           dosecount,
           ehrrush17f8uts3n,
           consumerunits,
           electroniccertificate,
           preparation
    FROM temp_dali tmp;
    
    INSERT INTO
        rds.t_additional_factors_drug_application_item (additionalfactorsdrugapplicationitemid,
                                                        drugapplicationlistitem,
                                                        benefits)
    SELECT gen_random_uuid(),
           tmp.newdrugapplicationlistitemid,
           afd.benefits
    FROM rds.t_additional_factors_drug_application_item afd
    INNER JOIN temp_dali                                tmp
                   ON tmp.olddrugapplicationlistitemid = 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;
$$;
