SET NOCOUNT ON
go
create database human_resource_management;
use human_resource_management;
create table CV(
	cvID int,
	candidateID int,
	can_img image,
	working_area nvarchar,
	working_place nvarchar,
	position nvarchar,
	workingTime nvarchar,
	salary nvarchar,
	degree nvarchar,
	chung_chi nvarchar,
	university nvarchar,
	uni_address nvarchar,
	falcuty nvarchar,
	graduated nvarchar,
	used_job nvarchar,
	num_experience nvarchar,
	description nvarchar,
	submitTime nvarchar,
	ngon_ngu nvarchar
	 

);
create table candidate(
	candidateID int,
	fullname nvarchar,
	sex nvarchar,
	DOB nvarchar,
	marriage_stt nvarchar,
	address nvarchar,
	distrist nvarchar,
	city nvarchar,
	phone int,
	email nvarchar,
	username nvarchar,
	password nvarchar,
	candidateStatus nvarchar,
	blocked nvarchar,
	time_chstt nvarchar
	 

);
create table employer(
	employerID int,
	em_img image,
	fullname nvarchar,
	sex nvarchar,
	username nvarchar,
	password nvarchar,
	companyName nvarchar,
	companyEmail nvarchar,
	companyAddress nvarchar,
	company_fax nvarchar,
	companyPhone nvarchar,
	companyField nvarchar,
	company_Description nvarchar,
	website nvarchar,
	timeRegister nvarchar,
	lastVisit nvarchar,
	blocked nvarchar
	 

);
create table quantri(
	ID int,
	fullname nvarchar,
	username nvarchar,
	password nvarchar,
	bac nvarchar
	 

);
create table FavouriteCandidate(
	employerID int,
	candidateID int,
	addedTime nvarchar
	 

);
create table news(
	newsID int,
	newsTitle nvarchar,
	newsContent nvarchar,
	author nvarchar,
	timepost nvarchar,
	summary nvarchar
	 

);
drop procedure [dbo].[employer_login]
create proc [dbo].[employer_login](
	
	@username nvarchar(50),
	@password nvarchar(50),
	@exist int output

)
as
begin 
if((select count (*) from employer where username = @username and password = @password) = 1)
begin 
	set @exist = 1
PRINT 'log in successfully '
end
else set @exist = 0
PRINT 'log in not successfully '
end 
--execute
declare @output int
exec employer_login 'employer', 'employer', @output output


--2

create proc [dbo].[employer_register](
	@name nvarchar(50),
	@username nvarchar(50),
	@password nvarchar(50),
	@email nvarchar(50),
	@phone nvarchar(50),
	@companyName nvarchar(50),
	@companyEmail nvarchar(50),
	@companyAddress nvarchar(50),
	@company_fax nvarchar(50),
	@companyPhone nvarchar(50),
	@companyField nvarchar(50),
	@company_Description nvarchar(50)
)
as
begin 
if((select count (*) from employer where username = @username)=1)
		begin 
			PRINT 'This account was used! Please choose another one!'
		END
	else 
	set @exist = 0
		BEGIN
			PRINT 'You can use this account!'
			--- kiem tra email hop. le.
				
			if(@email IS NOT NULL)
				set @email=
				(
					CHARINDEX(' ',LTRIM(RTRIM([Email]))) = 0 -- No embedded spaces
				AND 	LEFT(LTRIM([Email]),1) <> '@'  -- '@' can't be the first character of an email address
				AND 	RIGHT(RTRIM([Email]),1) <> '.' -- '.' can't be the last character of an email address
				AND 	LEN(LTRIM(RTRIM([Email]))) - LEN(REPLACE(LTRIM(RTRIM([Email])),'@','')) = 1 -- Only one '@' sign is allowed
				AND 	CHARINDEX('.',REVERSE(LTRIM(RTRIM([Email])))) >= 3
				AND 	(CHARINDEX('.@',[Email]) = 0 AND CHARINDEX('..',[Email]) = 0) -- can't have patterns like '.@' and '..'
				)
			print 'acceptable'
			insert into employer value() 
			end

--execute
exec employer_register 'employer', 'employer','employer','employer' ,'employer' ,
'employer' ,'employer', 'employer' ,'employer' ,'employer' ,'employer' ,'employer'

--3
create proc [dbo].[Candidate_login](
	
	@username nvarchar(50),
	@password nvarchar(50),
	@exist int output

)
as
begin 
if((select count (*) from Candidate where username = @username and password = @password) = 1)
begin 
	set @exist = 1
PRINT 'log in successfully '
end
else set @exist = 0
PRINT 'log in not successfully '
end 
--execute
declare @output int
exec Candidate_login 'Candidate', 'Candidate', @output output


--4
create proc [dbo].[Candidate_register](
	@name nvarchar(50),
	@username nvarchar(50),
	@password nvarchar(50),
	@email nvarchar(50),
	@phone nvarchar(50),
	@companyName nvarchar(50),
	@companyEmail nvarchar(50),
	@companyAddress nvarchar(50),
	@company_fax nvarchar(50),
	@companyPhone nvarchar(50),
	@companyField nvarchar(50),
	@company_Description nvarchar(50),
	@exist int output

)
as
begin 
DECLARE @invalChars varchar(5),@valid int,@badChar varchar(1),@atPos
int,@periodPos int
SET @valid = 1
SET @invalChars = ' /:,;'
--Check to see if it's blank
IF len(ltrim(rtrim(@email))) = 0
   SET @valid = 0
ELSE
        --Loop invalid characters to see if it exists in email
      WHILE len(@invalChars) > 0
         BEGIN
            SET @badChar = substring(@invalChars,1,1)
            IF(charindex(@badChar,@email) > 0)
               --If invalid character was found, return 0 to invalidate
               SET @valid = 0
            SET @invalChars = replace(@invalChars,@badChar,'')
         END
      --Check to see if "@" exists.
      SET @atPos = charindex('@',@email,1)
      IF @atPos = 0
         SET @valid = 0
       --Check to see if extra "@" exists after 1st "@".
      IF charindex('@',@email,@atPos+1) > 0
         SET @valid = 0
      SET @periodPos = charindex('.',@email,@atPos)
      IF @periodPos = 0
         SET @valid = 0
      IF (@periodPos+3) > len(@email)
         SET @valid = 0
      RETURN (@valid)
END


--5
create proc [dbo].[Admin_login](
	
	@username nvarchar(50),
	@password nvarchar(50),
	@exist int output

)
as
begin 
if((select count (*) from Admin where username = @username and password = @password) = 1)
begin 
	set @exist = 1
PRINT 'log in successfully '
end
else set @exist = 0
PRINT 'log in not successfully '
end 
--execute
declare @output int
exec Admin_login 'Candidate', 'Candidate', @output output