USE [demo3db]
GO
/****** Object:  StoredProcedure [dbo].[AdminRoleBasedMenuGetById]    Script Date: 04-03-2026 17:46:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[AdminRoleBasedMenuGetById]
	(@UserId bigint
	,@mid nvarchar(512)
    ,@QStatus int OUTPUT)    
        
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	
	SET NOCOUNT ON;

   BEGIN TRY 

	   --DECLARE @RoleId bigint;
	   --SET @RoleId = (SELECT RoleId FROM Roles WHERE RoleName = @RoleName);

	   --SELECT * FROM UserRoles WHERE Menuid=@Menuid  AND UserId = @UserId;
	
SELECT TOP 1 * 
FROM UserRoles 
WHERE Menuid = @mid AND UserId = @UserId
ORDER BY UserRoleId DESC;


	   


	   SELECT @QStatus = 1;
		
    END TRY

    BEGIN CATCH
		 SELECT @QStatus = -1;
    END CATCH		
	 
END











GO
/****** Object:  StoredProcedure [dbo].[AppInfoGetByCompanyEmail]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[AppInfoGetByCompanyEmail]
	(@CompanyEmail nvarchar(128)
    ,@QStatus int OUTPUT)    
        
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	
   SET NOCOUNT ON;

    BEGIN TRY 
		 SELECT  *FROM  AppInfo WHERE   CompanyEmail=@CompanyEmail
		 SELECT @QStatus = 1;
    END TRY
    
    BEGIN CATCH
		 SELECT @QStatus = -1;
    END CATCH	
	 
END













GO
/****** Object:  StoredProcedure [dbo].[AppInfoGetList]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[AppInfoGetList]
   
    @type nvarchar(256),
    @QStatus int output
	
        
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	
	SET NOCOUNT ON;
    
    BEGIN TRY

		SELECT * FROM AppInfo  where TollFreeNumber = @Type

		SELECT @QStatus = 1;
    END TRY

    BEGIN CATCH
		 SELECT @QStatus = -1;
    END CATCH
    	
END

GO
/****** Object:  StoredProcedure [dbo].[AppInfoGetListByChapterId]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[AppInfoGetListByChapterId]
    @ChapterId bigint,
    @QStatus int output
        
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	
	SET NOCOUNT ON;
    
    BEGIN TRY
		SELECT * FROM AppInfo Where ChapterId = @ChapterId
		SELECT @QStatus = 1;
    END TRY

    BEGIN CATCH
		 SELECT @QStatus = -1;
    END CATCH
    	
END
































GO
/****** Object:  StoredProcedure [dbo].[AppInfoInsert]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




CREATE PROCEDURE [dbo].[AppInfoInsert]
    (@AppInfoId bigint
	,@ChapterId bigint
	,@SiteName nvarchar(128)
	,@CompanyAddress nvarchar(512)
	,@CompanyWebSite nvarchar(256)
	,@CompanyEmail nvarchar(60)
	,@CompanyPhone nvarchar(64)
	,@PresidentEmail nvarchar(128)
	,@PresidentPhone nvarchar(25)
	,@SecretaryEmail nvarchar(128)
	,@SecretaryPhone nvarchar(128)
	,@CustomerCareNumber nvarchar(25)
	,@TollFreeNumber nvarchar(25)
	,@FacebookUrl nvarchar(512)
	,@TwitterUrl nvarchar(512)
	,@YoutubeUrl nvarchar(512)
	,@SupportEmail nvarchar(512)
	,@EnqueryEmail nvarchar(512)
	,@PageTitle nvarchar(1024)
	,@MetaDescription nvarchar(max)
	,@MetaKeywords nvarchar(max)
	,@Topline nvarchar(2024)
	,@PageItems int
	,@UpdatedBy nvarchar(64)
	,@UpdatedTime datetime
	,@QStatus int output
	,@BaseUrl	nvarchar(MAX)	
	,@UploadPath	nvarchar(MAX)	
	,@UserUploadPath	nvarchar(MAX)	
	,@UserSiteUrl	nvarchar(1024)	
	,@ServerMapUrl	nvarchar(MAX)	
	,@AdminImageUrl	nvarchar(1024)	
	,@AdminSiteUrl	nvarchar(1024)	
	,@MailName	nvarchar(512)	
	,@SenderEmail	nvarchar(1024)	
	,@MemberEmail	nvarchar(1024)	
	,@ExhibitEmail	nvarchar(1024)	
	,@EventsEmail	nvarchar(1024)	
	,@ContactEmail	nvarchar(1024)	
	,@DonationEmail	nvarchar(1024)	
	,@VolunteerEmail	nvarchar(1024)	
	,@SponsorshipEmail	nvarchar(1024)	
	,@BrevoKey	nvarchar(MAX)	
	,@AndroidVersion	int	
	,@IOSVersion	int	
	,@DesktopVersion	int	
	,@AppUpdate	nvarchar(256)	
	,@CapchaSiteKey	nvarchar(MAX)	
	,@CapchaSecreatKey	nvarchar(MAX)	
	,@ShowCapcha	nvarchar(256)	
	,@InstagramUrl nvarchar(1024)
	,@GooglePlusUrl nvarchar(1024)
	,@WhatsappNumber nvarchar(256)
	,@WhatsappScript nvarchar(MAX)
    ,@GoogleAnalyticsScript nvarchar(MAX)
	,@TimeZones nvarchar(1024)
    ,@CAPTCHA nvarchar(512)
	,@Email nvarchar(512)
	,@LayoutLogo nvarchar(1024) output
	,@faviconlogo nvarchar(1024) output
	,@Loginlogo nvarchar(1024) output

	
	
	)  
        
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	
	SET NOCOUNT ON;

		IF((SELECT COUNT(*) FROM AppInfo WHERE AppInfoId = @AppInfoId) = 1)
		BEGIN
			UPDATE AppInfo
			   SET   SiteName=@SiteName
					,CompanyAddress=@CompanyAddress
					,CompanyWebSite=@CompanyWebSite
					,CompanyEmail= @CompanyEmail
					,CompanyPhone=@CompanyPhone
					,PresidentEmail =@PresidentEmail
					,PresidentPhone=@PresidentPhone
					,SecretaryEmail=@SecretaryEmail
					,SecretaryPhone=@SecretaryPhone
					,CustomerCareNumber=@CustomerCareNumber
					,FacebookUrl=@FacebookUrl
					,TwitterUrl=@TwitterUrl
					,YoutubeUrl=@YoutubeUrl
					,SupportEmail=@SupportEmail
					,EnqueryEmail=@EnqueryEmail
					,PageTitle=@PageTitle
					,MetaDescription=@MetaDescription
					,MetaKeywords=@MetaKeywords
					,Topline=@Topline
					,PageItems=@PageItems
					,UpdatedBy=@UpdatedBy
					,UpdatedTime=@UpdatedTime
					,BaseUrl=@BaseUrl	
					,UploadPath=@UploadPath		
					,UserUploadPath=@UserUploadPath	
					,UserSiteUrl=@UserSiteUrl		
					,ServerMapUrl=@ServerMapUrl	
					,AdminImageUrl=@AdminImageUrl		
					,AdminSiteUrl=@AdminSiteUrl	
					,MailName=@MailName	
					,SenderEmail=@SenderEmail
					,MemberEmail=@MemberEmail	
					,ExhibitEmail=@ExhibitEmail	
					,EventsEmail=@EventsEmail	
					,ContactEmail=@ContactEmail	
					,DonationEmail=@DonationEmail		
					,VolunteerEmail=@VolunteerEmail
					,SponsorshipEmail=@SponsorshipEmail	
					,BrevoKey=@BrevoKey	
					,AndroidVersion=@AndroidVersion
					,IOSVersion=@IOSVersion	
					,DesktopVersion=@DesktopVersion
					,AppUpdate=@AppUpdate	
					,CapchaSiteKey=@CapchaSiteKey		
					,CapchaSecreatKey=@CapchaSecreatKey
					,ShowCapcha=@ShowCapcha
					,InstagramUrl=@InstagramUrl
					,GooglePlusUrl=@GooglePlusUrl
					,WhatsappNumber=@WhatsappNumber
					,WhatsappScript = @WhatsappScript
                   ,GoogleAnalyticsScript=@GoogleAnalyticsScript
				    ,Email=    @Email
					,CAPTCHA=    @CAPTCHA
					,TimeZones=@TimeZones


			 WHERE AppInfoId = @AppInfoId				
				
			SELECT @QStatus = 2;
			
			
			
			
			IF(@LayoutLogo <> 'NA')
			BEGIN
				UPDATE AppInfo
				   SET  LayoutLogo= CAST(@AppInfoId  AS nvarchar(12)) + '-LayoutLogo-' + dbo.RemoveSpecialChar(@SiteName, '^a-z0-9') + '.' + @LayoutLogo
				   WHERE AppInfoId = @AppInfoId	
			  
				  SELECT @LayoutLogo = CAST(@AppInfoId AS nvarchar(12)) + '-LayoutLogo-' + dbo.RemoveSpecialChar(@SiteName, '^a-z0-9') + '.' + @LayoutLogo	
			END	
		ELSE
			BEGIN
			  SELECT @LayoutLogo=''
			END	

	
		      IF(@faviconlogo <> 'NA')
			BEGIN
				UPDATE AppInfo
				   SET  faviconlogo= CAST(@AppInfoId  AS nvarchar(12)) + '-faviconlogo-' + dbo.RemoveSpecialChar(@SiteName, '^a-z0-9') + '.' + @faviconlogo
				   WHERE AppInfoId = @AppInfoId	
			  
				  SELECT @faviconlogo = CAST(@AppInfoId AS nvarchar(12)) + '-faviconlogo-' + dbo.RemoveSpecialChar(@SiteName, '^a-z0-9') + '.' + @faviconlogo	
			END	
		ELSE
			BEGIN
			  SELECT @faviconlogo=''
			END	




				IF(@Loginlogo  <> 'NA' )
			BEGIN
				UPDATE AppInfo
				   SET  Loginlogo= CAST(@AppInfoId  AS nvarchar(12)) + '-Loginlogo-' + dbo.RemoveSpecialChar(@SiteName, '^a-z0-9') + '.' + @Loginlogo
				   WHERE AppInfoId = @AppInfoId	
			  
				  SELECT @Loginlogo  = CAST(@AppInfoId AS nvarchar(12)) + '-Loginlogo-' + dbo.RemoveSpecialChar(@SiteName, '^a-z0-9') + '.' + @Loginlogo	
			END	
		ELSE
			BEGIN
			  SELECT @Loginlogo =''
			END	




		END



		ELSE
		BEGIN
			 INSERT INTO AppInfo VALUES
				(   @ChapterId 
				    ,@SiteName
					,@CompanyAddress
					,@CompanyWebSite
					,@CompanyEmail 
					,@CompanyPhone
					,@PresidentEmail 
					,@PresidentPhone
					,@SecretaryEmail
					,@SecretaryPhone
					,@CustomerCareNumber
					,@TollFreeNumber
					,@FacebookUrl
					,@TwitterUrl
					,@YoutubeUrl
					,@SupportEmail
					,@EnqueryEmail
					,@PageTitle
					,@MetaDescription
					,@MetaKeywords
					,@Topline
					,@PageItems
					,@UpdatedBy
					,@UpdatedTime
					,@BaseUrl	
					,@UploadPath		
					,@UserUploadPath	
					,@UserSiteUrl		
					,@ServerMapUrl	
					,@AdminImageUrl		
					,@AdminSiteUrl	
					,@MailName	
					,@SenderEmail
					,@MemberEmail	
					,@ExhibitEmail	
					,@EventsEmail	
					,@ContactEmail	
					,@DonationEmail		
					,@VolunteerEmail
					,@SponsorshipEmail	
					,@BrevoKey	
					,@AndroidVersion
					,@IOSVersion	
					,@DesktopVersion
					,@AppUpdate	
					,@CapchaSiteKey		
					,@CapchaSecreatKey
					,@ShowCapcha
					,@InstagramUrl
					,@GooglePlusUrl
					,@WhatsappNumber
					,@GoogleAnalyticsScript
					,@WhatsappScript
					,@TimeZones
					,@Email
					,@CAPTCHA
					,@LayoutLogo 
	                ,@faviconlogo 
	                ,@Loginlogo 

					
					)	
			 		 
			 SELECT @QStatus = 1;	
		END

END


















GO
/****** Object:  StoredProcedure [dbo].[APPPageDetailsGetListById]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[APPPageDetailsGetListById]
	 (@ChapterId bigint
	 ,@Heading nvarchar(512)
	 ,@PageUrl nvarchar(512)
	 ,@MenuItemId bigint
	 ,@IDPath nvarchar(128) output
     ,@QStatus int output)
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	
	SET NOCOUNT ON;
	
	BEGIN TRY
			DECLARE @Query nvarchar(max);
			Declare @PageDetailId bigint
    
			IF(@PageUrl <> '')
				BEGIN
					IF((Select Count(*) From PageDetails Where PageUrl = @PageUrl) = 1)
					BEGIN
						SELECT  * FROM PageDetails WHERE PageUrl = @PageUrl
					END
				END 
			ELSE If(@MenuItemId <> 0)
				BEGIN
					Select @IDPath = IDPath From MenuItems Where MenuItemId = @MenuItemId
				END

			Select @PageDetailId = PageDetailId from MenuPages Where MenuItemId = @MenuItemId

			IF(@PageDetailId<>0)
				BEGIN
					 SELECT  * FROM PageDetails WHERE PageDetailId=@PageDetailId 		
				END  
			ELSE IF(@Heading<>'')
				BEGIN
					 SELECT  * FROM PageDetails WHERE dbo.RemoveSpecialChar(Heading, '^a-z0-9') = dbo.RemoveSpecialChar(@Heading, '^a-z0-9')
				END	

			SELECT @QStatus = 1; 
	END TRY   
	
    BEGIN CATCH  
       SELECT @QStatus = -1; 		   
    END CATCH   
END











GO
/****** Object:  StoredProcedure [dbo].[CarrierRegistrationInsert]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[CarrierRegistrationInsert]
(
    @RegistrationId bigint,
    @CarrierId bigint,
    @FirstName nvarchar(256),
	@LastName nvarchar(256),
	@PreferredFirstName nvarchar(256),
    @Email nvarchar(256),
    @Phone nvarchar(50),
    @ExperienceYears int,
    @ResumeURL nvarchar(512),
	@linkedURL nvarchar(512),
	@WebsiteURL nvarchar(512),
    @Status nvarchar(64),
    @AdminComments nvarchar(max),
    @CreatedDate datetime,
    @CreatedBy nvarchar(256),
    @UpdatedDate datetime,
    @UpdatedBy nvarchar(256),
    @QStatus int output,
	@IpAddress nvarchar(512)
)
as
begin
    set nocount on;

    begin try
        begin transaction;

        if ((select count(*) from CarrierRegistrations where  Email = @Email) = 0)
        begin
            insert into CarrierRegistrations
            values
            (
                @CarrierId,
                @FirstName,
				@LastName,
				@PreferredFirstName,
                @Email,
                @Phone,
                @ExperienceYears,
                @ResumeURL,
			    @linkedURL,
			    @WebsiteURL,
                @Status,
                @AdminComments,
                @CreatedDate,
                @CreatedBy,
                @UpdatedDate,
                @UpdatedBy,
				@IpAddress
            );


			  set @RegistrationId = scope_identity();

            select @QStatus = 1;
          
        end
        else
        begin
            update CarrierRegistrations
            set CarrierId       = @CarrierId,
                FirstName            = @FirstName,
				LastName            = @LastName,
				PreferredFirstName            = @PreferredFirstName,
                Email           = @Email,
                Phone           = @Phone,
                ExperienceYears = @ExperienceYears,
                ResumeURL       = @ResumeURL,
                Status          = @Status,
                AdminComments   = @AdminComments,
				linkedURL     = @linkedURL,
				WebsiteURL     = @WebsiteURL,
                UpdatedDate     = @UpdatedDate,
                UpdatedBy       = @UpdatedBy,
				IpAddress      = @IpAddress

            where  RegistrationId = @RegistrationId;

     
            

            select @QStatus = 2;
        end

        commit transaction;
    end try

    begin catch
        rollback transaction;
        select @QStatus = -1;
    end catch
end

GO
/****** Object:  StoredProcedure [dbo].[CarrierRegistrationsDelete]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[CarrierRegistrationsDelete]
    (@RegistrationId bigint
   ,@QStatus int output)  
        
AS
BEGIN
	
	SET NOCOUNT ON;
    
    BEGIN TRY    
		BEGIN TRANSACTION
			DELETE FROM CarrierRegistrations WHERE RegistrationId = @RegistrationId
			SELECT @QStatus = 1;
		COMMIT TRANSACTION
    END TRY

    BEGIN CATCH
         ROLLBACK TRANSACTION     
		 SELECT @QStatus = -1;
    END CATCH
    	
END

















GO
/****** Object:  StoredProcedure [dbo].[CarrierRegistrationsGetById]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[CarrierRegistrationsGetById]
(
    @CarrierId bigint,
    @QStatus int output
)
as
begin
    set nocount on;
	select * from carrierregistrations where Carrierid = @CarrierId
   
end

GO
/****** Object:  StoredProcedure [dbo].[CarrierRegistrationsGetInfoById]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create procedure [dbo].[CarrierRegistrationsGetInfoById]
(
    @RegistrationId bigint,
    @QStatus int output
)
as
begin
    set nocount on;

    begin try
        if(@RegistrationId != 0)
        begin
            select 
                CR.*,
                C.JobTitle
               
            from CarrierRegistrations CR
            left join Carriers C on CR.CarrierId = C.CarrierId
    
            where CR.RegistrationId = @RegistrationId;
        end

        select @QStatus = 1;
    end try
    begin catch
        select @QStatus = -1;
    end catch
end

GO
/****** Object:  StoredProcedure [dbo].[CarrierRegistrationsGetList]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[CarrierRegistrationsGetList]
    @QStatus int output
        
AS
BEGIN
	
	SET NOCOUNT ON;
    
    BEGIN TRY 
		SELECT * FROM CarrierRegistrations	
		SELECT @QStatus = 1;
    END TRY

    BEGIN CATCH
		 SELECT @QStatus = -1;
    END CATCH
    	
END






































GO
/****** Object:  StoredProcedure [dbo].[CarrierRegistrationsGetListByVariable]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[CarrierRegistrationsGetListByVariable]
(
     @CarrierId bigint,
     @PageNo int,
     @Items int,
     @Search nvarchar(126),
     @Total int output,
     @Sort nvarchar(126)
)
as
begin
    set nocount on;
    
    declare @Start int, @End int;
    declare @Query nvarchar(max);
    
    create table #temp
    (
        Rid int primary key identity(1,1),
        RegistrationId bigint,
        CarrierId bigint,
		JobTitle nvarchar(256),
        FirstName nvarchar(256),
	    LastName nvarchar(256),
		PreferredFirstName nvarchar(256),
        Email nvarchar(256),
        Phone nvarchar(50),
        ExperienceYears int,
        ResumeURL nvarchar(512),
		linkedURL nvarchar(512),
		WebsiteURL nvarchar(512),
        Status nvarchar(64),
        AdminComments nvarchar(max),
        CreatedDate datetime,
        CreatedBy nvarchar(256),
        UpdatedDate datetime,
        UpdatedBy nvarchar(256)
    );

    select @Start = (@PageNo - 1) * @Items;
    select @End   = @Start + @Items;

    set @Query = 'insert into #temp
        select 
            CR.RegistrationId,
            CR.CarrierId,
		    C.JobTitle,
            CR.FirstName,
			CR.LastName,
		    CR.PreferredFirstName,
            CR.Email,
            CR.Phone,
            CR.ExperienceYears,
            CR.ResumeURL,
			CR.linkedURL,
			CR.WebsiteURL,
            CR.Status,
            CR.AdminComments,
            CR.CreatedDate,
            CR.CreatedBy,
            CR.UpdatedDate,
            CR.UpdatedBy
       FROM CarrierRegistrations CR 
			
				LEFT JOIN Carriers C ON C.CarrierId= CR.CarrierId
				WHERE CR.RegistrationId <> 0';

    if (@CarrierId <> 0)
    begin
        set @Query = @Query + ' and CR.CarrierId = ' + cast(@CarrierId as nvarchar(250));
    end

    if (@Search <> '')
    begin
        set @Query = @Query + ' and (CR.FirstName like ''%' + @Search + '%'' 
                                or CR.Email like ''%' + @Search + '%'' 
                                or CR.Phone like ''%' + @Search + '%''
								   or C.JobTitle like ''%' + @Search + '%''
								)';
							
    end

    if (@Sort <> '')
    begin
        set @Query = @Query + ' order by ' + @Sort;
    end

    exec sp_executesql @Query;

    select @Total = count(Rid) from #temp;
    
    select * from #temp where Rid > @Start and Rid <= @End;
    
    drop table #temp;
end

GO
/****** Object:  StoredProcedure [dbo].[CarrierRegistrationsUpdate]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[CarrierRegistrationsUpdate]
    @RegistrationId BIGINT,
    @Status NVARCHAR(50),
    @QStatus INT OUTPUT  
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY    
        BEGIN TRANSACTION;

        UPDATE CarrierRegistrations
        SET Status = @Status
        WHERE RegistrationId = @RegistrationId;

        SET @QStatus = 1;
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;     
        SET @QStatus = -1;
    END CATCH
END
















GO
/****** Object:  StoredProcedure [dbo].[CarriersDelete]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[CarriersDelete]
    (@CarrierId bigint
    ,@QStatus int output )  
        
AS
BEGIN
	
	SET NOCOUNT ON;
    
    BEGIN TRY 
		BEGIN TRANSACTION   
			DELETE FROM Carriers
			WHERE CarrierId = @CarrierId	    
			SELECT @QStatus = 1;
		COMMIT TRANSACTION	
    END TRY

    BEGIN CATCH
		 ROLLBACK TRANSACTION
		 SELECT @QStatus = -1;
    END CATCH
    	
END






































GO
/****** Object:  StoredProcedure [dbo].[CarriersGetById]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[CarriersGetById]
	(@CarrierId bigint,
     @QStatus int OUTPUT)    
AS
BEGIN
   SET NOCOUNT ON;

	BEGIN TRY 
		SELECT  CarrierId,
				JobTitle,
				ShortDescription,
				JobDescription,
				EmploymentType,
				ExperienceLevel,
				NoOfPositions,
				NoOfPeopleToHire,
				HiringTimeline,
				SkillId,
				CompanyName,
				ImageURL,
				Address,
				AreaName,
				City,
				State,
				Country,
				PostalCode,
				SalaryMin,
				SalaryMax,
				PayDisplayType,
				PayFrequency,
				Benefits,
				Languages,
				ApplyURL,
				IsActive,
				OrderNo,
				CreatedDate,
				CreatedBy,
				UpdatedDate,
				UpdatedBy,
				Skills
		FROM Carriers 
		WHERE CarrierId = @CarrierId
		 
		SELECT @QStatus = 1;   -- Success
		 
	END TRY
    
	BEGIN CATCH
		SELECT @QStatus = -1;  -- Failure
	END CATCH	 
END

GO
/****** Object:  StoredProcedure [dbo].[CarriersGetList]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
  
CREATE PROCEDURE [dbo].[CarriersGetList]  
    @QStatus int output  
          
AS  
BEGIN  
   
 SET NOCOUNT ON;  
      
    BEGIN TRY   
  SELECT * FROM Carriers   where IsActive = 1
  SELECT @QStatus = 1;  
    END TRY  
  
    BEGIN CATCH  
   SELECT @QStatus = -1;  
    END CATCH  
       
END  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

GO
/****** Object:  StoredProcedure [dbo].[CarriersGetListByVariable]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[CarriersGetListByVariable]
	(@Search nvarchar(126),
     @Sort nvarchar(126),
	 @PageNo int,
     @Items int,
     @Total int output)
AS
BEGIN
	SET NOCOUNT ON;
	
	BEGIN TRY
		DECLARE @Start int, @End int;
		DECLARE @Query nvarchar(max);
    
		CREATE TABLE #temp(
			 Rid int PRIMARY KEY IDENTITY(1,1),
			 CarrierId bigint,
			 JobTitle nvarchar(256),
			 ShortDescription nvarchar(max),
			 JobDescription nvarchar(max),
			 EmploymentType nvarchar(100),
			 ExperienceLevel nvarchar(100),
			 NoOfPositions int,
			 NoOfPeopleToHire int,
			 HiringTimeline nvarchar(100),
			 SkillId bigint,
			 CompanyName nvarchar(256),
			 ImageURL nvarchar(512),
			 Address nvarchar(256),
			 AreaName nvarchar(256),
			 City nvarchar(256),
			 State nvarchar(256),
			 Country nvarchar(256),
			 PostalCode nvarchar(20),
			 SalaryMin decimal(18,2),
			 SalaryMax decimal(18,2),
			 PayDisplayType nvarchar(50),
			 PayFrequency nvarchar(50),
			 Benefits nvarchar(max),
			 Languages nvarchar(256),
			  
			 ApplyURL nvarchar(512),
			 IsActive bit,
			 OrderNo bigint,
			 CreatedDate datetime,
			 CreatedBy nvarchar(256),
			 UpdatedDate datetime,
			 UpdatedBy nvarchar(256),
			 Skills nvarchar(1024),
			 SkillName nvarchar(256)
		)

		SELECT @Start = (@PageNo - 1) * @Items  
	    SELECT @End = @Start + @Items 
	
		SET @Query = 'INSERT INTO #temp 
					  SELECT
					   C.CarrierId,
					   C.JobTitle,
					   C.ShortDescription,
					   C.JobDescription,
					   C.EmploymentType,
					   C.ExperienceLevel,
					   C.NoOfPositions,
					   C.NoOfPeopleToHire,
					   C.HiringTimeline,
					   C.SkillId,
					   C.CompanyName,
					   C.ImageURL,
					   C.Address,
					   C.AreaName,
					   C.City,
					   C.State,
					   C.Country,
					   C.PostalCode,
					   C.SalaryMin,
					   C.SalaryMax,
					   C.PayDisplayType,
					   C.PayFrequency,
					   C.Benefits,
					   C.Languages,
					    
					   C.ApplyURL,
					   C.IsActive,
					   C.OrderNo,
					   C.CreatedDate,
					   C.CreatedBy,
					   C.UpdatedDate,
					   C.UpdatedBy,
					   C.Skills,
					   S.SkillName
					  FROM Carriers C 
					  inner join Skills S on S.SkillId =C.SkillId
					  
					  WHERE C.CarrierId <> 0 '
		
		IF(@Search <> '')
		BEGIN
			SET @Query = @Query + ' AND C.JobTitle LIKE ''%' + @Search + '%'''
		END

		IF(@Sort <> '')
		BEGIN
			SET @Query = @Query + ' ORDER BY ' + @Sort
		END
	
		EXEC sp_ExecuteSQL @Query;
		
		SELECT @Total = COUNT(Rid) from #temp 
				
		SELECT * FROM #temp WHERE Rid > @Start AND  Rid <= @End
	
		DROP TABLE #temp

	END TRY
    
    BEGIN CATCH  
       SELECT @Total = -1; 		   	   
    END CATCH   
END

GO
/****** Object:  StoredProcedure [dbo].[CarriersInsert]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[CarriersInsert]
     @CarrierId        bigint ,
    @JobTitle         nvarchar(256),
    @ShortDescription nvarchar(max),
    @JobDescription   nvarchar(max),
    @EmploymentType   nvarchar(100),
    @ExperienceLevel  nvarchar(100),
    @NoOfPositions    int,
    @NoOfPeopleToHire int,
    @HiringTimeline   nvarchar(100),
    @SkillId          bigint,
    @CompanyName      nvarchar(256),
    @ImageURL         nvarchar(512) output,
    @Address          nvarchar(256),
    @AreaName         nvarchar(256),
    @City             nvarchar(256),
    @State            nvarchar(256),
    @Country          nvarchar(256),
    @PostalCode       nvarchar(20),
    @SalaryMin        decimal(18,2),
    @SalaryMax        decimal(18,2),
    @PayDisplayType   nvarchar(50),
    @PayFrequency     nvarchar(50),
    @Benefits         nvarchar(max),
    @Languages        nvarchar(256),

    @ApplyURL         nvarchar(512),
    @IsActive         bit,
    @OrderNo          bigint,
    @CreatedDate      datetime,
    @CreatedBy        nvarchar(256),
    @UpdatedDate      datetime,
    @UpdatedBy        nvarchar(256),
    @QStatus          int output,
	@Skills           nvarchar(1024)

AS
BEGIN
    SET NOCOUNT ON;

    --BEGIN TRY
    --    BEGIN TRANSACTION;

        -- If record does not exist, insert new
        IF ((SELECT COUNT(*) FROM Carriers WHERE CarrierId = @CarrierId) = 0)
        BEGIN
            INSERT INTO Carriers
            VALUES
            ( @JobTitle
             ,@ShortDescription
             ,@JobDescription
             ,@EmploymentType
             ,@ExperienceLevel
             ,@NoOfPositions
             ,@NoOfPeopleToHire
             ,@HiringTimeline
             ,@SkillId
             ,@CompanyName
             ,Null
             ,@Address
             ,@AreaName
             ,@City
             ,@State
             ,@Country
             ,@PostalCode
             ,@SalaryMin
             ,@SalaryMax
             ,@PayDisplayType
             ,@PayFrequency
             ,@Benefits
             ,@Languages
		
             ,@ApplyURL
             ,@IsActive
             ,@OrderNo
             ,@CreatedDate
             ,@CreatedBy
             ,@UpdatedDate
             ,@UpdatedBy
			 ,@Skills)
	 
			  	SET @CarrierId=SCOPE_IDENTITY()

            SELECT @QStatus = 1;   -- Insert Success
        END
        ELSE
        BEGIN
            -- Update existing record
            UPDATE Carriers
            SET
                JobTitle         = @JobTitle,
                ShortDescription = @ShortDescription,
                JobDescription   = @JobDescription,
                EmploymentType   = @EmploymentType,
                ExperienceLevel  = @ExperienceLevel,
                NoOfPositions    = @NoOfPositions,
                NoOfPeopleToHire = @NoOfPeopleToHire,
                HiringTimeline   = @HiringTimeline,
                SkillId          = @SkillId,
                CompanyName      = @CompanyName,
          
                Address          = @Address,
                AreaName         = @AreaName,
                City             = @City,
                State            = @State,
                Country          = @Country,
                PostalCode       = @PostalCode,
                SalaryMin        = @SalaryMin,
                SalaryMax        = @SalaryMax,
                PayDisplayType   = @PayDisplayType,
                PayFrequency     = @PayFrequency,
                Benefits         = @Benefits,
                Languages        = @Languages,
			
                ApplyURL         = @ApplyURL,
           
                OrderNo          = @OrderNo,
                UpdatedDate      = @UpdatedDate,
                UpdatedBy        = @UpdatedBy,
				Skills        = @Skills

	
            WHERE CarrierId = @CarrierId;
			

            SELECT @QStatus = 2;   -- Update Success
        END

		 IF(@ImageURL <> 'NA')
			BEGIN
				UPDATE Carriers
				   SET  ImageURL= CAST(@CarrierId  AS nvarchar(12)) + '-' + dbo.RemoveSpecialChar(@JobTitle, '^a-z0-9') + '.' + @ImageURL
				   WHERE CarrierId = @CarrierId	
			  
				  SELECT @ImageURL= CAST(@CarrierId AS nvarchar(12)) + '-' + dbo.RemoveSpecialChar(@JobTitle, '^a-z0-9') + '.' + @ImageURL	
			END	
		ELSE
			BEGIN
			  SELECT @ImageURL=''
			END	

    --    COMMIT TRANSACTION;
    --END TRY

    --BEGIN CATCH
    --    ROLLBACK TRANSACTION;
    --    SELECT @QStatus = -1; -- Error
    --END CATCH
END

GO
/****** Object:  StoredProcedure [dbo].[CarriersUpdateOrderNo]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[CarriersUpdateOrderNo]
    (@CarrierId bigint
    ,@OrderNo int
    ,@QStatus int output)  
        
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	
	SET NOCOUNT ON;
    
    BEGIN TRY    
		BEGIN TRANSACTION
    
			IF((SELECT COUNT(*) FROM Carriers WHERE CarrierId = @CarrierId) = 1)
				BEGIN
					UPDATE Carriers SET OrderNo = @OrderNo WHERE CarrierId = @CarrierId
					SELECT @QStatus = 1;
				END
			ELSE
				BEGIN
					SELECT @QStatus = 3;
				END	
		COMMIT TRANSACTION
    END TRY

    BEGIN CATCH
         ROLLBACK TRANSACTION
		 SELECT @QStatus = -1;
    END CATCH
    	
END













GO
/****** Object:  StoredProcedure [dbo].[CarriersUpdateStatus]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[CarriersUpdateStatus]
    (@CarrierId bigint,
     @QStatus int OUTPUT)  
AS
BEGIN
	SET NOCOUNT ON;
    
    BEGIN TRY    
		BEGIN TRANSACTION;
    
			IF ((SELECT IsActive FROM Carriers WHERE CarrierId = @CarrierId) = 1)
			BEGIN
				UPDATE Carriers SET IsActive = 0 WHERE CarrierId = @CarrierId;
			END
			ELSE
			BEGIN
				UPDATE Carriers SET IsActive = 1 WHERE CarrierId = @CarrierId;
			END		
		
			SELECT @QStatus = 1;   -- Success
		
		COMMIT TRANSACTION;
    END TRY

    BEGIN CATCH
         ROLLBACK TRANSACTION;
		 SELECT @QStatus = -1;   -- Failure
    END CATCH
    	
END

GO
/****** Object:  StoredProcedure [dbo].[CustomerDocumentsDelete]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[CustomerDocumentsDelete]
    (@DocumentId bigint
    ,@QStatus int output)  
        
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.	
	SET NOCOUNT ON;
    -- Insert statements for procedure here
    
    BEGIN TRY    
    BEGIN TRANSACTION

	 Declare @CustomerId bigint
	 Set @CustomerId =(Select Top(1) CustomerId from CustomerDocuments WHERE DocumentId = @DocumentId)


		DELETE FROM CustomerDocuments WHERE DocumentId = @DocumentId
	
		

			
		SELECT @QStatus = 1;
	
	COMMIT TRANSACTION	
    END TRY

    BEGIN CATCH    
         ROLLBACK TRANSACTION
		 SELECT @QStatus = -1;		 
    END CATCH
    	
END


GO
/****** Object:  StoredProcedure [dbo].[CustomerDocumentsGetById]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[CustomerDocumentsGetById] 
	(@DocumentId bigint
    ,@QStatus int OUTPUT)    
        
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.	
   SET NOCOUNT ON;
    -- Insert statements for procedure here

    BEGIN TRY     
		 SELECT (Select Email from Customers C where  C.CustomerId=CD.CustomerId) as Email, 
		 (Select Name from Customers C where  C.CustomerId=CD.CustomerId) as Name, 
		 
		  * FROM CustomerDocuments CD  WHERE DocumentId  = @DocumentId
		 SELECT @QStatus = 1;		 
    END TRY
    
    BEGIN CATCH    
		 SELECT @QStatus = -1;		 
    END CATCH	
	 
END

GO
/****** Object:  StoredProcedure [dbo].[CustomerDocumentsGetList]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[CustomerDocumentsGetList]
    @QStatus int output
        
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.	
	SET NOCOUNT ON;
    -- Insert statements for procedure here
    
    BEGIN TRY        
		SELECT * FROM CustomerDocuments	ORDER BY InsertedDate ASC
		SELECT @QStatus = 1;			
    END TRY

    BEGIN CATCH    
		 SELECT @QStatus = -1;		 
    END CATCH
    	
END


GO
/****** Object:  StoredProcedure [dbo].[CustomerDocumentsGetListByVariable]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[CustomerDocumentsGetListByVariable] 
	 (@PageNo int
	 ,@Items int
     ,@CustomerId bigint
     ,@Search nvarchar(126)
     ,@Total int output
     ,@Sort nvarchar(126))
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	
	SET NOCOUNT ON;
	
	DECLARE @Start int, @End INT ;
	DECLARE @Query nvarchar(max);
	Set @Items = 1000
    -- Insert statements for procedure here
    
    CREATE TABLE #temp(Rid INT PRIMARY KEY IDENTITY(1,1)
				,DocumentId bigint 
				,CustomerId bigint
				,DocumentName nvarchar(256)
				,DocumentUrl nvarchar(1024)
				,DocumentStatus nvarchar(256)
				,VerifiedDate datetime
				,RejectionReason nvarchar(1024)
				,InsertedDate datetime
				,InsertedBy nvarchar(256)
				,UpdatedBy nvarchar(256)
				,UpdatedDate datetime)
	
	SELECT @Start = (@PageNo - 1) * @Items  
	   
	SELECT @End = @Start + @Items 
	
	SET @Query = 'INSERT INTO #temp 
		 SELECT  S.DocumentId  
				,S.CustomerId 
				,S.DocumentName 
				,S.DocumentUrl
				,S.DocumentStatus
				,S.VerifiedDate
				,S.RejectionReason
				,S.InsertedDate
				,S.InsertedBy 
				,S.UpdatedBy 
				,S.UpdatedDate
						 
		FROM CustomerDocuments S  
		
		inner join  Customers C on S.CustomerId =C.CustomerId
		
		WHERE S.DocumentId <> 0'
		
	IF(@Search <> '')
	
    BEGIN
		SET @Query = @Query + ' AND  S.DocumentName LIKE ''%' + @Search + '%'' OR C.PhoneNo LIKE ''%' + @Search + '%'' '
    END
    
    IF(@CustomerId<>0)
		BEGIN
			SET @Query=@Query + ' AND S.CustomerId='+cast(@CustomerId as nvarchar(max))
		END
	
	IF(@Sort <> '')
	
	BEGIN
		SET @Query = @Query + ' ORDER BY ' + @Sort
	END
		 	
	EXEC sp_ExecuteSQL @Query;
	
	SELECT @Total = COUNT(Rid) from #temp 
			
	SELECT * FROM #temp WHERE Rid > @Start AND  Rid <= @End
	
	  DROP TABLE #temp

END

GO
/****** Object:  StoredProcedure [dbo].[CustomerDocumentsInsert]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[CustomerDocumentsInsert]
    (@DocumentId bigint 
	,@CustomerId bigint
	,@DocumentName nvarchar(256)
	,@DocumentUrl nvarchar(max) output
	,@DocumentStatus nvarchar(256)
	--,@VerifiedDate datetime
	,@RejectionReason nvarchar(max)
	,@AdminComments nvarchar(1024)
	,@InsertedDate datetime
	,@InsertedBy nvarchar(256)
	,@UpdatedDate datetime
	,@UpdatedBy nvarchar(256)
	,@QStatus int output)  
        
AS
BEGIN
	
	SET NOCOUNT ON;
  
   -- BEGIN TRY
   -- BEGIN TRANSACTION
    
		 IF((SELECT COUNT(*) FROM CustomerDocuments WHERE DocumentId = @DocumentId) = 0)
		BEGIN
			INSERT INTO CustomerDocuments VALUES
			        (@CustomerId
					,@DocumentName
					,null
					,@DocumentStatus
					,null
					,@RejectionReason
					,@AdminComments
					,(Convert(DateTime, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')))
					,@InsertedBy
					,(Convert(DateTime, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')))
			        ,@UpdatedBy
					
					)
					
					Set @DocumentId = SCOPE_IDENTITY()

		SELECT @QStatus = 1;	
		END
		ELSE BEGIN		
			UPDATE CustomerDocuments
			   SET 
				   CustomerId=@CustomerId
				  ,DocumentName=@DocumentName
				  ,UpdatedBy=@UpdatedBy
				  ,UpdatedDate=(Convert(DateTime, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')))
			 WHERE DocumentId = @DocumentId
			 		 
			 SELECT @QStatus = 2;
			 
		 END
		

			IF(LEFT(@DocumentUrl, 1) = '.' OR @DocumentUrl <> 'NA')
		BEGIN
		
			IF(@DocumentUrl <> 'NA')
			BEGIN

			
				UPDATE CustomerDocuments
				   SET  DocumentUrl= CAST(@DocumentId  AS nvarchar(12)) + '-' + dbo.RemoveSpecialChar(@DocumentName, '^a-z0-9') + @DocumentUrl
				   WHERE DocumentId = @DocumentId	
			  
				  SELECT @DocumentUrl = CAST(@DocumentId AS nvarchar(12)) + '-' + dbo.RemoveSpecialChar(@DocumentName, '^a-z0-9') + @DocumentUrl	
			END	
			ELSE
			BEGIN
			  SELECT @DocumentUrl=''
			END 
		END
		

	--COMMIT TRANSACTION
	--END TRY
    
 --   BEGIN CATCH     
	--   ROLLBACK TRANSACTION 
 --      SELECT @QStatus = -1;           
 --   END CATCH   
    
END

GO
/****** Object:  StoredProcedure [dbo].[CustomerDocumentsUpdateStatus]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CustomerDocumentsUpdateStatus]
    (@DocumentId bigint
    ,@QStatus int output
	,@Status nvarchar(256)
	,@UpdatedBy nvarchar(256)
	,@RejectionReason nvarchar(512))  
        
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    
    BEGIN TRY    
    BEGIN TRANSACTION
    
		IF(@Status <> '')
		BEGIN
			UPDATE CustomerDocuments SET DocumentStatus = @Status,VerifiedDate=GetDate(),UpdatedBy=@UpdatedBy,
			
			RejectionReason=@RejectionReason WHERE DocumentId = @DocumentId
		
		END			
		
		SELECT @QStatus = 1;
		
	COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
    
         ROLLBACK TRANSACTION
		 SELECT @QStatus = -1;
		 
    END CATCH
    	
END







GO
/****** Object:  StoredProcedure [dbo].[CustomerMOUDelete]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** CustomerMOUDelete ******/
CREATE PROCEDURE [dbo].[CustomerMOUDelete]
    @Id BIGINT,
    @QStatus INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        DECLARE @CustomerId BIGINT;
        SELECT TOP 1 @CustomerId = CustomerId FROM CustomerMOU WHERE Id = @Id;

        DELETE FROM CustomerMOU WHERE Id = @Id;

        SELECT @QStatus = 1;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        SELECT @QStatus = -1;
    END CATCH
END


GO
/****** Object:  StoredProcedure [dbo].[CustomerMOUGetById]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** CustomerMOUGetById ******/
CREATE PROCEDURE [dbo].[CustomerMOUGetById]
    @Id BIGINT,
    @QStatus INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        SELECT (Select Email from Customers C where  C.CustomerId=CD.CustomerId) as Email, 
		 (Select Name from Customers C where  C.CustomerId=CD.CustomerId) as Name, 
		 * FROM CustomerMOU CD WHERE Id = @Id;
        SELECT @QStatus = 1;
    END TRY
    BEGIN CATCH
        SELECT @QStatus = -1;
    END CATCH
END


GO
/****** Object:  StoredProcedure [dbo].[CustomerMOUGetList]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** CustomerMOUGetList ******/
CREATE PROCEDURE [dbo].[CustomerMOUGetList]
    @QStatus INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        SELECT * FROM CustomerMOU ORDER BY InsertedDate ASC;
        SELECT @QStatus = 1;
    END TRY
    BEGIN CATCH
        SELECT @QStatus = -1;
    END CATCH
END


GO
/****** Object:  StoredProcedure [dbo].[CustomerMOUGetListByVariable]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** CustomerMOUGetListByVariable ******/
CREATE PROCEDURE [dbo].[CustomerMOUGetListByVariable]
    @PageNo INT,
    @Items INT,
    @CustomerId BIGINT,
    @Search NVARCHAR(126),
    @Total INT OUTPUT,
    @Sort NVARCHAR(126)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Start INT, @End INT;
    DECLARE @Query NVARCHAR(MAX);

    CREATE TABLE #temp(
        Rid INT PRIMARY KEY IDENTITY(1,1),
        Id BIGINT,
        CustomerId BIGINT,
        MOUNumber NVARCHAR(50),
        DocumentUrl NVARCHAR(512),
        MOUStatus NVARCHAR(50),
        SubmittedDate DATETIME,
        ReviewedDate DATETIME,
        ApprovedDate DATETIME,
        RejectionReason NVARCHAR(1024),
        AdminComments NVARCHAR(1024),
        CustomerComments NVARCHAR(1024),
        IsActive BIT,
        InsertedDate DATETIME,
        InsertedBy NVARCHAR(256),
        UpdatedDate DATETIME,
        UpdatedBy NVARCHAR(256)
    );

    SELECT @Start = (@PageNo - 1) * @Items;
    SELECT @End = @Start + @Items;

    SET @Query = N'
        INSERT INTO #temp
        SELECT
            C.Id,
            C.CustomerId,
            C.MOUNumber,
            C.DocumentUrl,
            C.MOUStatus,
            C.SubmittedDate,
            C.ReviewedDate,
            C.ApprovedDate,
            C.RejectionReason,
            C.AdminComments,
            C.CustomerComments,
            C.IsActive,
            C.InsertedDate,
            C.InsertedBy,
            C.UpdatedDate,
            C.UpdatedBy
        FROM CustomerMOU C

		inner join  Customers S on S.CustomerId =C.CustomerId

        WHERE C.Id <> 0
    ';

    SET @Query = @Query + N' AND (
		C.MOUNumber LIKE ''%' + REPLACE(@Search, '''', '''''') + '%'' 
		OR C.MOUStatus LIKE ''%' + REPLACE(@Search, '''', '''''') + '%''
		OR S.PhoneNo LIKE ''%' + REPLACE(@Search, '''', '''''') + '%''
	)';


    IF(@CustomerId <> 0)
        SET @Query = @Query + ' AND C.CustomerId = ' + CAST(@CustomerId AS NVARCHAR(20));

    IF(@Sort <> '')
        SET @Query = @Query + ' ORDER BY ' + @Sort;

    EXEC sp_executesql @Query;

    SELECT @Total = COUNT(Rid) FROM #temp;

    SELECT * FROM #temp WHERE Rid > @Start AND Rid <= @End;

    DROP TABLE #temp;
END


GO
/****** Object:  StoredProcedure [dbo].[CustomerMOUInsert]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CustomerMOUInsert]
    @Id BIGINT,
    @CustomerId BIGINT,
  --  @MOUNumber NVARCHAR(50),
    @DocumentUrl NVARCHAR(512) OUTPUT,
 --   @MOUStatus NVARCHAR(50),
  --  @SubmittedDate DATETIME,
  --  @ReviewedDate DATETIME,
  --  @ApprovedDate DATETIME,
  --  @RejectionReason NVARCHAR(1024),
  --  @AdminComments NVARCHAR(1024),
  --  @CustomerComments NVARCHAR(1024),
  --  @IsActive BIT,
    @InsertedBy NVARCHAR(256),
   -- @InsertedDate DATETIME,
    @UpdatedBy NVARCHAR(256),
   -- @UpdatedDate DATETIME,
    @QStatus INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;


		Declare  @MOUNumber NVARCHAR(50);

      -- Auto-generate MOUNumber if not provided
		IF (@MOUNumber IS NULL OR LTRIM(RTRIM(@MOUNumber)) = '')
		BEGIN
			DECLARE @MaxNum INT

			SELECT @MaxNum = 
				MAX(CAST(RIGHT(MOUNumber, 4) AS INT))
			FROM CustomerMOU
			WHERE MOUNumber LIKE 'CUS[0-9][0-9][0-9][0-9]'

			IF @MaxNum IS NULL
				SET @MaxNum = 0

			SET @MOUNumber = 'CUS' + RIGHT('000' + CAST(@MaxNum + 1 AS NVARCHAR(4)), 4)
		END


        IF((SELECT COUNT(*) FROM CustomerMOU WHERE Id = @Id) = 0)
        BEGIN
            INSERT INTO CustomerMOU
            (
                CustomerId, MOUNumber, DocumentUrl, MOUStatus,
                SubmittedDate, ReviewedDate, ApprovedDate, RejectionReason,
                AdminComments, CustomerComments, IsActive,
                InsertedDate, InsertedBy, UpdatedDate, UpdatedBy
            )
            VALUES
            (
                @CustomerId, @MOUNumber, NULL, NULL,
                CONVERT(DATETIME, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')), NULL, NULL, NULL,
                NULL, NULL, 1,
                CONVERT(DATETIME, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')),
                @InsertedBy,
                CONVERT(DATETIME, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')),
                @UpdatedBy
            );

            SET @Id = SCOPE_IDENTITY();
            SELECT @QStatus = 1;
        END
        ELSE
        BEGIN
            UPDATE CustomerMOU
            SET
                CustomerId = @CustomerId,
               -- MOUNumber = @MOUNumber,
               -- MOUStatus = @MOUStatus,
               --- SubmittedDate = @SubmittedDate,
               -- ReviewedDate = @ReviewedDate,
               -- ApprovedDate = @ApprovedDate,
               -- RejectionReason = @RejectionReason,
             --   AdminComments = @AdminComments,
             --   CustomerComments = @CustomerComments,
               -- IsActive = @IsActive,
                UpdatedDate = CONVERT(DATETIME, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')),
                UpdatedBy = @UpdatedBy
            WHERE Id = @Id;

            SELECT @QStatus = 2;
        END

        -- Document URL naming logic
        IF(@DocumentUrl IS NOT NULL AND @DocumentUrl <> 'NA')
        BEGIN
            UPDATE CustomerMOU
            SET DocumentUrl = CAST(@Id AS NVARCHAR(12)) + '-' + dbo.RemoveSpecialChar(@MOUNumber, '^a-z0-9') + @DocumentUrl
            WHERE Id = @Id;

            SELECT @DocumentUrl = CAST(@Id AS NVARCHAR(12)) + '-' + dbo.RemoveSpecialChar(@MOUNumber, '^a-z0-9') + @DocumentUrl;
        END
        ELSE
        BEGIN
            SELECT @DocumentUrl = '';
        END

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        SELECT @QStatus = -1;
    END CATCH
END


GO
/****** Object:  StoredProcedure [dbo].[CustomerMOUUpdateStatus]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** CustomerMOUUpdateStatus ******/
CREATE PROCEDURE [dbo].[CustomerMOUUpdateStatus]
    (@Id bigint
    ,@QStatus int output
	,@Status nvarchar(256)
	,@UpdatedBy nvarchar(256)
	,@RejectionReason nvarchar(512))  
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

    
		IF(@Status <> '')
		BEGIN
			UPDATE CustomerMOU SET MOUStatus = @Status,ReviewedDate=GetDate(),UpdatedBy=@UpdatedBy,
			RejectionReason=@RejectionReason WHERE Id = @Id
		
		END	

        SELECT @QStatus = 1;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        SELECT @QStatus = -1;
    END CATCH
END




GO
/****** Object:  StoredProcedure [dbo].[CustomersDelete]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CustomersDelete]
    @CustomerId BIGINT,
    @QStatus INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;
            DELETE FROM Customers WHERE CustomerId = @CustomerId;
            SELECT @QStatus = 1;
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        SELECT @QStatus = -1;
    END CATCH
END

GO
/****** Object:  StoredProcedure [dbo].[CustomersExportExcel]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


--'06/19/2024','06/20/2024','TEST','UpdatedDate DESC',0
CREATE PROCEDURE [dbo].[CustomersExportExcel]
	 (@Search nvarchar(126)
     ,@Sort nvarchar(126)
     ,@Total int output
	 
	 )

	
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.	
	SET NOCOUNT ON;
	
	--BEGIN TRY
	 
		
	DECLARE @Start int, @End INT ;
	DECLARE @Query nvarchar(max);
	--DECLARE @Query nvarchar(max);


	
    -- Insert statements for procedure here
     
	SET @Query = 'SELECT 
	C.CustomerId,
C.Name,
C.PhoneNo,
C.WhatsappNo,
C.Email,
C.Gender,
C.DateOfBirth,
C.CompanyName,
C.BusinessType,
C.Address1,
C.Address2,
C.City,
C.State,
C.Country,
C.ZipCode,
C.PassPortNo,
C.CitizenShip,
C.SSNNo,
C.DrivingNo,
C.ProfileImage,
C.CustomerStatus,
C.Comments,
C.InsertedDate,
C.InsertedBy

	
	 FROM Customers C with(nolock)
				  WHERE C.CustomerId <> 0 '


    IF(@Search <> '')
    BEGIN
        SET @Query = @Query + ' AND (C.Name LIKE ''%' + @Search + '%'' OR C.PhoneNo LIKE ''%' + @Search + '%'' OR C.Email LIKE ''%' + @Search + '%'' OR C.CustomerNo LIKE ''%' + @Search + '%'' OR C.SSNNo LIKE ''%' + @Search + '%'' OR C.DrivingNo LIKE ''%' + @Search + '%'')'
    END

	

	
		--Set @Query = @Query + @Query;

		IF(@Sort <> '')
		BEGIN
			SET @Query = @Query + ' ORDER BY ' + @Sort 
		END 

		EXEC sp_ExecuteSQL @Query;
	    print @Query;

	    SELECT @Total = 1;
		
		
		
		 
	--END TRY
    
 --   BEGIN CATCH  
 --      SELECT @Total = -1; 		   
 --   END CATCH   
END





























GO
/****** Object:  StoredProcedure [dbo].[CustomersGetById]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CustomersGetById]
    @CustomerId BIGINT,
    @QStatus INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        SELECT * FROM Customers WHERE CustomerId = @CustomerId;
        SELECT @QStatus = 1;
    END TRY
    BEGIN CATCH
        SELECT @QStatus = -1;
    END CATCH
END

GO
/****** Object:  StoredProcedure [dbo].[CustomersGetList]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CustomersGetList]
    @QStatus INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        SELECT * FROM Customers WHERE IsActive = 1 ORDER BY Name ASC;
        SELECT @QStatus = 1;
    END TRY
    BEGIN CATCH
        SELECT @QStatus = -1;
    END CATCH
END

GO
/****** Object:  StoredProcedure [dbo].[CustomersGetListByVariable]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CustomersGetListByVariable]
    @PageNo INT,
    @Items INT,
    @Search NVARCHAR(126),
    @Total INT OUTPUT,
    @Sort NVARCHAR(126)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Start INT, @End INT;
    DECLARE @Query NVARCHAR(MAX);

    CREATE TABLE #temp (
        Rid INT PRIMARY KEY IDENTITY(1,1),
        CustomerId BIGINT,
		CustomerNo NVARCHAR(128),
        Name NVARCHAR(128),
        PhoneNo NVARCHAR(64),
        WhatsappNo NVARCHAR(64),
        Email NVARCHAR(128),
        Password NVARCHAR(256),
        Gender NVARCHAR(20),
        DateOfBirth DATETIME,
        CompanyName NVARCHAR(256),
        BusinessType NVARCHAR(128),
        Address1 NVARCHAR(512),
        City NVARCHAR(128),
        State NVARCHAR(128),
        Country NVARCHAR(128),
        ZipCode NVARCHAR(64),
        PassPortNo NVARCHAR(20),
        ProfileImage NVARCHAR(256),
        CustomerStatus NVARCHAR(50),
        IsActive BIT,
        FailedPasswordAttemptCount INT,
        IsLockedOut BIT,
        LastPasswordChangedDate DATETIME,
        LastLoginDate DATETIME,
        RegistrationDate DATETIME,
        AcceptedDate DATETIME,
        IPAddress NVARCHAR(45),
        DeviceInfo NVARCHAR(256),
        TermsConditions BIT,
        Comments NVARCHAR(2048),
        InsertedDate DATETIME,
        InsertedBy NVARCHAR(256),
        UpdatedDate DATETIME,
        UpdatedBy NVARCHAR(256)
    )

    SELECT @Start = (@PageNo - 1) * @Items
    SELECT @End = @Start + @Items

    SET @Query = '
        INSERT INTO #temp
        SELECT
            C.CustomerId,C.CustomerNo, C.Name, C.PhoneNo, C.WhatsappNo, C.Email, C.Password, C.Gender, C.DateOfBirth,
            C.CompanyName, C.BusinessType, C.Address1, C.City, C.State, C.Country, C.ZipCode, C.PassPortNo,
            C.ProfileImage, C.CustomerStatus, C.IsActive, C.FailedPasswordAttemptCount, C.IsLockedOut,
            C.LastPasswordChangedDate, C.LastLoginDate, C.RegistrationDate, C.AcceptedDate, C.IPAddress,
            C.DeviceInfo, C.TermsConditions, C.Comments, C.InsertedDate, C.InsertedBy, C.UpdatedDate, C.UpdatedBy
        FROM Customers C
        WHERE C.CustomerId <> 0
    '

    IF(@Search <> '')
    BEGIN
        SET @Query = @Query + ' AND (C.Name LIKE ''%' + @Search + '%'' OR C.PhoneNo LIKE ''%' + @Search + '%'' OR C.Email LIKE ''%' + @Search + '%'' OR C.CustomerNo LIKE ''%' + @Search + '%'' OR C.SSNNo LIKE ''%' + @Search + '%'' OR C.DrivingNo LIKE ''%' + @Search + '%'')'
    END

    IF(@Sort <> '')
    BEGIN
        SET @Query = @Query + ' ORDER BY ' + @Sort
    END

    EXEC sp_executesql @Query

    SELECT @Total = COUNT(Rid) FROM #temp

    SELECT * FROM #temp WHERE Rid > @Start AND Rid <= @End

    DROP TABLE #temp
END


GO
/****** Object:  StoredProcedure [dbo].[CustomersGetPassword]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[CustomersGetPassword]

@CustomerId int,
@QStatus int output

AS
BEGIN

		-- SET NOCOUNT ON added to prevent extra result sets from
		-- interfering with SELECT statements.

		SET NOCOUNT ON;

		-- Insert statements for procedure here

		BEGIN TRY
			SELECT Password FROM Customers WHERE CustomerId = @CustomerId
			SELECT @QStatus = 1
		END TRY
		
		BEGIN CATCH
		    SELECT @QStatus = -1
		END CATCH

END

GO
/****** Object:  StoredProcedure [dbo].[CustomersGetSSNNO]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[CustomersGetSSNNO]
    @SSNNO nvarchar(64),
    @QStatus INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        SELECT * FROM Customers WHERE SSNNO = @SSNNO;
        SELECT @QStatus = 1;
    END TRY
    BEGIN CATCH
        SELECT @QStatus = -1;
    END CATCH
END

GO
/****** Object:  StoredProcedure [dbo].[CustomersInsert]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CustomersInsert]
    @CustomerId BIGINT,
    @CustomerNo NVARCHAR(20),
    @Name NVARCHAR(128),
    @PhoneNo NVARCHAR(64),
    @WhatsAppNo NVARCHAR(64), -- Corrected typo
    @Email NVARCHAR(128),
    @Password NVARCHAR(256),
    @Gender NVARCHAR(20),
    @DateOfBirth DATETIME,
    @CompanyName NVARCHAR(256),
    @BusinessType NVARCHAR(128),
    @Address1 NVARCHAR(2048),
    @Address2 NVARCHAR(2048),
    @City NVARCHAR(128),
    @State NVARCHAR(128),
    @Country NVARCHAR(128),
    @ZipCode NVARCHAR(64),
    @CitizenShip NVARCHAR(64), -- Added missing column
    @PassPortNo NVARCHAR(30),
    @SSNNo NVARCHAR(50),
    @DrivingNo NVARCHAR(50),
    @ProfileImage NVARCHAR(256) OUTPUT, -- Changed to match table schema
    @CustomerStatus NVARCHAR(50),
    @IsActive BIT,
    @FailedPasswordAttemptCount INT,
    @IsLockedOut BIT,
    @LastPasswordChangedDate DATETIME,
    @LastLoginDate DATETIME,
    @RegistrationDate DATETIME,
    @AcceptedDate DATETIME,
    @IPAddress NVARCHAR(45),
    @DeviceInfo NVARCHAR(256),
    @TermsConditions BIT,
    @Comments NVARCHAR(2048),
    @InsertedBy NVARCHAR(256),
    @UpdatedBy NVARCHAR(256),
    @QStatus INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION
        -- Auto-generate CustomerNo if not provided
        IF (@CustomerNo IS NULL OR LTRIM(RTRIM(@CustomerNo)) = '')
        BEGIN
            DECLARE @MaxNum INT
            SELECT @MaxNum = MAX(CAST(RIGHT(CustomerNo, 4) AS INT))
            FROM Customers
            WHERE CustomerNo LIKE 'CUS[0-9][0-9][0-9][0-9]'
            IF @MaxNum IS NULL
                SET @MaxNum = 0
            SET @CustomerNo = 'CUS' + RIGHT('000' + CAST(@MaxNum + 1 AS NVARCHAR(4)), 4)
        END

		--and IPAddress=@IPAddress
        IF ((SELECT COUNT(*) FROM Customers WHERE SSNNo = @SSNNo ) = 0)
        BEGIN
            INSERT INTO Customers
            (
                CustomerNo, Name, PhoneNo, WhatsAppNo, Email, Password, Gender, DateOfBirth, CompanyName, BusinessType,
                Address1, Address2, City, State, Country, ZipCode, CitizenShip, PassPortNo, SSNNo, DrivingNo, ProfileImage,
                CustomerStatus, IsActive, FailedPasswordAttemptCount, IsLockedOut, LastPasswordChangedDate,
                LastLoginDate, RegistrationDate, AcceptedDate, IPAddress, DeviceInfo, TermsConditions, Comments,
                InsertedDate, InsertedBy, UpdatedDate, UpdatedBy
            )
            VALUES
            (
                @CustomerNo, @Name, @PhoneNo, @WhatsAppNo, @Email, @Password, @Gender, @DateOfBirth, @CompanyName, @BusinessType,
                @Address1, @Address2, @City, @State, @Country, @ZipCode, @CitizenShip, @PassPortNo, @SSNNo, @DrivingNo, @ProfileImage,
                @CustomerStatus, @IsActive, @FailedPasswordAttemptCount, @IsLockedOut, @LastPasswordChangedDate,
                @LastLoginDate, @RegistrationDate, @AcceptedDate, @IPAddress, @DeviceInfo, @TermsConditions, @Comments,
                CONVERT(DATETIME, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')),
                @InsertedBy,
                CONVERT(DATETIME, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')),
                @UpdatedBy
            )
            SET @CustomerId = SCOPE_IDENTITY()
            SELECT @QStatus = 1
        END
        ELSE
        BEGIN
            UPDATE Customers
            SET CustomerNo = @CustomerNo,
                Name = @Name,
                PhoneNo = @PhoneNo,
                WhatsAppNo = @WhatsAppNo, -- Corrected typo
                Email = @Email,
                Password = @Password,
                Gender = @Gender,
                DateOfBirth = @DateOfBirth,
                CompanyName = @CompanyName,
                BusinessType = @BusinessType,
                Address1 = @Address1,
                Address2 = @Address2,
                City = @City,
                State = @State,
                Country = @Country,
                ZipCode = @ZipCode,
                CitizenShip = @CitizenShip, -- Added missing column
                PassPortNo = @PassPortNo,
            --    SSNNo = @SSNNo,
                DrivingNo = @DrivingNo,
                ProfileImage = @ProfileImage,
              --  CustomerStatus = @CustomerStatus, -- Included
                IsActive = @IsActive, -- Included
                FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
                IsLockedOut = @IsLockedOut,
                LastPasswordChangedDate = @LastPasswordChangedDate,
                LastLoginDate = @LastLoginDate,
                RegistrationDate = @RegistrationDate,
                AcceptedDate = @AcceptedDate,
                IPAddress = @IPAddress,
                DeviceInfo = @DeviceInfo,
                TermsConditions = @TermsConditions,
                Comments = @Comments,
                UpdatedDate = CONVERT(DATETIME, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')),
                UpdatedBy = @UpdatedBy

            WHERE CustomerId = @CustomerId
            SELECT @QStatus = 2
        END

        IF (LEFT(@ProfileImage, 1) = '.' OR @ProfileImage <> 'NA')
        BEGIN
            IF (@ProfileImage <> 'NA')
            BEGIN
                UPDATE Customers
                SET ProfileImage = CAST(@CustomerId AS NVARCHAR(12)) + '-' + dbo.RemoveSpecialChar(@Name, '^a-z0-9') + @ProfileImage
                WHERE CustomerId = @CustomerId
                SELECT @ProfileImage = CAST(@CustomerId AS NVARCHAR(12)) + '-' + dbo.RemoveSpecialChar(@Name, '^a-z0-9') + @ProfileImage
            END
            ELSE
            BEGIN
                SELECT @ProfileImage = ''
            END
        END

        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        SELECT @QStatus = -1
    END CATCH
END

GO
/****** Object:  StoredProcedure [dbo].[CustomerStatusDelete]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CustomerStatusDelete]
    (@Id bigint
    ,@QStatus int output)  
        
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    
    --BEGIN TRY    
    BEGIN TRANSACTION
    
			DELETE FROM CustomerStatus WHERE Id = @Id
		
		SELECT @QStatus = 1;
	
	COMMIT TRANSACTION	
   -- END TRY
   -- BEGIN CATCH
    
   --      ROLLBACK TRANSACTION
		 --SELECT @QStatus = -1;
		 
   -- END CATCH
    	
END


GO
/****** Object:  StoredProcedure [dbo].[CustomerStatusGetById]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[CustomerStatusGetById]
	(@Id bigint
    ,@QStatus int OUTPUT)    
        
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.	
   SET NOCOUNT ON;

    -- Insert statements for procedure here

    BEGIN TRY     
		 SELECT * FROM CustomerStatus  WHERE Id  = @Id
		 SELECT @QStatus = 1;		 
    END TRY
    
    BEGIN CATCH    
		 SELECT @QStatus = -1;		 
    END CATCH	
	 
END

GO
/****** Object:  StoredProcedure [dbo].[CustomerStatusGetList]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CustomerStatusGetList]
    @QStatus int output
        
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    
    BEGIN TRY 
       
		SELECT * FROM CustomerStatus where IsActive=1 Order by OrderNo Asc	
		SELECT @QStatus = 1;
			
    END TRY
    BEGIN CATCH
    
		 SELECT @QStatus = -1;
		 
    END CATCH
    	
END

GO
/****** Object:  StoredProcedure [dbo].[CustomerStatusGetListByVariable]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CustomerStatusGetListByVariable]
	 (@PageNo int
     ,@Items int
     ,@Search nvarchar(126)
     ,@Total int output
     ,@Sort nvarchar(126))
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.	
	SET NOCOUNT ON;
	
	DECLARE @Start int, @End INT ;
	DECLARE @Query nvarchar(max);
	
    -- Insert statements for procedure here
    
    CREATE TABLE #temp(Rid INT PRIMARY KEY IDENTITY(1,1)
				,Id bigint 
				,Name varchar(50)
				,OrderNo bigint
				,IsActive bit
				,UpdatedBy nvarchar(256)
				,UpdatedDate datetime
				)
	
	SELECT @Start = (@PageNo - 1) * @Items  
	   
	SELECT @End = @Start + @Items 
	
	SET @Query = 'INSERT INTO #temp 
		 SELECT  SL.Id 
				,SL.Name 
				,SL.OrderNo
				,SL.IsActive
				,SL.UpdatedBy 
				,SL.UpdatedDate 
		FROM CustomerStatus SL  WHERE SL.Id <> 0 '
	
	
	IF(@Search <> '')
	
    BEGIN
		SET @Query = @Query + ' AND  SL.Name LIKE ''%' + @Search + '%'''
    END
	
	IF(@Sort <> '')
	
	BEGIN
		SET @Query = @Query + ' ORDER BY ' + @Sort
	END
	
	 	
	EXEC sp_ExecuteSQL @Query;
	
	SELECT @Total = COUNT(Rid) from #temp 
			
	SELECT * FROM #temp WHERE Rid > @Start AND  Rid <= @End
	
	  DROP TABLE #temp

END

GO
/****** Object:  StoredProcedure [dbo].[CustomerStatusInsert]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CustomerStatusInsert]
    (@Id bigint 
	,@Name Nvarchar(256)	
	,@IsActive bit
	,@UpdatedBy nvarchar(50)
	,@UpdatedDate datetime
	,@QStatus int output)  
        
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.	
	SET NOCOUNT ON;
    -- Insert statements for procedure here
    
   -- BEGIN TRY
   -- BEGIN TRANSACTION
    
		 IF((SELECT COUNT(*) FROM CustomerStatus WHERE Id = @Id) = 0)
		BEGIN
			INSERT INTO CustomerStatus VALUES
			        (@Name	
					,0 
	                ,@IsActive
	                ,@UpdatedDate
					,@UpdatedBy
					)
					
					Set @Id = SCOPE_IDENTITY()

		SELECT @QStatus = 1;	
		END
		ELSE BEGIN
		
			UPDATE CustomerStatus
			   SET 
				   Name=@Name
				  ,UpdatedBy=@UpdatedBy
				  ,UpdatedDate=@UpdatedDate
			 WHERE  Id = @Id
			 		 
			 SELECT @QStatus = 2;
			 
		 END
		 
		
	--COMMIT TRANSACTION
	--END TRY
    
 --   BEGIN CATCH     
	--   ROLLBACK TRANSACTION 
 --      SELECT @QStatus = -1;           
 --   END CATCH   
    
END

GO
/****** Object:  StoredProcedure [dbo].[CustomerStatusUpdateOrderBy]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CustomerStatusUpdateOrderBy]
    (@Id bigint
    ,@OrderNo int
    ,@QStatus int output)  
        
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    
    BEGIN TRY    
    BEGIN TRANSACTION
    
			UPDATE CustomerStatus SET OrderNo = @OrderNo WHERE Id=@Id
		
		 SELECT @QStatus = 1;	
	COMMIT TRANSACTION
    END TRY

    BEGIN CATCH    
         ROLLBACK TRANSACTION
		 SELECT @QStatus = -1;		 
    END CATCH
    	
END























GO
/****** Object:  StoredProcedure [dbo].[CustomerStatusUpdateStatus]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[CustomerStatusUpdateStatus]
    (@Id bigint
    ,@QStatus int output)  
        
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.	
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    
    BEGIN TRY    
    BEGIN TRANSACTION
    
		IF((SELECT IsActive FROM CustomerStatus WHERE Id = @Id) = 1)
		BEGIN
			UPDATE CustomerStatus SET IsActive = 0 WHERE Id = @Id
		END
		ELSE
		BEGIN
			UPDATE CustomerStatus SET IsActive = 1 WHERE Id = @Id
		END		
		
		SELECT @QStatus = 1;
		
	COMMIT TRANSACTION
    END TRY

    BEGIN CATCH    
         ROLLBACK TRANSACTION
		 SELECT @QStatus = -1;		 
    END CATCH
    	
END


GO
/****** Object:  StoredProcedure [dbo].[CustomersUpdateStatus]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CustomersUpdateStatus]
    (@CustomerId bigint
    ,@QStatus int output
	,@Status nvarchar(256)
	,@UpdatedBy nvarchar(256))  
        
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    
    BEGIN TRY    
    BEGIN TRANSACTION
    
		IF(@Status <> '')
		BEGIN
			UPDATE Customers SET CustomerStatus = @Status,UpdatedDate=GetDate(),UpdatedBy=@UpdatedBy WHERE CustomerId = @CustomerId
		
		END			
		
		SELECT @QStatus = 1;
		
	COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
    
         ROLLBACK TRANSACTION
		 SELECT @QStatus = -1;
		 
    END CATCH
    	
END







GO
/****** Object:  StoredProcedure [dbo].[EnquiriesDelete]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[EnquiriesDelete]
    (@EnquiryId bigint
    ,@QStatus int output )  
        
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	
	SET NOCOUNT ON;
    
    BEGIN TRY  
		    BEGIN TRANSACTION  
			DELETE FROM Enquiries WHERE EnquiryId = @EnquiryId		
			SELECT @QStatus = 1;
			COMMIT TRANSACTION		
    END TRY

    BEGIN CATCH
    ROLLBACK TRANSACTION
		 SELECT @QStatus = -1;
    END CATCH
    	
END















GO
/****** Object:  StoredProcedure [dbo].[EnquiriesGetById]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[EnquiriesGetById]
	(@EnquiryId bigint
    ,@QStatus int OUTPUT)    
        
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	
   SET NOCOUNT ON;

    BEGIN TRY 
		 SELECT  EnquiryId
		        ,Name
				,Email
				,Description
				,Subject 
				,PhoneNo 
				,IsActive
				,InsertedTime 
				,Field1
				,Field2
		        from Enquiries where EnquiryId=@EnquiryId
		 
		 SELECT @QStatus = 1;
    END TRY
    
    BEGIN CATCH
		 SELECT @QStatus = -1;
    END CATCH	
	 
END












GO
/****** Object:  StoredProcedure [dbo].[EnquiriesGetListByVariable]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[EnquiriesGetListByVariable] 
	 (@EventId bigint
	 ,@FromDate nvarchar(126)
	 ,@ToDate nvarchar(126)
	 ,@Search nvarchar(126)
     ,@Sort nvarchar(126)
	 ,@PageNo int
     ,@Items int
     ,@Total int output)
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	
	SET NOCOUNT ON;
	
	DECLARE @Start int, @End INT ;
	DECLARE @Query nvarchar(max);
    
    CREATE TABLE #temp(Rid INT PRIMARY KEY IDENTITY(1,1)
				,EnquiryId bigint
				,Name nvarchar(128)
				,Email nvarchar(128)
				,Description nvarchar(1024)
				,Subject nvarchar(128)
				,PhoneNo nvarchar(512)
				,IsActive bit
				,InsertedTime datetime
				,Field1 nvarchar(512)
				,Field2 nvarchar(512)
				,ChapterName nvarchar(512))
				
	SELECT @Start = (@PageNo - 1) * @Items  
	   
	SELECT @End = @Start + @Items 
	
	SET @Query = 'INSERT INTO #temp SELECT 
	             E.EnquiryId
	            ,E.Name
	            ,E.Email
	            ,E.Description
	            ,E.Subject
	            ,E.PhoneNo
	            ,E.IsActive
	            ,E.InsertedTime 
				,E.Field1
				,E.Field2
				,'''' as ChapterName
				FROM Enquiries E
		        WHERE E.EnquiryId <> 0 '

	
		IF(@Search <> '')
			BEGIN
				SET @Query = @Query + ' AND  E.Name LIKE ''%' + @Search + '%'''
			END

		IF(@EventId <> 0)
			BEGIN
				SET @Query = @Query + ' AND E.EventId= '+CAST(@EventId as nvarchar(250))
			END



		IF(@FromDate <> '' OR @ToDate <> '')
		BEGIN
			IF(@FromDate <> '' AND @ToDate <> '')
				BEGIN
					SET @Query = @Query + ' AND  (Convert(Date, E.InsertedTime) >= ''' + @FromDate + ''' AND Convert(Date, E.InsertedTime) <= ''' + @ToDate + ''')'
				END 

			IF(@FromDate <> '' AND @ToDate = '')
				BEGIN
					SET @Query = @Query + ' AND  Convert(Date,E.InsertedTime) >= ''' + @FromDate + ''''
				END 
			IF(@FromDate = '' AND @ToDate <> '')
				BEGIN
					SET @Query = @Query + ' AND Convert(Date, E.InsertedTime) <= ''' + @ToDate + ''''
				END
	    END 

		IF(@Sort <> '')
			BEGIN
				SET @Query = @Query + ' ORDER BY ' + @Sort
			END
	
		EXEC sp_ExecuteSQL @Query;
		
		SELECT @Total = COUNT(Rid) from #temp 
				
		SELECT * FROM #temp WHERE Rid > @Start AND  Rid <= @End
	
		DROP TABLE #temp
 
END













GO
/****** Object:  StoredProcedure [dbo].[EnquiriesInsert]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[EnquiriesInsert]
    (@EnquiryId bigint output
	,@EventId bigint
	,@Name nvarchar(128)
	,@Email nvarchar(128)
	,@Description nvarchar(1024)
	,@Subject nvarchar(128)
	,@PhoneNo nvarchar(50)
	,@IsActive bit
	,@InsertedTime datetime
	,@Field1 nvarchar(512)
	,@Field2 nvarchar(512)
	,@QStatus int output)  
        
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	
	SET NOCOUNT ON;
    
    BEGIN TRY
    BEGIN TRANSACTION
    IF((SELECT COUNT(*) FROM Enquiries WHERE EnquiryId = @EnquiryId) = 0)
    BEGIN
        INSERT INTO Enquiries VALUES
            (@EventId
			,@Name
            ,@Email
            ,@Description
            ,@Subject
            ,@PhoneNo
			,@IsActive
			,@InsertedTime
			,'New'
			,@Field2)		

			SELECT @EnquiryId = SCOPE_IDENTITY()
			
		SELECT @QStatus = 1;	
	END
	ELSE
	BEGIN
		UPDATE Enquiries
		   SET EventId = @EventId
		       ,Name =@Name,
			   Email=@Email,
			   Description=@Description,
			   Subject=@Subject,
			   PhoneNo=@PhoneNo,
			   IsActive=@IsActive ,
			   InsertedTime=@InsertedTime
			   --Field1 = @Field1,
			   --Field2 = @Field2

		 WHERE EnquiryId = @EnquiryId
		 		 
		 SELECT @QStatus = 1;	
	END
    COMMIT TRANSACTION
    END TRY
    
    BEGIN CATCH    
    ROLLBACK TRANSACTION
		SELECT @QStatus = -1;    
    END CATCH    
END



















GO
/****** Object:  StoredProcedure [dbo].[EnquiriesUpdateStatus]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[EnquiriesUpdateStatus]
    (@EnquiryId bigint
    ,@QStatus int output)  
        
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	
	SET NOCOUNT ON;
    
    BEGIN TRY    
		BEGIN TRANSACTION

			IF((SELECT IsActive FROM Enquiries WHERE EnquiryId = @EnquiryId) = 1)
				BEGIN
					UPDATE Enquiries SET IsActive = 0 WHERE EnquiryId = @EnquiryId
				END
			ELSE
				BEGIN
					UPDATE Enquiries SET IsActive = 1 WHERE EnquiryId = @EnquiryId
				END		
		
			SELECT @QStatus = 1;
		
		COMMIT TRANSACTION
    END TRY

    BEGIN CATCH
    
         ROLLBACK TRANSACTION
		 SELECT @QStatus = -1;
		 
    END CATCH
    	
END











GO
/****** Object:  StoredProcedure [dbo].[EnquirysUpdateStatus]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[EnquirysUpdateStatus]
    @EnquiryId BIGINT,
    @Status NVARCHAR(50),
    @QStatus INT OUTPUT  
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY    
        BEGIN TRANSACTION;

        UPDATE Enquiries
        SET Field1 = @Status
        WHERE EnquiryId = @EnquiryId;

        SET @QStatus = 1;
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;     
        SET @QStatus = -1;
    END CATCH
END
















GO
/****** Object:  StoredProcedure [dbo].[ExistingMenuItemsGetbyId]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


Create PROCEDURE [dbo].[ExistingMenuItemsGetbyId]
	@PageDetailId bigint,
	@QStatus int OUTPUT
        
AS
BEGIN

	BEGIN TRY
		DECLARE @Query nvarchar(max);
		DECLARE @Start int, @End INT ;
	    
		SET NOCOUNT ON;
		    
		select * from MenuItems M where MenuItemid not in (select MenuItemid from MenuPages where MenuItemid=M.MenuItemid)

		union all
		select * from MenuItems M where  MenuItemid in (select MenuItemid from MenuPages where PageDetailId=@PageDetailId)
		

	          SELECT @QStatus = 1;		
	END TRY

    BEGIN CATCH
		 SELECT @QStatus = -1;
    END CATCH  
    
END




GO
/****** Object:  StoredProcedure [dbo].[ExistingMenuItemsGetList]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[ExistingMenuItemsGetList]
	--@ChapterId bigint,
	@QStatus int OUTPUT
        
AS
BEGIN

	BEGIN TRY
		DECLARE @Query nvarchar(max);
		DECLARE @Start int, @End INT ;
	    
		SET NOCOUNT ON;
		    
		select * from MenuItems M where MenuItemid not in (select MenuItemid from MenuPages where MenuItemid=M.MenuItemid)



	          SELECT @QStatus = 1;		
	END TRY

    BEGIN CATCH
		 SELECT @QStatus = -1;
    END CATCH  
    
END

GO
/****** Object:  StoredProcedure [dbo].[ExportToEnquiries]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


Create PROCEDURE [dbo].[ExportToEnquiries]
	 (@EventId bigint
	 ,@FromDate nvarchar(126)
	 ,@ToDate nvarchar(126)
	 ,@Search nvarchar(126)
	 ,@Sort nvarchar(216)
     ,@Total int OUTPUT)
        
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	
	BEGIN TRY 
	
			SET NOCOUNT ON;
			
			DECLARE @Start int, @End INT ;
			DECLARE @Query nvarchar(max);
			
			SET @Query = 'SELECT  E.EnquiryId
						,E.Name	
						,E.Email	
						,E.PhoneNo		
						,E.Subject		
						,E.Description												  
				         FROM Enquiries E where E.EnquiryId <> 0 '
			 
			
			IF(@Search <> '')
			BEGIN
				SET @Query = @Query + ' AND  E.Name LIKE ''%' + @Search + '%'' OR  E.Email LIKE ''%' + @Search + '%'' OR  E.PhoneNo LIKE ''%' + @Search + '%'''
			END

			IF(@EventId <> 0)
			BEGIN
				SET @Query = @Query + ' AND E.EventId= '+CAST(@EventId as nvarchar(250))
			END



			IF(@FromDate <> '' OR @ToDate <> '')
	  		BEGIN
				IF(@FromDate <> '' AND @ToDate <> '')
				BEGIN
					SET @Query = @Query + ' AND  (Convert(Date, E.InsertedTime) >= ''' + @FromDate + ''' AND Convert(Date, E.InsertedTime) <= ''' + @ToDate + ''')'
				END 

				IF(@FromDate <> '' AND @ToDate = '')
				BEGIN
					SET @Query = @Query + ' AND  Convert(Date,E.InsertedTime) >= ''' + @FromDate + ''''
				END 

				IF(@FromDate = '' AND @ToDate <> '')
				BEGIN
					SET @Query = @Query + ' AND Convert(Date, E.InsertedTime) <= ''' + @ToDate + ''''
	 			END
			END 

			
			IF(@Sort <> '')
			BEGIN
				SET @Query = @Query + ' ORDER BY ' + @Sort
			END
			
			EXEC sp_ExecuteSQL @Query;
			SELECT @Total = 1;
			
    END TRY
    BEGIN CATCH
		 SELECT @Total = -1;
    END CATCH
	 
END











GO
/****** Object:  StoredProcedure [dbo].[FeCarrierRegistrationInsert]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[FeCarrierRegistrationInsert]
(
    @RegistrationId bigint,
    @CarrierId bigint,
    @FirstName nvarchar(256),
	@LastName nvarchar(256),
	@PreferredFirstName nvarchar(256),
    @Email nvarchar(256),
    @Phone nvarchar(50),
    @ExperienceYears int,
    @ResumeURL nvarchar(512),
	@linkedURL nvarchar(512),
	@WebsiteURL nvarchar(512),
    @Status nvarchar(64),
    @AdminComments nvarchar(max),
    @CreatedDate datetime,
    @CreatedBy nvarchar(256),
    @UpdatedDate datetime,
    @UpdatedBy nvarchar(256),
    @QStatus int output,
	@IpAddress nvarchar(256)
)
as
begin
    set nocount on;

    begin try
        begin transaction;

        if ((select count(*) from CarrierRegistrations where RegistrationId = @RegistrationId) = 0)
        begin
            insert into CarrierRegistrations
            values
            (
                @CarrierId,
                @FirstName,
				@LastName,
				@PreferredFirstName,
                @Email,
                @Phone,
                @ExperienceYears,
                Null,
				@linkedURL,
			    @WebsiteURL,
                'Registered',
                @AdminComments,
                @CreatedDate,
                @CreatedBy,
                @UpdatedDate,
                @UpdatedBy,
				@IpAddress
            );

		
			  set @RegistrationId = scope_identity();

            select @QStatus = 1;
          
        end
        else
        begin
            update CarrierRegistrations
            set CarrierId       = @CarrierId,
               FirstName            = @FirstName,
				LastName            = @LastName,
				PreferredFirstName            = @PreferredFirstName,
                Email           = @Email,
                Phone           = @Phone,
                ExperienceYears = @ExperienceYears,
              --  ResumeURL       = @ResumeURL,
				linkedURL     = @linkedURL,
				WebsiteURL     = @WebsiteURL,
                Status          = @Status,
                AdminComments   = @AdminComments,
                UpdatedDate     = @UpdatedDate,
                UpdatedBy       = @UpdatedBy

            where  RegistrationId = @RegistrationId;

     
            

            select @QStatus = 2;
        end


		IF(@ResumeURL <> 'NA')
			BEGIN
			   UPDATE CarrierRegistrations
			   SET  ResumeURL= CAST(@RegistrationId  AS nvarchar(12)) + '-' + dbo.RemoveSpecialChar(@FirstName, '&^a-z0-9') + '.' + @ResumeURL
			   WHERE RegistrationId = @RegistrationId	
			  
			  SELECT @ResumeURL = CAST(@RegistrationId  AS nvarchar(12)) + '-' + dbo.RemoveSpecialChar(@FirstName, '&^a-z0-9') + '.' + @ResumeURL
			END
			ELSE
			BEGIN
			 SELECT @ResumeURL=''
			END



        commit transaction;
    end try

    begin catch
        rollback transaction;
        select @QStatus = -1;
    end catch
end

GO
/****** Object:  StoredProcedure [dbo].[FEFinalHomePageInitialLoad]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[FEFinalHomePageInitialLoad]    
     
  @QStatus int output    
 
         
AS    
BEGIN    
    
 -- SET NOCOUNT ON added to prevent extra result sets from    
     
 SET NOCOUNT ON;    
     
 BEGIN TRY     
     
  
    
  /*====================================================================*/    
  /*         HomePage Engineered for Real-World Impact and Performance         */    
  /*====================================================================*/    
      
  SELECT * FROM PageDetails WHERE IsActive=1 AND dbo.RemoveSpecialChar(Heading, '^a-z0-9') = dbo.RemoveSpecialChar('HomePage Engineered for Real-World Impact and Performance', '^a-z0-9')     
    
 

   /*====================================================================*/    
  /*       HomePage Transforming the Future with Robotics       */    
  /*====================================================================*/    
      
  SELECT * FROM PageDetails WHERE IsActive=1 AND dbo.RemoveSpecialChar(Heading, '^a-z0-9') = dbo.RemoveSpecialChar('HomePage Transforming the Future with Robotics', '^a-z0-9')     
    
    


   /*====================================================================*/    
  /*        HomePage Applications Use Cases       */    
  /*====================================================================*/    
      
  SELECT * FROM PageDetails WHERE IsActive=1 AND dbo.RemoveSpecialChar(Heading, '^a-z0-9') = dbo.RemoveSpecialChar('HomePage Applications Use Cases', '^a-z0-9')     
    
   
 



          
   /*============================================*/    
  /*    Menu Details         */    
  /*============================================*/    



		 SELECT MI.MenuItemId, MI.DisplayName, MI.IdPath, MI.PageParentId,MI.PageLevel, MI.Position, PD.PageDetailId,PD.Target, PD.Heading, PD.PageUrl, PD.DocumentUrl, PD.OtherUrl     
    ,(SELECT IsActive FROM MenuItems WHERE MenuItems.MenuItemId = MI.PageParentId) AS ParentActive     
    ,(SELECT COUNT(*) FROM MenuItems AS CATCOUNT WHERE CATCOUNT.PageParentId = MI.MenuItemId) AS SubMenuItemCount    
    FROM MenuItems MI    
    LEFT Join MenuPages MP On MP.MenuItemId = MI.MenuItemId    
    LEFT Join PageDetails PD on PD.PageDetailId= MP.PageDetailId    
    Where MI.IsMenuBar=1 AND MI.IsActive = 1 Order By MI.Position    

 
     SELECT MI.MenuItemId, MI.DisplayName, MI.IdPath, MI.PageParentId,MI.PageLevel, MI.Position, PD.PageDetailId,PD.Target, PD.Heading, PD.PageUrl, PD.DocumentUrl, PD.OtherUrl     
    ,(SELECT IsActive FROM MenuItems WHERE MenuItems.MenuItemId = MI.PageParentId) AS ParentActive     
    ,(SELECT COUNT(*) FROM MenuItems AS CATCOUNT WHERE CATCOUNT.PageParentId = MI.MenuItemId) AS SubMenuItemCount    
    ,CASE WHEN MI.PageLevel=1 THEN MI.DisplayName     
    ELSE (CASE WHEN MI.PageLevel=2 THEN (SELECT M2.DisplayName from MenuItems M2 where M2.MenuItemId=MI.PageParentId)     
    ELSE (CASE WHEN MI.PageLevel=3 THEN (SELECT M3.DisplayName from MenuItems M3 where M3.MenuItemId=(SELECT M31.PageParentId from MenuItems M31 where M31.MenuItemId=MI.PageParentId))    
    ELSE (CASE WHEN MI.PageLevel=4 THEN (SELECT M4.DisplayName from MenuItems M4 where M4.MenuItemId=(SELECT M41.PageParentId from MenuItems M41 where M41.MenuItemId=(SELECT M42.PageParentId from MenuItems M42 where M42.MenuItemId=MI.PageParentId)))     
    ELSE '' END) END ) END ) END as ParentPageName    
    FROM MenuItems MI    
    Inner Join MenuPages MP On MP.MenuItemId = MI.MenuItemId    
    Inner Join PageDetails PD on PD.PageDetailId= MP.PageDetailId    
    Where   MI.IsFooterBar=1 AND MI.IsActive = 1 Order By MI.Position    

   /*====================================================================*/    
  /*         App Info       */    
  /*====================================================================*/    
    
  Select  *  From AppInfo 
    
    
    
     
  /*============================================*/    
  /*   Quick Links Details         */    
  /*============================================*/    
     
           SELECT MI.MenuItemId, MI.DisplayName, MI.IdPath, MI.PageParentId,MI.PageLevel, MI.Position, PD.PageDetailId,PD.Target, PD.Heading, PD.PageUrl, PD.DocumentUrl, PD.OtherUrl     
    ,(SELECT IsActive FROM MenuItems WHERE MenuItems.MenuItemId = MI.PageParentId) AS ParentActive     
    ,(SELECT COUNT(*) FROM MenuItems AS CATCOUNT WHERE CATCOUNT.PageParentId = MI.MenuItemId) AS SubMenuItemCount    
    ,CASE WHEN MI.PageLevel=1 THEN MI.DisplayName     
    ELSE (CASE WHEN MI.PageLevel=2 THEN (SELECT M2.DisplayName from MenuItems M2 where M2.MenuItemId=MI.PageParentId)     
    ELSE (CASE WHEN MI.PageLevel=3 THEN (SELECT M3.DisplayName from MenuItems M3 where M3.MenuItemId=(SELECT M31.PageParentId from MenuItems M31 where M31.MenuItemId=MI.PageParentId))    
    ELSE (CASE WHEN MI.PageLevel=4 THEN (SELECT M4.DisplayName from MenuItems M4 where M4.MenuItemId=(SELECT M41.PageParentId from MenuItems M41 where M41.MenuItemId=(SELECT M42.PageParentId from MenuItems M42 where M42.MenuItemId=MI.PageParentId)))     
    ELSE '' END) END ) END ) END as ParentPageName    
    FROM MenuItems MI    
    Inner Join MenuPages MP On MP.MenuItemId = MI.MenuItemId    
    Inner Join PageDetails PD on PD.PageDetailId= MP.PageDetailId    
    Where  MI.IsQuickLinks=1 AND MI.IsActive = 1 Order By MI.Position    
     
     
   

      /*====================================================================*/    
  /*        Customers & Partners       */    
  /*====================================================================*/    
      
  SELECT * FROM PageDetails WHERE IsActive=1 AND dbo.RemoveSpecialChar(Heading, '^a-z0-9') = dbo.RemoveSpecialChar('HomePage CustomersPatners', '^a-z0-9')     
    
   
 

  
 



  SELECT @QStatus = 1;    
        
 END TRY    
        
    BEGIN CATCH      
       SELECT @QStatus = -1;    
    END CATCH     
END    
    

GO
/****** Object:  StoredProcedure [dbo].[FEGetCustomersPatners]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

    
Create PROCEDURE [dbo].[FEGetCustomersPatners]    
  (@QStatus int output)    
         
AS    
BEGIN    
    
 -- SET NOCOUNT ON added to prevent extra result sets from    
 -- interfering with SELECT statements.    
     
 SET NOCOUNT ON;    
 DECLARE @Query NVARCHAR(max);    
     
 BEGIN TRY     
    
    
  
      /*====================================================================*/    
  /*        Customers & Partners       */    
  /*====================================================================*/    
      
  SELECT * FROM PageDetails WHERE IsActive=1 AND dbo.RemoveSpecialChar(Heading, '^a-z0-9') = dbo.RemoveSpecialChar('HomePage CustomersPatners', '^a-z0-9')     
    


  SELECT @QStatus = 1;    
        
 END TRY    
        
    BEGIN CATCH      
       SELECT @QStatus = -1;    
    END CATCH     
END     
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    


GO
/****** Object:  StoredProcedure [dbo].[FEGetListMainLayout]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

    
CREATE PROCEDURE [dbo].[FEGetListMainLayout]    
  (@QStatus int output)    
         
AS    
BEGIN    
    
 -- SET NOCOUNT ON added to prevent extra result sets from    
 -- interfering with SELECT statements.    
     
 SET NOCOUNT ON;    
 DECLARE @Query NVARCHAR(max);    
     
 BEGIN TRY     
    
    
  


     /*====================================================================*/    
  /*         App Info       */    
  /*====================================================================*/    
    
  Select * From AppInfo    
    

	

    /*============================================*/    
  /*    Menu Details         */    
  /*============================================*/    
      SELECT MI.MenuItemId, MI.DisplayName, MI.IdPath, MI.PageParentId,MI.PageLevel, MI.Position, PD.PageDetailId,PD.Target, PD.Heading, PD.PageUrl, PD.DocumentUrl, PD.OtherUrl         
    ,(SELECT IsActive FROM MenuItems WHERE MenuItems.MenuItemId = MI.PageParentId) AS ParentActive         
    ,(SELECT COUNT(*) FROM MenuItems AS CATCOUNT WHERE CATCOUNT.PageParentId = MI.MenuItemId) AS SubMenuItemCount        
    FROM MenuItems MI        
    LEFT Join MenuPages MP On MP.MenuItemId = MI.MenuItemId        
    LEFT Join PageDetails PD on PD.PageDetailId= MP.PageDetailId        
    Where  MI.IsMenuBar=1 AND MI.IsActive = 1 Order By MI.Position  
  /*============================================*/    
  /*   Footer Menu Details         */    
  /*============================================*/    
    
    SELECT MI.MenuItemId, MI.DisplayName, MI.IdPath, MI.PageParentId,MI.PageLevel, MI.Position, PD.PageDetailId,PD.Target, PD.Heading, PD.PageUrl, PD.DocumentUrl, PD.OtherUrl         
    ,(SELECT IsActive FROM MenuItems WHERE MenuItems.MenuItemId = MI.PageParentId) AS ParentActive         
    ,(SELECT COUNT(*) FROM MenuItems AS CATCOUNT WHERE CATCOUNT.PageParentId = MI.MenuItemId) AS SubMenuItemCount        
    ,CASE WHEN MI.PageLevel=1 THEN MI.DisplayName         
    ELSE (CASE WHEN MI.PageLevel=2 THEN (SELECT M2.DisplayName from MenuItems M2 where M2.MenuItemId=MI.PageParentId)         
    ELSE (CASE WHEN MI.PageLevel=3 THEN (SELECT M3.DisplayName from MenuItems M3 where M3.MenuItemId=(SELECT M31.PageParentId from MenuItems M31 where M31.MenuItemId=MI.PageParentId))        
    ELSE (CASE WHEN MI.PageLevel=4 THEN (SELECT M4.DisplayName from MenuItems M4 where M4.MenuItemId=(SELECT M41.PageParentId from MenuItems M41 where M41.MenuItemId=(SELECT M42.PageParentId from MenuItems M42 where M42.MenuItemId=MI.PageParentId)))      
  
   
    ELSE '' END) END ) END ) END as ParentPageName        
    FROM MenuItems MI        
    Inner Join MenuPages MP On MP.MenuItemId = MI.MenuItemId        
    Inner Join PageDetails PD on PD.PageDetailId= MP.PageDetailId        
    Where  MI.IsFooterBar=1 AND MI.IsActive = 1 Order By MI.Position     
  

    
   /*============================================*/        
  /*   Quick Links Details         */        
  /*============================================*/        
         
           SELECT MI.MenuItemId, MI.DisplayName, MI.IdPath, MI.PageParentId,MI.PageLevel, MI.Position, PD.PageDetailId,PD.Target, PD.Heading, PD.PageUrl, PD.DocumentUrl, PD.OtherUrl         
    ,(SELECT IsActive FROM MenuItems WHERE MenuItems.MenuItemId = MI.PageParentId) AS ParentActive         
    ,(SELECT COUNT(*) FROM MenuItems AS CATCOUNT WHERE CATCOUNT.PageParentId = MI.MenuItemId) AS SubMenuItemCount        
    ,CASE WHEN MI.PageLevel=1 THEN MI.DisplayName         
    ELSE (CASE WHEN MI.PageLevel=2 THEN (SELECT M2.DisplayName from MenuItems M2 where M2.MenuItemId=MI.PageParentId)         
    ELSE (CASE WHEN MI.PageLevel=3 THEN (SELECT M3.DisplayName from MenuItems M3 where M3.MenuItemId=(SELECT M31.PageParentId from MenuItems M31 where M31.MenuItemId=MI.PageParentId))        
    ELSE (CASE WHEN MI.PageLevel=4 THEN (SELECT M4.DisplayName from MenuItems M4 where M4.MenuItemId=(SELECT M41.PageParentId from MenuItems M41 where M41.MenuItemId=(SELECT M42.PageParentId from MenuItems M42 where M42.MenuItemId=MI.PageParentId)))      
  
   
    ELSE '' END) END ) END ) END as ParentPageName        
    FROM MenuItems MI        
    Inner Join MenuPages MP On MP.MenuItemId = MI.MenuItemId        
    Inner Join PageDetails PD on PD.PageDetailId= MP.PageDetailId        
    Where  MI.IsQuickLinks=1 AND MI.IsActive = 1 Order By MI.Position        
    
  

  
 

  SELECT @QStatus = 1;    
        
 END TRY    
        
    BEGIN CATCH      
       SELECT @QStatus = -1;    
    END CATCH     
END     
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    


GO
/****** Object:  StoredProcedure [dbo].[FEInsertLogReport]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


Create PROCEDURE [dbo].[FEInsertLogReport]
    (@QStatus int output
	,@XMLLogCounts nvarchar(max))  
        
AS
BEGIN

	SET NOCOUNT ON;


	declare @LogReportCount int

	
	set @LogReportCount =(select Count(*) from LogReport)

	
	


    BEGIN TRY
    BEGIN TRANSACTION
    
		IF(@XMLLogCounts<>'')
		BEGIN
			DECLARE @XML1 AS XML	        
				SELECT @XML1 = @XMLLogCounts

				CREATE TABLE #temp1(Rid INT PRIMARY KEY IDENTITY(1,1),
					LogTitle nvarchar(max),
					LogDescription nvarchar(max),			
					InsertedBy nvarchar(max))
				
				DECLARE @RowCount1 AS int
				DECLARE @CurrentRow1 AS int	
				
				INSERT INTO #temp1(LogTitle,LogDescription,InsertedBy)
					select 
					M.Item.value('LogTitle[1]','nvarchar(max)'),
					M.Item.value('LogDescription[1]','nvarchar(max)'),			
					M.Item.value('InsertedBy[1]','nvarchar(max)')
					FROM @XML1.nodes('/ArrayOfLogReport/LogReport') AS M(Item)
					
				SET @RowCount1 = @@ROWCOUNT	
				SELECT @RowCount1
				SET @CurrentRow1 = 1
				WHILE @CurrentRow1 < @RowCount1 + 1

				BEGIN	
				if(@LogReportCount>10000)
	             begin

				WITH CTE AS (
				SELECT TOP (1) *
				FROM LogReport
				ORDER BY LogId ASC
				)
				DELETE FROM CTE;

			End
			
						INSERT INTO LogReport SELECT							
									T.LogTitle,
									T.LogDescription,
									GETUTCDATE(),
									T.InsertedBy,
									GETUTCDATE(),
									T.InsertedBy,
									GETUTCDATE()
									FROM #temp1 T WHERE T.Rid = @CurrentRow1 	
						
					SELECT @QStatus = 1;
					SET @CurrentRow1 = @CurrentRow1 + 1
				END	

				DROP TABLE #temp1
		END
	COMMIT TRANSACTION   
    END TRY
    
    BEGIN CATCH 
		ROLLBACK TRANSACTION     
		SELECT @QStatus = -1;    
    END CATCH   
    
END
































GO
/****** Object:  StoredProcedure [dbo].[FEInverstorConformationUpdate]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[FEInverstorConformationUpdate]
    @Email NVARCHAR(64),
	@InverstorConformation  NVARCHAR(64),
	@UpdatedBy NVARCHAR(256),
    @QStatus INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION
        -- Auto-generate InvestorNo if not provided
      

		--and IPAddress=@IPAddress
        IF ((SELECT COUNT(*) FROM Investors WHERE Email = @Email ) = 1)
        BEGIN

             UPDATE Investors


            SET 
                InverstorConformation = @InverstorConformation,
                UpdatedDate = CONVERT(DATETIME, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')),
                UpdatedBy = @UpdatedBy

             WHERE Email = @Email



            SELECT @QStatus = 1
        END
        
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        SELECT @QStatus = -1
    END CATCH
END

GO
/****** Object:  StoredProcedure [dbo].[FEInvesmentAmountUpdate]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[FEInvesmentAmountUpdate]
    @Email NVARCHAR(64),
    @Quantity NVARCHAR(20),
    @USDAmount NVARCHAR(64),
	@UpdatedBy NVARCHAR(256),
    @QStatus INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION
        -- Auto-generate InvestorNo if not provided
      

		--and IPAddress=@IPAddress
        IF ((SELECT COUNT(*) FROM Investors WHERE Email = @Email ) = 1)
        BEGIN

             UPDATE Investors


            SET Quantity = @Quantity,
                USDAmount = @USDAmount,
                UpdatedDate = CONVERT(DATETIME, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')),
                UpdatedBy = @UpdatedBy

             WHERE Email = @Email



            SELECT @QStatus = 1
        END
        
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        SELECT @QStatus = -1
    END CATCH
END

GO
/****** Object:  StoredProcedure [dbo].[FEInvestorDocumentsInsert]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[FEInvestorDocumentsInsert]
    (@DocumentId bigint output
	,@InvestorId bigint
	,@DocumentName nvarchar(256)
	,@DocumentUrl nvarchar(max) output
	,@DocumentStatus nvarchar(256)
	--,@VerifiedDate datetime
	,@RejectionReason nvarchar(max)
	,@InvestorComments nvarchar(1024)
	,@AdminComments nvarchar(1024)
	,@InsertedDate datetime
	,@InsertedBy nvarchar(256)
	,@UpdatedDate datetime
	,@UpdatedBy nvarchar(256)
	,@QStatus int output)  
        
AS
BEGIN
	
	SET NOCOUNT ON;
  
   -- BEGIN TRY
   -- BEGIN TRANSACTION

   Declare @Name Nvarchar(256);

  Set @Name = (Select Top(1) FirstName +' '+ LastName from Investors where InvestorId=@InvestorId)
    
		 IF((SELECT COUNT(*) FROM InvestorDocuments WHERE DocumentId = @DocumentId) = 0)
		BEGIN
			INSERT INTO InvestorDocuments VALUES
			        (@InvestorId
					,@DocumentName
					,null
					,@DocumentStatus
					,null
					,@RejectionReason
					,@InvestorComments
					,@AdminComments
					,(Convert(DateTime, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')))
					,@Name
					,(Convert(DateTime, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')))
			        ,@UpdatedBy
					
					)
					
					Set @DocumentId = SCOPE_IDENTITY()

		SELECT @QStatus = 1;	
		END
		ELSE BEGIN		
			UPDATE InvestorDocuments
			   SET 
				   InvestorId=@InvestorId
				  ,DocumentName=@DocumentName
				  ,UpdatedBy=@UpdatedBy
				  ,UpdatedDate=(Convert(DateTime, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')))
			 WHERE DocumentId = @DocumentId
			 		 
			 SELECT @QStatus = 2;
			 
		 END
		

			IF(LEFT(@DocumentUrl, 1) = '.' OR @DocumentUrl <> 'NA')
		BEGIN
		
			IF(@DocumentUrl <> 'NA')
			BEGIN

			
				UPDATE InvestorDocuments
				   SET  DocumentUrl= CAST(@DocumentId  AS nvarchar(12)) + '-' + dbo.RemoveSpecialChar(@DocumentName, '^a-z0-9') + @DocumentUrl
				   WHERE DocumentId = @DocumentId	
			  
				  SELECT @DocumentUrl = CAST(@DocumentId AS nvarchar(12)) + '-' + dbo.RemoveSpecialChar(@DocumentName, '^a-z0-9') + @DocumentUrl	
			END	
			ELSE
			BEGIN
			  SELECT @DocumentUrl=''
			END 
		END
		

	--COMMIT TRANSACTION
	--END TRY
    
 --   BEGIN CATCH     
	--   ROLLBACK TRANSACTION 
 --      SELECT @QStatus = -1;           
 --   END CATCH   
    
END


GO
/****** Object:  StoredProcedure [dbo].[FEInvestorsCorporationUpdate]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[FEInvestorsCorporationUpdate]
    
	@Email NVARCHAR(64),
    @InvestType NVARCHAR(128),
	@CorporationName NVARCHAR(512),
    @CorporationAddress1 NVARCHAR(2048),
    @CorporationAddress2 NVARCHAR(2048),
    @CorporationCity NVARCHAR(128),
    @CorporationState NVARCHAR(128),
    @CorporationCountry NVARCHAR(128),
    @CorporationZipCode NVARCHAR(64),
    @EmployeeIdentityNo NVARCHAR(64),
    ---------------------------------
    @SigningPosition NVARCHAR(512),
    @SigningFirstName NVARCHAR(256),
    @SigningLastName NVARCHAR(256),
    @SigningAddress1 NVARCHAR(2048),
    @SigningAddress2 NVARCHAR(2048),
    @SigningCity NVARCHAR(128),
    @SigningState NVARCHAR(128),
    @SigningCountry NVARCHAR(128),
    @SigningZipCode NVARCHAR(64),
    @SigningDateOfBirth DATETIME,
    @SigningPhoneNo NVARCHAR(64),
    ---------------------------------
    @BeneficialFirstName NVARCHAR(256),
    @BeneficialLastName NVARCHAR(256),
    @BeneficialAddress1 NVARCHAR(2048),
    @BeneficialAddress2 NVARCHAR(2048),
    @BeneficialCity NVARCHAR(128),
    @BeneficialState NVARCHAR(128),
    @BeneficialCountry NVARCHAR(128),
    @BeneficialZipCode NVARCHAR(64),
    @BeneficialDateOfBirth DATETIME,
    @BeneficialPhoneNo NVARCHAR(64),
    @UpdatedBy NVARCHAR(256),
    @QStatus INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION

        IF ((SELECT COUNT(*) FROM Investors WHERE Email = @Email) = 1)
        BEGIN
            UPDATE Investors
            SET
			    InvestType = @InvestType, 
                CorporationName = @CorporationName,
                CorporationAddress1 = @CorporationAddress1,
                CorporationAddress2 = @CorporationAddress2,
                CorporationCity = @CorporationCity,
                CorporationState = @CorporationState,
                CorporationCountry = @CorporationCountry,
                CorporationZipCode = @CorporationZipCode,
                SigningPosition = @SigningPosition,
                SigningFirstName = @SigningFirstName,
                SigningLastName = @SigningLastName,
                SigningAddress1 = @SigningAddress1,
                SigningAddress2 = @SigningAddress2,
                SigningCity = @SigningCity,
                SigningState = @SigningState,
                SigningCountry = @SigningCountry,
                SigningZipCode = @SigningZipCode,
                SigningDateOfBirth = @SigningDateOfBirth,
                SigningPhoneNo = @SigningPhoneNo,
                BeneficialFirstName = @BeneficialFirstName,
                BeneficialLastName = @BeneficialLastName,
                BeneficialAddress1 = @BeneficialAddress1,
                BeneficialAddress2 = @BeneficialAddress2,
                BeneficialCity = @BeneficialCity,
                BeneficialState = @BeneficialState,
                BeneficialCountry = @BeneficialCountry,
                BeneficialZipCode = @BeneficialZipCode,
                BeneficialDateOfBirth = @BeneficialDateOfBirth,
                BeneficialPhoneNo = @BeneficialPhoneNo,
                UpdatedDate = CONVERT(DATETIME, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')),
                UpdatedBy = @UpdatedBy


              WHERE Email = @Email

            SELECT @QStatus = 1
        END
        ELSE
        BEGIN
            SELECT @QStatus = 0  -- No matching record found
        END
        
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        SELECT @QStatus = -1
    END CATCH
END

GO
/****** Object:  StoredProcedure [dbo].[FEInvestorsGetByEmail]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[FEInvestorsGetByEmail]
	(@Email nvarchar(64),
     @QStatus int OUTPUT)    
AS
BEGIN
   SET NOCOUNT ON;

	BEGIN TRY 


	Declare @InvestorId bigint

	Set @InvestorId =(SELECT  InvestorId FROM Investors WHERE Email = @Email)



		SELECT  * FROM Investors WHERE Email = @Email

		SELECT  * FROM InvestorDocuments WHERE InvestorId = @InvestorId order by InsertedDate DESC
		 
		 
		SELECT @QStatus = 1;   -- Success
		 
	END TRY
    
	BEGIN CATCH
		SELECT @QStatus = -1;  -- Failure
	END CATCH	 
END

GO
/****** Object:  StoredProcedure [dbo].[FEInvestorsGetByEmailandSSNO]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[FEInvestorsGetByEmailandSSNO]
(
    @Email NVARCHAR(64),
    @QStatus INT OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY


		IF((SELECT COUNT(*) FROM Investors WHERE InvestorStatus='Hold' and Email = @Email ) = 1)

        BEGIN
 
            UPDATE Investors SET SSNNO = NULL WHERE Email = @Email;

            SET @QStatus = 1;  
        END

        SELECT * FROM Investors WHERE Email = @Email AND SSNNO IS NOT NULL;

            SET @QStatus = 1;  -- 1 means Success
      
    END TRY

    BEGIN CATCH
        SET @QStatus = -1;  -- Failure
    END CATCH
END

GO
/****** Object:  StoredProcedure [dbo].[FEInvestorsInsertSignup]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[FEInvestorsInsertSignup]
    @InvestorId BIGINT,
    @InvestorNo NVARCHAR(20),
    @FirstName NVARCHAR(256),
	@LastName NVARCHAR(256),
	@PhoneNo NVARCHAR(64),
    @Email NVARCHAR(128),
    @Password NVARCHAR(256),
    @InsertedBy NVARCHAR(256),
    @UpdatedBy NVARCHAR(256),
    @QStatus INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION
        -- Auto-generate InvestorNo if not provided
        IF (@InvestorNo IS NULL OR LTRIM(RTRIM(@InvestorNo)) = '')
        BEGIN
            DECLARE @MaxNum INT
            SELECT @MaxNum = MAX(CAST(RIGHT(InvestorNo, 4) AS INT))
            FROM Investors
            WHERE InvestorNo LIKE 'INV[0-9][0-9][0-9][0-9]'
            IF @MaxNum IS NULL
                SET @MaxNum = 0
            SET @InvestorNo = 'INV' + RIGHT('000' + CAST(@MaxNum + 1 AS NVARCHAR(4)), 4)
        END

		--and IPAddress=@IPAddress
        IF ((SELECT COUNT(*) FROM Investors WHERE Email = @Email ) = 0)
        BEGIN
            INSERT INTO Investors(InvestorNo,FirstName,LastName,Email,PhoneNo,Password,InvestorStatus,InsertedDate,InsertedBy,UpdatedDate,UpdatedBy)
            VALUES
            (@InvestorNo, @FirstName, @LastName,  @Email,@PhoneNo, @Password,'New',CONVERT(DATETIME, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')),
             @InsertedBy,CONVERT(DATETIME, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')),@UpdatedBy)


            SET @InvestorId = SCOPE_IDENTITY()
            SELECT @QStatus = 1
        END
        ELSE
        BEGIN
            UPDATE Investors
            SET InvestorNo = @InvestorNo,
                FirstName = @FirstName,
				LastName=@LastName,
				PhoneNo=@PhoneNo,
				Password=@Password,
                UpdatedDate = CONVERT(DATETIME, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')),
                UpdatedBy = @UpdatedBy

             WHERE Email = @Email

            SELECT @QStatus = 2
        END

        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        SELECT @QStatus = -1
    END CATCH
END

GO
/****** Object:  StoredProcedure [dbo].[FEInvestorsIRAUpdate]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[FEInvestorsIRAUpdate]
    @Email NVARCHAR(128),
    @InvestType NVARCHAR(128),
    @IRADetails NVARCHAR(256),
    @IRAName NVARCHAR(512),
    @IRAEmail NVARCHAR(512),
    @IRAAddress1 NVARCHAR(2048),
    @IRAAddress2 NVARCHAR(2048),
    @IRACity NVARCHAR(128),
    @IRAState NVARCHAR(128),
    @IRACountry NVARCHAR(128),
    @IRAZipCode NVARCHAR(64),
    @IRAEmployeeNo NVARCHAR(64),
    @IRAAccountNo NVARCHAR(64),
    @IRACheckbox NVARCHAR(64),
    ---------------------------------
    @IndividualFirstName NVARCHAR(512),
    @IndividualLastName NVARCHAR(512),
    @IndividualEmail NVARCHAR(512),
    @IndividualAddress1 NVARCHAR(2048),
    @IndividualAddress2 NVARCHAR(2048),
    @IndividualCity NVARCHAR(128),
    @IndividualState NVARCHAR(128),
    @IndividualCountry NVARCHAR(128),
    @IndividualZipCode NVARCHAR(64),
    @IndividualDateOfBirth DATETIME,
    @IndividualPhoneNo NVARCHAR(64),
    @UpdatedBy NVARCHAR(256),
    @QStatus INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION

        IF ((SELECT COUNT(*) FROM Investors WHERE Email = @Email) = 1)
        BEGIN
            UPDATE Investors
            SET 
                InvestType = @InvestType,
                IRADetails = @IRADetails,
                IRAName = @IRAName,
                IRAEmail = @IRAEmail,
                IRAAddress1 = @IRAAddress1,
                IRAAddress2 = @IRAAddress2,
                IRACity = @IRACity,
                IRAState = @IRAState,
                IRACountry = @IRACountry,
                IRAZipCode = @IRAZipCode,
                IRAEmployeeNo = @IRAEmployeeNo,
                IRAAccountNo = @IRAAccountNo,
                IRACheckbox = @IRACheckbox,
                IndividualFirstName = @IndividualFirstName,
                IndividualLastName = @IndividualLastName,
                IndividualEmail = @IndividualEmail,
                IndividualAddress1 = @IndividualAddress1,
                IndividualAddress2 = @IndividualAddress2,
                IndividualCity = @IndividualCity,
                IndividualState = @IndividualState,
                IndividualCountry = @IndividualCountry,
                IndividualZipCode = @IndividualZipCode,
                IndividualDateOfBirth = @IndividualDateOfBirth,
                IndividualPhoneNo = @IndividualPhoneNo,
                UpdatedDate = CONVERT(DATETIME, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')),
                UpdatedBy = @UpdatedBy
            WHERE Email = @Email

            SELECT @QStatus = 1
        END
        ELSE
        BEGIN
            SELECT @QStatus = 0  -- No matching record found
        END
        
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        SELECT @QStatus = -1
    END CATCH
END

GO
/****** Object:  StoredProcedure [dbo].[FEInvestorsJointUpdate]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[FEInvestorsJointUpdate]
    @Email NVARCHAR(64),
    @InvestType NVARCHAR(128),
    @JiontHoldingType NVARCHAR(128),
    @PrimaryFirstName NVARCHAR(256),
    @PrimaryLastName NVARCHAR(256),
    @PrimaryAddress1 NVARCHAR(2048),
    @PrimaryAddress2 NVARCHAR(2048),
    @PrimaryCity NVARCHAR(256),
    @PrimaryState NVARCHAR(256),
    @PrimaryCountry NVARCHAR(256),
    @PrimaryZipCode NVARCHAR(256),
    @DateOfBirth DATETIME,
    ---------------------------------
    @JiontFirstName NVARCHAR(256),
    @JiontLastName NVARCHAR(256),
    @JiontAddress1 NVARCHAR(2048),
    @JiontAddress2 NVARCHAR(2048),
    @JiontCity NVARCHAR(256),
    @JiontState NVARCHAR(256),
    @JiontCountry NVARCHAR(256),
    @JiontZipCode NVARCHAR(256),
    @JiontDateOfBirth DATETIME,
    @UpdatedBy NVARCHAR(256),
    @QStatus INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION
        -- Auto-generate InvestorNo if not provided (assuming logic here if needed, but not implemented as per original)

        IF ((SELECT COUNT(*) FROM Investors WHERE Email = @Email) = 1)
        BEGIN
            UPDATE Investors
            SET 
                InvestType = @InvestType,
                JiontHoldingType = @JiontHoldingType,
                PrimaryFirstName = @PrimaryFirstName,
                PrimaryLastName = @PrimaryLastName,
                PrimaryAddress1 = @PrimaryAddress1,
                PrimaryAddress2 = @PrimaryAddress2,
                PrimaryCity = @PrimaryCity,
                PrimaryState = @PrimaryState,
                PrimaryCountry = @PrimaryCountry,
                PrimaryZipCode = @PrimaryZipCode,
                DateOfBirth = @DateOfBirth,
                JiontFirstName = @JiontFirstName,
                JiontLastName = @JiontLastName,
                JiontAddress1 = @JiontAddress1,
                JiontAddress2 = @JiontAddress2,
                JiontCity = @JiontCity,
                JiontState = @JiontState,
                JiontCountry = @JiontCountry,
                JiontZipCode = @JiontZipCode,
                JiontDateOfBirth = @JiontDateOfBirth,
                UpdatedDate = CONVERT(DATETIME, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')),
                UpdatedBy = @UpdatedBy
            WHERE Email = @Email

            SELECT @QStatus = 1
        END
        ELSE
        BEGIN
            SELECT @QStatus = 0  -- No matching record found
        END
        
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        SELECT @QStatus = -1
    END CATCH
END

GO
/****** Object:  StoredProcedure [dbo].[FEInvestorsMySelfUpdate]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[FEInvestorsMySelfUpdate]
    @Email NVARCHAR(64),
	@InvestType NVARCHAR(64),
    @LegalFirstName NVARCHAR(256),
	@LegalLastName NVARCHAR(256),
	@MyselfAddress1 NVARCHAR(2048),
	@MyselfAddress2 NVARCHAR(2048),
	@MyselfCity NVARCHAR(256),
	@MyselfState NVARCHAR(256),
	@MyselfCountry NVARCHAR(256),
	@MyselfZipCode NVARCHAR(256),
	@DateOfBirth Datetime,
	@UpdatedBy NVARCHAR(256),
	@SSNNo nvarchar(256),
    @QStatus INT OUTPUT


	

AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION
        -- Auto-generate InvestorNo if not provided
      

		--and IPAddress=@IPAddress
        IF ((SELECT COUNT(*) FROM Investors WHERE Email = @Email ) = 1)
        BEGIN

             UPDATE Investors


            SET 
			    InvestType     = @InvestType,
			    LegalFirstName = @LegalFirstName,
                LegalLastName = @LegalLastName,
				MyselfAddress1 = @MyselfAddress1,
				MyselfAddress2 = @MyselfAddress2,
				MyselfCity = @MyselfCity,
				MyselfState = @MyselfState,
				MyselfCountry = @MyselfCountry,
				MyselfZipCode = @MyselfZipCode,
				DateOfBirth = @DateOfBirth,
                UpdatedDate = CONVERT(DATETIME, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')),
                UpdatedBy = @UpdatedBy,
				SSNNo   =@SSNNo

             WHERE Email = @Email



            SELECT @QStatus = 1
        END
        
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        SELECT @QStatus = -1
    END CATCH
END

GO
/****** Object:  StoredProcedure [dbo].[FEInvestorsNewProfileUpdate]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[FEInvestorsNewProfileUpdate]
    @Email NVARCHAR(64),
	@InvestType NVARCHAR(64),
    @NewProfileFirstName NVARCHAR(256),
	@NewProfileLastName NVARCHAR(256),
	@NewProfileAddress1 NVARCHAR(2048),
	@NewProfileAddress2 NVARCHAR(2048),
	@NewProfileCity NVARCHAR(256),
	@NewProfileState NVARCHAR(256),
	@NewProfileCountry NVARCHAR(256),
	@NewProfileZipCode NVARCHAR(256),
	@NewProfileDateOfBirth Datetime,
	@UpdatedBy NVARCHAR(256),
    @QStatus INT OUTPUT


	

AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION
        -- Auto-generate InvestorNo if not provided
      

		--and IPAddress=@IPAddress
        IF ((SELECT COUNT(*) FROM Investors WHERE Email = @Email ) = 1)
        BEGIN

             UPDATE Investors


            SET 
			    InvestType     = @InvestType,
			    NewProfileFirstName = @NewProfileFirstName,
                NewProfileLastName = @NewProfileLastName,
				NewProfileAddress1 = @NewProfileAddress1,
				NewProfileAddress2 = @NewProfileAddress2,
				NewProfileCity = @NewProfileCity,
				NewProfileState = @NewProfileState,
				NewProfileCountry = @NewProfileCountry,
				NewProfileZipCode = @NewProfileZipCode,
				NewProfileDateOfBirth = @NewProfileDateOfBirth,
                UpdatedDate = CONVERT(DATETIME, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')),
                UpdatedBy = @UpdatedBy

             WHERE Email = @Email



            SELECT @QStatus = 1
        END
        
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        SELECT @QStatus = -1
    END CATCH
END

GO
/****** Object:  StoredProcedure [dbo].[FEInvestorsTrustUpdate]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[FEInvestorsTrustUpdate]
     @Email NVARCHAR(64),
    @InvestType NVARCHAR(128),
    ---------------------------------
    @TrustName NVARCHAR(512),
    @TrustDate DATETIME,
    @TrustAddress1 NVARCHAR(2048),
    @TrustAddress2 NVARCHAR(2048),
    @TrustCity NVARCHAR(128),
    @TrustState NVARCHAR(128),
    @TrustCountry NVARCHAR(128),
    @TrustZipCode NVARCHAR(64),
    ---------------------------------
    @SigningTrusteeFirstName NVARCHAR(256),
    @SigningTrusteeLastName NVARCHAR(256),
    @SigningTrusteeAddress1 NVARCHAR(2048),
    @SigningTrusteeAddress2 NVARCHAR(2048),
    @SigningTrusteeCity NVARCHAR(128),
    @SigningTrusteeState NVARCHAR(128),
    @SigningTrusteeCountry NVARCHAR(128),
    @SigningTrusteeZipCode NVARCHAR(64),
    @SigningTrusteeDateOfBirth DATETIME,
    @SigningTrusteePhoneNo NVARCHAR(64),
    @UpdatedBy NVARCHAR(256),
    @QStatus INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION

        IF ((SELECT COUNT(*) FROM Investors WHERE  Email = @Email) = 1)
        BEGIN
            UPDATE Investors
            SET 
                TrustName = @TrustName,
                TrustDate = @TrustDate,
                TrustAddress1 = @TrustAddress1,
                TrustAddress2 = @TrustAddress2,
                TrustCity = @TrustCity,
                TrustState = @TrustState,
                TrustCountry = @TrustCountry,
                TrustZipCode = @TrustZipCode,
                SigningTrusteeFirstName = @SigningTrusteeFirstName,
                SigningTrusteeLastName = @SigningTrusteeLastName,
                SigningTrusteeAddress1 = @SigningTrusteeAddress1,
                SigningTrusteeAddress2 = @SigningTrusteeAddress2,
                SigningTrusteeCity = @SigningTrusteeCity,
                SigningTrusteeState = @SigningTrusteeState,
                SigningTrusteeCountry = @SigningTrusteeCountry,
                SigningTrusteeZipCode = @SigningTrusteeZipCode,
                SigningTrusteeDateOfBirth = @SigningTrusteeDateOfBirth,
                SigningTrusteePhoneNo = @SigningTrusteePhoneNo,
                UpdatedDate = CONVERT(DATETIME, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')),
                UpdatedBy = @UpdatedBy

            WHERE Email = @Email

            SELECT @QStatus = 1
        END
        ELSE
        BEGIN
            SELECT @QStatus = 0  -- No matching record found
        END
        
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        SELECT @QStatus = -1
    END CATCH
END

GO
/****** Object:  StoredProcedure [dbo].[FEInvestorsUpdateDocument]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[FEInvestorsUpdateDocument]
    @Email NVARCHAR(64),
	@NewProfileFirstName NVARCHAR(64),
	@UpdatedBy NVARCHAR(256),
    @QStatus INT OUTPUT


	

AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION
        -- Auto-generate InvestorNo if not provided
      

		--and IPAddress=@IPAddress
        IF ((SELECT COUNT(*) FROM Investors WHERE Email = @Email ) = 1)
        BEGIN

             UPDATE Investors


            SET 
			    NewProfileFirstName     = @NewProfileFirstName,
                UpdatedDate = CONVERT(DATETIME, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')),
                UpdatedBy = @UpdatedBy

             WHERE Email = @Email



            SELECT @QStatus = 1
        END
        
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        SELECT @QStatus = -1
    END CATCH
END

GO
/****** Object:  StoredProcedure [dbo].[FEInvestorsUpdatePaymentConformation]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[FEInvestorsUpdatePaymentConformation]
    @Email NVARCHAR(64),
	@PaymentType NVARCHAR(64),
	@TermsConditions bit,
	@UpdatedBy NVARCHAR(256),
    @QStatus INT OUTPUT

	
	

AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION
        -- Auto-generate InvestorNo if not provided
      

		--and IPAddress=@IPAddress
        IF ((SELECT COUNT(*) FROM Investors WHERE Email = @Email ) = 1)
        BEGIN

             UPDATE Investors


            SET 
			    PaymentType     = @PaymentType,
				TermsConditions     = @TermsConditions,
                UpdatedDate = CONVERT(DATETIME, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')),
                UpdatedBy = @UpdatedBy

             WHERE Email = @Email



            SELECT @QStatus = 1
        END
        
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        SELECT @QStatus = -1
    END CATCH
END

GO
/****** Object:  StoredProcedure [dbo].[InvestorDocumentsDelete]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[InvestorDocumentsDelete]
    (@DocumentId bigint
    ,@QStatus int output)  
        
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.	
	SET NOCOUNT ON;
    -- Insert statements for procedure here
    
    BEGIN TRY    
    BEGIN TRANSACTION

	 Declare @InvestorId bigint
	 Set @InvestorId =(Select Top(1) InvestorId from InvestorDocuments WHERE DocumentId = @DocumentId)


		DELETE FROM InvestorDocuments WHERE DocumentId = @DocumentId
	
		

			
		SELECT @QStatus = 1;
	
	COMMIT TRANSACTION	
    END TRY

    BEGIN CATCH    
         ROLLBACK TRANSACTION
		 SELECT @QStatus = -1;		 
    END CATCH
    	
END

GO
/****** Object:  StoredProcedure [dbo].[InvestorDocumentsGetById]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InvestorDocumentsGetById] 
	(@DocumentId bigint
    ,@QStatus int OUTPUT)    
        
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.	
   SET NOCOUNT ON;
    -- Insert statements for procedure here

    BEGIN TRY     
		 SELECT (Select Email from Investors C where  C.InvestorId=CD.InvestorId) as Email, 
		 (Select FirstName from Investors C where  C.InvestorId=CD.InvestorId) as Name, 
		 
		  * FROM InvestorDocuments CD  WHERE DocumentId  = @DocumentId
		 SELECT @QStatus = 1;		 
    END TRY
    
    BEGIN CATCH    
		 SELECT @QStatus = -1;		 
    END CATCH	
	 
END

GO
/****** Object:  StoredProcedure [dbo].[InvestorDocumentsGetList]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[InvestorDocumentsGetList]
    @QStatus int output
        
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.	
	SET NOCOUNT ON;
    -- Insert statements for procedure here
    
    BEGIN TRY        
		SELECT * FROM InvestorDocuments	ORDER BY InsertedDate ASC
		SELECT @QStatus = 1;			
    END TRY

    BEGIN CATCH    
		 SELECT @QStatus = -1;		 
    END CATCH
    	
END

GO
/****** Object:  StoredProcedure [dbo].[InvestorDocumentsGetListByVariable]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[InvestorDocumentsGetListByVariable] 
	 (@PageNo int
	 ,@Items int
     ,@InvestorId bigint
     ,@Search nvarchar(126)
     ,@Total int output
     ,@Sort nvarchar(126))
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	
	SET NOCOUNT ON;
	
	DECLARE @Start int, @End INT ;
	DECLARE @Query nvarchar(max);
	Set @Items = 1000
    -- Insert statements for procedure here
    
    CREATE TABLE #temp(Rid INT PRIMARY KEY IDENTITY(1,1)
				,DocumentId bigint 
				,InvestorId bigint
				,DocumentName nvarchar(256)
				,DocumentUrl nvarchar(1024)
				,DocumentStatus nvarchar(256)
				,VerifiedDate datetime
				,RejectionReason nvarchar(1024)
				,InsertedDate datetime
				,InsertedBy nvarchar(256)
				,UpdatedBy nvarchar(256)
				,UpdatedDate datetime
				,InvestorComments nvarchar(1024))
	
	SELECT @Start = (@PageNo - 1) * @Items  
	   
	SELECT @End = @Start + @Items 
	
	SET @Query = 'INSERT INTO #temp 
		 SELECT  S.DocumentId  
				,S.InvestorId 
				,S.DocumentName 
				,S.DocumentUrl
				,S.DocumentStatus
				,S.VerifiedDate
				,S.RejectionReason
				,S.InsertedDate
				,S.InsertedBy 
				,S.UpdatedBy 
				,S.UpdatedDate
				,S.InvestorComments
						 
		FROM InvestorDocuments S  
		
		inner join  Investors C on S.InvestorId =C.InvestorId
		
		WHERE S.DocumentId <> 0'
		
	IF(@Search <> '')
	
    BEGIN
		SET @Query = @Query + ' AND  (S.DocumentName LIKE ''%' + @Search + '%'' OR C.PhoneNo LIKE ''%' + @Search + '%'') '
    END
    
    IF(@InvestorId<>0)
		BEGIN
			SET @Query=@Query + ' AND S.InvestorId='+cast(@InvestorId as nvarchar(max))
		END
	
	IF(@Sort <> '')
	
	BEGIN
		SET @Query = @Query + ' ORDER BY ' + @Sort
	END
		 	
	EXEC sp_ExecuteSQL @Query;
	
	SELECT @Total = COUNT(Rid) from #temp 
			
	SELECT * FROM #temp WHERE Rid > @Start AND  Rid <= @End
	
	  DROP TABLE #temp

END

GO
/****** Object:  StoredProcedure [dbo].[InvestorDocumentsInsert]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[InvestorDocumentsInsert]
    (@DocumentId bigint 
	,@InvestorId bigint
	,@DocumentName nvarchar(256)
	,@DocumentUrl nvarchar(max) output
	,@DocumentStatus nvarchar(256)
	--,@VerifiedDate datetime
	,@RejectionReason nvarchar(max)
	,@AdminComments nvarchar(1024)
	,@InsertedDate datetime
	,@InsertedBy nvarchar(256)
	,@UpdatedDate datetime
	,@UpdatedBy nvarchar(256)
	,@QStatus int output)  
        
AS
BEGIN
	
	SET NOCOUNT ON;
  
   -- BEGIN TRY
   -- BEGIN TRANSACTION
    
		 IF((SELECT COUNT(*) FROM InvestorDocuments WHERE DocumentId = @DocumentId) = 0)
		BEGIN
			INSERT INTO InvestorDocuments VALUES
			        (@InvestorId
					,@DocumentName
					,null
					,@DocumentStatus
					,null
					,@RejectionReason
					,null
					,@AdminComments
					,(Convert(DateTime, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')))
					,@InsertedBy
					,(Convert(DateTime, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')))
			        ,@UpdatedBy
					
					)
					
					Set @DocumentId = SCOPE_IDENTITY()

		SELECT @QStatus = 1;	
		END
		ELSE BEGIN		
			UPDATE InvestorDocuments
			   SET 
				   InvestorId=@InvestorId
				  ,DocumentName=@DocumentName
				  ,UpdatedBy=@UpdatedBy
				  ,UpdatedDate=(Convert(DateTime, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')))
			 WHERE DocumentId = @DocumentId
			 		 
			 SELECT @QStatus = 2;
			 
		 END
		

			IF(LEFT(@DocumentUrl, 1) = '.' OR @DocumentUrl <> 'NA')
		BEGIN
		
			IF(@DocumentUrl <> 'NA')
			BEGIN

			
				UPDATE InvestorDocuments
				   SET  DocumentUrl= CAST(@DocumentId  AS nvarchar(12)) + '-' + dbo.RemoveSpecialChar(@DocumentName, '^a-z0-9') + @DocumentUrl
				   WHERE DocumentId = @DocumentId	
			  
				  SELECT @DocumentUrl = CAST(@DocumentId AS nvarchar(12)) + '-' + dbo.RemoveSpecialChar(@DocumentName, '^a-z0-9') + @DocumentUrl	
			END	
			ELSE
			BEGIN
			  SELECT @DocumentUrl=''
			END 
		END
		

	--COMMIT TRANSACTION
	--END TRY
    
 --   BEGIN CATCH     
	--   ROLLBACK TRANSACTION 
 --      SELECT @QStatus = -1;           
 --   END CATCH   
    
END


GO
/****** Object:  StoredProcedure [dbo].[InvestorDocumentsUpdateStatus]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InvestorDocumentsUpdateStatus]
    (@DocumentId bigint
    ,@QStatus int output
	,@Status nvarchar(256)
	,@UpdatedBy nvarchar(256)
	,@RejectionReason nvarchar(512))  
        
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    
    BEGIN TRY    
    BEGIN TRANSACTION
    
		IF(@Status <> '')
		BEGIN
			UPDATE InvestorDocuments SET DocumentStatus = @Status,VerifiedDate=GetDate(),UpdatedBy=@UpdatedBy,
			
			RejectionReason=@RejectionReason WHERE DocumentId = @DocumentId
		
		END			
		
		SELECT @QStatus = 1;
		
	COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
    
         ROLLBACK TRANSACTION
		 SELECT @QStatus = -1;
		 
    END CATCH
    	
END








GO
/****** Object:  StoredProcedure [dbo].[InvestorMOUDelete]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** InvestorMOUDelete ******/
CREATE PROCEDURE [dbo].[InvestorMOUDelete]
    @Id BIGINT,
    @QStatus INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        DECLARE @InvestorId BIGINT;
        SELECT TOP 1 @InvestorId = InvestorId FROM InvestorMOU WHERE Id = @Id;

        DELETE FROM InvestorMOU WHERE Id = @Id;

        SELECT @QStatus = 1;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        SELECT @QStatus = -1;
    END CATCH
END



GO
/****** Object:  StoredProcedure [dbo].[InvestorMOUGetById]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** InvestorMOUGetById ******/
CREATE PROCEDURE [dbo].[InvestorMOUGetById]
    @Id BIGINT,
    @QStatus INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        SELECT (Select Email from Investors C where  C.InvestorId=CD.InvestorId) as Email, 
		 (Select FirstName from Investors C where  C.InvestorId=CD.InvestorId) as Name, 
		 * FROM InvestorMOU CD WHERE Id = @Id;
        SELECT @QStatus = 1;
    END TRY
    BEGIN CATCH
        SELECT @QStatus = -1;
    END CATCH
END



GO
/****** Object:  StoredProcedure [dbo].[InvestorMOUGetList]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** InvestorMOUGetList ******/
CREATE PROCEDURE [dbo].[InvestorMOUGetList]
    @QStatus INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        SELECT * FROM InvestorMOU ORDER BY InsertedDate ASC;
        SELECT @QStatus = 1;
    END TRY
    BEGIN CATCH
        SELECT @QStatus = -1;
    END CATCH
END



GO
/****** Object:  StoredProcedure [dbo].[InvestorMOUGetListByVariable]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** InvestorMOUGetListByVariable ******/
CREATE PROCEDURE [dbo].[InvestorMOUGetListByVariable]
    @PageNo INT,
    @Items INT,
    @InvestorId BIGINT,
    @Search NVARCHAR(126),
    @Total INT OUTPUT,
    @Sort NVARCHAR(126)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Start INT, @End INT;
    DECLARE @Query NVARCHAR(MAX);

    CREATE TABLE #temp(
        Rid INT PRIMARY KEY IDENTITY(1,1),
        Id BIGINT,
        InvestorId BIGINT,
        MOUNumber NVARCHAR(50),
        DocumentUrl NVARCHAR(512),
        MOUStatus NVARCHAR(50),
        SubmittedDate DATETIME,
        ReviewedDate DATETIME,
        ApprovedDate DATETIME,
        RejectionReason NVARCHAR(1024),
        AdminComments NVARCHAR(1024),
        InvestorComments NVARCHAR(1024),
        IsActive BIT,
        InsertedDate DATETIME,
        InsertedBy NVARCHAR(256),
        UpdatedDate DATETIME,
        UpdatedBy NVARCHAR(256)
    );

    SELECT @Start = (@PageNo - 1) * @Items;
    SELECT @End = @Start + @Items;

    SET @Query = N'
        INSERT INTO #temp
      SELECT
    C.Id,
    C.InvestorId,
    C.MOUNumber,
    C.DocumentUrl,
    C.MOUStatus,
    C.SubmittedDate,
    C.ReviewedDate,
    C.ApprovedDate,
    C.RejectionReason,
    C.AdminComments,
    C.InvestorComments,
    C.IsActive,
    C.InsertedDate,
    C.InsertedBy,
    C.UpdatedDate,
    C.UpdatedBy
FROM InvestorMOU C
INNER JOIN Investors S ON S.InvestorId = C.InvestorId
WHERE C.Id <> 0 
    AND (C.InvestorComments <> ''WireTransfer'' OR C.InvestorComments IS NULL)
    ';

    SET @Query = @Query + N' AND (
		C.MOUNumber LIKE ''%' + REPLACE(@Search, '''', '''''') + '%'' 
		OR C.MOUStatus LIKE ''%' + REPLACE(@Search, '''', '''''') + '%''
		OR S.PhoneNo LIKE ''%' + REPLACE(@Search, '''', '''''') + '%''
	)';


    IF(@InvestorId <> 0)
        SET @Query = @Query + ' AND C.InvestorId = ' + CAST(@InvestorId AS NVARCHAR(20));

    IF(@Sort <> '')
        SET @Query = @Query + ' ORDER BY ' + @Sort;

    EXEC sp_executesql @Query;

    SELECT @Total = COUNT(Rid) FROM #temp;

    SELECT * FROM #temp WHERE Rid > @Start AND Rid <= @End;

    DROP TABLE #temp;
END



GO
/****** Object:  StoredProcedure [dbo].[InvestorMOUInsert]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InvestorMOUInsert]
    @Id BIGINT,
    @InvestorId BIGINT,
  --  @MOUNumber NVARCHAR(50),
    @DocumentUrl NVARCHAR(512) OUTPUT,
 --   @MOUStatus NVARCHAR(50),
  --  @SubmittedDate DATETIME,
  --  @ReviewedDate DATETIME,
  --  @ApprovedDate DATETIME,
  --  @RejectionReason NVARCHAR(1024),
  --  @AdminComments NVARCHAR(1024),
  --  @InvestorComments NVARCHAR(1024),
  --  @IsActive BIT,
    @InsertedBy NVARCHAR(256),
   -- @InsertedDate DATETIME,
    @UpdatedBy NVARCHAR(256),
   -- @UpdatedDate DATETIME,
    @QStatus INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;


		Declare  @MOUNumber NVARCHAR(50);

      -- Auto-generate MOUNumber if not provided
		IF (@MOUNumber IS NULL OR LTRIM(RTRIM(@MOUNumber)) = '')
		BEGIN
			DECLARE @MaxNum INT

			SELECT @MaxNum = 
				MAX(CAST(RIGHT(MOUNumber, 4) AS INT))
			FROM InvestorMOU
			WHERE MOUNumber LIKE 'CUS[0-9][0-9][0-9][0-9]'

			IF @MaxNum IS NULL
				SET @MaxNum = 0

			SET @MOUNumber = 'CUS' + RIGHT('000' + CAST(@MaxNum + 1 AS NVARCHAR(4)), 4)
		END


        IF((SELECT COUNT(*) FROM InvestorMOU WHERE Id = @Id) = 0)
        BEGIN
            INSERT INTO InvestorMOU
            (
                InvestorId, MOUNumber, DocumentUrl, MOUStatus,
                SubmittedDate, ReviewedDate, ApprovedDate, RejectionReason,
                AdminComments, InvestorComments, IsActive,
                InsertedDate, InsertedBy, UpdatedDate, UpdatedBy
            )
            VALUES
            (
                @InvestorId, @MOUNumber, NULL, NULL,
                CONVERT(DATETIME, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')), NULL, NULL, NULL,
                NULL, NULL, 1,
                CONVERT(DATETIME, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')),
                @InsertedBy,
                CONVERT(DATETIME, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')),
                @UpdatedBy
            );

            SET @Id = SCOPE_IDENTITY();
            SELECT @QStatus = 1;
        END
        ELSE
        BEGIN
            UPDATE InvestorMOU
            SET
                InvestorId = @InvestorId,
               -- MOUNumber = @MOUNumber,
               -- MOUStatus = @MOUStatus,
               --- SubmittedDate = @SubmittedDate,
               -- ReviewedDate = @ReviewedDate,
               -- ApprovedDate = @ApprovedDate,
               -- RejectionReason = @RejectionReason,
             --   AdminComments = @AdminComments,
             --   InvestorComments = @InvestorComments,
               -- IsActive = @IsActive,
                UpdatedDate = CONVERT(DATETIME, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')),
                UpdatedBy = @UpdatedBy
            WHERE Id = @Id;

            SELECT @QStatus = 2;
        END

        -- Document URL naming logic
        IF(@DocumentUrl IS NOT NULL AND @DocumentUrl <> 'NA')
        BEGIN
            UPDATE InvestorMOU
            SET DocumentUrl = CAST(@Id AS NVARCHAR(12)) + '-' + dbo.RemoveSpecialChar(@MOUNumber, '^a-z0-9') + @DocumentUrl
            WHERE Id = @Id;

            SELECT @DocumentUrl = CAST(@Id AS NVARCHAR(12)) + '-' + dbo.RemoveSpecialChar(@MOUNumber, '^a-z0-9') + @DocumentUrl;
        END
        ELSE
        BEGIN
            SELECT @DocumentUrl = '';
        END

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        SELECT @QStatus = -1;
    END CATCH
END



GO
/****** Object:  StoredProcedure [dbo].[InvestorMOUUpdateStatus]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** InvestorMOUUpdateStatus ******/
CREATE PROCEDURE [dbo].[InvestorMOUUpdateStatus]
    (@Id bigint
    ,@QStatus int output
	,@Status nvarchar(256)
	,@UpdatedBy nvarchar(256)
	,@RejectionReason nvarchar(512))  
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

    
		IF(@Status <> '')
		BEGIN
			UPDATE InvestorMOU SET MOUStatus = @Status,ReviewedDate=GetDate(),UpdatedBy=@UpdatedBy,
			RejectionReason=@RejectionReason WHERE Id = @Id
		
		END	

        SELECT @QStatus = 1;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        SELECT @QStatus = -1;
    END CATCH
END





GO
/****** Object:  StoredProcedure [dbo].[InvestorPageDetailsGetListByVariable]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[InvestorPageDetailsGetListByVariable]
	 (@ChapterId bigint
	 ,@Search nvarchar(126)
     ,@Sort nvarchar(126)
	 ,@PageNo int
     ,@Items int
     ,@Total int output)
AS
BEGIN
	
	SET NOCOUNT ON;
	
	BEGIN TRY
	
	DECLARE @Start int, @End INT ;
	DECLARE @Query nvarchar(max);
    
    CREATE TABLE #temp(Rid INT PRIMARY KEY IDENTITY(1,1)
				,PageDetailId bigint 
				,Heading nvarchar(256)
				,Description nvarchar(max)
				,PageUrl nvarchar(512)
				,DocumentUrl nvarchar(512)
				,Target nvarchar(256)
				,IsActive bit
				,PageTitle nvarchar(512)
				,MetaDescription nvarchar(1024)
				,MetaKeywords nvarchar(1024)
				,Topline nvarchar(2024)
				,InsertedBy nvarchar(64)
				,InsertedDate datetime
				,UpdatedBy nvarchar(64)
				,UpdatedDate datetime 
				,ChapterId bigint
				,ChapterName nvarchar(128)
				,OtherUrl nvarchar(512))

				
			SELECT @Start = (@PageNo - 1) * @Items  
	   
			SELECT @End = @Start + @Items 
	
			SET @Query = 'INSERT INTO #temp 
				 SELECT Distinct IP.PageDetailId  
				,IP.Heading
				,IP.Description
				,IP.PageUrl
				,IP.DocumentUrl
				,IP.Target
				,IP.IsActive
				,IP.PageTitle
				,IP.MetaDescription
				,IP.MetaKeywords
				,IP.Topline
				,IP.InsertedBy
				,IP.InsertedDate 
				,IP.UpdatedBy
				,IP.UpdatedDate
				,0 as ChapterId
				,'''' as ChapterName
				,IP.OtherUrl
				FROM PageDetails IP 
				WHERE IP.PageDetailId <> 0 
				--and PageDetailId in (17,18,19,20,21) 
				
				
				'

				
		
	--IF(@ChapterId <> '' and @ChapterId <> 1)
	--		BEGIN
	--			SET @Query = @Query + ' AND  MI.ChapterId = '+ CAST(@ChapterId as nvarchar(64))
	--		END
		IF(@Search <> '')
			BEGIN
				SET @Query = @Query + ' AND  IP.Heading LIKE ''%' + @Search + '%'''
			END
		IF(@Sort <> '')
			BEGIN
				SET @Query = @Query + ' ORDER BY ' + @Sort
			END
	
		EXEC sp_ExecuteSQL @Query;
		
		SELECT @Total = COUNT(Rid) from #temp 
				
		SELECT * FROM #temp WHERE Rid > @Start AND  Rid <= @End
	
		DROP TABLE #temp
	END TRY
    
    BEGIN CATCH  
       SELECT @Total = -1; 		   
    END CATCH   
END










GO
/****** Object:  StoredProcedure [dbo].[InvestorsDelete]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InvestorsDelete]
@InvestorId BIGINT,
@QStatus INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION;
DELETE FROM Investors WHERE InvestorId = @InvestorId;
SELECT @QStatus = 1;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
SELECT @QStatus = -1;
END CATCH
END

GO
/****** Object:  StoredProcedure [dbo].[InvestorsExportExcel]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InvestorsExportExcel]
(@Search nvarchar(126)
,@Sort nvarchar(126)
,@Total int output
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--BEGIN TRY
DECLARE @Start int, @End INT ;
DECLARE @Query nvarchar(max);
--DECLARE @Query nvarchar(max);
-- Insert statements for procedure here
SET @Query = 'SELECT
I.InvestorId,
I.InvestorNo,
I.FirstName,
I.LastName,
I.PhoneNo,
I.WhatsAppNo,
I.Email,
I.DateOfBirth,
I.ProfileImage,
I.InvestorStatus,
I.Quantity,
I.USDAmount,
I.InvestType,
I.MyselfAddress1,
I.MyselfAddress2,
I.MyselfCity,
I.MyselfState,
I.MyselfCountry,
I.MyselfZipCode,
I.PassPortNo,
I.SSNNo,
I.DrivingNo,
I.Comments,
I.InsertedDate,
I.InsertedBy
FROM Investors I with(nolock)
WHERE I.InvestorId <> 0 '
IF(@Search <> '')
BEGIN
SET @Query = @Query + ' AND (I.FirstName LIKE ''%' + @Search + '%'' OR I.LastName LIKE ''%' + @Search + '%'' OR I.PhoneNo LIKE ''%' + @Search + '%'' OR I.Email LIKE ''%' + @Search + '%'' OR I.InvestorNo LIKE ''%' + @Search + '%'' OR I.SSNNo LIKE ''%' + @Search + '%'' OR I.DrivingNo LIKE ''%' + @Search + '%'')'
END
--Set @Query = @Query + @Query;
IF(@Sort <> '')
BEGIN
SET @Query = @Query + ' ORDER BY ' + @Sort
END
EXEC sp_ExecuteSQL @Query;
print @Query;
SELECT @Total = 1;
--END TRY
--   BEGIN CATCH
--      SELECT @Total = -1;
--   END CATCH
END

GO
/****** Object:  StoredProcedure [dbo].[InvestorsFEFinalHomePageInitialLoad]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE [dbo].[InvestorsFEFinalHomePageInitialLoad]    
     
  @QStatus int output    
 
         
AS    
BEGIN    
    
 -- SET NOCOUNT ON added to prevent extra result sets from    
     
 SET NOCOUNT ON;    
     
 BEGIN TRY     
     
  
    
  /*====================================================================*/    
  /*         HomePage Engineered for Real-World Impact and Performance         */    
  /*====================================================================*/    
      
  SELECT * FROM PageDetails WHERE IsActive=1 AND dbo.RemoveSpecialChar(Heading, '^a-z0-9') = dbo.RemoveSpecialChar('InvestorsHomePage Scaling the World', '^a-z0-9')     
    
 

   /*====================================================================*/    
  /*       HomePage Transforming the Future with Robotics       */    
  /*====================================================================*/    
      
  SELECT * FROM PageDetails WHERE IsActive=1 AND dbo.RemoveSpecialChar(Heading, '^a-z0-9') = dbo.RemoveSpecialChar('InvestorsHomePage InvestersPartners', '^a-z0-9')     
    
    


   /*====================================================================*/    
  /*        HomePage Applications Use Cases       */    
  /*====================================================================*/    
      
  SELECT * FROM PageDetails WHERE IsActive=1 AND dbo.RemoveSpecialChar(Heading, '^a-z0-9') = dbo.RemoveSpecialChar('InvestorsHomePage JoinourNextChat', '^a-z0-9')     
    
   
 
 

      /*====================================================================*/    
  /*        Customers & Partners       */    
  /*====================================================================*/    
      
  SELECT * FROM PageDetails WHERE IsActive=1 AND dbo.RemoveSpecialChar(Heading, '^a-z0-9') = dbo.RemoveSpecialChar('InvestorsHomePage WhatCanbeDone', '^a-z0-9')     
    
   

   

      /*====================================================================*/    
  /*        Customers & Partners       */    
  /*====================================================================*/    
      
  SELECT * FROM PageDetails WHERE IsActive=1 AND dbo.RemoveSpecialChar(Heading, '^a-z0-9') = dbo.RemoveSpecialChar('InvestorsHomePage Jio RoboticsisinProduction', '^a-z0-9')     
    
   



   /*====================================================================*/    
  /*         App Info       */    
  /*====================================================================*/    
    
  Select  *  From AppInfo 
    
    
    
     
  SELECT @QStatus = 1;    
        
 END TRY    
        
    BEGIN CATCH      
       SELECT @QStatus = -1;    
    END CATCH     
END    
    

GO
/****** Object:  StoredProcedure [dbo].[InvestorsGetByEmail]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InvestorsGetByEmail]
	(@Email nvarchar(64),
     @QStatus int OUTPUT)    
AS
BEGIN
   SET NOCOUNT ON;

	BEGIN TRY 

		SELECT  CASE 
        WHEN FirstName IS NOT NULL AND LastName IS NOT NULL THEN FirstName + ' ' + LastName
        WHEN FirstName IS NOT NULL AND LastName IS NULL THEN FirstName
        WHEN FirstName IS NULL AND LastName IS NOT NULL THEN LastName
        ELSE ''
    END AS InvestorName,
	
	 * FROM Investors WHERE Email = @Email
		 
		SELECT @QStatus = 1;   -- Success
		 
	END TRY
    
	BEGIN CATCH
		SELECT @QStatus = -1;  -- Failure
	END CATCH	 
END

GO
/****** Object:  StoredProcedure [dbo].[InvestorsGetById]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InvestorsGetById]
@InvestorId BIGINT,
@QStatus INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
SELECT * FROM Investors WHERE InvestorId = @InvestorId;
SELECT @QStatus = 1;
END TRY
BEGIN CATCH
SELECT @QStatus = -1;
END CATCH
END

GO
/****** Object:  StoredProcedure [dbo].[InvestorsGetList]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InvestorsGetList]
@QStatus INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
SELECT * FROM Investors WHERE IsLockedOut = 0 ORDER BY FirstName ASC;
SELECT @QStatus = 1;
END TRY
BEGIN CATCH
SELECT @QStatus = -1;
END CATCH
END

GO
/****** Object:  StoredProcedure [dbo].[InvestorsGetListByVariable]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[InvestorsGetListByVariable]
(
@PageNo int,
@Items int,
@Search nvarchar(126),
@Total int output,
@Sort nvarchar(126)
)
as
begin
set nocount on;
declare @Start int, @End int;
declare @Query nvarchar(max);
create table #temp
(
Rid int primary key identity(1,1),
InvestorId bigint,
InvestorNo nvarchar(20),
FirstName nvarchar(256),
LastName nvarchar(256),
Email nvarchar(128),
PhoneNo nvarchar(64),
WhatsAppNo nvarchar(64),
Password nvarchar(128),
DateOfBirth datetime,
PassPortNo nvarchar(30),
SSNNo nvarchar(50),
DrivingNo nvarchar(50),
ProfileImage nvarchar(256),
InvestorStatus nvarchar(50),
Quantity nvarchar(128),
USDAmount nvarchar(128),
InvestType nvarchar(128),
LegalFirstName nvarchar(256),
LegalLastName nvarchar(256),
MyselfAddress1 nvarchar(2048),
MyselfAddress2 nvarchar(2048),
MyselfCity nvarchar(128),
MyselfState nvarchar(128),
MyselfCountry nvarchar(128),
MyselfZipCode nvarchar(64),
JiontHoldingType nvarchar(128),
PrimaryFirstName nvarchar(256),
PrimaryLastName nvarchar(256),
PrimaryAddress1 nvarchar(2048),
PrimaryAddress2 nvarchar(2048),
PrimaryCity nvarchar(128),
PrimaryState nvarchar(128),
PrimaryCountry nvarchar(128),
PrimaryZipCode nvarchar(64),
JiontFirstName nvarchar(256),
JiontLastName nvarchar(256),
JiontAddress1 nvarchar(2048),
JiontAddress2 nvarchar(2048),
JiontCity nvarchar(128),
JiontState nvarchar(128),
JiontCountry nvarchar(128),
JiontZipCode nvarchar(64),
JiontDateOfBirth datetime,
CorporationName nvarchar(512),
CorporationAddress1 nvarchar(2048),
CorporationAddress2 nvarchar(2048),
CorporationCity nvarchar(128),
CorporationState nvarchar(128),
CorporationCountry nvarchar(128),
CorporationZipCode nvarchar(64),
EmployeeIdentityNo nvarchar(64),
SigningPosition nvarchar(512),
SigningFirstName nvarchar(256),
SigningLastName nvarchar(256),
SigningAddress1 nvarchar(2048),
SigningAddress2 nvarchar(2048),
SigningCity nvarchar(128),
SigningState nvarchar(128),
SigningCountry nvarchar(128),
SigningZipCode nvarchar(64),
SigningDateOfBirth datetime,
SigningPhoneNo nvarchar(64),
BeneficialFirstName nvarchar(256),
BeneficialLastName nvarchar(256),
BeneficialAddress1 nvarchar(2048),
BeneficialAddress2 nvarchar(2048),
BeneficialCity nvarchar(128),
BeneficialState nvarchar(128),
BeneficialCountry nvarchar(128),
BeneficialZipCode nvarchar(64),
BeneficialDateOfBirth datetime,
BeneficialPhoneNo nvarchar(64),
TrustName nvarchar(512),
TrustDate datetime,
TrustAddress1 nvarchar(2048),
TrustAddress2 nvarchar(2048),
TrustCity nvarchar(128),
TrustState nvarchar(128),
TrustCountry nvarchar(128),
TrustZipCode nvarchar(64),
SigningTrusteeFirstName nvarchar(256),
SigningTrusteeLastName nvarchar(256),
SigningTrusteeAddress1 nvarchar(2048),
SigningTrusteeAddress2 nvarchar(2048),
SigningTrusteeCity nvarchar(128),
SigningTrusteeState nvarchar(128),
SigningTrusteeCountry nvarchar(128),
SigningTrusteeZipCode nvarchar(64),
SigningTrusteeDateOfBirth datetime,
SigningTrusteePhoneNo nvarchar(64),
IRADetails nvarchar(256),
IRAName nvarchar(512),
IRAEmail nvarchar(512),
IRAAddress1 nvarchar(2048),
IRAAddress2 nvarchar(2048),
IRACity nvarchar(128),
IRAState nvarchar(128),
IRACountry nvarchar(128),
IRAZipCode nvarchar(64),
IRAEmployeeNo nvarchar(64),
IRAAccountNo nvarchar(64),
IRACheckbox nvarchar(64),
IndividualFirstName nvarchar(512),
IndividualLastName nvarchar(512),
IndividualEmail nvarchar(512),
IndividualAddress1 nvarchar(2048),
IndividualAddress2 nvarchar(2048),
IndividualCity nvarchar(128),
IndividualState nvarchar(128),
IndividualCountry nvarchar(128),
IndividualZipCode nvarchar(64),
IndividualDateOfBirth datetime,
IndividualPhoneNo nvarchar(64),
InverstorConformation nvarchar(256),
FailedPasswordAttemptCount int,
IsLockedOut bit,
LastPasswordChangedDate datetime,
LastLoginDate datetime,
RegistrationDate datetime,
AcceptedDate datetime,
IPAddress nvarchar(45),
DeviceInfo nvarchar(256),
TermsConditions bit,
Comments nvarchar(2048),
InsertedDate datetime,
InsertedBy nvarchar(256),
UpdatedDate datetime,
UpdatedBy nvarchar(256)
);
select @Start = (@PageNo - 1) * @Items;
select @End   = @Start + @Items;
set @Query = 'insert into #temp
select
I.InvestorId,
I.InvestorNo,
I.FirstName,
I.LastName,
I.Email,
I.PhoneNo,
I.WhatsAppNo,
I.Password,
I.DateOfBirth,
I.PassPortNo,
I.SSNNo,
I.DrivingNo,
I.ProfileImage,
I.InvestorStatus,
I.Quantity,
I.USDAmount,
I.InvestType,
I.LegalFirstName,
I.LegalLastName,
I.MyselfAddress1,
I.MyselfAddress2,
I.MyselfCity,
I.MyselfState,
I.MyselfCountry,
I.MyselfZipCode,
I.JiontHoldingType,
I.PrimaryFirstName,
I.PrimaryLastName,
I.PrimaryAddress1,
I.PrimaryAddress2,
I.PrimaryCity,
I.PrimaryState,
I.PrimaryCountry,
I.PrimaryZipCode,
I.JiontFirstName,
I.JiontLastName,
I.JiontAddress1,
I.JiontAddress2,
I.JiontCity,
I.JiontState,
I.JiontCountry,
I.JiontZipCode,
I.JiontDateOfBirth,
I.CorporationName,
I.CorporationAddress1,
I.CorporationAddress2,
I.CorporationCity,
I.CorporationState,
I.CorporationCountry,
I.CorporationZipCode,
I.EmployeeIdentityNo,
I.SigningPosition,
I.SigningFirstName,
I.SigningLastName,
I.SigningAddress1,
I.SigningAddress2,
I.SigningCity,
I.SigningState,
I.SigningCountry,
I.SigningZipCode,
I.SigningDateOfBirth,
I.SigningPhoneNo,
I.BeneficialFirstName,
I.BeneficialLastName,
I.BeneficialAddress1,
I.BeneficialAddress2,
I.BeneficialCity,
I.BeneficialState,
I.BeneficialCountry,
I.BeneficialZipCode,
I.BeneficialDateOfBirth,
I.BeneficialPhoneNo,
I.TrustName,
I.TrustDate,
I.TrustAddress1,
I.TrustAddress2,
I.TrustCity,
I.TrustState,
I.TrustCountry,
I.TrustZipCode,
I.SigningTrusteeFirstName,
I.SigningTrusteeLastName,
I.SigningTrusteeAddress1,
I.SigningTrusteeAddress2,
I.SigningTrusteeCity,
I.SigningTrusteeState,
I.SigningTrusteeCountry,
I.SigningTrusteeZipCode,
I.SigningTrusteeDateOfBirth,
I.SigningTrusteePhoneNo,
I.IRADetails,
I.IRAName,
I.IRAEmail,
I.IRAAddress1,
I.IRAAddress2,
I.IRACity,
I.IRAState,
I.IRACountry,
I.IRAZipCode,
I.IRAEmployeeNo,
I.IRAAccountNo,
I.IRACheckbox,
I.IndividualFirstName,
I.IndividualLastName,
I.IndividualEmail,
I.IndividualAddress1,
I.IndividualAddress2,
I.IndividualCity,
I.IndividualState,
I.IndividualCountry,
I.IndividualZipCode,
I.IndividualDateOfBirth,
I.IndividualPhoneNo,
I.InverstorConformation,
I.FailedPasswordAttemptCount,
I.IsLockedOut,
I.LastPasswordChangedDate,
I.LastLoginDate,
I.RegistrationDate,
I.AcceptedDate,
I.IPAddress,
I.DeviceInfo,
I.TermsConditions,
I.Comments,
I.InsertedDate,
I.InsertedBy,
I.UpdatedDate,
I.UpdatedBy
FROM Investors I
WHERE I.InvestorId <> 0';
if (@Search <> '')
begin
set @Query = @Query + ' and (I.FirstName like ''%' + @Search + '%'' or I.InvestorNo like ''%' + @Search + '%''
or I.SSNNo like ''%' + @Search + '%''
or I.LastName like ''%' + @Search + '%''
or I.Email like ''%' + @Search + '%''
or I.PhoneNo like ''%' + @Search + '%''
)';
end
if (@Sort <> '')
begin
set @Query = @Query + ' order by ' + @Sort;
end
exec sp_executesql @Query;
select @Total = count(Rid) from #temp;
select * from #temp where Rid > @Start and Rid <= @End;
drop table #temp;
end

GO
/****** Object:  StoredProcedure [dbo].[InvestorsGetPassword]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[InvestorsGetPassword]
@InvestorId int,
@QStatus int output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
BEGIN TRY
SELECT Password FROM Investors WHERE InvestorId = @InvestorId
SELECT @QStatus = 1
END TRY
BEGIN CATCH
SELECT @QStatus = -1
END CATCH
END

GO
/****** Object:  StoredProcedure [dbo].[InvestorsGetSSNNO]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[InvestorsGetSSNNO]
@SSNNO nvarchar(64),
@QStatus INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
SELECT * FROM Investors WHERE SSNNo = @SSNNO;
SELECT @QStatus = 1;
END TRY
BEGIN CATCH
SELECT @QStatus = -1;
END CATCH
END

GO
/****** Object:  StoredProcedure [dbo].[InvestorsInsert]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InvestorsInsert]
@InvestorId BIGINT,
@InvestorNo NVARCHAR(20),
@FirstName NVARCHAR(256),
@LastName NVARCHAR(256),
@Email NVARCHAR(128),
@PhoneNo NVARCHAR(64),
@WhatsAppNo NVARCHAR(64),
@Password NVARCHAR(256),
@DateOfBirth DATETIME,
@PassPortNo NVARCHAR(30),
@SSNNo NVARCHAR(50),
@DrivingNo NVARCHAR(50),
@ProfileImage NVARCHAR(256) OUTPUT,
@InvestorStatus NVARCHAR(50),
@Quantity NVARCHAR(128),
@USDAmount NVARCHAR(128),
@InvestType NVARCHAR(128),
@LegalFirstName NVARCHAR(256),
@LegalLastName NVARCHAR(256),
@MyselfAddress1 NVARCHAR(2048),
@MyselfAddress2 NVARCHAR(2048),
@MyselfCity NVARCHAR(128),
@MyselfState NVARCHAR(128),
@MyselfCountry NVARCHAR(128),
@MyselfZipCode NVARCHAR(64),
@JiontHoldingType NVARCHAR(128),
@PrimaryFirstName NVARCHAR(256),
@PrimaryLastName NVARCHAR(256),
@PrimaryAddress1 NVARCHAR(2048),
@PrimaryAddress2 NVARCHAR(2048),
@PrimaryCity NVARCHAR(128),
@PrimaryState NVARCHAR(128),
@PrimaryCountry NVARCHAR(128),
@PrimaryZipCode NVARCHAR(64),
@JiontFirstName NVARCHAR(256),
@JiontLastName NVARCHAR(256),
@JiontAddress1 NVARCHAR(2048),
@JiontAddress2 NVARCHAR(2048),
@JiontCity NVARCHAR(128),
@JiontState NVARCHAR(128),
@JiontCountry NVARCHAR(128),
@JiontZipCode NVARCHAR(64),
@JiontDateOfBirth DATETIME,
@CorporationName NVARCHAR(512),
@CorporationAddress1 NVARCHAR(2048),
@CorporationAddress2 NVARCHAR(2048),
@CorporationCity NVARCHAR(128),
@CorporationState NVARCHAR(128),
@CorporationCountry NVARCHAR(128),
@CorporationZipCode NVARCHAR(64),
@EmployeeIdentityNo NVARCHAR(64),
@SigningPosition NVARCHAR(512),
@SigningFirstName NVARCHAR(256),
@SigningLastName NVARCHAR(256),
@SigningAddress1 NVARCHAR(2048),
@SigningAddress2 NVARCHAR(2048),
@SigningCity NVARCHAR(128),
@SigningState NVARCHAR(128),
@SigningCountry NVARCHAR(128),
@SigningZipCode NVARCHAR(64),
@SigningDateOfBirth DATETIME,
@SigningPhoneNo NVARCHAR(64),
@BeneficialFirstName NVARCHAR(256),
@BeneficialLastName NVARCHAR(256),
@BeneficialAddress1 NVARCHAR(2048),
@BeneficialAddress2 NVARCHAR(2048),
@BeneficialCity NVARCHAR(128),
@BeneficialState NVARCHAR(128),
@BeneficialCountry NVARCHAR(128),
@BeneficialZipCode NVARCHAR(64),
@BeneficialDateOfBirth DATETIME,
@BeneficialPhoneNo NVARCHAR(64),
@TrustName NVARCHAR(512),
@TrustDate DATETIME,
@TrustAddress1 NVARCHAR(2048),
@TrustAddress2 NVARCHAR(2048),
@TrustCity NVARCHAR(128),
@TrustState NVARCHAR(128),
@TrustCountry NVARCHAR(128),
@TrustZipCode NVARCHAR(64),
@SigningTrusteeFirstName NVARCHAR(256),
@SigningTrusteeLastName NVARCHAR(256),
@SigningTrusteeAddress1 NVARCHAR(2048),
@SigningTrusteeAddress2 NVARCHAR(2048),
@SigningTrusteeCity NVARCHAR(128),
@SigningTrusteeState NVARCHAR(128),
@SigningTrusteeCountry NVARCHAR(128),
@SigningTrusteeZipCode NVARCHAR(64),
@SigningTrusteeDateOfBirth DATETIME,
@SigningTrusteePhoneNo NVARCHAR(64),
@IRADetails NVARCHAR(256),
@IRAName NVARCHAR(512),
@IRAEmail NVARCHAR(512),
@IRAAddress1 NVARCHAR(2048),
@IRAAddress2 NVARCHAR(2048),
@IRACity NVARCHAR(128),
@IRAState NVARCHAR(128),
@IRACountry NVARCHAR(128),
@IRAZipCode NVARCHAR(64),
@IRAEmployeeNo NVARCHAR(64),
@IRAAccountNo NVARCHAR(64),
@IRACheckbox NVARCHAR(64),
@IndividualFirstName NVARCHAR(512),
@IndividualLastName NVARCHAR(512),
@IndividualEmail NVARCHAR(512),
@IndividualAddress1 NVARCHAR(2048),
@IndividualAddress2 NVARCHAR(2048),
@IndividualCity NVARCHAR(128),
@IndividualState NVARCHAR(128),
@IndividualCountry NVARCHAR(128),
@IndividualZipCode NVARCHAR(64),
@IndividualDateOfBirth DATETIME,
@IndividualPhoneNo NVARCHAR(64),
@InverstorConformation NVARCHAR(256),
@FailedPasswordAttemptCount INT,
@IsLockedOut BIT,
@LastPasswordChangedDate DATETIME,
@LastLoginDate DATETIME,
@RegistrationDate DATETIME,
@AcceptedDate DATETIME,
@IPAddress NVARCHAR(45),
@DeviceInfo NVARCHAR(256),
@TermsConditions BIT,
@Comments NVARCHAR(2048),
@InsertedBy NVARCHAR(256),
@UpdatedBy NVARCHAR(256),
@QStatus INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION
-- Auto-generate InvestorNo if not provided
IF (@InvestorNo IS NULL OR LTRIM(RTRIM(@InvestorNo)) = '')
BEGIN
DECLARE @MaxNum INT
SELECT @MaxNum = MAX(CAST(RIGHT(InvestorNo, 4) AS INT))
FROM Investors
WHERE InvestorNo LIKE 'INV[0-9][0-9][0-9][0-9]'
IF @MaxNum IS NULL
SET @MaxNum = 0
SET @InvestorNo = 'INV' + RIGHT('000' + CAST(@MaxNum + 1 AS NVARCHAR(4)), 4)
END
--and IPAddress=@IPAddress
IF ((SELECT COUNT(*) FROM Investors WHERE Email = @Email ) = 0)
BEGIN
INSERT INTO Investors
(
InvestorNo, FirstName, LastName, Email, PhoneNo, WhatsAppNo, Password, DateOfBirth, PassPortNo, SSNNo, DrivingNo, ProfileImage,
InvestorStatus, Quantity, USDAmount, InvestType, LegalFirstName, LegalLastName, MyselfAddress1, MyselfAddress2, MyselfCity, MyselfState, MyselfCountry, MyselfZipCode,
JiontHoldingType, PrimaryFirstName, PrimaryLastName, PrimaryAddress1, PrimaryAddress2, PrimaryCity, PrimaryState, PrimaryCountry, PrimaryZipCode,
JiontFirstName, JiontLastName, JiontAddress1, JiontAddress2, JiontCity, JiontState, JiontCountry, JiontZipCode, JiontDateOfBirth,
CorporationName, CorporationAddress1, CorporationAddress2, CorporationCity, CorporationState, CorporationCountry, CorporationZipCode,
EmployeeIdentityNo, SigningPosition, SigningFirstName, SigningLastName, SigningAddress1, SigningAddress2, SigningCity, SigningState, SigningCountry, SigningZipCode, SigningDateOfBirth,
SigningPhoneNo, BeneficialFirstName, BeneficialLastName, BeneficialAddress1, BeneficialAddress2, BeneficialCity, BeneficialState, BeneficialCountry, BeneficialZipCode, BeneficialDateOfBirth,
BeneficialPhoneNo, TrustName, TrustDate, TrustAddress1, TrustAddress2, TrustCity, TrustState, TrustCountry, TrustZipCode,
SigningTrusteeFirstName, SigningTrusteeLastName, SigningTrusteeAddress1, SigningTrusteeAddress2, SigningTrusteeCity, SigningTrusteeState, SigningTrusteeCountry, SigningTrusteeZipCode, SigningTrusteeDateOfBirth,
SigningTrusteePhoneNo, IRADetails, IRAName, IRAEmail, IRAAddress1, IRAAddress2, IRACity, IRAState, IRACountry, IRAZipCode,
IRAEmployeeNo, IRAAccountNo, IRACheckbox, IndividualFirstName, IndividualLastName, IndividualEmail, IndividualAddress1, IndividualAddress2, IndividualCity, IndividualState, IndividualCountry, IndividualZipCode, IndividualDateOfBirth,
IndividualPhoneNo, InverstorConformation, FailedPasswordAttemptCount, IsLockedOut, LastPasswordChangedDate,
LastLoginDate, RegistrationDate, AcceptedDate, IPAddress, DeviceInfo, TermsConditions, Comments,
InsertedDate, InsertedBy, UpdatedDate, UpdatedBy
)
VALUES
(
@InvestorNo, @FirstName, @LastName, @Email, @PhoneNo, @WhatsAppNo, @Password, @DateOfBirth, @PassPortNo, @SSNNo, @DrivingNo, @ProfileImage,
@InvestorStatus, @Quantity, @USDAmount, @InvestType, @LegalFirstName, @LegalLastName, @MyselfAddress1, @MyselfAddress2, @MyselfCity, @MyselfState, @MyselfCountry, @MyselfZipCode,
@JiontHoldingType, @PrimaryFirstName, @PrimaryLastName, @PrimaryAddress1, @PrimaryAddress2, @PrimaryCity, @PrimaryState, @PrimaryCountry, @PrimaryZipCode,
@JiontFirstName, @JiontLastName, @JiontAddress1, @JiontAddress2, @JiontCity, @JiontState, @JiontCountry, @JiontZipCode, @JiontDateOfBirth,
@CorporationName, @CorporationAddress1, @CorporationAddress2, @CorporationCity, @CorporationState, @CorporationCountry, @CorporationZipCode,
@EmployeeIdentityNo, @SigningPosition, @SigningFirstName, @SigningLastName, @SigningAddress1, @SigningAddress2, @SigningCity, @SigningState, @SigningCountry, @SigningZipCode, @SigningDateOfBirth,
@SigningPhoneNo, @BeneficialFirstName, @BeneficialLastName, @BeneficialAddress1, @BeneficialAddress2, @BeneficialCity, @BeneficialState, @BeneficialCountry, @BeneficialZipCode, @BeneficialDateOfBirth,
@BeneficialPhoneNo, @TrustName, @TrustDate, @TrustAddress1, @TrustAddress2, @TrustCity, @TrustState, @TrustCountry, @TrustZipCode,
@SigningTrusteeFirstName, @SigningTrusteeLastName, @SigningTrusteeAddress1, @SigningTrusteeAddress2, @SigningTrusteeCity, @SigningTrusteeState, @SigningTrusteeCountry, @SigningTrusteeZipCode, @SigningTrusteeDateOfBirth,
@SigningTrusteePhoneNo, @IRADetails, @IRAName, @IRAEmail, @IRAAddress1, @IRAAddress2, @IRACity, @IRAState, @IRACountry, @IRAZipCode,
@IRAEmployeeNo, @IRAAccountNo, @IRACheckbox, @IndividualFirstName, @IndividualLastName, @IndividualEmail, @IndividualAddress1, @IndividualAddress2, @IndividualCity, @IndividualState, @IndividualCountry, @IndividualZipCode, @IndividualDateOfBirth,
@IndividualPhoneNo, @InverstorConformation, @FailedPasswordAttemptCount, @IsLockedOut, @LastPasswordChangedDate,
@LastLoginDate, @RegistrationDate, @AcceptedDate, @IPAddress, @DeviceInfo, @TermsConditions, @Comments,
CONVERT(DATETIME, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')),
@InsertedBy,
CONVERT(DATETIME, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')),
@UpdatedBy
)
SET @InvestorId = SCOPE_IDENTITY()
SELECT @QStatus = 1
END
ELSE
BEGIN
UPDATE Investors
SET InvestorNo = @InvestorNo,
FirstName = @FirstName,
LastName = @LastName,
Email = @Email,
PhoneNo = @PhoneNo,
WhatsAppNo = @WhatsAppNo,
Password = @Password,
DateOfBirth = @DateOfBirth,
PassPortNo = @PassPortNo,
DrivingNo = @DrivingNo,
Quantity = @Quantity,
USDAmount = @USDAmount,
InvestType = @InvestType,
LegalFirstName = @LegalFirstName,
LegalLastName = @LegalLastName,
MyselfAddress1 = @MyselfAddress1,
MyselfAddress2 = @MyselfAddress2,
MyselfCity = @MyselfCity,
MyselfState = @MyselfState,
MyselfCountry = @MyselfCountry,
MyselfZipCode = @MyselfZipCode,
JiontHoldingType = @JiontHoldingType,
PrimaryFirstName = @PrimaryFirstName,
PrimaryLastName = @PrimaryLastName,
PrimaryAddress1 = @PrimaryAddress1,
PrimaryAddress2 = @PrimaryAddress2,
PrimaryCity = @PrimaryCity,
PrimaryState = @PrimaryState,
PrimaryCountry = @PrimaryCountry,
PrimaryZipCode = @PrimaryZipCode,
JiontFirstName = @JiontFirstName,
JiontLastName = @JiontLastName,
JiontAddress1 = @JiontAddress1,
JiontAddress2 = @JiontAddress2,
JiontCity = @JiontCity,
JiontState = @JiontState,
JiontCountry = @JiontCountry,
JiontZipCode = @JiontZipCode,
JiontDateOfBirth = @JiontDateOfBirth,
CorporationName = @CorporationName,
CorporationAddress1 = @CorporationAddress1,
CorporationAddress2 = @CorporationAddress2,
CorporationCity = @CorporationCity,
CorporationState = @CorporationState,
CorporationCountry = @CorporationCountry,
CorporationZipCode = @CorporationZipCode,
EmployeeIdentityNo = @EmployeeIdentityNo,
SigningPosition = @SigningPosition,
SigningFirstName = @SigningFirstName,
SigningLastName = @SigningLastName,
SigningAddress1 = @SigningAddress1,
SigningAddress2 = @SigningAddress2,
SigningCity = @SigningCity,
SigningState = @SigningState,
SigningCountry = @SigningCountry,
SigningZipCode = @SigningZipCode,
SigningDateOfBirth = @SigningDateOfBirth,
SigningPhoneNo = @SigningPhoneNo,
BeneficialFirstName = @BeneficialFirstName,
BeneficialLastName = @BeneficialLastName,
BeneficialAddress1 = @BeneficialAddress1,
BeneficialAddress2 = @BeneficialAddress2,
BeneficialCity = @BeneficialCity,
BeneficialState = @BeneficialState,
BeneficialCountry = @BeneficialCountry,
BeneficialZipCode = @BeneficialZipCode,
BeneficialDateOfBirth = @BeneficialDateOfBirth,
BeneficialPhoneNo = @BeneficialPhoneNo,
TrustName = @TrustName,
TrustDate = @TrustDate,
TrustAddress1 = @TrustAddress1,
TrustAddress2 = @TrustAddress2,
TrustCity = @TrustCity,
TrustState = @TrustState,
TrustCountry = @TrustCountry,
TrustZipCode = @TrustZipCode,
SigningTrusteeFirstName = @SigningTrusteeFirstName,
SigningTrusteeLastName = @SigningTrusteeLastName,
SigningTrusteeAddress1 = @SigningTrusteeAddress1,
SigningTrusteeAddress2 = @SigningTrusteeAddress2,
SigningTrusteeCity = @SigningTrusteeCity,
SigningTrusteeState = @SigningTrusteeState,
SigningTrusteeCountry = @SigningTrusteeCountry,
SigningTrusteeZipCode = @SigningTrusteeZipCode,
SigningTrusteeDateOfBirth = @SigningTrusteeDateOfBirth,
SigningTrusteePhoneNo = @SigningTrusteePhoneNo,
IRADetails = @IRADetails,
IRAName = @IRAName,
IRAEmail = @IRAEmail,
IRAAddress1 = @IRAAddress1,
IRAAddress2 = @IRAAddress2,
IRACity = @IRACity,
IRAState = @IRAState,
IRACountry = @IRACountry,
IRAZipCode = @IRAZipCode,
IRAEmployeeNo = @IRAEmployeeNo,
IRAAccountNo = @IRAAccountNo,
IRACheckbox = @IRACheckbox,
IndividualFirstName = @IndividualFirstName,
IndividualLastName = @IndividualLastName,
IndividualEmail = @IndividualEmail,
IndividualAddress1 = @IndividualAddress1,
IndividualAddress2 = @IndividualAddress2,
IndividualCity = @IndividualCity,
IndividualState = @IndividualState,
IndividualCountry = @IndividualCountry,
IndividualZipCode = @IndividualZipCode,
IndividualDateOfBirth = @IndividualDateOfBirth,
IndividualPhoneNo = @IndividualPhoneNo,
InverstorConformation = @InverstorConformation,
FailedPasswordAttemptCount = @FailedPasswordAttemptCount,
IsLockedOut = @IsLockedOut,
LastPasswordChangedDate = @LastPasswordChangedDate,
LastLoginDate = @LastLoginDate,
RegistrationDate = @RegistrationDate,
AcceptedDate = @AcceptedDate,
IPAddress = @IPAddress,
DeviceInfo = @DeviceInfo,
TermsConditions = @TermsConditions,
Comments = @Comments,
UpdatedDate = CONVERT(DATETIME, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')),
UpdatedBy = @UpdatedBy
WHERE InvestorId = @InvestorId
SELECT @QStatus = 2
END

IF (LEFT(@ProfileImage, 1) = '.' OR @ProfileImage <> 'NA')
BEGIN
IF (@ProfileImage <> 'NA')
BEGIN
UPDATE Investors
SET ProfileImage = CAST(@InvestorId AS NVARCHAR(12)) + '-' + dbo.RemoveSpecialChar(@FirstName, '^a-z0-9') + @ProfileImage
WHERE InvestorId = @InvestorId
SELECT @ProfileImage = CAST(@InvestorId AS NVARCHAR(12)) + '-' + dbo.RemoveSpecialChar(@FirstName, '^a-z0-9') + @ProfileImage
END
ELSE
BEGIN
SELECT @ProfileImage = ''
END
END
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SELECT @QStatus = -1
END CATCH
END

GO
/****** Object:  StoredProcedure [dbo].[InvestorStatusDelete]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InvestorStatusDelete]
    (@Id bigint
    ,@QStatus int output)  
        
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    
    --BEGIN TRY    
    BEGIN TRANSACTION
    
			DELETE FROM InvestorStatus WHERE Id = @Id
		
		SELECT @QStatus = 1;
	
	COMMIT TRANSACTION	
   -- END TRY
   -- BEGIN CATCH
    
   --      ROLLBACK TRANSACTION
		 --SELECT @QStatus = -1;
		 
   -- END CATCH
    	
END



GO
/****** Object:  StoredProcedure [dbo].[InvestorStatusGetById]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[InvestorStatusGetById]
	(@Id bigint
    ,@QStatus int OUTPUT)    
        
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.	
   SET NOCOUNT ON;

    -- Insert statements for procedure here

    BEGIN TRY     
		 SELECT * FROM InvestorStatus  WHERE Id  = @Id
		 SELECT @QStatus = 1;		 
    END TRY
    
    BEGIN CATCH    
		 SELECT @QStatus = -1;		 
    END CATCH	
	 
END


GO
/****** Object:  StoredProcedure [dbo].[InvestorStatusGetList]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InvestorStatusGetList]
    @QStatus int output
        
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    
    BEGIN TRY 
       
		SELECT * FROM InvestorStatus where IsActive=1 Order by OrderNo Asc	
		SELECT @QStatus = 1;
			
    END TRY
    BEGIN CATCH
    
		 SELECT @QStatus = -1;
		 
    END CATCH
    	
END


GO
/****** Object:  StoredProcedure [dbo].[InvestorStatusGetListByVariable]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InvestorStatusGetListByVariable]
	 (@PageNo int
     ,@Items int
     ,@Search nvarchar(126)
     ,@Total int output
     ,@Sort nvarchar(126))
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.	
	SET NOCOUNT ON;
	
	DECLARE @Start int, @End INT ;
	DECLARE @Query nvarchar(max);
	
    -- Insert statements for procedure here
    
    CREATE TABLE #temp(Rid INT PRIMARY KEY IDENTITY(1,1)
				,Id bigint 
				,Name varchar(50)
				,OrderNo bigint
				,IsActive bit
				,UpdatedBy nvarchar(256)
				,UpdatedDate datetime
				)
	
	SELECT @Start = (@PageNo - 1) * @Items  
	   
	SELECT @End = @Start + @Items 
	
	SET @Query = 'INSERT INTO #temp 
		 SELECT  SL.Id 
				,SL.Name 
				,SL.OrderNo
				,SL.IsActive
				,SL.UpdatedBy 
				,SL.UpdatedDate 
		FROM InvestorStatus SL  WHERE SL.Id <> 0 '
	
	
	IF(@Search <> '')
	
    BEGIN
		SET @Query = @Query + ' AND  (SL.Name LIKE ''%' + @Search + '%'')'
    END
	
	IF(@Sort <> '')
	
	BEGIN
		SET @Query = @Query + ' ORDER BY ' + @Sort
	END
	
	 	
	EXEC sp_ExecuteSQL @Query;
	
	SELECT @Total = COUNT(Rid) from #temp 
			
	SELECT * FROM #temp WHERE Rid > @Start AND  Rid <= @End
	
	  DROP TABLE #temp

END


GO
/****** Object:  StoredProcedure [dbo].[InvestorStatusInsert]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InvestorStatusInsert]
    (@Id bigint 
	,@Name Nvarchar(256)	
	,@IsActive bit
	,@UpdatedBy nvarchar(50)
	,@UpdatedDate datetime
	,@QStatus int output)  
        
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.	
	SET NOCOUNT ON;
    -- Insert statements for procedure here
    
   -- BEGIN TRY
   -- BEGIN TRANSACTION
    
		 IF((SELECT COUNT(*) FROM InvestorStatus WHERE Id = @Id) = 0)
		BEGIN
			INSERT INTO InvestorStatus VALUES
			        (@Name	
					,0 
	                ,@IsActive
	                ,@UpdatedDate
					,@UpdatedBy
					)
					
					Set @Id = SCOPE_IDENTITY()

		SELECT @QStatus = 1;	
		END
		ELSE BEGIN
		
			UPDATE InvestorStatus
			   SET 
				   Name=@Name
				  ,UpdatedBy=@UpdatedBy
				  ,UpdatedDate=@UpdatedDate
			 WHERE  Id = @Id
			 		 
			 SELECT @QStatus = 2;
			 
		 END
		 
		
	--COMMIT TRANSACTION
	--END TRY
    
 --   BEGIN CATCH     
	--   ROLLBACK TRANSACTION 
 --      SELECT @QStatus = -1;           
 --   END CATCH   
    
END


GO
/****** Object:  StoredProcedure [dbo].[InvestorStatusUpdateOrderBy]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InvestorStatusUpdateOrderBy]
    (@Id bigint
    ,@OrderNo int
    ,@QStatus int output)  
        
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    
    BEGIN TRY    
    BEGIN TRANSACTION
    
			UPDATE InvestorStatus SET OrderNo = @OrderNo WHERE Id=@Id
		
		 SELECT @QStatus = 1;	
	COMMIT TRANSACTION
    END TRY

    BEGIN CATCH    
         ROLLBACK TRANSACTION
		 SELECT @QStatus = -1;		 
    END CATCH
    	
END
























GO
/****** Object:  StoredProcedure [dbo].[InvestorStatusUpdateStatus]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[InvestorStatusUpdateStatus]
    (@Id bigint
    ,@QStatus int output)  
        
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.	
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    
    BEGIN TRY    
    BEGIN TRANSACTION
    
		IF((SELECT IsActive FROM InvestorStatus WHERE Id = @Id) = 1)
		BEGIN
			UPDATE InvestorStatus SET IsActive = 0 WHERE Id = @Id
		END
		ELSE
		BEGIN
			UPDATE InvestorStatus SET IsActive = 1 WHERE Id = @Id
		END		
		
		SELECT @QStatus = 1;
		
	COMMIT TRANSACTION
    END TRY

    BEGIN CATCH    
         ROLLBACK TRANSACTION
		 SELECT @QStatus = -1;		 
    END CATCH
    	
END



GO
/****** Object:  StoredProcedure [dbo].[InvestorsUpdateStatus]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[InvestorsUpdateStatus]
    (@InvestorId bigint
    ,@QStatus int output
	,@Status nvarchar(256)
	,@UpdatedBy nvarchar(256))  
        
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    
    BEGIN TRY    
    BEGIN TRANSACTION
    
		IF(@Status <> '')
		BEGIN
			UPDATE Investors SET InvestorStatus = @Status,UpdatedDate=GetDate(),UpdatedBy=@UpdatedBy WHERE InvestorId = @InvestorId
		
		END			
		
		SELECT @QStatus = 1;
		
	COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
    
         ROLLBACK TRANSACTION
		 SELECT @QStatus = -1;
		 
    END CATCH
    	
END







GO
/****** Object:  StoredProcedure [dbo].[InvestorWireGetListByVariable]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InvestorWireGetListByVariable]
    @PageNo INT,
    @Items INT,
    @InvestorId BIGINT,
    @Search NVARCHAR(126),
    @Total INT OUTPUT,
    @Sort NVARCHAR(126)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Start INT, @End INT;
    DECLARE @Query NVARCHAR(MAX);

    CREATE TABLE #temp(
        Rid INT PRIMARY KEY IDENTITY(1,1),
        Id BIGINT,
        InvestorId BIGINT,
        MOUNumber NVARCHAR(50),
        DocumentUrl NVARCHAR(512),
        MOUStatus NVARCHAR(50),
        SubmittedDate DATETIME,
        ReviewedDate DATETIME,
        ApprovedDate DATETIME,
        RejectionReason NVARCHAR(1024),
        AdminComments NVARCHAR(1024),
        InvestorComments NVARCHAR(1024),
        IsActive BIT,
        InsertedDate DATETIME,
        InsertedBy NVARCHAR(256),
        UpdatedDate DATETIME,
        UpdatedBy NVARCHAR(256)
    );

    SELECT @Start = (@PageNo - 1) * @Items;
    SELECT @End = @Start + @Items;

    SET @Query = N'
        INSERT INTO #temp
      SELECT
    C.Id,
    C.InvestorId,
    C.MOUNumber,
    C.DocumentUrl,
    C.MOUStatus,
    C.SubmittedDate,
    C.ReviewedDate,
    C.ApprovedDate,
    C.RejectionReason,
    C.AdminComments,
    C.InvestorComments,
    C.IsActive,
    C.InsertedDate,
    C.InsertedBy,
    C.UpdatedDate,
    C.UpdatedBy
FROM InvestorMOU C
INNER JOIN Investors S ON S.InvestorId = C.InvestorId
WHERE C.Id <> 0 
    AND (C.InvestorComments = ''WireTransfer'')
    ';

    SET @Query = @Query + N' AND (
		C.MOUNumber LIKE ''%' + REPLACE(@Search, '''', '''''') + '%'' 
		OR C.MOUStatus LIKE ''%' + REPLACE(@Search, '''', '''''') + '%''
		OR S.PhoneNo LIKE ''%' + REPLACE(@Search, '''', '''''') + '%''
	)';


    IF(@InvestorId <> 0)
        SET @Query = @Query + ' AND C.InvestorId = ' + CAST(@InvestorId AS NVARCHAR(20));

    IF(@Sort <> '')
        SET @Query = @Query + ' ORDER BY ' + @Sort;

    EXEC sp_executesql @Query;
	print @Query;

    SELECT @Total = COUNT(Rid) FROM #temp;

    SELECT * FROM #temp WHERE Rid > @Start AND Rid <= @End;

    DROP TABLE #temp;
END



GO
/****** Object:  StoredProcedure [dbo].[InvestorWireInsert]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[InvestorWireInsert]
    @Id BIGINT,
    @InvestorId BIGINT,
  --  @MOUNumber NVARCHAR(50),
    @DocumentUrl NVARCHAR(512) OUTPUT,
 --   @MOUStatus NVARCHAR(50),
  --  @SubmittedDate DATETIME,
  --  @ReviewedDate DATETIME,
  --  @ApprovedDate DATETIME,
  --  @RejectionReason NVARCHAR(1024),
  --  @AdminComments NVARCHAR(1024),
  --  @InvestorComments NVARCHAR(1024),
  --  @IsActive BIT,
    @InsertedBy NVARCHAR(256),
   -- @InsertedDate DATETIME,
    @UpdatedBy NVARCHAR(256),
   -- @UpdatedDate DATETIME,
    @QStatus INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;


		Declare  @MOUNumber NVARCHAR(50);

      -- Auto-generate MOUNumber if not provided
		IF (@MOUNumber IS NULL OR LTRIM(RTRIM(@MOUNumber)) = '')
		BEGIN
			DECLARE @MaxNum INT

			SELECT @MaxNum = 
				MAX(CAST(RIGHT(MOUNumber, 4) AS INT))
			FROM InvestorMOU
			WHERE MOUNumber LIKE 'CUS[0-9][0-9][0-9][0-9]'

			IF @MaxNum IS NULL
				SET @MaxNum = 0

			SET @MOUNumber = 'CUS' + RIGHT('000' + CAST(@MaxNum + 1 AS NVARCHAR(4)), 4)
		END


        IF((SELECT COUNT(*) FROM InvestorMOU WHERE Id = @Id) = 0)
        BEGIN
            INSERT INTO InvestorMOU
            (
                InvestorId, MOUNumber, DocumentUrl, MOUStatus,
                SubmittedDate, ReviewedDate, ApprovedDate, RejectionReason,
                AdminComments, InvestorComments, IsActive,
                InsertedDate, InsertedBy, UpdatedDate, UpdatedBy
            )
            VALUES
            (
                @InvestorId, @MOUNumber, NULL, NULL,
                CONVERT(DATETIME, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')), NULL, NULL, NULL,
                NULL, 'WireTransfer', 1,
                CONVERT(DATETIME, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')),
                @InsertedBy,
                CONVERT(DATETIME, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')),
                @UpdatedBy
            );

            SET @Id = SCOPE_IDENTITY();
            SELECT @QStatus = 1;
        END
        ELSE
        BEGIN
            UPDATE InvestorMOU
            SET
                InvestorId = @InvestorId,
               -- MOUNumber = @MOUNumber,
               -- MOUStatus = @MOUStatus,
               --- SubmittedDate = @SubmittedDate,
               -- ReviewedDate = @ReviewedDate,
               -- ApprovedDate = @ApprovedDate,
               -- RejectionReason = @RejectionReason,
             --   AdminComments = @AdminComments,
             --   InvestorComments = @InvestorComments,
               -- IsActive = @IsActive,
                UpdatedDate = CONVERT(DATETIME, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')),
                UpdatedBy = @UpdatedBy
            WHERE Id = @Id;

            SELECT @QStatus = 2;
        END

        -- Document URL naming logic
        IF(@DocumentUrl IS NOT NULL AND @DocumentUrl <> 'NA')
        BEGIN
            UPDATE InvestorMOU
            SET DocumentUrl = CAST(@Id AS NVARCHAR(12)) + '-' + dbo.RemoveSpecialChar(@MOUNumber, '^a-z0-9') + @DocumentUrl
            WHERE Id = @Id;

            SELECT @DocumentUrl = CAST(@Id AS NVARCHAR(12)) + '-' + dbo.RemoveSpecialChar(@MOUNumber, '^a-z0-9') + @DocumentUrl;
        END
        ELSE
        BEGIN
            SELECT @DocumentUrl = '';
        END

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        SELECT @QStatus = -1;
    END CATCH
END



GO
/****** Object:  StoredProcedure [dbo].[MailTemplatesDelete]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[MailTemplatesDelete]
    (@MailTemplateId bigint
    ,@QStatus int output )  
        
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.	
	SET NOCOUNT ON;
    -- Insert statements for procedure here
    
    BEGIN TRY  
    BEGIN TRANSACTION  
		DELETE FROM MailTemplate WHERE MailTemplateId = @MailTemplateId		
		SELECT @QStatus = 1;

		COMMIT TRANSACTION		
    END TRY
    BEGIN CATCH
    ROLLBACK TRANSACTION
		 SELECT @QStatus = -1;
    END CATCH
    	
END














GO
/****** Object:  StoredProcedure [dbo].[MailTemplatesGetById]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[MailTemplatesGetById]
	(@MailTemplateId bigint
	,@TemplateName nvarchar(256)
    ,@QStatus int OUTPUT)    
        
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.	
   SET NOCOUNT ON;
    -- Insert statements for procedure here

    BEGIN TRY 
     IF(@MailTemplateId!=0)
	   BEGIN
		 SELECT * FROM MailTemplate WHERE MailTemplateId  = @MailTemplateId
		 SELECT @QStatus = 1;
		END
		ELSE IF(@TemplateName!='')
		BEGIN
			SELECT * FROM MailTemplate WHERE dbo.RemoveSpecialChar(Heading, '^a-z0-9')=dbo.RemoveSpecialChar(@TemplateName, '^a-z0-9')
			SELECT @QStatus = 1;
		END
		ELSE
		BEGIN
			SELECT @QStatus = -1;
		END
    
		 SELECT @QStatus = 1;
		 
    END TRY
    
    BEGIN CATCH    
		 SELECT @QStatus = -1;		 
    END CATCH	
	 
END









GO
/****** Object:  StoredProcedure [dbo].[MailTemplatesGetList]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[MailTemplatesGetList]
	(@MailType nvarchar(128),
    @QStatus int output)
        
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    -- Insert statements for procedure here
    
    BEGIN TRY
			IF(@MailType<>'')
			BEGIN
				SELECT * FROM MailTemplate	WHERE IsActive = 1 AND MailType=@MailType
				SELECT @QStatus = 1;
			END
			ELSE
			BEGIN
				SELECT * FROM MailTemplate	WHERE IsActive = 1

				SELECT @QStatus = 1;
			END
    END TRY
    BEGIN CATCH    
		 SELECT @QStatus = -1;		 
    END CATCH
    	
END









GO
/****** Object:  StoredProcedure [dbo].[MailTemplatesGetListByVariable]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create  PROCEDURE [dbo].[MailTemplatesGetListByVariable]
	 (@Search nvarchar(126)
     ,@Sort nvarchar(126)
	 ,@PageNo int
     ,@Items int
     ,@Total int output)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.	
	SET NOCOUNT ON;
	
	BEGIN TRY
	
	DECLARE @Start int, @End INT ;
	DECLARE @Query nvarchar(max);
	
    -- Insert statements for procedure here
    
    CREATE TABLE #temp(Rid INT PRIMARY KEY IDENTITY(1,1)
				,MailTemplateId bigint
				,Heading nvarchar(512)
				,Subject nvarchar(512)
				,Description nvarchar(max)
				,MailType nvarchar(50)
				,UpdatedBy nvarchar(64)
				,UpdatedTime datetime
				,IsActive bit)
				
	SELECT @Start = (@PageNo - 1) * @Items  
	   
	SELECT @End = @Start + @Items 
	
	SET @Query = 'INSERT INTO #temp SELECT 
	             E.MailTemplateId
	            ,E.Heading
	            ,E.Subject
	            ,E.Description
	            ,E.MailType
	            ,E.UpdatedBy
	            ,E.UpdatedTime
				,E.IsActive
	             FROM MailTemplate E
		WHERE E.MailTemplateId <> 0 '
		
		
		IF(@Search <> '')
		BEGIN
			SET @Query = @Query + ' AND  E.Heading LIKE ''%' + @Search + '%'''
		END
		IF(@Sort <> '')
		BEGIN
			SET @Query = @Query + ' ORDER BY ' + @Sort
		END
	
		EXEC sp_ExecuteSQL @Query;
		
		SELECT @Total = COUNT(Rid) from #temp 
				
		SELECT * FROM #temp WHERE Rid > @Start AND  Rid <= @End
	
		DROP TABLE #temp

	END TRY
    
    BEGIN CATCH  
       SELECT @Total = -1; 		   
    END CATCH   
END







GO
/****** Object:  StoredProcedure [dbo].[MailTemplatesInsert]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[MailTemplatesInsert]
    (@MailTemplateId bigint
	,@Heading nvarchar(512)
	,@Subject nvarchar(512)
	,@Description nvarchar(max)
	,@MailType nvarchar(50)
	,@UpdatedBy nvarchar(64)
	,@UpdatedTime datetime
	,@IsActive bit
	,@QStatus int output)  
        
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT IsLockedOutments.
	
	SET NOCOUNT ON;

    -- Insert IsLockedOutments for procedure here
    
    BEGIN TRY
    BEGIN TRANSACTION
    IF((SELECT COUNT(*) FROM MailTemplate WHERE MailTemplateId = @MailTemplateId) = 0)
    BEGIN
        INSERT INTO MailTemplate VALUES
            (@Heading
            ,@Subject
            ,@Description
            ,@MailType
			,@UpdatedBy
			,@UpdatedTime
			,@IsActive)		
			
		SELECT @QStatus = 1;	
	END
	ELSE
	BEGIN
		UPDATE MailTemplate
		   SET  Heading=@Heading,
			   Subject=@Subject,
			   Description=@Description,
			   MailType=@MailType,
			   UpdatedBy=@UpdatedBy ,
			   UpdatedTime=@UpdatedTime
		 WHERE MailTemplateId = @MailTemplateId
		 		 
		 SELECT @QStatus = 2;	
	END
    COMMIT TRANSACTION
    END TRY
    
    BEGIN CATCH    
    ROLLBACK TRANSACTION
		SELECT @QStatus = -1;    
    END CATCH    
END









GO
/****** Object:  StoredProcedure [dbo].[MailTemplateUpdateStatus]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[MailTemplateUpdateStatus]
       (@MailTemplateId bigint
       ,@QStatus bigint OUTPUT)
        
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	BEGIN TRY
		IF((SELECT IsActive FROM MailTemplate WHERE MailTemplateId = @MailTemplateId) = 'True')
			BEGIN
			
			UPDATE MailTemplate SET IsActive = 'False' WHERE MailTemplateId = @MailTemplateId
			END
			
		ELSE
		
			BEGIN
			UPDATE MailTemplate SET IsActive = 'True' WHERE MailTemplateId = @MailTemplateId
			END	
			
	    SELECT @QStatus = 1;
	
	END TRY
    BEGIN CATCH    
		 SELECT @QStatus = -1;		 
    END CATCH  
    
END













GO
/****** Object:  StoredProcedure [dbo].[MenuItemsDelete]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[MenuItemsDelete]
    (@MenuItemId bigint
    ,@QStatus int OUTPUT)  
        
AS
BEGIN
	
	SET NOCOUNT ON;

    BEGIN TRY  
	Declare @PageDetailId bigint
	set @PageDetailId=(select PageDetailId from MenuPages where MenuItemId = @MenuItemId)
	if(@PageDetailId <>0)
	BEGIN
	Update PageDetails set AddPage='Only Page' where PageDetailId=@PageDetailId
	End

	  
		DELETE FROM MenuItems WHERE MenuItemId = @MenuItemId	    
		SELECT @QStatus = 1;
    END TRY

    BEGIN CATCH
		 SELECT @QStatus = -1;
    END CATCH
    	
END













GO
/****** Object:  StoredProcedure [dbo].[MenuItemsGetAll]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[MenuItemsGetAll]
    (@QStatus int OUTPUT)  
        
AS
BEGIN
	
	SET NOCOUNT ON;

    BEGIN TRY    
	    SELECT MenuItemId
			  ,DisplayName 
			  ,PageLevel
			  ,PageParentId
			  ,IdPath 
			  ,Position
			  ,IsFooterBar
			  ,IsMenuBar
			  ,IsTopBar
			  ,IsQuickLinks
			  ,IsActive
			  ,UpdatedBy
			  ,UpdatedDate
			  ,InsertedBy
			  ,InsertedDate
			  ,(SELECT IsActive FROM MenuItems WHERE MenuItems.MenuItemId = c.PageParentId) AS ParentActive 
			  ,(SELECT COUNT(*) FROM MenuItems AS CATCOUNT WHERE CATCOUNT.PageParentId = c.MenuItemId AND IsActive = 1) AS SubMenuItemCount
	          FROM MenuItems c WHERE c.IsActive = 1 ORDER BY IdPath, Position

		SELECT @QStatus = 1;
    END TRY

    BEGIN CATCH
		 SELECT @QStatus = -1;
    END CATCH
    	
END













GO
/****** Object:  StoredProcedure [dbo].[MenuItemsGetById]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[MenuItemsGetById]
	(@MenuItemId bigint
    ,@QStatus int OUTPUT)    
        
AS
BEGIN

	
	SET NOCOUNT ON;

    BEGIN TRY 
		SELECT *, (Select DisplayName From MenuItems Where MenuItemId = M.PageParentId) as ParentName FROM MenuItems M WHERE MenuItemId = @MenuItemId
	    SELECT @QStatus = 1;
    END TRY

    BEGIN CATCH
		 SELECT @QStatus = -1;
    END CATCH		
	 
END












GO
/****** Object:  StoredProcedure [dbo].[MenuItemsGetByLevel]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[MenuItemsGetByLevel]
	(@PageLevel int
    ,@QStatus int OUTPUT)    
        
AS
BEGIN
	
	SET NOCOUNT ON;

   BEGIN TRY 
		SELECT * FROM MenuItems  WHERE PageLevel = @PageLevel
	    SELECT @QStatus = 1;
    END TRY

    BEGIN CATCH
		 SELECT @QStatus = -1;
    END CATCH		
	 
END












GO
/****** Object:  StoredProcedure [dbo].[MenuItemsGetByName]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[MenuItemsGetByName]
	(@DisplayName nvarchar(256)
    ,@QStatus int OUTPUT)    
        
AS
BEGIN
	
	SET NOCOUNT ON;

   BEGIN TRY 
		SELECT * FROM MenuItems  WHERE DisplayName = @DisplayName
	    SELECT @QStatus = 1;
    END TRY

    BEGIN CATCH
		 SELECT @QStatus = -1;
    END CATCH		
	 
END












GO
/****** Object:  StoredProcedure [dbo].[MenuItemsGetByParentId]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[MenuItemsGetByParentId]
	(@PageParentId int
    ,@QStatus int OUTPUT)    
        
AS
BEGIN

	
	SET NOCOUNT ON;

    BEGIN TRY 
		SELECT * FROM MenuItems  WHERE PageParentId = @PageParentId
	    SELECT @QStatus = 1;
    END TRY

    BEGIN CATCH
		 SELECT @QStatus = -1;
    END CATCH		
	 
END












GO
/****** Object:  StoredProcedure [dbo].[MenuItemsGetList]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[MenuItemsGetList]
	@ChapterId bigint,
	@QStatus int OUTPUT
        
AS
BEGIN

	BEGIN TRY
		DECLARE @Query nvarchar(max);
		DECLARE @Start int, @End INT ;
	    
		SET NOCOUNT ON;
		    
		SELECT MenuItemId
			  ,ChapterId
			  ,DisplayName  
			  ,PageLevel
			  ,PageParentId
			  ,IdPath 
			  ,IsFooterBar
			  ,IsMenuBar
			  ,IsTopBar
			  ,IsQuickLinks
			  ,Position
			  ,IsActive
			  ,UpdatedBy
			  ,UpdatedDate
			  ,InsertedBy
			  ,InsertedDate
			  ,(SELECT IsActive FROM MenuItems WHERE MenuItems.MenuItemId = c.PageParentId) AS ParentActive 
			  ,(SELECT COUNT(*) FROM MenuItems AS CATCOUNT WHERE CATCOUNT.PageParentId = c.MenuItemId) AS SubMenuItemCount
			  ,(Select TOP(1) PageDetailId From MenuPages Where MenuItemId = c.MenuItemId) as PageDetailId
			  ,(Select TOP(1) MenuPageId From MenuPages Where MenuItemId = c.MenuItemId) as MenuPageId
			  ,(Select PageUrl From PageDetails Where PageDetailId = (Select TOP(1) PageDetailId From MenuPages Where MenuItemId = c.MenuItemId)) as PageUrl
			  ,null as ChapterName
	          FROM MenuItems c WHERE c.ChapterId = @ChapterId ORDER BY Position ASC

	          SELECT @QStatus = 1;		
	END TRY

    BEGIN CATCH
		 SELECT @QStatus = -1;
    END CATCH  
    
END












GO
/****** Object:  StoredProcedure [dbo].[MenuItemsGetListbkp]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[MenuItemsGetListbkp]
	@ChapterId bigint,
	@IsFooterBar bit,
	@IsMenuBar bit,
	@IsQuickLinks bit,
	@QStatus int OUTPUT
        
AS
BEGIN

	BEGIN TRY
		DECLARE @Query nvarchar(max);
		DECLARE @Start int, @End INT ;
	    
		SET NOCOUNT ON;
		    
		SELECT MenuItemId
			  ,ChapterId
			  ,DisplayName  
			  ,PageLevel
			  ,PageParentId
			  ,IdPath 
			  ,IsFooterBar
			  ,IsMenuBar
			  ,IsTopBar
			  ,IsQuickLinks
			  ,Position
			  ,IsActive
			  ,UpdatedBy
			  ,UpdatedDate
			  ,InsertedBy
			  ,InsertedDate
			  ,(SELECT IsActive FROM MenuItems WHERE MenuItems.MenuItemId = c.PageParentId) AS ParentActive 
			  ,(SELECT COUNT(*) FROM MenuItems AS CATCOUNT WHERE CATCOUNT.PageParentId = c.MenuItemId) AS SubMenuItemCount
			  ,(Select TOP(1) PageDetailId From MenuPages Where MenuItemId = c.MenuItemId) as PageDetailId
			  ,(Select TOP(1) MenuPageId From MenuPages Where MenuItemId = c.MenuItemId) as MenuPageId
			  ,(Select PageUrl From PageDetails Where PageDetailId = (Select TOP(1) PageDetailId From MenuPages Where MenuItemId = c.MenuItemId)) as PageUrl
			  ,null as ChapterName
	          FROM MenuItems c WHERE c.ChapterId = @ChapterId  
			  AND (@IsFooterBar =0 OR c.IsFooterBar = @IsFooterBar)
			 AND (@IsMenuBar =0 OR c.IsMenuBar = @IsMenuBar)
			 AND (@IsQuickLinks =0 OR c.IsQuickLinks = @IsQuickLinks)
			  
			  
			  ORDER BY Position ASC

	          SELECT @QStatus = 1;		
	END TRY

    BEGIN CATCH
		 SELECT @QStatus = -1;
    END CATCH  
    
END








GO
/****** Object:  StoredProcedure [dbo].[MenuItemsGetLists]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[MenuItemsGetLists]  
    @ChapterId bigint,  
    @SearchTerm nvarchar(255) = NULL,  -- New search parameter
    @QStatus int OUTPUT  
AS  
BEGIN  
    BEGIN TRY  
        SET NOCOUNT ON;  
        
        SELECT 
            MenuItemId,
            ChapterId,
            DisplayName,
            PageLevel,
            PageParentId,
            IdPath,
            IsFooterBar,
            IsMenuBar,
            IsTopBar,
            IsQuickLinks,
            Position,
            IsActive,
            UpdatedBy,
            UpdatedDate,
            InsertedBy,
            InsertedDate,
            (SELECT IsActive FROM MenuItems WHERE MenuItems.MenuItemId = c.PageParentId) AS ParentActive,
            (SELECT COUNT(*) FROM MenuItems AS CATCOUNT WHERE CATCOUNT.PageParentId = c.MenuItemId) AS SubMenuItemCount,
            (SELECT TOP(1) PageDetailId FROM MenuPages WHERE MenuItemId = c.MenuItemId) as PageDetailId,
            (SELECT TOP(1) MenuPageId FROM MenuPages WHERE MenuItemId = c.MenuItemId) as MenuPageId,
            (SELECT PageUrl FROM PageDetails WHERE PageDetailId = (SELECT TOP(1) PageDetailId FROM MenuPages WHERE MenuItemId = c.MenuItemId)) as PageUrl,
            (SELECT ShortName FROM Chapters WHERE ChapterId = c.ChapterId) as ChapterName
        FROM 
            MenuItems c
        WHERE 
            c.ChapterId = @ChapterId
            AND (@SearchTerm IS NULL OR c.DisplayName LIKE '%' + @SearchTerm + '%')
        ORDER BY 
            Position ASC;
        
        -- Set the status to indicate success
        SELECT @QStatus = 1;    
    END TRY  
    BEGIN CATCH  
        -- Set the status to indicate failure
        SELECT @QStatus = -1;  
    END CATCH    
END;





GO
/****** Object:  StoredProcedure [dbo].[MenuItemsInsert]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[MenuItemsInsert]
    (@MenuItemId bigint
	,@ChapterId bigint
    ,@DisplayName nvarchar(256) 
	,@PageParentId bigint 
	,@IsTopBar bit
	,@IsMenuBar bit
	,@IsQuickLinks bit
	,@IsFooterBar bit
	,@Position int						
	,@IsActive bit
	,@UpdatedBy nvarchar(64)
	,@UpdatedDate datetime
	,@InsertedBy nvarchar(64)
	,@InsertedDate datetime
	,@QStatus int OUTPUT)  
        
AS
BEGIN
	
	SET NOCOUNT ON;
    
    --BEGIN TRY
    --BEGIN TRANSACTION
		DECLARE @IdPath nvarchar(512)
		DECLARE @PageLevel int
		
		IF((SELECT COUNT(*) FROM MenuItems WHERE MenuItemId = @MenuItemId) = 0)
			BEGIN
				INSERT INTO MenuItems VALUES
						(@ChapterId
						,@DisplayName 
						,NULL
						,@PageParentId
						,NULL 
						,@Position
						,@IsTopBar
						,@IsMenuBar
						,@IsQuickLinks
						,@IsFooterBar
						,0
						,@UpdatedBy
						,@UpdatedDate
						,@InsertedBy
						,@InsertedDate)
			 
				 SELECT @MenuItemId =  SCOPE_IDENTITY()
			 
				 IF(@PageParentId IS NULL)
					 BEGIN
						UPDATE MenuItems SET IdPath = CAST(@MenuItemId as nvarchar(20)), PageLevel = 1  WHERE MenuItemId = @MenuItemId				
					 END
				 ELSE
					 BEGIN
						SELECT @IdPath = IdPath, @PageLevel = PageLevel + 1 FROM MenuItems WHERE MenuItemId = @PageParentId
						UPDATE MenuItems SET IdPath = @IdPath + '/' + CAST(@MenuItemId as nvarchar(20)), PageLevel = @PageLevel WHERE MenuItemId = @MenuItemId							 
					 END
			 
				 SELECT @QStatus = 1;	
			END
		ELSE
			BEGIN
				UPDATE MenuItems
				   SET  ChapterId = @ChapterId
					   ,DisplayName=@DisplayName 
					   ,PageParentId=@PageParentId 
					   ,IsFooterBar=@IsFooterBar
					   ,IsMenuBar=@IsMenuBar
					   ,IsTopBar=@IsTopBar
					   ,IsQuickLinks=@IsQuickLinks 
					   ,Position = @Position
					   ,UpdatedBy=@UpdatedBy
					   ,UpdatedDate=@UpdatedDate
					   ,InsertedBy=@InsertedBy
					   ,InsertedDate=@InsertedDate 
				WHERE MenuItemId = @MenuItemId
			
				 IF(@PageParentId IS NULL)
					 BEGIN
						UPDATE MenuItems SET IdPath = CAST(@MenuItemId as nvarchar(20)), PageLevel = 1  WHERE MenuItemId = @MenuItemId				
					 END
				 ELSE
					 BEGIN
						SELECT @IdPath = IdPath, @PageLevel = PageLevel + 1 FROM MenuItems WHERE MenuItemId = @PageParentId
						UPDATE MenuItems SET IdPath = @IdPath + '/' + CAST(@MenuItemId as nvarchar(20)), PageLevel = @PageLevel WHERE MenuItemId = @MenuItemId							 
					 END
			 		 
				SELECT @QStatus = 2;	
				--COMMIT TRANSACTION
			END
    
  --  END TRY
    
  --  BEGIN CATCH    
	 --   ROLLBACK TRANSACTION
		--SELECT @QStatus = -1;    
  --  END CATCH    
    
END












GO
/****** Object:  StoredProcedure [dbo].[MenuItemsUpdateOrderNo]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[MenuItemsUpdateOrderNo]
    (@MenuItemId bigint
    ,@Position int
    ,@QStatus int output)  
        
AS
BEGIN
	
	SET NOCOUNT ON;
    
    BEGIN TRY    
		BEGIN TRANSACTION
    
			IF((SELECT COUNT(*) FROM MenuItems WHERE MenuItemId = @MenuItemId) = 1)
				BEGIN
					UPDATE MenuItems SET Position = @Position WHERE MenuItemId = @MenuItemId
					SELECT @QStatus = 1;
				END
			ELSE
				BEGIN
					SELECT @QStatus = 3;
				END	
		COMMIT TRANSACTION
    END TRY

    BEGIN CATCH
         ROLLBACK TRANSACTION
		 SELECT @QStatus = -1;
    END CATCH
    	
END














GO
/****** Object:  StoredProcedure [dbo].[MenuItemsUpdateStatus]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[MenuItemsUpdateStatus]
       (@MenuItemId bigint
       ,@QStatus bigint OUTPUT)
        
AS
BEGIN

	SET NOCOUNT ON;
	
	BEGIN TRY
			DECLARE @Query1 nvarchar(max)
			DECLARE @Query2 nvarchar(max)
			DECLARE @IsActive int
			
			IF((SELECT IsActive FROM MenuItems WHERE MenuItemId = @MenuItemId) = 'True')
				BEGIN
					SET @IsActive = 0
				END
			ELSE
				BEGIN
					SET @IsActive = 1
				END
			
			SET @Query1 = 'UPDATE MenuItems SET IsActive = ' + CAST(@IsActive AS Nvarchar(10)) + ' WHERE MenuItemId IN (' + CAST((select dbo.SelectMenuItemsIdList(@MenuItemId,6)) as nvarchar(max)) + ')'
				 
			EXEC sp_ExecuteSQL @Query1;
			 
		    SELECT @QStatus = 1;
	END TRY
	
    BEGIN CATCH
		 SELECT @QStatus = -1;
    END CATCH  
    
END












GO
/****** Object:  StoredProcedure [dbo].[MenuPagesDelete]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[MenuPagesDelete]
    (@MenuPageId bigint
    ,@QStatus int output )  
        
AS
BEGIN
	
	SET NOCOUNT ON;
    
    BEGIN TRY  
        BEGIN TRANSACTION  
		DELETE FROM MenuPages WHERE MenuPageId = @MenuPageId		
		SELECT @QStatus = 1;
		COMMIT TRANSACTION		
    END TRY

    BEGIN CATCH
         ROLLBACK TRANSACTION
		 SELECT @QStatus = -1;
    END CATCH
    	
END












GO
/****** Object:  StoredProcedure [dbo].[MenuPagesDetailsGetById]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[MenuPagesDetailsGetById]
	(@PageDetailId bigint
    ,@QStatus int OUTPUT)    
AS
BEGIN
    SET NOCOUNT ON;

   -- BEGIN TRY 
		 SELECT  * from PageDetails where PageDetailId=@PageDetailId
		 SELECT  * from MenuPages where PageDetailId=@PageDetailId


		 SELECT  * from MenuItems where MenuItemId=(SELECT top(1)  MenuItemId from MenuPages where PageDetailId=@PageDetailId order by MenuItemId desc)


		 SELECT @QStatus = 1;
   -- END TRY
    
   -- BEGIN CATCH
		 --SELECT @QStatus = -1;
   -- END CATCH	
	 
END





















GO
/****** Object:  StoredProcedure [dbo].[MenuPagesGetById]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[MenuPagesGetById]
	(@MenuPageId bigint
    ,@QStatus int OUTPUT)    
        
AS
BEGIN
	
   SET NOCOUNT ON;

    BEGIN TRY 
    
		 SELECT *, (Select Heading From PageDetails Where PageDetailId = MP.PageDetailId) as Heading from MenuPages MP where MP.MenuPageId=@MenuPageId
		 
		 SELECT @QStatus = 1;
		 
    END TRY
    
    BEGIN CATCH
    
		 SELECT @QStatus = -1;
		 
    END CATCH	
	 
END












GO
/****** Object:  StoredProcedure [dbo].[MenuPagesGetList]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[MenuPagesGetList]
    @QStatus int output
        
AS
BEGIN
	
	SET NOCOUNT ON;
    
    BEGIN TRY
		SELECT * FROM MenuPages	
		SELECT @QStatus = 1;
    END TRY

    BEGIN CATCH
		 SELECT @QStatus = -1;
    END CATCH
    	
END













GO
/****** Object:  StoredProcedure [dbo].[MenuPagesGetListByMenuItemId]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[MenuPagesGetListByMenuItemId]
	@MenuItemId bigint,
    @QStatus int output
        
AS
BEGIN
	
	SET NOCOUNT ON;
    
    BEGIN TRY
		SELECT distinct PageDetailId ,* FROM PageDetails C where PageDetailId NOT IN (select PageDetailId from MenuPages where MenuItemId=@MenuItemId)
		SELECT @QStatus = 1;
    END TRY

    BEGIN CATCH
		 SELECT @QStatus = -1;
    END CATCH
    	
END













GO
/****** Object:  StoredProcedure [dbo].[MenuPagesInsert]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[MenuPagesInsert]
    (@MenuPageId bigint
    ,@PageDetailId bigint
    ,@MenuItemId bigint 
	,@QStatus int output)  
        
AS
BEGIN
	
	SET NOCOUNT ON;
    
    BEGIN TRY
    BEGIN TRANSACTION
		IF((SELECT COUNT(*) FROM MenuPages WHERE MenuPageId = @MenuPageId) = 0)
			BEGIN
				INSERT INTO MenuPages VALUES
					(@MenuItemId
					,@PageDetailId)		
			
				SELECT @QStatus = 1;	
			END
		ELSE
			BEGIN
				UPDATE MenuPages SET  PageDetailId=@PageDetailId  WHERE MenuPageId = @MenuPageId
		 		 
				 SELECT @QStatus = 2;	
			END
    COMMIT TRANSACTION
    END TRY
    
    BEGIN CATCH    
    ROLLBACK TRANSACTION
		SELECT @QStatus = -1;    
    END CATCH    
END











GO
/****** Object:  StoredProcedure [dbo].[MenuPagesList]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[MenuPagesList]
	 (@MenuItemId BIGINT
	 ,@Search nvarchar(126)
     ,@Sort nvarchar(126)
	 ,@PageNo int
     ,@Items int
     ,@Total int output)
AS
BEGIN
	
	SET NOCOUNT ON;
	
	BEGIN TRY
	
	DECLARE @Start int, @End INT ;
	DECLARE @Query nvarchar(max);
    
    CREATE TABLE #temp(Rid INT PRIMARY KEY IDENTITY(1,1)
				,PageDetailId bigint
				,Heading nvarchar(512)
				,PageUrl nvarchar(512)
				,DisplayName nvarchar(512)
				,UpdatedBy nvarchar(64)
				,UpdatedDate datetime
				,MenuPageId bigint
				,MenuItemId bigint)

				
	SELECT @Start = (@PageNo - 1) * @Items  
	   
	SELECT @End = @Start + @Items 
	
	SET @Query = 'INSERT INTO #temp SELECT
	             C.PageDetailId
	            ,C.Heading
				,C.PageUrl
				,CC.DisplayName 
				,C.UpdatedBy
				,C.UpdatedDate
				,CM.MenuPageId
				,CM.MenuItemId
				FROM PageDetails C 
				INNER JOIN MenuPages CM ON CM.PageDetailId=C.PageDetailId	
				INNER JOIN MenuItems CC ON CC.MenuItemId=CM.MenuItemId 
				WHERE C.PageDetailId <> 0'
		
		
		IF(@MenuItemId <> 0)
		BEGIN
			SET @Query=@Query + ' AND CM.MenuItemId = ' + CAST(@MenuItemId AS nvarchar(30))
		END 
		
		IF(@Search <> '')
		BEGIN
			SET @Query = @Query + ' AND  C.Heading LIKE ''%' + @Search + '%'''
		END

		IF(@Sort <> '')
		BEGIN
			SET @Query = @Query + ' ORDER BY ' + @Sort
		END
	
		EXEC sp_ExecuteSQL @Query;
		
		SELECT @Total = COUNT(Rid) from #temp 
				
		SELECT * FROM #temp WHERE Rid > @Start AND  Rid <= @End
	
		DROP TABLE #temp

	END TRY
    
    BEGIN CATCH  
       SELECT @Total = -1; 		   
    END CATCH   
END












GO
/****** Object:  StoredProcedure [dbo].[NewsDelete]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[NewsDelete]
    (@NewsId bigint
    ,@QStatus int output)  
        
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.	
	SET NOCOUNT ON;
    -- Insert statements for procedure here
    
    BEGIN TRY    
    BEGIN TRANSACTION

	

		DELETE FROM News WHERE NewsId = @NewsId
	
		

			
		SELECT @QStatus = 1;
	
	COMMIT TRANSACTION	
    END TRY

    BEGIN CATCH    
         ROLLBACK TRANSACTION
		 SELECT @QStatus = -1;		 
    END CATCH
    	
END

GO
/****** Object:  StoredProcedure [dbo].[NewsGetById]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[NewsGetById] 
	(@NewsId bigint
    ,@QStatus int OUTPUT)    
        
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.	
   SET NOCOUNT ON;
    -- Insert statements for procedure here

    BEGIN TRY     
		 SELECT * FROM News   WHERE NewsId  = @NewsId
		 SELECT @QStatus = 1;		 
    END TRY
    
    BEGIN CATCH    
		 SELECT @QStatus = -1;		 
    END CATCH	
	 
END

GO
/****** Object:  StoredProcedure [dbo].[NewsGetList]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[NewsGetList]
    @QStatus int output
        
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.	
	SET NOCOUNT ON;
    -- Insert statements for procedure here
    
    BEGIN TRY        
		SELECT * FROM News	ORDER BY InsertedDate ASC
		SELECT @QStatus = 1;			
    END TRY

    BEGIN CATCH    
		 SELECT @QStatus = -1;		 
    END CATCH
    	
END

GO
/****** Object:  StoredProcedure [dbo].[NewsGetListByVariable]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[NewsGetListByVariable] 
    (@PageNo int
    ,@Items int
    ,@Search nvarchar(126)
    ,@Total int output
    ,@Sort nvarchar(126)

)
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @Start int, @End INT;
    DECLARE @Query nvarchar(max);
    Set @Items = 1000
    
    -- Create temporary table matching News schema
    CREATE TABLE #temp(
        Rid INT PRIMARY KEY IDENTITY(1,1)
        ,NewsId bigint 
        ,Title nvarchar(2098)
        ,ImageUrl nvarchar(512)
        ,VideoUrl nvarchar(512)
        ,ShortDescription nvarchar(1024)
        ,Description nvarchar(max)
        ,PublishDate datetime
        ,IsActive bit
        ,InsertedBy nvarchar(256)
        ,InsertedDate datetime
        ,UpdatedBy nvarchar(256)
        ,UpdatedDate datetime
    )
    
    SELECT @Start = (@PageNo - 1) * @Items  
    SELECT @End = @Start + @Items 
    
    SET @Query = 'INSERT INTO #temp 
        SELECT  
            N.NewsId  
            ,N.Title
            ,N.ImageUrl
            ,N.VideoUrl
            ,N.ShortDescription
            ,N.Description
            ,N.PublishDate
            ,N.IsActive
            ,N.InsertedBy
            ,N.InsertedDate
            ,N.UpdatedBy
            ,N.UpdatedDate
        FROM News N
        WHERE N.NewsId <> 0'
        
    IF(@Search <> '')
    BEGIN
        SET @Query = @Query + ' AND (N.Title LIKE ''%' + @Search + '%'' 
                                OR N.ShortDescription LIKE ''%' + @Search + '%''
                                OR N.Description LIKE ''%' + @Search + '%'')'
    END
  
    
    IF(@Sort <> '')
    BEGIN
        SET @Query = @Query + ' ORDER BY ' + @Sort
    END
    ELSE
    BEGIN
        SET @Query = @Query + ' ORDER BY N.PublishDate DESC'
    END
         
    EXEC sp_ExecuteSQL @Query;
    
    SELECT @Total = COUNT(Rid) from #temp 
            
    SELECT * FROM #temp WHERE Rid > @Start AND Rid <= @End
    
    DROP TABLE #temp
END

GO
/****** Object:  StoredProcedure [dbo].[NewsInsert]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[NewsInsert]
    (@NewsId bigint 
    ,@Title nvarchar(2098)
    ,@ImageUrl nvarchar(512) output
    ,@VideoUrl nvarchar(512) = NULL
    ,@ShortDescription nvarchar(1024) = NULL
    ,@Description nvarchar(max) = NULL
    ,@PublishDate datetime = NULL
    ,@InsertedBy nvarchar(256)
    ,@UpdatedBy nvarchar(256)
    ,@QStatus int output)  
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        BEGIN TRANSACTION
        
        IF((SELECT COUNT(*) FROM News WHERE NewsId = @NewsId) = 0)
        BEGIN
            -- Insert new record
            INSERT INTO News 
            (
                Title
                ,ImageUrl
                ,VideoUrl
                ,ShortDescription
                ,Description
                ,PublishDate
                ,IsActive
                ,InsertedBy
                ,InsertedDate
                ,UpdatedBy
                ,UpdatedDate
            )
            VALUES
            (
                @Title
                ,null
                ,@VideoUrl
                ,@ShortDescription
                ,@Description
                ,@PublishDate
                ,1
                ,@InsertedBy
                ,CONVERT(DateTime, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30'))
                ,@UpdatedBy
                ,CONVERT(DateTime, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30'))
            )
            
            SET @NewsId = SCOPE_IDENTITY()
            SELECT @QStatus = 1; -- Insert successful
        END
        ELSE 
        BEGIN		
            -- Update existing record
            UPDATE News
            SET 
                Title = @Title
              --  ,ImageUrl = @ImageUrl
                ,VideoUrl = @VideoUrl
                ,ShortDescription = @ShortDescription
                ,Description = @Description
                ,PublishDate = @PublishDate
              --  ,IsActive = @IsActive
                ,UpdatedBy = @UpdatedBy
                ,UpdatedDate = CONVERT(DateTime, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30'))
            WHERE NewsId = @NewsId
             		 
            SELECT @QStatus = 2; -- Update successful
        END



		

			IF(LEFT(@ImageUrl, 1) = '.' OR @ImageUrl <> 'NA')
		BEGIN
		
			IF(@ImageUrl <> 'NA')
			BEGIN

			
				UPDATE News
				   SET  ImageUrl= CAST(@NewsId  AS nvarchar(12)) + '-' + dbo.RemoveSpecialChar(@Title, '^a-z0-9') + @ImageUrl
				   WHERE NewsId = @NewsId	
			  
				  SELECT @ImageUrl = CAST(@NewsId AS nvarchar(12)) + '-' + dbo.RemoveSpecialChar(@Title, '^a-z0-9') + @ImageUrl	
			END	
			ELSE
			BEGIN
			  SELECT @ImageUrl=''
			END 
		END
        
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH     
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION 
        SELECT @QStatus = -1; -- Error occurred          
    END CATCH   
END

GO
/****** Object:  StoredProcedure [dbo].[NewsUpdateStatus]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[NewsUpdateStatus]
    (@NewsId bigint,
     @QStatus int OUTPUT)  
AS
BEGIN
	SET NOCOUNT ON;
    
    BEGIN TRY    
		BEGIN TRANSACTION;
    
			IF ((SELECT IsActive FROM News WHERE NewsId = @NewsId) = 1)
			BEGIN
				UPDATE News SET IsActive = 0 WHERE NewsId = @NewsId;
			END
			ELSE
			BEGIN
				UPDATE News SET IsActive = 1 WHERE NewsId = @NewsId;
			END		
		
			SELECT @QStatus = 1;   -- Success
		
		COMMIT TRANSACTION;
    END TRY

    BEGIN CATCH
         ROLLBACK TRANSACTION;
		 SELECT @QStatus = -1;   -- Failure
    END CATCH
    	
END

GO
/****** Object:  StoredProcedure [dbo].[PageDetailsDelete]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[PageDetailsDelete]
    (@PageDetailId bigint
    ,@QStatus int output )  
        
AS
BEGIN
	
	SET NOCOUNT ON;
    
    BEGIN TRY  
        BEGIN TRANSACTION  
			DELETE FROM PageDetails WHERE PageDetailId = @PageDetailId		
			SELECT @QStatus = 1;
		COMMIT TRANSACTION		
    END TRY

    BEGIN CATCH
         ROLLBACK TRANSACTION
		 SELECT @QStatus = -1;
    END CATCH
    	
END












GO
/****** Object:  StoredProcedure [dbo].[PageDetailsGetByHeading]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[PageDetailsGetByHeading]
           (@Heading nvarchar(512)
           ,@QStatus int output)  
         
AS
BEGIN

    SET NOCOUNT ON;

    BEGIN TRY 
       SELECT * from PageDetails WHERE dbo.RemoveSpecialChar(Heading, '^a-z0-9') = dbo.RemoveSpecialChar(@Heading, '^a-z0-9')
	   SELECT @QStatus = 1;
    END TRY

    BEGIN CATCH
		 SELECT @QStatus = -1;
    END CATCH	

END










GO
/****** Object:  StoredProcedure [dbo].[PageDetailsGetById]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[PageDetailsGetById]
	(@PageDetailId bigint
    ,@QStatus int OUTPUT)    
        
AS
BEGIN

    SET NOCOUNT ON;

    BEGIN TRY 
		 SELECT  * from PageDetails where PageDetailId=@PageDetailId
		 SELECT @QStatus = 1;
    END TRY
    
    BEGIN CATCH
		 SELECT @QStatus = -1;
    END CATCH	
	 
END












GO
/****** Object:  StoredProcedure [dbo].[PageDetailsGetList]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[PageDetailsGetList]
	 (@QStatus int output)
AS
BEGIN
	
	SET NOCOUNT ON;
	
	BEGIN TRY
	    DECLARE @Query nvarchar(max);
		SELECT  * FROM PageDetails Order By Heading ASC
		SELECT @QStatus = 1; 
	END TRY   
	
    BEGIN CATCH  
       SELECT @QStatus = -1; 		   
    END CATCH   
END












GO
/****** Object:  StoredProcedure [dbo].[PageDetailsGetListById]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[PageDetailsGetListById]
	 (@ChapterId bigint
	 ,@Heading nvarchar(512)
	 ,@PageUrl nvarchar(512)
	 ,@MenuItemId bigint
	 ,@IDPath nvarchar(128) output
     ,@QStatus int output)
AS
BEGIN
	
	SET NOCOUNT ON;
	
	BEGIN TRY
		DECLARE @Query nvarchar(max);
		Declare @PageDetailId bigint
    
		IF(@MenuItemId <> 0)
			BEGIN
				Select @IDPath = IDPath From MenuItems Where MenuItemId = @MenuItemId
			END

		Select @PageDetailId = PageDetailId from MenuPages Where MenuItemId = @MenuItemId

		IF(@PageDetailId<>0)
			BEGIN
				 SELECT  * FROM PageDetails WHERE PageDetailId=@PageDetailId 		
			END 
		ELSE IF(@PageUrl <> '')
			BEGIN
				IF((Select Count(*) From PageDetails Where PageUrl = @PageUrl) = 1)
					BEGIN
						SELECT  * FROM PageDetails WHERE PageUrl = @PageUrl
					END
			END 
		ELSE IF(@Heading<>'')
			BEGIN
				 SELECT  * FROM PageDetails WHERE dbo.RemoveSpecialChar(Heading, '^a-z0-9') = dbo.RemoveSpecialChar(@Heading, '^a-z0-9')
			END
		SELECT @QStatus = 1; 
	END TRY  
	
    BEGIN CATCH  
       SELECT @QStatus = -1; 		   
    END CATCH   
END











GO
/****** Object:  StoredProcedure [dbo].[PageDetailsGetListByVariable]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[PageDetailsGetListByVariable]
	 (@ChapterId bigint
	 ,@Search nvarchar(126)
     ,@Sort nvarchar(126)
	 ,@PageNo int
     ,@Items int
     ,@Total int output)
AS
BEGIN
	
	SET NOCOUNT ON;
	
	BEGIN TRY
	
	DECLARE @Start int, @End INT ;
	DECLARE @Query nvarchar(max);
    
    CREATE TABLE #temp(Rid INT PRIMARY KEY IDENTITY(1,1)
				,PageDetailId bigint 
				,Heading nvarchar(256)
				,Description nvarchar(max)
				,PageUrl nvarchar(512)
				,DocumentUrl nvarchar(512)
				,Target nvarchar(256)
				,IsActive bit
				,PageTitle nvarchar(512)
				,MetaDescription nvarchar(1024)
				,MetaKeywords nvarchar(1024)
				,Topline nvarchar(2024)
				,InsertedBy nvarchar(64)
				,InsertedDate datetime
				,UpdatedBy nvarchar(64)
				,UpdatedDate datetime 
				,ChapterId bigint
				,ChapterName nvarchar(128)
				,OtherUrl nvarchar(512))

				
			SELECT @Start = (@PageNo - 1) * @Items  
	   
			SELECT @End = @Start + @Items 
	
			SET @Query = 'INSERT INTO #temp 
				 SELECT Distinct IP.PageDetailId  
				,IP.Heading
				,IP.Description
				,IP.PageUrl
				,IP.DocumentUrl
				,IP.Target
				,IP.IsActive
				,IP.PageTitle
				,IP.MetaDescription
				,IP.MetaKeywords
				,IP.Topline
				,IP.InsertedBy
				,IP.InsertedDate 
				,IP.UpdatedBy
				,IP.UpdatedDate
				,0 as ChapterId
				,'''' as ChapterName
				,IP.OtherUrl
				FROM PageDetails IP 
				WHERE IP.PageDetailId <> 0 and  
				IP.PageDetailId not in (17,18,19,20,21)'

				
		
	--IF(@ChapterId <> '' and @ChapterId <> 1)
	--		BEGIN
	--			SET @Query = @Query + ' AND  MI.ChapterId = '+ CAST(@ChapterId as nvarchar(64))
	--		END
		IF(@Search <> '')
			BEGIN
				SET @Query = @Query + ' AND  IP.Heading LIKE ''%' + @Search + '%'''
			END
		IF(@Sort <> '')
			BEGIN
				SET @Query = @Query + ' ORDER BY ' + @Sort
			END
	
		EXEC sp_ExecuteSQL @Query;
		
		SELECT @Total = COUNT(Rid) from #temp 
				
		SELECT * FROM #temp WHERE Rid > @Start AND  Rid <= @End
	
		DROP TABLE #temp
	END TRY
    
    BEGIN CATCH  
       SELECT @Total = -1; 		   
    END CATCH   
END










GO
/****** Object:  StoredProcedure [dbo].[PageDetailsInsert]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[PageDetailsInsert]  
    (@PageDetailId bigint   
 ,@Heading nvarchar(256)  
 ,@Description nvarchar(max)  
 ,@PageUrl nvarchar(512)  
 ,@DocumentUrl nvarchar(512)  
 ,@Target nvarchar(25)  
 ,@IsActive bit  
 ,@PageTitle nvarchar(512)  
 ,@MetaDescription nvarchar(1024)  
 ,@MetaKeywords nvarchar(1024)  
 ,@Topline nvarchar(2024)  
 ,@InsertedBy nvarchar(64)  
 ,@InsertedDate datetime  
 ,@UpdatedBy nvarchar(64)  
 ,@UpdatedDate datetime  
 ,@OtherUrl nvarchar(512)  
 ,@QStatus int output  
 --MenuItems--  
 ,@MenuItemId bigint  
 ,@ChapterId bigint  
    ,@DisplayName nvarchar(256)   
 ,@AddPage nvarchar(256)  
 ,@PageParentId bigint   
 ,@IsTopBar bit  
 ,@IsMenuBar bit  
 ,@IsQuickLinks bit  
 ,@IsFooterBar bit  
 ,@ExistingMenuItemId bigint  
 ,@Position int)    
          
AS  
BEGIN  
   
 SET NOCOUNT ON;  
     DECLARE @IdPath nvarchar(512)  
  DECLARE @PageLevel int  
    --BEGIN TRY  
    --BEGIN TRANSACTION  
  IF((SELECT COUNT(*) FROM PageDetails WHERE PageDetailId = @PageDetailId or Heading=@Heading) = 0)  
   BEGIN  
    INSERT INTO PageDetails VALUES  
     (@Heading   
     ,@Description   
     ,Case When (Select Count(*) from PageDetails Where PageUrl = @PageUrl) <> 0 Then @PageUrl + '-new' else @PageUrl END  
     ,@DocumentUrl  
     ,@Target   
     ,@PageTitle  
     ,@MetaDescription   
     ,@MetaKeywords   
     ,@Topline   
     ,@IsActive   
     ,@UpdatedBy   
     ,@UpdatedDate  
     ,@InsertedBy   
     ,@InsertedDate   
     ,@OtherUrl,  
      @AddPage)    
   SELECT @PageDetailId =  SCOPE_IDENTITY()  
    if(@AddPage='New Menu Item')  
   BEGIN  
    INSERT INTO MenuItems VALUES  
      (@ChapterId  
      ,@DisplayName   
      ,NULL  
      ,@PageParentId  
      ,NULL   
      ,@Position  
      ,@IsTopBar  
      ,@IsMenuBar  
      ,@IsQuickLinks  
      ,@IsFooterBar  
      ,@IsActive  
      ,@UpdatedBy  
      ,@UpdatedDate  
      ,@InsertedBy  
      ,@InsertedDate)  
      
     SELECT @MenuItemId =  SCOPE_IDENTITY()  
      
     IF(@PageParentId IS NULL)  
      BEGIN  
      UPDATE MenuItems SET IdPath = CAST(@MenuItemId as nvarchar(20)), PageLevel = 1  WHERE MenuItemId = @MenuItemId      
      END  
     ELSE  
      BEGIN  
      SELECT @IdPath = IdPath, @PageLevel = PageLevel + 1 FROM MenuItems WHERE MenuItemId = @PageParentId  
      UPDATE MenuItems SET IdPath = @IdPath + '/' + CAST(@MenuItemId as nvarchar(20)), PageLevel = @PageLevel WHERE MenuItemId = @MenuItemId          
      END  
  
      INSERT INTO MenuPages VALUES  
       (@MenuItemId  
       ,@PageDetailId)   
          

  end
     if(@AddPage='Existing Menu')  
   BEGIN  
     IF((SELECT COUNT(*) FROM MenuPages wHERE MenuItemId = @ExistingMenuItemId AND PageDetailId = @PageDetailId) = 0)
	  BEGIN  
      INSERT INTO MenuPages VALUES  
       (@ExistingMenuItemId  
       ,@PageDetailId) 
	   END  
   End 
 
    SELECT @QStatus = 1;   
   end  
   
    ELSE  
   BEGIN  
    UPDATE PageDetails  
       SET Heading   = @Heading ,  
        Description  = @Description ,  
        PageUrl   = Case When (Select Count(*) from PageDetails Where PageDetailId <> @PageDetailId AND PageUrl = @PageUrl) <> 0 Then @PageUrl + '-new' else @PageUrl END,   
        OtherUrl   = @OtherUrl,  
        DocumentUrl  = @DocumentUrl,  
        Target   = @Target,  
        PageTitle  = @PageTitle,  
        MetaDescription  = @MetaDescription ,  
        MetaKeywords  = @MetaKeywords ,  
        Topline   = @Topline ,   
        UpdatedBy  = @UpdatedBy ,  
        UpdatedDate  = @UpdatedDate ,AddPage=@AddPage  
     WHERE PageDetailId  = @PageDetailId  
  
     if(@AddPage='New Menu Item')  
   BEGIN  
   IF((SELECT COUNT(*) FROM MenuItems WHERE MenuItemId = @MenuItemId) = 0)  
   BEGIN  
    INSERT INTO MenuItems VALUES  
      (@ChapterId  
      ,@DisplayName   
      ,NULL  
      ,@PageParentId  
      ,NULL   
      ,@Position  
      ,@IsTopBar  
      ,@IsMenuBar  
      ,@IsQuickLinks  
      ,@IsFooterBar  
      ,@IsActive  
      ,@UpdatedBy  
      ,@UpdatedDate  
      ,@InsertedBy  
      ,@InsertedDate)  
      
     SELECT @MenuItemId =  SCOPE_IDENTITY()  
      
     IF(@PageParentId IS NULL)  
      BEGIN  
      UPDATE MenuItems SET IdPath = CAST(@MenuItemId as nvarchar(20)), PageLevel = 1  WHERE MenuItemId = @MenuItemId      
      END  
     ELSE  
      BEGIN  
      SELECT @IdPath = IdPath, @PageLevel = PageLevel + 1 FROM MenuItems WHERE MenuItemId = @PageParentId  
      UPDATE MenuItems SET IdPath = @IdPath + '/' + CAST(@MenuItemId as nvarchar(20)), PageLevel = @PageLevel WHERE MenuItemId = @MenuItemId          
      END  
  
      INSERT INTO MenuPages VALUES  
       (@MenuItemId  
       ,@PageDetailId)   
  end
       ELSE  
      BEGIN  
  
      UPDATE MenuItems  
       SET    
        DisplayName=@DisplayName   
        ,PageParentId=@PageParentId   
        ,IsFooterBar=@IsFooterBar  
        ,IsMenuBar=@IsMenuBar  
        ,IsTopBar=@IsTopBar  
        ,IsQuickLinks=@IsQuickLinks   
        ,Position = @Position  
        ,UpdatedBy=@UpdatedBy  
        ,UpdatedDate=@UpdatedDate  
        ,InsertedBy=@InsertedBy  
        ,InsertedDate=@InsertedDate   
        ,ChapterId=@ChapterId  
    WHERE MenuItemId = @MenuItemId  
     
     IF(@PageParentId IS NULL)  
      BEGIN  
      UPDATE MenuItems SET IdPath = CAST(@MenuItemId as nvarchar(20)), PageLevel = 1  WHERE MenuItemId = @MenuItemId      
      END  
     ELSE  
      BEGIN  
      SELECT @IdPath = IdPath, @PageLevel = PageLevel + 1 FROM MenuItems WHERE MenuItemId = @PageParentId  
      UPDATE MenuItems SET IdPath = @IdPath + '/' + CAST(@MenuItemId as nvarchar(20)), PageLevel = @PageLevel WHERE MenuItemId = @MenuItemId          
      END  
    
          
     end
       end  

     if(@AddPage='Existing Menu')  
   BEGIN  
      IF((SELECT COUNT(*) FROM MenuPages wHERE MenuItemId = @ExistingMenuItemId AND PageDetailId = @PageDetailId) = 0)
	  BEGIN  
      INSERT INTO MenuPages VALUES  
       (@ExistingMenuItemId  
       ,@PageDetailId)   
	   END
   End 
end 
  SELECT @QStatus = 2;
  
  
     
     
  end  
      
   
  --  COMMIT TRANSACTION  
  --  END TRY  
      
  --  BEGIN CATCH      
  --  ROLLBACK TRANSACTION  
  --SELECT @QStatus = -1;      
  --  END CATCH      

  
  
  
  
  
  
  
  
  
  



GO
/****** Object:  StoredProcedure [dbo].[PageDetailsUpdateStatus]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[PageDetailsUpdateStatus]
    (@PageDetailId bigint
    ,@QStatus int output)  
        
AS
BEGIN
	
	SET NOCOUNT ON;
    
    BEGIN TRY    
		BEGIN TRANSACTION
			IF((SELECT IsActive FROM PageDetails WHERE PageDetailId = @PageDetailId) = 1)
				BEGIN
					UPDATE PageDetails SET IsActive = 0 WHERE PageDetailId = @PageDetailId
				END
			ELSE
				BEGIN
					UPDATE PageDetails SET IsActive = 1 WHERE PageDetailId = @PageDetailId
				END		
			SELECT @QStatus = 1;
		COMMIT TRANSACTION
    END TRY

    BEGIN CATCH
         ROLLBACK TRANSACTION
		 SELECT @QStatus = -1;
    END CATCH
    	
END
 










GO
/****** Object:  StoredProcedure [dbo].[RolesDelete]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[RolesDelete]
    (@RoleId bigint
    ,@QStatus int output)  
        
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    
    --BEGIN TRY    
    BEGIN TRANSACTION
    
			DELETE FROM Roles WHERE RoleId = @RoleId
		
		SELECT @QStatus = 1;
	
	COMMIT TRANSACTION	
   -- END TRY
   -- BEGIN CATCH
    
   --      ROLLBACK TRANSACTION
		 --SELECT @QStatus = -1;
		 
   -- END CATCH
    	
END






























GO
/****** Object:  StoredProcedure [dbo].[RolesGetById]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[RolesGetById]
	(@RoleId bigint
    ,@QStatus int OUTPUT)    
        
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.	
   SET NOCOUNT ON;

    -- Insert statements for procedure here

    BEGIN TRY     
		 SELECT * FROM Roles  WHERE RoleId  = @RoleId
		 SELECT @QStatus = 1;		 
    END TRY
    
    BEGIN CATCH    
		 SELECT @QStatus = -1;		 
    END CATCH	
	 
END






























GO
/****** Object:  StoredProcedure [dbo].[RolesGetList]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[RolesGetList]
    @QStatus int output
        
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    
    BEGIN TRY 
       
		SELECT * FROM Roles where IsActive=1 Order by RoleName Asc	
		SELECT @QStatus = 1;
			
    END TRY
    BEGIN CATCH
    
		 SELECT @QStatus = -1;
		 
    END CATCH
    	
END






























GO
/****** Object:  StoredProcedure [dbo].[RolesGetListByVariable]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[RolesGetListByVariable]
	 (@PageNo int
     ,@Items int
     ,@Search nvarchar(126)
     ,@Total int output
     ,@Sort nvarchar(126))
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.	
	SET NOCOUNT ON;
	
	DECLARE @Start int, @End INT ;
	DECLARE @Query nvarchar(max);
	
    -- Insert statements for procedure here
    
    CREATE TABLE #temp(Rid INT PRIMARY KEY IDENTITY(1,1)
				,RoleId bigint 
				,RoleName varchar(50)
				,IsActive bit
				,UpdatedBy nvarchar(256)
				,UpdatedTime datetime
				)
	
	SELECT @Start = (@PageNo - 1) * @Items  
	   
	SELECT @End = @Start + @Items 
	
	SET @Query = 'INSERT INTO #temp 
		 SELECT  SL.RoleId 
				,SL.RoleName 
				,SL.IsActive
				,SL.UpdatedBy 
				,SL.UpdatedTime 
		FROM Roles SL  WHERE SL.RoleId <> 0 '
	
	
	IF(@Search <> '')
	
    BEGIN
		SET @Query = @Query + ' AND  SL.RoleName LIKE ''%' + @Search + '%'''
    END
	
	IF(@Sort <> '')
	
	BEGIN
		SET @Query = @Query + ' ORDER BY ' + @Sort
	END
	
	 	
	EXEC sp_ExecuteSQL @Query;
	
	SELECT @Total = COUNT(Rid) from #temp 
			
	SELECT * FROM #temp WHERE Rid > @Start AND  Rid <= @End
	
	  DROP TABLE #temp

END






























GO
/****** Object:  StoredProcedure [dbo].[RolesInsert]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[RolesInsert]
    (@RoleId bigint 
	,@RoleName Nvarchar(256)	
	,@IsActive bit
	,@UpdatedBy nvarchar(50)
	,@UpdatedTime datetime
	,@QStatus int output)  
        
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.	
	SET NOCOUNT ON;
    -- Insert statements for procedure here
    
   -- BEGIN TRY
   -- BEGIN TRANSACTION
    
		 IF((SELECT COUNT(*) FROM Roles WHERE RoleId = @RoleId) = 0)
		BEGIN
			INSERT INTO Roles VALUES
			        (@RoleName	 
	                ,@IsActive
	                ,@UpdatedTime
					 ,@UpdatedBy
					)
					
					Set @RoleId = SCOPE_IDENTITY()

		SELECT @QStatus = 1;	
		END
		ELSE BEGIN
		
			UPDATE Roles
			   SET 
				   RoleName=@RoleName
				  ,UpdatedBy=@UpdatedBy
				  ,UpdatedTime=@UpdatedTime
			 WHERE  RoleId = @RoleId
			 		 
			 SELECT @QStatus = 2;
			 
		 END
		 
		
	--COMMIT TRANSACTION
	--END TRY
    
 --   BEGIN CATCH     
	--   ROLLBACK TRANSACTION 
 --      SELECT @QStatus = -1;           
 --   END CATCH   
    
END



























GO
/****** Object:  StoredProcedure [dbo].[RolesNameByGetByNameAvailability]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[RolesNameByGetByNameAvailability]
	(@RoleName nvarchar(max)
    ,@QStatus int OUTPUT)    
        
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.	
   SET NOCOUNT ON;

    -- Insert statements for procedure here

    BEGIN TRY     
		 SELECT * FROM Roles  WHERE RoleName  = @RoleName
		 SELECT @QStatus = 1;		 
    END TRY
    
    BEGIN CATCH   
		 SELECT @QStatus = -1;		 
    END CATCH	
	 
END






GO
/****** Object:  StoredProcedure [dbo].[SampleMOUFileDelete]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[SampleMOUFileDelete]
    (@Id bigint
    ,@QStatus int output)  
        
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    
    --BEGIN TRY    
    BEGIN TRANSACTION
    
			DELETE FROM SampleMOUFile WHERE Id = @Id
		
		SELECT @QStatus = 1;
	
	COMMIT TRANSACTION	
   -- END TRY
   -- BEGIN CATCH
    
   --      ROLLBACK TRANSACTION
		 --SELECT @QStatus = -1;
		 
   -- END CATCH
    	
END






























GO
/****** Object:  StoredProcedure [dbo].[SampleMOUFileGetById]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SampleMOUFileGetById] 
	(@Id bigint
    ,@QStatus int OUTPUT)    
        
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.	
   SET NOCOUNT ON;
    -- Insert statements for procedure here

    BEGIN TRY     
		 SELECT * FROM SampleMOUFile   WHERE Id  = @Id
		 SELECT @QStatus = 1;		 
    END TRY
    
    BEGIN CATCH    
		 SELECT @QStatus = -1;		 
    END CATCH	
	 
END

GO
/****** Object:  StoredProcedure [dbo].[SampleMOUFileGetList]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SampleMOUFileGetList]
    @QStatus int output
        
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.	
	SET NOCOUNT ON;
    -- Insert statements for procedure here
    
    BEGIN TRY        
		SELECT * FROM SampleMOUFile	where IsActive=1
		SELECT @QStatus = 1;			
    END TRY

    BEGIN CATCH    
		 SELECT @QStatus = -1;		 
    END CATCH
    	
END

GO
/****** Object:  StoredProcedure [dbo].[SampleMOUFileGetListByVariable]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SampleMOUFileGetListByVariable] 
	 (@PageNo int
	 ,@Items int
     ,@Search nvarchar(126)
     ,@Total int output
     ,@Sort nvarchar(126))
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	
	SET NOCOUNT ON;
	
	DECLARE @Start int, @End INT ;
	DECLARE @Query nvarchar(max);
	Set @Items = 1000
    -- Insert statements for procedure here
    
    CREATE TABLE #temp(Rid INT PRIMARY KEY IDENTITY(1,1)
				,Id bigint 
				,Name nvarchar(256)
				,DocumentUrl nvarchar(1024)
				,UpdatedBy nvarchar(256)
				,UpdatedDate datetime
				,ISActive bit
				)
				
	
	SELECT @Start = (@PageNo - 1) * @Items  
	   
	SELECT @End = @Start + @Items 
	
	SET @Query = 'INSERT INTO #temp 
		 SELECT  S.Id  
				,S.Name 
				,S.DocumentUrl
				,S.UpdatedBy 
				,S.UpdatedDate
			    ,S.ISActive	 
		FROM SampleMOUFile S  
		
		
		WHERE S.Id <> 0
		
		'
		
	IF(@Search <> '')
	
    BEGIN
		SET @Query = @Query + ' AND  S.Name LIKE ''%' + @Search + '%'''
    END
    

	
	IF(@Sort <> '')
	
	BEGIN
		SET @Query = @Query + ' ORDER BY ' + @Sort
	END
		 	
	EXEC sp_ExecuteSQL @Query;
	
	SELECT @Total = COUNT(Rid) from #temp 
			
	SELECT * FROM #temp WHERE Rid > @Start AND  Rid <= @End
	
	  DROP TABLE #temp

END

GO
/****** Object:  StoredProcedure [dbo].[SampleMOUFileInsert]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SampleMOUFileInsert]
    (@Id bigint 
	,@Name nvarchar(256)
	,@DocumentUrl nvarchar(max) output
	,@UpdatedDate datetime
	,@UpdatedBy nvarchar(256)
	,@QStatus int output)  
        
AS
BEGIN
	
	SET NOCOUNT ON;
  
   -- BEGIN TRY
   -- BEGIN TRANSACTION
    
		 IF((SELECT COUNT(*) FROM SampleMOUFile WHERE Id = @Id) = 0)
		BEGIN
			INSERT INTO SampleMOUFile VALUES
			        (@Name
					,null
					,1
					,(Convert(DateTime, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')))
			        ,@UpdatedBy
					
					)
					
					Set @Id = SCOPE_IDENTITY()

		SELECT @QStatus = 1;	
		END
		ELSE BEGIN		
			UPDATE SampleMOUFile
			   SET 
				   Name=@Name
				  ,UpdatedBy=@UpdatedBy
				  ,UpdatedDate=(Convert(DateTime, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')))
			 WHERE Id = @Id
			 		 
			 SELECT @QStatus = 2;
			 
		 END
		

			IF(LEFT(@DocumentUrl, 1) = '.' OR @DocumentUrl <> 'NA')
		BEGIN
		
			IF(@DocumentUrl <> 'NA')
			BEGIN

			
				UPDATE SampleMOUFile
				   SET  DocumentUrl= CAST(@Id  AS nvarchar(12)) + '-' + dbo.RemoveSpecialChar(@Name, '^a-z0-9') + @DocumentUrl
				   WHERE Id = @Id	
			  
				  SELECT @DocumentUrl = CAST(@Id AS nvarchar(12)) + '-' + dbo.RemoveSpecialChar(@Name, '^a-z0-9') + @DocumentUrl	
			END	
			ELSE
			BEGIN
			  SELECT @DocumentUrl=''
			END 
		END
		

	--COMMIT TRANSACTION
	--END TRY
    
 --   BEGIN CATCH     
	--   ROLLBACK TRANSACTION 
 --      SELECT @QStatus = -1;           
 --   END CATCH   
    
END

GO
/****** Object:  StoredProcedure [dbo].[SampleMOUFileUpdateStatus]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[SampleMOUFileUpdateStatus]
    (@Id bigint
    ,@QStatus int output)  
        
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.	
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    
    BEGIN TRY    
    BEGIN TRANSACTION
    
		IF((SELECT IsActive FROM SampleMOUFile WHERE Id = @Id) = 1)
		BEGIN
			UPDATE SampleMOUFile SET IsActive = 0 WHERE Id = @Id
		END
		ELSE
		BEGIN
			UPDATE SampleMOUFile SET IsActive = 1 WHERE Id = @Id
		END		
		
		SELECT @QStatus = 1;
		
	COMMIT TRANSACTION
    END TRY

    BEGIN CATCH    
         ROLLBACK TRANSACTION
		 SELECT @QStatus = -1;		 
    END CATCH
    	
END


GO
/****** Object:  StoredProcedure [dbo].[SkillsDelete]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[SkillsDelete]
    (@SkillId bigint
    ,@QStatus int output)  
        
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    
    --BEGIN TRY    
    BEGIN TRANSACTION
    

		DELETE FROM Skills WHERE SkillId = @SkillId
		
		SELECT @QStatus = 1;
	
	COMMIT TRANSACTION	
   -- END TRY
   -- BEGIN CATCH
    
   --      ROLLBACK TRANSACTION
		 --SELECT @QStatus = -1;
		 
   -- END CATCH
    	
END




























GO
/****** Object:  StoredProcedure [dbo].[SkillsGetById]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[SkillsGetById]
	(@SkillId bigint
    ,@QStatus int OUTPUT)    
        
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.	
   SET NOCOUNT ON;

    -- Insert statements for procedure here

    BEGIN TRY     
		 SELECT * FROM Skills  WHERE SkillId  = @SkillId
		 SELECT @QStatus = 1;		 
    END TRY
    
    BEGIN CATCH    
		 SELECT @QStatus = -1;		 
    END CATCH	
	 
END




























GO
/****** Object:  StoredProcedure [dbo].[SkillsGetExcludingIds]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[SkillsGetExcludingIds]
    @SkillId NVARCHAR(MAX),
    @QStatus INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        SELECT * 
        FROM Skills 
        WHERE SkillId NOT IN (SELECT Item FROM dbo.SplitStringToTable(@SkillId, ','))

        SELECT @QStatus = 1;
    END TRY
    BEGIN CATCH
        SELECT @QStatus = -1;
    END CATCH
END




GO
/****** Object:  StoredProcedure [dbo].[SkillsGetList]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[SkillsGetList]
    @QStatus int output
        
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    
    BEGIN TRY 
       
		SELECT * FROM Skills where IsActive=1 Order by SkillName Asc	
		SELECT @QStatus = 1;
			
    END TRY
    BEGIN CATCH
    
		 SELECT @QStatus = -1;
		 
    END CATCH
    	
END




























GO
/****** Object:  StoredProcedure [dbo].[SkillsGetListByVariable]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SkillsGetListByVariable]
	 (@PageNo int
     ,@Items int
     ,@Search nvarchar(126)
     ,@Total int output
     ,@Sort nvarchar(126))
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.	
	SET NOCOUNT ON;
	
	DECLARE @Start int, @End INT ;
	DECLARE @Query nvarchar(max);
	
    -- Insert statements for procedure here
    
    CREATE TABLE #temp(Rid INT PRIMARY KEY IDENTITY(1,1)
				,SkillId bigint 
				,SkillName varchar(512)
				,IsActive bit
				,CreatedBy nvarchar(256)
				,CreatedDate datetime 
				,UpdatedBy nvarchar(256)
				,UpdatedDate datetime
				)
	
	SELECT @Start = (@PageNo - 1) * @Items  
	   
	SELECT @End = @Start + @Items 
	
	SET @Query = 'INSERT INTO #temp 
		 SELECT  SK.SkillId 
				,SK.SkillName 
				,SK.IsActive
				,SK.CreatedBy
				,SK.CreatedDate
				,SK.UpdatedBy 
				,SK.UpdatedDate 
		FROM Skills SK  WHERE SK.SkillId <> 0 '
	
	
	IF(@Search <> '')
	
    BEGIN
		SET @Query = @Query + ' AND  SK.SkillName LIKE ''%' + @Search + '%'''
    END
	
	IF(@Sort <> '')
	
	BEGIN
		SET @Query = @Query + ' ORDER BY ' + @Sort
	END
	
	 	
	EXEC sp_ExecuteSQL @Query;
	
	SELECT @Total = COUNT(Rid) from #temp 
			
	SELECT * FROM #temp WHERE Rid > @Start AND  Rid <= @End
	
	  DROP TABLE #temp

END



























GO
/****** Object:  StoredProcedure [dbo].[SkillsInsert]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SkillsInsert]
    (@SkillId bigint 
	,@SkillName Nvarchar(256)
	,@IsActive bit
	,@CreatedBy nvarchar(256)
	,@CreatedDate datetime
	,@UpdatedBy nvarchar(256)
	,@UpdatedDate datetime
	,@QStatus int output)  
        
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.	
	SET NOCOUNT ON;
    -- Insert statements for procedure here
    
    --BEGIN TRY
    --BEGIN TRANSACTION
    
		 IF((SELECT COUNT(*) FROM Skills WHERE SkillId = @SkillId) = 0)
		BEGIN
			INSERT INTO Skills VALUES
			        (@SkillName	 
	                ,@IsActive
					,@CreatedDate
	                ,@CreatedBy
	                ,@UpdatedDate
	                ,@UpdatedBy
					)

					Set @SkillId = SCOPE_IDENTITY()

		SELECT @QStatus = 1;	
		END
		ELSE BEGIN
		
			UPDATE Skills
			   SET 
				   SkillName=@SkillName
				  ,UpdatedBy=@UpdatedBy
				  ,UpdatedDate=@UpdatedDate
			 WHERE  SkillId = @SkillId
			 		 
			 SELECT @QStatus = 2;
			 
		 END
		 
		
	--COMMIT TRANSACTION
	--END TRY
    
 --   BEGIN CATCH     
	--   ROLLBACK TRANSACTION 
 --      SELECT @QStatus = -1;           
 --   END CATCH   
    
END


























GO
/****** Object:  StoredProcedure [dbo].[SkillsNameByGetByNameAvailability]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[SkillsNameByGetByNameAvailability]
	(@SkillName nvarchar(max)
    ,@QStatus int OUTPUT)    
        
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.	
   SET NOCOUNT ON;

    -- Insert statements for procedure here

    BEGIN TRY     
		 SELECT * FROM Skills  WHERE SkillName  = @SkillName
		 SELECT @QStatus = 1;		 
    END TRY
    
    BEGIN CATCH   
		 SELECT @QStatus = -1;		 
    END CATCH	
	 
END



GO
/****** Object:  StoredProcedure [dbo].[SkillsUpdateStatus]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[SkillsUpdateStatus]
    (@SkillId bigint
    ,@QStatus int output)  
        
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.	
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    
    BEGIN TRY    
    BEGIN TRANSACTION
    
		IF((SELECT IsActive FROM Skills WHERE SkillId = @SkillId) = 1)
		BEGIN
			UPDATE Skills SET IsActive = 0 WHERE SkillId = @SkillId
		END
		ELSE
		BEGIN
			UPDATE Skills SET IsActive = 1 WHERE SkillId = @SkillId
		END		
		
		SELECT @QStatus = 1;
		
	COMMIT TRANSACTION
    END TRY

    BEGIN CATCH    
         ROLLBACK TRANSACTION
		 SELECT @QStatus = -1;		 
    END CATCH
    	
END





















GO
/****** Object:  StoredProcedure [dbo].[UpdateInvestorsForm]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[UpdateInvestorsForm]
    @InvestorId BIGINT,
    @InvestorNo NVARCHAR(20),
    @FirstName NVARCHAR(256),
    @LastName NVARCHAR(256),
    @Email NVARCHAR(128),
    @PhoneNo NVARCHAR(64),
    @WhatsAppNo NVARCHAR(64),
    @Password NVARCHAR(256),
    @DateOfBirth DATETIME,
    @PassPortNo NVARCHAR(30),
    @SSNNo NVARCHAR(50),
    @DrivingNo NVARCHAR(50),
    @Quantity NVARCHAR(128),
    @USDAmount NVARCHAR(128),
    @InvestType NVARCHAR(128),
    @LegalFirstName NVARCHAR(256),
    @LegalLastName NVARCHAR(256),
    @MyselfAddress1 NVARCHAR(2048),
    @MyselfAddress2 NVARCHAR(2048),
    @MyselfCity NVARCHAR(128),
    @MyselfState NVARCHAR(128),
    @MyselfCountry NVARCHAR(128),
    @MyselfZipCode NVARCHAR(64),
    @PaymentType NVARCHAR(256),
    @UpdatedBy NVARCHAR(256),
    @QStatus INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE Investors
        SET 
            FirstName       = @FirstName,
            LastName        = @LastName,
            Email           = @Email,
            PhoneNo         = @PhoneNo,
            WhatsAppNo      = @WhatsAppNo,
            --Password        = @Password,
            DateOfBirth     = @DateOfBirth,
            PassPortNo      = @PassPortNo,
            SSNNo           = @SSNNo,
            DrivingNo       = @DrivingNo,
            Quantity        = @Quantity,
            USDAmount       = @USDAmount,
            InvestType      = @InvestType,
            LegalFirstName  = @LegalFirstName,
            LegalLastName   = @LegalLastName,
            MyselfAddress1  = @MyselfAddress1,
            MyselfAddress2  = @MyselfAddress2,
            MyselfCity      = @MyselfCity,
            MyselfState     = @MyselfState,
            MyselfCountry   = @MyselfCountry,
            MyselfZipCode   = @MyselfZipCode,
            PaymentType     = @PaymentType,
            UpdatedDate     = CONVERT(DATETIME, SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:30')),
            UpdatedBy       = @UpdatedBy
        WHERE InvestorId = @InvestorId;

        SELECT @QStatus = 2; -- success

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        SELECT @QStatus = -1; -- failure
    END CATCH
END

GO
/****** Object:  StoredProcedure [dbo].[UserRolesGetList]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[UserRolesGetList]
    @QStatus int output
        
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    
    BEGIN TRY 
       
		SELECT R.RoleId,R.RoleName FROM UserRoles UR
		left join  Roles R on R.RoleId = UR.RoleId
		
		SELECT @QStatus = 1;
			
    END TRY
    BEGIN CATCH
    
		 SELECT @QStatus = -1;
		 
    END CATCH
    	
END






























GO
/****** Object:  StoredProcedure [dbo].[UsersChangePassword]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[UsersChangePassword]
    (@UserId bigint
    ,@Password nvarchar(756)
    ,@QStatus int output )  
        
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    
    BEGIN TRY    
		
		UPDATE Users 
		SET Password = @Password
		,LastPasswordChangedDate = GETDATE()
		WHERE UserId =@UserId 		
		
		SELECT @QStatus = 1;		
    END TRY
    BEGIN CATCH
		 SELECT @QStatus = -1;
    END CATCH
    	
END


GO
/****** Object:  StoredProcedure [dbo].[UsersDelete]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[UsersDelete]
    (@UserId bigint
    ,@QStatus int output)  
        
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    
    --BEGIN TRY    
    BEGIN TRANSACTION
    
			DELETE FROM Users WHERE UserId = @UserId
		
		SELECT @QStatus = 1;
	
	COMMIT TRANSACTION	
   -- END TRY
   -- BEGIN CATCH
    
   --      ROLLBACK TRANSACTION
		 --SELECT @QStatus = -1;
		 
   -- END CATCH
    	
END






























GO
/****** Object:  StoredProcedure [dbo].[UsersGetByEmail]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[UsersGetByEmail]
	(@Email nvarchar(128)
    ,@QStatus int OUTPUT)    
        
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	
   SET NOCOUNT ON;

    -- Insert statements for procedure here

    BEGIN TRY 
	DECLARE @UserId bigint

		SET @UserId = (SELECT Top(1) UserId FROM Users WHERE Email=@Email)
    
		 SELECT    *	
				  ,(SELECT CAST((SELECT CAST(RoleId as nvarchar(80)) + ',' FROM Roles R  WHERE RoleId IN (SELECT RoleId FROM UserRoles WHERE RoleId=R.RoleId AND UserId=(SELECT UserId FROM Users WHERE UserId=@UserId)) FOR XML PATH('')) AS NVARCHAR(max))) as RoleIds
				  ,(SELECT CAST((SELECT CAST(RoleName as nvarchar(80)) + ',' FROM Roles R  WHERE RoleId IN (SELECT RoleId FROM UserRoles WHERE RoleId=R.RoleId AND UserId=(SELECT UserId FROM Users WHERE UserId=@UserId)) FOR XML PATH('')) AS NVARCHAR(max))) as RoleName
			  			  
			  FROM Users U  
			  WHERE U.UserId = @UserId and isactivated=1

	
		
		 SELECT @QStatus = 1;
		 
    END TRY
    
    BEGIN CATCH
    
		 SELECT @QStatus = -1;
		 
    END CATCH	
	 
END






GO
/****** Object:  StoredProcedure [dbo].[UsersGetById]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[UsersGetById]
	(@UserId bigint
    ,@QStatus int OUTPUT)    
        
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.	
   SET NOCOUNT ON;

    -- Insert statements for procedure here

    BEGIN TRY     

		SELECT U.*,
			   (CASE WHEN UR.RoleId IS NULL THEN NULL
					 ELSE CAST(UR.RoleId AS nvarchar(MAX)) -- Cast to nvarchar(MAX) for flexibility
				END) AS RoleIds,
			   R.RoleName  -- Include RoleName from a potential Roles table (optional)
			FROM Users U
			LEFT JOIN UserRoles UR ON UR.UserId = U.UserId
			LEFT JOIN Roles R ON R.RoleId = UR.RoleId  -- Optional join for role names
			WHERE U.UserId = @UserId;

		 SELECT * FROM UserEducations  WHERE UserId  = @UserId

		 SELECT * FROM UserEmploymentHistory  WHERE UserId  = @UserId

		 SELECT @QStatus = 1;		 
    END TRY
    
    BEGIN CATCH    
		 SELECT @QStatus = -1;		 
    END CATCH	
	 
END




























GO
/****** Object:  StoredProcedure [dbo].[UsersGetByUserName]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[UsersGetByUserName]
    @UserName nvarchar(128), 				
    @QStatus int output   
        
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    BEGIN TRY
    Update Users SET LastLoginDate=GETDATE() WHERE FirstName = @UserName		
			SELECT U.UserId
				  ,U.FirstName
				  ,U.Email
				  ,U.Password
				 -- ,U.Designation
				 -- ,U.MobilePhone
				  ,U.IsApproved
				  ,U.IsLockedOut
				  ,U.IsActivated
				  ,U.DateActivated
				  ,U.RegistrationGUID
				  ,U.FailedPasswordAttemptCount
				  ,U.LastPasswordChangedDate
				  ,U.LastLoginDate
				  --,U.InsertedTime
				 -- ,U.InsertedBy
				  ,U.UpdatedDate
				  ,U.UpdatedBy
				  ,(SELECT CAST((SELECT CAST(RoleId as nvarchar(80)) + ',' FROM Roles R  WHERE RoleId IN (SELECT RoleId FROM UserRoles WHERE RoleId=R.RoleId AND UserId=(SELECT UserId FROM Users WHERE FirstName=@UserName)) FOR XML PATH('')) AS NVARCHAR(max))) as RoleIds
				  ,(SELECT CAST((SELECT CAST(RoleName as nvarchar(80)) + ',' FROM Roles R  WHERE RoleId IN (SELECT RoleId FROM UserRoles WHERE RoleId=R.RoleId AND UserId=(SELECT UserId FROM Users WHERE FirstName=@UserName)) FOR XML PATH('')) AS NVARCHAR(max))) as RoleName
			  FROM Users U  	    
			  WHERE U.FirstName = @UserName			
				SELECT @QStatus=1;
	    END TRY
	    
	    BEGIN CATCH
	          SELECT @QStatus=-1;
	    END CATCH
	 
END  








GO
/****** Object:  StoredProcedure [dbo].[UsersGetList]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UsersGetList]
    @QStatus int output
        
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    
    BEGIN TRY 
       
		SELECT U.* FROM Users U
		
		left join UserRoles UR on U.userid=UR.userid where UR.Roleid=32 Order by FirstName Asc	
		SELECT @QStatus = 1;
			
    END TRY
    BEGIN CATCH
    
		 SELECT @QStatus = -1;
		 
    END CATCH
    	
END






























GO
/****** Object:  StoredProcedure [dbo].[UsersGetListByVariable]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--1,20,'',0,''
--'','UserId ASC',1,20,0
CREATE PROCEDURE [dbo].[UsersGetListByVariable]
	 (@Search nvarchar(126)
	 ,@Sort nvarchar(126)
	 ,@PageNo int
     ,@Items int
	 ,@IsActivated bigint
     ,@Total int output
     )
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.	
	SET NOCOUNT ON;
	
	DECLARE @Start int, @End INT ;
	DECLARE @Query nvarchar(max);
	
    -- Insert statements for procedure here
    
    CREATE TABLE #temp(Rid INT PRIMARY KEY IDENTITY(1,1)
				,UserId	bigint
				,FirstName	varchar(512)
				,MiddleName	varchar(512)
				,LastName	varchar(512)
				,UserNumber	varchar(512)
				,Password	varchar(512)
				,Gender	varchar(512)
				,DOB	datetime
				,SSN	varchar(512)
				,Phone	varchar(512)
				,HomePhone	varchar(512)
				,Email	varchar(512)
				,Email1	varchar(512)
				,ProfileImageUrl	varchar(512)
				,Status	varchar(512)
				,IsActivated bit
				,CreatedDate	datetime
				,CreatedBy	nvarchar(512)
				,UpdatedDate	datetime
				,UpdatedBy	nvarchar(512)
				,RoleName	nvarchar(215)
				,Candidatecount bigint
				)
	
	SELECT @Start = (@PageNo - 1) * @Items  
	   
	SELECT @End = @Start + @Items 
	
	SET @Query = 'INSERT INTO #temp 
		 SELECT  U.UserId 
				,U.FirstName
				,U.MiddleName
				,U.LastName
				,U.UserNumber
				,U.Password
				,U.Gender
				,U.DOB
				,U.SSN
				,U.Phone
				,U.HomePhone
				,U.Email
				,U.Email1
				,U.ProfileImageUrl
				,U.Status
				,U.IsActivated
				,U.CreatedDate
				,U.CreatedBy
				,U.UpdatedDate
				,U.UpdatedBy 
				,R.RoleName
				,(select count(*) from BenchCandidateUsers where UserId=U.UserId) as Candidatecount
		FROM Users U  
			LEFT JOIN UserRoles UR ON UR.UserId = U.UserId
			LEFT JOIN Roles R ON R.RoleId = UR.RoleId  
			WHERE U.UserId <> 2 and U.UserId <> 191'
	
	IF (@Search <> '')
BEGIN
    SET @Query = @Query + ' AND (' +
                 '  (CASE WHEN U.FirstName <> '''' AND U.LastName <> '''' THEN U.FirstName + '' '' + U.LastName ' +
                 '        ELSE U.FirstName END) ' +
                 '  LIKE ''%' + @Search + '%'' ' +
                 '  OR U.Email LIKE ''%' + @Search + '%'' ' +
                 '  OR U.Phone LIKE ''%' + @Search + '%'' ' +
                 '  OR U.CountryName LIKE ''%' + @Search + '%'' ' +
                 ')'
END

IF (@IsActivated =0)
BEGIN
    SET @Query = @Query + ' AND U.IsActivated = 0'
END
IF (@IsActivated =1)
BEGIN
    SET @Query = @Query + ' AND U.IsActivated = 1' 
END

IF (@IsActivated =3)
BEGIN
    SET @Query = @Query + ' AND U.IsActivated = 0 and YEAR(E.CreatedDate) < YEAR(GETDATE())' 
END
IF (@IsActivated =4)
BEGIN
    SET @Query = @Query + 'AND U.IsActivated = 1 AND YEAR(E.CreatedDate) = YEAR(GETDATE())' 
END
IF (@IsActivated =5)
BEGIN
    SET @Query = @Query + ' AND YEAR(E.CreatedDate) = YEAR(GETDATE())' 
END

	
	IF(@Sort <> '')
	
	BEGIN
		SET @Query = @Query + ' ORDER BY ' + @Sort
	END
	
	 	print @Query
	EXEC sp_ExecuteSQL @Query;
	
	SELECT @Total = COUNT(Rid) from #temp 
			
	SELECT * FROM #temp WHERE Rid > @Start AND  Rid <= @End
	
	  DROP TABLE #temp

END






























GO
/****** Object:  StoredProcedure [dbo].[UsersGetPassword]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


create PROCEDURE [dbo].[UsersGetPassword]

@UserId int,
@QStatus int output

AS
BEGIN

		-- SET NOCOUNT ON added to prevent extra result sets from
		-- interfering with SELECT statements.

		SET NOCOUNT ON;

		-- Insert statements for procedure here

		BEGIN TRY
			SELECT Password FROM Users WHERE UserId = @UserId
			SELECT @QStatus = 1
		END TRY
		
		BEGIN CATCH
		    SELECT @QStatus = -1
		END CATCH

END












GO
/****** Object:  StoredProcedure [dbo].[UsersInsert]    Script Date: 04-03-2026 17:46:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[UsersInsert]
    @UserId bigint OUTPUT,
    @FirstName varchar(256),
    @MiddleName varchar(256),
    @LastName varchar(256),
    @UserNumber varchar(512),
    @Password varchar(max),
    @Gender varchar(256),
    @DOB datetime,
    @SSN varchar(256),
    @Phone varchar(256),
    @HomePhone varchar(256),
    @Email varchar(256),
    @Email1 varchar(256),
    @ProfileImageUrl varchar(256),
    @AadharNumber varchar(256),
    @PANNumber varchar(256),
    @PassportNo varchar(256),
    @DateofIssue datetime,
    @DateofExpiry datetime,
    @CountryIssued varchar(256),
    @CountryName varchar(256),
    @StateName varchar(256),
    @CityName varchar(256),
    @ZipCode varchar(256),
    @PermanentAddress varchar(max),
    @MailingAddress varchar(max),
    @EmergencyName varchar(256),
    @EmergencyPhone varchar(256),
	@EmergencyCountyCode varchar(16),
	@EmergencyCountyCode1 varchar(16),
	@EmergencyRelation varchar(256),
	@CountyCode varchar(16),
    @Everworked varchar(256),
    @WorkedName varchar(256),
    @Status varchar(256),
    @IsApproved bit,
    @IsLockedOut bit,
    @IsActivated bit,
    --@DateActivated datetime,
    @RegistrationGUID uniqueidentifier,
    @FailedPasswordAttemptCount int,
  --  @LastPasswordChangedDate datetime,
   -- @LastLoginDate datetime,
    @CreatedDate datetime,
    @CreatedBy nvarchar(512),
    @UpdatedDate datetime,
    @UpdatedBy nvarchar(512),
    @QStatus int OUTPUT,
    @RoleIds nvarchar(max),
    @Qualifications nvarchar(max),
    @Experiences nvarchar(max)
	,@EmergencyName1 nvarchar(126)
,@EmergencyPhone1 nvarchar(126)
,@EmergencyRelation1 nvarchar(126)
,@HomePhoneCountryCode nvarchar(16)
,@HireDate datetime,
@StartDate datetime
,@MonthlySalary decimal(15,2)
,@AnnualSalary decimal(15,2)
,@PayFrequency nvarchar(126)
,@BonusPerPlacement nvarchar(126)
AS
BEGIN
    SET NOCOUNT ON;



    IF ((SELECT COUNT(*) FROM Users WHERE UserId = @UserId OR Email=@Email) = 0)
    BEGIN
        INSERT INTO Users (
            FirstName, MiddleName, LastName, UserNumber, Password, Gender, DOB, SSN, Phone, 
            HomePhone, Email, Email1, ProfileImageUrl, AadharNumber, PANNumber, PassportNo, 
            DateofIssue, DateofExpiry, CountryIssued, CountryName, StateName, CityName, 
            ZipCode, PermanentAddress, MailingAddress, EmergencyName, EmergencyPhone, 
            Everworked, WorkedName, Status, IsApproved, IsLockedOut, IsActivated, 
            RegistrationGUID, FailedPasswordAttemptCount, 
           CreatedDate, CreatedBy, UpdatedDate, UpdatedBy,EmergencyCountyCode,EmergencyCountyCode1,EmergencyRelation,CountyCode,
			EmergencyName1,EmergencyPhone1,EmergencyRelation1,HomePhoneCountryCode,HireDate,StartDate,MonthlySalary,AnnualSalary,PayFrequency,BonusPerPlacement
        ) VALUES (
            @FirstName, @MiddleName, @LastName, @UserNumber, @Password, @Gender, @DOB, @SSN, @Phone, 
            @HomePhone, @Email, @Email1, @ProfileImageUrl, @AadharNumber, @PANNumber, @PassportNo, 
            @DateofIssue, @DateofExpiry, @CountryIssued, @CountryName, @StateName, @CityName, 
            @ZipCode, @PermanentAddress, @MailingAddress, @EmergencyName, @EmergencyPhone, 
            @Everworked, @WorkedName, @Status, 1, @IsLockedOut, 1, 
           @RegistrationGUID, @FailedPasswordAttemptCount,
            @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy,@EmergencyCountyCode,@EmergencyCountyCode1,@EmergencyRelation,@CountyCode,
			@EmergencyName1,@EmergencyPhone1,@EmergencyRelation1,@HomePhoneCountryCode,@HireDate,@StartDate,@MonthlySalary,@AnnualSalary,@PayFrequency,@BonusPerPlacement
        );

        SET @UserId = SCOPE_IDENTITY();
        SET @QStatus = 1;
    END
    ELSE
    BEGIN
        UPDATE Users SET
            FirstName = @FirstName,
            MiddleName = @MiddleName,
            LastName = @LastName,
            UserNumber = @UserNumber,
            Gender = @Gender,
            DOB = @DOB,
            SSN = @SSN,
            Phone = @Phone,
            HomePhone = @HomePhone,
            Email = @Email,
            Email1 = @Email1,
            AadharNumber = @AadharNumber,
            PANNumber = @PANNumber,
            PassportNo = @PassportNo,
            DateofIssue = @DateofIssue,
            DateofExpiry = @DateofExpiry,
            CountryIssued = @CountryIssued,
            ProfileImageUrl = @ProfileImageUrl,
            CountryName = @CountryName,
            StateName = @StateName,
            CityName = @CityName,
            ZipCode = @ZipCode,
            PermanentAddress = @PermanentAddress,
            MailingAddress = @MailingAddress,
            EmergencyName = @EmergencyName,
            EmergencyPhone = @EmergencyPhone,
            WorkedName = @WorkedName,
           -- LastPasswordChangedDate = @LastPasswordChangedDate,
            --LastLoginDate = @LastLoginDate,
            UpdatedDate = @UpdatedDate,
            UpdatedBy = @UpdatedBy,
			EmergencyCountyCode=@EmergencyCountyCode,
			EmergencyRelation=@EmergencyRelation,
			CountyCode=@CountyCode,
			EmergencyName1=@EmergencyName1,
            EmergencyPhone1=@EmergencyPhone1,
            EmergencyRelation1=@EmergencyRelation1,
			HomePhoneCountryCode=@HomePhoneCountryCode
			,HireDate=@HireDate
			,StartDate=@StartDate
			,MonthlySalary=@MonthlySalary
			,AnnualSalary=@AnnualSalary
			,PayFrequency=@PayFrequency
			,BonusPerPlacement=@BonusPerPlacement
			,EmergencyCountyCode1=@EmergencyCountyCode1
        WHERE UserId = @UserId;

        SET @QStatus = 2;
    END

    -- User Roles
    IF (SELECT COUNT(*) FROM UserRoles WHERE UserId = @UserId) = 0
        BEGIN
            DECLARE @RoleIdTable TABLE (RoleId NVARCHAR(MAX));
            INSERT INTO @RoleIdTable (RoleId)
            SELECT Item FROM dbo.fn_SplitString(@RoleIds, ',');

            INSERT INTO UserRoles (UserId, RoleId)
            SELECT @UserId, RoleId FROM @RoleIdTable;
            SET @QStatus = 1;
        END


		   
IF(@Qualifications <> '')
		BEGIN
			  DELETE FROM UserEducations WHERE UserId = @UserId;
			
			DECLARE @QuaXML1 AS XML	        
			SELECT @QuaXML1 = @Qualifications 
			CREATE TABLE #Quatemp(Rid INT PRIMARY KEY IDENTITY(1,1),	
			    CollegeName nvarchar(max), 				
				Address nvarchar(max),
				Qualification nvarchar(max),
				Percentage nvarchar(max),
				BackLogs nvarchar(max))
				
			DECLARE @QuaRowCount1 AS int
			DECLARE @QuaCurrentRow1 AS int	
	
			INSERT INTO #Quatemp(
			    CollegeName,
				Address,
				Qualification,
				Percentage,
				BackLogs
				)

				select 
					M.Item.value('CollegeName[1]','nvarchar(max)'),
					M.Item.value('Address[1]','nvarchar(max)'),
					M.Item.value('Qualification[1]','nvarchar(max)'),
					M.Item.value('Percentage[1]','nvarchar(max)'),
					M.Item.value('BackLogs[1]','nvarchar(max)')
				FROM @QuaXML1.nodes('/ArrayOfUserEducations/UserEducations') AS M(Item)
					
			SET @QuaRowCount1 = @@ROWCOUNT

			IF(@QuaRowCount1 <> 0)
			BEGIN 
				 INSERT INTO UserEducations (UserId, CollegeName, Address, Qualification, Percentage, BackLogs, InsertedDate, InsertedBy, UpdatedDate, UpdatedBy)

										Select @UserId,CollegeName,Address, Qualification ,Percentage,BackLogs, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy from #Quatemp 

				DROP TABLE #Quatemp	
			END
		END




		IF(@Experiences <> '')
		BEGIN
			  DELETE FROM UserEmploymentHistory WHERE UserId = @UserId;
			
			DECLARE @QuaXML2 AS XML	        
			SELECT @QuaXML2 = @Experiences 
		  CREATE TABLE #Expertemp (
            Rid INT PRIMARY KEY IDENTITY(1,1),
            Type nvarchar(max),
            Name nvarchar(max),
            JobTitle nvarchar(max),
            EAddress nvarchar(max),
            Phone nvarchar(max),
            FromDate datetime,
            ToDate datetime,
            SupervisorName nvarchar(max),
            SupervisorPhone nvarchar(max),
            StartSalary nvarchar(max),
            EndSalary nvarchar(max),
            ReasonForLeaving nvarchar(max),
			CountryCode nvarchar(max),
			SupervisorCountryCode nvarchar(max),
        );
				
			DECLARE @QuaRowCount2 AS int
			DECLARE @QuaCurrentRow2 AS int	
	
			 INSERT INTO #Expertemp (Type, Name, JobTitle, EAddress, Phone, FromDate, ToDate, SupervisorName, SupervisorPhone, StartSalary, EndSalary, ReasonForLeaving,CountryCode,SupervisorCountryCode)

					
			SELECT 
            M.Item.value('Type[1]', 'nvarchar(max)'),
            M.Item.value('Name[1]', 'nvarchar(max)'),
            M.Item.value('JobTitle[1]', 'nvarchar(max)'),
            M.Item.value('EAddress[1]', 'nvarchar(max)'),
            M.Item.value('Phone[1]', 'nvarchar(max)'),
           CASE 
        WHEN M.Item.value('FromDate[1]', 'datetime2') < '1753-01-01' THEN NULL
        ELSE M.Item.value('FromDate[1]', 'datetime2')
    END AS FromDate,
    CASE 
        WHEN M.Item.value('ToDate[1]', 'datetime2') < '1753-01-01' THEN NULL
        ELSE M.Item.value('ToDate[1]', 'datetime2')
    END AS ToDate,
            M.Item.value('SupervisorName[1]', 'nvarchar(max)'),
            M.Item.value('SupervisorPhone[1]', 'nvarchar(max)'),
            M.Item.value('StartSalary[1]', 'nvarchar(max)'),
            M.Item.value('EndSalary[1]', 'nvarchar(max)'),
            M.Item.value('ReasonForLeaving[1]', 'nvarchar(max)'),
			 M.Item.value('CountryCode[1]', 'nvarchar(max)'),
			 M.Item.value('SupervisorCountryCode[1]', 'nvarchar(max)')
			 
        FROM @QuaXML2.nodes('/ArrayOfUserEmploymentHistory/UserEmploymentHistory') AS M(Item);
		SET @QuaRowCount2 = @@ROWCOUNT

			IF(@QuaRowCount2 <> 0)
			BEGIN 
				  INSERT INTO UserEmploymentHistory (UserId, Type, Name, JobTitle, EAddress, Phone, FromDate, ToDate, SupervisorName, SupervisorPhone, StartSalary, EndSalary, ReasonForLeaving, InsertedDate, InsertedBy, UpdatedDate, UpdatedBy,CountryCode,SupervisorCountryCode)
        SELECT @UserId, Type, Name, JobTitle, EAddress, Phone, FromDate, ToDate, SupervisorName, SupervisorPhone, StartSalary, EndSalary, ReasonForLeaving, @CreatedDate, @CreatedBy, @CreatedDate, @CreatedBy,CountryCode,SupervisorCountryCode
        FROM #Expertemp WHERE Type IS NOT NULL;
