/*
ISC 561
In-class demo: AMCIS database (see handout)

Sample data

*/
SET NOCOUNT ON

GO

--Insert Institutions

BEGIN TRY
  BEGIN TRANSACTION

--INSERT INTO tblInstituion (LongName, ShortName)
--SELECT dbo.Northwind.CompanyName, dbo.Northwind.CustomerID
--FROM Customers

  INSERT INTO tblInstitution (LongName, ShortName)
    VALUES ('University of South Alabama', 'USA')
  INSERT INTO tblInstitution (LongName, ShortName)
    VALUES ('University of California, Berkeley', 'UCB')
  INSERT INTO tblInstitution (LongName, ShortName)
    VALUES ('Georgia State University', 'GSU')
  INSERT INTO tblInstitution (LongName, ShortName)
    VALUES ('Penn State University', 'PSU')

  COMMIT TRANSACTION

  PRINT 'Institutions successfully inserted...'

END TRY
BEGIN CATCH
  DECLARE @ErrorMessage VARCHAR(500)
  SET @ErrorMessage = ERROR_MESSAGE() + ' Rolledback transaction: Institution insertions.'
  ROLLBACK TRANSACTION
  RAISERROR (@ErrorMessage, 16,1)
END CATCH
GO

--SELECT * FROM Master.dbo.sysmessages

delete tblInstitution

--SELECT * FROM tblInstitution

--Insert participants
BEGIN TRY
  BEGIN TRANSACTION
  DECLARE @InstitutionID INT
  SET @InstitutionID = (SELECT InstitutionID FROM tblInstitution WHERE ShortName = 'UCB')
  INSERT INTO tblParticipant (FirstName, LastName, Email, InstitutionID)
    VALUES ('Ajit', 'Patankar', 'apatankar@ucb.edu', @InstitutionID)
  SET @InstitutionID = (SELECT InstitutionID FROM tblInstitution WHERE ShortName = 'GSU')
  INSERT INTO tblParticipant (FirstName, LastName, Email, InstitutionID)
     VALUES ('Cecil', 'Chua', 'cchua@gsu.edu', @InstitutionID)
  INSERT INTO tblParticipant (FirstName, LastName, Email, InstitutionID)
     VALUES ('Veda', 'Storey', 'vstorey@gsu.edu', @InstitutionID)
  SET @InstitutionID = (SELECT InstitutionID FROM tblInstitution WHERE ShortName = 'PSU')
  INSERT INTO tblParticipant (FirstName, LastName, Email, InstitutionID)
     VALUES ('Sandeep', 'Purao', 'spurao@psu.edu', @InstitutionID)
  SET @InstitutionID = (SELECT InstitutionID FROM tblInstitution WHERE ShortName = 'USA')
  INSERT INTO tblParticipant (FirstName, LastName, Email, InstitutionID)
     VALUES ('Cesar', 'Alvarez', 'calvarez@usouthal.edu', @InstitutionID)
  INSERT INTO tblParticipant (FirstName, LastName, Email, InstitutionID)
     VALUES ('Harold', 'Pardue', 'hpardue@usouthal.edu', @InstitutionID)
  INSERT INTO tblParticipant (FirstName, LastName, Email, InstitutionID)
     VALUES ('Jeffrey', 'Landry', 'jlandry@usouthal.edu', @InstitutionID)
  INSERT INTO tblParticipant (FirstName, LastName, Email, InstitutionID)
     VALUES ('Loveleen', 'Sharma', 'lsharma@usouthal.edu', @InstitutionID)

  COMMIT TRANSACTION

  PRINT 'Participants successfully inserted...'

END TRY
BEGIN CATCH
  DECLARE @ErrorMessage VARCHAR(500)
  SET @ErrorMessage = ERROR_MESSAGE() + ' Rolledback transaction: Participant insertions.'
  ROLLBACK TRANSACTION
  RAISERROR (@ErrorMessage, 16,1)
END CATCH
GO

--SELECT * FROM tblParticipant

--Insert Editor
BEGIN TRY
  BEGIN TRANSACTION

  DECLARE @ParticipantID INT
  SET @ParticipantID = (SELECT ParticipantID FROM tblParticipant WHERE Email = 'hpardue@usouthal.edu')
  INSERT INTO tblEditor (Title, Cellphone, ParticipantID) 
     VALUES ('Executive Editor', '2514333456', @ParticipantID) 

  COMMIT TRANSACTION
  PRINT 'Editors successfully inserted...'

END TRY
BEGIN CATCH
  DECLARE @ErrorMessage VARCHAR(500)
  SET @ErrorMessage = ERROR_MESSAGE() + ' Rolledback transaction: Editor insertions.'
  ROLLBACK TRANSACTION
  RAISERROR (@ErrorMessage, 16,1)
END CATCH
GO

--SELECT * FROM tblEditor

--Insert TrackChair
BEGIN TRY
  BEGIN TRANSACTION

  DECLARE @ParticipantID INT
  SET @ParticipantID = (SELECT ParticipantID FROM tblParticipant WHERE Email = 'apatankar@ucb.edu')
  INSERT INTO tblTrackChair (AcceptanceRate, ParticipantID) 
     VALUES (0, @ParticipantID) 

  COMMIT TRANSACTION
  PRINT 'TrackChair successfully inserted...'

END TRY
BEGIN CATCH
  DECLARE @ErrorMessage VARCHAR(500)
  SET @ErrorMessage = ERROR_MESSAGE() + ' Rolledback transaction: TrackChair insertions.'
  ROLLBACK TRANSACTION
  RAISERROR (@ErrorMessage, 16,1)
END CATCH
GO

--SELECT * FROM tblTrackChair

--Insert Authors
BEGIN TRY
  BEGIN TRANSACTION

  DECLARE @ParticipantID INT
  SET @ParticipantID = (SELECT ParticipantID FROM tblParticipant WHERE Email = 'apatankar@ucb.edu')
  INSERT INTO tblAuthor (Attending, ParticipantID) 
     VALUES (1, @ParticipantID) 
  SET @ParticipantID = (SELECT ParticipantID FROM tblParticipant WHERE Email = 'cchua@gsu.edu')
  INSERT INTO tblAuthor (Attending, ParticipantID) 
     VALUES (1, @ParticipantID) 
  SET @ParticipantID = (SELECT ParticipantID FROM tblParticipant WHERE Email = 'vstorey@gsu.edu')
  INSERT INTO tblAuthor (Attending, ParticipantID) 
     VALUES (1, @ParticipantID) 
  SET @ParticipantID = (SELECT ParticipantID FROM tblParticipant WHERE Email = 'spurao@psu.edu')
  INSERT INTO tblAuthor (Attending, ParticipantID) 
     VALUES (1, @ParticipantID) 
  SET @ParticipantID = (SELECT ParticipantID FROM tblParticipant WHERE Email = 'calvarez@usouthal.edu')
  INSERT INTO tblAuthor (Attending, ParticipantID) 
     VALUES (1, @ParticipantID) 
  SET @ParticipantID = (SELECT ParticipantID FROM tblParticipant WHERE Email = 'hpardue@usouthal.edu')
  INSERT INTO tblAuthor (Attending, ParticipantID) 
     VALUES (1, @ParticipantID) 
  SET @ParticipantID = (SELECT ParticipantID FROM tblParticipant WHERE Email = 'jlandry@usouthal.edu')
  INSERT INTO tblAuthor (Attending, ParticipantID) 
     VALUES (1, @ParticipantID) 
  SET @ParticipantID = (SELECT ParticipantID FROM tblParticipant WHERE Email = 'lsharma@usouthal.edu')
  INSERT INTO tblAuthor (Attending, ParticipantID) 
     VALUES (1, @ParticipantID) 


  COMMIT TRANSACTION
  PRINT 'Authors successfully inserted...'

END TRY
BEGIN CATCH
  DECLARE @ErrorMessage VARCHAR(500)
  SET @ErrorMessage = ERROR_MESSAGE() + ' Rolledback transaction: Author insertions.'
  ROLLBACK TRANSACTION
  RAISERROR (@ErrorMessage, 16,1)
END CATCH
GO

--SELECT * FROM tblAuthor

--Insert TimeSlots
BEGIN TRY
  BEGIN TRANSACTION

  INSERT INTO tblTimeSlot (TimeBegin, TimeEnd, ConferenceYear)
     VALUES ('2003-8-4 8:30 am', '2003-8-4 10:00 am', '2003')
  INSERT INTO tblTimeSlot (TimeBegin, TimeEnd, ConferenceYear)
     VALUES ('2003-8-4 10:30 am', '2003-8-4 12:00 am', '2003')
  INSERT INTO tblTimeSlot (TimeBegin, TimeEnd, ConferenceYear)
     VALUES ('2003-8-4 1:30 am', '2003-8-4 4:00 am', '2003')
  INSERT INTO tblTimeSlot (TimeBegin, TimeEnd, ConferenceYear)
     VALUES ('2003-8-4 4:30 am', '2003-8-4 6:00 am', '2003')
  INSERT INTO tblTimeSlot (TimeBegin, TimeEnd, ConferenceYear)
     VALUES ('2003-8-5 8:30 am', '2003-8-5 10:00 am', '2003')
  INSERT INTO tblTimeSlot (TimeBegin, TimeEnd, ConferenceYear)
     VALUES ('2003-8-5 10:30 am', '2003-8-5 12:00 am', '2003')
  INSERT INTO tblTimeSlot (TimeBegin, TimeEnd, ConferenceYear)
     VALUES ('2003-8-5 1:30 am', '2003-8-5 4:00 am', '2003')
  INSERT INTO tblTimeSlot (TimeBegin, TimeEnd, ConferenceYear)
     VALUES ('2003-8-5 4:30 am', '2003-8-5 6:00 am', '2003')
  INSERT INTO tblTimeSlot (TimeBegin, TimeEnd, ConferenceYear)
     VALUES ('2003-8-6 8:30 am', '2003-8-6 10:00 am', '2003')
  INSERT INTO tblTimeSlot (TimeBegin, TimeEnd, ConferenceYear)
     VALUES ('2003-8-6 10:30 am', '2003-8-6 12:00 am', '2003')

  COMMIT TRANSACTION
  PRINT 'TimeSlots successfully inserted...'

END TRY
BEGIN CATCH
  DECLARE @ErrorMessage VARCHAR(500)
  SET @ErrorMessage = ERROR_MESSAGE() + ' Rolledback transaction: TimeSlot insertions.'
  ROLLBACK TRANSACTION
  RAISERROR (@ErrorMessage, 16,1)
END CATCH
GO

--SELECT * FROM tblTimeSlot

--Insert Rooms
BEGIN TRY
  BEGIN TRANSACTION

  INSERT INTO tblRoom (RoomName, MaxOccupancy, ConferenceYear)
     VALUES ('Salon A', 25, '2003')
  INSERT INTO tblRoom (RoomName, MaxOccupancy, ConferenceYear)
     VALUES ('Salon B', 20, '2003')
  INSERT INTO tblRoom (RoomName, MaxOccupancy, ConferenceYear)
       VALUES ('Salon C', 18, '2003')
  INSERT INTO tblRoom (RoomName, MaxOccupancy, ConferenceYear)
     VALUES ('Salon D', 25, '2003')
  INSERT INTO tblRoom (RoomName, MaxOccupancy, ConferenceYear)
     VALUES ('Room 1', 20, '2003')
  INSERT INTO tblRoom (RoomName, MaxOccupancy, ConferenceYear)
     VALUES ('Room 2', 56, '2003')
  INSERT INTO tblRoom (RoomName, MaxOccupancy, ConferenceYear)
     VALUES ('Room 3', 128, '2003')
  INSERT INTO tblRoom (RoomName, MaxOccupancy, ConferenceYear)
     VALUES ('Room 4', 75, '2003')
  INSERT INTO tblRoom (RoomName, MaxOccupancy, ConferenceYear)
     VALUES ('Room 5', 25, '2003')
  INSERT INTO tblRoom (RoomName, MaxOccupancy, ConferenceYear)
     VALUES ('Room 6', 25, '2003')
  INSERT INTO tblRoom (RoomName, MaxOccupancy, ConferenceYear)
     VALUES ('Room 7', 56, '2003')
  INSERT INTO tblRoom (RoomName, MaxOccupancy, ConferenceYear)
     VALUES ('Room 8', 128, '2003')
  INSERT INTO tblRoom (RoomName, MaxOccupancy, ConferenceYear)
     VALUES ('Room 9', 45, '2003')
  INSERT INTO tblRoom (RoomName, MaxOccupancy, ConferenceYear)
     VALUES ('Room 10', 45, '2003')
  INSERT INTO tblRoom (RoomName, MaxOccupancy, ConferenceYear)
     VALUES ('Room 11', 35, '2003')

  COMMIT TRANSACTION
  PRINT 'Rooms successfully inserted...'

END TRY
BEGIN CATCH
  DECLARE @ErrorMessage VARCHAR(500)
  SET @ErrorMessage = ERROR_MESSAGE() + ' Rolledback transaction: Room insertions.'
  ROLLBACK TRANSACTION
  RAISERROR (@ErrorMessage, 16,1)
END CATCH
GO

--SELECT * FROM tblRoom

--Insert Tracks
BEGIN TRY
  BEGIN TRANSACTION

  INSERT INTO tblTrack (Title, SubTrackID)
     VALUES ('Infrastructure', NULL)

  INSERT INTO tblTrack (Title, SubTrackID)
     VALUES ('Component-Based Software Development and Web Services', @@Identity)

  COMMIT TRANSACTION
  PRINT 'Tracks successfully inserted...'

END TRY
BEGIN CATCH
  DECLARE @ErrorMessage VARCHAR(500)
  SET @ErrorMessage = ERROR_MESSAGE() + ' Rolledback transaction: Track insertions.'
  ROLLBACK TRANSACTION
  RAISERROR (@ErrorMessage, 16,1)
END CATCH
GO

--SELECT * FROM tblTrack

--Insert Papers
BEGIN TRY
  BEGIN TRANSACTION
  DECLARE @TrackID INT, @PaperID INT, @ParticipantID INT

  SET @TrackID = (SELECT TrackID FROM tblTrack WHERE Title = 'Component-Based Software Development and Web Services')

  INSERT INTO tblPaper (Title, KeywordList, SubmissionStatus, TrackID)
     VALUES ('READABLE: An Approach and an Environment for Developing Maintainable Web Software', 'Web, software', 'Received',@TrackID)
  SET @PaperID = SCOPE_IDENTITY()

  SET @ParticipantID = (SELECT ParticipantID FROM tblParticipant WHERE Email = 'cchua@gsu.edu')
  INSERT INTO tblAuthorPaper (AuthorID, PaperID) VALUES (@ParticipantID, @PaperID)
  SET @ParticipantID = (SELECT ParticipantID FROM tblParticipant WHERE Email = 'vstorey@gsu.edu')
  INSERT INTO tblAuthorPaper (AuthorID, PaperID) VALUES (@ParticipantID, @PaperID)
  SET @ParticipantID = (SELECT ParticipantID FROM tblParticipant WHERE Email = 'spurao@psu.edu')
  INSERT INTO tblAuthorPaper (AuthorID, PaperID) VALUES (@ParticipantID, @PaperID)

  INSERT INTO tblPaper (Title, KeywordList, SubmissionStatus, TrackID)
     VALUES ('A Reference Architecture Based on Web Components for Ubiquitous Information Systems', 'Components, software, Architecture', 'Received',@TrackID)
  SET @PaperID = SCOPE_IDENTITY()

  SET @ParticipantID = (SELECT ParticipantID FROM tblParticipant WHERE Email = 'calvarez@usouthal.edu')
  INSERT INTO tblAuthorPaper (AuthorID, PaperID) VALUES (@ParticipantID, @PaperID)
  SET @ParticipantID = (SELECT ParticipantID FROM tblParticipant WHERE Email = 'hpardue@usouthal.edu')
  INSERT INTO tblAuthorPaper (AuthorID, PaperID) VALUES (@ParticipantID, @PaperID)

  INSERT INTO tblPaper(Title, KeywordList, SubmissionStatus, TrackID)
     VALUES ('Web Services Enabled Architecture for Interorganizational Business Process Management', 'Components, software, Architecture', 'Received',@TrackID)
  SET @PaperID = SCOPE_IDENTITY()

  SET @ParticipantID = (SELECT ParticipantID FROM tblParticipant WHERE Email = 'apatankar@ucb.edu')
  INSERT INTO tblAuthorPaper (AuthorID, PaperID) VALUES (@ParticipantID, @PaperID)

  COMMIT TRANSACTION
  PRINT 'Papers successfully inserted...'

END TRY
BEGIN CATCH
  DECLARE @ErrorMessage VARCHAR(500)
  SET @ErrorMessage = ERROR_MESSAGE() + ' Rolledback transaction: Paper insertions.'
  ROLLBACK TRANSACTION
  RAISERROR (@ErrorMessage, 16,1)
END CATCH
GO

--SELECT * FROM tblPaper
--SELECT * FROM tblAuthorPaper
/*

SELECT * 
FROM tblParticipant, tblAuthor, tblAuthorPaper, tblPaper
WHERE tblParticipant.ParticipantID=tblAuthor.ParticipantID AND
      tblAuthor.ParticipantID=tblAuthorPaper.AuthorID AND
      tblPaper.PaperID=tblAuthorPaper.PaperID
*/

SET NOCOUNT OFF