StockLevelRepositoryImpl.java

package com.tradecloud.repository.requestforquote.impl;


import com.tradecloud.domain.model.requestforquote.StockLevel;
import com.tradecloud.dto.requestforquote.StockLevelDTO;
import com.tradecloud.repository.base.impl.RepositoryBaseImpl;
import com.tradecloud.repository.requestforquote.StockLevelRepository;
import org.hibernate.Session;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.stream.Collectors;

@Repository
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.DEFAULT)
public class StockLevelRepositoryImpl extends RepositoryBaseImpl<StockLevel,Object> implements StockLevelRepository {

    @Override
    public List<StockLevelDTO> findAllFinalDestinations() {
        String hql = "SELECT DISTINCT sl.id, sl.organisationalUnit.id, sl.finalDestination.code FROM StockLevel sl";

        List<Object[]> results = getSessionCustom()
                .createQuery(hql, Object[].class)
                .getResultList();

        return results.stream()
                .map(row -> new StockLevelDTO(
                        (Long) row[0],
                        (Long) row[1],
                        (String) row[2]
                ))
                .collect(Collectors.toList());
    }

    @Override
    public void fastDeleteStockLevel(Long stockLevelId) {
        Session session =getSessionCustom();

        // 1. DELETE MAP VALUES FOR PRODUCTS
        session.createNativeQuery("DELETE FROM StockLevelStyleProduct_columnValueMap WHERE StockLevelStyleProduct_id IN (" +
                        "  SELECT p.id FROM StockLevelStyleProduct p " +
                        "  JOIN StockLevelRegion r ON p.stockLevelRegion_id = r.id " +
                        "  JOIN StockLevelStyle s ON r.stockLevelStyle_id = s.id " +
                        "  WHERE s.stock_level_id = :id)")
                .setParameter("id", stockLevelId).executeUpdate();

        // 2. DELETE THE PRODUCTS
        session.createQuery("DELETE FROM StockLevelStyleProduct p " +
                        "WHERE p.stockLevelRegion.id IN (" +
                        "  SELECT r.id FROM StockLevelRegion r " +
                        "  WHERE r.stockLevelStyle.stockLevel.id = :id)")
                .setParameter("id", stockLevelId).executeUpdate();

        // 3. DELETE MAP VALUES FOR REGIONS
        session.createNativeQuery("DELETE FROM StockLevelRegion_columnValueMap WHERE StockLevelRegion_id IN (" +
                        "  SELECT r.id FROM StockLevelRegion r " +
                        "  JOIN StockLevelStyle s ON r.stockLevelStyle_id = s.id " +
                        "  WHERE s.stock_level_id = :id)")
                .setParameter("id", stockLevelId).executeUpdate();

        // 4. DELETE THE REGIONS
        session.createQuery("DELETE FROM StockLevelRegion r " +
                        "WHERE r.stockLevelStyle.id IN (" +
                        "  SELECT s.id FROM StockLevelStyle s " +
                        "  WHERE s.stockLevel.id = :id)")
                .setParameter("id", stockLevelId).executeUpdate();

        // 5. DELETE MAP VALUES FOR STYLES
        session.createNativeQuery("DELETE FROM StockLevelStyle_columnValueMap WHERE StockLevelStyle_id IN (" +
                        "  SELECT s.id FROM StockLevelStyle s WHERE s.stock_level_id = :id)")
                .setParameter("id", stockLevelId).executeUpdate();

        // 6. DELETE THE STYLES
        session.createQuery("DELETE FROM StockLevelStyle s WHERE s.stockLevel.id = :id")
                .setParameter("id", stockLevelId).executeUpdate();

        // 7. FINALLY DELETE THE ROOT
        session.createNativeQuery("delete from stocklevel_regions where stocklevel_id = :id")
                .setParameter("id", stockLevelId).executeUpdate();
        session.createNativeQuery("delete from stocklevel_organisationalunits where stocklevel_id = :id")
                .setParameter("id", stockLevelId).executeUpdate();
        session.createQuery("DELETE FROM StockLevel s WHERE s.id = :id")
                .setParameter("id", stockLevelId).executeUpdate();

    }
}