srt

PHOTO EMBED

Fri Apr 12 2024 05:37:53 GMT+0000 (Coordinated Universal Time)

Saved by @thanuj #sql 

package com.modeln.channelcollab.junit;

import com.modeln.channelnetwork.junit.graphql.AbstractChannelNetworkTest;
import com.modeln.channelnetwork.junit.graphql.GraphQLClient;
import com.modeln.channelnetwork.junit.graphql.SubmissionScheduleClient;
import io.restassured.path.json.JsonPath;
import io.restassured.response.Response;
import org.junit.Assert;
import org.junit.jupiter.api.*;
import org.junit.jupiter.api.extension.ExtendWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.junit.jupiter.SpringExtension;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.*;

@ExtendWith(SpringExtension.class)
@TestInstance(TestInstance.Lifecycle.PER_CLASS)
@TestMethodOrder(MethodOrderer.OrderAnnotation.class)
@Tag("graphql")
public class SubmissionResultsTest extends AbstractChannelNetworkTest
{
    public static final String SUBMISSION_SCHEDULE_FILE_NAME_PREFIX = "Submission_Schedule_";
    public static String newSubmissionSchedulefilename, invoiceDate;

    public static final Integer DEFAULT_OFFSET = 0, DEFAULT_LIMIT = 100;

    public static SubmissionScheduleClient adminClient;

    public static BigDecimal sid;

    @Autowired
    protected JdbcTemplate jdbcTemplate;

    public static final String SUBMISSION_SCHEDULE_QUERY_OUTPUT_JSON = "SELECT distinct JSON_OBJECT('sid' value ss.sid,'reporting_partner_name' value rp_ovw.entity_name,'name' value name,'periodRule' value period_rule,'dataType' value dt.type ,'expectedDay' value expected_day,'isInPeriodReporter' value case when is_in_period_reporter = '1' then 'true' Else 'false' end,'weekOfMonth' value week_of_month,'monthOfQuarter' value month_of_quarter,'startDate' value start_date,'endDate' value end_date) AS \" \" FROM submission_schedule ss join reporting_partner rp on rp.sid = ss.reporting_partner_sid left join data_type dt on dt.sid = ss.data_type_sid left join rp_csr_overlay_v rp_ovw on rp_ovw.ip_sid = rp.inow_profile_sid";

    public static String NO_DATA_REASON = "SYSTEM ISSUE", noDataReason;

    public static String GET_SID_TO_UPDATE = "select sid from submission_period";

    public static final String ACQUIRE_LOCK_GRAPHQL_MUTATION = "mutation acquireLock{" +
            "  acquireLock(objectType: SUBMISSION_PERIOD, sid: $sid) {" +
            "    userId" +
            "    serviceName" +
            "    sid" +
            "    expiration" +
            "  }" +
            "}";

    public static String MARK_NO_DATA_MUTATION = "mutation markNoData{" +
            "  markNoData(data: [{" +
            "        sid: $sid, " +
            "    noDataReason: \"$noDataReason\"" +
            "  }]) {" +
            "    sid "+
            "    status" +
            "    code" +
            "    message" +
            "  }" +
            "}";

    public static final String CHECK_NO_DATA_FOR_SID = "select no_data from submission_period where sid = :sid";

    public static final String CHECK_NO_DATA_REASON_FOR_SID = "select no_data_reason from submission_period where sid = :sid";

    @BeforeAll
    public void createClient() throws Exception
    {
        String endPoint = getEnvironment().getProperty(
                GraphQLClient.CHANNEL_NETWORK_GRAPHQL_URI);
        adminClient = new SubmissionScheduleClient(endPoint, generateUser1Token());
        List fromDb = jdbcTemplate.queryForList(SUBMISSION_SCHEDULE_QUERY_OUTPUT_JSON);
        if (fromDb.size() == 0)
        {
            uploadFileToCreateNewSubmissionSchedule();
        }
    }

    public void uploadFileToCreateNewSubmissionSchedule() throws Exception
    {
        newSubmissionSchedulefilename = getFilename(SUBMISSION_SCHEDULE_FILE_NAME_PREFIX, XLS_EXTENSION);
        LocalDate localDate = LocalDate.now();
        invoiceDate = DateTimeFormatter.ofPattern("MM/dd/yy").format(localDate.plusMonths(1).withDayOfMonth(1));
        String date2 = DateTimeFormatter.ofPattern("MM/dd/yy").format(localDate.withDayOfMonth(1));

        String[] rowData1 =
                { "CTHULHU", "Y", "\"ARROW PARTNER Transaction WEEKLY 20000000\"", "", "WEEKLY",
                        "Monday", "ARROW", "transaction", "No",
                        date2, "",
                        "ci_testuserint_email@cdmutlmail.aws.modeln.com", "Y", "", "Y",
                        "Y", "", "" };

        String[] rowData2 =
                { "CTHULHU", "Y", "\"CC SS BASE PARTNER Inventory MONTHLY 20000000\"", "", "MONTHLY",
                        "1", "BASE", "inventory", "Yes",
                        date2, "",
                        "ci_testuserint_email@cdmutlmail.aws.modeln.com", "Y", "", "Y",
                        "Y", "", "" };

        String[] rowData3 =
                { "CTHULHU", "Y", "\"AMSDIRSAP PARTNER Inventory DAILY 20000000\"", "", "QUARTERLY",
                        "1", "AMSDIRSAP", "inventory", "No",
                        date2, "",
                        "ci_testuserint_email@cdmutlmail.aws.modeln.com", "Y", "", "Y",
                        "Y", "", "1" };

        String[] rowData4 =
                { "CTHULHU", "Y", "\"BASE PARTNER 2 Transaction MONTHLY 20000000\"", "", "MONTHLY",
                        "1", "BASE2", "transaction", "No",
                        date2, "",
                        "ci_testuserint_email@cdmutlmail.aws.modeln.com", "Y", "", "Y",
                        "Y", "", "1" };

        String[][] fileData = new String[][]
                { CTH_SUBMISSION_SCHEDULE_FIELDS, rowData1, rowData2, rowData3, rowData4 };

        createXlsFile(getEnvironment(), fileData,
                newSubmissionSchedulefilename);
        invokeFileScanner(CLIENT_ID);
        waitForFileUploadSuccess(newSubmissionSchedulefilename);
        Thread.sleep(10000);
    }

    @Test
    public void submissionResultsTest() throws Exception
    {
        List<Map<String, Object>> sidList = jdbcTemplate.queryForList(GET_SID_TO_UPDATE);
        Map<String, Object> params = new HashMap<String, Object>();
        for(int i=0;i<sidList.size();i++)
        {
            sid = (BigDecimal) sidList.get(i).get("SID");
            params.put("sid", sid);
            String noDataForSid = getNamedParamJdbcTemplate()
                    .queryForObject(CHECK_NO_DATA_FOR_SID, params, String.class);
            Map<String, Object> mutationVariables = new HashMap<>();
            mutationVariables.put("sid", sid);
            mutationVariables.put("noDataReason", NO_DATA_REASON);
            String acquireLockMutation = ACQUIRE_LOCK_GRAPHQL_MUTATION.replace("$sid", sid.toString());

            Response response = adminClient.submissionScheduleQueryrunner(DEFAULT_LIMIT,
                    DEFAULT_OFFSET, null, null,
                    acquireLockMutation);
            noDataReason = getNamedParamJdbcTemplate()
                    .queryForObject(CHECK_NO_DATA_REASON_FOR_SID, params, String.class);

            if (Integer.parseInt(noDataForSid) == 0)
            {
                String markNoDataMutation = MARK_NO_DATA_MUTATION.replace("$sid", sid.toString()).replace("$noDataReason", NO_DATA_REASON);

            adminClient.submissionScheduleQueryrunner(DEFAULT_LIMIT,
                    DEFAULT_OFFSET, null, null,
                        markNoDataMutation);

            Thread.sleep(1000);
        }

        else {

            String markNoDataMutation = MARK_NO_DATA_MUTATION.replace("$sid", sid.toString()).replace("$noDataReason", NO_DATA_REASON);

            Response response1 = adminClient.submissionScheduleQueryrunner(DEFAULT_LIMIT,
                    DEFAULT_OFFSET, null, null,
                        markNoDataMutation);

            String message = JsonPath.with(response1.getBody().asString())
                    .get("data.markNoData[0].message");

            Assert.assertEquals(message,"Submission Period already has reported data, so No-Data-To-Report is not applicable.");
        }

            noDataForSid = getNamedParamJdbcTemplate()
                    .queryForObject(CHECK_NO_DATA_FOR_SID, params, String.class);
        Assert.assertEquals(Integer.parseInt(noDataForSid), 1);
        noDataReason = getNamedParamJdbcTemplate()
                    .queryForObject(CHECK_NO_DATA_REASON_FOR_SID, params, String.class);
        Assert.assertEquals(noDataReason, NO_DATA_REASON);
    }
    }
}
content_copyCOPY