sp_Update_real_svr

USE [college2]

GO

/****** Object:  StoredProcedure [dbo].[sp_Insert_real_svr]    Script Date: 04/25/2024 17:04:16 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER procedure [dbo].[sp_Insert_real_svr]

@aEnrollNumber numeric,

@aVerifyMode nvarchar(50),

@aInOutMode numeric,

@adtLog datetime,

@abDrawFlag bit,

@astrRemoteIP nvarchar(50),

@anRemotePort numeric,

@anDeviceID nvarchar(50),

@astrSerialNo nvarchar(50),

@astrRouterIP nvarchar(50),

@out int output

as

begin

    if(select count(*) from real_svr where (aEnrollNumber = @aEnrollNumber) AND (adtLog = @adtLog)) = 0 

        begin

declare

@uid uniqueidentifier,

@batch_id uniqueidentifier, 

@class_id uniqueidentifier,

@department_id uniqueidentifier

set @uid = null

SELECT @uid = id, @department_id = department_id FROM faculty_enrollment WHERE (faculty_enroll_id = @aEnrollNumber)

SELECT class_id FROM unique_class WHERE (@adtLog BETWEEN class_start_time AND class_end_time) AND (class_subject_or_department = @department_id)

if (@uid = null)

BEGIN

(SELECT @uid = id, @batch_id = batch_id FROM batch_student WHERE (batch_student_enroll_id = @aEnrollNumber))

SELECT @class_id = class_id FROM unique_class WHERE (@adtLog BETWEEN class_start_time AND class_end_time) AND (batch_id = @batch_id)

END

        

            INSERT INTO real_svr([uid], aEnrollNumber, aVerifyMode, aInOutMode, adtLog, abDrawFlag, astrRemoteIP, anRemotePort, anDeviceID, astrSerialNo, astrRouterIP, batch_id, class_id, department_id) VALUES (@uid, @aEnrollNumber, @aVerifyMode, @aInOutMode, @adtLog, @abDrawFlag, @astrRemoteIP, @anRemotePort, @anDeviceID, @astrSerialNo, @astrRouterIP, @batch_id, @class_id, @department_id)

            set @out = 1 -- Record inserted successfully.

        end

    else

        begin 

            set @out = 2 -- Record already exists.

        end

    return @out

end

  UPDATE unique_class

SET faculty_enrollment_uid = faculty_enrollment.id

FROM unique_class INNER JOIN faculty_enrollment ON unique_class.Id = faculty_enrollment.faculty_enroll_id

WHERE (unique_class.class_id = unique_class.class_id)