USE [SRO_VT_SHARD]
GO
truncate table _SiegeFortressStruct
-- Fortress JA
INSERT INTO [_SiegeFortressStruct]
SELECT
1 as FortressID, -- FortressID
0 as OwnerGuildID , -- OwnerGuildID
_RefEventZone.ID as RefEventStructID,
_RefObjCommon.ID as RefObjID,
_RefObjChar.MaxHP as HP,
GETDATE() as MakeDate,
0 as State -- State
FROM
_RefEventZone
JOIN [_RefObjCommon] ON _RefObjCommon.CodeName128 = _RefEventZone.strParam1
JOIN [_RefObjChar] ON _RefObjChar.ID = _RefObjCommon.Link
WHERE _RefEventZone.strParam2 = 'INS_FORT_JA'
-- Fortress HT
INSERT INTO [_SiegeFortressStruct]
SELECT
3 as FortressID, -- FortressID
0 as OwnerGuildID , -- OwnerGuildID
_RefEventZone.ID as RefEventStructID,
_RefObjCommon.ID as RefObjID,
_RefObjChar.MaxHP as HP,
GETDATE() as MakeDate,
0 as State -- State
FROM
_RefEventZone
JOIN [_RefObjCommon] ON _RefObjCommon.CodeName128 = _RefEventZone.strParam1
JOIN [_RefObjChar] ON _RefObjChar.ID = _RefObjCommon.Link
WHERE _RefEventZone.strParam2 = 'INS_FORT_HT'
-- Fortress CT
INSERT INTO [_SiegeFortressStruct]
SELECT
4 as FortressID, -- FortressID
0 as OwnerGuildID , -- OwnerGuildID
_RefEventZone.ID as RefEventStructID,
_RefObjCommon.ID as RefObjID,
_RefObjChar.MaxHP as HP,
GETDATE() as MakeDate,
0 as State -- State
FROM
_RefEventZone
JOIN [_RefObjCommon] ON _RefObjCommon.CodeName128 = _RefEventZone.strParam1
JOIN [_RefObjChar] ON _RefObjChar.ID = _RefObjCommon.Link
WHERE _RefEventZone.strParam2 = 'INS_FORT_CT'
-- Fortress BJ
INSERT INTO [_SiegeFortressStruct]
SELECT
6 as FortressID, -- FortressID
0 as OwnerGuildID , -- OwnerGuildID
_RefEventZone.ID as RefEventStructID,
_RefObjCommon.ID as RefObjID,
_RefObjChar.MaxHP as HP,
GETDATE() as MakeDate,
0 as State -- State
FROM
_RefEventZone
JOIN [_RefObjCommon] ON _RefObjCommon.CodeName128 = _RefEventZone.strParam1
JOIN [_RefObjChar] ON _RefObjChar.ID = _RefObjCommon.Link
WHERE _RefEventZone.strParam2 = 'INS_FORT_BJ'
DELETE _SiegeFortressStruct WHERE HP = 80000
--UPDATE _SiegeFortressStruct SET HP = 0 WHERE HP = 80000
USE [SRO_VT_SHARD]
GO
IF OBJECT_ID('dbo.usp_InitSiegeFortressStructs', 'P') IS NOT NULL
DROP PROCEDURE dbo.usp_InitSiegeFortressStructs
GO
CREATE PROCEDURE dbo.usp_InitSiegeFortressStructs
[MENTION=486320]forcer[/MENTION]ebuild BIT = 0, -- Set to 1 to truncate and rebuild
[MENTION=992054]Fortress[/MENTION]ID INT = NULL -- NULL = all fortresses, or specify 1/3/4/6
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
-- Fortress definitions
DECLARE [MENTION=992054]Fortress[/MENTION]es TABLE (
FortressID INT,
FilterParam NVARCHAR(50)
);
INSERT INTO [MENTION=992054]Fortress[/MENTION]es VALUES
(1, 'INS_FORT_JA'),
(3, 'INS_FORT_HT'),
(4, 'INS_FORT_CT'),
(6, 'INS_FORT_BJ');
-- Validate FortressID if provided
IF [MENTION=992054]Fortress[/MENTION]ID IS NOT NULL AND NOT EXISTS (
SELECT 1 FROM [MENTION=992054]Fortress[/MENTION]es WHERE FortressID = [MENTION=992054]Fortress[/MENTION]ID
)
BEGIN
RAISERROR('Invalid [MENTION=992054]Fortress[/MENTION]ID [%d]. Valid values: 1 (JA), 3 (HT), 4 (CT), 6 (BJ).', 16, 1, [MENTION=992054]Fortress[/MENTION]ID);
RETURN;
END
BEGIN TRY
BEGIN TRANSACTION;
-- Force rebuild: delete target records
IF [MENTION=486320]forcer[/MENTION]ebuild = 1
BEGIN
IF [MENTION=992054]Fortress[/MENTION]ID IS NULL
TRUNCATE TABLE _SiegeFortressStruct;
ELSE
DELETE FROM _SiegeFortressStruct
WHERE FortressID = [MENTION=992054]Fortress[/MENTION]ID;
END
-- Insert missing records
INSERT INTO [_SiegeFortressStruct]
SELECT
f.FortressID,
0 AS OwnerGuildID,
ez.ID AS RefEventStructID,
oc.ID AS RefObjID,
och.MaxHP AS HP,
GETDATE() AS MakeDate,
0 AS State
FROM [MENTION=992054]Fortress[/MENTION]es f
CROSS APPLY (
SELECT ID, strParam1
FROM _RefEventZone
WHERE strParam2 = f.FilterParam
) ez
JOIN _RefObjCommon oc ON oc.CodeName128 = ez.strParam1
JOIN _RefObjChar och ON och.ID = oc.Link
WHERE [MENTION=992054]Fortress[/MENTION]ID IS NULL OR f.FortressID = [MENTION=992054]Fortress[/MENTION]ID)
AND och.MaxHP <> 80000
AND NOT EXISTS (
SELECT 1
FROM _SiegeFortressStruct sfs
WHERE sfs.FortressID = f.FortressID
AND sfs.RefEventStructID = ez.ID
AND sfs.RefObjID = oc.ID
);
-- Clean up any existing 80000 HP records
DELETE FROM _SiegeFortressStruct
WHERE HP = 80000
AND [MENTION=992054]Fortress[/MENTION]ID IS NULL OR FortressID = [MENTION=992054]Fortress[/MENTION]ID);
COMMIT TRANSACTION;
-- Summary
SELECT
FortressID,
COUNT(*) AS StructCount,
MIN(HP) AS MinHP,
MAX(HP) AS MaxHP
FROM _SiegeFortressStruct
WHERE [MENTION=992054]Fortress[/MENTION]ID IS NULL OR FortressID = [MENTION=992054]Fortress[/MENTION]ID)
GROUP BY FortressID
ORDER BY FortressID;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
THROW;
END CATCH
END
GO
Code:
-- Safe insert all fortresses (skip existing)
EXEC dbo.usp_InitSiegeFortressStructs;
-- Full rebuild all fortresses
EXEC dbo.usp_InitSiegeFortressStructs [MENTION=486320]forcer[/MENTION]ebuild = 1;
-- Safe insert only Fortress JA
EXEC dbo.usp_InitSiegeFortressStructs [MENTION=992054]Fortress[/MENTION]ID = 1;
-- Rebuild only Fortress HT
EXEC dbo.usp_InitSiegeFortressStructs [MENTION=486320]forcer[/MENTION]ebuild = 1, [MENTION=992054]Fortress[/MENTION]ID = 3;
-- Invalid ID → error message
EXEC dbo.usp_InitSiegeFortressStructs [MENTION=992054]Fortress[/MENTION]ID = 99;
Constantinople Fortress 11/27/2012 - SRO Private Server - 99 Replies Who else will work to add it on your own servers?
SRO - Constantinople Fortress - YouTube
The serverfiles are ready for support more Fortress, so it shouldn't be a problem to add.
Constantinople Fortress 07/25/2012 - SRO Private Server - 3 Replies Hi guys,
i need some help about constantinople fortress
everything is okay on db no errors everything is normal
but the client .. how can i say it lol
first there is no Fortress building ( teleport building on map (M) )
and i add it on map.pk2 but nothing ;(
i can't even click on them
if i warp - wp
new constantinople fortress war images 07/23/2012 - SRO Private Server - 2 Replies hello, today i found some others fortresswars images so i want to share them
maybe u already had them but maybe some others no.
http://i.epvpimg.com/vgDOg.png
http://i.epvpimg.com/ig3kf.png
http://i.epvpimg.com/Gnv7f.png
http://i.epvpimg.com/dRfbh.png
Help# Constantinople fortress 07/22/2012 - SRO Private Server - 4 Replies Hey guys. I've added Constantinople Fortress to my server. I'm not getting any error before importing the siege files to my media.pk2. But when i do, I'm not being able to teleport to the Constantinople Fortress because client is crashing. I'm working on this problem to solve for several days, and i have no patience anymore.. I'm getting angry hour by hour.. I'll be happy if you can help me..
Thank you all for everything..
Constantinople Fortress 07/21/2012 - Silkroad Online - 8 Replies joymax is adding it on sro-r, they doing something new, just seen the changelog, no screens yet
