· 8 years ago · Jun 14, 2018, 12:52 PM
1-- ONLY FOR DEV
2-- SET SEARCH_PATH TO alto_stats;
3
4DROP FUNCTION IF EXISTS selectFailedClosures(_possible_forms TEXT);
5DROP FUNCTION IF EXISTS selectFailedClosures(_possible_forms TEXT, _sync_archive BOOLEAN);
6
7CREATE OR REPLACE FUNCTION selectFailedClosures(_possible_forms TEXT, _sync_archive BOOLEAN)
8 RETURNS TABLE (
9 external_terminal text,
10 currency INTEGER,
11 happened_at text,
12 booked_at date,
13 name character(17)
14 )
15AS $$
16DECLARE
17 _happened_at VARCHAR[50];
18BEGIN
19
20 _happened_at = string_to_array(_possible_forms, ',');
21
22 RETURN QUERY
23 SELECT * FROM
24 (
25 (
26 SELECT
27 trim(sk__uza.id_kas),
28 sk__sez_a.c_mena,
29 to_char(to_timestamp(closure_datetime_correction(sk__uza.uzaverka), 'YYYYMMDD HH24:MI:SS') :: TIMESTAMPTZ, 'YYYY-MM-DD"T"HH24:MI:SS') AS uzaverka,
30 sk__sez_a.uct_den,
31 sk__uza.uzaverka AS _name
32 FROM sk__uza
33 LEFT JOIN sk__sez_a ON sk__uza.uzaverka = sk__sez_a.uzaverka
34 WHERE
35 sk__sez_a.c_mena IS NOT NULL
36 AND sk__uza.uzaverka = sk__sez_a.uzaverka
37 AND sk__uza.uzaverka = ANY(_happened_at)
38 AND _sync_archive IS TRUE
39 )
40 UNION
41 (
42 SELECT
43 trim(sk__uza.id_kas),
44 sk__sez.c_mena,
45 to_char(to_timestamp(closure_datetime_correction(sk__uza.uzaverka), 'YYYYMMDD HH24:MI:SS') :: TIMESTAMPTZ, 'YYYY-MM-DD"T"HH24:MI:SS') AS uzaverka,
46 sk__sez.uct_den,
47 sk__uza.uzaverka AS _name
48 FROM sk__uza
49 LEFT JOIN sk__sez ON sk__uza.uzaverka = sk__sez.uzaverka
50 WHERE
51 sk__sez.c_mena IS NOT NULL
52 AND sk__uza.uzaverka = sk__sez.uzaverka
53 AND sk__uza.uzaverka = ANY(_happened_at)
54 )
55 ) as uzaverka2 ORDER BY uzaverka ASC
56 ;
57END; $$
58LANGUAGE 'plpgsql';