def soh_data_total(self):
if not self.case_ids:
return {}
previous_month_date = (self.start_date.replace(day=1) - timedelta(days=1))
pm_last_business_day = get_business_day_of_month(previous_month_date.year, previous_month_date.month, -1)
on_time_date = get_business_day_of_month(self.start_date.year, self.start_date.month, 6)
cm_last_business_day = get_business_day_of_month(self.start_date.year, self.start_date.month, -1)
query = """
SELECT status, COUNT(case_id) FROM
(
SELECT DISTINCT ON (case_id) case_id,
CASE WHEN sr.date::date < %s THEN 'on_time' ELSE 'late' END as status
FROM stock_stocktransaction st JOIN stock_stockreport sr ON sr.id = st.report_id
WHERE sr.domain = %s AND sr.date BETWEEN %s AND %s AND case_id IN %s
AND st.type IN ('stockonhand', 'stockout')
ORDER BY case_id, sr.date
)x
GROUP BY status;
"""
with connection.cursor() as cursor:
cursor.execute(query, [
on_time_date, self.domain, pm_last_business_day,
cm_last_business_day - timedelta(microseconds=1), self.case_ids
])
rows = cursor.fetchall()
return dict(rows)
def soh_data_total(self):
if not self.case_ids:
return {}
previous_month_date = (self.start_date.replace(day=1) - timedelta(days=1))
pm_last_business_day = get_business_day_of_month(previous_month_date.year, previous_month_date.month, -1)
on_time_date = get_business_day_of_month(self.start_date.year, self.start_date.month, 6)
cm_last_business_day = get_business_day_of_month(self.start_date.year, self.start_date.month, -1)
query = """
SELECT status, COUNT(case_id) FROM
(
SELECT DISTINCT ON (case_id) case_id,
CASE WHEN sr.date::date < %s THEN 'on_time' ELSE 'late' END as status
FROM stock_stocktransaction st JOIN stock_stockreport sr ON sr.id = st.report_id
WHERE sr.domain = %s AND sr.date BETWEEN %s AND %s AND case_id IN %s
AND st.type IN ('stockonhand', 'stockout')
ORDER BY case_id, sr.date
)x
GROUP BY status;
"""
with connection.cursor() as cursor:
cursor.execute(query, [
on_time_date, self.domain, pm_last_business_day,
cm_last_business_day - timedelta(microseconds=1), self.case_ids
])
rows = cursor.fetchall()
return dict(rows)
def get_last_and_nth_business_day(date, n):
last_month = datetime(date.year, date.month, 1) - timedelta(days=1)
last_month_last_day = get_business_day_of_month(month=last_month.month,
year=last_month.year,
count=-1)
nth_business_day = get_business_day_of_month(month=date.month,
year=date.year,
count=n)
return last_month_last_day, nth_business_day
def reporting_window(start_date, end_date):
"""
Returns the range of time when people are supposed to report
"""
last_of_last_month = datetime(start_date.year, start_date.month, 1) - timedelta(days=1)
last_bd_of_last_month = datetime.combine(
get_business_day_of_month(last_of_last_month.year, last_of_last_month.month, -1), time()
)
last_bd_of_the_month = get_business_day_of_month(end_date.year, end_date.month, -1)
return last_bd_of_last_month, last_bd_of_the_month
def reporting_window(start_date, end_date):
"""
Returns the range of time when people are supposed to report
"""
last_of_last_month = datetime(start_date.year, start_date.month, 1) - timedelta(days=1)
last_bd_of_last_month = datetime.combine(
get_business_day_of_month(last_of_last_month.year, last_of_last_month.month, -1),
time()
)
last_bd_of_the_month = get_business_day_of_month(end_date.year, end_date.month, -1)
return last_bd_of_last_month, last_bd_of_the_month
def is_on_time(sp, status_date, warehouse_date, type):
"""
on_time requirement
"""
if type == SupplyPointStatusTypes.SOH_FACILITY:
if status_date.date() < get_business_day_of_month(warehouse_date.year, warehouse_date.month, 6):
return True
if type == SupplyPointStatusTypes.R_AND_R_FACILITY:
if status_date.date() < get_business_day_of_month(warehouse_date.year, warehouse_date.month, 13):
return True
return False
def reporting_window(status_type, year, month):
"""
Returns the range of time when people are supposed to report
"""
# if status_type == SupplyPointStatusTypes.SUPERVISION_FACILITY or \
# status_type == SupplyPointStatusTypes.SOH_FACILITY:
last_of_last_month = datetime(year, month, 1) - timedelta(days=1)
last_bd_of_last_month = datetime.combine\
(get_business_day_of_month(last_of_last_month.year,
last_of_last_month.month,
-1), time())
last_bd_of_the_month = get_business_day_of_month(year, month, -1)
return last_bd_of_last_month, last_bd_of_the_month
def reporting_window(status_type, year, month):
"""
Returns the range of time when people are supposed to report
"""
# if status_type == SupplyPointStatusTypes.SUPERVISION_FACILITY or \
# status_type == SupplyPointStatusTypes.SOH_FACILITY:
last_of_last_month = datetime(year, month, 1) - timedelta(days=1)
last_bd_of_last_month = datetime.combine\
(get_business_day_of_month(last_of_last_month.year,
last_of_last_month.month,
-1), time())
last_bd_of_the_month = get_business_day_of_month(year, month, -1)
return last_bd_of_last_month, last_bd_of_the_month
def is_on_time(status_date, warehouse_date, status_type):
"""
on_time requirement
SOH report should be submitted before 6th business day of the month.
R & R report should be submitted before 13th business day of the month.
Otherwise reports are marked as late response.
"""
if status_type == SupplyPointStatusTypes.SOH_FACILITY:
if status_date.date() < get_business_day_of_month(warehouse_date.year, warehouse_date.month, 6):
return True
if status_type == SupplyPointStatusTypes.R_AND_R_FACILITY:
if status_date.date() < get_business_day_of_month(warehouse_date.year, warehouse_date.month, 13):
return True
return False
def is_on_time(status_date, warehouse_date, status_type):
"""
on_time requirement
SOH report should be submitted before 6th business day of the month.
R & R report should be submitted before 13th business day of the month.
Otherwise reports are marked as late response.
"""
if status_type == SupplyPointStatusTypes.SOH_FACILITY:
if status_date.date() < get_business_day_of_month(warehouse_date.year, warehouse_date.month, 6):
return True
if status_type == SupplyPointStatusTypes.R_AND_R_FACILITY:
if status_date.date() < get_business_day_of_month(warehouse_date.year, warehouse_date.month, 13):
return True
return False
def rr_data_total(self):
current_submitting_group = DeliveryGroups(self.start_date.month).current_submitting_group()
location_ids = self.get_location_ids(current_submitting_group)
if not location_ids:
return {}
on_time_date = get_business_day_of_month(self.start_date.year, self.start_date.month, 13)
query = """
SELECT status, status_value, COUNT(location_id) FROM
(
SELECT DISTINCT ON (location_id) location_id, status_value,
CASE WHEN status_date::date < %s THEN 'on_time' ELSE 'late' END as status
FROM ilsgateway_supplypointstatus
WHERE status_date BETWEEN %s AND %s AND status_type='rr_fac'
AND status_value != 'reminder_sent' AND location_id IN %s
ORDER BY location_id, status_date DESC
)x
GROUP BY status, status_value;
"""
with connection.cursor() as cursor:
cursor.execute(query, [on_time_date, self.start_date, self.end_date, location_ids])
rows = cursor.fetchall()
rows = [((status, status_value), count) for status, status_value, count in rows]
rows.append(('total', len(location_ids)))
return dict(rows)
def supervision_task():
now = datetime.datetime.utcnow()
last_business_day = get_business_day_of_month(month=now.month,
year=now.year,
count=-1)
if now.day == last_business_day.day:
send_for_all_domains(last_business_day, send_supervision_reminder)
def rr_data_total(self):
current_submitting_group = DeliveryGroups(self.start_date.month).current_submitting_group()
location_ids = self.get_location_ids(current_submitting_group)
if not location_ids:
return {}
on_time_date = get_business_day_of_month(self.start_date.year, self.start_date.month, 13)
query = """
SELECT status, status_value, COUNT(location_id) FROM
(
SELECT DISTINCT ON (location_id) location_id, status_value,
CASE WHEN status_date::date < %s THEN 'on_time' ELSE 'late' END as status
FROM ilsgateway_supplypointstatus
WHERE status_date BETWEEN %s AND %s AND status_type='rr_fac'
AND status_value != 'reminder_sent' AND location_id IN %s
ORDER BY location_id, status_date DESC
)x
GROUP BY status, status_value;
"""
with connection.cursor() as cursor:
cursor.execute(query, [on_time_date, self.start_date, self.end_date, location_ids])
rows = cursor.fetchall()
rows = [((status, status_value), count) for status, status_value, count in rows]
rows.append(('total', len(location_ids)))
return dict(rows)
def check_day(*args, **kwargs):
comp = date_generator_func()
appropriate_day = get_business_day_of_month(comp.year, comp.month, day)
if comp.date() == appropriate_day:
return f(*args, **kwargs)
else:
pass
def first_soh_task():
now = datetime.utcnow()
last_business_day = get_business_day_of_month(month=now.month,
year=now.year,
count=-1)
if now.day == last_business_day.day:
send_for_all_domains(last_business_day, SOHReminder)
def last_report_elem(elem, supply_point, year, month, format=True):
cell_template = '<%(elem)s class="%(classes)s">%(msg)s</%(elem)s>'
state = soh_reported_on_time(supply_point, year, month)
classes = "insufficient_data"
msg = _("Waiting for reply")
if state == OnTimeStates.NO_DATA or state == OnTimeStates.INSUFFICIENT_DATA:
if format:
return cell_template % {
"classes": classes,
"msg": msg,
"elem": elem
}
else:
return msg
# check from business day to business day
last_bd_of_the_month = get_business_day_of_month(year, month, -1)
last_report = last_stock_on_hand_before(supply_point, last_bd_of_the_month)
msg = defaultfilters.date(last_report.report_date, "d M Y")
if not format: return msg
if state == OnTimeStates.LATE:
classes = "warning_icon iconified"
elif state == OnTimeStates.ON_TIME:
classes = "good_icon iconified"
return cell_template % {"classes": classes, "msg": msg, "elem": elem}
def get_last_reported(supplypoint, domain, enddate):
from custom.ilsgateway.tanzania.reports.stock_on_hand import _reported_on_time, OnTimeStates
last_bd_of_the_month = get_business_day_of_month(enddate.year, enddate.month, -1)
st = StockTransaction.objects.filter(
case_id=supplypoint, type="stockonhand", report__date__lte=last_bd_of_the_month, report__domain=domain
).order_by("-report__date")
last_of_last_month = datetime(enddate.year, enddate.month, 1) - timedelta(days=1)
last_bd_of_last_month = datetime.combine(
get_business_day_of_month(last_of_last_month.year, last_of_last_month.month, -1), time()
)
if st:
sts = _reported_on_time(last_bd_of_last_month, st[0].report.date)
return sts, st[0].report.date.date()
else:
sts = OnTimeStates.NO_DATA
return sts, None
def stockout_reminder_task():
"""
6th business day of month
"""
now = datetime.utcnow()
last_business_day = get_business_day_of_month(month=now.month, year=now.year, count=6)
if now.day != last_business_day.day:
return
send_for_all_domains(last_business_day, StockoutReminder)
def _get_window_date(status_type, date):
# we need this method because the soh and super reports actually
# are sometimes treated as reports for _next_ month
if status_type == SupplyPointStatusTypes.SOH_FACILITY or status_type == SupplyPointStatusTypes.SUPERVISION_FACILITY:
# if the date is after the last business day of the month
# count it for the next month
if date.date() >= get_business_day_of_month(date.year, date.month, -1):
year, month = add_months(date.year, date.month, 1)
return datetime(year, month, 1)
return datetime(date.year, date.month, 1)
def stockout_reminder_task():
"""
6th business day of month
"""
now = datetime.utcnow()
last_business_day = get_business_day_of_month(month=now.month, year=now.year, count=6)
if now.day != last_business_day.day:
return
send_for_all_domains(last_business_day, StockoutReminder)
def _get_window_date(status_type, date):
# we need this method because the soh and super reports actually
# are sometimes treated as reports for _next_ month
if status_type == SupplyPointStatusTypes.SOH_FACILITY or \
status_type == SupplyPointStatusTypes.SUPERVISION_FACILITY:
# if the date is after the last business day of the month
# count it for the next month
if date.date() >= get_business_day_of_month(date.year, date.month, -1):
year, month = add_months(date.year, date.month, 1)
return datetime(year, month, 1)
return datetime(date.year, date.month, 1)
def get_last_reported(supplypoint, domain, enddate):
from custom.ilsgateway.tanzania.reports.stock_on_hand import _reported_on_time, OnTimeStates
last_bd_of_the_month = get_business_day_of_month(enddate.year,
enddate.month, -1)
st = StockTransaction.objects.filter(
case_id=supplypoint,
type='stockonhand',
report__date__lte=last_bd_of_the_month,
report__domain=domain).order_by('-report__date').first()
last_of_last_month = datetime(enddate.year, enddate.month,
1) - timedelta(days=1)
last_bd_of_last_month = datetime.combine(
get_business_day_of_month(last_of_last_month.year,
last_of_last_month.month, -1), time())
if st:
sts = _reported_on_time(last_bd_of_last_month, st.report.date)
return sts, st.report.date.date()
else:
sts = OnTimeStates.NO_DATA
return sts, None
def get_last_reported(supplypoint):
last_bd_of_the_month = get_business_day_of_month(enddate.year,
enddate.month,
-1)
st = StockTransaction.objects.filter(
case_id=supplypoint,
type='stockonhand',
report__date__lte=last_bd_of_the_month
).order_by('-report__date')
last_bd_of_last_month = datetime.combine(get_business_day_of_month(enddate.year,
enddate.month,
-1), time())
if st:
sts = _reported_on_time(last_bd_of_last_month, st[0].report.date)
return sts, st[0].report.date.date()
else:
sts = OnTimeStates.NO_DATA
return sts, None
def soh_summary_task():
"""
6th business day of the month @ 3pm Tanzania time
"""
now = datetime.utcnow()
sixth_business_day = get_business_day_of_month(month=now.month, year=now.year, count=6)
if now.day != sixth_business_day.day:
return
for domain in ILSGatewayConfig.get_all_enabled_domains():
for user in get_district_people(domain):
send_translated_message(user, REMINDER_MONTHLY_SOH_SUMMARY, **construct_soh_summary(user.location))
def soh_summary_task():
"""
6th business day of the month @ 3pm Tanzania time
"""
now = datetime.utcnow()
sixth_business_day = get_business_day_of_month(month=now.month, year=now.year, count=6)
if now.day != sixth_business_day.day:
return
for domain in ILSGatewayConfig.get_all_enabled_domains():
for user in get_district_people(domain):
send_translated_message(user, REMINDER_MONTHLY_SOH_SUMMARY, **construct_soh_summary(user.location))
def soh_reported_on_time(supply_point, year, month):
key = "log_soh_reported_on_time-%(sp)s-%(year)s-%(month)s" % \
{"sp": supply_point.code, "year": year, "month": month}
if settings.LOGISTICS_USE_SPOT_CACHING:
from_cache = cache.get(key)
if from_cache: return from_cache
last_bd_of_the_month = get_business_day_of_month(year, month, -1)
last_report = last_stock_on_hand_before(supply_point, last_bd_of_the_month)
last_of_last_month = datetime(year, month, 1) - timedelta(days=1)
last_bd_of_last_month = datetime.combine\
(get_business_day_of_month(last_of_last_month.year,
last_of_last_month.month,
-1), time())
if last_report:
ret = _reported_on_time(last_bd_of_last_month, last_report.report_date)
else:
ret = OnTimeStates.NO_DATA
if settings.LOGISTICS_USE_SPOT_CACHING:
cache.set(key, ret, settings.LOGISTICS_SPOT_CACHE_TIMEOUT)
return ret
def soh_reported_on_time(supply_point, year, month):
key = "log_soh_reported_on_time-%(sp)s-%(year)s-%(month)s" % \
{"sp": supply_point.code, "year": year, "month": month}
if settings.LOGISTICS_USE_SPOT_CACHING:
from_cache = cache.get(key)
if from_cache: return from_cache
last_bd_of_the_month = get_business_day_of_month(year, month, -1)
last_report = last_stock_on_hand_before(supply_point, last_bd_of_the_month)
last_of_last_month = datetime(year, month, 1) - timedelta(days=1)
last_bd_of_last_month = datetime.combine\
(get_business_day_of_month(last_of_last_month.year,
last_of_last_month.month,
-1), time())
if last_report:
ret = _reported_on_time(last_bd_of_last_month, last_report.report_date)
else:
ret = OnTimeStates.NO_DATA
if settings.LOGISTICS_USE_SPOT_CACHING:
cache.set(key, ret, settings.LOGISTICS_SPOT_CACHE_TIMEOUT)
return ret
def delivery_summary_task():
"""
last business day of month 3pm Tanzania time
"""
now = datetime.utcnow()
last_business_day = get_business_day_of_month(month=now.month, year=now.year, count=-1)
if now.day != last_business_day.day:
return
for domain in ILSGatewayConfig.get_all_enabled_domains():
for user in get_district_people(domain):
send_translated_message(
user, REMINDER_MONTHLY_DELIVERY_SUMMARY, **construct_delivery_summary(user.location)
)
def delivery_summary_task():
"""
last business day of month 3pm Tanzania time
"""
now = datetime.utcnow()
last_business_day = get_business_day_of_month(month=now.month, year=now.year, count=-1)
if now.day != last_business_day.day:
return
for domain in ILSGatewayConfig.get_all_enabled_domains():
for user in get_district_people(domain):
send_translated_message(
user, REMINDER_MONTHLY_DELIVERY_SUMMARY, **construct_delivery_summary(user.location)
)
def last_report_elem(elem, supply_point, year, month, format=True):
cell_template = '<%(elem)s class="%(classes)s">%(msg)s</%(elem)s>'
state = soh_reported_on_time(supply_point, year, month)
classes = "insufficient_data"
msg = _("Waiting for reply")
if state == OnTimeStates.NO_DATA or state == OnTimeStates.INSUFFICIENT_DATA:
if format:
return cell_template % {"classes": classes, "msg": msg, "elem": elem}
else:
return msg
# check from business day to business day
last_bd_of_the_month = get_business_day_of_month(year, month, -1)
last_report = last_stock_on_hand_before(supply_point, last_bd_of_the_month)
msg = defaultfilters.date(last_report.report_date, "d M Y")
if not format: return msg
if state == OnTimeStates.LATE:
classes = "warning_icon iconified"
elif state == OnTimeStates.ON_TIME:
classes = "good_icon iconified"
return cell_template % {"classes": classes, "msg": msg, "elem": elem}
def get_cutoff(year, month):
return get_business_day_of_month(year, month, -1)
def get_cutoff(year, month):
return get_business_day_of_month(year, month, -1)
def supervision_task():
now = datetime.utcnow()
last_business_day = get_business_day_of_month(month=now.month, year=now.year, count=-1)
if now.day == last_business_day.day:
send_for_all_domains(last_business_day, SupervisionReminder)
def first_soh_task():
now = datetime.datetime.utcnow()
last_business_day = get_business_day_of_month(month=now.month, year=now.year, count=-1)
if now.day == last_business_day.day:
send_for_all_domains(last_business_day, send_soh_reminder)
def get_last_and_nth_business_day(date, n):
last_month = datetime.datetime(date.year, date.month, 1) - datetime.timedelta(days=1)
last_month_last_day = get_business_day_of_month(month=last_month.month, year=last_month.year, count=-1)
nth_business_day = get_business_day_of_month(month=date.month, year=date.year, count=n)
return last_month_last_day, nth_business_day
def testBusinessDays(self):
# normal
self.assertEqual(date(2011, 8, 1), get_business_day_of_month(2011, 8, 1))
self.assertEqual(date(2011, 8, 2), get_business_day_of_month(2011, 8, 2))
self.assertEqual(date(2011, 8, 3), get_business_day_of_month(2011, 8, 3))
self.assertEqual(date(2011, 8, 4), get_business_day_of_month(2011, 8, 4))
self.assertEqual(date(2011, 8, 5), get_business_day_of_month(2011, 8, 5))
self.assertEqual(date(2011, 8, 8), get_business_day_of_month(2011, 8, 6))
# negative
self.assertEqual(date(2011, 8, 31), get_business_day_of_month(2011, 8, -1))
self.assertEqual(date(2011, 8, 26), get_business_day_of_month(2011, 8, -4))
# random
self.assertEqual(date(2011, 9, 1), get_business_day_of_month(2011, 9, 1))
self.assertEqual(date(2011, 10, 3), get_business_day_of_month(2011, 10, 1))
self.assertEqual(date(2012, 2, 7), get_business_day_of_month(2012, 2, 5))
# leap year
self.assertEqual(date(2012, 2, 29), get_business_day_of_month(2012, 2, -1))
