Declare @offCountryID as INT
Declare @defCountryID as INT
Declare @warHit as INT
Declare @lgDR as INT
Declare @fullDR as INT
Declare @drCalc as INT
Declare @defLandGrabCount as INT
Declare @offLandGrabCount as INT

BEGIN TRAN
/** check for war hits made by offense **/

Begin

Select @fullDR = fullDR, @lgDR = lgDR from countries where offCountryID=@offCountryID

if exist(Select ah.defCountryID, ah.attackType
from attackHistory ah inner join countries c on countryID=@defCountryID
where ah.offCountryID=@offCountryID and ah.attackType in ('GS','BR','AB','NM','CM','EM'))


SET @warHit=1 -- is true
SET @drCalc = @fullDR

if not exist(Select ah.defCountryID, ah.attackType
from attackHistory ah inner join countries c on countryID=@defCountryID
where ah.offCountryID=@offCountryID and ah.attackType in ('GS','BR','AB','NM','CM','EM'))

SET @warHit = 0 -- is false

End

/**

count of LGs between offense and defense country
if the two counts are equal then offending country landgrab is considered a new landgrab
if defending country made more LGs against offense country then LGs until equality is reached are considered retal
i.e.
@defLandGrabCount = 4 -- these are landgrabs against offense country
@offLandGrabCount = 4 -- these are landgrabs against defending country
@defLandGrabCount - @offLandGrabCount = 0 -- current land grab is consider new

@defLandGrabCount = 4 -- these are landgrabs against offense country
@offLandGrabCount = 3 -- these are landgrabs against defending country
@defLandGrabCount - @offLandGrabCount = 1 -- current land grab is consider retal

**/

If @warHit = 0 -- the following does not apply to countries a player is at war with!!
Begin

-- (count of LGs made by defending country against offense country)

select @defLandGrabCount = count(attackHistoryID)
from attackHistory ah inner join countries c on countryID=@offCountryID
where ah.defCountryID=@defCountryID and ah.attackType in ('SS','PS'))

-- (count of LGs made by offense country against defending country)

select @offLandGrabCount = count(attackHistoryID)
from attackHistory ah inner join countries c on countryID=@defCountryID
where ah.offCountryID=@offCountryID and ah.attackType in ('SS','PS'))

/** (count of warhits made by defending country against offense country this is to close the loophole of countries using warhits instead of LGs, and possibly thinking these cannot be retaled. A player can technically turn this country into a personal landfarm as long as they do not war hit them back until equality is reached. NOTE: this is inside the @warHit=0 for the offense country meaning the offense country has not attacked defending country with warhits. This advantage disappears as soon as @warHit = 1 or true) **/

select @defWarHitCount = count(attackHistoryID)
from attackHistory ah inner join countries c on countryID=@offCountryID
where ah.defCountryID=@defCountryID and ah.attackType in ('GS','BR','AB','NM','CM','EM'))

-- if less than or equal to zero then it is considered a new landgrab defending country does not have outstanding retals owed

if (@defLandGrabCount+@defWarHitCount) - @offLandGrabCount <=0
begin
SET @drCalc = @warDR
end

-- if greater than zero then it is considered a retal defending country has outstanding retals owed

if (@defLandGrabCount+@defWarHitCount) - @offLandGrabCount > 0
begin
SET @drCalc = @lgDR
end
End

Print 'drCalc = ' + @drCalc

END TRAN

just a quick rough draft at a SQL Stored Procedure to calculate the DRs -- would need to be tested/debugged and realigned to actual tables and fields. -- but it provides the basic psuedo code to make it a reality.

mFrost