def work_days_possible(date_start, date_end):
ds_s = date_start.strftime('%m/%d/%Y')
de_s = date_end.strftime('%m/%d/%Y')
sql = """
select
sum(w.clinicdayint) as clinic_days
from tblworkdays w
where
w.clinicdate between #{d1}# and #{d2}#
""".format(d1=ds_s, d2=de_s)
a = int(cf.access_retrieve_vmr(sql)[0][0])
return a
def coid_dept_clinician_count():
sql = """
select
cad.clinic_coid & '.' & cad.dept as coid_dept,
count(*) as clinician_count
from clinician_accounting_demographics cad
inner join clinic_hierarchy ch
on cad.clinic_coid = ch.clinic_coid
group by
cad.clinic_coid & '.' & cad.dept
"""
lt = cf.access_retrieve_vmr(sql)
dt = {r[0]: r[1] for r in lt}
return dt
def work_days_actual(date_start, date_end):
ds_s = date_start.strftime('%m/%d/%Y')
de_s = date_end.strftime('%m/%d/%Y')
sql = """
select
w.clinicdate,
w.clinicdayint
from tblworkdays w
where
w.clinicdate between #{d1}# and #{d2}#
""".format(d1=ds_s, d2=de_s)
a = list(cf.access_retrieve_vmr(sql))
return a
def clinician_attributes():
sql = """
select
q1.npi,
ch.division,
ch.top_market_name,
ch.market_name,
ch.hospital_state as state,
ch.clinic_name,
q1.coid,
iif(q1.source = 'roster', 'employed', 'non-employed') as employed_status,
q1.clinician,
q1.specialty,
q1.fte,
q1.category,
q1.tenure,
q1.start_date,
q1.term_date,
q1.provider_type,
iif(left(q1.category, 1) in ('H', 'P'), 'hospital', 'not hospital') AS is_hospital
from (
select
cad.npi,
max(cad.clinic_coid) as coid,
max(cad.source) as source,
max(cad.clinician) as clinician,
max(cad.specialty) as specialty,
sum(cad.fte) as fte,
max(cad.category) as category,
max(cad.tenure) as tenure,
max(cad.start_date) as start_date,
max(cad.term_date) as term_date,
max(cad.provider_type) as provider_type
from clinician_accounting_demographics cad
group by
cad.npi
) q1
inner join clinic_hierarchy ch
on ch.clinic_coid = q1.coid
where
ch.divested_flag = 'N'
"""
lt = cf.access_retrieve_vmr(sql)
dt = {r[0]: r[1:] for r in lt}
return dt
def clinician_coid_dept():
sql = """
select distinct
cad.npi,
cad.clinic_coid & '.' & cad.dept as coid_dept
from clinician_accounting_demographics cad
inner join clinic_hierarchy ch
on cad.clinic_coid = ch.clinic_coid
where
ch.divested_flag = 'N'
"""
lt = cf.access_retrieve_vmr(sql)
dt = dict()
for r in lt:
dt.setdefault(r[0], []).append(r[1])
return dt
def mgma_benchmark():
#wrvu
sql = """
select
ms.specialty,
mb.percent_number as rank,
mb.amount as benchmark
from (mgma_benchmark mb
inner join benchmark b
on mb.benchmark_id = b.id)
inner join mgma_specialty ms
on mb.specialty_id = ms.id
where
b.id = 7
and mb.is_current = 1
"""
result = cf.access_retrieve_vmr(sql)
wrvu_bm = defaultdict(list)
for r in result:
wrvu_bm[r[0]].append(r[1:])
#comp
sql = """
select
ms.specialty,
mb.percent_number as rank,
mb.amount as benchmark
from (mgma_benchmark mb
inner join benchmark b
on mb.benchmark_id = b.id)
inner join mgma_specialty ms
on mb.specialty_id = ms.id
where
b.id = 3
and mb.is_current = 1
"""
result = cf.access_retrieve_vmr(sql)
comp_bm = defaultdict(list)
for r in result:
comp_bm[r[0]].append(r[1:])
#collection
sql = """
select
ms.specialty,
mb.percent_number as rank,
mb.amount as benchmark
from (mgma_benchmark mb
inner join benchmark b
on mb.benchmark_id = b.id)
inner join mgma_specialty ms
on mb.specialty_id = ms.id
where
b.id = 1
and mb.is_current = 1
"""
result = cf.access_retrieve_vmr(sql)
coll_bm = defaultdict(list)
for r in result:
coll_bm[r[0]].append(r[1:])
#coll_to_wru
sql = """
select
ms.specialty,
mb.percent_number as rank,
mb.amount as benchmark
from (mgma_benchmark mb
inner join benchmark b
on mb.benchmark_id = b.id)
inner join mgma_specialty ms
on mb.specialty_id = ms.id
where
b.id = 2
and mb.is_current = 1
"""
result = cf.access_retrieve_vmr(sql)
coll_to_wrvu_bm = defaultdict(list)
for r in result:
coll_to_wrvu_bm[r[0]].append(r[1:])
#comp_to_coll
sql = """
select
ms.specialty,
mb.percent_number as rank,
mb.amount as benchmark
from (mgma_benchmark mb
inner join benchmark b
on mb.benchmark_id = b.id)
inner join mgma_specialty ms
on mb.specialty_id = ms.id
where
b.id = 4
and mb.is_current = 1
"""
result = cf.access_retrieve_vmr(sql)
comp_to_coll_bm = defaultdict(list)
for r in result:
comp_to_coll_bm[r[0]].append(r[1:])
#comp_to_wrvu
sql = """
select
ms.specialty,
mb.percent_number as rank,
mb.amount as benchmark
from (mgma_benchmark mb
inner join benchmark b
on mb.benchmark_id = b.id)
inner join mgma_specialty ms
on mb.specialty_id = ms.id
where
b.id = 5
and mb.is_current = 1
"""
result = cf.access_retrieve_vmr(sql)
comp_to_wrvu_bm = defaultdict(list)
for r in result:
comp_to_wrvu_bm[r[0]].append(r[1:])
#wrvu_to_visit
sql = """
select
ms.specialty,
mb.percent_number as rank,
mb.amount as benchmark
from (mgma_benchmark mb
inner join benchmark b
on mb.benchmark_id = b.id)
inner join mgma_specialty ms
on mb.specialty_id = ms.id
where
b.id = 8
and mb.is_current = 1
"""
result = cf.access_retrieve_vmr(sql)
wrvu_to_visit_bm = defaultdict(list)
for r in result:
wrvu_to_visit_bm[r[0]].append(r[1:])
bm_dict = {'wrvu': wrvu_bm
,'comp': comp_bm
,'coll': coll_bm
,'coll_to_wrvu': coll_to_wrvu_bm
,'comp_to_coll': comp_to_coll_bm
,'comp_to_wrvu': comp_to_wrvu_bm
,'wrvu_to_visit': wrvu_to_visit_bm}
return bm_dict