Lowerhousing_GV12/GV12/DB/BI_ReadLog.py

import sys
import os
import yaml
import shutil
import pprint
import lxml.etree as ET
import lxml.builder
import datetime
from datetime import datetime as DT
import time as t
import json
import sqlite3
from sqlite3 import Error

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


def check_folder(target_path,folder):
    if not os.path.exists(os.path.join(target_path, folder)):
        os.makedirs(os.path.join(target_path, folder))
    return os.path.join(target_path, folder)

def get_target_path(prod_step, root_path):
    switch = {
        "GV12 Order Processing": "level1",
        "GV12 Production": "level2",
        "GV12 Turn Production": "level3",
        "GV12 Turn Machining": "level4"
    }
    return check_folder(os.path.join(root_path, "task3/sortedTemplates"), switch[prod_step])


def check_timestamp(time, rootNode,search):
    #convert time-string to date_time
    call_node_list = []
    time_conv = time.split("+")[0]
    datetime_object = DT.strptime(time_conv, '%Y-%m-%d' + 'T' + '%H:%M:%S')
    time_upper = str(datetime_object + datetime.timedelta(0, 2)) + "+02:00"
    time_lower = str(datetime_object - datetime.timedelta(0, 2)) + "+02:00"
    time_lower = time_lower.split(" ")[0] + "T" + time_lower.split(" ")[1]
    time_upper = time_upper.split(" ")[0] + "T" + time_upper.split(" ")[1]

    #find suitable timestamp among all timestamps for call level
    time_range = rootNode.xpath("//" + search + "/@time")
    time_range.sort()
    for time in time_range:
        if time <= time_upper and time >= time_lower:
            call_node_list = rootNode.xpath("//" + search + "[@time = '" + time + "']")
           # print(call_node_list)
            break
        if time > time_upper:
            break
    return call_node_list

def create_json(root_path,list,file):
    with open(os.path.join(root_path, 'task3/'+file), 'w') as fp:
        json.dump(list, fp, indent=4)
    fp.close()


def categorize_logs(root_path, logs_path):
    origin_path = logs_path
    print(origin_path)
    for root, dirs, files in os.walk(origin_path):
        for file in files:
            with open(os.path.join(root, file), "r") as input_file:
                results = yaml.load_all(input_file)
                target_path=""
                for value in results:
                    if 'log' in value:
                        try:
                            val = value['log']['trace']['cpee:name']
                            target_path=get_target_path(val, root_path)
                            print(val)
                            break
                        except (KeyError, AttributeError):
                            continue
                input_file.close()
                shutil.move(os.path.join(root, file), os.path.join(target_path, file))


def xml_check(xml_file):
    if not os.path.isfile(xml_file):
        rootNode = ET.Element("ProcessInstances")
        tree = ET.ElementTree(rootNode)
        level1 = ET.SubElement(rootNode, "level1")
    else:
        tree = ET.parse(xml_file, ET.XMLParser(remove_blank_text=True))
        rootNode = tree.getroot()
        if tree.find('level1') is None:
            level1 = ET.SubElement(rootNode, "level1")
        else:
            level1 = tree.find('level1')
    return [tree,rootNode,level1]



def recreate_process(root_path, conn, level):
    # variables
    f = open('taska3.txt', 'a')
    cur = conn.cursor()
    origin_path = os.path.join(root_path, "task3/sortedTemplates/level"+str(level))
    counter = 0;
    oknok = {'ok':0, 'nok':0}
    ok = 0
    nok = 0
    # traverse through all logs
    for root, dirs, files in os.walk(origin_path):
        for file in files:
            added = False;
            print(file)
            with open(os.path.join(root, file), "r") as input_file:
                results = yaml.load_all(input_file)
                # traverse throug single log
                measured_result = 'NULL'
                for value in results:
                    #try:
                    if 'log' in value:
                        logname = value['log']['trace']['cpee:name']
                        uuid = value['log']['trace']['cpee:uuid']
                        instance = value['log']['trace']['concept:name']

                    if 'event' in value and value['event']['id:id'] == "external" and value['event']['cpee:lifecycle:transition'] == 'dataelements/change':
                        for listOut in value['event']['list']['data_values']:
                            if listOut=='qr' and len(value['event']['list']['data_values'][listOut])>0:
                                charge = value['event']['list']['data_values'][listOut].split(' ')[0]
                                part = value['event']['list']['data_values'][listOut].split(' ')[1]
                                #print("Charge: "+ charge + " Part: " + part)


                    if 'event' in value and not value['event']['id:id']=="external":
                        try:

                            time = value['event']['time:timestamp']
                            activity = value['event']['cpee:lifecycle:transition']
                            name = value['event']['concept:name']
                            step_id = value['event']['id:id']
                            val="";
                            if value['event']['lifecycle:transition'] == "start":
                                val = json.dumps(value['event']['list']['data_send'])

                            if 'data_values' in value['event']['list'].keys() and name == 'Measure with MicroVu' and activity == 'dataelements/change' :
                                # print(value['event']['list']['data_values']['status'])
                                #print("PART: " + str(part))
                                #print("AKT; " + str(value['event']['list']['data_values']['qr'].split(' ')[1]))

                                if value['event']['list']['data_values']['qr'].split(' ')[1]!=part:
                                    print("NOTPART: " + str(part))
                                    print("NOTAKT; " + str(value['event']['list']['data_values']['qr'].split(' ')[1]))
                                    continue
                                measured_result = 'ok'
                                if value['event']['list']['data_values']['status'] != 'ok':
                                    measured_result = 'nok'
                                query = "Update MeasureTot SET status = '" + measured_result + "' where part = '" + part + "'" # and status = 'NULL'"
                                #print(query)
                                cur.execute(query)
                                conn.commit()


                            if 'data_receiver' in value['event']['list'].keys() and name!='Fetch':
                                #print("ICHICHICH")
                                for listIn in value['event']['list']['data_receiver']:
                                    #print(listIn.keys())
                                    #print(listIn['name'])
                                    #print(listIn['data'])

                                    if 'results' in listIn['data']:
                                        if 'raw' in listIn['data'] and len(listIn['data']['raw']) > 0:
                                            # print("DOOF")
                                            # print(listIn['data']['raw']['Aufford QR-Code-Eingabe']['Eingabe'][0])
                                            if listIn['data']['raw']['Aufford QR-Code-Eingabe']['Eingabe'][0].split(" ")[1] != part:
                                                continue
                                       #print(listIn['data']['results'])
                                        for entry in listIn['data']['results']:



                                            messungen = listIn['data']['results'][entry]

                                            measured_attr = entry

                                            query = "Insert into MeasureTot('charge','part', 'status', 'measuredAttr', 'instance', 'timestamp') VALUES ('" + charge + "','" + part + "', '" + measured_result + "','" + measured_attr + "', '" + instance + "','" + time + "' )"
                                            #print(query)
                                            cur.execute(query)
                                            conn.commit()
                                            for messung in messungen:
                                                measured_det = messung
                                                measured_value = messungen[messung]['on_scale_from_zero_to_one']
                                                status = messungen[messung]['status']
                                                query = "Insert into MeasureDet('measuredDet','measuredVal', 'status', 'measuredAttr', 'timestamp') VALUES ('" + measured_det + "','" + str(measured_value) + "', '" + status + "','" + measured_attr + "', '" + time + "' )"
                                                cur.execute(query)
                                            conn.commit()
                                        #qrcode=listIn['data']['raw']['Aufford QR-Code-Eingabe']

                                val = json.dumps(value['event']['list']['data_receiver'])


                            if not added:
                                cur.execute("Select called_by from instances where instance = '" + instance + "'")
                                parent_inst = cur.fetchone()
                                if parent_inst is None:
                                    print(time)
                                    #print(len(time))
                                    time = time.split('+')[0]
                                    print(len(time))
                                    if len(time)>19:
                                        time = time [:-4]
                                        print(time)
                                    datetime_object = DT.strptime(time, '%Y-%m-%d' + 'T' + '%H:%M:%S')
                                    time_upper = str(datetime_object + datetime.timedelta(0, 2)) + "+02:00"
                                    time_lower = str(datetime_object - datetime.timedelta(0, 2)) + "+02:00"
                                    time_lower = time_lower.split(" ")[0] + "T" + time_lower.split(" ")[1]
                                    time_upper = time_upper.split(" ")[0] + "T" + time_upper.split(" ")[1]
                                    #print(time)
                                    #print(time_upper)
                                    #print(time_lower)
                                   # cur.execute("Insert into Instances VALUES ('" + instance + "','" + uuid + "', '" + logname + "', '2','Null')")
                                    query = "Select log.instance from LogEntries log join instances i on i.instance = log.instance where i.level='" + str(level-1) +"' and log.activity ='activity/calling' and log.timestamp>='" + time_lower + "' and log.timestamp<='" + time_upper + "'"
                                    #print(query)
                                    cur.execute(query)
                                    parent_inst = cur.fetchone()
                                    #print(parent_inst)
                                    if parent_inst is None:
                                        #print(parent_inst)
                                        #print(val)
                                        #print("Insert into Instances VALUES ('" + instance + "','" + uuid + "', '" + logname + "', '3','Null')")
                                        cur.execute("Insert into Instances VALUES ('" + instance + "','" + uuid + "', '" + logname + "', '" + str(level) +"','Null')")
                                        #conn.commit()
                                    else:
                                        cur.execute("Insert into Instances VALUES ('" + instance + "','" + uuid + "', '" + logname + "', '" + str(level) +"','" + parent_inst[0] + "')")
                                else:
                                    #print(instance)
                                    #print(parent_inst)
                                    query = "Update Instances set uuid = '" + uuid + "', name = '" + logname + "' where called_by = '" + parent_inst[0] + "' and instance='" + instance + "'"
                                    #print(query)
                                    cur.execute(query)
                                    #conn.commit()
                                #con.commit()
                                added = True

                            #print("Insert into LogEntries VALUES ('" + step_id + "','" + time + "', '" + name + "','" + activity + "', '" + val + "','" + instance + "' )")

                            #to add calling instances
                            if 'data_receiver' in value['event']['list'].keys() and activity == 'activity/receiving':
                                # if step_id=='a1':
                                for attr in value['event']['list']['data_receiver']:
                                    # print('addNEw')
                                    if type(attr['data']) is dict and 'CPEE-INSTANCE' in attr['data']:
                                        # print('addNEw1')
                                        c_instance = attr['data']['CPEE-INSTANCE'].split('/')[-1]
                                        query = "Insert into Instances VALUES ('" + c_instance + "','Null', 'Null', '" + str(
                                            level + 1) + "','" + instance + "')"
                                        cur.execute(query)
                                        conn.commit()
                                        break
                                    elif attr['name'] == 'instance':
                                        # print('addNEw2')
                                        c_instance = attr['data']
                                        query = "Insert into Instances VALUES ('" + c_instance + "','Null', 'Null', '" + str(
                                            level + 1) + "','" + instance + "')"
                                        # print(query)
                                        cur.execute(query)
                                        conn.commit()
                                        break
                                    # print("Insert into Instances VALUES ('" + c_instance + "','Null', 'Null', '2','"+instance+"')")
                                    # conn.commit()


                            #To avoid adding machine_logs afters receiving status continue
                            if step_id == 'a3' and name== 'Status' and activity == 'dataelements/change':
                                    #print("BR")
                                    #print(value['event']['list']['data_values'].keys())
                                    #print(value['event']['list']['data_values']['lets_continue'])
                                    if value['event']['list']['data_values']['lets_continue']==False:
                                        #print("EAK")
                                        query = "Insert into LogEntries VALUES ('" + step_id + "','" + time + "', '" + name + "','" + activity + "', '" + val + "','" + instance + "' )"
                                        cur.execute(query)
                                        conn.commit()
                                        break
                            query="Insert into LogEntries VALUES ('" + step_id + "','" + time + "', '" + name + "','" + activity + "', '" + val + "','" + instance + "' )"
                            if instance in ('446', '447', '448'):
                                print(query, file = f)
                            cur.execute(query)
                            conn.commit()




                        except(KeyError) as e:
                            print(e, file = f)
                            #print(query)
                            print(activity, file = f)
                            print(time, file = f)
                            counter+=1

                        #except sqlite3.Error as qe:
                        except sqlite3.IntegrityError as qe:
                            #if instance in ('446','447','448'):
                                #print(query, file = f)
                                #print(qe, file = f)
                            print(qe, file = f)
                            counter += 1
                            pass
                            #exit()
                        except sqlite3.Error as qe_all:
                            print("Error_GEN: " + str(query), file=f)
                            print("Error_GEN: " + str(qe_all), file=f)
                        except Exception as e:
                            counter += 1
                            #message = template.format(type(ex).__name__, ex.args)
                            print("Error_GEN" + str(e), file = f)
                            print("Unexpected error!!", file = f)


                        try:
                            # to handle machining:
                            if step_id == 'a1' and name == 'Fetch' and activity == 'activity/receiving':
                                for attr in value['event']['list']['data_receiver']:
                                    for entry in attr['data']:
                                        try:
                                            m_id = name = entry['ID']
                                            name = entry['name']
                                            val = entry['value']
                                            clientHandle = entry['meta']['ClientHandle']
                                            statusCode = entry['meta']['StatusCode']
                                            server_timestamp = entry['meta']['ServerTimestamp']
                                            query = "insert into Machining (timestamp, clientHandle, m_id, status, name, value, level4_step_id, level4_activity, level4_timestamp, level4_instance) VALUES('" + server_timestamp + "','" + clientHandle + "','" + m_id + "','" + statusCode + "','" + name + "','" + val + "','" + step_id + "','" + activity + "','" + time + "','" + instance + "')"
                                            #if instance in ('446', '447', '448', '449', '450', '451','452'):
                                                #print(query, file = f)
                                            #if time == '2018-10-17T14:37:40+02:00':
                                                #print(query, file = f)
                                            print(query, file=f)
                                            cur.execute(query)
                                        except(KeyError) as e:
                                            print("ERROR IN ENTRY: " + str(e), file=f)
                                            # print(query)
                                            print(activity, file=f)
                                            print(time, file=f)
                                            counter += 1
                                            # except sqlite3.Error as qe:
                                        except sqlite3.IntegrityError as qe:
                                            # if instance in ('446', '447', '448'):
                                            # print(query, file = f)
                                            # print(qe, file =
                                            print("Error: " + str(query), file=f)
                                            print("Error: " + str(qe), file=f)
                                            counter += 1
                                            pass
                                            # exit()
                                        except sqlite3.Error as qe_all:
                                            print("Error_GEN: " + str(query), file=f)
                                            print("Error_GEN: " + str(qe_all), file=f)
                                        except Exception as e:
                                            counter += 1
                                            # message = template.format(type(ex).__name__, ex.args)
                                            print("Error_GEN" + str(e), file=f)
                                            print("Unexpected error!!", file=f)
                                        conn.commit()

                        except(KeyError) as e:
                            print("ERROR IN ATTR: " + str(e), file=f)
                            #print(query)
                            print(activity, file = f)
                            print(time, file = f)
                            counter += 1
                            continue
                            # except sqlite3.Error as qe:
                        except Exception as e:
                            counter += 1
                            #message = template.format(type(ex).__name__, ex.args)
                            print("Error_GEN" + str(e), file = f)
                            print("Unexpected error!!", file = f)
                #oknok['ok']=oknok['ok']+ok
                #oknok['nok'] = oknok['nok'] + nok
            #print(oknok)
            #conn.commit()
            #print(counter)
            counter = 0
            input_file.close()
            target_path = check_folder(os.path.join(root_path, 'task3/processed'), ('level'+str(level)))
            #shutil.move(os.path.join(origin_path,file), target_path)



def recreate_process_level1(root_path,conn):
    #variables
    cur = conn.cursor()
    origin_path = os.path.join(root_path,"task3/sortedTemplates/level1")

    #traverse through all logs
    for root, dirs, files in os.walk(origin_path):
        for file in files:
            first = True;
            print(file)
            with open(os.path.join(root, file), "r") as input_file:
                results = yaml.load_all(input_file)
                #traverse throug single log
                for value in results:
                    try:
                        if 'log' in value:
                            logname = value['log']['trace']['cpee:name']
                            uuid = value['log']['trace']['cpee:uuid']
                            instance = value['log']['trace']['concept:name']
                            #print("Insert into Instances VALUES ('" + instance + "','" + uuid + "', '" + logname + "', '1','Null')")
                            query = "Insert into Instances VALUES ('" + instance + "','" + uuid + "', '" + logname + "', '1','Null')"
                            cur.execute(query)
                            #conn.commit()
                        if 'event' in value:
                            time = value['event']['time:timestamp']

                            val=""
                            if value['event']['lifecycle:transition']=="start":
                                val = json.dumps(value['event']['list']['data_send'])

                            if 'data_receiver' in value['event']['list'].keys():
                                val = json.dumps(value['event']['list']['data_receiver'])


                            step_id = value['event']['id:id']
                            activity = value['event']['cpee:lifecycle:transition']
                            name = value['event']['concept:name']
                            query = "Insert into LogEntries VALUES ('" + step_id + "','" + time + "', '" + name + "','" + activity + "', '" + val + "','" + instance + "' )"
                            #print("Insert into LogEntries VALUES ('" + step_id + "','" + time + "', '" + name + "','" + activity + "', '" + val + "','" + instance + "' )")
                            cur.execute(query)
                            #conn.commit()
                            for attr in value['event']['list']['data_receiver']:
                                if attr['name'] == "url":
                                    c_instance = attr['data'].split('/')[-1]
                                    query="Insert into Instances VALUES ('" + c_instance + "','Null', 'Null', '2','"+instance+"')"
                                    cur.execute(query)
                                    #print("Insert into Instances VALUES ('" + c_instance + "','Null', 'Null', '2','"+instance+"')")
                                    #conn.commit()
                        conn.commit()
                    except(KeyError) as e:
                        #print(e)
                        #print(time)
                        continue
            input_file.close()
            target_path = check_folder(os.path.join(root_path,'task3/processed'),'level1')
            #shutil.move(os.path.join(origin_path,file), target_path)


root_path = os.getcwd()
db = os.path.join(root_path,"BIII.db")
conn = create_connection(db)
conn.execute('delete from Machining')
conn.execute('delete from LogEntries')
conn.execute('delete from Instances')
conn.execute('delete from MeasureTot')
conn.execute('delete from MeasureDet')
logs_path = os.path.join(root_path, "logs")
#print(logs_path)
categorize_logs(root_path, logs_path)
recreate_process_level1(root_path,conn)
recreate_process(root_path,conn,2)
recreate_process(root_path,conn,3)
recreate_process(root_path,conn,4)

#recreate_process_level3(root_path,conn,"level3")
#recreate_process_level4(root_path,conn,"level4")
conn.close()