def aws_list_data2(request, id): context = {} #1 get aws details aws_details = AwsUserAccessDetails.objects.filter(user_id=request.user) aws_details = aws_details.last() host = aws_details.host database = aws_details.database password = aws_details.aws_password user_id = aws_details.aws_user_name #2 set up instance aws_database_object = PostgresDB(host, database, user_id, password) aws_site_object = AwsUploadDetails.objects.get(id=id) table_name = aws_site_object.table_name context['table_name'] = table_name data = aws_database_object.show_all_data(table_name) data_dic = {} context['data2'] = data for i in data: data_dic[i[0]] = i[1] context['data'] = data_dic if request.method == 'POST': for key in data_dic: new_item = request.POST.get(key) new_item = float(new_item) origional_item = data_dic[key] new_list = [origional_item, new_item] data_dic[key] = new_list for key in data_dic.keys(): if data_dic[key][0] != data_dic[key][1]: aws_database_object.update_fx_table_sql(table_name, key, 'av_fx_rate', data_dic[key][1], 'currency' ) return redirect('aws_menu') return render(request,"fileprocess/aws_listing2.html", context)
def aws_upload_csv_create(request): context = {} form = AwsDatabaseUpload() context['form'] = form #1 get aws details aws_details = AwsUserAccessDetails.objects.filter(user_id=request.user) aws_details = aws_details.last() host = aws_details.host database = aws_details.database password = aws_details.aws_password user_id = aws_details.aws_user_name #2 set up instance aws_database_object = PostgresDB(host, database, user_id, password) #3 get the file if request.method == 'POST': table_name = request.POST.get('table_name') csv_file = request.FILES['file'] #change to excel csv_file_df = pandas.read_excel(csv_file) aws_database_object.df_create_update_database(csv_file_df, table_name) aws_site_object = AwsUploadDetails() aws_site_object.user_id = request.user aws_site_object.table_name = table_name aws_site_object.save() return redirect('aws_menu') return render(request, "fileprocess/aws_database_upload.html", context)
def aws_map_query2(request): context = {} form = AwsMapQueryForm2(request.user) context['form'] = form if request.method == 'POST': data_table = request.POST.get('data_table') aws_site_object = AwsUploadDetails.objects.get(id=data_table) data_table_name = aws_site_object.table_name data_table1 = request.POST.get('data_table1') aws_site_object = AwsUploadDetails.objects.get(id=data_table1) data_table_name1 = aws_site_object.table_name mapping_table = request.POST.get('mapping_table') aws_site_object = AwsUploadDetails.objects.get(id=mapping_table) mapping_table_name = aws_site_object.table_name fx_table = request.POST.get('fx_table') aws_site_object = AwsUploadDetails.objects.get(id=fx_table) fx_table_name = aws_site_object.table_name aws_details = AwsUserAccessDetails.objects.filter(user_id=request.user) aws_details = aws_details.last() host = aws_details.host database = aws_details.database password = aws_details.aws_password user = aws_details.aws_user_name #2 set up instance aws_database_object = PostgresDB(host, database, user, password) #run query on above table_list data_table = aws_database_object.run_mappping_query(data_table_name, mapping_table_name, fx_table_name) data_graph1 = aws_database_object.run_mappping_query_graph(data_table_name, mapping_table_name, fx_table_name) data_graph2 = aws_database_object.run_mappping_query_graph(data_table_name1, mapping_table_name, fx_table_name) data2 = data_graph1.groupby('class').sum()['amount'] data1 = data_graph2.groupby('class').sum()['amount'] versions = ['v1', 'v2'] x = [(c, v) for c in [i for i in data1.index] for v in versions] counts = [] for a,b in zip(data1.values, data2.values): counts.append(a) counts.append(b) counts = tuple(counts) source = ColumnDataSource(data=dict(x=x, counts=counts)) p = figure(x_range=FactorRange(*x), plot_height=350, title="Comparison",) p.vbar(x='x', top='counts', width=0.9, source=source) p.y_range.start = 0 p.x_range.range_padding = 0.1 p.xaxis.major_label_orientation = 1 p.xgrid.grid_line_color = None html_graph = file_html(p, CDN, "Data") context['html_graph'] = html_graph data_table = data_table.replace("""<table border="1" class="dataframe">""","""<table class="table table-hover">""").replace("""<thead>""", """<thead class="thead-inverse">""" ) context['data_table'] = data_table return render(request, "fileprocess/aws_query_output.html", context) return render(request, "fileprocess/aws_query_output.html", context)
def aws_delete_table(request, id): context = {} #1 get aws details aws_details = AwsUserAccessDetails.objects.filter(user_id=request.user) aws_details = aws_details.last() host = aws_details.host database = aws_details.database password = aws_details.aws_password user = aws_details.aws_user_name #2 set up instance aws_database_object = PostgresDB(host, database, user, password) aws_site_object = AwsUploadDetails.objects.get(id=id) table_name = aws_site_object.table_name aws_database_object.execute_drop_table(table_name) aws_site_object.delete() return redirect('aws_menu')
def aws_list_data3(request, id): context = {} #1 get aws details aws_details = AwsUserAccessDetails.objects.filter(user_id=request.user) aws_details = aws_details.last() host = aws_details.host database = aws_details.database password = aws_details.aws_password user_id = aws_details.aws_user_name #2 set up instance aws_database_object = PostgresDB(host, database, user_id, password) aws_site_object = AwsUploadDetails.objects.get(id=id) table_name = aws_site_object.table_name context['table_name'] = table_name data_html_edit = aws_database_object.data_to_html_table_edit(table_name) data = aws_database_object.get_plain_data(table_name)['data'] di_data = {} for i in data: di_data[i[0]] = i[1] context['data_html_edit'] = data_html_edit if request.method =='POST': for key in di_data.keys(): updated_value = request.POST.get(key) updated_value = float(updated_value) origional_value = di_data.get(key) origional_value = float(origional_value) if origional_value == updated_value: pass else: print('to be updated') aws_database_object.update_fx_table_sql(table_name, key, 'av_fx_rate', updated_value, 'currency') data_html_edit = aws_database_object.data_to_html_table_edit(table_name) context['data_html_edit'] = data_html_edit return render(request,"fileprocess/aws_listing3.html", context) return render(request,"fileprocess/aws_listing3.html", context)
def aws_list_data(request, id): context = {} #1 get aws details aws_details = AwsUserAccessDetails.objects.filter(user_id=request.user) aws_details = aws_details.last() host = aws_details.host database = aws_details.database password = aws_details.aws_password user_id = aws_details.aws_user_name #2 set up instance aws_database_object = PostgresDB(host, database, user_id, password) aws_site_object = AwsUploadDetails.objects.get(id=id) table_name = aws_site_object.table_name context['table_name'] = table_name data_table = aws_database_object.show_all_data_in_html(table_name) data_table = data_table.replace("""<table border="1" class="dataframe">""","""<table class="table table-hover">""").replace("""<thead>""", """<thead class="thead-inverse">""" ) context['data_table'] = data_table return render(request,"fileprocess/aws_listing.html", context)
def aws_download_table(request, id): #1 get aws details aws_details = AwsUserAccessDetails.objects.filter(user_id=request.user) aws_details = aws_details.last() host = aws_details.host database = aws_details.database password = aws_details.aws_password user = aws_details.aws_user_name #2 set up instance aws_database_object = PostgresDB(host, database, user, password) aws_site_object = AwsUploadDetails.objects.get(id=id) table_name = aws_site_object.table_name sql_data = aws_database_object.show_all_data(table_name) df = pandas.DataFrame(sql_data) file = df.to_csv() response = HttpResponse(file, content_type='text/csv') response['Content-Disposition'] = 'attachment; filename="sql_table_download.csv"' return response
def aws_list_data4(request, id): context = {} #1 get aws details aws_details = AwsUserAccessDetails.objects.filter(user_id=request.user) aws_details = aws_details.last() host = aws_details.host database = aws_details.database password = aws_details.aws_password user_id = aws_details.aws_user_name #2 set up instance aws_database_object = PostgresDB(host, database, user_id, password) aws_site_object = AwsUploadDetails.objects.get(id=id) table_name = aws_site_object.table_name context['table_name'] = table_name data_html_edit = aws_database_object.data_to_html_table_editFSOnly(table_name) data = aws_database_object.get_plain_data(table_name)['data'] di_data = {} for i in data: di_data[i[0]] = i[1] context['data_html_edit'] = data_html_edit return render(request,"fileprocess/aws_listing4.html", context)
def aws_map_query(request): context = {} form = AwsMapQueryForm(request.user) context['form'] = form if request.method == 'POST': data_table = request.POST.get('data_table') aws_site_object = AwsUploadDetails.objects.get(id=data_table) data_table_name = aws_site_object.table_name mapping_table = request.POST.get('mapping_table') aws_site_object = AwsUploadDetails.objects.get(id=mapping_table) mapping_table_name = aws_site_object.table_name fx_table = request.POST.get('fx_table') aws_site_object = AwsUploadDetails.objects.get(id=fx_table) fx_table_name = aws_site_object.table_name aws_details = AwsUserAccessDetails.objects.filter(user_id=request.user) aws_details = aws_details.last() host = aws_details.host database = aws_details.database password = aws_details.aws_password user = aws_details.aws_user_name #2 set up instance aws_database_object = PostgresDB(host, database, user, password) #run query on above table_list data_table = aws_database_object.run_mappping_query(data_table_name, mapping_table_name, fx_table_name) data_graph = aws_database_object.run_mappping_query_graph(data_table_name, mapping_table_name, fx_table_name) data_graph = data_graph.groupby('nominal').sum()['amount'] p = figure(plot_width=600, plot_height=400) p.vbar(x=[data_graph.index[i] for i in range(0,len(data_graph))], width=0.5, bottom=0,top=data_graph.values, color="navy") html_graph = file_html(p, CDN, "Data") context['html_graph'] = html_graph data_table = data_table.replace("""<table border="1" class="dataframe">""","""<table class="table table-hover">""").replace("""<thead>""", """<thead class="thead-inverse">""" ) context['data_table'] = data_table return render(request, "fileprocess/aws_query_output.html", context) return render(request, "fileprocess/aws_query_output.html", context)
def aws_query_outputJ(request): aws_database_object = None context = {} context['stage'] = 'not_run' list_doc_normal = AwsUploadDetails.objects.filter(user_id=request.user) list_doc_json = serializers.serialize('json', list_doc_normal) form_list = {"f1":"Data table", "f2":"Mapping Table", "f3":"FX Table"} form_list_json = json.dumps(form_list) context["form_list_json"] = form_list_json context["list_doc_json"] = list_doc_json if request.method == 'POST' and "request_data" in request.POST and "mapping_2" not in request.POST: print(request.POST) global data_table_name data_table_name = request.POST.get("Data_table") global mapping_table_name mapping_table_name = request.POST.get('Mapping_Table') global fx_table_name fx_table_name = request.POST.get('FX_Table') aws_details = AwsUserAccessDetails.objects.filter(user_id=request.user) aws_details = aws_details.last() host = aws_details.host database = aws_details.database password = aws_details.aws_password user = aws_details.aws_user_name aws_database_object = PostgresDB(host, database, user, password) global data_output data_output = aws_database_object.run_mappping_query_fetchall(data_table_name, mapping_table_name, fx_table_name) global data_table data_table = aws_database_object.run_mappping_query(data_table_name, mapping_table_name, fx_table_name) data_table = data_table.replace("""<table border="1" class="dataframe">""","""<table class="table table-hover">""").replace("""<thead>""", """<thead class="thead-inverse">""" ) context['data_table'] = data_table context['stage'] = 'pushed' global data_graph data_graph = aws_database_object.run_mappping_query_graph(data_table_name, mapping_table_name, fx_table_name) data_graph = data_graph.groupby('nominal').sum()['amount'] p = figure(plot_width=600, plot_height=400) p.vbar(x=[data_graph.index[i] for i in range(0,len(data_graph))], width=0.5, bottom=0,top=data_graph.values, color="navy") html_graph = file_html(p, CDN, "Data") context['html_graph'] = html_graph if request.method == 'POST' and "download_request" in request.POST: df = pandas.DataFrame(data_output) file = df.to_csv() response = HttpResponse(file, content_type='text/csv') response['Content-Disposition'] = 'attachment; filename="financial_calc_output.csv"' return response if request.method == 'POST' and "mapping_2" in request.POST: aws_details = AwsUserAccessDetails.objects.filter(user_id=request.user) aws_details = aws_details.last() host = aws_details.host database = aws_details.database password = aws_details.aws_password user = aws_details.aws_user_name aws_database_object = PostgresDB(host, database, user, password) data_table_name = request.POST.get("Data_table") mapping_table_name = request.POST.get('Mapping_Table') fx_table_name = request.POST.get('FX_Table') data_table_name1 = request.POST.get('mapping_2') data_graph1 = aws_database_object.run_mappping_query_graph(data_table_name, mapping_table_name, fx_table_name) data_graph2 = aws_database_object.run_mappping_query_graph(data_table_name1, mapping_table_name, fx_table_name) data2 = data_graph1.groupby('class').sum()['amount'] data1 = data_graph2.groupby('class').sum()['amount'] versions = ['v1', 'v2'] x = [(c, v) for c in [i for i in data1.index] for v in versions] counts = [] for a,b in zip(data1.values, data2.values): counts.append(a) counts.append(b) counts = tuple(counts) source = ColumnDataSource(data=dict(x=x, counts=counts)) p = figure(x_range=FactorRange(*x), plot_height=350, title="Comparison",) p.vbar(x='x', top='counts', width=0.9, source=source) p.y_range.start = 0 p.x_range.range_padding = 0.1 p.xaxis.major_label_orientation = 1 p.xgrid.grid_line_color = None html_graph = file_html(p, CDN, "Data") context['html_graph'] = html_graph # form = AwsMapQueryForm2(request.user) # context['form'] = form # if request.method == 'POST': # data_table = request.POST.get('data_table') # aws_site_object = AwsUploadDetails.objects.get(id=data_table) # data_table_name = aws_site_object.table_name # data_table1 = request.POST.get('data_table1') # aws_site_object = AwsUploadDetails.objects.get(id=data_table1) # data_table_name1 = aws_site_object.table_name # mapping_table = request.POST.get('mapping_table') # aws_site_object = AwsUploadDetails.objects.get(id=mapping_table) # mapping_table_name = aws_site_object.table_name # fx_table = request.POST.get('fx_table') # aws_site_object = AwsUploadDetails.objects.get(id=fx_table) # fx_table_name = aws_site_object.table_name # # aws_details = AwsUserAccessDetails.objects.filter(user_id=request.user) # aws_details = aws_details.last() # host = aws_details.host # database = aws_details.database # password = aws_details.aws_password # user = aws_details.aws_user_name # #2 set up instance # aws_database_object = PostgresDB(host, database, user, password) # #run query on above table_list # data_table = aws_database_object.run_mappping_query(data_table_name, mapping_table_name, fx_table_name) # data_graph1 = aws_database_object.run_mappping_query_graph(data_table_name, mapping_table_name, fx_table_name) # data_graph2 = aws_database_object.run_mappping_query_graph(data_table_name1, mapping_table_name, fx_table_name) # data2 = data_graph1.groupby('class').sum()['amount'] # data1 = data_graph2.groupby('class').sum()['amount'] # versions = ['v1', 'v2'] # x = [(c, v) for c in [i for i in data1.index] for v in versions] # counts = [] # for a,b in zip(data1.values, data2.values): # counts.append(a) # counts.append(b) # counts = tuple(counts) # source = ColumnDataSource(data=dict(x=x, counts=counts)) # p = figure(x_range=FactorRange(*x), plot_height=350, title="Comparison",) # p.vbar(x='x', top='counts', width=0.9, source=source) # p.y_range.start = 0 # p.x_range.range_padding = 0.1 # p.xaxis.major_label_orientation = 1 # p.xgrid.grid_line_color = None # html_graph = file_html(p, CDN, "Data") # context['html_graph'] = html_graph # data_table = data_table.replace("""<table border="1" class="dataframe">""","""<table class="table table-hover">""").replace("""<thead>""", """<thead class="thead-inverse">""" ) # context['data_table'] = data_table # return render(request, "fileprocess/aws_query_output.html", context) return render(request, "fileprocess/aws_query_outputJ.html", context)