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

GO


If EXISTS(SELECT * from Sys_CustomScripts WHERE ID = 1)
Update a SET a.Custom = b.Custom from SYS_Scripts a join Sys_CustomScripts b on b.ID = a.ID
where a.ID=1
GO


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


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=2146)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode,Custom)  Values(2146, 14, 'SQL_MLimit_Sel_Spec', '', '存储限量中检索对应库房的物资', 1, 0, Null, 1, 'DECLARE @lSql varchar(100), @BCK01 INT

SET @lSql = %s
SET @BCK01 = %d

SELECT TA.BBY01,TA.BBY04,TA.BBY05,isnull(TA.BBY06,'''')+''(￥''+isnull(convert(varchar(20),convert(float,BBY25)),'''')+'')'' BBY06,TA.BBE02,TG.BCT18 BBY08
  ,DD.BAL04,DD.BAL05,DD.BCD02, ISNULL(DD.BAL08,0) AS BAL08
  , DWMQF = CASE substring(isnull(DD.BAL06,''0000''),1,1) WHEN ''1'' then 1 else 0 end
  , DWeek = CASE substring(isnull(DD.BAL06,''0000''),2,1) WHEN ''1'' then 1 else 0 end
  , DMonth = CASE substring(isnull(DD.BAL06,''0000''),3,1) WHEN ''1'' then 1 else 0 end
  , DQuarter = CASE substring(isnull(DD.BAL06,''0000''),4,1) WHEN ''1'' then 1 else 0 END
  , DD.BAL09, DD.BAL10
  , ISNULL(K.LSQty, 0) AS LSQty
INTO #TMP_BBY1  
FROM BBY1 AS TA
  INNER JOIN BCT1 AS TG ON TA.BBY01 = TG.BBY01
  LEFT JOIN BAL1 AS DD ON TA.BBY01 = DD.BBY01 AND DD.BCK01 = @BCK01
  LEFT JOIN V_MSK4 AS K ON K.BBY01=TA.BBY01 AND K.BCK01 = @BCK01
  JOIN (SELECT CASE BAU01 WHEN 57 THEN ''4''
					WHEN 93 THEN ''m''
					ELSE ''0'' END AS BAU01
			FROM BAZ1 Z WHERE Z.BCK01 = @BCK01) U ON U.BAU01 = TA.BDN01
WHERE EXISTS(SELECT b.BBY01 FROM BCL1 b WHERE TA.BBY01=b.BBY01
    AND (b.BCL03 LIKE @lSql OR b.ABBRP LIKE @lSql OR b.ABBRW LIKE @lSql))

SELECT * FROM #TMP_BBY1

DROP TABLE #TMP_BBY1
', '2018-01-12 11:20:22', '(8046)刘 敏', 0, 0)
else
print 'SYS_Scripts.id=2146 已经存在.'

GO


If EXISTS(SELECT * from Sys_CustomScripts WHERE ID = 2146)
Update a SET a.Custom = b.Custom from SYS_Scripts a join Sys_CustomScripts b on b.ID = a.ID
where a.ID=2146
GO