Compalex.net

Database schema compare tool

tables views procedures functions indexes triggers  all changed

zdback_devdb

Developer database

localhost:3306/zdback_devdb

zdback_uatdb

Production database

localhost:3306/zdback_uatdb

sp_os4kh_documents 68

  • BEGIN
  • DECLARE finished INTEGER DEFAULT 0;
  • DECLARE pId BIGINT DEFAULT 0;
  • DECLARE pContractNo VARCHAR(50) DEFAULT "";
  • DECLARE pIdCard VARCHAR(13) DEFAULT "";
  • DECLARE pName VARCHAR(255) DEFAULT "";
  • DECLARE pAmount DECIMAL(8,2) DEFAULT 0;
  • DECLARE pRefNo VARCHAR(50) DEFAULT "";
  • DECLARE contract_cursor CURSOR FOR
  • SELECT c.application_form_id
  • , c.contract_no
  • , cs.id_card_number
  • , CONCAT(IFNULL(pn.name, ''), cs.first_name, ' ', cs.last_name) AS name
  • , IF(a.reference_no IS NULL, c.loan_amount, (
  • SELECT c.loan_amount - r.loan_amount
  • FROM contracts r
  • WHERE r.contract_no = a.reference_no
  • LIMIT 1
  • )) AS amount
  • , IFNULL(a.reference_no, c.contract_no) AS ref_no
  • FROM contracts c
  • INNER JOIN application_forms a ON a.id = c.application_form_id
  • INNER JOIN customers cs ON cs.id = c.customer_id
  • LEFT JOIN prefix_names pn ON pn.id = cs.prefix_name_id
  • WHERE c.canceled_at IS NULL
  • AND c.contract_date BETWEEN begin_date AND end_date
  • AND c.branch_id = branch_id
  • ORDER BY c.contract_date;
  • DECLARE CONTINUE HANDLER
  • FOR NOT FOUND SET finished = 1;
  • CREATE TEMPORARY TABLE IF NOT EXISTS tmp_result (
  • section INT,
  • section_name VARCHAR(50),
  • id_card VARCHAR(13),
  • name VARCHAR(255),
  • contract_no VARCHAR(50),
  • amount DECIMAL(8,2) null,
  • stamp_duty DECIMAL(8,2)
  • );
  • OPEN contract_cursor;
  • contract_loop: LOOP
  • FETCH contract_cursor INTO pId, pContractNo, pIdCard, pName, pAmount, pRefNo;
  • IF finished = 1 THEN
  • LEAVE contract_loop;
  • END IF;
  • DELETE FROM tmp_result WHERE contract_no = pRefNo;
  • INSERT INTO tmp_result (section, section_name, id_card, name, contract_no, amount, stamp_duty)
  • VALUES (5, 'กู้ยืมเงิน', pIdCard, pName, pContractNo, pAmount, CEIL(pAmount / 2000));
  • VALUES (23, 'คู่ฉบับ', pIdCard, pName, pContractNo, NULL, 5);
  • SELECT 17 AS section
  • , 'ค้ำประกัน' AS section_name
  • , cg.id_card_number AS id_card
  • , CONCAT(IFNULL(png.name, ''), cg.first_name, ' ', cg.last_name) AS name
  • , pContractNo
  • , NULL AS amount
  • , 10 AS stamp_duty
  • FROM application_guarantors g
  • INNER JOIN customers cg ON cg.id = g.customer_id
  • LEFT JOIN prefix_names png ON png.id = cg.prefix_name_id
  • WHERE g.application_form_id = pId
  • AND g.canceled_at IS NULL;
  • END LOOP contract_loop;
  • CLOSE contract_cursor;
  • SELECT * FROM tmp_result r ORDER BY r.contract_no, r.section;
  • DROP TEMPORARY TABLE IF EXISTS tmp_result;
  • END

sp_os4kh_documents 68

  • BEGIN
  • DECLARE finished INTEGER DEFAULT 0;
  • DECLARE pId BIGINT DEFAULT 0;
  • DECLARE pContractNo VARCHAR(50) DEFAULT "";
  • DECLARE pIdCard VARCHAR(13) DEFAULT "";
  • DECLARE pName VARCHAR(255) DEFAULT "";
  • DECLARE pAmount DECIMAL(8,2) DEFAULT 0;
  • DECLARE pRefNo VARCHAR(50) DEFAULT "";
  • DECLARE contract_cursor CURSOR FOR
  • SELECT c.application_form_id
  • , c.contract_no
  • , cs.id_card_number
  • , CONCAT(IFNULL(pn.name, ''), cs.first_name, ' ', cs.last_name) AS name
  • , IF(a.reference_no IS NULL, c.loan_amount, (
  • SELECT c.loan_amount - r.loan_amount
  • FROM contracts r
  • WHERE r.contract_no = a.reference_no
  • LIMIT 1
  • )) AS amount
  • , IFNULL(a.reference_no, c.contract_no) AS ref_no
  • FROM contracts c
  • INNER JOIN application_forms a ON a.id = c.application_form_id
  • INNER JOIN customers cs ON cs.id = c.customer_id
  • LEFT JOIN prefix_names pn ON pn.id = cs.prefix_name_id
  • WHERE c.canceled_at IS NULL
  • AND c.contract_date BETWEEN begin_date AND end_date
  • AND c.branch_id = branch_id
  • ORDER BY c.contract_date;
  • DECLARE CONTINUE HANDLER
  • FOR NOT FOUND SET finished = 1;
  • CREATE TEMPORARY TABLE IF NOT EXISTS tmp_result (
  • section INT,
  • section_name VARCHAR(50),
  • id_card VARCHAR(13),
  • name VARCHAR(255),
  • contract_no VARCHAR(50),
  • amount DECIMAL(8,2) null,
  • stamp_duty DECIMAL(8,2)
  • );
  • OPEN contract_cursor;
  • contract_loop: LOOP
  • FETCH contract_cursor INTO pId, pContractNo, pIdCard, pName, pAmount, pRefNo;
  • IF finished = 1 THEN
  • LEAVE contract_loop;
  • END IF;
  • DELETE FROM tmp_result WHERE contract_no = pRefNo;
  • INSERT INTO tmp_result (section, section_name, id_card, name, contract_no, amount, stamp_duty)
  • VALUES (5, 'กู้ยืมเงิน', pIdCard, pName, pContractNo, pAmount, CEIL(pAmount / 2000));
  • VALUES (23, 'คู่ฉบับ', pIdCard, pName, pContractNo, NULL, 5);
  • SELECT 17 AS section
  • , 'ค้ำประกัน' AS section_name
  • , cg.id_card_number AS id_card
  • , CONCAT(IFNULL(png.name, ''), cg.first_name, ' ', cg.last_name) AS name
  • , pContractNo
  • , NULL AS amount
  • , 10 AS stamp_duty
  • FROM application_guarantors g
  • INNER JOIN customers cg ON cg.id = g.customer_id
  • LEFT JOIN prefix_names png ON png.id = cg.prefix_name_id
  • WHERE g.application_form_id = pId
  • AND g.canceled_at IS NULL;
  • END LOOP contract_loop;
  • CLOSE contract_cursor;
  • SELECT * FROM tmp_result r ORDER BY r.contract_no, r.section;
  • DROP TEMPORARY TABLE IF EXISTS tmp_result;
  • END

 

For more information go to compalex.net