1. Список говнокодов пользователя g2347933

    Всего: 1

  2. Куча / Говнокод #18054

    +141

    1. 01
    2. 02
    3. 03
    4. 04
    5. 05
    6. 06
    7. 07
    8. 08
    9. 09
    10. 10
    11. 11
    12. 12
    13. 13
    14. 14
    15. 15
    16. 16
    17. 17
    18. 18
    19. 19
    20. 20
    21. 21
    22. 22
    23. 23
    24. 24
    25. 25
    26. 26
    27. 27
    28. 28
    29. 29
    30. 30
    31. 31
    32. 32
    33. 33
    34. 34
    35. 35
    36. 36
    37. 37
    38. 38
    39. 39
    40. 40
    41. 41
    42. 42
    43. 43
    44. 44
    45. 45
    46. 46
    47. 47
    48. 48
    49. 49
    50. 50
    51. 51
    52. 52
    53. 53
    54. 54
    55. 55
    56. 56
    57. 57
    58. 58
    59. 59
    60. 60
    61. 61
    62. 62
    63. 63
    64. 64
    65. 65
    66. 66
    67. 67
    68. 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")
	 		...

    Код для БД на go

    g2347933, 24 Апреля 2015

    Комментарии (2)