Java tutorial
/** * TNTConcept Easy Enterprise Management by Autentia Real Bussiness Solution S.L. * Copyright (C) 2007 Autentia Real Bussiness Solution S.L. * * This program is free software: you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * the Free Software Foundation, either version 3 of the License. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with this program. If not, see <http://www.gnu.org/licenses/>. */ package com.autentia.tnt.bill.migration.support; import java.io.LineNumberReader; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Date; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import com.autentia.tnt.bill.migration.BillToBillPaymentMigration; public class OriginalInformationRecoverer { private static final Log log = LogFactory.getLog(OriginalInformationRecoverer.class); /** * Recupera la suma total de todos los conceptos de todas las facturas del tipo que se envie por parametro * @param billType tipo de factura */ public static double getTotalFacturasOriginal(String billType) throws Exception { Connection con = null; PreparedStatement pstmt = null; ResultSet rs = null; LineNumberReader file = null; double result = -1; try { log.info("RECOVERING TOTAL FACTURAS " + billType + " ORIGINALES"); // connect to database Class.forName(BillToBillPaymentMigration.DATABASE_DRIVER); con = DriverManager.getConnection(BillToBillPaymentMigration.DATABASE_CONNECTION, BillToBillPaymentMigration.DATABASE_USER, BillToBillPaymentMigration.DATABASE_PASS); //NOSONAR con.setAutoCommit(false); // DATABASE_PASS vacio String sql = "SELECT sum((bb.units*bb.amount)*(1+(bb.iva/100))) as total from Bill b left join BillBreakDown bb on b.id=bb.billId, Organization o, Project p where b.projectId = p.id and p.organizationId = o.id and b.billType= ?"; pstmt = con.prepareStatement(sql); pstmt.setString(1, billType); rs = pstmt.executeQuery(); while (rs.next()) { result = rs.getDouble(1); log.info("\t" + result); } con.commit(); } catch (Exception e) { log.error("FAILED: WILL BE ROLLED BACK: ", e); if (con != null) { con.rollback(); } } finally { cierraFichero(file); liberaConexion(con, pstmt, rs); } return result; } /** * Recupera la suma total de todos los conceptos de cada una de las facturas cuyo tipo se envia por parametro * @param billType tipo de factura */ public static double[] getImporteFacturaOriginal(String billType) throws Exception { Connection con = null; PreparedStatement pstmt = null; ResultSet rs = null; LineNumberReader file = null; double[] result = new double[0]; try { log.info("RECOVERING IMPORTE FACTURAS " + billType + " ORIGINALES"); // connect to database Class.forName(BillToBillPaymentMigration.DATABASE_DRIVER); con = DriverManager.getConnection(BillToBillPaymentMigration.DATABASE_CONNECTION, BillToBillPaymentMigration.DATABASE_USER, BillToBillPaymentMigration.DATABASE_PASS); //NOSONAR con.setAutoCommit(false); //DATABASE_PASS vacio. String sql = "SELECT sum((bb.units*bb.amount)*(1+(bb.iva/100))) as total from Bill b left join BillBreakDown bb on b.id=bb.billId, Organization o, Project p where b.projectId = p.id and p.organizationId = o.id and b.billType= ? group by b.id order by total"; pstmt = con.prepareStatement(sql); rs = pstmt.executeQuery(); pstmt.setString(1, billType); rs.last(); result = new double[rs.getRow()]; rs.beforeFirst(); int counter = 0; while (rs.next()) { result[counter] = rs.getDouble(1); log.info("\t" + result[counter]); counter++; } con.commit(); } catch (Exception e) { log.error("FAILED: WILL BE ROLLED BACK: ", e); if (con != null) { con.rollback(); } } finally { cierraFichero(file); liberaConexion(con, pstmt, rs); } return result; } /** * Recupera la fecha de pago o cobro de cada una de las facturas cuyo tipo se envia por parametro * @param billType tipo de factura */ public static Date[] getFechaFacturaOriginal(String billType) throws Exception { Connection con = null; PreparedStatement pstmt = null; ResultSet rs = null; LineNumberReader file = null; Date[] result = new Date[0]; try { log.info("RECOVERING FECHAS " + billType + " ORIGINALES"); // connect to database Class.forName(BillToBillPaymentMigration.DATABASE_DRIVER); con = DriverManager.getConnection(BillToBillPaymentMigration.DATABASE_CONNECTION, BillToBillPaymentMigration.DATABASE_USER, BillToBillPaymentMigration.DATABASE_PASS); //NOSONAR con.setAutoCommit(false); // DATABASE_PASS vacia String sql = "SELECT date_add(creationDate, INTERVAL expiration DAY) as date FROM Bill B where billType = ? order by date"; pstmt = con.prepareStatement(sql); pstmt.setString(1, billType); rs = pstmt.executeQuery(); rs.last(); result = new Date[rs.getRow()]; rs.beforeFirst(); int counter = 0; while (rs.next()) { result[counter] = rs.getDate(1); log.info("\t" + result[counter]); counter++; } con.commit(); } catch (Exception e) { log.error("FAILED: WILL BE ROLLED BACK: ", e); if (con != null) { con.rollback(); } } finally { cierraFichero(file); liberaConexion(con, pstmt, rs); } return result; } private static void liberaConexion(Connection con, PreparedStatement stmt, ResultSet rs) { if (rs != null) { try { rs.close(); } catch (SQLException sqlex) { log.error("Error al liberar el ResultSet", sqlex); } } if (stmt != null) { try { stmt.close(); } catch (SQLException sqlex) { log.error("Error al liberar el Statement", sqlex); } } if (con != null) { try { con.close(); } catch (SQLException sqlex) { log.error("Error al liberar la conexin", sqlex); } } } private static void cierraFichero(LineNumberReader f) { try { if (f != null) { f.close(); } } catch (Exception ex) { log.error("Error al cerrar fichero", ex); } } }