Dev.Ri3o
02-12-2016, 08:35 PM
http://www4.0zz0.com/2015/11/27/18/243536469.gif (https://www.0zz0.com)
http://www4.0zz0.com/2015/11/27/18/884808031.gif (https://www.0zz0.com)
http://www4.0zz0.com/2015/11/27/18/702514124.gif (https://www.0zz0.com)
كان فى مشكلة فى C-sro بخصوص موضوع Confirmed Yahoo لما تعمل New Account محتاج انك تغير ال Cert_Num يدويا ودى مشكلة كبيرة لو نويت تفتح سيرفر C-sro عشان كدة انا كنت منزل موضوع امبارح هنا بس وصلح المشكلة بس عمل مشكلة تانية فى JID عشان كدة عملت حل تانى وشغال 100%
اولا هتخش على Table اللى اسمو TB_USER وتمسح ال Accounts وتخلى Account واحد بس بشرط ان يكون ال Cert_num اللى فى ال account يكون
203103198405100000
http://www5.0zz0.com/2016/12/02/20/795999246.jpg (https://www.0zz0.com)
وبعد كدة تعمل ده هتعمل ده
http://www4.0zz0.com/2015/11/27/18/171651594.gif (https://www.0zz0.com)
USE [SRO_R_Accountdb]
GO
/****** Object: StoredProcedure [dbo].[_CertifyTB_User] Script Date: 02.12.2016 7:10:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[_CertifyTB_User]
@szUserID varchar(25),
@szPassword varchar(50)
AS
declare @Serial64_new bigint
SELECT @Serial64_new = LatestItemSerial FROM dbo._LatestItemSerial
SET @Serial64_new = @Serial64_new +1
declare @nUserJID int
declare @sec_primary tinyint
declare @sec_content tinyint
declare @AccPlayTime int
declare @LatestUpdateTime_ToPlayTime int
declare @Certificate_num varchar(50)
declare @IsAdult tinyint -- int형 필요 없음
-- ifdef SYSTEM_EMAIL_CERTIFICATION_NOTUSE_CGI_LOGIN
declare @Email varchar(50)
declare @EmailValidate tinyint
-- endif SYSTEM_EMAIL_CERTIFICATION_NOTUSE_CGI_LOGIN
set @nUserJID = 0
set @sec_primary = 0
set @sec_content = 0
set @Certificate_num = @Serial64_new
set @AccPlayTime = 0
set @LatestUpdateTime_ToPlayTime = 0
set @IsAdult = 0
-- ifdef SYSTEM_EMAIL_CERTIFICATION_NOTUSE_CGI_LOGIN
set @Email = null
set @EmailValidate = 0
-- endif SYSTEM_EMAIL_CERTIFICATION_NOTUSE_CGI_LOGIN
select @nUserJID = JID,
@sec_primary = sec_primary,
@sec_content = sec_content,
@AccPlayTime = AccPlayTime,
@LatestUpdateTime_ToPlayTime = LatestUpdateTime_ToPlayTime,
@Certificate_num = certificate_num,
-- ifdef SYSTEM_EMAIL_CERTIFICATION_NOTUSE_CGI_LOGIN
@Email = Email,
@EmailValidate = EmailValidate
-- endif SYSTEM_EMAIL_CERTIFICATION_NOTUSE_CGI_LOGIN
from TB_User
where StrUserID = @szUserID and password = @szPassword
--set @EmailValidate=0
if( @nUserJID = 0 or @nUserJID is null or @@error <> 0 or @@rowcount = 0)
begin
--select convert( tinyint, 1), convert( int, 0), convert( tinyint, 0), convert( tinyint, 0), convert( tinyint, 0), convert(datetime, 0), convert(bigint, 0)
select convert( tinyint, 1), convert( int, 0), convert( tinyint, 0), convert( tinyint, 0), convert( tinyint, 0), convert(datetime, 0), convert(bigint, 0), convert(tinyint, 0)
return
end
if( exists( select Type from _BlockedUser where UserJID = @nUserJID and Type = 1 and getdate() between timeBegin and timeEnd))
begin
--select convert( tinyint, 3), @nUserJID, convert( tinyint, 0), convert( tinyint, 0), convert( tinyint, 0), convert(datetime, 0), convert(bigint, 0)
select convert( tinyint, 3), @nUserJID, convert( tinyint, 0), convert( tinyint, 0), convert( tinyint, 0), convert(datetime, 0), convert(bigint, 0), convert(tinyint, 0)
return
end
if( (@Certificate_num is null) or (len(@Certificate_num) < 15))
begin
--select convert( tinyint, 5), @nUserJID, convert( tinyint, 0), convert( tinyint, 0), convert( tinyint, 0), convert(datetime, 0), convert(bigint, 0)
select convert( tinyint, 5), @nUserJID, convert( tinyint, 0), convert( tinyint, 0), convert( tinyint, 0), convert(datetime, 0), convert(bigint, 0), convert(tinyint, 0)
return
end
------------
declare @ssn bigint
declare @max_playtime int
declare @max_latestUpdateTime int
declare @max_totalLoggedOutTime int
declare @max_lastLoggedOutTime datetime
declare @certf varchar(60)
select @max_playtime = max(AccPlayTime),
@max_latestUpdateTime = max(LatestUpdateTime_ToPlayTime),
@max_totalLoggedOutTime = max(TotalLoggedOutTime),
@max_lastLoggedOutTime = max(LastLoggedOutTime)
from TB_User where certificate_num = @Certificate_num
if (len(@Certificate_num) = 15)
begin
if (substring(@Certificate_num, 15, 1) = 'X')
begin
set @certf = '1'
set @certf = @certf + substring(@Certificate_num, 1, 14)
set @certf = @certf + '0'
set @ssn = cast(@certf as bigint)
end
else
begin
set @ssn = cast(@Certificate_num as bigint)
end
set @Certificate_num = substring(@Certificate_num, 7, 6)
end
else if (len(@Certificate_num) = 18)
begin
if (substring(@Certificate_num, 18, 1) = 'X')
begin
set @certf = '1'
set @certf = @certf + substring(@Certificate_num, 1, 17)
set @certf = @certf + '0'
set @ssn = cast(@certf as bigint)
end
else
begin
set @ssn = cast(@Certificate_num as bigint)
end
set @Certificate_num = substring(@Certificate_num, 9, 6)
end
else
begin
--select convert( tinyint, 6), @nUserJID, convert( tinyint, 0), convert( tinyint, 0), convert( tinyint, 0), convert(datetime, 0), convert(bigint, 0)
select convert( tinyint, 6), @nUserJID, convert( tinyint, 0), convert( tinyint, 0), convert( tinyint, 0), convert(datetime, 0), convert(bigint, 0), convert(tinyint, 0)
return
end
-- if( datediff(Day, CAST(@certificate_num AS smalldatetime), CAST(getdate() AS smalldatetime)) > 365*18)
if( CAST(@certificate_num AS smalldatetime) <= DateAdd(Year, -18, getdate()) )
begin
set @AccPlayTime = 60000
set @IsAdult = 1 -- 중국은 certificate_num 에 생년정보를 담고 있다. 요기는 성인으로 경험치 종량제에 영향을 받지 않는다.
end
else
begin
set @AccPlayTime = @max_playtime
end
--select convert(tinyint, 0), @nUserJID, @sec_primary, @sec_content, @AccPlayTime, @max_LatestUpdateTime, @max_totalLoggedOutTime, @max_lastLoggedOutTime, @ssn
--select convert(tinyint, 0), @nUserJID, @sec_primary, @sec_content, @AccPlayTime, @max_LatestUpdateTime, @max_totalLoggedOutTime, @max_lastLoggedOutTime, @ssn, @IsAdult
-- ifdef SYSTEM_EMAIL_CERTIFICATION_NOTUSE_CGI_LOGIN
select convert(tinyint, 0), @nUserJID, @sec_primary, @sec_content, @AccPlayTime, @max_LatestUpdateTime, @max_totalLoggedOutTime, @max_lastLoggedOutTime, @ssn, @IsAdult, @Email, @EmailValidate
-- endif SYSTEM_EMAIL_CERTIFICATION_NOTUSE_CGI_LOGIN
-- print @EmailValidate
return
وده كمان
http://www4.0zz0.com/2015/11/27/18/171651594.gif (https://www.0zz0.com)
USE [SRO_R_Accountdb]
GO
/****** Object: StoredProcedure [dbo].[_CertifyUser] Script Date: 02.12.2016 7:20:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[_CertifyUser]
@szUserID varchar(25),
@szPassword varchar(50),
@szSSNumber varchar(32)
as
declare @nUserJID int
declare @sec_primary tinyint
declare @sec_content tinyint
set @nUserJID = 0
set @sec_primary = 0
set @sec_content = 0
if (not exists(select JID from SRO_R_Accountdb.dbo.TB_User where StrUserID = @szUserID))
begin
select convert( int, 0), convert( tinyint, 0), convert( tinyint, 0)
return
end
select @nUserJID = JID from SRO_R_Accountdb.dbo.TB_User where StrUserID = @szUserID
if (@nUserJID = 0 or @nUserJID is null or @@error <> 0 or @@rowcount = 0)
begin
select convert( int, 0), convert( tinyint, 0), convert( tinyint, 0)
return
end
if (not exists(select JID from SRO_R_Accountdb.dbo.TB_User where JID = @nUserJID and password = @szPassword))
begin
select convert( int, 0), convert( tinyint, 0), convert( tinyint, 0)
return
end
select @sec_primary = sec_primary, @sec_content = sec_content from SRO_R_Accountdb.dbo.TB_User with (nolock) where JID = @nUserJID
select @nUserJID, @sec_primary, @sec_content
http://www4.0zz0.com/2015/11/27/18/171651594.gif (https://www.0zz0.com)
وهتعمل ال Trigger ده
USE [SRO_R_Accountdb]
GO
/****** Object: Trigger [dbo].[FIX_CERT_NUM] Script Date: 02.12.2016 7:12:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[FIX_CERT_NUM]
ON [dbo].[TB_User]
AFTER INSERT
AS
BEGIN
BEGIN TRAN
Declare @INSERTED bigint = (Select JID From inserted)
Declare @Real_Cert bigint = (Select MAX(certificate_num) From TB_User)
Update TB_User Set certificate_num = @Real_Cert+1 Where JID=@INSERTED
Commit Tran
END
http://www4.0zz0.com/2015/11/27/18/171651594.gif (https://www.0zz0.com)
واخيرا ده
alter table TB_User alter column certificate_num Varchar(50);
http://www4.0zz0.com/2015/11/27/18/171651594.gif (https://www.0zz0.com)
ودى صورة عملت Account من ال Evatools وبعد كدة ال Cert اتغير لوحدو
http://www5.0zz0.com/2016/12/02/20/343993555.jpg (https://www.0zz0.com)
http://www5.0zz0.com/2016/12/02/20/137187520.jpg (https://www.0zz0.com)
http://www4.0zz0.com/2015/11/27/18/171651594.gif (https://www.0zz0.com)
تحياتى الحارة ^_^
http://www4.0zz0.com/2015/11/27/18/735351340.gif (https://www.0zz0.com)
http://www4.0zz0.com/2015/11/27/18/884808031.gif (https://www.0zz0.com)
http://www4.0zz0.com/2015/11/27/18/702514124.gif (https://www.0zz0.com)
كان فى مشكلة فى C-sro بخصوص موضوع Confirmed Yahoo لما تعمل New Account محتاج انك تغير ال Cert_Num يدويا ودى مشكلة كبيرة لو نويت تفتح سيرفر C-sro عشان كدة انا كنت منزل موضوع امبارح هنا بس وصلح المشكلة بس عمل مشكلة تانية فى JID عشان كدة عملت حل تانى وشغال 100%
اولا هتخش على Table اللى اسمو TB_USER وتمسح ال Accounts وتخلى Account واحد بس بشرط ان يكون ال Cert_num اللى فى ال account يكون
203103198405100000
http://www5.0zz0.com/2016/12/02/20/795999246.jpg (https://www.0zz0.com)
وبعد كدة تعمل ده هتعمل ده
http://www4.0zz0.com/2015/11/27/18/171651594.gif (https://www.0zz0.com)
USE [SRO_R_Accountdb]
GO
/****** Object: StoredProcedure [dbo].[_CertifyTB_User] Script Date: 02.12.2016 7:10:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[_CertifyTB_User]
@szUserID varchar(25),
@szPassword varchar(50)
AS
declare @Serial64_new bigint
SELECT @Serial64_new = LatestItemSerial FROM dbo._LatestItemSerial
SET @Serial64_new = @Serial64_new +1
declare @nUserJID int
declare @sec_primary tinyint
declare @sec_content tinyint
declare @AccPlayTime int
declare @LatestUpdateTime_ToPlayTime int
declare @Certificate_num varchar(50)
declare @IsAdult tinyint -- int형 필요 없음
-- ifdef SYSTEM_EMAIL_CERTIFICATION_NOTUSE_CGI_LOGIN
declare @Email varchar(50)
declare @EmailValidate tinyint
-- endif SYSTEM_EMAIL_CERTIFICATION_NOTUSE_CGI_LOGIN
set @nUserJID = 0
set @sec_primary = 0
set @sec_content = 0
set @Certificate_num = @Serial64_new
set @AccPlayTime = 0
set @LatestUpdateTime_ToPlayTime = 0
set @IsAdult = 0
-- ifdef SYSTEM_EMAIL_CERTIFICATION_NOTUSE_CGI_LOGIN
set @Email = null
set @EmailValidate = 0
-- endif SYSTEM_EMAIL_CERTIFICATION_NOTUSE_CGI_LOGIN
select @nUserJID = JID,
@sec_primary = sec_primary,
@sec_content = sec_content,
@AccPlayTime = AccPlayTime,
@LatestUpdateTime_ToPlayTime = LatestUpdateTime_ToPlayTime,
@Certificate_num = certificate_num,
-- ifdef SYSTEM_EMAIL_CERTIFICATION_NOTUSE_CGI_LOGIN
@Email = Email,
@EmailValidate = EmailValidate
-- endif SYSTEM_EMAIL_CERTIFICATION_NOTUSE_CGI_LOGIN
from TB_User
where StrUserID = @szUserID and password = @szPassword
--set @EmailValidate=0
if( @nUserJID = 0 or @nUserJID is null or @@error <> 0 or @@rowcount = 0)
begin
--select convert( tinyint, 1), convert( int, 0), convert( tinyint, 0), convert( tinyint, 0), convert( tinyint, 0), convert(datetime, 0), convert(bigint, 0)
select convert( tinyint, 1), convert( int, 0), convert( tinyint, 0), convert( tinyint, 0), convert( tinyint, 0), convert(datetime, 0), convert(bigint, 0), convert(tinyint, 0)
return
end
if( exists( select Type from _BlockedUser where UserJID = @nUserJID and Type = 1 and getdate() between timeBegin and timeEnd))
begin
--select convert( tinyint, 3), @nUserJID, convert( tinyint, 0), convert( tinyint, 0), convert( tinyint, 0), convert(datetime, 0), convert(bigint, 0)
select convert( tinyint, 3), @nUserJID, convert( tinyint, 0), convert( tinyint, 0), convert( tinyint, 0), convert(datetime, 0), convert(bigint, 0), convert(tinyint, 0)
return
end
if( (@Certificate_num is null) or (len(@Certificate_num) < 15))
begin
--select convert( tinyint, 5), @nUserJID, convert( tinyint, 0), convert( tinyint, 0), convert( tinyint, 0), convert(datetime, 0), convert(bigint, 0)
select convert( tinyint, 5), @nUserJID, convert( tinyint, 0), convert( tinyint, 0), convert( tinyint, 0), convert(datetime, 0), convert(bigint, 0), convert(tinyint, 0)
return
end
------------
declare @ssn bigint
declare @max_playtime int
declare @max_latestUpdateTime int
declare @max_totalLoggedOutTime int
declare @max_lastLoggedOutTime datetime
declare @certf varchar(60)
select @max_playtime = max(AccPlayTime),
@max_latestUpdateTime = max(LatestUpdateTime_ToPlayTime),
@max_totalLoggedOutTime = max(TotalLoggedOutTime),
@max_lastLoggedOutTime = max(LastLoggedOutTime)
from TB_User where certificate_num = @Certificate_num
if (len(@Certificate_num) = 15)
begin
if (substring(@Certificate_num, 15, 1) = 'X')
begin
set @certf = '1'
set @certf = @certf + substring(@Certificate_num, 1, 14)
set @certf = @certf + '0'
set @ssn = cast(@certf as bigint)
end
else
begin
set @ssn = cast(@Certificate_num as bigint)
end
set @Certificate_num = substring(@Certificate_num, 7, 6)
end
else if (len(@Certificate_num) = 18)
begin
if (substring(@Certificate_num, 18, 1) = 'X')
begin
set @certf = '1'
set @certf = @certf + substring(@Certificate_num, 1, 17)
set @certf = @certf + '0'
set @ssn = cast(@certf as bigint)
end
else
begin
set @ssn = cast(@Certificate_num as bigint)
end
set @Certificate_num = substring(@Certificate_num, 9, 6)
end
else
begin
--select convert( tinyint, 6), @nUserJID, convert( tinyint, 0), convert( tinyint, 0), convert( tinyint, 0), convert(datetime, 0), convert(bigint, 0)
select convert( tinyint, 6), @nUserJID, convert( tinyint, 0), convert( tinyint, 0), convert( tinyint, 0), convert(datetime, 0), convert(bigint, 0), convert(tinyint, 0)
return
end
-- if( datediff(Day, CAST(@certificate_num AS smalldatetime), CAST(getdate() AS smalldatetime)) > 365*18)
if( CAST(@certificate_num AS smalldatetime) <= DateAdd(Year, -18, getdate()) )
begin
set @AccPlayTime = 60000
set @IsAdult = 1 -- 중국은 certificate_num 에 생년정보를 담고 있다. 요기는 성인으로 경험치 종량제에 영향을 받지 않는다.
end
else
begin
set @AccPlayTime = @max_playtime
end
--select convert(tinyint, 0), @nUserJID, @sec_primary, @sec_content, @AccPlayTime, @max_LatestUpdateTime, @max_totalLoggedOutTime, @max_lastLoggedOutTime, @ssn
--select convert(tinyint, 0), @nUserJID, @sec_primary, @sec_content, @AccPlayTime, @max_LatestUpdateTime, @max_totalLoggedOutTime, @max_lastLoggedOutTime, @ssn, @IsAdult
-- ifdef SYSTEM_EMAIL_CERTIFICATION_NOTUSE_CGI_LOGIN
select convert(tinyint, 0), @nUserJID, @sec_primary, @sec_content, @AccPlayTime, @max_LatestUpdateTime, @max_totalLoggedOutTime, @max_lastLoggedOutTime, @ssn, @IsAdult, @Email, @EmailValidate
-- endif SYSTEM_EMAIL_CERTIFICATION_NOTUSE_CGI_LOGIN
-- print @EmailValidate
return
وده كمان
http://www4.0zz0.com/2015/11/27/18/171651594.gif (https://www.0zz0.com)
USE [SRO_R_Accountdb]
GO
/****** Object: StoredProcedure [dbo].[_CertifyUser] Script Date: 02.12.2016 7:20:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[_CertifyUser]
@szUserID varchar(25),
@szPassword varchar(50),
@szSSNumber varchar(32)
as
declare @nUserJID int
declare @sec_primary tinyint
declare @sec_content tinyint
set @nUserJID = 0
set @sec_primary = 0
set @sec_content = 0
if (not exists(select JID from SRO_R_Accountdb.dbo.TB_User where StrUserID = @szUserID))
begin
select convert( int, 0), convert( tinyint, 0), convert( tinyint, 0)
return
end
select @nUserJID = JID from SRO_R_Accountdb.dbo.TB_User where StrUserID = @szUserID
if (@nUserJID = 0 or @nUserJID is null or @@error <> 0 or @@rowcount = 0)
begin
select convert( int, 0), convert( tinyint, 0), convert( tinyint, 0)
return
end
if (not exists(select JID from SRO_R_Accountdb.dbo.TB_User where JID = @nUserJID and password = @szPassword))
begin
select convert( int, 0), convert( tinyint, 0), convert( tinyint, 0)
return
end
select @sec_primary = sec_primary, @sec_content = sec_content from SRO_R_Accountdb.dbo.TB_User with (nolock) where JID = @nUserJID
select @nUserJID, @sec_primary, @sec_content
http://www4.0zz0.com/2015/11/27/18/171651594.gif (https://www.0zz0.com)
وهتعمل ال Trigger ده
USE [SRO_R_Accountdb]
GO
/****** Object: Trigger [dbo].[FIX_CERT_NUM] Script Date: 02.12.2016 7:12:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[FIX_CERT_NUM]
ON [dbo].[TB_User]
AFTER INSERT
AS
BEGIN
BEGIN TRAN
Declare @INSERTED bigint = (Select JID From inserted)
Declare @Real_Cert bigint = (Select MAX(certificate_num) From TB_User)
Update TB_User Set certificate_num = @Real_Cert+1 Where JID=@INSERTED
Commit Tran
END
http://www4.0zz0.com/2015/11/27/18/171651594.gif (https://www.0zz0.com)
واخيرا ده
alter table TB_User alter column certificate_num Varchar(50);
http://www4.0zz0.com/2015/11/27/18/171651594.gif (https://www.0zz0.com)
ودى صورة عملت Account من ال Evatools وبعد كدة ال Cert اتغير لوحدو
http://www5.0zz0.com/2016/12/02/20/343993555.jpg (https://www.0zz0.com)
http://www5.0zz0.com/2016/12/02/20/137187520.jpg (https://www.0zz0.com)
http://www4.0zz0.com/2015/11/27/18/171651594.gif (https://www.0zz0.com)
تحياتى الحارة ^_^
http://www4.0zz0.com/2015/11/27/18/735351340.gif (https://www.0zz0.com)