/* SAVE MEDICINES FROM A TO Z */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS saveMedicines;
COMMIT;

DELIMITER //

CREATE PROCEDURE `saveMedicines`(`p_transaction` VARCHAR(36))
BEGIN

DECLARE errno INT;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
  GET CURRENT DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO;
  SELECT errno AS MYSQL_ERROR;

  ROLLBACK;

	INSERT INTO debug_log(activity, ts_when, log) 
  SELECT 'PROC ERR: saveMeds', NOW(), errno; 

  RESIGNAL;
END;

START TRANSACTION;

  INSERT IGNORE INTO `Medicines`(`ID`, `FullName`, `TradeName`, `TradeStr`, `TradeForm`, `Schedule`, `TradeManufacturer`, 
    `ImageDate`, `createdAt`, `updatedAt`)
  SELECT `T`.`ID`, `T`.`FullName`, `T`.`TradeName`, `T`.`TradeStr`, `T`.`TradeForm`, `T`.`Schedule`, 
    `T`.`TradeManufacturer`, `T`.`ImageDate`, NOW(), NOW()
  FROM `TempMedicines` AS `T`
  WHERE `transaction` = p_transaction;

  UPDATE `Medicines` AS `M`
  INNER JOIN `TempMedicines` AS `T` ON `M`.`ID` = `T`.`ID` AND `T`.`transaction` = p_transaction
  SET `M`.`FullName` = `T`.`FullName`,
  `M`.`TradeName` = `T`.`TradeName`,
  `M`.`TradeStr` = `T`.`TradeStr`,
  `M`.`TradeForm` = `T`.`TradeForm`,
  `M`.`Schedule` = `T`.`Schedule`,
  `M`.`TradeManufacturer` = `T`.`TradeManufacturer`,
  `M`.`updatedAt` = NOW()
  WHERE BINARY `M`.`FullName` != BINARY `T`.`FullName`
  OR BINARY `M`.`TradeName` != BINARY `T`.`TradeName` 
  OR BINARY `M`.`TradeStr` != BINARY `T`.`TradeStr` 
  OR BINARY `M`.`TradeForm` != BINARY `T`.`TradeForm` 
  OR BINARY `M`.`Schedule` != BINARY `T`.`Schedule` 
  OR BINARY `M`.`TradeManufacturer` != BINARY `T`.`TradeManufacturer`;

  DELETE FROM `TempMedicines`
  WHERE `transaction` = p_transaction;

COMMIT WORK;

END
//

COMMIT //

DELIMITER ;
