Subversion Repositories SmartDukaan

Rev

Rev 13172 | Rev 13365 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 13172 Rev 13176
Line 50... Line 50...
50 
 
 50 
 
51 
SOLD_OUT_GROUP_SQL="""
 51 
SOLD_OUT_GROUP_SQL="""
52 
                    select c.name, concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as prodname, count(*),  sum(o.total_amount) as total_sale
52 
                    select c.name, concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as prodname, count(*),  sum(o.total_amount) as total_sale
53 
                        from transaction.order o join user.privatedealuser pd on o.customer_id=pd.id
53 
                        from transaction.order o join user.privatedealuser pd on o.customer_id=pd.id
54 
                        join mobileactivationrecord mar on mar.orderId = o.id join user.counter c on c.id = pd.counter_id
54 
                        join mobileactivationrecord mar on mar.orderId = o.id join user.counter c on c.id = pd.counter_id
55 
                        join transaction.lineitem li on li.order_id=o.id  where date(mar.activationTime) = curdate()-1 group by c.name, prodname  with rollup;
 55 
                        join transaction.lineitem li on li.order_id=o.id  where date(mar.activationTime) = curdate()- interval 1 day group by c.name, prodname  with rollup;
56 
                   """
 56 
                   """
57 
SOLD_OUT_SQL="""
 57 
SOLD_OUT_SQL="""
58 
                          select c.code,c.name, o.id, date(o.created_timestamp) as orderedon, date(delivery_timestamp), mar.activationTime,
58 
                          select c.code,c.name, o.id, date(o.created_timestamp) as orderedon, date(delivery_timestamp), mar.activationTime,
59 
                          concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as prodname, li.quantity, o.total_amount as amount from transaction.order o
59 
                          concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as prodname, li.quantity, o.total_amount as amount from transaction.order o
60 
                          join user.privatedealuser pd on o.customer_id=pd.id  join user.counter c on c.id = pd.counter_id join transaction.lineitem li on li.order_id=o.id
60 
                          join user.privatedealuser pd on o.customer_id=pd.id  join user.counter c on c.id = pd.counter_id join transaction.lineitem li on li.order_id=o.id
61 
                          join mobileactivationrecord mar on mar.orderId = o.id where date(mar.activationTime) = curdate()-1 order by code, prodname
 61 
                          join mobileactivationrecord mar on mar.orderId = o.id where date(mar.activationTime) = curdate()- interval 1 day order by code, prodname
62 
             """
 62 
             """
63 
PENDING_ORDER_GROUP_SQL="""     select c.name, concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as prodname, count(*),sum(o.total_amount) as amount
63 
PENDING_ORDER_GROUP_SQL="""     select c.name, concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as prodname, count(*),sum(o.total_amount) as amount
64 
                                from transaction.order o join user.privatedealuser pd on o.customer_id=pd.id
64 
                                from transaction.order o join user.privatedealuser pd on o.customer_id=pd.id
65 
                                join user.counter c on c.id = pd.counter_id join transaction.lineitem li on li.order_id=o.id
65 
                                join user.counter c on c.id = pd.counter_id join transaction.lineitem li on li.order_id=o.id
66 
                                where o.status in (3,4,5,7) and o.total_amount > 1 group by c.name, prodname  with rollup
 66 
                                where o.status in (3,4,5,7) and o.total_amount > 1 group by c.name, prodname  with rollup
Line 89... Line 89...
89 
                        join user.counter c on c.id = pd.counter_id join transaction.lineitem li on li.order_id=o.id join orderstatus os on o.status = os.status
 89 
                        join user.counter c on c.id = pd.counter_id join transaction.lineitem li on li.order_id=o.id join orderstatus os on o.status = os.status
90 
                        where o.status=12 and o.total_amount > 0 and mar.activationTime is null and li.brand = 'Spice' and li.item_id not in (select itemId from catalog.spiceuntraceable)
 90 
                        where o.status=12 and o.total_amount > 0 and mar.activationTime is null and li.brand = 'Spice' and li.item_id not in (select itemId from catalog.spiceuntraceable)
91 
                        """
 91 
                        """
92 
 
 92 
 
93 
MIS_REPORT_GROUP_SQL="""
 93 
MIS_REPORT_GROUP_SQL="""
94 
                        select c.name, sum(if(date(o.created_timestamp)=curdate()-1, 1, 0)) as lastDayQty,
94 
                        select c.name, sum(if(date(o.created_timestamp)=curdate()-interval 1 day, 1, 0)) as lastDayQty,
95 
                            sum(if(date(o.created_timestamp)=curdate()-1, o.total_amount , 0)) lastDayValue,
95 
                            sum(if(date(o.created_timestamp)=curdate()- interval 1 day, o.total_amount , 0)) lastDayValue,
96 
                            count(*) as MTDQty, sum(o.total_amount) as MTDValue from transaction.order o
96 
                            count(*) as MTDQty, sum(o.total_amount) as MTDValue from transaction.order o
97 
                            join user.privatedealuser pd on o.customer_id=pd.id join user.counter c on c.id = pd.counter_id
97 
                            join user.privatedealuser pd on o.customer_id=pd.id join user.counter c on c.id = pd.counter_id
98 
                            join transaction.lineitem li on li.order_id=o.id        where o.status in (2,3,4,5,7,9,10,12,16,17,40)
98 
                            join transaction.lineitem li on li.order_id=o.id        where o.status in (2,3,4,5,7,9,10,12,16,17,40)
99 
                            and o.total_amount > 1 and o.created_timestamp between CURDATE() -  interval DAY(CURDATE()-INTERVAL 1 day)  day and CURDATE()
99 
                            and o.total_amount > 1 and o.created_timestamp between CURDATE() -  interval DAY(CURDATE()-INTERVAL 1 day)  day and CURDATE()
100 
                            group by c.name with rollup;
 100 
                            group by c.name with rollup;
101 
                     """
 101 
                     """
102 
MIS_REPORT_GROUP_PROD_SQL="""
 102 
MIS_REPORT_GROUP_PROD_SQL="""
103 
                        select concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as product, sum(if(date(o.created_timestamp)=curdate()-1, 1, 0)) as lastDayQty,
103 
                        select concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as product, sum(if(date(o.created_timestamp)=curdate()- interval 1 day, 1, 0)) as lastDayQty,
104 
                            sum(if(date(o.created_timestamp)=curdate()-1, o.total_amount , 0)) lastDayValue,
104 
                            sum(if(date(o.created_timestamp)=curdate()- interval 1 day, o.total_amount , 0)) lastDayValue,
105 
                            count(*) as MTDQty, sum(o.total_amount) as MTDValue from transaction.order o
105 
                            count(*) as MTDQty, sum(o.total_amount) as MTDValue from transaction.order o
106 
                            join user.privatedealuser pd on o.customer_id=pd.id join user.counter c on c.id = pd.counter_id
106 
                            join user.privatedealuser pd on o.customer_id=pd.id join user.counter c on c.id = pd.counter_id
107 
                            join transaction.lineitem li on li.order_id=o.id        where o.status in (2,3,4,5,7,9,10,12,16,17,40)
107 
                            join transaction.lineitem li on li.order_id=o.id        where o.status in (2,3,4,5,7,9,10,12,16,17,40)
108 
                            and o.total_amount > 1 and o.created_timestamp between CURDATE() -  interval DAY(CURDATE()-INTERVAL 1 day)  day and CURDATE()
108 
                            and o.total_amount > 1 and o.created_timestamp between CURDATE() -  interval DAY(CURDATE()-INTERVAL 1 day)  day and CURDATE()
109 
                            group by product with rollup;
 109 
                            group by product with rollup;