123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209 |
- 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]
- 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))
- 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']
- # Add new Weeks to dict
- for week in list(set(calendar_weeks_list)):
- 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(list(set(calendar_weeks_list))):
- 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']
- # 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 list(set(calendar_weeks_list)):
- 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(list(set(calendar_weeks_list))):
- 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])
|