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 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")
