Coverage for marshall_webapp/models/transients/models_transients_get.py : 57%
Hot-keys on this page
r m x p toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1#!/usr/local/bin/python
2# encoding: utf-8
3"""
4*model for transient data in the marshall database - use this code to grab the transient data*
6:Author:
7 David Young
8"""
9from future import standard_library
10standard_library.install_aliases()
11from builtins import zip
12from builtins import object
13import sys
14import os
15import collections
16from sqlalchemy.sql import text
17import urllib.request
18import urllib.parse
19import urllib.error
20from astrocalc.coords import unit_conversion
21from dryxPyramid.models.models_base import base_model
22import re
25class models_transients_get(base_model):
26 """
27 The worker class for the `models_transients_get` module
29 **Key Arguments**
31 - ``log`` -- logger
32 - ``request`` -- the pyramid request
33 - ``elementId`` -- the specific element id requests (or False)
35 """
37 def __init__(self, log, request, elementId=False, search=False, tcsCatalogueId=False):
38 super().__init__(log, request, elementId, search)
40 self.resourceName = "transients"
41 self.defaultQs = { # the query string defaults
42 "mwl": "inbox",
43 "format": "html_tickets",
44 "ticketLimit": 10,
45 "tableLimit": 100,
46 "pageStart": 0,
47 "sortBy": "dateAdded",
48 "sortDesc": False,
49 "filterBy1": "decDeg",
50 "filterValue1": 30,
51 "filterOp1": "<",
52 "filterBy2": False,
53 "filterValue2": False,
54 "filterOp2": "="
55 }
56 self.tcsCatalogueId = tcsCatalogueId
58 self._set_default_parameters()
59 self.transientData, self.matchedTransientBucketIds, self.totalTicketCount = self._get_transient_data_from_database()
61 log.debug(
62 "instansiating a new 'models_transients_get' object")
63 return None
65 def get(self):
66 """
67 *get the transientData object*
69 **Return**
71 - ``transientData``
73 """
74 self.log.debug('starting the ``get`` method')
76 self.transientAkas = self._get_associated_transient_aka()
77 self.transientLightcurveData = self._get_associated_lightcurve_data()
78 self.transientAtelMatches = self._get_associated_atel_data()
79 self.transients_comments = self._get_associated_comments()
80 self.transient_history = self._get_associated_transient_history()
81 self.transient_crossmatches = self._get_associated_transient_crossmatches()
83 self.log.debug('completed the ``get`` method')
85 if "format" in self.qs and (self.qs["format"] == "json" or self.qs["format"] == "csv" or self.qs["format"] == "plain_table"):
86 self._clean_data_for_plain_text_outputs()
87 return self.transientData
89 qs = self.qs
90 self.log.debug("""self.qs: `%(qs)s`""" % locals())
92 return self.qs, self.transientData, self.transientAkas, self.transientLightcurveData, self.transientAtelMatches, self.transients_comments, self.totalTicketCount, self.transient_history, self.transient_crossmatches
94 def _get_transient_data_from_database(
95 self):
96 """
97 *get the sqlquery based on the request object parameters*
99 **Return**
101 - ``objectData``
102 - ``matchedTransientBucketIds``
103 - ``totalTicketCount``
105 """
106 self.log.debug('starting the ``get_data_from_database`` method')
108 tcsCatalogueId = self.tcsCatalogueId
109 sqlWhereList = []
110 regex1 = re.compile(r'[^A-Za-z0-9]')
111 regex2 = re.compile(r'^(AT|SN)')
112 # SEARCH
113 if self.search and "q" in self.request.params:
114 searchString = self.request.params["q"]
115 searchString = regex1.sub('', searchString)
116 searchString = regex2.sub('^(AT|SN)', searchString)
117 self.log.debug("""searchString: `%(searchString)s`""" % locals())
119 sqlQuery = f"""
120 select DISTINCT transientBucketId from marshall_transient_akas where REGEXP_REPLACE(name,"[^A-Za-z0-9]","") REGEXP '{searchString}'
121 union
122 select DISTINCT transientBucketId from pesstoObjects where REGEXP_REPLACE(pi_name,"[^A-Za-z0-9]","") REGEXP '{searchString}'
123 """
124 rows = self.request.db.execute(
125 text(sqlQuery)).fetchall()
127 searchList = ""
128 for row in rows:
129 transientBucketId = row["transientBucketId"]
130 searchList = """%(searchList)s %(transientBucketId)s,""" % locals(
131 )
133 thisWhere = """t.transientBucketId in (%(searchList)s -99)""" % locals(
134 )
136 sqlWhereList.append(thisWhere)
137 thisPageName = searchString
138 self.log.debug("""searchList: `%(searchList)s`""" % locals())
140 # Single Element Id (i.e. transientBucketId)
141 if self.elementId:
142 thisTransientBucketId = self.elementId
143 thisWhere = """t.transientBucketId in (%(thisTransientBucketId)s)""" % locals(
144 )
145 sqlWhereList.append(thisWhere)
147 # MARSHALL WORKFLOW
148 if "mwl" in self.qs:
149 if self.qs["mwl"] == "allObsQueue":
150 thisWhere = """(marshallWorkflowLocation = "following" or marshallWorkflowLocation = "pending observation") """
151 elif self.qs["mwl"] == "all":
152 thisWhere = """1=1"""
153 else:
154 thisWhere = """marshallWorkflowLocation = "%(mwl)s" """ % self.qs
155 sqlWhereList.append(thisWhere)
157 # ALERT WORKFLOW
158 if "awl" in self.qs:
159 thisWhere = """alertWorkflowLocation = "%(awl)s" """ % self.qs
160 sqlWhereList.append(thisWhere)
162 # CLASSIFIED?
163 if "cf" in self.qs:
164 thisWhere = """classifiedFlag = "%(cf)s" """ % self.qs
165 sqlWhereList.append(thisWhere)
167 # SNOOZED?
168 if "snoozed" in self.qs:
169 thisWhere = """snoozed = "%(snoozed)s" """ % self.qs
170 sqlWhereList.append(thisWhere)
172 # FILTER?
173 if "filterBy1" in self.qs and self.qs['filterBy1'] and "filterValue1" in self.qs and self.qs['filterValue1'] and "filterOp1" in self.qs and self.qs['filterOp1']:
174 if self.qs['filterBy1'] in ("decDeg", "raDeg"):
175 thisWhere = """t.`%(filterBy1)s` %(filterOp1)s %(filterValue1)s """ % self.qs
176 else:
177 try:
178 self.qs["filterValue1"] = float(self.qs["filterValue1"])
179 thisWhere = """`%(filterBy1)s` %(filterOp1)s %(filterValue1)s """ % self.qs
180 except:
181 thisWhere = """`%(filterBy1)s` %(filterOp1)s "%(filterValue1)s" """ % self.qs
183 sqlWhereList.append(thisWhere)
185 if "filterBy2" in self.qs and self.qs['filterBy2'] and "filterValue2" in self.qs and self.qs['filterValue2'] and "filterOp2" in self.qs and self.qs['filterOp2']:
186 if self.qs['filterBy2'] in ("decDeg", "raDeg"):
187 thisWhere = """t.`%(filterBy2)s` %(filterOp2)s %(filterValue2)s """ % self.qs
188 else:
189 try:
190 self.qs["filterValue2"] = float(self.qs["filterValue2"])
191 thisWhere = """`%(filterBy2)s` %(filterOp2)s %(filterValue2)s """ % self.qs
192 except:
193 thisWhere = """`%(filterBy2)s` %(filterOp2)s "%(filterValue2)s" """ % self.qs
195 sqlWhereList.append(thisWhere)
197 if "phaseiiiCheck" in self.qs:
198 phaseiiiCheck = self.qs["phaseiiiCheck"]
199 if phaseiiiCheck == "null":
200 phaseiiiCheck = "is null"
201 else:
202 phaseiiiCheck = "= %(phaseiiiCheck)s" % locals()
203 thisWhere = """t.transientBucketId in (SELECT transientBucketId FROM phase_iii_transient_catalogue_ssdr3 p, sherlock_classifications s where s.transient_object_id=p.TransientBucketId and s.matchVerified %(phaseiiiCheck)s) """ % locals()
204 sqlWhereList.append(thisWhere)
206 # tcsCatalogueId?
207 if tcsCatalogueId:
208 thisWhere = """cm.catalogue_table_id = %(tcsCatalogueId)s """ % locals(
209 )
210 if "tcsRank" in self.qs:
211 rank = self.qs["tcsRank"]
212 thisWhere += """ and cm.rank=%(rank)s""" % locals()
213 sqlWhereList = []
214 sqlWhereList.append(thisWhere)
215 tcsCm = ", sherlock_crossmatches cm"
216 tec = "and t.transientBucketId = cm.transient_object_id"
217 sec = "and s.transientBucketId = cm.transient_object_id"
218 else:
219 tcsCm = ""
220 tec = ""
221 sec = ""
223 # COMBINE THE WHERE CLAUSES
224 queryWhere = ""
225 for i, v in enumerate(sqlWhereList[:-1]):
226 queryWhere = """%(queryWhere)s %(v)s and""" % locals()
227 if len(sqlWhereList):
228 finalWhere = sqlWhereList[-1]
229 queryWhere = """where %(queryWhere)s %(finalWhere)s""" % locals()
231 # Now build the SQL Query
232 tep = "and t.transientBucketId = p.transientBucketId"
233 sep = "and s.transientBucketId = p.transientBucketId"
235 orderBy = ""
236 if "sortBy" in self.qs and self.qs["sortBy"] is not False:
237 sortRev = 0
238 sortDirection = ""
239 if "dateAdded" in self.qs["sortBy"] or "earliestDetection" in self.qs["sortBy"] or "Comment" in self.qs["sortBy"] or "lastNonDetectionDate" in self.qs["sortBy"]:
240 sortRev = 1
242 if (self.qs["sortDesc"] == "True" or self.qs["sortDesc"] == True) and sortRev == 0:
243 sortDirection = "desc"
244 elif (self.qs["sortDesc"] != "True" and self.qs["sortDesc"] != True) and sortRev == 1:
245 sortDirection = "desc"
247 if self.qs["sortBy"] == "redshift":
248 sqlQuery = """
249 select s.transientBucketId from transientBucketSummaries s, pesstoObjects p %(tcsCm)s %(queryWhere)s %(sep)s %(sec)s order by s.best_redshift %(sortDirection)s
250 """ % locals()
251 elif self.qs["sortBy"] == "latestComment":
252 sqlQuery = """
253 select * from (select t.transientBucketId from pesstoObjects p, transientBucketSummaries t %(tcsCm)s %(queryWhere)s %(tec)s %(tep)s) as a LEFT OUTER JOIN (SELECT pesstoObjectsId, MAX(dateCreated) AS latestCommentDate FROM pesstoObjectsComments GROUP BY pesstoObjectsId) as b ON a.transientBucketId = b.pesstoObjectsId ORDER BY latestCommentDate %(sortDirection)s
254 """ % locals()
256 elif self.qs["sortBy"] == "pi_name":
257 # the ticket selection query
258 sortBy = self.qs["sortBy"]
259 sqlQuery = """
260 select t.transientBucketId from transientBucketSummaries t, pesstoObjects p %(tcsCm)s %(queryWhere)s %(tep)s %(tec)s order by case when p.%(sortBy)s is null then 1 else 0 end, p.%(sortBy)s %(sortDirection)s
261 """ % locals()
263 elif self.qs["sortBy"] == "observationPriority":
264 sortBy = self.qs["sortBy"]
265 sqlQuery = """
266 select t.transientBucketId from transientBucketSummaries t, pesstoObjects p %(tcsCm)s %(queryWhere)s %(tep)s %(tec)s order by p.%(sortBy)s %(sortDirection)s, case when t.dateAdded is null then 1 else 0 end, t.dateAdded desc
267 """ % locals()
269 else:
270 # the ticket selection query
271 sortBy = self.qs["sortBy"]
272 sqlQuery = """
273 select t.transientBucketId from transientBucketSummaries t, pesstoObjects p %(tcsCm)s %(queryWhere)s %(tep)s %(tec)s order by case when t.%(sortBy)s is null then 1 else 0 end, t.%(sortBy)s %(sortDirection)s
274 """ % locals()
275 else:
276 sqlQuery = """
277 select t.transientBucketId from transientBucket t, pesstoObjects p %(tcsCm)s %(queryWhere)s and replacedByRowId =0 %(tep)s %(tec)s
278 """ % locals()
280 # Add the limits and pagination to query
281 pageStart = self.qs["pageStart"]
282 limit = self.qs["limit"]
283 sqlQuery = """%(sqlQuery)s limit %(pageStart)s, %(limit)s""" % locals()
285 # grab the transientBucketIds
287 rows = self.request.db.execute(sqlQuery).fetchall()
288 self.log.debug("""{rows}""".format(**dict(globals(), **locals())))
289 # GET ORDERED LIST OF THE TRANSIENTBUCKETIDs
290 matchedTransientBucketIds = ""
291 for aRow in rows:
292 tbi = aRow["transientBucketId"]
293 matchedTransientBucketIds = """%(matchedTransientBucketIds)s %(tbi)s,""" % locals(
294 )
295 matchedTransientBucketIds = "%(matchedTransientBucketIds)s -99" % locals(
296 )
298 # select column names
299 selectColumns = ""
300 skipColumns = [
301 "lastNonDetectionDate", "classificationWRTMax", "classificationPhase"]
302 tables = {"transientBucketSummaries": "s",
303 "transientBucket": "t",
304 "pesstoObjects": "p",
305 "sherlock_classifications": "sc"}
307 thisSchema = self.request.registry.settings["database_schema"]
308 for k, v in list(tables.items()):
309 sqlQuery = """SELECT COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where table_name = "%(k)s" and TABLE_SCHEMA = "%(thisSchema)s" """ % locals(
310 )
311 rows = self.request.db.execute(sqlQuery).fetchall()
312 self.log.debug(
313 """rows: {rows}""".format(**dict(globals(), **locals())))
315 for row in rows:
316 if row["COLUMN_NAME"] not in skipColumns or k == "transientBucketSummaries":
317 skipColumns.append(row["COLUMN_NAME"])
318 columnName = row["COLUMN_NAME"]
319 selectColumns = """{selectColumns} {v}.{columnName},""".format(
320 **dict(globals(), **locals()))
322 selectColumns = selectColumns[:-1]
324 self.log.debug(
325 """selectColumns: {selectColumns}""".format(**dict(globals(), **locals())))
327 # grab the remaining data assocatied with the transientBucketIds
328 sqlQuery = """
329 select %(selectColumns)s from transientBucket t, transientBucketSummaries s, pesstoObjects p, sherlock_classifications sc where t.replacedByRowId = 0 and t.transientBucketId in (%(matchedTransientBucketIds)s) and t.masterIdFlag = 1 and t.transientBucketId = p.transientBucketId and p.transientBucketId=s.transientBucketId and t.transientBucketId = sc.transient_object_id order by FIELD(t.transientBucketId, %(matchedTransientBucketIds)s)
330 """ % locals()
331 tmpObjectData = self.request.db.execute(
332 text(sqlQuery)).fetchall()
334 objectData = []
335 objectData[:] = [dict(list(zip(list(row.keys()), row)))
336 for row in tmpObjectData]
337 # for row in objectData:
338 # row = dict(zip(row.keys(), row))
339 self.log.debug(
340 """{objectData}""".format(**dict(globals(), **locals())))
342 # get the total ticket count
343 totalTicketCount = self._get_total_ticket_count_for_list(
344 queryWhere=queryWhere)
346 self.log.debug('completed the ``get_data_from_database`` method')
347 return objectData, matchedTransientBucketIds, totalTicketCount
349 def _set_default_parameters(
350 self):
351 """
352 *set default parameters in the request object if they have not yet been set*
353 """
354 self.log.debug('starting the ``set_default_parameters`` method')
356 self.log.debug("""these are the current query string key/values: {self.qs}""".format(
357 **dict(globals(), **locals())))
359 # Assign the default values for the variables if they do not exist
360 # Set inbox as the default view
361 checkList = ["q", "mwl", "cf", "awl", "snoozed"]
362 setDefaults = True
363 for item in checkList:
364 if item in self.qs:
365 setDefaults = False
367 if setDefaults is True and self.search == False and self.elementId == False:
368 self.qs[u"mwl"] = u"inbox"
370 # set ticketView as the default data type
371 if "format" not in self.qs:
372 self.qs["format"] = self.defaultQs["format"]
374 # setup default pagination and limits
375 if "limit" not in self.qs:
376 if self.qs["format"] == "html_table":
377 self.qs["limit"] = self.defaultQs["tableLimit"]
378 elif self.qs["format"] == "html_tickets":
379 self.qs["limit"] = self.defaultQs["ticketLimit"]
380 else:
381 self.qs["limit"] = 20000
383 if "pageStart" not in self.qs:
384 self.qs["pageStart"] = self.defaultQs["pageStart"]
386 if "sortBy" not in self.qs:
387 if "mwl" in self.qs and self.qs["mwl"] in ["following", "pending observation", "allObsQueue"]:
388 self.qs["sortBy"] = "observationPriority"
389 self.qs["sortDesc"] = False
390 elif ("mwl" in self.qs and self.qs["mwl"] in ["inbox"]) or "snoozed" in self.qs:
391 self.qs["sortBy"] = "currentMagnitudeDate"
392 self.qs["sortDesc"] = True
393 else:
394 self.qs["sortBy"] = self.defaultQs["sortBy"]
395 self.qs["sortDesc"] = self.defaultQs["sortDesc"]
397 # ADD THE REST OF THE DEFAULTS TO THE QUERY STRING
398 for k, v in list(self.defaultQs.items()):
399 if k not in self.qs and 'q' not in self.qs and not self.elementId:
400 if "awl" in self.qs and k == "mwl":
401 continue
402 self.qs[k] = v
404 self.qs["filterText1"] = ""
405 if "filterBy1" in self.qs and self.qs["filterBy1"] and (self.qs["filterBy1"].lower() == "false" or "null" in self.qs["filterBy1"].lower()):
406 self.qs["filterBy1"] = False
407 if "filterBy1" in self.qs and self.qs["filterBy1"]:
408 if "filterOp1" not in self.qs:
409 self.qs["filterOp1"] = self.defaultQs["filterOp1"]
410 if self.qs["filterOp1"].lower() == "eq":
411 self.qs["filterOp1"] = "="
412 elif self.qs["filterOp1"].lower() == "lt":
413 self.qs["filterOp1"] = "<"
414 elif self.qs["filterOp1"].lower() == "gt":
415 self.qs["filterOp1"] = ">"
416 elif self.qs["filterOp1"].lower() == "neq":
417 self.qs["filterOp1"] = "!="
419 self.qs[
420 "filterText1"] = "with <strong>%(filterBy1)s %(filterOp1)s %(filterValue1)s</strong> " % self.qs
422 self.qs["filterText2"] = ""
423 if "filterBy2" in self.qs and self.qs["filterBy2"] and self.qs["filterBy2"].lower() == "false":
424 self.qs["filterBy2"] = False
425 if "filterBy2" in self.qs and self.qs["filterBy2"]:
426 if "filterOp2" not in self.qs:
427 self.qs["filterOp2"] = self.defaultQs["filterOp2"]
428 if self.qs["filterOp2"].lower() == "eq":
429 self.qs["filterOp2"] = "="
430 elif self.qs["filterOp2"].lower() == "lt":
431 self.qs["filterOp2"] = "<"
432 elif self.qs["filterOp2"].lower() == "gt":
433 self.qs["filterOp2"] = ">"
434 elif self.qs["filterOp2"].lower() == "neq":
435 self.qs["filterOp2"] = "!="
437 self.qs[
438 "filterText2"] = "with <strong>%(filterBy2)s %(filterOp2)s %(filterValue2)s</strong> " % self.qs
439 self.qs["filterText2"] = self.qs["filterText2"].replace(
440 "sherlockClassification", "contextual classification")
442 self.log.debug("""these are the new query string key/values: {self.qs}""".format(
443 **dict(globals(), **locals())))
445 self.log.debug('completed the ``set_default_parameters`` method')
446 return None
448 def _get_associated_transient_aka(
449 self):
450 """
451 *get associated aka names for the trasnsients*
453 **Return**
455 - ``objectAkas`` -- the akas for the objects found
457 """
458 self.log.debug('starting the ``_get_associated_transient_aka`` method')
459 from marshall_webapp.models.transients_akas import models_transients_akas_get
460 transients_akas = models_transients_akas_get(
461 log=self.log,
462 request=self.request,
463 elementId=self.matchedTransientBucketIds
464 )
465 akas = transients_akas.get()
467 self.log.debug(
468 'completed the ``_get_associated_transient_aka`` method')
469 return akas
471 def _get_associated_lightcurve_data(
472 self):
473 """
474 *get associated lightcurve data for the matched transients*
476 **Return**
478 - ``lightCurveData`` -- the found objects' lightcurve data
480 """
481 self.log.debug(
482 'completed the ````_get_associated_lightcurve_data`` method')
484 matchedTransientBucketIds = self.matchedTransientBucketIds
486 sqlQuery = """
487 select transientBucketId, magnitude, filter, survey, surveyObjectUrl, observationDate from transientBucket where replacedByRowId = 0 and transientBucketId in (%(matchedTransientBucketIds)s) and observationDate is not null and observationDate != 0000-00-00 and magnitude is not null and magnitude < 50 and limitingMag = 0 order by observationDate desc;
488 """ % locals()
489 lightCurveDataTmp = self.request.db.execute(sqlQuery).fetchall()
490 lightCurveData = []
491 lightCurveData[:] = [dict(list(zip(list(row.keys()), row)))
492 for row in lightCurveDataTmp]
494 self.log.debug(
495 'completed the ``_get_associated_lightcurve_data`` method')
496 return lightCurveData
498 def _get_associated_atel_data(
499 self):
500 """
501 *get associated atel data for the matched transients*
503 **Return**
505 - ``transientAtelMatches`` -- the matched atels fot the transients
507 """
508 self.log.debug('starting the ``_get_associated_atel_data`` method')
510 matchedTransientBucketIds = self.matchedTransientBucketIds
512 sqlQuery = """
513 select distinct transientBucketId, name, surveyObjectUrl from transientBucket where replacedByRowId = 0 and transientBucketId in (%(matchedTransientBucketIds)s) and name like "%%atel_%%"
514 """ % locals()
515 transientAtelMatchesTmp = self.request.db.execute(sqlQuery).fetchall()
516 transientAtelMatches = []
517 transientAtelMatches[:] = [
518 dict(list(zip(list(row.keys()), row))) for row in transientAtelMatchesTmp]
520 self.log.debug('completed the ``_get_associated_atel_data`` method')
521 return transientAtelMatches
523 def _get_associated_comments(
524 self):
525 """
526 *get associated comments for the transients*
528 **Return**
530 - ``objectComments`` -- object comments
532 """
533 self.log.debug('starting the ``_get_associated_comments`` method')
535 matchedTransientBucketIds = self.matchedTransientBucketIds
537 sqlQuery = """
538 select * from pesstoObjectsComments where pesstoObjectsID in (%(matchedTransientBucketIds)s) order by dateCreated desc
539 """ % locals()
540 objectCommentsTmp = self.request.db.execute(sqlQuery).fetchall()
541 objectComments = []
542 objectComments[:] = [dict(list(zip(list(row.keys()), row)))
543 for row in objectCommentsTmp]
545 self.log.debug('completed the ``_get_associated_comments`` method')
546 return objectComments
548 def _get_total_ticket_count_for_list(
549 self,
550 queryWhere):
551 """
552 *get total ticket count for list*
554 **Key Arguments**
556 - ``queryWhere`` -- the where segment of the ticket list sqlQuery string
559 **Return**
561 - ``totalTickets`` -- total number of object in list
563 """
564 self.log.debug(
565 'completed the ````_get_total_ticket_count_for_list`` method')
567 tcsCatalogueId = self.tcsCatalogueId
568 if self.search:
569 sqlQuery = """
570 select count(*) from pesstoObjects p, transientBucketSummaries t %(queryWhere)s and t.transientBucketId = p.transientBucketId;
571 """ % locals()
572 totalTicketsTmp = self.request.db.execute(sqlQuery).fetchall()
573 totalTickets = []
574 totalTickets[:] = [dict(list(zip(list(row.keys()), row)))
575 for row in totalTicketsTmp]
576 totalTickets = totalTickets[0]["count(*)"]
577 elif self.elementId:
578 totalTickets = 1
579 elif tcsCatalogueId:
580 if "tcsRank" in self.qs:
581 sqlQuery = """
582 select top_ranked_transient_associations as count from tcs_stats_catalogues where table_id = %(tcsCatalogueId)s;
583 """ % locals()
584 else:
585 sqlQuery = """
586 select all_transient_associations as count from tcs_stats_catalogues where table_id = %(tcsCatalogueId)s;
587 """ % locals()
588 ticketCountRowsTmp = self.request.db.execute(sqlQuery).fetchall()
589 ticketCountRows = []
590 ticketCountRows[:] = [dict(list(zip(list(row.keys()), row)))
591 for row in ticketCountRowsTmp]
592 totalTickets = 0
593 for row in ticketCountRows:
594 totalTickets += row["count"]
595 elif 'filterBy1' in self.qs or 'filterBy2' in self.qs:
596 tcsCm = ", sherlock_crossmatches cm"
597 tec = "and t.transientBucketId = cm.transient_object_id"
598 sec = "and s.transientBucketId = cm.transient_object_id"
599 tep = "and t.transientBucketId = p.transientBucketId"
600 sep = "and s.transientBucketId = p.transientBucketId"
602 sqlQuery = """
603 select count(*) as count from transientBucketSummaries t, pesstoObjects p %(queryWhere)s %(tep)s
604 """ % locals()
606 ticketCountRowsTmp = self.request.db.execute(sqlQuery).fetchall()
607 ticketCountRows = []
608 ticketCountRows[:] = [dict(list(zip(list(row.keys()), row)))
609 for row in ticketCountRowsTmp]
610 totalTickets = 0
611 for row in ticketCountRows:
612 totalTickets += row["count"]
613 elif 'phase_iii_transient_catalogue_ssdr3' in queryWhere:
614 sqlQuery = """
615 select count(*) as count FROM phase_iii_transient_catalogue_ssdr3 p, sherlock_classifications s where s.transient_object_id=p.TransientBucketId and s.matchVerified is null
616 """ % locals()
617 ticketCountRowsTmp = self.request.db.execute(sqlQuery).fetchall()
618 ticketCountRows = []
619 ticketCountRows[:] = [dict(list(zip(list(row.keys()), row)))
620 for row in ticketCountRowsTmp]
621 totalTickets = 0
622 for row in ticketCountRows:
623 totalTickets += row["count"]
624 else:
625 ticketCountWhere = queryWhere.replace("marshallWorkflowLocation", "listName").replace(
626 "alertWorkflowLocation", "listName").replace('classifiedFlag = "1"', 'listName="classified"').replace('snoozed = "1"', 'listName="snoozed"')
627 sqlQuery = """
628 select count from meta_workflow_lists_counts %(ticketCountWhere)s;
629 """ % locals()
630 ticketCountRowsTmp = self.request.db.execute(sqlQuery).fetchall()
631 ticketCountRows = []
632 ticketCountRows[:] = [dict(list(zip(list(row.keys()), row)))
633 for row in ticketCountRowsTmp]
634 totalTickets = 0
635 for row in ticketCountRows:
636 totalTickets += row["count"]
638 self.log.debug(
639 'completed the ``_get_total_ticket_count_for_list`` method')
640 return totalTickets
642 def _clean_data_for_plain_text_outputs(
643 self):
644 """
645 *clean data for plain text outputs*
647 **Return**
649 - None
651 """
652 self.log.debug(
653 'completed the ````_clean_data_for_plain_text_outputs`` method')
655 # ASTROCALC UNIT CONVERTER OBJECT
656 converter = unit_conversion(
657 log=self.log
658 )
660 # assoicate the correct column name to mysql database column name
661 tmpDict = {}
662 tableColumnNames = collections.OrderedDict(
663 sorted(tmpDict.items()))
664 tableColumnNames["masterName"] = "name"
665 tableColumnNames["observationPriority"] = "priority"
666 tableColumnNames["raDeg"] = "ra"
667 tableColumnNames["decDeg"] = "dec"
668 tableColumnNames["recentClassification"] = "spectral class"
669 tableColumnNames["classificationPhase"] = "classification phase"
670 tableColumnNames["classificationWRTMax"] = "classificationWRTMax"
671 tableColumnNames["classificationDate"] = "classification date"
672 tableColumnNames["currentMagnitude"] = "latest mag"
673 tableColumnNames["absolutePeakMagnitude"] = "abs peak mag"
674 tableColumnNames["best_redshift"] = "z"
675 tableColumnNames["distanceMpc"] = "mpc"
676 tableColumnNames["earliestDetection"] = "discovery date"
677 tableColumnNames["lastNonDetectionDate"] = "last non-detection date"
678 tableColumnNames["dateAdded"] = "added to marshall"
679 tableColumnNames["pi_name"] = "PI"
680 tableColumnNames["pi_email"] = "pi email"
682 # a list of names for table and csv views
683 tableColumns = [
684 "masterName",
685 "observationPriority",
686 "raDeg",
687 "decDeg",
688 "recentClassification",
689 "classificationPhase",
690 "classificationWRTMax",
691 "classificationDate",
692 "currentMagnitude",
693 "absolutePeakMagnitude",
694 "best_redshift",
695 "distanceMpc",
696 "earliestDetection",
697 "lastNonDetectionDate",
698 "dateAdded",
699 "pi_name"
700 ]
702 # convert priorities to words
703 for obj in self.transientData:
705 for item in self.transientAkas:
706 if item["transientBucketId"] == obj["transientBucketId"]:
707 obj["masterName"] = item["name"]
708 break
710 if "marshallWorkflowLocation" in obj:
711 if obj["marshallWorkflowLocation"] == "following":
712 for n, w, c in zip([1, 2, 3, 4], ["CRITICAL", "IMPORTANT", "USEFUL", "NONE"], ["green", "yellow", "red", "blue"]):
713 if obj["observationPriority"] == n:
714 obj["observationPriority"] = w
715 break
716 elif obj["marshallWorkflowLocation"] == "pending observation":
717 for n, w, c in zip([1, 2, 3], ["HIGH", "MEDIUM", "LOW"], ["green", "yellow", "red"]):
718 if obj["observationPriority"] == n:
719 obj["observationPriority"] = w
720 break
722 newTransientData = []
723 for oldRow in self.transientData:
724 tmpRow = {}
725 newRow = collections.OrderedDict(sorted(tmpRow.items()))
727 for oldName, newName in list(tableColumnNames.items()):
729 newRow[newName] = oldRow[oldName]
730 if "decdeg" in oldName.lower():
732 raSex = converter.ra_decimal_to_sexegesimal(
733 ra=float(oldRow["raDeg"]),
734 delimiter=":"
735 )
736 newRow["ra (sex)"] = raSex
737 decSex = converter.dec_decimal_to_sexegesimal(
738 dec=float(oldRow["decDeg"]),
739 delimiter=":"
740 )
741 newRow["dec (sex)"] = decSex
742 newTransientData.append(newRow)
744 self.transientData = newTransientData
746 self.log.debug(
747 'completed the ``_clean_data_for_plain_text_outputs`` method')
748 return None
750 def _get_associated_transient_history(
751 self):
752 """
753 *get associated transient history*
754 """
755 self.log.debug(
756 'completed the ````_get_associated_transient_history`` method')
758 matchedTransientBucketIds = self.matchedTransientBucketIds
760 sqlQuery = """
761 select * from transients_history_logs where transientBucketId in (%(matchedTransientBucketIds)s) order by dateCreated desc
762 """ % locals()
764 objectHistoryTmp = self.request.db.execute(sqlQuery).fetchall()
765 objectHistory = []
766 objectHistory[:] = [dict(list(zip(list(row.keys()), row)))
767 for row in objectHistoryTmp]
769 from operator import itemgetter
770 objectHistory = list(objectHistory)
771 objectHistory = sorted(
772 objectHistory, key=itemgetter('dateCreated'), reverse=False)
774 self.log.debug(
775 'completed the ``_get_associated_transient_history`` method')
776 return objectHistory
778 def _get_associated_transient_crossmatches(
779 self):
780 """
781 *get associated transient crossmatches*
782 """
783 self.log.debug(
784 'completed the ````_get_associated_transient_crossmatches`` method')
786 from astrocalc.coords import unit_conversion
787 # ASTROCALC UNIT CONVERTER OBJECT
788 converter = unit_conversion(
789 log=self.log
790 )
792 matchedTransientBucketIds = self.matchedTransientBucketIds
794 sqlQuery = """
795 select *, t.raDeg, t.decDeg from sherlock_crossmatches t, transientBucket b where b.replacedByRowId = 0 and b.transientBucketId in (%(matchedTransientBucketIds)s) and b.transientBucketId = t.transient_object_id and b.masterIDFlag = 1 and rank is not null order by rank
796 """ % locals()
798 crossmatchesTmp = self.request.db.execute(sqlQuery).fetchall()
799 crossmatches = []
800 crossmatches[:] = [dict(list(zip(list(row.keys()), row)))
801 for row in crossmatchesTmp]
803 from operator import itemgetter
804 crossmatches = list(crossmatches)
805 crossmatches = sorted(
806 crossmatches, key=itemgetter('rank'), reverse=False)
808 for c in crossmatches:
809 c["object_link"] = None
810 objectName = urllib.parse.quote(c["catalogue_object_id"])
811 if "ned" in c["catalogue_table_name"].lower():
812 c[
813 "object_link"] = "https://ned.ipac.caltech.edu/cgi-bin/objsearch?objname=%(objectName)s&extend=no&hconst=73&omegam=0.27&omegav=0.73&corr_z=1&out_csys=Equatorial&out_equinox=J2000.0&obj_sort=RA+or+Longitude&of=pre_text&zv_breaker=30000.0&list_limit=5&img_stamp=YES" % locals()
814 elif "sdss" in c["catalogue_table_name"].lower():
815 c[
816 "object_link"] = "http://skyserver.sdss.org/dr12/en/tools/explore/Summary.aspx?id=%(objectName)s" % locals()
817 ra = converter.ra_decimal_to_sexegesimal(
818 ra=c["raDeg"],
819 delimiter=""
820 )
821 dec = converter.dec_decimal_to_sexegesimal(
822 dec=c["decDeg"],
823 delimiter=""
824 )
825 c["catalogue_object_id"] = "SDSS J" + ra[0:9] + dec[0:9]
826 objectName = urllib.parse.quote(c["catalogue_object_id"])
827 elif "milliquas" in c["catalogue_table_name"].lower():
828 c[
829 "object_link"] = "https://heasarc.gsfc.nasa.gov/db-perl/W3Browse/w3query.pl?bparam_name=%(objectName)s&navtrail=%%3Ca+class%%3D%%27navpast%%27+href%%3D%%27https%%3A%%2F%%2Fheasarc.gsfc.nasa.gov%%2FW3Browse%%2Fall%%2Fmilliquas.html%%27%%3E+Choose+Tables%%3C%%2Fa%%3E+%%3E+%%3Ca+class%%3D%%27navpast%%27+href%%3D%%27%%2Fcgi-bin%%2FW3Browse%%2Fw3table.pl%%3FREAL_REMOTE_HOST%%3D143.117.37.81%%26tablehead%%3Dname%%253Dmilliquas%%26Action%%3DMore%%2BOptions%%26REAL_REMOTE_HOST%%3D143%%252E117%%252E37%%252E81%%26Equinox%%3D2000%%26Action%%3DMore%%2BOptions%%26sortby%%3Dpriority%%26ResultMax%%3D1000%%26maxpriority%%3D99%%26Coordinates%%3DEquatorial%%26tablehead%%3Dname%%253Dmilliquas%%26Action%%3DParameter%%2BSearch%%27%%3EParameter+Search%%3C%%2Fa%%3E&popupFrom=Query+Results&tablehead=name%%3Dheasarc_milliquas%%26description%%3DMillion+Quasars+Catalog+%%28MILLIQUAS%%29%%2C+Version+4.5+%%2810+May+2015%%29%%26url%%3Dhttp%%3A%%2F%%2Fheasarc.gsfc.nasa.gov%%2FW3Browse%%2Fgalaxy-catalog%%2Fmilliquas.html%%26archive%%3DN%%26radius%%3D1%%26mission%%3DGALAXY+CATALOG%%26priority%%3D5%%26tabletype%%3DObject&dummy=Examples+of+query+constraints%%3A&varon=name&bparam_name%%3A%%3Aunit=+&bparam_name%%3A%%3Aformat=char25&varon=ra&bparam_ra=&bparam_ra%%3A%%3Aunit=degree&bparam_ra%%3A%%3Aformat=float8%%3A.5f&varon=dec&bparam_dec=&bparam_dec%%3A%%3Aunit=degree&bparam_dec%%3A%%3Aformat=float8%%3A.5f&varon=bmag&bparam_bmag=&bparam_bmag%%3A%%3Aunit=mag&bparam_bmag%%3A%%3Aformat=float8%%3A4.1f&varon=rmag&bparam_rmag=&bparam_rmag%%3A%%3Aunit=mag&bparam_rmag%%3A%%3Aformat=float8%%3A4.1f&varon=redshift&bparam_redshift=&bparam_redshift%%3A%%3Aunit=+&bparam_redshift%%3A%%3Aformat=float8%%3A6.3f&varon=radio_name&bparam_radio_name=&bparam_radio_name%%3A%%3Aunit=+&bparam_radio_name%%3A%%3Aformat=char22&varon=xray_name&bparam_xray_name=&bparam_xray_name%%3A%%3Aunit=+&bparam_xray_name%%3A%%3Aformat=char22&bparam_lii=&bparam_lii%%3A%%3Aunit=degree&bparam_lii%%3A%%3Aformat=float8%%3A.5f&bparam_bii=&bparam_bii%%3A%%3Aunit=degree&bparam_bii%%3A%%3Aformat=float8%%3A.5f&bparam_broad_type=&bparam_broad_type%%3A%%3Aunit=+&bparam_broad_type%%3A%%3Aformat=char4&bparam_optical_flag=&bparam_optical_flag%%3A%%3Aunit=+&bparam_optical_flag%%3A%%3Aformat=char3&bparam_red_psf_flag=&bparam_red_psf_flag%%3A%%3Aunit=+&bparam_red_psf_flag%%3A%%3Aformat=char1&bparam_blue_psf_flag=&bparam_blue_psf_flag%%3A%%3Aunit=+&bparam_blue_psf_flag%%3A%%3Aformat=char1&bparam_ref_name=&bparam_ref_name%%3A%%3Aunit=+&bparam_ref_name%%3A%%3Aformat=char6&bparam_ref_redshift=&bparam_ref_redshift%%3A%%3Aunit=+&bparam_ref_redshift%%3A%%3Aformat=char6&bparam_qso_prob=&bparam_qso_prob%%3A%%3Aunit=percent&bparam_qso_prob%%3A%%3Aformat=int2%%3A3d&bparam_alt_name_1=&bparam_alt_name_1%%3A%%3Aunit=+&bparam_alt_name_1%%3A%%3Aformat=char22&bparam_alt_name_2=&bparam_alt_name_2%%3A%%3Aunit=+&bparam_alt_name_2%%3A%%3Aformat=char22&Entry=&Coordinates=J2000&Radius=Default&Radius_unit=arcsec&NR=CheckCaches%%2FGRB%%2FSIMBAD%%2FNED&Time=&ResultMax=10&displaymode=Display&Action=Start+Search&table=heasarc_milliquas" % locals()
830 elif ("ps1" not in c["catalogue_table_name"].lower()) and ("ritter" not in c["catalogue_table_name"].lower()) and ("down" not in c["catalogue_table_name"].lower()) and ("guide_star" not in c["catalogue_table_name"].lower()) and ("kepler" not in c["catalogue_table_name"].lower()):
831 c[
832 "object_link"] = "http://simbad.u-strasbg.fr/simbad/sim-id?Ident=%(objectName)s&NbIdent=1&Radius=2&Radius.unit=arcmin&submit=submit+id" % locals()
834 c["best_mag"] = None
835 c["best_mag_error"] = None
836 c["best_mag_filter"] = None
837 filters = ["R", "V", "B", "I", "J", "G", "H", "K", "U",
838 "_r", "_g", "_i", "_g", "_z", "_y", "_u", "unkMag"]
839 for f in filters:
840 if c[f] and not c["best_mag"]:
841 c["best_mag"] = c[f]
842 c["best_mag_error"] = c[f + "Err"]
843 c["best_mag_filter"] = f.replace(
844 "_", "").replace("Mag", "")
846 self.log.debug(
847 'completed the ``_get_associated_transient_crossmatches`` method')
848 return crossmatches
850 # xt-class-method