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 Accessory Sales Report"
 30 
SUBJECT = "Previous Day Accessory Sales 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.category, i.brand, IFNULL(i.model_name, ''), IFNULL(i.model_number, ''), i.color
- 
 
 53 
                    ORDER BY i.parent_category, i.brand;
51 
                '''
 54 
                '''
52
55
53
56
54 
    conn = getDbConnection()
 57 
    conn = getDbConnection()
55 
    monthdatesmap = {}
 58 
    monthdatesmap = {}
Line 64... Line 67...
64 
        msg =   """\
 67 
        msg =   """\
65 
                    <html>
 68 
                    <html>
66 
                    <body>\n<table border="1">
 69 
                    <body>\n<table border="1">
67 
                    \n<thead>\n
 70 
                    \n<thead>\n
68 
                    <th>Category\n</th>
 71 
                    <th>Category\n</th>
69 
                    <th>Sub Category\n</th>
 - 
 
70 
                    <th>Brand\n</th>
 72 
                    <th>Brand\n</th>
71 
                    <th>Model Name\n</th>
 73 
                    <th>Model Name\n</th>
72 
                    <th>Model Number\n</th>
 74 
                    <th>Model Number\n</th>
73 
                    <th>Color\n</th>
 75 
                    <th>Color\n</th>
74 
                    <th>Quantity\n</th>
 76 
                    <th>Quantity\n</th>
- 
 
 77 
                    <th>Value\n</th>
75 
                    \n</thead>
 78 
                    \n</thead>
76 
                """
79 
                """
- 
 
 80 
        column = 0
- 
 
 81 
        grossTotal = 0
- 
 
 82 
        grossQuantity = 0
- 
 
 83 
        msg = msg + '<tr><td colspan="5"><b>Total</b></td><td>======QuantityToBeReplaced======</td><td>======ValueToBeReplaced======</td></tr>'
77 
        for r in result:
 84 
        for r in result:
78 
            #(parent, category, brand, model_name, model_number, color) = r[0:6]
 - 
 
79 
            #dayofmonth = r[8]
 - 
 
80 
            msg = msg + '<tr>'
 85 
            msg = msg + '<tr>'
81 
            for col in r:
 86 
            for data in r:
- 
 
 87 
                if column == 6 :
- 
 
 88 
                    grossTotal += data
- 
 
 89 
                if column == 5 :
- 
 
 90 
                    grossQuantity += data
82 
                msg = msg + '<td>' + str(col) + '</td>'
 91 
                msg = msg + '<td>' + str(data) + '</td>'
- 
 
 92 
                column += 1
- 
 
 93 
            column = 0
83 
            msg = msg + '</tr>'
 94 
            msg = msg + '</tr>'
- 
 
 95 
        msg = msg + '</table>\n</body>\n</html>'
- 
 
 96 
        msg = msg.replace('======QuantityToBeReplaced======', str(grossQuantity))
- 
 
 97 
        msg = msg.replace('======ValueToBeReplaced======', str(grossTotal))
84 
    except Exception as e:
 98 
    except Exception as e:
85 
      print "Error: unable to fetch data"
 99 
      print "Error: unable to fetch data"
86 
      print e
 100 
      print e
87
101
88 
    msg = msg + '</table>\n</body>\n</html>'
 - 
 
89 
    return msg, result
 102 
    return msg, result
90 
 
 103 
 
91 
def createXlsReport(result):
 104 
def createXlsReport(result):
92 
    workbook = Workbook()
 105 
    workbook = xlwt.Workbook()
93 
    worksheet = workbook.add_sheet("Sheet 1")
 106 
    worksheet = workbook.add_sheet("Sheet 1")
94 
    boldStyle = XFStyle()
 107 
    boldStyle = xlwt.XFStyle()
95 
    f = Font()
 108 
    f = xlwt.Font()
96 
    f.bold = True
 109 
    f.bold = True
97 
    boldStyle.font = f
 110 
    boldStyle.font = f
98
111
99 
    datecolmap = {}
 112 
    datecolmap = {}
100 
    column = 0
 113 
    column = 0
101 
    row = 0
 114 
    row = 0
102
115
103 
    worksheet.write(row, 0, 'Category', boldStyle)
 116 
    worksheet.write(row, 0, 'Category', boldStyle)
104 
    worksheet.write(row, 1, 'Sub Category', boldStyle)
 117 
    worksheet.write(row, 1, 'Brand', boldStyle)
105 
    worksheet.write(row, 2, 'Brand', boldStyle)
 118 
    worksheet.write(row, 2, 'Model Name', boldStyle)
106 
    worksheet.write(row, 3, 'Model Name', boldStyle)
 119 
    worksheet.write(row, 3, 'Model Number', boldStyle)
107 
    worksheet.write(row, 4, 'Model Number', boldStyle)
 120 
    worksheet.write(row, 4, 'Color', boldStyle)
108 
    worksheet.write(row, 5, 'Color', boldStyle)
 121 
    worksheet.write(row, 5, 'Quantity', boldStyle)
109 
    worksheet.write(row, 6, 'Quantity', boldStyle)
 122 
    worksheet.write(row, 6, 'Value', boldStyle)
110
123
111 
    row = 1
 124 
    row = 2
- 
 
 125 
    grossTotal = 0
- 
 
 126 
    grossQuantity = 0
112
127
113 
    for r in result:
 128 
    for r in result:
114 
        #(parent, category, brand, model_name, model_number, color) = r[0:6]
 129 
        #(parent, category, brand, model_name, model_number, color) = r[0:6]
115 
        #dayofmonth = r[8]
 130 
        #dayofmonth = r[8]
116 
        for data in r :
 131 
        for data in r :
- 
 
 132 
            if column == 6 :
- 
 
 133 
                grossTotal += data
- 
 
 134 
            if column == 5 :
- 
 
 135 
                grossQuantity += data
117 
            worksheet.write(row, column, str(data))
 136 
            worksheet.write(row, column, str(data))
118 
            column += 1
 137 
            column += 1
119 
        column = 0
 138 
        column = 0
120 
        row += 1
 139 
        row += 1
- 
 
 140
- 
 
 141 
    worksheet.write_merge(1, 1, 0, 4, 'Total')
- 
 
 142 
    worksheet.write(1, 5, str(grossQuantity), boldStyle)
- 
 
 143 
    worksheet.write(1, 6, str(grossTotal), boldStyle)
121 
    workbook.save(TMP_FILE)
 144 
    workbook.save(TMP_FILE)
122 
 
 145 
 
123 
def sendmail(message):
 146 
def sendmail(message):
124 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
 147 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
125 
    mailServer.ehlo()
 148 
    mailServer.ehlo()