Subversion Repositories SmartDukaan

Rev

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

Rev 5653 Rev 5666
Line 2... Line 2...
2 
import urllib2, cookielib
 2 
import urllib2, cookielib
3 
import MySQLdb
 3 
import MySQLdb
4 
import datetime
 4 
import datetime
5 
import sys
 5 
import sys
6 
import smtplib
 6 
import smtplib
- 
 
 7 
import xlwt
7 
 
 8 
 
8 
from email import encoders
 9 
from email import encoders
9 
from email.mime.text import MIMEText
 10 
from email.mime.text import MIMEText
10 
from email.mime.base import MIMEBase
 11 
from email.mime.base import MIMEBase
11 
from email.mime.multipart import MIMEMultipart
 12 
from email.mime.multipart import MIMEMultipart
12 
from pyExcelerator import Workbook, Font, XFStyle
 13 
#from pyExcelerator import Workbook, Font, XFStyle
13 
from datetime import date
 14 
from datetime import date
14 
 
 15 
 
15 
# Initialize db connection settings.
 16 
# Initialize db connection settings.
16 
DB_HOST = "localhost"
 17 
DB_HOST = "localhost"
17 
DB_USER = "root"
 18 
DB_USER = "root"
Line 20... Line 21...
20 
 
 21 
 
21 
# KEY NAMES
 22 
# KEY NAMES
22 
MONTHNAME = 'monthname'
 23 
MONTHNAME = 'monthname'
23 
DATES = 'dates'
 24 
DATES = 'dates'
24 
 
 25 
 
- 
 
 26 
MAILTO = ['anupam.singh@shop2020.in']
25 
MAILTO = ['rajneesharora@spiceretail.co.in', 'pankaj.kankar@shop2020.in', 'ashutosh.saxena@shop2020.in', 'anupam.singh@shop2020.in']
 27 
#['rajneesharora@spiceretail.co.in', 'pankaj.kankar@shop2020.in', 'ashutosh.saxena@shop2020.in', 'anupam.singh@shop2020.in']
26 
SENDER = "cnc.center@shop2020.in"
 28 
SENDER = "cnc.center@shop2020.in"
27 
PASSWORD = "5h0p2o2o"
 29 
PASSWORD = "5h0p2o2o"
28 
SUBJECT = "Previous Day Orders Report"
 30 
SUBJECT = "Previous Day Orders Report"
29 
SMTP_SERVER = "smtp.gmail.com"
 31 
SMTP_SERVER = "smtp.gmail.com"
30 
SMTP_PORT = 587
32 
SMTP_PORT = 587
Line 36... Line 38...
36
38
37 
def closeConnection(conn):
 39 
def closeConnection(conn):
38 
    conn.close()
 40 
    conn.close()
39 
 
 41 
 
40 
def getProductSaleData():
 42 
def getProductSaleData():
41 
    selectSql = '''SELECT i.parent_category, i.category, i.brand, IFNULL(i.model_name, ''),
 43 
    selectSql = '''SELECT i.parent_category, i.brand, IFNULL(i.model_name, ''),
42 
                    IFNULL(i.model_number, ''), i.color, sum(quantity) AS quantity
 44 
                    IFNULL(i.model_number, ''), i.color, sum(quantity) AS quantity, sum(total_amount) AS totalAmount
43 
                    FROM sales s join item i on (i.id = s.item_id)
45 
                    FROM sales s join item i on (i.id = s.item_id)
44 
                    JOIN datedim d on (d.date_id = s.date_id)
46 
                    JOIN datedim d on (d.date_id = s.date_id)
45 
                    JOIN orderstatus os on (s.status = os.status)
47 
                    JOIN orderstatus os on (s.status = os.status)
46 
                    WHERE d.fulldate in (DATE_SUB(curdate(), INTERVAL 1 DAY))
48 
                    WHERE d.fulldate in (DATE_SUB(curdate(), INTERVAL 1 DAY))
47 
                    AND os.statusGroup in ('In process', 'Delivered', 'Cancelled', 'Return in process', 'Reshipped', 'Refunded')
49 
                    AND os.statusGroup in ('In process', 'Delivered', 'Cancelled', 'Return in process', 'Reshipped', 'Refunded')
48 
                    AND i.parent_category != 'Mobile Accessories'
50 
                    AND i.parent_category != 'Mobile Accessories'
49 
                    AND os.statusSubGroup != 'Cod verification failed'
51 
                    AND os.statusSubGroup != 'Cod verification failed'
50 
                    GROUP BY i.parent_category, i.category, i.brand, IFNULL(i.model_name, ''), IFNULL(i.model_number, ''), i.color;
 52 
                    GROUP BY i.parent_category, i.brand, IFNULL(i.model_name, ''), IFNULL(i.model_number, ''), i.color
- 
 
 53 
                    ORDER BY i.parent_category, i.brand;
51 
                '''
 54 
                '''
52
- 
 
53
- 
 
54 
    conn = getDbConnection()
 55 
    conn = getDbConnection()
55 
    monthdatesmap = {}
 56 
    monthdatesmap = {}
56 
    prodsalesmap = {}
 57 
    prodsalesmap = {}
57 
    try:
 58 
    try:
58 
        # prepare a cursor object using cursor() method
 59 
        # prepare a cursor object using cursor() method
Line 64... Line 65...
64 
        msg =   """\
 65 
        msg =   """\
65 
                    <html>
 66 
                    <html>
66 
                    <body>\n<table border="1">
 67 
                    <body>\n<table border="1">
67 
                    \n<thead>\n
 68 
                    \n<thead>\n
68 
                    <th>Category\n</th>
 69 
                    <th>Category\n</th>
69 
                    <th>Sub Category\n</th>
 - 
 
70 
                    <th>Brand\n</th>
 70 
                    <th>Brand\n</th>
71 
                    <th>Model Name\n</th>
 71 
                    <th>Model Name\n</th>
72 
                    <th>Model Number\n</th>
 72 
                    <th>Model Number\n</th>
73 
                    <th>Color\n</th>
 73 
                    <th>Color\n</th>
74 
                    <th>Quantity\n</th>
 74 
                    <th>Quantity\n</th>
- 
 
 75 
                    <th>Value\n</th>
75 
                    \n</thead>
 76 
                    \n</thead>
76 
                """
77 
                """
- 
 
 78 
        column = 0
- 
 
 79 
        grossTotal = 0
- 
 
 80 
        grossQuantity = 0
- 
 
 81 
        msg = msg + '<tr><td colspan="5"><b>Total</b></td><td>======QuantityToBeReplaced======</td><td>======ValueToBeReplaced======</td></tr>'
77 
        for r in result:
 82 
        for r in result:
78 
            #(parent, category, brand, model_name, model_number, color) = r[0:6]
 - 
 
79 
            #dayofmonth = r[8]
 - 
 
80 
            msg = msg + '<tr>'
 83 
            msg = msg + '<tr>'
81 
            for col in r:
 84 
            for data in r:
- 
 
 85 
                if column == 6 :
- 
 
 86 
                    grossTotal += data
- 
 
 87 
                if column == 5 :
- 
 
 88 
                    grossQuantity += data
82 
                msg = msg + '<td>' + str(col) + '</td>'
 89 
                msg = msg + '<td>' + str(data) + '</td>'
- 
 
 90 
                column += 1
- 
 
 91 
            column = 0
83 
            msg = msg + '</tr>'
 92 
            msg = msg + '</tr>'
- 
 
 93 
        msg = msg + '</table>\n</body>\n</html>'
- 
 
 94 
        msg = msg.replace('======QuantityToBeReplaced======', str(grossQuantity))
- 
 
 95 
        msg = msg.replace('======ValueToBeReplaced======', str(grossTotal))
84 
    except Exception as e:
 96 
    except Exception as e:
85 
      print "Error: unable to fetch data"
 97 
      print "Error: unable to fetch data"
86 
      print e
 98 
      print e
87
99
88 
    msg = msg + '</table>\n</body>\n</html>'
 - 
 
89 
    return msg, result
 100 
    return msg, result
90 
 
 101 
 
91 
def createXlsReport(result):
 102 
def createXlsReport(result):
92 
    workbook = Workbook()
 103 
    workbook = xlwt.Workbook()
93 
    worksheet = workbook.add_sheet("Sheet 1")
 104 
    worksheet = workbook.add_sheet("Sheet 1")
94 
    boldStyle = XFStyle()
 105 
    boldStyle = xlwt.XFStyle()
95 
    f = Font()
 106 
    f = xlwt.Font()
96 
    f.bold = True
 107 
    f.bold = True
97 
    boldStyle.font = f
 108 
    boldStyle.font = f
98
109
99 
    datecolmap = {}
 110 
    datecolmap = {}
100 
    column = 0
 111 
    column = 0
101 
    row = 0
 112 
    row = 0
102
113
103 
    worksheet.write(row, 0, 'Category', boldStyle)
 114 
    worksheet.write(row, 0, 'Category', boldStyle)
104 
    worksheet.write(row, 1, 'Sub Category', boldStyle)
 115 
    worksheet.write(row, 1, 'Brand', boldStyle)
105 
    worksheet.write(row, 2, 'Brand', boldStyle)
 116 
    worksheet.write(row, 2, 'Model Name', boldStyle)
106 
    worksheet.write(row, 3, 'Model Name', boldStyle)
 117 
    worksheet.write(row, 3, 'Model Number', boldStyle)
107 
    worksheet.write(row, 4, 'Model Number', boldStyle)
 118 
    worksheet.write(row, 4, 'Color', boldStyle)
108 
    worksheet.write(row, 5, 'Color', boldStyle)
 119 
    worksheet.write(row, 5, 'Quantity', boldStyle)
109 
    worksheet.write(row, 6, 'Quantity', boldStyle)
 120 
    worksheet.write(row, 6, 'Value', boldStyle)
110
121
111 
    row = 1
 122 
    row = 2
- 
 
 123 
    grossTotal = 0
- 
 
 124 
    grossQuantity = 0
112
125
113 
    for r in result:
 126 
    for r in result:
114 
        #(parent, category, brand, model_name, model_number, color) = r[0:6]
 127 
        #(parent, category, brand, model_name, model_number, color) = r[0:6]
115 
        #dayofmonth = r[8]
 128 
        #dayofmonth = r[8]
116 
        for data in r :
 129 
        for data in r :
- 
 
 130 
            if column == 6 :
- 
 
 131 
                grossTotal += data
- 
 
 132 
            if column == 5 :
- 
 
 133 
                grossQuantity += data
117 
            worksheet.write(row, column, str(data))
 134 
            worksheet.write(row, column, str(data))
118 
            column += 1
 135 
            column += 1
119 
        column = 0
 136 
        column = 0
120 
        row += 1
 137 
        row += 1
- 
 
 138
- 
 
 139 
    worksheet.write_merge(1, 1, 0, 4, 'Total')
- 
 
 140 
    worksheet.write(1, 5, str(grossQuantity), boldStyle)
- 
 
 141 
    worksheet.write(1, 6, str(grossTotal), boldStyle)
121 
    workbook.save(TMP_FILE)
 142 
    workbook.save(TMP_FILE)
122 
 
 143 
 
123 
def sendmail(message):
 144 
def sendmail(message):
124 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
 145 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
125 
    mailServer.ehlo()
 146 
    mailServer.ehlo()