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)