• ERD 구조 변경으로 인한 재고 관련 프로시저 삭제 ( 강창선 )

-- 기존 입고 프로시저
DELIMITER $$

CREATE PROCEDURE updateIncomingStock(IN in_incoming_num INT)
BEGIN
    DECLARE v_product_id VARCHAR(100);
    DECLARE v_count INT;
    DECLARE v_user_id INT;
    DECLARE v_admin_id INT;
    DECLARE v_unit_price INT;
    DECLARE v_total_price INT;
    DECLARE v_stock_num INT;
    DECLARE v_sector_id CHAR(3);
    DECLARE v_warehouse_id INT;
    DECLARE stock_not_found INT DEFAULT 0;

    -- 기존에 존재하는 Stock 레코드를 찾고 없으면 1로 설정함
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET stock_not_found = 1;

    -- 입고 테이블에서 완료 처리된 입고 번호를 통해 찾아옴
    SELECT product_id, count, user_id
      INTO v_product_id, v_count, v_user_id
      FROM incoming
     WHERE incoming_num = in_incoming_num
       AND status = '완료';

    -- 해당 제품 가격을 들고옴
    SELECT price
      INTO v_unit_price
      FROM product
     WHERE product_id = v_product_id;

    SET v_total_price = v_unit_price * v_count;

    -- 어드민 id 를 들고 옴
    SELECT admin_id
      INTO v_admin_id
      FROM user
     WHERE user_id = v_user_id;

    -- 섹터 아이디와 창고 아이디를 들고옴
    SELECT sector_id, warehouse_id
      INTO v_sector_id, v_warehouse_id
      FROM rent_history
     WHERE user_id = v_user_id
       AND status = '완료'
     LIMIT 1;

    -- 기존에 있는 재고인지 확인 함
    SET stock_not_found = 0;
    SELECT stock_num
      INTO v_stock_num
      FROM stock
     WHERE user_id = v_user_id
       AND product_id = v_product_id
     LIMIT 1;

    -- 있으면 수량만 업데이트 하고 없으면 새로 추가함
    IF stock_not_found = 1 THEN
        -- 새 재고 생성
        INSERT INTO stock
            (count, total_price, user_id, product_id, incoming_num,
             sector_id, warehouse_id)
        VALUES
            (v_count,
             v_total_price,
             v_user_id,
             v_product_id,
             in_incoming_num,
             v_sector_id,
             v_warehouse_id);

        SET v_stock_num = LAST_INSERT_ID();
    ELSE
        -- 기존 재고에 수량/총가격 누적 + 섹터/창고/회원 추가
        UPDATE stock
           SET count = count + v_count,
               total_price = total_price + v_total_price,
               user_id = v_user_id,
               sector_id = v_sector_id,
               warehouse_id = v_warehouse_id
         WHERE stock_num = v_stock_num;
    END IF;

END$$

DELIMITER ;

-- 새로운 입고 트리거
DELIMITER $$
CREATE TRIGGER insert_stock
    AFTER UPDATE ON incoming
    FOR EACH ROW
BEGIN
    DECLARE v_count INT;
    DECLARE v_product_price INT;
    DECLARE v_product_id VARCHAR(100);
    DECLARE v_total_price INT;
    DECLARE v_user_id VARCHAR(100);
    DECLARE v_stock_num INT;
    DECLARE v_sector_id CHAR(3);
    DECLARE v_warehouse_id INT;
    DECLARE stock_not_fount INT DEFAULT 0;

    -- 기존에 존재하는 stock 레코드를 찾고 없으면 1로 설정
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET stock_not_fount = 1;

    -- 완료 처리된 입고 번호를 통해 제품 id, 수량, 유저 id를 들고옴
    SELECT product_id, count, user_id
    INTO v_product_id, v_count, v_user_id
    FROM incoming
    WHERE incoming_num = NEW.incoming_num
    LIMIT 1;

    -- 제품에서 해당 제품의 가격을 들고옴
    SELECT price
    INTO v_product_price
    FROM product
    WHERE product_id = v_product_id
    LIMIT 1;

    -- 총 가격을 계산해서 할당
    SET v_total_price = v_count * v_product_price;

    -- 섹터 아이디와 창고 아이디를 들고옴
    SELECT sector_id, warehouse_id
    INTO v_sector_id, v_warehouse_id
    FROM rent_history
    WHERE user_id = v_user_id
    LIMIT 1;

    SELECT stock_num
    INTO v_stock_num
    FROM stock
    WHERE user_id = v_user_id
      AND product_id = v_product_id
    LIMIT 1;

    IF NEW.status = '완료' AND OLD.status <> '완료' THEN
        IF stock_not_fount = 1 THEN
            INSERT INTO stock (count, total_price, user_id, product_id, sector_id, warehouse_id)
            VALUES (v_count,v_total_price,v_user_id,v_product_id,v_sector_id,v_warehouse_id);
            SET v_stock_num = LAST_INSERT_ID();
        ELSE
            UPDATE stock
            SET count = count + v_count,
                total_price = total_price + v_total_price,
                user_id = v_user_id,
                sector_id = v_sector_id,
                warehouse_id = v_warehouse_id
            WHERE stock_num = v_stock_num;
        END IF ;
    END IF ;
END $$
DELIMITER ;

-- 트리거를 만들기 위한 쿼리문 root 에서 실행해야 함
SET GLOBAL log_bin_trust_function_creators = 1;

-- 기존 재고 변경 이력 테이블 저장 프로시저
DELIMITER $$
CREATE PROCEDURE updateIncomingStockHistory(IN in_incoming_num INT)
BEGIN
    DECLARE v_product_id VARCHAR(100);
    DECLARE v_sector_id INT;
    DECLARE v_count INT;
    DECLARE v_admin_id INT;
    DECLARE v_user_id INT;
    DECLARE v_stock_num INT;

    -- 제품 아이디, 수량, 유저 아이디 불러오기
    SELECT product_id, count, user_id
    INTO v_product_id, v_count, v_user_id
    FROM incoming
    WHERE incoming_num = in_incoming_num;

    -- 유저아이디를 통해 어드민 아이디 불러오기
    SELECT admin_id
    INTO v_admin_id
    FROM user
    WHERE user_id = v_user_id;

    -- 유저아이디와 제품 아이디를 통해 입고번호를 들고오기
    SELECT stock_num
    INTO v_stock_num
    FROM stock
    WHERE user_id = v_user_id
      AND product_id = v_product_id
    LIMIT 1;

    INSERT INTO stock_history
    (product_id, sector_id, count, change_date, change_type,
     admin_id, stock_num)
    VALUES
        (v_product_id,
         v_sector_id,
         v_count,
         SYSDATE(),
         '입고',
         v_admin_id,
         v_stock_num
        );
END $$
DELIMITER ;

-- 재고 변경 이력 테이블 insert 트리거
DELIMITER $$
CREATE TRIGGER insert_stock_history
    AFTER INSERT ON stock
    FOR EACH ROW
BEGIN
    DECLARE v_product_id VARCHAR(100);
    DECLARE v_user_id VARCHAR(100);
    DECLARE v_admin_id VARCHAR(100);
    DECLARE v_sector_id CHAR(3);
    DECLARE v_warehouse_id INT;
    DECLARE v_count INT;
    DECLARE v_incoming_num INT;
    DECLARE v_stock_num INT;

    -- 제품 아이디, 수량, 유저 아이디 불러오기
    SELECT product_id, count, user_id
        INTO v_product_id, v_count, v_user_id
    FROM stock
        WHERE stock_num = NEW.stock_num;

    -- 유저 아이디를 통해 어드민 아이디 불러오기
    SELECT admin_id
        INTO v_admin_id
    FROM user
        WHERE user_id = v_user_id;

    -- 섹터 아이디와 창고 아이디를 들고옴
    SELECT sector_id, warehouse_id
    INTO v_sector_id, v_warehouse_id
    FROM rent_history
    WHERE user_id = v_user_id
    LIMIT 1;

    -- 입고 번호 들고오기
    SELECT incoming_num
    INTO v_incoming_num
    FROM incoming
    WHERE user_id = v_user_id
    AND status = '완료'
    LIMIT 1;

    -- 유저 아이디와 제품 아이디를 통해 재고번호 들고오기
    SELECT stock_num
        INTO v_stock_num
    FROM stock
        WHERE user_id = v_user_id
        AND product_id = v_product_id
    LIMIT 1;

    INSERT INTO stock_history
        (product_id, sector_id, count, change_date,
         change_type, admin_id, stock_num, incoming_num, outgoing_num)
        VALUES (v_product_id,v_sector_id,v_count,SYSDATE(),
                '입고',v_admin_id,v_stock_num,v_incoming_num, null);

END $$
DELIMITER ;

-- 재고 변경 이력 테이블 update 트리거
DELIMITER $$
CREATE TRIGGER update_stock_history
    AFTER UPDATE
    ON stock
    FOR EACH ROW
BEGIN
    DECLARE v_product_id VARCHAR(100);
    DECLARE v_user_id VARCHAR(100);
    DECLARE v_admin_id VARCHAR(100);
    DECLARE v_sector_id CHAR(3);
    DECLARE v_warehouse_id INT;
    DECLARE v_change INT;
    DECLARE v_incoming_num INT;
    DECLARE v_outgoing_num INT;
    DECLARE v_stock_num INT;

    -- NEW 값을 이용해 정확한 행의 제품 아이디, 유저 아이디, 재고 번호 지정
    SET v_product_id = NEW.product_id;
    SET v_user_id = NEW.user_id;
    SET v_stock_num  = NEW.stock_num;

    -- 유저 아이디를 통해 어드민 아이디 불러오기
    SELECT admin_id
    INTO v_admin_id
    FROM user
    WHERE user_id = v_user_id;

    -- 섹터 아이디와 창고 아이디 불러오기
    SELECT sector_id, warehouse_id
    INTO v_sector_id, v_warehouse_id
    FROM rent_history
    WHERE user_id = v_user_id
    LIMIT 1;

    IF NEW.count > OLD.count THEN
        -- 입고인 경우
        SET v_change = NEW.count - OLD.count;

        SELECT incoming_num
        INTO v_incoming_num
        FROM incoming i
        WHERE NOT EXISTS (
            SELECT 1
            FROM stock_history sh
            WHERE sh.incoming_num = i.incoming_num
        )
        LIMIT 1;

        INSERT INTO stock_history
        (product_id, sector_id, count, change_date, change_type, admin_id,
         stock_num, incoming_num, outgoing_num)
        VALUES (v_product_id, v_sector_id, v_change, NOW(),
                '입고', v_admin_id, v_stock_num, v_incoming_num, NULL);

    ELSEIF NEW.count < OLD.count THEN
        -- 출고인 경우
        SET v_change = OLD.count - NEW.count;

        SELECT outgoing_num
        INTO v_outgoing_num
        FROM outgoing o
        WHERE NOT EXISTS (
            SELECT 1
            FROM stock_history sh
            WHERE sh.outgoing_num = o.outgoing_num
        )
        LIMIT 1;

        INSERT INTO stock_history
        (product_id, sector_id, count, change_date, change_type, admin_id,
         stock_num, incoming_num, outgoing_num)
        VALUES (v_product_id, v_sector_id, v_change, NOW(),
                '출고', v_admin_id, v_stock_num, NULL, v_outgoing_num);
    END IF;
END $$
DELIMITER ;

-- 기존 출고 프로시저
DELIMITER $$
CREATE PROCEDURE updateOutgoingStock(IN in_outgoing_num INT)
BEGIN
    DECLARE v_product_id VARCHAR(100);
    DECLARE v_count INT;
    DECLARE v_user_id INT;
    DECLARE v_total_price INT;
    DECLARE v_unit_price INT;
    DECLARE v_stock_num INT;
    DECLARE stock_not_found INT DEFAULT 0;
    DECLARE stockNotFound INT DEFAULT 0;

    -- 기존에 존재하는 Stock 레코드를 찾고 없으면 1로 설정함
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET stock_not_found = 1;

    -- 출고 테이블에서 완료 처리된 출고 번호를 통해 찾아옴
    SELECT stock_num, count
    INTO v_stock_num, v_count
    FROM outgoing
    WHERE outgoing_num = in_outgoing_num
      AND status = '완료';

    IF stock_not_found = 1 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '출고 완료된 레코드를 찾을 수 없습니다.';
    END IF;

    SELECT user_id, product_id
    INTO v_user_id, v_product_id
    FROM stock
    WHERE stock_num = v_stock_num;

    -- 제품 가격 들고오기
    SELECT price
    INTO v_unit_price
    FROM product
    WHERE product_id = v_product_id;

    SET v_total_price = v_unit_price * v_count;

    -- 기존에 있는 재고인지 확인 함
    SELECT stock_num
    INTO v_stock_num
    FROM stock
    WHERE user_id = v_user_id
      AND product_id = v_product_id
    LIMIT 1;

    IF ROW_COUNT() = 0 THEN
        SET stockNotFound = 1;
    ELSE
        SET stockNotFound = 0;
    END IF;

    -- 있으면 수량만 업데이트 하고 없으면 새로 추가함
    IF stock_not_found = 0 THEN
        -- 있는 재고에 대한 수량 감소
        UPDATE stock
        SET count = count - v_count,
            total_price = total_price - v_total_price
        WHERE stock_num = v_stock_num;

    ELSE
        SELECT '재고가 존재하지 않습니다.';

    END IF;

END$$

DELIMITER ;

-- 재고 테이블 출고 트리거
DELIMITER $$
CREATE TRIGGER outgoing_stock
    AFTER UPDATE
    ON outgoing
    FOR EACH ROW
BEGIN
    DECLARE v_count INT;
    DECLARE v_product_price INT;
    DECLARE v_product_id VARCHAR(100);
    DECLARE v_total_price INT;
    DECLARE v_user_id VARCHAR(100);
    DECLARE v_stock_num INT;
    DECLARE stock_not_found INT DEFAULT 0;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET stock_not_found = 1;

-- 완료 처리된 출고 번호를 통해 수량, 재고번호를 들고옴
    SELECT count, stock_num
    INTO v_count, v_stock_num
    FROM outgoing
    WHERE outgoing_num = NEW.outgoing_num;

    SELECT user_id, product_id
    INTO v_user_id, v_product_id
    FROM stock
    WHERE stock_num = v_stock_num;

    -- 제품에서 해당 제품의 가격을 들고옴
    SELECT price
    INTO v_product_price
    FROM product
    WHERE product_id = v_product_id;

    -- 총 가격을 계산해서 할당
    SET v_total_price = v_count * v_product_price;

    IF NEW.status = '완료' AND OLD.status <> '완료' THEN
        IF stock_not_found = 1 THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '해당 재고가 없습니다.';
        ELSE
            UPDATE
                stock
            SET count       = count - v_count,
                total_price = total_price - v_total_price
            WHERE stock_num = v_stock_num;
        END IF;
    end if;
END $$
DELIMITER ;

BEGIN
    DECLARE v_count INT;
    DECLARE v_product_price INT;
    DECLARE v_product_id VARCHAR(100);
    DECLARE v_total_price INT;
    DECLARE v_user_id VARCHAR(100);
    DECLARE v_stock_num INT;
    DECLARE v_sector_id CHAR(3);
    DECLARE v_warehouse_id INT;
    DECLARE stock_not_fount INT DEFAULT 0;

    -- 기존에 존재하는 stock 레코드를 찾고 없으면 1로 설정
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET stock_not_fount = 1;

    -- 완료 처리된 입고 번호를 통해 제품 id, 수량, 유저 id를 들고옴
    SELECT product_id, count, user_id
    INTO v_product_id, v_count, v_user_id
    FROM incoming
    WHERE incoming_num = NEW.incoming_num;

    -- 제품에서 해당 제품의 가격을 들고옴
    SELECT price
    INTO v_product_price
    FROM product
    WHERE product_id = v_product_id;

    -- 총 가격을 계산해서 할당
    SET v_total_price = v_count * v_product_price;

    -- 섹터 아이디와 창고 아이디를 들고옴
    SELECT sector_id, warehouse_id
    INTO v_sector_id, v_warehouse_id
    FROM rent_history
    WHERE user_id = v_user_id;

    -- 재고 번호를 들고옴
    SELECT stock_num
    INTO v_stock_num
    FROM stock
    WHERE user_id = v_user_id
    AND product_id = v_product_id
    LIMIT 1;

    -- 새로 변경 된 status 가 완료 , 예전 status 가 완료가 아닌 컬럼에 대한 트리거라고 생각하믄 댐
    IF NEW.status = '완료' AND OLD.status <> '완료' THEN
        IF stock_not_fount = 1 THEN
            INSERT INTO stock (count, total_price, user_id, product_id, sector_id, warehouse_id)
            VALUES (v_count,v_total_price,v_user_id,v_product_id,v_sector_id,v_warehouse_id);
            SET v_stock_num = LAST_INSERT_ID();
        ELSE
            UPDATE stock
            SET count = count + v_count,
                total_price = total_price + v_total_price,
                user_id = v_user_id,
                sector_id = v_sector_id,
                warehouse_id = v_warehouse_id
            WHERE stock_num = v_stock_num;
        END IF ;
    END IF ;
END

BEGIN
    DECLARE v_count INT;
    DECLARE v_product_price INT;
    DECLARE v_product_id VARCHAR(100);
    DECLARE v_total_price INT;
    DECLARE v_user_id VARCHAR(100);
    DECLARE v_stock_num INT;
    DECLARE stock_not_found INT DEFAULT 0;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET stock_not_found = 1;

-- 완료 처리된 출고 번호를 통해 수량, 재고번호를 들고옴
    SELECT count, stock_num
    INTO v_count, v_stock_num
    FROM outgoing
    WHERE outgoing_num = NEW.outgoing_num;

    SELECT user_id, product_id
    INTO v_user_id, v_product_id
    FROM stock
    WHERE stock_num = v_stock_num;

    -- 제품에서 해당 제품의 가격을 들고옴
    SELECT price
    INTO v_product_price
    FROM product
    WHERE product_id = v_product_id;

    -- 총 가격을 계산해서 할당
    SET v_total_price = v_count * v_product_price;

    IF NEW.status = '완료' AND OLD.status <> '완료' THEN
        IF stock_not_found = 1 THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '해당 재고가 없습니다.';
        ELSE
            UPDATE
                stock
            SET
                count = count - v_count,
                total_price = total_price - v_total_price
            WHERE stock_num = v_stock_num;
        END IF;
    end if ;
END

• 용적률 계산 방식 오류로 인한 트리거 수정 및 삭제 (방민영)
• 기존) 입고된 상품만 용적률 적용(출고시 용적률이 적용되지 않는 문제) 및 섹터의 용적률 평균으로 창고의 용적률이 들어가는 문제 → 수정) 재고 테이블에 입,출고 되는 상품의 부피를 섹터의 용적률과 창고의 용적률에 적용

-- 재고에 제품이 입력되면 제품의 부피 계산하여 섹터의 용적률에 적용하는 트리거

DROP TRIGGER IF EXISTS updateSectorAndWarehouseFar;
DELIMITER //

CREATE TRIGGER updateSectorAndWarehouseFar
    AFTER UPDATE ON stock
    FOR EACH ROW
BEGIN
    DECLARE sector_total_volume INTEGER;
    DECLARE warehouse_total_volume INTEGER;
    DECLARE current_sector_far DECIMAL(6,2);
    DECLARE current_warehouse_far DECIMAL(6,2);
    DECLARE product_volume_change INTEGER;

    SELECT height * width INTO sector_total_volume
    FROM sector
    WHERE sector_id = NEW.sector_id AND warehouse_id = NEW.warehouse_id;

    SELECT height * width INTO warehouse_total_volume
    FROM warehouse
    WHERE warehouse_id = NEW.warehouse_id;

    SELECT FAR INTO current_sector_far
    FROM sector
    WHERE sector_id = NEW.sector_id AND warehouse_id = NEW.warehouse_id;

    SELECT FAR INTO current_warehouse_far
    FROM warehouse
    WHERE warehouse_id = NEW.warehouse_id;

    SET product_volume_change = (NEW.count - OLD.count) *
                                (SELECT height * width FROM product WHERE product_id = NEW.product_id);

    UPDATE sector
    SET FAR = current_sector_far + (product_volume_change / sector_total_volume) * 100
    WHERE sector_id = NEW.sector_id AND warehouse_id = NEW.warehouse_id;

    UPDATE warehouse
    SET FAR = current_warehouse_far + (product_volume_change / warehouse_total_volume) * 100
    WHERE warehouse_id = NEW.warehouse_id;

END;
//

DELIMITER ;

• 기존) 창고,섹터,기간 선택후 선택한 조건에 해당하는 금액출력 → 수정) 창고와 섹터를 선택한 후 해당하는 섹터의 모든 기간을 출력하는 프로시저로 변경

-- 선택한 창고와 섹터와 기간에 해당하는 가격 출력하는 프로시저
DELIMITER //
CREATE PROCEDURE GetCostInfo(
    IN p_warehouse_id INT,
    IN p_sector_id VARCHAR(10),
    IN p_period INT
)
BEGIN
    SELECT `period`, price
    FROM cost_info
    WHERE warehouse_id = p_warehouse_id
      AND sector_id = p_sector_id
      AND `period` = p_period;
END//
DELIMITER ;

-- 선택한 창고와 섹터의 모든 기간별 가격 출력하는 프로시저
delimiter //
create procedure GetCostInfoSector(
IN p_warehouse_id int, 
IN p_sector_id varchar(10)
)
BEGIN
    SELECT `period`, price
    FROM cost_info
    WHERE warehouse_id = p_warehouse_id
      AND sector_id = p_sector_id;
END//
delimiter ;

• 기존) 본인의 창고가 아닌 창고도 보이는 문제 → 수정) 본인의 창고만 출력

-- 임대내역 테이블에서 상태가 진행중인 레코드 모두 출력하는 프로시저

drop procedure GetinProgressRentHistory;

delimiter //
create procedure GetinProgressRentHistory()
BEGIN
    SELECT * FROM rent_history WHERE status = '진행중';
END;
delimiter ;

DELIMITER //

CREATE PROCEDURE GetInProgressRentHistory(IN p_admin_id VARCHAR(100))
BEGIN
    SELECT rh.* 
    FROM rent_history rh
    JOIN admin a ON rh.warehouse_id = a.warehouse_id
    WHERE a.admin_id = p_admin_id AND rh.status = '진행중';
END //

DELIMITER ;