客户增加首次收款出库日期最新收款出库日期

一、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='smcreatorid' 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='smcreatorid' LIMIT 1);
SET @columnname='first_cashdate'; 
SET @fieldname='first_cashdate'; 
SET @zh='首次收款日期'; 
SET @en='首次收款日期'; 
SET @uitype=5; 
SET @typeofdata='D~V';
SET @quickcreate=1;
SET @displaytype=2;
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);

 

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='smcreatorid' 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='smcreatorid' LIMIT 1);
SET @columnname='last_cashdate'; 
SET @fieldname='last_cashdate'; 
SET @zh='最新收款日期'; 
SET @en='最新收款日期'; 
SET @uitype=5; 
SET @typeofdata='D~V';
SET @quickcreate=1;
SET @displaytype=2;
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);

 

 

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='smcreatorid' 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='smcreatorid' LIMIT 1);
SET @columnname='first_outdate'; 
SET @fieldname='first_outdate'; 
SET @zh='首次出库日期'; 
SET @en='首次出库日期'; 
SET @uitype=5; 
SET @typeofdata='D~V';
SET @quickcreate=1;
SET @displaytype=2;
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);

 

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='smcreatorid' 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='smcreatorid' LIMIT 1);
SET @columnname='last_outdate'; 
SET @fieldname='last_outdate'; 
SET @zh='最新出库日期'; 
SET @en='最新出库日期'; 
SET @uitype=5; 
SET @typeofdata='D~V';
SET @quickcreate=1;
SET @displaytype=2;
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);

 

DROP TRIGGER IF EXISTS `update_salesorder_received1`;
DELIMITER ;;
CREATE TRIGGER `update_salesorder_received1` AFTER UPDATE ON `ld_cashbill` FOR EACH ROW 
BEGIN

    IF @disable_triggers IS NULL THEN
        CALL update_sales_total(NEW.cashbillid);

        SET @disable_triggers='abcdefg';
        UPDATE (
            SELECT
                a.accountid,
                MIN(a.cashbilldate) AS first_date,
                MAX(a.cashbilldate) AS last_date
            FROM ld_cashbill a
            WHERE a.deleted=0
                AND a.approvestatus<>'已拒绝'
                AND a.accountid>0
                AND a.accountid IN (NEW.accountid,OLD.accountid)
            GROUP BY a.accountid
        ) a
        INNER JOIN ld_account b ON b.accountid=a.accountid
        SET
            b.first_cashdate=a.first_date,
            b.last_cashdate=a.last_date;
        SET @disable_triggers=NULL;
    END IF;

END
;;
DELIMITER ;

DROP TRIGGER IF EXISTS `reset_billinfo_after_outboundorder_update`;
DELIMITER ;;
CREATE TRIGGER `reset_billinfo_after_outboundorder_update` AFTER UPDATE ON `ld_outboundorder` FOR EACH ROW 
BEGIN

    IF @disable_triggers IS NULL THEN
        SET @disable_triggers='abcdefg';
        UPDATE (
            SELECT
                a.accountid,
                MIN(a.outdate) AS first_date,
                MAX(a.outdate) AS last_date
            FROM ld_outboundorder a
            WHERE a.deleted=0
                AND a.approvestatus<>'已拒绝'
                AND a.accountid>0
                AND a.accountid IN (NEW.accountid,OLD.accountid)
            GROUP BY a.accountid
        ) a
        INNER JOIN ld_account b ON b.accountid=a.accountid
        SET
            b.first_outdate=a.first_date,
            b.last_outdate=a.last_date;
        SET @disable_triggers=NULL;
    END IF;

END
;;
DELIMITER ;

 

-- 历史数据
SET @disable_triggers='abcdefg';
UPDATE (
    SELECT
        a.accountid,
        MIN(a.cashbilldate) AS first_date,
        MAX(a.cashbilldate) AS last_date
    FROM ld_cashbill a
    WHERE a.deleted=0
        AND a.approvestatus<>'已拒绝'
        AND a.accountid>0
    GROUP BY a.accountid
) a
INNER JOIN ld_account b ON b.accountid=a.accountid
SET
    b.first_cashdate=a.first_date,
    b.last_cashdate=a.last_date;

UPDATE (
    SELECT
        a.accountid,
        MIN(a.outdate) AS first_date,
        MAX(a.outdate) AS last_date
    FROM ld_outboundorder a
    WHERE a.deleted=0
        AND a.approvestatus<>'已拒绝'
        AND a.accountid>0
    GROUP BY a.accountid
) a
INNER JOIN ld_account b ON b.accountid=a.accountid
SET
    b.first_outdate=a.first_date,
    b.last_outdate=a.last_date;
SET @disable_triggers=NULL;

客户增加首次收款出库日期最新收款出库日期.sql