123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596 |
- 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()
|