Transaction cutting above certain amount limits

This document will guide you on how to cut down a certain percentage of the total amount of the transactions

Procedure:

Follow these steps to set the Delete Transactions button to a certain percentage greater than the given amount

Executing mySQL scipt (Windows)

1. Copy paste below scripts to a text file (notepad) and save the file as deltran.sql in C:\ drive ( To cut the transactions of the last sequence)

DELIMITER $$
DROP PROCEDURE IF EXISTS SyncTables$$
CREATE PROCEDURE SyncTables(IN minSalesAmount DOUBLE, IN cutPctValue DOUBLE)
BEGIN
DECLARE minSales DOUBLE;
DECLARE cutPct DOUBLE;
DECLARE totalSales DOUBLE;
DECLARE lastID INT;
DECLARE tmpTotal DOUBLE;
DECLARE tmpTICKETID INT;
DECLARE tmpRECEIPTID INT;
CREATE TABLE IF NOT EXISTS DELTRAN
(
LASTTICKETID INTEGER DEFAULT 1 NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

      SET @rwcount = (SELECT count(*) FROM DELTRAN );

      IF (@rwcount =0) THEN
        INSERT INTO DELTRAN (LASTTICKETID) VALUES(1);
      END IF;

      SET @minSales = minSalesAmount;
      SET @cutPct =  cutPctValue;
      SET @lastID = (SELECT LASTTICKETID FROM DELTRAN LIMIT 1);

      SET @totalSales = (SELECT SUM(TOTAL) FROM PAYMENTS P, TICKETS T, RECEIPTS R WHERE T.TICKETTYPE = 0 AND T.ID = R.ID AND R.ID=P.RECEIPT AND T.TICKETID > @lastID);

IF (@totalSales > @minSales) THEN
SET @tmpTotal = ( @totalSales * @cutPct ) / 100;
WHILE @tmpTotal > 0 DO
SET @tmpRECEIPTID = (SELECT ID FROM tickets WHERE TICKETID > @lastID ORDER BY RAND() LIMIT 1);
SET @tmpTotal = (SELECT @tmpTotal - SUM(TOTAL) FROM PAYMENTS WHERE RECEIPT = @tmpRECEIPTID);
DELETE FROM TAXLINES WHERE RECEIPT = @tmpRECEIPTID;
DELETE FROM TICKETLINES WHERE TICKET = @tmpRECEIPTID;
DELETE FROM TICKETS WHERE ID = @tmpRECEIPTID;
DELETE FROM PAYMENTS WHERE RECEIPT = @tmpRECEIPTID;
DELETE FROM DELIVERYORDERS WHERE RECEIPT = @tmpRECEIPTID;
DELETE FROM RECEIPTS WHERE ID = @tmpRECEIPTID;
SELECT @tmpTotal;
END WHILE;
SET @row := @lastID;
UPDATE TICKETS SET TICKETID = @row := @row + 1 WHERE TICKETID > @lastID ORDER BY TICKETID ASC;
UPDATE DELTRAN SET LASTTICKETID = @row;
UPDATE TICKETSNUM SET ID = @row;
END IF;
END$$
DELIMITER ;

2. Copy paste below scripts to a text file (notepad) and save the file as sequencedeltran.sql in C:\ drive (To cut the transactions sequence by sequence starting from the first sequence)

DROP TABLE IF EXISTS DELTRAN;
CREATE TABLE DELTRAN
(
LASTSEQUENCE int(11),
HOSTNAME varchar(255)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
DELIMITER $$
DROP PROCEDURE IF EXISTS SyncTables$$
CREATE PROCEDURE SyncTables(IN hostNameParam VARCHAR(255), IN minSalesAmount DOUBLE, IN cutPctValue DOUBLE)
BEGIN
DECLARE minSales DOUBLE;
DECLARE cutPct DOUBLE;
DECLARE totalSales DOUBLE;
DECLARE lastID INT;
DECLARE tmpTotal DOUBLE;
DECLARE tmpTICKETID INT;
DECLARE tmpRECEIPTID INT;

    SET @minSales = minSalesAmount;
    SET @cutPct =  cutPctValue;
      SET @rwcount = (SELECT LASTSEQUENCE FROM DELTRAN WHERE HOSTNAME = hostNameParam LIMIT 1);
      IF (@rwcount IS NULL) THEN
        INSERT INTO DELTRAN (LASTSEQUENCE, HOSTNAME) VALUES(0, hostNameParam);
      END IF;
      SET @lastSequence = (SELECT LASTSEQUENCE+1 FROM DELTRAN WHERE HOSTNAME = hostNameParam LIMIT 1);
      SET @lastMoney = (SELECT MONEY FROM CLOSEDCASH WHERE HOSTSEQUENCE=@lastSequence AND HOST = hostNameParam AND DATEEND IS NOT NULL LIMIT 1);

IF @lastMoney IS NOT NULL THEN
SET @totalSales = (SELECT SUM(TOTAL) FROM PAYMENTS P, TICKETS T, RECEIPTS R WHERE T.TICKETTYPE = 0 AND T.ID = R.ID AND R.ID=P.RECEIPT AND R.MONEY=@lastMoney);

IF (@totalSales > @minSales) THEN
SET @tmpTotal = ( @totalSales * @cutPct ) / 100;
WHILE @tmpTotal > 0 DO
SET @tmpRECEIPTID = (SELECT ID FROM RECEIPTS WHERE MONEY = @lastMoney ORDER BY RAND() LIMIT 1);
SET @tmpTotal = (SELECT @tmpTotal - SUM(TOTAL) FROM PAYMENTS WHERE RECEIPT = @tmpRECEIPTID);
DELETE FROM TAXLINES WHERE RECEIPT = @tmpRECEIPTID;
DELETE FROM TICKETLINES WHERE TICKET = @tmpRECEIPTID;
DELETE FROM TICKETS WHERE ID = @tmpRECEIPTID;
DELETE FROM PAYMENTS WHERE RECEIPT = @tmpRECEIPTID;
DELETE FROM DELIVERYORDERS WHERE RECEIPT = @tmpRECEIPTID;
DELETE FROM RECEIPTS WHERE ID = @tmpRECEIPTID;
-- SELECT @tmpTotal;
END WHILE;

IF (@lastSequence > 1) THEN
    SET @curSequence = (SELECT LASTSEQUENCE FROM DELTRAN WHERE HOSTNAME = hostNameParam LIMIT 1);
    SET @curMoney = (SELECT MONEY FROM CLOSEDCASH WHERE HOSTSEQUENCE=@curSequence LIMIT 1);
    SET @row := (SELECT MAX(TICKETID) FROM TICKETS JOIN RECEIPTS ON RECEIPTS.ID=TICKETS.ID AND RECEIPTS.MONEY=@curMoney);
ELSE    
    SET @row := 1;
END IF;

UPDATE TICKETS SET TICKETID = @row := @row + 1 WHERE ID IN (SELECT ID FROM RECEIPTS WHERE MONEY=@lastMoney ) ORDER BY TICKETID ASC;

END IF;
UPDATE DELTRAN SET LASTSEQUENCE = @lastSequence WHERE HOSTNAME = hostNameParam;
SET @maxID := (SELECT MAX(TICKETID) FROM TICKETS);
UPDATE TICKETSNUM SET ID = @maxID;
END IF;
END$$
DELIMITER ;

3. Open MySQL command prompt login using the password “password” without the quotation marks and type the below commands

use SALECULATOR; (Press Enter Key)
source C:\deltran.sql  (For step 1 scripts and Press Enter Key)
source C:\sequencedeltran.sql  (For step 2 scripts and Press Enter Key)  
exit  (Press Enter Key)

4. Administration Menu > Maintenance > Resources

5. Under Resources click on SQL.DeleteTransactions from the left side list

6. Comment all the lines in the scripts window using — at front of the lines, and copy-paste the below script at the bottom:

CALL SyncTables(5000, 30);

This means once the total amount of a sequence goes above 5000 then it will be reduced or cut by 30%

(Paste this if there is only one Close Cash Station available, see below image)

CALL SYNCTABLES('SALECULATOR', 250, 50);

This means once the total amount of a sequence goes above 250 then it will be reduced or cut by 50% from the SALECULATOR close cash station.

(Paste this if there are multiple Close Cash Station available, i.e you need to mention also the station name just like it is shown in the station field. See below image)

7. In case of multiple names under the Close Cash Station you can set your restaurant/shop name as the default station by typing your restaurant/shop name in the Cash Closing Host field under the General tab in Configuration (See the image below). Click save and restart after making the changes.

This is how it should look once the command is pasted in SQL.DeleteTransactions resource

7. In Case if you see any blank sequences (Sequences with Payments, Total & Expected Cash fields showing blank) as shown in the below image. You need to execute an SQL script to skip this sequence before pressing the Delete Transactions button.

8. Click Execute SQL button under the Maintenance and Copy paste below SQL command in the command field

UPDATE DELTRAN SET LASTSEQUENCE = 20 WHERE HOSTNAME = 'SALECULATOR' ;

(Were number 20 is the blank sequence number and SALECULATOR is your shop name)

After pasting the command press the green Play button. Once the command is executed successfully it will show Update count: 1 in the Resultset1 field. (Refer to the image below)