def data(self, dbconn):
orders = dbconn.execute('select * from (select porder.order_id,porder.username, porder.sername, porder.patronymic, porder.email, porder.address, porder.phones, porder.sale_type, porder.note, porder.delivery_id, porder.payment_id, porder.amount, history_prod.datetime, status_order.name as name, status_order.status_order_id from porder join history_prod using (order_id) join status_order using (status_order_id) order by status_order.name desc, order_id) as t group by name, datetime;')
delivery = dbconn.query(Delivery).all()
payment = dbconn.query(Payment).all()
ajax = []
for d in delivery:
ajax.append(E.delivery(
delivery_id = str(d.delivery_id),
name = d.name,
))
for p in payment:
ajax.append(E.payment(
payment_id = str(p.payment_id),
name = p.name,
))
for r in orders:
ajax.append(E.order(
order_id = str(r[0]),
name = r[1],
sename = r[2],
patronymic = r[3],
email = r[4],
address = r[5],
phones = r[6],
sale_type = str(r[7]),
note = r[8],
delivery_id = str(r[9]),
payment_id = str(r[10]),
amount = str(r[11]),
date = r[12].strftime('%Y-%m-%d'),
status = r[13]
))
return E.ajax(*ajax)
def E_data(self):
dbconn = self.dbconn
delivery = dbconn.query(Price_delivery)
data = E.data()
data_tag = data.xpath('//data')[0]
for p in delivery:
data_tag.append(
E.delivery(delivery_id=str(p.price_delivery_id),
name=p.name,
price=str(p.price)))
return data
def E_data(self):
dbconn = self.dbconn
delivery = dbconn.query(
Delivery,
Payment.name).filter(Delivery.payment_id == Payment.payment_id)
data = E.data()
data_tag = data.xpath('//data')[0]
for p in delivery:
data_tag.append(
E.delivery(payment_id=str(p[0].payment_id),
delivery_id=str(p[0].delivery_id),
name=p[0].name,
note=p[0].note,
payment_name=p[1]))
return data
def config(self, dbconn):
form = self.form
action = form['action']
order_id = form['order_id']
name = form['username']
sename = form['sername']
patronymic = form['patronymic']
email = form['email']
address = form['address']
phones = form['phones']
sale_type = form['sale_type']
note = form['note']
delivery_id = form['delivery_id']
payment_id = form['payment_id']
amount = form['amount']
order_id = order_id
if order_id == 0:
order = Order('', '','', '','','','all','', 0,0, 0)
dbconn.add(order)
dbconn.commit()
form['order_id'] = order.order_id
form.store_to_orm(order)
try:
dbconn.commit()
history = History(order.order_id, datetime.datetime.now(), '1')
dbconn.add(history)
dbconn.commit()
except:
dbconn.delete(order)
dbconn.commit()
else:
try:
order = dbconn.query(Order).filter(Order.order_id == order_id).one()
except sqlalchemy.orm.exc.NoResultFound:
sys.exc_clear()
return
form.store_to_orm(order)
dbconn.commit()
try:
dbconn.commit()
except:
dbconn.delete(order)
dbconn.commit()
orders = dbconn.query(Order, History.datetime).filter(sqlalchemy.and_(History.order_id == Order.order_id, History.status_order_id == 1)).all()
delivery = dbconn.query(Delivery).all()
payment = dbconn.query(Payment).all()
ajax = []
for d in delivery:
ajax.append(E.delivery(
delivery_id = str(d.delivery_id),
name = d.name,
))
for p in payment:
ajax.append(E.payment(
payment_id = str(p.payment_id),
name = p.name,
))
for r in orders:
ajax.append(E.order(
order_id = str(r[0].order_id),
name = r[0].username,
sename = r[0].sername,
patronymic = r[0].patronymic,
email = r[0].email,
address = r[0].address,
phones = r[0].phones,
sale_type = str(r[0].sale_type),
note = r[0].note,
delivery_id = str(r[0].delivery_id),
payment_id = str(r[0].payment_id),
amount = str(r[0].amount),
date = r[1].strftime('%Y-%m-%d')
))
return E.ajax(*ajax)
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))