package art.servers.gost.access.reports;

import art.servers.ServerException;
import art.servers.Shared;
import art.servers.gost.access.configuration.Configuration;
import com.sun.star.beans.PropertyValue;
import com.sun.star.frame.XComponentLoader;
import com.sun.star.frame.XDesktop;
import com.sun.star.frame.XStorable;
import com.sun.star.lang.XComponent;
import com.sun.star.lang.XMultiComponentFactory;
import com.sun.star.lib.uno.adapter.ByteArrayToXInputStreamAdapter;
import com.sun.star.lib.uno.adapter.OutputStreamToXOutputStreamAdapter;
import com.sun.star.uno.UnoRuntime;
import com.sun.star.uno.XComponentContext;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.PrintStream;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.TimeZone;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import ooo.connector.BootstrapSocketConnector;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class XLSX 
{
    protected XSSFWorkbook workbook;
    protected String language = null;

    
    public XLSX ()
    {
    }
    
    public XLSX (File source) throws Exception
    {
        workbook = new XSSFWorkbook(new FileInputStream(source));
    }    

    
    public byte[] save(String format) throws Exception
    {
        if ((format.equalsIgnoreCase("xlsx")) || (format.equalsIgnoreCase("xlsm")))
        {
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            workbook.write(bos);
            byte[] xlsx = bos.toByteArray();
            bos.close();
            return xlsx;
        }

        if (format.equalsIgnoreCase("pdf"))
        {
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            workbook.write(bos);
            byte[] xlsx = bos.toByteArray();
            bos.close();
            return toPDF(xlsx);
        }
        
        if (format.equalsIgnoreCase("csv"))
        {
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            workbook.write(bos);
            byte[] xlsx = bos.toByteArray();
            bos.close();
            return toSCV(xlsx);
        }
        
        throw new Exception("Unknow format");
        
    }
    
    
    
    public byte[] saveAndClose(String format) throws Exception
    {   
        byte[] result = save(format);
        close();
        return result;
    }
    
    
    
    
    public void close ()
    {
        try
        {
            workbook.close();
        }
        catch (Exception e)
        {
        }
    }    


    

    
    public byte[] save(SXSSFWorkbook sxworkbook, String format) throws Exception
    {
        if ((format.equalsIgnoreCase("xlsx")) || (format.equalsIgnoreCase("xlsm")))
        {
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            sxworkbook.write(bos);
            byte[] xlsx = bos.toByteArray();
            bos.close();
            return xlsx;
        }

        if (format.equalsIgnoreCase("pdf"))
        {
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            sxworkbook.write(bos);
            byte[] xlsx = bos.toByteArray();
            bos.close();
            return toPDF(xlsx);
        }
        
        if (format.equalsIgnoreCase("csv"))
        {
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            sxworkbook.write(bos);
            byte[] xlsx = bos.toByteArray();
            bos.close();
            return toSCV(xlsx);
        }
        
        throw new Exception("Unknow format");
        
    }



    public byte[] saveAndClose(SXSSFWorkbook sxworkbook, String format) throws Exception
    {   
        byte[] result = save(sxworkbook, format);
        close(sxworkbook);
        return result;
    }




    public void close (SXSSFWorkbook sxworkbook)
    {
        close();

        try
        {
            sxworkbook.close();
        }
        catch (Exception e)
        {
        }
    }

 
    public XSSFCell getXSSFCell(XSSFSheet xssfsheet, String reference)
    {
        CellReference cellReference = new CellReference(reference);
        XSSFRow row = xssfsheet.getRow(cellReference.getRow());
        XSSFCell cell = row.getCell(cellReference.getCol());
        return cell;
    }
 
 
 
    


    // La primera columna e 1 y no 0
    
    private char[] chars = new char[] {'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'};

    protected String columnName (int index)
    {
        index -= 1;

        int quotient = index / 26;
        if (quotient > 0)
        {
            return columnName(quotient) + chars[index % 26];
        }
        else
        {
            return "" + chars[index % 26];
        }
    }

    
  
    public void createRow (XSSFSheet hoja, int numeroFilaReferencia, int numeroFila) throws Exception
    {
        if (numeroFilaReferencia == numeroFila)  return;
        XSSFRow xssfrowReferencia = hoja.getRow(numeroFilaReferencia);
        XSSFRow xssfrow = hoja.createRow(numeroFila);
        xssfrow.setHeight(xssfrowReferencia.getHeight());
    }
    
    public void createRow (XSSFSheet sheet, int numeroFilaReferencia, int numeroFila, int numeroColumnas) throws Exception
    {
        createRow(sheet, numeroFilaReferencia, numeroFila, numeroColumnas, false);
    }
    
    
    public void createRow (XSSFSheet sheet, int numeroFilaReferencia, int numeroFila, int numeroColumnas, boolean copycontent) throws Exception
    {
        // Creamos la fila

        if (numeroFilaReferencia == numeroFila)  return;
        XSSFRow sourceRow = sheet.getRow(numeroFilaReferencia);
        XSSFRow destinationRow = sheet.createRow(numeroFila);
        destinationRow.setHeight(sourceRow.getHeight());
        
        for (int i=0; i<numeroColumnas; i++)
        {
            XSSFCell celda = destinationRow.createCell(i);

            if (sourceRow.getCell(i) != null)
            {
                celda.setCellStyle(sourceRow.getCell(i).getCellStyle());

                if (copycontent == true)
                {
                    copyValue(sourceRow.getCell(i), celda);
                }
            }
        }
        
        // If there are are any merged regions in the source row, copy to new row
        
        for (CellRangeAddress cellRangeAddress : sheet.getMergedRegions())
        {
                if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum())
                {
                    try
                    {
                        CellRangeAddress  newCellRangeAddress = new CellRangeAddress
                        (    
                            destinationRow.getRowNum(),
                            (destinationRow.getRowNum() +
                            (cellRangeAddress.getFirstRow() -
                            cellRangeAddress.getLastRow())),
                            cellRangeAddress.getFirstColumn(),
                            cellRangeAddress.getLastColumn()
                        );

                        sheet.addMergedRegion(newCellRangeAddress);
                    }
                    catch (Exception e)
                    {
                    }
            }
        }        
    }
    
    
    public void copyValue (XSSFCell source, XSSFCell destination) throws Exception
    {
        if (source == null) return;
        if (destination == null) return;

        if (source.getStringCellValue().isEmpty() == false) 
        {
            switch(source.getCellTypeEnum()) 
            {   
                case STRING:   
                     destination.setCellValue(source.getStringCellValue());   
                     break;   
                case NUMERIC:
                     destination.setCellValue(source.getNumericCellValue());   
                     break;   
                case BLANK:
                     destination.setCellType(CellType.BLANK);   
                     break;   
                case BOOLEAN: 
                     destination.setCellValue(source.getBooleanCellValue());   
                     break;   
                case ERROR:   
                     destination.setCellErrorValue(source.getErrorCellValue());   
                     break;   
                case FORMULA:   
                     destination.setCellFormula(source.getCellFormula());   
                     break;   
                default:   
                     break;   
            }               
        }
    }
  
    
    
    public void createRow (XSSFSheet sheet, SXSSFSheet sxsheet, int numeroFilaReferencia, int numeroFila, int numeroColumnas) throws Exception
    {
        createRow(sheet, sxsheet, numeroFilaReferencia, numeroFila, numeroColumnas, false);
    }
    
    
    public void createRow (XSSFSheet sheet, SXSSFSheet sxsheet, int numeroFilaReferencia, int numeroFila, int numeroColumnas, boolean copycontent) throws Exception
    {
        // Creamos la fila

        XSSFRow sourceRow = sheet.getRow(numeroFilaReferencia);
        SXSSFRow destinationRow = sxsheet.createRow(numeroFila);
        destinationRow.setHeight(sourceRow.getHeight());
        
        for (int i=0; i<numeroColumnas; i++)
        {
            SXSSFCell celda = destinationRow.createCell(i);

            if (sourceRow.getCell(i) != null)
            {
                celda.setCellStyle(sourceRow.getCell(i).getCellStyle());

                if (copycontent == true)
                {
                    copyValue(sourceRow.getCell(i), celda);
                }
            }
        }
        
        // If there are are any merged regions in the source row, copy to new row
        
        for (CellRangeAddress cellRangeAddress : sheet.getMergedRegions())
        {
                if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum())
                {
                    try
                    {
                        CellRangeAddress  newCellRangeAddress = new CellRangeAddress
                        (    
                            destinationRow.getRowNum(),
                            (destinationRow.getRowNum() +
                            (cellRangeAddress.getFirstRow() -
                            cellRangeAddress.getLastRow())),
                            cellRangeAddress.getFirstColumn(),
                            cellRangeAddress.getLastColumn()
                        );

                        sheet.addMergedRegion(newCellRangeAddress);
                    }
                    catch (Exception e)
                    {
                    }
            }
        }        
    }
    
    
    public void copyValue (XSSFCell source, SXSSFCell destination) throws Exception
    {
        if (source == null) return;
        if (destination == null) return;

        if (source.getStringCellValue().isEmpty() == false) 
        {
            switch(source.getCellTypeEnum()) 
            {   
                case STRING:   
                     destination.setCellValue(source.getStringCellValue());   
                     break;   
                case NUMERIC:
                     destination.setCellValue(source.getNumericCellValue());   
                     break;   
                case BLANK:
                     destination.setCellType(CellType.BLANK);   
                     break;   
                case BOOLEAN: 
                     destination.setCellValue(source.getBooleanCellValue());   
                     break;   
                case ERROR:   
                     destination.setCellErrorValue(source.getErrorCellValue());   
                     break;   
                case FORMULA:   
                     destination.setCellFormula(source.getCellFormula());   
                     break;   
                default:   
                     break;   
            }               
        }
    }



    public String getCellValueAsText (XSSFCell cell)
    {
        try
        {
            switch(cell.getCellTypeEnum()) 
            {   
                case STRING: return cell.getStringCellValue();
                case NUMERIC: return "" + (int)cell.getNumericCellValue();
                case BOOLEAN: return "" + cell.getBooleanCellValue(); 
                case FORMULA: return "" + cell.getCellFormula(); 
            }               
        }
        catch (Exception e)
        {
        }
        
        return "";
    }    
    
    
    
        
    public int getColumnWidth (XSSFSheet sheet, int col1, int col2) throws Exception
    {
        int result = 0;
        
        for (int i=col1; i<=col2; i++)
        {
            result = result + sheet.getColumnWidth(i);
        }
        
        return result;
    }
    
    
    public float getColumnWidthInPixels (XSSFSheet sheet, int col1, int col2) throws Exception
    {
        float result = 0;
        
        for (int i=col1; i<=col2; i++)
        {
            result = result + sheet.getColumnWidthInPixels(i);
        }
        
        return result;
    }
    
    
    
    
    public void createRow (SXSSFSheet sheet, int numeroFilaReferencia, int numeroFila, int numeroColumnas) throws Exception
    {
        createRow(sheet, numeroFilaReferencia, numeroFila, numeroColumnas, false);
    }
    
    
    public void createRow (SXSSFSheet sheet, int numeroFilaReferencia, int numeroFila, int numeroColumnas, boolean copycontent) throws Exception
    {
        // Creamos la fila

        if (numeroFilaReferencia == numeroFila)  return;
        SXSSFRow sourceRow = sheet.getRow(numeroFilaReferencia);
        SXSSFRow destinationRow = sheet.createRow(numeroFila);
        destinationRow.setHeight(sourceRow.getHeight());
        
        for (int i=0; i<numeroColumnas; i++)
        {
            SXSSFCell celda = destinationRow.createCell(i);

            if (sourceRow.getCell(i) != null)
            {
                celda.setCellStyle(sourceRow.getCell(i).getCellStyle());

                if (copycontent == true)
                {
                    copyValue(sourceRow.getCell(i), celda);
                }
            }
        }
        
        // If there are are any merged regions in the source row, copy to new row
        
        for (CellRangeAddress cellRangeAddress : sheet.getMergedRegions())
        {
                if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum())
                {
                    try
                    {
                        CellRangeAddress  newCellRangeAddress = new CellRangeAddress
                        (    
                            destinationRow.getRowNum(),
                            (destinationRow.getRowNum() +
                            (cellRangeAddress.getFirstRow() -
                            cellRangeAddress.getLastRow())),
                            cellRangeAddress.getFirstColumn(),
                            cellRangeAddress.getLastColumn()
                        );

                        sheet.addMergedRegion(newCellRangeAddress);
                    }
                    catch (Exception e)
                    {
                    }
            }
        }        
    }
    
    
    public void copyValue (SXSSFCell source, SXSSFCell destination) throws Exception
    {
        if (source == null) return;
        if (destination == null) return;

        if (source.getStringCellValue().isEmpty() == false) 
        {
            switch(source.getCellTypeEnum()) 
            {   
                case STRING:   
                     destination.setCellValue(source.getStringCellValue());   
                     break;   
                case NUMERIC:
                     destination.setCellValue(source.getNumericCellValue());   
                     break;   
                case BLANK:
                     destination.setCellType(CellType.BLANK);   
                     break;   
                case BOOLEAN: 
                     destination.setCellValue(source.getBooleanCellValue());   
                     break;   
                case ERROR:   
                     destination.setCellErrorValue(source.getErrorCellValue());   
                     break;   
                case FORMULA:   
                     destination.setCellFormula(source.getCellFormula());   
                     break;   
                default:   
                     break;   
            }               
        }
    }
    
    
    public String getDate(long timestamp)
    {
        SimpleDateFormat ISO8601DATEFORMAT = new SimpleDateFormat(Shared.getMessage("yyyy-MM-dd HH:mm:ss"));
        ISO8601DATEFORMAT.setTimeZone(TimeZone.getDefault());
        return ISO8601DATEFORMAT.format(timestamp);
    }
    

    
    
    
    
    
    public int getNumberOfDays(long timestamp1, long timestamp2)
    {
        Calendar calendar1 = Calendar.getInstance();
        calendar1.setTimeInMillis(timestamp1);
        calendar1.set(Calendar.HOUR_OF_DAY,0);
        calendar1.set(Calendar.MINUTE,0);
        calendar1.set(Calendar.SECOND,0);
        calendar1.set(Calendar.MILLISECOND,0);

        Calendar calendar2 = Calendar.getInstance();
        calendar2.setTimeInMillis(timestamp2);
        calendar2.set(Calendar.HOUR_OF_DAY,0);
        calendar2.set(Calendar.MINUTE,0);
        calendar2.set(Calendar.SECOND,0);
        calendar2.set(Calendar.MILLISECOND,0);
        
        int numberOfDays = 0;

        do
        {
            numberOfDays = numberOfDays + 1;
            calendar1.add(Calendar.DATE, 1);
        }
        while(calendar1.getTimeInMillis() < calendar2.getTimeInMillis());
        
        
       return numberOfDays;
    }
    
    

    public void setBorder(XSSFSheet xssfsheet, CellRangeAddress cellRangeAddress, BorderStyle borderStyle)
    {
        RegionUtil.setBorderTop(borderStyle, cellRangeAddress, xssfsheet);
        RegionUtil.setBorderLeft(borderStyle, cellRangeAddress, xssfsheet);
        RegionUtil.setBorderBottom(borderStyle, cellRangeAddress, xssfsheet);
        RegionUtil.setBorderRight(borderStyle, cellRangeAddress, xssfsheet);
    }
    
    

    public void setBorder(SXSSFSheet sxssfsheet, CellRangeAddress cellRangeAddress, BorderStyle borderStyle)
    {
        RegionUtil.setBorderTop(borderStyle, cellRangeAddress, sxssfsheet);
        RegionUtil.setBorderLeft(borderStyle, cellRangeAddress, sxssfsheet);
        RegionUtil.setBorderBottom(borderStyle, cellRangeAddress, sxssfsheet);
        RegionUtil.setBorderRight(borderStyle, cellRangeAddress, sxssfsheet);
    }
    
    

    
    public byte[] toPDF(byte[] xlsx) throws ServerException, Exception
    {
        try
        {
            // Initialise
            String oooExeFolder = ((Configuration)Shared.configuration).detail.libreOfficebinaryPath;
            XComponentContext xContext = BootstrapSocketConnector.bootstrap(oooExeFolder);
            XMultiComponentFactory xMCF = xContext.getServiceManager();
            Object oDesktop = xMCF.createInstanceWithContext("com.sun.star.frame.Desktop", xContext);
            XDesktop xDesktop = (XDesktop) UnoRuntime.queryInterface(XDesktop.class, oDesktop);
            XComponentLoader xCompLoader = (XComponentLoader)UnoRuntime.queryInterface(com.sun.star.frame.XComponentLoader.class, xDesktop);

            PropertyValue[] propertyValues = new PropertyValue[2];
            propertyValues[0] = new PropertyValue();
            propertyValues[0].Name = "Hidden";
            propertyValues[0].Value = new Boolean(true);
            propertyValues[1] = new PropertyValue();
            propertyValues[1].Name = "InputStream";
            propertyValues[1].Value = new ByteArrayToXInputStreamAdapter(xlsx);
            XComponent xComp = xCompLoader.loadComponentFromURL("private:stream", "_blank", 0, propertyValues);         

            
            // save as a PDF

            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            XStorable xStorable = (XStorable) UnoRuntime.queryInterface(XStorable.class, xComp);
            propertyValues = new PropertyValue[3];
            propertyValues[0] = new PropertyValue();
            propertyValues[0].Name = "Overwrite";
            propertyValues[0].Value = new Boolean(true);
            propertyValues[1] = new PropertyValue();
            propertyValues[1].Name = "FilterName";
            propertyValues[1].Value = "writer_pdf_Export";
            propertyValues[2] = new PropertyValue();
            propertyValues[2].Name = "OutputStream";
            propertyValues[2].Value = new OutputStreamToXOutputStreamAdapter(bos);
            xStorable.storeToURL("private:stream", propertyValues);

            // shutdown
            
            xDesktop.terminate();

            // Result 
            
            byte[] pdf = bos.toByteArray();
            bos.close();
            return pdf;
        }
        catch (Exception e)
        {
            throw new ServerException(Shared.getMessage(language, "Error generating pdf"));
        }
    }
    


    
    public byte[] toSCV (byte[] xlsx) throws ServerException, Exception
    {
        try
        {
            XSSFFormulaEvaluator fe = workbook.getCreationHelper().createFormulaEvaluator();
            DataFormatter formatter = new DataFormatter();
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            PrintStream out = new PrintStream(bos, true, "UTF-8");
            byte[] bom = {(byte)0xEF, (byte)0xBB, (byte)0xBF};
            {
                for (int i=0; i<workbook.getNumberOfSheets(); i++)
                {
                    XSSFSheet sheet = workbook.getSheetAt(i);

                    for (int r = 0, rn = sheet.getLastRowNum() ; r <= rn ; r++) 
                    {
                        XSSFRow row = sheet.getRow(r);
                        if ( row == null ) { out.println(';'); continue; }
                        boolean firstCell = true;
                        for (int c = 0, cn = row.getLastCellNum() ; c < cn ; c++) 
                        {
                            XSSFCell cell = row.getCell(c, XSSFRow.MissingCellPolicy.RETURN_BLANK_AS_NULL);

                            if (!firstCell) out.print(';');

                            try
                            {
                                if (cell != null) 
                                {
                                    if (cell.getCellTypeEnum() == CellType.BLANK) 
                                    {
                                        out.print("");
                                    }
                                    else if (cell.getCellTypeEnum() == CellType.BOOLEAN) 
                                    {
                                        out.print(cell.getBooleanCellValue());
                                    }
                                    else if (cell.getCellTypeEnum() == CellType.ERROR) 
                                    {
                                        out.print("");
                                    }
                                    else if (cell.getCellTypeEnum() == CellType.FORMULA) 
                                    {
                                        out.print(formatter.formatCellValue(fe.evaluateInCell(cell)));
                                    }
                                    else if (cell.getCellTypeEnum() == CellType.NUMERIC)
                                    {
                                        out.print(cell.getNumericCellValue());
                                    }
                                    else if (cell.getCellTypeEnum() == CellType.STRING)
                                    {
                                        out.print(cell.getStringCellValue());
                                    }
                                    else if (cell.getCellTypeEnum() == CellType._NONE)
                                    {
                                        out.print("");
                                    }
                                    else
                                    {
                                        out.print("");
                                    }
                                }
                            }
                            catch (Exception e)
                            {
                            }
                            firstCell = false;
                        }
                        out.println();
                    }
                }
            }

            out.close();
            bos.close();
            return bos.toByteArray();
        }
        catch (Exception e)
        {
            throw new ServerException(Shared.getMessage(language, "Error generating csv"));
        }
    }    

    
    

    static private Pattern rxquote = Pattern.compile("\"");

    static private String encodeValue(String value) 
    {
        boolean needQuotes = false;
        if (value.indexOf(',') != -1 || value.indexOf('"') != -1 || value.indexOf('\n') != -1 || value.indexOf('\r') != -1)
        {
            needQuotes = true;
        }
        Matcher m = rxquote.matcher(value);
        if ( m.find() ) needQuotes = true; value = m.replaceAll("\"\"");
        if ( needQuotes ) return "\"" + value + "\"";
        else return value;

    }        
    
    
}