Commits

Nikolaj Sjujskij  committed 9288d66

Add `due_date`-less tickets closed on month being viewed to calendar

  • Participants
  • Parent commits 738282a

Comments (0)

Files changed (2)

 except ImportError:
     pass
 
-# query to fetch open tickets
-QUERY_OPEN = "SELECT id, owner, summary FROM ticket WHERE status!='closed'"
-# query to fetch month data
+# query to fetch month data (with DATE_FIELD set)
 QUERY_MONTH = """SELECT ticket.id, ticket.owner, ticket.summary,
                         ticket.status, ticket_custom.value AS due_date
                 FROM ticket INNER JOIN ticket_custom
                 WHERE ticket_custom.name='{0}' AND
                     ticket_custom.value ILIKE %s""".format(DATE_FIELD)
 
+# query to fetch tickets closed this month without DATE_FIELD set
+##TODO: strip reopened
+QUERY_UNSET_DATE = """SELECT ticket.id, ticket.owner, ticket.summary,
+                            ticket_change.time AS closed_at
+                        FROM ticket INNER JOIN ticket_change
+                            ON ticket.id = ticket_change.ticket
+                        INNER JOIN ticket_custom
+                            ON ticket.id = ticket_custom.ticket
+                        WHERE ticket_change.field='status' AND
+                            ticket_change.newvalue='closed' AND
+                            ticket_custom.name='{0}' AND
+                            ticket_custom.value='' AND
+                            ticket_change.time>=%s AND
+                            ticket_change.time<=%s""".format(DATE_FIELD)
+
+# query to fetch open tickets
+QUERY_OPEN = "SELECT id, owner, summary FROM ticket WHERE status!='closed'"
 # query to fetch tickets close not earlier than a week ago
 QUERY_CLSD_WEEK = """SELECT id, owner, summary FROM ticket WHERE status='closed'
                      AND changetime > %s"""
     del replace_dict
     monthstr = basedate.strftime("%%.%m.%Y")
 
+    # calculate timestamp boundaries of the month
+    clsd_after = calendar.timegm(
+                      (basedate.year, basedate.month, 1) + (0,) * 3) * (10**6)
+    clsd_before = calendar.timegm(
+                      (basedate.year, basedate.month + 1, 1) + (0,) * 3) * (10**6)
+
     month_data, month_data_raw, opened, closed = ({}, ) * 4
     for trac in TRACS:
         ## query Trac databases
                              dict2up=month_data_raw,
                              add_data={'trac': trac['name'],
                                        'base_url': trac['base_url']})
+        # query for due_date'less tickets closed on this month
+        cur.execute(QUERY_UNSET_DATE, (clsd_after, clsd_before))
+        for tckt in cur.fetchall():
+            # add ticket to corresponding list of owner's tickets
+            owner = tckt['owner']
+            # convert timestamp-like ticket.time to `due_date`
+            due_date = datetime.date.fromtimestamp(tckt['closed_at'] // (10**6))
+            due_date = due_date.strftime(DATEFORMAT)
+            user_tckt_list = month_data_raw.get(owner, [])
+            user_tckt_list.append({'id': tckt['id'],
+                                   'due_date': due_date,
+                                   'summary': tckt['summary'],
+                                   'status': 'closed',
+                                   'trac': trac['name'],
+                                   'base_url': trac['base_url']})
+            month_data_raw['owner'] = user_tckt_list
+
         # for open tickets
         cur.execute(QUERY_OPEN)
         opened = dictify(cur.fetchall(), 'owner',
                              dict2up=closed,
                              add_data={'trac': trac['name'],
                                        'base_url': trac['base_url']})
+
     # total dictification of month data
     for user, tickets in month_data_raw.items():
         month_data[user] = dictify(tickets, 'due_date')
         for due_date in month_data[user].keys():
             tickets = month_data[user].pop(due_date)
             month_data[user][due_date] = dictify(tickets, 'trac')
+
     # a wee bit of simple dictification for opened tickets
     for user, tickets in opened.items():
         opened[user] = dictify(tickets, 'trac')