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)
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
}))
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)
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]
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()
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})
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')
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)
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)
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
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)
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()
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]
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
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))
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
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)
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)
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'
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
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
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)))
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()
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.')
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'
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)
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
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
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)
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()
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
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
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)
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')
# 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()
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)
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)
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()
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)
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"
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)
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)
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()
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)
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)
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)
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)
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)
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)
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 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)
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()
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()
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')
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)
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)
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)