USE [420923_2013]
GO
/****** Object: StoredProcedure [dbo].[CheckDataMxb] Script Date: 01/29/2013 10:20:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--sp_password
ALTER proc [dbo].[CheckDataMxb](
@Years varchar(6),
@OrgCode varchar(12),
@CoopMedCode varchar(10),
@IDNo varchar(3),
@DiagNo varchar(3),
@Value varchar(5000), --'420583000000||'
@UserCode varchar(12),
@OperType varchar(1),
@Info varchar(1000) output)
as
--新增
declare @HospitalCode varchar(12)
declare @InDate varchar(10)
declare @IllCode varchar(10)
declare @MBID varchar(20)
declare @InputDate varchar(10)
--结算
declare @OutDate varchar(10)
declare @JsDate datetime
declare @TotalFee numeric(10,2)
declare @CanFreeFee numeric(10,2)
declare @FreeFee numeric(10,2)
declare @InterfaceIn varchar(1)
--设置变量
declare @SFileName varchar(5000)
declare @SFileValue varchar(5000)
declare @SFileN varchar(100)
declare @SFileV varchar(100)
declare @GrIsAttend varchar(1)
declare @GrValid varchar(1)
--print @Value
if @OperType in ('1','2','3')
begin
if @CoopMedCode=''
begin
set @Info='1&无登记慢病患者信息，请输入！'
return
end
select @GrIsAttend=isAttend,@GrValid=Valid from p_CbNhGrXx where CoopMedCode=@CoopMedCode and IDNo=@IDNo
if (@GrIsAttend = '2') and (exists(select * from p_ParamLists where ParamName='MxbIsAttend' and ParamValue='2'))
begin
set @Info='1&当前人员未参合不能报销，请检查！'
return
end
if @GrValid = '2'
begin
set @Info='1&当前人员已禁用不能报销，请联系合管办！'
return
end
if @OperType = '1'
begin
if not exists(select 1 from p_paramlists where ParamName='MxbYears' and ParamValue=@Years)
begin
set @Info='1&当前慢病就诊登记年度与慢病系统年度不一致，请检查！'
return
end
if (not exists(select 1 from p_CbNhGrXx where CoopMedCode=@CoopMedCode and IDNo=@IDNo and ((Len(lTrim(rTrim(IDCard)))=15) or (Len(lTrim(rTrim(IDCard)))=18)) ))
and (exists(select 1 from p_Paramlists where ParamName='MxbIDCard' and ParamValue='1') )
begin
set @Info='1&当前患者身份证号信息不完整，请联系合管办！'
return
end
select @SFileName=SubString(@Value,CharIndex('(',@Value)+1,CharIndex(')',@Value)-CharIndex('(',@Value)-1)+','
select @Value=lTrim(rTrim(SubString(@Value,CharIndex(')',@Value)+1,Len(@Value))))+'@'
select @SFileValue=SubString(@Value,CharIndex('(',@Value)+1,CharIndex(')@',@Value)-CharIndex('(',@Value)-1)+','
while Len(@SFileName) > 0
begin
select @SFileN=Ltrim(Rtrim(SubString(@SFileName,1,CharIndex(',',@SFileName)-1))),@SFileV=Replace(Ltrim(Rtrim(SubString(@SFileValue,1,CharIndex(',',@SFileValue)-1))),'''','')
if Upper(@SFileV) = Upper('null') select @SFileV=''
if Upper(@SFileN)=Upper('HospitalCode') select @HospitalCode=@SFileV
if Upper(@SFileN)=Upper('InDate') select @InDate=@SFileV
if Upper(@SFileN)=Upper('IllCode') select @IllCode=@SFileV
if Upper(@SFileN)=Upper('MBID') select @MBID=@SFileV
select @SFileName=SubString(@SFileName,CharIndex(',',@SFileName)+1,Len(@SFileName)),@SFileValue=SubString(@SFileValue,CharIndex(',',@SFileValue)+1,Len(@SFileValue))
end
set @InputDate=CONVERT(varchar(10),GETDATE(),120)
end else if @OperType = '2'
begin
select @HospitalCode=HospitalCode,@InDate=convert(varchar(10),InDate,120),@IllCode=IllCode,@MBID=MBID,@InputDate=convert(varchar(10),InputDate,120)
from c_MxbBxDj
where Years=@Years and OrgCode=@OrgCode and CoopMedCode=@CoopMedCode and IDNo=@IDNo and DiagNo=@DiagNo
select @Value=SubString(@Value,CharIndex('set',@Value)+len('set'),CharIndex('where',@Value)-1)+','
while len(@Value) > 0
begin
select @SFileN=Ltrim(Rtrim(SubString(@Value,1,CharIndex('=',@Value)-1))),@SFileV=Replace(Ltrim(Rtrim(SubString(@Value,CharIndex('=',@Value)+1,CharIndex(',',@Value)-CharIndex('=',@Value)-1))),'''','')
if Upper(@SFileV) = Upper('null') select @SFileV=''
if Upper(@SFileN)=Upper('HospitalCode') select @HospitalCode=@SFileV
if Upper(@SFileN)=Upper('InDate') select @InDate=@SFileV
if Upper(@SFileN)=Upper('IllCode') select @IllCode=@SFileV
if Upper(@SFileN)=Upper('MBID') select @MBID=@SFileV
select @Value=SubString(@Value,CharIndex(',',@Value)+1,Len(@Value))
end
--if @OperType = '3' set @JsDate=CONVERT(varchar(10),GETDATE(),120)
end
if @OperType = '1'
begin
if @HospitalCode=''
begin
set @Info='1&就诊机构不能为空，请输入！'
return
end
if not exists(select 1 from c_MxbCardInfos where CoopMedCode=@CoopMedCode and IDNo=@IDNo and IllCode=@IllCode and CheckStatus='1' and convert(varchar(10),getdate(),126) between StartDate and EndDate)
begin
set @Info='1&当前慢病患者不存在或未审核或不在有效日期内！'
return
end
if exists(select 1 from c_MxbCardInfos where CoopMedCode=@CoopMedCode and IDNo=@IDNo and IllCode=@IllCode and Valid='2')
begin
set @Info='1&慢病患者当前慢病已停用！'
return
end
if not exists(select 1 from c_MxbOrg where OrgCode=@HospitalCode and IllCode=@IllCode)
begin
set @Info='1&当前就诊机构不是登记慢病定点机构！'
return
end
if isnull(@IllCode,'') = ''
begin
set @Info='1&当前疾病不能为空！'
return
end
end
if (@InDate <> @InputDate) and (exists(select 1 from r_UserRights a,r_RoleItem b where a.RoleCode=b.RoleCode and b.RightCode='MxbRegHosp' and a.UserCode=@UserCode and CharIndex('5',Param)=0))
begin
set @Info='1&当前用户无权选择就诊日期，请选择当前日期！'
return
end
if @OperType <> '3'
begin
if datediff(d,@InDate,GetDate()) < 0
begin
set @Info='1&当前患者就诊日期大于当前日期！'
return
end
end
if @OperType = '3'
begin
if exists(select 1 from c_MxbBxDj where Years=@Years and OrgCode=@OrgCode and CoopMedCode=@CoopMedCode and IDNo=@IDNo and DiagNo=@DiagNo and JsDate is not null)
begin
set @Info='1&当前就诊信息已结算！'
return
end
if not exists(select 1 from c_MxbBxDjZb where Years=@Years and OrgCode=@OrgCode and CoopMedCode=@CoopMedCode and IDNo=@IDNo and DiagNo=@DiagNo)
begin
set @Info='1&当前慢病患者无费用信息！'
return
end
if exists(select 1 from c_MxbBxDjZb where Years=@Years and OrgCode=@OrgCode and CoopMedCode=@CoopMedCode and IDNo=@IDNo and DiagNo=@DiagNo and convert(varchar(10),YyDate,120)<>@InDate)
begin
set @Info='1&当前慢病患者有用药时间在就诊时间外，请检查！'
return
end
end
end
if @OperType ='4'
begin
if not exists(select 1 from r_UserRights a,r_RoleItem b where a.RoleCode=b.RoleCode and b.RightCode='MxbGlide' and a.UserCode=@UserCode and CharIndex('7',Param)>0)
begin
set @Info='1&当前用户无退单权限！'
return
end
if exists(select 1 from c_MxbBxDj where Years=@Years and OrgCode=@OrgCode and CoopMedCode=@CoopMedCode and IDNo=@IDNo and DiagNo=@DiagNo and BillCode is not null)
begin
set @Info='1&当前慢病患者已制单，不能退单！'
return
end
select @JsDate=JsDate,@IllCode=IllCode from c_MxbBxDj where Years=@Years and OrgCode=@OrgCode and CoopMedCode=@CoopMedCode and IDNo=@IDNo and DiagNo=@DiagNo
if exists(select 1 from c_MxbBxDj where Years=@Years and CoopMedCode=@CoopMedCode and IDNo=@IDNO and IllCode=@IllCode and JsDate>@JsDate
union all
select 1 from s_MxbBxDj where Years=@Years and CoopMedCode=@CoopMedCode and IDNo=@IDNO and IllCode=@IllCode and JsDate>@JsDate)
begin
set @Info='1&当前慢病患者结算后有结算信息，不能退单！'
return
end
end
set @Info='0'