Subversion Repositories SmartDukaan

Rev

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

Rev 6713 Rev 8207
Line 38... Line 38...
38 
def closeConnection(conn):
 38 
def closeConnection(conn):
39 
    conn.close()
 39 
    conn.close()
40 
 
 40 
 
41 
def getProductSaleData():
 41 
def getProductSaleData():
42 
    selectSql = '''SELECT i.parent_category, i.brand, IFNULL(i.model_name, ''),
 42 
    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
 43 
                    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)
44 
                    FROM sales s join item i on (i.id = s.item_id)
45 
                    JOIN datedim d on (d.date_id = s.date_id)
45 
                    JOIN datedim d on (d.date_id = s.date_id)
46 
                    JOIN orderstatus os on (s.status = os.status)
46 
                    JOIN orderstatus os on (s.status = os.status)
47 
                    WHERE d.fulldate in (DATE_SUB(curdate(), INTERVAL 1 DAY))
47 
                    WHERE d.fulldate in (DATE_SUB(curdate(), INTERVAL 1 DAY))
48 
                    AND os.statusGroup in ('Delivered', 'In process', 'Refunded', 'Return in process', 'Unused')
48 
                    AND os.statusGroup in ('Delivered', 'In process', 'Refunded', 'Return in process', 'Unused')
49 
                    AND i.parent_category = 'Mobile Accessories'
49 
                    AND i.parent_category = 'Mobile Accessories'
50 
                    AND os.statusSubGroup not in ('Cancellation pending')
50 
                    AND os.statusSubGroup not in ('Cancellation pending')
51 
                    GROUP BY i.parent_category, i.category, i.brand, IFNULL(i.model_name, ''), IFNULL(i.model_number, ''), i.color
 51 
                    GROUP BY i.parent_category, i.category, i.brand, IFNULL(i.model_name, ''), IFNULL(i.model_number, ''), i.color, s.source
52 
                    ORDER BY i.parent_category, i.brand;
 52 
                    ORDER BY i.parent_category, i.brand;
53 
                '''
 53 
                '''
54
54
55 
    conn = getDbConnection()
 55 
    conn = getDbConnection()
56 
    monthdatesmap = {}
 56 
    monthdatesmap = {}
Line 62... Line 62...
62 
        # Fetch source id.
 62 
        # Fetch source id.
63 
        cursor.execute(selectSql)
 63 
        cursor.execute(selectSql)
64 
        result = cursor.fetchall()
 64 
        result = cursor.fetchall()
65 
        msg =   """\
 65 
        msg =   """\
66 
                    <html>
 66 
                    <html>
67 
                    <body>\n<table border="1">
 67 
                    <body>\n<h3>Website  ======WebsiteSale====== </h3><h3>Amazon  ======AmazonSale====== </h3>\n<table border="1">
68 
                    \n<thead>\n
 68 
                    \n<thead>\n
69 
                    <th>Category\n</th>
 69 
                    <th>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>Source\n</th>
74 
                    <th>Quantity\n</th>
 75 
                    <th>Quantity\n</th>
75 
                    <th>Value\n</th>
 76 
                    <th>Value\n</th>
76 
                    \n</thead>
 77 
                    \n</thead>
77 
                """
78 
                """
78 
        column = 0
 79 
        column = 0
79 
        grossTotal = 0
 80 
        grossTotal = 0
80 
        grossQuantity = 0
 81 
        grossQuantity = 0
- 
 
 82 
        websiteSale = 0
- 
 
 83 
        amazonSale = 0
- 
 
 84 
        websiteQty = 0
- 
 
 85 
        amazonQty = 0
81 
        msg = msg + '<tr><td colspan="5"><b>Total</b></td><td>======QuantityToBeReplaced======</td><td>======ValueToBeReplaced======</td></tr>'
 86 
        msg = msg + '<tr><td colspan="5"><b>Total</b></td><td>======QuantityToBeReplaced======</td><td>======ValueToBeReplaced======</td></tr>'
82 
        for r in result:
 87 
        for r in result:
83 
            msg = msg + '<tr>'
 88 
            msg = msg + '<tr>'
- 
 
 89 
	    source = 1
84 
            for data in r:
 90 
            for data in r:
85 
                if column == 6 :
 91 
                if column == 7 :
86 
                    grossTotal += data
 92 
                    grossTotal += data
- 
 
 93 
		    if source == 1:
- 
 
 94 
			websiteSale += data
- 
 
 95 
		    if source == 3:
- 
 
 96 
			amazonSale += data
87 
                if column == 5 :
 97 
                if column == 6 :
88 
                    grossQuantity += data
 98 
                    grossQuantity += data
- 
 
 99 
		    if source == 1:
- 
 
 100 
			websiteQty += data
- 
 
 101 
		    if source == 3:
- 
 
 102 
			amazonQty += data
- 
 
 103 
		if column == 5 :
- 
 
 104 
		    source = data
89 
                msg = msg + '<td>' + str(data) + '</td>'
 105 
                msg = msg + '<td>' + str(data) + '</td>'
90 
                column += 1
 106 
                column += 1
91 
            column = 0
 107 
            column = 0
92 
            msg = msg + '</tr>'
 108 
            msg = msg + '</tr>'
- 
 
 109 
 
93 
        msg = msg + '</table>\n</body>\n</html>'
 110 
        msg = msg + '</table>\n</body>\n</html>'
94 
        msg = msg.replace('======QuantityToBeReplaced======', str(grossQuantity))
 111 
        msg = msg.replace('======QuantityToBeReplaced======', str(grossQuantity))
95 
        msg = msg.replace('======ValueToBeReplaced======', str(grossTotal))
 112 
        msg = msg.replace('======ValueToBeReplaced======', str(grossTotal))
- 
 
 113 
        #msg = msg.replace('======WebsiteQty=====', str(websiteQty))
- 
 
 114 
        #msg = msg.replace('======AmazonQty=====', str(amazonQty))
- 
 
 115 
        msg = msg.replace('======WebsiteSale======', "Sale - " + str(websiteSale) + "    Quantity - " + str(websiteQty))
- 
 
 116 
        msg = msg.replace('======AmazonSale======', "Sale - " + str(amazonSale) + "    Quantity - " + str(amazonQty))
96 
    except Exception as e:
 117 
    except Exception as e:
97 
      print "Error: unable to fetch data"
 118 
      print "Error: unable to fetch data"
98 
      print e
 119 
      print e
99
120
100 
    return msg, result
 121 
    return msg, result
Line 114... Line 135...
114 
    worksheet.write(row, 0, 'Category', boldStyle)
 135 
    worksheet.write(row, 0, 'Category', boldStyle)
115 
    worksheet.write(row, 1, 'Brand', boldStyle)
 136 
    worksheet.write(row, 1, 'Brand', boldStyle)
116 
    worksheet.write(row, 2, 'Model Name', boldStyle)
 137 
    worksheet.write(row, 2, 'Model Name', boldStyle)
117 
    worksheet.write(row, 3, 'Model Number', boldStyle)
 138 
    worksheet.write(row, 3, 'Model Number', boldStyle)
118 
    worksheet.write(row, 4, 'Color', boldStyle)
 139 
    worksheet.write(row, 4, 'Color', boldStyle)
- 
 
 140 
    worksheet.write(row, 5, 'Source', boldStyle)
119 
    worksheet.write(row, 5, 'Quantity', boldStyle)
 141 
    worksheet.write(row, 6, 'Quantity', boldStyle)
120 
    worksheet.write(row, 6, 'Value', boldStyle)
 142 
    worksheet.write(row, 7, 'Value', boldStyle)
121
143
122 
    row = 2
 144 
    row = 2
123 
    grossTotal = 0
 145 
    grossTotal = 0
124 
    grossQuantity = 0
 146 
    grossQuantity = 0
125
147
126 
    for r in result:
 148 
    for r in result:
127 
        #(parent, category, brand, model_name, model_number, color) = r[0:6]
 149 
        #(parent, category, brand, model_name, model_number, color) = r[0:6]
128 
        #dayofmonth = r[8]
 150 
        #dayofmonth = r[8]
129 
        for data in r :
 151 
        for data in r :
130 
            if column == 6 :
 152 
            if column == 7 :
131 
                grossTotal += data
 153 
                grossTotal += data
132 
            if column == 5 :
 154 
            if column == 6 :
133 
                grossQuantity += data
 155 
                grossQuantity += data
134 
            worksheet.write(row, column, str(data))
 156 
            worksheet.write(row, column, str(data))
135 
            column += 1
 157 
            column += 1
136 
        column = 0
 158 
        column = 0
137 
        row += 1
 159 
        row += 1
138
160 
 
139 
    worksheet.write_merge(1, 1, 0, 4, 'Total')
 161 
    worksheet.write_merge(1, 1, 0, 4, 'Total')
140 
    worksheet.write(1, 5, str(grossQuantity), boldStyle)
 162 
    worksheet.write(1, 6, str(grossQuantity), boldStyle)
141 
    worksheet.write(1, 6, str(grossTotal), boldStyle)
 163 
    worksheet.write(1, 7, str(grossTotal), boldStyle)
142 
    workbook.save(TMP_FILE)
 164 
    workbook.save(TMP_FILE)
143 
 
 165 
 
144 
def sendmail(message):
 166 
def sendmail(message):
145 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
 167 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
146 
    mailServer.ehlo()
 168 
    mailServer.ehlo()
Line 169... Line 191...
169 
    message, result = getProductSaleData()
 191 
    message, result = getProductSaleData()
170 
    createXlsReport(result)
 192 
    createXlsReport(result)
171 
    sendmail(message)
 193 
    sendmail(message)
172 
 
 194 
 
173 
if __name__ == '__main__':
 195 
if __name__ == '__main__':
174 
    main()
 - 
 
175 

            196
            
    main()

          

          

            -
            
 

            197