def referred_to(self, request): dateFrom, dateTo, resort = get_param(request) user_role = get_roleid_user(resort, request.user) if user_role == 1: return Response( { _("detail"): _("You do not have permission to view analytics") }, status=403) with connection.cursor() as cursor: cursor.execute( """SELECT incident_data ->>'field_52d48077a16be' AS referred_to , COUNT(incident_data->>'field_52d48077a16be') AS num, ROUND((COUNT(incident_data->>'field_52d48077a16be')*100.0)/(SELECT CASE COUNT(*) WHEN 0 THEN 1 ELSE COUNT(*) END AS total FROM incidents_incident WHERE resort_id = %s AND CAST(incidents_incident.incident_data->>'field_52d48077a16be' AS integer)>0 AND dt_created >= %s AND dt_created <= %s AND incident_status_id <> 9)) AS percent, (SELECT COUNT(*) AS total FROM incidents_incident WHERE resort_id = %s AND CAST(incident_data->>'field_52d48077a16be' AS integer) > 0 AND dt_created >= %s AND dt_created <= %s AND incident_status_id <> 9) FROM incidents_incident INNER JOIN custom_user_users ON custom_user_users.user_pk = incidents_incident.assigned_to_id WHERE resort_id = %s AND custom_user_users.user_connected = %s AND CAST(incident_data->>'field_52d48077a16be' AS integer) > 0 AND dt_created IS NOT NULL AND dt_created >= %s AND dt_created <= %s AND incident_status_id <> 9 GROUP BY 1;""", [ resort.resort_pk, dateFrom, dateTo, resort.resort_pk, dateFrom, dateTo, resort.resort_pk, request.user.user_connected, dateFrom, dateTo ]) data = dictfetchall(cursor) return Response({'success': True, 'data': data}, status=200)
def age(self, request): dateFrom, dateTo, resort = get_param(request) user_role = get_roleid_user(resort, request.user) if user_role == 1: return Response( { _("detail"): _("You do not have permission to view analytics") }, status=403) data_key = get_data_key(resort) with connection.cursor() as cursor: cursor.execute("""SELECT SUM(case when age_years>=0 and age_years <= 10 then 1 else 0 end) AS G0_10, SUM(case when age_years>=11 and age_years <= 15 then 1 else 0 end) AS G11_15, SUM(case when age_years>=16 and age_years <= 18 then 1 else 0 end) AS G16_18, SUM(case when age_years>=19 and age_years <= 21 then 1 else 0 end) AS G19_21, SUM(case when age_years>=22 and age_years <= 30 then 1 else 0 end) AS G22_30, SUM(case when age_years>=31 and age_years <= 100 then 1 else 0 end) AS G31_ FROM incidents_incident INNER JOIN custom_user_users ON custom_user_users.user_pk = incidents_incident.assigned_to_id, incidents_patients, date_part('year',age(to_timestamp(pgp_sym_decrypt(incidents_patients.dob, '%s'::TEXT),'YYYYMMDD'))) as age_years WHERE incidents_incident.incident_pk = incidents_patients.incident_id AND incidents_incident.resort_id = %d AND custom_user_users.user_connected = %d AND incidents_incident.dt_created IS NOT NULL AND incidents_incident.dt_created >= '%s' AND incidents_incident.dt_created <= '%s' AND incidents_incident.incident_status_id <> 9;""" % (data_key, resort.resort_pk, request.user.user_connected, dateFrom, dateTo)) data = dictfetchall(cursor) return Response({'success': True, 'data': data[0]}, status=200)
def injury_types(self, request): dateFrom, dateTo, resort = get_param(request) user_role = get_roleid_user(resort, request.user) if user_role == 1: return Response( { _("detail"): _("You do not have permission to view analytics") }, status=403) with connection.cursor() as cursor: cursor.execute("""SELECT injury_types->>'injury_type' as injury, COUNT(injury_types->'injury_type') as num FROM incidents_incident INNER JOIN custom_user_users ON custom_user_users.user_pk = incidents_incident.assigned_to_id, json_array_elements(incidents_incident.incident_data -> 'field_52d4798f6d227') as injury_types WHERE resort_id = %d AND custom_user_users.user_connected = %d AND dt_Created IS NOT NULL AND dt_Created >= '%s' AND dt_Created <= '%s' AND incidents_incident.incident_status_id <> 9 GROUP BY injury_types->>'injury_type';""" % (resort.resort_pk, request.user.user_connected, dateFrom, dateTo)) data = dictfetchall(cursor) return Response({'success': True, 'data': data}, status=200)
def alcohol(self, request): dateFrom, dateTo, resort = get_param(request) user_role = get_roleid_user(resort, request.user) if user_role == 1: return Response( { _("detail"): _("You do not have permission to view analytics") }, status=403) with connection.cursor() as cursor: cursor.execute("""SELECT extract(year from dt_created) as year, extract(month from dt_created) as month, SUM(case when incidents_incident.incident_data->>'field_52ca437b62b9c' = 'yes' then 1 else 0 end) as drugs, SUM(case when incidents_incident.incident_data->>'field_52ca3fcc59d29' = 'yes' then 1 else 0 end) as alcohol FROM incidents_incident INNER JOIN custom_user_users ON custom_user_users.user_pk = incidents_incident.assigned_to_id WHERE resort_id = %d AND custom_user_users.user_connected = %d AND dt_Created IS NOT NULL AND dt_created >= '%s' AND dt_created <= '%s' AND incidents_incident.incident_status_id <> 9 GROUP BY 1,2 ORDER BY 1,2;""" % (resort.resort_pk, request.user.user_connected, dateFrom, dateTo)) data = dictfetchall(cursor) return Response({'success': True, 'data': data}, status=200)
def get_report(report_id): with connection.cursor() as cursor: cursor.execute( """SELECT report_id, report_config FROM reports_report WHERE report_id = %s;""", [report_id]) data = dictfetchall(cursor) return data[0]
def list_report(user): resort = get_resort_for_user(user) with connection.cursor() as cursor: cursor.execute( """SELECT report_id as report_id, (report_config ->> 'label') as label, global_status as global, (report_config ->> 'type') as type FROM reports_report WHERE report_resort = %s AND (report_user = %s OR global_status = 1) ;""", [resort.resort_pk, user.user_pk]) data = dictfetchall(cursor) return data
def update_report(status, user, config, report_id): with connection.cursor() as cursor: cursor.execute( """UPDATE reports_report SET global_status = %s, report_user = %s, report_config = %s WHERE report_id = %s RETURNING report_id, report_config;""", [status, user.user_pk, json.dumps(config), report_id]) data = dictfetchall(cursor) cursor.close() return data[0]
def update_incident(apps, schema_editor): Incident = apps.get_model("incidents", "Incident") cursor = connection.cursor() cursor.execute("""SELECT incident_pk, incident_data FROM incidents_incident WHERE (incident_data ->> 'field_52ca453862ba6') = '????'""") data = dictfetchall(cursor) for id, val in enumerate(data): incident_data = val['incident_data'] incident_data['field_52ca453862ba6'] = "1431" cursor.execute("UPDATE incidents_incident SET incident_data = %s WHERE incident_pk = %s", [json.dumps(incident_data), val['incident_pk']])
def patrollers(self, request): dateFrom, dateTo, resort = get_param(request) user_role = get_roleid_user(resort, request.user) if user_role == 1: return Response( { _("detail"): _("You do not have permission to view analytics") }, status=403) with connection.cursor() as cursor: cursor.execute("""SELECT json_array_elements(CAST (resorts_resort.incident_template AS JSON)->'DashboardItems'->'field_52d47aac9bd13'->'RepeatingQuestions'->'patroller'->'Values') ->> (patrollers_incident->>'patroller') as name, SUM(CASE WHEN CAST (patrollers_incident->'incident_role' AS text) = '"170"' THEN 1 ELSE 0 END) AS "primary", SUM(CASE WHEN CAST (patrollers_incident->'incident_role' AS text) = '"171"' THEN 1 ELSE 0 END) AS secondary, SUM(CASE WHEN CAST (patrollers_incident->'incident_role' AS text) = '"172"' THEN 1 ELSE 0 END) AS assist FROM incidents_incident INNER JOIN custom_user_users ON custom_user_users.user_pk = incidents_incident.assigned_to_id, json_array_elements(incidents_incident.incident_data->'field_52d47aac9bd13') as patrollers_incident LEFT JOIN resorts_resort ON resorts_resort.resort_pk = %d WHERE CAST (patrollers_incident AS text) <> '[]' AND CAST (patrollers_incident AS text) <> 'false' AND CAST (patrollers_incident AS text) <> '' AND incidents_incident.resort_id = %d AND custom_user_users.user_connected = %d AND incidents_incident.dt_created IS NOT NULL AND incidents_incident.dt_created >= '%s' AND incidents_incident.dt_created <= '%s' AND incidents_incident.incident_status_id <> 9 GROUP BY 1;""" % (resort.resort_pk, request.user.user_connected, resort.resort_pk, dateFrom, dateTo)) data = dictfetchall(cursor) if data: data.pop(0) return Response({'success': True, 'data': data}, status=200)
def update_incident_audit_values(apps, schema_editor): cursor = connection.cursor() cursor.execute("""SELECT audit_id, incident_data FROM incidents_incidentaudit""") data = dictfetchall(cursor) for id, val in enumerate(data): incident_data = val['incident_data'] try: incident_data["field_52dd8c049b005"] = float( incident_data["field_52dd8c049b005"]) except: pass try: incident_data["field_52dd8bee9b004"] = float( incident_data["field_52dd8bee9b004"]) except: pass try: incident_data["field_52ca4637cc0fe"] = float( incident_data["field_52ca4637cc0fe"]) except: pass try: incident_data["field_52ca461ccc0fd"] = float( incident_data["field_52ca461ccc0fd"]) except: pass cursor.execute( "UPDATE incidents_incidentaudit SET incident_data = %s WHERE audit_id = %s", [json.dumps(incident_data), val['audit_id']]) cursor.close()
def create_report(status, user, config, resort): with connection.cursor() as cursor: cursor.execute( """INSERT INTO reports_report ( report_id, global_status, report_user, report_config, report_resort ) VALUES ( uuid_generate_v4(), %s, %s, %s, %s ) RETURNING report_id, report_config; """, [status, user.user_pk, json.dumps(config), resort.resort_pk]) data = dictfetchall(cursor) cursor.close() return data[0]
def gender(self, request): dateFrom, dateTo, resort = get_param(request) user_role = get_roleid_user(resort, request.user) if user_role == 1: return Response( { _("detail"): _("You do not have permission to view analytics") }, status=403) with connection.cursor() as cursor: cursor.execute("""SELECT extract(year from incidents_incident.dt_created) as year, extract(month from incidents_incident.dt_created) as month, SUM(CASE WHEN incidents_patients.sex = 'male' then 1 else 0 end) as Male, SUM(CASE WHEN incidents_patients.sex = 'female' then 1 else 0 end) as Female, SUM(CASE WHEN incidents_patients.sex = '' then 1 else 0 end) as Unknown FROM incidents_incident LEFT JOIN incidents_patients ON incidents_incident.incident_pk = incidents_patients.incident_id INNER JOIN custom_user_users ON assigned_to_id = custom_user_users.user_pk WHERE incidents_incident.resort_id = %d AND custom_user_users.user_connected = %d AND incidents_incident.dt_created IS NOT NULL AND incidents_incident.dt_created >= '%s' AND incidents_incident.dt_created <= '%s' AND incidents_incident.incident_status_id <> 9 GROUP BY 1,2 ORDER BY 1,2;""" % (resort.resort_pk, request.user.user_connected, dateFrom, dateTo)) data = dictfetchall(cursor) return Response({'success': True, 'data': data}, status=200)
def table(self, request): if request.user.user_connected == 0: return Response( {_('detail'): _('you do not have access to report')}, status=403) dateFrom, dateTo, resort = get_param_post(request) text_data_key = get_data_key(resort) chunk = int(request.query_params.get('chunk', 100)) offset = int(request.query_params.get('offset', 0)) output_format = request.query_params.get('output_format', 'json') from_query = '' where_query = '' inner_join = '' cross_join = '' table_no = 0 inner_patient = False inner_note = False response_data = {} where_parameters = [] b = incident_template_field_type_mapping(resort.incident_template) def shouldRegex(field): if field == 'field_539158b37814e': return False if field == 'field_52dd8a24e95a6': return False if field == 'field_53c386190a2dd': return False if field == 'field_5334b101c8779': return False if field == 'field_52ca437b62b9c': return False if field == 'field_52ca43f362ba0': return False if field == 'field_52ca429c62b98': return False if field == 'field_52ca405959d2c': return False if field == 'field_52ca3fcc59d29': return False if field == 'field_52ca431e62b9b': return False if field == 'field_54b084fb2d255': return False return True for data_key, data_value in request.data.iteritems(): if data_key in patient_fields: if not inner_patient: inner_join += ' INNER JOIN incidents_patients ON incidents_patients.incident_id = incidents_incident.incident_pk' cross_join += """CROSS JOIN (SELECT '%s'::TEXT As datakey) As keys""" % ( text_data_key) inner_patient = True data_length = len(data_value) if data_length == 1: if data_key in encrypted_fields: where_query += " AND pgp_sym_decrypt(incidents_patients.%s, keys.datakey) ILIKE %s" % ( data_key, '%s') where_parameters.append("%" + data_value[0] + "%") elif data_key == 'sex': where_query += " AND incidents_patients.%s = %s" % ( 'sex', '%s') where_parameters.append(data_value[0].lower()) else: where_query += " AND incidents_patients.%s ILIKE %s" % ( data_key, '%s') where_parameters.append(data_value[0]) else: temp_query = '' for id, value in enumerate(data_value): if data_key in encrypted_fields: temp_query += "%spgp_sym_decrypt(incidents_patients.%s, keys.datakey) ILIKE %s" % ( " OR " if id > 0 else "", data_key, '%s') where_parameters.append("%" + data_value[id] + "%") elif data_key == 'sex': temp_query += "%sincidents_patients.%s = %s" % ( " OR " if id > 0 else "", 'sex', '%s') where_parameters.append(data_value[id].lower()) else: temp_query += "%sincidents_patients.%s ILIKE %s" % ( " OR " if id > 0 else "", data_key, '%s') where_parameters.append(data_value[id]) where_query += " AND " + "(" + temp_query + ")" elif data_key in note_fields: if not inner_note: inner_join += ' INNER JOIN incidents_incidentnotes ON incidents_incidentnotes.incident_id = incidents_incident.incident_pk' inner_note = True data_length = len(data_value) if data_length == 1: where_query += " AND incidents_incidentnotes.%s ILIKE %s" % ( note_field_map[data_key], '%s') where_parameters.append("%" + data_value[0] + "%") else: temp_query = "" for id, value in enumerate(data_value): temp_query += "%sincidents_incidentnotes.%s ILIKE %s" % ( " OR " if id > 0 else "", note_field_map[data_key], '%s') where_parameters.append("%" + data_value[id] + "%") where_query += " AND " + "(" + temp_query + ")" elif data_key in location_field: if not 'field_52ca456962ba8' in from_query: from_query += ", CAST((incidents_incident.incident_data -> 'field_52ca456962ba8') AS JSON) as location_json" data_length = len(data_value) if data_length == 1: where_query += " AND (location_json ->> '%s') ILIKE %s" % ( data_key.split('____')[1], '%s') where_parameters.append("%" + data_value[0] + "%") else: temp_query = "" for id, value in enumerate(data_value): temp_query += "%s(location_json ->> '%s') ILIKE %s" % ( " OR " if id > 0 else "", data_key.split('____')[1], '%s') where_parameters.append("%" + data_value[id] + "%") where_query += " AND " + "(" + temp_query + ")" elif b[data_key]['type'] == 'text' or b[data_key]['type'] == 'int': data_length = len(data_value) if data_length == 1: where_query += " AND (incident_data ->> '%s') %s %s" % ( b[data_key]['key'], "ILIKE" if type( data_value[0]) in [str, unicode] else "=", '%s') if shouldRegex(data_key): where_parameters.append( "%" + data_value[0] + "%" if type(data_value[0]) in [str, unicode] else str(data_value[0])) else: where_parameters.append(data_value[0]) else: temp_query = "" for id, value in enumerate(data_value): temp_query += "%s(incident_data ->> '%s') %s %s" % ( " OR " if id > 0 else "", b[data_key]['key'], "ILIKE" if type(data_value[0]) in [str, unicode] else "=", '%s') where_parameters.append( "%" + data_value[id] + "%" if type(data_value[id]) in [str, unicode] else str(data_value[id])) where_query += " AND " + "(" + temp_query + ")" elif b[data_key]['type'] == 'array': if not b[data_key]['key'] in from_query: from_query += ", json_array_elements(incidents_incident.incident_data -> '%s') as table%d" % ( b[data_key]['key'], table_no) data_length = len(data_value) if data_length == 1: where_query += " AND CAST(table%d AS TEXT) = %s" % ( table_no, '%s') where_parameters.append('"' + data_value[0] + '"') else: temp_query = "" for id, value in enumerate(data_value): temp_query += "%sCAST(table%d AS TEXT) = %s" % ( " OR " if id > 0 else "", table_no, '%s') where_parameters.append('"' + data_value[id] + '"') where_query += " AND " + "(" + temp_query + ")" table_no += 1 elif 'repeating' in b[data_key]['type']: if not b[data_key]['key'] in from_query: from_query += ", CAST(json_array_elements(incidents_incident.incident_data -> '%s') AS JSON) as table%d" % ( b[data_key]['key'], table_no) data_length = len(data_value) if data_length == 1: where_query += " AND (table%d ->> '%s') ILIKE %s" % ( table_no, b[data_key]['sub_key'], '%s') where_parameters.append("%" + data_value[0] + "%") else: temp_query = "" for id, value in enumerate(data_value): temp_query += "%s(table%d ->> '%s') ILIKE %s" % ( " OR " if id > 0 else "", table_no, b[data_key]['sub_key'], '%s') where_parameters.append("%" + data_value[id] + "%") where_query += " AND " + "(" + temp_query + ")" table_no += 1 with connection.cursor() as cursor: if output_format == 'json': query = """SELECT count(*) OVER() AS full_count, incident_pk FROM incidents_incident INNER JOIN custom_user_users ON assigned_to_id = custom_user_users.user_pk INNER JOIN incidents_incidentstatus ON (incidents_incident.incident_status_id = incidents_incidentstatus.incident_status_id)%s%s %s WHERE resort_id = %d AND custom_user_users.user_connected = 1 AND dt_created IS NOT NULL AND dt_created >= '%s' AND dt_created <= '%s' AND incidents_incidentstatus.order IN (1,2,3,4,5,6,7,8)%s GROUP BY incident_pk ORDER BY incidents_incident.dt_created DESC OFFSET %d LIMIT %d;""" % (inner_join, from_query, cross_join, resort.resort_pk, dateFrom, dateTo, where_query, offset, chunk) elif output_format == 'csv': query = """SELECT count(*) OVER() AS full_count, incident_pk FROM incidents_incident INNER JOIN custom_user_users ON assigned_to_id = custom_user_users.user_pk INNER JOIN incidents_incidentstatus ON (incidents_incident.incident_status_id = incidents_incidentstatus.incident_status_id)%s%s %s WHERE resort_id = %d AND custom_user_users.user_connected = 1 AND dt_created IS NOT NULL AND dt_created >= '%s' AND dt_created <= '%s' AND incidents_incidentstatus.order IN (1,2,3,4,5,6,7,8)%s GROUP BY incident_pk ORDER BY incidents_incident.dt_created DESC;""" % ( inner_join, from_query, cross_join, resort.resort_pk, dateFrom, dateTo, where_query) cursor.execute(query, where_parameters) data = dictfetchall(cursor) incident_data = [] if output_format == 'json': for val in data: incident_info = IncidentReportSerializer( Incident.objects.get(incident_pk=val['incident_pk']), fields=('incident_pk', 'incident_id', 'dt_created', 'incident_status', 'assigned_to'), context={ 'data_key': text_data_key }).data incident_data.append(incident_info) response_data['offset'] = offset response_data['chunk'] = chunk response_data['count'] = data[0]['full_count'] if len( data) > 0 else 0 response_data['results'] = incident_data elif output_format == 'csv': return sendfile( request, settings.MEDIA_ROOT + dict_to_csv_report( get_report_data_for_csv(data, resort, text_data_key)), attachment=True) return Response(response_data, status=200)
def patrollers(self, request): order_by_fields = ['name', 'primary', 'secondary', 'assist', 'total'] dateFrom, dateTo, resort, chunk, offset, orderBy, orderByDirection, outputFormat = get_param_with_pagination( request) user_role = get_roleid_user(resort, request.user) original_data_len = 0 if user_role == 1: return Response( { _("detail"): _("You do not have permission to view analytics") }, status=403) if orderBy not in order_by_fields: orderBy = order_by_fields[0] with connection.cursor() as cursor: cursor.execute("""SELECT json_array_elements(CAST (resorts_resort.incident_template AS JSON)->'DashboardItems'->'field_52d47aac9bd13'-> 'RepeatingQuestions'->'patroller'->'Values') ->> (patrollers_incident->>'patroller') as name, SUM(CASE WHEN CAST (patrollers_incident->'incident_role' AS text) = '"170"' THEN 1 ELSE 0 END) AS "primary", SUM(CASE WHEN CAST (patrollers_incident->'incident_role' AS text) = '"171"' THEN 1 ELSE 0 END) AS "secondary", SUM(CASE WHEN CAST (patrollers_incident->'incident_role' AS text) = '"172"' THEN 1 ELSE 0 END) AS "assist", SUM(CASE WHEN CAST (patrollers_incident->'incident_role' AS text) = '"170"' THEN 1 ELSE 0 END + CASE WHEN CAST (patrollers_incident->'incident_role' AS text) = '"171"' THEN 1 ELSE 0 END + CASE WHEN CAST (patrollers_incident->'incident_role' AS text) = '"172"' THEN 1 ELSE 0 END) AS "total" FROM incidents_incident INNER JOIN custom_user_users ON custom_user_users.user_pk = incidents_incident.assigned_to_id, json_array_elements(incidents_incident.incident_data->'field_52d47aac9bd13') as patrollers_incident LEFT JOIN resorts_resort ON resorts_resort.resort_pk = %d WHERE CAST (patrollers_incident AS text) <> '[]' AND CAST (patrollers_incident AS text) <> 'false' AND CAST (patrollers_incident AS text) <> '' AND incidents_incident.resort_id = %d AND custom_user_users.user_connected = %d AND incidents_incident.dt_created IS NOT NULL AND incidents_incident.dt_created >= '%s' AND incidents_incident.dt_created <= '%s' AND incidents_incident.incident_status_id <> 9 GROUP BY 1 ORDER BY %s %s;""" % (resort.resort_pk, resort.resort_pk, request.user.user_connected, dateFrom, dateTo, "\"" + orderBy + "\"", orderByDirection)) data = dictfetchall(cursor) print resort, request.user if data: # Delete the global group row. pop_index = 0 if orderByDirection == 'DESC' else (len(data) - 1) data.pop(pop_index) original_data_len = len(data) # Pagination if offset is not 0: data = data[offset:] if chunk is not None: chunk = chunk if chunk <= len(data) else len(data) data = data[:chunk] if outputFormat.upper() == 'CSV': def toCSV(content): csvfile = StringIO.StringIO() csvwriter = csv.writer(csvfile) csvwriter.writerow( ["name", "primary", "secondary", "assist", "total"]) yield csvfile.getvalue() for row in content: csvfile = StringIO.StringIO() csvwriter = csv.writer(csvfile) csvwriter.writerow([ row["name"], row["primary"], row["secondary"], row["assist"], row["total"] ]) yield csvfile.getvalue() response = HttpResponse(toCSV(data), content_type='text/csv') response[ 'Content-Disposition'] = 'attachment; filename="patrollers_report.csv"' return response else: return Response( { 'success': True, 'data': data, 'total_rows': original_data_len }, status=200)
def report(self, request): resort = get_resort_for_user(request.user) order_by = request.query_params.get( 'order_by', 'controlled_substance__controlled_substance_name') order_by_direction = request.query_params.get('order_by_direction', 'desc') current_status = request.query_params.get('current_status', '') location_id = request.query_params.get('location_id', '') controlled_substance_id = request.query_params.get( 'controlled_substance_id', '') if order_by_direction == 'desc': order = '-' + order_by elif order_by_direction == 'asc': order = order_by if current_status == 'used': where_query = "" try: if location_id: location = ResortLocation.objects.get( location_id=location_id) else: location = None except: return Response({_('detail'): _('location not found')}) try: if controlled_substance_id: controlled_substance = ControlledSubstances.objects.get( controlled_substance_id=controlled_substance_id) else: controlled_substance = None except: return Response( {_('detail'): _('controlled_substance not found')}) # Create extended where query if location is not None: where_query += ' AND controlled_substance_stock.location = ' + str( location.location_pk) if controlled_substance is not None: where_query += ' AND controlled_substance_controlledsubstances.controlled_substance_pk = ' + str( controlled_substance.controlled_substance_pk) dateFrom = request.GET.get('date_from', None) if dateFrom is None: dateFrom = datetime.datetime.today() - datetime.timedelta( days=30) dateFrom = dateFrom.strftime("%Y-%m-%d 00:00:00") else: dateFrom = (datetime.datetime.strptime(dateFrom, "%Y-%m-%d %H:%M:%S")) dateFrom = dateFrom.strftime("%Y-%m-%d 00:00:00") dateTo = request.GET.get('date_to', None) if dateTo is None: dateTo = datetime.datetime.today() dateTo = dateTo.strftime("%Y-%m-%d 23:59:59") else: dateTo = (datetime.datetime.strptime(dateTo, "%Y-%m-%d %H:%M:%S")) dateTo = dateTo.strftime("%Y-%m-%d 23:59:59") with connection.cursor() as cursor: cursor.execute( """SELECT array_agg(controlled_substance_stockassignment.controlled_substance_stock) as pk FROM controlled_substance_stockassignment INNER JOIN controlled_substance_stock ON controlled_substance_stockassignment.controlled_substance_stock = controlled_substance_stock.controlled_substance_stock_pk INNER JOIN controlled_substance_controlledsubstances ON controlled_substance_stock.controlled_substance = controlled_substance_controlledsubstances.controlled_substance_pk WHERE controlled_substance_controlledsubstances.resort = %d AND controlled_substance_stockassignment.dt_used >= '%s' AND controlled_substance_stockassignment.dt_used <= '%s' AND controlled_substance_stockassignment.controlled_substance_stock_assignment_status = 2%s;""" % (resort.resort_pk, dateFrom, dateTo, where_query)) data = dictfetchall(cursor) query = Stock.objects.filter( controlled_substance_stock_pk__in=data[0]['pk'] if data[0]['pk'] is not None else []).exclude( current_status=DISPOSED).order_by(order) else: extra_conditions = {} if current_status: extra_conditions.update( {"current_status": STATUS[current_status]}) if location_id: extra_conditions.update({"location__location_id": location_id}) if controlled_substance_id: extra_conditions.update({ "controlled_substance__controlled_substance_id": controlled_substance_id }) query = Stock.objects.filter( controlled_substance__resort=resort, **extra_conditions).exclude( current_status=DISPOSED).order_by(order) queryset = self.filter_queryset(query) page = self.paginate_queryset(queryset) if page is not None: stocks = Stock.objects.filter( controlled_substance__resort=resort, controlled_substance__controlled_substance_id= controlled_substance_id).exclude(current_status=DISPOSED) stock_status = stock_status_count(stocks) serializer = StockReportSerializer( page, many=True, fields=('controlled_substance_stock_pk', 'controlled_substance_stock_id', 'controlled_substance', 'location', 'volume', 'dt_expiry', 'added_by_user', 'disposed_by_user')) response = self.get_paginated_response(serializer.data) response.data.update({"summary": stock_status}) return response
def encrypt_patient_data_kms(resort, pre_data_key, new_data_key): from apps.incidents.models import Patients from apps.incidents.utils import dictfetchall patient_id = Patients.objects.filter(incident__resort__resort_id=resort.resort_id).values_list( 'incident_patient_id', flat=True) if len(patient_id) > 0: cursor = connection.cursor() cursor.execute("""SELECT incident_patient_id, pgp_sym_decrypt(name, keys.datakey) AS name, pgp_sym_decrypt(address, keys.datakey) AS address, pgp_sym_decrypt(suburb, keys.datakey) AS suburb, pgp_sym_decrypt(state, keys.datakey) AS state, pgp_sym_decrypt(postcode, keys.datakey) AS postcode, pgp_sym_decrypt(phone, keys.datakey) AS phone, pgp_sym_decrypt(email, keys.datakey) AS email, pgp_sym_decrypt(dob, keys.datakey) AS dob FROM incidents_patients CROSS JOIN (SELECT %s::TEXT As datakey) As keys WHERE incidents_patients.incident_patient_id IN %s;""", [pre_data_key, tuple(patient_id)]) data = dictfetchall(cursor) for each_data in data: cursor.execute("""UPDATE incidents_patients SET name = pgp_sym_encrypt(v.name, keys.datakey, 'compress-algo=1, cipher-algo=aes256'), address = pgp_sym_encrypt(v.address, keys.datakey, 'compress-algo=1, cipher-algo=aes256'), suburb = pgp_sym_encrypt(v.suburb, keys.datakey, 'compress-algo=1, cipher-algo=aes256'), state = pgp_sym_encrypt(v.state,keys.datakey, 'compress-algo=1, cipher-algo=aes256'), postcode = pgp_sym_encrypt(v.postcode, keys.datakey, 'compress-algo=1, cipher-algo=aes256'), phone = pgp_sym_encrypt(v.phone,keys.datakey, 'compress-algo=1, cipher-algo=aes256'), email = pgp_sym_encrypt(v.email, keys.datakey, 'compress-algo=1, cipher-algo=aes256'), dob = pgp_sym_encrypt(v.dob, keys.datakey, 'compress-algo=1, cipher-algo=aes256') FROM ( VALUES ( %s, %s, %s, %s, %s, %s, %s, %s ) ) AS v ( name, address, suburb, state, postcode, phone, email, dob ) CROSS JOIN (SELECT %s::TEXT As datakey) As keys WHERE incidents_patients.incident_patient_id = %s;""", [each_data['name'], each_data['address'], each_data['suburb'], each_data['state'], each_data['postcode'], each_data['phone'], each_data['email'], each_data['dob'], new_data_key, each_data['incident_patient_id']]) cursor.close() return True
def bar(self, request): tempdata = [] output_format = request.query_params.get('output_format', 'json') if request.user.user_connected == 0: return Response( {_('detail'): _('you do not have access to report')}, status=403) resort = get_resort_for_user(request.user) text_data_key = get_data_key(resort) b = incident_template_field_type_mapping(resort.incident_template) datetime_object = extract_date_chart(request.data, resort) for idx, value in enumerate(request.data): from_query = '' where_query = '' inner_join = '' cross_join = '' table_no = 0 inner_patient = False inner_note = False where_parameter = [] group_by_query = '' order_by_query = '' dateFrom, dateTo = datetime_object[idx][ 'dateFrom'], datetime_object[idx]['dateTo'] scale = scale_mapping[value.get('scale', 'date')] if scale == 'date': column_query = 'incidents_incident.dt_created::TIMESTAMP::DATE as columndetail' else: if scale in ["hour", "dow"]: column_query = 'extract( ' + scale + ' FROM incidents_incident.dt_created::TIMESTAMP) + 1 as columndetail' elif scale == 'hdow': column_query = "extract(DOW FROM incidents_incident.dt_created::TIMESTAMP) + 1 as columndetail, extract( HOUR FROM incidents_incident.dt_created::TIMESTAMP) + 1 as columndetail1" group_by_query = ',columndetail1' order_by_query = ',columndetail1' else: column_query = 'extract( ' + scale + ' FROM incidents_incident.dt_created::TIMESTAMP) as columndetail' for data_key, data_value in value['data'].iteritems(): if data_key == 'total_incident': pass elif data_key in patient_fields: if not inner_patient: inner_join += ' INNER JOIN incidents_patients ON incidents_patients.incident_id = incidents_incident.incident_pk' cross_join += """CROSS JOIN (SELECT '%s' As datakey) As keys""" % ( text_data_key) inner_patient = True for id, value in enumerate(data_value): if data_key in encrypted_fields: where_query += " AND pgp_sym_decrypt(incidents_patients.%s, keys.datakey) ILIKE %s" % ( data_key, '%s') where_parameter.append("%" + data_value[id] + "%") elif data_key == 'sex': where_query += " AND incidents_patients.%s = %s" % ( 'sex', '%s') where_parameter.append(data_value[id].lower()) else: where_query += " AND incidents_patients.%s = %s" % ( data_key, '%s') where_parameter.append(data_value[id]) elif data_key in note_fields: if not inner_note: inner_join += ' INNER JOIN incidents_incidentnotes ON incidents_incidentnotes.incident_id = incidents_incident.incident_pk' inner_note = True for id, value in enumerate(data_value): where_query += " AND incidents_incidentnotes.%s = %s" % ( note_field_map[data_key], '%s') where_parameter.append(data_value[id]) elif data_key in location_field: if not 'field_52ca456962ba8' in from_query: from_query += ", CAST((incidents_incident.incident_data -> 'field_52ca456962ba8') AS JSON) as location_json" for id, value in enumerate(data_value): where_query += " AND (location_json ->> '%s') = %s" % ( data_key.split('____')[1], '%s') where_parameter.append(data_value[id]) elif b[data_key]['type'] == 'text' or b[data_key][ 'type'] == 'int': for id, value in enumerate(data_value): where_query += " AND (incident_data ->> '%s') = %s" % ( b[data_key]['key'], '%s') where_parameter.append(data_value[id]) elif b[data_key]['type'] == 'array': if not b[data_key]['key'] in from_query: from_query += ", json_array_elements(incidents_incident.incident_data -> '%s') as table%d" % ( b[data_key]['key'], table_no) for id, value in enumerate(data_value): where_query += " AND CAST(table%d AS TEXT) = %s" % ( table_no, '%s') where_parameter.append('"' + data_value[id] + '"') table_no += 1 elif 'repeating' in b[data_key]['type']: if not b[data_key]['key'] in from_query: from_query += ", CAST(json_array_elements(incidents_incident.incident_data -> '%s') AS JSON) as table%d" % ( b[data_key]['key'], table_no) for id, value in enumerate(data_value): where_query += " AND (table%d ->> '%s') = %s" % ( table_no, b[data_key]['sub_key'], '%s') where_parameter.append(data_value[id]) with connection.cursor() as cursor: query = """SELECT %d as field, %s , count(*) count FROM incidents_incident INNER JOIN custom_user_users ON assigned_to_id = custom_user_users.user_pk INNER JOIN incidents_incidentstatus ON (incidents_incident.incident_status_id = incidents_incidentstatus.incident_status_id)%s %s %s WHERE resort_id = %d AND custom_user_users.user_connected = 1 AND dt_created IS NOT NULL AND dt_created >= '%s' AND dt_created <= '%s' AND incidents_incidentstatus.order IN (1,2,3,4,5,6,7,8) %s GROUP BY columndetail %s ORDER BY columndetail%s;""" % \ ( idx + 1, column_query, inner_join, from_query, cross_join, resort.resort_pk, dateFrom, dateTo, where_query, group_by_query, order_by_query) cursor.execute('BEGIN;') cursor.execute("SET LOCAL TIME ZONE %s;", [resort.timezone]) cursor.execute(query, where_parameter) data = dictfetchall(cursor) cursor.execute('END;') tempdata.append( add_missing_data(data, idx + 1, dateFrom, dateTo, scale)) final_data = merge_compare_data(tempdata) if output_format == 'csv': scale = scale_mapping[request.data[0].get('scale', 'date')] return sendfile(request, settings.MEDIA_ROOT + dict_to_csv_report(final_data, 'bar', scale), attachment=True) return Response(final_data)
def pie(self, request): if request.user.user_connected == 0: return Response( {_('detail'): _('you do not have access to report')}, status=403) response_data = [] output_format = request.query_params.get('output_format', 'json') resort = get_resort_for_user(request.user) b = incident_template_field_type_mapping(resort.incident_template) datetime_object = extract_date_chart(request.data, resort) dateFrom = datetime_object[0]['dateFrom'] dateTo = datetime_object[0]['dateTo'] for idx, value in enumerate(request.data): dateFrom, dateTo = datetime_object[idx][ 'dateFrom'], datetime_object[idx]['dateTo'] key = b[value['data'].keys()[0]] for id, val in enumerate(key['data']): inner_join = '' from_query = '' select_query = '' where_query = '' where_parameter = [] select_query += "%s as name ,COUNT(*) as count" where_parameter.append(val[val.keys()[0]]) if key['main_type'] == 'gender' or key['key'] == 'country': inner_join += " INNER JOIN incidents_patients on incidents_patients.incident_id = incidents_incident.incident_pk" where_query += " AND incidents_patients.%s = %s" % ( key['key'], '%s') where_parameter.append(val.keys()[0]) elif key['type'] in ['text']: where_query += " AND (incident_data ->> '%s') = %s" % ( key['key'], '%s') where_parameter.append(val.keys()[0]) elif key['type'] == 'array': from_query += ", json_array_elements(incidents_incident.incident_data -> '%s') as table0" % ( key['key']) where_query += " AND CAST(table0 AS TEXT) = %s" % ('%s') where_parameter.append('"' + val.keys()[0] + '"') elif 'repeating' in key['type']: from_query += ", CAST(json_array_elements(incidents_incident.incident_data -> '%s') AS JSON) as table0" % ( key['key']) where_query += " AND (table0 ->> '%s') = %s" % ( key['sub_key'], '%s') where_parameter.append(val.keys()[0]) with connection.cursor() as cursor: query = """SELECT %s FROM incidents_incident INNER JOIN custom_user_users ON assigned_to_id = custom_user_users.user_pk INNER JOIN incidents_incidentstatus ON (incidents_incident.incident_status_id = incidents_incidentstatus.incident_status_id)%s%s WHERE resort_id = %d AND custom_user_users.user_connected = 1 AND dt_created IS NOT NULL AND dt_created >= '%s' AND dt_created <= '%s' AND incidents_incidentstatus.order IN (1,2,3,4,5,6,7,8)%s;""" % ( select_query, inner_join, from_query, resort.resort_pk, dateFrom, dateTo, where_query) cursor.execute('BEGIN;') cursor.execute("SET LOCAL TIME ZONE %s;", [resort.timezone]) cursor.execute(query, where_parameter) data = dictfetchall(cursor) cursor.execute('END;') response_data.append(data[0]) cursor = connection.cursor() query = """SELECT COUNT(*) as count FROM incidents_incident INNER JOIN custom_user_users ON assigned_to_id = custom_user_users.user_pk INNER JOIN incidents_incidentstatus ON (incidents_incident.incident_status_id = incidents_incidentstatus.incident_status_id) WHERE resort_id = %d AND custom_user_users.user_connected = 1 AND dt_created IS NOT NULL AND dt_created >= '%s' AND dt_created <= '%s' AND incidents_incidentstatus.order IN (1,2,3,4,5,6,7,8);""" % ( resort.resort_pk, dateFrom, dateTo) cursor.execute(query) total_data = dictfetchall(cursor) final_data = process_pie_chart_data(response_data, total_data[0]['count']) if output_format == 'csv': return sendfile(request, settings.MEDIA_ROOT + dict_to_csv_report(final_data), attachment=True) return Response(final_data)
def migrate_encrypted_patient_info(apps, schema_editor): from apps.incidents.utils import dictfetchall from helper_functions import create_client Resort = apps.get_model("resorts", "Resort") client = create_client() response = client.generate_data_key(KeyId=settings.GLOBAL_KMS_KEY_ID, KeySpec='AES_256') cursor = connection.cursor() cursor.execute("""SELECT incident_patient_id, pgp_pub_decrypt(name, keys.privkey) AS name, pgp_pub_decrypt(address, keys.privkey) AS address, pgp_pub_decrypt(suburb, keys.privkey) AS suburb, pgp_pub_decrypt(state, keys.privkey) AS state, pgp_pub_decrypt(postcode, keys.privkey) AS postcode, pgp_pub_decrypt(phone, keys.privkey) AS phone, pgp_pub_decrypt(email, keys.privkey) AS email, pgp_pub_decrypt(dob, keys.privkey) AS dob FROM incidents_patients CROSS JOIN (SELECT dearmor(%s) As privkey) As keys;""", [settings.GPG_PRIVATE_KEY, ]) data = dictfetchall(cursor) for each_data in data: each_data = replace_null(each_data) cursor.execute("""UPDATE incidents_patients SET name = pgp_sym_encrypt(v.name, keys.datakey, 'compress-algo=1, cipher-algo=aes256'), address = pgp_sym_encrypt(v.address, keys.datakey, 'compress-algo=1, cipher-algo=aes256'), suburb = pgp_sym_encrypt(v.suburb, keys.datakey, 'compress-algo=1, cipher-algo=aes256'), state = pgp_sym_encrypt(v.state,keys.datakey, 'compress-algo=1, cipher-algo=aes256'), postcode = pgp_sym_encrypt(v.postcode, keys.datakey, 'compress-algo=1, cipher-algo=aes256'), phone = pgp_sym_encrypt(v.phone,keys.datakey, 'compress-algo=1, cipher-algo=aes256'), email = pgp_sym_encrypt(v.email, keys.datakey, 'compress-algo=1, cipher-algo=aes256'), dob = pgp_sym_encrypt(v.dob, keys.datakey, 'compress-algo=1, cipher-algo=aes256') FROM ( VALUES ( %s, %s, %s, %s, %s, %s, %s, %s ) ) AS v ( name, address, suburb, state, postcode, phone, email, dob ) CROSS JOIN ( SELECT %s::TEXT AS datakey) AS keys WHERE incidents_patients.incident_patient_id = %s;""", [each_data['name'], each_data['address'], each_data['suburb'], each_data['state'], each_data['postcode'], each_data['phone'], each_data['email'], each_data['dob'], base64.b64encode(response['Plaintext']), each_data['incident_patient_id']]) resorts = Resort.objects.all().update(enc_data_key=base64.b64encode(response['CiphertextBlob'])) cursor.close()