def export_prices_view(request):
start = time.time() # profiling
sheets = {}
category_list = [i for i in Category.objects.all() if Product.objects.filter(category__name=i.name).exists()]
for category in category_list:
field_names = ['name', 'code']
arr_title = [[str(i) for i in field_names] + ['price']]
cat_objects = Product.objects.filter(category__name=category.name).order_by('category', 'id')
arr = []
for i in cat_objects:
arr_add = []
for field in field_names:
arr_add.append(str(getattr(i, field, '')))
try:
arr_add.append(str(StockRecord.objects.get(product=i.id).price))
except Exception:
arr_add.append('0')
arr.append(arr_add)
sheet = pyexcel.Sheet(arr_title + arr)
if "/" not in category.name and cat_objects:
sheets.update({str(category.name): sheet}) # {"Category":sheet1, "Product":sheet2}
book = pyexcel.Book(sheets=sheets)
file_name = "Prices_{}".format(str(datetime.date.today()))
return excel.make_response(book, file_type='xlsx', file_name=file_name, status=200)
def exchange(request, file_type):
form = UploadFileForm(request.POST, request.FILES)
if form.is_valid():
filehandle = request.FILES['file']
return excel.make_response(filehandle.get_sheet(), file_type)
else:
return HttpResponseBadRequest()
def upload(request):
if request.method == "POST":
form = UploadFileForm(request.POST, request.FILES)
if form.is_valid():
filehandle = request.FILES['file']
return excel.make_response(filehandle.get_sheet(), file_type="xls", dest_file_name='raba')
else:
form = UploadFileForm()
return render(request, 'upload_form.html', {'form': form})
def upload(request):
if request.method == "POST":
form = UploadFileForm(request.POST, request.FILES)
if form.is_valid():
filehandle = request.FILES['file']
# Convert Excel sheet from get_sheet() to a csv and download as download.csv
return excel.make_response(filehandle.get_sheet(), "csv", file_name="download")
else:
form = UploadFileForm()
return render_to_reponse("upload_form.html", {"form": form}, context_instance=RequestContext(request))
def export_products_view(request):
start = time.time() # profiling
sheets = {}
category_list = [i for i in Category.objects.all() if Product.objects.filter(category__name=i.name).exists()]
for category in category_list:
field_names = ['name', 'code', 'first_text'] # 'first_text'
parameter_names = list(set([i.parameter.name for i in NewCategoryParameter.objects.all()
if i.category.name == category.name]))
arr_title = [[str(i) for i in field_names] + ['price'] + ['provider'] + ['num_in_stock'] + ['destination'] + parameter_names]
cat_objects = Product.objects.filter(category__name=category.name).order_by('code')
arr = []
for i in cat_objects:
arr_add = []
for field in field_names:
arr_add.append(str(getattr(i, field, '')))
if StockRecord.objects.filter(product=i.id).exists():
arr_add.append(str(StockRecord.objects.get(product=i.id).price))
arr_add.append(str(StockRecord.objects.get(product=i.id).provider.name))
arr_add.append(str(StockRecord.objects.get(product=i.id).num_in_stock))
else:
arr_add.append('0')
arr_add.append('')
# if ProductImage.objects.filter(product=i.id).exists():
# arr_add.append(str(ProductImage.objects.get(product=i.id).image_original))
# else:
# arr_add.append('')
dest_list = []
for dest in i.destination.all():
dest_list.append(str(dest.value))
arr_add.append(str(', '.join(dest_list)))
for param in parameter_names:
if NewProductParameterValue.objects.filter(product=i.id, parameter__name=param).exists():
arr_add.append(
NewProductParameterValue.objects.get(product=i.id, parameter__name=param).value.value)
else:
arr_add.append('')
# ts = time.time() - start # profiling
# arr_add.append(ts) # profiling
arr.append(arr_add)
time_stump = time.time() - start # profiling
sheet = pyexcel.Sheet(arr_title + arr)
if "/" not in category.name and cat_objects:
sheets.update({str(category.name): sheet}) # {"Category":sheet1, "Product":sheet2}
book = pyexcel.Book(sheets=sheets)
file_name = "All_{}".format(str(datetime.date.today()))
return excel.make_response(book, file_type='xlsx', file_name=file_name, status=200)
def upload(request):
if request.method == "POST":
form = UploadFileForm(request.POST, request.FILES)
if form.is_valid():
filehandle = request.FILES['file']
return excel.make_response(filehandle.get_sheet(), "csv")
else:
return HttpResponseBadRequest()
else:
form = UploadFileForm()
return render_to_response('upload_form.html', {'form': form},
context_instance=RequestContext(request))
def download_spreadsheet(request, report_id, file_type) -> HttpResponse:
account = get_account(request)
filename, spreadsheet = create_spreadsheet(account=account,
report_id=report_id)
if not spreadsheet:
return JsonResponse({}, encoder=JSEncoder)
if file_type == "xlsx":
# todo: requesting infinite files will flood the system as temp files are saved. Probably load file into
# memory and then remove the original file. With the current group of users the risk is minimal, so no bother
tmp_file_handle = upgrade_excel_spreadsheet(spreadsheet)
with open(tmp_file_handle.name, 'rb') as file_handle:
response = HttpResponse(
file_handle.read(),
content_type=
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
)
response[
"Content-Disposition"] = f"attachment; filename={slugify(filename)}.xlsx"
return response
if file_type == "ods":
output: HttpResponse = excel.make_response(spreadsheet, file_type)
output[
"Content-Disposition"] = f"attachment; filename={slugify(filename)}.ods"
output[
"Content-type"] = "application/vnd.oasis.opendocument.spreadsheet"
return output
if file_type == "csv":
output = excel.make_response(spreadsheet, file_type)
output[
"Content-Disposition"] = f"attachment; filename={slugify(filename)}.csv"
output["Content-type"] = "text/csv"
return output
# anything that is not valid at all.
return JsonResponse({}, encoder=JSEncoder)
def render_to_response(self, context, **response_kwargs):
categoria = context['categoria']
votos = self.sumarizador.votos_csv_export(categoria)
headers = ['distrito', 'seccion', 'circuito', 'mesa', 'opcion', 'votos']
csv_list = [headers]
for voto in votos:
csv_list.append(voto)
(nivel, id_nivel) = self.get_filtro_por_nivel()
filename = f'{categoria.slug}-{nivel if nivel else "todo"}-{id_nivel[0] if id_nivel else "todo"}'
return excel.make_response(excel.pe.Sheet(csv_list), self.filetype, file_name=filename)
def upload(request):
if request.method == "POST":
form = UploadFileForm(request.POST, request.FILES)
if form.is_valid():
filehandle = request.FILES['file']
return excel.make_response(filehandle.get_sheet(), "csv")
else:
return HttpResponseBadRequest()
else:
form = UploadFileForm()
return render_to_response('upload_form.html',
{'form': form},
context_instance=RequestContext(request))
def upload(request):
if request.method == "POST":
form = UploadFileForm(request.POST, request.FILES)
if form.is_valid():
file_handle = request.FILES['file']
return excel.make_response(file_handle.get_sheet(), "csv")
else:
print(traceback.print_stack(f=None, limit=None, file=None))
return HttpResponseBadRequest()
else:
form = UploadFileForm()
return render(request, 'MasterDataManagement/UploadFile.html',
{'form': form})
def get(request, number=None):
headers = {'Authorization': 'JWT ' + request.session["token"]}
get_account = requests.get(
"http://localhost:8000" + reverse("api:account_detail", kwargs={'number': number}),
headers=headers
)
if get_account.status_code == 200:
account = get_account.json()
if account.get('user') == request.user.id:
date_from = request.GET.get('date_from', None)
date_to = request.GET.get('date_to', None)
if (date_from and date_to) is not None:
get_stats_by_date = requests.get(
"http://localhost:8000" + reverse("api:statistics", kwargs={'number': number})
+ "?date_from=" + date_from + "&date_to=" + date_to,
headers=headers
)
stats = get_stats_by_date.json()
stats.insert(0, ['Year', 'Month', 'Total'])
sheet = Sheet(stats)
return excel.make_response(
sheet,
file_type='xlsx',
file_name="account_" + str(number) + "_report_from_" + date_from + "_to_" + date_to
)
else:
get_statistic = requests.get(
"http://localhost:8000" + reverse("api:statistics", kwargs={'number': number}),
headers=headers
)
stats = get_statistic.json()
stats.insert(0, ['Year', 'Month', 'Total'])
sheet = Sheet(stats)
return excel.make_response(
sheet,
file_type='xlsx',
file_name="account_" + str(number) + "_report"
)
def directiva_grupo_excel(request, id):
export = []
# Encabezados de excel
export.append([
'No.',
'CUI',
'Sexo',
'Primer Nombre',
'Segundo Nombre',
'Tercer Nombre',
'Primer Apellido',
'Segundo Apellido',
'Apellido Casada',
'Fecha Nacimiento',
'Correo Electronico',
'Telefono',
'Puesto',
])
# Obtener registros del modelo
grupo_actual = Grupo.objects.get(id=id)
integrantes = AsignacionPersonaGrupo.objects.filter(
grupo=grupo_actual).exclude(puesto=0).order_by('puesto')
count = 1
for integrante in integrantes:
export.append([
count, integrante.persona.cui,
integrante.persona.get_sexo_display(),
integrante.persona.primer_nombre,
integrante.persona.segundo_nombre,
integrante.persona.tercer_nombre,
integrante.persona.primer_apellido,
integrante.persona.segundo_apellido,
integrante.persona.apellido_casada,
str(integrante.persona.fecha_nacimiento),
integrante.persona.correo_electronico, integrante.persona.telefono,
integrante.get_puesto_display()
])
count = count + 1
today = datetime.now()
strToday = today.strftime("%Y%m%d")
# Transformar el array a un arreglo
sheet = excel.pe.Sheet(export)
return excel.make_response(sheet,
"xlsx",
file_name="directiva-" +
grupo_actual.nombre_grupo + "-" + strToday +
".xlsx")
def upload(request):
if request.method == "POST":
form = UploadFileForm(request.POST, request.FILES)
if form.is_valid():
filehandle = request.FILES['file']
return excel.make_response(filehandle.get_sheet(), "csv", file_name="download")
else:
form = UploadFileForm()
return render_to_response(
'upload_form.html',
{
'form': form,
'title': 'Excel file upload and download example',
'header': 'Please choose any excel file from your cloned repository:'
},
context_instance=RequestContext(request))
def exportarDatosExcel(
request): ##Se exportan los datos a un archivo de excel organizado
lock.acquire()
sheet = pyexcel.get_sheet(array=[
vaca1[10], vaca1[6], vaca1[7], vaca1[0], vaca1[1], vaca1[2], vaca1[3],
vaca1[4], vaca1[5], vaca1[8], Vacio, vaca2[10], vaca2[6], vaca2[7],
vaca2[0], vaca2[1], vaca2[2], vaca2[3], vaca2[4], vaca2[5], vaca2[8],
Vacio, vaca3[10], vaca3[6], vaca3[7], vaca3[0], vaca3[1], vaca3[2],
vaca3[3], vaca3[4], vaca3[5], vaca3[8]
])
lock.release()
sheet.transpose()
return excel.make_response(sheet, "xlsx")
def generar_excel(request):
export = []
# Encabezados de excel
export.append([
'No.',
'CUI',
'Sexo',
'Primer Nombre',
'Segundo Nombre',
'Tercer Nombre',
'Primer Apellido',
'Segundo Apellido',
'Apellido Casada',
'Fecha Nacimiento',
'Dirección',
'Teléfono',
'Correo Electronico',
])
# Obtener registros del modelo
personas = Persona.objects.all()
count = 1
for persona in personas:
export.append([
count,
persona.cui,
persona.get_sexo_display(),
persona.primer_nombre,
persona.segundo_nombre,
persona.tercer_nombre,
persona.primer_apellido,
persona.segundo_apellido,
persona.apellido_casada,
str(persona.fecha_nacimiento),
persona.direccion,
persona.telefono,
persona.correo_electronico,
])
count = count + 1
today = datetime.now()
strToday = today.strftime("%Y%m%d")
# Transformar el array a un arreglo
sheet = excel.pe.Sheet(export)
return excel.make_response(sheet,
"xlsx",
file_name="personas-" + strToday + ".xlsx")
def upload(request):
form = UploadFileForm()
if request.method == "POST":
form = UploadFileForm(request.POST, request.FILES)
if form.is_valid():
filehandle = request.FILES['file']
return excel.make_response(filehandle.get_sheet(),
"csv",
file_name="download")
else:
form = UploadFileForm()
return render(
request, 'upload_form.html', {
'form': form,
'title': 'Please upload Your gst Fillings',
'header':
('Please choose any excel file ' + 'Containing Your returns')
})
def upload_file(request):
if request.method == 'POST':
form = UploadFileForm(request.POST, request.FILES)
if form.is_valid():
filehandle = request.FILES('file')
return excel.make_response(filehandle.get_sheet(),
"xls",
file_name="download")
else:
return HttpResponseBadRequest()
else:
form = UploadFileForm()
return render(
request, 'upload_form.html', {
'form': form,
'title': 'Excel file upload and download example',
'header': ('Please choose any excel file')
})
def export_blank_view(request):
sheets = {}
category_list = [i for i in Category.objects.all()]
for category in category_list:
field_names = ['name', 'code', 'first_text'] # 'first_text'
parameter_names = list(set([i.parameter.name for i in NewCategoryParameter.objects.all()
if i.category.name == category.name]))
arr_title = [[str(i) for i in field_names] + ['price'] + ['provider'] + ['num_in_stock'] + ['destination'] + parameter_names]
sheet = pyexcel.Sheet(arr_title)
if "/" not in category.name:
sheets.update({str(category.name): sheet})
book = pyexcel.Book(sheets=sheets)
file_name = "Blank_{}".format(str(datetime.date.today()))
return excel.make_response(book, file_type='xlsx', file_name=file_name, status=200)
def get_csv_file(request, x, y):
"""
Generamos el archivo CSV con los restaurantes ordenados
según su distancia al usuario, y devolvemos el archivo CSV
para su descarga...
"""
export = generate_csv(x, y)
# Creamos una string para nombrar al archivo a descargar.
today = timezone.now()
strToday = today.strftime("%Y/%m/%d-%H:%M:%S")
file_name = "results-" + strToday + ".csv"
# Generamos el archivo CSV en memoria
sheet = excel.pe.Sheet(export)
return excel.make_response(sheet, "csv", file_name=file_name)
def upload(request):
if request.method == "POST":
form = UploadFileForm(request.POST, request.FILES)
if form.is_valid():
filehandle = request.FILES['file']
return excel.make_response(filehandle.get_sheet(),
"csv",
file_name="download")
else:
form = UploadFileForm()
return render_to_response(
'upload_form.html', {
'form': form,
'title': 'Excel file upload and download example',
'header':
'Please choose any excel file from your cloned repository:'
},
context_instance=RequestContext(request))
def do_excel(request):
if request.method == "POST":
form = UploadFileForm(request.POST, request.FILES)
if form.is_valid():
filehandle = request.FILES['file']
return excel.make_response(filehandle.get_sheet(),
"csv",
file_name="download")
else:
form = UploadFileForm()
return render(
request, 'excel/excel.html', {
'form':
form,
'title':
'Excel file upload and download example',
'header':
('Please choose any excel file ' + 'from your cloned repository:')
})
def export_excel(request):
voting_id = request.GET.get('voting_id')
if voting_id is '':
voting_id = -1
census = list(Census.objects.filter(voting_id=voting_id))
users = []
for cen in census:
user = list(User.objects.filter(pk=cen.voter_id))[0]
users.append(user.username)
template = export_to_xlsx(users)
voting = Voting.objects.filter(pk=voting_id)
return excel.make_response(template,
"xlsx",
file_name=voting.name + ".xlsx")
def upload(request):
if request.method == "POST":
form = UploadFileForm(request.POST, request.FILES)
if form.is_valid():
filehandle = request.FILES['file']
return excel.make_response(filehandle.get_sheet(),
"csv",
file_name="download")
else:
form = UploadFileForm()
return render(
request, 'upload_form.html', {
'form':
form,
'title':
'Todo Pinturas S.L.',
'header':
('Introduzca el archivo excel a subir en ' + 'la base de datos:')
})
def resultado_parcial_categoria(request, slug_categoria, filetype):
'''
lista de paradas de transporte urbano de pasajeros
'''
categoria = Categoria.objects.get(slug=slug_categoria)
mesas_reportadas = VotoMesaReportado.objects.filter(
categoria=categoria).order_by('mesa__numero', 'opcion__orden')
headers = [
'seccion', 'numero seccion', 'circuito', 'codigo circuito',
'centro de votacion', 'mesa'
]
for opcion in categoria.opciones.all().order_by('orden'):
headers.append(opcion.nombre)
csv_list = [headers]
resultados = {}
for mesa_reportada in mesas_reportadas:
mesa = mesa_reportada.mesa
opcion = mesa_reportada.opcion
if mesa not in resultados.keys():
resultados[mesa] = {}
if opcion.nombre not in resultados[mesa].keys():
resultados[mesa][opcion.nombre] = 0
resultados[mesa][opcion.nombre] += mesa_reportada.votos
for mesa, opciones in resultados.items():
fila = [
mesa.lugar_votacion.circuito.seccion.nombre,
mesa.lugar_votacion.circuito.seccion.numero,
mesa.lugar_votacion.circuito.nombre,
mesa.lugar_votacion.circuito.numero, mesa.lugar_votacion.nombre,
mesa.numero
]
for opcion, votos in opciones.items():
fila.append(votos)
csv_list.append(fila)
return excel.make_response(excel.pe.Sheet(csv_list), filetype)
def export(self, request):
"""Export filtered reports to given file format."""
queryset = self.get_queryset().select_related(
"task__project__billing_type",
"task__cost_center",
"task__project__cost_center",
)
queryset = self.filter_queryset(queryset)
colnames = [
"Date",
"Duration",
"Customer",
"Project",
"Task",
"User",
"Comment",
"Billing Type",
"Cost Center",
]
content = [
[
report.date,
report.duration,
report.task.project.customer.name,
report.task.project.name,
report.task.name,
report.user.username,
report.comment,
self._extract_billing_type(report),
self._extract_cost_center(report),
]
for report in queryset
]
file_type = request.query_params.get("file_type")
if file_type not in ["csv", "xlsx", "ods"]:
return HttpResponseBadRequest()
sheet = django_excel.pe.Sheet(content, name="Report", colnames=colnames)
return django_excel.make_response(
sheet, file_type=file_type, file_name="report.%s" % file_type
)
def upload(request):
if request.method == "POST":
form = UploadFileForm(request.POST, request.FILES)
if form.is_valid():
filehandle = request.FILES["file"]
return excel.make_response(filehandle.get_sheet(),
"csv",
file_name="download")
else:
form = UploadFileForm()
return render(
request,
"upload_form.html",
{
"form":
form,
"title":
"Excel file upload and download example",
"header":
("Please choose any excel file " + "from your cloned repository:"),
},
)
def export_excel(request, uuid, historic_id):
#check if user has permission to see this prodcut
product = Product.objects.get(uuid=uuid)
#user created this product
if (product.user_id == request.user.id):
data = []
historic = ProductHistoricIndexing.objects.get(
id=urlsafe_base64_decode(force_text(historic_id)))
keywords = Keywords.objects.filter(
historic=historic).order_by('-indexing')
column_names = ['#', 'keyword or phrase', 'indexed']
data.append(column_names)
for index, keyword in enumerate(keywords):
new_row = []
new_row.extend((index, keyword.keyword, keyword.indexing))
data.append(new_row)
sheet = excel.pe.Sheet(data)
sheet.name = "Check My Keywords"
timestamp = datetime.now().strftime("%I-%M-%m-%d-%Y")
return excel.make_response(sheet,
"xlsx",
file_name="export_data_" + timestamp)
def ExportToCsv(request):
export = []
export.append([
'date',
'group',
'server',
])
# Se obtienen los datos de la tabla o model y se agregan al array
results = []
i = 1
for i in range(1, 10):
export.append(["1", "2", "3"])
today = datetime.now()
strToday = today.strftime("%Y%m%d")
sheet = excel.pe.Sheet(export)
return excel.make_response(sheet,
"csv",
file_name="respuesta-" + strToday + ".csv")
def handle_uploaded_file(file: File, output_format):
api = API()
with NamedTemporaryFile(suffix='.pdf', mode='wb') as pdf_file, \
NamedTemporaryFile(suffix='.csv', mode='r') as csv_file:
# Convert PDF to CSV
for chunk in file.chunks():
pdf_file.write(chunk)
convert_into(pdf_file.name,
csv_file.name,
spreadsheet=True,
pages='all',
output_format='csv')
# Process
sheet = pyexcel.get_sheet(file_name=csv_file.name)
sheet.name = 'Output'
api.process_spreadsheet(sheet)
# Output file
return django_excel.make_response(sheet,
output_format,
file_name=path.splitext(
path.basename(file.name))[0])
def model_form_upload(request):
if request.method == 'POST':
form = DocumentForm(request.POST, request.FILES)
if form.is_valid():
df = pd.read_excel(request.FILES['document'])
writer = pd.ExcelWriter('teste.xlsx')
filehandle = df.to_excel(writer)
writer.save()
return excel.make_response(pe.get_sheet(file_name='teste.xlsx'),
"csv",
file_name='forecast_2018')
else:
form = DocumentForm()
return render(
request, 'blog/model_form_upload.html', {
'form':
form,
'title':
'Excel file upload and download example',
'header':
('Please choose any excel file ' + 'from your cloned repository:')
})
def export_operations(request, atype):
if atype == 'data':
data = [[1, 2, 3], [4, 5, 6]]
sheet = excel.pe.Sheet(data)
return excel.make_response(sheet, 'xlsx')
elif atype == "sheet2":
return excel.make_response_from_a_table(Operation,
'xlsx',
file_name='sheet2')
elif atype == "sheet":
query_sets = Operation.objects.all().values_list(
'operation_date', 'label', 'debit_or_credit', 'account', 'amount',
'vat_rate', 'provision_rate', 'all_tax_included', 'apply_vat',
'apply_provision')
column_names = [
'operation_date',
'label',
'debit_or_credit',
'account',
'amount',
'vat_rate',
'vat_amount',
'provision_rate',
'provision_amount',
'all_tax_included',
'apply_vat',
'apply_provision',
]
return excel.make_response_from_array(query_sets,
'xlsx',
file_name='sheet')
# return excel.make_response_from_a_table(
# Operation, 'xlsx', file_name="sheet")
else:
return HttpResponseBadRequest(
"Bad request. please put one of these " +
"in your url suffix: sheet, book or custom")
def export(self, request):
"""Export filtered reports to given file format."""
queryset = self.get_queryset().select_related(
'task__project__billing_type',
'task__cost_center', 'task__project__cost_center'
)
queryset = self.filter_queryset(queryset)
colnames = [
'Date', 'Duration', 'Customer',
'Project', 'Task', 'User', 'Comment',
'Billing Type', 'Cost Center'
]
content = [
[
report.date,
report.duration,
report.task.project.customer.name,
report.task.project.name,
report.task.name,
report.user.username,
report.comment,
self._extract_billing_type(report),
self._extract_cost_center(report),
]
for report in queryset
]
file_type = request.query_params.get('file_type')
if file_type not in ['csv', 'xlsx', 'ods']:
return HttpResponseBadRequest()
sheet = django_excel.pe.Sheet(
content, name='Report', colnames=colnames
)
return django_excel.make_response(
sheet, file_type=file_type, file_name='report.%s' % file_type
)
def export_csv(request):
voting_id = request.GET.get('voting_id')
if voting_id is '':
voting_id = -1
if voting_id is not None:
census = list(Census.objects.filter(voting_id=voting_id))
datos = []
for c in census:
user = list(User.objects.filter(pk=c.voter_id))[0]
votacion = list(Voting.objects.filter(pk=c.voting_id))[0]
tupla = (user, votacion)
datos.append(tupla)
sheet = ExportToCsv(datos)
else:
census = list(Census.objects.all())
datos = []
for c in census:
user = list(User.objects.filter(pk=c.voter_id))[0]
votacion = list(Voting.objects.filter(pk=c.voting_id))[0]
tupla = (user, votacion)
datos.append(tupla)
sheet = ExportToCsv(datos)
return excel.make_response(sheet, "csv", file_name="census_data.csv")
def output(request):
# get parameters
response, school = get_variable_with_error(request, 'al', 'school')
if response is not None:
return response
response, cutoff = get_variable_with_error(request, 'al', 'cutoff')
if response is not None:
return response
cutoff_date = datetime.strptime(cutoff + "-01", "%Y-%m-%d")
min_age = get_variable(request, 'min_age')
if (min_age is None) or (len(min_age) == 0):
min_age = MIN_AGE
else:
min_gpa = float(min_age)
min_gpa = get_variable(request, 'min_gpa')
if (min_gpa is None) or (len(min_gpa) == 0):
min_gpa = 0.0
else:
min_gpa = float(min_gpa)
min_points = get_variable(request, 'min_points')
if (min_points is None) or (len(min_points) == 0):
min_points = MIN_CREDITS
else:
min_points = float(min_points)
formattype = get_variable(request, 'format')
# save parameters
write_last_parameter(request.user, 'al.school', school)
write_last_parameter(request.user, 'al.cutoff', cutoff)
write_last_parameter(request.user, 'al.min_age', min_age)
write_last_parameter(request.user, 'al.min_gpa', min_gpa)
write_last_parameter(request.user, 'al.min_points', min_points)
cursor = connection.cursor()
last_year = cutoff_date.year - 1
curr_year = cutoff_date.year
last_year_where = "year = {0}".format(last_year)
curr_year_where = "(year = {0}) and (issemesteracourse = 1)".format(
curr_year)
cols = "owning_school_clevel,paper_occurrence,credits,name,student_id,school_of_study_clevel,dateofbirth,prog_abbr,result_status,grade"
query = """
select
{7}
from
{6}
where
({0})
and (dateofbirth < date '{1}-01' - interval '{2} year')
and (credits_per_student >= {3})
and (programme_type_code in ('{4}'))
and (school_of_study_clevel = '{5}')
order by student_id
"""
# last year: query
sql = query.format(last_year_where, cutoff, min_age, min_points,
"', '".join(PROGRAM_CODES), school,
GradeResults._meta.db_table, cols)
logger.debug(sql)
cursor.execute(sql)
# last year: generate CSV
fd, outname = tempfile.mkstemp(suffix=".csv",
prefix="reporting-",
dir=tempfile_utils.gettempdir())
query_to_csv(cursor, cols, outname)
os_utils.close_file(fd)
# last year: calculate GPA
last_header = []
last_body = []
compute_gpa(outname, school, cutoff_date, min_gpa, last_header, last_body)
# current year: query
sql = query.format(curr_year_where, cutoff, min_age, min_points,
"', '".join(PROGRAM_CODES), school,
GradeResults._meta.db_table, cols)
logger.debug(sql)
cursor.execute(sql)
# current year: generate CSV
fd, outname = tempfile.mkstemp(suffix=".csv",
prefix="reporting-",
dir=tempfile_utils.gettempdir())
query_to_csv(cursor, cols, outname)
# current year: calculate GPA
curr_header = []
curr_body = []
compute_gpa(outname, school, cutoff_date, min_gpa, curr_header, curr_body)
# students have to be good in both years
final_header = last_header[:]
final_body = []
id_idx = last_header.index(HEADER_ID)
gpa_idx = last_header.index(HEADER_GPA)
points_idx = last_header.index(HEADER_POINTS)
final_header.remove(HEADER_GPA)
final_header.remove(HEADER_POINTS)
last_ids = [row[id_idx] for row in last_body]
curr_ids = [row[id_idx] for row in curr_body]
both = set(last_ids).intersection(set(curr_ids))
for row in curr_body:
if row[id_idx] in both:
arow = []
for i, c in enumerate(row):
if (i == gpa_idx) or (i == points_idx):
continue
arow.append(c)
final_body.append(arow)
# generate output
if formattype in ["csv", "xls"]:
data = OrderedDict()
data['AL - Recommendations - {0}'.format(school)] = [final_header
] + final_body
data['AL - {1} - {0}'.format(school,
curr_year)] = [curr_header] + curr_body
data['AL - {1} - {0}'.format(school,
last_year)] = [last_header] + last_body
book = excel.pe.Book(data)
response = excel.make_response(book,
formattype,
file_name="al-{0}.{1}".format(
cutoff, formattype))
return response
else:
template = loader.get_template('al/list.html')
context = applist.template_context('al')
context['header'] = final_header
context['body'] = final_body
context['curr_year'] = curr_year
context['curr_header'] = curr_header
context['curr_body'] = curr_body
context['last_year'] = last_year
context['last_header'] = last_header
context['last_body'] = last_body
context['school'] = school
form_utils.add_export_urls(request, context, "/al/output",
['csv', 'xls'])
response = HttpResponse(template.render(context, request))
return response
def download(request):
sheet = excel.pe.Sheet([[1, 8],[3, 4]])
return excel.make_response(sheet, "csv")
def download(request, file_type):
sheet = excel.pe.Sheet(data)
return excel.make_response(sheet, file_type)
def generateAssesmentExcel(request, id_assesment):
request.session.set_expiry(timeSleep)
if request.method == 'GET':
#args = request.POST
#id_assesment = args['id_assesment']
#id_assesment = 19
infoAssesment = Assesment.objects.filter(id_assesment=id_assesment)
#funcion que genera el excel de una evaluacion
#variables
delta = 7
viewFields = ['Estudiante','Recomendadas Completadas','Ejercicios Incorrectos',
'Ejercicios Correctos','Tiempo en Ejercicios','Tiempo en Videos',
'En Dificultad','Practicado','Nivel 1','Nivel 2','Dominado',
'Nota','Bonificacion por esfuerzo']
totalFields = len(viewFields)
#carga al arreglo los datos de la evaluacion id_assesment
try:
assesment = Assesment.objects.get(id_assesment=id_assesment)
grades = Student.objects.filter(grade__id_assesment_id=id_assesment
).values('name','grade__grade',
'grade__bonus_grade','grade__recomended_complete','grade__incorrect','grade__correct','grade__excercice_time',
'grade__video_time','grade__struggling','grade__practiced','grade__mastery1','grade__mastery2','grade__mastery3')
except Exception as e:
print '***ERROR*** Ha fallado la query linea 424'
print e
totalGrades = grades.count()
#crea el arreglo inicial
w, h = totalFields +10 ,totalGrades + delta + 10
data = [['' for x in range(w)] for y in range(h)]
print '***************debug******************'
print assesment
data[0][0] = 'Evaluacion'
data[0][1] = assesment.name
data[1][0] = 'Nota Minima'
data[1][1] = assesment.min_grade
data[2][0] = 'Nota Maxima'
data[2][1] = assesment.max_grade
data[3][0] = 'Nota de Aprobacion'
data[3][1] = assesment.approval_grade
data[4][0] = 'Bonificacion por Esfuerzo'
data[4][1] = assesment.max_effort_bonus
#carga las notas y las variables disponibles en grade
for k in range(totalFields):
data[delta-1][k] = viewFields[k]
for i in range(totalGrades):
for j in range(totalFields):
if j==0:
data[i+delta][j] = grades[i]['name']
if j==1:
data[i+delta][j] = grades[i]['grade__recomended_complete']
if j==2:
data[i+delta][j] = grades[i]['grade__incorrect']
if j==3:
data[i+delta][j] = grades[i]['grade__correct']
if j==4:
data[i+delta][j] = grades[i]['grade__excercice_time']
if j==5:
data[i+delta][j] = grades[i]['grade__video_time']
if j==6:
data[i+delta][j] = grades[i]['grade__struggling']
if j==7:
data[i+delta][j] = grades[i]['grade__practiced']
if j==8:
data[i+delta][j] = grades[i]['grade__mastery1']
if j==9:
data[i+delta][j] = grades[i]['grade__mastery2']
if j==10:
data[i+delta][j] = grades[i]['grade__mastery3']
elif j==11:
data[i+delta][j] = grades[i]['grade__grade']
elif j==12:
data[i+delta][j] = grades[i]['grade__bonus_grade']
try:
response = excel.make_response(pe.Sheet(data), 'xls', file_name=assesment.name)
except Exception as e:
print '***ERROR*** no se ha podido generar la respuesta excel'
print e
response = False
return response
def generateAssesmentExcel(request, id_assesment):
request.session.set_expiry(timeSleep)
if request.method == 'GET':
#args = request.POST
#id_assesment = args['id_assesment']
#id_assesment = 19
infoAssesment = Assesment.objects.filter(id_assesment=id_assesment)
#funcion que genera el excel de una evaluacion
#variables
delta = 7
viewFields = [
'Estudiante', 'Recomendadas Completadas', 'Ejercicios Incorrectos',
'Ejercicios Correctos', 'Tiempo en Ejercicios', 'Tiempo en Videos',
'En Dificultad', 'Practicado', 'Nivel 1', 'Nivel 2', 'Dominado',
'Nota', 'Bonificacion por esfuerzo'
]
totalFields = len(viewFields)
#carga al arreglo los datos de la evaluacion id_assesment
try:
assesment = Assesment.objects.get(id_assesment=id_assesment)
grades = Student.objects.filter(
grade__id_assesment_id=id_assesment).values(
'name', 'grade__grade', 'grade__bonus_grade',
'grade__recomended_complete', 'grade__incorrect',
'grade__correct', 'grade__excercice_time',
'grade__video_time', 'grade__struggling',
'grade__practiced', 'grade__mastery1', 'grade__mastery2',
'grade__mastery3')
except Exception as e:
print '***ERROR*** Ha fallado la query linea 424'
print e
totalGrades = grades.count()
#crea el arreglo inicial
w, h = totalFields + 10, totalGrades + delta + 10
data = [['' for x in range(w)] for y in range(h)]
print '***************debug******************'
print assesment
data[0][0] = 'Evaluacion'
data[0][1] = assesment.name
data[1][0] = 'Nota Minima'
data[1][1] = assesment.min_grade
data[2][0] = 'Nota Maxima'
data[2][1] = assesment.max_grade
data[3][0] = 'Nota de Aprobacion'
data[3][1] = assesment.approval_grade
data[4][0] = 'Bonificacion por Esfuerzo'
data[4][1] = assesment.max_effort_bonus
#carga las notas y las variables disponibles en grade
for k in range(totalFields):
data[delta - 1][k] = viewFields[k]
for i in range(totalGrades):
for j in range(totalFields):
if j == 0:
data[i + delta][j] = grades[i]['name']
if j == 1:
data[i +
delta][j] = grades[i]['grade__recomended_complete']
if j == 2:
data[i + delta][j] = grades[i]['grade__incorrect']
if j == 3:
data[i + delta][j] = grades[i]['grade__correct']
if j == 4:
data[i + delta][j] = grades[i]['grade__excercice_time']
if j == 5:
data[i + delta][j] = grades[i]['grade__video_time']
if j == 6:
data[i + delta][j] = grades[i]['grade__struggling']
if j == 7:
data[i + delta][j] = grades[i]['grade__practiced']
if j == 8:
data[i + delta][j] = grades[i]['grade__mastery1']
if j == 9:
data[i + delta][j] = grades[i]['grade__mastery2']
if j == 10:
data[i + delta][j] = grades[i]['grade__mastery3']
elif j == 11:
data[i + delta][j] = grades[i]['grade__grade']
elif j == 12:
data[i + delta][j] = grades[i]['grade__bonus_grade']
try:
response = excel.make_response(pe.Sheet(data),
'xls',
file_name=assesment.name)
except Exception as e:
print '***ERROR*** no se ha podido generar la respuesta excel'
print e
response = False
return response
def download(request):
sheet = excel.pe.Sheet([[1, 2], [3, 4]])
return excel.make_response(sheet, "csv")
def download(request, file_type):
sheet = excel.pe.Sheet(data)
return excel.make_response(sheet, file_type)
def details(request):
# get parameters
response, studentid = get_variable_with_error(request, 'al', 'student')
if response is not None:
return response
formattype = get_variable(request, 'format')
# get name
sql = """
select name
from {1}
where student_id = '{0}'
order by year desc
limit 1
""".format(studentid, GradeResults._meta.db_table)
logger.debug(sql)
cursor = connection.cursor()
cursor.execute(sql)
sname = ""
for row2 in cursor.fetchall():
sname = row2[0]
break
# papers
papers = []
for gr in GradeResults.objects.all().filter(
student_id=studentid).order_by('-occurrence_startdate'):
data = dict()
data["studentid"] = studentid
data["studentname"] = sname
data["program"] = gr.prog_abbr
data["school"] = gr.school_of_study_clevel
data["paper"] = gr.paper_master_code
data["start_date"] = gr.occurrence_startdate
data["classification"] = gr.classification
data["credits"] = gr.credits
data["student_credit_points"] = gr.student_credit_points
data["grade"] = gr.grade
data["grade_status"] = gr.grade_status
papers.append(data)
# CSV or HTML?
if formattype in ['csv', 'xls']:
content = dict()
# papers
data = list()
data.append([
'ID',
'Name',
'Program',
'Faculty/School',
'Paper code',
'Start date',
'Classification',
'Credits',
'Student credit points',
'Grade',
'Grade status',
])
for row in papers:
data.append([
row["studentid"],
row["studentname"],
row["program"],
row["school"],
row["paper"],
row["start_date"],
row["classification"],
row["credits"],
row["student_credit_points"],
row["grade"],
row["grade_status"],
])
content['Academic history'] = data
book = excel.pe.Book(content)
response = excel.make_response(book,
formattype,
file_name="al-student-{0}.{1}".format(
studentid, formattype))
return response
else:
template = loader.get_template('al/details.html')
context = applist.template_context('al')
context['papers'] = papers
form_utils.add_export_urls(request, context, "/al/details",
['csv', 'xls'])
return HttpResponse(template.render(context, request))
def download_report(payout):
sheet = excel.pe.Sheet([[float, balance]])
return excel.make_response(sheet, "csv")
