侧边栏壁纸
博主头像
月光一线博主等级

  • 累计撰写 45 篇文章
  • 累计创建 1 个标签
  • 累计收到 1 条评论

目 录CONTENT

文章目录
用友U9

【037】U9手动更新菜单发布包

绀香廿九
绀香廿九
2023-10-28 / 0 评论 / 0 点赞 / 31 阅读 / 18717 字

使用存储过程生成要设置的SQL语句

此处以小辣椒为例子，新增一个【BOM子项替代关系】【BOM子项替代列表】的页面，然后还有对应的BE跟UI以及LIstUI的DLL，以及对应的菜单

if object_id('tempdb..#insertSQL') is not null  
	drop table #insertSQL
create table #insertSQL(seq int IDENTITY(1,1),sqlstr nvarchar(max))

insert into #insertSQL(sqlstr) values('-- 页面[BOM子项替代关系]');
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_Page]', 'Code = ''5fdb9458-9d7f-4cb1-8d11-ae25470707d8''';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_Page_Trl]', 'ID in (select ID from UBF_Assemble_Page where Code = ''5fdb9458-9d7f-4cb1-8d11-ae25470707d8'')';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_PageColumn]', 'Page in (select ID from UBF_Assemble_Page where Code = ''5fdb9458-9d7f-4cb1-8d11-ae25470707d8'')';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_PageColumn_Trl]', 'ID IN (select ID from UBF_Assemble_PageColumn where Page in (select ID from UBF_Assemble_Page where Code = ''5fdb9458-9d7f-4cb1-8d11-ae25470707d8''))';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_Part]', 'Page in (select ID from UBF_Assemble_Page where Code = ''5fdb9458-9d7f-4cb1-8d11-ae25470707d8'')';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_Part_Trl]', 'ID in (select ID from UBF_Assemble_Part where Page in (select ID from UBF_Assemble_Page where Code = ''5fdb9458-9d7f-4cb1-8d11-ae25470707d8''))';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_ColumnPart]', 'PageColumn IN (select ID from UBF_Assemble_PageColumn where Page in (select ID from UBF_Assemble_Page where Code = ''5fdb9458-9d7f-4cb1-8d11-ae25470707d8''))';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_ParamBinding]', 'PageParameter IN (select ID from UBF_Assemble_PageParameter where Page in (select ID from UBF_Assemble_Page where Code = ''5fdb9458-9d7f-4cb1-8d11-ae25470707d8''))';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_PageParameter]', 'Page in (select ID from UBF_Assemble_Page where Code = ''5fdb9458-9d7f-4cb1-8d11-ae25470707d8'')';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_PartRelation]', 'Page in (select ID from UBF_Assemble_Page where Code = ''5fdb9458-9d7f-4cb1-8d11-ae25470707d8'')';

insert into #insertSQL(sqlstr) values('-- 页面[BOM子项替代关系列表]');
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_Page]', 'Code = ''ae537b14-55d9-48e4-ba75-f33b2f8d2b08''';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_Page_Trl]', 'ID in (select ID from UBF_Assemble_Page where Code = ''ae537b14-55d9-48e4-ba75-f33b2f8d2b08'')';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_PageColumn]', 'Page in (select ID from UBF_Assemble_Page where Code = ''ae537b14-55d9-48e4-ba75-f33b2f8d2b08'')';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_PageColumn_Trl]', 'ID IN (select ID from UBF_Assemble_PageColumn where Page in (select ID from UBF_Assemble_Page where Code = ''ae537b14-55d9-48e4-ba75-f33b2f8d2b08''))';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_Part]', 'Page in (select ID from UBF_Assemble_Page where Code = ''ae537b14-55d9-48e4-ba75-f33b2f8d2b08'')';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_Part_Trl]', 'ID in (select ID from UBF_Assemble_Part where Page in (select ID from UBF_Assemble_Page where Code = ''ae537b14-55d9-48e4-ba75-f33b2f8d2b08''))';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_ColumnPart]', 'PageColumn IN (select ID from UBF_Assemble_PageColumn where Page in (select ID from UBF_Assemble_Page where Code = ''ae537b14-55d9-48e4-ba75-f33b2f8d2b08''))';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_ParamBinding]', 'PageParameter IN (select ID from UBF_Assemble_PageParameter where Page in (select ID from UBF_Assemble_Page where Code = ''ae537b14-55d9-48e4-ba75-f33b2f8d2b08''))';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_PageParameter]', 'Page in (select ID from UBF_Assemble_Page where Code = ''ae537b14-55d9-48e4-ba75-f33b2f8d2b08'')';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_PartRelation]', 'Page in (select ID from UBF_Assemble_Page where Code = ''ae537b14-55d9-48e4-ba75-f33b2f8d2b08'')';

insert into #insertSQL(sqlstr) values('-- DLL');
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_AppComponents]', 'Component = ''UFIDA.U9.SH.PubUI.WebPart.dll''';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_AppComponents]', 'Component = ''UFIDA.U9.SH.PubQryListUI.WebPart.dll''';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_AppComponents]', 'Component = ''UFIDA.U9.SH.PubBE.dll''';

insert into #insertSQL(sqlstr) values('-- 菜单[客开]');
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_Menu]', 'Code = ''a6dcc019-7877-4c5a-9a2f-e50653104337''';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_Menu_Trl]', 'ID IN (select ID from UBF_Assemble_Menu where Code = ''a6dcc019-7877-4c5a-9a2f-e50653104337'')';

insert into #insertSQL(sqlstr) values('-- 菜单[BOM子项替代关系]');
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_Menu]', 'Code = ''25f8fca6-3ce5-425d-9956-1d108ebc84f3''';
insert into #insertSQL(sqlstr) exec proc_insert '[dbo].[UBF_Assemble_Menu_Trl]', 'ID IN (select ID from UBF_Assemble_Menu where Code = ''25f8fca6-3ce5-425d-9956-1d108ebc84f3'')';

select * from #insertSQL order by seq;

生成Insert语句的存储过程

/****** Object:  StoredProcedure [dbo].[proc_insert]    Script Date: 2023/9/21 13:04:42 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		qxq
-- Create date: 2023/9/21
-- Description:	生成单表的Insert语句
-- =============================================
CREATE OR ALTER proc [dbo].[Proc_Insert] (
	@tablename varchar(256), -- 表名
	@where varchar(max) -- 条件
)
as
begin
	set nocount on
	declare @sqlstr varchar(MAX)
	declare @sqlstr1 varchar(MAX)
	declare @sqlstr2 varchar(MAX)
	select @sqlstr='select ''INSERT '+@tablename
	select @sqlstr1=''
	select @sqlstr2=' ('
	select @sqlstr1= ' VALUES ( ''+'
	select @sqlstr1=@sqlstr1+col+'+'',''+' ,@sqlstr2=@sqlstr2+name +',' from (select case 
	-- when a.xtype =173 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
	when a.xtype =36 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
	when a.xtype =127 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(20),'+a.name +')'+' end'
	when a.xtype =104 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(1),'+a.name +')'+' end'
	when a.xtype =175 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
	when a.xtype =61 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'
	when a.xtype =106 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name +')'+' end'
	when a.xtype =62 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'
	when a.xtype =56 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(11),'+a.name +')'+' end'
	when a.xtype =60 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'
	when a.xtype =239 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
	when a.xtype =108 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name +')'+' end'
	when a.xtype =231 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
	when a.xtype =59 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'
	when a.xtype =58 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'
	when a.xtype =52 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(12),'+a.name +')'+' end'
	when a.xtype =122 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'
	when a.xtype =48 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(6),'+a.name +')'+' end'
	-- when a.xtype =165 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
	when a.xtype =167 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
	else '''NULL'''
	end as col,a.colid,a.name
	from syscolumns a
	where a.id = object_id(@tablename) and a.xtype <>189 and a.xtype <>34 and a.xtype <>35 -- and a.xtype <>36
	AND COLUMNPROPERTY(a.id, a.name, 'IsIdentity') <> 1
	)t order by colid
	select @sqlstr=@sqlstr+left(@sqlstr2,len(@sqlstr2)-1)+') '+left(@sqlstr1,len(@sqlstr1)-3)+')'' from '+@tablename + ' where '+@where
	-- print @sqlstr
	
	exec(@sqlstr)

	set nocount off
end
GO

执行SQL

在执行完成的结果集，选中sqlstr列，Ctrl+C复制到新的查询窗口执行即可
其中的主键ID可使用系统获取ID的存储过程替换

执行结果如下，选中sqlstr列，Ctrl+C复制出来即可
bd9f7273207549549b8c4951ae32aeb5.png

U9获取ID

declare	@SNIndex bigint
if object_id('InnerAllocSerials') is null
	exec	[dbo].[AllocSerials]
			@AllocCount = 20000,
			@StartSN = @SNIndex output

else
	exec	[dbo].[InnerAllocSerials]
			@AllocCount = 20000,
			@StartSN = @SNIndex output

手动卸载菜单发布

以小辣椒刚刚新增的为例子

删除页面

--删除页面[BOM子项替代关系]
DELETE FROM UBF_Assemble_PartRelation
WHERE Page in (select ID from UBF_Assemble_Page where Code = '5fdb9458-9d7f-4cb1-8d11-ae25470707d8')
;
DELETE FROM UBF_Assemble_PageParameter
WHERE Page in (select ID from UBF_Assemble_Page where Code = '5fdb9458-9d7f-4cb1-8d11-ae25470707d8')
;
with
	PageID as
	(select ID from UBF_Assemble_Page where Code = '5fdb9458-9d7f-4cb1-8d11-ae25470707d8'),
	PageParameterID as
	(select ID from UBF_Assemble_PageParameter where Page in (select ID from PageID))
DELETE FROM UBF_Assemble_ParamBinding
WHERE PageParameter in (select ID from PageParameterID)
;
with
	PageID as
	(select ID from UBF_Assemble_Page where Code = '5fdb9458-9d7f-4cb1-8d11-ae25470707d8'),
	PagePartID as
	(select ID from UBF_Assemble_Part where Page in (select ID from PageID))
DELETE FROM UBF_Assemble_Part_Trl WHERE ID in (select ID from PagePartID)
;
DELETE FROM UBF_Assemble_Part
WHERE Page in (select ID from UBF_Assemble_Page where Code = '5fdb9458-9d7f-4cb1-8d11-ae25470707d8')
;
with
	PageID as
	(select ID from UBF_Assemble_Page where Code = '5fdb9458-9d7f-4cb1-8d11-ae25470707d8'),
	PageColumnID as
	(select ID from UBF_Assemble_PageColumn where Page in (select ID from PageID))
DELETE FROM UBF_Assemble_ColumnPart
WHERE PageColumn in (select ID from PageColumnID)
;
with
	PageID as
	(select ID from UBF_Assemble_Page where Code = '5fdb9458-9d7f-4cb1-8d11-ae25470707d8'),
	PageColumnID as
	(select ID from UBF_Assemble_PageColumn where Page in (select ID from PageID))
DELETE FROM UBF_Assemble_PageColumn_Trl
WHERE ID in (select ID from PageColumnID)
;
DELETE FROM UBF_Assemble_PageColumn
WHERE Page in (select ID from UBF_Assemble_Page where Code = '5fdb9458-9d7f-4cb1-8d11-ae25470707d8')
;
DELETE FROM UBF_Assemble_Page_Trl
WHERE ID in (select ID from UBF_Assemble_Page where Code = '5fdb9458-9d7f-4cb1-8d11-ae25470707d8')
;
DELETE FROM UBF_Assemble_Page
WHERE Code = '5fdb9458-9d7f-4cb1-8d11-ae25470707d8'
;

--删除页面[BOM子项替代关系列表]
DELETE FROM UBF_Assemble_PartRelation
WHERE Page in (select ID from UBF_Assemble_Page where Code = 'ae537b14-55d9-48e4-ba75-f33b2f8d2b08')
;
DELETE FROM UBF_Assemble_PageParameter
WHERE Page in (select ID from UBF_Assemble_Page where Code = 'ae537b14-55d9-48e4-ba75-f33b2f8d2b08')
;
with
	PageID as
	(select ID from UBF_Assemble_Page where Code = 'ae537b14-55d9-48e4-ba75-f33b2f8d2b08'),
	PageParameterID as
	(select ID from UBF_Assemble_PageParameter where Page in (select ID from PageID))
DELETE FROM UBF_Assemble_ParamBinding
WHERE PageParameter in (select ID from PageParameterID)
;
with
	PageID as
	(select ID from UBF_Assemble_Page where Code = 'ae537b14-55d9-48e4-ba75-f33b2f8d2b08'),
	PagePartID as
	(select ID from UBF_Assemble_Part where Page in (select ID from PageID))
DELETE FROM UBF_Assemble_Part_Trl WHERE ID in (select ID from PagePartID)
;
DELETE FROM UBF_Assemble_Part
WHERE Page in (select ID from UBF_Assemble_Page where Code = 'ae537b14-55d9-48e4-ba75-f33b2f8d2b08')
;
with
	PageID as
	(select ID from UBF_Assemble_Page where Code = 'ae537b14-55d9-48e4-ba75-f33b2f8d2b08'),
	PageColumnID as
	(select ID from UBF_Assemble_PageColumn where Page in (select ID from PageID))
DELETE FROM UBF_Assemble_ColumnPart
WHERE PageColumn in (select ID from PageColumnID)
;
with
	PageID as
	(select ID from UBF_Assemble_Page where Code = 'ae537b14-55d9-48e4-ba75-f33b2f8d2b08'),
	PageColumnID as
	(select ID from UBF_Assemble_PageColumn where Page in (select ID from PageID))
DELETE FROM UBF_Assemble_PageColumn_Trl
WHERE ID in (select ID from PageColumnID)
;
DELETE FROM UBF_Assemble_PageColumn
WHERE Page in (select ID from UBF_Assemble_Page where Code = 'ae537b14-55d9-48e4-ba75-f33b2f8d2b08')
;
DELETE FROM UBF_Assemble_Page_Trl
WHERE ID in (select ID from UBF_Assemble_Page where Code = 'ae537b14-55d9-48e4-ba75-f33b2f8d2b08')
;
DELETE FROM UBF_Assemble_Page
WHERE Code = 'ae537b14-55d9-48e4-ba75-f33b2f8d2b08'
;

删除DLL

DELETE FROM UBF_Assemble_AppComponents
      WHERE Component = 'UFIDA.U9.SH.PubUI.WebPart.dll'
;
DELETE FROM UBF_Assemble_AppComponents
      WHERE Component = 'UFIDA.U9.SH.PubQryListUI.WebPart.dll'
;
DELETE FROM UBF_Assemble_AppComponents
      WHERE Component = 'UFIDA.U9.SH.PubBE.dll'
;

删除菜单

-- 删除菜单[客开]
delete from UBF_Assemble_Menu_Trl 
where ID in (select ID from UBF_Assemble_Menu where Code = 'a6dcc019-7877-4c5a-9a2f-e50653104337')
;
delete from UBF_Assemble_Menu
where Code = 'a6dcc019-7877-4c5a-9a2f-e50653104337'
;

-- 删除菜单[BOM子项替代关系]
delete from UBF_Assemble_Menu_Trl 
where ID in (select ID from UBF_Assemble_Menu where Code = '25f8fca6-3ce5-425d-9956-1d108ebc84f3')
;
delete from UBF_Assemble_Menu
where Code = '25f8fca6-3ce5-425d-9956-1d108ebc84f3'
;
0
用友U9
版权归属： 绀香廿九
本文链接： https://pee.ee/archives/u9037
许可协议： 本文使用《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》协议授权

评论区