def generate_user_report():
"""Returns a generator for an HTTP Streaming Response"""
# write the header
fields = [
"first_name",
"last_name",
"email",
"date_joined",
"last_login",
"pageviews",
"apps_used",
"cumulative_users",
]
qs = User.objects.filter(last_login__isnull=False).order_by("date_joined")
pseudo_buffer = Echo()
pseudo_buffer.write(u'\ufeff'.encode(
'utf8')) # BOM (optional...Excel needs it to open UTF-8 file properly)
writer = csv.writer(pseudo_buffer)
yield writer.writerow(fields)
i = 1
for obj in qs:
data_row = [] # starter
for field in fields:
if field == "pageviews":
data_row.append(
sum([v.page_visits for v in obj.visitsummary_set.all()]))
elif field == "apps_used":
data_row.append(
listrify(
set([
v.application_name
for v in obj.visitsummary_set.all()
])))
elif field == "cumulative_users":
data_row.append(i)
else:
data_row.append(get_field_value(obj, field, display_time=True))
yield writer.writerow(data_row)
i += 1
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_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_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_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_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_odi_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 trip list
resource_list = models.Resource.objects.filter(odi_id__isnull=False)
field_list = [
"odi_id",
"title_eng",
"title_fre",
"descr_eng",
"descr_fre",
"publisher_en",
"publisher_fr",
"od_publication_date",
"language",
"size",
"eligible_for_release",
"paa_en",
"paa_fr",
"od_release_date",
"url_en",
"url_fr",
]
# define the headers
header1 = [
"dfo-mpo",
"Open Data Inventory Fisheries and Oceans Canada | Pêches et Océans Canada",
"",
"",
"",
"",
"",
"",
"",
"",
"",
"",
"",
"",
"",
"",
]
header2 = [
"Reference Number",
"Title (English)",
"Title (French)",
"Description (English)",
"Description (French)",
"Publisher - Name at Publication (English)",
"Publisher - Name at Publication (French)",
"Date Published",
"Language",
"Size",
"Eligible for Release",
"Program Alignment Architecture (English)",
"Program Alignment Architecture (French)",
"Date Released",
"Open Government Portal Record (English)",
"Open Government Portal Record (French)",
]
header3 = [
"ref_number",
"title_en",
"title_fr",
"description_en",
"description_fr",
"publisher_en",
"publisher_fr",
"date_published",
"language",
"size",
"eligible_for_release",
"program_alignment_architecture_en",
"program_alignment_architecture_fr",
"date_released",
"portal_url_en",
"portal_url_fr",
]
# header.append('Number of projects tagged')
# define a worksheet
my_ws = workbook.add_worksheet(name="trip list")
my_ws.write_row(0, 0, header1, title_format)
my_ws.write_row(1, 0, header2, header_format)
my_ws.write_row(2, 0, header3, header_format)
my_ws.set_row(2, 0) # row # 2 is a hidden row
i = 3
for r in resource_list.order_by("odi_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 header2]
j = 0
for field in field_list:
if "publisher_en" in field:
my_val = "Fisheries and Oceans"
my_ws.write(i, j, my_val, normal_format)
elif "publisher_fr" in field:
my_val = "Pêches et Océans"
my_ws.write(i, j, my_val, normal_format)
elif "eligible_for_release" in field:
my_val = "Y"
my_ws.write(i, j, my_val, normal_format)
elif "language" in field:
my_val = "en+fr" if r.od_publication_date else ""
my_ws.write(i, j, my_val, normal_format)
elif "paa_en" in field:
my_val = listrify(
[f'{paa.code} {paa.name}' for paa in r.paa_items.all()])
my_ws.write(i, j, my_val, normal_format)
elif "paa_fr" in field:
my_val = listrify(
[f'{paa.code} {paa.nom}' for paa in r.paa_items.all()])
my_ws.write(i, j, my_val, normal_format)
elif "url_en" in field:
if r.public_url:
my_val = f'https://open.canada.ca/data/en/dataset/{r.uuid}'
else:
my_val = ""
my_ws.write_url(i, j, url=my_val, string=my_val)
elif "url_fr" in field:
if r.public_url:
my_val = f'https://ouvert.canada.ca/data/fr/dataset/{r.uuid}'
else:
my_val = ""
my_ws.write_url(i, j, url=my_val, string=my_val)
elif "date" in field:
my_val = get_field_value(r, field, nullmark="")
my_ws.write(i, j, my_val, date_format)
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_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
