$w.onReady(function () {
// import sqlite3
def create_db():
conn = sqlite3.connect('kerrigans_xl.db')
cursor = conn.cursor()
# Create staff table
cursor.execute('''
CREATE TABLE IF NOT EXISTS staff (
id INTEGER PRIMARY KEY,
username TEXT NOT NULL,
password TEXT NOT NULL,
role TEXT NOT NULL
);
''')
# Create stock table
cursor.execute('''
CREATE TABLE IF NOT EXISTS stock (
id INTEGER PRIMARY KEY,
product_name TEXT NOT NULL,
quantity INTEGER NOT NULL,
price REAL NOT NULL
);
''')
# Create sales table
cursor.execute('''
CREATE TABLE IF NOT EXISTS sales (
id INTEGER PRIMARY KEY,
product_id INTEGER NOT NULL,
quantity INTEGER NOT NULL,
total_price REAL NOT NULL,
date_time TEXT NOT NULL,
payment_method TEXT NOT NULL,
FOREIGN KEY (product_id) REFERENCES stock (id)
);
''')
# Create hourly sales record table
cursor.execute('''
CREATE TABLE IF NOT EXISTS hourly_sales (
id INTEGER PRIMARY KEY,
sales_date TEXT NOT NULL,
total_sales REAL NOT NULL,
hour INTEGER NOT NULL
);
''')
conn.commit()
conn.close()
# Create the database
create_db()
import hashlib
def create_staff_account(username, password, role='staff'):
conn = sqlite3.connect('kerrigans_xl.db')
cursor = conn.cursor()
hashed_password = hashlib.sha256(password.encode()).hexdigest() # Hash the password
cursor.execute('''
INSERT INTO staff (username, password, role)
VALUES (?, ?, ?)
''', (username, hashed_password, role))
conn.commit()
conn.close()
def staff_login(username, password):
conn = sqlite3.connect('kerrigans_xl.db')
cursor = conn.cursor()
hashed_password = hashlib.sha256(password.encode()).hexdigest() # Hash input password
cursor.execute('''
SELECT * FROM staff WHERE username = ? AND password = ?
''', (username, hashed_password))
staff = cursor.fetchone()
conn.close()
return staff
import datetime
def process_sale(product_id, quantity, payment_method):
conn = sqlite3.connect('kerrigans_xl.db')
cursor = conn.cursor()
# Get product info to calculate total price
cursor.execute('''
SELECT * FROM stock WHERE id = ?
''', (product_id,))
product = cursor.fetchone()
if product and product[2] >= quantity: # Check if stock is sufficient
total_price = product[3] * quantity # Calculate total price
new_quantity = product[2] - quantity # Update stock
# Record sale
cursor.execute('''
INSERT INTO sales (product_id, quantity, total_price, date_time, payment_method)
VALUES (?, ?, ?, ?, ?)
''', (product_id, quantity, total_price, datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'), payment_method))
# Update stock
cursor.execute('''
UPDATE stock SET quantity = ? WHERE id = ?
''', (new_quantity, product_id))
conn.commit()
else:
print("Not enough stock!")
conn.close()
# Example: Sell 2 of product with ID 1 via cash payment
process_sale(1, 2, 'card')
# Add new stock
def add_stock(product_name, quantity, price):
conn = sqlite3.connect('kerrigans_xl.db')
cursor = conn.cursor()
cursor.execute('''
INSERT INTO stock (product_name, quantity, price)
VALUES (?, ?, ?)
''', (product_name, quantity, price))
conn.commit()
conn.close()
# Stock take
def stock_take():
conn = sqlite3.connect('kerrigans_xl.db')
cursor = conn.cursor()
cursor.execute('SELECT * FROM stock')
stock_items = cursor.fetchall()
conn.close()
for item in stock_items:
print(f"Product: {item[1]}, Quantity: {item[2]}, Price: {item[3]}")
# Record hourly sales
def record_hourly_sales():
conn = sqlite3.connect('kerrigans_xl.db')
cursor = conn.cursor()
# Get sales for the past hour
current_time = datetime.datetime.now()
start_time = (current_time - datetime.timedelta(hours=1)).strftime('%Y-%m-%d %H:%M:%S')
cursor.execute('''
SELECT SUM(total_price) FROM sales WHERE date_time > ?
''', (start_time,))
total_sales = cursor.fetchone()[0] or 0 # If no sales, set to 0
# Insert hourly record
cursor.execute('''
INSERT INTO hourly_sales (sales_date, total_sales, hour)
VALUES (?, ?, ?)
''', (current_time.strftime('%Y-%m-%d'), total_sales, current_time.hour))
conn.commit()
conn.close()
import stripe
# Initialize Stripe with your API key
stripe.api_key = 'your_api_key'
def process_card_payment(amount, card_details):
try:
charge = stripe.Charge.create(
amount=int(amount * 100), # Amount in cents
currency="usd",
source=card_details, # Token from card details
description="Payment for Kerrigans XL"
)
print(f"Payment successful: {charge}")
return charge
except stripe.error.CardError as e:
print(f"Payment failed: {e}")
return None