Subversion Repositories SmartDukaan

Rev

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

Rev 17152 Rev 17153
Line 146... Line 146...
146 
 
 146 
 
147 
def summaryByBrandAndStore():
 147 
def summaryByBrandAndStore():
148 
    con = mdb.connect('localhost','root','shop2020','dtr')
 148 
    con = mdb.connect('localhost','root','shop2020','dtr')
149 
    try:
 149 
    try:
150 
        cur = con.cursor()
150 
        cur = con.cursor()
151 
        book = xlwt.Workbook()
 - 
 
152 
        sheet1 = book.add_sheet("data_by_brand")
 - 
 
153 
        sheet2 = book.add_sheet("data_by_store")
 - 
 
154 
        sheet1.write(0,0,"Brand")
 - 
 
155 
        sheet1.write(0,1,"Amount")
 - 
 
156 
        sheet1.write(0,2,"Quantity")
 - 
 
157 
        sheet1.write(0,3,"No. of order")
 - 
 
158
- 
 
159 
        sheet2.write(0,0,"Store")
 - 
 
160 
        sheet2.write(0,1,"Amount")
 - 
 
161 
        sheet2.write(0,2,"Quantity")
 - 
 
162 
        sheet2.write(0,3,"No. of order")
 - 
 
163 
        sheet2.write(0,4,"MTD Amount")
 - 
 
164 
        sheet2.write(0,5,"MTD Quantity")
 - 
 
165 
        sheet2.write(0,6,"MTD No. of order")
 - 
 
166
- 
 
167
- 
 
168 
            # ----Data by brand---
 151 
        # ----Data by brand---
169 
        tbody = []
 152 
        tbody = []
170 
        rowtemplate_brand="<tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td></tr>"
 153 
        rowtemplate_brand="<tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td></tr>"
171 
        mailBodyTemplate_brand="""
 154 
        mailBodyTemplate_brand="""
172 
                <html>
 155 
                <html>
173 
                    <body>
 156 
                    <body>
Line 196... Line 179...
196 
                    ''')
 179 
                    ''')
197 
        rows = cur.fetchall()
 180 
        rows = cur.fetchall()
198 
        row = 1
 181 
        row = 1
199 
        alldata =''
 182 
        alldata =''
200 
        for data in rows:
 183 
        for data in rows:
201 
            sheet1.write(row,0,data[0])
 - 
 
202 
            sheet1.write(row,1,data[1])
 - 
 
203 
            sheet1.write(row,2,data[2])
 - 
 
204 
            sheet1.write(row,3,data[3])
 - 
 
205 
            row +=1
 184 
            row +=1
206 
            if len(alldata)>0:
 185 
            if len(alldata)>0:
207 
                alldata = alldata + ",'"+ data[0]+"'"
 186 
                alldata = alldata + ",'"+ data[0]+"'"
208 
            else:
 187 
            else:
209 
                alldata = "'"+data[0]+"'"
 188 
                alldata = "'"+data[0]+"'"
210 
            tbody.append(rowtemplate_brand.format(data[0],data[1],data[2],data[3]))
 189 
            tbody.append(rowtemplate_brand.format(data[0],data[1],data[2],data[3]))
211
190
212 
        print alldata
 - 
 
213 
        cur.execute("select sum(amount_paid) amount,count(*) quantity,count(distinct order_id) from allorder where created_on >= CURDATE() -  interval 1 day and created_on < CURDATE() and brand not in("+alldata+")")
 191 
        cur.execute("select sum(amount_paid) amount,count(*) quantity,count(distinct order_id) from allorder where created_on >= CURDATE() -  interval 1 day and created_on < CURDATE() and brand not in("+alldata+")")
214 
        row_other = cur.fetchall()
 192 
        row_other = cur.fetchall()
215 
        print row_other
 - 
 
216 
        for data in row_other:
 193 
        for data in row_other:
217 
            sheet1.write(row,0,'Other')
 - 
 
218 
            sheet1.write(row,1,data[0])
 - 
 
219 
            sheet1.write(row,2,data[1])
 - 
 
220 
            sheet1.write(row,3,data[2])
 - 
 
221 
            tbody.append(rowtemplate_brand.format('Other',data[0],data[1],data[2]))
 194 
            tbody.append(rowtemplate_brand.format('Other',data[0],data[1],data[2]))
222 
        tbody.append(rowtemplate_brand.format('','','',''))
 195 
        tbody.append(rowtemplate_brand.format('','','',''))
223 
        tbody.append(rowtemplate_brand.format('','<b>MTD Amount</b>','<b>MTD Quantity</b>','<b>MTD No. of Order</b>'))
 196 
        tbody.append(rowtemplate_brand.format('','<b>MTD Amount</b>','<b>MTD Quantity</b>','<b>MTD No. of Order</b>'))
224
197
- 
 
 198 
 
- 
 
 199
225 
        # Brand by MTD
 200 
        # Brand by MTD
226 
        cur.execute('''select brand ,sum(amount_paid) amount,count(brand) quantity, count(distinct order_id) no_of_orders
201 
        cur.execute('''select brand ,sum(amount_paid) amount,count(brand) quantity, count(distinct order_id) no_of_orders
227 
                    from allorder where created_on >= CURDATE() -  interval DAY(CURDATE()-INTERVAL 1 day) day and
202 
                    from allorder where created_on >= CURDATE() -  interval DAY(CURDATE()-INTERVAL 1 day) day and
228 
                                        created_on < CURDATE() group by brand order by count(brand) desc limit 10''')
 203 
                                        created_on < CURDATE() group by brand order by count(brand) desc limit 10''')
229 
        rows = cur.fetchall()
 204 
        rows = cur.fetchall()
230 
        row += 2
 205 
        row += 2
231 
        alldata =''
 206 
        alldata =''
232 
        sheet1.write(row,0,"Brands")
 - 
 
233 
        sheet1.write(row,1,"MTD Amount")
 - 
 
234 
        sheet1.write(row,2,"MTD Quantity")
 - 
 
235 
        sheet1.write(row,3,"MTD No. of order")
 - 
 
236 
        row +=1
 - 
 
237
- 
 
238 
        for data in rows:
 207 
        for data in rows:
239 
            sheet1.write(row,0,data[0])
 - 
 
240 
            sheet1.write(row,1,data[1])
 - 
 
241 
            sheet1.write(row,2,data[2])
 - 
 
242 
            sheet1.write(row,3,data[3])
 - 
 
243 
            row +=1
 - 
 
244 
            if len(alldata)>0:
 208 
            if len(alldata)>0:
245 
                alldata = alldata + ",'"+ data[0]+"'"
 209 
                alldata = alldata + ",'"+ data[0]+"'"
246 
            else:
 210 
            else:
247 
                alldata = "'"+data[0]+"'"
 211 
                alldata = "'"+data[0]+"'"
248 
            tbody.append(rowtemplate_brand.format(data[0],data[1],data[2],data[3]))
 212 
            tbody.append(rowtemplate_brand.format(data[0],data[1],data[2],data[3]))
249
213
250 
        print alldata
 - 
 
251 
        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+")")
 214 
        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+")")
252 
        mtd_row_other = cur.fetchall()
 215 
        mtd_row_other = cur.fetchall()
253 
        print row_other
 - 
 
254 
        for data in mtd_row_other:
 216 
        for data in mtd_row_other:
255 
            sheet1.write(row,0,'Other')
 - 
 
256 
            sheet1.write(row,1,data[0])
 - 
 
257 
            sheet1.write(row,2,data[1])
 - 
 
258 
            sheet1.write(row,3,data[2])
 - 
 
259 
            tbody.append(rowtemplate_brand.format('Other',data[0],data[1],data[2]))
 217 
            tbody.append(rowtemplate_brand.format('Other',data[0],data[1],data[2]))
260
218
261 
        message_by_brand = mailBodyTemplate_brand.format("".join(tbody))
 219 
        message_by_brand = mailBodyTemplate_brand.format("".join(tbody))
262 
#         sendmail(["naman.kumar@shop2020.in"] ,message_by_brand, "Summary by brand","")
 - 
 
- 
 
 220
263 
        # Data by Store
 221 
        # Data by Store
264 
        # cur.execute("select store_id store,sum(amount_paid) amount,count(store_id) quantity ,count(distinct order_id) from allorder group by store_id order by store_id ASC;")
 222 
        # cur.execute("select store_id store,sum(amount_paid) amount,count(store_id) quantity ,count(distinct order_id) from allorder group by store_id order by store_id ASC;")
265 
        cur.execute("select x.*,a.store_id mtd_store,sum(a.amount_paid) mtd_amount,count(a.store_id) mtd_quantity, count(distinct a.order_id) mtd_num_of_order from allorder a left join (select store_id store,sum(amount_paid) amount,count(store_id) quantity,count(distinct order_id) num_of_orders from allorder where month(created_on) = 4 group by store_id ) as x on a.store_id=x.store  group by a.store_id order by store_id ASC;")
 223 
        cur.execute("select name, c.* from store left join (select a.*, b.mtdamount,b.mtdquantity, b.mtdorders from  (select store_id, sum(amount_paid), count(*),  count(distinct order_id)  from allorder where date(created_on)=curdate()-interval 1 day group by store_id) a join (select store_id, sum(amount_paid) mtdamount, count(*) mtdquantity,  count(distinct order_id) mtdorders  from allorder where created_on >= CURDATE() -  interval DAY(CURDATE()-INTERVAL 1 day) day group by store_id) b on a.store_id=b.store_id) as c on name = c.store_id")
266 
        rows = cur.fetchall()
 224 
        rows = cur.fetchall()
267 
        new_row = 1
 - 
 
268 
        tbody =[]
 225 
        tbody =[]
269 
        rowtemplate="<tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td><td>{4}</td><td>{5}</td><td>{6}</td></tr>"
 226 
        rowtemplate="<tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td><td>{4}</td><td>{5}</td><td>{6}</td></tr>"
270 
        for data in rows:
 227 
        for data in rows:
271 
            print data[0]
 - 
 
272 
            sheet2.write(new_row,0,data[4])
 - 
 
273 
            sheet2.write(new_row,1,data[1])
 - 
 
274 
            sheet2.write(new_row,2,data[2])
 - 
 
275 
            sheet2.write(new_row,3,data[3])
 - 
 
276 
            sheet2.write(new_row,4,data[5])
 - 
 
277 
            sheet2.write(new_row,5,data[6])
 - 
 
278 
            sheet2.write(new_row,6,data[7])
 - 
 
279 
            new_row +=1
 - 
 
280 
            tbody.append(rowtemplate.format(data[4],data[1],data[2],data[3],data[5],data[6],data[7]))
 228 
            tbody.append(rowtemplate.format(data[4],data[1],data[2],data[3],data[5],data[6],data[7]))
281
229
282 
        # book.save("/home/manish/Desktop/Summary.xls")
 230 
        # book.save("/home/manish/Desktop/Summary.xls")
283
231
284 
        mailBodyTemplate="""
 232 
        mailBodyTemplate="""