Notes

USE [ComputerizedTest]
GO
/****** Object: StoredProcedure [dbo].[CBT_TESACTUALH_AssignTesToUser] Script Date: 03/01/2015 22:41:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER procedure [dbo].[CBT_TESACTUALH_AssignTesToUser]
@Username varchar(100)
AS
DECLARE @UserInputId varchar(100),
@TesCd varchar(100),
@KodePaket varchar(100),
@Subtes varchar(100),
@Descs varchar(100),
@JumlahSoal int,
@JumlahSoalTes int,
@count int,
@Status char(1),
@SequenceNo int,
@tesSequenceNo int, @NamaFile varchar(100), @KD varchar(50), @MATERI_SOAL varchar(80),
@NoPeserta varchar(15), @Priority int, @Prioritas int,
@UserSubtes varchar(100), @UserPaket varchar(100), @Type varchar(10)

-- cek apakah user sudah memiliki nopeserta
SELECT @NoPeserta=cek_kd_pes, @UserSubtes=ISNULL(Subtes,''), @UserPaket=ISNULL(Paket,'') FROM CBT_USERS WHERE UserName=@UserName

--IF (@NoPeserta IS NOT NULL AND @UserSubtes IS NOT NULL AND @UserPaket IS NOT NULL)
IF (@NoPeserta IS NOT NULL)
BEGIN
IF OBJECT_ID (N'#CBT_TempActualTes', N'U') IS NOT NULL
drop TABLE #CBT_TempActualTes
CREATE TABLE #CBT_TempActualTes (SequenceNo int, TesSequenceNo int, NAMA_FILE varchar(100), KD varchar(50), MateriSoal varchar(80), Prioritas int)

SELECT @UserInputId = UserId FROM aspnet_users WHERE Username = @Username

--cek apakah ada tes yang tersedia untuk user
IF EXISTS( SELECT TOP 1 TesCd, 0, @UserInputId, 'A', @UserInputId, @UserInputId, GETDATE(), GETDATE() FROM CBT_TESH WHERE Status='A'
AND TesCd NOT IN (SELECT TesCd FROM CBT_ACtualTesH WHERE UserId=@UserInputId)
)
BEGIN

CREATE TABLE #Paket (Value varchar(100))
DECLARE @SQL NVARCHAR(2000)
SET @SQL = 'INSERT INTO #Paket select KODE_PAKET FROM CBT_TESD WHERE ISNULL(KODE_PAKET,'''') IN (''' + @UserPaket + ''')'
EXECUTE sp_executesql @SQL

--CREATE TABLE #Subtes (Value varchar(100))
DECLARE @SQLSubtes NVARCHAR(2000)
--IF @UserSubtes <> ''
--SET @SQLSubtes = 'INSERT INTO #Subtes VALUES (''' + @UserSubtes + ''')'
--ELSE
--SET @SQLSubtes = 'INSERT INTO #Subtes SELECT Subtes FROM CBT_USERS_TES WHERE TesCd=(SELECT TOP 1 TesCd FROM CBT_TESH WHERE Status=''A'') AND UserId =''' + @UserInputId + ''''
--EXECUTE sp_executesql @SQLSubtes
-- Create Header
INSERT CBT_ActualTESH
(
TesCd,
ActualTime,
UserId,
Status,
UserInput,
UserEdit,
TimeInput,
TimeEdit,
TESH_HSequenceNo,
TESD_HSequenceNo
)
SELECT TOP 1 TesCd, 0, @UserInputId, 'A', @UserInputId, @UserInputId, GETDATE(), GETDATE(),
null,--(select TOP 1 SequenceNo FROM CBT_TESH_History WHERE TesCd=A.TesCd ORDER By SequenceNo DESC)TESH_HSequenceNo,
null--(select TOP 1 HSequenceNo FROM CBT_TESD_History WHERE TesCd=A.TesCd ORDER By ID DESC)TESD_HSequenceNo
FROM CBT_TESH A WHERE Status='A'

--Ambil Jumlah Soal yang akan dimasukkan dalam TES
SELECT @JumlahSoalTes=JumlahSoal FROM CBT_TESH WHERE Status='A' --AND TesCd='234'

SET @SequenceNo = @@IDENTITY
SELECT @TesCd = TesCd FROM CBT_ActualTESH WHERE SequenceNo=@SequenceNo

--UPDATE CBT_TESH
--SET UserEdit=@UserInputId,
-- TimeEdit=GETDATE()
--WHERE TesCd=@TesCd

-- Create Detail

DECLARE #Tesdetail CURSOR
-- TODO : Seharusnya Setiap SUBTES digroup by Prioritas
FOR SELECT * FROM
(
SELECT * FROM(SELECT TOP 1 SequenceNo, KODE_PAKET, JumlahSoal, TesCd, Priority, Subtes FROM CBT_TESD WHERE TesCd=@TesCd AND Priority=1 AND Status='A' AND KODE_PAKET IN (SELECT KODE_PAKET FROM CBT_USERS_TES WHERE TesCd=@TesCd AND UserId=@UserInputId) ORDER BY Priority, NEWID())P1
UNION
SELECT * FROM(SELECT TOP 1 SequenceNo, KODE_PAKET, JumlahSoal, TesCd, Priority, Subtes FROM CBT_TESD WHERE TesCd=@TesCd AND Priority=2 AND Status='A' AND KODE_PAKET IN (SELECT KODE_PAKET FROM CBT_USERS_TES WHERE TesCd=@TesCd AND UserId=@UserInputId) ORDER BY Priority, NEWID())P2
)A
WHERE TesCd = (SELECT TOP 1 TesCd FROM CBT_TESH WHERE Status='A') -- only 1 tes has A status.
--AND ISNULL(SUBTES,'') IN (CASE @UserSubtes WHEN '' THEN Subtes ELSE @UserSubtes END)
--AND ISNULL(KODE_PAKET,'') IN (CASE @UserPaket WHEN '' THEN KODE_PAKET ELSE (SELECT TOP 1 VALUE FROM #Paket) END)
--AND KODE_PAKET IN (CASE @UserPaket WHEN '' THEN KODE_PAKET ELSE (SELECT TOP 1 VALUE FROM #Paket) END)
--AND KODE_PAKET IN (SELECT VALUE FROM #Subtes)
--AND Subtes IN (SELECT Subtes FROM CBT_USERS_TES WHERE TesCd=(SELECT TOP 1 TesCd FROM CBT_TESH WHERE Status='A') AND UserId = @UserInputId)
--AND SUBTES IN (CASE @UserSubtes WHEN '' THEN (SELECT Subtes FROM CBT_USERS_TES WHERE TesCd=(SELECT TOP 1 TesCd FROM CBT_TESH WHERE Status='A') AND UserId = @UserInputId) ELSE @UserSubtes END)
ORDER BY Priority
OPEN #Tesdetail
FETCH NEXT FROM #Tesdetail INTO @tesSequenceNo, @KodePaket, @JumlahSoal, @TesCd, @Priority, @Subtes
WHILE @@FETCH_STATUS = 0
BEGIN

DELETE FROM #CBT_TempActualTes -- Temporary tabel datanya per paket

DECLARE #Soal CURSOR

FOR SELECT A.NAMA_FILE, ISNULL(B.KD,'') AS KD, ISNULL(B.MATERI_SOAL,'') AS MATERI_SOAL FROM [BankSoal].dbo.PAKET_SOAL_DETIL A
INNER JOIN [BankSoal].dbo.SOAL B ON B.NAMA_FILE=A.NAMA_FILE
WHERE KODE_PAKET=@KodePaket --ORDER BY NEWID()
OPEN #Soal
FETCH NEXT FROM #Soal INTO @NamaFile, @KD, @MATERI_SOAL
WHILE @@FETCH_STATUS = 0
BEGIN


/*
INSERT INTO #CBT_TempActualTes(SequenceNo, TesSequenceNo, NAMA_FILE, KD, MateriSoal, Prioritas)
VALUES
(
@SequenceNo, @tesSequenceNo, @NamaFile, @KD, @MATERI_SOAL,
(
SELECT TOP 1 Prioritas FROM CBT_TESD_KD_MateriSoal
WHERE detailSequenceNo=@tesSequenceNo AND KD=@KD AND MATERI_SOAL=@MATERI_SOAL
)
)
*/

INSERT INTO CBT_ActualTESD(SequenceNo, TesSequenceNo, NAMA_FILE, PATH_FILE, UserAnswer, ActualTime, UserId, Status, UserInput, UserEdit, TimeInput, TimeEdit)
VALUES (@SequenceNo, @tesSequenceNo, @NamaFile, '', '', 0, @UserInputId, 'A', @UserInputId, @UserInputId, GETDATE(), GETDATE())



FETCH NEXT FROM #Soal INTO @NamaFile, @KD, @MATERI_SOAL
END
CLOSE #Soal
DEALLOCATE #Soal

/*
DECLARE #Prioritas CURSOR
FOR SELECT Prioritas FROM #CBT_TempActualTes GROUP BY Prioritas
OPEN #Prioritas
FETCH NEXT FROM #Prioritas INTO @Prioritas
WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO CBT_ActualTESD(SequenceNo, TesSequenceNo, NAMA_FILE, PATH_FILE, UserAnswer, ActualTime, UserId, Status, UserInput, UserEdit, TimeInput, TimeEdit)
SELECT SequenceNo, TesSequenceNo, NAMA_FILE, '', '', 0, @UserInputId, 'A', @UserInputId, @UserInputId, GETDATE(), GETDATE()
FROM #CBT_TempActualTes WHERE Prioritas=@Prioritas ORDER BY NEWID()

FETCH NEXT FROM #Prioritas INTO @Prioritas
END
CLOSE #Prioritas
DEALLOCATE #Prioritas
*/


FETCH NEXT FROM #Tesdetail INTO @tesSequenceNo, @KodePaket, @JumlahSoal, @TesCd, @Priority, @Subtes
END
CLOSE #Tesdetail
DEALLOCATE #Tesdetail

DROP TABLE #CBT_TempActualTes
DROP TABLE #Paket
--DROP TABLE #Subtes


RETURN @SequenceNo
END
ELSE
BEGIN
drop TABLE #CBT_TempActualTes
RETURN 0
END
END
ELSE
RETURN 0

USE [ComputerizedTest]
GO
/****** Object: StoredProcedure [dbo].[CBT_TESACTUALH_AssignTesToUser] Script Date: 03/05/2015 14:48:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER procedure [dbo].[CBT_TESACTUALH_AssignTesToUser]
@Username varchar(100)
AS
DECLARE @UserInputId varchar(100),
@TesCd varchar(100),
@KodePaket varchar(100),
@Subtes varchar(100),
@Descs varchar(100),
@JumlahSoal int,
@JumlahSoalTes int,
@count int,
@Status char(1),
@SequenceNo int,
@tesSequenceNo int, @NamaFile varchar(100), @KD varchar(50), @MATERI_SOAL varchar(80),
@NoPeserta varchar(15), @Priority int, @Prioritas int,
@UserSubtes varchar(100), @UserPaket varchar(100), @Type varchar(10)

-- cek apakah user sudah memiliki nopeserta
SELECT @NoPeserta=cek_kd_pes, @UserSubtes=ISNULL(Subtes,''), @UserPaket=ISNULL(Paket,'') FROM CBT_USERS WHERE UserName=@UserName

--IF (@NoPeserta IS NOT NULL AND @UserSubtes IS NOT NULL AND @UserPaket IS NOT NULL)
IF (@NoPeserta IS NOT NULL)
BEGIN
IF OBJECT_ID (N'#CBT_TempActualTes', N'U') IS NOT NULL
drop TABLE #CBT_TempActualTes
CREATE TABLE #CBT_TempActualTes (SequenceNo int, TesSequenceNo int, NAMA_FILE varchar(100), KD varchar(50), MateriSoal varchar(80), Prioritas int)

SELECT @UserInputId = UserId FROM aspnet_users WHERE Username = @Username

--cek apakah ada tes yang tersedia untuk user
IF EXISTS( SELECT TOP 1 TesCd, 0, @UserInputId, 'A', @UserInputId, @UserInputId, GETDATE(), GETDATE() FROM CBT_TESH WHERE Status='A'
AND TesCd NOT IN (SELECT TesCd FROM CBT_ACtualTesH WHERE UserId=@UserInputId)
)
BEGIN

CREATE TABLE #Paket (Value varchar(100))
DECLARE @SQL NVARCHAR(2000)
SET @SQL = 'INSERT INTO #Paket select KODE_PAKET FROM CBT_TESD WHERE ISNULL(KODE_PAKET,'''') IN (''' + @UserPaket + ''')'
EXECUTE sp_executesql @SQL

--CREATE TABLE #Subtes (Value varchar(100))
DECLARE @SQLSubtes NVARCHAR(2000)
--IF @UserSubtes <> ''
--SET @SQLSubtes = 'INSERT INTO #Subtes VALUES (''' + @UserSubtes + ''')'
--ELSE
--SET @SQLSubtes = 'INSERT INTO #Subtes SELECT Subtes FROM CBT_USERS_TES WHERE TesCd=(SELECT TOP 1 TesCd FROM CBT_TESH WHERE Status=''A'') AND UserId =''' + @UserInputId + ''''
--EXECUTE sp_executesql @SQLSubtes
-- Create Header
INSERT CBT_ActualTESH
(
TesCd,
ActualTime,
UserId,
Status,
UserInput,
UserEdit,
TimeInput,
TimeEdit,
TESH_HSequenceNo,
TESD_HSequenceNo
)
SELECT TOP 1 TesCd, 0, @UserInputId, 'A', @UserInputId, @UserInputId, GETDATE(), GETDATE(),
null,--(select TOP 1 SequenceNo FROM CBT_TESH_History WHERE TesCd=A.TesCd ORDER By SequenceNo DESC)TESH_HSequenceNo,
null--(select TOP 1 HSequenceNo FROM CBT_TESD_History WHERE TesCd=A.TesCd ORDER By ID DESC)TESD_HSequenceNo
FROM CBT_TESH A WHERE Status='A'

--Ambil Jumlah Soal yang akan dimasukkan dalam TES
SELECT @JumlahSoalTes=JumlahSoal FROM CBT_TESH WHERE Status='A' --AND TesCd='234'

SET @SequenceNo = @@IDENTITY
SELECT @TesCd = TesCd FROM CBT_ActualTESH WHERE SequenceNo=@SequenceNo

--UPDATE CBT_TESH
--SET UserEdit=@UserInputId,
-- TimeEdit=GETDATE()
--WHERE TesCd=@TesCd

-- Create Detail

DECLARE #Tesdetail CURSOR
-- TODO : Seharusnya Setiap SUBTES digroup by Prioritas
FOR SELECT * FROM
(
SELECT * FROM(SELECT TOP 1 SequenceNo, KODE_PAKET, JumlahSoal, TesCd, Priority, Subtes FROM CBT_TESD WHERE TesCd=@TesCd AND Priority=1 AND Status='A' AND KODE_PAKET IN (SELECT KODE_PAKET FROM CBT_USERS_TES WHERE TesCd=@TesCd AND UserId=@UserInputId) ORDER BY Priority, NEWID())P1
UNION
SELECT * FROM(SELECT TOP 1 SequenceNo, KODE_PAKET, JumlahSoal, TesCd, Priority, Subtes FROM CBT_TESD WHERE TesCd=@TesCd AND Priority=2 AND Status='A' AND KODE_PAKET IN (SELECT KODE_PAKET FROM CBT_USERS_TES WHERE TesCd=@TesCd AND UserId=@UserInputId) ORDER BY Priority, NEWID())P2
UNION
SELECT * FROM(SELECT TOP 1 SequenceNo, KODE_PAKET, JumlahSoal, TesCd, Priority, Subtes FROM CBT_TESD WHERE TesCd=@TesCd AND Priority=3 AND Status='A' AND KODE_PAKET IN (SELECT KODE_PAKET FROM CBT_USERS_TES WHERE TesCd=@TesCd AND UserId=@UserInputId) ORDER BY Priority, NEWID())P3
UNION
SELECT * FROM(SELECT TOP 1 SequenceNo, KODE_PAKET, JumlahSoal, TesCd, Priority, Subtes FROM CBT_TESD WHERE TesCd=@TesCd AND Priority=4 AND Status='A' AND KODE_PAKET IN (SELECT KODE_PAKET FROM CBT_USERS_TES WHERE TesCd=@TesCd AND UserId=@UserInputId) ORDER BY Priority, NEWID())P4
UNION
SELECT * FROM(SELECT TOP 1 SequenceNo, KODE_PAKET, JumlahSoal, TesCd, Priority, Subtes FROM CBT_TESD WHERE TesCd=@TesCd AND Priority=5 AND Status='A' AND KODE_PAKET IN (SELECT KODE_PAKET FROM CBT_USERS_TES WHERE TesCd=@TesCd AND UserId=@UserInputId) ORDER BY Priority, NEWID())P5
UNION
SELECT * FROM(SELECT TOP 1 SequenceNo, KODE_PAKET, JumlahSoal, TesCd, Priority, Subtes FROM CBT_TESD WHERE TesCd=@TesCd AND Priority=6 AND Status='A' AND KODE_PAKET IN (SELECT KODE_PAKET FROM CBT_USERS_TES WHERE TesCd=@TesCd AND UserId=@UserInputId) ORDER BY Priority, NEWID())P6
UNION
SELECT * FROM(SELECT TOP 1 SequenceNo, KODE_PAKET, JumlahSoal, TesCd, Priority, Subtes FROM CBT_TESD WHERE TesCd=@TesCd AND Priority=7 AND Status='A' AND KODE_PAKET IN (SELECT KODE_PAKET FROM CBT_USERS_TES WHERE TesCd=@TesCd AND UserId=@UserInputId) ORDER BY Priority, NEWID())P7
)A
WHERE TesCd = (SELECT TOP 1 TesCd FROM CBT_TESH WHERE Status='A') -- only 1 tes has A status.
--AND ISNULL(SUBTES,'') IN (CASE @UserSubtes WHEN '' THEN Subtes ELSE @UserSubtes END)
--AND ISNULL(KODE_PAKET,'') IN (CASE @UserPaket WHEN '' THEN KODE_PAKET ELSE (SELECT TOP 1 VALUE FROM #Paket) END)
--AND KODE_PAKET IN (CASE @UserPaket WHEN '' THEN KODE_PAKET ELSE (SELECT TOP 1 VALUE FROM #Paket) END)
--AND KODE_PAKET IN (SELECT VALUE FROM #Subtes)
--AND Subtes IN (SELECT Subtes FROM CBT_USERS_TES WHERE TesCd=(SELECT TOP 1 TesCd FROM CBT_TESH WHERE Status='A') AND UserId = @UserInputId)
--AND SUBTES IN (CASE @UserSubtes WHEN '' THEN (SELECT Subtes FROM CBT_USERS_TES WHERE TesCd=(SELECT TOP 1 TesCd FROM CBT_TESH WHERE Status='A') AND UserId = @UserInputId) ELSE @UserSubtes END)
ORDER BY Priority
OPEN #Tesdetail
FETCH NEXT FROM #Tesdetail INTO @tesSequenceNo, @KodePaket, @JumlahSoal, @TesCd, @Priority, @Subtes
WHILE @@FETCH_STATUS = 0
BEGIN

DELETE FROM #CBT_TempActualTes -- Temporary tabel datanya per paket

DECLARE #Soal CURSOR

FOR SELECT A.NAMA_FILE, ISNULL(B.KD,'') AS KD, ISNULL(B.MATERI_SOAL,'') AS MATERI_SOAL FROM [BankSoal].dbo.PAKET_SOAL_DETIL A
INNER JOIN [BankSoal].dbo.SOAL B ON B.NAMA_FILE=A.NAMA_FILE
WHERE KODE_PAKET=@KodePaket --ORDER BY NEWID()
OPEN #Soal
FETCH NEXT FROM #Soal INTO @NamaFile, @KD, @MATERI_SOAL
WHILE @@FETCH_STATUS = 0
BEGIN


/*
INSERT INTO #CBT_TempActualTes(SequenceNo, TesSequenceNo, NAMA_FILE, KD, MateriSoal, Prioritas)
VALUES
(
@SequenceNo, @tesSequenceNo, @NamaFile, @KD, @MATERI_SOAL,
(
SELECT TOP 1 Prioritas FROM CBT_TESD_KD_MateriSoal
WHERE detailSequenceNo=@tesSequenceNo AND KD=@KD AND MATERI_SOAL=@MATERI_SOAL
)
)
*/

INSERT INTO CBT_ActualTESD(SequenceNo, TesSequenceNo, NAMA_FILE, PATH_FILE, UserAnswer, ActualTime, UserId, Status, UserInput, UserEdit, TimeInput, TimeEdit)
VALUES (@SequenceNo, @tesSequenceNo, @NamaFile, '', '', 0, @UserInputId, 'A', @UserInputId, @UserInputId, GETDATE(), GETDATE())



FETCH NEXT FROM #Soal INTO @NamaFile, @KD, @MATERI_SOAL
END
CLOSE #Soal
DEALLOCATE #Soal

/*
DECLARE #Prioritas CURSOR
FOR SELECT Prioritas FROM #CBT_TempActualTes GROUP BY Prioritas
OPEN #Prioritas
FETCH NEXT FROM #Prioritas INTO @Prioritas
WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO CBT_ActualTESD(SequenceNo, TesSequenceNo, NAMA_FILE, PATH_FILE, UserAnswer, ActualTime, UserId, Status, UserInput, UserEdit, TimeInput, TimeEdit)
SELECT SequenceNo, TesSequenceNo, NAMA_FILE, '', '', 0, @UserInputId, 'A', @UserInputId, @UserInputId, GETDATE(), GETDATE()
FROM #CBT_TempActualTes WHERE Prioritas=@Prioritas ORDER BY NEWID()

FETCH NEXT FROM #Prioritas INTO @Prioritas
END
CLOSE #Prioritas
DEALLOCATE #Prioritas
*/


FETCH NEXT FROM #Tesdetail INTO @tesSequenceNo, @KodePaket, @JumlahSoal, @TesCd, @Priority, @Subtes
END
CLOSE #Tesdetail
DEALLOCATE #Tesdetail

DROP TABLE #CBT_TempActualTes
DROP TABLE #Paket
--DROP TABLE #Subtes


RETURN @SequenceNo
END
ELSE
BEGIN
drop TABLE #CBT_TempActualTes
RETURN 0
END
END
ELSE
RETURN 0

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.CBT_USERS_TES ADD
UserName varchar(50) NULL
GO
ALTER TABLE dbo.CBT_USERS_TES SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

USE [ComputerizedTest]
GO
/****** Object: StoredProcedure [dbo].[CBT_USERS_TES_AssignDataToUserOverrideValidPaketData] Script Date: 3/1/2015 6:25:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER procedure [dbo].[CBT_USERS_TES_AssignDataToUserOverrideValidPaketData]
@TesCd VARCHAR(100),
@KodeMapel VARCHAR(100),
@UserId UNIQUEIDENTIFIER
AS
DECLARE @KodePaket VARCHAR(100), @UserName VARCHAR(50)

SELECT @KodePaket=Kode_Paket FROM CBT_TESD WHERE TesCd=@TesCd AND Subtes=@KodeMapel ORDER BY NEWID()
SELECT @UserName=UserName FROM CBT_USERS WHERE UserId=@UserId
IF NOT EXISTS(
SELECT * FROM CBT_USERS_TES WHERE TesCd=@TesCd AND Kode_Paket=@KodePaket
AND UserId=@UserId
)
BEGIN
INSERT INTO CBT_USERS_TES(TesCd, Tanggal, Kode_Paket, UserId, UserName)
VALUES(@TesCd, GETDATE(), @KodePaket, @UserId, @UserName)
RETURN 0
END
ELSE
RETURN 1

USE [ComputerizedTest]
GO
/****** Object: StoredProcedure [dbo].[CBT_USERS_TES_AssignDataToUser] Script Date: 3/1/2015 6:25:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER procedure [dbo].[CBT_USERS_TES_AssignDataToUser]
@TesCd VARCHAR(100),
@KodePaket VARCHAR(100),
@UserId UNIQUEIDENTIFIER
AS
DECLARE @UserName VARCHAR(50)

SELECT @UserName=UserName FROM CBT_USERS WHERE UserId=@UserId

IF NOT EXISTS(
SELECT * FROM CBT_USERS_TES WHERE TesCd=@TesCd AND Kode_Paket=@KodePaket
AND UserId=@UserId
)
BEGIN
INSERT INTO CBT_USERS_TES(TesCd, Tanggal, Kode_Paket, UserId, UserName)
VALUES(@TesCd, GETDATE(), @KodePaket, @UserId, @UserName)
RETURN 0
END
ELSE
RETURN 1