usersmanager.java

Email营销是在用户事先许可的前提下

package com.qq.content;

import java.util.ArrayList;
import java.sql.*;

import javax.sql.rowset.CachedRowSet;

public class UsersManager {
private static DBTool db=null;
	ArrayList list;


	public UsersManager() throws Exception{
		list = new ArrayList();
		db=new DBTool();
	}
	
	public boolean validQuit(String email,String[] service){
		if(service.length==0)return false;
		String temprule="";
		for(int i=0;i<service.length;i++){			
		  if(service[i].length()>0){
			  temprule+=" service='"+service[i]+"' or";			 
		  }
		}
		 temprule=temprule.substring(0,temprule.length()-3);
			String sql="UPDATE  user_detail SET status=9 WHERE email='"+email+"' AND ("+temprule+")";
			System.out.println(sql);
			try {
				db.execute(sql);
				return true;
			} catch (Exception e) {
				e.printStackTrace();
				return false;
			}		
	}
	
	public boolean canQuit(String email,String[] service){
		if(service.length==0)return false;
		for(int i=0;i<service.length;i++){
		  if(service[i].length()>0){
			  if(haveSubed(email,service[i])==false)return false;
		  }
		}
		return true;
	}
	
	public boolean haveSubed(String email,String service){
		  if(service==null || service.length()==0)return false;
		  
			String sql="SELECT * FROM user_detail WHERE email='"+email+"' AND status=0 AND service='"+service+"'";
			System.out.println(sql);
			try {
				CachedRowSet crs=db.query(sql);
				return crs.next();
			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}			
	}	
	
	public ArrayList getAllUserInfo(int page){
		String sql="";
		try {				
			sql = "SELECT distinct email,service,IP,source,join_date,status from user_detail limit "+(page*10)+",10";
			System.out.println("sql is:"+ sql);
			CachedRowSet rs =db.query(sql);
			ArrayList al=new ArrayList();
			while (rs.next()){
				String email = rs.getString(1);
				String service=rs.getString(2);
				String IP=rs.getString(3);
				String source=rs.getString(4);
				String join_date=rs.getString(5);
				int status=rs.getInt(6);
				String st="未知";
				if(status==0)st="已订制";
				if(status==9)st="已退订";
				ArrayList alx=new ArrayList();
				alx.add(email);
				alx.add(service);
				alx.add(IP);
				alx.add(source);
				alx.add(join_date);
				alx.add(st);
				al.add(alx);
			}
			return al;
		} catch (Exception e) {
			System.out.println("getAllUserInfo error:" + e.getMessage()+"=== "+sql);
			return new ArrayList();
		}

		
	}

	public ArrayList getUsersList(String [] services) {
		
		if((services==null)||(services.length==0)) return new ArrayList();
		
		String sql = "";
		String [] tmpservices = new String[services.length];
		
		for(int i=0;i<services.length;i++)
			tmpservices[i] = services[i];
		
		int size = tmpservices.length;
		if(size==0) return new ArrayList();
		
		String tmprules="";
		for(int i=0;i<size;i++){
			if((tmpservices[i]!=null)&&(tmpservices[i].length()>0)){
				tmprules = tmprules+ " service='" + tmpservices[i] + "' or ";
			}
		}
		
		if(tmprules.length()==0) return new ArrayList();
	
		try {
			String rules = tmprules.substring(0, tmprules.length() - 3);					
			sql = "SELECT distinct email from user_detail where status=0 AND ("+rules+")";
			System.out.println("sql is:"+ sql);
			CachedRowSet rs =db.query(sql);
			while (rs.next()){
				String email = rs.getString(1);
				list.add(email);
			}
		} catch (Exception e) {
			System.out.println("getUsersList error:" + e.getMessage()+"=== "+sql);
			return new ArrayList();
		}
		return list;

	}

	public int saveServiceMail(String title, String msg, String [] services, String sender, int sendtimes) throws Exception {
		String servicestr = "";
		for(int i=0;i<services.length;i++){
			if((services[i]!=null)&&(services[i].length()>0))
				servicestr = servicestr+services[i]+":";
		}
		
		if(servicestr.length()>0) servicestr=servicestr.substring(0,servicestr.length()-1);
		else
			return -1;
		String sql = "insert into service_mail_detail set service_name='"+servicestr+"', mail_sent_times="+sendtimes+", mail_sender_name='"+sender+"', mail_title='"+title+"', mail_content='"+msg+"'";
		System.out.println(sql);
		db.exec(sql);
		return 0;
	}
	public int addServiceMailSentNum(String [] services){
		String [] tmpservices = new String[services.length];
		for(int i=0;i<services.length;i++)
			tmpservices[i] = services[i];
			
		try {
			int size = tmpservices.length;
			if(size==0) return -1;
			String tmprules="";
			for(int i=0;i<size;i++){
				if(tmpservices[i].length()>0){
					tmprules = tmprules+ " service='" + tmpservices[i] + "' or ";
				}
			}
			System.out.println(tmprules);
			String rules = tmprules.substring(0, tmprules.length() - 3);
			System.out.println(rules);
			String sql = "update user_detail set service_mail_sent_times=service_mail_sent_times+1 where ("+rules+")";
			System.out.println("sql is:"+ sql);
			db.exec(sql);	
			}catch(Exception e){
				return -2;
			}
			return 0;
			
	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {

		
	}

}