WebSVN – SmartDukaan – Diff – //trunk/PyDTR/src/dtr/reports/getdata.py



                    </body>
                </html>
            """
                        
        cur.execute('''select brand ,sum(amount_paid) amount,count(brand) quantity, count(distinct order_id) no_of_orders  
                    from allorder where created_on >= CURDATE() -  interval 1 day and created_on < CURDATE() group by brand order by count(brand) desc limit 10
                    ''')
        rows = cur.fetchall()
        row = 1
        alldata =''
        for data in rows:

                alldata = "'"+data[0]+"'"
            tbody.append(rowtemplate_brand.format(data[0],data[1],data[2],data[3]))
        
        print alldata
        cur.execute('''select sum(amount_paid) amount,count(*) quantity,count(distinct order_id) from allorder 
                    where created_on >= CURDATE() -  interval 1 day and o.created_on < CURDATE() and brand not in("+alldata+")
                    ''')
        row_other = cur.fetchall()
        print row_other
        for data in row_other:
            sheet1.write(row,0,'Other')

            tbody.append(rowtemplate_brand.format('Other',data[0],data[1],data[2]))
        tbody.append(rowtemplate_brand.format('','','',''))
        tbody.append(rowtemplate_brand.format('','<b>MTD Amount</b>','<b>MTD Quantity</b>','<b>MTD No. of Order</b>'))
        
        # Brand by MTD
        cur.execute('''select brand ,sum(amount_paid) amount,count(brand) quantity, count(distinct order_id) no_of_orders  
                    from allorder where created_on >= CURDATE() -  interval DAY(CURDATE()-INTERVAL 1 day) day and 
                                        created_on < CURDATE() group by brand order by count(brand) desc limit 10''')
        rows = cur.fetchall()
        row += 2
        alldata =''
        sheet1.write(row,0,"Brands")
        sheet1.write(row,1,"MTD Amount")

            else:
                alldata = "'"+data[0]+"'"
            tbody.append(rowtemplate_brand.format(data[0],data[1],data[2],data[3]))
        
        print alldata
        cur.execute(''''select sum(amount_paid) amount,count(*) quantity,count(distinct order_id) from allorder where 
                        created_on >= CURDATE() -  interval DAY(CURDATE()-INTERVAL 1 day) day and 
                        created_on < CURDATE() AND brand not in("+alldata+")''')
        mtd_row_other = cur.fetchall()
        print row_other
        for data in mtd_row_other:
            sheet1.write(row,0,'Other')
            sheet1.write(row,1,data[0])

Rev 17149	Rev 17150
Line 190...	Line 190...
190	`</body>`	190	`</body>`
191	`</html>`	191	`</html>`
192	`"""`	192	`"""`
193		193
194	`cur.execute('''select brand ,sum(amount_paid) amount,count(brand) quantity, count(distinct order_id) no_of_orders`	194	`cur.execute('''select brand ,sum(amount_paid) amount,count(brand) quantity, count(distinct order_id) no_of_orders`
195	`from allorder where o.created_on >= CURDATE() - interval 1 day and o.created_on < CURDATE() group by brand order by count(brand) desc limit 10`	195	`from allorder where created_on >= CURDATE() - interval 1 day and created_on < CURDATE() group by brand order by count(brand) desc limit 10`
196	`''')`	196	`''')`
197	`rows = cur.fetchall()`	197	`rows = cur.fetchall()`
198	`row = 1`	198	`row = 1`
199	`alldata =''`	199	`alldata =''`
200	`for data in rows:`	200	`for data in rows:`
Line 209...	Line 209...
209	`alldata = "'"+data[0]+"'"`	209	`alldata = "'"+data[0]+"'"`
210	`tbody.append(rowtemplate_brand.format(data[0],data[1],data[2],data[3]))`	210	`tbody.append(rowtemplate_brand.format(data[0],data[1],data[2],data[3]))`
211		211
212	`print alldata`	212	`print alldata`
213	`cur.execute('''select sum(amount_paid) amount,count(*) quantity,count(distinct order_id) from allorder`	213	`cur.execute('''select sum(amount_paid) amount,count(*) quantity,count(distinct order_id) from allorder`
214	`where o.created_on >= CURDATE() - interval 1 day and o.created_on < CURDATE() and brand not in("+alldata+")`	214	`where created_on >= CURDATE() - interval 1 day and o.created_on < CURDATE() and brand not in("+alldata+")`
215	`''')`	215	`''')`
216	`row_other = cur.fetchall()`	216	`row_other = cur.fetchall()`
217	`print row_other`	217	`print row_other`
218	`for data in row_other:`	218	`for data in row_other:`
219	`sheet1.write(row,0,'Other')`	219	`sheet1.write(row,0,'Other')`
Line 223...	Line 223...
223	`tbody.append(rowtemplate_brand.format('Other',data[0],data[1],data[2]))`	223	`tbody.append(rowtemplate_brand.format('Other',data[0],data[1],data[2]))`
224	`tbody.append(rowtemplate_brand.format('','','',''))`	224	`tbody.append(rowtemplate_brand.format('','','',''))`
225	`tbody.append(rowtemplate_brand.format('','<b>MTD Amount</b>','<b>MTD Quantity</b>','<b>MTD No. of Order</b>'))`	225	`tbody.append(rowtemplate_brand.format('','<b>MTD Amount</b>','<b>MTD Quantity</b>','<b>MTD No. of Order</b>'))`
226		226
227	`# Brand by MTD`	227	`# Brand by MTD`
228	cur.execute("select brand ,sum(amount_paid) amount,count(brand) quantity, count(distinct order_id) no_of_orders from allorder where month(`created_on`) = month(CURDATE()-interval 1 day) group by brand order by count(brand) desc limit 10;")	228	`cur.execute('''select brand ,sum(amount_paid) amount,count(brand) quantity, count(distinct order_id) no_of_orders`
-		229	`from allorder where created_on >= CURDATE() - interval DAY(CURDATE()-INTERVAL 1 day) day and`
-		230	`created_on < CURDATE() group by brand order by count(brand) desc limit 10''')`
229	`rows = cur.fetchall()`	231	`rows = cur.fetchall()`
230	`row += 2`	232	`row += 2`
231	`alldata =''`	233	`alldata =''`
232	`sheet1.write(row,0,"Brands")`	234	`sheet1.write(row,0,"Brands")`
233	`sheet1.write(row,1,"MTD Amount")`	235	`sheet1.write(row,1,"MTD Amount")`
Line 246...	Line 248...
246	`else:`	248	`else:`
247	`alldata = "'"+data[0]+"'"`	249	`alldata = "'"+data[0]+"'"`
248	`tbody.append(rowtemplate_brand.format(data[0],data[1],data[2],data[3]))`	250	`tbody.append(rowtemplate_brand.format(data[0],data[1],data[2],data[3]))`
249		251
250	`print alldata`	252	`print alldata`
251	cur.execute("select sum(amount_paid) amount,count(*) quantity,count(distinct order_id) from allorder where month(`created_on`) = month(CURDATE()-interval 1 day) AND brand not in("+alldata+");")	253	`cur.execute(''''select sum(amount_paid) amount,count(*) quantity,count(distinct order_id) from allorder where`
-		254	`created_on >= CURDATE() - interval DAY(CURDATE()-INTERVAL 1 day) day and`
-		255	`created_on < CURDATE() AND brand not in("+alldata+")''')`
252	`mtd_row_other = cur.fetchall()`	256	`mtd_row_other = cur.fetchall()`
253	`print row_other`	257	`print row_other`
254	`for data in mtd_row_other:`	258	`for data in mtd_row_other:`
255	`sheet1.write(row,0,'Other')`	259	`sheet1.write(row,0,'Other')`
256	`sheet1.write(row,1,data[0])`	260	`sheet1.write(row,1,data[0])`

Subversion Repositories SmartDukaan

(root)//trunk/PyDTR/src/dtr/reports/getdata.py – Rev 17149 → 17150