Java tutorial
/*$Id: KostaReportGeneratorH2.java 12166 2008-12-08 10:34:22Z jens $*/ /* **************************************************************************** * * * (c) Copyright 2005 ABM-utvikling * * * * 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 2 of the License, or (at your * * option) any later version. * * * * 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. http://www.gnu.org/licenses/gpl.html * * * **************************************************************************** */ package no.abmu.abmstatistikk.annualstatistic.util; import java.io.FileOutputStream; import java.io.IOException; import java.util.ArrayList; import java.util.Arrays; import java.util.Calendar; import java.util.Date; import java.util.GregorianCalendar; import java.util.HashMap; import java.util.Iterator; import java.util.List; import java.util.Map; import java.util.Properties; import net.sf.hibernate.type.DateType; import net.sf.hibernate.type.StringType; import net.sf.hibernate.type.Type; import no.abmu.abmstatistikk.annualstatistic.domain.Answer; import no.abmu.abmstatistikk.annualstatistic.service.AnnualStatisticService; import no.abmu.common.excel.ExcelTable; import no.abmu.common.excel.TableModel; import no.abmu.organisationregister.domain.OrganisationUnit; import no.abmu.organisationregister.service.OrganisationUnitService; import no.abmu.util.hibernate2.spring.ApplicationContextLoaderH2; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.springframework.context.ApplicationContext; /** * KostaReportGenerator. * * @author Erik Romson, erik@zenior.no * @author $Author:jens $ * @version $Rev: 12166 $ * $Date: 2008-12-08 11:34:22 +0100 (Mon, 08 Dec 2008) $ * copyright ABM-Utvikling * @since 2005-02-23 (Rev. 1136) */ public class KostaReportGeneratorH2 { public static final String HIB_FILE_KEY = "hibernate.property.file"; public static final String HIB_DIALECT_KEY = "hibernate.dialect"; /* 013", "Bestand: fagbker for barn", true, false, true, false, true, true, true); 016", "Bestand: skjnnlitteratur for barn", true, false, true, false, true, true, true); 019", "Bestand: fagbker voksne", true, true, false, true, true, true, true); 022", "Bestand: skjnnlitteratur for voksne"067 - "Utln bker barn faglitteratur" 067", "Utln bker barn faglitteratur", true, false, true, false, true, true, false); 068 - "Utln bker barn skjnnlitteratur" 069 - "Utln bker voksne - faglitteratur" 070 - "Utln bker voksne - skjnnlitteratur" 071 - "Utln: Musikkinnspillinger for barn" 072 - "Utln: Musikkinnspillinger for voksne" 073 - "Utln: Lydbker for barn" 074 - "Utln: Lydbker for voksne" 075 - "Utln: Lysbildeserier/billedbnd, spill" 076 - "Utln: Video/DVD for barn" 077 - "Utln: Video/DVD for voksne" 078 - "Utln: CD-ROM for barn" 079 - "Utln: CD-ROM for voksne" 080 - "Utln: AV-medier" 081 - "Utln: Annet" */ public static final Object[][] COLUMN_FORMAT = { { "Region", new int[] { 0 } }, { "KK_BIBLIOTEK_Totalt_utln_alle_medier", new int[] { 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81 } }, { "KK_BIBLIOTEK_Bokutln", new int[] { 67, 68, 69, 70 } }, { "KK_BIBLIOTEK_Bokutln_barnelitteratur", new int[] { 67, 68 } }, { "KK_BIBLIOTEK_Bokutln_voksenlitteratur", new int[] { 69, 70 } }, { "KK_BIBLIOTEK_Utln_andre_medier_i_alt", new int[] { 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81 } }, { "KK_BIBLIOTEK_Utln_musikkinnspillinger", new int[] { 71, 72 } }, { "KK_BIBLIOTEK_Utln_lydbker", new int[] { 73, 74 } }, { "KK_BIBLIOTEK_Utln_video", new int[] { 76, 77 } }, { "KK_BIBLIOTEK_Utln_CD-ROM", new int[] { 78, 79 } }, { "KK_BIBLIOTEK_Bokbestand_i_alt", new int[] { 13, 16, 19, 22 } }, { "KK_BIBLIOTEK_Bokbestand_barnebker", new int[] { 13, 16 } }, { "KK_BIBLIOTEK_Bokbestand_voksenbker", new int[] { 19, 22 } }, { "KK_BIBLIOTEK_rsverk_i_alt", new int[] {} } }; private static final Log logger = (Log) LogFactory.getLog(KostaReportGeneratorH2.class); private AnnualStatisticService annualStatisticService; private OrganisationUnitService organisationUnitService; private String dialect; public KostaReportGeneratorH2() throws IOException, IllegalAccessException { Properties properties = new Properties(); String hibFile = System.getProperty(HIB_FILE_KEY, "conf/hibernate/hibernate.properties"); properties.load( ClassLoader.getSystemClassLoader().getResourceAsStream("conf/hibernate/hibernate.properties")); // properties.load(ClassLoader.getSystemClassLoader().getResourceAsStream(hibFile)); dialect = (String) properties.get(HIB_DIALECT_KEY); if (dialect == null) { throw new IllegalAccessException("The file " + hibFile + " had no key " + HIB_DIALECT_KEY); } System.getProperties().setProperty("applicationContextConfig", "conf/spring/application-context.xml"); System.getProperties().setProperty("config_locs_cp", "conf/spring/appContext-util.xml," + "conf/spring/appContext-configuration.xml," + "conf/spring/appContext-db-dataLayer.xml," + "conf/spring/appContext-service-orgRegister.xml," + "conf/spring/appContext-service-annualStatistic.xml," + "conf/spring/appContext-service-user.xml"); /* + "conf/spring/appContext-security.xml" */ System.getProperties().setProperty("config_locs", ""); ApplicationContextLoaderH2.getInstance().init(); ApplicationContext context = ApplicationContextLoaderH2.getInstance().getApplicationContext(); annualStatisticService = (AnnualStatisticService) ApplicationContextLoaderH2.getInstance() .getApplicationContext().getBean("AnnualStatisticService"); organisationUnitService = (OrganisationUnitService) ApplicationContextLoaderH2.getInstance() .getApplicationContext().getBean("organisationUnitService"); } HashMap getSortedMuncipalityDataValues(List dataValues) { Object[] munNumAndOrgIds = organisationUnitService.find("SELECT muncipality.number,orgUnit.id " + "FROM " + OrganisationUnit.class.getName() + " AS orgUnit " + "join orgUnit.muncipalityRelation.muncipality muncipality " + "order by muncipality.number"); HashMap retMap = new HashMap(munNumAndOrgIds.length); int munId = -1; DataValue dataValue = null; for (int i = 0; i < munNumAndOrgIds.length; i++) { Object[] munNumAndOrgId = (Object[]) munNumAndOrgIds[i]; if (munId != ((Integer) munNumAndOrgId[0]).intValue()) { munId = ((Integer) munNumAndOrgId[0]).intValue(); dataValue = new DataValue(munId); retMap.put(munNumAndOrgId[1], dataValue); dataValues.add(dataValue); } else { retMap.put(munNumAndOrgId[1], dataValue); } } return retMap; } void fillInData(Map map, int year, String schemaShortName) { //suppose it is not thread safe... Calendar calendar = GregorianCalendar.getInstance(); //get 1:st of june for the intended year, start and stop date is always //at the 1:st of january and last of december? calendar.set(year, 6, 1); Date date = calendar.getTime(); Object[] arr = annualStatisticService .find("select answer.report.organisationid,answer.field.name,answer.value" + " from " + Answer.class.getName() + " answer " + " inner join fetch answer.report as report " + " join fetch answer.field as field " + " WHERE " + " field.name IN ('013','016','019','022','067','068','069'," + "'070','071','071','072','073','074','075','076','077','078','079','080','081') " + // " field.name between '067' and '081' " + " and " + // " report.organisationid=? and "+ " report.schema.shortname=? and " + " report.schema.startdate <= ? and " + " report.schema.stopdate >= ? ", new Object[] { schemaShortName, date, date }, new Type[] { new StringType(), new DateType(), new DateType() }); List list = Arrays.asList(arr); for (int j = 0; j < list.size(); j++) { Object[] arr1 = (Object[]) list.get(j); Long orgUnitId = (Long) arr1[0]; int fieldName = Integer.parseInt((String) arr1[1]); int value = Integer.parseInt((String) arr1[2]); DataValue dataValue = (DataValue) map.get(orgUnitId); if (dataValue == null) { throw new IllegalArgumentException(orgUnitId.toString()); } dataValue.addFieldValue(fieldName, value); } } public void generate() throws IOException { List dataValues = new ArrayList(2000); Map map = getSortedMuncipalityDataValues(dataValues); fillInData(map, 2003, "folkbib"); Iterator iterator = map.values().iterator(); TableModel tableModel = new TableModel(); for (int cidx = 0; cidx < COLUMN_FORMAT.length; cidx++) { Object[] arr = (Object[]) COLUMN_FORMAT[cidx]; String headerName = (String) arr[0]; tableModel.setHeaderLabel(cidx, headerName); } for (int i = 0; i < dataValues.size(); i++) { DataValue dataValue = (DataValue) dataValues.get(i); tableModel.setValue(i, 0, ("0" + String.valueOf(dataValue.muncipalityId * 100))); for (int cidx = 1; cidx < COLUMN_FORMAT.length; cidx++) { Object[] arr = (Object[]) COLUMN_FORMAT[cidx]; int[] fields = (int[]) arr[1]; int result = 0; for (int j = 0; j < fields.length; j++) { int field = fields[j]; result += dataValue.getFieldValue(field); } tableModel.setValue(i, cidx, String.valueOf(result)); } } ExcelTable excelTable = new ExcelTable(tableModel); java.io.FileOutputStream fileOutputStream = new FileOutputStream("test4.xls"); fileOutputStream.write(excelTable.getExcelDocumentAsBinaryData()); fileOutputStream.flush(); fileOutputStream.close(); } public static void main(String[] args) throws IllegalAccessException, IOException { KostaReportGeneratorH2 kostaReportGenerator = new KostaReportGeneratorH2(); kostaReportGenerator.generate(); } /** * DataValue. * */ class DataValue { private int muncipalityId; private int[] fields = new int[81 + 1]; public DataValue(int muncipalityId) { this.muncipalityId = muncipalityId; } public void addFieldValue(int fieldId, int fieldValue) { fields[fieldId] += fieldValue; } public int getFieldValue(int fieldId) { return fields[fieldId]; } public String toString() { StringBuffer sBuffer = new StringBuffer(300); for (int i = 0; i < fields.length; i++) { int field = fields[i]; sBuffer.append((i + 67) + ":" + field + ", "); } return "DataValue{" + "muncipalityId=" + muncipalityId + ", fields=" + sBuffer.toString() + "}"; } } }