def post(self, request, *args, **kwargs):
"""Get form instance from upload.
:returns: URL
:rtype: HttpResponse
"""
form_class = self.get_form_class()
form = self.get_form(form_class)
gw_location_file = request.FILES.get('gw_location_file')
gw_level_file = request.FILES.get('gw_level_file')
if form.is_valid():
if gw_location_file:
gw_location_file.seek(0)
if str(gw_location_file).split('.')[-1] == "xls":
sheet = xls_get(gw_location_file, column_limit=4)
elif str(gw_location_file).split('.')[-1] == "xlsx":
sheet = xlsx_get(gw_location_file, column_limit=4)
sheetname = next(iter(sheet))
records = sheet[sheetname]
for record in records:
if record[0].lower() == 'id well':
continue
point = Point(x=record[3], y=record[2], srid=4326)
well = GWWell.objects.create(gwwellname=record[0],
gwwelllocation=point,
gwwelltotallength=record[1])
if gw_level_file:
gw_level_file.seek(0)
if str(gw_level_file).split('.')[-1] == "xls":
sheet = xls_get(gw_level_file, column_limit=4)
elif str(gw_level_file).split('.')[-1] == "xlsx":
sheet = xlsx_get(gw_level_file, column_limit=4)
sheetname = next(iter(sheet))
records = sheet[sheetname]
for record in records:
if record[0].lower == 'time':
continue
try:
well = GWWell.objects.get(gwwellname=record[3])
time = dateparse.parse_datetime(record[0])
well_level_log = GWGeologyLog.objects.create(
phenomenonTime=time,
resultTime=time,
gw_level=record[2],
reference=record[1])
well.gwwellgeology.add(well_level_log)
except GWWell.DoesNotExist:
pass
pass
return self.form_valid(form)
else:
return self.form_invalid(form)
def excel(request):
if request.method == 'POST':
try:
excel_file = request.FILES['excel_file']
if str(excel_file).split('.')[-1] == "xls":
data = xls_get(excel_file, column_limit=5)
elif str(excel_file).split('.')[-1] == 'xlsx':
data = xlsx_get(excel_file, column_limit=5)
else:
return HttpResponse("Invalid File")
details = data["Detalle"]
if len(details) > 1:
for detail in details:
if len(detail) > 0 and detail[0] != "Sucursal":
Client.objects.create(sucursal=detail[0],
cartera=detail[1],
clientes=detail[2],
fecha_alta=detail[3],
saldo=detail[4])
return HttpResponse("Data Readed")
except MultiValueDictKeyError:
return HttpResponse("Invalid File")
else:
return render(request, 'excelapp/index.html')
def addExcel(request, id):
errors = ""
filename = str(request.FILES["file"])
if filename.endswith('.xlsx'):
data = xlsx_get(request.FILES["file"])
else:
data = xls_get(request.FILES["file"])
for key, sheet in data.items():
c = 0
for row in sheet:
c += 1
if sheet.index(row) == 0:
continue
else:
if validationRowXls(row, sheet):
dataRecord = Data()
dataRecord.name = row[sheet[0].index('name')]
dataRecord.age = row[sheet[0].index('age')]
dataRecord.gender = row[sheet[0].index('gender')]
dataRecord.phone = row[sheet[0].index('phone')]
dataRecord.email = row[sheet[0].index('email')]
dataRecord.file_id = id
dataRecord.save()
else:
errors += "Error in row" + str(c) + "\n"
return errors
def __init__(self, upload_session: UploadSession):
self.upload_session = upload_session
_file = self.upload_session.upload_file
self.total_records = 0
records = {}
if _file:
_file.seek(0)
sheet = None
if str(_file).split('.')[-1] == 'xls':
sheet = xls_get(_file, column_limit=20)
elif str(_file).split('.')[-1] == 'xlsx':
sheet = xlsx_get(_file, column_limit=20)
if sheet:
try:
for sheet_name in self.SHEETS:
sheet_records = sheet[sheet_name][self.START_ROW:]
records[sheet_name] = sheet_records
self.total_records += len(sheet_records)
except KeyError as e:
self.upload_session.update_progress(
finished=True,
progress=100,
status='Sheet {} is needed'.format(e)
)
return
self.records = records
self.process()
def Parse_xl(request, format=None):
try:
excel_file = request.FILES['myfile']
except MultiValueDictKeyError:
messages.error(request, 'Votre Upload a mal tourné')
return render(request, 'users/importdb.html')
if (str(excel_file).split('.')[-1] == "xls"):
data = xls_get(excel_file, column_limit=5)
elif (str(excel_file).split('.')[-1] == "xlsx"):
data = xlsx_get(excel_file, column_limit=5)
else:
messages.info(request, 'Veuillez importer un fichier de type Excel')
return render(request, 'users/importdb.html')
Clients = data["Worksheet"]
if (len(Clients) > 1): # We have company data
for Worksheet in Clients:
if (len(Worksheet) > 0): # The row is not blank
if (Worksheet[0] != "id"): # This is not header
# Fill ending columns with blank
if (len(Worksheet) < 5):
i = len(Worksheet)
while (i < 5):
Worksheet.append("")
i += 1
# Check if Client exist
# Assume that Client name is unique
c = Users.objects.filter(name=Worksheet[1])
if (c.count() == 0):
Users.objects.create(name=Worksheet[1],
prenom=Worksheet[2],
email=Worksheet[3],
city=Worksheet[4])
messages.success(request, 'Votre base de donnée a bien été Sauvegardé!')
return render(request, 'users/importdb.html')
def save(self, *args, **kwargs):
super(ImportExcel, self).save(*args, **kwargs)
if self.excel_file != self.__original_excel:
print("save excel")
else:
print("read excel")
ef = self.excel_file.url
data = xls_get(ef, column_limit=4)
data_json = ast.literal_eval(json.dumps(data)).values()
data_iterator = iter(data_json)
data_only = next(data_iterator)
if self.status_file == False:
for item in data_only:
try:
model_product = Producto()
model_product.nombre_producto = str(item[0])
model_product.stock_level = int(item[1])
model_product.price = float(item[2])
model_product.save()
except:
print("Se omite %s" % (item))
self.status_file = True
self.save()
def get_input_post(request):
#print("I am called")
try:
excel_file = request.FILES['files']
print("hello world")
if (str(excel_file).split('.')[-1] == "xls"):
data = xls_get(excel_file)
print(data)
print("xls file called")
elif (str(excel_file).split('.')[-1] == "xlsx"):
data = xlsx_get(excel_file)
print("xlsx file called")
else:
return render(request, 'index.html')
json_data = json.dumps(data)
#print(json_data)
j = 1
for i in range(5):
data['Sheet1'][i + 1][j] = str(
int(data['Sheet1'][i + 1][j]) + int(data['Sheet1'][i + 1][j]))
print(data['Sheet1'][i + 1][j])
save_data("my_output.xlsx", data)
return render(request, 'success.html')
except MultiValueDictKeyError:
return render(request, 'index.html')
def post(self, request, format=None):
try:
excel_file = request.FILES['files']
except MultiValueDictKeyError:
return redirect('upload/')
if (str(excel_file).split('.')[-1] == "xls"):
data = xls_get(excel_file, column_limit=4)
elif (str(excel_file).split('.')[-1] == "xlsx"):
data = xlsx_get(excel_file, column_limit=4)
else:
return redirect('upload/')
def create(request):
form = DataForm()
if request.method == 'POST':
if request.POST.get("input_excel"):
try:
excel_file = request.FILES['file-excel']
except MultiValueDictKeyError:
return render(request, 'home_data_training_create.html',
{'form': form})
if str(excel_file).split('.')[-1] == 'xls':
data = xls_get(excel_file, column_limit=5)
elif str(excel_file).split('.')[-1] == 'xlsx':
data = xlsx_get(excel_file, column_limit=5)
else:
return render(request, 'home_data_training_create.html',
{'form': form})
if data is not None:
save_excel_to_db(data)
return redirect('home:data-training')
else:
form = DataForm(request.POST)
if form.is_valid():
no = form.cleaned_data['no']
ppm_ch4 = float(form.cleaned_data['ppm_ch4'])
ppm_c2h4 = float(form.cleaned_data['ppm_c2h4'])
ppm_c2h2 = float(form.cleaned_data['ppm_c2h2'])
fault = form.cleaned_data['fault']
sum = ppm_ch4 + ppm_c2h4 + ppm_c2h2
ch4 = (ppm_ch4 / sum) * 100
c2h4 = (ppm_c2h4 / sum) * 100
c2h2 = (ppm_c2h2 / sum) * 100
try:
param = Data.objects.get(no=no)
except Data.DoesNotExist:
param = Data()
param.no = no
param.ppm_ch4 = ppm_ch4
param.ppm_c2h4 = ppm_c2h4
param.ppm_c2h2 = ppm_c2h2
param.persen_ch4 = ch4
param.persen_c2h4 = c2h4
param.persen_c2h2 = c2h2
param.fault = fault
param.save()
return redirect('home:data-training')
return render(request, 'home_data_training_create.html', {'form': form})
def general(request):
if request.method == 'POST':
numbers = ""
message = request.POST.get("message")
parents = request.POST.get("parents")
teachers = request.POST.get("teachers")
entered_numbers = request.POST.get("entered_numbers")
if parents:
students = Student.objects.filter(completed=False)
for student in students:
numbers += f"{student.parent_sms},"
if teachers:
teachers = Staff.objects.filter(has_left=False)
for teacher in teachers:
numbers += f"{teacher.sms_number},"
if entered_numbers:
entered_numbers = entered_numbers.split(",")
for number in entered_numbers:
if number.strip():
numbers += f"{number.strip()},"
excel_file = request.FILES.get("files")
if excel_file:
COLUMN_LIMIT = 1
file_extention = str(excel_file).split(".")[-1]
if (file_extention == "xls") or file_extention == "xlsx":
file_extention = str(excel_file).split(".")[-1]
if (file_extention == "xls"):
data = xls_get(excel_file, column_limit=COLUMN_LIMIT)
else:
data = xlsx_get(excel_file, column_limit=COLUMN_LIMIT)
try:
contacts = data["sms_contacts"]
for row in contacts:
if (len(row) > 0):
if (row[0].isdigit() and len(row[0]) == 10):
numbers += f"{row[0]},"
except KeyError as err:
request.session[
"error_message"] = f"The uploaded file has no sheet named '{sheet_name}'"
return False
else:
request.session["error_message"] = "File format not acceptable"
return redirect("sms:index")
send_bulk_sms.delay(name="bulk", message=message, numbers=numbers)
return redirect("sms:index")
def post(self, request, *args, **kwargs):
try:
excel_file = request.FILES["excelFile"]
except MultiValueDictKeyError:
return redirect(self.get_success_url())
if str(excel_file).split(".")[-1] == "xls":
data = xls_get(excel_file, column_limit=15)
elif str(excel_file).split(".")[-1] == "xlsx":
data = xlsx_get(excel_file, column_limit=15)
users = data["Usuarios"]
categories = data["Categorias"]
subjects = data["Assuntos"]
groups = data["Grupos"]
usersList = []
usersErrors = []
categoriesList = []
categoriesErrors = []
subjectsList = []
subjectsErrors = []
groupsList = []
groupsErrors = []
if len(users) > 1:
usersList, usersErrors = importUsers(users)
if len(categories) > 1:
categoriesList, categoriesErrors = importCategories(
categories, usersList)
if len(subjects) > 1:
subjectsList, subjectsErrors = importSubjects(
subjects, usersList, categoriesList)
if len(groups) > 1:
groupsList, groupsErrors = importGroups(groups, subjectsList,
usersList)
messages.success(self.request, _("Environment imported successfully!"))
context = self.get_context_data(**kwargs)
context["usersErrors"] = usersErrors
context["categoriesErrors"] = categoriesErrors
context["subjectsErrors"] = subjectsErrors
context["groupsErrors"] = groupsErrors
return self.render_to_response(context)
def post(self, request, *args, **kwargs):
"""Get form instance from upload.
:returns: URL
:rtype: HttpResponse
"""
form_class = self.get_form_class()
form = self.get_form(form_class)
gw_location_file = request.FILES.get('gw_location_file')
gw_level_file = request.FILES.get('gw_level_file')
location_records = None
level_records = None
if form.is_valid():
if gw_location_file:
gw_location_file.seek(0)
if str(gw_location_file).split('.')[-1] == "xls":
sheet = xls_get(gw_location_file, column_limit=4)
elif str(gw_location_file).split('.')[-1] == "xlsx":
sheet = xlsx_get(gw_location_file, column_limit=4)
sheetname = next(iter(sheet))
location_records = sheet[sheetname]
if gw_level_file:
gw_level_file.seek(0)
if str(gw_level_file).split('.')[-1] == "xls":
sheet = xls_get(gw_level_file, column_limit=4)
elif str(gw_level_file).split('.')[-1] == "xlsx":
sheet = xlsx_get(gw_level_file, column_limit=4)
sheetname = next(iter(sheet))
level_records = sheet[sheetname]
job = well_from_excel.delay(location_records, level_records)
request.session['task_id'] = job.id
return self.form_valid(form)
else:
return self.form_invalid(form)
class ParseExcel(APIView)
def post(self, request),format = None:
try:
excel_file = request.FILES['parse.xlsx']
except MultiValueDictKeyError:
return redirect(<your_upload_file_failed_url>)
data = xls_get(excel_file, column_limit=4)
elif (str(excel_file).split(‘.’)[-1] == “xlsx”):
data = xlsx_get(excel_file, column_limit=4)
else:
def get_records(self):
""" Get records form upload session """
_file = self.upload_session.upload_file
records = []
if _file:
_file.seek(0)
sheet = None
if str(_file).split('.')[-1] == 'xls':
sheet = xls_get(_file, column_limit=20)
elif str(_file).split('.')[-1] == 'xlsx':
sheet = xlsx_get(_file, column_limit=20)
if sheet:
sheet_name = next(iter(sheet))
records = sheet[sheet_name]
return records[self.START_ROW:]
def post(self, request, format=None):
try:
excel_file = request.FILES['fileToUpload']
except MultiValueDictKeyError:
# return reverse_lazy("schools:students:list")
return HttpResponse("No File")
if str(excel_file).split('.')[-1] == 'xls':
data = xls_get(excel_file, column_limit=6)
elif str(excel_file).split('.')[-1] == 'xlsx':
data = xlsx_get(excel_file, column_limit=6)
else:
return HttpResponse("Error of Invalid file")
students = data["Students"]
register_students.delay(request.user, students)
return redirect("schools:students:list")
def post(self, request, format=None):
"""
Import Industries from xlsx/xls files
:param request:
:type request:
:param format:
:type format:
:return:
:rtype:
"""
try:
excel_file = request.data["files"]
except MultiValueDictKeyError:
raise Exception("Excel files needs to be uploaded!!")
file_type = str(excel_file).split('.')[-1]
if file_type == "xls":
data = xls_get(excel_file, start_row=1)
elif file_type == "xlsx":
data = xlsx_get(excel_file, start_row=1)
else:
return Response('Only xls and xlsx file types are allowed')
try:
for obj in data['Sheet1']:
try:
Industry.objects.get_or_create(naics_code=obj[1],
industry_type=obj[2])
except Exception as error:
settings.LOGGER.info(
"IndustryImportView >> post >> error: {}, data: {}".
format(error, obj))
return Response({
"result": "Industry Successfully exported!!",
"status": status.HTTP_201_CREATED
})
except Exception as error:
settings.LOGGER.info(
"IndustryImportView >> post >> error: {}".format(error))
return Response({"error": "{}".format(error)},
status=status.HTTP_400_BAD_REQUEST)
def handle_input_file(self, request):
"""
Saves the information provided by the excel file into database.
Adds error if the file is not an excel file
"""
try:
excel_file = request.FILES['file']
except MultiValueDictKeyError:
raise MultiValueDictKeyError("MultiValueDictKeyError")
if (str(excel_file).split('.')[-1] == "xls"):
data = xls_get(excel_file, column_limit=2)['Post']
elif (str(excel_file).split('.')[-1] == "xlsx"):
data = xlsx_get(excel_file, column_limit=2)['Post']
else:
messages.error(request, "this is not an excel file")
return
user = request.user
return self.save_data(data, user)
def parse_excel_into_consumption_objects(file, request, space_id=1):
try:
space = Space.objects.get(pk=space_id)
except ObjectDoesNotExist:
initial = Space.objects.create(name="Default", owner=None)
space = Space.objects.get(pk=space_id)
data = list(xls_get(file).items())[0][1]
regions = Region.objects.all()
with open(os.path.join(BASE_DIR, 'static', "utils/regions_ids.json"), encoding='utf-8') as json_file:
regions_ids = json.load(json_file)
substrings = ["область", "республика", "г.", "край", "автономный округ", "федеральный округ"]
for rownum in range(len(data)):
if len(data[rownum]) > 1:
if any([substring in data[rownum][0].lower() for substring in substrings]):
if data[rownum][0] not in regions.values_list('name', flat=True):
try:
region = Region.objects.create(
id=regions_ids[data[rownum][0]],
name=data[rownum][0]
)
except KeyError:
continue
else:
for reg in regions:
if reg.name == data[rownum][0]:
region = reg
for colnum in range(len(data[rownum])):
if isinstance(data[3][colnum], int):
year = data[3][colnum]
if isinstance(data[rownum][colnum], float) or isinstance(data[rownum][colnum], int):
consumption = data[rownum][colnum]
else:
continue
_, created = ElectricityConsumption.objects.update_or_create(
year=year,
region=region,
space=space,
defaults={"consumption": consumption, "created_by": request.user}
)
def _get_data_from_sheet(request, excel_file, COLUMN_LIMIT, sheet_name):
try:
file_extention = str(excel_file).split(".")[-1]
if (file_extention == "xls"):
data = xls_get(excel_file, column_limit=COLUMN_LIMIT)
else:
data = xlsx_get(excel_file, column_limit=COLUMN_LIMIT)
except Exception as err:
request.session["error_message"] = str(err)
return False
try:
data = data[sheet_name]
return data
except KeyError as err:
request.session[
"error_message"] = f"The uploaded file has no sheet named '{sheet_name}'"
return False
except Exception as err:
request.session["error_message"] = str(err)
return False
def get_context_data(self, **kwargs):
context = super(UploadSessionDetailView,
self).get_context_data(**kwargs)
if self.object.uploader != self.request.user.id:
raise PermissionDenied('You do not have permission.')
# construct excel
excel = {}
for row_status in self.object.uploadsessionrowstatus_set.order_by(
'row'):
# create header
if not excel or row_status.sheet_name not in excel:
_file = self.object.upload_file.path
sheet = None
if str(_file).split('.')[-1] == 'xls':
sheet = xls_get(_file, column_limit=20)
elif str(_file).split('.')[-1] == 'xlsx':
sheet = xlsx_get(_file, column_limit=20)
if sheet:
sheet_records = sheet[
row_status.sheet_name][:BaseUploader.START_ROW]
excel[row_status.sheet_name] = {
'header': sheet_records,
'size': len(sheet_records[0]),
'body': {},
'id': row_status.sheet_name.replace(' ', '_')
}
try:
excel[row_status.sheet_name]['body'][row_status.row]
except KeyError:
excel[row_status.sheet_name]['body'][row_status.row] = [
''
] * excel[row_status.sheet_name]['size']
excel[row_status.sheet_name]['body'][row_status.row][
row_status.
column] = row_status.note if row_status.note else 'Skipped'
context['excel'] = excel
return context
def post(self, request, format=None):
try:
excel_file = request.FILES['excel_file']
if str(excel_file).split('.')[-1] == "xls":
data = xls_get(excel_file, column_limit=5)
elif str(excel_file).split('.')[-1] == "xlsx":
data = xlsx_get(excel_file, column_limit=5)
else:
return HttpResponse("Invalid File")
details = data["Detalle"]
if len(details) > 1:
for detail in details:
if len(detail) > 0 and detail[0] != "Sucursal":
Client.objects.create(sucursal=detail[0],
cartera=detail[1],
clientes=detail[2],
fecha_alta=detail[3],
saldo=detail[4])
return HttpResponse("Data Readed")
except MultiValueDictKeyError:
return HttpResponse("Invalid File")
def daftar_buku(request):
daftar_buku = data_buku.objects.all()
data = ""
company = ''
if request.method == "POST":
try:
excel_file = request.FILES["fl"]
except MultiValueDictKeyError:
return HttpResponse('Salah')
if (str(excel_file).split(".")[-1] == "xls"):
data = xls_get(excel_file, column_limit=13)
elif (str(excel_file).split(".")[-1] == "xlsx"):
data = xlsx_get(excel_file, column_limit=13)
else:
print("salah")
key = ''.join(data.keys())
solve = data[key]
solved = 0
for i in range(len(solve)):
if len(solve[i]) != 0 :
solved += 1
else :
pass
for i in range(1,solved):
try :
data_buku.objects.create(judul_buku=solve[i][1],jenis_buku=solve[i][2],penulis=solve[i][3],tgl_terbit=solve[i][4],penerbit=solve[i][5],jumlah_buku=solve[i][6])
messages.add_message(request, messages.INFO, 'Buku berhasil ditambahkan')
return redirect('home:daftar_buku')
except:
messages.add_message(request, messages.WARNING, 'Buku gagal ditambahkan')
return redirect('home:daftar_buku')
return render(request, 'home/daftar_buku.html',{'daftar_buku':daftar_buku})
def post(self, request):
try:
file = request.FILES['file']
if (str(file).split('.')[-1] == "xls"):
data = xls_get(file, column_limit=12)
elif (str(file).split('.')[-1] == "xlsx"):
data = xlsx_get(file, column_limit=12)
else:
return redirect('/pollingstations')
# The data is in a sheet labelled PS
# ToDo:Refactor We'll refactor this later. For now, I need to get the data into the application
# So this function might be a long with multiple ifs, but it'll be okay.
admin_user = User.objects.get(pk=1)
excel_data = data['PS']
clean_data = excel_data[7:]
for row in clean_data:
if (len(clean_data) > 0):
serial_no, district_code, district_name, county_code, county_name, subcounty_code, subcounty_name, parish_code, parish_name, polling_station_code, polling_station_name, voter_count = row
# Save District
# ToDo - Exclude excel column titles
if District.objects.filter(name=district_name,
code=district_code).exists():
district = District.objects.get(name=district_name,
code=district_code)
else:
if district_name:
district = District.objects.create(
name=district_name,
code=district_code,
created_by=admin_user,
updated_by=admin_user)
# Save Counties
if County.objects.filter(name=county_name,
code=county_code).exists():
county_ = County.objects.get(name=county_name,
code=county_code)
else:
if county_name:
county_ = County.objects.create(
name=county_name,
code=county_code,
district=district,
created_by=admin_user,
updated_by=admin_user)
# Save Sub counties
if Subcounty.objects.filter(name=subcounty_name,
code=subcounty_code).exists():
subcounty = Subcounty.objects.get(name=subcounty_name,
code=subcounty_code)
else:
if subcounty_name:
subcounty = Subcounty.objects.create(
name=subcounty_name,
code=subcounty_code,
county=county_,
created_by=admin_user,
updated_by=admin_user)
# Save Parishes
if Parish.objects.filter(name=parish_name,
code=parish_code).exists():
pass
else:
if parish_name and parish_name:
Parish.objects.create(name=parish_name,
code=parish_code,
subcounty=subcounty,
created_by=admin_user,
updated_by=admin_user)
if county_:
# Save Polling Stations
if Pollingstation.objects.filter(
name=polling_station_name,
code=polling_station_code,
county=county_).exists():
pass
else:
if polling_station_name and polling_station_name:
Pollingstation.objects.create(
name=polling_station_name,
code=polling_station_code,
county=county_,
total_voters=voter_count,
created_by=admin_user,
updated_by=admin_user)
return redirect('/pollingstation')
except MultiValueDictKeyError:
print('Exception caught')
return redirect('/pollingstation')
def upload_excel(request):
if request.method == 'POST':
try:
excel_file = request.FILES.get('income_excel_file')
if excel_file == None:
messages.error(request,'Excel file required')
return redirect('import_income')
if not (excel_file.name.endswith('.xls') or excel_file.name.endswith('.xlsx')):
messages.error(request,'Please Upload a Excel file.')
return redirect('import_income')
if excel_file.multiple_chunks():
messages.error(request,"Uploaded file is too big (%.2f MB)." % (excel_file.size/(1000*1000),))
return redirect('import_income')
if excel_file.name.endswith('.xls'):
data = xls_get(excel_file, column_limit=4)
elif excel_file.name.endswith('.xlsx'):
data = xlsx_get(excel_file, column_limit=4)
else:
messages.error(request,'Please Upload a Excel file.')
return redirect('import_income')
keys_excel = list(data.keys())
income_excel_data = data[keys_excel[0]]
try:
income_excel_data.remove([])
except:
pass
if len(income_excel_data) > 11:
messages.error(request,'Please upload a excel file with less than 10 rows.')
return redirect('import_income')
if IncomeSource.objects.filter(user = request.user, source='Loaded From Excel'):
excel_income_source = IncomeSource.objects.get(user = request.user, source='Loaded From Excel')
else:
excel_income_source = IncomeSource.objects.create(user = request.user, source='Loaded From Excel')
excel_income_source.save()
headers = income_excel_data.pop(0)
headers = [c.lower() for c in headers]
if headers != ['date', 'source', 'description', 'amount']:
income_send_error_mail(request,excel_file.name,'Excel')
messages.error(request,'Please Check if the format of excel file is correct.')
return redirect('import_income')
income_count = 0
for row in income_excel_data:
if(len(row) != 4):
break
if not row[0] == '':
if isinstance(row[0],datetime.date):
date = row[0]
else:
date = row['date'].split('-')
try:
date = datetime.date(2000 + int(date[2]) ,int(date[1]),int(date[0]))
except:
date = datetime.date.today()
else:
date = datetime.date.today()
if not row[1] == '':
source = row[1].strip().lower().capitalize()
if IncomeSource.objects.filter(user = request.user, source = source).exists():
income_source = IncomeSource.objects.get(user = request.user, source = source)
else:
income_source = IncomeSource.objects.create(user = request.user, source = source)
income_source.save()
else:
income_source = excel_income_source
if not row[2] == '':
description = row[2].strip()
else:
description = 'Loaded From Excel'
if not row[3] == '':
Income.objects.create(
user = request.user,
amount = float(row[3]),
date = date,
description = description,
source = income_source
).save()
income_count += 1
income_send_success_mail(request,excel_file.name,income_count,'Excel')
messages.success(request,'Incomes are saved from excel file.')
return redirect('income')
except Exception as e:
income_send_error_mail(request,excel_file.name,'Excel')
print(repr(e))
messages.error(request,'Please Check if the format of excel file is correct.')
return redirect('import_income')
def Extract_dt(request):
# Handle file upload
if request.user.is_authenticated:
if request.method == 'POST':
form = DocFileForm(request.POST, request.FILES)
if form.is_valid():
try:
excel_file = request.FILES['docfile']
except:
return JsonResponse({"error": "File not found"})
if (str(excel_file).split('.')[-1] == "xls"):
data = xls_get(excel_file, column_limit=10)
elif (str(excel_file).split('.')[-1] == "xlsx"):
data = xlsx_get(excel_file, column_limit=10)
else:
return JsonResponse({"error": "Invalid File Format"})
data = data[list(data.keys())[0]]
data = to_dict(data)
try:
cat = Seller.objects.get(id=request.user.id).bs_category
except:
return redirect('404')
if cat == "Literature and Stationary":
Product_List.objects.filter(
seller=request.user.id).delete()
# try:
for i in data:
Product_List(
name=i["title"],
brand=i["publisher"],
base_price=i["price"],
rating=i["rating"],
total_ratings=i["total ratings"],
stock=i["stock"],
description=i["description"]
if "description" in i.keys() else
"No description provided by Seller",
additional=json.dumps({
"author":
i["author"],
"pages":
i["pages"],
"language":
i["language"],
"publication date":
i["publication date"]
}),
seller=request.user.id,
).save()
# except:
# return JsonResponse({"error":"Bad Request"})
else:
return JsonResponse({"error": "Bad Request"})
return JsonResponse({"success": True})
else:
form = DocFileForm() # A empty, unbound form
return render(request, 'sheet_upload.html', {'form': form})
return redirect('404')
def post(self, request):
try:
excel_file = request.FILES['file']
data = {}
if str(excel_file).split('.')[-1] == 'xls':
data = xls_get(excel_file, column_limit=50)
elif str(excel_file).split('.')[-1] == 'xlsx':
data = xlsx_get(excel_file, column_limit=50)
data = data['ResearchProductivity'][6:][0:]
for d in data:
if d[0] != 'end of records':
title = d[1]
year_accomplished = d[2]
classification = 1
psced_classification = d[5]
if not d[3]:
classification = 2
conference_level = 1
conference_title = d[9]
conference_date = d[14]
conference_venue = d[15]
if d[11]:
conference_level = 2
elif d[12]:
conference_level = 3
elif d[13]:
conference_level = 4
record_len = len(
Record.objects.filter(
title=title,
year_accomplished=year_accomplished,
classification=Classification.objects.get(
pk=classification),
psced_classification=PSCEDClassification.objects.
get(pk=psced_classification)))
if record_len == 0:
record = Record(
title=title,
year_accomplished=year_accomplished,
classification=Classification.objects.get(
pk=classification),
psced_classification=PSCEDClassification.objects.
get(pk=psced_classification))
record.save()
UserRecord(record=record, user=request.user).save()
else:
continue
Conference(title=conference_title,
conference_level=ConferenceLevel.objects.get(
pk=conference_level),
date=conference_date,
venue=conference_venue,
record=record).save()
if d[16]:
publication_name = d[23]
sn_list = "".join(d[24].split()).split(',')
isbn = ''
issn = ''
isi = ''
for sn in sn_list:
if sn.upper().find('ISBN:') >= 0:
isbn = sn.replace('ISBN:', '')
elif sn.upper().find('ISSN:') >= 0:
issn = sn.replace('ISSN:', '')
elif sn.upper().find('ISI:') >= 0:
isi = sn.replace('ISI:', '')
publication_level = 1
if d[20]:
publication_level = 2
elif d[21]:
publication_level = 3
elif d[22]:
publication_level = 4
year_published = d[18]
Publication(
name=publication_name,
isbn=isbn,
issn=issn,
isi=isi,
publication_level=PublicationLevel.objects.get(
pk=publication_level),
year_published=year_published,
record=record).save()
if d[25]:
budget_type = 1
budget_allocation = d[30]
funding_source = d[31]
if d[28]:
budget_type = 2
elif d[20]:
budget_type = 3
Budget(
budget_type=BudgetType.objects.get(pk=budget_type),
budget_allocation=budget_allocation,
funding_source=funding_source,
record=record).save()
if d[32]:
industry = d[34]
institution = d[35]
collaboration_type = 1
if len(d) >= 38:
if d[37]:
collaboration_type = 2
elif len(d) >= 39:
if d[38]:
collaboration_type = 3
Collaboration(
collaboration_type=CollaborationType.objects.get(
pk=collaboration_type),
industry=industry,
institution=institution,
record=record).save()
else:
break
messages.success(request, 'Import Success!')
except (MultiValueDictKeyError, KeyError, ValueError, OSError):
messages.error(request, "Some rows have invalid values")
print('Multivaluedictkeyerror/KeyError/ValueError/OSError')
except (DataError, ValidationError):
messages.error(request, "The form is invalid")
print('DataError/ValidationError')
return redirect('records-index')
def upload(request):
if request.method == 'POST':
form = UploadFileForm(request.POST, request.FILES)
if form.is_valid():
try:
excel_file = request.FILES['files']
if (str(excel_file).split('.')[-1] == "xls"):
data = xls_get(excel_file, column_limit=7)
elif (str(excel_file).split('.')[-1] == "xlsx"):
data = xlsx_get(excel_file, column_limit=7)
unit_stations = data["unit_station_names"]
loan_status = data["loan_status"]
loans = data["loans"]
#adding unit stations data to the database
if (len(unit_stations) > 2):
for i in range(1, len(unit_stations)):
#to avoid making duplicate records
if not Unit_station_names.objects.filter(
station_name=unit_stations[i][1]):
Unit_station_names.objects.create(
station_name=unit_stations[i][1],
daily_target=unit_stations[i][2],
monthly_target=unit_stations[i][3])
else:
#to update only the daily target and monthly target values that have changed
station = Unit_station_names.objects.get(
station_name=unit_stations[i][1])
station.daily_target = unit_stations[i][2]
station.monthly_target = unit_stations[i][3]
station.save()
#adding loan status data to the database
if (len(loan_status) > 2):
for j in range(1, len(loan_status)):
#to avoid making duplicate records
if not Loan_status.objects.filter(
loan_status=loan_status[j][1]):
Loan_status.objects.create(
loan_status=loan_status[j][1])
#adding loans data to the database
if (len(loans) > 2):
for k in range(1, len(loans)):
#to avoid making duplicate records
if not Loans.objects.filter(loan_code=loans[k][2]):
station = Unit_station_names.objects.get(
id=loans[k][4])
status = Loan_status.objects.get(id=loans[k][6])
Loans.objects.create(loan_date=loans[k][0],
due_date=loans[k][1],
loan_code=loans[k][2],
loan_amount=loans[k][3],
customer_station=station,
customer_id=loans[k][5],
loan_status=status)
messages.info(request, 'Processed successfully!!!')
return HttpResponseRedirect('/upload/')
except:
#used to raise exceptions if the excel document is not formatted properly
messages.info(
request,
'This is not a valid document for the above process')
return HttpResponseRedirect('/upload/')
else:
messages.info(
request,
'The was an error processing your upload.Check your excel file.'
)
return HttpResponseRedirect('/upload/')
else:
form = UploadFileForm()
return render(request, 'home/upload.html', {'form': form})
def index(request):
if request.method == "POST":
excel_file = request.FILES['file']
if str(excel_file).split('.')[-1] == 'xls':
data = xls_get(excel_file)
elif str(excel_file).split('.')[-1] == 'xlsx':
data = get_data(excel_file)
dataSet = data[list(data)[0]]
insertedNumber = 0
for row in dataSet:
if dataSet.index(row) == 0:
next
else:
datasource = Data_Source.objects.filter(
name=row[dataSet[0].index('Data_Source')])
testname = Test_Name.objects.filter(
Name=row[dataSet[0].index('Result_Name')])
print(datasource)
yield gen_message("Cheeking " +
row[dataSet[0].index('Data_Source')] +
" Row Number " + str(dataSet.index(row)) +
"\n")
if len(datasource) == 0:
datasource = Data_Source()
datasource.name = row[dataSet[0].index('Data_Source')]
datasource.save()
yield gen_message("<span style='color:green'>Insert " +
datasource.name + " " +
str(datasource.id) + "</span>\n")
yield gen_message("Cheeking " +
row[dataSet[0].index('Result_Name')] +
" Row Number " +
str(dataSet.index(row)) + "\n")
if len(testname) == 0:
testname = Test_Name()
datasource = Data_Source.objects.filter(
name=row[dataSet[0].index('Data_Source')])
testname.Name = row[dataSet[0].index('Result_Name')]
testname.Data_Source = datasource[0]
testname.save()
yield gen_message("<span style='color:green'>Insert " +
testname.Name + " " + str(testname.id) +
"</span>\n")
dataset = Data_Set()
datasource = Data_Source.objects.filter(
name=row[dataSet[0].index('Data_Source')])
testname = Test_Name.objects.filter(
Name=row[dataSet[0].index('Result_Name')])
dataset.Name = row[dataSet[0].index('Result_Name')]
dataset.Member_ID = row[dataSet[0].index('Member_ID')]
dataset.Result_Name = row[dataSet[0].index('Result_Name')]
dataset.Source = row[dataSet[0].index('Data_Source')]
dataset.Data_Source = datasource[0]
dataset.Test_Name = testname[0]
dataset.Result_Description = row[dataSet[0].index(
'Result_Description')]
dataset.Numeric_Result = row[dataSet[0].index(
'Numeric_Result')]
dataset.Patient_DOB = row[dataSet[0].index('Patient_DOB')]
dataset.Patient_Gender = row[dataSet[0].index(
'Patient_Gender')]
dataset.Date_Resulted = row[dataSet[0].index('Date_Resulted')]
dataset.save()
yield gen_message("<span style='color:green'>Insert " +
dataset.Name + " " + str(dataset.id) +
"</span> \n")
insertedNumber += 1
yield gen_message(
"..........................................................\n")
yield gen_message(
"<span style='color:orange'>..........................DONE............................</span>\n"
)
yield gen_message("<span style='color:orange'>Total Row Inserted " +
str(insertedNumber) + "</span> \n")
return HttpResponse('home')
def register_from_excel(request):
if request.method == "POST":
file_form = FileUploadForm(request.POST, request.FILES)
if file_form.is_valid():
excel_file = file_form.cleaned_data['choose_file']
if (str(excel_file)).split('.')[-1] == "xls":
# read the file and loop though to get the sheets available
file_data = xls_get(excel_file)
elif (str(excel_file)).split('.')[-1] == "xlsx":
file_data = xlsx_get(excel_file, column_limit=10)
failed_entries = []
saved_entries = 0
if len(file_data) > 1: # check to see the file has one sheet
msg = '\nError Bad Excel File....\nFile Contains One Sheet...!!'
messages.error(request, msg)
return redirect('accounts:view_members')
for data_obj in file_data: # loop through each sheet
sheet_obj = file_data[data_obj]
sheet_len = len(sheet_obj)
header_indexes = []
if sheet_len > 1: # we have data
for header in sheet_obj[0]: # get the headers
if header.lower() == "first name": # change all to lower case and map the indexes
header_indexes.append(sheet_obj[0].index(header))
elif header.lower() == 'middle name':
header_indexes.append(sheet_obj[0].index(header))
elif header.lower() == 'last name':
header_indexes.append(sheet_obj[0].index(header))
elif header.lower() == 'gender':
header_indexes.append(sheet_obj[0].index(header))
elif header.lower() == 'email':
header_indexes.append(sheet_obj[0].index(header))
elif header.lower() == 'residency':
header_indexes.append(sheet_obj[0].index(header))
elif header.lower() == 'phone number':
header_indexes.append(sheet_obj[0].index(header))
elif header.lower() == 'occupation':
header_indexes.append(sheet_obj[0].index(header))
elif header.lower() == 'citizenship':
header_indexes.append(sheet_obj[0].index(header))
elif header.lower() == 'marital status':
header_indexes.append(sheet_obj[0].index(header))
else:
sheet_obj[0].remove(header)
if len(header_indexes) != 10:
msg = '\nFile Contains Bad Header Format, Check File And Try Again'
messages.error(request, msg)
return redirect('accounts:view_members')
else:
i = 1
# loop trough each row
while i < sheet_len:
if len(sheet_obj[i]) != 0:
available_row = sheet_obj[i]
try:
gender = available_row[header_indexes[3]]
marital_status = available_row[header_indexes[9]]
if len(gender) == 1: # if gender is one character implement good variable
if gender.lower() == 'm':
gender = 'Male'
elif gender.lower() == 'f':
gender = 'Female'
else:
gender = 'Other'
if len(
marital_status) == 1: # if marital_status is one character implement a proper variable
if marital_status.lower() == 'm':
marital_status = 'Married'
elif marital_status.lower() == 'd':
marital_status = 'Divorced'
elif marital_status.lower() == 'w':
marital_status = 'Widowed'
else:
marital_status = 'Single'
ChurchMember.objects.create(
first_name=available_row[header_indexes[0]],
middle_name=available_row[header_indexes[1]],
last_name=available_row[header_indexes[2]],
gender=gender,
email=available_row[header_indexes[4]],
residency=available_row[header_indexes[5]],
phone_number=available_row[header_indexes[6]],
occupation=available_row[header_indexes[7]],
citizenship=available_row[header_indexes[8]],
marital_status=marital_status,
)
saved_entries += 1
except:
failed_entries.append(i)
i += 1
else: # no data
pass
if len(failed_entries) == 0:
msg = 'Success All Members Registered, Total: {}'.format(saved_entries)
messages.success(request, msg)
return redirect('accounts:view_members')
else:
msg = '\nRegitered Memebrs:{}, Failed:{}, RowsFailed:{}'.format(
saved_entries, len(failed_entries), [x for x in failed_entries]
)
messages.error(request, msg)
return redirect('accounts:view_members')
else:
messages.error(request, file_form.errors)
return redirect('accounts:view_members')
else:
file_form = FileUploadForm()
template = 'accounts/file_upload_form.html'
context = {
'form_file': file_form,
}
html_form = render_to_string(
template, context, request=request
)
return JsonResponse({"html_response": html_form})
def index(request):
print("oke")
if request.method == 'POST':
if len(request.FILES) != 0:
file = request.FILES['LB1File']
print(file)
#Get pkm_code
arr_pkmCode = xls_get(file,
start_column=4,
start_row=3,
row_limit=1)
list_pkmCode = list(arr_pkmCode.items())[0][1][0][0]
print(list_pkmCode)
split_pkmCode = list_pkmCode.split(" ")
pkmCode = split_pkmCode[0]
print(pkmCode)
#Get date
arr_date = xls_get(file, start_column=4, start_row=4, row_limit=1)
list_date = list(arr_date.items())[0][1][0][0]
print(list_date)
split_date = list_date.split(" ")
str_date = split_date[0]
date = datetime.strptime(str_date, '%d-%m-%Y').date()
print(date)
#Get code
if Indeks.objects.filter(kode_pkm=pkmCode,
tanggal=date).exists() == False:
print("create new indeks")
Indeks.objects.create(
kode_pkm=Puskesmas.objects.get(kode_pkm=pkmCode),
tanggal=date)
code = Indeks.objects.filter(
kode_pkm=pkmCode, tanggal=date).values('kode')[0]['kode']
print(code)
#Get list penyakit
obj_diseases = xls_get(file,
start_column=1,
column_limit=1,
start_row=14,
skip_empty_rows=True)
diseases = list(obj_diseases.items())[0][1]
#Get matriks kasus
obj_case = xls_get(file,
start_column=3,
column_limit=48,
start_row=14,
skip_empty_rows=True)
case = list(obj_case.items())[0][1]
#Get jumlah kasus
obj_jumlahKasus = xls_get(file,
start_column=51,
column_limit=6,
start_row=14,
skip_empty_rows=True)
sumCase = list(obj_jumlahKasus.items())[0][1]
print("identifikasi clear")
#Add data in database
if (Jumlah_Chapter.objects.filter(kode=code).exists() == False):
for idx, disease in enumerate(diseases, start=0):
print(idx)
print(disease[0])
if "." in disease[0]:
subkat = disease[0]
kat = ICD10_Subkategori.objects.filter(
subkat=subkat).values("kat")[0]['kat']
else:
kat = disease[0]
subch = ICD10_Kategori.objects.filter(
kat=kat).values("subchapter")[0]['subchapter']
ch = ICD10_Subchapter.objects.filter(
subchapter=subch).values("chapter")[0]['chapter']
print(kat)
print(subch)
print(ch)
j = 0
for i in range(24):
i += 1
k = i + j - 1
if "." in disease[0]:
Kasus.objects.create(
kode=Indeks.objects.get(kode=code),
icd_10=ICD10_Subkategori.objects.get(
subkat=subkat),
kat_pasien=Pasien.objects.get(kat_pasien=i),
kasus_baru=case[idx][k],
kasus_lama=case[idx][k + 2],
jumlah=case[idx][k] + case[idx][k + 2])
if Jumlah_Kategori.objects.filter(kode=code,
kat_pasien=i,
kat=kat).exists():
print("update kat")
Jumlah_Kategori.objects.filter(kode=code, kat_pasien=i, kat=kat)\
.update(
kasus_baru=F('kasus_baru') + case[idx][k],
kasus_lama=F('kasus_lama') + case[idx][k+2],
jumlah = F('jumlah') + case[idx][k] + case[idx][k+2]
)
else:
print("create new kat")
Jumlah_Kategori.objects.create(
kode=Indeks.objects.get(kode=code),
kat_pasien=Pasien.objects.get(kat_pasien=i),
kat=ICD10_Kategori.objects.get(kat=kat),
kasus_baru=case[idx][k],
kasus_lama=case[idx][k + 2],
jumlah=case[idx][k] + case[idx][k + 2])
if Jumlah_Subchapter.objects.filter(
kode=code, kat_pasien=i,
subchapter=subch).exists():
print("update subchapter")
Jumlah_Subchapter.objects.filter(kode=code, kat_pasien=i, subchapter=subch)\
.update(
kasus_baru=F('kasus_baru') + case[idx][k],
kasus_lama=F('kasus_lama') + case[idx][k+2],
jumlah = F('jumlah') +case[idx][k] + case[idx][k+2]
)
else:
print("create new subchapter")
Jumlah_Subchapter.objects.create(
kode=Indeks.objects.get(kode=code),
subchapter=ICD10_Subchapter.objects.get(
subchapter=subch),
kat_pasien=Pasien.objects.get(kat_pasien=i),
kasus_baru=case[idx][k],
kasus_lama=case[idx][k + 2],
jumlah=case[idx][k] + case[idx][k + 2])
if Jumlah_Chapter.objects.filter(kode=code,
kat_pasien=i,
chapter=ch).exists():
print("update chapter")
Jumlah_Chapter.objects.filter(kode=code, kat_pasien=i, chapter=ch)\
.update(
kasus_baru=F('kasus_baru') + case[idx][k],
kasus_lama=F('kasus_lama') + case[idx][k+2],
jumlah = F('jumlah') +case[idx][k] + case[idx][k+2]
)
else:
print("create new chapter")
Jumlah_Chapter.objects.create(
kode=Indeks.objects.get(kode=code),
chapter=ICD10_Chapter.objects.get(chapter=ch),
kat_pasien=Pasien.objects.get(kat_pasien=i),
kasus_baru=case[idx][k],
kasus_lama=case[idx][k + 2],
jumlah=case[idx][k] + case[idx][k + 2])
if i % 2 == 0:
j += 2
if "." in disease[0]:
print("create Jumlah_Kasus_Subkat")
Jumlah_Kasus_Subkat.objects.create(
kode=Indeks.objects.get(kode=code),
icd_10=ICD10_Subkategori.objects.get(
subkat=subkat),
jumlah_baru_l=sumCase[idx][0],
jumlah_baru_p=sumCase[idx][1],
jumlah_lama_l=sumCase[idx][2],
jumlah_lama_p=sumCase[idx][3],
jumlah=sumCase[idx][4],
gakin=sumCase[idx][5])
if Jumlah_Kasus_Kat.objects.filter(kode=code,
kat=kat).exists():
print("update Jumlah_Kasus_Kat")
Jumlah_Kasus_Kat.objects.filter(kode=code, kat=kat)\
.update(
jumlah_baru_l=F('jumlah_baru_l') + sumCase[idx][0],
jumlah_baru_p=F('jumlah_baru_p') + sumCase[idx][1],
jumlah_lama_l=F('jumlah_lama_l') + sumCase[idx][2],
jumlah_lama_p=F('jumlah_lama_p') + sumCase[idx][3],
jumlah = F('jumlah') + sumCase[idx][4],
gakin= F('gakin') + sumCase[idx][5]
)
else:
print("create new Jumlah_Kasus_Kat")
Jumlah_Kasus_Kat.objects.create(
kode=Indeks.objects.get(kode=code),
kat=ICD10_Kategori.objects.get(kat=kat),
jumlah_baru_l=sumCase[idx][0],
jumlah_baru_p=sumCase[idx][1],
jumlah_lama_l=sumCase[idx][2],
jumlah_lama_p=sumCase[idx][3],
jumlah=sumCase[idx][4],
gakin=sumCase[idx][5])
#Clustering
countPkm = Indeks.objects.filter(tanggal=date)\
.annotate(Count('kode_pkm', distinct=True))
if countPkm > 60:
funcClustering(date)
print("Done")
print(list_pkmCode)
print(date)
print(len(diseases))
print(len(diseases) * 24)
fs = FileSystemStorage()
filename = fs.save(file.name, file)
uploaded_file_url = fs.url(filename)
return redirect('index')
else:
return redirect('index')
elif request.method == 'GET':
penyakit_query = request.GET.get("Penyakit")
gender_query = request.GET.get('JenisKelamin')
umur_query = request.GET.get('Umur')
dateStart_query = request.GET.get('dateStart')
dateEnd_query = request.GET.get('dateEnd')
jenisKasus_query = request.GET.get('JenisKasus')
print(penyakit_query)
print(gender_query)
print(umur_query)
print(dateStart_query)
print(dateEnd_query)
print(jenisKasus_query)
if is_valid_queryparam(penyakit_query) and is_valid_queryparam(
gender_query
) and is_valid_queryparam(umur_query) and is_valid_queryparam(
dateStart_query) and is_valid_queryparam(
dateEnd_query) and is_valid_queryparam(jenisKasus_query):
if penyakit_query != "Semua Penyakit":
splitKodePenyakit = penyakit_query.split(":")
kodePenyakit = splitKodePenyakit[0]
if "." in kodePenyakit:
qs = Kasus.objects.select_related('kode__kode_pkm')\
.filter(icd_10=kodePenyakit)
else:
qs = Jumlah_Kategori.objects.select_related('kode__kode_pkm')\
.filter(kat=kodePenyakit)
#Clustering
qsClustering = Klaster_Penyakit.objects\
.select_related('subkat')\
.filter(
tanggal__gte=dateStart_query,
tanggal__lt=dateEnd_query,
subkat=kodePenyakit,
jenis_kelamin=gender_query
)\
.order_by('-llr')[:3]\
.values(
'subkat',
'subkat__nama_subkat',
'jenis_kelamin',
'tanggal',
'klaster_kode',
'klaster_nama',
'llr')
elif penyakit_query == "Semua Penyakit":
qs = Jumlah_Chapter.objects.select_related('kode__kode_pkm')
qsClustering = Klaster_Penyakit.objects\
.filter(
tanggal__gte=dateStart_query,
tanggal__lt=dateEnd_query,
jenis_kelamin=gender_query
)\
.order_by('-llr')[:3]\
.values(
'subkat',
'subkat__nama_subkat',
'jenis_kelamin',
'tanggal',
'klaster_kode',
'klaster_nama',
'llr')
if gender_query != "Semua Jenis":
qs = qs.filter(kat_pasien__jenis_kelamin__iexact=gender_query)
if umur_query != "Semua Umur":
qs = qs.filter(kat_pasien__umur__iexact=umur_query)
#Filter Periode
qs = qs.filter(kode__tanggal__gte=dateStart_query,
kode__tanggal__lt=dateEnd_query)
tempStartDate = datetime.strptime(dateStart_query,
'%Y-%m-%d').date()
startPeriode = tempStartDate
tempEndDate = datetime.strptime(dateEnd_query, '%Y-%m-%d').date()
endPeriode = last_day_of_month(tempEndDate)
#Filter Jenis Kasus
if jenisKasus_query == "Kasus Baru":
kasusStatus = 'kasus_baru'
qsChartKasus = qs.aggregate(Kasus_Baru=Sum(kasusStatus))
elif jenisKasus_query == "Kasus Lama":
kasusStatus = 'kasus_lama'
qsChartKasus = qs.aggregate(Kasus_Lama=Sum(kasusStatus))
else:
kasusStatus = 'jumlah'
qsChartKasus = qs.aggregate(Kasus_Baru=Sum('kasus_baru'),
Kasus_Lama=Sum('kasus_lama'))
qsPkm = qs.values(
'kode__kode_pkm',
'kode__kode_pkm__nama_pkm').annotate(kasus=Sum(kasusStatus))
qsKec = qs.values('kode__kode_pkm__kode_kec',
'kode__kode_pkm__kode_kec__nama_kec').annotate(
kasus=Sum(kasusStatus))
qsChartUmur = qs.values('kat_pasien__umur').annotate(
kasus=Sum(kasusStatus)).order_by('-kasus')[:10]
qsChartGender = qs.values('kat_pasien__jenis_kelamin').annotate(
kasus=Sum(kasusStatus))
qsChartDate = qs.values('kode__tanggal').annotate(
kasus=Sum(kasusStatus)).order_by('-kode__tanggal')[:10]
if penyakit_query == "Semua Penyakit":
qsChartPenyakit = qs.values('chapter__nama_chapter').annotate(
kasus=Sum(kasusStatus)).order_by('-kasus')[:10]
elif "." in kodePenyakit:
qsChartPenyakit = qs.values('icd_10__nama_subkat').annotate(
kasus=Sum(kasusStatus)).order_by('-kasus')[:10]
else:
qsChartPenyakit = qs.values('kat__nama_kat').annotate(
kasus=Sum(kasusStatus)).order_by('-kasus')[:10]
else:
#Tampilan Default
#Get last date in database
startPeriode = Indeks.objects.values('tanggal').order_by(
'-tanggal')[0]['tanggal']
print(startPeriode)
#get last date in the last month
endPeriode = last_day_of_month(startPeriode)
qs = Jumlah_Chapter.objects.select_related(
'kode__kode_pkm').filter(kode__tanggal=startPeriode)
qsPkm = qs.values('kode__kode_pkm', 'kode__kode_pkm__nama_pkm')\
.annotate(kasus = Sum('jumlah'))
qsKec = qs.values('kode__kode_pkm__kode_kec', 'kode__kode_pkm__kode_kec__nama_kec')\
.annotate(kasus = Sum('jumlah'))
qsChartPenyakit = qs.values('chapter__nama_chapter')\
.annotate(kasus = Sum('jumlah'))\
.order_by('-kasus')[:10]
qsChartUmur = qs.values('kat_pasien__umur').annotate(
kasus=Sum('jumlah')).order_by('-kasus')[:10]
qsChartGender = qs.values('kat_pasien__jenis_kelamin').annotate(
kasus=Sum('jumlah'))
qsChartDate = qs.values('kode__tanggal').annotate(
kasus=Sum('jumlah')).order_by('-kasus')[:10]
qsClustering = Klaster_Penyakit.objects.select_related('subkat')\
.order_by('-llr')[:3]\
.values(
'subkat',
'subkat__nama_subkat',
'jenis_kelamin',
'tanggal',
'klaster_kode',
'klaster_nama',
'llr')
qsChartKasus = qs.aggregate(Kasus_Baru=Sum('kasus_baru'),
Kasus_Lama=Sum('kasus_lama'))
qsChartPkm = qsPkm.order_by('-kasus')[:10]
qsChartKec = qsKec.order_by('-kasus')[:10]
distNormPkm = qsPkm.aggregate(Max('kasus'), Min('kasus'))
distNormKec = qsKec.aggregate(Max('kasus'), Min('kasus'))
startPeriode = startPeriode.strftime('%d/%m/%y')
endPeriode = endPeriode.strftime('%d/%m/%y')
#Informasi legenda di map
query = {
'penyakit_query': penyakit_query,
'gender_query': gender_query,
'umur_query': umur_query,
'jenisKasus_query': jenisKasus_query,
'startPeriode': startPeriode,
'endPeriode': endPeriode
}
data = {
'areaPkm': list(qsPkm),
'areaKec': list(qsKec),
'statPkm': distNormPkm,
'statKec': distNormKec,
'chartPkm': list(qsChartPkm),
'chartKec': list(qsChartKec),
'chartPenyakit': list(qsChartPenyakit),
'chartGender': list(qsChartGender),
'chartUmur': list(qsChartUmur),
'chartPeriode': list(qsChartDate),
'chartKasus': qsChartKasus,
'qsClustering': list(qsClustering),
'qs': query
}
return render(request, 'app_BHGIS/indexV2.html', data)
return render(request, 'app_BHGIS/indexV2.html')
