一、demo脚本
SET @modulename='Accounts';
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='singin_cnt';
SET @fieldname='singin_cnt';
SET @zh='签到次数';
SET @en='签到次数';
SET @uitype=7;
SET @typeofdata='NN~O~13,0';
SET @quickcreate=1;
SET @displaytype=2;
SET @bigtype='number';
SET @datarang=NULL;
SET @maxrowsshow=0;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)
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 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,'INT','11',NULL);
CALL getallprofile(@modulename);
CALL fieldprofile(@modulename);
SET @modulename='Accounts';
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='singin_date';
SET @fieldname='singin_date';
SET @zh='最新签到日期';
SET @en='最新签到日期';
SET @uitype=5;
SET @typeofdata='D~O';
SET @quickcreate=1;
SET @displaytype=1;
SET @bigtype='date';
SET @datarang=NULL;
SET @maxrowsshow=0;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)
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 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,'DATE','10',NULL);
CALL getallprofile(@modulename);
CALL fieldprofile(@modulename);
SET @disable_triggers=1;
UPDATE ld_account a
LEFT JOIN (
SELECT
record,
COUNT(1) AS cnt,
MAX(createdtime) AS max_time
FROM ld_singin
WHERE deleted=0
AND tabid=6
AND record>0
GROUP BY record
) b ON a.accountid=b.record
SET
a.singin_cnt=IFNULL(b.cnt,0),
a.singin_date=DATE_FORMAT(b.max_time,'%Y-%m-%d');
SET @disable_triggers=NULL;
DROP TRIGGER IF EXISTS `resert_accountinfo_singininsert`;
DELIMITER ;;
CREATE TRIGGER `resert_accountinfo_singininsert` AFTER INSERT ON `ld_singin` FOR EACH ROW
BEGINIF @disable_triggers IS NULL AND NEW.tabid=6 AND NEW.record>0 THEN
UPDATE ld_account a
LEFT JOIN (
SELECT
record,
COUNT(1) AS cnt,
MAX(createdtime) AS max_time
FROM ld_singin
WHERE deleted=0
AND tabid=6
AND record=NEW.record
GROUP BY record
) b ON a.accountid=b.record
SET
a.singin_cnt=IFNULL(b.cnt,0),
a.singin_date=DATE_FORMAT(b.max_time,'%Y-%m-%d')
WHERE a.accountid=NEW.record;
END IF;END
;;
DELIMITER ;
DROP TRIGGER IF EXISTS `resert_accountinfo_singinupdate`;
DELIMITER ;;
CREATE TRIGGER `resert_accountinfo_singinupdate` AFTER UPDATE ON `ld_singin` FOR EACH ROW
BEGINIF @disable_triggers IS NULL AND NEW.tabid=6 AND NEW.record>0 AND NEW.deleted=1 THEN
UPDATE ld_account a
LEFT JOIN (
SELECT
record,
COUNT(1) AS cnt,
MAX(createdtime) AS max_time
FROM ld_singin
WHERE deleted=0
AND tabid=6
AND record=NEW.record
GROUP BY record
) b ON a.accountid=b.record
SET
a.singin_cnt=IFNULL(b.cnt,0),
a.singin_date=DATE_FORMAT(b.max_time,'%Y-%m-%d')
WHERE a.accountid=NEW.record;
END IF;
END
;;
DELIMITER ;