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 accrual_table():
_type = sort_tuple(choices_change_type())
_change_number = sort_tuple(choices_change_number())
results = Accrual.query.join(Contract, Contractor).\
values(
Accrual.id,
Accrual.accrued,
Accrual.accrued_date,
Accrual.amount,
Contract.contract_number,
Contract.change_type,
Contract.change_number,
Contractor.name
)
resultlist = []
for id, accrued, accrued_date,\
amount, contract_number, change_type, \
change_number, contractor_name in results:
resultlist.append({
'id': id,
'accrued': '{}'.format('Yes' if accrued==True else 'No'),
'accrued_date': accrued_date.strftime('%b-%Y'),
'amount': '{:,}'.format(amount),
'contract_no': '{} {}-{}'.format(contract_number,
_type[change_type-1],
_change_number[int(change_number)-1]
),
'contractor_name': contractor_name
})
return resultlist
def alias_choice(self):
from project.utils import sort_tuple
from project.utils.choices import choices_change_type, choices_change_number
_type = sort_tuple(choices_change_type())
_change_number = sort_tuple(choices_change_number())
return "{0} {1}-{2}".format(self.contract_number,
_type[int(self.change_type)-1],
_change_number[int(self.change_number)-1]
),
def __iter__(self):
from project.models import Contract
change_type = sort_tuple(choices_change_type())
change_number = sort_tuple(choices_change_number())
for i in Contract.query.order_by(Contract.contract_number.asc()).all():
yield (i.id, '{} {}-{}'.format(i.contract_number,
change_type[i.change_type-1],
change_number[i.change_number-1]
)
)
def task_table():
_change_type = sort_tuple(choices_change_type())
results = Task.query.join(Contract, Contractor).\
values(
Task.id,
Task.task_number,
Task.remarks,
Contract.contract_number,
Contract.change_type,
Contract.change_number,
Contractor.name,
)
resultlist = []
for id, task_number, remarks, contract_number, change_type,\
change_number, name in results:
resultlist.append({
'id': id,
'contractor_name' : name,
'contract_no': '{} {}-{}'.format(contract_number,
_change_type[change_type-1],
change_number
),
'remarks': remarks
})
return resultlist
def task_table(filter=None):
_category = sort_tuple(choices_team_task_category())
_status = sort_tuple(choices_team_task_status())
results = Task.query
results = results.\
values(
# Contractor.name,
Task.id,
Task.task_no,
Task.commitment_value,
Task.cear_title,
Task.remarks,
Task.category,
Task.status,
Task.expenditure_actual
)
resultlist = []
for id, task_no, \
commitment_value, cear_title, remarks, \
category, status, expenditure_actual in results:
resultlist.append({
# 'contractor_name' : contractor_name,
'id' : id,
'task_no' : task_no,
'expenditure_value' : '{:,.2f}'.format(int(commitment_value)),
'cear_title' : cear_title,
'remarks' : remarks,
'category' : category,
'status' : status,
'actual': '{:,.2f}'.format(expenditure_actual or 0),
'overrun': True if expenditure_actual > commitment_value else False
})
return resultlist
def certificate_table():
_certificate_type = sort_tuple(choices_certificate_type())
_change_type = sort_tuple(choices_change_type())
results = Certificate.query.join(Contract, Contractor).\
values(
Certificate.id,
Contract.contract_number,
Contract.change_type,
Contract.change_number,
Contractor.name,
Certificate.certificate_reference,
Certificate.certificate_type,
Certificate.contractual_date,
Certificate.actual_date,
)
resultlist = []
for id, contract_number, change_type,\
change_number, name, certificate_reference, \
certificate_type, contractual_date,\
actual_date in results:
resultlist.append({
'id': id,
'contractor_name' : name,
'certificate_reference' : certificate_reference,
'certificate_type_string' : _certificate_type[certificate_type-1],
'contractual_date' : contractual_date.strftime('%d-%b-%Y'),
'actual_date' : actual_date.strftime('%d-%b-%Y'),
'contract_no': '{} {}-{}'.format(contract_number,
_change_type[change_type-1],
change_number
),
})
return resultlist
def document_table():
_status = sort_tuple(choices_document_status())
_type_of_doc = sort_tuple(choices_document_type())
_destination = sort_tuple(choices_document_destination())
results = Document.query.\
join(Contractor, User).\
values(
Contractor.name,
User.username,
Document.id,
Document.reference_no,
Document.ref_no_old,
Document.contract_no,
Document.counter,
Document.type_of_doc,
Document.status,
Document.subject,
Document.date_document,
Document.remarks,
Document.signed_by,
Document.date_expected,
Document.path,
Document.destination,
Document.filename,
Document.technology,
Document.received_from,
Document.end_user,
Document.reply_flag,
Document.reserve_flag,
)
resultlist = []
for contractor_name, username, id, reference_no, \
ref_no_old, contract_no, counter, type_of_doc, \
status, subject, date_document, remarks, signed_by, \
date_expected, path, destination, filename, \
technology, received_from, end_user, reply_flag, \
reserve_flag in results:
resultlist.append({
'contractor_name' : contractor_name,
'username' : username,
'id' : id,
'reference_no' : reference_no,
'ref_no_old' : ref_no_old,
'contract_no' : contract_no,
'counter' : counter,
'type_of_doc' : _type_of_doc[type_of_doc-1],
'status' : _status[status-1],
'subject' : subject,
'date_document' : date_document,
'remarks' : remarks,
'signed_by' : signed_by,
'date_expected' : date_expected,
'full_path' : get_full_path(path),
'destination' : _destination[destination-1],
'filename' : filename,
'technology' : technology,
'received_from' : received_from,
'end_user' : end_user,
'reply_flag' : reply_flag,
'reserve_flag' : reserve_flag,
})
return resultlist
def contract_table():
_type = sort_tuple(choices_change_type())
_change_number = sort_tuple(choices_change_number())
_support_provided=sort_tuple(choices_support_provided())
results = Contract.query.join(Contractor).\
filter(Contract.flag_latest==True).\
values(
Contractor.name,
Contract.id,
Contract.cwp_no,
Contract.contract_number,
Contract.change_type,
Contract.change_number,
Contract.contract_scope,
Contract.value_contract,
Contract.value_equipment,
Contract.value_services,
Contract.value_support,
Contract.delivery_date,
# Contract.rfs_date,
# Contract.pac_date,
# Contract.fac_date,
Contract.signed_date,
Contract.expiry_date,
Contract.support_provided,
Contract.support_start_date,
)
resultlist = []
for contractor_name, id, cwp_no, contract_number,\
change_type, change_number, contract_scope,\
value_contract, value_equipment, value_services,\
value_support, delivery_date, signed_date,\
expiry_date, support_provided, support_start_date in results:
resultlist.append({
'contractor_name' : contractor_name,
'id': id,
'cwp_no' : cwp_no,
'contract_number' : "{0} {1}-{2}".format(contract_number,
_type[change_type-1],
_change_number[int(change_number)-1]
),
'contract_scope' : contract_scope,
'value_contract' : value_contract,
'value_equipment' : value_equipment,
'value_services' : value_services,
'value_support' : value_support,
'delivery_date' : delivery_date.strftime('%d-%b-%Y'),
# 'rfs_date' : rfs_date.strftime('%d-%b-%Y'),
# 'pac_date' : pac_date.strftime('%d-%b-%Y'),
# 'fac_date' : fac_date.strftime('%d-%b-%Y'),
'signed_date' : signed_date.strftime('%d-%b-%Y'),
'expiry_date' : expiry_date.strftime('%d-%b-%Y'),
'support_provided' : _support_provided[support_provided-1],
'support_start_date' : support_start_date.strftime('%d-%b-%Y'),
})
return resultlist
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
)
