def adminrep(request):
uid = request.session.get('uid')
a = 1
rep_tbl_msg = ''
empty_tbl_set = [{"sel" : ""}]
admin_rep_table = emptyTable(empty_tbl_set)
adminrep_form = adminrepForm()
cursor = connection.cursor()
cursor.execute("""SELECT first_name||' '||last_name AS full_name, (SELECT role_name
FROM user_roles
WHERE role_id = ud.role_id) role_name
FROM Users_Details ud WHERE user_id = %s AND 1=%s""",
(uid,
a))
usr = cursor.fetchone()
full_name = usr[0]
role_name = usr[1]
if request.method == 'GET':
if 'adminrepselect' in request.GET:
adminrep_form = adminrepForm(request.GET)
if adminrep_form.is_valid():
admin_rep_id = adminrep_form.cleaned_data["admin_rep_id"]
if admin_rep_id == '1':
admin_rep_tbl = UsersDetails.objects.raw("""SELECT ud.user_id, ud.username,
ud.first_name
||' '
||ud.last_name full_name,
NVL(ud.user_brokerage_pct, ur.brokerage_pct) brokerage_pct,
ur.role_name,
ud.gender,
ud.age
FROM users_details ud,
user_roles ur
WHERE ud.role_id = ur.role_id
AND ud.creation_date BETWEEN SYSDATE-1 AND SYSDATE""")
admin_rep_table = adminrep1Table(admin_rep_tbl)
if len(list(admin_rep_tbl)) == 0:
rep_tbl_msg = 'There are no users added in the past 24 hrs.'
elif admin_rep_id == '2':
admin_rep_tbl = UsersDetails.objects.raw("""SELECT user_id, username,
first_name
||' '
||last_name full_name,
(SELECT role_name
FROM user_roles
WHERE role_id = ud.role_id) role_name
FROM users_details ud
WHERE current_login_time IS NOT NULL
AND user_id <> %s""",[uid])
admin_rep_table = adminrep2Table(admin_rep_tbl)
if len(list(admin_rep_tbl)) == 0:
rep_tbl_msg = 'There are no users active currently.'
elif admin_rep_id == '3':
admin_rep_tbl = UsersDetails.objects.raw("""SELECT ud.user_id, ud.first_name
||' '
||ud.last_name full_name,
ca.initial_bal total_credited_amount,
ROUND(ca.current_bal, 2) current_balance,
NVL(
(SELECT COUNT(1)
FROM customer_transactions ct
WHERE ct.user_id = ud.user_id
AND CT.TRANS_DATE BETWEEN SYSDATE - 7 AND SYSDATE
), 0) total_number_of_transactions,
NVL(
(SELECT ROUND(SUM(ct2.amount), 2)
FROM customer_transactions ct2
WHERE ct2.user_id = ud.user_id
AND ct2.trans_type = 'BUY'
AND CT2.TRANS_DATE BETWEEN SYSDATE - 7 AND SYSDATE
), 0) total_buy,
NVL(
(SELECT ROUND(SUM(ct2.amount), 2)
FROM customer_transactions ct2
WHERE ct2.user_id = ud.user_id
AND ct2.trans_type = 'SELL'
AND CT2.TRANS_DATE BETWEEN SYSDATE - 7 AND SYSDATE
), 0) total_sell
FROM users_details ud,
customer_accounts ca,
user_roles ur
WHERE ud.user_id = ca.user_id
AND ur.role_id = ud.role_id
AND ur.role_name <> 'ADMIN';""")
admin_rep_table = adminrep3Table(admin_rep_tbl)
if len(list(admin_rep_tbl)) == 0:
rep_tbl_msg = 'There are no users with non admin priviledges for you to moniter.'
elif admin_rep_id == '4':
admin_rep_tbl = CompanyDetails.objects.raw("""SELECT cd.company_id,
CD.COMP_NAME company_name,
sd.share_code,
SD.CURRENT_PRICE current_value,
NVL(
(SELECT SUM(ct1.no_of_shares)
FROM customer_transactions ct1
WHERE CT1.TRANS_TYPE = 'BUY'
AND SD.SHARE_ID = CT1.SHARE_ID
AND CT1.TRANS_DATE BETWEEN SYSDATE - 7 AND SYSDATE
),0) volume_of_buy_orders,
NVL(
(SELECT SUM(ct2.no_of_shares)
FROM customer_transactions ct2
WHERE ct2.trans_type = 'SELL'
AND sd.share_id = ct2.share_id
AND CT2.TRANS_DATE BETWEEN SYSDATE - 7 AND SYSDATE
),0) volume_of_sell_orders
FROM company_details cd,
shares_details sd
WHERE CD.COMPANY_ID = SD.COMPANY_ID""")
admin_rep_table = adminrep4Table(admin_rep_tbl)
if len(list(admin_rep_tbl)) == 0:
rep_tbl_msg = 'There are no companies with active transactions in the past week.'
RequestConfig(request, paginate={"per_page": 5}).configure(admin_rep_table)
cursor.close()
return render(request, 'finapp/adminrep.html', {'full_name' : full_name,
'role_name' : role_name,
'adminrep_form' : adminrep_form,
'rep_tbl_msg' : rep_tbl_msg,
'admin_rep_table' : admin_rep_table})
def reports(request):
a = 1
uid = request.session.get('uid')
cursor = connection.cursor()
cursor.execute("""SELECT first_name||' '||last_name AS full_name, (SELECT role_name
FROM user_roles
WHERE role_id = ud.role_id) role_name
FROM Users_Details ud WHERE user_id = %s AND 1=%s""",
(uid,
a))
usr = cursor.fetchone()
full_name = usr[0]
role_name = usr[1]
cursor.close()
form = reportsearchForm()
rep_form = repselectForm()
res_dtls = SharesHistory.objects.raw("""SELECT hist_id, '' pivot_point, '' support1, '' support2,
'' support3, '' resistance1, '' resistance2, '' resistance3
FROM shares_history
WHERE 1=2""")
empty_tbl_set = [{"sel" : ""}]
table = emptyTable(empty_tbl_set)
msg = ''
tbl_msg = ''
if request.method == 'POST':
if 'repsearch' in request.POST:
form = reportsearchForm(request.POST)
if form.is_valid():
share_id = form.cleaned_data["share_id"]
time_frame = form.cleaned_data["time_frame"]
res_dtls = SharesHistory.objects.raw("""SELECT temp3.hist_id, ROUND(pivot_point,2) pivot_point,
ROUND(pivot_point - (0.382*(high_val - low_val)), 2) support1,
ROUND(pivot_point - (0.618*(high_val - low_val)), 2) support2,
ROUND(pivot_point - (1*(high_val - low_val)), 2) support3,
ROUND(pivot_point + (0.382*(high_val - low_val)), 2) resistance1,
ROUND(pivot_point + (0.618*(high_val - low_val)), 2) resistance2,
ROUND(pivot_point + (1*(high_val - low_val)), 2) resistance3
FROM
(SELECT temp2.high_val,
temp2.low_val,
sh2.close_value,
(temp2.high_val+temp2.low_val+sh2.close_value)/3 pivot_point,
sh2.hist_id
FROM
(SELECT MAX(NVL(h_day_high,0)) high_val,
MIN(NVL(h_day_low,0)) low_val,
temp.last_traded_date, sh.share_id
FROM shares_history sh,
(SELECT MAX(sh1.date_traded) last_traded_date,
sh1.share_id
FROM shares_history sh1
WHERE sh1.share_id = %s
GROUP BY sh1.share_id
) temp
WHERE sh.share_id = temp.share_id
AND sh.date_traded BETWEEN
(temp.last_traded_date -
DECODE(%s,
'D', 1,
'W', 7,
'M', 30,
'Y', 365)
) AND temp.last_traded_date
GROUP BY temp.last_traded_date, sh.share_id
) temp2,
shares_history sh2
WHERE sh2.date_traded = temp2.last_traded_date
AND sh2.share_id = temp2.share_id) temp3""",
[share_id, time_frame])[0]
elif request.method == 'GET':
if'repselect' in request.GET:
rep_form = repselectForm(request.GET)
if rep_form.is_valid():
rep_id = rep_form.cleaned_data["rep_id"]
time_frame = rep_form.cleaned_data["time_frame"]
if rep_id == '1':
res_tbl = SharesHistory.objects.raw("""SELECT cd.comp_name,
sd.share_code,
seg.seg_name,
sd.current_price,
ROUND(100*(sh3.close_value-sh2.open_value)/sh2.open_value, 2) growth_pct,
sh3.hist_id
FROM company_details cd,
shares_details sd,
segment_details seg,
shares_history sh2,
shares_history sh3,
(SELECT MIN(sh.date_traded) first_traded_date,
temp.last_traded_date,
sh.share_id
FROM shares_history sh,
(SELECT MAX(sh1.date_traded) last_traded_date,
sh1.share_id
FROM shares_history sh1
GROUP BY sh1.share_id
) temp
WHERE sh.share_id = temp.share_id
AND sh.date_traded >
(temp.last_traded_date -
DECODE(%s,
'D', 1,
'W', 7,
'M', 30,
'Y', 365
)
)
GROUP BY temp.last_traded_date, sh.share_id
) temp1
WHERE sh2.share_id = temp1.share_id
AND sh2.share_id = sh3.share_id
AND sh2.date_traded = temp1.first_traded_date
AND sh3.date_traded = temp1.last_traded_date
AND sh2.share_id = sd.share_id
AND sd.company_id = cd.company_id
AND cd.segment_id = seg.seg_id
ORDER BY ROUND(100*(sh3.close_value-sh2.open_value)/sh2.open_value, 2) DESC""",
[time_frame])
table = resTable(res_tbl)
elif rep_id == '2':
res_tbl = SharesDetails.objects.raw("""SELECT cd.comp_name,
sd.share_code,
seg.seg_name,
sd.current_price,
SUM(sh2.volume_traded) total_volume,
sd.share_id
FROM company_details cd,
shares_details sd,
segment_details seg,
shares_history sh2,
(SELECT MIN(sh.date_traded) first_traded_date,
temp.last_traded_date,
sh.share_id
FROM shares_history sh,
(SELECT MAX(sh1.date_traded) last_traded_date,
sh1.share_id
FROM shares_history sh1
GROUP BY sh1.share_id
) temp
WHERE sh.share_id = temp.share_id
AND sh.date_traded >
(temp.last_traded_date - DECODE(%s,
'D', 1,
'W', 7,
'M', 30,
'Y', 365)
)
GROUP BY temp.last_traded_date,
sh.share_id
) temp1
WHERE sh2.share_id = temp1.share_id
AND sh2.date_traded BETWEEN temp1.first_traded_date AND temp1.last_traded_date
AND sh2.share_id = sd.share_id
AND sd.company_id = cd.company_id
AND cd.segment_id = seg.seg_id
GROUP BY cd.comp_name,
sd.share_code,
seg.seg_name,
sd.current_price,
sd.share_id
ORDER BY SUM(sh2.volume_traded) DESC""",
[time_frame])
table = res2Table(res_tbl)
elif rep_id == '3':
res_tbl = SegmentDetails.objects.raw("""SELECT seg.seg_name,
ROUND(AVG(
ROUND(
100*(sh3.close_value-sh2.open_value)
/sh2.open_value
, 2)
), 2) change_pct,
seg.seg_id
FROM company_details cd,
shares_details sd,
segment_details seg,
shares_history sh2,
shares_history sh3,
(SELECT MIN(sh.date_traded) first_traded_date,
temp.last_traded_date,
sh.share_id
FROM shares_history sh,
(SELECT MAX(sh1.date_traded) last_traded_date,
sh1.share_id
FROM shares_history sh1
GROUP BY sh1.share_id
) temp
WHERE sh.share_id = temp.share_id
AND sh.date_traded >
(temp.last_traded_date -
DECODE(%s,
'D', 1,
'W', 7,
'M', 30,
'Y', 365
)
)
GROUP BY temp.last_traded_date, sh.share_id
) temp1
WHERE sh2.share_id = temp1.share_id
AND sh2.share_id = sh3.share_id
AND sh2.date_traded = temp1.first_traded_date
AND sh3.date_traded = temp1.last_traded_date
AND sh2.share_id = sd.share_id
AND sd.company_id = cd.company_id
AND cd.segment_id = seg.seg_id
GROUP BY seg.seg_name, seg.seg_id
ORDER BY AVG(
ROUND(
100*(sh3.close_value-sh2.open_value)
/sh2.open_value, 2)
) DESC""",
[time_frame])
table = res3Table(res_tbl)
table.paginate(page=request.GET.get('page', 1), per_page=5)
if len(list(res_tbl)) == 0:
tbl_msg = 'There are no records for given range.'
return render(request, 'finapp/reports.html', {'form' : form,
'full_name' : full_name,
'role_name' : role_name,
'msg' : msg,
'res_dtls' : res_dtls,
'rep_form' : rep_form,
'table' : table,
'tbl_msg' : tbl_msg})
