USE AdventureWorks;

CREATE ROUTE ProjectDetailsRoute
WITH
   SERVICE_NAME = 'http://schemas.helion.pl/prosqlserver/ProjectDetailsService',
   ADDRESS = 'TCP://peiriantprawf:4022';
GO

CREATE XML SCHEMA COLLECTION [http://schemas.helion.pl/prosqlserver/ProjectRequestSchema]
AS N'<?xml version="1.0" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
   <xs:element name="projectRequest">
      <xs:complexType>
         <xs:sequence minOccurs="1" maxOccurs="1">
            <xs:element name="email" type="xs:string" />
            <xs:element name="startTime" type="xs:dateTime" />
            <xs:element name="hours" type="xs:integer" />
         </xs:sequence>
      </xs:complexType>
   </xs:element>
</xs:schema>';

CREATE XML SCHEMA COLLECTION [http://schemas.helion.pl/prosqlserver/ProjectResponseSchema]
AS N'<?xml version="1.0" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
   <xs:element name="projectResponse">
      <xs:complexType>
         <xs:sequence minOccurs="1" maxOccurs="1">
            <xs:element name="email" type="xs:string" />
            <xs:element name="startTime" type="xs:dateTime" />
            <xs:element name="activeProjects" type="xs:integer" />
         </xs:sequence>
      </xs:complexType>
   </xs:element>
</xs:schema>';

CREATE MESSAGE TYPE [http://schemas.helion.pl/prosqlserver/ProjectRequestMessage]
VALIDATION = VALID_XML WITH SCHEMA COLLECTION [http://schemas.helion.pl/prosqlserver/ProjectRequestSchema];

CREATE MESSAGE TYPE [http://schemas.helion.pl/prosqlserver/ProjectResponseMessage]
VALIDATION = VALID_XML WITH SCHEMA COLLECTION [http://schemas.helion.pl/prosqlserver/ProjectResponseSchema];

CREATE CONTRACT [http://schemas.helion.pl/prosqlserver/ProjectServiceContract]
(
   [http://schemas.helion.pl/prosqlserver/ProjectRequestMessage] SENT BY INITIATOR,
   [http://schemas.helion.pl/prosqlserver/ProjectResponseMessage] SENT BY TARGET
);
GO

CREATE ROUTE ProjectDetailsRoute
WITH
   SERVICE_NAME = 'http://schemas.helion.pl/prosqlserver/ProjectDetailsService',
   ADDRESS = 'TCP://peiriantprawf:4022';
GO

CREATE XML SCHEMA COLLECTION [http://schemas.helion.pl/prosqlserver/EmployeeRequestSchema]
AS N'<?xml version="1.0" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
   <xs:element name="employeeRequest">
      <xs:complexType>
         <xs:sequence minOccurs="1" maxOccurs="1">
            <xs:element name="id" type="xs:integer" />
            <xs:element name="hours" type="xs:integer" />
         </xs:sequence>
      </xs:complexType>
   </xs:element>
</xs:schema>';

CREATE XML SCHEMA COLLECTION [http://schemas.helion.pl/prosqlserver/EmployeeResponseSchema]
AS N'<?xml version="1.0" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
   <xs:element name="employeeResponse">
      <xs:complexType>
         <xs:sequence minOccurs="1" maxOccurs="1">
            <xs:element name="id" type="xs:integer" />
            <xs:element name="hoursVacation" type="xs:integer" />
         </xs:sequence>
      </xs:complexType>
   </xs:element>
</xs:schema>';

CREATE MESSAGE TYPE [http://schemas.helion.pl/prosqlserver/EmployeeRequestMessage]
VALIDATION = VALID_XML WITH SCHEMA COLLECTION [http://schemas.helion.pl/prosqlserver/EmployeeRequestSchema];

CREATE MESSAGE TYPE [http://schemas.helion.pl/prosqlserver/EmployeeResponseMessage]
VALIDATION = VALID_XML WITH SCHEMA COLLECTION [http://schemas.helion.pl/prosqlserver/EmployeeResponseSchema];

CREATE CONTRACT [http://schemas.helion.pl/prosqlserver/EmployeeServiceContract]
(
   [http://schemas.helion.pl/prosqlserver/EmployeeRequestMessage] SENT BY INITIATOR,
   [http://schemas.helion.pl/prosqlserver/EmployeeResponseMessage] SENT BY TARGET
);
GO

CREATE PROCEDURE usp_GetHoursVacation
AS
DECLARE @msgBody      XML(
           [http://schemas.helion.pl/prosqlserver/EmployeeRequestSchema]),
        @response     XML(
           [http://schemas.helion.pl/prosqlserver/EmployeeResponseSchema]),
        @convID       uniqueidentifier,
        @empID        int,
        @hours        int,
        @hoursTaken   int,
        @totalHours   int,
        @msgType      nvarchar(256),
        @respText     nvarchar(1000);

DECLARE @msgTable TABLE
(
   message_body        varbinary(max),
   conversation_handle uniqueidentifier,
   message_type_name   nvarchar(256)
);
BEGIN
   WAITFOR
   (
      RECEIVE TOP (1) message_body, conversation_handle, message_type_name
      FROM EmployeeDetailsQueue INTO @msgTable
   ), TIMEOUT 2000;
   
   SET @msgBody = (SELECT TOP (1) CAST(message_body AS XML) FROM @msgTable);
   SET @convID = (SELECT TOP (1) conversation_handle FROM @msgTable);
   SET @msgType = (SELECT TOP (1) message_type_name FROM @msgTable);

   IF @msgType = 'http://schemas.helion.pl/prosqlserver/EmployeeRequestMessage'
   BEGIN
      SET @empID = @msgBody.value('data(//id)[1]', 'int');
      SET @hours = @msgBody.value('data(//id)[1]', 'int');
      SET @hoursTaken = (SELECT VacationHours FROM HumanResources.Employee
                         WHERE EmployeeID = @empID);
      SET @totalHours = @hoursTaken + @hours;
      SET @respText = N'<?xml version="1.0"?>
<employeeResponse>
   <id>' + CAST(@empID AS nvarchar) + '</id>
   <hoursVacation>' + CAST(@totalHours AS nvarchar) + '</hoursVacation>
</employeeResponse>';

      SET @response = CAST(@respText AS XML);
      SEND ON CONVERSATION @convID
         MESSAGE TYPE [http://schemas.helion.pl/prosqlserver/EmployeeResponseMessage]
         (@response);

      END CONVERSATION @convID;
   END;
END;
GO

CREATE QUEUE EmployeeDetailsQueue
WITH
   STATUS = ON,
   RETENTION = OFF,
   ACTIVATION
   (
      STATUS = ON,
      PROCEDURE_NAME = usp_GetHoursVacation,
      MAX_QUEUE_READERS = 5,
      EXECUTE AS SELF
   );

CREATE SERVICE [http://schemas.helion.pl/prosqlserver/EmployeeDetailsService]
ON QUEUE EmployeeDetailsQueue
(
   [http://schemas.helion.pl/prosqlserver/EmployeeServiceContract]
);
GO

CREATE XML SCHEMA COLLECTION [http://schemas.helion.pl/prosqlserver/VacationRequestSchema]
AS N'<?xml version="1.0" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
   <xs:element name="vacationRequest">
      <xs:complexType>
         <xs:sequence minOccurs="1" maxOccurs="1">
            <xs:element name="employeeId" type="xs:integer" />
            <xs:element name="email" type="xs:string" />
            <xs:element name="startTime" type="xs:dateTime" />
            <xs:element name="hours" type="xs:integer" />
         </xs:sequence>
      </xs:complexType>
   </xs:element>
</xs:schema>';

CREATE MESSAGE TYPE [http://schemas.helion.pl/prosqlserver/VacationRequest]
VALIDATION = VALID_XML WITH SCHEMA COLLECTION [http://schemas.helion.pl/prosqlserver/VacationRequestSchema]

CREATE CONTRACT [http://schemas.helion.pl/prosqlserver/VacationRequestContract]
(
   [http://schemas.helion.pl/prosqlserver/VacationRequest] SENT BY INITIATOR
);
GO

CREATE PROCEDURE usp_ProcessVacationRequest
AS
DECLARE @msgBody         XML(
           [http://schemas.helion.pl/prosqlserver/VacationRequestSchema]),
        @empRequest      XML(
           [http://schemas.helion.pl/prosqlserver/EmployeeRequestSchema]),
        @projRequest     XML(
           [http://schemas.helion.pl/prosqlserver/ProjectRequestSchema]),
        @empRequestBody  nvarchar(1000),
        @projRequestBody nvarchar(1000),
        @msgType         nvarchar(256),
        @convID          uniqueidentifier,
        @empConvID       uniqueidentifier,
        @projConvID      uniqueidentifier,
        @email           varchar(50),
        @employeeID      int,
        @hours           int,
        @startTime       DateTime;

DECLARE @msgTable TABLE
(
   message_body          varbinary(max),
   conversation_handle   uniqueidentifier,
   message_type_name     nvarchar(256)
);

BEGIN
   WAITFOR
   (
      RECEIVE TOP (1) message_body, conversation_handle, message_type_name
      FROM VacationRequestQueue INTO @msgTable
   ), TIMEOUT 2000;
   
   SET @msgBody = (SELECT TOP (1) CAST(message_body AS XML) FROM @msgTable);
   SET @convID = (SELECT TOP (1) conversation_handle FROM @msgTable);
   SET @msgType = (SELECT TOP (1) message_type_name FROM @msgTable);
   END CONVERSATION @convID;

   IF @msgType = 'http://schemas.helion.pl/prosqlserver/VacationRequest'
   BEGIN
      SET @email = @msgBody.value('data(//email)[1]', 'varchar(50)');
      SET @hours = @msgBody.value('data(//hours)[1]', 'int');
      SET @startTime = @msgBody.value('data(//startTime)[1]', 'datetime');
      SET @employeeID = @msgBody.value('data(//employeeId)[1]', 'int');

      SET @empRequestBody = N'<?xml version="1.0"?><employeeRequest>
   <id>' + CAST(@employeeID AS varchar) + '</id>
   <hours>' + CAST(@hours AS varchar) + '</hours>
</employeeRequest>';
      SET @empRequest = CAST(@empRequestBody AS XML)

      SET @projRequestBody = N'<projectRequest>
   <email>' + @email + '</email>
   <startTime>' + CONVERT(nvarchar, @startTime, 126) + '+00:00</startTime>
   <hours>' + CAST(@hours AS varchar) + '</hours>
</projectRequest>';
      SET @projRequest = CAST(@projRequestBody AS XML)

      BEGIN DIALOG CONVERSATION @empConvID
         FROM SERVICE [http://schemas.helion.pl/prosqlserver/EmployeeProjectDetailsService]
         TO SERVICE 'http://schemas.helion.pl/prosqlserver/EmployeeDetailsService'
         ON CONTRACT [http://schemas.helion.pl/prosqlserver/EmployeeServiceContract];

      SEND ON CONVERSATION @empConvID
         MESSAGE TYPE [http://schemas.helion.pl/prosqlserver/EmployeeRequestMessage]
         (@empRequest);

      BEGIN DIALOG CONVERSATION @projConvID
         FROM SERVICE [http://schemas.helion.pl/prosqlserver/EmployeeProjectDetailsService]
         TO SERVICE 'http://schemas.helion.pl/prosqlserver/ProjectDetailsService'
         ON CONTRACT [http://schemas.helion.pl/prosqlserver/ProjectServiceContract]
         WITH RELATED_CONVERSATION = @empConvID, ENCRYPTION=OFF;

      SEND ON CONVERSATION @projConvID
         MESSAGE TYPE [http://schemas.helion.pl/prosqlserver/ProjectRequestMessage]
         (@projRequest);
   END
END;
GO

CREATE QUEUE EmployeeProjectDetailsQueue
WITH STATUS = ON, RETENTION = OFF;

CREATE SERVICE [http://schemas.helion.pl/prosqlserver/EmployeeProjectDetailsService]
ON QUEUE EmployeeProjectDetailsQueue
(
   [http://schemas.helion.pl/prosqlserver/EmployeeServiceContract],
   [http://schemas.helion.pl/prosqlserver/ProjectServiceContract]
);
GO

CREATE QUEUE VacationRequestQueue
WITH
   STATUS = ON,
   RETENTION = OFF,
   ACTIVATION
   (
      STATUS = ON,
      PROCEDURE_NAME = usp_ProcessVacationRequest,
      MAX_QUEUE_READERS = 5,
      EXECUTE AS SELF
   );

CREATE SERVICE [http://schemas.helion.pl/prosqlserver/VacationRequestProcessorService]
ON QUEUE VacationRequestQueue
(
   [http://schemas.helion.pl/prosqlserver/VacationRequestContract]
);
GO

CREATE PROCEDURE usp_ReadResponseMessages
AS
DECLARE @empMsgBody   XML([http://schemas.helion.pl/prosqlserver/EmployeeResponseSchema]),
        @projMsgBody  XML([http://schemas.helion.pl/prosqlserver/ProjectResponseSchema]),
        @groupId      uniqueidentifier,
        @empConvId    uniqueidentifier,
        @projConvId   uniqueidentifier,
        @activeProj   int,
        @hours        int,
        @empId        int,
        @email        nvarchar(50),
        @startTime    datetime;

DECLARE @msgTable TABLE
(
   message_body         varbinary(max),
   message_type_name    nvarchar(256),
   conversation_handle  uniqueidentifier
);
BEGIN
   WAITFOR
   (
      GET CONVERSATION GROUP @groupID
      FROM EmployeeProjectDetailsQueue
   ), TIMEOUT 500;
   WHILE @groupID IS NOT NULL
   BEGIN
      WAITFOR
      (
         RECEIVE message_body, message_type_name, conversation_handle
         FROM EmployeeProjectDetailsQueue INTO @msgTable
      ), TIMEOUT 2000;

      IF (SELECT COUNT(*) FROM @msgTable) > 0
      BEGIN
         SET @empMsgBody = (SELECT TOP (1) CAST(message_body AS XML)
                            FROM @msgTable
                            WHERE message_type_name = 'http://schemas.helion.pl/prosqlserver/EmployeeResponseMessage');
         SET @empConvID = (SELECT TOP (1) conversation_handle FROM @msgTable
                           WHERE message_type_name = 'http://schemas.helion.pl/prosqlserver/EmployeeResponseMessage');
         SET @hours = @empMsgBody.value('data(//hoursVacation)[1]', 'int');
         SET @empId = @empMsgBody.value('data(//id)[1]', 'int');

         SET @projMsgBody = (SELECT TOP (1) CAST(message_body AS XML)
                             FROM @msgTable
                             WHERE message_type_name = 'http://schemas.helion.pl/prosqlserver/ProjectResponseMessage');
         SET @projConvID = (SELECT TOP (1) conversation_handle FROM @msgTable
                            WHERE message_type_name = 'http://schemas.helion.pl/prosqlserver/ProjectResponseMessage');
         SET @activeProj = @projMsgBody.value('data(//activeProjects)[1]', 'int');
         SET @email = @projMsgBody.value('data(//email)[1]', 'varchar');
         SET @startTime = @projMsgBody.value('data(//startTime)[1]', 'datetime');

         IF @hours > 160
            EXEC msdb.dbo.sp_send_dbmail
               @profile_name = 'Default Profile',
               @recipients = @email,
               @subject = 'Wniosek urlopowy',
               @body = Twój wniosek o urlop został odrzucony, ponieważ przysługująca Ci liczba godzin urlopu jest mniejsza niż wnioskowana liczba godzin urlopu.';
         ELSE IF @startTime < DATEADD(Week, 1, GETDATE())
            EXEC msdb.dbo.sp_send_dbmail
               @profile_name = 'Default Profile',
               @recipients = @email,
               @subject = 'Wniosek urlopowy',
               @body = 'Twój wniosek o urlop został odrzucony, ponieważ złożyłeś go zbyt późno. Wnioski urlopowe należy składać najpóźniej na tydzień przed początkiem wnioskowanego urlopu.';
         ELSE IF @activeProj > 1
            EXEC msdb.dbo.sp_send_dbmail
               @profile_name = 'Default Profile',
               @recipients = @email,
               @subject = 'Wniosek urlopowy',
               @body = 'Twój wniosek o urlop został odrzucony, ponieważ w trakcie wnioskowanego urlopu będziesz uczestniczył w więcej niż jednym projekcie.';
         ELSE
            BEGIN
               UPDATE HumanResources.Employee
                  SET VacationHours = @hours
                  WHERE EmployeeID = @empId;
               EXEC msdb.dbo.sp_send_dbmail
                  @profile_name = 'Default Profile',
                  @recipients = @email,
                  @subject = 'Wniosek urlopowy',
                  @body = 'Twój wniosek urlopowy został zaakceptowany.';
            END

         END CONVERSATION @empConvID;
         END CONVERSATION @projConvID;
      END;

      WAITFOR
      (
         GET CONVERSATION GROUP @groupID
         FROM EmployeeProjectDetailsQueue
      ), TIMEOUT 500;
   END;
END;
GO

CREATE USER projUser FOR LOGIN [JulianSkinner\Julian];
GO

CREATE CERTIFICATE projUserCert
AUTHORIZATION projUser
FROM FILE = 'C:\Helion\ProSqlServer\Rozdzial_12\projUserCert.cer';
GO

GRANT CONNECT TO projUser;
GRANT SEND ON SERVICE::[http://schemas.helion.pl/prosqlserver/EmployeeProjectDetailsService]
TO projUser;

-- Jeśli klucz główny dla bazy AdventureWorks jeszcze nie istnieje, należy odkomentować poniższe wiersze.
-- Trzeba będzie również zmienić hasło.
--CREATE MASTER KEY ENCRYPTION BY PASSWORD = '45Gme*3^&fwu';
--GO

CREATE CERTIFICATE awUserCert
WITH SUBJECT = 'ecspi.julianskinner.local',
     START_DATE = '01/01/2005',
     EXPIRY_DATE = '01/01/2006'

BACKUP CERTIFICATE awUserCert TO FILE = 'C:\Helion\ProSqlServer\Rozdzial_12\awUserCert.cer';

CREATE REMOTE SERVICE BINDING ProjectDetailsServiceBinding
TO SERVICE 'http://schemas.helion.pl/prosqlserver/ProjectDetailsService'
WITH USER = projUser;
GO

CREATE SERVICE [http://schemas.helion.pl/prosqlserver/VacationRequestInitiatorService]
ON QUEUE VacationRequestQueue
(
   [http://schemas.helion.pl/prosqlserver/VacationRequestContract]
);
GO

CREATE PROCEDURE usp_RequestVacation
   @employeeId int,
   @email varchar(50),
   @hours int,
   @startDate varchar(50)
AS
DECLARE @dialogHandle uniqueidentifier,
        @body         nvarchar(1000),
        @msg          XML,
        @date         nvarchar(100)
BEGIN
   SET @body = N'<?xml version="1.0"?>
<vacationRequest>
   <employeeId>' + CAST(@employeeID AS varchar) + '</employeeId>
   <email>' + @email + '</email>
   <startTime>' + @startDate + '</startTime>
   <hours>' + CAST(@hours AS nvarchar) + '</hours>
</vacationRequest>';
   SET @msg = CAST(@body AS XML)

   BEGIN DIALOG CONVERSATION @dialogHandle
      FROM SERVICE [http://schemas.helion.pl/prosqlserver/VacationRequestInitiatorService]
      TO SERVICE 'http://schemas.helion.pl/prosqlserver/VacationRequestProcessorService'
      ON CONTRACT [http://schemas.helion.pl/prosqlserver/VacationRequestContract];

   SEND ON CONVERSATION @dialogHandle
      MESSAGE TYPE [http://schemas.helion.pl/prosqlserver/VacationRequest]
      (@msg);
   END CONVERSATION @dialogHandle;
END;
