exportservlet.java

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

				}
			
				if (stmt != null) {
					stmt.close();
				}
			
				if (con != null) {
					con.close();
				}   
			} catch (SQLException ex) {
				throw new RuntimeException(ex);
			}
		}
	}
	
	private void handleCallExport(BufferedOutputStream out, String user, int CompanyID) throws IOException {
		Connection con = null;
		ResultSet rs = null;
		Statement stmt = null;
	
		String sql = "SELECT c.*, "
				+" cm.\"CompanyName\" AS \"CompanyName\" \n"
				+" FROM \"Call\" AS c \n"
				+" LEFT JOIN \"Company\" AS cm on c.\"CompanyID\" = cm.\"CompanyID\" \n" 
				+" WHERE c.\"Deleted\" = false\n"
				+ DAOFactory.getInstance().getPermissionDAO().getPermissionForReadSQL("c.\"CallID\"", EntityType.CALL, user);
		if (CompanyID >= 0) {
			sql += "AND c.\"CompanyID\" = " + CompanyID + "\n";
		}

		logger.debug("Call Export SQL: " + sql);

		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("date,to,first,last,number,company,note,creator,read"
						+ "\r\n");

			out.write(str.getBytes()) ;
			while (rs.next()) {
				str = new String(
								AbstractDAO.decode( rs.getString("Date") )
								+ "," + AbstractDAO.decode( rs.getString("Owner") )
								+ "," + AbstractDAO.decode( rs.getString("FirstName") )
								+ "," + AbstractDAO.decode( rs.getString("LastName") )
								+ "," + AbstractDAO.decode( rs.getString("Number") )
								+ "," + AbstractDAO.decode( rs.getString("CompanyName") )
								+ "," + AbstractDAO.decode( rs.getString("Note") )
								+ "," + AbstractDAO.decode( rs.getString("Creator") )
								+ "," + AbstractDAO.decode( rs.getString("OwnerHasRead") )
								+ "\r\n");
				out.write(str.getBytes()) ;
			}
						
		} catch (Exception ex) {
			System.out.println(sql);
			throw new RuntimeException(ex);
		}

		finally {
			try {
				if (rs != null) {
					rs.close();
				}
			
				if (stmt != null) {
					stmt.close();
				}
			
				if (con != null) {
					con.close();
				}   
			} catch (SQLException ex) {
				throw new RuntimeException(ex);
			}
		}
	}
	
	private void handleReminderExport(BufferedOutputStream out, String user) throws IOException {
		Connection con = null;
		ResultSet rs = null;
		Statement stmt = null;
	
		String sql = "SELECT r.* "
				+" FROM \"Reminder\" as r\n"
				+" WHERE r.\"Deleted\" = false\n"
				+ DAOFactory.getInstance().getPermissionDAO().getPermissionForReadSQL("r.\"ReminderID\"", EntityType.REMINDER, user);

		logger.debug("Reminder Export SQL: " + sql);

		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("date,to,priority,reminder,email,emailto,sms,smsto,creator,read"
						+ "\r\n");

			out.write(str.getBytes()) ;
			while (rs.next()) {
				str = new String(
								AbstractDAO.decode( rs.getString("ReminderDate") )
								+ "," + AbstractDAO.decode( rs.getString("Owner") )
								+ "," + AbstractDAO.decode( rs.getString("ReminderRate") )
								+ "," + AbstractDAO.decode( rs.getString("ReminderMessage") )
								+ "," + AbstractDAO.decode( rs.getString("EmailReminder") )
								+ "," + AbstractDAO.decode( rs.getString("EmailReminderTo") )
								+ "," + AbstractDAO.decode( rs.getString("SMSReminder") )
								+ "," + AbstractDAO.decode( rs.getString("SMSReminderTo") )
								+ "," + AbstractDAO.decode( rs.getString("Creator") )
								+ "," + AbstractDAO.decode( rs.getString("OwnerHasRead") )
								+ "\r\n");
				out.write(str.getBytes()) ;
			}
						
		} catch (Exception ex) {
			System.out.println(sql);
			throw new RuntimeException(ex);
		}

		finally {
			try {
				if (rs != null) {
					rs.close();
				}
			
				if (stmt != null) {
					stmt.close();
				}
			
				if (con != null) {
					con.close();
				}   
			} catch (SQLException ex) {
				throw new RuntimeException(ex);
			}
		}
	}
	
	private void handleCompanyExport(BufferedOutputStream out, String user) throws IOException {
		Connection con = null;
		ResultSet rs = null;
		Statement stmt = null;
	
		String sql = "SELECT c.*, "
				+" d1.\"ContactValue\" as address,\n"
				+" d2.\"ContactValue\" as suburb,\n"
				+" d3.\"ContactValue\" as state,\n"
				+" d4.\"ContactValue\" as postcode,\n"
				+" d5.\"ContactValue\" as phone,\n"
				+" d6.\"ContactValue\" as fax,\n"
				+" d7.\"ContactValue\" as abn,\n"
				+" d8.\"ContactValue\" as acn\n"
				+" FROM \"Company\" as c\n"
				+" LEFT JOIN \"DataStore\" AS d1 on c.\"CompanyID\" = d1.\"ContactNumber\" AND d1.\"ContactValueType\"='address' \n"
				+" LEFT JOIN \"DataStore\" AS d2 on c.\"CompanyID\" = d2.\"ContactNumber\" AND d2.\"ContactValueType\"='suburb' \n"
				+" LEFT JOIN \"DataStore\" AS d3 on c.\"CompanyID\" = d3.\"ContactNumber\" AND d3.\"ContactValueType\"='state' \n"
				+" LEFT JOIN \"DataStore\" AS d4 on c.\"CompanyID\" = d4.\"ContactNumber\" AND d4.\"ContactValueType\"='postcode' \n"
				+" LEFT JOIN \"DataStore\" AS d5 on c.\"CompanyID\" = d5.\"ContactNumber\" AND d5.\"ContactValueType\"='phone' \n"
				+" LEFT JOIN \"DataStore\" AS d6 on c.\"CompanyID\" = d6.\"ContactNumber\" AND d6.\"ContactValueType\"='fax' \n"
				+" LEFT JOIN \"DataStore\" AS d7 on c.\"CompanyID\" = d7.\"ContactNumber\" AND d7.\"ContactValueType\"='abn' \n"
				+" LEFT JOIN \"DataStore\" AS d8 on c.\"CompanyID\" = d8.\"ContactNumber\" AND d8.\"ContactValueType\"='acn' \n"
				+" WHERE c.\"Deleted\" = false\n"
				+ DAOFactory.getInstance().getPermissionDAO().getPermissionForReadSQL("c.\"CompanyID\"", EntityType.COMPANY, user);

		logger.debug("Company Export SQL: " + sql);

		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("name,phone,fax,address,suburb,state,postcode,abn,acn,profile"
						+ "\r\n");

			out.write(str.getBytes()) ;
			while (rs.next()) {
				str = new String(
								AbstractDAO.decode( rs.getString("CompanyName") )
								+ "," + AbstractDAO.decode( rs.getString("phone") )
								+ "," + AbstractDAO.decode( rs.getString("fax") )
								+ "," + AbstractDAO.decode( rs.getString("address") )
								+ "," + AbstractDAO.decode( rs.getString("suburb") )
								+ "," + AbstractDAO.decode( rs.getString("state") )
								+ "," + AbstractDAO.decode( rs.getString("postcode") )
								+ "," + AbstractDAO.decode( rs.getString("abn") )
								+ "," + AbstractDAO.decode( rs.getString("acn") )
								+ "," + AbstractDAO.decode( rs.getString("Notes") )
								+ "\r\n");
				out.write(str.getBytes()) ;
			}
						
		} catch (Exception ex) {
			System.out.println(sql);
			throw new RuntimeException(ex);
		}

		finally {
			try {
				if (rs != null) {
					rs.close();
				}
			
				if (stmt != null) {
					stmt.close();
				}
			
				if (con != null) {
					con.close();
				}   
			} catch (SQLException ex) {
				throw new RuntimeException(ex);
			}
		}
	}
	
}