Заказы клиента

[Teo90]

Opencart 3

модуль, который будет фильтровать заказы

 

есть уже база заказов, нужно сделать так, чтобы я мог отфильтровать клиентов которые уже не заказывали больше 30 дней и чтобы я смог экспортировать email а так же номера телефонов

[chukcha]

Як часто вам потрібен такий модуль?

 

SELECT `order_id`, `customer_id`, `date_modified` 
FROM `oc_order`
WHERE `customer_id` > 0
GROUP BY `customer_id`
HAVING `date_modified` < DATE_SUB(NOW(), INTERVAL 10 DAY) 
ORDER BY `date_modified` DESC

ви отримаєте потрібний список

[chukcha]

SELECT `order_id`, `customer_id`, `date_modified` 
FROM `oc_order` 
WHERE `customer_id` > 0 
AND `date_modified` < DATE_SUB(NOW(), INTERVAL 10 DAY)
GROUP BY `customer_id` 
 ORDER BY `date_modified` DESC

так краще

[yurabr]

Тримай (собі робив для того ж).
php
 

Скрытый текст

<?php
// З'єднання з базою даних
$host = "Пишіть своє";
$username = "Пишіть своє";
$password = "Пишіть своє";
$database = "Пишіть своє";

$connection = new mysqli($host, $username, $password, $database);

if ($connection->connect_error) {
    die("Connection failed: " . $connection->connect_error);
}
$connection->set_charset("utf8");
// Отримання даних з форми
$minOrderTotal = $_POST["min_order_total"];
$minOrderCount = $_POST["min_order_count"];
$intervalMonth = $_POST["interval_month"];

// Підготовка та виконання SQL-запиту
$sql = "SELECT c.customer_id, c.firstname, c.lastname, COUNT(o.order_id) AS order_count, SUM(o.total) AS total_order_amount, MAX(o.date_added) AS last_order_date 
        FROM oc_customer c 
        JOIN oc_order o ON c.customer_id = o.customer_id 
        GROUP BY c.customer_id 
        HAVING (order_count > $minOrderCount OR total_order_amount > $minOrderTotal) AND last_order_date <= NOW() - INTERVAL $intervalMonth MONTH 
        ORDER BY `order_count` ASC";

$result = $connection->query($sql);

// Формування HTML-виводу
$output = '<table>
                <tr>
                    <th onclick="sortTable(1)">Customer ID</th>
                    <th onclick="sortTable(2)">Ім*я</th>
                    <th onclick="sortTable(3)">Прізвище</th>
                    <th onclick="sortTable(4)">Кількість замовлень</th>
                    <th onclick="sortTable(5)">Сума замовлень</th>
                    <th onclick="sortTable(6)">Дата останнього замовлення</th>
                </tr>';

if ($result->num_rows > 0) {
    $customerCount = 0;

    while ($row = $result->fetch_assoc()) {
        $customerCount++;
        $output .= '<tr>
                        <td>' . $row["customer_id"] . '</td>
                        <td>' . $row["firstname"] . '</td>
                        <td>' . $row["lastname"] . '</td>
                        <td>' . $row["order_count"] . '</td>
                        <td>' . $row["total_order_amount"] . '</td>
                        <td>' . $row["last_order_date"] . '</td>
                    </tr>';
    }
}
$output .= '<p>Total Customers: ' . $customerCount . '</p>';
$output .= '</table>';

echo $output;

// Закриття з'єднання
$connection->close();
?>

<script>
    function sortTable(n) {
        var table, rows, switching, i, x, y, shouldSwitch, dir, switchcount = 0;
        table = document.querySelector("table");
        switching = true;
        //Set the sorting direction to ascending:
        dir = "asc";
        /*Make a loop that will continue until
        no switching has been done:*/
        while (switching) {
            //start by saying: no switching is done:
            switching = false;
            rows = table.rows;
            /*Loop through all table rows (except the
            first, which contains table headers):*/
            for (i = 1; i < (rows.length - 1); i++) {
                //start by saying there should be no switching:
                shouldSwitch = false;
                /*Get the two elements you want to compare,
                one from current row and one from the next:*/
                x = rows[i].getElementsByTagName("td")[n];
                y = rows[i + 1].getElementsByTagName("td")[n];
                /*check if the two rows should switch place,
                based on the direction, asc or desc:*/
                if (dir == "asc") {
                    if (x.innerHTML.toLowerCase() > y.innerHTML.toLowerCase()) {
                        //if so, mark as a switch and break the loop:
                        shouldSwitch = true;
                        break;
                    }
                } else if (dir == "desc") {
                    if (x.innerHTML.toLowerCase() < y.innerHTML.toLowerCase()) {
                        //if so, mark as a switch and break the loop:
                        shouldSwitch = true;
                        break;
                    }
                }
            }
            if (shouldSwitch) {
                /*If a switch has been marked, make the switch
                and mark that a switch has been done:*/
                rows[i].parentNode.insertBefore(rows[i + 1], rows[i]);
                switching = true;
                //Each time a switch is done, increase this count by 1:
                switchcount++;
            } else {
                /*If no switching has been done AND the direction is "asc",
                set the direction to "desc" and run the while loop again.*/
                if (switchcount == 0 && dir == "asc") {
                    dir = "desc";
                    switching = true;
                }
            }
        }
    }
</script>

 

html

Скрытый текст

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Customer Information</title>
    <style>
        body {
            font-family: 'Arial', sans-serif;
            background-color: #f8f8f8;
            margin: 0;
            padding: 0;
        }

        h2 {
            color: #333;
            text-align: center;
            margin-top: 20px;
        }

        form {
            max-width: 400px;
            margin: 20px auto;
            background-color: #fff;
            padding: 20px;
            border-radius: 8px;
            box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);
        }

        label {
            display: block;
            margin-bottom: 8px;
            color: #555;
        }

        input {
            width: 100%;
            padding: 10px;
            margin-bottom: 15px;
            border: 1px solid #ddd;
            border-radius: 4px;
            box-sizing: border-box;
        }

        input[type="submit"] {
            background-color: #4CAF50;
            color: #fff;
            cursor: pointer;
        }

        input[type="submit"]:hover {
            background-color: #45a049;
        }

        #customerInfo {
            max-width: 800px;
            margin: 20px auto;
        }

        table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 20px;
            border-radius: 8px;
            overflow: hidden;
            box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);
        }

        th, td {
            padding: 15px;
            text-align: left;
            border-bottom: 1px solid #ddd;
        }

        th {
            background-color: #4CAF50;
            color: white;
            cursor: pointer;
        }

        th:hover {
            background-color: #45a049;
        }
    </style>
</head>
<body>

<h2>Інформація про клієнтів</h2>

<form method="post" onsubmit="submitForm(event)">
    <label for="min_order_total">Сума замовлень:</label>
    <input type="number" name="min_order_total" value="10000" required>

    <label for="min_order_count">Кількість замовлень:</label>
    <input type="number" name="min_order_count" value="3" required>

    <label for="interval_month">Останнє замовлення місяців тому:</label>
    <input type="number" name="interval_month" value="2" required>

    <input type="submit" value="Submit">
</form>

<div id="customerInfo"></div>

<script>
    function submitForm(event) {
        event.preventDefault();
        var formData = new FormData(event.target);
        var xhr = new XMLHttpRequest();
        xhr.onreadystatechange = function () {
            if (xhr.readyState === 4 && xhr.status === 200) {
                document.getElementById('customerInfo').innerHTML = xhr.responseText;
            }
        };
        xhr.open("POST", "get_customer_info.php", true);
        xhr.send(formData);
    }

    function sortTable(n) {
        var table, rows, switching, i, x, y, shouldSwitch, dir, switchcount = 0;
        table = document.querySelector("table");
        switching = true;
        dir = "asc";

        while (switching) {
            switching = false;
            rows = table.rows;

            for (i = 1; i < (rows.length - 1); i++) {
                shouldSwitch = false;
                x = rows[i].getElementsByTagName("td")[n];
                y = rows[i + 1].getElementsByTagName("td")[n];

                if (dir == "asc") {
                    if (x.innerHTML.toLowerCase() > y.innerHTML.toLowerCase()) {
                        shouldSwitch = true;
                        break;
                    }
                } else if (dir == "desc") {
                    if (x.innerHTML.toLowerCase() < y.innerHTML.toLowerCase()) {
                        shouldSwitch = true;
                        break;
                    }
                }
            }

            if (shouldSwitch) {
                rows[i].parentNode.insertBefore(rows[i + 1], rows[i]);
                switching = true;
                switchcount++;
            } else {
                if (switchcount == 0 && dir == "asc") {
                    dir = "desc";
                    switching = true;
                }
            }
        }
    }
</script>

</body>
</html>

 

Може комусь знадобиться...

[Teo90]

04.03.2024 в 08:06, yurabr сказал:

Тримай (собі робив для того ж).
php
 

  Скрыть содержимое

<?php
// З'єднання з базою даних
$host = "Пишіть своє";
$username = "Пишіть своє";
$password = "Пишіть своє";
$database = "Пишіть своє";

$connection = new mysqli($host, $username, $password, $database);

if ($connection->connect_error) {
    die("Connection failed: " . $connection->connect_error);
}
$connection->set_charset("utf8");
// Отримання даних з форми
$minOrderTotal = $_POST["min_order_total"];
$minOrderCount = $_POST["min_order_count"];
$intervalMonth = $_POST["interval_month"];

// Підготовка та виконання SQL-запиту
$sql = "SELECT c.customer_id, c.firstname, c.lastname, COUNT(o.order_id) AS order_count, SUM(o.total) AS total_order_amount, MAX(o.date_added) AS last_order_date 
        FROM oc_customer c 
        JOIN oc_order o ON c.customer_id = o.customer_id 
        GROUP BY c.customer_id 
        HAVING (order_count > $minOrderCount OR total_order_amount > $minOrderTotal) AND last_order_date <= NOW() - INTERVAL $intervalMonth MONTH 
        ORDER BY `order_count` ASC";

$result = $connection->query($sql);

// Формування HTML-виводу
$output = '<table>
                <tr>
                    <th onclick="sortTable(1)">Customer ID</th>
                    <th onclick="sortTable(2)">Ім*я</th>
                    <th onclick="sortTable(3)">Прізвище</th>
                    <th onclick="sortTable(4)">Кількість замовлень</th>
                    <th onclick="sortTable(5)">Сума замовлень</th>
                    <th onclick="sortTable(6)">Дата останнього замовлення</th>
                </tr>';

if ($result->num_rows > 0) {
    $customerCount = 0;

    while ($row = $result->fetch_assoc()) {
        $customerCount++;
        $output .= '<tr>
                        <td>' . $row["customer_id"] . '</td>
                        <td>' . $row["firstname"] . '</td>
                        <td>' . $row["lastname"] . '</td>
                        <td>' . $row["order_count"] . '</td>
                        <td>' . $row["total_order_amount"] . '</td>
                        <td>' . $row["last_order_date"] . '</td>
                    </tr>';
    }
}
$output .= '<p>Total Customers: ' . $customerCount . '</p>';
$output .= '</table>';

echo $output;

// Закриття з'єднання
$connection->close();
?>

<script>
    function sortTable(n) {
        var table, rows, switching, i, x, y, shouldSwitch, dir, switchcount = 0;
        table = document.querySelector("table");
        switching = true;
        //Set the sorting direction to ascending:
        dir = "asc";
        /*Make a loop that will continue until
        no switching has been done:*/
        while (switching) {
            //start by saying: no switching is done:
            switching = false;
            rows = table.rows;
            /*Loop through all table rows (except the
            first, which contains table headers):*/
            for (i = 1; i < (rows.length - 1); i++) {
                //start by saying there should be no switching:
                shouldSwitch = false;
                /*Get the two elements you want to compare,
                one from current row and one from the next:*/
                x = rows[i].getElementsByTagName("td")[n];
                y = rows[i + 1].getElementsByTagName("td")[n];
                /*check if the two rows should switch place,
                based on the direction, asc or desc:*/
                if (dir == "asc") {
                    if (x.innerHTML.toLowerCase() > y.innerHTML.toLowerCase()) {
                        //if so, mark as a switch and break the loop:
                        shouldSwitch = true;
                        break;
                    }
                } else if (dir == "desc") {
                    if (x.innerHTML.toLowerCase() < y.innerHTML.toLowerCase()) {
                        //if so, mark as a switch and break the loop:
                        shouldSwitch = true;
                        break;
                    }
                }
            }
            if (shouldSwitch) {
                /*If a switch has been marked, make the switch
                and mark that a switch has been done:*/
                rows[i].parentNode.insertBefore(rows[i + 1], rows[i]);
                switching = true;
                //Each time a switch is done, increase this count by 1:
                switchcount++;
            } else {
                /*If no switching has been done AND the direction is "asc",
                set the direction to "desc" and run the while loop again.*/
                if (switchcount == 0 && dir == "asc") {
                    dir = "desc";
                    switching = true;
                }
            }
        }
    }
</script>

 

html

  Скрыть содержимое

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Customer Information</title>
    <style>
        body {
            font-family: 'Arial', sans-serif;
            background-color: #f8f8f8;
            margin: 0;
            padding: 0;
        }

        h2 {
            color: #333;
            text-align: center;
            margin-top: 20px;
        }

        form {
            max-width: 400px;
            margin: 20px auto;
            background-color: #fff;
            padding: 20px;
            border-radius: 8px;
            box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);
        }

        label {
            display: block;
            margin-bottom: 8px;
            color: #555;
        }

        input {
            width: 100%;
            padding: 10px;
            margin-bottom: 15px;
            border: 1px solid #ddd;
            border-radius: 4px;
            box-sizing: border-box;
        }

        input[type="submit"] {
            background-color: #4CAF50;
            color: #fff;
            cursor: pointer;
        }

        input[type="submit"]:hover {
            background-color: #45a049;
        }

        #customerInfo {
            max-width: 800px;
            margin: 20px auto;
        }

        table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 20px;
            border-radius: 8px;
            overflow: hidden;
            box-shadow: 0 0 10px rgba(0, 0, 0, 0.1);
        }

        th, td {
            padding: 15px;
            text-align: left;
            border-bottom: 1px solid #ddd;
        }

        th {
            background-color: #4CAF50;
            color: white;
            cursor: pointer;
        }

        th:hover {
            background-color: #45a049;
        }
    </style>
</head>
<body>

<h2>Інформація про клієнтів</h2>

<form method="post" onsubmit="submitForm(event)">
    <label for="min_order_total">Сума замовлень:</label>
    <input type="number" name="min_order_total" value="10000" required>

    <label for="min_order_count">Кількість замовлень:</label>
    <input type="number" name="min_order_count" value="3" required>

    <label for="interval_month">Останнє замовлення місяців тому:</label>
    <input type="number" name="interval_month" value="2" required>

    <input type="submit" value="Submit">
</form>

<div id="customerInfo"></div>

<script>
    function submitForm(event) {
        event.preventDefault();
        var formData = new FormData(event.target);
        var xhr = new XMLHttpRequest();
        xhr.onreadystatechange = function () {
            if (xhr.readyState === 4 && xhr.status === 200) {
                document.getElementById('customerInfo').innerHTML = xhr.responseText;
            }
        };
        xhr.open("POST", "get_customer_info.php", true);
        xhr.send(formData);
    }

    function sortTable(n) {
        var table, rows, switching, i, x, y, shouldSwitch, dir, switchcount = 0;
        table = document.querySelector("table");
        switching = true;
        dir = "asc";

        while (switching) {
            switching = false;
            rows = table.rows;

            for (i = 1; i < (rows.length - 1); i++) {
                shouldSwitch = false;
                x = rows[i].getElementsByTagName("td")[n];
                y = rows[i + 1].getElementsByTagName("td")[n];

                if (dir == "asc") {
                    if (x.innerHTML.toLowerCase() > y.innerHTML.toLowerCase()) {
                        shouldSwitch = true;
                        break;
                    }
                } else if (dir == "desc") {
                    if (x.innerHTML.toLowerCase() < y.innerHTML.toLowerCase()) {
                        shouldSwitch = true;
                        break;
                    }
                }
            }

            if (shouldSwitch) {
                rows[i].parentNode.insertBefore(rows[i + 1], rows[i]);
                switching = true;
                switchcount++;
            } else {
                if (switchcount == 0 && dir == "asc") {
                    dir = "desc";
                    switching = true;
                }
            }
        }
    }
</script>

</body>
</html>

 

Може комусь знадобиться...

 

а где мне это нужно вставить?

[yurabr]

Це два файлики, які створюють простенький сайтик:)

Кидаєш в піддомен на хості чи як воно там у тебе робиться... спочатку створюєш піддомен

Приблизно вийде customer.mysite.ua

Користуєшся. Дороблюєш, як дозволить фантазія.