Spaces:
Sleeping
Sleeping
File size: 5,233 Bytes
aac1bde 3aab50a aac1bde 3aab50a aac1bde 4ec3e1e aac1bde 4ec3e1e aac1bde |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 |
import mysql.connector
from decimal import Decimal
import pandas as pd
from prophet import Prophet
import math
# Define the connection parameters
host = "159.138.104.192"
user = "storemate_ml"
password = "bTgZd77VpD^o4Ai6Dw9xs9"
database = "lite_version"
def forecast(monthly_sales):
# Prepare the data for Prophet
monthly_sales.rename(columns={'transaction_date': 'ds', 'sell_qty': 'y'}, inplace=True)
# Initialize and fit the Prophet model
model = Prophet()
model.fit(monthly_sales)
# Make a future dataframe for the next month
future = model.make_future_dataframe(periods=1, freq='M')
forecast = model.predict(future)
# Extract the forecasted sales for the next month
forecasted_sales = forecast[['ds', 'yhat']].tail(2)
# Combine historical and forecasted data
combined_sales = pd.concat([monthly_sales, forecasted_sales[-1:]], ignore_index=True)
original_forecasted_value = combined_sales.tail(1)
rounded_value = combined_sales.tail(1)
rounded_value['yhat'] = rounded_value['yhat'].apply(lambda x: max(0, math.ceil(x)))
return combined_sales,original_forecasted_value,rounded_value
def get_data(b_id,product_name):
# Create a connection to the MySQL server
try:
# Create a connection to the MySQL server
connection = mysql.connector.connect(
host=host,
user=user,
password=password,
database=database
)
if connection.is_connected():
print("Connected to MySQL database")
# Create a cursor object for executing SQL queries
cursor = connection.cursor()
# Define the SQL SELECT query
sql_query = f"""
SELECT
b.id AS business_id,
b.name AS business_name,
p.name AS product_name,
p.type AS product_type,
c1.name AS category_name,
br.name AS brand_name,
p.image AS product_image,
pv.name AS product_variation,
v.name AS variation_name,
v.sub_sku,
c.name AS customer,
c.contact_id,
t.id AS transaction_id,
t.invoice_no,
t.transaction_date AS transaction_date,
(transaction_sell_lines.quantity - transaction_sell_lines.quantity_returned) AS sell_qty,
u.short_name AS unit,
transaction_sell_lines.unit_price_inc_tax,
transaction_sell_lines.unit_price_before_discount
FROM
transaction_sell_lines
INNER JOIN transactions AS t ON transaction_sell_lines.transaction_id = t.id
INNER JOIN variations AS v ON transaction_sell_lines.variation_id = v.id
LEFT JOIN transaction_sell_lines_purchase_lines AS tspl ON transaction_sell_lines.id = tspl.sell_line_id
LEFT JOIN purchase_lines AS pl ON tspl.purchase_line_id = pl.id
INNER JOIN product_variations AS pv ON v.product_variation_id = pv.id
INNER JOIN contacts AS c ON t.contact_id = c.id
INNER JOIN products AS p ON pv.product_id = p.id
LEFT JOIN business AS b ON p.business_id = b.id
LEFT JOIN categories AS c1 ON p.category_id = c1.id
LEFT JOIN brands AS br ON p.brand_id = br.id
LEFT JOIN tax_rates ON transaction_sell_lines.tax_id = tax_rates.id
LEFT JOIN units AS u ON p.unit_id = u.id
LEFT JOIN transaction_payments AS tp ON tp.transaction_id = t.id
LEFT JOIN transaction_sell_lines AS tsl ON transaction_sell_lines.parent_sell_line_id = tsl.id
WHERE
t.type = 'sell'
AND t.status = 'final'
AND t.business_id = {b_id}
GROUP BY
b.id, transaction_sell_lines.id;
"""
# Execute the SQL query
cursor.execute(sql_query)
# Fetch all the rows as a list of tuples
results = cursor.fetchall()
results = [tuple(
float(val) if isinstance(val, Decimal) else val for val in row
) for row in results]
#print(results)
# Display the results
#for row in results:
#print(row) # You can process the results as needed
# Close the cursor and connection
cursor.close()
connection.close()
# Create a DataFrame
columns = [
"business_id", "business_name", "product_name", "product_type",
"category_name", "brand_name", "product_image", "product_variation",
"variation_name", "sub_sku", "customer", "contact_id",
"transaction_id", "invoice_no", "transaction_date", "sell_qty",
"unit", "unit_price_inc_tax", "unit_price_before_discount"
]
df = pd.DataFrame(results, columns=columns)
return df,"done"
except mysql.connector.Error as e:
return e,"error"
|