from datetime import datetime import helper as h from dateutil import relativedelta from dateutil.relativedelta import relativedelta import json import pandas pandas.options.mode.chained_assignment = None # default='warn' # Function to return all the common report labels def target_report_labels(self): return { "month_label": h.execute_sp('get_translation_by_tag', [2050, self.lang, 1], self.args)[0]['ltr_text'], "date_label": h.execute_sp('get_translation_by_tag', [124, self.lang, 1], self.args)[0]['ltr_text'], "modified_by_label": h.execute_sp('get_translation_by_tag', [1288, self.lang, 1], self.args)[0]['ltr_text'], "action_label": h.execute_sp('get_translation_by_tag', [1060, self.lang, 1], self.args)[0]['ltr_text'], "form_name_label": h.execute_sp('get_translation_by_tag', [1903, self.lang, 1], self.args)[0]['ltr_text'], "employee_label": h.execute_sp('get_translation_by_tag', [190, self.lang, 1], self.args)[0]['ltr_text'], "context_label": h.execute_sp('get_translation_by_tag', [1342, self.lang, 1], self.args)[0]['ltr_text'], "target_label": h.execute_sp('get_translation_by_tag', [1107, self.lang, 1], self.args)[0]['ltr_text'], "frequency_label": h.execute_sp('get_translation_by_tag', [1106, self.lang, 1], self.args)[0]['ltr_text'], "effective_label": h.execute_sp('get_translation_by_tag', [3886, self.lang, 1], self.args)[0]['ltr_text'], "targets_change_log_label": h.execute_sp('get_translation_by_tag', [2445, self.lang, 1], self.args)[0]['ltr_text'], "comment_by_label": h.execute_sp('get_translation_by_tag', [8860, self.lang, 1], self.args)[0]['ltr_text'], "comment_label": h.execute_sp('get_translation_by_tag', [3915, self.lang, 1], self.args)[0]['ltr_text'], "comments_log_label": h.execute_sp('get_translation_by_tag', [8862, self.lang, 1], self.args)[0]['ltr_text'], "quarter_label": h.execute_sp('get_translation_by_tag', [2891, self.lang, 1], self.args)[0]['ltr_text'], "biannual_label": h.execute_sp('get_translation_by_tag', [2892, self.lang, 1], self.args)[0]['ltr_text'], "year_label": h.execute_sp('get_translation_by_tag', [2061, self.lang, 1], self.args)[0]['ltr_text'], "target_monthly_label": h.execute_sp('get_translation_by_tag', [2051, self.lang, 1], self.args)[0]['ltr_text'], "target_quarterly_label": h.execute_sp('get_translation_by_tag', [2526, self.lang, 1], self.args)[0]['ltr_text'], "target_annually_label": h.execute_sp('get_translation_by_tag', [2062, self.lang, 1], self.args)[0]['ltr_text'], "target_bi_annually_label": h.execute_sp('get_translation_by_tag', [2528, self.lang, 1], self.args)[0]['ltr_text'], "actual_monthly_label": h.execute_sp('get_translation_by_tag', [2052, self.lang, 1], self.args)[0]['ltr_text'], "actual_quarterly_label": h.execute_sp('get_translation_by_tag', [2527, self.lang, 1], self.args)[0]['ltr_text'], "actual_annually_label": h.execute_sp('get_translation_by_tag', [2063, self.lang, 1], self.args)[0]['ltr_text'], "actual_bi_annually_label": h.execute_sp('get_translation_by_tag', [2529, self.lang, 1], self.args)[0]['ltr_text'], "compliance_label": h.execute_sp('get_translation_by_tag', [1538, self.lang, 1], self.args)[0]['ltr_text'], "actual_compliance_label": h.execute_sp('get_translation_by_tag', [8873, self.lang, 1], self.args)[0]['ltr_text'], "submitted_by_label": h.execute_sp('get_translation_by_tag', [1168, self.lang, 1], self.args)[0]['ltr_text'], "totals_label": h.execute_sp('get_translation_by_tag', [1044, self.lang, 1], self.args)[0]['ltr_text'], "annually_compliance_label": h.execute_sp('get_translation_by_tag', [2059, self.lang, 1], self.args)[0]['ltr_text'], "quarterly_compliance_label": h.execute_sp('get_translation_by_tag', [2066, self.lang, 1], self.args)[0]['ltr_text'], "monthly_compliance_label": h.execute_sp('get_translation_by_tag', [2049, self.lang, 1], self.args)[0]['ltr_text'], "bi_annual_compliance_label": h.execute_sp('get_translation_by_tag', [2064, self.lang, 1], self.args)[0]['ltr_text'], "no_data_available_label": h.execute_sp('get_translation_by_tag', [2401, self.lang, 1], self.args)[0]['ltr_text'], } # Function to get the frequencies to dispaly in the charts as per start and end dates given by the user inputs def getFrequencies(start_date, end_date): STATIC_QUARETRS = { 1: ('01', '02', '03'), 2: ('04', '05', '06'), 3: ('07', '08', '09'), 4: ('10', '11', '12') } STATIC_BIANNUALS = { 1: ('01', '02', '03', '04', '05', '06'), 2: ('07', '08', '09', '10', '11', '12') } # Quarters all_quarters = [] q_start_date, q_end_date = datetime.strptime( start_date.rsplit('-', 1)[0], '%Y-%m'), datetime.strptime(end_date.rsplit('-', 1)[0], '%Y-%m') + relativedelta(months=3) for each in pandas.date_range(q_start_date, q_end_date, freq='3m').to_list(): year = each.year quarter_number = each.quarter all_quarters.append({ 'start': f'{year}-{STATIC_QUARETRS[quarter_number][0]}', 'End': f'{year}-{STATIC_QUARETRS[quarter_number][-1]}', 'year': str(year), 'quarternumber': quarter_number }) quarters = pandas.DataFrame(all_quarters) # End Quarters # Bi Annual all_biannual = [] b_start_date, b_end_date = datetime.strptime( start_date.rsplit('-', 1)[0], '%Y-%m'), datetime.strptime(end_date.rsplit('-', 1)[0], '%Y-%m') + relativedelta(months=6) for each in pandas.date_range(b_start_date, b_end_date, freq='6m').to_list(): year = each.year biannual = 1 if each.month < 6 else 2 all_biannual.append({ 'start': f'{year}-{STATIC_BIANNUALS[biannual][0]}', 'End': f'{year}-{STATIC_BIANNUALS[biannual][-1]}', 'year': str(year), 'binumber': biannual }) biAnnuals = pandas.DataFrame(all_biannual) #End Bi Annual # Years all_years = [] a_start_date, a_end_date = datetime.strptime( start_date.rsplit('-', 1)[0], '%Y-%m'), datetime.strptime(end_date.rsplit('-', 1)[0], '%Y-%m') + relativedelta(months=12) for each in pandas.date_range(a_start_date, a_end_date, freq='12m').to_list(): year = each.year all_years.append({ "year": year, "start": str(year) + '-01-01', "end": str(year) + '-12-31', }) years = pandas.DataFrame(all_years) # End Years return ({"Quarters": quarters, "Years": years, "bi_annual": biAnnuals}) # Getting biannual number and year def get_biannual_name(data): return str(data['biannual_number']) + '-' + data['biannual_start_date'][0:4] # Comparing the assigned and the actual targets to identify the periods within the frequencies in which they were active def find_targets(type, data, df): if df.empty: return 0 if type == 'month': if data["month"] > '0': data_val = df.query(f'"{data["month"]}" == yearmonth & {data["SupervisorID"]} == SubmittedBy_SupervisorID & {data["FDFormID"]} == allFormID', inplace=False)[ 'Actual_Monthly'].values if len(data_val) > 0: return data_val[0] else: return 0 else: return 0 if type == 'Quarter': if data["quarter_start_date"] > '0': quarter_data_val = df.query(f' yearmonth >= "{data["quarter_start_date"]}" & yearmonth <= "{data["quarter_end_date"]}" & {data["SupervisorID"]} == SubmittedBy_SupervisorID & {data["FDFormID"]} == allFormID', inplace=False)[ 'Actual_Monthly'].values if len(quarter_data_val) > 0: return quarter_data_val.sum() else: return 0 else: return 0 if type == 'Year': year_data_val = [] if data["annual_year"] > 0: year_data_val = df.query(f' year == "{data["annual_year"]}" & {data["SupervisorID"]} == SubmittedBy_SupervisorID & {data["FDFormID"]} == allFormID', inplace=False)[ 'Actual_Monthly'].values if len(year_data_val) > 0: return year_data_val.sum() else: return 0 if type == 'bi_annual': if data["biannual_start_date"] > '0': biannual_data_val = df.query(f' yearmonth >= "{data["biannual_start_date"]}" & yearmonth <= "{data["biannual_end_date"]}" & {data["SupervisorID"]} == SubmittedBy_SupervisorID & {data["FDFormID"]} == allFormID', inplace=False)[ 'Actual_Monthly'].values if len(biannual_data_val) > 0: return biannual_data_val.sum() else: return 0 else: return 0 else: return 0 # Calculate the compliance percentage for all the frequencies def compliance(type, data): percentvalue_val = round( (data['Actual_'+type]/data['Target_Monthly'] * 100), 2) if data['Target_Monthly'] else 0 if percentvalue_val > 100: percentvalue_val = 100 return percentvalue_val # Function to return the quarter names to display it in the report table def get_quarter_name(data): if data['quarter_start_date'][5:7] == '01': return 'Q1 - ' + data['quarter_start_date'][0:4] elif data['quarter_start_date'][5:7] == '04': return 'Q2 - ' + data['quarter_start_date'][0:4] elif data['quarter_start_date'][5:7] == '07': return 'Q3 - ' + data['quarter_start_date'][0:4] else: return 'Q4 - ' + data['quarter_start_date'][0:4] def filter_target_value(f_target_value, frequency): temp = f_target_value.groupby('SupervisorID') temp_df = pandas.DataFrame() for sid, data in temp: data = pandas.DataFrame(data) data = data.sort_values(by=['ID'], ascending=[False]).iloc[:1] temp_df = pandas.concat([temp_df, data], ignore_index=True) temp_df['Target_Monthly'].iloc[0] = temp_df['Target_Monthly'].sum() temp_df[f'Actual_{frequency}'].iloc[0] = temp_df[f'Actual_{frequency}'].sum() return temp_df.iloc[:1] # Function to get the assigned targets def targets_data(self, start_date, end_date, sup_ids, result, frequency_pd, mode, mode_value, report_category): result['rpt_all_targets_'+mode] = h.execute_sp(self.rpt_all_targets, [ start_date, end_date, sup_ids, self.lang, mode_value, report_category], self.args) df_targets = pandas.DataFrame(result['rpt_all_targets_'+mode]) print("all targets output: ",result['rpt_all_targets_'+mode]) if not df_targets.empty: # get dataframe column to distinct list result['associated_per_ids'] +=df_targets['per_id'].unique().tolist() df_group = df_targets.groupby('form_type')['FDFormID'].apply( list).reset_index(name='form_id_list') custom_form_ids = df_group.loc[df_group['form_type'] == 'custom_form']['form_id_list'].to_list() predefined_form_ids = df_group.loc[df_group['form_type'] == 'pre_defined_form']['form_id_list'].to_list() if predefined_form_ids: predefined_form_ids = list(set(predefined_form_ids[0])) predefined_form_ids = ",".join( [str(i) for i in predefined_form_ids]) else: predefined_form_ids = None if custom_form_ids: custom_form_ids = list(set(custom_form_ids[0])) custom_form_ids = ",".join([str(i) for i in custom_form_ids]) else: custom_form_ids = None result['rpt_all_targets_supervisor_'+mode] = h.execute_sp(self.rpt_all_targets_supervisor, [ start_date, end_date, sup_ids, predefined_form_ids, custom_form_ids], self.args) else: result['rpt_all_targets_supervisor_'+mode] = [] # Creating name dictionaries for all the frequencies. if mode == 'monthly': # Executing the rpt_targets_get_months SP to get the month names. result['rpt_targets_get_'+mode+'_names'] = h.execute_sp( 'rpt_targets_get_months', [start_date, end_date, self.lang], self.args) # Below frequency names are geting populated usinig the getFrequencies() elif mode == 'quarterly': result['rpt_targets_get_'+mode + '_names'] = frequency_pd['Quarters'].to_dict(orient='records') elif mode == 'bi-annual': result['rpt_targets_get_'+mode + '_names'] = frequency_pd['bi_annual'].to_dict(orient='records') elif mode == 'annually': result['rpt_targets_get_'+mode + '_names'] = frequency_pd['Years'].to_dict(orient='records') return result # FUNCTION TO CREATE THE FINAL DICTIONARY - result['rpt_target_status_by_employee'] ---------------- Work in progress for adding comments def build_targets_data(self, result, supervisor_data, start_date, end_date, frequency_pd, report_type): category_ids = supervisor_data.keys() data_frames = {} data_frames['monthly'] = create_dataframes(result, end_date, 'monthly') data_frames['quarterly'] = create_dataframes(result, end_date, 'quarterly') data_frames['bi-annual'] = create_dataframes(result, end_date, 'bi-annual') data_frames['annually'] = create_dataframes(result, end_date, 'annually') # Getting comments per sup_id for each in category_ids: print(each) targets_final_object = {} # Creating the frequency specific object using functions monthly_return_object = get_monthly_targets_data( result, each, data_frames['monthly'], report_type) quarterly_return_object = get_quarterly_targets_data( result, each, data_frames['quarterly'], report_type) biannually_return_object = get_biannually_targets_data( result, each, data_frames['bi-annual'], frequency_pd, report_type) annually_return_object = get_annually_targets_data( result, each, data_frames['annually'], frequency_pd, report_type) # Adding inactive labels in the HTML. inactive_label = h.execute_sp('get_translation_by_tag', [ 3793, self.lang, 1], self.args)[0]['ltr_text'] status = f" ({inactive_label})" if supervisor_data[each]['status'] == 0 else '' targets_final_object['user_name'] = supervisor_data[each]['name'] + status targets_final_object['comments'] = result['rpt_all_targets_comments'] targets_final_object.update(monthly_return_object) targets_final_object.update(quarterly_return_object) targets_final_object.update(biannually_return_object) targets_final_object.update(annually_return_object) result['rpt_target_status_by_employee'].append(targets_final_object) print(monthly_return_object) print(quarterly_return_object) print(annually_return_object) print(biannually_return_object) return result # Creating df for the assigned targets(rpt_all_targets SP output) and targets submitted by the supervisor(rpt_all_targets_supervisor SP output) for each frequency def create_dataframes(result, end_date, frequency): temp = {} temp['df_targets_' + frequency] = pandas.DataFrame(result['rpt_all_targets_'+frequency]) temp['df_targets_'+frequency] = temp['df_targets_'+frequency].fillna(datetime.strptime( end_date, '%Y-%m-%d').strftime('%Y-%m-%d')) # Adding values to null effective end dates temp['df_get_'+frequency] = pandas.DataFrame( result['rpt_targets_get_'+frequency+'_names']) temp['df_all_form_sup_'+frequency] = pandas.DataFrame( result['rpt_all_targets_supervisor_'+frequency]) temp['df_targets_forms_'+frequency] = dict() # Grouping the targets per FormID to get the df_targets_forms for all the frequencies if not temp['df_targets_'+frequency].empty: temp['df_targets_forms_'+frequency] = temp['df_targets_' + frequency].groupby('FDFormID', sort=False) return temp def get_comments(self, result, start_date, end_date, id): # get comments for each person - standalone - result['rpt_all_targets_comments'] = h.execute_sp( 'rpt_all_targets_comments', [start_date, end_date, id, self.lang], self.args) for comment in result['rpt_all_targets_comments']: if comment['comment_translate']: comment['stc_comment'] = comment['comment_translate'] return result # Function to get all the monthly targets def get_monthly_targets_data(result, each, data, report_type): monthly_return_object = {} f_target_all_monthly = pandas.DataFrame([]) graphdata_monthly = [] print("Monthly Dictionary Data: ", data) for form_id, group in data['df_targets_forms_monthly']: all_months = [] all_compliance_monthly = [] f_target_monthly = pandas.DataFrame(group) form_name = f_target_monthly.iloc[0]['Form_Name'] for rec, val in data['df_get_monthly'].iterrows(): start_month = val['yearmonth'] if report_type == 'employee': f_target_value_monthly = f_target_monthly[(start_month <= f_target_monthly.EffectiveEnd) & (start_month >= f_target_monthly.EffectiveOn) & (f_target_monthly.SupervisorID == each)] elif report_type == 'job': f_target_value_monthly = f_target_monthly[(start_month <= f_target_monthly.EffectiveEnd) & (start_month >= f_target_monthly.EffectiveOn) & ((f_target_monthly.job_id == str(each)) | (f_target_monthly.job_id == 'all'))] elif report_type == 'site': f_target_value_monthly = f_target_monthly[(start_month <= f_target_monthly.EffectiveEnd) & (start_month >= f_target_monthly.EffectiveOn) & ((f_target_monthly.site_id == str(each)) | (f_target_monthly.site_id == 'all'))] elif report_type == 'role': f_target_value_monthly = f_target_monthly[(start_month <= f_target_monthly.EffectiveEnd) & (start_month >= f_target_monthly.EffectiveOn) & (f_target_monthly.role_ids == str(each))] if f_target_value_monthly.empty == False: actual_targets_df = pandas.DataFrame() # For employees no query needed if data['df_all_form_sup_monthly'].empty == False: actual_targets_df = data['df_all_form_sup_monthly'][(data['df_all_form_sup_monthly'].yearmonth == start_month) & (data['df_all_form_sup_monthly'].allFormID == form_id)] if not actual_targets_df.empty: if report_type == 'role': actual_targets_df = actual_targets_df[( actual_targets_df.role_id == each)] elif report_type == 'site': actual_targets_df = actual_targets_df[(actual_targets_df.site_id == 'all') | ( actual_targets_df.site_id == str(each))] elif report_type == 'job': actual_targets_df = actual_targets_df[(actual_targets_df.job_id == 'all') | (actual_targets_df.job_id == str(each))] f_target_value_monthly.loc[0:, 'month'] = start_month f_target_value_monthly.loc[0:,'month_year'] = val['Month_Year_Name'] f_target_value_monthly.loc[0:, 'Actual_Monthly'] = f_target_value_monthly.apply( lambda row: find_targets('month', row, actual_targets_df), axis=1) f_target_value_monthly = filter_target_value( f_target_value_monthly, 'Monthly') compliance_monthly = f_target_value_monthly.apply(lambda row: round( row['Actual_Monthly'] / row['Target_Monthly'] * 100 if row['Target_Monthly'] else 0), axis=1) if (type(compliance_monthly) == pandas.Series) and compliance_monthly is not None: compliance_monthly = int(compliance_monthly.mean()) if compliance_monthly is not None: all_compliance_monthly.append(int(compliance_monthly)) f_target_all_monthly = pandas.concat( [f_target_all_monthly, f_target_value_monthly], ignore_index=True) all_months.append(start_month) graphdata_monthly.append({ "x": all_months, "y": all_compliance_monthly, "type": 'bar', "name": form_name, "showlegend": 1, "marker_color": 'rgb(55, 83, 109)' }) monthly = [] monthly_layout = {} monthly_target_totals = 0 monthly_actual_totals = 0 monthly_compliance_totals = 0 if not f_target_all_monthly.empty: yearmonthValue = f_target_all_monthly.groupby(['month']) final_month_graph_value = [month_data['month_year'] for month_name, month_data in yearmonthValue] monthly_layout = graph_layout( result, final_month_graph_value, mode='monthly') f_target_all_monthly['percentactualvalue'] = f_target_all_monthly.apply(lambda row: round( (row['Actual_Monthly']/row['Target_Monthly'] * 100), 2) if row['Target_Monthly'] else 0, axis=1) f_target_all_monthly['percentvalue'] = f_target_all_monthly.apply( lambda row: compliance('Monthly', row), axis=1) f_target_all_monthly.sort_values(by=['month']) f_target_all_monthly = f_target_all_monthly.drop( columns=["EffectiveEnd", "EffectiveOn", "FDFormID", "FrequencyID", "ID", "SupervisorID", "per_id"]) month_group = f_target_all_monthly.groupby('month') for month_name, month_data in month_group: temp = {} temp['data'] = pandas.DataFrame( month_data).to_dict(orient='records') temp['month'] = temp['data'][0]['month_year'] monthly.append(temp) monthly_target_totals = f_target_all_monthly['Target_Monthly'].sum() monthly_actual_totals = f_target_all_monthly['Actual_Monthly'].sum() if (monthly_actual_totals): if (round(monthly_actual_totals/monthly_target_totals * 100) >= 100): monthly_compliance_totals = 100 else: monthly_compliance_totals = round( (monthly_actual_totals/monthly_target_totals * 100), 2) monthly_return_object = { "monthly": monthly, "monthly_graph": graphdata_monthly, "monthly_layout": json.dumps(monthly_layout), "monthly_totals": { "target": int(monthly_target_totals), "actual": int(monthly_actual_totals), "actual_compliance": monthly_actual_totals and round((monthly_actual_totals / monthly_target_totals * 100), 2) or 0, "total_compliance": monthly_compliance_totals } } return monthly_return_object def get_quarterly_targets_data(result, each, data, report_type): # QUARTERS quarterly = [] graphdata_quarterly = [] f_target_all_quarterly = pandas.DataFrame([]) filter_value = '' if report_type == 'employee': filter_value = 'SupervisorID' elif report_type == 'role': filter_value = 'role_ids' elif report_type == 'site': filter_value = 'site_id' elif report_type == 'job': filter_value = 'job_id' print("Quarterly data in the dictionary: ", data) for form_id, group in data['df_targets_forms_quarterly']: all_quarters = [] all_compliance_quarterly = [] f_target_quarterly = pandas.DataFrame(group).to_dict(orient='records') form_name = f_target_quarterly[0]['Form_Name'] for rec, val in data['df_get_quarterly'].iterrows(): frequency_val_quarter = [] q_start = val['start'] q_end = val['End'] frequency_year = datetime.strptime(q_start, '%Y-%m').year for quarterly_Val in f_target_quarterly: quarterly_on = quarterly_Val['EffectiveOn'] quarterly_end = quarterly_Val['EffectiveEnd'] quarterly_years = [datetime.strptime( quarterly_on, '%Y-%m').year, datetime.strptime(quarterly_end, '%Y-%m').year] if (str(quarterly_Val[filter_value]) == str(each) or str(quarterly_Val[filter_value]) == 'all') and (q_end >= quarterly_on) and frequency_year in quarterly_years: quarterly_Val['quarter_start_date'] = q_start quarterly_Val['quarter_end_date'] = q_end frequency_val_quarter.append(quarterly_Val) f_target_value_quarterly = pandas.DataFrame(frequency_val_quarter) if f_target_value_quarterly.empty == False: actual_targets_df = pandas.DataFrame() if data['df_all_form_sup_quarterly'].empty == False: actual_targets_df = data['df_all_form_sup_quarterly'][(data['df_all_form_sup_quarterly'].yearmonth == q_start) & (data['df_all_form_sup_quarterly'].allFormID == form_id)] if actual_targets_df.empty == False: if report_type == 'role': actual_targets_df = actual_targets_df[( actual_targets_df.role_id == each)] elif report_type == 'site': actual_targets_df = actual_targets_df[(actual_targets_df.site_id == str(each)) | (actual_targets_df.site_id == 'all')] elif report_type == 'job': actual_targets_df = actual_targets_df[(actual_targets_df.job_id == str(each)) | (actual_targets_df.job_id == 'all')] f_target_value_quarterly['Actual_Quarterly'] = f_target_value_quarterly.apply( lambda row: find_targets('Quarter', row, actual_targets_df), axis=1) f_target_value_quarterly = filter_target_value( f_target_value_quarterly, 'Quarterly') compliance_quarterly = all_compliance_quarterly.append(int(f_target_value_quarterly.apply(lambda row: round( row['Actual_Quarterly'] / row['Target_Monthly'] * 100 if row['Target_Monthly'] else 0), axis=1))) if (type(compliance_quarterly) == pandas.Series) and compliance_quarterly is not None: compliance_quarterly = int(compliance_quarterly.mean()) if compliance_quarterly is not None: all_compliance_quarterly.append(compliance_quarterly) f_target_all_quarterly = pandas.concat( [f_target_all_quarterly, f_target_value_quarterly], ignore_index=True) for rec, val in f_target_value_quarterly.iterrows(): all_quarters.append(val['quarter_start_date']) graphdata_quarterly.append({ "x": all_quarters, "y": all_compliance_quarterly, "type": 'bar', "name": form_name, "showlegend": 1, "marker_color": 'rgb(55, 83, 109)' }) print("Checking the quarterly group: ", f_target_all_quarterly) if not f_target_all_quarterly.empty: f_target_all_quarterly['percentactualvalue'] = f_target_all_quarterly.apply(lambda row: round( (row['Actual_Quarterly']/row['Target_Monthly'] * 100), 2) if row['Target_Monthly'] else 0, axis=1) f_target_all_quarterly['percentvalue'] = f_target_all_quarterly.apply( lambda row: compliance('Quarterly', row), axis=1) f_target_all_quarterly.sort_values(by=['quarter_start_date']) quarterly_group = f_target_all_quarterly.groupby('quarter_start_date') f_target_all_quarterly['quarter_name'] = f_target_all_quarterly.apply( lambda row: get_quarter_name(row), axis=1) quarterly_compliance_totals = 0 for quarter_name, quarter_data in quarterly_group: temp = {} temp['data'] = pandas.DataFrame( quarter_data).to_dict(orient='records') temp['quarter'] = temp['data'][0]['quarter_name'] quarterly.append(temp) quarterly_target_totals = 0 quarterly_actual_totals = 0 yearquarterValue = dict() if not f_target_all_quarterly.empty: quarterly_target_totals = f_target_all_quarterly['Target_Monthly'].sum( ) quarterly_actual_totals = f_target_all_quarterly['Actual_Quarterly'].sum( ) yearquarterValue = f_target_all_quarterly.groupby( ['quarter_start_date']) quarterly_compliance_totals = 0 if (quarterly_target_totals) > 0: if (round(quarterly_actual_totals/quarterly_target_totals * 100) >= 100): quarterly_compliance_totals = 100 else: quarterly_compliance_totals = round( (quarterly_actual_totals/quarterly_target_totals * 100), 2) final_quarter_graph_value = [quarter_data['quarter_name'] for quarter_name, quarter_data in yearquarterValue] quarterly_layout = graph_layout( result, final_quarter_graph_value, mode='quarterly') quarterly_return_object = { "quarterly": quarterly, "quarterly_graph": graphdata_quarterly, "quarterly_layout": json.dumps(quarterly_layout), "quarterly_totals": { "target": int(quarterly_target_totals), "actual": int(quarterly_actual_totals), "actual_compliance": quarterly_actual_totals and round((quarterly_actual_totals / quarterly_target_totals * 100), 2) or 0, "total_compliance": quarterly_compliance_totals } } return quarterly_return_object def get_biannually_targets_data(result, each, data, frequency_pd, report_type): biannually = [] biannuallygraphdata = [] biannually_layout = dict() biannually_target_totals = 0 biannually_actual_totals = 0 biannually_compliance_totals = 0 f_target_all_biannually = pandas.DataFrame([]) if report_type == 'employee': filter_value = 'SupervisorID' elif report_type == 'role': filter_value = 'role_ids' elif report_type == 'site': filter_value = 'site_id' elif report_type == 'job': filter_value = 'job_id' print("Biannually data in the dictionary: ", data) for form_id, group in data['df_targets_forms_bi-annual']: all_biannually = [] all_compliance_biannually = [] f_target_biannually = pandas.DataFrame(group).to_dict(orient='records') form_name = f_target_biannually[0]['Form_Name'] print("Print the frequency_pd['bi_annual'] data: ", frequency_pd['bi_annual']) for rec, val in frequency_pd['bi_annual'].iterrows(): frequency_val_biannual = [] b_start = val['start'] b_end = val['End'] # 2 frequency_year = datetime.strptime(b_start, '%Y-%m').year print("Checking the f_target_biannually variable: ",f_target_biannually) for biannually_Val in f_target_biannually: print("Printing the value of biannually_Val: ",biannually_Val) biannually_on = biannually_Val['EffectiveOn'] biannually_end = biannually_Val['EffectiveEnd'] biannual_years = [datetime.strptime( biannually_on, '%Y-%m').year, datetime.strptime(biannually_end, '%Y-%m').year] print(f"Debug - Checking condition: biannually_Val[filter_value]={biannually_Val[filter_value]}, "f"each={each}, b_end={b_end}, biannually_on={biannually_on}, "f"frequency_year={frequency_year}, biannual_years={biannual_years}") # write a debug statement for this if condition if (str(biannually_Val[filter_value]) == str(each) or str(biannually_Val[filter_value]) == 'all') and (b_end >= biannually_on) and frequency_year in biannual_years: print(f"Debug - Checking the if condition ") biannually_Val['biannual_number'] = val['binumber'] biannually_Val['biannual_start_date'] = b_start biannually_Val['biannual_end_date'] = b_end biannually_on = biannually_Val['EffectiveOn'] print("Printing the value before append: ",biannually_Val) frequency_val_biannual.append(biannually_Val) print("Printing the value of frequency_val_biannual: ",frequency_val_biannual) f_target_value_biannually = pandas.DataFrame( frequency_val_biannual) print("Printing the value of f_target_value_biannually: ",f_target_value_biannually) if f_target_value_biannually.empty == False: actual_targets_df = pandas.DataFrame() if data['df_all_form_sup_bi-annual'].empty == False: actual_targets_df = data['df_all_form_sup_bi-annual'][(data['df_all_form_sup_bi-annual'].yearmonth == b_start) & (data['df_all_form_sup_bi-annual'].allFormID == form_id)] if actual_targets_df.empty == False: if report_type == 'role': actual_targets_df = actual_targets_df[( actual_targets_df.role_id == each)] elif report_type == 'site': actual_targets_df = actual_targets_df[(actual_targets_df.site_id == str(each)) | (actual_targets_df.site_id == 'all')] elif report_type == 'job': actual_targets_df = actual_targets_df[(actual_targets_df.job_id == str(each)) | (actual_targets_df.job_id == 'all')] f_target_value_biannually['Actual_Biannually'] = f_target_value_biannually.apply( lambda row: find_targets('bi_annual', row, actual_targets_df), axis=1) f_target_value_biannually = filter_target_value( f_target_value_biannually, 'Biannually') compliance_biannually = all_compliance_biannually.append(int(f_target_value_biannually.apply(lambda row: round( row['Actual_Biannually'] / row['Target_Monthly'] * 100 if row['Target_Monthly'] else 0), axis=1))) if (type(compliance_biannually) == pandas.Series) and compliance_biannually is not None: compliance_biannually = int(compliance_biannually.mean()) if compliance_biannually is not None: all_compliance_biannually.append(compliance_biannually) f_target_all_biannually = pandas.concat( [f_target_all_biannually, f_target_value_biannually], ignore_index=True) all_biannually.append(b_start) biannuallygraphdata.append({ "x": all_biannually, "y": all_compliance_biannually, "type": 'bar', "name": form_name, "showlegend": 1, "marker_color": 'rgb(55, 83, 109)' }) print("Checking the biannually group: ", f_target_all_biannually) if not f_target_all_biannually.empty: f_target_all_biannually['percentactualvalue'] = f_target_all_biannually.apply(lambda row: round( row['Actual_Biannually']/row['Target_Monthly'] * 100) if row['Target_Monthly'] else 0, axis=1) f_target_all_biannually['percentvalue'] = f_target_all_biannually.apply( lambda row: compliance('Biannually', row), axis=1) f_target_all_biannually.sort_values(by=['biannual_start_date']) f_target_all_biannually['biannual_name'] = f_target_all_biannually.apply( lambda row: get_biannual_name(row), axis=1) biannually_group = f_target_all_biannually.groupby( 'biannual_start_date') print("Printing the biannually group: ", biannually_group) for biannual_name, biannual_data in biannually_group: temp = {} temp['data'] = pandas.DataFrame( biannual_data).to_dict(orient='records') temp['biannual'] = temp['data'][0]['biannual_name'] biannually.append(temp) print("Printing the processed bianually group: ", biannually) biannually_target_totals = f_target_all_biannually['Target_Monthly'].sum( ) biannually_actual_totals = f_target_all_biannually['Actual_Biannually'].sum( ) if (biannually_target_totals) > 0: if (round(biannually_actual_totals/biannually_target_totals * 100) >= 100): biannually_compliance_totals = 100 else: biannually_compliance_totals = round( biannually_actual_totals/biannually_target_totals * 100) yearbiannualValue = f_target_all_biannually.groupby( ['biannual_start_date']) final_biannual_graph_value = [biannual_data['biannual_name'] for biannual_name, biannual_data in yearbiannualValue] biannually_layout = graph_layout( result, final_biannual_graph_value, mode='bi_annual') biannualy_return_object = { "bi_annual": biannually, "bi_annual_graph": biannuallygraphdata, "bi_annual_layout": json.dumps(biannually_layout), "bi_annual_totals": { "target": int(biannually_target_totals), "actual": int(biannually_actual_totals), "actual_compliance": int(biannually_actual_totals and round(biannually_actual_totals / biannually_target_totals * 100) or 0), "total_compliance": int(biannually_compliance_totals) } } print("Printing the biannual object: ", biannualy_return_object) return biannualy_return_object def get_annually_targets_data(result, each, data, frequency_pd, report_type): annually = [] annuallygraphdata = [] f_target_all_annually = pandas.DataFrame([]) annually_target_totals = 0 annually_compliance_totals = 0 annually_actual_totals = 0 annually_layout = dict() if report_type == 'employee': filter_value = 'SupervisorID' elif report_type == 'role': filter_value = 'role_ids' elif report_type == 'site': filter_value = 'site_id' elif report_type == 'job': filter_value = 'job_id' for form_id, group in data['df_targets_forms_annually']: all_annuals = [] all_compliance_annually = [] f_target_annually = pandas.DataFrame(group).to_dict(orient='records') form_name = f_target_annually[0]['Form_Name'] for rec, val in frequency_pd['Years'].iterrows(): frequency_val_year = [] q_year = val['year'] q_end = val['end'] q_start = val['start'] for annually_Val in f_target_annually: annually_on = annually_Val['EffectiveOn'] annually_end = annually_Val['EffectiveEnd'] annual_years = [datetime.strptime( annually_on, '%Y-%m').year, datetime.strptime(annually_end, '%Y-%m').year] if (str(annually_Val[filter_value]) == str(each) or str(annually_Val[filter_value]) == 'all') and (q_end >= annually_on) and int(q_year) in annual_years: annually_Val['annual_start_date'] = q_start annually_Val['annual_end_date'] = q_end annually_Val['annual_year'] = q_year frequency_val_year.append(annually_Val) f_target_value_annually = pandas.DataFrame(frequency_val_year) if f_target_value_annually.empty == False: # For employees no query needed actual_targets_df = pandas.DataFrame() if data['df_all_form_sup_annually'].empty == False: actual_targets_df = data['df_all_form_sup_annually'][(data['df_all_form_sup_annually'].year == str(q_year)) & (data['df_all_form_sup_annually'].allFormID == form_id)] if actual_targets_df.empty == False: if report_type == 'role': actual_targets_df = actual_targets_df[( actual_targets_df.role_id == each)] elif report_type == 'site': actual_targets_df = actual_targets_df[(actual_targets_df.site_id == str(each)) | (actual_targets_df.site_id == 'all')] elif report_type == 'job': actual_targets_df = actual_targets_df[(actual_targets_df.job_id == str(each)) | (actual_targets_df.job_id == 'all')] f_target_value_annually['Actual_Annually'] = f_target_value_annually.apply( lambda row: find_targets('Year', row, actual_targets_df), axis=1) f_target_value_annually = filter_target_value( f_target_value_annually, 'Annually') compliance_annually = all_compliance_annually.append(int(f_target_value_annually.apply(lambda row: round( row['Actual_Annually'] / row['Target_Monthly'] * 100 if row['Target_Monthly'] else 0), axis=1))) if (type(compliance_annually) == pandas.Series) and compliance_annually is not None: compliance_annually = int(compliance_annually.mean()) if compliance_annually is not None: all_compliance_annually.append(compliance_annually) f_target_all_annually = pandas.concat( [f_target_all_annually, f_target_value_annually], ignore_index=True) for rec, val in f_target_value_annually.iterrows(): all_annuals.append(val['annual_start_date']) annuallygraphdata.append({ "x": all_annuals, "y": all_compliance_annually, "type": 'bar', "name": form_name, "showlegend": 1, "marker_color": 'rgb(55, 83, 109)' }) if not f_target_all_annually.empty: f_target_all_annually['percentactualvalue'] = f_target_all_annually.apply(lambda row: round( row['Actual_Annually']/row['Target_Monthly'] * 100) if row['Target_Monthly'] else 0, axis=1) f_target_all_annually['percentvalue'] = f_target_all_annually.apply( lambda row: compliance('Annually', row), axis=1) f_target_all_annually.sort_values(by=['annual_year']) annually_group = f_target_all_annually.groupby('annual_year') for annual_name, annual_data in annually_group: temp = {} temp['data'] = pandas.DataFrame( annual_data).to_dict(orient='records') temp['annual'] = temp['data'][0]['annual_year'] annually.append(temp) annually_target_totals = f_target_all_annually['Target_Monthly'].sum() annually_actual_totals = f_target_all_annually['Actual_Annually'].sum() if (annually_target_totals) > 0: if (round(annually_actual_totals/annually_target_totals * 100) >= 100): annually_compliance_totals = 100 else: annually_compliance_totals = round( annually_actual_totals/annually_target_totals * 100) yearannualValue = f_target_all_annually.groupby(['annual_year']) final_annual_graph_value = [annual_data['annual_year'] for annual_name, annual_data in yearannualValue] annually_layout = graph_layout( result, final_annual_graph_value, mode='annually') annually_return_object = { "annual": annually, "annual_graph": annuallygraphdata, "annual_layout": json.dumps(annually_layout), "annual_totals": { "target": int(annually_target_totals), "actual": int(annually_actual_totals), "actual_compliance": int(annually_actual_totals and round(annually_actual_totals / annually_target_totals * 100) or 0), "total_compliance": int(annually_compliance_totals) } } return annually_return_object def graph_layout(result, graph_value, mode): title = result['report_labels'][f"{mode}_compliance_label"] layout = { "title": title, "height": 500, "width": 750, "xaxis_tickfont_size": 14, "shapes": [ { "type": 'line', "x0": -0.5, "y0": 100, "x1": len(graph_value), "y1": 100, "line": { "color": 'rgb(50, 171, 96)', "width": 2, "dash": "dot" } } ], "yaxis": { "title": result['report_labels']['compliance_label'], "titlefont_size": 16, "tickfont_size": 14, "autotick": True, "interval": 20 }, "xaxis": { 'type': 'category', 'categoryorder': 'category ascending' }, "legend": { "bgcolor": 'rgba(255, 255, 255, 0)', "bordercolor": 'rgba(255, 255, 255, 0)' }, "barmode": 'group', # gap b#// gap between bars of the same location coordinate. "bargap": 0.15, } return layout