def raapLijst(keuze, zoekterm, m_email, route): class MyWindow(QDialog): def __init__(self, data_list, header, *args): QWidget.__init__( self, *args, ) self.setWindowTitle('Raaplijst Opvragen / Muteren') self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setWindowFlags(self.windowFlags() | Qt.WindowSystemMenuHint | Qt.WindowMinMaxButtonsHint) self.setFont(QFont('Arial', 10)) grid = QGridLayout() grid.setSpacing(20) table_model = MyTableModel(self, data_list, header) table_view = QTableView() table_view.setModel(table_model) font = QFont("Arial", 10) table_view.setFont(font) table_view.resizeColumnsToContents() table_view.setSelectionBehavior(QTableView.SelectRows) table_view.clicked.connect(boekAfroep) grid.addWidget(table_view, 0, 0, 1, 13) lbl = QLabel() pixmap = QPixmap('./images/logos/verbinding.jpg') lbl.setPixmap(pixmap) grid.addWidget(lbl, 1, 0, 1, 2) logo = QLabel() pixmap = QPixmap('./images/logos/logo.jpg') logo.setPixmap(pixmap) grid.addWidget(logo, 1, 12, 1, 1, Qt.AlignRight) freshBtn = QPushButton('Verversen') freshBtn.clicked.connect( lambda: refresh(keuze, zoekterm, m_email, route, self)) freshBtn.setFont(QFont("Arial", 10)) freshBtn.setFixedWidth(100) freshBtn.setStyleSheet( "color: black; background-color: gainsboro") grid.addWidget(freshBtn, 1, 11, 1, 1, Qt.AlignRight) sluitBtn = QPushButton('Sluiten') sluitBtn.clicked.connect(self.close) sluitBtn.setFont(QFont("Arial", 10)) sluitBtn.setFixedWidth(100) sluitBtn.setStyleSheet( "color: black; background-color: gainsboro") grid.addWidget(sluitBtn, 1, 10) grid.addWidget( QLabel('\u00A9 2017 all rights reserved [email protected]'), 1, 0, 1, 13, Qt.AlignCenter) self.setLayout(grid) self.setGeometry(50, 50, 1500, 900) class MyTableModel(QAbstractTableModel): def __init__(self, parent, mylist, header, *args): QAbstractTableModel.__init__(self, parent, *args) self.mylist = mylist self.header = header def rowCount(self, parent): return len(self.mylist) def columnCount(self, parent): return len(self.mylist[0]) def data(self, index, role): veld = self.mylist[index.row()][index.column()] if not index.isValid(): return None elif role == Qt.TextAlignmentRole and (type(veld) == float or type(veld) == int): return Qt.AlignRight | Qt.AlignVCenter elif role != Qt.DisplayRole: return None if type(veld) == float: return '{:12.2f}'.format(veld) else: return veld def headerData(self, col, orientation, role): if orientation == Qt.Horizontal and role == Qt.DisplayRole: return self.header[col] return None metadata = MetaData() raaplijst = Table('raaplijst', metadata, Column('lijstID', Integer, primary_key=True), Column('artikelID', Integer), Column('werkorder', Integer), Column('afroep', Float), Column('leverdatum', String), Column('geleverd', Float), Column('meerwerk', Boolean), Column('postcode', String), Column('huisnummer', Integer), Column('toevoeging', String), Column('alternatief', String), Column('boekdatum', String), Column('straat', String), Column('woonplaats', String)) engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem') con = engine.connect() header = ['LijstID','Artikelnr','Werkorder','Afroep','Leverdatum','Geleverd',\ 'Meerwerk','Postcode', 'Huisnummer','Toevoeging', 'Alternatief Adres',\ 'Boekdatum','Straat','Woonplaats'] import validZt if keuze == 1: selafr = select([raaplijst]).where(raaplijst.c.geleverd < raaplijst.c.afroep)\ .order_by(raaplijst.c.werkorder, raaplijst.c.leverdatum) elif keuze == 2: selafr = select([raaplijst]).where(and_(raaplijst.c.werkorder < 800000000,\ raaplijst.c.geleverd < raaplijst.c.afroep)).order_by(raaplijst.c.leverdatum) elif keuze == 3: selafr = select([raaplijst]).where( and_(raaplijst.c.werkorder > 800000000, raaplijst.c.geleverd < raaplijst.c.afroep)).order_by( raaplijst.c.leverdatum) elif keuze == 4 and validZt.zt(zoekterm, 15): selafr = select([raaplijst]).where(and_(raaplijst.c.werkorder == int(zoekterm),\ raaplijst.c.geleverd < raaplijst.c.afroep)).order_by(raaplijst.c.leverdatum.desc()) elif keuze == 5 and validZt.zt(zoekterm, 2): selafr = select([raaplijst]).where(and_(raaplijst.c.artikelID == int(zoekterm),\ raaplijst.c.geleverd < raaplijst.c.afroep)).order_by(raaplijst.c.leverdatum.desc()) elif keuze == 6 and validZt.zt(zoekterm, 10): selafr = select([raaplijst]).where(and_(raaplijst.c.leverdatum.like(zoekterm+'%'), raaplijst.c.geleverd < raaplijst.c.afroep))\ .order_by(raaplijst.c.leverdatum, raaplijst.c.werkorder) elif keuze == 7 and validZt.zt(zoekterm, 9): selafr = select([raaplijst]).where(and_(raaplijst.c.postcode.ilike(zoekterm+'%'),\ raaplijst.c.geleverd < raaplijst.c.afroep))\ .order_by(raaplijst.c.leverdatum, raaplijst.c.werkorder) elif keuze == 8: selafr = select([raaplijst]).where(and_(raaplijst.c.geleverd >= raaplijst.c.afroep,\ raaplijst.c.afroep > 0))\ .order_by(raaplijst.c.werkorder, raaplijst.c.leverdatum) else: ongInvoer() kiesSelektie(0, m_email) if con.execute(selafr).fetchone(): rpafr = con.execute(selafr) else: geenRecord() kiesSelektie(0, m_email) data_list = [] for row in rpafr: data_list += [(row)] def boekAfroep(idx): mlijstnr = idx.data() if idx.column() == 0: metadata = MetaData() raaplijst = Table('raaplijst', metadata, Column('lijstID', Integer, primary_key=True), Column('artikelID', Integer), Column('werkorder', Integer), Column('afroep', Float), Column('leverdatum', String), Column('geleverd', Float), Column('meerwerk', Boolean), Column('postcode', String), Column('huisnummer', Integer), Column('toevoeging', String), Column('alternatief', String), Column('boekdatum', String), Column('straat', String), Column('woonplaats', String)) engine = create_engine( 'postgresql+psycopg2://postgres@localhost/bisystem') con = engine.connect() selrlr = select([raaplijst]).where(raaplijst.c.lijstID == mlijstnr) rprlr = con.execute(selrlr).first() mwerknr = rprlr[2] martnr = rprlr[1] mleverdat = rprlr[4] mmmstatus = rprlr[6] mafroep = rprlr[3] mgeleverd = rprlr[5] class Widget(QDialog): def __init__(self, parent=None): super(Widget, self).__init__(parent) self.setWindowTitle("Materiaaluitgifte muteren") self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setFont(QFont('Arial', 10)) self.Werknummer = QLabel() zkwerknEdit = QLineEdit(str(mwerknr)) zkwerknEdit.setFont(QFont("Arial", 10)) zkwerknEdit.setDisabled(True) zkwerknEdit.setStyleSheet("color:black") zkwerknEdit.setAlignment(Qt.AlignRight) zkwerknEdit.textChanged.connect(self.zkwerknChanged) reg_ex = QRegExp("^[8]{1}[0-9]{8}$") input_validator = QRegExpValidator(reg_ex, zkwerknEdit) zkwerknEdit.setValidator(input_validator) self.Artikelnummer = QLabel() artEdit = QLineEdit(str(martnr)) artEdit.setFixedWidth(150) artEdit.setFont(QFont("Arial", 10)) artEdit.setDisabled(True) artEdit.setStyleSheet("color:black") artEdit.setAlignment(Qt.AlignRight) artEdit.textChanged.connect(self.artChanged) reg_ex = QRegExp("^[2]{1}[0-9]{8}$") input_validator = QRegExpValidator(reg_ex, artEdit) artEdit.setValidator(input_validator) self.Hoeveelheid = QLabel() hoevEdit = QLineEdit() hoevEdit.setFixedWidth(150) hoevEdit.setFont(QFont("Arial", 10)) hoevEdit.textChanged.connect(self.hoevChanged) reg_ex = QRegExp("^[-+]?[0-9]*\.?[0-9]+$") input_validator = QRegExpValidator(reg_ex, hoevEdit) hoevEdit.setValidator(input_validator) grid = QGridLayout() grid.setSpacing(20) lbl = QLabel() pixmap = QPixmap('./images/logos/verbinding.jpg') lbl.setPixmap(pixmap) grid.addWidget(lbl, 0, 0) logo = QLabel() pixmap = QPixmap('./images/logos/logo.jpg') logo.setPixmap(pixmap) grid.addWidget(logo, 0, 2, 1, 1, Qt.AlignRight) lbl1 = QLabel('Werknummer') lbl1.setAlignment(Qt.AlignRight | Qt.AlignVCenter) grid.addWidget(lbl1, 1, 0) grid.addWidget(zkwerknEdit, 1, 1) lbl2 = QLabel('Artikelnummer') lbl2.setAlignment(Qt.AlignRight | Qt.AlignVCenter) grid.addWidget(lbl2, 2, 0) grid.addWidget(artEdit, 2, 1) lbl4 = QLabel('Afroep: ' + str(mafroep) + ' - Reeds geleverd: ' + str(mgeleverd)) grid.addWidget(lbl4, 3, 0, 1, 3, Qt.AlignCenter) lbl3 = QLabel('Uitgifte') lbl3.setAlignment(Qt.AlignRight | Qt.AlignVCenter) grid.addWidget(lbl3, 4, 0) grid.addWidget(hoevEdit, 4, 1) if mmmstatus: grid.addWidget(QLabel('Meerwerk'), 4, 2) grid.addWidget(QLabel('Leverdatum'), 5, 0, Qt.AlignRight) grid.addWidget(QLabel(mleverdat), 5, 1) self.setLayout(grid) self.setGeometry(500, 300, 150, 150) applyBtn = QPushButton('Muteren') applyBtn.clicked.connect(self.accept) grid.addWidget(applyBtn, 6, 2, 1, 1, Qt.AlignRight) applyBtn.setFont(QFont("Arial", 10)) applyBtn.setFixedWidth(100) applyBtn.setStyleSheet( "color: black; background-color: gainsboro") sluitBtn = QPushButton('Sluiten') sluitBtn.clicked.connect(self.close) grid.addWidget(sluitBtn, 6, 0, 1, 2, Qt.AlignRight) sluitBtn.setFont(QFont("Arial", 10)) sluitBtn.setFixedWidth(100) sluitBtn.setStyleSheet( "color: black; background-color: gainsboro") grid.addWidget( QLabel( '\u00A9 2017 all rights reserved [email protected]' ), 7, 0, 1, 3, Qt.AlignCenter) def zkwerknChanged(self, text): self.Werknummer.setText(text) def artChanged(self, text): self.Artikelnummer.setText(text) def hoevChanged(self, text): self.Hoeveelheid.setText(text) def returnzkwerkn(self): return self.Werknummer.text() def returnart(self): return self.Artikelnummer.text() def returnhoev(self): return self.Hoeveelheid.text() @staticmethod def getData(parent=None): dialog = Widget(parent) dialog.exec_() return [ dialog.returnzkwerkn(), dialog.returnart(), dialog.returnhoev() ] window = Widget() data = window.getData() mhoev = 0 if data[0] and len(data[0]) == 9 and _11check(data[0]): mwerknr = int(data[0]) elif not data[0] and len(str(mwerknr)) == 9 and _11check(mwerknr): mwerknr = int(mwerknr) else: foutWerknr() return ('') if str(mwerknr)[0] == '7': metadata = MetaData() orders_intern = Table( 'orders_intern', metadata, Column('werkorderID', Integer(), primary_key=True), Column('voortgangstatus', String)) engine = create_engine( 'postgresql+psycopg2://postgres@localhost/bisystem') con = engine.connect() selwerk = select([ orders_intern ]).where(orders_intern.c.werkorderID == mwerknr) rpwerk = con.execute(selwerk).first() metadata = MetaData() orders_intern = Table( 'orders_intern', metadata, Column('werkorderID', Integer(), primary_key=True), Column('werk_materialen', Float), Column('meerminderwerk', Float)) artikelmutaties = Table( 'artikelmutaties', metadata, Column('mutatieID', Integer, primary_key=True), Column('artikelID', None, ForeignKey('artikelen.artikelID')), Column('werkorderID', None, ForeignKey('orders_intern.werkorderID')), Column('orderinkoopID', None, ForeignKey('orders_inkoop.orderinkoopID')), Column('hoeveelheid', Float), Column('boekdatum', String), Column('tot_mag_prijs', Float), Column('btw_hoog', Float), Column('mmstatus', Boolean)) elif str(mwerknr)[0] == '8': metadata = MetaData() werken = Table( 'werken', metadata, Column('werknummerID', Integer, primary_key=True), Column('voortgangstatus', String), Column('kosten_materialen', Float), Column('meerminderwerk', Float)) artikelmutaties = Table( 'artikelmutaties', metadata, Column('mutatieID', Integer, primary_key=True), Column('artikelID', None, ForeignKey('artikelen.artikelID')), Column('werknummerID', None, ForeignKey('werken.werknoID')), Column('orderinkoopID', None, ForeignKey('orders_inkoop.orderinkoopID')), Column('hoeveelheid', Float), Column('boekdatum', String), Column('tot_mag_prijs', Float), Column('btw_hoog', Float), Column('mmstatus', Boolean)) engine = create_engine( 'postgresql+psycopg2://postgres@localhost/bisystem') con = engine.connect() selwerk = select([werken ]).where(werken.c.werknummerID == mwerknr) rpwerk = con.execute(selwerk).first() if rpwerk[1] == 'H': werkGereed() return (mwerknr) if data[2]: mhoev = float(data[2]) else: return (mwerknr) metadata = MetaData() artikelen = Table( 'artikelen', metadata, Column('artikelID', Integer(), primary_key=True), Column('artikelomschrijving', String), Column('artikelprijs', Float), Column('art_voorraad', Float, CheckConstraint('art_voorraad >= 0')), Column('art_min_voorraad', Float), Column('art_bestelgrootte', Float), Column('bestelstatus', Boolean), Column('mutatiedatum', String), Column('reserveringsaldo', Float), Column('jaarverbruik_1', Float), Column('jaarverbruik_2', Float)) engine = create_engine( 'postgresql+psycopg2://postgres@localhost/bisystem') con = engine.connect() sel = select([artikelen]).where(artikelen.c.artikelID == martnr) transaction = con.begin() result = con.execute(sel).first() martprijs = result[2] martvrd = result[3] martminvrd = result[4] mboekd = str(datetime.datetime.now())[0:10] mjaar = int(str(datetime.datetime.now())[0:4]) if martvrd - mhoev <= martminvrd: martbestst = False else: martbestst = True if mhoev <= 0: foutHoev() return ('') try: if mjaar % 2 == 0: #even jaartal stmt = update(artikelen).where(artikelen.c.artikelID == martnr).values(\ art_voorraad = artikelen.c.art_voorraad - mhoev, bestelstatus = martbestst,\ mutatiedatum = mboekd, reserveringsaldo = artikelen.c.reserveringsaldo - mhoev,\ jaarverbruik_1 = artikelen.c.jaarverbruik_1 + mhoev) con.execute(stmt) mwaarde = martprijs * 1.1 * mhoev con = engine.connect() elif mjaar % 2 == 1: #oneven jaartal stmt = update(artikelen).where(artikelen.c.artikelID == martnr).values(\ art_voorraad = artikelen.c.art_voorraad - mhoev, bestelstatus = martbestst,\ mutatiedatum = mboekd, reserveringsaldo = artikelen.c.reserveringsaldo - mhoev,\ jaarverbruik_2 = artikelen.c.jaarverbruik_2 + mhoev) con.execute(stmt) mwaarde = martprijs * 1.1 * mhoev con = engine.connect() if mmmstatus: mmeerminder = mwaarde else: mmeerminder = 0 if str(mwerknr)[0] == '8': stmt = update(werken).where(werken.c.werknummerID == mwerknr).values(\ kosten_materialen = werken.c.kosten_materialen + mwaarde,\ meerminderwerk = werken.c.meerminderwerk + mmeerminder) con.execute(stmt) try: mutatienr=(con.execute(select([func.max(artikelmutaties.c.mutatieID,\ type_=Integer)])).scalar()) mutatienr += 1 except: mutatienr = 1 ins = insert(artikelmutaties).values(mutatieID = mutatienr, artikelID =\ martnr, werknummerID = mwerknr, hoeveelheid = -mhoev, boekdatum = mboekd,\ tot_mag_prijs = mhoev*martprijs, btw_hoog = .21*mhoev*martprijs,\ mmstatus = mmmstatus) con.execute(ins) elif str(mwerknr)[0] == '7': stmt = update(orders_intern).where(orders_intern.c.werkorderID == mwerknr).values(\ werk_materialen = orders_intern.c.werk_materialen + mwaarde,\ meerminderwerk = orders_intern.c.meerminderwerk + mmeerminder) con.execute(stmt) try: mutatienr=(con.execute(select([func.max(artikelmutaties.c.mutatieID,\ type_=Integer)])).scalar()) mutatienr += 1 except: mutatienr = 1 ins = insert(artikelmutaties).values(mutatieID = mutatienr, artikelID =\ martnr, werkorderID = mwerknr, hoeveelheid = -mhoev, boekdatum = mboekd,\ tot_mag_prijs = mhoev*martprijs, btw_hoog = .21*mhoev*martprijs,\ mmstatus = mmmstatus) con.execute(ins) updrl = update(raaplijst).where(raaplijst.c.lijstID == mlijstnr).\ values(geleverd = raaplijst.c.geleverd+mhoev, boekdatum = mboekd ) con.execute(updrl) transaction.commit() invoerOK() except IntegrityError: transaction.rollback() negVoorraad() con.close return (mwerknr) win = MyWindow(data_list, header) win.exec_() kiesSelektie(route, m_email)
def toonMutaties(keuze,zoekterm, m_email): import validZt metadata = MetaData() dienstenmutaties = Table('dienstenmutaties', metadata, Column('mutatieID', Integer, primary_key=True), Column('werknummerID', None, ForeignKey('werken.werknummerID')), Column('orderinkoopID', None, ForeignKey('orders_inkoop.orderinkoopID')), Column('boekbedrag', Float), Column('boekdatum', String), Column('leverancierID', None, ForeignKey('leveranciers.leverancierID')), Column('werkomschr', String), Column('btw_hoog', Float), Column('omschrijving', String), Column('regel', Integer)) werken = Table('werken', metadata, Column('werknummerID', Integer(), primary_key=True), Column('werkomschrijving', String(50))) orders_inkoop = Table('orders_inkoop', metadata, Column('orderinkoopID', Integer, primary_key=True), Column('leverancierID', None, ForeignKey('leveranciers.leverancierID'))) leveranciers = Table('leveranciers', metadata, Column('leverancierID', Integer, primary_key=True), Column('bedrijfsnaam', String), Column('rechtsvorm', String)) engine = create_engine('postgresql://postgres@localhost/bisystem') conn = engine.connect() if keuze == 1: sel = select([dienstenmutaties, werken, orders_inkoop, leveranciers])\ .where(and_(werken.c.werknummerID==dienstenmutaties.c.werknummerID,\ leveranciers.c.leverancierID == dienstenmutaties.c.leverancierID,\ orders_inkoop.c.orderinkoopID==dienstenmutaties.c.orderinkoopID))\ .order_by(werken.c.werknummerID) elif keuze == 2: sel = select([dienstenmutaties, werken, orders_inkoop, leveranciers])\ .where(and_(werken.c.werknummerID==dienstenmutaties.c.werknummerID,\ leveranciers.c.leverancierID == dienstenmutaties.c.leverancierID,\ orders_inkoop.c.orderinkoopID==dienstenmutaties.c.orderinkoopID,\ werken.c.werknummerID== int(zoekterm))) elif keuze == 3: sel = select([dienstenmutaties, werken, orders_inkoop, leveranciers])\ .where(and_(werken.c.werknummerID==dienstenmutaties.c.werknummerID,\ leveranciers.c.leverancierID == dienstenmutaties.c.leverancierID,\ orders_inkoop.c.orderinkoopID==dienstenmutaties.c.orderinkoopID,\ werken.c.werkomschrijving.ilike('%'+zoekterm+'%')))\ .order_by(werken.c.werknummerID) elif keuze == 4 and validZt.zt(zoekterm, 3): sel = select([dienstenmutaties, werken, orders_inkoop, leveranciers])\ .where(and_(werken.c.werknummerID==dienstenmutaties.c.werknummerID,\ leveranciers.c.leverancierID == dienstenmutaties.c.leverancierID,\ orders_inkoop.c.orderinkoopID==dienstenmutaties.c.orderinkoopID,\ leveranciers.c.leverancierID == int(zoekterm))) elif keuze == 5: sel = select([dienstenmutaties, werken, orders_inkoop, leveranciers])\ .where(and_(werken.c.werknummerID==dienstenmutaties.c.werknummerID,\ leveranciers.c.leverancierID == dienstenmutaties.c.leverancierID,\ orders_inkoop.c.orderinkoopID==dienstenmutaties.c.orderinkoopID,\ leveranciers.c.bedrijfsnaam.ilike('%'+zoekterm+'%'))) elif keuze == 6 and validZt.zt(zoekterm, 5): sel = select([dienstenmutaties, werken, orders_inkoop, leveranciers])\ .where(and_(werken.c.werknummerID==dienstenmutaties.c.werknummerID,\ leveranciers.c.leverancierID == dienstenmutaties.c.leverancierID,\ orders_inkoop.c.orderinkoopID==dienstenmutaties.c.orderinkoopID,\ orders_inkoop.c.orderinkoopID== int(zoekterm))) elif keuze == 7 and validZt.zt(zoekterm, 16): sel = select([dienstenmutaties, werken, orders_inkoop, leveranciers])\ .where(and_(werken.c.werknummerID==dienstenmutaties.c.werknummerID,\ leveranciers.c.leverancierID == dienstenmutaties.c.leverancierID,\ orders_inkoop.c.orderinkoopID==dienstenmutaties.c.orderinkoopID,\ dienstenmutaties.c.werkomschr.like(zoekterm+'%')))\ .order_by(dienstenmutaties.c.werkomschr) elif keuze == 8 and validZt.zt(zoekterm, 10): sel = select([dienstenmutaties, werken, orders_inkoop, leveranciers])\ .where(and_(werken.c.werknummerID==dienstenmutaties.c.werknummerID,\ leveranciers.c.leverancierID == dienstenmutaties.c.leverancierID,\ orders_inkoop.c.orderinkoopID==dienstenmutaties.c.orderinkoopID,\ dienstenmutaties.c.boekdatum.ilike(zoekterm+'%')))\ .order_by(dienstenmutaties.c.boekdatum) else: ongInvoer() mutatieKeuze(m_email) if conn.execute(sel).fetchone(): rp = conn.execute(sel) else: geenRecord() mutatieKeuze(m_email) class MyWindow(QDialog): def __init__(self, data_list, header, *args): QWidget.__init__(self, *args,) self.setGeometry(50, 50, 1800, 900) self.setWindowTitle('Dienstenmutaties opvragen') self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setWindowFlags(self.windowFlags()| Qt.WindowSystemMenuHint | Qt.WindowMinMaxButtonsHint) table_model = MyTableModel(self, data_list, header) table_view = QTableView() table_view.setModel(table_model) font = QFont("Arial", 10) table_view.setFont(font) table_view.resizeColumnsToContents() table_view.setSelectionBehavior(QTableView.SelectRows) table_view.setColumnHidden(1, True) table_view.setColumnHidden(2, True) table_view.setColumnHidden(5, True) table_view.setColumnHidden(12, True) #table_view.clicked.connect(showAccount) layout = QVBoxLayout(self) layout.addWidget(table_view) self.setLayout(layout) class MyTableModel(QAbstractTableModel): def __init__(self, parent, mylist, header, *args): QAbstractTableModel.__init__(self, parent, *args) self.mylist = mylist self.header = header def rowCount(self, parent): return len(self.mylist) def columnCount(self, parent): return len(self.mylist[0]) def data(self, index, role): if not index.isValid(): return None elif role != Qt.DisplayRole: return None return str(self.mylist[index.row()][index.column()]) def headerData(self, col, orientation, role): if orientation == Qt.Horizontal and role == Qt.DisplayRole: return self.header[col] return None header = ['Mutatienummer','Werknummer', 'OrderinkoopID','Boekbedrag', 'Boekdatun', 'LeverancierID',\ 'Categorie-werk','BTW-hoog', 'Omschrijving', 'Regelnummer', 'Werknummer', 'Omschrijving',\ 'OrderinkoopID', 'LeverancierID', 'LeverancierID','Bedrijfsnaam', 'Rechtsvorm'] data_list=[] for row in rp: data_list += [(row)] win = MyWindow(data_list, header) win.exec_() mutatieKeuze(m_email)
def inlog(): #verification and logon class Widget(QDialog): def __init__(self, parent=None): super(Widget, self).__init__(parent) self.setWindowTitle("Pandora bedrijfs informatie systeem login scherm") self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setFont(QFont('Arial', 10)) self.setStyleSheet("background-color: #D9E1DF") grid = QGridLayout() grid.setSpacing(20) self.email = QLabel() emailEdit = QLineEdit() emailEdit.setStyleSheet("background: #F8F7EE") emailEdit.setFixedWidth(200) emailEdit.setFont(QFont("Arial",10)) emailEdit.textChanged.connect(self.emailChanged) self.Wachtwoord = QLabel() wachtwEdit = QLineEdit() wachtwEdit.setStyleSheet("background: #F8F7EE") wachtwEdit.setEchoMode(QLineEdit.Password) wachtwEdit.setFixedWidth(200) wachtwEdit.setFont(QFont("Arial",10)) wachtwEdit.textChanged.connect(self.wachtwChanged) lbl = QLabel() pixmap = QPixmap('./images/logos/verbinding.jpg') lbl.setPixmap(pixmap) grid.addWidget(lbl, 0, 0, 1, 2) lblinfo = QLabel(' Pandora login') grid.addWidget(lblinfo, 0, 1, 1, 2, Qt.AlignLeft) lblinfo.setStyleSheet("color:rgba(45, 83, 115, 255); font: 25pt Comic Sans MS") logo = QLabel() pixmap = QPixmap('./images/logos/logo.jpg') logo.setPixmap(pixmap) grid.addWidget(logo , 0, 2, 1, 1, Qt.AlignRight) pandora = QLabel() movie = QMovie('./images/logos/pyqt5.gif') pandora.setMovie(movie) movie.start() grid.addWidget(pandora, 1 ,0, 1, 3, Qt.AlignCenter) grid.addWidget(QLabel('emailadres'), 3, 1) grid.addWidget(emailEdit, 3, 2) grid.addWidget(QLabel('Wachtwoord'), 4, 1) grid.addWidget(wachtwEdit, 4, 2) self.setLayout(grid) self.setGeometry(600, 250, 150, 150) applyBtn = QPushButton('Login') applyBtn.clicked.connect(self.accept) grid.addWidget(applyBtn, 5, 1, 1 , 2, Qt.AlignRight) applyBtn.setFont(QFont("Arial",10)) applyBtn.setFixedWidth(90) applyBtn.setStyleSheet("color: black; background-color: gainsboro") cancelBtn = QPushButton('Afsluiten') cancelBtn.clicked.connect(lambda: sys.exit(totZiens())) grid.addWidget(cancelBtn, 5, 2) cancelBtn.setFont(QFont("Arial",10)) cancelBtn.setFixedWidth(90) cancelBtn.setStyleSheet("color: black; background-color: gainsboro") nwBtn = QPushButton('Nieuw Account') nwBtn.clicked.connect(lambda: maakAccount(self)) grid.addWidget(nwBtn, 5, 0, 1, 2, Qt.AlignRight) nwBtn.setFont(QFont("Arial",10)) nwBtn.setFixedWidth(140) nwBtn.setStyleSheet("color: black; background-color: gainsboro") infoBtn = QPushButton('Informatie') infoBtn.clicked.connect(lambda: info()) grid.addWidget(infoBtn, 5, 0, 1, 2) infoBtn.setFont(QFont("Arial",10)) infoBtn.setFixedWidth(120) infoBtn.setStyleSheet("color: black; background-color: gainsboro") grid.addWidget(QLabel('\u00A9 2017 all rights reserved [email protected]'), 6, 0, 1, 3, Qt.AlignCenter) def emailChanged(self, text): self.email.setText(text) def wachtwChanged(self, text): self.Wachtwoord.setText(text) def returnemail(self): return self.email.text() def returnWachtwoord(self): return self.Wachtwoord.text() @staticmethod def getData(parent=None): dialog = Widget(parent) dialog.exec_() return [dialog.returnemail(), dialog.returnWachtwoord()] window = Widget() data = window.getData() for item in data: if item.startswith(' '): geenKeuze() inlog() if sys.platform == 'win32': from win32api import GetKeyState from win32con import VK_CAPITAL capslk = GetKeyState(VK_CAPITAL) if capslk == 1: capslkOn() inlog() else: import subprocess if (subprocess.getoutput("xset q | grep LED")[65]) == '1': capslkOn() inlog() if data[0] and zt(data[0],12): m_email = data[0] else: foutemailAdres() inlog() if data[1]: mwachtw = data[1] else: foutWachtw() inlog() metadata = MetaData() accounts = Table('accounts', metadata, Column('accountID', Integer(), primary_key=True), Column('email', String, nullable=False), Column('password', String, nullable=False)) engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem') conn = engine.connect() sel = select([accounts]).where(accounts.c.email == m_email) rpaccount = conn.execute(sel).first() if rpaccount: maccountnr = rpaccount[0] maccountnr = int(maccountnr) m_email = rpaccount[1] mpassword = rpaccount[2] else: foutInlog() inlog() if not check_password(mpassword,mwachtw): foutWachtw() inlog() hoofdMenu(m_email)
def printRaaplijst(keuze, zoekterm, m_email, route): import validZt metadata = MetaData() raaplijst = Table('raaplijst', metadata, Column('lijstID', Integer, primary_key=True), Column('artikelID', Integer), Column('werkorder', Integer), Column('afroep', Float), Column('leverdatum', String), Column('geleverd', Float), Column('meerwerk', Boolean), Column('postcode', String), Column('huisnummer', Integer), Column('toevoeging', String), Column('alternatief', String), Column('boekdatum', String), Column('straat', String), Column('woonplaats', String)) artikelen = Table('artikelen', metadata, Column('artikelID', Integer(), primary_key=True), Column('artikelomschrijving', String), Column('locatie_magazijn', String)) engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem') con = engine.connect() if route < 2: if keuze == 1: selrl = select([raaplijst, artikelen]).where(and_(raaplijst.c.geleverd < raaplijst.c.afroep,\ raaplijst.c.artikelID == artikelen.c.artikelID)).order_by(raaplijst.c.werkorder, raaplijst.c.leverdatum) kop1 = 'Raaplijst Alle Afroepen ' tekst1 = '' elif keuze == 2: selrl = select([raaplijst, artikelen]).where(and_(raaplijst.c.werkorder < 800000000,\ raaplijst.c.geleverd < raaplijst.c.afroep, raaplijst.c.artikelID ==\ artikelen.c.artikelID)).order_by(raaplijst.c.leverdatum) kop1 = '' tekst1 = 'Raaplijst Werkorders ' elif keuze == 3: selrl = select([raaplijst, artikelen]).where(and_(raaplijst.c.werkorder > 800000000,\ raaplijst.c.geleverd < raaplijst.c.afroep, raaplijst.c.artikelID ==\ artikelen.c.artikelID)).order_by(raaplijst.c.leverdatum) kop1 = '' tekst1 = 'Raaplijst Werknummers ' elif keuze == 4 and validZt.zt(zoekterm, 15): selrl = select([raaplijst, artikelen]).where(and_(raaplijst.c.werkorder == int(zoekterm),\ raaplijst.c.geleverd < raaplijst.c.afroep, raaplijst.c.artikelID ==\ artikelen.c.artikelID)).order_by(raaplijst.c.leverdatum) kop1 = '' tekst1 = 'Raaplijst Ordernummer ' elif keuze == 5 and validZt.zt(zoekterm, 2): selrl = select([raaplijst, artikelen]).where(and_(raaplijst.c.artikelID == int(zoekterm),\ raaplijst.c.geleverd < raaplijst.c.afroep, raaplijst.c.artikelID ==\ artikelen.c.artikelID)).order_by(raaplijst.c.leverdatum) kop1 = '' tekst1 = 'Raaplijst Artikelnummer ' elif keuze == 6 and validZt.zt(zoekterm, 10): selrl = select([raaplijst, artikelen]).where(and_(raaplijst.c.leverdatum.like(zoekterm+'%'), raaplijst.c.geleverd < raaplijst.c.afroep, raaplijst.c.artikelID ==\ artikelen.c.artikelID)).order_by(raaplijst.c.leverdatum, raaplijst.c.werkorder) kop1 = '' tekst1 = 'Raaplijst Leverdatum ' elif keuze == 7 and validZt.zt(zoekterm, 9): selrl = select([raaplijst, artikelen]).where(and_(raaplijst.c.postcode.ilike(zoekterm+'%'),\ raaplijst.c.geleverd < raaplijst.c.afroep, raaplijst.c.artikelID ==\ artikelen.c.artikelID)).order_by(raaplijst.c.leverdatum, raaplijst.c.werkorder) kop1 = '' tekst1 = 'Raaplijst Postcode ' elif keuze == 8: selrl = select([raaplijst, artikelen]).where(and_(raaplijst.c.geleverd >= raaplijst.c.afroep,\ raaplijst.c.afroep > 0, raaplijst.c.artikelID == artikelen.c.artikelID))\ .order_by(raaplijst.c.werkorder, raaplijst.c.leverdatum) kop1 = 'Uitgeleverd ' tekst1 = '' else: ongInvoer() kiesSelektie(route, m_email) elif route == 2: if keuze == 2: selrl = select([raaplijst, artikelen]).where(and_(raaplijst.c.werkorder < 800000000,\ raaplijst.c.geleverd < raaplijst.c.afroep, raaplijst.c.artikelID ==\ artikelen.c.artikelID)).order_by(raaplijst.c.leverdatum) kop1 = '' tekst1 = 'Raaplijst Werkorders ' elif keuze == 4 and validZt.zt(zoekterm, 15): selrl = select([raaplijst, artikelen]).where(and_(raaplijst.c.werkorder < 800000000,\ raaplijst.c.werkorder == int(zoekterm), raaplijst.c.geleverd < raaplijst.c.afroep,\ raaplijst.c.artikelID == artikelen.c.artikelID)).order_by(raaplijst.c.leverdatum) kop1 = '' tekst1 = 'Raaplijst Werkorder ' elif keuze == 5 and validZt.zt(zoekterm, 2): selrl = select([raaplijst, artikelen]).where(and_(raaplijst.c.werkorder < 800000000,\ raaplijst.c.artikelID == int(zoekterm), raaplijst.c.geleverd < raaplijst.c.afroep,\ raaplijst.c.artikelID == artikelen.c.artikelID)).order_by(raaplijst.c.leverdatum) kop1 = '' tekst1 = 'Raaplijst Artikelnummer ' elif keuze == 6 and validZt.zt(zoekterm, 10): selrl = select([raaplijst, artikelen]).where(and_(raaplijst.c.werkorder < 800000000,\ raaplijst.c.leverdatum.like(zoekterm+'%'), raaplijst.c.geleverd < raaplijst.c.afroep,\ raaplijst.c.artikelID == artikelen.c.artikelID)).order_by(raaplijst.c.leverdatum,\ raaplijst.c.werkorder) kop1 = '' tekst1 = 'Raaplijst Leverdatum ' elif keuze == 7 and validZt.zt(zoekterm, 9): selrl = select([raaplijst, artikelen]).where(and_(raaplijst.c.werkorder < 800000000,\ raaplijst.c.postcode.ilike(zoekterm+'%'),raaplijst.c.geleverd < raaplijst.c.afroep,\ raaplijst.c.artikelID == artikelen.c.artikelID)).order_by(raaplijst.c.leverdatum,\ raaplijst.c.werkorder) kop1 = '' tekst1 = 'Raaplijst Postcode ' elif keuze == 8: selrl = select([raaplijst, artikelen]).where(and_(raaplijst.c.werkorder < 800000000,\ raaplijst.c.geleverd >= raaplijst.c.afroep, raaplijst.c.afroep > 0,\ raaplijst.c.artikelID == artikelen.c.artikelID)).order_by(raaplijst.c.werkorder,\ raaplijst.c.leverdatum) kop1 = 'Uitgeleverd ' tekst1 = '' else: ongInvoer() kiesSelektie(route, m_email) elif route == 3: if keuze == 3: selrl = select([raaplijst, artikelen]).where(and_(raaplijst.c.werkorder > 800000000,\ raaplijst.c.geleverd < raaplijst.c.afroep, raaplijst.c.artikelID ==\ artikelen.c.artikelID)).order_by(raaplijst.c.leverdatum) kop1 = '' tekst1 = 'Raaplijst Werknummers ' elif keuze == 4 and validZt.zt(zoekterm, 15): selrl = select([raaplijst, artikelen]).where(and_(raaplijst.c.werkorder > 800000000,\ raaplijst.c.werkorder == int(zoekterm), raaplijst.c.geleverd < raaplijst.c.afroep,\ raaplijst.c.artikelID == artikelen.c.artikelID)).order_by(raaplijst.c.leverdatum) kop1 = '' tekst1 = 'Raaplijst Werknummer ' elif keuze == 5 and validZt.zt(zoekterm, 2): selrl = select([raaplijst, artikelen]).where(and_(raaplijst.c.werkorder > 800000000,\ raaplijst.c.artikelID == int(zoekterm), raaplijst.c.geleverd < raaplijst.c.afroep,\ raaplijst.c.artikelID == artikelen.c.artikelID)).order_by(raaplijst.c.leverdatum) kop1 = '' tekst1 = 'Raaplijst Artikelnummer ' elif keuze == 6 and validZt.zt(zoekterm, 10): selrl = select([raaplijst, artikelen]).where(and_(raaplijst.c.werkorder > 800000000,\ raaplijst.c.leverdatum.like(zoekterm+'%'), raaplijst.c.geleverd < raaplijst.c.afroep,\ raaplijst.c.artikelID == artikelen.c.artikelID)).order_by(raaplijst.c.leverdatum,\ raaplijst.c.werkorder) kop1 = '' tekst1 = 'Raaplijst Leverdatum ' elif keuze == 7 and validZt.zt(zoekterm, 9): selrl = select([raaplijst, artikelen]).where(and_(raaplijst.c.werkorder > 800000000,\ raaplijst.c.postcode.ilike(zoekterm+'%'), raaplijst.c.geleverd < raaplijst.c.afroep,\ raaplijst.c.artikelID == artikelen.c.artikelID)).order_by(raaplijst.c.leverdatum,\ raaplijst.c.werkorder) kop1 = '' tekst1 = 'Raaplijst Postcode ' elif keuze == 8: selrl = select([raaplijst, artikelen]).where(and_(raaplijst.c.werkorder > 800000000,\ raaplijst.c.geleverd >= raaplijst.c.afroep, raaplijst.c.afroep > 0,\ raaplijst.c.artikelID == artikelen.c.artikelID)).order_by(raaplijst.c.werkorder,\ raaplijst.c.leverdatum) kop1 = 'Uitgeleverd ' tekst1 = '' else: ongInvoer() kiesSelektie(route, m_email) if con.execute(selrl).fetchone(): rprl = con.execute(selrl) else: geenRecord() kiesSelektie(route, m_email) mblad = 1 rgl = 0 for row in rprl: from sys import platform if rgl == 0 or rgl % 57 == 0: kop4=\ (tekst1+str(kop1)+' Datum: '+str(datetime.datetime.now())[0:10]+' Blad : '+str(mblad)+' '+ 'Afleveradres '+row[12]+' '+str(row[8])+row[9]+', '+row[7]+' '+row[13]+'.\n'+ '=============================================================================================\n'+ 'Artikelnr Omschrijving Afroep Geleverd Leverdatum Werkorder Locatie \n'+ '=============================================================================================\n') kop5=\ (tekst1+str(kop1)+' Datum: '+str(datetime.datetime.now())[0:10]+' Blad : '+str(mblad)+'\n'+ '=============================================================================================\n'+ 'Artikelnr Omschrijving Afroep Geleverd Leverdatum Werkorder Locatie \n'+ '=============================================================================================\n') if keuze == 2: kop1 = row[2] kop = kop5 elif keuze == 3: kop1 = row[2] kop = kop5 elif keuze == 4: kop1 = row[2] kop = kop4 elif keuze == 5: kop1 = row[1] kop = kop5 elif keuze == 6: kop1 = row[4] kop = kop5 elif keuze == 7: kop1 = row[7] kop = kop4 else: kop = kop5 if platform == 'win32': filename = '.\\forms\\Raaplijsten\\raaplijst-' + str( kop1) + '-' + str(datetime.datetime.now())[0:10] + '.txt' else: filename = './forms/Raaplijsten/raaplijst-' + str( kop1) + '-' + str(datetime.datetime.now())[0:10] + '.txt' if rgl == 0: open(filename, 'w').write(kop) elif rgl % 57 == 0: open(filename, 'a').write(kop) mblad += 1 open(filename,'a').write('{:<11d}'.format(row[1])+'{:<37.35s}'.format(row[15])\ +'{:>6.2f}'.format(row[3])+'{:>6.2f}'.format(row[5])+' '+\ '{:10s}'.format(row[4])+' '+'{:<10d}'.format(row[2])+' '+'{:<10s}'.format(row[16])+'\n') rgl += 1 printGeg(filename) kiesSelektie(route, m_email)
def opvrOrder(keuze,zoekterm, m_email): import validZt metadata = MetaData() orders_inkoop_artikelen = Table('orders_inkoop_artikelen', metadata, Column('ordartlevID', Integer(), primary_key=True), Column('orderinkoopID', None, ForeignKey('orders_inkoop.orderinkoopID')), Column('artikelID', None, ForeignKey('artikelen.artikelID')), Column('bestelaantal', Float), Column('inkoopprijs', Float), Column('levering_start', String), Column('levering_eind', String), Column('reclamatie', String), Column('aantal_reclamaties', Integer), Column('ontvangstdatum', String), Column('ontvangen_hoeveelheid', Float), Column('acceptatie_datum', String), Column('hoeveelheid_acceptatie', Float), Column('betaald', Float), Column('regel', Integer)) orders_inkoop = Table('orders_inkoop', metadata, Column('orderinkoopID', Integer(), primary_key=True), Column('leverancierID', None, ForeignKey('leveranciers.leverancierID')), Column('besteldatum', String), Column('goedgekeurd', String), Column('betaald', String), Column('afgemeld', String)) leveranciers = Table('leveranciers', metadata, Column('leverancierID', Integer(), primary_key=True), Column('bedrijfsnaam', String), Column('rechtsvorm', String)) artikelen = Table('artikelen', metadata, Column('artikelID', Integer(), primary_key=True), Column('artikelomschrijving', String), Column('art_voorraad', Float), Column('art_eenheid', String(20)), Column('thumb_artikel', String(70))) engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem') con = engine.connect() if keuze == 1: sel = select([orders_inkoop_artikelen,orders_inkoop, leveranciers,artikelen]).\ where(and_(orders_inkoop.c.leverancierID == leveranciers.c.\ leverancierID, orders_inkoop_artikelen.c.artikelID == artikelen.c.\ artikelID, orders_inkoop_artikelen.c.orderinkoopID == orders_inkoop.c.\ orderinkoopID)).order_by(orders_inkoop_artikelen.c.artikelID) elif keuze == 2: sel = select([orders_inkoop_artikelen,orders_inkoop, leveranciers,artikelen]).\ where(and_(orders_inkoop.c.leverancierID == leveranciers.c.\ leverancierID, orders_inkoop_artikelen.c.artikelID == artikelen.c.\ artikelID, orders_inkoop_artikelen.c.orderinkoopID == orders_inkoop.c.\ orderinkoopID)).order_by(orders_inkoop_artikelen.c.orderinkoopID,\ orders_inkoop_artikelen.c.regel) elif keuze == 3: sel = select([orders_inkoop_artikelen,orders_inkoop, leveranciers,artikelen])\ .where(and_(orders_inkoop_artikelen.c.orderinkoopID==orders_inkoop.c.orderinkoopID,\ orders_inkoop.c.leverancierID == leveranciers.c.leverancierID,\ orders_inkoop_artikelen.c.artikelID == artikelen.c.artikelID,\ orders_inkoop_artikelen.c.orderinkoopID == orders_inkoop.c.orderinkoopID,\ leveranciers.c.bedrijfsnaam.ilike('%'+zoekterm+'%'))) elif keuze == 4: sel = select([orders_inkoop_artikelen,orders_inkoop, leveranciers,artikelen])\ .where(and_(orders_inkoop.c.leverancierID == leveranciers.c.leverancierID,\ orders_inkoop_artikelen.c.orderinkoopID == orders_inkoop.c.orderinkoopID,\ orders_inkoop_artikelen.c.artikelID == artikelen.c.artikelID,\ artikelen.c.artikelomschrijving.ilike('%'+zoekterm+'%'))) elif keuze == 5 and validZt.zt(zoekterm, 4): sel = select([orders_inkoop_artikelen,orders_inkoop, leveranciers,artikelen])\ .where(and_(orders_inkoop.c.leverancierID == leveranciers.c.leverancierID,\ orders_inkoop_artikelen.c.artikelID == artikelen.c.artikelID,\ orders_inkoop_artikelen.c.orderinkoopID == orders_inkoop.c.orderinkoopID, orders_inkoop_artikelen.c.orderinkoopID == int(zoekterm))).order_by(\ orders_inkoop_artikelen.c.orderinkoopID, orders_inkoop_artikelen.c.regel) elif keuze == 6 and validZt.zt(zoekterm, 10): sel = select([orders_inkoop_artikelen,orders_inkoop, leveranciers,artikelen])\ .where(and_(orders_inkoop.c.leverancierID == leveranciers.c.leverancierID,\ orders_inkoop_artikelen.c.artikelID == artikelen.c.artikelID,\ orders_inkoop_artikelen.c.orderinkoopID == orders_inkoop.c.orderinkoopID, orders_inkoop_artikelen.c.levering_start.like(zoekterm+'%'))) elif keuze == 7 and validZt.zt(zoekterm, 10): sel = select([orders_inkoop_artikelen,orders_inkoop, leveranciers,artikelen])\ .where(and_(orders_inkoop.c.leverancierID == leveranciers.c.leverancierID,\ orders_inkoop_artikelen.c.artikelID == artikelen.c.artikelID,\ orders_inkoop_artikelen.c.orderinkoopID == orders_inkoop.c.orderinkoopID, orders_inkoop_artikelen.c.levering_eind.like(zoekterm+'%'))) else: ongInvoer() inkooporderKeuze(m_email) if con.execute(sel).fetchone(): rpinkorders = con.execute(sel) else: geenRecord() inkooporderKeuze(m_email) class Window(QDialog): def __init__(self, data_list, header, *args): QWidget.__init__(self, *args,) self.setGeometry(100, 50, 1600, 900) self.setWindowTitle('Inkooporders opvragen') self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setWindowFlags(self.windowFlags()| Qt.WindowSystemMenuHint | Qt.WindowMinMaxButtonsHint) table_model = MyTableModel(self, data_list, header) table_view = QTableView() table_view.setModel(table_model) font = QFont("Arial", 10) table_view.setFont(font) table_view.resizeColumnsToContents() table_view.setColumnHidden(1,True) table_view.setColumnHidden(2,True) table_view.setColumnHidden(16,True) table_view.setSelectionBehavior(QTableView.SelectRows) table_view.setColumnWidth(28, 100) table_view.verticalHeader().setDefaultSectionSize(75) table_view.setItemDelegateForColumn(28, showImage(self)) table_view.clicked.connect(showOrder) layout = QVBoxLayout(self) layout.addWidget(table_view) self.setLayout(layout) class MyTableModel(QAbstractTableModel): def __init__(self, parent, mylist, header, *args): QAbstractTableModel.__init__(self, parent, *args) self.mylist = mylist self.header = header def rowCount(self, parent): return len(self.mylist) def columnCount(self, parent): return len(self.mylist[0]) def data(self, index, role): veld = self.mylist[index.row()][index.column()] if not index.isValid(): return None elif role == Qt.TextAlignmentRole and (type(veld) == float or type(veld) == int): return Qt.AlignRight | Qt.AlignVCenter elif role != Qt.DisplayRole: return None if type(veld) == float: return '{:12.2f}'.format(veld) else: return veld def headerData(self, col, orientation, role): if orientation == Qt.Horizontal and role == Qt.DisplayRole: return self.header[col] return None class showImage(QStyledItemDelegate): def __init__(self, parent): QStyledItemDelegate.__init__(self, parent) def paint(self, painter, option, index): painter.fillRect(option.rect,QColor(255,255,255)) image = QImage(index.data()) pixmap = QPixmap(image) pixmap.scaled(256,256) return(painter.drawPixmap(option.rect, pixmap)) header = ['OrderartikelID','Orderinkoopnummer','Artikelnummer', 'Bestelaantal',\ 'Inkoopprijs','Levering start','Levering eind', 'Recl.Datum', 'Recl.aantal', \ 'Ontvangsdatum', 'Ontv.aantal', 'Acceptatiedatum','Geacc.hoeveelheid',\ 'Betaald', 'Regelnummer','Orderinkoopnummer', 'Leveranciernummer',\ 'Besteldatum', 'Goedgekeeurd','Betaald', 'Afgemeld','Leveranciernummer',\ 'Bedrijfsnaam', 'Rechtsvorm', 'Artikelnummer','Artikelomschr',\ 'Artikelvoorraad', 'Artikeleenheid', 'Afbeelding',] data_list=[] for row in rpinkorders: data_list += [(row)] def showOrder(idx): mordartlevnr = idx.data() engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem') con = engine.connect() engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem') con = engine.connect() if idx.column() == 0: seloal = select([orders_inkoop_artikelen, orders_inkoop, leveranciers, artikelen]).\ where(and_(orders_inkoop.c.leverancierID == leveranciers.c.\ leverancierID, orders_inkoop_artikelen.c.artikelID == artikelen.c.\ artikelID, orders_inkoop_artikelen.c.orderinkoopID == orders_inkoop.c.\ orderinkoopID, orders_inkoop_artikelen.c.ordartlevID == mordartlevnr)) rpordartlev = con.execute(seloal).first() class Widget(QDialog): def __init__(self, parent=None): super(Widget, self).__init__(parent) self.setWindowTitle("Inkooporders materialen opvragen") self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setFont(QFont('Arial', 10)) self.Orderinkoopnummer = QLabel() q2Edit = QLineEdit(str(rpordartlev[15])) q2Edit.setAlignment(Qt.AlignRight) q2Edit.setFixedWidth(90) q2Edit.setDisabled(True) q2Edit.setStyleSheet("QLineEdit { font-size: 10pt; font-family: Arial; color: black }") self.Leveranciernummer = QLabel() q4Edit = QLineEdit(str(rpordartlev[16])) q4Edit.setFixedWidth(90) q4Edit.setAlignment(Qt.AlignRight) q4Edit.setStyleSheet("QLineEdit { font-size: 10pt; font-family: Arial; color: black }") q4Edit.setDisabled(True) self.Besteldatum = QLabel() q5Edit = QLineEdit(str(rpordartlev[17])) q5Edit.setFixedWidth(90) q5Edit.setStyleSheet("QLineEdit { font-size: 10pt; font-family: Arial; color: black }") q5Edit.setDisabled(True) self.Goedgekeurd = QLabel() q8Edit = QLineEdit(str(rpordartlev[18])) q8Edit.setFixedWidth(90) q8Edit.setStyleSheet("QLineEdit { font-size: 10pt; font-family: Arial; color: black }") q8Edit.setDisabled(True) self.Betaald = QLabel() q11Edit = QLineEdit(str(rpordartlev[19])) q11Edit.setFixedWidth(90) q11Edit.setStyleSheet("QLineEdit { font-size: 10pt; font-family: Arial; color: black }") q11Edit.setDisabled(True) self.Afgemeld = QLabel() q12Edit = QLineEdit(str(rpordartlev[20])) q12Edit.setFixedWidth(90) q12Edit.setStyleSheet("QLineEdit { font-size: 10pt; font-family: Arial; color: black }") q12Edit.setDisabled(True) self.Leveranciernummer = QLabel() q13Edit = QLineEdit(str(rpordartlev[21])) q13Edit.setFixedWidth(100) q13Edit.setAlignment(Qt.AlignRight) q13Edit.setStyleSheet("QLineEdit { font-size: 10pt; font-family: Arial; color: black }") q13Edit.setDisabled(True) self.Bedrijfsnaam = QLabel() q19Edit = QLineEdit(str(rpordartlev[22])) q19Edit.setFixedWidth(380) q19Edit.setStyleSheet("QLineEdit { font-size: 10pt; font-family: Arial; color: black }") q19Edit.setDisabled(True) self.Rechtsvorm = QLabel() q14Edit = QLineEdit(str(rpordartlev[23])) q14Edit.setFixedWidth(100) q14Edit.setStyleSheet("QLineEdit { font-size: 10pt; font-family: Arial; color: black }") q14Edit.setDisabled(True) self.Artikelnummer= QLabel() q15Edit = QLineEdit(str(rpordartlev[24])) q15Edit.setDisabled(True) q15Edit.setFixedWidth(100) q15Edit.setAlignment(Qt.AlignRight) q15Edit.setStyleSheet("QLineEdit { font-size: 10pt; font-family: Arial; color: black }") q15Edit.setDisabled(True) self.Artikelomschrijving = QLabel() q16Edit = QLineEdit(str(rpordartlev[25])) q16Edit.setFixedWidth(370) q16Edit.setStyleSheet("QLineEdit { font-size: 10pt; font-family: Arial; color: black }") q16Edit.setDisabled(True) self.Bestelaantal = QLabel() q18Edit = QLineEdit('{:12.2f}'.format(rpordartlev[3])) q18Edit.setFixedWidth(90) q18Edit.setAlignment(Qt.AlignRight) q18Edit.setStyleSheet("QLineEdit { font-size: 10pt; font-family: Arial; color: black }") q18Edit.setDisabled(True) self.Inkoopprijs = QLabel() q17Edit = QLineEdit('{:12.2f}'.format(rpordartlev[4])) q17Edit.setFixedWidth(100) q17Edit.setAlignment(Qt.AlignRight) q17Edit.setStyleSheet("QLineEdit { font-size: 10pt; font-family: Arial; color: black }") q17Edit.setDisabled(True) self.Levering_start = QLabel() q20Edit = QLineEdit(str(rpordartlev[5])) q20Edit.setFixedWidth(100) q20Edit.setStyleSheet("QLineEdit { font-size: 10pt; font-family: Arial; color: black }") q20Edit.setDisabled(True) self.Levering_eind = QLabel() q28Edit = QLineEdit(str(rpordartlev[6])) q28Edit.setFixedWidth(100) q28Edit.setStyleSheet("QLineEdit { font-size: 10pt; font-family: Arial; color: black }") q28Edit.setDisabled(True) self.Reclamatiedatum = QLabel() q21Edit = QLineEdit(str(rpordartlev[7])) q21Edit.setFixedWidth(100) q21Edit.setStyleSheet("QLineEdit { font-size: 10pt; font-family: Arial; color: black }") q21Edit.setDisabled(True) self.Reclamatieaantal = QLabel() q22Edit = QLineEdit('{:12.2f}'.format(rpordartlev[8])) q22Edit.setFixedWidth(100) q22Edit.setAlignment(Qt.AlignRight) q22Edit.setStyleSheet("QLineEdit { font-size: 10pt; font-family: Arial; color: black }") q22Edit.setDisabled(True) self.Ontvangdatum = QLabel() q23Edit = QLineEdit(str(rpordartlev[9])) q23Edit.setFixedWidth(100) q23Edit.setStyleSheet("QLineEdit { font-size: 10pt; font-family: Arial; color: black }") q23Edit.setDisabled(True) self.Ontvangstaantal = QLabel() q24Edit = QLineEdit('{:12.2f}'.format(rpordartlev[10])) q24Edit.setFixedWidth(100) q24Edit.setAlignment(Qt.AlignRight) q24Edit.setStyleSheet("QLineEdit { font-size: 10pt; font-family: Arial; color: black }") q24Edit.setDisabled(True) self.Acceptatiedatum = QLabel() q25Edit = QLineEdit(str(rpordartlev[11])) q25Edit.setFixedWidth(100) q25Edit.setStyleSheet("QLineEdit { font-size: 10pt; font-family: Arial; color: black }") q25Edit.setDisabled(True) self.Aantalgeaccepteerd = QLabel() q26Edit = QLineEdit('{:12.2f}'.format(rpordartlev[12])) q26Edit.setFixedWidth(100) q26Edit.setAlignment(Qt.AlignRight) q26Edit.setStyleSheet("QLineEdit { font-size: 10pt; font-family: Arial; color: black }") q26Edit.setDisabled(True) self.Betaaldaantal = QLabel() q27Edit = QLineEdit('{:12.2f}'.format(rpordartlev[13])) q27Edit.setFixedWidth(100) q27Edit.setAlignment(Qt.AlignRight) q27Edit.setStyleSheet("QLineEdit { font-size: 10pt; font-family: Arial; color: black }") q27Edit.setDisabled(True) grid = QGridLayout() grid.setSpacing(20) lbl = QLabel() pixmap = QPixmap('./images/logos/verbinding.jpg') lbl.setPixmap(pixmap) grid.addWidget(lbl ,0 , 0) logo = QLabel() pixmap = QPixmap('./images/logos/logo.jpg') logo.setPixmap(pixmap) grid.addWidget(logo , 0, 5, 1, 1, Qt.AlignRight) self.setFont(QFont('Arial', 10)) lbl1 = QLabel('Ordergegevens') lbl1.setStyleSheet("font: 12pt Comic Sans MS") grid.addWidget(lbl1, 1, 0) grid.addWidget(QLabel('Orderinkoopnummer'), 2, 0) grid.addWidget(q2Edit, 2, 1) grid.addWidget(QLabel('Leveranciernummer'), 2, 2) grid.addWidget(q4Edit, 2, 3) grid.addWidget(QLabel('Besteldatum'), 2, 4) grid.addWidget(q5Edit, 2 , 5) grid.addWidget(QLabel('Goedgekeurd'), 3, 0) grid.addWidget(q8Edit, 3, 1) grid.addWidget(QLabel('Betaaldatum'), 3, 2) grid.addWidget(q11Edit, 3, 3) grid.addWidget(QLabel('Afgemeld'), 3, 4) grid.addWidget(q12Edit, 3, 5) lbl2 = QLabel('Orderregelgegevens'+' - regelnummer: '+str(rpordartlev[14])) lbl2.setStyleSheet("font: 12pt Comic Sans MS") grid.addWidget(lbl2, 5, 0, 1, 4) grid.addWidget(QLabel('Leveranciernummer'), 6, 0) grid.addWidget(q13Edit, 6, 1) grid.addWidget(QLabel('Bedrijfsnaam'), 7, 0) grid.addWidget(q19Edit, 7, 1, 1, 4) grid.addWidget(q14Edit, 7, 4) grid.addWidget(QLabel('Artikelnummer'), 8, 0) grid.addWidget(q15Edit, 8, 1) pixmap = QPixmap(rpordartlev[28]) lbl3 = QLabel() lbl3.setPixmap(pixmap) grid.addWidget(lbl3 , 8, 3, 2, 3, Qt.AlignTop) grid.addWidget(QLabel('Artikelomschrijving'), 10, 0) grid.addWidget(q16Edit, 10, 1, 1, 4) grid.addWidget(QLabel('Bestelaantal'), 11, 0) grid.addWidget(q18Edit, 11, 1) grid.addWidget(QLabel('Inkoopprijs'), 11, 2) grid.addWidget(q17Edit, 11, 3) grid.addWidget(QLabel('Levering start'), 11, 4) grid.addWidget(q20Edit, 11, 5) grid.addWidget(QLabel('Levering eind'), 12, 0) grid.addWidget(q28Edit, 12, 1) grid.addWidget(QLabel('Reclamatiedatum'), 12, 2) grid.addWidget(q21Edit, 12, 3) grid.addWidget(QLabel('Reclamaties aantal'), 12, 4) grid.addWidget(q22Edit, 12, 5) grid.addWidget(QLabel('Ontvangstdatum'), 13, 0) grid.addWidget(q23Edit, 13, 1) grid.addWidget(QLabel('Ontvangen aantal'), 13, 2) grid.addWidget(q24Edit, 13, 3) grid.addWidget(QLabel('Acceptatie datum'), 13, 4) grid.addWidget(q25Edit, 13, 5) grid.addWidget(QLabel('Acceptatie aantal'), 14, 0) grid.addWidget(q26Edit, 14, 1) grid.addWidget(QLabel('Betaald aantal'), 14, 2) grid.addWidget(q27Edit, 14, 3) grid.addWidget(QLabel('\u00A9 2017 all rights reserved [email protected]'), 16, 0, 1, 6, Qt.AlignCenter) self.setLayout(grid) self.setGeometry(500, 200, 350, 300) cancelBtn = QPushButton('Sluiten') cancelBtn.clicked.connect(self.close) grid.addWidget(cancelBtn, 15, 5, 1, 1, Qt.AlignRight) cancelBtn.setFont(QFont("Arial",10)) cancelBtn.setFixedWidth(100) cancelBtn.setStyleSheet("color: black; background-color: gainsboro") window = Widget() window.exec_() win = Window(data_list, header) win.exec_() inkooporderKeuze(m_email)
def maandPeriode(m_email): import validZt class Widget(QDialog): def __init__(self, parent=None): super(Widget, self).__init__(parent) self.setWindowTitle("Periode opgeven tbv loonspecificaties") self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setFont(QFont('Arial', 10)) self.Betaalperiode = QLabel() betEdit = QLineEdit() betEdit.setFixedWidth(100) betEdit.setFont(QFont("Arial", 10)) betEdit.textChanged.connect(self.betChanged) reg_ex = QRegExp('^[2]{1}[01]{1}[0-9]{2}[-][0-1]{1}[0-9]{1}$') input_validator = QRegExpValidator(reg_ex, betEdit) betEdit.setValidator(input_validator) grid = QGridLayout() grid.setSpacing(20) lbl = QLabel() pixmap = QPixmap('./images/logos/verbinding.jpg') lbl.setPixmap(pixmap) grid.addWidget(lbl, 0, 0, 1, 2) logo = QLabel() pixmap = QPixmap('./images/logos/logo.jpg') logo.setPixmap(pixmap) grid.addWidget(logo, 0, 1, 1, 1, Qt.AlignRight) grid.addWidget(QLabel('Betaalperiode jjjj-mm'), 1, 0) grid.addWidget(betEdit, 1, 1) cancelBtn = QPushButton('Sluiten') cancelBtn.clicked.connect(lambda: windowSluit(self, m_email)) applyBtn = QPushButton('Uitvoeren') applyBtn.clicked.connect(self.accept) grid.addWidget(applyBtn, 2, 1, 1, 1, Qt.AlignRight) applyBtn.setFont(QFont("Arial", 10)) applyBtn.setFixedWidth(100) applyBtn.setStyleSheet( "color: black; background-color: gainsboro") grid.addWidget(cancelBtn, 2, 0, 1, 1, Qt.AlignRight) cancelBtn.setFont(QFont("Arial", 10)) cancelBtn.setFixedWidth(100) cancelBtn.setStyleSheet( "color: black; background-color: gainsboro") grid.addWidget( QLabel('\u00A9 2017 all rights reserved [email protected]'), 3, 0, 1, 2, Qt.AlignCenter) self.setLayout(grid) self.setGeometry(400, 300, 150, 150) def betChanged(self, text): self.Betaalperiode.setText(text) def returnBetaalperiode(self): return self.Betaalperiode.text() @staticmethod def getData(parent=None): dialog = Widget(parent) dialog.exec_() return [dialog.returnBetaalperiode()] window = Widget() data = window.getData() if data[0] and validZt.zt(data[0], 20): mjrmnd = data[0] controleUren(mjrmnd, m_email) else: ongPeriode() maandPeriode(m_email)
def toonIclusterartikelen(zoekterm, m_email): import validZt class MyWindow(QDialog): def __init__(self, data_list, header, *args): QWidget.__init__( self, *args, ) self.setGeometry(50, 50, 1800, 900) self.setWindowTitle('Artikelen per cluster opvragen') self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setWindowFlags(self.windowFlags() | Qt.WindowSystemMenuHint | Qt.WindowMinMaxButtonsHint) table_model = MyTableModel(self, data_list, header) table_view = QTableView() table_view.setModel(table_model) font = QFont("Arial", 10) table_view.setFont(font) table_view.resizeColumnsToContents() table_view.setSelectionBehavior(QTableView.SelectRows) table_view.clicked.connect(showSelection) layout = QVBoxLayout(self) layout.addWidget(table_view) self.setLayout(layout) class MyTableModel(QAbstractTableModel): def __init__(self, parent, mylist, header, *args): QAbstractTableModel.__init__(self, parent, *args) self.mylist = mylist self.header = header def rowCount(self, parent): return len(self.mylist) def columnCount(self, parent): return len(self.mylist[0]) def data(self, index, role): veld = self.mylist[index.row()][index.column()] if not index.isValid(): return None elif role == Qt.TextAlignmentRole and (type(veld) == float or type(veld) == int): return Qt.AlignRight | Qt.AlignVCenter elif role != Qt.DisplayRole: return None if type(veld) == float: return '{:12.2f}'.format(veld) else: return veld def headerData(self, col, orientation, role): if orientation == Qt.Horizontal and role == Qt.DisplayRole: return self.header[col] return None header = ['Clusternr', 'Omschrijving', 'Prijs', 'Eenheid', 'Materialen', 'Lonen',\ 'Diensten', 'Materiëel', 'Inhuur', 'Zagen', 'Schaven','Steken',\ 'Boren', 'Frezen', 'Draaien-klein', 'Draaien-groot', 'Tappen',\ 'Nube-draaien', 'Nube-bewerken', 'Knippen', 'Kanten',\ 'Stansen', 'Lassen-CO2', 'Lassen-Hand', 'Verpakken', 'Verzinken',\ 'Moffelen', 'Schilderen', 'Spuiten', 'Ponsen','Persen','Gritstralen'] metadata = MetaData() iclusters = Table('iclusters', metadata, Column('iclusterID', Integer(), primary_key=True), Column('omschrijving', String), Column('prijs', Float), Column('eenheid', String), Column('materialen', Float), Column('lonen', Float), Column('diensten', Float), Column('materieel', Float), Column('inhuur', Float), Column('zagen', Float), Column('schaven', Float), Column('steken', Float), Column('boren', Float), Column('frezen', Float), Column('draaien_klein', Float), Column('draaien_groot', Float), Column('tappen', Float), Column('nube_draaien', Float), Column('nube_bewerken', Float), Column('knippen', Float), Column('kanten', Float), Column('stansen', Float), Column('lassen_co2', Float), Column('lassen_hand', Float), Column('verpakken', Float), Column('verzinken', Float), Column('moffelen', Float), Column('schilderen', Float), Column('spuiten', Float), Column('ponsen', Float), Column('persen', Float), Column('gritstralen', Float)) engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem') con = engine.connect() if zoekterm == '0': sel = select([iclusters]).order_by(iclusters.c.iclusterID) elif validZt.zt(zoekterm, 25): sel = select([iclusters]).where(iclusters.c.iclusterID.ilike(zoekterm+'%'))\ .order_by(iclusters.c.iclusterID) else: ongInvoer() zoeken(m_email) if con.execute(sel).fetchone(): rp = con.execute(sel) else: geenRecord() zoeken(m_email) data_list = [] for row in rp: data_list += [(row)] def showSelection(idx): clusternr = str(idx.data()) if idx.column() == 0: metadata = MetaData() iclusters = Table('iclusters', metadata, Column('iclusterID', Integer, primary_key=True), Column('omschrijving', String)) artikelen = Table('artikelen', metadata, Column('artikelID', Integer, primary_key=True), Column('artikelomschrijving', String), Column('artikelprijs', Float)) icluster_artikelen = Table( 'icluster_artikelen', metadata, Column('icluster_artID', Integer, primary_key=True), Column('iclusterID', None, ForeignKey('iclusters.iclusterID')), Column('artikelID', None, ForeignKey('artikelen.artikelID')), Column('hoeveelheid', Float)) engine = create_engine( 'postgresql+psycopg2://postgres@localhost/bisystem') con = engine.connect() selcl = select([icluster_artikelen, iclusters, artikelen]).where(and_\ (icluster_artikelen.c.iclusterID == clusternr, icluster_artikelen.c.iclusterID==\ iclusters.c.iclusterID, icluster_artikelen.c.artikelID == artikelen.c.artikelID)) if con.execute(selcl).fetchone(): rpsel = con.execute(selcl) else: geenRegels() return regel = 0 for row in rpsel: regel += 1 class MainWindow(QDialog): def __init__(self): QDialog.__init__(self) grid = QGridLayout() grid.setSpacing(20) self.setWindowTitle( "Opvragen artikelregels per cluster") self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setFont(QFont('Arial', 10)) q1Edit = QLineEdit(str(row[5])) q1Edit.setFixedWidth(400) q1Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q1Edit.setDisabled(True) q6Edit = QLineEdit(str(row[2])) q6Edit.setFixedWidth(150) q6Edit.setAlignment(Qt.AlignRight) q6Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q6Edit.setDisabled(True) q2Edit = QLineEdit('{:12.2f}'.format(row[8])) q2Edit.setFixedWidth(150) q2Edit.setAlignment(Qt.AlignRight) q2Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q2Edit.setDisabled(True) q3Edit = QLineEdit('{:12.2f}'.format(row[3])) q3Edit.setFixedWidth(150) q3Edit.setAlignment(Qt.AlignRight) q3Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q3Edit.setDisabled(True) q5Edit = QLineEdit(str(row[7])) q5Edit.setFixedWidth(400) q5Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q5Edit.setDisabled(True) grid = QGridLayout() grid.setSpacing(20) lbl1 = QLabel('Clusternummer') lbl1.setAlignment(Qt.AlignRight | Qt.AlignVCenter) grid.addWidget(lbl1, 1, 0) lbl2 = QLabel(clusternr) grid.addWidget(lbl2, 1, 1) lbl9 = QLabel('Regelnummer: ') lbl9.setAlignment(Qt.AlignRight | Qt.AlignVCenter) grid.addWidget(lbl9, 1, 2) lbl10 = QLabel(str(regel)) grid.addWidget(lbl10, 1, 3) lbl3 = QLabel('Clusteromschrijving') lbl3.setAlignment(Qt.AlignRight | Qt.AlignVCenter) grid.addWidget(lbl3, 2, 0) grid.addWidget(q1Edit, 2, 1, 1, 3) lbl8 = QLabel('Artikelnummer') lbl8.setAlignment(Qt.AlignRight | Qt.AlignVCenter) grid.addWidget(lbl8, 3, 0) grid.addWidget(q6Edit, 3, 1) lbl4 = QLabel('Artikelprijs') lbl4.setAlignment(Qt.AlignRight | Qt.AlignVCenter) grid.addWidget(lbl4, 4, 0) grid.addWidget(q2Edit, 4, 1) lbl5 = QLabel('Hoeveelheid\nArtikelen per cluster') lbl5.setAlignment(Qt.AlignRight | Qt.AlignVCenter) grid.addWidget(lbl5, 5, 0) grid.addWidget(q3Edit, 5, 1) lbl7 = QLabel('Artikelomschrijving') lbl7.setAlignment(Qt.AlignRight | Qt.AlignVCenter) grid.addWidget(lbl7, 6, 0) grid.addWidget(q5Edit, 6, 1, 1, 3) lbl = QLabel() pixmap = QPixmap('./images/logos/verbinding.jpg') lbl.setPixmap(pixmap) grid.addWidget(lbl, 0, 0, 1, 2) logo = QLabel() pixmap = QPixmap('./images/logos/logo.jpg') logo.setPixmap(pixmap) grid.addWidget(logo, 0, 3, 1, 1, Qt.AlignRight) grid.addWidget( QLabel( '\u00A9 2017 all rights reserved [email protected]' ), 8, 0, 1, 4, Qt.AlignCenter) self.setLayout(grid) self.setGeometry(500, 300, 150, 150) applyBtn = QPushButton('Regels') applyBtn.clicked.connect(self.accept) grid.addWidget(applyBtn, 7, 3, 1, 1, Qt.AlignRight) applyBtn.setFont(QFont("Arial", 10)) applyBtn.setFixedWidth(100) applyBtn.setStyleSheet( "color: black; background-color: gainsboro") mainWin = MainWindow() mainWin.exec_() win = MyWindow(data_list, header) win.exec_() zoeken(m_email)
def toonAccounts(keuze, zoekterm, m_email): import validZt metadata = MetaData() accounts = Table('accounts', metadata, Column('accountID', Integer(), primary_key=True), Column('aanhef', String(8)), Column('voornaam', String(30), nullable=False), Column('tussenvoegsel', String(10)), Column('achternaam', String(50), nullable=False), Column('postcode', String(6), nullable=False), Column('huisnummer', String(5), nullable=False), Column('toevoeging', String), Column('email', String, nullable=False), Column('telnr', String(10)), Column('account_count', Integer(), nullable=False), Column('geboortedatum', String)) leveranciers = Table('leveranciers', metadata, Column('leverancierID', Integer(), primary_key=True), Column('bedrijfsnaam', String), Column('rechtsvorm', String)) lev_accounts = Table( 'lev_accounts', metadata, Column('levaccID', Integer, primary_key=True), Column('accountID', None, ForeignKey('accounts.accountID')), Column('leverancierID', None, ForeignKey('leveranciers.leverancierID'))) werknemers = Table( 'werknemers', metadata, Column('werknemerID', Integer, primary_key=True), Column('accountID', None, ForeignKey('accounts.accountID')), Column('loonID', Integer)) engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem') conn = engine.connect() if keuze == 1: sel = select([accounts]).order_by(accounts.c.accountID) elif keuze == 2 and validZt.zt(zoekterm, 9): sel = select([accounts]).where(accounts.c.postcode.ilike(zoekterm)) elif keuze == 3 and validZt.zt(zoekterm, 12): sel = select([accounts ]).where(accounts.c.email.ilike('%' + zoekterm + '%')) elif keuze == 4: sel = select([accounts]).where( accounts.c.achternaam.ilike('%' + zoekterm + '%')) elif keuze == 5: sel = select([accounts, leveranciers.c.leverancierID, leveranciers.c.bedrijfsnaam,\ leveranciers.c.rechtsvorm]).where(and_(lev_accounts.c.accountID ==\ accounts.c.accountID, leveranciers.c.leverancierID == lev_accounts.c.leverancierID)) elif keuze == 6: sel = select([accounts, werknemers]).where(werknemers.c.accountID ==\ accounts.c.accountID) else: ongInvoer() accKeuze(m_email) if conn.execute(sel).fetchone(): rpaccount = conn.execute(sel) else: geenRecord() accKeuze(m_email) class MyWindow(QDialog): def __init__(self, data_list, header, *args): QWidget.__init__( self, *args, ) self.setGeometry(50, 50, 1500, 900) self.setWindowTitle('Accountgegevens opvragen') self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setWindowFlags(self.windowFlags() | Qt.WindowSystemMenuHint | Qt.WindowMinMaxButtonsHint) table_model = MyTableModel(self, data_list, header) table_view = QTableView() table_view.setModel(table_model) font = QFont("Arial", 10) table_view.setFont(font) if keuze == 6: table_view.setColumnHidden(13, True) table_view.setColumnHidden(14, True) table_view.resizeColumnsToContents() table_view.setSelectionBehavior(QTableView.SelectRows) table_view.clicked.connect(showAccount) layout = QVBoxLayout(self) layout.addWidget(table_view) self.setLayout(layout) class MyTableModel(QAbstractTableModel): def __init__(self, parent, mylist, header, *args): QAbstractTableModel.__init__(self, parent, *args) self.mylist = mylist self.header = header def rowCount(self, parent): return len(self.mylist) def columnCount(self, parent): return len(self.mylist[0]) def data(self, index, role): veld = self.mylist[index.row()][index.column()] if not index.isValid(): return None elif role == Qt.TextAlignmentRole and (type(veld) == float or type(veld) == int): return Qt.AlignRight | Qt.AlignVCenter elif role != Qt.DisplayRole: return None if type(veld) == float: return '{:12.2f}'.format(veld) else: return veld def headerData(self, col, orientation, role): if orientation == Qt.Horizontal and role == Qt.DisplayRole: return self.header[col] return None header = ['Accountnummer','Aanhef', 'Voornaam','Tussenvoegsel', 'Achternaam', 'Postcode',\ 'Huisnummer','Toevoeging', 'E-mail', 'Telefoonnummer', 'Accountcount', 'Geboortedatum'] if keuze == 5: header1 = ['Leveranciernummer', 'Bedrijfsnaam', 'Rechtsvorm'] header.extend(header1) elif keuze == 6: header2 = ['Werknemernummer', 'Account', 'Loonschaal'] header.extend(header2) data_list = [] for row in rpaccount: data_list += [(row)] def showAccount(idx): maccountnr = idx.data() if idx.column() == 0: engine = create_engine( 'postgresql+psycopg2://postgres@localhost/bisystem') conn = engine.connect() sel = select([accounts, werknemers ]).where(accounts.c.accountID == maccountnr) rpaccount = conn.execute(sel).first() maccountnr = rpaccount[0] maanhef = rpaccount[1] mvoornaam = rpaccount[2] mtussenv = rpaccount[3] machternaam = rpaccount[4] mpostcode = rpaccount[5] mhuisnr = rpaccount[6] mhuisnr = int(mhuisnr) mtoev = rpaccount[7] m_email = rpaccount[8] mtelnr = rpaccount[9] mcount = rpaccount[10] mcount = int(mcount) + 1 mgebdat = rpaccount[11] mhuisnr = int(mhuisnr) mstrtplts = checkpostcode(mpostcode, mhuisnr) mstraat = mstrtplts[0] mplaats = mstrtplts[1] class Widget(QDialog): def __init__(self, parent=None): super(Widget, self).__init__(parent) self.setWindowTitle("Accountgegevens opvragen") self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setFont(QFont('Arial', 10)) self.Aanhef = QLabel() q2Edit = QLineEdit(maanhef) q2Edit.setFixedWidth(80) q2Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q2Edit.setDisabled(True) self.Voornaam = QLabel() q3Edit = QLineEdit(mvoornaam) q3Edit.setFixedWidth(200) q3Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q3Edit.setDisabled(True) self.Tussenvoegsel = QLabel() q4Edit = QLineEdit(mtussenv) q4Edit.setFixedWidth(80) q4Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q4Edit.setDisabled(True) self.Achternaam = QLabel() q5Edit = QLineEdit(machternaam) q5Edit.setFixedWidth(400) q5Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q5Edit.setDisabled(True) self.Straat = QLabel() q6Edit = QLineEdit(mstraat) q6Edit.setFixedWidth(500) q6Edit.setDisabled(True) q6Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) self.Huisnummer = QLabel() q7Edit = QLineEdit(str(mhuisnr)) q7Edit.setAlignment(Qt.AlignRight) q7Edit.setFixedWidth(60) q7Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q7Edit.setDisabled(True) self.Toevoeging = QLabel() q8Edit = QLineEdit(mtoev) q8Edit.setFixedWidth(80) q8Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q8Edit.setDisabled(True) self.Postcode = QLabel() q9Edit = QLineEdit(mpostcode) q9Edit.setFixedWidth(70) q9Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q9Edit.setDisabled(True) self.Woonplaats = QLabel() q10Edit = QLineEdit(mplaats) q10Edit.setFixedWidth(500) q10Edit.setDisabled(True) q10Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) self.email = QLabel() q11Edit = QLineEdit(m_email) q11Edit.setFixedWidth(300) q11Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q11Edit.setDisabled(True) self.Telefoonnr = QLabel() q15Edit = QLineEdit(mtelnr) q15Edit.setFixedWidth(100) q15Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q15Edit.setDisabled(True) self.Accountnummer = QLabel() q16Edit = QLineEdit(str(maccountnr)) q16Edit.setFixedWidth(100) q16Edit.setAlignment(Qt.AlignRight) q16Edit.setDisabled(True) q16Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) self.Geboortedatum = QLabel() q17Edit = QLineEdit(mgebdat) q17Edit.setFixedWidth(100) q17Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q17Edit.setDisabled(True) grid = QGridLayout() grid.setSpacing(20) lbl = QLabel() pixmap = QPixmap('./images/logos/verbinding.jpg') lbl.setPixmap(pixmap) grid.addWidget(lbl, 1, 0) logo = QLabel() pixmap = QPixmap('./images/logos/logo.jpg') logo.setPixmap(pixmap) grid.addWidget(logo, 1, 2, 1, 1, Qt.AlignRight) self.setFont(QFont('Arial', 10)) grid.addWidget(QLabel('Opvragen persoongegevens'), 1, 1) grid.addWidget(QLabel('Aanhef'), 2, 0) grid.addWidget(q2Edit, 2, 1) grid.addWidget(QLabel('Voornaam'), 3, 0) grid.addWidget(q3Edit, 3, 1) grid.addWidget(QLabel('Tussenvoegsel'), 4, 0) grid.addWidget(q4Edit, 4, 1) grid.addWidget(QLabel('Achternaam'), 5, 0) grid.addWidget(q5Edit, 5, 1, 1, 2) grid.addWidget(QLabel('Geboortedatum'), 6, 0) grid.addWidget(q17Edit, 6, 1) grid.addWidget(QLabel('Straat'), 7, 0) grid.addWidget(q6Edit, 7, 1, 1, 2) grid.addWidget(QLabel('Huisnummer'), 8, 0) grid.addWidget(q7Edit, 8, 1) grid.addWidget(QLabel('Toevoeging'), 8, 1, 1, 1, Qt.AlignRight) grid.addWidget(q8Edit, 8, 2) grid.addWidget(QLabel('Postcode'), 9, 0) grid.addWidget(q9Edit, 9, 1) grid.addWidget(QLabel('Woonplaats'), 10, 0) grid.addWidget(q10Edit, 10, 1, 1, 2) grid.addWidget(QLabel('e-mail'), 11, 0) grid.addWidget(q11Edit, 11, 1, 1, 2) grid.addWidget(QLabel('Telefoonnummer'), 12, 0) grid.addWidget(q15Edit, 12, 1) grid.addWidget(QLabel('Accountnummer'), 13, 0) grid.addWidget(q16Edit, 13, 1, 1, 2) grid.addWidget( QLabel( '\u00A9 2017 all rights reserved [email protected]' ), 15, 0, 1, 3, Qt.AlignCenter) self.setLayout(grid) self.setGeometry(500, 150, 350, 300) cancelBtn = QPushButton('Sluiten') cancelBtn.clicked.connect(self.close) grid.addWidget(cancelBtn, 14, 2, 1, 1, Qt.AlignRight) cancelBtn.setFont(QFont("Arial", 10)) cancelBtn.setFixedWidth(100) cancelBtn.setStyleSheet( "color: black; background-color: gainsboro") win = Widget() win.exec_() win = MyWindow(data_list, header) win.exec_() accKeuze(m_email)
def toonMutaties(keuze, zoekterm, m_email): import validZt metadata = MetaData() accounts = Table('accounts', metadata, Column('accountID', Integer, primary_key=True), Column('voornaam', String), Column('tussenvoegsel', String), Column('achternaam', String)) werknemers = Table('werknemers', metadata, Column('werknemerID', Integer(), primary_key=True), Column('accountID', None, ForeignKey('accounts.accountID')), Column('loonID', None, ForeignKey('lonen.loonID'))) wrkwnrln = Table('wrkwnrln', metadata, Column('wrkwnrurenID', Integer, primary_key=True), Column('werknemerID', None, ForeignKey('werknemers.werknemerID')), Column('werknummerID', Integer), Column('boekdatum', String(6)), Column('aantaluren', Float), Column('soort', String), Column('meerwerkstatus', Boolean), Column('bruto_loonbedrag', Float)) lonen = Table('lonen', metadata, Column('loonID', Integer, primary_key=True), Column('tabelloon', Float), Column('werkuur', Float), Column('reisuur', Float), Column('direct', Boolean)) engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem') con = engine.connect() if keuze == 1: columns = [accounts.c.accountID, accounts.c.voornaam, accounts.c.tussenvoegsel,\ accounts.c.achternaam, lonen.c.loonID, lonen.c.tabelloon, lonen.c.reisuur,\ wrkwnrln.c.aantaluren, wrkwnrln.c.soort,wrkwnrln.c.bruto_loonbedrag, \ wrkwnrln.c.werknummerID, wrkwnrln.c.boekdatum, wrkwnrln.c.meerwerkstatus] loonuren = select(columns).where(and_(accounts.c.accountID == werknemers.c.accountID,\ werknemers.c.werknemerID == wrkwnrln.c.werknemerID,\ lonen.c.loonID == werknemers.c.loonID)) loonuren = loonuren.select_from(wrkwnrln.join(werknemers).join(lonen).join(accounts))\ .order_by(accounts.c.accountID, desc(wrkwnrln.c.boekdatum)) elif keuze == 2 and validZt.zt(zoekterm, 24): columns = [accounts.c.accountID, accounts.c.voornaam, accounts.c.tussenvoegsel,\ accounts.c.achternaam, lonen.c.loonID, lonen.c.tabelloon, lonen.c.reisuur,\ wrkwnrln.c.aantaluren, wrkwnrln.c.soort,wrkwnrln.c.bruto_loonbedrag, \ wrkwnrln.c.werknummerID, wrkwnrln.c.boekdatum, wrkwnrln.c.meerwerkstatus] loonuren = select(columns).where(and_(accounts.c.accountID == werknemers.c.accountID,\ werknemers.c.werknemerID == wrkwnrln.c.werknemerID,\ lonen.c.loonID == werknemers.c.loonID, wrkwnrln.c.soort.ilike('%'+zoekterm+'%'))) loonuren = loonuren.select_from(wrkwnrln.join(werknemers).join(lonen).join(accounts))\ .order_by(accounts.c.achternaam, desc(wrkwnrln.c.boekdatum)) elif keuze == 3 and validZt.zt(zoekterm, 23): zoekterm = zoekterm.split('-') zk1 = int(zoekterm[0]) zk2 = int(zoekterm[1]) columns = [accounts.c.accountID, accounts.c.voornaam, accounts.c.tussenvoegsel,\ accounts.c.achternaam, lonen.c.loonID, lonen.c.tabelloon, lonen.c.reisuur,\ wrkwnrln.c.aantaluren, wrkwnrln.c.soort,wrkwnrln.c.bruto_loonbedrag, \ wrkwnrln.c.werknummerID, wrkwnrln.c.boekdatum, wrkwnrln.c.meerwerkstatus] loonuren = select(columns).where(and_(accounts.c.accountID == werknemers.c.accountID,\ werknemers.c.werknemerID == wrkwnrln.c.werknemerID,\ lonen.c.loonID == werknemers.c.loonID,\ lonen.c.loonID.between(zk1, zk2))) loonuren = loonuren.select_from(wrkwnrln.join(werknemers).join(lonen).join(accounts))\ .order_by(accounts.c.accountID, desc(wrkwnrln.c.boekdatum)) elif keuze == 4 and validZt.zt(zoekterm, 10): columns = [accounts.c.accountID, accounts.c.voornaam, accounts.c.tussenvoegsel,\ accounts.c.achternaam, lonen.c.loonID, lonen.c.tabelloon, lonen.c.reisuur,\ wrkwnrln.c.aantaluren, wrkwnrln.c.soort,wrkwnrln.c.bruto_loonbedrag, \ wrkwnrln.c.werknummerID, wrkwnrln.c.boekdatum, wrkwnrln.c.meerwerkstatus] loonuren = select(columns).where(and_(accounts.c.accountID == werknemers.c.accountID,\ werknemers.c.werknemerID == wrkwnrln.c.werknemerID,\ lonen.c.loonID == werknemers.c.loonID,\ wrkwnrln.c.boekdatum.like(zoekterm+'%'))) loonuren = loonuren.select_from(wrkwnrln.join(werknemers).join(lonen).join(accounts))\ .order_by(accounts.c.accountID, desc(wrkwnrln.c.boekdatum)) elif keuze == 5 and validZt.zt(zoekterm, 21): zoekterm = int(zoekterm) columns = [accounts.c.accountID, accounts.c.voornaam, accounts.c.tussenvoegsel,\ accounts.c.achternaam, lonen.c.loonID, lonen.c.tabelloon, lonen.c.reisuur,\ wrkwnrln.c.aantaluren, wrkwnrln.c.soort,wrkwnrln.c.bruto_loonbedrag, \ wrkwnrln.c.werknummerID, wrkwnrln.c.boekdatum, wrkwnrln.c.meerwerkstatus] loonuren = select(columns).where(and_(accounts.c.accountID == werknemers.c.accountID,\ werknemers.c.werknemerID == wrkwnrln.c.werknemerID,\ lonen.c.loonID == werknemers.c.loonID,\ wrkwnrln.c.werknummerID == zoekterm)) loonuren = loonuren.select_from(wrkwnrln.join(werknemers).join(lonen).join(accounts))\ .order_by(accounts.c.achternaam, desc(wrkwnrln.c.boekdatum)) elif keuze == 6 and validZt.zt(zoekterm, 22): zoekterm=zoekterm.upper() if zoekterm[0] == 'I' or zoekterm == 'i': zoekterm = False else: zoekterm = True columns = [accounts.c.accountID, accounts.c.voornaam, accounts.c.tussenvoegsel,\ accounts.c.achternaam, lonen.c.loonID, lonen.c.tabelloon, lonen.c.reisuur,\ wrkwnrln.c.aantaluren, wrkwnrln.c.soort,wrkwnrln.c.bruto_loonbedrag, \ wrkwnrln.c.werknummerID, wrkwnrln.c.boekdatum, wrkwnrln.c.meerwerkstatus] loonuren = select(columns).where(and_(accounts.c.accountID == werknemers.c.accountID,\ werknemers.c.werknemerID == wrkwnrln.c.werknemerID,\ lonen.c.loonID == werknemers.c.loonID,\ lonen.c.direct == zoekterm)) loonuren = loonuren.select_from(wrkwnrln.join(werknemers).join(lonen).join(accounts))\ .order_by(accounts.c.achternaam, desc(wrkwnrln.c.boekdatum)) elif keuze == 7: columns = [accounts.c.accountID, accounts.c.voornaam, accounts.c.tussenvoegsel,\ accounts.c.achternaam, lonen.c.loonID, lonen.c.tabelloon, lonen.c.reisuur,\ wrkwnrln.c.aantaluren, wrkwnrln.c.soort,wrkwnrln.c.bruto_loonbedrag, \ wrkwnrln.c.werknummerID, wrkwnrln.c.boekdatum, wrkwnrln.c.meerwerkstatus] loonuren = select(columns).where(and_(accounts.c.accountID == werknemers.c.accountID,\ werknemers.c.werknemerID == wrkwnrln.c.werknemerID,\ lonen.c.loonID == werknemers.c.loonID,\ accounts.c.achternaam.ilike('%'+zoekterm+'%'))) loonuren = loonuren.select_from(wrkwnrln.join(werknemers).join(lonen).join(accounts))\ .order_by(accounts.c.achternaam, desc(wrkwnrln.c.boekdatum)) else: ongInvoer() loonKeuze(m_email) if con.execute(loonuren).fetchone(): rpwrklonen = con.execute(loonuren) else: geenRecord() loonKeuze(m_email) class Widget(QDialog): def __init__(self, data_list, header, *args): QWidget.__init__(self, *args,) self.setGeometry(10, 50, 1400, 900) self.setWindowTitle('Loonbetalingen opvragen') self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setWindowFlags(self.windowFlags()| Qt.WindowSystemMenuHint | Qt.WindowMinMaxButtonsHint) table_model = MyTableModel(self, data_list, header) table_view = QTableView() table_view.setModel(table_model) font = QFont("Arial", 10) table_view.setFont(font) table_view.resizeColumnsToContents() table_view.setSelectionBehavior(QTableView.SelectRows) #table_view.clicked.connect(selectRow) #table_view.clicked.connect(showSelection) layout = QVBoxLayout(self) layout.addWidget(table_view) self.setLayout(layout) class MyTableModel(QAbstractTableModel): def __init__(self, parent, mylist, header, *args): QAbstractTableModel.__init__(self, parent, *args) self.mylist = mylist self.header = header def rowCount(self, parent): return len(self.mylist) def columnCount(self, parent): return len(self.mylist[0]) def data(self, index, role): veld = self.mylist[index.row()][index.column()] if not index.isValid(): return None elif role == Qt.TextAlignmentRole and (type(veld) == float or type(veld) == int): return Qt.AlignRight | Qt.AlignVCenter elif role != Qt.DisplayRole: return None if type(veld) == float: return '{:12.2f}'.format(veld) else: return veld def headerData(self, col, orientation, role): if orientation == Qt.Horizontal and role == Qt.DisplayRole: return self.header[col] return None header = ['Acountnummer', 'Voornaam', 'Tussenvoegsel', 'Achternaam', 'Loonschaal',\ 'Tabelloon', 'Reisuurloon','Aantal uren','Soort uren', 'Bruto-loonbedrag',\ 'Werknummer', 'Boekdatum','Meerwerkstatus'] data_list=[] for row in rpwrklonen: data_list += [(row)] win = Widget(data_list, header) win.exec_() loonKeuze(m_email)
def toonWerknemers(keuze,zoekterm, m_email): import validZt metadata = MetaData() werknemers = Table('werknemers', metadata, Column('accountID', None, ForeignKey('accounts.accountID')), Column('werknemerID', Integer(), primary_key=True), Column('loonID', None, ForeignKey('lonen.loonID')), Column('loontrede', Integer), Column('loonheffing', Float), Column('pensioenpremie', Float), Column('reservering_vakantietoeslag', Float), Column('werkgevers_pensioenpremie', Float), Column('periodieke_uitkeringen', Float), Column('overige_inhoudingen', Float), Column('overige_vergoedingen', Float), Column('bedrijfsauto', Float), Column('reiskosten_vergoeding', Float), Column('indienst', String), Column('verlofsaldo', Float), Column('extraverlof', Float)) accounts = Table('accounts', metadata, Column('accountID', Integer, primary_key=True), Column('aanhef', String(8)), Column('voornaam', String(30), nullable=False), Column('tussenvoegsel', String(10)), Column('achternaam', String(50), nullable=False), Column('geboortedatum', String)) lonen = Table('lonen', metadata, Column('loonID', Integer, primary_key=True), Column('tabelloon', Float), Column('reisuur', Float), Column('maandloon', Float)) engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem') conn = engine.connect() if keuze == 1: selwerkn = select([werknemers, accounts, lonen]).where(and_(werknemers.c.accountID == accounts.c.accountID, werknemers.c.loonID == lonen.c.loonID)).order_by(werknemers.c.accountID) elif keuze == 2: selwerkn = select([werknemers, accounts, lonen]).where(and_(werknemers.c.accountID == accounts.c.accountID, werknemers.c.loonID == lonen.c.loonID, accounts.c.achternaam.\ ilike('%'+zoekterm+'%'))).order_by(werknemers.c.accountID) elif keuze == 3 and validZt.zt(zoekterm, 13): selwerkn = select([werknemers, accounts, lonen]).where(and_(werknemers.c.accountID == accounts.c.accountID, werknemers.c.loonID == lonen.c.loonID, werknemers.c.loonID == int(zoekterm))).\ order_by(werknemers.c.loonID, werknemers.c.accountID) elif keuze == 4 and validZt.zt(zoekterm, 1): selwerkn = select([werknemers, accounts, lonen]).where(and_(werknemers.c.accountID == accounts.c.accountID, werknemers.c.loonID == lonen.c.loonID, werknemers.c.accountID == int(zoekterm))) elif keuze == 5 and validZt.zt(zoekterm, 14): selwerkn = select([werknemers, accounts, lonen]).where(and_(werknemers.c.accountID == accounts.c.accountID, werknemers.c.loonID == lonen.c.loonID, lonen.c.tabelloon < int(zoekterm))).\ order_by(lonen.c.tabelloon) elif keuze == 6 and validZt.zt(zoekterm, 14): selwerkn = select([werknemers, accounts, lonen]).where(and_(werknemers.c.accountID == accounts.c.accountID, werknemers.c.loonID == lonen.c.loonID, lonen.c.tabelloon > int(zoekterm))).\ order_by(lonen.c.tabelloon) elif keuze == 7 and validZt.zt(zoekterm, 14): selwerkn = select([werknemers, accounts, lonen]).where(and_(werknemers.c.accountID == accounts.c.accountID, werknemers.c.loonID == lonen.c.loonID, lonen.c.maandloon > int(zoekterm))).\ order_by(lonen.c.tabelloon) elif keuze == 8 and validZt.zt(zoekterm, 10): selwerkn = select([werknemers, accounts, lonen]).where(and_(werknemers.c.accountID == accounts.c.accountID, werknemers.c.loonID == lonen.c.loonID, werknemers.c.indienst.\ like(zoekterm+'%'))).order_by(werknemers.c.indienst) else: ongInvoer() accKeuze(m_email) if conn.execute(selwerkn).fetchone(): rpwerkn = conn.execute(selwerkn) else: geenRecord() accKeuze(m_email) class MyWindow(QDialog): def __init__(self, data_list, header, *args): QWidget.__init__(self, *args,) self.setGeometry(100, 50, 1600, 900) self.setWindowTitle('Werknemergegevens opvragen') self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setWindowFlags(self.windowFlags()| Qt.WindowSystemMenuHint | Qt.WindowMinMaxButtonsHint) table_model = MyTableModel(self, data_list, header) table_view = QTableView() table_view.setModel(table_model) font = QFont("Arial", 10) table_view.setFont(font) table_view.resizeColumnsToContents() table_view.setSelectionBehavior(QTableView.SelectRows) table_view.setColumnHidden(16,True) table_view.clicked.connect(showWerknemer) layout = QVBoxLayout(self) layout.addWidget(table_view) self.setLayout(layout) class MyTableModel(QAbstractTableModel): def __init__(self, parent, mylist, header, *args): QAbstractTableModel.__init__(self, parent, *args) self.mylist = mylist self.header = header def rowCount(self, parent): return len(self.mylist) def columnCount(self, parent): return len(self.mylist[0]) def data(self, index, role): if not index.isValid(): return None elif role != Qt.DisplayRole: return None return str(self.mylist[index.row()][index.column()]) def headerData(self, col, orientation, role): if orientation == Qt.Horizontal and role == Qt.DisplayRole: return self.header[col] return None header = ['Accountnummer', 'Werknemernummer', 'Loonschaal', 'Loontrede', 'Loonheffing',\ 'Pensioenpremie', 'Res. Vak.toeslag', 'Werkg. Pensioenpremie', 'Periodieke uitk.',\ 'Overige inhoudingen', 'Overige Vergoedingen','Bedrijfsauto bijtelling',\ 'Reiskostenvergoeding', 'Indienst datum', 'Verlof saldo', 'Extra verlof',\ 'Accountnummer', 'Aanhef', 'Voornaam', 'Tussenvoegsel', 'Achternaam',\ 'Geboortedatun', 'Loonschaal', 'Tabelloon', 'Reisuurloon', 'Maandloon'] data_list=[] for row in rpwerkn: data_list += [(row)] def showWerknemer(idx): maccountnr = idx.data() if idx.column() == 0: engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem') conn = engine.connect() selwrknmr = select([werknemers, accounts, lonen]).where(and_(werknemers.c.accountID\ == maccountnr, werknemers.c.accountID == accounts.c.accountID,\ lonen.c.loonID == werknemers.c.loonID)) rpwrknmr = conn.execute(selwrknmr).first() class Widget(QDialog): def __init__(self, parent=None): super(Widget, self).__init__(parent) self.setWindowTitle("Werknemersgegevens opvragen") self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setFont(QFont('Arial', 10)) self.Accountnummer = QLabel() q2Edit = QLineEdit(str(maccountnr)) q2Edit.setFixedWidth(90) q2Edit.setDisabled(True) q2Edit.setFont(QFont("Arial",10)) self.Loontabelnummer = QLabel() q4Edit = QLineEdit(str(rpwrknmr[2])) q4Edit.setFixedWidth(30) q4Edit.setFont(QFont("Arial",10)) q4Edit.setDisabled(True) self.Loontrede = QLabel() q5Edit = QLineEdit(str(rpwrknmr[3])) q5Edit.setFixedWidth(30) q5Edit.setFont(QFont("Arial",10)) q5Edit.setDisabled(True) self.Reiskostenvergoeding = QLabel() q8Edit = QLineEdit(str(rpwrknmr[12])) q8Edit.setFixedWidth(100) q8Edit.setFont(QFont("Arial",10)) q8Edit.setDisabled(True) self.Auto = QLabel() q18Edit = QLineEdit(str(round(rpwrknmr[11],2))) q18Edit.setFixedWidth(70) q18Edit.setFont(QFont("Arial",10)) q18Edit.setDisabled(True) self.Periodiekeuitkering = QLabel() q12Edit = QLineEdit(str(rpwrknmr[8])) q12Edit.setFixedWidth(100) q12Edit.setFont(QFont("Arial",10)) q12Edit.setDisabled(True) self.Overigeinhoudingen = QLabel() q13Edit = QLineEdit(str(rpwrknmr[9])) q13Edit.setFixedWidth(100) q13Edit.setFont(QFont("Arial",10)) q13Edit.setDisabled(True) self.Overigevergoedingen = QLabel() q19Edit = QLineEdit(str(rpwrknmr[10])) q19Edit.setFixedWidth(100) q19Edit.setFont(QFont("Arial",10)) q19Edit.setDisabled(True) self.Indienst = QLabel() q14Edit = QLineEdit(rpwrknmr[13]) q14Edit.setFixedWidth(100) q14Edit.setFont(QFont("Arial",10)) q14Edit.setDisabled(True) self.Maandloon = QLabel() q15Edit = QLineEdit(str(round(rpwrknmr[25]*(1+(rpwrknmr[3]*3/100)),2))) q15Edit.setDisabled(True) q15Edit.setFixedWidth(100) q15Edit.setFont(QFont("Arial",10)) q15Edit.setDisabled(True) self.Verlofsaldo = QLabel() q16Edit = QLineEdit(str(rpwrknmr[14])) q16Edit.setFixedWidth(100) q16Edit.setFont(QFont("Arial",10)) q16Edit.setDisabled(True) self.ExtraVerlof = QLabel() q17Edit = QLineEdit(str(rpwrknmr[15])) q17Edit.setFixedWidth(100) q17Edit.setFont(QFont("Arial",10)) q17Edit.setDisabled(True) grid = QGridLayout() grid.setSpacing(20) lbl = QLabel() pixmap = QPixmap('./images/logos/verbinding.jpg') lbl.setPixmap(pixmap) grid.addWidget(lbl ,1 , 0) logo = QLabel() pixmap = QPixmap('./images/logos/logo.jpg') logo.setPixmap(pixmap) grid.addWidget(logo , 1, 3, 1, 1, Qt.AlignRight) self.setFont(QFont('Arial', 10)) grid.addWidget(QLabel('Opvragen werknemergegevens van\n'+rpwrknmr[18]+\ ' '+rpwrknmr[19]+' '+rpwrknmr[20]+'\nGeboren: '+rpwrknmr[21]), 1, 1, 1, 3) grid.addWidget(QLabel('Bruto maandloon'), 3, 2) grid.addWidget(q15Edit, 3, 3) grid.addWidget(QLabel('Accountnummer'), 3, 0) grid.addWidget(q2Edit, 3, 1) grid.addWidget(QLabel('Loontabel'), 6, 0) grid.addWidget(q4Edit, 6 , 1) grid.addWidget(QLabel('Loontrede'), 7, 0) grid.addWidget(q5Edit, 7, 1) grid.addWidget(QLabel('Reiskostenvergoeding'), 4, 2) grid.addWidget(q8Edit, 4, 3) grid.addWidget(QLabel('Periodieke uitkering belast'), 5, 0) grid.addWidget(q12Edit, 5, 1) grid.addWidget(QLabel('Overige inhoudingen onbelast'), 5, 2) grid.addWidget(q13Edit, 5, 3) grid.addWidget(QLabel('Bijtelling Bedrijfsauto'), 4, 0) grid.addWidget(q18Edit, 4, 1) grid.addWidget(QLabel('Overige Vergoedingen\nonbelast'), 6, 2) grid.addWidget(q19Edit, 6, 3) grid.addWidget(QLabel('Datum indiensttreding'), 8, 0) grid.addWidget(q14Edit, 8, 1) grid.addWidget(QLabel('Verlofsaldo in uren'), 7, 2) grid.addWidget(q16Edit, 7, 3) grid.addWidget(QLabel('Extra verlof in uren'), 8, 2) grid.addWidget(q17Edit, 8, 3) grid.addWidget(QLabel('Uurloon'), 9, 0) grid.addWidget(QLabel(str(round(rpwrknmr[23], 2))), 9, 1, 1, 1, Qt.AlignRight) grid.addWidget(QLabel('Reisuurloon'), 9, 2) grid.addWidget(QLabel(str(round(rpwrknmr[24], 2))), 9, 3) grid.addWidget(QLabel('Pensioenpremie'), 10, 0) grid.addWidget(QLabel(str(round(rpwrknmr[5], 2))), 10, 1, 1, 1, Qt.AlignRight) grid.addWidget(QLabel('Reservering vakantietoeslag'), 10, 2) grid.addWidget(QLabel(str(round(rpwrknmr[6], 2))), 10, 3) grid.addWidget(QLabel('Werkgever pensioenpremie'), 11, 0) grid.addWidget(QLabel(str(round(rpwrknmr[7], 2))), 11, 1, 1, 1, Qt.AlignRight) grid.addWidget(QLabel('Loonheffing'), 11, 2) grid.addWidget(QLabel(str(round(rpwrknmr[4], 2))), 11, 3) grid.addWidget(QLabel('\u00A9 2017 all rights reserved [email protected]'), 13, 0, 1, 4, Qt.AlignCenter) self.setLayout(grid) self.setGeometry(500, 200, 350, 300) cancelBtn = QPushButton('Sluiten') cancelBtn.clicked.connect(self.close) grid.addWidget(cancelBtn, 12, 3) cancelBtn.setFont(QFont("Arial",10)) cancelBtn.setFixedWidth(100) cancelBtn.setStyleSheet("color: black; background-color: gainsboro") window = Widget() window.exec_() win = MyWindow(data_list, header) win.exec_() accKeuze(m_email)
def zoekWerk(m_email): class Widget(QDialog): def __init__(self, parent=None): super(Widget, self).__init__(parent) self.setWindowTitle("Werken Wijzigen.") self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setFont(QFont('Arial', 10)) self.Werknummer = QLabel() werknEdit = QLineEdit() werknEdit.setFixedWidth(100) werknEdit.setFont(QFont("Arial", 10)) werknEdit.textChanged.connect(self.werknChanged) reg_ex = QRegExp('^[8]{1}[0-9]{8}$') input_validator = QRegExpValidator(reg_ex, werknEdit) werknEdit.setValidator(input_validator) grid = QGridLayout() grid.setSpacing(20) lbl = QLabel() pixmap = QPixmap('./images/logos/verbinding.jpg') lbl.setPixmap(pixmap) grid.addWidget(lbl, 0, 0, 1, 2) logo = QLabel() pixmap = QPixmap('./images/logos/logo.jpg') logo.setPixmap(pixmap) grid.addWidget(logo, 0, 1, 1, 1, Qt.AlignRight) self.setFont(QFont('Arial', 10)) grid.addWidget(QLabel('Werknummer'), 1, 0, 1, 1, Qt.AlignRight) grid.addWidget(werknEdit, 1, 1) applyBtn = QPushButton('Zoeken') applyBtn.clicked.connect(self.accept) grid.addWidget(applyBtn, 2, 1, 1, 1, Qt.AlignRight) applyBtn.setFont(QFont("Arial", 10)) applyBtn.setFixedWidth(100) applyBtn.setStyleSheet( "color: black; background-color: gainsboro") cancelBtn = QPushButton('Sluiten') cancelBtn.clicked.connect(lambda: windowSluit(self, m_email)) grid.addWidget(cancelBtn, 2, 0, 1, 1, Qt.AlignRight) cancelBtn.setFont(QFont("Arial", 10)) cancelBtn.setFixedWidth(100) cancelBtn.setStyleSheet( "color: black; background-color: gainsboro") grid.addWidget( QLabel('\u00A9 2017 all rights reserved [email protected]'), 3, 0, 1, 2, Qt.AlignCenter) self.setLayout(grid) self.setGeometry(300, 300, 150, 150) def werknChanged(self, text): self.Werknummer.setText(text) def returnwerkn(self): return self.Werknummer.text() @staticmethod def getData(parent=None): dialog = Widget(parent) dialog.exec_() return [dialog.returnwerkn()] window = Widget() data = window.getData() import validZt if validZt.zt(data[0], 8): mwerknr = int(data[0]) else: foutWerknr() zoekWerk(m_email) metadata = MetaData() werken = Table('werken', metadata, Column('werknummerID', Integer(), primary_key=True), Column('voortgangstatus', String)) engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem') conn = engine.connect() s = select([werken]).where(werken.c.werknummerID == mwerknr) rp = conn.execute(s).first() if not rp: foutWerknr() zoekWerk(m_email) elif rp[1] == 'H': werkGereed() zoekWerk(m_email) else: wijzWerk(mwerknr, m_email)
def toonLeveranciers(keuze, zoekterm, m_email): import validZt metadata = MetaData() leveranciers = Table('leveranciers', metadata, Column('leverancierID', Integer(), primary_key=True), Column('bedrijfsnaam', String), Column('rechtsvorm', String), Column('btwnummer', String), Column('kvknummer', String), Column('telnr', String), Column('postcode', String), Column('huisnummer', String), Column('toevoeging', String)) engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem') con = engine.connect() if keuze == 1: sel = select([leveranciers ]).order_by(desc(leveranciers.c.leverancierID)) elif keuze == 2: sel = select([leveranciers]).where( leveranciers.c.bedrijfsnaam.ilike('%' + zoekterm + '%')) elif keuze == 3 and validZt.zt(zoekterm, 3): zoekterm = int(zoekterm) sel = select([leveranciers ]).where(leveranciers.c.leverancierID == zoekterm) else: ongInvoer() leveranciersKeuze(m_email) if con.execute(sel).fetchone(): rplev = con.execute(sel) else: geenRecord() leveranciersKeuze(m_email) class MyWindow(QDialog): def __init__(self, data_list, header, *args): QWidget.__init__( self, *args, ) self.setGeometry(50, 50, 1500, 900) self.setWindowTitle('Leveranciers opvragen') self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setWindowFlags(self.windowFlags() | Qt.WindowSystemMenuHint | Qt.WindowMinMaxButtonsHint) table_model = MyTableModel(self, data_list, header) table_view = QTableView() table_view.setModel(table_model) font = QFont("Arial", 10) table_view.setFont(font) table_view.resizeColumnsToContents() table_view.setSelectionBehavior(QTableView.SelectRows) #table_view.clicked.connect(showLeverancier) layout = QVBoxLayout(self) layout.addWidget(table_view) self.setLayout(layout) class MyTableModel(QAbstractTableModel): def __init__(self, parent, mylist, header, *args): QAbstractTableModel.__init__(self, parent, *args) self.mylist = mylist self.header = header def rowCount(self, parent): return len(self.mylist) def columnCount(self, parent): return len(self.mylist[0]) def data(self, index, role): veld = self.mylist[index.row()][index.column()] if not index.isValid(): return None elif role == Qt.TextAlignmentRole and (type(veld) == float or type(veld) == int): return Qt.AlignRight | Qt.AlignVCenter elif role != Qt.DisplayRole: return None if type(veld) == float: return '{:12.2f}'.format(veld) else: return veld def headerData(self, col, orientation, role): if orientation == Qt.Horizontal and role == Qt.DisplayRole: return self.header[col] return None header = ['Leveranciernummer','Bedrijfsnaam', 'Rechtsvorm','BTWnummer',\ 'KvKnummer','Telefoonnummer','Straat', 'Huisnummer','Toevoeging',\ 'Postcode','Woonplaats'] data_list = [] for row in rplev: mstrtplts = checkpostcode(row[6], int(row[7])) data_list += [(row[0],row[1],row[2],row[3],row[4],row[5],mstrtplts[0],int(row[7]),\ row[8],row[6], mstrtplts[1])] win = MyWindow(data_list, header) win.exec_() leveranciersKeuze(m_email)
def bestelOrder(m_email, keuze, zoekterm, afd): class MyWindow(QDialog): def __init__(self, data_list, header, *args): QWidget.__init__( self, *args, ) self.setWindowTitle('Webverkooporders') self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setWindowFlags(self.windowFlags() | Qt.WindowSystemMenuHint | Qt.WindowMinMaxButtonsHint) self.setFont(QFont('Arial', 10)) grid = QGridLayout() grid.setSpacing(20) table_model = MyTableModel(self, data_list, header) table_view = QTableView() table_view.setModel(table_model) font = QFont("Arial", 10) table_view.setFont(font) table_view.resizeColumnsToContents() table_view.setSelectionBehavior(QTableView.SelectRows) table_view.setColumnHidden(1, True) table_view.setColumnHidden(6, True) table_view.setColumnHidden(9, True) table_view.clicked.connect(showSelorder) grid.addWidget(table_view, 0, 0, 1, 13) lbl = QLabel() pixmap = QPixmap('./images/logos/verbinding.jpg') lbl.setPixmap(pixmap) grid.addWidget(lbl, 1, 0, 1, 2) logo = QLabel() pixmap = QPixmap('./images/logos/logo.jpg') logo.setPixmap(pixmap) grid.addWidget(logo, 1, 12, 1, 1, Qt.AlignRight) freshBtn = QPushButton('Verversen') freshBtn.clicked.connect( lambda: refresh(m_email, keuze, zoekterm, afd, self)) freshBtn.setFont(QFont("Arial", 10)) freshBtn.setFixedWidth(100) freshBtn.setStyleSheet( "color: black; background-color: gainsboro") grid.addWidget(freshBtn, 1, 11, 1, 1, Qt.AlignRight) sluitBtn = QPushButton('Sluiten') sluitBtn.clicked.connect(self.close) sluitBtn.setFont(QFont("Arial", 10)) sluitBtn.setFixedWidth(100) sluitBtn.setStyleSheet( "color: black; background-color: gainsboro") grid.addWidget(sluitBtn, 1, 10) grid.addWidget( QLabel('\u00A9 2017 all rights reserved [email protected]'), 1, 0, 1, 13, Qt.AlignCenter) self.setLayout(grid) self.setGeometry(50, 50, 1500, 900) class MyTableModel(QAbstractTableModel): def __init__(self, parent, mylist, header, *args): QAbstractTableModel.__init__(self, parent, *args) self.mylist = mylist self.header = header def rowCount(self, parent): return len(self.mylist) def columnCount(self, parent): return len(self.mylist[0]) def data(self, index, role): if not index.isValid(): return None elif role != Qt.DisplayRole: return None return str(self.mylist[index.row()][index.column()]) def headerData(self, col, orientation, role): if orientation == Qt.Horizontal and role == Qt.DisplayRole: return self.header[col] return None header = ['Ordernr','-','Besteldatum', 'Betaald','Geleverd','Totaalbedrag','-', 'Accountnr',\ 'Rekeningnr','-','Aanhef', 'Voornaam','Tussen', 'Achternaam',\ 'Postcode', 'Huisnummer', 'Toev','e-mailadres','Telefoon'] metadata = MetaData() orders_verkoop = Table( 'orders_verkoop', metadata, Column('ovbestelID', Integer, primary_key=True), Column('klantID', None, ForeignKey('klanten.klantID')), Column('ovbesteldatum', String), Column('datum_betaald', String), Column('datum_geleverd', String), Column('bedrag', Float)) klanten = Table( 'klanten', metadata, Column('klantID', Integer(), primary_key=True), Column('accountID', None, ForeignKey('accounts.accountID')), Column('rekening', String)) accounts = Table('accounts', metadata, Column('accountID', Integer(), primary_key=True), Column('aanhef', String), Column('voornaam', String), Column('tussenvoegsel', String), Column('achternaam', String), Column('postcode', String), Column('huisnummer', String), Column('toevoeging', String), Column('email', String), Column('telnr', String)) engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem') con = engine.connect() import validZt if keuze == 1: selwo = select([orders_verkoop, klanten, accounts]).where(and_(orders_verkoop\ .c.klantID == klanten.c.klantID, klanten.c.accountID == accounts\ .c.accountID)).order_by(orders_verkoop.c.ovbestelID) elif keuze == 2 and validZt.zt(zoekterm, 9): selwo = select([orders_verkoop, klanten, accounts]).where(and_(orders_verkoop\ .c.klantID == klanten.c.klantID, klanten.c.accountID == accounts\ .c.accountID, accounts.c.postcode.ilike(zoekterm+'%')))\ .order_by(orders_verkoop.c.ovbestelID) elif keuze == 3: selwo = select([orders_verkoop, klanten, accounts]).where(and_(orders_verkoop\ .c.klantID == klanten.c.klantID, klanten.c.accountID == accounts\ .c.accountID, accounts.c.achternaam.ilike('%'+zoekterm+'%')))\ .order_by(orders_verkoop.c.ovbestelID) elif keuze == 4 and validZt.zt(zoekterm, 5): selwo = select([orders_verkoop, klanten, accounts]).where(and_(orders_verkoop\ .c.klantID == klanten.c.klantID, klanten.c.accountID == accounts\ .c.accountID, orders_verkoop.c.ovbestelID == int(zoekterm)))\ .order_by(orders_verkoop.c.ovbestelID) elif keuze == 5 and validZt.zt(zoekterm, 10): selwo = select([orders_verkoop, klanten, accounts]).where(and_(orders_verkoop\ .c.klantID == klanten.c.klantID, klanten.c.accountID == accounts\ .c.accountID, orders_verkoop.c.ovbesteldatum.like(zoekterm+'%')))\ .order_by(orders_verkoop.c.ovbestelID) else: ongInvoer() zoekWeborder(m_email, afd) if con.execute(selwo).fetchone(): rpwo = con.execute(selwo) else: geenRecord() zoekWeborder(m_email, afd) data_list = [] for row in rpwo: data_list += [(row)] def showSelorder(idx): movbestnr = idx.data() if idx.column() == 0: metadata = MetaData() orders_verkoop = Table( 'orders_verkoop', metadata, Column('ovbestelID', Integer, primary_key=True), Column('klantID', None, ForeignKey('klanten.klantID')), Column('ovbesteldatum', String), Column('bedrag', Float), Column('datum_betaald', String), Column('datum_geleverd', String)) klanten = Table( 'klanten', metadata, Column('klantID', Integer(), primary_key=True), Column('accountID', None, ForeignKey('accounts.accountID')), Column('rekening', String)) accounts = Table('accounts', metadata, Column('accountID', Integer(), primary_key=True), Column('aanhef', String), Column('voornaam', String), Column('tussenvoegsel', String), Column('achternaam', String), Column('postcode', String), Column('huisnummer', String), Column('toevoeging', String), Column('email', String), Column('telnr', String)) orders_verkoop_artikelen = Table( 'orders_verkoop_artikelen', metadata, Column('ovaID', Integer, primary_key=True), Column('ovbestelID', None, ForeignKey('orders_verkoop.ovbestelID')), Column('artikelID', None, ForeignKey('artikelen.artikelID')), Column('ovaantal', Integer), Column('ovleverdatum', String), Column('verkoopprijs', Float), Column('regel', Integer), Column('retour', Float), Column('betaaldatum', String), Column('leveringsdatum', String)) artikelen = Table('artikelen', metadata, Column('artikelID', Integer(), primary_key=True), Column('artikelomschrijving', String), Column('artikelprijs', Float), Column('art_voorraad', Float), Column('art_eenheid', String), Column('locatie_magazijn', String)) engine = create_engine( 'postgresql+psycopg2://postgres@localhost/bisystem') con = engine.connect() selord = select([orders_verkoop, klanten, accounts]).where(and_(orders_verkoop\ .c.ovbestelID == movbestnr, orders_verkoop.c.klantID == klanten.c.klantID,\ klanten.c.accountID == accounts.c.accountID)) rpord = con.execute(selord).first() selova = select([orders_verkoop_artikelen,artikelen]).where(and_(\ orders_verkoop_artikelen.c.ovbestelID == movbestnr, orders_verkoop_artikelen\ .c.artikelID == artikelen.c.artikelID)).order_by(orders_verkoop_artikelen.c.ovaID) rpova = con.execute(selova) mpostcode = rpord[14] mhuisnr = int(rpord[15]) madres = checkpostcode(mpostcode, mhuisnr) mstraat = madres[0] mplaats = madres[1] class MainWindow(QDialog): def __init__(self): QDialog.__init__(self) grid = QGridLayout() grid.setSpacing(20) self.setWindowTitle("Bestelling Webartikelen") self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setFont(QFont('Arial', 10)) self.lbl = QLabel() self.pixmap = QPixmap('./images/logos/verbinding.jpg') self.lbl.setPixmap(self.pixmap) grid.addWidget(self.lbl, 0, 0) grid.addWidget(QLabel('Bestelling Webartikelen'), 0, 1, 1, 2) self.logo = QLabel() self.pixmap = QPixmap('./images/logos/logo.jpg') self.logo.setPixmap(self.pixmap) grid.addWidget(self.logo, 0, 4, 1, 1, Qt.AlignRight) lbl1 = QLabel('Ordernummer') q1Edit = QLineEdit(str(rpord[0])) q1Edit.setFixedWidth(90) q1Edit.setFont(QFont("Arial", 10)) q1Edit.setDisabled(True) grid.addWidget(lbl1, 1, 0) grid.addWidget(q1Edit, 1, 1) lbl2 = QLabel('Bestel- / Leveringsdatum') q2Edit = QLineEdit(str(rpord[2])) q2Edit.setFixedWidth(100) q2Edit.setFont(QFont("Arial", 10)) q2Edit.setDisabled(True) q17Edit = QLineEdit(str(rpord[5])) q17Edit.setFixedWidth(90) q17Edit.setFont(QFont("Arial", 10)) q17Edit.setDisabled(True) grid.addWidget(lbl2, 1, 2) grid.addWidget(q2Edit, 1, 3) grid.addWidget(q17Edit, 1, 4, 1, 1, Qt.AlignRight) lbl3 = QLabel('Totaalbedrag') q3Edit = QLineEdit(str(round(rpord[3], 2))) q3Edit.setFixedWidth(100) q3Edit.setFont(QFont("Arial", 10)) q3Edit.setDisabled(True) grid.addWidget(lbl3, 2, 0) grid.addWidget(q3Edit, 2, 1) lbl4 = QLabel('Betaaldatum') q4Edit = QLineEdit(rpord[4]) q4Edit.setFixedWidth(100) q4Edit.setFont(QFont("Arial", 10)) q4Edit.setDisabled(True) grid.addWidget(lbl4, 2, 2) grid.addWidget(q4Edit, 2, 3, 1, 2) lbl5 = QLabel('Accountnummer') q5Edit = QLineEdit(str(rpord[7])) q5Edit.setFixedWidth(100) q5Edit.setFont(QFont("Arial", 10)) q5Edit.setDisabled(True) grid.addWidget(lbl5, 3, 0) grid.addWidget(q5Edit, 3, 1) lbl6 = QLabel('Rekeningnummer') q6Edit = QLineEdit(str(rpord[8])) q6Edit.setFixedWidth(200) q6Edit.setFont(QFont("Arial", 10)) q6Edit.setDisabled(True) grid.addWidget(lbl6, 3, 2) grid.addWidget(q6Edit, 3, 3, 1, 2) lbl7 = QLabel('Aanhef') q7Edit = QLineEdit(str(rpord[10])) q7Edit.setFixedWidth(100) q7Edit.setFont(QFont("Arial", 10)) q7Edit.setDisabled(True) grid.addWidget(lbl7, 4, 0) grid.addWidget(q7Edit, 4, 1) lbl8 = QLabel('Voornaam') q8Edit = QLineEdit(str(rpord[11])) q8Edit.setFixedWidth(230) q8Edit.setFont(QFont("Arial", 10)) q8Edit.setDisabled(True) grid.addWidget(lbl8, 4, 2) grid.addWidget(q8Edit, 4, 3, 1, 2) lbl9 = QLabel('Tussen') q9Edit = QLineEdit(str(rpord[12])) q9Edit.setFixedWidth(100) q9Edit.setFont(QFont("Arial", 10)) q9Edit.setDisabled(True) grid.addWidget(lbl9, 5, 0) grid.addWidget(q9Edit, 5, 1) lbl10 = QLabel('Achternaam') q10Edit = QLineEdit(str(rpord[13])) q10Edit.setFixedWidth(230) q10Edit.setFont(QFont("Arial", 10)) q10Edit.setDisabled(True) grid.addWidget(lbl10, 5, 2) grid.addWidget(q10Edit, 5, 3, 1, 2) lbl11 = QLabel('Straatnaam') q11Edit = QLineEdit(mstraat) q11Edit.setFixedWidth(260) q11Edit.setFont(QFont("Arial", 10)) q11Edit.setDisabled(True) grid.addWidget(lbl11, 6, 0) grid.addWidget(q11Edit, 6, 1, 1, 3) lbl12 = QLabel('Huisnummer') q12Edit = QLineEdit(str(rpord[15]) + rpord[16]) q12Edit.setFixedWidth(100) q12Edit.setFont(QFont("Arial", 10)) q12Edit.setDisabled(True) grid.addWidget(lbl12, 6, 3) grid.addWidget(q12Edit, 6, 4) lbl13 = QLabel('Postcode') q13Edit = QLineEdit(rpord[14]) q13Edit.setFixedWidth(70) q13Edit.setFont(QFont("Arial", 10)) q13Edit.setDisabled(True) grid.addWidget(lbl13, 7, 0) grid.addWidget(q13Edit, 7, 1) lbl14 = QLabel('Woonplaats') q14Edit = QLineEdit(str(mplaats)) q14Edit.setFixedWidth(230) q14Edit.setFont(QFont("Arial", 10)) q14Edit.setDisabled(True) grid.addWidget(lbl14, 7, 2) grid.addWidget(q14Edit, 7, 3, 1, 2) lbl15 = QLabel('E-Mailadres') q15Edit = QLineEdit(rpord[17]) q15Edit.setFixedWidth(260) q15Edit.setFont(QFont("Arial", 10)) q15Edit.setDisabled(True) grid.addWidget(lbl15, 8, 0) grid.addWidget(q15Edit, 8, 1, 1, 3) lbl16 = QLabel('Telefoon') q16Edit = QLineEdit(str(rpord[18])) q16Edit.setFixedWidth(100) q16Edit.setFont(QFont("Arial", 10)) q16Edit.setDisabled(True) grid.addWidget(lbl16, 8, 3) grid.addWidget(q16Edit, 8, 3, 1, 2, Qt.AlignRight) regelBtn = QPushButton('Bestelregels') regelBtn.clicked.connect(lambda: regelsOrder(rpova, afd)) grid.addWidget(regelBtn, 10, 4) regelBtn.setFont(QFont("Arial", 10)) regelBtn.setFixedWidth(110) regelBtn.setStyleSheet( "color: black; background-color: gainsboro") terugBtn = QPushButton('Sluiten') terugBtn.clicked.connect(self.accept) grid.addWidget(terugBtn, 10, 3, 1, 1, Qt.AlignRight) terugBtn.setFont(QFont("Arial", 10)) terugBtn.setFixedWidth(110) terugBtn.setStyleSheet( "color: black; background-color: gainsboro") pakbonBtn = QPushButton('Print Pakbon') pakbonBtn.clicked.connect(lambda: printPakbon( rpord, mstraat, mplaats, movbestnr)) grid.addWidget(pakbonBtn, 9, 4) pakbonBtn.setFont(QFont("Arial", 10)) pakbonBtn.setFixedWidth(110) factuurBtn = QPushButton('Print Factuur') factuurBtn.clicked.connect(lambda: printFactuur( rpord, mstraat, mplaats, movbestnr)) grid.addWidget(factuurBtn, 9, 3, 1, 1, Qt.AlignRight) factuurBtn.setFont(QFont("Arial", 10)) factuurBtn.setFixedWidth(110) if afd == 1: pakbonBtn.setDisabled(True) factuurBtn.setStyleSheet( "color: black; background-color: gainsboro") elif afd == 0: factuurBtn.setDisabled(True) pakbonBtn.setStyleSheet( "color: black; background-color: gainsboro") elif afd == 2: factuurBtn.setDisabled(True) pakbonBtn.setDisabled(True) grid.addWidget( QLabel( '\u00A9 2017 all rights reserved [email protected]' ), 11, 1, 1, 3, Qt.AlignCenter) self.setLayout(grid) self.setGeometry(300, 150, 150, 150) mainWin = MainWindow() mainWin.exec_() win = MyWindow(data_list, header) win.exec_() zoekWeborder(m_email, afd)
def toonBetalingen(keuze, zoekterm, m_email): import validZt class MyWindow(QDialog): def __init__(self, data_list, header, *args): QWidget.__init__( self, *args, ) self.setGeometry(10, 50, 1900, 900) self.setWindowTitle('Loonbetalingen opvragen') self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setWindowFlags(self.windowFlags() | Qt.WindowSystemMenuHint | Qt.WindowMinMaxButtonsHint) table_model = MyTableModel(self, data_list, header) table_view = QTableView() table_view.setModel(table_model) table_view.resizeColumnsToContents() table_view.setSelectionBehavior(QTableView.SelectRows) font = QFont("Arial", 10) table_view.setFont(font) table_view.hideColumn(3) table_view.hideColumn(36) table_view.hideColumn(37) #table_view.clicked.connect(selectRow) table_view.clicked.connect(showSelection) layout = QVBoxLayout(self) layout.addWidget(table_view) self.setLayout(layout) class MyTableModel(QAbstractTableModel): def __init__(self, parent, mylist, header, *args): QAbstractTableModel.__init__(self, parent, *args) self.mylist = mylist self.header = header def rowCount(self, parent): return len(self.mylist) def columnCount(self, parent): return len(self.mylist[0]) def data(self, index, role): if not index.isValid(): return None elif role != Qt.DisplayRole: return None return str(self.mylist[index.row()][index.column()]) def headerData(self, col, orientation, role): if orientation == Qt.Horizontal and role == Qt.DisplayRole: return self.header[col] return None header = ['Betalingnr', 'Maandperiode', 'Accountnummer', 'Werknemer', 'Voornaam',\ 'Tussenvoegsel','Achternaam', 'Straat', 'Huisnummer', 'Toevoeging',\ 'Postcode', 'Woonplaats', 'Geboortedatum', 'Indienstdatum','Brutoloon',\ 'Bruto variabel', 'Pensioenpremie', 'Bijtelling auto','Loonheffing',\ 'Inhouding overig','Periodieke uitkering','Vergoeding overig',\ 'Vergoeding reiskosten','Res. vakantietoeslag','Werkuren',\ 'Bijzonder tarief', 'Bedrag_byz_tarief','Reisuren', 'Overuren 125%',\ 'Overuren 150%', 'Overuren 200%', 'Nettoloon','Uurloon', 'Reisuurloon',\ 'Uren verlof', 'Uren extra verlof', 'Uren feestdag', 'Uren ziekte',\ 'Uren dokter', 'Uren geoorl. verzuim', 'Uren ongeoorl. verzuim',\ 'Loonschaal', 'Loontrede', 'Verlofsaldo', 'Alg. Heffingskorting',\ 'Arbeidskorting', 'Werkgever pensioenpremie','Werkgever WAO-IVA-WGA premie',\ 'Werkgever AWF premie', 'Werkgever ZVW kosten','Boekdatum', 'Cumulatief verschil uren',\ 'Uren in deze maand', 'Geboekt deze maand'] metadata = MetaData() loonbetalingen = Table( 'loonbetalingen', metadata, Column('betalingID', Integer(), primary_key=True), Column('periode', String), Column('accountID', None, ForeignKey('accounts.accountID')), Column('werknemerID', None, ForeignKey('werknemers.werknemerID')), Column('voornaam', String), Column('tussenvoegsel', String), Column('achternaam', String), Column('straat', String), Column('huisnummer', Integer), Column('toevoeging', String), Column('postcode', String), Column('woonplaats', String), Column('geboortedatum', String), Column('indienst', String), Column('brutoloon', Float), Column('bruto_variabel', Float), Column('pensioenpremie', Float), Column('bijtelling_auto', Float), Column('loonheffing', Float), Column('inhouding_overig', Float), Column('periodieke_uitkering', Float), Column('vergoeding_overig', Float), Column('vergoeding_reiskosten', Float), Column('res_vakantietoeslag', Float), Column('werkuren', Float), Column('byz_tarief', Float), Column('bedrag_byz_tarief', Float), Column('reisuren', Float), Column('overuren_125', Float), Column('overuren_150', Float), Column('overuren_200', Float), Column('nettoloon', Float), Column('uurloon', Float), Column('reisuurloon', Float), Column('uren_verlof', Float), Column('uren_extra_verlof', Float), Column('uren_feestdag', Float), Column('uren_ziek', Float), Column('uren_dokter', Float), Column('uren_geoorloofd_verzuim', Float), Column('uren_ongeoorloofd_verzuim', Float), Column('loonschaal', Integer), Column('loontrede', Integer), Column('verlofsaldo', Float), Column('alg_heffingskorting', Float), Column('arbeidskorting', Float), Column('wg_pensioenpremie', Float), Column('wg_WAO_IVA_WGA', Float), Column('wg_AWF', Float), Column('wg_ZVW', Float), Column('boekdatum', String), Column('saldo_uren_geboekt', Float), Column('maandwerkuren', Float), Column('uren_geboekt', Float)) engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem') con = engine.connect() if keuze == 1: selbet = select([loonbetalingen]).order_by(loonbetalingen.c.periode,\ loonbetalingen.c.accountID) elif keuze == 2 and validZt.zt(zoekterm, 20): selbet = select([loonbetalingen]).where( loonbetalingen.c.periode.like(zoekterm + '%')) elif keuze == 2 and zoekterm[4:7] == 'vak': selbet = select([loonbetalingen]).where( loonbetalingen.c.periode.like(zoekterm + '%')) elif keuze == 3: selbet = select([loonbetalingen]).where( loonbetalingen.c.achternaam.ilike('%' + zoekterm + '%')) else: ongInvoer() zoeken(m_email) if con.execute(selbet).fetchone(): rpbet = con.execute(selbet) else: geenRecord() zoeken(m_email) data_list = [] for row in rpbet: data_list += [(row)] def showSelection(idx): mbetaalnr = idx.data() if idx.column() == 0: engine = create_engine( 'postgresql+psycopg2://postgres@localhost/bisystem') con = engine.connect() selbet = select([loonbetalingen ]).where(loonbetalingen.c.betalingID == mbetaalnr) rpbet = con.execute(selbet).first() header = ['Betalingnr', 'Maandperiode', 'Accountnummer', 'Werknemer', 'Voornaam',\ 'Tussenvoegsel','Achternaam', 'Straat', 'Huisnummer', 'Toevoeging',\ 'Postcode', 'Woonplaats', 'Geboortedatum', 'Indienstdatum','Brutoloon',\ 'Bruto variabel', 'Pensioenpremie', 'Bijtelling auto','Loonheffing',\ 'Inhouding overig','Periodieke uitkering','Vergoeding overig',\ 'Vergoeding reiskosten','Res. vakantietoeslag','Werkuren',\ 'Bijzonder tarief', 'Bedrag_byz_tarief','Reisuren', 'Overuren 125%',\ 'Overuren 150%', 'Overuren 200%', 'Nettoloon','Uurloon', 'Reisuurloon',\ 'Uren verlof', 'Uren extra verlof', 'Uren feestdag', 'Uren ziekte',\ 'Uren dokter', 'Uren geoorl. verzuim', 'Uren ongeoorl. verzuim',\ 'Loonschaal', 'Loontrede', 'Verlofsaldo', 'Alg. Heffingskorting',\ 'Arbeidskorting', 'Werkgever pensioenpremie','Werkgever WAO-IVA-WGA premie',\ 'Werkgever AWF premie', 'Werkgever ZVW kosten','Boekdatum', 'Cumulatief verschil uren',\ 'Uren in deze maand', 'Geboekt deze maand'] class MainWindow(QDialog): def __init__(self): QDialog.__init__(self) grid = QGridLayout() grid.setSpacing(20) self.setWindowTitle("Opvragen loonbetaling") self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setFont(QFont('Arial', 10)) self.lbl = QLabel() self.pixmap = QPixmap('./images/logos/verbinding.jpg') self.lbl.setPixmap(self.pixmap) grid.addWidget(self.lbl, 0, 0) grid.addWidget( QLabel( 'Opvragen Loonbetalingen per werknemer en periode' ), 0, 2, 1, 3) self.logo = QLabel() self.pixmap = QPixmap('./images/logos/logo.jpg') self.logo.setPixmap(self.pixmap) grid.addWidget(self.logo, 0, 5, 1, 1, Qt.AlignRight) index = 3 for item in header: horpos = index % 3 verpos = index if index % 3 == 1: verpos = index - 1 elif index % 3 == 2: verpos = index - 2 self.lbl = QLabel('{:30}'.format(header[index - 3])) self.Gegevens = QLabel() q1Edit = QLineEdit('{:20}'.format(str(rpbet[index - 3]))) q1Edit.setFixedWidth(200) q1Edit.setDisabled(True) grid.addWidget(self.lbl, verpos, horpos + horpos % 3) grid.addWidget(q1Edit, verpos, horpos + horpos % 3 + 1) index += 1 terugBtn = QPushButton('Sluiten') terugBtn.clicked.connect(self.accept) grid.addWidget(terugBtn, verpos + 1, 5, 1, 1, Qt.AlignRight) terugBtn.setFont(QFont("Arial", 10)) terugBtn.setFixedWidth(100) terugBtn.setStyleSheet( "color: black; background-color: gainsboro") grid.addWidget( QLabel( '\u00A9 2017 all rights reserved [email protected]' ), verpos + 1, 2, 1, 2) self.setLayout(grid) self.setGeometry(100, 50, 150, 150) mainWin = MainWindow() mainWin.exec_() win = MyWindow(data_list, header) win.exec_() zoeken(m_email)
def zoeken(m_email): import validZt class Widget(QDialog): def __init__(self, parent=None): super(Widget, self).__init__(parent) self.setWindowTitle("Cluster -> Artikelen bereik") self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setFont(QFont('Arial', 10)) self.Clusternummer = QLabel() clEdit = QLineEdit() font = QFont("Arial", 10) font.setCapitalization(QFont.AllUppercase) clEdit.setFont(font) reg_ex = QRegExp('^[A-Ka-k]{1}[[A-Za-z]{1}[0-9]{5}') input_validator = QRegExpValidator(reg_ex, clEdit) clEdit.setValidator(input_validator) clEdit.textChanged.connect(self.clChanged) self.Keuze = QLabel() k0Edit = QComboBox() k0Edit.setFixedWidth(320) k0Edit.setFont(QFont("Arial", 10)) k0Edit.setStyleSheet("color: black; background-color: #F8F7EE") k0Edit.addItem(' Zoeken Artikelen') k0Edit.addItem('1. Alle Artikelen') k0Edit.addItem('2. Filter op artikelnummer') k0Edit.addItem('3. Filter op artikelomschrijving') k0Edit.activated[str].connect(self.k0Changed) self.Zoekterm = QLabel() zktermEdit = QLineEdit() zktermEdit.setFixedWidth(210) zktermEdit.setFont(QFont("Arial", 10)) reg_ex = QRegExp('^.{0,20}$') input_validator = QRegExpValidator(reg_ex, zktermEdit) zktermEdit.setValidator(input_validator) zktermEdit.textChanged.connect(self.zktermChanged) grid = QGridLayout() grid.setSpacing(20) lbl = QLabel() pixmap = QPixmap('./images/logos/verbinding.jpg') lbl.setPixmap(pixmap) grid.addWidget(lbl, 1, 0, 1, 2) lbl2 = QLabel('Clusternummer') lbl2.setAlignment(Qt.AlignRight | Qt.AlignVCenter) grid.addWidget(lbl2, 2, 0) grid.addWidget(clEdit, 2, 1) grid.addWidget(k0Edit, 3, 0, 1, 2, Qt.AlignRight) lbl1 = QLabel('Zoekterm') lbl1.setAlignment(Qt.AlignRight | Qt.AlignVCenter) grid.addWidget(lbl1, 4, 0) grid.addWidget(zktermEdit, 4, 1) self.setLayout(grid) self.setGeometry(500, 300, 150, 150) grid.addWidget( QLabel('\u00A9 2017 all rights reserved [email protected]'), 6, 0, 1, 3) logo = QLabel() pixmap = QPixmap('./images/logos/logo.jpg') logo.setPixmap(pixmap) grid.addWidget(logo, 1, 1, 1, 1, Qt.AlignRight) applyBtn = QPushButton('Zoeken') applyBtn.clicked.connect(self.accept) grid.addWidget(applyBtn, 5, 1, 1, 1, Qt.AlignRight) applyBtn.setFont(QFont("Arial", 10)) applyBtn.setFixedWidth(100) applyBtn.setStyleSheet( "color: black; background-color: gainsboro") cancelBtn = QPushButton('Sluiten') cancelBtn.clicked.connect(lambda: windowSluit(self, m_email)) grid.addWidget(cancelBtn, 5, 0, 1, 2, Qt.AlignCenter) cancelBtn.setFont(QFont("Arial", 10)) cancelBtn.setFixedWidth(100) cancelBtn.setStyleSheet( "color: black; background-color: gainsboro") def clChanged(self, text): self.Clusternummer.setText(text) def k0Changed(self, text): self.Keuze.setText(text) def zktermChanged(self, text): self.Zoekterm.setText(text) def returnClusternummer(self): return self.Clusternummer.text() def returnKeuze(self): return self.Keuze.text() def returnZoekterm(self): return self.Zoekterm.text() @staticmethod def getData(parent=None): dialog = Widget(parent) dialog.exec_() return [dialog.returnClusternummer(), dialog.returnKeuze(),\ dialog.returnZoekterm()] window = Widget() data = window.getData() zoekterm = '' keuze = '' metadata = MetaData() clusters = Table('clusters', metadata, Column('clusterID', Integer, primary_key=True), Column('omschrijving', String)) engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem') con = engine.connect() selcl = select([clusters ]).where(clusters.c.clusterID == str(data[0]).upper()) rpcl = con.execute(selcl).first() if rpcl: momschr = rpcl[1] else: foutCluster() zoeken(m_email) if data[0]: clusternr = str(data[0]).upper() if not data[1] or data[1][0] == ' ': ongInvoer() zoeken(m_email) if not data[1] or data[1][0] == ' ': ongInvoer() zoeken(m_email) elif data[1][0] == '1': keuze = '1' elif data[1][0] == '2' and validZt.zt(data[2], 2): keuze = '2' zoekterm = data[2] elif data[1][0] == '3' and data[1]: keuze = '3' zoekterm = data[2] else: ongInvoer() zoeken(m_email) toonArtikelen(keuze, zoekterm, m_email, momschr, clusternr)
def toonArtikelen(keuze, zoekterm, m_email): import validZt metadata = MetaData() artikelen = Table('artikelen', metadata, Column('artikelID', Integer(), primary_key=True), Column('barcode', String), Column('artikelomschrijving', String), Column('thumb_artikel', String(70)), Column('artikelprijs', Float), Column('art_voorraad', Float), Column('art_eenheid', String(20)), Column('art_min_voorraad', Float), Column('art_bestelgrootte', Float), Column('locatie_magazijn', String(10)), Column('artikelgroep', String), Column('categorie', Integer), Column('afmeting', String), Column('mutatiedatum', String), Column('bestelsaldo', Float), Column('reserveringsaldo', Float), Column('jaarverbruik_1', Float), Column('jaarverbruik_2', Float)) engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem') con = engine.connect() if keuze == 1: sel = select([artikelen]).order_by(artikelen.c.artikelID) elif keuze == 2: sel = select([artikelen]).order_by(artikelen.c.art_voorraad.desc()) elif keuze == 3: sel = select([artikelen]).where( artikelen.c.artikelomschrijving.ilike('%' + zoekterm + '%')) elif keuze == 4: sel = select([artikelen]).where( artikelen.c.artikelgroep.ilike('%' + zoekterm + '%')) elif keuze == 5: sel = select([artikelen]).where( artikelen.c.locatie_magazijn.ilike('%' + zoekterm + '%')) elif keuze == 6 and validZt.zt(zoekterm, 10): sel = select([artikelen]).where(artikelen.c.mutatiedatum >= (zoekterm+'%')).\ order_by(artikelen.c.artikelID) elif keuze == 7: sel = select([artikelen]).where(and_(artikelen.c.reserveringsaldo > 0,\ artikelen.c.art_voorraad+artikelen.c.bestelsaldo < \ artikelen.c.reserveringsaldo,artikelen.c.categorie > 4)) elif keuze == 8: sel = select([artikelen]).where(and_(artikelen.c.art_voorraad < artikelen.c.art_min_voorraad,\ artikelen.c.categorie < 5)) elif keuze == 9: sel = select([artikelen]).where(and_(artikelen.c.art_min_voorraad+artikelen.c.\ art_bestelgrootte < artikelen.c.reserveringsaldo,artikelen.c.categorie > 4)) else: ongInvoer() artKeuze(m_email) if con.execute(sel).fetchone(): rpartikelen = con.execute(sel) else: geenRecord() artKeuze(m_email) class MyWindow(QDialog): def __init__(self, data_list, header, *args): QWidget.__init__( self, *args, ) self.setGeometry(100, 50, 1800, 900) self.setWindowTitle('Artikelen opvragen') self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setWindowFlags(self.windowFlags() | Qt.WindowSystemMenuHint | Qt.WindowMinMaxButtonsHint) table_model = MyTableModel(self, data_list, header) table_view = QTableView() table_view.setModel(table_model) font = QFont("Arial", 10) table_view.setFont(font) table_view.resizeColumnsToContents() table_view.setSelectionBehavior(QTableView.SelectRows) table_view.setItemDelegateForColumn(3, showImage(self)) table_view.setColumnWidth(3, 100) table_view.verticalHeader().setDefaultSectionSize(75) table_view.clicked.connect(showArtikel) layout = QVBoxLayout(self) layout.addWidget(table_view) self.setLayout(layout) class MyTableModel(QAbstractTableModel): def __init__(self, parent, mylist, header, *args): QAbstractTableModel.__init__(self, parent, *args) self.mylist = mylist self.header = header def rowCount(self, parent): return len(self.mylist) def columnCount(self, parent): return len(self.mylist[0]) def data(self, index, role): veld = self.mylist[index.row()][index.column()] if not index.isValid(): return None #elif index.column() == 9 and role == Qt.DecorationRole: # alternatief picture echter # met tekst rechts van path # return QPixmap(index.data()) elif role == Qt.TextAlignmentRole and (type(veld) == float or type(veld) == int): return Qt.AlignRight | Qt.AlignVCenter elif role != Qt.DisplayRole: return None if type(veld) == float: return '{:12.2f}'.format(veld) else: return veld def headerData(self, col, orientation, role): if orientation == Qt.Horizontal and role == Qt.DisplayRole: return self.header[col] return None class showImage(QStyledItemDelegate): def __init__(self, parent): QStyledItemDelegate.__init__(self, parent) def paint(self, painter, option, index): painter.fillRect(option.rect, QColor(255, 255, 255)) image = QImage(index.data()) pixmap = QPixmap(image) pixmap.scaled(256, 256) return (painter.drawPixmap(option.rect, pixmap)) header = ['Artikelnr', 'Barcodenummer','Omschrijving', 'Afbeelding', 'Prijs', 'Voorraad', 'Eenheid',\ 'MinVrd', 'BestGr', 'Locatie', 'Groep', 'Categorie',\ 'Afmeting', 'Mutatie\ndatum', 'Bestelsaldo' ,'Reservering\nsaldo', \ 'Jaarverbruik\neven jaren','Jaarverbruik\noneven jaren'] data_list = [] for row in rpartikelen: data_list += [(row)] def showArtikel(idx): martikelnr = idx.data() if idx.column() == 0: engine = create_engine( 'postgresql+psycopg2://postgres@localhost/bisystem') con = engine.connect() sel = select([artikelen ]).where(artikelen.c.artikelID == martikelnr) rpartikel = con.execute(sel).first() class Widget(QDialog): def __init__(self): super(Widget, self).__init__() self.setWindowTitle("Artikelen opvragen") self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setFont(QFont('Arial', 10)) self.Artikelnummer = QLabel() q1Edit = QLineEdit(str(rpartikel[0])) q1Edit.setFixedWidth(100) q1Edit.setAlignment(Qt.AlignRight) q1Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q1Edit.setDisabled(True) self.Barcode = QLabel() q1aEdit = QLineEdit(str(rpartikel[1])) q1aEdit.setFixedWidth(130) q1aEdit.setAlignment(Qt.AlignRight) q1aEdit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q1aEdit.setDisabled(True) self.Artikelomschrijving = QLabel() q2Edit = QLineEdit(str(rpartikel[2])) q2Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q2Edit.setFixedWidth(400) q2Edit.setDisabled(True) self.Artikelprijs = QLabel() q3Edit = QLineEdit('{:12.2f}'.format(rpartikel[4])) q3Edit.setAlignment(Qt.AlignRight) q3Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q3Edit.setAlignment(Qt.AlignRight) q3Edit.setFixedWidth(100) q3Edit.setDisabled(True) self.Artikelvoorraad = QLabel() q4Edit = QLineEdit('{:12.2f}'.format(rpartikel[5])) q4Edit.setAlignment(Qt.AlignRight) q4Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q4Edit.setFixedWidth(100) q4Edit.setDisabled(True) self.Bestelsaldo = QLabel() q16Edit = QLineEdit('{:12.2f}'.format(rpartikel[14])) q16Edit.setAlignment(Qt.AlignRight) q16Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q16Edit.setFixedWidth(100) q16Edit.setDisabled(True) self.Artikeleenheid = QLabel() q5Edit = QLineEdit(rpartikel[6]) q5Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q5Edit.setFixedWidth(100) q5Edit.setDisabled(True) self.Minimumvoorraad = QLabel() q6Edit = QLineEdit('{:12.2f}'.format(rpartikel[7])) q6Edit.setAlignment(Qt.AlignRight) q6Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q6Edit.setFixedWidth(100) q6Edit.setDisabled(True) self.Bestelgrootte = QLabel() q7Edit = QLineEdit('{:12.2f}'.format(rpartikel[8])) q7Edit.setAlignment(Qt.AlignRight) q7Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q7Edit.setFixedWidth(100) q7Edit.setDisabled(True) self.Reserveringsaldo = QLabel() q12Edit = QLineEdit('{:12.2f}'.format(rpartikel[15])) q12Edit.setAlignment(Qt.AlignRight) q12Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q12Edit.setFixedWidth(100) q12Edit.setDisabled(True) self.Magazijnlocatie = QLabel() q8Edit = QLineEdit(str(rpartikel[9])) q8Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q8Edit.setFixedWidth(100) q8Edit.setDisabled(True) self.Artikelgroep = QLabel() q9Edit = QLineEdit(str(rpartikel[10])) q9Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q9Edit.setFixedWidth(200) q9Edit.setDisabled(True) self.Artikelthumbnail = QLabel() q11Edit = QLineEdit(str(rpartikel[3])) q11Edit.setFixedWidth(400) q11Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q11Edit.setDisabled(True) self.Categorie = QLabel() q13Edit = QLineEdit(str(rpartikel[11])) q13Edit.setFixedWidth(100) q13Edit.setAlignment(Qt.AlignRight) q13Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q13Edit.setDisabled(True) self.Afmeting = QLabel() q14Edit = QLineEdit(str(rpartikel[12])) q14Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q14Edit.setFixedWidth(100) q14Edit.setDisabled(True) self.Mutatiedatum = QLabel() q15Edit = QLineEdit(str(rpartikel[13])) q15Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q15Edit.setFixedWidth(100) q15Edit.setDisabled(True) grid = QGridLayout() grid.setSpacing(20) lbl = QLabel() pixmap = QPixmap('./images/logos/verbinding.jpg') lbl.setPixmap(pixmap) grid.addWidget(lbl, 0, 0, 1, 2) logo = QLabel() pixmap = QPixmap('./images/logos/logo.jpg') logo.setPixmap(pixmap) grid.addWidget(logo, 0, 2, 1, 3, Qt.AlignRight) grid.addWidget(QLabel('Artikelnummer'), 1, 0, 2, 1) grid.addWidget(q1Edit, 1, 1, 2, 1) grid.addWidget(QLabel('Barcodenummer'), 1, 2, 2, 1) grid.addWidget(q1aEdit, 1, 3, 2, 1) grid.addWidget(QLabel('Artikelomschrijving'), 3, 0) grid.addWidget(q2Edit, 3, 1, 1, 3) grid.addWidget(QLabel('Artikelgroep'), 4, 0) grid.addWidget(q9Edit, 4, 1, 1, 3) grid.addWidget(QLabel('Afmeting'), 5, 0) grid.addWidget(q14Edit, 5, 1) grid.addWidget(QLabel('Eenheid'), 5, 2) grid.addWidget(q5Edit, 5, 3) grid.addWidget(QLabel('Magazijnlocatie'), 6, 0) grid.addWidget(q8Edit, 6, 1) grid.addWidget(QLabel('Artikelprijs'), 6, 2) grid.addWidget(q3Edit, 6, 3) grid.addWidget(QLabel('Artikelvoorraad'), 7, 0) grid.addWidget(q4Edit, 7, 1) grid.addWidget(QLabel('Minimumvoorraad'), 7, 2) grid.addWidget(q6Edit, 7, 3) grid.addWidget(QLabel('Bestelsaldo'), 8, 0) grid.addWidget(q16Edit, 8, 1) grid.addWidget(QLabel('Reserveringsaldo '), 8, 2) grid.addWidget(q12Edit, 8, 3) grid.addWidget(QLabel('Bestelgrootte '), 9, 0) grid.addWidget(q7Edit, 9, 1) grid.addWidget(QLabel('Categorie'), 9, 2) grid.addWidget(q13Edit, 9, 3) grid.addWidget(QLabel('Mutatiedatum'), 10, 0) grid.addWidget(q15Edit, 10, 1) pixmap = QPixmap(rpartikel[9]) lbl2 = QLabel(self) lbl2.setPixmap(pixmap) grid.addWidget(lbl2, 1, 2, 2, 2, Qt.AlignRight) grid.addWidget( QLabel( '\u00A9 2017 all rights reserved [email protected]' ), 12, 0, 1, 4, Qt.AlignCenter) cancelBtn = QPushButton('Sluiten') cancelBtn.clicked.connect(self.close) grid.addWidget(cancelBtn, 11, 3, 1, 1, Qt.AlignRight) cancelBtn.setFont(QFont("Arial", 10)) cancelBtn.setFixedWidth(100) cancelBtn.setStyleSheet( "color: black; background-color: gainsboro") self.setLayout(grid) self.setGeometry(500, 100, 350, 300) win = Widget() win.exec_() win = MyWindow(data_list, header) win.exec_() artKeuze(m_email)
def toonMutaties(keuze, zoekterm, m_email): import validZt metadata = MetaData() artikelmutaties = Table( 'artikelmutaties', metadata, Column('mutatieID', Integer, primary_key=True), Column('artikelID', None, ForeignKey('artikelen.artikelID')), Column('hoeveelheid', Float), Column('boekdatum', String), Column('werknummerID', None, ForeignKey('werken.werknummerID')), Column('orderinkoopID', None, ForeignKey('orders_inkoop.orderinkoopID')), Column('ovbestelID', None, ForeignKey('klanten.klantID')), Column('werkorderID', None, ForeignKey('orders_intern.werkorderID')), Column('tot_mag_prijs', Float), Column('btw_hoog', Float)) artikelen = Table('artikelen', metadata, Column('artikelID', Integer, primary_key=True), Column('artikelomschrijving', String), Column('artikelprijs', Float)) werken = Table('werken', metadata, Column('werknummerID', Integer, primary_key=True), Column('werkomschrijving', String)) orders_inkoop = Table( 'orders_inkoop', metadata, Column('orderinkoopID', Integer, primary_key=True), Column('leverancierID', None, ForeignKey('leveranciers.leverancierID'))) leveranciers = Table('leveranciers', metadata, Column('leverancierID', Integer, primary_key=True), Column('bedrijfsnaam', String)) orders_verkoop = Table('orders_verkoop', metadata, Column('ovbestelID', Integer, primary_key=True)) orders_intern = Table('orders_intern', metadata, Column('werkorderID', Integer, primary_key=True)) engine = create_engine('postgresql://postgres@localhost/bisystem') conn = engine.connect() if keuze == 1: sel = select([artikelmutaties, artikelen.c.artikelomschrijving,artikelen.c\ .artikelprijs]).where(artikelen.c.artikelID==artikelmutaties.c.artikelID) elif keuze == 2: sel = select([artikelmutaties, artikelen.c.artikelomschrijving, artikelen.c\ .artikelprijs,werken.c.werknummerID, werken.c.werkomschrijving])\ .where(and_(artikelen.c.artikelID==artikelmutaties.c.artikelID,\ artikelmutaties.c.werknummerID == werken.c.werknummerID,\ werken.c.werkomschrijving.ilike('%'+zoekterm+'%'))).\ order_by(artikelmutaties.c.werknummerID, artikelmutaties.c.artikelID) elif keuze == 3: sel = select ([artikelmutaties, artikelen.c.artikelomschrijving,artikelen.c\ .artikelprijs, leveranciers.c.leverancierID,leveranciers.c.bedrijfsnaam]).\ where(and_(artikelen.c.artikelID==artikelmutaties.c.artikelID,\ artikelmutaties.c.orderinkoopID==orders_inkoop.c.orderinkoopID,\ orders_inkoop.c.leverancierID == leveranciers.c.leverancierID, leveranciers.c.\ bedrijfsnaam.ilike('%'+zoekterm+'%'))).order_by(leveranciers.c.leverancierID,\ artikelmutaties.c.artikelID) elif keuze == 4: sel = select([artikelmutaties, artikelen.c.artikelomschrijving,artikelen.c\ .artikelprijs]).where(and_(artikelen.c.artikelID==artikelmutaties.c.artikelID,\ artikelmutaties.c.ovbestelID==orders_verkoop.c.ovbestelID)).\ order_by(artikelmutaties.c.ovbestelID, artikelmutaties.c.artikelID) elif keuze == 5: sel = select([artikelmutaties, artikelen.c.artikelomschrijving, artikelen.c\ .artikelprijs])\ .where(and_(artikelen.c.artikelID==artikelmutaties.c.artikelID,\ artikelmutaties.c.werkorderID==orders_intern.c.werkorderID)).\ order_by(artikelmutaties.c.werkorderID, artikelmutaties.c.artikelID) elif keuze == 6 and validZt.zt(zoekterm, 10): sel = select([artikelmutaties, artikelen.c.artikelomschrijving,artikelen.c\ .artikelprijs]).where(and_(artikelen.c.artikelID==artikelmutaties.c.artikelID,\ artikelmutaties.c.boekdatum.like(zoekterm+'%'))).order_by(artikelmutaties.c.boekdatum) else: ongInvoer() mutatieKeuze(m_email) if conn.execute(sel).fetchone(): rp = conn.execute(sel) else: geenRecord() mutatieKeuze(m_email) class MyWindow(QDialog): def __init__(self, data_list, header, *args): QDialog.__init__( self, *args, ) self.setGeometry(50, 50, 1800, 900) self.setWindowTitle('Artikelmutaties opvragen') self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setWindowFlags(self.windowFlags() | Qt.WindowSystemMenuHint | Qt.WindowMinMaxButtonsHint) table_model = MyTableModel(self, data_list, header) table_view = QTableView() table_view.setModel(table_model) font = QFont("Arial", 10) table_view.setFont(font) table_view.resizeColumnsToContents() table_view.setSelectionBehavior(QTableView.SelectRows) #table_view.clicked.connect(showAccount) layout = QVBoxLayout(self) layout.addWidget(table_view) self.setLayout(layout) class MyTableModel(QAbstractTableModel): def __init__(self, parent, mylist, header, *args): QAbstractTableModel.__init__(self, parent, *args) self.mylist = mylist self.header = header def rowCount(self, parent): return len(self.mylist) def columnCount(self, parent): return len(self.mylist[0]) def data(self, index, role): if not index.isValid(): return None elif role != Qt.DisplayRole: return None return str(self.mylist[index.row()][index.column()]) def headerData(self, col, orientation, role): if orientation == Qt.Horizontal and role == Qt.DisplayRole: return self.header[col] return None header = ['Mutatienummer','Artikelnummer', 'Hoeveelheid','Boekdatum', 'Werknummer', 'Orderinkoopnummer',\ 'Bestelordernummer','Internordernummer', 'Totaal magazijnprijs', 'BTW-hoog', 'Artikelomschrijving', 'Artikelprijs'] if keuze == 2: header1 = ['Werknummer', ' Werkomschrijving'] header.extend(header1) elif keuze == 3: header2 = ['Leveranciernummer', 'Bedrijfsnaam'] header.extend(header2) data_list = [] for row in rp: data_list += [(row)] win = MyWindow(data_list, header) win.exec_() mutatieKeuze(m_email)
def updateAccount(m_email): from sqlalchemy import (Table, Column, Integer, String, MetaData,\ create_engine, update) from sqlalchemy.sql import select metadata = MetaData() accounts = Table('accounts', metadata, Column('accountID', Integer(), primary_key=True), Column('aanhef', String(8)), Column('voornaam', String(30), nullable=False), Column('tussenvoegsel', String(10)), Column('achternaam', String(50), nullable=False), Column('postcode', String(6), nullable=False), Column('huisnummer', String(5), nullable=False), Column('toevoeging', String), Column('email', String, nullable=False), Column('password', String, nullable=False), Column('telnr', String(10)), Column('account_count', Integer(), nullable=False), Column('geboortedatum', String)) engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem') conn = engine.connect() sel = select([accounts]).\ where(accounts.c.email == m_email) rpaccount = conn.execute(sel).first() if rpaccount: maccountnr = rpaccount[0] maanhef = rpaccount[1] mvoornaam = rpaccount[2] mtussenv = rpaccount[3] machternaam = rpaccount[4] mpostcode = rpaccount[5] mhuisnr = rpaccount[6] mhuisnr = int(mhuisnr) mtoev = rpaccount[7] m_email = rpaccount[8] mtelnr = rpaccount[10] mcount = rpaccount[11] mcount = int(mcount) + 1 mgebdat = rpaccount[12] mstrtplts = checkpostcode(mpostcode, mhuisnr) mstraat = mstrtplts[0] mplaats = mstrtplts[1] class Widget(QDialog): def __init__(self, parent=None): super(Widget, self).__init__(parent) self.setWindowTitle("Bedrijfs Informatie Systeem") self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setFont(QFont('Arial', 10)) self.Aanhef = QLabel() q2Edit = QComboBox() q2Edit.setFixedWidth(80) q2Edit.setFont(QFont("Arial", 10)) q2Edit.setStyleSheet("color: black; background-color: gainsboro") q2Edit.addItem(' ') q2Edit.addItem('Dhr. ') q2Edit.addItem('Mevr. ') q2Edit.setCurrentIndex(q2Edit.findText(rpaccount[1])) q2Edit.activated[str].connect(self.q2Changed) self.Voornaam = QLabel() q3Edit = QLineEdit(mvoornaam) q3Edit.setFixedWidth(200) q3Edit.setFont(QFont("Arial", 10)) q3Edit.textChanged.connect(self.q3Changed) reg_ex = QRegExp("^[^0-9]{1,30}$") input_validator = QRegExpValidator(reg_ex, q3Edit) q3Edit.setValidator(input_validator) self.Tussenvoegsel = QLabel() q4Edit = QLineEdit('') q4Edit.setFixedWidth(80) q4Edit.setFont(QFont("Arial", 10)) q4Edit.textChanged.connect(self.q4Changed) q4Edit.setText(mtussenv) reg_ex = QRegExp("^[^0-9]{1,10}$") input_validator = QRegExpValidator(reg_ex, q3Edit) q4Edit.setValidator(input_validator) self.Achternaam = QLabel() q5Edit = QLineEdit(machternaam) q5Edit.setFixedWidth(400) q5Edit.setFont(QFont("Arial", 10)) q5Edit.textChanged.connect(self.q5Changed) reg_ex = QRegExp("^[^0-9]{1,50}$") input_validator = QRegExpValidator(reg_ex, q5Edit) q5Edit.setValidator(input_validator) self.Straat = QLabel() q6Edit = QLineEdit(mstraat) q6Edit.setFixedWidth(500) q6Edit.setDisabled(True) q6Edit.setStyleSheet( "QLineEdit { background-color: ; color: black }") q6Edit.setFont(QFont("Arial", 10)) q6Edit.textChanged.connect(self.q6Changed) self.Huisnummer = QLabel() q7Edit = QLineEdit(str(mhuisnr)) q7Edit.setFixedWidth(60) q7Edit.setFont(QFont("Arial", 10)) q7Edit.textChanged.connect(self.q7Changed) reg_ex = QRegExp("^[0-9]{1,5}$") input_validator = QRegExpValidator(reg_ex, q7Edit) q7Edit.setValidator(input_validator) self.Toevoeging = QLabel() q8Edit = QLineEdit('') q8Edit.setFixedWidth(80) q8Edit.setFont(QFont("Arial", 10)) q8Edit.textChanged.connect(self.q8Changed) q8Edit.setText(mtoev) reg_ex = QRegExp("^[A-Za-z0-9-#]{0,10}") input_validator = QRegExpValidator(reg_ex, q8Edit) q8Edit.setValidator(input_validator) self.Postcode = QLabel() q9Edit = QLineEdit(mpostcode) q9Edit.setFixedWidth(70) font = QFont("Arial", 10) font.setCapitalization(QFont.AllUppercase) q9Edit.setFont(font) q9Edit.textChanged.connect(self.q9Changed) reg_ex = QRegExp("^[0-9]{4}[A-Za-z]{2}$") input_validator = QRegExpValidator(reg_ex, q9Edit) q9Edit.setValidator(input_validator) self.Woonplaats = QLabel() q10Edit = QLineEdit(mplaats) q10Edit.setFixedWidth(500) q10Edit.setDisabled(True) q10Edit.setFont(QFont("Arial", 10)) q10Edit.setStyleSheet( "QLineEdit { background-color: ; color: black }") q10Edit.textChanged.connect(self.q10Changed) self.email = QLabel() q11Edit = QLineEdit(m_email) q11Edit.setFixedWidth(300) q11Edit.setFont(QFont("Arial", 10)) q11Edit.textChanged.connect(self.q11Changed) reg_ex = QRegExp( "^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$") input_validator = QRegExpValidator(reg_ex, q11Edit) q11Edit.setValidator(input_validator) self.BestaandWachtwoord = QLabel() q12Edit = QLineEdit() q12Edit.setFixedWidth(300) q12Edit.setFont(QFont("Arial", 10)) q12Edit.setEchoMode(QLineEdit.Password) q12Edit.textChanged.connect(self.q12Changed) self.NieuwWachtwoord = QLabel() q13Edit = QLineEdit() q13Edit.setFixedWidth(300) q13Edit.setFont(QFont("Arial", 10)) q13Edit.setEchoMode(QLineEdit.Password) q13Edit.textChanged.connect(self.q13Changed) self.NieuwWachtwoordControle = QLabel() q14Edit = QLineEdit() q14Edit.setFixedWidth(300) q14Edit.setFont(QFont("Arial", 10)) q14Edit.setEchoMode(QLineEdit.Password) q14Edit.textChanged.connect(self.q14Changed) self.Telefoonnr = QLabel() q15Edit = QLineEdit('') q15Edit.setFixedWidth(100) q15Edit.setFont(QFont("Arial", 10)) q15Edit.textChanged.connect(self.q15Changed) q15Edit.setText(mtelnr) reg_ex = QRegExp("^[#0]{1}[0-9]{9}$") input_validator = QRegExpValidator(reg_ex, q15Edit) q15Edit.setValidator(input_validator) self.Accountnummer = QLabel() q16Edit = QLineEdit(str(maccountnr)) q16Edit.setFixedWidth(100) q16Edit.setDisabled(True) q16Edit.setStyleSheet( "QLineEdit { background-color: ; color: black }") q16Edit.setFont(QFont("Arial", 10)) q16Edit.textChanged.connect(self.q16Changed) self.Geboortedatum = QLabel() q17Edit = QLineEdit(mgebdat) q17Edit.setFixedWidth(100) q17Edit.setFont(QFont("Arial", 10)) q17Edit.textChanged.connect(self.q17Changed) reg_ex = QRegExp( '^[1-2]{1}[09]{1}[0-9]{2}-[0-1]{1}[0-9]{1}-[0-3]{1}[0-9]{1}$') input_validator = QRegExpValidator(reg_ex, q17Edit) q17Edit.setValidator(input_validator) grid = QGridLayout() grid.setSpacing(20) lbl = QLabel() pixmap = QPixmap('./images/logos/verbinding.jpg') lbl.setPixmap(pixmap) grid.addWidget(lbl, 1, 0) logo = QLabel() pixmap = QPixmap('./images/logos/logo.jpg') logo.setPixmap(pixmap) grid.addWidget(logo, 1, 2, 1, 1, Qt.AlignRight) self.setFont(QFont('Arial', 10)) grid.addWidget(QLabel('Opvragen of aanpassen persoongegevens'), 1, 1) grid.addWidget( QLabel(' *'), 2, 0) grid.addWidget(QLabel('Verplichte velden'), 2, 1) grid.addWidget(QLabel('Aanhef'), 3, 0) grid.addWidget(q2Edit, 3, 1) grid.addWidget(QLabel('Voornaam *'), 4, 0) grid.addWidget(q3Edit, 4, 1) grid.addWidget(QLabel('Tussenvoegsel'), 5, 0) grid.addWidget(q4Edit, 5, 1) grid.addWidget(QLabel('Achternaam *'), 6, 0) grid.addWidget(q5Edit, 6, 1, 1, 2) grid.addWidget(QLabel('Geboortedatum *'), 7, 0) grid.addWidget( QLabel(' formaat: jjjj-mm-dd'), 7, 1, 1, 2) grid.addWidget(q17Edit, 7, 1) grid.addWidget(QLabel('Straat'), 8, 0) grid.addWidget(q6Edit, 8, 1, 1, 2) grid.addWidget(QLabel('Huisnummer *'), 9, 0) grid.addWidget(q7Edit, 9, 1) grid.addWidget(QLabel('Toevoeging'), 9, 1, 1, 1, Qt.AlignRight) grid.addWidget(q8Edit, 9, 2) grid.addWidget(QLabel('Postcode *'), 10, 0) grid.addWidget(q9Edit, 10, 1) grid.addWidget(QLabel('Woonplaats'), 11, 0) grid.addWidget(q10Edit, 11, 1, 1, 2) grid.addWidget(QLabel('e-mail *'), 12, 0) grid.addWidget(q11Edit, 12, 1, 1, 2) grid.addWidget(QLabel('Bestaand wachtwoord *'), 13, 0) grid.addWidget(q12Edit, 13, 1, 1, 2) grid.addWidget(QLabel('Nieuw wachtwoord *'), 14, 0) grid.addWidget(q13Edit, 14, 1, 1, 2) grid.addWidget(QLabel('Nieuw wachtwoord controle *'), 15, 0) grid.addWidget(q14Edit, 15, 1, 1, 2) grid.addWidget(QLabel('Telefoonnummer'), 16, 0) grid.addWidget(q15Edit, 16, 1) grid.addWidget(QLabel('Accountnummer'), 17, 0) grid.addWidget(q16Edit, 17, 1, 1, 2) grid.addWidget( QLabel('\u00A9 2017 all rights reserved [email protected]'), 18, 0, 1, 3, Qt.AlignCenter) self.setLayout(grid) self.setGeometry(500, 50, 350, 300) applyBtn = QPushButton('Wijzigen') applyBtn.clicked.connect(self.accept) grid.addWidget(applyBtn, 17, 2, 1, 1, Qt.AlignRight) applyBtn.setFont(QFont("Arial", 10)) applyBtn.setFixedWidth(100) applyBtn.setStyleSheet( "color: black; background-color: gainsboro") cancelBtn = QPushButton('Sluiten') cancelBtn.clicked.connect(lambda: windowSluit(self, m_email)) grid.addWidget(cancelBtn, 16, 2, 1, 1, Qt.AlignRight) cancelBtn.setFont(QFont("Arial", 10)) cancelBtn.setFixedWidth(100) cancelBtn.setStyleSheet( "color: black; background-color: gainsboro") def q2Changed(self, text): self.Aanhef.setText(text) def q3Changed(self, text): self.Voornaam.setText(text) def q4Changed(self, text): self.Tussenvoegsel.setText(text) def q5Changed(self, text): self.Achternaam.setText(text) def q6Changed(self, text): self.Straat.setText(text) def q7Changed(self, text): self.Huisnummer.setText(text) def q8Changed(self, text): self.Toevoeging.setText(text) def q9Changed(self, text): self.Postcode.setText(text) def q10Changed(self, text): self.Woonplaats.setText(text) def q11Changed(self, text): self.email.setText(text) def q12Changed(self, text): self.BestaandWachtwoord.setText(text) def q13Changed(self, text): self.NieuwWachtwoord.setText(text) def q14Changed(self, text): self.NieuwWachtwoordControle.setText(text) def q15Changed(self, text): self.Telefoonnr.setText(text) def q16Changed(self, text): self.Accountnummer.setText(text) def q17Changed(self, text): self.Geboortedatum.setText(text) def returnAanhef(self): return self.Aanhef.text() def returnVoornaam(self): return self.Voornaam.text() def returnTussenvoegsel(self): return self.Tussenvoegsel.text() def returnAchternaam(self): return self.Achternaam.text() def returnStraat(self): return self.Straat.text() def returnHuisnummer(self): return self.Huisnummer.text() def returnToevoeging(self): return self.Toevoeging.text() def returnPostcode(self): return self.Postcode.text() def returnWoonplaats(self): return self.Woonplaats.text() def returnemail(self): return self.email.text() def returnBestaandWachtwoord(self): return self.BestaandWachtwoord.text() def returnNieuwWachtwoord(self): return self.NieuwWachtwoord.text() def returnNieuwWachtwoordControle(self): return self.NieuwWachtwoordControle.text() def returnTelefoonnummer(self): return self.Telefoonnr.text() def returnAccountnummer(self): return self.Accountnummer.text() def returnGeboortedatum(self): return self.Geboortedatum.text() @staticmethod def getData(parent=None): dialog = Widget(parent) dialog.exec_() return [dialog.returnAanhef(), dialog.returnVoornaam(),\ dialog.returnTussenvoegsel(), dialog.returnAchternaam(),\ dialog.returnStraat(), dialog.returnHuisnummer(),\ dialog.returnToevoeging(), dialog.returnPostcode(),\ dialog.returnWoonplaats(), dialog.returnemail(),\ dialog.returnBestaandWachtwoord(), dialog.returnNieuwWachtwoord(),\ dialog.returnNieuwWachtwoordControle(), dialog.returnTelefoonnummer(),\ dialog.returnAccountnummer(),dialog.returnGeboortedatum()] window = Widget() data = window.getData() if data[0]: maanhef = data[0] else: maanhef = rpaccount[1] if data[1]: mvoornaam = (data[1]).title() else: mvoornaam = rpaccount[2].title() if data[2]: mtussenv = data[2] else: mtussenv = '' if data[3]: machternaam = (data[3]).title() else: machternaam = rpaccount[4].title() if data[5]: mhuisnr = data[5] else: mhuisnr = rpaccount[6] if data[7]: mpostcode = data[7].upper() else: mpostcode = rpaccount[5] if checkpostcode(mpostcode, int(mhuisnr))[0] == '': foutPostcode() hoofdMenu(m_email) if data[6]: mtoev = '-' + data[6] else: mtoev = '' if data[9]: if zt(data[9], 12): m_email = (data[9]) else: foutEmail() hoofdMenu(m_email) else: m_email = rpaccount[8] mpassword = rpaccount[9] old_password = '' nw_password = '' contr_nwpass = '' if data[10] and data[11] and data[12]: old_password = data[10] nw_password = data[11] contr_nwpass = data[12] if check_password(mpassword, old_password) and len(nw_password) > 7 \ and (nw_password == contr_nwpass): nw_passwd = hash_password(nw_password) else: foutWachtw() hoofdMenu(m_email) else: nw_passwd = mpassword dontMatch() if data[13]: mtelnr = data[13] else: mtelnr = '' if not (len(mtelnr) == 10 or mtelnr == ''): foutTelnr() hoofdMenu(m_email) if data[15]: mgebdat = data[15] else: mgebdat = rpaccount[12] u = update(accounts).where(accounts.c.accountID == maccountnr).\ values(aanhef = maanhef, voornaam = mvoornaam, tussenvoegsel =\ mtussenv, achternaam = machternaam, huisnummer = int(mhuisnr),\ toevoeging = mtoev, postcode = mpostcode, email = m_email, password =\ nw_passwd, telnr = mtelnr, account_count = mcount, geboortedatum = mgebdat) conn.execute(u) conn.close() updateOK() hoofdMenu(m_email)
def toonReserveringen(keuze, zoekterm, m_email): metadata = MetaData() materiaallijsten = Table( 'materiaallijsten', metadata, Column('matlijstID', Integer, primary_key=True), Column('artikelID', None, ForeignKey('artikelen.artikelID')), Column('werknummerID', Integer), Column('calculatie', Integer), Column('icalculatie', Integer), Column('orderinkoopID', Integer), Column('hoeveelheid', Float), Column('reserverings_datum', String), Column('levertijd_begin', String), Column('levertijd_end', String), Column('categorie', Integer)) artikelen = Table('artikelen', metadata, Column('artikelID', Integer(), primary_key=True), Column('artikelomschrijving', String), Column('artikelprijs', Float), Column('art_voorraad', Float), Column('categorie', String(10)), Column('bestelsaldo', Float), Column('reserveringsaldo', Float)) engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem') con = engine.connect() if keuze == 1: sel = select([materiaallijsten, artikelen]).where(materiaallijsten.c.artikelID == \ artikelen.c.artikelID).order_by(materiaallijsten.c.artikelID) elif keuze == 2: sel = select([materiaallijsten, artikelen]).where(materiaallijsten.c.artikelID ==\ artikelen.c.artikelID).order_by(materiaallijsten.c.reserverings_datum) elif keuze == 3: sel = select([materiaallijsten, artikelen]).where(materiaallijsten.c.artikelID ==\ artikelen.c.artikelID).order_by(materiaallijsten.c.levertijd_begin) elif keuze == 4 and zt(zoekterm, 2): sel = select([materiaallijsten, artikelen]).where(and_(materiaallijsten.c.artikelID ==\ artikelen.c.artikelID, materiaallijsten.c.artikelID == int(zoekterm))) elif keuze == 5 and zt(zoekterm, 21): sel = select([materiaallijsten, artikelen]).where(and_(materiaallijsten.c.artikelID ==\ artikelen.c.artikelID, materiaallijsten.c.werknummerID == int(zoekterm))) elif keuze == 6: sel = select([materiaallijsten, artikelen]).where(and_(materiaallijsten.c.artikelID ==\ artikelen.c.artikelID,materiaallijsten.c.orderinkoopID > 0)).order_by(materiaallijsten.c.artikelID) elif keuze == 7: sel = select([materiaallijsten, artikelen]).where(and_(materiaallijsten.c.artikelID ==\ artikelen.c.artikelID,materiaallijsten.c.orderinkoopID == 0)).order_by(materiaallijsten.c.artikelID) elif keuze == 8: sel = select([materiaallijsten,artikelen]).where(and_(materiaallijsten.c.artikelID ==\ artikelen.c.artikelID, materiaallijsten.c.categorie == 4)).order_by(artikelen.c.artikelID) elif keuze == 9: sel = select([materiaallijsten,artikelen]).where(and_(materiaallijsten.c.artikelID ==\ artikelen.c.artikelID, materiaallijsten.c.categorie == 5)).order_by(artikelen.c.artikelID) else: ongInvoer() resKeuze(m_email) if con.execute(sel).fetchone(): rpres = con.execute(sel) else: geenRecord() resKeuze(m_email) class MyWindow(QDialog): def __init__(self, data_list, header, *args): QDialog.__init__( self, *args, ) self.setGeometry(100, 50, 1600, 900) self.setWindowTitle('Reserveringen opvragen') self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setWindowFlags(self.windowFlags() | Qt.WindowSystemMenuHint | Qt.WindowMinMaxButtonsHint) table_model = MyTableModel(self, data_list, header) table_view = QTableView() table_view.setModel(table_model) font = QFont("Arial", 10) table_view.setFont(font) table_view.resizeColumnsToContents() table_view.setColumnHidden(11, True) table_view.setColumnHidden(15, True) table_view.setSelectionBehavior(QTableView.SelectRows) table_view.clicked.connect(showReservering) layout = QVBoxLayout(self) layout.addWidget(table_view) self.setLayout(layout) class MyTableModel(QAbstractTableModel): def __init__(self, parent, mylist, header, *args): QAbstractTableModel.__init__(self, parent, *args) self.mylist = mylist self.header = header def rowCount(self, parent): return len(self.mylist) def columnCount(self, parent): return len(self.mylist[0]) def data(self, index, role): if not index.isValid(): return None elif role != Qt.DisplayRole: return None return str(self.mylist[index.row()][index.column()]) def headerData(self, col, orientation, role): if orientation == Qt.Horizontal and role == Qt.DisplayRole: return self.header[col] return None header = ['MatlijstID','Artikelnummer','Werknummer','Calculatie','Icalculatie',\ 'Inkoopordernummer','Hoeveelheid','Reserveringdatum','Levertijd begin',\ 'Levertijd einde','Categorie', 'Artikelnummer','Artikelomschrijving',\ 'Artikelprijs','Artikelvoorraad','Categorie','Bestelsaldo','Reserveringsaldo'] data_list = [] for row in rpres: data_list += [(row)] def showReservering(idx): mresnr = idx.data() if idx.column() == 0: engine = create_engine( 'postgresql+psycopg2://postgres@localhost/bisystem') con = engine.connect() sel = select([materiaallijsten, artikelen]).where(and_(materiaallijsten.c.artikelID ==\ artikelen.c.artikelID, materiaallijsten.c.matlijstID == mresnr)) rpres = con.execute(sel).first() class Widget(QDialog): def __init__(self, parent=None): super(Widget, self).__init__(parent) self.setWindowTitle("Reserveringen opvragen") self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setFont(QFont('Arial', 10)) self.ResID = QLabel() q1Edit = QLineEdit(str(rpres[0])) q1Edit.setFixedWidth(100) q1Edit.setDisabled(True) q1Edit.setFont(QFont("Arial", 10)) self.Artikelnummer = QLabel() q2Edit = QLineEdit(str(rpres[1])) q2Edit.setFixedWidth(100) q2Edit.setFont(QFont("Arial", 10)) q2Edit.setDisabled(True) self.Artikelomschrijving = QLabel() q3Edit = QLineEdit(str(rpres[12])) q3Edit.setFixedWidth(400) q3Edit.setFont(QFont("Arial", 10)) q3Edit.setDisabled(True) self.Artikelvoorraad = QLabel() q18Edit = QLineEdit(str(rpres[14])) q18Edit.setFixedWidth(400) q18Edit.setFont(QFont("Arial", 10)) q18Edit.setDisabled(True) self.Werknummer = QLabel() q4Edit = QLineEdit(str(rpres[2])) q4Edit.setFixedWidth(100) q4Edit.setFont(QFont("Arial", 10)) q4Edit.setDisabled(True) self.Calculatie = QLabel() q5Edit = QLineEdit(str(rpres[3])) q5Edit.setFixedWidth(100) q5Edit.setFont(QFont("Arial", 10)) q5Edit.setDisabled(True) self.Icalculatie = QLabel() q10Edit = QLineEdit(str(rpres[4])) q10Edit.setFixedWidth(100) q10Edit.setFont(QFont("Arial", 10)) q10Edit.setDisabled(True) self.Inkooporder = QLabel() q6Edit = QLineEdit(str(rpres[5])) q6Edit.setFixedWidth(100) q6Edit.setFont(QFont("Arial", 10)) q6Edit.setDisabled(True) self.Hoeveelheid = QLabel() q17Edit = QLineEdit(str(rpres[6])) q17Edit.setFixedWidth(100) q17Edit.setFont(QFont("Arial", 10)) q17Edit.setDisabled(True) self.Artikelvoorraad = QLabel() q18Edit = QLineEdit(str(rpres[14])) q18Edit.setFixedWidth(100) q18Edit.setFont(QFont("Arial", 10)) q18Edit.setDisabled(True) self.Reserveringsaldo = QLabel() q7Edit = QLineEdit(str(rpres[17])) q7Edit.setFixedWidth(100) q7Edit.setFont(QFont("Arial", 10)) q7Edit.setDisabled(True) self.Reserveringdatum = QLabel() q8Edit = QLineEdit(str(rpres[7])) q8Edit.setFixedWidth(100) q8Edit.setFont(QFont("Arial", 10)) q8Edit.setDisabled(True) self.Levering_start = QLabel() q9Edit = QLineEdit(str(rpres[8])) q9Edit.setFixedWidth(100) q9Edit.setFont(QFont("Arial", 10)) q9Edit.setDisabled(True) self.Levering_eind = QLabel() q11Edit = QLineEdit(str(rpres[9])) q11Edit.setFixedWidth(100) q11Edit.setFont(QFont("Arial", 10)) q11Edit.setDisabled(True) self.Categorie = QLabel() q13Edit = QLineEdit(str(rpres[10])) q13Edit.setFixedWidth(100) q13Edit.setFont(QFont("Arial", 9)) q13Edit.setDisabled(True) self.Artikelprijs = QLabel() q14Edit = QLineEdit(str(rpres[13])) q14Edit.setFixedWidth(100) q14Edit.setFont(QFont("Arial", 10)) q14Edit.setDisabled(True) self.Bestelsaldo = QLabel() q16Edit = QLineEdit(str(rpres[16])) q16Edit.setFixedWidth(100) q16Edit.setFont(QFont("Arial", 10)) q16Edit.setDisabled(True) grid = QGridLayout() grid.setSpacing(20) lbl = QLabel() pixmap = QPixmap('./images/logos/verbinding.jpg') lbl.setPixmap(pixmap) grid.addWidget(lbl, 0, 0) logo = QLabel() pixmap = QPixmap('./images/logos/logo.jpg') logo.setPixmap(pixmap) grid.addWidget(logo, 0, 3, 1, 1, Qt.AlignRight) grid.addWidget(QLabel('MatlijstID'), 1, 0) grid.addWidget(q1Edit, 1, 1) grid.addWidget(QLabel('Artikelnummer'), 2, 0) grid.addWidget(q2Edit, 2, 1) grid.addWidget(QLabel('Werknummer'), 2, 2) grid.addWidget(q4Edit, 2, 3) grid.addWidget(QLabel('Calculatie'), 3, 0) grid.addWidget(q5Edit, 3, 1) grid.addWidget(QLabel('Icalculatie'), 3, 2) grid.addWidget(q10Edit, 3, 3) grid.addWidget(QLabel('Artikelomschrijving'), 4, 0) grid.addWidget(q3Edit, 4, 1, 1, 3) grid.addWidget(QLabel('Inkoopordernummer'), 5, 0) grid.addWidget(q6Edit, 5, 1) grid.addWidget(QLabel('Hoeveelheid'), 5, 2) grid.addWidget(q17Edit, 5, 3) grid.addWidget(QLabel('Artikelvoorraad'), 6, 0) grid.addWidget(q18Edit, 6, 1) grid.addWidget(QLabel('Reserveringsaldo'), 6, 2) grid.addWidget(q7Edit, 6, 3) grid.addWidget(QLabel('Reserveringsdatum'), 7, 0) grid.addWidget(q8Edit, 7, 1) grid.addWidget(QLabel('Bestelsaldo'), 7, 2) grid.addWidget(q16Edit, 7, 3) grid.addWidget(QLabel('Levering start'), 8, 0) grid.addWidget(q9Edit, 8, 1) grid.addWidget(QLabel('Levering eind'), 8, 2) grid.addWidget(q11Edit, 8, 3) grid.addWidget(QLabel('Categorie'), 9, 0) grid.addWidget(q13Edit, 9, 1) grid.addWidget(QLabel('Artikelprijs'), 9, 2) grid.addWidget(q14Edit, 9, 3) grid.addWidget( QLabel( '\u00A9 2017 all rights reserved [email protected]' ), 11, 0, 1, 4, Qt.AlignCenter) cancelBtn = QPushButton('Sluiten') cancelBtn.clicked.connect(self.close) grid.addWidget(cancelBtn, 10, 3, 1, 1, Qt.AlignRight) cancelBtn.setFont(QFont("Arial", 10)) cancelBtn.setFixedWidth(100) cancelBtn.setStyleSheet( "color: black; background-color: gainsboro") self.setLayout(grid) self.setGeometry(500, 100, 350, 300) window = Widget() window.exec_() win = MyWindow(data_list, header) win.exec_() resKeuze(m_email)
def toonOrders(keuze, zoekterm, m_email): import validZt class MyWindow(QDialog): def __init__(self, data_list, header, *args): QWidget.__init__( self, *args, ) self.setWindowTitle('Wijzigen interne orders') self.setWindowFlags(self.windowFlags() | Qt.WindowSystemMenuHint | Qt.WindowMinMaxButtonsHint) self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setFont(QFont('Arial', 10)) grid = QGridLayout() grid.setSpacing(20) table_model = MyTableModel(self, data_list, header) table_view = QTableView() table_view.setModel(table_model) font = QFont("Arial", 10) table_view.setFont(font) table_view.resizeColumnsToContents() table_view.setSelectionBehavior(QTableView.SelectRows) table_view.clicked.connect(wijzigOrder) grid.addWidget(table_view, 0, 0, 1, 16) lbl = QLabel() pixmap = QPixmap('./images/logos/verbinding.jpg') lbl.setPixmap(pixmap) grid.addWidget(lbl, 1, 0, 1, 2) logo = QLabel() pixmap = QPixmap('./images/logos/logo.jpg') logo.setPixmap(pixmap) grid.addWidget(logo, 1, 15, 1, 1, Qt.AlignRight) freshBtn = QPushButton('Verversen') freshBtn.clicked.connect( lambda: refresh(keuze, zoekterm, m_email, self)) freshBtn.setFont(QFont("Arial", 10)) freshBtn.setFixedWidth(100) freshBtn.setStyleSheet( "color: black; background-color: gainsboro") grid.addWidget(freshBtn, 1, 14, 1, 1, Qt.AlignRight) sluitBtn = QPushButton('Sluiten') sluitBtn.clicked.connect(self.close) sluitBtn.setFont(QFont("Arial", 10)) sluitBtn.setFixedWidth(100) sluitBtn.setStyleSheet( "color: black; background-color: gainsboro") grid.addWidget(sluitBtn, 1, 13, 1, 1, Qt.AlignRight) grid.addWidget( QLabel('\u00A9 2017 all rights reserved [email protected]'), 1, 0, 1, 16, Qt.AlignCenter) self.setLayout(grid) self.setGeometry(50, 50, 1800, 900) self.setLayout(grid) class MyTableModel(QAbstractTableModel): def __init__(self, parent, mylist, header, *args): QAbstractTableModel.__init__(self, parent, *args) self.mylist = mylist self.header = header def rowCount(self, parent): return len(self.mylist) def columnCount(self, parent): return len(self.mylist[0]) def data(self, index, role): veld = self.mylist[index.row()][index.column()] if not index.isValid(): return None elif role == Qt.TextAlignmentRole and (type(veld) == float or type(veld) == int): return Qt.AlignRight | Qt.AlignVCenter elif role != Qt.DisplayRole: return None if type(veld) == float: return '{:12.2f}'.format(veld) else: return veld def headerData(self, col, orientation, role): if orientation == Qt.Horizontal and role == Qt.DisplayRole: return self.header[col] return None header = ['Werkorder', 'Omschrijving','Artikelnummer','Hoeveelheid',\ 'Werkbrief','Besteldatum', 'Voortgangstatus','Statusweek','Boekdatum',\ 'Startdatum', 'Afgemeld', 'Begroot Totaal','Werkelijk Totaal',\ 'Begroot Materialen','Werkelijk Materialen','Begroot Lonen',\ 'Werkelijk Lonen', 'Meerminderwerk', 'Szagen','Bzagen', 'Wzagen',\ 'Sschaven','Bschaven', 'Wschaven', 'Ssteken','Bsteken', 'Wsteken',\ 'Sboren','Bboren', 'Wboren','Sfrezen','Bfrezen','WFrezen',\ 'Sdraaien klein', 'Bdraaien klein','Wdraaien klein','Sdraaien groot',\ 'Bdraaien groot','Wdraaien groot','Stappen','Btappen','Wtappen',\ 'Snube draaien','Bnube draaien','Wnube draaien','Snube bewerken',\ 'Bnube bewerken', 'Wnube bewerken', 'Sknippen','Bknippen', 'Wknippen',\ 'Skanten', 'Bkanten', 'Wkanten','Sstansen','Bstansen', 'Wstansen',\ 'Slassen Co2', 'Blassen Co2', 'Wlassen Co2','Slassen hand','Blassen hand',\ 'Wlassen hand','Sverpakken','Bverpakken','Wverpakken', 'Sverzinken',\ 'Bverzinken','Wverzinken','Smoffelen','Bmoffelen', 'Wmoffelen',\ 'Sschilderen','Bschilderen', 'Wschilderen','Sspuiten','Bspuiten',\ 'Wspuiten','Sponsen','Bponsen', 'Wponsen','Spersen','Bpersen',\ 'Wpersen','Sgritstralen','Bgritstralen','Wgritstralen','Smontage',\ 'Bmontage','Wmontage', 'Wreisuren','Gereed', 'Goedgekeurd'] metadata = MetaData() orders_intern = Table( 'orders_intern', metadata, Column('werkorderID', Integer(), primary_key=True), Column('werkomschrijving', String), Column('artikelID', None, ForeignKey('artikelen.artikelID')), Column('hoeveelheid', Float), Column('werkbrief', String), Column('besteldatum', String), Column('voortgangstatus', String), Column('statusweek', String), Column('boekdatum', String), Column('startdatum', String), Column('afgemeld', String), Column('begroot_totaal', Float), Column('werk_totaal', Float), Column('begr_materialen', Float), Column('werk_materialen', Float), Column('begr_lonen', Float), Column('werk_lonen', Float), Column('meerminderwerk', Float), Column('szagen', Float), Column('bzagen', Float), Column('wzagen', Float), Column('sschaven', Float), Column('bschaven', Float), Column('wschaven', Float), Column('ssteken', Float), Column('bsteken', Float), Column('wsteken', Float), Column('sboren', Float), Column('bboren', Float), Column('wboren', Float), Column('sfrezen', Float), Column('bfrezen', Float), Column('wfrezen', Float), Column('sdraaien_klein', Float), Column('bdraaien_klein', Float), Column('wdraaien_klein', Float), Column('sdraaien_groot', Float), Column('bdraaien_groot', Float), Column('wdraaien_groot', Float), Column('stappen', Float), Column('btappen', Float), Column('wtappen', Float), Column('snube_draaien', Float), Column('bnube_draaien', Float), Column('wnube_draaien', Float), Column('snube_bewerken', Float), Column('bnube_bewerken', Float), Column('wnube_bewerken', Float), Column('sknippen', Float), Column('bknippen', Float), Column('wknippen', Float), Column('skanten', Float), Column('bkanten', Float), Column('wkanten', Float), Column('sstansen', Float), Column('bstansen', Float), Column('wstansen', Float), Column('slassen_co2', Float), Column('blassen_co2', Float), Column('wlassen_co2', Float), Column('slassen_hand', Float), Column('blassen_hand', Float), Column('wlassen_hand', Float), Column('sverpakken', Float), Column('bverpakken', Float), Column('wverpakken', Float), Column('sverzinken', Float), Column('bverzinken', Float), Column('wverzinken', Float), Column('smoffelen', Float), Column('bmoffelen', Float), Column('wmoffelen', Float), Column('sschilderen', Float), Column('bschilderen', Float), Column('wschilderen', Float), Column('sspuiten', Float), Column('bspuiten', Float), Column('wspuiten', Float), Column('sponsen', Float), Column('bponsen', Float), Column('wponsen', Float), Column('spersen', Float), Column('bpersen', Float), Column('wpersen', Float), Column('sgritstralen', Float), Column('bgritstralen', Float), Column('wgritstralen', Float), Column('smontage', Float), Column('bmontage', Float), Column('wmontage', Float), Column('werk_reis_uren', Float), Column('gereed', Float), Column('goedgekeurd', Float)) engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem') con = engine.connect() if keuze == 1: sel = select([orders_intern]).order_by(orders_intern.c.werkorderID) elif keuze == 2 and validZt.zt(zoekterm, 7): sel = select([orders_intern ]).where(orders_intern.c.werkorderID == int(zoekterm)) elif keuze == 3 and validZt.zt(zoekterm, 2): sel = select([orders_intern ]).where(orders_intern.c.artikelID == int(zoekterm)) elif keuze == 4 and validZt.zt(zoekterm, 10): sel = select([orders_intern ]).where(orders_intern.c.startdatum == (zoekterm + '%')) elif keuze == 5 and validZt.zt(zoekterm, 10): sel = select([orders_intern ]).where(orders_intern.c.afgemeld == (zoekterm + '%')) elif keuze == 6 and validZt.zt(zoekterm, 18): sel = select([ orders_intern ]).where(orders_intern.c.voortgangstatus == (zoekterm.upper()[0])) elif keuze == 7: sel = select([orders_intern]).where( orders_intern.c.werkomschrijving.ilike('%' + zoekterm + '%')) else: ongInvoer() zoeken(m_email) if con.execute(sel).fetchone(): rp = con.execute(sel) else: geenRecord() zoeken(m_email) data_list = [] for row in rp: data_list += [(row)] def wijzigOrder(idx): mwerkorder = idx.data() if idx.column() == 0: metadata = MetaData() orders_intern = Table( 'orders_intern', metadata, Column('werkorderID', Integer(), primary_key=True), Column('werkomschrijving', String), Column('besteldatum', String), Column('voortgangstatus', String), Column('statusweek', String), Column('boekdatum', String), Column('startdatum', String), Column('afgemeld', String), Column('begroot_totaal', Float), Column('werk_totaal', Float), Column('begr_materialen', Float), Column('werk_materialen', Float), Column('begr_lonen', Float), Column('werk_lonen', Float), Column('meerminderwerk', Float), Column('werk_reis_uren', Float), Column('artikelID', None, ForeignKey('artikelen.c.artikelID')), Column('hoeveelheid', Float), Column('goedgekeurd', Float), Column('gereed', Float)) artikelen = Table('artikelen', metadata, Column('artikelID', Integer(), primary_key=True), Column('art_voorraad', Float), Column('bestelstatus'), Column('artikelprijs', Float), Column('bestelsaldo', Float)) artikelmutaties = Table( 'artikelmutaties', metadata, Column('mutatieID', Integer, primary_key=True), Column('artikelID', None, ForeignKey('artikelen.artikelID')), Column('orderinkoopID', None, ForeignKey('orders_inkoop.orderinkoopID')), Column('hoeveelheid', Float), Column('boekdatum', String), Column('tot_mag_prijs', Float), Column('btw_hoog', Float), Column('werkorderID', Integer)) engine = create_engine( 'postgresql+psycopg2://postgres@localhost/bisystem') con = engine.connect() selord = select([ orders_intern ]).where(orders_intern.c.werkorderID == mwerkorder) rpord = con.execute(selord).first() class Widget(QDialog): def __init__(self): QDialog.__init__(self) grid = QGridLayout() grid.setSpacing(12) self.setWindowTitle("Wijzigen Werkorders Intern") self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setFont(QFont('Arial', 10)) self.lbl = QLabel() self.pixmap = QPixmap('./images/logos/verbinding.jpg') self.lbl.setPixmap(self.pixmap) grid.addWidget(self.lbl, 0, 0) self.logo = QLabel() self.pixmap = QPixmap('./images/logos/logo.jpg') self.logo.setPixmap(self.pixmap) grid.addWidget(self.logo, 0, 1, 1, 2, Qt.AlignRight) grid.addWidget(QLabel('Wijzigen Orders Intern'), 0, 0, 1, 3, Qt.AlignCenter) self.Werkordernummer = QLabel() q1Edit = QLineEdit(str(rpord[0])) q1Edit.setFixedWidth(100) q1Edit.setAlignment(Qt.AlignRight) q1Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q1Edit.setDisabled(True) q1Edit.setFont(QFont("Arial", 10)) self.Omschrijving = QLabel() q2Edit = QLineEdit(str(rpord[1])) q2Edit.setFixedWidth(400) q2Edit.setFont(QFont("Arial", 10)) q2Edit.textChanged.connect(self.q2Changed) self.Voorgangstatus = QLabel() q3Edit = QLineEdit(str(rpord[3])) q3Edit.setFixedWidth(20) font = QFont("Arial", 10) font.setCapitalization(QFont.AllUppercase) q3Edit.setFont(font) if str(rpord[3]) < 'G': q3Edit.setDisabled(True) q3Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q3Edit.textChanged.connect(self.q3Changed) reg_ex = QRegExp('^[A-Ha-h]{1}$') input_validator = QRegExpValidator(reg_ex, q3Edit) q3Edit.setValidator(input_validator) self.Startdatum = QLabel() q4Edit = QLineEdit(str(rpord[6])) q4Edit.setFixedWidth(100) q4Edit.setFont(QFont("Arial", 10)) q4Edit.textChanged.connect(self.q4Changed) reg_ex = QRegExp( '^[2]{1}[0]{1}[0-9]{2}[-]{1}[0-1]{1}[0-9]{1}[-]{1}[0-3]{1}[0-9]{1}$' ) input_validator = QRegExpValidator(reg_ex, q4Edit) q4Edit.setValidator(input_validator) self.Afgemeld = QLabel() q5Edit = QLineEdit(str(rpord[7])) q5Edit.setFixedWidth(100) q5Edit.setFont(QFont("Arial", 10)) q5Edit.textChanged.connect(self.q5Changed) reg_ex = QRegExp( '^[2]{1}[0]{1}[0-9]{2}[-]{1}[0-1]{1}[0-9]{1}[-]{1}[0-3]{1}[0-9]{1}$' ) input_validator = QRegExpValidator(reg_ex, q5Edit) q5Edit.setValidator(input_validator) self.Gereed = QLabel() q6Edit = QLineEdit(str(round(float(rpord[19]), 2))) q6Edit.setFixedWidth(100) q6Edit.setFont(QFont("Arial", 10)) q6Edit.setAlignment(Qt.AlignRight) q6Edit.textChanged.connect(self.q6Changed) reg_ex = QRegExp("^[-+]?[0-9]*\.?[0-9]+$") input_validator = QRegExpValidator(reg_ex, q6Edit) q6Edit.setValidator(input_validator) self.Goedgekeurd = QLabel() q7Edit = QLineEdit(str(round(float(rpord[18]), 2))) q7Edit.setFixedWidth(100) q7Edit.setFont(QFont("Arial", 10)) q7Edit.setAlignment(Qt.AlignRight) q7Edit.textChanged.connect(self.q7Changed) reg_ex = QRegExp("^[-+]?[0-9]*\.?[0-9]+$") input_validator = QRegExpValidator(reg_ex, q7Edit) q7Edit.setValidator(input_validator) lbl1 = QLabel('Werkordernummer') grid.addWidget(lbl1, 1, 0) grid.addWidget(q1Edit, 1, 1) lbl2 = QLabel('Werkomschrijving') grid.addWidget(lbl2, 2, 0) grid.addWidget(q2Edit, 2, 1, 1, 2) lbl3 = QLabel('Voorgangstatus') grid.addWidget(lbl3, 3, 0) grid.addWidget(q3Edit, 3, 1) lbl4 = QLabel('Statusweek: ' + rpord[4]) grid.addWidget(lbl4, 3, 1, 1, 2, Qt.AlignRight) lbl5 = QLabel('Startdatum') grid.addWidget(lbl5, 4, 0) grid.addWidget(q4Edit, 4, 1) lbl6 = QLabel('Afgemeld') grid.addWidget(lbl6, 5, 0) grid.addWidget(q5Edit, 5, 1) lbl7 = QLabel('Hoeveelheid\nGereed') grid.addWidget(lbl7, 6, 0) grid.addWidget(q6Edit, 6, 1) lbl8 = QLabel(' Artikel: ' + str(rpord[16])) grid.addWidget(lbl8, 5, 1, 1, 2, Qt.AlignRight) lbl9 = QLabel(' Bestelhoeveelheid: ' + '{:12.2f}'.format(rpord[17])) grid.addWidget(lbl9, 6, 1, 1, 2, Qt.AlignRight) lbl10 = QLabel('Hoeveelheid\nGoedgekeurd') grid.addWidget(lbl10, 7, 0) grid.addWidget(q7Edit, 7, 1) wijzig = QPushButton('Wijzig') wijzig.clicked.connect(self.accept) grid.addWidget(wijzig, 9, 2, 1, 1, Qt.AlignRight) wijzig.setFont(QFont("Arial", 10)) wijzig.setFixedWidth(100) sluit = QPushButton('Sluiten') sluit.clicked.connect(self.close) grid.addWidget(sluit, 9, 1, 1, 2, Qt.AlignCenter) sluit.setFont(QFont("Arial", 10)) sluit.setFixedWidth(100) grid.addWidget( QLabel( '\u00A9 2017 all rights reserved [email protected]' ), 10, 0, 1, 3, Qt.AlignCenter) self.setLayout(grid) self.setGeometry(500, 300, 150, 150) def q2Changed(self, text): self.Omschrijving.setText(text) def q3Changed(self, text): self.Voortgangstatus.setText(text) def q4Changed(self, text): self.Startdatum.setText(text) def q5Changed(self, text): self.Afgemeld.setText(text) def q6Changed(self, text): self.Gereed.setText(text) def q7Changed(self, text): self.Goedgekeurd.setText(text) def returnq2(self): return self.Omschrijving.text() def returnq3(self): return self.Voorgangstatus.text() def returnq4(self): return self.Startdatum.text() def returnq5(self): return self.Afgemeld.text() def returnq6(self): return self.Gereed.text() def returnq7(self): return self.Goedgekeurd.text() @staticmethod def getData(parent=None): dialog = Widget() dialog.exec_() return [dialog.returnq2(), dialog.returnq3(), dialog.returnq4(),\ dialog.returnq5(), dialog.returnq6(), dialog.returnq7()] window = Widget() data = window.getData() if not (data[0] or data[1] or data[2] or data[3] or data[4] or data[5]): return () if data[0]: momschr = data[0] else: momschr = rpord[1] if data[1]: mvgst = data[1] else: mvgst = rpord[3] if data[2]: mstart = data[2] else: mstart = rpord[6] if data[3]: mafgem = data[3] else: mafgem = rpord[7] if data[4]: mgereed = float(data[4]) else: mgereed = rpord[19] if data[5]: mgoedg = float(data[5]) else: mgoedg = rpord[18] mboekd = str(datetime.datetime.now())[0:10] mgoedgversch = mgoedg - rpord[18] updio = update(orders_intern).where(orders_intern.c.werkorderID == mwerkorder)\ .values(werkomschrijving = momschr, voortgangstatus = mvgst, startdatum = mstart,\ afgemeld = mafgem, boekdatum = mboekd, gereed = mgereed, goedgekeurd = mgoedg) con.execute(updio) updart = update(artikelen).where(and_(orders_intern.c.werkorderID == mwerkorder,\ orders_intern.c.artikelID == artikelen.c.artikelID)).values(art_voorraad =\ artikelen.c.art_voorraad + mgoedgversch, bestelsaldo = artikelen.c.bestelsaldo -\ mgoedgversch) con.execute(updart) try: mutnr=(con.execute(select([func.max(artikelmutaties.c.mutatieID,\ type_=Integer)])).scalar()) mutnr += 1 except: mutnr = 1 params = Table('params', metadata, Column('paramID', Integer, primary_key=True), Column('tarief', Float), Column('item', String), Column('lock', Boolean), Column('ondergrens', Float), Column('bovengrens', Float)) selpar = select([params]).order_by(params.c.paramID) rppar = con.execute(selpar).fetchall() selart = select([artikelen ]).where(rpord[16] == artikelen.c.artikelID) rpart = con.execute(selart).first() martprijs = rpart[3] ins = insert(artikelmutaties).values(mutatieID = mutnr, artikelID =\ rpord[16], werkorderID = mwerkorder, hoeveelheid = mgoedgversch,\ boekdatum = mboekd, tot_mag_prijs = mgoedgversch*martprijs,\ btw_hoog = mgoedgversch*martprijs*(rppar[0][1])) con.execute(ins) invoerOK() win = MyWindow(data_list, header) win.exec_() zoeken(m_email)
def toonWerken(keuze, zoekterm, m_email): import validZt metadata = MetaData() werken = Table('werken', metadata, Column('werknummerID', Integer(), primary_key=True), Column('werkomschrijving', String), Column('voortgangstatus', String), Column('statusweek', String), Column('startweek', String), Column('opdracht_datum', String), Column('aanneemsom', Float), Column('betaald_bedrag', Float), Column('begr_materialen', Float), Column('kosten_materialen', Float), Column('begr_lonen', Float), Column('kosten_lonen', Float), Column('begr_materieel', Float), Column('kosten_materieel', Float), Column('begr_leiding', Float), Column('kosten_leiding', Float), Column('begr_huisv', Float), Column('kosten_huisv', Float), Column('begr_inhuur', Float), Column('kosten_inhuur', Float), Column('begr_overig', Float), Column('kosten_overig', Float), Column('begr_vervoer', Float), Column('kosten_vervoer', Float), Column('begr_beton_bvl', Float), Column('beton_bvl', Float), Column('begr_kabelwerk', Float), Column('kabelwerk', Float), Column('begr_grondverzet', Float), Column('grondverzet', Float), Column('meerminderwerk', Float), Column('begr_constr_uren', Float), Column('werk_constr_uren', Float), Column('begr_mont_uren', Float), Column('werk_mont_uren', Float), Column('begr_retourlas_uren', Float), Column('werk_retourlas_uren', Float), Column('begr_telecom_uren', Float), Column('werk_telecom_uren', Float), Column('begr_bfi_uren', Float), Column('werk_bfi_uren', Float), Column('begr_bvl_uren', Float), Column('werk_bvl_uren', Float), Column('begr_voeding_uren', Float), Column('werk_voeding_uren', Float), Column('begr_spoorleg_uren', Float), Column('werk_spoorleg_uren', Float), Column('begr_spoorlas_uren', Float), Column('werk_spoorlas_uren', Float), Column('begr_reis_uren', Float), Column('werk_reis_uren', Float)) engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem') con = engine.connect() if keuze == 1: sel = select([werken]).order_by(werken.c.werknummerID) elif keuze == 2 and validZt.zt(zoekterm, 8): sel = select([werken]).where(werken.c.werknummerID == int(zoekterm)) elif keuze == 3: sel = select([werken]).where( werken.c.werkomschrijving.ilike('%' + zoekterm + '%')) elif keuze == 4 and validZt.zt(zoekterm, 18): sel = select([werken ]).where(werken.c.voortgangstatus == zoekterm.upper()) elif keuze == 5 and validZt.zt(zoekterm, 14): sel = select([werken]).where(werken.c.aanneemsom > float(zoekterm)) elif keuze == 6 and validZt.zt(zoekterm, 14): sel = select([werken]).where(werken.c.aanneemsom < float(zoekterm)) elif keuze == 7 and validZt.zt(zoekterm, 14): sel = select([werken ]).where(werken.c.betaald_bedrag / werken.c.aanneemsom > float(zoekterm) / 100) else: ongInvoer() werkenKeuze(m_email) if con.execute(sel).fetchone(): rpwerken = con.execute(sel) else: geenRecord() werkenKeuze(m_email) class MyWindow(QDialog): def __init__(self, data_list, header, *args): QWidget.__init__( self, *args, ) self.setGeometry(50, 50, 1800, 900) self.setWindowTitle('Werken extern opvragen') self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setWindowFlags(self.windowFlags() | Qt.WindowSystemMenuHint | Qt.WindowMinMaxButtonsHint) table_model = MyTableModel(self, data_list, header) table_view = QTableView() table_view.setModel(table_model) font = QFont("Arial", 10) table_view.setFont(font) table_view.resizeColumnsToContents() table_view.setSelectionBehavior(QTableView.SelectRows) table_view.clicked.connect(showWerk) layout = QVBoxLayout(self) layout.addWidget(table_view) self.setLayout(layout) class MyTableModel(QAbstractTableModel): def __init__(self, parent, mylist, header, *args): QAbstractTableModel.__init__(self, parent, *args) self.mylist = mylist self.header = header def rowCount(self, parent): return len(self.mylist) def columnCount(self, parent): return len(self.mylist[0]) def data(self, index, role): if not index.isValid(): return None elif role != Qt.DisplayRole: return None return str(self.mylist[index.row()][index.column()]).format( {'12.2f'}) def headerData(self, col, orientation, role): if orientation == Qt.Horizontal and role == Qt.DisplayRole: return self.header[col] return None header = ['Werknummer','Werkomschrijving', 'Voortgangstatus','Statusweek',\ 'Startweek','Opdrachtdatum','Aanneemsom','Betaald bedrag','Begr.Materialen',\ 'Werk.Materialen', 'Begr.Lonen', 'Werk.Lonen', 'Begr.Materieel',\ 'Werk.materieel','Begr.leiding', 'Werk.leiding','Begr.huisvesting',\ 'Werk.huisvesting', 'Begr.inhuur','Werk.inhuur','Begr.Overig',\ 'Werk.Overig', 'Begr.Vervoer', 'Werk.Vervoer', 'Begr.beton bvl',\ 'Werk.beton bvl','Begr.kabelwerk', 'Werk.kabelwerk','Begr.grondverzet',\ 'Werk,grondverzet', 'Meerminderwerk','Begr.constr.uren','Werk.constr.uren',\ 'Begr.mont.uren', 'Werk.mont.uren', 'Begr.retourl.uren', 'Werk.retourl.uren',\ 'Begr.telecomuren','Werk.telecomuren', 'Begr.bfi-uren','Werk.bfi-uren',\ 'Begr.bvl-uren', 'Werk.bvl-uren','Begr.voedings-uren','Werk.voedings-uren',\ 'Begr.spoorleg-uren','Werk.spoorleg-uren', 'Begr.spoorlas-uren',\ 'Werk.spoorlas-uren', 'Begr.reis-uren', 'Werk.reis-uren'] data_list = [] for row in rpwerken: data_list += [(row)] def showWerk(idx): mwerknr = idx.data() if idx.column() == 0: selwerk = select([werken]).where(werken.c.werknummerID == mwerknr) rpwerk = con.execute(selwerk).first() msom = rpwerk[6] mtotopbr = rpwerk[6] + rpwerk[30] mbtot = rpwerk[8]+rpwerk[10]+rpwerk[12]+rpwerk[14]+rpwerk[16]+rpwerk[18]+\ rpwerk[20]+rpwerk[22]+rpwerk[24]+rpwerk[26]+rpwerk[28]+rpwerk[30] mktotal=rpwerk[9]+rpwerk[11]+rpwerk[13]+rpwerk[15]+rpwerk[17]+rpwerk[19]+rpwerk[21]+rpwerk[23]\ +rpwerk[25]+rpwerk[27]+rpwerk[29] mbderden = rpwerk[16] + rpwerk[18] + rpwerk[20] + rpwerk[ 22] + rpwerk[24] + rpwerk[26] + rpwerk[28] mkderden = rpwerk[17] + rpwerk[19] + rpwerk[21] + rpwerk[ 23] + rpwerk[25] + rpwerk[27] + rpwerk[29] mbetaald = rpwerk[7] mvgangst = rpwerk[2] mstatwk = rpwerk[3] mfact = 0 flag = 0 mwerknr = rpwerk[0] mmeerwerk = float(rpwerk[30]) if mvgangst == 'A': if mktotal > 0: mvgangst = 'B' mstatwk = jaarweek() flag = 1 elif mvgangst == 'B': if mktotal > msom / 3: mvgangst = 'C' mstatwk = jaarweek() flag = 1 elif mvgangst == 'C': if mktotal > msom / 2: mvgangst = 'D' mstatwk = jaarweek() flag = 1 mfact = msom / 3 - mbetaald elif mvgangst == 'D': if mktotal > msom / 1.5: mvgangst = 'E' mstatwk = jaarweek() flag = 1 mfact = 0 elif mvgangst == 'E': if mktotal >= msom: mvgangst = 'F' mstatwk = jaarweek() flag = 1 mfact = msom / 1.5 - mbetaald elif mvgangst == 'F': mfact = msom - mbetaald * 0.9 elif mvgangst == 'G': mfact = msom + mmeerwerk - mbetaald if flag: werkupd = update(werken).where(werken.c.werknummerID == mwerknr)\ .values(statusweek = mstatwk, voortgangstatus = mvgangst) con.execute(werkupd) class Widget(QDialog): def __init__(self, parent=None): super(Widget, self).__init__(parent) self.setWindowTitle("Werken extern gegevens opvragen") self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setFont(QFont('Arial', 10)) q1Edit = QLineEdit(str(rpwerk[0])) q1Edit.setFixedWidth(100) q1Edit.setDisabled(True) q1Edit.setFont(QFont("Arial", 10)) q2Edit = QLineEdit(str(rpwerk[1])) q2Edit.setFixedWidth(400) q2Edit.setDisabled(True) q2Edit.setFont(QFont("Arial", 10)) q3Edit = QLineEdit(rpwerk[2]) q3Edit.setFixedWidth(20) q3Edit.setFont(QFont("Arial", 10)) q3Edit.setDisabled(True) q4Edit = QLineEdit(rpwerk[3]) q4Edit.setFixedWidth(100) q4Edit.setFont(QFont("Arial", 10)) q4Edit.setDisabled(True) q5Edit = QLineEdit(rpwerk[4]) q5Edit.setFixedWidth(100) q5Edit.setFont(QFont("Arial", 10)) q5Edit.setDisabled(True) q8Edit = QLineEdit(str(round(rpwerk[6], 2))) q8Edit.setFixedWidth(100) q8Edit.setFont(QFont("Arial", 10)) q8Edit.setDisabled(True) q9Edit = QLineEdit(str(round(rpwerk[30], 2))) q9Edit.setFixedWidth(100) q9Edit.setFont(QFont("Arial", 10)) q9Edit.setDisabled(True) q11Edit = QLineEdit(str(round(rpwerk[7], 2))) q11Edit.setFixedWidth(100) q11Edit.setFont(QFont("Arial", 10)) q11Edit.setDisabled(True) q12Edit = QLineEdit(str(round(rpwerk[8], 2))) q12Edit.setFixedWidth(100) q12Edit.setFont(QFont("Arial", 10)) q12Edit.setDisabled(True) q13Edit = QLineEdit(str(round(rpwerk[9], 2))) q13Edit.setFixedWidth(100) q13Edit.setFont(QFont("Arial", 10)) q13Edit.setDisabled(True) q19Edit = QLineEdit(str(round(rpwerk[10], 2))) q19Edit.setFixedWidth(100) q19Edit.setFont(QFont("Arial", 10)) q19Edit.setDisabled(True) q14Edit = QLineEdit(str(round(rpwerk[11], 2))) q14Edit.setFixedWidth(100) q14Edit.setFont(QFont("Arial", 10)) q14Edit.setDisabled(True) q15Edit = QLineEdit(str(round(rpwerk[12], 2))) q15Edit.setDisabled(True) q15Edit.setFixedWidth(100) q15Edit.setFont(QFont("Arial", 10)) q16Edit = QLineEdit(str(round(rpwerk[13], 2))) q16Edit.setFixedWidth(100) q16Edit.setFont(QFont("Arial", 10)) q16Edit.setDisabled(True) q18Edit = QLineEdit(str(round(rpwerk[14], 2))) q18Edit.setFixedWidth(100) q18Edit.setFont(QFont("Arial", 10)) q18Edit.setDisabled(True) q17Edit = QLineEdit(str(round(rpwerk[15], 2))) q17Edit.setFixedWidth(100) q17Edit.setFont(QFont("Arial", 10)) q17Edit.setDisabled(True) q20Edit = QLineEdit(str(round(mtotopbr, 2))) q20Edit.setFixedWidth(100) q20Edit.setFont(QFont("Arial", 10)) q20Edit.setDisabled(True) q21Edit = QLineEdit(str(round(mbtot, 2))) q21Edit.setFixedWidth(100) q21Edit.setFont(QFont("Arial", 10)) q17Edit.setDisabled(True) q22Edit = QLineEdit(str(round(mktotal, 2))) q22Edit.setFixedWidth(100) q22Edit.setFont(QFont("Arial", 10)) q22Edit.setDisabled(True) q23Edit = QLineEdit(str(round(mbderden, 2))) q23Edit.setFixedWidth(100) q23Edit.setFont(QFont("Arial", 10)) q23Edit.setDisabled(True) q24Edit = QLineEdit(str(round(mkderden, 2))) q24Edit.setFixedWidth(100) q24Edit.setFont(QFont("Arial", 10)) q24Edit.setDisabled(True) q26Edit = QLineEdit(str(round(mfact, 2))) q26Edit.setFixedWidth(100) q26Edit.setFont(QFont("Arial", 10)) q26Edit.setDisabled(True) u1Edit = QLineEdit(str(round(rpwerk[31], 2))) u1Edit.setFixedWidth(100) u1Edit.setFont(QFont("Arial", 10)) u1Edit.setDisabled(True) u2Edit = QLineEdit(str(round(rpwerk[32], 2))) u2Edit.setFixedWidth(100) u2Edit.setFont(QFont("Arial", 10)) u2Edit.setDisabled(True) u3Edit = QLineEdit(str(round(rpwerk[33], 2))) u3Edit.setFixedWidth(100) u3Edit.setFont(QFont("Arial", 10)) u3Edit.setDisabled(True) u4Edit = QLineEdit(str(round(rpwerk[34], 2))) u4Edit.setFixedWidth(100) u4Edit.setFont(QFont("Arial", 10)) u4Edit.setDisabled(True) u5Edit = QLineEdit(str(round(rpwerk[35], 2))) u5Edit.setFixedWidth(100) u5Edit.setFont(QFont("Arial", 10)) u5Edit.setDisabled(True) u6Edit = QLineEdit(str(round(rpwerk[36], 2))) u6Edit.setFixedWidth(100) u6Edit.setFont(QFont("Arial", 10)) u6Edit.setDisabled(True) u7Edit = QLineEdit(str(round(rpwerk[37], 2))) u7Edit.setFixedWidth(100) u7Edit.setFont(QFont("Arial", 10)) u7Edit.setDisabled(True) u8Edit = QLineEdit(str(round(rpwerk[38], 2))) u8Edit.setFixedWidth(100) u8Edit.setFont(QFont("Arial", 10)) u8Edit.setDisabled(True) u9Edit = QLineEdit(str(round(rpwerk[39], 2))) u9Edit.setFixedWidth(100) u9Edit.setFont(QFont("Arial", 10)) u9Edit.setDisabled(True) u10Edit = QLineEdit(str(round(rpwerk[40], 2))) u10Edit.setFixedWidth(100) u10Edit.setFont(QFont("Arial", 10)) u10Edit.setDisabled(True) u11Edit = QLineEdit(str(round(rpwerk[41], 2))) u11Edit.setFixedWidth(100) u11Edit.setFont(QFont("Arial", 10)) u11Edit.setDisabled(True) u12Edit = QLineEdit(str(round(rpwerk[42], 2))) u12Edit.setFixedWidth(100) u12Edit.setFont(QFont("Arial", 10)) u12Edit.setDisabled(True) u13Edit = QLineEdit(str(round(rpwerk[43], 2))) u13Edit.setFixedWidth(100) u13Edit.setFont(QFont("Arial", 10)) u13Edit.setDisabled(True) u14Edit = QLineEdit(str(round(rpwerk[44], 2))) u14Edit.setFixedWidth(100) u14Edit.setFont(QFont("Arial", 10)) u14Edit.setDisabled(True) u15Edit = QLineEdit(str(round(rpwerk[45], 2))) u15Edit.setFixedWidth(100) u15Edit.setFont(QFont("Arial", 10)) u15Edit.setDisabled(True) u16Edit = QLineEdit(str(round(rpwerk[46], 2))) u16Edit.setFixedWidth(100) u16Edit.setFont(QFont("Arial", 10)) u16Edit.setDisabled(True) u17Edit = QLineEdit(str(round(rpwerk[47], 2))) u17Edit.setFixedWidth(100) u17Edit.setFont(QFont("Arial", 10)) u17Edit.setDisabled(True) u18Edit = QLineEdit(str(round(rpwerk[48], 2))) u18Edit.setFixedWidth(100) u18Edit.setFont(QFont("Arial", 10)) u18Edit.setDisabled(True) u19Edit = QLineEdit(str(round(rpwerk[49], 2))) u19Edit.setFixedWidth(100) u19Edit.setFont(QFont("Arial", 10)) u19Edit.setDisabled(True) u20Edit = QLineEdit(str(round(rpwerk[50], 2))) u20Edit.setFixedWidth(100) u20Edit.setFont(QFont("Arial", 10)) u20Edit.setDisabled(True) d1Edit = QLineEdit(str(round(rpwerk[16], 2))) d1Edit.setFixedWidth(100) d1Edit.setFont(QFont("Arial", 10)) d1Edit.setDisabled(True) d2Edit = QLineEdit(str(round(rpwerk[17], 2))) d2Edit.setFixedWidth(100) d2Edit.setFont(QFont("Arial", 10)) d2Edit.setDisabled(True) d3Edit = QLineEdit(str(round(rpwerk[18], 2))) d3Edit.setFixedWidth(100) d3Edit.setFont(QFont("Arial", 10)) d3Edit.setDisabled(True) d4Edit = QLineEdit(str(round(rpwerk[19], 2))) d4Edit.setFixedWidth(100) d4Edit.setFont(QFont("Arial", 10)) d4Edit.setDisabled(True) d5Edit = QLineEdit(str(round(rpwerk[20], 2))) d5Edit.setFixedWidth(100) d5Edit.setFont(QFont("Arial", 10)) d5Edit.setDisabled(True) d6Edit = QLineEdit(str(round(rpwerk[21], 2))) d6Edit.setFixedWidth(100) d6Edit.setFont(QFont("Arial", 10)) d6Edit.setDisabled(True) d7Edit = QLineEdit(str(round(rpwerk[22], 2))) d7Edit.setFixedWidth(100) d7Edit.setFont(QFont("Arial", 10)) d7Edit.setDisabled(True) d8Edit = QLineEdit(str(round(rpwerk[23], 2))) d8Edit.setFixedWidth(100) d8Edit.setFont(QFont("Arial", 10)) d8Edit.setDisabled(True) d9Edit = QLineEdit(str(round(rpwerk[24], 2))) d9Edit.setFixedWidth(100) d9Edit.setFont(QFont("Arial", 10)) d9Edit.setDisabled(True) d10Edit = QLineEdit(str(round(rpwerk[25], 2))) d10Edit.setFixedWidth(100) d10Edit.setFont(QFont("Arial", 10)) d10Edit.setDisabled(True) d11Edit = QLineEdit(str(round(rpwerk[26], 2))) d11Edit.setFixedWidth(100) d11Edit.setFont(QFont("Arial", 10)) d11Edit.setDisabled(True) d12Edit = QLineEdit(str(round(rpwerk[27], 2))) d12Edit.setFixedWidth(100) d12Edit.setFont(QFont("Arial", 10)) d12Edit.setDisabled(True) d13Edit = QLineEdit(str(round(rpwerk[28], 2))) d13Edit.setFixedWidth(100) d13Edit.setFont(QFont("Arial", 10)) d13Edit.setDisabled(True) d14Edit = QLineEdit(str(round(rpwerk[29], 2))) d14Edit.setFixedWidth(100) d14Edit.setFont(QFont("Arial", 10)) d14Edit.setDisabled(True) grid = QGridLayout() grid.setSpacing(20) lbl = QLabel() pixmap = QPixmap('./images/logos/verbinding.jpg') lbl.setPixmap(pixmap) grid.addWidget(lbl, 0, 0) logo = QLabel() pixmap = QPixmap('./images/logos/logo.jpg') logo.setPixmap(pixmap) grid.addWidget(logo, 0, 8, 1, 1, Qt.AlignRight) self.setFont(QFont('Arial', 10)) grid.addWidget(QLabel('Werknummer'), 1, 0) grid.addWidget(q1Edit, 1, 1) grid.addWidget(QLabel('Werkomschrijving'), 1, 2) grid.addWidget(q2Edit, 1, 3, 1, 3) grid.addWidget(QLabel('Voortgangstatus'), 1, 6) grid.addWidget(q3Edit, 1, 7) grid.addWidget(QLabel('Statusweek'), 1, 7, 1, 1, Qt.AlignRight) grid.addWidget(q4Edit, 1, 8) grid.addWidget(QLabel('Startweek'), 2, 2) grid.addWidget(q5Edit, 2, 3) grid.addWidget(QLabel('Aanneemsom'), 2, 4) grid.addWidget(q8Edit, 2, 5) grid.addWidget(QLabel('Meerminderwerk'), 2, 6) grid.addWidget(q9Edit, 2, 7) lbl1 = QLabel('Financieële totaal bedragen') lbl1.setStyleSheet("font: 12pt Comic Sans MS") grid.addWidget(lbl1, 3, 0, 1, 2) grid.addWidget(QLabel('Totaal opbrengsten'), 4, 0) grid.addWidget(q21Edit, 4, 1) grid.addWidget(QLabel('Totaal kosten'), 4, 2) grid.addWidget(q22Edit, 4, 3) grid.addWidget(QLabel('Betaald bedrag'), 4, 4) grid.addWidget(q11Edit, 4, 5) grid.addWidget(QLabel('Te factureren'), 4, 6) grid.addWidget(q26Edit, 4, 7) grid.addWidget(QLabel('Begroot'), 5, 1) grid.addWidget(QLabel('Werkelijk'), 5, 2) grid.addWidget(QLabel('Begroot'), 5, 4) grid.addWidget(QLabel('Werkelijk'), 5, 5) grid.addWidget(QLabel('Begroot'), 5, 7) grid.addWidget(QLabel('Werkelijk'), 5, 8) grid.addWidget(QLabel('Materialen'), 6, 0) grid.addWidget(q12Edit, 6, 1) grid.addWidget(q13Edit, 6, 2) grid.addWidget(QLabel('Lonen'), 6, 3) grid.addWidget(q19Edit, 6, 4) grid.addWidget(q14Edit, 6, 5) grid.addWidget(QLabel('Materieel'), 6, 6) grid.addWidget(q15Edit, 6, 7) grid.addWidget(q16Edit, 6, 8) grid.addWidget(QLabel('Derden'), 7, 0) grid.addWidget(q23Edit, 7, 1) grid.addWidget(q24Edit, 7, 2) grid.addWidget(QLabel('Leiding'), 7, 3) grid.addWidget(q18Edit, 7, 4) grid.addWidget(q17Edit, 7, 5) lbl2 = QLabel('Werkuren verbruik') lbl2.setStyleSheet("font: 12pt Comic Sans MS") grid.addWidget(lbl2, 8, 0, 1, 2) grid.addWidget(QLabel('Begroot'), 9, 1) grid.addWidget(QLabel('Werkelijk'), 9, 2) grid.addWidget(QLabel('Begroot'), 9, 4) grid.addWidget(QLabel('Werkelijk'), 9, 5) grid.addWidget(QLabel('Begroot'), 9, 7) grid.addWidget(QLabel('Werkelijk'), 9, 8) grid.addWidget(QLabel('Construktie'), 10, 0) grid.addWidget(u1Edit, 10, 1) grid.addWidget(u2Edit, 10, 2) grid.addWidget(QLabel('Montage'), 10, 3) grid.addWidget(u3Edit, 10, 4) grid.addWidget(u4Edit, 10, 5) grid.addWidget(QLabel('Retourlas'), 10, 6) grid.addWidget(u5Edit, 10, 7) grid.addWidget(u6Edit, 10, 8) grid.addWidget(QLabel('Telecom'), 11, 0) grid.addWidget(u7Edit, 11, 1) grid.addWidget(u8Edit, 11, 2) grid.addWidget(QLabel('Bfi'), 11, 3) grid.addWidget(u9Edit, 11, 4) grid.addWidget(u10Edit, 11, 5) grid.addWidget(QLabel('Bovenleiding'), 11, 6) grid.addWidget(u11Edit, 11, 7) grid.addWidget(u12Edit, 11, 8) grid.addWidget(QLabel('Voeding'), 12, 0) grid.addWidget(u13Edit, 12, 1) grid.addWidget(u14Edit, 12, 2) grid.addWidget(QLabel('Spoorleg'), 12, 3) grid.addWidget(u15Edit, 12, 4) grid.addWidget(u16Edit, 12, 5) grid.addWidget(QLabel('Spoorlas'), 12, 6) grid.addWidget(u17Edit, 12, 7) grid.addWidget(u18Edit, 12, 8) grid.addWidget(QLabel('Reisuren'), 13, 0) grid.addWidget(u19Edit, 13, 1) grid.addWidget(u20Edit, 13, 2) lbl3 = QLabel('Diensten derden') lbl3.setStyleSheet("font: 12pt Comic Sans MS") grid.addWidget(lbl3, 14, 0, 1, 2) grid.addWidget(QLabel('Begroot'), 15, 1) grid.addWidget(QLabel('Werkelijk'), 15, 2) grid.addWidget(QLabel('Begroot'), 15, 4) grid.addWidget(QLabel('Werkelijk'), 15, 5) grid.addWidget(QLabel('Begroot'), 15, 7) grid.addWidget(QLabel('Werkelijk'), 15, 8) grid.addWidget(QLabel('Huisvesting'), 16, 0) grid.addWidget(d1Edit, 16, 1) grid.addWidget(d2Edit, 16, 2) grid.addWidget(QLabel('Inhuur'), 16, 3) grid.addWidget(d3Edit, 16, 4) grid.addWidget(d4Edit, 16, 5) grid.addWidget(QLabel('Overig'), 16, 6) grid.addWidget(d5Edit, 16, 7) grid.addWidget(d6Edit, 16, 8) grid.addWidget(QLabel('Vervoer'), 17, 0) grid.addWidget(d7Edit, 17, 1) grid.addWidget(d8Edit, 17, 2) grid.addWidget(QLabel('Beton bvl'), 17, 3) grid.addWidget(d9Edit, 17, 4) grid.addWidget(d10Edit, 17, 5) grid.addWidget(QLabel('Kabelwerk'), 17, 6) grid.addWidget(d11Edit, 17, 7) grid.addWidget(d12Edit, 17, 8) grid.addWidget(QLabel('Grondverzet'), 18, 0) grid.addWidget(d13Edit, 18, 1) grid.addWidget(d14Edit, 18, 2) grid.addWidget( QLabel( '\u00A9 2017 all rights reserved [email protected]' ), 20, 0, 1, 8, Qt.AlignCenter) self.setLayout(grid) self.setGeometry(400, 50, 350, 300) cancelBtn = QPushButton('Sluiten') cancelBtn.clicked.connect(self.close) grid.addWidget(cancelBtn, 19, 8, 1, 1, Qt.AlignRight) cancelBtn.setFont(QFont("Arial", 10)) cancelBtn.setFixedWidth(100) cancelBtn.setStyleSheet( "color: black; background-color: gainsboro") window = Widget() window.exec_() win = MyWindow(data_list, header) win.exec_() werkenKeuze(m_email)
def toonBedrijfverkoop(keuze, zoekterm, m_email): import validZt class MyWindow(QDialog): def __init__(self, data_list, header, *args): QWidget.__init__( self, *args, ) self.setGeometry(50, 50, 1500, 900) self.setWindowTitle('Verkoopbedrijven opvragen') self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setWindowFlags(self.windowFlags() | Qt.WindowSystemMenuHint | Qt.WindowMinMaxButtonsHint) table_model = MyTableModel(self, data_list, header) table_view = QTableView() table_view.setModel(table_model) font = QFont("Arial", 10) table_view.setFont(font) table_view.resizeColumnsToContents() table_view.setSelectionBehavior(QTableView.SelectRows) table_view.clicked.connect(showBedrijf) layout = QVBoxLayout(self) layout.addWidget(table_view) self.setLayout(layout) class MyTableModel(QAbstractTableModel): def __init__(self, parent, mylist, header, *args): QAbstractTableModel.__init__(self, parent, *args) self.mylist = mylist self.header = header def rowCount(self, parent): return len(self.mylist) def columnCount(self, parent): return len(self.mylist[0]) def data(self, index, role): if not index.isValid(): return None elif role != Qt.DisplayRole: return None return str(self.mylist[index.row()][index.column()]) def headerData(self, col, orientation, role): if orientation == Qt.Horizontal and role == Qt.DisplayRole: return self.header[col] return None header = ['BedrijfverkoopID','Bedrijfsnaam', 'Rechtsvorm','Afdeling', 'BTWNummer', 'KVKNummer',\ 'Telefoonnummer','Straat', 'Huisnummer', 'Toevoeging', 'Postcode', 'Woonplaats'] metadata = MetaData() kopers = Table('kopers', metadata, Column('koperID', Integer(), primary_key=True), Column('bedrijfsnaam', String), Column('rechtsvorm', String), Column('afdeling', String), Column('btwnummer', String), Column('kvknummer', String), Column('telnr', String), Column('huisnummer', String), Column('toevoeging', String), Column('postcode', String)) engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem') conn = engine.connect() if keuze == 1: selkop = select([kopers]).order_by(kopers.c.koperID) elif keuze == 2: selkop = select([kopers]).where(kopers.c.bedrijfsnaam.ilike('%'+zoekterm+'%'))\ .order_by(kopers.c.koperID) elif keuze == 3 and validZt.zt(zoekterm, 6): selkop = select([kopers]).where(kopers.c.koperID == zoekterm).\ order_by(kopers.c.koperID) elif keuze == 4 and validZt.zt(zoekterm, 9): selkop = select([kopers]).where(kopers.c.postcode.ilike(zoekterm+'%')).\ order_by(kopers.c.koperID) else: ongInvoer() koperKeuze(m_email) if conn.execute(selkop).fetchone(): rpkop = conn.execute(selkop) else: geenRecord() koperKeuze(m_email) import postcode data_list = [] for row in rpkop: msp = postcode.checkpostcode(row[9], int(row[7])) mstraat = msp[0] mplaats = msp[1] data_list += [(row[0],row[1],row[2],row[3],row[4],row[5],\ row[6],mstraat,row[7],row[8],row[9],mplaats)] def showBedrijf(idx): mbedrnr = idx.data() if idx.column() == 0: selkoper = select([kopers]).where(kopers.c.koperID == mbedrnr) rpkoper = conn.execute(selkoper).first() mpostcode = rpkoper[9] mhuisnr = int(rpkoper[7]) import postcode mstrtplts = postcode.checkpostcode(mpostcode, mhuisnr) mstraat = mstrtplts[0] mplaats = mstrtplts[1] class Widget(QDialog): def __init__(self, parent=None): super(Widget, self).__init__(parent) self.setWindowTitle("Opvragen verkoopbedrijven") self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setFont(QFont('Arial', 10)) self.Bedrijfsnaam = QLabel() q3Edit = QLineEdit(rpkoper[1]) q3Edit.setFixedWidth(540) q3Edit.setFont(QFont("Arial", 10)) q3Edit.setDisabled(True) self.Afdeling = QLabel() q16Edit = QLineEdit(rpkoper[3]) q16Edit.setFixedWidth(540) q16Edit.setFont(QFont("Arial", 10)) q16Edit.setDisabled(True) self.Rechtsvorm = QLabel() q5Edit = QLineEdit(rpkoper[2]) q5Edit.setFixedWidth(100) q5Edit.setFont(QFont("Arial", 10)) q5Edit.setDisabled(True) self.BTWnummer = QLabel() q2Edit = QLineEdit(rpkoper[4]) q2Edit.setDisabled(True) q2Edit.setFixedWidth(170) q2Edit.setFont(QFont("Arial", 10)) self.KvKnummer = QLabel() q4Edit = QLineEdit(rpkoper[5]) q4Edit.setFixedWidth(110) q4Edit.setDisabled(True) self.Straat = QLabel() q1Edit = QLineEdit() q1Edit.setText(mstraat) q1Edit.setFixedWidth(540) q1Edit.setDisabled(True) self.Huisnummer = QLabel() q7Edit = QLineEdit(str(mhuisnr)) q7Edit.setFixedWidth(60) q7Edit.setFont(QFont("Arial", 10)) q7Edit.setDisabled(True) self.Toevoeging = QLabel() q8Edit = QLineEdit(rpkoper[8]) q8Edit.setFixedWidth(80) q8Edit.setFont(QFont("Arial", 10)) q8Edit.setDisabled(True) self.Postcode = QLabel() q6Edit = QLineEdit(mpostcode) q6Edit.setFixedWidth(80) q6Edit.setFont(QFont("Arial", 10)) q6Edit.setDisabled(True) self.Woonplaats = QLabel() q15Edit = QLineEdit(mplaats) q15Edit.setFixedWidth(400) q15Edit.setDisabled(True) self.Telefoonnr = QLabel() q13Edit = QLineEdit(rpkoper[6]) q13Edit.setFixedWidth(120) q13Edit.setFont(QFont("Arial", 10)) q13Edit.setDisabled(True) self.Bedrijfverkoopnummer = QLabel() q14Edit = QLineEdit(str(rpkoper[0])) q14Edit.setFixedWidth(120) q14Edit.setDisabled(True) q14Edit.setFont(QFont("Arial", 10)) grid = QGridLayout() grid.setSpacing(20) lbl = QLabel() pixmap = QPixmap('./images/logos/verbinding.jpg') lbl.setPixmap(pixmap) grid.addWidget(lbl, 0, 0) logo = QLabel() pixmap = QPixmap('./images/logos/logo.jpg') logo.setPixmap(pixmap) grid.addWidget(logo, 0, 1, 1, 2, Qt.AlignRight) self.setFont(QFont('Arial', 10)) grid.addWidget(QLabel('Verkoopbedrijven opvragen'), 0, 1) grid.addWidget(QLabel('Bedrijfsnaam'), 1, 0) grid.addWidget(q3Edit, 1, 1, 1, 3) grid.addWidget( QLabel('Afdelingsnaam/Kamer/\nKontaktpersoon'), 2, 0) grid.addWidget(q16Edit, 2, 1, 1, 3) grid.addWidget(QLabel('Rechtsvorm'), 3, 0) grid.addWidget(q5Edit, 3, 1) grid.addWidget(QLabel('BTWnummer'), 3, 1, 1, 1, Qt.AlignRight) grid.addWidget(q2Edit, 3, 2) grid.addWidget(QLabel('KvKnummer'), 4, 0) grid.addWidget(q4Edit, 4, 1) grid.addWidget(QLabel('Straat'), 5, 0) grid.addWidget(q1Edit, 5, 1, 1, 3) grid.addWidget(QLabel('Huisnummer'), 6, 0) grid.addWidget(q7Edit, 6, 1) grid.addWidget(QLabel('Toevoeging'), 6, 1, 1, 1, Qt.AlignRight) grid.addWidget(q8Edit, 6, 2) grid.addWidget(QLabel('Postcode Woonplaats'), 7, 0) grid.addWidget(q6Edit, 7, 1) grid.addWidget(q15Edit, 7, 1, 1, 2, Qt.AlignRight) grid.addWidget(QLabel('Telefoonnummer'), 8, 0) grid.addWidget(q13Edit, 8, 1) grid.addWidget(QLabel('Verkoop bedrijfnummer'), 9, 0) grid.addWidget(q14Edit, 9, 1) grid.addWidget( QLabel( '\u00A9 2017 all rights reserved [email protected]' ), 10, 1) terugBtn = QPushButton('Sluiten') terugBtn.clicked.connect(self.close) grid.addWidget(terugBtn, 9, 2, 1, 1, Qt.AlignRight) terugBtn.setFont(QFont("Arial", 10)) terugBtn.setFixedWidth(100) terugBtn.setStyleSheet( "color: black; background-color: gainsboro") self.setLayout(grid) self.setGeometry(500, 100, 150, 150) mainWin = Widget() mainWin.exec_() win = MyWindow(data_list, header) win.exec_() koperKeuze(m_email)
def toonClusterartikelen(zoekterm, m_email): import validZt class MyWindow(QDialog): def __init__(self, data_list, header, *args): QWidget.__init__( self, *args, ) self.setGeometry(100, 50, 1800, 900) self.setWindowTitle('Artikelen per cluster opvragen') self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setWindowFlags(self.windowFlags() | Qt.WindowSystemMenuHint | Qt.WindowMinMaxButtonsHint) table_model = MyTableModel(self, data_list, header) table_view = QTableView() table_view.setModel(table_model) font = QFont("Arial", 10) table_view.setFont(font) table_view.resizeColumnsToContents() table_view.setSelectionBehavior(QTableView.SelectRows) table_view.clicked.connect(showSelection) layout = QVBoxLayout(self) layout.addWidget(table_view) self.setLayout(layout) class MyTableModel(QAbstractTableModel): def __init__(self, parent, mylist, header, *args): QAbstractTableModel.__init__(self, parent, *args) self.mylist = mylist self.header = header def rowCount(self, parent): return len(self.mylist) def columnCount(self, parent): return len(self.mylist[0]) def data(self, index, role): if not index.isValid(): return None elif role != Qt.DisplayRole: return None return str(self.mylist[index.row()][index.column()]) def headerData(self, col, orientation, role): if orientation == Qt.Horizontal and role == Qt.DisplayRole: return self.header[col] return None header = ['Clusternr', 'Omschrijving', 'Prijs', 'Eenheid', 'Materialen', 'Lonen',\ 'Diensten', 'Materiëel', 'Inhuur', 'uren_constr', 'uren_mont','uren_retourlas',\ 'uren_bfi', 'uren_voeding', 'uren_bvl', 'uren_spoorleg', 'uren_spoorlas',\ 'uren_inhuur', 'Sleuvengraver', 'Persapparaat', 'Atlaskraan',\ 'Kraan_groot', 'Mainliner', 'Hormachine', 'Wagon', 'Locomotor',\ 'Locomotief', 'Montagewagen', 'Stormobiel', 'uren_telecom'] metadata = MetaData() clusters = Table('clusters', metadata, Column('clusterID', Integer(), primary_key=True), Column('omschrijving', String), Column('prijs', Float), Column('eenheid', String), Column('materialen', Float), Column('lonen', Float), Column('diensten', Float), Column('materieel', Float), Column('inhuur', Float), Column('uren_constr', Float), Column('uren_mont', Float), Column('uren_retourlas', Float), Column('uren_bfi', Float), Column('uren_voeding', Float), Column('uren_bvl', Float), Column('uren_spoorleg', Float), Column('uren_spoorlas', Float), Column('uren_inhuur', Float), Column('sleuvengraver', Float), Column('persapparaat', Float), Column('atlaskraan', Float), Column('kraan_groot', Float), Column('mainliner', Float), Column('hormachine', Float), Column('wagon', Float), Column('locomotor', Float), Column('locomotief', Float), Column('montagewagen', Float), Column('stormobiel', Float), Column('uren_telecom', Float)) engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem') con = engine.connect() if zoekterm == '0': sel = select([clusters]).order_by(clusters.c.clusterID) elif validZt.zt(zoekterm, 26): sel = select([clusters]).where(clusters.c.clusterID.ilike(zoekterm+'%'))\ .order_by(clusters.c.clusterID) else: ongInvoer() zoeken(m_email) if con.execute(sel).fetchone(): rp = con.execute(sel) else: geenRecord() zoeken(m_email) data_list = [] for row in rp: data_list += [(row)] def showSelection(idx): clusternr = str(idx.data()) if idx.column() == 0: metadata = MetaData() clusters = Table('clusters', metadata, Column('clusterID', Integer, primary_key=True), Column('omschrijving', String)) artikelen = Table('artikelen', metadata, Column('artikelID', Integer, primary_key=True), Column('artikelomschrijving', String), Column('artikelprijs', Float)) cluster_artikelen = Table( 'cluster_artikelen', metadata, Column('cluster_artID', Integer, primary_key=True), Column('clusterID', None, ForeignKey('clusters.clusterID')), Column('artikelID', None, ForeignKey('artikelen.artikelID')), Column('hoeveelheid', Float)) engine = create_engine( 'postgresql+psycopg2://postgres@localhost/bisystem') con = engine.connect() selcl = select([cluster_artikelen, clusters, artikelen]).where(and_\ (cluster_artikelen.c.clusterID == clusternr, cluster_artikelen.c.clusterID==\ clusters.c.clusterID, cluster_artikelen.c.artikelID == artikelen.c.artikelID)) rpsel = con.execute(selcl) regel = 0 for row in rpsel: regel += 1 class MainWindow(QDialog): def __init__(self): QDialog.__init__(self) grid = QGridLayout() grid.setSpacing(20) self.setWindowTitle( "Opvragen artikelregels per cluster") self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setFont(QFont('Arial', 10)) q1Edit = QLineEdit(str(row[5])) q1Edit.setFixedWidth(400) q1Edit.setFont(QFont("Arial", 10)) q1Edit.setDisabled(True) q6Edit = QLineEdit(str(row[2])) q6Edit.setFixedWidth(150) q6Edit.setFont(QFont("Arial", 10)) q6Edit.setDisabled(True) q2Edit = QLineEdit(str(row[8])) q2Edit.setFixedWidth(150) q2Edit.setFont(QFont("Arial", 10)) q2Edit.setDisabled(True) q3Edit = QLineEdit(str(row[3])) q3Edit.setFixedWidth(150) q3Edit.setFont(QFont("Arial", 10)) q3Edit.setDisabled(True) q5Edit = QLineEdit(str(row[7])) q5Edit.setFixedWidth(400) q5Edit.setFont(QFont("Arial", 10)) q5Edit.setDisabled(True) grid = QGridLayout() grid.setSpacing(20) lbl1 = QLabel('Clusternummer') lbl1.setAlignment(Qt.AlignRight | Qt.AlignVCenter) grid.addWidget(lbl1, 1, 0) lbl2 = QLabel(clusternr) grid.addWidget(lbl2, 1, 1) lbl9 = QLabel('Regelnummer: ') lbl9.setAlignment(Qt.AlignRight | Qt.AlignVCenter) grid.addWidget(lbl9, 1, 2) lbl10 = QLabel(str(regel)) grid.addWidget(lbl10, 1, 3) lbl3 = QLabel('Clusteromschrijving') lbl3.setAlignment(Qt.AlignRight | Qt.AlignVCenter) grid.addWidget(lbl3, 2, 0) grid.addWidget(q1Edit, 2, 1, 1, 3) lbl8 = QLabel('Artikelnummer') lbl8.setAlignment(Qt.AlignRight | Qt.AlignVCenter) grid.addWidget(lbl8, 3, 0) grid.addWidget(q6Edit, 3, 1) lbl4 = QLabel('Artikelprijs') lbl4.setAlignment(Qt.AlignRight | Qt.AlignVCenter) grid.addWidget(lbl4, 4, 0) grid.addWidget(q2Edit, 4, 1) lbl5 = QLabel('Hoeveelheid\nArtikelen per cluster') lbl5.setAlignment(Qt.AlignRight | Qt.AlignVCenter) grid.addWidget(lbl5, 5, 0) grid.addWidget(q3Edit, 5, 1) lbl7 = QLabel('Artikelomschrijving') lbl7.setAlignment(Qt.AlignRight | Qt.AlignVCenter) grid.addWidget(lbl7, 6, 0) grid.addWidget(q5Edit, 6, 1, 1, 3) lbl = QLabel() pixmap = QPixmap('./images/logos/verbinding.jpg') lbl.setPixmap(pixmap) grid.addWidget(lbl, 0, 0, 1, 2) logo = QLabel() pixmap = QPixmap('./images/logos/logo.jpg') logo.setPixmap(pixmap) grid.addWidget(logo, 0, 3, 1, 1, Qt.AlignRight) grid.addWidget( QLabel( '\u00A9 2017 all rights reserved [email protected]' ), 8, 0, 1, 4, Qt.AlignCenter) self.setLayout(grid) self.setGeometry(500, 300, 150, 150) applyBtn = QPushButton('Regels') applyBtn.clicked.connect(self.accept) grid.addWidget(applyBtn, 7, 3, 1, 1, Qt.AlignRight) applyBtn.setFont(QFont("Arial", 10)) applyBtn.setFixedWidth(100) applyBtn.setStyleSheet( "color: black; background-color: gainsboro") mainWin = MainWindow() mainWin.exec_() win = MyWindow(data_list, header) win.exec_() zoeken(m_email)