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 )