--LIST-PAYROLL-DATES---------------------------------------------------------------------------------				

CREATE TABLE employee_bc_payrollperiod
				(rec_id int IDENTITY(1,1) PRIMARY KEY NOT NULL,payrolldate date NOT NULL);
GO

--USED-WHEN-CREATING-EMAIL-NOTIFICATION------------------------------------------------------------------------------------				

CREATE TABLE employee_pto_document_conversation
				(doc_key int IDENTITY(1,1) PRIMARY KEY NOT NULL,doc_type varchar(150) NOT NULL,
				doc_fk varchar(150) NOT NULL,doc_created datetime2(6) NOT NULL,doc_status varchar(150) NOT NULL,
				doc_converted datetime2(6),doc_conversion_attempts int,doc_email_address varchar(150) NOT NULL,
				doc_email_address_from varchar(150),doc_email_subject varchar(150),doc_email_body varchar(MAX),
				doc_email_attachment varchar(MAX),doc_email_from varchar(150));
GO

--NO-DATA-FIGURE-OUT-WHY---------------------------------------------------------------------------------				 

CREATE TABLE employee_pto_accrual
				(rec_id int PRIMARY KEY NOT NULL,accrual_date date NOT NULL,
				accrued_amount numeric(13,2));
GO

---USED-TO-CALCULATE-ACCRUAL--------------------------------------------------------------------------------				 
				    
CREATE TABLE employee_pto_accrual_hist
				(rec_id int IDENTITY(1,1) PRIMARY KEY NOT NULL,
				 run_date date NOT NULL,accrual_date date NOT NULL,emp_id int NOT NULL,
				 accrued_vacation_hours decimal(8,5),accrual_max_reached BIT);
GO

--SET-ACCRUAL-AMOUNT-USED-TO-RUN-ACCRUAL--------------------------------------------------------------------------------				 

CREATE TABLE employee_pto_accrual_info
				(accrual_id int IDENTITY(1,1) PRIMARY KEY NOT NULL,accrual_desc varchar(MAX) NOT NULL,
				 accrual_rate decimal(8,5),accrual_max decimal(5,2));
GO

--EMPLOYEE-PTO-BALANCE-TRACKER---------------------------------------------------------------------------------

CREATE TABLE employee_pto_balance
				(emp_id int NOT NULL,pto_year int NOT NULL,
				 starting_holiday_hours int,starting_vacation_hours decimal(8,5),submitted_not_approved_holiday_hours int,submitted_not_approved_vacation_hours int,
				 approved_holiday_hours int,approved_vacation_hours int,starting_sick_hours int,approved_sick_hours int,submitted_not_approved_sick_hours int,
				 accrued_vacation_hours decimal (8,5),PRIMARY KEY (emp_id,pto_year));
GO

--PTO-DAYS-OF-THE-WEEK-LIST---------------------------------------------------------------------------------	

CREATE TABLE employee_pto_dow
				(dow_id int PRIMARY KEY NOT NULL,dow_name varchar(50) NOT NULL);
				
GO				
				
---PTO-EMPLOYEE-ACCRUAL-RATE-------------------------------------------------------------------------------------

CREATE TABLE employee_pto_emp_accrual_rate
	(rec_id int IDENTITY(1,1) NOT NULL,emp_id int NOT NULL,
	accrual_id int NOT NULL,accrual_effective_from date NOT NULL,
	accrual_effective_to date NULL);

GO					

----------------------------------------------------------------------------------

CREATE TABLE employee_pto_emp_rate(
	rec_id int PRIMARY KEY NOT NULL,
	emp_id int NOT NULL,
	emp_rate int NULL);
	
GO	

--VIEW-HOLIDAY-LIST---------------------------------------------------------------------------------				 

CREATE TABLE employee_pto_holiday
				(rec_id int IDENTITY(1,1) PRIMARY KEY NOT NULL,holiday_date date NOT NULL,
				holiday_desc varchar(MAX) NOT NULL);
GO


--VIEW-LIST-OF-PTO-REQUEST---------------------------------------------------------------------------------				 

CREATE TABLE employee_pto_request
				(rec_id int IDENTITY(1,1) PRIMARY KEY NOT NULL,resource_id int NOT NULL,approver_id int NOT NULL,
				request_date date NOT NULL,status_id int NOT NULL,approval_date date,
				rejection_date date,start_date date NOT NULL,end_date date NOT NULL,
				size int NOT NULL,total_hours int NOT NULL,pto_type int NOT NULL,start_date_dow int,
				end_date_dow int,comments varchar(MAX),approver_comments varchar(MAX),transferred_to_payroll BIT);
GO

--PTO-REQUEST-AMOUNT-FULL/HALF-DAY---------------------------------------------------------------------------------				

CREATE TABLE employee_pto_size
				(rec_id int IDENTITY(1,1) PRIMARY KEY NOT NULL,description varchar(MAX) NOT NULL,
				 hours int NOT NULL,short_desc varchar(MAX));
GO

--PTO-PROGRESS-STATUSES---------------------------------------------------------------------------------				 

CREATE TABLE employee_pto_status
				(rec_id int IDENTITY(1,1) PRIMARY KEY NOT NULL,description varchar(MAX) NOT NULL);
GO

--LAUNCHER-TABLE---------------------------------------------------------------------------------				 

CREATE TABLE employee_pto_temp_table
				(rec_id int PRIMARY KEY NOT NULL);
GO


--PTO-VACATION-TYPES---------------------------------------------------------------------------------	
				

CREATE TABLE employee_pto_type
				(rec_id int IDENTITY(1,1) PRIMARY KEY NOT NULL,description varchar(MAX) NOT NULL,
				 sequence int NOT NULL);
GO

--EMPLOYEE-JOB-ROLES---------------------------------------------------------------------------------	

CREATE TABLE employee_resource_role
				(recid int IDENTITY(1,1) PRIMARY KEY NOT NULL,roledesc varchar(50));
GO

--EMPLOYEE-JOB-TYPE---------------------------------------------------------------------------------	


CREATE TABLE employee_resource_type
				(recid int IDENTITY(1,1) PRIMARY KEY NOT NULL,typedesc varchar(50));
GO

--EMPLOYEE-TABLE-LIST---------------------------------------------------------------------------------				
				

CREATE TABLE employee_resources
				(resourceid int IDENTITY(1,1) PRIMARY KEY NOT NULL,resourcename varchar(80),
				 active BIT NOT NULL,fulltime BIT NOT NULL,internal BIT NOT NULL
				 ,extendername varchar(15),managerid int,
				 email varchar(150),manager BIT NOT NULL,typeid int,
				 roleid int,empid int);
GO


--INSERT-PROGRESS-STATUS-VALUES---------------------------------------------------------------------------------

INSERT INTO employee_pto_status (description) VALUES ('Created'),('Submitted'),('Approved'),('Rejected'),('Cancellation Pending'),('Cancelled');
GO	

--INSERT-DAYS-OF-THE-WEEK-LIST-SUN-SAT---------------------------------------------------------------------------------

INSERT INTO employee_pto_dow (dow_id,dow_name) VALUES (0,'Sunday'),(1,'Monday'),(2,'Tuesday'),(3,'Wednesday'),(4,'Thursday'),(5,'Friday'),(6,'Saturday');
GO


--INSERT-PTO-SIZE-VALUES---------------------------------------------------------------------------------

INSERT INTO employee_pto_size (description,hours,short_desc) VALUES ('Half Day - AM',4,'AM'),('Half Day - PM',4,'PM'),('Full Day',8,'All');
GO	

--INSERT-EMPLOYEE-ACCRUAL-RATE----------------------------------------------------------------------------------

INSERT INTO employee_pto_accrual_info (accrual_desc,accrual_rate,accrual_max) VALUES ('15 PTO days per year',5.0,120.00);
GO

--INSERT-INTO-PTO-TYPE----------------------------------------------------------------------------------------------

INSERT INTO employee_pto_type (description,sequence) VALUES ('Vacations',1),('Sick Time',3),('Jury Duty',999),('Floating Holiday',2),('Bereavement Leave',999),('Military Leave',999),('Personal Leave of Absence',999),('Paternal leave',999),('Maternity Leave',999),('Pay Day',9999),('Company Holiday',9999);
GO															

--MANAGER-LIST-COMMENT-OUT-IF-ALREADY-EXIST--------------------------------------------------------------------------------

CREATE VIEW manager AS
				SELECT employee_resources.resourceid,employee_resources.resourcename,
				employee_resources.extendername,employee_resources.managerid,
				manager.resourcename AS managername,manager.extendername AS managerextendername
				FROM employee_resources LEFT JOIN employee_resources manager ON manager.resourceid = employee_resources.managerid;
GO

-- ---------------------------------------------------------------------------------	

CREATE VIEW employee_pto_payrollperiod_accrual_info AS
SELECT a.rec_id,
    a.payrolldate,
	accrued=
        CASE
            WHEN b.accrual_date IS NULL THEN CAST(0 AS BIT)
            ELSE CAST(1 AS BIT)
        END,
    c.accrued_amount
   FROM employee_bc_payrollperiod a
     LEFT JOIN ( SELECT employee_pto_accrual_hist.accrual_date
           FROM employee_pto_accrual_hist
          GROUP BY employee_pto_accrual_hist.accrual_date) b ON a.payrolldate = b.accrual_date
     LEFT JOIN employee_pto_accrual c ON c.accrual_date = a.payrolldate
  WHERE YEAR(a.payrolldate) >= CAST(2016 AS FLOAT(53));
 GO 

-- ---------------------------------------------------------------------------------	
 


CREATE VIEW employee_pto_emp_current_accrual_info AS

 SELECT a.emp_id,
    a.starting_vacation_hours + a.accrued_vacation_hours - CAST(a.approved_vacation_hours AS numeric) AS emp_vac_balance,
    b.emp_rate * 100 / 2080 AS emp_rate
   FROM employee_pto_balance a
     JOIN employee_pto_emp_rate b ON a.emp_id = b.emp_id
     JOIN employee_resources c ON a.emp_id = c.resourceid
  WHERE c.active = 'true' AND c.internal = 'true' AND CAST(a.pto_year AS FLOAT(53)) = YEAR(GETDATE());
GO
  
--sp_create_email_notification-stored-procedure---------------------------------------------------------------------------------	
  

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Joanie Solorzano
-- Create date: 1/4/2019
-- Description:	Convert Procedure from postgresql to sql server
-- =============================================
CREATE PROCEDURE sp_create_email_notification 
	(@p_to_address varchar(150),@p_from_address varchar(150),@p_subject_text varchar(150),@p_body_text varchar(MAX),@p_errors varchar(2048) output)
AS
BEGIN
	DECLARE @w_errors varchar(2048);

	BEGIN

	IF (@w_errors <> '') 
		set @p_errors = '<Response>' + @w_errors + '</Response>';
	ELSE 
		set @p_errors = '';

		INSERT INTO employee_pto_document_conversation 
		(doc_type, doc_fk, doc_created, doc_status, doc_conversion_attempts, 
		doc_email_address, doc_email_address_from, doc_email_subject, doc_email_body, doc_email_attachment) 
		VALUES 
		('EMAIL', 0, CURRENT_TIMESTAMP, 'NEW', 0, @p_to_address, @p_from_address, @p_subject_text, @p_body_text, '');

	END;

END
GO

--sp_create_push_notification-stored-procedure---------------------------------------------------------------------------------	

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Joanie Solorzano
-- Create date: 1/4/2019
-- Description:	Convert Procedure from postgresql to sql server
-- =============================================
CREATE PROCEDURE sp_create_push_notification 
	(@p_notification_type varchar(150),@p_notification_userid varchar(150),@p_notification_groupid varchar(150),@p_notification_title varchar(150),
	@p_notification_body varchar(250),@p_userdata1 varchar(150),@p_longuserdata1 varchar(150),@p_manual_completion varchar(150),
	@p_errors varchar(2048) output)
AS
BEGIN
	DECLARE @w_errors varchar(2048);

BEGIN

  IF (@w_errors <> '')
    set @p_errors = '<Response>' + @w_errors + '</Response>';
  ELSE 
    set @p_errors = '';

	INSERT INTO catavolt_notification 
		(notification_type, notification_userid, notification_groupid, notification_title, notification_body, 
		userdata1, longuserdata1, manual_completion) 
	VALUES 
		(@p_notification_type, @p_notification_userid, @p_notification_groupid, @p_notification_title, @p_notification_body, 
		@p_userdata1, @p_longuserdata1, @p_manual_completion);


	END

END;
GO


--sp_pto_submit_request-stored-procedure---------------------------------------------------------------------------------	

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Joanie Solorzano
-- Create date: 1/4/2019
-- Description:	Convert Procedure from postgresql to sql server
-- =============================================


CREATE PROCEDURE sp_pto_submit_request
	(@p_request_id int,@p_submit_errors varchar(2048) OUTPUT)   
	
AS 
BEGIN 

DECLARE 
@w_allowable_negative_vacation_balance int,
@w_errors varchar(2048),
@w_resource_id int,
@w_approver_id int,
@w_status_id int,
@w_total_hours int,
@w_pto_type int,
@w_start_date date,
@w_end_date date,
@w_starting_holiday_hours int,
@w_submitted_not_approved_holiday_hours int, 
@w_approved_holiday_hours int,
@w_starting_vacation_hours numeric(8,5),
@w_accrued_vacation_hours decimal(8,5),   
@w_submitted_not_approved_vacation_hours int, 
@w_approved_vacation_hours int,
@w_starting_sick_hours int,
@w_submitted_not_approved_sick_hours int, 
@w_approved_sick_hours int,
@w_resource_name varchar(150),
@w_approver_email varchar(150),
@w_approver_extendername varchar(150),
@w_pto_description varchar(150),
@w_subject varchar(150),
@w_body varchar(MAX),
@w_convertedID varchar(MAX),
@w_pto_emails nvarchar(1),
@w_pto_notifications nvarchar(1);    

	BEGIN    
		set @w_allowable_negative_vacation_balance = 40;   
		 
		 
	--retrieve request details and balance details--    
	-- error checking:      
	--  * verify that current status is created     
	--  * verify that remaining balance is enough to cover this request    
	-- change status to submitted    
	-- update submitted_not_approved_hours    
	
	print 'starting sp_pto_submit_request'
	select @w_resource_id=resource_id, @w_approver_id=approver_id, @w_status_id=status_id, @w_total_hours=total_hours, @w_pto_type=pto_type, @w_start_date=start_date, @w_end_date=end_date 
	from employee_pto_request where rec_id = @p_request_id;    
	
	select @w_starting_holiday_hours=starting_holiday_hours, @w_submitted_not_approved_holiday_hours=submitted_not_approved_holiday_hours, @w_approved_holiday_hours=approved_holiday_hours, @w_starting_vacation_hours=starting_vacation_hours, @w_accrued_vacation_hours=accrued_vacation_hours, @w_submitted_not_approved_vacation_hours=submitted_not_approved_vacation_hours, @w_approved_vacation_hours=approved_vacation_hours,@w_starting_sick_hours=starting_sick_hours, @w_submitted_not_approved_sick_hours=submitted_not_approved_sick_hours, @w_approved_sick_hours=approved_sick_hours 
	from employee_pto_balance where emp_id = @w_resource_id and pto_year = YEAR(@w_start_date);    
	
	set @w_errors = ''; 
	print 'starting checking for errors'
	IF (@w_status_id <> 1) 
		BEGIN 
			set @w_errors = @w_errors + '<Message type=''error''><Text>This request cannot be submitted.</Text></Message>'; 
		END    
		
	IF(@w_pto_type = 1) /*-- vacation */ 
		BEGIN
		IF (@w_starting_vacation_hours + @w_accrued_vacation_hours - @w_approved_vacation_hours - @w_submitted_not_approved_vacation_hours + @w_allowable_negative_vacation_balance < @w_total_hours) 
			BEGIN  
				set @w_errors = @w_errors + '<Message type=''error''><Text>Your vacation balance is too low for this request.</Text></Message>'; 
			END
		END	 
	ELSE IF(@w_pto_type = 2) /* --sick-- */ 
		BEGIN
		IF (@w_starting_sick_hours - @w_approved_sick_hours - @w_submitted_not_approved_sick_hours < @w_total_hours)  
			BEGIN  
				set @w_errors = @w_errors + '<Message type=''error''><Text>Your sick day balance is too low for this request.</Text></Message>'; 
			END
		END	 
	ELSE IF(@w_pto_type = 4) /* --holiday-- */ 
		BEGIN
		IF (@w_starting_holiday_hours - @w_approved_holiday_hours - @w_submitted_not_approved_holiday_hours < @w_total_hours)  
			BEGIN	 
				set @w_errors = @w_errors + '<Message type=''error''><Text>Your floating Holiday balance is too low for this request.</Text></Message>'; 
			END 
		END

	IF (@w_errors <> '') 
		BEGIN 
			set @p_submit_errors = '<Response>' + @w_errors + '</Response>'; 
		END    
	ELSE 
		print 'no errors found so far, updating employee pto balance'
		update employee_pto_request set status_id = 2 where rec_id = @p_request_id; 
		IF(@w_pto_type = 1) /* --vacation-- */ 
			BEGIN 
				update employee_pto_balance set submitted_not_approved_vacation_hours = @w_submitted_not_approved_vacation_hours + @w_total_hours where emp_id = @w_resource_id and pto_year = YEAR(@w_start_date); 
			END 
		ELSE IF(@w_pto_type = 2) /* --sick-- */ 
			BEGIN 
				update employee_pto_balance set submitted_not_approved_sick_hours = @w_submitted_not_approved_sick_hours + @w_total_hours where emp_id = @w_resource_id and pto_year = YEAR(@w_start_date); 
			END 
		ELSE IF(@w_pto_type = 4) /* --holiday-- */ 
			BEGIN 
				update employee_pto_balance set submitted_not_approved_holiday_hours = @w_submitted_not_approved_holiday_hours + @w_total_hours where emp_id = @w_resource_id and pto_year = YEAR(@w_start_date); 
			END    
			
	
	/* -- notify approver that a request was submitted */  
	
	set @w_resource_name = (select resourcename from employee_resources where resourceid = @w_resource_id); 
	set @w_approver_email = (select email from employee_resources where resourceid = @w_approver_id); 
	set @w_pto_description = (select description from employee_pto_type where rec_id = @w_pto_type); 
	set @w_subject = 'A new PTO Request has been submitted for your approval'; 
	set @w_body = @w_resource_name + ' has requested ' + @w_pto_description +' from ' + convert(varchar(MAX),@w_start_date) +  ' to ' + convert(varchar(MAX),@w_end_date) + '. Please log on to http://www.catavolt.net/cvops to review this request.';    
	
		BEGIN 
		print 'create email notification' 
			
			--call stored procedure	 
			EXEC @w_errors = dbo.sp_create_email_notification @w_approver_email,'notifications@catavolt.com',@w_subject,@w_body,@w_errors output
			IF (@w_errors <> '') 
			BEGIN 
				set @p_submit_errors = '<Response>' + @w_errors + '</Response>'; 
			END   
		END    

		BEGIN 
			set @w_approver_extendername = (select extendername from employee_resources where resourceid = @w_approver_id); 
			set @w_body = @w_resource_name + ' has requested ' + @w_pto_description +' from ' + convert(varchar(MAX),@w_start_date) +  ' to ' + convert(varchar(MAX),@w_end_date) + '.';    
			set @w_convertedID = CAST(@p_request_id AS varchar(MAX));
			/* --perform sa.create_push_notification('PTO', @w_approver_extendername, NULL, @w_subject, @w_body, @p_request_id::text, 'submitted', '0', NULL); */    
			
			--call stored procedure
			print 'call sp_create_push_notification'	 
			EXEC @w_errors = dbo.sp_create_push_notification 'PTO',@w_approver_extendername,NULL,@w_subject,@w_body, @w_convertedID,'submitted','0',@w_errors output
			

			IF (@w_errors <> '') 
			BEGIN 
				set @p_submit_errors = '<Response>' + @w_errors + '</Response>'; 
			END 
			print 'end sp_pto_submit_request' 
		END 
	END 
END
GO


--sp_pto_create_request-stored-procedure---------------------------------------------------------------------------------	

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Joanie Solorzano
-- Create date: 1/4/2019
-- Description:	Converted Stored Proc from Postgesql to SQL Server
-- =============================================
CREATE PROCEDURE sp_pto_create_request
	(@p_current_user varchar(150),@p_request_date date,@p_pto_type int,@p_start_date date,@p_end_date date,@p_size int,@p_comments varchar(2048),@p_auto_submit BIT,@p_errors varchar(2048) OUTPUT)    
	
AS 
BEGIN 

DECLARE @w_errors VARCHAR(2048)
		,@w_resource_id INT
		,@w_approver_id INT
		,@w_overlap INT
		,@x_record DATE
		,@w_size_hours INT
		,@w_total_hours INT
		,@RowNum INT
		,@w_date DATE
		,@w_rec_id int
		,@w_holiday_date date
		,@w_holiday_desc varchar(150)
		,@w_pto_days INT
		,@w_workday INT
		,@w_date_dow INT
		,@w_start_date DATE
		,@w_end_date DATE
		,@w_submit_errors VARCHAR(2048)
		,@w_request_id INT
		,@i INT;

	BEGIN
		-- retrieve employee id and approver id based on passed in Extender Id 
		-- error checking: 
		--  * end date must be equal to or later than start date 
		--  * start and end date must be in same year 
		--  * requests for floating holiday must be for a full day 
		--  * must not already have a created or submitted or approved request that overlaps this request 
		--  * must not be a request for a single day that falls on a Catavolt holiday 
		-- calculate actual hours, taking into account weekends and holidays 
		-- write record to datbase 
		
		
		SELECT @w_resource_id = (
				SELECT resourceid
				FROM employee_resources
				WHERE extendername = @p_current_user
				);
		SELECT @w_approver_id = (
				SELECT managerid
				FROM employee_resources
				WHERE extendername = @p_current_user
				);
		SET @w_errors = '';
		IF (@p_start_date > @p_end_date)
		BEGIN
			SET @w_errors = @w_errors + '<Message type=''error''><Text>Start date must be before End date.</Text></Message>';
		END
		ELSE IF ((year(@p_start_date)) <> (year(@p_end_date)))
		BEGIN
			SET @w_errors = @w_errors + '<Message type=''error''><Text>Start date and End date must be in same year.</Text></Message>';
		END
		ELSE IF (
				(@p_pto_type = 4)
				AND (@p_size <> 3)
				)
		BEGIN
			SET @w_errors = @w_errors + '<Message type=''error''><Text>A floating Holiday must be taken as a full day.</Text></Message>';
		END
		SET @w_overlap = NULL;

		SELECT @w_overlap = count(*)
		FROM employee_pto_request
		WHERE (
				(
					(
						start_date BETWEEN @p_start_date
							AND @p_end_date
						)
					OR (
						end_date BETWEEN @p_start_date
							AND @p_end_date
						)
					)
				AND (
					status_id IN (
						1
						,2
						,3
						)
					)
				AND (resource_id = @w_resource_id)
				);

		IF (
				(@w_overlap IS NOT NULL)
				AND (@w_overlap > 0)
				)
		BEGIN
			SET @w_errors = @w_errors + '<Message type=''error''><Text>You already have an existing created, submitted or approved request that overlaps with this request</Text></Message>';
		END

		IF (@p_start_date = @p_end_date)
		BEGIN
			print 'Begin first loop'
			select @RowNum = count(*) from employee_pto_holiday;
			WHILE @RowNum > 0
			BEGIN
			select @w_rec_id=rec_id, @w_holiday_date=holiday_date, @w_holiday_desc=holiday_desc from employee_pto_holiday where rec_id=@RowNum
				IF @w_holiday_date = @p_start_date
					BEGIN 
						SET @w_errors = @w_errors + '<Message type=''error''><Text>Your request is for a Catavolt Holiday. No PTO request is needed.</Text></Message>';
					END
				set @RowNum = @RowNum - 1

			END


		END
			print 'end first loop'
		print @w_errors
		IF (@w_errors <> '')
		BEGIN
			SET @p_errors = '<Response>' + @w_errors + '</Response>';
		END
		ELSE
			BEGIN

				SET @p_errors = '';

				SET @w_start_date = @p_start_date;
				SET @w_end_date = @p_end_date;
				--if start date is in a weekend or on a holiday, change date to first workday after start date  
				SET @w_date = @p_start_date;
				SET @w_workday = 0;
				SET datefirst 1;
				print 'starting first while'
				WHILE (
				(@w_workday = 0)
				AND (@w_date <= @w_end_date)
				)
				BEGIN
					SELECT @w_date_dow = DATEPART(dw, @w_date);
					print @w_date_dow
					IF (
					(
						@w_date_dow BETWEEN 1
							AND 5
						)
					AND (
						@w_date NOT IN (
							SELECT holiday_date
							FROM employee_pto_holiday
							)
						)
					)
					BEGIN
						SET @w_workday = 1;
					END
				ELSE
					BEGIN
						SET @w_date = DATEADD(DAY, 1, @w_date);
					END
				END
				print 'ending first while'

				--if at this point w_workday is still 0 then we basically have an error because there are no workdays for this PTO request  
				IF (@w_workday = 0)
				BEGIN
					SET @w_errors = @w_errors + '<Message type=''error''><Text>There are no workdays in your requested PTO period.</Text></Message>';
				END
			ELSE
				BEGIN
					SET @w_start_date = @w_date;

					--if start date is in a weekend or on a holiday, change date to first workday after start date 
					SET @w_date = @w_end_date;
					SET @w_workday = 0;
					SET datefirst 1;
					print 'starting second whlie'
					WHILE (
					(@w_workday = 0)
					AND (@w_date >= @w_start_date)
					)
					BEGIN
						SET @w_date_dow = DATEPART(dw, @w_date);
						IF (
						(
						@w_date_dow BETWEEN 1
							AND 5
						)
						AND (
						@w_date NOT IN (
							SELECT holiday_date
							FROM employee_pto_holiday
							)
						)
					)
					BEGIN
						SET @w_workday = 1;
					END
					ELSE
						BEGIN
							SET @w_date = DATEADD(DAY, - 1, @w_date);
						END
					END
				print 'ending second while'
				--if at this point w_workday is still 0 then we basically have an error because there are no workdays for this PTO request 
				IF (@w_workday = 0)
				BEGIN
					SET @w_errors = @w_errors + '<Message type=''error''><Text>There are no workdays in your requested PTO period.</Text></Message>';
				END
			ELSE
				print 'calculate total days'
				--calculate total days  
				SET @w_pto_days = DATEDIFF(day, @w_start_date, @w_end_date) + 1;
				print @w_pto_days

				--set @w_pto_days = @w_end_date - @w_start_date + 1;
				--deduct weekends  
				SET @w_date = @w_start_date;
				print 'starting third while '
				WHILE (@w_date <= @w_end_date)
				BEGIN
				    SET datefirst 1;	 
					SET @w_date_dow = DATEPART(dw, @w_date);
					print cast(@w_date as varchar(25))

					IF (@w_date_dow = 7 OR @w_date_dow = 6)
					BEGIN
						SET @w_pto_days = @w_pto_days - 1;
					END 
					SET @w_date = DATEADD(DAY, + 1, @w_date);
				END
				print 'ending third while '
				--deduct holidays  
				DECLARE x CURSOR
				FOR
				SELECT holiday_date
				FROM employee_pto_holiday;

				OPEN x

				FETCH NEXT FROM x INTO @x_record
				print 'starting second cursor'
				WHILE @@fetch_status = 0
				BEGIN
					IF @x_record BETWEEN @w_start_date
					AND @w_end_date 
					begin 
						SET @w_pto_days = @w_pto_days - 1;
					end

					FETCH NEXT FROM x INTO @x_record
				END

				CLOSE x

				DEALLOCATE x
				print 'ending second cursor'
			
				--calculate total hours--  \
				SELECT @w_size_hours = hours
				FROM employee_pto_size
				WHERE rec_id = @p_size;

				print 'insert into pto request'
				SET @w_total_hours = @w_size_hours * @w_pto_days;
				print @w_total_hours
				BEGIN
					INSERT INTO employee_pto_request (
					resource_id
					,approver_id
					,request_date
					,status_id
					,approval_date
					,rejection_date
					,start_date
					,start_date_dow
					,end_date
					,end_date_dow
					,size
					,total_hours
					,pto_type
					,comments
					,approver_comments
					)
					VALUES (
					@w_resource_id
					,@w_approver_id
					,@p_request_date
					,1
					,NULL
					,NULL
					,@w_start_date
					,(
					SELECT DATEPART(dw, @w_start_date)
					)
					,@w_end_date
					,(
					SELECT DATEPART(dw, @w_end_date)
					)
					,@p_size
					,@w_total_hours
					,@p_pto_type
					,@p_comments
					,NULL
					)

					SET @i = SCOPE_IDENTITY()
				END

				print @i
				SELECT @w_request_id = @i

				/* if auto-submit selected, submit request and pass any errors back to user */
				IF (@p_auto_submit = 1)
					SET @w_submit_errors = '';

				--call stored procedure	 
				EXEC @w_submit_errors = sp_pto_submit_request @w_request_id
				,@w_submit_errors OUTPUT

				/*select sp_pto_submit_request(@w_request_id,@w_submit_errors) into @w_submit_errors;*/
				print @w_submit_errors
				IF (@w_submit_errors <> '' and @w_submit_errors <> 0)
					BEGIN
						print 'deleting request record'
						SET @w_submit_errors = replace(@w_submit_errors, '<Response>', '');
						SET @w_submit_errors = replace(@w_submit_errors, '</Response>', '');
						SET @w_errors = @w_submit_errors;

						/* back out the created request */
						DELETE FROM employee_pto_request WHERE rec_id = @w_request_id;
					END
			END
		END
		END
	IF (@w_errors <> '')
	BEGIN
		SET @p_errors = '<Response>' + @w_errors + '</Response>';
	END
END;
GO

--sp_pto_change_request-stored-procedure---------------------------------------------------------------------------------	

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Joanie Solorzano
-- Create date: 1/4/2019
-- Description:	Converted Stored Procedure from postgresql to sql server
-- =============================================
CREATE PROCEDURE sp_pto_change_request 
	(
	@p_current_user varchar(2048),
	@p_request_id int,
	@p_request_date date,
	@p_pto_type int,
	@p_start_date date,
	@p_end_date date,
	@p_size int,
	@p_comments varchar(MAX),
	@p_errors varchar(2048) OUTPUT
	)
AS
BEGIN


DECLARE 
	@w_allowable_negative_vacation_balance int,
	@w_errors varchar(2048),
	@w_resource_id int,
	@w_approver_id int,
	@w_overlap int,
	@x_record date,
	@w_size_hours int,
	@w_total_hours int,
	@w_date date,
	@w_pto_days int,
	@w_workday int,
	@w_date_dow int,
	@w_start_date date,
	@w_end_date date,
	@w_request_status int,
	@w_starting_holiday_hours int,
	@w_submitted_not_approved_holiday_hours int, 
	@w_approved_holiday_hours int,
	@w_starting_vacation_hours numeric(8,5),
	@w_accrued_vacation_hours numeric(8,5),
	@w_submitted_not_approved_vacation_hours int, 
	@w_approved_vacation_hours int,
	@w_starting_sick_hours int,
	@w_submitted_not_approved_sick_hours int, 
	@w_approved_sick_hours int,
	@w_original_starting_holiday_hours int,
	@w_original_submitted_not_approved_holiday_hours int, 
	@w_original_approved_holiday_hours int,
	@w_original_starting_vacation_hours numeric(8,5),
	@w_original_accrued_vacation_hours numeric(8,5),
	@w_original_submitted_not_approved_vacation_hours int, 
	@w_original_approved_vacation_hours int,
	@w_original_starting_sick_hours int,
	@w_original_submitted_not_approved_sick_hours int, 
	@w_original_approved_sick_hours int,
	@w_original_total_hours int,
	@w_original_pto_type int,
	@w_original_start_date date,
	@new_submitted_not_approved_vacation_hours int,
	@new_submitted_not_approved_sick_hours integer,
	@new_submitted_not_approved_holiday_hours integer;

BEGIN

set @w_allowable_negative_vacation_balance = 40;

/*
-- retrieve employee id and approver id based on passed in Extender Id
-- retrieve current request details and balance details
-- error checking:
--  * status of request being changed must be created or submitted
--  * end date must be equal to or later than start date
--  * start and end date must be in the same year
--  * requests for floating holidays must be for the full day
--  * must not already have a created or submitted or approved request that overlaps this request (except for the one being changed)
--  * must not be a request for a single day that falls on a Catavolt holiday
-- calculate actual hours, taking into account weekends and holidays
-- update record in database
-- update balance in database  */


print 'Start Change Request'
set @w_resource_id = (select resourceid from employee_resources where extendername = @p_current_user);
set @w_approver_id = (select managerid from employee_resources where extendername = @p_current_user);

select @w_original_total_hours=total_hours, @w_original_pto_type=pto_type, @w_original_start_date=start_date 
from employee_pto_request
where rec_id = @p_request_id;

select @w_original_starting_holiday_hours=starting_holiday_hours, @w_original_submitted_not_approved_holiday_hours=submitted_not_approved_holiday_hours, @w_original_approved_holiday_hours=approved_holiday_hours, 
       @w_original_starting_vacation_hours=starting_vacation_hours, @w_original_accrued_vacation_hours=accrued_vacation_hours, @w_original_submitted_not_approved_vacation_hours=submitted_not_approved_vacation_hours, @w_original_approved_vacation_hours=approved_vacation_hours,
       @w_original_starting_sick_hours=starting_sick_hours, @w_original_submitted_not_approved_sick_hours=submitted_not_approved_sick_hours, @w_original_approved_sick_hours=approved_sick_hours
from employee_pto_balance
where emp_id = @w_resource_id and pto_year = YEAR(@w_original_start_date);


set @w_errors = '';

set @w_request_status = (select status_id from employee_pto_request where rec_id = @p_request_id);
if (@w_request_status not in (1,2)) 
	set @w_errors = @w_errors + '<Message type=''error''><Text>You cannot change this request.</Text></Message>';

if (@p_start_date > @p_end_date) 
	set @w_errors = @w_errors + '<Message type=''error''><Text>Start date must be before End date.</Text></Message>';

if ((@p_pto_type = 4) and (@p_size <> 3)) 
	set @w_errors = @w_errors + '<Message type=''error''><Text>A floating Holiday must be taken as a full day.</Text></Message>';


if (YEAR(@p_start_date)) > YEAR(@p_end_date)
	set @w_errors = @w_errors + '<Message type=''error''><Text>End date must be in the same calendar year as Start date.</Text></Message>';

set @w_overlap = null;
set @w_overlap = (select count(*) from employee_pto_request 
	     where (((start_date between @p_start_date and @p_end_date) 
		or (end_date between @p_start_date and @p_end_date))
	       and (status_id in (1,2,3))
	       and (resource_id = @w_resource_id)
	       and (rec_id <> @p_request_id)));


if ((@w_overlap is not null) and (@w_overlap > 0)) 
	set @w_errors = @w_errors + '<Message type=''error''><Text>You already have an existing created, submitted or approved request that overlaps with this request</Text></Message>';

print 'Start first cursor'
if (@p_start_date = @p_end_date)
	BEGIN
	declare x cursor for select holiday_date from employee_pto_holiday 
	OPEN x fetch next from x into @x_record while @@fetch_status=0 
		BEGIN IF @x_record = @p_start_date set @w_errors = @w_errors + '<Message type=''error''><Text>Your request is for a Catavolt Holiday. No PTO request is needed.</Text></Message>'; 
		FETCH NEXT FROM x INTO @x_record 
		END 
	close x; 
	deallocate x;  
	END 
	


  IF (@w_errors <> '') 
    set @p_errors = '<Response>' + @w_errors + '</Response>';
  ELSE 
	set @p_errors = '';
	set @w_start_date = @p_start_date;
	set @w_end_date = @p_end_date;
	
	/* -- if start date is in a weekend or on a holiday, change date to first workday after start date */
	print 'first while loop'
	set @w_date = @p_start_date;
	set @w_workday = 0;
	while (
			(@w_workday = 0) 
			AND (@w_date <= @w_end_date)
		  )
		BEGIN
		select @w_date_dow = DATEPART(dw, @w_date);
		/* -- insert into sa.debug(timestamp,userid,comment) values(now(),'heymchr','w_date ' || w_date || ' - DOW ' || w_date_dow); */

		if ((@w_date_dow between 1 and 5) and (@w_date not in (select holiday_date from employee_pto_holiday))) 
			BEGIN
				set @w_workday = 1;
			END
		ELSE 
			BEGIN
				set @w_date = DATEADD(DAY,1,@w_date);
			END
		END		
		print 'End First Loop'
	/* -- if at this point w_workday is still 0 then we basically have an error because there are no workdays for this PTO request */
	if (@w_workday = 0) 
		BEGIN
		set @w_errors = @w_errors + '<Message type=''error''><Text>There are no workdays in your requested PTO period.</Text></Message>';
		END
	else
		set @w_start_date = @w_date;
		/* -- if start date is in a weekend or on a holiday, change date to first workday after start date */
		print 'second while loop'
		set @w_date = @w_end_date;
		set @w_workday = 0;
		while (
				(@w_workday = 0) 
				AND (@w_date >= @w_start_date)
			  )
		BEGIN
		select @w_date_dow = DATEPART(dw, @w_date);
			/* -- insert into sa.debug(timestamp,userid,comment) values(now(),'heymchr','w_date ' || w_date || ' - DOW ' || w_date_dow); */

			if ((@w_date_dow between 1 and 5) and (@w_date not in (select holiday_date from employee_pto_holiday)))
				BEGIN 
					set @w_workday = 1;
				END
			ELSE
				BEGIN 
				set @w_date = DATEADD(DAY,1,@w_date);
				END
		END
		print 'End Second Loop'
		/* -- if at this point w_workday is still 0 then we basically have an error because there are no workdays for this PTO request */
		if (@w_workday = 0) 
			BEGIN
			set @w_errors = @w_errors + '<Message type=''error''><Text>There are no workdays in your requested PTO period.</Text></Message>';
			END
		else
			/* -- calculate total days */
			set @w_pto_days = (DATEDIFF(day, @w_end_date, @w_start_date)) + 1;
			
			print 'third while loop'
			/* -- deduct weekends */
			set @w_date = @w_start_date;
			while (@w_date <= @w_end_date)
			BEGIN
				set @w_date_dow = DATEPART(dw, @w_date);
				BEGIN
				SET @w_pto_days = @w_pto_days - 1;
				END 
					SET @w_date = DATEADD(DAY, + 1, @w_date);
			END
			print 'ending third while '
			/* -- deduct holidays */
			print 'second cursor'
			declare x cursor for select holiday_date from employee_pto_holiday; 
			open x 
			fetch next from x into @x_record while @@fetch_status=0
			BEGIN 
				if @x_record between @w_start_date and @w_end_date set @w_pto_days = @w_pto_days - 1; 
				fetch x into @x_record 
			END 
			close x 
			deallocate x
			
			/* -- calculate total hours */
			set @w_size_hours = (select hours from employee_pto_size where rec_id = @p_size);
			set @w_total_hours = @w_size_hours * @w_pto_days; 


                       /* -- now we need to make sure there is enough balance */
                       set @new_submitted_not_approved_vacation_hours = @w_original_submitted_not_approved_vacation_hours;
                       set @new_submitted_not_approved_sick_hours = @w_original_submitted_not_approved_sick_hours;
                       set @new_submitted_not_approved_holiday_hours = @w_original_submitted_not_approved_holiday_hours;


                       IF(@w_original_pto_type=1) /* -- vacation */
						BEGIN
							set @new_submitted_not_approved_vacation_hours = @w_original_submitted_not_approved_vacation_hours - @w_original_total_hours;
						END
					   ELSE IF(@w_original_pto_type=2) /* -- sick */
					   BEGIN
							set @new_submitted_not_approved_sick_hours = @w_original_submitted_not_approved_sick_hours - @w_original_total_hours;
                       END
					   ELSE IF(@w_original_pto_type=4) /* -- holiday */
					   BEGIN
							set @new_submitted_not_approved_holiday_hours = @w_original_submitted_not_approved_holiday_hours - @w_original_total_hours;
                       END

			/* --check PTO Balance */		
			print 'Check PTO Balance'			 
			IF(@p_pto_type=1) -- vacation 
				BEGIN
				if (@w_original_starting_vacation_hours + @w_original_accrued_vacation_hours - @w_original_approved_vacation_hours - @new_submitted_not_approved_vacation_hours + @w_allowable_negative_vacation_balance < @w_total_hours)
					set @w_errors = @w_errors + '<Message type=''error''><Text>Your vacation balance is too low for this request.</Text></Message>';
				END
			 IF(@p_pto_type=2) -- sick
				BEGIN
				if (@w_original_starting_sick_hours - @w_original_approved_sick_hours - @new_submitted_not_approved_sick_hours < @w_total_hours)
					set @w_errors = @w_errors + '<Message type=''error''><Text>Your sick day balance is too low for this request.</Text></Message>';
				END
			 IF(@p_pto_type=4) -- holiday
				BEGIN
				if (@w_original_starting_holiday_hours - @w_original_approved_holiday_hours - @new_submitted_not_approved_holiday_hours < @w_total_hours)
					set @w_errors = @w_errors + '<Message type=''error''><Text>Your floating Holiday balance is too low for this request.</Text></Message>';
				END
			
			IF (@w_errors = '')
			print 'Update PTO Request'
			update employee_pto_request set 
				request_date = @p_request_date,
				start_date = @w_start_date,
				start_date_dow = (select DATEPART(dw, @w_start_date)),
				end_date = @w_end_date,
				end_date_dow = (select DATEPART(dw, @w_end_date)),
				size = @p_size,
				pto_type = @p_pto_type,
				total_hours = @w_total_hours,
				comments = @p_comments
			where rec_id = @p_request_id;

			/* -- if this is a change for a previously submitted request, we need to update the pto balance
			-- first update balance to remove the original request */
			print 'Update PTO Balance'
			IF(@w_original_pto_type=1) -- vacation
				BEGIN
				update employee_pto_balance 
				set submitted_not_approved_vacation_hours = @w_original_submitted_not_approved_vacation_hours - @w_original_total_hours
				where emp_id = @w_resource_id and pto_year = (YEAR(@w_original_start_date));
				END
			ELSE IF(@w_original_pto_type=2) -- sick
				BEGIN
				update employee_pto_balance 
				set submitted_not_approved_sick_hours = @w_original_submitted_not_approved_sick_hours - @w_original_total_hours
				where emp_id = @w_resource_id and pto_year = (YEAR(@w_original_start_date));
				END
			ELSE IF(@w_original_pto_type=4) -- holiday
				BEGIN
				update employee_pto_balance 
				set submitted_not_approved_holiday_hours = @w_original_submitted_not_approved_holiday_hours - @w_original_total_hours
				where emp_id = @w_resource_id and pto_year = (YEAR(@w_original_start_date));
				END

			/* -- now update balance based on new request */
			print 'Update Balance Based on Request'
			select @w_submitted_not_approved_holiday_hours=submitted_not_approved_holiday_hours, @w_submitted_not_approved_vacation_hours=submitted_not_approved_vacation_hours, @w_submitted_not_approved_sick_hours=submitted_not_approved_sick_hours
			from employee_pto_balance
			where emp_id = @w_resource_id and pto_year = (YEAR(@w_start_date));
			
			IF(@p_pto_type=1) -- vacation
				BEGIN
				update employee_pto_balance set submitted_not_approved_vacation_hours = @w_submitted_not_approved_vacation_hours + @w_total_hours
				where emp_id = @w_resource_id and pto_year = (YEAR(@w_start_date));
				END
			ELSE IF(@p_pto_type=2) -- sick
				BEGIN
				update employee_pto_balance set submitted_not_approved_sick_hours = @w_submitted_not_approved_sick_hours + @w_total_hours
				where emp_id = @w_resource_id and pto_year = (YEAR(@w_start_date));
				END
			ELSE IF(@p_pto_type=4) -- holiday
				BEGIN
				update employee_pto_balance set submitted_not_approved_holiday_hours = @w_submitted_not_approved_holiday_hours + @w_total_hours
				where emp_id = @w_resource_id and pto_year = (YEAR(@w_start_date));
				END
			
	if (@w_errors <> '') 
		BEGIN
		set @p_errors = '<Response>' + @w_errors + '</Response>';
		END
  END

END;
GO

--sp_pto_cancel_request-stored-procedure---------------------------------------------------------------------------------	


-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Joanie Solorzano
-- Create date: 1/4/2019
-- Description:	Converted Stored Procedure from postgresql to sql server
-- =============================================
CREATE PROCEDURE sp_pto_cancel_request(
	@p_request_id int,
	@p_submit_errors varchar(2048) OUTPUT
	)
AS
BEGIN


DECLARE 
	@w_errors varchar(2048),
	@w_resource_id int,
	@w_approver_id int,
	@w_status_id int,
	@w_total_hours int,
	@w_pto_type int,
	@w_start_date date,
	@w_end_date date,
	@w_resource_name varchar(150),
	@w_approver_email varchar(150),
	@w_approver_extendername varchar(150),
	@w_pto_description varchar(250),
	@w_subject varchar(150),
	@w_body varchar(MAX),
	@w_body2 varchar(MAX),
	@w_pto_emails varchar(150),
	@w_convertedID varchar(MAX),
	@w_pto_notifications varchar(150);

BEGIN

/*
-- retrieve request status
-- error checking:
--  * request must be in approved status
-- change status to cancellation pending
*/

select @w_resource_id=resource_id, @w_approver_id=approver_id, @w_status_id=status_id, @w_total_hours=total_hours, @w_pto_type=pto_type, @w_start_date=start_date, @w_end_date=end_date
from employee_pto_request
where rec_id = @p_request_id;

set @w_errors = '';
IF (@w_status_id <> 3)
	set @w_errors = @w_errors + '<Message type=''error''><Text>This request cannot be cancelled.</Text></Message>';

IF (@w_errors <> '') 
	set @p_submit_errors = '<Response>' + @w_errors + '</Response>';
ELSE 
	set @p_submit_errors = '';
	update employee_pto_request set status_id = 6 where rec_id = @p_request_id;

	
	/* -- notify approver that a cancellation request was submitted */
	set @w_resource_name = (select resourcename from employee_resources where resourceid = @w_resource_id);
	set @w_approver_extendername = (select extendername from employee_resources where resourceid = @w_approver_id);
	set @w_approver_email = (select email from employee_resources where resourceid = @w_approver_id);
	set @w_pto_description = (select description from employee_pto_type where rec_id = @w_pto_type);
	set @w_subject = 'A new PTO cancellation Request has been submitted for your approval';
	set @w_body = @w_resource_name + ' has requested to cancel the ' + @w_pto_description + ' from ' + convert(varchar(MAX),@w_start_date) + 
			' to ' + convert(varchar(MAX),@w_end_date) + '.';
	set @w_body2 = @w_body + ' Please log on to http://www.catavolt.net/cvops to review this request.';

		BEGIN
		/* --perform sa.create_email_notification(@w_approver_email,'notifications@catavolt.com', @w_subject, @w_body2, null); */
		
		--call stored procedure	
		EXEC @w_errors = dbo.sp_create_email_notification @w_approver_email,'notifications@catavolt.com',@w_subject,@w_body2,@w_errors output
			IF (@w_errors <> '') 
			BEGIN 
				set @p_submit_errors = '<Response>' + @w_errors + '</Response>'; 
			END  
		END


		BEGIN 
		set @w_convertedID = CAST(@p_request_id AS varchar(MAX));

		--call stored procedure	 
			EXEC @w_errors = dbo.sp_create_push_notification 'PTO',@w_approver_extendername,NULL,@w_subject,@w_body, @w_convertedID,'cancellationrequested','0',@w_errors output
			IF (@w_errors <> '') 
			BEGIN 
				set @p_submit_errors = '<Response>' + @w_errors + '</Response>'; 
			END 
		
		END
  
	END;
END;
GO


--sp_update_push_notification-stored-procedure---------------------------------------------------------------------------------

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Joanie Solorzano	
-- Create date: 1/4/2019
-- Description:	Converted Stored Procedure from postgresql to sql server
-- =============================================
CREATE PROCEDURE sp_update_push_notification 
	(@p_notification_type varchar(150),@p_userdata1 varchar(150),@p_longuserdata1 varchar(150),
	@p_notification_title varchar(150),@p_notification_body varchar(MAX),@p_newuserdata1 varchar(150),
	@p_newlonguserdata1 varchar(150),@p_manual_completion varchar(150),@p_notification_status varchar(150),
	@p_errors varchar(2048) output)
AS
BEGIN
	DECLARE @w_errors varchar(2048);

BEGIN

set @w_errors = '';

if ((@p_notification_type is null) or (@p_notification_type = '')) 
	set @w_errors = @w_errors + '<Message type=''error''><Text>You must provide the notification type.</Text></Message>';

if (((@p_userdata1 is null) or (@p_userdata1 = '')) and ((@p_longuserdata1 is null) or (@p_longuserdata1 = '')))
	set @w_errors = @w_errors + '<Message type=''error''><Text>You must provide criteria to identify the record.</Text></Message>';

  IF (@w_errors <> '')
    set @p_errors = '<Response>' + @w_errors + '</Response>';
  ELSE 
    set @p_errors = '';

    if ((@p_notification_title is not null) and (@p_notification_title <> '')) 
	BEGIN
		UPDATE catavolt_notification 
		set notification_title = @p_notification_title 
		where ((userdata1 = @p_userdata1) and (longuserdata1 = @p_longuserdata1));
    END
 
    if ((@p_notification_body is not null) and (@p_notification_body <> ''))
	BEGIN 
		UPDATE catavolt_notification 
		set notification_body = @p_notification_body 
		where ((userdata1 = @p_userdata1) and (longuserdata1 = @p_longuserdata1));
    END

    if ((@p_manual_completion is not null) and (@p_manual_completion <> ''))
	BEGIN 
		UPDATE catavolt_notification 
		set manual_completion = @p_manual_completion 
		where ((userdata1 = @p_userdata1) and (longuserdata1 = @p_longuserdata1));
    END

    if ((@p_notification_status is not null) and (@p_notification_status <> ''))
	BEGIN 
		UPDATE catavolt_notification 
		set notification_status = @p_notification_status
		where ((userdata1 = @p_userdata1) and (longuserdata1 = @p_longuserdata1));
    END

    if ((@p_newuserdata1 is not null) and (@p_newuserdata1 <> '')) 
	BEGIN
		UPDATE catavolt_notification 
		set userdata1 = @p_newuserdata1 
		where ((userdata1 = @p_userdata1) and (longuserdata1 = @p_longuserdata1));
    END

    if ((@p_newlonguserdata1 is not null) and (@p_newlonguserdata1 <> '')) 
		if ((@p_newuserdata1 is not null) and (@p_newuserdata1 <> '')) 
			BEGIN
				UPDATE catavolt_notification 
				set longuserdata1 = @p_newlonguserdata1 
				where ((userdata1 = @p_newuserdata1) and (longuserdata1 = @p_longuserdata1));
			END
		else
			BEGIN
				UPDATE catavolt_notification 
				set longuserdata1 = @p_newlonguserdata1 
				where ((userdata1 = @p_userdata1) and (longuserdata1 = @p_longuserdata1));
			END

  END;

END;
GO
	
--sp_pto_delete_request-stored-procedure---------------------------------------------------------------------------------

			
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Joanie Solorzano
-- Create date: 1/4/2019
-- Description:	Converted Stored Procedure from postgresql to sql server
-- =============================================
CREATE PROCEDURE sp_pto_delete_request(
	@p_request_id int,
	@p_submit_errors varchar(2048) OUTPUT
	)
AS
BEGIN
	
DECLARE 
@w_errors varchar(2048),
@w_resource_id int,
@w_approver_id int,
@w_status_id int,
@w_total_hours int,
@w_pto_type int,
@w_start_date date,
@w_end_date date,
@w_starting_holiday_hours int,
@w_submitted_not_approved_holiday_hours int, 
@w_approved_holiday_hours int,
@w_starting_vacation_hours int,
@w_submitted_not_approved_vacation_hours int, 
@w_approved_vacation_hours int,
@w_starting_sick_hours int,
@w_submitted_not_approved_sick_hours int, 
@w_approved_sick_hours int,
@w_resource_name varchar(150),
@w_approver_email varchar(150),
@w_approver_extendername varchar(150),
@w_pto_description varchar(50),
@w_subject varchar(150),
@w_body varchar(MAX),
@w_pto_emails varchar(150),
@w_convertedID varchar(MAX),
@w_pto_notifications varchar(150);


BEGIN

-- retrieve request status
-- error checking:
--  * request must be in created, submitted, cancelled or rejected status
-- delete request

select @w_resource_id=resource_id, @w_approver_id=approver_id, @w_status_id=status_id, @w_total_hours=total_hours, @w_pto_type=pto_type, @w_start_date=start_date, @w_end_date=end_date
from employee_pto_request
where rec_id = @p_request_id;

set @w_errors = '';
if (@w_status_id not in (1,2,4,6))
	set @w_errors = @w_errors + '<Message type=''error''><Text>This request cannot be deleted.</Text></Message>';

if (@w_errors <> '') 
	set @p_submit_errors = '<Response>' + @w_errors + '</Response>';
else 
	set @p_submit_errors = '';
	delete from employee_pto_request where rec_id = @p_request_id;
	-- if request was submitted before, then we need to adjust the balance accordingly
	if(@w_status_id = 2)
		BEGIN
		select @w_submitted_not_approved_holiday_hours=submitted_not_approved_holiday_hours, @w_submitted_not_approved_vacation_hours=submitted_not_approved_vacation_hours, @w_submitted_not_approved_sick_hours=submitted_not_approved_sick_hours
		from employee_pto_balance
		where emp_id = @w_resource_id and pto_year = YEAR(@w_start_date);
		IF(@w_pto_type=1)  -- vacation
			update employee_pto_balance set submitted_not_approved_vacation_hours = @w_submitted_not_approved_vacation_hours - @w_total_hours
			where emp_id = @w_resource_id and pto_year = YEAR(@w_start_date);
		IF(@w_pto_type=2)  -- sick
			update employee_pto_balance set submitted_not_approved_sick_hours = @w_submitted_not_approved_sick_hours - @w_total_hours
			where emp_id = @w_resource_id and pto_year = YEAR(@w_start_date);
		IF(@w_pto_type=4) -- holiday
			update employee_pto_balance set submitted_not_approved_holiday_hours = @w_submitted_not_approved_holiday_hours - @w_total_hours
			where emp_id = @w_resource_id and pto_year = YEAR(@w_start_date);
		END
	else

		
		-- notify approver that the previously submitted request has been cancelled 
		set @w_resource_name = (select resourcename from employee_resources where resourceid = @w_resource_id);
		set @w_approver_email = (select email from employee_resources where resourceid = @w_approver_id);
		set @w_approver_extendername = (select extendername from employee_resources where resourceid = @w_approver_id);
		set @w_pto_description = (select description from employee_pto_type where rec_id = @w_pto_type);
		set @w_subject = 'A previously submitted PTO Request has been cancelled';
		set @w_body = @w_resource_name + ' has cancelled ' + @w_pto_description + ' from ' + convert(varchar(MAX),@w_start_date) + 
				' to ' + convert(varchar(MAX),@w_end_date) + '. Please ignore the previous approval request. No other action is required.';

			BEGIN
			--perform sa.create_email_notification(w_approver_email,'notifications@catavolt.com', w_subject, w_body, null);
			--call stored procedure	
			EXEC @w_errors = dbo.sp_create_email_notification @w_approver_email,'notifications@catavolt.com',@w_subject,@w_body,@w_errors output
			IF (@w_errors <> '') 
				BEGIN 
				set @p_submit_errors = '<Response>' + @w_errors + '</Response>'; 
				END 
			END;

			BEGIN
			-- update the approval request notification since we have deleted the PTO request
			-- - set status to completed
			-- - change user data so the workflow will show a message that the request has been removed instead of erroring out with no records selected
			--perform sp_update_push_notification('PTO', p_request_id::text,'submitted', null, null, null, 'submittedanddeleted', null, 'COMPLETE', null);
			
			--call stored procedure	
			set @w_convertedID = CAST(@p_request_id AS varchar(MAX));
			EXEC @w_errors = dbo.sp_update_push_notification 'PTO', @w_convertedID,'submitted', null, null, null, 'submittedanddeleted', null, 'COMPLETE',@w_errors output
			IF (@w_errors <> '') 
			BEGIN 
				set @p_submit_errors = '<Response>' + @w_errors + '</Response>'; 
			END 
			
			-- Commented out the following line - no need to send another notification to say that no action is required
			-- perform sa.create_push_notification('PTO', w_approver_extendername, NULL, w_subject, w_body, p_request_id::text, 'deleted', '0', NULL);
			END

		BEGIN
		-- status is created, rejected or cancelled
		-- We need to make sure any previously generated notifications are updated since we have deleted the PTO request
		-- - set status to completed
		-- - change user data so the workflow will show a message that the request has been removed instead of erroring out with no records
		--perform sp_update_push_notification('PTO', p_request_id::text,'approved', null, null, null, 'requestnolongerexists', null, 'COMPLETE', null);
		--perform sp_update_push_notification('PTO', p_request_id::text,'rejected', null, null, null, 'requestnolongerexists', null, 'COMPLETE', null);
		--perform sp_update_push_notification('PTO', p_request_id::text,'cancelled', null, null, null, 'requestnolongerexists', null, 'COMPLETE', null);
		
		--call stored procedure	
			set @w_convertedID = CAST(@p_request_id AS varchar(MAX));
			BEGIN
				EXEC @w_errors = dbo.sp_update_push_notification 'PTO', @w_convertedID,'approved', null, null, null, 'requestnolongerexists', null, 'COMPLETE',@w_errors output
				IF (@w_errors <> '') 
					BEGIN 
						set @p_submit_errors = '<Response>' + @w_errors + '</Response>'; 
					END
			END	 
			BEGIN
				EXEC @w_errors = dbo.sp_update_push_notification 'PTO', @w_convertedID,'rejected', null, null, null, 'requestnolongerexists', null, 'COMPLETE',@w_errors output
				IF (@w_errors <> '') 
					BEGIN 
						set @p_submit_errors = '<Response>' + @w_errors + '</Response>'; 
					END
			END	
			BEGIN
				EXEC @w_errors = dbo.sp_update_push_notification 'PTO', @w_convertedID,'cancelled', null, null, null, 'requestnolongerexists', null, 'COMPLETE',@w_errors output
				IF (@w_errors <> '') 
					BEGIN 
						set @p_submit_errors = '<Response>' + @w_errors + '</Response>'; 
					END
			END	
		END
	END
  
END;
GO


--sp_pto_approve_request-stored-procedure---------------------------------------------------------------------------------


-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Joanie Solorzano	
-- Create date: 1/4/2019
-- Description:	Converted Stored Procedure from postgresql to sql server
-- =============================================
CREATE PROCEDURE sp_pto_approve_request 
	(@p_request_id int,@p_approver_comments varchar(MAX),
	@p_submit_errors varchar(2048) output)
AS
BEGIN
	
DECLARE 
@w_errors varchar(2048),
@w_resource_id int,
@w_approver_id int,
@w_status_id int,
@w_total_hours int,
@w_pto_type int,
@w_start_date date,
@w_starting_holiday_hours int,
@w_submitted_not_approved_holiday_hours int, 
@w_approved_holiday_hours int,
@w_starting_vacation_hours numeric(8,5),
@w_accrued_vacation_hours numeric(8,5),
@w_submitted_not_approved_vacation_hours int, 
@w_approved_vacation_hours int,
@w_starting_sick_hours int,
@w_submitted_not_approved_sick_hours int, 
@w_approved_sick_hours int,
@w_requester_email varchar(150),
@w_requester_extendername varchar(150),
@w_approver_name varchar(150),
@w_pto_description varchar(150),
@w_subject varchar(150),
@w_body varchar(MAX),
@w_body2 varchar(MAX),
@w_pto_emails varchar(150),
@w_convertedID varchar(MAX),
@w_pto_notifications varchar(150);

BEGIN

/*
-- retrieve request details and balance details
-- error checking:
--  * request must be in submitted  or cancellation pending status
-- change status to approved or cancelled
-- update submitted_not_approved_hours and approved_hours
*/

select @w_resource_id=resource_id, @w_approver_id=approver_id, @w_status_id=status_id, @w_total_hours=total_hours, @w_pto_type=pto_type, @w_start_date=start_date
from employee_pto_request
where rec_id = @p_request_id;

select @w_starting_holiday_hours=starting_holiday_hours, @w_submitted_not_approved_holiday_hours=submitted_not_approved_holiday_hours, @w_approved_holiday_hours=approved_holiday_hours, 
       @w_starting_vacation_hours=starting_vacation_hours, @w_accrued_vacation_hours=accrued_vacation_hours, @w_submitted_not_approved_vacation_hours=submitted_not_approved_vacation_hours, @w_approved_vacation_hours=approved_vacation_hours,
       @w_starting_sick_hours=starting_sick_hours, @w_submitted_not_approved_sick_hours=submitted_not_approved_sick_hours, @w_approved_sick_hours=approved_sick_hours
from employee_pto_balance
where emp_id = @w_resource_id and pto_year = YEAR(@w_start_date);

set @w_errors = '';
if (@w_status_id not in (2,5))
	set @w_errors = @w_errors + '<Message type=''error''><Text>This request cannot be approved.</Text></Message>';

if (@w_errors <> '') 
	set @p_submit_errors = '<Response>' + @w_errors + '</Response>';
else 
	set @p_submit_errors = '';
	set @w_requester_email = (select email from employee_resources where resourceid = @w_resource_id);
	set @w_requester_extendername = (select extendername from employee_resources where resourceid = @w_resource_id);
	set @w_approver_name = (select resourcename from employee_resources where resourceid = @w_approver_id);
	set @w_pto_description = (select description from employee_pto_type where rec_id = @w_pto_type);
	IF(@w_status_id=2) /* -- approval request */
		BEGIN
		update employee_pto_request set status_id = 3, approval_date = CONVERT (date, GETDATE()), approver_comments = @p_approver_comments where rec_id = @p_request_id;
		END
	IF(@w_pto_type=1)  /* -- vacation */
		BEGIN
		    print @w_submitted_not_approved_vacation_hours - @w_total_hours
			print @w_approved_vacation_hours + @w_total_hours
			update employee_pto_balance 
				set submitted_not_approved_vacation_hours = @w_submitted_not_approved_vacation_hours - @w_total_hours,
				approved_vacation_hours = @w_approved_vacation_hours + @w_total_hours
			where emp_id = @w_resource_id and pto_year = YEAR(@w_start_date);
			
			print @w_starting_vacation_hours + @w_accrued_vacation_hours - @w_approved_vacation_hours - @w_total_hours
			update employee_pto_balance
			        set starting_vacation_hours =  @w_starting_vacation_hours + @w_accrued_vacation_hours - @w_approved_vacation_hours - @w_total_hours
			where emp_id = @w_resource_id and pto_year = YEAR(@w_start_date) + 1;
		END
	IF(@w_pto_type=2) /* -- sick */
		BEGIN
			update employee_pto_balance 
				set submitted_not_approved_sick_hours = @w_submitted_not_approved_sick_hours - @w_total_hours,
				approved_sick_hours = @w_approved_sick_hours + @w_total_hours
			where emp_id = @w_resource_id and pto_year = YEAR(@w_start_date);
		END
	IF(@w_pto_type=4) /* -- holiday */
		BEGIN
			update employee_pto_balance 
				set submitted_not_approved_holiday_hours = @w_submitted_not_approved_holiday_hours - @w_total_hours,
				approved_holiday_hours = @w_approved_holiday_hours + @w_total_hours
			where emp_id = @w_resource_id and pto_year = YEAR(@w_start_date);

		set @w_subject = 'Your ' + @w_pto_description + ' request has been approved';
		set @w_body = @w_approver_name + ' has approved your ' + @w_pto_description + ' request.';
		set @w_body2 = @w_body + ' Please log on to http://www.catavolt.net/cvops for details.'; 
		END
	IF(@w_pto_type=5) /* -- cancellation request */
		
		update employee_pto_request set status_id = 6, approval_date = CONVERT (date, GETDATE()), approver_comments = @p_approver_comments where rec_id = @p_request_id;
		IF(@w_pto_type=1) /* -- vacation */
			BEGIN
			update employee_pto_balance 
				set approved_vacation_hours = @w_approved_vacation_hours - @w_total_hours
			where emp_id = @w_resource_id and pto_year = YEAR(@w_start_date);
			update employee_pto_balance
			        set starting_vacation_hours =  @w_starting_vacation_hours + @w_accrued_vacation_hours - @w_approved_vacation_hours + @w_total_hours
			where emp_id = @w_resource_id and pto_year = YEAR(@w_start_date) + 1;
			END
		IF(@w_pto_type=2) /* -- sick */
			BEGIN
			update employee_pto_balance 
				set approved_sick_hours = @w_approved_sick_hours - @w_total_hours
			where emp_id = @w_resource_id and pto_year = YEAR(@w_start_date);	
			END
		IF(@w_pto_type=4) /* -- holiday */
			update employee_pto_balance 
				set approved_holiday_hours = @w_approved_holiday_hours - @w_total_hours
			where emp_id = @w_resource_id and pto_year = YEAR(@w_start_date);
		else
		set @w_subject = 'Your ' + @w_pto_description + ' request has been approved';
		set @w_body = @w_approver_name + ' has approved your ' + @w_pto_description + ' request.';
		set @w_body2 = @w_body + ' Please log on to http://www.catavolt.net/cvops for details.';

			
		--call stored procedure	
		EXEC @w_errors = dbo.sp_create_email_notification @w_requester_email,'notifications@catavolt.com',@w_subject,@w_body2,@w_errors output
			IF (@w_errors <> '') 
			BEGIN 
				set @p_submit_errors = '<Response>' + @w_errors + '</Response>'; 
			END  
		END
		
			
		BEGIN 
		set @w_convertedID = CAST(@p_request_id AS varchar(MAX));

		--call stored procedure	 
			EXEC @w_errors = dbo.sp_create_push_notification 'PTO',@w_requester_extendername,NULL,@w_subject,@w_body, @w_convertedID,'approved','0',@w_errors output
			IF (@w_errors <> '') 
			BEGIN 
				set @p_submit_errors = '<Response>' + @w_errors + '</Response>'; 
			END 
		
		END

END;
GO

--sp_pto_reject_request-stored-procedure---------------------------------------------------------------------------------


-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Joanie Solorzano	
-- Create date: 1/4/2019
-- Description:	Converted Stored Procedure from postgresql to sql server
-- =============================================
CREATE PROCEDURE sp_pto_reject_request 
	(@p_request_id int,@p_approver_comments varchar(MAX),
	@p_submit_errors varchar(2048) output)
AS
BEGIN
	
DECLARE 
@w_errors varchar(2048),
@w_resource_id int,
@w_approver_id int,
@w_status_id int,
@w_total_hours int,
@w_pto_type int,
@w_start_date date,
@w_starting_holiday_hours int,
@w_submitted_not_approved_holiday_hours int, 
@w_approved_holiday_hours int,
@w_starting_vacation_hours numeric(8,5),
@w_submitted_not_approved_vacation_hours int, 
@w_approved_vacation_hours int,
@w_starting_sick_hours int,
@w_submitted_not_approved_sick_hours int, 
@w_approved_sick_hours int,
@w_requester_email varchar(150),
@w_requester_extendername varchar(150),
@w_approver_name varchar(150),
@w_pto_description varchar(150),
@w_subject varchar(150),
@w_body varchar(MAX),
@w_body2 varchar(MAX),
@w_pto_emails varchar(150),
@w_convertedID varchar(MAX),
@w_pto_notifications varchar(150);

BEGIN

/*
-- retrieve request details and balance details
-- error checking:
--  * request must be in submitted  or cancellation pending status
--  * must provide rejection comments
-- change status to rejected (and update rejected date if this was not a cancellation requeest)
-- update submitted_not_approved_hours
*/

select @w_resource_id=resource_id, @w_approver_id=approver_id, @w_status_id=status_id, @w_total_hours=total_hours, @w_pto_type=pto_type, @w_start_date=start_date
from employee_pto_request
where rec_id = @p_request_id;


select @w_starting_holiday_hours=starting_holiday_hours, @w_submitted_not_approved_holiday_hours=submitted_not_approved_holiday_hours, @w_approved_holiday_hours=approved_holiday_hours, 
       @w_starting_vacation_hours=starting_vacation_hours, @w_submitted_not_approved_vacation_hours=submitted_not_approved_vacation_hours, @w_approved_vacation_hours=approved_vacation_hours,
       @w_starting_sick_hours=starting_sick_hours, @w_submitted_not_approved_sick_hours=submitted_not_approved_sick_hours, @w_approved_sick_hours=approved_sick_hours
from employee_pto_balance
where emp_id = @w_resource_id and pto_year = YEAR(@w_start_date);

set @w_errors = '';
if (@w_status_id not in (2,5))
	set @w_errors = @w_errors + '<Message type=''error''><Text>This request cannot be rejected.</Text></Message>';

if ((@p_approver_comments is null) or (@p_approver_comments) = '')
	set @w_errors = @w_errors + '<Message type=''error''><Text>You must provide a reason for rejecting this request.</Text></Message>';

if (@w_errors <> '') 
	set @p_submit_errors = '<Response>' + @w_errors + '</Response>';
else 
	set @p_submit_errors = '';
	set @w_requester_email = (select email from employee_resources where resourceid = @w_resource_id);
	set @w_requester_extendername = (select extendername from employee_resources where resourceid = @w_resource_id);
	set @w_approver_name = (select resourcename from employee_resources where resourceid = @w_approver_id);
	set @w_pto_description = (select description from employee_pto_type where rec_id = @w_pto_type);
	IF(@w_status_id=2) /* -- approval request */
		update employee_pto_request set status_id = 4, rejection_date = CONVERT (date, GETDATE()), approver_comments = @p_approver_comments where rec_id = @p_request_id;
		IF(@w_pto_type=1) /* -- vacation */
			update employee_pto_balance 
			set submitted_not_approved_vacation_hours = @w_submitted_not_approved_vacation_hours - @w_total_hours
			where emp_id = @w_resource_id and pto_year = YEAR(@w_start_date);
		IF(@w_pto_type=2) /* -- sick */
			update employee_pto_balance 
			set submitted_not_approved_sick_hours = @w_submitted_not_approved_sick_hours - @w_total_hours
			where emp_id = @w_resource_id and pto_year = YEAR(@w_start_date);
		IF(@w_pto_type=4) /* -- holiday */
			update employee_pto_balance 
			set submitted_not_approved_holiday_hours = @w_submitted_not_approved_holiday_hours - @w_total_hours
			where emp_id = @w_resource_id and pto_year = YEAR(@w_start_date);
		else

		set @w_subject = 'Your ' + @w_pto_description + ' request has been rejected';
		set @w_body = @w_approver_name + ' has rejected your ' + @w_pto_description + ' request.';
		set @w_body2 = @w_body + ' Please log on to http://www.catavolt.net/cvops for details.'; 
	
	IF(@w_pto_type=5) /* -- cancellation request */
		update employee_pto_request set status_id = 3, approver_comments = @p_approver_comments where rec_id = @p_request_id;
		set @w_subject = 'Your ' + @w_pto_description + ' cancellation request has been rejected';
		set @w_body = @w_approver_name + ' has rejected your ' + @w_pto_description + ' cancellation request.';
		set @w_body2 = @w_body + ' Please log on to http://www.catavolt.net/cvops for details.';

			
		--call stored procedure	
		EXEC @w_errors = dbo.sp_create_email_notification @w_requester_email,'notifications@catavolt.com',@w_subject,@w_body2,@w_errors output
			IF (@w_errors <> '') 
			BEGIN 
				set @p_submit_errors = '<Response>' + @w_errors + '</Response>'; 
			END  
		END
				
		BEGIN 
		set @w_convertedID = CAST(@p_request_id AS varchar(MAX));

		--call stored procedure	 
			EXEC @w_errors = dbo.sp_create_push_notification 'PTO',@w_requester_extendername,NULL,@w_subject,@w_body, @w_convertedID,'rejected','0',@w_errors output
			IF (@w_errors <> '') 
			BEGIN 
				set @p_submit_errors = '<Response>' + @w_errors + '</Response>'; 
			END 
		
		END
		
END;
GO

--sp_pto_run_accrual-stored-procedure---------------------------------------------------------------------------------

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Joanie Solorzano	
-- Create date: 1/4/2019
-- Description:	Converted Stored Procedure from postgresql to sql server
-- =============================================
CREATE PROCEDURE sp_pto_run_accrual 
	(@p_effective_date date,
	@p_submit_errors varchar(2048) output)
AS
BEGIN

DECLARE 
@w_errors varchar(2048),
@RowNum int,
@w_accrued_vacation_hours numeric(8,5),
@w_next_year_starting_vacation_hours numeric(8,5),
@w_emp_id int,
@w_pto_year int,
@w_vacation_hours decimal(8,5),
@w_date date;

BEGIN

-- error checking:
-- if any accrual history records already exist for this effective date, do not allow accrual
-- if accrual for any previous pay period 9starting 2016) has not yet run, do not allow accrual for this period
print 'start run accrual'
set @w_errors = '';
if (exists (select * from employee_pto_accrual_hist where accrual_date = @p_effective_date)) 
	set @w_errors = @w_errors + '<Message type=''error''><Text>Accrual has already been run for this pay period.</Text></Message>';

set @w_date = (select min(payrolldate) 
          from employee_bc_payrollperiod 
          where YEAR(payrolldate) >= 2016 
            and payrolldate not in  (select accrual_date from employee_pto_accrual_hist group by accrual_date));
if (@w_date < @p_effective_date)
	set @w_errors = @w_errors + '<Message type=''error''><Text>Cannot run accrual. You must first run accrual for ' +  convert(varchar(MAX),@w_date) + '.</Text></Message>';


if (@w_errors <> '') 
	set @p_submit_errors = '<Response>' + @w_errors + '</Response>';
else 
	set @p_submit_errors = '';

	print 'build accrual history'
	-- build accrual history
	insert into employee_pto_accrual_hist (run_date, accrual_date, emp_id, accrued_vacation_hours, accrual_max_reached)
	select GETDATE(), @p_effective_date, A.resourceid, 
			  actual_accrued_hours_this_period=
			  CASE
                when (D.starting_vacation_hours + D.accrued_vacation_hours - D.approved_vacation_hours + C.accrual_rate > C.accrual_max)
                then
					CASE 
						--greatest(C.accrual_max - D.starting_vacation_hours - D.accrued_vacation_hours + D.approved_vacation_hours,0)
						WHEN ((C.accrual_max - D.starting_vacation_hours - D.accrued_vacation_hours + D.approved_vacation_hours) > 0)
						THEN (C.accrual_max - D.starting_vacation_hours - D.accrued_vacation_hours + D.approved_vacation_hours)
						ELSE 0
					END
				else
					CASE 
					--greatest(C.accrual_rate,0)
					WHEN (C.accrual_rate > 0)
					THEN C.accrual_rate
					ELSE 0
					END
              END,
			  max_accrued_reached=
              CASE
                when (D.starting_vacation_hours + D.accrued_vacation_hours - D.approved_vacation_hours + C.accrual_rate > C.accrual_max)
                then 'TRUE'
                else 'FALSE'
              END
        from employee_resources A
        join employee_pto_emp_accrual_rate B on A.resourceid = B.emp_id and B.accrual_effective_from <= @p_effective_date and 
                                                                     (B.accrual_effective_to is NULL or @p_effective_date < B.accrual_effective_to )
        join employee_pto_accrual_info C on B.accrual_id = C.accrual_id
        join employee_pto_balance D on A.resourceid = D.emp_id and D.pto_year = YEAR(@p_effective_date) 
        where A.active = 'true' and A.internal = 'true';

	-- update pto balance with new accrued hours this year
	-- also update next year's starting hours accordingly
		print 'update pto balance with accrued'
        --select @w_emp_id=emp_id, @w_pto_year=pto_year, @w_vacation_hours=accrued_vacation_hours from employee_pto_balance where pto_year = YEAR(@p_effective_date)
		Select @RowNum = Count(*) From employee_pto_balance
		print @RowNum
		While @RowNum > 0
		BEGIN
		    select @w_emp_id=emp_id, @w_pto_year=pto_year, @w_vacation_hours=accrued_vacation_hours from employee_pto_balance where pto_year = YEAR(@p_effective_date) and emp_id=@RowNum
			print @w_vacation_hours
			set @w_accrued_vacation_hours = @w_vacation_hours + 
		                           (select accrued_vacation_hours from employee_pto_accrual_hist 
		                            where emp_id = @w_emp_id and accrual_date = @p_effective_date);
			print @w_accrued_vacation_hours
			if (@w_accrued_vacation_hours is not null)
			BEGIN 
		     update employee_pto_balance set accrued_vacation_hours = @w_accrued_vacation_hours 
			     where emp_id = @w_emp_id and pto_year = YEAR(@p_effective_date);
			END
		
			set @w_next_year_starting_vacation_hours = (select starting_vacation_hours from employee_pto_balance 
							where emp_id = @w_emp_id and pto_year = (YEAR(@p_effective_date)+1))
						     + (select accrued_vacation_hours from employee_pto_accrual_hist 
							where emp_id = @w_emp_id and accrual_date = @p_effective_date);
			print @w_next_year_starting_vacation_hours
			if (@w_next_year_starting_vacation_hours is not null)
			BEGIN
		     update employee_pto_balance set starting_vacation_hours = @w_next_year_starting_vacation_hours
		          where emp_id = @w_emp_id and pto_year = (YEAR(@p_effective_date)+1);
			END

			set @RowNum = @RowNum - 1
        END
END;
  
END;

GO

--sp_pto_undo_accrual-stored-procedure---------------------------------------------------------------------------------


-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Joanie Solorzano	
-- Create date: 1/4/2019
-- Description:	Converted Stored Procedure from postgresql to sql server
-- =============================================
CREATE PROCEDURE sp_pto_undo_accrual 
	(@p_effective_date date,
	@p_submit_errors varchar(2048) output)
AS
BEGIN

DECLARE 
@w_errors varchar(2048),
@w_accrued_vacation_hours numeric(8,5),
@w_next_year_starting_vacation_hours numeric(8,5),
@w_emp_id int,
@RowNum int,
@w_pto_year int,
@w_date date;

BEGIN

/*
-- error checking:
-- if no accrual history records  exist for this effective date, cannot undo
*/

set @w_errors = '';
if not (exists (select * from employee_pto_accrual_hist where accrual_date = @p_effective_date))
	set @w_errors = @w_errors + '<Message type=''error''><Text>No Accrual History exists for this pay period.</Text></Message>';


set @w_date = (select max(payrolldate) 
          from employee_bc_payrollperiod 
          where YEAR(payrolldate) >= 2016 
            and payrolldate in  (select accrual_date from employee_pto_accrual_hist group by accrual_date));
if (@w_date > @p_effective_date) 
	set @w_errors = @w_errors + '<Message type=''error''><Text>You must first undo accrual for ' + convert(varchar(MAX),@w_date) + '.</Text></Message>';

if (@w_errors <> '') 
	set @p_submit_errors = '<Response>' + @w_errors + '</Response>';
else 
	set @p_submit_errors = '';

	/* -- update pto balance to reduce accrued hours this year based on accrual history records for this effective date
	-- also update next year's starting vacation balance accordingly */
		
		Select @RowNum = Count(*) From employee_pto_balance
		print @RowNum
		While @RowNum > 0
		BEGIN
			select @w_emp_id=emp_id, @w_pto_year=pto_year, @w_accrued_vacation_hours=accrued_vacation_hours from employee_pto_balance where pto_year = YEAR(@p_effective_date) and emp_id=@RowNum
			print @w_accrued_vacation_hours
			set @w_accrued_vacation_hours = @w_accrued_vacation_hours - 
					   (select accrued_vacation_hours from employee_pto_accrual_hist 
					    where emp_id = @w_emp_id and accrual_date = @p_effective_date);
			update employee_pto_balance set accrued_vacation_hours = @w_accrued_vacation_hours 
				where emp_id = @w_emp_id and pto_year = (select YEAR(@p_effective_date));
			set @w_next_year_starting_vacation_hours = (select starting_vacation_hours from employee_pto_balance 
							where emp_id = @w_emp_id and pto_year = (select YEAR(@p_effective_date)+1) - (select accrued_vacation_hours from employee_pto_accrual_hist 
							where emp_id = @w_emp_id and accrual_date = @p_effective_date));
			update employee_pto_balance set starting_vacation_hours = @w_next_year_starting_vacation_hours
			where emp_id = @w_emp_id and pto_year = (select YEAR(@p_effective_date)+1);

			set @RowNum = @RowNum - 1
		END
			/* -- delete accrual history records */
			delete from employee_pto_accrual_hist where accrual_date = @p_effective_date;

			/*-- CCH 20170602
			-- delete accrued amount record */
			delete from employee_pto_accrual where accrual_date = @p_effective_date;
	END

END;

GO


--sp_pto_yearly_request_data-stored-procedure---------------------------------------------------------------------------------

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Joanie Solorzano	
-- Create date: 1/4/2019
-- Description:	Yearly Setup of Data
-- =============================================
CREATE PROCEDURE sp_pto_yearly_request_data
	(@p_submit_errors varchar(2048) output)
AS
BEGIN
	DECLARE 
	@w_errors varchar(2048),
	@RowNum int,
	@payRollRowNum int,
	@ptoRecords int,
	@paydayRecords int,
	@w_accrued_vacation_hours numeric(8,5),
	@w_next_year_starting_vacation_hours numeric(8,5),
	@w_rec_id int,
	@w_holiday_date date,
	@w_holiday_desc varchar(150),
	@w_payroll_date date;

BEGIN
	print 'start stored procedure'
	set @w_errors = '';
	select @RowNum = count(*) from employee_pto_holiday;
	select @payRollRowNum = count(*) from employee_bc_payrollperiod;
	print @RowNum
	IF @RowNum = 0
		BEGIN
			set @w_errors = @w_errors + '<Message type=''error''><Text>You have zero holidays set. You must first setup your holidays.</Text></Message>';
		END
	IF (@w_errors <> '') 
		set @p_submit_errors = '<Response>' + @w_errors + '</Response>';
	ELSE 
		set @p_submit_errors = '';
		select @ptoRecords = count(*) from employee_pto_request where pto_type=11 and status_id=-1 and YEAR(request_date) = YEAR(GETDATE());
		While @RowNum > 0 AND @ptoRecords = 0
			BEGIN
				select @w_rec_id=rec_id, @w_holiday_date=holiday_date, @w_holiday_desc=holiday_desc from employee_pto_holiday where rec_id=@RowNum
				print @w_holiday_desc
					BEGIN
					insert into employee_pto_request (resource_id,approver_id,request_date,status_id,approval_date,
						rejection_date,start_date,end_date,size,total_hours,pto_type,start_date_dow,end_date_dow,
						comments,approver_comments,transferred_to_payroll) 
						values (0,0,@w_holiday_date,-1,NULL,NULL,@w_holiday_date,@w_holiday_date,3,8,
							11,NULL,NULL,@w_holiday_desc,NULL,NULL);
					END
				set @RowNum = @RowNum - 1
			END
		
		select @paydayRecords = count(*) from employee_pto_request where pto_type=10 and status_id=-1 and YEAR(request_date) = YEAR(GETDATE());
		While @payRollRowNum > 0 AND @paydayRecords=0
			BEGIN
				select @w_payroll_date=payrolldate from employee_bc_payrollperiod where rec_id=@payRollRowNum
				print @w_payroll_date
					BEGIN
					insert into employee_pto_request (resource_id,approver_id,request_date,status_id,approval_date,
						rejection_date,start_date,end_date,size,total_hours,pto_type,start_date_dow,end_date_dow,
						comments,approver_comments,transferred_to_payroll) 
						values (0,0,@w_payroll_date,-1,NULL,NULL,@w_payroll_date,@w_payroll_date,3,8,
							10,NULL,NULL,NULL,NULL,NULL);
					END
				set @payRollRowNum = @payRollRowNum - 1
			END


END
END
GO

--sp_pto_employee_yearly_pto_data-stored-procedure---------------------------------------------------------------------------------

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Joanie Solorzano	
-- Create date: 1/4/2019
-- Description:	Employee PTO Setup of Data
-- =============================================
CREATE PROCEDURE sp_pto_employee_yearly_pto_data
	(@p_submit_errors varchar(2048) output)
AS
BEGIN
	DECLARE 
	@w_errors varchar(2048)

BEGIN
	print 'start stored procedure'
	set @w_errors = '';

	IF (@w_errors <> '') 
		set @p_submit_errors = '<Response>' + @w_errors + '</Response>';
	ELSE 
		set @p_submit_errors = '';
			BEGIN
				insert into employee_pto_balance 
				select emp_id, YEAR(GETDATE()), 8, (starting_vacation_hours + accrued_vacation_hours - approved_vacation_hours), 0, 0, 0, 0, 40, 0, 0, 0 from employee_pto_balance A join employee_resources B
				on A.emp_id = B.resourceid
				where pto_year = YEAR(DATEADD(Year,-1,GETDATE())) and B.active=1 and B.internal=1; 
			END


END
END
GO


--sp_pto_create_employee_record---------------------------------------------------------------------------------

-- =============================================
-- Author:		Joanie Solorzano	
-- Create date: 2/27/2019
-- Description:	Converted Stored Procedure from postgresql to sql server
-- =============================================
CREATE PROCEDURE [dbo].[sp_pto_create_employee_record]
	-- Add the parameters for the stored procedure here
	(@p_extendername varchar(250),@p_resourcename varchar(250),
	@p_managerid bit,@p_active bit,@p_fulltime bit,@p_internal bit,@p_manager bit,
    @p_email varchar(150),@p_typeid int,@p_roleid int,@p_empid int,
	@p_submit_errors varchar(2048) output)
AS
BEGIN

	DECLARE 
	@w_resourceid varchar(2048),
	@w_errors varchar(2048)

	INSERT INTO employee_resources(extendername,resourcename,managerid,active,fulltime,internal,manager
    ,email,typeid,roleid,empid)
    VALUES 
	(@p_extendername,@p_resourcename, @p_managerid,@p_active,@p_fulltime,@p_internal
	, @p_manager, @p_email, @p_typeid, @p_roleid, @p_empid)

	SELECT @w_resourceid = SCOPE_IDENTITY()
 
	set @p_submit_errors = '<Response><SetSessionProperty>'+ 
  '<SessionProperty name=''S_empid''><Value>' + @w_resourceid + '</Value></SessionProperty>' +
  '</SetSessionProperty>'+
  '</Response>';
	RETURN;
END
GO





