Useful Code for Timesheet Reminder

USE [MTSC]
GO
/****** Object:  StoredProcedure [dbo].[TimesheetHoursDailyReminder]    Script Date: 11/03/2010 13:28:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER Procedure [dbo].[TimesheetHoursDailyReminder]

AS

/********************************************************************************
Stored Procedure Name:    [dbo].[TimesheetHoursDailyReminder]
Date Written:             10/06/09           
Author:                    Chitanya Chitturi
Project/System:            MTSC               
Purpose/Description:    Time Sheet Reminder
Input Parameters:       
Tables:                 None
Procedures Called By:   NONE    
Procedures Called:      NONE    
Return Status:          NONE                 
Error Codes:            NONE                
           
CHANGE HISTORY:               
Date Modified   Modified By   Description of Modification
-------------   -----------   ----------------------------------------
********************************************************************************/
declare

@startdate char(25),
@enddate varchar(25),
@startdate_current char(25),
@enddate_current varchar(25),
@WeekdaysLastMonth int,
@WeekdaysThisMonth int,
@totalhourslastmonth int,
@totalhoursThismonth int,
@From varchar(100),
@LastDate datetime,
@Body varchar(200),
@Subject varchar(200),
@HoursPossiblePartial int,
@PartialHours int,
@UserID int,
@Hours int,
@Email varchar (100),
@JoinDate datetime

/* Set the email field variables */
SET @From = 'Timesheet@la.gov'
set @Subject = 'Daily Timesheet Reminder'

/* Calculate the last and the first days of the current Month */
set @enddate_current = Convert(varchar(12),dateadd(day,-1,getdate()), 101)
set @startdate_current = convert(varchar(2),datepart(month,@enddate_current)) + '/1/'+ convert(varchar(4),datepart(year,@enddate_current))

/* Calculate the last and the first days of the Last Month */
set @enddate = convert(varchar(12),dateadd(day,-1,@startdate_current), 101)
set @startdate = convert(varchar(2),datepart(month,@enddate)) + '/1/'+ convert(varchar(4),datepart(year,@enddate))

/* Calculate the number of hours per month */
select @WeekdaysLastMonth = dbo.GetBusinessDayCount(@startdate,@enddate)
select @WeekdaysThisMonth = dbo.GetBusinessDayCount(@startdate_current,@enddate_current)

/* Total Hours Worked */
set @totalhourslastmonth=@WeekdaysLastMonth*8
set @totalhoursThismonth=@WeekdaysThisMonth*8

/* Cursor for the Past month*/
Declare
    DataLastMonth cursor
For
    Select   
        U.UserID,
        sum(isnull(DL.Hours,0)) total_hrs,
        U.WorkEmail,
        VPE.BeginDate,
        VPE.Enddate
    From
        dbo.viewPositionsAndEmployees VPE
        inner join
        dbo.tblUser U
        on VPE.UserID = U.UserID
        Left join
        (select * from dbo.tblDailyLog where DateWorked between @startdate AND @enddate AND Deleted = 0)DL
        On U.UserID=DL.UserID
    where
        U.Deleted = 0
        AND VPE.EmploymentType = 'Full-Time'
        AND VPE.UserID <> 0
        AND VPE.UserID <> 65
        AND EmployeeID IS NOT NULL
        AND U.WorkEmail <> ''
    group By
        U.UserID,
        U.workEmail,   
        VPE.BeginDate,
        VPE.enddate

    Open DataLastMonth

    --perform the first fetch
    fetch next from DataLastMonth into @UserID, @Hours, @Email, @JoinDate, @lastDate

    --Check @@fetch_status to see if there are any more rows to fetch
    while @@fetch_status = 0
    begin /* Calculates the number of Hours from the join date of the month*/
        /* Cursor for the Past month*/
        if @lastdate > getdate()
        begin
            if(@joindate BETWEEN @startdate AND @enddate)
            begin
                select
                    @PartialHours = sum(hours)
                from
                    dbo.tblDailyLog
                where
                    DateWorked between @JoinDate AND @enddate AND Deleted = 0
                    AND userID = @userID

                set @HoursPossiblepartial =  dbo.GetBusinessDayCount(@joindate, @Enddate) * 8

                if (@PartialHours< @HoursPossiblePartial)
                Begin
                    Set @Body = 'Your hours for ' + datename(month,@startdate)+ ' are short. Please update your hours in Timesheet.'
                    exec msdb.dbo.DBADS_SP_SENDMAIL
                        @From, @Email, NULL, @Subject, @Body, NULL
                end
            end
            Else If(@JoinDate < @startdate) /* Calculates the number of hours for the last month*/
            begin
                if(@hours< @totalhourslastmonth)
                Begin
                    SET @Body = 'Your hours for ' + datename(month,@startdate)+ ' are short. Please update your hours in Timesheet' + char(13) + CHAR(13) +
                                'http://mvaweb/MTSC/TimesheetReviewLog.aspx?SessionID=5682 '
                    exec msdb.dbo.DBADS_SP_SENDMAIL
                        @From, @Email, NULL, @Subject, @Body, NULL
                End   
            end
        end
    fetch next from DataLastMonth into @UserID, @Hours, @Email, @JoinDate, @lastDate
    end
close DataLastMonth
Deallocate DataLastMonth

/*Cursors for the Current Month*/

Declare
    DataThisMonth cursor
For
    Select   
        U.UserID,
        sum(isnull(DL.Hours,0)) total_hrs,
        U.WorkEmail,
        VPE.BeginDate,
        VPE.enddate
    From
        dbo.viewPositionsAndEmployees VPE
        inner join
        dbo.tblUser U
        on VPE.UserID = U.UserID
        Left join
        (select * from dbo.tblDailyLog where DateWorked between @startdate_current and @enddate_current AND Deleted = 0)DL
        On U.UserID=DL.UserID
    where
        U.Deleted = 0
        AND VPE.EmploymentType = 'Full-Time'
        AND VPE.UserID <> 0
        AND VPE.UserID <> 65
        AND EmployeeID IS NOT NULL
        AND U.WorkEmail <> ''
    group By
        U.UserID,
        U.workEmail,
        VPE.BeginDate,
        VPE.enddate

    Open DataThisMonth

    --perform the first fetch
    fetch next from DataThisMonth into @UserID, @Hours, @Email, @JoinDate, @lastDate

    --Check @@fetch_status to see if there are any more rows to fetch
    while @@fetch_status = 0
    begin /* Calculates the number of Hours from the join date of the current month*/
        if @lastdate > getdate()
        begin
            if(@joindate BETWEEN @startdate_current AND @enddate_current)
            begin
                select
                    @PartialHours = sum(hours)
                from
                    dbo.tblDailyLog
                where
                    DateWorked between @JoinDate AND @enddate_current AND Deleted = 0
                    AND userID = @userID

                set @HoursPossiblepartial = dbo.GetBusinessDayCount(@JoinDate, @enddate_current) * 8

                if (@PartialHours < @HoursPossiblePartial)
                begin
                    Set @Body = 'Your hours for ' + datename(month,@startdate_current)+ ' are short. Please update your hours in Timesheet.'
                    exec msdb.dbo.DBADS_SP_SENDMAIL
                        @From, @Email, NULL, @Subject, @Body, NULL
                end
            end
            Else
            begin  /* Calculates the number of hours for the last month*/
                if(@hours < @totalhoursthismonth)
                Begin
                    SET @Body = 'Your hours for ' + datename(month,@startdate_current)+ ' are short. Please update your hours in Timesheet.'+ char(13) + CHAR(13) +
                                'http://mvaweb/MTSC/TimesheetReviewLog.aspx?SessionID=5682 '
                    exec msdb.dbo.DBADS_SP_SENDMAIL
                        @From, @Email, NULL, @Subject, @Body, NULL
                End   
            end
        end
    fetch next from DataThisMonth into @UserID, @Hours, @Email, @Joindate, @lastDate
end
close DataThisMonth
Deallocate DataThisMonth