<reponame>FRIARGREG/PROJ-WORKSHOPS
# -*- coding: utf-8 -*-
import sys
import sqlite3
import slocwindow
from PyQt5 import QtCore, QtGui, QtWidgets


app = QtWidgets.QApplication(sys.argv)
MainWindow = QtWidgets.QMainWindow()
ui = slocwindow.Ui_MainWindow()
ui.setupUi(MainWindow)

conn = sqlite3.connect("slocInventory.db3")
SLOC_curs = conn.cursor()
NSN_curs = conn.cursor()
Locs_curs = conn.cursor()
ivty_curs = conn.cursor()


def getSLOCid(text):
    SQL = "SELECT ID FROM SLOC WHERE name = '%s';" % text.upper()
    row = SLOC_curs.execute(SQL).fetchall()
    return int(row[0][0])

def getNSNid(text):
    SQL = "SELECT ID FROM NSN WHERE name = '%s';" % text.upper()
    row = NSN_curs.execute(SQL).fetchall()
    print(row)
    if len(row) > 0:
        return int(row[0][0])

def getNSNdescription(text):
    SQL = "SELECT description FROM NSN WHERE name = '%s';" % text.upper()
    row = NSN_curs.execute(SQL).fetchall()
    return int(row[0][0])

def add_another():
    """Takes all the fields from userinterface and appends to inventory
    referencing related SLOC and NSN tables, clears item fields but leaves
    NSN/SLOC fields because the next item will still belong to them."""

    # EXAMPLE ##################
    # INSERT INTO Customers
    #   (CustomerName, ContactName, Address, City, PostalCode, Country)
    # VALUES
    #   ('Cardinal', '<NAME>', 'Skagen 21', 'Stavanger', '4006', 'Norway');
    # EXAMPLE ##################

    NSN     = getNSNid(ui.lnNSN.text())
    SLOC    = getSLOCid(ui.cbSLOC.currentText())
    WORK    = str(ui.lnWorkshops.text).upper()
    serial  = str(ui.lnSerial.text).upper()
    model   = str(ui.lnModel.text).upper()
    name    = getNSNdescription(ui.lnNSN.text())


    SQL = """INSERT INTO inventory
                (NSN_ID, SLOC_ID, workshop_ID, serialNumber, model, name)
             VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s');""" % (
                 NSN, SLOC, WORK, serial, model, name )
    ivty_curs.execute(SQL)
    conn.commit()

    ui.lnWorkshops.text = ""
    ui.lnSerial.text = ""
    ui.lnModel.text = ""



def cancel_clear():
    """clears all the fields."""
    pass

def save_clear():
    """Takes all the fields from userinterface and appends to inventory
    referencing related SLOC and NSN tables then clears all fields."""

def newLocation(text=str(ui.cbLocation.currentText())):
    SQL = """SELECT ID, name from locations WHERE name = '%s';""" % text
    locs = Locs_curs.execute(SQL).fetchall()
    if len(locs) == 0:
        SQL = """INSERT INTO locations (name) VALUES ('%s');""" % text
        Locs_curs.execute(SQL)
        conn.commit()
        refreshLocations()

def refreshLocations():
    SQL = """SELECT ID, name from locations;"""
    locs = Locs_curs.execute(SQL).fetchall()
    ui.cbLocation.clear()
    for row in locs:
        ui.cbLocation.addItem(row[1])



def newSLOC(text=ui.cbSLOC.currentText()):
    print(ui.cbSLOC.currentText())
    SQL = """SELECT ID, name from SLOC WHERE name = '%s';""" % text
    SLOCS = SLOC_curs.execute(SQL).fetchall()
    if len(SLOCS) == 0:
        SQL = """INSERT INTO SLOC (name) VALUES ('%s');""" % text
        SLOC_curs.execute(SQL)
        conn.commit()
        refreshSLOCS()

def refreshSLOCS():
    SQL = """SELECT ID, name from SLOC;"""
    SLOCS = SLOC_curs.execute(SQL).fetchall()
    ui.cbSLOC.clear()
    for row in SLOCS:
        ui.cbSLOC.addItem(row[1])


### objects in app ###########################################################

# ui.cbLocation
ui.cbLocation.currentIndexChanged.connect(newLocation)
refreshLocations()

# ui.cbSLOC
ui.cbSLOC.currentIndexChanged.connect(newSLOC)
refreshSLOCS()

# ui.lnQTYcount
# ui.lnNSN
# ui.lnQTYexpected

# ui.lnIMGloc
# ui.gvIMG

# ui.lnModel = QtWidgets.QLineEdit(self.centralwidget)
# ui.lnSerial = QtWidgets.QLineEdit(self.centralwidget)
# ui.lnWorkshops = QtWidgets.QLineEdit(self.centralwidget)


# ui.pbADD - saves all information and clears only item fields so we can
#            add another just like it.
ui.pbADD.clicked.connect(add_another)

# ui.pbCANCEL - will simply clear all the fields.
ui.pbCANCEL.clicked.connect(cancel_clear)

# ui.pbSAVE - will save all the current information and clear screen.
ui.pbSAVE.clicked.connect(save_clear)

# ui.pbEXIT - is the exit button.
#ui.pbEXIT.clicked.connect(app.exec_)

# ui.statusbar


MainWindow.show()
sys.exit(app.exec_())

# -*- coding: utf-8 -*-
import sys
import sqlite3
import slocwindow
from PyQt5 import QtCore, QtGui, QtWidgets


app = QtWidgets.QApplication(sys.argv)
MainWindow = QtWidgets.QMainWindow()
ui = slocwindow.Ui_MainWindow()
ui.setupUi(MainWindow)

conn = sqlite3.connect("slocInventory.db3")
SLOC_curs = conn.cursor()
NSN_curs = conn.cursor()
Locs_curs = conn.cursor()
ivty_curs = conn.cursor()


def getSLOCid(text):
    SQL = "SELECT ID FROM SLOC WHERE name = '%s';" % text.upper()
    row = SLOC_curs.execute(SQL).fetchall()
    return int(row[0][0])

def getNSNid(text):
    SQL = "SELECT ID FROM NSN WHERE name = '%s';" % text.upper()
    row = NSN_curs.execute(SQL).fetchall()
    print(row)
    if len(row) > 0:
        return int(row[0][0])

def getNSNdescription(text):
    SQL = "SELECT description FROM NSN WHERE name = '%s';" % text.upper()
    row = NSN_curs.execute(SQL).fetchall()
    return int(row[0][0])

def add_another():
    """Takes all the fields from userinterface and appends to inventory
    referencing related SLOC and NSN tables, clears item fields but leaves
    NSN/SLOC fields because the next item will still belong to them."""

    # EXAMPLE ##################
    # INSERT INTO Customers
    #   (CustomerName, ContactName, Address, City, PostalCode, Country)
    # VALUES
    #   ('Cardinal', '<NAME>', 'Skagen 21', 'Stavanger', '4006', 'Norway');
    # EXAMPLE ##################

    NSN     = getNSNid(ui.lnNSN.text())
    SLOC    = getSLOCid(ui.cbSLOC.currentText())
    WORK    = str(ui.lnWorkshops.text).upper()
    serial  = str(ui.lnSerial.text).upper()
    model   = str(ui.lnModel.text).upper()
    name    = getNSNdescription(ui.lnNSN.text())


    SQL = """INSERT INTO inventory
                (NSN_ID, SLOC_ID, workshop_ID, serialNumber, model, name)
             VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s');""" % (
                 NSN, SLOC, WORK, serial, model, name )
    ivty_curs.execute(SQL)
    conn.commit()

    ui.lnWorkshops.text = ""
    ui.lnSerial.text = ""
    ui.lnModel.text = ""



def cancel_clear():
    """clears all the fields."""
    pass

def save_clear():
    """Takes all the fields from userinterface and appends to inventory
    referencing related SLOC and NSN tables then clears all fields."""

def newLocation(text=str(ui.cbLocation.currentText())):
    SQL = """SELECT ID, name from locations WHERE name = '%s';""" % text
    locs = Locs_curs.execute(SQL).fetchall()
    if len(locs) == 0:
        SQL = """INSERT INTO locations (name) VALUES ('%s');""" % text
        Locs_curs.execute(SQL)
        conn.commit()
        refreshLocations()

def refreshLocations():
    SQL = """SELECT ID, name from locations;"""
    locs = Locs_curs.execute(SQL).fetchall()
    ui.cbLocation.clear()
    for row in locs:
        ui.cbLocation.addItem(row[1])



def newSLOC(text=ui.cbSLOC.currentText()):
    print(ui.cbSLOC.currentText())
    SQL = """SELECT ID, name from SLOC WHERE name = '%s';""" % text
    SLOCS = SLOC_curs.execute(SQL).fetchall()
    if len(SLOCS) == 0:
        SQL = """INSERT INTO SLOC (name) VALUES ('%s');""" % text
        SLOC_curs.execute(SQL)
        conn.commit()
        refreshSLOCS()

def refreshSLOCS():
    SQL = """SELECT ID, name from SLOC;"""
    SLOCS = SLOC_curs.execute(SQL).fetchall()
    ui.cbSLOC.clear()
    for row in SLOCS:
        ui.cbSLOC.addItem(row[1])


### objects in app ###########################################################

# ui.cbLocation
ui.cbLocation.currentIndexChanged.connect(newLocation)
refreshLocations()

# ui.cbSLOC
ui.cbSLOC.currentIndexChanged.connect(newSLOC)
refreshSLOCS()

# ui.lnQTYcount
# ui.lnNSN
# ui.lnQTYexpected

# ui.lnIMGloc
# ui.gvIMG

# ui.lnModel = QtWidgets.QLineEdit(self.centralwidget)
# ui.lnSerial = QtWidgets.QLineEdit(self.centralwidget)
# ui.lnWorkshops = QtWidgets.QLineEdit(self.centralwidget)


# ui.pbADD - saves all information and clears only item fields so we can
#            add another just like it.
ui.pbADD.clicked.connect(add_another)

# ui.pbCANCEL - will simply clear all the fields.
ui.pbCANCEL.clicked.connect(cancel_clear)

# ui.pbSAVE - will save all the current information and clear screen.
ui.pbSAVE.clicked.connect(save_clear)

# ui.pbEXIT - is the exit button.
#ui.pbEXIT.clicked.connect(app.exec_)

# ui.statusbar


MainWindow.show()
sys.exit(app.exec_())
# -*- coding: utf-8 -*- Takes all the fields from userinterface and appends to inventory     referencing related SLOC and NSN tables, clears item fields but leaves     NSN/SLOC fields because the next item will still belong to them. # EXAMPLE ################## # INSERT INTO Customers #   (CustomerName, ContactName, Address, City, PostalCode, Country) # VALUES #   ('Cardinal', '<NAME>', 'Skagen 21', 'Stavanger', '4006', 'Norway'); # EXAMPLE ################## INSERT INTO inventory                 (NSN_ID, SLOC_ID, workshop_ID, serialNumber, model, name)              VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s'); clears all the fields. Takes all the fields from userinterface and appends to inventory     referencing related SLOC and NSN tables then clears all fields. SELECT ID, name from locations WHERE name = '%s'; INSERT INTO locations (name) VALUES ('%s'); SELECT ID, name from locations; SELECT ID, name from SLOC WHERE name = '%s'; INSERT INTO SLOC (name) VALUES ('%s'); SELECT ID, name from SLOC; ### objects in app ########################################################### # ui.cbLocation # ui.cbSLOC # ui.lnQTYcount # ui.lnNSN # ui.lnQTYexpected # ui.lnIMGloc # ui.gvIMG # ui.lnModel = QtWidgets.QLineEdit(self.centralwidget) # ui.lnSerial = QtWidgets.QLineEdit(self.centralwidget) # ui.lnWorkshops = QtWidgets.QLineEdit(self.centralwidget) # ui.pbADD - saves all information and clears only item fields so we can #            add another just like it. # ui.pbCANCEL - will simply clear all the fields. # ui.pbSAVE - will save all the current information and clear screen. # ui.pbEXIT - is the exit button. #ui.pbEXIT.clicked.connect(app.exec_) # ui.statusbar