def get_request_field_list(csas_request, user): my_list = [ 'id|{}'.format(_("request Id")), 'fiscal_year', 'status_display|{}'.format(_("status")), 'is_carry_over|{}'.format(_("is carry over?")), 'language', 'section', 'coordinator', 'client', 'multiregional_display|{}'.format(_("Multiregional / multisector?")), 'issue_html|{}'.format(get_verbose_label(csas_request, "issue")), 'assistance_display|{}'.format(_("Assistance from DFO Science?")), 'rationale_html|{}'.format(get_verbose_label(csas_request, "rationale")), 'risk_text', 'advice_needed_by', 'rationale_for_timeline', 'funding_display|{}'.format(_("client funding?")), 'prioritization_display|{}'.format(_("client prioritization")), 'submission_date', 'uuid', 'metadata|{}'.format(_("metadata")), ] while None in my_list: my_list.remove(None) return my_list
def generate_summary_spreadsheet(orgs, sectors, from_date, to_date, entry_note_types, entry_note_statuses): # figure out the filename target_dir = os.path.join(settings.BASE_DIR, 'media', 'ihub', 'temp') target_file = "temp_data_export_{}.xlsx".format( timezone.now().strftime("%Y-%m-%d")) target_file_path = os.path.join(target_dir, target_file) target_url = os.path.join(settings.MEDIA_ROOT, 'ihub', 'temp', target_file) # create workbook and worksheets workbook = xlsxwriter.Workbook(target_file_path) # create formatting title_format = workbook.add_format({ 'bold': True, "align": 'normal', 'font_size': 24, }) header_format = workbook.add_format({ 'bold': True, 'border': 1, 'border_color': 'black', 'bg_color': '#D6D1C0', "align": 'normal', "text_wrap": True }) total_format = workbook.add_format({ 'bold': True, "align": 'left', "text_wrap": True, 'num_format': '$#,##0' }) normal_format = workbook.add_format({ "align": 'left', "text_wrap": True, 'num_format': '$#,##0' }) # first, filter out the "none" placeholder if sectors == "None": sectors = None if orgs == "None": orgs = None if from_date == "None": from_date = None if to_date == "None": to_date = None if entry_note_types == "None": entry_note_types = None else: entry_note_types = [int(i) for i in entry_note_types.split(",") ] if entry_note_types else None if entry_note_statuses == "None": entry_note_statuses = None else: entry_note_statuses = [int(i) for i in entry_note_statuses.split(",") ] if entry_note_statuses else None # build an entry list: entry_list = models.Entry.objects.all() if sectors: # we have to refine the queryset to only the selected sectors sector_list = [ ml_models.Sector.objects.get(pk=int(s)) for s in sectors.split(",") ] entry_list = entry_list.filter(sectors__in=sector_list) if orgs: # we have to refine the queryset to only the selected orgs org_list = [ ml_models.Organization.objects.get(pk=int(o)) for o in orgs.split(",") ] entry_list = entry_list.filter(organizations__in=org_list) else: # if no orgs were passed in to the report, we need to make an org list based on the orgs in the entries # this org_list will serve as basis for spreadsheet tabs org_id_list = list( set([ org.id for entry in entry_list for org in entry.organizations.all() ])) org_list = ml_models.Organization.objects.filter( id__in=org_id_list).order_by("abbrev") if from_date or to_date: id_list = [] d0_start = datetime.strptime(from_date, "%Y-%m-%d").replace( tzinfo=timezone.get_current_timezone()) if from_date else None d0_end = datetime.strptime(to_date, "%Y-%m-%d").replace( tzinfo=timezone.get_current_timezone()) if to_date else None for e in entry_list: d1_start = e.initial_date d1_end = e.anticipated_end_date if get_date_range_overlap(d0_start, d0_end, d1_start, d1_end) > 0: id_list.append(e.id) entry_list = entry_list.filter(id__in=id_list) entry_list.distinct() # define the header header = [ get_verbose_label(entry_list.first(), 'fiscal_year'), get_verbose_label(entry_list.first(), 'title'), get_verbose_label(entry_list.first(), 'organizations'), get_verbose_label(entry_list.first(), 'status'), get_verbose_label(entry_list.first(), 'sectors'), get_verbose_label(entry_list.first(), 'entry_type'), get_verbose_label(entry_list.first(), 'initial_date'), get_verbose_label(entry_list.first(), 'anticipated_end_date'), get_verbose_label(entry_list.first(), 'regions'), _("DFO Contacts"), _("Notes"), get_verbose_label(entry_list.first(), 'funding_program'), get_verbose_label(entry_list.first(), 'funding_needed'), get_verbose_label(entry_list.first(), 'funding_purpose'), get_verbose_label(entry_list.first(), 'amount_requested'), get_verbose_label(entry_list.first(), 'amount_approved'), get_verbose_label(entry_list.first(), 'amount_transferred'), get_verbose_label(entry_list.first(), 'amount_lapsed'), _("Amount outstanding"), ] # worksheets # ############## # each org should be represented on a separate worksheet # therefore determine an appropriate org list org_counter = 0 for org in org_list: org_abbrev = slugify( org.abbrev) if org.abbrev else f"missing_abbrev_{org_counter}" org_counter += 1 my_ws = workbook.add_worksheet(name=org_abbrev) # create the col_max column to store the length of each header # should be a maximum column width to 100 col_max = [len(str(d)) if len(str(d)) <= 100 else 100 for d in header] my_ws.write(0, 0, str(org), title_format) my_ws.write_row(2, 0, header, header_format) tot_requested = 0 tot_approved = 0 tot_transferred = 0 tot_lapsed = 0 tot_outstanding = 0 i = 3 for e in entry_list.filter(organizations=org): if e.organizations.count() > 0: orgs = str([str(obj) for obj in e.organizations.all() ]).replace("[", "").replace("]", "").replace( "'", "").replace('"', "").replace(', ', "\n") else: orgs = None if e.people.count() > 0: people = str([ "{} - {} ({})".format(obj.get_role_display(), obj, obj.organization) for obj in e.people.all() ]).replace("[", "").replace("]", "").replace("'", "").replace( '"', "").replace(', ', "\n") else: people = None note_qry = e.notes.all() if note_qry.count() > 0: notes = "" count = 0 max_count = note_qry.count() for obj in note_qry: if not entry_note_types or (obj.type in entry_note_types): if not entry_note_statuses or (obj.status_id in entry_note_statuses): notes += "{} - {} [STATUS: {}] (Created by {} {} on {})\n".format( obj.get_type_display().upper(), obj.note, obj.status, obj.author.first_name if obj.author else "", obj.author.last_name if obj.author else "", obj.creation_date.strftime("%Y-%m-%d"), ) if not count == max_count: notes += "\n" else: notes = None if e.sectors.count() > 0: sectors = str([str(obj) for obj in e.sectors.all() ]).replace("[", "").replace("]", "").replace( "'", "").replace('"', "").replace(', ', "\n") else: sectors = None if e.regions.count() > 0: regions = str([str(obj) for obj in e.regions.all()]).replace( "[", "").replace("]", "").replace("'", "").replace('"', "") else: regions = None data_row = [ e.fiscal_year, e.title, orgs, str(e.status), sectors, str(e.entry_type), e.initial_date.strftime("%Y-%m-%d") if e.initial_date else "n/a", e.anticipated_end_date.strftime("%Y-%m-%d") if e.anticipated_end_date else "", regions, people, notes, nz(str(e.funding_program), ""), yesno(e.funding_needed), nz(str(e.funding_purpose), ""), nz(e.amount_requested, 0), nz(e.amount_approved, 0), nz(e.amount_transferred, 0), nz(e.amount_lapsed, 0), nz(e.amount_outstanding, 0), ] tot_requested += nz(e.amount_requested, 0) tot_approved += nz(e.amount_approved, 0) tot_transferred += nz(e.amount_transferred, 0) tot_lapsed += nz(e.amount_lapsed, 0) tot_outstanding += nz(e.amount_outstanding, 0) # adjust the width of the columns based on the max string length in each col ## replace col_max[j] if str length j is bigger than stored value j = 0 for d in data_row: # if new value > stored value... replace stored value if len(str(d)) > col_max[j]: if len(str(d)) < 75: col_max[j] = len(str(d)) else: col_max[j] = 75 j += 1 my_ws.write_row(i, 0, data_row, normal_format) i += 1 # set column widths for j in range(0, len(col_max)): my_ws.set_column(j, j, width=col_max[j] * 1.1) # sum all the currency columns total_row = [ _("GRAND TOTAL:"), tot_requested, tot_approved, tot_transferred, tot_lapsed, tot_outstanding, ] try: my_ws.write_row(i + 2, header.index(_("Funding requested")) - 1, total_row, total_format) # set formatting for status for status in models.Status.objects.all(): my_ws.conditional_format( 0, header.index(_("status").title()), i, header.index(_("status").title()), { 'type': 'cell', 'criteria': 'equal to', 'value': '"{}"'.format(status.name), 'format': workbook.add_format({ 'bg_color': status.color, }), }) # set formatting for entry type for entry_type in models.EntryType.objects.all(): my_ws.conditional_format( 0, header.index(_("Entry Type").title()), i, header.index(_("Entry Type").title()), { 'type': 'cell', 'criteria': 'equal to', 'value': '"{}"'.format(entry_type.name), 'format': workbook.add_format({ 'bg_color': entry_type.color, }), }) except: print("problem with summary row") i += 1 workbook.close() return target_url
def generate_capacity_spreadsheet(fy, orgs, sectors): # figure out the filename target_dir = os.path.join(settings.BASE_DIR, 'media', 'ihub', 'temp') target_file = "temp_data_export_{}.xlsx".format( timezone.now().strftime("%Y-%m-%d")) target_file_path = os.path.join(target_dir, target_file) target_url = os.path.join(settings.MEDIA_ROOT, 'ihub', 'temp', target_file) # create workbook and worksheets workbook = xlsxwriter.Workbook(target_file_path) # create formatting title_format = workbook.add_format({ 'bold': True, "align": 'normal', 'font_size': 24, }) header_format = workbook.add_format({ 'bold': True, 'border': 1, 'border_color': 'black', 'bg_color': '#D6D1C0', "align": 'normal', "text_wrap": True }) total_format = workbook.add_format({ 'bold': True, "align": 'left', "text_wrap": True, 'num_format': '$#,##0' }) normal_format = workbook.add_format({ "align": 'left', "text_wrap": True, 'num_format': '$#,##0' }) # first, filter out the "none" placeholder if fy == "None": fy = None if orgs == "None": orgs = None if sectors == "None": sectors = None # build an entry list: entry_list = models.Entry.objects.all() if fy: entry_list = models.Entry.objects.filter(fiscal_year=fy) if sectors: # we have to refine the queryset to only the selected sectors sector_list = [ ml_models.Sector.objects.get(pk=int(s)) for s in sectors.split(",") ] entry_list = entry_list.filter(sectors__in=sector_list) if orgs: # we have to refine the queryset to only the selected orgs org_list = [ ml_models.Organization.objects.get(pk=int(o)) for o in orgs.split(",") ] entry_list = entry_list.filter(organizations__in=org_list) else: # if no orgs were passed in to the report, we need to make an org list based on the orgs in the entries # this org_list will serve as basis for spreadsheet tabs org_id_list = list( set([ org.id for entry in entry_list for org in entry.organizations.all() ])) org_list = ml_models.Organization.objects.filter( id__in=org_id_list).order_by("abbrev") # define the header header = [ get_verbose_label(entry_list.first(), 'fiscal_year'), get_verbose_label(entry_list.first(), 'title'), get_verbose_label(entry_list.first(), 'organizations'), get_verbose_label(entry_list.first(), 'status'), get_verbose_label(entry_list.first(), 'sectors'), get_verbose_label(entry_list.first(), 'entry_type'), get_verbose_label(entry_list.first(), 'initial_date'), get_verbose_label(entry_list.first(), 'anticipated_end_date'), get_verbose_label(entry_list.first(), 'regions'), get_verbose_label(entry_list.first(), 'funding_program'), get_verbose_label(entry_list.first(), 'funding_needed'), get_verbose_label(entry_list.first(), 'funding_purpose'), get_verbose_label(entry_list.first(), 'amount_requested'), get_verbose_label(entry_list.first(), 'amount_approved'), get_verbose_label(entry_list.first(), 'amount_transferred'), get_verbose_label(entry_list.first(), 'amount_lapsed'), _("Amount outstanding"), ] # worksheets # ############## for org in org_list: my_ws = workbook.add_worksheet(name=org.abbrev) # create the col_max column to store the length of each header # should be a maximum column width to 100 col_max = [len(str(d)) if len(str(d)) <= 100 else 100 for d in header] my_ws.write(0, 0, str(org), title_format) my_ws.write_row(2, 0, header, header_format) tot_requested = 0 tot_approved = 0 tot_transferred = 0 tot_lapsed = 0 tot_outstanding = 0 i = 3 for e in entry_list.filter(organizations=org): if e.organizations.count() > 0: orgs = str([str(obj) for obj in e.organizations.all() ]).replace("[", "").replace("]", "").replace( "'", "").replace('"', "") else: orgs = None if e.sectors.count() > 0: sectors = str([str(obj) for obj in e.sectors.all()]).replace( "[", "").replace("]", "").replace("'", "").replace('"', "") else: sectors = None if e.regions.count() > 0: regions = str([str(obj) for obj in e.regions.all()]).replace( "[", "").replace("]", "").replace("'", "").replace('"', "") else: regions = None data_row = [ e.fiscal_year, e.title, orgs, str(e.status), sectors, str(e.entry_type), e.initial_date.strftime("%Y-%m-%d") if e.initial_date else "n/a", e.anticipated_end_date.strftime("%Y-%m-%d") if e.anticipated_end_date else "", regions, nz(str(e.funding_program), ""), nz(str(e.funding_needed), ""), nz(str(e.funding_purpose), ""), nz(e.amount_requested, 0), nz(e.amount_approved, 0), nz(e.amount_transferred, 0), nz(e.amount_lapsed, 0), nz(e.amount_outstanding, 0), ] tot_requested += nz(e.amount_requested, 0) tot_approved += nz(e.amount_approved, 0) tot_transferred += nz(e.amount_transferred, 0) tot_lapsed += nz(e.amount_lapsed, 0) tot_outstanding += nz(e.amount_outstanding, 0) # adjust the width of the columns based on the max string length in each col ## replace col_max[j] if str length j is bigger than stored value j = 0 for d in data_row: # if new value > stored value... replace stored value if len(str(d)) > col_max[j]: if len(str(d)) < 100: col_max[j] = len(str(d)) else: col_max[j] = 100 j += 1 my_ws.write_row(i, 0, data_row, normal_format) i += 1 # set column widths for j in range(0, len(col_max)): my_ws.set_column(j, j, width=col_max[j] * 1.1) # sum all the currency columns total_row = [ _("GRAND TOTAL:"), tot_requested, tot_approved, tot_transferred, tot_lapsed, tot_outstanding, ] try: my_ws.write_row(i + 2, header.index(_("Funding requested")) - 1, total_row, total_format) # set formatting for status for status in models.Status.objects.all(): my_ws.conditional_format( 0, header.index(_("status").title()), i, header.index(_("status").title()), { 'type': 'cell', 'criteria': 'equal to', 'value': '"{}"'.format(status.name), 'format': workbook.add_format({ 'bg_color': status.color, }), }) # set formatting for entry type for entry_type in models.EntryType.objects.all(): my_ws.conditional_format( 0, header.index(_("Entry Type").title()), i, header.index(_("Entry Type").title()), { 'type': 'cell', 'criteria': 'equal to', 'value': '"{}"'.format(entry_type.name), 'format': workbook.add_format({ 'bg_color': entry_type.color, }), }) except: print("problem with summary row") workbook.close() return target_url
def generate_project_list(user, year, region, section): # Create the HttpResponse object with the appropriate CSV header. response = HttpResponse(content_type='text/csv') response.write(u'\ufeff'.encode( 'utf8')) # BOM (optional...Excel needs it to open UTF-8 file properly) writer = csv.writer(response) status_choices = models.ProjectYear.status_choices fields = [ 'region', 'division', 'project.section|section', 'project.id|Project Id', 'fiscal_year', 'project.title|title', 'Overview', 'Overview word count', 'project.default_funding_source|Primary funding source', 'project.functional_group|Functional group', 'Project leads', 'status', 'updated_at|Last modified date', 'modified_by|Last modified by', 'Last modified description', 'Activity count', 'Staff count', 'Sum of staff FTE (weeks)', 'Sum of costs', ] if in_projects_admin_group(user): qs = ProjectYear.objects.filter(fiscal_year_id=year).distinct() if section != "None": qs = qs.filter(project__section_id=section) elif region != "None": qs = qs.filter( project__section__division__branch__region_id=region) else: sections = utils.get_manageable_sections(user) qs = ProjectYear.objects.filter( project__section__in=sections).distinct() header_row = [ get_verbose_label(ProjectYear.objects.first(), header) for header in fields ] writer.writerow(header_row) for obj in qs: data_row = list() for field in fields: if "division" in field: val = " ---" if obj.project.section: val = obj.project.section.division.tname elif "region" in field: val = " ---" if obj.project.section: val = obj.project.section.division.branch.region.tname elif "leads" in field: val = listrify(obj.get_project_leads_as_users()) elif "updated_at" in field: val = obj.updated_at.strftime("%Y-%m-%d") elif "Last modified description" in field: val = naturaltime(obj.updated_at) elif field == "Overview": val = html2text(nz(obj.project.overview_html, "")) elif field == "Overview word count": val = len( html2text(nz(obj.project.overview_html, "")).split(" ")) elif field == "Activity count": val = obj.activities.count() elif field == "Staff count": val = obj.staff_set.count() elif field == "Sum of staff FTE (weeks)": val = obj.staff_set.order_by("duration_weeks").aggregate( dsum=Sum("duration_weeks"))["dsum"] elif field == "Sum of costs": val = nz(obj.omcost_set.filter(amount__isnull=False).aggregate(dsum=Sum("amount"))["dsum"], 0) + \ nz(obj.capitalcost_set.filter(amount__isnull=False).aggregate(dsum=Sum("amount"))["dsum"], 0) + \ nz(obj.staff_set.filter(amount__isnull=False).aggregate(dsum=Sum("amount"))["dsum"], 0) else: val = get_field_value(obj, field) data_row.append(val) writer.writerow(data_row) return response
def generate_dive_log(year): # figure out the filename target_dir = os.path.join(settings.BASE_DIR, 'media', 'temp') target_file = "temp_data_export_{}.xlsx".format( timezone.now().strftime("%Y-%m-%d")) target_file_path = os.path.join(target_dir, target_file) target_url = os.path.join(settings.MEDIA_ROOT, 'temp', target_file) # create workbook and worksheets workbook = xlsxwriter.Workbook(target_file_path) # create formatting variables title_format = workbook.add_format({ 'bold': True, "align": 'normal', 'font_size': 24, }) header_format = workbook.add_format({ 'bold': True, 'border': 1, 'border_color': 'black', "align": 'normal', "text_wrap": True }) total_format = workbook.add_format({ 'bold': True, "align": 'left', "text_wrap": True, 'num_format': '$#,##0' }) normal_format = workbook.add_format({ "align": 'left', "text_wrap": True, 'border': 1, 'border_color': 'black', }) currency_format = workbook.add_format({'num_format': '#,##0.00'}) date_format = workbook.add_format({ 'num_format': "yyyy-mm-dd", "align": 'left', }) # get the dive list dives = models.Dive.objects.all() if year: dives = dives.filter(sample__datetime__year=year) field_list = [ "datetime|Date", "site|Region/Site", "diver", "psi_in", "psi_out", "start_descent", "bottom_time", "max_depth_ft", ] # get_cost_comparison_dict # define the header header = [get_verbose_label(dives.first(), field) for field in field_list] # header.append('Number of projects tagged') title = "res Dive Log" # define a worksheet my_ws = workbook.add_worksheet(name="trip list") my_ws.write(0, 0, title, title_format) my_ws.write_row(2, 0, header, header_format) i = 3 for dive in dives.order_by("sample__datetime"): # create the col_max column to store the length of each header # should be a maximum column width to 100 col_max = [len(str(d)) if len(str(d)) <= 100 else 100 for d in header] j = 0 for field in field_list: if "datetime" in field: my_val = dive.sample.datetime.strftime("%Y-%m-%d") my_ws.write(i, j, my_val, date_format) elif "site" in field: my_val = f"{dive.sample.site.region.name} / {dive.sample.site.name}" my_ws.write(i, j, my_val, normal_format) else: my_val = str(get_field_value(dive, field)) my_ws.write(i, j, my_val, normal_format) # adjust the width of the columns based on the max string length in each col ## replace col_max[j] if str length j is bigger than stored value # if new value > stored value... replace stored value if len(str(my_val)) > col_max[j]: if len(str(my_val)) < 75: col_max[j] = len(str(my_val)) else: col_max[j] = 75 j += 1 i += 1 # set column widths for j in range(0, len(col_max)): my_ws.set_column(j, j, width=col_max[j] * 1.1) workbook.close() return target_url
def generate_process_list(processes): # figure out the filename target_dir = os.path.join(settings.BASE_DIR, 'media', 'temp') target_file = "temp_data_export_{}.xlsx".format( timezone.now().strftime("%Y-%m-%d")) target_file_path = os.path.join(target_dir, target_file) target_url = os.path.join(settings.MEDIA_ROOT, 'temp', target_file) # create workbook and worksheets workbook = xlsxwriter.Workbook(target_file_path) # create formatting variables title_format = workbook.add_format({ 'bold': True, "align": 'normal', 'font_size': 24, }) header_format = workbook.add_format({ 'bold': True, 'border': 1, 'border_color': 'black', "align": 'normal', "text_wrap": True }) total_format = workbook.add_format({ 'bold': True, "align": 'left', "text_wrap": True, 'num_format': '$#,##0' }) normal_format = workbook.add_format({ "align": 'left', "text_wrap": True, 'border': 1, 'border_color': 'black', }) currency_format = workbook.add_format({'num_format': '#,##0.00'}) date_format = workbook.add_format({ 'num_format': "yyyy-mm-dd", "align": 'left', }) field_list = [ 'id', 'fiscal_year', 'name', 'scope_type|{}'.format(_("Advisory process type")), 'status', 'science_leads|{}'.format(_("Lead scientists")), 'chair|{}'.format(_("chair")), 'coordinator', 'advisors', 'lead_office', 'other_offices', 'expected_publications|{}'.format(_("expected publications")), 'key_meetings|{}'.format(_("key meetings")), 'doc_summary|{}'.format(_("document summary")), 'formatted_notes|{}'.format(_("notes")), ] # define the header header = [ get_verbose_label(processes.first(), field) for field in field_list ] title = "CSAS Process List" # define a worksheet my_ws = workbook.add_worksheet(name="requests") my_ws.write(0, 0, title, title_format) my_ws.write_row(2, 0, header, header_format) col_max = [len(str(d)) if len(str(d)) <= 100 else 100 for d in header] i = 3 for obj in processes: # create the col_max column to store the length of each header # should be a maximum column width to 100 j = 0 for field in field_list: if "other_regions" in field: my_val = listrify(obj.other_offices.all()) my_ws.write(i, j, my_val, normal_format) elif "advisors" in field: my_val = listrify(obj.advisors.all()) my_ws.write(i, j, my_val, normal_format) elif "expected publications" in field: if hasattr(obj, "tor"): my_val = listrify(obj.tor.expected_document_types.all()) else: my_val = "n/a" my_ws.write(i, j, my_val, normal_format) else: my_val = str(get_field_value(obj, field)) my_ws.write(i, j, my_val, normal_format) # adjust the width of the columns based on the max string length in each col ## replace col_max[j] if str length j is bigger than stored value # if new value > stored value... replace stored value if len(str(my_val)) > col_max[j]: if len(str(my_val)) < 50: col_max[j] = len(str(my_val)) else: col_max[j] = 50 j += 1 i += 1 # set column widths for j in range(0, len(col_max)): my_ws.set_column(j, j, width=col_max[j] * 1.1) workbook.close() return target_url
def generate_meeting_report(fiscal_year=None, is_posted=None): # figure out the filename target_dir = os.path.join(settings.BASE_DIR, 'media', 'temp') target_file = "temp_data_export_{}.xlsx".format( timezone.now().strftime("%Y-%m-%d")) target_file_path = os.path.join(target_dir, target_file) target_url = os.path.join(settings.MEDIA_ROOT, 'temp', target_file) # create workbook and worksheets workbook = xlsxwriter.Workbook(target_file_path) # create formatting variables title_format = workbook.add_format({ 'bold': True, "align": 'normal', 'font_size': 24, }) header_format = workbook.add_format({ 'bold': True, 'border': 1, 'border_color': 'black', "align": 'normal', "text_wrap": True }) total_format = workbook.add_format({ 'bold': True, "align": 'left', "text_wrap": True, 'num_format': '$#,##0' }) normal_format = workbook.add_format({ "align": 'left', "text_wrap": True, 'border': 1, 'border_color': 'black', }) currency_format = workbook.add_format({'num_format': '#,##0.00'}) date_format = workbook.add_format({ 'num_format': "yyyy-mm-dd", "align": 'left', }) # get the meeting list objects = models.Meeting.objects.filter(is_planning=False) if fiscal_year: objects = objects.filter(process__fiscal_year=fiscal_year) if is_posted is not None: objects = objects.filter(process__is_posted=is_posted) field_list = [ 'process.fiscal_year|fiscal year', 'process.is_posted|Has been posted?', 'process.name|Process name', 'process.scope_type|type of process', 'tor_display_dates|meeting dates', 'process.name|meeting name (English)', 'process.nom|meeting name (French)', 'chair|Chairperson name', 'process.coordinator|CSAS Coordinator', 'process.advisors|Science advisors', 'expected publications', 'other regions', ] # define the header header = [ get_verbose_label(objects.first(), field) for field in field_list ] title = "CSAS Meeting Report" # define a worksheet my_ws = workbook.add_worksheet(name="meeting report") my_ws.write(0, 0, title, title_format) my_ws.write_row(2, 0, header, header_format) i = 3 for obj in objects: # create the col_max column to store the length of each header # should be a maximum column width to 100 col_max = [len(str(d)) if len(str(d)) <= 100 else 100 for d in header] j = 0 for field in field_list: if "other regions" in field: my_val = listrify(obj.process.other_regions.all()) my_ws.write(i, j, my_val, normal_format) elif "advisors" in field: my_val = listrify(obj.process.advisors.all()) my_ws.write(i, j, my_val, normal_format) elif "expected publications" in field: if hasattr(obj.process, "tor"): my_val = listrify( obj.process.tor.expected_document_types.all()) else: my_val = "n/a" my_ws.write(i, j, my_val, normal_format) else: my_val = str(get_field_value(obj, field)) my_ws.write(i, j, my_val, normal_format) # adjust the width of the columns based on the max string length in each col ## replace col_max[j] if str length j is bigger than stored value # if new value > stored value... replace stored value if len(str(my_val)) > col_max[j]: if len(str(my_val)) < 75: col_max[j] = len(str(my_val)) else: col_max[j] = 75 j += 1 i += 1 # set column widths for j in range(0, len(col_max)): my_ws.set_column(j, j, width=col_max[j] * 1.1) workbook.close() return target_url
def generate_request_list(requests): # figure out the filename target_dir = os.path.join(settings.BASE_DIR, 'media', 'temp') target_file = "temp_data_export_{}.xlsx".format( timezone.now().strftime("%Y-%m-%d")) target_file_path = os.path.join(target_dir, target_file) target_url = os.path.join(settings.MEDIA_ROOT, 'temp', target_file) # create workbook and worksheets workbook = xlsxwriter.Workbook(target_file_path) # create formatting variables title_format = workbook.add_format({ 'bold': True, "align": 'normal', 'font_size': 24, }) header_format = workbook.add_format({ 'bold': True, 'border': 1, 'border_color': 'black', "align": 'normal', "text_wrap": True }) total_format = workbook.add_format({ 'bold': True, "align": 'left', "text_wrap": True, 'num_format': '$#,##0' }) normal_format = workbook.add_format({ "align": 'left', "text_wrap": True, 'border': 1, 'border_color': 'black', }) currency_format = workbook.add_format({'num_format': '#,##0.00'}) date_format = workbook.add_format({ 'num_format': "mm/dd/yyyy", "align": 'left', }) field_list = [ 'id', 'fiscal_year', 'title|{}'.format(_("title")), 'advice_fiscal_year', 'target_advice_date|{}'.format(_("advice date")), 'status', 'has_process|{}'.format(_("has process?")), 'coordinator', 'client', 'region|{}'.format(_("client region")), 'sector|{}'.format(_("client sector")), 'section|{}'.format(_("client section")), ] # define the header header = [ get_verbose_label(requests.first(), field) for field in field_list ] title = "CSAS Request List" # define a worksheet my_ws = workbook.add_worksheet(name="requests") my_ws.write(0, 0, title, title_format) my_ws.write_row(2, 0, header, header_format) col_max = [len(str(d)) if len(str(d)) <= 100 else 100 for d in header] i = 3 for obj in requests: # create the col_max column to store the length of each header # should be a maximum column width to 100 j = 0 for field in field_list: if "other regions" in field: my_val = listrify(obj.process.other_regions.all()) my_ws.write(i, j, my_val, normal_format) elif "advisors" in field: my_val = listrify(obj.process.advisors.all()) my_ws.write(i, j, my_val, normal_format) elif "date" in field: my_val = obj.target_advice_date.strftime( "%m/%d/%Y") if obj.target_advice_date else "" my_ws.write(i, j, my_val, date_format) elif "expected publications" in field: if hasattr(obj.process, "tor"): my_val = listrify( obj.process.tor.expected_document_types.all()) else: my_val = "n/a" my_ws.write(i, j, my_val, normal_format) else: my_val = str(get_field_value(obj, field)) my_ws.write(i, j, my_val, normal_format) # adjust the width of the columns based on the max string length in each col ## replace col_max[j] if str length j is bigger than stored value # if new value > stored value... replace stored value if len(str(my_val)) > col_max[j]: if len(str(my_val)) < 75: col_max[j] = len(str(my_val)) else: col_max[j] = 75 j += 1 i += 1 # set column widths for j in range(0, len(col_max)): my_ws.set_column(j, j, width=col_max[j] * 1.1) workbook.close() return target_url
def generate_physical_samples_report(): # figure out the filename target_dir = os.path.join(settings.BASE_DIR, 'media', 'inventory', 'temp') target_file = "temp_data_export_{}.xlsx".format( timezone.now().strftime("%Y-%m-%d")) target_file_path = os.path.join(target_dir, target_file) target_url = os.path.join(settings.MEDIA_ROOT, 'inventory', 'temp', target_file) # create workbook and worksheets workbook = xlsxwriter.Workbook(target_file_path) # create formatting variables title_format = workbook.add_format({ 'bold': False, "align": 'normal', 'font_size': 14, "text_wrap": False, 'bg_color': '#006640', 'font_color': 'white' }) header_format = workbook.add_format({ 'bold': False, 'border': 1, 'border_color': 'black', 'bg_color': '#A1B7BF', "align": 'normal', "text_wrap": False }) normal_format = workbook.add_format({ "align": 'left', "text_wrap": True, }) date_format = workbook.add_format({ 'num_format': "yyyy-mm-dd", "align": 'left', }) # get the resource list # anything with resource type as "physical collection" ids = [r.id for r in models.Resource.objects.filter(resource_type_id=4)] # anything where this is a meaningful description of physical samples ids.extend([ r.id for r in models.Resource.objects.filter( physical_sample_descr_eng__isnull=False) if len(r.physical_sample_descr_eng) > 10 ]) ids.extend([ r.id for r in models.Resource.objects.filter( physical_sample_descr_fre__isnull=False) if len(r.physical_sample_descr_fre) > 10 ]) # anything where the word "physical" is in the title ids.extend([ r.id for r in models.Resource.objects.filter( title_eng__icontains="physical").filter( title_eng__icontains="sample") ]) ids.extend([ r.id for r in models.Resource.objects.filter( title_fre__icontains="physique").filter( title_fre__icontains="échantillon") ]) # anything where the word "physical" is in the storage notes ids.extend([ r.id for r in models.Resource.objects.filter( storage_envr_notes__icontains="physical").filter( storage_envr_notes__icontains="sample") ]) ids.extend([ r.id for r in models.Resource.objects.filter( storage_envr_notes__icontains="physique").filter( storage_envr_notes__icontains="échantillon") ]) resources = models.Resource.objects.filter(id__in=ids) field_list = [ "id", "title_eng", "title_fre", "physical_sample_descr_eng", "physical_sample_descr_fre", "storage_envr_notes", "hyperlink", ] # define the headers header0 = [ "dfo-mpo", "Physical Samples Report Fisheries and Oceans Canada | Pêches et Océans Canada", "", "", "", "", "", ] header = [ get_verbose_label(resources.first(), field) for field in field_list ] # header.append('Number of projects tagged') # define a worksheet my_ws = workbook.add_worksheet(name="query results") my_ws.write_row(0, 0, header0, title_format) my_ws.write_row(2, 0, header, header_format) i = 3 for r in resources.order_by("id"): # create the col_max column to store the length of each header # should be a maximum column width to 100 col_max = [len(str(d)) if len(str(d)) <= 100 else 100 for d in header] j = 0 for field in field_list: if "hyperlink" in field: my_val = f'http://dmapps{reverse("inventory:resource_detail", args=[r.id])}' my_ws.write_url(i, j, url=my_val, string=my_val) else: my_val = get_field_value(r, field, nullmark="") my_ws.write(i, j, my_val, normal_format) # adjust the width of the columns based on the max string length in each col ## replace col_max[j] if str length j is bigger than stored value # if new value > stored value... replace stored value if len(str(my_val)) > col_max[j]: if len(str(my_val)) < 75: col_max[j] = len(str(my_val)) else: col_max[j] = 75 j += 1 i += 1 # set column widths for j in range(0, len(col_max)): my_ws.set_column(j, j, width=col_max[j] * 1.1) workbook.close() return target_url
def generate_resources_report(sections): # figure out the filename target_dir = os.path.join(settings.BASE_DIR, 'media', 'inventory', 'temp') target_file = "temp_data_export_{}.xlsx".format( timezone.now().strftime("%Y-%m-%d")) target_file_path = os.path.join(target_dir, target_file) target_url = os.path.join(settings.MEDIA_ROOT, 'inventory', 'temp', target_file) # create workbook and worksheets workbook = xlsxwriter.Workbook(target_file_path) # create formatting variables title_format = workbook.add_format({ 'bold': False, "align": 'normal', 'font_size': 14, "text_wrap": False, 'bg_color': '#006640', 'font_color': 'white' }) header_format = workbook.add_format({ 'bold': False, 'border': 1, 'border_color': 'black', 'bg_color': '#A1B7BF', "align": 'normal', "text_wrap": False }) normal_format = workbook.add_format({ "align": 'left', "text_wrap": True, }) date_format = workbook.add_format({ 'num_format': "yyyy-mm-dd", "align": 'left', }) # get the resource list # anything with resource type as "physical collection" resources = models.Resource.objects.all() if sections and sections != "None": sections = sections.split(",") resources = resources.filter(section_id__in=sections) field_list = [ "uuid", "resource_type", "section", "title_eng", "title_fre", "status", "maintenance", "purpose_eng", "purpose_fre", "descr_eng", "descr_fre", "time_start_day", "time_start_month", "time_start_year", "time_end_day", "time_end_month", "time_end_year", "sampling_method_eng", "sampling_method_fre", "physical_sample_descr_eng", "physical_sample_descr_fre", "resource_constraint_eng", "resource_constraint_fre", "qc_process_descr_eng", "qc_process_descr_fre", "security_use_limitation_eng", "security_use_limitation_fre", "security_classification", "storage_envr_notes", "distribution_formats", "data_char_set", "spat_representation", "spat_ref_system", "geo_descr_eng", "geo_descr_fre", "west_bounding", "south_bounding", "east_bounding", "north_bounding", "parameters_collected_eng", "parameters_collected_fre", "additional_credit", "analytic_software", "date_verified", "fgp_url", "public_url", "fgp_publication_date", "od_publication_date", "od_release_date", "odi_id", "last_revision_date", "open_data_notes", "notes", "citations2", "keywords", "people", "paa_items", "parent", "date_last_modified", "last_modified_by", "flagged_4_deletion", "flagged_4_publication", "completedness_report", "completedness_rating", "translation_needed", "publication_fy", "hyperlink", ] header = [ get_verbose_label(resources.first(), field) for field in field_list ] # header.append('Number of projects tagged') # define a worksheet my_ws = workbook.add_worksheet(name="query results") my_ws.write_row(0, 0, header, header_format) i = 1 for r in resources.order_by("id"): # create the col_max column to store the length of each header # should be a maximum column width to 100 col_max = [len(str(d)) if len(str(d)) <= 100 else 100 for d in header] j = 0 for field in field_list: if "hyperlink" in field: my_val = f'http://dmapps{reverse("inventory:resource_detail", args=[r.id])}' my_ws.write_url(i, j, url=my_val, string=my_val) elif "people" in field: my_val = listrify([obj for obj in r.resource_people.all()]) my_ws.write(i, j, str(my_val), normal_format) elif "keywords" in field: my_val = listrify( [obj.non_hierarchical_name_en for obj in r.keywords.all()]) my_ws.write(i, j, str(my_val), normal_format) else: my_val = get_field_value(r, field, nullmark="") my_ws.write(i, j, str(my_val), normal_format) # adjust the width of the columns based on the max string length in each col ## replace col_max[j] if str length j is bigger than stored value # if new value > stored value... replace stored value if len(str(my_val)) > col_max[j]: if len(str(my_val)) < 75: col_max[j] = len(str(my_val)) else: col_max[j] = 75 j += 1 i += 1 # set column widths for j in range(0, len(col_max)): my_ws.set_column(j, j, width=col_max[j] * 1.1) workbook.close() return target_url
def generate_upcoming_trip_list(site_url): # figure out the filename target_dir = os.path.join(settings.BASE_DIR, 'media', 'temp') target_file = "temp.xlsx" target_file_path = os.path.join(target_dir, target_file) target_url = os.path.join(settings.MEDIA_ROOT, 'temp', target_file) # create workbook and worksheets workbook = xlsxwriter.Workbook(target_file_path, {'remove_timezone': True}) # create formatting variables title_format = workbook.add_format({ 'bold': True, "align": 'normal', 'font_size': 24, }) header_format = workbook.add_format({ 'bold': True, 'border': 1, 'border_color': 'black', 'bg_color': '#D6D1C0', "align": 'normal', "text_wrap": True }) total_format = workbook.add_format({ 'bold': True, "align": 'left', "text_wrap": True, 'num_format': '$#,##0' }) normal_format = workbook.add_format({ "align": 'left', "text_wrap": True, }) date_format = workbook.add_format({ 'num_format': "yyyy-mm-dd", "align": 'left', }) # get the trip list trip_list = models.Trip.objects.filter(start_date__gte=timezone.now()) field_list = [ "fiscal_year", "name", "status", "location", "lead", "travellers", "date_eligible_for_adm_review", "start_date", "end_date", "is_adm_approval_required", "registration_deadline", "abstract_deadline", ] # define the header header = [ get_verbose_label(trip_list.first(), field) for field in field_list ] # header.append('Number of projects tagged') title = gettext("Upcoming Trips and Meetings") # define a worksheet my_ws = workbook.add_worksheet(name="list") my_ws.write(0, 0, title, title_format) my_ws.write_row(2, 0, header, header_format) i = 3 for trip in trip_list.order_by("date_eligible_for_adm_review"): # create the col_max column to store the length of each header # should be a maximum column width to 100 col_max = [len(str(d)) if len(str(d)) <= 100 else 100 for d in header] data_row = list() j = 0 for field in field_list: if "travellers" in field: my_val = get_field_value(trip, field).count() my_ws.write(i, j, my_val, normal_format) elif "fiscal_year" in field: my_val = str(get_field_value(trip, field)) my_ws.write(i, j, my_val, normal_format) elif "date" in field or "deadline" in field: my_val = getattr(trip, field) if my_val: my_ws.write_datetime(i, j, my_val, date_format) else: my_ws.write(i, j, my_val, normal_format) elif field == "name": my_val = str(get_field_value(trip, field)) my_ws.write_url( i, j, url= f'{site_url}/{reverse("travel:trip_detail", args=[trip.id])}', string=my_val) else: my_val = str(get_field_value(trip, field)) my_ws.write(i, j, my_val, normal_format) # adjust the width of the columns based on the max string length in each col ## replace col_max[j] if str length j is bigger than stored value # if new value > stored value... replace stored value if len(str(my_val)) > col_max[j]: if len(str(my_val)) < 75: col_max[j] = len(str(my_val)) else: col_max[j] = 75 j += 1 i += 1 # set column widths for j in range(0, len(col_max)): my_ws.set_column(j, j, width=col_max[j] * 1.1) workbook.close() if settings.AZURE_STORAGE_ACCOUNT_NAME: utils.upload_to_azure_blob(target_file_path, f'temp/{target_file}') return target_url
def generate_trip_list(fiscal_year, region, adm, from_date, to_date, site_url): # figure out the filename target_dir = os.path.join(settings.BASE_DIR, 'media', 'temp') target_file = "temp.xlsx" target_file_path = os.path.join(target_dir, target_file) target_url = os.path.join(settings.MEDIA_ROOT, 'temp', target_file) # create workbook and worksheets workbook = xlsxwriter.Workbook(target_file_path) # create formatting variables title_format = workbook.add_format({ 'bold': True, "align": 'normal', 'font_size': 24, }) header_format = workbook.add_format({ 'bold': True, 'border': 1, 'border_color': 'black', 'bg_color': '#D6D1C0', "align": 'normal', "text_wrap": True }) total_format = workbook.add_format({ 'bold': True, "align": 'left', "text_wrap": True, 'num_format': '$#,##0' }) normal_format = workbook.add_format({ "align": 'left', "text_wrap": True, }) currency_format = workbook.add_format({'num_format': '#,##0.00'}) if fiscal_year == "None": fiscal_year = None if adm == "None": adm = None if region == "None": region = None if from_date == "None": from_date = None else: fiscal_year = None # should not filter on both criteria if to_date == "None": to_date = None else: fiscal_year = None # should not filter on both criteria # get the trip list trip_list = models.Trip.objects.all() if fiscal_year: trip_list = trip_list.filter(fiscal_year=fiscal_year) # optional filter on trips for adm_approval_required if adm: adm = bool(int(adm)) trip_list = trip_list.filter(is_adm_approval_required=adm) # optional filter on trips for regional lead if region: # too dangerous to only filter by the lead field... we should look at each request / traveller and determine # if they are the correct region request_list = list() # for each trip for trip in trip_list: # look at a list of the requests... for request in trip.requests.all(): # if the traveller is in the region of interest, add the request tp the list if request.region.id == int(region): # add the request request_list.append(request) break trip_list = trip_list.filter(requests__in=request_list) if from_date: my_date = datetime.strptime( from_date, "%Y-%m-%d").replace(tzinfo=timezone.get_current_timezone()) trip_list = trip_list.filter(start_date__gte=my_date, ) if to_date: my_date = datetime.strptime( to_date, "%Y-%m-%d").replace(tzinfo=timezone.get_current_timezone()) trip_list = trip_list.filter(start_date__lt=my_date, ) field_list = [ "fiscal_year", "name", "status", "trip_subcategory", "is_adm_approval_required", "location", "start_date", "end_date", "number_of_days|Number of days", "travellers|Travellers (region)", "total_cost|Total trip cost", "total_non_dfo_cost|Total non-DFO funding", "total_non_dfo_funding_sources|Non-DFO funding sources", "total_dfo_cost|Total DFO cost", "non_res_total_cost|Total DFO cost (non RES)", ] # get_cost_comparison_dict # define the header header = [ get_verbose_label(trip_list.first(), field) for field in field_list ] # header.append('Number of projects tagged') title = "DFO Science Trips" if fiscal_year: title += f" for {shared_models.FiscalYear.objects.get(pk=fiscal_year)}" elif from_date and not to_date: title += f" from {from_date} Onwards" elif to_date and not from_date: title += f" up until {to_date}" elif to_date and from_date: title += f" ranging from {from_date} to {to_date}" if region: title += f" ({shared_models.Region.objects.get(pk=region)})" if adm is not None: if adm: title += " (Trip requiring ADM approval only)" else: title += " (Only trips NOT requiring ADM approval)" # define a worksheet my_ws = workbook.add_worksheet(name="trip list") my_ws.write(0, 0, title, title_format) my_ws.write_row(2, 0, header, header_format) i = 3 for trip in trip_list.order_by("start_date"): # create the col_max column to store the length of each header # should be a maximum column width to 100 col_max = [len(str(d)) if len(str(d)) <= 100 else 100 for d in header] data_row = list() j = 0 for field in field_list: if "travellers" in field: my_list = list() for t in trip.travellers.all(): my_list.append( f'{t.smart_name} ({t.request.region}) - {currency(t.total_dfo_funding)}' ) my_val = listrify(my_list, "\n") my_ws.write(i, j, my_val, normal_format) elif "fiscal_year" in field or "subcategory" in field or "status" in field: my_val = str(get_field_value(trip, field)) my_ws.write(i, j, my_val, normal_format) elif field == "name": my_val = str(get_field_value(trip, field)) my_ws.write_url( i, j, url= f'{site_url}/{reverse("travel:trip_detail", args=[trip.id])}', string=my_val) elif "cost" in field: my_val = nz(get_field_value(trip, field), 0) my_ws.write(i, j, my_val, currency_format) else: my_val = get_field_value(trip, field) my_ws.write(i, j, my_val, normal_format) # adjust the width of the columns based on the max string length in each col ## replace col_max[j] if str length j is bigger than stored value # if new value > stored value... replace stored value if len(str(my_val)) > col_max[j]: if len(str(my_val)) < 75: col_max[j] = len(str(my_val)) else: col_max[j] = 75 j += 1 i += 1 # set column widths for j in range(0, len(col_max)): my_ws.set_column(j, j, width=col_max[j] * 1.1) workbook.close() if settings.AZURE_STORAGE_ACCOUNT_NAME: utils.upload_to_azure_blob(target_file_path, f'temp/{target_file}') return target_url