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