poi/src/test/java/org/apache/poi/ss/formula/functions/TestDays360.java

/* ====================================================================
   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to You under the Apache License, Version 2.0
   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
==================================================================== */

package org.apache.poi.ss.formula.functions;

import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertTrue;

import java.util.Calendar;
import java.util.Date;
import java.util.Locale;

import org.apache.poi.ss.formula.eval.BoolEval;
import org.apache.poi.ss.formula.eval.NumberEval;
import org.apache.poi.ss.formula.eval.ValueEval;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.util.LocaleUtil;
import org.junit.jupiter.api.Test;

final class TestDays360 {

    /**
     * @param month 1-based
     */
    private static Date makeDate(int year, int month, int day) {
        Calendar cal = LocaleUtil.getLocaleCalendar(year, month-1, day);
        return cal.getTime();
    }

    private static Date decrementDay(Date d) {
        Calendar c = LocaleUtil.getLocaleCalendar();
        c.setTime(d);
        c.add(Calendar.DAY_OF_MONTH, -1);
        return c.getTime();
    }

    @Test
    void testBasic() {
        confirm(120, 2009, 1, 15, 2009, 5, 15);
        confirm(158, 2009, 1, 26, 2009, 7, 4);

        // same results in leap years
        confirm(120, 2008, 1, 15, 2008, 5, 15);
        confirm(158, 2008, 1, 26, 2008, 7, 4);

        // longer time spans
        confirm(562, 2008, 8, 11, 2010, 3, 3);
        confirm(916, 2007, 2, 23, 2009, 9, 9);

        // other tests
        confirm(1, makeDate(1993, 2, 28), makeDate(1993, 3, 1), false);
        confirm(1, makeDate(1996, 2, 29), makeDate(1996, 3, 1), false);
        confirm(-2, makeDate(1993, 2, 28), makeDate(1993, 2, 28), false);
        confirm(3, makeDate(1993, 2, 28), makeDate(1993, 3, 1), true);
        confirm(2, makeDate(1996, 2, 29), makeDate(1996, 3, 1), true);

        // from https://support.office.com/en-us/article/DAYS360-function-B9A509FD-49EF-407E-94DF-0CBDA5718C2A
        confirm(1, makeDate(2011, 1, 30), makeDate(2011, 2, 1), false);
        confirm(360, makeDate(2011, 1, 1), makeDate(2011, 12, 31), false);
        confirm(30, makeDate(2011, 1, 1), makeDate(2011, 2, 1), false);
    }

    private static void confirm(int expResult, int y1, int m1, int d1, int y2, int m2, int d2) {
        confirm(expResult, makeDate(y1, m1, d1), makeDate(y2, m2, d2), false);
        confirm(-expResult, makeDate(y2, m2, d2), makeDate(y1, m1, d1), false);
    }

    /**
     * The {@code method} parameter only makes a difference when the second parameter
     * is the last day of the month that does <em>not</em> have 30 days.
     */
    @Test
    void testMonthBoundaries() {
        // jan
        confirmMonthBoundary(false, 2001, 1, 0, 0, 2, 3, 4);
        confirmMonthBoundary(true,  2001, 1, 0, 0, 1, 2, 3);
        // feb
        confirmMonthBoundary(false, 2001, 2,-2, 1, 2, 3, 4);
        confirmMonthBoundary(true,  2001, 2, 0, 1, 2, 3, 4);
        // mar
        confirmMonthBoundary(false, 2001, 3, 0, 0, 2, 3, 4);
        confirmMonthBoundary(true,  2001, 3, 0, 0, 1, 2, 3);
        // apr
        confirmMonthBoundary(false, 2001, 4, 0, 1, 2, 3, 4);
        confirmMonthBoundary(true,  2001, 4, 0, 1, 2, 3, 4);
        // may
        confirmMonthBoundary(false, 2001, 5, 0, 0, 2, 3, 4);
        confirmMonthBoundary(true,  2001, 5, 0, 0, 1, 2, 3);
        // jun
        confirmMonthBoundary(false, 2001, 6, 0, 1, 2, 3, 4);
        confirmMonthBoundary(true,  2001, 6, 0, 1, 2, 3, 4);
        // leap year
        confirmMonthBoundary(false, 2012, 2, -1, 1, 2, 3, 4);
        confirmMonthBoundary(true,  2012, 2, 0, 1, 2, 3, 4);

        // bug 60029
        Date start = makeDate(2018, 2, 28);
        Date end = makeDate(2018, 3, 31);
        confirm(30, start, end, false);

        // examples from https://support.office.com/en-us/article/DAYS360-function-B9A509FD-49EF-407E-94DF-0CBDA5718C2A
        start = makeDate(2011, 1, 30);
        end = makeDate(2011, 2, 1);
        confirm(1, start, end, false);

        start = makeDate(2011, 1, 1);
        end = makeDate(2011, 12, 31);
        confirm(360, start, end, false);

        start = makeDate(2011, 1, 1);
        end = makeDate(2011, 2, 1);
        confirm(30, start, end, false);
    }


    /**
     * @param monthNo 1-based
     */
    private static void confirmMonthBoundary(boolean method, int year, int monthNo, int...diffs) {
        Date firstDayOfNextMonth = makeDate(year, monthNo+1, 1);
        Date secondArg = decrementDay(firstDayOfNextMonth);
        Date firstArg = secondArg;

        for (int expResult : diffs) {
            confirm(expResult, firstArg, secondArg, method);
            firstArg = decrementDay(firstArg);
        }

    }
    private static void confirm(int expResult, Date firstArg, Date secondArg, boolean method) {
        ValueEval ve;
        if (method) {
            ve = invokeDays360(convert(firstArg), convert(secondArg), BoolEval.TRUE);
        } else {
            ve = invokeDays360(convert(firstArg), convert(secondArg));
        }
        assertTrue(ve instanceof NumberEval, "wrong return type (" + ve.getClass().getName() + ")");

        NumberEval numberEval = (NumberEval) ve;
        String err = String.format(Locale.ROOT, "days360(%tF,%tF,%b) wrong result", firstArg, secondArg, method);
        assertEquals(expResult, numberEval.getNumberValue(), 0, err);
    }

    private static ValueEval invokeDays360(ValueEval...args) {
        return new Days360().evaluate(args, -1, -1);
    }

    private static NumberEval convert(Date d) {
        return new NumberEval(DateUtil.getExcelDate(d));
    }
}