anc/q_stat.sh at master · minzak/anc · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
#!/bin/bash

sqlite3 -echo -box data.db '
SELECT
    depun,
    COUNT(*) as total_dosare,
    COUNT(CASE WHEN solutie IS NOT NULL THEN 1 END) as cu_solutie,
    COUNT(CASE WHEN ordin IS NOT NULL THEN 1 END) as cu_ordin,
    COUNT(CASE WHEN anexa IS NOT NULL THEN 1 END) as cu_anexa,
    COUNT(CASE WHEN cminori IS NOT NULL THEN 1 END) as cu_cminori,
    COUNT(CASE WHEN result = 1 THEN 1 END) as cu_result,
    COUNT(CASE WHEN juramat IS NOT NULL THEN 1 END) as cu_juramat,
    ROUND(COUNT(CASE WHEN solutie IS NOT NULL THEN 1 END) * 100.0 / COUNT(*), 2) as procent_solutie,
    ROUND(COUNT(CASE WHEN result = 1 THEN 1 END) * 100.0 / COUNT(*), 2) as procent_result
FROM Dosar11
WHERE depun = "2023-12-19"
GROUP BY depun
ORDER BY depun;'


sqlite3 -echo -box data.db '
SELECT
    'solutie' as field_name,
    COUNT(CASE WHEN solutie IS NOT NULL THEN 1 END) as filled,
    COUNT(*) as total,
    ROUND(COUNT(CASE WHEN solutie IS NOT NULL THEN 1 END) * 100.0 / COUNT(*), 2) as procent
FROM Dosar11
WHERE depun = "2023-12-19"

UNION ALL

SELECT
    'ordin' as field_name,
    COUNT(CASE WHEN ordin IS NOT NULL THEN 1 END) as filled,
    COUNT(*) as total,
    ROUND(COUNT(CASE WHEN ordin IS NOT NULL THEN 1 END) * 100.0 / COUNT(*), 2) as procent
FROM Dosar11
WHERE depun = "2023-12-19"

UNION ALL

SELECT
    'anexa' as field_name,
    COUNT(CASE WHEN anexa IS NOT NULL THEN 1 END) as filled,
    COUNT(*) as total,
    ROUND(COUNT(CASE WHEN anexa IS NOT NULL THEN 1 END) * 100.0 / COUNT(*), 2) as procent
FROM Dosar11
WHERE depun = "2023-12-19"

UNION ALL

SELECT
    'cminori' as field_name,
    COUNT(CASE WHEN cminori IS NOT NULL THEN 1 END) as filled,
    COUNT(*) as total,
    ROUND(COUNT(CASE WHEN cminori IS NOT NULL THEN 1 END) * 100.0 / COUNT(*), 2) as procent
FROM Dosar11
WHERE depun = "2023-12-19"

UNION ALL

SELECT
    'result' as field_name,
    COUNT(CASE WHEN result = 1 THEN 1 END) as filled,
    COUNT(*) as total,
    ROUND(COUNT(CASE WHEN result = 1 THEN 1 END) * 100.0 / COUNT(*), 2) as procent
FROM Dosar11
WHERE depun = "2023-12-19"

UNION ALL

SELECT
    'juramat' as field_name,
    COUNT(CASE WHEN juramat IS NOT NULL THEN 1 END) as filled,
    COUNT(*) as total,
    ROUND(COUNT(CASE WHEN juramat IS NOT NULL THEN 1 END) * 100.0 / COUNT(*), 2) as procent
FROM Dosar11
WHERE depun = "2023-12-19";
'

sqlite3 -echo -box data.db '
SELECT
    id,
    depun,
    CASE WHEN solutie IS NOT NULL THEN '✓' ELSE '✗' END as solutie,
    CASE WHEN ordin IS NOT NULL THEN '✓' ELSE '✗' END as ordin,
    CASE WHEN anexa IS NOT NULL THEN '✓' ELSE '✗' END as anexa,
    CASE WHEN cminori IS NOT NULL THEN '✓' ELSE '✗' END as cminori,
    CASE WHEN result = 1 THEN '✓' ELSE '✗' END as result,
    CASE WHEN juramat IS NOT NULL THEN '✓' ELSE '✗' END as juramat,
    termen
FROM Dosar11
WHERE depun = "2023-12-19"
ORDER BY id;
'

sqlite3 -echo -box data.db '
SELECT
    depun,
    COUNT(*) as total_dosare,
    COUNT(CASE WHEN solutie IS NOT NULL THEN 1 END) as cu_solutie,
    COUNT(CASE WHEN ordin IS NOT NULL THEN 1 END) as cu_ordin,
    COUNT(CASE WHEN result = 1 THEN 1 END) as cu_result,
    ROUND(COUNT(CASE WHEN result = 1 THEN 1 END) * 100.0 / COUNT(*), 2) as procent_complet
FROM Dosar11
WHERE depun = "2023-12-19"
GROUP BY depun
ORDER BY depun DESC
LIMIT 20;
'

sqlite3 -echo -box data.db '
SELECT
    id,
    depun,
    termen,
    CASE
        WHEN solutie IS NULL AND ordin IS NULL THEN 'Нет решения'
        WHEN solutie IS NOT NULL AND ordin IS NULL THEN 'Есть решение, нет приказа'
        WHEN solutie IS NULL AND ordin IS NOT NULL THEN 'Есть приказ, нет решения'
        ELSE 'Обработано'
    END as status
FROM Dosar11
WHERE depun = "2023-12-19"
AND (solutie IS NULL OR ordin IS NULL OR result != 1)
ORDER BY id;
'


sqlite3 -echo -box data.db 'SELECT *FROM Termen11 WHERE id = "10051/RD/2025" ORDER BY id;'