import { fileIdsSql, reportedFlagSql, statusSql, filesCountSql } from '../../../../src/domain/submission/SubmissionPeriodSql'; describe('SubmissionPeriodSql Tests', () => { test('statusSql', () => { const actual = statusSql(`"sp"`); expect(actual).toEqual( `(select CASE WHEN ( "sp"."ON_TIME_OVERRIDE" = 1) THEN 'On-time' WHEN ( "spli1"."EARLIEST_FILE_SUBMISSION_DATE" < "sp"."EXPECTED_DATE" ) THEN 'On-time' WHEN ( ( "sp"."NO_DATA" = 1 ) AND ( "sp"."NO_DATA_CREATE_DATE" < "sp"."EXPECTED_DATE")) THEN 'On-time' WHEN ( "sp"."EXPECTED_DATE" > SYSTIMESTAMP) THEN 'Pending' ELSE 'Late' END from SUBMISSION_PERIOD "sp1" left join SUBMISSION_PERIOD_LINE_ITEM_V "spli1" on "spli1"."SUBMISSION_PERIOD_SID" = "sp1"."SID" AND "spli1"."CUSTOMER_SID" = "sp1"."CUSTOMER_SID" where "sp1"."SID" = "sp"."SID" AND "sp1"."CUSTOMER_SID" = "sp"."CUSTOMER_SID")` ); }); test('reportedFlagSql', () => { const actual = reportedFlagSql(`"sp"`); expect(actual).toEqual( `(select CASE WHEN MAX("sp"."ON_TIME_OVERRIDE") = 1 THEN 1 WHEN MAX("sp"."NO_DATA") = 1 THEN 1 WHEN COUNT("df"."ID") > 0 THEN 1 ELSE 0 END from SUBMISSION_SCHEDULE "ss1" left join DATA_FILE_SUMMARY_INFO "dfsi" on "dfsi"."SUBMISSION_PERIOD_SID" = "sp"."SID" AND "dfsi"."CUSTOMER_SID" = "sp"."CUSTOMER_SID" left join DATA_TYPE "dt1" on "ss1"."DATA_TYPE_SID" = "dt1"."SID" left join DATA_FILE "df" on "dfsi"."CUSTOMER_SID" = "df"."CUSTOMER_SID" AND "dfsi"."DATA_FILE_SID" = "df"."SID" AND "df"."DATA_TYPE" = "dt1"."TYPE" where "ss1"."SID" = "sp"."SUBMISSION_SCHEDULE_SID" AND "ss1"."CUSTOMER_SID" = "sp"."CUSTOMER_SID")` ); }); test('fileIdsSql', () => { const actual = fileIdsSql(`"sp"`); expect(actual).toEqual( `(select listagg("df"."ID",',') within group (ORDER BY "df"."CREATE_DATE") from SUBMISSION_SCHEDULE "ss1" left join DATA_FILE_SUMMARY_INFO "dfsi" on "dfsi"."SUBMISSION_PERIOD_SID" = "sp"."SID" AND "dfsi"."CUSTOMER_SID" = "sp"."CUSTOMER_SID" left join DATA_TYPE "dt1" on "ss1"."DATA_TYPE_SID" = "dt1"."SID" left join DATA_FILE "df" on "dfsi"."CUSTOMER_SID" = "df"."CUSTOMER_SID" AND "dfsi"."DATA_FILE_SID" = "df"."SID" AND "df"."DELETED" = 0 AND "df"."DATA_TYPE" = "dt1"."TYPE" where "ss1"."SID" = "sp"."SUBMISSION_SCHEDULE_SID" AND "ss1"."CUSTOMER_SID" = "sp"."CUSTOMER_SID")` ); }); test('filesCountSql', () => { const actual = filesCountSql(`"sp"`); expect(actual).toEqual( `(select count(df.id) as number_of_files from SUBMISSION_SCHEDULE ss1 left join DATA_FILE_SUMMARY_INFO dfsi on dfsi.SUBMISSION_PERIOD_SID = "sp".SID AND dfsi.CUSTOMER_SID = "sp".CUSTOMER_SID left join DATA_TYPE dt1 on ss1.DATA_TYPE_SID = dt1.SID left join DATA_FILE df on dfsi.CUSTOMER_SID = df.CUSTOMER_SID AND dfsi.DATA_FILE_SID = df.SID AND df.DELETED = 0 AND df.DATA_TYPE = dt1.TYPE where ss1.SID = "sp".SUBMISSION_SCHEDULE_SID AND ss1.CUSTOMER_SID= "sp".CUSTOMER_SID)` ); }); });