def handle(self, **options):
# Pick up the options
now = datetime.now()
self.database = options["database"]
if self.database not in settings.DATABASES:
raise CommandError("No database settings known for '%s'" %
self.database)
if options["user"]:
try:
self.user = (User.objects.all().using(
self.database).get(username=options["user"]))
except Exception:
raise CommandError("User '%s' not found" % options["user"])
else:
self.user = None
timestamp = now.strftime("%Y%m%d%H%M%S")
if self.database == DEFAULT_DB_ALIAS:
logfile = "importfromfolder-%s.log" % timestamp
else:
logfile = "importfromfolder_%s-%s.log" % (self.database, timestamp)
try:
handler = logging.FileHandler(os.path.join(settings.FREPPLE_LOGDIR,
logfile),
encoding="utf-8")
# handler.setFormatter(logging.Formatter(settings.LOGGING['formatters']['simple']['format']))
logger.addHandler(handler)
logger.propagate = False
except Exception as e:
print("%s Failed to open logfile %s: %s" %
(datetime.now(), logfile, e))
task = None
errors = [0, 0]
try:
setattr(_thread_locals, "database", self.database)
# Initialize the task
if options["task"]:
try:
task = (Task.objects.all().using(
self.database).get(pk=options["task"]))
except Exception:
raise CommandError("Task identifier not found")
if (task.started or task.finished or task.status != "Waiting"
or task.name not in ("frepple_importfromfolder",
"importfromfolder")):
raise CommandError("Invalid task identifier")
task.status = "0%"
task.started = now
task.logfile = logfile
else:
task = Task(
name="importfromfolder",
submitted=now,
started=now,
status="0%",
user=self.user,
logfile=logfile,
)
task.processid = os.getpid()
task.save(using=self.database)
# Choose the right self.delimiter and language
self.delimiter = (get_format("DECIMAL_SEPARATOR",
settings.LANGUAGE_CODE, True) == ","
and ";" or ",")
translation.activate(settings.LANGUAGE_CODE)
self.SQLrole = settings.DATABASES[self.database].get(
"SQL_ROLE", "report_role")
# Execute
if "FILEUPLOADFOLDER" in settings.DATABASES[
self.database] and os.path.isdir(
settings.DATABASES[self.database]["FILEUPLOADFOLDER"]):
# Open the logfile
logger.info("%s Started importfromfolder\n" %
datetime.now().replace(microsecond=0))
all_models = [(ct.model_class(), ct.pk)
for ct in ContentType.objects.all()
if ct.model_class()]
models = []
for ifile in os.listdir(
settings.DATABASES[self.database]["FILEUPLOADFOLDER"]):
if not ifile.lower().endswith((
".sql",
".sql.gz",
".csv",
".csv.gz",
".cpy",
".cpy.gz",
".xlsx",
)):
continue
filename0 = ifile.split(".")[0].split(" (")[0]
model = None
contenttype_id = None
for m, ct in all_models:
if matchesModelName(filename0, m):
model = m
contenttype_id = ct
break
if not model or model in EXCLUDE_FROM_BULK_OPERATIONS:
logger.info(
"%s Ignoring data in file: %s" %
(datetime.now().replace(microsecond=0), ifile))
elif self.user and not self.user.has_perm("%s.%s" % (
model._meta.app_label,
get_permission_codename("add", model._meta),
)):
# Check permissions
logger.info(
"%s You don't have permissions to add: %s" %
(datetime.now().replace(microsecond=0), ifile))
else:
deps = set([model])
GridReport.dependent_models(model, deps)
models.append((ifile, model, contenttype_id, deps))
# Sort the list of models, based on dependencies between models
models = GridReport.sort_models(models)
i = 0
cnt = len(models)
for ifile, model, contenttype_id, dependencies in models:
task.status = str(int(10 + i / cnt * 80)) + "%"
task.message = "Processing data file %s" % ifile
task.save(using=self.database)
i += 1
filetoparse = os.path.join(
os.path.abspath(settings.DATABASES[self.database]
["FILEUPLOADFOLDER"]),
ifile,
)
if ifile.lower().endswith((".sql", ".sql.gz")):
logger.info(
"%s Started executing SQL statements from file: %s"
% (datetime.now().replace(microsecond=0), ifile))
errors[0] += self.executeSQLfile(filetoparse)
logger.info(
"%s Finished executing SQL statements from file: %s"
% (datetime.now().replace(microsecond=0), ifile))
elif ifile.lower().endswith((".cpy", ".cpy.gz")):
logger.info(
"%s Started uploading copy file: %s" %
(datetime.now().replace(microsecond=0), ifile))
errors[0] += self.executeCOPYfile(model, filetoparse)
logger.info(
"%s Finished uploading copy file: %s" %
(datetime.now().replace(microsecond=0), ifile))
elif ifile.lower().endswith(".xlsx"):
logger.info(
"%s Started processing data in Excel file: %s" %
(datetime.now().replace(microsecond=0), ifile))
returnederrors = self.loadExcelfile(model, filetoparse)
errors[0] += returnederrors[0]
errors[1] += returnederrors[1]
logger.info(
"%s Finished processing data in file: %s" %
(datetime.now().replace(microsecond=0), ifile))
else:
logger.info(
"%s Started processing data in CSV file: %s" %
(datetime.now().replace(microsecond=0), ifile))
returnederrors = self.loadCSVfile(model, filetoparse)
errors[0] += returnederrors[0]
errors[1] += returnederrors[1]
logger.info(
"%s Finished processing data in CSV file: %s" %
(datetime.now().replace(microsecond=0), ifile))
else:
errors[0] += 1
cnt = 0
logger.error("%s Failed, folder does not exist" %
datetime.now().replace(microsecond=0))
# Task update
if errors[0] > 0:
task.status = "Failed"
if not cnt:
task.message = "Destination folder does not exist"
else:
task.message = (
"Uploaded %s data files with %s errors and %s warnings"
% (cnt, errors[0], errors[1]))
else:
task.status = "Done"
task.message = "Uploaded %s data files with %s warnings" % (
cnt,
errors[1],
)
task.finished = datetime.now()
except KeyboardInterrupt:
if task:
task.status = "Cancelled"
task.message = "Cancelled"
logger.info("%s Cancelled\n" %
datetime.now().replace(microsecond=0))
except Exception as e:
logger.error("%s Failed" % datetime.now().replace(microsecond=0))
if task:
task.status = "Failed"
task.message = "%s" % e
raise e
finally:
setattr(_thread_locals, "database", None)
if task:
if errors[0] == 0:
task.status = "Done"
else:
task.status = "Failed"
task.processid = None
task.finished = datetime.now()
task.save(using=self.database)
logger.info("%s End of importfromfolder\n" %
datetime.now().replace(microsecond=0))
def importWorkbook(request):
"""
This method reads a spreadsheet in Office Open XML format (typically with
the extension .xlsx or .ods).
Each entity has a tab in the spreadsheet, and the first row contains
the fields names.
"""
# Build a list of all contenttypes
all_models = [(ct.model_class(), ct.pk)
for ct in ContentType.objects.all() if ct.model_class()]
try:
# Find all models in the workbook
for filename, file in request.FILES.items():
yield "<strong>" + force_text(
_("Processing file")) + " " + filename + "</strong><br>"
if filename.endswith(".xls"):
yield _(
"Files in the old .XLS excel format can't be read.<br>Please convert them to the new .XLSX format."
)
continue
elif (file.content_type !=
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
):
yield _("Unsupported file format.")
continue
wb = load_workbook(filename=file, read_only=True, data_only=True)
models = []
for ws_name in wb.sheetnames:
# Find the model
model = None
contenttype_id = None
for m, ct in all_models:
if matchesModelName(ws_name, m):
model = m
contenttype_id = ct
break
if not model or model in EXCLUDE_FROM_BULK_OPERATIONS:
yield '<div class="alert alert-warning">' + force_text(
_("Ignoring data in worksheet: %s") %
ws_name) + "</div>"
elif not request.user.has_perm("%s.%s" % (
model._meta.app_label,
get_permission_codename("add", model._meta),
)):
# Check permissions
yield '<div class="alert alert-danger">' + force_text(
_("You don't permissions to add: %s") %
ws_name) + "</div>"
else:
deps = set([model])
GridReport.dependent_models(model, deps)
models.append((ws_name, model, contenttype_id, deps))
# Sort the list of models, based on dependencies between models
models = GridReport.sort_models(models)
# Process all rows in each worksheet
for ws_name, model, contenttype_id, dependencies in models:
with transaction.atomic(using=request.database):
yield "<strong>" + force_text(
_("Processing data in worksheet: %s") %
ws_name) + "</strong><br>"
yield (
'<div class="table-responsive">'
'<table class="table table-condensed" style="white-space: nowrap;"><tbody>'
)
numerrors = 0
numwarnings = 0
firsterror = True
ws = wb[ws_name]
for error in parseExcelWorksheet(
model,
ws,
user=request.user,
database=request.database,
ping=True,
):
if error[0] == logging.DEBUG:
# Yield some result so we can detect disconnect clients and interrupt the upload
yield " "
continue
if firsterror and error[0] in (logging.ERROR,
logging.WARNING):
yield '<tr><th class="sr-only">%s</th><th>%s</th><th>%s</th><th>%s</th><th>%s%s%s</th></tr>' % (
capfirst(_("worksheet")),
capfirst(_("row")),
capfirst(_("field")),
capfirst(_("value")),
capfirst(_("error")),
" / ",
capfirst(_("warning")),
)
firsterror = False
if error[0] == logging.ERROR:
yield '<tr><td class="sr-only">%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s: %s</td></tr>' % (
ws_name,
error[1] if error[1] else "",
error[2] if error[2] else "",
error[3] if error[3] else "",
capfirst(_("error")),
error[4],
)
numerrors += 1
elif error[1] == logging.WARNING:
yield '<tr><td class="sr-only">%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s: %s</td></tr>' % (
ws_name,
error[1] if error[1] else "",
error[2] if error[2] else "",
error[3] if error[3] else "",
capfirst(_("warning")),
error[4],
)
numwarnings += 1
else:
yield '<tr class=%s><td class="sr-only">%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>' % (
"danger" if numerrors > 0 else "success",
ws_name,
error[1] if error[1] else "",
error[2] if error[2] else "",
error[3] if error[3] else "",
error[4],
)
yield "</tbody></table></div>"
yield "<div><strong>%s</strong><br><br></div>" % _("Done")
except GeneratorExit:
logger.warning("Connection Aborted")
except Exception as e:
yield "Import aborted: %s" % e
logger.error("Exception importing workbook: %s" % e)
def handle(self, **options):
# Pick up the options
now = datetime.now()
self.database = options['database']
if self.database not in settings.DATABASES:
raise CommandError("No database settings known for '%s'" % self.database )
if options['user']:
try:
self.user = User.objects.all().using(self.database).get(username=options['user'])
except:
raise CommandError("User '%s' not found" % options['user'] )
else:
self.user = None
timestamp = now.strftime("%Y%m%d%H%M%S")
if self.database == DEFAULT_DB_ALIAS:
logfile = 'importfromfolder-%s.log' % timestamp
else:
logfile = 'importfromfolder_%s-%s.log' % (self.database, timestamp)
try:
handler = logging.FileHandler(os.path.join(settings.FREPPLE_LOGDIR, logfile), encoding='utf-8')
# handler.setFormatter(logging.Formatter(settings.LOGGING['formatters']['simple']['format']))
logger.addHandler(handler)
logger.propagate = False
except Exception as e:
print("%s Failed to open logfile %s: %s" % (datetime.now(), logfile, e))
task = None
errors = [0, 0]
returnederrors = [0, 0]
try:
setattr(_thread_locals, 'database', self.database)
# Initialize the task
if options['task']:
try:
task = Task.objects.all().using(self.database).get(pk=options['task'])
except:
raise CommandError("Task identifier not found")
if task.started or task.finished or task.status != "Waiting" or task.name not in ('frepple_importfromfolder', 'importfromfolder'):
raise CommandError("Invalid task identifier")
task.status = '0%'
task.started = now
task.logfile = logfile
else:
task = Task(name='importfromfolder', submitted=now, started=now, status='0%', user=self.user, logfile=logfile)
task.processid = os.getpid()
task.save(using=self.database)
# Choose the right self.delimiter and language
self.delimiter = get_format('DECIMAL_SEPARATOR', settings.LANGUAGE_CODE, True) == ',' and ';' or ','
translation.activate(settings.LANGUAGE_CODE)
# Execute
if 'FILEUPLOADFOLDER' in settings.DATABASES[self.database] \
and os.path.isdir(settings.DATABASES[self.database]['FILEUPLOADFOLDER']):
# Open the logfile
logger.info("%s Started importfromfolder\n" % datetime.now().replace(microsecond=0))
all_models = [ (ct.model_class(), ct.pk) for ct in ContentType.objects.all() if ct.model_class() ]
models = []
for ifile in os.listdir(settings.DATABASES[self.database]['FILEUPLOADFOLDER']):
if not ifile.lower().endswith(('.csv', '.csv.gz', '.xlsx')):
continue
filename0 = ifile.split('.')[0]
model = None
contenttype_id = None
for m, ct in all_models:
if matchesModelName(filename0, m):
model = m
contenttype_id = ct
logger.info("%s Matched a model to file: %s" % (datetime.now().replace(microsecond=0), ifile))
break
if not model or model in EXCLUDE_FROM_BULK_OPERATIONS:
logger.info("%s Ignoring data in file: %s" % (datetime.now().replace(microsecond=0), ifile))
elif self.user and not self.user.has_perm('%s.%s' % (model._meta.app_label, get_permission_codename('add', model._meta))):
# Check permissions
logger.info("%s You don't have permissions to add: %s" % (datetime.now().replace(microsecond=0), ifile))
else:
deps = set([model])
GridReport.dependent_models(model, deps)
models.append( (ifile, model, contenttype_id, deps) )
# Sort the list of models, based on dependencies between models
models = GridReport.sort_models(models)
i = 0
cnt = len(models)
for ifile, model, contenttype_id, dependencies in models:
task.status = str(int(10 + i / cnt * 80)) + '%'
task.message = 'Processing data file %s' % ifile
task.save(using=self.database)
i += 1
filetoparse = os.path.join(os.path.abspath(settings.DATABASES[self.database]['FILEUPLOADFOLDER']), ifile)
if ifile.lower().endswith('.xlsx'):
logger.info("%s Started processing data in Excel file: %s" % (datetime.now().replace(microsecond=0), ifile))
returnederrors = self.loadExcelfile(model, filetoparse)
errors[0] += returnederrors[0]
errors[1] += returnederrors[1]
logger.info("%s Finished processing data in file: %s" % (datetime.now().replace(microsecond=0), ifile))
else:
logger.info("%s Started processing data in CSV file: %s" % (datetime.now().replace(microsecond=0), ifile))
returnederrors = self.loadCSVfile(model, filetoparse)
errors[0] += returnederrors[0]
errors[1] += returnederrors[1]
logger.info("%s Finished processing data in CSV file: %s" % (datetime.now().replace(microsecond=0), ifile))
else:
errors[0] += 1
cnt = 0
logger.error("%s Failed, folder does not exist" % datetime.now().replace(microsecond=0))
# Task update
if errors[0] > 0:
task.status = 'Failed'
if not cnt:
task.message = "Destination folder does not exist"
else:
task.message = "Uploaded %s data files with %s errors and %s warnings" % (cnt, errors[0], errors[1])
else:
task.status = 'Done'
task.message = "Uploaded %s data files with %s warnings" % (cnt, errors[1])
task.finished = datetime.now()
except KeyboardInterrupt:
if task:
task.status = 'Cancelled'
task.message = 'Cancelled'
logger.info('%s Cancelled\n' % datetime.now().replace(microsecond=0))
except Exception as e:
logger.error("%s Failed" % datetime.now().replace(microsecond=0))
if task:
task.status = 'Failed'
task.message = '%s' % e
raise e
finally:
setattr(_thread_locals, 'database', None)
if task:
if errors[0] == 0:
task.status = 'Done'
else:
task.status = 'Failed'
task.processid = None
task.finished = datetime.now()
task.save(using=self.database)
logger.info('%s End of importfromfolder\n' % datetime.now().replace(microsecond=0))
def handle(self, **options):
# Pick up the options
now = datetime.now()
self.database = options["database"]
if self.database not in settings.DATABASES:
raise CommandError("No database settings known for '%s'" %
self.database)
if options["user"]:
try:
self.user = (User.objects.all().using(
self.database).get(username=options["user"]))
except Exception:
raise CommandError("User '%s' not found" % options["user"])
else:
self.user = None
timestamp = now.strftime("%Y%m%d%H%M%S")
if self.database == DEFAULT_DB_ALIAS:
logfile = "importworkbook-%s.log" % timestamp
else:
logfile = "importworkbook_%s-%s.log" % (self.database, timestamp)
task = None
try:
setattr(_thread_locals, "database", self.database)
# Initialize the task
if options["task"]:
try:
task = (Task.objects.all().using(
self.database).get(pk=options["task"]))
except Exception:
raise CommandError("Task identifier not found")
if (task.started or task.finished or task.status != "Waiting"
or task.name
not in ("frepple_importworkbook", "importworkbook")):
raise CommandError("Invalid task identifier")
task.status = "0%"
task.started = now
else:
task = Task(
name="importworkbook",
submitted=now,
started=now,
status="0%",
user=self.user,
)
task.arguments = " ".join(options["file"])
task.save(using=self.database)
all_models = [(ct.model_class(), ct.pk)
for ct in ContentType.objects.all()
if ct.model_class()]
try:
with transaction.atomic(using=self.database):
# Find all models in the workbook
if "filename" not in locals():
filename = options["file"]
for file in filename:
wb = load_workbook(filename=file,
read_only=True,
data_only=True)
models = []
for ws_name in wb.sheetnames:
# Find the model
model = None
contenttype_id = None
for m, ct in all_models:
if matchesModelName(ws_name, m):
model = m
contenttype_id = ct
break
if not model or model in EXCLUDE_FROM_BULK_OPERATIONS:
print(
force_text(
_("Ignoring data in worksheet: %s") %
ws_name))
# yield '<div class="alert alert-warning">' + force_text(_("Ignoring data in worksheet: %s") % ws_name) + '</div>'
elif not self.user.has_perm("%s.%s" % (
model._meta.app_label,
get_permission_codename(
"add", model._meta),
)):
# Check permissions
print(
force_text(
_("You don't permissions to add: %s") %
ws_name))
# yield '<div class="alert alert-danger">' + force_text(_("You don't permissions to add: %s") % ws_name) + '</div>'
else:
deps = set([model])
GridReport.dependent_models(model, deps)
models.append(
(ws_name, model, contenttype_id, deps))
# Sort the list of models, based on dependencies between models
models = GridReport.sort_models(models)
# Process all rows in each worksheet
for ws_name, model, contenttype_id, dependencies in models:
print(
force_text(
_("Processing data in worksheet: %s") %
ws_name))
# yield '<strong>' + force_text(_("Processing data in worksheet: %s") % ws_name) + '</strong><br>'
# yield ('<div class="table-responsive">'
# '<table class="table table-condensed" style="white-space: nowrap;"><tbody>')
numerrors = 0
numwarnings = 0
firsterror = True
ws = wb[ws_name]
for error in parseExcelWorksheet(
model,
ws,
user=self.user,
database=self.database,
ping=True,
):
if error[0] == logging.DEBUG:
# Yield some result so we can detect disconnect clients and interrupt the upload
# yield ' '
continue
if firsterror and error[0] in (
logging.ERROR,
logging.WARNING,
):
print("%s %s %s %s %s%s%s" % (
capfirst(_("worksheet")),
capfirst(_("row")),
capfirst(_("field")),
capfirst(_("value")),
capfirst(_("error")),
" / ",
capfirst(_("warning")),
))
# yield '<tr><th class="sr-only">%s</th><th>%s</th><th>%s</th><th>%s</th><th>%s%s%s</th></tr>' % (
# capfirst(_("worksheet")), capfirst(_("row")),
# capfirst(_("field")), capfirst(_("value")),
# capfirst(_("error")), " / ", capfirst(_("warning"))
# )
firsterror = False
if error[0] == logging.ERROR:
print("%s %s %s %s %s: %s" % (
ws_name,
error[1] if error[1] else "",
error[2] if error[2] else "",
error[3] if error[3] else "",
capfirst(_("error")),
error[4],
))
# yield '<tr><td class="sr-only">%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s: %s</td></tr>' % (
# ws_name,
# error[1] if error[1] else '',
# error[2] if error[2] else '',
# error[3] if error[3] else '',
# capfirst(_('error')),
# error[4]
# )
numerrors += 1
elif error[1] == logging.WARNING:
print("%s %s %s %s %s: %s" % (
ws_name,
error[1] if error[1] else "",
error[2] if error[2] else "",
error[3] if error[3] else "",
capfirst(_("warning")),
error[4],
))
# yield '<tr><td class="sr-only">%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s: %s</td></tr>' % (
# ws_name,
# error[1] if error[1] else '',
# error[2] if error[2] else '',
# error[3] if error[3] else '',
# capfirst(_('warning')),
# error[4]
# )
numwarnings += 1
else:
print("%s %s %s %s %s %s" % (
"danger"
if numerrors > 0 else "success",
ws_name,
error[1] if error[1] else "",
error[2] if error[2] else "",
error[3] if error[3] else "",
error[4],
))
# yield '<tr class=%s><td class="sr-only">%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>' % (
# "danger" if numerrors > 0 else 'success',
# ws_name,
# error[1] if error[1] else '',
# error[2] if error[2] else '',
# error[3] if error[3] else '',
# error[4]
# )
# yield '</tbody></table></div>'
print("%s" % _("Done"))
# yield '<div><strong>%s</strong></div>' % _("Done")
except GeneratorExit:
logger.warning("Connection Aborted")
except Exception as e:
if task:
task.status = "Failed"
task.message = "%s" % e
task.finished = datetime.now()
raise e
finally:
setattr(_thread_locals, "database", None)
if task:
task.save(using=self.database)
# Task update
task.status = "Done"
task.finished = datetime.now()
task.processid = None
task.save(using=self.database, update_fields=["status", "finished"])
return _("Done")
def handle(self, **options):
# Pick up the options
now = datetime.now()
self.database = options['database']
if self.database not in settings.DATABASES:
raise CommandError("No database settings known for '%s'" % self.database )
if options['user']:
try:
self.user = User.objects.all().using(self.database).get(username=options['user'])
except:
raise CommandError("User '%s' not found" % options['user'] )
else:
self.user = None
timestamp = now.strftime("%Y%m%d%H%M%S")
if self.database == DEFAULT_DB_ALIAS:
logfile = 'importworkbook-%s.log' % timestamp
else:
logfile = 'importworkbook_%s-%s.log' % (self.database, timestamp)
task = None
try:
setattr(_thread_locals, 'database', self.database)
# Initialize the task
if options['task']:
try:
task = Task.objects.all().using(self.database).get(pk=options['task'])
except:
raise CommandError("Task identifier not found")
if task.started or task.finished or task.status != "Waiting" or task.name != 'frepple_importworkbook':
raise CommandError("Invalid task identifier")
task.status = '0%'
task.started = now
else:
task = Task(name='frepple_importworkbook', submitted=now, started=now, status='0%', user=self.user)
task.arguments = ' '.join(options['file'])
task.save(using=self.database)
all_models = [ (ct.model_class(), ct.pk) for ct in ContentType.objects.all() if ct.model_class() ]
try:
with transaction.atomic(using=self.database):
# Find all models in the workbook
for file in filename:
wb = load_workbook(filename=file, read_only=True, data_only=True)
models = []
for ws_name in wb.get_sheet_names():
# Find the model
model = None
contenttype_id = None
for m, ct in all_models:
# Try with translated model names
if ws_name.lower() in (m._meta.model_name.lower(), m._meta.verbose_name.lower(), m._meta.verbose_name_plural.lower()):
model = m
contenttype_id = ct
break
# Try with English model names
with translation.override('en'):
if ws_name.lower() in (m._meta.model_name.lower(), m._meta.verbose_name.lower(), m._meta.verbose_name_plural.lower()):
model = m
contenttype_id = ct
break
if not model or model in EXCLUDE_FROM_BULK_OPERATIONS:
print(force_text(_("Ignoring data in worksheet: %s") % ws_name))
# yield '<div class="alert alert-warning">' + force_text(_("Ignoring data in worksheet: %s") % ws_name) + '</div>'
elif not self.user.has_perm('%s.%s' % (model._meta.app_label, get_permission_codename('add', model._meta))):
# Check permissions
print(force_text(_("You don't permissions to add: %s") % ws_name))
# yield '<div class="alert alert-danger">' + force_text(_("You don't permissions to add: %s") % ws_name) + '</div>'
else:
deps = set([model])
GridReport.dependent_models(model, deps)
models.append( (ws_name, model, contenttype_id, deps) )
# Sort the list of models, based on dependencies between models
models = GridReport.sort_models(models)
print('197----', models)
# Process all rows in each worksheet
for ws_name, model, contenttype_id, dependencies in models:
print(force_text(_("Processing data in worksheet: %s") % ws_name))
# yield '<strong>' + force_text(_("Processing data in worksheet: %s") % ws_name) + '</strong></br>'
# yield ('<div class="table-responsive">'
# '<table class="table table-condensed" style="white-space: nowrap;"><tbody>')
numerrors = 0
numwarnings = 0
firsterror = True
ws = wb.get_sheet_by_name(name=ws_name)
for error in parseExcelWorksheet(model, ws, user=self.user, database=self.database, ping=True):
if error[0] == DEBUG:
# Yield some result so we can detect disconnect clients and interrupt the upload
# yield ' '
continue
if firsterror and error[0] in (ERROR, WARNING):
print('%s %s %s %s %s%s%s' % (
capfirst(_("worksheet")), capfirst(_("row")),
capfirst(_("field")), capfirst(_("value")),
capfirst(_("error")), " / ", capfirst(_("warning"))
))
# yield '<tr><th class="sr-only">%s</th><th>%s</th><th>%s</th><th>%s</th><th>%s%s%s</th></tr>' % (
# capfirst(_("worksheet")), capfirst(_("row")),
# capfirst(_("field")), capfirst(_("value")),
# capfirst(_("error")), " / ", capfirst(_("warning"))
# )
firsterror = False
if error[0] == ERROR:
print('%s %s %s %s %s: %s' % (
ws_name,
error[1] if error[1] else '',
error[2] if error[2] else '',
error[3] if error[3] else '',
capfirst(_('error')),
error[4]
))
# yield '<tr><td class="sr-only">%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s: %s</td></tr>' % (
# ws_name,
# error[1] if error[1] else '',
# error[2] if error[2] else '',
# error[3] if error[3] else '',
# capfirst(_('error')),
# error[4]
# )
numerrors += 1
elif error[1] == WARNING:
print('%s %s %s %s %s: %s' % (
ws_name,
error[1] if error[1] else '',
error[2] if error[2] else '',
error[3] if error[3] else '',
capfirst(_('warning')),
error[4]
))
# yield '<tr><td class="sr-only">%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s: %s</td></tr>' % (
# ws_name,
# error[1] if error[1] else '',
# error[2] if error[2] else '',
# error[3] if error[3] else '',
# capfirst(_('warning')),
# error[4]
# )
numwarnings += 1
else:
print('%s %s %s %s %s %s' % (
"danger" if numerrors > 0 else 'success',
ws_name,
error[1] if error[1] else '',
error[2] if error[2] else '',
error[3] if error[3] else '',
error[4]
))
# yield '<tr class=%s><td class="sr-only">%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>' % (
# "danger" if numerrors > 0 else 'success',
# ws_name,
# error[1] if error[1] else '',
# error[2] if error[2] else '',
# error[3] if error[3] else '',
# error[4]
# )
# yield '</tbody></table></div>'
print('%s' % _("Done"))
# yield '<div><strong>%s</strong></div>' % _("Done")
except GeneratorExit:
logger.warning('Connection Aborted')
except Exception as e:
if task:
task.status = 'Failed'
task.message = '%s' % e
task.finished = datetime.now()
raise e
finally:
setattr(_thread_locals, 'database', None)
if task:
task.save(using=self.database)
return _("Done")
def handle(self, *args, **options):
# Pick up the options
if 'database' in options:
self.database = options['database'] or DEFAULT_DB_ALIAS
else:
self.database = DEFAULT_DB_ALIAS
if self.database not in settings.DATABASES:
raise CommandError("No database settings known for '%s'" %
self.database)
if 'user' in options and options['user']:
try:
self.user = User.objects.all().using(
self.database).get(username=options['user'])
except:
raise CommandError("User '%s' not found" % options['user'])
else:
self.user = None
now = datetime.now()
task = None
self.logfile = None
try:
# Initialize the task
if 'task' in options and options['task']:
try:
task = Task.objects.all().using(
self.database).get(pk=options['task'])
except:
raise CommandError("Task identifier not found")
if task.started or task.finished or task.status != "Waiting" or task.name != 'import from folder':
raise CommandError("Invalid task identifier")
task.status = '0%'
task.started = now
else:
task = Task(name='import from folder',
submitted=now,
started=now,
status='0%',
user=self.user)
task.arguments = ' '.join(['"%s"' % i for i in args])
task.save(using=self.database)
# Choose the right self.delimiter and language
self.delimiter = get_format('DECIMAL_SEPARATOR',
settings.LANGUAGE_CODE,
True) == ',' and ';' or ','
translation.activate(settings.LANGUAGE_CODE)
# Execute
errors = 0
if os.path.isdir(
settings.DATABASES[self.database]['FILEUPLOADFOLDER']):
# Open the logfile
self.logfile = open(
os.path.join(
settings.DATABASES[self.database]['FILEUPLOADFOLDER'],
'importfromfolder.log'), "a")
print("%s Started import from folder\n" % datetime.now(),
file=self.logfile)
all_models = [(ct.model_class(), ct.pk)
for ct in ContentType.objects.all()
if ct.model_class()]
models = []
for ifile in os.listdir(
settings.DATABASES[self.database]['FILEUPLOADFOLDER']):
if not ifile.endswith('.csv'):
continue
filename0 = ifile.split('.')[0]
model = None
contenttype_id = None
for m, ct in all_models:
if filename0.lower() in (
m._meta.model_name.lower(),
m._meta.verbose_name.lower(),
m._meta.verbose_name_plural.lower()):
model = m
contenttype_id = ct
print("%s Matched a model to file: %s" %
(datetime.now(), ifile),
file=self.logfile)
break
if not model or model in EXCLUDE_FROM_BULK_OPERATIONS:
print("%s Ignoring data in file: %s" %
(datetime.now(), ifile),
file=self.logfile)
elif self.user and not self.user.has_perm(
'%s.%s' %
(model._meta.app_label,
get_permission_codename('add', model._meta))):
# Check permissions
print("%s You don't have permissions to add: %s" %
(datetime.now(), ifile),
file=self.logfile)
else:
deps = set([model])
GridReport.dependent_models(model, deps)
models.append((ifile, model, contenttype_id, deps))
# Sort the list of models, based on dependencies between models
models = GridReport.sort_models(models)
i = 0
cnt = len(models)
for ifile, model, contenttype_id, dependencies in models:
i += 1
print("%s Started processing data in file: %s" %
(datetime.now(), ifile),
file=self.logfile)
filetoparse = os.path.join(
os.path.abspath(settings.DATABASES[self.database]
['FILEUPLOADFOLDER']), ifile)
errors += self.parseCSVloadfromfolder(model, filetoparse)
print("%s Finished processing data in file: %s\n" %
(datetime.now(), ifile),
file=self.logfile)
task.status = str(int(10 + i / cnt * 80)) + '%'
task.save(using=self.database)
else:
errors += 1
cnt = 0
print("%s Failed, folder does not exist" % datetime.now(),
file=self.logfile)
# Task update
if errors:
task.status = 'Failed'
if not cnt:
task.message = "Destination folder does not exist"
else:
task.message = "Uploaded %s data files with %s errors" % (
cnt, errors)
else:
task.status = 'Done'
task.message = "Uploaded %s data files" % cnt
task.finished = datetime.now()
except Exception as e:
print("%s Failed" % datetime.now(), file=self.logfile)
if task:
task.status = 'Failed'
task.message = '%s' % e
raise e
finally:
if task:
if not errors:
task.status = '100%'
else:
task.status = 'Failed'
task.finished = datetime.now()
task.save(using=self.database)
if self.logfile:
print('%s End of import from folder\n' % datetime.now(),
file=self.logfile)
self.logfile.close()
def handle(self, **options):
# Pick up the options
now = datetime.now()
self.database = options['database']
if self.database not in settings.DATABASES:
raise CommandError("No database settings known for '%s'" % self.database )
if options['user']:
try:
self.user = User.objects.all().using(self.database).get(username=options['user'])
except:
raise CommandError("User '%s' not found" % options['user'] )
else:
self.user = None
timestamp = now.strftime("%Y%m%d%H%M%S")
if self.database == DEFAULT_DB_ALIAS:
logfile = 'importfromfolder-%s.log' % timestamp
else:
logfile = 'importfromfolder_%s-%s.log' % (self.database, timestamp)
try:
handler = logging.FileHandler(os.path.join(settings.FREPPLE_LOGDIR, logfile), encoding='utf-8')
# handler.setFormatter(logging.Formatter(settings.LOGGING['formatters']['simple']['format']))
logger.addHandler(handler)
logger.propagate = False
except Exception as e:
print("%s Failed to open logfile %s: %s" % (datetime.now(), logfile, e))
task = None
errors = [0, 0]
returnederrors = [0, 0]
try:
setattr(_thread_locals, 'database', self.database)
# Initialize the task
if options['task']:
try:
task = Task.objects.all().using(self.database).get(pk=options['task'])
except:
raise CommandError("Task identifier not found")
if task.started or task.finished or task.status != "Waiting" or task.name not in ('frepple_importfromfolder', 'importfromfolder'):
raise CommandError("Invalid task identifier")
task.status = '0%'
task.started = now
task.logfile = logfile
else:
task = Task(name='importfromfolder', submitted=now, started=now, status='0%', user=self.user, logfile=logfile)
task.save(using=self.database)
# Choose the right self.delimiter and language
self.delimiter = get_format('DECIMAL_SEPARATOR', settings.LANGUAGE_CODE, True) == ',' and ';' or ','
translation.activate(settings.LANGUAGE_CODE)
# Execute
if 'FILEUPLOADFOLDER' in settings.DATABASES[self.database] \
and os.path.isdir(settings.DATABASES[self.database]['FILEUPLOADFOLDER']):
# Open the logfile
logger.info("%s Started importfromfolder\n" % datetime.now().replace(microsecond=0))
all_models = [ (ct.model_class(), ct.pk) for ct in ContentType.objects.all() if ct.model_class() ]
models = []
for ifile in os.listdir(settings.DATABASES[self.database]['FILEUPLOADFOLDER']):
if not ifile.lower().endswith(('.csv', '.csv.gz', '.xlsx')):
continue
filename0 = ifile.split('.')[0]
model = None
contenttype_id = None
for m, ct in all_models:
if matchesModelName(filename0, m):
model = m
contenttype_id = ct
logger.info("%s Matched a model to file: %s" % (datetime.now().replace(microsecond=0), ifile))
break
if not model or model in EXCLUDE_FROM_BULK_OPERATIONS:
logger.info("%s Ignoring data in file: %s" % (datetime.now().replace(microsecond=0), ifile))
elif self.user and not self.user.has_perm('%s.%s' % (model._meta.app_label, get_permission_codename('add', model._meta))):
# Check permissions
logger.info("%s You don't have permissions to add: %s" % (datetime.now().replace(microsecond=0), ifile))
else:
deps = set([model])
GridReport.dependent_models(model, deps)
models.append( (ifile, model, contenttype_id, deps) )
# Sort the list of models, based on dependencies between models
models = GridReport.sort_models(models)
i = 0
cnt = len(models)
for ifile, model, contenttype_id, dependencies in models:
task.status = str(int(10 + i / cnt * 80)) + '%'
task.message = 'Processing data file %s' % ifile
task.save(using=self.database)
i += 1
filetoparse = os.path.join(os.path.abspath(settings.DATABASES[self.database]['FILEUPLOADFOLDER']), ifile)
if ifile.lower().endswith('.xlsx'):
logger.info("%s Started processing data in Excel file: %s" % (datetime.now().replace(microsecond=0), ifile))
returnederrors = self.loadExcelfile(model, filetoparse)
errors[0] += returnederrors[0]
errors[1] += returnederrors[1]
logger.info("%s Finished processing data in file: %s" % (datetime.now().replace(microsecond=0), ifile))
else:
logger.info("%s Started processing data in CSV file: %s" % (datetime.now().replace(microsecond=0), ifile))
returnederrors = self.loadCSVfile(model, filetoparse)
errors[0] += returnederrors[0]
errors[1] += returnederrors[1]
logger.info("%s Finished processing data in CSV file: %s" % (datetime.now().replace(microsecond=0), ifile))
else:
errors[0] += 1
cnt = 0
logger.error("%s Failed, folder does not exist" % datetime.now().replace(microsecond=0))
# Task update
if errors[0] > 0:
task.status = 'Failed'
if not cnt:
task.message = "Destination folder does not exist"
else:
task.message = "Uploaded %s data files with %s errors and %s warnings" % (cnt, errors[0], errors[1])
else:
task.status = 'Done'
task.message = "Uploaded %s data files with %s warnings" % (cnt, errors[1])
task.finished = datetime.now()
except KeyboardInterrupt:
if task:
task.status = 'Cancelled'
task.message = 'Cancelled'
logger.info('%s Cancelled\n' % datetime.now().replace(microsecond=0))
except Exception as e:
logger.error("%s Failed" % datetime.now().replace(microsecond=0))
if task:
task.status = 'Failed'
task.message = '%s' % e
raise e
finally:
setattr(_thread_locals, 'database', None)
if task:
if errors[0] == 0:
task.status = 'Done'
else:
task.status = 'Failed'
task.finished = datetime.now()
task.save(using=self.database)
logger.info('%s End of importfromfolder\n' % datetime.now().replace(microsecond=0))
def handle(self, **options):
# Pick up the options
now = datetime.now()
self.database = options['database']
if self.database not in settings.DATABASES:
raise CommandError("No database settings known for '%s'" % self.database )
if options['user']:
try:
self.user = User.objects.all().using(self.database).get(username=options['user'])
except:
raise CommandError("User '%s' not found" % options['user'] )
else:
self.user = None
timestamp = now.strftime("%Y%m%d%H%M%S")
if self.database == DEFAULT_DB_ALIAS:
logfile = 'importworkbook-%s.log' % timestamp
else:
logfile = 'importworkbook_%s-%s.log' % (self.database, timestamp)
task = None
try:
setattr(_thread_locals, 'database', self.database)
# Initialize the task
if options['task']:
try:
task = Task.objects.all().using(self.database).get(pk=options['task'])
except:
raise CommandError("Task identifier not found")
if task.started or task.finished or task.status != "Waiting" or task.name not in ('frepple_importworkbook', 'importworkbook'):
raise CommandError("Invalid task identifier")
task.status = '0%'
task.started = now
else:
task = Task(name='importworkbook', submitted=now, started=now, status='0%', user=self.user)
task.arguments = ' '.join(options['file'])
task.save(using=self.database)
all_models = [ (ct.model_class(), ct.pk) for ct in ContentType.objects.all() if ct.model_class() ]
try:
with transaction.atomic(using=self.database):
# Find all models in the workbook
for file in filename:
wb = load_workbook(filename=file, read_only=True, data_only=True)
models = []
for ws_name in wb.sheetnames:
# Find the model
model = None
contenttype_id = None
for m, ct in all_models:
if matchesModelName(ws_name, m):
model = m
contenttype_id = ct
break
if not model or model in EXCLUDE_FROM_BULK_OPERATIONS:
print(force_text(_("Ignoring data in worksheet: %s") % ws_name))
# yield '<div class="alert alert-warning">' + force_text(_("Ignoring data in worksheet: %s") % ws_name) + '</div>'
elif not self.user.has_perm('%s.%s' % (model._meta.app_label, get_permission_codename('add', model._meta))):
# Check permissions
print(force_text(_("You don't permissions to add: %s") % ws_name))
# yield '<div class="alert alert-danger">' + force_text(_("You don't permissions to add: %s") % ws_name) + '</div>'
else:
deps = set([model])
GridReport.dependent_models(model, deps)
models.append( (ws_name, model, contenttype_id, deps) )
# Sort the list of models, based on dependencies between models
models = GridReport.sort_models(models)
print('197----', models)
# Process all rows in each worksheet
for ws_name, model, contenttype_id, dependencies in models:
print(force_text(_("Processing data in worksheet: %s") % ws_name))
# yield '<strong>' + force_text(_("Processing data in worksheet: %s") % ws_name) + '</strong><br>'
# yield ('<div class="table-responsive">'
# '<table class="table table-condensed" style="white-space: nowrap;"><tbody>')
numerrors = 0
numwarnings = 0
firsterror = True
ws = wb[ws_name]
for error in parseExcelWorksheet(model, ws, user=self.user, database=self.database, ping=True):
if error[0] == DEBUG:
# Yield some result so we can detect disconnect clients and interrupt the upload
# yield ' '
continue
if firsterror and error[0] in (ERROR, WARNING):
print('%s %s %s %s %s%s%s' % (
capfirst(_("worksheet")), capfirst(_("row")),
capfirst(_("field")), capfirst(_("value")),
capfirst(_("error")), " / ", capfirst(_("warning"))
))
# yield '<tr><th class="sr-only">%s</th><th>%s</th><th>%s</th><th>%s</th><th>%s%s%s</th></tr>' % (
# capfirst(_("worksheet")), capfirst(_("row")),
# capfirst(_("field")), capfirst(_("value")),
# capfirst(_("error")), " / ", capfirst(_("warning"))
# )
firsterror = False
if error[0] == ERROR:
print('%s %s %s %s %s: %s' % (
ws_name,
error[1] if error[1] else '',
error[2] if error[2] else '',
error[3] if error[3] else '',
capfirst(_('error')),
error[4]
))
# yield '<tr><td class="sr-only">%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s: %s</td></tr>' % (
# ws_name,
# error[1] if error[1] else '',
# error[2] if error[2] else '',
# error[3] if error[3] else '',
# capfirst(_('error')),
# error[4]
# )
numerrors += 1
elif error[1] == WARNING:
print('%s %s %s %s %s: %s' % (
ws_name,
error[1] if error[1] else '',
error[2] if error[2] else '',
error[3] if error[3] else '',
capfirst(_('warning')),
error[4]
))
# yield '<tr><td class="sr-only">%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s: %s</td></tr>' % (
# ws_name,
# error[1] if error[1] else '',
# error[2] if error[2] else '',
# error[3] if error[3] else '',
# capfirst(_('warning')),
# error[4]
# )
numwarnings += 1
else:
print('%s %s %s %s %s %s' % (
"danger" if numerrors > 0 else 'success',
ws_name,
error[1] if error[1] else '',
error[2] if error[2] else '',
error[3] if error[3] else '',
error[4]
))
# yield '<tr class=%s><td class="sr-only">%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>' % (
# "danger" if numerrors > 0 else 'success',
# ws_name,
# error[1] if error[1] else '',
# error[2] if error[2] else '',
# error[3] if error[3] else '',
# error[4]
# )
# yield '</tbody></table></div>'
print('%s' % _("Done"))
# yield '<div><strong>%s</strong></div>' % _("Done")
except GeneratorExit:
logger.warning('Connection Aborted')
except Exception as e:
if task:
task.status = 'Failed'
task.message = '%s' % e
task.finished = datetime.now()
raise e
finally:
setattr(_thread_locals, 'database', None)
if task:
task.save(using=self.database)
return _("Done")
def handle(self, *args, **options):
# Pick up the options
if 'database' in options:
self.database = options['database'] or DEFAULT_DB_ALIAS
else:
self.database = DEFAULT_DB_ALIAS
if self.database not in settings.DATABASES:
raise CommandError("No database settings known for '%s'" % self.database )
if 'user' in options and options['user']:
try:
self.user = User.objects.all().using(self.database).get(username=options['user'])
except:
raise CommandError("User '%s' not found" % options['user'] )
else:
try:
self.user = User.objects.all().using(self.database).filter(is_superuser=True)[0]
except:
self.user = None
now = datetime.now()
task = None
self.logfile = None
try:
# Initialize the task
if 'task' in options and options['task']:
try:
task = Task.objects.all().using(self.database).get(pk=options['task'])
except:
raise CommandError("Task identifier not found")
if task.started or task.finished or task.status != "Waiting" or task.name != 'import from folder':
raise CommandError("Invalid task identifier")
task.status = '0%'
task.started = now
else:
task = Task(name='import from folder', submitted=now, started=now, status='0%', user=self.user)
task.arguments = ' '.join(['"%s"' % i for i in args])
task.save(using=self.database)
# Choose the right self.delimiter and language
self.delimiter = get_format('DECIMAL_SEPARATOR', settings.LANGUAGE_CODE, True) == ',' and ';' or ','
translation.activate(settings.LANGUAGE_CODE)
# Execute
errors = 0
if os.path.isdir(settings.DATABASES[self.database]['FILEUPLOADFOLDER']):
# Open the logfile
self.logfile = open(os.path.join(settings.DATABASES[self.database]['FILEUPLOADFOLDER'], 'importfromfolder.log'), "a")
print("%s Started import from folder\n" % datetime.now(), file=self.logfile)
all_models = [ (ct.model_class(), ct.pk) for ct in ContentType.objects.all() if ct.model_class() ]
models = []
for ifile in os.listdir(settings.DATABASES[self.database]['FILEUPLOADFOLDER']):
if not ifile.endswith('.csv'):
continue
filename0 = ifile.split('.')[0]
model = None
contenttype_id = None
for m, ct in all_models:
if filename0.lower() in (m._meta.model_name.lower(), m._meta.verbose_name.lower(), m._meta.verbose_name_plural.lower()):
model = m
contenttype_id = ct
print("%s Matched a model to file: %s" % (datetime.now(),ifile), file=self.logfile)
break
if not model or model in EXCLUDE_FROM_BULK_OPERATIONS:
print("%s Ignoring data in file: %s" % (datetime.now(),ifile), file=self.logfile)
elif self.user and not self.user.has_perm('%s.%s' % (model._meta.app_label, get_permission_codename('add', model._meta))):
# Check permissions
print("%s You don't have permissions to add: %s" % (datetime.now(),ifile), file=self.logfile)
else:
deps = set([model])
GridReport.dependent_models(model, deps)
models.append( (ifile, model, contenttype_id, deps) )
# Sort the list of models, based on dependencies between models
models = GridReport.sort_models(models)
i=0
cnt = len(models)
for ifile, model, contenttype_id, dependencies in models:
i += 1
print("%s Started processing data in file: %s" % (datetime.now(),ifile), file=self.logfile)
filetoparse=os.path.join(os.path.abspath(settings.DATABASES[self.database]['FILEUPLOADFOLDER']), ifile)
errors += self.parseCSVloadfromfolder(model, filetoparse)
print("%s Finished processing data in file: %s\n" % (datetime.now(),ifile), file=self.logfile)
task.status = str(int(10+i/cnt*80))+'%'
task.save(using=self.database)
else:
errors += 1
cnt = 0
print("%s Failed, folder does not exist" % datetime.now(), file=self.logfile)
# Task update
if errors:
task.status = 'Failed'
if not cnt:
task.message = "Destination folder does not exist"
else:
task.message = "Uploaded %s data files with %s errors" % (cnt, errors)
else:
task.status = 'Done'
task.message = "Uploaded %s data files" % cnt
task.finished = datetime.now()
except Exception as e:
print("%s Failed" % datetime.now(), file=self.logfile)
if task:
task.status = 'Failed'
task.message = '%s' % e
raise e
finally:
if task:
if not errors:
task.status = '100%'
else:
task.status = 'Failed'
task.finished = datetime.now()
task.save(using=self.database)
if self.logfile:
print('%s End of import from folder\n' % datetime.now(), file=self.logfile)
self.logfile.close()
