K3建一个SQL报表用来查询订单的修改记录（SQL游标）

在采购订单下有个变更功能，采购想有一个地方能够查询所有变更记录而不用一个个查询。

经过查看结果很尴尬，这个模块我们没买。

经查询这个记录是存放在ICOrderAlter 表中，但其中的数据是这样的：
ftablename是修改的数据表名，FFILEDNAME是修改的列名。
相当于修改了哪张表的哪个列的值都给记录下来了，原本我只是简单地认为把这张表的信息做成SQL报表，但这张表的信息不完美，希望信息能不能提供地再详细点。

最初的想法，是将每个FINTERID对应的表的信息给取过来，首先是单据号:

if exists(select 1 from tempdb.sys.objects where name='##temp')
drop table ##temp ;
 
create table ##temp ( fid int,finterid int,fbillno varchar(20) )

insert into ##temp(fid,finterid)
select FID,FInterID from ICOrderAlter

declare @fid int, 
        @finterid int ,
		@ftablename varchar(20),
		@fbilltype int,
		@ffieldname  varchar(20)


declare ICOrderAlter_cursor cursor
for(select fid,finterid,ftablename from  ICOrderAlter where FBillType=71 or FBillType=81 )

open ICOrderAlter_cursor
fetch next from ICOrderAlter_cursor into  @fid,@finterid,@ftablename
while @@FETCH_STATUS=0

begin	
select  @finterid=FInterID,@fbilltype=FBillType,@ffieldname=ffieldname from ICOrderAlter where fid=@fid ;
select @ftablename=FHeadTable from  ICTransactionType where fid=@fbilltype;


exec  ('update A set A.FBILLNO=B.FBILLNO FROM  ##temp A  
LEFT  JOIN '+ @ftablename +  ' b ON A.FINTERID=B.FINTERID
WHERE A.FID='+@fid
)

fetch next from ICOrderAlter_cursor into @fid,@finterid,@ftablename
end
close ICOrderAlter_cursor
deallocate ICOrderAlter_cursor

select * from  ##temp

结果

后来想想干嘛不做成触发器，当变更时直接存到ICOrderAlter表中去，但没有相应的字段，看到FChangecause是空的，就利用一下吧。

ALTER TRIGGER [dbo].[ICOrderAlter_insert]
   ON  [dbo].[ICOrderAlter]
   AFTER  INSERT,UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here

if exists(select 1 from tempdb.sys.objects where name='##ICOrderAlter_temp')
drop table ##ICOrderAlter_temp;
	create table ##ICOrderAlter_temp ( finterid int,fbillno varchar(20) )

declare @fid int, 
        @finterid int ,
		@ftablename varchar(20),
		@fbilltype int


declare ICOrderAlter_cursor cursor
for(select fid,finterid,ftablename from  Inserted where FBillType=71 or FBillType=81 )

open ICOrderAlter_cursor
fetch next from ICOrderAlter_cursor into  @fid,@finterid,@ftablename
while @@FETCH_STATUS=0

begin	
select  @finterid=FInterID,@fbilltype=FBillType from Inserted where fid=@fid ;
select @ftablename=FHeadTable from  ICTransactionType where fid=@fbilltype;

insert into ##ICOrderAlter_temp (finterid ,fbillno)
exec  ('select finterid,fbillno from ' +  @ftablename + ' where finterid= '+@finterid);

update a  set  a.FChangeCauses=b.fbillno from ICOrderAlter a
inner join ##ICOrderAlter_temp b on a.FInterID=b.finterid
where a.fid=@fid;

fetch next from ICOrderAlter_cursor into @fid,@finterid,@ftablename
end
close ICOrderAlter_cursor
deallocate ICOrderAlter_cursor

效果

但根本问题没有解决，如何做一个SQL报表，要求单据的详情和修改的内容都呈现出来。
后来发现基本上和业务有关的是71和81两张表，分别是采购订单和销售订单。
语句如下：

--判断临时表是否存在，有删除没有新建
if exists(select 1 from tempdb.sys.objects where name='##temp')
drop table ##temp ;
 
create table ##temp ( fid int,finterid int,fbillno varchar(20),custom varchar(100),fversionno varchar(20))
--临时表插入关键信息
insert into ##temp(fid,finterid)
select FID,FInterID from ICOrderAlter where FBillType=71 or FBillType=81

--用游标来插入相应表的单据号和客户
declare @fid int, 
        @finterid int ,
		@ftablename varchar(20),
		@fbilltype int,
		@ffieldname  varchar(20)
		
declare ICOrderAlter_cursor cursor
for(select fid from  ICOrderAlter where FBillType=71 or FBillType=81 )

open ICOrderAlter_cursor
fetch next from ICOrderAlter_cursor into  @fid
while @@FETCH_STATUS=0

begin	
select  @finterid=FInterID,@fbilltype=FBillType,@ffieldname=ffieldname from ICOrderAlter where fid=@fid 
select @ftablename=FHeadTable from  ICTransactionType where fid=@fbilltype 
;

if @fbilltype =71 
begin
exec  ('update A set A.FBILLNO=B.FBILLNO,a.custom=c.fname FROM  ##temp A  
LEFT  JOIN '+ @ftablename +  ' b ON A.FINTERID=B.FINTERID
 inner join  t_supplier C on b.FSupplyID=c.FitemID 
WHERE A.FID='+@fid
)
end
else if   @fbilltype =81 
begin
exec  ('update A set A.FBILLNO=B.FBILLNO,a.custom=c.fname FROM  ##temp A  
LEFT  JOIN '+ @ftablename +  ' b ON A.FINTERID=B.FINTERID
 inner join  t_Organization C on b.FCUSTID=c.FitemID 
WHERE A.FID='+@fid
)
end


fetch next from ICOrderAlter_cursor into @fid
end
close ICOrderAlter_cursor
deallocate ICOrderAlter_cursor

--关联查询
 select b.fid ,b.fbillno ,b.custom ,a.FVersionNo,convert(VARCHAR(20),a.FChangeDate,111) CHANGEDATE,a.FChangeUser ,a.FChangeCauses ,a.FChangeType,
 case a.FoldNo when 0 then '单据头' when '99999' then '' else a.FoldNo end as FoldNo ,a.FNumbers ,
a.FitemName ,a.Fmodel ,a.FChangeField,a.FBChangeValue ,a.FEChangeValue 
 from ICOrderAlter   a
 inner join ##temp b on a.FID=b.fid
 order by b.fid

效果：

在这里很大的因素是因为销售单和采购单的表结构和列名不一致

做到SQL报表中