def Inkooporder(m_email, rplev, mregel): mlevnr = int(rplev[0]) mbedrnaam = rplev[1] mrechtsvorm = rplev[2] mpostcode = rplev[3] mhuisnr = int(rplev[4]) if rplev[5]: mtoev = rplev[5] else: mtoev='' import postcode mstrplts = postcode.checkpostcode(mpostcode, mhuisnr) mstraat = mstrplts[0] mplaats = mstrplts[1] minkordnr = bepaalInkoopOrdernr(mregel) return(minkordnr, mlevnr, mbedrnaam, mrechtsvorm, mstraat, mhuisnr,\ mtoev, mpostcode, mplaats)
def Inkooporder(m_email): rplev = zoekLeverancier(m_email) if rplev: mlevnr = int(rplev[0]) mwerknr = int(rplev[6]) else: check = 0 return (check) mbedrnaam = rplev[1] mrechtsvorm = rplev[2] mpostcode = rplev[3] mhuisnr = int(rplev[4]) if rplev[5]: mtoev = rplev[5] else: mtoev = '' import postcode mstrplts = postcode.checkpostcode(mpostcode, mhuisnr) mstraat = mstrplts[0] mplaats = mstrplts[1] minkordnr = bepaalInkoopOrdernr() return(minkordnr, mlevnr, mbedrnaam, mrechtsvorm, mstraat, mhuisnr,\ mtoev, mpostcode, mplaats, mwerknr)
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_()
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 invBedrijf(m_email): class Widget(QDialog): def __init__(self, parent=None): super(Widget, self).__init__(parent) self.setWindowTitle("Invoer Bedrijf Verkoop") self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setFont(QFont('Arial', 10)) self.Bedrijfsnaam = QLabel() q3Edit = QLineEdit() q3Edit.setFixedWidth(540) q3Edit.setFont(QFont("Arial", 10)) q3Edit.textChanged.connect(self.q3Changed) reg_ex = QRegExp("^.{1,50}$") input_validator = QRegExpValidator(reg_ex, q3Edit) q3Edit.setValidator(input_validator) self.Afdeling = QLabel() q14Edit = QLineEdit() q14Edit.setFixedWidth(540) q14Edit.setFont(QFont("Arial", 10)) q14Edit.textChanged.connect(self.q14Changed) reg_ex = QRegExp("^.{1,50}$") input_validator = QRegExpValidator(reg_ex, q14Edit) q14Edit.setValidator(input_validator) self.Rechtsvorm = QLabel() q5Edit = QLineEdit() q5Edit.setFixedWidth(200) q5Edit.setFont(QFont("Arial", 10)) q5Edit.textChanged.connect(self.q5Changed) reg_ex = QRegExp("^.{1,30}$") input_validator = QRegExpValidator(reg_ex, q5Edit) q5Edit.setValidator(input_validator) self.BTWnummer = QLabel() q2Edit = QLineEdit() q2Edit.setFixedWidth(170) q2Edit.setText("") q2Edit.setFont(QFont("Arial", 10)) q2Edit.textChanged.connect(self.q2Changed) reg_ex = QRegExp("^[NLnl]{2}[0-9]{9}[Bb]{1}[0-9]{2}$") input_validator = QRegExpValidator(reg_ex, q2Edit) q2Edit.setValidator(input_validator) self.KvKnummer = QLabel() q4Edit = QLineEdit() q4Edit.setFixedWidth(120) q4Edit.setFont(QFont("Arial", 10)) q4Edit.textChanged.connect(self.q4Changed) reg_ex = QRegExp("^[0-9]{8}$") input_validator = QRegExpValidator(reg_ex, q4Edit) q4Edit.setValidator(input_validator) self.Postcode = QLabel() q6Edit = QLineEdit() q6Edit.setFixedWidth(80) font = QFont("Arial", 10) font.setCapitalization(QFont.AllUppercase) q6Edit.setFont(font) q6Edit.textChanged.connect(self.q6Changed) reg_ex = QRegExp("^[0-9]{4}[A-Za-z]{2}$") input_validator = QRegExpValidator(reg_ex, q6Edit) q6Edit.setValidator(input_validator) self.Huisnummer = QLabel() q7Edit = QLineEdit() 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) reg_ex = QRegExp("^[A-Za-z0-9-]{0,10}") input_validator = QRegExpValidator(reg_ex, q8Edit) q8Edit.setValidator(input_validator) self.Telefoonnr = QLabel() q12Edit = QLineEdit() q12Edit.setFixedWidth(120) q12Edit.setFont(QFont("Arial", 10)) q12Edit.textChanged.connect(self.q12Changed) reg_ex = QRegExp("^[0]{1}[0-9]{9}$") input_validator = QRegExpValidator(reg_ex, q12Edit) q12Edit.setValidator(input_validator) self.Bedrijfsnummer = QLabel() q13Edit = QLineEdit() q13Edit.setText(str(bepaalKoper())) q13Edit.setAlignment(Qt.AlignRight) q13Edit.setStyleSheet("color: black") q13Edit.setFixedWidth(110) q13Edit.setDisabled(True) q13Edit.setFont(QFont("Arial", 10)) q13Edit.textChanged.connect(self.q13Changed) 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, 2, Qt.AlignRight) self.setFont(QFont('Arial', 10)) grid.addWidget(QLabel('Nieuwe Bedrijf-Verkoop invoeren'), 0, 1, 1, 2, Qt.AlignCenter) grid.addWidget(QLabel(' *'), 1, 0) grid.addWidget(QLabel('Verplichte velden'), 1, 1) grid.addWidget(QLabel('Bedrijfsnaam *'), 2, 0) grid.addWidget(q3Edit, 2, 1, 1, 3) grid.addWidget(QLabel('Afdelingsnaam/Kamer/\nKontaktpersoon'), 3, 0) grid.addWidget(q14Edit, 3, 1, 1, 3) grid.addWidget(QLabel('Rechtsvorm *'), 4, 0) grid.addWidget(q5Edit, 4, 1) grid.addWidget(QLabel('BTWnummer *'), 4, 1, 1, 1, Qt.AlignRight) grid.addWidget(q2Edit, 4, 2) grid.addWidget(QLabel('KvKnummer *'), 5, 0) grid.addWidget(q4Edit, 5, 1) grid.addWidget(QLabel('Postcode *'), 5, 1, 1, 1, Qt.AlignRight) grid.addWidget(q6Edit, 5, 2) 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('Telefoonnummer *'), 7, 0) grid.addWidget(q12Edit, 7, 1) grid.addWidget(QLabel('Bedrijf-Verkoopnummer'), 8, 0) grid.addWidget(q13Edit, 8, 1) grid.addWidget( QLabel('\u00A9 2017 all rights reserved [email protected]'), 9, 1) cancelBtn = QPushButton('Sluiten') cancelBtn.clicked.connect(lambda: windowSluit(self, m_email)) applyBtn = QPushButton('Invoeren') applyBtn.clicked.connect(self.accept) grid.addWidget(applyBtn, 8, 2, 1, 1, Qt.AlignRight) applyBtn.setFont(QFont("Arial", 10)) applyBtn.setFixedWidth(100) applyBtn.setStyleSheet( "color: black; background-color: gainsboro") grid.addWidget(cancelBtn, 7, 2, 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, 150, 150) def q3Changed(self, text): self.Bedrijfsnaam.setText(text) def q14Changed(self, text): self.Afdeling.setText(text) def q5Changed(self, text): self.Rechtsvorm.setText(text) def q2Changed(self, text): self.BTWnummer.setText(text) def q4Changed(self, text): self.KvKnummer.setText(text) def q6Changed(self, text): self.Postcode.setText(text) def q7Changed(self, text): self.Huisnummer.setText(text) def q8Changed(self, text): self.Toevoeging.setText(text) def q12Changed(self, text): self.Telefoonnr.setText(text) def q13Changed(self, text): self.Bedrijfsnummer.setText(text) def returnBedrijfsnaam(self): return self.Bedrijfsnaam.text() def returnAfdeling(self): return self.Afdeling.text() def returnRechtsvorm(self): return self.Rechtsvorm.text() def returnBTWnummer(self): return self.BTWnummer.text() def returnKvKnummer(self): return self.KvKnummer.text() def returnPostcode(self): return self.Postcode.text() def returnHuisnummer(self): return self.Huisnummer.text() def returnToevoeging(self): return self.Toevoeging.text() def returnTelefoonnummer(self): return self.Telefoonnr.text() def returnBedrijfsnummer(self): return self.Bedrijfsnummer.text() @staticmethod def getData(parent=None): dialog = Widget(parent) dialog.exec_() return [dialog.returnBedrijfsnaam(), dialog.returnRechtsvorm(),\ dialog.returnBTWnummer(), dialog.returnKvKnummer(),\ dialog.returnPostcode(), dialog.returnHuisnummer(),\ dialog.returnToevoeging(), dialog.returnTelefoonnummer(),\ dialog.returnBedrijfsnummer(),dialog.returnAfdeling()] window = Widget() data = window.getData() mbedrijfsnaam = (data[0]).title() mrechtsvorm = (data[1]).upper() if data[2] and len(data[2]) == 14: mbtwnr = data[2].upper() else: foutBtwnr() invBedrijf(m_email) if data[3] and len(data[3]) == 8: mkvknr = data[3] else: foutKvknr() invBedrijf(m_email) mpostcode = (data[4]).upper() mhuisnr = data[5] if not (mbedrijfsnaam and mrechtsvorm and mpostcode and mhuisnr): geenGegevens() invBedrijf(m_email) mhuisnr = int(mhuisnr) mtoev = (data[6]) if mtoev: mtoev = '-' + mtoev mtelnr = (data[7]) if data[7]: if len(mtelnr) < 10: foutTelnr() invBedrijf(m_email) else: geenGegevens() invBedrijf(m_email) mkopernr = (data[8]) if data[9]: mafd = data[9] else: mafd = '' import postcode if postcode.checkpostcode(mpostcode, mhuisnr): metadata = MetaData() kopers = Table('kopers', metadata, Column('koperID', Integer(), primary_key=True), Column('bedrijfsnaam', String), Column('rechtsvorm', String), Column('btwnummer', String), Column('kvknummer', String), Column('telnr', String(10)), Column('huisnummer', String(5), nullable=False), Column('postcode', String(6), nullable=False), Column('toevoeging', String), Column('afdeling', String)) engine = create_engine( 'postgresql+psycopg2://postgres@localhost/bisystem') conn = engine.connect() mkopernr = bepaalKoper() conn = engine.connect() s = select([kopers.c.koperID]).\ where(kopers.c.koperID == mkopernr) result = conn.execute(s).first() if result: conn.close() koperBestaat() invBedrijf(m_email) else: inskop = kopers.insert().values(koperID=mkopernr, bedrijfsnaam=mbedrijfsnaam, rechtsvorm=mrechtsvorm, btwnummer=mbtwnr, kvknummer=mkvknr, postcode=mpostcode, huisnummer=mhuisnr, toevoeging=mtoev, telnr=mtelnr, afdeling=mafd) conn.execute(inskop) conn.close() Invoer() else: postcode.foutPostc() invBedrijf(m_email)
def inkoopOrder(m_email, minkordernr, mregel): metadata = MetaData() 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), Column('postcode', String), Column('huisnummer', String), Column('toevoeging', String)) 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)) engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem') conn = engine.connect() sel1 = select([orders_inkoop]).where(orders_inkoop.c.orderinkoopID == minkordernr) sel2 = select([leveranciers]).where(and_(leveranciers.c.leverancierID==\ orders_inkoop.c.leverancierID, orders_inkoop.c.orderinkoopID == minkordernr)) sel3 = select([orders_inkoop_artikelen]).where(orders_inkoop_artikelen.\ c.orderinkoopID == minkordernr).order_by(orders_inkoop_artikelen.c.regel) rp1 = conn.execute(sel1).first() rp2 = conn.execute(sel2).first() if conn.execute(sel3).fetchone(): rp3 = conn.execute(sel3) else: foutCombinatie() zoekInkooporder(m_email, minkordernr, mregel) mpostcode = rp2[3] mhuisnr = int(rp2[4]) import postcode mstrpls = postcode.checkpostcode(mpostcode, mhuisnr) mstraat = mstrpls[0] mplaats = mstrpls[1] class Widget(QDialog): def __init__(self, parent=None): super(Widget, self).__init__(parent) self.setWindowTitle("Inkooporder artikelen Wijzigen.") self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setFont(QFont('Arial', 10)) self.Inkoopordernummer = QLabel() inkordEdit = QLineEdit(str(minkordernr)) inkordEdit.setFixedWidth(110) inkordEdit.setAlignment(Qt.AlignRight) inkordEdit.setDisabled(True) inkordEdit.setStyleSheet("QLineEdit { font-size: 10pt; font-family: Arial; color: black }") inkordEdit.textChanged.connect(self.inkordChanged) self.Besteldatum = QLabel() q3Edit = QLineEdit() datum = str(datetime.datetime.now()) q3Edit.setText((datum[8:10]+'-'+datum[5:8]+datum[0:4])) q3Edit.setDisabled(True) q3Edit.setFixedWidth(110) q3Edit.setStyleSheet("QLineEdit { font-size: 10pt; font-family: Arial; color: black }") self.Goedgekeurd = QLabel() q4Edit = QLineEdit(rp1[3]) q4Edit.setCursorPosition(0) q4Edit.setFixedWidth(110) 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.Betaald = QLabel() q5Edit = QLineEdit(rp1[4]) q5Edit.setCursorPosition(0) q5Edit.setFixedWidth(110) 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.Afgemeld = QLabel() q6Edit = QLineEdit(rp1[5]) q6Edit.setCursorPosition(0) q6Edit.setFixedWidth(110) q6Edit.setFont(QFont("Arial",10)) q6Edit.textChanged.connect(self.q6Changed) 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, q6Edit) q6Edit.setValidator(input_validator) grid = QGridLayout() grid.setSpacing(20) lbl = QLabel() pixmap = QPixmap('./images/logos/verbinding.jpg') lbl.setPixmap(pixmap) grid.addWidget(lbl , 1, 0) self.setFont(QFont('Arial', 10)) grid.addWidget(QLabel('Bestelling voor\nLeverancier: '+str(rp2[0])+\ ',\n'+rp2[1]+' '+rp2[2]+',\n'+mstraat+' '+str(rp2[4])+\ rp2[5]+',\n'+rp2[3]+' '+mplaats+'.'), 1, 1, 1 , 2) logo = QLabel() pixmap = QPixmap('./images/logos/logo.jpg') logo.setPixmap(pixmap) grid.addWidget(logo , 5, 2, 1 ,1, Qt.AlignCenter) grid.addWidget(QLabel('Inkoopordernummer'), 4, 0, 1, 1, Qt.AlignRight) grid.addWidget(inkordEdit, 4, 1) grid.addWidget(QLabel('Besteldatum'), 5, 0, 1, 1, Qt.AlignRight) grid.addWidget(q3Edit, 5, 1) grid.addWidget(QLabel('Goedgekeurd jjjj-mm-dd'), 6, 0, 1, 1, Qt.AlignRight) grid.addWidget(q4Edit, 6, 1) grid.addWidget(QLabel('Betaald jjjj-mm-dd'), 7, 0, 1, 1, Qt.AlignRight) grid.addWidget(q5Edit, 7, 1) grid.addWidget(QLabel('Afgemeld jjjj-mm-dd'), 8, 0, 1, 1, Qt.AlignRight) grid.addWidget(q6Edit, 8, 1) grid.addWidget(QLabel('\u00A9 2017 all rights reserved [email protected]'), 9, 0, 1, 3, Qt.AlignCenter) applyBtn = QPushButton('Bestelregels') applyBtn.clicked.connect(self.accept) cancelBtn = QPushButton('Sluiten') cancelBtn.clicked.connect(lambda: closeOrder(m_email, minkordernr, 0, self)) grid.addWidget(applyBtn, 8, 2, 1 , 1, Qt.AlignRight) applyBtn.setFont(QFont("Arial",10)) applyBtn.setFixedWidth(120) applyBtn.setStyleSheet("color: black; background-color: gainsboro") grid.addWidget(cancelBtn, 7, 2, 1 , 1, Qt.AlignRight) cancelBtn.setFont(QFont("Arial",10)) cancelBtn.setFixedWidth(120) cancelBtn.setStyleSheet("color: black; background-color: gainsboro") self.setLayout(grid) self.setGeometry(500, 200, 350, 300) def inkordChanged(self, text): self.Inkoopordernummer.setText(text) def q3Changed(self, text): self.Besteldatum.setText(text) def q4Changed(self, text): self.Goedgekeurd.setText(text) def q5Changed(self, text): self.Betaald.setText(text) def q6Changed(self, text): self.Afgemeld.setText(text) def returnInkoopordernummer(self): return self.Inkoopordernummer.text() def returnBesteldatum(self): return self.Besteldatum.text() def returnGoedgekeurd(self): return self.Goedgekeurd.text() def returnBetaald(self): return self.Betaald.text() def returnAfgemeld(self): return self.Afgemeld.text() @staticmethod def getData(parent=None): dialog = Widget(parent) dialog.exec_() return [dialog.returnGoedgekeurd(), dialog.returnBetaald(),\ dialog.returnAfgemeld()] window = Widget() data = window.getData() if data[0]: mgoedgek = data[0] else: mgoedgek = rp1[3] if data[1]: mbetaald = data[1] else: mbetaald = rp1[4] if data[2]: mafgemeld = data[2] else: mafgemeld = rp1[5] sel1 = select([orders_inkoop]).where(orders_inkoop.c.orderinkoopID == minkordernr) rp1 = conn.execute(sel1).first() u = update(orders_inkoop).where(orders_inkoop.c.orderinkoopID == minkordernr).\ values(goedgekeurd = mgoedgek, betaald = mbetaald, afgemeld = mafgemeld) conn.execute(u) conn.close() mregel = 0 inkoopRegels(m_email, rp1, rp2, rp3, mstraat, mplaats, mregel)
def artAfroep(idx): martnr = idx.data() if idx.column() == 0: class Widget(QDialog): def __init__(self, parent=None): super(Widget, self).__init__(parent) self.setWindowTitle("Materialen afroepen") self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setFont(QFont('Arial', 10)) self.Werkorder = QLabel() zkwerknEdit = QLineEdit(str(koppelnr)) zkwerknEdit.setFixedWidth(100) zkwerknEdit.setFont(QFont("Arial",10)) zkwerknEdit.setDisabled(True) self.Artikelnummer = QLabel() artEdit = QLineEdit(str(martnr)) artEdit.setFixedWidth(100) artEdit.setFont(QFont("Arial",10)) artEdit.setDisabled(True) self.Hoeveelheid = QLabel() hoevEdit = QLineEdit() hoevEdit.setFixedWidth(80) hoevEdit.setFont(QFont("Arial",10)) hoevEdit.textChanged.connect(self.hoevChanged) reg_ex = QRegExp("^[-]{0,1}[0-9]{0,10}[.]{0,1}[0-9]{0,4}$") input_validator = QRegExpValidator(reg_ex, hoevEdit) hoevEdit.setValidator(input_validator) self.Postcode = QLabel() pcEdit = QLineEdit() pcEdit.setFixedWidth(80) font = QFont("Arial",10) font.setCapitalization(QFont.AllUppercase) pcEdit.setFont(font) pcEdit.textChanged.connect(self.pcChanged) reg_ex = QRegExp("^[0-9]{4}[A-Za-z]{2}$") input_validator = QRegExpValidator(reg_ex, pcEdit) pcEdit.setValidator(input_validator) self.Huisnummer = QLabel() hnoEdit = QLineEdit() hnoEdit.setFixedWidth(60) hnoEdit.setFont(QFont("Arial",10)) hnoEdit.textChanged.connect(self.hnoChanged) reg_ex = QRegExp("^[0-9]{1,5}$") input_validator = QRegExpValidator(reg_ex, hnoEdit) hnoEdit.setValidator(input_validator) self.Toevoeging = QLabel() toevEdit = QLineEdit() toevEdit.setFixedWidth(50) toevEdit.setFont(QFont("Arial",10)) toevEdit.textChanged.connect(self.toevChanged) reg_ex = QRegExp("^.{0,20}$") input_validator = QRegExpValidator(reg_ex, toevEdit) toevEdit.setValidator(input_validator) self.Alternatief = QLabel() altEdit = QLineEdit() altEdit.setFixedWidth(280) altEdit.setFont(QFont("Arial",10)) altEdit.textChanged.connect(self.altChanged) reg_ex = QRegExp("^.{0,30}$") input_validator = QRegExpValidator(reg_ex, altEdit) altEdit.setValidator(input_validator) grid = QGridLayout() grid.setSpacing(20) lbl = QLabel() pixmap = QPixmap('./images/logos/verbinding.jpg') lbl.setPixmap(pixmap) grid.addWidget(lbl ,0, 0) lbl4 = QLabel('Afroepen materialen samenstellen') lbl4.setStyleSheet("font: 12pt Comic Sans MS") grid.addWidget(lbl4, 0, 1) logo = QLabel() pixmap = QPixmap('./images/logos/logo.jpg') logo.setPixmap(pixmap) grid.addWidget(logo , 0, 2, 1, 1, Qt.AlignRight) grid.addWidget(QLabel('\u00A9 2017 all rights reserved [email protected]'), 9, 0, 1, 3, Qt.AlignCenter) 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) lbl3 = QLabel('Afroep') lbl3.setAlignment(Qt.AlignRight | Qt.AlignVCenter) grid.addWidget(lbl3, 3, 0) grid.addWidget(hoevEdit, 3 ,1) cBox = QCheckBox('Meerwerk') cBox.stateChanged.connect(self.cBoxChanged) grid.addWidget(cBox, 3, 2) lbl8 = QLabel('Afleveradres') lbl8.setStyleSheet("font: 12pt Comic Sans MS") grid.addWidget(lbl8, 4, 1) lbl4 = QLabel('Postcode') lbl4.setAlignment(Qt.AlignRight | Qt.AlignVCenter) grid.addWidget(lbl4, 5, 0) grid.addWidget(pcEdit, 5 , 1) lbl5 = QLabel('Huisnummer') lbl5.setAlignment(Qt.AlignRight | Qt.AlignVCenter) grid.addWidget(lbl5, 6, 0) grid.addWidget(hnoEdit, 6 , 1) lbl6 = QLabel('Toevoeging') grid.addWidget(lbl6, 6, 0, 1, 2, Qt.AlignRight) grid.addWidget(toevEdit, 6, 2) lbl7 = QLabel('Alternatief adres') lbl7.setAlignment(Qt.AlignRight | Qt.AlignVCenter) grid.addWidget(lbl7, 7, 0) grid.addWidget(altEdit, 7 , 1, 1, 3) self.setLayout(grid) self.setGeometry(500, 300, 150, 150) applyBtn = QPushButton('Muteren') applyBtn.setStyleSheet("color: black; background-color: gainsboro") applyBtn.clicked.connect(self.accept) grid.addWidget(applyBtn, 8, 2, 1 , 1, Qt.AlignRight) applyBtn.setFont(QFont("Arial",10)) applyBtn.setFixedWidth(100) sluitBtn = QPushButton('Sluiten') sluitBtn.setStyleSheet("color: black; background-color: gainsboro") sluitBtn.clicked.connect(self.close) grid.addWidget(sluitBtn, 8, 0, 1, 3, Qt.AlignCenter) sluitBtn.setFont(QFont("Arial",10)) sluitBtn.setFixedWidth(100) opvrBtn = QPushButton('Raaplijst') opvrBtn.setStyleSheet("color: black; background-color: gainsboro") opvrBtn.clicked.connect(lambda: raapLijst(koppelnr)) grid.addWidget(opvrBtn, 8, 1, 1, 1, Qt.AlignRight) opvrBtn.setFont(QFont("Arial",10)) opvrBtn.setFixedWidth(100) def hoevChanged(self,text): self.Hoeveelheid.setText(text) state = False def cBoxChanged(self, state): if state == Qt.Checked: self.state = True def pcChanged(self,text): self.Postcode.setText(text) def hnoChanged(self,text): self.Huisnummer.setText(text) def altChanged(self,text): self.Alternatief.setText(text) def toevChanged(self,text): self.Toevoeging.setText(text) def returnhoev(self): return self.Hoeveelheid.text() def returncBox(self): return self.state def returnpc(self): return self.Postcode.text() def returnhno(self): return self.Huisnummer.text() def returntoev(self): return self.Toevoeging.text() def returnalt(self): return self.Alternatief.text() @staticmethod def getData(parent=None): dialog = Widget(parent) dialog.exec_() return [dialog.returnhoev(), dialog.returncBox(), dialog.returnpc(),\ dialog.returnhno(), dialog.returntoev(), dialog.returnalt()] window = Widget() data = window.getData() mhoev = 0 martikelnr = int(martnr) metadata = MetaData() if str(koppelnr)[0] == '7': 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 == koppelnr) rpwerk = con.execute(selwerk).first() materiaallijsten = Table('materiaallijsten', metadata, Column('matlijstID', Integer, primary_key=True), Column('icalculatie', Integer), Column('hoeveelheid', Float), Column('artikelID', None, ForeignKey('artikelen.artikelID')), Column('resterend', Float), Column('afroep', Float)) artikelen = Table('artikelen', metadata, Column('artikelID', Integer, primary_key=True), Column('artikelomschrijving', String)) icalculaties = Table('icalculaties', metadata, Column('icalcID', Integer, primary_key=True), Column('icalculatie', Integer), Column('koppelnummer', Integer)) 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('straat', String), Column('woonplaats', String)) if rpwerk[1] == 'H': werkGereed() return() mlevdat = str(datetime.datetime.now()+datetime.timedelta(days=7))[0:10] engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem') con = engine.connect() sel = select([icalculaties]).where(icalculaties.c.koppelnummer == koppelnr) rpsel = con.execute(sel).first() elif str(koppelnr)[0] == '8': werken = Table('werken', metadata, Column('werknummerID', Integer, primary_key=True), Column('voortgangstatus', String)) engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem') con = engine.connect() selwerk = select([werken]).where(werken.c.werknummerID == koppelnr) rpwerk = con.execute(selwerk).first() materiaallijsten = Table('materiaallijsten', metadata, Column('matlijstID', Integer, primary_key=True), Column('calculatie', Integer), Column('hoeveelheid', Float), Column('artikelID', None, ForeignKey('artikelen.artikelID')), Column('resterend', Float), Column('afroep', Float)) artikelen = Table('artikelen', metadata, Column('artikelID', Integer, primary_key=True), Column('artikelomschrijving', String)) calculaties = Table('calculaties', metadata, Column('calcID', Integer, primary_key=True), Column('calculatie', Integer), Column('koppelnummer', Integer)) 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('straat', String), Column('woonplaats', String)) if rpwerk[1] == 'H': werkGereed() return() mlevdat = str(datetime.datetime.now()+datetime.timedelta(days=7))[0:10] engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem') con = engine.connect() sel = select([calculaties]).where(calculaties.c.koppelnummer == koppelnr) rpsel = con.execute(sel).first() if data[0]: mhoev = float(data[0]) else: return() if data[1]: mmmstatus = True else: mmmstatus = False if data[3]: mhuisnr = int(data[3]) else: mhuisnr = 0 if data[2] and data[3]: mpostcode = data[2].upper() mstrpl = checkpostcode(mpostcode, mhuisnr) if mstrpl[0]: mstraat = mstrpl[0] mplaats = mstrpl[1] else: mpostcode = '' mhuisnr = '' mstraat = '' mplaats = '' foutPostcode() return() else: mpostcode = '' mhuisnr = '' mstraat = '' mplaats = '' if data[4]: mtoev = data[4] else: mtoev = '' if data[5]: maltern = data[5] else: maltern = '' if mhoev != 0: if str(koppelnr)[0] == '7': selmatl = select([materiaallijsten]).where(and_(materiaallijsten.c.icalculatie ==\ rpsel[1], materiaallijsten.c.artikelID == artikelen.c.artikelID,\ materiaallijsten.c.artikelID == martikelnr)) rpmatl = con.execute(selmatl).first() mresterend = rpmatl[4] if mresterend < mhoev: class Button(QDialog): def __init__(self, parent=None): super(Button, self).__init__(parent) msgBox = QMessageBox() msgBox.setWindowTitle("Artikelafroep") msgBox.setWindowIcon(QIcon('./images/logos/logo.jpg')) msgBox.setStyleSheet("color: black; background-color: gainsboro; font-size: 16px;height: 20px; width: 50px") msgBox.setText('De afroep '+str(mhoev)+' is groter dan nog te leveren '+str(round(mresterend,3))) msgBox.addButton(QPushButton('Doorgaan'), QMessageBox.YesRole) msgBox.addButton(QPushButton('Stoppen'), QMessageBox.RejectRole) retour = msgBox.exec_() if retour: artAfroep(idx) else: updmatl = update(materiaallijsten).where(and_(materiaallijsten.c.icalculatie ==\ rpsel[1], materiaallijsten.c.artikelID == artikelen.c.artikelID,\ materiaallijsten.c.artikelID == martikelnr)).values(afroep = mhoev,\ resterend=materiaallijsten.c.resterend - round(mhoev,4)) con.execute(updmatl) mlijstnr=(con.execute(select([func.max(raaplijst.c.lijstID, type_=Integer)\ .label('mlijstnr')])).scalar()) mlijstnr += 1 insrl = insert(raaplijst).values(lijstID = mlijstnr, artikelID = martikelnr,\ werkorder = koppelnr, afroep = mhoev, leverdatum = mlevdat,\ meerwerk = mmmstatus, postcode = mpostcode, huisnummer = mhuisnr,\ toevoeging = mtoev, alternatief = maltern, straat = mstraat, woonplaats = mplaats) con.execute(insrl) invoerOK() btn = Button() btn.show() else: updmatl = update(materiaallijsten).where(and_(materiaallijsten.c.icalculatie ==\ rpsel[1], materiaallijsten.c.artikelID == artikelen.c.artikelID,\ materiaallijsten.c.artikelID == martikelnr)).values(afroep = mhoev,\ resterend=materiaallijsten.c.resterend - round(mhoev,4)) con.execute(updmatl) mlijstnr=(con.execute(select([func.max(raaplijst.c.lijstID, type_=Integer)\ .label('mlijstnr')])).scalar()) mlijstnr += 1 insrl = insert(raaplijst).values(lijstID = mlijstnr, artikelID = martikelnr,\ werkorder = koppelnr, afroep = mhoev, leverdatum = mlevdat,\ meerwerk = mmmstatus, postcode = mpostcode, huisnummer = mhuisnr,\ toevoeging = mtoev, alternatief = maltern, straat = mstraat, woonplaats = mplaats) con.execute(insrl) invoerOK() elif str(koppelnr)[0] == '8': selmatl = select([materiaallijsten]).where(and_(materiaallijsten.c.calculatie ==\ rpsel[1], materiaallijsten.c.artikelID == artikelen.c.artikelID,\ materiaallijsten.c.artikelID == martikelnr)) rpmatl = con.execute(selmatl).first() mresterend = rpmatl[4] if mresterend < mhoev: class Button(QDialog): def __init__(self, parent=None): super(Button, self).__init__(parent) msgBox = QMessageBox() msgBox.setWindowTitle("Artikelafroep") msgBox.setWindowIcon(QIcon('./images/logos/logo.jpg')) msgBox.setStyleSheet("color: black; background-color: gainsboro; font-size: 10pt") msgBox.setText('De afroep '+str(mhoev)+' is groter dan nog te leveren '+str(round(mresterend,3))) msgBox.addButton(QPushButton('Doorgaan'), QMessageBox.YesRole) msgBox.addButton(QPushButton('Stoppen'), QMessageBox.RejectRole) retour = msgBox.exec_() if retour: artAfroep(idx) else: updmatl = update(materiaallijsten).where(and_(materiaallijsten.c.calculatie ==\ rpsel[1], materiaallijsten.c.artikelID == artikelen.c.artikelID,\ materiaallijsten.c.artikelID == martikelnr)).values(afroep = mhoev,\ resterend=materiaallijsten.c.resterend - round(mhoev,4)) con.execute(updmatl) mlijstnr=(con.execute(select([func.max(raaplijst.c.lijstID, type_=Integer)\ .label('mlijstnr')])).scalar()) mlijstnr += 1 insrl = insert(raaplijst).values(lijstID = mlijstnr, artikelID = martikelnr,\ werkorder = koppelnr, afroep = mhoev, leverdatum = mlevdat,\ meerwerk = mmmstatus, postcode = mpostcode, huisnummer = mhuisnr,\ toevoeging = mtoev, alternatief = maltern, straat = mstraat, woonplaats = mplaats) con.execute(insrl) invoerOK() btn = Button() btn.show() else: updmatl = update(materiaallijsten).where(and_(materiaallijsten.c.calculatie ==\ rpsel[1], materiaallijsten.c.artikelID == artikelen.c.artikelID,\ materiaallijsten.c.artikelID == martikelnr)).values(afroep = mhoev,\ resterend=materiaallijsten.c.resterend - round(mhoev,4)) con.execute(updmatl) mlijstnr=(con.execute(select([func.max(raaplijst.c.lijstID, type_=Integer)\ .label('mlijstnr')])).scalar()) mlijstnr += 1 insrl = insert(raaplijst).values(lijstID = mlijstnr, artikelID = martikelnr,\ werkorder = koppelnr, afroep = mhoev, leverdatum = mlevdat,\ meerwerk = mmmstatus, postcode = mpostcode, huisnummer = mhuisnr,\ toevoeging = mtoev, alternatief = maltern, straat = mstraat, woonplaats = mplaats) con.execute(insrl) invoerOK()
def opvraagAfroep(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)) if str(koppelnr)[0] == '7': materiaallijsten = Table('materiaallijsten', metadata, Column('artikelID', Integer), Column('icalculatie', Integer), Column('afroep', Float), Column('resterend', Float)) icalculaties = Table('icalculaties', metadata, Column('icalculatie', Integer), Column('koppelnummer', Integer)) engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem') con = engine.connect() selmc = select([raaplijst, materiaallijsten,icalculaties]).where(and_(raaplijst\ .c.lijstID == mlijstnr, raaplijst.c.werkorder == icalculaties.c.koppelnummer,\ icalculaties.c.icalculatie == materiaallijsten.c.icalculatie,\ raaplijst.c.artikelID == materiaallijsten.c.artikelID)) rpmc = con.execute(selmc).first() elif str(koppelnr)[0] == '8': materiaallijsten = Table('materiaallijsten', metadata, Column('artikelID', Integer), Column('calculatie', Integer), Column('afroep', Float), Column('resterend', Float)) calculaties = Table('calculaties', metadata, Column('calculatie', Integer), Column('koppelnummer', Integer)) engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem') con = engine.connect() selmc = select([raaplijst, materiaallijsten,calculaties]).where(and_(raaplijst\ .c.lijstID == mlijstnr, raaplijst.c.werkorder == calculaties.c.koppelnummer,\ calculaties.c.calculatie == materiaallijsten.c.calculatie,\ raaplijst.c.artikelID == materiaallijsten.c.artikelID)) rpmc = con.execute(selmc).first() engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem') con = engine.connect() selrl = select([raaplijst]).where(and_(raaplijst.c.lijstID==mlijstnr,\ raaplijst.c.werkorder == koppelnr)) rprl = con.execute(selrl).first() mresterend = rpmc[14] mmmstatus = rprl[6] mlever = rprl[4] mafroep = rprl[3] mgeleverd = rprl[5] mpostcode = rprl[7] mhuisnr = rprl[8] mtoev = rprl[9] maltern = rprl[10] if mhuisnr != '': mstrpl = checkpostcode(mpostcode,int(mhuisnr)) mstraat = mstrpl[0] mplaats = mstrpl[1] class Widget(QDialog): def __init__(self, parent=None): super(Widget, self).__init__(parent) self.setWindowTitle("Materiaalafroep opvragen") self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setFont(QFont('Arial', 10)) self.Werknummer = QLabel() zkwerknEdit = QLineEdit(str(koppelnr)) zkwerknEdit.setFixedWidth(100) zkwerknEdit.setFont(QFont("Arial",10)) zkwerknEdit.setDisabled(True) self.Artikelnummer = QLabel() artEdit = QLineEdit(str(rpmc[1])) artEdit.setFixedWidth(100) artEdit.setFont(QFont("Arial",10)) artEdit.setDisabled(True) self.Leverdatum = QLabel() leverEdit = QLineEdit(str(mlever)) leverEdit.setFixedWidth(100) leverEdit.setFont(QFont("Arial",10)) leverEdit.setDisabled(True) self.Afroep = QLabel() afroepEdit = QLineEdit(str(mafroep)) afroepEdit.setFixedWidth(80) afroepEdit.setFont(QFont("Arial",10)) afroepEdit.setDisabled(True) self.Geleverd = QLabel() geleverdEdit = QLineEdit(str(mgeleverd)) geleverdEdit.setFixedWidth(80) geleverdEdit.setFont(QFont("Arial",10)) geleverdEdit.setDisabled(True) self.Resterend = QLabel() restEdit = QLineEdit(str(mresterend)) restEdit.setFixedWidth(80) restEdit.setFont(QFont("Arial",10)) restEdit.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, 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) lbl3 = QLabel('Leverdatum') lbl3.setAlignment(Qt.AlignRight | Qt.AlignVCenter) grid.addWidget(lbl3, 3, 0) grid.addWidget(leverEdit, 3 , 1) lbl4 = QLabel('Afroep') lbl4.setAlignment(Qt.AlignRight | Qt.AlignVCenter) grid.addWidget(lbl4, 4, 0) grid.addWidget(afroepEdit, 4 , 1) if mmmstatus: grid.addWidget(QLabel('Meerwerk'), 4, 2) lbl5 = QLabel('Geleverd') lbl5.setAlignment(Qt.AlignRight | Qt.AlignVCenter) grid.addWidget(lbl5, 5, 0) grid.addWidget(geleverdEdit, 5 , 1) if mmmstatus: grid.addWidget(QLabel('Meerwerk'), 5, 2) lbl6 = QLabel('Resterend') lbl6.setAlignment(Qt.AlignRight | Qt.AlignVCenter) grid.addWidget(lbl6, 6, 0) grid.addWidget(restEdit, 6 , 1) if mpostcode and mhuisnr: grid.addWidget(QLabel('Afleveradres: '+mstraat+' '+str(mhuisnr)+' '+mtoev), 7, 0, 1 ,3) grid.addWidget(QLabel('Woonplaats: '+mpostcode+' '+mplaats), 8, 0, 1, 3) if maltern: grid.addWidget(QLabel('Alternatief adres: '+maltern), 9, 0, 1 ,3) self.setLayout(grid) self.setGeometry(500, 300, 150, 150) cancelBtn = QPushButton('Sluiten') cancelBtn.clicked.connect(self.accept) cancelBtn.setStyleSheet("color: black; background-color: gainsboro") grid.addWidget(cancelBtn,10, 2) cancelBtn.setFont(QFont("Arial",10)) cancelBtn.setFixedWidth(100) grid.addWidget(QLabel('\u00A9 2017 all rights reserved [email protected]'), 11, 0, 1, 3, Qt.AlignCenter) window = Widget() window.exec_() return(koppelnr)
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 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_()
def updateLeverancier(mlevnr, m_email): 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('huisnummer', String), Column('toevoeging', String), Column('postcode', String)) engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem') conn = engine.connect() sel = select([leveranciers]).\ where(leveranciers.c.leverancierID == mlevnr) rplev = conn.execute(sel).first() if rplev: mlevnr = rplev[0] mbedrnaam = rplev[1] mrechtsvorm = rplev[2] mbtwnr = rplev[3] mkvknr = rplev[4] mtelnr = rplev[5] mhuisnr = rplev[6] mhuisnr = int(mhuisnr) mtoev = rplev[7] mpostcode = rplev[8] 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("Wijzigen leverancier") self.setWindowIcon(QIcon('./images/logos/logo.jpg')) self.setFont(QFont('Arial', 10)) self.Bedrijfsnaam = QLabel() q3Edit = QLineEdit() q3Edit.setText(mbedrnaam) q3Edit.setFixedWidth(540) q3Edit.setFont(QFont("Arial", 10)) q3Edit.textChanged.connect(self.q3Changed) reg_ex = QRegExp("^[^0-9]{1,50}$") input_validator = QRegExpValidator(reg_ex, q3Edit) q3Edit.setValidator(input_validator) self.Rechtsvorm = QLabel() q5Edit = QLineEdit() q5Edit.setText(mrechtsvorm) q5Edit.setFixedWidth(100) q5Edit.setFont(QFont("Arial", 10)) q5Edit.textChanged.connect(self.q5Changed) reg_ex = QRegExp("^[^0-9]{1,30}$") input_validator = QRegExpValidator(reg_ex, q5Edit) q5Edit.setValidator(input_validator) self.BTWnummer = QLabel() q2Edit = QLineEdit() q2Edit.setText(str(mbtwnr)) q2Edit.setDisabled(True) q2Edit.setFixedWidth(170) q2Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q2Edit.textChanged.connect(self.q2Changed) reg_ex = QRegExp("^[A-Za-z]{2}[0-9]{9}[Bb]{1}[0-9]{2}$") input_validator = QRegExpValidator(reg_ex, q2Edit) q2Edit.setValidator(input_validator) self.KvKnummer = QLabel() q4Edit = QLineEdit() q4Edit.setText(str(mkvknr)) q4Edit.setFixedWidth(110) q4Edit.setDisabled(True) q4Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q4Edit.textChanged.connect(self.q4Changed) reg_ex = QRegExp("^[0-9]{8}$") input_validator = QRegExpValidator(reg_ex, q4Edit) q4Edit.setValidator(input_validator) self.Straat = QLabel() q1Edit = QLineEdit() q1Edit.setText(mstraat) q1Edit.setFixedWidth(500) q1Edit.setDisabled(True) q1Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q1Edit.textChanged.connect(self.q1Changed) self.Huisnummer = QLabel() q7Edit = QLineEdit() q7Edit.setText(str(mhuisnr)) q7Edit.setFixedWidth(60) q7Edit.setAlignment(Qt.AlignRight) 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() q6Edit = QLineEdit() q6Edit.setText(mpostcode) q6Edit.setFixedWidth(80) font = QFont("Arial", 10) font.setCapitalization(QFont.AllUppercase) q6Edit.setFont(font) q6Edit.textChanged.connect(self.q6Changed) reg_ex = QRegExp("^[0-9]{4}[A-Za-z]{2}$") input_validator = QRegExpValidator(reg_ex, q6Edit) q6Edit.setValidator(input_validator) self.Woonplaats = QLabel() q15Edit = QLineEdit() q15Edit.setText(mplaats) q15Edit.setFixedWidth(400) q15Edit.setDisabled(True) q15Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q15Edit.textChanged.connect(self.q15Changed) self.Telefoonnr = QLabel() q13Edit = QLineEdit('') q13Edit.setFixedWidth(120) q13Edit.setFont(QFont("Arial", 10)) q13Edit.textChanged.connect(self.q13Changed) q13Edit.setText(mtelnr) reg_ex = QRegExp("^[0]{1}[0-9]{9}$") input_validator = QRegExpValidator(reg_ex, q13Edit) q13Edit.setValidator(input_validator) self.Leveranciernummer = QLabel() q14Edit = QLineEdit() q14Edit.setText(str(mlevnr)) q14Edit.setAlignment(Qt.AlignRight) q14Edit.setFixedWidth(120) q14Edit.setDisabled(True) q14Edit.setStyleSheet( "QLineEdit { font-size: 10pt; font-family: Arial; color: black }" ) q14Edit.textChanged.connect(self.q14Changed) 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('Leverancier wijzigingen'), 0, 1) grid.addWidget(QLabel(' *'), 1, 0) grid.addWidget(QLabel('Verplichte velden'), 1, 1) grid.addWidget(QLabel('Bedrijfsnaam *'), 2, 0) grid.addWidget(q3Edit, 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'), 5, 0) grid.addWidget(q4Edit, 5, 1) grid.addWidget(QLabel('Straat'), 6, 0) grid.addWidget(q1Edit, 6, 1, 1, 2) grid.addWidget(QLabel('Huisnummer *'), 7, 0) grid.addWidget(q7Edit, 7, 1) grid.addWidget(QLabel('Toevoeging'), 7, 1, 1, 1, Qt.AlignRight) grid.addWidget(q8Edit, 7, 2) grid.addWidget(QLabel('Postcode Woonplaats *'), 8, 0) grid.addWidget(q6Edit, 8, 1) grid.addWidget(q15Edit, 8, 1, 1, 2, Qt.AlignRight) grid.addWidget(QLabel('Telefoonnummer *'), 9, 0) grid.addWidget(q13Edit, 9, 1) grid.addWidget(QLabel('Leveranciernummer'), 10, 0) grid.addWidget(q14Edit, 10, 1) grid.addWidget( QLabel('\u00A9 2017 all rights reserved [email protected]'), 11, 1, 1, 1) cancelBtn = QPushButton('Sluiten') cancelBtn.clicked.connect(lambda: winzlevSluit(self, m_email)) applyBtn = QPushButton('Wijzigen') applyBtn.clicked.connect(self.accept) grid.addWidget(applyBtn, 10, 2, 1, 1, Qt.AlignRight) applyBtn.setFont(QFont("Arial", 10)) applyBtn.setFixedWidth(100) applyBtn.setStyleSheet( "color: black; background-color: gainsboro") grid.addWidget(cancelBtn, 9, 2, 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, 150, 150) def q3Changed(self, text): self.Bedrijfsnaam.setText(text) def q5Changed(self, text): self.Rechtsvorm.setText(text) def q2Changed(self, text): self.BTWnummer.setText(text) def q4Changed(self, text): self.KvKnummer.setText(text) def q1Changed(self, text): self.Straat.setText(text) def q7Changed(self, text): self.Huisnummer.setText(text) def q8Changed(self, text): self.Toevoeging.setText(text) def q6Changed(self, text): self.Postcode.setText(text) def q15Changed(self, text): self.Woonplaats.setText(text) def q13Changed(self, text): self.Telefoonnr.setText(text) def q14Changed(self, text): self.Leveranciernummer.setText(text) def returnBedrijfsnaam(self): return self.Bedrijfsnaam.text() def returnRechtsvorm(self): return self.Rechtsvorm.text() def returnBTWnummer(self): return self.BTWnummer.text() def returnKvKnummer(self): return self.KvKnummer.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 returnTelefoonnummer(self): return self.Telefoonnr.text() def returnLeveranciernummer(self): return self.Leveranciernummer.text() @staticmethod def getData(parent=None): dialog = Widget(parent) dialog.exec_() return [dialog.returnBedrijfsnaam(), dialog.returnRechtsvorm(),\ dialog.returnBTWnummer(), dialog.returnKvKnummer(),\ dialog.returnStraat(),dialog.returnHuisnummer(),\ dialog.returnToevoeging(), dialog.returnPostcode(),\ dialog.returnWoonplaats(), dialog.returnTelefoonnummer(),\ dialog.returnLeveranciernummer()] window = Widget() data = window.getData() if data[0]: mbedrnaam = (data[0]) else: mbedrnaam = rplev[1] if data[1]: mrechtsvorm = (data[1]).upper() else: mrechtsvorm = rplev[2] if data[2]: mbtwnr = data[2].upper() else: mbtwnr = rplev[3] if data[3]: mkvknr = data[3] else: mkvknr = rplev[4] if data[7]: mpostcode = (data[7]).upper() else: mpostcode = rplev[8] if data[5]: mhuisnr = data[5] else: mhuisnr = rplev[6] mhuisnr = int(mhuisnr) if data[6]: mtoev = '-' + data[6] else: mtoev = '' if data[9]: mtelnr = data[9] else: mtelnr = '' if not (len(mtelnr) == 10 or mtelnr == ''): foutTelnr() hoofdMenu(m_email) engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem') conn = engine.connect() u = update(leveranciers).where(leveranciers.c.leverancierID == mlevnr).\ values(bedrijfsnaam = mbedrnaam, rechtsvorm = mrechtsvorm, btwnummer =\ mbtwnr, kvknummer = mkvknr, telnr = mtelnr, postcode = mpostcode,\ huisnummer = mhuisnr, toevoeging = mtoev) conn.execute(u) conn.close() updateOK() zoekLeverancier(m_email)
def kiesOrder(m_email): 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() selord = select([orders_verkoop, klanten, accounts]).where(and_(orders_verkoop.c.klantID == klanten.c.klantID,\ klanten.c.accountID == accounts.c.accountID, accounts.c.email == m_email)) rpc = con.execute(selord).fetchone() if rpc: rpord = con.execute(selord) else: geenBest(m_email) mpostcode = rpc[14] mhuisnr = int(rpc[15]) madres = checkpostcode(mpostcode, mhuisnr) mstraat = madres[0] mplaats = madres[1] class MyWindow(QDialog): def __init__(self, data_list, header, *args): QWidget.__init__( self, *args, ) self.setGeometry(50, 50, 900, 900) self.setWindowTitle('Printen orderfacturen') 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.setColumnHidden(1, True) table_view.setColumnHidden(6, True) table_view.setColumnHidden(9, True) table_view.setColumnHidden(10, True) table_view.setColumnHidden(11, True) table_view.setColumnHidden(12, True) table_view.setColumnHidden(13, True) table_view.setColumnHidden(14, True) table_view.setColumnHidden(15, True) table_view.setColumnHidden(16, True) table_view.setColumnHidden(17, True) table_view.setColumnHidden(18, True) table_view.resizeColumnsToContents() table_view.setSelectionBehavior(QTableView.SelectRows) table_view.clicked.connect(printFactuur) 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 = ['Bestelnummer','','Besteldatum','Betaaldatum', 'Leverdatum','Totaalbedrag','', 'Accountnr',\ 'Rekeningnr','','','','','','','','','',''] data_list = [] for row in rpord: data_list += [(row)] def printFactuur(idx): from sys import platform movbestnr = idx.data() if idx.column() == 0: metadata = MetaData() 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() 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) mblad = 1 rgl = 0 if platform == 'win32': filename = '.\\forms\\Weborders_Facturen\\Weborder-factuur_' + str( rpc[0]) + '.txt' else: filename = './forms/Weborders_Facturen/Weborder-factuur_' + str( rpc[0]) + '.txt' adreskop=\ ('\n\n\n\n\n\n\nFACTUUR\n\n'+rpc[10]+' '+rpc[11]+' '+rpc[12]+' '+rpc[13]+',\n'+\ mstraat+' '+rpc[15]+rpc[16]+',\n'+\ rpc[14]+' '+mplaats+'.\n\n\n\n\n') open(filename, 'w').write(adreskop) kop=\ ('Ordernummer: '+ str(movbestnr)+' Datum: '+str(datetime.datetime.now())[0:10]+' Besteldatum: '+str(rpc[2])+' Blad : '+str(mblad)+'\n'+ '==============================================================================================\n'+ 'Artikelnr Omschrijving Eenheid Aantal Prijs Subtotaal BTW \n'+ '==============================================================================================\n') mtotaal = 0 for row in rpova: if rgl == 0 and mblad == 1: open(filename, 'w').write(adreskop) open(filename, 'a').write(kop) rgl = 16 elif rgl % 57 == 0: open(filename, 'a').write(kop) mblad += 1 msub = row[3] * row[5] open(filename,'a').write('{:<11d}'.format(row[2])+'{:<37.35s}'.format(row[11])+\ '{:>6s}'.format(row[14])+' '+'{:>6.2f}'.format(row[3])+'{:>10.2f}'.format(row[5])+\ ' '+'{:>10.2f}'.format(msub)+' '+'{:>10.2f}'.format(msub*21/121)+'\n') mtotaal = mtotaal + msub rgl += 1 tail =(\ '----------------------------------------------------------------------------------------------\n'+ 'Totaal factuurbedrag inclusief 21% BTW '+'{:10.2f}'.format(mtotaal)+' \n'+ '==============================================================================================\n') open(filename, 'a').write(tail) printGeg(filename, movbestnr) win = MyWindow(data_list, header) win.exec_() hoofdMenu(m_email)
def nieuwAccount(self): self.close() class Widget(QDialog): def __init__(self, parent=None): super(Widget, self).__init__(parent) self.setWindowTitle("Invoer account") 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.activated[str].connect(self.q2Changed) self.Voornaam = QLabel() q3Edit = QLineEdit() q3Edit.setFixedWidth(200) q3Edit.setFont(QFont("Arial",10)) q3Edit.textChanged.connect(self.q3Changed) reg_ex = QRegExp("^.{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) reg_ex = QRegExp("^.{1,10}$") input_validator = QRegExpValidator(reg_ex, q3Edit) q4Edit.setValidator(input_validator) self.Achternaam = QLabel() q5Edit = QLineEdit() q5Edit.setFixedWidth(540) q5Edit.setFont(QFont("Arial",10)) q5Edit.textChanged.connect(self.q5Changed) reg_ex = QRegExp("^.{1,50}$") input_validator = QRegExpValidator(reg_ex, q5Edit) q5Edit.setValidator(input_validator) self.Postcode = QLabel() q6Edit = QLineEdit() q6Edit.setFixedWidth(80) font = QFont("Arial",10) font.setCapitalization(QFont.AllUppercase) q6Edit.setFont(font) q6Edit.textChanged.connect(self.q6Changed) reg_ex = QRegExp("^[0-9]{4}[A-Za-z]{2}$") input_validator = QRegExpValidator(reg_ex, q6Edit) q6Edit.setValidator(input_validator) self.Huisnummer = QLabel() q7Edit = QLineEdit() 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) reg_ex = QRegExp("^[A-Za-z0-9-]{0,8}") input_validator = QRegExpValidator(reg_ex, q8Edit) q8Edit.setValidator(input_validator) self.email = QLabel() q9Edit = QLineEdit() q9Edit.setFixedWidth(300) q9Edit.setFont(QFont("Arial",10)) q9Edit.textChanged.connect(self.q9Changed) reg_ex = QRegExp("^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+$") input_validator = QRegExpValidator(reg_ex, q9Edit) q9Edit.setValidator(input_validator) self.Wachtwoord = QLabel() q10Edit = QLineEdit() q10Edit.setFixedWidth(300) q10Edit.setFont(QFont("Arial",10)) q10Edit.setEchoMode(QLineEdit.Password) q10Edit.textChanged.connect(self.q10Changed) self.ContrWachtwoord = QLabel() q11Edit = QLineEdit() q11Edit.setFixedWidth(300) q11Edit.setFont(QFont("Arial",10)) q11Edit.setEchoMode(QLineEdit.Password) q11Edit.textChanged.connect(self.q11Changed) self.Telefoonnr = QLabel() q12Edit = QLineEdit() q12Edit.setFixedWidth(100) q12Edit.setFont(QFont("Arial",10)) q12Edit.textChanged.connect(self.q12Changed) reg_ex = QRegExp("^[0]{1}[0-9]{9}$") input_validator = QRegExpValidator(reg_ex, q12Edit) q12Edit.setValidator(input_validator) self.Accountnummer = QLabel() q13Edit = QLineEdit(str(bepaalAccountnr())) q13Edit.setFixedWidth(100) q13Edit.setDisabled(True) q13Edit.setStyleSheet("QLineEdit { background-color: ; color: black }") q13Edit.setFont(QFont("Arial",10)) q13Edit.textChanged.connect(self.q13Changed) self.Geboortedatum = QLabel() q14Edit = QLineEdit() q14Edit.setFixedWidth(100) q14Edit.setFont(QFont("Arial",10)) q14Edit.textChanged.connect(self.q14Changed) 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, q14Edit) q14Edit.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('Nieuw account invoeren'), 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('Tussenvoegel'), 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(q14Edit, 7, 1) grid.addWidget(QLabel('Postcode *'), 8, 0) grid.addWidget(q6Edit, 8, 1) 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('e-mail *'), 10, 0) grid.addWidget(q9Edit, 10, 1, 1 ,2) grid.addWidget(QLabel('Wachtwoord *'), 11, 0) grid.addWidget(q10Edit, 11, 1, 1 ,2) grid.addWidget(QLabel('Wachtwoord controle *'), 12, 0) grid.addWidget(q11Edit, 12, 1, 1, 2) grid.addWidget(QLabel('Telefoonnummer'), 13, 0) grid.addWidget(q12Edit, 13, 1) grid.addWidget(QLabel('Accountnummer'), 14, 0) grid.addWidget(q13Edit, 14, 1) grid.addWidget(QLabel('\u00A9 2017 all rights reserved [email protected]'), 16, 0, 1, 3, Qt.AlignCenter) applyBtn = QPushButton('Invoeren') applyBtn.clicked.connect(self.accept) grid.addWidget(applyBtn, 14, 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)) grid.addWidget(cancelBtn, 13, 2 , 1, 1, Qt.AlignRight) cancelBtn.setFont(QFont("Arial",10)) cancelBtn.setFixedWidth(100) cancelBtn.setStyleSheet("color: black; background-color: gainsboro") helpBtn = QPushButton('Info') helpBtn.clicked.connect(lambda: info()) grid.addWidget(helpBtn, 12, 2 , 1, 1, Qt.AlignRight) helpBtn.setFont(QFont("Arial",10)) helpBtn.setFixedWidth(100) helpBtn.setStyleSheet("color: black; background-color: gainsboro") self.setLayout(grid) self.setGeometry(400, 150, 350, 300) 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.Postcode.setText(text) def q7Changed(self, text): self.Huisnummer.setText(text) def q8Changed(self, text): self.Toevoeging.setText(text) def q9Changed(self, text): self.email.setText(text) def q10Changed(self, text): self.Wachtwoord.setText(text) def q11Changed(self, text): self.ContrWachtwoord.setText(text) def q12Changed(self, text): self.Telefoonnr.setText(text) def q13Changed(self, text): self.Accountnummer.setText(text) def q14Changed(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 returnPostcode(self): return self.Postcode.text() def returnHuisnummer(self): return self.Huisnummer.text() def returnToevoeging(self): return self.Toevoeging.text() def returnemail(self): return self.email.text() def returnWachtwoord(self): return self.Wachtwoord.text() def returnContrWachtwoord(self): return self.ContrWachtwoord.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.returnPostcode(), dialog.returnHuisnummer(),\ dialog.returnToevoeging(), dialog.returnemail(),\ dialog.returnWachtwoord(), dialog.returnContrWachtwoord(),\ dialog.returnTelefoonnummer(),dialog.returnAccountnummer(),\ dialog.returnGeboortedatum()] window = Widget() data = window.getData() if data[1] and data[3] and valid(data[4], 1) and valid(data[5],2)\ and valid(data[7],3) and data[12] and passwcontrol(data[8], data[9]) and valid(data[10],6): if data[0]: maanhef = data[0] else: maanhef = ' ' mvoornaam = (data[1]).title() mtussenv = (data[2]).lower() if mtussenv: mtussenv = mtussenv+' ' machternaam = (data[3]).title() mpostcode = (data[4]).upper() mhuisnr = int(data[5]) mtoev = (data[6]) if mtoev: mtoev = '-'+mtoev m_email = (data[7]) mpassword = hash_password(data[8]) mtelnr = (data[10]) maccountnr = (data[11]) if data[12]: mgebdatum = data[12] import postcode mstrpl = postcode.checkpostcode(mpostcode,mhuisnr) if mstrpl[0]: metadata = MetaData() accounts = Table('accounts', metadata, Column('accountID', Integer(), primary_key=True), Column('aanhef', String(8)), Column('voornaam', String(30), nullable=False), Column('achternaam', String(50), nullable=False), Column('tussenvoegsel', String(10)), Column('telnr', String(10)), Column('email', String, nullable=False), Column('huisnummer', String(5), nullable=False), Column('postcode', String(6), nullable=False), Column('toevoeging', String), Column('password', String, nullable=False), Column('account_count', Integer(), nullable=False), Column('account_created', String), Column('geboortedatum', String)) klanten = Table('klanten', metadata, Column('klantID', Integer(), primary_key=True), Column('accountID', None, ForeignKey('accounts.c.accountID')), Column('klant_status', Integer())) engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem') metadata.create_all(engine) conn = engine.connect() maccountnr=bepaalAccountnr() try: mklantnr=(conn.execute(select([func.max(klanten.c.klantID,\ type_=Integer)])).scalar()) mklantnr += 1 except: mklantnr = 1 maccdatum = (datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))[0:10] metadata.create_all(engine) insacc = accounts.insert().values( accountID=maccountnr, aanhef=maanhef, voornaam=mvoornaam, tussenvoegsel=mtussenv, achternaam=machternaam, postcode=mpostcode.upper(), huisnummer=mhuisnr, toevoeging=mtoev, email=m_email, password=mpassword, account_count=1, telnr=mtelnr, account_created = maccdatum, geboortedatum = mgebdatum) insklant = klanten.insert().values( klantID = mklantnr, accountID = maccountnr, klant_status = 9) conn = engine.connect() s = select([accounts]).where(accounts.c.email == m_email) result = conn.execute(s).first() if result: accountBestaat() login.inlog() else: result = conn.execute(insacc) insacc.bind = engine result.inserted_primary_key result = conn.execute(insklant) insklant.bind = engine result.inserted_primary_key conn.close() Invoer() login.inlog() else: foutPostcode() self.close nieuwAccount(self) else: geenGegevens() self.close() nieuwAccount(self)
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 eigenLeverancier(m_email): metadata = MetaData() accounts = Table('accounts', metadata, Column('accountID', Integer, primary_key=True), Column('email', 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'))) 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() sel = select([leveranciers]).where(and_(leveranciers.c.leverancierID ==\ lev_accounts.c.leverancierID, lev_accounts.c.accountID ==\ accounts.c.accountID , accounts.c.email == m_email)) rplev = con.execute(sel) 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): 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 = ['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],row[7],\ row[8],row[6], mstrtplts[1])] win = MyWindow(data_list, header) win.exec_() hoofdMenu(m_email)
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_()