package excelTesting;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.util.Calendar;
import java.util.Date;

import org.apache.poi.hssf.record.crypto.Biff8EncryptionKey;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.poifs.crypt.EncryptionInfo;
import org.apache.poi.poifs.crypt.EncryptionMode;
import org.apache.poi.poifs.crypt.Encryptor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Header;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.util.LittleEndianInput;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.ibx.consumer.common.javautils.DateUtils;
import com.ibx.consumer.common.javautils.FileUtils;


public class ExcelEncryption {

	
	XSSFWorkbook workbook = null;
	
	private static XSSFFont claimBoldFont = null;
	private static XSSFFont smallBoltFont = null;
	
	private static XSSFFont boldFont = null;
	private static XSSFFont patientboldFont = null;
	private static XSSFFont smallFont = null;
	private static ReportColumn[] claimColumns = null;
	private static ReportColumn[] patientColumns = null;
	private static ReportColumn[] patientTotalColumns = null;
	private static ReportColumn[] patientOnlyColumns = null;
	private static ReportColumn[] patientOver18 = null;
	private static String patientOver18Msg1 = "This member on your account is over 18. "
			+ "To get their tax year report information, they must log in to their own account ";
	private static String patientOver18Msg2 = "to generate a tax year report.";

	public static int ELIG_DEPENDENT_AGE = 18;
	
	
	
	private void initCERXlsx() {
		boldFont = workbook.createFont();
		boldFont.setBold(true);
		
		claimBoldFont = workbook.createFont();
		claimBoldFont.setFontHeightInPoints((short) 10);
		claimBoldFont.setBold(true);
		
		smallBoltFont = workbook.createFont();
		smallBoltFont.setFontHeightInPoints((short) 10);
		smallBoltFont.setBold(true);
		
		smallFont = workbook.createFont();
		smallFont.setFontHeightInPoints((short) 10);

		claimColumns = new ReportColumn[] {
				new ReportColumn("patientIdentifierKey", "Member Name", FormatType.TEXT,17),
				new ReportColumn("serviceDate", "Service Date", FormatType.DATE,10),
				new ReportColumn("claimNumber", "Claim Number", FormatType.TEXT,12),	
				new ReportColumn("claimType", "Type of Claim", FormatType.TEXT,11),
				new ReportColumn("provider", "Provider", FormatType.TEXT,24),			
				new ReportColumn("claimAmt", "Total Amount of Claim", FormatType.MONEY,13),
				new ReportColumn("patientResponsibility", "Member Cost", FormatType.MONEY,10) };	
	}
	
	private void writeCell(XSSFRow row, int col, Object value,
			FormatType formatType, Short bgColor, XSSFFont font,
			XSSFCellStyle style) throws Exception {
		System.out.println("starting write Cell ");
		try {
			XSSFCell cell = row.createCell(col);

			if (value == null) {
				return;
			}
			if (style == null) {
				style = workbook.createCellStyle();
				style.setVerticalAlignment(VerticalAlignment.CENTER);
			} else {
				style.setVerticalAlignment(VerticalAlignment.CENTER);
			}

			if (font != null) {
				style.setFont(font);
			} else {
				style.setFont(smallFont);
			}
			DataFormat format = workbook.createDataFormat();
			switch (formatType) {
			case TEXT:
				cell.setCellValue(value.toString().replaceAll("Name :", "Name:"));
				style.setAlignment(HorizontalAlignment.LEFT);
				style.setWrapText(true);
				cell.setCellStyle(style);
				break;
			case INTEGER:
				cell.setCellValue(((Number) value).intValue());
				style.setDataFormat(format.getFormat("#,##0"));
				style.setAlignment(HorizontalAlignment.LEFT);
				cell.setCellStyle(style);
				break;
			case NUMBER:
				cell.setCellValue(value.toString());
				style.setDataFormat(format.getFormat("###0"));
				style.setAlignment(HorizontalAlignment.LEFT);
				cell.setCellStyle(style);
				cell.setCellType(Cell.CELL_TYPE_NUMERIC);
				break;
			case FLOAT:
				cell.setCellValue(((Number) value).doubleValue());
				style.setDataFormat(format.getFormat("#,##0.00"));
				style.setAlignment(HorizontalAlignment.LEFT);
				cell.setCellStyle(style);
				break;
			case DATE:
				cell.setCellValue((Date) value);
				style.setDataFormat(format.getFormat("mm/dd/yyyy"));
				style.setAlignment(HorizontalAlignment.LEFT);
				cell.setCellStyle(style);
				break;
			case MONEY:
				/*
				 * cell.setCellValue(((Number) value).doubleValue());
				 * style.setDataFormat(format.getFormat("$#,##0.00;$#,##0.00"));
				 * style.setAlignment(HorizontalAlignment.LEFT);
				 * cell.setCellStyle(style);
				 */
				cell.setCellType(Cell.CELL_TYPE_STRING);
				style.setAlignment(HorizontalAlignment.RIGHT);
				cell.setCellStyle(style);
				DecimalFormat df = new DecimalFormat("#,###,###.00");
				cell.setCellValue("$"
						+ df.format(((Number) value).doubleValue()));
				break;
			case PERCENTAGE:
				cell.setCellValue(((Number) value).doubleValue());
				style.setDataFormat(format.getFormat("0.00%"));
				cell.setCellStyle(style);
			default:
				break;
			}
			if (bgColor != null) {

			}
			System.out.println("end write Cell ");
		} catch (Exception e) {
			System.out.println(e.getMessage() + " (ECM3212)");
			e.fillInStackTrace();
			throw new Exception(e);
		}
	}
	
	private void writeTitleRow(XSSFRow row) throws Exception{
		 int numCols = claimColumns.length;
		
		 try{
			 for (int x = 0; x < numCols; x++) {
				 XSSFCellStyle style = null;
				 style = workbook.createCellStyle();
				 style.setBorderTop(BorderStyle.MEDIUM);
				 style.setBorderBottom(BorderStyle.MEDIUM);
				 
				
				//style.setBorderBottom(XSSFCellStyle.BORDER_DOTTED);
				style.setBorderLeft(BorderStyle.MEDIUM);
				style.setBorderRight(BorderStyle.MEDIUM);

				 style.setAlignment(HorizontalAlignment.RIGHT);
					 //style.setWrapText(false);
					 //writeCell(row, x, claimColumns[x].getHeader(),
					//			FormatType.TEXT, null, claimBoldFont ,style);
				 
				 writeCell(row, x, claimColumns[x].getHeader(),
								FormatType.TEXT, null, claimBoldFont ,style);
			 }
		 }catch (Exception e) {
				System.out.println(e.getMessage() + " (ECM3197)");
				e.fillInStackTrace();
				throw new Exception(e);
		}
		
	}
	
	public byte[] doReport(String brand) throws Exception {
		byte[] xlsBytes = null;
		try {
			String fileName = "Headertemplate.xlsx";
			InputStream file = FileUtils.readDataFile(brand, fileName);
			String dateRangeLabel = "Date Range of Report: ";
			String dateRangeVal = "2017";
			
			OPCPackage opc = OPCPackage.open(file);
			workbook = new XSSFWorkbook(opc);
			initCERXlsx();
			XSSFSheet my_worksheet = workbook.getSheetAt(0);
			Header header = my_worksheet.getHeader();
			String rightHeader = header.getRight() + DateUtils.currentDate("MM/dd/yyyy");
			header.setRight(rightHeader);
						
			my_worksheet.setMargin(Sheet.LeftMargin,0.1);
			my_worksheet.setMargin(Sheet.RightMargin,0.1);
			
//			addRowsToSheet(taxYearBean.getPatientList(), my_worksheet, dateRangeVal);
			XSSFRow row;
			int currentRow = 0;
			row = my_worksheet.createRow(currentRow++);
			writeTitleRow(row);			
			
			Calendar cal = Calendar.getInstance();
	        cal.setTime(new Date());
	        long timinmillis = cal.getTimeInMillis();
	        
	        
	        my_worksheet.enableLocking();
	        my_worksheet.lockDeleteColumns(true);
	        my_worksheet.lockDeleteRows(true);
	        my_worksheet.lockFormatCells(true);
	        my_worksheet.lockFormatColumns(true);
	        my_worksheet.lockFormatRows(true);
	        my_worksheet.lockInsertColumns(true);
	        my_worksheet.lockInsertRows(true);
	        
			//try {
			//FileOutputStream fileOut = new FileOutputStream(CacheEnv.APP_HOME + "/TYR/" + userid + "excel.xlsx");
//			FileOutputStream fileOut = new FileOutputStream("c:/Temp/" + userid + "excel.xlsx");
			//InputStream inStream;
			//inStream.read(workbook);
	        
	        //workbook.write(fileOut);
			//fileOut.close();
//			} catch (IOException e) {
//				e.printStackTrace();
//			} // Add password protection and encrypt the file
			POIFSFileSystem fs = new POIFSFileSystem();
			EncryptionInfo info = new EncryptionInfo(EncryptionMode.agile);

			Encryptor enc = info.getEncryptor();
			enc.confirmPassword("Password1");

			//OPCPackage opc = OPCPackage.open(new File(CacheEnv.APP_HOME + "/TYR/" + userid + "excel.xlsx"),
//			OPCPackage opc = OPCPackage.open(new File("c:/Temp/" + userid + "excel.xlsx"),
//					PackageAccess.READ_WRITE);
			OutputStream os = enc.getDataStream(fs);
			opc.save(os);
			opc.close();
			
	        
	        //Biff8EncryptionKey.setCurrentUserPassword("Password1");
	        
			ByteArrayOutputStream outputStream = new ByteArrayOutputStream();

			//FileOutputStream fos = new FileOutputStream("c:/Temp/excel.xlsx");
			workbook.write(outputStream);
			fs.writeFilesystem(outputStream);
			//fs.writeFilesystem(outputStream);
			//workbook.write(outputStream);
			//outputStream.flush();
			outputStream.close();
			
			//fos.close();

	        System.out.println("closing the template file");
			file.close();
			// my_worksheet.protectSheet("blue");
			xlsBytes = outputStream.toByteArray();
			
//			//File f = new File(CacheEnv.APP_HOME + "/TYR/" + userid + "excel.xlsx");
//			File f = new File("c:/Temp/" + userid + "excel.xlsx");
//			if (f.delete()) {
//				System.out.println("File Deleted!");
//			} else {
//				System.out.println("Failed to delete...");
//			}
//			
		} catch (FileNotFoundException e1) {
			System.out.println(e1.getMessage() + " (ECM3211)");
			e1.printStackTrace();
			throw new Exception(e1);
		} catch (IOException ioe) {
			System.out.println(ioe.getMessage() + " (ECM3211)");
			ioe.fillInStackTrace();
			throw new Exception(ioe);
		} catch (Exception e) {
			System.out.println(e.getMessage() + " (ECM3211)");
			e.fillInStackTrace();
			throw new Exception(e);
		}
		return xlsBytes;
	}
	
	
	
	public static void main(String[] args) throws Exception {
		ExcelEncryption ee = new ExcelEncryption();
		byte[] typeBytes = ee.doReport("IBC");
		EmailMediator em = new EmailMediator();
		em.emailTaxYearReportXls("consumeribx", typeBytes,
				"jacob.bigelow@ibx.com", EmailMediator.STATUS_CLOSE,
				username, password);
	}
}