import terminaltable_functions as ttf #------------------------------------------------------------------------------- # Aufbau der Datenbank ... build a database #------------------------------------------------------------------------------- def createSQLiteTable(cursor, connection, rueckmeldungen): cursor.execute(""" CREATE TABLE IF NOT EXISTS conductors( element1 TEXT (38), terminal1 TEXT (38), element2 TEXT (38), terminal2 TEXT (38), function TEXT, conductor_section TEXT, cable TEXT, conductor_color TEXT, bus TEXT, tension_protocol TEXT); """) cursor.execute(""" CREATE TABLE IF NOT EXISTS diagrams( folio TEXT, plant TEXT, locmach TEXT, title TEXT, date TEXT, author TEXT, indexrev TEXT, orderNo INTEGER, cols INTEGER, colsize INTEGER, rows INTEGER, rowsize INTEGER, displaycols TEXT, displayrows TEXT, titleblocktemplate TEXT, titleblocktemplateCollection TEXT, displayAt TEXT, version TEXT, auto_page_num TEXT, freezeNewConductor TEXT, freezeNewElement TEXT, filename TEXT); """) cursor.execute(""" CREATE TABLE IF NOT EXISTS elements( uuid TEXT, type TEXT, location TEXT, function TEXT, label TEXT, plant TEXT, folio TEXT, x TEXT, y TEXT); """) cursor.execute(""" CREATE TABLE IF NOT EXISTS elementTerminals( folio TEXT, uuid TEXT, id INTEGER); """) cursor.execute(""" CREATE TABLE IF NOT EXISTS elementLinks( uuid TEXT, destination_uuid TEXT); """) cursor.execute(""" CREATE TABLE IF NOT EXISTS links( uuid TEXT, destination_uuid TEXT); """) cursor.execute(""" CREATE TABLE IF NOT EXISTS embeddedElements( uuid TEXT, name TEXT, type TEXT, link_type TEXT, terminal_type TEXT, terminal_function TEXT); """) cursor.execute(""" CREATE TABLE IF NOT EXISTS embeddedElementTerminals( uuid TEXT, name TEXT, type TEXT, element_name TEXT); """) # Tabelle zur Ausgabe der Klemmleisten cursor.execute(""" CREATE TABLE IF NOT EXISTS terminalstrips( Id INTEGER PRIMARY KEY AUTOINCREMENT, uuid TEXT, label TEXT UNIQUE ON CONFLICT IGNORE, element_name TEXT ); """) cursor.execute(""" CREATE TABLE IF NOT EXISTS terminals( Id INTEGER PRIMARY KEY AUTOINCREMENT, label integer NOT NULL, terminalstrip INTEGER NOT NULL, folio TEXT, X TEXT, Y TEXT, FOREIGN KEY(terminalstrip) REFERENCES terminalstrips(Id), UNIQUE(label, terminalstrip) ON CONFLICT IGNORE ); """) cursor.execute(""" CREATE TABLE IF NOT EXISTS terminalpoints( terminalstrip INTEGER NOT NULL, terminal INTEGER NOT NULL, name TEXT, page TEXT, X TEXT, Y TEXT, plant TEXT, location TEXT, element TEXT, element_terminalpoint TEXT, type TEXT, function TEXT, cross_section TEXT, cable TEXT, conductor_color TEXT, bus TEXT, tension_protocol TEXT, FOREIGN KEY(terminalstrip) REFERENCES terminalstrips(Id), FOREIGN KEY(terminal) REFERENCES terminals(Id)); """) cursor.execute(""" CREATE TABLE IF NOT EXISTS bridgepoint( terminal INTEGER, start_y INTEGER); """) connection.commit() print("SQLite Table created!") ruekm = "Datenbank wird erstellt\n" rueckmeldungen(ruekm) def setTerminalLocation(cursor, connection): sqlTerminalFolio = ("SELECT label, folio FROM elements WHERE label LIKE '-X%'") cursor.execute(sqlTerminalFolio) terminalFolio = cursor.fetchall() for t in terminalFolio: sqlTerminalLocation = (f"""SELECT locmach, plant FROM diagrams WHERE folio IS'{t[1]}'""") cursor.execute(sqlTerminalLocation) terminalLocation = cursor.fetchone() sqlSetTerminalLocation=(f"""UPDATE elements SET location = '{terminalLocation[0]}', plant = '{terminalLocation[1]}' WHERE label = '{t[0]}'""") cursor.execute(sqlSetTerminalLocation) connection.commit() def sorting_terminals(databaseElements, Element, cursor, connection): # Zunächst werden die Klemmen gesucht und in die "klemmleiste" Tabelle eingefügt # damit auch die leeren Klemmen erfasst werden for s in databaseElements: try: k = s[4] ks = k.split(':') terminal = Element(s[0], s[1], s[2], s[3], ks[0], ks[1], s[5], s[6], s[7], s[8]) except(IndentationError,TypeError) as e: print("Something went wrong!", e.args[0]) print("last found element:", s) # erfassen der Seitengröße mit Reihen und Spalten, für Verweise # take the rows, collums and pagediameter to make the references cursor.execute(f"""SELECT colsize, rowsize FROM diagrams WHERE folio IS ('{terminal.folio}')""") page = cursor.fetchone() if page != None: reference = ttf.reference(terminal.x, terminal.y, page) terminal.x = reference[0] terminal.y = reference[1] # Klemmleiste einfügen # insert terminalstrips cursor.execute(f"""INSERT OR IGNORE INTO terminalstrips (uuid, label, element_name) VALUES ('{terminal.uuid}', '{terminal.strip_label}', '{terminal.element_name}')""") connection.commit() # Klemme einfügen # insert Terminals cursor.execute(f"""SELECT Id FROM terminalstrips WHERE label IS ('{terminal.strip_label}')""") stripnumber = cursor.fetchone() cursor.execute(f"""INSERT OR IGNORE INTO terminals (label, terminalstrip, folio, X, Y) VALUES ('{terminal.terminal_label}', '{stripnumber[0]}', '{terminal.folio}', '{terminal.x}', '{terminal.y}')""") connection.commit() def sorting_terminalpoints(databaseElements, Element, Conductor, cursor, connection, rueckmeldungen): for e in databaseElements: try: k = e[4] es = k.split(':') terminal = Element(e[0], e[1], e[2], e[3], es[0], es[1], e[5], e[6], e[7], e[8]) # erfassen der Seitengröße mit Reihen und Spalten, für Verweise cursor.execute(f"""SELECT colsize, rowsize FROM diagrams WHERE folio IS ('{terminal.folio}')""") page = cursor.fetchone() sql = (f"""SELECT * FROM conductors WHERE element1 = '{terminal.uuid}' OR element2 = '{terminal.uuid}'""") cursor.execute(sql) conductors = cursor.fetchall() # Suche abgehende Leiter an den Klemmen, erst mit Seitenverweisen # immer 2 mal weil nicht klar ist, welche Seite zuerst gezeichnet # wurde, daher muss das target "gegenüber" der Klemme gesucht werden for c in conductors: conductor = Conductor(c[0], c[1], c[2], c[3], c[4], c[6], c[5], c[7], c[8], c[9]) if conductor.element1 != terminal.uuid: conductorEndElement = conductor.element1 conductorStartTerminal = conductor.terminal2 conductorEndTerminal = conductor.terminal1 else: conductorEndElement = conductor.element2 conductorStartTerminal = conductor.terminal1 conductorEndTerminal = conductor.terminal2 sqlAnschlussconductorStartTerminal = (f"""SELECT name, type, element_name FROM embeddedElementTerminals WHERE uuid IS '{conductorStartTerminal}'""") cursor.execute(sqlAnschlussconductorStartTerminal) anschlussconductorStartTerminal = cursor.fetchall() sqlLink = (f"""SELECT destination_uuid FROM elementLinks WHERE uuid IS '{conductorEndElement}'""") cursor.execute(sqlLink) link = cursor.fetchall() if link != None : sqlLinkedElement = (f"""SELECT name FROM embeddedElements WHERE uuid IS '{link}'""") cursor.execute(sqlLink) linkedElement = cursor.fetchall() # Ist kein Verweis vorhanden, das target suchen if link == None: sqltarget = (f"""SELECT label, location, plant, x, y, folio FROM elements WHERE uuid IS '{conductorEndElement}'""") sqlAnschlusstarget = (f"""SELECT name FROM embeddedElementTerminals WHERE uuid IS '{conductorEndTerminal}'""") # Ist ein Verweis vohranden, über den Verweis das target suchen else: sqlt = (f"""SELECT x, y, folio FROM elements WHERE uuid IS '{conductorEndElement}'""") cursor.execute(sqlt) t = cursor.fetchone() sqlU = (f"""SELECT label, location, plant FROM elements WHERE uuid IS '{conductorEndElement}'""") cursor.execute(sqlU) u = cursor.fetchall() r = [] for i in u: if i[0] != "": r = (i[0], i[1], i[2], t[0], t[1], t[2]) sqlAnschlusstarget = (f"""SELECT name FROM embeddedElementTerminals WHERE uuid IS '{conductorEndTerminal}'""") sqlAnschlusstarget = (f"""SELECT name FROM embeddedElementTerminals WHERE uuid IS '{conductorEndTerminal}'""") if r != []: target = r else: sqltargetFolioInfo = (f"""SELECT x, y, folio FROM elements WHERE uuid IS '{conductorEndElement}'""") cursor.execute(sqltargetFolioInfo) targetFolioInfo = cursor.fetchone() sqltargetElementUuid = (f"""SELECT destination_uuid FROM elementLinks WHERE uuid IS '{conductorEndElement}'""") cursor.execute(sqltargetElementUuid) targetElementUuid = cursor.fetchone()[0] sqltargetElementInfo = (f"""SELECT label, location, plant FROM elements WHERE uuid IS '{targetElementUuid}'""") cursor.execute(sqltargetElementInfo) targeElementInfo = cursor.fetchone() target = targeElementInfo[0::] + targetFolioInfo[0::] cursor.execute(sqlAnschlusstarget) anschlusstarget = cursor.fetchone() reference = ttf.reference(target[3], target[4], page) anschlussconductorStartTerminal1 = anschlussconductorStartTerminal[0] # Aufbau der Klemmleistenzeile # 0 Klemmleiste # 1 Klemmemmummer # 2 Klemmenanschluss # 3 PlanverweisSeite # 4 PlanverweisX # 5 PlanverweisY # 6 Anlage # 7 Ort, # 8 Bauteil # 9 Klemmpunkt # 10 Klemmpunkt extern/intern/generic # 11 function # 12 Querschnitt # 13 Kabel # 14 Leiterfarbe # 15 bus # 16 Spannung/Protokoll # klemmenzeile = ["","","","","","","","","","","","","","","","",""] klemmenzeile[0] = terminal.strip_label klemmenzeile[1] = terminal.terminal_label klemmenzeile[2] = anschlussconductorStartTerminal1[0] klemmenzeile[3] = target[5] klemmenzeile[4] = reference[0] klemmenzeile[5] = reference[1] klemmenzeile[6] = target[2] klemmenzeile[7] = target[1] klemmenzeile[8] = target[0] klemmenzeile[9] = anschlusstarget[0] klemmenzeile[10] = anschlussconductorStartTerminal1[1] klemmenzeile[11] = conductor.function klemmenzeile[12] = conductor.conductor_section klemmenzeile[13] = conductor.cable klemmenzeile[14] = conductor.conductor_color klemmenzeile[15] = conductor.bus klemmenzeile[16] = conductor.tension_protocol cursor.execute(f"SELECT Id FROM terminalstrips WHERE label IS ('{klemmenzeile[0]}')") terminalStripId = cursor.fetchone() klemmenzeile[0] = terminalStripId[0] cursor.execute(f"SELECT Id FROM terminals WHERE " f"terminalstrip IS ('{klemmenzeile[0]}') AND label IS ('{klemmenzeile[1]}')") terminalId = cursor.fetchone() klemmenzeile[1] = terminalId[0] cursor.execute("INSERT INTO terminalpoints VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", klemmenzeile[0::]) connection.commit() except (TypeError) as e: ruekm = ("\nEtwas ist schiefgelaufen!" + e.args[0] + "\n") rueckmeldungen(ruekm) print("The terminals are stored in database") ruekm = ("Die Klemmen sind in der Datenbank gespeichert") rueckmeldungen(ruekm) def noticePagenumber(cursor): pagesql = ("SELECT orderNo FROM diagrams ORDER BY orderNo DESC") cursor.execute(pagesql) greatestOrderNo = cursor.fetchone() nextOrderNo = greatestOrderNo[0] + 1 return nextOrderNo