Lowerhousing_GV12/GV12/Operations.py

import Support as sp
import numpy as np
import Level_2 as LV2
import Level_4 as LV4
import Parts as part
import Connection as connect
import Graph as gr
from datetime import datetime as DT

def get_duration_by_break(lv4_instances, dur_break, conn):
    #TODO CREATE LIST WITH SINGLE Durations to calc quantile
    t_quartile = connect.get_third_quartile(conn)
    dataset = np.array([], dtype=[('keys', int), ('data', float)])
    cur = conn.cursor()
    liststring = sp.list_to_string(lv4_instances)
    query = "select m.timestamp, m.name, m.value, m.level4_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 and log.instance=m.level4_instance) where m.level4_instance in (" + liststring + ") order by m.timestamp"
    cur.execute(query)
    time = cur.fetchall()
    first = True;
    test_counter = 0
    for timestamp in time:

        if first:
            begin_time = timestamp[0]
            instance_old = timestamp[3]
            first = False
        end_time = timestamp[0]
        instance_new = timestamp[3]
        begin_time = DT.strptime(begin_time.split(".")[0], '%Y-%m-%d' + ' ' + '%H:%M:%S')
        end_time = DT.strptime(end_time.split(".")[0], '%Y-%m-%d' + ' ' + '%H:%M:%S')
        diff = (end_time - begin_time).total_seconds() / 60
        # if diff > dur_break:
        #     if instance_new != instance_old:
        #         f = open('break_inbetween.txt', 'a')
        #         print(str(timestamp[0]) + str(", ") + str(timestamp[1]) + str(", ") + str(begin_time) + str(", ") + str(
        #             end_time) + str(", ") + str(diff), file=f)
        #         f.close()
        #     else:
        #         f = open('break_within.txt', 'a')
        #         print(str(timestamp[3]) + str(timestamp[0]) + str(", ") + str(timestamp[1]) + str(", ") + str(begin_time) + str(", ") + str(end_time) + str(", ") + str(diff), file=f)
        #         f.close()
        # else:

        if diff <= dur_break:

            #print({int(timestamp[3]):float(diff)})
            if int(timestamp[3]) in dataset['keys']:
                index = int(np.where(dataset['keys'] == int(timestamp[3]))[0])
                old_value = dataset[index]['data']
                new_value = old_value + diff
                dataset[index]['data'] = new_value
            else:
                dataset = np.append(dataset, np.array([(int(timestamp[3]), float(diff))], dtype=dataset.dtype))
        else:
            test_counter += 1
            #print("I am again bigger for the " + str(test_counter) + "thats the value i tried to insert: " + str(diff))

            if int(timestamp[3]) in dataset['keys']:
                index = int(np.where(dataset['keys'] == int(timestamp[3]))[0])
                old_value = dataset[index]['data']
                new_value = old_value + float(t_quartile)
                dataset[index]['data'] = new_value
            else:
                dataset = np.append(dataset, np.array([(int(timestamp[3]), float(t_quartile))], dtype=dataset.dtype))

        begin_time = timestamp[0]

        instance_old = instance_new

    return dataset



def get_breaks(dur_break, conn):
    level4_instances = LV4.get_level4Instances(conn)
    sortedlevel4_instances = LV4.orderLV4InstancesChronoligically(level4_instances, conn)
    converter = sp.convert_part_to_lv4(conn)
    breakswithin = np.array([], dtype=[('keys', int), ('data', float)])
    breaksinbetween = np.array([], dtype=[('keys', int), ('data', float)])
    cur = conn.cursor()
    liststring = sp.list_to_string(sortedlevel4_instances)
    query = "select m.timestamp, m.name, m.value, m.level4_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 and log.instance=m.level4_instance) where m.level4_instance in (" + liststring + ") order by m.timestamp"
    cur.execute(query)
    time = cur.fetchall()
    first = True;
    for timestamp in time:
        if first:
            begin_time = timestamp[0]
            instance_old = timestamp[3]
            first = False
        end_time = timestamp[0]
        instance_new = timestamp[3]
        begin_time = DT.strptime(begin_time.split(".")[0], '%Y-%m-%d' + ' ' + '%H:%M:%S')
        end_time = DT.strptime(end_time.split(".")[0], '%Y-%m-%d' + ' ' + '%H:%M:%S')
        diff = (end_time - begin_time).total_seconds() / 60
        if diff > dur_break:
            #print("DIFF: " + str(diff))
            if instance_new != instance_old:
                if int(timestamp[3]) in breaksinbetween['keys']:
                    #print("EXISTS1")
                    for entry in breaksinbetween:
                        if int(entry['keys'])==int(timestamp[3]):
                            dur_new = float(diff) + float(entry['data'])

                else:
                    dur_new = float(diff)
                breaksinbetween = np.append(breaksinbetween, np.array([(int(timestamp[3]), float(dur_new))], dtype=breaksinbetween.dtype))
            else:
                if int(timestamp[3]) in breakswithin['keys']:
                    #print("EXISTS2")
                    for entry in breakswithin:
                        if int(entry['keys'])==int(timestamp[3]):
                            dur_new = float(diff) + float(entry['data'])
                else:
                     dur_new = float(diff)
                breakswithin= np.append(breakswithin, np.array([(int(timestamp[3]), float(dur_new))], dtype=breakswithin.dtype))
            #print("DUR_NEW: " + str(dur_new))
        begin_time = timestamp[0]
        instance_old = instance_new
    print("BINBETWEEN")
    print(breaksinbetween)
    print("BWITHIN")
    print(breakswithin)
    i = 0
    indexToRemove = []
    for entry in breaksinbetween:
        if entry['keys'] in converter:
            breaksinbetween[i]['keys'] = converter[entry['keys']]
        else:
            indexToRemove.append(i)
        i += 1
    durationWOBreaks = np.delete(breaksinbetween, indexToRemove, axis=0)
    dbstring = breaksinbetween.tolist()
    dbstring = str(dbstring)
    dbstring = dbstring.strip('[]')
    dbstring = dbstring.replace("), (", ");(")
    connect.write_result_to_DB(str("Duration_BreaksInbetween" + str(dur_break)), dbstring, conn)
    i = 0
    indexToRemove = []
    for entry in breakswithin:
        if entry['keys'] in converter:
            breakswithin[i]['keys'] = converter[entry['keys']]
        else:
            indexToRemove.append(i)
        i += 1
    durationWOBreaks = np.delete(breakswithin, indexToRemove, axis=0)
    dbstring = breakswithin.tolist()
    dbstring = str(dbstring)
    dbstring = dbstring.strip('[]')
    dbstring = dbstring.replace("), (", ");(")
    connect.write_result_to_DB(str("Duration_BreaksWithin" + str(dur_break)), dbstring, conn)
    dataset={"BreakWithin": breakswithin, "BreakInbetween": breaksinbetween}
    return dataset


def get_all_parts(conn):
    level4_instances = LV4.get_level4Instances(conn)
    sortedlevel4_instances = LV4.orderLV4InstancesChronoligically(level4_instances, conn)
    level2_instances = LV2.get_level2Instance_by_LV4Instance(sortedlevel4_instances, conn)
    sortedlevel2_instances = LV2.orderLV2InstancesChronoligically(level2_instances, conn)
    parts = part.get_part_by_lv2Instance(sortedlevel2_instances, 'all', conn)
    sortedParts = part.orderPartsChronoligically(parts, conn)
    connect.write_result_to_DB("get_all_parts", sortedParts, conn)
    print(sortedParts)
    return sortedParts


def get_nok_parts(conn):
    level4_instances = LV4.get_level4Instances(conn)
    sortedlevel4_instances = LV4.orderLV4InstancesChronoligically(level4_instances, conn)
    level2_instances = LV2.get_level2Instance_by_LV4Instance(sortedlevel4_instances, conn)
    sortedlevel2_instances = LV2.orderLV2InstancesChronoligically(level2_instances, conn)
    parts = part.get_part_by_lv2Instance(sortedlevel2_instances, 'nok', conn)
    sortedParts = part.orderPartsChronoligically(parts, conn)
    print(sortedParts)
    return sortedParts

def get_durations_lv4(conn):
    level4_instances = LV4.get_level4Instances(conn)
    sortedlevel4_instances = LV4.orderLV4InstancesChronoligically(level4_instances, conn)
    #durationsWOLunch = get_duration_by_break(sortedlevel4_instances, 45, conn)
    #durationWOWeekend = get_duration_by_break(sortedlevel4_instances, 480, conn)
    durationWOBreaks = get_duration_by_break(sortedlevel4_instances, 1, conn)
    print(durationWOBreaks)
    return durationWOBreaks


def get_durations_part(break_dur, conn):
    level4_instances = LV4.get_level4Instances(conn)
    sortedlevel4_instances = LV4.orderLV4InstancesChronoligically(level4_instances, conn)
    converter = sp.convert_part_to_lv4(conn)
    #print(converter)
    durationWOBreaks = get_duration_by_break(sortedlevel4_instances, break_dur, conn)
    #durationWOBreaks = np.array([(int(838),float(12.4)),(int(116),float(12.4))], dtype=[('keys', int), ('data', float)])

    i = 0
    #print(durationWOBreaks)
    indexToRemove = []
    for entry in durationWOBreaks:
        if entry['keys']in converter:
            durationWOBreaks[i]['keys']=converter[entry['keys']]
        else:
            indexToRemove.append(i)
        i += 1
    durationWOBreaks = np.delete(durationWOBreaks,indexToRemove, axis=0)
    #print(durationWOBreaks)
    dbstring = durationWOBreaks.tolist()
    dbstring = str(dbstring)
    dbstring = dbstring.strip('[]')
    dbstring = dbstring.replace("), (", ");(")
    #print("string")
    #print(dbstring)
    connect.write_result_to_DB(str("Duration_Part_Breakl_" + str(break_dur)),dbstring,conn)
    return durationWOBreaks


def aggregate_duration(durations):
    dur_aggr = 0
    result = np.array([], dtype=[('keys', int), ('data', float)])
    #print(durations)
    for entry in durations:
        dur_aggr = dur_aggr + entry['data']
        result = np.append(result, np.array([(entry['keys'],dur_aggr)],dtype=result.dtype))
    print(result)
    return result


def get_break_length_stat(conn):
    dataset = np.array([], dtype=float)
    cur = conn.cursor()
    query = "select m.timestamp 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 and log.instance=m.level4_instance) order by m.timestamp"
    cur.execute(query)
    time = cur.fetchall()
    test_counter = 0
    cool_counter = 0
    if not time is None:
        #print(time)
        first = True;
        for timestamp in time:
            if first:
                begin_time = timestamp[0]
                first = False
            end_time = timestamp[0]
            begin_time = DT.strptime(begin_time.split(".")[0], '%Y-%m-%d' + ' ' + '%H:%M:%S')
            end_time = DT.strptime(end_time.split(".")[0], '%Y-%m-%d' + ' ' + '%H:%M:%S')
            diff = (end_time - begin_time).total_seconds() / 60
            if diff>0.0167:
                test_counter += 1
            if diff>0:
                cool_counter += 1
                dataset = np.append(dataset,np.array([float(diff)],dtype=dataset.dtype))
            begin_time = timestamp[0]
    #print(dataset)
    mid = np.percentile(dataset,80)
    quart = np.percentile(dataset,90)
    quart_3 = np.percentile(dataset,95)
    limit = (np.percentile(dataset,99))

    print(test_counter)
    print("------vs------")
    print(cool_counter)
    print(mid)
    print(quart)
    print(quart_3)

    #connect.write_result_to_DB("first_quartile", quart,conn)
    #connect.write_result_to_DB("second_quartile", mid, conn)
    #connect.write_result_to_DB("third_quartile", quart_3, conn)

def get_all_measures(conn):
    f = open('all_measures.txt', 'a')
    cur = conn.cursor()
    measures_dict = {}

    cur.execute("select mt.part, mt.measuredAttr, md.measuredDet, md.measuredval, md.status from MeasureTot mt join MeasureDet md on (md.timestamp=mt.timestamp and md.MeasuredAttr=mt.MeasuredAttr) order by mt.timestamp asc")
    measures = cur.fetchall()

    measure_dict = {}
    part_no = ""
    for measure in measures:
        if part_no != measure[0]:
            measure_dict = {}
        part_no=measure[0]
        part = {measure[0]:measure_dict}
        dict_entry = {str(measure[1])+"-"+str(measure[2]):measure[3]}
        part[measure[0]].update(dict_entry)
        measures_dict.update(part)
    print(measures_dict, file=f)
    return measures_dict


def get_durations_zylinder(conn):
    # ------------- get breaks from db ------------------------
    query = "select value from results where category ='Duration_Part_Breakl_0.08333333333333333' order by timestamp desc limit 1"
    result = np.array([], dtype=[('keys', int), ('data', float)])
    breaks = connect.get_result_from_db(query, result, conn)

    print('-----------------------------------------')
    # check the boxplot in order to define a threshold to find bar changes which is approximately 3.5
    print(np.percentile(breaks['data'], 75))
    print('-----------------------------------------')
    greater_values_i = np.where(breaks['data'] > 3.5)[0]

    # filter all instances with values above 3.5 minutes and make a new array
    filtered_result = np.array([], dtype=[('keys', int), ('data', float)])

    for number in greater_values_i:
        item = breaks[number]
        filtered_result = np.append(filtered_result,
                                    np.array([(int(item['keys']), float(item['data']))], dtype=filtered_result.dtype))

    print(filtered_result)
    gr.plot_duration_part(filtered_result)
    print('-----------------------------------------')

    # retrieve the corresponding z-values from the part
    final_result = np.array([], dtype=[('keys', int), ('data', float)])
    cur = conn.cursor()
    for key in filtered_result:
        if key['keys'] < 300:
            cur.execute(
                "Select distinct md.measuredVal from MeasureDet md join MeasureTot mt on(md.timestamp = mt.timestamp) where md.measuredAttr Like 'Zylinder%' and md.measuredDet='Durchmesser' and mt.part='" + str(
                    key['keys']) + "'")
            attr_value = cur.fetchone()
            final_result = np.append(final_result,
                                     np.array([(int(key['keys']), float(attr_value[0]))], dtype=final_result.dtype))
    return (final_result)

def outsiders(durations, conn):
    print("DURATION")
    print(durations)
    outsiders = np.array([], dtype=[('keys', int), ('data', float)])
    treshold_upper = np.percentile(durations['data'], 90)
    #treshold_lower = np.percentile(durations['data'], 10)
    #
    # print(treshold_lower)
    print(treshold_upper)
    for entry in durations:
        if entry['data']>treshold_upper:
            #if entry['data']>treshold_lower and entry['data']<treshold_upper:
            outsiders=np.append(outsiders,np.array([(int(entry['keys']),float(entry['data']))],dtype=outsiders.dtype))
    print("Outsiders")
    print(outsiders)
    #
    noks = get_nok_parts(conn)
    statusArr = np.array([], dtype=[('keys', int), ('dur', float), ('status', np.object)])
    minimum = min(outsiders['data'])
    counter = 0
    for entry in outsiders:
        diff = entry['data']-minimum
        if entry['keys'] in noks:
            statusArr = np.append(statusArr, np.array([(int(entry['keys']), float(diff), 'nok')], dtype=statusArr.dtype))
            counter = counter + 1
        else:
            statusArr = np.append(statusArr, np.array([(int(entry['keys']), float(diff), 'ok')], dtype=statusArr.dtype))
    print("Top 10%")
    print(statusArr)
    print(counter)