联系人是否20天内生日

一、demo脚本

SET @modulename='Contacts';        -- 这里模块名需要根据需要修改
SET @tabid=(SELECT tabid FROM ld_tab WHERE `name`=@modulename);
SET @relateid=(SELECT block FROM ld_field WHERE tabid=@tabid AND columnname='smownerid' LIMIT 1);
SET @maxseq=IFNULL((SELECT MAX(sequence) FROM ld_field WHERE tabid=@tabid AND block=@relateid),0);
SET @trantable='ld_field';
SET @tablename=(SELECT tablename FROM ld_field WHERE tabid=@tabid AND columnname='smownerid' LIMIT 1);
SET @columnname='isnear_birthday';     -- 这里 columnname 需要根据需要修改
SET @fieldname='isnear_birthday';     -- 这里 fieldname  需要根据需要修改
SET @zh='是否20天内生日';                 -- 这里中文标签根据需要修改
SET @en='是否20天内生日';                 -- 这里应文标签根据需要修改
SET @uitype=1; 
SET @typeofdata='V~O';
SET @quickcreate=1;
SET @displaytype=2;                    -- displaytype=2 表示只在查看界面显示
SET @bigtype='text';
SET @datarang=NULL;
SET @maxrowsshow=0;
SET @allowedit='yes';

SET @maxid=(SELECT MAX(fieldid) FROM ld_field);
INSERT INTO ld_field(tabid,fieldid,columnname,tablename,generatedtype,uitype,fieldname,fieldlabel,readonly,presence,defaultvalue,maximumlength,sequence,block,displaytype,typeofdata,quickcreate,quickcreatesequence,info_type,masseditable,helpinfo,fieldonly,approve_demand,is_checkimportfield,bigtype,datarang,maxrowsshow,allowedit)
SELECT @tabid,@maxid+1,@columnname,@tablename,1,@uitype,@fieldname,@zh,1,2,NULL,100,@maxseq+1,@relateid,@displaytype,@typeofdata,@quickcreate,NULL,NULL,1,NULL,0,2,0,@bigtype,@datarang,@maxrowsshow,@allowedit from dual 
WHERE NOT EXISTS (SELECT 1 FROM ld_field WHERE tabid=@tabid AND tablename=@tablename AND columnname=@columnname); 

UPDATE ld_field_seq SET id=(SELECT max(fieldid) FROM ld_field);

SET @relateid=(SELECT fieldid FROM ld_field WHERE tabid=@tabid AND columnname=@columnname LIMIT 1);

INSERT INTO ld_tranlabel(languagetype,trantable,tabid,relateid,fieldlabel)
SELECT 'zh',@trantable,@tabid,@relateid,@zh FROM DUAL 
WHERE NOT EXISTS (SELECT 1 FROM ld_tranlabel WHERE languagetype = 'zh' AND trantable =@trantable AND tabid = @tabid AND relateid=@relateid);

INSERT INTO ld_tranlabel(languagetype,trantable,tabid,relateid,fieldlabel)
SELECT 'en',@trantable,@tabid,@relateid,@en FROM DUAL 
WHERE NOT EXISTS (SELECT 1 FROM ld_tranlabel WHERE languagetype = 'en' AND trantable =@trantable AND tabid = @tabid AND relateid=@relateid);

CALl AddColumn(@tablename,@columnname,'VARCHAR','10','');

CALL getallprofile(@modulename);
CALL fieldprofile(@modulename);

 

-- 01.重算是否最近20天生日触发器
DROP PROCEDURE IF EXISTS `bjc_reset_contactdetailsinfo`;
delimiter ||
CREATE  PROCEDURE `bjc_reset_contactdetailsinfo`(
)
BEGIN

SET @disable_triggers=1;
UPDATE ld_contactdetails a
SET
    isnear_birthday=(
        CASE
            WHEN a.birthday>'1600-01-01' AND DATE_ADD(a.birthday,INTERVAL YEAR(NOW())-YEAR(a.birthday) YEAR)>=CURDATE() THEN IF(DATEDIFF(DATE_ADD(a.birthday,INTERVAL YEAR(NOW())-YEAR(a.birthday) YEAR),CURDATE())<=20,'是','否')
            WHEN a.birthday>'1600-01-01' AND DATE_ADD(a.birthday,INTERVAL YEAR(NOW())-YEAR(a.birthday) YEAR)<CURDATE()  THEN IF(DATEDIFF(DATE_ADD(a.birthday,INTERVAL YEAR(NOW())-YEAR(a.birthday)+1 YEAR),CURDATE())<=20,'是','否')
            ELSE '否'
        END)
WHERE IFNULL(isnear_birthday,'')<>(
        CASE
            WHEN a.birthday>'1600-01-01' AND DATE_ADD(a.birthday,INTERVAL YEAR(NOW())-YEAR(a.birthday) YEAR)>=CURDATE() THEN IF(DATEDIFF(DATE_ADD(a.birthday,INTERVAL YEAR(NOW())-YEAR(a.birthday) YEAR),CURDATE())<=20,'是','否')
            WHEN a.birthday>'1600-01-01' AND DATE_ADD(a.birthday,INTERVAL YEAR(NOW())-YEAR(a.birthday) YEAR)<CURDATE()  THEN IF(DATEDIFF(DATE_ADD(a.birthday,INTERVAL YEAR(NOW())-YEAR(a.birthday)+1 YEAR),CURDATE())<=20,'是','否')
            ELSE '否'
        END);

SET @disable_triggers=NULL;

end ||
delimiter;

-- 02.增加事件每天重算
DROP EVENT IF EXISTS `bjc_reset_contactdetailsinfo_event`;
delimiter ||
CREATE  EVENT `bjc_reset_contactdetailsinfo_event` ON SCHEDULE EVERY 1 DAY STARTS '2023-01-06 00:00:01' ON COMPLETION NOT PRESERVE ENABLE DO 
CALL bjc_reset_contactdetailsinfo();
||
delimiter;

 

-- 03.增加触发器联系人新增修改的时候重算
DROP TRIGGER IF EXISTS `trigger_before_contactdetails_update`;
DELIMITER ;;
CREATE TRIGGER `trigger_before_contactdetails_update` BEFORE UPDATE ON `ld_contactdetails` FOR EACH ROW BEGIN

    IF @disable_triggers IS NULL THEN
        IF NEW.birthday>'1600-01-01' THEN
            IF DATE_ADD(NEW.birthday,INTERVAL YEAR(NOW())-YEAR(NEW.birthday) YEAR)>=CURDATE() THEN
                SET NEW.isnear_birthday=IF(DATEDIFF(DATE_ADD(NEW.birthday,INTERVAL YEAR(NOW())-YEAR(NEW.birthday) YEAR),CURDATE())<=20,'是','否');
            ELSE
                SET NEW.isnear_birthday=IF(DATEDIFF(DATE_ADD(NEW.birthday,INTERVAL YEAR(NOW())-YEAR(NEW.birthday)+1 YEAR),CURDATE())<=20,'是','否');
            END IF;
        ELSE
            SET NEW.isnear_birthday='否';
        END IF;
    END IF;

END
;;
DELIMITER ;

DROP TRIGGER IF EXISTS `trigger_before_contactdetails_insert`;
DELIMITER ;;
CREATE TRIGGER `trigger_before_contactdetails_insert` BEFORE INSERT ON `ld_contactdetails` FOR EACH ROW BEGIN

    IF @disable_triggers IS NULL THEN
        IF NEW.birthday>'1600-01-01' THEN
            IF DATE_ADD(NEW.birthday,INTERVAL YEAR(NOW())-YEAR(NEW.birthday) YEAR)>=CURDATE() THEN
                SET NEW.isnear_birthday=IF(DATEDIFF(DATE_ADD(NEW.birthday,INTERVAL YEAR(NOW())-YEAR(NEW.birthday) YEAR),CURDATE())<=20,'是','否');
            ELSE
                SET NEW.isnear_birthday=IF(DATEDIFF(DATE_ADD(NEW.birthday,INTERVAL YEAR(NOW())-YEAR(NEW.birthday)+1 YEAR),CURDATE())<=20,'是','否');
            END IF;
        ELSE
            SET NEW.isnear_birthday='否';
        END IF;
    END IF;

END
;;
DELIMITER ;

联系人是否20天内生日.sql