예제 #1
0
 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) 
예제 #2
0
    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))