|
| 1 | +import pandas as pd |
| 2 | +from sqlalchemy import create_engine |
| 3 | +from xlwings import Workbook, Range |
| 4 | +import os |
| 5 | + |
| 6 | + |
| 7 | +def summarize_sales(): |
| 8 | + """ |
| 9 | + Retrieve the account number and date ranges from the Excel sheet |
| 10 | + Read in the data from the sqlite database, then manipulate and return it to excel |
| 11 | + """ |
| 12 | + # Make a connection to the calling Excel file |
| 13 | + wb = Workbook.caller() |
| 14 | + |
| 15 | + # Connect to sqlite db |
| 16 | + db_file = os.path.join(os.path.dirname(wb.fullname), 'pbp_proj.db') |
| 17 | + engine = create_engine(r"sqlite:///{}".format(db_file)) |
| 18 | + |
| 19 | + # Retrieve the account number from the excel sheet as an int |
| 20 | + account = Range('B2').options(numbers=int).value |
| 21 | + |
| 22 | + # Get our dates - in real life would need to do some error checking to ensure |
| 23 | + # the correct format |
| 24 | + start_date = Range('D2').value |
| 25 | + end_date = Range('F2').value |
| 26 | + |
| 27 | + # Clear existing data |
| 28 | + Range('A5:F100').clear_contents() |
| 29 | + |
| 30 | + # Create SQL query |
| 31 | + sql = 'SELECT * from sales WHERE account="{}" AND date BETWEEN "{}" AND "{}"'.format(account, start_date, end_date) |
| 32 | + |
| 33 | + # Read query directly into a dataframe |
| 34 | + sales_data = pd.read_sql(sql, engine) |
| 35 | + |
| 36 | + # Analyze the data however we want |
| 37 | + summary = sales_data.groupby(["sku"])["quantity", "ext-price"].sum() |
| 38 | + |
| 39 | + total_sales = sales_data["ext-price"].sum() |
| 40 | + |
| 41 | + # Output the results |
| 42 | + if summary.empty: |
| 43 | + Range('A5').value = "No Data for account {}".format(account) |
| 44 | + else: |
| 45 | + Range('A5').options(index=True).value = summary |
| 46 | + Range('E5').value = "Total Sales" |
| 47 | + Range('F5').value = total_sales |
0 commit comments