--增加分类
GO
IF not EXISTS(SELECT * FROM SYS_ScriptCategories WHERE id=14)
insert into SYS_ScriptCategories(ID,Code,Name,ComponentID,xRowNum,ParentID,xLevel,Description)  Values(14, 'SC14', '卫生材料管理', 1, 0, 1, 0, Null)
else
print 'SYS_ScriptCategories.id=14 已经存在.'

GO


--删除旧记录
delete from SYS_Scripts where id=255 --删除脚本内容
GO


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=255)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode)  Values(255, 14, 'SQL_MaterialKindCur', Null, '查询出当前材料品种', 1, 1, Null, 1, 'DECLARE @Now datetime,@BDM01 varchar(20),@BDO01 int
Set @Now=getdate()
Set @BDM01 = %s
Set @BDO01 = :BDO01
Set @BDO01 = :BDO01A

;with subqry(BDO01,BDO03,BDO01A) 
  as (
  select BDO01,BDO03,BDO01A from BDO1 where BDO01=@BDO01
  union all
  select a.BDO01,a.BDO03,a.BDO01A from BDO1 a,subqry
  where a.BDO01A = subqry.BDO01
  ) select * into #tmp_BDO1 from subqry
                 
Select td.BBX01, td.BDA01, td.BBX04, td.BDO01, td.BBX05, td.BDG02, td.BBX24, td.BBX25,
 tc.BDO03, tdt.NameA,tdt1.NameB, tdt2.NameC, tdt3.NameD, tdt4.NameE, isExpiry = case WHEN BBX25>@Now THEN 0 ELSE 1 END FROM BBX1 td 
 JOIN BDO1 tc ON td.BDO01 = tc.BDO01
 LEFT JOIN (SELECT tt.BDK03 AS NameA, tt.BBX01 FROM BDK1 tt WHERE tt.BDK06 = 1) tdt ON  td.BBX01 = tdt.BBX01
 LEFT JOIN (SELECT tt.BDK03 AS NameB, tt.BBX01 FROM BDK1 tt WHERE tt.BDK06 = 2) tdt1 ON  td.BBX01 = tdt1.BBX01
 LEFT JOIN (SELECT tt.BDK03 AS NameC, tt.BBX01 FROM BDK1 tt WHERE tt.BDK06 = 3) tdt2 ON  td.BBX01 = tdt2.BBX01
 LEFT JOIN (SELECT tt.BDK03 AS NameD, tt.BBX01 FROM BDK1 tt WHERE tt.BDK06 = 4) tdt3 ON  td.BBX01 = tdt3.BBX01
 LEFT JOIN (SELECT tt.BDK03 AS NameE, tt.BBX01 FROM BDK1 tt WHERE tt.BDK06 = 5) tdt4 ON  td.BBX01 = tdt4.BBX01 
 WHERE tc.BDM01 = @BDM01 And exists(select * from #tmp_BDO1 where BDO01=td.BDO01)
 order by  td.BBX04
 
 drop table #tmp_BDO1', '2014-11-04 15:40:14', '(8088)王海涛', 0)
else
print 'SYS_Scripts.id=255 已经存在.'

GO