Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE DEFINER=`root`@`localhost` PROCEDURE `dbo_db_prod_24_02_2020`.`order_procedure`(
- IN agent_id INT,
- IN brand_id INT,
- IN code_format TEXT,
- IN store_id INT(11),
- IN salesman_id INT(11),
- IN creator VARCHAR(191),
- IN status VARCHAR(191),
- IN total_amount DECIMAL(15,2),
- IN so_ref_number VARCHAR(191),
- IN so_ref_date DATE,
- IN notes TEXT,
- IN internal_comment TEXT,
- IN date DATETIME,
- IN user_info INT(11),
- IN created_at DATETIME,
- IN order_type VARCHAR(191),
- IN visitation_detail_id INT(11),
- IN token VARCHAR(191))
- BEGIN
- DECLARE order_code varchar(50);
- DECLARE unique_code varchar(5000);
- DECLARE id_last varchar(50);
- DECLARE exit handler for sqlexception
- BEGIN
- -- ERROR
- select "order_code";
- ROLLBACK;
- END;
- -- DECLARE exit handler for sqlwarning
- -- BEGIN
- -- WARNING
- -- show warnings;
- -- select "unique_code";
- -- ROLLBACK;
- -- END;
- START TRANSACTION;
- set @ModelID = (SELECT orders.code FROM orders WHERE code like code_format order by id desc limit 1);
- SELECT orders.code into order_code FROM orders WHERE code like code_format order by id desc limit 1;
- IF order_code IS NOT NULL THEN
- set unique_code = CONCAT("O",LPAD(agent_id
- ,
- 4,"0"),DATE_FORMAT(NOW(), "%y%m"),LPAD(SUBSTR(@ModelID, -7) + 1, 7,"0"));
- ELSE
- set unique_code = CONCAT("O",LPAD(agent_id, 4,"0"),DATE_FORMAT(NOW(), "%y%m"),LPAD(1, 7,"0"));
- END IF;
- INSERT INTO orders (code, brand_id, agent_id, store_id, salesman_id, creator, status, total_amount, so_ref_number, so_ref_date, notes, internal_comment, g_lat, g_lng, date, created_at, updated_at, deleted_at, user_id_created, user_id_updated, order_type, visitation_detail_id,token)
- VALUES (unique_code, brand_id, agent_id, store_id, salesman_id, creator, status, total_amount, so_ref_number, so_ref_date, notes, internal_comment, NULL, NULL, date, created_at, created_at, NULL, user_info, user_info, order_type, visitation_detail_id,token);
- set id_last = LAST_INSERT_ID();
- SELECT unique_code, id_last;
- COMMIT;
- END
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement