procedures.sql

DROP PROCEDURE IF EXISTS `registrar_usuario` ;
DROP PROCEDURE IF EXISTS `registrar_endereco` ;
DROP PROCEDURE IF EXISTS `registrar_revenda` ;

DELIMITER $$

-- Procedure OK
CREATE PROCEDURE registrar_endereco(
	IN stateName VARCHAR(64),
    IN stateInitials CHAR(2),
    IN countryName VARCHAR(64),
    IN cityName VARCHAR(64),
    IN districtName VARCHAR(64),
    IN addressCEP VARCHAR(9),
    IN addressNumber VARCHAR(24),
    IN addressStreet VARCHAR(64),
    IN addressComplement VARCHAR(24),
    OUT addressId INT(11)
)
BEGIN
	DECLARE stateId INT;
    DECLARE cityId INT;
    DECLARE districtId INT;
    
    IF (
		SELECT EXISTS (
			SELECT 1 FROM state as stt
			WHERE stt.name = stateName AND stt.initials = stateInitials AND stt.country = countryName
        )
    ) THEN
		SELECT stt.id INTO @stateId FROM state as stt
		WHERE stt.name = stateName AND stt.initials = stateInitials AND stt.country = countryName;
    ELSE
		INSERT INTO `state` (`name`, `initials`, `country`, `created_at`, `updated_at`, `state`) 
        VALUES (stateName, stateInitials, countryName, NOW(), NOW(), 'A');
        SET stateId = last_insert_id();
    END IF;
    
    IF (
		SELECT EXISTS(
			SELECT 1 FROM city as c
            WHERE c.name = cityName AND c.state_id = @stateId
        )
    ) THEN
		SELECT c.id INTO @cityId from city as c
        WHERE c.name = cityName AND c.state_id = @stateId;
    ELSE
		INSERT INTO `city` (`name`, `created_at`, `updated_at`, `state`, `state_id`) 
        VALUES (cityName, NOW(), NOW(), 'A', @stateId);
        SET cityId = last_insert_id();
    END IF;
    
    IF (
		SELECT EXISTS(
			SELECT 1 FROM district d
            WHERE d.name = districtName AND d.city_id = @cityId
		)
    ) THEN
		SELECT d.id INTO @districtId from district as d
        WHERE d.name = districtName AND d.city_id = @cityId;
    ELSE
		INSERT INTO `district` (`name`, `created_at`, `updated_at`, `state`, `city_id`) 
        VALUES (districtName, NOW(), NOW(), 'A', @cityId);
    END IF;
    
    IF (
		SELECT EXISTS(
			SELECT 1 FROM address as a
            WHERE a.CEP = addressCEP AND a.number = addressNumber 
            AND a.street = addressStreet AND a.complement = addressComplement
        )
    ) THEN
		SELECT a.id INTO addressId FROM address a
        WHERE a.CEP = addressCEP AND a.number = addressNumber 
		AND a.street = addressStreet AND a.complement = addressComplement;
    ELSE
		INSERT INTO `address` (`CEP`, `number`, `street`, `complement`, `created_at`, `updated_at`, `state`, `district_id`) 
		VALUES (addressCEP, addressNumber, addressStreet, addressComplement, NOW(), NOW(), 'A', @districtId);
		SET addressId = last_insert_id();
    END IF;
END$$

-- Procedure OK
CREATE PROCEDURE registrar_usuario(
	IN stateName VARCHAR(64),
    IN stateInitials CHAR(2),
    IN countryName VARCHAR(64),
    IN cityName VARCHAR(64),
    IN districtName VARCHAR(64),
    IN addressCEP VARCHAR(9),
    IN addressNumber VARCHAR(24),
    IN addressStreet VARCHAR(64),
    IN addressComplement VARCHAR(24),
    IN userName VARCHAR(64),
    IN userCPF VARCHAR(14),
    IN userRG VARCHAR(10),
    IN userDataNascimento DATE,
    IN userUsername VARCHAR(64),
    IN userPassword VARCHAR(255)
)
BEGIN
	DECLARE addressId INT;
	CALL registrar_endereco(stateName, stateInitials, countryName, cityName, 
			districtName, addressCEP, addressNumber, addressStreet, addressComplement, @addressId);
	INSERT INTO `user` (`username`, `password`, `name`, `cpf`, `rg`, `birth_date`, `admission_date`, `resignation_date`, `created_at`, `updated_at`, `state`, `address_id`) 
    VALUES (userUsername, userPassword, userName, userCPF, userRG, userDataNascimento, NOW(), NULL, NOW(), NOW(), 'A', @addressId);
END$$

-- Procedure on going
CREATE PROCEDURE registrar_revenda(
	IN stateName VARCHAR(64),
    IN stateInitials CHAR(2),
    IN countryName VARCHAR(64),
    IN cityName VARCHAR(64),
    IN districtName VARCHAR(64),
    IN addressCEP VARCHAR(9),
    IN addressNumber VARCHAR(24),
    IN addressStreet VARCHAR(64),
    IN addressComplement VARCHAR(24),
    IN resaleName VARCHAR(64)
)
BEGIN
	DECLARE addressId INT;
	CALL registrar_endereco(stateName, stateInitials, countryName, cityName, 
			districtName, addressCEP, addressNumber, addressStreet, addressComplement, @addressId);
	INSERT INTO `resale` (`name`, `created_at`, `updated_at`, `state`, `address_id`) 
    VALUES (resaleName, NOW(), NOW(), 'A', @addressId);
END$$
DELIMITER ;


CALL registrar_usuario("Rio Grande do Sulis", "RS", "Brasilis", "Itaqui", 
			"Enio Sayago", "97650-000", "1391", "Rodrigues Lima", "", "Gustavo Fão Valvassori",
            "033.750.670-11", "2088525445", now(), "faogustavo", "1234");
            
CALL registrar_revenda("Rio Grande do Sulis", "RS", "Brasilis", "Itaquis", 
			"Enio Sayago", "97650-000", "1391", "Rodrigues Lima", "", "Revenda N");