CREATE OR ALTER PROCEDURE PSESSIONS_EDIT (
VIN_ACT SMALLINT NOT NULL,
VIN_SESSION INTEGER,
VIN_CASHDESK INTEGER NOT NULL,
VIN_USER SMALLINT NOT NULL,
VIN_ACTUAL DECIMAL(15,2),
VIN_COMMENT VARCHAR(150))
RETURNS (
VID INTEGER,
VUUID VARCHAR(36),
VOPENED TIMESTAMP,
VCLOSED TIMESTAMP)
AS
DECLARE VARIABLE VMY_AUTOACTUAL DECIMAL(15,2);
BEGIN
IF (:VIN_ACT = 0) THEN
/* ОТКРЫТЬ СМЕНУ */
BEGIN
SELECT FIRST 1 FID, FUUID, FOPENED, FCLOSED
FROM TSESSIONS
WHERE FCASHDESK = :VIN_CASHDESK
ORDER BY FID DESC
INTO :VID, :VUUID, :VOPENED, :VCLOSED;
IF (:VOPENED IS NOT NULL AND :VCLOSED IS NULL) THEN
EXCEPTION ESESSION_ALREADY_OPEN;
/* Открытие */
VID = COALESCE((SELECT FIRST 1 FID FROM TSESSIONS ORDER BY FID DESC), 0) + 1;
VUUID = LOWER(UUID_TO_CHAR(GEN_UUID()));
INSERT INTO TSESSIONS (FID, FUUID, FCASHDESK, FOPENED, FUSER)
VALUES (:VID, :VUUID, :VIN_CASHDESK, CURRENT_TIMESTAMP, :VIN_USER);
END ELSE
/* ЗАКРЫТЬ СМЕНУ */
BEGIN
VID = :VIN_SESSION;
VUUID = (SELECT FIRST 1 FUUID FROM TSESSIONS WHERE FID = :VIN_SESSION);
UPDATE TSESSIONS SET
FCLOSED = CURRENT_TIMESTAMP,
FACTUAL = :VIN_ACTUAL,
FCOMMENT = :VIN_COMMENT
WHERE FID = :VIN_SESSION AND FCASHDESK = :VIN_CASHDESK;
END
SUSPEND;
END