CREATE TABLE tb_numbers (
unique_uid varchar(40) NOT NULL,
keterangan varchar(250) DEFAULT NULL,
tanggal varchar(10) NOT NULL,
trans_id varchar(50) NOT NULL,
id int(11) NOT NULL AUTO_INCREMENT,
tsx timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (tanggal,trans_id,id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

CREATE FUNCTION NoBaru(Codes varchar(4),Tgl Varchar(10), sKeterangan VARCHAR(250)) RETURNS char(11) CHARSET latin1
BEGIN
DECLARE NoChk Varchar(36) DEFAULT "";
DECLARE Nomer Varchar(11) DEFAULT 0;
DECLARE NoInt INTEGER DEFAULT 0;
DECLARE TipeNo Integer DEFAULT 0;
DECLARE DigitNum Integer DEFAULT 0;
DECLARE DigitTgl Integer DEFAULT 0;
DECLARE NoID Integer DEFAULT 0;
SET Tgl= DATE_FORMAT(CAST(Tgl AS DATETIME),'%y%m%d');
SET NoChk = UUID();
SET TipeNo = IFNULL((SELECT NilaiNum FROM tm_setupall WHERE Groups="Nomor" AND SubGroups = Codes),1);
SET @Keterangan = sKeterangan;
IF (TRIM(IFNULL(sKeterangan,''))='') THEN
SET @Keterangan = IFNULL((SELECT KetNilai FROM tm_setupall WHERE Groups="Nomor" AND SubGroups=Codes),"");
END IF;
IF (TipeNo=0) THEN
SET DigitNum = 7;
SET DigitTgl = 2;
END IF;
IF (TipeNo = 1) THEN
SET DigitNum = 6;
SET DigitTgl = 2;
END IF;
IF (TipeNo = 2) THEN
SET DigitNum = 4;
SET DigitTgl = 4;
END IF;
IF (TipeNo = 3) THEN
SET DigitNum = 6;
SET DigitTgl = 3;
END IF;
IF (SELECT COUNT(*) FROM tb_numbers WHERE Trans_ID=Codes) = 0 THEN
SET NoID = (SELECT IFNULL(RIGHT(NilaiChar,DigitNum),0) + 1 FROM tm_setupall WHERE Groups = "Nomor" AND SubGroups= Codes);
INSERT INTO tb_numbers(Unique_UID, ID, Tanggal, Trans_ID, Keterangan) VALUES(NoChk, NoID, LEFT(Tgl,DigitTgl), Codes, @Keterangan);
ELSE
INSERT INTO tb_numbers(Unique_UID, Tanggal, Trans_ID, Keterangan) VALUES(NoChk, LEFT(Tgl,DigitTgl), Codes, @Keterangan);
END IF;
SET NoInt = (SELECT ID FROM tb_numbers WHERE Unique_Uid = NoChk);
SET Nomer = CONCAT(LEFT(Tgl,DigitTgl), LPAD(NoInt,DigitNum,'0'));
IF TipeNo=0 THEN
RETURN CONCAT(Codes, Nomer);
ELSE
Return Nomer;
END IF;
END