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
Publicar un comentario