CezFfLcM

1DROP TABLE IF EXISTS `payment`;
2CREATE TABLE `payment` (
3    `id` int NOT NULL AUTO_INCREMENT,
4    `txnId` varchar(20) NOT NULL,
5    `paymentAmount` decimal(7,2) NOT NULL,
6    `paymentCurrency` varchar(15) NOT NULL,
7    `paymentStatus` varchar(25) NOT NULL,
8    `userId` INT NOT NULL,
9    `itemId` INT NOT NULL,
10    `itemName` varchar(50) NOT NULL,
11    `createdTime` datetime NOT NULL,
12    PRIMARY KEY (`id`)
13    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
14    
15DROP PROCEDURE IF EXISTS pa_payment_register;
16DELIMITER //
17CREATE PROCEDURE pa_payment_register
18(
19 	IN _txtnId VARCHAR(20),
20 	IN _paymentAmount DECIMAL(7,2),
21 	IN _paymentCurrency VARCHAR(15),
22 	IN _paymentStatus VARCHAR(25),
23 	IN _userId INT,
24 	IN _itemdId INT,
25 	IN _itemName VARCHAR(50),
26 	OUT rpta INT
27)
28BEGIN
29	IF (SELECT COUNT(*) AS existsPayment FROM payment WHERE txnid = _txtnId) > 0 THEN
30		SET rpta = 0;
31	END IF;
32	
33	INSERT INTO payment (txtnId, paymentAmount, paymentCurrency, paymentStatus, userId, itemId, itemName, createdTime)
34	VALUES (_txtnId, _paymentAmount, _paymentCurrency, _paymentStatus, _userId, _itemdId, _itemName, NOW());
35	 
36	SET rpta = LAST_INSERT_ID();
37END//
38DELIMITER ;