3. Aplicación Java (I): Consulta de documentos y firmas de Gexflow a la BD. Arreglar el documento

 1. Acceso a la BD mediante Hibernate. Configuración

Se va a acceder a la BD por JNI, por tanto tenemos que tener presente 2 ficheros : context.xml y persistence_Gexflow.properties  

1.1 Fichero src/main/webapp/META-INF/context.xml

Este fichero se inican todas las conexiones a las BBDD que se realizan por JNDI, en este caso se destaca la conexión a Gexflow. Las demás pueden quitarse del fichero pero se han puesto a modo de ejemplo.

<Context>
  <Resource name="control_post"            auth="Container" type="javax.sql.DataSource" username="user-control"  password="pwd-control"  maxTotal="50" maxIdle="20" maxWait="10000" timeBetweenEvictionRunsMillis="180000" removeAbandonedOnMaintenance="true" removeAbandonedOnBorrow="true" driverClassName="org.postgresql.Driver"            url="jdbc:postgresql://192.168.xx.xx:5432/openweb"/>
  <Resource name="PostgressGexflowJPAPU"   auth="Container" type="javax.sql.DataSource" username="user-gexflow"  password="pwd-gexflow"  maxTotal="50" maxIdle="20" maxWait="10000" timeBetweenEvictionRunsMillis="180000" removeAbandonedOnMaintenance="true" removeAbandonedOnBorrow="true" driverClassName="org.postgresql.Driver"            url="jdbc:postgresql://192.168.xx.xx:5432/suite_gexflow"/>
  <Resource name="sqlserver-aytos-jtds"    auth="Container" type="javax.sql.DataSource" username="user.aytos"    password="pwd-aytos"    maxTotal="50" maxIdle="20" maxWait="10000" timeBetweenEvictionRunsMillis="180000" removeAbandonedOnMaintenance="true" removeAbandonedOnBorrow="true" driverClassName="net.sourceforge.jtds.jdbc.Driver" url="jdbc:jtds:sqlserver://192.168.xx.xx:1433/BIB_AY"/>
  <Resource name="sqlserver-gestio3-jtds"  auth="Container" type="javax.sql.DataSource" username="user-gestio3"  password="pwd-gestio3"  maxTotal="50" maxIdle="20" maxWait="10000" timeBetweenEvictionRunsMillis="180000" removeAbandonedOnMaintenance="true" removeAbandonedOnBorrow="true" driverClassName="net.sourceforge.jtds.jdbc.Driver" url="jdbc:jtds:sqlserver://192.168.xx.xx:1433/Cadastre_2019"/>
</Context>

1.2 Fichero src/main/resources/properties/persistence_Gexflow.properites

Este fichero se muestran las tablas (objetos) que vamos a utilizar, y datos de conexión. Hay que tener en cuenta que las acreditaciones (usuario y contraseña) a la BD se indican en el fichero context.xml. Si no se usara JNDI, estas acreditaciones se incluirían en este fichero. A modo de ejmplo se inican pero comentadas.

#-----------------------------------------------------------------------------
#1. General Properties
persistence.unit.name=            PostgressGexflowJPAPU
persistence.provider.class.name=  org.hibernate.jpa.HibernatePersistenceProvider
jta.datasource.name=              PostgressGexflowJPAPU
#non.jta.datasource=               
#mapping.file.names=
#jar.file.urls=
#persistence.unit.root.url
managed.classes=                  openadmin.model.gexflow.edu.basic.Doc;
#
exclude.unlisted.classes=         true	
#shared.cache.mode=
#-----------------------------------------------------------------------------
#Properties not included in datasource
hibernate.dialect=                org.hibernate.dialect.PostgreSQLDialect
hibernate.hbm2ddl.auto=           none
hibernate.show_sql=               true
#-----------------------------------------------------------------------------
#Properties (included in datasource)
#hibernate.cache.provider_class=   org.hibernate.cache.HashtableCacheProvider
#hibernate.connection.url=         jdbc:postgresql://192.168.28.38:5432/suite_gexflow
##hibernate.connection.url=         jdbc:postgresql://localhost:5432/openweb
#hibernate.connection.driver_class=org.postgresql.Driver
#hibernate.connection.username=    user-gexflow
#hibernate.connection.password=    pwd-gexflow
#-----------------------------------------------------------------------------

2. Consulta del documento y firmas.Imprimir documento

Se va a utilizar la clase de java src/main/java/ximodante/rest/utils/GexflowCSV.java 

que tiene los siguientes métodos destacados:

getDocByCSV: para obtener la información del documento y firmas desde la BD

ImprimirDocPorCSV: que se encarga de hacer llamadas a los demás métodos para obtener el documento desde la BD, cargar el documento desde Alfresco (Open CMIS), y escalar el documento e insertarle la información de las firmas.

main: Que nos muestra como hacer pruebas 

Si se quiere saber como utilizar el Open CMIS para consultar documentos de Alfresco se puede ver esta entrada.

Si se quiere saber como manejar documentos PDF con Apache PDFBox, ver esta entrada.

Veamos el listado de la clase GexflowCSV

package ximodante.rest.utils;

import java.awt.Color;
import java.awt.image.BufferedImage;
import java.io.File;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.DateFormatSymbols;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import java.util.Properties;

import org.apache.pdfbox.pdmodel.PDDocument;
import org.apache.pdfbox.pdmodel.PDPage;
import org.apache.pdfbox.pdmodel.PDPageContentStream;
import org.apache.pdfbox.pdmodel.common.PDRectangle;
import org.apache.pdfbox.pdmodel.font.PDType1Font;
import org.apache.pdfbox.pdmodel.graphics.image.LosslessFactory;
import org.apache.pdfbox.pdmodel.graphics.image.PDImageXObject;
import org.apache.pdfbox.util.Matrix;

import ximodante.basic.utils.barcode.BarCodeUtils;
import ximodante.basic.utils.basic.ExecutionTypeEnum;
import ximodante.basic.utils.basic.PropertyUtilsEdu;
import ximodante.basic.utils.cmis.CMISEduImp;
import ximodante.basic.utils.cmis.ICMISEdu;
import ximodante.basic.utils.graphic.GraphicUtils;
import ximodante.modeldao.dao.main.DaoOperationFacadeEdu;
import ximodante.rest.enums.AppEnum;
import ximodante.rest.services.DBService;




public class GexflowCSV {

	//1. Get the document and all the info needed to print int
	@SuppressWarnings("unchecked")
	public static List<Object[]> getDocBycsv (DaoOperationFacadeEdu DAOGexflow, String csv) throws Exception{
		
		
		String SQL=
				"SELECT A.id_documento, A.cvd, " +                //0-1
				"  C.puesto, C.nombre_firmante, C.fecha, " +      //2-4
				"  A1.uuid,A1.nombre_archivo, A1.extension, " +   //5-7
				"  A2.uuid as a21,A2.nombre_archivo as a22, A2.extension as a33, " +   //8-10 
				"  F1.codigo, " +                                 //11
				"  F2.codigo as f21 " +                                  //12
				"FROM gexflow.exp_documento A " + 
				"LEFT JOIN gexflow.mfe_dato_firma B on B.id_documento=A.id_documento " + 
				"LEFT JOIN gexflow.mfe_acto_firma C on C.id_acto_firma=B.id_acto_firma " + 
				"LEFT JOIN gexflow.fwk_documento A1 on A1.id_documento=A.id_documentofwkoo " +  
				"LEFT JOIN gexflow.fwk_documento A2 on A2.id_documento=A.id_documentofwk " + //PDF!
				"LEFT JOIN gexflow.exp_linea_libro_decreto F1 on F1.id_documento=A.id_documento " + 
				"LEFT JOIN gexflow.exp_linea_lib_actas F2 on F2.id_documento=A.id_documento ";
      String WhereClause = " WHERE A.cvd='" + csv.trim() + "'";

		SQL = SQL + WhereClause;
		
		//System.out.println("1  "+ SQL);		
		List<?> lDoc=DAOGexflow.findObjecstByNativeQuery(SQL, 0);
		if (DAOGexflow.isResultOperation()) throw new Exception("Error en consulta SQL \n"+ SQL);
		return (List<Object[]>) lDoc;
	}
	
	//2. Get the first line (decreto or acta)
	public static String getInfoDecret(List<Object[]> lDoc) {
		String infoDec="";
		Object[] aOb=lDoc.get(0);
		if (aOb[11]!=null) infoDec="" +aOb[11]; //Decret
		if (aOb[12]!=null) infoDec="" +aOb[12]; //Acta
		
		return infoDec.trim();
	}
	
	//3. Get the signers (firmantes)
	public static String[] getInfoFirmantes(List<Object[]> lDoc) {
		int nfrm=lDoc.size();
		
		int i=0;
		String[] linFrm=new String[nfrm];
		
		for (Object[] aOb: lDoc) {
			if (aOb[3]==null) linFrm[i]="Sin firmar.";
			linFrm[i]="" + ++i + ". " + 
					aOb[2] + ", " +  // cargo 
					aOb[3] + ", a " + //nombre
					getFechaTexto((Date)aOb[4], new Locale("es"), false, false);
		}	
		return linFrm;
	}
	
	//3. Get the signers (firmantes)
	public static boolean hasFirmantes(List<Object[]> lDoc) {
		Object[] aOb=lDoc.get(0);
		if (aOb[3]==null) return false;
		return true;
	}		
	
	
	//3. Get printing date
	public static String getInfoImpresion() {
		String impr=getFechaTexto(new Date(), new Locale("es"), true, true);
		return impr;
	}
	
	
	//4. Get PDF UUID
	public static String getPdfUuid(List<Object[]> lDoc) {
		Object[] aOb=lDoc.get(0);
		return (String)aOb[8];
	}
	
	//5. Get the PDF File input Stream
	/*
	public static InputStream getCMISContentStream(Properties CMISProps, String partialDocName, String nameOrId) throws Exception {
		return CMISUtils.getContentStreamDocument(CMISUtils.getCMISSession(CMISProps), partialDocName, nameOrId).getStream();
	}
	*/
	public static InputStream getCMISContentStream(ICMISEdu cmis, String partialDocName, String nameOrId) throws Exception {
		return cmis.getContentInputStreamDocument(partialDocName, nameOrId);
	}
	
	//5. Get PDF filename
	public static String getPdfFileName(List<Object[]> lDoc) {
		Object[] aOb=lDoc.get(0);
		//return ((String)aOb[9]).trim() +"."+((String)aOb[10]).trim().toLowerCase();
		return (""+aOb[0]).trim() +"."+((String)aOb[10]).trim().toLowerCase();
	}
			
	/**
	 * Transform a date to text "12 de marzo de 2021"
	 * @param date
	 * @return
	 */
	public static String getFechaTexto (Date date, Locale locale, boolean isDayOfWeek, boolean isHour) {
		SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy");
		String[] a=sdf.format(date).split("-");
		Integer[] ai=new Integer[3];
		for (int i=0; i<a.length; i++) ai[i]=Integer.parseInt(a[i]); 
		
		String monthName=DateFormatSymbols.getInstance(locale).getMonths()[ai[1]-1]; 
		String myDate= ""+ai[0]+" de " + capitalize(monthName) + " de " + ai[2];
		if (isDayOfWeek) {
			DateFormat formatter = new SimpleDateFormat("EEEE", locale);
			String weekDay=formatter.format(date);
			myDate=capitalize(weekDay) +", " + myDate;
		}
		if (isHour) {
			sdf = new SimpleDateFormat("HH:mm");
			myDate=myDate + " " + sdf.format(date);
		}
		return myDate;
	}
	
	/**
	 * Get the document and modify it for having a "Copia Auténtica"
	 * 
	 * call: ImprimirDocPorCSV(daoGexflow, AlbaProps, CMISProps,
	 * CSV,"/home/eduard/Myresources", 19f,8f,22f) ;
	 * 
	 * @param props
	 * @param CSV
	 * @param filePath
	 * @param LMarginMm
	 * @param RMarginMm
	 * @param TMarginMm
	 * @throws Exception
	 */
	/*
	public static String ImprimirDocPorCSV(DaoOperationFacadeEdu DAOGexflow, Properties AlbaProps, Properties CMISProps,
			String CSV, String folderPath, float LMarginMm, float RMarginMm, float TMarginMm) throws Exception {
    */
	public static String ImprimirDocPorCSV(DaoOperationFacadeEdu DAOGexflow, Properties props, ICMISEdu cmis,
			String CSV, String folderPath, float LMarginMm, float RMarginMm, float TMarginMm) throws Exception {
    
		// 0. Parameters
		float mm2point = 2.834f; //Comversion mm to points
		float A4WMm = 210f; // A4 width in mm.

		float scaleX = 0.4f; // For Barcode
		float scaleY = 0.45f; // For Barcode

		float factor = (A4WMm - LMarginMm - RMarginMm) / A4WMm; // Scale of document into the frame

		String sedeURL = props.getProperty("gexflow.csv.url");

		// 1. Get info of the document
		List<Object[]> lDoc = getDocBycsv(DAOGexflow, CSV);
		if (lDoc==null || lDoc.isEmpty()) return "";
		// PDDocument document = PDDocument.load(new File(filePath));

		// 2. Load the document form alfresco
		PDDocument document = PDDocument
		//		.load(getCMISContentStream(CMISProps, getPdfUuid(lDoc), "id"));
				.load(getCMISContentStream(cmis, getPdfUuid(lDoc), "id"));
		
		// If has signatures then put the csv and modify document with signatures 
		if (hasFirmantes(lDoc)) {
			int nPage = 1;
			int nPages = document.getDocumentCatalog().getPages().getCount();

			// 3. Apply changes for every page
			// PDPage page = document.getDocumentCatalog().getPages().get(0);

			for (PDPage page : document.getDocumentCatalog().getPages()) {

				// 4. Get dimensions of crop box
				PDRectangle mediaBox = page.getMediaBox();
				float width = mediaBox.getWidth();
				float height = mediaBox.getHeight();
				// System.out.println("width="+width + " height="+height);
				float dif=width - 595.30396f;
				//if ((width - 595.30396f) > 1)
				if (dif > 600)
					throw new Exception("Invalid paper size");

				// 5. Draw a black rectangle APPEND-> For the first time!!!)
				PDPageContentStream cs = new PDPageContentStream(document, page, PDPageContentStream.AppendMode.APPEND,
						false, false);
				cs.addRect(0, height, width, -height); // APPEND
				cs.setLineWidth(0.1f * mm2point);
				cs.setStrokingColor(Color.RED);
				cs.stroke();
				// cs.close();

				// 6. Add the CSV barcode
				float heighToRest = 390;
				// BarCode Code128
				BufferedImage bcImage = BarCodeUtils.writeBarcodeCode128Zxing2File(CSV, 1000, 80, null);
				// Rotate image
				bcImage = GraphicUtils.rotate(bcImage, -90);
				// Convert image to PDF object
				PDImageXObject pdImage = LosslessFactory.createFromImage(document, bcImage);
				cs.drawImage(pdImage, -50, height - heighToRest, pdImage.getWidth() * scaleX,
						pdImage.getHeight() * scaleY);

				// 6.1. Remove borders of bar code using white rectangle
				cs.addRect(-50, height - heighToRest, pdImage.getWidth() * scaleX, pdImage.getHeight() * scaleY); // APPEND
				cs.setLineWidth(1f * mm2point);
				cs.setStrokingColor(Color.WHITE);
				cs.stroke();
				cs.close();

				// 7. Rotate the page and print CSV text (From now on , use PREPEND and NOT
				// APPEND)
				cs = new PDPageContentStream(document, page, PDPageContentStream.AppendMode.PREPEND, false, false);
				cs.beginText();
				Matrix matrixkk = Matrix.getRotateInstance(Math.toRadians(90), 0, 0);
				cs.setTextMatrix(matrixkk);
				cs.setFont(PDType1Font.HELVETICA, 13);
				cs.newLineAtOffset(530, 6); //
				cs.showText(CSV);

				// 8. Print "Copia auténtica..."
				String rollet0 = "Copia auténtica. ";
				String rollet1 = "Mediante el código impreso puede comprobar la validez de la firma";
				String rollet2 = " electrónica en la URL:  ";

				cs.setNonStrokingColor(Color.DARK_GRAY);
				cs.setFont(PDType1Font.HELVETICA_BOLD_OBLIQUE, 10);
				cs.newLineAtOffset(-515, 30); //
				cs.showText(rollet0);
				cs.setFont(PDType1Font.HELVETICA_OBLIQUE, 10);
				cs.showText(rollet1);

				cs.newLineAtOffset(0, -15); //
				cs.showText(rollet2);

				cs.setFont(PDType1Font.HELVETICA_BOLD_OBLIQUE, 11);
				cs.showText(sedeURL);
				cs.endText();

				// 9. Print "Fecha de impresion..."
				// 9.1 undo the rotation
				matrixkk = Matrix.getRotateInstance(Math.toRadians(0), 0, 0);
				cs.setFont(PDType1Font.HELVETICA_BOLD_OBLIQUE, 10);
				cs.beginText();
				cs.setTextMatrix(matrixkk);
				String fechita1 = "Fecha de impresión: ";
				// String fechita2="Martes, 4 de Octubre de 2021 7:38";
				String fechita2 = getInfoImpresion();
				String paginitas = "Página " + nPage++ + " de " + nPages;
				cs.newLineAtOffset(5, -15); //
				cs.showText(fechita1);
				cs.setFont(PDType1Font.HELVETICA_OBLIQUE, 10);
				cs.showText(fechita2);

				cs.newLineAtOffset(500, 0); //
				cs.showText(paginitas);

				cs.endText();
				// end undo rotation
				cs.close();

				// 10. Scale the page
				cs = new PDPageContentStream(document, page, PDPageContentStream.AppendMode.PREPEND, false, false);
				Matrix matrix = Matrix.getScaleInstance(factor, factor);
				cs.transform(matrix);
				cs.close();

				// 11. Trasllate the page
				float trX = LMarginMm * mm2point;
				float trY = (1 - factor) * height - TMarginMm * mm2point;
				// System.out.println("trX="+trX + " trY="+trY);
				cs = new PDPageContentStream(document, page, PDPageContentStream.AppendMode.PREPEND, false, false);
				Matrix matrix1 = Matrix.getTranslateInstance(trX, trY);
				cs.transform(matrix1);
				cs.close();

				// 11. Write DECRETO and signatures
				cs = new PDPageContentStream(document, page, PDPageContentStream.AppendMode.PREPEND, false, false);
				cs.setFont(PDType1Font.HELVETICA_BOLD, 9);
				cs.beginText();

				// 11.1. Decreto / acta
				cs.newLineAtOffset(trX + 2 * mm2point, height - 5 * mm2point);
				String sDecret = getInfoDecret(lDoc);
				// If it is not Decreto or Acta then doen't print this line
				if (sDecret.trim().length() > 5) {
					cs.showText(getInfoDecret(lDoc)); // Decret nº 2021 -2

					// 11.2. FIRMADO
					cs.newLineAtOffset(0, -11);
				}
				cs.showText("FIRMADO");

				// 11.3 Firmantes
				cs.setFont(PDType1Font.HELVETICA, 7);
				for (String firmantes : getInfoFirmantes(lDoc)) {
					cs.newLineAtOffset(0, -7);
					cs.showText(firmantes);
				}
				cs.endText();
				cs.close(); // don't forget that one!

			}
		}
		//String myFileName = folderPath + "/" + getPdfFileName(lDoc).replace(".pdf", ".esc" + ((int) (100 * factor)) + ".pdf");
		String myFileName = folderPath + "/" + getPdfFileName(lDoc);
		System.out.println(myFileName);
		File myFile = new File(myFileName);
		// myFile.createNewFile();
		document.save(myFile);
		document.close();
		return myFileName;
	}

	
	/**
	 * Transform the first letter to uppercase
	 * @param s
	 * @return
	 */
	public static String capitalize(String s) {
		return s.substring(0, 1).toUpperCase() + s.substring(1);
	}
	
	public static void main(String[] args) {
		int month=1;
		
		System.out.println(DateFormatSymbols.getInstance(new Locale("es")).getMonths()[month-1]);
		//String str=DateFormatSymbols.getInstance(new Locale("es")).getMonths()[month-1];
		//String cap = capitalize(str);
		
		System.out.println(getFechaTexto(new Date(), new Locale("es"), true, true));
		//for (String s: getInfoImpresion()) System.out.print(s);

		
		try {
			//String fileName = "/home/eduard/MyResources/decret-2021-0496.pdf";
			// Scale01_SOLS_PROVA(fileName,19f,8f,22f);

			// ======================================================================================
			ExecutionTypeEnum myExec=ExecutionTypeEnum.NO_JAR;
			
			Properties CMISProps=PropertyUtilsEdu.getProperties(myExec, "app_encrypt");
			ICMISEdu cmis=new CMISEduImp(CMISProps);
			
			DaoOperationFacadeEdu DaoGexflow=DBService.getNewDaoControl(ExecutionTypeEnum.WAR, AppEnum.GEXFLOW);
			String myFolder = "/home/eduard/MyResources";
						
			String CSV ="MF1XXXXXXXXzxt5Ehzc8JCz4ubQ3Uf*Fvp2AGPQ";
			//String CSV = "IB6wXUxR+45ZPLqPBSwuA9YlSdC5U6K989OnIKg";
			//ImprimirDocPorCSV(daoGexflow, AlbaProps, CMISProps, CSV, myFolder, 19f, 8f, 22f);
			ImprimirDocPorCSV(DaoGexflow, CMISProps, cmis, CSV, myFolder, 19f, 8f, 22f);

			CSV = "e-Aqhe5yWHNm5PK*YtXXXXXXXXfHwEXX71miw7w"; // Acta
			//ImprimirDocPorCSV(daoGexflow, AlbaProps, CMISProps, CSV, myFolder, 19f, 8f, 22f);
			ImprimirDocPorCSV(DaoGexflow, CMISProps, cmis, CSV, myFolder, 19f, 8f, 22f);

			CSV = "XZW9VdYdgm3v3e-9uMZxFXXXXXXXXGcKqMb3MsQ"; // Decret
			//ImprimirDocPorCSV(daoGexflow, AlbaProps, CMISProps, CSV, myFolder, 19f, 8f, 22f);
			ImprimirDocPorCSV(DaoGexflow, CMISProps, cmis, CSV, myFolder, 19f, 8f, 22f);

			CSV = "JveQte+exrJZ09xMw7XXXXXXXXvCJLPT0iwFpOw"; // 6 firmes !!
			ImprimirDocPorCSV(DaoGexflow, CMISProps, cmis, CSV, myFolder, 19f, 8f, 22f);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}

}


Comentarios

Entradas populares de este blog

1. Instalación del servidor Tomcat y VSFTP , certificados y librerias

7. Comprobaciones

8.Eclipse. Cambio de configuración de los servidores