Alte Bestellungen löschen?

[jwinkel]

Hallo zusammen,

wie könnte ich alle Bestellungen löschen, die vor 2015 getätigt wurden???

Zusatzfrage: Wie könnte ich alle Kundenkonten löschen, die seit 5 Jahren nichts mehr bestellt haben???

[oldbear]

am einfachsten mit einem PHP-Skript .

aber zuerst die Kunden, um die xt_orders ( date_purchased ) noch abfragen zu können

$rs = $db->Execute("SELECT customers_id FROM xt_customers");
while (!$rs->EOF) {
    $cid = $rs->fields['customers_id'];
    $rs_o = $db->Execute("SELECT c.customers_id FROM xt_customers AS c SELECT c.customers_id FROM xt_customers AS c LEFT JOIN xt_orders AS o ON ( c.customers_id = o.customers_id )
WHERE c.customers_id = '".$cid."' AND o.date_purchased >= '2016-01-01'");
    if ($rs_o->RecordCount() == 0) {
        // Adressen löschen
        $rs_d = $db->Execute("DELETE FROM xt_customers_addresses WHERE customers_id = '".$cid."'");
        // Kunden löschen
        $rs_d = $db->Execute("DELETE FROM xt_customers WHERE customers_id = '".$cid."'");
    }
}

Bei den Orders sinds ja mehrere Tabellen:

$rs = $db->Execute("SELECT orders_id FROM xt_orders WHERE data_purchased <= '2016-01-01'");

while (!$rs->EOF) {
    $oid = $rs->fields['orders_id'];
    // order_products
    $rs_d = $db->Execute("DELETE FROM xt_orders_products WHERE orders_id = '".$oid."'");    // order_products
    // orders_products_media
    $rs_d = $db->Execute("DELETE FROM xt_orders_products_media WHERE orders_id = '".$oid."'");
    // orders_total
    $rs_d = $db->Execute("DELETE FROM xt_orders_total WHERE orders_id = '".$oid."'");
    // orders_status_history
    $rs_d = $db->Execute("DELETE FROM xt_orders_status_history WHERE orders_id = '".$oid."'");
}

ohne Gewähr, Du kannst ja programmieren ....:-)

Grüße

[jwinkel]

Danke! Mit ein paar kleinen Anpassungen klappt's:

$wann="2015-01-01";
$rs = $db->Execute("SELECT customers_id FROM xt_customers");
while (!$rs->EOF) {
    $cid = $rs->fields['customers_id'];
    $rs_o = $db->Execute("SELECT c.customers_id FROM xt_customers AS c LEFT JOIN xt_orders AS o ON ( c.customers_id = o.customers_id ) WHERE c.customers_id = '" . $cid . "' AND o.date_purchased >= '".$wann."'");
    if ($rs_o->RecordCount() == 0) {
        // Adressen löschen
        $rs_d = $db->Execute("DELETE FROM xt_customers_addresses WHERE customers_id = '".$cid."'");
        // Kunden löschen
        $rs_d = $db->Execute("DELETE FROM xt_customers WHERE customers_id = '".$cid."'");
        // Warenkörbe löschen
        $rs_d = $db->Execute("DELETE FROM xt_customers_basket WHERE customers_id = '".$cid."'");
    }
    $rs->MoveNext();
}
$rs = $db->Execute("SELECT orders_id FROM xt_orders WHERE date_purchased <= '".$wann."'");

while (!$rs->EOF) {
    $oid = $rs->fields['orders_id'];
    // order
    $rs_d = $db->Execute("DELETE FROM xt_orders WHERE orders_id = '".$oid."'");    // order_products
    // order_products
    $rs_d = $db->Execute("DELETE FROM xt_orders_products WHERE orders_id = '".$oid."'");    // order_products
    // orders_products_media
    $rs_d = $db->Execute("DELETE FROM xt_orders_products_media WHERE orders_id = '".$oid."'");
    // orders_total
    $rs_d = $db->Execute("DELETE FROM xt_orders_total WHERE orders_id = '".$oid."'");
    // order_stats
    $rs_d = $db->Execute("DELETE FROM xt_orders_stats WHERE orders_id = '".$oid."'");    // order_products
    // orders_status_history
    $rs_d = $db->Execute("DELETE FROM xt_orders_status_history WHERE orders_id = '".$oid."'");
    $rs->MoveNext();
}

Aber auch hier: Klappt bei mir, sonst ohne Gewähr.
Schade dass es eine solche Aufräumfunktion nicht standardmässig gibt - wäre ja allein schon wegen DSGVO erforderlich.