Subversion Repositories SmartDukaan

Rev

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

Rev 5495 Rev 5506
Line 49... Line 49...
49 
                          FROM lineitem l
49 
                          FROM lineitem l
50 
                          JOIN purchaseorder p ON l.purchaseOrder_id = p.id
50 
                          JOIN purchaseorder p ON l.purchaseOrder_id = p.id
51 
                          JOIN supplier s ON p.supplierId = s.id
51 
                          JOIN supplier s ON p.supplierId = s.id
52 
                          WHERE l.createdAt BETWEEN \'''' + fromDate + '''\' AND \'''' + toDate + '''\' GROUP BY s.name, l.itemId;'''
 52 
                          WHERE l.createdAt BETWEEN \'''' + fromDate + '''\' AND \'''' + toDate + '''\' GROUP BY s.name, l.itemId;'''
53 
    conn = getDbConnection()
 53 
    conn = getDbConnection()
54 
    data = {}
 54 
    #data = {}
55 
    try:
 55 
    try:
56 
        # prepare a cursor object using cursor() method
 56 
        # prepare a cursor object using cursor() method
57 
        cursor = conn.cursor()
 57 
        cursor = conn.cursor()
58 
        # Execute the SQL command
 58 
        # Execute the SQL command
59 
        # Fetch source id.
 59 
        # Fetch source id.
60 
        cursor.execute(selectSql)
 60 
        cursor.execute(selectSql)
61 
        result = cursor.fetchall()
 61 
        result = cursor.fetchall()
62 
        for r in result:
 - 
 
63 
            supplier = r[0:1][0]
 - 
 
64 
            if data.get(supplier):
 - 
 
65 
                data.get(supplier).append(r[1:4])
 - 
 
66 
            else:
 62
67 
                data[supplier] = [r[1:4]]
 - 
 
68 
    except Exception as e:
 63 
    except Exception as e:
69 
      print "Error: unable to fetch data"
 64 
        print "Error: unable to fetch data"
70 
      print e
 65 
        print e
71
66
72 
    return data
 67 
    return result
73 
 
 68 
 
74 
def createXlsReport(wb, data, sheetNumber):
 69 
def createXlsReport(wb, data, sheetNumber):
75 
    if sheetNumber == 1:
 70 
    if sheetNumber == 1:
76 
        sheetName = "Yesterday"
 71 
        sheetName = "Yesterday"
77 
    if sheetNumber == 2:
 72 
    if sheetNumber == 2:
Line 80... Line 75...
80 
    boldStyle = XFStyle()
 75 
    boldStyle = XFStyle()
81 
    f = Font()
 76 
    f = Font()
82 
    f.bold = True
 77 
    f.bold = True
83 
    boldStyle.font = f
 78 
    boldStyle.font = f
84
79
85 
    row = -3
 80 
    row = 0
86 
    for supplier in data.keys():
 - 
 
87 
        row += 3
 81
88 
        worksheet.write(row, 0, supplier, boldStyle)
 82 
    worksheet.write(row, 0, "VENDOR", boldStyle)
89 
        row += 2
 - 
 
90 
        worksheet.write(row, 0, "PRODUCT", boldStyle)
 83 
    worksheet.write(row, 1, "PRODUCT", boldStyle)
91 
        worksheet.write(row, 1, "QTY REQUIRED", boldStyle)
 84 
    worksheet.write(row, 2, "QTY REQUIRED", boldStyle)
92 
        worksheet.write(row, 2, "QTY UNFULFILLED", boldStyle)
85 
    worksheet.write(row, 3, "QTY UNFULFILLED", boldStyle)
- 
 
 86
93 
        lineitems = data.get(supplier)
 87 
    row += 2
- 
 
 88
94 
        for lineitem in lineitems:
 89 
    for datum in data:
95 
            row += 1
 90 
        worksheet.write(row, 0, datum[0])
96 
            worksheet.write(row, 0, lineitem[0])
 91 
        worksheet.write(row, 1, datum[1])
97 
            worksheet.write(row, 1, lineitem[1])
 92 
        worksheet.write(row, 2, datum[2])
98 
            worksheet.write(row, 2, lineitem[2])
 93 
        worksheet.write(row, 3, datum[3])
- 
 
 94 
        row += 1
99
95
100 
    wb.save(TMP_FILE)
 96 
    wb.save(TMP_FILE)
101 
 
 97 
 
102 
def sendmail():
 98 
def sendmail():
103 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
 99 
    mailServer = smtplib.SMTP(SMTP_SERVER, SMTP_PORT)
104 
    mailServer.ehlo()
 100 
    mailServer.ehlo()
Line 127... Line 123...
127 
    timeNow = datetime.datetime.now()
 123 
    timeNow = datetime.datetime.now()
128 
    toDate = timeNow.strftime('%Y-%m-%d %H:%M:%S')
 124 
    toDate = timeNow.strftime('%Y-%m-%d %H:%M:%S')
129 
    fromDate = (timeNow - timedelta(days=1)).strftime('%Y-%m-%d %H:%M:%S')
 125 
    fromDate = (timeNow - timedelta(days=1)).strftime('%Y-%m-%d %H:%M:%S')
130 
    data = getProductSaleData(fromDate, toDate)
 126 
    data = getProductSaleData(fromDate, toDate)
131 
    createXlsReport(workbook, data, 1)
 127 
    createXlsReport(workbook, data, 1)
132
128 
 
133 
    fromDate = datetime.datetime(datetime.datetime.now().year, datetime.datetime.now().month, 1, 0, 0, 0, 0)
 129 
    fromDate = datetime.datetime(datetime.datetime.now().year, datetime.datetime.now().month, 1, 0, 0, 0, 0)
134 
    data = getProductSaleData(str(fromDate), str(toDate))
 130 
    data = getProductSaleData(str(fromDate), str(toDate))
135 
    createXlsReport(workbook, data, 2)
 131 
    createXlsReport(workbook, data, 2)
136
132
137 
    sendmail()
 133 
    sendmail()