DROP PROCEDURE IF EXISTS dbo.sp_createfrlloremainsprocess;
DROP PROCEDURE IF EXISTS public.sp_createfrlloremainsprocess;
-- =============================================
-- Author:      Vitacore (с)
-- Create date: 2024 11 25
-- Description: Выгрузка данных в ФРЛЛО
-- =============================================
CREATE OR REPLACE PROCEDURE rds.sp_createfrlloremainsprocess(
    "ExportId" uuid DEFAULT NULL::uuid
)
    LANGUAGE plpgsql
AS
$$
DECLARE
    p_exportid    uuid := "ExportId";
    v_lastexport  uuid;
    v_currentdate timestamp;
    v_lastid      integer;
    v_count       integer;
    v_filenumber  integer;
BEGIN
    
    SELECT efr.exportfrlloremainsid
    INTO v_lastexport
    FROM rds.t_export_frllo_remains efr
    WHERE efr.createdate < v_currentdate
      AND efr.exportfrlloremainsid <> p_exportid
    ORDER BY efr.createdate DESC
    LIMIT 1;
    
    SELECT efr.createdate
    FROM rds.t_export_frllo_remains efr
    WHERE efr.exportfrlloremainsid = p_exportid
    INTO v_currentdate;
    
    
    CREATE TEMP TABLE tmp_frllo
    (
        "id"  integer NOT NULL UNIQUE,
        "num" serial
    );
    
    INSERT INTO tmp_frllo("id")
    SELECT exportfrlloremainsdataid
    FROM rds.t_export_frllo_remains_data efrd
    WHERE efrd.exportfrlloremains = v_lastexport
      AND (CASE
               WHEN efrd.itemqty > 0 THEN 1
               WHEN (efrd.frllostatus = 400 OR efrd.FrlloStatus = 1) AND efrd.isdelete = 1 THEN 1
               ELSE 0
           END) = 1
      
      AND NOT EXISTS (SELECT *
                      FROM rds.t_export_frllo_remains_data ef
                      WHERE ef.exportfrlloremains = p_exportid
                        AND ef.remainsid = efrd.remainsid
                     );
    
    SELECT MAX(num)
    INTO v_count
    FROM tmp_frllo;
    
    SELECT CASE
               WHEN COUNT(*) > 100000 THEN filenumber + 1
               ELSE filenumber
           END
    INTO v_filenumber
    FROM rds.t_export_frllo_remains_data rd
    WHERE rd.exportfrlloremains = p_exportid
    GROUP BY filenumber
    ORDER BY filenumber DESC
    LIMIT 1;
    
    IF (v_count > 0) THEN
        CALL sp_generateidforentity('EXPORT_FRLLO_REMAINS_DATA', 'PrimaryKey', v_count, v_lastid);
        
        INSERT INTO
            rds.t_export_frllo_remains_data
        (exportfrlloremainsdataid,
         exportfrlloremains,
         frlloid,
         pharmacy,
         klpcode,
         micode,
         speccode,
         packqty,
         itemqty,
         drugexpirationdt,
         drugseria,
         name,
         isdelete,
         remainsid,
         filenumber)
        SELECT (v_lastid + ROW_NUMBER() OVER (ORDER BY efrd.remainsid)),
               p_exportid,
               frlloid,
               efrd.pharmacy,
               efrd.klpcode,
               efrd.micode,
               efrd.speccode,
               0,
               0,
               efrd.drugexpirationdt,
               efrd.drugseria,
               efrd.name,
               TRUE,
               efrd.remainsid,
               v_filenumber
        FROM rds.t_export_frllo_remains_data efrd
        WHERE EXISTS
                  (SELECT 1
                   FROM tmp_frllo
                   WHERE tmp_frllo.id = efrd.exportfrlloremainsdataid
                  );
        
        
        UPDATE t_entity_ids
        SET id = (SELECT MAX(exportfrlloremainsdataid) FROM rds.t_export_frllo_remains_data)
        WHERE entname = 'RDS.EXPORT_FRLLO_REMAINS_DATA'
          AND fieldname = 'PrimaryKey';
    
    END IF;

END;
$$;