Delete transactional data older than given days

Follow these steps to set Delete Transactions button to delete transactional data older than given days

1. In Saleculator, go to Maintenance>Execute SQL
Copy paste the below code in the text box and click the execute button:

CREATE PROCEDURE DeleteTransactions(IN days INT)
      BEGIN
      SET @days = days;
      DELETE FROM DELIVERYORDERS WHERE CALLID IN (SELECT ID FROM CALLS WHERE DATENEW<=DATE_SUB(CURDATE(),INTERVAL @days DAY));
      DELETE FROM CALLS WHERE DATENEW<=DATE_SUB(CURDATE(),INTERVAL @days DAY);
      DELETE FROM TICKETLINES WHERE TICKET IN (SELECT ID FROM RECEIPTS WHERE DATENEW<=DATE_SUB(CURDATE(),INTERVAL @days DAY));
      DELETE FROM TAXLINES WHERE RECEIPT IN (SELECT ID FROM RECEIPTS WHERE DATENEW<=DATE_SUB(CURDATE(),INTERVAL @days DAY));
      DELETE FROM TICKETS WHERE ID IN (SELECT ID FROM RECEIPTS WHERE DATENEW<=DATE_SUB(CURDATE(),INTERVAL @days DAY));
      DELETE FROM PAYMENTS WHERE RECEIPT IN (SELECT ID FROM RECEIPTS WHERE DATENEW<=DATE_SUB(CURDATE(),INTERVAL @days DAY));
      DELETE FROM RECEIPTS WHERE DATENEW<=DATE_SUB(CURDATE(),INTERVAL @days DAY);
      DELETE FROM CLOSEDCASH WHERE DATEEND<=DATE_SUB(CURDATE(),INTERVAL @days DAY);
      DELETE FROM STOCKDIARY WHERE DATENEW<=DATE_SUB(CURDATE(),INTERVAL @days DAY);
      DELETE FROM RESERVATIONS WHERE DATENEW<=DATE_SUB(CURDATE(),INTERVAL @days DAY);
END

2. Go to Maintenance>Resources and choose the resource SQL.DeleteTransactions. Delete all lines from it and add the below line:

CALL DeleteTransactions(30);

Here, 30 denotes the number of days. This statement delete all transactional data older than 30 days, Change this value according to your need.
Now every time you click Maintenance>Delete Transactions button, system delete all transactional data older than the given days.

0 Comments

Leave a Reply

Arrow-up