Subversion Repositories SmartDukaan

Rev

Rev 6713 | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 6713 Rev 8075
Line 21... Line 21...
21 
 
 21 
 
22 
# KEY NAMES
 22 
# KEY NAMES
23 
MONTHNAME = 'monthname'
 23 
MONTHNAME = 'monthname'
24 
DATES = 'dates'
 24 
DATES = 'dates'
25 
 
 25 
 
26 
MAILTO = ['rajneesharora@spiceretail.co.in', 'chaitnaya.vats@shop2020.in', 'ashutosh.saxena@shop2020.in', 'chandan.kumar@shop2020.in', 'anupam.singh@shop2020.in']
 26 
MAILTO = ['rajneesharora@spiceretail.co.in', 'chaitnaya.vats@shop2020.in', 'ashutosh.saxena@shop2020.in', 'chandan.kumar@shop2020.in', 'anupam.singh@shop2020.in', 'rajveer.singh@shop2020.in']
27 
SENDER = "cnc.center@shop2020.in"
 27 
#SENDER = "cnc.center@shop2020.in"
28 
PASSWORD = "5h0p2o2o"
 28 
#PASSWORD = "5h0p2o2o"
- 
 
 29 
#MAILTO = ['rajveer.singh@shop2020.in']
- 
 
 30 
SENDER = "build@shop2020.in"
- 
 
 31 
PASSWORD = "cafe@nes"
29 
SUBJECT = "Previous Day Orders Report"
 32 
SUBJECT = "Previous Day Orders Report"
30 
SMTP_SERVER = "smtp.gmail.com"
 33 
SMTP_SERVER = "smtp.gmail.com"
31 
SMTP_PORT = 587
34 
SMTP_PORT = 587
32 
 
 35 
 
33 
TMP_FILE="/tmp/previous_orders_report.xls"
 36 
TMP_FILE="/tmp/previous_orders_report.xls"
Line 38... Line 41...
38 
def closeConnection(conn):
 41 
def closeConnection(conn):
39 
    conn.close()
 42 
    conn.close()
40 
 
 43 
 
41 
def getProductSaleData():
 44 
def getProductSaleData():
42 
    selectSql = '''SELECT i.parent_category, i.brand, IFNULL(i.model_name, ''),
 45 
    selectSql = '''SELECT i.parent_category, i.brand, IFNULL(i.model_name, ''),
43 
                    IFNULL(i.model_number, ''), i.color, sum(quantity) AS quantity, sum(total_amount) AS totalAmount
 46 
                    IFNULL(i.model_number, ''), i.color, s.source, sum(quantity) AS quantity, sum(total_amount) AS totalAmount
44 
                    FROM sales s join item i on (i.id = s.item_id)
47 
                    FROM sales s join item i on (i.id = s.item_id)
45 
                    JOIN datedim d on (d.date_id = s.date_id)
48 
                    JOIN datedim d on (d.date_id = s.date_id)
46 
                    JOIN orderstatus os on (s.status = os.status)
49 
                    JOIN orderstatus os on (s.status = os.status)
47 
                    WHERE d.fulldate in (DATE_SUB(curdate(), INTERVAL 1 DAY))
50 
                    WHERE d.fulldate in (DATE_SUB(curdate(), INTERVAL 1 DAY))
48 
                    AND os.statusGroup in ('Delivered', 'In process', 'Refunded', 'Return in process', 'Unused')
51 
                    AND os.statusGroup in ('Delivered', 'In process', 'Refunded', 'Return in process', 'Unused')
49 
                    AND i.parent_category != 'Mobile Accessories'
52 
                    AND i.parent_category != 'Mobile Accessories'
50 
                    AND os.statusSubGroup not in ('Cancellation pending')
53 
                    AND os.statusSubGroup not in ('Cancellation pending')
51 
                    GROUP BY i.parent_category, i.brand, IFNULL(i.model_name, ''), IFNULL(i.model_number, ''), i.color
 54 
                    GROUP BY i.parent_category, i.brand, IFNULL(i.model_name, ''), IFNULL(i.model_number, ''), i.color, s.source
52 
                    ORDER BY i.parent_category, i.brand;
 55 
                    ORDER BY i.parent_category, i.brand;
53 
                '''
 56 
                '''
54 
    conn = getDbConnection()
 57 
    conn = getDbConnection()
55 
    monthdatesmap = {}
 58 
    monthdatesmap = {}
56 
    prodsalesmap = {}
 59 
    prodsalesmap = {}
Line 61... Line 64...
61 
        # Fetch source id.
 64 
        # Fetch source id.
62 
        cursor.execute(selectSql)
 65 
        cursor.execute(selectSql)
63 
        result = cursor.fetchall()
 66 
        result = cursor.fetchall()
64 
        msg =   """\
 67 
        msg =   """\
65 
                    <html>
 68 
                    <html>
66 
                    <body>\n<table border="1">
 69 
                    <body>\n<h3>Website  ======WebsiteSale====== </h3><h3>Amazon  ======AmazonSale====== </h3>\n<table border="1">
67 
                    \n<thead>\n
 70 
                    \n<thead>\n
68 
                    <th>Category\n</th>
 71 
                    <th>Category\n</th>
69 
                    <th>Brand\n</th>
 72 
                    <th>Brand\n</th>
70 
                    <th>Model Name\n</th>
 73 
                    <th>Model Name\n</th>
71 
                    <th>Model Number\n</th>
 74 
                    <th>Model Number\n</th>
72 
                    <th>Color\n</th>
 75 
                    <th>Color\n</th>
- 
 
 76 
                    <th>Source\n</th>
73 
                    <th>Quantity\n</th>
 77 
                    <th>Quantity\n</th>
74 
                    <th>Value\n</th>
 78 
                    <th>Value\n</th>
75 
                    \n</thead>
 79 
                    \n</thead>
76 
                """
80 
                """
77 
        column = 0
 81 
        column = 0
78 
        grossTotal = 0
 82 
        grossTotal = 0
79 
        grossQuantity = 0
 83 
        grossQuantity = 0
- 
 
 84 
        websiteSale = 0
- 
 
 85 
        amazonSale = 0
- 
 
 86 
        websiteQty = 0
- 
 
 87 
        amazonQty = 0
80 
        msg = msg + '<tr><td colspan="5"><b>Total</b></td><td>======QuantityToBeReplaced======</td><td>======ValueToBeReplaced======</td></tr>'
 88 
        msg = msg + '<tr><td colspan="6"><b>Total</b></td><td>======QuantityToBeReplaced======</td><td>======ValueToBeReplaced======</td></tr>'
81 
        for r in result:
 89 
        for r in result:
82 
            msg = msg + '<tr>'
 90 
            msg = msg + '<tr>'
- 
 
 91 
	    source = 1
83 
            for data in r:
 92 
            for data in r:
84 
                if column == 6 :
 93 
                if column == 7 :
85 
                    grossTotal += data
 94 
                    grossTotal += data
- 
 
 95 
		    if source == 1:
- 
 
 96 
			websiteSale += data
- 
 
 97 
		    if source == 3:
- 
 
 98 
			amazonSale += data
86 
                if column == 5 :
 99 
                if column == 6 :
87 
                    grossQuantity += data
 100 
                    grossQuantity += data
- 
 
 101 
		    if source == 1:
- 
 
 102 
			websiteQty += data
- 
 
 103 
		    if source == 3:
- 
 
 104 
			amazonQty += data
- 
 
 105 
		if column == 5 :
- 
 
 106 
		    source = data
88 
                msg = msg + '<td>' + str(data) + '</td>'
 107 
                msg = msg + '<td>' + str(data) + '</td>'
89 
                column += 1
 108 
                column += 1
90 
            column = 0
 109 
            column = 0
91 
            msg = msg + '</tr>'
 110 
            msg = msg + '</tr>'
92 
        msg = msg + '</table>\n</body>\n</html>'
 111 
        msg = msg + '</table>\n</body>\n</html>'
93 
        msg = msg.replace('======QuantityToBeReplaced======', str(grossQuantity))
 112 
        msg = msg.replace('======QuantityToBeReplaced======', str(grossQuantity))
94 
        msg = msg.replace('======ValueToBeReplaced======', str(grossTotal))
 113 
        msg = msg.replace('======ValueToBeReplaced======', str(grossTotal))
- 
 
 114 
        #msg = msg.replace('======WebsiteQty=====', str(websiteQty))
- 
 
 115 
        #msg = msg.replace('======AmazonQty=====', str(amazonQty))
- 
 
 116 
        msg = msg.replace('======WebsiteSale======', "Sale - " + str(websiteSale) + "    Quantity - " + str(websiteQty))
- 
 
 117 
        msg = msg.replace('======AmazonSale======', "Sale - " + str(amazonSale) + "    Quantity - " + str(amazonQty))
95 
    except Exception as e:
 118 
    except Exception as e:
96 
      print "Error: unable to fetch data"
 119 
      print "Error: unable to fetch data"
97 
      print e
 120 
      print e
98
121
99 
    return msg, result
 122 
    return msg, result
Line 113... Line 136...
113 
    worksheet.write(row, 0, 'Category', boldStyle)
 136 
    worksheet.write(row, 0, 'Category', boldStyle)
114 
    worksheet.write(row, 1, 'Brand', boldStyle)
 137 
    worksheet.write(row, 1, 'Brand', boldStyle)
115 
    worksheet.write(row, 2, 'Model Name', boldStyle)
 138 
    worksheet.write(row, 2, 'Model Name', boldStyle)
116 
    worksheet.write(row, 3, 'Model Number', boldStyle)
 139 
    worksheet.write(row, 3, 'Model Number', boldStyle)
117 
    worksheet.write(row, 4, 'Color', boldStyle)
 140 
    worksheet.write(row, 4, 'Color', boldStyle)
- 
 
 141 
    worksheet.write(row, 5, 'Source', boldStyle)
118 
    worksheet.write(row, 5, 'Quantity', boldStyle)
 142 
    worksheet.write(row, 6, 'Quantity', boldStyle)
119 
    worksheet.write(row, 6, 'Value', boldStyle)
 143 
    worksheet.write(row, 7, 'Value', boldStyle)
120
144
121 
    row = 2
 145 
    row = 2
122 
    grossTotal = 0
 146 
    grossTotal = 0
123 
    grossQuantity = 0
 147 
    grossQuantity = 0
124
148
125 
    for r in result:
 149 
    for r in result:
126 
        #(parent, category, brand, model_name, model_number, color) = r[0:6]
 150 
        #(parent, category, brand, model_name, model_number, color) = r[0:6]
127 
        #dayofmonth = r[8]
 151 
        #dayofmonth = r[8]
128 
        for data in r :
 152 
        for data in r :
129 
            if column == 6 :
 153 
            if column == 7 :
130 
                grossTotal += data
 154 
                grossTotal += data
131 
            if column == 5 :
 155 
            if column == 6 :
132 
                grossQuantity += data
 156 
                grossQuantity += data
133 
            worksheet.write(row, column, str(data))
 157 
            worksheet.write(row, column, str(data))
134 
            column += 1
 158 
            column += 1
135 
        column = 0
 159 
        column = 0
136 
        row += 1
 160 
        row += 1
137
161
138 
    worksheet.write_merge(1, 1, 0, 4, 'Total')
 162 
    worksheet.write_merge(1, 1, 0, 4, 'Total')
139 
    worksheet.write(1, 5, str(grossQuantity), boldStyle)
 163 
    worksheet.write(1, 6, str(grossQuantity), boldStyle)
140 
    worksheet.write(1, 6, str(grossTotal), boldStyle)
 164 
    worksheet.write(1, 7, str(grossTotal), boldStyle)
141 
    workbook.save(TMP_FILE)
 165 
    workbook.save(TMP_FILE)
142 
 
 166 
 
143 
def sendmail(message):
 167 
def sendmail(message):
144 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
 168 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
145 
    mailServer.ehlo()
 169 
    mailServer.ehlo()
Line 168... Line 192...
168 
    message, result = getProductSaleData()
 192 
    message, result = getProductSaleData()
169 
    createXlsReport(result)
 193 
    createXlsReport(result)
170 
    sendmail(message)
 194 
    sendmail(message)
171 
 
 195 
 
172 
if __name__ == '__main__':
 196 
if __name__ == '__main__':
173 
    main()
 - 
 
174 

            197
            
    main()

          

          

            -
            
 

            198