from django.shortcuts import render, redirect from django.http import HttpResponse import mysql.connector from django.views.generic import ListView from django.contrib.auth import authenticate, login, logout from django.http import HttpResponseRedirect from django.contrib import messages from django.contrib.auth.decorators import login_required from datetime import date, datetime, timedelta import copy from icecream import ic from .models import Exclusion from .tables import ExclusionTable from django_tables2 import SingleTableView import math from .decorators import unauthenticated_user, allowed_users, admin_only from django.conf import settings conn = mysql.connector.connect( user=settings.DB_USER, password=settings.DB_PASSWORD, host=settings.DB_HOST, database=settings.DB_NAME, ) cursor = conn.cursor(dictionary=True) def first_day_previous_quarter(): today = datetime.today() current_quarter = math.ceil(today.month / 3) previous_quarter = current_quarter - 1 if previous_quarter == 0: year = today.year - 1 month = 10 # Start of Q4 else: year = today.year month = (previous_quarter - 1) * 3 + 1 # 1st month of the quarter first_day = datetime(year, month, 1) return first_day.strftime("%Y%m%d") def last_day_previous_quarter(): today = datetime.today() current_quarter = math.ceil(today.month / 3) previous_quarter = current_quarter - 1 # ic(previous_quarter) if previous_quarter == 0: year = today.year - 1 month = 12 # End of Q4 next_year = today.year first_month = 1 else: year = today.year month = previous_quarter * 3 # Last month of the quarter next_year = today.year first_month = month + 1 last_day = datetime(next_year, first_month, 1) + timedelta(days=-1) # ic(last_day.strftime("%Y%m%d")) return last_day.strftime("%Y%m%d") def get_quarters(date): # Determine the current quarter year = date.year month = date.month current_quarter = (month - 2) // 3 + 1 # Create a list to store the quarters quarters = [] # Calculate the current and previous two quarters for i in range(3): quarter = current_quarter - i if quarter <= 0: quarter += 4 year -= 1 quarters.append(f"{year}Q{quarter}") return quarters def home(request): return render(request, "portal/home.html", {"title": "Home"}) @login_required(login_url="/") def rick(request): return render(request, "rick.html", {"title": "Rick"}) @login_required(login_url="/") def dashboard_02(request): return render(request, "dashboards/dashboard-02.html", {"title": "Dashboard-02"}) @login_required(login_url="/") def dashboard_03(request): return render(request, "dashboards/dashboard-03.html", {"title": "Dashboard-03"}) @login_required(login_url="/") def customerinvenstory(request): # get the user id current_user = request.user #ic(current_user) #ic(current_user.id) sql = """SELECT reportid FROM portal_user_map WHERE userid = %s;""" cursor.execute(sql, (current_user.id,)) report_id = cursor.fetchone() #ic(report_id) # get report info sql = """SELECT * FROM report_queue WHERE id = %s LIMIT 1;""" cursor.execute(sql, (report_id['reportid'],)) report = cursor.fetchone() #ic(report) start = request.GET.get("start") end = request.GET.get("end") if start is None: start = first_day_previous_quarter() if end is None: end = last_day_previous_quarter() payment_model = report["payment_model"] report_identifier = report["report_identifier"] #ic(start) #ic(request.GET.get("start")) if payment_model == "POR" or report["data_source"] == "Invoices": invs_query = """SELECT DISTINCT ndc FROM 340b_claims WHERE bill_date BETWEEN %s AND %s AND report_identifier = %s UNION SELECT DISTINCT ndc11 as ndc FROM replenishments WHERE replenishment_date BETWEEN %s AND %s AND report_identifier = %s;""" else: invs_query = """SELECT DISTINCT ndc FROM 340b_claims WHERE fill_date BETWEEN %s AND %s AND report_identifier = %s UNION SELECT DISTINCT ndc11 as ndc FROM replenishments WHERE replenishment_date BETWEEN %s AND %s AND report_identifier = %s;;""" invs_input = ( start, end, report_identifier, start, end, report_identifier, ) cursor.execute(invs_query, invs_input) invs_ndcs = cursor.fetchall() for ndc in invs_ndcs: ndc["ndc"] = ndc["ndc"].zfill(11) # get manuf name manuf_query = ( """SELECT manufacturer FROM manuf_exclusions WHERE ndc11 = %s LIMIT 1;""" ) cursor.execute(manuf_query, (ndc["ndc"],)) manuf_name = cursor.fetchone() try: ndc["manufacturer"] = manuf_name["manufacturer"] except: ndc["manufacturer"] = "Not Restricted Manufacturer" # get description and indicator invs_desc_query = """SELECT * FROM 340b_claims WHERE ndc = %s AND bill_date BETWEEN %s AND %s AND report_identifier = %s LIMIT 1;""" invs_desc_inputs = (ndc["ndc"], start, end, report_identifier) cursor.execute(invs_desc_query, invs_desc_inputs) invs_desc = cursor.fetchone() # ic(invs_desc) try: ndc["description"] = invs_desc["drug_name"] ndc["indicator"] = invs_desc["indicator"] except: invs_desc_query2 = """SELECT * FROM replenishments WHERE ndc11 = %s AND replenishment_date BETWEEN %s AND %s AND report_identifier = %s LIMIT 1;""" invs_desc_inputs2 = (ndc["ndc"], start, end, report_identifier) cursor.execute(invs_desc_query2, invs_desc_inputs2) invs_desc2 = cursor.fetchone() try: ndc["description"] = invs_desc2["drug_name"] ndc["indicator"] = "" except: pass # get package price invs_pp_query = """SELECT * FROM drug_catalog WHERE ndc11 = %s LIMIT 1;""" invs_pp_inputs = (ndc["ndc"],) cursor.execute(invs_pp_query, invs_pp_inputs) invs_pp = cursor.fetchone() ndc["pkg_price"] = float(invs_pp["price"]) # dispense packages query invs_dp_query = """SELECT IFNULL(sum(pkgs_disp), 0) AS sum_pkgs_disp FROM 340b_claims WHERE ndc = %s AND bill_date BETWEEN %s AND %s AND report_identifier = %s;""" invs_dp_inputs = (ndc["ndc"], start, end, report_identifier) cursor.execute(invs_dp_query, invs_dp_inputs) invs_dp = cursor.fetchone() ndc["num_pkgs"] = float(invs_dp["sum_pkgs_disp"]) # dispensed value ndc["disp_value"] = float(ndc["num_pkgs"]) * ndc["pkg_price"] # replenished packages query # replenishment packages query invs_rp_query = """SELECT IFNULL(sum(num_pkgs), 0) AS sum_num_pkgs FROM replenishments WHERE ndc11 = %s AND replenishment_date BETWEEN %s AND %s AND report_identifier = %s;""" invs_rp_inputs = (ndc["ndc"], start, end, report_identifier) cursor.execute(invs_rp_query, invs_rp_inputs) invs_rp = cursor.fetchone() ndc["repl_pkgs"] = float(invs_rp["sum_num_pkgs"]) # replenished value ndc["repl_value"] = ndc["repl_pkgs"] * ndc["pkg_price"] # variance ndc["variance"] = ndc["num_pkgs"] - ndc["repl_pkgs"] # variance value ndc["variance_value"] = ndc["variance"] * ndc["pkg_price"] # accumulator packages query invs_ap_query = """SELECT IFNULL(sum(num_pkgs), 0) as sum_num_pkgs FROM accumulator WHERE ndc11 = %s AND accumulator_date = (select MAX(accumulator_date) FROM accumulator WHERE report_identifier = %s) AND report_identifier = %s;""" invs_ap_inputs = (ndc["ndc"], report_identifier, report_identifier) cursor.execute(invs_ap_query, invs_ap_inputs) invs_ap = cursor.fetchone() ndc["accum_pkgs"] = float(invs_ap["sum_num_pkgs"]) # get max accumulator date accum_date_sql = """SELECT MAX(accumulator_date) as accumulator_date FROM accumulator WHERE report_identifier = %s;""" cursor.execute(accum_date_sql, (report_identifier,)) accum_date = cursor.fetchone() context = {} context.update({"title": "InvenStory", "invenstory": invs_ndcs, "report": report, "start": start, "end": end, "accum_date": accum_date["accumulator_date"] }) # ic(context) return render(request, "portal/customer-invenstory.html", context) @login_required(login_url="/") def customerreplenishments(request): # get the user id current_user = request.user #ic(current_user) #ic(current_user.id) sql = """SELECT reportid FROM portal_user_map WHERE userid = %s;""" cursor.execute(sql, (current_user.id,)) report_id = cursor.fetchone() #ic(report_id) # get report info sql = """SELECT * FROM report_queue WHERE id = %s LIMIT 1;""" cursor.execute(sql, (report_id['reportid'],)) report = cursor.fetchone() #ic(report) start = request.GET.get("start") end = request.GET.get("end") if start is None: start = first_day_previous_quarter() if end is None: end = last_day_previous_quarter() payment_model = report["payment_model"] #ic(start) #ic(request.GET.get("start")) fin_sql = """SELECT * FROM replenishments WHERE report_identifier = %s AND replenishment_date BETWEEN %s AND %s;""" cursor.execute(fin_sql, (report["report_identifier"], start, end)) replenishments = cursor.fetchall() context = {} context.update({"title": "Replenishments", "replenishments": replenishments, "report": report, "start": start, "end": end }) # ic(context) return render(request, "portal/customer-replenishments.html", context) @login_required(login_url="/") def customeraccumulator(request): # get the user id current_user = request.user #ic(current_user) #ic(current_user.id) sql = """SELECT reportid FROM portal_user_map WHERE userid = %s;""" cursor.execute(sql, (current_user.id,)) report_id = cursor.fetchone() #ic(report_id) # get report info sql = """SELECT * FROM report_queue WHERE id = %s LIMIT 1;""" cursor.execute(sql, (report_id['reportid'],)) report = cursor.fetchone() #ic(report) # get last accumulator date accum_date_sql = """SELECT MAX(accumulator_date) as accumulator_date FROM accumulator WHERE report_identifier = %s;""" cursor.execute(accum_date_sql, (report["report_identifier"],)) accumulator_date = cursor.fetchone() #ic(accumulator_date) # get accumulator data accum_sql = """SELECT * FROM accumulator WHERE report_identifier = %s AND accumulator_date = %s;""" cursor.execute(accum_sql, (report["report_identifier"], accumulator_date["accumulator_date"])) accum_data = cursor.fetchall() #ic(accum_data) context = { "title": "Customer Accumulator", "accumulator_date": accumulator_date["accumulator_date"], "report": report, "accum_data": accum_data, } return render(request, "portal/customer-accumulator.html", context) @login_required(login_url="/") def indiv(request): # get the user id current_user = request.user #ic(current_user) #ic(current_user.id) sql = """SELECT reportid FROM portal_user_map WHERE userid = %s;""" cursor.execute(sql, (current_user.id,)) report_id = cursor.fetchone() #ic(report_id) # get report info sql = """SELECT * FROM report_queue WHERE id = %s LIMIT 1;""" cursor.execute(sql, (report_id['reportid'],)) report = cursor.fetchone() #ic(report) start = request.GET.get("start") end = request.GET.get("end") if start is None: start = first_day_previous_quarter() if end is None: end = last_day_previous_quarter() payment_model = report["payment_model"] #ic(start) #ic(request.GET.get("start")) if payment_model == "POR" or report["data_source"] == "Invoices": date_field = "bill_date" else: date_field = "fill_date" fin_sql = """SELECT IFNULL(SUM(transaction_payment),0) as sum_tp, IFNULL(SUM(disp_fee),0) as sum_df, IFNULL(SUM(revenue),0) as paid_ce, IFNULL(SUM(retail_margin),0) as sum_rm FROM 340b_claims WHERE report_identifier = %s AND {date_field} BETWEEN %s AND %s;""".format(date_field=date_field) cursor.execute(fin_sql, (report["report_identifier"], start, end)) fin_info = cursor.fetchall() impact = fin_info[0]["sum_df"] - fin_info[0]["sum_rm"] context = { "report_id": report_id, "title": "Individual", "start": start, "end": end, "report": report, "fin_info": fin_info, "tp": '${:,.2f}'.format(fin_info[0]["sum_tp"]), "df": '${:,.2f}'.format(fin_info[0]["sum_df"]), "paid_ce": '${:,.2f}'.format(fin_info[0]["paid_ce"]), "rm": '${:,.2f}'.format(fin_info[0]["sum_rm"]), "impact": '${:,.2f}'.format(impact), } return render(request, "dashboards/indiv.html", context) # customer-main page function @login_required(login_url="/") def customermain(request): # get the user id current_user = request.user #ic(current_user) #ic(current_user.id) sql = """SELECT reportid FROM portal_user_map WHERE userid = %s;""" cursor.execute(sql, (current_user.id,)) report_id = cursor.fetchone() #ic(report_id) # get report info sql = """SELECT * FROM report_queue WHERE id = %s LIMIT 1;""" cursor.execute(sql, (report_id['reportid'],)) report = cursor.fetchone() #ic(report) start = request.GET.get("start") end = request.GET.get("end") if start is None: start = first_day_previous_quarter() if end is None: end = last_day_previous_quarter() payment_model = report["payment_model"] #ic(start) #ic(request.GET.get("start")) if payment_model == "POR" or report["data_source"] == "Invoices": date_field = "bill_date" else: date_field = "fill_date" fin_sql = """SELECT IFNULL(SUM(transaction_payment),0) as sum_tp, IFNULL(SUM(disp_fee),0) as sum_df, IFNULL(SUM(revenue),0) as paid_ce, IFNULL(SUM(retail_margin),0) as sum_rm FROM 340b_claims WHERE report_identifier = %s AND {date_field} BETWEEN %s AND %s;""".format(date_field=date_field) disp_pkgs_sql = """SELECT IFNULL(SUM(pkgs_disp),0) as sum_dp, IFNULL(SUM(ce_cost),0) as sum_dc FROM 340b_claims WHERE report_identifier = %s AND {date_field} BETWEEN %s AND %s;""".format(date_field=date_field) repl_pkgs_sql = """SELECT IFNULL(SUM(num_pkgs),0) as sum_rp, IFNULL(SUM(extended_cost),0) as sum_rc FROM replenishments WHERE report_identifier = %s AND replenishment_date BETWEEN %s AND %s;""" cursor.execute(fin_sql, (report["report_identifier"], start, end)) fin_info = cursor.fetchall() impact = fin_info[0]["sum_df"] - fin_info[0]["sum_rm"] est_ret_margin_pct = (fin_info[0]["sum_rm"] / fin_info[0]["sum_tp"]) * 100 tpa_sql = """SELECT DATE(MAX(timestamp)) AS last_upload FROM 340b_claims WHERE report_identifier = %s LIMIT 1;""" cursor.execute(tpa_sql, (report["report_identifier"],)) last_tpa_upload_date = cursor.fetchone() # get replenished packages and dollar value cursor.execute(repl_pkgs_sql, (report["report_identifier"], start, end)) repl_pkgs = cursor.fetchone() # get dispensed packages and dollar value cursor.execute(disp_pkgs_sql, (report["report_identifier"], start, end)) disp_pkgs = cursor.fetchone() # get replenishment rate by dollar replenishment_rate = (repl_pkgs["sum_rc"] / disp_pkgs["sum_dc"]) * 100 # get accumulation info accum_pkg_sql = """SELECT IFNULL(SUM(num_pkgs),0) as sum_ap, IFNULL(SUM(FLOOR(num_pkgs)),0) as sum_fullpkg_ap, IFNULL(SUM(FLOOR(num_pkgs)*wac_price),0) as sum_fullpkg_ac, IFNULL(SUM(extended_cost),0) as sum_ac FROM accumulator WHERE report_identifier = %s AND accumulator_date = (SELECT MAX(accumulator_date) FROM accumulator WHERE report_identifier = %s);""" cursor.execute(accum_pkg_sql, (report["report_identifier"], report["report_identifier"])) accum_pkg = cursor.fetchone() accum_date_sql = """SELECT MAX(accumulator_date) as max_date FROM accumulator WHERE report_identifier = %s;""" cursor.execute(accum_date_sql, (report["report_identifier"],)) accum_date = cursor.fetchone() disp_vs_repl = disp_pkgs["sum_dc"] - repl_pkgs["sum_rc"] context = { "report_id": report_id, "start": start, "end": end, "report": report, "fin_info": fin_info, "tp": '${:,.2f}'.format(fin_info[0]["sum_tp"]), "df": '${:,.2f}'.format(fin_info[0]["sum_df"]), "paid_ce": '${:,.2f}'.format(fin_info[0]["paid_ce"]), "rm": '${:,.2f}'.format(fin_info[0]["sum_rm"]), "rm_pct": '{:.2f}%'.format(est_ret_margin_pct), "impact": '${:,.2f}'.format(impact), "update_date": last_tpa_upload_date["last_upload"], "replenished_pkgs": round(repl_pkgs["sum_rp"],2), "replenished_value": '${:,.2f}'.format(repl_pkgs["sum_rc"]), "dispensed_pkgs": round(disp_pkgs["sum_dp"],2), "dispensed_value": '${:,.2f}'.format(disp_pkgs["sum_dc"]), "replenishment_rate": '{:.2f}%'.format(replenishment_rate), "accumulator_pkgs": round(accum_pkg["sum_ap"],2), "accumulator_fullpkgs": round(accum_pkg["sum_fullpkg_ap"],2), "accumulator_fullpkg_value": '${:,.2f}'.format(accum_pkg["sum_fullpkg_ac"]), "disp_vs_repl": '${:,.2f}'.format(disp_vs_repl), "accumulator_date": accum_date["max_date"], } return render(request, "portal/customer-main.html", context) @login_required(login_url="/") def customerperformance(request): # get the user id current_user = request.user #ic(current_user) #ic(current_user.id) sql = """SELECT reportid FROM portal_user_map WHERE userid = %s;""" cursor.execute(sql, (current_user.id,)) report_id = cursor.fetchone() #ic(report_id) # get report info sql = """SELECT * FROM report_queue WHERE id = %s LIMIT 1;""" cursor.execute(sql, (report_id['reportid'],)) report = cursor.fetchone() payment_model = report["payment_model"] if payment_model == "POR" or report["data_source"] == "Invoices": date_field = "bill_date" else: date_field = "fill_date" count_sql = """SELECT bill_quarter, IFNULL(COUNT(*),0) AS count, IFNULL(SUM(transaction_payment),0) as transaction_payment, IFNULL(SUM(disp_fee),0) as sum_df FROM 340b_claims WHERE report_identifier = %s GROUP BY bill_quarter;""" cursor.execute(count_sql, (report["report_identifier"],)) counts = cursor.fetchall() count_data = [] count_labels = [] transaction_payments = [] dispense_fees = [] for count in counts: count_data.append(count["count"]) count_labels.append(count["bill_quarter"]) transaction_payments.append(round(float(count["transaction_payment"]),2)) dispense_fees.append(round(float(count["sum_df"]),2)) context = { "report_id": report_id, "report": report, "count_data": count_data, "count_labels": count_labels, "transaction_payments": transaction_payments, "dispense_fees": dispense_fees, } #ic(context) return render(request, "portal/customer-performance.html", context) @login_required(login_url="/") def test(request): return render(request, "portal/test.html", {"title": "Test"}) @login_required(login_url="/") def customerclaims(request): # get the user id current_user = request.user #ic(current_user) #ic(current_user.id) sql = """SELECT reportid FROM portal_user_map WHERE userid = %s;""" cursor.execute(sql, (current_user.id,)) report_id = cursor.fetchone() #ic(report_id) # get report info sql = """SELECT * FROM report_queue WHERE id = %s LIMIT 1;""" cursor.execute(sql, (report_id['reportid'],)) report = cursor.fetchone() #ic(report) start = request.GET.get("start") end = request.GET.get("end") if start is None: start = first_day_previous_quarter() if end is None: end = last_day_previous_quarter() payment_model = report["payment_model"] #ic(start) #ic(request.GET.get("start")) if payment_model == "POR" or report["data_source"] == "Invoices": date_field = "bill_date" else: date_field = "fill_date" fin_sql = """SELECT * FROM 340b_claims WHERE report_identifier = %s AND {date_field} BETWEEN %s AND %s;""".format(date_field=date_field) cursor.execute(fin_sql, (report["report_identifier"], start, end)) claims = cursor.fetchall() context = {} context.update({"title": "Claims", "claims": claims, "report": report, "start": start, "end": end }) # ic(context) return render(request, "portal/customer-claims.html", context) def about(request): return render(request, "portal/about.html", {"title": "About"}) def loginPage(request): if request.user.is_authenticated: return redirect("../customer-main") else: if request.method == "POST": username = request.POST.get("username") password = request.POST.get("password") user = authenticate(request, username=username, password=password) if user is not None: login(request, user) if request.user.is_staff: return redirect("../adminmainlist") else: return redirect("../customer-main") else: messages.info(request, "Username OR password is incorrect") context = {} return render(request, "portal/login.html", context) def logoutUser(request): logout(request) return redirect("/") @login_required(login_url="/") def dashboard(request): report = request.GET.get("report") # don't let a user see another user's report current_user = request.user allow_view = 0 block_sql = """SELECT reportid FROM portal_user_map WHERE userid = %s""" cursor.execute(block_sql, (current_user.id,)) allowed_reports = cursor.fetchall() for report_check in allowed_reports: if report_check["reportid"] == int(report): allow_view = 1 if allow_view == 0: return redirect("../customer-report-list") report_id = report # get report info sql = """SELECT * FROM report_queue WHERE id = %s""" cursor.execute(sql, (report_id,)) reports = cursor.fetchall() # get claims claim_sql = """SELECT * FROM 340b_claims WHERE report_identifier = (SELECT report_identifier FROM report_queue where id = %s) limit 100""" cursor.execute(claim_sql, (report_id,)) claims = cursor.fetchall() bc_sql = """SELECT quarter as quarter, count(*) as count, SUM(total_payment) as sum_tp, SUM(disp_fee) as sum_df, SUM(retail_margin) as sum_rm, SUM(total_payment)-SUM(disp_fee) as paidce, SUM(disp_fee)-SUM(retail_margin) as impact, SUM(dir_fee) as sum_dir FROM 340b_claims WHERE report_identifier = (SELECT report_identifier FROM report_queue WHERE id = %s) GROUP BY quarter ORDER BY quarter;""" cursor.execute(bc_sql, (report_id,)) bc_data = cursor.fetchall() table_data = copy.deepcopy(bc_data) for qtr in table_data: qtr["sum_tp"] = "${:,.2f}".format(float(qtr["sum_tp"])) qtr["sum_df"] = "${:,.2f}".format(float(qtr["sum_df"])) qtr["paidce"] = "${:,.2f}".format(float(qtr["paidce"])) qtr["impact"] = "${:,.2f}".format(float(qtr["impact"])) bcquarter = [] bccount = [] bctotalpayments = [] bcdispfee = [] bcpaidce = [] bcimpact = [] bcdirfee = [] bccountuninsured = [] bc_sum_uninsured_pmts = [] for quarter in bc_data: paid_ce = quarter["sum_tp"] - quarter["sum_df"] impact = quarter["sum_df"] - quarter["sum_rm"] bcquarter.append(quarter["quarter"]) bccount.append(quarter["count"]) bctotalpayments.append(int(quarter["sum_tp"])) bcdispfee.append(int(quarter["sum_df"])) bcdirfee.append(int(quarter["sum_dir"])) bcpaidce.append(int(paid_ce)) bcimpact.append(int(impact)) uninsured_sql = """SELECT quarter AS quarter, count(*) as uninsured_count, SUM(total_payment) as sum_uninsured_pmts FROM 340b_claims WHERE report_identifier = (SELECT report_identifier FROM report_queue where id = %s) AND uninsured = "YES" GROUP BY quarter ORDER BY quarter;""" cursor.execute(uninsured_sql, (report_id,)) uninsured_data = cursor.fetchall() for ins_quarter in uninsured_data: bccountuninsured.append(int(ins_quarter["uninsured_count"])) bc_sum_uninsured_pmts.append(int(ins_quarter["sum_uninsured_pmts"])) context = { "bcdata": bc_data, "table_data": table_data, "report_num": report, "reports": reports, "claims": claims, "labels": bcquarter, "countdata": bccount, "totalpmts": list(bctotalpayments), "dispfees": list(bcdispfee), "dirfees": list(bcdirfee), "paidce": list(bcpaidce), "impact": list(bcimpact), "uninsured": list(bccountuninsured), "uninsuredpmts": list(bc_sum_uninsured_pmts), } past_months = [ "2021-01", "2021-02", "2021-03", "2021-04", "2021-05", "2021-06", "2021-07", "2021-08", "2021-09", "2021-10", "2021-11", "2021-12", "2022-01", "2022-02", "2022-03", ] for past_month in past_months: table_month = past_month.split("-")[1] table_year = past_month.split("-")[0] table_month_label = date(1900, int(table_month), 1).strftime("%B") table_query = """SELECT COUNT(*), SUM(transaction_payment), SUM(disp_fee), SUM(retail_margin) FROM 340b_claims WHERE report_identifier = (SELECT report_identifier FROM report_queue where id = %s) AND DATE_FORMAT(fill_date,"%Y-%m") = %s;""" table_record = (report_id, past_month) cursor.execute(table_query, table_record) table_result = cursor.fetchone() return render(request, "portal/dashboard.html", context) @login_required(login_url="/") @admin_only def reportlist(request): rpt_sql = """SELECT * FROM report_queue;""" cursor.execute(rpt_sql) reportlist = cursor.fetchall() return render(request, "portal/reportlist.html", {"reportlist": reportlist}) @login_required(login_url="/") @admin_only def adminreportlist(request): rpt_sql = """SELECT * FROM report_queue WHERE report_type IN ('Panoramic','Financial', 'Counterfill') ORDER BY salesforce_report_name ASC;""" cursor.execute(rpt_sql) reportlist = cursor.fetchall() return render(request, "portal/adminreportlist.html", {"reportlist": reportlist, "count": len(reportlist)}) @login_required(login_url="/") @admin_only def adminmainlist(request): rpt_sql = """SELECT * from report_queue""" cursor.execute(rpt_sql) reportlist = cursor.fetchall() return render(request, "portal/adminmainlist.html", {"reportlist": reportlist}) @login_required(login_url="/") @admin_only def adminquarterlyimpact(request): rpt_sql = """SELECT report_identifier, quarter, tpa, count(*) as num_claims, sum(disp_fee) AS disp_fees, sum(retail_margin) AS retail_margins, sum(disp_fee)-sum(retail_margin) AS 340b_impact, sum(dir_fee) AS dir_fees FROM 340b_claims GROUP BY tpa, report_identifier, quarter ORDER BY report_identifier, quarter;""" cursor.execute(rpt_sql) reportlist = cursor.fetchall() return render( request, "portal/adminquarterlyimpact.html", {"reportlist": reportlist} ) @login_required(login_url="/") @admin_only def adminmanuexclusion(request): table = ExclusionTable(Exclusion.objects.all()) table.paginate(page=request.GET.get("page", 1), per_page=100) return render(request, "portal/adminmanuexclusion.html", {"table": table}) @login_required(login_url="/") @admin_only def admindirfactorlist(request): rpt_sql = """SELECT * from dir_factors;""" cursor.execute(rpt_sql) reportlist = cursor.fetchall() return render(request, "portal/admindirfactorlist.html", {"reportlist": reportlist}) # group report list @login_required(login_url="/") def customerreportlist(request): if request.user.is_staff: return redirect("../adminmainlist") reportslist = [] current_user = request.user report_list_query = """SELECT * FROM report_queue WHERE id IN (SELECT reportid FROM portal_user_map WHERE userid = %s)""" cursor.execute(report_list_query, (current_user.id,)) reportlist = cursor.fetchall() return render( request, "portal/customer-report-list.html", {"reportlist": reportlist} ) @login_required(login_url="/") def claim_list(request): rpt_sql = """SELECT * from report_queue limit 3;""" cursor.execute(rpt_sql) reportlist = cursor.fetchall() tablelist = [] for r in reportlist: tablelist.append(reportlist) table = tablelist return render(request, "portal/claims.html", {"table": table}) # prescriptions page @login_required(login_url="/") def prescriptions(request): context = {} # ic(request) try: report_id = request.GET.get("report") # ic(report_id) except: report_id = None try: rx_number = request.GET.get("rx_number") # ic(rx_number) rx_number = rx_number.zfill(12) except: rx_number = "" # get report identifier from report_id if report_id: sql = """SELECT * FROM report_queue WHERE id = %s;""" cursor.execute(sql, (report_id,)) report = cursor.fetchone() report_identifier = report["report_identifier"] else: report_identifier = None # ic(rx_number) # ic(report_identifier) rpt_sql = """SELECT * from 340b_claims where rx_number = %s AND report_identifier = %s;""" cursor.execute(rpt_sql, (rx_number, report_identifier,)) claim_list = cursor.fetchall() # ic(claim_list) # get averages for the report avg_sql = """SELECT AVG(total_payment) as avg_tp, AVG(disp_fee) as avg_df, AVG(retail_margin) as avg_rm, AVG(disp_fee) as avg_disp_fee FROM 340b_claims WHERE report_identifier = %s AND rx_number = %s;""" try: cursor.execute(avg_sql, (report_identifier, rx_number,)) avg_data = cursor.fetchone() # ic(avg_data) avg_tp = round(avg_data["avg_tp"],2) avg_rm = round(avg_data["avg_rm"],2) avg_disp_fee = round(avg_data["avg_disp_fee"],2) except Exception as e: avg_tp = 0 avg_rm = 0 avg_disp_fee = 0 print("no avg data") print(e) context.update({ "rx_number": rx_number, "report_id": report_id, "avg_tp": avg_tp, "avg_rm": avg_rm, "avg_disp_fee": avg_disp_fee, "claim_list": claim_list, "report": report, }) return render(request, "portal/prescriptions.html", context) # report page @login_required(login_url="/") def report(request): current_user = request.user report_id_query = """SELECT * FROM portal_user_map WHERE userid = %s LIMIT 1""" cursor.execute(report_id_query, (current_user.id,)) report_id = cursor.fetchall() this_reportid = report_id[0]["reportid"] sql = """SELECT * FROM report_queue WHERE id = %s""" sql_data = (this_reportid,) cursor.execute(sql, sql_data) reports = cursor.fetchall() # get claims claim_sql = """SELECT * FROM 340b_claims WHERE report_identifier = (SELECT report_identifier FROM report_queue WHERE id = %s) LIMIT 100;""" claim_data = (this_reportid,) cursor.execute(claim_sql, claim_data) claims = cursor.fetchall() bc_sql = """SELECT quarter as quarter, count(*) as count, SUM(total_payment) as sum_tp, SUM(disp_fee) as sum_df, SUM(retail_margin) as sum_rm, SUM(total_payment)-SUM(disp_fee) as paidce, SUM(disp_fee)-SUM(retail_margin) as impact, SUM(dir_fee) as sum_dir FROM 340b_claims WHERE report_identifier = (SELECT report_identifier FROM report_queue where id = %s) GROUP BY quarter ORDER BY quarter;""" cursor.execute(bc_sql, claim_data) bc_data = cursor.fetchall() uninsured_sql = """SELECT quarter as quarter, count(*) as uninsured_count FROM 340b_claims WHERE report_identifier = (SELECT report_identifier FROM report_queue where id = %s) AND uninsured = "YES" GROUP BY quarter ORDER BY quarter;""" cursor.execute(uninsured_sql, claim_data) uninsured_data = cursor.fetchall() bcquarter = [] bccount = [] bctotalpayments = [] bcdispfee = [] bcpaidce = [] bcimpact = [] bcdirfee = [] bc_count_uninsured = [] for quarter in bc_data: paid_ce = quarter["sum_tp"] - quarter["sum_df"] impact = quarter["sum_df"] - quarter["sum_rm"] bcquarter.append(quarter["quarter"]) bccount.append(quarter["count"]) bctotalpayments.append(int(quarter["sum_tp"])) bcdispfee.append(int(quarter["sum_df"])) bcdirfee.append(int(quarter["sum_dir"])) bcpaidce.append(int(paid_ce)) bcimpact.append(int(impact)) for ins_quarter in uninsured_data: bc_count_uninsured.append(int(ins_quarter("uninsured_count"))) context = { "bcdata": bc_data, "report_num": this_reportid, "reports": reports, "claims": claims, "labels": bcquarter, "countdata": bccount, "totalpmts": list(bctotalpayments), "dispfees": list(bcdispfee), "dirfees": list(bcdirfee), "paidce": list(bcpaidce), "impact": list(bcimpact), "uninsured": list(bc_count_uninsured), } past_months = [ "2021-01", "2021-02", "2021-03", "2021-04", "2021-05", "2021-06", "2021-07", "2021-08", "2021-09", "2021-10", "2021-11", "2021-12", "2022-01", "2022-02", "2022-03", ] return render(request, "portal/report.html", context) @login_required(login_url="/") @admin_only def admindashboard(request): context = {} report = request.GET.get("report") try: start = request.GET.get("start") end = request.GET.get("end") except: print("whatever") quarters = get_quarters(date.today()) this_quarter = quarters[0] last_quarter = quarters[1] prev_quarter = quarters[2] # ic(quarters) report_id = report # get report info sql = """SELECT * FROM report_queue WHERE id = %s LIMIT 1;""" cursor.execute(sql, (report_id,)) report = cursor.fetchone() report_id = report["id"] report_identifier = report["report_identifier"] payment_model = report["payment_model"] data_source = report["data_source"] # is counterfill? cf_customer = report["counterfill_customer"] report_type = report["report_type"] # tp, ce data sql statements if payment_model == "POR" or data_source == "Invoices": claim_sql = """SELECT * FROM 340b_claims WHERE report_identifier = %s AND bill_quarter = %s;""" prescriber_sql = """SELECT prescriber_npi, prescriber_name, count(*) as count FROM 340b_claims WHERE report_identifier = %s AND bill_quarter = %s GROUP BY prescriber_npi, prescriber_name ORDER BY count DESC LIMIT 50;""" tp_sql = """SELECT IFNULL(SUM(transaction_payment),0) as sum_tp FROM 340b_claims WHERE report_identifier = %s AND bill_quarter = %s;""" tp_cash_sql = """SELECT IFNULL(SUM(total_payment),0) as sum_tp FROM 340b_claims WHERE report_identifier = %s AND bill_quarter = %s AND uninsured = 'YES';""" ce_sql = """SELECT IFNULL(SUM(revenue),0) as sum_rev FROM 340b_claims WHERE report_identifier = %s AND bill_quarter = %s;""" ce_cash_sql = """SELECT IFNULL(SUM(revenue),0) as sum_rev FROM 340b_claims WHERE report_identifier = %s AND bill_quarter = %s AND uninsured = 'YES';""" df_sql = """SELECT IFNULL(SUM(disp_fee),0) as sum_df FROM 340b_claims WHERE report_identifier = %s AND bill_quarter = %s;""" df_cash_sql = """SELECT IFNULL(SUM(disp_fee),0) as sum_df FROM 340b_claims WHERE report_identifier = %s AND bill_quarter = %s AND uninsured = 'YES';""" rm_sql = """SELECT IFNULL(SUM(retail_margin),0) as sum_rm FROM 340b_claims WHERE report_identifier = %s AND bill_quarter = %s;""" rm_cash_sql = """SELECT IFNULL(SUM(retail_margin),0) as sum_rm FROM 340b_claims WHERE report_identifier = %s AND bill_quarter = %s AND uninsured = 'YES';""" imp_sql = """SELECT IFNULL(SUM(disp_fee),0) - IFNULL(SUM(retail_margin),0) as impact FROM 340b_claims WHERE report_identifier = %s AND bill_quarter = %s;""" imp_cash_sql = """SELECT IFNULL(SUM(disp_fee),0) - IFNULL(SUM(retail_margin),0) as impact FROM 340b_claims WHERE report_identifier = %s AND bill_quarter = %s AND uninsured = 'YES';""" disp_pkgs_sql = """SELECT IFNULL(SUM(pkgs_disp),0) as sum_dp FROM 340b_claims WHERE report_identifier = %s AND bill_quarter = %s;""" repl_pkgs_sql = """SELECT IFNULL(SUM(num_pkgs),0) as sum_rp FROM replenishments WHERE report_identifier = %s AND replenishment_date between %s AND %s;""" disp_cost_sql = """SELECT IFNULL(SUM(transaction_cost),0) as sum_dc FROM 340b_claims WHERE report_identifier = %s AND bill_quarter = %s;""" else: claim_sql = """SELECT * FROM 340b_claims WHERE report_identifier = %s AND quarter = %s;""" prescriber_sql = """SELECT prescriber_npi, prescriber_name, count(*) as count FROM 340b_claims WHERE report_identifier = %s AND quarter = %s GROUP BY prescriber_npi, prescriber_name ORDER BY count DESC LIMIT 50;""" tp_sql = """SELECT IFNULL(SUM(transaction_payment),0) as sum_tp FROM 340b_claims WHERE report_identifier = %s AND quarter = %s;""" tp_cash_sql = """SELECT IFNULL(SUM(total_payment),0) as sum_tp FROM 340b_claims WHERE report_identifier = %s AND quarter = %s AND uninsured = 'YES';""" ce_sql = """SELECT IFNULL(SUM(revenue),0) as sum_rev FROM 340b_claims WHERE report_identifier = %s AND quarter = %s;""" ce_cash_sql = """SELECT IFNULL(SUM(revenue),0) as sum_rev FROM 340b_claims WHERE report_identifier = %s AND quarter = %s AND uninsured = 'YES';""" df_sql = """SELECT IFNULL(SUM(disp_fee),0) as sum_df FROM 340b_claims WHERE report_identifier = %s AND quarter = %s;""" df_cash_sql = """SELECT IFNULL(SUM(disp_fee),0) as sum_df FROM 340b_claims WHERE report_identifier = %s AND quarter = %s AND uninsured = 'YES';""" rm_sql = """SELECT IFNULL(SUM(retail_margin),0) as sum_rm FROM 340b_claims WHERE report_identifier = %s AND quarter = %s;""" rm_cash_sql = """SELECT IFNULL(SUM(retail_margin),0) as sum_rm FROM 340b_claims WHERE report_identifier = %s AND quarter = %s AND uninsured = 'YES';""" imp_sql = """SELECT IFNULL(SUM(disp_fee),0) - IFNULL(SUM(retail_margin),0) as impact FROM 340b_claims WHERE report_identifier = %s AND quarter = %s;""" imp_cash_sql = """SELECT IFNULL(SUM(disp_fee),0) - IFNULL(SUM(retail_margin),0) as impact FROM 340b_claims WHERE report_identifier = %s AND quarter = %s AND uninsured = 'YES';""" disp_pkgs_sql = """SELECT IFNULL(SUM(pkgs_disp),0) as sum_dp FROM 340b_claims WHERE report_identifier = %s AND quarter = %s;""" disp_cost_sql = """SELECT IFNULL(SUM(transaction_cost),0) as sum_dc FROM 340b_claims WHERE report_identifier = %s AND quarter = %s;""" repl_pkgs_sql = """SELECT IFNULL(SUM(num_pkgs),0) as sum_rp FROM replenishments WHERE report_identifier = %s AND replenishment_quarter = %s;""" repl_cost_sql = """SELECT IFNULL(SUM(extended_cost),0) as sum_rc FROM replenishments WHERE report_identifier = %s AND replenishment_quarter = %s;""" accum_pkg_sql = """SELECT IFNULL(SUM(num_pkgs),0) as sum_ap FROM accumulator WHERE report_identifier = %s AND accumulator_quarter = %s;""" accum_cost_sql = """SELECT IFNULL(SUM(extended_cost),0) as sum_ac FROM accumulator WHERE report_identifier = %s AND accumulator_quarter = %s;""" # get tp data (total payments) cursor.execute(tp_sql, (report_identifier, last_quarter)) tp_data = cursor.fetchone() tp = tp_data["sum_tp"] cursor.execute(tp_sql, (report_identifier, prev_quarter)) tp_data_prev_qtr = cursor.fetchone() tp_prev_qtr = tp_data_prev_qtr["sum_tp"] cursor.execute(tp_cash_sql, (report_identifier, last_quarter)) tp_cash_data = cursor.fetchone() tp_cash = tp_cash_data["sum_tp"] # get ce data (covered entity payments) cursor.execute(ce_sql, (report_identifier, last_quarter)) ce_data = cursor.fetchone() ce = ce_data["sum_rev"] cursor.execute(ce_sql, (report_identifier, prev_quarter)) ce_data_prev_qtr = cursor.fetchone() ce_prev_qtr = ce_data_prev_qtr["sum_rev"] cursor.execute(ce_cash_sql, (report_identifier, last_quarter)) ce_cash_data = cursor.fetchone() ce_cash = ce_cash_data["sum_rev"] # get df data (dispense fees) cursor.execute(df_sql, (report_identifier, last_quarter)) df_data = cursor.fetchone() df = df_data["sum_df"] cursor.execute(df_sql, (report_identifier, prev_quarter)) df_data_prev_qtr = cursor.fetchone() df_prev_qtr = df_data_prev_qtr["sum_df"] cursor.execute(df_cash_sql, (report_identifier, last_quarter)) df_cash_data = cursor.fetchone() df_cash = df_cash_data["sum_df"] try: df_margin = (df/tp)*100 except: df_margin = 0 # get rm data cursor.execute(rm_sql, (report_identifier, last_quarter)) rm_data = cursor.fetchone() rm = rm_data["sum_rm"] cursor.execute(rm_sql, (report_identifier, prev_quarter)) rm_data_prev_qtr = cursor.fetchone() rm_prev_qtr = rm_data_prev_qtr["sum_rm"] cursor.execute(rm_cash_sql, (report_identifier, last_quarter)) rm_cash_data = cursor.fetchone() rm_cash = rm_cash_data["sum_rm"] try: rm_margin = (rm/tp)*100 except: rm_margin = 0 # get impact data cursor.execute(imp_sql, (report_identifier, last_quarter)) imp_data = cursor.fetchone() imp = imp_data["impact"] cursor.execute(imp_sql, (report_identifier, prev_quarter)) imp_data_prev_qtr = cursor.fetchone() imp_prev_qtr = imp_data_prev_qtr["impact"] cursor.execute(imp_cash_sql, (report_identifier, last_quarter)) imp_cash_data = cursor.fetchone() imp_cash = imp_cash_data["impact"] if report_type == "Panoramic": # get dispensed pkgs cursor.execute(disp_pkgs_sql, (report_identifier, last_quarter)) disp_pkgs_data = cursor.fetchone() disp_pkgs = round(disp_pkgs_data["sum_dp"],2) cursor.execute(disp_pkgs_sql, (report_identifier, prev_quarter)) disp_pkgs_data_prev_qtr = cursor.fetchone() disp_pkgs_prev_qtr = round(disp_pkgs_data_prev_qtr["sum_dp"],2) disp_pkgs_delta = round(disp_pkgs - disp_pkgs_prev_qtr,2) context.update({'disp_pkgs': disp_pkgs}) context.update({'disp_pkgs_prev_qtr': disp_pkgs_prev_qtr}) context.update({'disp_pkgs_delta': disp_pkgs_delta}) # get dispensed costs cursor.execute(disp_cost_sql, (report_identifier, last_quarter)) disp_cost_data = cursor.fetchone() disp_cost = round(disp_cost_data["sum_dc"],2) cursor.execute(disp_cost_sql, (report_identifier, prev_quarter)) disp_cost_data_prev_qtr = cursor.fetchone() disp_cost_prev_qtr = round(disp_cost_data_prev_qtr["sum_dc"],2) disp_cost_delta = round(disp_cost - disp_cost_prev_qtr,2) context.update({'disp_cost': '${:,.2f}'.format(disp_cost)}) context.update({'disp_cost_prev_qtr': '${:,.2f}'.format(disp_cost_prev_qtr)}) context.update({'disp_cost_delta': '${:,.2f}'.format(disp_cost_delta)}) # get replenished packages cursor.execute(repl_pkgs_sql, (report_identifier, last_quarter)) repl_pkgs_data = cursor.fetchone() repl_pkgs = round(repl_pkgs_data["sum_rp"],2) cursor.execute(repl_pkgs_sql, (report_identifier, prev_quarter)) repl_pkgs_data_prev_qtr = cursor.fetchone() repl_pkgs_prev_qtr = round(repl_pkgs_data_prev_qtr["sum_rp"],2) repl_pkgs_delta = round(repl_pkgs - repl_pkgs_prev_qtr,2) try: repl_rate = round((repl_pkgs/disp_pkgs)*100,2) except: repl_rate = 0 context.update({'repl_pkgs': repl_pkgs}) context.update({'repl_pkgs_prev_qtr': repl_pkgs_prev_qtr}) context.update({'repl_pkgs_delta': repl_pkgs_delta}) context.update({'repl_rate': '{:.2f}%'.format(repl_rate)}) # get replenished costs cursor.execute(repl_cost_sql, (report_identifier, last_quarter)) repl_cost_data = cursor.fetchone() repl_cost = round(repl_cost_data["sum_rc"],2) cursor.execute(repl_cost_sql, (report_identifier, prev_quarter)) repl_cost_data_prev_qtr = cursor.fetchone() repl_cost_prev_qtr = round(repl_cost_data_prev_qtr["sum_rc"],2) repl_cost_delta = round(repl_cost - repl_cost_prev_qtr,2) context.update({'repl_cost': '${:,.2f}'.format(repl_cost)}) context.update({'repl_cost_prev_qtr': '${:,.2f}'.format(repl_cost_prev_qtr)}) context.update({'repl_cost_delta': '${:,.2f}'.format(repl_cost_delta)}) # get disp - repl pkgs_diff = disp_pkgs - repl_pkgs context.update({'pkgs_diff': pkgs_diff}) cost_diff = disp_cost - repl_cost context.update({'cost_diff': '${:,.2f}'.format(cost_diff)}) # get accumulations cursor.execute(accum_pkg_sql, (report_identifier, last_quarter)) accum_pkg_data = cursor.fetchone() accum_pkgs = round(accum_pkg_data["sum_ap"],2) cursor.execute(accum_cost_sql, (report_identifier, last_quarter)) accum_cost_data = cursor.fetchone() accum_cost = round(accum_cost_data["sum_ac"],2) context.update({'accum_pkgs': accum_pkgs}) context.update({'accum_cost': '${:,.2f}'.format(accum_cost)}) # invenstory at a glance calculations # invenstory factor = sum_replenished/sum_dispensed <90% red, >95% green try: invenstory_factor = round(repl_pkgs/disp_pkgs,2) except: invenstory_factor = 1 if invenstory_factor < 0.95: invenstory_color = "green" elif invenstory_factor > 0.90: invenstory_color = "red" else: invenstory_color = "yellow" # POD factor sum_inv_owed/net_impact, if >75% - red, if <50% - green try: pod_factor = round(accum_cost/imp_cash,2) except: pod_factor = 1 if pod_factor < 0.50: pod_color = "green" elif pod_factor > 0.75: pod_color = "red" else: pod_color = "yellow" if invenstory_color == "red" or pod_color == "red": at_glance_color = "red" elif invenstory_color == "green" and pod_color == "green": at_glance_color = "green" else: at_glance_color = "yellow" context.update({'at_glance_color': at_glance_color}) context.update({'invenstory_factor': invenstory_factor}) context.update({'pod_factor': pod_factor}) # financials at a glance calculations # net impact ratio = this quarter net impact / previous quarter net impact > .85 green, <.65 red try: net_impact_ratio = round(imp/imp_prev_qtr,2) except: net_impact_ratio = 1 if net_impact_ratio < 0.65: net_impact_color = "red" elif net_impact_ratio > 0.85: net_impact_color = "green" else: net_impact_color = "yellow" # insured impact pct = insured disp fee margin - uninsured disp fee margin insured_disp_fee_margin = round((df - df_cash)/tp,2) uninsured_disp_fee_margin = round((df_cash)/tp,2) insured_impact_pct = round(insured_disp_fee_margin - uninsured_disp_fee_margin,2) if insured_impact_pct < 0.05: insured_impact_color = "red" elif insured_impact_pct > 0.15: insured_impact_color = "green" else: insured_impact_color = "yellow" if net_impact_color == "red" or insured_impact_color == "red": financials_color = "red" elif net_impact_color == "green" and insured_impact_color == "green": financials_color = "green" else: financials_color = "yellow" context.update({'financials_color': financials_color}) context.update({'net_impact_ratio': net_impact_ratio}) context.update({'insured_impact_pct': insured_impact_pct}) # get claims cursor.execute(claim_sql, (report_identifier, last_quarter)) claims = cursor.fetchall() # get prescribers cursor.execute(prescriber_sql, (report_identifier, last_quarter)) prescribers = cursor.fetchall() bc_sql = """SELECT quarter as quarter, count(*) as count, SUM(total_payment) as sum_tp, SUM(disp_fee) as sum_df, SUM(retail_margin) as sum_rm, SUM(total_payment)-SUM(disp_fee) as paidce, SUM(disp_fee)-SUM(retail_margin) as impact, SUM(dir_fee) as sum_dir FROM 340b_claims WHERE report_identifier = %s AND quarter > "2021Q2" GROUP BY quarter ORDER BY quarter;""" cursor.execute(bc_sql, (report["report_identifier"],)) bc_data = cursor.fetchall() table_data = copy.deepcopy(bc_data) for qtr in table_data: qtr["sum_tp"] = "${:,.2f}".format(float(qtr["sum_tp"])) qtr["sum_df"] = "${:,.2f}".format(float(qtr["sum_df"])) qtr["paidce"] = "${:,.2f}".format(float(qtr["paidce"])) qtr["impact"] = "${:,.2f}".format(float(qtr["impact"])) bcquarter = [] bccount = [] bctotalpayments = [] bcdispfee = [] bcpaidce = [] bcimpact = [] bcdirfee = [] bcretmargin = [] bccountuninsured = [] bc_sum_uninsured_pmts = [] for quarter in bc_data: paid_ce = quarter["sum_tp"] - quarter["sum_df"] impact = quarter["sum_df"] - quarter["sum_rm"] bcquarter.append(quarter["quarter"]) bccount.append(quarter["count"]) bctotalpayments.append(int(quarter["sum_tp"])) bcdispfee.append(int(quarter["sum_df"])) bcretmargin.append(int(quarter["sum_rm"])) bcdirfee.append(int(quarter["sum_dir"])) bcpaidce.append(int(paid_ce)) bcimpact.append(int(impact)) uninsured_sql = """SELECT IFNULL(quarter, quarter) as quarter, IFNULL(count(*),0) as uninsured_count, IFNULL(SUM(total_payment),0) as sum_uninsured_pmts FROM 340b_claims WHERE report_identifier = (SELECT report_identifier FROM report_queue where id = %s) AND uninsured = "YES" AND quarter > "2021Q2" GROUP BY quarter ORDER BY quarter;""" cursor.execute(uninsured_sql, (report_id,)) uninsured_data = cursor.fetchall() # ic(uninsured_data) for ins_quarter in uninsured_data: bccountuninsured.append(int(ins_quarter["uninsured_count"])) bc_sum_uninsured_pmts.append(int(ins_quarter["sum_uninsured_pmts"])) # get last tpa upload date tpa_sql = """SELECT DATE(MAX(timestamp)) AS last_upload FROM 340b_claims WHERE report_identifier = %s LIMIT 1;""" cursor.execute(tpa_sql, (report_identifier,)) last_tpa_upload_date = cursor.fetchone() # ic(last_tpa_upload_date) # ic(claims) context.update({ "report_quarter": last_quarter, "cf_customer": cf_customer, "report_type": report_type, "tp_data": tp_data, "tp": '${:,.2f}'.format(tp), "tp_prev_qtr": '${:,.2f}'.format(tp_prev_qtr), "tp_delta": '${:,.2f}'.format(tp - tp_prev_qtr), "tp_cash": '${:,.2f}'.format(tp_cash), "ce": '${:,.2f}'.format(ce), "ce_prev_qtr": '${:,.2f}'.format(ce_prev_qtr), "ce_delta": '${:,.2f}'.format(ce - ce_prev_qtr), "ce_cash": '${:,.2f}'.format(ce_cash), "df": '${:,.2f}'.format(df), "df_prev_qtr": '${:,.2f}'.format(df_prev_qtr), "df_delta": '${:,.2f}'.format(df - df_prev_qtr), "df_cash": '${:,.2f}'.format(df_cash), "df_margin": '{:.2f}%'.format(df_margin), "rm": '${:,.2f}'.format(rm), "rm_prev_qtr": '${:,.2f}'.format(rm_prev_qtr), "rm_delta": '${:,.2f}'.format(rm - rm_prev_qtr), "rm_cash": '${:,.2f}'.format(rm_cash), "rm_margin": '{:.2f}%'.format(rm_margin), "imp_decimal": imp, "imp": '${:,.2f}'.format(imp), "imp_prev_qtr": '${:,.2f}'.format(imp_prev_qtr), "imp_delta": '${:,.2f}'.format(imp - imp_prev_qtr), "imp_cash": '${:,.2f}'.format(imp_cash), "last_tpa_upload_date": last_tpa_upload_date["last_upload"], "prescribers": prescribers, "bcdata": bc_data, "table_data": table_data, "report_num": report, "report": report, "report_id": report_id, "claims": claims, "labels": bcquarter, "countdata": bccount, "totalpmts": list(bctotalpayments), "dispfees": list(bcdispfee), "dirfees": list(bcdirfee), "retmargin": list(bcretmargin), "paidce": list(bcpaidce), "impact": list(bcimpact), "uninsured": list(bccountuninsured), "uninsuredpmts": list(bc_sum_uninsured_pmts), }) return render(request, "portal/admindashboard.html", context) # reports by range page @login_required(login_url="/") def reportbyrange (request): try: report_id = request.GET.get("report") start = request.GET.get("start") end = request.GET.get("end") except: return render(request, "portal/reports-by-range.html") report_query = """SELECT * FROM report_queue WHERE id = %s;""" cursor.execute(report_query, (report_id,)) report = cursor.fetchone() payment_model = report["payment_model"] if payment_model == "POR" or report["data_source"] == "Invoices": date_field = "bill_date" else: date_field = "fill_date" fin_sql = """SELECT IFNULL(SUM(transaction_payment),0) as sum_tp, IFNULL(SUM(disp_fee),0) as sum_df, IFNULL(SUM(revenue),0) as paid_ce, IFNULL(SUM(retail_margin),0) as sum_rm FROM 340b_claims WHERE report_identifier = %s AND {date_field} BETWEEN %s AND %s;""".format(date_field=date_field) cursor.execute(fin_sql, (report["report_identifier"], start, end)) fin_info = cursor.fetchall() impact = fin_info[0]["sum_df"] - fin_info[0]["sum_rm"] context = { "report_id": report_id, "start": start, "end": end, "report": report, "fin_info": fin_info, "tp": '${:,.2f}'.format(fin_info[0]["sum_tp"]), "df": '${:,.2f}'.format(fin_info[0]["sum_df"]), "paid_ce": '${:,.2f}'.format(fin_info[0]["paid_ce"]), "rm": '${:,.2f}'.format(fin_info[0]["sum_rm"]), "impact": '${:,.2f}'.format(impact), } return render(request, "portal/reports-by-range.html", context)