def dashboard_kpi_overall(record_id=None):
_template = "section_kpi_mgt/dashboard-overall.html"
df_kpi = query_to_df(SectionWeightedScore.query.all())
df_section = query_to_df(User.query.filter(User.section == True).all())
merged = pd.merge(df_kpi, df_section, left_on="section_id", right_on="id", how="left")
merged["date-grouping"] = merged["weighted_score_date"].apply(lambda x: x.strftime("%Y-%m"))
merged = merged[[u"weighted_score_date", u"weighted_score", u"category", u"full_name", u"date-grouping"]]
merged = merged.rename(columns={"full_name": "section"})
gp = merged.groupby("date-grouping")
groupings = sorted(list(gp.groups.keys()))
gp_section = gp.get_group(groupings[-1])
pivoted = gp_section.pivot(index="section", columns="category", values="weighted_score").reset_index()
all_month = (
merged.groupby(["date-grouping", "section"])
.sum()
.reset_index()
.groupby("date-grouping")
.mean()
.reset_index()
.to_json(orient="records")
)
data = pivoted.to_json(orient="records")
return render_template(_template, data=data, all_month=all_month)
def team_task_table():
_category = sort_tuple(choices_team_task_category())
_status = sort_tuple(choices_team_task_status())
_severity = sort_tuple(choices_team_task_severity())
df_contractor = query_to_df(Contractor.query.all())
df_team_task = query_to_df(TeamTask.query.all())
del df_team_task['users']
df_team_task['date_team_task'] = df_team_task['date_team_task'].apply( lambda x: date_format(x))
df_team_task['date_expected'] = df_team_task['date_expected'].apply( lambda x: date_format(x))
df_team_task['user_names'] = df_team_task['id'].apply( lambda x: get_user_names(x))
df_team_task['category'] = df_team_task['category'].apply( lambda x:_category[int(x)-1])
df_team_task['status'] = df_team_task['status'].apply( lambda x: _status[int(x)-1])
df_team_task['severity'] = df_team_task['severity'].apply( lambda x: _severity[int(x)-1])
mg = pd.merge(df_team_task, df_contractor, left_on='contractor_id', right_on='id', how='left')
mg.rename(columns={ 'name': 'contractor_name', # {'old_name': 'new_name'}
'id_x': 'id',
},
inplace=True)
data = mg.to_dict('records')
return data
def index(all_flag=None):
"""
:param all_flag:
:return:
models involve User, Contractor, TeamTask
"""
user = User.query.filter(User.id==current_user.id).first()
# create dictionary for query_to_df from the user
list_temp = []
list_grouped = [[],[]]
counter = [(0,0),(0,0)]
for i in user.tasks:
try:
list_temp.append(
{
"id": i.id,
"contractor_id": i.contractor_id,
"status": i.status,
"severity": i.severity,
"notify": i.notify,
"date_expected": i.date_expected,
"classification": i.classification,
"description": i.description
}
)
except:
pass
df_tasks = pd.DataFrame(list_temp)
df_contractor = query_to_df(Contractor.query.all())
if not df_tasks.empty:
mg = pd.merge(df_tasks, df_contractor, left_on='contractor_id', right_on='id', how='left')
mg['sorting_date'] = pd.to_datetime(mg['date_expected'])
mg = mg.sort_values(['sorting_date','id_x'], ascending=False) # Sort by Date and by ID of tasks
mg.rename(columns={'id_x': 'id'},
inplace=True)
# Filter per user, and open status
gp = mg.groupby('classification')
for i in gp.groups: # Make sure that no error will raise if only 1 or 2 is
# active
rset = gp.get_group(i)
num_total = len(rset)
num_active = len(rset[rset['status']!=2])
list_grouped[i-1] = rset[rset['status']!=2].to_dict('record')
counter[i-1] = (num_active,num_total)
return render_template("main/home.html",
i_task_data=list_grouped[0], #i_task,
v_task_data=list_grouped[1], #v_task,
counter=counter #counter
)
def section_kpi_table():
df_spec = query_to_df(SectionRawScore.query.all())
df_user = query_to_df(User.query.all())
df_spec['date-grouping'] = df_spec['score_date'].\
apply(lambda x : x.strftime('%Y-%m'))
mg = pd.merge(df_spec, df_user, left_on='section_id', right_on='id', how='left')
gp = mg.groupby(['date-grouping','full_name']).first().reset_index()
gp['id'] = gp['date-grouping']
gp = gp[['date-grouping', 'full_name', 'id']]
data = gp.to_dict('records')
columns = list(gp.columns)
return data, columns
def table_invoices_summary_(record_id):
df_invoice = query_to_df(Invoice.query.filter(Invoice.task_id==record_id).all())
df_contractor = query_to_df(Contractor.query.all())
mg = pd.merge(df_invoice, df_contractor, left_on='contractor_id', right_on='id', how='left')
mg['decimal_str_format'] = mg['capex_amount'].map(lambda x: '{:,.2f}'.format(x))
mg.rename(columns={'name': 'contractor_name', # {'old_name': 'new_name'}
'id_x': 'id',
'decimal_str_format': 'amount'},
inplace=True)
data = mg.to_dict('records')
total = float(mg['capex_amount'].sum())
return data, total
def review_kpi(filename=None):
"""
Review Details of the KPI per section by month and year
"""
section_id = current_user.get_id()
section = User.query.filter(User.id == section_id).first_or_404()
specs = SpecSectionKpi.query.filter(SpecSectionKpi.filename == filename).first()
if specs is None:
return render_template(
"section_kpi_mgt/coming-soon.html", msg="Specification for {} doesn't exist yet".format(filename)
)
spec = specs.doc[section.full_name]
classes_description = spec["_classes_description"]
targets = spec["_target"]
data = {}
record = SectionRawScore.query.filter(
and_(
SectionRawScore.section_id == section_id,
extract("month", SectionRawScore.score_date) == filename.split("-")[1],
extract("year", SectionRawScore.score_date) == filename.split("-")[0],
)
).all()
if record is None:
return render_template("section_kpi_mgt/coming-soon.html")
df = query_to_df(record)
df = df.set_index("score_class")
temp_list = []
for i in ["a11", "a12", "a13", "a2", "a3"]:
temp_list.append(
{"class": classes_description[i], "target": targets[i], "actual": float(df.loc[i, "raw_score"])}
)
data["capex"] = temp_list
temp_list = []
for i in ["b1", "b2"]:
temp_list.append(
{"class": classes_description[i], "target": targets[i], "actual": float(df.loc[i, "raw_score"])}
)
data["opex"] = temp_list
temp_list = []
for i in ["c1", "c2"]:
temp_list.append(
{"class": classes_description[i], "target": targets[i], "actual": float(df.loc[i, "raw_score"])}
)
data["initiatives"] = temp_list
_template = "section_kpi_mgt/detailed-dashboard.html"
return render_template(_template, data=data)
def get_actual(id=None):
query = Invoice.query.filter(Invoice.task_id==id).all()
df = query_to_df(query)
try:
total_amount = df['capex_amount'].sum()
except KeyError:
total_amount = 0
return total_amount
def update_kpi_dashboard(section_id=None, date=None):
mn = date.month
yr = date.year
if section_id is None:
return (False, 'Section ID must not be None')
record = SectionWeightedScore.query.\
filter(and_(SectionWeightedScore.section_id==section_id,
extract('month', SectionWeightedScore.weighted_score_date) == mn,
extract('year', SectionWeightedScore.weighted_score_date) == yr
)).\
first()
if record is not None:
return (False, "Can't Update, Updated Record Already Exist")
else:
section = 'BP&TE'
scores = SectionRawScore.query.\
filter(and_(SectionRawScore.section_id==section_id,
extract('month', SectionRawScore.score_date) == mn,
extract('year', SectionRawScore.score_date) == yr
)).\
all()
df_score = query_to_df(scores)
df_score = df_score[['score_class', 'raw_score']]
score_dict = df_score.set_index('score_class').to_dict()
score_from_raw = cal_raw_scores(raw_scores=score_dict['raw_score'],
targets=targets[section],
classes=classes
)
score_eqvs = get_score_equivalent(score_from_raw, criteria)
weighted_each_class = get_weighted_each_class(score_eqvs,
weightage_class[section],
classes)
weighted_each_category = get_weighted_each_category(weighted_each_class,
weightage_category[section],
categories)
for k, v in weighted_each_category.items():
record = SectionWeightedScore(
section_id=section_id,
weighted_score_date=scores[0].score_date,
weighted_score=v,
category=k
)
db.session.add(record)
db.session.commit()
return (True, 'Success')
def data():
df_team_task = query_to_df(TeamTask.query.all())
df_team_task['str_date_team_task'] = df_team_task['date_team_task'].map(lambda x: x.strftime('%Y-%m'))
df_team_task['open'] = df_team_task['status'].map(lambda x: 1 if x == 1 else 0)
df_team_task['closed'] = df_team_task['status'].map(lambda x: 1 if x == 2 else 0)
grouped = df_team_task[['open','closed', 'str_date_team_task']].groupby(['str_date_team_task'])
counted = grouped.sum()
normalized = counted.reset_index()
data = normalized.to_dict('records')
return json.dumps(data)
def update_budget_task(record_id=None): # Task ID
from project import db
from project.models import Task, Invoice
from project.utils.analytic import query_to_df
task =Task.query.filter_by(id=record_id).first_or_404()
invoice = Invoice.query.filter(Invoice.task_id==record_id).all()
df = query_to_df(invoice)
try:
total_amount = df['capex_amount'].sum()
except KeyError:
total_amount = 0
task.expenditure_actual = total_amount
db.session.add(task)
db.session.commit()
def dashboard(record_id=None):
_template = 'dashboard/all.html'
df_team_task = query_to_df(TeamTask.query.all())
df_team_task['str_date_team_task'] = df_team_task['date_team_task'].map(lambda x: x.strftime('%Y-%m-25'))
df_team_task['open'] = df_team_task['status'].map(lambda x: 1 if x == 1 else 0)
df_team_task['closed'] = df_team_task['status'].map(lambda x: 1 if x == 2 else 0)
grouped = df_team_task[['open','closed', 'str_date_team_task']].groupby(['str_date_team_task'])
counted = grouped.sum()
normalized = counted.reset_index()
data = normalized.to_dict('records')
table_meta = {'title': 'Teak Task',
'description': 'Categorized by Date',
'table_id': 'dashboard_amchart_1'}
return render_template(_template,
table_meta = table_meta,
data = data
)
def dashboard_kpi(record_id=None):
# section_id = 1001
section_id = current_user.get_id()
today = dt.now()
_template = "section_kpi_mgt/dashboard.html"
record = SectionWeightedScore.query.filter(SectionWeightedScore.section_id == section_id).all()
if len(record) == 0:
return render_template("section_kpi_mgt/coming-soon.html")
df_kpi = query_to_df(record)
df_kpi = df_kpi.set_index("category")
df_kpi["date-grouping"] = df_kpi["weighted_score_date"].apply(lambda x: x.strftime("%Y-%m"))
df_kpi["date-year"] = df_kpi["weighted_score_date"].apply(lambda x: x.strftime("%Y"))
df_kpi_grouped = df_kpi.groupby("date-grouping")
date_groups = list(df_kpi_grouped.groups.keys())
lastest_mn = df_kpi_grouped.get_group(date_groups[-1])
# To Solve Error When there's no previous month data
try:
prev_kpi = df_kpi_grouped.get_group(date_groups[-2])
except IndexError:
prev_kpi = lastest_mn
# for getting by year data
data_list = []
keys = list(df_kpi_grouped.groups.keys())
for key in sorted(keys):
temp_df = df_kpi_grouped.get_group(key)
data_list.append(
{
"year-mn": key,
"capex": float(temp_df.loc["capex", "weighted_score"]),
"opex": float(temp_df.loc["opex", "weighted_score"]),
"initiatives": float(temp_df.loc["initiatives", "weighted_score"]),
}
)
data = {
"by_month": {
"previous": prev_kpi["weighted_score"].sum(),
"current": lastest_mn["weighted_score"].sum(),
"arrow": "up" if lastest_mn["weighted_score"].sum() > prev_kpi["weighted_score"].sum() else "down",
"opex": lastest_mn["weighted_score"]["opex"],
"capex": lastest_mn["weighted_score"]["capex"],
"initiatives": lastest_mn["weighted_score"]["initiatives"],
},
"by_year": {
"previous": df_kpi[df_kpi["date-year"] == "%s" % (today.year - 1)]["weighted_score"].mean(),
"current": df_kpi[df_kpi["date-year"] == today.strftime("%Y")]["weighted_score"].mean(),
"data_provider": data_list,
"arrow": "down"
if df_kpi[df_kpi["date-year"] == today.strftime("%Y")]["weighted_score"].mean()
< df_kpi[df_kpi["date-year"] == "%s" % (today.year - 1)]["weighted_score"].mean()
else "up",
},
}
return render_template(_template, data=data)
def spec_section_kpi_table():
df_spec = query_to_df(SpecSectionKpi.query.all())
data = df_spec.to_dict('records')
return data
def report_view_team_task(filter_class=None, filter_val=None):
summary = {}
how = 'left' # default merging how
_field_arrangement = [
'contractor_name', 'description', 'action_taken', 'update_date', 'action_by'
]
_field_dictionary = key_label(_field_arrangement)
_template = 'dashboard/team-task.html'
_timeline_link_func_name = 'team_mgt.timeline'
_notify_link_func_name = 'team_mgt.notify'
_severity = sort_tuple(choices_team_task_severity())
_category = sort_tuple(choices_team_task_category())
_classification = sort_tuple(choices_team_task_class())
df_team_task = query_to_df(TeamTask.query.all())
df_contractor = query_to_df(Contractor.query.all())
df_history = query_to_df(TeamTaskHistory.query.\
order_by(TeamTaskHistory.date_action.desc(),
TeamTaskHistory.id.desc()).\
# limit(1).
all()
)
df_user = query_to_df(User.query.all())
if filter_class == 'Contractor':
df_contractor = df_contractor[df_contractor['id']==filter_val]
how = 'right' # to remove other contractors
elif filter_class is not None:
df_team_task = df_team_task[df_team_task[filter_class.lower()]==filter_val]
how = 'left' #retain all
df_team_task_open = df_team_task[df_team_task['status']!=2] # filter by status open
mg_history_user = pd.merge(df_history, df_user, left_on='action_taken_by', right_on='id', how='left')
mg = pd.merge(df_team_task_open, df_contractor, left_on='contractor_id', right_on='id', how=how)
#id_x is the team_task ID
mg = pd.merge(mg, mg_history_user, left_on='id_x', right_on='team_task_id', how='outer')
group_by_severity = df_team_task_open.groupby('severity')
group_by_category = df_team_task_open.groupby('category')
group_by_classification = df_team_task_open.groupby('classification')
group_by_contractor = mg.groupby('name')
#id_x_x is the team_task ID
group_by_team_task = mg.groupby('id_x_x')
summary['total_closed'] = len(df_team_task[df_team_task['status']==2])
summary['total_open'] = len(df_team_task[df_team_task['status']!=2])
summary['by_contractor'] = ('Contractor', group_sort_to_list(group_by_contractor, str_list=None))
summary['by_severity'] = ('Severity', group_sort_to_list(group_by_severity,str_list=_severity))
summary['by_category'] = ('Category', group_sort_to_list(group_by_category,str_list=_category))
summary['by_classification'] = ('Classification', group_sort_to_list(group_by_classification,str_list=_classification))
data=[]
for group in group_by_team_task:
record = group[1].head(1) # 0 is the group name; 1 is the group data
index = record.index[0] #takes the index in the Dataframe to access
try:
data.append(
{
'id': int(group[0]),
'contractor_name': record.loc[index, 'name'],
'description': record.loc[index, 'description'],
'update_date': record.loc[index, 'date_action'].strftime('%Y-%m-%d'),
'action_taken': record.loc[index, 'action_taken'],
'action_by': record.loc[index, 'username']
}
)
except:
data.append(
{
'id': int(group[0]),
'contractor_name': record.loc[index, 'name'],
'description': record.loc[index, 'description'],
'latest_action': "No Update",
'action_taken': "No Update",
'action_by': "No Update"
}
)
# records = TeamTask.query.\
# filter(TeamTask.status != 2).\
# all()
# data=[]
# for record in records:
# try:
# data.append(
# {
# 'id': record.id,
# 'contractor_name': record.contractor.name,
# 'description': record.description,
# 'update_date': record.history[0].date_action.strftime('%Y-%m-%d'),
# 'action_taken': record.history[0].action_taken,
# 'action_by': record.history[0].action_taken_by_username
# }
# )
#
# except IndexError:
# data.append(
# {
# 'id': record.id,
# 'contractor_name': record.contractor.name,
# 'description': record.description,
# 'latest_action': "No Update",
# 'action_taken': "No Update",
# 'action_by': "No Update"
# }
# )
return render_template(_template,
# table_meta = table_meta,
data = data,
summary=summary,
columns=_field_dictionary,
keys=_field_arrangement,
timeline_link_func=_timeline_link_func_name,
notify_link_func=_notify_link_func_name
)
