Spaces:
Runtime error
Runtime error
| 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() | |