Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --CREATE EXTENSION IF NOT EXISTS tablefunc;
- SELECT
- "INN" AS "ИНН",
- "OGRN" AS "ОГРН",
- "ShortName" AS "Краткое наименование",
- dbo."Files"."Name" AS "Имя файла",
- "PivotTable"."MessageId" AS "ID Сообщения",
- "DictCatalogTasks"."Name" AS "Наименование задачи",
- COALESCE(TO_CHAR("1"+INTERVAL '3 hour','yyyy-mm-dd hh24:mi:ss'),'NULL') AS "Отправлено",
- COALESCE(TO_CHAR("2"+INTERVAL '3 hour','yyyy-mm-dd hh24:mi:ss'),'NULL') AS "Загружено",
- COALESCE(TO_CHAR("3"+INTERVAL '3 hour','yyyy-mm-dd hh24:mi:ss'),'NULL') AS "Ошибка",
- COALESCE(TO_CHAR("4"+INTERVAL '3 hour','yyyy-mm-dd hh24:mi:ss'),'NULL') AS "Принято в обработку",
- COALESCE(TO_CHAR("5"+INTERVAL '3 hour','yyyy-mm-dd hh24:mi:ss'),'NULL') AS "Зарегистрировано",
- COALESCE(TO_CHAR("6"+INTERVAL '3 hour','yyyy-mm-dd hh24:mi:ss'),'NULL') AS "Отклонено"
- FROM
- (
- SELECT * FROM crosstab('SELECT r."MessageId", COALESCE(s."Value",r."ReceiptStatus") ReceiptStatus, MAX(r."DateReceipt") "DateReceipt"
- FROM dbo."MessageReceipts" r
- LEFT JOIN dbo."StatusEc" s ON s."Id" = r."StatusEcId"
- GROUP BY r."MessageId", coalesce(s."Value",r."ReceiptStatus") ORDER BY 1',
- $$SELECT unnest('{0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 18, 99}'::int[])$$
- )
- AS SourceTable ("MessageId" uuid, "0" TIMESTAMP, "1" TIMESTAMP,"2" TIMESTAMP,"3" TIMESTAMP,"4" TIMESTAMP,"5" TIMESTAMP,"6" TIMESTAMP,
- "7" TIMESTAMP, "8" TIMESTAMP,"9" TIMESTAMP,"10" TIMESTAMP,"18" TIMESTAMP,"99" TIMESTAMP
- )
- ) "PivotTable"
- JOIN (
- SELECT m.*, COALESCE(s."Value",m."Status") "CombinedStatus"
- FROM dbo."Messages" m
- LEFT JOIN dbo."StatusEc" s ON s."Id" = m."StatusEcId"
- ) AS m ON "PivotTable"."MessageId" = m."Id"
- JOIN dbo."Accounts" ON m."OwnerId" = dbo."Accounts"."Id"
- JOIN dbo."DictCatalogTasks" ON m."CatalogTaskId" = dbo."DictCatalogTasks"."Id"
- JOIN dbo."MessageFiles" ON m."Id" = dbo."MessageFiles"."Message_Id"
- JOIN dbo."Files" ON dbo."MessageFiles"."File_Id" = dbo."Files"."Id"
- WHERE
- ((dbo."DictCatalogTasks"."Id"='832CB17D-8F5F-481A-827D-4D7EF3C53DD7' )
- AND (m."CreationDate" BETWEEN (SELECT now() AT TIME ZONE 'utc')-INTERVAL '1 day' AND (SELECT now() AT TIME ZONE 'utc')+INTERVAL '1 day')
- AND (m."CombinedStatus") NOT IN (0,8,9,3,6,5)
- AND "1" < (SELECT now() AT TIME ZONE 'utc')-INTERVAL '1 hour'
- AND (dbo."Accounts"."OGRN" IN ('1028900507657','1027806865481'))
- )
- OR
- ((dbo."DictCatalogTasks"."Id"='832CB17D-8F5F-481A-827D-4D7EF3C53DD7' )
- AND (m."CreationDate" BETWEEN (SELECT now() AT TIME ZONE 'utc')-INTERVAL '1 day' AND (SELECT now() AT TIME ZONE 'utc')+INTERVAL '1 day')
- AND (m."CombinedStatus") NOT IN (0,8,9,3,6,5)
- AND "2" < (SELECT now() AT TIME ZONE 'utc')-INTERVAL '1 hour'
- AND (dbo."Accounts"."OGRN" IN ('1028900507657','1027806865481'))
- )
- OR
- ((dbo."DictCatalogTasks"."Id"='832CB17D-8F5F-481A-827D-4D7EF3C53DD7' )
- AND (m."CreationDate" BETWEEN (SELECT now() AT TIME ZONE 'utc')-INTERVAL '1 day' AND (SELECT now() AT TIME ZONE 'utc')+INTERVAL '1 day')
- AND (m."CombinedStatus") NOT IN (0,8,9,3,6,5)
- AND "4" < (SELECT now() AT TIME ZONE 'utc')-INTERVAL '1 hour'
- AND (dbo."Accounts"."OGRN" IN ('1028900507657','1027806865481'))
- )
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
