INVESTOPIA_TSQL.sql

CREATE DATABASE INVESTOPIA
GO

USE INVESTOPIA
GO


IF OBJECT_ID('USERDETAILS')  IS NOT NULL
DROP TABLE USERDETAILS
GO



IF OBJECT_ID('usp_RegisterUser')  IS NOT NULL
DROP PROC usp_RegisterUser
GO


IF OBJECT_ID('ufn_GetUserDetails')  IS NOT NULL
DROP FUNCTION ufn_GetUserDetails
GO






CREATE TABLE USERDETAILS
(
	[USERID] INT IDENTITY(1000,1),
	[FIRSTNAME] VARCHAR(20) NOT NULL,
	[LASTNAME] VARCHAR(20) NOT NULL,
	[EMAILID] VARCHAR(20)  CONSTRAINT pk_EmailId PRIMARY KEY,
	[PASSWORD] VARCHAR(20) NOT NULL,
	[ROLEID] VARCHAR(20) NOT NULL CONSTRAINT chk_Role CHECK ([ROLEID] IN ('Admin','User'))
)
GO 


SELECT * FROM USERDETAILS


INSERT INTO USERDETAILS(FIRSTNAME,LASTNAME,EMAILID,PASSWORD,ROLEID) VALUES ('Arnab','Das','raj713335@gmail.com','12345678','Admin')
INSERT INTO USERDETAILS(FIRSTNAME,LASTNAME,EMAILID,PASSWORD,ROLEID) VALUES ('Sayan','Sinha','sayan@gmail.com','12345678','Admin')




CREATE FUNCTION ufn_GetUserDetails()
RETURNS TABLE 
AS
	RETURN (SELECT * FROM USERDETAILS)
GO



SELECT * FROM ufn_GetUserDetails()



CREATE PROCEDURE usp_RegisterUser
(
	@FIRSTNAME VARCHAR(20),
	@LASTNAME VARCHAR(20),
	@EMAILID VARCHAR(20),
	@PASSWORD VARCHAR(20)
)
AS
BEGIN
	DECLARE @RoleId TINYINT
	BEGIN TRY
		INSERT INTO USERDETAILS(FIRSTNAME,LASTNAME,EMAILID,PASSWORD,ROLEID) 
		VALUES (@FIRSTNAME,@LASTNAME,@EMAILID,@PASSWORD,'User')
		RETURN 1
	END TRY
	BEGIN CATCH
		RETURN -99
	END CATCH
END
GO



CREATE PROCEDURE usp_LoginValidation
(
	@EMAILID VARCHAR(20),
	@PASSWORD VARCHAR(20)
)
AS
BEGIN
	DECLARE @RoleId TINYINT
	BEGIN TRY
		IF EXISTS (SELECT * FROM USERDETAILS WHERE EMAILID=@EMAILID AND PASSWORD=@PASSWORD)
			RETURN 1
		ELSE
			RETURN -1
	END TRY
	BEGIN CATCH
		RETURN -99
	END CATCH
END
GO


DECLARE @RETURNVAL INT
EXEC @RETURNVAL = usp_LoginValidation 'trisha@gmail.com', '12345678'
SELECT @RETURNVAL AS RETURNVAL



CREATE PROCEDURE usp_LoginValidation
(
	@EMAILID VARCHAR(20),
	@PASSWORD VARCHAR(20)
)
AS
BEGIN
	DECLARE @RoleId TINYINT
	BEGIN TRY
		IF EXISTS (SELECT * FROM USERDETAILS WHERE EMAILID=@EMAILID AND PASSWORD=@PASSWORD)
			RETURN 1
		ELSE
			RETURN -1
	END TRY
	BEGIN CATCH
		RETURN -99
	END CATCH
END
GO

IF OBJECT_ID('USERINFORMATION')  IS NOT NULL
DROP TABLE USERINFORMATION
GO



IF OBJECT_ID('USP_INSERT_USER_INFORMATION')  IS NOT NULL
DROP PROC USP_INSERT_USER_INFORMATION
GO


IF OBJECT_ID('ufn_GetUserINFORMATION')  IS NOT NULL
DROP FUNCTION ufn_GetUserINFORMATION
GO


CREATE TABLE USERINFORMATION
(
	[EMAILID] VARCHAR(50)  CONSTRAINT pk_EmailIds PRIMARY KEY,
	[GENDER] VARCHAR(20) NOT NULL CONSTRAINT chk_gender CHECK ([GENDER] IN ('Male','Female')),
	[AGE] INT NOT NULL,
	[DISABILITIES] VARCHAR(20) NOT NULL CONSTRAINT chk_disabilities CHECK ([DISABILITIES] IN ('Yes','No')),
	[DEPENDENTS] INT NOT NULL,
	[COUNTRY] VARCHAR(20) NOT NULL,
	[STATE] VARCHAR(20) NOT NULL,
	[COUNTRY_STATUS] VARCHAR(20) NOT NULL,
	[PIN_CODE] VARCHAR(20) NOT NULL,
	[TAXID] VARCHAR(50) NOT NULL,
	[PASSPORTID] VARCHAR(50) NOT NULL,
	[NOMINEE] VARCHAR(50) NOT NULL,
	[RELATIONSHIP_WITH_NOMINEE] VARCHAR(20) NOT NULL,
	[CURRENT_SALARY] DECIMAL(20,2) NOT NULL,
	[SAVINGS] DECIMAL(20,2) NOT NULL,
	[HEALTH_INSURANCE] VARCHAR(20) NOT NULL CONSTRAINT chk_Health_Insurance CHECK ([HEALTH_INSURANCE] IN ('Yes','No')),
	[OTHER_INSURANCE] VARCHAR(20) NOT NULL CONSTRAINT chk_Other_Insurance CHECK ([OTHER_INSURANCE] IN ('Yes','No')),
	[SMOKE] VARCHAR(20) NOT NULL CONSTRAINT chk_Smoke CHECK ([SMOKE]  IN ('Yes','No')),
	[HEALTH_ISSUE] VARCHAR(20) NOT NULL CONSTRAINT chk_health CHECK ([HEALTH_ISSUE]  IN ('Yes','No')),
	[EXERCISE] VARCHAR(20) NOT NULL CONSTRAINT chk_Exercise CHECK ([EXERCISE]  IN ('Yes','No')),
	[ALCOHOL] DECIMAL(10,2) NOT NULL,
	[BMI] DECIMAL(10,2) NOT NULL,
	[EXPECTED_AGE_TO_LIVE] DECIMAL(5,2) NOT NULL,
	[YEARLY_SAVINGS_REQUIRED] DECIMAL(20,2)
)
GO 



CREATE PROCEDURE USP_INSERT_USER_INFORMATION
(
	@EMAILID VARCHAR(50),
	@GENDER VARCHAR(20),
	@AGE INT,
	@DISABILITIES VARCHAR(20),
	@DEPENDENTS INT,
	@COUNTRY VARCHAR(20),
	@STATE VARCHAR(20),
	@COUNTRY_STATUS VARCHAR(20),
	@PIN_CODE VARCHAR(20),
	@TAXID VARCHAR(50),
	@PASSPORTID VARCHAR(50),
	@NOMINEE VARCHAR(50),
	@RELATIONSHIP_WITH_NOMINEE VARCHAR(20),
	@CURRENT_SALARY DECIMAL(20,2),
	@SAVINGS DECIMAL(20,2),
	@HEALTH_INSURANCE VARCHAR(20),
	@OTHER_INSURANCE VARCHAR(20),
	@SMOKE VARCHAR(20),
	@HEALTH_ISSUE VARCHAR(20),
	@EXERCISE VARCHAR(20),
	@ALCOHOL DECIMAL(10,2),
	@BMI DECIMAL(10,2),
	@EXPECTED_AGE_TO_LIVE DECIMAL(5,2),
	@YEARLY_SAVINGS_REQUIRED DECIMAL(20,2)
)
AS
BEGIN
	DECLARE @RoleId TINYINT
	BEGIN TRY
		IF NOT EXISTS(SELECT * FROM USERDETAILS WHERE EMAILID=@EMAILID)
		RETURN -1
		IF EXISTS(SELECT * FROM USERINFORMATION WHERE EMAILID=@EMAILID)
		RETURN -1
		INSERT INTO USERINFORMATION([EMAILID],[GENDER],[AGE],[DISABILITIES],[DEPENDENTS],[COUNTRY],[STATE],[COUNTRY_STATUS],[PIN_CODE],[TAXID],[PASSPORTID],
		[NOMINEE],[RELATIONSHIP_WITH_NOMINEE],[CURRENT_SALARY],[SAVINGS],[HEALTH_INSURANCE],[OTHER_INSURANCE],[SMOKE],[HEALTH_ISSUE],[EXERCISE],[ALCOHOL],[BMI],[EXPECTED_AGE_TO_LIVE],[YEARLY_SAVINGS_REQUIRED]) 
		VALUES (@EMAILID,@GENDER,@AGE,@DISABILITIES,@DEPENDENTS,@COUNTRY,@STATE,@COUNTRY_STATUS,@PIN_CODE,@TAXID,@PASSPORTID,@NOMINEE,@RELATIONSHIP_WITH_NOMINEE,
		@CURRENT_SALARY,@SAVINGS,@HEALTH_INSURANCE,@OTHER_INSURANCE,@SMOKE,@HEALTH_ISSUE,@EXERCISE,@ALCOHOL,@BMI,@EXPECTED_AGE_TO_LIVE,@YEARLY_SAVINGS_REQUIRED)
		RETURN 1
	END TRY
	BEGIN CATCH
		RETURN -99
	END CATCH
END
GO

DECLARE @RETURNVAL INT
EXEC @RETURNVAL = USP_INSERT_USER_INFORMATION 'chonga@gmail.com', 'Male', 24, 'No', '4', 'India', 'West Bengal', 'Developing', '713335', 'HHDHBB2388', 'JJSJUU23', 'ASITA DAS', 'Mother',
345624.00, 7772812.09, 'No', 'No', 'No', 'No','No', 2, 34 , 74.2, 100
SELECT @RETURNVAL AS RETURNVAL



DECLARE @RETURNVAL INT
EXEC @RETURNVAL = USP_INSERT_USER_INFORMATION 'arnav@gmail.com', 'Male', 24, 'No', '4', 'India', 'West Bengal', 'Developing', '713335', 'HHDHBB2388', 'JJSJUU23', 'ASITA DAS', 'Mother',
61267, 128762, 'No', 'No', 'No', 'No','No', 2, 34 , 74.2
SELECT @RETURNVAL AS RETURNVAL

SELECT * FROM USERINFORMATION

DELETE FROM USERINFORMATION WHERE EMAILID='chin@gmail.com'

CREATE FUNCTION ufn_GetUserINFORMATION()
RETURNS TABLE 
AS
	RETURN (SELECT * FROM USERINFORMATION)
GO



SELECT * FROM ufn_GetUserINFORMATION()



CREATE FUNCTION ufn_GetSpecificUserINFORMATION(@EMAILID VARCHAR(50))
RETURNS TABLE 
AS
	RETURN (SELECT * FROM USERINFORMATION WHERE EMAILID=@EMAILID)
GO


SELECT * FROM ufn_GetSpecificUserINFORMATION('z@gmail.com')


SELECT EMAILID,AGE,EXPECTED_AGE_TO_LIVE,CURRENT_SALARY,SAVINGS,YEARLY_SAVINGS_REQUIRED FROM USERINFORMATION WHERE EMAILID='z@gmail.com'