def generate_master_spreadsheet(fy, rc, project):
# figure out the filename
target_dir = os.path.join(settings.BASE_DIR, 'media', 'scifi', 'temp')
target_file = "temp_export.xlsx"
target_file_path = os.path.join(target_dir, target_file)
target_url = os.path.join(settings.MEDIA_ROOT, 'scifi', 'temp',
target_file)
# create workbook and worksheets
workbook = xlsxwriter.Workbook(target_file_path)
worksheet1 = workbook.add_worksheet(name="Transactions")
# create formatting
header_format = workbook.add_format({
'bold': True,
'border': 1,
'border_color': 'black',
'bg_color': '#8C96A0',
"align": 'normal',
"text_wrap": True
})
total_format = workbook.add_format({
'bg_color': '#D6D1C0',
"align": 'left',
"text_wrap": True
})
normal_format = workbook.add_format({"align": 'left', "text_wrap": True})
bold_format = workbook.add_format({"align": 'left', 'bold': True})
# need to assemble a transaction list
transaction_list = models.Transaction.objects.filter(fiscal_year=fy)
if rc != "None":
transaction_list = transaction_list.filter(
responsibility_center_id=int(rc))
if project != "None":
transaction_list = transaction_list.filter(project_id=int(project))
# spreadsheet: Project List #
#############################
if len(transaction_list) == 0:
worksheet1.write_row(0, 0, [
"There are no transactions to report",
], bold_format)
else:
# get a project list for the year
field_list = [
'fiscal_year',
'responsibility_center',
'business_line',
'allotment_code',
'line_object',
'project',
'transaction_type',
'supplier_description',
'expected_purchase_date',
'creation_date',
'obligation_cost',
'outstanding_obligation',
'invoice_cost',
'reference_number',
'invoice_date',
'in_mrs',
'amount_paid_in_mrs',
'mrs_notes',
'procurement_hub_contact',
'comment',
'created_by',
'exclude_from_rollup',
]
header = [
"ID",
]
header.extend([
verbose_field_name(transaction_list[0], field)
for field in field_list
])
# 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]
worksheet1.write_row(0, 0, header, header_format)
i = 1
for t in transaction_list:
if t.expected_purchase_date:
expected_purchase_date = t.expected_purchase_date.strftime(
"%Y-%m-%d")
else:
expected_purchase_date = t.expected_purchase_date
if t.creation_date:
creation_date = t.creation_date.strftime("%Y-%m-%d")
else:
creation_date = t.creation_date
if t.invoice_date:
invoice_date = t.invoice_date.strftime("%Y-%m-%d")
else:
invoice_date = t.invoice_date
data_row = [
t.id,
str(t.fiscal_year),
str(t.responsibility_center),
str(t.business_line),
str(t.allotment_code),
str(t.line_object),
str(t.project),
t.get_transaction_type_display(),
t.supplier_description,
expected_purchase_date,
creation_date,
t.obligation_cost,
t.outstanding_obligation,
t.invoice_cost,
t.reference_number,
invoice_date,
t.in_mrs,
t.amount_paid_in_mrs,
t.mrs_notes,
t.procurement_hub_contact,
t.comment,
str(t.created_by),
t.exclude_from_rollup,
]
# 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
worksheet1.write_row(i, 0, data_row, normal_format)
i += 1
for j in range(0, len(col_max)):
worksheet1.set_column(j, j, width=col_max[j] * 1.1)
workbook.close()
return target_url
def generate_species_sample_spreadsheet(species_list=None):
# figure out the filename
target_dir = os.path.join(settings.BASE_DIR, 'media', 'grais', '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, 'grais', 'temp',
target_file)
# create workbook and worksheets
workbook = xlsxwriter.Workbook(target_file_path)
# create formatting
header_format = workbook.add_format({
'bold': True,
'border': 1,
'border_color': 'black',
'bg_color': '#8C96A0',
"align": 'normal',
"text_wrap": True
})
normal_format = workbook.add_format({"align": 'left', "text_wrap": True})
# Add a format. Light red fill with dark red text.
red_format = workbook.add_format({
'bg_color': '#FFC7CE',
'font_color': '#9C0006'
})
# Add a format. Green fill with dark green text.
green_format = workbook.add_format({
'bg_color': '#C6EFCE',
'font_color': '#006100'
})
# get a sample instance to create header
my_sample = models.Sample.objects.first()
my_species = models.Species.objects.first()
# define the header
header = [
"Species ID",
verbose_field_name(my_species, 'common_name'),
verbose_field_name(my_species, 'scientific_name'),
verbose_field_name(my_species, 'abbrev'),
verbose_field_name(my_species, 'epibiont_type'),
verbose_field_name(my_species, 'tsn'),
verbose_field_name(my_species, 'aphia_id'),
"Sample ID",
"Observation platform",
"Observation year",
"Observation month",
"Observation day",
verbose_field_name(my_sample, 'station'),
verbose_field_name(my_sample.station, 'province'),
verbose_field_name(my_sample.station, 'latitude_n'),
verbose_field_name(my_sample.station, 'longitude_w'),
"observed at station?",
"observed on line?",
"observed on collector surface?",
"% surface coverage - plates (mean)",
"% surface coverage - petris (mean)",
]
# worksheets #
##############
new_species_list = [
models.Species.objects.get(pk=int(s)) for s in species_list.split(",")
]
i = 1
my_ws = workbook.add_worksheet(name="Samples")
for species in new_species_list:
# 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)
# get a list of samples for each requested species FROM ALL SOURCES
sample_list = [
models.Sample.objects.get(pk=s["surface__line__sample"])
for s in models.SurfaceSpecies.objects.filter(
species=species).values("surface__line__sample").distinct()
]
sample_list.extend([
models.Sample.objects.get(pk=s["sample"])
for s in models.SampleSpecies.objects.filter(
species=species).values("sample").distinct()
])
sample_list.extend([
models.Sample.objects.get(pk=s["line__sample"])
for s in models.LineSpecies.objects.filter(
species=species).values("line__sample").distinct()
])
# distill the list
sample_set = set(sample_list)
for sample in sample_set:
if sample.date_retrieved:
obs_year = sample.date_retrieved.year
obs_month = sample.date_retrieved.month
obs_day = sample.date_retrieved.day
else:
obs_year = None
obs_month = None
obs_day = None
if models.SampleSpecies.objects.filter(
sample=sample, species=species).count() > 0:
at_station = "yes"
else:
at_station = "no"
if models.LineSpecies.objects.filter(line__sample=sample,
species=species).count() > 0:
on_line = "yes"
else:
on_line = "no"
if models.SurfaceSpecies.objects.filter(
surface__line__sample=sample, species=species).count() > 0:
on_surface = "yes"
else:
on_surface = "no"
# calculate the % coverage
if on_surface:
# for each surface, determine the percent coverage and store in list
coverage_list_pl = []
coverage_list_pe = []
for surface in models.Surface.objects.filter(
line__sample=sample).all():
if surface.surface_type == "pl":
try:
my_coverage = models.SurfaceSpecies.objects.get(
surface=surface,
species=species).percent_coverage
except:
my_coverage = 0
coverage_list_pl.append(my_coverage)
elif surface.surface_type == "pe":
try:
my_coverage = models.SurfaceSpecies.objects.get(
surface=surface,
species=species).percent_coverage
except:
my_coverage = 0
coverage_list_pe.append(my_coverage)
try:
mean_pl_coverage = statistics.mean(coverage_list_pl)
except statistics.StatisticsError:
mean_pl_coverage = 0
try:
mean_pe_coverage = statistics.mean(coverage_list_pe)
except statistics.StatisticsError:
mean_pe_coverage = 0
data_row = [
species.id,
species.common_name,
species.scientific_name,
species.abbrev,
species.get_epibiont_type_display(),
species.tsn,
species.aphia_id,
sample.id,
'Biofouling Monitoring',
obs_year,
obs_month,
obs_day,
sample.station.station_name,
sample.station.province.tabbrev,
sample.station.latitude_n,
sample.station.longitude_w,
at_station,
on_line,
on_surface,
mean_pl_coverage,
mean_pe_coverage,
]
# 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
for j in range(0, len(col_max)):
my_ws.set_column(j, j, width=col_max[j] * 1.1)
# set formatting for last three columns
my_ws.conditional_format(
0, header.index("observed at station?"), i,
header.index("observed on collector surface?"), {
'type': 'cell',
'criteria': 'equal to',
'value': '"yes"',
'format': green_format,
})
my_ws.conditional_format(
0, header.index("observed at station?"), i,
header.index("observed on collector surface?"), {
'type': 'cell',
'criteria': 'equal to',
'value': '"no"',
'format': red_format,
})
workbook.close()
return target_url
def generate_sample_report(year):
# create instance of mission:
qs = models.Sample.objects.filter(season=year)
# Create the HttpResponse object with the appropriate CSV header.
response = HttpResponse(content_type='text/csv')
response[
'Content-Disposition'] = 'attachment; filename="herring_sample_report_{}.csv"'.format(
year)
writer = csv.writer(response)
writer.writerow([
verbose_field_name(qs.first(), "id"),
verbose_field_name(qs.first(), 'season'),
verbose_field_name(qs.first(), 'type'),
verbose_field_name(qs.first(), 'sample_date'),
verbose_field_name(qs.first(), 'sampler_ref_number'),
verbose_field_name(qs.first(), 'sampler'),
verbose_field_name(qs.first(), 'port'),
verbose_field_name(qs.first(), 'district'),
verbose_field_name(qs.first(), 'survey_id'),
verbose_field_name(qs.first(), 'latitude_n'),
verbose_field_name(qs.first(), 'longitude_w'),
verbose_field_name(qs.first(), 'fishing_area'),
verbose_field_name(qs.first(), 'gear'),
verbose_field_name(
qs.first(),
'experimental_net_used',
),
verbose_field_name(qs.first(), 'vessel_cfvn'),
verbose_field_name(qs.first(), 'mesh_size'),
verbose_field_name(qs.first(), 'total_fish_measured'),
"length-frequency counts",
verbose_field_name(qs.first(), 'total_fish_preserved'),
verbose_field_name(qs.first(), 'catch_weight_lbs'),
verbose_field_name(qs.first(), 'sample_weight_lbs'),
verbose_field_name(qs.first(), 'remarks'),
])
for sample in qs:
if sample.sample_date:
sample_date = sample.sample_date.strftime('%Y-%m-%d')
else:
sample_date = None
if sample.port:
district = "{}{}".format(sample.port.province_code,
sample.port.district_code)
else:
district = None
writer.writerow([
sample.id,
sample.season,
sample.get_type_display(),
sample_date,
sample.sampler_ref_number,
str(sample.sampler),
str(sample.port),
district,
sample.survey_id,
sample.latitude_n,
sample.longitude_w,
str(sample.fishing_area),
str(sample.gear),
sample.experimental_net_used,
sample.vessel_cfvn,
str(sample.mesh_size),
sample.total_fish_measured,
sample.lf_count,
sample.total_fish_preserved,
sample.catch_weight_lbs,
sample.sample_weight_lbs,
sample.remarks,
])
return response