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