SCRIPTS VARIOS CHIVO

ALTER TABLE tm_usuario ADD salt1 VARCHAR(50);
ALTER TABLE tm_usuario ADD hash1 VARCHAR(200);
ALTER TABLE tm_usuario ADD salt2 VARCHAR(50);
ALTER TABLE tm_usuario ADD hash2 VARCHAR(200);

DROP TABLE IF EXISTS `payment`;
CREATE TABLE `payment` (
    `id` int NOT NULL AUTO_INCREMENT,
    `txnId` varchar(20) NOT NULL,
    `paymentAmount` decimal(7,2) NOT NULL,
    `paymentCurrency` varchar(15) NOT NULL,
    `paymentStatus` varchar(25) NOT NULL,
    `userId` INT NOT NULL,
    `itemId` INT NOT NULL,
    `itemName` varchar(50) NOT NULL,
    `createdTime` datetime NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

DROP PROCEDURE IF EXISTS pa_payment_register;
DELIMITER //
CREATE PROCEDURE pa_payment_register
(
    IN _txtnId VARCHAR(20),
    IN _paymentAmount DECIMAL(7,2),
    IN _paymentCurrency VARCHAR(15),
    IN _paymentStatus VARCHAR(25),
    IN _userId INT,
    IN _itemdId INT,
    IN _itemName VARCHAR(50),
    OUT rpta INT
)
BEGIN
    IF (SELECT COUNT(*) AS existsPayment FROM payment WHERE txnid = _txtnId) > 0 THEN
        SET rpta = 0;
    END IF;

    INSERT INTO payment (txtnId, paymentAmount, paymentCurrency, paymentStatus, userId, itemId, itemName, createdTime)
    VALUES (_txtnId, _paymentAmount, _paymentCurrency, _paymentStatus, _userId, _itemdId, _itemName, NOW());

    SET rpta = LAST_INSERT_ID();
END//
DELIMITER ;


DROP  PROCEDURE IF EXISTS pa_tokenUser_register;
DELIMITER //
CREATE PROCEDURE pa_tokenUser_register
(
    IN _source VARCHAR(10),
    IN _userId INT,
    IN _token TEXT,
    IN _salt VARCHAR(200),
    IN _hash VARCHAR(200),
    OUT `rpta` INT(11)
)
BEGIN
    UPDATE tokenUser SET active = 0 WHERE source = _source AND userId = _userId AND active = 1;

    UPDATE tm_usuario SET salt1 = _salt, hash2 = _hash WHERE tm_idusuario = _userId;

    INSERT INTO tokenUser (userId, source, token, active, createDate, modifyDate)
    VALUES (_userId, _source, _token, 1, NOW(), NOW());

    SELECT LAST_INSERT_ID() INTO rpta;
END//
DELIMITER ;


DROP  PROCEDURE IF EXISTS pa_tokenUser_get;
DELIMITER //
CREATE PROCEDURE pa_tokenUser_get
(
    IN _source VARCHAR(10),
    IN _userId INT
)
BEGIN
    SELECT token FROM tokenUser WHERE source = _source AND userId = _userId AND active = 1;
END//
DELIMITER ;

DROP PROCEDURE IF EXISTS `sp_user_register`;
DELIMITER //
CREATE PROCEDURE `sp_user_register`(IN `interfazusuario` VARCHAR(2), IN `login` VARCHAR(50),
    IN `nombres` VARCHAR(150),
    IN `apellidos` VARCHAR(150),
    IN `email` VARCHAR(100),
    IN `facebook` TINYINT(11),
    IN `codigofacebook` VARCHAR(200),
    IN `salt` VARCHAR(200),
    IN `hash` VARCHAR(200),
    IN `idusuario` INT(11),
    OUT `rpta` INT(11), OUT `titulomsje` VARCHAR(200), OUT `contenidomsje` VARCHAR(200)
)
BEGIN
    INSERT INTO tm_usuario (ta_interfazusuario, ta_tipousuario, tm_login, tm_nombres, tm_apellidos, tm_email,
    tm_facebook, tm_codigo_facebook, salt2, hash1,  Activo, IdUsuarioReg, FechaReg , IdUsuarioAct, FechaAct)
    VALUES (interfazusuario, '01', login, clave, nombres, apellidos, email, facebook, codigofacebook, salt, hash, 1, idusuario, NOW(), idusuario, NOW());

    SELECT LAST_INSERT_ID() into rpta;
    SET titulomsje = 'Registrado correctamente';
    SET contenidomsje = 'La operación se realizo con éxito';
END//
DELIMITER ;