def E_data(self):
form = self.form
order_id = form['order_id']
where = []
if order_id:
where = 'porder.order_id=%s' % order_id
if where:
where_clause = 'where '+where
dbconn = self.dbconn
t = """
select sql_calc_found_rows * from( select * from (
select porder.order_id as ord_id,
status_order.name as status,
status_order.status_order_id as st_id,
porder.datetime as dt,
datetime_distr,
concat(username, ' ', sername, ' ', patronymic) as fio,
email,
address,
phones,
amount,
porder.note,
payment.name as pmt,
delivery.name as delivery,
porder.sale as sale
from porder join history_prod using(order_id) join status_order using(status_order_id) join delivery using(delivery_id) join payment on payment.payment_id=porder.payment_id
%s
order by porder.order_id, history_prod.datetime desc)
as k group by ord_id order by ord_id desc) as p;
""" % (where_clause,)
order_status = dbconn.execute(t)
orders = []
for ord_id , sts, st_id, dt, dt_distr, fio, email, address, phones, amount, note, pmt, delivery, sale in order_status:
prods = dbconn.query(Prod_order).filter(Prod_order.order_id==ord_id)
sale_str = u"нет"
for pr in prods:
if pr.sale:
sale_str = u"есть"
css = 'green'
orders.append(E.order(
E.ord_id(str(ord_id)),
E.status(sts),
E.stid(str(st_id)),
E.dt(dt.strftime('%Y-%m-%d')),
E.dt_distr(dt_distr.strftime('%Y-%m-%d %H:%M'), css=css),
E.fio(fio),
E.email(email),
E.address(address),
E.phones(phones),
E.amount('%10.2f' % (amount*(1-sale/100))),
E.note(note),
E.pmt(pmt),
E.delivery(delivery),
E.sale(sale_str),
E.prods(*(E.prod(name=p.product_name, count=str(p.count), amount=str(p.amount), close=p.close) for p in prods)),
#dbconn.query(Prod_order).filter(Prod_order.order_id==ord_id))),
E.comments(*(E.comment(c.comment, name=d.firstname) for c,d in dbconn.query(Comment, Distrib_oper).filter(Comment.order_id==ord_id).filter(Comment.oper_id==Distrib_oper.oper_id).order_by(Comment.comment_order_id))),
E.history(*(E.st(dt=h.datetime.strftime('%Y-%m-%d %H:%M:%S'), name=s.name, oper=op.firstname) for h,s, op in dbconn.query(History, Status_order, Distrib_oper).filter(
History.order_id==ord_id).filter(Status_order.status_order_id==History.status_order_id).filter(Distrib_oper.oper_id==History.oper_id).order_by(desc(History.datetime)) )),
))
return E.data(*orders)
def E_data(self):
form = self.form
limit=form['limit']
offset=form['offset']
date_b = form['date_b']
date_e = form['date_e']
date_distr = form['date_distr']
sess = self.req.environ['rrduet.sess']
oper_id = sess['oper_id']
# if date_distr == '' and oper_id==1:
# date_distr = datetime.datetime.now().strftime('%Y-%m-%d')
date_now = datetime.datetime.now().strftime('%Y-%m-%d')
status = form['status']
order_id = form['order_id']
where = []
if date_b !='':
where.append('porder.datetime>="%s"' % date_b)
if date_e != '':
where.append('porder.datetime<="%s"' % date_e)
if date_distr != '':
where.append('porder.datetime_distr>="%s 0:0:0" and porder.datetime_distr<="%s 23:59:59"' % (date_distr, date_distr))
if order_id:
where = ['porder.order_id=%s' % order_id]
if where:
where_clause = 'where '+' and '.join(where)
if where:
where_clause = 'where '+' and '.join(where)
else:
where_clause = ''
if status:
st_where = 'where st_id=%s' % (status,)
else:
st_where = ''
dbconn = self.dbconn
t = """
select sql_calc_found_rows * from( select * from (
select porder.order_id as ord_id,
status_order.name as status,
status_order.status_order_id as st_id,
porder.datetime as dt,
datetime_distr,
concat(username, ' ', sername, ' ', patronymic) as fio,
email,
address,
phones,
amount,
porder.note,
payment.name as pmt,
delivery.name as delivery,
porder.sale as sale
from porder join history_prod using(order_id) join status_order using(status_order_id) join delivery using(delivery_id) join payment on payment.payment_id=porder.payment_id
%s
order by porder.order_id, history_prod.datetime desc)
as k group by ord_id order by ord_id desc) as p %s limit %s offset %s;
""" % (where_clause, st_where, limit, offset,)
order_status = dbconn.execute(t)
count = list(dbconn.execute("select found_rows()"))[0][0]
next = prev = ''
if count>offset+limit:
next = str(offset+limit)
if offset > 0:
prev = str(offset-limit)
orders= []
for ord_id , sts, st_id, dt, dt_distr, fio, email, address, phones, amount, note, pmt, delivery, sale in order_status:
prods = dbconn.query(Prod_order).filter(Prod_order.order_id==ord_id)
sale_str = u"нет"
for pr in prods:
if pr.sale:
sale_str = u"есть"
css = 'green'
if date_now == dt_distr.strftime('%Y-%m-%d'):
css = 'red'
orders.append(E.order(
E.ord_id(str(ord_id)),
E.status(sts),
E.stid(str(st_id)),
E.dt(dt.strftime('%Y-%m-%d')),
E.dt_distr(dt_distr.strftime('%Y-%m-%d %H:%M'), css=css),
E.fio(fio),
E.email(email),
E.address(address),
E.phones(phones),
E.amount('%10.2f' % (amount*(1-sale/100))),
E.note(note),
E.pmt(pmt),
E.delivery(delivery),
E.sale(sale_str),
E.prods(*(E.prod(name=p.product_name, count=str(p.count), amount=str(p.amount), close=p.close) for p in prods)),
#dbconn.query(Prod_order).filter(Prod_order.order_id==ord_id))),
E.comments(*(E.comment(c.comment, name=d.firstname) for c,d in dbconn.query(Comment, Distrib_oper).filter(Comment.order_id==ord_id).filter(Comment.oper_id==Distrib_oper.oper_id).order_by(Comment.comment_order_id))),
E.history(*(E.st(dt=h.datetime.strftime('%Y-%m-%d %H:%M:%S'), name=s.name, oper=op.firstname) for h,s, op in dbconn.query(History, Status_order, Distrib_oper).filter(
History.order_id==ord_id).filter(Status_order.status_order_id==History.status_order_id).filter(Distrib_oper.oper_id==History.oper_id).order_by(desc(History.datetime)) )),
))
return E.data(*orders, offset=str(offset), next=next, prev=prev,date_b=date_b, date_e=str(date_e), status=str(status))