Source code for import.functions
from django.conf import settings
from cellar.settings import MEDIA_ROOT
from core.models import WineLocationValues,Addition
from openpyxl import load_workbook,Workbook
from tempfile import NamedTemporaryFile
from django.http import HttpResponse
import os
import glob
from django.conf import settings
[docs]
def func_chk():
status = "success"
return status
[docs]
def addition_check():
error_list=[]
errormsg = str('A list of the errors found when running addition check')
error_list.append(errormsg)
error_list.append('\n')
errormsg = str(r'Go to \info\audit\errrors to see how to fix each possible error')
error_list.append(errormsg)
error_list.append('\n')
total_wine_qty=0
total_wlv_qty =0
addition_records = Addition.objects.filter(quantity__gt=0,addition_status='Put Away')|Addition.objects.filter(quantity__gt=0,addition_status='Tried')
w= WineLocationValues.objects.all()
for x in addition_records: # iterate thru additions and check qty against wlv qty
total_wine_qty = total_wine_qty+x.quantity # so we can check total qty of addition matches total wlv
wine_id_qty = x.quantity # now check each addition item
wlv_id_qty=0 # set the id to 0 as we start the process
wcheck = w.filter(wine_id=x.id) # get wlv records for this wine id
wcount = wcheck.count() #count how many records have been found
if wcount > 0: # we have found record(s) so can iterate through each one
wcounter = 0 # wcounter used to see how many wlv records found
for z in wcheck:
wcounter = wcounter +1 # increment counter
wlv_id_qty =wlv_id_qty + z.quantity # get the wlv quantity for this wine
else:
status_check = 'errors'
str_id=str(x.id)
errormsg = 'No Wine Location records found for ' + str(x.name) + ' ID(' + str_id + ') Quantity = ' + str(x.quantity)
error_list.append(errormsg)
error_list.append('\n')
if wcount != 0:
if wlv_id_qty != wine_id_qty :
status_check = 'errors'
str_id=str(x.id)
errormsg='Location quantity mismatch for ' + str(x.name) + ' ID(' + str_id + ') Quantity = ' + str(x. quantity) + ' Wine Location qty = ' + str(wlv_id_qty) + ' Wine location records are :' + str(wcheck)
error_list.append(errormsg)
error_list.append('\n')
return status_check,error_list
[docs]
def write_errors(errors):
#passed a list of errors and writes to a
response = HttpResponse(content_type='text/plain')
response['Content-Disposition'] = 'attachment; filename="audit_error.txt"'
# \n is a line boundary
#sentence = 'I\nlove\nPython\nProgramming.'
#errors is a text string with \nat the end of each message
# returns a list after spliting string at line breaks
error_list = errors.splitlines()
#print(resulting_list)
# Output: ['I', 'love', 'Python', 'Programming.']
response.write(error_list)
return response
[docs]
def wlv_data_load(path, sheet_name):
print('Starting wlv import')
workbook=load_workbook(filename=path)
# Check Sheet Name
if sheet_name not in workbook.sheetnames:
print(f"'{sheet_name}' not found. Quitting.")
return
print ('Testfile and sheet found')
sheet = workbook[sheet_name]
# Set the max row for data processing
data_row = sheet.max_row
print( 'Last data row = ',data_row )
count1 = WineLocationValues.objects.count()
print('process start - record count = ',count1)
# delete all records do we dont have duplicates
WineLocationValues.objects.all().delete()
count2 = WineLocationValues.objects.count()
print('post delete process: record count = ',count2)
i=1 # So we start at row 2
fail_count = 0
for value in sheet.iter_rows(min_row=2,max_row=data_row,values_only=True):
i= i+1
loctype_val, locname_val, locid_val, wine_val,wineid_val,qty_val,style_val,style_id_val,year_val,country_val,size_val = value
if loctype_val=='Rack': # make sure Rack values are 1
qty_val= '1'
try:
WineLocationValues.objects.create(quantity=qty_val,
location_id=locid_val,
wine_id=wineid_val,
country=country_val,
location_type=loctype_val,
price='0.00',
vintage=year_val,
size=size_val,
style_id=style_id_val)
#print(value)
except:
fail_count = fail_count + 1
print('wlv data row load failed ',i)
print (i-1,'Rows read - excluding header')
print (fail_count,'Rows failed - review log for failing rows')
# checks - number of rows processed = number of rows on file
#return number of failed rows
#return some sort of coded error for rows processed ne rows on file
count3 = WineLocationValues.objects.count()
print('final step process: record count = ',count3 )
[docs]
def create_template():
mroot = MEDIA_ROOT
# Set Template directory and filename
template_file = 'template.xlsx'
directory = str(os.path.join(MEDIA_ROOT, 'template'))
print('directory path = ',directory)
path = str(os.path.join(directory,template_file))
print('template path = ',path)
# Remove any old Template files
files =glob.glob(directory)
for file in files:
if os.path.exists(file):
try:
os.remove(path)
print(f"{file} removed successfully")
except OSError as error:
print(error)
print("File path cannot be removed")
# Prep workbook for writing
workbook = Workbook()
sheet = workbook.active
sheet.title = 'Load'
workbook.save(path)
[docs]
def wlv_stream(request):
# Create a workbook (e.g., add sheets, populate data)
wb = Workbook()
with NamedTemporaryFile() as tmp:
wb.save(tmp.name)
tmp.seek(0)
stream = tmp.read()
ws = wb.active
ws['A1'] = 'Hello, World!'
# Generate the response using save_virtual_workbook
response = HttpResponse(wb, content_type='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename="my_workbook.xlsx"'
return response