Advertisement
Guest User

Untitled

a guest
Feb 26th, 2020
109
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.23 KB | None | 0 0
  1. CREATE DEFINER=`root`@`localhost` PROCEDURE `dbo_db_prod_24_02_2020`.`order_procedure`(
  2. IN agent_id INT,
  3. IN brand_id INT,
  4. IN code_format TEXT,
  5. IN store_id INT(11),
  6. IN salesman_id INT(11),
  7. IN creator VARCHAR(191),
  8. IN status VARCHAR(191),
  9. IN total_amount DECIMAL(15,2),
  10. IN so_ref_number VARCHAR(191),
  11. IN so_ref_date DATE,
  12. IN notes TEXT,
  13. IN internal_comment TEXT,
  14. IN date DATETIME,
  15. IN user_info INT(11),
  16. IN created_at DATETIME,
  17. IN order_type VARCHAR(191),
  18. IN visitation_detail_id INT(11),
  19. IN token VARCHAR(191))
  20. BEGIN
  21. DECLARE order_code varchar(50);
  22. DECLARE unique_code varchar(5000);
  23. DECLARE id_last varchar(50);
  24.  
  25. DECLARE exit handler for sqlexception
  26. BEGIN
  27. -- ERROR
  28. select "order_code";
  29. ROLLBACK;
  30. END;
  31.  
  32. -- DECLARE exit handler for sqlwarning
  33. -- BEGIN
  34. -- WARNING
  35. -- show warnings;
  36. -- select "unique_code";
  37. -- ROLLBACK;
  38. -- END;
  39.  
  40. START TRANSACTION;
  41. set @ModelID = (SELECT orders.code FROM orders WHERE code like code_format order by id desc limit 1);
  42.  
  43. SELECT orders.code into order_code FROM orders WHERE code like code_format order by id desc limit 1;
  44.  
  45. IF order_code IS NOT NULL THEN
  46. set unique_code = CONCAT("O",LPAD(agent_id
  47. ,
  48. 4,"0"),DATE_FORMAT(NOW(), "%y%m"),LPAD(SUBSTR(@ModelID, -7) + 1, 7,"0"));
  49. ELSE
  50. set unique_code = CONCAT("O",LPAD(agent_id, 4,"0"),DATE_FORMAT(NOW(), "%y%m"),LPAD(1, 7,"0"));
  51. END IF;
  52.  
  53. 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)
  54. 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);
  55. set id_last = LAST_INSERT_ID();
  56. SELECT unique_code, id_last;
  57. COMMIT;
  58. END
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement