from fast_excel_to_bill.config_for_custom_bills import OUTPUT_CUSTOMER, OUTPUT_MONTH, OUTPUT_YEAR from tool_lib.read_table import Read_Table from tool_lib.create_excel_tables import CreateTable import numpy as np def add_sum_to_dict(work_time_dict, income_per_hour): for key in work_time_dict.keys(): work_time_dict[key]['Betrag'] = int(work_time_dict[key]['Arbeitszeit']*income_per_hour) return work_time_dict def save_overtime_in_excel(overtime_dict, overtime_table_path): # Try to read existing Table try: read_table_object = Read_Table(overtime_table_path) table_object = read_table_object.table_to_dict() overtime_dict['Überstunden'] += table_object['Überstunden'] overtime_dict['KW'] += table_object['KW'] except: pass table_tuple = [] xlsx = CreateTable(overtime_dict, path=overtime_table_path) writer = xlsx.create_writer() xlsx.export_to_excel(writer) # Add to Table def round_worktime_for_week(work_time_dict,overtime_table_path, round_time = 300): overtime_dict ={'Überstunden':[], 'KW': []} for key in work_time_dict.keys(): overtime = work_time_dict[key]['Arbeitszeit'] % round_time # default round to 5 hours work_time_dict[key]['Arbeitszeit'] = (work_time_dict[key]['Arbeitszeit']-overtime)/ 60 overtime_dict['Überstunden'] += [overtime] overtime_dict['KW'] += [key] save_overtime_in_excel(overtime_dict,overtime_table_path) return work_time_dict def get_task_out_of_tree(work_time_dict): for key in work_time_dict.keys(): work_time_dict[key]['Task'] = ['- ' + last_branch[last_branch.rfind('/')+1:] for last_branch in work_time_dict[key]['Task']] return work_time_dict def compress_data_by_key_list(*compressable_lists, key_list): ''' function: a list containing different Values, if a Value is more than one time in the list. Then this list an all list of the same size are compressed, for the values of the list_of_same_size they are put in tuple. ''' if len(compressable_lists) > 1: for list_ in compressable_lists: past_values = [] pos_bias = 0 for pos, value in enumerate(key_list): if value in past_values: pos = pos - pos_bias if isinstance(list_[pos - pos_bias], int): list_[pos - 1] += list_[pos] elif isinstance(list_[pos - pos_bias], str): list_[pos - 1] = [list_[pos - 1], list_[pos]] elif isinstance(list_[pos - pos_bias], list): list_[pos - 1] = list_[pos - 1].append(list_[pos]) list_.remove(list_[pos]) pos_bias += 1 else: past_values += [value] else: compressable_lists = compressable_lists[0] if len(compressable_lists) > len(key_list): compressable_lists = compressable_lists[:len(key_list)] elif len(compressable_lists) < len(key_list): assert (False, 'Compressable list length should be greater equal then the length of the key_list') past_values = [] pos_bias = 0 for pos, value in enumerate(key_list): if value in past_values: pos = pos - pos_bias if isinstance(compressable_lists[pos - 1], int): compressable_lists[pos - 1] += compressable_lists[pos] elif isinstance(compressable_lists[pos - 1], str): compressable_lists[pos - 1] = [compressable_lists[pos - 1], compressable_lists[pos]] elif isinstance(compressable_lists[pos - 1], list): compressable_lists[pos - 1] += [compressable_lists[pos]] compressable_lists.remove(compressable_lists[pos]) pos_bias += 1 else: past_values += [value] if isinstance(compressable_lists[pos - pos_bias], str): compressable_lists[pos-pos_bias] = [compressable_lists[pos-pos_bias]] return compressable_lists def main_data_collection(pathes, list_of_keys=['Arbeitszeit', 'Task']): week_keys_to_list_of_keys_dict = {} if isinstance(pathes, list): for c, path in enumerate(pathes): # Read Data read_table_object = Read_Table(path) table_object = read_table_object.table_to_dict() # Filter Data customer_filter_value_indices = [] for task_value_idx in range(len(table_object['Task'])): task_value = table_object['Task'][task_value_idx] if task_value.startswith('root/' + OUTPUT_CUSTOMER): customer_filter_value_indices.append(task_value_idx) if OUTPUT_MONTH > 9: date_filter = str(OUTPUT_MONTH) + '/' + str(OUTPUT_YEAR) else: date_filter = '0' + str(OUTPUT_MONTH) + '/' + str(OUTPUT_YEAR) date_filter_value_indices = [] for date_value_idx in range(len(table_object['Datum'])): date_value = table_object['Datum'][date_value_idx] if date_value.endswith(date_filter): date_filter_value_indices.append(date_value_idx) customer_and_date_filtered_indices = list(set(customer_filter_value_indices) & set(date_filter_value_indices)) customer_and_date_filtered_indices.sort() for key in table_object: table_object[key] = [table_object[key][idx] for idx in customer_and_date_filtered_indices] # Calendar Week calendar_weeks_list = table_object['Kalenderwoche'] sortet_calendar_weeks = list(set(calendar_weeks_list)) sortet_calendar_weeks.sort() # Add new Weeks to dict for week in sortet_calendar_weeks: if week in week_keys_to_list_of_keys_dict: pass else: week_keys_to_list_of_keys_dict[week] = {} # Get variable key dict with values of table key_dict = {} for key in list_of_keys: key_dict[key] = table_object[key] # Compress Data by Calendar week for key in key_dict.keys(): all_weeks = compress_data_by_key_list(key_dict[key], key_list=calendar_weeks_list) if key == 'Task': for idx in range(len(all_weeks)): all_weeks[idx] = list(set(all_weeks[idx])) for week_indx, week in enumerate(sortet_calendar_weeks): if c == 0: week_keys_to_list_of_keys_dict[week][key] = all_weeks[week_indx] else: if isinstance(all_weeks[week_indx], int): week_keys_to_list_of_keys_dict[week][key] += all_weeks[week_indx] elif isinstance(all_weeks[week_indx], list): week_keys_to_list_of_keys_dict[week][key] += all_weeks[week_indx] # Change to Append if needed else: # Read Data read_table_object = Read_Table(pathes) table_object = read_table_object.table_to_dict() # Calendar Week calendar_weeks_list = table_object['Kalenderwoche'] sortet_calendar_weeks = list(set(calendar_weeks_list)) sortet_calendar_weeks.sort() # Filter Data customer_filter_value_indices = [] for task_value_idx in range(len(table_object['Task'])): task_value = table_object['Task'][task_value_idx] if task_value.startswith('root/' + OUTPUT_CUSTOMER): customer_filter_value_indices.append(task_value_idx) if OUTPUT_MONTH > 9: date_filter = str(OUTPUT_MONTH) + '/' + str(OUTPUT_YEAR) else: date_filter = '0' + str(OUTPUT_MONTH) + '/' + str(OUTPUT_YEAR) date_filter_value_indices = [] for date_value_idx in range(len(table_object['Datum'])): date_value = table_object['Datum'][date_value_idx] if date_value.endswith(date_filter): date_filter_value_indices.append(date_value_idx) customer_and_date_filtered_indices = list(set(customer_filter_value_indices) & set(date_filter_value_indices)) for key in table_object: table_object[key] = [table_object[key][idx] for idx in customer_and_date_filtered_indices] # Add new Weeks to dict for week in sortet_calendar_weeks: if week in week_keys_to_list_of_keys_dict: pass else: week_keys_to_list_of_keys_dict[week] = {} # Get variable key dict with values of table key_dict = {} for key in list_of_keys: key_dict[key] = table_object[key] # Compress Data by Calendar week compressed_lists = [] for key in key_dict.keys(): all_weeks = compress_data_by_key_list(key_dict[key], key_list=calendar_weeks_list) if key == 'Task': for idx in range(len(all_weeks)): all_weeks[idx] = list(set(all_weeks[idx])) for week_indx, week in enumerate(sortet_calendar_weeks): week_keys_to_list_of_keys_dict[week][key] = all_weeks[week_indx] return week_keys_to_list_of_keys_dict if __name__ == '__main__': table_object = main_data_collection([TABLE_PATH, TABLE_PATH])