drop proc UnsubscribeUser drop proc UserMaster_SubscriptionReport drop proc getLastPaidAmount drop function getFollowUpCount drop function getNoteCount drop function getReminderCount USE [NewVacationChat] GO /****** Object: StoredProcedure [dbo].[ChatGroupDetails_ListData] Script Date: 1/17/2025 2:50:47 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: <17 Dec 2024> -- Description: -- ============================================= alter PROCEDURE [dbo].[ChatGroupDetails_ListData] @AccountId bigint, @ChatGroupId bigint, @UserId bigint AS BEGIN SET NOCOUNT ON; select * from ( SELECT UM.UserId,FirstName AS [Name], CASE WHEN @ChatGroupId=0 AND @UserId= UM.UserId THEN 1 WHEN ISNULL(CGD.USERID,0) =0 THEN 0 ELSE 1 END IsAcive, isnull(ChatGroupDetailId,0) as ChatGroupDetailId, CASE WHEN @ChatGroupId=0 AND @UserId= UM.UserId THEN 0 WHEN CGM.CreatedBy = @UserId and cgd.UserId = @UserId THEN 0 ELSE 1 end IsEnable FROM UserMaster UM LEFT JOIN ChatGroupDetails CGD ON UM.UserId = CGD.UserId AND UM.AccountId = CGD.AccountId AND CGD.ChatGroupId = @ChatGroupId LEFT JOIN ChatGroupMaster CGM on CGM.ChatGroupId = CGD.ChatGroupId AND CGM.AccountId = CGD.AccountId AND CGM.ChatGroupId = @ChatGroupId where UM.AccountId=@AccountId AND UM.IsActive =1 ) as a ORDER BY IsAcive desc ,name ASC END GO /****** Object: StoredProcedure [dbo].[UserMaster_Delete] Script Date: 1/17/2025 2:50:47 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO alter PROCEDURE [dbo].[UserMaster_Delete] @UserId bigint, @AccountId bigint AS BEGIN SET NOCOUNT ON; --commented by gaurav vishwakarma on 5 oct 2020 --DELETE FROM UserMaster --WHERE (@UserId = 0 OR UserId=@UserId) --Added by appmix-trupti on 16-1-2024,to delete user from group details DELETE FROM ChatGroupDetails WHERE UserId = @UserId AND AccountId=@AccountId -- added by gaurav gaurav vishwakarma on 5 oct 2020 update UserMaster set IsActive=0 WHERE (@UserId = 0 OR UserId=@UserId) AND AccountId=@AccountId -- added by gaurav gaurav vishwakarma on 5 oct 2020 to release license update AccountLicenseMaster set UserId=0 WHERE (@UserId = 0 OR UserId=@UserId) and IsActive=1 AND AccountId=@AccountId END GO /****** Object: StoredProcedure [dbo].[UserMaster_Update_Token] Script Date: 1/17/2025 2:50:47 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO alter proc [dbo].[UserMaster_Update_Token] ( @AccountId bigint, @UserId bigint, @DeviceToken nvarchar(max) ) AS BEGIN UPDATE UserMaster SET DeviceToken ='' WHERE DeviceToken=@DeviceToken UPDATE UserMaster SET DeviceToken=@DeviceToken WHERE UserId = @UserId and AccountId=@AccountId END GO /****** Object: StoredProcedure [dbo].[UserMaster_Validation] Script Date: 1/17/2025 2:50:47 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --[UserMaster_Validation] 'nikhilchotaliya@appmix.com','Test1234' alter PROCEDURE [dbo].[UserMaster_Validation] -- Add the parameters for the stored procedure here @EmailAddress varchar(50), @Password nvarchar(max) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT UM.UserId,RoleId,FirstName,LastName ,Convert(nvarchar(250),DECRYPTBYPASSPHRASE('Appmix',[Password],654321,'IES')) As [Password] ,UM.[IsActive],UM.CMDate,UM.CountryId,UM.CurrencyCode,UM.IndustryId, UM.AccountId,VerifyAccount,Convert(nvarchar(MAX),DECRYPTBYPASSPHRASE('Appmix',EmailAddress,654321,'IES')) as EmailAddress, [Address],[City],[Country],[State],[ZipCode], isnull(AccessType,'RO') as AccessType ,ALM.AccountLicenseId,ISNULL(PLM.SubscriptionId,0) NewSubscriptionId, ALM.SubscriptionId,IsNull((SELECT DATEDIFF(DAY,GETdate(),ALM.SubscriptionStartDate)),0) as SubscriptionRemainingDays ,ALM.SubscriptionStartDate,ALM.SubscriptionEndDate,ALM.PaymentStatus,TM.TimeZoneValue,isnull(UM.IsIntroVideoSync,0) as IsIntroVideoSync,UM.ContactNumber ,ISNULL(UM.ViewType,0) as ViewType --Added Appmix Kalpesh on 8-1-2021 for Show Stage or Table view ,ISNULL(UM.UserId,0) AS ChatUserId FROM UserMaster as UM INNER JOIN AccountMaster AM ON AM.AccountId=UM.AccountId INNER JOIN AccountLicenseMaster as ALM on ALM.UserId=UM.UserId LEFT JOIN TimeZoneMaster TM ON TM.TimeZoneId=UM.TimeZoneId LEFT JOIN PendingLicenseMaster PLM ON PLM.AccountLicenseId=ALM.AccountLicenseId WHERE Convert(nvarchar(MAX),DECRYPTBYPASSPHRASE('Appmix',EmailAddress,654321,'IES')) =@EmailAddress AND Convert(nvarchar(250),DECRYPTBYPASSPHRASE('Appmix',[Password],654321,'IES')) COLLATE Latin1_General_CS_AS =@Password AND ALM.IsActive=1 AND AM.IsActive=1 END GO /****** Object: StoredProcedure [dbo].[AgentMaster_ListData] Script Date: 1/17/2025 2:50:47 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO alter PROCEDURE [dbo].[AgentMaster_ListData] -- 964 @UserId bigint = 0, @RoleId bigint = 0, @IsActive tinyint =2, @AccountId bigint = 0 AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SELECT UM.UserId,UM.RoleId,UM.FirstName,TZ.TimeZoneValue,UM.LastName,Convert(nvarchar(250),DECRYPTBYPASSPHRASE('Appmix',UM.[Password],654321,'IES')) As [Password] ,UM.IsActive,UM.CMDate,UM.CountryId,UM.CurrencyCode,UM.IndustryId, RM.RoleName ,RM.RoleDisplayName,(Case UM.IsActive When 1 then 'Active' else 'Inactive/Deleted' End) as StatusLabel ,Convert(nvarchar(MAX),DECRYPTBYPASSPHRASE('Appmix',EmailAddress,654321,'IES')) as EmailAddress, UM.AccountId, (select count(1) from AccountLicenseMaster where accountid=@AccountId and IsActive=1) TotalLicense, IsNull(VerifyAccount,0) as VerifyAccount ,[Address],City,[State],Country,ZipCode,AccessType,UM.TimeZoneId,isnull(UM.IsIntroVideoSync,0) as IsIntroVideoSync , isnull(AgentStartTime,'') as AgentStartTime ,isnull(AgentEndTime,'') as AgentEndTime FROM [dbo].[UserMaster] UM INNER JOIN [dbo].[RoleMaster] RM ON UM.RoleID=RM.RoleId LEFT JOIN TimeZoneMaster TZ ON TZ.TimeZoneId=UM.TimeZoneId WHERE (@UserId=0 or UM.UserId=@UserId) AND (@RoleId=0 or UM.RoleId=@RoleId) AND (@IsActive=2 or UM.IsActive=@IsActive) AND (@AccountId=0 or UM.AccountId=@AccountId) ORDER BY UM.UserId desc --userid to firstname change by gaurav vishwakarma on 6 oct 2020 END GO /****** Object: StoredProcedure [dbo].[UserMaster_FetchAdminDetails] Script Date: 1/17/2025 2:50:47 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= --Created By : Kalpesh Panchal --Created Date: 22th May 2020 --Description : Fetch Account Details and SubscriptionEndDate -- Modified by appmix kalpesh on 22-12-2020 for Show only admin user in the Grid -- Modified by appmix kalpesh on 7-5-2020 for Added Usercount nd other count. -- Modified by appmix kalpesh on 28-5-2021 for Added Phone column. -- Modified by appmix kalpesh on 15-6-2021 for Added Status Column Codition check -- ============================================= alter PROCEDURE [dbo].[UserMaster_FetchAdminDetails] @EmailAddress nvarchar(100)='' AS BEGIN SELECT *fROM ( SELECT RANK() OVER (PARTITION BY UM.ACCOUNTID ORDER BY UM.USERID ASC) ROWNO, UM.AccountId,UM.UserId,UM.FirstName,UM.LastName,UM.CMDate,IsNull(VerifyAccount,0) as VerifyAccount, --(Case UM.IsActive When 1 then 'Active' else 'Inactive' End) as StatusLabel, (Case When (getdate()x.enddate) then 'Expired' else 'Inactive' End) as StatusLabel, --(Case isnull(UM.DeleteAccountDate,'') When '' then 'Active' else 'Deleted' End) as AccountStatus, Convert(nvarchar(MAX),DECRYPTBYPASSPHRASE('Appmix',UM.EmailAddress,654321,'IES')) as EmailAddress ,(select isnull(count(1),0) from UserMaster where AccountId=UM.AccountId) UserCount ,(Select top 1 RM.RoleName from RoleMaster RM where UM.RoleID=RM.RoleId) RoleName ,format(x.enddate,'MM/dd/yyyy') as SubscriptionEndDate ,(select isnull(count(1),0) from AccountLicenseMaster where AccountId=UM.AccountId and IsActive=1) AccountLicenceUserCount ,(select top 1 IsTrialEnd from SubscriptionPlanDetails where AccountId=UM.AccountId and IsActive=1) IsTrialEnd ,UM.Phone FROM UserMaster UM outer APPLY ( SELECT top 1 SubscriptionEndDate as enddate FROM AccountLicenseMaster ALM WHERE UM.AccountId=ALM.AccountId and ALM.IsActive=1 order by alm.AccountLicenseId desc )x ) t WHERE t.ROWNO=1 and (@EmailAddress='' OR EmailAddress=@EmailAddress) ORDER BY t.UserId DESC END GO GO /****** Object: StoredProcedure [dbo].[ChatUserNGroup_ListData] Script Date: 1/17/2025 3:04:01 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: <16 Dec 2024> -- Description: -- ============================================= ALTER PROCEDURE [dbo].[ChatUserNGroup_ListData] @AccountId bigint, @UserId BIGINT AS BEGIN SET NOCOUNT ON; select * from( SELECT UserId as id,FirstName AS [Name],Left(FirstName,1) + LEFT(LASTNAME,1) AS Initials, 'user' as [ChatType],0 as ChatGroupId,0 as IsVisible, ISNULL(A.IsReadCount,0) AS IsReadCount, 0 as type FROM UserMaster UM Outer APPLY --added by apmmix-trupti on 20-12-2024,to display read count ( select count(*) as IsReadCount from MessageDetails MDetail where MDetail.ToChatUserId =@UserId and MDetail.FromChatUserId = um.UserId and MDetail.AccountId = UM.AccountId and isnull(MDetail.IsRead,0) =0 AND ChatGroupId =0 )A where AccountId=@AccountId AND IsActive =1 UNION ALL SELECT CGM.ChatGroupId as id,GroupName AS [Name],Left(GroupName,1) AS Initials, 'group' as [ChatType],CGM.ChatGroupId,case when CreatedBy =@UserId then 1 else 0 end IsVisible, ISNULL(A.IsReadCount,0) AS IsReadCount, 1 as type FROM ChatGroupMaster CGM INNER JOIN ChatGroupDetails CGD ON CGD.ChatGroupId = CGM.ChatGroupId AND CGM.AccountId = CGD.AccountId AND UserId =@UserId INNER JOIN UserMaster um ON CGD.AccountId = um.AccountId AND um.UserId = CGD.UserId Outer APPLY --added by apmmix-trupti on 20-12-2024,to display read count ( select count(*) as IsReadCount from MessageDetails MDetail where MDetail.ToChatUserId = UM.UserId and MDetail.AccountId = UM.AccountId and isnull(MDetail.IsRead,0) =0 AND ChatGroupId =CGM.ChatGroupId )A where CGM.AccountId=@AccountId AND CGM.IsActive =1 and um.IsActive =1 )ChatUserNGroup order by type,Name END =================================================Kalpesh Changes================================================= Webconfig