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