示例#1
0
def update_changed(src_table, dim_table):
	"""Update changed records"""

	# create condition
	where_clause = and_(or_(src_table.c.name != dim_table.c.name, src_table.c.address != dim_table.c.address), dim_table.c.to_date == None)

	# join table based on their ids
	joined_table = src_table.join(dim_table, src_table.c.id == dim_table.c.id)

	# create statement for table with only changed records
	change_table = sql.select(src_table.c, whereclause=where_clause, from_obj=joined_table)

	# update records in dim table and create new records
	for row in change_table.execute():
		# create condition
		where_clause = and_(dim_table.c.id == row.id, dim_table.c.to_date == None)

		# create current timestamp
		timestamp = sql.functions.current_timestamp()

    	# update record
		sql.update(dim_table, whereclause=where_clause, values={dim_table.c.to_date:timestamp}).execute()
		print "Updating row", str(row)

    	# insert record
		engine.execute(dim_table.insert(), row)
示例#2
0
文件: api.py 项目: FroopleXP/BitOrb
def api_token_redeem():
    try:
        auth_token = request.form["auth_token"]

        token_code = request.form["token_code"]
    except KeyError as e:
        raise APIMissingField(e.args[0])

    caller = get_user_from_token(auth_token)

    conn = engine.connect()

    query = sql.select([Token]).where(Token.code == token_code).limit(1)
    res = conn.execute(query)
    try:
        token = res.fetchall()[0]
    except IndexError:
        raise APIInvalidField("token_code", 200)

    query1 = sql.update(Token).where(Token.id == token.id).values({
        Token.redeemed: True,
        Token.redeemer: caller.id
    })
    query2 = sql.update(User).where(User.id == caller.id).values({
        User.credits: caller.credits + token.value
    })
    res1 = conn.execute(query1)
    res2 = conn.execute(query2)

    if res1.inserted_primary_key and res2.inserted_primary_key:
        return make_response(jsonify({
            "status": "success",
            "message": "Token successfully redeemed",
            "new_balance": caller.credits + token.value
        }))
示例#3
0
文件: network.py 项目: jrha/aquilon
def fix_foreign_links(session, oldnet, newnet):
    """
    Fix foreign keys that point to the network table

    When a network is split or multiple networks are merged, foreign keys
    must be updated accordingly. Do not use the size of the old network,
    as it has already been updated when this function gets called.
    """
    session.execute(
        update(AddressAssignment.__table__,
               values={'network_id': newnet.id})
        .where(and_(AddressAssignment.network_id == oldnet.id,
                    AddressAssignment.ip >= newnet.ip,
                    AddressAssignment.ip <= newnet.broadcast))
    )
    session.expire(oldnet, ['assignments'])
    session.expire(newnet, ['assignments'])

    session.execute(
        update(ARecord.__table__,
               values={'network_id': newnet.id})
        .where(and_(ARecord.network_id == oldnet.id,
                    ARecord.ip >= newnet.ip,
                    ARecord.ip <= newnet.broadcast))
    )
    session.expire(oldnet, ['dns_records'])
    session.expire(newnet, ['dns_records'])
def _add_shards(engine, table, dst_col, src_col):
    dialect = engine.url.get_dialect().name
    if dialect.startswith('mysql'):
        sql = "UPDATE %s SET %s = CONV(SUBSTR(%s, 1, 3), 16, 10)"
        engine.execute(sql % (table.name, dst_col.name, src_col.name))
    elif dialect.startswith('postgres'):
        sql = "UPDATE %s SET %s = ('x'||lpad(substr(%s::text, 1, 3), 8, '0')"\
            ")::bit(32)::int"
        engine.execute(sql % (table.name, dst_col.name, src_col.name))
    else:
        rows = select(columns=[table.c.id]).execute().fetchall()
        for r in rows:
            shard = int(r.id[0:3], 16)
            values = {dst_col.name: shard}
            update(table).where(table.id == r.id).values(values)
示例#5
0
def update_tag(id, obj):
    query = (
        update([tag_table])
        .where(tag_table.c.id == id)
        .values(**obj)
        .returning(tag_table.c.id))
    return query.execute().first()[0]
示例#6
0
 def _update_atom_details(self, conn, ad, e_ad):
     e_ad.merge(ad)
     conn.execute(
         sql.update(self._tables.atomdetails)
         .where(self._tables.atomdetails.c.uuid == e_ad.uuid)
         .values(e_ad.to_dict())
     )
def upgrade(migrate_engine):
    meta.bind = migrate_engine
    dialect = migrate_engine.url.get_dialect().name

    domains_table = Table('domains', meta, autoload=True)

    if dialect.startswith('sqlite'):
        # SQLite can't drop a constraint. Yay. This will be fun..

        # Create a new name column without the unique index
        name_tmp_column = Column('name_tmp', String(255))
        name_tmp_column.create(domains_table)

        # Copy the data over.
        query = update(domains_table).values(name_tmp=domains_table.c.name)
        migrate_engine.execute(query)

        # Delete the name column
        domains_table.c.name.drop()

        # Rename the name_tmp column to name
        domains_table.c.name_tmp.alter(name='name')
    elif dialect.startswith('postgresql'):
        constraint = UniqueConstraint('name', name='domains_name_key',
                                      table=domains_table)
        constraint.drop()
    else:
        constraint = UniqueConstraint('name', name='name', table=domains_table)
        constraint.drop()
示例#8
0
    def _set_loaned_flag(self, flag):
        """Sets loaned flag in current collection and all associated movies.

        :param flag: if True and there are loaned movies in the collection
            already, exception will be raised (whole collection cannot be
            loaned if one of the movies is not available).
            Please also remember to create new entry in loans table later (no
            need to do that if flag is False).
        """

        session = object_session(self)

        if flag:  # loaning whole collection
            loaned_movies = session.execute(
                select([tables.movies.columns.movie_id]).where(
                    and_(
                        tables.movies.columns.collection_id == self.collection_id, tables.movies.columns.loaned == True
                    )
                )
            ).fetchall()
            if loaned_movies:
                log.error("cannot loan it, collection contains loaned movie(s): %s", loaned_movies)
                raise Exception("loaned movies in the collection already")

        self._loaned = flag
        update_query = update(tables.movies, tables.movies.columns.collection_id == self.collection_id)
        session.execute(update_query, params={"loaned": flag})
示例#9
0
def downgrade():
    # Add ship and pilot transformer columns back to division
    op.add_column('division', sa.Column('ship_transformer', sa.PickleType))
    op.add_column('division', sa.Column('pilot_transformer', sa.PickleType))
    # Convert transformerrefs back to the old columns
    conn = op.get_bind()
    columns = [
        transformerref.c.division_id,
        transformerref.c.attribute_name,
        transformerref.c.transformer,
    ]
    transformer_sel = select(columns)\
            .where(or_(
                    transformerref.c.attribute_name == 'ship_type',
                    transformerref.c.attribute_name == 'pilot',
            ))
    transformer_rows = conn.execute(transformer_sel)
    for division_id, attribute_name, transformer in transformer_rows:
        if attribute_name == 'ship_type':
            colname = 'ship'
            transformer_class = evesrp.transformers.ShipTransformer
        elif attribute_name == 'pilot':
            colname = 'pilot'
            transformer_class = evesrp.transformers.PilotTransformer
        colname += '_transformer'
        transformer = transformer_class(transformer.name, transformer.slug)
        update_stmt = update(division)\
                .where(division.c.id == division_id)\
                .values({
                        colname: transformer
                })
        conn.execute(update_stmt)
    transformer_rows.close()
    # Drop the transformerref table. This is going to be lossy.
    op.drop_table('transformerref')
示例#10
0
 def save_logbook(self, book):
     try:
         logbooks = self._tables.logbooks
         with self._engine.begin() as conn:
             q = (sql.select([logbooks]).
                  where(logbooks.c.uuid == book.uuid))
             row = conn.execute(q).first()
             if row:
                 e_lb = self._converter.convert_book(row)
                 self._converter.populate_book(conn, e_lb)
                 e_lb.merge(book)
                 conn.execute(sql.update(logbooks)
                              .where(logbooks.c.uuid == e_lb.uuid)
                              .values(e_lb.to_dict()))
                 for fd in book:
                     e_fd = e_lb.find(fd.uuid)
                     if e_fd is None:
                         e_lb.add(fd)
                         self._insert_flow_details(conn, fd, e_lb.uuid)
                     else:
                         self._update_flow_details(conn, fd, e_fd)
                 return e_lb
             else:
                 conn.execute(sql.insert(logbooks, book.to_dict()))
                 for fd in book:
                     self._insert_flow_details(conn, fd, book.uuid)
                 return book
     except sa_exc.DBAPIError:
         exc.raise_with_cause(
             exc.StorageFailure,
             "Failed saving logbook '%s'" % book.uuid)
示例#11
0
def downgrade():
    op.add_column('request',
            sa.Column('float_base_payout', sa.Float, default=0.0)
    )
    request = table('request',
            column('id', sa.Integer),
            column('base_payout', sa.Numeric(precision=15, scale=2)),
            column('float_base_payout', sa.Float),
    )
    conn = op.get_bind()
    requests_sel = select([request.c.id, request.c.base_payout])
    requests = conn.execute(requests_sel)
    for request_id, decimal_payout in requests:
        decimal_payout = decimal_payout / 1000000
        float_payout = float(decimal_payout)
        update_stmt = update(request)\
                .where(request.c.id == request_id)\
                .values({
                    'float_base_payout': float_payout,
                })
        conn.execute(update_stmt)
    requests.close()
    op.drop_column('request', 'base_payout')
    op.alter_column('request',
            column_name='numeric_base_payout',
            new_column_name='base_payout',
            existing_type=sa.Float,
            existing_server_default=0.0)
示例#12
0
    def _upsert_generic(self, table, items, annotations):
        """Upsert a batch of items one at a time, trying INSERT then UPDATE.

        This is a tremendously inefficient way to write a batch of items,
        but it's guaranteed to work without special cooperation from the
        database.  For MySQL we use the much improved _upsert_onduplicatekey.
        """
        userid = items[0].get("userid")
        num_created = 0
        for item in items:
            assert item.get("userid") == userid
            try:
                # Try to insert the item.
                # If it already exists, this fails with an integrity error.
                query = insert(table).values(**item)
                self.execute(query, item, annotations).close()
                num_created += 1
            except IntegrityError:
                # Update the item.
                # Use the table's primary key fields in the WHERE clause,
                # and put all other fields into the UPDATE clause.
                item = item.copy()
                query = update(table)
                for key in table.primary_key:
                    try:
                        query = query.where(key == item.pop(key.name))
                    except KeyError:
                        msg = "Item is missing primary key column %r"
                        raise ValueError(msg % (key.name,))
                query = query.values(**item)
                self.execute(query, item, annotations).close()
        return num_created
示例#13
0
    async def _test_execute(self, engines, binds):
        test_table1 = self.test_models['db1'].test_table1
        test_table2 = self.test_models['db2'].test_table2
        async with Session(engines, binds) as session:
            q = sql.insert(test_table1).values(id=5, title='test_title')
            result = await session.execute(q)
            self.assertEqual(result.lastrowid, 5)
            q = sql.select(test_table1.c).where(test_table1.c.id == 5)
            result = await session.execute(q)
            self.assertEqual(result.rowcount, 1)
            result = list(result)
            self.assertEqual(result[0]['id'], 5)
            self.assertEqual(result[0]['title'], 'test_title')

            q = sql.update(test_table1).where(test_table1.c.id == 5).\
                    values(title='test_title2')
            result = await session.execute(q)
            self.assertEqual(result.rowcount, 1)
            q = sql.select(test_table1.c).\
                    where(test_table1.c.id == 5)
            result = await session.execute(q)
            self.assertEqual(result.rowcount, 1)
            result = list(result)
            self.assertEqual(result[0]['id'], 5)
            self.assertEqual(result[0]['title'], 'test_title2')

            q = sql.delete(test_table1).where(test_table1.c.id == 5)
            result = await session.execute(q)
            self.assertEqual(result.rowcount, 1)
            q = sql.select(test_table1.c).\
                    where(test_table1.c.id == 5)
            result = await session.execute(q)
            self.assertEqual(result.rowcount, 0)
示例#14
0
    def update_session():
        now = datetime.datetime.now()
        if 17 <= now.hour <= 23:
            lower_limit = datetime.datetime(
                datetime.datetime.today().year,
                datetime.datetime.today().month,
                datetime.datetime.today().day,
                19
            )
            upper_limit = datetime.datetime(
                datetime.datetime.today().year,
                datetime.datetime.today().month,
                datetime.datetime.today().day + 1,
                7
            )

            db.session.execute(
                sql.update(Session).where(
                    Session.id == 1
                ).values(
                    upper_limit=upper_limit,
                    lower_limit=lower_limit
                )
            )
            db.session.commit()
示例#15
0
文件: helpers.py 项目: pitcons/amarak
def update_helper(ikey, table, session, obj, mapping):
    raise ValueError
    if hasattr(obj, '_alchemy_pk') and obj._alchemy_pk is not None:
        query = update(table)\
            .where(table.c.id==obj._alchemy_pk)\
            .values(**mapping)
        result = session.execute(query)

        if result.rowcount == 0:
            raise RuntimeError("Can't update object")
            # obj._alchemy_pk = None
            # return update_helper(session, obj, mapping)
    else:
        # TODO search for an object
        # query = select([table]).where(table.c.uri==obj.uri)
        # result = session.execute(query).fetchone()
        # if result:
        #     obj._alchemy_pk = result[0]
        #     return update_helper(session, obj, mapping)
        # else:

        # print "INSERT", obj, session.execute(select([table])).fetchall()
        aquery = table.insert().values(**mapping)
        result = session.execute(aquery)
        obj._alchemy_pk = result.inserted_primary_key[0]
示例#16
0
    def update_password(self, user_id, password, old_password=None, key=None):
        """Change the user password

        Args:
            user_id: user id
            password: new password

        Returns:
            True if the change was successful, False otherwise
        """
        if old_password is None:
            if key:
                #using a key, therefore we should check it
                if self._get_reset_code(user_id) == key:
                    self.clear_reset_code(user_id)
                else:
                    logger.error("bad key used for update password")
                    return False
            else:
                return False

        password_hash = ssha256(password)
        query = update(users).where(users.c.id == user_id)
        res = safe_execute(self._engine,
                           query.values(password_hash=password_hash))
        return res.rowcount == 1
示例#17
0
 def _updateResources(self):
     query = {'status':self.computer_status,'is_mac': self.is_mac}
     if self.computer_status == 0:
         query['update0'] = functions.now()
     else:
         query['update1'] = functions.now()
     self._simpleQuery(update(schema.computers,schema.computers.c.computername==self.computer_id,values=query))  
示例#18
0
 def renameItem(self):
     self.nodeName(textToShow=self.treeWidget.currentItem().text(0))
     try:
         self.treeWidget.currentItem().parent().indexOfChild(self.treeWidget.currentItem())
     except:
         parentUniqueID = self.treeWidget.currentItem().statusTip(1)
         renNode = update(self.nodes).where(self.nodes.c.node_id == parentUniqueID).values(name=self.inputNodeName)
         renNode.execute()
         self.redraw()
     else:
         parentUniqueID = self.treeWidget.currentItem().parent().statusTip(0)
         childIndex = self.treeWidget.currentItem().statusTip(1)
         renSubNode = update(self.children).where(self.children.c.node_id == childIndex and self.children.c.father_id == parentUniqueID).values(name=self.inputNodeName)
         renSubNode.execute()
         self.redraw()
     del self.inputNodeName
示例#19
0
def upgrade(migrate_engine):
    TableBase.metadata.bind = migrate_engine

    Discussion.__table__.create()
    Comment.__table__.create()

    Artwork.__table__.c.discussion_id.nullable = True
    Artwork.__table__.c.discussion_id.create()
    User.__table__.c.discussion_id.nullable = True
    User.__table__.c.discussion_id.create()

    # Create a new discussion for each artwork and user
    conn = migrate_engine.connect()
    tr = conn.begin()

    for table in Artwork, User:
        for id, in conn.execute( sql.select([table.id]) ):
            res = conn.execute( sql.insert(Discussion.__table__) )
            discussion_id = res.inserted_primary_key[0]

            conn.execute(sql.update(
                table.__table__,
                table.__table__.c.id == id,
                dict(discussion_id=discussion_id),
            ))

    tr.commit()

    Artwork.__table__.c.discussion_id.alter(nullable=False)
    User.__table__.c.discussion_id.alter(nullable=False)
示例#20
0
文件: database.py 项目: Plurk/Solace
def atomic_add(obj, column, delta, expire=False):
    """Performs an atomic add (or subtract) of the given column on the
    object.  This updates the object in place for reflection but does
    the real add on the server to avoid race conditions.  This assumes
    that the database's '+' operation is atomic.

    If `expire` is set to `True`, the value is expired and reloaded instead
    of added of the local value.  This is a good idea if the value should
    be used for reflection.
    """
    sess = orm.object_session(obj) or session
    mapper = orm.object_mapper(obj)
    pk = mapper.primary_key_from_instance(obj)
    assert len(pk) == 1, 'atomic_add not supported for classes with ' \
                         'more than one primary key'

    val = orm.attributes.get_attribute(obj, column)
    if expire:
        orm.attributes.instance_state(obj).expire_attributes([column])
    else:
        orm.attributes.set_committed_value(obj, column, val + delta)

    table = mapper.tables[0]
    stmt = sql.update(table, mapper.primary_key[0] == pk[0], {
        column:     table.c[column] + delta
    })
    sess.execute(stmt)
示例#21
0
 def UpdateTable(self):
     print 'Start updating the table'
     ## TODO Make a unique update statement 
     bc_value = self.node.__table__.columns[self.column_name]
     for val in self.bc:
         u = update(self.node.__table__).where(self.node.id == val).values(bc_value = self.bc[val])
         u.execute()
     print 'Table updated'
示例#22
0
文件: db.py 项目: mkhettry/thyme
def update_category(category_name, budget):
    category_id = find_category_id(category_name)
    if category_id:
        stmt = update(categories).where(categories.c.id == category_id).values(budget=budget)
        upd = engine.execute(stmt)
        return upd.rowcount
    else:
        return 0
示例#23
0
 def _set_reset_code(self, user_id):
     code, expiration = generate_reset_code()
     query = update(users).values(reset=code, reset_expiration=expiration)
     res = safe_execute(self._engine, query.where(users.c.id == user_id))
     if res.rowcount != 1:
         logger.debug('Unable to add a new reset code')
         return None  # XXX see if appropriate
     return code
示例#24
0
文件: aggbuilder.py 项目: Kozea/pypet
 def update_stmt(self):
     pk_values = self._pk_values_from_variable()
     conditions = []
     for key, value in pk_values.iteritems():
         conditions.append(value == self.agg_row.c[key])
     return (update(self.agg.selectable)
             .values(self._values_from_variable())
             .where(and_(*conditions)))
示例#25
0
文件: FTS3DB.py 项目: DIRACGrid/DIRAC
  def getNonFinishedOperations(self, limit=20, operationAssignmentTag="Assigned"):
    """ Get all the non assigned FTS3Operations that are not yet finished, so either Active or Processed.
        An operation won't be picked if it is already assigned, or one of its job is.

        :param limit: max number of operations to retrieve
        :param operationAssignmentTag: if not None, block the operations for other queries,
                              and use it as a prefix for the value in the operation table
        :return: list of Operations
    """

    session = self.dbSession(expire_on_commit=False)

    try:

      ftsOperations = []

      # We need to do the select in two times because the join clause that makes the limit difficult
      operationIDsQuery = session.query(FTS3Operation.operationID)\
          .outerjoin(FTS3Job)\
          .filter(FTS3Operation.status.in_(['Active', 'Processed']))\
          .filter(FTS3Operation.assignment.is_(None))\
          .filter(FTS3Job.assignment.is_(None))\
          .limit(limit)\
          .distinct()

      # Block the Operations for other requests
      if operationAssignmentTag:
        operationIDsQuery = operationIDsQuery.with_for_update()

      operationIDs = operationIDsQuery.all()

      operationIDs = [oidTuple[0] for oidTuple in operationIDs]

      if operationIDs:
        # Fetch the operation object for these IDs
        ftsOperations = session.query(FTS3Operation)\
            .filter(FTS3Operation.operationID.in_(operationIDs))\
            .all()

        if operationAssignmentTag:
          operationAssignmentTag += "_%s" % datetime.datetime.utcnow().strftime('%Y-%m-%d %H:%M:%S')

          session.execute(update(FTS3Operation)
                          .where(FTS3Operation.operationID.in_(operationIDs)
                                 )
                          .values({'assignment': operationAssignmentTag})
                          )

      session.commit()
      session.expunge_all()

      return S_OK(ftsOperations)

    except SQLAlchemyError as e:
      session.rollback()
      return S_ERROR("getAllProcessedOperations: unexpected exception : %s" % e)
    finally:
      session.close()
示例#26
0
def link_account(existing_email, new_email):
    db = connect()
    ids = keystone_ids_from_email(db, existing_email)
    new_email_ids = keystone_ids_from_email(db, new_email)

    if len(ids) != 1:
        print('User has multiple accounts with email %s' % existing_email)
        return

    user_id = ids[0]
    orphan_user_id = new_email_ids[0]

    print('%s: %s' % (existing_email, user_id))
    print('%s: %s' % (new_email, orphan_user_id))

    if user_id == orphan_user_id:
        print('Those accounts are already linked')
        return

    client = keystone.client()
    user = client.users.get(orphan_user_id)

    project = client.projects.get(user.default_project_id)
    servers = nova.client().servers.list(search_opts={
        'all_tenants': True, 'project_id': project.id})

    if len(servers):
        print('Soon to be orphaned project has active instances.')
        print('Advise user to terminate them.')
        return

    print()
    print('Confirm that you want to:')
    print(' - Link %s to account %s' % (new_email, existing_email))
    print(' - Disable orphan Keystone project %s' % (project.name))
    print(' - Disable orphan Keystone user %s' % (user.name))
    print()

    response = raw_input('(yes/no): ')
    if response != 'yes':
        return

    print('Linking account.')
    sql = (update(users)
           .where(users.c.email == new_email)
           .values(user_id=user_id))
    result = db.execute(sql)

    if result.rowcount == 0:
        print('Something went wrong.')
        return

    print('Disabling orphaned Keystone project %s (%s).' % (
        project.name, project.id))
    client.projects.update(project.id, enabled=False)
    print('Disabling orphaned Keystone user %s (%s).' % (user.name, user.id))
    client.users.update(user.id, enabled=False, name="%s-disabled" % user.name)
    print('All done.')
示例#27
0
 def UpdateTable(self):
     print 'Start updating the table'
     ## TODO Make a unique update statement 
     for val in self.bc:
         edge_id = self.G.get_edge_data(val[0],val[1])['eid']
         bc_value = self.network.__table__.columns[self.column_name]
         u = update(self.network.__table__).where(self.network.id == edge_id).values(bc_value = self.bc[val])
         u.execute()
     print 'Table updated'
示例#28
0
文件: db.py 项目: mkhettry/thyme
def update_description_mapping(desc, category_id):
    cleaned_desc = desc.strip().lower()
    stmt = select([description_category_mapping.c.id]).where(description_category_mapping.c.description == cleaned_desc)

    if not engine.execute(stmt).fetchone():
        engine.execute(description_category_mapping.insert(), description=cleaned_desc, category_id=category_id)
    else:
        stmt = update(description_category_mapping).where(description_category_mapping.c.description == cleaned_desc).values(category_id=category_id)
        engine.execute(stmt)
示例#29
0
文件: db.py 项目: mkhettry/thyme
def update_tx_category(txid, category_id):
    stmt = update(xactions).where(xactions.c.id == txid).values(category_id=category_id)
    upd = engine.execute(stmt)

    if upd.rowcount == 1:
        stmt = select([xactions.c.description]).where(xactions.c.id == txid)
        desc = engine.execute(stmt).fetchone()
        update_description_mapping(desc[0], category_id)
    return upd.rowcount
示例#30
0
 def _set_reset_code(self, user_id):
     rc = ResetCode()
     code = rc._generate_reset_code()
     expiration = datetime.datetime.now() + datetime.timedelta(hours=6)
     query = update(users).values(reset=code, reset_expiration=expiration)
     res = safe_execute(self._engine, query.where(users.c.id == user_id))
     if res.rowcount != 1:
         self.logger.debug('Unable to add a new reset code')
         return None  # XXX see if appropriate
     return code
示例#31
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))
    orders_intern = Table('orders_intern', metadata,
        Column('werkorderID', Integer(), primary_key=True),
        Column('werkomschrijving', String),
        Column('voortgangstatus', String),
        Column('statusweek', String),
        Column('boekdatum', String),
        Column('begroot_totaal', Float),
        Column('werk_totaal', Float),
        Column('begr_materialen', Float),
        Column('werk_materialen', Float),
        Column('begr_lonen', Float),
        Column('werk_lonen', Float),
        Column('bzagen', Float),
        Column('wzagen', Float),
        Column('bschaven', Float),
        Column('wschaven', Float),
        Column('bsteken', Float),
        Column('wsteken', Float),
        Column('bboren', Float),
        Column('wboren', Float),
        Column('bfrezen', Float),
        Column('wfrezen', Float),
        Column('bdraaien_klein', Float),
        Column('wdraaien_klein', Float),
        Column('bdraaien_groot', Float),
        Column('wdraaien_groot', Float),
        Column('btappen', Float),
        Column('wtappen', Float),
        Column('bnube_draaien', Float),
        Column('wnube_draaien', Float),
        Column('bnube_bewerken', Float),
        Column('wnube_bewerken', Float),
        Column('bknippen', Float),
        Column('wknippen', Float),
        Column('bkanten', Float),
        Column('wkanten', Float),
        Column('bstansen', Float),
        Column('wstansen', Float),
        Column('blassen_co2', Float),
        Column('wlassen_co2', Float),
        Column('blassen_hand', Float),
        Column('wlassen_hand', Float),
        Column('bverpakken', Float),
        Column('wverpakken', Float),
        Column('bverzinken', Float),
        Column('wverzinken', Float),
        Column('bmoffelen', Float),
        Column('wmoffelen', Float),
        Column('bschilderen', Float),
        Column('wschilderen', Float),
        Column('bspuiten', Float),
        Column('wspuiten', Float),
        Column('bponsen', Float),
        Column('wponsen', Float),
        Column('bpersen', Float),
        Column('wpersen', Float),
        Column('bgritstralen', Float),
        Column('wgritstralen', Float),
        Column('bmontage', Float),
        Column('wmontage', Float),
        Column('werk_reis_uren', Float),
        Column('meerminderwerk', 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 > 52))
    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 werkordernummer!')
       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([orders_intern]).where(orders_intern.c.werkorderID == 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)    #loonID
    loonsel2 = select([lonen]).where(lonen.c.loonID == wrkgr2)
    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 > 52 and wrkgr < 56 and msoort < 5:
        stmt = update(orders_intern).where(orders_intern.c.werkorderID == mwerknr).\
               values(werk_lonen = orders_intern.c.werk_lonen+loonk,
               wzagen = orders_intern.c.wzagen+muren+mu125+mu150+mu200\
               +mmeerw100+mmeerw125+mmeerw150+mmeerw200,\
               werk_reis_uren = orders_intern.c.werk_reis_uren+mreis,\
               meerminderwerk = orders_intern.c.meerminderwerk + meerk)
        con.execute(stmt)
        sel = select([orders_intern]).where(orders_intern.c.werkorderID == mwerknr)
        rpsel = con.execute(sel).first()
        self.urenbegrEdit.setText('{:<12.2f}'.format(rpsel[11]))
        self.urentotEdit.setText('{:<12.2f}'.format(rpsel[12]))
        lblptext = 'Totalen: Werkelijk / Begroot\nUren Zagen'
        lbltext = 'Muteren uren (werken - lonen) niet cumulatief'
        self.lblprof.setText(lblptext)
        self.lblt.setStyleSheet("color: black")
        self.lblt.setText(lbltext)
    elif wrkgr > 55 and wrkgr < 59 and msoort < 5:   
        stmt = update(orders_intern).where(orders_intern.c.werkorderID == mwerknr).\
            values(werk_lonen = orders_intern.c.werk_lonen+loonk,
           wschaven = orders_intern.c.wschaven+mu125+mu150+mu200\
           +mmeerw100+mmeerw125+mmeerw150+mmeerw200,\
           werk_reis_uren = orders_intern.c.werk_reis_uren+mreis,\
           meerminderwerk = orders_intern.c.meerminderwerk + meerk)
        con.execute(stmt)
        sel = select([orders_intern]).where(orders_intern.c.werkorderID == mwerknr)
        rpsel = con.execute(sel).first()
        self.urenbegrEdit.setText('{:<12.2f}'.format(rpsel[13]))
        self.urentotEdit.setText('{:<12.2f}'.format(rpsel[14]))
        lblptext = 'Totalen: Werkelijk / Begroot\nUren Schaven'
        lbltext = 'Muteren uren (werken - lonen) niet cumulatief'
        self.lblprof.setText(lblptext)
        self.lblt.setStyleSheet("color: black")
        self.lblt.setText(lbltext)
    elif wrkgr > 58 and wrkgr < 62 and msoort < 5:
        stmt = update(orders_intern).where(orders_intern.c.werkorderID == mwerknr).\
           values(werk_lonen = orders_intern.c.werk_lonen+loonk,
           wsteken = orders_intern.c.wsteken+muren+mu125+mu150+mu200\
           +mmeerw100+mmeerw125+mmeerw150+mmeerw200,\
           werk_reis_uren = orders_intern.c.werk_reis_uren+mreis,\
           meerminderwerk = orders_intern.c.meerminderwerk + meerk)
        con.execute(stmt)
        sel = select([orders_intern]).where(orders_intern.c.werkorderID == mwerknr)
        rpsel = con.execute(sel).first()
        self.urenbegrEdit.setText('{:<12.2f}'.format(rpsel[15]))
        self.urentotEdit.setText('{:<12.2f}'.format(rpsel[16]))
        lblptext = 'Totalen: Werkelijk / Begroot\nUren Steken'
        lbltext = 'Muteren uren (werken - lonen) niet cumulatief'
        self.lblprof.setText(lblptext)
        self.lblt.setStyleSheet("color: black")
        self.lblt.setText(lbltext)
    elif wrkgr > 61 and wrkgr < 65 and msoort < 5:
        stmt = update(orders_intern).where(orders_intern.c.werkorderID == mwerknr).\
          values(werk_lonen = orders_intern.c.werk_lonen+loonk,
           wboren = orders_intern.c.wboren+muren+mu125+mu150+mu200\
           +mmeerw100+mmeerw125+mmeerw150+mmeerw200,\
           werk_reis_uren = orders_intern.c.werk_reis_uren+mreis,\
            meerminderwerk = orders_intern.c.meerminderwerk + meerk)
        con.execute(stmt)
        sel = select([orders_intern]).where(orders_intern.c.werkorderID == mwerknr)
        rpsel = con.execute(sel).first()
        self.urenbegrEdit.setText('{:<12.2f}'.format(rpsel[17]))
        self.urentotEdit.setText('{:<12.2f}'.format(rpsel[18]))
        lblptext = 'Totalen: Werkelijk / Begroot\nUren Boren'
        lbltext = 'Muteren uren (werken - lonen) niet cumulatief'
        self.lblprof.setText(lblptext)
        self.lblt.setStyleSheet("color: black")
        self.lblt.setText(lbltext)
    elif wrkgr > 64 and wrkgr < 68 and msoort < 5:
        stmt = update(orders_intern).where(orders_intern.c.werkorderID == mwerknr).\
          values(werk_lonen = orders_intern.c.werk_lonen+loonk,
           wfrezen = orders_intern.c.wfrezen+muren+mu125+mu150+mu200\
           +mmeerw100+mmeerw125+mmeerw150+mmeerw200,\
           werk_reis_uren = orders_intern.c.werk_reis_uren+mreis,\
           meerminderwerk = orders_intern.c.meerminderwerk + meerk)
        con.execute(stmt)
        sel = select([orders_intern]).where(orders_intern.c.werkorderID == mwerknr)
        rpsel = con.execute(sel).first()
        self.urenbegrEdit.setText('{:<12.2f}'.format(rpsel[19]))
        self.urentotEdit.setText('{:<12.2f}'.format(rpsel[20]))
        lblptext = 'Totalen: Werkelijk / Begroot\nUren Frezen'
        lbltext = 'Muteren uren (werken - lonen) niet cumulatief'
        self.lblprof.setText(lblptext)
        self.lblt.setStyleSheet("color: black")
        self.lblt.setText(lbltext)
    elif wrkgr > 67 and wrkgr < 71 and msoort < 5:
        stmt = update(orders_intern).where(orders_intern.c.werkorderID == mwerknr).\
          values(werk_lonen = orders_intern.c.werk_lonen+loonk,
           wdraaien_klein = orders_intern.c.wdraaien_klein+muren+mu125+mu150+mu200\
           +mmeerw100+mmeerw125+mmeerw150+mmeerw200,\
           werk_reis_uren = orders_intern.c.werk_reis_uren+mreis,\
            meerminderwerk = orders_intern.c.meerminderwerk + meerk)
        con.execute(stmt)
        sel = select([orders_intern]).where(orders_intern.c.werkorderID == mwerknr)
        rpsel = con.execute(sel).first()
        self.urenbegrEdit.setText('{:<12.2f}'.format(rpsel[21]))
        self.urentotEdit.setText('{:<12.2f}'.format(rpsel[22]))
        lblptext = 'Totalen: Werkelijk / Begroot\nUren Draaien klein'
        lbltext = 'Muteren uren (werken - lonen) niet cumulatief'
        self.lblprof.setText(lblptext)
        self.lblt.setStyleSheet("color: black")
        self.lblt.setText(lbltext)
    elif wrkgr > 70 and wrkgr < 74 and msoort < 5:
        stmt = update(orders_intern).where(orders_intern.c.werkorderID == mwerknr).\
           values(werk_lonen = orders_intern.c.werk_lonen+loonk,
           wdraaien_groot = orders_intern.c.wdraaien_groot+muren+mu125+mu150+mu200\
           +mmeerw100+mmeerw125+mmeerw150+mmeerw200,\
           werk_reis_uren = orders_intern.c.werk_reis_uren+mreis,\
           meerminderwerk = orders_intern.c.meerminderwerk + meerk)
        con.execute(stmt)
        sel = select([orders_intern]).where(orders_intern.c.werkorderID == 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 Draaien groot'
        lbltext = 'Muteren uren (werken - lonen) niet cumulatief'
        self.lblprof.setText(lblptext)
        self.lblt.setStyleSheet("color: black")
        self.lblt.setText(lbltext)
    elif wrkgr > 73 and wrkgr < 77 and msoort < 5:
        stmt = update(orders_intern).where(orders_intern.c.werkorderID == mwerknr).\
           values(werk_lonen = orders_intern.c.werk_lonen+loonk,
           wtappen = orders_intern.c.wtappen+muren+mu125+mu150+mu200\
           +mmeerw100+mmeerw125+mmeerw150+mmeerw200,\
           werk_reis_uren = orders_intern.c.werk_reis_uren+mreis,\
           meerminderwerk = orders_intern.c.meerminderwerk + meerk)
        con.execute(stmt)
        sel = select([orders_intern]).where(orders_intern.c.werkorderID == mwerknr)
        rpsel = con.execute(sel).first()
        self.urenbegrEdit.setText('{:<12.2f}'.format(rpsel[25]))
        self.urentotEdit.setText('{:<12.2f}'.format(rpsel[26]))
        lblptext = 'Totalen: Werkelijk / Begroot\nUren Tappen'
        lbltext = 'Muteren uren (werken - lonen) niet cumulatief'
        self.lblprof.setText(lblptext)
        self.lblt.setStyleSheet("color: black")
        self.lblt.setText(lbltext)
    elif wrkgr > 76 and wrkgr < 80 and msoort < 5:
        stmt = update(orders_intern).where(orders_intern.c.werkorderID == mwerknr).\
            values(werk_lonen = orders_intern.c.werk_lonen+loonk,
           wnube_draaien = orders_intern.c.wnube_draaien+muren+mu125+mu150+mu200\
           +mmeerw100+mmeerw125+mmeerw150+mmeerw200,\
           werk_reis_uren = orders_intern.c.werk_reis_uren+mreis,\
           meerminderwerk = orders_intern.c.meerminderwerk + meerk)
        con.execute(stmt)
        sel = select([orders_intern]).where(orders_intern.c.werkorderID == mwerknr)
        rpsel = con.execute(sel).first()
        self.urenbegrEdit.setText('{:<12.2f}'.format(rpsel[27]))
        self.urentotEdit.setText('{:<12.2f}'.format(rpsel[28]))
        lblptext = 'Totalen: Werkelijk / Begroot\nUren Nubé draaien'
        lbltext = 'Muteren uren (werken - lonen) niet cumulatief'
        self.lblprof.setText(lblptext)
        self.lblt.setStyleSheet("color: black")
        self.lblt.setText(lbltext)
    elif wrkgr > 79 and wrkgr < 83 and msoort < 5:
        stmt = update(orders_intern).where(orders_intern.c.werkorderID == mwerknr).\
            values(werk_lonen = orders_intern.c.werk_lonen+loonk,
           wnube_bewerken = orders_intern.c.wnube_bewerken+muren+mu125+mu150+mu200\
           +mmeerw100+mmeerw125+mmeerw150+mmeerw200,\
           werk_reis_uren = orders_intern.c.werk_reis_uren+mreis,\
           meerminderwerk = orders_intern.c.meerminderwerk + meerk)
        con.execute(stmt)
        sel = select([orders_intern]).where(orders_intern.c.werkorderID == mwerknr)
        rpsel = con.execute(sel).first()
        self.urenbegrEdit.setText('{:<12.2f}'.format(rpsel[29]))
        self.urentotEdit.setText('{:<12.2f}'.format(rpsel[30]))
        lblptext = 'Totalen: Werkelijk / Begroot\nUren Nubé bewerken'
        lbltext = 'Muteren uren (werken - lonen) niet cumulatief'
        self.lblprof.setText(lblptext)
        self.lblt.setStyleSheet("color: black")
        self.lblt.setText(lbltext)
    elif wrkgr > 72 and wrkgr < 86 and msoort < 5:
        stmt = update(orders_intern).where(orders_intern.c.werkorderID == mwerknr).\
            values(werk_lonen = orders_intern.c.werk_lonen+loonk,
           wknippen = orders_intern.c.wknippen+muren+mu125+mu150+mu200\
           +mmeerw100+mmeerw125+mmeerw150+mmeerw200,\
           werk_reis_uren = orders_intern.c.werk_reis_uren+mreis,\
           meerminderwerk = orders_intern.c.meerminderwerk + meerk)
        con.execute(stmt)
        sel = select([orders_intern]).where(orders_intern.c.werkorderID == mwerknr)
        rpsel = con.execute(sel).first()
        self.urenbegrEdit.setText('{:<12.2f}'.format(rpsel[31]))
        self.urentotEdit.setText('{:<12.2f}'.format(rpsel[32]))
        lblptext = 'Totalen: Werkelijk / Begroot\nUren Knippen'
        lbltext = 'Muteren uren (werken - lonen) niet cumulatief'
        self.lblprof.setText(lblptext)
        self.lblt.setStyleSheet("color: black")
        self.lblt.setText(lbltext)
    elif wrkgr > 75 and wrkgr < 89 and msoort < 5:
        stmt = update(orders_intern).where(orders_intern.c.werkorderID == mwerknr).\
            values(werk_lonen = orders_intern.c.werk_lonen+loonk,
           wkanten = orders_intern.c.wkanten+muren+mu125+mu150+mu200\
           +mmeerw100+mmeerw125+mmeerw150+mmeerw200,\
           werk_reis_uren = orders_intern.c.werk_reis_uren+mreis,\
           meerminderwerk = orders_intern.c.meerminderwerk + meerk)
        con.execute(stmt)
        sel = select([orders_intern]).where(orders_intern.c.werkorderID == mwerknr)
        rpsel = con.execute(sel).first()
        self.urenbegrEdit.setText('{:<12.2f}'.format(rpsel[33]))
        self.urentotEdit.setText('{:<12.2f}'.format(rpsel[34]))
        lblptext = 'Totalen: Werkelijk / Begroot\nUren Kanten'
        lbltext = 'Muteren uren (werken - lonen) niet cumulatief'
        self.lblprof.setText(lblptext)
        self.lblt.setStyleSheet("color: black")
        self.lblt.setText(lbltext)
    elif wrkgr > 88 and wrkgr < 92 and msoort < 5:
        stmt = update(orders_intern).where(orders_intern.c.werkorderID == mwerknr).\
            values(werk_lonen = orders_intern.c.werk_lonen+loonk,
           wstansen = orders_intern.c.wstansen+muren+mu125+mu150+mu200\
           +mmeerw100+mmeerw125+mmeerw150+mmeerw200,\
           werk_reis_uren = orders_intern.c.werk_reis_uren+mreis,\
           meerminderwerk = orders_intern.c.meerminderwerk + meerk)
        con.execute(stmt)
        sel = select([orders_intern]).where(orders_intern.c.werkorderID == mwerknr)
        rpsel = con.execute(sel).first()
        self.urenbegrEdit.setText('{:<12.2f}'.format(rpsel[35]))
        self.urentotEdit.setText('{:<12.2f}'.format(rpsel[36]))
        lblptext = 'Totalen: Werkelijk / Begroot\nUren Stanzen'
        lbltext = 'Muteren uren (werken - lonen) niet cumulatief'
        self.lblprof.setText(lblptext)
        self.lblt.setStyleSheet("color: black")
        self.lblt.setText(lbltext)
    elif wrkgr > 91 and wrkgr < 95 and msoort < 5:
        stmt = update(orders_intern).where(orders_intern.c.werkorderID == mwerknr).\
           values(werk_lonen = orders_intern.c.werk_lonen+loonk,
           wlassen_co2 = orders_intern.c.wlassen_co2+muren+mu125+mu150+mu200\
           +mmeerw100+mmeerw125+mmeerw150+mmeerw200,\
           werk_reis_uren = orders_intern.c.werk_reis_uren+mreis,\
           meerminderwerk = orders_intern.c.meerminderwerk + meerk)
        con.execute(stmt)
        sel = select([orders_intern]).where(orders_intern.c.werkorderID == mwerknr)
        rpsel = con.execute(sel).first()
        self.urenbegrEdit.setText('{:<12.2f}'.format(rpsel[37]))
        self.urentotEdit.setText('{:<12.2f}'.format(rpsel[38]))
        lblptext = 'Totalen: Werkelijk / Begroot\nUren Lassen co2'
        lbltext = 'Muteren uren (werken - lonen) niet cumulatief'
        self.lblprof.setText(lblptext)
        self.lblt.setStyleSheet("color: black")
        self.lblt.setText(lbltext)
    elif wrkgr > 94 and wrkgr < 98 and msoort < 5:
        stmt = update(orders_intern).where(orders_intern.c.werkorderID == mwerknr).\
            values(werk_lonen = orders_intern.c.werk_lonen+loonk,
           wlassen_hand = orders_intern.c.wlassen_hand+muren+mu125+mu150+mu200\
           +mmeerw100+mmeerw125+mmeerw150+mmeerw200,\
           werk_reis_uren = orders_intern.c.werk_reis_uren+mreis,\
           meerminderwerk = orders_intern.c.meerminderwerk + meerk)
        con.execute(stmt)
        sel = select([orders_intern]).where(orders_intern.c.werkorderID == mwerknr)
        rpsel = con.execute(sel).first()
        self.urenbegrEdit.setText('{:<12.2f}'.format(rpsel[39]))
        self.urentotEdit.setText('{:<12.2f}'.format(rpsel[40]))
        lblptext = 'Totalen: Werkelijk / Begroot\nUren Lassen hand'
        lbltext = 'Muteren uren (werken - lonen) niet cumulatief'
        self.lblprof.setText(lblptext)
        self.lblt.setStyleSheet("color: black")
        self.lblt.setText(lbltext)
    elif wrkgr > 97 and wrkgr < 101 and msoort < 5:
        stmt = update(orders_intern).where(orders_intern.c.werkorderID == mwerknr).\
            values(werk_lonen = orders_intern.c.werk_lonen+loonk,
           wverpakken = orders_intern.c.wverpakken+muren+mu125+mu150+mu200\
           +mmeerw100+mmeerw125+mmeerw150+mmeerw200,\
           werk_reis_uren = orders_intern.c.werk_reis_uren+mreis,\
           meerminderwerk = orders_intern.c.meerminderwerk + meerk)
        con.execute(stmt)
        sel = select([orders_intern]).where(orders_intern.c.werkorderID == mwerknr)
        rpsel = con.execute(sel).first()
        self.urenbegrEdit.setText('{:<12.2f}'.format(rpsel[41]))
        self.urentotEdit.setText('{:<12.2f}'.format(rpsel[42]))
        lblptext = 'Totalen: Werkelijk / Begroot\nUren Verpakken'
        lbltext = 'Muteren uren (werken - lonen) niet cumulatief'
        self.lblprof.setText(lblptext)
        self.lblt.setStyleSheet("color: black")
        self.lblt.setText(lbltext)
    elif wrkgr > 100 and wrkgr < 104 and msoort < 5:
        stmt = update(orders_intern).where(orders_intern.c.werkorderID == mwerknr).\
            values(werk_lonen = orders_intern.c.werk_lonen+loonk,
           wverzinken = orders_intern.c.wverzinken+muren+mu125+mu150+mu200\
           +mmeerw100+mmeerw125+mmeerw150+mmeerw200,\
           werk_reis_uren = orders_intern.c.werk_reis_uren+mreis,\
           meerminderwerk = orders_intern.c.meerminderwerk + meerk)
        con.execute(stmt)
        sel = select([orders_intern]).where(orders_intern.c.werkorderID == mwerknr)
        rpsel = con.execute(sel).first()
        self.urenbegrEdit.setText('{:<12.2f}'.format(rpsel[43]))
        self.urentotEdit.setText('{:<12.2f}'.format(rpsel[44]))
        lblptext = 'Totalen: Werkelijk / Begroot\nUren Verzinken'
        lbltext = 'Muteren uren (werken - lonen) niet cumulatief'
        self.lblprof.setText(lblptext)
        self.lblt.setStyleSheet("color: black")
        self.lblt.setText(lbltext)
    elif wrkgr > 103 and wrkgr < 107 and msoort < 5:
        stmt = update(orders_intern).where(orders_intern.c.werkorderID == mwerknr).\
            values(werk_lonen = orders_intern.c.werk_lonen+loonk,
           wmoffelen = orders_intern.c.wmoffelen+muren+mu125+mu150+mu200\
           +mmeerw100+mmeerw125+mmeerw150+mmeerw200,\
           werk_reis_uren = orders_intern.c.werk_reis_uren+mreis,\
           meerminderwerk = orders_intern.c.meerminderwerk + meerk)
        con.execute(stmt)
        sel = select([orders_intern]).where(orders_intern.c.werkorderID == mwerknr)
        rpsel = con.execute(sel).first()
        self.urenbegrEdit.setText('{:<12.2f}'.format(rpsel[45]))
        self.urentotEdit.setText('{:<12.2f}'.format(rpsel[46]))
        lblptext = 'Totalen: Werkelijk / Begroot\nUren Moffelen'
        lbltext = 'Muteren uren (werken - lonen) niet cumulatief'
        self.lblprof.setText(lblptext)
        self.lblt.setStyleSheet("color: black")
        self.lblt.setText(lbltext)
    elif wrkgr > 106 and wrkgr < 110 and msoort < 5:
        stmt = update(orders_intern).where(orders_intern.c.werkorderID == mwerknr).\
            values(werk_lonen = orders_intern.c.werk_lonen+loonk,
           wschilderen = orders_intern.c.wschilderen+muren+mu125+mu150+mu200\
           +mmeerw100+mmeerw125+mmeerw150+mmeerw200,\
           werk_reis_uren = orders_intern.c.werk_reis_uren+mreis,\
           meerminderwerk = orders_intern.c.meerminderwerk + meerk)
        con.execute(stmt)
        sel = select([orders_intern]).where(orders_intern.c.werkorderID == mwerknr)
        rpsel = con.execute(sel).first()
        self.urenbegrEdit.setText('{:<12.2f}'.format(rpsel[47]))
        self.urentotEdit.setText('{:<12.2f}'.format(rpsel[48]))
        lblptext = 'Totalen: Werkelijk / Begroot\nUren Schilderen'
        lbltext = 'Muteren uren (werken - lonen) niet cumulatief'
        self.lblprof.setText(lblptext)
        self.lblt.setStyleSheet("color: black")
        self.lblt.setText(lbltext)
    elif wrkgr > 109 and wrkgr < 113 and msoort < 5:
        stmt = update(orders_intern).where(orders_intern.c.werkorderID == mwerknr).\
            values(werk_lonen = orders_intern.c.werk_lonen+loonk,
           wspuiten = orders_intern.c.wspuiten+muren+mu125+mu150+mu200\
           +mmeerw100+mmeerw125+mmeerw150+mmeerw200,\
           werk_reis_uren = orders_intern.c.werk_reis_uren+mreis,\
           meerminderwerk = orders_intern.c.meerminderwerk + meerk)
        con.execute(stmt)
        sel = select([orders_intern]).where(orders_intern.c.werkorderID == mwerknr)
        rpsel = con.execute(sel).first()
        self.urenbegrEdit.setText('{:<12.2f}'.format(rpsel[49]))
        self.urentotEdit.setText('{:<12.2f}'.format(rpsel[50]))
        lblptext = 'Totalen: Werkelijk / Begroot\nUren Spuiten'
        lbltext = 'Muteren uren (werken - lonen) niet cumulatief'
        self.lblprof.setText(lblptext)
        self.lblt.setStyleSheet("color: black")
        self.lblt.setText(lbltext)
    elif wrkgr > 112 and wrkgr < 116 and msoort < 5:
        stmt = update(orders_intern).where(orders_intern.c.werkorderID == mwerknr).\
            values(werk_lonen = orders_intern.c.werk_lonen+loonk,
           wponsen = orders_intern.c.wponsen+muren+mu125+mu150+mu200\
           +mmeerw100+mmeerw125+mmeerw150+mmeerw200,\
           werk_reis_uren = orders_intern.c.werk_reis_uren+mreis,\
           meerminderwerk = orders_intern.c.meerminderwerk + meerk)
        con.execute(stmt)
        sel = select([orders_intern]).where(orders_intern.c.werkorderID == mwerknr)
        rpsel = con.execute(sel).first()
        self.urenbegrEdit.setText('{:<12.2f}'.format(rpsel[51]))
        self.urentotEdit.setText('{:<12.2f}'.format(rpsel[52]))
        lblptext = 'Totalen: Werkelijk / Begroot\nUren Ponsen'
        lbltext = 'Muteren uren (werken - lonen) niet cumulatief'
        self.lblprof.setText(lblptext)
        self.lblt.setStyleSheet("color: black")
        self.lblt.setText(lbltext)
    elif wrkgr > 115 and wrkgr < 119 and msoort < 5:
        stmt = update(orders_intern).where(orders_intern.c.werkorderID == mwerknr).\
            values(werk_lonen = orders_intern.c.werk_lonen+loonk,
           wpersen = orders_intern.c.wpersen+muren+mu125+mu150+mu200\
           +mmeerw100+mmeerw125+mmeerw150+mmeerw200,\
           werk_reis_uren = orders_intern.c.werk_reis_uren+mreis,\
           meerminderwerk = orders_intern.c.meerminderwerk + meerk)
        con.execute(stmt)
        sel = select([orders_intern]).where(orders_intern.c.werkorderID == mwerknr)
        rpsel = con.execute(sel).first()
        self.urenbegrEdit.setText('{:<12.2f}'.format(rpsel[53]))
        self.urentotEdit.setText('{:<12.2f}'.format(rpsel[54]))
        lblptext = 'Totalen: Werkelijk / Begroot\nUren Persen'
        lbltext = 'Muteren uren (werken - lonen) niet cumulatief'
        self.lblprof.setText(lblptext)
        self.lblt.setStyleSheet("color: black")
        self.lblt.setText(lbltext)
    elif wrkgr > 118 and wrkgr < 122 and msoort < 5:
        stmt = update(orders_intern).where(orders_intern.c.werkorderID == mwerknr).\
           values(werk_lonen = orders_intern.c.werk_lonen+loonk,
           wgritstralen = orders_intern.c.wgritstralen+muren+mu125+mu150+mu200\
           +mmeerw100+mmeerw125+mmeerw150+mmeerw200,\
           werk_reis_uren = orders_intern.c.werk_reis_uren+mreis,\
           meerminderwerk = orders_intern.c.meerminderwerk + meerk)
        con.execute(stmt)
        sel = select([orders_intern]).where(orders_intern.c.werkorderID == mwerknr)
        rpsel = con.execute(sel).first()
        self.urenbegrEdit.setText('{:<12.2f}'.format(rpsel[55]))
        self.urentotEdit.setText('{:<12.2f}'.format(rpsel[56]))
        lblptext = 'Totalen: Werkelijk / Begroot\nUren Gritstralen'
        lbltext = 'Muteren uren (werken - lonen) niet cumulatief'
        self.lblprof.setText(lblptext)
        self.lblt.setStyleSheet("color: black")
        self.lblt.setText(lbltext)
    elif wrkgr > 121 and wrkgr < 125 and msoort < 5:
        stmt = update(orders_intern).where(orders_intern.c.werkorderID == mwerknr).\
            values(werk_lonen = orders_intern.c.werk_lonen+loonk,
           wmontage = orders_intern.c.wmontage+muren+mu125+mu150+mu200\
           +mmeerw100+mmeerw125+mmeerw150+mmeerw200,\
           werk_reis_uren = orders_intern.c.werk_reis_uren+mreis,\
           meerminderwerk = orders_intern.c.meerminderwerk + meerk)
        con.execute(stmt)
        sel = select([orders_intern]).where(orders_intern.c.werkorderID == mwerknr)
        rpsel = con.execute(sel).first()
        self.urenbegrEdit.setText('{:<12.2f}'.format(rpsel[57]))
        self.urentotEdit.setText('{:<12.2f}'.format(rpsel[58]))
        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)
    else:
        msaldo = ''
        mboekuren = str(mboekuren)
        if msoort == 5 and wrkgr > 52:
            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 > 52:
            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 > 52:
            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 > 52:
            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 > 52:
            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 > 52:
            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 > 52:
            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) 
示例#32
0
  def getActiveJobs(self, limit=20, lastMonitor=None, jobAssignmentTag="Assigned"):
    """ Get  the FTSJobs that are not in a final state, and are not assigned for monitoring
        or has its operation being treated

        By assigning the job to the DB:
          * it cannot be monitored by another agent
          * the operation to which it belongs cannot be treated

       :param limit: max number of Jobs to retrieve
       :param lastMonitor: jobs monitored earlier than the given date
       :param jobAssignmentTag: if not None, block the Job for other queries,
                              and use it as a prefix for the value in the operation table

       :returns: list of FTS3Jobs

    """

    session = self.dbSession(expire_on_commit=False)

    try:
      # the tild sign is for "not"

      ftsJobsQuery = session.query(FTS3Job)\
          .join(FTS3Operation)\
          .filter(~FTS3Job.status.in_(FTS3Job.FINAL_STATES))\
          .filter(FTS3Job.assignment.is_(None))\
          .filter(FTS3Operation.assignment.is_(None))

      if lastMonitor:
        ftsJobsQuery = ftsJobsQuery.filter(FTS3Job.lastMonitor < lastMonitor)

      if jobAssignmentTag:
        ftsJobsQuery = ftsJobsQuery.with_for_update()

      ftsJobsQuery = ftsJobsQuery.order_by(FTS3Job.lastMonitor.asc())
      ftsJobsQuery = ftsJobsQuery.limit(limit)

      ftsJobs = ftsJobsQuery.all()

      if jobAssignmentTag:
        jobAssignmentTag += "_%s" % datetime.datetime.utcnow().strftime('%Y-%m-%d %H:%M:%S')

        jobIds = [job.jobID for job in ftsJobs]
        if jobIds:
          session.execute(update(FTS3Job)
                          .where(FTS3Job.jobID.in_(jobIds)
                                 )
                          .values({'assignment': jobAssignmentTag})
                          )

      session.commit()

      session.expunge_all()

      return S_OK(ftsJobs)

    except SQLAlchemyError as e:
      session.rollback()
      return S_ERROR("getAllActiveJobs: unexpected exception : %s" % e)
    finally:
      session.close()
示例#33
0
def get_update_query(table, user_id, token):
    update_query = update(table)
    update_query = update_query.values({"token": token})
    update_query = update_query.where(table.c.user_id == user_id)
    return update_query
示例#34
0
def update_answer(
    self,
    user_id: int,
    task_id: int,
    files: List[File],
    description: str,
    files_to_delete=None,
    reveal: datetime.datetime = pytz.utc.localize(datetime.datetime.utcnow())
) -> int:
    """Update the database answer to match. If no answer was submitted, a new one is created. If one is found, it is replaced
        Doesn't check if the user has rights to update the answer
    Arguments:
        user_id {int} -- [description]
        task_id {int} -- [description]
        files {List[File]} -- [description]
        description {str} -- [description]

    Keyword Arguments:
        reveal {datetime.datetime} -- [if this arguments is None, set reveal to be the assignment deadline] (default: {datetime.datetime.utcnow()}) 

    Returns:
        int -- [inserted (or exsisting answer id)]
    """

    self.logger.info("Updating answer for task %s for user %s", task_id,
                     str(user_id))

    with self.engine.connect() as conn:
        if reveal:
            reveal = reveal.astimezone(pytz.utc)
        else:

            j = self.task.join(self.assignment)
            sql = select([self.assignment.c.deadline
                          ]).select_from(j).where(self.task.c.id == task_id)
            rs = conn.execute(sql)
            row = rs.first()
            if row is None:
                self.logger.error("Invalid task id! %s", task_id)
                raise ValueError("Invalid task id!")
            reveal = row[self.assignment.c.deadline]
            self.logger.info(
                "Setting answer reveal same as assignment deadline %s", reveal)

        sql = select([self.answer.c.id
                      ]).where(self.answer.c.task_id == task_id)
        rs = conn.execute(sql)
        row = rs.first()

        if row is None:
            new_answer = True
            self.logger.info("no old answer found, creating new entry")
            sql = self.answer.insert().values(reveal=reveal,
                                              task_id=task_id,
                                              description=description)

            rs = conn.execute(sql)
            id = rs.inserted_primary_key[0]
            self.logger.info("Insert success! id: %s", id)
        else:
            new_answer = False
            id = row[self.answer.c.id]
            self.logger.info("old answer with id " + str(id) +
                             " found, updating values")
            sql = update(self.answer).values(
                reveal=reveal, task_id=task_id).where(self.answer.c.id == id)
            if description is not None:
                sql = sql.values(description=description)
            conn.execute(sql)
            self.logger.info("Update success!")

        if new_answer:
            self.update_file(user_id, files, answer_id=id)
        else:
            self.update_file(user_id,
                             files,
                             answer_id=id,
                             files_to_delete=files_to_delete)
    return id
示例#35
0
 def add_to_db(params, db_session, current_order):
     update_stmt = update(Inventory).where(
         Inventory.product_id == bindparam('prod')).values(
             {Inventory.quantity: Inventory.quantity - bindparam('qty')})
     db_session.execute(update_stmt, params)
     db_session.add(current_order)
示例#36
0
    def _create_or_update_package(self, package_dict, harvest_object):
        '''
        Creates a new package or updates an exisiting one according to the
        package dictionary provided. The package dictionary should look like
        the REST API response for a package:

        http://ckan.net/api/rest/package/statistics-catalunya

        Note that the package_dict must contain an id, which will be used to
        check if the package needs to be created or updated (use the remote
        dataset id).

        If the remote server provides the modification date of the remote
        package, add it to package_dict['metadata_modified'].


        TODO: Not sure it is worth keeping this function. If useful it should
        use the output of package_show logic function (maybe keeping support
        for rest api based dicts
        '''
        try:
            # Change default schema
            schema = default_create_package_schema()
            schema['id'] = [ignore_missing, unicode]
            schema['__junk'] = [ignore]

            # Check API version
            if self.config:
                try:
                    api_version = int(self.config.get('api_version', 2))
                except ValueError:
                    raise ValueError('api_version must be an integer')

                #TODO: use site user when available
                user_name = self.config.get('user', u'harvest')
            else:
                api_version = 2
                user_name = u'harvest'

            context = {
                'model': model,
                'session': Session,
                'user': user_name,
                'api_version': api_version,
                'schema': schema,
                'ignore_auth': True,
            }

            if self.config and self.config.get('clean_tags', False):
                tags = package_dict.get('tags', [])
                tags = [munge_tag(t) for t in tags if munge_tag(t) != '']
                tags = list(set(tags))
                package_dict['tags'] = tags

            # Check if package exists
            data_dict = {}
            data_dict['id'] = package_dict['id']
            try:
                existing_package_dict = get_action('package_show')(context,
                                                                   data_dict)

                # In case name has been modified when first importing. See issue #101.
                package_dict['name'] = existing_package_dict['name']

                # Check modified date
                if not 'metadata_modified' in package_dict or \
                   package_dict['metadata_modified'] > existing_package_dict.get('metadata_modified'):
                    log.info(
                        'Package with GUID %s exists and needs to be updated' %
                        harvest_object.guid)
                    # Update package
                    context.update({'id': package_dict['id']})
                    package_dict.setdefault('name',
                                            existing_package_dict['name'])
                    new_package = get_action('package_update_rest')(
                        context, package_dict)

                else:
                    log.info('Package with GUID %s not updated, skipping...' %
                             harvest_object.guid)
                    return

                # Flag the other objects linking to this package as not current anymore
                from ckanext.harvest.model import harvest_object_table
                conn = Session.connection()
                u = update(harvest_object_table) \
                        .where(harvest_object_table.c.package_id==bindparam('b_package_id')) \
                        .values(current=False)
                conn.execute(u, b_package_id=new_package['id'])

                # Flag this as the current harvest object

                harvest_object.package_id = new_package['id']
                harvest_object.current = True
                harvest_object.save()

            except NotFound:
                # Package needs to be created

                # Get rid of auth audit on the context otherwise we'll get an
                # exception
                context.pop('__auth_audit', None)

                # Set name for new package to prevent name conflict, see issue #117
                if package_dict.get('name', None):
                    package_dict['name'] = self._gen_new_name(
                        package_dict['name'])
                else:
                    package_dict['name'] = self._gen_new_name(
                        package_dict['title'])

                log.info(
                    'Package with GUID %s does not exist, let\'s create it' %
                    harvest_object.guid)
                harvest_object.current = True
                harvest_object.package_id = package_dict['id']
                # Defer constraints and flush so the dataset can be indexed with
                # the harvest object id (on the after_show hook from the harvester
                # plugin)
                harvest_object.add()

                model.Session.execute(
                    'SET CONSTRAINTS harvest_object_package_id_fkey DEFERRED')
                model.Session.flush()

                new_package = get_action('package_create_rest')(context,
                                                                package_dict)

            Session.commit()

            return True

        except ValidationError, e:
            log.exception(e)
            self._save_object_error(
                'Invalid package with GUID %s: %r' %
                (harvest_object.guid, e.error_dict), harvest_object, 'Import')
示例#37
0
文件: RequestDB.py 项目: vingar/DIRAC
            # If we are here, the request MUST exist, so no try catch
            # the joinedload_all is to force the non-lazy loading of all the attributes, especially _parent
            request = session.query( Request )\
                             .options( joinedload_all( '__operations__.__files__' ) )\
                             .filter( Request.RequestID == requestID )\
                             .one()

            if not reqID:
                log.verbose("selected request %s('%s')%s" %
                            (request.RequestID, request.RequestName,
                             ' (Assigned)' if assigned else ''))

            if assigned:
                session.execute( update( Request )\
                                 .where( Request.RequestID == requestID )\
                                 .values( {Request._Status : 'Assigned',
                                           Request._LastUpdate : datetime.datetime.utcnow()\
                                                                .strftime( Request._datetimeFormat )} )
                               )
                session.commit()

            session.expunge_all()
            return S_OK(request)

        except Exception as e:
            session.rollback()
            log.exception("getRequest: unexpected exception", lException=e)
            return S_ERROR("getRequest: unexpected exception : %s" % e)
        finally:
            session.close()
示例#38
0
def regelsOrder(rpova, afd):
    for rij in rpova:

        class RegelWindow(QDialog):
            def __init__(self):
                QDialog.__init__(self)

                grid = QGridLayout()
                grid.setSpacing(20)

                self.setWindowTitle("Bestelling Artikelregel")
                self.setWindowIcon(QIcon('./images/logos/logo.jpg'))

                self.setFont(QFont('Arial', 10))

                self.lbl = QLabel()
                self.pixmap = QPixmap('./images/logos/verbinding.jpg')
                self.lbl.setPixmap(self.pixmap)
                grid.addWidget(self.lbl, 0, 0)

                grid.addWidget(
                    QLabel('Bestelling Artikelregel: ' + str(rij[6])), 0, 1, 1,
                    2)

                self.logo = QLabel()
                self.pixmap = QPixmap('./images/logos/logo.jpg')
                self.logo.setPixmap(self.pixmap)
                grid.addWidget(self.logo, 0, 5, 1, 1, Qt.AlignRight)

                lbl1 = QLabel('OrderregelID')
                q1Edit = QLineEdit(str(rij[0]))
                q1Edit.setFixedWidth(90)
                q1Edit.setFont(QFont("Arial", 10))
                q1Edit.setDisabled(True)

                grid.addWidget(lbl1, 1, 0)
                grid.addWidget(q1Edit, 1, 1)

                lbl2 = QLabel('Ordernummer')
                q2Edit = QLineEdit(str(rij[1]))
                q2Edit.setFixedWidth(100)
                q2Edit.setFont(QFont("Arial", 10))
                q2Edit.setDisabled(True)

                grid.addWidget(lbl2, 1, 2)
                grid.addWidget(q2Edit, 1, 3)

                lbl3 = QLabel('Artikelnummer')
                q3Edit = QLineEdit(str(rij[10]))
                q3Edit.setFixedWidth(100)
                q3Edit.setFont(QFont("Arial", 10))
                q3Edit.setDisabled(True)

                grid.addWidget(lbl3, 2, 0)
                grid.addWidget(q3Edit, 2, 1)

                lbl9 = QLabel('Omschrijving')
                q9Edit = QLineEdit(str(rij[11]))
                q9Edit.setFixedWidth(350)
                q9Edit.setFont(QFont("Arial", 10))
                q9Edit.setDisabled(True)

                grid.addWidget(lbl9, 2, 2)
                grid.addWidget(q9Edit, 2, 3, 1, 3)

                lbl4 = QLabel('Aantal')
                q4Edit = QLineEdit(str(rij[3]))
                q4Edit.setFixedWidth(100)
                q4Edit.setFont(QFont("Arial", 10))
                q4Edit.setDisabled(True)

                grid.addWidget(lbl4, 3, 0)
                grid.addWidget(q4Edit, 3, 1)

                lbl5 = QLabel('Verkoopprijs')
                q5Edit = QLineEdit(str(rij[5]))
                q5Edit.setFixedWidth(100)
                q5Edit.setFont(QFont("Arial", 10))
                q5Edit.setDisabled(True)

                grid.addWidget(lbl5, 3, 2)
                grid.addWidget(q5Edit, 3, 3)

                lbl6 = QLabel('Leverdatum')
                q6Edit = QLineEdit(str(rij[4]))
                q6Edit.setFixedWidth(100)
                q6Edit.setFont(QFont("Arial", 10))
                q6Edit.setDisabled(True)

                grid.addWidget(lbl6, 4, 0)
                grid.addWidget(q6Edit, 4, 1)

                lbl7 = QLabel('RetourLevering')
                q7Edit = QLineEdit(str(rij[7]))
                q7Edit.setFixedWidth(100)
                q7Edit.setFont(QFont("Arial", 10))
                q7Edit.setDisabled(True)

                grid.addWidget(lbl7, 4, 2)
                grid.addWidget(q7Edit, 4, 3)

                lbl8 = QLabel('Betaaldatum')
                q8Edit = QLineEdit(str(rij[8]))
                q8Edit.setFixedWidth(100)
                q8Edit.setFont(QFont("Arial", 10))
                q8Edit.setDisabled(True)

                grid.addWidget(lbl8, 5, 0)
                grid.addWidget(q8Edit, 5, 1)

                cBox = QCheckBox('Betaling ontvangen')
                cBox.stateChanged.connect(self.cBoxChanged)
                if not afd or afd == 2 or rij[8]:
                    cBox.setDisabled(True)
                grid.addWidget(cBox, 5, 2)

                lbl9 = QLabel('Datum geleverd')
                q9Edit = QLineEdit(str(rij[9]))
                q9Edit.setFixedWidth(100)
                q9Edit.setFont(QFont("Arial", 10))
                q9Edit.setDisabled(True)

                grid.addWidget(lbl9, 5, 3)
                grid.addWidget(q9Edit, 5, 4)

                cBox1 = QCheckBox('Geleverd')
                cBox1.stateChanged.connect(self.cBox1Changed)
                if afd or rij[9]:
                    cBox1.setDisabled(True)
                grid.addWidget(cBox1, 5, 5)

                regelBtn = QPushButton('Regel')
                regelBtn.clicked.connect(self.accept)

                grid.addWidget(regelBtn, 6, 5)
                regelBtn.setFont(QFont("Arial", 10))
                regelBtn.setFixedWidth(100)
                regelBtn.setStyleSheet(
                    "color: black;  background-color: gainsboro")

                grid.addWidget(
                    QLabel(
                        '\u00A9 2017 all rights reserved [email protected]'),
                    7, 1, 1, 3, Qt.AlignCenter)

                self.setLayout(grid)
                self.setGeometry(250, 300, 150, 150)

            state = False

            def cBoxChanged(self, state):
                if state == Qt.Checked:
                    self.state = True

            def returncBox(self):
                return self.state

            state1 = False

            def cBox1Changed(self, state1):
                if state1 == Qt.Checked:
                    self.state1 = True

            def returncBox1(self):
                return self.state1

            @staticmethod
            def getData(parent=None):
                dialog = RegelWindow()
                dialog.exec_()
                return [dialog.returncBox(), dialog.returncBox1()]

        regelWin = RegelWindow()
        data = regelWin.getData()

        metadata = MetaData()
        orders_verkoop_artikelen = Table(
            'orders_verkoop_artikelen', metadata,
            Column('ovaID', Integer, primary_key=True),
            Column('ovbestelID', None,
                   ForeignKey('orders_verkoop.ovbestelID')),
            Column('regel', Integer), Column('betaaldatum', String),
            Column('leveringsdatum', String))

        engine = create_engine(
            'postgresql+psycopg2://postgres@localhost/bisystem')
        con = engine.connect()
        selb = select([orders_verkoop_artikelen]).where(and_(orders_verkoop_artikelen.\
            c.ovbestelID == rij[1], orders_verkoop_artikelen.c.regel == rij[6]))
        rpb = con.execute(selb).first()
        if data[0] and not rpb[3]:
            up = update(orders_verkoop_artikelen).where(and_(orders_verkoop_artikelen\
             .c.regel == rij[6], orders_verkoop_artikelen.c.ovbestelID == rij[1]))\
             .values(betaaldatum = str(datetime.datetime.now())[0:10])
            con.execute(up)
        if data[1] and not rpb[4]:
            upl = update(orders_verkoop_artikelen).where(and_(orders_verkoop_artikelen\
             .c.regel == rij[6], orders_verkoop_artikelen.c.ovbestelID == rij[1]))\
             .values(leveringsdatum = str(datetime.datetime.now())[0:10])
            con.execute(upl)
        metadata = MetaData()
        orders_verkoop = Table('orders_verkoop', metadata,
                               Column('ovbestelID', Integer, primary_key=True),
                               Column('datum_betaald', String),
                               Column('datum_geleverd', String))
        orders_verkoop_artikelen = Table(
            'orders_verkoop_artikelen', metadata,
            Column('ovaID', Integer, primary_key=True),
            Column('ovbestelID', None,
                   ForeignKey('orders_verkoop.ovbestelID')),
            Column('regel', Integer), Column('betaaldatum', String),
            Column('leveringsdatum', String))
        engine = create_engine(
            'postgresql+psycopg2://postgres@localhost/bisystem')
        con = engine.connect()
        selb = select([orders_verkoop, orders_verkoop_artikelen]).where(and_\
            (orders_verkoop_artikelen.c.ovbestelID==rij[1], orders_verkoop_artikelen.\
             c.ovbestelID == orders_verkoop.c.ovbestelID)).order_by\
            (orders_verkoop_artikelen.c.regel.desc())
        rpbal = con.execute(selb)
        rpbmax = con.execute(selb).first()
        mrgl = rpbmax[5]
        mregel = 0
        if not rpbmax[1]:
            for item in rpbal:
                mbet = item[6]
                if mbet:
                    mregel += 1
                    if mrgl == mregel:
                        updbet = update(orders_verkoop).where(orders_verkoop.c.ovbestelID ==\
                         rij[1]).values(datum_betaald = str(datetime.datetime.now())[0:10])
                        con.execute(updbet)
                        rpbal = con.execute(selb)
        mregel1 = 0
        if not rpbmax[2]:
            for item1 in rpbal:
                mlev = item1[7]
                if mlev:
                    mregel1 += 1
                    if mrgl == mregel1:
                        updlev =  update(orders_verkoop).where(orders_verkoop.c.ovbestelID ==\
                         rij[1]).values(datum_geleverd = str(datetime.datetime.now())[0:10])
                        con.execute(updlev)
示例#39
0
def schoonCalculatie(mcalnr, m_email):
    msgBox = QMessageBox()
    msgBox.setStyleSheet("color: black;  background-color: gainsboro")
    msgBox.setWindowIcon(QIcon('./images/logos/logo.jpg'))
    msgBox.setWindowTitle("Calculatie Aanpassen")
    msgBox.setIcon(QMessageBox.Warning)
    msgBox.setText(
        "Deze calculatie bestaat al\nwilt u deze calculatie aanpassen?")
    msgBox.setStandardButtons(QMessageBox.Yes)
    msgBox.addButton(QMessageBox.No)
    msgBox.setDefaultButton(QMessageBox.Yes)
    if (msgBox.exec_() == QMessageBox.Yes):
        metadata = MetaData()
        calculaties = Table('calculaties', metadata,
                            Column('calcID', Integer(), primary_key=True),
                            Column('calculatie', Integer),
                            Column('prijs',
                                   Float), Column('materialen', Float),
                            Column('lonen', Float), Column('diensten', Float),
                            Column('materieel',
                                   Float), Column('inhuur', Float),
                            Column('uren_constr', Float),
                            Column('uren_mont', Float),
                            Column('uren_retourlas', Float),
                            Column('uren_telecom', Float),
                            Column('uren_bfi', Float),
                            Column('uren_voeding', Float),
                            Column('uren_bvl', Float),
                            Column('uren_spoorleg', Float),
                            Column('uren_spoorlas', Float),
                            Column('uren_inhuur', Float),
                            Column('sleuvengraver', Float),
                            Column('persapparaat', Float),
                            Column('atlaskraan', Float),
                            Column('kraan_groot', Float),
                            Column('mainliner', Float),
                            Column('hormachine', Float),
                            Column('wagon', Float), Column('locomotor', Float),
                            Column('locomotief', Float),
                            Column('montagewagen', Float),
                            Column('stormobiel', Float),
                            Column('robeltrein', Float),
                            Column('werkomschrijving', String),
                            Column('verwerkt', Integer))
        materiaallijsten = Table(
            'materiaallijsten', metadata,
            Column('matlijstID', Integer, primary_key=True),
            Column('calculatie', Integer))

        engine = create_engine(
            'postgresql+psycopg2://postgres@localhost/bisystem')
        con = engine.connect()
        updcal = update(calculaties).where(calculaties.c.calculatie == mcalnr).values\
          (prijs=0, materialen=0, lonen=0,diensten=0,materieel=0,inhuur=0,uren_constr=0,\
          uren_mont=0,uren_retourlas=0,uren_telecom=0,uren_bfi=0,uren_voeding=0,\
          uren_bvl=0,uren_spoorleg=0,uren_spoorlas=0,uren_inhuur=0,sleuvengraver=0,\
          persapparaat=0,atlaskraan=0,kraan_groot=0,mainliner=0,hormachine=0,\
          wagon=0,locomotor=0,locomotief=0,montagewagen=0,stormobiel=0,robeltrein=0,\
          verwerkt=0)
        con.execute(updcal)
        delmat = delete(materiaallijsten).where(
            materiaallijsten.c.calculatie == mcalnr)
        con.execute(delmat)
    else:
        zoeken(m_email)
示例#40
0
activeJobs = ftsJobsQuery.all()

#activeJobs = session.query(FTS3Job).filter(FTS3Job.jobID == 1787750).all()
session.commit()
session.expunge_all()
session.close()

fileID_jobGUID = {}
for job in activeJobs:
    #print job
    guid = job.ftsGUID
    fileStatus = job.monitor()['Value']
    for fID in fileStatus:
        fileID_jobGUID[fID] = guid

print fileID_jobGUID

for fileID, jobGUID in fileID_jobGUID.iteritems():
    session = db.dbSession(expire_on_commit=False)

    updateDict = {FTS3File.ftsGUID: jobGUID}
    upQuery = update(FTS3File).where(and_(FTS3File.fileID == fileID,
                               ~ FTS3File.status.in_(FTS3File.FINAL_STATES)
                                      )
                                 )\
                          .values(updateDict)
    session.execute(upQuery)
    session.commit()
    session.expunge_all()
    session.close()
示例#41
0
async def buy(client, event,
        item : ([(item.name, item.id) for item in BUYABLE], 'Select the item to buy nya!'),
        amount : (int, 'How much items would you want to buy?'),
            ):
    """Buy?"""
    try:
        item = ITEMS[item]
    except KeyError:
        abort('Item not available.')
    
    permissions = event.channel.cached_permissions_for(client)
    if (not permissions.can_send_messages) or (not permissions.can_add_reactions):
        abort('I need `send messages` and `add reactions` permissions to execute the command.')
    
    yield
    
    user = event.user
    async with DB_ENGINE.connect() as connector:
        response = await connector.execute(
            select([currency_model.total_love]). \
                where(currency_model.user_id==user.id))
        
        results = await response.fetchall()
        if results:
            total_love = results[0]
        else:
            total_love = 0
    
    embed = Embed('Confirm buying',
        f'Selected item: {item.emoji:e} **{item.name}**\n'
        f'Amount: **{amount}**\n'
        f'\n'
        f'Price: {calculate_buy_cost(item.market_cost, amount)} {EMOJI__HEART_CURRENCY:e}\n'
        f'Budget: {total_love} {EMOJI__HEART_CURRENCY:e}'
    )
    
    embed.add_author(user.avaar_url, user.full_name)
    embed.add_footer('The prices of context of demand and supply.')
    
    message = await client.message_create(event.channel, embed=embed)
    await client.reaction_add(message, item.emoji)
    await client.reaction_add(message, CONFIRM_NAH)
    
    try:
        event = await wait_for_reaction(client, message, partial_func(check_confirm_emoji, item.emoji), 300.0)
    except TimeoutError:
        return
    
    if event.emoji is CONFIRM_NAH:
        embed.title = 'Buying cancelled'
    else:
        user = event.user
        async with DB_ENGINE.connect() as connector:
            response = await connector.execute(
                select([currency_model.total_love, currency_model.total_allocated]). \
                    where(currency_model.user_id==user.id))
            
            results = await response.fetchall()
            if results:
                total_love, total_allocated = results[0]
            else:
                total_love = total_allocated = 0
            
            if total_love == 0:
                amount = cost = 0
            else:
                amount, cost = calculate_buyable_and_cost(item.market_cost, amount, total_love-total_allocated)
                
                item.market_cost += amount
            
            if cost == 0:
                new_love = total_love
            else:
                new_love = total_love-cost
                await connector.execute(update(currency_model.user_id==user.id). \
                    values(total_love = new_love))
                
                response = await connector.execute(select([item_model.id, item_model.amount]). \
                    where(item_model.user_id==user.id).where(item_model.type==item.id))
                
                results = await response.fetchall()
                if results:
                    row_id, actual_amount = results[0]
                    new_amount = actual_amount+amount
                    to_execute = ITEM_TABLE.update().values(
                        amount=new_amount
                            ).where(item_model.id==row_id)
                else:
                    to_execute = ITEM_TABLE.insert().values(
                        user_id = user.id,
                        amount  = amount,
                        type    = item.id
                    )
                
                await connector.execute(to_execute)
        
        embed.title = 'Buying confirmed'
        embed.description = (
            f'Selected item: {item.emoji:e} **{item.name}**\n'
            f'Bought mount: **{amount}**\n'
            f'\n'
            f'Hearts: {total_love} {EMOJI__HEART_CURRENCY:e} -> {new_love} {EMOJI__HEART_CURRENCY:e}'
        )
    
    await client.message_edit(message, embed=embed)
示例#42
0
def main():
    """
    Usage:
    manage.py db upgrade
    python index_walker.py

    This script is going to populate Account and Campaign database structures.  It does this
    by reading the currently deployed tile distributions (s3), where it determines the currently active
    tile set, as well as the geo-targetting data (currently only country level) for each tile/adgroup.

    The script will discriminate between 'active' and 'inactive' adgroups based on whether or not
    the adgroup exists in the current distribution.  Inactive adgroups are given start/end dates
    in campaigns that are in the *past*.  Active adgroups are placed in campaigns that start on their
    adgroup creation date and end at some far distant future date.

    We are using some data structures developed by the Zenko project to build the derive_account_campaign()
    function in order to identify existing campaigns from our tile data.

    Campaign objects are considered unique by grouping together the following keys in the adgroup:
    * the name of the campaign and account returned by derive_account_campaign()
    * the channel of the adgroup
    * the 'active' flag (determined as explained above) of the adgroup

    One campaign row will be assigned for each unique campaign detected.

    The script will populate the adgroup.campaign_id with the campaign that the adgroup fits into.

    All writes to the database are transactional.

    This script is *not* idempotent, and will therefore check that accounts and campaigns tables are empty before running.

    :return:
    """
    index_files = [
        'https://tiles-resources-prod-tiless3-qbv71djahz3b.s3.amazonaws.com/hello_tile_index_v3.json',
        'https://tiles-resources-prod-tiless3-qbv71djahz3b.s3.amazonaws.com/android_tile_index_v3.json',
        'https://tiles-resources-prod-tiless3-qbv71djahz3b.s3.amazonaws.com/desktop_tile_index_v3.json',
        'https://tiles-resources-prod-tiless3-qbv71djahz3b.s3.amazonaws.com/desktop-prerelease_tile_index_v3.json'
    ]
    active_tiles = set()
    tile_geodes = defaultdict(set)

    for index in index_files:
        r = requests.get(index)
        if 200 <= r.status_code <= 299:
            data = json.loads(r.text)

            for geo_locale, dist_dict in data.iteritems():
                try:
                    ag = dist_dict.get('ag')
                    if ag:
                        geode = tuple(geo_locale.split('/'))
                        print "processing ", geo_locale
                        ag_r = requests.get(ag)
                        if 200 <= ag_r.status_code <= 299:
                            tiles = json.loads(ag_r.text)
                            directory_tiles = tiles['directory']
                            suggested_tiles = tiles['suggested']
                            newts = set(chain((t['directoryId'] for t in directory_tiles),
                                        (t['directoryId'] for t in suggested_tiles)))
                            active_tiles.update(newts)
                            for tile_id in newts:
                                tile_geodes[tile_id].add(geode)
                except:
                    print "skipping ", geo_locale

    # print "active", str(active_tiles)

    env = Environment.instance()

    db_uri = env.config.SQLALCHEMY_DATABASE_URI
    engine = create_engine(db_uri)
    connection = engine.connect()

    try:
        # assert that campaigns and accounts are empty
        account_count, = connection.execute("SELECT count(*) FROM accounts").fetchone()
        assert account_count == 0, "Accounts not empty"
        campaign_count, = connection.execute("SELECT count(*) FROM campaigns").fetchone()
        assert campaign_count == 0, "Campaigns not empty"

        # collate/generate campaign and account data
        # stmt = select([Adgroup.id, Tile.target_url, Adgroup.channel_id, Adgroup.created_at]). \
        #     where(Tile.adgroup_id == Adgroup.id)
        stmt = """SELECT a.id, t.target_url, t.title, a.channel_id, a.created_at, c.name,
                    t.id, t.image_uri, t.enhanced_image_uri
                  FROM adgroups a
                  JOIN tiles t on t.adgroup_id = a.id
                  JOIN channels c on a.channel_id = c.id"""
        result = connection.execute(stmt)

        campaign_id = 0
        account_id = 0
        campaigns = dict()
        adgroups = defaultdict(list)
        countries = defaultdict(set)
        accounts = dict()

        for adgroup_id, url, title, channel, created_at, channel_name, tile_id, i_url, ei_url in result:
            assert all(x is not None for x in (adgroup_id, url, channel)), \
                "Some of %s is None" % str((adgroup_id, url, channel))

            # do tld -> account mapping substitution
            active = adgroup_id in active_tiles
            account_name, campaign_name = derive_account_campaign(adgroup_id, title, url)
            curr = (account_name, campaign_name, channel, active)
            if curr not in campaigns:
                # this is a new campaign, see if it's active
                campaign_id += 1
                if active:
                    # print "active", curr
                    start_date = created_at.date()
                    end_date = ARBITRARY_FUTURE
                else:
                    start_date = created_at.date()
                    end_date = created_at.date()

                # insert it into the right account
                if account_name not in accounts:
                    account_id += 1
                    next_account_id = account_id
                    accounts[account_name] = account_id
                else:
                    next_account_id = accounts[account_name]

                active_name = '' if active else ' (Closed)'
                ctuple = (campaign_id, start_date, end_date,
                          "%s %s%s" % (safe_str(campaign_name), channel_name, active_name),
                          False, channel, next_account_id)
                campaigns[curr] = ctuple

                # append all the countries
                for sub_country_code, sub_locale in tile_geodes[adgroup_id]:
                    countries[campaign_id].add(sub_country_code)
                # this fixes the closed campaigns can't get the correct country code as above
                if account_name in _campaign_countries:
                    countries[campaign_id] = countries[campaign_id].union(_campaign_countries[account_name])
                # print "campaign", ctuple

            adgroups[campaigns[curr][0]].append(adgroup_id)

        # insert data into new tables
        Session = sessionmaker(bind=engine)
        session = Session()

        # we need to monkeypatch flask's monkeypatch...
        session._model_changes = None

        try:
            # grab all s3 images and reproduce image hash
            bucket = env.s3.get_bucket(env.config.S3["bucket"])
            images = bucket.list('images/')

            image_hashes = defaultdict(list)
            enhanced_image_hashes = defaultdict(list)
            stmt = "SELECT t.id, t.image_uri, t.enhanced_image_uri FROM tiles t"
            for tile_id, image_uri, enhanced_image_uri in connection.execute(stmt):
                image_hashes[image_uri].append(tile_id)
                enhanced_image_hashes[enhanced_image_uri].append(tile_id)

            for image in images:
                ext = image.key.split('.')[-1]
                if ext == 'svg':
                    ext = 'svg+xml'
                elif ext == 'jpeg':
                    ext = 'jpg'
                new_hash = hashlib.sha1("data:image/%s;base64,%s" %
                                        (ext, base64.b64encode(image.get_contents_as_string()))).hexdigest()
                new_uri = image.generate_url(expires_in=0, query_auth=False)
                # remove x-amz-security-token, which is inserted even if query_auth=False
                # ref: https://github.com/boto/boto/issues/1477
                uri = furl(new_uri)
                try:
                    uri.args.pop('x-amz-security-token')
                except:
                    pass
                new_uri = uri.url

                tile_ids = image_hashes.get(new_hash)
                if tile_ids:
                    print "image: %s" % image.key
                    session.execute("update tiles set image_uri = '%s' where id in (%s)" %
                                    (new_uri, ','.join(str(tid) for tid in tile_ids)))

                tile_ids = enhanced_image_hashes.get(new_hash)
                if tile_ids:
                    print "enhanced_image: %s" % image.key
                    session.execute("update tiles set enhanced_image_uri = '%s' where id in (%s)" %
                                    (new_uri, ','.join(str(tid) for tid in tile_ids)))

            account_stmt = insert(Account).values([dict(id=aid, name=aname) for aname, aid in accounts.iteritems()])
            session.execute(account_stmt)
            session.execute("SELECT setval('accounts_id_seq', %s, false)" % (account_id + 1))

            target_query = StringIO()
            target_query.write("""insert into campaigns(id, start_date, end_date, name, paused, channel_id, account_id) values """)
            pg2_cursor = session.connection().connection.cursor()
            for campaign_tuple in campaigns.values():
                # print "%s %s" % (type(campaign_tuple), campaign_tuple)
                target_query.write(unicode(pg2_cursor.mogrify("(%s,%s,%s,%s,%s,%s,%s),", campaign_tuple)))
            session.execute(target_query.getvalue()[:-1])
            session.execute("SELECT setval('campaigns_id_seq', %s, false)" % (campaign_id + 1))

            cc_stmt = insert(CampaignCountry).values([dict(country_code=cc, campaign_id=cid)
                                                      for cid, cc_list in countries.iteritems()
                                                      for cc in cc_list])
            session.execute(cc_stmt)

            adgroup_updates = [update(Adgroup)
                               .where(Adgroup.id.in_(tuple(adgroup_ids)))
                               .values(dict(campaign_id=cid, type="directory", name="adgoup_cpmg_%d" % cid))
                               for cid, adgroup_ids in adgroups.iteritems()]
            for adgroup_stmt in adgroup_updates:
                session.execute(adgroup_stmt)
            # set the type for the suggested adgroups
            session.execute("update adgroups set type = 'suggested' where id in (select distinct adgroup_id from adgroup_sites)")

            session.commit()
        except Exception as e:
            print "Error: ", str(e)
            session.rollback()
            raise e
    finally:
        connection.close()
        print "done"
示例#43
0
def update_article_date(article_id, new_day, new_month, new_year):
    u = update(articles_table).where(articles_table.c.articleID == article_id)
    u = u.values(day=new_day, month=new_month, year=new_year)
    result = connection.execute(u)
    print(result.rowcount)
示例#44
0
def update_category(category_id, new_category_name):
    u = update(categories_table).where(
        categories_table.c.categoryID == category_id)
    u = u.values(name=new_category_name)
    result = connection.execute(u)
示例#45
0
 def change_password(self, user_id, password):
     s = update(self.object_type.__table__, User.id==user_id, {'password': password})
     self.db_session.session.execute(s)
     self.commit()
示例#46
0
def update_article_publication(article_id, new_publication):
    u = update(articles_table).where(articles_table.c.articleID == article_id)
    u = u.values(publication=new_publication)
    result = connection.execute(u)
    print(result.rowcount)
示例#47
0
 def update_credit(self, user_id, value):
     s = update(self.object_type.__table__, User.id==user_id, {'credit': value})
     self.db_session.session.execute(s)
     self.commit()
     self.emit('credit_update', user_id, value)
示例#48
0
def update_article_author(article_id, new_author):
    u = update(articles_table).where(articles_table.c.articleID == article_id)
    u = u.values(author=new_author)
    result = connection.execute(u)
    print(result.rowcount)
示例#49
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)
示例#50
0
def update_article_category(article_id, new_category):
    u = update(articles_table).where(articles_table.c.articleID == article_id)
    u = u.values(categoryID=new_category)
    result = connection.execute(u)
    print(result.rowcount)
示例#51
0
 def _removePlayerFromList(self, role, index, stato):
     players, metadata = self._getRoleModel(role)
     stmt = update(players).where(players.c.id == index).values(stato=stato)
     self.con_legadb.execute(stmt)
示例#52
0
 def _update_atom_details(self, conn, ad, e_ad):
     e_ad.merge(ad)
     conn.execute(
         sql.update(self._tables.atomdetails).where(
             self._tables.atomdetails.c.uuid == e_ad.uuid).values(
                 e_ad.to_dict()))
示例#53
0
    def get_best_node(self, service):
        """Returns the 'least loaded' node currently available, increments the
        active count on that node, and decrements the slots currently available
        """
        nodes = self._get_nodes_table(service)
        service = self._get_service_id(service)

        # Pick the least-loaded node that has available slots.
        where = [
            nodes.c.service == service, nodes.c.available > 0,
            nodes.c.capacity > nodes.c.current_load, nodes.c.downed == 0,
            nodes.c.backoff == 0
        ]

        query = select([nodes]).where(and_(*where))

        if self._is_sqlite:
            # sqlite doesn't have the 'log' funtion, and requires
            # coercion to a float for the sorting to work.
            query = query.order_by(nodes.c.current_load * 1.0 /
                                   nodes.c.capacity)
        else:
            # using log() increases floating-point precision on mysql
            # and thus makes the sorting more accurate.
            query = query.order_by(
                sqlfunc.log(nodes.c.current_load) /
                sqlfunc.log(nodes.c.capacity))
        query = query.limit(1)

        # We may have to re-try the query if we need to release more capacity.
        # This loop allows a maximum of five retries before bailing out.
        for _ in xrange(5):
            res = self._safe_execute(query)
            row = res.fetchone()
            res.close()
            if row is None:
                # Try to release additional capacity from any nodes
                # that are not fully occupied.
                where = and_(nodes.c.service == service,
                             nodes.c.available <= 0,
                             nodes.c.capacity > nodes.c.current_load,
                             nodes.c.downed == 0)
                fields = {
                    'available':
                    sqlfunc.min(nodes.c.capacity * self.capacity_release_rate,
                                nodes.c.capacity - nodes.c.current_load),
                }
                res = self._safe_execute(update(nodes, where, fields))
                res.close()
                if res.rowcount == 0:
                    break

        # Did we succeed in finding a node?
        if row is None:
            raise BackendError('unable to get a node')

        nodeid = row.id
        node = str(row.node)

        # Update the node to reflect the new assignment.
        # This is a little racy with concurrent assignments, but no big deal.
        where = [nodes.c.service == service, nodes.c.node == node]
        where = and_(*where)
        fields = {
            'available': nodes.c.available - 1,
            'current_load': nodes.c.current_load + 1
        }
        query = update(nodes, where, fields)
        con = self._safe_execute(query, close=True)
        con.close()

        return nodeid, node
def schoonCalculatie(mcalnr, m_email):
    msgBox = QMessageBox()
    msgBox.setStyleSheet("color: black;  background-color: gainsboro")
    msgBox.setWindowIcon(QIcon('./images/logos/logo.jpg'))
    msgBox.setWindowTitle("Calculatie Aanpassen")
    msgBox.setIcon(QMessageBox.Warning)
    msgBox.setText(
        "Deze calculatie bestaat al\nwilt u deze calculatie aanpassen?")
    msgBox.setStandardButtons(QMessageBox.Yes)
    msgBox.addButton(QMessageBox.No)
    msgBox.setDefaultButton(QMessageBox.Yes)
    if (msgBox.exec_() == QMessageBox.Yes):
        metadata = MetaData()
        icalculaties = Table(
            'icalculaties', metadata,
            Column('icalcID', Integer(), primary_key=True),
            Column('icalculatie', Integer),
            Column('iclusterID', None, ForeignKey('iclusters.iclusterID')),
            Column('omschrijving', String), Column('werkomschrijving', String),
            Column('verwerkt', Integer), Column('hoeveelheid', Float),
            Column('eenheid', String), Column('koppelnummer', Integer),
            Column('prijs', Float), Column('materialen', Float),
            Column('lonen', Float), Column('materieel', Float),
            Column('diensten', Float), Column('inhuur', Float),
            Column('szagen', Float), Column('zagen', Float),
            Column('sschaven', Float), Column('schaven', Float),
            Column('ssteken', Float), Column('steken', Float),
            Column('sboren', Float), Column('boren', Float),
            Column('sfrezen', Float), Column('frezen', Float),
            Column('sdraaien_klein', Float), Column('draaien_klein', Float),
            Column('sdraaien_groot', Float), Column('draaien_groot', Float),
            Column('stappen', Float), Column('tappen', Float),
            Column('snube_draaien', Float), Column('nube_draaien', Float),
            Column('snube_bewerken', Float), Column('nube_bewerken', Float),
            Column('sknippen', Float), Column('knippen', Float),
            Column('skanten', Float), Column('kanten', Float),
            Column('sstansen', Float), Column('stansen', Float),
            Column('slassen_co2', Float), Column('lassen_co2', Float),
            Column('slassen_hand', Float), Column('lassen_hand', Float),
            Column('sverpakken', Float), Column('verpakken', Float),
            Column('sverzinken', Float), Column('verzinken', Float),
            Column('smoffelen', Float), Column('moffelen', Float),
            Column('sschilderen', Float), Column('schilderen', Float),
            Column('sspuiten', Float), Column('spuiten', Float),
            Column('sponsen', Float), Column('ponsen', Float),
            Column('spersen', Float), Column('persen', Float),
            Column('sgritstralen', Float), Column('gritstralen', Float),
            Column('smontage', Float), Column('montage', Float))
        materiaallijsten = Table(
            'materiaallijsten', metadata,
            Column('matlijstID', Integer, primary_key=True),
            Column('icalculatie', Integer))

        engine = create_engine(
            'postgresql+psycopg2://postgres@localhost/bisystem')
        con = engine.connect()
        updcal = update(icalculaties).where(icalculaties.c.icalculatie == mcalnr).values\
          (prijs=0, materialen=0, lonen=0,diensten=0,materieel=0,inhuur=0,szagen=0,\
           zagen=0,sschaven=0,schaven=0,ssteken=0,steken=0,sboren=0,boren=0,sfrezen=0,\
           frezen=0,sdraaien_klein=0,draaien_klein=0,sdraaien_groot=0,draaien_groot=0,\
           stappen=0,tappen=0,snube_draaien=0,nube_draaien=0,snube_bewerken=0,\
           nube_bewerken=0,sknippen=0,knippen=0,skanten=0,kanten=0,sstansen=0,stansen=0,\
           slassen_co2=0,lassen_co2=0,slassen_hand=0,lassen_hand=0,sverpakken=0,\
           verpakken=0,sverzinken=0,verzinken=0,smoffelen=0,moffelen=0,sschilderen=0,\
           schilderen=0,sspuiten=0,spuiten=0,sponsen=0,ponsen=0,spersen=0,persen=0,\
           sgritstralen=0,gritstralen=0,smontage=0,montage=0,verwerkt=0)
        con.execute(updcal)
        delmat = delete(materiaallijsten).where(
            materiaallijsten.c.icalculatie == mcalnr)
        con.execute(delmat)
    else:
        zoeken(m_email)
示例#55
0
  def updateFileStatus(self, fileStatusDict, ftsGUID=None):
    """Update the file ftsStatus and error
        The update is only done if the file is not in a final state
        (To avoid bringing back to life a file by consuming MQ a posteriori)



        TODO: maybe it should query first the status and filter the rows I want to update !

       :param fileStatusDict: { fileID : { status , error, ftsGUID } }
       :param ftsGUID: If specified, only update the rows where the ftsGUID matches this value.
                       This avoids two jobs handling the same file one after another to step on each other foot.
                       Note that for the moment it is an optional parameter, but it may turn mandatory soon.

    """

    # This here is inneficient as we update every files, even if it did not change, and we commit every time.
    # It would probably be best to update only the files that changed.
    # However, commiting every time is the recommendation of MySQL
    # (https://dev.mysql.com/doc/refman/5.7/en/innodb-deadlocks-handling.html)

    for fileID, valueDict in fileStatusDict.iteritems():

      session = self.dbSession()
      try:

        updateDict = {FTS3File.status: valueDict['status']}

        # We only update error if it is specified
        if 'error' in valueDict:
          newError = valueDict['error']
          # Replace empty string with None
          if not newError:
            newError = None
          updateDict[FTS3File.error] = newError

        # We only update ftsGUID if it is specified
        if 'ftsGUID' in valueDict:
          newFtsGUID = valueDict['ftsGUID']
          # Replace empty string with None
          if not newFtsGUID:
            newFtsGUID = None
          updateDict[FTS3File.ftsGUID] = newFtsGUID

        # We only update the lines matching:
        # * the good fileID
        # * the status is not Final

        whereConditions = [FTS3File.fileID == fileID,
                           ~ FTS3File.status.in_(FTS3File.FINAL_STATES)]

        # If an ftsGUID is specified, add it to the `where` condition
        if ftsGUID:
          whereConditions.append(FTS3File.ftsGUID == ftsGUID)

        updateQuery = update(FTS3File)\
            .where(and_(*whereConditions)
                   )\
            .values(updateDict)

        session.execute(updateQuery)

        session.commit()

      except SQLAlchemyError as e:
        session.rollback()
        self.log.exception("updateFileFtsStatus: unexpected exception", lException=e)
        return S_ERROR("updateFileFtsStatus: unexpected exception %s" % e)
      finally:
        session.close()

    return S_OK()
示例#56
0
    def getRequest(self, reqID=0, assigned=True):
        """ read request for execution

    :param reqID: request's ID (default 0) If 0, take a pseudo random one

    """

        # expire_on_commit is set to False so that we can still use the object after we close the session
        session = self.DBSession(expire_on_commit=False)
        log = self.log.getSubLogger(
            'getRequest' if assigned else 'peekRequest')

        requestID = None

        try:

            if reqID:
                requestID = reqID

                log.verbose("selecting request '%s'%s" %
                            (reqID, ' (Assigned)' if assigned else ''))
                status = None
                try:
                    status = session.query(Request._Status)\
                                    .filter(Request.RequestID == reqID)\
                                    .one()
                except NoResultFound as e:
                    return S_ERROR("getRequest: request '%s' not exists" %
                                   reqID)

                if status and status == "Assigned" and assigned:
                    return S_ERROR(
                        "getRequest: status of request '%s' is 'Assigned', request cannot be selected"
                        % reqID)

            else:
                now = datetime.datetime.utcnow().replace(microsecond=0)
                reqIDs = set()
                try:
                    reqAscIDs = session.query(Request.RequestID)\
                                       .filter(Request._Status == 'Waiting')\
                                       .filter(Request._NotBefore < now)\
                                       .order_by(Request._LastUpdate)\
                                       .limit(100)\
                                       .all()

                    reqIDs = set([reqID[0] for reqID in reqAscIDs])

                    reqDescIDs = session.query(Request.RequestID)\
                                        .filter(Request._Status == 'Waiting')\
                                        .filter(Request._NotBefore < now)\
                                        .order_by(Request._LastUpdate.desc())\
                                        .limit(50)\
                                        .all()

                    reqIDs |= set([reqID[0] for reqID in reqDescIDs])
                # No Waiting requests
                except NoResultFound as e:
                    return S_OK()

                if not reqIDs:
                    return S_OK()

                reqIDs = list(reqIDs)
                random.shuffle(reqIDs)
                requestID = reqIDs[0]

            # If we are here, the request MUST exist, so no try catch
            # the joinedload is to force the non-lazy loading of all the attributes, especially _parent
            request = session.query(Request) \
                             .options(joinedload('__operations__').joinedload('__files__')) \
                             .filter(Request.RequestID == requestID)\
                             .one()

            if not reqID:
                log.verbose("selected request %s('%s')%s" %
                            (request.RequestID, request.RequestName,
                             ' (Assigned)' if assigned else ''))

            if assigned:
                session.execute(
                    update(Request).where(
                        Request.RequestID == requestID).values({
                            Request._Status:
                            'Assigned',
                            Request._LastUpdate:
                            datetime.datetime.utcnow().strftime(
                                Request._datetimeFormat)
                        }))
                session.commit()

            session.expunge_all()
            return S_OK(request)

        except Exception as e:
            session.rollback()
            log.exception("getRequest: unexpected exception", lException=e)
            return S_ERROR("getRequest: unexpected exception : %s" % e)
        finally:
            session.close()
示例#57
0
    def _create_or_update_package(self, package_dict, harvest_object,
                                  package_dict_form='rest'):
        '''
        Creates a new package or updates an existing one according to the
        package dictionary provided.

        The package dictionary can be in one of two forms:

        1. 'rest' - as seen on the RESTful API:

                http://datahub.io/api/rest/dataset/1996_population_census_data_canada

           This is the legacy form. It is the default to provide backward
           compatibility.

           * 'extras' is a dict e.g. {'theme': 'health', 'sub-theme': 'cancer'}
           * 'tags' is a list of strings e.g. ['large-river', 'flood']

        2. 'package_show' form, as provided by the Action API (CKAN v2.0+):

               http://datahub.io/api/action/package_show?id=1996_population_census_data_canada

           * 'extras' is a list of dicts
                e.g. [{'key': 'theme', 'value': 'health'},
                        {'key': 'sub-theme', 'value': 'cancer'}]
           * 'tags' is a list of dicts
                e.g. [{'name': 'large-river'}, {'name': 'flood'}]

        Note that the package_dict must contain an id, which will be used to
        check if the package needs to be created or updated (use the remote
        dataset id).

        If the remote server provides the modification date of the remote
        package, add it to package_dict['metadata_modified'].

        :returns: The same as what import_stage should return. i.e. True if the
                  create or update occurred ok, 'unchanged' if it didn't need
                  updating or False if there were errors.


        TODO: Not sure it is worth keeping this function. If useful it should
        use the output of package_show logic function (maybe keeping support
        for rest api based dicts
        '''
        assert package_dict_form in ('rest', 'package_show')
        try:
            # Change default schema
            # schema = default_create_package_schema()
            package_plugin = lib_plugins.lookup_package_plugin(None)
            schema = package_plugin.create_package_schema()
            schema['id'] = [ignore_missing, unicode]
            schema['__junk'] = [ignore]

            # Check API version
            if self.config:
                try:
                    api_version = int(self.config.get('api_version', 2))
                except ValueError:
                    raise ValueError('api_version must be an integer')
            else:
                api_version = 2

            user_name = self._get_user_name()
            context = {
                'model': model,
                'session': Session,
                'user': user_name,
                'api_version': api_version,
                'schema': schema,
                'ignore_auth': True,
            }

            if self.config and self.config.get('clean_tags', False):
                tags = package_dict.get('tags', [])
                package_dict['tags'] = self._clean_tags(tags)

            # Check if package exists
            try:
                # _find_existing_package can be overridden if necessary
                existing_package_dict = self._find_existing_package(package_dict)

                # In case name has been modified when first importing. See issue #101.
                package_dict['name'] = existing_package_dict['name']

                # Check modified date
                if not 'metadata_modified' in package_dict or \
                   package_dict['metadata_modified'] > existing_package_dict.get('metadata_modified'):
                    log.info('Package with GUID %s exists and needs to be updated' % harvest_object.guid)
                    # Update package
                    context.update({'id':package_dict['id']})
                    package_dict.setdefault('name',
                                            existing_package_dict['name'])

                    new_package = p.toolkit.get_action(
                        'package_update' if package_dict_form == 'package_show'
                        else 'package_update_rest')(context, package_dict)

                else:
                    log.info('No changes to package with GUID %s, skipping...' % harvest_object.guid)
                    # NB harvest_object.current/package_id are not set
                    return 'unchanged'

                # Flag the other objects linking to this package as not current anymore
                from ckanext.harvest.model import harvest_object_table
                conn = Session.connection()
                u = update(harvest_object_table) \
                        .where(harvest_object_table.c.package_id==bindparam('b_package_id')) \
                        .values(current=False)
                conn.execute(u, b_package_id=new_package['id'])

                # Flag this as the current harvest object

                harvest_object.package_id = new_package['id']
                harvest_object.current = True
                harvest_object.save()

            except p.toolkit.ObjectNotFound:
                # Package needs to be created

                # Get rid of auth audit on the context otherwise we'll get an
                # exception
                context.pop('__auth_audit', None)

                # Set name for new package to prevent name conflict, see issue #117
                if package_dict.get('name', None):
                    package_dict['name'] = self._gen_new_name(package_dict['name'])
                else:
                    package_dict['name'] = self._gen_new_name(package_dict['title'])

                log.info('Package with GUID %s does not exist, let\'s create it' % harvest_object.guid)
                harvest_object.current = True
                harvest_object.package_id = package_dict['id']
                # Defer constraints and flush so the dataset can be indexed with
                # the harvest object id (on the after_show hook from the harvester
                # plugin)
                harvest_object.add()

                model.Session.execute('SET CONSTRAINTS harvest_object_package_id_fkey DEFERRED')
                model.Session.flush()

                new_package = p.toolkit.get_action(
                    'package_create' if package_dict_form == 'package_show'
                    else 'package_create_rest')(context, package_dict)

            Session.commit()

            return True

        except p.toolkit.ValidationError, e:
            log.exception(e)
            self._save_object_error('Invalid package with GUID %s: %r'%(harvest_object.guid,e.error_dict),harvest_object,'Import')
示例#58
0
    def boekAfroep(idx):
        mlijstnr = idx.data()
        if idx.column() == 0:
            metadata = MetaData()
            raaplijst = Table('raaplijst', metadata,
                              Column('lijstID', Integer, primary_key=True),
                              Column('artikelID', Integer),
                              Column('werkorder', Integer),
                              Column('afroep', Float),
                              Column('leverdatum', String),
                              Column('geleverd', Float),
                              Column('meerwerk', Boolean),
                              Column('postcode', String),
                              Column('huisnummer', Integer),
                              Column('toevoeging', String),
                              Column('alternatief', String),
                              Column('boekdatum', String),
                              Column('straat', String),
                              Column('woonplaats', String))

            engine = create_engine(
                'postgresql+psycopg2://postgres@localhost/bisystem')
            con = engine.connect()

            selrlr = select([raaplijst]).where(raaplijst.c.lijstID == mlijstnr)
            rprlr = con.execute(selrlr).first()

            mwerknr = rprlr[2]
            martnr = rprlr[1]
            mleverdat = rprlr[4]
            mmmstatus = rprlr[6]
            mafroep = rprlr[3]
            mgeleverd = rprlr[5]

            class Widget(QDialog):
                def __init__(self, parent=None):
                    super(Widget, self).__init__(parent)
                    self.setWindowTitle("Materiaaluitgifte muteren")
                    self.setWindowIcon(QIcon('./images/logos/logo.jpg'))

                    self.setFont(QFont('Arial', 10))

                    self.Werknummer = QLabel()
                    zkwerknEdit = QLineEdit(str(mwerknr))
                    zkwerknEdit.setFont(QFont("Arial", 10))
                    zkwerknEdit.setDisabled(True)
                    zkwerknEdit.setStyleSheet("color:black")
                    zkwerknEdit.setAlignment(Qt.AlignRight)
                    zkwerknEdit.textChanged.connect(self.zkwerknChanged)
                    reg_ex = QRegExp("^[8]{1}[0-9]{8}$")
                    input_validator = QRegExpValidator(reg_ex, zkwerknEdit)
                    zkwerknEdit.setValidator(input_validator)

                    self.Artikelnummer = QLabel()
                    artEdit = QLineEdit(str(martnr))
                    artEdit.setFixedWidth(150)
                    artEdit.setFont(QFont("Arial", 10))
                    artEdit.setDisabled(True)
                    artEdit.setStyleSheet("color:black")
                    artEdit.setAlignment(Qt.AlignRight)
                    artEdit.textChanged.connect(self.artChanged)
                    reg_ex = QRegExp("^[2]{1}[0-9]{8}$")
                    input_validator = QRegExpValidator(reg_ex, artEdit)
                    artEdit.setValidator(input_validator)

                    self.Hoeveelheid = QLabel()
                    hoevEdit = QLineEdit()
                    hoevEdit.setFixedWidth(150)
                    hoevEdit.setFont(QFont("Arial", 10))
                    hoevEdit.textChanged.connect(self.hoevChanged)
                    reg_ex = QRegExp("^[-+]?[0-9]*\.?[0-9]+$")
                    input_validator = QRegExpValidator(reg_ex, hoevEdit)
                    hoevEdit.setValidator(input_validator)

                    grid = QGridLayout()
                    grid.setSpacing(20)

                    lbl = QLabel()
                    pixmap = QPixmap('./images/logos/verbinding.jpg')
                    lbl.setPixmap(pixmap)
                    grid.addWidget(lbl, 0, 0)

                    logo = QLabel()
                    pixmap = QPixmap('./images/logos/logo.jpg')
                    logo.setPixmap(pixmap)
                    grid.addWidget(logo, 0, 2, 1, 1, Qt.AlignRight)

                    lbl1 = QLabel('Werknummer')
                    lbl1.setAlignment(Qt.AlignRight | Qt.AlignVCenter)
                    grid.addWidget(lbl1, 1, 0)
                    grid.addWidget(zkwerknEdit, 1, 1)

                    lbl2 = QLabel('Artikelnummer')
                    lbl2.setAlignment(Qt.AlignRight | Qt.AlignVCenter)
                    grid.addWidget(lbl2, 2, 0)
                    grid.addWidget(artEdit, 2, 1)

                    lbl4 = QLabel('Afroep: ' + str(mafroep) +
                                  ' - Reeds geleverd: ' + str(mgeleverd))
                    grid.addWidget(lbl4, 3, 0, 1, 3, Qt.AlignCenter)
                    lbl3 = QLabel('Uitgifte')
                    lbl3.setAlignment(Qt.AlignRight | Qt.AlignVCenter)
                    grid.addWidget(lbl3, 4, 0)
                    grid.addWidget(hoevEdit, 4, 1)

                    if mmmstatus:
                        grid.addWidget(QLabel('Meerwerk'), 4, 2)

                    grid.addWidget(QLabel('Leverdatum'), 5, 0, Qt.AlignRight)
                    grid.addWidget(QLabel(mleverdat), 5, 1)

                    self.setLayout(grid)
                    self.setGeometry(500, 300, 150, 150)

                    applyBtn = QPushButton('Muteren')
                    applyBtn.clicked.connect(self.accept)

                    grid.addWidget(applyBtn, 6, 2, 1, 1, Qt.AlignRight)
                    applyBtn.setFont(QFont("Arial", 10))
                    applyBtn.setFixedWidth(100)
                    applyBtn.setStyleSheet(
                        "color: black;  background-color: gainsboro")

                    sluitBtn = QPushButton('Sluiten')
                    sluitBtn.clicked.connect(self.close)

                    grid.addWidget(sluitBtn, 6, 0, 1, 2, Qt.AlignRight)
                    sluitBtn.setFont(QFont("Arial", 10))
                    sluitBtn.setFixedWidth(100)
                    sluitBtn.setStyleSheet(
                        "color: black;  background-color: gainsboro")

                    grid.addWidget(
                        QLabel(
                            '\u00A9 2017 all rights reserved [email protected]'
                        ), 7, 0, 1, 3, Qt.AlignCenter)

                def zkwerknChanged(self, text):
                    self.Werknummer.setText(text)

                def artChanged(self, text):
                    self.Artikelnummer.setText(text)

                def hoevChanged(self, text):
                    self.Hoeveelheid.setText(text)

                def returnzkwerkn(self):
                    return self.Werknummer.text()

                def returnart(self):
                    return self.Artikelnummer.text()

                def returnhoev(self):
                    return self.Hoeveelheid.text()

                @staticmethod
                def getData(parent=None):
                    dialog = Widget(parent)
                    dialog.exec_()
                    return [
                        dialog.returnzkwerkn(),
                        dialog.returnart(),
                        dialog.returnhoev()
                    ]

            window = Widget()
            data = window.getData()
            mhoev = 0
            if data[0] and len(data[0]) == 9 and _11check(data[0]):
                mwerknr = int(data[0])
            elif not data[0] and len(str(mwerknr)) == 9 and _11check(mwerknr):
                mwerknr = int(mwerknr)
            else:
                foutWerknr()
                return ('')

            if str(mwerknr)[0] == '7':
                metadata = MetaData()
                orders_intern = Table(
                    'orders_intern', metadata,
                    Column('werkorderID', Integer(), primary_key=True),
                    Column('voortgangstatus', String))
                engine = create_engine(
                    'postgresql+psycopg2://postgres@localhost/bisystem')
                con = engine.connect()
                selwerk = select([
                    orders_intern
                ]).where(orders_intern.c.werkorderID == mwerknr)
                rpwerk = con.execute(selwerk).first()
                metadata = MetaData()
                orders_intern = Table(
                    'orders_intern', metadata,
                    Column('werkorderID', Integer(), primary_key=True),
                    Column('werk_materialen', Float),
                    Column('meerminderwerk', Float))
                artikelmutaties = Table(
                    'artikelmutaties', metadata,
                    Column('mutatieID', Integer, primary_key=True),
                    Column('artikelID', None,
                           ForeignKey('artikelen.artikelID')),
                    Column('werkorderID', None,
                           ForeignKey('orders_intern.werkorderID')),
                    Column('orderinkoopID', None,
                           ForeignKey('orders_inkoop.orderinkoopID')),
                    Column('hoeveelheid', Float), Column('boekdatum', String),
                    Column('tot_mag_prijs', Float), Column('btw_hoog', Float),
                    Column('mmstatus', Boolean))
            elif str(mwerknr)[0] == '8':
                metadata = MetaData()
                werken = Table(
                    'werken', metadata,
                    Column('werknummerID', Integer, primary_key=True),
                    Column('voortgangstatus', String),
                    Column('kosten_materialen', Float),
                    Column('meerminderwerk', Float))
                artikelmutaties = Table(
                    'artikelmutaties', metadata,
                    Column('mutatieID', Integer, primary_key=True),
                    Column('artikelID', None,
                           ForeignKey('artikelen.artikelID')),
                    Column('werknummerID', None,
                           ForeignKey('werken.werknoID')),
                    Column('orderinkoopID', None,
                           ForeignKey('orders_inkoop.orderinkoopID')),
                    Column('hoeveelheid', Float), Column('boekdatum', String),
                    Column('tot_mag_prijs', Float), Column('btw_hoog', Float),
                    Column('mmstatus', Boolean))

                engine = create_engine(
                    'postgresql+psycopg2://postgres@localhost/bisystem')
                con = engine.connect()
                selwerk = select([werken
                                  ]).where(werken.c.werknummerID == mwerknr)
                rpwerk = con.execute(selwerk).first()

            if rpwerk[1] == 'H':
                werkGereed()
                return (mwerknr)
            if data[2]:
                mhoev = float(data[2])
            else:
                return (mwerknr)

            metadata = MetaData()
            artikelen = Table(
                'artikelen', metadata,
                Column('artikelID', Integer(), primary_key=True),
                Column('artikelomschrijving', String),
                Column('artikelprijs', Float),
                Column('art_voorraad', Float,
                       CheckConstraint('art_voorraad >= 0')),
                Column('art_min_voorraad', Float),
                Column('art_bestelgrootte', Float),
                Column('bestelstatus',
                       Boolean), Column('mutatiedatum', String),
                Column('reserveringsaldo', Float),
                Column('jaarverbruik_1', Float),
                Column('jaarverbruik_2', Float))

            engine = create_engine(
                'postgresql+psycopg2://postgres@localhost/bisystem')
            con = engine.connect()
            sel = select([artikelen]).where(artikelen.c.artikelID == martnr)
            transaction = con.begin()
            result = con.execute(sel).first()
            martprijs = result[2]
            martvrd = result[3]
            martminvrd = result[4]
            mboekd = str(datetime.datetime.now())[0:10]
            mjaar = int(str(datetime.datetime.now())[0:4])

            if martvrd - mhoev <= martminvrd:
                martbestst = False
            else:
                martbestst = True
            if mhoev <= 0:
                foutHoev()
                return ('')
            try:
                if mjaar % 2 == 0:  #even jaartal
                    stmt = update(artikelen).where(artikelen.c.artikelID == martnr).values(\
                     art_voorraad = artikelen.c.art_voorraad - mhoev, bestelstatus = martbestst,\
                     mutatiedatum = mboekd, reserveringsaldo = artikelen.c.reserveringsaldo - mhoev,\
                     jaarverbruik_1 = artikelen.c.jaarverbruik_1 + mhoev)
                    con.execute(stmt)
                    mwaarde = martprijs * 1.1 * mhoev
                    con = engine.connect()
                elif mjaar % 2 == 1:  #oneven jaartal
                    stmt = update(artikelen).where(artikelen.c.artikelID == martnr).values(\
                     art_voorraad = artikelen.c.art_voorraad - mhoev, bestelstatus = martbestst,\
                     mutatiedatum = mboekd, reserveringsaldo = artikelen.c.reserveringsaldo - mhoev,\
                     jaarverbruik_2 = artikelen.c.jaarverbruik_2 + mhoev)
                    con.execute(stmt)
                    mwaarde = martprijs * 1.1 * mhoev
                    con = engine.connect()
                if mmmstatus:
                    mmeerminder = mwaarde
                else:
                    mmeerminder = 0

                if str(mwerknr)[0] == '8':
                    stmt = update(werken).where(werken.c.werknummerID == mwerknr).values(\
                     kosten_materialen = werken.c.kosten_materialen + mwaarde,\
                     meerminderwerk = werken.c.meerminderwerk + mmeerminder)
                    con.execute(stmt)
                    mutatienr=(con.execute(select([func.max(artikelmutaties.c.mutatieID,\
                    type_=Integer).label('mutatienr')])).scalar())
                    mutatienr += 1
                    ins = insert(artikelmutaties).values(mutatieID = mutatienr, artikelID =\
                        martnr, werknummerID = mwerknr, hoeveelheid = -mhoev, boekdatum = mboekd,\
                        tot_mag_prijs = mhoev*martprijs, btw_hoog = .21*mhoev*martprijs,\
                        mmstatus = mmmstatus)
                    con.execute(ins)
                elif str(mwerknr)[0] == '7':
                    stmt = update(orders_intern).where(orders_intern.c.werkorderID == mwerknr).values(\
                     werk_materialen = orders_intern.c.werk_materialen + mwaarde,\
                     meerminderwerk = orders_intern.c.meerminderwerk + mmeerminder)
                    con.execute(stmt)
                    mutatienr=(con.execute(select([func.max(artikelmutaties.c.mutatieID,\
                        type_=Integer).label('mutatienr')])).scalar())
                    mutatienr += 1
                    ins = insert(artikelmutaties).values(mutatieID = mutatienr, artikelID =\
                        martnr, werkorderID = mwerknr, hoeveelheid = -mhoev, boekdatum = mboekd,\
                        tot_mag_prijs = mhoev*martprijs, btw_hoog = .21*mhoev*martprijs,\
                        mmstatus = mmmstatus)
                    con.execute(ins)
                updrl = update(raaplijst).where(raaplijst.c.lijstID == mlijstnr).\
                values(geleverd = raaplijst.c.geleverd+mhoev, boekdatum = mboekd )
                con.execute(updrl)
                transaction.commit()
                invoerOK()
            except IntegrityError:
                transaction.rollback()
                negVoorraad()
            con.close
            return (mwerknr)
示例#59
0
    def getBulkRequests(self, numberOfRequest=10, assigned=True):
        """ read as many requests as requested for execution

    :param int numberOfRequest: Number of Request we want (default 10)
    :param bool assigned: if True, the status of the selected requests are set to assign

    :returns: a dictionary of Request objects indexed on the RequestID

    """

        # expire_on_commit is set to False so that we can still use the object after we close the session
        session = self.DBSession(expire_on_commit=False)
        log = self.log.getSubLogger(
            'getBulkRequest' if assigned else 'peekBulkRequest')

        requestDict = {}

        try:
            # If we are here, the request MUST exist, so no try catch
            # the joinedload is to force the non-lazy loading of all the attributes, especially _parent
            try:
                now = datetime.datetime.utcnow().replace(microsecond=0)
                requestIDs = session.query(Request.RequestID)\
                    .with_for_update()\
                    .filter(Request._Status == 'Waiting')\
                    .filter(Request._NotBefore < now)\
                    .order_by(Request._LastUpdate)\
                    .limit(numberOfRequest)\
                    .all()

                requestIDs = [ridTuple[0] for ridTuple in requestIDs]
                log.debug("Got request ids %s" % requestIDs)

                requests = session.query(Request) \
                                  .options(joinedload('__operations__').joinedload('__files__')) \
                                  .filter(Request.RequestID.in_(requestIDs))\
                                  .all()
                log.debug("Got %s Request objects " % len(requests))
                requestDict = dict((req.RequestID, req) for req in requests)
            # No Waiting requests
            except NoResultFound as e:
                pass

            if assigned and requestDict:
                session.execute(
                    update(Request).where(
                        Request.RequestID.in_(requestDict.keys())).values({
                            Request._Status:
                            'Assigned',
                            Request._LastUpdate:
                            datetime.datetime.utcnow().strftime(
                                Request._datetimeFormat)
                        }))
            session.commit()

            session.expunge_all()

        except Exception as e:
            session.rollback()
            log.exception("unexpected exception", lException=e)
            return S_ERROR("getBulkRequest: unexpected exception : %s" % e)
        finally:
            session.close()

        return S_OK(requestDict)
示例#60
0
def wijzWerk(mwerknr, m_email):
    metadata = MetaData()
    werken = Table('werken', metadata,
        Column('werknummerID', Integer(), primary_key=True),
        Column('werkomschrijving', String(50)),
        Column('voortgangstatus', String(1)),
        Column('statusweek',  String(6)),
        Column('aanneemsom', Float),
        Column('begr_materialen', Float),
        Column('begr_materieel', Float),
        Column('begr_huisv', Float),
        Column('begr_leiding', Float),
        Column('begr_inhuur', Float),
        Column('begr_vervoer', Float),
        Column('begr_beton_bvl', Float),
        Column('begr_kabelwerk', Float),
        Column('begr_grondverzet', Float),
        Column('begr_overig', Float),
        Column('begr_constr_uren', Float),
        Column('begr_mont_uren', Float),
        Column('begr_retourlas_uren', Float),
        Column('begr_telecom_uren', Float),
        Column('begr_bfi_uren', Float),
        Column('begr_bvl_uren', Float),
        Column('begr_spoorleg_uren', Float),
        Column('begr_spoorlas_uren', Float),
        Column('begr_voeding_uren', Float),
        Column('begr_lonen', Float),
        Column('startweek', String),
        Column('betaald_bedrag', Float),
        Column('meerminderwerk', Float),
        Column('kosten_materialen', Float),
        Column('kosten_lonen', Float),
        Column('kosten_materieel', Float),
        Column('kosten_leiding', Float),
        Column('kosten_huisv', Float),
        Column('kosten_overig', Float),
        Column('kosten_vervoer', Float),
        Column('kosten_inhuur', Float),
        Column('beton_bvl', Float),
        Column('kabelwerk', Float),
        Column('grondverzet', Float),
        Column('opdracht_datum', String))
               
    engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem')
    conn = engine.connect()
    selwerk = select([werken]).where(werken.c.werknummerID == mwerknr)
    rpwerk = conn.execute(selwerk).first()
                                          
    class Widget(QDialog):
        def __init__(self, parent=None):
            super(Widget, self).__init__(parent)
            self.setWindowTitle("Wijzig begroting werk")
            self.setWindowIcon(QIcon('./images/logos/logo.jpg'))
                                  
            self.setFont(QFont('Arial', 10))
                                               
            self.Omschrijving = QLabel()
            q1Edit = QLineEdit(rpwerk[1])
            q1Edit.setFont(QFont("Arial",10))
            q1Edit.textChanged.connect(self.q1Changed) 
            reg_ex = QRegExp("^.{0,49}$")
            input_validator = QRegExpValidator(reg_ex, q1Edit)
            q1Edit.setValidator(input_validator)
                            
            self.Aanneemsom = QLabel()
            q2Edit = QLineEdit(str(rpwerk[4]))
            q2Edit.setFixedWidth(150)
            q2Edit.setFont(QFont("Arial",10))
            q2Edit.textChanged.connect(self.q2Changed) 
            reg_ex = QRegExp("^[0-9.]{0,12}$")
            input_validator = QRegExpValidator(reg_ex, q2Edit)
            q2Edit.setValidator(input_validator)
             
            self.Materialen = QLabel()
            q3Edit = QLineEdit(str(rpwerk[5]))
            q3Edit.setFixedWidth(150)
            q3Edit.setFont(QFont("Arial",10))
            q3Edit.textChanged.connect(self.q3Changed) 
            reg_ex = QRegExp("^[0-9.]{0,12}$")
            input_validator = QRegExpValidator(reg_ex, q3Edit)
            q3Edit.setValidator(input_validator)
            
            self.Materieel = QLabel()
            q4Edit = QLineEdit(str(rpwerk[6]))
            q4Edit.setFixedWidth(150)
            q4Edit.setFont(QFont("Arial",10))
            q4Edit.textChanged.connect(self.q4Changed) 
            reg_ex = QRegExp("^[0-9.]{0,12}$")
            input_validator = QRegExpValidator(reg_ex, q4Edit)
            q4Edit.setValidator(input_validator)
            
            self.Huisvesting = QLabel()
            q5Edit = QLineEdit(str(rpwerk[7]))
            q5Edit.setFixedWidth(150)
            q5Edit.setFont(QFont("Arial",10))
            q5Edit.textChanged.connect(self.q5Changed) 
            reg_ex = QRegExp("^[0-9.]{0,12}$")
            input_validator = QRegExpValidator(reg_ex, q5Edit)
            q5Edit.setValidator(input_validator)
            
            self.Leiding = QLabel()
            q6Edit = QLineEdit(str(rpwerk[8]))
            q6Edit.setFixedWidth(150)
            q6Edit.setFont(QFont("Arial",10))
            q6Edit.textChanged.connect(self.q6Changed) 
            reg_ex = QRegExp("^[0-9.]{0,12}$")
            input_validator = QRegExpValidator(reg_ex, q6Edit)
            q6Edit.setValidator(input_validator)
            
            self.Inhuur = QLabel()
            q7Edit = QLineEdit(str(rpwerk[9]))
            q7Edit.setFixedWidth(150)
            q7Edit.setFont(QFont("Arial",10))
            q7Edit.textChanged.connect(self.q7Changed) 
            reg_ex = QRegExp("^[0-9.]{0,12}$")
            input_validator = QRegExpValidator(reg_ex, q7Edit)
            q7Edit.setValidator(input_validator)
            
            self.Vervoer = QLabel()
            q8Edit = QLineEdit(str(rpwerk[10]))
            q8Edit.setFixedWidth(150)
            q8Edit.setFont(QFont("Arial",10))
            q8Edit.textChanged.connect(self.q8Changed) 
            reg_ex = QRegExp("^[0-9.]{0,12}$")
            input_validator = QRegExpValidator(reg_ex, q8Edit)
            q8Edit.setValidator(input_validator)
            
            self.Beton_Bvl = QLabel()
            q9Edit = QLineEdit(str(rpwerk[11]))
            q9Edit.setFixedWidth(150)
            q9Edit.setFont(QFont("Arial",10))
            q9Edit.textChanged.connect(self.q9Changed) 
            reg_ex = QRegExp("^[0-9.]{0,12}$")
            input_validator = QRegExpValidator(reg_ex, q9Edit)
            q9Edit.setValidator(input_validator)
            
            self.Kabelwerk = QLabel()
            q10Edit = QLineEdit(str(rpwerk[12]))
            q10Edit.setFixedWidth(150)
            q10Edit.setFont(QFont("Arial",10))
            q10Edit.textChanged.connect(self.q10Changed) 
            reg_ex = QRegExp("^[0-9.]{0,12}$")
            input_validator = QRegExpValidator(reg_ex, q10Edit)
            q10Edit.setValidator(input_validator)
            
            self.Grondverzet = QLabel()
            q11Edit = QLineEdit(str(rpwerk[13]))
            q11Edit.setFixedWidth(150)
            q11Edit.setFont(QFont("Arial",10))
            q11Edit.textChanged.connect(self.q11Changed) 
            reg_ex = QRegExp("^[0-9.]{0,12}$")
            input_validator = QRegExpValidator(reg_ex, q11Edit)
            q11Edit.setValidator(input_validator)
            
            self.Overig = QLabel()
            q12Edit = QLineEdit(str(rpwerk[14]))
            q12Edit.setFixedWidth(150)
            q12Edit.setFont(QFont("Arial",10))
            q12Edit.textChanged.connect(self.q12Changed) 
            reg_ex = QRegExp("^[0-9.]{0,12}$")
            input_validator = QRegExpValidator(reg_ex, q12Edit)
            q12Edit.setValidator(input_validator) 
                 
            self.Uren_Construktie = QLabel()
            q13Edit = QLineEdit(str(rpwerk[15]))
            q13Edit.setFixedWidth(150)
            q13Edit.setFont(QFont("Arial",10))
            q13Edit.setDisabled(True)
            q13Edit.textChanged.connect(self.q13Changed) 
            reg_ex = QRegExp("^[0-9.]{0,12}$")
            input_validator = QRegExpValidator(reg_ex, q13Edit)
            q13Edit.setValidator(input_validator)
            
            self.Uren_Montage = QLabel()
            q14Edit = QLineEdit(str(rpwerk[16]))
            q14Edit.setFixedWidth(150)
            q14Edit.setFont(QFont("Arial",10))
            q14Edit.setDisabled(True)
            q14Edit.textChanged.connect(self.q14Changed) 
            reg_ex = QRegExp("^[0-9.]{0,12}$")
            input_validator = QRegExpValidator(reg_ex, q14Edit)
            q14Edit.setValidator(input_validator)
               
            self.Uren_Retourlas = QLabel()
            q15Edit = QLineEdit(str(rpwerk[17]))
            q15Edit.setFixedWidth(150)
            q15Edit.setFont(QFont("Arial",10))
            q15Edit.setDisabled(True)
            q15Edit.textChanged.connect(self.q15Changed) 
            reg_ex = QRegExp("^[0-9.]{0,12}$")
            input_validator = QRegExpValidator(reg_ex, q15Edit)
            q15Edit.setValidator(input_validator)
            
            self.Uren_Telecom = QLabel()
            q16Edit = QLineEdit(str(rpwerk[18]))
            q16Edit.setFixedWidth(150)
            q16Edit.setFont(QFont("Arial",10))
            q16Edit.setDisabled(True)
            q16Edit.textChanged.connect(self.q16Changed) 
            reg_ex = QRegExp("^[0-9.]{0,12}$")
            input_validator = QRegExpValidator(reg_ex, q16Edit)
            q16Edit.setValidator(input_validator)
            
            self.Uren_Bfi = QLabel()
            q17Edit = QLineEdit(str(rpwerk[19]))
            q17Edit.setFixedWidth(150)
            q17Edit.setFont(QFont("Arial",10))
            q17Edit.setDisabled(True)
            q17Edit.textChanged.connect(self.q17Changed) 
            reg_ex = QRegExp("^[0-9.]{0,12}$")
            input_validator = QRegExpValidator(reg_ex, q17Edit)
            q17Edit.setValidator(input_validator)
               
            self.Uren_Bvl = QLabel()
            q18Edit = QLineEdit(str(rpwerk[20]))
            q18Edit.setFixedWidth(150)
            q18Edit.setFont(QFont("Arial",10))
            q18Edit.setDisabled(True)
            q18Edit.textChanged.connect(self.q17Changed) 
            reg_ex = QRegExp("^[0-9.]{0,12}$")
            input_validator = QRegExpValidator(reg_ex, q18Edit)
            q18Edit.setValidator(input_validator)
                    
            self.Uren_Spoorleg = QLabel()
            q19Edit = QLineEdit(str(rpwerk[21]))
            q19Edit.setFixedWidth(150)
            q19Edit.setFont(QFont("Arial",10))
            q19Edit.setDisabled(True)
            q19Edit.textChanged.connect(self.q19Changed) 
            reg_ex = QRegExp("^[0-9.]{0,12}$")
            input_validator = QRegExpValidator(reg_ex, q19Edit)
            q19Edit.setValidator(input_validator)
            
            self.Uren_Spoorlas = QLabel()
            q20Edit = QLineEdit(str(rpwerk[22]))
            q20Edit.setFixedWidth(150)
            q20Edit.setFont(QFont("Arial",10))
            q20Edit.setDisabled(True)
            q20Edit.textChanged.connect(self.q20Changed) 
            reg_ex = QRegExp("^[0-9.]{0,12}$")
            input_validator = QRegExpValidator(reg_ex, q20Edit)
            q20Edit.setValidator(input_validator)
            
            self.Uren_Voeding = QLabel()
            q21Edit = QLineEdit(str(rpwerk[23]))
            q21Edit.setFixedWidth(150)
            q21Edit.setFont(QFont("Arial",10))
            q21Edit.setDisabled(True)
            q21Edit.textChanged.connect(self.q21Changed) 
            reg_ex = QRegExp("^[0-9.]{0,12}$")
            input_validator = QRegExpValidator(reg_ex, q21Edit)
            q21Edit.setValidator(input_validator)
            
            self.Begroot_Lonen = QLabel()
            q22Edit = QLineEdit(str(round(rpwerk[24],2)))
            q22Edit.setFixedWidth(150)
            q22Edit.setFont(QFont("Arial",10))
            q22Edit.textChanged.connect(self.q22Changed) 
            reg_ex = QRegExp("^[0-9.]{0,12}$")
            input_validator = QRegExpValidator(reg_ex, q22Edit)
            q22Edit.setValidator(input_validator)
            
            self.StartWerk = QLabel()
            q23Edit = QLineEdit(str(rpwerk[25]))
            q23Edit.setFixedWidth(80)
            q23Edit.setFont(QFont("Arial",10))
            q23Edit.textChanged.connect(self.q23Changed) 
            reg_ex = QRegExp("^[2]{1}[0]{1}[0-9]{2}[0-5]{1}[0-9]{1}$")
            input_validator = QRegExpValidator(reg_ex, q23Edit)
            q23Edit.setValidator(input_validator)
            
            self.Betaald = QLabel()
            q24Edit = QLineEdit(str(round(rpwerk[26],2)))
            q24Edit.setFixedWidth(150)
            q24Edit.setFont(QFont("Arial",10))
            q24Edit.textChanged.connect(self.q24Changed) 
            reg_ex = QRegExp("^[0-9.]{0,12}$")
            input_validator = QRegExpValidator(reg_ex, q24Edit)
            q24Edit.setValidator(input_validator)
            
            grid = QGridLayout()
            grid.setSpacing(20)
            
            lbl1 = QLabel('Werknummer')  
            lbl1.setAlignment(Qt.AlignRight | Qt.AlignVCenter)
            grid.addWidget(lbl1, 1, 0)
            
            lbl2 = QLabel(str(mwerknr))
            grid.addWidget(lbl2, 1, 1)
                   
            lbl3 = QLabel('Omschrijving')  
            lbl3.setAlignment(Qt.AlignRight | Qt.AlignVCenter)
            grid.addWidget(lbl3, 2, 0)
            grid.addWidget(q1Edit, 2, 1, 1, 3) # RowSpan 1 ,ColumnSpan 3
                                                 
            lbl4 = QLabel('Aanneemsom')  
            lbl4.setAlignment(Qt.AlignRight | Qt.AlignVCenter)
            grid.addWidget(lbl4, 3, 0)
            grid.addWidget(q2Edit, 3, 1)
            
            lbl5 = QLabel('Materialen')  
            lbl5.setAlignment(Qt.AlignRight | Qt.AlignVCenter)
            grid.addWidget(lbl5, 4, 0)
            grid.addWidget(q3Edit, 4, 1)
            
            lbl6 = QLabel('Materieel')  
            lbl6.setAlignment(Qt.AlignRight | Qt.AlignVCenter)
            grid.addWidget(lbl6, 5, 0)
            grid.addWidget(q4Edit, 5, 1)
            
            lbl7 = QLabel('Huisvesting')  
            lbl7.setAlignment(Qt.AlignRight | Qt.AlignVCenter)
            grid.addWidget(lbl7, 6, 0)
            grid.addWidget(q5Edit, 6, 1)
            
            lbl8 = QLabel('Leiding')  
            lbl8.setAlignment(Qt.AlignRight | Qt.AlignVCenter)
            grid.addWidget(lbl8, 7, 0)
            grid.addWidget(q6Edit, 7, 1)
            
            lbl9 = QLabel('Inhuur')  
            lbl9.setAlignment(Qt.AlignRight | Qt.AlignVCenter)
            grid.addWidget(lbl9, 8, 0)
            grid.addWidget(q7Edit, 8, 1)
            
            lbl10 = QLabel('Vervoer')  
            lbl10.setAlignment(Qt.AlignRight | Qt.AlignVCenter)
            grid.addWidget(lbl10, 9, 0)
            grid.addWidget(q8Edit, 9, 1)
            
            lbl11 = QLabel('Beton_Bvl')  
            lbl11.setAlignment(Qt.AlignRight | Qt.AlignVCenter)
            grid.addWidget(lbl11, 10, 0)
            grid.addWidget(q9Edit, 10, 1)
                
            lbl12 = QLabel('Kabelwerk')  
            lbl12.setAlignment(Qt.AlignRight | Qt.AlignVCenter)
            grid.addWidget(lbl12, 11, 0)
            grid.addWidget(q10Edit, 11, 1)
            
            lbl13 = QLabel('Grondverzet')  
            lbl13.setAlignment(Qt.AlignRight | Qt.AlignVCenter)
            grid.addWidget(lbl13, 12, 0)
            grid.addWidget(q11Edit, 12, 1)
            
            lbl14 = QLabel('Overig')  
            lbl14.setAlignment(Qt.AlignRight | Qt.AlignVCenter)
            grid.addWidget(lbl14, 13, 0)
            grid.addWidget(q12Edit, 13, 1)
            
            lblwk = QLabel('Status-JaarWeek')
            lblwk.setAlignment(Qt.AlignRight | Qt.AlignVCenter)
            grid.addWidget(lblwk, 1,2)
            
            lblst = QLabel(rpwerk[2]+'  '+rpwerk[3])
            grid.addWidget(lblst,1,3)
               
            lbl15 = QLabel('Uren_Construktie')  
            lbl15.setAlignment(Qt.AlignRight | Qt.AlignVCenter)
            grid.addWidget(lbl15, 3, 2)
            grid.addWidget(q13Edit, 3, 3)
         
            lbl16 = QLabel('Uren_Montage')  
            lbl16.setAlignment(Qt.AlignRight | Qt.AlignVCenter)
            grid.addWidget(lbl16, 4, 2)
            grid.addWidget(q14Edit, 4, 3)
            
            lbl17 = QLabel('Uren_Retourlas')  
            lbl17.setAlignment(Qt.AlignRight | Qt.AlignVCenter)
            grid.addWidget(lbl17, 5, 2)
            grid.addWidget(q15Edit, 5, 3)
            
            lbl18 = QLabel('Uren_Telecom')  
            lbl18.setAlignment(Qt.AlignRight | Qt.AlignVCenter)
            grid.addWidget(lbl18, 6, 2)
            grid.addWidget(q16Edit, 6, 3)
            
            lbl19 = QLabel('Uren_Bfi')  
            lbl19.setAlignment(Qt.AlignRight | Qt.AlignVCenter)
            grid.addWidget(lbl19, 7, 2)
            grid.addWidget(q17Edit, 7, 3)
            
            lbl20 = QLabel('Uren_Bvl')  
            lbl20.setAlignment(Qt.AlignRight | Qt.AlignVCenter)
            grid.addWidget(lbl20, 8, 2)
            grid.addWidget(q18Edit, 8, 3)
              
            lbl21 = QLabel('Uren_Spoorleg')  
            lbl21.setAlignment(Qt.AlignRight | Qt.AlignVCenter)
            grid.addWidget(lbl21, 9, 2)
            grid.addWidget(q19Edit, 9, 3)
            
            lbl22 = QLabel('Uren_Spoorlas')  
            lbl22.setAlignment(Qt.AlignRight | Qt.AlignVCenter)
            grid.addWidget(lbl22, 10, 2)
            grid.addWidget(q20Edit, 10, 3)
            
            lbl23 = QLabel('Uren_Voeding')  
            lbl23.setAlignment(Qt.AlignRight | Qt.AlignVCenter)
            grid.addWidget(lbl23, 11, 2)
            grid.addWidget(q21Edit, 11, 3)
            
            lbl24 = QLabel('Begroot_Lonen')  
            lbl24.setAlignment(Qt.AlignRight | Qt.AlignVCenter)
            grid.addWidget(lbl24, 12, 2)
            grid.addWidget(q22Edit, 12, 3)
            
            lbl25 = QLabel('Start werk')  
            lbl25.setAlignment(Qt.AlignRight | Qt.AlignVCenter)
            grid.addWidget(lbl25, 13, 2)
            grid.addWidget(q23Edit, 13, 3)
  
            lbl26 = QLabel('Cumulatief betaald')  
            lbl26.setAlignment(Qt.AlignRight | Qt.AlignVCenter)
            grid.addWidget(lbl26, 14, 0)
            grid.addWidget(q24Edit, 14, 1)
            
            lbl27 = QLabel('Opdrachtdatum')
            grid.addWidget(lbl27, 15, 0, 1, 1, Qt.AlignRight)
            lbl28 =QLabel(rpwerk[39])
            grid.addWidget(lbl28, 15, 1)
            
            cBox1 = QCheckBox('Meerwerk akkoord')
            cBox1.stateChanged.connect(self.cBox1Changed)
            if rpwerk[2] != 'F':
                cBox1.setDisabled(True)
            grid.addWidget(cBox1, 14, 2)
            
            cBox = QCheckBox('Werk Afgemeld')
            cBox.stateChanged.connect(self.cBoxChanged)
            if rpwerk[2] != 'G':
                cBox.setDisabled(True)
            grid.addWidget(cBox, 14, 3)
           
            cBox2 = QCheckBox('Opdracht')
            cBox2.stateChanged.connect(self.cBox2Changed)
            if rpwerk[39]:
                cBox2.setDisabled(True)
            grid.addWidget(cBox2, 15, 2) 
                       
            lbl = QLabel()
            pixmap = QPixmap('./images/logos/verbinding.jpg')
            lbl.setPixmap(pixmap)
            grid.addWidget(lbl, 0, 0, 1, 2)
            
            logo = QLabel()
            pixmap = QPixmap('./images/logos/logo.jpg')
            logo.setPixmap(pixmap)
            grid.addWidget(logo , 0, 3, 1, 1, Qt.AlignRight)
                                            
            grid.addWidget(QLabel('       \u00A9 2017 all rights reserved [email protected]'), 17, 0, 1, 3, Qt.AlignRight)
              
            self.setLayout(grid)
            self.setGeometry(100, 100, 150, 150)
    
            applyBtn = QPushButton('Wijzigen')
            applyBtn.clicked.connect(self.accept)
    
            grid.addWidget(applyBtn, 16, 3, 1, 1, Qt.AlignRight)
            applyBtn.setFont(QFont("Arial",10))
            applyBtn.setFixedWidth(100)
            applyBtn.setStyleSheet("color: black;  background-color: gainsboro")
            
            cancelBtn = QPushButton('Sluiten')
            cancelBtn.clicked.connect(lambda: winSluit(self, m_email))
    
            grid.addWidget(cancelBtn, 16, 2, 1 , 1, Qt.AlignRight)
            cancelBtn.setFont(QFont("Arial",10))
            cancelBtn.setFixedWidth(100)
            cancelBtn.setStyleSheet("color: black;  background-color: gainsboro")
                                       
        def q1Changed(self,text):
            self.Omschrijving.setText(text)
    
        def q2Changed(self,text):
            self.Aanneemsom.setText(text)
    
        def q3Changed(self,text):
            self.Materialen.setText(text)
     
        def q4Changed(self,text):
            self.Materieel.setText(text)
            
        def q5Changed(self,text):
            self.Huisvesting.setText(text)
            
        def q6Changed(self,text):
            self.Leiding.setText(text)
            
        def q7Changed(self,text):
            self.Inhuur.setText(text)
            
        def q8Changed(self,text):
            self.Vervoer.setText(text)
        
        def q9Changed(self,text):
            self.Beton_Bvl.setText(text)
            
        def q10Changed(self,text):
            self.Kabelwerk.setText(text)
               
        def q11Changed(self,text):
            self.Grondverzet.setText(text)
            
        def q12Changed(self,text):
            self.Overig.setText(text)
            
        def q13Changed(self,text):
            self.Uren_Construktie.setText(text)
            
        def q14Changed(self,text):
            self.Uren_Montage.setText(text)
            
        def q15Changed(self,text):
            self.Uren_Retourlas.setText(text)
            
        def q16Changed(self,text):
            self.Uren_Telecom.setText(text)
            
        def q17Changed(self,text):
            self.Uren_Bfi.setText(text)
            
        def q18Changed(self,text):
            self.Uren_Bvl.setText(text)
            
        def q19Changed(self,text):
            self.Uren_Spoorleg.setText(text)
            
        def q20Changed(self,text):
            self.Uren_Spoorlas.setText(text)
            
        def q21Changed(self,text):
            self.Uren_Voeding.setText(text)
            
        def q22Changed(self,text):
            self.Begroot_Lonen.setText(text)
            
        def q23Changed(self,text):
            self.StartWerk.setText(text)
                
        def q24Changed(self,text):
            self.Betaald.setText(text)
            
        state = False  
        def cBoxChanged(self, state):
            if state == Qt.Checked:
                self.state = True

        state1 = False  
        def cBox1Changed(self, state1):
            if state1 == Qt.Checked:
                self.state1 = True

        state2 = False  
        def cBox2Changed(self, state2):
            if state2 == Qt.Checked:
                self.state2 = True

        def returnq1(self):
            return self.Omschrijving.text()
        
        def returnq2(self):
            return self.Aanneemsom.text()
        
        def returnq3(self):
            return self.Materialen.text()
        
        def returnq4(self):
            return self.Materieel.text()
    
        def returnq5(self):
            return self.Huisvesting.text()
    
        def returnq6(self):
            return self.Leiding.text()
        
        def returnq7(self):
            return self.Inhuur.text()
              
        def returnq8(self):
            return self.Vervoer.text()
        
        def returnq9(self):
            return self.Beton_Bvl.text()
        
        def returnq10(self):
            return self.Kabelwerk.text()
        
        def returnq11(self):
            return self.Grondverzet.text()
        
        def returnq12(self):
            return self.Overig.text()
        
        def returnq13(self):
            return self.Uren_Construktie.text()
        
        def returnq14(self):
            return self.Uren_Montage.text()
        
        def returnq15(self):
            return self.Uren_Retourlas.text()
        
        def returnq16(self):
            return self.Uren_Telecom.text()
        
        def returnq17(self):
            return self.Uren_Bfi.text()
        
        def returnq18(self):
            return self.Uren_Bvl.text()
        
        def returnq19(self):
            return self.Uren_Spoorleg.text()
        
        def returnq20(self):
            return self.Uren_Spoorlas.text()
        
        def returnq21(self):
            return self.Uren_Voeding.text()
        
        def returnq22(self):
            return self.Begroot_Lonen.text()
        
        def returnq23(self):
            return self.StartWerk.text()
      
        def returnq24(self):
            return self.Betaald.text()
        
        def returncBox(self):
            return self.state
        
        def returncBox1(self):
            return self.state1
        
        def returncBox2(self):
            return self.state2
        
        @staticmethod
        def getData(parent=None):
            dialog = Widget(parent)
            dialog.exec_()
            return [dialog.returnq1(), dialog.returnq2(), dialog.returnq3(),\
                    dialog.returnq4(), dialog.returnq5(), dialog.returnq6(),\
                    dialog.returnq7(), dialog.returnq8(), dialog.returnq9(),\
                    dialog.returnq10(), dialog.returnq11(), dialog.returnq12(),\
                    dialog.returnq13(),dialog.returnq14(), dialog.returnq15(),\
                    dialog.returnq16(), dialog.returnq17(), dialog.returnq18(),\
                    dialog.returnq19(), dialog.returnq20(), dialog.returnq21(),\
                    dialog.returnq22(), dialog.returnq23(), dialog.returnq24(),
                    dialog.returncBox(), dialog.returncBox1(), dialog.returncBox2()]  
                       
    window = Widget()
    data = window.getData()
    if data[0]:
        ms0 = str(data[0])
    else:
        ms0 = rpwerk[1]
    if data[1]:
        mf1 = float(data[1])
    else:
        mf1 = rpwerk[4]
    if data[2]:
        mf2 = float(data[2])
    else:
        mf2 = rpwerk[5]  
    if data[3]:
        mf3 = float(data[3])
    else:
        mf3 = rpwerk[6]
    if data[4]:
        mf4 = float(data[4])
    else:
        mf4 = rpwerk[7]
    if data[5]:
        mf5 = float(data[5])
    else:
        mf5 = rpwerk[8]
    if data[6]:
        mf6 = float(data[6])
    else:
        mf6 = rpwerk[9]
    if data[7]:
        mf7 = float(data[7])
    else:
        mf7 = rpwerk[10]
    if data[8]:
        mf8 = float(data[8])
    else:
        mf8 = rpwerk[11]
    if data[9]:
        mf9 = float(data[9])
    else:
        mf9 = rpwerk[12]
    if data[10]:
        mf10 = float(data[10])
    else:
        mf10 = rpwerk[13]
    if data[11]:
        mf11 = float(data[11])
    else:
        mf11 = rpwerk[14]
    if data[12]:
        mf12 = float(data[12])
    else:
        mf12 = rpwerk[15]
    if data[13]:
        mf13 = float(data[13])
    else:
        mf13 = rpwerk[16]
    if data[14]:
        mf14 = float(data[14])
    else:
        mf14 = rpwerk[17]
    if data[15]:
        mf15 = float(data[15])
    else:
        mf15 = rpwerk[18]
    if data[16]:
        mf16 = float(data[16])
    else:
        mf16 = rpwerk[19]
    if data[17]:
        mf17 = float(data[17])
    else:
        mf17 = rpwerk[20]
    if data[18]:
        mf18 = float(data[18])
    else:
        mf18 = rpwerk[21]
    if data[19]:
        mf19 = float(data[19])
    else:
        mf19 = rpwerk[22]
    if data[20]:
        mf20 = float(data[20])
    else:
        mf20 = rpwerk[23]
    if data[21]:
        mf21 = float(data[21])
    else:
        mf21 = rpwerk[24]
    if data[22]:
        mf22 = data[22]
    else:
        mf22 = rpwerk[25]
    if data[23]:
        mf23 = float(data[23])
    else:
        mf23 = rpwerk[26]
    if data[25]:
        mvgangst = 'G'
        mstatwk = jaarweek()
    elif data[24]:
        mvgangst = 'H'
        mstatwk =  jaarweek()
    else:
        mvgangst = rpwerk[2]
        mstatwk = rpwerk[3]
    if data[26]:
        mopdrdatum = str(datetime.datetime.now())[0:10]
    else:
        mopdrdatum = rpwerk[39]
                   
    engine = create_engine('postgresql+psycopg2://postgres@localhost/bisystem')
    conn = engine.connect()        
    uwrk = update(werken).where(werken.c.werknummerID == mwerknr).values(\
        werkomschrijving = ms0, aanneemsom = mf1, begr_materialen = mf2,\
        begr_materieel = mf3, begr_huisv = mf4, begr_leiding = mf5,\
        begr_inhuur = mf6, begr_vervoer = mf7, begr_beton_bvl = mf8,\
        begr_kabelwerk = mf9, begr_grondverzet = mf10, begr_overig = mf11,\
        begr_constr_uren = mf12, begr_mont_uren = mf13, begr_retourlas_uren = mf14,\
        begr_telecom_uren = mf15, begr_bfi_uren = mf16, begr_bvl_uren = mf17,\
        begr_spoorleg_uren = mf18, begr_spoorlas_uren = mf19, begr_voeding_uren = mf20,\
        begr_lonen = mf21, startweek = mf22, statusweek = mstatwk,\
        voortgangstatus = mvgangst, betaald_bedrag = mf23, opdracht_datum =\
        mopdrdatum)
    conn.execute(uwrk)
    conn.close()
    updateOK() 
    zoekWerk(m_email)