[HELP] Siege script every week

conserra3 · 08/31/2020, 14:32

can someone give me siege query for every week siege ? thank you for your replies ^_^

asnar002 · 08/31/2020, 18:45

there are so many threads. learn to use search.

nemesidevmv · 08/31/2020, 19:17

USE [Character]
GO
/****** Object: UserDefinedFunction [dbo].[FN_BinDateToDateTime] Script Date: 10/12/2020 by ULTIMO ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER FUNCTION [dbo].[FN_BinDateToDateTime] (
@i_bin_time binary(4)
) RETURNS datetime
AS
BEGIN
DECLARE @v_datetime datetime
DECLARE @v_strAux varchar(20)
DECLARE @v_strtime varchar(20)
SET @v_strAux = CAST(CAST(@i_bin_time as int) as varchar(20))
IF len(@v_strAux) = 7
SELECT @v_strtime = '20200' + CAST(CAST(@i_bin_time as int) as varchar(20))
ELSE
IF len(@v_strAux) = 8
SELECT @v_strtime = '2020' + CAST(CAST(@i_bin_time as int) as varchar(20))
ELSE
SELECT @v_strtime = '202' + CAST(CAST(@i_bin_time as int) as varchar(20))

SELECT @v_strtime = SUBSTRING(@v_strtime,1,4)
+'-'+SUBSTRING(@v_strtime,5,2)
+'-'+SUBSTRING(@v_strtime,7,2)
+' '+SUBSTRING(@v_strtime,9,2)
+':'+SUBSTRING(@v_strtime,11,2)
SELECT @v_datetime = CAST(@v_strtime as datetime)
RETURN @v_datetime
END

////////////////////////////////////////////////////////////////

USE [Character];
GO
DECLARE @v_siege_no char(10)
DECLARE @

_code varchar(10)
DECLARE @

_name varchar(30)
DECLARE @v_strDate varchar(10)
DECLARE @

egdate varchar(10)
DECLARE @v_strChannel varchar(2)
DECLARE @i_bychannel tinyint
DECLARE @o_dwDungeonRegistTime varbinary(4)
DECLARE @o_DeadFrontTimeInfo varbinary(1000)
DECLARE @o_sp_rtn int
DECLARE @o_dwRegistTimeStart varbinary(4)
DECLARE @o_dwRegistTimeEnd varbinary(4)
DECLARE @o_dwTaxRegistTime varbinary(4)
DECLARE @o_dwStartTime varbinary(4)
DECLARE @o_dwRegistTime varbinary(4)
DECLARE @v_strDate1 varchar(10)
DECLARE @i_GetDate datetime
DECLARE @v_siege_start_date varchar(14)
DECLARE @v_siege_start_time varbinary(4)
SET @i_GetDate=getdate()
-- Siege War Every Saturday:
SELECT @v_siege_start_date = CONVERT(VARCHAR(10), DATEADD(d, 7-DATEPART(dw, @i_GetDate), @i_GetDate), 112) + '210000'
--
SELECT @v_siege_start_time = SUBSTRING(@v_siege_start_date,4,1) * 100000000
+ SUBSTRING(@v_siege_start_date,5,2) * 1000000
+ SUBSTRING(@v_siege_start_date,7,2) * 10000
+ SUBSTRING(@v_siege_start_date,9,2) * 100
+ SUBSTRING(@v_siege_start_date,11,2)
SET @

_code = '001'
SET @

_name = 'FirstSiege'
SET @v_siege_no = SUBSTRING(dbo.FN_DateToShortStrDate(GetDate()), 3, 4) + '0001'
INSERT
INTO GUILD_INFO(guild_code, guild_name, guild_Level, bystate, bychannel)
VALUES @

_code, @

_name, 3, 1, 1)
INSERT
INTO SIEGE_INFO(SIEGE_NO, CHANNEL_NO, GUILD_CODE, GUILD_NAME, DWSTARTTIME, BYREGISTTIMEFLAG, DEFENDER_INFO, SIEGE_TAG)
VALUES (@v_siege_no, 1, @

_code, @

_name,@v_siege_start_time, 1, 0X0000, 'Y')
EXEC SP_SIEGE_START_TIME_U @

_code,1,0x01,@v_siege_start_time output,@o_dwRegistTime output,@o_sp_rtn output
SET @v_strDate1=dbo.FN_BinDateToDateTime(@v_siege_star t_time)
DECLARE @P1 varchar(13)
SET @P1=NULL
DECLARE @P2 varchar(33)
SET @P2=NULL
DECLARE @P3 varchar(25)
SET @P3=NULL
DECLARE @P4 varbinary(4)
SET @P4=NULL
DECLARE @P5 varbinary(4)
SET @P5=NULL
DECLARE @P6 varbinary(4)
SET @P6=NULL
DECLARE @P7 varbinary(4)
SET @P7=NULL
DECLARE @P8 varbinary(4)
SET @P8=NULL
DECLARE @P9 varbinary(4)
SET @P9=NULL
DECLARE @P10 varbinary(4)
SET @P10=NULL
DECLARE @

varbinary(2)
SET @

=NULL
DECLARE @

varbinary(2)
SET @

=NULL
DECLARE @P13 varbinary(2)
SET @P13=NULL
DECLARE @

int
SET @

=NULL
DECLARE @P15 int
SET @P15=NULL
DECLARE @P16 varbinary(4)
SET @P16=NULL
DECLARE @

varbinary(996)
SET @

=NULL
DECLARE @

varbinary(996)
SET @

=NULL
DECLARE @

varbinary(1000)
SET @

=NULL
DECLARE @

int
SET @

=NULL
EXEC SP_SIEGE_INFO_R 1, @P1 output, @P2 output, @P3 output, @P4 output, @P5 output, @P6 output, @P7 output, @P8 output, @P9 output, @P10 output, @

output, @

output, @P13 output, @

output, @P15 output, @P16 output, @

output, @

output
SELECT @P1, @P2, @P3, @P4, @P5, @P6,@v_strDate1

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

yami1203 · 09/17/2020, 12:39

Quote:

Originally Posted by nemesidevmv

USE [Character]
GO
/****** Object: UserDefinedFunction [dbo].[FN_BinDateToDateTime] Script Date: 10/12/2020 by ULTIMO ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER FUNCTION [dbo].[FN_BinDateToDateTime] (
@i_bin_time binary(4)
) RETURNS datetime
AS
BEGIN
DECLARE @v_datetime datetime
DECLARE @v_strAux varchar(20)
DECLARE @v_strtime varchar(20)
SET @v_strAux = CAST(CAST(@i_bin_time as int) as varchar(20))
IF len(@v_strAux) = 7
SELECT @v_strtime = '20200' + CAST(CAST(@i_bin_time as int) as varchar(20))
ELSE
IF len(@v_strAux) = 8
SELECT @v_strtime = '2020' + CAST(CAST(@i_bin_time as int) as varchar(20))
ELSE
SELECT @v_strtime = '202' + CAST(CAST(@i_bin_time as int) as varchar(20))

SELECT @v_strtime = SUBSTRING(@v_strtime,1,4)
+'-'+SUBSTRING(@v_strtime,5,2)
+'-'+SUBSTRING(@v_strtime,7,2)
+' '+SUBSTRING(@v_strtime,9,2)
+':'+SUBSTRING(@v_strtime,11,2)
SELECT @v_datetime = CAST(@v_strtime as datetime)
RETURN @v_datetime
END

////////////////////////////////////////////////////////////////

USE [Character];
GO
DECLARE @v_siege_no char(10)
DECLARE @guild_code varchar(10)
DECLARE @guild_name varchar(30)
DECLARE @v_strDate varchar(10)
DECLARE @egdate varchar(10)
DECLARE @v_strChannel varchar(2)
DECLARE @i_bychannel tinyint
DECLARE @o_dwDungeonRegistTime varbinary(4)
DECLARE @o_DeadFrontTimeInfo varbinary(1000)
DECLARE @o_sp_rtn int
DECLARE @o_dwRegistTimeStart varbinary(4)
DECLARE @o_dwRegistTimeEnd varbinary(4)
DECLARE @o_dwTaxRegistTime varbinary(4)
DECLARE @o_dwStartTime varbinary(4)
DECLARE @o_dwRegistTime varbinary(4)
DECLARE @v_strDate1 varchar(10)
DECLARE @i_GetDate datetime
DECLARE @v_siege_start_date varchar(14)
DECLARE @v_siege_start_time varbinary(4)
SET @i_GetDate=getdate()
-- Siege War Every Saturday:
SELECT @v_siege_start_date = CONVERT(VARCHAR(10), DATEADD(d, 7-DATEPART(dw, @i_GetDate), @i_GetDate), 112) + '210000'
--
SELECT @v_siege_start_time = SUBSTRING(@v_siege_start_date,4,1) * 100000000
+ SUBSTRING(@v_siege_start_date,5,2) * 1000000
+ SUBSTRING(@v_siege_start_date,7,2) * 10000
+ SUBSTRING(@v_siege_start_date,9,2) * 100
+ SUBSTRING(@v_siege_start_date,11,2)
SET @guild_code = '001'
SET @guild_name = 'FirstSiege'
SET @v_siege_no = SUBSTRING(dbo.FN_DateToShortStrDate(GetDate()), 3, 4) + '0001'
INSERT
INTO GUILD_INFO(guild_code, guild_name, guild_Level, bystate, bychannel)
VALUES (@guild_code, @guild_name, 3, 1, 1)
INSERT
INTO SIEGE_INFO(SIEGE_NO, CHANNEL_NO, GUILD_CODE, GUILD_NAME, DWSTARTTIME, BYREGISTTIMEFLAG, DEFENDER_INFO, SIEGE_TAG)
VALUES (@v_siege_no, 1, @guild_code, @guild_name,@v_siege_start_time, 1, 0X0000, 'Y')
EXEC SP_SIEGE_START_TIME_U @guild_code,1,0x01,@v_siege_start_time output,@o_dwRegistTime output,@o_sp_rtn output
SET @v_strDate1=dbo.FN_BinDateToDateTime(@v_siege_star t_time)
DECLARE @P1 varchar(13)
SET @P1=NULL
DECLARE @P2 varchar(33)
SET @P2=NULL
DECLARE @P3 varchar(25)
SET @P3=NULL
DECLARE @P4 varbinary(4)
SET @P4=NULL
DECLARE @P5 varbinary(4)
SET @P5=NULL
DECLARE @P6 varbinary(4)
SET @P6=NULL
DECLARE @P7 varbinary(4)
SET @P7=NULL
DECLARE @P8 varbinary(4)
SET @P8=NULL
DECLARE @P9 varbinary(4)
SET @P9=NULL
DECLARE @P10 varbinary(4)
SET @P10=NULL
DECLARE @P11 varbinary(2)
SET @P11=NULL
DECLARE @P12 varbinary(2)
SET @P12=NULL
DECLARE @P13 varbinary(2)
SET @P13=NULL
DECLARE @P14 int
SET @P14=NULL
DECLARE @P15 int
SET @P15=NULL
DECLARE @P16 varbinary(4)
SET @P16=NULL
DECLARE @ varbinary(996)
SET @=NULL
DECLARE @ varbinary(996)
SET @=NULL
DECLARE @ varbinary(1000)
SET @=NULL
DECLARE @ int
SET @=NULL
EXEC SP_SIEGE_INFO_R 1, @P1 output, @P2 output, @P3 output, @P4 output, @P5 output, @P6 output, @P7 output, @P8 output, @P9 output, @P10 output, @P11 output, @P12 output, @P13 output, @P14 output, @P15 output, @P16 output, @ output, @ output, @ output, @ output
SELECT @P1, @P2, @P3, @P4, @P5, @P6,@v_strDate1

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

doesn't work on a9 i think?

nemesidevmv · 09/17/2020, 19:18

try I think it works

~~[DEV]Dekaron~~ · 09/17/2020, 21:17

Quote:

Originally Posted by yami1203

doesn't work on a9 i think?

yami1203 · 09/19/2020, 16:21

Got some errors on SP_SIEGE_INFO

and I tried editing FN_GetSiegeStartBinTime. But it says Next siege will start 2014 7/9 xD

nemesidevmv · 09/20/2020, 04:33

change the date

yami1203 · 09/21/2020, 18:13

Quote:

Originally Posted by nemesidevmv

change the date

I'm sorry man. I too nub for this </3 I don't really understand these scripts

~~[DEV]Dekaron~~ · 09/21/2020, 18:22

then when you learn, you can comeback and ask about something else.

but probably your server have problem with date and time, here is on forum some script that fixing this problem.

(sorry man but how we can help you if you do not understand basic)

nemesidevmv · 09/21/2020, 23:34

I think that date you have to change haha

yami1203 · 09/22/2020, 09:29

Quote:

Originally Posted by nemesidevmv

I think that date you have to change haha

I've change it but it broke the siege xD

btw scripts i'm using rn and that script you post are kinda different.

nemesidevmv · 09/22/2020, 15:20

are 2 script

yami1203 · 09/23/2020, 13:27

Quote:

Originally Posted by nemesidevmv

are 2 script

USE [CHARACTER]
GO
/****** Object: UserDefinedFunction [dbo].[FN_GetSiegeStartBinTime] Script Date: 9/22/2020 12:28:39 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/************************************************** ****************************
이 름 : dbo.FN_GetSiegeStartBinTime
설 명 : 다음 공성전 시작일자를 반환한다.
예 제 : select dbo.FN_GetSiegeStartTime --//반환값 : 0x23E52D6C
수정내역:
Ver Date Author Description
--------- ---------- --------------- -----------------------------------
1.0 2006-05-05 Han Ji Wook 1. 초기생성
1.1 2006-05-11 Han Ji Wook 1. 다음공성전 시작시간 변경 >> 2주후 일요일
1.2 2009-12-22 Park Chanik 1. 공성 2010년 에러 관련 변경
************************************************** ****************************/
ALTER FUNCTION [dbo].[FN_GetSiegeStartBinTime]
(
@i_GetDate DATETIME
) RETURNS VARBINARY(4)
AS
BEGIN
DECLARE @v_siege_start_time VARBINARY(4)
DECLARE @v_siege_start_date VARCHAR(14)
--// 다음공성전 시작시간 Default Setting : 일요일 9시
SELECT @v_siege_start_date = CONVERT(VARCHAR(10), DATEADD(d, 8-DATEPART(dw, @i_GetDate), @i_GetDate), 112) + '210000'

-- 2009년 12월 22일 변경
-- CAST(SUBSTRING(@v_siege_start_date,4,1) AS INT)* 100000000 ---> CAST(SUBSTRING(@v_siege_start_date,3,2) AS INT)* 100000000
SELECT @v_siege_start_time = CAST(SUBSTRING(@v_siege_start_date,4,1) AS INT) * 100000000
+ CAST(SUBSTRING(@v_siege_start_date,5,2) AS INT) * 1000000
+ CAST(SUBSTRING(@v_siege_start_date,7,2) AS INT) * 10000
+ CAST(SUBSTRING(@v_siege_start_date,9,2) AS INT) * 100
+ CAST(SUBSTRING(@v_siege_start_date,11,2) AS INT)

IF DATALENGTH(@v_siege_start_time) <> 4
BEGIN
SELECT @v_siege_start_time = 0x00000000
END

RETURN @v_siege_start_time
END

and

USE [CHARACTER]
GO
/****** Object: UserDefinedFunction [dbo].[FN_BinDateToDateTime] Script Date: 9/23/2020 7:26:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

/************************************************** ****************************
이 름 : dbo.FN_BinDateToDateTime
설 명 : Binary Date를 DateTime 형식으로 반환한다.
반환값 :
수정내역:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2006-05-05 Han Ji Wook 1. 초기생성
1.1 2009-12-22 Park Chanik 1. 공성 2010년 에러 관련 변경 , 2010년 구분 처리
************************************************** ****************************/
ALTER FUNCTION [dbo].[FN_BinDateToDateTime] (
@i_bin_time BINARY(4)
) RETURNS DATETIME
AS
BEGIN
DECLARE @v_datetime DATETIME
DECLARE @v_strtime VARCHAR(20)

-- 2009년 12월 22일 변경
IF LEN(CAST(@i_bin_time AS INT)) < 10
BEGIN
SELECT @v_strtime = '200' + CAST(CAST(@i_bin_time AS INT) AS VARCHAR(20))
END
ELSE
BEGIN
SELECT @v_strtime = '20' + CAST(CAST(@i_bin_time AS INT) AS VARCHAR(20))
END
SELECT @v_strtime = SUBSTRING(@v_strtime,1,4)
+'-'+SUBSTRING(@v_strtime,5,2)
+'-'+SUBSTRING(@v_strtime,7,2)
+' '+SUBSTRING(@v_strtime,9,2)
+':'+SUBSTRING(@v_strtime,11,2)
SELECT @v_datetime = CAST(@v_strtime AS DATETIME)
RETURN @v_datetime
END

conserra3 · 09/23/2020, 17:42

Quote:

Originally Posted by nemesidevmv

USE [Character]
GO
/****** Object: UserDefinedFunction [dbo].[FN_BinDateToDateTime] Script Date: 10/12/2020 by ULTIMO ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER FUNCTION [dbo].[FN_BinDateToDateTime] (
@i_bin_time binary(4)
) RETURNS datetime
AS
BEGIN
DECLARE @v_datetime datetime
DECLARE @v_strAux varchar(20)
DECLARE @v_strtime varchar(20)
SET @v_strAux = CAST(CAST(@i_bin_time as int) as varchar(20))
IF len(@v_strAux) = 7
SELECT @v_strtime = '20200' + CAST(CAST(@i_bin_time as int) as varchar(20))
ELSE
IF len(@v_strAux) = 8
SELECT @v_strtime = '2020' + CAST(CAST(@i_bin_time as int) as varchar(20))
ELSE
SELECT @v_strtime = '202' + CAST(CAST(@i_bin_time as int) as varchar(20))

SELECT @v_strtime = SUBSTRING(@v_strtime,1,4)
+'-'+SUBSTRING(@v_strtime,5,2)
+'-'+SUBSTRING(@v_strtime,7,2)
+' '+SUBSTRING(@v_strtime,9,2)
+':'+SUBSTRING(@v_strtime,11,2)
SELECT @v_datetime = CAST(@v_strtime as datetime)
RETURN @v_datetime
END

////////////////////////////////////////////////////////////////

USE [Character];
GO
DECLARE @v_siege_no char(10)
DECLARE @guild_code varchar(10)
DECLARE @guild_name varchar(30)
DECLARE @v_strDate varchar(10)
DECLARE @egdate varchar(10)
DECLARE @v_strChannel varchar(2)
DECLARE @i_bychannel tinyint
DECLARE @o_dwDungeonRegistTime varbinary(4)
DECLARE @o_DeadFrontTimeInfo varbinary(1000)
DECLARE @o_sp_rtn int
DECLARE @o_dwRegistTimeStart varbinary(4)
DECLARE @o_dwRegistTimeEnd varbinary(4)
DECLARE @o_dwTaxRegistTime varbinary(4)
DECLARE @o_dwStartTime varbinary(4)
DECLARE @o_dwRegistTime varbinary(4)
DECLARE @v_strDate1 varchar(10)
DECLARE @i_GetDate datetime
DECLARE @v_siege_start_date varchar(14)
DECLARE @v_siege_start_time varbinary(4)
SET @i_GetDate=getdate()
-- Siege War Every Saturday:
SELECT @v_siege_start_date = CONVERT(VARCHAR(10), DATEADD(d, 7-DATEPART(dw, @i_GetDate), @i_GetDate), 112) + '210000'
--
SELECT @v_siege_start_time = SUBSTRING(@v_siege_start_date,4,1) * 100000000
+ SUBSTRING(@v_siege_start_date,5,2) * 1000000
+ SUBSTRING(@v_siege_start_date,7,2) * 10000
+ SUBSTRING(@v_siege_start_date,9,2) * 100
+ SUBSTRING(@v_siege_start_date,11,2)
SET @guild_code = '001'
SET @guild_name = 'FirstSiege'
SET @v_siege_no = SUBSTRING(dbo.FN_DateToShortStrDate(GetDate()), 3, 4) + '0001'
INSERT
INTO GUILD_INFO(guild_code, guild_name, guild_Level, bystate, bychannel)
VALUES (@guild_code, @guild_name, 3, 1, 1)
INSERT
INTO SIEGE_INFO(SIEGE_NO, CHANNEL_NO, GUILD_CODE, GUILD_NAME, DWSTARTTIME, BYREGISTTIMEFLAG, DEFENDER_INFO, SIEGE_TAG)
VALUES (@v_siege_no, 1, @guild_code, @guild_name,@v_siege_start_time, 1, 0X0000, 'Y')
EXEC SP_SIEGE_START_TIME_U @guild_code,1,0x01,@v_siege_start_time output,@o_dwRegistTime output,@o_sp_rtn output
SET @v_strDate1=dbo.FN_BinDateToDateTime(@v_siege_star t_time)
DECLARE @P1 varchar(13)
SET @P1=NULL
DECLARE @P2 varchar(33)
SET @P2=NULL
DECLARE @P3 varchar(25)
SET @P3=NULL
DECLARE @P4 varbinary(4)
SET @P4=NULL
DECLARE @P5 varbinary(4)
SET @P5=NULL
DECLARE @P6 varbinary(4)
SET @P6=NULL
DECLARE @P7 varbinary(4)
SET @P7=NULL
DECLARE @P8 varbinary(4)
SET @P8=NULL
DECLARE @P9 varbinary(4)
SET @P9=NULL
DECLARE @P10 varbinary(4)
SET @P10=NULL
DECLARE @P11 varbinary(2)
SET @P11=NULL
DECLARE @P12 varbinary(2)
SET @P12=NULL
DECLARE @P13 varbinary(2)
SET @P13=NULL
DECLARE @P14 int
SET @P14=NULL
DECLARE @P15 int
SET @P15=NULL
DECLARE @P16 varbinary(4)
SET @P16=NULL
DECLARE @ varbinary(996)
SET @=NULL
DECLARE @ varbinary(996)
SET @=NULL
DECLARE @ varbinary(1000)
SET @=NULL
DECLARE @ int
SET @=NULL
EXEC SP_SIEGE_INFO_R 1, @P1 output, @P2 output, @P3 output, @P4 output, @P5 output, @P6 output, @P7 output, @P8 output, @P9 output, @P10 output, @P11 output, @P12 output, @P13 output, @P14 output, @P15 output, @P16 output, @ output, @ output, @ output, @ output
SELECT @P1, @P2, @P3, @P4, @P5, @P6,@v_strDate1

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

still getting those error idk why please someone help me