- 01
- 02
- 03
- 04
- 05
- 06
- 07
- 08
- 09
- 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
func (db *DB) CountResults(filial []int64, device []int64, workplace []int64, from time.Time, to time.Time, splits bool, skip int64) (res []*AvgResults, err error) {
var query string
var rows *pgx.Rows
_, err = db.Exec("SET LOCAL TIME ZONE 'Asia/Yekaterinburg';")
if err != nil {
return nil, err
}
switch {
case splits:
query = `WITH data AS (SELECT "IndicatorResultValue"."IndicatorId", "qm"."Session"."Blocked", "qm"."Result"."SessionId" AS "SessionId", "qm"."DeviceIPRangeType"."Number" AS "RangeType", "qm"."DeviceIPRangeType"."Id" AS "RangeTypeId", "qm"."BranchWorkPlace"."Id" AS "WorkPlaceId", "DeviceIPRange"."BranchId", date("Datetime") as "Date"
FROM qm."Result"
LEFT OUTER JOIN "qm"."Device" ON ("Result"."DeviceId" = "Device"."Id")
LEFT OUTER JOIN "qm"."BranchWorkPlace" ON ("BranchWorkPlace"."Id" = "Device"."WorkPlaceId")
LEFT OUTER JOIN "qm"."Session" ON ("Result"."SessionId" = "Session"."Id")
LEFT OUTER JOIN "qm"."IndicatorResultValue" ON ("Result"."IndicatorResultValueId" = "IndicatorResultValue"."Id")
LEFT OUTER JOIN "qm"."ResultValue" ON ("IndicatorResultValue"."ResultValueId" = "ResultValue"."Id")
LEFT OUTER JOIN "qm"."DeviceIPRangeType" ON ("DeviceIPRangeType"."Id" = "Session"."DeviceIPRangeTypeId")
LEFT OUTER JOIN "qm"."DeviceIPRange" ON ("Session"."DeviceIPRangeId" = "DeviceIPRange"."Id")
FULL OUTER JOIN "queue"."Branches" ON ("DeviceIPRange"."BranchId" = "Branches"."Id" AND (0 = any ($1::int8[]) OR "Branches"."Id" = any($1::int8[])))),
data1 AS (SELECT COUNT(DISTINCT(data."SessionId")) AS "Count",
"BranchId",
CASE WHEN "RangeTypeId" < 3 THEN 0
ELSE "RangeTypeId"
END,
"Date"
FROM data
WHERE (0 = any($2::int8[]) OR "RangeType" = any($2::int8[]))
AND (0 = any ($1::int8[]) OR "BranchId" = any($1::int8[]))
AND (0 = any($3::int8[]) OR "WorkPlaceId" = any($3::int8[]))
AND "Date" BETWEEN $4::timestamptz AND $5::timestamptz
AND NOT "Blocked"
GROUP BY "BranchId", CASE WHEN "RangeTypeId" < 3 THEN 0 ELSE "RangeTypeId" END, "Date"),
data2 AS (SELECT "Id", "Name", date("d") AS "Date" FROM
queue."Branches"
CROSS JOIN generate_series($4::timestamptz, $5::timestamptz, $6) as "d"
WHERE (0 = any ($1::int8[]) OR "Id" = any($1::int8[])) ),
data3 AS (SELECT "Count" AS C, 0 AS Avg, data1."Date", data1."RangeTypeId", data1."BranchId"
FROM "data1" GROUP BY "data1"."Date", "Count", "data1"."BranchId", "data1"."RangeTypeId"),
data3_1 AS (SELECT "qm"."DeviceIPRangeType"."Id" FROM "qm"."DeviceIPRange"
RIGHT OUTER JOIN "qm"."DeviceIPRangeType" ON ("qm"."DeviceIPRange"."DeviceIPTypeId" = "qm"."DeviceIPRangeType"."Id")
WHERE (0 = any ($1::int8[]) OR "DeviceIPRange"."BranchId" = any($1::int8[]))
GROUP BY "qm"."DeviceIPRangeType"."Id"),
data4 AS (SELECT "Id", "Name", date("d") AS "Date" FROM
"qm"."DeviceIPRangeType"
CROSS JOIN generate_series($4::timestamptz, $5::timestamptz, $6) as "d"
WHERE "Id" > 2 AND (0 = any($2::int8[]) OR "Number" = any($2::int8[])) AND "Id" IN (SELECT "Id" FROM data3_1)),
data5 AS (SELECT coalesce(c, 0) AS C, coalesce(avg, 0) AS Avg, coalesce("data2"."Date", "data3"."Date") AS date, "data2"."Name", "data3"."RangeTypeId" FROM
data2
FULL OUTER JOIN data3 ON ("data3"."BranchId" = "data2"."Id" AND "data3"."Date" = "data2"."Date"))
SELECT array_agg(coalesce(c, 0) ORDER BY coalesce("data5"."date", "data4"."Date")) AS "Count",
array_agg(coalesce(avg::double precision, 0::double precision) ORDER BY coalesce("data5"."date", "data4"."Date")) AS "Avg",
array_agg(coalesce("data5"."date", "data4"."Date") ORDER BY coalesce("data5"."date", "data4"."Date"))::timestamptz[] AS "Date",
coalesce("data5"."Name", "data4"."Name") AS "Name" FROM data5
FULL OUTER JOIN data4 ON ("data4"."Id" = "data5"."RangeTypeId" AND "data5"."date" = "data4"."Date")
WHERE coalesce("data5"."Name", "data4"."Name") IS NOT NULL
GROUP BY coalesce("data5"."Name", "data4"."Name")
ORDER BY coalesce("data5"."Name", "data4"."Name")`
default:
query = `WITH data AS (SELECT "IndicatorResultValue"."IndicatorId", "qm"."Session"."Blocked", "qm"."Result"."SessionId" AS "SessionId", "qm"."DeviceIPRangeType"."Number" AS "RangeType", "qm"."DeviceIPRangeType"."Id" AS "RangeTypeId", "qm"."BranchWorkPlace"."Id" AS "WorkPlaceId", "DeviceIPRange"."BranchId", date("Datetime") as "Date"
FROM qm."Result"
LEFT OUTER JOIN "qm"."Device" ON ("Result"."DeviceId" = "Device"."Id")
LEFT OUTER JOIN "qm"."BranchWorkPlace" ON ("BranchWorkPlace"."Id" = "Device"."WorkPlaceId")
LEFT OUTER JOIN "qm"."Session" ON ("Result"."SessionId" = "Session"."Id")
LEFT OUTER JOIN "qm"."IndicatorResultValue" ON ("Result"."IndicatorResultValueId" = "IndicatorResultValue"."Id")
...
Follow us!