""" informant_reports/export_excel.py Author: Haidar Khazen Created: 9/29/22 (c) Copyright by The Rhazenette Corp for Chemtrusion Excel Export functionality Arguments: wb - workbook that includes filters sheet (or just a blank workbook) -initial_filters - filters from options form (self.request.GET) -filters - front end filters - obtained from hidden form in template if applicable -buildings - building filter if applicable """ import json import re import os from pathlib import Path from openpyxl import Workbook, load_workbook from openpyxl.styles import Font, Border, Side from openpyxl.utils import get_column_letter from django.conf import settings from django.contrib import messages from django.http import HttpResponse, HttpResponseBadRequest, HttpResponseRedirect from django.utils import timezone from django.shortcuts import redirect from django.urls import reverse from django.views.decorators.csrf import ensure_csrf_cookie from informant_site.utils import remove_tz, get_username from informant_site.models import ExportLog from .utilities import apply_fe_filters from .querysets import ( InvOnHandQueryset, InvOnHandByItemQueryset, InvOnHandByPackageQueryset, TxnSummaryQueryset, ORCSAEQueryset, ORCSAERMQueryset, ORCSAEFGQueryset, LocationByLocationQueryset, LocationByReceiverQueryset, LocationByItemQueryset, InvMinimumsQueryset, InvReorderQueryset, InvFindUseQueryset, JobFrmFdrRunQueryset, GroupPermissionsQueryset ) def inv_on_hand_excel(wb, initial_filters, filters, buildings): # First get qs based on initial form filters qs, total = InvOnHandQueryset(initial_filters) # Then apply front end filters and building limit filters = filters.lower() terms = re.split("[, ]+", filters) ops = [] for c in filters: if c == ',': ops.append('or') if c == ' ': ops.append('and') filtered_qs = [] for rec in qs: if rec.receiver not in ['INSPECT','MULTIPLE']: car = rec.car if rec.car is not None else '' description = rec.description if rec.description is not None else '' car_desc = car if rec.car else description search_string = (rec.receiver + ' '\ + rec.item.name + ' '\ + rec.cust_item_code + ' '\ + car_desc + ' '\ + rec.lot + ' '\ + rec.package.name + ' '\ + rec.customer.name + ' '\ + rec.inv_class.name + ' '\ + rec.group.name).lower() bools = [] for term in terms: bools.append(term in search_string) str_to_eval = '' i=0 for elem in bools: if i < len(ops): op = ops[i] + ' ' else: op = '' str_to_eval = str_to_eval + str(elem) + ' ' + op i+=1 result = eval(str_to_eval) else: result = False # don't display MULTiPLE and INSPECT invlog records building_result = buildings == "B1B2" # If both buildings, initial val = True if building_result == False: # One or both of the buildings missing if buildings == "B1" and not rec.receiver.endswith('-2'): building_result = True if buildings == "B2" and rec.receiver.endswith('-2'): building_result = True result = result and building_result if result: filtered_qs.append(rec) # Start building sheet title_font = Font(size=18, bold=True) conf_font = Font(italic=True) inv_header_font = Font(bold=True,) # Define the titles for columns headers = [ 'Receiver', 'Item', 'Item Code', 'Description/Car', 'Lot', 'CofAMFR', 'Pacakge', 'Unit Weight', 'Qty On Hand', ] ws = wb.active for x in range(1, len(headers)+1): ws.column_dimensions[get_column_letter(x)].width = 16 dateCell = ws['A1'] dateCell.value = '=today()' dateCell.number_format = 'mm/dd/yyyy;@' timeCell = ws['A2'] timeCell.value = '=now()' timeCell.number_format = 'hh:mm:ss AM/PM' ws['C1'] = 'Inventory On Hand Report' ws['C1'].font = title_font ws['F1'] = 'Page 1' ws['C2'] = 'Confidential Information' ws['C2'].font = conf_font row_num = 3 customer_prev = '' inv_class_prev = '' group_prev = '' start_sum_row = 0 end_sum_row = 0 for rec in filtered_qs: customer = rec.customer.name inv_class = rec.inv_class.name group = rec.group.name if (customer != customer_prev or inv_class != inv_class_prev or group != group_prev): if start_sum_row != 0 or end_sum_row != 0: row_num = row_num + 1 row = [ '', '', '', '', '', '', '', '', f'=SUM(I{start_sum_row}:I{end_sum_row})' ] # Assign the data for each cell of the row for col_num, cell_value in enumerate(row, 1): cell = ws.cell(row=row_num, column=col_num) if cell_value != '': cell.value = cell_value cell.border = Border(top=Side(border_style="double")) cell.number_format = '#,##0' row_num = row_num + 2 cell = ws.cell(row=row_num, column=1) cell.value = 'Inventory Owner' cell = ws.cell(row=row_num, column=2) cell.value = customer cell = ws.cell(row=row_num, column=3) cell.value = 'Inventory Class' cell = ws.cell(row=row_num, column=4) cell.value = inv_class cell = ws.cell(row=row_num, column=5) cell.value = 'Inventory group' cell = ws.cell(row=row_num, column=6) cell.value = group row_num = row_num + 2 # Assign the titles for each cell of the header for col_num, column_title in enumerate(headers, 1): cell = ws.cell(row=row_num, column=col_num) cell.value = column_title cell.font = inv_header_font row_num = row_num + 1 start_sum_row = row_num # Display row row = [ rec.receiver, rec.item.name, rec.cust_item_code, rec.car + "-" + rec.car_location if rec.car else rec.description, rec.lot, rec.cofa_mfr, rec.package.name, rec.unit_weight, rec.qty_on_hand ] # Assign the data for each cell of the row for col_num, cell_value in enumerate(row, 1): cell = ws.cell(row=row_num, column=col_num) cell.value = cell_value if col_num == 8: cell.number_format = '#,##0.00' # Unit Weight Format if col_num == 9: cell.number_format = '#,##0' # Qty format customer_prev = customer inv_class_prev = inv_class group_prev = group end_sum_row = row_num row_num = row_num + 1 row_num = row_num + 1 row = [ '', '', '', '', '', '', '', '', f'=SUM(I{start_sum_row}:I{end_sum_row})' ] # Assign the data for each cell of the row for col_num, cell_value in enumerate(row, 1): cell = ws.cell(row=row_num, column=col_num) if cell_value != '': cell.value = cell_value cell.border = Border(top=Side(border_style="double")) cell.number_format = '#,##0' if(filters == ''): # only show report total if no front end filters row_num = row_num + 2 row = [ '', '', '', '', '', '', '', '', total['qty_on_hand__sum'] ] # Assign the data for each cell of the row for col_num, cell_value in enumerate(row, 1): cell = ws.cell(row=row_num, column=col_num) if cell_value != '': cell.value = cell_value cell.border = Border(top=Side(border_style="double")) cell.number_format = '#,##0' return wb def inv_on_hand_item_excel(wb, initial_filters, filters): # First get qs based on initial form filters qs = InvOnHandByItemQueryset(initial_filters) # Then apply front end filters filtered_qs = apply_fe_filters(filters, qs, ['item__name']) # Start building sheet title_font = Font(size=18, bold=True) conf_font = Font(italic=True) inv_header_font = Font(bold=True,) # Define the titles for columns headers = [ 'Item', 'Qty On Hand', ] ws = wb.active for x in range(1, len(headers)+5): ws.column_dimensions[get_column_letter(x)].width = 16 dateCell = ws['A1'] dateCell.value = '=today()' dateCell.number_format = 'mm/dd/yyyy;@' timeCell = ws['A2'] timeCell.value = '=now()' timeCell.number_format = 'hh:mm:ss AM/PM' ws['C1'] = 'Inventory On Hand By Item Report' ws['C1'].font = title_font ws['F1'] = 'Page 1' ws['C2'] = 'Confidential Information' ws['C2'].font = conf_font row_num = 3 customer_prev = '' inv_class_prev = '' group_prev = '' item_prev = '' start_sum_row = 0 end_sum_row = 0 for rec in filtered_qs: customer = rec.customer.name inv_class = rec.inv_class.name group = rec.group.name item = rec.item.name if (customer != customer_prev or inv_class != inv_class_prev or group != group_prev): if start_sum_row != 0 or end_sum_row != 0: row_num = row_num + 1 row_num = row_num + 2 cell = ws.cell(row=row_num, column=1) cell.value = 'Inventory Owner' cell = ws.cell(row=row_num, column=2) cell.value = customer cell = ws.cell(row=row_num, column=3) cell.value = 'Inventory Class' cell = ws.cell(row=row_num, column=4) cell.value = inv_class cell = ws.cell(row=row_num, column=5) cell.value = 'Inventory group' cell = ws.cell(row=row_num, column=6) cell.value = group row_num = row_num + 2 # Assign the titles for each cell of the header for col_num, column_title in enumerate(headers, 1): cell = ws.cell(row=row_num, column=col_num) cell.value = column_title cell.font = inv_header_font row_num = row_num + 1 start_sum_row = row_num # Display row if(item != item_prev): row = [ rec.item.name, rec.total_item ] # Assign the data for each cell of the row for col_num, cell_value in enumerate(row, 1): cell = ws.cell(row=row_num, column=col_num) cell.value = cell_value if col_num == 2: cell.number_format = '#,##0' # Qty format row_num = row_num + 1 customer_prev = customer inv_class_prev = inv_class group_prev = group item_prev = item return wb def inv_on_hand_package_excel(wb, initial_filters, filters): # First get qs based on initial form filters qs = InvOnHandByPackageQueryset(initial_filters) # Then apply front end filters filtered_qs = apply_fe_filters(filters, qs, ['inv_class_id', 'package_id']) # Start building sheet title_font = Font(size=18, bold=True) conf_font = Font(italic=True) inv_header_font = Font(bold=True,) # Define the titles for columns headers = [ 'Class', 'Package', 'Qty On Hand', ] ws = wb.active for x in range(1, len(headers)+4): ws.column_dimensions[get_column_letter(x)].width = 18 dateCell = ws['A1'] dateCell.value = '=today()' dateCell.number_format = 'mm/dd/yyyy;@' timeCell = ws['A2'] timeCell.value = '=now()' timeCell.number_format = 'hh:mm:ss AM/PM' ws['C1'] = 'Inventory On Hand by Package Report' ws['C1'].font = title_font ws['F1'] = 'Page 1' ws['C2'] = 'Confidential Information' ws['C2'].font = conf_font row_num = 3 inv_class_prev = '' package_prev = '' start_sum_row = 0 end_sum_row = 0 for rec in filtered_qs: inv_class = rec.inv_class.name package = rec.package.name # Display row if(inv_class != inv_class_prev or package != package_prev): row = [ rec.inv_class.name, rec.package.name, rec.total_class_package ] # Assign the data for each cell of the row for col_num, cell_value in enumerate(row, 1): cell = ws.cell(row=row_num, column=col_num) cell.value = cell_value if col_num == 3: cell.number_format = '#,##0' # Qty format row_num = row_num + 1 inv_class_prev = inv_class package_prev = package return wb #def txn_summary_excel(self, qs, qstr): def txn_summary_excel(wb, initial_filters): qs = TxnSummaryQueryset(initial_filters) title_font = Font(size=18, bold=True) conf_font = Font(italic=True) txn_header_font = Font(bold=True,) # Define the titles for columns headers = [ 'receiver', 'Item', 'Cust Item Code', 'Lot', 'Transaction Date', 'Reference', 'Produced/Received', 'Consumed', 'Shipped', 'Adjustment' ] ws = wb.active for x in range(1, len(headers)+1): ws.column_dimensions[get_column_letter(x)].width = 16 ws.title='Txn Summary' dateCell = ws['A1'] dateCell.value = '=today()' dateCell.number_format = 'mm/dd/yyyy;@' timeCell = ws['A2'] timeCell.value = '=now()' timeCell.number_format = 'hh:mm:ss AM/PM' ws['C1'] = 'Transaction Summary' ws['C1'].font = title_font ws['F1'] = 'Page 1' ws['C2'] = 'Confidential Information' ws['C2'].font = conf_font row_num = 3 customer_prev = '' inv_class_prev = '' group_prev = '' item_prev='' start_sum_row = 0 end_sum_row = 0 total_received = total_consumed = total_shipped = total_adjustment = 0 for txn in qs: customer = txn.receiver.customer.name inv_class = txn.receiver.inv_class.name group = txn.receiver.group.name item = txn.receiver.item if (customer != customer_prev or inv_class != inv_class_prev or group != group_prev or item != item_prev): if start_sum_row != 0 or end_sum_row != 0: #row_num = row_num + 1 row = [ '', '', '', '', '', '', f'=SUM(G{start_sum_row}:G{end_sum_row})', f'=SUM(H{start_sum_row}:H{end_sum_row})', f'=SUM(I{start_sum_row}:I{end_sum_row})', f'=SUM(J{start_sum_row}:J{end_sum_row})' ] # Assign the data for each cell of the row for col_num, cell_value in enumerate(row, 1): cell = ws.cell(row=row_num, column=col_num) if cell_value != '': cell.value = cell_value cell.border = Border(top=Side(border_style="double")) cell.number_format = '#,##0' row_num = row_num + 2 if customer != customer_prev or inv_class != inv_class_prev or group != group_prev: cell = ws.cell(row=row_num, column=1) cell.value = 'Inventory Owner' cell = ws.cell(row=row_num, column=2) cell.value = customer row_num = row_num + 1 cell = ws.cell(row=row_num, column=1) cell.value = 'Inventory Class' cell = ws.cell(row=row_num, column=2) cell.value = inv_class cell = ws.cell(row=row_num, column=3) cell.value = 'Inventory group' cell = ws.cell(row=row_num, column=4) cell.value = group row_num = row_num + 2 # Assign the titles for each cell of the header for col_num, column_title in enumerate(headers, 1): cell = ws.cell(row=row_num, column=col_num) cell.value = column_title cell.font = txn_header_font row_num = row_num + 1 start_sum_row = row_num # Get received, consumed, etc. in local vars and convert to 0 if null txn_received = txn.received or 0 txn_consumed = txn.consumed or 0 txn_shipped = txn.shipped or 0 txn_adjustment = txn.adjustment or 0 # Display row only if all numbers not 0 if txn_received != 0 or txn_consumed != 0 or txn_shipped != 0 or txn_adjustment != 0: row = [ txn.receiver.receiver, txn.receiver.item.name, txn.receiver.cust_item_code, txn.receiver.lot, txn.transaction_date, txn.reference, txn_received, txn_consumed, txn_shipped, txn_adjustment ] # Assign the data for each cell of the row for col_num, cell_value in enumerate(row, 1): cell = ws.cell(row=row_num, column=col_num) cell.value = cell_value if col_num > 4: # only format number cells cell.number_format = '#,##0' total_received = total_received + txn_received total_consumed = total_consumed + txn_consumed total_shipped = total_shipped + txn_shipped total_adjustment = total_adjustment + txn_adjustment customer_prev = customer inv_class_prev = inv_class group_prev = group item_prev = item end_sum_row = row_num row_num = row_num + 1 #row_num = row_num + 1 row = [ '', '', '', '', '', '', f'=SUM(G{start_sum_row}:G{end_sum_row})', f'=SUM(H{start_sum_row}:H{end_sum_row})', f'=SUM(I{start_sum_row}:I{end_sum_row})', f'=SUM(J{start_sum_row}:J{end_sum_row})' ] # Assign the data for each cell of the row for col_num, cell_value in enumerate(row, 1): cell = ws.cell(row=row_num, column=col_num) if cell_value != '': cell.value = cell_value cell.border = Border(top=Side(border_style="double")) cell.number_format = '#,##0' row_num = row_num + 1 row = [ '', '', '', '', '', '', total_received, total_consumed, total_shipped, total_adjustment ] # Assign the data for each cell of the row for col_num, cell_value in enumerate(row, 1): cell = ws.cell(row=row_num, column=col_num) if cell_value != '': cell.value = cell_value cell.border = Border(top=Side(border_style="double")) cell.number_format = '#,##0' return wb #def orcsae_excel(self, qs, qstr): def orcsae_excel(wb, initial_filters): qs = TxnSummaryQueryset(initial_filters) qs = ORCSAEQueryset(qs) title_font = Font(size=18, bold=True) conf_font = Font(italic=True) txn_header_font = Font(bold=True,) # Define the titles for columns headers = [ 'receiver', 'Item', 'Cust Item Code', 'Lot', 'Open', 'Produced/Received', 'Consumed', 'Shipped', 'Adjustment', 'End' ] ws = wb.active for x in range(1, len(headers)+1): ws.column_dimensions[get_column_letter(x)].width = 16 ws.title='ORCSAE' dateCell = ws['A1'] dateCell.value = '=today()' dateCell.number_format = 'mm/dd/yyyy;@' timeCell = ws['A2'] timeCell.value = '=now()' timeCell.number_format = 'hh:mm:ss AM/PM' ws['C1'] = 'ORCSAE Inventory Summary' ws['C1'].font = title_font ws['F1'] = 'Page 1' ws['C2'] = 'Confidential Information' ws['C2'].font = conf_font row_num = 3 customer_prev = '' inv_class_prev = '' group_prev = '' start_sum_row = 0 end_sum_row = 0 total_open = total_received = total_consumed = total_shipped = total_adjustment = total_end = 0 for txn in qs: customer = txn.receiver.customer.name inv_class = txn.receiver.inv_class.name group = txn.receiver.group.name if (customer != customer_prev or inv_class != inv_class_prev or group != group_prev): if start_sum_row != 0 or end_sum_row != 0: row_num = row_num + 1 row = [ '', '', '', '', f'=SUM(E{start_sum_row}:E{end_sum_row})', f'=SUM(F{start_sum_row}:F{end_sum_row})', f'=SUM(G{start_sum_row}:G{end_sum_row})', f'=SUM(H{start_sum_row}:H{end_sum_row})', f'=SUM(I{start_sum_row}:I{end_sum_row})', f'=SUM(J{start_sum_row}:J{end_sum_row})' ] # Assign the data for each cell of the row for col_num, cell_value in enumerate(row, 1): cell = ws.cell(row=row_num, column=col_num) if cell_value != '': cell.value = cell_value cell.border = Border(top=Side(border_style="double")) cell.number_format = '#,##0' row_num = row_num + 2 cell = ws.cell(row=row_num, column=1) cell.value = 'Inventory Owner' cell = ws.cell(row=row_num, column=2) cell.value = customer row_num = row_num + 1 cell = ws.cell(row=row_num, column=1) cell.value = 'Inventory Class' cell = ws.cell(row=row_num, column=2) cell.value = inv_class cell = ws.cell(row=row_num, column=3) cell.value = 'Inventory group' cell = ws.cell(row=row_num, column=4) cell.value = group row_num = row_num + 2 # Assign the titles for each cell of the header for col_num, column_title in enumerate(headers, 1): cell = ws.cell(row=row_num, column=col_num) cell.value = column_title cell.font = txn_header_font row_num = row_num + 1 start_sum_row = row_num # Display row only if all numbers not 0 if txn.open_balance != 0 or txn.received_tot != 0 or txn.consumed_tot != 0 or txn.shipped_tot != 0 or txn.adjustment_tot != 0 or txn.end_balance: row = [ txn.receiver.receiver, txn.receiver.item.name, txn.receiver.cust_item_code, txn.receiver.lot, txn.open_balance, txn.received_tot, txn.consumed_tot, txn.shipped_tot, txn.adjustment_tot, txn.end_balance ] # Assign the data for each cell of the row for col_num, cell_value in enumerate(row, 1): cell = ws.cell(row=row_num, column=col_num) cell.value = cell_value if col_num > 4: # only format number cells cell.number_format = '#,##0' total_open = total_open + txn.open_balance total_received = total_received + txn.received_tot total_consumed = total_consumed + txn.consumed_tot total_shipped = total_shipped + txn.shipped_tot total_adjustment = total_adjustment + txn.adjustment_tot total_end = total_end + txn.end_balance customer_prev = customer inv_class_prev = inv_class group_prev = group end_sum_row = row_num row_num = row_num + 1 row_num = row_num + 1 row = [ '', '', '', '', f'=SUM(E{start_sum_row}:E{end_sum_row})', f'=SUM(F{start_sum_row}:F{end_sum_row})', f'=SUM(G{start_sum_row}:G{end_sum_row})', f'=SUM(H{start_sum_row}:H{end_sum_row})', f'=SUM(I{start_sum_row}:I{end_sum_row})', f'=SUM(J{start_sum_row}:J{end_sum_row})' ] # Assign the data for each cell of the row for col_num, cell_value in enumerate(row, 1): cell = ws.cell(row=row_num, column=col_num) if cell_value != '': cell.value = cell_value cell.border = Border(top=Side(border_style="double")) cell.number_format = '#,##0' row_num = row_num + 1 row = [ '', '', '', '', total_open, total_received, total_consumed, total_shipped, total_adjustment, total_end ] # Assign the data for each cell of the row for col_num, cell_value in enumerate(row, 1): cell = ws.cell(row=row_num, column=col_num) if cell_value != '': cell.value = cell_value cell.border = Border(top=Side(border_style="double")) cell.number_format = '#,##0' return wb def orcsae_rm_excel(wb, initial_filters): # First get qs based on initial form filters qs = ORCSAERMQueryset(initial_filters) # Start building sheet title_font = Font(size=18, bold=True) conf_font = Font(italic=True) header_font = Font(bold=True,) # Define the titles for columns headers = [ 'Product Code', 'Item', 'Open', 'Produced/Received', 'Consumed', 'Shipped', 'Adjustment', 'End' ] ws = wb.active for x in range(1, len(headers)+1): ws.column_dimensions[get_column_letter(x)].width = 16 ws.title='ORCSAE-RM' dateCell = ws['A1'] dateCell.value = '=today()' dateCell.number_format = 'mm/dd/yyyy;@' timeCell = ws['A2'] timeCell.value = '=now()' timeCell.number_format = 'hh:mm:ss AM/PM' ws['C1'] = 'Raw Material Summary' ws['C1'].font = title_font ws['F1'] = 'Page 1' ws['C2'] = 'Confidential Information' ws['C2'].font = conf_font row_num = 3 # Assign the titles for each cell of the header for col_num, column_title in enumerate(headers, 1): cell = ws.cell(row=row_num, column=col_num) cell.value = column_title cell.font = header_font row_num = row_num + 1 start_sum_row = row_num for txn in qs: # Display row row = [ txn.receiver.cust_item_code, txn.receiver.item.name, txn.open_balance, txn.received_tot, txn.consumed_tot, txn.shipped_tot, txn.adjustment_tot, txn.end_balance ] # Assign the data for each cell of the row for col_num, cell_value in enumerate(row, 1): cell = ws.cell(row=row_num, column=col_num) cell.value = cell_value if col_num > 4: cell.number_format = '#,##0' # Qty format end_sum_row = row_num row_num = row_num + 1 row_num = row_num + 1 row = [ '', '', f'=SUM(C{start_sum_row}:C{end_sum_row})', f'=SUM(D{start_sum_row}:D{end_sum_row})', f'=SUM(E{start_sum_row}:E{end_sum_row})', f'=SUM(F{start_sum_row}:F{end_sum_row})', f'=SUM(G{start_sum_row}:G{end_sum_row})', f'=SUM(H{start_sum_row}:H{end_sum_row})' ] # Assign the data for each cell of the row for col_num, cell_value in enumerate(row, 1): cell = ws.cell(row=row_num, column=col_num) if cell_value != '': cell.value = cell_value cell.border = Border(top=Side(border_style="double")) cell.number_format = '#,##0' return wb def orcsae_fg_excel(wb, initial_filters): # First get qs based on initial form filters qs = ORCSAEFGQueryset(initial_filters) # Start building sheet title_font = Font(size=18, bold=True) conf_font = Font(italic=True) header_font = Font(bold=True,) # Define the titles for columns headers = [ 'Item Code', 'Class', 'Group', 'Item', 'Lot', 'Open', 'Produced/Received', 'Consumed', 'Shipped', 'Adjustment', 'End' ] ws = wb.active for x in range(1, len(headers)+1): ws.column_dimensions[get_column_letter(x)].width = 16 ws.title='ORCSAE-FG' dateCell = ws['A1'] dateCell.value = '=today()' dateCell.number_format = 'mm/dd/yyyy;@' timeCell = ws['A2'] timeCell.value = '=now()' timeCell.number_format = 'hh:mm:ss AM/PM' ws['C1'] = 'Finished Goods Summary' ws['C1'].font = title_font ws['F1'] = 'Page 1' ws['C2'] = 'Confidential Information' ws['C2'].font = conf_font row_num = 3 # Assign the titles for each cell of the header for col_num, column_title in enumerate(headers, 1): cell = ws.cell(row=row_num, column=col_num) cell.value = column_title cell.font = header_font row_num = row_num + 1 cic_prev = '' item_prev = '' start_sum_row = 0 end_sum_row = 0 total_open = total_received = total_consumed = total_shipped = total_adjustment = total_end = 0 for txn in qs: cic = txn.cic item = txn.receiver.item.name if (cic != cic_prev): if start_sum_row != 0 or end_sum_row != 0: row_num = row_num + 1 row = [ cic_prev, '', '', item_prev, 'TOTAL', f'=SUM(F{start_sum_row}:F{end_sum_row})', f'=SUM(G{start_sum_row}:G{end_sum_row})', f'=SUM(H{start_sum_row}:H{end_sum_row})', f'=SUM(I{start_sum_row}:I{end_sum_row})', f'=SUM(J{start_sum_row}:J{end_sum_row})', f'=SUM(K{start_sum_row}:K{end_sum_row})' ] # Assign the data for each cell of the row for col_num, cell_value in enumerate(row, 1): cell = ws.cell(row=row_num, column=col_num) if cell_value != '': cell.value = cell_value cell.border = Border(top=Side(border_style="double")) cell.number_format = '#,##0' row_num = row_num + 2 start_sum_row = row_num row = [ txn.cic, txn.receiver.inv_class.name, txn.receiver.group.name, txn.receiver.item.name, txn.receiver.lot, txn.open_balance, txn.received_tot, txn.consumed_tot, txn.shipped_tot, txn.adjustment_tot, txn.end_balance ] # Assign the data for each cell of the row for col_num, cell_value in enumerate(row, 1): cell = ws.cell(row=row_num, column=col_num) cell.value = cell_value if col_num > 5: # only format number cells cell.number_format = '#,##0' total_open = total_open + txn.open_balance total_received = total_received + txn.received_tot total_consumed = total_consumed + txn.consumed_tot total_shipped = total_shipped + txn.shipped_tot total_adjustment = total_adjustment + txn.adjustment_tot total_end = total_end + txn.end_balance cic_prev = cic item_prev = item end_sum_row = row_num row_num = row_num + 1 row_num = row_num + 1 row = [ cic_prev, '', '', item_prev, 'TOTAL', f'=SUM(F{start_sum_row}:F{end_sum_row})', f'=SUM(G{start_sum_row}:G{end_sum_row})', f'=SUM(H{start_sum_row}:H{end_sum_row})', f'=SUM(I{start_sum_row}:I{end_sum_row})', f'=SUM(J{start_sum_row}:J{end_sum_row})', f'=SUM(K{start_sum_row}:K{end_sum_row})' ] # Assign the data for each cell of the row for col_num, cell_value in enumerate(row, 1): cell = ws.cell(row=row_num, column=col_num) if cell_value != '': cell.value = cell_value cell.border = Border(top=Side(border_style="double")) cell.number_format = '#,##0' row_num = row_num + 1 row = [ '', '', '', '', '', total_open, total_received, total_consumed, total_shipped, total_adjustment, total_end ] # Assign the data for each cell of the row for col_num, cell_value in enumerate(row, 1): cell = ws.cell(row=row_num, column=col_num) if cell_value != '': cell.value = cell_value cell.border = Border(top=Side(border_style="double")) cell.number_format = '#,##0' return wb def location_location_excel(wb, initial_filters, filters): # First get qs based on initial form filters qs = LocationByLocationQueryset(initial_filters) # Then apply front end filters filtered_qs = apply_fe_filters(filters, qs, ['receiver_id', 'receiver__item__name', 'row', 'receiver__lot', 'receiver__package_id', 'whse']) # Start building sheet title_font = Font(size=18, bold=True) conf_font = Font(italic=True) header_font = Font(bold=True,) # Define the titles for columns headers = [ 'Whse', 'Row', 'Receiver', 'Item', 'Lot', 'Package', 'Unit Weight', 'Qty. On Hand', 'Unit', 'Qty', ] ws = wb.active for x in range(1, len(headers)+1): ws.column_dimensions[get_column_letter(x)].width = 16 dateCell = ws['A1'] dateCell.value = '=today()' dateCell.number_format = 'mm/dd/yyyy;@' timeCell = ws['A2'] timeCell.value = '=now()' timeCell.number_format = 'hh:mm:ss AM/PM' ws['C1'] = 'Inventory Location Report By Location' ws['C1'].font = title_font ws['G1'] = 'Page 1' ws['C2'] = 'Confidential Information' ws['C2'].font = conf_font row_num = 3 # Assign the titles for each cell of the header for col_num, column_title in enumerate(headers, 1): cell = ws.cell(row=row_num, column=col_num) cell.value = column_title cell.font = header_font row_num = row_num + 1 for rec in filtered_qs: # Display row row = [ rec.whse, rec.row, rec.receiver_id, rec.receiver.item.name, rec.receiver.lot, rec.receiver.package_id, rec.receiver.unit_weight, rec.receiver.qty_on_hand, rec.units, rec.qty ] # Assign the data for each cell of the row for col_num, cell_value in enumerate(row, 1): cell = ws.cell(row=row_num, column=col_num) cell.value = cell_value if col_num > 6: cell.number_format = '#,##0' # Qty format row_num = row_num + 1 return wb def location_receiver_excel(wb, initial_filters, filters): # First get qs based on initial form filters qs = LocationByReceiverQueryset(initial_filters) # Then apply front end filters filtered_qs = apply_fe_filters(filters, qs, ['receiver_id', 'receiver__item__name', 'row', 'receiver__lot', 'receiver__package_id', 'whse', 'receiver__remarks']) # Start building sheet title_font = Font(size=18, bold=True) conf_font = Font(italic=True) header_font = Font(bold=True,) # Define the titles for columns headers = [ 'Receiver', 'Item', 'Lot', 'Package', 'Unit Weight', 'Qty. On Hand', 'Whse', 'Row', 'Unit', 'Qty', 'Remarks' ] ws = wb.active for x in range(1, len(headers)+1): ws.column_dimensions[get_column_letter(x)].width = 16 dateCell = ws['A1'] dateCell.value = '=today()' dateCell.number_format = 'mm/dd/yyyy;@' timeCell = ws['A2'] timeCell.value = '=now()' timeCell.number_format = 'hh:mm:ss AM/PM' ws['C1'] = 'Inventory Location Report By Receiver' ws['C1'].font = title_font ws['G1'] = 'Page 1' ws['C2'] = 'Confidential Information' ws['C2'].font = conf_font row_num = 3 # Assign the titles for each cell of the header for col_num, column_title in enumerate(headers, 1): cell = ws.cell(row=row_num, column=col_num) cell.value = column_title cell.font = header_font row_num = row_num + 1 for rec in filtered_qs: # Display row row = [ rec.receiver_id, rec.receiver.item.name, rec.receiver.lot, rec.receiver.package_id, rec.receiver.unit_weight, rec.receiver.qty_on_hand, rec.whse, rec.row, rec.units, rec.qty, rec.receiver.remarks ] # Assign the data for each cell of the row for col_num, cell_value in enumerate(row, 1): cell = ws.cell(row=row_num, column=col_num) cell.value = cell_value if col_num == 5 or col_num == 6 or col_num == 9 or col_num == 10: cell.number_format = '#,##0' # Qty format row_num = row_num + 1 return wb def location_item_excel(wb, initial_filters, filters): # First get qs based on initial form filters qs = LocationByItemQueryset(initial_filters) # Then apply front end filters filtered_qs = apply_fe_filters(filters, qs, ['receiver_id', 'receiver__item__name', 'receiver__cust_item_code', 'row', 'receiver__lot', 'receiver__package_id', 'whse', 'receiver__inv_class_id', 'receiver__group_id']) # Start building sheet title_font = Font(size=18, bold=True) conf_font = Font(italic=True) header_font = Font(bold=True,) # Define the titles for columns headers = [ 'Item', 'Cust Item Code', 'Class/Group', 'Receiver', 'Lot', 'Package/Unit Weight', 'Qty. On Hand', 'Whse', 'Row', 'Unit', 'Qty', ] ws = wb.active for x in range(1, len(headers)+1): ws.column_dimensions[get_column_letter(x)].width = 16 dateCell = ws['A1'] dateCell.value = '=today()' dateCell.number_format = 'mm/dd/yyyy;@' timeCell = ws['A2'] timeCell.value = '=now()' timeCell.number_format = 'hh:mm:ss AM/PM' ws['C1'] = 'Inventory Location Report' ws['C1'].font = title_font ws['F1'] = 'Page 1' ws['C2'] = 'Confidential Information' ws['C2'].font = conf_font row_num = 3 # Assign the titles for each cell of the header for col_num, column_title in enumerate(headers, 1): cell = ws.cell(row=row_num, column=col_num) cell.value = column_title cell.font = header_font row_num = row_num + 1 item_prev = '' for rec in filtered_qs: item = rec.receiver.item.name if (item != item_prev): # Display group block row row = [ item, rec.receiver.cust_item_code, f"{rec.receiver.inv_class}/{rec.receiver.group}", '', '', '', '', '', '', '', '' ] # Assign the data for each cell of the row for col_num, cell_value in enumerate(row, 1): cell = ws.cell(row=row_num, column=col_num) cell.value = cell_value cell.border = Border(top=Side(border_style="double")) row_num = row_num + 1 # Display row row = [ '', '', '', rec.receiver_id, rec.receiver.lot, f'{rec.receiver.package_id}/{rec.receiver.unit_weight}', rec.receiver.qty_on_hand, rec.whse, rec.row, rec.units, rec.qty ] # Assign the data for each cell of the row for col_num, cell_value in enumerate(row, 1): cell = ws.cell(row=row_num, column=col_num) cell.value = cell_value if col_num == 7 or col_num == 10 or col_num == 11: cell.number_format = '#,##0' # Qty format item_prev = item row_num = row_num + 1 return wb def inv_minimums_excel(wb, initial_filters): # First get qs based on initial form filters filtered_qs = InvMinimumsQueryset(initial_filters) # Start building sheet title_font = Font(size=18, bold=True) conf_font = Font(italic=True) header_font = Font(bold=True,) # Define the titles for columns headers_bldg = [ '', '', '', '', 'Bldg-1', '', 'Bldg-2', '', '', ] headers = [ 'Customer', 'Item', 'Cust Item Code', 'Package', 'Min', 'Reorder', 'Min', 'Reorder', 'Vendor', ] ws = wb.active for x in range(1, len(headers)+1): ws.column_dimensions[get_column_letter(x)].width = 16 dateCell = ws['A1'] dateCell.value = '=today()' dateCell.number_format = 'mm/dd/yyyy;@' timeCell = ws['A2'] timeCell.value = '=now()' timeCell.number_format = 'hh:mm:ss AM/PM' ws['C1'] = 'Inventory Minimums Report' ws['C1'].font = title_font ws['G1'] = 'Page 1' ws['C2'] = 'Confidential Information' ws['C2'].font = conf_font row_num = 3 # Assign the titles for each cell of the for col_num, column_title in enumerate(headers_bldg, 1): cell = ws.cell(row=row_num, column=col_num) cell.value = column_title cell.font = header_font row_num = row_num + 1 for col_num, column_title in enumerate(headers, 1): cell = ws.cell(row=row_num, column=col_num) cell.value = column_title cell.font = header_font row_num = row_num + 1 for rec in filtered_qs: # Display row row = [ rec.customer.name, rec.item.name, rec.cust_item_code, rec.p_category.name, rec.min_qty, rec.reorder_qty, rec.min_qty2, rec.reorder_qty2, rec.vendor_id ] # Assign the data for each cell of the row for col_num, cell_value in enumerate(row, 1): cell = ws.cell(row=row_num, column=col_num) cell.value = cell_value if col_num > 4 and col_num < 9: cell.number_format = '#,##0' # Qty format row_num = row_num + 1 return wb def inv_reorder_excel(wb, initial_filters): # First get qs based on initial form filters filtered_qs = InvReorderQueryset(initial_filters) # Start building sheet title_font = Font(size=18, bold=True) conf_font = Font(italic=True) header_font = Font(bold=True,) # Define the titles for columns headers_bldg = [ '', '', '', '', '', 'Bldg-1', '', '', 'Bldg-2', '', ] headers = [ 'Customer', 'Item', 'Cust Item Code', 'Package', 'Min', 'Reorder', 'Qty On Hand', 'Min', 'Reorder', 'Qty On Hand', ] ws = wb.active for x in range(1, len(headers)+1): ws.column_dimensions[get_column_letter(x)].width = 16 dateCell = ws['A1'] dateCell.value = '=today()' dateCell.number_format = 'mm/dd/yyyy;@' timeCell = ws['A2'] timeCell.value = '=now()' timeCell.number_format = 'hh:mm:ss AM/PM' ws['C1'] = 'Inventory Minimums Report' ws['C1'].font = title_font ws['G1'] = 'Page 1' ws['C2'] = 'Confidential Information' ws['C2'].font = conf_font row_num = 3 # Assign the titles for each cell of the for col_num, column_title in enumerate(headers_bldg, 1): cell = ws.cell(row=row_num, column=col_num) cell.value = column_title cell.font = header_font row_num = row_num + 1 for col_num, column_title in enumerate(headers, 1): cell = ws.cell(row=row_num, column=col_num) cell.value = column_title cell.font = header_font row_num = row_num + 1 for rec in filtered_qs: # Display row row = [ rec.customer.name, rec.item.name, rec.cust_item_code, rec.p_category.name, rec.min_qty, rec.reorder_qty, rec.qty_on_hand1, rec.min_qty2, rec.reorder_qty2, rec.qty_on_hand2, ] # Assign the data for each cell of the row for col_num, cell_value in enumerate(row, 1): cell = ws.cell(row=row_num, column=col_num) cell.value = cell_value if col_num > 5: cell.number_format = '#,##0' # Qty format row_num = row_num + 1 return wb def finduse_excel(wb, initial_filters): # First get qs based on initial form filters filtered_qs = InvFindUseQueryset(initial_filters) # Start building sheet title_font = Font(size=18, bold=True) conf_font = Font(italic=True) header_font = Font(bold=True,) # Define the titles for columns headers = [ 'Job', 'Product', 'Product Lot', 'Line', 'Job Complete', 'Qty Prod', 'Receiver', 'Item', 'Item Lot', 'Qty Consumed', ] ws = wb.active for x in range(1, len(headers)+1): ws.column_dimensions[get_column_letter(x)].width = 18 dateCell = ws['A1'] dateCell.value = '=today()' dateCell.number_format = 'mm/dd/yyyy;@' timeCell = ws['A2'] timeCell.value = '=now()' timeCell.number_format = 'hh:mm:ss AM/PM' ws['C1'] = 'Inventory Minimums Report' ws['C1'].font = title_font ws['G1'] = 'Page 1' ws['C2'] = 'Confidential Information' ws['C2'].font = conf_font row_num = 3 # Assign the titles for each cell of the for col_num, column_title in enumerate(headers, 1): cell = ws.cell(row=row_num, column=col_num) cell.value = column_title cell.font = header_font row_num = row_num + 1 for rec in filtered_qs: # Display row row = [ rec.job, rec.product, rec.product_lot, rec.line, remove_tz(rec.job_complete), rec.qty_produced, rec.receiver_id, rec.receiver.item.name, rec.receiver.lot, rec.qty_consumed, ] # Assign the data for each cell of the row for col_num, cell_value in enumerate(row, 1): cell = ws.cell(row=row_num, column=col_num) cell.value = cell_value if col_num == 6 or col_num == 10: cell.number_format = '#,##0' # Qty format row_num = row_num + 1 return wb def job_frm_fdr_run_export(wb, initial_filters, filename_fields): # First get qs based on initial form filters job, frm, frmitems, frmdevs, fdrlog, runlog, compounder_type, pelletizer_type, locked_status = JobFrmFdrRunQueryset(initial_filters) if locked_status != '': return None, locked_status # Don't run report if locked_status is not fully locked # Start building sheet title_font = Font(size=18, bold=True) conf_font = Font(italic=True) header_font = Font(bold=True) ws = wb.active for x in range(1, 13): ws.column_dimensions[get_column_letter(x)].width = 18 dateCell = ws['A1'] dateCell.value = '=today()' dateCell.number_format = 'mm/dd/yyyy;@' timeCell = ws['A2'] timeCell.value = '=now()' timeCell.number_format = 'hh:mm:ss AM/PM' ws['C1'] = 'Job/Formula/Feeder/Parameter Export' ws['C1'].font = title_font ws['G1'] = 'Page 1' ws['C2'] = 'Confidential Information' ws['C2'].font = conf_font ws['A3'].font = title_font ws['A3'] = 'Job Info' # Job Info labels = [ 'Job', 'Customer', 'Product', 'Ver', 'Status', 'Job Start', 'Job Complete', 'Qty Ordered', 'Qty Produced', 'Line', 'Lot', 'Changeover Code' ] values = [ job.job, job.customer_id, job.product, job.frm_ver, job.status_id, remove_tz(job.job_start), remove_tz(job.job_complete), job.qty_ordered, job.qty_produced, job.line_id, job.lot, job.cln_code ] col_num = 1 # Assign the labels for each cell for row_num, row_title in enumerate(labels, 4): cell = ws.cell(row=row_num, column=col_num) cell.value = row_title cell.font = header_font col_num = col_num + 1 # Assign the labels for each cell for row_num, row_value in enumerate(values, 4): cell = ws.cell(row=row_num, column=col_num) cell.value = row_value #Formula info and ingredients ws['A50'].font = title_font if frm: ws['A50'] = 'Formula Info for Job ' + job.job labels = [ 'Job', 'Customer', 'Product', 'Ver', 'Deviated Rework %', 'Confirmed By', 'Confirmed TS', 'Locked By', 'Locked TS', ] values = [ frm.job_id, frm.customer_id, frm.product.name, frm.frm_ver, frm.drw_pct, get_username(frm.confirmed_by), remove_tz(frm.confirmed_ts), get_username(frm.locked_by), remove_tz(frm.locked_ts), ] col_num = 1 # Assign the labels for each cell for row_num, row_title in enumerate(labels, 51): cell = ws.cell(row=row_num, column=col_num) cell.value = row_title cell.font = header_font col_num = col_num + 1 # Assign the labels for each cell for row_num, row_value in enumerate(values, 51): cell = ws.cell(row=row_num, column=col_num) cell.value = row_value ws['A61'].font = header_font ws['A61'] = 'Formula Ingredient Detail' # Define the titles for ingredients columns row_num = 62 headers = [ 'Item', 'Customer', 'Target', 'Adjust', 'Min', 'Max', 'Link ID', 'Link Total', ] for col_num, column_title in enumerate(headers, 1): cell = ws.cell(row=row_num, column=col_num) cell.value = column_title cell.font = header_font row_num = row_num + 1 for rec in frmitems: # Display row row = [ rec.item.name, rec.item_customer.name, rec.tgt, rec.adjust, rec.min, rec.max, rec.link_id, rec.link_total, ] # Assign the data for each cell of the row for col_num, cell_value in enumerate(row, 1): cell = ws.cell(row=row_num, column=col_num) cell.value = cell_value if col_num == 3 or col_num == 5 or col_num == 6 or col_num == 8: cell.number_format = '#,##0.0000' # 4 decimals row_num = row_num + 1 # Add FrmLog DevIDs associated (Board 99) ws['I61'].font = header_font ws['I61'] = 'DevIDs' row_num = 63 for rec in frm.frmdev_set.all(): # Display row row = [ rec.devid, ] # Assign the data for each cell of the row for col_num, cell_value in enumerate(row, 9): cell = ws.cell(row=row_num, column=col_num) cell.value = cell_value row_num = row_num + 1 # Add Frmlog Deviations list (Board 99) ws['J61'].font = header_font ws['J61'] = 'Deviations' # Define the titles for ingredients columns row_num = 62 headers = [ 'Receiver', 'Item', 'Qty on Hand', ] for col_num, column_title in enumerate(headers, 10): cell = ws.cell(row=row_num, column=col_num) cell.value = column_title cell.font = header_font row_num = row_num + 1 for rec in frmdevs: # Display row row = [ rec.receiver.receiver, rec.receiver.item.name, rec.receiver.qty_on_hand, ] # Assign the data for each cell of the row for col_num, cell_value in enumerate(row, 10): cell = ws.cell(row=row_num, column=col_num) cell.value = cell_value if col_num == 13: cell.number_format = '#,##0' # Qty format row_num = row_num + 1 else: ws['A50'] = 'No Formula Info for Job ' + job.job # Start Fdrlog row_num = 100 # Start feeder info on row 300 ws['A100'].font = title_font if fdrlog: ws['A100'] = 'Feeder Info for Job ' + job.job row_num = row_num + 1 fdrlog_headers = [ 'Feeder', 'Item', '%', 'Agitator', 'Screw/Tube', 'Switch', 'Refill', 'IFF', '', '#', 'Item', '%', '#', 'Item', '%' ] blend_headers = [ 'Blend', 'Item', '%', ] labels = [ 'Customer', 'Product', 'Ver', 'Line', 'Locked By', 'Locked TS', ] values = [ fdrlog.customer_id, fdrlog.product.name, fdrlog.frm_ver, fdrlog.line_id, get_username(fdrlog.locked_by), remove_tz(fdrlog.locked_ts), ] col_num = 1 counter = 0 # To keep track of how many rows to jump back up for 2nd column # Assign the labels for each cell for row_num, row_title in enumerate(labels, row_num): cell = ws.cell(row=row_num, column=col_num) cell.value = row_title cell.font = header_font counter = counter + 1 col_num = col_num + 1 # Assign the labels for each cell for row_num, row_value in enumerate(values, row_num-counter+1): cell = ws.cell(row=row_num, column=col_num) cell.value = row_value col_num = 8 for blender in range (1,3): labels = [ 'Blender ' + str(blender) + ' Info', 'Blender', 'Blend Size' ] values = [ getattr(fdrlog, 'b'+str(blender)+'_blender_id'), getattr(fdrlog, 'b'+str(blender)+'_size'), ] col_num = col_num + 2 row_num = row_num - counter + 1 counter = 0 # Assign the labels for each cell for row_num, row_title in enumerate(labels, row_num): cell = ws.cell(row=row_num, column=col_num) cell.value = row_title cell.font = header_font counter = counter + 1 col_num = col_num + 1 # Assign the labels for each cell for row_num, row_value in enumerate(values, row_num-counter+2): cell = ws.cell(row=row_num, column=col_num) cell.value = row_value row_num = row_num + 5 for col_num, column_title in enumerate(fdrlog_headers, 1): cell = ws.cell(row=row_num, column=col_num) cell.value = column_title cell.font = header_font row_num = row_num + 1 for feeder in range(1,10): if getattr(fdrlog, 'f'+str(feeder)+'_item') is not None: item_special = getattr(fdrlog, 'f'+str(feeder)+'_item').name elif getattr(fdrlog, 'f'+str(feeder)+'_special') is not None: item_special = getattr(fdrlog, 'f'+str(feeder)+'_special').name else: item_special = None row = [ 'F'+str(feeder), item_special, getattr(fdrlog, 'f'+str(feeder)+'_pct'), getattr(fdrlog, 'f'+str(feeder)+'_agitator') if item_special is not None else None, getattr(fdrlog, 'f'+str(feeder)+'_tube_screw_id'), getattr(fdrlog, 'f'+str(feeder)+'_switch_id'), getattr(fdrlog, 'f'+str(feeder)+'_refill'), getattr(fdrlog, 'f'+str(feeder)+'_iff'), ] for col_num, column_title in enumerate(row, 1): cell = ws.cell(row=row_num, column=col_num) cell.value = column_title row_num = row_num + 1 row_num = row_num - 9 col_num = col_num + 2 for blender1 in range (1,25): if getattr(fdrlog, 'b1itm'+str(blender1)+'_item') is not None: item = getattr(fdrlog, 'b1itm'+str(blender1)+'_item').name else: item = None row = [ str(blender1), item, getattr(fdrlog, 'b1itm'+str(blender1)+'_pct'), ] for col_num, column_title in enumerate(row, col_num): cell = ws.cell(row=row_num, column=col_num) cell.value = column_title row_num = row_num + 1 col_num = col_num -2 row_num = row_num - 24 col_num = col_num + 3 for blender2 in range (1,7): if getattr(fdrlog, 'b2itm'+str(blender2)+'_item') is not None: item = getattr(fdrlog, 'b2itm'+str(blender2)+'_item').name else: item = None row = [ str(blender2), item, getattr(fdrlog, 'b2itm'+str(blender2)+'_pct'), ] for col_num, column_title in enumerate(row, col_num): cell = ws.cell(row=row_num, column=col_num) cell.value = column_title row_num = row_num + 1 col_num = col_num -2 row_num = row_num + 19 else: ws['A100'] = 'No Feeder Info for Job ' + job.job # Runlog start row_num = 150 # Start Paramater info on row 150 ws['A150'].font = title_font if runlog: ws['A150'] = 'Parameter Info for Job ' + job.job row_num = row_num + 1 labels = [ 'Customer', 'Product', 'Ver', 'Line', 'Compounder Type', 'Pelletizer Type', 'Locked By', 'Locked TS', ] values = [ runlog.customer_id, runlog.product.name, runlog.frm_ver, runlog.line_id, compounder_type, pelletizer_type, get_username(runlog.locked_by), remove_tz(runlog.locked_ts), ] col_num = 1 counter = 0 # To keep track of how many rows to jump back up for 2nd column # Assign the labels for each cell for row_num, row_title in enumerate(labels, row_num): cell = ws.cell(row=row_num, column=col_num) cell.value = row_title cell.font = header_font counter = counter + 1 col_num = col_num + 1 # Assign the labels for each cell for row_num, row_value in enumerate(values, row_num-counter+1): cell = ws.cell(row=row_num, column=col_num) cell.value = row_value row_num = row_num + 1 # Disaply all runlog fields and labels from database (Board 111) labels = [] values = [] labels_col = [ 'Z1', 'Z2', 'Z3', 'Z4', 'Z5', 'Z6', 'Z7', 'Z8', 'Z9', 'Z10', 'Z11', 'Z12', 'Z13', 'Z14', 'Z15' ] labels.append(labels_col) values_col = [ runlog.z1, runlog.z2, runlog.z3, runlog.z4, runlog.z5, runlog.z6, runlog.z7, runlog.z8, runlog.z9, runlog.z10, runlog.z11, runlog.z12, runlog.z13, runlog.z14, runlog.z15 ] values.append(values_col) labels_col = [ 'z16', 'z17', 'die', 'melt', 'screw', 'nitrogen', 'vent1', 'vent2', 'vent3', 'screen_pack', 'up_press', 'dn_press', 'feed_rate', 'rpm1', 'pwr1' ] labels.append(labels_col) values_col = [ runlog.z16, runlog.z17, runlog.die, runlog.melt, runlog.screw, runlog.nitrogen, runlog.vent1, runlog.vent2, runlog.vent3, runlog.screen_pack, runlog.up_press, runlog.dn_press, runlog.feed_rate, runlog.rpm1, runlog.pwr1 ] values.append(values_col) labels_col = [ 'rpm2', 'pwr2', 'die_size', 'die_holes', 'die_pattern', 'spring', 'blades', 'cutter_rpm', 'pipe', 'water_temp', 'pelletizer', 'water_bath', 'air_knife', 'class_hole_size', 'metal_sep' ] labels.append(labels_col) values_col = [ runlog.rpm2, runlog.pwr2, runlog.die_size, runlog.die_holes, runlog.die_pattern, runlog.spring, runlog.blades, runlog.cutter_rpm, runlog.pipe, runlog.water_temp, runlog.pelletizer, runlog.water_bath, runlog.air_knife, runlog.class_hole_size, runlog.metal_sep ] values.append(values_col) """ Cuatomize labels and fields based on line - prefer all fields in data dump if compounder_type == 'CM': labels_col = [ 'MFH', 'MCB', 'Orifice', 'ExtR', 'ExtV', 'ExtC', 'ExtF', 'SC-CE', 'SC-B', 'SC-FE', 'Adapt', 'Die', 'MT', 'Screen Pack' ] labels.append(labels_col) values_col = [ runlog.z1, runlog.z2, runlog.z3, runlog.z4, runlog.z5, runlog.z6, runlog.z7, runlog.z8, runlog.z9, runlog.z10, runlog.z11, runlog.die, runlog.melt, runlog.screen_pack ] values.append(values_col) labels_col = [ 'Rate', 'Mixer RPM', 'Mixer Amps', 'Orifice', 'Ext Vac', 'Ext RPM', 'Ext Amps', 'Up Press', 'Dn Press' ] labels.append(labels_col) values_col = [ runlog.feed_rate, runlog.rpm1, runlog.pwr1, runlog.vent1, runlog.vent2, runlog.rpm2, runlog.pwr2, runlog.up_press, runlog.dn_press ] values.append(values_col) else: labels_col = [ 'Z1', 'Z2', 'Z3', 'Z4', 'Z5', 'Z6', 'Z7', 'Z8', 'Z9', 'Z10', 'Z11', 'Z12', 'Z13', 'Z14', 'Die', 'MT', 'Screen Pack' ] labels.append(labels_col) values_col = [ runlog.z1, runlog.z2, runlog.z3, runlog.z4, runlog.z5, runlog.z6, runlog.z7, runlog.z8, runlog.z9, runlog.z10, runlog.z11, runlog.z12, runlog.z13, runlog.z14, runlog.die, runlog.melt, runlog.screen_pack ] values.append(values_col) labels_col = [ 'Screw', 'Rate', 'RPM', 'Torque %', 'Nitrogen', 'Vent 1', 'Vent 2', 'Vent 3', 'Up Press', 'Dn Press' ] labels.append(labels_col) values_col = [ runlog.screw, runlog.feed_rate, runlog.rpm1, runlog.pwr1, runlog.nitrogen, runlog.vent1, runlog.vent2, runlog.vent3, runlog.up_press, runlog.dn_press ] values.append(values_col) labels_col = [ 'Die Size', '# Holes', 'Die Pattern', 'Water Temp', 'Pltzr Type', 'Bath Config', 'Air Knife', 'Class Deck', 'Metal Sep' ] labels.append(labels_col) values_col = [ runlog.die_size, runlog.die_holes, runlog.die_pattern, runlog.water_temp, runlog.pelletizer, runlog.water_bath, runlog.air_knife, runlog.class_hole_size, runlog.metal_sep ] values.append(values_col) """ col_num = 1 counter_prev = 0 for data_col in range(3): counter = 0 # To keep track of how many rows to jump back up for 2nd column # Assign the labels for each cell for row_num, row_title in enumerate(labels[data_col], row_num-counter_prev+1): cell = ws.cell(row=row_num, column=col_num) cell.value = row_title cell.font = header_font counter = counter + 1 col_num = col_num + 1 # Assign the labels for each cell for row_num, row_value in enumerate(values[data_col], row_num-counter+1): cell = ws.cell(row=row_num, column=col_num) cell.value = row_value col_num = col_num + 1 counter_prev = counter row_num = row_num + 10 else: ws['A150'] = 'No Parameter Info for Job ' + job.job # Generate custom filename based filename_fields fields = filename_fields.split('_') data_list = [] for field in fields: data_list.append(str(getattr(job, field))) filename = '_'.join(data_list) return wb, filename def group_permissions_excel(wb): # First get qs based on initial form filters qs = GroupPermissionsQueryset() # Start building sheet title_font = Font(size=18, bold=True) conf_font = Font(italic=True) header_font = Font(bold=True,) # Define the titles for columns headers = [ '', ] ws = wb.active for x in range(1, len(headers)+1): ws.column_dimensions[get_column_letter(x)].width = 16 dateCell = ws['A1'] dateCell.value = '=today()' dateCell.number_format = 'mm/dd/yyyy;@' timeCell = ws['A2'] timeCell.value = '=now()' timeCell.number_format = 'hh:mm:ss AM/PM' ws['C1'] = 'User Groups and Permissions Report' ws['C1'].font = title_font ws['G1'] = 'Page 1' ws['C2'] = 'Confidential Information' ws['C2'].font = conf_font row_num = 3 # Assign the titles for each cell of the header for col_num, column_title in enumerate(headers, 1): cell = ws.cell(row=row_num, column=col_num) cell.value = column_title cell.font = header_font row_num = row_num + 1 for group_name, permissions in qs.items(): # Display Group name row = [ group_name, ] # Assign the data for each cell of the row for col_num, cell_value in enumerate(row, 1): cell = ws.cell(row=row_num, column=col_num) cell.value = cell_value cell.font = title_font row_num = row_num + 1 for permission in permissions: row = [ permission, ] # Assign the data for each cell of the row for col_num, cell_value in enumerate(row, 1): cell = ws.cell(row=row_num, column=col_num) cell.value = cell_value row_num = row_num + 1 return wb def generate_excel(filename_fields, report_name, load_template, initial_filters, filters, username, buildings): if load_template: wb = load_workbook(filename=os.path.join(settings.EXPORT_TEMPLATES, load_template)) else: wb = Workbook() # insert report generation info into a sheet filter_sheet = wb.create_sheet('Filters') filter_sheet.column_dimensions['A'].width = 16 filter_sheet.column_dimensions['B'].width = 100 filter_sheet['A1'] = 'Initial Filters' filter_sheet['A2'] = 'Front end filters' filter_sheet['A3'] = 'Username' filter_sheet['A4'] = 'Buildings' filter_sheet['A5'] = 'Report Name' filter_sheet['B1'] = str(initial_filters) filter_sheet['B2'] = filters filter_sheet['B3'] = username filter_sheet['B4'] = buildings filter_sheet['B5'] = report_name ws = wb.active ws.title=report_name if report_name == 'onhand_receiver': wb = inv_on_hand_excel(wb, initial_filters, filters, buildings) elif report_name == 'onhand_item': wb = inv_on_hand_item_excel(wb, initial_filters, filters) elif report_name == 'onhand_package': wb = inv_on_hand_package_excel(wb, initial_filters, filters) elif report_name == 'orcsae': wb = orcsae_excel(wb, initial_filters) elif report_name == 'orcsae_rm': wb = orcsae_rm_excel(wb, initial_filters) elif report_name == 'orcsae_fg': wb = orcsae_fg_excel(wb, initial_filters) elif report_name == 'txn_summary': wb = txn_summary_excel(wb, initial_filters) elif report_name == 'location_location': wb = location_location_excel(wb, initial_filters, filters) elif report_name == 'location_receiver': wb = location_receiver_excel(wb, initial_filters, filters) elif report_name == 'location_item': wb = location_item_excel(wb, initial_filters, filters) elif report_name == 'inv_minimums': wb = inv_minimums_excel(wb, initial_filters) elif report_name == 'inv_reorder': wb = inv_reorder_excel(wb, initial_filters) elif report_name == 'finduse': wb = finduse_excel(wb, initial_filters) elif report_name == 'group_permissions': wb = group_permissions_excel(wb) elif report_name == 'Informant': wb, report_name = job_frm_fdr_run_export(wb, initial_filters, filename_fields) # filename_fields used to generate filename based on data fields. Returns filename as report_name to be used to save """ consider: functions = { 'onhand_receiver': inv_on_hand_excel, 'onhand_item': inv_on_hand_item_excel, 'onhand_package': inv_on_hand_package_excel, 'orcsae': orcsae_excel, 'orcsae_rm': orcsae_rm_excel, 'orcsae_fg': orcsae_fg_excel, 'txn_summary': txn_summary_excel, 'location_location': location_location_excel, 'location_receiver': location_receiver_excel, 'location_item': location_item_excel, 'inv_minimums': inv_minimums_excel, 'inv_reorder': inv_reorder_excel, 'finduse': finduse_excel, 'job_frm_fdr_run': job_frm_fdr_run_export } if report_name in functions: wb = functions[report_name](wb, initial_filters, filters, buildings) # OK to send unused paramters? else: print(f"Invalid report name: {report_name}") """ if wb is None: return report_name # report_name is the locked_status if wb is None if filename_fields: # Don't include username if filename is generated from data filename = report_name + '.xlsx' else: filename = report_name + '_' + username + '.xlsx' filepath = os.path.join(settings.EXPORT_ROOT, 'excel_templates') full_path = os.path.join(filepath, filename) wb.save(full_path) return filename @ensure_csrf_cookie def export_excel(request): initial_filters = request.POST.get('export_initial_filters', None) if(initial_filters): report_name = request.POST.get('export_report_name') initial_filters = initial_filters.replace("'", '"') initial_filters = json.loads(initial_filters) filters = request.POST.get('export_filters', '') username = request.POST.get('export_username') buildings = request.POST.get('export_buildings', None) load_template = request.POST.get('export_load_template', None) filename_fields = request.POST.get('export_filename', None) # filename_fields used to generate filename based on data fields filename = generate_excel(filename_fields, report_name, load_template, initial_filters, filters, username, buildings) else: filename = request.POST.get('export_filename') if filename.startswith('Logs not locked'): messages.warning(request, "Unable to run export. " + filename) return redirect(reverse('export_job_frm_options')) filepath = os.path.join(settings.EXPORT_ROOT, 'excel_templates') full_path = os.path.join(filepath, filename) if request.POST.get('log_export', None): log_entries = ExportLog.objects.filter(filename=filename, filepath=filepath) if log_entries.exists(): log_entry = log_entries.first() # There should not be more than one log_entry.updated_by = request.user log_entry.updated_ts = timezone.now() log_entry.overwritten = True log_entry.save() else: log_entry = ExportLog(report_name='JOBPACK', filename=filename, filepath=filepath, created_by=request.user, created_ts=timezone.now()) log_entry.save() with open(full_path, 'rb') as f: response = HttpResponse(f.read(), content_type='application/vnd.ms-excel') response['Content-Disposition'] = 'attachment; filename=' + filename response['Content-Type'] = 'application/vnd.ms-excel; charset=utf-16' return response #return HttpResponseRedirect(reverse('home')) # Mkae the file download via js ajax so that a redirect can be issued after the file download is complete