客户增加线索创建时间取线索的创建时间

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='lead_createdtime'; 
SET @fieldname='lead_createdtime'; 
SET @zh='线索创建时间'; 
SET @en='线索创建时间'; 
SET @uitype=70; 
SET @typeofdata='DT~O';
SET @quickcreate=1;
SET @displaytype=2;
SET @bigtype='datetime';
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,'DATETIME','10',NULL);

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

 

DROP TRIGGER IF EXISTS `ldcrm_resetaccountinfo_leads_deleted`;
DELIMITER ;;
CREATE TRIGGER `ldcrm_resetaccountinfo_leads_deleted` BEFORE DELETE ON `ld_leads` FOR EACH ROW 
BEGIN

    IF @disable_triggers IS NULL AND OLD.deleted=0 THEN
        IF EXISTS (SELECT 1 FROM ld_account WHERE deleted=0 AND account_no=OLD.leads_no AND createdtime BETWEEN DATE_SUB(NOW(),INTERVAL 2 SECOND) AND NOW()) THEN
            UPDATE ld_account SET lead_createdtime=OLD.createdtime WHERE deleted=0 AND kis_no='from_leads' AND createdtime BETWEEN DATE_SUB(NOW(),INTERVAL 2 SECOND) AND NOW() AND IFNULL(lead_createdtime,'')='' AND account_no=OLD.leads_no ORDER BY accountid DESC LIMIT 1;
        ELSEIF EXISTS (SELECT 1 FROM ld_account WHERE deleted=0 AND accountname=OLD.accountname AND createdtime BETWEEN DATE_SUB(NOW(),INTERVAL 2 SECOND) AND NOW()) THEN
            UPDATE ld_account SET lead_createdtime=OLD.createdtime WHERE deleted=0 AND kis_no='from_leads' AND createdtime BETWEEN DATE_SUB(NOW(),INTERVAL 2 SECOND) AND NOW() AND IFNULL(lead_createdtime,'')='' AND accountname=OLD.accountname ORDER BY accountid DESC LIMIT 1;
        ELSE
            UPDATE ld_account SET lead_createdtime=OLD.createdtime WHERE deleted=0 AND kis_no='from_leads' AND createdtime BETWEEN DATE_SUB(NOW(),INTERVAL 2 SECOND) AND NOW() AND IFNULL(lead_createdtime,'')='' ORDER BY accountid DESC LIMIT 1;
        END IF;
    END IF;

END
;;
DELIMITER ;

客户增加线索创建时间取线索的创建时间.sql