Tools/fast_excel_to_bill/transform_excel_data_to_bill_data/time_recorder_format_main.py

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])