Lowerhousing_GV12/Lowerhousing/Time_Analysis.py

import sys
import os
import shutil
import pprint
import datetime
from datetime import datetime as DT
import time as t
import json
import sqlite3
from sqlite3 import Error
import matplotlib.pyplot as plt
import json
import numpy as np
import pandas as pd
import matplotlib.dates as mdates




def create_connection(db):
    try:
        conn = sqlite3.connect(db)
        return conn
    except Error as e:
        print(e)
    return None


def Task_a0(conn):
    f = open('taska0.txt', 'a')
    cur = conn.cursor()
    duration_p_part = {}
    parts = []
    for part_sql in cur.execute("select instance from Instances where called_by='179' or (called_by = 'Null' and level='2') order by instance asc"):
        part = part_sql[0]
        parts.append(part)
    for lv2_inst in parts:
        cur.execute("select instance from instances where called_by='" + lv2_inst + "' order by instance asc")
        lv3 = cur.fetchall()
        duration = 0
        for lv3_inst in lv3:
            template = lv3_inst[0]
            cur.execute("select instance from instances i where called_by = '" + template + "' order by i.instance asc")
            lv4 = cur.fetchall()
            for lv4_inst in lv4:
                if not lv4_inst is None:
                    log = lv4_inst[0]
                    print("", file=f)
                    print("P" + log, file=f)
                    print("", file=f)
                    first = True
                    for time in cur.execute("select m.timestamp, m.level4_timestamp, m.name, m.value, log.instance from machining m join logEntries log on (log.timestamp=m.level4_timestamp and log.activity = m.level4_activity and log.step_id=m.level4_step_id) where log.instance='" + log + "' order by m.timestamp asc limit 30"):
                        if first:
                            time_old = DT.strptime(time[0], '%Y-%m-%d' + 'T' + '%H:%M:%S.%f')
                            first = False
                        #if time[2] == 'Program/actBlock':  # or time[2] == 'Program/blockNoStr':
                        time_new = DT.strptime(time[0], '%Y-%m-%d' + 'T' + '%H:%M:%S.%f')
                        diff = time_new - time_old
                        new_duration = diff.microseconds / 1000000
                        duration = duration + new_duration
                        print(time[2], file = f)
                        print(duration, file=f)
                        time_old = time_new
                        print(new_duration, file=f)
                        print(duration, file=f)
                        print("-------", file=f)
        duration_p_part[lv2_inst] = duration / 60
    print(duration_p_part)
    print(duration_p_part, file=f)


def Task_a1(conn):
    f = open('taska1.txt', 'a')
    cur = conn.cursor()
    optimum_dur_diff = 0
    overlap_dur_diff = 0
    duration_p_part = {}
    parts=[]
    for part_sql in cur.execute("select instance from instances where called_by='179' or (called_by = 'Null' and level='2') order by instance asc"):
        part = part_sql[0]
        parts.append(part)
        # print(parts)
    count = 0
    print(parts)
    for lv2_inst in parts:
        cur.execute("select instance from instances where called_by='" + lv2_inst + "' order by instance asc")
        lv3 = cur.fetchall()
        duration = 0
        for lv3_inst in lv3:
            template = lv3_inst[0]
            cur.execute("select instance from instances i where called_by = '" + template + "' order by i.instance asc")
            lv4 = cur.fetchall()
            overlap = False
            for lv4_inst in lv4:
                if not lv4_inst is None:
                    log = lv4_inst[0]
                    print("", file=f)
                    print("P" + log, file=f)
                    print("", file=f)
                    first = True
                    skip = False
                    feedrate = 10000
                    #spindle = 10000

                    for time in cur.execute("select m.timestamp, m.level4_timestamp, m.name, m.value, log.instance from machining m join logEntries log on (log.timestamp=m.level4_timestamp and log.activity = m.level4_activity and log.step_id=m.level4_step_id) where log.instance='" + log + "' order by m.timestamp asc"):
                        if first:
                            time_old = DT.strptime(time[0], '%Y-%m-%d' + 'T' + '%H:%M:%S.%f')
                            first = False
                        if time[2] == 'Program/actBlock':  # or time[2] == 'Program/blockNoStr':
                            #print(time[3])
                            n = time[3].split(" ")[0]
                            if len(n) <= 0 or '_' in n or 'N' not in n:
                                    n = 'N0'
                            if overlap and 'N' in time[3].split(" ")[0]:
                                print("N: " + n, file=f)
                                print("N_old: " + n_old, file=f)

                                if int(n[1:]) <= int(n_old[1:]): # and n != 'N0':
                                    #try:
                                        first = True
                                        print("SKIP", file=f)
                                        skip = True
                                else:
                                    skip = False
                        if not skip:
                            time_new = DT.strptime(time[0], '%Y-%m-%d' + 'T' + '%H:%M:%S.%f')
                            diff = time_new - time_old
                            new_duration = float(diff.microseconds / 1000000)
                            if time[2] == 'Axis/feedRateOvr':
                                feedrate =int(float(time[3])*100)
                            if feedrate != 10000:
                                optimum_dur_diff = optimum_dur_diff + new_duration - new_duration * feedrate/10000
                                new_duration = new_duration * feedrate/10000
                            duration = duration + new_duration
                            print(duration, file = f)
                            time_old = time_new
                            print(time[2], file=f)
                            print(new_duration, file=f)
                            print(duration, file=f)
                            print("-------", file=f)
                        else:
                            time_new = DT.strptime(time[0], '%Y-%m-%d' + 'T' + '%H:%M:%S.%f')
                            diff = time_new - time_old
                            overlap_dur_diff = overlap_dur_diff + diff.microseconds / 1000000
                            time_old=time_new
                    if len(lv4) > 1:
                        overlap = True
                        n_old = n;
        count += 1
        duration_p_part[lv2_inst] = duration / 60
    print(duration_p_part, file = f)
    print(optimum_dur_diff, file = f)
    print(overlap_dur_diff, file=f)
    return duration_p_part

def Task_c(conn):
    f = open('taskC.txt', 'a')
    cur = conn.cursor()
    optimum_dur_diff = 0
    overlap_dur_diff = 0
    duration_all_parts = {}
    parts=[]
    for part_sql in cur.execute("select instance from instances where called_by='179' or (called_by = 'Null' and level='2') order by instance asc"):
        part = part_sql[0]
        parts.append(part)
        # print(parts)
    count = 0
    print(parts)
    for lv2_inst in parts:
        cur.execute("select instance from instances where called_by='" + lv2_inst + "' order by instance asc")
        lv3 = cur.fetchall()
        duration_p_part = {}
        for lv3_inst in lv3:
            template = lv3_inst[0]
            duration = 0
            cur.execute("select instance from instances i where called_by = '" + template + "' order by i.instance asc")
            lv4 = cur.fetchall()
            overlap = False
            for lv4_inst in lv4:
                if not lv4_inst is None:
                    log = lv4_inst[0]
                    print("", file=f)
                    print("P" + log, file=f)
                    print("", file=f)
                    first = True
                    skip = False
                    feedrate = 10000
                    #spindle = 10000

                    for time in cur.execute("select m.timestamp, m.level4_timestamp, m.name, m.value, log.instance from machining m join logEntries log on (log.timestamp=m.level4_timestamp and log.activity = m.level4_activity and log.step_id=m.level4_step_id) where log.instance='" + log + "' order by m.timestamp asc"):
                        if first:
                            time_old = DT.strptime(time[0], '%Y-%m-%d' + 'T' + '%H:%M:%S.%f')
                            first = False
                        if time[2] == 'Program/actBlock':  # or time[2] == 'Program/blockNoStr':
                            #print(time[3])
                            n = time[3].split(" ")[0]
                            if len(n) <= 0 or '_' in n or 'N' not in n:
                                    n = 'N0'
                            if overlap and 'N' in time[3].split(" ")[0]:
                                print("N: " + n, file=f)
                                print("N_old: " + n_old, file=f)

                                if int(n[1:]) <= int(n_old[1:]) and n != 'N0':
                                    #try:
                                        first = True
                                        print("SKIP", file=f)
                                        skip = True
                                else:
                                    skip = False
                        if not skip:
                            time_new = DT.strptime(time[0], '%Y-%m-%d' + 'T' + '%H:%M:%S.%f')
                            diff = time_new - time_old
                            new_duration = float(diff.microseconds / 1000000)
                            if time[2] == 'Axis/feedRateOvr':
                                feedrate =int(float(time[3])*100)
                            if feedrate != 10000:
                                optimum_dur_diff = optimum_dur_diff + new_duration - new_duration * feedrate/10000
                                new_duration = new_duration * feedrate/10000
                            duration = duration + new_duration
                            print(duration, file = f)
                            time_old = time_new
                            print(time[2], file=f)
                            print(new_duration, file=f)
                            print(duration, file=f)
                            print("-------", file=f)
                        else:
                            time_new = DT.strptime(time[0], '%Y-%m-%d' + 'T' + '%H:%M:%S.%f')
                            diff = time_new - time_old
                            overlap_dur_diff = overlap_dur_diff + diff.microseconds / 1000000
                            time_old=time_new
                    if len(lv4) > 1:
                        overlap = True
                        n_old = n;
            duration_p_part[lv3_inst[0]] = duration / 60

            print(duration_p_part, file = f)
        duration_all_parts[lv2_inst]=duration_p_part
    print("----------------")
    print("Here comes the feedrate saving")
    print(optimum_dur_diff)
    print("----------------")
    print("Here comes the overlap saving")
    print(overlap_dur_diff)
    print("----------------")
    print("Here comes the total result")
    print(duration_all_parts)
    print(optimum_dur_diff, file = f)
    print(overlap_dur_diff, file=f)
    return duration_all_parts


def create_plot(duration_p_part):
    print(duration_p_part)
    result_array=[0]
    first = True
    instances=[]
    results = []
    for m in duration_p_part:
        print()
        if duration_p_part[m] == 0:
            continue
        instances.append(m)
        if first:
            l1 = float(duration_p_part[m])
            first = False
        else:
            l2 = float(duration_p_part[m]);
            learning_curve = (l2 / l1)

            converted_values = round(learning_curve,2)-1
            print(converted_values)
            result_array.append(converted_values)
            print("The Learning curve for Part_" + str(l1) + "/Part_" + str(l2) + " = " + str(converted_values)+ "%")
            l1=l2
            results.append(l2)

    #Used for ploting the following link: https://stackoverflow.com/questions/48413069/plot-x-axis-with-string-array-as-in-the-same-order-in-original-array-and-not-sor
    print(result_array)
    print(instances)
    percentage = np.array(result_array)
    instance = np.array(instances)
    plt.xticks(range(5),instance)
    #plt.xticks(percentage, results)
    #plt.yticks(percentage, instances)
    #plt.ylabel('Relative Time Movement')
    #plt.xlabel('Parts Comparison')
    plt.plot(percentage)
    plt.show()

def create_plot_abs(duration_p_part):
    print(duration_p_part)
    machine_keys = duration_p_part.keys()
    result_array=[]
    first = True
    instances=[]
    results = []
    for m in duration_p_part:
        print()
        if duration_p_part[m] == 0:
            continue
        instances.append(m)
        #l1 = float(duration_p_part[m])
        #first = False

        l2 = float(duration_p_part[m]);
        learning_curve = l2/60

        converted_values = round(learning_curve,2)
        print(converted_values)
        result_array.append(converted_values)
        print("The Learning curve for Part_" + str(l2) + " = " + str(converted_values)+ "%")
        l1=l2
        results.append(l2)
    #Used for ploting the following link: https://stackoverflow.com/questions/48413069/plot-x-axis-with-string-array-as-in-the-same-order-in-original-array-and-not-sor
    print(result_array)
    percentage = np.array(result_array)
    instances = np.array(instances)
    plt.xticks(range(5),instances)
    #plt.yticks(percentage, instances)
    #plt.ylabel('Production Duration hours')
    #plt.xlabel('Parts Comparison')
    plt.plot(percentage)
    plt.show()

def plot_time_series(b_result_instances):
    for ins in b_result_instances:
        print("-------------------")
        print(ins)
        print("-------------------")
        result_array = []
        instances = []
        min = []
        max = []
        myprint(b_result_instances[ins], result_array, instances, min, max)
        if instances and result_array:
            #print(instances)
            #print(result_array)
    #Used for ploting the following link: https://stackoverflow.com/questions/48413069/plot-x-axis-with-string-array-as-in-the-same-order-in-original-array-and-not-sor
    #print(result_array)
            durations = np.array(result_array)
            blocks = np.array(instances)
            sliced_durations = durations[1::4]
            sliced_blocks = blocks[1::4]
            #plt.xticks(percentage, results)
            #plt.yticks(percentage, instances)
            #plt.xticks(range(40),sliced_blocks,rotation=75)
            # print("-------------------")
            # print(durations)
            # print(blocks)
            # print("-------------------")
            #plt.subplot(1,2,1)
            #plt.ylabel('Relative Time Movement')
            #plt.xlabel('Blocks ')
            plt.xticks([])
            #plt.xticks(range(len(durations)), blocks)
            plt.plot(blocks,durations)

            #to see the start and end of each log file
            # for mi in min:
            #     plt.axhline(y=mi, color="red")
            #
            # for ma in max:
            #     plt.axhline(y=ma, color="blue")
            #plt.subplot(1,2,2)
            #plt.ylabel('Sliced Relative Time Movement')
            #plt.xlabel('Blocks')
            #plt.xticks([])
            #plt.xticks(range(len(sliced_durations)),sliced_blocks)
            #plt.plot(sliced_blocks,sliced_durations)

            #plt.plot(sliced_blocks, sliced_durations)

            plt.xlabel('Machine Code Sequence (NC-value)')
            plt.ylabel('Production Time (s)')
            plt.show()



def myprint(d, result_array, instances, mins, maxs):
    #https://stackoverflow.com/questions/10756427/loop-through-all-nested-dictionary-values
    for k, v in d.items():
        if isinstance(v, dict):
            #if len(v) > 1:
            myprint(v, result_array, instances, mins, maxs)
        else:
            if k is not None:
                if k == "min":
                    mins.append(v)
                if k == "max":
                    maxs.append(v)
                else:
                    instances.append(k)
                    result_array.append(v)


'''
def print_graph(framelist2):
    time_list = []
    task_list = []
    sublist = []
    framelist=[]
    for lv2 in framelist2:
        for lv3 in framelist2[lv2]:

            for value in framelist2[lv2][lv3]:
                time_list.append(framelist2[lv2][lv3][value])
                task_list.append(value)
                sublist=[value, framelist2[lv2][lv3][value]]
                framelist.append(sublist)
        print(time_list)
        print(task_list)
        print(sublist)
        ts = pd.DataFrame(framelist, columns=list('xy'))
        xax = np.arange(0, len(ts), 1)
        fig, ax = plt.subplots(1, 1)
        ax.plot(ts['x'], ts['y'])
        #ax.set_xticks(xax)
        #ax.set_xticks(task_list)
        #ax.set(np.arange(task_list))
        ax.set_xticks(np.arange(0, len(ts['x']), 4))
        #ax.set_xticklabels(ts['x'])

        plt.xticks(rotation=70, fontsize=11)
        plt.subplots_adjust(left=0.3, bottom=0.35)

        # # How to change interval e.g. every 20th timestamp label
        # drange = pd.date_range(ts['x'][0], periods=len(ts), freq="S")
        # values = task_list
        # df2 = pd.DataFrame(values, index=drange)
        # fig, ax = plt.subplots(1, 1)
        # ax.plot(df2.index, values)
        # ax.set_xticks(pd.date_range(ts['x'][0], periods=len(ts), freq='S'))
        # # ax.set_xticklabels(df2.index)
        #
        # plt.subplots_adjust(left=0.3, bottom=0.35)
        plt.show()
        time_list = []
        task_list = []
        sublist = []
        framelist = []
    fig.savefig("graphics.png")


    #ax.xaxis.set_major_formatter(mdates.DateFormatter('%Y-%m-%d' + 'T' + '%H:%M:%S'))
    #print(ts['x'][0])
    #timearray = np.arange(ts['x'][0], ts['x'][len(ts)-1],np.timedelta64(20,'s'), dtype='datetime64')
    #ax.set_xticks(timearray)
'''

def Task_b(conn):
    f = open('taskB.txt', 'a')
    amk = open('amk.txt', 'a')
    cur = conn.cursor()
    duration_p_part = {}
    parts = []
    b_result = {}

    for part_sql in cur.execute(
            "select instance from instances where called_by='179' or (called_by = 'Null' and level='2') order by instance asc"):
        part = part_sql[0]
        parts.append(part)

    for lv2_inst in parts:
        #b_duration = 0
        b_result[lv2_inst] = {}
        duration_p_part[lv2_inst] = {}

        cur.execute(
            "select instance from instances where called_by='" + lv2_inst + "' order by instance asc")
        lv3 = cur.fetchall()
        duration = 0
        #the following counter helps to differntiate among the various machinings for the corresponding level 3 instances
        lv3_counter = 0
        for lv3_inst in lv3:
            template = lv3_inst[0]

            lv3_counter += 1

            cur.execute(
                "select instance from instances i where called_by = '" + template + "' order by i.instance asc")
            lv4 = cur.fetchall()
            overlap_n_value = 0
            for lv4_inst in lv4:
                # print("-------------------")
                # print(lv2_inst)
                # print("-------------------")
                # print(lv3_inst[0])
                # print(lv3_counter)
                # print("-------------------")
                if not lv4_inst is None:
                    log = lv4_inst[0]

                    b_result[lv2_inst]["p" + log] = {}
                    first = True
                    first_duration = True
                    n_value_counter = 0
                    # n_skip_counter = 0

                    for time in cur.execute(
                            "select m.timestamp, m.level4_timestamp, m.name, m.value, log.instance from machining m join logEntries log on (log.timestamp=m.level4_timestamp and log.activity = m.level4_activity and log.step_id=m.level4_step_id) where log.instance='" + log + "' order by m.timestamp asc"):
                        if first:
                            time_old = DT.strptime(time[0], '%Y-%m-%d' + 'T' + '%H:%M:%S.%f')
                            first = False
                        # if time[2] == 'Program/actBlock':  # or time[2] == 'Program/blockNoStr':
                        time_new = DT.strptime(time[0], '%Y-%m-%d' + 'T' + '%H:%M:%S.%f')


                        diff = time_new - time_old
                        new_duration = diff.microseconds / 1000000
                        duration = duration + new_duration
                        #b_duration = b_duration + new_duration
                        if time[2] == 'Program/actBlock':
                            n = time[3].split(" ")[0]

                            if 'N' in n and '_' not in n and len(n) > 0 and "None" not in n:
                                #To assign to a variable to check overlaps within a certain level 4 log
                                # if n_value_counter == 0:
                                #     n_value_counter = int(n[1:])
                                #     print(n_value_counter)
                                #     print("-------------------")
                                #     print(int(n[1:]))

                                    #in this case we have an overlap ... whats next
                                    #go to the start and send it to the dictionary including instance
                                    #skip until the condition is not met
                                    #at the end mark final n-value for this instance
                                    #if int(n[1:]) < overlap_n_value:
                                # if first_duration:
                                #     b_result[lv2_inst]["p" + log]["min"] = duration
                                #     first_duration = False

                                    b_result[lv2_inst]["p" + log][str(lv3_counter) + n] = duration

                                # b_result[lv2_inst]["p" + log]["max"] = duration

                                # print("-------------------")
                                # print(n)
                                # print("-------------------")
                                # For checking if overlaps happened within a log instance
                                # if n_value_counter >= int(n[1:]):
                                #     print("-------------------")
                                #     print("Biatch i found something")
                                #     print("-------------------")
                                #     duration_p_part[lv2_inst]["p" + log] = {}
                                #     duration_p_part[lv2_inst]["p" + log][str(lv3_counter) + n] = duration
                                # n_value_counter = int(n[1:])

                        # if 'N' in time[3]:
                        #     n = time[3].split(" ")[0]
                        #     if n_value_counter < 3 or n_skip_counter > 75:
                        #         b_result[lv2_inst]["p" + log]["_" + n] = b_duration
                        #         n_value_counter += 1
                        #         n_skip_counter = 0
                        #     else:                        #         n_skip_counter += 1
                        time_old = time_new

                    # if len(lv4) > 1 and overlap_n_value == 0:
                    #     overlap_n_value = int(n[1:])
        #duration_p_part[lv2_inst] = duration / 60

    print("------------------")
    print("Here comes the part durations")
    #print(duration_p_part)
    # print("------------------")
    # print("Here comes the part b_durations")
    # print(b_duration)
    print("-------", file=f)
    print(duration_p_part, file=amk)
    print("-------", file=f)
    print("-------", file=f)
    print(b_result, file=f)
    print("-------", file=f)
    #print(duration_p_part, file=f)
    return b_result

print("Hello from se other side")
print(os.getcwd())
root_path = os.getcwd()
abs_path = os.path.dirname(os.path.abspath("Business/GV12_Lowerhousing/Uni-Sachen/Task_2b/"))
print("---------------")
print(abs_path)
db = os.path.join(root_path,"BIII.db")
conn = create_connection(db)



#Task_a0(conn)
#duration_p_part=Task_a1(conn)
#create_plot(duration_p_part)
#create_plot_abs(duration_p_part)
task_b_all_instances = Task_b(conn)
plot_time_series(task_b_all_instances)
#framelist = Task_c(conn)
#print_graph(framelist)
conn.close()