if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[emrSPChkLogin]') AND type = 'P')
drop Procedure [dbo].[emrSPChkLogin]
GO

Create Procedure emrSPChkLogin(@PLoginName VARCHAR(100),@PPassWord VARCHAR(100),@PLocationid INT,@PTodayTime VARCHAR(100), @PTodayDay VARCHAR(100), @PCurrentDate VARCHAR(100),@temp  VARCHAR(100) OUTPUT) AS
Declare
	@PGroup_Name	VARCHAR(255),
	@PLoginCnt	INT,
	@PResetValue	INT,
	@PGroup_id	INT,
	@PRetFlag	INT,
	@PPassExpCnt	INT,
	@PLoginTimeDef	INT,
	@PUserLockStatus INT,
	@PAllowToLogin	INT,
	@PRoleCnt	INT,
	@PPassCnt	INT,
	@PUserName	VARCHAR(255),
	@PRoleName	VARCHAR(255),
	@PLocationName	VARCHAR(255),
	@PGroupid	INT,
	@PRoleAbbrid	VARCHAR(100),
	@PCountDays	INT,
	@PPassExp	INT,
	@PTemp1		VARCHAR(1000);
Begin
--	1. Check login credentials. If Record exists with user name and password. If record is not exists return 100 Means Invalid credentials. It should come out from procedure. [RETURN]
--	a. If credentials are fine go to next checking. Point No 2
	Select  @PPassCnt=Count(*)  From Users Where ((Upper(USER_LOGIN))) = ((Upper(@PLoginName))) and ((USER_PASS)) = ((@PPassWord));
	If (@PPassCnt =0) 
	Begin
		set @temp='100'
		--return;
	End
	
--	2. Check user is locked or not. If the user is locked that is having an entry in table LOCKED_USERS then return 101 Means UserLocked. It should come out from procedure. (Table: LOCKED_USERS) [RETURN]
--	a. If user is not locked go to next checking. Point No 3
	SELECT  @PUserLockStatus=Count(USER_LOGIN) FROM LOCKED_USERS WHERE ((Upper(USER_LOGIN))) = ((Upper(@PLoginName))) AND LOCKED_STATUS = 1
	IF (@PUserLockStatus=1) 
	Begin
		set @temp='101'
		--return;
	End

--	3. Check first time login. If first login means need to return back saying first time login change your password. 102 Means FirstTime Login (Table: EMRPASSWORDHISTORY) [RETURN]
--	a. If there is no record in this table EmrPasswordHistory then it is first time login.
--	b. If record is there in the table then it is not first time login go to next checking. Point No 4
	SELECT  @PLoginCnt=count(*) FROM EMRPASSWORDHISTORY WHERE ((Upper(USER_LOGIN))) = ((Upper(@PLoginName)))
	IF (@PLoginCnt = 0) 
	Begin
		set @temp='102'
		--return;
	End
	
--	4. Check password is reset by admin. If password reset by admin. We have to give password reset by admin and return back. 103 Means Password Reset by Admin  (Table: EmrPasswordHistory) [RETURN]
--	a. If reset value is 1 then password is reset by admin. Return from procedure.
--	b. If not go to next checking. Point no 5.
	Begin
		SELECT @PResetValue=RESET  FROM EMRPASSWORDHISTORY WHERE ((Upper(USER_LOGIN))) = ((Upper(@PLoginName))) AND PASSWORD_CREATED_ON = (SELECT MAX(PASSWORD_CREATED_ON)  FROM EMRPASSWORDHISTORY  WHERE ((Upper(USER_LOGIN))) = ((Upper(@PLoginName))) )
		--EXCEPTION 
			--WHEN NO_DATA_FOUND THEN
				set @PResetValue = 0
	End
	IF (@PResetValue = 1) 
	Begin
		set @temp='103';
		--return;
	End

--	5. Check if user is admin user or not. If the user is admin user he may select location or he may not select location. So for admin user location id will have value or will be 0.
--	a. Based on admin user (location selected or not selected) or normal user (location selected) need to fire select query* to get the other details like roles do to next checking.
	Begin
		SELECT @PGroup_Name=B.GROUP_NAME FROM EMRUserRoleLocation A , EMRGroupMaster B   
		WHERE A.USER_LOGIN = B.GROUP_NAME  AND ((Upper(A.USER_LOGIN))) = ((Upper(@PloginName))) AND LOCATION_ID = @PLocationid
		--EXCEPTION 
			--WHEN NO_DATA_FOUND THEN
				set @PGroup_Name = ' ';
	End
	If isnull(@PGroup_Name,'') <> ''  -- If not null then the logged in user is an Admin user
	Begin
		If (@PLocationid =0) 
		Begin
			SELECT  @PUserName=A.USER_NAME, @PRoleName= B.ROLE_NAME, @PGroupid =B.GROUP_ID,@PLocationName= '' --AS LOCATION_NAME, @PRoleabbrid= B.ROLE_ABBR_ID 
			FROM    USERS A,   EMRUserRoleLocation B,   EMRRoleAbbreviationLkup C   
			WHERE   ((Upper(A.USER_LOGIN))) = ((Upper(@PLoginName))) AND A.USER_LOGIN = B.USER_LOGIN  AND 
			((A.USER_PASS)) = ((@PPassWord)) AND B.ROLE_NAME  = C.ROLE_NAME  AND A.USER_STATUS ='active' AND A.LOCKED_USER <> 1 
			ORDER BY C.ROLE_PRIORITY ASC
		End
		Else
		Begin
			SELECT  @PUserName=A.USER_NAME, @PRoleName =B.ROLE_NAME,@PGroupid =  B.GROUP_ID, @PLocationName=D.LOCATION_NAME, @PRoleabbrid= B.ROLE_ABBR_ID  
			FROM    USERS A,   EMRUserRoleLocation B,   EMRRoleAbbreviationLkup C,   EMRLocationMaster D  
			WHERE   ((Upper(A.USER_LOGIN))) = ((Upper(@PLoginName))) AND A.USER_LOGIN = B.USER_LOGIN  AND 
			((A.USER_PASS)) = ((@PPassWord)) AND B.LOCATION_ID = @PLocationid AND D.LOCATION_ID = B.LOCATION_ID  AND 
			B.ROLE_NAME  = C.ROLE_NAME  AND A.USER_STATUS ='active' AND A.LOCKED_USER <> 1 
			ORDER BY C.ROLE_PRIORITY ASC 
		End
	End
	Else
	Begin
		SELECT  @PUserName=A.USER_NAME, @PRoleName= B.ROLE_NAME,@PGroupid = B.GROUP_ID, @PLocationName=D.LOCATION_NAME,@PRoleabbrid=  B.ROLE_ABBR_ID 
		FROM	USERS A,   EMRUserRoleLocation B,   EMRRoleAbbreviationLkup C,   EMRLocationMaster D  
		WHERE   ((Upper(A.USER_LOGIN))) = ((Upper(@PLoginName))) AND A.USER_LOGIN = B.USER_LOGIN  AND 
		((A.USER_PASS)) = ((@PPassWord)) AND B.LOCATION_ID = @PLocationid AND D.LOCATION_ID = B.LOCATION_ID  AND 
		B.ROLE_NAME  = C.ROLE_NAME  AND A.USER_STATUS ='active' AND A.LOCKED_USER <> 1 
		ORDER BY C.ROLE_PRIORITY ASC;
	End

--	6. Check login configuration is there for the Groupid in this table EmrLoginConfigInfo. If login is not configured go to next checking. Point no 7.
--	a. If login is configured, we need to check password is expired or not. If password is expired return. 104 means password expired. [RETURN]
--	b. If login is configured, we need to check password is expired or not. If password is not expired go to next checking. Point no 7.
	SELECT  @PPassExpCnt=Count(password_expiry) from EMRLOGINCONFIGINFO where group_id = @PGroupid;
	If @PPassExpCnt >0   -- Login is configured
	Begin
		SELECT @PCountDays=(SELECT password_expiry FROM EMRLOGINCONFIGINFO WHERE group_id = @PGroupid) - datediff(Day,Convert(Datetime,@PCurrentDate, 120) , password_created_on) 
		
		FROM (SELECT  max(password_created_on) as password_created_on FROM emrpasswordhistory WHERE ((Upper(USER_LOGIN))) = ((Upper(@PLoginName))) ) x ;
		IF (@PCountDays <=0)   -- means Password expired
		set @temp='104';
			--return;
	End


--	7. Check login time is defined for that user. (Table. EMRUserLoginTime) If login time is not defined go to next checking. Point no 8.
--	a. If the login time is defined for the user and if it is over and it is need to return back. 105 Means Login time over.
--	b. If the login time is defined for the user and if it is with login time go to next checking. Point no 8.
	Begin
		SELECT  @PLoginTimeDef=Count(user_login) FROM EMRUserLoginTime where ((Upper(user_login))) = ((Upper(@PLoginName)));
		--EXCEPTION 
			--WHEN NO_DATA_FOUND THEN
				set @PLoginTimeDef = 0;
	End
	If @PLoginTimeDef > 0   -- Login Time Defined
	Begin
		Select @PAllowToLogin=Count(*) From (
        select logintime_id, user_login, upper(login_day) as c,  @PTodayTime as Time_Now, convert(datetime,login_from_time,108) AS From_Time, convert(datetime,login_to_time,108)  AS To_Time 
        From EMRUserLoginTime 
        Where Rtrim(LTrim(upper(login_day))) =  Rtrim(LTrim(upper(@PTodayDay))) and Ltrim(Rtrim(Upper(USER_LOGIN))) = Ltrim(Rtrim(Upper(@PLoginName)))
        ) x 
        Where CONVERT(varchar(35),From_Time,112) <= convert(varchar(35),@PTodayTime,112) and convert(varchar(35),To_Time,108) > convert(datetime,@PTodayTime,108)
		IF (@PAllowToLogin = 0) 
		Begin
		set @temp='105';
			--return;
		End
	End
	

--	8. Check user role is view Role. If the role is not view role, need to go to next checking. Point No 9
--	a. If the user role is view role, then need to find for login expired.(Table: EMRViewOnlyUserExpiry) If login is expired, return.  106 means VIEW ROLE expired
--	b. If view role login time is not expired then allow login. That is return set of data separated by delimiter.
	If (@PRoleName = 'VIEW_ONLY') 
	Begin
		SELECT @PTemp1=convert (datetime,user_expires_on, 112) FROM EMRViewOnlyUserExpiry WHERE ((Upper(user_login))) = ((Upper(@PLoginName)));
	
		IF convert(datetime,@PCurrentDate, 108) >  convert(datetime,@PTemp1,108) 
		Begin
			set @temp='106';
			--return;
		End
		Else
		Begin
		set @temp=@PUserName + '||' + cast(@PGroupid as varchar) + '||' + @PLocationName + '||' + cast(@PRoleabbrid as varchar) + '||' + @PRoleName;
			--return;
		End
		
	End

--	9. Check user id consultant role. If the role is consultant role, then need to check consultant is registered or not.(Table: EMRConsultantDetails) If not registered then return. 107 Means Consultant is Not registered[RETURN]
--	a. If it is not a consultant role. Return set of data. 
--	b. If consultant and registered return set of data.
	IF (@PRoleName = 'CONSULTANT' or @PRoleName = 'NURSE PRACTITIONER') 
	Begin
		SELECT  @PRoleCnt=Count(DISTINCT(ECD.CONSULTANT_ID))
		FROM EMRConsultantDetails ECD,EMRUserRoleLocation EURL, USERS USR 
		WHERE EURL.USER_LOGIN = ECD.CONSULTANT_ID and USR.USER_LOGIN = ECD.CONSULTANT_ID  and EURL.LOCATION_ID = @PLocationid AND USR.USER_STATUS = 'active' AND ((Upper(EURL.USER_LOGIN)))  = ((Upper(@PLoginName)));
		IF (@PRoleCnt >0)
		Begin
			set @temp=@PUserName + '||' + Cast(@PGroupid as Varchar) + '||' + @PLocationName + '||' + cast(@PRoleabbrid as varchar) + '||' + @PRoleName
			--return;
		End
		Else
		Begin
			set @temp='107';
			--return;
		End
	
	End
	Else
	Begin
		set @temp=@PUserName + '||' + Cast(@PGroupid as Varchar) + '||' + @PLocationName + '||' + Cast(@PRoleabbrid as Varchar) + '||' + @PRoleName;
		--return;
	End
End
GO
-------------------------------------------------



--Declare 
--@temp varchar(50)
--Begin
--exec emrSPChkLogin '1501_consul1','10-25-135-234-190-146-39-22',1501,'12:08','Friday','08-01-2010 06:30:00',@temp output
-print @temp
--print 'hi'
--Insert Into VWTEST VAlues(RC1);
--End