SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE [dbo].[PayCalcServiceUpgradeUpdate] (
	@CompanyID int,
	@SetupID int,
	@UpgradeDate datetime,
	@UpgradeOrigSubtotal decimal(9,2),
	@UpgradeOrigScheduleID int,
	@UpgradeOrigService varchar(15),
	@UpgradeTechID1 int,
	@UpgradeTechID2 int,
	@UpgradeTechID3 int,
	@UpgradeTechID4 int,
	@UpgradeTechID5 int,
	@UpgradeTechShare1 Percentage,
	@UpgradeTechShare2 Percentage,
	@UpgradeTechShare3 Percentage,
	@UpgradeTechShare4 Percentage,
	@UpgradeTechShare5 Percentage,
	@UpgradeComment varchar(500),
	@UpgradeSkipMonths char(12),
	@UpgradeUserDef23 varchar(10),
	@UpgradeTechBonus1 Percentage,
	@UpgradeTechBonus2 Percentage,
	@UpgradeTechBonus3 Percentage,
	@UpgradeTechBonus4 Percentage,
	@UpgradeTechBonus5 Percentage
)
AS

SET NOCOUNT ON

DELETE  FROM PayCalcServiceUpgrade WHERE SetupID = @SetupID

DECLARE @UpgradeStartDate datetime,
	@UpgradeEndDate datetime,
	@OriginalStartDate datetime,
	@OriginalEndDate datetime,
	@RemainingOriginalMonths int,
	@UpgradedAnnualValue decimal(9,2),
	@UpgradedMonthlyValue decimal(9,2),
	@UpgradedMonthlyValueDiff decimal(9,2),
	@OriginalMonthlyValue decimal(9,2),
	@OriginalSubtotal decimal(9,2),
	@OriginalRemainingServiceCount int,
	@NewScheduleID int,
	@NewSubtotal decimal(9,2),
	@IsSplit bit

SET @OriginalSubtotal = @UpgradeOrigSubtotal

--Set start date to first of the upgrade month, to end of previous month 1 year away
SET @UpgradeStartDate = dbo.FirstOfMonth(@UpgradeDate)
SET @UpgradeEndDate = DATEADD(dd, -1, DATEADD(yyyy,1,@UpgradeStartDate))

SELECT
@OriginalStartDate = StartDate,
@NewScheduleID = ScheduleID,
@NewSubtotal = SubTotal,
@IsSplit = CASE WHEN SetupType = 'SP' THEN 1 ELSE 0 END
FROM ServiceSetups
WHERE SetupID = @SetupID

SET @OriginalEndDate = DATEADD(yyyy, 1, @OriginalStartDate)

SET @RemainingOriginalMonths = DATEDIFF(mm, @UpgradeStartDate, @OriginalEndDate)
IF @RemainingOriginalMonths < 0
	SET @RemainingOriginalMonths = 0

IF @IsSplit = 0 BEGIN --Regular service
	SET @UpgradedAnnualValue = @NewSubtotal * (dbo.ScheduleCount(@NewScheduleID, @UpgradeStartDate, @UpgradeEndDate)  - 1)

	--Get new annual values amount each time it would generate (new annual value / times setup is going to generate)
	SET @UpgradedMonthlyValue = @UpgradedAnnualValue / (dbo.ScheduleCount(@NewScheduleID, @UpgradeStartDate, @UpgradeEndDate) - 1)

	SET @OriginalRemainingServiceCount = dbo.ScheduleCount(@UpgradeOrigScheduleID, @UpgradeStartDate, @UpgradeEndDate)

	--Annualize out what it would have been (yearly count of old schedule * old subtotal) / new frequency
	SET @OriginalMonthlyValue = (dbo.ScheduleCount(@UpgradeOrigScheduleID, '01/01/2005', CONVERT(datetime, '2005-12-31 11:59 pm', 120)) * @OriginalSubtotal) / dbo.ScheduleCount(@NewScheduleID, @UpgradeStartDate, @UpgradeEndDate)

	--Poor naming convention - but this would be the amount each time the new invoice generates, not always monthly
	SET @UpgradedMonthlyValueDiff = @UpgradedMonthlyValue - @OriginalMonthlyValue
	IF @UpgradedMonthlyValueDiff < 0
		SET @UpgradedMonthlyValueDiff = 0
END
ELSE BEGIN --Is a split

	DECLARE @StartOfMonth datetime,
			@EndOfMonth datetime

	SET @StartOfMonth = DATEADD(month, DATEDIFF(month, 0, @UpgradeDate), 0)

	SET @EndOfMonth = DATEADD(mi,1439,CONVERT(datetime,EOMONTH(@UpgradeDate)))
	--New Subtotal * 12 because amount on setup is split up into the PR invoices, per month
	SET @UpgradedAnnualValue = @NewSubtotal * 12

	--Monthly Value / number of times PR will generate in a month


	IF dbo.ScheduleCount(@UpgradeOrigScheduleID, @StartOfMonth, @EndOfMonth) > dbo.ScheduleCount(@NewScheduleID, @StartOfMonth, @EndOfMonth ) BEGIN

		SET @UpgradedMonthlyValue = @NewSubtotal / dbo.ScheduleCount(@UpgradeOrigScheduleID,  @StartOfMonth, @EndOfMonth)

		--Annualize out what it would have been (yearly count of old schedule * old subtotal) / new frequency
		SET @OriginalMonthlyValue = @OriginalSubtotal / dbo.ScheduleCount(@UpgradeOrigScheduleID,  @StartOfMonth, @EndOfMonth)
	END
	ELSE BEGIN

		SET @UpgradedMonthlyValue = @NewSubtotal / dbo.ScheduleCount(@NewScheduleID, @StartOfMonth, @EndOfMonth)

		--Annualize out what it would have been (yearly count of old schedule * old subtotal) / new frequency
		SET @OriginalMonthlyValue = @OriginalSubtotal / dbo.ScheduleCount(@NewScheduleID, @StartOfMonth, @EndOfMonth)
	END

	SET @OriginalRemainingServiceCount = dbo.ScheduleCountSkip(@UpgradeOrigScheduleID, @UpgradeStartDate, @UpgradeEndDate)

	--Poor naming convention - but this would be the amount each time the new invoice generates, not always monthly
	SET @UpgradedMonthlyValueDiff = @UpgradedMonthlyValue - @OriginalMonthlyValue
	IF @UpgradedMonthlyValueDiff < 0
		SET @UpgradedMonthlyValueDiff = 0
END

INSERT INTO PayCalcServiceUpgrade
(SetupID,UpgradeDate,UpgradeOrigSubtotal,UpgradeOrigScheduleID,UpgradeOrigService, UpgradeTechID1,
UpgradeTechID2,UpgradeTechID3,UpgradeTechID4,UpgradeTechID5,UpgradeTechShare1,UpgradeTechShare2,
UpgradeTechShare3,UpgradeTechShare4,UpgradeTechShare5,UpgradeComment,UpgradeSkipMonths, UpgradeUserDef23,
UpgradeStartDate, UpgradeEndDate, UpgradedAnnualValue, UpgradedMonthlyValue, UpgradedMonthlyValueDiff,
OriginalStartDate, OriginalEndDate, OriginalRemainingServiceCount, OriginalSubtotal, RemainingOriginalMonths,  OriginalMonthlyValue,
UpgradeTechBonus1,UpgradeTechBonus2,UpgradeTechBonus3,UpgradeTechBonus4,UpgradeTechBonus5)

VALUES
(@SetupID,@UpgradeDate,@UpgradeOrigSubtotal,@UpgradeOrigScheduleID,@UpgradeOrigService, @UpgradeTechID1,
@UpgradeTechID2,@UpgradeTechID3,	@UpgradeTechID4,@UpgradeTechID5,	@UpgradeTechShare1,	@UpgradeTechShare2,
@UpgradeTechShare3,	@UpgradeTechShare4,	@UpgradeTechShare5,	@UpgradeComment, @UpgradeSkipMonths, @UpgradeUserDef23,
@UpgradeStartDate, @UpgradeEndDate, @UpgradedAnnualValue, @UpgradedMonthlyValue, @UpgradedMonthlyValueDiff,
@OriginalStartDate, @OriginalEndDate, @OriginalRemainingServiceCount, @OriginalSubtotal, @RemainingOriginalMonths, @OriginalMonthlyValue,
@UpgradeTechBonus1, @UpgradeTechBonus2, @UpgradeTechBonus3, @UpgradeTechBonus4, @UpgradeTechBonus5)

SET NOCOUNT OFF



GO