WebSVN – SmartDukaan – Diff – //trunk/PyProj/src/shop2020/model/v1/order/script/DTRReports.py

                           join inventory.warehouse w on w.id = o.fulfilmentWarehouseId join orderstatus os on o.status = os.status
                           where o.status in (3,4,5,7) and o.total_amount > 1 order by code, prodname
                         """
 STOCK_REPORT_GROUP_SQL= """
-                        select c.name, concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as product,  count(*) as delivered,
-                            sum(if(mar.activationTime is not null, 1, 0)) as sold, sum(if(mar.activationTime is not null, 0, 1)) as instock,
-                            sum(if(mar.activationTime is not null, 0,o.total_amount)) as valueinstock
-                        from transaction.order o join user.privatedealuser pd on o.customer_id=pd.id
-                            left join mobileactivationrecord mar on mar.orderId = o.id  join user.counter c on c.id = pd.counter_id
-                            join transaction.lineitem li on li.order_id=o.id
-                        where o.status=12 and o.total_amount > 0 and li.brand = 'Spice' and li.item_id not in (select itemId from catalog.spiceuntraceable) group by name, product  with rollup
+                        select c.name, concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as product,  sum(if(o.status=12, 1,0)) delivered, sum(if(mar.activationTime is not null, 1, 0)) as sold, sum(if(mar.activationTime is null and o.status=12, 1, 0)) as instock, sum(if(mar.activationTime is null and o.status=12, o.total_amount,0)) as valueinstock, sum(if(o.status in (9,10,16,17,40), 1,0)) shipped, sum(if(o.status in (9,10,16,17,40), o.total_amount,0)) shippedAmount, sum(if(o.status in (3,4,5,7), 1,0)) pending, sum(if(o.status in (3,4,5,7), o.total_amount,0)) pendingAmount from transaction.order o join user.privatedealuser pd on o.customer_id=pd.id left join mobileactivationrecord mar on mar.orderId = o.id  join user.counter c on c.id = pd.counter_id join transaction.lineitem li on li.order_id=o.id where o.status in (12,9,10,16,17,40,3,4,5,7) and o.total_amount > 0 and li.brand = 'Spice' and li.item_id not in (select itemId from catalog.spiceuntraceable) group by name, product  with rollup
                         """
 STOCK_REPORT_SQL=       """
                         select c.code, c.name, o.id, date(o.created_timestamp) createddate, date(o.delivery_timestamp) delivereddate, os.statusName,
                         concat_ws(' ',li.brand, li.model_name, li.model_number, li.color) as product,  li.quantity, o.total_amount,
                         datediff(curdate(),date(o.delivery_timestamp)) as ageing  from transaction.order o join user.privatedealuser
                         pd on o.customer_id=pd.id left join mobileactivationrecord mar on mar.orderId = o.id
 default_format = xlwt.XFStyle()
 shipped_headers=['Counter', 'Product', 'Quantity', 'Value']
 tertiary_headers=shipped_headers
 pending_headers=shipped_headers
-ageing_headers=['Counter','Product','QtyDelivered','QtySold','QtyInStock','ValueInStock']
+ageing_headers=['Counter','Product','QtyDelivered','QtySold','QtyInStock','ValueInStock', 'QtyPending', 'ValuePending', 'QtyShipped','ValueShipped']
 mis_headers=['Counter','Last Day Qty', 'Last Day Value','MTD Qty', 'MTD Value']
 mis_headers_prod=['Product','Last Day Qty', 'Last Day Value','MTD Qty', 'MTD Value']
 report_map['shipped'] = {'title':'Shipped Orders Report', 'query' : SHIPPED_ORDER_GROUP_SQL, 'group':2, 'headers':shipped_headers}
 report_map['tertiary'] = {'title':'Tertiary Report', 'query' : SOLD_OUT_GROUP_SQL, 'group':2, 'headers':tertiary_headers}
 report_map['pending'] = {'title':'Pending Orders Report', 'query' : PENDING_ORDER_GROUP_SQL, 'group':2, 'headers':pending_headers}
-report_map['ageing'] = {'title':'Spice Stock Ageing Report', 'query' : STOCK_REPORT_GROUP_SQL, 'group':2, 'headers':ageing_headers}
+report_map['ageing'] = {'title':'Spice Stock Ageing Report', 'query' : STOCK_REPORT_GROUP_SQL, 'group':2, 'headers':ageing_headers, 'grandIndex':5}
 report_map['mis'] = {'title':'Counter wise Sales Report', 'query' : MIS_REPORT_GROUP_SQL, 'group':1, 'headers':mis_headers}
 report_map['mis_prod'] = {'title':'Product wise Sales Report', 'query' : MIS_REPORT_GROUP_PROD_SQL, 'group':1, 'headers':mis_headers_prod}
 #recipients = []
-recipients = ['rajneesh.arora@saholic.com', 'lakshman.g@smobility.in', 'sandeep.sachdeva@shop2020.in', 'amit.sirohi@shop2020.in', 'khushal.bhatia@saholic.com', 'chaitnaya.vats@saholic.com']
+recipients = ['rajneesh.arora@saholic.com', 'lakshman.g@smobility.in', 'sandeep.sachdeva@shop2020.in', 'amit.sirohi@shop2020.in', 'khushal.bhatia@saholic.com', 'chaitnaya.vats@saholic.com', 'ritesh.chauhan@shop2020.in']
 def getDbConnection():
     return MySQLdb.connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME)
                     nullColumn = grColumn
                     break
             tbody.append("<tr>")
+            grandIndex =  headerLen - 1
+            if reportmap.has_key('grandIndex'):
+                grandIndex = reportmap['grandIndex']
             for column in range(0, headerLen):
                 tbody.append(tag)
                 if nullColumn==column:
                     if nullColumn==0:
                         tbody.append('Grand Total')
-                        grandTotal = row[headerLen-1]
+                        grandTotal = row[grandIndex]
                     else:
                         tbody.append('Sub Total')
                 elif row[column] is None:
                         tbody.append('')
                 else:

Subversion Repositories SmartDukaan

(root)//trunk/PyProj/src/shop2020/model/v1/order/script/DTRReports.py – Rev 13088 → 13093