Spaces:
Runtime error
Runtime error
File size: 6,943 Bytes
e3cb0b8 4177035 e3cb0b8 9e47b63 e3cb0b8 9ce1e7e 9e47b63 e3cb0b8 | 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 136 137 138 139 140 141 142 | import os, mysql.connector, streamlit as st
from datetime import datetime
def mysql_conn():
password= os.getenv("MYSQL_PWD"),
conn = mysql.connector.connect(
host="mysql-omni-omni.b.aivencloud.com",
port="21906",
user="avnadmin",
password= password,
database = "defaultdb"
)
cursor = conn.cursor()
return cursor, conn
def mysql_check():
cursor, conn = mysql_conn()
cursor.execute("SHOW TABLES;")
# cursor.execute("DESCRIBE receipt_headers_allvarchar;")
cursor.close()
conn.close()
def mysql_create_receipt_table():
cursor, conn = mysql_conn()
cursor.execute("""
CREATE TABLE IF NOT EXISTS receipt_headers (
receipt_id INT AUTO_INCREMENT PRIMARY KEY,
store_name VARCHAR(255), slogan VARCHAR(255), address VARCHAR(255), store_manager VARCHAR(255), phone_number VARCHAR(50),
transaction_id VARCHAR(255), date DATE, time TIME, cashier VARCHAR(255), subtotal DECIMAL(10,2),
sales_tax DECIMAL(10,2), total DECIMAL(10,2), gift_card DECIMAL(10,2), charged_amount DECIMAL(10,2), card_type VARCHAR(50),
auth_code VARCHAR(50), chip_read VARCHAR(50), aid VARCHAR(50), issuer VARCHAR(255), policy_id VARCHAR(50),
expiration_date DATE, survey_message TEXT, survey_website VARCHAR(255), user_id VARCHAR(255), password VARCHAR(255), eligibility_note TEXT )
""")
# Create line_items table
cursor.execute("""
CREATE TABLE IF NOT EXISTS line_items (
line_item_id INT AUTO_INCREMENT PRIMARY KEY,
receipt_id INT, sku VARCHAR(255), description VARCHAR(255), details TEXT, price DECIMAL(10,2),
FOREIGN KEY (receipt_id) REFERENCES receipt_headers(receipt_id) )
""")
conn.commit()
cursor.close()
conn.close()
def mysql_create_receipt_table_allvarchar():
cursor, conn = mysql_conn()
cursor.execute("""
CREATE TABLE IF NOT EXISTS receipt_headers_allvarchar (
receipt_id INT AUTO_INCREMENT PRIMARY KEY,
store_name VARCHAR(255), slogan VARCHAR(255), address VARCHAR(255), store_manager VARCHAR(255), phone_number VARCHAR(50),
transaction_id VARCHAR(255), date VARCHAR(50), time VARCHAR(50), cashier VARCHAR(255), subtotal DECIMAL(10,2),
sales_tax DECIMAL(10,2), total DECIMAL(10,2), gift_card DECIMAL(10,2), charged_amount DECIMAL(10,2), card_type VARCHAR(50),
auth_code VARCHAR(50), chip_read VARCHAR(50), aid VARCHAR(50), issuer VARCHAR(255), policy_id VARCHAR(50),
expiration_date VARCHAR(50), survey_message TEXT, survey_website VARCHAR(255), user_id VARCHAR(255), password VARCHAR(255), eligibility_note TEXT )
""")
# Create line_items table
cursor.execute("""
CREATE TABLE IF NOT EXISTS line_items_allvarchar (
line_item_id INT AUTO_INCREMENT PRIMARY KEY,
receipt_id INT, sku VARCHAR(255), description VARCHAR(255), details TEXT, price DECIMAL(10,2),
FOREIGN KEY (receipt_id) REFERENCES receipt_headers(receipt_id))
""")
conn.commit()
cursor.close()
conn.close()
def mysql_insert_receipt(receipt_data):
cursor, conn = mysql_conn()
# Insert into receipt_headers
header_insert_query = """
INSERT INTO receipt_headers (store_name, slogan, address, store_manager, phone_number, transaction_id, date, time, cashier, subtotal, sales_tax, total, gift_card, charged_amount, card_type, auth_code, chip_read, aid, issuer, policy_id, expiration_date, survey_message, survey_website, user_id, password, eligibility_note)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
"""
header_info = receipt_data['receipt_headers']
line_items = receipt_data['line_items']
# Format date, time, and expiration_date
formatted_date = datetime.strptime(header_info['date'], '%m/%d/%Y').strftime('%Y-%m-%d')
formatted_time = datetime.strptime(header_info['time'], '%I:%M %p').strftime('%H:%M:%S')
# formatted_expiration_date = datetime.strptime(header_info['expiration_date'], '%m/%d/%Y').strftime('%Y-%m-%d')
formatted_expiration_date = datetime.strptime(header_info['expiration_date'], '%m/%d/%Y').strftime('%Y-%m-%d') if header_info['expiration_date'] else '01/01/2023'
formatted_expiration_date = datetime.strptime(header_info['expiration_date'], '%m/%d/%Y') if header_info['expiration_date'] else datetime.strptime('01/01/1000', '%m/%d/%Y')
# Prepare header values
header_values = (
header_info['store_name'],
header_info['slogan'],
header_info['address'],
header_info['store_manager'],
header_info['phone_number'],
header_info['transaction_id'],
formatted_date,
formatted_time,
header_info['cashier'],
header_info['subtotal'],
header_info['sales_tax'],
header_info['total'],
header_info['gift_card'],
header_info['charged_amount'],
header_info['card_type'],
header_info['auth_code'],
header_info['chip_read'],
header_info['aid'],
header_info['issuer'],
header_info['policy_id'],
formatted_expiration_date,
header_info['survey_message'],
header_info['survey_website'],
header_info['user_id'],
header_info['password'],
header_info['eligibility_note']
)
# Insert header values
cursor.execute(header_insert_query, header_values)
receipt_id = cursor.lastrowid
# Prepare and insert line items
line_item_insert_query = """
INSERT INTO line_items (receipt_id, sku, description, details, price)
VALUES (%s, %s, %s, %s, %s)
"""
for item in line_items:
price = float(item['price'])
line_item_values = (
receipt_id,
item['sku'],
item['description'],
item.get('details', ''),
price
)
cursor.execute(line_item_insert_query, line_item_values)
# Commit and close the connection
conn.commit()
cursor.close()
conn.close()
|