/**
*    Marks a crew time record as deleted.
*
*    Parameters:
*        @inSubmitterId: SysUserID of who is submitting this item.
*        @inMobileCrewTimeId: MobileCrewTime primary key (approval, edit).
*        @inInvokedFromWeb: 1 if this is from a web browser via MoversConnect.  When from the web, the submitter must be the lead crewmember on the service.
*
* A select statement returns these fields (used to display the results back to the user):
*        ActionResult = A message about why the action was not successful.
*/
create procedure [dbo].[MobileCrewTimeDeletePunch]
    @inSubmitterId int,
    @inMobileCrewTimeId bigint,
    @inInvokedFromWeb bit
as
set nocount on

-- Constants to clarify what these single character codes mean.
declare @DELETED_ACTION varchar(1) = 'D'
declare @WEB_PAGE_SOURCE varchar(1) = 'W'
declare @APP_SOURCE varchar(1) = 'A'

declare @theActionResult varchar(255)
declare @theServiceWasDeleted int
declare @theMobileCrewTimeId bigint
declare @theActionType varchar(1)
declare @theHasBeenApproved bit
declare @theIsMarkedAsOvertime bit
declare @theCrewTimeIsActivated bit
declare @theCrewTimeAlreadyDeleted bit

begin transaction

select
    @theMobileCrewTimeId = MobileCrewTime.MobileCrewTimeID,
    @theHasBeenApproved = MobileCrewTime.IsApproved,
    @theCrewTimeIsActivated = MobileSetup.ActivateCrewTime,
    @inInvokedFromWeb = isnull( @inInvokedFromWeb, 0 ),
    @theCrewTimeAlreadyDeleted = MobileCrewTime.IsDeleted
from MobileCrewTime
inner join MobileSetup on MobileSetup.MobileSetupID = 1
left outer join MobileCrewTimeActivity as DefaultMobileCrewTimeActivity on DefaultMobileCrewTimeActivity.IsDefaultActivity = 1
where MobileCrewTime.MobileCrewTimeID = @inMobileCrewTimeId

if( isnull( @theCrewTimeIsActivated, 0 ) != 1 )
begin
    set @theActionResult = 'Crew Time feature is not activated.'
end
else if( @theMobileCrewTimeId is null )
begin
    set @theActionResult = 'Crew time record could not be found.'
end
else if( @theCrewTimeAlreadyDeleted = 1 )
begin
    set @theActionResult = 'Crew time record is already deleted.'
end
else if( @theHasBeenApproved = 1 )
begin
    set @theActionResult = 'Crew time record has already been approved.'
end

if( @theActionResult is not null )
begin
    set @theServiceWasDeleted = 0
end
else
begin
    -- Mark the time punch as deleted.
    update MobileCrewTime set
        IsDeleted = cast( 1 as bit )
    from MobileCrewTime
    where MobileCrewTime.MobileCrewTimeID = @inMobileCrewTimeId and
        MobileCrewTime.IsApproved = 0

    set @theServiceWasDeleted = @@rowcount
    if( @theServiceWasDeleted = 0 )
    begin
        set @theActionResult = 'Crew time record has already been approved.'
    end
end

commit transaction

if( @theServiceWasDeleted = 1 )
begin

    -- Get the current UTC time and the DB time zone (in minutes).
    declare @theTimeStampUtc datetime = getutcdate()
    declare @theDBTimeZone smallint = datediff( minute, getutcdate(), getdate() )

    insert into MobileCrewTimeAudit
    (
        MobileCrewTimeFID,
        [Action],
        SubmitterFID,
        [Source],
        TimeUtc,
        TimeZone
    )
    select
        MobileCrewTimeFID = @inMobileCrewTimeId,
        [Action] = @DELETED_ACTION,
        SubmitterFID = @inSubmitterId,
        [Source] =
            case
                when 1 = @inInvokedFromWeb then @WEB_PAGE_SOURCE
                else @APP_SOURCE
            end,
        TimeUtc = @theTimeStampUtc,
        TimeZone = @theDBTimeZone

        -- If the deleted punch was an "open" punch (one that was punched in but not punched out)
        -- then reset the "open" punch to null.
        update MobileCrewTimeLastPunch set
            MobileCrewTimeFID = null,
            LastPunchTimeUtc = '2016-01-01'
        where
            MobileCrewTimeFID = @inMobileCrewTimeId

end

select
    ActionResult = isnull( @theActionResult, '' )
GO
GRANT EXECUTE ON  [dbo].[MobileCrewTimeDeletePunch] TO [MssExec]
GO