Java Project – Sales Data Analysis

Program 1

// Sales Data Analysis

package src;

import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import javax.swing.*;
import javax.swing.table.DefaultTableModel;

public class SalesAnalyticsApp extends JFrame {
    Connection conn;

    public SalesAnalyticsApp() {
        // Set up JFrame
        setTitle("Sales Data Analytics");
        setSize(600, 400);
        setDefaultCloseOperation(EXIT_ON_CLOSE);
        setLocationRelativeTo(null);

        // Main Panel
        JPanel panel = new JPanel();
        panel.setLayout(new GridLayout(6, 1, 10, 10));

        // Buttons for menu
        JButton revenueBtn = new JButton("1. View Total Revenue");
        JButton topProductsBtn = new JButton("2. Top Selling Products");
        JButton monthlySalesBtn = new JButton("3. Monthly Sales Report");
        JButton cityWiseBtn = new JButton("4. City-wise Customer Count");
        JButton addCustomerBtn = new JButton("5. Add New Customer");
        JButton addProductBtn = new JButton("6. Add New Product");
        JButton addSaleBtn = new JButton("7. Add New Sale");
        JButton exitBtn = new JButton("8. Exit");


        // Add buttons to panel
        panel.add(revenueBtn);
        panel.add(topProductsBtn);
        panel.add(monthlySalesBtn);
        panel.add(cityWiseBtn);
        panel.add(addCustomerBtn);
        panel.add(addProductBtn);
        panel.add(addSaleBtn);
        panel.add(exitBtn);

        // Add panel to frame
        add(panel);

        // Connect to DB
        try {
            conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/sales_db", "root", "root"
            );
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Database connection failed: " + e);
        }

        // Button listeners
        revenueBtn.addActionListener(e -> showResult("SELECT SUM(p.price * s.quantity) AS total_revenue FROM sales s JOIN products p ON s.product_id = p.product_id", "Total Revenue"));

        topProductsBtn.addActionListener(e -> showResult(
            "SELECT p.product_name, SUM(s.quantity) AS total_sold FROM sales s JOIN products p ON s.product_id = p.product_id GROUP BY p.product_name ORDER BY total_sold DESC LIMIT 5",
            "Top Selling Products"));

        monthlySalesBtn.addActionListener(e -> showResult(
            "SELECT DATE_FORMAT(sale_date, '%Y-%m') AS month, SUM(p.price * s.quantity) AS revenue FROM sales s JOIN products p ON s.product_id = p.product_id GROUP BY month ORDER BY month",
            "Monthly Sales Report"));

        cityWiseBtn.addActionListener(e -> showResult(
            "SELECT city, COUNT(*) AS customer_count FROM customers GROUP BY city ORDER BY customer_count DESC",
            "City-wise Customer Count"));
        addCustomerBtn.addActionListener(e -> addCustomerForm());
        addProductBtn.addActionListener(e -> addProductForm());
        addSaleBtn.addActionListener(e -> addSaleForm());

        exitBtn.addActionListener(e -> System.exit(0));

        setVisible(true);
    }

    private void showResult(String query, String title) {
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);

            // Display results in JTable
            ResultSetMetaData rsmd = rs.getMetaData();
            int columns = rsmd.getColumnCount();

            DefaultTableModel tableModel = new DefaultTableModel();
            for (int i = 1; i <= columns; i++) {
                tableModel.addColumn(rsmd.getColumnName(i));
            }

            while (rs.next()) {
                Object[] row = new Object[columns];
                for (int i = 0; i < columns; i++) {
                    row[i] = rs.getObject(i + 1);
                }
                tableModel.addRow(row);
            }

            JTable table = new JTable(tableModel);
            JOptionPane.showMessageDialog(null, new JScrollPane(table), title, JOptionPane.INFORMATION_MESSAGE);

        } catch (Exception ex) {
            JOptionPane.showMessageDialog(null, "Error: " + ex);
        }
    }
    
    private void addProductForm() {
    JTextField idField = new JTextField();
    JTextField nameField = new JTextField();
    JTextField categoryField = new JTextField();
    JTextField priceField = new JTextField();

    Object[] fields = {
        "Product ID:", idField,
        "Product Name:", nameField,
        "Category:", categoryField,
        "Price:", priceField
    };

    int option = JOptionPane.showConfirmDialog(null, fields, "Add New Product", JOptionPane.OK_CANCEL_OPTION);
    if (option == JOptionPane.OK_OPTION) {
        try {
            PreparedStatement ps = conn.prepareStatement(
                "INSERT INTO products (product_id, product_name, category, price) VALUES (?, ?, ?, ?)"
            );
            ps.setInt(1, Integer.parseInt(idField.getText()));
            ps.setString(2, nameField.getText());
            ps.setString(3, categoryField.getText());
            ps.setDouble(4, Double.parseDouble(priceField.getText()));

            ps.executeUpdate();
            JOptionPane.showMessageDialog(null, "Product added successfully!");
        } catch (Exception ex) {
            JOptionPane.showMessageDialog(null, "Error: " + ex);
        }
    }
}


private void addSaleForm() {
    JTextField saleIdField = new JTextField();
    JTextField customerIdField = new JTextField();
    JTextField productIdField = new JTextField();
    JTextField quantityField = new JTextField();
    JTextField dateField = new JTextField();

    Object[] fields = {
        "Sale ID:", saleIdField,
        "Customer ID:", customerIdField,
        "Product ID:", productIdField,
        "Quantity:", quantityField,
        "Sale Date (YYYY-MM-DD):", dateField
    };

    int option = JOptionPane.showConfirmDialog(null, fields, "Add New Sale Record", JOptionPane.OK_CANCEL_OPTION);
    if (option == JOptionPane.OK_OPTION) {
        try {
            PreparedStatement ps = conn.prepareStatement
               (
                "INSERT INTO sales (sale_id, customer_id, product_id, quantity, sale_date) VALUES (?, ?, ?, ?, ?)"
               );
            ps.setInt(1, Integer.parseInt(saleIdField.getText()));
            ps.setInt(2, Integer.parseInt(customerIdField.getText()));
            ps.setInt(3, Integer.parseInt(productIdField.getText()));
            ps.setInt(4, Integer.parseInt(quantityField.getText()));
            ps.setDate(5, Date.valueOf(dateField.getText()));

            ps.executeUpdate();
            JOptionPane.showMessageDialog(null, "Sale record added successfully!");
        } catch (Exception ex) {
            JOptionPane.showMessageDialog(null, "Error: " + ex.getMessage());
        }
    }
}
    
    
    private void addCustomerForm() {
    JTextField idField = new JTextField();
    JTextField nameField = new JTextField();
    JTextField emailField = new JTextField();
    JTextField cityField = new JTextField();
    JTextField regDateField = new JTextField();

    Object[] fields = {
        "Customer ID:", idField,
        "Name:", nameField,
        "Email:", emailField,
        "City:", cityField,
        "Registration Date (YYYY-MM-DD):", regDateField
    };

    int option = JOptionPane.showConfirmDialog(null, fields, "Add New Customer", JOptionPane.OK_CANCEL_OPTION);
    if (option == JOptionPane.OK_OPTION) {
        try {
            PreparedStatement ps = conn.prepareStatement(
                "INSERT INTO customers (customer_id, name, email, city, registration_date) VALUES (?, ?, ?, ?, ?)"
            );
            ps.setInt(1, Integer.parseInt(idField.getText()));
            ps.setString(2, nameField.getText());
            ps.setString(3, emailField.getText());
            ps.setString(4, cityField.getText());
            ps.setDate(5, Date.valueOf(regDateField.getText()));

            ps.executeUpdate();
            JOptionPane.showMessageDialog(null, "Customer added successfully!");
        } catch (Exception ex) {
            JOptionPane.showMessageDialog(null, "Error: " + ex);
        }
    }
}


    public static void main(String[] args) {
        new SalesAnalyticsApp();
    }
}