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