APIPullNjbNscRepository.java

package com.eksad.masterdata.repository;

import com.eksad.ddms.common.util.StringUtil;
import com.eksad.masterdata.domain.APIPullNjbNscDetail;
import com.eksad.masterdata.domain.APIPullNjbNscHeader;
import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import jakarta.transaction.Transactional;
import org.hibernate.Session;
import org.hibernate.jdbc.Work;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Repository
@Transactional
public class APIPullNjbNscRepository {

    @PersistenceContext
    EntityManager entityManager;

    public List<APIPullNjbNscHeader> findAllNjbNscHeaderByRangeDate(String from, String to, String ahassID) {
        List<APIPullNjbNscHeader> list = new ArrayList<>();
        try {
            Session session = entityManager.unwrap(Session.class);
            session.doWork(new Work() {
                @Override
                public void execute(Connection cnctn) throws SQLException {
                    String query;
                    if (StringUtil.hasValue(ahassID)) {
                        query = "SELECT * FROM get_pull_njbnsc_by_range_createdat(?,?,?) order by woid desc";
                    } else {
                        query = "SELECT * FROM get_pull_njbnsc_by_range_createdat(?,?) order by woid desc";
                    }
                    PreparedStatement ps = cnctn.prepareStatement(query);
                    ps.setTimestamp(1, Timestamp.valueOf(from));
                    ps.setTimestamp(2, Timestamp.valueOf(to));
                    if (StringUtil.hasValue(ahassID)) ps.setString(3, ahassID);

                    ResultSet rs = ps.executeQuery();
                    while (rs.next()) {
                        APIPullNjbNscHeader data = new APIPullNjbNscHeader();
                        data.setNoWorkOrder(rs.getString("woid"));
                        data.setCreatedTime(rs.getTimestamp("createdtime"));
                        data.setHondaIdMekanik(rs.getString("hondaidmekanik"));
                        data.setHondaIdSA(rs.getString("hondaidsa"));
                        data.setModifiedTime(rs.getTimestamp("modifiedtime"));
                        data.setDealerId(rs.getString("dealerid"));
                        data.setTotalHargaJasa(rs.getDouble("totalhargajasa"));
                        data.setTotalHargaSparepart(rs.getDouble("totalhargasparepart"));
                        data.setPromoIdJasa(rs.getString("promoidjasa"));
                        data.setPromoIdPart(rs.getString("promoidpart"));
                        data.setStartWOTime(rs.getTimestamp("startwotime"));
                        data.setFinishFinalCheckTime(rs.getTimestamp("modifiedtimeorafin"));
                        data.setTotalHargaNJB(rs.getDouble("totalharganjb"));
                        data.setTotalHargaNSC(rs.getDouble("totalhargansc"));
                        data.setTanggalNJB(rs.getTimestamp("tanggalnjb"));
                        data.setTanggalNSC(rs.getTimestamp("tanggalnsc"));
                        data.setNoWorkOrderOrafin(rs.getString("noworkorderorafin"));
                        data.setNoNJB(rs.getString("nonjb"));
                        data.setNoNSC(rs.getString("nonsc"));
                        data.setTotalDP(rs.getDouble("totalbiayajadi"));
                        data.setDiscountCode(rs.getString("discountcode"));
                        list.add(data);
                    }
                }
            });
            return list;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public List<APIPullNjbNscDetail> findAllPullNjbNscDetailByRangeDate(String startDate, String endDate, String ahassID) {
        List<APIPullNjbNscDetail> list = new ArrayList<>();
        try {
            Session session = entityManager.unwrap(Session.class);
            session.doWork(new Work() {
                @Override
                public void execute(Connection cnctn) throws SQLException {
                    String query;
                    if (StringUtil.hasValue(ahassID)) {
                        query = "SELECT * FROM get_detail_pull_njbnsc_by_range_createdat_v2(?,?,?)";
                    } else {
                        query = "SELECT * FROM get_detail_pull_njbnsc_by_range_createdat(?,?)";
                    }
                    PreparedStatement ps = cnctn.prepareStatement(query);
                    ps.setTimestamp(1, Timestamp.valueOf(startDate));
                    ps.setTimestamp(2, Timestamp.valueOf(endDate));
                    if (StringUtil.hasValue(ahassID)) ps.setString(3, ahassID);

                    ResultSet rs = ps.executeQuery();
                    while (rs.next()) {
                        APIPullNjbNscDetail data = new APIPullNjbNscDetail();
                        data.setWoID(rs.getString("woid"));
                        data.setCreatedAt(rs.getTimestamp("createdat"));
                        data.setModifiedAt(rs.getTimestamp("modifiedat"));
                        data.setHarga(rs.getDouble("harga"));
                        data.setDiscount(rs.getDouble("discount"));
                        data.setTotal(rs.getDouble("total"));
                        data.setQty(rs.getDouble("qty"));
                        data.setCwsCode(rs.getString("cwscode"));
                        data.setPartID(rs.getString("partid"));
                        data.setTipe(rs.getString("tipe"));
                        data.setUangMuka(rs.getDouble("uangmuka"));
                        list.add(data);
                    }
                }
            });
            return list;
        } catch (Exception e) {
            e.printStackTrace();
            return list;
        }
    }

    public List<APIPullNjbNscHeader> findAllNjbNscHeaderByWoID(String woID) {
        List<APIPullNjbNscHeader> list = new ArrayList<>();
        try {
            Session session = entityManager.unwrap(Session.class);
            session.doWork(new Work() {
                @Override
                public void execute(Connection cnctn) throws SQLException {
                    String query;
                    String prefix = woID.split("-")[0];
                    if (prefix.equalsIgnoreCase("SO") || prefix.equalsIgnoreCase("SOP")) {
                        query = "SELECT * FROM get_pull_njbnsc_by_soid(?)";
                    } else {
                        query = "SELECT * FROM get_pull_njbnsc_by_woid(?)";
                    }
                    PreparedStatement ps = cnctn.prepareStatement(query);
                    ps.setString(1, woID);

                    ResultSet rs = ps.executeQuery();
                    while (rs.next()) {
                        APIPullNjbNscHeader data = new APIPullNjbNscHeader();
                        data.setNoWorkOrder(rs.getString("woid"));
                        data.setCreatedTime(rs.getTimestamp("createdtime"));
                        data.setHondaIdMekanik(rs.getString("hondaidmekanik"));
                        data.setHondaIdSA(rs.getString("hondaidsa"));
                        data.setModifiedTime(rs.getTimestamp("modifiedtime"));
                        data.setDealerId(rs.getString("dealerid"));
                        data.setTotalHargaJasa(rs.getDouble("totalhargajasa"));
                        data.setTotalHargaSparepart(rs.getDouble("totalhargasparepart"));
                        data.setPromoIdJasa(rs.getString("promoidjasa"));
                        data.setPromoIdPart(rs.getString("promoidpart"));
                        data.setStartWOTime(rs.getTimestamp("startwotime"));
                        data.setFinishFinalCheckTime(rs.getTimestamp("modifiedtimeorafin"));
                        data.setTotalHargaNJB(rs.getDouble("totalharganjb"));
                        data.setTotalHargaNSC(rs.getDouble("totalhargansc"));
                        data.setTanggalNJB(rs.getTimestamp("tanggalnjb"));
                        data.setTanggalNSC(rs.getTimestamp("tanggalnsc"));
                        data.setNoWorkOrderOrafin(rs.getString("noworkorderorafin"));
                        data.setNoNJB(rs.getString("nonjb"));
                        data.setNoNSC(rs.getString("nonsc"));
                        data.setTotalDP(rs.getDouble("totalbiayajadi"));
                        data.setDiscountCode(rs.getString("discountcode"));

                        list.add(data);
                    }
                }
            });
            return list;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public List<APIPullNjbNscDetail> findAllPullNjbNscDetailByWoID(String woID) {
        List<APIPullNjbNscDetail> list = new ArrayList<>();
        try {
            Session session = entityManager.unwrap(Session.class);
            session.doWork(new Work() {
                @Override
                public void execute(Connection cnctn) throws SQLException {
                    String query;
                    String prefix = woID.split("-")[0];
                    if (prefix.equalsIgnoreCase("SO") || prefix.equalsIgnoreCase("SOP")) {
                        query = "SELECT * FROM get_detail_pull_njbnsc_by_soid(?)";
                    } else {
                        query = "SELECT * FROM get_detail_pull_njbnsc_by_woid_v2(?)";
                    }
                    PreparedStatement ps = cnctn.prepareStatement(query);
                    ps.setString(1, woID);

                    ResultSet rs = ps.executeQuery();
                    while (rs.next()) {
                        APIPullNjbNscDetail data = new APIPullNjbNscDetail();
                        data.setWoID(rs.getString("woid"));
                        data.setCreatedAt(rs.getTimestamp("createdat"));
                        data.setModifiedAt(rs.getTimestamp("modifiedat"));
                        data.setHarga(rs.getDouble("harga"));
                        data.setDiscount(rs.getDouble("discount"));
                        data.setTotal(rs.getDouble("total"));
                        data.setQty(rs.getDouble("qty"));
                        data.setCwsCode(rs.getString("cwscode"));
                        data.setPartID(rs.getString("partid"));
                        data.setTipe(rs.getString("tipe"));
                        data.setUangMuka(rs.getDouble("uangmuka"));
                        list.add(data);
                    }
                }
            });
            return list;
        } catch (Exception e) {
            e.printStackTrace();
            return list;
        }
    }
}