CREATE DEFINER=devrds@% PROCEDURE prod_gateway_db_4th_Sep_6am.initiate_transaction(
IN merchant_key VARCHAR(15), IN sub_merchant_id VARCHAR(15), IN txnid VARCHAR(40), IN email VARCHAR(200),
IN phone VARCHAR(45), IN firstname VARCHAR(150), IN udf1 VARCHAR(300), IN udf2 VARCHAR(300),
IN udf3 VARCHAR(300), IN udf4 VARCHAR(300), IN udf5 VARCHAR(300), IN udf6 VARCHAR(300), IN udf7 VARCHAR(300), IN surl VARCHAR(2000), IN furl VARCHAR(2000),
IN start_time VARCHAR(50), IN address1 VARCHAR(100), IN address2 VARCHAR(100), IN city VARCHAR(50),
IN state VARCHAR(50), IN country VARCHAR(50), IN zipcode VARCHAR(10), IN productinfo VARCHAR(45),
IN unique_id VARCHAR(255), IN amount FLOAT(15), IN isMobile INT(5), IN user_agent VARCHAR(200), IN device VARCHAR(45),
IN referrer_ip VARCHAR(45), IN referrer_url VARCHAR(2000),IN environment VARCHAR(4),IN split_payments VARCHAR(255),
IN request_flow VARCHAR(15),IN show_payment_mode VARCHAR(255),IN allowed_cards VARCHAR(255),
OUT status VARCHAR(10),OUT message VARCHAR(100),OUT peb_transaction_id VARCHAR(15),OUT is_coupon_enabled TINYINT(1),OUT cashback_percent INT)
BEGIN
DECLARE merchant_id INT;
DECLARE is_subaggregator INT;
DECLARE submerchant_id INT;
DECLARE db_query VARCHAR(1000);
DECLARE customer_id INT;
DECLARE transaction_id INT;
DECLARE access_key VARCHAR(64);
SET status = “false”;
SET access_key = generate_access_key(start_time);
SELECT mi.id,is_sub_aggregator,cashback_status,cashback_percentage INTO merchant_id ,is_subaggregator,is_coupon_enabled,cashback_percent from peb_merchant_info mi,peb_merchant_settings ms,peb_gateway_settings gs WHERE mi.merchant_settings_id = ms.id and mi.gateway_settings_id = gs.id and mi.key=merchant_key;
IF is_subaggregator = 1 THEN
SELECT smi.id,gs.cashback_status,gs.cashback_percentage INTO submerchant_id,is_coupon_enabled,cashback_percent FROM peb_submerchant_info smi,peb_gateway_settings gs WHERE smi.submerchant_id = sub_merchant_id AND smi.merchant_id = merchant_id AND smi.gateway_settings_id = gs.id AND is_active = 1 ;
END IF;
INSERT INTO peb_customer_request (merchant_txn_id, email, phone, first_name, product_info, access_key, is_done, timestamp, address1, address2, city, state, country, zipcode, additional1, additional2, additional3, additional4, additional5, additional6, additional7, surl, furl, start_time, is_coupon_enabled, coupon_list, merchant_id, unique_id,split_payments, request_flow, show_payment_mode, allowed_cards) VALUES (txnid,email,phone,firstname,productinfo,access_key,0,NOW(),address1,address2,city,state,country,zipcode,udf1,udf2,udf3,udf4,udf5,udf6,udf7,surl,furl,start_time,is_coupon_enabled,‘’,merchant_id,unique_id,split_payments,request_flow, show_payment_mode, allowed_cards);
SET customer_id = LAST_INSERT_ID();
SET peb_transaction_id = generate_transaction_id(environment);
IF submerchant_id = ‘’ THEN SET submerchant_id = NULL;END IF;
INSERT INTO peb_transaction_info(
peb_transaction_id,
amount,
peb_transaction_status,
amount_tdr,
peb_cash_back_percentage,
peb_customer_id,
peb_merchant_id,
peb_submerchant_id,
peb_is_mobile_app_initiated,
peb_user_agent,
peb_device,
peb_referer_ip,
peb_referer,
peb_transaction_date,
currency_type,
transaction_ref_num,
bank_ref_num,
transaction_type,
error_msg,
checksum,
peb_card_category,
peb_payout_id,
peb_pg_payout_id,
retry_valid,
peb_settlement_status,
peb_chargeback_status,
peb_put_in_sheet,
pg_id_id,
is_stos_updated,
hdfc_transaction_status,
peb_name_on_card,
peb_upi_va,
peb_card_number,
peb_subaggregator_commission,
payment_category,
peb_refund_status
) VALUES (
peb_transaction_id,
round(amount,2),
‘preinitiated’,
round(amount,2),
cashback_percent,
customer_id,
merchant_id,
submerchant_id,
isMobile,
user_agent,
device,
referrer_ip,
referrer_url,
start_time,
‘INR’,
‘NA’,
‘NA’,
‘NA’,
‘NA’,
‘NA’,
‘NA’,
NULL,
‘’,
0,
‘UT’,
‘NC’,
0,
NULL,
0,
‘NA’,
‘NA’,
‘NA’,
NULL,
0,
‘DEFAULT’,
‘’
);
SET transaction_id = LAST_INSERT_ID();
SET status=“true”;
SET message=access_key;
END