salix/db/routines/vn/triggers/invoiceOut_beforeInsert.sql

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` TRIGGER `vn`.`invoiceOut_beforeInsert`
	BEFORE INSERT ON `invoiceOut`
	FOR EACH ROW
BEGIN
/**
 * Generates the next reference for the invoice serial. There cannot be gaps
 * between identifiers of the same serial!
 *
 * Reference format:
 *  {0} Invoice serial
 *  {1} The company code
 *  {2-3} Last two digits of issue year
 *  {4-$} Autoincrement identifier
 */
	DECLARE vRef INT DEFAULT 0;
	DECLARE vRefLen INT;
	DECLARE vRefPrefix VARCHAR(255);
	DECLARE vLastRef VARCHAR(255);
	DECLARE vCompanyCode INT;
	DECLARE vYearLen INT DEFAULT 2;
	DECLARE vPrefixLen INT;

	SELECT companyCode INTO vCompanyCode
		FROM company
		WHERE id = NEW.companyFk;

	IF vCompanyCode IS NULL THEN
		CALL util.throw('companyCodeNotDefined');
	END IF;

	SELECT MAX(i.ref) INTO vLastRef
		FROM invoiceOut i
		WHERE i.serial = NEW.serial
			AND i.issued BETWEEN util.firstDayOfYear(NEW.issued) AND util.lastDayOfYear(NEW.issued)
			AND i.companyFk = NEW.companyFk;

	IF vLastRef IS NOT NULL THEN
		SET vPrefixLen = LENGTH(NEW.serial) + LENGTH(vCompanyCode) + vYearLen;
		SET vRefLen = LENGTH(vLastRef) - vPrefixLen;
		SET vRefPrefix = LEFT(vLastRef, vPrefixLen);
		SET vRef = RIGHT(vLastRef, vRefLen);
	ELSE
		SELECT refLen INTO vRefLen FROM invoiceOutConfig;
		SET vRefPrefix = CONCAT(
			NEW.serial,
			vCompanyCode,
			RIGHT(YEAR(NEW.issued), vYearLen)
		);
	END IF;

	SET vRef = vRef + 1;
	IF LENGTH(vRef) > vRefLen THEN
		CALL util.throw('refIdLenExceeded');
	END IF;

	SET NEW.ref = CONCAT(vRefPrefix, LPAD(vRef, vRefLen, '0'));
END$$
DELIMITER ;