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



from email.mime.text import MIMEText
from email.mime.base import MIMEBase
from email.mime.multipart import MIMEMultipart
 
 
DataService.initialize(db_hostname='192.168.190.114')
SaleMap = {}
BrandCatMap = {}
UnUsableStatus = [25, 27, 30, 32, 47, 48, 68, 71, 72, 73, 74]
UsableStatus = [21, 28, 64, 67, 69, 70]
db = MySQLdb.connect('localhost',"root","shop2020","warehouse" )

    #allShippedOrders = Order.query.filter(Order.created_timestamp.between(fromDate.date(),toDate.date())).filter(Order.shipping_timestamp!=None).all()
    
    allShippedOrders = session.query(LineItem.item_id,LineItem.productGroup,Order.source,Order.status,Order.received_return_timestamp, \
                                     LineItem.quantity,Order.id,LineItem.brand,LineItem.model_name,LineItem.model_number,LineItem.color) \
    .join((Order,LineItem.order_id==Order.id)).filter(Order.created_timestamp.between(fromDate.date(),toDate.date())) \
    .filter(Order.shipping_timestamp!=None).filter(~LineItem.productGroup.in_(('Handsets','Mobile Handset','Smart Phone','Mobile Phone','Tablets'))).all()
 
    for shippedOrder in allShippedOrders:
        if SaleMap.has_key(shippedOrder[0]):
            val = SaleMap.get(shippedOrder[0])
            if shippedOrder[2] in (OrderSource.WEBSITE,OrderSource.MOBILESITE):

    
    for shippedOrder in allShippedOrders:
        if shippedOrder[4] is not None:
            val = SaleMap.get(shippedOrder[0])
            sql = "select quantity,type from scanNew where orderId=%d and type in ('SALE_RET','DOA_IN','SALE_RET_UNUSABLE')" %(shippedOrder[6])
 
            cursor.execute(sql)
            result = cursor.fetchone()
            print result
            if result is None:
                continue

def sendEmail():
    xstr = lambda s: s or ""
    message="""<html>
            <body>
            <h3>Source Wise Return</h3>
            <h4>Orders from """+str((datetime.now()-timedelta(days=45)).date())+"""
             to """+str((datetime.now()-timedelta(days=30)).date())+"""</h4>
            <table border="1" style="width:50%;">
            <thead>
            <tr><th>Source</th>
            <th>Total Sale</th>
            <th>Total Returns</th>

                    <td style="text-align:center">"""+str(int(highReturnDataBrandCat.nonSellableReturn))+"""</td>
                    <td style="text-align:center">"""+str(round(highReturnDataBrandCat.nonSellableReturnPercentage*100,1))+"""</td>
                     </tr>"""
    message+="""</tr></tbody></table></body></html>"""
    
    wbk = xlwt.Workbook(encoding="UTF-8")
    sheet = wbk.add_sheet('Source Wise Return')
    xstr = lambda s: s or ""
    heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')
    
    excel_integer_format = '0'

        if (v.nonSellableCountWebsite+v.nonSellableCountAmazon+v.nonSellableCountEbay+v.nonSellableCountSnapdeal+v.nonSellableCountFlipkart) > 0:
            sheet.write(sheet_iterator,23,round(float(v.nonSellableCountWebsite+v.nonSellableCountAmazon+v.nonSellableCountEbay+v.nonSellableCountSnapdeal+v.nonSellableCountFlipkart)*100/(v.websiteSale+v.amazonSale+v.ebaySale+v.flipkartSale+v.snapdealSale),1))
        else:
            sheet.write(sheet_iterator,23,0)
        sheet_iterator+=1
    filename = "/tmp/return-report-source-ex-mob-tab.xls"
    wbk.save(filename)
        
    mailServer = smtplib.SMTP("smtp.gmail.com", 587)
    mailServer.ehlo()
    mailServer.starttls()
    mailServer.ehlo()
 
    recipients = ['rajneesh.arora@saholic.com','anikendra.das@saholic.com','kshitij.sood@saholic.com','amit.sirohi@saholic.com']
    msg = MIMEMultipart()
    msg['Subject'] = "Sale Return Source Wise Except Mobiles & Tablets" + ' - ' + str(datetime.now())
    msg['From'] = ""
    msg['To'] = ",".join(recipients)
    msg.preamble = "Sale Return Source Wise Sale Return Source Wise Except Mobiles & Tablets" + ' - ' + str(datetime.now())
    html_msg = MIMEText(message, 'html')
    msg.attach(html_msg)
    fileMsg = MIMEBase('application','vnd.ms-excel')
    fileMsg.set_payload(file(filename).read())
    encoders.encode_base64(fileMsg)
    from datetime import date
    fileMsg.add_header('Content-Disposition','attachment;filename=Return-Source-Wise-Except-MOb-Tab' + ' - ' + date.today().isoformat() + '.xls')
    msg.attach(fileMsg)
    try:
        mailServer.login("cnc.center@shop2020.in", "5h0p2o2o")
        #mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())
        mailServer.sendmail("5h0p2o2o", recipients, msg.as_string())

Rev 12002	Rev 12241
Line 14...	Line 14...
14	`from email.mime.text import MIMEText`	14	`from email.mime.text import MIMEText`
15	`from email.mime.base import MIMEBase`	15	`from email.mime.base import MIMEBase`
16	`from email.mime.multipart import MIMEMultipart`	16	`from email.mime.multipart import MIMEMultipart`
17		17
18		18
19	`DataService.initialize(db_hostname='localhost')`	19	`DataService.initialize(db_hostname='192.168.190.114')`
20	`SaleMap = {}`	20	`SaleMap = {}`
21	`BrandCatMap = {}`	21	`BrandCatMap = {}`
22	`UnUsableStatus = [25, 27, 30, 32, 47, 48, 68, 71, 72, 73, 74]`	22	`UnUsableStatus = [25, 27, 30, 32, 47, 48, 68, 71, 72, 73, 74]`
23	`UsableStatus = [21, 28, 64, 67, 69, 70]`	23	`UsableStatus = [21, 28, 64, 67, 69, 70]`
24	`db = MySQLdb.connect('localhost',"root","shop2020","warehouse" )`	24	`db = MySQLdb.connect('localhost',"root","shop2020","warehouse" )`
Line 82...	Line 82...
82	`#allShippedOrders = Order.query.filter(Order.created_timestamp.between(fromDate.date(),toDate.date())).filter(Order.shipping_timestamp!=None).all()`	82	`#allShippedOrders = Order.query.filter(Order.created_timestamp.between(fromDate.date(),toDate.date())).filter(Order.shipping_timestamp!=None).all()`
83		83
84	`allShippedOrders = session.query(LineItem.item_id,LineItem.productGroup,Order.source,Order.status,Order.received_return_timestamp, \`	84	`allShippedOrders = session.query(LineItem.item_id,LineItem.productGroup,Order.source,Order.status,Order.received_return_timestamp, \`
85	`LineItem.quantity,Order.id,LineItem.brand,LineItem.model_name,LineItem.model_number,LineItem.color) \`	85	`LineItem.quantity,Order.id,LineItem.brand,LineItem.model_name,LineItem.model_number,LineItem.color) \`
86	`.join((Order,LineItem.order_id==Order.id)).filter(Order.created_timestamp.between(fromDate.date(),toDate.date())) \`	86	`.join((Order,LineItem.order_id==Order.id)).filter(Order.created_timestamp.between(fromDate.date(),toDate.date())) \`
87	`.filter(Order.shipping_timestamp!=None).all()`	87	`.filter(Order.shipping_timestamp!=None).filter(~LineItem.productGroup.in_(('Handsets','Mobile Handset','Smart Phone','Mobile Phone','Tablets'))).all()`
88		88
89	`for shippedOrder in allShippedOrders:`	89	`for shippedOrder in allShippedOrders:`
90	`if SaleMap.has_key(shippedOrder[0]):`	90	`if SaleMap.has_key(shippedOrder[0]):`
91	`val = SaleMap.get(shippedOrder[0])`	91	`val = SaleMap.get(shippedOrder[0])`
92	`if shippedOrder[2] in (OrderSource.WEBSITE,OrderSource.MOBILESITE):`	92	`if shippedOrder[2] in (OrderSource.WEBSITE,OrderSource.MOBILESITE):`
Line 122...	Line 122...
122		122
123	`for shippedOrder in allShippedOrders:`	123	`for shippedOrder in allShippedOrders:`
124	`if shippedOrder[4] is not None:`	124	`if shippedOrder[4] is not None:`
125	`val = SaleMap.get(shippedOrder[0])`	125	`val = SaleMap.get(shippedOrder[0])`
126	`sql = "select quantity,type from scanNew where orderId=%d and type in ('SALE_RET','DOA_IN','SALE_RET_UNUSABLE')" %(shippedOrder[6])`	126	`sql = "select quantity,type from scanNew where orderId=%d and type in ('SALE_RET','DOA_IN','SALE_RET_UNUSABLE')" %(shippedOrder[6])`
127	`print sql`	-
128	`cursor.execute(sql)`	127	`cursor.execute(sql)`
129	`result = cursor.fetchone()`	128	`result = cursor.fetchone()`
130	`print result`	129	`print result`
131	`if result is None:`	130	`if result is None:`
132	`continue`	131	`continue`
Line 196...	Line 195...
196	`def sendEmail():`	195	`def sendEmail():`
197	`xstr = lambda s: s or ""`	196	`xstr = lambda s: s or ""`
198	`message="""<html>`	197	`message="""<html>`
199	`<body>`	198	`<body>`
200	`<h3>Source Wise Return</h3>`	199	`<h3>Source Wise Return</h3>`
-		200	`<h4>Orders from """+str((datetime.now()-timedelta(days=45)).date())+"""`
-		201	`to """+str((datetime.now()-timedelta(days=30)).date())+"""</h4>`
201	`<table border="1" style="width:50%;">`	202	`<table border="1" style="width:50%;">`
202	`<thead>`	203	`<thead>`
203	`<tr><th>Source</th>`	204	`<tr><th>Source</th>`
204	`<th>Total Sale</th>`	205	`<th>Total Sale</th>`
205	`<th>Total Returns</th>`	206	`<th>Total Returns</th>`
Line 420...	Line 421...
420	`<td style="text-align:center">"""+str(int(highReturnDataBrandCat.nonSellableReturn))+"""</td>`	421	`<td style="text-align:center">"""+str(int(highReturnDataBrandCat.nonSellableReturn))+"""</td>`
421	`<td style="text-align:center">"""+str(round(highReturnDataBrandCat.nonSellableReturnPercentage*100,1))+"""</td>`	422	`<td style="text-align:center">"""+str(round(highReturnDataBrandCat.nonSellableReturnPercentage*100,1))+"""</td>`
422	`</tr>"""`	423	`</tr>"""`
423	`message+="""</tr></tbody></table></body></html>"""`	424	`message+="""</tr></tbody></table></body></html>"""`
424		425
425	`wbk = xlwt.Workbook()`	426	`wbk = xlwt.Workbook(encoding="UTF-8")`
426	`sheet = wbk.add_sheet('Source Wise Return')`	427	`sheet = wbk.add_sheet('Source Wise Return')`
427	`xstr = lambda s: s or ""`	428	`xstr = lambda s: s or ""`
428	`heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')`	429	`heading_xf = xlwt.easyxf('font: bold on; align: wrap off, vert centre, horiz center')`
429		430
430	`excel_integer_format = '0'`	431	`excel_integer_format = '0'`
Line 487...	Line 488...
487	`if (v.nonSellableCountWebsite+v.nonSellableCountAmazon+v.nonSellableCountEbay+v.nonSellableCountSnapdeal+v.nonSellableCountFlipkart) > 0:`	488	`if (v.nonSellableCountWebsite+v.nonSellableCountAmazon+v.nonSellableCountEbay+v.nonSellableCountSnapdeal+v.nonSellableCountFlipkart) > 0:`
488	`sheet.write(sheet_iterator,23,round(float(v.nonSellableCountWebsite+v.nonSellableCountAmazon+v.nonSellableCountEbay+v.nonSellableCountSnapdeal+v.nonSellableCountFlipkart)*100/(v.websiteSale+v.amazonSale+v.ebaySale+v.flipkartSale+v.snapdealSale),1))`	489	`sheet.write(sheet_iterator,23,round(float(v.nonSellableCountWebsite+v.nonSellableCountAmazon+v.nonSellableCountEbay+v.nonSellableCountSnapdeal+v.nonSellableCountFlipkart)*100/(v.websiteSale+v.amazonSale+v.ebaySale+v.flipkartSale+v.snapdealSale),1))`
489	`else:`	490	`else:`
490	`sheet.write(sheet_iterator,23,0)`	491	`sheet.write(sheet_iterator,23,0)`
491	`sheet_iterator+=1`	492	`sheet_iterator+=1`
492	`filename = "/tmp/return-report-source.xls"`	493	`filename = "/tmp/return-report-source-ex-mob-tab.xls"`
493	`wbk.save(filename)`	494	`wbk.save(filename)`
494		495
495	`mailServer = smtplib.SMTP("smtp.gmail.com", 587)`	496	`mailServer = smtplib.SMTP("smtp.gmail.com", 587)`
496	`mailServer.ehlo()`	497	`mailServer.ehlo()`
497	`mailServer.starttls()`	498	`mailServer.starttls()`
498	`mailServer.ehlo()`	499	`mailServer.ehlo()`
499		500
500	`recipients = ['kshitij.sood@saholic.com']`	501	`recipients = ['rajneesh.arora@saholic.com','anikendra.das@saholic.com','kshitij.sood@saholic.com','amit.sirohi@saholic.com']`
501	`msg = MIMEMultipart()`	502	`msg = MIMEMultipart()`
502	`msg['Subject'] = "Sale Return Source Wise" + ' - ' + str(datetime.now())`	503	`msg['Subject'] = "Sale Return Source Wise Except Mobiles & Tablets" + ' - ' + str(datetime.now())`
503	`msg['From'] = ""`	504	`msg['From'] = ""`
504	`msg['To'] = ",".join(recipients)`	505	`msg['To'] = ",".join(recipients)`
505	`msg.preamble = "Sale Return Source Wise" + ' - ' + str(datetime.now())`	506	`msg.preamble = "Sale Return Source Wise Sale Return Source Wise Except Mobiles & Tablets" + ' - ' + str(datetime.now())`
506	`html_msg = MIMEText(message, 'html')`	507	`html_msg = MIMEText(message, 'html')`
507	`msg.attach(html_msg)`	508	`msg.attach(html_msg)`
508	`fileMsg = MIMEBase('application','vnd.ms-excel')`	509	`fileMsg = MIMEBase('application','vnd.ms-excel')`
509	`fileMsg.set_payload(file(filename).read())`	510	`fileMsg.set_payload(file(filename).read())`
510	`encoders.encode_base64(fileMsg)`	511	`encoders.encode_base64(fileMsg)`
511	`from datetime import date`	512	`from datetime import date`
512	`fileMsg.add_header('Content-Disposition','attachment;filename=Return-Source-Wise' + ' - ' + date.today().isoformat() + '.xls')`	513	`fileMsg.add_header('Content-Disposition','attachment;filename=Return-Source-Wise-Except-MOb-Tab' + ' - ' + date.today().isoformat() + '.xls')`
513	`msg.attach(fileMsg)`	514	`msg.attach(fileMsg)`
514	`try:`	515	`try:`
515	`mailServer.login("cnc.center@shop2020.in", "5h0p2o2o")`	516	`mailServer.login("cnc.center@shop2020.in", "5h0p2o2o")`
516	`#mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())`	517	`#mailServer.sendmail("cafe@nes", ['kshitij.sood@saholic.com'], msg.as_string())`
517	`mailServer.sendmail("5h0p2o2o", recipients, msg.as_string())`	518	`mailServer.sendmail("5h0p2o2o", recipients, msg.as_string())`

Subversion Repositories SmartDukaan

(root)/trunk/PyProj/src/shop2020/model/v1/order/script/SourceWiseItemReturnReportExceptMobilesTab.py – Rev 12002 → 12241