| Line 189... |
Line 189... |
| 189 |
</table><br><br>
|
189 |
</table><br><br>
|
| 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 from allorder group by brand order by count(brand) desc limit 10;")
|
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
|
| - |
|
196 |
''')
|
| 195 |
rows = cur.fetchall()
|
197 |
rows = cur.fetchall()
|
| 196 |
row = 1
|
198 |
row = 1
|
| 197 |
alldata =''
|
199 |
alldata =''
|
| 198 |
for data in rows:
|
200 |
for data in rows:
|
| 199 |
sheet1.write(row,0,data[0])
|
201 |
sheet1.write(row,0,data[0])
|
| Line 206... |
Line 208... |
| 206 |
else:
|
208 |
else:
|
| 207 |
alldata = "'"+data[0]+"'"
|
209 |
alldata = "'"+data[0]+"'"
|
| 208 |
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]))
|
| 209 |
|
211 |
|
| 210 |
print alldata
|
212 |
print alldata
|
| 211 |
cur.execute("select sum(amount_paid) amount,count(*) quantity,count(distinct order_id) from allorder where brand not in("+alldata+");")
|
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+")
|
| - |
|
215 |
''')
|
| 212 |
row_other = cur.fetchall()
|
216 |
row_other = cur.fetchall()
|
| 213 |
print row_other
|
217 |
print row_other
|
| 214 |
for data in row_other:
|
218 |
for data in row_other:
|
| 215 |
sheet1.write(row,0,'Other')
|
219 |
sheet1.write(row,0,'Other')
|
| 216 |
sheet1.write(row,1,data[0])
|
220 |
sheet1.write(row,1,data[0])
|
| Line 219... |
Line 223... |
| 219 |
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]))
|
| 220 |
tbody.append(rowtemplate_brand.format('','','',''))
|
224 |
tbody.append(rowtemplate_brand.format('','','',''))
|
| 221 |
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>'))
|
| 222 |
|
226 |
|
| 223 |
# Brand by MTD
|
227 |
# Brand by MTD
|
| 224 |
cur.execute("select brand ,sum(amount_paid) amount,count(brand) quantity, count(distinct order_id) no_of_orders from allorder where month(`created_on`) = 4 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 from allorder where month(`created_on`) = month(CURDATE()-interval 1 day) group by brand order by count(brand) desc limit 10;")
|
| 225 |
rows = cur.fetchall()
|
229 |
rows = cur.fetchall()
|
| 226 |
row += 2
|
230 |
row += 2
|
| 227 |
alldata =''
|
231 |
alldata =''
|
| 228 |
sheet1.write(row,0,"Brands")
|
232 |
sheet1.write(row,0,"Brands")
|
| 229 |
sheet1.write(row,1,"MTD Amount")
|
233 |
sheet1.write(row,1,"MTD Amount")
|
| Line 242... |
Line 246... |
| 242 |
else:
|
246 |
else:
|
| 243 |
alldata = "'"+data[0]+"'"
|
247 |
alldata = "'"+data[0]+"'"
|
| 244 |
tbody.append(rowtemplate_brand.format(data[0],data[1],data[2],data[3]))
|
248 |
tbody.append(rowtemplate_brand.format(data[0],data[1],data[2],data[3]))
|
| 245 |
|
249 |
|
| 246 |
print alldata
|
250 |
print alldata
|
| 247 |
cur.execute("select sum(amount_paid) amount,count(*) quantity,count(distinct order_id) from allorder where month(`created_on`) = 4 AND brand not in("+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+");")
|
| 248 |
mtd_row_other = cur.fetchall()
|
252 |
mtd_row_other = cur.fetchall()
|
| 249 |
print row_other
|
253 |
print row_other
|
| 250 |
for data in mtd_row_other:
|
254 |
for data in mtd_row_other:
|
| 251 |
sheet1.write(row,0,'Other')
|
255 |
sheet1.write(row,0,'Other')
|
| 252 |
sheet1.write(row,1,data[0])
|
256 |
sheet1.write(row,1,data[0])
|