USE [Mu3]
GO
DROP FUNCTION [dbo].[fn_medal]
GO
DROP FUNCTION [dbo].[fn_nivel_gm]
GO
DROP FUNCTION [dbo].[fn_online]
GO
DROP FUNCTION [dbo].[fn_patente]
GO
DROP FUNCTION [dbo].[fn_exp]
GO
DROP FUNCTION [dbo].[fn_acc]
GO
DROP FUNCTION [dbo].[fn_info]
GO
DROP FUNCTION [dbo].[fn_info_adm]
GO
DROP FUNCTION [dbo].[fn_char_forte]
GO
DROP FUNCTION [dbo].[fn_chat_online]
GO
DROP FUNCTION [dbo].[fn_classe]
GO
DROP FUNCTION [dbo].[fn_tops]
GO
DROP FUNCTION [dbo].[fn_rank_pos]
GO
DROP FUNCTION [dbo].[fn_senha_bau]
GO
DROP FUNCTION [dbo].[fn_wc]
GO
DROP FUNCTION [dbo].[fn_whats]
GO
DROP FUNCTION [dbo].[fn_wp]
GO
DROP FUNCTION [dbo].[fn_dados]
GO
DROP FUNCTION [dbo].[fn_atualizar_msg]
GO
DROP FUNCTION [dbo].[fn_bandeira]
GO
DROP FUNCTION [dbo].[fn_gens_Contribution]
GO
DROP FUNCTION [dbo].[fn_gens_family]
GO
DROP FUNCTION [dbo].[fn_gp]
GO
DROP FUNCTION [dbo].[fn_img]
GO
DROP FUNCTION [dbo].[fn_rank]
GO
DROP FUNCTION [dbo].[fn_rank_gens]
GO
DROP FUNCTION [dbo].[fn_rank_gens_d]
GO
DROP FUNCTION [dbo].[fn_rank_gens_v]
GO
DROP FUNCTION [dbo].[fn_ml]
GO
DROP FUNCTION [dbo].[fn_msg1]
GO
DROP FUNCTION [dbo].[fn_msg2]
GO
DROP FUNCTION [dbo].[fn_mudar_mail]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[fn_mudar_mail]
(
@acc varchar(10)
)
returns int
as
begin
declare @valor int

set @valor = 0
if (SELECT COUNT(*) FROM MK_mudar_mail WHERE login=@acc) > 0 
begin
	set @valor = 1
end

return @valor
end
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[fn_msg2]
(
@acc varchar(10)
)
returns int
as
begin
declare @valor int

set @valor = 0
if (SELECT COUNT(*) FROM MK_mensagens WHERE login_r=@acc) > 0 
begin
	select @valor = COUNT(*) FROM MK_mensagens WHERE login_R=@acc and (removeu1 <> @acc or removeu1 is null) and (removeu2 <> @acc or removeu2 is null)
end

return @valor
end
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[fn_msg1]
(
@acc varchar(10)
)
returns int
as
begin
declare @valor int

set @valor = 0
if (SELECT COUNT(*) FROM MK_mensagens WHERE login=@acc) > 0 
begin
	select @valor = COUNT(*) FROM MK_mensagens WHERE login=@acc and (removeu1 <> @acc or removeu1 is null) and (removeu2 <> @acc or removeu2 is null)
end

return @valor
end
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[fn_ml]
(
@name varchar(10)
)
returns int
as
begin

declare @ml int 
set @ml = 0
if exists (select masterlevel from MasterSkillTree where Name = @name)
begin
set @ml = (select masterlevel from MasterSkillTree where Name = @name)
end

return @ml
end
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create function [dbo].[fn_rank_gens_v]
(
@name varchar(10)
)
returns int
as
begin
declare @valor int;

set @valor = 0;

WITH cte AS (
SELECT DENSE_RANK() OVER (ORDER BY contribution DESC) AS Rank, Name
FROM Gens_Rank where Family=2)
select @valor = rank from cte where Name = @name;

return @valor;
end
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create function [dbo].[fn_rank_gens_d]
(
@name varchar(10)
)
returns int
as
begin
declare @valor int;

set @valor = 0;

WITH cte AS (
SELECT DENSE_RANK() OVER (ORDER BY contribution DESC) AS Rank, Name
FROM Gens_Rank where Family=1)
select @valor = rank from cte where Name = @name;

return @valor;
end
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[fn_rank_gens]
(
@name varchar(10),
@familia int
)
returns int
as
begin
declare @valor int;

set @valor = 0;

if exists (select Contribution from Gens_Rank where Name = @name and Contribution > 9999)
begin
WITH cte AS (
SELECT DENSE_RANK() OVER (ORDER BY contribution DESC) AS Rank, Name
FROM Gens_Rank where family=@familia)
select @valor = rank from cte where Name = @name;
end

return @valor;
end
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[fn_rank]
(
@name varchar(10),
@tipo int
)
returns int
as
begin
declare @valor int

set @valor = 0
if exists (select posicao from LOG_RANK where name = @name and tipo = @tipo) 
begin
set @valor = (select top 1 posicao from LOG_RANK where name = @name and tipo = @tipo)
end

return @valor
end
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[fn_img]
(
@acc varchar(10)
)
returns nvarchar(25)
as
begin
declare @img nvarchar(25)
set @img = (select img from MEMB_INFO where memb___id=@acc)

return @img
end
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create function [dbo].[fn_gp]
(
@acc varchar(10)
)
returns int
as
begin

declare @gp int 
set @gp = 0
if exists (select GoblinPoint from CashShopData where AccountID = @acc)
begin
set @gp = (select GoblinPoint from CashShopData where AccountID = @acc)
end

return @gp
end
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[fn_gens_family]
(
@name varchar(10)
)
returns int
as
begin
declare @valor int;

set @valor = 0;

if exists (select Family from Gens_Rank where Name = @name)
begin
set @valor = (select Family from Gens_Rank where Name = @name)
end

return @valor;
end
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[fn_gens_Contribution]
(
@name varchar(10)
)
returns int
as
begin
declare @valor int;

set @valor = 0;

if exists (select Contribution from Gens_Rank where Name = @name)
begin
set @valor = (select Contribution from Gens_Rank where Name = @name)
end

return @valor;
end
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[fn_bandeira]
(
@acc varchar(10)
)
returns nvarchar(10)
as
begin
declare @img nvarchar(25)
set @img = (select bandeira from MEMB_INFO where memb___id=@acc)

return @img
end
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[fn_atualizar_msg]
(
@login varchar(10)
)
RETURNS @tabela TABLE 
(
    -- Columns returned by the function
    valor1 int,
    valor2 int,
    valor3 int,
    valor4 int
)
AS 
begin
declare @valor1 int
declare @valor2 int
declare @valor3 int
declare @valor4 int

select @valor1 = COUNT(*) FROM mk_mensagens WHERE login=@login and respondido=0 and suporte = 0 and viu=0;
select @valor2 = COUNT(*) FROM mk_mensagens WHERE login=@login and respondido=0 and suporte = 0;
select @valor3 = COUNT(*) FROM mk_mensagens WHERE login=@login and respondido=0 and suporte = 1 and viu=0;
select @valor4 = COUNT(*) FROM mk_mensagens WHERE login=@login and respondido=0 and suporte = 1;

        INSERT @tabela
        SELECT @valor1, @valor2, @valor3, @valor4;
 return;
end
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[fn_dados]
(

)
returns int
as
begin
declare @valor1 int
declare @valor2 int
declare @valor3 int
declare @valor4 int

select @valor1 = COUNT(*) from MEMB_STAT where ConnectStat=1
select @valor2 = COUNT(*) from MEMB_INFO

return @valor1
end
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create function [dbo].[fn_wp]
(
@acc varchar(10)
)
returns int
as
begin

declare @wp int 
set @wp = 0
if exists (select WCoinP from CashShopData where AccountID = @acc)
begin
set @wp = (select WCoinP from CashShopData where AccountID = @acc)
end

return @wp
end
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[fn_whats]
(
@acc varchar(10)
)
returns int
as
begin
declare @valor int

set @valor = 0
if (Select COUNT(*) from memb_info where memb___id=@acc and whatsapp is not null) > 0 
begin
set @valor = 1
end

return @valor
end
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create function [dbo].[fn_wc]
(
@acc varchar(10)
)
returns int
as
begin

declare @wc int 
set @wc = 0
if exists (select WCoinC from CashShopData where AccountID = @acc)
begin
set @wc = (select WCoinC from CashShopData where AccountID = @acc)
end

return @wc
end
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[fn_senha_bau]
(
@acc varchar(10)
)
returns int
as
begin
declare @senha int
select @senha = pw from warehouse where accountid=@acc

return @senha
end
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[fn_rank_pos]
(
@name varchar(10),
@tipo int
)
returns int
as
begin
declare @valor int;

set @valor = 0;

if @tipo = 1
begin
WITH cte AS (
SELECT ROW_NUMBER() OVER (ORDER BY resetcount DESC, clevel) AS Rank,name
FROM Character where nivel_gm < 2 and CtlCode=0)
select @valor = rank from cte where Name = @name;
end

if @tipo = 2
begin
WITH cte AS (
SELECT ROW_NUMBER() OVER (ORDER BY masterresetcount desc, resetcount DESC, clevel) AS Rank,name
FROM Character where nivel_gm < 2 and CtlCode=0)
select @valor = rank from cte where Name = @name;
end

return @valor;
end
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[fn_tops]
(

)
RETURNS @tabela TABLE 
(
    -- Columns returned by the function
    valor1 nvarchar(10),
    valor2 nvarchar(10),
    valor3 nvarchar(10),
    valor4 nvarchar(10),
    valor5 nvarchar(10),
    valor6 nvarchar(10),
    valor7 nvarchar(10),
    valor8 nvarchar(10),
    valor9 nvarchar(10),
    valor10 nvarchar(10),
    valor11 nvarchar(10),
    valor12 nvarchar(10),
    valor13 nvarchar(10),
    valor14 nvarchar(10),
    valor15 nvarchar(10),
    valor16 nvarchar(10)
)
AS 
begin
declare @valor1 nvarchar(10)
declare @valor2 nvarchar(10)
declare @valor3 nvarchar(10)
declare @valor4 nvarchar(10)
declare @valor5 nvarchar(10)
declare @valor6 nvarchar(10)
declare @valor7 nvarchar(10)
declare @valor8 nvarchar(10)
declare @valor9 nvarchar(10)
declare @valor10 nvarchar(10)
declare @valor11 nvarchar(10)
declare @valor12 nvarchar(10)
declare @valor13 nvarchar(10)
declare @valor14 nvarchar(10)
declare @valor15 nvarchar(10)
declare @valor16 nvarchar(10)

set @valor1 = (select top 1 Name from character where nivel_gm<2 order by masterresetcount desc,resetcount desc,clevel desc)
set @valor2 = (select top 1 Name from character where nivel_gm<2 order by resetcount desc,clevel desc)
set @valor3 = (select top 1 Name from character where nivel_gm<2 order by exp_war desc,resetcount desc,clevel desc)
set @valor4 = (select top 1 Name from character where nivel_gm<2 order by eventos desc,resetcount desc,clevel desc)
set @valor5 = (select top 1 Name from character where nivel_gm<2 order by Z_RankPK desc,resetcount desc,clevel desc)
set @valor6 = (select top 1 Name from character where nivel_gm<2 order by Z_RankHR desc,resetcount desc,clevel desc)
set @valor7 = (select top 1 G_name from guild order by g_score desc)
set @valor8 = (select top 1 Name from RankingDuel order by WinScore desc)
set @valor9 = (select top 1 Name from character where nivel_gm<2 and Class = 2 order by masterresetcount desc,resetcount desc,clevel desc)
set @valor10= (select top 1 Name from character where nivel_gm<2 and Class = 18 order by masterresetcount desc,resetcount desc,clevel desc)
set @valor11= (select top 1 Name from character where nivel_gm<2 and Class = 34 order by masterresetcount desc,resetcount desc,clevel desc)
set @valor12= (select top 1 Name from character where nivel_gm<2 and Class = 50 order by masterresetcount desc,resetcount desc,clevel desc)
set @valor13= (select top 1 Name from character where nivel_gm<2 and Class = 66 order by masterresetcount desc,resetcount desc,clevel desc)
set @valor14= (select top 1 Name from character where nivel_gm<2 and Class = 82 order by masterresetcount desc,resetcount desc,clevel desc)
set @valor15= (select top 1 Name from character where nivel_gm<2 and Class = 98 order by masterresetcount desc,resetcount desc,clevel desc)
set @valor16= (select TOP 1 name from log_custom_arena where arena=0 group by name order by SUM(valor) desc)

        INSERT @tabela
        SELECT @valor1, @valor2, @valor3, @valor4, @valor5, @valor6, @valor7, @valor8, @valor9, @valor10, @valor11, @valor12, @valor13, @valor14, @valor15, @valor16;
 return;
end
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[fn_classe]
(
@name varchar(10)
)
returns int
as
begin
declare @num int
set @num = (select Class from Character where Name=@name)

return @num
end
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[fn_chat_online]
(
)
returns int
as
begin
declare @num int
set @num = (Select count(*) from MK_chat_online where data > DateADD(mi, -6, getdate()))

return @num
end
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[fn_char_forte]
(
@acc varchar(10)
)
returns varchar(10)
as
begin
declare @char varchar(10)
set @char = (Select top 1 name from character where accountid=@acc order by nivel_gm desc, masterresetcount desc,resetcount desc,clevel desc)

return @char
end
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[fn_info_adm]
(

)
RETURNS @tabela TABLE 
(
    valor1 int,
    valor2 int,
    valor3 int,
    valor4 int,
    valor5 int,
	valor6 int,
    valor7 int,
    valor8 int,
    valor9 int,
    valor10 int,
    valor11 int,
    valor12 int
)
AS 
begin
declare @valor1 int
declare @valor2 int
declare @valor3 int
declare @valor4 int
declare @valor5 int
declare @valor6 int
declare @valor7 int
declare @valor8 int
declare @valor9 int
declare @valor10 int
declare @valor11 int
declare @valor12 int

select @valor1 = COUNT(*) from MEMB_STAT where ConnectStat=1;
select @valor2 = COUNT(*) from MEMB_INFO;
select @valor3 = COUNT(*) from Character;
select @valor4 = COUNT(*) from Character where CtlCode=1;
select @valor5 = COUNT(*) FROM Character join Memb_Stat on Character.Accountid = Memb_Stat.memb___id join AccountCharacter on Character.Name = AccountCharacter.GameIDC WHERE (nivel_gm = 2 or nivel_gm = 3) and Memb_Stat.ConnectStat = 1;
select @valor6 = COUNT(*) FROM Character WHERE nivel_gm = 2 or nivel_gm = 3;
select @valor7 = count(*) FROM MEMB_INFO WHERE accountlevel = 1;
select @valor8 = count(*) FROM MK_DV;
select @valor9 = count(*) FROM MEMB_INFO where bloc_code = 1;
select @valor10 = COUNT(*) FROM Character join Memb_Stat on Character.Accountid = Memb_Stat.memb___id join AccountCharacter on Character.Name = AccountCharacter.GameIDC WHERE (nivel_gm = 4 or nivel_gm = 5) and Memb_Stat.ConnectStat = 1;
select @valor11 = COUNT(*) FROM Character WHERE nivel_gm = 4 or nivel_gm = 5;
select @valor12 = recorde_on FROM MK_Server;

        INSERT @tabela
        SELECT @valor1, @valor2, @valor3, @valor4, @valor5, @valor6, @valor7, @valor8, @valor9, @valor10, @valor11, @valor12;
 return;
end
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[fn_info]
(

)
RETURNS @tabela TABLE 
(
    -- Columns returned by the function
    valor1 int,
    valor2 int,
    valor3 int,
    valor4 int,
    valor5 int,
    valor6 int
)
AS 
begin
declare @valor1 int
declare @valor2 int
declare @valor3 int
declare @valor4 int
declare @valor5 int
declare @valor6 int

select @valor1 = COUNT(*) from MEMB_STAT where ConnectStat=1
select @valor2 = COUNT(*) from MEMB_INFO
select @valor3 = COUNT(*) from Character
select @valor4 = COUNT(*) from Character where CtlCode=1
select @valor5 = COUNT(*) FROM Character join Memb_Stat on Character.Accountid = Memb_Stat.memb___id join AccountCharacter on Character.Name = AccountCharacter.GameIDC WHERE CtlCode > 7 and Memb_Stat.ConnectStat = 1
select @valor6 = COUNT(*) FROM Character WHERE CtlCode > 7

        INSERT @tabela
        SELECT @valor1, @valor2, @valor3, @valor4, @valor5, @valor6;
 return;
end
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[fn_acc]
(
@name varchar(10)
)
returns varchar(10)
as
begin
declare @acc varchar(10)
set @acc = (select accountid from Character where Name=@name)

return @acc
end
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[fn_exp]
(
@name varchar(10)
)
returns int
as
begin
declare @num int
set @num = (select exp_war from Character where Name=@name)

return @num
end
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[fn_patente]
(
@name varchar(10)
)
returns int
as
begin
declare @num int
set @num = (select patente from Character where Name=@name)

return @num
end
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[fn_online]
(
@name varchar(10)
)
returns int
as
begin
declare @valor int

set @valor = 0
if (select COUNT(*) from AccountCharacter where GameIDC = @name) > 0 
begin
if (select COUNT(*) from MEMB_STAT where memb___id = (select accountid from Character where Name=@name) and connectstat=1)>0
begin
set @valor = 1
end
end

return @valor
end
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[fn_nivel_gm]
(
@acc varchar(10)
)
returns int
as
begin
declare @nivel int
set @nivel = (Select top 1 nivel_gm from character where accountid=@acc order by nivel_gm desc)

return @nivel
end
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[fn_medal]
(
@acc varchar(10),
@medal int
)
returns int
as
begin

declare @num int

set @num = 0

if @medal = 1
begin
if exists (select login from MK_MEDALHAS where login=@acc and medal_reputacao=1)
set @num = 1
end

if @medal = 2
begin
if exists (select login from MK_MEDALHAS where login=@acc and medal_contribuinte = 1)
set @num = 1
end

if @medal = 3
begin
if exists (select login from MK_MEDALHAS where login=@acc and medal_missao=1)
set @num = 1
end

return @num
end
GO
