灵当CRM

 找回密码
 立即注册
查看: 2688|回复: 0

合同订单和报价单模块增加“未税金额小计”字段方法(用于打印)

[复制链接]

1660

主题

1709

帖子

5万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
59056
 楼主| 发表于 2019-12-16 17:03:17 | 显示全部楼层 |阅读模式
问题:如何在合同订单、报价单模块中增加“未税金额小计”(用于打印)



解决方法:执行下面脚本即可(只适用于合同和报价单没有做过个性化开发的账套)

SET @modulename='Quotes';
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='subtotal_listamout';
SET @fieldname='subtotal_listamout';
SET @zh='未税总价小计';
SET @en='未税总价小计';
SET @uitype=71;
SET @typeofdata='N~O~25,2';
SET @quickcreate=1;
SET @displaytype=3;
SET @bigtype='currency';
SET @datarang=NULL;
SET @maxrowsshow=0;


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,'VARCHAR','100',NULL);
ALTER TABLE ld_quotes MODIFY COLUMN subtotal_listamout DECIMAL(25,2);


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


DROP TRIGGER IF EXISTS `reset_zongjia_info_edit`;
DELIMITER ;;
CREATE TRIGGER `reset_zongjia_info_edit` BEFORE UPDATE ON `ld_quotes` FOR EACH ROW
BEGIN
               
        SET NEW.subtotal_listamout=(SELECT SUM(list_amount) FROM ld_quotesdetail WHERE id=NEW.quoteid);


END
;;
DELIMITER ;


SET @modulename='SalesOrder';
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='subtotal_listamout';
SET @fieldname='subtotal_listamout';
SET @zh='未税总价小计';
SET @en='未税总价小计';
SET @uitype=71;
SET @typeofdata='N~O~25,2';
SET @quickcreate=1;
SET @displaytype=3;
SET @bigtype='currency';
SET @datarang=NULL;
SET @maxrowsshow=0;


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,'VARCHAR','100',NULL);
ALTER TABLE ld_salesorder MODIFY COLUMN subtotal_listamout DECIMAL(25,2);


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




DROP TRIGGER IF EXISTS `update_by_salesorder1`;
DELIMITER ;;
CREATE TRIGGER `update_by_salesorder1` BEFORE UPDATE ON `ld_salesorder` FOR EACH ROW BEGIN


        IF NEW.triggercount<>OLD.triggercount THEN
                SET NEW.subtotal_listamout=(SELECT SUM(list_amount) FROM ld_salesorderdetail WHERE id=NEW.salesorderid);
                SET NEW.receivable_balance=ROUND(IFNULL(NEW.total,0)-IFNULL(NEW.received_amount,0),2);
        END IF;


END
;;
DELIMITER ;




UPDATE ld_quotes SET subtotal_listamout=0.07;
UPDATE ld_salesorder SET triggercount=triggercount+1;

回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

灵当CRM

GMT+8, 2024-3-29 07:52 , Processed in 0.049031 second(s), 17 queries .

Powered by Discuz! X3.4

Copyright © 2008-2022, Tencent Cloud.

沪ICP备08110973号-2

快速回复 返回顶部 返回列表