def export_message_tracker_details_to_excel(modeladmin, request, query_set):
headers = ["Organization Name", "Organization Id","Type", "Month", "Total Incoming Submissions (In total)", "Total Incoming Submissions",
"Total SMS (incoming and outgoing)", "Outgoing Charged SMS: Total", "Outgoing SMS: Auto Reply", "Outgoing Charged SMS: Auto Reply",
"Outgoing SMS: Reminders", "Outgoing Charged SMS: Reminders", "Outgoing SMS: Send Message", "Outgoing Charged SMS: Send Message",
"Outgoing SMS: API", "Outgoing Charged SMS: API", "SMS Submissions", "SP Submissions", "Web Submissions", "SMS Subject Registration"]
list = []
textSearchFilter = get_text_search_filter(request.GET,MessageTrackerAdmin.search_fields)
adminPanelFilter = get_admin_panel_filter(request.GET)
filteredSms = MessageTracker.objects.all().filter(Q(**adminPanelFilter) & (textSearchFilter))
for messageTracker in filteredSms:
sms_tracker_month = ExcelDate(datetime.datetime.combine(messageTracker.month, datetime.datetime.min.time()),
'dd.mm.yyyy') if messageTracker.month else None
list.append([modeladmin.organization_name(messageTracker), modeladmin.organization_id(messageTracker),
modeladmin.type(messageTracker),
sms_tracker_month, messageTracker.total_incoming_in_total(),
messageTracker.total_monthly_incoming_messages(),
messageTracker.total_messages(), messageTracker.outgoing_message_count(),
messageTracker.outgoing_sms_count, messageTracker.outgoing_sms_charged_count,
messageTracker.sent_reminders_count, messageTracker.sent_reminders_charged_count,
messageTracker.send_message_count,
messageTracker.send_message_charged_count, messageTracker.sms_api_usage_count,
messageTracker.sms_api_usage_charged_count,
modeladmin.sms_submission(messageTracker), messageTracker.incoming_sp_count,
messageTracker.incoming_web_count, messageTracker.sms_registration_count])
response = export_to_new_excel(headers, list, 'tracker_list')
return response
def export_user_list_to_excel(a,b,c):
#Custom Method to export user details.
def is_required(user):
return True if user.groups.filter(name="NGO Admins").count() or user.groups.filter(name="Project Managers").count() \
or user.groups.filter(name="Extended Users").count()else False
def user_role(user):
if user.groups.filter(name='NGO Admins').count():
return 'Admin'
elif user.groups.filter(name='Project Managers').count():
return 'User'
list = []
for ngo_user in NGOUserProfile.objects.all():
try:
user = User.objects.get(id=ngo_user.user_id)
if is_required(user) and not user.is_superuser:
details = []
details.append(user.first_name + ' ' + user.last_name)
details.append(user.username)
org_id = ngo_user.org_id
organization = Organization.objects.get(org_id = org_id)
details.append(organization.name)
details.append(organization.status)
details.append(organization.language)
details.append(user_role(user))
list.append(details)
except Exception:
continue
headers = ['Name', 'email', 'Organization Name', 'Status', 'Account language','User Role']
response = export_to_new_excel(headers,list,'user_list')
return response
def export_sms_details_to_excel(modeladmin, request, query_set):
list = []
textSearchFilter = get_text_search_filter(request.GET,
SMSAdmin.search_fields)
adminPanelFilter = get_admin_panel_filter(request.GET)
filteredSms = SMS.objects.all().filter(
Q(**adminPanelFilter) & (textSearchFilter))
for sms in filteredSms:
delivered_date_time = ExcelDate(
datetime.combine(sms.delivered_at, datetime.min.time()),
'dd.mm.yyyy') if sms.delivered_at else None
creation_date_time = ExcelDate(
datetime.combine(sms.created_at, datetime.min.time()),
'dd.mm.yyyy') if sms.created_at else None
list.append([
sms.organization_id, sms.status, creation_date_time,
delivered_date_time, sms.msg_from, sms.msg_to, sms.msg_type,
sms.message
])
headers = [
'Organisation Id', 'Status', 'Creation Date', 'Delivery Date',
'Message from Number', 'Message to Number', 'Message Type',
'Content'
]
response = export_to_new_excel(headers, list, 'sms_list')
return response
def create_workbook_response(self, data, field_codes):
field_codes.insert(0, self.form_code)
headers = OrderedDict()
headers[self.sheet_name] = data[0]
headers['codes'] = field_codes
return export_to_new_excel(headers, {},
self.file_name,
hide_codes_sheet=True,
browser=self.browser)
def _create_response(self,
columns,
submission_list,
submission_type,
hide_codes_sheet=False):
header_list, submission_formatter = self._get_header_list(columns)
return export_to_new_excel(header_list,
submission_list,
export_filename(submission_type,
self.project_name),
submission_formatter,
hide_codes_sheet,
questionnaire=self.form_model)
def test_should_export_data_in_xlsx(self):
self.format_row_patch.side_effect = lambda x: x
file_response = export_to_new_excel(
self.headers, self.data, 'filename',
SubmissionFormatter(self.headers, None))
self.assertTrue(isinstance(file_response, HttpResponse))
self.assertEquals(file_response.get('Content-Disposition', None),
"attachment; filename=filename.xlsx")
f = StringIO.StringIO(file_response.content)
wb = load_workbook(f)
ws = wb.get_active_sheet()
self.assertEqual(len(ws.rows), 2)
self.assertEqual(len(ws.columns), 14)
f.close()
def test_should_export_data_in_xlsx(self):
local_time_delta = '+', 0, 0
submission_formatter = SubmissionFormatter(self.columns,
local_time_delta,
self.preferences)
header_list = submission_formatter.format_header_data()
file_response = export_to_new_excel(header_list, self.data, 'filename',
submission_formatter)
self.assertTrue(isinstance(file_response, HttpResponse))
self.assertEquals(file_response.get('Content-Disposition', None),
"attachment; filename=filename.xlsx")
f = StringIO.StringIO(file_response.content)
wb = load_workbook(f)
ws = wb.get_active_sheet()
row_values = [[cell.value for cell in row] for row in ws.rows]
expected_row_values = [[
u'Submission Date', u'Data Sender Name', u'Data Sender Id',
u"What is the city's last name?",
u"What is the city's Unique ID Number?", u'Total sales'
],
[
datetime.datetime(2016, 1, 30, 19, 32, 59,
999997), u'Einstein',
u'rep2', u'chennai', u'cit1', 5000
]]
column_values = [[cell.value for cell in column]
for column in ws.columns]
expected_column_values = [[
u'Submission Date',
datetime.datetime(2016, 1, 30, 19, 32, 59, 999997)
], [u'Data Sender Name', u'Einstein'
], [u'Data Sender Id', u'rep2'
], [u"What is the city's last name?", u'chennai'
], [u"What is the city's Unique ID Number?", u'cit1'],
[u'Total sales', 5000]]
self.assertEqual(len(ws.rows), 2)
self.assertEqual(len(ws.columns), 6)
self.assertEqual(row_values, expected_row_values)
self.assertEqual(column_values, expected_column_values)
f.close()
def export_subject(request):
manager = get_database_manager(request.user)
query_text = request.POST.get('query_text', '')
subject_type = request.POST.get('subject_type', '').lower()
subject_list = SubjectQuery().query(request.user, subject_type, query_text)
form_model = get_form_model_by_entity_type(manager, [subject_type.lower()])
response = HttpResponse(mimetype='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename="%s.xls"' % (
subject_type, )
field_codes = form_model.field_codes()
field_codes.insert(0, form_model.form_code)
labels = get_subject_headers(form_model.form_fields)
raw_data = []
headers = OrderedDict([(subject_type, labels), ("codes", field_codes)])
for subject in subject_list:
raw_data.append(subject)
return export_to_new_excel(headers, {subject_type: raw_data},
subject_type,
hide_codes_sheet=True)
def _create_response(self, columns, submission_list, submission_type):
header_list, submission_formatter = self._get_header_list(columns)
return export_to_new_excel(header_list, submission_list, export_filename(submission_type, self.project_name),
submission_formatter)
