--增加分类
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=946 --删除脚本内容
GO


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=946)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode,Custom)  Values(946, 33, 'NoCheckOutGroup4', '费用记录', '未结费用 逐日单据汇总', 1, 1, Null, 1, 'declare @lVAE44 tinyint, @lVAA07 int, @bDate datetime, @eDate datetime
set @lVAA07 = %d
set @bDate = %s
set @eDate = %s
set @lVAE44 = isnull((select VAE44 from VAE1 with(nolock) where VAE01 = @lVAA07),2)
if @lVAE44 <= 4
begin
  SELECT CONVERT(varchar(10), a.VAJ46,21) AS 发生日期,b.VAI04 AS 单据号,d.BBY05 as 项目名称
    ,sum(a.VAJ36) as 应收金额,sum(a.VAJ37) as 实收金额,b.BCE03B as 操作员,a.VAJ27 婴儿ID 
  FROM VAJ2 a with(nolock)
  JOIN VAI2 b with(nolock) ON a.VAI01 = b.VAI01
  LEFT JOIN BBY1 d with(nolock) ON a.BBY01 = d.BBY01
  LEFT JOIN VAF2 v with(nolock) on a.VAF01 = v.VAF01
  WHERE a.VAK01 = 0 and a.VAA07 = @lVAA07 and a.ACF01 = 2
    AND a.VAJ46 >= @bDate AND a.VAJ46 < @eDate
  GROUP BY CONVERT(varchar(10), a.VAJ46,21),b.VAI04,d.BBY05,b.BCE03B,a.VAJ27
end
else
begin
  SELECT CONVERT(varchar(10), a.VAJ46,21) AS 发生日期,b.VAI04 AS 单据号,d.BBY05 as 项目名称
    ,sum(a.VAJ36) as 应收金额,sum(a.VAJ37) as 实收金额,b.BCE03B as 操作员,a.VAJ27 婴儿ID 
  FROM V_VAJ_2 a with(nolock)
  JOIN V_VAI_2 b with(nolock) ON a.VAI01 = b.VAI01
  LEFT JOIN BBY1 d with(nolock) ON a.BBY01 = d.BBY01
  LEFT JOIN VAF2 v with(nolock) on a.VAF01 = v.VAF01
  WHERE a.VAK01 > 0 and a.VAA07 = @lVAA07 and a.ACF01 = 2
    AND a.VAJ46 >= @bDate AND a.VAJ46 < @eDate
  GROUP BY CONVERT(varchar(10), a.VAJ46,21),b.VAI04,d.BBY05,b.BCE03B,a.VAJ27
end', '2019-12-24 17:51:22', '(8088)王海涛', 0, 0)
else
print 'SYS_Scripts.id=946 已经存在.'

GO


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