/****** Object: StoredProcedure [dbo].[SP_Members] Script Date: 03/13/2014 10:51:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SP_Members]
@Mode VARCHAR(50) = '',
@MemberID int = 0,
@MemberName varchar(255) = '',
@FirstName varchar(255) = '',
@LastName varchar(255) = '',
@Address1 varchar(255) = '',
@Address2 varchar(255) = '',
@City varchar(255) = '',
@Pincode varchar(10) = '',
@State varchar(255) = '',
@StateID int=0,
@Region varchar(255) = '',
@RegionID int=0,
@Country varchar(255) = '',
@CategoryID int = 0,
@Gender varchar(6) = '',
@Website varchar(50) = '',
@CompanyName varchar(100) = '',
@EmailID varchar(50) = '',
@Password varchar(50) = '',
@OldPassword varchar(50) = '',
@PhoneNo varchar(15) = '',
@MobileNo varchar(15) = '',
@IsApproved bit = false,
@IsAccept bit = false,
@MemberImage varchar(100) = '',
@DeleteFlag bit = false,
@GroupID INT = 0,
@MemberSince VARCHAR(10) = '',
@DirectNo VARCHAR(15) = '',
@FindUs NVARCHAR(255) = '',
@CountryID INT = 0,
@CompanyABN NVARCHAR(50)= '',
@CityID INT = 0,
@IsPaid BIT = 0,
@GoupID INT = 0,
@Latitude NVARCHAR(50) = '',
@Longitude NVARCHAR(50) = '',
@MultiIDs VARCHAR(MAX) ='',
@SortBy VARCHAR(255) ='Name',
@SortOrder VARCHAR(10) = 'ASC',
@SearchField VARCHAR(200) = '',
@SearchValue VARCHAR(200) = ''
AS
BEGIN
DECLARE @Str VARCHAR(MAX)
SET @Str = ''
/*
[SP_Members] @Mode= 'INSERTFromFront',@FirstName='test',@LastName='test',@City='test',
@Pincode='test'
*/
IF @Mode = 'INSERTFromFront'
BEGIN
IF NOT EXISTS(SELECT MemberID FROM Members WHERE EmailID = @EmailID)
BEGIN
INSERT INTO [Members]
([FirstName]
,[LastName]
,[Address2]
,[Pincode]
,[Country]
,[CategoryID]
,[Gender]
,[EmailID]
,[Password]
,[IsApproved]
,[IsAccept]
,[CreatedOn]
,[ModifiedOn]
,[DeleteFlag],
CompanyName, [State], MobileNo, DirectNo, FindUs, CountryID,
IsPaid
)
VALUES
(@FirstName
,@LastName
,@Address2
,@Pincode
,@Country
,@CategoryID
,@Gender
,@EmailID
,@Password
,@IsApproved
,@IsAccept
,GETDATE()
,GETDATE()
,@DeleteFlag,
@CompanyName, @State, @MobileNo, @DirectNo, @FindUs , @CountryID,
@IsPaid
)
Select SCOPE_IDENTITY() AS RETURNVAL
END
ELSE
BEGIN
Select 0 AS RETURNVAL
END
END
IF @Mode = 'INSERT'
BEGIN
IF NOT EXISTS(SELECT MemberID FROM Members WHERE EmailID = @EmailID)
BEGIN
INSERT INTO [Members]
([FirstName],[LastName],[Address1],[Address2],[City],[Pincode],[State],[StateID],[Region],[RegionID],
[Country],[CategoryID],[Gender],[EmailID],[Password],[Website],[CompanyName],[PhoneNo],[MobileNo],
[IsApproved],[IsAccept],[MemberImage],[CreatedOn],[ModifiedOn],[DeleteFlag], IsPaid)
VALUES
(@FirstName,@LastName,@Address1,@Address2,@City,@Pincode,@State,@StateID,@Region,@RegionID,
@Country,@CategoryID,@Gender,@EmailID,@Password,@Website,@CompanyName,@PhoneNo,@MobileNo,
@IsApproved,@IsAccept,@MemberImage,GETDATE(),GETDATE(),@DeleteFlag, @IsPaid)
Select SCOPE_IDENTITY() AS RETURNVAL
END
ELSE
BEGIN
Select 0 AS RETURNVAL
END
END
IF @Mode = 'UPDATE'
BEGIN
IF NOT EXISTS(SELECT MemberID FROM Members WHERE EmailID = @EmailID AND [MemberID] <> @MemberID)
BEGIN
Update [Members] Set
[FirstName] = @FirstName,[LastName] = @LastName,[Address1] = @Address1,[Address2] = @Address2,
[City] = @City,[Pincode] = @Pincode,[State] = @State,[StateID] = @StateID,[Region] = @Region,
[RegionID] = @RegionID,[Country] = @Country,[CategoryID] = @CategoryID,[Gender] = @Gender,
[EmailID] = @EmailID,[Password] = @Password,[Website] = @Website,[CompanyName] = @CompanyName,
[PhoneNo] = @PhoneNo,[MobileNo] = @MobileNo,[IsApproved] = @IsApproved,[IsAccept] = @IsAccept,
[MemberImage]= @MemberImage,[DeleteFlag] = @DeleteFlag
Where [MemberID] = @MemberID
Select @MemberID AS RETURNVAL
END
ELSE
BEGIN
Select 0 AS RETURNVAL
END
END
IF @Mode='DELETE'
BEGIN
IF EXISTS(SELECT MemberID FROM Members WHERE MemberID = @MemberID)
BEGIN
DELETE FROM Members
WHERE MemberID = @MemberID
SELECT @MemberID AS RETURNVAL
END
ELSE
BEGIN
SELECT 0 AS RETURNVAL
END
END
IF @Mode = 'MULTIDELETE'
BEGIN
IF @MultiIDs <> ''
BEGIN
SET @Str = 'DELETE FROM Members WHERE MemberID IN (' + @MultiIDs + ')'
EXEC(@Str)
SELECT 1 AS RETURNVAL
END
ELSE
BEGIN
SELECT 0 AS RETURNVAL
END
END
IF @Mode = 'MULTIACTIVE'
BEGIN
IF @MultiIDs <> ''
BEGIN
SET @Str = 'UPDATE Members SET DeleteFlag = 1 WHERE MemberID IN (' + @MultiIDs + ')'
EXEC(@Str)
SELECT 1 AS RETURNVAL
END
ELSE
BEGIN
SELECT 0 AS RETURNVAL
END
END
IF @Mode = 'MULTIINACTIVE'
BEGIN
IF @MultiIDs <> ''
BEGIN
SET @Str = 'UPDATE Members SET DeleteFlag = 0 WHERE MemberID IN (' + @MultiIDs + ')'
EXEC(@Str)
SELECT 1 AS RETURNVAL
END
ELSE
BEGIN
SELECT 0 AS RETURNVAL
END
END
IF @Mode = 'UpdateForAddress'
BEGIN
IF NOT EXISTS(SELECT MemberID FROM Members WHERE EmailID = @EmailID)
BEGIN
Update [Members]
Set
[Address1] = @Address1
,[Address2] = @Address2
,[City] = @City
,[State] = @State
,[StateID]= @StateID
,[Country] = @Country
,[MemberImage] = @MemberImage
,[Website] = @Website
,[ModifiedOn] = GETDATE(),
CompanyABN = @CompanyABN,
CityID = @CityID,
Latitude = @Latitude,
Longitude = @Longitude
Where [MemberID] = @MemberID
Select @MemberID AS RETURNVAL
END
ELSE
BEGIN
Select 0 AS RETURNVAL
END
END
/*
[SP_Members] @Mode = 'SelectByID' , @MemberID = 47
*/
IF @Mode = 'SelectByID'
BEGIN
SELECT [MemberID],[FirstName],[LastName],[Address1],[Address2],[City],[Pincode]
,[State],[StateID],UserCategories.Name AS CategoryName,[Region],[RegionID],[Country]
,Members.[CategoryID],[Website],[CompanyName],[PhoneNo],[MobileNo],[MemberImage]
,[EmailID],[Password],Members.CreatedOn , dbo.GetStatus(MemberID,0) as eStatus
,dbo.GetGroupName(MemberID,1) as GroupName, dbo.GetGroupName(MemberID,2) as GroupID,
CountryID,StateID, RegionID, CityID, CompanyABN
FROM [Members]
INNER JOIN UserCategories ON Members.CategoryID = UserCategories.CategoryID
WHERE [MemberID] = @MemberID
END
IF @Mode = 'SelectForConfirmation'
BEGIN
SELECT [MemberID],[FirstName],[LastName],[Address1],[Address2],[City],[Pincode],[State],[StateID],
[Region],[RegionID],[Country],[MemberImage],[Website],[CompanyName],[PhoneNo],[MobileNo], CategoryID
FROM [Members] WHERE [EmailID] = @EmailID and [Password] = @Password
END
IF @Mode = 'ChangePassword'
BEGIN
IF EXISTS(SELECT MemberID FROM Members WHERE MemberID = @MemberID and [Password] = @OldPassword)
BEGIN
Update [Members]
Set
[Password] = @Password
,[ModifiedOn] = GETDATE()
Where [MemberID] = @MemberID
Select @MemberID AS RETURNVAL
END
ELSE
BEGIN
Select 0 AS RETURNVAL
END
END
IF @Mode = 'SELECTALL'
BEGIN
SET @Str = 'SELECT Members.MemberID, Members.FirstName + '' '' + Members.LastName as MemberName,
Members.City, Members.Pincode, Members.State, Members.Region, Members.Country,Members.[MobileNo],
Members.[PhoneNo],Members.[Website],Members.[CompanyName],Members.MemberImage, UserCategories.Name AS CategoryName,Status = CASE Members.DeleteFlag
WHEN 1 THEN ''images/icon-active.png'' ELSE ''images/icon-inactive.png'' END
FROM Members INNER JOIN UserCategories ON Members.CategoryID = UserCategories.CategoryID
WHERE 1=1 '
IF @SearchField <> '' AND @SearchValue <> ''
BEGIN
IF @SearchField = 'IsStatus'
BEGIN
SET @Str = @Str + ' AND Members.DeleteFlag = ' + @SearchValue
END
ELSE IF @SearchField = 'MemberName'
BEGIN
SET @Str = @Str + ' AND (Members.FirstName LIKE ''' + @SearchValue + '%'' OR Members.LastName LIKE ''' + @SearchValue + '%'') '
END
ELSE
BEGIN
SET @Str = @Str + ' AND ' + @SearchField + ' LIKE ''' + @SearchValue + '%'' '
END
END
SET @Str = @Str + ' ORDER BY ' + @SortBy + ' ' + @SortOrder
EXEC(@Str)
END
IF @Mode = 'GETMEMBERFRONT'
BEGIN
SET @Str = 'SELECT UserCategories.Name AS CategoryName, Groups.GroupName,
(Members.FirstName + '' '' + Members.LastName) AS MemberName ,
substring(datename(month,Members.CreatedOn),1,3) + '' ''
+ convert(varchar, datepart(year,Members.CreatedOn)) AS CreatedOn,
Members.MemberImage, Members.MemberID,
Members.EmailID, Members.Website, Members.MobileNo,
Members.Country + '', '' + Members.State + '', '' + Members.City AS Address
FROM Members
INNER JOIN UserCategories
ON Members.CategoryID = UserCategories.CategoryID
INNER JOIN GroupMembers
ON Members.MemberID = GroupMembers.MemberID
INNER JOIN Groups
ON GroupMembers.GroupID = Groups.GroupID
WHERE 1=1 '
IF @GroupID > 0
BEGIN
SET @Str = @Str + ' AND Groups.GroupID = ' + CONVERT(VARCHAR, @GroupID)
END
IF LEN(@MemberName) > 0
BEGIN
SET @Str = @Str + ' AND (Members.FirstName LIKE ''' + @MemberName + '%'' OR Members.LastName LIKE ''' + @MemberName + '%'') '
END
IF @CategoryID > 0
BEGIN
SET @Str = @Str + ' AND Members.CategoryID = ' + CONVERT(VARCHAR, @CategoryID)
END
IF LEN(@MemberSince) > 0
BEGIN
SET @Str = @Str + ' AND CONVERT(VARCHAR,Members.CreatedOn,112) >= ' + @MemberSince
END
PRINT(@Str)
EXEC(@Str)
END
IF @Mode = 'VALIDEMAILADDRESS'
BEGIN
IF ((SELECT COUNT(*) FROM Members WHERE EmailID = @EmailID) > 0)
BEGIN
SELECT 0 AS RETURNVAL
END
ELSE
BEGIN
SELECT 1 AS RETURNVAL
END
END
IF @Mode = 'FORGOTPASSWORD'
BEGIN
SELECT MemberID, FirstName, LastName, [Password], EmailID FROM Members
WHERE EmailID = @EmailID
END
IF @Mode = 'CHECKTRIAL'
BEGIN
DECLARE @Paid BIT
SET @Paid = 1
SELECT @Paid = IsPaid FROM Members WHERE EmailID = @EmailID
IF @Paid = 1
BEGIN
SELECT 1 AS RETURNVAL
END
ELSE
BEGIN
DECLARE @Cnt INT
SET @Cnt = 0
SELECT @Cnt = DATEDIFF(DAY, CreatedOn, GETDATE()) FROM Members WHERE EmailID = @EmailID
IF (@Cnt > 30)
BEGIN
SELECT 0 AS RETURNVAL
END
ELSE
BEGIN
SELECT 1 AS RETURNVAL
END
END
END
/*
[SP_Members] @Mode= 'SELECTEDIRECTORY', @Pincode='4350'
*/
IF @Mode = 'SELECTEDIRECTORY'
BEGIN
SET @Str = ' SELECT MemberID, MemberImage, (FirstName + '' '' + LastName) AS MemberName,
DirectNo, MobileNo, EmailID, Website, substring(datename(month,Members.CreatedOn),1,3) + '' ''
+ convert(varchar, datepart(year,Members.CreatedOn)) AS CreatedOn,
dbo.GetMemberGroup(MemberID) AS MemberGroups,
(Country + '', '' + State + '', '' + City) AS Address, UserCategories.Name AS CategoryName
FROM Members INNER JOIN UserCategories ON UserCategories.CategoryID = Members.CategoryID
WHERE 1=1 '
IF LEN(@MemberName) > 0
BEGIN
SET @Str = @Str + ' AND (FirstName LIKE ''' + @MemberName + '%'' OR LastName LIKE ''' + @MemberName + '%'') '
END
IF LEN(@CompanyName) > 0
BEGIN
SET @Str = @Str + ' AND CompanyName like ''' + @CompanyName + '%'''
END
IF @CategoryID > 0
BEGIN
SET @Str = @Str + ' AND CategoryID = ' + CONVERT(VARCHAR,@CategoryID)
END
IF LEN(@Pincode) > 0
BEGIN
SET @Str = @Str + ' AND Pincode = ' + @Pincode
END
EXEC(@Str)
END
IF @Mode = 'UpdatePicture'
BEGIN
Update Members
SET
MemberImage = @MemberImage
WHERE MemberID = @MemberID
SELECT @MemberID AS RETURNVAL
END
IF @Mode = 'EDITPROFILE'
BEGIN
IF NOT EXISTS(SELECT MemberID FROM Members WHERE EmailID = @EmailID AND [MemberID] <> @MemberID)
BEGIN
Update [Members] Set
[FirstName] = @FirstName,[LastName] = @LastName,[Address1] = @Address1,[Address2] = @Address2,
[City] = @City,[Pincode] = @Pincode,[State] = @State,[StateID] = @StateID,[Region] = @Region,
[RegionID] = @RegionID,[Country] = @Country,[CategoryID] = @CategoryID,[Gender] = @Gender,
[EmailID] = @EmailID,[Website] = @Website,[CompanyName] = @CompanyName,
[PhoneNo] = @PhoneNo,[MobileNo] = @MobileNo, CompanyABN = @CompanyABN
Where [MemberID] = @MemberID
Select @MemberID AS RETURNVAL
END
ELSE
BEGIN
Select 0 AS RETURNVAL
END
END
IF @Mode = 'AUTHENTICUSERLOGIN'
BEGIN
IF NOT EXISTS(SELECT MemberID FROM Members WHERE EmailID = @EmailID AND [Password] = @Password)
BEGIN
SELECT 'Invalid UserName Or Password' AS RETURNVAL
END
ELSE
BEGIN
IF NOT EXISTS(SELECT MMID FROM MeetingMaster WHERE GroupID = @GoupID AND IsCompleted = 0)
BEGIN
SELECT 'No Meeting Available' AS RETURNVAL
END
ELSE
BEGIN
--DECLARE @StartDate AS DATETIME
DECLARE @RTNMMID AS INT
declare @RTMemberPic as varchar(100)
--DECLARE @Minute AS INT
--SET @Minute = 0
--SET @RTNMMID = 0
--SELECT @StartDate = StartDateTime, @RTNMMID = MMID FROM MeetingMaster
--WHERE GroupID = @GoupID AND IsCompleted = 0
--SELECT @Minute = DATEDIFF(mi,GETDATE(),@StartDate)
--print(@Minute)
--IF (@Minute >= 1 AND @Minute <= 10)
--BEGIN
-- SELECT @RTNMMID AS RETURNVAL
--END
--ELSE
--BEGIN
-- SELECT 'Still Metting Not Started. Please Wait' AS RETURNVAL
--END
SELECT @RTNMMID = MMID FROM MeetingMaster
WHERE GroupID = @GoupID AND IsCompleted = 0
select @RTMemberPic = MemberImage from Members WHERE EmailID = @EmailID
SELECT @RTNMMID,@RTMemberPic AS RETURNVAL
END
END
END
IF @Mode = 'SELECTBYIDFORFRONT_Webservices'
BEGIN
select FirstName,LastName,EmailID,Address1,Address2,PhoneNo, MemberID
from Members WHERE EmailID = @EmailID
END
IF @Mode = 'UPDATEFROMFRONT_Webservices'
BEGIN
update Members set
FirstName = @FirstName,
LastName = @LastName,
Address1 = @Address1,
Address2 = @Address2,
PhoneNo = @PhoneNo
WHERE EmailID = @EmailID
SELECT 1 AS RETURNVAL
END
END
No comments:
Post a Comment