<%
' MyPAYE
' Module: ExportHolidayReport.asp
'
' This software is the copyright of Mr T J Sheppard and may not be copied, duplicated or 
' modified other than as permitted in the licence agreement.
'
'       Š 2012 - 2020  Mr T J Sheppard
'                      Lyndale House
'                      Church Street
'                      Bradwell
'                      Hope Valley
'                      S33 9HJ
'
'  Tel 0845 643 5191
'  Email Support@MyPAYE.co.uk
'
' MyPAYE is a Registered Trademark of MyPAYE Ltd
'-------------------------------------------------------------------
'
' Updated 14 October 2020
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
'   Description -    This module 
'
' Amendment Log
'------------------------------------------------------------------------------------------
' Date     | Name            | Vers.  | Description
'------------------------------------------------------------------------------------------
' 20/09/18 | DC              | 5.4.01 | Page added
' 21/09/18 | DC              | 5.4.01a| rsMaxID beib=g closed in the wrong place.
' 08/11/18 | TJS             | 5.4.05 | Modified to cater for adjustment values
' 27/12/18 | TJS             | 5.4.10 | Corrected closing of recordset
' 14/10/20 | TJS             | 5.9.01 | Modified to cater for Holiday approval and Leave_Type replacing Unpaid_Leave and Compassionate_Leave flags
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

PageID = 90313
NoBack = False
SessionID = request.querystring("ID")
SessionVarsID = RemoveHTML(request.querystring("SV")) 
SourceFrame = RemoveHTML(request.querystring("S"))
DestFrame = RemoveHTML(request.querystring("D"))
PayrollID = RemoveHTML(request.querystring("PR"))
EmployerID = CheckURLDBIDField(RemoveHTML(request.querystring("ER")), "EmployerID")
EmployeeID = RemoveHTML(request.querystring("EE"))
HolYearStart = cDate(CheckURLDateField(RemoveHTML(request.querystring("HolYear")), "HolYear"))
if RemoveHTML(request.querystring("ShowDet")) = "Yes" then 
	ShowDetails = true 
else
	ShowDetails = false 
end if
%>

<!--#include virtual ="Secure\Functions\CoreValidationRoutines.asp"-->

<!--#include virtual ="Secure\Functions\ConnectDatabaseMain.asp"-->

<!--#include virtual ="Secure\Functions\CreateSessionID.asp"-->

<!--#include virtual ="Secure\Functions\FrameNameConst.asp"-->

<%GetSessionVarsIDRecord(SessionVarsID)%>

<!--#include virtual ="Secure\Functions\DataProtection.asp"-->

<!--#include virtual ="Secure\Functions\URLValidation.asp"-->

<!--#include virtual ="Secure\Functions\CommonPayrollRoutines.asp"-->

<!--#include virtual ="Secure\Functions\LeaveTypeConstants.asp"--> <!-- TJS 14/10/20 -->

<%'
function ConvertForFileName(NameToConvert)

	' can't have spaces in parameters
	ConvertForFileName = replace("" & NameToConvert, " ", "_")
	' this confuses Chrome so the end parameter flag gets included in the file type
	ConvertForFileName = replace(ConvertForFileName, "'", "_")
	' not valid in a file name
	ConvertForFileName = replace(ConvertForFileName, "/", "_")
	ConvertForFileName = replace(ConvertForFileName, "\", "_")
	
end function

' Get some employer details
sql = "SELECT Employer_Name, Holiday_Accrual_Type, Holiday_Calendar_Start_Month, Enable_Holiday_Approval FROM " & DBSchema & "employers WHERE Employer_ID = " & EmployerID ' DC 26/01/18 TJS 01/10/20
set rsEmployer = OpenRecordset(objConn, sql)
EmployerName = rsEmployer("Employer_Name")
Accrual_Type = rsEmployer("Holiday_Accrual_Type")

CurrentHolYearStart = dateserial(year(date()), rsEmployer("Holiday_Calendar_Start_Month"), 1)
if CurrentHolYearStart > date() then
	CurrentHolYearStart = dateadd("yyyy", -1, CurrentHolYearStart)
end if
LastHolYearEnd = dateadd("d", -1, CurrentHolYearStart)

rsEmployer.close
set rsEmployer = nothing

' Calculate end date
HolYearEnd = DateAdd("d", -1, DateAdd("yyyy", 1, HolYearStart))

' Get some payroll details
sql = "SELECT Payroll_Name, Tax_Period, Last_Tax_Period, Tax_Year FROM " & DBSchema & "Payrolls WHERE Payroll_ID = " & PayrollID ' DC 26/01/18
set rsPayroll = OpenRecordset(objConn, sql)
PayrollName = rsPayroll("Payroll_Name")

' Select Employees
sql = "SELECT Employee_ID, Payroll_ID, Payroll_Ref, Fore_Name_1, Fore_Name_2, Last_Name, Annual_Holiday_Allowance, Holiday_Type, Starting_Date, "  
sql = sql & "Leaving_Date FROM " & DBSchema & "Employees WHERE Employer_ID = " & EmployerID & " AND Deleted = 0 AND Transfered_To_Payroll = -1"  
if cLng(PayrollID) > 0 then
	sql = sql & " AND Payroll_ID = " & PayrollID
end if
if cLng(EmployeeID) > 0 then
	sql = sql & " AND Employee_ID = " & EmployeeID
	PayrollName = "N/A"
end if
if ShowDetails then 
	sql = sql & " ORDER BY Holiday_Type, Payroll_ID, Payroll_Sort_Order" 
else
	sql = sql & " ORDER BY Payroll_ID, Payroll_Sort_Order"
end if
set rsEmployees = OpenRecordset(objConn, sql)

' Find first period to report on
sql = "SELECT IFNULL(MIN(Tax_Period), " & rsPayroll("Tax_Period") & ") AS TaxPeriod FROM " & DBSchema & "Pay_Slips WHERE Employer_ID = " & EmployerID
if rsPayroll("Last_Tax_Period") > rsPayroll("Tax_Period") then
	sql = sql & " AND Tax_Year = " & rsPayroll("Tax_Year") - 1
else
	sql = sql & " AND Tax_Year = " & rsPayroll("Tax_Year")
end if
sql = sql & " AND Payroll_ID = " & PayrollID & " AND Pay_Date > " & DBFormatDateTime(LastHolYearEnd, false) & " AND Deleted = 0"
set rsPeriod = OpenRecordset(objConn, sql)


FileName = ConvertForFileName(EmployerName) & "_Holiday_Report" & ".csv"

Response.Clear
response.ContentType = "text/csv" 
response.AddHeader "Content-Disposition", "attachment; filename=" & FileName & ";"

' create header rows
ColumnData = chr(34) & EmployerName & chr(34) & ",,,,," & vbcrlf
response.write ColumnData 

if ShowDetails then 
	ColumnData = chr(34) & "Holiday / Leave Detail Data" & chr(34) & ",,,,,," & vbcrlf
else
	ColumnData = chr(34) & "Holiday / Leave Summary Data" & chr(34) & ",,,,," & vbcrlf
end if
response.write ColumnData 
	
ColumnData = chr(34) & "Date: " & Date() & chr(34) & "," & chr(34) & "Payroll: " & PayrollName & chr(34) & "," & chr(34) & "For Period: " & HolYearStart & " - " & HolYearEnd & chr(34) & ",,,," & vbcrlf
response.write ColumnData

ColumnData = chr(34) & "Emp Ref" & chr(34) & "," & chr(34) & "Employee Name" & chr(34) & ","

if ShowDetails and rsEmployees("Holiday_Type") = "A" then
	ColumnData = ColumnData & chr(34) & "Accrued" & chr(34) & "," & chr(34) & "Taken" & chr(34) & "," & chr(34) & "Date" & chr(34) & "," & chr(34) & "Period" & chr(34) & "," & chr(34) & "Balance" & chr(34)
else
	ColumnData = ColumnData & chr(34) & "Entitlement/Accrued" & chr(34) & "," & chr(34) & "Date" & chr(34) & "," & chr(34) & "Taken" & chr(34) & "," & chr(34) & "Remaining" & chr(34)

	if rsEmployees("Holiday_Type") = "D" then  
		ColumnData = ColumnData & chr(34) & "Unpaid" & chr(34)
	end if 
end if

ColumnData = ColumnData & vbcrlf
response.write ColumnData
	
LastEmployeeHolType = ""

do while not rsEmployees.EOF
	HolidaysUsed = 0
	StartingDate = DecryptData(rsEmployees("Starting_Date")) 
	LeavingDate = DecryptData(rsEmployees("Leaving_Date"))
	PayrollRef = ""
	EmplName = "" 
	strColOne = ""
	strColTwo = ""
	strColThree = ""
	strColFour = ""
	strColFive = ""
		
	' Are/were they a current Employee for report period ?
	if CompareDates(StartingDate, HolYearEnd, True, False, False) AND (CompareDates(LeavingDate, HolYearStart, False, False, True) OR "" & LeavingDate  = "") then
 		
		if rsEmployees("Holiday_Type") = "D" then ' (Has annual days allowance) 
			sql = "SELECT First_Day_Of_Holiday, Number_of_Days, Leave_Type FROM " & DBSchema & "Employee_Holidays WHERE Employee_ID = " ' TJS 14/10/20
			sql = sql & rsEmployees("Employee_ID") & " AND Holiday_Year_Starting >= " & DBFormatDateTime(HolYearStart, false)
			sql = sql & " AND Holiday_Year_Starting <= " & DBFormatDateTime(HolYearEnd, false) & " AND Deleted = 0 ORDER BY First_Day_Of_Holiday" 
			set rsHoliday = OpenRecordset(objConn, sql)
			
			intRemaining = cSng(rsEmployees("Annual_Holiday_Allowance")) 

		elseif rsEmployees("Holiday_Type") = "A" then ' (Accrues holiday pay/time) 
			sql = "SELECT Accrual_ID, Payroll_Type, Tax_Year, Tax_Period, Holiday_Pay_Accrued_Current_Date, Holiday_Pay_Accrued_Prev_Date, Current_Date_Accrued_To, " 
			sql = sql & "Holiday_Pay_Paid, Include_Final_Accrual, Final_Accrual_Value, Adjustment, Adjusted_By FROM " & DBSchema & "Employee_Holiday_Pay_Accrued " ' TJS 08/11/18
			sql = sql & "WHERE Employee_ID = " & rsEmployees("Employee_ID") & " AND Current_Date_Accrued_To >= " & DBFormatDateTime(HolYearStart, false)
			sql = sql & " AND Current_Date_Accrued_To <= " & DBFormatDateTime(HolYearEnd, false) & " AND Deleted = 0 ORDER BY Tax_Year, Tax_Period, Holiday_Pay_Paid, Accrual_ID"	
			set rsHolidayPay = OpenRecordset(objConn, sql)

			sql = "SELECT MAX(Accrual_ID) AS MAX_ID FROM " & DBSchema & "Employee_Holiday_Pay_Accrued WHERE Employee_ID = " & rsEmployees("Employee_ID")
			sql = sql & " AND Current_Date_Accrued_To >= " & DBFormatDateTime(HolYearStart, false) & " AND Current_Date_Accrued_To <= " & DBFormatDateTime(HolYearEnd, false) 
			sql = sql & " AND Deleted = 0"
			set rsMaxID = OpenRecordset(objConn, sql)
		end if
	
		PayrollRef = DecryptData(rsEmployees("Payroll_Ref"))

		EmplName = DecryptData(rsEmployees("Fore_Name_1")) 
		if DecryptData(rsEmployees("Fore_Name_2")) <> "" then
			EmplName = EmplName & " " & DecryptData(rsEmployees("Fore_Name_2")) 
		end if
		
		EmplName = EmplName & " " & DecryptData(rsEmployees("Last_Name"))
	
		if rsEmployees("Holiday_Type") = "D" then ' (Has annual days allowance)
			strColOne = FormatNumber(rsEmployees("Annual_Holiday_Allowance"), 1) & " Days" 		
	
			do while not rsHoliday.EOF 							   		  		
				strColTwo = rsHoliday("First_Day_Of_Holiday")   			
	
				strColThree = FormatNumber(rsHoliday("Number_of_Days"), 1)
		
				if rsHoliday("Leave_Type") <> LeaveType_Compassionate_Leave and rsHoliday("Leave_Type") <> LeaveType_Unpaid_Leave then ' TJS 14/10/20
					intRemaining = intRemaining - cSng(rsHoliday("Number_of_Days"))
				end if

				strColFour = FormatNumber(intRemaining, 1)
		
				if rsHoliday("Leave_Type") = LeaveType_Unpaid_Leave then ' TJS 14/10/20
					strColFive = "X"
					MyPage.AddTextArea MyStrText, 490 + HorizOffset, LineNo + VertOffset, 20, 15, DPDF_Align_Right, DPDF_Font_Helvetica, 10
				end if
	
				ColumnData = chr(34) & PayrollRef & chr(34) & "," & chr(34) & EmplName & chr(34) & "," & chr(34) & strColOne & chr(34) & "," & chr(34) & strColTwo & chr(34) & ","
				ColumnData = ColumnData & chr(34) & strColThree & chr(34) & "," & chr(34) & strColFour & chr(34) & "," & chr(34) & strColFive & chr(34) & vbcrlf
				response.write ColumnData
						
				PayrollRef = ""
				EmplName = "" 
				strColOne = ""
				strColTwo = ""
				strColThree = ""
				strColFour = ""
				strColFive = ""
	
				rsHoliday.movenext
			loop
			rsHoliday.close
			set rsHoliday = nothing

		elseif rsEmployees("Holiday_Type") = "A" then ' (Accrues holiday pay/time)
	
			intMaxID = rsMaxID("MAX_ID")
			TotalPaid = 0 
			TotalAccrued = 0 
			
			if ShowDetails then
				strColTHree = HolYearStart			
				strColFour = "Opening balance"

				if not rsHolidayPay.EOF then
					if Accrual_Type = "M" then ' Money
						strColFive = "Ł" & FormatNumber(rsHolidayPay("Holiday_Pay_Accrued_Prev_Date"), 2)
					else
						strColFive = FormatNumber(rsHolidayPay("Holiday_Pay_Accrued_Prev_Date"), 2) & " Hrs."
					end if
				else
					if Accrual_Type = "M" then ' Money
						strColFive = "Ł0.00"
					else
						strColFive = "0 Hrs."
					end if
				end if
				
				ColumnData = chr(34) & PayrollRef & chr(34) & "," & chr(34) & EmplName & chr(34) & "," & chr(34) & strColOne & chr(34) & "," & chr(34) & strColTwo & chr(34) & ","
				ColumnData = ColumnData & chr(34) & strColThree & chr(34) & "," & chr(34) & strColFour & chr(34) & "," & chr(34) & strColFive & chr(34) & vbcrlf
				response.write ColumnData
						
				PayrollRef = ""
				EmplName = "" 
				strColOne = ""
				strColTwo = ""
				strColThree = ""
				strColFour = ""
				strColFive = ""
			
			end if

			do while not rsHolidayPay.EOF 				
	   			if ShowDetails then
	   				if Accrual_Type = "M" then ' Money
	   					if not cBool(rsHolidayPay("Holiday_Pay_Paid")) then
							strColOne = "Ł" & FormatNumber(rsHolidayPay("Holiday_Pay_Accrued_Current_Date") + rsHolidayPay("Adjustment"), 2) ' TJS 08/11/18
						else
	   						if cBool(rsHolidayPay("Include_Final_Accrual")) then 
								strColTwo = "Ł" & FormatNumber(rsHolidayPay("Final_Accrual_Value") - (rsHolidayPay("Holiday_Pay_Accrued_Current_Date") + rsHolidayPay("Adjustment")), 2) ' TJS 08/11/18
							else
								strColTwo = "Ł" & FormatNumber(0 - (rsHolidayPay("Holiday_Pay_Accrued_Current_Date") + rsHolidayPay("Adjustment")), 2) ' TJS 08/11/18
							end if
						end if
	
					elseif Accrual_Type = "T" then ' Time
	   					if not cBool(rsHolidayPay("Holiday_Pay_Paid")) then
			   			  	strColOne = FormatNumber(rsHolidayPay("Holiday_Pay_Accrued_Current_Date") + rsHolidayPay("Adjustment"), 2) & " Hrs." ' TJS 08/11/18
						else
	   						if cBool(rsHolidayPay("Include_Final_Accrual")) then
								strColTwo = FormatNumber(rsHolidayPay("Final_Accrual_Value") - (rsHolidayPay("Holiday_Pay_Accrued_Current_Date") + rsHolidayPay("Adjustment")), 2) & " Hrs." ' TJS 08/11/18
							else
								strColTwo = FormatNumber(0 - (rsHolidayPay("Holiday_Pay_Accrued_Current_Date") + rsHolidayPay("Adjustment")), 2) & " Hrs." ' TJS 08/11/18
							end if
						end if
		   			end if
	
					strColThree = rsHolidayPay("Current_Date_Accrued_To")   			
	
					if rsHolidayPay("Tax_Period") = 0 then
						strColFour = "Inital YTD entry"
					else
						strColFour = rsHolidayPay("Tax_Year") & "/" & right(rsHolidayPay("Tax_Year") + 1, 2)
						select case rsHolidayPay("Payroll_Type")
							case "W"
								strColFour = strColFour & " Week " & rsHolidayPay("Tax_Period")
							case "B"
								strColFour = strColFour & " Wk " & rsHolidayPay("Tax_Period") * 2 & " (2-W Per. " & rsHolidayPay("Tax_Period") & ")"
							case "F"
								strColFour = strColFour & " Wk " & rsHolidayPay("Tax_Period") * 4 & " (4-W Per. " & rsHolidayPay("Tax_Period") & ")"
							case "M"
								strColFour = strColFour & " Month " & rsHolidayPay("Tax_Period")
							case "Q"
								strColFour = strColFour & " Quarter " & rsHolidayPay("Tax_Period")
							case else
								strColFour = "Tax Year " & strColFour 
						end select
					end if
	
	   				if Accrual_Type = "M" then ' Money
  						if cBool(rsHolidayPay("Include_Final_Accrual")) then ' TJS 13/07/18
							strColFive = "Ł" & FormatNumber(rsHolidayPay("Holiday_Pay_Accrued_Current_Date") + rsHolidayPay("Holiday_Pay_Accrued_Prev_Date") - rsHolidayPay("Final_Accrual_Value"), 2) 
						else
							strColFive = "Ł" & FormatNumber(rsHolidayPay("Holiday_Pay_Accrued_Current_Date") + rsHolidayPay("Holiday_Pay_Accrued_Prev_Date"), 2)
						end if
	
					elseif Accrual_Type = "T" then ' Time
  						if cBool(rsHolidayPay("Include_Final_Accrual")) then ' TJS 13/07/18
				   			strColFive = FormatNumber(rsHolidayPay("Holiday_Pay_Accrued_Current_Date") + rsHolidayPay("Holiday_Pay_Accrued_Prev_Date") - rsHolidayPay("Final_Accrual_Value"), 2) & " Hrs." 
						else
				   			strColFive = FormatNumber(rsHolidayPay("Holiday_Pay_Accrued_Current_Date") + rsHolidayPay("Holiday_Pay_Accrued_Prev_Date"), 2) & " Hrs."
						end if
	
		   			end if

					ColumnData = chr(34) & PayrollRef & chr(34) & "," & chr(34) & EmplName & chr(34) & "," & chr(34) & strColOne & chr(34) & "," & chr(34) & strColTwo & chr(34) & ","
					ColumnData = ColumnData & chr(34) & strColThree & chr(34) & "," & chr(34) & strColFour & chr(34) & "," & chr(34) & strColFive & chr(34) & vbcrlf
					response.write ColumnData
						
					PayrollRef = ""
					EmplName = "" 
					strColOne = ""
					strColTwo = ""
					strColThree = ""
					strColFour = ""
					strColFive = ""
							
		   		elseif cBool(rsHolidayPay("Holiday_Pay_Paid")) or rsHolidayPay("Accrual_ID") = intMaxID then
			  		if Accrual_Type = "M" then ' Money
		   			  	if cBool(rsHolidayPay("Holiday_Pay_Paid")) then
			  			   	TotalPaid = TotalPaid + (cDbl(rsHolidayPay("Holiday_Pay_Accrued_Current_Date")) * -1) + rsHolidayPay("Final_Accrual_Value")
						
		   			  	elseif rsHolidayPay("Accrual_ID") = intMaxID then
		   			  		strColOne = "Ł" & FormatNumber(TotalAccrued + cDbl(rsHolidayPay("Holiday_Pay_Accrued_Current_Date")), 2)		
		   			  		strColTwo = rsHolidayPay("Current_Date_Accrued_To")   			
							
							if TotalPaid > 0 then 
								strColThree = "Ł" & FormatNumber(TotalPaid, 2) 						
								strColFour = "Ł"  & FormatNumber((rsHolidayPay("Holiday_Pay_Accrued_Current_Date") + rsHolidayPay("Holiday_Pay_Accrued_Prev_Date")), 2)
							end if 	
						else
							TotalAccrued = TotalAccrued + cDbl(rsHolidayPay("Holiday_Pay_Accrued_Current_Date"))
						end if
						
					elseif Accrual_Type = "T" then 'Time
						if cBool(rsHolidayPay("Holiday_Pay_Paid")) then
			  			   	TotalPaid = TotalPaid + (cDbl(rsHolidayPay("Holiday_Pay_Accrued_Current_Date")) * -1) + rsHolidayPay("Final_Accrual_Value") 
						
		   			  	elseif rsHolidayPay("Accrual_ID") = intMaxID then
		   			  		strColOne = FormatNumber(TotalAccrued + cDbl(rsHolidayPay("Holiday_Pay_Accrued_Current_Date")), 2) & " Hrs."
		   			  		strColTwo = rsHolidayPay("Current_Date_Accrued_To")   			
								
							if TotalPaid > 0 then
								strColThree = FormatNumber(TotalPaid, 2) & " Hrs."							
								strColFour = FormatNumber((rsHolidayPay("Holiday_Pay_Accrued_Current_Date") + rsHolidayPay("Holiday_Pay_Accrued_Prev_Date")), 2) & " Hrs."
							end if  ' DC 15/02/18								
						else
							TotalAccrued = TotalAccrued + cDbl(rsHolidayPay("Holiday_Pay_Accrued_Current_Date"))
						end if
					end if						
				else
					TotalAccrued = TotalAccrued + cDbl(rsHolidayPay("Holiday_Pay_Accrued_Current_Date"))
				end if
			
				rsHolidayPay.movenext
			loop
			
			ColumnData = chr(34) & PayrollRef & chr(34) & "," & chr(34) & EmplName & chr(34) & "," & chr(34) & strColOne & chr(34) & "," & chr(34) & strColTwo & chr(34) & ","
			ColumnData = ColumnData & chr(34) & strColThree & chr(34) & "," & chr(34) & strColFour & chr(34) & "," & chr(34) & strColFive & chr(34) & vbcrlf
			response.write ColumnData	
			
			rsMaxID.close ' DC 21/09/18
			set rsMaxID	= nothing  ' DC 21/09/18
			
			' start of code moved TJS 27/12/18
			rsHolidayPay.close
			set rsHolidayPay = nothing
			' end of code moved TJS 27/12/18
			
		end if

	end if
	
	LastEmployeeHolType = "" & rsEmployees("Holiday_Type")
	rsEmployees.movenext
loop
	
rsEmployees.close
set rsEmployees = nothing
rsPayroll.close 
set rsPayroll = nothing 
rsPeriod.close
set rsPeriod = nothing 

ReleaseASPEncrypt
SaveSessionVarsRecord SessionVarsID 

objConn.close
set objConn = nothing
%>