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