mailado.java

辅助办公系统,具有发布公告、站内邮箱、日程安排、日志查看等功能

package com.x3408.mail;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import com.jspsmart.upload.File;
import com.jspsmart.upload.SmartUpload;
import com.jspsmart.upload.SmartUploadException;
import com.x3408.database.CNProvider;
import com.x3408.office.Constants;

public class MailADO {
	public static Vector insert(Mail1Info mail1Info,Vector addrFormat) {
		PreparedStatement pstat=null;
		Connection conn = null;
		ResultSet rs=null;
		Vector<String> mailIDList=null;
		if (mail1Info==null||!mail1Info.mailValid()||addrFormat==null) {
			return null;
		}
		try {
			conn=CNProvider.getConnection();
			if(conn==null){
				return null;
			}
			mailIDList=new Vector<String>();
			for(int i=0;i<addrFormat.size();i++){
				if(i==addrFormat.size()-1){
					mail1Info.setLooked("1");
					mail1Info.setMailSort("2");
				}
				mail1Info.setReceiverAddr(i==addrFormat.size()-1?addrFormat.toString(): (String) addrFormat.elementAt(i));
				mail1Info.setAddress((String)addrFormat.elementAt(i));
				pstat = conn
						.prepareStatement("insert into email1(address,senderName,senderAddr,receiverAddr,looked,mailSort,caption,content,receiveTime)"
								+ "values(?,?,?,?,?,?,?,?,?)");
				pstat.setString(1, mail1Info.getAddress());
				pstat.setString(2, mail1Info.getSenderName());
				pstat.setString(3, mail1Info.getSenderAddr());
				pstat.setString(4, mail1Info.getReceiverAddr());
				pstat.setBoolean(5, mail1Info.getLooked());
				pstat.setByte(6, mail1Info.getMailSort());
				pstat.setString(7, mail1Info.getCaption());
				pstat.setString(8, mail1Info.getContent());
				pstat.setString(9, mail1Info.getReceiveTime());
				pstat.executeUpdate();
				CNProvider.release(pstat);
				pstat=conn.prepareStatement("select @@IDENTITY AS 'mailID'");
				rs=pstat.executeQuery();
				rs.next();
				mailIDList.addElement(rs.getString("mailID"));
			}
				return mailIDList.size()<1?null:mailIDList;
		} catch (SQLException e) {
				e.printStackTrace();
		} finally {
			CNProvider.release(pstat, conn);
		}
		return null;
	}
	public static String appendSave(SmartUpload mySmartUpload,Vector mailIDList){
		Connection conn=CNProvider.getConnection();
		PreparedStatement pstat=null;
		String msg=null;
		int mailID=0;
		int byteread=0; 
		byte[] buffer =null; 
		if(mailIDList==null||mailIDList.size()<1){
			return "指定的邮件地址无效";
		}
		if(mySmartUpload.getFiles().getCount()<1){
			return null;
		}
		java.io.File testDir=new java.io.File(Constants.APPEND_PATH);
		if(!testDir.exists()){
			testDir.mkdir();
		}
		mailID=Integer.parseInt((String)mailIDList.firstElement());
		for(int i=0;i<mySmartUpload.getFiles().getCount();i++){
			File myFile=mySmartUpload.getFiles().getFile(i);
			if(!myFile.isMissing()){
				try {
					myFile.saveAs(Constants.APPEND_PATH+mailID+"_"+myFile.getFileName().trim());
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					msg="附件已超过允许大小";
					break;
				} catch (SmartUploadException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					msg="附件未成功发送";
					break;
				}
			}
		}
		for(int i=0;i<mailIDList.size();i++){
			mailID=Integer.parseInt((String)mailIDList.elementAt(i));
			for(int j=0;j<mySmartUpload.getFiles().getCount();j++){
				File myFile=mySmartUpload.getFiles().getFile(j);
				if(!myFile.isMissing()){
					try {
						pstat = conn
						.prepareStatement("insert into email2(mailID,appendName,space) values(?,?,?)");
						pstat.setInt(1,mailID);
						pstat.setString(2, myFile.getFileName());
						pstat.setInt(3, myFile.getSize()/1000);
						pstat.executeUpdate();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
						break;
					}
					if(i==0)continue;
					try {
						InputStream inStream = new FileInputStream(Constants.APPEND_PATH+mailIDList.firstElement()+"_"+myFile.getFileName().trim());
						FileOutputStream outStream=new FileOutputStream( Constants.APPEND_PATH+mailID+"_"+myFile.getFileName().trim());
						buffer =new byte[1444];
						try {
							while ((byteread=inStream.read(buffer))!=-1) 
								
							{ 
								outStream.write(buffer,0,byteread); 
							}
							inStream.close();
							outStream.close();
						} catch (IOException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						} 
					} catch (FileNotFoundException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					} 
				}
			}
		}
		CNProvider.release(pstat,conn);
		return msg;
	}

	public static Vector mailListQuery(String address,String pMailSort) {
		Connection conn=null;
		PreparedStatement pstat=null;
		ResultSet rs=null;
		conn = CNProvider.getConnection();
		Vector<Mail1Info> mailList = new Vector<Mail1Info>();
		Mail1Info mailInfo = null;
		int mailSort;
		try {
			if(pMailSort==null){
				pstat=conn.prepareStatement("select * from Email1 where address=?");
				pstat.setString(1, address);
			}else{
				try{
					mailSort=Integer.parseInt(pMailSort);
				}catch(NumberFormatException e){
					e.printStackTrace();
					return null;
				}
				pstat = conn
						.prepareStatement("select * from Email1 where address=? and mailSort=?");
				pstat.setString(1, address);
				pstat.setInt(2,mailSort);
			}
			rs = pstat.executeQuery();
			while (rs.next()) {
				mailInfo=new Mail1Info(rs.getString("mailID"),rs.getString("address"),rs.getString("senderName"),
						rs.getString("senderAddr"),rs.getString("receiverAddr"),rs.getString("looked"),rs.getString("mailSort"),
						rs.getString("caption"),rs.getString("content"),rs.getString("receiveTime"));
				if(appendListQuery(rs.getString("mailID"))==null){
					mailInfo.setAppendHaving("0");
				}
				mailList.addElement(mailInfo);
				}
			return mailList.size()<1?null:mailList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			CNProvider.release(rs, pstat, conn);
		}
		return null;
	}
	public static Mail1Info  mail1InfoQuery(String pMailID){
		Connection conn=null;
		PreparedStatement pstat=null;
		ResultSet rs=null;
		int mailID;
		conn = CNProvider.getConnection();
		mailID=Integer.parseInt(pMailID);
		try {
			pstat=conn.prepareStatement("select * from Email1 where mailID=?");
			pstat.setInt(1, mailID);
			rs=pstat.executeQuery();
			rs.next();
			Mail1Info mail1Info=new Mail1Info(rs.getString("mailID"),rs.getString("address"),rs.getString("senderName"),
					rs.getString("senderAddr"),rs.getString("receiverAddr"),rs.getString("looked"),rs.getString("mailSort"),
					rs.getString("caption"),rs.getString("content"),rs.getString("receiveTime").substring(0,16));
			return mail1Info;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			CNProvider.release(rs, pstat, conn);
		}
		return null;
	}
	public static boolean mail1Update(String pMailID,int mailSort){
		Connection conn=null;
		PreparedStatement pstat=null;
		conn=CNProvider.getConnection();
		int mailID;
		try{
			mailID=Integer.parseInt(pMailID);
		}catch(NumberFormatException e){
			e.printStackTrace();
			return false;
		}
			try {
				pstat=conn.prepareStatement("update Email1 set mailSort=? where mailID=?");
				pstat.setInt(1, mailSort);
				pstat.setInt(2, mailID);
				pstat.executeUpdate();
				return true;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				CNProvider.release(pstat, conn);
			}
		return false;
	}	
	public static boolean mail1Update(String pMailID,boolean looked){
		Connection conn=null;
		PreparedStatement pstat=null;
		conn=CNProvider.getConnection();
		int mailID;
		try{
		mailID=Integer.parseInt(pMailID);
		}catch(NumberFormatException e){
			e.printStackTrace();
			return false;
		}
		try {
			pstat=conn.prepareStatement("update Email1 set looked=? where mailID=?");
			pstat.setBoolean(1, looked);
			pstat.setInt(2, mailID);
			pstat.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			CNProvider.release(pstat, conn);
		}
		return false;
	}
	public static boolean mailDelQuite(String pMailID){
		Connection conn=null;
		int mailID;
		mailID=Integer.parseInt(pMailID);
		conn=CNProvider.getConnection();
		PreparedStatement pstat=null;
		java.io.File fileDel=null;
		Mail2Info mail2Info=null;
		Vector appendList=appendListQuery(pMailID);
		for(int i=0;appendList!=null&&i<appendList.size();i++){
			mail2Info=(Mail2Info)appendList.elementAt(i);
			fileDel=new java.io.File(Constants.APPEND_PATH+pMailID+"_"+mail2Info.getAppendName());
			if(fileDel.exists()){
				fileDel.delete();
			}
		}
		try {
			pstat = conn.prepareStatement("delete from Email1 where mailID="+mailID);
			pstat.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			CNProvider.release(pstat, conn);
		}
		return false;
	}
	public static Vector appendListQuery(String pMailID){
		PreparedStatement pstat=null;
		ResultSet rs=null;
		Connection conn=CNProvider.getConnection();
		Vector<Mail2Info> appendList=new Vector<Mail2Info>();
		Mail2Info mail2Info=null;
		int mailID=Integer.parseInt(pMailID);
		try {
			pstat=conn.prepareStatement("select * from Email2 where mailID=?");
			pstat.setInt(1, mailID);
			rs=pstat.executeQuery();
			while(rs.next()){
				mail2Info=new Mail2Info(rs.getString("mailID"), rs.getString("appendName"),rs.getString("space"));
				appendList.addElement(mail2Info);
			}
			return appendList.size()<1?null:appendList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			CNProvider.release(rs,pstat,conn);
		}
		return null;
	}
	public static int newMailCount(String employeeID){
		Connection conn=CNProvider.getConnection();
		PreparedStatement pstat=null;
		ResultSet rs=null;
		int count=0;
		try {
			pstat=conn.prepareStatement("select count(*) from email1 where address=? and looked=? and mailSort=?");
			pstat.setString(1, employeeID);
			pstat.setBoolean(2, false);
			pstat.setInt(3, 1);
			rs=pstat.executeQuery();
			if(rs.next()){
				count=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			CNProvider.release(rs,pstat,conn);
		}
		return count;
	}
}