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()
