WebAPI-DealerSelection/Source/DealerSelection.WebApi/Scripts/003.AddStoredProcedure.sql

/****** Object:  StoredProcedure [DealerSelection].[usp_get_24HrOldRecordsFor_jobProcessing]    Script Date: 1/2/2024 12:08:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE   PROCEDURE [DealerSelection].[usp_get_24HrOldRecordsFor_jobProcessing]
 @BuId Int
AS
BEGIN
	DECLARE @BuCode AS VARCHAR(10)
	SELECT @BuCode = BuCode FROM tbl_businessUnit WHERE  BuId=@buId 

SELECT top 1 cust.BuId,@BuCode as BuName,trans.BookingId,ModelName,ModelCode, ColorCode,CustomerName,CustomerLat,CustomerLong,
 MobileNumber,Pincode,DealerCode,cust.RecordId,ReferrerUrl as ReferralUrl,IsWhatsAppOptIn,IsRegisterInterestRequest,
 UtmCustomDetails1,UtmCustomDetails2, trans.Status,trans.CCTransactionId,
    trans.ReceiptId,trans.AmountPaid
FROM [tbl_customer_information] cust INNER JOIN
	[tbl_transaction_details] trans ON cust.RecordId = trans.RecordId 
	AND cust.BuId = trans.BuId
	WHERE DealerSelectionJobStatus in (0,1,2)-- ReadyForJob,Picked,SentToBot
	and trans.CompletionDate < DATEADD(HOUR, -24, GETDATE()) 
	and trans.Status='Successful'
	order by trans.CreationDate desc

END
GO

/****** Object:  StoredProcedure [DealerSelection].[usp_get_CustomerDataForJob]    Script Date: 1/2/2024 12:08:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author, , Name>
-- Create Date: <Create Date, , >
-- Description: <Description, , >
-- =============================================
CREATE   PROCEDURE [DealerSelection].[usp_get_CustomerDataForJob]
(
    @buid int,
	@recordId int
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON
		DECLARE @BuCode AS VARCHAR(10)
	SELECT @BuCode = BuCode FROM tbl_businessUnit WHERE  BuId=@buId 

 SELECT top 1 cus.BuId,@BuCode as BuCode,trans.BookingId,ModelName,ModelCode, ColorCode,CustomerName,CustomerLat,CustomerLong,
 MobileNumber,Pincode,DealerCode,cus.RecordId,ReferrerUrl as ReferralUrl,IsWhatsAppOptIn,IsRegisterInterestRequest,
 UtmCustomDetails1,UtmCustomDetails2, trans.Status,trans.CCTransactionId,
    trans.ReceiptId,trans.AmountPaid
  FROM [dbo].[tbl_customer_information] as cus
  left join tbl_transaction_details as trans on cus.RecordId=trans.RecordId
WHERE trans.RecordId=@recordId and cus.BuId =@buid  and cus.RecordId=@recordId and trans.BuId=@buid
END
GO
/****** Object:  StoredProcedure [DealerSelection].[usp_get_CustomerDetailByBuCode]    Script Date: 1/2/2024 12:08:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE   PROCEDURE [DealerSelection].[usp_get_CustomerDetailByBuCode]
(
@BuCode VARCHAr(10),
@MobileNumber VARCHAr(50),
@BuSubType varchar(10),
@DealerSelectionJobStatus int
 )
AS
BEGIN

	SELECT c.BuId,c.RecordId FROM tbl_transaction_details T
	LEFT JOIN tbl_customer_information C ON
	T.RecordId = C.RecordId 
	WHERE T.BuId=C.BuId AND C.BuId in(select BuId from tbl_businessUnit where BuCode=@BuCode and BuSubType=@BuSubType )
	AND C.MobileNumber = @mobileNumber 
	AND (UPPER([CCTransactionStatus]) = 'SUCCESS' OR UPPER(CCTransactionStatus) = 'SHIPPED') 
	--AND c.DealerSelectionJobStatus=@dealerSelectionJobStatus

END
GO
/****** Object:  StoredProcedure [DealerSelection].[usp_get_dealer_selection_for_job]    Script Date: 1/2/2024 12:08:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author, , Name>
-- Create Date: <Create Date, , >
-- Description: <Description, , >
-- =============================================
CREATE   PROCEDURE [DealerSelection].[usp_get_dealer_selection_for_job]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON

 SELECT top 1 buid,recordid from tbl_customer_information order by RecordId desc

END
GO
/****** Object:  StoredProcedure [DealerSelection].[usp_get_ModelDetails]    Script Date: 1/2/2024 12:08:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE      PROCEDURE [DealerSelection].[usp_get_ModelDetails]
(
@buCode VARCHAR(10),
@BuSubType varchar(10),
@mobileNumber VARCHAR(12)
 )
AS
BEGIN

SELECT c.ModelCode FROM tbl_customer_information C
LEFT JOIN tbl_businessUnit b
ON c.buId = b.buId
where c.MobileNumber=@mobileNumber and b.BuCode=@buCode and b.BuSubType=@BuSubType

END
GO
/****** Object:  StoredProcedure [DealerSelection].[usp_isDuplicateMobileNumber]    Script Date: 1/2/2024 12:08:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      <Author, , Name>
-- Create Date: <Create Date, , >
-- Description: <Description, , >
-- =============================================

CREATE     PROCEDURE [DealerSelection].[usp_isDuplicateMobileNumber]
(
@buName VARCHAr(50),
@mobileNumber int,
@dealerSelectionJobStatus int
 )
AS
BEGIN
	DECLARE @SucessCount INT = 0
	DECLARE @BuId INT=0
	SET @BuId=(SELECT BUID FROM tbl_businessUnit WHERE BuName=@buName)

	SELECT c.BuId,c.RecordId FROM tbl_transaction_details T
	LEFT JOIN tbl_customer_information C ON
	T.RecordId = C.RecordId 
	WHERE T.BuId=C.BuId AND C.BuId = @BuId 
	AND C.MobileNumber = @mobileNumber 
	AND (UPPER([CCTransactionStatus]) = 'SUCCESS' OR UPPER(CCTransactionStatus) = 'SHIPPED') 
	AND c.DealerSelectionJobStatus=@dealerSelectionJobStatus

	
	--IF @SucessCount > 1
	--BEGIN
	--	SELECT @SucessCount AS SucessCount
	--	RETURN
	--END
	
	--SELECT BookingId,T.CreationDate FROM tbl_transaction_details T
	--LEFT JOIN tbl_customer_information C ON
	--T.RecordId = C.RecordId 
	--WHERE T.BuId=C.BuId AND C.BuId = 1 
	--AND C.MobileNumber = @mobileNumber
	--AND (CCTransactionStatus IS NULL OR UPPER(CCTransactionStatus) = 'AWAITED')
	--order by t.CreationDate desc

END
GO
/****** Object:  StoredProcedure [DealerSelection].[usp_update_dealer_details]    Script Date: 1/2/2024 12:08:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE   PROCEDURE [DealerSelection].[usp_update_dealer_details]
(
    -- Add the parameters for the stored procedure here
	 @BuId INT,
	@RecordId INT,    
    @MobileNumber varchar(12),  
	@DealerCode varchar(100),
    @DealerName varchar(100),  
	@CustomerLat varchar(100),
	@CustomerLong varchar(100),
	@Pincode varchar(100) = '',
	@DealerSelectionJobStatus INT,
	@DealerSelectedMode varchar(50)
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON
   
 
UPDATE [dbo].[tbl_customer_information]
   SET  
	  [DealerCode]=@DealerCode
      ,[DealerName] = @DealerName	 
	  ,[CustomerLat]=@CustomerLat
	  ,[CustomerLong]=@CustomerLong	
	  ,[Pincode] = @Pincode
	  ,[DealerSelectionJobStatus]=@DealerSelectionJobStatus
	  ,[DealerSelectedMode] = @DealerSelectedMode
      WHERE RecordId=@RecordId  and BuId = @BuId and MobileNumber=@MobileNumber	 
	  	 
END
GO


/****** Object:  StoredProcedure [DealerSelection].[usp_update_dealer_details]    Script Date: 1/3/2024 4:01:31 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE or ALTER   PROCEDURE [DealerSelection].[usp_update_DealerSelectionJobStatus]
(
    -- Add the parameters for the stored procedure here
	@BuId INT,
	@RecordId INT,
	@DealerSelectionJobStatus INT
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON
   
 
UPDATE [dbo].[tbl_customer_information]
   SET 
   [DealerSelectionJobStatus]=@DealerSelectionJobStatus	
   WHERE RecordId=@RecordId  and BuId = @BuId 
	  	 
END