def truncated_title(self):
if self.title_eng:
my_str = truncate(self.title_eng, 50)
else:
my_str = "no title"
return my_str
def perform_update(self, serializer):
if self.request.FILES.get("file"):
filename = self.request.FILES["file"].name
filesize = self.request.FILES["file"].size
validate_file_size(filesize)
suffix = ""
if len(filename.split(".")) > 1:
suffix = f'.{filename.split(".")[-1]}'
filename = filename.split(".")[0]
filename = truncate(slugify(filename), 20, False)
self.request.FILES["file"].name = f"{filename}{suffix}"
serializer.save(updated_by=self.request.user)
def achievement_display_no_code(self):
mystr = ""
if self.category and self.category.is_publication:
if self.date:
mystr += f"{self.date.year}"
else:
mystr += "n/a"
if self.review_type:
mystr += f"{nz(self.review_type.code, '')}"
mystr += "."
elif self.date:
mystr += f"{self.date.year}."
if self.detail:
mystr += f" {self.detail}"
return truncate(mystr, max_length=1000)
def generate_consultation_report(orgs, sectors, statuses, from_date, to_date,
entry_note_types, entry_note_statuses,
org_regions, entry_regions):
# 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)
# first, filter out the "none" placeholder
if sectors == "None":
sectors = None
if orgs == "None":
orgs = None
if statuses == "None":
statuses = None
if from_date == "None":
from_date = None
if to_date == "None":
to_date = None
if org_regions == "None":
org_regions = None
if entry_regions == "None":
entry_regions = 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
# get an entry list for the fiscal year (if any)
entry_list = models.Entry.objects.filter(
entry_type__name__icontains="consultation").order_by(
"status", "-initial_date")
if org_regions:
# we have to refine the queryset to only the selected orgs
region_list = [
Region.objects.get(pk=int(o)) for o in org_regions.split(",")
]
entry_list = entry_list.filter(organizations__regions__in=region_list)
if entry_regions:
# we have to refine the queryset to only the selected orgs
region_list = [
Region.objects.get(pk=int(o)) for o in entry_regions.split(",")
]
entry_list = entry_list.filter(regions__in=region_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)
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 statuses:
# we have to refine the queryset to only the selected statuses
status_list = [
models.Status.objects.get(pk=int(o)) for o in statuses.split(",")
]
entry_list = entry_list.filter(status__in=status_list)
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()
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': '#a6cbf5',
"align": 'normal',
"text_wrap": True,
"valign": 'top',
})
normal_format = workbook.add_format({
"align": 'left',
"text_wrap": True,
'num_format': 'mm/dd/yyyy',
"valign": 'top',
})
highlighted_format = workbook.add_format({
"align": 'left',
"text_wrap": True,
'num_format': 'mm/dd/yyyy',
"valign": 'top',
"bg_color": "yellow"
})
# we want a sheet for every sector
sector_ids = []
for e in entry_list:
for s in e.sectors.all():
sector_ids.append(s.id)
sectors = ml_models.Sector.objects.filter(id__in=sector_ids)
# there is a problem: some of the sectors have duplicate names, especially when truncated..
for s in sectors:
sector_name = truncate(s.name, 30, False)
if sectors.filter(name__icontains=sector_name).count() > 1:
sector_name = truncate(s.name, 25, False) + f" ({s.id})"
my_ws = workbook.add_worksheet(name=sector_name)
entries = s.entries.filter(id__in=[e.id for e in entry_list])
# define the header
header = [
"Title",
"Organizations",
"Status",
"Persons/lead",
"DFO programs involved",
"letter sent",
"Response Requested by",
"Proponent",
"FAA triggered (Yes/No)",
"Comments",
]
# 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_row(0, 0, header, header_format)
i = 1
for e in entries.all():
people = nz(listrify([p for p in e.people.all()], "\n\n"), "")
notes = ""
if e.notes.exists():
for n in e.notes.all():
if not entry_note_types or (n.type in entry_note_types):
if not entry_note_statuses or (n.status_id
in entry_note_statuses):
if len(notes):
notes += "\n\n*************************\n" + str(
n)
else:
notes = str(n)
data_row = [
e.title,
e.orgs_str,
str(e.status),
people,
e.sectors_str,
e.initial_date.strftime("%m/%d/%Y")
if e.initial_date else " ---",
e.response_requested_by.strftime("%m/%d/%Y")
if e.response_requested_by else " ---",
e.proponent,
yesno(e.is_faa_required, "yes,no,no"),
notes.replace("\\r\\n", "\r\n"),
]
# 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
format = normal_format
if e.sectors.count() > 1:
format = highlighted_format
my_ws.write_row(i, 0, data_row, 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)
workbook.close()
return target_url