Subversion Repositories SmartDukaan

Rev

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

Rev 13099 Rev 13172
Line 94... Line 94...
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()-1, 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()-1, 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())-1 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()-1, 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()-1, 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())-1 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;
110 
                     """
 110 
                     """
111 
MIS_REPORT_SQL="""
 111 
MIS_REPORT_SQL="""
112 
                select c.code,c.name, w.displayName as warehouse, o.id, date(o.created_timestamp) as orderedon, os.statusName , date(mar.activationTime) activatedon,
112 
                select c.code,c.name, w.displayName as warehouse, o.id, date(o.created_timestamp) as orderedon, os.statusName , date(mar.activationTime) activatedon,
113 
                concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as prodname, li.quantity, o.total_amount as amount
113 
                concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as prodname, li.quantity, o.total_amount as amount
114 
                from transaction.order o join user.privatedealuser pd on o.customer_id=pd.id  join user.counter c on c.id = pd.counter_id
114 
                from transaction.order o join user.privatedealuser pd on o.customer_id=pd.id  join user.counter c on c.id = pd.counter_id
115 
                join transaction.lineitem li on li.order_id=o.id join inventory.warehouse w on w.id = o.fulfilmentWarehouseId
115 
                join transaction.lineitem li on li.order_id=o.id join inventory.warehouse w on w.id = o.fulfilmentWarehouseId
116 
                join orderstatus os on o.status = os.status left join mobileactivationrecord mar on mar.orderId = o.id
116 
                join orderstatus os on o.status = os.status left join mobileactivationrecord mar on mar.orderId = o.id
117 
                where o.status in (2,3,4,5,7,9,10,12,16,17,40) and o.total_amount > 1
117 
                where o.status in (2,3,4,5,7,9,10,12,16,17,40) and o.total_amount > 1
118 
                and o.created_timestamp between CURDATE() - INTERVAL DAY(CURDATE())-1 DAY and CURDATE() order by orderedon desc, c.code
118 
                and o.created_timestamp between CURDATE() -  interval DAY(CURDATE()-INTERVAL 1 day)  day and CURDATE() order by orderedon desc, c.code
119 
                """
 119 
                """
120 
date_format = xlwt.XFStyle()
 120 
date_format = xlwt.XFStyle()
121 
date_format.num_format_str = 'dd/mm/yyyy'
 121 
date_format.num_format_str = 'dd/mm/yyyy'
122 
 
 122 
 
123 
datetime_format = xlwt.XFStyle()
 123 
datetime_format = xlwt.XFStyle()