/****** Object:  StoredProcedure [dbo].[GetConfigValue]    Script Date: 07/07/2016 14:39:43 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO


ALTER PROCEDURE [dbo].[GetConfigValue] (
 @ConfigMastid varchar(40),
 @LocId varchar(40),
 @AcMastId varchar(40),
 @AppUserID varchar(40),
 @AcActId varchar(40),
 @AcSuperID varchar(40),
 @SecID varchar(40),
 @rolemastid varchar(40),
 @dt varchar(40)
)
AS
/*
 declare @ConfigMastid varchar(40),
 @LocId varchar(40),
 @AcMastId varchar(40),
 @AppUserID varchar(40),
 @AcActId varchar(40),
 @AcSuperID varchar(40),
 @SecID varchar(40),
 @rolemastid varchar(40)

 set @ConfigMastId = '#CM173'
-- set @AcMastId='0653'
 set @AcActId='#ACACTBANK'
 set @LocId='#H'
 --	SB WITH SCDCC BK SULLIA 65	#ACACTBANK	#ACMASBANKACCOUNTS
 select * from ConfigMast where ConfigMastId=@ConfigMastid


SELECT     ConfigMastId, UserConfigId, AcMastID, AcActID, AcSuperID, ConfigLocID, ConfigSecID, AppUserId, RuleSetId, EDt, ValueType, AnyValue, NumValue, CharValue, DtValue, ConfigKindFieldName, 
                      ConfigKindFieldValue, CharSubValue, RoleOrUser
FROM         UserConfig
WHERE     ConfigMastId = @configmastid and r=0 and t=0
         --  and AcMastID=@AcMastId
           order by AcMastID,AcActID,AcSuperID,ConfigLocID,ConfigSecID,AppUserId,RuleSetId
*/

declare @ValueType varchar(50),@NumValue numeric(18,3),@DtValue datetime,@CharValue  varchar(100),@CharSubValue varchar(50),@recfound int,@IsAc int

--- checking for acconfig
if @AcMastId is not null or @AcActId is not null or @AcSuperID is not null begin
   set @IsAc=1 -- USED TO IGNORE ACMASATID,ACACT,ACSUPER FOR NonAcConfig otherwise record wond come and 
   if @AcMastId is not null and @AcActId is null begin
     select @AcActId=acactid ,@acsuperid=(case @acsuperid when null then acsuperid else @AcSuperID end) from AcMast with (nolock)  where AcMastId=@AcMastId
   end
end


   SELECT top 1 @recfound=1, @ValueType=ValueType,@NumValue= NumValue,@DtValue= DtValue,@CharValue= CharValue,@CharSubValue= CharSubValue 
   FROM UserConfig  with (nolock) 
   WHERE ConfigMastID=@configmastid AND r=0 and t=0 and
   (@isAc is null OR AcMastID= @AcMastId  or AcActID= @AcActID or AcSuperID=@AcSuperID )
   and
   (ConfigLocID=@LocId or  ConfigLocID is null or ConfigLocID='' or ConfigLocID='#ALL' )
   and
   (ConfigSecID=@SecID or   ConfigSecID is null or ConfigSecID='' or ConfigSecID='#ALL' )
   and 
   (AppUserId=isnull(@AppUserID,appuserid) or AppUserId is null   )
   and
   (RuleSetId =isnull(@rolemastid,rulesetid) or RuleSetId is null or RuleSetId in (select rolemastid from UserRole with (nolock)  where AppUserID=@AppUserID and r=0 and t=0))
   and 
   (ConfigDt <=ISNULL(@dt,ConfigDt) or ConfigDt is null)
   order by
   configDt desc,
   (case when AcMastID=@AcMastId then 1 else (case when AcActID =@AcActId then 2 else 3 end) end),
   (case when ConfigLocID=@LocId then 1 else 2 end),
   (case when AppUserId=@AppUserID then 1 else 2 end ),
   (case when RuleSetId=@rolemastid then 1 else (case when RuleSetId is NoT null then 2 else 3 end) end)
   

if @recfound is null begin
 SELECT @ValueType=ValueType,@NumValue= NumValue,@DtValue= DtValue,@CharValue= CharValue,@CharSubValue= CharSubValue
 FROM ConfigMast  with (nolock)  
 WHERE ConfigMastID=@ConfigMastid
end

 SELECT @recfound as RecFound,@ValueType as ValueType,@NumValue as NumValue,@DtValue As DtValue,
 @CharValue As CharValue,@CharSubValue as CharSubValue
GO