MySQL Data Analysis Script | Iot và ứng dụng | Học viện Công nghệ Bưu chính Viễn thông

import mysql.connector
from datetime import datetime, timedelta
conn = mysql.connector.connect(   host="localhost",   user="root",   password="",   database="btlttql" )
cursor = conn.cursor(dictionary=True) # Lấ! y ngày đấ% u tiên có đơn hàng
cursor.execute("SELECT MIN(DATE(created_at)) AS first_date FROM orders")
first_date = cursor.fetchone()["first_date"] if not first_date:
  print("Khong co don hang")   exit()
today = datetime.today().date()
yesterday = today - timedelta(days=1) current_date = first_date
while current_date <= yesterday:
  report_date_str = current_date.strftime('%Y-%m-%d')
  print(f"Dang xu ly: {report_date_str}")   # Truy vấ! n các chỉ E số!
  cursor.execute("SELECT COUNT(*) as total_views FROM page_views WHERE
DATE(viewed_at) = %s", (report_date_str,))
  total_views = cursor.fetchone()["total_views"]
  cursor.execute("SELECT COUNT(*) as total_new_users FROM users WHERE
DATE(created_at) = %s", (report_date_str,))
  total_new_users = cursor.fetchone()["total_new_users"]
  cursor.execute("SELECT COUNT(*) as total_orders FROM orders WHERE
DATE(created_at) = %s", (report_date_str,))
  total_orders = cursor.fetchone()["total_orders"]
  cursor.execute("SELECT COUNT(*) as completed_orders FROM orders WHERE
DATE(created_at) = %s AND status = 'completed'", (report_date_str,))
  completed_orders = cursor.fetchone()["completed_orders"]
  cursor.execute("SELECT COUNT(*) as pending_orders FROM orders WHERE
DATE(created_at) = %s AND status = 'pending'", (report_date_str,))
  pending_orders = cursor.fetchone()[ ] "pending_orders"
  cursor.execute("SELECT COUNT(*) as processing_orders FROM orders
WHERE DATE(created_at) = %s AND status = 'processing'", (report_date_str,))
  processing_orders = cursor.fetchone()["processing_orders"]  
  cursor.execute("SELECT COUNT(*) as canceled_orders FROM orders WHERE
DATE(created_at) = %s AND status = 'canceled'", (report_date_str,))
  canceled_orders = cursor.fetchone()["canceled_orders"]   cursor.execute("""
    SELECT COUNT(DISTINCT user_id) as user_visitor
    FROM page_views
    WHERE DATE(viewed_at) = %s AND is_authenticated = 1
  """, (report_date_str,))
  user_visitor = cursor.fetchone()["user_visitor"]   cursor.execute("""
    SELECT SUM(final_amount) as total_sales     FROM orders
    WHERE DATE(created_at) = %s AND status = 'completed'
  """, (report_date_str,))
  total_sales = cursor.fetchone()["total_sales"] or 0   cursor.execute("""
    SELECT SUM(oi.quantity * p.import_price) as total_cost     FROM orders o
    JOIN order_items oi ON o.id = oi.order_id
    JOIN products p ON oi.product_id = p.id
    WHERE DATE(o.created_at) = %s AND o.status = 'completed'
  """, (report_date_str,))
  total_cost = cursor.fetchone()["total_cost"] or 0
  profit = total_sales - total_cost
  conversion_rate = round(total_orders / user_visitor, 2) if user_visitor else 0
  avg_order_value = round(total_sales / total_orders, 2) if total_orders else 0
  avg_profit_per_order = round(profit / total_orders, 2) if total_orders else 0
  cancel_rate = round(canceled_orders / total_orders, 2) if total_orders else 0   # Lấ! y sa En phấE m top doanh thu   cursor.execute("""
    SELECT p.name AS product_name, SUM(oi.quantity * oi.price) AS revenue
    FROM order_items oi
    JOIN orders o ON oi.order_id = o.id
    JOIN products p ON oi.product_id = p.id
    WHERE DATE(o.created_at) = %s AND o.status = 'completed'     GROUP BY p.id
    ORDER BY revenue DESC     LIMIT 1
  """, (report_date_str,))
  result = cursor.fetchone()
  top_product_name = result["product_name"] if result else None
  top_product_sales = result["revenue"] if result else 0   # Ghi vào ba Eng (cập nhật nê! u đã có)   cursor.execute("""
    INSERT INTO reports_business (
      report_date, total_views, total_new_users, total_orders, completed_orders,
      pending_orders,processing_orders, canceled_orders,
user_visitor, total_sales, profit,
      conversion_rate, avg_order_value, avg_profit_per_order, cancel_rate,
      top_product_name, top_product_sales     ) VALUES (
      %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,%s     )
    ON DUPLICATE KEY UPDATE
      total_views = VALUES(total_views),
      total_new_users = VALUES(total_new_users),
      total_orders = VALUES(total_orders),
      completed_orders = VALUES(completed_orders),
      pending_orders = VALUES(pending_orders),
      processing_orders = VALUES(processing_orders),
      canceled_orders = VALUES(canceled_orders),
      user_visitor = VALUES(user_visitor),
      total_sales = VALUES(total_sales),
      profit = VALUES(profit),
      conversion_rate = VALUES(conversion_rate),
      avg_order_value = VALUES(avg_order_value),
      avg_profit_per_order = VALUES(avg_profit_per_order),
      cancel_rate = VALUES(cancel_rate),
      top_product_name = VALUES(top_product_name),
      top_product_sales = VALUES(top_product_sales)   """, (
    report_date_str, total_views, total_new_users, total_orders, completed_orders,
    pending_orders,processing_orders, canceled_orders, user_visitor, total_sales, profit,
    conversion_rate, avg_order_value, avg_profit_per_order, cancel_rate,
    top_product_name, top_product_sales   ))   conn.commit()
  current_date += timedelta(days=1) print("Hoan Thanh") cursor.close() conn.close()