ALTER PROC [dbo].[HOMR_CheckMustItem]
   @VAA07 int --就诊ID
   ,@MustByDoc int --是否是医生站检查必输项，是则只检查下达出院医嘱前必填项 1为医生站检查 0其它
   ,@VAE01_Out int output --如果有未输的必输项则该参数返回就诊ID
AS
begin
  declare 
    @T_MustItem table(ParamNo int, Value varchar(32))
  declare
    @T_Ret table(VAE01 int)
    
  declare 
    @ParamNo int, 
    @Value varchar(32),
    @VAE89 int,
    @VAE90 int,
    @RowCount int, 
    @SQL_BASIC varchar(8000), --基本信息选择SQL
    @SQL_Diag varchar(512), --出院诊断选择SQL
    @SQL_Oper varchar(512), --手术记录选择SQL
    @SQL_InDiag varchar(256), --入院诊断选择SQL
    @SQL_MzDiag varchar(256) --门诊诊断选择SQL
  
  IF @VAA07 is Null
  BEGIN
    RAISERROR('检查必输项存储过程入参“病人就诊ID（VAA07）”不能为空！', 16, 1) with nowait
    RETURN 1  
  END

  IF @MustByDoc is Null
  BEGIN
    RAISERROR('检查必输项存储过程入参“是否是医生站检查必输项（MustByDoc）”不能为空！', 16, 1) with nowait
    RETURN 1  
  END

  set @VAE01_Out = 0
  if @MustByDoc = 1 --说明是医生站检查，则只能检查 下达出院医嘱前必填 的必填项
  begin
    Insert  @T_MustItem  
    SELECT ParamNo, Value FROM Sys_Parameters
    WHERE ProductID = 500 AND ProgramID = 501 and ParamNo > 200 and LEFT(Value, 1) = 'T' and RIGHT(Value, 2) = 'ue'
    Order By ParamNo
  end
  else begin
    Insert  @T_MustItem
    SELECT ParamNo, Value FROM Sys_Parameters
    WHERE ProductID = 500 AND ProgramID = 501 and ParamNo > 200 and LEFT(Value, 1) = 'T' and RIGHT(Value, 2) = 'se'
    Order By ParamNo
  end

  declare MustItem cursor local FORWARD_ONLY for
  Select ParamNo, Value from @T_MustItem
  
  set @SQL_BASIc = ''
  set @SQL_Diag = ''
  set @SQL_Oper = ''
  set @SQL_InDiag = ''
  set @SQL_MzDiag = ''
  set @VAE89 = 0
  set @VAE90 = 0
  Open MustItem
  fetch next from MustItem into @ParamNo, @Value
  while (@@fetch_status = 0)
  begin
    if @ParamNo = 201 --医疗付款方式 
      set @SQL_BASIC = @SQL_BASIC + ' isnull(VAE1.ABJ01, '''') <> '''''
    else if @ParamNo = 202 --健康卡号
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VAA1.VAA82, '''') <> '''''
    else if @ParamNo = 203 --住院次数
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VAE1.VAE19, 0) <> 0'
    else if @ParamNo = 204 --性别
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VAA1.ABW01, '''') <> '''''
    else if @ParamNo = 205 --出生日期
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VAA1.VAA12, 0) <> 0' 
    else if @ParamNo = 206 --国籍
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VAE1.ACC02, '''') <> '''''
    else if @ParamNo = 207 --民族
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VAA1.ABQ02, '''') <> '''''

    else if @ParamNo = 208 --新生儿出生体重
      set @VAE89 = 1
    else if @ParamNo = 209 --新生儿入院体重
      set @VAE90 = 1
      
    else if @ParamNo = 210 --出生地省
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VCT1.VCT32, '''') <> '''''
    else if @ParamNo = 211 --出生地市
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VCT1.VCT33, '''') <> '''''
    else if @ParamNo = 212 --出生地县
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VCT1.VCT34, '''') <> '''''
    else if @ParamNo = 213 --籍贯省
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VCT1.VCT35, '''') <> '''''
    else if @ParamNo = 214 --籍贯市
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VCT1.VCT36, '''') <> '''''
    else if @ParamNo = 215 --身份证号
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VAA1.VAA15, '''') <> '''''
    else if @ParamNo = 216 --职业
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VAE1.AAT02, '''') <> '''''
    else if @ParamNo = 217 --婚姻
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VAE1.ACK01, '''') <> '''''
    else if @ParamNo = 218 --现住址省
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VAE1.VAE60, '''') <> '''''
    else if @ParamNo = 219 --现住址市
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VAE1.VAE61, '''') <> ''''' 
    else if @ParamNo = 220 --现住址县
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VCT1.VCT41, '''') <> '''''
    else if @ParamNo = 221 --现住址
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VAE1.VAE62, '''') <> '''''
    else if @ParamNo = 222 --现住址电话
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VAE1.VAE63, '''') <> '''''
    else if @ParamNo = 223 --户口地址省
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VCT1.VCT37, '''') <> '''''
    else if @ParamNo = 224 --户口地址市
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VCT1.VCT38, '''') <> '''''
    else if @ParamNo = 225 --户口地址县
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VCT1.VCT39, '''') <> '''''
    else if @ParamNo = 226 --户口地址
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VAE1.VAE57, '''') <> '''''      
    else if @ParamNo = 227 --联系人姓名
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VAE1.VAE68, '''') <> '''''      
    else if @ParamNo = 228 --联系人关系
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VAE1.AAZ02, '''') <> '''''      
    else if @ParamNo = 229 --联系人电话
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VAE1.VAE71, '''') <> '''''      
    else if @ParamNo = 230 --入院途径
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VAE1.ABR01, '''') <> '''''      
    else if @ParamNo = 231 --入院诊断
      set @SQL_InDiag = ' isnull(VAO15, '''') <> '''''        
    else if @ParamNo = 232 --入院诊断编码
    begin
      if @SQL_InDiag = ''
        set @SQL_InDiag = ' isnull(BAK01A, 0) <> 0'        
      else
        set @SQL_InDiag = @SQL_InDiag + ' and isnull(BAK01A, 0) <> 0'        
    end
    else if @ParamNo = 233 --入院情况
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VAE1.ABO01, '''') <> '''''      
    else if @ParamNo = 234 --血型
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VAA1.ABL01, '''') <> '''''      
    else if @ParamNo = 235 --Rh
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VAA1.VAA78, '''') <> '''''      
    else if @ParamNo = 236 --病案质量
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VAM1.VAM33, '''') <> '''''      
    else if @ParamNo = 237 --离院方式
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VAE1.ABV01, '''') <> '''''      
    else if @ParamNo = 239 --病例分型
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VAM1.VAM05, '''') <> '''''      
    else if @ParamNo = 240 --临床路径
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VAM1.VAM96, 0) <> 0'      
    else if @ParamNo = 241 --门（急）诊诊断
      set @SQL_MzDiag = ' isnull(VAO15, '''') <> '''''
    else if @ParamNo = 242 --门（急）诊诊断编码
    begin
      if @SQL_MzDiag = ''
        set @SQL_MzDiag = ' isnull(BAK01A, 0) <> 0'        
      else
        set @SQL_MzDiag = @SQL_MzDiag + ' and isnull(BAK01A, 0) <> 0'        
    end
    else if @ParamNo = 243 --出院诊断_诊断编码
      set @SQL_Diag = ' isnull(BAK01A, 0) <> 0'  
    else if @ParamNo = 244 --出院诊断_入院病情
    begin
      if @SQL_Diag = ''
        set @SQL_Diag =  ' isnull(VAO24, 0) <> 0'
      else
        set @SQL_Diag = ' and isnull(VAO24, 0) <> 0'
    end
    else if @ParamNo = 245 --出院诊断_出院情况
    begin
      if @SQL_Diag = ''
        set @SQL_Diag =  ' isnull(ABX01, '''') <> '''''
      else
        set @SQL_Diag = ' and isnull(ABX01, '''') <> '''''
    end
    else if @ParamNo = 246 --出院诊断_发（生）现日期
    begin
      if @SQL_Diag = ''
        set @SQL_Diag =  ' isnull(VAO19, 0) <> 0'
      else
        set @SQL_Diag = ' and isnull(VAO19, 0) <> 0'
    end
    else if @ParamNo = 247 --手术记录_手术编码
      set @SQL_Oper = ' isnull(BAK02, '') = '''
    else if @ParamNo = 248 --手术记录_手术等级
    begin
      if @SQL_Oper = ''
        set @SQL_Oper =  ' isnull(ACH01, '''') <> '''''
      else
        set @SQL_Oper = ' and isnull(ACH01, '''') <> '''''
    end
    else if @ParamNo = 249 --手术记录_择期手术
    begin
      if @SQL_Oper = ''
        set @SQL_Oper =  ' isnull(ACZ01, '''') <> '''''
      else
        set @SQL_Oper = ' and isnull(ACZ01, '''') <> '''''
    end
    else if @ParamNo = 250 --手术记录_操作部位
    begin
      if @SQL_Oper = ''
        set @SQL_Oper =  ' isnull(BJY01, '''') <> '''''
      else
        set @SQL_Oper = ' and isnull(BJY01, '''') <> '''''
    end
    else if @ParamNo = 251 --手术记录_手术开始时间
    begin
      if @SQL_Oper = ''
        set @SQL_Oper =  ' isnull(RAB17, 0) <> 0'
      else
        set @SQL_Oper = ' and isnull(RAB17, 0) <> 0'
    end
    else if @ParamNo = 252 --手术记录_手术结束时间
    begin
      if @SQL_Oper = ''
        set @SQL_Oper =  ' isnull(RAB18, 0) <> 0'
      else
        set @SQL_Oper = ' and isnull(RAB18, 0) <> 0'
    end
    else if @ParamNo = 253 --科主任
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VAM1.VAM26, '''') <> '''''      
    else if @ParamNo = 254 --主任（副主任医师）
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VAM1.VAM27, '''') <> '''''        
    else if @ParamNo = 255 --主治医师
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VAM1.VAM31, '''') <> '''''      
    else if @ParamNo = 256 --住院医师
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VAE1.BCE03C, '''') <> '''''      
    else if @ParamNo = 257 --责任护士
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VAE1.BCE03B, '''') <> '''''      
    else if @ParamNo = 258 --编码员
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VAM1.VAM68, '''') <> '''''       
    else if @ParamNo = 259 --质控医师
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VAM1.VAM34, '''') <> '''''       
    else if @ParamNo = 260 --质控护士
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VAM1.VAM35, '''') <> '''''       
    else if @ParamNo = 261 --质控日期
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VAM1.VAM36, 0) <> 0'       
    else if @ParamNo = 262 --出院诊断依据
    begin
      if @SQL_Diag = ''
        set @SQL_Diag =  ' isnull(BGP01, '''') <> '''''
      else
        set @SQL_Diag = ' and isnull(BGP01, '''') <> '''''
    end
    else if @ParamNo = 263 --生育状况
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VCT1.VCT60, '''') <> '''''
    else if @ParamNo = 264 --发病时间
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VCT1.VCT61, 0) <> 0'
    else if @ParamNo = 265 --发病地点
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VCT1.ADX01, '''') <> '''''
    else if @ParamNo = 266 --家庭住址区划代码
      set @SQL_BASIC = @SQL_BASIC + ' and isnull(VCT1.VCT63, '''') <> '''''
    fetch next from MustItem into @ParamNo, @Value
  end
  close MustItem
  deallocate MustItem
  
  
  --判断基本信息是否有未输入项
  if @SQL_BASIC <> '' 
  begin
    if left(@SQL_BASIC, 4) = ' and'
    
      set @SQL_Basic = 'select VAE1.VAE01 from VAE1 
        join VAA1 on VAE1.VAA01 = VAA1.VAA01
        join VCT1 on VCT1.VAA07 = VAE1.VAE01
        join VAM1 on VAM1.VAA07 = VAE1.VAE01
        where VAE1.VAE01=' + cast(@VAA07 as varchar(16)) + @SQL_BASIC
    else
      set @SQL_Basic = 'select VAE1.VAE01 from VAE1 
        join VAA1 on VAE1.VAA01 = VAA1.VAA01
        join VCT1 on VCT1.VAA07 = VAE1.VAE01
        join VAM1 on VAM1.VAA07 = VAE1.VAE01
        where VAE1.VAE01=' + cast(@VAA07 as varchar(16)) + ' and ' + @SQL_BASIC 
    
    --print @SQL_BASIC
    Insert into @T_Ret(VAE01)
    exec(@SQL_Basic)
    
    if @@ROWCOUNT < 1
    begin
      set @VAE01_Out = @VAA07
      return
    end
       
  end
  
  --判断入院诊断是否有未输项
  if @SQL_InDiag <> ''
  begin
    set @SQL_InDiag = 'select VAO01 from VAO2 where ACF01=3 and VAO11=2 and VAF01 = 0 and VAA07 =' + cast(@VAA07 as varchar(16)) + ' and ' + @SQL_InDiag
    --print @sql_indiag
    Insert into @T_Ret(VAE01)
    exec(@SQL_InDiag)
    if @@ROWCOUNT < 1
    begin
      set @VAE01_Out = @VAA07
      return
    end
  end
  
  --判断门诊诊断是否有未输项
  if @SQL_MzDiag <> ''
  begin
    set @SQL_MzDiag = 'select VAO01 from VAO2 where ACF01=3 and VAO11=1 and VAF01 = 0 and VAA07 =' + cast(@VAA07 as varchar(16)) + ' and ' + @SQL_MzDiag
    
    --print @sql_Mzdiag
    Insert into @T_Ret(VAE01)
    exec(@SQL_MzDiag)
    if @@ROWCOUNT < 1
    begin
      set @VAE01_Out = @VAA07
      return
    end
  end
  
  --判断手术记录是否有未输项
  if @SQL_Oper <> ''
  begin
    select @RowCount = Count(RAB01) from RAB1 where VAA07 = @VAA07 --取出所有手术记录
    if @RowCount > 0 --说明手术记录
    begin
      set @SQL_Oper = 'select RAB01 from RAB1 where VAA07 =' + cast(@VAA07 as varchar(16)) + ' and ' +  @SQL_Oper
      --print @SQL_Oper
      Insert into @T_Ret(VAE01)    
      exec(@SQL_Oper)
      if @RowCount <> @@ROWCOUNT --如果所有手术记录数不等于符合必须项记录数则报错
      begin
        set @VAE01_Out = @VAA07
        return
      end  
     end
  end
  
  
  
  --判断出院诊断是否有未输项
  if @SQL_Diag <> ''
  begin
    select @RowCount = Count(VAO01) from VAO2 where VAA07 = @VAA07 and ACF01 = 3 and VAO11 = 3 and VAF01 = 0  --取出所有出院诊断
    if @RowCount > 0 --说明有出院诊断
    begin
      set @SQL_Diag = 'select VAO01 from VAO2 where VAA07 =' + cast(@VAA07 as varchar(16)) 
        + ' and ACF01 = 3 and VAO11 = 3 and VAF01 = 0 and ' +  @SQL_Diag
      --print @SQL_Diag
      Insert into @T_Ret(VAE01)
      exec(@SQL_Diag)
      if @RowCount <> @@ROWCOUNT 
      begin
        set @VAE01_Out = @VAA07
        return
      end
    end
    else begin --说明没有出院诊断 直接返回，因为必须有出院诊断
      set @VAE01_Out = @VAA07
      return
    end
  end
  
  --判断新生儿
  if exists(select VAE01 from VAE1 where VAE01 = @VAA07 and ((AAU01 = 'D' and VAE46 < 29) or (AAU01 = 'H' or AAU01 = 'N'))) --说明是新生儿
  begin
    if (@VAE89 = 1) and not exists(select VAE01 from VAE1 where VAE01 = @VAA07 and isnull(VAE89, 0) <> 0) --说明没有入院体重
    begin
      set @VAE01_Out = @VAA07
      return
    end
  
    if (@VAE90 = 1) and not exists(select VAE01 from VAE1 where VAE01 = @VAA07 and isnull(VAE90, 0) <> 0) --说明没有新生儿体重
    begin
      set @VAE01_Out = @VAA07
      return
    end
  end
end