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


GO
IF not EXISTS(SELECT * FROM SYS_Scripts WHERE id=1919)
insert into SYS_Scripts(ID,CategoryID,Code,Name,Description,xType,DatasourceID,DisplayNames,xRowNum,xText,UpdateDate,UpdateUser,UpdateMode,Custom)  Values(1919, 82, 'SQL_InPtSnd_DetailSe', '住院病人发药-待发药明细记录(分表)', '检索住院病人发药明细记录', 1, 0, Null, 1, 'DECLARE @PharmacyId int ,
        @Start datetime ,
        @Finish datetime ,
        @FDateStyle int--0=不限制、1=当天、2=两天内、3=3天内、4=自己选择时间

SET @PharmacyId = %d 
SET @FDateStyle = %d 
SET @Start = ''%s''
SET @Finish = ''%s'' 

If @FDateStyle = 0 --不限制
BEGIN
  SET @Start = ''1900-01-01''
  SET @Finish = CONVERT(datetime, CONVERT(varchar(10), getdate() +1 , 120))
END ELSE If @FDateStyle > 0 and @FDateStyle < 3
BEGIN
  SET @Start = CONVERT(datetime, CONVERT(varchar(10), getdate() - @FDateStyle+1 , 120))
  SET @Finish = CONVERT(datetime, CONVERT(varchar(10), getdate() +1 , 120))
END

IF object_id(''tempdb..#tmpDrug1919'') IS NOT NULL DROP TABLE #tmpDrug1919
select * into #tmpDrug1919 from VAJ2 J with(nolock)
 WHERE (@FDateStyle = 0 or (J.VAJ47 >@Start and J.VAJ47 <@Finish))
 and J.VAJ53 = 0 --0=未执行
 and J.BDN01 <= ''3'' 
 
SELECT J.VAJ01, J.VAA01, A.VAA02, A.VAA04, A.VAA05, A.ABW01 
  , ABW02  = CASE A.ABW01 WHEN 1 THEN ''男'' WHEN 2 THEN ''女'' WHEN 0 THEN ''未知'' END--性别
  , VAA10A=ISNULL(CONVERT(VARCHAR, VAA10),'''') +'' ''+ ISNULL((SELECT AAU02 FROM AAU1 U WHERE U.AAU01 = A.AAU01),'''') 
  , ISNULL(Q.BCQ03,'''')+'' ''+ISNULL(E.BCQ04B, '''') AS BCQ04--床号
  , ISNULL(Q.ROWNR, 0) AS BCQ_ROWNR--床位序号  
  , E.BDP02   
  , J.VAJ04, J.VAJ05, J.VAJ09, J.VAA07, J.VAI01, J.VAF01, F.CBM01
  , ISNULL(F.VAF11, 2) AS VAF11
  , VAF11A = CASE WHEN F.VAF11 = 1 and X.BBX20 = 0 THEN 3 --3=长嘱+口服
                  WHEN F.VAF11 = 1 and X.BBX20<> 0 THEN 4 --4=长嘱+其它
                  ELSE ISNULL(F.VAF11,2) END                                    
  , J.BDN01 , (SELECT BDN02 FROM BDN1 WHERE BDN1.BDN01=Y.BDN01) AS BDN02
  , J.BBY01, Y.BBY04 , Y.BBY05, Y.BBY06, Y.BBE02, G.BDG02C , G.BAG07, G.BAG09   
  , CAST(CAST(J.VAJ25/J.VAJ34 AS NUMERIC(18,4)) AS VARCHAR) + '' ''+G.BDG02B AS BDG02B--住院单位数量
  , G.BDG02B as ZyUnit , j.VAJ25/j.VAJ34 as ZyVAJ25
  , CONVERT(VARCHAR, CONVERT(INT, ROUND(J.VAJ25/G.BAG09,0,1))) +'' ''+ G.BDG02C +
      CASE WHEN J.VAJ25 - ROUND(J.VAJ25/G.BAG09,0,1) * G.BAG09 <> 0
           THEN CONVERT(VARCHAR,CONVERT(INT, J.VAJ25 - ROUND(J.VAJ25/G.BAG09,0,1) * G.BAG09)) + '' ''+ Y.BBY08
           ELSE '''' END AS TQtyAbst--数量摘要
  , J.VAJ23, J.VAJ24, J.VAJ25, J.VAJ32, J.VAJ33, J.VAJ34, J.VAJ35, J.VAJ36, J.VAJ37, J.VAJ38 
  , F.VAF59, F.VAF17, F.VAF18, G.BAG03, X.BDG02, X.BBX05, X.BBX20  
  , ISNULL((SELECT TOP 1 P.BAP03 FROM BAP1 P WHERE P.BAP02 = T.BAP02),9) AS BAP01A--剂型 0:口服，1:针剂，9:其它
  , X.BBX05 AS VAF22, F.VAF19, F.VAF26, F.VAF23+V.VAF23 AS VAF23 , ISNULL(F.VAF25 , '''') VAF25 
  , ISNULL(F.VAF58, 0) AS VAF58--给药性质 0.正常 2.离院带院
  , G.BAT02--发药类型
  , T.BAP02--剂型
  , J.BCK01A--住院病区ID
  ,   BCK03A=(SELECT BCK03 FROM BCK1 K WHERE K.BCK01=J.BCK01A)--住院病区
  , J.BCK01B--住院科室ID
  ,   BCK03B=(SELECT BCK03 FROM BCK1 K WHERE K.BCK01=J.BCK01B)--住院科室
  , J.BCK01C--开单科室ID
  ,   BCK03C=(SELECT BCK03 FROM BCK1 K WHERE K.BCK01=J.BCK01C)--开单科室
  , J.BCK01D--执行科室ID
    , BCK03D=(SELECT BCK03 FROM BCK1 K WHERE K.BCK01=J.BCK01D)--执行科室
  , J.BCK01E--给药科室ID
    , BCK03E=(SELECT BCK03 FROM BCK1 K WHERE K.BCK01=J.BCK01E)--给药科室
  , J.BCE03A--划价人
  , J.BCE02B--开单人号, 关联字段:BCE1.BCE02
  , J.BCE03B--开单人, Physician, 一般为医师
  , J.BCE03C--执行者
  , J.VAJ46--业务时间, 记帐时间, 手工时间
  , J.VAJ47--交易时间, 机器时间
  , J.VAJ48--执行ID
  , J.VAJ51--执行时间
  , J.VAJ54--备注
  , z.AAS02--偿付类别
  , z.AAS07--偿付类别颜色 
, ISNULL(K.LSQty , 0) LSQty --规格数量 
, isnull(G.BAT02,'''')+isnull(L.BCD02,'''') BCD02 --货位号 
, CONVERT(bit, 1) AS IsSelect
, %s  AS Caption
, '''' AS BatchNo--产品批号 , J.AAS02 , J.AAS07  
, 1 as canedit , BBT20  , J.VAJ25 as VAD02 
, F.VAF53 , J.ROWNR
,(select top 1 vao15 from vao2 with(nolock) where e.vaa01=vaa01 and e.vae01=vaa07 and vao11=2)  ryzd
,CASE T.BAM01 WHEN 1 THEN ''普通药品'' 
               WHEN 2 THEN ''麻醉药品'' 
               WHEN 3 THEN ''毒性药品''
               WHEN 4 THEN ''第一类精神药品''
               WHEN 5 THEN ''第二类精神药品''
               WHEN 6 THEN ''放射性药品''
 END as BAM02  , G.BLW01 , Y.BBY08 
FROM #tmpDrug1919 J with(nolock)
join BBY1 Y with(nolock) ON Y.BBY01 = J.BBY01
join BAG1 G with(nolock) ON G.BBY01 = Y.BBY01
join VAA1 A with(nolock) ON A.VAA01 = J.VAA01
join VAE1 E with(nolock) ON E.VAE01 = J.VAA07
left join VAF2 F with(nolock) ON J.VAF01 = F.VAF01
left join VAF2 V with(nolock) ON V.VAF01 = F.VAF01A
left join BBX1 X with(nolock) ON X.BBX01 = V.BBX01
left join BBT1 T with(nolock) ON T.BBX01 = G.BBX01
left join BCQ1 Q with(nolock) ON Q.BCK01A= E.BCK01C and Q.BCQ04 = E.BCQ04B 
left join AAS1 z with(nolock) on z.AAS01 = y.AAS01 
left join BCK1 m with(nolock) on m.BCK01 = j.BCK01B 
join (SELECT CASE BAU01 WHEN ''51'' THEN ''1'' --西药房
                        WHEN ''53'' THEN ''2'' --成药房
                        WHEN ''52'' THEN ''3'' --中药房
                        ELSE ''0''  END BAU01 FROM BAZ1 WHERE BCK01 = @PharmacyID) U ON U.BAU01 = J.BDN01
left join V_DPK4 K with(nolock) ON K.BBY01 = J.BBY01 and K.BCK01 = @PharmacyId --药房Id
left join BAL1 L with(nolock) ON L.BCK01 = @PharmacyId and L.BBY01 = J.BBY01
WHERE  %s
  
IF object_id(''tempdb..#tmpDrug1919'') IS NOT NULL DROP TABLE #tmpDrug1919', '2019-04-13 10:43:03', '(8065)冯义强', 0, 0)
else
print 'SYS_Scripts.id=1919 已经存在.'

GO


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