示例#1
0
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)
示例#2
0
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
示例#3
0
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
                           )
示例#4
0
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
示例#5
0
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
示例#6
0
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)
示例#7
0
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
示例#8
0
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')
示例#9
0
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)
示例#10
0
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()
示例#11
0
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
                        )
示例#12
0
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)
示例#13
0
def spec_section_kpi_table():
    df_spec = query_to_df(SpecSectionKpi.query.all())
    data = df_spec.to_dict('records')

    return data
示例#14
0
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
                        )