if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CommentManagement]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CommentManagement]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spCalendarManagement]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spCalendarManagement]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spCalendarViews]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spCalendarViews]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spCategoryManagement]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spCategoryManagement]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spEventManagement]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spEventManagement]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spExportCalendandarEvents]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spExportCalendandarEvents]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spGroupManagment]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spGroupManagment]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spHolidayDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spHolidayDelete]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spHolidayInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spHolidayInsert]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spHolidayManagement]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spHolidayManagement]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spHolidaySelectAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spHolidaySelectAll]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spHolidaySelectSingle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spHolidaySelectSingle]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spHolidayUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spHolidayUpdate]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spInvitationManagment]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spInvitationManagment]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spLookupManagement]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spLookupManagement]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spReminderManagement]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spReminderManagement]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spSearchManagment]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spSearchManagment]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spTagManagement]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spTagManagement]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spUserManagement]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spUserManagement]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblCalendar]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblCalendar]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblCalendarGroup]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblCalendarGroup]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblCategory]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblCategory]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblComments]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblComments]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblEvents]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblEvents]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblGroup]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblGroup]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblGroupUser]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblGroupUser]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblHolidays]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblHolidays]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblInvitations]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblInvitations]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblLookup]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblLookup]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPictureScheme]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPictureScheme]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblSecurityQuestions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblSecurityQuestions]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblTagged]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblTagged]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblUsers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblUsers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblcms]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblcms]
GO

CREATE TABLE [dbo].[tblCalendar] (
	[CalendarID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[UserID] [bigint] NOT NULL ,
	[CalendarName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CreatedDate] [datetime] NOT NULL ,
	[Description] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PictureschemeId] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblCalendarGroup] (
	[CalendarID] [bigint] NOT NULL ,
	[GroupID] [bigint] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblCategory] (
	[CategoryId] [bigint] IDENTITY (1, 1) NOT NULL ,
	[CategoryName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblComments] (
	[CommentID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[EventID] [bigint] NOT NULL ,
	[UserID] [bigint] NOT NULL ,
	[Description] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CommentDate] [datetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblEvents] (
	[EventID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[CalendarID] [bigint] NOT NULL ,
	[UserID] [bigint] NOT NULL ,
	[StartDate] [datetime] NOT NULL ,
	[EndDate] [datetime] NOT NULL ,
	[EventType] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[EventDescription] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[EventStatus] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ShareStatus] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[EventTitle] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Location] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ReminderMode] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[RecurringMode] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CategoryId] [bigint] NULL ,
	[ReminderSendDate] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblGroup] (
	[GroupID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[GroupName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CreatedDate] [datetime] NOT NULL ,
	[Description] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CreatedBy] [bigint] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblGroupUser] (
	[GroupID] [bigint] NOT NULL ,
	[UserID] [bigint] NOT NULL ,
	[IsValid] [bit] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblHolidays] (
	[HolidayId] [bigint] IDENTITY (1, 1) NOT NULL ,
	[HolidayDate] [datetime] NOT NULL ,
	[Description] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Title] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Calendarid] [bigint] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblInvitations] (
	[InvitationId] [bigint] IDENTITY (1, 1) NOT NULL ,
	[SenderId] [bigint] NOT NULL ,
	[RecieverId] [bigint] NOT NULL ,
	[Sentdate] [datetime] NOT NULL ,
	[Status] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[RefId] [bigint] NOT NULL ,
	[InvitationTitle] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Description] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Type] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblLookup] (
	[LookUpID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[LookUpName] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[LookUpValue] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblPictureScheme] (
	[SchemeId] [int] IDENTITY (1, 1) NOT NULL ,
	[Name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblSecurityQuestions] (
	[QuestionId] [bigint] IDENTITY (1, 1) NOT NULL ,
	[QuestionText] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblTagged] (
	[TagId] [bigint] IDENTITY (1, 1) NOT NULL ,
	[TaggedBy] [bigint] NOT NULL ,
	[EmailId] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[IsValid] [bit] NOT NULL ,
	[TaggedDate] [datetime] NOT NULL ,
	[GroupId] [bigint] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblUsers] (
	[UserID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[FirstName] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[LastName] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Address1] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Address2] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[EmailID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Mobile1] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Mobile2] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[UserName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Password] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Phone] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SecureQId] [bigint] NOT NULL ,
	[Answer] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Style] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Status] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[RegisterDate] [datetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblcms] (
	[pgID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[pgName] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[pgData] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



--To get all the comments of the event    
CREATE procedure CommentManagement;1     
@eventId BIGINT,   
@userId bigint  
As
begin
  select C.commentid,C.eventid,C.userid,
  fullname=(select firstname+' '+lastname from tblusers where userid=C.userid),
  C.[description],C.CommentDate 
  from tblcomments C 
  where C.eventid =@eventid
  order by C.commentDate desc
End 




GO



--To add comment for an event    
CREATE procedure CommentManagement;2    
@eventId BIGINT,   
@userId bigint,
@comment nvarchar(200),
@commentDate datetime
As
begin
  insert into tblcomments(eventid,userid,[description],commentDate) 
	values(@eventId,@userid,@comment,@commentDate)
End  





GO


--To get all the comments of the event for admin view     
CREATE procedure CommentManagement;3      
@eventId BIGINT  
As  
begin  
  select C.commentid,C.eventid,C.userid,  
  fullname=(select firstname+' '+lastname from tblusers where userid=C.userid),  
  C.[description],C.CommentDate   
  from tblcomments C   
  where C.eventid =@eventid  
  order by C.commentDate desc  
End   



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE procedure spCalendarManagement;1              
@UserId bigint,                
@CalendarName nvarchar(50),               
@CreatedDate datetime,            
@Description nvarchar(200),          
@GroupId bigint,
@SchemeId bigint           
AS                
declare @CalendarId bigint          
Begin                
    insert into tblcalendar(UserId,calendarname,createddate,[description],PictureSchemeId)              
    values(@UserId ,@CalendarName,@CreatedDate,@Description,@SchemeId)    
   if(@GroupId>0)    
 begin           
    select @CalendarId=(select max(calendarid) from tblcalendar)       
 insert into tblCalendarGroup(calendarid,groupid) values(@CalendarId,@GroupId)              
 end    
end
GO

--To Get  calenderid of the default calender  
CREATE procedure spCalendarManagement;2  
@UserId bigint,  
@CalendarId bigint out  
AS    
Begin    
select @CalendarId=(select isnull(min(calendarid),0) from tblcalendar where userid=@UserId)  
end
GO

--To All the calendars belogs to the user   
CREATE procedure spCalendarManagement;3      
@UserId bigint      
AS        
Begin        
select C.calendarid,C.calendarname,C.createddate,isnull(C.[description],'') as [Description], 
 EventCount=(select count(eventid)from tblevents where eventType='ET' and calendarid= C.calendarid),
 AppCount=(select count(eventid)from tblevents where eventType='AP' and calendarid= C.calendarid),
 TaskCount=(select count(eventid)from tblevents where eventType='TS' and calendarid= C.calendarid),
 HolidayCount=(select count(holidayid)from tblholidays where calendarid= C.calendarid)
 from tblcalendar C where userid=@UserId    
end
GO

--To get the calendardetails by userid and calenderid       
CREATE procedure spCalendarManagement;4          
@UserId bigint,      
@CalendarId bigint           
AS            
Begin            
select C.calendarid,C.calendarname,C.createddate,    
 isnull(C.[description],'') as [Description],    
 Groupid=(select isnull(max(groupid),0) from tblCalendargroup where calendarid=C.calendarid),
 pictureschemeid     
 from tblcalendar C     
 where C.userid=@UserId and C.calendarid=@CalendarId      
end
GO

--To Update the Calender          
CREATE procedure spCalendarManagement;5          
@UserId bigint,        
@CalendarId bigint,             
@CalendarName nvarchar(50),      
@Description nvarchar(200),      
@GroupId bigint, 
@SchemeId int        
AS        
Begin        
update tblcalendar set calendarname=@CalendarName,[Description]=@Description,
 pictureschemeid=@SchemeId         
where userId=@Userid and calendarId=@CalendarId        
delete from tblCalendargroup where calendarid=@CalendarId      
 if(@Groupid>0)    
 insert into tblCalendargroup(calendarId,GroupId)values(@CalendarId,@GroupId)      
end
GO

--To Delete the Calender          
CREATE procedure spCalendarManagement;6         
@UserId bigint,        
@CalendarId bigint           
      
AS        
Begin       
delete from tblcalendargroup where  calendarid=@calendarId    
delete from tblcalendar where calendarid=@calendarid and userid=@userid        
end
GO

--To Erase the Calender            
CREATE procedure spCalendarManagement;7           
@UserId bigint,          
@CalendarId bigint             
     
AS          
Begin  
--'E'is for events and 'R' is for replay for the 'event invitation'
delete from tblinvitations where (type='E' or type='R') and refid in(select eventid from tblevents where calendarid=@calendarId and Userid=@userid)
delete from tblcomments where eventid in (select eventid from tblevents where calendarid=@calendarId and Userid=@userid)
delete from tblevents where  calendarid=@calendarId  and userid=@userid    
  
end
GO

--To Get the Scheme name of the Calendar             
CREATE procedure spCalendarManagement;8              
@UserId bigint,              
@CalendarId bigint,  
@SchemeName nvarchar(100) out               
          
AS              
Begin      
select @SchemeName=(select isnull(P.name,'') from tblcalendar C inner join tblpicturescheme P on C.PictureschemeId=P.schemeid  where calendarid=@calendarId  and userid=@userid)        

end
GO

--To Get all Schemes            
CREATE procedure spCalendarManagement;9 
AS              
Begin      
select schemeid,[name] from tblpicturescheme       
end
GO

--To All the calendars belogs to the user     
CREATE procedure spCalendarManagement;10       
AS          
Begin          
select C.calendarid,C.calendarname,C.createddate,
isnull(C.[description],'') as [Description],
groupname=isnull(
	(select G.groupname 
 	from tblcalendargroup A left join tblgroup G 
	on A.groupid=G.groupid where A.calendarid=C.calendarid),'Not assigned'),
C.userid,
username=(select username from tblusers where userid=C.userid)

from tblcalendar C 
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



/*To get month view details */    
CREATE procedure spCalendarViews;1        
@CalendarId int,
@UserId int,
@StartDate datetime,
@EndDate datetime,
@EventType char(2)   
as
select startdate,left(min(eventTitle),14)+'...' as firstEvent,
Count(eventid) as EventCount 
from tblEvents 
where calendarId=@CalendarId 
and userid=@UserId 
and Eventtype=@EventType 
and startdate between @StartDate and @EndDate 
group by startdate  




GO



/*To get Day view details with Time Dependant */        
CREATE procedure spCalendarViews;2            
@UserId int,@CalendarId int,  
@SelectedDate datetime            
as    
Begin          
select eventId,eventTitle,eventType,startDate,endDate,recurringMode   
from tblEvents   
where calendarId=@CalendarId and userid=@UserId   
and(  
@selectedDate between startDate and endDate or   
Cast(@SelectedDate AS CHAR(11))= Cast(startdate AS CHAR(11)) or   
Cast(@SelectedDate AS CHAR(11))= Cast(EndDate AS CHAR(11)) 
or(@SelectedDate>startdate and (recurringmode='DAY' or recurringMode='MNT' or recurringMode='YER'))  
)  
End  




GO



/*To get Evebt Details for a period used for dayview,monthview,weekview and 5daysview */    
CREATE procedure spCalendarViews;3        
@UserId int,
@CalendarId int,
@FirstDate datetime,
@LastDate datetime    
as        
select eventId,eventTitle,eventType,startDate,endDate,recurringMode
from tblEvents 
where calendarId=@CalendarId 
and userid=@UserId   
and(
	@FirstDate between startDate and endDate 
	or @Lastdate between startdate and enddate 
	or startdate between @FirstDate and @Lastdate
	or(startdate<=@LastDate and (recurringmode='DAY' or recurringMode='MNT' or recurringMode='YER'))
)  
  




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


--To get all the categories
CREATE PROCEDURE spCategoryManagement;1  
@UserId bigint  
As  
Begin  
  select categoryId,categoryName from tblCategory  
End
GO

--To get all the categories for admin view  
CREATE PROCEDURE spCategoryManagement;2 
As    
Begin    
  select categoryId,categoryName from tblCategory    
End
GO

--To update selected category 
CREATE PROCEDURE spCategoryManagement;3  
@CategoryId bigint,
@CategoryName nvarchar(50)  
As 
Begin    
  update tblCategory set categoryname=@CategoryName
  where categoryid=@CategoryId
End
GO

--To delete selected category 
CREATE PROCEDURE spCategoryManagement;4  
@CategoryId bigint
As 
Begin  
Declare @RowCount  int
  select @RowCount=(select count(eventId) from tblevents where categoryid=@CategoryId)
  if(@RowCount<1)
  	delete from tblCategory where categoryid=@CategoryId
End
GO

--To get category details
CREATE PROCEDURE spCategoryManagement;5
@CategoryId bigint
As 
Begin  
select categoryId,CategoryName from tblCategory where categoryid=@CategoryId
End
GO

--To create a new category
CREATE PROCEDURE spCategoryManagement;6
@CategoryName nvarchar(50),
@CategoryId bigint out
As 
Begin  
Declare @RowCount  int
  select @RowCount=(select count(categoryid) from tblcategory where categoryname=@CategoryName)
  if(@RowCount<1)
  Begin
	insert into tblcategory(categoryname) values(@CategoryName)
  	select @CategoryId =(select max(categoryid) from tblcategory)
  End
  Else
  select @CategoryId=-1

End
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

--To add an Event(Event,Appointment,Task)            
CREATE  PROCEDURE spEventManagement;1               
@UserId bigint,              
@CalendarId bigint,              
@StartDate datetime,              
@EndDate datetime,              
@EventTitle nvarchar(100),              
@EventType nvarchar(2),              
@EventDescription nvarchar(200),              
@EventStatus nvarchar(2),              
@ShareStatus nvarchar(2),          
@Location nvarchar(50),      
@ReminderMode nvarchar(2),    
@RecurringMode nvarchar(3),    
@CategoryId bigint           
AS              
Begin     
select eventid from tblevents where eventtitle=@eventtitle and startdate=@startdate and enddate=@enddate  
if(@@rowcount < 1)  
insert into tblevents(UserId,CalendarId,StartDate,EndDate,EventTitle,EventType,              
EventDescription,EventStatus,ShareStatus,Location,remindermode,recurringmode,categoryId)              
values(@UserId,@CalendarId,@StartDate,@Enddate,@EventTitle,@EventType,@EventDescription,              
@EventStatus,@ShareStatus,@Location,@ReminderMode,@recurringmode,@CategoryId)              
end
GO

--To get the details of the Event          
CREATE PROCEDURE spEventManagement;2            
@UserId bigint,            
@CalendarId bigint,           
@EventId bigint          
AS            
Begin            
select StartDate,EndDate,EventTitle,EventType,EventDescription,EventStatus,ShareStatus,  
Location,remindermode,recurringmode,isnull(categoryid,0)  
from tblevents where userid=@UserId and calendarId=@CalendarId and eventId=@EventId          
end
GO

--To Update an Event(Event,Appointment,Task)        
CREATE PROCEDURE spEventManagement;3           
@UserId bigint,          
@CalendarId bigint,      
@EventId bigint,        
@StartDate datetime,          
@EndDate datetime,          
@EventTitle nvarchar(100),          
@EventType nvarchar(2),          
@EventDescription nvarchar(200),          
@EventStatus nvarchar(2),          
@ShareStatus nvarchar(2),      
@Location nvarchar(50),  
@ReminderMode nvarchar(2),
@RecurringMode nvarchar(3),
@CategoryId bigint        
AS          
Begin          
update tblevents set StartDate=@StartDate      
,EndDate=@Enddate,EventTitle=@EventTitle,EventType=@EventType,          
EventDescription=@EventDescription,EventStatus=@EventStatus,      
ShareStatus=@ShareStatus,Location=@Location,remindermode=@ReminderMode,
recurringmode=@recurringMode,CategoryId=@CategoryId 
where UserId=@UserId and CalendarId=@CalendarId and eventid=@EventId          
end
GO

--To Delete an Event(Event,Appointment,Task)    
CREATE PROCEDURE spEventManagement;4      
@UserId bigint,      
@CalendarId bigint,  
@EventId bigint   
  
AS      
Begin      
delete from tblevents where UserId=@UserId and CalendarId=@CalendarId and eventid=@EventId  
end
GO

--To Get all Events belongs to a user with different mode            
CREATE PROCEDURE spEventManagement;5                    
@UserId bigint,        
@Mode nvarchar(10),        
@CurrDate datetime        
AS                    
Begin          
if(@Mode='All'or @Mode='')                  
  SELECT [EventID],[EventTitle],[CalendarID],CalendarName=              
  (SELECT [CalendarName] FROM [tblCalendar] WHERE [CalendarID]=E.[CalendarID]),              
  [StartDate],[Location],[EventType],[EventStatus],[ShareStatus],
  Commentcount=(select count(commentId)from tblcomments where eventid=E.eventId)       
  from tblevents E         
  where UserId=@UserId  order by startdate                   
else if(@Mode='Pending')       
  SELECT [EventID],[EventTitle],[CalendarID],CalendarName=              
  (SELECT [CalendarName] FROM [tblCalendar] WHERE [CalendarID]=E.[CalendarID]),              
  [StartDate],[Location],[EventType],[EventStatus],[ShareStatus],
  Commentcount=(select count(commentId)from tblcomments where eventid=E.eventId)       
  from tblevents E         
  where E.UserId=@UserId  and E.EventStatus!='DN'        
  and DateDiff(n,@CurrDate,enddate)>0     
else if(@Mode='Recurring')       
  SELECT [EventID],[EventTitle],[CalendarID],CalendarName=              
  (SELECT [CalendarName] FROM [tblCalendar] WHERE [CalendarID]=E.[CalendarID]),              
  [StartDate],[Location],[EventType],[EventStatus],[ShareStatus],
  Commentcount=(select count(commentId)from tblcomments where eventid=E.eventId)
  from tblevents E  
  where E.UserId=@UserId  and E.recurringmode!='NN'     
else if(@Mode='OnProcess')       
  SELECT [EventID],[EventTitle],[CalendarID],CalendarName=              
  (SELECT [CalendarName] FROM [tblCalendar] WHERE [CalendarID]=E.[CalendarID]),              
  [StartDate],[Location],[EventType],[EventStatus],[ShareStatus],
  Commentcount=(select count(commentId)from tblcomments where eventid=E.eventId)       
  from tblevents E         
  where E.UserId=@UserId  and E.EventStatus='OP'      
else if(@Mode='Completed')       
  SELECT [EventID],[EventTitle],[CalendarID],CalendarName=              
  (SELECT [CalendarName] FROM [tblCalendar] WHERE [CalendarID]=E.[CalendarID]),              
  [StartDate],[Location],[EventType],[EventStatus],[ShareStatus],
  Commentcount=(select count(commentId)from tblcomments where eventid=E.eventId)
  from tblevents E         
  where E.UserId=@UserId  and E.EventStatus='DN'     
    
else if(@Mode='Events')       
  SELECT [EventID],[EventTitle],[CalendarID],CalendarName=              
  (SELECT [CalendarName] FROM [tblCalendar] WHERE [CalendarID]=E.[CalendarID]),              
  [StartDate],[Location],[EventType],[EventStatus],[ShareStatus],
  Commentcount=(select count(commentId)from tblcomments where eventid=E.eventId)         
  from tblevents E         
  where E.UserId=@UserId  and E.EventType='ET'    
    
else if(@Mode='Appoints')       
  SELECT [EventID],[EventTitle],[CalendarID],CalendarName=              
  (SELECT [CalendarName] FROM [tblCalendar] WHERE [CalendarID]=E.[CalendarID]),              
  [StartDate],[Location],[EventType],[EventStatus],[ShareStatus],
  Commentcount=(select count(commentId)from tblcomments where eventid=E.eventId)       
  from tblevents E         
  where E.UserId=@UserId  and E.EventType='AP'    
    
else if(@Mode='Tasks')       
  SELECT [EventID],[EventTitle],[CalendarID],CalendarName=              
  (SELECT [CalendarName] FROM [tblCalendar] WHERE [CalendarID]=E.[CalendarID]),              
  [StartDate],[Location],[EventType],[EventStatus],[ShareStatus],
  Commentcount=(select count(commentId)from tblcomments where eventid=E.eventId)         
  from tblevents E         
  where E.UserId=@UserId  and E.EventType='TS'      
    
else if(@Mode='Public')       
  SELECT [EventID],[EventTitle],[CalendarID],CalendarName=              
  (SELECT [CalendarName] FROM [tblCalendar] WHERE [CalendarID]=E.[CalendarID]),              
  [StartDate],[Location],[EventType],[EventStatus],[ShareStatus],
  Commentcount=(select count(commentId)from tblcomments where eventid=E.eventId)         
  from tblevents E         
  where E.UserId=@UserId  and E.ShareStatus='PB'      
    
else if(@Mode='Private')       
  SELECT [EventID],[EventTitle],[CalendarID],CalendarName=              
  (SELECT [CalendarName] FROM [tblCalendar] WHERE [CalendarID]=E.[CalendarID]),              
  [StartDate],[Location],[EventType],[EventStatus],[ShareStatus],
  Commentcount=(select count(commentId)from tblcomments where eventid=E.eventId)        
  from tblevents E         
  where E.UserId=@UserId  and E.ShareStatus='PV'      
    
else if(@Mode='Protected')       
  SELECT [EventID],[EventTitle],[CalendarID],CalendarName=              
  (SELECT [CalendarName] FROM [tblCalendar] WHERE [CalendarID]=E.[CalendarID]),              
  [StartDate],[Location],[EventType],[EventStatus],[ShareStatus],
  Commentcount=(select count(commentId)from tblcomments where eventid=E.eventId)         
  from tblevents E         
  where E.UserId=@UserId  and E.ShareStatus='PT'      
end
GO

--To Get All Events belongs to a calendar and a User        
CREATE PROCEDURE spEventManagement;6                
@UserId bigint,        
@CalendarId bigint        
AS                
Begin                
SELECT E.EventID,E.EventTitle,E.CalendarId,          
E.StartDate,E.Location,E.EventType,E.EventStatus,E.ShareStatus,
Commentcount=(select count(commentId)from tblcomments where eventid=E.eventId),
C.CalendarName         
from tblevents E left join tblCalendar C on E.calendarid=C.calendarid where E.UserId=@UserId  and E.calendarid=@CalendarId           
end
GO

--To get the details of the Event without userid and calendarid             
CREATE PROCEDURE spEventManagement;7                
@UserId bigint,        
@EventId bigint              
AS                
Begin   
declare @eventCount bigint  
select @eventCount=  
( select isnull(eventid,0) from tblevents where eventid=@EventId and    
  ( userid=@UserId or     
    sharestatus='PB' or    
    (sharestatus='PT' and calendarid in     
      (    
        select calendarid from tblcalendargroup where groupid in    
        (      
          select groupId from tblgroupuser where userid=@UserId and isvalid=1    
        )    
      )    
    )    
  )   
)     
if(@eventCount>0)    
select StartDate,EndDate,EventTitle,EventType,EventDescription,EventStatus,ShareStatus,      
CommentCount=(select count(commentId)from tblcomments where eventid=E.eventId),
Location,remindermode,recurringmode,isnull(categoryid,0),Userid,calendarid     
from tblevents E where eventId=@EventId              
end
GO

--To Get all Events for admin user              
CREATE PROCEDURE spEventManagement;8      
@Mode nvarchar(10),          
@CurrDate datetime          
AS                      
Begin            
if(@Mode='All'or @Mode='')                    
  SELECT [EventID],[EventTitle],[CalendarID],CalendarName=                
  (SELECT [CalendarName] FROM [tblCalendar] WHERE [CalendarID]=E.[CalendarID]),                
  [StartDate],[Location],[EventType],[EventStatus],[ShareStatus],
  CommentCount=(select count(commentId)from tblcomments where eventid=E.eventId)          
  from tblevents E           
  order by startdate                     
else if(@Mode='Pending')         
  SELECT [EventID],[EventTitle],[CalendarID],CalendarName=                
  (SELECT [CalendarName] FROM [tblCalendar] WHERE [CalendarID]=E.[CalendarID]),                
  [StartDate],[Location],[EventType],[EventStatus],[ShareStatus],
  CommentCount=(select count(commentId)from tblcomments where eventid=E.eventId)
  from tblevents E           
  where E.EventStatus!='DN' and @currdate>enddate          
  and E.startdate<@CurrDate       
else if(@Mode='Recurring')         
  SELECT [EventID],[EventTitle],[CalendarID],CalendarName=                
  (SELECT [CalendarName] FROM [tblCalendar] WHERE [CalendarID]=E.[CalendarID]),                
  [StartDate],[Location],[EventType],[EventStatus],[ShareStatus],
  CommentCount=(select count(commentId)from tblcomments where eventid=E.eventId)
  from tblevents E    
  where E.recurringmode!='NN'       
else if(@Mode='OnProcess')         
  SELECT [EventID],[EventTitle],[CalendarID],CalendarName=                
  (SELECT [CalendarName] FROM [tblCalendar] WHERE [CalendarID]=E.[CalendarID]),                
  [StartDate],[Location],[EventType],[EventStatus],[ShareStatus],
  CommentCount=(select count(commentId)from tblcomments where eventid=E.eventId)
  from tblevents E           
  where E.EventStatus='OP'       
  --and(@currdate between startdate and enddate or @currdate<startdate)      
        
else if(@Mode='Completed')         
  SELECT [EventID],[EventTitle],[CalendarID],CalendarName=                
  (SELECT [CalendarName] FROM [tblCalendar] WHERE [CalendarID]=E.[CalendarID]),                
  [StartDate],[Location],[EventType],[EventStatus],[ShareStatus],
  CommentCount=(select count(commentId)from tblcomments where eventid=E.eventId)
  from tblevents E           
  where E.EventStatus='DN'       
      
else if(@Mode='Events')         
  SELECT [EventID],[EventTitle],[CalendarID],CalendarName=                
  (SELECT [CalendarName] FROM [tblCalendar] WHERE [CalendarID]=E.[CalendarID]),                
  [StartDate],[Location],[EventType],[EventStatus],[ShareStatus],
  CommentCount=(select count(commentId)from tblcomments where eventid=E.eventId)
  from tblevents E           
  where E.EventType='ET'      
      
else if(@Mode='Appoints')         
  SELECT [EventID],[EventTitle],[CalendarID],CalendarName=                
  (SELECT [CalendarName] FROM [tblCalendar] WHERE [CalendarID]=E.[CalendarID]),                
  [StartDate],[Location],[EventType],[EventStatus],[ShareStatus],
  CommentCount=(select count(commentId)from tblcomments where eventid=E.eventId)
  from tblevents E           
  where E.EventType='AP'      
      
else if(@Mode='Tasks')         
  SELECT [EventID],[EventTitle],[CalendarID],CalendarName=                
  (SELECT [CalendarName] FROM [tblCalendar] WHERE [CalendarID]=E.[CalendarID]),                
  [StartDate],[Location],[EventType],[EventStatus],[ShareStatus],
  CommentCount=(select count(commentId)from tblcomments where eventid=E.eventId)
  from tblevents E           
  where E.EventType='TS'        
      
else if(@Mode='Public')         
  SELECT [EventID],[EventTitle],[CalendarID],CalendarName=                
  (SELECT [CalendarName] FROM [tblCalendar] WHERE [CalendarID]=E.[CalendarID]),                
  [StartDate],[Location],[EventType],[EventStatus],[ShareStatus],
  CommentCount=(select count(commentId)from tblcomments where eventid=E.eventId)
  from tblevents E           
  where E.ShareStatus='PB'        
      
else if(@Mode='Private')         
  SELECT [EventID],[EventTitle],[CalendarID],CalendarName=                
  (SELECT [CalendarName] FROM [tblCalendar] WHERE [CalendarID]=E.[CalendarID]),                
  [StartDate],[Location],[EventType],[EventStatus],[ShareStatus],
  CommentCount=(select count(commentId)from tblcomments where eventid=E.eventId)
  from tblevents E           
  where E.ShareStatus='PV'        
      
else if(@Mode='Protected')         
  SELECT [EventID],[EventTitle],[CalendarID],CalendarName=                
  (SELECT [CalendarName] FROM [tblCalendar] WHERE [CalendarID]=E.[CalendarID]),                
  [StartDate],[Location],[EventType],[EventStatus],[ShareStatus],
  CommentCount=(select count(commentId)from tblcomments where eventid=E.eventId)
  from tblevents E           
  where E.ShareStatus='PT'        
end
GO

--To get the details of the Event for admin view         
CREATE PROCEDURE spEventManagement;9          
@EventId bigint                
AS                  
Begin  
select StartDate,EndDate,EventTitle,EventType,EventDescription,EventStatus,ShareStatus,        
CommentCount=(select count(commentId)from tblcomments where eventid=E.eventId),
Location,remindermode,recurringmode,isnull(categoryid,0),Userid,calendarid       
from tblevents E where eventId=@EventId                
end
GO

--To get all events belongs to a calendar for import         
CREATE PROCEDURE spEventManagement;10          
@UserId bigint,
@CalendarId bigint,
@FirstDate datetime,
@Lastdate datetime            
AS                  
Begin  
select EventTitle,StartDate,EndDate,EventType,EventDescription,Location,remindermode,recurringmode
from tblevents where userid=@userid and 
(   
 @FirstDate between startDate and endDate   
 or @Lastdate between startdate and enddate   
 or startdate between @FirstDate and @Lastdate  
 or(startdate<=@LastDate and (recurringmode='DAY' or recurringMode='MNT' or recurringMode='YER'))    
)            
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE  spExportCalendandarEvents

@UserID BIGINT,
@CalendarID BIGINT

AS

SELECT     tblEvents.StartDate, tblEvents.EndDate, tblEvents.EventType, tblEvents.EventDescription, tblEvents.EventStatus, tblEvents.ShareStatus, 
                      tblEvents.EventTitle, tblEvents.Location, tblCalendar.CalendarName
FROM         tblEvents INNER JOIN
                      tblCalendar ON tblEvents.CalendarID = tblCalendar.CalendarID
WHERE  tblCalendar.UserID = @UserID AND tblCalendar.CalendarID = @CalendarID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

--To add new Group  
CREATE  procedure spGroupManagment;1  
@UserId bigint,  
@GroupName nvarchar(50),  
@CreatedDate datetime,  
@Description nvarchar(200),  
@Status int OUT  
as  
Begin  
declare @RCount int  
   SELECT @RCount=(SELECT COUNT(*) FROM tblgroup WHERE [GroupName]=@GroupName and createdby=@userId)  
   IF(@RCount<1)  
   begin  
    insert into tblgroup(CreatedBy,groupname,createdDate,[Description]) values(@UserId,@GroupName,@CreatedDate,@Description)  
 SELECT @Status=1   
   end  
   ELSE  
 SELECT @Status=0  
End
GO

--To Update Group
CREATE procedure spGroupManagment;2
@GroupId bigint,
@UserId bigint,
@GroupName nvarchar(50),
@Description nvarchar(200)
as
Begin
   update tblgroup set groupname=@GroupName,[Description]=@Description where Createdby=@UserId and Groupid=@GroupId
End
GO

--To Delete Group
CREATE PROCEDURE spGroupManagment;3
@GroupId bigint,
@UserId bigint
as
Begin
   delete from tblgroup where Createdby=@UserId and Groupid=@GroupId
End
GO

--To get all groups belongs to the user
CREATE procedure spGroupManagment;4
@UserId bigint
as
Begin
   select G.GroupId,memeberCount=(select ISNULL((count(*)),0) from tblgroupuser where groupid=g.groupid),G.GroupName,G.CreatedDate,G.[Description] from tblgroup G where G.Createdby=@UserId
End
GO

--To get details about the group
CREATE procedure spGroupManagment;5
@UserId BIGINT,
@GroupId bigint
as
Begin
   select GroupId,memeberCount=(select count(*)from tblgroupuser where groupid=@GroupId),GroupName,CreatedDate,[Description] from tblgroup G where Createdby=@UserId AND GROUPid=@GroupId
End
GO

--To get usernames of the users of the group  
CREATE procedure spGroupManagment;6  
@userId BIGINT, --for the group belongs to 
@groupId bigint
as  
Begin  
declare @rowCount int
	select @rowCount=(select count(Groupname) from tblGroup where createdby=@userid)

	if(@rowCount>0)	
		select U.userId,U.username,GU.IsValid from tblgroupuser GU left join tblusers U on GU.userid=U.userid  where GU.groupId=@groupid order by U.username

End
GO

--To get usernames of the users which is not belongs to the group     
CREATE procedure spGroupManagment;7      
@userId BIGINT, --for the group belongs to     
@groupId bigint    
as      
Begin      
declare @rowCount int    
 select @rowCount=(select count(Groupid) from tblGroup where createdby=@userid)    
    
 if(@rowCount>0)     
  select userId,username from tblusers where userid not in(select distinct userid from tblgroupuser where groupid=@groupid) and userid!=@userId order by username    
 else
   select userId,username from tblusers where userid!=@userId order by username    
End
GO

--To add user to the group     
CREATE procedure spGroupManagment;8      
@userId BIGINT, --for the group belongs to     
@groupId bigint,    
@memberid bigint    
as      
Begin      
declare @rowCount int    
 -- to     
 select @rowCount=(select count(Groupname) from tblGroup where createdby=@userid)    
    
 if(@rowCount>0) 
 begin    
  select @rowCount=(select count(Groupid) from tblgroupuser where userid=@memberid and Groupid=@groupid)   
  if(@rowCount<1)
  	insert into tblgroupuser(Groupid,userid,Isvalid) values(@groupid,@memberid,0)    
 end
    
End
GO

--To get the members of the group that is not confirmed the membership    
CREATE procedure spGroupManagment;9      
@userId BIGINT,   
@groupId bigint  
  
as      
Begin      
declare @rowCount int    
 -- to     
 select @rowCount=(select count(Groupname) from tblGroup where createdby=@userid)    
    
 if(@rowCount>0)     
  select userid from tblgroupuser where groupid=@groupid and isvalid=0    
    
End
GO

--To Send group invitation - insert a invitation entry in the tatble tblInvitation        
CREATE procedure spGroupManagment;10          
@userId BIGINT,       
@groupId bigint,      
@RecieverId bigint,      
@SendDate Datetime      
as          
Begin     
 declare @groupName nvarchar(50);    
 DECLARE @fullName nvarchar(55);    
select @groupName=(select groupname from tblgroup where groupid=@groupid)         
select @fullName=(select firstName+' '+lastname from tblusers where userid=@userid)         
 insert into tblInvitations(senderId,RecieverId,Sentdate,Status,RefId,InvitationTitle,[description],Type)       
 values(@userId,@RecieverId,@SendDate,'N',@GroupId,'Invitation to join group, '+@groupName,@fullName+' sent you an invitation to join the group,'+@groupName,'G' )          
End
GO

--To delete a member from a group  
CREATE procedure spGroupManagment;11
@OwnerId BIGINT,   
@GroupId bigint, 
@MemberId bigint
as

begin
 declare @RowCount int
 select @RowCount=(select isnull(count(Groupid),0) from tblgroup where groupid=@GroupId and createdby=@OwnerId)

 if (@RowCount>0)
     delete from tblgroupUser where userid=@MemberId and groupid=@GroupId
end
GO

--To get the members of the group who accepted the membership        
CREATE procedure spGroupManagment;12          
@userId BIGINT,       
@groupId bigint      
      
as          
Begin          
declare @rowCount int        
 -- to         
 select @rowCount=(select count(Groupname) from tblGroup where createdby=@userid)        
        
 if(@rowCount>0)         
  select U.userid,U.firstname,U.lastname,U.mobile1,U.mobile2,U.phone,U.emailid from (select userid from tblgroupuser where groupid=@groupid) as R left join tblusers as U on R.userid=U.userid
End
GO

--To Send Event invitation - insert a invitation entry in the tatble tblInvitation        
CREATE procedure spGroupManagment;14          
@userId BIGINT,       
@eventId bigint,      
@RecieverId bigint,      
@SendDate Datetime      
as          
Begin     
 declare @eventName nvarchar(50)    
 DECLARE @fullName nvarchar(55)    
 declare @eventdescription nvarchar(200)    
select @eventName=(select eventtitle from tblevents where eventid=@eventId)    
select @eventdescription=(select eventdescription from tblevents where eventid=@eventId)       
select @fullName=(select firstName+' '+lastname from tblusers where userid=@userid)         
 insert into tblInvitations(senderId,RecieverId,Sentdate,Status,RefId,InvitationTitle,[description],Type)       
 values(@userId,@RecieverId,@SendDate,'N',@eventId,  
'Invitation for the event, '+@eventName,  
@fullName+' sent you an invitation to for the event, '+@eventName+'. '+@eventdescription,  
'E' )          
End
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

--To Delete A Holiday
CREATE PROCEDURE [spHolidayDelete]  
@HolidayId  bigint,
@calendarId bigint
  
AS DELETE tblHolidays   
  
WHERE   
[HolidayId]  = @HolidayId and 
Calendarid=@calendarid
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

--Insert Holiday for a specified calendar
CREATE PROCEDURE [spHolidayInsert]  
 (  
  @HolidayDate  [datetime],  
  @Description  [nvarchar](100),  
  @Title [nvarchar](50),
  @Calendarid bigint  
)  
AS INSERT INTO tblHolidays([HolidayDate],[Description],Title,calendarId)   
VALUES (@HolidayDate,@Description,@Title,@CalendarId)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

--To get Holiday detials between a period
CREATE PROCEDURE spHolidayManagement;1
@UserId bigint,
@CalendarId bigint,
@StartDate datetime,
@EndDate datetime
AS 
select H.holidayid,H.title,H.holidaydate,H.[description] 
from tblHolidays H left join tblCalendar C 
on H.calendarid=C.Calendarid
WHERE   
H.Calendarid=@calendarid and C.Userid=@Userid and H.holidaydate between @StartDate and @EndDate
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

--To Get all the Holidays belongs to a User
CREATE PROCEDURE spHolidaySelectAll
@UserId bigint
as  
SELECT H.HolidayId, H.HolidayDate, H.[Description], H.Title ,C.calendarName,C.CalendarId
FROM tblHolidays H join tblCalendar C on H.calendarid= C.calendarid
where C.userid=@Userid
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

--To select a single holiday for view the holiday
CREATE PROCEDURE spHolidaySelectSingle   
@Userid Bigint,
@HolidayId BIGINT  
as  
SELECT H.HolidayId, H.HolidayDate, H.[Description],H.Title,H.CalendarId 
FROM tblHolidays H join tblCalendar C on H.calendarid= C.calendarid
WHERE HolidayId = @HolidayId and C.Userid=@Userid
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

--To upldate holiday
CREATE PROCEDURE [spHolidayUpdate]  
 (@HolidayId  [bigint],  
  @HolidayDate  [datetime],  
  @Description  [nvarchar](100),  
  @Title [nvarchar](50),
  @CalendarId bigint  
)  
  
AS UPDATE tblHolidays 
  
SET  [HolidayDate]  = @HolidayDate,  
  [Description]  = @Description,  
 Title = @Title  
WHERE   
 ( [HolidayId]  = @HolidayId) and calendarid=@CalendarId
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

--To get all the tblInvitations belongs to the user        
CREATE procedure spInvitationManagment;1           
@userId BIGINT         
As        
Begin            
select I.invitationid,I.invitationTitle,I.senderId,        
  sendername=(select username from tblusers where userid=I.senderId),        
  I.sentdate,I.status,I.refId,I.type
  from tblInvitations I 
  where RecieverId=@UserId  
  and Status!='D' 
  order by I.sentDate DESC         
End
GO

--To get the Invitation Details           
CREATE procedure spInvitationManagment;2               
@invitationId bigint,          
@userId BIGINT           
As            
Begin           
--update the status that the notification is read        
update tblinvitations set status='R' where invitationid=@invitationid and recieverid=@userid     
if(@@rowcount>0)
begin
	--get Invitation details             
	select I.invitationid,I.invitationTitle,I.senderId,            
	sendername=(select username from tblusers where userid=I.senderId),            
	I.sentdate,I.status,I.refId,I.type,I.[description]
	from tblInvitations I where RecieverId=@UserId  and invitationId=@invitationId order by I.sentDate desc        
end
End
GO

--To update Invitation Status    
CREATE procedure spInvitationManagment;3         
@userId BIGINT,    
@invitationId bigint,    
@GroupId bigint,    
@Status nvarchar(1)    
As      
Begin          
update tblinvitations set status=@Status where invitationid=@invitationId and refid=@groupid and recieverid=@userid    
end
GO

--To update membership status    
CREATE procedure spInvitationManagment;4         
@userId BIGINT,    
@GroupId bigint, 
@GroupName nvarchar(50) out      
As      
Begin          
update tblGroupUser set isvalid=1 where userid=@userid and Groupid=@groupid 
select @GroupName=(select groupname from  tblgroup where groupid=@groupId)
end
GO

--To Send Event invitation - insert a invitation entry in the tatble tblInvitation          
CREATE procedure spInvitationManagment;5            
@userId BIGINT,         
@eventId bigint,        
@RecieverId bigint,        
@SendDate Datetime        
as            
Begin       
 declare @eventName nvarchar(50)      
 DECLARE @fullName nvarchar(55)      
 declare @eventdescription nvarchar(200)      
select @eventName=(select eventtitle from tblevents where eventid=@eventId)      
select @eventdescription=(select eventdescription from tblevents where eventid=@eventId)         
select @fullName=(select firstName+' '+lastname from tblusers where userid=@userid)           
 insert into tblInvitations(senderId,RecieverId,Sentdate,Status,RefId,InvitationTitle,[description],Type)         
 values(@userId,@RecieverId,@SendDate,'N',@eventId,    
'Invitation for the event, '+@eventName,    
@fullName+' sent you an invitation to for the event, '+@eventName+'. '+@eventdescription,    
'E' )            
End
GO

--To Send Event invitation - insert a invitation entry in the tatble tblInvitation          
CREATE procedure spInvitationManagment;6
@ReplayTitle nvarchar(100),         
@SenderId BIGINT, 
@RecieverId BIGINT,
@SendDate datetime,       
@eventId bigint,        
@Status nvarchar(1),        
@Type nvarchar(2),
@Replay nvarchar(250)        
as            
Begin       
 
 DECLARE @fullName nvarchar(55)          
  
 
select @fullName=(select firstName+' '+lastname from tblusers where userid=@SenderId)           
 insert into tblInvitations(senderId,RecieverId,Sentdate,Status,RefId,InvitationTitle,[description],Type)         
 values(@SenderId,@RecieverId,@SendDate,@Status,@eventId,@ReplayTitle,@Replay,@Type)            
End
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

--To Add admin record
CREATE PROCEDURE spLookupManagement;1
@LookupName nvarchar(100),          
@LookUpValue nvarchar(4000),  
@status int out              
AS                
insert into tbllookup(lookupname,lookupvalue) values(@LookupName,@LookupValue)  
if(@@Rowcount>0)  
 select @status=1  
else  
 select @status=-1
GO

--To Add appsetting details
CREATE PROCEDURE spLookupManagement;2 
@LookupName nvarchar(100),          
@LookUpValue nvarchar(4000),  
@status int out                  
 AS  
declare @Count int
set @Count=(select count(lookupid) from tbllookup where lookupname='AppSettings')
if(@Count>0)
   update tbllookup set lookupvalue=@LookUpValue where lookupname='AppSettings'
else
  insert into tbllookup(lookupname,lookupvalue) values(@LookupName,@LookupValue)  
if(@@Rowcount>0)  
 select @status=1  
else  
 select @status=-1
GO

--To Get appsettings
CREATE PROCEDURE spLookupManagement;3 
@LookupName nvarchar(100) out,          
@LookUpValue nvarchar(4000) out,  
@status int out                  
 AS  
declare @id bigint
set @id=(select isnull(max(lookupid),0) from tbllookup where lookupname='AppSettings')
if(@id>0)
   begin
   select @LookUpValue=(select lookupvalue from tbllookup where lookupid=@id)
   select @status=1  
   end
else
   select @status=-1
GO

--To Add SQL connection details
CREATE PROCEDURE spLookupManagement;4 
@LookupName nvarchar(100),          
@LookUpValue nvarchar(4000),  
@status int out                  
 AS  
declare @Count int
set @Count=(select count(lookupid) from tbllookup where lookupname='SqlConStr')
if(@Count>0)
   update tbllookup set lookupvalue=@LookUpValue where lookupname='SqlConStr'
else
  insert into tbllookup(lookupname,lookupvalue) values(@LookupName,@LookupValue)  
if(@@Rowcount>0)  
 select @status=1  
else  
 select @status=-1
GO

--To Get SQL connection details
CREATE PROCEDURE spLookupManagement;5 
@LookupName nvarchar(100) out,          
@LookUpValue nvarchar(4000) out,  
@status int out                  
 AS  
declare @id bigint
set @id=(select isnull(max(lookupid),0) from tbllookup where lookupname='SqlConStr')
if(@id>0)
   begin
   select @LookUpValue=(select lookupvalue from tbllookup where lookupid=@id)
   select @status=1  
   end
else
   select @status=-1
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/*To get Reminder Details for a period used for dayview,monthview,weekview and 5daysview */      
CREATE procedure spReminderManagement;1            
@FirstDate datetime,  
@LastDate datetime      
as    
select R.UserName,R.Mobile1,C.CalendarName,R.eventId,R.eventTitle,R.eventType,R.startDate,R.endDate,R.remindermode,R.Eventdescription,R.Emailid,R.location,R.recurringMode
from
  ( select U.FirstName + ' '+U.LastName as UserName,U.mobile1,E.eventId,E.Calendarid,E.eventTitle,E.eventType,
     E.startDate,E.endDate,E.remindermode,isnull(E.Eventdescription,'') as eventdescription,U.emailid,E.location,E.recurringMode
    from tblEvents E left join tblUsers U on U.userid=E.userid    
    where 
    @FirstDate between startDate and endDate   
    or @Lastdate between startdate and enddate   
    or startdate between @FirstDate and @Lastdate  
    or(startdate<=@LastDate and (recurringmode='DAY' or recurringMode='MNT' or recurringMode='YER')and E.EventType!='DN')  
   )R 
  left join tblCalendar C on R.calendarid=C.calendarid
GO

--Not using
CREATE PROCEDURE spReminderManagement;2    
@CurrDate datetime    
AS     
select top 100 E.Eventid,E.EventTitle, E.startdate    
from tblEvents E
GO

/*To get Reminder Details for a period used for dayview,monthview,weekview and 5daysview */      
CREATE procedure spReminderManagement;3   
@UserId datetime,         
@FirstDate datetime,  
@LastDate datetime      
as    
select R.UserName,R.Mobile1,C.CalendarName,R.eventId,R.eventTitle,R.eventType,R.startDate,R.endDate,R.remindermode,R.Eventdescription,R.Emailid,R.location,R.recurringMode
from
  ( select U.FirstName + ' '+U.LastName as UserName,U.mobile1,E.eventId,E.Calendarid,E.eventTitle,E.eventType,
     E.startDate,E.endDate,E.remindermode,isnull(E.Eventdescription,'') as eventdescription,U.emailid,E.location,E.recurringMode
    from tblEvents E left join tblUsers U on U.userid=E.userid    
    where 
    E.userid=@Userid and
    (
    @FirstDate between startDate and endDate   
    or @Lastdate between startdate and enddate   
    or startdate between @FirstDate and @Lastdate  
    or(startdate<=@LastDate and (recurringmode='DAY' or recurringMode='MNT' or recurringMode='YER') and E.EventType!='DN')  
    )
   )R 
  left join tblCalendar C on R.calendarid=C.calendarid
GO

--to update the reminderstatus
CREATE procedure spReminderManagement;4
@eventId bigint,
@SendDate Datetime
As
update tblevents set reminderSendDate=@senddate where eventid=@eventID
GO

/*To get Reminder Details for a period 
New approach*/
CREATE procedure spReminderManagement;5            
@FirstDate datetime,  
@LastDate datetime      
as    
select R.UserName,R.Mobile1,C.CalendarName,R.eventId,R.eventTitle,R.eventType,R.startDate,R.endDate,R.remindermode,R.Eventdescription,R.Emailid,R.location,R.recurringMode
from
  ( select U.FirstName + ' '+U.LastName as UserName,U.mobile1,E.eventId,E.Calendarid,E.eventTitle,E.eventType,
     E.startDate,E.endDate,E.remindermode,isnull(E.Eventdescription,'') as eventdescription,U.emailid,E.location,E.recurringMode
    from tblEvents E left join tblUsers U on U.userid=E.userid    
    where
    E.EventType!='DN' and E.remindermode!='NN'and
    (  
      (
    	E.recurringmode='NN' and    
        E.startDate between @FirstDate and @LastDate and 
        (E.reminderSendDate=null or E.reminderSendDate<dateAdd(Hour,-2,E.startdate))
      
      )
      or
      ( 
        E.recurringmode!='NN' and E.startdate<=@LastDate 
      )  
    )
   )R 
  left join tblCalendar C on R.calendarid=C.calendarid
GO

/*To get Reminder Details for a period with userId
This is for showing popup remindar for a purticular user
New approach*/
CREATE procedure spReminderManagement;6 
@userId bigint,           
@FirstDate datetime,  
@LastDate datetime      
as    
select R.UserName,R.Mobile1,C.CalendarName,R.eventId,R.eventTitle,R.eventType,R.startDate,R.endDate,R.remindermode,R.Eventdescription,R.Emailid,R.location,R.recurringMode
from
  ( select U.FirstName + ' '+U.LastName as UserName,U.mobile1,E.eventId,E.Calendarid,E.eventTitle,E.eventType,
     E.startDate,E.endDate,E.remindermode,isnull(E.Eventdescription,'') as eventdescription,U.emailid,E.location,E.recurringMode
    from tblEvents E left join tblUsers U on U.userid=E.userid    
    where
    E.userId=@userId and E.EventType!='DN' and E.remindermode!='NN'and
    (  
      (
    	E.recurringmode='NN' and    
        E.startDate between @FirstDate and @LastDate and 
        (E.reminderSendDate=null or E.reminderSendDate<dateAdd(Hour,-2,E.startdate))
      
      )
      or
      ( 
        E.recurringmode!='NN' and E.startdate<=@LastDate 
      )  
    )
   )R 
  left join tblCalendar C on R.calendarid=C.calendarid
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

--To search for Events that have access to the user          
CREATE procedure spSearchManagment;1            
@UserId bigint,               
@UserName nvarchar(50),            
@CalendarName nvarchar(50),            
@EventTitle nvarchar(100),            
@Location nvarchar(50),        
@CategoryId bigint           
As              
Begin           
        
if(@CategoryId>0)               
 select * from(         
  select Username=(select username from tblusers where userid=E.userid),              
  Calendername=(select calendarname from tblcalendar where calendarid=E.calendarid),              
  E.Eventid,E.EventTitle,E.startdate,E.eventtype,E.location,E.eventstatus,E.sharestatus,  
  Commentcount=(select count(commentId)from tblcomments where eventid=E.eventId),  
  isnull(E.categoryId,0) as CategoryId            
  from tblEvents E              
  where E.userid=@UserId or       
 E.sharestatus='PB' or      
 (      
    sharestatus='PT' and       
    E.calendarid in(      
       select calendarid from tblcalendargroup where groupid in(      
                select groupId from tblgroupuser where userid=@userid and isvalid=1      
      )      
           )      
         )       
        )R          
 where R.username like '%'+@Username+'%'            
 and R.Calendername like '%'+@CalendarName+'%'             
 and R.EventTitle like '%'+@EventTitle+'%'            
 and R.Location like '%'+@Location+'%'         
 and R.categoryId=@CategoryId       
else        
  select * from(              
              
  select Username=(select username from tblusers where userid=E.userid),              
  Calendername=(select calendarname from tblcalendar where calendarid=E.calendarid),              
  E.Eventid,E.EventTitle,E.startdate,E.eventtype,E.location,E.eventstatus,E.sharestatus,  
  Commentcount=(select count(commentId)from tblcomments where eventid=E.eventId),  
  isnull(E.categoryId,0) as CategoryId            
  from tblEvents E              
  where E.userid=@UserId or       
 E.sharestatus='PB' or      
 (      
    sharestatus='PT' and       
    E.calendarid in(      
       select calendarid from tblcalendargroup where groupid in(      
                select groupId from tblgroupuser where userid=@userid and isvalid=1      
      )      
           )      
         )       
        )R               
 where R.username like '%'+@Username+'%'              
 and R.Calendername like '%'+@CalendarName+'%'               
 and R.EventTitle like '%'+@EventTitle+'%'              
 and R.Location like '%'+@Location+'%'              
end
GO

--To search for Events with full rights by the admin
CREATE procedure spSearchManagment;2           
@UserName nvarchar(50),          
@CalendarName nvarchar(50),          
@EventTitle nvarchar(100),          
@Location nvarchar(50),      
@CategoryId bigint         
As            
Begin         
      
if(@CategoryId>0)             
 select * from(       
  select Username=(select username from tblusers where userid=E.userid),            
  Calendername=(select calendarname from tblcalendar where calendarid=E.calendarid),            
  E.Eventid,E.EventTitle,E.startdate,E.eventtype,E.location,E.eventstatus,E.sharestatus,
  Commentcount=(select count(commentId)from tblcomments where eventid=E.eventId),
  isnull(E.categoryId,0) as CategoryId          
  from tblEvents E  )R        
 where R.username like '%'+@Username+'%'          
 and Calendername like '%'+@CalendarName+'%'           
 and EventTitle like '%'+@EventTitle+'%'          
 and Location like '%'+@Location+'%'       
 and categoryId=@CategoryId     
else      
  select * from(   
  select Username=(select username from tblusers where userid=E.userid),            
  Calendername=(select calendarname from tblcalendar where calendarid=E.calendarid),            
  E.Eventid,E.EventTitle,E.startdate,E.eventtype,E.location,E.eventstatus,E.sharestatus,
  Commentcount=(select count(commentId)from tblcomments where eventid=E.eventId),
  isnull(E.categoryId,0) as CategoryId          
  from tblEvents E )R             
 where R.username like '%'+@Username+'%'            
 and Calendername like '%'+@CalendarName+'%'             
 and EventTitle like '%'+@EventTitle+'%'            
 and Location like '%'+@Location+'%'            
end
GO

--To get usernames of the users which is not belongs to the group     
CREATE procedure spSearchManagment;3     
@UserId BIGINT,
@GroupId bigint, --for the group belongs to     
@Username nvarchar(50),
@Firstname nvarchar(25),
@Lastname nvarchar(25) 
as      
Begin      
declare @rowCount int    
 select @rowCount=(select count(Groupid) from tblGroup where createdby=@userid)    
    
 if(@rowCount>0)     
  select userId,username,firstname,lastname from tblusers where 
  userid not in(select distinct userid from tblgroupuser where groupid=@groupid) 
  and userid!=@userId 
  and username like '%'+@Username+'%'
  and Firstname like '%'+@FirstName+'%'
  and Lastname like '%'+@Lastname+'%'
  order by username    
 else
   select userId,username,firstname,lastname
   from tblusers where 
   userid!=@userId 
  and username like '%'+@Username+'%'
  and Firstname like '%'+@FirstName+'%'
  and Lastname like '%'+@Lastname+'%'
   order by username    
End
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/*To tag a user to the group */
CREATE procedure spTagManagement;1              
@TaggedBy bigint,
@TaggedDate datetime,
@emailId nvarchar(50),
@groupid bigint,
@tagId bigint output,
@GroupName nvarchar(50) output
As     
declare @Count int
select @Count=(select count(G.Groupid) from tblgroupuser G left join tblusers U on G.userid=U.userid where U.emailid=@emailId)
if(@Count>0)--check whether the user already exists in the group
begin
	select @tagid=-1; --user already exists in the group
end
else
begin
  select @Count=(select count(tagid) from tbltagged where emailid=@emailId)
  if(@Count>0)
  begin
    select @tagid=-2;  --user already tagged.
  end
  else
  begin
     insert into tblTagged(taggedBy,EmailId,Groupid,IsValid,TaggedDate)
     values(@TaggedBy,@emailId, @groupid,0, @TaggedDate)
     select @tagid=(select max(tagid) from tbltagged)
     select @GroupName=(select groupname from tblgroup where groupid=@Groupid)
  end
end
GO

/*To all tagged user belogs to a group*/  
CREATE procedure spTagManagement;2               
@userid bigint,  
@groupid bigint    
As    
select emailid,taggedDate,isvalid from tblTagged where taggedby=@userid and groupid=@groupid
GO

/*To accept membership for a tagged user in a group*/  
CREATE procedure spTagManagement;3
@TagId BigInt,
@groupId BigInt,
@TaggedById BigInt,
@UserName NVarChar(50),
@Password NVarChar(25),
@status int out
As       
declare @count bigint
declare @Userid bigint
select @Userid=(select isnull(max(userid),0) from tblusers where username=@username and [password]=@password)
if(@Userid<1)
begin
  select @status=-1  --user does not exist
end
else
begin
  select @count =(select count(userid) from tblgroupuser where groupid=@Groupid and userid=@Userid)
  if(@count>0)
  begin
    select @status=-2  --user already a member of the group
  end
  else
     begin
	select  @count =(select count(tagid) from tbltagged where tagid=@tagid and taggedby=@TaggedById and groupid=@Groupid)
   	if(@count<1)
  	begin
    	   select @status=-3  --not a tagged user
  	end
	else
  	begin
    		insert into tblgroupuser(groupid,userid,isvalid) values(@groupid,@Userid,1);
    		delete from tbltagged where tagid=@tagid
    		select @status=1;
	end
    end
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

--To Create a new user Account            
CREATE PROCEDURE spUserManagement;1              
@UserName nvarchar(50),            
@pwd nvarchar(25),            
@FName nvarchar(25),            
@LName nvarchar(25),            
@Address1 varchar(100),            
@Address2 varchar(100),            
@Mobile1 varchar(15),            
@Mobile2 varchar(15),      
@Phone varchar(15),          
@EmailId varchar(50),    
@SecureQId bigint,    
@Answer varchar(50),  
@Style varchar(50), 
@RegisterDate datetime,   
@status int out              
 AS            
declare @RecordCount int         
          
begin         
 select @RecordCount=0         
 select @RecordCount=(select count(*) from tblUsers where username=@Username)        
            
if(@RecordCount>0)        
 select @status=-1        
else        
 begin        
  select @RecordCount=0         
  select @RecordCount=(select count(*) from tblUsers where emailid=@EmailId)        
  if(@RecordCount>0)        
     select @status=-2        
  else        
  begin        
   insert into tblUsers(username,[password],firstname,lastname,address1,address2,mobile1,mobile2,phone,emailid,secureQId,Answer,style,registerdate)             
   values(@UserName,@pwd,@FName,@LName,@Address1,@Address2,@Mobile1,@Mobile2,@Phone,@EmailId,@SecureQId,@Answer,@style,@RegisterDate)            
   if (@@rowcount>0)              
      select @status=1              
   else              
      select @status=0          
  end            
 end            
end
GO

--To validate the user using username and password         
--This will return userInformation whether the user is valid othewise -1         
CREATE PROCEDURE spUserManagement;2            
@UserName nvarchar(50),
@pwd nvarchar(25), 
@UserId bigInt out,
@Style nvarchar(50) out           
 AS         
declare @Count int        
begin            
 select @Count=(select count(UserId) from tblUsers where username=@UserName and password=@pwd and(isnull(status,'')='' or status='ACT'))        
     
  if(@Count<1)
  begin      
    select @UserId=-1  
  end     
  else 
  Begin
    if(@Count>1)             
      select @UserId=(select min(Result.userId) from(select userId from tblUsers where username=@UserName and password=@pwd)Result)          
    else       
      select @UserId=(select UserId from tblUsers where username=@UserName and password=@pwd)      

    select @Style=(select style from tblUsers where userid=@UserId)
  End     
end
GO

--To check the username is available
--The value of @status will be 1 if the username is availabel otherwise 0 
CREATE PROCEDURE spUserManagement;3  
@UserName nvarchar(50), @status bit out  
 AS  
begin  
 select UserName from tblUsers where username=@UserName 
 if (@@rowcount>0)  
     select @status=1  
 else  
  select @status=0  
  
end
GO

--To all the Security Questions  
CREATE PROCEDURE spUserManagement;4    

 AS    
begin    
 select QuestionId,QuestionText from tblSecurityQuestions
end
GO

--To check email and security question answer for 'forgot password'
CREATE PROCEDURE spUserManagement;5      
@emailId nvarchar(50), 
@Answer nvarchar(50) 
 AS      
begin      
 select UserId,FirstName,LastName,Username,[Password] from tblusers where emailid=@emailId and answer=@answer 
end
GO

--To get Security Question using emailid
CREATE PROCEDURE spUserManagement;6      
@emailId nvarchar(50) 
AS      
begin      
 select S.QuestionText from tblusers 
U left join tblSecurityQuestions S on U.SecureQId=S.QuestionId 
where U.emailid=@emailId 
end
GO

--To Update a user Account          
CREATE PROCEDURE spUserManagement;7            
@UserId bigint,         
@FName nvarchar(25),          
@LName nvarchar(25),          
@Address1 varchar(100),          
@Address2 varchar(100),          
@Mobile1 varchar(15),          
@Mobile2 varchar(15),    
@Phone varchar(15),        
@EmailId varchar(50),  
@SecureQId bigint,  
@Answer varchar(50), 
@Style varchar(50), 
@status int out            
 AS          
declare @RecordCount int       
        
begin       
 select @RecordCount=0       
 select @RecordCount=(select count(Userid) from tblUsers where emailid=@EmailId and not userid=@UserId)      
  if(@RecordCount>0)      
     select @status=-1      
  else      
  begin      
   update tblUsers set firstname=@FName,lastname=@LName,address1=@Address1,
	address2=@Address2,mobile1=@Mobile1,mobile2=@Mobile2,phone=@Phone,
	emailid=@EmailId,secureQId=@SecureQId,Answer=@Answer,style=@style where userid=@Userid
  
   if (@@rowcount>0)            
      select @status=1            
   else            
      select @status=0        
  end
end
GO

--To Update Password of a user account           
CREATE PROCEDURE spUserManagement;8                
@UserId bigint,           
@Oldpassword nvarchar(25),  
@Newpassword nvarchar(25),  
@status int out              
 AS            
declare @RecordCount int  
  
begin    

   if(len(@Oldpassword)>1)
   begin
 	select @RecordCount=0         
 	select @RecordCount=(select count(Userid) from tblUsers where [password]=@Oldpassword and userid=@UserId)        
  	if(@RecordCount<1)        
     		select @status=-1        
  	else        
  		update tblUsers set [password]=@Newpassword where userid=@Userid  
    
   end
   else
      update tblUsers set [password]=@Newpassword where userid=@Userid 

	if (@@rowcount>0)              
      		select @status=1              
   	else              
     		select @status=0   
print @status
end
GO

--To get user account details  
CREATE PROCEDURE spUserManagement;9                
@UserId bigint  
 AS       
begin       
 select Username,FirstName,LastName,Address1,Address2,EmailId,Mobile1,Mobile2,Phone,SecureQId,Answer,style,status from tblUsers where userid=@UserId  
end
GO

--To validate the admin user using username and password           
--This will return userInformation whether the user is valid     
--othewise -1 for invalid user -2 for not an admin user        
CREATE PROCEDURE spUserManagement;10              
@UserName nvarchar(50),  
@pwd nvarchar(25),   
@UserId bigInt out,  
@Style nvarchar(50) out             
 AS           
declare @Count int          
begin              
 select @Count=(select count(UserId) from tblUsers where username=@UserName and password=@pwd and(isnull(status,'')='' or status='ACT'))          
print @count        
  if(@Count<1)        
    select @UserId=-1          
         
  else     
  begin    
   if(@Count>1)           
        select @UserId=(select min(Result.userId) from(select userId from tblUsers where username=@UserName and password=@pwd)Result)        
        else         
          select @UserId=(select UserId from tblUsers where username=@UserName and password=@pwd)        
    
 select @Count=(select count(lookupid) from tbllookup where lookupvalue=''+@UserId and lookupName='AdminUser' )          
  if(@count<1)    
    select @UserId=-2     
  else             
    select @Style=(select style from tblusers where userid=@Userid)  
  end    
print @UserId        
end
GO

--To get all the user for admin  
CREATE procedure spUserManagement;11  
@Userid bigint  
as  
  select U.userid,U.firstName,U.lastName,  
  CalendarCount=(select Count(calendarId) from tblcalendar where userid=U.userid), 
  status, 
  registerDate,
  username 
  from tblusers U   
  where U.userid!=@Userid
GO

--To upadate user account status
CREATE procedure spUserManagement;12  
@Userid bigint,
@AccountStatus nvarchar(3)  
as  
  update tblusers set status=@AccountStatus
  where userid=@Userid
GO

--To Create a admin account            
CREATE PROCEDURE spUserManagement;13               
@UserName nvarchar(50),              
@pwd nvarchar(25),              
@FName nvarchar(25),              
@LName nvarchar(25),              
@Address1 varchar(100),              
@Address2 varchar(100),              
@Mobile1 varchar(15),              
@Mobile2 varchar(15),        
@Phone varchar(15),            
@EmailId varchar(50),      
@SecureQId bigint,      
@Answer varchar(50),    
@Style varchar(50),   
@RegisterDate datetime,     
@status int out                
 AS              
declare @RecordCount int           
     
begin  
  --check for users with same user name           
  select @RecordCount=0           
  select @RecordCount=(select count(*) from tblUsers where username=@Username)              
  if(@RecordCount>0)          
     delete from tblusers where username=@Username

  --check for users with same email address      
  select @RecordCount=0           
  select @RecordCount=(select count(*) from tblUsers where emailid=@EmailId)          
  if(@RecordCount>0)          
     delete from tblusers where emailid=@EmailId  
    
  -- adding admin users
   insert into tblUsers(username,[password],firstname,lastname,address1,address2,mobile1,mobile2,phone,emailid,secureQId,Answer,style,registerdate)               
   values(@UserName,@pwd,@FName,@LName,@Address1,@Address2,@Mobile1,@Mobile2,@Phone,@EmailId,@SecureQId,@Answer,@style,@RegisterDate)              
   if (@@rowcount>0)                
      select @status=1                
   else                
      select @status=0              
         
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

