def item_list(req, pager, **kwargs): # static method
client = kwargs.pop('client') if 'client' in kwargs else None
keys = list("%s %s %%s" % ('o.'+k, 'in' if islistable(v) else '=')
for k, v in kwargs.items())
cond = "WHERE " + ' AND '.join(keys) if keys else ''
if client: # client have special OR mod
cond = cond + ' AND ' if cond else "WHERE "
cond += "(o.email LIKE %s OR c.email LIKE %s)"
kwargs['o.email'] = "%{0}%".format(client)
kwargs['c.email'] = "%{0}%".format(client)
tran = req.db.transaction(req.log_info)
c = tran.cursor()
c.execute("""
SELECT o.order_id, o.client_id, c.email, o.email, o.create_date,
o.modify_date, o.state
FROM eshop_orders o LEFT JOIN logins c ON (o.client_id = c.login_id)
%s ORDER BY %s %s LIMIT %%s, %%s
""" % (cond, pager.order, pager.sort),
tuple(kwargs.values()) + (pager.offset, pager.limit))
items = []
for row in iter(c.fetchone, None):
item = Object()
item.id, item.client_id, item.client, item.email, item.create_date, \
item.modify_date, item.state = row
items.append(item)
# endfor
c.execute("""
SELECT count(*) FROM eshop_orders o
LEFT JOIN logins c ON (o.client_id = c.login_id) %s
""" % cond, kwargs.values())
pager.total = c.fetchone()[0]
tran.commit()
return items
