/* ====================================================================
   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 org.apache.poi.hssf.HSSFTestDataSamples;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.FormulaParseException;
import org.apache.poi.ss.formula.eval.*;

import junit.framework.TestCase;
import org.apache.poi.ss.usermodel.*;

/**
 * Tests for {@link Subtotal}
 *
 * @author Paul Tomlin
 */
public final class TestSubtotal extends TestCase {
	private static final int FUNCTION_AVERAGE = 1;
	private static final int FUNCTION_COUNT = 2;
	private static final int FUNCTION_MAX = 4;
	private static final int FUNCTION_MIN = 5;
	private static final int FUNCTION_PRODUCT = 6;
	private static final int FUNCTION_STDEV = 7;
	private static final int FUNCTION_SUM = 9;

	private static final double[] TEST_VALUES0 = {
		1, 2,
		3, 4,
		5, 6,
		7, 8,
		9, 10
	};

	private static void confirmSubtotal(int function, double expected) {
		ValueEval[] values = new ValueEval[TEST_VALUES0.length];
		for (int i = 0; i < TEST_VALUES0.length; i++) {
			values[i] = new NumberEval(TEST_VALUES0[i]);
		}

		AreaEval arg1 = EvalFactory.createAreaEval("C1:D5", values);
		ValueEval args[] = { new NumberEval(function), arg1 };

		ValueEval result = new Subtotal().evaluate(args, 0, 0);

		assertEquals(NumberEval.class, result.getClass());
		assertEquals(expected, ((NumberEval) result).getNumberValue(), 0.0);
	}

	public void testBasics() {
		confirmSubtotal(FUNCTION_SUM, 55.0);
		confirmSubtotal(FUNCTION_AVERAGE, 5.5);
		confirmSubtotal(FUNCTION_COUNT, 10.0);
		confirmSubtotal(FUNCTION_MAX, 10.0);
		confirmSubtotal(FUNCTION_MIN, 1.0);
		confirmSubtotal(FUNCTION_PRODUCT, 3628800.0);
		confirmSubtotal(FUNCTION_STDEV, 3.0276503540974917);
	}

     public void testAvg(){
        Workbook wb = new HSSFWorkbook();

        FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();

        Sheet sh = wb.createSheet();
        Cell a1 = sh.createRow(1).createCell(1);
        a1.setCellValue(1);
        Cell a2 = sh.createRow(2).createCell(1);
        a2.setCellValue(3);
        Cell a3 = sh.createRow(3).createCell(1);
        a3.setCellFormula("SUBTOTAL(1,B2:B3)");
        Cell a4 = sh.createRow(4).createCell(1);
        a4.setCellValue(1);
        Cell a5 = sh.createRow(5).createCell(1);
        a5.setCellValue(7);
        Cell a6 = sh.createRow(6).createCell(1);
        a6.setCellFormula("SUBTOTAL(1,B2:B6)*2 + 2");
        Cell a7 = sh.createRow(7).createCell(1);
        a7.setCellFormula("SUBTOTAL(1,B2:B7)");
        Cell a8 = sh.createRow(8).createCell(1);
        a8.setCellFormula("SUBTOTAL(1,B2,B3,B4,B5,B6,B7,B8)");

        fe.evaluateAll();

        assertEquals(2.0, a3.getNumericCellValue());
        assertEquals(8.0, a6.getNumericCellValue());
        assertEquals(3.0, a7.getNumericCellValue());
        assertEquals(3.0, a8.getNumericCellValue());
    }

    public void testSum(){
        Workbook wb = new HSSFWorkbook();

        FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();

        Sheet sh = wb.createSheet();
        Cell a1 = sh.createRow(1).createCell(1);
        a1.setCellValue(1);
        Cell a2 = sh.createRow(2).createCell(1);
        a2.setCellValue(3);
        Cell a3 = sh.createRow(3).createCell(1);
        a3.setCellFormula("SUBTOTAL(9,B2:B3)");
        Cell a4 = sh.createRow(4).createCell(1);
        a4.setCellValue(1);
        Cell a5 = sh.createRow(5).createCell(1);
        a5.setCellValue(7);
        Cell a6 = sh.createRow(6).createCell(1);
        a6.setCellFormula("SUBTOTAL(9,B2:B6)*2 + 2");
        Cell a7 = sh.createRow(7).createCell(1);
        a7.setCellFormula("SUBTOTAL(9,B2:B7)");
        Cell a8 = sh.createRow(8).createCell(1);
        a8.setCellFormula("SUBTOTAL(9,B2,B3,B4,B5,B6,B7,B8)");

        fe.evaluateAll();

        assertEquals(4.0, a3.getNumericCellValue());
        assertEquals(26.0, a6.getNumericCellValue());
        assertEquals(12.0, a7.getNumericCellValue());
        assertEquals(12.0, a8.getNumericCellValue());
    }

    public void testCount(){

        Workbook wb = new HSSFWorkbook();

        FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();

        Sheet sh = wb.createSheet();
        Cell a1 = sh.createRow(1).createCell(1);
        a1.setCellValue(1);
        Cell a2 = sh.createRow(2).createCell(1);
        a2.setCellValue(3);
        Cell a3 = sh.createRow(3).createCell(1);
        a3.setCellFormula("SUBTOTAL(2,B2:B3)");
        Cell a4 = sh.createRow(4).createCell(1);
        a4.setCellValue("POI");                  // A4 is string and not counted
        /*Cell a5 =*/ sh.createRow(5).createCell(1); // A5 is blank and not counted

        Cell a6 = sh.createRow(6).createCell(1);
        a6.setCellFormula("SUBTOTAL(2,B2:B6)*2 + 2");
        Cell a7 = sh.createRow(7).createCell(1);
        a7.setCellFormula("SUBTOTAL(2,B2:B7)");
        Cell a8 = sh.createRow(8).createCell(1);
        a8.setCellFormula("SUBTOTAL(2,B2,B3,B4,B5,B6,B7,B8)");

        fe.evaluateAll();

        assertEquals(2.0, a3.getNumericCellValue());
        assertEquals(6.0, a6.getNumericCellValue());
        assertEquals(2.0, a7.getNumericCellValue());
        assertEquals(2.0, a8.getNumericCellValue());
    }

    public void testCounta(){

        Workbook wb = new HSSFWorkbook();

        FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();

        Sheet sh = wb.createSheet();
        Cell a1 = sh.createRow(1).createCell(1);
        a1.setCellValue(1);
        Cell a2 = sh.createRow(2).createCell(1);
        a2.setCellValue(3);
        Cell a3 = sh.createRow(3).createCell(1);
        a3.setCellFormula("SUBTOTAL(3,B2:B3)");
        Cell a4 = sh.createRow(4).createCell(1);
        a4.setCellValue("POI");                  // A4 is string and not counted
        /*Cell a5 =*/ sh.createRow(5).createCell(1); // A5 is blank and not counted

        Cell a6 = sh.createRow(6).createCell(1);
        a6.setCellFormula("SUBTOTAL(3,B2:B6)*2 + 2");
        Cell a7 = sh.createRow(7).createCell(1);
        a7.setCellFormula("SUBTOTAL(3,B2:B7)");
        Cell a8 = sh.createRow(8).createCell(1);
        a8.setCellFormula("SUBTOTAL(3,B2,B3,B4,B5,B6,B7,B8)");

        fe.evaluateAll();

        assertEquals(2.0, a3.getNumericCellValue());
        assertEquals(8.0, a6.getNumericCellValue());
        assertEquals(3.0, a7.getNumericCellValue());
        assertEquals(3.0, a8.getNumericCellValue());
    }

    public void testMax(){

        Workbook wb = new HSSFWorkbook();

        FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();

        Sheet sh = wb.createSheet();
        Cell a1 = sh.createRow(1).createCell(1);
        a1.setCellValue(1);
        Cell a2 = sh.createRow(2).createCell(1);
        a2.setCellValue(3);
        Cell a3 = sh.createRow(3).createCell(1);
        a3.setCellFormula("SUBTOTAL(4,B2:B3)");
        Cell a4 = sh.createRow(4).createCell(1);
        a4.setCellValue(1);
        Cell a5 = sh.createRow(5).createCell(1);
        a5.setCellValue(7);
        Cell a6 = sh.createRow(6).createCell(1);
        a6.setCellFormula("SUBTOTAL(4,B2:B6)*2 + 2");
        Cell a7 = sh.createRow(7).createCell(1);
        a7.setCellFormula("SUBTOTAL(4,B2:B7)");
        Cell a8 = sh.createRow(8).createCell(1);
        a8.setCellFormula("SUBTOTAL(4,B2,B3,B4,B5,B6,B7,B8)");

        fe.evaluateAll();

        assertEquals(3.0, a3.getNumericCellValue());
        assertEquals(16.0, a6.getNumericCellValue());
        assertEquals(7.0, a7.getNumericCellValue());
        assertEquals(7.0, a8.getNumericCellValue());
    }

    public void testMin(){

        Workbook wb = new HSSFWorkbook();

        FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();

        Sheet sh = wb.createSheet();
        Cell a1 = sh.createRow(1).createCell(1);
        a1.setCellValue(1);
        Cell a2 = sh.createRow(2).createCell(1);
        a2.setCellValue(3);
        Cell a3 = sh.createRow(3).createCell(1);
        a3.setCellFormula("SUBTOTAL(5,B2:B3)");
        Cell a4 = sh.createRow(4).createCell(1);
        a4.setCellValue(1);
        Cell a5 = sh.createRow(5).createCell(1);
        a5.setCellValue(7);
        Cell a6 = sh.createRow(6).createCell(1);
        a6.setCellFormula("SUBTOTAL(5,B2:B6)*2 + 2");
        Cell a7 = sh.createRow(7).createCell(1);
        a7.setCellFormula("SUBTOTAL(5,B2:B7)");
        Cell a8 = sh.createRow(8).createCell(1);
        a8.setCellFormula("SUBTOTAL(5,B2,B3,B4,B5,B6,B7,B8)");

        fe.evaluateAll();

        assertEquals(1.0, a3.getNumericCellValue());
        assertEquals(4.0, a6.getNumericCellValue());
        assertEquals(1.0, a7.getNumericCellValue());
        assertEquals(1.0, a8.getNumericCellValue());
    }

    public void testStdev(){

        Workbook wb = new HSSFWorkbook();

        FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();

        Sheet sh = wb.createSheet();
        Cell a1 = sh.createRow(1).createCell(1);
        a1.setCellValue(1);
        Cell a2 = sh.createRow(2).createCell(1);
        a2.setCellValue(3);
        Cell a3 = sh.createRow(3).createCell(1);
        a3.setCellFormula("SUBTOTAL(7,B2:B3)");
        Cell a4 = sh.createRow(4).createCell(1);
        a4.setCellValue(1);
        Cell a5 = sh.createRow(5).createCell(1);
        a5.setCellValue(7);
        Cell a6 = sh.createRow(6).createCell(1);
        a6.setCellFormula("SUBTOTAL(7,B2:B6)*2 + 2");
        Cell a7 = sh.createRow(7).createCell(1);
        a7.setCellFormula("SUBTOTAL(7,B2:B7)");
        Cell a8 = sh.createRow(8).createCell(1);
        a8.setCellFormula("SUBTOTAL(7,B2,B3,B4,B5,B6,B7,B8)");

        fe.evaluateAll();

        assertEquals(1.41421, a3.getNumericCellValue(), 0.0001);
        assertEquals(7.65685, a6.getNumericCellValue(), 0.0001);
        assertEquals(2.82842, a7.getNumericCellValue(), 0.0001);
        assertEquals(2.82842, a8.getNumericCellValue(), 0.0001);
    }

    public void test50209(){
        Workbook wb = new HSSFWorkbook();
        Sheet sh = wb.createSheet();
        Cell a1 = sh.createRow(1).createCell(1);
        a1.setCellValue(1);
        Cell a2 = sh.createRow(2).createCell(1);
        a2.setCellFormula("SUBTOTAL(9,B2)");
        Cell a3 = sh.createRow(3).createCell(1);
        a3.setCellFormula("SUBTOTAL(9,B2:B3)");

        FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
        fe.evaluateAll();
        assertEquals(1.0, a2.getNumericCellValue());
        assertEquals(1.0, a3.getNumericCellValue());
    }

    private static void confirmExpectedResult(FormulaEvaluator evaluator, String msg, Cell cell, double expected) {

        CellValue value = evaluator.evaluate(cell);
        if (value.getErrorValue() != 0)
            throw new RuntimeException(msg + ": " + value.formatAsString());
        assertEquals(msg, expected, value.getNumberValue());
    }

    public void testFunctionsFromTestSpreadsheet() {
        HSSFWorkbook workbook = HSSFTestDataSamples.openSampleWorkbook("SubtotalsNested.xls");
        HSSFSheet sheet = workbook.getSheetAt(0);
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

        assertEquals("B2", 10.0, sheet.getRow(1).getCell(1).getNumericCellValue());
        assertEquals("B3", 20.0, sheet.getRow(2).getCell(1).getNumericCellValue());

        //Test simple subtotal over one area
        Cell cellA3 = sheet.getRow(3).getCell(1);
        confirmExpectedResult(evaluator, "B4", cellA3, 30.0);

        //Test existence of the second area
        assertNotNull("C2 must not be null", sheet.getRow(1).getCell(2));
        assertEquals("C2", 7.0, sheet.getRow(1).getCell(2).getNumericCellValue());

        Cell cellC1 = sheet.getRow(1).getCell(3);
        Cell cellC2 = sheet.getRow(2).getCell(3);
        Cell cellC3 = sheet.getRow(3).getCell(3);

        //Test Functions SUM, COUNT and COUNTA calculation of SUBTOTAL
        //a) areas A and B are used
        //b) first 2 subtotals don't consider the value of nested subtotal in A3
        confirmExpectedResult(evaluator, "SUBTOTAL(SUM;B2:B8;C2:C8)", cellC1, 37.0);
        confirmExpectedResult(evaluator, "SUBTOTAL(COUNT;B2:B8,C2:C8)", cellC2, 3.0);
        confirmExpectedResult(evaluator, "SUBTOTAL(COUNTA;B2:B8,C2:C8)", cellC3, 5.0);
    }

    public void testUnimplemented(){
        Workbook wb = new HSSFWorkbook();

        FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();

        Sheet sh = wb.createSheet();
        Cell a3 = sh.createRow(3).createCell(1);
        a3.setCellFormula("SUBTOTAL(8,B2:B3)");

        try {
            fe.evaluateAll();
            fail("Should catch an NotImplementedFunctionException here, adjust these tests if it was actually implemented");
        } catch (NotImplementedException e) {
            // expected here
        }

        a3.setCellFormula("SUBTOTAL(10,B2:B3)");

        try {
            fe.evaluateAll();
            fail("Should catch an NotImplementedFunctionException here, adjust these tests if it was actually implemented");
        } catch (NotImplementedException e) {
            // expected here
        }

        a3.setCellFormula("SUBTOTAL(11,B2:B3)");

        try {
            fe.evaluateAll();
            fail("Should catch an NotImplementedFunctionException here, adjust these tests if it was actually implemented");
        } catch (NotImplementedException e) {
            // expected here
        }

        a3.setCellFormula("SUBTOTAL(107,B2:B3)");

        try {
            fe.evaluateAll();
            fail("Should catch an NotImplementedFunctionException here, adjust these tests if it was actually implemented");
        } catch (NotImplementedException e) {
            // expected here
        }

        a3.setCellFormula("SUBTOTAL(0,B2:B3)");
        fe.evaluateAll();
        assertEquals(FormulaError.VALUE.getCode(), a3.getErrorCellValue());

        try {
            a3.setCellFormula("SUBTOTAL(9)");
            fail("Should catch an exception here");
        } catch (FormulaParseException e) {
            // expected here
        }

        try {
            a3.setCellFormula("SUBTOTAL()");
            fail("Should catch an exception here");
        } catch (FormulaParseException e) {
            // expected here
        }

        Subtotal subtotal = new Subtotal();
        assertEquals(ErrorEval.VALUE_INVALID, subtotal.evaluate(new ValueEval[] {}, 0, 0));
    }
}