/* ====================================================================
   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.usermodel;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNull;
import static org.junit.Assert.assertSame;
import static org.junit.Assert.fail;

import java.io.IOException;

import org.apache.poi.ss.ITestDataProvider;
import org.junit.Test;

/**
 * Common superclass for testing implementation of {@link FormulaEvaluator}
 */
public abstract class BaseTestFormulaEvaluator {

	protected final ITestDataProvider _testDataProvider;

	/**
	 * @param testDataProvider an object that provides test data in  /  specific way
	 */
	protected BaseTestFormulaEvaluator(ITestDataProvider testDataProvider) {
		_testDataProvider = testDataProvider;
	}

	@Test
    public void testSimpleArithmetic() throws IOException {
        Workbook wb = _testDataProvider.createWorkbook();
        Sheet s = wb.createSheet();
        Row r = s.createRow(0);

        Cell c1 = r.createCell(0);
        c1.setCellFormula("1+5");
        assertEquals(0.0, c1.getNumericCellValue(), 0.0);

        Cell c2 = r.createCell(1);
        c2.setCellFormula("10/2");
        assertEquals(0.0, c2.getNumericCellValue(), 0.0);

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

        fe.evaluateFormulaCellEnum(c1);
        fe.evaluateFormulaCellEnum(c2);

        assertEquals(6.0, c1.getNumericCellValue(), 0.0001);
        assertEquals(5.0, c2.getNumericCellValue(), 0.0001);
        
        wb.close();
    }

	@Test
	public void testSumCount() throws IOException {
        Workbook wb = _testDataProvider.createWorkbook();
        Sheet s = wb.createSheet();
        Row r = s.createRow(0);
        r.createCell(0).setCellValue(2.5);
        r.createCell(1).setCellValue(1.1);
        r.createCell(2).setCellValue(3.2);
        r.createCell(4).setCellValue(10.7);

        r = s.createRow(1);

        Cell c1 = r.createCell(0);
        c1.setCellFormula("SUM(A1:B1)");
        assertEquals(0.0, c1.getNumericCellValue(), 0.0);

        Cell c2 = r.createCell(1);
        c2.setCellFormula("SUM(A1:E1)");
        assertEquals(0.0, c2.getNumericCellValue(), 0.0);

        Cell c3 = r.createCell(2);
        c3.setCellFormula("COUNT(A1:A1)");
        assertEquals(0.0, c3.getNumericCellValue(), 0.0);

        Cell c4 = r.createCell(3);
        c4.setCellFormula("COUNTA(A1:E1)");
        assertEquals(0.0, c4.getNumericCellValue(), 0.0);


        // Evaluate and test
        FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();

        fe.evaluateFormulaCellEnum(c1);
        fe.evaluateFormulaCellEnum(c2);
        fe.evaluateFormulaCellEnum(c3);
        fe.evaluateFormulaCellEnum(c4);

        assertEquals(3.6, c1.getNumericCellValue(), 0.0001);
        assertEquals(17.5, c2.getNumericCellValue(), 0.0001);
        assertEquals(1, c3.getNumericCellValue(), 0.0001);
        assertEquals(4, c4.getNumericCellValue(), 0.0001);
        
        wb.close();
    }

	public void baseTestSharedFormulas(String sampleFile) throws IOException {
        Workbook wb = _testDataProvider.openSampleWorkbook(sampleFile);

        Sheet sheet = wb.getSheetAt(0);

        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        Cell cell;

        cell = sheet.getRow(1).getCell(0);
        assertEquals("B2", cell.getCellFormula());
        assertEquals("ProductionOrderConfirmation", evaluator.evaluate(cell).getStringValue());

        cell = sheet.getRow(2).getCell(0);
        assertEquals("B3", cell.getCellFormula());
        assertEquals("RequiredAcceptanceDate", evaluator.evaluate(cell).getStringValue());

        cell = sheet.getRow(3).getCell(0);
        assertEquals("B4", cell.getCellFormula());
        assertEquals("Header", evaluator.evaluate(cell).getStringValue());

        cell = sheet.getRow(4).getCell(0);
        assertEquals("B5", cell.getCellFormula());
        assertEquals("UniqueDocumentNumberID", evaluator.evaluate(cell).getStringValue());
        
        wb.close();
    }

    /**
     * Test creation / evaluation of formulas with sheet-level names
     */
	@Test
    public void testSheetLevelFormulas() throws IOException {
        Workbook wb = _testDataProvider.createWorkbook();

        Row row;
        Sheet sh1 = wb.createSheet("Sheet1");
        Name nm1 = wb.createName();
        nm1.setNameName("sales_1");
        nm1.setSheetIndex(0);
        nm1.setRefersToFormula("Sheet1!$A$1");
        row = sh1.createRow(0);
        row.createCell(0).setCellValue(3);
        row.createCell(1).setCellFormula("sales_1");
        row.createCell(2).setCellFormula("sales_1*2");

        Sheet sh2 = wb.createSheet("Sheet2");
        Name nm2 = wb.createName();
        nm2.setNameName("sales_1");
        nm2.setSheetIndex(1);
        nm2.setRefersToFormula("Sheet2!$A$1");

        row = sh2.createRow(0);
        row.createCell(0).setCellValue(5);
        row.createCell(1).setCellFormula("sales_1");
        row.createCell(2).setCellFormula("sales_1*3");

        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        assertEquals(3.0, evaluator.evaluate(sh1.getRow(0).getCell(1)).getNumberValue(), 0.0);
        assertEquals(6.0, evaluator.evaluate(sh1.getRow(0).getCell(2)).getNumberValue(), 0.0);

        assertEquals(5.0, evaluator.evaluate(sh2.getRow(0).getCell(1)).getNumberValue(), 0.0);
        assertEquals(15.0, evaluator.evaluate(sh2.getRow(0).getCell(2)).getNumberValue(), 0.0);
        
        wb.close();
    }

	@Test
    public void testFullColumnRefs() throws IOException {
        Workbook wb = _testDataProvider.createWorkbook();
        Sheet sheet = wb.createSheet("Sheet1");
        Row row = sheet.createRow(0);
        Cell cell0 = row.createCell(0);
        cell0.setCellFormula("sum(D:D)");
        Cell cell1 = row.createCell(1);
        cell1.setCellFormula("sum(D:E)");

        // some values in column D
        setValue(sheet, 1, 3, 5.0);
        setValue(sheet, 2, 3, 6.0);
        setValue(sheet, 5, 3, 7.0);
        setValue(sheet, 50, 3, 8.0);

        // some values in column E
        setValue(sheet, 1, 4, 9.0);
        setValue(sheet, 2, 4, 10.0);
        setValue(sheet, 30000, 4, 11.0);

        // some other values
        setValue(sheet, 1, 2, 100.0);
        setValue(sheet, 2, 5, 100.0);
        setValue(sheet, 3, 6, 100.0);


        FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
        assertEquals(26.0, fe.evaluate(cell0).getNumberValue(), 0.0);
        assertEquals(56.0, fe.evaluate(cell1).getNumberValue(), 0.0);
        
        wb.close();
    }
    
	@Test
    public void testRepeatedEvaluation() throws IOException {
       Workbook wb = _testDataProvider.createWorkbook();
       FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
       Sheet sheet = wb.createSheet("Sheet1");
       Row r = sheet.createRow(0);
       Cell c = r.createCell(0, CellType.FORMULA);
       
       // Create a value and check it
       c.setCellFormula("Date(2011,10,6)");
       CellValue cellValue = fe.evaluate(c);
       assertEquals(40822.0, cellValue.getNumberValue(), 0.0);
       cellValue = fe.evaluate(c);
       assertEquals(40822.0, cellValue.getNumberValue(), 0.0);
       
       // Change it
       c.setCellFormula("Date(2011,10,4)");
       
       // Evaluate it, no change as the formula evaluator
       //  won't know to clear the cache
       cellValue = fe.evaluate(c);
       assertEquals(40822.0, cellValue.getNumberValue(), 0.0);
       
       // Manually flush for this cell, and check
       fe.notifySetFormula(c);
       cellValue = fe.evaluate(c);
       assertEquals(40820.0, cellValue.getNumberValue(), 0.0);
       
       // Change again, without notifying
       c.setCellFormula("Date(2010,10,4)");
       cellValue = fe.evaluate(c);
       assertEquals(40820.0, cellValue.getNumberValue(), 0.0);
       
       // Now manually clear all, will see the new value
       fe.clearAllCachedResultValues();
       cellValue = fe.evaluate(c);
       assertEquals(40455.0, cellValue.getNumberValue(), 0.0);
       
       wb.close();
    }

    private static void setValue(Sheet sheet, int rowIndex, int colIndex, double value) {
        Row row = sheet.getRow(rowIndex);
        if (row == null) {
            row = sheet.createRow(rowIndex);
        }
        row.createCell(colIndex).setCellValue(value);
    }

    /**
     * {@link FormulaEvaluator#evaluate(org.apache.poi.ss.usermodel.Cell)} should behave the same whether the cell
     * is <code>null</code> or blank.
     */
    @Test
    public void testEvaluateBlank() throws IOException {
        Workbook wb = _testDataProvider.createWorkbook();
        FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
        assertNull(fe.evaluate(null));
        Sheet sheet = wb.createSheet("Sheet1");
        Cell cell = sheet.createRow(0).createCell(0);
        assertNull(fe.evaluate(cell));
        wb.close();
    }

    /**
     * Test for bug due to attempt to convert a cached formula error result to a boolean
     */
    @Test
    public void testUpdateCachedFormulaResultFromErrorToNumber_bug46479() throws IOException {

        Workbook wb = _testDataProvider.createWorkbook();
        Sheet sheet = wb.createSheet("Sheet1");
        Row row = sheet.createRow(0);
        Cell cellA1 = row.createCell(0);
        Cell cellB1 = row.createCell(1);
        cellB1.setCellFormula("A1+1");
        FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();

        cellA1.setCellErrorValue(FormulaError.NAME.getCode());
        fe.evaluateFormulaCellEnum(cellB1);

        cellA1.setCellValue(2.5);
        fe.notifyUpdateCell(cellA1);
        try {
            fe.evaluateInCell(cellB1);
        } catch (IllegalStateException e) {
            if (e.getMessage().equals("Cannot get a numeric value from a error formula cell")) {
                fail("Identified bug 46479a");
            }
        }
        assertEquals(3.5, cellB1.getNumericCellValue(), 0.0);
        
        wb.close();
    }

    @Test
    public void testRounding_bug51339() throws IOException {
        Workbook wb = _testDataProvider.createWorkbook();
        Sheet sheet = wb.createSheet("Sheet1");
        Row row = sheet.createRow(0);
        Cell cellA1 = row.createCell(0);
        cellA1.setCellValue(2162.615d);
        Cell cellB1 = row.createCell(1);
        cellB1.setCellFormula("round(a1,2)");
        Cell cellC1 = row.createCell(2);
        cellC1.setCellFormula("roundup(a1,2)");
        Cell cellD1 = row.createCell(3);
        cellD1.setCellFormula("rounddown(a1,2)");
        FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();

        assertEquals(2162.62, fe.evaluateInCell(cellB1).getNumericCellValue(), 0.0);
        assertEquals(2162.62, fe.evaluateInCell(cellC1).getNumericCellValue(), 0.0);
        assertEquals(2162.61, fe.evaluateInCell(cellD1).getNumericCellValue(), 0.0);
        
        wb.close();
    }
    
    @Test
    public void evaluateInCellReturnsSameCell() throws IOException {
        Workbook wb = _testDataProvider.createWorkbook();
        wb.createSheet().createRow(0).createCell(0);
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        Cell cell = wb.getSheetAt(0).getRow(0).getCell(0);
        Cell same = evaluator.evaluateInCell(cell);
        assertSame(cell, same);
        wb.close();
    }
}