def load_dialect_impl(self, dialect):
if dialect.name == 'sqlite':
return dialect.type_descriptor(Integer())
else:
return dialect.type_descriptor(CHAR(32))
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):
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 = ['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,int(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.setStyleSheet(
"QLineEdit { font-size: 10pt; font-family: Arial; color: black }"
)
q3Edit.setDisabled(True)
self.Afdeling = QLabel()
q16Edit = QLineEdit(rpkoper[3])
q16Edit.setFixedWidth(540)
q16Edit.setStyleSheet(
"QLineEdit { font-size: 10pt; font-family: Arial; color: black }"
)
q16Edit.setDisabled(True)
self.Rechtsvorm = QLabel()
q5Edit = QLineEdit(rpkoper[2])
q5Edit.setFixedWidth(100)
q5Edit.setStyleSheet(
"QLineEdit { font-size: 10pt; font-family: Arial; color: black }"
)
q5Edit.setDisabled(True)
self.BTWnummer = QLabel()
q2Edit = QLineEdit(rpkoper[4])
q2Edit.setDisabled(True)
q2Edit.setFixedWidth(170)
q2Edit.setStyleSheet(
"QLineEdit { font-size: 10pt; font-family: Arial; color: black }"
)
self.KvKnummer = QLabel()
q4Edit = QLineEdit(rpkoper[5])
q4Edit.setFixedWidth(110)
q4Edit.setDisabled(True)
q4Edit.setStyleSheet(
"QLineEdit { font-size: 10pt; font-family: Arial; color: black }"
)
self.Straat = QLabel()
q1Edit = QLineEdit()
q1Edit.setText(mstraat)
q1Edit.setFixedWidth(540)
q1Edit.setDisabled(True)
q1Edit.setStyleSheet(
"QLineEdit { font-size: 10pt; font-family: Arial; color: black }"
)
self.Huisnummer = QLabel()
q7Edit = QLineEdit(str(mhuisnr))
q7Edit.setFixedWidth(60)
q7Edit.setFont(QFont("Arial", 10))
q7Edit.setDisabled(True)
q7Edit.setStyleSheet(
"QLineEdit { font-size: 10pt; font-family: Arial; color: black }"
)
self.Toevoeging = QLabel()
q8Edit = QLineEdit(rpkoper[8])
q8Edit.setFixedWidth(80)
q8Edit.setFont(QFont("Arial", 10))
q8Edit.setDisabled(True)
q8Edit.setStyleSheet(
"QLineEdit { font-size: 10pt; font-family: Arial; color: black }"
)
self.Postcode = QLabel()
q6Edit = QLineEdit(mpostcode)
q6Edit.setFixedWidth(80)
q6Edit.setFont(QFont("Arial", 10))
q6Edit.setDisabled(True)
q6Edit.setStyleSheet(
"QLineEdit { font-size: 10pt; font-family: Arial; color: black }"
)
self.Woonplaats = QLabel()
q15Edit = QLineEdit(mplaats)
q15Edit.setFixedWidth(400)
q15Edit.setDisabled(True)
q15Edit.setStyleSheet(
"QLineEdit { font-size: 10pt; font-family: Arial; color: black }"
)
self.Telefoonnr = QLabel()
q13Edit = QLineEdit(rpkoper[6])
q13Edit.setFixedWidth(120)
q13Edit.setFont(QFont("Arial", 10))
q13Edit.setDisabled(True)
q13Edit.setStyleSheet(
"QLineEdit { font-size: 10pt; font-family: Arial; color: black }"
)
self.Bedrijfverkoopnummer = QLabel()
q14Edit = QLineEdit(str(rpkoper[0]))
q14Edit.setFixedWidth(120)
q14Edit.setDisabled(True)
q14Edit.setFont(QFont("Arial", 10))
q14Edit.setStyleSheet(
"QLineEdit { font-size: 10pt; font-family: Arial; color: black }"
)
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)
class LinksToDiscordIgnoreList(Base):
""" List of twitch users to ignore for sending links to discord."""
__tablename__ = "linkstodiscordignorelist"
id = Column(Integer(), primary_key=True)
username = Column(String(128), nullable=False, unique=True)
class Node(Base, ContainerMixin, PersistentACLMixin, metaclass=NodeMeta):
"""Basic node in the persistance hierarchy.
"""
__table_args__ = (UniqueConstraint("parent_id", "name"), )
__mapper_args__ = dict(polymorphic_on="type",
polymorphic_identity="node",
with_polymorphic="*")
#: Primary key for the node in the DB
#: (:class:`sqlalchemy.types.Integer`)
id = Column(Integer(), primary_key=True)
#: Lowercase class name of the node instance
#: (:class:`sqlalchemy.types.String`)
type = Column(String(30), nullable=False)
#: ID of the node's parent
#: (:class:`sqlalchemy.types.Integer`)
parent_id = Column(ForeignKey("nodes.id"), index=True)
#: Position of the node within its container / parent
#: (:class:`sqlalchemy.types.Integer`)
position = Column(Integer())
_acl = Column(MutationList.as_mutable(ACLType))
#: Name of the node as used in the URL
#: (:class:`sqlalchemy.types.Unicode`)
name = Column(Unicode(250), nullable=False)
#: Title of the node, e.g. as shown in search results
#: (:class:`sqlalchemy.types.Unicode`)
title = Column(Unicode(250))
#: Annotations can be used to store arbitrary data in a nested dictionary
#: (:class:`kotti.sqla.NestedMustationDict`)
annotations = Column(NestedMutationDict.as_mutable(JsonType))
#: The path can be used to efficiently filter for child objects
#: (:class:`sqlalchemy.types.Unicode`).
path = Column(Unicode(2000), index=True)
parent = relation(
"Node",
remote_side=[id],
backref=backref(
"_children",
collection_class=ordering_list("position", reorder_on_append=True),
order_by=[position],
cascade="all",
),
)
local_groups = relation(LocalGroup,
backref=backref("node"),
cascade="all",
lazy="joined")
__hash__ = Base.__hash__
def __init__(self,
name: str = None,
parent: "Node" = None,
title: str = "",
annotations: dict = None,
**kwargs):
"""Constructor"""
super(Node, self).__init__(**kwargs)
if annotations is None:
annotations = {}
self.parent = parent
self.name = name
self.title = title
self.annotations = annotations
@property
def __name__(self):
return self.name
@property
def __parent__(self):
return self.parent
@__parent__.setter
def __parent__(self, value):
self.parent = value
def __repr__(self) -> str:
return "<{0} {1} at {2}>".format(self.__class__.__name__, self.id,
resource_path(self))
def __eq__(self, other: Any) -> bool:
return isinstance(other, Node) and self.id == other.id
def __ne__(self, other: Any) -> bool:
return not self == other
copy_properties_blacklist = (
"id",
"parent",
"parent_id",
"_children",
"local_groups",
"_tags",
)
def clear(self) -> None:
DBSession.query(Node).filter(Node.parent == self).delete()
def copy(self, **kwargs) -> "Node":
"""
:result: A copy of the current instance
:rtype: :class:`~kotti.resources.Node`
"""
children = list(self.children)
copy = self.__class__()
for prop in object_mapper(self).iterate_properties:
if prop.key not in self.copy_properties_blacklist:
setattr(copy, prop.key, getattr(self, prop.key))
for key, value in kwargs.items():
setattr(copy, key, value)
for child in children:
copy.children.append(child.copy())
return copy
def upgrade(migrate_engine):
# Upgrade operations go here
# Don't create your own engine; bind migrate_engine
# to your metadata
meta = MetaData()
meta.bind = migrate_engine
#
# New Tables
#
instance_types = Table('instance_types', meta,
Column('created_at', DateTime(timezone=False)),
Column('updated_at', DateTime(timezone=False)),
Column('deleted_at', DateTime(timezone=False)),
Column('deleted', Boolean(create_constraint=True, name=None)),
Column('name',
String(length=255, convert_unicode=False,
assert_unicode=None,
unicode_error=None, _warn_on_bytestring=False),
unique=True),
Column('id', Integer(), primary_key=True, nullable=False),
Column('memory_mb', Integer(), nullable=False),
Column('vcpus', Integer(), nullable=False),
Column('local_gb', Integer(), nullable=False),
Column('flavorid', Integer(), nullable=False, unique=True),
Column('swap', Integer(), nullable=False, default=0),
Column('rxtx_quota', Integer(), nullable=False, default=0),
Column('rxtx_cap', Integer(), nullable=False, default=0))
try:
instance_types.create()
except Exception:
LOG.info(repr(instance_types))
LOG.exception('Exception while creating instance_types table')
raise
# Here are the old static instance types
INSTANCE_TYPES = {
'm1.tiny': dict(memory_mb=512, vcpus=1, local_gb=0, flavorid=1),
'm1.small': dict(memory_mb=2048, vcpus=1, local_gb=20, flavorid=2),
'm1.medium': dict(memory_mb=4096, vcpus=2, local_gb=40, flavorid=3),
'm1.large': dict(memory_mb=8192, vcpus=4, local_gb=80, flavorid=4),
'm1.xlarge': dict(memory_mb=16384, vcpus=8, local_gb=160, flavorid=5),
'cg1.small': dict(memory_mb=2048, vcpus=1, local_gb=20, flavorid=101),
'cg1.medium': dict(memory_mb=4096, vcpus=2, local_gb=40, flavorid=102),
'cg1.large': dict(memory_mb=8192, vcpus=4, local_gb=80, flavorid=103),
'cg1.xlarge': dict(memory_mb=16384, vcpus=8, local_gb=160, flavorid=104),
'cg1.2xlarge': dict(memory_mb=16384, vcpus=8, local_gb=320, flavorid=105),
'cg1.4xlarge': dict(memory_mb=22000, vcpus=8, local_gb=640, flavorid=106),
'sh1.small': dict(memory_mb=2048, vcpus=1, local_gb=20, flavorid=201),
'sh1.medium': dict(memory_mb=4096, vcpus=2, local_gb=40, flavorid=202),
'sh1.large': dict(memory_mb=8192, vcpus=4, local_gb=80, flavorid=203),
'sh1.xlarge': dict(memory_mb=16384, vcpus=8, local_gb=160, flavorid=204),
'sh1.2xlarge': dict(memory_mb=32768, vcpus=16, local_gb=320, flavorid=205),
'sh1.4xlarge': dict(memory_mb=65536, vcpus=32, local_gb=320, flavorid=206),
'sh1.8xlarge': dict(memory_mb=131072, vcpus=64, local_gb=500,
flavorid=207),
'sh1.16xlarge': dict(memory_mb=262144, vcpus=128, local_gb=500,
flavorid=208),
'sh1.32xlarge': dict(memory_mb=524288, vcpus=256, local_gb=1000,
flavorid=209),
'tp64.8x8': dict(memory_mb=16384, vcpus=1, local_gb=1000, flavorid=302)
}
try:
i = instance_types.insert()
for name, values in INSTANCE_TYPES.iteritems():
# FIXME(kpepple) should we be seeding created_at / updated_at ?
# now = datetime.datatime.utcnow()
i.execute({'name': name, 'memory_mb': values["memory_mb"],
'vcpus': values["vcpus"], 'deleted': False,
'local_gb': values["local_gb"],
'flavorid': values["flavorid"]})
except Exception:
LOG.info(repr(instance_types))
LOG.exception('Exception while seeding instance_types table')
raise
class Role(Base, RoleMixin):
__tablename__ = 'role'
id = Column(Integer(), primary_key=True)
name = Column(String(80), unique=True)
description = Column(String(255))
ACTIONS = ['CREATE', 'DELETE', 'UPDATE', 'NONE']
ZONE_ATTRIBUTE_KEYS = ('master', )
ZONE_TYPES = (
'PRIMARY',
'SECONDARY',
)
metadata = MetaData()
quotas = Table(
'quotas',
metadata,
Column('id', UUID, default=utils.generate_uuid, primary_key=True),
Column('version', Integer(), default=1, nullable=False),
Column('created_at', DateTime, default=lambda: timeutils.utcnow()),
Column('updated_at', DateTime, onupdate=lambda: timeutils.utcnow()),
Column('tenant_id', String(36), default=None, nullable=True),
Column('resource', String(32), nullable=False),
Column('hard_limit', Integer(), nullable=False),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
tlds = Table(
'tlds',
metadata,
Column('id', UUID, default=utils.generate_uuid, primary_key=True),
Column('version', Integer(), default=1, nullable=False),
Column('created_at', DateTime, default=lambda: timeutils.utcnow()),
def point_map(self, cls):
table = Table('point', MetaData(),
Column('id', Integer(), primary_key=True),
Column('x', Integer), Column('y', Integer))
mapper(cls, table)
return table
def urenBoeking(self, m_email):
maccountnr = self.zkaccEdit.text()
mwerknr = self.zkwerknEdit.text()
mboekd = self.boekdatumEdit.text()
mstatus = self.cBox.checkState()
if mstatus == 0:
mstatus = False
else:
mstatus = True
metadata = MetaData()
wrkwnrln = Table('wrkwnrln', metadata,
Column('wrkwnrurenID', Integer, primary_key=True),
Column('werknemerID', None, ForeignKey('werknemers.werknemerID')),
Column('werknummerID',Integer),
Column('loonID', None, ForeignKey('lonen.loonID')),
Column('boekdatum', String),
Column('aantaluren', Float),
Column('tabelloon', Float),
Column('reisloon', Float),
Column('bruto_loonbedrag', Float),
Column('meerwerkstatus', Boolean),
Column('soort', String))
werknemers = Table('werknemers', metadata,
Column('werknemerID', Integer(), primary_key=True),
Column('accountID', None, ForeignKey('accounts.accountID')),
Column('loonID', None, ForeignKey('lonen.loonID')),
Column('verlofsaldo', Float),
Column('extraverlof', Float),
Column('wnrloonID', Integer))
werken = Table('werken', metadata,
Column('werknummerID', Integer, primary_key=True),
Column('kosten_lonen', Float),
Column('voortgangstatus', String),
Column('statusweek', String(6)),
Column('begr_constr_uren', Float),
Column('werk_constr_uren', Float),
Column('begr_mont_uren', Float),
Column('werk_mont_uren', Float),
Column('begr_retourlas_uren', Float),
Column('werk_retourlas_uren', Float),
Column('begr_telecom_uren', Float),
Column('werk_telecom_uren', Float),
Column('begr_bfi_uren', Float),
Column('werk_bfi_uren', Float),
Column('begr_bvl_uren', Float),
Column('werk_bvl_uren', Float),
Column('begr_spoorleg_uren', Float),
Column('werk_spoorleg_uren', Float),
Column('begr_spoorlas_uren', Float),
Column('werk_spoorlas_uren', Float),
Column('begr_reis_uren', Float),
Column('werk_reis_uren', Float),
Column('meerminderwerk', Float),
Column('begr_voeding_uren', Float),
Column('werk_voeding_uren', Float))
lonen = Table('lonen', metadata,
Column('loonID', Integer, primary_key=True),
Column('tabelloon', Float),
Column('werkuur', Float),
Column('reisuur', Float))
engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem')
con = engine.connect()
selwnr = select([werknemers]).where(and_(werknemers.c.accountID==int(maccountnr),\
werknemers.c.loonID < 37))
rpwnr = con.execute(selwnr).first()
if rpwnr:
maccountnr = int(maccountnr)
else:
self.urenEdit.setText('0')
self.lblt.setStyleSheet("font: bold ; color: red")
self.lblt.setText('Persoon niet in deze arbeidspool!')
self.applyBtn.setStyleSheet("color: black; background-color: #FF3333")
return('', mwerknr, mboekd, m_email)
if mwerknr and len(mwerknr)== 9 and _11check(mwerknr):
mwerknr = int(mwerknr)
else:
self.urenEdit.setText('0')
self.lblt.setStyleSheet("font: bold ; color: red")
self.lblt.setText('Dit is geen geldig werknummer!')
self.applyBtn.setStyleSheet("color: black; background-color: #FF3333")
return(maccountnr, '', mboekd, m_email)
engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem')
con = engine.connect()
selwerk = select([werken]).where(werken.c.werknummerID == mwerknr)
rpwerk = con.execute(selwerk).first()
muren = 0
mu125 = 0
mu150 = 0
mu200 = 0
mreis = 0
mmeerw100 = 0
mmeerw125 = 0
mmeerw150 = 0
mmeerw200 = 0
mverlof = 0
mextraverlof = 0
mziek = 0
mfeest = 0
mdokter = 0
mgverzuim = 0
moverzuim = 0
msoort = self.k0Edit.currentIndex()
mboekuren = float(self.urenEdit.text())
mlist = ['100%','125%','150%','200%','Reis','Verlof','Extra verlof','Ziekte',\
'Feestdag','Dokter','Geoorl. verzuim','Ong. verzuim']
if rpwerk[2] == 'H':
self.urenEdit.setText('0')
self.lblt.setStyleSheet("font: bold ; color: red")
self.lblt.setText('Werk is gereed en afgemeld!')
self.applyBtn.setStyleSheet("color: black; background-color: #FF3333")
return(maccountnr, mwerknr, mboekd, m_email)
elif mboekuren and msoort == 0 and mstatus:
mmeerw100 = mboekuren
elif mboekuren and msoort == 0:
muren = mboekuren
elif mboekuren and msoort == 1 and mstatus:
mmeerw125 = mboekuren
elif mboekuren and msoort == 1:
mu125 = mboekuren
elif mboekuren and msoort == 2 and mstatus:
mmeerw150 = mboekuren
elif mboekuren and msoort == 2:
mu150 = mboekuren
elif mboekuren and msoort == 3 and mstatus:
mmeerw200 = mboekuren
elif mboekuren and msoort == 3:
mu200 = mboekuren
elif mboekuren and msoort == 4:
mreis = mboekuren
elif mboekuren and msoort == 5:
mverlof = mboekuren
upd = update(werknemers).where(werknemers.c.accountID ==\
maccountnr).values(verlofsaldo = werknemers.c.verlofsaldo - mboekuren)
con.execute(upd)
elif mboekuren and msoort == 6:
mextraverlof = mboekuren
upd = update(werknemers).where(werknemers.c.accountID ==\
maccountnr).values(extraverlof = werknemers.c.extraverlof - mextraverlof)
con.execute(upd)
elif mboekuren and msoort == 7:
mziek = mboekuren
elif mboekuren and msoort == 8:
mfeest = mboekuren
elif mboekuren and msoort == 9:
mdokter = mboekuren
elif mboekuren and msoort == 10:
mgverzuim = mboekuren
elif mboekuren and msoort == 11:
moverzuim = mboekuren
else:
self.urenEdit.setText('0')
self.lblt.setStyleSheet("font: bold;color: red")
self.lblt.setText('Geen uren ingevoerd!')
self.applyBtn.setStyleSheet("color: black; background-color: #FF3333")
return(maccountnr, mwerknr, mboekd, m_email)
engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem')
con = engine.connect()
mwrkwnruren=(con.execute(select([func.max(wrkwnrln.c.wrkwnrurenID, type_=Integer)\
.label('mwrkwnruren')])).scalar())
wrkgr = rpwnr[2]
wrkgr2 = rpwnr[5]
loonsel = select([lonen]).where(lonen.c.loonID == wrkgr) #tijd loonID voor werken
loonsel2 = select([lonen]).where(lonen.c.loonID == wrkgr2) #loonID voor lonen
loonres = con.execute(loonsel).first()
loonres2 = con.execute(loonsel2).first()
muurloon = loonres2[1]
mwerkuur = loonres[2]
mreisuur = loonres[3]
mwrku125 = mwerkuur*1.25
mwrku150 = mwerkuur*1.5
mwrku200 = mwerkuur*2
loonk = (muren*mwerkuur)+(mreis*mreisuur)+(mu125*mwrku125)+(mu150*mwrku150)+\
(mu200*mwrku200)+(mmeerw100*mwerkuur)+(mmeerw125*mwerkuur)+(mmeerw150*\
mwerkuur)+(mmeerw200*mwerkuur)
meerk = (mmeerw100*mwerkuur)+(mmeerw125*mwrku125)+\
(mmeerw150*mwrku150)+(mmeerw200*mwrku200)
lonen = (muren*muurloon)+(mu125*muurloon*1.25)+(mu150*muurloon*1.5)+(mu200\
*muurloon*2)+(mreis*mreisuur)+(mmeerw100*muurloon)+\
(mmeerw125*muurloon*1.25)+(mmeerw150*muurloon*1.5)+\
(mmeerw200*muurloon*2)+(mverlof*muurloon)+(mextraverlof*muurloon)+\
(mziek*muurloon)+(mfeest*muurloon)+(mdokter*muurloon)+\
(mgverzuim*muurloon)+(moverzuim*muurloon)
inswrkwnrln = wrkwnrln.insert().values(
wrkwnrurenID = mwrkwnruren+1,
werknemerID = rpwnr[0],
boekdatum = mboekd,
aantaluren = muren+mu125+mu150+mu200+mreis+mmeerw100+mmeerw125+mmeerw150+\
mmeerw200+mverlof+mextraverlof+mziek+mfeest+mdokter+mgverzuim+moverzuim,
soort = mlist[msoort],
werknummerID = mwerknr,
tabelloon = muurloon,
reisloon = mreisuur,
bruto_loonbedrag = lonen,
meerwerkstatus = mstatus,
loonID = wrkgr2)
if con.execute(inswrkwnrln):
self.applyBtn.setStyleSheet("color: black; background-color: #00CC66")
else:
self.urenEdit.setText('0')
self.applyBtn.setStyleSheet("color: black; background-color: #FF3333")
return(maccountnr, mwerknr, mboekd, m_email)
if wrkgr < 5 and msoort < 5:
stmt = update(werken).where(werken.c.werknummerID == mwerknr).\
values(kosten_lonen = werken.c.kosten_lonen+loonk,
werk_constr_uren = werken.c.werk_constr_uren+muren+mu125+mu150+mu200\
+mmeerw100+mmeerw125+mmeerw150+mmeerw200,\
werk_reis_uren = werken.c.werk_reis_uren+mreis,\
meerminderwerk = werken.c.meerminderwerk + meerk)
con.execute(stmt)
sel = select([werken]).where(werken.c.werknummerID == mwerknr)
rpsel = con.execute(sel).first()
self.urentotEdit.setText('{:<12.2f}'.format(rpsel[5]))
self.urenbegrEdit.setText('{:<12.2f}'.format(rpsel[4]))
lblptext = 'Totalen: Werkelijk / Begroot\nUren Constructie'
lbltext = 'Muteren uren (werken - lonen) niet cumulatief'
self.lblprof.setText(lblptext)
self.lblt.setStyleSheet("color: black")
self.lblt.setText(lbltext)
elif wrkgr < 9 and msoort < 5:
stmt = update(werken).where(werken.c.werknummerID == mwerknr).\
values(kosten_lonen = werken.c.kosten_lonen+loonk,
werk_mont_uren = werken.c.werk_mont_uren+muren+mu125+mu150+mu200\
+mmeerw100+mmeerw125+mmeerw150+mmeerw200,\
werk_reis_uren = werken.c.werk_reis_uren+mreis,\
meerminderwerk = werken.c.meerminderwerk + meerk)
con.execute(stmt)
sel = select([werken]).where(werken.c.werknummerID == mwerknr)
rpsel = con.execute(sel).first()
self.urenbegrEdit.setText('{:<12.2f}'.format(rpsel[6]))
self.urentotEdit.setText('{:<12.2f}'.format(rpsel[7]))
lblptext = 'Totalen: Werkelijk / Begroot\nUren Montage'
lbltext = 'Muteren uren (werken - lonen) niet cumulatief'
self.lblprof.setText(lblptext)
self.lblt.setStyleSheet("color: black")
self.lblt.setText(lbltext)
elif wrkgr < 13 and msoort < 5:
stmt = update(werken).where(werken.c.werknummerID == mwerknr).\
values(kosten_lonen = werken.c.kosten_lonen+loonk,
werk_retourlas_uren = werken.c.werk_retourlas_uren+muren+mu125+mu150+mu200\
+mmeerw100+mmeerw125+mmeerw150+mmeerw200,\
werk_reis_uren = werken.c.werk_reis_uren+mreis,\
meerminderwerk = werken.c.meerminderwerk + meerk)
con.execute(stmt)
sel = select([werken]).where(werken.c.werknummerID == mwerknr)
rpsel = con.execute(sel).first()
self.urenbegrEdit.setText('{:<12.2f}'.format(rpsel[8]))
self.urentotEdit.setText('{:<12.2f}'.format(rpsel[9]))
lblptext = 'Totalen: Werkelijk / Begroot\nUren Retourlas'
lbltext = 'Muteren uren (werken - lonen) niet cumulatief'
self.lblprof.setText(lblptext)
self.lblt.setStyleSheet("color: black")
self.lblt.setText(lbltext)
elif wrkgr < 17 and msoort < 5:
stmt = update(werken).where(werken.c.werknummerID == mwerknr).\
values(kosten_lonen = werken.c.kosten_lonen+loonk,
werk_telecom_uren = werken.c.werk_telecom_uren+muren+mu125+mu150+mu200\
+mmeerw100+mmeerw125+mmeerw150+mmeerw200,\
werk_reis_uren = werken.c.werk_reis_uren+mreis,\
meerminderwerk = werken.c.meerminderwerk + meerk)
con.execute(stmt)
sel = select([werken]).where(werken.c.werknummerID == mwerknr)
rpsel = con.execute(sel).first()
self.urenbegrEdit.setText('{:<12.2f}'.format(rpsel[10]))
self.urentotEdit.setText('{:<12.2f}'.format(rpsel[11]))
lblptext = 'Totalen: Werkelijk / Begroot\nUren Telecom'
lbltext = 'Muteren uren (werken - lonen) niet cumulatief'
self.lblprof.setText(lblptext)
self.lblt.setStyleSheet("color: black")
self.lblt.setText(lbltext)
elif wrkgr < 21 and msoort < 5:
stmt = update(werken).where(werken.c.werknummerID == mwerknr).\
values(kosten_lonen = werken.c.kosten_lonen+loonk,
werk_bfi_uren = werken.c.werk_bfi_uren+muren+mu125+mu150+mu200\
+mmeerw100+mmeerw125+mmeerw150+mmeerw200,\
werk_reis_uren = werken.c.werk_reis_uren+mreis,\
meerminderwerk = werken.c.meerminderwerk + meerk)
con.execute(stmt)
sel = select([werken]).where(werken.c.werknummerID == mwerknr)
rpsel = con.execute(sel).first()
self.urenbegrEdit.setText('{:<12.2f}'.format(rpsel[12]))
self.urentotEdit.setText('{:<12.2f}'.format(rpsel[13]))
lblptext = 'Totalen: Werkelijk / Begroot\nUren BFI'
lbltext = 'Muteren uren (werken - lonen) niet cumulatief'
self.lblprof.setText(lblptext)
self.lblt.setStyleSheet("color: black")
self.lblt.setText(lbltext)
elif wrkgr < 25 and msoort < 5:
stmt = update(werken).where(werken.c.werknummerID == mwerknr).\
values(kosten_lonen = werken.c.kosten_lonen+loonk,
werk_bvl_uren = werken.c.werk_bvl_uren+muren+mu125+mu150+mu200\
+mmeerw100+mmeerw125+mmeerw150+mmeerw200,\
werk_reis_uren = werken.c.werk_reis_uren+mreis,\
meerminderwerk = werken.c.meerminderwerk + meerk)
con.xecute(stmt)
sel = select([werken]).where(werken.c.werknummerID == mwerknr)
rpsel = con.execute(sel).first()
self.urenbegrEdit.setText('{:<12.2f}'.format(rpsel[14]))
self.urentotEdit.setText('{:<12.2f}'.format(rpsel[15]))
lblptext = 'Totalen: Werkelijk / Begroot\nUren Bovenleiding'
lbltext = 'Muteren uren (werken - lonen) niet cumulatief'
self.lblprof.setText(lblptext)
self.lblt.setStyleSheet("color: black")
self.lblt.setText(lbltext)
elif wrkgr < 29 and msoort < 5:
stmt = update(werken).where(werken.c.werknummerID == mwerknr).\
values(kosten_lonen = werken.c.kosten_lonen+loonk,
werk_spoorleg_uren = werken.c.werk_spoorleg_uren+muren+mu125+mu150+mu200\
+mmeerw100+mmeerw125+mmeerw150+mmeerw200,\
werk_reis_uren = werken.c.werk_reis_uren+mreis,\
meerminderwerk = werken.c.meerminderwerk + meerk)
con.execute(stmt)
sel = select([werken]).where(werken.c.werknummerID == mwerknr)
rpsel = con.execute(sel).first()
self.urenbegrEdit.setText('{:<12.2f}'.format(rpsel[16]))
self.urentotEdit.setText('{:>12.2f}'.format(rpsel[17]))
lblptext = 'Totalen: Werkelijk / Begroot\nUren Spoorleg'
lbltext = 'Muteren uren (werken - lonen) niet cumulatief'
self.lblprof.setText(lblptext)
self.lblt.setStyleSheet("color: black")
self.lblt.setText(lbltext)
elif wrkgr < 33 and msoort < 5:
stmt = update(werken).where(werken.c.werknummerID == mwerknr).\
values(kosten_lonen = werken.c.kosten_lonen+loonk,
werk_mont_uren = werken.c.werk_spoorlas_uren+muren+mu125+mu150+mu200\
+mmeerw100+mmeerw125+mmeerw150+mmeerw200,\
werk_reis_uren = werken.c.werk_reis_uren+mreis,\
meerminderwerk = werken.c.meerminderwerk + meerk)
con.execute(stmt)
sel = select([werken]).where(werken.c.werknummerID == mwerknr)
rpsel = con.execute(sel).first()
self.urenbegrEdit.setText('{:<12.2f}'.format(rpsel[18]))
self.urentotEdit.setText('{:<12.2f}'.format(rpsel[19]))
lblptext = 'Totalen: Werkelijk / Begroot\nUren Spoorlas'
lbltext = 'Muteren uren (werken - lonen) niet cumulatief'
self.lblprof.setText(lblptext)
self.lblt.setStyleSheet("color: black")
self.lblt.setText(lbltext)
elif wrkgr < 37 and msoort < 5:
stmt = update(werken).where(werken.c.werknummerID == mwerknr).\
values(kosten_lonen = werken.c.kosten_lonen+loonk,
werk_voeding_uren = werken.c.werk_voeding_uren+muren+mu125+mu150+mu200\
+mmeerw100+mmeerw125+mmeerw150+mmeerw200,\
werk_reis_uren = werken.c.werk_reis_uren+mreis,\
meerminderwerk = werken.c.meerminderwerk + meerk)
con.execute(stmt)
sel = select([werken]).where(werken.c.werknummerID == mwerknr)
rpsel = con.execute(sel).first()
self.urenbegrEdit.setText('{:<12.2f}'.format(rpsel[23]))
self.urentotEdit.setText('{:<12.2f}'.format(rpsel[24]))
lblptext = 'Totalen: Werkelijk / Begroot\nUren Voeding'
lbltext = 'Muteren uren (werken - lonen) niet cumulatief'
self.lblprof.setText(lblptext)
self.lblt.setStyleSheet("color: black")
self.lblt.setText(lbltext)
else:
msaldo = ''
mboekuren = str(mboekuren)
if msoort == 5 and wrkgr < 37:
selsal = select([werknemers]).where(werknemers.c.accountID == maccountnr)
rpsal = con.execute(selsal).first()
msaldo = str(rpsal[3])
lbltext = mboekuren+' Verlofuren ingevoerd, Saldo = '+msaldo+' uren.'
lblptext = '\n'
self.lblt.setStyleSheet("color: navy")
self.lblt.setText(lbltext)
self.lblprof.setText(lblptext)
elif msoort == 6 and wrkgr < 37:
lbltext = mboekuren+' Extra verlofuren ingevoerd'
lblptext = '\n'
self.lblt.setStyleSheet("color: navy")
self.lblt.setText(lbltext)
self.lblprof.setText(lblptext)
elif msoort == 7 and wrkgr < 37:
lbltext = mboekuren+' Uren ziekte ingevoerd'
lblptext = '\n'
self.lblt.setStyleSheet("color: navy")
self.lblt.setText(lbltext)
self.lblprof.setText(lblptext)
elif msoort == 8 and wrkgr < 37:
lbltext = mboekuren+' Uren feestdagen ingevoerd'
lblptext = '\n'
self.lblt.setStyleSheet("color: navy")
self.lblt.setText(lbltext)
self.lblprof.setText(lblptext)
elif msoort == 9 and wrkgr < 37:
lbltext = mboekuren+' Uren dokterbezoek ingevoerd'
lblptext = '\n'
self.lblt.setStyleSheet("color: navy")
self.lblt.setText(lbltext)
self.lblprof.setText(lblptext)
elif msoort == 10 and wrkgr < 37:
lbltext = mboekuren+' Uren geoorloofd verzuim ingevoerd'
lblptext = '\n'
self.lblt.setStyleSheet("color: navy")
self.lblt.setText(lbltext)
self.lblprof.setText(lblptext)
elif msoort == 11 and wrkgr < 37:
lbltext = mboekuren+' Uren ongeoorloofd verzuim ingevoerd'
lblptext = '\n'
self.lblt.setStyleSheet("color: navy")
self.lblt.setText(lbltext)
self.lblprof.setText(lblptext)
else:
self.urenEdit.setText('0')
self.lblt.setStyleSheet("font: bold;color: red")
self.lblt.setText('Persoon niet in deze arbeidspool!')
self.applyBtn.setStyleSheet("color: black; background-color: #FF3333")
return(maccountnr, mwerknr, mboekd, m_email)
self.urenEdit.setText('0')
self.k0Edit.setCurrentIndex(0)
return(maccountnr, mwerknr, mboekd, m_email)
def upgrade(migrate_engine):
meta = MetaData()
meta.bind = migrate_engine
# New tables
quota_usages = Table('quota_usages', meta,
Column('created_at', DateTime(timezone=False)),
Column('updated_at', DateTime(timezone=False)),
Column('deleted_at', DateTime(timezone=False)),
Column('deleted', Boolean(create_constraint=True, name=None)),
Column('id', Integer(), primary_key=True),
Column('project_id',
String(length=255, convert_unicode=True,
assert_unicode=None, unicode_error=None,
_warn_on_bytestring=False),
index=True),
Column('resource',
String(length=255, convert_unicode=True,
assert_unicode=None, unicode_error=None,
_warn_on_bytestring=False)),
Column('in_use', Integer(), nullable=False),
Column('reserved', Integer(), nullable=False),
Column('until_refresh', Integer(), nullable=True),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
try:
quota_usages.create()
except Exception:
LOG.error(_("Table |%s| not created!"), repr(quota_usages))
raise
reservations = Table('reservations', meta,
Column('created_at', DateTime(timezone=False)),
Column('updated_at', DateTime(timezone=False)),
Column('deleted_at', DateTime(timezone=False)),
Column('deleted', Boolean(create_constraint=True, name=None)),
Column('id', Integer(), primary_key=True),
Column('uuid',
String(length=36, convert_unicode=True,
assert_unicode=None, unicode_error=None,
_warn_on_bytestring=False), nullable=False),
Column('usage_id', Integer(), ForeignKey('quota_usages.id'),
nullable=False),
Column('project_id',
String(length=255, convert_unicode=True,
assert_unicode=None, unicode_error=None,
_warn_on_bytestring=False),
index=True),
Column('resource',
String(length=255, convert_unicode=True,
assert_unicode=None, unicode_error=None,
_warn_on_bytestring=False)),
Column('delta', Integer(), nullable=False),
Column('expire', DateTime(timezone=False)),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
try:
reservations.create()
except Exception:
LOG.error(_("Table |%s| not created!"), repr(reservations))
raise
from sqlalchemy.engine import reflection
from sqlalchemy import MetaData
import logging
import database_credentials
import pdb
from datetime import datetime
from sqlalchemy import (MetaData, Table, Column, Integer, Numeric, String,
DateTime, ForeignKey)
metadata = MetaData()
bibliographic_record_dimension = Table(
'dw_stg_3_bib_rec_dim', metadata,
Column('bib_rec_dim_key', Integer(), primary_key=True),
Column('bib_record_rec_type_desc', String(50)),
Column('bib_rec_id', String(9)),
Column('bib_rec_aleph_lbry_name', String(5)),
Column('bib_rec_marc_rec_field_cnt', Integer()),
Column('bib_rec_marc_rec_data_content_len_cnt', Integer()),
Column('bib_rec_marc_rec_data_content_txt', String(45000)),
Column('bib_rec_publication_year_no', Integer()),
Column('bib_rec_title', String(100)),
Column('bib_rec_author_name', String(100)),
Column('bib_rec_imprint_txt', String(100)),
Column('bib_rec_isbn_issn_source_cd', String(5)),
Column('bib_rec_isbn_txt', String(100)),
Column('bib_rec_all_associated_issns_txt', String(100)),
Column('bib_rec_oclc_no', String(500)),
Column('bib_rec_marc_rec_leader_field_txt', String(500)),
auth = OAuthHandler(consumer_key, consumer_secret)
auth.set_access_token(access_token, access_token_secret)
api = API(auth)
quoted = urllib.parse.quote_plus('DRIVER={ODBC Driver 13 for SQL Server};Server=<server name>;Database=<database>;UID=<username>;PWD=<password>;Port=1433;Trusted_connection=yes')
engine = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted))
conn = engine.connect()
metadata = MetaData()
twitter = Table('Twitter_Data' , metadata ,
Column('Date',Date()),
Column('TweetId',String(50)),
Column('Tweet',String(5000)),
Column('AuthorId',String(50)),
Column('ScreenName',String(100)),
Column('Source',String(500)),
Column('RetweetCount',Integer()),
Column('FavoriteCount',Integer()),
Column('FollowerCount',Integer()),
Column('UserURL',String(500)),
Column('Language',String(50)),
extend_existing=True
)
tweet = {}
# A class to stream tweets and its metadata
class StdOutListener(StreamListener):
def __init__(self,api,count):
self.api = api
self.counter = count + 1
def on_status(self,data):
#!/usr/bin/env python
import time
from datetime import datetime
from sqlalchemy import (MetaData, create_engine, Table, Column,
Integer, Numeric, String, ForeignKey, DateTime,
insert, select)
metadata = MetaData()
cookies = Table('cookies', metadata,
Column('cookie_id', Integer(), primary_key=True),
Column('cookie_name', String(50), index=True, nullable=False),
Column('cost', Numeric(12, 2)),
Column('added', DateTime(), default=datetime.now)
)
#ins = cookies.insert().values(
# cookie_name="chocolate chip",
# cost="1.5"
#)
# also possible ins = insert(cookies).values( ...
#print(str(ins))
# direct DB access
#engine = create_engine('postgresql://*****:*****@127.0.0.1:'\
# '5432/learn_sqlalchemy')
engine = create_engine('postgresql://*****:*****@127.0.0.1:'\
'6543/learn_sqlalchemy')
metadata.create_all(engine)
class unix_timestamp(expression.FunctionElement):
type = Integer()
class User(Base):
__tablename__ = 'user'
user_id = Column(Integer(), primary_key=True, unique=True, nullable=False)
name = Column(String(20))
class Foo(Base):
__tablename__ = "foo"
id: int = Column(Integer(), primary_key=True)
name: Mapped[str] = Column(String)
bar: List["Bar"] = relationship("Bar")
class RolesUsers(Base):
__tablename__ = 'roles_users'
id = Column(Integer(), primary_key=True)
user_id = Column('user_id', Integer(), ForeignKey('user.id'))
role_id = Column('role_id', Integer(), ForeignKey('role.id'))
class Bar(Base):
__tablename__ = "bar"
id: int = Column(Integer(), primary_key=True)
foo_id: int = Column(ForeignKey("foo.id"))
foo: Optional[Foo] = relationship(Foo)
# In[1]:
import sqlalchemy
import datetime
from sqlalchemy import create_engine
engine = create_engine('sqlite:///listings.db')
from sqlalchemy import (MetaData, Table, Column, Integer, Numeric, String,
DateTime, Boolean, ForeignKey, create_engine)
metadata = MetaData()
employees = Table('employees',
metadata,
Column('id', Integer(), primary_key=True),
Column('name', String(32), nullable=False),
Column('surname', String(32), nullable=False),
Column('birthdate', DateTime(), nullable=False),
Column('position', String(32), nullable=False),
Column('salary', Integer(), nullable=False),
Column('contract_date', DateTime(), nullable=False),
extend_existing=True)
personal_info = Table('personal_info',
metadata,
Column('id', Integer(), primary_key=True),
Column('city', String(32), nullable=False),
Column('birthdate', DateTime(), nullable=False),
Column('adress_one', String(32), nullable=False),
Column('adress_two', String(32)),
def test_numerics_broken_inspection(self, metadata, connection):
"""Numeric scenarios where Oracle type info is 'broken',
returning us precision, scale of the form (0, 0) or (0, -127).
We convert to Decimal and let int()/float() processors take over.
"""
# this test requires cx_oracle 5
foo = Table(
"foo",
metadata,
Column("idata", Integer),
Column("ndata", Numeric(20, 2)),
Column("ndata2", Numeric(20, 2)),
Column("nidata", Numeric(5, 0)),
Column("fdata", Float()),
)
foo.create(connection)
connection.execute(
foo.insert(),
{
"idata": 5,
"ndata": decimal.Decimal("45.6"),
"ndata2": decimal.Decimal("45.0"),
"nidata": decimal.Decimal("53"),
"fdata": 45.68392,
},
)
stmt = "SELECT idata, ndata, ndata2, nidata, fdata FROM foo"
row = exec_sql(connection, stmt).fetchall()[0]
eq_(
[type(x) for x in row],
[int, decimal.Decimal, decimal.Decimal, int, float],
)
eq_(
row,
(
5,
decimal.Decimal("45.6"),
decimal.Decimal("45"),
53,
45.683920000000001,
),
)
# with a nested subquery,
# both Numeric values that don't have decimal places, regardless
# of their originating type, come back as ints with no useful
# typing information beyond "numeric". So native handler
# must convert to int.
# this means our Decimal converters need to run no matter what.
# totally sucks.
stmt = """
SELECT
(SELECT (SELECT idata FROM foo) FROM DUAL) AS idata,
(SELECT CAST((SELECT ndata FROM foo) AS NUMERIC(20, 2)) FROM DUAL)
AS ndata,
(SELECT CAST((SELECT ndata2 FROM foo) AS NUMERIC(20, 2)) FROM DUAL)
AS ndata2,
(SELECT CAST((SELECT nidata FROM foo) AS NUMERIC(5, 0)) FROM DUAL)
AS nidata,
(SELECT CAST((SELECT fdata FROM foo) AS FLOAT) FROM DUAL) AS fdata
FROM dual
"""
row = exec_sql(connection, stmt).fetchall()[0]
eq_(
[type(x) for x in row],
[int, decimal.Decimal, int, int, decimal.Decimal],
)
eq_(
row,
(5, decimal.Decimal("45.6"), 45, 53, decimal.Decimal("45.68392")),
)
row = connection.execute(
text(stmt).columns(
idata=Integer(),
ndata=Numeric(20, 2),
ndata2=Numeric(20, 2),
nidata=Numeric(5, 0),
fdata=Float(),
)).fetchall()[0]
eq_(
[type(x) for x in row],
[int, decimal.Decimal, decimal.Decimal, decimal.Decimal, float],
)
eq_(
row,
(
5,
decimal.Decimal("45.6"),
decimal.Decimal("45"),
decimal.Decimal("53"),
45.683920000000001,
),
)
stmt = """
SELECT
anon_1.idata AS anon_1_idata,
anon_1.ndata AS anon_1_ndata,
anon_1.ndata2 AS anon_1_ndata2,
anon_1.nidata AS anon_1_nidata,
anon_1.fdata AS anon_1_fdata
FROM (SELECT idata, ndata, ndata2, nidata, fdata
FROM (
SELECT
(SELECT (SELECT idata FROM foo) FROM DUAL) AS idata,
(SELECT CAST((SELECT ndata FROM foo) AS NUMERIC(20, 2))
FROM DUAL) AS ndata,
(SELECT CAST((SELECT ndata2 FROM foo) AS NUMERIC(20, 2))
FROM DUAL) AS ndata2,
(SELECT CAST((SELECT nidata FROM foo) AS NUMERIC(5, 0))
FROM DUAL) AS nidata,
(SELECT CAST((SELECT fdata FROM foo) AS FLOAT) FROM DUAL)
AS fdata
FROM dual
)
WHERE ROWNUM >= 0) anon_1
"""
row = exec_sql(connection, stmt).fetchall()[0]
eq_(
[type(x) for x in row],
[int, decimal.Decimal, int, int, decimal.Decimal],
)
eq_(
row,
(5, decimal.Decimal("45.6"), 45, 53, decimal.Decimal("45.68392")),
)
row = connection.execute(
text(stmt).columns(
anon_1_idata=Integer(),
anon_1_ndata=Numeric(20, 2),
anon_1_ndata2=Numeric(20, 2),
anon_1_nidata=Numeric(5, 0),
anon_1_fdata=Float(),
)).fetchall()[0]
eq_(
[type(x) for x in row],
[int, decimal.Decimal, decimal.Decimal, decimal.Decimal, float],
)
eq_(
row,
(
5,
decimal.Decimal("45.6"),
decimal.Decimal("45"),
decimal.Decimal("53"),
45.683920000000001,
),
)
row = connection.execute(
text(stmt).columns(
anon_1_idata=Integer(),
anon_1_ndata=Numeric(20, 2, asdecimal=False),
anon_1_ndata2=Numeric(20, 2, asdecimal=False),
anon_1_nidata=Numeric(5, 0, asdecimal=False),
anon_1_fdata=Float(asdecimal=True),
)).fetchall()[0]
eq_([type(x) for x in row],
[int, float, float, float, decimal.Decimal])
eq_(row, (5, 45.6, 45, 53, decimal.Decimal("45.68392")))
from sqlalchemy import Column, DateTime, Integer, MetaData, String, Table
from sqlalchemy import Text, Boolean, ForeignKey
from engine import log as logging
meta = MetaData()
# Just for the ForeignKey and column creation to succeed, these are not the
# actual definitions of tables .
#
volumes = Table(
'volumes',
meta,
Column('id', Integer(), primary_key=True, nullable=False),
)
volume_type_id = Column('volume_type_id', Integer(), nullable=True)
# New Tables
#
volume_types = Table(
'volume_types', meta, Column('created_at', DateTime(timezone=False)),
Column('updated_at', DateTime(timezone=False)),
Column('deleted_at', DateTime(timezone=False)),
Column('deleted', Boolean(create_constraint=True, name=None)),
Column('id', Integer(), primary_key=True, nullable=False),
Column('name',
String(length=255,
class RolesUsers(db.Model):
__tablename__ = 'roles_users'
id = Column(Integer(), primary_key=True)
user_id = Column('user_id', UUID(as_uuid=True), ForeignKey('users.id'))
role_id = Column('role_id', UUID(as_uuid=True), ForeignKey('roles.id'))
class SaveDataMixin(object):
""" The classmethods must not be implemented on a class that inherits
from ``Base`` with ``SQLAlchemy>=1.0``, otherwise that class cannot be
subclassed further.
See http://stackoverflow.com/questions/30433960/how-to-use-declare-last-in-sqlalchemy-1-0 # noqa
"""
#: The filename is used in the attachment view to give downloads
#: the original filename it had when it was uploaded.
#: (:class:`sqlalchemy.types.Unicode`)
filename = Column(Unicode(100))
#: MIME type of the file
#: (:class:`sqlalchemy.types.String`)
mimetype = Column(String(100))
#: Size of the file in bytes
#: (:class:`sqlalchemy.types.Integer`)
size = Column(Integer())
#: Filedepot mapped blob
#: (:class:`depot.fileds.sqlalchemy.UploadedFileField`)
@declared_attr
def data(cls) -> Column:
return cls.__table__.c.get("data",
Column(UploadedFileField(cls.data_filters)))
data_filters = ()
@classmethod
def __declare_last__(cls) -> None:
""" Unconfigure the event set in _SQLAMutationTracker,
we have _save_data """
mapper = cls._sa_class_manager.mapper
args = (mapper.attrs["data"], "set", _SQLAMutationTracker._field_set)
if event.contains(*args):
event.remove(*args)
# Declaring the event on the class attribute instead of mapper property
# enables proper registration on its subclasses
event.listen(cls.data, "set", cls._save_data, retval=True)
@staticmethod
def _save_data(
target: "File",
value: Optional[Union[FieldStorage, bytes, UploadedFile,
BufferedReader]], # noqa
oldvalue: Optional[Union[UploadedFile, _symbol]],
initiator: Event,
) -> Optional[UploadedFile]:
""" Refresh metadata and save the binary data to the data field.
:param target: The File instance
:type target: :class:`kotti.resources.File` or subclass
:param value: The container for binary data
:type value: A :class:`cgi.FieldStorage` instance
"""
if isinstance(value, bytes):
fp = BytesIO(value)
value = _to_fieldstorage(
fp=fp,
filename=target.filename,
mimetype=target.mimetype,
size=len(value),
)
newvalue = _SQLAMutationTracker._field_set(target, value, oldvalue,
initiator)
if newvalue is None:
return
target.filename = newvalue.filename
target.mimetype = newvalue.content_type
target.size = newvalue.file.content_length
return newvalue
@classmethod
def from_field_storage(cls, fs):
""" Create and return an instance of this class from a file upload
through a webbrowser.
:param fs: FieldStorage instance as found in a
:class:`kotti.request.Request`'s ``POST`` MultiDict.
:type fs: :class:`cgi.FieldStorage`
:result: The created instance.
:rtype: :class:`kotti.resources.File`
"""
if not cls.type_info.is_uploadable_mimetype(fs.type):
raise ValueError("Unsupported MIME type: {0}".format(fs.type))
return cls(data=fs)
def __init__(
self,
data: Optional[Union[bytes, BufferedReader,
FieldStorage]] = None, # noqa
filename: Optional[str] = None,
mimetype: Optional[str] = None,
size: Optional[int] = None,
**kwargs) -> None:
super(SaveDataMixin, self).__init__(**kwargs)
self.filename = filename
self.mimetype = mimetype
self.size = size
self.data = data
def copy(self, **kwargs) -> "File":
""" Same as `Content.copy` with additional data support. ``data`` needs
some special attention, because we don't want the same depot file to be
assigned to multiple content nodes.
"""
_copy = super(SaveDataMixin, self).copy(**kwargs)
_copy.data = self.data.file.read()
return _copy
def wijzArt(m_email, martikelnr):
metadata = MetaData()
artikelen = Table('artikelen', metadata,
Column('artikelID', Integer(), primary_key=True),
Column('artikelomschrijving', String),
Column('artikelprijs', Float),
Column('art_voorraad', Float),
Column('art_eenheid', String(20)),
Column('art_min_voorraad', Float),
Column('art_bestelgrootte', Float),
Column('locatie_magazijn', String(10)),
Column('artikelgroep', String),
Column('barcode', Integer),
Column('thumb_artikel', String(70)),
Column('foto_artikel', String(70)),
Column('categorie', String(10)),
Column('afmeting', String))
engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem')
conn = engine.connect()
sel = select([artikelen]).where(artikelen.c.artikelID == martikelnr)
rpartikel = conn.execute(sel).first()
conn.close()
class Widget(QDialog):
def __init__(self):
super(Widget, self).__init__()
self.setWindowTitle("Artikelen wijzigen")
self.setWindowIcon(QIcon('./images/logos/logo.jpg'))
self.setFont(QFont('Arial', 10))
self.Artikelnummer = QLabel()
q1Edit = QLineEdit(str(rpartikel[0]))
q1Edit.setFixedWidth(100)
q1Edit.setDisabled(True)
q1Edit.setFont(QFont("Arial",10))
q1Edit.textChanged.connect(self.q1Changed)
self.Artikelomschrijving = QLabel()
q2Edit = QLineEdit(str(rpartikel[1]))
q2Edit.setFixedWidth(400)
q2Edit.setFont(QFont("Arial",10))
q2Edit.textChanged.connect(self.q2Changed)
self.Artikelprijs = QLabel()
q3Edit = QLineEdit(str(rpartikel[2]))
q3Edit.setFixedWidth(100)
q3Edit.setFont(QFont("Arial",10))
q3Edit.textChanged.connect(self.q3Changed)
reg_ex = QRegExp('^[0-9]{1,10}[.0-9]{0,3}$')
input_validator = QRegExpValidator(reg_ex, q3Edit)
q3Edit.setValidator(input_validator)
self.Artikelvoorraad = QLabel()
q4Edit = QLineEdit(str(rpartikel[3]))
q4Edit.setFixedWidth(100)
q4Edit.setFont(QFont("Arial",10))
q4Edit.setDisabled(True)
q4Edit.textChanged.connect(self.q4Changed)
reg_ex = QRegExp('^[0-9]{0,10}[.0-9]{0,3}$')
input_validator = QRegExpValidator(reg_ex, q4Edit)
q4Edit.setValidator(input_validator)
self.Artikeleenheid = QLabel()
q5Edit = QComboBox()
q5Edit.setFixedWidth(140)
q5Edit.setFont(QFont("Arial",10))
q5Edit.setStyleSheet("color: black; background-color: gainsboro")
q5Edit.addItem(' Maak uw keuze')
q5Edit.addItem('stuk')
q5Edit.addItem('100')
q5Edit.addItem('meter')
q5Edit.addItem('kg')
q5Edit.addItem('liter')
q5Edit.addItem('m²')
q5Edit.addItem('m³')
q5Edit.activated[str].connect(self.q5Changed)
self.Minimumvoorraad = QLabel()
q6Edit = QLineEdit(str(rpartikel[5]))
q6Edit.setFixedWidth(100)
q6Edit.setFont(QFont("Arial",10))
q6Edit.textChanged.connect(self.q6Changed)
reg_ex = QRegExp('^[0-9]{1,10}[.0-9]{0,3}$')
input_validator = QRegExpValidator(reg_ex, q6Edit)
q6Edit.setValidator(input_validator)
self.Bestelgrootte = QLabel()
q7Edit = QLineEdit(str(rpartikel[6]))
q7Edit.setFixedWidth(100)
q7Edit.setFont(QFont("Arial",10))
q7Edit.textChanged.connect(self.q7Changed)
reg_ex = QRegExp('^[0-9]{1,10}[.0-9]{0,3}$')
input_validator = QRegExpValidator(reg_ex, q7Edit)
q7Edit.setValidator(input_validator)
self.Magazijnlocatie = QLabel()
q8Edit = QLineEdit(str(rpartikel[7]))
q8Edit.setFixedWidth(100)
q8Edit.setFont(QFont("Arial",10))
q8Edit.textChanged.connect(self.q8Changed)
self.Artikelgroep = QLabel()
q9Edit = QLineEdit(str(rpartikel[8]))
q9Edit.setFixedWidth(200)
q9Edit.setFont(QFont("Arial",10))
q9Edit.textChanged.connect(self.q9Changed)
self.Barcode = QLabel()
q10Edit = QLineEdit(str(rpartikel[9]))
q10Edit.setFixedWidth(100)
q10Edit.setFont(QFont("Arial",10))
q10Edit.textChanged.connect(self.q10Changed)
reg_ex = QRegExp('^[1-9]{1,13}$')
input_validator = QRegExpValidator(reg_ex, q10Edit)
q10Edit.setValidator(input_validator)
self.Artikelthumbnail = QLabel()
q11Edit = QLineEdit(str(rpartikel[10]))
q11Edit.setFixedWidth(400)
q11Edit.setFont(QFont("Arial",10))
q11Edit.textChanged.connect(self.q11Changed)
self.Artikelfoto = QLabel()
q12Edit = QLineEdit(str(rpartikel[11]))
q12Edit.setFixedWidth(400)
q12Edit.setFont(QFont("Arial",10))
q12Edit.textChanged.connect(self.q12Changed)
self.Categorie = QLabel()
q13Edit = QComboBox()
q13Edit.setFixedWidth(270)
q13Edit.setFont(QFont("Arial",10))
q13Edit.setStyleSheet("color: black; background-color: gainsboro")
q13Edit.addItem(' Maak uw keuze')
q13Edit.addItem('1. Voorraadgestuurd prijs < 5')
q13Edit.addItem('2. Voorraadgestuurd prijs < 20')
q13Edit.addItem('3. Voorraadgestuurd prijs < 500')
q13Edit.addItem('4. Voorraadgestuurd prijs > 500')
q13Edit.addItem('5. Reserveringgestuurd prijs < 5')
q13Edit.addItem('6. Reserveringgestuurd prijs < 20')
q13Edit.addItem('7. Reserveringgestuurd prijs < 500')
q13Edit.addItem('8. Reserveringgestuurd prijs > 500')
q13Edit.activated[str].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, 1, Qt.AlignRight)
self.setFont(QFont('Arial', 10))
grid.addWidget(QLabel('Wijzigen artikel'), 1, 1)
grid.addWidget(QLabel(' *'), 2, 0)
grid.addWidget(QLabel('Verplichte velden'), 2, 1)
grid.addWidget(QLabel('Artikelnummer'), 3, 0)
grid.addWidget(q1Edit, 3, 1)
grid.addWidget(QLabel('Artikelomschrijving *'), 4, 0)
grid.addWidget(q2Edit, 4, 1)
grid.addWidget(QLabel('Artikelprijs *'), 5, 0)
grid.addWidget(q3Edit, 5 , 1)
grid.addWidget(QLabel('Artikelvoorraad'), 6, 0)
grid.addWidget(q4Edit, 6, 1)
grid.addWidget(QLabel(' '+str(rpartikel[4])), 7, 0)
grid.addWidget(QLabel('Eenheid *'), 7, 0)
grid.addWidget(q5Edit, 7, 1)
grid.addWidget(QLabel('Minimumvoorraad *'), 8, 0)
grid.addWidget(q6Edit, 8, 1)
grid.addWidget(QLabel('Bestelgrootte *'), 9, 0)
grid.addWidget(q7Edit, 9, 1)
grid.addWidget(QLabel('Magazijnlocatie *'), 10, 0)
grid.addWidget(q8Edit, 10, 1)
grid.addWidget(QLabel('Artikelgroep *'), 11, 0)
grid.addWidget(q9Edit, 11, 1)
grid.addWidget(QLabel('Barcode'), 12, 0)
grid.addWidget(q10Edit, 12, 1)
grid.addWidget(QLabel('Artikelthumbnail'), 13, 0)
grid.addWidget(q11Edit, 13, 1)
grid.addWidget(QLabel('Artikelfoto'), 14, 0)
grid.addWidget(q12Edit, 14, 1)
grid.addWidget(QLabel(' '+str(rpartikel[12])), 15, 0)
grid.addWidget(QLabel('Categorie *'),15,0)
grid.addWidget(q13Edit, 15, 1)
grid.addWidget(QLabel('\u00A9 2017 all rights reserved [email protected]'), 16, 1)
applyBtn = QPushButton('Wijzigen')
applyBtn.clicked.connect(self.accept)
grid.addWidget(applyBtn, 15, 2)
applyBtn.setFont(QFont("Arial",10))
applyBtn.setFixedWidth(100)
applyBtn.setStyleSheet("color: black; background-color: gainsboro")
cancelBtn = QPushButton('Sluiten')
cancelBtn.clicked.connect(lambda: wijzSluit(self, m_email))
grid.addWidget(cancelBtn, 14, 2)
cancelBtn.setFont(QFont("Arial",10))
cancelBtn.setFixedWidth(100)
cancelBtn.setStyleSheet("color: black; background-color: gainsboro")
self.setLayout(grid)
self.setGeometry(500, 100, 350, 300)
def q1Changed(self, text):
self.Artikelnummer.setText(text)
def q2Changed(self, text):
self.Artikelomschrijving.setText(text)
def q3Changed(self, text):
self.Artikelprijs.setText(text)
def q4Changed(self, text):
self.Artikelvoorraad.setText(text)
def q5Changed(self, text):
self.Artikeleenheid.setText(text)
def q6Changed(self, text):
self.Minimumvoorraad.setText(text)
def q7Changed(self, text):
self.Bestelgrootte.setText(text)
def q8Changed(self, text):
self.Magazijnlocatie.setText(text)
def q9Changed(self, text):
self.Artikelgroep.setText(text)
def q10Changed(self, text):
self.Barcode.setText(text)
def q11Changed(self, text):
self.Artikelthumbnail.setText(text)
def q12Changed(self, text):
self.Artikelfoto.setText(text)
def q13Changed(self, text):
self.Categorie.setText(text)
def returnArtikelnummer(self):
return self.Artikelnummer.text()
def returnArtikelomschrijving(self):
return self.Artikelomschrijving.text()
def returnArtikelprijs(self):
return self.Artikelprijs.text()
def returnArtikelvoorraad(self):
return self.Artikelvoorraad.text()
def returnArtikeleenheid(self):
return self.Artikeleenheid.text()
def returnMinimumvoorraad(self):
return self.Minimumvoorraad.text()
def returnBestelgrootte(self):
return self.Bestelgrootte.text()
def returnMagazijnlocatie(self):
return self.Magazijnlocatie.text()
def returnArtikelgroep(self):
return self.Artikelgroep.text()
def returnBarcode(self):
return self.Barcode.text()
def returnArtikelthumbnail(self):
return self.Artikelthumbnail.text()
def returnArtikelfoto(self):
return self.Artikelfoto.text()
def returnCategorie(self):
return self.Categorie.text()
@staticmethod
def getData(parent=None):
dialog = Widget()
dialog.exec_()
return [dialog.returnArtikelnummer(),dialog.returnArtikelomschrijving(),\
dialog.returnArtikelprijs(), dialog.returnArtikelvoorraad(),\
dialog.returnArtikeleenheid(), dialog.returnMinimumvoorraad(),\
dialog.returnBestelgrootte(), dialog.returnMagazijnlocatie(),\
dialog.returnArtikelgroep(), dialog.returnBarcode(),\
dialog.returnArtikelthumbnail(),dialog.returnArtikelfoto(),\
dialog.returnCategorie()]
window = Widget()
data = window.getData()
if data[1]:
martomschr = data[1]
else:
martomschr = rpartikel[1]
if data[2]:
martprijs = str(data[2])
else:
martprijs = str(rpartikel[2])
if data[3]:
martvrd = str(data[3])
else:
martvrd = str(rpartikel[3])
if data[4]:
marteenh = data[4]
else:
marteenh = rpartikel[4]
if data[5]:
martminvrd = str(data[5])
else:
martminvrd = str(rpartikel[5])
if data[6]:
martbestgr = str(data[6])
else:
martbestgr = str(rpartikel[6])
if data[7]:
mlocmag = data[7]
else:
mlocmag = rpartikel[7]
if data[8]:
martgr = data[8]
else:
martgr = rpartikel[8]
if data[9]:
mbarc = int(data[9])
else:
mbarc = int(rpartikel[9])
if data[10]:
mthumb = data[10]
else:
mthumb = rpartikel[10]
if data[11]:
mfotoart = data[11]
else:
mfotoart = rpartikel[11]
if data[12]:
mcat = str(data[12])[0]
else:
mcat = str(rpartikel[12])
engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem')
conn = engine.connect()
u = update(artikelen).where(artikelen.c.artikelID == martikelnr).\
values(artikelomschrijving = martomschr, artikelprijs = martprijs,\
art_voorraad = martvrd, art_eenheid = marteenh, art_min_voorraad = \
martminvrd, art_bestelgrootte = martbestgr, locatie_magazijn = mlocmag,\
artikelgroep = martgr, barcode = mbarc, thumb_artikel = mthumb,\
foto_artikel = mfotoart, categorie = mcat)
conn.execute(u)
conn.close()
updateOK()
zoekArtikel(m_email)
class Drink(db.Model):
# Autoincrementing, unique primary key
id = Column(Integer().with_variant(Integer, "sqlite"), primary_key=True)
# String Title
title = Column(String(80), unique=True)
# the ingredients blob - this stores a lazy json blob
'''
the required datatype is
[{'color': string, 'name':string, 'parts':number}]
'''
recipe = Column(String(180), nullable=False)
'''
short()
short form representation of the Drink model
'''
def short(self):
try:
short_recipe = [{
'color': r['color'],
'parts': r['parts']
} for r in self['recipe']]
return {
'id': self['id'],
'title': self['title'],
'recipe': short_recipe
}
except Exception as e:
print(e)
'''
long()
long form representation of the Drink model
'''
def long(self):
try:
return {
'id': self.id,
'title': self.title,
'recipe': json.loads(self.recipe)
}
except Exception as e:
print(e)
'''
insert()
inserts a new model into a database
the model must have a unique name
the model must have a unique id or null id
EXAMPLE
drink = Drink(title=req_title, recipe=req_recipe)
drink.insert()
'''
def insert(self):
try:
db.session.add(self)
db.session.commit()
except Exception as e:
print(e)
'''
delete()
deletes a new model into a database
the model must exist in the database
EXAMPLE
drink = Drink(title=req_title, recipe=req_recipe)
drink.delete()
'''
def delete(self):
try:
db.session.delete(self)
db.session.commit()
except Exception as e:
print(e)
'''
update()
updates a new model into a database
the model must exist in the database
EXAMPLE
drink = Drink.query.filter(Drink.id == id).one_or_none()
drink.title = 'Black Coffee'
drink.update()
'''
def update(self):
try:
db.session.commit()
except Exception as e:
print(e)
def __repr__(self):
return json.dumps(self.long())
def zoekArtikel(m_email):
class Widget(QDialog):
def __init__(self, parent=None):
super(Widget, self).__init__(parent)
self.setWindowTitle("Artikelen Wijzigen.")
self.setWindowIcon(QIcon('./images/logos/logo.jpg'))
self.setFont(QFont('Arial', 10))
self.Artikelnummer = QLabel()
artEdit = QLineEdit()
artEdit.setFixedWidth(100)
artEdit.setFont(QFont("Arial",10))
artEdit.textChanged.connect(self.artChanged)
reg_ex = QRegExp('^[2]{1}[0-9]{8}$')
input_validator = QRegExpValidator(reg_ex, artEdit)
artEdit.setValidator(input_validator)
grid = QGridLayout()
grid.setSpacing(20)
lbl = QLabel()
pixmap = QPixmap('./images/logos/verbinding.jpg')
lbl.setPixmap(pixmap)
grid.addWidget(lbl , 0, 0, 1, 2)
logo = QLabel()
pixmap = QPixmap('./images/logos/logo.jpg')
logo.setPixmap(pixmap)
grid.addWidget(logo , 0, 2, 1, 1, Qt.AlignRight)
self.setFont(QFont('Arial', 10))
grid.addWidget(QLabel('Artikelnummer'), 1, 1)
grid.addWidget(artEdit, 1, 2)
cancelBtn = QPushButton('Sluiten')
cancelBtn.clicked.connect(lambda: windowSluit(self, m_email))
applyBtn = QPushButton('Zoeken')
applyBtn.clicked.connect(self.accept)
grid.addWidget(applyBtn, 2, 2, 1, 1, Qt.AlignRight)
applyBtn.setFont(QFont("Arial",10))
applyBtn.setFixedWidth(100)
applyBtn.setStyleSheet("color: black; background-color: gainsboro")
grid.addWidget(cancelBtn, 2, 1, 1, 1, Qt.AlignRight)
cancelBtn.setFont(QFont("Arial",10))
cancelBtn.setFixedWidth(100)
cancelBtn.setStyleSheet("color: black; background-color: gainsboro")
grid.addWidget(QLabel('\u00A9 2017 all rights reserved [email protected]'), 3, 0, 1, 3, Qt.AlignCenter)
self.setLayout(grid)
self.setGeometry(500, 100, 150, 150)
def artChanged(self, text):
self.Artikelnummer.setText(text)
def returnArtikelnummer(self):
return self.Artikelnummer.text()
@staticmethod
def getData(parent=None):
dialog = Widget(parent)
dialog.exec_()
return [dialog.returnArtikelnummer()]
window = Widget()
data = window.getData()
if data[0]:
martikelnr = data[0]
else:
martikelnr = 0
metadata = MetaData()
artikelen = Table('artikelen', metadata,
Column('artikelID', Integer(), primary_key=True))
engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem')
conn = engine.connect()
s = select([artikelen]).where(artikelen.c.artikelID == int(martikelnr))
rp = conn.execute(s).first()
if len(martikelnr) == 9 and _11check(martikelnr) and rp:
martikelnr = int(martikelnr)
else:
foutArtnr()
zoekArtikel(m_email)
wijzArt(m_email, martikelnr)
class AnnouncementCategories(Base):
"""Announcement Categories"""
__tablename__ = "announcements_categories"
id = Column(Integer(), primary_key=True, nullable=False)
name = Column(String(1024), nullable=False)
from sqlalchemy import PrimaryKeyConstraint
from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy import Table
from sqlalchemy import Unicode
from sqlalchemy import UnicodeText
from sqlalchemy.orm import backref
from sqlalchemy.orm import relationship
from timesketch.models import BaseModel
from timesketch.models.annotations import LabelMixin
from timesketch.models.annotations import StatusMixin
# Helper table for Groups many-to-many relationship.
user_group = Table('user_group', BaseModel.metadata,
Column('user_id', Integer(), ForeignKey('user.id')),
Column('group_id', Integer(), ForeignKey('group.id')),
PrimaryKeyConstraint('user_id', 'group_id'))
class User(UserMixin, BaseModel):
"""Implements the User model."""
username = Column(Unicode(255), unique=True)
password = Column(Unicode(128))
name = Column(Unicode(255))
email = Column(Unicode(255))
active = Column(Boolean(), default=True)
admin = Column(Boolean(), default=False)
sketches = relationship('Sketch', backref='user', lazy='dynamic')
searchindices = relationship('SearchIndex', backref='user', lazy='dynamic')
class Task(Base):
"""Analysis task queue."""
__tablename__ = "tasks"
id = Column(Integer(), primary_key=True)
target = Column(Text(), nullable=False)
category = Column(String(255), nullable=False)
timeout = Column(Integer(), server_default="0", nullable=False)
priority = Column(Integer(), server_default="1", nullable=False)
custom = Column(String(255), nullable=True)
machine = Column(String(255), nullable=True)
package = Column(String(255), nullable=True)
tags = relationship("Tag", secondary=tasks_tags, cascade="all, delete",
single_parent=True, backref=backref("task", cascade="all"),
lazy="subquery")
options = Column(String(255), nullable=True)
platform = Column(String(255), nullable=True)
memory = Column(Boolean, nullable=False, default=False)
enforce_timeout = Column(Boolean, nullable=False, default=False)
clock = Column(DateTime(timezone=False),
default=datetime.now,
nullable=False)
added_on = Column(DateTime(timezone=False),
default=datetime.now,
nullable=False)
started_on = Column(DateTime(timezone=False), nullable=True)
completed_on = Column(DateTime(timezone=False), nullable=True)
status = Column(Enum(TASK_PENDING, TASK_RUNNING, TASK_COMPLETED,
TASK_REPORTED, TASK_RECOVERED, name="status_type"),
server_default=TASK_PENDING,
nullable=False)
sample_id = Column(Integer, ForeignKey("samples.id"), nullable=True)
sample = relationship("Sample", backref="tasks")
guest = relationship("Guest", uselist=False, backref="tasks", cascade="save-update, delete")
errors = relationship("Error", backref="tasks", cascade="save-update, delete")
def to_dict(self):
"""Converts object to dict.
@return: dict
"""
d = {}
for column in self.__table__.columns:
value = getattr(self, column.name)
if isinstance(value, datetime):
d[column.name] = value.strftime("%Y-%m-%d %H:%M:%S")
else:
d[column.name] = value
# Tags are a relation so no column to iterate.
d["tags"] = [tag.name for tag in self.tags]
return d
def to_json(self):
"""Converts object to JSON.
@return: JSON data
"""
return json.dumps(self.to_dict())
def __init__(self, target=None):
self.target = target
def __repr__(self):
return "<Task('{0}','{1}')>".format(self.id, self.target)
class IPBanRecord(Database.Base):
'''
Record for a single IP ban.
'''
##
## Database configuration
##
__tablename__ = "ipbans"
id = Column(Integer, primary_key=True)
##
## Internal IP address storage schema.
##
Low64 = Column(BigInteger(unsigned=True), index=True)
'''Low 64 bits of an IPv6 address. Set to None for IPv4 addresses.'''
Mid32 = Column(Integer(unsigned=True))
'''"Middle" 32 bits of an IPv6 address. Set to None for IPv4 addresses.'''
High32 = Column(Integer(unsigned=True))
'''High 32 bits of an IPv6 address, or the entire IPv4 address.'''
CIDR = Column(SmallInteger(unsigned=True))
'''CIDR notation prefix size. 0 to 32 for IPv4, 0 to 128 for IPv6.'''
##
## Ban time options
##
Created = Column(DateTime)
'''Date and time at which the ban was created.'''
Permanent = Column(Boolean)
'''If True, the ban is treated as permanent.'''
Expires = Column(DateTime)
'''Date and time at which the ban will expire.'''
##
## Misc. information
##
Comment = Column(String(64))
'''Optional comment explaining the reason for the ban.'''
##
## String conversion functions
##
def ToString(self):
'''
Returns the banned address in CIDR notation.
'''
# IPv4 or IPv6?
if self.Low64 != 0 or self.Mid32 != 0:
# IPv6
packed = _IPv6Struct.pack(self.Low64, self.Mid32, self.High32)
address = socket.inet_ntop(socket.AF_INET6, packed)
return "%s/%i" % (address, self.CIDR)
else:
# IPv4
packed = _IPv4Struct.pack(self.High32)
address = socket.inet_ntoa(packed)
return "%s/%i" % (address, self.CIDR)
def FromString(self, address):
'''
Fills in the database record from an address given in CIDR notation.
@type address: string
@param address: CIDR notation address to store.
@raise ValueError: Raised if an invalid address is supplied.
'''
# IPv4 or IPv6?
if address.find(":") == -1:
# IPv4
parts = address.split("/")
if len(parts) > 2: raise ValueError
elif len(parts) == 2: addr, cidr = parts
else:
# no CIDR prefix specified, assuming /32
addr = parts[0]
cidr = 32
try:
self.High32 = _IPv4Struct.unpack(socket.inet_aton(addr))[0]
except:
raise ValueError
self.Low64 = 0
self.Mid32 = 0
self.CIDR = int(cidr)
else:
# IPv6
parts = address.split("/")
if len(parts) > 2: raise ValueError
elif len(parts) == 2: addr, cidr = parts
else:
# no CIDR prefix specified, assuming /128
addr = parts[0]
cidr = 128
try:
chunk = socket.inet_pton(socket.AF_INET6, addr)
except:
raise ValueError
self.Low64, self.Mid32, self.High32 = _IPv6Struct.unpack(chunk)
self.CIDR = int(cidr)
def __repr__(self):
return "<IPBanRecord (%s)>" % self.ToString()
##
## Ban check methods
##
def ValidBan(self):
'''
Checks that this ban is valid (i.e. not expired).
@return: Boolean.
'''
if self.Permanent: return True
now = datetime.datetime.now()
return now < self.Expires
def MatchString(self, address):
'''
Matches an address as a string.
@type address: string
@param address: Address to check for match.
@raise ValueError: Raised if an invalid address is supplied.
'''
# IPv4 or IPv6?
if address.find(":") == -1:
# IPv4
try:
packed = socket.inet_aton(address)
except:
raise ValueError
return self.MatchPackedIPv4(packed)
else:
# IPv6
try:
packed = socket.inet_pton(socket.AF_INET6, address)
except:
raise ValueError
return self.MatchPackedIPv6(packed)
def MatchPackedIPv4(self, packed):
'''
Matches a packed IPv4 address (i.e. the return value of inet_aton).
@type packed: string
@param packed: Packed IPv4 address.
@return: Boolean, True if the address matches this ban.
'''
# If this is an IPv6 address ban, automatically dematch.
if self.Low64 > 0 or self.Mid32 > 0: return False
# Extract address information.
addr = _IPv4Struct.unpack(packed)[0]
# Strip non-prefix bits.
deltaCIDR = 32 - self.CIDR
addr &= 0xffffffff - (2**deltaCIDR - 1)
# Check for match.
return addr == self.High32
def MatchPackedIPv6(self, packed):
'''
Matches a packed IPv6 address (i.e. the return value of inet_pton).
@type packed: string
@param packed: Packed IPv6 address.
@return: Boolean, True if the address matches this ban.
'''
# If this is an IPv4 address an, automatically dematch.
if self.Low64 == 0 and self.Mid32 == 0: return False
# Extract address information.
low, mid, high = _IPv6Struct.unpack(packed)
if (low == 0 and mid == 0) and (self.Low64 > 0 or self.Mid32 > 0):
# This got passed an IPv4 address (why?), no match.
return False
# Figure out where the CIDR split is.
if self.CIDR < 64:
# Low 64
deltaCIDR = 64 - self.CIDR
high = 0
mid = 0
low &= 0xffffffffffffffff - (2**deltaCIDR - 1)
elif self.CIDR < 96:
# Mid 32
deltaCIDR = 96 - self.CIDR
high = 0
mid &= 0xffffffff - (2**deltaCIDR - 1)
# low = low
else:
# High 32
deltaCIDR = 128 - self.CIDR
high &= 0xffffffff - (2**deltaCIDR - 1)
# mid = mid
# low = low
# Check for match.
return high == self.High32 and mid == self.Mid32 and low == self.Low64
