MOBILE_API/DB/SPs/EXCHANGE_PROC_COSTRATE_SETUP.sql

USE [FastMoneyPro_Remit]
GO

/****** Object:  StoredProcedure [dbo].[EXCHANGE_PROC_COSTRATE_SETUP]    Script Date: 7/4/2019 11:35:48 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[EXCHANGE_PROC_COSTRATE_SETUP]
	  @flag						VARCHAR(20),
	  @id						INT				= NULL,
      @user						VARCHAR(50)		= NULL,
	  @agentName				VARCHAR(500)	= NULL,     
      @sortBy					VARCHAR(50)		= NULL,
	  @sortOrder				VARCHAR(5)		= NULL,
	  @pageSize					INT				= NULL,
	  @pageNumber				INT				= NULL,
	  @currencyRateId			BIGINT			= NULL,
	  @agentId					BIGINT			= NULL,
	  @buyTolerancePlus			MONEY			= NULL,
	  @buyToleranceMinus		MONEY			= NULL,
	  @saleTolerancePlus		MONEY			= NULL,
	  @saleToleranceMinus		MONEY			= NULL,
	  @buyRate					MONEY			= NULL,
	  @baseCurrency				CHAR(3)			= NULL,
	  @saleRate					MONEY			= NULL
	  
		
AS
BEGIN    
SET NOCOUNT ON;
			
IF @FLAG='S'
BEGIN	
	DECLARE 
			 @selectFieldList	VARCHAR(MAX)
			,@extraFieldList	VARCHAR(MAX)
			,@table				VARCHAR(MAX)
			,@sqlFilter			VARCHAR(MAX)
			
		IF @sortBy IS NULL  
			SET @sortBy = 'agentName'			
	
		SET @table = '(		
						SELECT agentId,agentName,agentCode,agentAddress,agentPhone1,createdBy,createdDate
						 FROM dbo.agentMaster (NOLOCK) WHERE parentid = 1008 AND agentGrp = 11023

					  ) x'	
					
		SET @sqlFilter = ''	
		IF @agentName IS NOT NULL
			SET @sqlFilter +=' AND  agentName LIKE  '''+@agentName + '%'''
		
		
		SET @selectFieldList = 'agentId,agentName,agentCode,agentAddress,agentPhone1,createdBy,createdDate'
		
		EXEC dbo.proc_paging
			@table					
			,@sqlFilter			
			,@selectFieldList		
			,@extraFieldList		
			,@sortBy				
			,@sortOrder			
			,@pageSize				
			,@pageNumber
	
	RETURN
END
IF @flag='curCostRateAv'  --currency cost rate avaiable
BEGIN
	SELECT   crs.rowId AS rowId, baseCurrency, quoteCurrency ,cm.currencyName AS currencyName, buyRate 
			, buyTolerancePlus,buyToleranceMinus,saleRate,saleTolerancePlus,saleToleranceMinus
		 FROM EXCHANGE_COSTRATE_SETUP (NOLOCK) crs
		 INNER JOIN dbo.currencyMaster (NOLOCK) cm ON cm.currencyCode=crs.quoteCurrency	
		 AND crs.agentId=@agentId 
	RETURN
	
END
IF @flag='i'
BEGIN	
	BEGIN TRANSACTION	
	INSERT INTO dbo.EXCHANGE_COSTRATE_SETUP
	(
		baseCurrency, quoteCurrency, buyRate, buyTolerancePlus, buyToleranceMinus,
		saleRate,saleTolerancePlus,saleToleranceMinus,agentId,modifiedBy,modifiedDate
	)
	SELECT cc.baseCurrency, cc.quoteCurrency, cc.buyRate, cc.buyTolerancePlus, cc.buyToleranceMinus,
		cc.saleRate,cc.saleTolerancePlus,cc.saleToleranceMinus,@agentId,@user,GETDATE()
		FROM EXCHANGE_CURRENCYRATE_SETUP cc 
	LEFT JOIN EXCHANGE_COSTRATE_SETUP c ON cc.baseCurrency=c.baseCurrency AND c.agentId=@agentId
	WHERE c.baseCurrency IS NULL AND  ISNULL(cc.active,'N')='Y' 
	
	--/*Register Account */
	--IF NOT EXISTS(SELECT * FROM FastMoneyPro_Account.dbo.BillSetting (NOLOCK) WHERE company_id=@agentId)
	--	BEGIN
	--		INSERT INTO FastMoneyPro_Account.dbo.BillSetting( 
	--			company_id ,journal_voucher ,receipt_voucher ,contra_voucher ,payment_voucher ,manual_voucher ,fcy_purchase ,fcy_sales,CommonDate
	--				)
	--		SELECT 1,1,1,1,1,1,1,1,GETDATE()
	--	END

	--SET @agentName='Cash in Hand'	

	--/*Gl code assign*/
	--IF NOT EXISTS(SELECT * FROM FastMoneyPro_Account.dbo.GL_Group (NOLOCK) WHERE p_id='22' AND gl_code=22 AND gl_Name=@agentName )
	--	BEGIN
	--		Exec FastMoneyPro_Account.dbo.procFindGLTreeShape @p_id='22',@gl_name = @agentName,@bal_grp='',@accountPrifix=''
	--	END
	--/*Account no */
	--EXEC FastMoneyPro_Account.dbo.EXCHANGE_PROC_BRANCHCURR_ASSIGN @BRANCH_ID=@agentId,@user=@user
	 
	 COMMIT TRANSACTION
	SELECT 0 errorCode,'Rate updated successfully' MSG,null id
	RETURN
END
IF @flag='u'
BEGIN
	IF NOT EXISTS(
			SELECT 'A' FROM currencyMaster C WITH(NOLOCK) 
			WHERE C.currencyCode = @baseCurrency
			AND  (@buyRate + ISNULL(@buyTolerancePlus,0)) BETWEEN C.rateMin AND C.rateMax
			AND  (@buyRate - ISNULL(@buyToleranceMinus,0)) BETWEEN C.rateMin AND C.rateMax
			AND  (@saleRate + ISNULL(@saleTolerancePlus,0)) BETWEEN C.rateMin AND C.rateMax
			AND  (@saleRate - ISNULL(@saleToleranceMinus,0)) BETWEEN C.rateMin AND C.rateMax
			)
		BEGIN
			SELECT TOP 1 '1' CODE,'MIN and MAX rate must be lies between :'+CAST(C.rateMin AS VARCHAR)+' and '+CAST(C.rateMax AS VARCHAR)+' with Tolerance' MSG ,null id
			FROM currencyMaster C WITH(NOLOCK) 
			WHERE C.currencyCode = @baseCurrency
			RETURN;
		END
	
	UPDATE EXCHANGE_COSTRATE_SETUP SET 
		buyTolerancePlus		=	@buyTolerancePlus
		,buyToleranceMinus		=	@buyToleranceMinus
		,saleTolerancePlus		=	@saleTolerancePlus
		,saleToleranceMinus		=	@saleToleranceMinus
		,modifiedBy				=   @user
		,modifiedDate			=   GETDATE()
	WHERE rowId=@id AND agentId=@agentId
	SELECT 0 errorCode,'Rate updated successfully' MSG,null id
	RETURN
END
END
GO