import os import re import mysql.connector import urllib.parse import uuid def scrape_payment_information(): # Database configuration from environment variables db_config = { 'user': os.getenv('DB_USER'), 'password': os.getenv('DB_PASSWORD'), 'host': os.getenv('DB_HOST'), 'database': os.getenv('DB_NAME'), 'port': int(os.getenv('DB_PORT', 3306)) } # Base directories success_directory = "/var/www/html/success_pages" payments_directory = "/var/www/html/payments" # Ensure payments directory exists if not os.path.exists(payments_directory): os.makedirs(payments_directory) print(f"Created base payments directory: {payments_directory}") try: # Connect to database conn = mysql.connector.connect(**db_config) cursor = conn.cursor(dictionary=True) print("Connected to database") # Get all campaign directories in success_pages campaign_dirs = [d for d in os.listdir(success_directory) if os.path.isdir(os.path.join(success_directory, d))] print(f"Found {len(campaign_dirs)} campaign directories to process") for campaign_code in campaign_dirs: campaign_success_dir = os.path.join(success_directory, campaign_code) campaign_payments_dir = os.path.join(payments_directory, campaign_code) # Create campaign directory in payments if needed if not os.path.exists(campaign_payments_dir): os.makedirs(campaign_payments_dir) print(f"Created directory for campaign: {campaign_code}") # Get campaign information cursor.execute(""" SELECT name, brand_id, influencer_id FROM tbl_campaigns WHERE code = %s LIMIT 1 """, (campaign_code,)) campaign_info = cursor.fetchone() if not campaign_info: print(f"Campaign {campaign_code} not found in database, skipping") continue campaign_name = campaign_info['name'] brand_id = campaign_info['brand_id'] influencer_id = campaign_info['influencer_id'] # Process HTML files in this campaign's success directory html_files = [f for f in os.listdir(campaign_success_dir) if f.endswith(".html")] print(f"Processing {len(html_files)} success pages for campaign {campaign_code}") for file_name in html_files: file_path = os.path.join(campaign_success_dir, file_name) order_uuid = file_name.replace('.html', '') payments_file_path = os.path.join(campaign_payments_dir, file_name) try: # Get URL and processed_at from tbl_tracker cursor.execute(""" SELECT url, processed_at FROM tbl_tracker WHERE uuid = %s LIMIT 1 """, (order_uuid,)) tracker_info = cursor.fetchone() if not tracker_info: print(f"UUID {order_uuid} not found in tbl_tracker, skipping") continue url = tracker_info['url'] time_payment = tracker_info['processed_at'] # Read the HTML file with open(file_path, 'r', encoding='utf-8', errors='replace') as file: html_content = file.read() # Find customer information first_name = "Unknown" last_name = "Unknown" # Look for the exact pattern in the content first_name_match = re.search(r'first_name%22%3A%22([^%"]+)', html_content) if first_name_match: first_name = urllib.parse.unquote(first_name_match.group(1)).replace('+', ' ') last_name_match = re.search(r'last_name%22%3A%22([^%]+)%22%2C%22', html_content) if last_name_match: last_name = urllib.parse.unquote(last_name_match.group(1)).replace('+', ' ') # If the exact pattern failed, try alternative patterns if first_name == "Unknown" or last_name == "Unknown": # Try looking directly in the HTML for billing details billing_first_name = re.search(r']*>Billing First name:\s*([^<]+)', html_content) if billing_first_name: first_name = billing_first_name.group(1).strip() billing_last_name = re.search(r']*>Billing Last name:\s*([^<]+)', html_content) if billing_last_name: last_name = billing_last_name.group(1).strip() # Look for products and prices products = [] # Find all product rows product_pattern = r'(.*?)' product_rows = re.findall(product_pattern, html_content, re.DOTALL) for row in product_rows: # Extract product name name_match = re.search(r'.*?]*>([^<]+)', row, re.DOTALL) product_name = "Unknown Product" if name_match: product_name = name_match.group(1).strip() # Extract quantity quantity_match = re.search(r'[^0-9]*([0-9]+)', row) quantity = 1 if quantity_match: try: quantity = int(quantity_match.group(1)) except: pass # Extract price price_match = re.search(r'.*?([0-9,.]+)', row, re.DOTALL) price = "0.00" if price_match: price_text = price_match.group(1).strip() # Remove any non-numeric characters except decimal point price = re.sub(r'[^0-9.]', '', price_text) # Add to products list products.append({ 'name': product_name, 'quantity': quantity, 'price': price }) # Write customer and product information to file with open(payments_file_path, 'w', encoding='utf-8') as payments_file: payments_file.write(f"Customer: {first_name} {last_name}\n\n") payments_file.write("Products:\n") for product in products: payments_file.write(f"- {product['name']} (x{product['quantity']}): {product['price']}\n") # Save data to tbl_sales_payments for product in products: for i in range(product['quantity']): # Create entry for each quantity record_uuid = str(uuid.uuid4()) cursor.execute(""" INSERT INTO tbl_sales_payments ( uuid, campaign_code, campaign_name, brand_id, influencer_id, first_name, last_name, product, price, url, time_payment ) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s ) """, ( record_uuid, campaign_code, campaign_name, brand_id, influencer_id, first_name, last_name, product['name'], product['price'], url, time_payment )) print(f"Processed payment information for {file_name} and added to database") except Exception as e: print(f"Error processing file {file_name}: {e}") # Commit all changes conn.commit() print("All database changes committed successfully") except mysql.connector.Error as err: print(f"Database error: {err}") if 'conn' in locals() and conn: conn.rollback() except Exception as e: print(f"Error: {e}") finally: if 'conn' in locals() and conn and conn.is_connected(): cursor.close() conn.close() print("Database connection closed") if __name__ == "__main__": scrape_payment_information()