DROP TABLE IF EXISTS `seq`;
CREATE TABLE `seq`  (
  `Name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '表名',
  `CurrDate` date NOT NULL COMMENT '當前日期',
  `Num` int(11) NOT NULL COMMENT '序號',
  `StepLen` int(11) NULL DEFAULT 1 COMMENT '步長',
  `SeqType` int(11) NULL DEFAULT 0 COMMENT '0 默認序號 1首位36位字符',
  `FirstIdx` int(11) NULL DEFAULT 0 COMMENT '首位索引值 type1有效',
  PRIMARY KEY (`Name`, `CurrDate`, `Num`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '序號表' ROW_FORMAT = Dynamic;

CREATE DEFINER=`root`@`%` FUNCTION `seq`(`seq_name` varchar(50),is_date_head int,`len` int) RETURNS varchar(50) CHARSET utf8
BEGIN
    -- 函數(shù)說明 為對應的序號標識生產指定長度的序號 
    -- 2024-10-19 11:13:11 Jevan
    
    -- seq_name 序號標識，一般為表名做為標識
    -- is_date_head 序號頭部是否生成以的日期格式(241019)六位字符串，1是 其他則否
    -- len 要生成的序號長度(日期格式(241019)六位字符串不計在內)，如傳6，則為000001

    DECLARE curr_date date;
    set curr_date = CURDATE();
    
    UPDATE seq SET Num=last_insert_id(Num + StepLen) WHERE name=seq_name and CurrDate=curr_date and SeqType=0;
    IF ROW_COUNT() = 0 then
        insert into seq (Name,CurrDate,Num,StepLen,SeqType,FirstIdx) select seq_name,curr_date,last_insert_id(1),StepLen,0,0 from seq where name=seq_name and SeqType=0 and CurrDate!=curr_date order by CurrDate desc limit 1;
        if Row_Count() =0 then
            insert into seq (Name,CurrDate,Num,StepLen,SeqType,FirstIdx) values(seq_name,curr_date,last_insert_id(1),1,0,0);
        end if;
    end if;
    
    if is_date_head =1 then
        return CONCAT(DATE_FORMAT(curr_date, '%y%m%d'),lpad(last_insert_id(),len,0));
    ELSE
        return lpad(last_insert_id(),len,0);
    end if;
END

CREATE DEFINER=`root`@`%` FUNCTION `seq_extend`(`seq_name` varchar(50),is_date_head int,`len` int) RETURNS varchar(50) CHARSET utf8
BEGIN
    -- 函數(shù)說明 為對應的序號標識生產指定長度的序號，序號首位通過36位字符(0-Z)進行升序, 
    -- 注：未處理Z后的升序；len 必須>=2
    -- 2024-10-19 11:13:11 Jevan
    
    -- seq_name 序號標識，一般為表名做為標識
    -- is_date_head 序號頭部是否生成以的日期格式(241019)六位字符串，1是 其他則否
    -- len 要生成的序號長度(日期格式(241019)六位字符串不計在內)，如傳6，則為Z00001
    
    DECLARE curr_idx int;
    DECLARE curr_date date;
    DECLARE char_str varchar(50);
    
    set curr_date = CURDATE();
    set curr_idx=-1;    
    set char_str = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    
  update seq set Num=last_insert_id(Num + StepLen) where `Name`=seq_name and CurrDate=curr_date and SeqType=1;
    
     IF ROW_COUNT() = 0 then
        set curr_idx=0;
        insert into seq (Name,CurrDate,Num,StepLen,SeqType,FirstIdx) select seq_name,curr_date,last_insert_id(1),StepLen,1,curr_idx from seq where name=seq_name and CurrDate!=curr_date and SeqType=1 order by CurrDate desc limit 1;
        if Row_Count() =0 then
            insert into seq (Name,CurrDate,Num,StepLen,SeqType,FirstIdx) values(seq_name,curr_date,last_insert_id(1),1,1,curr_idx);
        end if;
     end if;
     
     if curr_idx=-1 THEN
        select FirstIdx into curr_idx from seq where `Name`=seq_name and CurrDate=curr_date and SeqType=1;
     end if;
     
     if LENGTH(last_insert_id())>(len-1) THEN
        set curr_idx=curr_idx+1;
        update seq set num=last_insert_id(1),FirstIdx = FirstIdx+1 where `Name`=seq_name and CurrDate=curr_date and SeqType=1;
     end if;
     
     if is_date_head = 1 then
        return CONCAT(DATE_FORMAT(curr_date, '%y%m%d'),SUBSTRING(char_str,curr_idx+1,1),lpad(last_insert_id(),len-1,0));
     ELSE
        return CONCAT(SUBSTRING(char_str,curr_idx+1,1),lpad(last_insert_id(),len-1,0));
     end if;
END