示例#1
0
 def load_dialect_impl(self, dialect):
     if dialect.name == 'sqlite':
         return dialect.type_descriptor(Integer())
     else:
         return dialect.type_descriptor(CHAR(32))
示例#2
0
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)
示例#3
0
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)
示例#4
0
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
示例#6
0
 class Role(Base, RoleMixin):
     __tablename__ = 'role'
     id = Column(Integer(), primary_key=True)
     name = Column(String(80), unique=True)
     description = Column(String(255))
示例#7
0
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()),
示例#8
0
 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
示例#9
0
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
示例#11
0
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)),
示例#12
0
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):
示例#13
0
#!/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)
示例#14
0
class unix_timestamp(expression.FunctionElement):
    type = Integer()
示例#15
0
class User(Base):
    __tablename__ = 'user'

    user_id = Column(Integer(), primary_key=True, unique=True, nullable=False)
    name = Column(String(20))
示例#16
0
class Foo(Base):
    __tablename__ = "foo"
    id: int = Column(Integer(), primary_key=True)
    name: Mapped[str] = Column(String)

    bar: List["Bar"] = relationship("Bar")
示例#17
0
 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'))
示例#18
0
class Bar(Base):
    __tablename__ = "bar"
    id: int = Column(Integer(), primary_key=True)
    foo_id: int = Column(ForeignKey("foo.id"))

    foo: Optional[Foo] = relationship(Foo)
示例#19
0
# 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)),
示例#20
0
    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,
示例#22
0
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'))
示例#23
0
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
示例#24
0
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)
示例#25
0
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())
示例#26
0
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)
示例#27
0
class AnnouncementCategories(Base):
    """Announcement Categories"""

    __tablename__ = "announcements_categories"
    id = Column(Integer(), primary_key=True, nullable=False)
    name = Column(String(1024), nullable=False)
示例#28
0
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')
示例#29
0
文件: database.py 项目: idkwim/cuckoo
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)
示例#30
0
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