Coverage for chalicelib/core/weekly

1import logging

3from chalicelib.utils import pg_client, helper, email_helper, smtp

4from chalicelib.utils.TimeUTC import TimeUTC

5from chalicelib.utils.helper import get_issue_title

7logger = logging.getLogger(__name__)

9LOWEST_BAR_VALUE = 3

12def get_config(user_id):

13 with pg_client.PostgresClient() as cur:

14 cur.execute(cur.mogrify("""\

15 SELECT users.weekly_report

16 FROM public.users

17 WHERE users.deleted_at ISNULL AND users.user_id=%(user_id)s

18 LIMIT 1;""", {"user_id": user_id}))

19 result = cur.fetchone()

20 return helper.dict_to_camel_case(result)

23def edit_config(user_id, weekly_report):

24 with pg_client.PostgresClient() as cur:

25 cur.execute(cur.mogrify("""\

26 UPDATE public.users

27 SET weekly_report= %(weekly_report)s

28 WHERE users.deleted_at ISNULL

29 AND users.user_id=%(user_id)s

30 RETURNING weekly_report;""", {"user_id": user_id, "weekly_report": weekly_report}))

31 result = cur.fetchone()

32 return helper.dict_to_camel_case(result)

35def cron():

36 if not smtp.has_smtp():

37 logger.info("!!! No SMTP configuration found, ignoring weekly report")

38 return

39 _now = TimeUTC.now()

40 with pg_client.PostgresClient(unlimited_query=True) as cur:

41 params = {"tomorrow": TimeUTC.midnight(delta_days=1),

42 "3_days_ago": TimeUTC.midnight(delta_days=-3),

43 "1_week_ago": TimeUTC.midnight(delta_days=-7),

44 "2_week_ago": TimeUTC.midnight(delta_days=-14),

45 "5_week_ago": TimeUTC.midnight(delta_days=-35)}

46 cur.execute(cur.mogrify("""\

47 SELECT project_id,

48 name AS project_name,

49 users.emails AS emails,

50 TO_CHAR(DATE_TRUNC('day', now()) - INTERVAL '1 week', 'Mon. DDth, YYYY') AS period_start,

51 TO_CHAR(DATE_TRUNC('day', now()), 'Mon. DDth, YYYY') AS period_end,

52 COALESCE(week_0_issues.count, 0) AS this_week_issues_count,

53 COALESCE(week_1_issues.count, 0) AS past_week_issues_count,

54 COALESCE(month_1_issues.count, 0) AS past_month_issues_count

55 FROM (SELECT project_id, name FROM public.projects WHERE projects.deleted_at ISNULL) AS projects

56 INNER JOIN LATERAL (

57 SELECT sessions.project_id

58 FROM public.sessions

59 WHERE sessions.project_id = projects.project_id

60 AND start_ts >= %(3_days_ago)s

61 AND start_ts < %(tomorrow)s

62 LIMIT 1) AS recently_active USING (project_id)

63 INNER JOIN LATERAL (

64 SELECT COALESCE(ARRAY_AGG(email), '{}') AS emails

65 FROM public.users

66 WHERE users.deleted_at ISNULL

67 AND users.weekly_report

68 ) AS users ON (TRUE)

69 LEFT JOIN LATERAL (

70 SELECT COUNT(1) AS count

71 FROM events_common.issues

72 INNER JOIN public.sessions USING (session_id)

73 WHERE sessions.project_id = projects.project_id

74 AND issues.timestamp >= %(1_week_ago)s

75 AND issues.timestamp < %(tomorrow)s

76 ) AS week_0_issues ON (TRUE)

77 LEFT JOIN LATERAL (

78 SELECT COUNT(1) AS count

79 FROM events_common.issues

80 INNER JOIN public.sessions USING (session_id)

81 WHERE sessions.project_id = projects.project_id

82 AND issues.timestamp <= %(1_week_ago)s

83 AND issues.timestamp >= %(2_week_ago)s

84 ) AS week_1_issues ON (TRUE)

85 LEFT JOIN LATERAL (

86 SELECT COUNT(1) AS count

87 FROM events_common.issues

88 INNER JOIN public.sessions USING (session_id)

89 WHERE sessions.project_id = projects.project_id

90 AND issues.timestamp <= %(1_week_ago)s

91 AND issues.timestamp >= %(5_week_ago)s

92 ) AS month_1_issues ON (TRUE);"""), params)

93 projects_data = cur.fetchall()

94 _now2 = TimeUTC.now()

95 logger.debug(f">> Weekly report query: {_now2 - _now} ms")

96 _now = _now2

97 emails_to_send = []

98 for p in projects_data:

99 params["project_id"] = p["project_id"]

100 logger.debug(f"checking {p['project_name']} : {p['project_id']}")

101 if len(p["emails"]) == 0 \

102 or p["this_week_issues_count"] + p["past_week_issues_count"] + p["past_month_issues_count"] == 0:

103 logger.debug('ignore')

104 continue

105 logger.debug("valid")

106 p["past_week_issues_evolution"] = helper.__decimal_limit(

107 helper.__progress(p["this_week_issues_count"], p["past_week_issues_count"]), 1)

108 p["past_month_issues_evolution"] = helper.__decimal_limit(

109 helper.__progress(p["this_week_issues_count"], p["past_month_issues_count"]), 1)

110 cur.execute(cur.mogrify("""

111 SELECT LEFT(TO_CHAR(timestamp_i, 'Dy'),1) AS day_short,

112 TO_CHAR(timestamp_i, 'Mon. DD, YYYY') AS day_long,

113 (

114 SELECT COUNT(*)

115 FROM events_common.issues INNER JOIN public.issues USING (issue_id)

116 WHERE project_id = %(project_id)s

117 AND timestamp >= (EXTRACT(EPOCH FROM timestamp_i) * 1000)::BIGINT

118 AND timestamp <= (EXTRACT(EPOCH FROM timestamp_i + INTERVAL '1 day') * 1000)::BIGINT

119 ) AS issues_count

120 FROM generate_series(

121 DATE_TRUNC('day', now()) - INTERVAL '7 days',

122 DATE_TRUNC('day', now()) - INTERVAL '1 day',

123 '1 day'::INTERVAL

124 ) AS timestamp_i

125 ORDER BY timestamp_i;""", params))

126 days_partition = cur.fetchall()

127 _now2 = TimeUTC.now()

128 logger.debug(f">> Weekly report s-query-1: {_now2 - _now} ms project_id: {p['project_id']}")

129 _now = _now2

130 max_days_partition = max(x['issues_count'] for x in days_partition)

131 for d in days_partition:

132 if max_days_partition <= 0:

133 d["value"] = LOWEST_BAR_VALUE

134 else:

135 d["value"] = d["issues_count"] * 100 / max_days_partition

136 d["value"] = d["value"] if d["value"] > LOWEST_BAR_VALUE else LOWEST_BAR_VALUE

137 cur.execute(cur.mogrify("""\

138 SELECT type, COUNT(*) AS count

139 FROM events_common.issues INNER JOIN public.issues USING (issue_id)

140 WHERE project_id = %(project_id)s

141 AND timestamp >= (EXTRACT(EPOCH FROM DATE_TRUNC('day', now()) - INTERVAL '7 days') * 1000)::BIGINT

142 GROUP BY type

143 ORDER BY count DESC, type

144 LIMIT 4;""", params))

145 issues_by_type = cur.fetchall()

146 _now2 = TimeUTC.now()

147 logger.debug(f">> Weekly report s-query-1: {_now2 - _now} ms project_id: {p['project_id']}")

148 _now = _now2

149 max_issues_by_type = sum(i["count"] for i in issues_by_type)

150 for i in issues_by_type:

151 i["type"] = get_issue_title(i["type"])

152 if max_issues_by_type <= 0:

153 i["value"] = LOWEST_BAR_VALUE

154 else:

155 i["value"] = i["count"] * 100 / max_issues_by_type

156 cur.execute(cur.mogrify("""\

157 SELECT TO_CHAR(timestamp_i, 'Dy') AS day_short,

158 TO_CHAR(timestamp_i, 'Mon. DD, YYYY') AS day_long,

159 COALESCE((SELECT JSONB_AGG(sub)

160 FROM (

161 SELECT type, COUNT(*) AS count

162 FROM events_common.issues

163 INNER JOIN public.issues USING (issue_id)

164 WHERE project_id = %(project_id)s

165 AND timestamp >= (EXTRACT(EPOCH FROM timestamp_i) * 1000)::BIGINT

166 AND timestamp <= (EXTRACT(EPOCH FROM timestamp_i + INTERVAL '1 day') * 1000)::BIGINT

167 GROUP BY type

168 ORDER BY count

169 ) AS sub), '[]'::JSONB) AS partition

170 FROM generate_series(

171 DATE_TRUNC('day', now()) - INTERVAL '7 days',

172 DATE_TRUNC('day', now()) - INTERVAL '1 day',

173 '1 day'::INTERVAL

174 ) AS timestamp_i

175 GROUP BY timestamp_i

176 ORDER BY timestamp_i;""", params))

177 issues_breakdown_by_day = cur.fetchall()

178 _now2 = TimeUTC.now()

179 logger.debug(f">> Weekly report s-query-1: {_now2 - _now} ms project_id: {p['project_id']}")

180 _now = _now2

181 for i in issues_breakdown_by_day:

182 i["sum"] = sum(x["count"] for x in i["partition"])

183 for j in i["partition"]:

184 j["type"] = get_issue_title(j["type"])

185 max_days_partition = max(i["sum"] for i in issues_breakdown_by_day)

186 for i in issues_breakdown_by_day:

187 for j in i["partition"]:

188 if max_days_partition <= 0:

189 j["value"] = LOWEST_BAR_VALUE

190 else:

191 j["value"] = j["count"] * 100 / max_days_partition

192 j["value"] = j["value"] if j["value"] > LOWEST_BAR_VALUE else LOWEST_BAR_VALUE

193 cur.execute(cur.mogrify("""

194 SELECT type,

195 COUNT(*) AS issue_count,

196 COUNT(DISTINCT session_id) AS sessions_count,

197 (SELECT COUNT(DISTINCT sessions.session_id)

198 FROM public.sessions

199 INNER JOIN events_common.issues AS sci USING (session_id)

200 INNER JOIN public.issues AS si USING (issue_id)

201 WHERE si.project_id = %(project_id)s

202 AND sessions.project_id = %(project_id)s

203 AND sessions.start_ts <= (EXTRACT(EPOCH FROM DATE_TRUNC('day', now()) - INTERVAL '1 week') * 1000)::BIGINT

204 AND sessions.start_ts >= (EXTRACT(EPOCH FROM DATE_TRUNC('day', now()) - INTERVAL '2 weeks') * 1000)::BIGINT

205 AND si.type = mi.type

206 AND sessions.duration IS NOT NULL

207 ) AS last_week_sessions_count,

208 (SELECT COUNT(DISTINCT sci.session_id)

209 FROM public.sessions

210 INNER JOIN events_common.issues AS sci USING (session_id)

211 INNER JOIN public.issues AS si USING (issue_id)

212 WHERE si.project_id = %(project_id)s

213 AND sessions.project_id = %(project_id)s

214 AND sessions.start_ts <= (EXTRACT(EPOCH FROM DATE_TRUNC('day', now()) - INTERVAL '1 week') * 1000)::BIGINT

215 AND sessions.start_ts >= (EXTRACT(EPOCH FROM DATE_TRUNC('day', now()) - INTERVAL '5 weeks') * 1000)::BIGINT

216 AND si.type = mi.type

217 AND sessions.duration IS NOT NULL

218 ) AS last_month_sessions_count

219 FROM events_common.issues

220 INNER JOIN public.issues AS mi USING (issue_id)

221 INNER JOIN public.sessions USING (session_id)

222 WHERE mi.project_id = %(project_id)s AND sessions.project_id = %(project_id)s AND sessions.duration IS NOT NULL

223 AND sessions.start_ts >= (EXTRACT(EPOCH FROM DATE_TRUNC('day', now()) - INTERVAL '1 week') * 1000)::BIGINT

224 GROUP BY type

225 ORDER BY issue_count DESC;""", params))

226 issues_breakdown_list = cur.fetchall()

227 _now2 = TimeUTC.now()

228 logger.debug(f">> Weekly report s-query-1: {_now2 - _now} ms project_id: {p['project_id']}")

229 _now = _now2

230 if len(issues_breakdown_list) > 4:

231 others = {"type": "Others",

232 "sessions_count": sum(i["sessions_count"] for i in issues_breakdown_list[4:]),

233 "issue_count": sum(i["issue_count"] for i in issues_breakdown_list[4:]),

234 "last_week_sessions_count": sum(

235 i["last_week_sessions_count"] for i in issues_breakdown_list[4:]),

236 "last_month_sessions_count": sum(

237 i["last_month_sessions_count"] for i in issues_breakdown_list[4:])}

238 issues_breakdown_list = issues_breakdown_list[:4]

239 issues_breakdown_list.append(others)

240 for i in issues_breakdown_list:

241 i["type"] = get_issue_title(i["type"])

242 i["last_week_sessions_evolution"] = helper.__decimal_limit(

243 helper.__progress(i["sessions_count"], i["last_week_sessions_count"]), 1)

244 i["last_month_sessions_evolution"] = helper.__decimal_limit(

245 helper.__progress(i["sessions_count"], i["last_month_sessions_count"]), 1)

246 i["sessions_count"] = f'{i["sessions_count"]:,}'

247 keep_types = [i["type"] for i in issues_breakdown_list]

248 for i in issues_breakdown_by_day:

249 keep = []

250 for j in i["partition"]:

251 if j["type"] in keep_types:

252 keep.append(j)

253 i["partition"] = keep

254 emails_to_send.append({"email": p.pop("emails"),

255 "data": {

256 **p,

257 "days_partition": days_partition,

258 "issues_by_type": issues_by_type,

259 "issues_breakdown_by_day": issues_breakdown_by_day,

260 "issues_breakdown_list": issues_breakdown_list

261 }})

262 logger.info(f">>> Sending weekly report to {len(emails_to_send)} email-group")

263 for e in emails_to_send:

264 email_helper.weekly_report2(recipients=e["email"], data=e["data"])

Coverage for chalicelib/core/weekly_report.py: 12%

101 statements