报表数据源的做成

第一步，报表数据源的做成

SET QUOTED_IDENTIFIER ON 

GO

SET ANSI_NULLS ON 

GO















ALTER        PageAll

@BranchCD INT

,@LineCD INT 

,@DateS VARCHAR(10)

,@DateE VARCHAR(10)

,@strProductCD varchar(4000)=null

,@clipStart int

AS



declare @divNum int 

set @divNum=3



declare @sql varchar(8000)

if   object_id(N'tempdb..#tempProductInfor')   is   not   null  

drop   table  #tempProductInfor



create table #tempProductInfor

(

sid int identity(1,1) primary key

,ProductCDOld varchar(13)

,ProductNameOld varchar(80)

,DepartmentCD int

,原単価 money

,売単価 money

,temp_ID int 

,clip_ID int

)

print @strProductCD

if @strProductCD is null

begin

set @sql='insert into #tempProductInfor

select distinct 

	ProductCD

	,ProductName

	,DepartmentCD

	,null as 原単価

	,null as 売単価

	,0 as temp_ID

	,0 as clip_ID

from	

	dbo.Reorg_ReorgInfor RRI

inner join

	(

	select 

		YearWeek

		,AdmitDate

		,BusinessTalksID

		,SaleGroupCD

		,ReorganizeGroupCD

	from

		dbo.Reorg_ReorgInforStore

	where

		YearWeek>0

	and	AdmitDate between '''+@DateS+''' and '''+@DateE+'''

	and	BranchCD='+Convert(varchar(4),@BranchCD)+'

	and	EntryType=4) RRIS

on

	RRI.YearWeek=RRIS.YearWeek

and	RRI.AdmitDate=RRIS.AdmitDate

and	RRI.BusinessTalksID=RRIS.BusinessTalksID

and	RRI.SaleGroupCD=RRIS.SaleGroupCD

and	RRI.ReorganizeGroupCD=RRIS.ReorganizeGroupCD

and 	RRI.YearWeek>0

and	RRI.AdmitDate between '''+@DateS+''' and '''+@DateE+'''

where

	RRI.LineCD='+ Convert(varchar(4),@LineCD)+'

and	EntryType=4

Order by DepartmentCD,ProductCD'









end

else

begin

set @sql='insert into #tempProductInfor

select distinct 

	ProductCD

	,ProductName

	,DepartmentCD

	,null as 原単価

	,null as 売単価

	,0 as temp_ID

	,0 as clip_ID

from	

	dbo.Reorg_ReorgInfor RRI

inner join

	(

	select 

		YearWeek

		,AdmitDate

		,BusinessTalksID

		,SaleGroupCD

		,ReorganizeGroupCD

	from

		dbo.Reorg_ReorgInforStore

	where

		YearWeek>0

	and	AdmitDate between '''+@DateS+''' and '''+@DateE+'''

	and	BranchCD='+Convert(varchar(4),@BranchCD)+'

	and	EntryType=4) RRIS

on

	RRI.YearWeek=RRIS.YearWeek

and	RRI.AdmitDate=RRIS.AdmitDate

and	RRI.BusinessTalksID=RRIS.BusinessTalksID

and	RRI.SaleGroupCD=RRIS.SaleGroupCD

and	RRI.ReorganizeGroupCD=RRIS.ReorganizeGroupCD

and 	RRI.YearWeek>0

and	RRI.AdmitDate between '''+@DateS+''' and '''+@DateE+'''

where

	RRI.LineCD='+ Convert(varchar(4),@LineCD)+'

and	EntryType=4

RRI.ProductCD not in('+@strProductCD+')

Order by DepartmentCD,ProductCD'

end

print @sql

exec (@sql)

---------------------------------------原単価売単価---------------------------------------------------

update #tempProductInfor

set

	原単価=PR.purchprice

	,売単価=PR.retailprice

FROM

	OPENDATASOURCE ('SQLOLEDB',  'DATA SOURCE=10.17.254.5;USER ID=test;Password=test').sql_live.dbo.prsourceprefs PR

INNER JOIN

	OPENDATASOURCE ('SQLOLEDB',  'DATA SOURCE=10.17.254.5;USER ID=test;Password=test').sql_live.dbo.productcodes PC

ON

     PR.varint = PC.varint 

WHERE

     PC.variantcode = #tempProductInfor.ProductCDOld

     AND PR.Plint = (select plint from M_store where BranchCD=@BranchCD)

	





-----------------------伝票IDと伝票明细ID------------------------------------------------------------------



	declare @intMax int 

	set @intMax=(select count(1) as cnum from #tempProductInfor)

	declare @intDeptCD int

	set @intDeptCD=(select min(DepartmentCD) from #tempProductInfor where temp_ID=0)

		

	declare @i int

	set @i = 1

	declare @j int

	set @j = 1

	declare @k int

	set @k = 1

		while(@i<=@intMax)

		begin

			update 

				#tempProductInfor

			set

				temp_ID=(@j-1)%3+1

				,clip_ID=(@k-1)/3+1+@clipStart

			where

				sid =@i

				and	temp_ID=0

				and	DepartmentCD=@intDeptCD

			set @i = @i +1

	

	

			if (@intDeptCD<>(select min(DepartmentCD) from #tempProductInfor where temp_ID=0))

			begin

				set @intDeptCD=(select min(DepartmentCD) from #tempProductInfor where temp_ID=0)

				set @j=0

				set @k=((select max(clip_ID) from #tempProductInfor)-@clipStart)*3

			end



			set @j = @j +1

			set @k = @k +1	

		end

	select * from #tempProductInfor





-------------------------------------------------------------------------------------------------













GO

SET QUOTED_IDENTIFIER OFF 

GO

SET ANSI_NULLS ON 

GO