ALTER PROCEDURE [dbo].[new_office]
(
------------basic office info-------------
@name nvarchar(150),
@nameManger nvarchar(50),
@permit_id nchar(10),
@type_work nvarchar(50),
@create_Date date,
@location nvarchar(300)='no location',
@fk_nieghborehood int,
@fk_org int,
-------------info username-------

@username nvarchar(50),
@password nchar(20),
-----------------info contact------
@email nvarchar(50),
@mobile nchar(10),
@phone nchar(10)='no',
@facebook nvarchar(200)='no',
@twitter nvarchar(200)='no',
@website nvarchar(200)='no',
--------------message----------
@msg nvarchar(50) output
)
AS
BEGIN
if not exists(select @username from users where username=@username)
begin
    declare @time datetime2= sysdatetimeoffset() 
	insert into office(name,nameManger,permit_id,type_work,create_Date,map_location,date_register,fk_nieghborehood,fk_org)
	values(
	@name,
	@nameManger,
	@permit_id,
	@type_work,
	@create_Date,
	@location,
	@time,
	@fk_nieghborehood,
	@fk_org
	)
	------------second if-----------
	if not exists(select id  from office where date_register=@time)
	begin
	insert into users(user_id,username,password,type)
	values
	(
	(select id  from office where date_register=@time),
	@username,
	@password,
	'office'
	)
	insert into contact(id,email,mobile,phone,facebook,twitter,website)
	values
	(
	(select users.id  from users,office where date_register=@time and user_id=office.id and users.type='office'),
	@email ,
	@mobile ,
	@phone ,
	@facebook ,
	@twitter ,
	@website 
	)
	end
	else
	begin
	set @msg='fail create username for office'
	end
end
else
begin

set @msg ='sorry, this user not allow'
end
END