package com.b3.rbl.compensation.service; import com.b3.rbl.compensation.model.jbpm.*; import com.b3.rbl.compensation.util.ObjectUtil; import com.fasterxml.jackson.core.JsonProcessingException; import com.fasterxml.jackson.databind.ObjectMapper; import com.fasterxml.jackson.databind.ObjectWriter; import lombok.extern.log4j.Log4j2; import org.apache.commons.math3.analysis.function.Min; import org.dmg.pmml.time_series.MA; import org.drools.core.command.runtime.BatchExecutionCommandImpl; import org.hibernate.transform.Transformers; import org.kie.api.KieServices; import org.kie.api.command.BatchExecutionCommand; import org.kie.api.command.Command; import org.kie.api.command.KieCommands; import org.kie.api.runtime.ExecutionResults; import org.kie.server.api.marshalling.MarshallingFormat; import org.kie.server.api.model.KieContainerResource; import org.kie.server.api.model.KieContainerResourceList; import org.kie.server.api.model.KieServiceResponse; import org.kie.server.api.model.ServiceResponse; import org.kie.server.api.model.definition.ProcessDefinition; import org.kie.server.api.model.instance.ProcessInstance; import org.kie.server.api.model.instance.ProcessInstanceCustomVars; import org.kie.server.api.model.instance.ProcessInstanceList; import org.kie.server.api.model.instance.VariableInstance; import org.kie.server.client.*; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Value; import org.springframework.data.jpa.repository.Modifying; import org.springframework.stereotype.Service; import java.lang.reflect.Array; import java.math.BigDecimal; import java.math.RoundingMode; import java.sql.Date; import java.text.DecimalFormat; import java.text.ParseException; import java.text.SimpleDateFormat; import java.time.LocalDate; import java.time.format.DateTimeFormatter; import java.time.format.DateTimeParseException; import java.util.*; import javax.persistence.EntityManager; import javax.persistence.PersistenceContext; import javax.persistence.Query; import javax.sql.DataSource; import javax.transaction.Transactional; import com.b3.rbl.compensation.service.JbpmService; @Log4j2 @Service public class MonthlyBonusService { @Autowired JbpmService jbpmService; @Value("${kie.server.container_id}") private String CONTAINERID; @Value("${kie.server.process_idMonthly}") private String PROCESSID; @Value("${kie.server.url}") private String URL; @Value("${kie.server.username}") private String USERNAME; @Value("${kie.server.password}") private String PASSWORD; private MarshallingFormat FORMAT = MarshallingFormat.JSON; private KieServicesConfiguration conf; private KieServicesClient kieServicesClient; @Autowired private EntityManager apiDatasource; private List<Map<String, Object>> mapDataToDB = new ArrayList<>(Arrays.asList(new HashMap<String, Object>() { { put("MonthlyBonus", new HashMap<String, String>() { { put("agentCode", "agent_code"); put("agentTitle", "agent_title"); put("agentName", "agent_Name"); put("agentPosition", "agent_level_code_detail"); put("benefitCode", "benefit_code"); put("persistencyRate", "persistency_rate"); put("persistencyController", "persistency_controller"); put("fycAmount", "fyc"); put("fycProduction", "fyc_production"); put("bonusRate", "benefit_rate"); put("bonus", "benefit_amount"); put("incomeTax", "income_tax"); put("deductTax", "deduct_tax"); put("calculationDate", "create_date"); put("policyNo", "policy_no"); put("description", "description"); put("policyIssuesDate", "policy_issues_date"); put("policyProductType", "policy_product_type"); put("policyStatusCode", "policy_status_code"); put("policyStatusName", "policy_status_name"); put("premiumPeriod", "premium_period"); put("premiumYear", "premium_year"); put("transactionDate", "transaction_date"); put("transactionType", "transaction_type"); put("detailpolicyNo", "policy_no"); put("detailFycAmount", "fyc"); put("detailFycProduction", "fyc_production"); } }); } })); public void initialize() { conf = KieServicesFactory.newRestConfiguration(URL, USERNAME, PASSWORD, 60000); conf.setMarshallingFormat(FORMAT); kieServicesClient = KieServicesFactory.newKieServicesClient(conf); log.info(kieServicesClient); } public void executeBusinessProcess(AgentDetailData agentDetail) throws JsonProcessingException { // System.out.println("== Start Business Processes =="); log.info("== Start Business Processes =="); log.info("LOGGG TYPEE:::", PROCESSID); ProcessServicesClient processServicesClient = kieServicesClient.getServicesClient(ProcessServicesClient.class); Map<String, Object> data = new HashMap<String, Object>(); Class<?> cls = AgentDetailData.class; String classPackage = cls.getPackage().getName().substring(0, 24).concat(cls.getSimpleName()); // System.out.println("Class : " + classPackage); log.info("Class : " + classPackage); Map<String, Object> stringMap = new HashMap<String, Object>(); stringMap.put(classPackage, agentDetail); data.put("data", stringMap); Long process = processServicesClient.startProcess(CONTAINERID, PROCESSID, data); // System.out.println("== End Business Processes =="); log.info("== End Business Processes =="); } @Transactional public void getDataMonthlyBonus(Date startdate, Date enddate) { String sql = "SELECT * from compensation.f_rbl_monthly_bonus_detail(:start_date, :end_date) where policy_status_name = 'Active Member'"; String benefitType = "MonthlyBonus"; List<Map<String, Object>> data = new ArrayList<>(); List<Map<String, Object>> query = this.apiDatasource.createNativeQuery(sql) .setParameter("start_date", startdate) .setParameter("end_date", enddate) .unwrap(org.hibernate.query.Query.class) .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList(); data = ObjectUtil.columnToCamelCase(query); log.info("res {}", data.size()); log.info("resData {}", data); Map<String, List<Map<String, Object>>> groupedData = new HashMap<>(); for (Map<String, Object> recordData : data) { String agentCode = (String) recordData.get("agentCode"); if (!groupedData.containsKey(agentCode)) { groupedData.put(agentCode, new ArrayList<>()); } groupedData.get(agentCode).add(recordData); } for (Map.Entry<String, List<Map<String, Object>>> entry : groupedData.entrySet()) { try { String agentCode = entry.getKey(); List<Map<String, Object>> agentRecords = entry.getValue(); log.info("Agent Code After group::: {}", agentCode); AgentDetailData agentDetailData = setupMonthlyPayload(agentRecords, benefitType); log.info("Size Aggent Data Log::{}",agentDetailData.getAgents().size()); log.info("Aggent Data Log::{}",agentDetailData.getAgents()); executeBusinessProcess(agentDetailData); } catch (JsonProcessingException e) { e.printStackTrace(); } } } public AgentDetailData setupMonthlyPayload(List<Map<String, Object>> data, String benefitType) { AgentDetailData agentData = new AgentDetailData(); List<AgentResult> agentResultList = new ArrayList<>(); List<AgentDetail> agentDetailList = new ArrayList<>(); int count = 0; for (Map<String, Object> map : data) { // log.info("{} map {} {}",count, map.entrySet(), map.get("paidDate").toString()); AgentResult agentResult = new AgentResult(); AgentDetail agentDetail = new AgentDetail(); if (benefitType.equals("MonthlyBonus")) { agentResult.setStartDate((Date) map.get("startDate")); agentResult.setEndDate((Date) map.get("endDate")); agentResult.setAgentCode((String) map.get("agentCode")); agentResult.setAgentName((String) map.get("agentName")); agentResult.setAgentPosition((String) map.get("agentLevelCodeDetail")); agentResult.setPolicyNo((String) map.get("policyNo")); agentResult.setFycAmount(Double.valueOf(String.valueOf(map.get("fyc")))); agentResult.setFycProduction(Double.valueOf(String.valueOf(map.get("fycProduction")))); // agentResult.setTransactionEffectiveDate((Date) map.get("transactionEffectiveDate")); // agentResult.setPolicyStatusCode((String) map.get("policyStatusCode")); // agentResult.setPolicyStatusName((String) map.get("policyStatusName")); agentResult.setAgentTitle((String) map.get("agentTitle")); agentResult.setBenefitCode((String) map.get("benefitCode")); //agentResult.setPercistencyController(Double.valueOf(String.valueOf(map.get("percistencyController")))); //agentResult.setPersistencyRate(Double.valueOf(String.valueOf(map.get("persistencyRate")))); agentResult.setBenefitType(benefitType); //FOR AGENT DETAIL agentDetail.setAgentCode((String) map.get("agentCode")); //agentResult.setAgentName((String) map.get("agentName")); //agentDetail.setAgentPosition((String) map.get("agentLevelCodeDetail")); agentDetail.setPolicyNo((String) map.get("policyNo")); agentDetail.setFycAmount(Double.valueOf(String.valueOf(map.get("fyc")))); agentDetail.setFycProduction(Double.valueOf(String.valueOf(map.get("fycProduction")))); agentDetail.setPolicyIssuesDate((Date) map.get("policyIssueDate")); agentDetail.setTransactionDate((Date) map.get("transactionDate")); agentDetail.setTransactionType((String) map.get("transactionType")); agentDetail.setPremiumPeriod((Integer.valueOf(String.valueOf(map.get("premiumPeriod"))))); agentDetail.setPremiumYear((String) map.get("premiumYear")); agentDetail.setPolicyProductType((String) map.get("policyProductType")); agentDetail.setPolicyStatusCode((String) map.get("policyStatusCode")); agentDetail.setPolicyStatusCode((String) map.get("policyStatusCode")); agentDetail.setPolicyStatusName((String) map.get("policyStatusName")); } log.info("obj : {}", agentResult.getBenefitType()); agentDetailList.add(agentDetail); agentResult.setAgentDetail(agentDetailList); agentResultList.add(agentResult); count++; } agentData.setAgents(agentResultList); //agentData.setAgentDetails(agentDetailList); // log.info("agents {}", agentResultList.get(0).getBenefitType()); return agentData; } @Modifying @Transactional public void setupMonthlyData(AgentDetailData data) { log.info("data : {}", data.getAgents().get(0).getBenefitType()); log.info("Agent Size:: {}", data.getAgents().size()); String sql = "INSERT INTO compensation."; Integer count = 0; for (AgentResult agent : data.getAgents()) { if (agent.getBenefitType().equals("MonthlyBonus")) { Map<String, String> mp = (Map<String, String>) mapDataToDB.get(0).get("MonthlyBonus"); if (count == 0) { sql += "rbl_benefit_result (" + mp.get("agentCode") + "," + mp.get("agentTitle") + "," + mp.get("agentName") + "," + mp.get("agentPosition") + "," + mp.get("benefitCode") + "," + mp.get("persistencyRate") + "," + mp.get("persistencyController") + "," + mp.get("fycAmount") + "," + mp.get("fycProduction") + "," + mp.get("bonusRate") + "," + mp.get("bonus") + "," + mp.get("incomeTax") + "," + mp.get("deductTax") + "," + mp.get("calculationDate") + "," + mp.get("description") + ") VALUES "; sql += "(" + concatSingleQuote(agent.getAgentCode().toString()) + "," + concatSingleQuote(agent.getAgentTitle().toString()) + "," + concatSingleQuote(agent.getAgentName().toString()) + "," + concatSingleQuote(agent.getAgentPosition().toString()) + "," + concatSingleQuote(agent.getBenefitCode().toString()) + "," + agent.getPersistencyRate() + "," + agent.getPercistencyController() + "," + jbpmService.roundingDecimalScale(agent.getFycAmount()) + "," + jbpmService.roundingDecimalScale(agent.getFycProduction()) + "," + agent.getBonusRate() + "," + jbpmService.roundingDecimalScale(agent.getBonus()) + "," + jbpmService.roundingDecimalScale(agent.getIncomeTax()) + "," + jbpmService.roundingDecimalScale(agent.getDeductTax()) + "," + concatSingleQuote(java.time.LocalDate.now().toString()) + "," + concatSingleQuote(agent.getDescription()) // + concatSingleQuote(agent.getStartDate().toString()) + "," // + concatSingleQuote(agent.getEndDate().toString()) + "," + "),"; } else{ sql += "(" + agent.getAgentCode() + "," + agent.getAgentTitle() + "," + agent.getAgentName() + "," + agent.getAgentPosition() + "," + agent.getBenefitCode() + "," + agent.getPersistencyRate() + "," + agent.getPercistencyController() + "," + jbpmService.roundingDecimalScale(agent.getFycAmount()) + "," + jbpmService.roundingDecimalScale(agent.getFycProduction()) + "," + agent.getBonusRate() + "," + jbpmService.roundingDecimalScale(agent.getBonus()) + "," + jbpmService.roundingDecimalScale(agent.getIncomeTax()) + "," + jbpmService.roundingDecimalScale(agent.getDeductTax()) + "," + concatSingleQuote(java.time.LocalDate.now().toString()) + "," + concatSingleQuote(agent.getDescription()) + "),"; } } count++; } sql = sql.substring(0,sql.length()-1); log.info("sql {}",sql ); saveData(sql); // selectLastIdResult(); String sqlLastId = "SELECT MAX(Id) FROM compensation.rbl_benefit_result where benefit_code = 'FBM'"; log.info("SQL lastId::", sqlLastId ); List<Map<String, Object>> selectLastId = new ArrayList(); List<Map<String, Object>> queryResult = this.apiDatasource.createNativeQuery(sqlLastId) .unwrap(org.hibernate.query.Query.class) .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).getResultList(); selectLastId = ObjectUtil.columnToCamelCase(queryResult); setupMonthlyDetailData(data, selectLastId.get(0).get("max").toString()); log.info("lastd {}", selectLastId.get(0).get("max")); } @Modifying @Transactional public void setupMonthlyDetailData(AgentDetailData data, String benefitId) { String sql = "INSERT INTO compensation."; Integer count = 0; for (AgentResult agent : data.getAgents()) { for (AgentDetail detail : agent.getAgentDetail()) { if (agent.getBenefitType().equals("MonthlyBonus")) { Map<String, String> mp = (Map<String, String>) mapDataToDB.get(0).get("MonthlyBonus"); if (count == 0) { sql += "rbl_benefit_detail (" + mp.get("policyIssuesDate") + "," + mp.get("policyProductType") + "," + mp.get("policyStatusCode") + "," + mp.get("policyStatusName") + "," + mp.get("premiumPeriod") + "," + mp.get("premiumYear") + "," + mp.get("transactionDate") + "," + mp.get("transactionType") + "," + mp.get("agentCode") + "," + mp.get("detailpolicyNo") + "," + mp.get("detailFycAmount") + "," + mp.get("detailFycProduction") + "," + "benefit_result_id" + ") VALUES "; sql += "(" + concatSingleQuote(detail.getPolicyIssuesDate().toString()) + "," + concatSingleQuote(detail.getPolicyProductType()) + "," + concatSingleQuote(detail.getPolicyStatusCode()) + "," + concatSingleQuote(detail.getPolicyStatusName()) + "," + detail.getPremiumPeriod() + "," + concatSingleQuote(detail.getPremiumYear()) + "," + concatSingleQuote(detail.getTransactionDate().toString()) + "," + concatSingleQuote(detail.getTransactionType()) + "," + concatSingleQuote(detail.getAgentCode().toString()) + "," + concatSingleQuote(detail.getPolicyNo()) + "," + jbpmService.roundingDecimalScale(detail.getFycAmount()) + "," + jbpmService.roundingDecimalScale(detail.getFycProduction()) + "," + concatSingleQuote(benefitId) + "),"; } else{ sql += "(" + concatSingleQuote(detail.getPolicyIssuesDate().toString()) + "," + concatSingleQuote(detail.getPolicyProductType()) + "," + concatSingleQuote(detail.getPolicyStatusCode()) + "," + concatSingleQuote(detail.getPolicyStatusName()) + "," + detail.getPremiumPeriod() + "," + concatSingleQuote(detail.getPremiumYear()) + "," + concatSingleQuote(detail.getTransactionDate().toString()) + "," + concatSingleQuote(detail.getTransactionType()) + "," + concatSingleQuote(detail.getAgentCode().toString()) + "," + concatSingleQuote(detail.getPolicyNo()) + "," + jbpmService.roundingDecimalScale(detail.getFycAmount()) + "," + jbpmService.roundingDecimalScale(detail.getFycProduction()) + "," + concatSingleQuote(benefitId) + "),"; } } count++; } } sql = sql.substring(0, sql.length() - 1); log.info("sql for detail {}", sql); saveData(sql); } @Modifying @Transactional public void saveData(String sql){ try { this.apiDatasource.createNativeQuery(sql).executeUpdate(); } catch (Exception e) { log.info("err {} {}",e.getMessage(),e); // TODO: handle exception } } public String convertDateFormat(String strDate){ // Format of the original date string SimpleDateFormat originalFormat = new SimpleDateFormat("dd/MM/yyyy"); // Desired format SimpleDateFormat targetFormat = new SimpleDateFormat("yyyy-MM-dd"); String formattedDate = ""; try { // Parse the original date string to a Date object java.util.Date date = originalFormat.parse(strDate); // Format the Date object to the desired format formattedDate = targetFormat.format(date); // Print the formatted date log.info(formattedDate); } catch (ParseException e) { e.printStackTrace(); } return "'"+formattedDate+"'"; } public String concatSingleQuote(String str){ return "'"+str+"'"; } }