def handle(self, **options):
try:
from uganda_common.utils import ExcelResponse
excel_file_path = \
os.path.join(os.path.join(os.path.join(UREPORT_ROOT,
'static'), 'spreadsheets'),
'ureporters.xlsx')
export_data_list = []
# messages=Message.objects.select_related(depth=1)
# black_listed=Blacklist.objects.values_list('connection__contact__pk',flat=True)
# print black_listed
# black_list_messages=messages.filter(connection__contact__in=black_listed)
# opt_words=settings.OPT_OUT_WORDS
if connection.connection is None:
cursor = connection.cursor()
cursor = connection.connection.cursor(name='contacts')
cursor.execute(self.sql)
row_0 = [(
'Contact_pk',
'Language',
'Join Date',
'Quit Date',
'District',
'Id',
'Age',
'Gender',
'Health Facility',
'Village',
'Subcounty',
'Group 1',
'Group 2',
'Group 3',
'How did you hear about ureport?',
'Number Of Responses',
'Number Of Questions Asked',
'Number of Incoming',
)]
rows = row_0 + cursor.fetchall()
kinds = "int text date date text int int text text text text text text text text text text text".split(
)
kind_to_xf_map = {
'date': ezxf(num_format_str='yyyy-mm-dd'),
'int': ezxf(num_format_str='#,##0'),
'text': ezxf(),
}
data_xfs = [kind_to_xf_map[k] for k in kinds]
ExcelResponse(rows,
output_name=excel_file_path,
write_to_file=True)
#print rows
# self.write_xls(excel_file_path, 'ureporters', row_0, rows, data_xfs)
except Exception, exc:
print traceback.format_exc(exc)
def flag_categories(request, name):
group = get_object_or_404(Group, name=name)
if get_access(request) and request.user not in group.user_set.all():
return render(request, '403.html', status=403)
access = group.access_set.all()[0]
flags = access.flags.all()
flagged_messages = MessageFlag.objects.filter(flag__in=flags,
message__connection__contact__reporting_location__name__in=access.allowed_locations)
if request.GET.get('export', None):
data = flagged_messages.filter(
message__connection__contact__reporting_location__name__in=access.allowed_locations).values_list(
'message__connection_id', 'message__text', 'flag__name', 'message__date',
'message__connection__contact__reporting_location__name')
headers = ['Identifier', 'Message', 'Flag', 'Date', 'District']
return ExcelResponse(data=data, headers=headers)
return generic(
request,
model=MessageFlag,
queryset=flagged_messages,
objects_per_page=10,
results_title='Flagged Messages',
selectable=False,
partial_row='ureport/partials/messages/flagged_message_row.html'
,
base_template='ureport/flagged_message_base.html',
columns=[('Identifier', True, 'message__connection_id', SimpleSorter()),
('Message', True, 'message__text', SimpleSorter()),
('Date', True, 'message__date', SimpleSorter()),
('Flags', False, 'message__flagged', None)],
sort_column='date',
sort_ascending=False,
all_flags=flags,
go_to_dashboards=True
)
def view_flagged_with(request, pk):
flag = get_object_or_404(Flag, pk=pk)
access = get_access(request)
if access and not flag in access.flags.all():
return render(request, '403.html', status=403)
messages = flag.get_messages()
if request.GET.get('export', None):
export_data = messages.values_list('text', 'connection_id', 'date',
'connection__contact__reporting_location__name')
headers = ['Message', 'Identifier', 'Date', 'District']
return ExcelResponse(data=export_data, headers=headers)
return generic(
request,
model=Message,
queryset=messages,
objects_per_page=25,
partial_row='ureport/partials/contacts/contacts_row_flag.html',
base_template='ureport/view_flagged_with_base.html',
results_title='Messages Flagged With %s' % flag.name,
columns=[('Message', True, 'text', SimpleSorter()),
('Identifier', True, 'connection_id', SimpleSorter()),
('Date', True, 'date', SimpleSorter()),
('Type', True, 'application', SimpleSorter())],
sort_column='date',
sort_ascending=False,
)
def flagged_messages(request):
all_flags = Flag.objects.all()
if request.GET.get('export', None):
flaggedmessages = MessageFlag.objects.exclude(flag=None)
data = flaggedmessages.values_list(
'message__connection_id', 'message__text', 'flag__name',
'message__date',
'message__connection__contact__reporting_location__name')
headers = ['Identifier', 'Message', 'Flag', 'Date', 'District']
return ExcelResponse(data=data, headers=headers)
return generic(
request,
model=MessageFlag,
queryset=get_flagged_messages,
objects_per_page=10,
results_title='Flagged Messages',
selectable=False,
partial_row='ureport/partials/messages/flagged_message_row.html',
base_template='ureport/flagged_message_base.html',
columns=[('Identifier', True, 'message__connection_id',
SimpleSorter()),
('Message', True, 'message__text', SimpleSorter()),
('Date', True, 'message__date', SimpleSorter()),
('Flags', False, 'message__flagged', None)],
sort_column='date',
sort_ascending=False,
all_flags=all_flags,
)
def export_submissions(request, xform_pk):
export_data_list = []
xform = get_object_or_404(XForm, pk=xform_pk)
for r in XFormSubmission.objects.exclude(connection=None).filter(
xform=xform):
export_data = SortedDict()
export_data['report_id'] = r.pk
export_data['report'] = r.xform.name
export_data['date'] = str(r.created)
export_data[
'reporter'] = r.connection.contact.name if r.connection.contact else 'None'
export_data[
'reporter_id'] = r.connection.contact.pk if r.connection.contact else 'None'
export_data['phone'] = r.connection.identity
export_data['location'] = r.connection.contact.reporting_location.name
export_data['location_id'] = r.connection.contact.reporting_location.pk
export_data['valid'] = (r.has_errors and "No") or "Yes"
for f in xform.fields.order_by('order'):
export_data["%s:%s" % (f.command, f.description)] = getattr(
r.eav, f.slug) or 'None'
export_data_list.append(export_data)
return ExcelResponse(export_data_list)
def export(self, request, queryset, date_field):
if request.user.has_perm("ureport.can_export"):
start = self.cleaned_data['startdate']
end = self.cleaned_data['enddate']
date = "%s__range" % date_field
kwargs = dict(date=(start, end))
data = queryset.filter(**kwargs).values()
response = ExcelResponse(data=list(data))
return response
def export_approved_submissions(request, xform_pk):
if not request.user.is_staff:
res = HttpResponse("Unauthorized")
res.status_code = 401
return res
export_data_list = []
xform = get_object_or_404(XForm, pk=xform_pk)
for r in XFormSubmission.objects.exclude(connection=None).filter(
xform=xform):
try:
appr_sub = ApprovedSubmission.objects.get(submission_id=r.pk)
except ApprovedSubmission.DoesNotExist:
appr_sub = None
if appr_sub:
export_data = SortedDict()
export_data['report_id'] = r.pk
export_data['report'] = r.xform.name
export_data['date'] = str(r.created)
if r.connection.contact:
export_data['reporter'] = r.connection.contact.name
export_data['reporter_id'] = r.connection.contact.pk
for group in r.connection.contact.groups.all():
export_data['NGO'] = group.name
export_data[
'location'] = r.connection.contact.reporting_location.name
export_data[
'location_id'] = r.connection.contact.reporting_location.pk
else:
export_data['reporter'] = 'None'
export_data['reporter_id'] = 'None'
export_data['NGO'] = 'None'
export_data['location'] = 'None'
export_data['location_id'] = 'None'
export_data['phone'] = r.connection.identity
export_data['valid'] = (r.has_errors and "No") or "Yes"
export_data['Approved'] = r.approved
export_data['Date Approved'] = str(appr_sub.time_approved)
for f in xform.fields.order_by('order'):
export_data["%s:%s" % (f.command, f.description)] = getattr(
r.eav, f.slug) or 'None'
export_data_list.append(export_data)
return ExcelResponse(export_data_list)
def handle(self, *args, **options):
polls = Connection.objects.all()
poll_list = []
for poll in polls:
print "On Poll ", poll.pk, "===>", poll.identity
poll_export = SortedDict()
poll_export['Identity'] = poll.pk
poll_export['Phone'] = poll.identity
if poll.contact:
poll_export['District'] = poll.contact.reporting_location
poll_export['Gender'] = poll.contact.gender
else:
poll_export['District'] = "Not known"
poll_export['Gender'] = "Not known"
poll_list.append(poll_export)
ExcelResponse(poll_list,
output_name='/home/kenneth/all_contacts.xlsx',
write_to_file=True)
def export_alerts(range_form, access, user):
start = range_form.cleaned_data['startdate']
end = range_form.cleaned_data['enddate']
from django.core.servers.basehttp import FileWrapper
cols = ["replied", "rating", "direction", "district", "date", "message", "id",
"forwarded"]
data = AlertsExport.objects.filter(date__range=(start, end))
if access:
if access.assigned_messages.exists():
numbers = access.assigned_messages.values_list('connection__identity', flat=True)
else:
numbers = list(access.groups.values_list('contact__connection__identity', flat=True))
data = data.filter(mobile__in=numbers)
data = data.values_list(*cols).iterator()
excel_file_path = \
os.path.join(os.path.join(os.path.join(UREPORT_ROOT,
'static'), 'spreadsheets'),
'alerts_%s.xlsx' % user.pk)
ExcelResponse(data, output_name=excel_file_path,
write_to_file=True, headers=cols)
def get_all_contacts(request):
from uganda_common.utils import ExcelResponse
contacts = Contact.objects.all()
export_data_list = []
for contact in contacts:
if contact.name:
export_data = SortedDict()
export_data['name'] = contact.name
if contact.gender:
export_data['sex'] = contact.gender
else:
export_data['sex'] = 'N/A'
if contact.birthdate:
try:
contact.birthdate.tzinfo = None
export_data['age'] = (datetime.datetime.now() -
contact.birthdate).days / 365
except:
continue
else:
export_data['age'] = 'N/A'
if contact.reporting_location:
export_data['district'] = \
contact.reporting_location.name
else:
export_data['district'] = 'N/A'
if contact.village:
export_data['village'] = contact.village.name
else:
export_data['village'] = 'N/A'
if contact.groups.count() > 0:
export_data['group'] = contact.groups.all()[0].name
else:
export_data['group'] = 'N/A'
export_data_list.append(export_data)
response = ExcelResponse(export_data_list)
return response
def handle(self, *args, **options):
excel_file_path = os.path.join(os.path.join(os.path.join(UREPORT_ROOT,
'static'), 'spreadsheets'),
'responses.xlsx')
date_1 = date(2012, 01, 01)
print date_1
polls = Poll.objects.filter(start_date__range=[date_1, datetime.now()])
print polls.count()
poll_list = []
for poll in polls:
print "On Poll ", poll.pk
resp = poll.responses.count() + 0.0
cont = poll.contacts.count()
poll_export = SortedDict()
poll_export['poll_id'] = poll.pk
poll_export['poll_name'] = poll.name
poll_export['question'] = poll.question
poll_export['Responses'] = resp
poll_export['Recipients'] = cont
poll_export['Percentage Response'] = round(resp/cont * 100, 2)
poll_export['Date'] = str(poll.start_date)
poll_list.append(poll_export)
ExcelResponse(poll_list, output_name='/home/kenneth/poll_responses.xlsx', write_to_file=True)
def handle(self, **options):
poll =Poll.objects.get(pk=int(options['p']))
if poll.responses.exists():
print 'Working'
responses = poll.responses.all()
response_data_list = []
excel_file_path = os.path.join(os.path.join(settings.MEDIA_ROOT, 'downloads'), 'poll_%d.xlsx' % poll.pk)
# excel_file_path = \
# os.path.join(os.path.join(os.path.join(UREPORT_ROOT,
# 'static'), 'spreadsheets'),
# 'poll_%d.xlsx' % poll.pk)
for response in responses:
response_export_data = SortedDict()
if response.contact:
try:
response_export_data['contact_pk'] = response.contact.connection_set.all()[0].pk
except IndexError:
continue
else:
response_export_data['contact_pk'] = ""
response_export_data['message_pk'] = response.message.pk
if response.contact and response.contact.language:
response_export_data['language'] = response.contact.language
else:
response_export_data['language'] = "en"
if response.contact and response.contact.gender:
response_export_data['sex'] = \
response.contact.gender
else:
response_export_data['sex'] = 'N/A'
if response.contact and response.contact.birthdate:
response_export_data['age'] = \
(datetime.datetime.now()
- response.contact.birthdate).days / 365
else:
response_export_data['age'] = 'N/A'
if response.contact \
and response.contact.reporting_location:
response_export_data['province'] = \
response.contact.reporting_location.name
else:
response_export_data['province'] = 'N/A'
if response.contact and response.contact.colline:
response_export_data['colline'] = \
response.contact.colline_name
else:
response_export_data['colline'] = 'N/A'
if response.contact and response.contact.commune:
response_export_data['commune'] = \
response.contact.commune.name
else:
response_export_data['commune'] = 'N/A'
if response.contact \
and response.contact.groups.count() > 0:
gr = list(response.contact.groups.order_by('pk').values_list('name', flat=True))
try:
response_export_data['group1'] = gr[0]
except IndexError:
response_export_data['group1'] = "N/A"
try:
response_export_data['group2'] = gr[1]
except IndexError:
response_export_data['group2'] = "N/A"
try:
response_export_data['group3'] = gr[2]
except IndexError:
response_export_data['group3'] = "N/A"
response_export_data['groups'] = \
','.join([group.name for group in
response.contact.groups.all()])
else:
response_export_data['groups'] = 'N/A'
response_export_data['group1'] = response_export_data['group2'] = response_export_data[
'group3'] = 'N/A'
if response.message:
response_export_data['response'] = \
response.message.text
response_export_data['date'] = \
response.message.date.strftime("%Y-%m-%d")
response_export_data['time'] = \
response.message.date.strftime("%H:%M:%S")
else:
response_export_data['response'] = ''
response_export_data['date'] = ''
response_export_data['time'] = ''
if response.poll:
response_export_data['question'] = \
response.poll.question
else:
response_export_data['question'] = ''
if response.categories.all().exists():
response_export_data['category'] = response.categories.all()[0].category.name
else:
response_export_data['category'] = "uncategorized"
response_data_list.append(response_export_data)
ExcelResponse(response_data_list,output_name=excel_file_path,write_to_file=True)
class Command(BaseCommand):
year_now = datetime.datetime.now().year
sql = \
""" SELECT
"rapidsms_contact"."id",
"rapidsms_contact"."language",
"rapidsms_contact"."created_on",
(SELECT
DATE("rapidsms_httprouter_message"."date")
FROM
"rapidsms_httprouter_message"
WHERE
"rapidsms_httprouter_message"."direction" = 'I'
and "rapidsms_httprouter_message"."application" = 'unregister'
and "rapidsms_httprouter_message"."connection_id" = (
SELECT
"rapidsms_connection"."id"
FROM
"rapidsms_connection"
WHERE
"rapidsms_connection"."contact_id" = "rapidsms_contact"."id" LIMIT 1
) LIMIT 1
) as quit_date,
"locations_location"."name" as district,
(
%d-EXTRACT('year'
FROM
"rapidsms_contact"."birthdate")) as age,
"rapidsms_contact"."gender",
"rapidsms_contact"."health_facility" as facility,
"rapidsms_contact"."village_name" as village,
(SELECT
"locations_location"."name"
FROM
"locations_location"
WHERE
"locations_location"."id"="rapidsms_contact"."subcounty_id") as subcounty,
(array(SELECT
"auth_group"."name"
FROM
"auth_group"
INNER JOIN
"rapidsms_contact_groups"
ON (
"auth_group"."id" = "rapidsms_contact_groups"."group_id"
)
WHERE
"rapidsms_contact_groups"."contact_id" = "rapidsms_contact"."id" order by "auth_group"."id" ))[1] as
group1,
(array(SELECT
"auth_group"."name"
FROM
"auth_group"
INNER JOIN
"rapidsms_contact_groups"
ON (
"auth_group"."id" = "rapidsms_contact_groups"."group_id"
)
WHERE
"rapidsms_contact_groups"."contact_id" = "rapidsms_contact"."id" order by "auth_group"."id" ))[2] as
group2,
(array(SELECT
"auth_group"."name"
FROM
"auth_group"
INNER JOIN
"rapidsms_contact_groups"
ON (
"auth_group"."id" = "rapidsms_contact_groups"."group_id"
)
WHERE
"rapidsms_contact_groups"."contact_id" = "rapidsms_contact"."id" order by "auth_group"."id" ))[3] as
group3,
(SELECT
"rapidsms_httprouter_message"."text"
FROM "rapidsms_httprouter_message"
JOIN "poll_response"
ON "poll_response"."message_id"= "rapidsms_httprouter_message"."id" where poll_id=121 and contact_id="rapidsms_contact"."id" and has_errors='f' limit 1) as source,
(SELECT
COUNT(*) FROM
"poll_response"
WHERE
"poll_response"."contact_id"="rapidsms_contact"."id") as responses,
(SELECT DISTINCT
COUNT(*) FROM
"poll_poll_contacts"
WHERE
"poll_poll_contacts"."contact_id"="rapidsms_contact"."id" GROUP BY "poll_poll_contacts"."contact_id") as questions,
(SELECT DISTINCT count(*)
FROM "rapidsms_httprouter_message"
WHERE "rapidsms_httprouter_message"."direction" ='I' and
"rapidsms_httprouter_message"."connection_id" = (
SELECT
"rapidsms_connection"."id"
FROM
"rapidsms_connection"
WHERE
"rapidsms_connection"."contact_id" = "rapidsms_contact"."id" LIMIT 1
) ) as incoming
FROM
"rapidsms_contact"
LEFT JOIN
"locations_location"
ON "rapidsms_contact"."reporting_location_id" = "locations_location"."id";
""" \
% year_now
def write_with_openpyxl(self, filename, headers, data):
wb = Workbook()
ws = wb.worksheets[0]
ws.title = 'Ureporters'
row_x = 0
for row in data:
row_x += 1
for col_x, value in enumerate(headers):
col = get_column_letter(col_x)
ws.cell('%s%s' % (col, row_x)).value = value
def chunks(l, n):
""" Yield successive n-sized chunks from l.
"""
for i in xrange(0, len(l), n):
yield l[i:i + n]
def write_xls(self, file_name, sheet_name, headings, data, data_xfs,
heading_xf=ezxf('font: bold on; align: wrap on, vert centre, horiz center')):
data = self.chunks(data, 65000)
book = xlwt.Workbook()
sn = 0
for dat in data:
sn += 1
sheet = book.add_sheet(sheet_name + str(sn))
rowx = 0
for colx, value in enumerate(headings):
sheet.write(rowx, colx, value, heading_xf)
sheet.set_panes_frozen(True) # frozen headings instead of split panes
sheet.set_horz_split_pos(rowx + 1) # in general, freeze after last heading row
sheet.set_remove_splits(True) # if user does unfreeze, don't leave a split there
for row in dat:
rowx += 1
for colx, value in enumerate(row):
sheet.write(rowx, colx, value, data_xfs[colx])
book.save(file_name)
def handle(self, **options):
try:
from uganda_common.utils import ExcelResponse
excel_file_path = \
os.path.join(os.path.join(os.path.join(UREPORT_ROOT,
'static'), 'spreadsheets'),
'ureporters.xlsx')
export_data_list = []
# messages=Message.objects.select_related(depth=1)
# black_listed=Blacklist.objects.values_list('connection__contact__pk',flat=True)
# print black_listed
# black_list_messages=messages.filter(connection__contact__in=black_listed)
# opt_words=settings.OPT_OUT_WORDS
if connection.connection is None:
cursor = connection.cursor()
cursor = connection.connection.cursor(name='contacts')
cursor.execute(self.sql)
row_0 = [
(
'Id',
'Language',
'Join Date',
'Quit Date',
'District',
'Age',
'Gender',
'Health Facility',
'Village',
'Subcounty',
'Group 1',
'Group 2',
'Group 3',
'How did you hear about ureport?',
'Number Of Responses',
'Number Of Questions Asked',
'Number of Incoming',
)
]
rows = row_0 + cursor.fetchall()
kinds = "int text date date text int text text text text text text text text text text text".split()
kind_to_xf_map = {
'date': ezxf(num_format_str='yyyy-mm-dd'),
'int': ezxf(num_format_str='#,##0'),
'text': ezxf(),
}
data_xfs = [kind_to_xf_map[k] for k in kinds]
ExcelResponse(rows, output_name=excel_file_path,
write_to_file=True)
#print rows
# self.write_xls(excel_file_path, 'ureporters', row_0, rows, data_xfs)
except Exception, exc:
print traceback.format_exc(exc)
# export the last 2 polls
polls = Poll.objects.order_by('-pk')
print "doxing"
for poll in polls:
if poll.responses.exists():
responses = poll.responses.all()
response_data_list = []
excel_file_path = \
os.path.join(os.path.join(os.path.join(UREPORT_ROOT,
'static'), 'spreadsheets'),
'poll_%d.xlsx' % poll.pk)
for response in responses:
response_export_data = SortedDict()
if response.contact:
response_export_data['contact_pk'] = response.contact.default_connection.pk
else:
response_export_data['contact_pk'] = ""
response_export_data['message_pk'] = response.message.pk
if response.contact and response.contact.language:
response_export_data['language'] = response.contact.language
else:
response_export_data['language'] = "en"
if response.contact and response.contact.gender:
response_export_data['sex'] = \
response.contact.gender
else:
response_export_data['sex'] = 'N/A'
if response.contact and response.contact.birthdate:
response_export_data['age'] = \
(datetime.datetime.now()
- response.contact.birthdate).days / 365
else:
response_export_data['age'] = 'N/A'
if response.contact \
and response.contact.reporting_location:
response_export_data['district'] = \
response.contact.reporting_location.name
else:
response_export_data['district'] = 'N/A'
if response.contact and response.contact.village:
response_export_data['village'] = \
response.contact.village_name
else:
response_export_data['village'] = 'N/A'
if response.contact and response.contact.subcounty:
response_export_data['subcounty'] = \
response.contact.subcounty.name
else:
response_export_data['subcounty'] = 'N/A'
if response.contact \
and response.contact.groups.count() > 0:
gr = list(response.contact.groups.order_by('pk').values_list('name', flat=True))
try:
response_export_data['group1'] = gr[0]
except IndexError:
response_export_data['group1'] = "N/A"
try:
response_export_data['group2'] = gr[1]
except IndexError:
response_export_data['group2'] = "N/A"
try:
response_export_data['group3'] = gr[2]
except IndexError:
response_export_data['group3'] = "N/A"
response_export_data['groups'] = \
','.join([group.name for group in
response.contact.groups.all()])
else:
response_export_data['groups'] = 'N/A'
response_export_data['group1'] = response_export_data['group2'] = response_export_data[
'group3'] = 'N/A'
if response.message:
response_export_data['response'] = \
response.message.text
response_export_data['date'] = \
response.message.date.strftime("%Y-%m-%d")
response_export_data['time'] = \
response.message.date.strftime("%H:%M:%S")
else:
response_export_data['response'] = ''
response_export_data['date'] = ''
response_export_data['time'] = ''
if response.poll:
response_export_data['question'] = \
response.poll.question
else:
response_export_data['question'] = ''
if response.categories.all().exists():
response_export_data['category'] = response.categories.all()[0].category.name
else:
response_export_data['category'] = "uncategorized"
response_data_list.append(response_export_data)
ExcelResponse(response_data_list,
output_name=excel_file_path,
write_to_file=True)
def a_dashboard(request, name):
poll_form = NewPollForm()
range_form = rangeForm()
poll_form.updateTypes()
template = 'ureport/aids_dashboard.html'
(capture_status, _) = \
Settings.objects.get_or_create(attribute='aids')
(rate, _) = MessageAttribute.objects.get_or_create(name='rating')
name = name.replace("_", " ")
flag = get_object_or_404(Flag, name=name)
access = get_access(request)
if access is not None and flag not in access.flags.all():
return render(request, '403.html', status=403)
flagged_messages = Message.objects.filter(flags__flag=flag)\
.select_related('connection__contact__reporting_location').order_by('-date')
responses = Message.objects.filter(
pk__in=flag.flagtracker_set.exclude(response=None).values_list("response", flat=True)
).select_related('connection__contact__reporting_location')
messages = flagged_messages | responses
if request.GET.get('download', None):
message_details = MessageDetail.objects.filter(message__id__in=flagged_messages.values_list('id', flat=True))\
.order_by('message__id')\
.select_related('message', 'attribute', 'message__connection__contact__reporting_location')
export_data, messages_with_details = _build_report(message_details)
messages_without_details = flagged_messages.exclude(id__in=messages_with_details)
export_data += _build_plain_message_export_data(messages_without_details)
headers = ['message_id', 'Connection ID', 'Message', 'Date', 'District', 'Rating', 'Replied', "Forwarded"]
return ExcelResponse(data=export_data, headers=headers)
if request.GET.get('capture', None):
(s, _) = Settings.objects.get_or_create(attribute='aids')
if s.value == 'true':
s.value = 'false'
s.save()
reply = gettext('Start Capture')
else:
s.value = 'true'
s.save()
reply = gettext('Stop Capture')
return HttpResponse(reply)
if request.GET.get('ajax', None):
return HttpResponse('success')
if request.GET.get('rating', None):
rating = request.GET.get('rating')
descs = {
'1': 'Requires Attention',
'2': 'Moderate',
'3': 'Important',
'4': 'Urgent',
'5': 'Very Urgent',
}
msg = Message.objects.get(pk=int(request.GET.get('msg')))
(rate, _) = MessageAttribute.objects.get_or_create(name='rating'
)
det = MessageDetail.objects.create(message=msg, attribute=rate,
value=rating, description=descs.get(rating, ''))
response = \
"""<li><a href='javascript:void(0)' class="rate%s"
title="%s">%s</a></li>""" \
% (rating, descs.get(rating, ''), descs.get(rating, ''))
return HttpResponse(mark_safe(response))
paginator = UreportPaginator(messages.order_by('-date'), 10, body=12, padding=2)
page = request.GET.get('page', 1)
try:
messages = paginator.page(page)
except (PageNotAnInteger, EmptyPage):
messages = paginator.page(1)
return render_to_response(template, {
'name': name,
'messages': messages,
'paginator': paginator,
'capture_status': capture_status,
'rate': rate,
'range_form': range_form,
}, context_instance=RequestContext(request))
def handle(self, **options):
try:
from uganda_common.utils import ExcelResponse
excel_file_path = os.path.join(os.path.join(os.path.join(UREPORT_ROOT, 'static'), 'spreadsheets'), 'ureporters_full.xls')
contacts = Contact.objects.all()
export_data_list = []
for contact in contacts:
if contact.name:
print "adding " + contact.name
export_data = SortedDict()
export_data['name'] = contact.name
if contact.default_connection:
export_data['mobile'] = contact.default_connection.identity
else:
export_data['mobile'] = "N/A"
if contact.gender:
export_data['sex'] = contact.gender
else:
export_data['sex'] = 'N/A'
if contact.birthdate:
#contact.birthdate.tzinfo = None
#import pdb;pdb.set_trace()
export_data['age'] = (datetime.datetime.now() - contact.birthdate).days / 365
else:
export_data['age'] = 'N/A'
if contact.reporting_location:
export_data['district'] = contact.reporting_location.name
else:
export_data['district'] = 'N/A'
if contact.village:
export_data['village'] = contact.village.name
else:
export_data['village'] = 'N/A'
if contact.groups.count() > 0:
export_data['group'] = contact.groups.all()[0].name
else:
export_data['group'] = 'N/A'
if ScriptSession.objects.filter(connection__contact=contact).exists():
export_data["join date"] = ScriptSession.objects.filter(connection__contact=contact)[0].start_time
elif contact.default_connection and contact.default_connection.messages.exists():
export_data["join date"] = contact.default_connection.messages.order_by('date')[0].date
else:
export_data["join date"] = "N/A"
export_data["Total Poll Responses"] = contact.responses.count()
mnum = 0
for m in Message.objects.filter(connection__contact=contact).order_by('date'):
export_data["message %d" % mnum] = m.text
export_data["message %d date" % mnum] = str(m.date)
mnum += 1
for i in range(mnum, 200):
export_data["message %d" % i] = ''
export_data["message %d date" % i] = ''
export_data_list.append(export_data)
ExcelResponse(export_data_list, output_name=excel_file_path, write_to_file=True)
except Exception, exc:
print traceback.format_exc(exc)
def ureporter_profile(request, connection_pk):
from script.models import ScriptSession, ScriptResponse, Script
connection = get_object_or_404(Connection, pk=connection_pk)
session = ScriptSession.objects.filter(connection__pk=connection_pk)
messages = Message.objects.filter(connection=connection).order_by('-date')
contact = connection.contact
if contact:
#get the proxy
contact = Ureporter.objects.get(pk=connection.contact.pk)
reporter_form = EditReporterForm(instance=contact)
total_outgoing = messages.filter(direction='O',
connection__pk=connection_pk).count()
total_incoming = messages.filter(direction='I',
connection__pk=connection_pk).count()
try:
response_rate = contact.responses.values_list(
'poll').distinct().count() * 100 / float(
Poll.objects.filter(contacts=contact).distinct().count())
except (ZeroDivisionError, ValueError):
response_rate = None
# gr_poll = Poll.objects.get(pk=121)
gr_poll = Script.objects.get(slug="autoreg_en").steps.get(order=1).poll
how_did_u_hear = None
if session:
try:
how_did_u_hear = \
session[0].responses.filter(response__poll=gr_poll)[0].response.message.text
except (ScriptResponse.DoesNotExist, IndexError):
how_did_u_hear = 'N/A'
if request.GET.get('download', None):
data = []
data = messages.values_list(
'text', 'direction', 'date',
'connection__contact__reporting_location__name').iterator()
headers = ['Message', 'Direction', 'Date', 'District']
return ExcelResponse(data=data, headers=headers)
columns = [('Message', True, 'text', SimpleSorter()),
('connection', True, 'connection', SimpleSorter()),
('Date', True, 'date', SimpleSorter()),
('Direction', True, 'direction', SimpleSorter())]
# hack hack send the reply message by hacking the sendmessage form
if request.method == 'POST':
if not request.POST.get('text', None) == u'' and request.POST.get(
'action'
) == u'ureport.forms.ReplyTextForm' and not request.POST.get(
'page_action', None):
rep_form = ReplyTextForm(request=request)
Message.objects.create(connection=connection,
direction='O',
status='Q',
text=request.POST.get('text'))
return generic(
request,
model=Message,
queryset=messages,
total_outgoing=total_outgoing,
total_incoming=total_incoming,
response_rate=response_rate,
how_did_u_hear=how_did_u_hear,
contact=contact,
reporter_form=reporter_form,
objects_per_page=20,
status_message='Message sent',
status_message_type='success',
results_title='Message History',
selectable=False,
partial_row=
'ureport/partials/messages/message_history_row.html',
base_template='ureport/message_history_base.html',
action_forms=[ReplyTextForm],
columns=columns,
sort_column='date',
sort_ascending=False,
)
return generic(
request,
model=Message,
queryset=messages,
contact=contact,
total_outgoing=total_outgoing,
total_incoming=total_incoming,
response_rate=response_rate,
objects_per_page=20,
how_did_u_hear=how_did_u_hear,
reporter_form=reporter_form,
results_title='Message History',
selectable=False,
partial_row='ureport/partials/messages/message_history_row.html',
base_template='ureport/message_history_base.html',
action_forms=[ReplyTextForm],
columns=columns,
sort_column='date',
sort_ascending=False,
)
def aids_dashboard(request):
select_poll = SelectPoll()
poll_form = NewPollForm()
range_form = rangeForm()
poll_form.updateTypes()
template = 'ureport/aids_dashboard.html'
(capture_status, _) = \
Settings.objects.get_or_create(attribute='aids')
(rate, _) = MessageAttribute.objects.get_or_create(name='rating')
flag = Flag.objects.get(name="HIV")
messages = flag.get_messages().order_by('-date')
if request.GET.get('download', None):
export_data = messages.values_list(
'connection__pk', 'text', 'connection__identity',
'connection__contact__reporting_location__name').iterator()
return ExcelResponse(data=export_data)
if request.GET.get('capture', None):
(s, _) = Settings.objects.get_or_create(attribute='aids')
if s.value == 'true':
s.value = 'false'
s.save()
reply = 'Start Capture'
else:
s.value = 'true'
s.save()
reply = 'Stop Capture'
return HttpResponse(reply)
if request.GET.get('ajax', None):
date = datetime.datetime.now() - datetime.timedelta(seconds=30)
prev = request.session.get('prev', [])
msgs = flag.get_messages().filter(date__gte=date).exclude(pk__in=prev)
request.session['prev'] = list(msgs.values_list('pk', flat=True))
msgs_list = []
if msgs:
for msg in msgs:
from django.template.loader import render_to_string
row_rendered = \
render_to_string('ureport/partials/row.html',
{'msg': msg})
m = {}
m['text'] = msg.text
m['date'] = str(msg.date.date())
if msg.connection.contact:
m['name'] = msg.connection.contact.name
else:
m['name'] = 'Anonymous User'
m['number'] = msg.connection.identity
if msg.connection.contact \
and msg.connection.contact.reporting_location:
m['district'] = \
msg.connection.contact.reporting_location.name
else:
m['district'] = 'N/A'
rating = msg.details.filter(attribute__name='aids')
if rating:
r = rating[0].value
else:
r = 0
m['row'] = row_rendered
m['connection'] = msg.connection.pk
m['pk'] = msg.pk
msgs_list.append(m)
return HttpResponse(mark_safe(simplejson.dumps(msgs_list)))
else:
return HttpResponse('success')
if request.GET.get('rating', None):
rating = request.GET.get('rating')
descs = {
'1': 'Requires Attention',
'2': 'Moderate',
'3': 'Important',
'4': 'Urgent',
'5': 'Very Urgent',
}
msg = Message.objects.get(pk=int(request.GET.get('msg')))
(rate, _) = MessageAttribute.objects.get_or_create(name='rating')
det = MessageDetail.objects.create(message=msg,
attribute=rate,
value=rating,
description=descs.get(rating, ''))
response = \
"""<li><a href='javascript:void(0)' class="rate%s"
title="%s">%s</a></li>""" \
% (rating, descs.get(rating, ''), descs.get(rating, ''))
return HttpResponse(mark_safe(response))
paginator = UreportPaginator(messages, 10, body=12, padding=2)
page = request.GET.get('page', 1)
try:
messages = paginator.page(page)
except (PageNotAnInteger, EmptyPage):
# If page is not an integer, deliver first page.
messages = paginator.page(1)
return render_to_response(template, {
'messages': messages,
'paginator': paginator,
'capture_status': capture_status,
'rate': rate,
'range_form': range_form,
},
context_instance=RequestContext(request))
def alerts(request):
access = get_access(request)
poll_form = NewPollForm()
range_form = rangeForm()
poll_form.updateTypes()
assign_polls = Poll.objects.exclude(start_date=None).order_by('-pk')[0:5]
district_form = DistrictForm(request.POST or None)
if request.GET.get('reset_districts', None):
request.session['districts'] = None
request.session['groups'] = None
if district_form.is_valid():
request.session['districts'] = [
c.pk for c in district_form.cleaned_data['districts']
]
groupform = AssignResponseGroupForm(request=request, access=access)
if request.method == 'POST' and request.POST.get('groups', None):
g_form = AssignResponseGroupForm(request.POST, request=request)
if g_form.is_valid():
request.session['groups'] = g_form.cleaned_data['groups']
template = 'ureport/polls/alerts.html'
if request.session.get('districts'):
message_list = \
Message.objects.filter(details__attribute__name='alert'
).filter(connection__contact__reporting_location__in=request.session.get('districts'))
else:
message_list = Message.objects.filter(details__attribute__name='alert')
if request.session.get('groups', None):
message_list = message_list.filter(
connection__contact__groups__in=request.session.get('groups'))
if access:
message_list = message_list.filter(
connection__contact__groups__in=access.groups.all())
(capture_status, _) = \
Settings.objects.get_or_create(attribute='alerts')
(rate, _) = MessageAttribute.objects.get_or_create(name='rating')
# message_list=[Message.objects.latest('date')]
# use more efficient count
if request.GET.get('download', None) and access is None:
range_form = rangeForm(request.POST)
if range_form.is_valid():
start = range_form.cleaned_data['startdate']
end = range_form.cleaned_data['enddate']
from django.core.servers.basehttp import FileWrapper
cols = [
"replied", "rating", "direction", "district", "date",
"message", "id", "forwarded"
]
data = AlertsExport.objects.filter(
date__range=(start, end)).values_list(*cols).iterator()
excel_file_path = \
os.path.join(os.path.join(os.path.join(UREPORT_ROOT,
'static'), 'spreadsheets'),
'alerts.xlsx')
ExcelResponse(data,
output_name=excel_file_path,
write_to_file=True,
headers=cols)
response = HttpResponse(
FileWrapper(open(excel_file_path)),
content_type=
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
)
response[
'Content-Disposition'] = 'attachment; filename=alerts.xlsx'
from django import db
db.reset_queries()
response['Cache-Control'] = 'no-cache'
return response
if request.GET.get('search', None):
search = request.GET.get('search')
if search[0] == '"' and search[-1] == '"':
search = search[1:-1]
message_list = message_list.filter(
Q(text__iregex=".*\m(%s)\y.*" % search)
| Q(connection__contact__reporting_location__name__iregex=
".*\m(%s)\y.*" % search)
| Q(connection__pk__iregex=".*\m(%s)\y.*" % search))
elif search[0] == "'" and search[-1] == "'":
search = search[1:-1]
message_list = message_list.filter(
Q(text__iexact=search)
|
Q(connection__contact__reporting_location__name__iexact=search)
| Q(connection__pk__iexact=search))
elif search == "=numerical value()":
message_list = message_list.filter(text__iregex="(-?\d+(\.\d+)?)")
else:
message_list = message_list.filter(
Q(text__icontains=search)
| Q(connection__contact__reporting_location__name__icontains=
search)
| Q(connection__pk__icontains=search))
if request.GET.get('capture', None):
(s, _) = Settings.objects.get_or_create(attribute='alerts')
if s.value == 'true':
s.value = 'false'
s.save()
reply = 'Start Capture'
else:
s.value = 'true'
s.save()
reply = 'Stop Capture'
return HttpResponse(reply)
if request.GET.get('ajax', None):
date = datetime.datetime.now() - datetime.timedelta(seconds=30)
prev = request.session.get('prev', [])
msgs = Message.objects.filter(
details__attribute__name='alert',
direction='I').filter(date__gte=date).exclude(pk__in=prev)
if access:
msgs = msgs.filter(
connection__contact__groups__in=access.groups.all())
request.session['prev'] = list(msgs.values_list('pk', flat=True))
msgs_list = []
if msgs:
for msg in msgs:
from django.template.loader import render_to_string
can_view_number = request.user.has_perm('view_numbers')
can_foward = request.user.has_perm('forward')
row_rendered = \
render_to_string('ureport/partials/row.html',
{'msg': msg, 'can_foward': can_foward, 'can_view_number': can_view_number,
'assign_polls': assign_polls})
m = {}
m['text'] = msg.text
m['date'] = str(msg.date.date())
if msg.connection.contact:
m['name'] = msg.connection.contact.name
else:
m['name'] = 'Anonymous User'
m['number'] = msg.connection.identity
if msg.connection.contact \
and msg.connection.contact.reporting_location:
m['district'] = \
msg.connection.contact.reporting_location.name
else:
m['district'] = 'N/A'
rating = msg.details.filter(attribute__name='alerts')
if rating:
r = rating[0].value
else:
r = 0
m['row'] = row_rendered
m['connection'] = msg.connection.pk
m['pk'] = msg.pk
msgs_list.append(m)
return HttpResponse(mark_safe(simplejson.dumps(msgs_list)))
else:
return HttpResponse('success')
if request.GET.get('rating', None):
rating = request.GET.get('rating')
descs = {
'1': 'Requires Attention',
'2': 'Moderate',
'3': 'Important',
'4': 'Urgent',
'5': 'Very Urgent',
}
msg = Message.objects.get(pk=int(request.GET.get('msg')))
(rate, _) = MessageAttribute.objects.get_or_create(name='rating')
det = MessageDetail.objects.create(message=msg,
attribute=rate,
value=rating,
description=descs.get(rating, ''))
response = \
"""<li><a href='javascript:void(0)' class="rate%s"
title="%s">%s</a></li>""" \
% (rating, descs.get(rating, ''), descs.get(rating, ''))
return HttpResponse(mark_safe(response))
paginator = UreportPaginator(message_list.order_by('-date'),
10,
body=12,
padding=2)
page = request.GET.get('page', 1)
try:
messages = paginator.page(page)
except (PageNotAnInteger, EmptyPage):
# If page is not an integer, deliver first page.
messages = paginator.page(1)
return render_to_response(template, {
'messages': messages,
'assign_polls': assign_polls,
'paginator': paginator,
'capture_status': capture_status,
'rate': rate,
'district_form': district_form,
'range_form': range_form,
'groupform': groupform,
},
context_instance=RequestContext(request))
def export_by_group(self, poll):
groups = Group.objects.all()
for group in groups:
print(group)
group_name = group.name
responses = Response.objects.filter(poll=poll.pk).filter(
contact__groups=group.pk)
response_data_list = []
excel_file_path = os.path.join(
os.path.join(settings.MEDIA_ROOT, 'downloads'),
'poll_%d_%s.xlsx' % (poll.pk, group.name))
if responses.count() != 0:
for response in responses:
response_export_data = SortedDict()
if response.contact:
try:
response_export_data[
'contact_pk'] = response.contact.connection_set.all(
)[0].pk
except IndexError:
continue
else:
response_export_data['contact_pk'] = ""
response_export_data['message_pk'] = response.message.pk
if response.contact and response.contact.language:
response_export_data[
'language'] = response.contact.language
else:
response_export_data['language'] = "en"
if response.contact and response.contact.gender:
response_export_data['sex'] = \
response.contact.gender
else:
response_export_data['sex'] = 'N/A'
if response.contact and response.contact.birthdate:
response_export_data['age'] = \
(datetime.datetime.now()
- response.contact.birthdate).days / 365
else:
response_export_data['age'] = 'N/A'
if response.contact \
and response.contact.reporting_location:
response_export_data['province'] = \
response.contact.reporting_location.name
else:
response_export_data['province'] = 'N/A'
if response.contact and response.contact.colline:
response_export_data['colline'] = \
response.contact.colline_name
else:
response_export_data['colline'] = 'N/A'
if response.contact and response.contact.commune:
response_export_data['commune'] = \
response.contact.commune.name
else:
response_export_data['commune'] = 'N/A'
if response.contact \
and response.contact.groups.count() > 0:
gr = list(
response.contact.groups.order_by('pk').values_list(
'name', flat=True))
try:
response_export_data['group1'] = gr[0]
except IndexError:
response_export_data['group1'] = "N/A"
try:
response_export_data['group2'] = gr[1]
except IndexError:
response_export_data['group2'] = "N/A"
try:
response_export_data['group3'] = gr[2]
except IndexError:
response_export_data['group3'] = "N/A"
response_export_data['groups'] = \
','.join([group.name for group in
response.contact.groups.all()])
else:
response_export_data['groups'] = 'N/A'
response_export_data['group1'] = response_export_data[
'group2'] = response_export_data['group3'] = 'N/A'
if response.message:
response_export_data['response'] = \
response.message.text
response_export_data['date'] = \
response.message.date.strftime("%Y-%m-%d")
response_export_data['time'] = \
response.message.date.strftime("%H:%M:%S")
else:
response_export_data['response'] = ''
response_export_data['date'] = ''
response_export_data['time'] = ''
if response.poll:
response_export_data['question'] = \
response.poll.question
else:
response_export_data['question'] = ''
if response.categories.all().exists():
response_export_data[
'category'] = response.categories.all(
)[0].category.name
else:
response_export_data['category'] = "uncategorized"
response_data_list.append(response_export_data)
ExcelResponse(response_data_list,
output_name=excel_file_path,
write_to_file=True)
def extract_gen_reports(form_data, **kwargs):
user = User.objects.get(username=kwargs.get('username'))
time_now = str(datetime.now()).replace(" ", "").replace("-", "").replace(
":", "").replace(".", "")
excel_file_path = \
os.path.join(os.path.join(os.path.join(UREPORT_ROOT,
'static'), 'spreadsheets'),
'ureport_general_report_at_%s.xlsx' % time_now)
link = "/static/ureport/spreadsheets/ureport_general_report_at_%s.xlsx" % time_now
message_list = []
def _get_messages(cleaned_data):
if cleaned_data['age_from'] > cleaned_data['age_to']:
age1 = cleaned_data['age_from']
age2 = cleaned_data['age_to']
else:
age1 = cleaned_data['age_to']
age2 = cleaned_data['age_from']
districts = cleaned_data['districts']
age_range = [
date.today() - timedelta(days=356 * age1),
date.today() - timedelta(days=356 * age2)
]
if cleaned_data['date_from'] < cleaned_data['date_to']:
date_range = [cleaned_data['date_from'], cleaned_data['date_to']]
else:
date_range = [cleaned_data['date_to'], cleaned_data['date_from']]
partner = cleaned_data['partner']
f = cleaned_data['filter']
gender = cleaned_data['gender']
messages = Message.objects.filter(
connection__contact__reporting_location__in=districts,
connection__contact__birthdate__range=age_range,
date__range=date_range,
connection__contact__groups__in=cleaned_data['groups'],
direction='I')
if f == 'U':
messages = messages.filter(poll_responses=None)
elif f == 'P':
messages = messages.exclude(poll_responses=None)
try:
access = Access.objects.get(user=partner)
messages = messages.filter(
connection__contact__groups__in=access.groups)
except:
pass
if gender != 'A':
messages = messages.filter(
connection__contact__gender__iexact=gender)
return messages.distinct()
def _get_messages_for_user(messages):
try:
access = Access.objects.get(user=user)
return messages.filter(
connection__contact__groups__in=access.groups)
except Access.DoesNotExist:
return messages
queryset = _get_messages(form_data)
queryset = _get_messages_for_user(queryset)
for message in queryset:
message_list_dict = SortedDict()
message_list_dict['ID'] = message.connection_id
message_list_dict['text'] = message.text
message_list_dict['sent on'] = message.date
if not message.poll_responses.exists():
message_list_dict['poll'] = 'Unsolicited'
else:
message_list_dict['poll'] = message.poll_responses.all(
)[0].poll.question
message_list_dict['birth date'] = 'N/A'
message_list_dict['district'] = 'N/A'
if message.connection.contact:
if message.connection.contact.birthdate:
message_list_dict[
'birth date'] = message.connection.contact.birthdate
else:
message_list_dict['birth date'] = 'N/A'
if message.connection.contact.reporting_location:
message_list_dict[
'district'] = message.connection.contact.reporting_location
message_list.append(message_list_dict)
ExcelResponse(message_list,
output_name=excel_file_path,
write_to_file=True)
host = kwargs.get('host')
if user.email:
msg = "Hi %s,\nThe excel report that you requested to download is now ready for download. Please visit %s%s" \
" and download it.\n\nThank You\nUreport Team" % (user.username, host, link)
send_mail('General Ureport Report',
msg,
"", [user.email],
fail_silently=False)