USE [SAIB_Remttance]
GO
/****** Object:  StoredProcedure [dbo].[Get_PromotionData_Modules]    Script Date: 12/18/2024 12.03.04 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Get_PromotionData_Modules]   
@PromotionID  int = 0,                   
@ProcedureType int = 0, -- 0 for Select, 1 for update, 2 for Insert and 3 for Delete                      
@ViewType int = 0,  -- 0 for View, 1 for Process                      
@Filter nvarchar(max) = null,                      
@Script nvarchar(max) = null output                      
AS                      
set nocount on                      
BEGIN   
 
	create table #tempdetailstable (
	PromotionId int,
	PromotionDesp nvarchar(100),
	PromotionType char(1),
	PromotionValue varchar(50),
	MainActive bit,
	PromotionTypeID int,
	PromotionTypeDesp nvarchar(50),
	Value1 nvarchar(50),
	Value2 nvarchar(50),
	DltActive bit,
	CREATEDDATE datetime,
	EnteredBy nvarchar(50),
	UPDATEDDATE datetime,
	UpdatedBy nvarchar(50),
	PromotionStatus varchar(10))

    create table #tempdetailstable1 (
	PromotionId int,
	PromotionDesp nvarchar(100),
	PromotionType char(1),
	PromotionValue varchar(50),
	MainActive bit,
	MinAmt nvarchar(50),
	Partnercode nvarchar(50),
	PaymentMode nvarchar(50),
	PromoCode nvarchar(50),
	proCIF nvarchar(50),
	ReceiveAgent nvarchar(50),
	Currency nvarchar(10),
	Country nvarchar(10),
	Segment nvarchar(20),
	CustType nvarchar(10),
	ProStartDate nvarchar(10),
	ProEndDate nvarchar(10),
	MaxAmt nvarchar(50),
	CREATEDDATE datetime,
	EnteredBy nvarchar(50),
	UPDATEDDATE datetime,
	UpdatedBy nvarchar(50),
	PromotionStatus varchar(10)
	)

	DECLARE @CurrentID INT
	DECLARE @MaxID INT
  
  	if (@PromotionID=0)
	Begin

	    INSERT INTO #tempdetailstable(PromotionId,PromotionDesp,PromotionType,PromotionValue,MainActive,PromotionTypeID,PromotionTypeDesp,
		Value1,Value2,DltActive,CREATEDDATE,EnteredBy,UPDATEDDATE,UpdatedBy,PromotionStatus)(
	    select  Distinct A.PromotionID as PromotionID,A.PromotionDesp as PromotionDesp,
		A.PromotionType,A.PromotionValue,A.Active as MainActive,'' as PromoTypeID,'' as PromotionTypeDesp,
		'' as Value1,'' as Value2, '' as DltActive,A.CREATEDDATE,A.EnteredBy,A.UPDATEDDATE,A.UpdatedBy,A.Promotion_Status
		from tbl_PromotionMasterMain A Left outer join tbl_PromotionMasterDetails B
		on A.PromotionID = B.PromotionID
		Union
	    select  Distinct B.PromotionID,'' as PromotionDesp,'' as PromotionType,'' as PromotionValue,'' as MainActive,
		B.PromoTypeID,PromoTypeDesp as PromotionTypeDesp, B.Value1,B.Value2,B.Active as DltActive,A.CREATEDDATE,A.EnteredBy,A.UPDATEDDATE,A.UpdatedBy,'' as Promotion_Status
		from tbl_PromotionMasterMain A Left outer join tbl_PromotionMasterDetails B
		on A.PromotionID = B.PromotionID)


		insert into #tempdetailstable1(PromotionId,PromotionDesp,PromotionType,PromotionValue,MainActive,CREATEDDATE,EnteredBy,UPDATEDDATE,UpdatedBy,PromotionStatus) 
		select PromotionId,PromotionDesp,PromotionType,PromotionValue,MainActive,CREATEDDATE,EnteredBy,UPDATEDDATE,UpdatedBy,PromotionStatus from #tempdetailstable where PromotionTypeID=0

		SELECT @CurrentID = MIN(PromotionId), @MaxID = MAX(PromotionId) FROM #tempdetailstable1

		WHILE @CurrentID <= @MaxID
		BEGIN
    -- Update the row in the table for the current PromotionID
		UPDATE #tempdetailstable1
    SET MinAmt = (select Value1 from #tempdetailstable where  PromotionTypeID=16 and PromotionId=@CurrentID),
	Partnercode = (select Value1 from #tempdetailstable where  PromotionTypeID=15 and PromotionId=@CurrentID),
	PaymentMode = (select Value1 from #tempdetailstable where  PromotionTypeID=14 and PromotionId=@CurrentID),
	PromoCode = (select Value1 from #tempdetailstable where  PromotionTypeID=13 and PromotionId=@CurrentID),
	proCIF = (select Value1 from #tempdetailstable where  PromotionTypeID=12 and PromotionId=@CurrentID),
	ReceiveAgent= (select Value1 from #tempdetailstable where  PromotionTypeID=11 and PromotionId=@CurrentID),
	Currency= (select Value1 from #tempdetailstable where  PromotionTypeID=10 and PromotionId=@CurrentID),
	Country= (select Value1 from #tempdetailstable where  PromotionTypeID=9 and PromotionId=@CurrentID),
	Segment = (select Value1 from #tempdetailstable where  PromotionTypeID=8 and PromotionId=@CurrentID),
	CustType= (select Value1 from #tempdetailstable where  PromotionTypeID=7 and PromotionId=@CurrentID),
	ProStartDate= (select Value1 from #tempdetailstable where  PromotionTypeID=6 and PromotionId=@CurrentID),
	ProEndDate = (select Value2 from #tempdetailstable where  PromotionTypeID=6 and PromotionId=@CurrentID),
	MaxAmt= (select Value1 from #tempdetailstable where  PromotionTypeID=5 and PromotionId=@CurrentID)
    WHERE PromotionID = @CurrentID
	
    -- Move to the next ID
		SET @CurrentID = @CurrentID + 1
		END


	select 
	PromotionId,
	PromotionDesp,
	PromotionType,
	PromotionValue,
	MainActive,
	MinAmt,
	Partnercode,
	PaymentMode,
	PromoCode,
	proCIF,
	Case ReceiveAgent when '-1' then 'All Receive Agent' 
	when NULL then NULL 
	when 'None' then 'None' else (select Agent_Name from tbl_agent_details where Agent_ID=ReceiveAgent) end as ReceiveAgent,
	Currency,
	case Country when null then null
	when 'None' then 'None'
	else (select CountryName from tbl_04_08_Country_Details where CountryShortName=Country) end as Country ,
	case Segment when null then null 
	when '0' then '0' else (select ProductTypeName from tblproducttype where ProductType=Segment) end as Segment,
	CustType,
	ProStartDate,
	ProEndDate,
	MaxAmt,
	CREATEDDATE,
	EnteredBy,
	UPDATEDDATE,
	UpdatedBy,
	PromotionStatus
	from #tempdetailstable1 order by PromotionId asc


	End
	Else
	Begin
			    INSERT INTO #tempdetailstable(PromotionId,PromotionDesp,PromotionType,PromotionValue,MainActive,PromotionTypeID,PromotionTypeDesp,
		Value1,Value2,DltActive,CREATEDDATE,EnteredBy,UPDATEDDATE,UpdatedBy,PromotionStatus)(
	    select  Distinct A.PromotionID as PromotionID,A.PromotionDesp as PromotionDesp,
		A.PromotionType,A.PromotionValue,A.Active as MainActive,'' as PromoTypeID,'' as PromotionTypeDesp,
		'' as Value1,'' as Value2, '' as DltActive,A.CREATEDDATE,A.EnteredBy,A.UPDATEDDATE,A.UpdatedBy,A.Promotion_Status
		from tbl_PromotionMasterMain A Left outer join tbl_PromotionMasterDetails B
		on A.PromotionID = B.PromotionID where A.PromotionID=@PromotionID
		Union
	    select  Distinct B.PromotionID,'' as PromotionDesp,'' as PromotionType,'' as PromotionValue,'' as MainActive,
		B.PromoTypeID,PromoTypeDesp as PromotionTypeDesp, B.Value1,B.Value2,B.Active as DltActive,A.CREATEDDATE,A.EnteredBy,A.UPDATEDDATE,A.UpdatedBy, '' as Promotion_Status
		from tbl_PromotionMasterMain A Left outer join tbl_PromotionMasterDetails B
		on A.PromotionID = B.PromotionID where A.PromotionID=@PromotionID)


		insert into #tempdetailstable1(PromotionId,PromotionDesp,PromotionType,PromotionValue,MainActive,CREATEDDATE,EnteredBy,UPDATEDDATE,UpdatedBy,PromotionStatus) 
		select PromotionId,PromotionDesp,PromotionType,PromotionValue,MainActive,CREATEDDATE,EnteredBy,UPDATEDDATE,UpdatedBy,PromotionStatus from #tempdetailstable where PromotionTypeID=0

		SELECT @CurrentID = MIN(PromotionId), @MaxID = MAX(PromotionId) FROM #tempdetailstable1

		WHILE @CurrentID <= @MaxID
		BEGIN
    -- Update the row in the table for the current PromotionID
		UPDATE #tempdetailstable1
    SET MinAmt = (select Value1 from #tempdetailstable where  PromotionTypeID=16 and PromotionId=@CurrentID),
	Partnercode = (select Value1 from #tempdetailstable where  PromotionTypeID=15 and PromotionId=@CurrentID),
	PaymentMode = (select Value1 from #tempdetailstable where  PromotionTypeID=14 and PromotionId=@CurrentID),
	PromoCode = (select Value1 from #tempdetailstable where  PromotionTypeID=13 and PromotionId=@CurrentID),
	proCIF = (select Value1 from #tempdetailstable where  PromotionTypeID=12 and PromotionId=@CurrentID),
	ReceiveAgent= (select Value1 from #tempdetailstable where  PromotionTypeID=11 and PromotionId=@CurrentID),
	Currency= (select Value1 from #tempdetailstable where  PromotionTypeID=10 and PromotionId=@CurrentID),
	Country= (select Value1 from #tempdetailstable where  PromotionTypeID=9 and PromotionId=@CurrentID),
	Segment = (select Value1 from #tempdetailstable where  PromotionTypeID=8 and PromotionId=@CurrentID),
	CustType= (select Value1 from #tempdetailstable where  PromotionTypeID=7 and PromotionId=@CurrentID),
	ProStartDate= (select Value1 from #tempdetailstable where  PromotionTypeID=6 and PromotionId=@CurrentID),
	ProEndDate = (select Value2 from #tempdetailstable where  PromotionTypeID=6 and PromotionId=@CurrentID),
	MaxAmt= (select Value1 from #tempdetailstable where  PromotionTypeID=5 and PromotionId=@CurrentID)
    WHERE PromotionID = @CurrentID
	
    -- Move to the next ID
		SET @CurrentID = @CurrentID + 1
		END
	select 
	PromotionId,
	PromotionDesp,
	PromotionType,
	PromotionValue,
	MainActive,
	MinAmt,
	Partnercode,
	PaymentMode,
	PromoCode,
	proCIF,
	Case ReceiveAgent when '-1' then 'All Receive Agent' 
	when NULL then NULL 
	when 'None' then 'None' else (select Agent_Name from tbl_agent_details where Agent_ID=ReceiveAgent) end as ReceiveAgent,
	Currency,
	case Country when null then null 
	when 'None' then 'None' else (select CountryName from tbl_04_08_Country_Details where CountryShortName=Country) end as Country ,
	case Segment when null then null 
	when '0' then '0' else (select ProductTypeName from tblproducttype where ProductType=Segment) end as Segment,
	CustType,
	ProStartDate,
	ProEndDate,
	MaxAmt,
	CREATEDDATE,
	EnteredBy,
	UPDATEDDATE,
	UpdatedBy,
	PromotionStatus
	from #tempdetailstable1  order by PromotionId asc
	End


  
END      
      
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[Promotion_Details_Add]

@PromoTypeID int=0,
--@PromoName nvarchar(50)=null,
@PromoTypeDesp nvarchar(50)=null,
@PromotionValue nvarchar(250)=null,
@MinAmt nvarchar(50)=null,
@MaxAmt nvarchar(50)=null,
@CustType nvarchar(50)=null,
@StartDate nvarchar(50)=null,
@EndDate nvarchar(50)=null,
@ProSegment nvarchar(50)=null,
@ProCountryID nvarchar(50)=null,
@ProCurrency nvarchar(50)=null,
@ProReceiveAgent nvarchar(50)=null,
@ProCIF nvarchar(50)=null,
@PromoCode nvarchar(20)=null,
@ProPaymentMode nvarchar(20)=null,
@ProPartnerCode nvarchar(50)=null, 
@IsActive bit=false,
@CreatedBy nvarchar(20)=null,
@CreatedOn datetime =null,
@UpdatedBy nvarchar(20)=null,
@UpdatedOn datetime=null,
@Filter nvarchar(max) = null,
@ProcedureType int =0, -- 0 for Select, 1 for update, 2 for Insert and 3 for Delete 
@ViewType int = 0  -- 0 for View, 1 for Process 
AS
BEGIN

DECLARE @msg nvarchar(MAX)
DECLARE @PromotionID int
DECLARE @CountryID nvarchar(50)
DECLARE @RECEAGENT NVARCHAR(50)

BEGIN TRY
	Declare @Script nvarchar(MAX)	
	--VIEW
	IF @ProcedureType=0
	BEGIN
			Select * from tbl_PromotionMasterDetails where PromotionID=30
	END

	--UPDATE
	IF @ProcedureType=1
	BEGIN
			Select * from tbl_PromotionMasterDetails
	END
	--INSERT
	IF @ProcedureType=2
	BEGIN
	 
	 if @ProCountryID <>'NO'
	 Begin
	 if @ProCountryID<>'None'
	 Begin
	-- set @ProCountryID='0'
	 --End
	 SET @CountryID=@ProCountryID	 
	 set @ProCountryID=(select CountryShortName from tbl_04_08_Country_Details where CountryID=@CountryID)
	 End
	 END

	 if @ProReceiveAgent <>'NO'
	 Begin
	 SET @RECEAGENT=@ProReceiveAgent
	 if @ProReceiveAgent<>'None'
	 begin
	 if @ProReceiveAgent='All Receive Agent'
	 begin
	 set @ProReceiveAgent='-1'
	 end
	 Else
	 Begin
	 set @ProReceiveAgent=(select Agent_ID from tbl_agent_details where Agent_Name=@RECEAGENT)
	 End
	 End
	 End

	 if @ProPaymentMode <>'NO'
	 Begin
	 if @ProPaymentMode<>'None'
	 Begin
	 --set @ProPaymentMode='0'
	 --End
	 --set @ProCurrency=(select CurrencyShortName from tbl_04_13_Currency where currencyname=@ProCurrency)
	 set @ProPaymentMode=(SELECT ModeOfPayment_Name FROM tbl_Mode_Of_Payment_Master where ModeOfPayment_ID=@ProPaymentMode)
	 end
	 End
	 		INSERT INTO tbl_PromotionMasterMain(Promotiondesp,PromotionType,PromotionValue,CREATEDDATE,EnteredBy)
		VALUES(@PromoTypeDesp,@PromoTypeID,@PromotionValue,Getdate(),@CreatedBy)

		set @PromotionID=(select MAX(PromotionID) from tbl_PromotionMasterMain)

	  --   INSERT INTO tbl_PromotionMasterDetails(PromotionID,PromoTypeID,PromoTypeDesp,Value1,Value2,Value3,Value4,Value5,Active,EnteredBy,CREATEDDATE)
		 --VALUES(@PromotionID,@PromoTypeID,@PromoName,@MinAmt,@MaxAmt,@CustType,@StartDate,@EndDate,@IsActive,@CreatedBy,Getdate(),@ProSegment,@ProCountryID,@ProCurrency,@ProReceiveAgent,@ProCIF,@PromoCode,@ProPaymentMode,@ProPartnerCode)

		 	 if @MinAmt='NO'
			 Begin
			 INSERT INTO tbl_PromotionMasterDetails(PromotionID,PromoTypeID,PromoTypeDesp,Value1,Active,EnteredBy,CREATEDDATE)
		     VALUES(@PromotionID,'16','Min Amount',NUll,0,@CreatedBy,Getdate())
			 End
			 Else
			 Begin
			    INSERT INTO tbl_PromotionMasterDetails(PromotionID,PromoTypeID,PromoTypeDesp,Value1,Active,EnteredBy,CREATEDDATE)
				VALUES(@PromotionID,'16','Min Amount',@MinAmt,@IsActive,@CreatedBy,Getdate())
			 End

			 if @MaxAmt='NO'
			 Begin
		 INSERT INTO tbl_PromotionMasterDetails(PromotionID,PromoTypeID,PromoTypeDesp,Value1,Active,EnteredBy,CREATEDDATE)
		 VALUES(@PromotionID,'5','Max Amount',NUll,0,@CreatedBy,Getdate())
			 End
			 Else
			 Begin
		 INSERT INTO tbl_PromotionMasterDetails(PromotionID,PromoTypeID,PromoTypeDesp,Value1,Active,EnteredBy,CREATEDDATE)
		 VALUES(@PromotionID,'5','Max Amount',@MaxAmt,@IsActive,@CreatedBy,Getdate())
			 End

			 if @CustType='NO'
			 Begin
		 INSERT INTO tbl_PromotionMasterDetails(PromotionID,PromoTypeID,PromoTypeDesp,Value1,Active,EnteredBy,CREATEDDATE)
		 VALUES(@PromotionID,'7','CustType',NULL,0,@CreatedBy,Getdate())
			 End
			 Else
			 Begin
		 INSERT INTO tbl_PromotionMasterDetails(PromotionID,PromoTypeID,PromoTypeDesp,Value1,Active,EnteredBy,CREATEDDATE)
		 VALUES(@PromotionID,'7','CustType',@CustType,@IsActive,@CreatedBy,Getdate())
			 End

		 INSERT INTO tbl_PromotionMasterDetails(PromotionID,PromoTypeID,PromoTypeDesp,Value1,Value2,Active,EnteredBy,CREATEDDATE)
		 VALUES(@PromotionID,'6','Promotion Date',@StartDate,@EndDate,@IsActive,@CreatedBy,Getdate())

			 if @ProSegment='NO'
			 Begin
		 INSERT INTO tbl_PromotionMasterDetails(PromotionID,PromoTypeID,PromoTypeDesp,Value1,Active,EnteredBy,CREATEDDATE)
		 VALUES(@PromotionID,'8','Segment',NULL,0,@CreatedBy,Getdate())
			 End
			 Else
			 Begin
		 INSERT INTO tbl_PromotionMasterDetails(PromotionID,PromoTypeID,PromoTypeDesp,Value1,Active,EnteredBy,CREATEDDATE)
		 VALUES(@PromotionID,'8','Segment',@ProSegment,@IsActive,@CreatedBy,Getdate())
			 End

			 if @ProCountryID='NO'
			 Begin
		 INSERT INTO tbl_PromotionMasterDetails(PromotionID,PromoTypeID,PromoTypeDesp,Value1,Active,EnteredBy,CREATEDDATE)
		 VALUES(@PromotionID,'9','Country',NULL,0,@CreatedBy,Getdate())
			 End
			 Else
			 Begin
		 INSERT INTO tbl_PromotionMasterDetails(PromotionID,PromoTypeID,PromoTypeDesp,Value1,Active,EnteredBy,CREATEDDATE)
		 VALUES(@PromotionID,'9','Country',@ProCountryID,@IsActive,@CreatedBy,Getdate())
			 End

			 if @ProCurrency='NO'
			 Begin
		 INSERT INTO tbl_PromotionMasterDetails(PromotionID,PromoTypeID,PromoTypeDesp,Value1,Active,EnteredBy,CREATEDDATE)
		 VALUES(@PromotionID,'10','Currency',NULL,0,@CreatedBy,Getdate())
			 End
			 Else
			 Begin
		 INSERT INTO tbl_PromotionMasterDetails(PromotionID,PromoTypeID,PromoTypeDesp,Value1,Active,EnteredBy,CREATEDDATE)
		 VALUES(@PromotionID,'10','Currency',@ProCurrency,@IsActive,@CreatedBy,Getdate())
			 End

			 if @ProReceiveAgent='NO'
			 Begin
		 INSERT INTO tbl_PromotionMasterDetails(PromotionID,PromoTypeID,PromoTypeDesp,Value1,Active,EnteredBy,CREATEDDATE)
		 VALUES(@PromotionID,'11','ReceiveAgent',NULL,0,@CreatedBy,Getdate())
			 End
			 Else
			 Begin
		 INSERT INTO tbl_PromotionMasterDetails(PromotionID,PromoTypeID,PromoTypeDesp,Value1,Active,EnteredBy,CREATEDDATE)
		 VALUES(@PromotionID,'11','ReceiveAgent',@ProReceiveAgent,@IsActive,@CreatedBy,Getdate())
			 End

			 if @ProCIF='NO'
			 Begin
		 INSERT INTO tbl_PromotionMasterDetails(PromotionID,PromoTypeID,PromoTypeDesp,Value1,Active,EnteredBy,CREATEDDATE)
		 VALUES(@PromotionID,'12','CIF',NULL,0,@CreatedBy,Getdate())
			 End
			 Else
			 Begin
		 INSERT INTO tbl_PromotionMasterDetails(PromotionID,PromoTypeID,PromoTypeDesp,Value1,Active,EnteredBy,CREATEDDATE)
		 VALUES(@PromotionID,'12','CIF',@ProCIF,@IsActive,@CreatedBy,Getdate())
			 End

			 if @PromoCode='NO'
			 Begin
		 INSERT INTO tbl_PromotionMasterDetails(PromotionID,PromoTypeID,PromoTypeDesp,Value1,Active,EnteredBy,CREATEDDATE)
		 VALUES(@PromotionID,'13','PromoCode',NULL,0,@CreatedBy,Getdate())
			 End
			 Else
			 Begin
		 INSERT INTO tbl_PromotionMasterDetails(PromotionID,PromoTypeID,PromoTypeDesp,Value1,Active,EnteredBy,CREATEDDATE)
		 VALUES(@PromotionID,'13','PromoCode',@PromoCode,@IsActive,@CreatedBy,Getdate())
			 End

			 if @ProPaymentMode='NO'
			 Begin
		 INSERT INTO tbl_PromotionMasterDetails(PromotionID,PromoTypeID,PromoTypeDesp,Value1,Active,EnteredBy,CREATEDDATE)
		 VALUES(@PromotionID,'14','PaymentMode',NULL,0,@CreatedBy,Getdate())
			 End
			 Else
			 Begin
		 INSERT INTO tbl_PromotionMasterDetails(PromotionID,PromoTypeID,PromoTypeDesp,Value1,Active,EnteredBy,CREATEDDATE)
		 VALUES(@PromotionID,'14','PaymentMode',@ProPaymentMode,@IsActive,@CreatedBy,Getdate())
			 End

			 if @ProPartnerCode='NO'
			 Begin
		 INSERT INTO tbl_PromotionMasterDetails(PromotionID,PromoTypeID,PromoTypeDesp,Value1,Active,EnteredBy,CREATEDDATE)
		 VALUES(@PromotionID,'15','PartnerCode',NULL,0,@CreatedBy,Getdate())
			 End
			 Else
			 Begin
		 INSERT INTO tbl_PromotionMasterDetails(PromotionID,PromoTypeID,PromoTypeDesp,Value1,Active,EnteredBy,CREATEDDATE)
		 VALUES(@PromotionID,'15','PartnerCode',@ProPartnerCode,@IsActive,@CreatedBy,Getdate())
			 End
	
		 

	END

	--DELETE
	--IF @ProcedureType=3
	--BEGIN
	--		Select * from tbl_PromotionMasterDetails

	--END


END TRY
BEGIN CATCH
	SET @msg = error_message()
	RAISERROR(@msg,16,1)
END CATCH

END