Spaces:
Sleeping
Sleeping
File size: 4,646 Bytes
aac1bde 3aab50a aac1bde 3aab50a 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 133 134 135 |
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`,
`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 `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","product_variation","variation_name","sub_sku","customer","contact_id","transaction_id","invoice_no","transaction_date","sell_qty","unit","cost_price","selling_price"]
df = pd.DataFrame(results, columns=columns)
return df,"done"
except mysql.connector.Error as e:
return e,"error"
|