1. Куча / Говнокод #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) RSS

    • ava 1024-- 24.04.2015 20:01 # 0

      SEO-комментарии vs минусаторы.
      Кстати, стоководам рекомендую пройтись итеративно по недавним говнокодам.
      Ответить

    • ava 105_306330_ru 24.08.2021 20:37 # 0

      Нет, нас обоих за уши нельзя было оторвать от пирожков из картошки с зелёным луком, да ещё и таких маленьких и таких вкусных, обжаренных на растопленном сале со скворчащими шкварками! Повар запивал их горячим чаем, а я холодным молоком. Скукожив хитрюще-умное лицо, создатель вкуснятины на полном серьёзе спросил:
      Ответить

    Добавить комментарий