--删除旧记录
--delete from Report_Objects where Code='HO.DSForm.000018' --删除脚本内容


IF not EXISTS(SELECT * FROM Report_Objects WHERE code='HO.DSForm.000018')
begin --插入
  Declare @Report_Id_887 int
  Exec Core_NewId 'Report_Objects', 'id', @Report_Id_887 out
  Declare @Report_Id_887_CateID int
  Set @Report_Id_887_CateID = (select id from Report_Categories where code='HIS.11.3')
  insert into Report_Objects(ID,Code,Name,Description,Password,CategoryID,xType,Classification,ProductID,ProgramID,ElementID,CreateDate,UpdateDate,ISSUANCEDATE,Config,SetupPath,IsMenu,Enabled,GroupName,Authorized,RefreshInterval)  Values(@Report_Id_887, 'HO.DSForm.000018', '采购计划查询', Null, Null, @Report_Id_887_CateID, 1, 3, 100, 0, Null, '2013-07-02 14:09:00', '2013-07-02 14:09:31', Null, '<?xml version="1.0"?>
<Root Ver="1.0" YIBAO="" NoDialog="" Preview="11111111111100011"><Config TimeOut="15" BrowMode="0"><DBLink Mode="1"></DBLink><Chart HasChart="0" ViewFirst="0"/><BrowUrl User="" Pass=""></BrowUrl><CTI Enabled="0" Tel1="" Tel2="" Tel3=""/><Print Edit="0" ExecSql="" Select="0" SelectDefault="1" Mode="0"/><OtherReport><File></File><Fun></Fun></OtherReport><MD5>6D975366EAF5047E2B11491D7941F3E9</MD5></Config><DTS><Script>declare @BCK01 INT
, @BeginDate datetime
, @EndDate datetime
, @OrderType int
select @BCK01 = :BCK01
, @BeginDate = :BeginDate
, @EndDate = :EndDate
, @OrderType = :OrderType</Script><Ie Name="新建数据源" Fixed="0" Visible="1" OrderBy="" OrderType="0" Bands="0" UseBCD="0"><Descrption></Descrption><Sql>declare @BCK01 INT
, @BeginDate datetime
, @EndDate datetime
, @OrderType int
select @BCK01 = :BCK01
, @BeginDate = :BeginDate
, @EndDate = :EndDate
, @OrderType = :OrderType
select @BeginDate = CONVERT(varchar(30), @BeginDate, 121), @EndDate = CONVERT(varchar(30), @EndDate, 121)
if OBJECT_ID(''tempdb..#tmpDSA'') is not null drop table #tmpDSA
create table #tmpDSA(
	Checked int
  , BBY01 int  --药品ID
  , ConsumeQty numeric(18, 2) --消耗数量
  , DSKQty numeric(18, 2) --库存数量
  , BAL04 numeric(18, 2) --库存警戒上限
  , BAL05 numeric(18, 2) --库存警戒下限
  , ChkQty numeric(18, 2) --实际采购数量
  , BBH01 int --供应商
  , BBH04 varchar(128)
  , PurchasePrice numeric(18, 2)
  , PresellPrice numeric(18, 2)
)
if @OrderType = 0 --根据药库消耗生成采购
begin
	insert into #tmpDSA(BBY01, ConsumeQty)
	SELECT A.BBY01, SUM(A.Quantity)
	FROM (
		--出库单
		SELECT B.BBY01, B.Quantity
		FROM DSD1 A JOIN DSD2 B ON A.BCK01=B.BCK01 AND A.BillID=B.BillID
		  JOIN BBY1 C ON C.BBY01=B.BBY01
		WHERE A.BCK01=@BCK01 AND C.BBY31 &gt; GETDATE() AND A.AuditingDate between @BeginDate and @EndDate
		union
		--损益单
		SELECT B.BBY01, B.Quantity
		FROM DSF1 A JOIN DSF2 B ON A.BCK01=B.BCK01 AND A.BillID=B.BillID
		  JOIN BBY1 C ON C.BBY01=B.BBY01
		WHERE A.BCK01=@BCK01 AND C.BBY31 &gt; GETDATE() AND A.AuditingDate between @BeginDate and @EndDate
		union
		--调出单
		SELECT B.BBY01, B.Quantity
		FROM DSI1 A JOIN DSI2 B ON A.BCK01=B.BCK01 AND A.BillID=B.BillID
		  JOIN BBY1 C ON C.BBY01=B.BBY01
		WHERE A.BCK01=@BCK01 AND C.BBY31 &gt; GETDATE() AND A.AuditingDate between @BeginDate and @EndDate
	) A
	GROUP BY A.BBY01
end
if @OrderType = 1 --根据药房请领生成采购
begin
	insert into #tmpDSA(BBY01, ConsumeQty)
	SELECT B.BBY01, SUM(B.ReqQuantity)
	FROM DPA1 A JOIN DPA2 B ON A.BillType = 1 AND B.BCK01 = A.BCK01 AND B.BillID = A.BillID
		JOIN BBY1 C ON C.BBY01=B.BBY01
	WHERE A.StoreID=@BCK01 AND C.BBY31 &gt; GETDATE() AND A.AuditingDate between @BeginDate and @EndDate
	GROUP BY B.BBY01
end
if @OrderType = 2 --根据药房消耗生成采购
begin
	insert into #tmpDSA(BBY01, ConsumeQty)
	SELECT A.BBY01, SUM(A.Quantity)
	FROM (
		--出库单
		SELECT B.BBY01, B.Quantity/b.PackSize  Quantity
		FROM V_DPD1_FULL A JOIN V_DPD2_FULL B ON A.BCK01=B.BCK01 AND A.BillID=B.BillID
		  JOIN BBY1 C ON C.BBY01=B.BBY01
		WHERE  C.BBY31 &gt; GETDATE() AND A.AuditingDate between @BeginDate and @EndDate
		union all
		--损益单
		SELECT B.BBY01, B.Quantity/b.PackSize
		FROM DPF1 A JOIN DPF2 B ON A.BCK01=B.BCK01 AND A.BillID=B.BillID
		  JOIN BBY1 C ON C.BBY01=B.BBY01
		WHERE  C.BBY31 &gt; GETDATE() AND A.AuditingDate between @BeginDate and @EndDate
		union all
		--调出单
		SELECT B.BBY01, B.Quantity/b.PackSize
		FROM DPI1 A JOIN DPI2 B ON A.BCK01=B.BCK01 AND A.BillID=B.BillID
		  JOIN BBY1 C ON C.BBY01=B.BBY01
		WHERE  C.BBY31 &gt; GETDATE() AND A.AuditingDate between @BeginDate and @EndDate
	) A
	GROUP BY A.BBY01
end


delete from #tmpDSA where ConsumeQty &lt;= 0

update A set A.PurchasePrice = isnull((select top 1 K.PurchasePrice from DSK1 K where K.BCK01 = @BCK01 and K.BBY01 = A.BBY01 order by ID desc), 0)
  , A.PresellPrice = isnull((select top 1 K.PresellPrice from DSK1 K where K.BCK01 = @BCK01 and K.BBY01 = A.BBY01 order by ID desc), 0)
from #tmpDSA A

update A set A.BBH01 = B.BBH01B, A.BBH04 = C.BBH04
from #tmpDSA A join BAG1 B on B.BBY01 = A.BBY01
 join BBH1 C on C.BBH01 = B.BBH01B

update A set A.BAL04 = B.BAL04, A.BAL05 = B.BAL05, A.ChkQty = A.ConsumeQty, A.Checked = 0
from #tmpDSA A join BAL1 B on B.BCK01 = @BCK01 and B.BBY01 = A.BBY01

update A set A.DSKQty = B.LSQty
from #tmpDSA A join 
(select BBY01, SUM(LastQuantity) LSQty  from
 (select BBY01 ,LastQuantity
  from DSK1 where LastQuantity&gt;0 
  union all
  select  BBY01, LastQuantity/PackSize LastQuantity
  from DPK1 where LastQuantity&gt;0
  ) as T group by  BBY01
 ) B on B.BBY01 = A.BBY01

if OBJECT_ID(''tempdb..#tmpSql'') is not null drop table #tmpSql
select A.Checked, A.BBY01, B.BBY04, B.BBY05, B.BBY06,G.BDG02C as Unit,A.ConsumeQty, A.DSKQty, A.BAL04, A.BAL05, A.ChkQty
, A.BBH01, A.BBH04, A.PurchasePrice, A.PresellPrice
, isnull(G.BAG23, 0) as OldPurchasePrice/*上次进货价*/, G.BBH01B, H.BBH04 AS BBH04B, B.BBE02, G.BAG23*A.ChkQty as Amount, G.BAG15
into #tmpSql
from #tmpDSA A JOIN BBY1 B ON B.BBY01 = A.BBY01
join BAG1 G on G.BBY01 = B.BBY01
join BBH1 H on H.BBH01 = G.BBH01B

select * from #tmpSql
drop table #tmpSql
if OBJECT_ID(''tempdb..#tmpDSA'') is not null drop table #tmpDSA</Sql><Links/></Ie></DTS><Params><Ie Name="BCK01" Alias="仓库" GroupName="" DefaultValue="0" Style="0" NotNull="1" Privilege="3"><Config KeyField="BCK01" DispField="BCK03" Flag="1">&lt;?xml version="1.0"?&gt;
&lt;Configs Ver="1"&gt;&lt;Win SZ="1"/&gt;&lt;Grid Head="1" UseW="1" AWS="1"&gt;&lt;Sql&gt;SELECT BCK01, BCK02, BCK03, BCK04, ABBRP, ABBRW, BCK09, BCK01A FROM BCK1 K
WHERE exists(SELECT * FROM BAZ1 Z WHERE Z.BCK01=K.BCK01 and Z.BAU01 in (''54'',''55'',''56''))&lt;/Sql&gt;&lt;HD&gt;&lt;Ie FD="BCK02" CA="编码" WD="100"/&gt;&lt;Ie FD="BCK03" CA="名称" WD="100"/&gt;&lt;/HD&gt;&lt;QF&gt;&lt;Ie FD="BCK03" WE="7"/&gt;&lt;Ie FD="ABBRP" WE="7"/&gt;&lt;Ie FD="ABBRW" WE="7"/&gt;&lt;/QF&gt;&lt;/Grid&gt;&lt;/Configs&gt;</Config></Ie><Ie Name="OrderType" Alias="计划类型" GroupName="" DefaultValue="2" Style="6" NotNull="1" Privilege="0"><Config KeyField="" DispField="" Flag="0">0=根据药库消耗生成采购计划
1=根据药房请领生成采购计划
2=根据药房消耗生成采购</Config></Ie><Ie Name="BeginDate" Alias="开始时间" GroupName="" DefaultValue="2" Style="4" NotNull="1" Privilege="0"><Config KeyField="" DispField="" Flag="0"></Config></Ie><Ie Name="EndDate" Alias="结束时间" GroupName="" DefaultValue="3" Style="4" NotNull="1" Privilege="0"><Config KeyField="" DispField="" Flag="0"></Config></Ie></Params></Root>
', Null, 0, 1, Null, 1, Null)
end
else --更新
begin
  update Report_Objects set Config='<?xml version="1.0"?>
<Root Ver="1.0" YIBAO="" NoDialog="" Preview="11111111111100011"><Config TimeOut="15" BrowMode="0"><DBLink Mode="1"></DBLink><Chart HasChart="0" ViewFirst="0"/><BrowUrl User="" Pass=""></BrowUrl><CTI Enabled="0" Tel1="" Tel2="" Tel3=""/><Print Edit="0" ExecSql="" Select="0" SelectDefault="1" Mode="0"/><OtherReport><File></File><Fun></Fun></OtherReport><MD5>6D975366EAF5047E2B11491D7941F3E9</MD5></Config><DTS><Script>declare @BCK01 INT
, @BeginDate datetime
, @EndDate datetime
, @OrderType int
select @BCK01 = :BCK01
, @BeginDate = :BeginDate
, @EndDate = :EndDate
, @OrderType = :OrderType</Script><Ie Name="新建数据源" Fixed="0" Visible="1" OrderBy="" OrderType="0" Bands="0" UseBCD="0"><Descrption></Descrption><Sql>declare @BCK01 INT
, @BeginDate datetime
, @EndDate datetime
, @OrderType int
select @BCK01 = :BCK01
, @BeginDate = :BeginDate
, @EndDate = :EndDate
, @OrderType = :OrderType
select @BeginDate = CONVERT(varchar(30), @BeginDate, 121), @EndDate = CONVERT(varchar(30), @EndDate, 121)
if OBJECT_ID(''tempdb..#tmpDSA'') is not null drop table #tmpDSA
create table #tmpDSA(
	Checked int
  , BBY01 int  --药品ID
  , ConsumeQty numeric(18, 2) --消耗数量
  , DSKQty numeric(18, 2) --库存数量
  , BAL04 numeric(18, 2) --库存警戒上限
  , BAL05 numeric(18, 2) --库存警戒下限
  , ChkQty numeric(18, 2) --实际采购数量
  , BBH01 int --供应商
  , BBH04 varchar(128)
  , PurchasePrice numeric(18, 2)
  , PresellPrice numeric(18, 2)
)
if @OrderType = 0 --根据药库消耗生成采购
begin
	insert into #tmpDSA(BBY01, ConsumeQty)
	SELECT A.BBY01, SUM(A.Quantity)
	FROM (
		--出库单
		SELECT B.BBY01, B.Quantity
		FROM DSD1 A JOIN DSD2 B ON A.BCK01=B.BCK01 AND A.BillID=B.BillID
		  JOIN BBY1 C ON C.BBY01=B.BBY01
		WHERE A.BCK01=@BCK01 AND C.BBY31 &gt; GETDATE() AND A.AuditingDate between @BeginDate and @EndDate
		union
		--损益单
		SELECT B.BBY01, B.Quantity
		FROM DSF1 A JOIN DSF2 B ON A.BCK01=B.BCK01 AND A.BillID=B.BillID
		  JOIN BBY1 C ON C.BBY01=B.BBY01
		WHERE A.BCK01=@BCK01 AND C.BBY31 &gt; GETDATE() AND A.AuditingDate between @BeginDate and @EndDate
		union
		--调出单
		SELECT B.BBY01, B.Quantity
		FROM DSI1 A JOIN DSI2 B ON A.BCK01=B.BCK01 AND A.BillID=B.BillID
		  JOIN BBY1 C ON C.BBY01=B.BBY01
		WHERE A.BCK01=@BCK01 AND C.BBY31 &gt; GETDATE() AND A.AuditingDate between @BeginDate and @EndDate
	) A
	GROUP BY A.BBY01
end
if @OrderType = 1 --根据药房请领生成采购
begin
	insert into #tmpDSA(BBY01, ConsumeQty)
	SELECT B.BBY01, SUM(B.ReqQuantity)
	FROM DPA1 A JOIN DPA2 B ON A.BillType = 1 AND B.BCK01 = A.BCK01 AND B.BillID = A.BillID
		JOIN BBY1 C ON C.BBY01=B.BBY01
	WHERE A.StoreID=@BCK01 AND C.BBY31 &gt; GETDATE() AND A.AuditingDate between @BeginDate and @EndDate
	GROUP BY B.BBY01
end
if @OrderType = 2 --根据药房消耗生成采购
begin
	insert into #tmpDSA(BBY01, ConsumeQty)
	SELECT A.BBY01, SUM(A.Quantity)
	FROM (
		--出库单
		SELECT B.BBY01, B.Quantity/b.PackSize  Quantity
		FROM V_DPD1_FULL A JOIN V_DPD2_FULL B ON A.BCK01=B.BCK01 AND A.BillID=B.BillID
		  JOIN BBY1 C ON C.BBY01=B.BBY01
		WHERE  C.BBY31 &gt; GETDATE() AND A.AuditingDate between @BeginDate and @EndDate
		union all
		--损益单
		SELECT B.BBY01, B.Quantity/b.PackSize
		FROM DPF1 A JOIN DPF2 B ON A.BCK01=B.BCK01 AND A.BillID=B.BillID
		  JOIN BBY1 C ON C.BBY01=B.BBY01
		WHERE  C.BBY31 &gt; GETDATE() AND A.AuditingDate between @BeginDate and @EndDate
		union all
		--调出单
		SELECT B.BBY01, B.Quantity/b.PackSize
		FROM DPI1 A JOIN DPI2 B ON A.BCK01=B.BCK01 AND A.BillID=B.BillID
		  JOIN BBY1 C ON C.BBY01=B.BBY01
		WHERE  C.BBY31 &gt; GETDATE() AND A.AuditingDate between @BeginDate and @EndDate
	) A
	GROUP BY A.BBY01
end


delete from #tmpDSA where ConsumeQty &lt;= 0

update A set A.PurchasePrice = isnull((select top 1 K.PurchasePrice from DSK1 K where K.BCK01 = @BCK01 and K.BBY01 = A.BBY01 order by ID desc), 0)
  , A.PresellPrice = isnull((select top 1 K.PresellPrice from DSK1 K where K.BCK01 = @BCK01 and K.BBY01 = A.BBY01 order by ID desc), 0)
from #tmpDSA A

update A set A.BBH01 = B.BBH01B, A.BBH04 = C.BBH04
from #tmpDSA A join BAG1 B on B.BBY01 = A.BBY01
 join BBH1 C on C.BBH01 = B.BBH01B

update A set A.BAL04 = B.BAL04, A.BAL05 = B.BAL05, A.ChkQty = A.ConsumeQty, A.Checked = 0
from #tmpDSA A join BAL1 B on B.BCK01 = @BCK01 and B.BBY01 = A.BBY01

update A set A.DSKQty = B.LSQty
from #tmpDSA A join 
(select BBY01, SUM(LastQuantity) LSQty  from
 (select BBY01 ,LastQuantity
  from DSK1 where LastQuantity&gt;0 
  union all
  select  BBY01, LastQuantity/PackSize LastQuantity
  from DPK1 where LastQuantity&gt;0
  ) as T group by  BBY01
 ) B on B.BBY01 = A.BBY01

if OBJECT_ID(''tempdb..#tmpSql'') is not null drop table #tmpSql
select A.Checked, A.BBY01, B.BBY04, B.BBY05, B.BBY06,G.BDG02C as Unit,A.ConsumeQty, A.DSKQty, A.BAL04, A.BAL05, A.ChkQty
, A.BBH01, A.BBH04, A.PurchasePrice, A.PresellPrice
, isnull(G.BAG23, 0) as OldPurchasePrice/*上次进货价*/, G.BBH01B, H.BBH04 AS BBH04B, B.BBE02, G.BAG23*A.ChkQty as Amount, G.BAG15
into #tmpSql
from #tmpDSA A JOIN BBY1 B ON B.BBY01 = A.BBY01
join BAG1 G on G.BBY01 = B.BBY01
join BBH1 H on H.BBH01 = G.BBH01B

select * from #tmpSql
drop table #tmpSql
if OBJECT_ID(''tempdb..#tmpDSA'') is not null drop table #tmpDSA</Sql><Links/></Ie></DTS><Params><Ie Name="BCK01" Alias="仓库" GroupName="" DefaultValue="0" Style="0" NotNull="1" Privilege="3"><Config KeyField="BCK01" DispField="BCK03" Flag="1">&lt;?xml version="1.0"?&gt;
&lt;Configs Ver="1"&gt;&lt;Win SZ="1"/&gt;&lt;Grid Head="1" UseW="1" AWS="1"&gt;&lt;Sql&gt;SELECT BCK01, BCK02, BCK03, BCK04, ABBRP, ABBRW, BCK09, BCK01A FROM BCK1 K
WHERE exists(SELECT * FROM BAZ1 Z WHERE Z.BCK01=K.BCK01 and Z.BAU01 in (''54'',''55'',''56''))&lt;/Sql&gt;&lt;HD&gt;&lt;Ie FD="BCK02" CA="编码" WD="100"/&gt;&lt;Ie FD="BCK03" CA="名称" WD="100"/&gt;&lt;/HD&gt;&lt;QF&gt;&lt;Ie FD="BCK03" WE="7"/&gt;&lt;Ie FD="ABBRP" WE="7"/&gt;&lt;Ie FD="ABBRW" WE="7"/&gt;&lt;/QF&gt;&lt;/Grid&gt;&lt;/Configs&gt;</Config></Ie><Ie Name="OrderType" Alias="计划类型" GroupName="" DefaultValue="2" Style="6" NotNull="1" Privilege="0"><Config KeyField="" DispField="" Flag="0">0=根据药库消耗生成采购计划
1=根据药房请领生成采购计划
2=根据药房消耗生成采购</Config></Ie><Ie Name="BeginDate" Alias="开始时间" GroupName="" DefaultValue="2" Style="4" NotNull="1" Privilege="0"><Config KeyField="" DispField="" Flag="0"></Config></Ie><Ie Name="EndDate" Alias="结束时间" GroupName="" DefaultValue="3" Style="4" NotNull="1" Privilege="0"><Config KeyField="" DispField="" Flag="0"></Config></Ie></Params></Root>
' where Code='HO.DSForm.000018'
end

GO