exportservlet.java

发泄网！ 发泄网！ 发泄网！ 发泄网！ 发泄网！ 发泄网！ 发泄网！ 发泄网！

	/* CRMS, customer relationship management system
	Copyright (C) 2003  Service To Youth Council

	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.

	You should have received a copy of the GNU General Public License
	along with this program; if not, write to the Free Software
	Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

	For further information contact the SYC ICT department on [e-mail]
	98 Kermode Street
	North Adelaide
	South Australia
	SA 5006 
	[phone]
	*/
/*
 * FileUploadServlet.java
 *
 * Created on 5 May 2003, 15:58
 */

package crms.servlet;

import java.io.*;
import java.net.*;

import javax.servlet.*;
import javax.servlet.http.*;
import org.apache.commons.fileupload.*;

import java.util.*;
import org.apache.log4j.*;
import crms.module.FileAttachmentModule;
import crms.util.*;
import crms.dao.*;
import crms.vo.*;

import java.sql.*;

/** Download a file from within CRMS.
 *
 * @author  dmurphy
 */
public class ExportServlet extends HttpServlet {
	
	static Logger logger = Logger.getLogger(ExportServlet.class);
	
	public static String PARAM_USER = "user";
	public static String PARAM_ATTACHMENT_TYPE = "type";
	public static String PARAM_ATTACHMENT_ID = "id";
	
	private ContactDAO contactDAO = DAOFactory.getInstance().getContactDAO();   
	private CallDAO callDAO = DAOFactory.getInstance().getCallDAO();   
	
	/** Initializes the servlet.
	 */
	public void init() throws ServletException {
	}
	
	/** Destroys the servlet.
	 */
	public void destroy() {
		
	}
	/** Handles the HTTP <code>GET</code> method.
	 * @param request servlet request
	 * @param response servlet response
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response)
	throws ServletException, IOException {
		processRequest(request, response);
	}
	
	/** Handles the HTTP <code>POST</code> method.
	 * @param request servlet request
	 * @param response servlet response
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response)
	throws ServletException, IOException {
		processRequest(request, response);
	}
	
	/** Returns a short description of the servlet.
	 */
	public String getServletInfo() {
		return "Short description";
	}
	
	
	/** Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods.
	 * @param request servlet request
	 * @param response servlet response
	 */
	protected void processRequest(HttpServletRequest request, HttpServletResponse response)
	throws ServletException, IOException {

		BufferedOutputStream out = new BufferedOutputStream(response.getOutputStream());
				String mimeType = null;
		File file = null;
		String filename = null;
		
		String user = request.getParameter(PARAM_USER);
		String attachID = request.getParameter(PARAM_ATTACHMENT_ID);
		EntityType attachType = (EntityType) AbstractCode.decode(request.getParameter(PARAM_ATTACHMENT_TYPE), EntityType.class);

		filename = attachType.getCode() + ".csv";
		response.addHeader("Content-Disposition", "attachment; filename=\"" + filename+ "\"");
		response.setContentType("text/csv");
		
		if (attachType.getCode().equals(EntityType.CONTACT_EXPORT.getCode())) {
			String l = request.getParameter("l");
			String f = request.getParameter("f");
			String c = request.getParameter("c");
			
			handleContactExport(out, user, f, l, c);
		} else if (attachType.getCode().equals(EntityType.CALL_EXPORT.getCode())) {
			String compid = request.getParameter("compid");

			int CompanyID = -1;
			try {
				CompanyID = Integer.parseInt(compid);
			} catch (Exception e){ /* ignore parse errors */ }

			handleCallExport(out, user, CompanyID);
		} else if (attachType.getCode().equals(EntityType.REMINDER_EXPORT.getCode())) {
			handleReminderExport(out, user);
		} else if (attachType.getCode().equals(EntityType.COMPANY_EXPORT.getCode())) {
			handleCompanyExport(out, user);
		}

		out.flush();
		out.close();

	}
	
	private void handleContactExport(BufferedOutputStream out, String user, String f, String l, String c) throws IOException {
		Connection con = null;
		ResultSet rs = null;
		Statement stmt = null;
	
		String sql = "SELECT c.*, "
				+" cm.\"CompanyName\" AS \"CompanyName\", \n"
				+" d1.\"ContactValue\" AS \"home-phone\", \n"
				+" d2.\"ContactValue\" AS \"home-postcode\", \n"
				+" d3.\"ContactValue\" AS \"home-address\", \n"
				+" d4.\"ContactValue\" AS \"home-suburb\", \n"
				+" d5.\"ContactValue\" AS \"home-state\", \n"
				+" d6.\"ContactValue\" AS \"home-country\", \n"
				+" d7.\"ContactValue\" AS \"title\", \n"
				+" l.\"Title\" as \"LocationTitle\", l.*, \n"
				+" d17.\"ContactValue\" AS \"cont-email\", \n"
				+" d18.\"ContactValue\" AS \"cont-www\", \n"
				+" d19.\"ContactValue\" AS \"gender\", \n"
				+" d20.\"ContactValue\" AS \"birthdate\", \n"
				+" d21.\"ContactValue\" AS \"role\", \n"
				+" d22.\"ContactValue\" AS \"position\" \n"
				+" FROM \"Contacts\" AS c \n"
				+" LEFT JOIN \"DataStore\" AS d1 on c.\"ContactID\" = d1.\"ContactNumber\" AND d1.\"ContactValueType\"='home-phone' \n"
				+" LEFT JOIN \"DataStore\" AS d2 on c.\"ContactID\" = d2.\"ContactNumber\" AND d2.\"ContactValueType\"='home-postcode' \n"
				+" LEFT JOIN \"DataStore\" AS d3 on c.\"ContactID\" = d3.\"ContactNumber\" AND d3.\"ContactValueType\"='home-address' \n"
				+" LEFT JOIN \"DataStore\" AS d4 on c.\"ContactID\" = d4.\"ContactNumber\" AND d4.\"ContactValueType\"='home-suburb' \n"
				+" LEFT JOIN \"DataStore\" AS d5 on c.\"ContactID\" = d5.\"ContactNumber\" AND d5.\"ContactValueType\"='home-state' \n"
				+" LEFT JOIN \"DataStore\" AS d6 on c.\"ContactID\" = d6.\"ContactNumber\" AND d6.\"ContactValueType\"='home-country' \n"
				+" LEFT JOIN \"DataStore\" AS d7 on c.\"ContactID\" = d7.\"ContactNumber\" AND d7.\"ContactValueType\"='title' \n"
				+" LEFT JOIN \"DataStore\" AS d8 on c.\"ContactID\" = d8.\"ContactNumber\" AND d8.\"ContactValueType\"='location' \n"
				+" LEFT JOIN \"Location\" AS l on d8.\"ContactValue\" = l.\"LocationID\" \n"
				+" LEFT JOIN \"DataStore\" AS d17 on c.\"ContactID\" = d17.\"ContactNumber\" AND d17.\"ContactValueType\"='cont-email' \n"
				+" LEFT JOIN \"DataStore\" AS d18 on c.\"ContactID\" = d18.\"ContactNumber\" AND d18.\"ContactValueType\"='cont-www' \n"
				+" LEFT JOIN \"DataStore\" AS d19 on c.\"ContactID\" = d19.\"ContactNumber\" AND d19.\"ContactValueType\"='gender' \n"
				+" LEFT JOIN \"DataStore\" AS d20 on c.\"ContactID\" = d20.\"ContactNumber\" AND d20.\"ContactValueType\"='birthdate' \n"
				+" LEFT JOIN \"DataStore\" AS d21 on c.\"ContactID\" = d21.\"ContactNumber\" AND d21.\"ContactValueType\"='role' \n"
				+" LEFT JOIN \"DataStore\" AS d22 on c.\"ContactID\" = d22.\"ContactNumber\" AND d22.\"ContactValueType\"='position' \n"
				+" LEFT JOIN \"Company\" AS cm on c.\"CompanyID\" = cm.\"CompanyID\" \n" 
				+" WHERE (d1.\"ContactType\" = 'contact' or d1.\"ContactType\" is null)\n"
				+ DAOFactory.getInstance().getPermissionDAO().getPermissionForReadSQL("c.\"ContactID\"", EntityType.CONTACT, user);

		if (c != null) sql += "AND lower(cm.\"CompanyName\") like lower('%" + AbstractDAO.escape(c) + "%')\n";
		if (l != null) sql += "AND lower(c.\"LastName\") like lower('%" + AbstractDAO.escape(l) + "%')\n";
		if (f != null) sql += "AND lower(c.\"FirstName\") like lower('%" + AbstractDAO.escape(f) + "%')\n";

		ArrayList results = new ArrayList();
					
		try {
			con = contactDAO.getFactory().getInstance().getConnection();
			stmt = con.createStatement();
						
			logger.debug("Executing query:");
			logger.debug(sql);
						
			rs = stmt.executeQuery(sql);
				
			String str = null; 

			// add CSV header
			str = new String("title,firstname,lastname,company,position,role,email,url,"
						+ "work-location,work-address,work-suburb,work-state,work-country,work-postcode,"
						+ "home-phone,home-address,home-suburb,home-state,home-country,home-postcode,"
						+ "gender,birth\r\n");

			out.write(str.getBytes()) ;
			while (rs.next()) {
				str = new String(
								AbstractDAO.decode( rs.getString("title") )
								+ "," + AbstractDAO.decode( rs.getString("FirstName") )
								+ "," + AbstractDAO.decode( rs.getString("LastName") )
								+ "," + AbstractDAO.decode( rs.getString("CompanyName") )
								+ "," + AbstractDAO.decode( rs.getString("position") )
								+ "," + AbstractDAO.decode( rs.getString("role") )
								+ "," + AbstractDAO.decode( rs.getString("cont-email") )
								+ "," + AbstractDAO.decode( rs.getString("cont-www") )
								+ "," + AbstractDAO.decode( rs.getString("LocationTitle") )
								+ "," + AbstractDAO.decode( rs.getString("Address1") )
								+ "," + AbstractDAO.decode( rs.getString("Suburb") )
								+ "," + AbstractDAO.decode( rs.getString("State") )
								+ "," + AbstractDAO.decode( rs.getString("Country") )
								+ "," + AbstractDAO.decode( rs.getString("Postcode") )
								+ "," + AbstractDAO.decode( rs.getString("home-phone") )
								+ "," + AbstractDAO.decode( rs.getString("home-address") )
								+ "," + AbstractDAO.decode( rs.getString("home-suburb") )
								+ "," + AbstractDAO.decode( rs.getString("home-state") )
								+ "," + AbstractDAO.decode( rs.getString("home-country") )
								+ "," + AbstractDAO.decode( rs.getString("home-postcode") )
								+ "," + AbstractDAO.decode( rs.getString("gender") )
								+ "," + AbstractDAO.decode( rs.getString("birthdate") )
								+ "\r\n");
				out.write(str.getBytes()) ;
							
			}
						
		} catch (Exception ex) {
			System.out.println(sql);
			throw new RuntimeException(ex);
		}

		finally {
			try {
				if (rs != null) {
					rs.close();