Arrivals Report con datos de la reserva y de perfil del huésped desde Opera PMS

En este blog desarrollaremos un aplicativo para generar un reporte de Arrivals desde Java, similar al formulario Arrivals Search de Opera PMS.



La ventaja de desarrollar el aplicativo de Arrivals Reports personalizado es que podemos obtener en un solo reporte los datos de la reserva o el perfil del huésped que de otra manera tendríamos que adentrarnos a las demás pantallas de la reserva o del profile de Opera PMS, es decir podemos  mostrar en el reporte además de la información de la reserva, las notas, traces, alertas, comentarios, preferencias, attachment y demás información necesaria para poder anticiparnos a la llegada de los huéspedes y con ello agilizar las atenciones para brindarle una buena impresión al cliente.

El query para obtener los datos a mostrar en el reporte es el siguiente:

SELECT a.confirmation_no,a.guest_name,a.guest_first_name,TO_CHAR(a.arrival,'DD/MM/RR') arrival,TO_CHAR(a.departure,'DD/MM/RR') departure,
a.guest_country,a.guest_country_desc,a.guest_name_id,o.problem_desc file_desc,o.bfile_locator,
(CASE WHEN o.bfile_locator IS NULL THEN 'N' ELSE 'Y' END) is_attachment,
(SELECT wm_concat(note_code||' '||notes) FROM name$notes WHERE name_id=a.guest_name_id) notes
FROM reservation_general_view a
LEFT JOIN ACC_CON_ALL_ATTACHMENTS t ON (a.guest_name_id=t.link_id)
LEFT JOIN work_orders o ON (o.wo_number=t.attach_id)
WHERE TO_CHAR(a.ARRIVAL,'DD/MM/RRRR')='15/11/2019'
AND (A.RESV_STATUS='RESERVED' OR A.RESV_STATUS='PROSPECT')
ORDER BY a.sguest_name ASC

El siguiente programa en Java permite generar el reporte huéspedes por arribar con los datos de la reserva, los datos del perfil del huésped  y la descarga de los documentos anexos (attachment) del huésped. Los anexos son Documentos de Identidad o el Pasaporte que facilitan el registro de la reserva.




package com.myhotel.spring.controller;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.io.FileUtils;

import com.myhotel.dao.ConnectionFactoryOperaBat;
import com.myhotel.vo.Arrival;
import com.lowagie.text.Document;
import com.lowagie.text.DocumentException;
import com.lowagie.text.Font;
import com.lowagie.text.Paragraph;
import com.lowagie.text.pdf.PdfContentByte;
import com.lowagie.text.pdf.PdfImportedPage;
import com.lowagie.text.pdf.PdfPCell;
import com.lowagie.text.pdf.PdfPTable;
import com.lowagie.text.pdf.PdfReader;
import com.lowagie.text.pdf.PdfWriter;

//including this import makes the code easier to read
import oracle.jdbc.driver.OracleResultSet;
// needed for new BFILE class
import oracle.sql.BFILE;

public class FileAttachment {
	private String pathfile;
	private String mergefilename;
	
	public FileAttachment(String pathfile,String mergefilename){
		this.pathfile=pathfile;
		this.mergefilename=mergefilename;
	}
	
	public  void createFile(String fechaArrival)throws Exception{
		PreparedStatement ps=null;
		ResultSet rs=null;
		try{
			Connection conn = ConnectionFactoryOperaBat.getInstance().getConnection();
			// Select the file from the table
			StringBuilder query = new StringBuilder();
	        query.append(" SELECT a.confirmation_no,a.guest_name,a.guest_first_name,TO_CHAR(a.arrival,'DD/MM/RR') arrival,TO_CHAR(a.departure,'DD/MM/RR') departure,a.guest_country,a.guest_country_desc,a.guest_name_id,o.problem_desc file_desc,o.bfile_locator, "); 
	        query.append(" (CASE WHEN o.bfile_locator IS NULL THEN 'NO' ELSE 'SI' END) IS_ATTACHMENT ");
	        query.append(" FROM reservation_general_view a ");
	        query.append(" LEFT JOIN ACC_CON_ALL_ATTACHMENTS t on (a.guest_name_id=t.link_id) ");
	        query.append(" LEFT JOIN work_orders o on (o.wo_number=t.attach_id) ");
	        query.append(" WHERE TO_CHAR(a.ARRIVAL,'DD/MM/RRRR')=? ");
	        query.append(" AND (A.RESV_STATUS='RESERVED' OR A.RESV_STATUS='PROSPECT') ");
	        query.append(" ORDER BY a.sguest_name ASC ");
	        
            ps = conn.prepareStatement(query.toString());
            ps.setString(1, fechaArrival);
            
            rs = ps.executeQuery();
            
			List<InputStream> list = new ArrayList<InputStream>();
			
			List<Arrival> lAtt_SI = new ArrayList<Arrival>();
			List<Arrival> lAtt_NO = new ArrayList<Arrival>();
			
			while (rs.next()) {
				String isAttachment = rs.getString("IS_ATTACHMENT");
				if(isAttachment.equals("SI")){
					String x = rs.getString("file_desc");
					BFILE bfile = ((OracleResultSet) rs).getBFILE("bfile_locator");
					// Dump the file contents
					dumpBfile(conn, bfile);
					list.add(new FileInputStream(new File(this.pathfile+bfile.getName()+".pdf")));
				}
				
				Arrival att=new Arrival();
				att.setConfirmationNo(rs.getString("confirmation_no"));
				att.setGuestName(rs.getString("guest_name"));
				att.setGuestFirstName(rs.getString("guest_first_name"));
				att.setArrival(rs.getString("arrival"));
				att.setDeparture(rs.getString("departure"));
				att.setGuestCountry(rs.getString("guest_country_desc"));
				att.setIsAttachment(rs.getString("IS_ATTACHMENT"));
				
				if(isAttachment.equals("SI")){
					lAtt_SI.add(att);	
				}else{
					lAtt_NO.add(att);
				}
			}
			
			// Resulting pdf
	        OutputStream out = new FileOutputStream(new File(this.pathfile+this.mergefilename));
	        doMerge(list,lAtt_SI,lAtt_NO, out);
			
			// Close all resources
		} catch (SQLException e) {
            throw new RuntimeException(e);
        } catch (Exception ex) {
            throw new Exception(ex);
        } finally {
        	if (ps != null) {try {ps.close();} catch (SQLException e) {e.printStackTrace();}}
            if (rs != null) {try {rs.close();} catch (SQLException e) {e.printStackTrace();}}
        }
	}
	
	// Utility function to dump the contents of a Bfile
	private  void dumpBfile(Connection conn, BFILE bfile) throws Exception {
		bfile.openFile();
		long length = bfile.length();

		InputStream instream = bfile.getBinaryStream();
			byte[] buffer = new byte[instream.available()];
			instream.read(buffer);
		 
		    File targetFile = new File(this.pathfile+bfile.getName()+".pdf");
		    FileUtils.copyInputStreamToFile(instream, targetFile);
		    
	    //Files.write(buffer, targetFile);
		// Close input stream
		instream.close();
		// close file handler
		bfile.closeFile();
	}

	private  void doMerge(List<InputStream> list,List<Arrival> lAtt_SI,List<Arrival> lAtt_NO, OutputStream outputStream)
            throws DocumentException, IOException {
        Document document = new Document();
        PdfWriter writer = PdfWriter.getInstance(document, outputStream);
        document.open();
        
        PdfPTable table = new PdfPTable(7);
        
        PdfPCell cell = new PdfPCell(new Paragraph("WITH ATTACHMENTS"));
        cell.setColspan(7);
        table.addCell(cell);
        //--
        table.addCell("Confirmation No");
        table.addCell("Guest Name");
        table.addCell("Guest First Name");
        table.addCell("Arrival");
        table.addCell("Departure");
        table.addCell("Guest Country");
        table.addCell("Att");
        //--
        for (Arrival att : lAtt_SI) {
        	table.addCell(att.getConfirmationNo());
	        table.addCell(att.getGuestName());
	        table.addCell(att.getGuestFirstName());
	        table.addCell(att.getArrival());
	        table.addCell(att.getDeparture());
	        table.addCell(att.getGuestCountry());
	        table.addCell(att.getIsAttachment());
        }
        document.add(table);
        
        PdfPTable tableN = new PdfPTable(7);
        tableN.setSpacingBefore(20);
        
        PdfContentByte cb = writer.getDirectContent();
        for (InputStream in : list) {
            PdfReader reader = new PdfReader(in);
            for (int i = 1; i <= reader.getNumberOfPages(); i++) {
                document.newPage();
                PdfImportedPage page = writer.getImportedPage(reader, i);
                cb.addTemplate(page, 0, 0);
            }
        }
        
        outputStream.flush();
        document.close();
        outputStream.close();
    }
	
	public static void main(String args[]) throws Exception {
		FileAttachment s=new FileAttachment("D:\\etc\\","result.pdf");
		s.createFile("14/11/2019");
	}
}