qFh7SQPW

1ALTER TABLE tm_usuario ADD salt1 VARCHAR(50);
2ALTER TABLE tm_usuario ADD hash1 VARCHAR(200);
3ALTER TABLE tm_usuario ADD salt2 VARCHAR(50);
4ALTER TABLE tm_usuario ADD hash2 VARCHAR(200);
5
6DROP TABLE IF EXISTS `payment`;
7CREATE TABLE `payment` (
8    `id` int NOT NULL AUTO_INCREMENT,
9    `txnId` varchar(20) NOT NULL,
10    `paymentAmount` decimal(7,2) NOT NULL,
11    `paymentCurrency` varchar(15) NOT NULL,
12    `paymentStatus` varchar(25) NOT NULL,
13    `userId` INT NOT NULL,
14    `itemId` INT NOT NULL,
15    `itemName` varchar(50) NOT NULL,
16    `createdTime` datetime NOT NULL,
17    PRIMARY KEY (`id`)
18    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
19    
20DROP PROCEDURE IF EXISTS pa_payment_register;
21DELIMITER //
22CREATE PROCEDURE pa_payment_register
23(
24 	IN _txtnId VARCHAR(20),
25 	IN _paymentAmount DECIMAL(7,2),
26 	IN _paymentCurrency VARCHAR(15),
27 	IN _paymentStatus VARCHAR(25),
28 	IN _userId INT,
29 	IN _itemdId INT,
30 	IN _itemName VARCHAR(50),
31 	OUT rpta INT
32)
33BEGIN
34	IF (SELECT COUNT(*) AS existsPayment FROM payment WHERE txnid = _txtnId) > 0 THEN
35		SET rpta = 0;
36	END IF;
37	
38	INSERT INTO payment (txtnId, paymentAmount, paymentCurrency, paymentStatus, userId, itemId, itemName, createdTime)
39	VALUES (_txtnId, _paymentAmount, _paymentCurrency, _paymentStatus, _userId, _itemdId, _itemName, NOW());
40	 
41	SET rpta = LAST_INSERT_ID();
42END//
43DELIMITER ;
44
45
46DROP  PROCEDURE IF EXISTS pa_tokenUser_register;
47DELIMITER //
48CREATE PROCEDURE pa_tokenUser_register
49(
50	IN _source VARCHAR(10),
51	IN _userId INT,
52	IN _token TEXT,
53	IN _salt VARCHAR(200),
54	IN _hash VARCHAR(200),
55	OUT `rpta` INT(11)
56)
57BEGIN
58	UPDATE tokenUser SET active = 0 WHERE source = _source AND userId = _userId AND active = 1;
59	
60	UPDATE tm_usuario SET salt1 = _salt, hash2 = _hash WHERE tm_idusuario = _userId;
61	
62	INSERT INTO tokenUser (userId, source, token, active, createDate, modifyDate)
63	VALUES (_userId, _source, _token, 1, NOW(), NOW());
64
65	SELECT LAST_INSERT_ID() INTO rpta;
66END//
67DELIMITER ;
68
69
70DROP  PROCEDURE IF EXISTS pa_tokenUser_get;
71DELIMITER //
72CREATE PROCEDURE pa_tokenUser_get
73(
74	IN _source VARCHAR(10),
75	IN _userId INT
76)
77BEGIN
78	SELECT token FROM tokenUser WHERE source = _source AND userId = _userId AND active = 1;
79END//
80DELIMITER ;
81
82DROP PROCEDURE IF EXISTS `sp_user_register`;
83DELIMITER //
84CREATE PROCEDURE `sp_user_register`(IN `interfazusuario` VARCHAR(2), IN `login` VARCHAR(50), 
85	IN `nombres` VARCHAR(150), 
86	IN `apellidos` VARCHAR(150), 
87	IN `email` VARCHAR(100), 
88	IN `facebook` TINYINT(11), 
89	IN `codigofacebook` VARCHAR(200), 
90	IN `salt` VARCHAR(200), 
91	IN `hash` VARCHAR(200), 
92	IN `idusuario` INT(11), 
93	OUT `rpta` INT(11), OUT `titulomsje` VARCHAR(200), OUT `contenidomsje` VARCHAR(200)
94)
95BEGIN
96	INSERT INTO tm_usuario (ta_interfazusuario, ta_tipousuario, tm_login, tm_nombres, tm_apellidos, tm_email, 
97	tm_facebook, tm_codigo_facebook, salt2, hash1,  Activo, IdUsuarioReg, FechaReg , IdUsuarioAct, FechaAct) 
98	VALUES (interfazusuario, '01', login, clave, nombres, apellidos, email, facebook, codigofacebook, salt, hash, 1, idusuario, NOW(), idusuario, NOW());
99	
100	SELECT LAST_INSERT_ID() into rpta; 
101	SET titulomsje = 'Registrado correctamente';
102    SET contenidomsje = 'La operación se realizo con éxito';
103END//
104DELIMITER ;