ALTER PROCEDURE [dbo].[SP_CREATE_USER]
(
@p_usn BIGINT, -- Entrada
@p_encode_nick VARCHAR(50), -- Alterado para bater com Query Q34 (BY CODER)
@p_encode_lnick VARCHAR(50), -- Alterado para bater com Query Q34 (BY CODER)
@p_reg_no INT,
@p_o_usn BIGINT OUT, -- Saída
@p_Result INT OUT
)
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- =============================================
-- Declarações
-- =============================================
DECLARE @v_date DATETIME = GETDATE();
DECLARE @v_date_str VARCHAR(14);
DECLARE @v_real_nick VARCHAR(30);
DECLARE @v_lower_nick VARCHAR(30);
DECLARE @v_eff_end_date DATETIME = '3000-12-31 23:59:59';
DECLARE @v_eff_end_date_str VARCHAR(14) = '30001231235959';
DECLARE @v_Slot_0_Rifle BIGINT = 0;
DECLARE @v_Slot_1_Rifle BIGINT = 0;
DECLARE @v_throw_slot1_sack0 BIGINT = 0;
DECLARE @v_throw_slot1_sack1 BIGINT = 0;
DECLARE @v_chk INT;
-- Inicialização
SET @p_Result = -1;
SET @p_o_usn = 0;
SET @v_date_str = CONVERT(VARCHAR(8), @v_date, 112) + REPLACE(CONVERT(VARCHAR(8), @v_date, 108), ':', '');
-- Decodifica nick
BEGIN TRY
SET @v_real_nick = dbo.Base64_Decode(@p_encode_nick);
END TRY
BEGIN CATCH
SET @v_real_nick = @p_encode_nick; -- fallback
END CATCH
SET @v_lower_nick = LOWER(@v_real_nick);
BEGIN TRY
-- =============================================
-- 1. Verificação do Nick
-- =============================================
EXEC dbo.SP_USER_NICK_CHECK @p_encode_nick, @v_chk OUT;
IF @v_chk <> 1
BEGIN
SET @p_Result = @v_chk;
RETURN;
END
-- Verifica se usuário já existe
IF EXISTS (SELECT 1 FROM dbo.CF_USER WHERE USN = @p_usn)
BEGIN
SET @p_Result = -2;
RETURN;
END
-- =============================================
-- 2. Início da Transação
-- =============================================
BEGIN TRAN
-- CF_USER
INSERT INTO dbo.CF_USER
(usn, nick, lower_nick, game_point, default_char_item_id, FRIEND_KIND, last_play_date, reg_no)
VALUES
(@p_usn, @v_real_nick, @v_lower_nick, 200000, '-', 'N', @v_eff_end_date, @p_reg_no);
-- Tabelas de Perfil
INSERT INTO dbo.CF_USER_PROFILE (USN) VALUES (@p_usn);
INSERT INTO dbo.CF_USER_GAME_MODE (USN) VALUES (@p_usn);
INSERT INTO dbo.CF_USER_KEY_SETTING (usn) VALUES (@p_usn);
INSERT INTO dbo.CF_USER_INFO (usn, img_url, [profile]) VALUES (@p_usn, ' ', ' ');
INSERT INTO dbo.CF_USER_STATUS (USN, CLANKEY, GUIDE_TYPE, GUIDE_LEV) VALUES (@p_usn, 0, 1, 0);
-- Tabelas Opcionais
IF OBJECT_ID('dbo.CF_USER_AI_CLEAR_INFO', 'U') IS NOT NULL
INSERT INTO dbo.CF_USER_AI_CLEAR_INFO (USN, AI_RESURRECT_CNT) VALUES (@p_usn, 8);
IF OBJECT_ID('dbo.CF_USER_GACHA', 'U') IS NOT NULL
INSERT INTO dbo.CF_USER_GACHA (USN) VALUES (@p_usn);
IF OBJECT_ID('dbo.CF_USER_ATTENDANCE', 'U') IS NOT NULL
INSERT INTO dbo.CF_USER_ATTENDANCE (USN) VALUES (@p_usn);
-- =============================================
-- Inventory Inicial
-- =============================================
INSERT INTO dbo.CF_USER_INVENTORY
(USN, ITEM_TYPE, ITEM_CODE, EFF_START_DATE, EFF_END_DATE, GAUGE, CNT, FUNCTION_USE_YN)
SELECT
@p_usn,
ITEM_TYPE,
ITEM_CODE,
@v_date,
CASE WHEN USE_TYPE1 = 'E' THEN @v_eff_end_date
ELSE DATEADD(dd, use_effect1, @v_date)
END,
USE_EFFECT3,
1,
'N'
FROM dbo.CF_ITEM_INFO II
CROSS JOIN (SELECT 1 ORDERS UNION ALL SELECT 2) MULTI
WHERE ITEM_ID IN ('2010000101','2010000301','2010000701')
AND (MULTI.ORDERS = 1 OR (MULTI.ORDERS = 2 AND ITEM_ID = '2010000701'));
-- =============================================
-- Busca Serials das Armas
-- =============================================
SELECT
@v_Slot_0_Rifle = CASE WHEN ITEM_CODE = 'C0001' THEN INVENTORY_SRL ELSE @v_Slot_0_Rifle END,
@v_Slot_1_Rifle = CASE WHEN ITEM_CODE = 'C0003' THEN INVENTORY_SRL ELSE @v_Slot_1_Rifle END,
@v_throw_slot1_sack0 = CASE WHEN ITEM_CODE = 'C0007' AND @v_throw_slot1_sack0 = 0 THEN INVENTORY_SRL ELSE @v_throw_slot1_sack0 END,
@v_throw_slot1_sack1 = CASE WHEN ITEM_CODE = 'C0007' AND @v_throw_slot1_sack0 <> 0 THEN INVENTORY_SRL ELSE @v_throw_slot1_sack1 END
FROM dbo.CF_USER_INVENTORY
WHERE USN = @p_usn
AND ITEM_CODE IN (SELECT ITEM_CODE FROM dbo.CF_ITEM_INFO
WHERE ITEM_ID IN ('2010000101','2010000301','2010000701'));
-- =============================================
-- CF_USER_SACK (Mochilas)
-- =============================================
INSERT INTO dbo.CF_USER_SACK
(USN, SACK_SRL, DISP_ORDER, GAUGE, RIFLE_SLOT, PISTOL_SLOT, KNIFE_SLOT,
THROW_SLOT1, THROW_SLOT2, THROW_SLOT3, EFF_END_DATE)
SELECT
@p_usn, ORDERS, ORDERS, 999999999,
RIFLE_SLOT, 0, 0, THROW_SLOT, 0, 0,
CASE WHEN PERIOD_UNLIMIT = 1 THEN @v_eff_end_date ELSE @v_date END
FROM (
SELECT 0 ORDERS, 1 PERIOD_UNLIMIT, @v_Slot_0_Rifle RIFLE_SLOT, @v_throw_slot1_sack0 THROW_SLOT
UNION ALL
SELECT 1, 1, @v_Slot_1_Rifle, @v_throw_slot1_sack1
UNION ALL
SELECT 2, 0, 0, 0
UNION ALL
SELECT 3, 0, 0, 0
) MULTI;
-- Wish Item
INSERT INTO dbo.CF_WISH_ITEM (USN, UPT_DATE) VALUES (@p_usn, dbo.ConvVAR(@v_date));
-- =============================================
-- CF_USER_MP (Mileage Point)
-- =============================================
IF OBJECT_ID('dbo.CF_USER_MP', 'U') IS NOT NULL
BEGIN
INSERT INTO dbo.CF_USER_MP (
USN, SEASON_NUM, MP, ACC_MP, FIRST_DATE, LAST_UPD_DATE,
SEASON_END_DATE, GET_FRIEND_MP, GET_FRIEND_LAST_DATE,
RESET_LIMIT_MP, BEF_MP_GET, CUR_MP_GET,
GET_VVIP_LAST_DATE, VVIP_GIVE_CNT,
GET_VVIP_LAST_DATE_WP, VVIP_GIVE_MY_SUM_WP,
VVIP_GIVE_OTHER_SUM_WP, GET_VVIP_LAST_DATE_WP_OTHER)
VALUES (
@p_usn,
DATEPART(MONTH, @v_date),
0, 0,
@v_date,
@v_date,
CONVERT(VARCHAR(8), DATEADD(MONTH, 1, @v_date), 112) + '000000',
0,
@v_date,
12000, 0, 0,
'19000101000000', 0,
'19000101000000', 0, 0,
'19000101000000');
END
COMMIT TRAN;
-- Sucesso
SET @p_o_usn = @p_usn;
SET @p_Result = 1;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRAN;
PRINT 'SP_CREATE_USER ERROR: ' + ERROR_MESSAGE();
SET @p_Result = -2;
END CATCH
RETURN @p_Result;
END