<%@ page contentType="application/vnd.ms-excel" import="jxl.Workbook" import="jxl.write.Label" import="jxl.write.WritableSheet" import="jxl.write.WritableWorkbook" %> <%@ include file="/commons/definitions.jsp" %> <% response.setHeader("Content-Disposition", "attachment; filename=\"Cosmoprof 2010 Exhibitors.xls\""); String sCurrPage = request.getParameter("p")!=null && !"".equals(request.getParameter("p").trim()) ? request.getParameter("p") : "1"; int currPage = 1; try{ currPage = Integer.parseInt(sCurrPage); } catch(Exception e){} StringBuffer sql = new StringBuffer("SELECT trim( a.COMPANYNAME ) COMPANYNAME, trim( a.COMPANYPAVILLION ) COMPANYPAVILLION, CATL_COMPANYCUSTOM1 "); sql.append(" , s.descr DESCR, s.descr_eng DESCR_EN "); sql.append(" , sett.descr SETTDESCR "); sql.append(" , sett.descr_en SETTDESCR_EN "); sql.append(" FROM ( "); StringBuffer sqlCondition = new StringBuffer(""); if(!StringUtils.isBlank(request.getParameter("txtSearch"))){ sqlCondition.append(sqlCondition.length()==0 ? " where " : " and "); sqlCondition.append(" CATL_COMPANYNAME LIKE '%"+SQLEncode(request.getParameter("txtSearch"))+"%'"); } if(!StringUtils.isBlank(request.getParameter("pavillionSearch"))) { sqlCondition.append(sqlCondition.length()==0 ? " where " : " and "); sqlCondition.append(" CATL_COMPANYPAVILLION = '"+request.getParameter("pavillionSearch")+"'"); } if(!StringUtils.isBlank(request.getParameter("sectorSearch"))) { sqlCondition.append(sqlCondition.length()==0 ? " where " : " and "); sqlCondition.append(" CATL_COMPANYCUSTOM2 = '"+request.getParameter("sectorSearch")+"'"); } StringBuffer sql1 = new StringBuffer("SELECT substring( CATL_COMPANYNAME , 2 ) COMPANYNAME, CATL_COMPANYPAVILLION COMPANYPAVILLION, CATL_COMPANYCOUNTRY, CATL_COMPANYCUSTOM2, CATL_COMPANYCUSTOM1\n"); sql1.append(" FROM sog_catalogue_companies\n"); // sql1.append(sqlCondition); sql1.append(sqlCondition.length()==0 ? " where " : " and "); sql1.append(" CATL_COMPANYNAME LIKE '#%'\n"); StringBuffer sql2 = new StringBuffer("SELECT CATL_COMPANYNAME COMPANYNAME, CATL_COMPANYPAVILLION COMPANYPAVILLION, CATL_COMPANYCOUNTRY, CATL_COMPANYCUSTOM2, CATL_COMPANYCUSTOM1\n"); sql2.append(" FROM sog_catalogue_companies\n"); // sql2.append(sqlCondition); sql2.append(sqlCondition.length()==0 ? " where " : " and "); sql2.append(" CATL_COMPANYNAME NOT LIKE '#%'\n"); sql.append(sql1.toString()); sql.append(" union\n"); sql.append(sql2.toString()); sql.append(" )a\n"); sql.append(" left join sog_anag_state s on s.id = a.CATL_COMPANYCOUNTRY\n"); sql.append(" left join TSettoriAppartenenza sett on sett.id = a.CATL_COMPANYCUSTOM2\n"); sql.append(" ORDER BY trim( a.COMPANYNAME ) , trim( a.COMPANYPAVILLION )"); //writeLog(sql.toString()); LinkedList exhibitors = getList(sql.toString().replace("\n", " "), "COMPANYNAME,COMPANYPAVILLION,DESCR,DESCR_EN,SETTDESCR,SETTDESCR_EN,CATL_COMPANYCUSTOM1"); sql.delete(0, sql.length()); sqlCondition.delete(0, sqlCondition.length()); /* sql.append("select distinct CATL_COMPANYPAVILLION COMPANYPAVILLION from sog_catalogue_companies\n"); sql.append(" where CATL_COMPANYPAVILLION is not null\n"); sql.append(" ORDER BY COMPANYPAVILLION\n"); Map pavillionsMap = new LinkedHashMap (); LinkedList pavillions = getList(sql.toString().replace("\n", " "), "COMPANYNAME,COMPANYPAVILLION"); sql.delete(0, sql.length()); sqlCondition.delete(0, sqlCondition.length()); for (Iterator it = pavillions.iterator(); it.hasNext(); ) { ListItem item = it.next(); pavillionsMap.put(item, (String) pavillions.get(item)); } sql.append("select id, descr, descr_en from TSettoriAppartenenza\n"); sql.append(" ORDER BY descr \n"); //writeLog("SQL "+sql.toString()); Map sectorsMap = new LinkedHashMap (); LinkedList sectors = getList(sql.toString().replace("\n", " "), "ID,DESCR, DESCR_EN"); sql.delete(0, sql.length()); sqlCondition.delete(0, sqlCondition.length()); for (Iterator it = sectors.iterator(); it.hasNext(); ) { ListItem item = it.next(); sectorsMap.put(item, (String) sectors.get(item)); } */ //scrivo WritableWorkbook w = Workbook.createWorkbook(response.getOutputStream()); WritableSheet s = w.createSheet("Espositori", 0); // scrivo le etichette delle colonne sulla prima riga int i=0;//conta le colonne s.addCell(new Label(i++, 0, resources.getString("label.eld.companyTitle"))); s.addCell(new Label(i++, 0, resources.getString("label.el.countryTitle"))); s.addCell(new Label(i++, 0, resources.getString("label.wl.selling_product_sector"))); s.addCell(new Label(i++, 0, resources.getString("label.el.pavillionTitle")+"/Stand" )); // scrivo i dati int j=1;//conta le righe for (Iterator exhibitorsIt = exhibitors.iterator(); exhibitorsIt.hasNext(); ) { ListItem item = exhibitorsIt.next(); i=0;//reset delle colonne s.addCell(new Label(i++, j, item.getProperty("COMPANYNAME"))); s.addCell(new Label(i++, j, item.getProperty("DESCR", "", getLocale(request)))); s.addCell(new Label(i++, j, item.getProperty("SETTDESCR", "", getLocale(request)))); s.addCell(new Label(i++, j, item.getProperty("COMPANYPAVILLION") + " / " + item.getProperty("CATL_COMPANYCUSTOM1") )); j++; } w.write(); w.close(); %>