def render(cls, request=None): limit = int(request.GET.get('limit', cls.limit)) medium = int(request.GET.get('medium', cls.medium)) high = int(request.GET.get('high', cls.high)) result = [ '<svg class="chart" id="resLoad" style="width:100%%; height: %spx;"></svg>' % (limit * 25 + 30), '<table style="display:none">' ] cursor = connections[request.database].cursor() GridReport.getBuckets(request) query = '''select theresource, ( coalesce(sum(out_resourceplan.load),0) + coalesce(sum(out_resourceplan.setup),0) ) * 100.0 / coalesce(sum(out_resourceplan.available)+0.000001,1) as avg_util, coalesce(sum(out_resourceplan.load),0) + coalesce(sum(out_resourceplan.setup),0), coalesce(sum(out_resourceplan.free),0) from out_resourceplan where out_resourceplan.startdate >= '%s' and out_resourceplan.startdate < '%s' group by theresource order by 2 desc ''' % (request.report_startdate, request.report_enddate) cursor.execute(query) for res in cursor.fetchall(): limit -= 1 if limit < 0: break result.append('<tr><td><a href="%s/resource/%s/">%s</a></td><td class="util">%.2f</td></tr>' % ( request.prefix, urlquote(res[0]), res[0], res[1] )) result.append('</table>') result.append('<span id="resload_medium" style="display:none">%s</span>' % medium) result.append('<span id="resload_high" style="display:none">%s</span>' % high) return HttpResponse('\n'.join(result))
def render(cls, request=None): limit = int(request.GET.get('limit', cls.limit)) result = [ '<div id="resLoad" style="width:100%%; height: %spx;"></div>' % (limit * 25 + 30), '<table style="display:none">' ] cursor = connections[request.database].cursor() GridReport.getBuckets(request) query = '''select theresource, ( coalesce(sum(out_resourceplan.load),0) + coalesce(sum(out_resourceplan.setup),0) ) * 100.0 / coalesce(sum(out_resourceplan.available)+0.000001,1) as avg_util, coalesce(sum(out_resourceplan.load),0) + coalesce(sum(out_resourceplan.setup),0), coalesce(sum(out_resourceplan.free),0) from out_resourceplan where out_resourceplan.startdate >= '%s' and out_resourceplan.startdate < '%s' group by theresource order by 2 desc ''' % (request.report_startdate, request.report_enddate) cursor.execute(query) for res in cursor.fetchall(): limit -= 1 if limit < 0: break result.append( '<tr><td class="name"><span class="underline"><a href="%s/resource/%s/">%s</a></span></td><td class="util">%.2f</td></tr>' % (request.prefix, quote(res[0]), res[0], res[1])) result.append('</table>') return HttpResponse('\n'.join(result))
def render(cls, request=None): green = int(request.GET.get('green', cls.green)) yellow = int(request.GET.get('yellow', cls.yellow)) cursor = connections[request.database].cursor() GridReport.getBuckets(request) query = ''' select case when count(*) = 0 then 0 else 100 - sum(late) * 100.0 / count(*) end from ( select demand, max(case when plandate > due then 1 else 0 end) late from out_demand where due < '%s' group by demand ) demands ''' % request.report_enddate cursor.execute(query) val = cursor.fetchone()[0] result = [ '<div style="text-align: center"><span id="otd"></span></div>', '<span id="otd_label" style="display:none">%s</span>' % force_text(_("On time delivery")), '<span id="otd_value" style="display:none">%s</span>' % val, '<span id="otd_green" style="display:none">%s</span>' % green, '<span id="otd_yellow" style="display:none">%s</span>' % yellow ] return HttpResponse('\n'.join(result))
def scheduletasks(request): if not request.is_ajax() or request.method not in ("POST", "DELETE"): return HttpResponseNotAllowed( "Only post and delete ajax requests are allowed") try: data = json.loads(request.body.decode(request.encoding)) oldname = data.get("oldname", None) name = data.get("name", None) if not name and not oldname: return HttpResponse("Missing name attribute", status=400) elif request.method == "POST": if not request.user.has_perm("execute.add_scheduledtask"): return HttpResponse("Couldn't add or update scheduled task", status=401) obj, created = ScheduledTask.objects.using( request.database).get_or_create( name=oldname if oldname else name) if created: obj.user = request.user elif (not request.user.is_superuser and obj.user.username != request.user.username): return HttpResponse( "This task can only be updated by superusers and %s" % obj.user.username, status=401, ) fld = data.get("email_failure", None) if fld: obj.email_failure = fld fld = data.get("email_success", None) if fld: obj.email_success = fld fld = data.get("data", None) if isinstance(fld, dict): obj.data = fld if oldname and name and oldname != name: # Rename the task obj.name = name ScheduledTask.objects.using( request.database).filter(name=oldname).delete() obj.adjustForTimezone(-GridReport.getTimezoneOffset(request)) obj.save(using=request.database) call_command("scheduletasks", database=request.database) obj.adjustForTimezone(GridReport.getTimezoneOffset(request)) return HttpResponse(content=obj.next_run.strftime( "%Y-%m-%d %H:%M:%S") if obj.next_run else "") elif request.method == "DELETE": if not request.user.has_perm("execute.delete_scheduledtask"): return HttpResponse("Couldn't delete scheduled task", status=401) elif (ScheduledTask.objects.using( request.database).filter(name=name).delete()[0]): return HttpResponse(content="OK") else: return HttpResponse("Couldn't delete scheduled task", status=400) except Exception as e: logger.error("Error updating scheduled task: %s" % e) return HttpResponseServerError("Error updating scheduled task")
def getHTML(request): commands = [] for commandname, appname in get_commands().items(): if commandname != "scheduletasks": try: cmd = getattr( import_module("%s.management.commands.%s" % (appname, commandname)), "Command", ) if getattr(cmd, "index", -1) >= 0 and getattr( cmd, "getHTML", None): commands.append((cmd.index, commandname)) except Exception: pass commands = [i[1] for i in sorted(commands)] schedules = [ s.adjustForTimezone(GridReport.getTimezoneOffset(request)) for s in ScheduledTask.objects.all().using( request.database).order_by("name") ] schedules.append(ScheduledTask()) # Add an empty template return render_to_string( "commands/scheduletasks.html", { "schedules": schedules, "commands": commands }, request=request, )
def getHTML(request): if ( "FILEUPLOADFOLDER" not in settings.DATABASES[request.database] or not not settings.EMAIL_HOST or not request.user.is_superuser ): return None # Function to convert from bytes to human readabl format def sizeof_fmt(num): for unit in ["", "Ki", "Mi", "Gi", "Ti", "Pi", "Ei", "Zi"]: if abs(num) < 1024.0: return "%3.1f%sB" % (num, unit) num /= 1024.0 return "%.1f%sB" % (num, "Yi") # List available data files filesexported = [] all_reports = [] if "FILEUPLOADFOLDER" in settings.DATABASES[request.database]: exportfolder = os.path.join( settings.DATABASES[request.database]["FILEUPLOADFOLDER"], "export" ) if os.path.isdir(exportfolder): tzoffset = GridReport.getTimezoneOffset(request) for file in os.listdir(exportfolder): if file.endswith((".xlsx", ".xlsx.gz", ".csv", ".csv.gz", ".log")): all_reports.append(file) filesexported.append( [ file, strftime( "%Y-%m-%d %H:%M:%S", localtime( os.stat( os.path.join(exportfolder, file) ).st_mtime + tzoffset.total_seconds() ), ), sizeof_fmt( os.stat(os.path.join(exportfolder, file)).st_size ), file.replace(".", "\\\\."), ] ) return render_to_string( "commands/emailreport.html", { "filesexported": filesexported, "user_email": request.user.email, "all_reports": ",".join(map(str, all_reports)), "initially_disabled": "" if len(all_reports) > 0 else "disabled", }, request=request, )
def getHTML(request): if ( "FILEUPLOADFOLDER" not in settings.DATABASES[request.database] or not request.user.is_superuser ): return None # Function to convert from bytes to human readable format def sizeof_fmt(num): for unit in ["", "Ki", "Mi", "Gi", "Ti", "Pi", "Ei", "Zi"]: if abs(num) < 1024.0: return "%3.0f %sB" % (num, unit) num /= 1024.0 return "%.0f %sB" % (num, "Yi") filestoupload = [] if "FILEUPLOADFOLDER" in settings.DATABASES[request.database]: uploadfolder = settings.DATABASES[request.database]["FILEUPLOADFOLDER"] if os.path.isdir(uploadfolder): tzoffset = GridReport.getTimezoneOffset(request) for file in sorted(os.listdir(uploadfolder)): if file.endswith( ( ".csv", ".csv.gz", ".xlsx", ".xlsm", ".cpy", ".sql", ".cpy.gz", ".sql.gz", ) ): filestoupload.append( [ file, strftime( "%Y-%m-%d %H:%M:%S", localtime( os.stat( os.path.join(uploadfolder, file) ).st_mtime + tzoffset.total_seconds() ), ), sizeof_fmt( os.stat(os.path.join(uploadfolder, file)).st_size ), ] ) return render_to_string( "commands/importfromfolder.html", {"filestoupload": filestoupload}, request=request, )
def getHTML(request): if ("FILEUPLOADFOLDER" in settings.DATABASES[request.database] and request.user.is_superuser): # Function to convert from bytes to human readabl format def sizeof_fmt(num): for unit in ["", "Ki", "Mi", "Gi", "Ti", "Pi", "Ei", "Zi"]: if abs(num) < 1024.0: return "%3.1f%sB" % (num, unit) num /= 1024.0 return "%.1f%sB" % (num, "Yi") # List available data files filesexported = [] all_reports = [] if "FILEUPLOADFOLDER" in settings.DATABASES[request.database]: exportfolder = os.path.join( settings.DATABASES[request.database]["FILEUPLOADFOLDER"], "export") if os.path.isdir(exportfolder): tzoffset = GridReport.getTimezoneOffset(request) for file in os.listdir(exportfolder): if file.endswith((".csv", ".csv.gz", ".log")): all_reports.append(file) filesexported.append([ file, strftime( "%Y-%m-%d %H:%M:%S", localtime( os.stat( os.path.join(exportfolder, file)).st_mtime + tzoffset.total_seconds()), ), sizeof_fmt( os.stat(os.path.join(exportfolder, file)).st_size), file.split(".")[0], ]) context = RequestContext( request, { "filesexported": filesexported, "user_email": request.user.email, "all_reports": ",".join(map(str, all_reports)), "initially_disabled": "" if len(all_reports) > 0 else "disabled", }, ) template = Template(""" {% load i18n %} <form role="form" method="post" action="{{request.prefix}}/execute/launch/emailreport/">{% csrf_token %} <table> <tr> <td style="vertical-align:top; padding-left: 15px"> <button type="submit" class="btn btn-primary" id="emailreport" value="email" {{initially_disabled}}>{% trans "email"|capfirst %}</button> </td> <td colspan='5' style="padding-left: 15px;"> <p>{% trans "Emails the selected reports to a comma separated list of recipients. Files are zipped and attached to email." %}</p> </td> </tr> <tr> <td></td> <td><div> <input type="checkbox" id="allcheckboxes" checked> <strong>{% trans 'file name'|capfirst %}</strong></td> </div> <td><strong>{% trans 'size'|capfirst %}</strong></td> <td><strong>{% trans 'last modified'|capfirst %}</strong></td> <td></td> </tr></form> {% for j in filesexported %} <tr data-file="{{j.0}}"> <td></td> <td> <input type="checkbox" id="{{j.3}}" checked> {{j.0}} </td> <td>{{j.2}}</td> <td>{{j.1}}</td> </tr> {% endfor %} <tr> <td style="padding-left:15px; padding-top:10px"><strong>{% trans 'emails'|capfirst %}:</strong> </td> <td style="padding-top:10px" colspan="3"> <input type="email" class="form-control" id="emails" name="recipient" multiple value="{{user_email}}"> </td> </tr> </table> <input type="hidden" name="report" id="report" value="{{all_reports}}"> </form> <script> function validateButton() { var reports = ""; var first = true; {% for j in filesexported %} if ($('#{{j.3}}').is(':checked')) { if (first) { reports = reports + "{{j.0}}"; first = false } else { reports = reports + ",{{j.0}}"; } } {% endfor %} $("#report").val(reports); var oneChecked = false; {% for j in filesexported %} oneChecked = oneChecked || $('#{{j.3}}').is(':checked'); {% endfor %} var testEmail = /^([\w+-.%]+@[\w-.]+\.[A-Za-z]{2,},?)+$/; emails_ok = $("#emails").val() != '' && testEmail.test($("#emails").val()); if (!emails_ok) { $("#emails").attr('data-original-title', '{% trans 'please correct invalid email addresses'|capfirst %}'); $('#emails').tooltip('show'); } else { $("#emails").tooltip('hide').attr('data-original-title', '{% trans 'please enter email addresses'|capfirst %}'); } $('#emailreport').prop('disabled', !(oneChecked && emails_ok)); } $("#emails").on('input', function () { validateButton(); }); $("#allcheckboxes").on("click", function(event) { var isChecked = $('#allcheckboxes').is(':checked'); {% for j in filesexported %} $("#{{j.3}}").prop("checked", isChecked); {% endfor %} validateButton(); }); {% for j in filesexported %} $("#{{j.3}}").on("click", function(event) { var allChecked = true; {% for j in filesexported %} allChecked = allChecked && $('#{{j.3}}').is(':checked'); {% endfor %} $("#allcheckboxes").prop("checked", allChecked); validateButton(); }); {% endfor %} </script> """) return template.render(context) # A list of translation strings from the above translated = ( _("email"), _("Emails the selected reports to a comma separated list of recipients. Files are zipped and attached to email." ), _("file name"), _("size"), _("please correct invalid email addresses"), _("please enter email addresses"), ) else: return None
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, *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 != 'load from folder': raise CommandError("Invalid task identifier") task.status = '0%' task.started = now else: task = Task(name='load 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 if os.path.isdir(settings.DATABASES[self.database]['FILEUPLOADFOLDER']): # Open the logfile self.logfile = open(os.path.join(settings.DATABASES[self.database]['FILEUPLOADFOLDER'], 'loadfromfolder.log'), "a") print("%s Started upload 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 cnt = len(models) ok = False while not ok: ok = True for i in range(cnt): for j in range(i + 1, cnt): if models[i][1] != models[j][1] and models[i][1] in models[j][3]: # A subsequent model i depends on model i. The list ordering is # thus not ok yet. We move this element to the end of the list. models.append(models.pop(i)) ok = False task.status = '10%' task.save(using=self.database) i=0 errors = 0 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) # Task update if errors: task.status = 'Failed' task.message = "Uploaded %s data files with %s errors" % (cnt, errors) else: task.status = 'Done' task.message = "Uploaded %s data file" % 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 task.finished = datetime.now() raise e finally: if task: task.status = '100%' task.save(using=self.database) if self.logfile: print('%s End of upload from folder\n' % datetime.now(), file=self.logfile) self.logfile.close()
def getHTML(request): database = request.database ftp_protocol = (settings.FTP_PROTOCOL[database] if isinstance(settings.FTP_PROTOCOL, dict) and database in settings.FTP_PROTOCOL else (None if isinstance(settings.FTP_PROTOCOL, dict) else settings.FTP_PROTOCOL)) ftp_host = (settings.FTP_HOST[database] if isinstance(settings.FTP_HOST, dict) and database in settings.FTP_HOST else (None if isinstance( settings.FTP_HOST, dict) else settings.FTP_HOST)) ftp_port = (settings.FTP_PORT[database] if isinstance(settings.FTP_PORT, dict) and database in settings.FTP_PORT else (None if isinstance( settings.FTP_PORT, dict) else settings.FTP_PORT)) ftp_user = (settings.FTP_USER[database] if isinstance(settings.FTP_USER, dict) and database in settings.FTP_USER else (None if isinstance( settings.FTP_USER, dict) else settings.FTP_USER)) ftp_password = (settings.FTP_PASSWORD[database] if isinstance(settings.FTP_PASSWORD, dict) and database in settings.FTP_PASSWORD else (None if isinstance(settings.FTP_PASSWORD, dict) else settings.FTP_PASSWORD)) ftp_folder = (settings.FTP_FOLDER[database] if isinstance(settings.FTP_FOLDER, dict) and database in settings.FTP_FOLDER else (None if isinstance(settings.FTP_FOLDER, dict) else settings.FTP_FOLDER)) if ("FILEUPLOADFOLDER" not in settings.DATABASES[request.database] or not ftp_host or not ftp_user or not ftp_password or not ftp_port or not ftp_protocol or not ftp_folder or not request.user.is_superuser): return None # Function to convert from bytes to human readabl format def sizeof_fmt(num): for unit in ["", "Ki", "Mi", "Gi", "Ti", "Pi", "Ei", "Zi"]: if abs(num) < 1024.0: return "%3.1f%sB" % (num, unit) num /= 1024.0 return "%.1f%sB" % (num, "Yi") # List available data files filesexported = [] all_reports = [] if "FILEUPLOADFOLDER" in settings.DATABASES[request.database]: exportfolder = os.path.join( settings.DATABASES[request.database]["FILEUPLOADFOLDER"], "export") if os.path.isdir(exportfolder): tzoffset = GridReport.getTimezoneOffset(request) for file in os.listdir(exportfolder): if file.endswith( (".xlsx", ".xlsx.gz", ".csv", ".csv.gz", ".log")): all_reports.append(file) filesexported.append([ file, strftime( "%Y-%m-%d %H:%M:%S", localtime( os.stat(os.path.join(exportfolder, file)).st_mtime + tzoffset.total_seconds()), ), sizeof_fmt( os.stat(os.path.join(exportfolder, file)).st_size), file.replace(".", "\\\\."), ]) return render_to_string( "commands/uploadreport.html", { "filesexported": filesexported, "all_reports": ",".join(map(str, all_reports)), "initially_disabled": "" if len(all_reports) > 0 else "disabled", }, request=request, )
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 getHTML(request): if ("FILEUPLOADFOLDER" in settings.DATABASES[request.database] and request.user.is_superuser): # Function to convert from bytes to human readabl format def sizeof_fmt(num): for unit in ["", "Ki", "Mi", "Gi", "Ti", "Pi", "Ei", "Zi"]: if abs(num) < 1024.0: return "%3.1f%sB" % (num, unit) num /= 1024.0 return "%.1f%sB" % (num, "Yi") # List available data files filesexported = [] if "FILEUPLOADFOLDER" in settings.DATABASES[request.database]: exportfolder = os.path.join( settings.DATABASES[request.database]["FILEUPLOADFOLDER"], "export") if os.path.isdir(exportfolder): tzoffset = GridReport.getTimezoneOffset(request) for file in os.listdir(exportfolder): if file.endswith((".csv", ".csv.gz", ".log")): filesexported.append([ file, strftime( "%Y-%m-%d %H:%M:%S", localtime( os.stat( os.path.join(exportfolder, file)).st_mtime + tzoffset.total_seconds()), ), sizeof_fmt( os.stat(os.path.join(exportfolder, file)).st_size), ]) context = RequestContext(request, {"filesexported": filesexported}) template = Template(""" {% load i18n %} <form role="form" method="post" action="{{request.prefix}}/execute/launch/exporttofolder/">{% csrf_token %} <table> <tr> <td style="vertical-align:top; padding-left: 15px"> <button type="submit" class="btn btn-primary" id="exporttofolder" value="{% trans "export"|capfirst %}">{% trans "export"|capfirst %}</button> </td> <td colspan='5' style="padding-left: 15px;"> <p>{% trans "Exports the plan (purchase orders, distribution orders and manufacturing orders) as a set of CSV files." %}</p> </td> </tr> <tr> <td></td> <td><strong>{% trans 'file name'|capfirst %}</strong></td> <td><strong>{% trans 'size'|capfirst %}</strong></td> <td><strong>{% trans 'changed'|capfirst %}</strong></td> <td></td> <td> <div class="btn btn-xs btn-danger deletefile" style="margin-bottom: 5px;" id="allexportfilesdelete" data-toggle="tooltip" data-placement="top" data-original-title="{% trans 'Delete all files' %}" onClick="deleteExportFile(1, {{filesexported}})"> <span class="fa fa-close"></span> </div> </td> </tr></form> {% for j in filesexported %} <tr data-file="{{j.0}}"> <td></td> <td>{{j.0}}</td> <td>{{j.2}}</td> <td>{{j.1}}</td> <td> <div class="btn btn-xs btn-primary downloadfile" style="margin-bottom: 5px;" id="filedownload" data-toggle="tooltip" data-placement="top" data-original-title="Download file" onClick="downloadExportFile(1, '{{j.0}}')"> <span class="fa fa-arrow-down"></span> </div> </td> <td> <div class="btn btn-xs btn-danger deletefile" style="margin-bottom: 5px;" id="filedelete" data-toggle="tooltip" data-placement="top" data-original-title="Delete file from folder" onClick="deleteExportFile(1, '{{j.0}}')"> <span class="fa fa-close"></span> </div> </td> </tr> {% endfor %} </table> </form> <script> function deleteExportFile(folder, filename) { $.jgrid.hideModal("#searchmodfbox_grid"); var dialogcontent; if (typeof filename === 'object') { dialogcontent = "{% trans 'You are about to delete all files' %}"; var oldfilename = filename; filename = 'AllFiles'; } else { dialogcontent = interpolate("{% trans 'You are about to delete file %s' %}", [filename]); } $("#popup").html('<div class="modal-dialog">'+ '<div class="modal-content">'+ '<div class="modal-header">'+ '<h4 class="modal-title">{% trans 'Delete file' %}</h4>'+ '</div>'+ '<div class="modal-body"><p>'+ dialogcontent + '</p></div>'+ '<div class="modal-footer">'+ '<input type="submit" id="confirmbutton" role="button" class="btn btn-danger pull-left" value="{% trans 'Confirm' %}">'+ '<input type="submit" id="cancelbutton" role="button" class="btn btn-primary pull-right" data-dismiss="modal" value="{% trans 'Cancel' %}">'+ '</div>'+ '</div>'+ '</div>' ) .modal('show'); $('#confirmbutton').on('click', function() { $.ajax({ url: "{{request.prefix}}/execute/deletefromfolder/" + folder + "/" + filename + "/", type: ("delete").toUpperCase(), success: function () { if (filename === 'AllFiles') { $("#popup .modal-body>p").text("{% trans 'All data files were deleted' %}"); } else { $("#popup .modal-body>p").text(interpolate("{% trans 'File %s was deleted' %}", [filename])); } $('#confirmbutton').hide(); $('#cancelbutton').attr('value', "{% trans 'Close' %}"); $('#cancelbutton').one('click', function() {location.reload();}); }, error: function (result, stat, errorThrown) { var filelist = result.responseText.split(' / '); var elem = $("#popup .modal-body>p"); if (filelist.length === 1) { elem.text(interpolate("{% trans 'File %s was not deleted' %}", [filename])); } else { for (var i = 1; i < filelist.length; i++) { if (i === 1) { elem.text(interpolate("{% trans 'File %s was not deleted' }", [filelist[i]])); } else { elem.parent().append('<p>'+interpolate("{% trans "File %s was not deleted" %}", [filelist[i]])+'</p>'); } } } $("#popup .modal-body>p").addClass('alert alert-danger'); $('#confirmbutton').hide(); $('#cancelbutton').attr('value', "{% trans 'Close' %}"); $('#cancelbutton').one('click', function() {$("#popup").hide();}); } }) }) } function downloadExportFile(folder, filename) { $.jgrid.hideModal("#searchmodfbox_grid"); window.open("{{request.prefix}}/execute/downloadfromfolder/" + folder + "/" + filename + '/', '_blank'); } </script> """) return template.render(context) # A list of translation strings from the above translated = ( _("export"), _("file name"), _("size"), _("changed"), _("Delete all files"), _("Exports the plan (purchase orders, distribution orders and manufacturing orders) as a set of CSV files." ), _("File %s was not deleted"), _("Close"), _("File %s was deleted"), _("All data files were deleted"), _("You are about to delete all files"), _("You are about to delete file %s"), _("Delete file"), _("Confirm"), _("Cancel"), ) else: return None
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 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 != 'load from folder': raise CommandError("Invalid task identifier") task.status = '0%' task.started = now else: task = Task(name='load 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 filestoupload = list() if os.path.isdir(settings.DATABASES[self.database]['FILEUPLOADFOLDER']): thisfolder = settings.DATABASES[self.database]['FILEUPLOADFOLDER'] for fileindir in os.listdir(settings.DATABASES[self.database]['FILEUPLOADFOLDER']): if fileindir.endswith('.csv'): filestoupload.append(fileindir) #filestoupload.append([file,strftime("%Y-%m-%d %H:%M:%S",localtime(os.stat(os.path.join(thisfolder, file)).st_mtime)),sizeof_fmt(os.stat(os.path.join(thisfolder, file)).st_size, 'B')]) all_models = [ (ct.model_class(), ct.pk) for ct in ContentType.objects.all() if ct.model_class() ] models = [] for ifile in filestoupload: 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 break if not model or model in EXCLUDE_FROM_BULK_OPERATIONS: print("Ignoring data in file: %s" % ifile) elif not self.user==None and not self.user.has_perm('%s.%s' % (model._meta.app_label, get_permission_codename('add', model._meta))): # Check permissions print("You don't permissions to add: %s" % 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 cnt = len(models) ok = False while not ok: ok = True for i in range(cnt): for j in range(i + 1, cnt): if models[i][1] in models[j][3]: # A subsequent model i depends on model i. The list ordering is # thus not ok yet. We move this element to the end of the list. models.append(models.pop(i)) ok = False for ifile, model, contenttype_id, dependencies in models: print("Processing data in file: %s" % ifile) rownum = 0 has_pk_field = False headers = [] uploadform = None changed = 0 added = 0 numerrors = 0 #Will the permissions have to be checked table by table? permname = get_permission_codename('add', model._meta) if not self.user == None and not self.user.has_perm('%s.%s' % (model._meta.app_label, permname)): print('Permission denied') return filetoparse=os.path.join(os.path.abspath(thisfolder), ifile) self.parseCSVloadfromfolder(model, filetoparse) # Task update task.status = 'Done' task.finished = datetime.now() except Exception as e: if task: task.status = 'Failed' task.message = '%s' % e task.finished = datetime.now() raise e finally: if task: task.save(using=self.database)
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 getHTML(request): if ("FILEUPLOADFOLDER" in settings.DATABASES[request.database] and request.user.is_superuser): # Function to convert from bytes to human readabl format def sizeof_fmt(num): for unit in ["", "Ki", "Mi", "Gi", "Ti", "Pi", "Ei", "Zi"]: if abs(num) < 1024.0: return "%3.1f%sB" % (num, unit) num /= 1024.0 return "%.1f%sB" % (num, "Yi") filestoupload = [] if "FILEUPLOADFOLDER" in settings.DATABASES[request.database]: uploadfolder = settings.DATABASES[ request.database]["FILEUPLOADFOLDER"] if os.path.isdir(uploadfolder): tzoffset = GridReport.getTimezoneOffset(request) for file in os.listdir(uploadfolder): if file.endswith(( ".csv", ".csv.gz", ".xlsx", ".cpy", ".sql", ".cpy.gz", ".sql.gz", )): filestoupload.append([ file, strftime( "%Y-%m-%d %H:%M:%S", localtime( os.stat( os.path.join(uploadfolder, file)).st_mtime + tzoffset.total_seconds()), ), sizeof_fmt( os.stat(os.path.join(uploadfolder, file)).st_size), ]) context = RequestContext(request, {"filestoupload": filestoupload}) template = Template(""" {% load i18n %} <form role="form" method="post" action="{{request.prefix}}/execute/launch/importfromfolder/">{% csrf_token %} <table> <tr> <td style="vertical-align:top; padding-left: 15px"> <button type="submit" class="btn btn-primary" id="importfromfolder" value="{% trans "import"|capfirst %}">{% trans "import"|capfirst %}</button> </td> <td colspan='5' style="padding-left: 15px;"> <p>{% trans "Import CSV or Excel files from the data folder. The file names must match the names of data objects and the first line in the file must contain the field names." %}</p> </td> </tr> <tr> <td></td> <td><strong>{% trans 'file name'|capfirst %}</strong></td> <td><strong>{% trans 'size'|capfirst %}</strong></td> <td><strong>{% trans 'changed'|capfirst %}</strong></td> <td> <span class="btn btn-xs btn-primary" id="filescopy" style="margin-bottom: 5px;" data-toggle="tooltip" data-placement="top" data-original-title="{% trans 'Upload data files' %}" onclick="import_show('{% trans 'Copy files to folder' %}',null,true,uploadfilesajax)"> <span class="fa fa-arrow-up"></span> </span> </td> <td> <div class="btn btn-xs btn-danger deletefile" style="margin-bottom: 5px;" id="allimportfilesdelete" data-toggle="tooltip" data-placement="top" data-original-title="{% trans 'Delete all files' %}" onClick="deleteImportFile(0, {{filestoupload}})"> <span class="fa fa-close"></span> </div> </td> </tr></form> {% for j in filestoupload %} <tr data-file="{{j.0}}"> <td></td> <td>{{j.0}}</td> <td>{{j.2}}</td> <td>{{j.1}}</td> <td> <div class="btn btn-xs btn-primary downloadfile" style="margin-bottom: 5px;" id="filedownload" data-toggle="tooltip" data-placement="top" data-original-title="{% trans "Download file" %}" onClick="downloadImportFile(0, '{{j.0}}')"> <span class="fa fa-arrow-down"></span> </div> </td> <td> <div class="btn btn-xs btn-danger deletefile" style="margin-bottom: 5px;" id="filedelete" data-toggle="tooltip" data-placement="top" data-original-title="{% trans "Delete file" %}" onClick="deleteImportFile(0, '{{j.0}}')"> <span class="fa fa-close"></span> </div> </td> </tr> {% endfor %} </table> </form> <script> var xhr = {abort: function () {}}; var uploadfilesajax = { url: '{{request.prefix}}/execute/uploadtofolder/0/', success: function (data) { var el = $('#uploadResponse'); el.empty(); $("#animatedcog").css('visibility','hidden'); var lines = data.split('\\n'); for (var i = 0; i < lines.length; i++) { el.append(lines[i] + '<br>'); } if (el.attr('data-scrolled')!== "true") { el.attr('data-scrolled', el[0].scrollHeight - el.height()); el.scrollTop(el[0].scrollHeight - el.height()); } if (document.queryCommandSupported('copy')) $('#copytoclipboard').show(); $('#canceluploadbutton').hide(); $('#cancelimportbutton').hide(); $('#cancelbutton').one('click', function() {location.reload();}); }, error: function (result, stat, errorThrown) { var el = $('#uploadResponse'); el.empty(); $("#animatedcog").css('visibility','hidden'); var lines = result.responseText.split('\\n'); for (var i = 0; i < lines.length; i++) { el.append(lines[i] + '<br>'); } if (el.attr('data-scrolled')!== "true") { el.attr('data-scrolled', el[0].scrollHeight - el.height()); el.scrollTop(el[0].scrollHeight - el.height()); } if (document.queryCommandSupported('copy')) $('#copytoclipboard').show(); $('#canceluploadbutton').hide(); $('#cancelimportbutton').hide(); } }; function deleteImportFile(folder, filename) { $.jgrid.hideModal("#searchmodfbox_grid"); var dialogcontent; if (typeof filename === 'object') { dialogcontent = "{% trans 'You are about to delete all files' %}"; var oldfilename = filename; filename = 'AllFiles'; } else { dialogcontent = interpolate("{% trans 'You are about to delete file %s' %}", [filename]); } $("#popup").html('<div class="modal-dialog">'+ '<div class="modal-content">'+ '<div class="modal-header">'+ '<h4 class="modal-title">{% trans 'Delete file' %}</h4>'+ '</div>'+ '<div class="modal-body"><p>'+ dialogcontent + '</p></div>'+ '<div class="modal-footer">'+ '<input type="submit" id="confirmbutton" role="button" class="btn btn-danger pull-left" value="{% trans 'Confirm' %}">'+ '<input type="submit" id="cancelbutton" role="button" class="btn btn-primary pull-right" data-dismiss="modal" value="{% trans 'Cancel' %}">'+ '</div>'+ '</div>'+ '</div>' ) .modal('show'); $('#confirmbutton').on('click', function() { $.ajax({ url: "{{request.prefix}}/execute/deletefromfolder/" + folder + "/" + filename + "/", type: ("delete").toUpperCase(), success: function () { if (filename === 'AllFiles') { $("#popup .modal-body>p").text("{% trans 'All data files were deleted' %}"); } else { $("#popup .modal-body>p").text(interpolate("{% trans 'File %s was deleted' %}", [filename])); } $('#confirmbutton').hide(); $('#cancelbutton').attr('value', "{% trans 'Close' %}"); $('#cancelbutton').one('click', function() {$("#popup").hide();}); location.reload(); }, error: function (result, stat, errorThrown) { var filelist = result.responseText.split(' / '); var elem = $("#popup .modal-body>p"); if (filelist.length === 1) { elem.text(interpolate("{% trans 'File %s was not deleted' %}", [filename])); } else { for (var i = 1; i < filelist.length; i++) { if (i === 1) { elem.text(interpolate("{% trans 'File %s was not deleted' %}", [filelist[i]])); } else { elem.parent().append('<p>'+interpolate("{% trans "File %s was not deleted" %}", [filelist[i]])+'</p>'); } } } $("#popup .modal-body>p").addClass('alert alert-danger'); $('#confirmbutton').hide(); $('#cancelbutton').attr('value', "{% trans 'Close' %}"); $('#cancelbutton').one('click', function() {$("#popup").hide();}); } }) }) } function downloadImportFile(folder, filename) { $.jgrid.hideModal("#searchmodfbox_grid"); window.open("{{request.prefix}}/execute/downloadfromfolder/" + folder + "/" + filename + '/', '_blank'); } </script> """) return template.render(context) # A list of translation strings from the above translated = ( _("export"), _("file name"), _("size"), _("changed"), _("Delete all files"), _("Delete file"), _("Upload data files"), _("Download file"), _("Import CSV or Excel files from the data folder. The file names must match the names of data objects and the first line in the file must contain the field names." ), _("File %s was not deleted"), _("Close"), _("File %s was deleted"), _("All data files were deleted"), _("You are about to delete all files"), _("You are about to delete file %s"), _("Delete file"), _("Confirm"), _("Cancel"), ) else: return None
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")