Untitled

package com.byteplustech.arstatement.statement.repository;

import com.byteplustech.arstatement.statement.controller.v1.request.SearchStatementEmailLogRequest;
import com.byteplustech.arstatement.statement.controller.v1.request.SentStatementViaEmailReportRequest;
import com.byteplustech.arstatement.utils.DateUtils;
import lombok.extern.log4j.Log4j2;
import lombok.var;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.jooq.*;
import org.jooq.conf.ParamType;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.jooq.impl.DSL;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.sql.Connection;
import java.util.*;
import java.util.stream.Collectors;

import static org.jooq.impl.DSL.*;

@Log4j2
@Repository
public class CustomStatementEmailLogImplRepository implements CustomStatementEmailLogRepository {

    private final DataSource dataSource;

    public CustomStatementEmailLogImplRepository(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
    public List<Map<String, Object>> getSentStatementEmailReport(SentStatementViaEmailReportRequest request) {
        try (Connection connection = dataSource.getConnection()) {
            DSLContext dsl = DSL.using(connection);
            Table<Record> a = table("XCUST_STATEMENT_EMAIL_LOG_TBL a");
            Table<Record> b = table("XCUST_STATEMENT_HDR_TBL b");
            Table<Record> c = table("XCUST_CUSTOMER_MST_TBL c");

            List<Condition> conditions = new ArrayList<>();

            if (StringUtils.isNotBlank(request.getBusinessUnit())) {
                conditions.add(field("b.BU_ID").eq(request.getBusinessUnit()));
            }
            if (StringUtils.isNotBlank(request.getCustomerClass())) {
                conditions.add(field("b.CUSTOMER_CLASS").eq(request.getCustomerClass()));
            }
            if (StringUtils.isNotBlank(request.getCustomerSubClass())) {
                conditions.add(field("b.CUSTOMER_SUB_CLASS").eq(request.getCustomerSubClass()));
            }
            if (StringUtils.isNotBlank(request.getSiteNumber())) {
                conditions.add(field("b.SITE_NUMBER").eq(request.getSiteNumber()));
            }
            if (StringUtils.isNotBlank(request.getStatementNumber())) {
                conditions.add(field("b.STATEMENT_NUMBER").eq(request.getStatementNumber()));
            }
            if (Objects.nonNull(request.getStatementCreateDate())) {
                String statementCreateDate = DateUtils.instantToFormatddMMyyyyBangkok(request.getStatementCreateDate());
                conditions.add(field("TRUNC(b.STATEMENT_DATE)").eq(toDate(statementCreateDate, DateUtils.sdfddMMyyyy.toPattern())));
            }
            if (Objects.nonNull(request.getPaymentDueDate())) {
                String paymentDueDate = DateUtils.instantToFormatddMMyyyyBangkok(request.getPaymentDueDate());
                conditions.add(field("TRUNC(b.PAYMENT_DUE_DATE)").eq(toDate(paymentDueDate, DateUtils.sdfddMMyyyy.toPattern())));
            }
            if (CollectionUtils.isNotEmpty(request.getSendEmailStatuses())) {
                conditions.add(field("a.STATUS").in(request.getSendEmailStatuses()));
            }

            var query = dsl.select(
                            field("a.ID").as("LOG_ID"),
                            field("a.REQUEST_ID").as("REQUEST_ID"),
                            field("a.EMAIL").as("EMAIL"),
                            field("a.STATUS").as("STATUS"),
                            field("a.SEND_DATE").as("SEND_DATE"),
                            field("b.STATEMENT_NUMBER").as("STATEMENT_NUMBER"),
                            field("b.STATEMENT_DATE").as("STATEMENT_DATE"),
                            field("b.PAYMENT_DUE_DATE").as("PAYMENT_DUE_DATE"),
                            field("b.CUSTOMER_NAME").as("CUSTOMER_NAME"),
                            field("b.CUSTOMER_NUMBER").as("CUSTOMER_NUMBER"),
                            field("b.SITE_NUMBER").as("SITE_NUMBER"),
                            field("b.CREATE_BY").as("CREATE_BY"),
                            field("b.CUSTOMER_CLASS").as("CUSTOMER_CLASS"),
                            field("b.BRANCH").as("BRANCH"),
                            field("b.CREATE_DATE").as("CREATE_DATE"),
                            field("b.STATEMENT_ID").as("STATEMENT_ID"),
                            field("c.ATTRIBUTE7").as("D_CHANNEL"))
                    .from(a)
                    .leftJoin(b).on(field("b.REQUEST_ID").eq(field("a.REQUEST_ID"))
                            .and(field("b.STATEMENT_NUMBER").eq(field("a.STATEMENT_NUMBER"))))
                    .leftJoin(c)
                    .on(field("c.ACCOUNT_NUMBER").eq(field("b.CUSTOMER_NUMBER"))
                            .and(field("c.PARTY_SITE_NUMBER").eq(field("b.SITE_NUMBER"))))
                    .where(conditions)
                    .orderBy(field("a.ID").asc(), field("b.CREATE_DATE").asc(), field("b.STATEMENT_NUMBER").asc());

            return query.fetchMaps();
        } catch (Exception e) {
            log.error("getSentStatementEmailReport error : {}", e.getMessage(), e);
            throw new RuntimeException("Error while getSentStatementEmailReport", e);
        }
    }

    @Override
    public Page<Map<String, Object>> findStatementEmailLogWithStatementHeaderParams(SearchStatementEmailLogRequest request, Pageable pageable) {
        try (Connection connection = dataSource.getConnection()) {
            DSLContext dsl = DSL.using(connection);
            Table<Record> a = table("XCUST_STATEMENT_EMAIL_LOG_TBL a");
            Table<Record> b = table("XCUST_STATEMENT_HDR_TBL b");
            Table<Record> c = table("XCUST_CUSTOMER_MST_TBL c");

            List<Condition> conditions = new ArrayList<>();

            if (StringUtils.isNotBlank(request.getBusinessUnit())) {
                conditions.add(field("b.BU_ID").eq(request.getBusinessUnit()));
            }
            if (StringUtils.isNotBlank(request.getCustomerClass())) {
                conditions.add(field("b.CUSTOMER_CLASS").eq(request.getCustomerClass()));
            }
            if (StringUtils.isNotBlank(request.getCustomerSubClass())) {
                conditions.add(field("b.CUSTOMER_SUB_CLASS").eq(request.getCustomerSubClass()));
            }
            if (StringUtils.isNotBlank(request.getSiteNumber())) {
                conditions.add(field("b.SITE_NUMBER").eq(request.getSiteNumber()));
            }
            if (StringUtils.isNotBlank(request.getStatementNumber())) {
                conditions.add(field("b.STATEMENT_NUMBER").eq(request.getStatementNumber()));
            }
            if (Objects.nonNull(request.getStatementCreateDate())) {
                String statementCreateDate = DateUtils.instantToFormatddMMyyyyBangkok(request.getStatementCreateDate());
                conditions.add(field("TRUNC(b.STATEMENT_DATE)").eq(toDate(statementCreateDate, DateUtils.sdfddMMyyyy.toPattern())));
            }
            if (Objects.nonNull(request.getPaymentDueDate())) {
                String paymentDueDate = DateUtils.instantToFormatddMMyyyyBangkok(request.getPaymentDueDate());
                conditions.add(field("TRUNC(b.PAYMENT_DUE_DATE)").eq(toDate(paymentDueDate, DateUtils.sdfddMMyyyy.toPattern())));
            }
            if (CollectionUtils.isNotEmpty(request.getSendEmailStatuses())) {
                conditions.add(field("a.STATUS").in(request.getSendEmailStatuses()));
            }

            int totalCount = dsl.fetchCount(
                    dsl.select()
                            .from(a)
                            .leftJoin(b).on(field("b.REQUEST_ID").eq(field("a.REQUEST_ID"))
                                    .and(field("b.STATEMENT_NUMBER").eq(field("a.STATEMENT_NUMBER"))))
                            .leftJoin(c).on(field("c.ACCOUNT_NUMBER").eq(field("b.CUSTOMER_NUMBER"))
                                    .and(field("c.PARTY_SITE_NUMBER").eq(field("b.SITE_NUMBER"))))
                            .where(conditions)
            );

            String orderByClause = toOrderByClause(pageable.getSort());
            int offset = pageable.getPageNumber() * pageable.getPageSize();
            int pageSize = pageable.getPageSize();

            var query = dsl.select(
                            field("a.ID").as("LOG_ID"),
                            field("a.REQUEST_ID").as("REQUEST_ID"),
                            field("a.EMAIL").as("EMAIL"),
                            field("a.STATUS").as("STATUS"),
                            field("a.SEND_DATE").as("SEND_DATE"),
                            field("b.STATEMENT_NUMBER").as("STATEMENT_NUMBER"),
                            field("b.STATEMENT_DATE").as("STATEMENT_DATE"),
                            field("b.PAYMENT_DUE_DATE").as("PAYMENT_DUE_DATE"),
                            field("b.CUSTOMER_NAME").as("CUSTOMER_NAME"),
                            field("b.CUSTOMER_NUMBER").as("CUSTOMER_NUMBER"),
                            field("b.SITE_NUMBER").as("SITE_NUMBER"),
                            field("b.CREATE_BY").as("CREATE_BY"),
                            field("b.CUSTOMER_CLASS").as("CUSTOMER_CLASS"),
                            field("b.BRANCH").as("BRANCH"),
                            field("b.CREATE_DATE").as("CREATE_DATE"),
                            field("b.STATEMENT_ID").as("STATEMENT_ID"),
                            field("c.ATTRIBUTE7").as("D_CHANNEL"))
                    .from(a)
                    .leftJoin(b).on(field("b.REQUEST_ID").eq(field("a.REQUEST_ID"))
                            .and(field("b.STATEMENT_NUMBER").eq(field("a.STATEMENT_NUMBER"))))
                    .leftJoin(c)
                    .on(field("c.ACCOUNT_NUMBER").eq(field("b.CUSTOMER_NUMBER"))
                            .and(field("c.PARTY_SITE_NUMBER").eq(field("b.SITE_NUMBER"))))
                    .where(conditions)
                    .orderBy(field(orderByClause))
                    .offset(offset);

            String paginatedSql = query.getSQL(ParamType.INLINED) + " ROWS FETCH NEXT " + pageSize + " ROWS ONLY";

            List<Map<String, Object>> result = dsl.resultQuery(paginatedSql).fetchMaps();

            return new PageImpl<>(result, pageable, totalCount);
        } catch (Exception e) {
            log.error("findStatementEmailLogWithStatementHeaderParams error : {}", e.getMessage(), e);
            throw new RuntimeException("Error while findStatementEmailLogWithStatementHeaderParams", e);
        }
    }

    private String toOrderByClause(org.springframework.data.domain.Sort sort) {
        return sort.stream()
                .map(order -> {
                    String property = order.getProperty();
                    String direction = order.isAscending() ? Sort.Direction.ASC.name() : Sort.Direction.DESC.name();
                    return property + " " + direction;
                })
                .collect(Collectors.joining(", "));
    }
}