Tms-database

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [selectdocid]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' /**************************************************************************** ProcedureName :: selectdocid Purpose :: To Select Data from Documents Table with Attachid CreatedBy :: vamsi.N ****************************************************************************/ CREATE Proc [dbo].[selectdocid] @AttachmentId int as begin select * from Documents where Attachmentid=@AttachmentId end ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [DeleteUser]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' --****************************************************************** -CreatedBy: M.Kamala -Procedure Name:View Users -Description: To View users temporarily from Database --******************************************************************** CREATE procedure [dbo].[DeleteUser] @userid int as begin update usercreation set dstatus=1 where dstatus=0 and userid=@userid end ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [Documents]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[Documents]( [AttachmentId] [int] IDENTITY(1,1) NOT NULL,

[AttachmentName] [varchar](100) NULL, [Attachment] [image] NULL, [Dstatus] [tinyint] NULL CONSTRAINT [DF_Documents_Dstatus]

((0)),

DEFAULT

[CreatedOn] [varchar](50) NULL, [CreatedBy] [varchar](50) NULL, CONSTRAINT [PK_Documents] PRIMARY KEY CLUSTERED

(

[AttachmentId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [ChkLookText]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'/************************************************************************** ** ProcedureName :: ChkLookupText Purpose :: To Check LookupText CreatedBy :: swapna.b ****************************************************************************/ Create PROCEDURE [dbo].[ChkLookText] @LookUpId int, @lookupname varchar(50), @lookuptext varchar(50) as begin select * from lookup where lookupname=@lookupname and lookuptext=@lookuptext and LookUpId <> @LookUpId end ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [chklogin]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' /**************************************************************************** ProcedureName :: chklogin Purpose :: To Check userid, pwd from usercreation table CreatedBy :: swapna.b ****************************************************************************/ CREATE PROCEDURE [dbo].[chklogin] (@username varchar(50), @password varchar(50))

AS

select *, fname + '''' + lname fullname from usercreation where username=@username and password=@password RETURN ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [assignedusers]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE proc [dbo].[assignedusers] as begin select distinct assigneduser from delegatedactivity end ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [lookup]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[lookup]( [lookupid] [int] IDENTITY(1,1) NOT NULL, [lookupname] [varchar](50) NULL, [lookuptext] [varchar](50) NULL, [lookupdescription] [varchar](50) NULL, [dstatus] [tinyint] NOT NULL CONSTRAINT [DF_lookup_dstatus] DEFAULT ((0)), [createdon] [varchar](50) NULL, [createdby] [varchar](50) NULL, [lastediton] [varchar](50) NULL, [lasteditby] [varchar](50) NULL, CONSTRAINT [PK_lookup] PRIMARY KEY CLUSTERED ( [lookupid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [CoromandelNews]') AND type in (N'U'))

BEGIN CREATE TABLE [dbo].[CoromandelNews]( [noticeId] [int] IDENTITY(1,1) NOT NULL, [noticeDate] [varchar](50) NULL, [subject] [varchar](max) NULL, [message] [varchar](max) NULL, [postedBy] [varchar](50) NULL, [PostedSignature] [varchar](50) NULL, [postedUser] [varchar](50) NULL, [status] [tinyint] NULL, [selectStatus] [tinyint] NULL, [CreatedOn] [varchar](50) NULL, [createdBy] [varchar](50) NULL, [LastEditOn] [varchar](50) NULL, [LastEditBy] [varchar](50) NULL ) ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [SaveandUpdateUser]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' ------

==================================================== PROCEDURE : SaveandUpdateUser Created By : Swathi.G Description : To Save and update the user creation ====================================================

CREATE PROCEDURE [dbo].[SaveandUpdateUser] @userid int, @username varchar(50), @password varchar(50), @fname varchar(50), @lname varchar(50), @name varchar(50), @location varchar(50), @designation varchar(50), @reportingto varchar(50), @leveloftheemployee varchar(50) as begin if(@userid ='''' or @userid is null) begin insert into usercreation(username,password,fname,lname,fullname,location,designation,repo rtingto,leveloftheemployee,deletedon) values(@username,@password,@fname,@lname,@name,@location,@designation,@report ingto,@leveloftheemployee, convert(varchar(10),getdate(),101)) set @userid=@@identity

else begin

end

update usercreation set username=@username, password=@password, fname=@fname, lname=@lname, fullname=@name, location=@location, designation=@designation, reportingto=@reportingto, leveloftheemployee=@leveloftheemployee, deletedon= convert(varchar(10),getdate(),101) where userid=@userid end end ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [displayusers]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-=========================================================== -- Author : Kamala.m -- Proceduer name: displayuser -- Description : To display the usercreation details -- =========================================================== CREATE procedure [dbo].[displayusers] as begin select userid,username,fullname,deletedon,dyasleft from viewuser where dstatus=1 and dyasleft >0 order by dyasleft asc end' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [getpercentage]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE proc [dbo].[getpercentage] as begin

select distinct percentagecompletion from delegatedactivity where percentagecompletion <>'''' end ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [getdates]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE proc [dbo].[getdates] as begin select distinct duedate from delegatedactivity where duedate != '''' end ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [SaveUpdateNews]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' /************************************************************************** Stored Procedure :: SaveUpdateNews Purpose :: Save and Update NewsTable CreatedBy :: swapna.b **************************************************************************/ CREATE PROCEDURE [dbo].[SaveUpdateNews] ( @titleid int, @titlename varchar(50), @fromdate varchar(50), @todate varchar(50), @Newsdescription varchar(50) ) as begin if(@titleid is null or @titleid='''') begin insert into

news(titlename,fromdate,todate,Newsdescription)

values(@titlename,@fromdate,@todate,@Newsdescription) set @titleid=@@identity end else begin update news set titlename= @titlename, fromdate = @fromdate, todate = @todate, Newsdescription = @Newsdescription where titleid=@titleid end end /**************************** End Of SaveUpdateNews ***************************************/ ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [completlyDelete]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' ----****************************************************************** ---CreatedBy: M.Kamala ---Procedure Name:completlyDelete ---Description: To View users temporarily from Database ----******************************************************************** CREATE procedure [dbo].[completlyDelete] @userid int as begin delete usercreation where userid=@userid and dstatus=1 end' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON

GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [daysleft]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-****************************************************************** -CreatedBy: M.Kamala -Procedure Name:daysleft -Description: To View users temporarily from Database --******************************************************************** Create procedure [dbo].[daysleft] @userid int as begin select username,90-(datediff(day,deletedon,getdate())) as daysleft usercreation where dstatus=1

from

end --select username,90-(datediff(day,deletedon,getdate())) as daysleft from usercreation where dstatus=1 ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [GetAllNews]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'/************************************************************************** ** ProcedureName :: GetAllNews Purpose :: To Select news Table CreatedBy :: Swapna.B ****************************************************************************/ create PROCEDURE [dbo].[GetAllNews] as begin

select * from news order by titleid DESC end ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [chkdesignation]') AND type in (N'P', N'PC')) BEGIN

EXEC dbo.sp_executesql @statement = N' /**************************************************************************** ProcedureName :: Chkdesignation Purpose :: To Check designation CreatedBy :: swathi.G ****************************************************************************/ CREATE procedure [dbo].[chkdesignation] @userid int, @designation varchar(50) as begin select * from usercreation where designation=@designation and userid<>@userid end ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [GetUserName]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) BEGIN execute dbo.sp_executesql @statement = N' CREATE function [dbo].[GetUserName] (@UserId varchar(30)) returns varchar(50) begin return (select fullname from usercreation where UserId = @UserId); end

' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [gethodpercentage]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE proc [dbo].[gethodpercentage] @reportingname varchar(50) as begin select distinct percentagecompletion from delegate where reportingname = @reportingname end ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [gethoddates]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE proc [dbo].[gethoddates] @reportingname varchar(50) as begin select distinct duedate from delegate where reportingname = @reportingname end ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [gethodusers]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE proc [dbo].[gethodusers] @reportingname varchar(50) as begin select distinct assigneduser from delegate where reportingname=@reportingname end ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [Convertdate]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) BEGIN execute dbo.sp_executesql @statement = N' CREATE function [dbo].[Convertdate] (@Date varchar(30)) returns varchar(50) begin declare @date1 varchar(10) if(@Date is null or @Date = '''') begin set @date1= @Date; end else begin set @date1= (select convert(varchar(10),convert(datetime,@Date),103)) end return @date1; end ' END GO

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [usercreation]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[usercreation]( [userid] [int] IDENTITY(1,1) NOT NULL, [username] [varchar](50) NULL, [password] [varchar](50) NULL, [fname] [varchar](50) NULL, [lname] [varchar](50) NULL, [fullname] [varchar](50) NULL, [location] [varchar](50) NULL, [designation] [varchar](50) NULL, [reportingto] [int] NULL, [leveloftheemployee] [varchar](50) NULL, [dstatus] [varchar](50) NULL CONSTRAINT [DF_usercreation_dstatus] DEFAULT ((0)), [deletedon] [varchar](15) NULL, CONSTRAINT [PK_usercreation] PRIMARY KEY CLUSTERED ( [userid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo]. [viewuser]')) EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[viewuser] AS SELECT userid, username, password, fname + '' '' + lname + ''('' + username + '')'' AS fullname, location, designation, reportingto, leveloftheemployee, dstatus, deletedon, 90 - DATEDIFF(day, deletedon, GETDATE()) AS dyasleft FROM dbo.usercreation ' GO IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPane1' , N'SCHEMA',N'dbo', N'VIEW',N'viewuser', NULL,NULL)) EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0B466-11cf-A24F-00AA00A3EFFF, 1.00] Begin DesignProperties = Begin PaneConfigurations = Begin PaneConfiguration = 0 NumPanes = 4 Configuration = "(H (1[40] 4[20] 2[20] 3) )" End Begin PaneConfiguration = 1 NumPanes = 3

Configuration = "(H (1 [50] 4 [25] 3))" End Begin PaneConfiguration = 2 NumPanes = 3 Configuration = "(H (1 [50] 2 [25] 3))" End Begin PaneConfiguration = 3 NumPanes = 3 Configuration = "(H (4 [30] 2 [40] 3))" End Begin PaneConfiguration = 4 NumPanes = 2 Configuration = "(H (1 [56] 3))" End Begin PaneConfiguration = 5 NumPanes = 2 Configuration = "(H (2 [66] 3))" End Begin PaneConfiguration = 6 NumPanes = 2 Configuration = "(H (4 [50] 3))" End Begin PaneConfiguration = 7 NumPanes = 1 Configuration = "(V (3))" End Begin PaneConfiguration = 8 NumPanes = 3 Configuration = "(H (1[56] 4[18] 2) )" End Begin PaneConfiguration = 9 NumPanes = 2 Configuration = "(H (1 [75] 4))" End Begin PaneConfiguration = 10 NumPanes = 2 Configuration = "(H (1[66] 2) )" End Begin PaneConfiguration = 11 NumPanes = 2 Configuration = "(H (4 [60] 2))" End Begin PaneConfiguration = 12 NumPanes = 1 Configuration = "(H (1) )" End Begin PaneConfiguration = 13 NumPanes = 1 Configuration = "(V (4))" End Begin PaneConfiguration = 14 NumPanes = 1 Configuration = "(V (2))" End ActivePaneConfig = 0

End Begin DiagramPane =

Begin Origin = Top = 0 Left = 0 End Begin Tables = Begin Table = "usercreation" Begin Extent = Top = 6 Left = 38 Bottom = 121 Right = 213 End DisplayFlags = 280 TopColumn = 0 End End End Begin SQLPane = End Begin DataPane = Begin ParameterDefaults = "" End Begin ColumnWidths = 12 Width = 284 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 Width = 1500 End End Begin CriteriaPane = Begin ColumnWidths = 11 Column = 1440 Alias = 900 Table = 1170 Output = 720 Append = 1400 NewValue = 1170 SortType = 1350 SortOrder = 1410 GroupBy = 1350 Filter = 1350 Or = 1350 Or = 1350 Or = 1350 End End End ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'viewuser'

GO IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPaneCount' , N'SCHEMA',N'dbo', N'VIEW',N'viewuser', NULL,NULL)) EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'viewuser' GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [disp]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' -- =========================================================== -- Author : Swathi.G -- Proceduer name: disp -- Description : To display the user details -- =========================================================== CREATE procedure [dbo].[disp] @userid int as begin select * from UserCreation where userid=@userid and dstatus=0 end ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [LoginDetails]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[LoginDetails]( [LoginId] [int] IDENTITY(1,1) NOT NULL, [userid] [int] NULL, [CurrentDate] [varchar](50) NULL, [LogDateTime] [varchar](50) NULL, [LogOutDateTime] [varchar](50) NULL, CONSTRAINT [PK_LoginDetails] PRIMARY KEY CLUSTERED ( [LoginId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [Assign]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[Assign]( [AssignId] [int] IDENTITY(1,1) NOT NULL, [ActivityId] [int] NULL, [Activitystatus] [varchar](50) NULL, [PercentageCompletion] [varchar](50) NULL, [Comments] [varchar](50) NULL, [ReAssignStatus] [int] NULL, [assignStartdate] [varchar](50) NULL, [AssignEnddate] [varchar](50) NULL, [AssignUserid] [int] NULL, CONSTRAINT [PK_Assign] PRIMARY KEY CLUSTERED ( [AssignId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [delnews]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'/************************************************************************** ** ProcedureName :: ChkLookupText Purpose :: To Check LookupText CreatedBy :: swapna.b ****************************************************************************/ CREATE PROCEDURE [dbo].[delnews] @titleid int as begin delete from news where titleid=@titleid end' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [Stakeholder]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[Stakeholder]( [StakeholderId] [int] IDENTITY(1,1) NOT NULL, [UserId] [int] NULL, [AssignId] [int] NULL, CONSTRAINT [PK_Stakeholder] PRIMARY KEY CLUSTERED (

[StakeholderId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [AssignTo]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[AssignTo]( [AssignToId] [int] IDENTITY(1,1) NOT NULL, [UserId] [int] NULL, [AssignId] [int] NULL, CONSTRAINT [PK_AssignTo] PRIMARY KEY CLUSTERED ( [AssignToId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [Convertdate1]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) BEGIN execute dbo.sp_executesql @statement = N'create function [dbo].[Convertdate1] (@Date varchar(30)) returns varchar(50) begin declare @date1 varchar(10) if(@Date is null or @Date = '''') begin set @date1 =@Date; end else begin set @date1 = (select convert(varchar(10), convert(datetime,@Date),103)) end return @date1; end' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [activity]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[activity](

[activityid] [int] IDENTITY(1,1) NOT NULL, [activitydescription] [varchar](500) NULL, [activitytype] [varchar](50) NULL, [duration] [varchar](10) NULL, [activitystartdate] [varchar](20) NULL, [activityenddate] [varchar](20) NULL, [activityname] [varchar](50) NULL, [Attachmentname] [varchar](100) NULL, [Attachments] [image] NULL, [createdby] [varchar](50) NULL, [dstatus] [int] NULL, [CreatedOn] [varchar](50) NOT NULL, [LastEditedBy] [varchar](50) NULL, [LastEditedOn] [varchar](50) NULL, [Recurrence] [varchar](50) NULL, CONSTRAINT [PK_activity] PRIMARY KEY CLUSTERED (

[activityid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [DocumentsUpload]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' /**************************************************************************** ProcedureName :: DisplayDocuments Purpose :: To Display Attachments in Grid CreatedBy :: vamsi.N ****************************************************************************/

CREATE proc [dbo].[DocumentsUpload] @Attachid int, @Attachname varchar(50), @Attachment image as begin if(@Attachid is null or @Attachid ='''' or @Attachid =0) begin insert into attachmenttms ( Attachname, Attachment) values(@Attachname,@Attachment) end

end' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [DisplayDocuments]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' /**************************************************************************** ProcedureName :: DisplayDocuments Purpose :: To Display Attachments in Grid CreatedBy :: swapna.b ****************************************************************************/ CREATE proc [dbo].[DisplayDocuments] as begin select * from Documnts end' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [SaveandUpdateSchedules]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'create PROCEDURE [dbo]. [SaveandUpdateSchedules] @scheduleid int, @Title varchar(50), @ScheduleDate varchar(50) as begin if(@scheduleid ='''' or @scheduleid is null) begin insert into Schedules (title , scheduledate) values(@title , @scheduledate) set @scheduleid=@@identity end else begin update schedules set title =@title, scheduledate = @scheduledate where scheduleid = @scheduleid end end '

END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [Schedules]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[Schedules]( [ScheduleId] [int] IDENTITY(1,1) NOT NULL, [Title] [varchar](50) NULL, [ScheduleDate] [datetime] NULL, [dstatus] [tinyint] NULL ) ON [PRIMARY] END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [insertdoc]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'

/**************************************************************************** ProcedureName :: insertdoc Purpose :: To Insert Attachments into Table CreatedBy :: vamsi.N ****************************************************************************/ CREATE proc [dbo].[insertdoc] @Attachname varchar(50), @Attachment image, @CreatedOn varchar(20), @CreatedBy varchar(20) as begin insert into Documents ( AttachmentName, Attachment,CreatedOn,CreatedBy) values(@Attachname,@Attachment,convert(varchar(10),co nvert(datetime,@CreatedOn),103),@CreatedBy) end

' END

GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [selectdoc]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'

/**************************************************************************** ProcedureName :: selectdoc Purpose :: To Select Data from Documents Table CreatedBy :: vamsi.N ****************************************************************************/ CREATE Proc [dbo].[selectdoc] as begin select * from Documents where Dstatus=0 order by AttachmentId desc end ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [DelDocument]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'

/************************************************************************** Stored Procedure :: DelDocument Purpose :: To Update Deleted Record in the grid CreatedBy :: Vamsi **************************************************************************/

CREATE PROCEDURE [dbo].[DelDocument] @AttachmentId int AS begin update Documents set Dstatus=1 where AttachmentId=@AttachmentId

end

RETURN

' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [deletelookup]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE proc [dbo].[deletelookup] @lookuptext varchar(50), @lookupname varchar(50) as begin if(@lookupname = ''Designation'') begin delete lookup where lookuptext = ''@lookuptext'' and lookupname = ''Designation'' and lookuptext not in ( select distinct Designation from usercreation ) end if(@lookupname = ''location'') begin delete lookup where lookuptext = @lookuptext and lookupname = ''location'' and lookuptext not in ( select distinct location from usercreation ) end if(@lookupname = ''username'') begin delete lookup where lookuptext = @lookuptext and lookupname = ''username'' and lookuptext not in ( select distinct username from usercreation ) end if(@lookupname = ''activitytype'') begin delete lookup where lookuptext = @lookuptext and lookupname = ''activitytype'' and lookuptext not in ( select distinct activitytype from activity

end

)

end ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [SaveUpdateLookUp]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' /************************************************************************** Stored Procedure :: SaveUpdateLookUp Purpose :: Save and Update LookUpTable CreatedBy :: swapna.b **************************************************************************/ CREATE PROCEDURE [dbo].[SaveUpdateLookUp] ( @Lookupid int, @Lookupname varchar(50), @Lookuptext varchar(50), @Description varchar(50), @Who varchar(50) ) as begin if(@LookUpId is null or @LookUpId='''') begin insert into lookup(lookupname,lookuptext,lookupdescription,createdby,createdon) values(@Lookupname,@Lookuptext,@Description,@Who,getdate()) set @Lookupid=@@identity end else begin update LookUp set lookupname= @Lookupname,

lookuptext = @LookUpText, lookupdescription = @Description, lasteditby = @Who, lastediton=getdate() where lookupid=@LookUpId end end /**************************** End Of SaveUpdateLookUp ***************************************/

' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [GetLookup]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' ------

=========================================================== Author : Swapna.B Proceduer name: GetLookUp Description : To get the lookup text in to the drop down. ===========================================================

CREATE procedure [dbo].[GetLookup] @lookupname varchar(50) as begin select lookuptext,lookupid from LookUp where Lookupname=@lookupname and DStatus=0 end ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [GetAllLookUp]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'/************************************************************************** ** ProcedureName :: GetAllLookUp Purpose :: To Select LookUp Table

CreatedBy :: Swapna.B ****************************************************************************/ CREATE PROCEDURE [dbo].[GetAllLookUp] as begin select * from LookUp where dstatus=0 order by lookupid DESC end ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [getlookupname]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'/************************************************************************** ** ProcedureName :: getlookupname Purpose :: To get lookup names CreatedBy :: swapna.b ****************************************************************************/ CREATE PROCEDURE [dbo].[getlookupname] (@lookupname varchar(50)) AS select * from lookup where lookupname=@lookupname and DStatus=0 RETURN ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [Update_notice]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'/************************************************************************** ****** to update records in the database and datagrid ***************************************************************************** ******************/ create PROCEDURE [dbo].[Update_notice] @NoticeId int , @NoticeDate varchar(50), @Subject varchar(50), @Message text, @PostedBy varchar(50), @PostedSignature varchar(200), @PostedUser varchar(50), @who varchar(50) AS

IF @NoticeId IS NULL OR @NoticeId = 0 BEGIN INSERT INTO coromandelNews ( [NoticeDate], [Subject], [Message], [Status], [SelectStatus], [PostedBy], [PostedSignature], [PostedUser], [CreatedBy], [CreatedOn] ) VALUES( @NoticeDate, @Subject, @Message, 0, 0, @PostedBy, @PostedSignature, @PostedUser, @who, getdate() ) SET @NoticeId = @@IDENTITY END ELSE BEGIN

UPDATE coromandelNews SET [NoticeDate]=@NoticeDate, [Subject]=@Subject, [Message]=@Message, [PostedBy]=@PostedBy, [PostedSignature]=@PostedSignature, [PostedUser]=@PostedUser, [LastEditBy]=@who, [LastEditOn]=getdate() WHERE [NoticeId] = @NoticeId and Status=0

END'

END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [delete_notice]') AND type in (N'P', N'PC'))

BEGIN EXEC dbo.sp_executesql @statement = N' -***************************************************************************** ********* --*** To delete the records from DataGrid and Set status = 1 in DataBase*** -***************************************************************************** ********* CREATE procedure [dbo].[delete_notice] @NoticeId int output as begin update coromandelNews set status = 1 where NoticeId = @NoticeId end ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [Notice_SelectDeselect]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE procedure [dbo].[Notice_SelectDeselect] @NoticeId int , @checkstatus varchar(20) as begin if(@checkstatus = ''true'') update coromandelNews set SelectStatus = 1 where NoticeId = @NoticeId and Status=0 else update coromandelNews set SelectStatus = 0 where NoticeId = @NoticeId and Status=0 end ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [check_notice]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE proc [dbo].[check_notice] @NoticeId int as begin select SelectStatus from coromandelNews where NoticeId = @NoticeId end

' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [ViewNotice_Message]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE proc [dbo].[ViewNotice_Message] @NoticeId int as begin select Message,postedsignature from coromandelNews where NoticeId = @NoticeId end ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [Get_all_notice]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'/************************************************************************** ********************* Stored Procedure to retreive all the records from the database ***************************************************************************** *****************/ create procedure [dbo].[Get_all_notice] as begin select * FROM coromandelNews where status = 0 order by NoticeId desc end ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [getcompletedactivity]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'

/**************************************************************************

Stored Procedure :: getcompletedactivity Purpose :: getting records all completed activity CreatedBy :: Anil.R **************************************************************************/ CREATE proc [dbo].[getcompletedactivity] @assignuser varchar(50) as begin select a.AssignId,b.activityname,b.activityid,b.activitydescription,b.duration,b.Rec urrence, b.activitystartdate,b.activityenddate,b.activitytype, b.createdby,convert(varchar(10),convert(datetime,a.assignStartdate),103) assignStartdate,convert(varchar(10),convert(datetime,a.AssignEnddate),103) AssignEnddate,a.Comments ,dbo.GetUserName(c.UserId) Username from assign a,activity b , assignto c , usercreation u where (a.PercentageCompletion =''100%'' or a.PercentageCompletion =''yes'') and a.activityid=b.activityid and a.assignid= c.assignid and c.userid = u.userid and u.fullname = @assignuser and b.dstatus=0 end

' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [displaydeletedusers]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE proc [dbo].[displaydeletedusers] as begin select [dbo].[GetUserName](reportingto) reportingto1,* from usercreation where dstatus=1 end ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [GetActivityDetails]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'

/**************************************************************************** ProcedureName :: GetActivityDetails Purpose :: To Get the ActivityDetails Recored CreatedBy :: Anil.R ***************************************************************************/ CREATE proc [dbo].[GetActivityDetails] --''swapna b'' @createdby varchar(50) as begin select a.activityid,a.activitydescription,a.activitytype,a.duration, dbo.Convertdate(activitystartdate) activitystartdate,dbo.Convertdate(activityenddate) activityenddate,a.activityname,a.Attachmentname, a.Attachments,a.createdby,a.dstatus,a.CreatedOn,a.LastEditedBy,a.LastEditedOn ,a.Recurrence, dbo.GetUserName(c.UserId) username from activity a,Assign b,AssignTo c where a.activityid = b.ActivityId and b.AssignId = c.AssignId and dstatus=0 and createdby=@createdby union all select a.activityid,a.activitydescription,a.activitytype,a.duration, dbo.Convertdate(activitystartdate) activitystartdate,dbo.Convertdate(activityenddate) activityenddate,a.activityname,a.Attachmentname, a.Attachments,a.createdby,a.dstatus,a.CreatedOn,a.LastEditedBy,a.LastEditedOn ,a.Recurrence,'''' username from activity a where a.createdby=@createdby and dstatus=0 order by activityid desc end

' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo]. [MDPendingTask]')) EXEC dbo.sp_executesql @statement = N'CREATE view [dbo].[MDPendingTask] as select a.activityid as activityid , u1.username as assigneduser , a.createdby as createdby , a.activityname as activityname , b.activitystatus as acitivitystatus ,a.activityenddate as duedate, b.percentagecompletion as percentagecompletion from activity a , assign b , assignto c , usercreation u1 , usercreation u2 where a.activityid = b.activityid and b.reassignstatus=0 and b.assignid = c.assignid and

b.activitystatus=''OPEN'' and a.createdby = u2.fullname and c.userid = u1.userid and u2.designation=''Managing Director''' GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo]. [HODPendingTask]')) EXEC dbo.sp_executesql @statement = N' CREATE view [dbo].[HODPendingTask] as select a.activityid as activityid , u1.username as assigneduser , a.createdby as createdby , a.activityname as activityname , b.activitystatus as acitivitystatus , a.activityenddate as duedate ,b.percentagecompletion as percentagecompletion from activity a , assign b , assignto c , usercreation u1 , usercreation u2 where a.activityid = b.activityid and b.reassignstatus=0 and b.assignid = c.assignid and a.dstatus = 0 and (b.activitystatus=''OPEN'' or b.activitystatus = ''NEW'') and a.createdby = u2.fullname and c.userid = u1.userid and u2.designation=''HOD'' ' GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo]. [HODCompletedTask]')) EXEC dbo.sp_executesql @statement = N'CREATE view [dbo].[HODCompletedTask] as select a.activityid as activityid , u1.username as assigneduser , a.createdby as createdby , a.activityname as activityname , b.activitystatus as acitivitystatus ,a.activityenddate as duedate ,b.percentagecompletion as percentagecompletion from activity a , assign b , assignto c , usercreation u1 , usercreation u2 where a.activityid = b.activityid and b.reassignstatus=0 and b.assignid = c.assignid and a.dstatus = 0 and b.activitystatus=''Completed'' and a.createdby = u2.fullname and c.userid = u1.userid and u2.designation=''HOD''' GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [GetAllUserNames]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'/************************************************************************** ** ProcedureName :: GetAllUserNames Purpose :: To get all usernames to DDL of reportLogindetails CreatedBy :: swapna.b

****************************************************************************/ CREATE PROCEDURE [dbo].[GetAllUserNames] AS select username from usercreation where dstatus=0 RETURN ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo]. [MDDelCompleteTask]')) EXEC dbo.sp_executesql @statement = N' CREATE view [dbo].[MDDelCompleteTask] as select a.activityid as activityid , u1.username as assigneduser , a.createdby as createdby , a.activityname as activityname , b.activitystatus as acitivitystatus ,a.activityenddate as duedate,b.percentagecompletion as percentagecompletion from activity a , assign b , assignto c , usercreation u1 , usercreation u2 where a.activityid = b.activityid and b.reassignstatus=0 and b.assignid = c.assignid and a.dstatus= 1 and b.activitystatus=''Completed'' and a.createdby = u2.fullname and c.userid = u1.userid and u2.designation=''Managing Director'' ' GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [ReportLoginDetails]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE Proc [dbo].[ReportLoginDetails] @username varchar(50), @curdate varchar(50), @frdate varchar(50) AS Begin --declare @curdate varchar(50) -- set @curdate = (select convert(varchar(10),convert(datetime,CurrentDate),103) from LoginDetails where CurrentDate=@curtdate ) ---declare @frdate varchar(50) -- set @frdate =(select convert(varchar(10),convert(datetime,CurrentDate),103) from LoginDetails where CurrentDate=@frmdate) declare @uname int set @uname= (select userid from usercreation

where username=@username and dstatus=0) --1.When Selecting Only User if(@username!='''' and @curdate ='''' and @frdate ='''') Begin select l.*,u.username from LoginDetails l,usercreation u where l.userid=@uname and l.userid=u.userid order by l.loginid

desc RETURN End

--2.When Selecting User and Particular date else if(@username!='''' and @curdate !='''' and @frdate ='''') Begin select l.*,u.username from LoginDetails l,usercreation u where l.userid=@uname and l.CurrentDate=@curdate and l.userid=u.userid order by l.loginid desc End --3.When Selecting Particular date else if(@username='''' and @curdate !='''' and @frdate ='''') Begin select l.*,u.username from LoginDetails l,usercreation u where l.CurrentDate=@curdate and l.userid=u.userid order by l.loginid desc End --4.When Selecting User and Between Dates else if(@username!='''' and @curdate !='''' and @frdate !='''') Begin select l.*,u.username from LoginDetails l,usercreation u where l.userid=@uname and l.CurrentDate between @curdate and and l.userid=u.userid order by l.loginid desc

@frdate end

--5.When Selecting only between dates

else if(@username='''' and @curdate !='''' and @frdate !='''') Begin select l.*,u.username from LoginDetails l,usercreation u where l.CurrentDate between @curdate and @frdate and l.userid=u.userid order by l.loginid desc End

End

' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo]. [MDCompletedTask]')) EXEC dbo.sp_executesql @statement = N' CREATE view [dbo].[MDCompletedTask] as select a.activityid as activityid , u1.username as assigneduser , a.createdby as createdby , a.activityname as activityname , b.activitystatus as acitivitystatus ,a.activityenddate as duedate,b.percentagecompletion as percentagecompletion from activity a , assign b , assignto c , usercreation u1 , usercreation u2 where a.activityid = b.activityid and b.reassignstatus=0 and b.assignid = c.assignid and a.dstatus= 0 and b.activitystatus=''Completed'' and a.createdby = u2.fullname and c.userid = u1.userid and u2.designation=''Managing Director'' ' GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [chkdesignation1]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'

CREATE procedure [dbo].[chkdesignation1] @userid int, @designation varchar(50) as begin select * from usercreation where designation=@designation and userid<>@userid end

' END GO SET ANSI_NULLS ON

GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [getallpendactivity]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' -- =========================================================== -- Author : Kamala.m -- Proceduer name: getallpendactivity -- Description : To display the pending activity details -- =========================================================== CREATE PROCEDURE [dbo].[getallpendactivity]--''vasu krishna'' @assignuser varchar(50) AS begin select ac.activityid,ass.AssignId,ac.Recurrence,ac.activityname,ac.activitytype,ass. Comments, ac.activitydescription,ac.duration,ac.activitystartdate,ac.activityenddate,ac .activityname,ac.Attachmentname,ac.Attachments,ac.createdby,ass.assignStartda te,ass.AssignEnddate,ac.createdby, convert(varchar(10),convert(datetime,ass.assignstartdate),103) assignstartdate,convert(varchar(10),convert(datetime,ass.assignenddate),103) assignenddate, ass.PercentageCompletion from activity ac,assign ass , assignto c , usercreation u where ac.activityid=ass.activityid and ass.activitystatus=''OPEN'' and ass.assignid = c.assignid and ac.dstatus=0 and u.dstatus=0 and c.userid = u.userid and u.fullname=@assignuser and ass.ReAssignStatus =0 order by assignid desc end ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [CheckUserAvailability]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' create proc [dbo]. [CheckUserAvailability] @username varchar(200) as begin select count(*) from usercreation where username=@username and dstatus=0 end' END GO

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [PermenentUserDelete]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'create proc [dbo].[PermenentUserDelete] @userid int as begin delete usercreation where userid=@userid end' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo]. [HODDelCompleteTask]')) EXEC dbo.sp_executesql @statement = N' CREATE view [dbo].[HODDelCompleteTask] as select a.activityid as activityid , u1.username as assigneduser , a.createdby as createdby , a.activityname as activityname , b.activitystatus as acitivitystatus ,a.activityenddate as duedate ,b.percentagecompletion as percentagecompletion from activity a , assign b , assignto c , usercreation u1 , usercreation u2 where a.activityid = b.activityid and b.reassignstatus=0 and b.assignid = c.assignid and a.dstatus = 1 and b.activitystatus=''Completed'' and a.createdby = u2.fullname and c.userid = u1.userid and u2.designation=''HOD''' GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [SaveLoginDetails]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' -- =========================================================== -- Author : Swapna.B -- Proceduer name: SaveLoginDetails -- Description : To Save the login details into LoginDetails table -- =========================================================== CREATE PROCEDURE [dbo].[SaveLoginDetails] @username varchar(50) AS begin declare @uname varchar(50) set @uname=(select userid from usercreation where username=@username)

insert into LoginDetails(UserId,CurrentDate,LogDateTime) values(@uname,convert(varchar(10),getdate(),101), (convert(varchar(20),getdate(),106)+'' ''+ convert(varchar(20),getdate(),108))) select @@identity as newRecord end

' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo]. [MDCompletedActivity]')) EXEC dbo.sp_executesql @statement = N'CREATE view [dbo].[MDCompletedActivity] as select u1.userid as userid, b.activityid as asactivityid, b.assignid as assignid, u1.fullname as assigneduser , u1.designation as designation, u1.reportingto as reportingto, u2.fullname as reportingname , a.activityid as activityid, a.activityname as activityname , a.activitytype as activitytype, a.createdby as createdby , a.activityenddate as duedate,b.percentagecompletion as percentagecompletion from activity a, assign b, usercreation u , assignto c , usercreation u1, usercreation u2 where a.activityid = b.activityid and a.createdby = u.fullname and b.assignid = c.assignid and c.userid = u1.userid and b.reassignstatus =0 and b.activitystatus=''Completed'' and u1.reportingto = u2.userid and u2.designation=''Managing Director''' GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo]. [HODCompletedActivity]')) EXEC dbo.sp_executesql @statement = N' CREATE view [dbo].[HODCompletedActivity] as select u1.userid as userid, b.activityid as asactivityid, b.assignid as assignid, u1.username as assigneduser , u1.designation as designation, u1.reportingto as reportingto, u2.fullname as reportingname , a.activityid as activityid, a.activityname as activityname , a.createdby as createdby , a.activityenddate as duedate,b.percentagecompletion as percentagecompletion from activity a, assign b, usercreation u , assignto c , usercreation u1, usercreation u2 where a.activityid = b.activityid and a.createdby = u.fullname b.assignid = c.assignid and

and

c.userid = u1.userid and b.reassignstatus =0 and b.activitystatus=''Completed'' and u1.reportingto = u2.userid and u2.designation=''HOD'' ' GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo]. [delegatedactivity]')) EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[delegatedactivity] AS SELECT b.activityid as asactivityid, b.assignid as assignid, u1.username as assigneduser , a.activityid, a.activityname, a.createdby AS delegatedby, a.activityenddate AS duedate, b.PercentageCompletion FROM dbo.activity AS a INNER JOIN dbo.Assign AS b ON a.activityid = b.ActivityId INNER JOIN dbo.usercreation AS u ON a.createdby = u.fullname INNER JOIN dbo.AssignTo AS c ON b.AssignId = c.AssignId INNER JOIN dbo.usercreation AS u1 ON c.UserId = u1.userid WHERE (b.PercentageCompletion <> ''100%'') AND (b.ReAssignStatus = 0) AND (b.Activitystatus = ''OPEN'') and (a.dstatus =0)' GO IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPane1' , N'SCHEMA',N'dbo', N'VIEW',N'delegatedactivity', NULL,NULL)) EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0B466-11cf-A24F-00AA00A3EFFF, 1.00] Begin DesignProperties = Begin PaneConfigurations = Begin PaneConfiguration = 0 NumPanes = 4 Configuration = "(H (1[40] 4[20] 2[20] 3) )" End Begin PaneConfiguration = 1 NumPanes = 3 Configuration = "(H (1 [50] 4 [25] 3))" End Begin PaneConfiguration = 2 NumPanes = 3 Configuration = "(H (1 [50] 2 [25] 3))" End Begin PaneConfiguration = 3 NumPanes = 3 Configuration = "(H (4 [30] 2 [40] 3))" End Begin PaneConfiguration = 4 NumPanes = 2 Configuration = "(H (1 [56] 3))" End Begin PaneConfiguration = 5 NumPanes = 2 Configuration = "(H (2 [66] 3))" End Begin PaneConfiguration = 6

NumPanes = 2 Configuration = "(H (4 [50] 3))" End Begin PaneConfiguration = 7 NumPanes = 1 Configuration = "(V (3))" End Begin PaneConfiguration = 8 NumPanes = 3 Configuration = "(H (1[56] 4[18] 2) )" End Begin PaneConfiguration = 9 NumPanes = 2 Configuration = "(H (1 [75] 4))" End Begin PaneConfiguration = 10 NumPanes = 2 Configuration = "(H (1[66] 2) )" End Begin PaneConfiguration = 11 NumPanes = 2 Configuration = "(H (4 [60] 2))" End Begin PaneConfiguration = 12 NumPanes = 1 Configuration = "(H (1) )" End Begin PaneConfiguration = 13 NumPanes = 1 Configuration = "(V (4))" End Begin PaneConfiguration = 14 NumPanes = 1 Configuration = "(V (2))" End ActivePaneConfig = 0 End Begin DiagramPane = Begin Origin = Top = 0 Left = 0 End Begin Tables = Begin Table = "a" Begin Extent = Top = 6 Left = 38 Bottom = 114 Right = 205 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "b" Begin Extent = Top = 6 Left = 243

Bottom = 114 Right = 431 End DisplayFlags = 280 TopColumn = 0

End Begin Table = "u" Begin Extent = Top = 114 Left = 38 Bottom = 222 Right = 212 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "c" Begin Extent = Top = 114 Left = 250 Bottom = 207 Right = 401 End DisplayFlags = 280 TopColumn = 0 End Begin Table = "u1" Begin Extent = Top = 210 Left = 250 Bottom = 318 Right = 424 End DisplayFlags = 280 TopColumn = 0 End End End Begin SQLPane = End Begin DataPane = Begin ParameterDefaults = "" End End Begin CriteriaPane = Begin ColumnWidths = 11 Column = 1440 Alias = 900 Table = 1170 Output = 720 Append = 1400 NewValue = 1170 SortType = 1350 SortOrder = 1410 GroupBy = 1350 Filter = 1350 Or = 1350

Or = 1350 Or = 1350 End

End

End ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'delegatedactivity' GO IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPaneCount' , N'SCHEMA',N'dbo', N'VIEW',N'delegatedactivity', NULL,NULL)) EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'delegatedactivity' GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo]. [delegate]')) EXEC dbo.sp_executesql @statement = N'CREATE view [dbo].[delegate] as select u1.userid as userid, b.activityid as asactivityid, b.assignid as assignid, u1.username as assigneduser , u1.designation as designation, u1.reportingto as reportingto, u2.fullname as reportingname , a.activityid as activityid, a.activityname as activityname , a.createdby as delegatedby , a.activityenddate as duedate,b.percentagecompletion as percentagecompletion from activity a, assign b, usercreation u , assignto c , usercreation u1, usercreation u2 where a.activityid = b.activityid and a.createdby = u.fullname and b.percentagecompletion!=''100%'' and b.assignid = c.assignid and a.dstatus = 0 and c.userid = u1.userid and b.reassignstatus =0 and b.activitystatus=''OPEN'' and u1.reportingto = u2.userid and u2.designation=''HOD'' and u1.designation=''Employee''' GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [GetUserName1]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) BEGIN execute dbo.sp_executesql @statement = N'create function [dbo]. [GetUserName1] (@UserId varchar(30)) returns varchar(50) begin return (select fullname from viewuser where UserId = @UserId); end ' END GO SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [GetStakeHoldernames]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' /**************************************************************************** ProcedureName :: GetStakeHoldernames Purpose :: To Get the StakeHoldernames CreatedBy :: Anil.R ***************************************************************************/ CREATE proc [dbo].[GetStakeHoldernames] @designation varchar(50) as begin if(@designation = ''Managing Director'') select * from viewuser where designation = ''Managing Director'' else select * from viewuser where designation = ''Managing Director'' or designation = ''Admin'' end ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [reportingto]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE Procedure [dbo].[reportingto] --''Managing Director'' (@desi varchar(50)) as begin if @desi =''HOD'' begin select fullname,userid from viewuser where designation=''Managing Director'' end else if @desi=''Employee'' begin select fullname,userid from viewuser where designation=''HOD'' end end ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [GetUsersNames]') AND type in (N'P', N'PC'))

BEGIN EXEC dbo.sp_executesql @statement = N' /**************************************************************************** ProcedureName :: GetUserNammes Purpose :: To Get the UserNammes CreatedBy :: Anil.R ***************************************************************************/ CREATE procedure [dbo].[GetUsersNames] @designation varchar(50) as begin -- select userid,username from usercreation where dstatus=0 if(@designation = ''HOD'') select userid, fullname from viewuser where designation = ''Employee'' and dstatus = 0 else select userid, fullname from viewuser where dstatus = 0 end

' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [logoutdetail]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'/************************************************************************** ** ProcedureName :: logoutdetails Purpose :: To display all the details of login CreatedBy :: swapna.b ****************************************************************************/ CREATE PROCEDURE [dbo].[logoutdetail] @loginid varchar(50) AS begin --declare @uname int --set @uname=(select userid from usercreation where username=@username) --set @Loginid=(select LoginId from LoginDetails where userid = @uname) begin update LoginDetails set LogoutDateTime=(convert(varchar(20),getdate(),106)+'' ''+ convert(varchar(20),getdate(),108)) where LoginId=@loginid

end end ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [UpdateAssignedActivityStatus]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'/*****************************************************/ --to update pending activities details -- By ram /*****************************************************/ CREATE proc [dbo].[UpdateAssignedActivityStatus] @userid int, @assignid int, @percentage varchar(10), @comments varchar(max) as begin if(@percentage=''100%'') begin update assign set activitystatus=''Closed'',percentagecompletion=@percentage,comments=@comments where assignid=@assignid end else if (@percentage!=''0%'') begin update assign set activitystatus=''Open'',percentagecompletion=@percentage,comments=@comments where assignid=@assignid end end' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [NonAssignedActivity]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'create proc [dbo].[NonAssignedActivity] @username varchar(50) as begin SELECT A.activityid,A.activityname,A.createdby,convert(varchar(10),convert(datetime,

A.CreatedOn),103) CreatedOn,B.AssignId FROM activity A left JOIN assign B ON A.activityid = B.activityid where createdby = @username and B.activityid is null order by activityid asc end' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [SaveAssign]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'

/**************************************************************************** ProcedureName :: SaveAssign Purpose :: To Save the Assign Record CreatedBy :: Anil.R ***************************************************************************/ CREATE proc [dbo].[SaveAssign] @Assignid int, @ActivityId int, @Activitystatus varchar(50), @PercentageCompletion varchar(50), @Comments varchar(50), @ReAssignStatus int, @assignStartdate varchar(50), @AssignEnddate varchar(50), @AssignUserid int as begin if(@Assignid is null or @Assignid = '''' or @Assignid = 0) begin INSERT INTO Assign (ActivityId ,Activitystatus ,PercentageCompletion ,Comments ,ReAssignStatus ,assignStartdate ,AssignEnddate ,AssignUserid) VALUES (@ActivityId , @Activitystatus , @PercentageCompletion , @Comments, @ReAssignStatus , @assignStartdate , @AssignEnddate, @AssignUserid) select @@identity as newrecord end

else

begin UPDATE

Assign SET Activitystatus = @Activitystatus,

assignStartdate = convert(varchar(10),getdate(),101) WHERE Assignid=@Assignid end end

' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [GetPendingAssignments]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' /**************************************************************************** ProcedureName :: GetPendingAssignments Purpose :: To Get the Pending Assignments CreatedBy :: Anil.R ***************************************************************************/ CREATE proc [dbo].[GetPendingAssignments] as begin SELECT A.activityid,A.activityname,A.createdby,convert(varchar(10),convert(datetime, A.CreatedOn),103) CreatedOn,B.AssignId FROM activity A left JOIN assign B ON A.activityid = B.activityid where B.activityid is null order by A.activityid asc end

' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [ViewPendingActivity]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-=========================================================== -- Author : Kamala.m -- Proceduer name: ViewPendingActivity -- Description : To view pending activity details

-- =========================================================== CREATE procedure [dbo].[ViewPendingActivity] @activityid int as begin select ac.activitydescription,ac.activitytype,ac.duration,ac.activityname,ac.Attachm entname,ac.Attachments,ac.createdby,ac.Recurrence, ass.percentagecompletion,ac.activitystartdate,ac.activityenddate,ass.Comments from activity ac, assign ass where ass.Assignid=@activityid and ac.activityid=ass.activityid end ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [retrievecomments]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-============================================= -- Author: M.Kamala -- Procedure Name: retrievecomments -- Description: To get comments and percentage -- ============================================= create procedure [dbo].[retrievecomments] @assignid int as begin select Comments,PercentageCompletion from assign where AssignId=@assignid end' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [stakeholdername]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'create proc [dbo].[stakeholdername] as begin select a.createdby as stakeholdername from activity a , assign b , usercreation u , stakeholder c , usercreation u1 where end'

a.activityid = b.activityid and a.createdby = u.fullname and c.assignid = b.assignid and c.userid = u1.userid

END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [GetNewactivity]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' /**************************************************************************** ProcedureName :: GetNewactivity Purpose :: To Get the NewActivity Record CreatedBy :: Anil.R ***************************************************************************/ CREATE proc [dbo].[GetNewactivity] @assignuser varchar(50) as begin select b.AssignId, a.activityid,a.activityname,a.activitytype,a.duration,a.createdby,a.Recurrenc e ,b.activitystatus,a.activitystartdate,a.activityenddate,a.Attachmentname,a.ac tivitydescription,a.Recurrence from activity a, assign b,assignto c , usercreation u where a.activityid = b.activityid and b.assignid = c.assignid and u.userid = c.userid and b.activitystatus=''NEW'' and u.fullname=@assignuser order by a.activityid desc end ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [UpadatePendingactivity]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-=========================================================== -- Author : Kamala.m -- Proceduer name: UpdatePendingactivity -- Description : To update pending activity details -- =========================================================== CREATE procedure [dbo].[UpadatePendingactivity] @assignid int, @comments varchar(50), @percentage varchar(50) as begin if(@percentage =''Yes'' or @percentage = ''100%'')

BEGIN

end

update assign set Comments=@comments, PercentageCompletion=@percentage, Activitystatus=''Completed'', AssignEnddate=convert(varchar(10),getdate(),101) where AssignId=@assignid and Activitystatus=''OPEN''

else if(@percentage <> ''100%'' or @percentage =''No'') begin

update assign set Comments=@comments, PercentageCompletion=@percentage, AssignEnddate=convert(varchar(10),getdate(),101) where AssignId=@assignid and Activitystatus=''OPEN''

END end

' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [updateReassignstatus]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE proc [dbo].[updateReassignstatus] @assignid int as begin update assign = @assignid end ' END GO SET ANSI_NULLS ON

set reassignstatus = 1 where reassignstatus =0 and assignid

GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [saveStakeHolder]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' /**************************************************************************** ProcedureName :: saveStakeHolder Purpose :: To Save the StakeHolder Record CreatedBy :: Anil.R ***************************************************************************/ CREATE proc [dbo].[saveStakeHolder] @UserId int, @ActivityId int as begin INSERT INTO Stakeholder(UserId,AssignId)VALUES(@UserId,@ActivityId ) end ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [saveAssignTo]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' /**************************************************************************** ProcedureName :: saveAssignTo Purpose :: To Save the AssignTo Record CreatedBy :: Anil.R ***************************************************************************/ CREATE proc [dbo].[saveAssignTo] @UserId int, @ActivityId int as begin INSERT INTO AssignTo(UserId,AssignId)VALUES(@UserId,@ActivityId ) end ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [Saveactivity]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'

/**************************************************************************** ProcedureName :: SaveActivity Purpose :: To Save the Activity Record CreatedBy :: Anil.R ***************************************************************************/ CREATE proc [dbo].[Saveactivity] @activityid int, @activitydescription varchar(50), @activitytype varchar(50), @duration varchar(10), @activitystartdate varchar(20), @activityenddate varchar(20), @activityname varchar(50), @Attachmentname varchar(100), @Attachments image, @createdby varchar(50), @Recurrence varchar(50) as begin if(@activityid is null or @activityid ='''' or @activityid =0) begin INSERT INTO activity (activitydescription, activitytype, duration, activitystartdate, activityenddate, activityname, Attachmentname, Attachments, createdby, dstatus, CreatedOn, Recurrence) VALUES (@activitydescription, @activitytype , @duration, @activitystartdate, @activityenddate, @activityname, @Attachmentname, @Attachments, @createdby, 0, convert(varchar(10),getdate(),101), @Recurrence) select @@identity as newrecord end else begin UPDATE activity SET activitydescription = @activitydescription, activitytype = @activitytype, duration = @duration, activitystartdate = @activitystartdate,

activityenddate = @activityenddate, activityname = @activityname, Attachmentname=@Attachmentname, Attachments=@Attachments, LastEditedBy=@createdby, LastEditedOn=convert(varchar(10),getdate(),101), Recurrence=@Recurrence WHERE activityid=@activityid end end

' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [getAttachActivityid]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'/************************************************************************** ** ProcedureName :: getAttachActivityid Purpose :: To Select Data from Activity Table with Attachid CreatedBy :: anil.r ****************************************************************************/ CREATE proc [dbo].[getAttachActivityid] @activityid int as begin select * from activity where activityid=@activityid end ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [GetPendingRecord]') AND type in (N'P', N'PC')) BEGIN

EXEC dbo.sp_executesql @statement = N' /**************************************************************************** ProcedureName :: GetPendingRecord Purpose :: To Get the Pending Recored CreatedBy :: Anil.R ***************************************************************************/ CREATE proc [dbo].[GetPendingRecord] @Activityid int as begin select * from activity where activityid=@Activityid end ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [GetPendingDelegatedRecord]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'/************************************************************************** ** ProcedureName :: GetPendingDelegatedRecord Purpose :: To Get the delegated Recored CreatedBy :: Anil.R ***************************************************************************/ CREATE proc [dbo].[GetPendingDelegatedRecord] @Activityname varchar(50) as begin select * from activity where activityname=@Activityname end ' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [DelDeletedTask]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' CREATE proc [dbo].[DelDeletedTask] @activityid int as begin delete from activity where activityid=@activityid and dstatus =1 end ' END GO SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [DelCompleteTask]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'create proc [dbo].[DelCompleteTask] @activityid int as begin update activity set dstatus = 1 where dstatus =0 and activityid = @activityid end' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [DelScheduleEvents]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'create proc [dbo].[DelScheduleEvents] @scheduleid int as begin delete from Schedules where scheduleid = @scheduleid end' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [scheduleEvents]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' create proc [dbo].[scheduleEvents] as begin select * from schedules end' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [displayviewusers]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N' --****************************************************************** -CreatedBy: M.Kamala -Procedure Name:View Users

-Description: To View users temporarily from Database --******************************************************************** CREATE procedure [dbo].[displayviewusers] @userid int as begin select dbo.GetUserName1(reportingto) reportingto1,* from usercreation where userid=@userid and dstatus=0 end

' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [displayusercreation]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE procedure [dbo]. [displayusercreation] as begin --set reportingto=(select userid from usercreation where userid=@userid) select dbo.GetUserName1(reportingto) reportingto1,* from usercreation where dstatus=0 order by userid desc end' END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [pendingactivitystackholder]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'-=========================================================== -- Author : Kamala.m -- Proceduer name: pendingactivitystackholder -- Description : To display the pending activity stakeholders -- =========================================================== CREATE proc [dbo].[pendingactivitystackholder] @activityid int as begin select dbo.GetUserName1(userid) username from activity a,Assign b,Stakeholder c where a.activityid=b.activityid and b.assignid=c.assignid and a.activityid=@activityid end '

END GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]. [Completedelview]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE procedure [dbo].[Completedelview] @userid int as begin select dbo.GetUserName1(reportingto) reportingto1,* from usercreation where userid=@userid and dstatus=1 end' END