USE [MFA]
GO

/****** Object:  Table [dbo].[MFA_PAYMENT]    Script Date: 03/08/2022 15:05:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[MFA_PAYMENT](
	[MFA_PAYMENT_ID] [numeric](9, 0) IDENTITY(1,1) NOT NULL,
	[FULL_NAME] [nvarchar](250) NULL,
	[NATIONAL_NUMBER] [nvarchar](14) NULL,
	[PHONE] [nvarchar](20) NULL,
	[PAIED_AMOUNT] [numeric](8, 3) NULL,
	[ORDER_ID] [nvarchar](50) NULL,
	[ORDER_DATE] [datetime] NULL,
	[PAYMENT_DATE] [datetime] NULL,
	[PAY_FLAG] [numeric](1, 0) NULL,
	[NOTES] [nvarchar](50) NULL,
	[ED_ACAD_YEAR_ID] [numeric](9, 0) NULL,
 CONSTRAINT [PK_MFA_PAYMENT] PRIMARY KEY CLUSTERED 
(
	[MFA_PAYMENT_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO





USE [MFA]
GO
/****** Object:  StoredProcedure [dbo].[MFA_PAYMENT_CHECK]    Script Date: 03/08/2022 15:04:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[MFA_PAYMENT_CHECK]
@MFA_PAYMENT_ID numeric(9,0)

 AS
 BEGIN
 	
	update   MFA_PAYMENT set  PAY_FLAG=1 
	where MFA_PAYMENT_ID =@MFA_PAYMENT_ID
	select 1 from  MFA_PAYMENT where PAY_FLAG=1 
	and MFA_PAYMENT_ID =@MFA_PAYMENT_ID
END


USE [MFA]
GO
/****** Object:  StoredProcedure [dbo].[MFA_PAYMENT_I_CHECK]    Script Date: 03/08/2022 15:04:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec MFA_PAYMENT_I_CHECK '12345678','Doaa allam'
ALTER PROCEDURE [dbo].[MFA_PAYMENT_I_CHECK]
@NATIONAL_NUMBER nvarchar(20),
@FULL_NAME nvarchar(250)


 AS
 BEGIN
 DECLARE @PAY_CASE NUMERIC (1,0)=0
  DECLARE @ED_ACAD_YEAR_ID NUMERIC (9,0)
  --DECLARE @MFA_PAYMENT_ID NVARCHAR(10)
 
 SELECT @ED_ACAD_YEAR_ID=ED_ACAD_YEAR_ID FROM ED_ACAD_YEAR WHERE ED_ACAD_YEAR.CURRENT_YEAR=1
 
 	select @PAY_CASE=1 from  MFA_PAYMENT where NATIONAL_NUMBER=@NATIONAL_NUMBER AND  PAY_FLAG=1 AND ED_ACAD_YEAR_ID=@ED_ACAD_YEAR_ID
 	select @PAY_CASE=2 from  MFA_PAYMENT where NATIONAL_NUMBER=@NATIONAL_NUMBER AND  (PAY_FLAG=0 OR PAY_FLAG is NULL) AND ED_ACAD_YEAR_ID=@ED_ACAD_YEAR_ID
 	
 	IF (@PAY_CASE=0)
 	BEGIN
	INSERT INTO MFA_PAYMENT(FULL_NAME
      ,NATIONAL_NUMBER     
      ,PAIED_AMOUNT      
      ,ORDER_DATE,ED_ACAD_YEAR_ID)      
     VALUES(@FULL_NAME,@NATIONAL_NUMBER,10,GETDATE(),@ED_ACAD_YEAR_ID)
     SELECT MAX(MFA_PAYMENT_ID) AS MFA_PAYMENT_USER_ID FROM MFA_PAYMENT
     
	END 
	IF (@PAY_CASE=1)
	BEGIN
	SELECT '01' as MFA_PAYMENT_USER_ID
	END
	IF (@PAY_CASE=2)
	BEGIN
	 SELECT MFA_PAYMENT_ID AS MFA_PAYMENT_USER_ID FROM MFA_PAYMENT where NATIONAL_NUMBER=@NATIONAL_NUMBER
	END
	
	
	
END


USE [MFA]
GO
/****** Object:  StoredProcedure [dbo].[MFA_PAYMENT_U]    Script Date: 03/08/2022 15:05:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec MFA_PAYMENT_U 3,'1-1-2022','5555','2222'
ALTER PROCEDURE [dbo].[MFA_PAYMENT_U]
@MFA_PAYMENT_ID numeric(9,0)
,@AuthoriztionDateTime datetime
,@SenderRequestNumber nvarchar(100)
,@AuthorizationCode nvarchar(100)
 AS
 BEGIN

  
	-- print 'a';  
	 update MFA_PAYMENT set
	        PAY_FLAG=1
			
			,ORDER_ID=@SenderRequestNumber
			,PAYMENT_DATE=@AuthoriztionDateTime
		
			 ,NOTES=@AuthorizationCode
 
	 where MFA_PAYMENT_ID=@MFA_PAYMENT_ID
END