package DADA::MailingList::Subscribers::baseSQL; 

use strict; 

use lib qw(./ ../ ../../ ../../../ ./../../DADA ../../perllib); 


use Carp qw(croak carp); 

use DADA::Config qw(!:DEFAULT);  
use DADA::MailingList::Settings; 
use DADA::App::Guts;
use DADA::Logging::Usage;
	
my $email_id         = $DADA::Config::SQL_PARAMS{id_column} || 'email_id';

$DADA::Config::SQL_PARAMS{id_column} ||= 'email_id';



use Fcntl qw(O_WRONLY  
             O_TRUNC 
             O_CREAT 
             O_CREAT 
             O_RDWR
             O_RDONLY
             LOCK_EX
             LOCK_SH 
             LOCK_NB
            ); 



my %fields; 

my $dbi_obj; 



sub new {

	my $class = shift;
	my %args = (-List => undef, 
				@_); 
	   my $self = {};			
       bless $self, $class;
	   $self->_init(%args); 
	   return $self;

}





sub _init  { 

    my $self = shift; 
    my %args = @_; 
    
    $self->{'log'}      = new DADA::Logging::Usage;
    $self->{list}       = $args{-List};

	my $ls = DADA::MailingList::Settings->new(-List => $args{-List}); 
    $self->{list_info}  = $ls->get;
    $self->{sql_params} = {%DADA::Config::SQL_PARAMS};
    
	if(!$dbi_obj){ 
		require DADA::App::DBIHandle; 
		$dbi_obj = DADA::App::DBIHandle->new; 
		$self->{dbh} = $dbi_obj->dbh_obj; 
	}else{ 
		$self->{dbh} = $dbi_obj->dbh_obj; 
	}
	
	$self->{fields} = {%args}; 
}




sub open_email_list {  
	
	# TODO: Remove open_email_list method from Dada Mail 
	
	# Currently in the program, this method is ONLY used for blacklist stuff; 
	
	my $self = shift; 
	my %args = (-Type      => 'list',
				-As_Ref    => 0, 
				-Sorted    => 1,
				@_);
	

	
	my $list = $self->{list} || undef; 
	if($list){	#why wouldn't there be a list?!
		my @list   = ();    
		
		my $query = 'SELECT email FROM ' 
					 . $self->{sql_params}->{subscriber_table} . 
					 ' WHERE list_type = ? AND list_status = 1';
		
		if($args{-Type} eq 'black_list'){ 
			
			if (!$DADA::Config::GLOBAL_BLACK_LIST ){ 
				$query .= " AND list = ?";
			}else{ 
				# Well, nothin'
			}
			
		}else{ 
			$query .= " AND list = ?";
		}
		
		
		   $query .= ' ORDER BY email' 
		   		if $DADA::Config::LIST_IN_ORDER == 1;
		
		my $sth = $self->{dbh}->prepare($query);    
			
			
		if($DADA::Config::GLOBAL_BLACK_LIST  && $args{-Type} eq 'black_list'){ 

			$sth->execute($args{-Type}) 
				or croak "cannot do statment! $DBI::errstr\n";   
		
		}else{ 
		
			$sth->execute($args{-Type}, $self->{list}) 
				or croak "cannot do statment! $DBI::errstr\n";  
			
		}
		
		while((my $email) = $sth->fetchrow_array){ 
				push(@list, $email); 
			}
		
			$sth->finish;
		
		if($args{-As_Ref} eq "1"){ 
			return \@list; 
		}else{ 
			return @list; 
		}
	}else{ 
		return undef;
	}
}



sub open_list_handle { my $self = shift;}	# not needed
sub sort_email_list {  my $self = shift;}	# not needed

sub search_email_list { 

	my $self = shift; 
	
	my %args = (-Keyword   => undef,
	            -Method    => undef, 
	            -Type      => 'list',
	            -as_string => 0, 
	            @_);
	my @matches; 
	my $domain_regex = "(.*)\@(.*)"; 
	my $found = 0; 
	
	my $r = ''; 


	my $method  = $args{-Method} || "standard"; 
	my $keyword = $args{-Keyword};
	   $keyword = quotemeta($keyword);  

	my $type = $args{-Type}; 
	
	my $query = "SELECT email FROM " . $self->{sql_params}->{subscriber_table} . " WHERE list_type = ? AND list_status = 1";
	   $query .= " AND list = ?"
		unless $DADA::Config::GLOBAL_BLACK_LIST  && $args{-Type} eq 'black_list';
		
	   $query .= ' ORDER BY email' 
	   	if $DADA::Config::LIST_IN_ORDER == 1;		
	
	my $sth = $self->{dbh}->prepare($query); 
	   
	   if($DADA::Config::GLOBAL_BLACK_LIST  && $args{-Type} eq 'black_list'){ 
			$sth->execute($args{-Type}) 
			or croak "cannot do statment (for search_email_list)! $DBI::errstr\n";   
	   }else{ 
		$sth->execute($args{-Type}, $self->{list}) 
			or croak "cannot do statment (for search_email_list)! $DBI::errstr\n";   
	   }
	   
	if(defined($keyword) && $keyword ne ""){
		if($method eq "domain"){ 
			while((my $email) = $sth->fetchrow_array){ 
				if($email =~ m/$domain_regex$keyword$/i){ 
					my $str = "<input type=\"checkbox\" name=\"address\" value=\"$email\" />&nbsp;&nbsp; <a href=\"$DADA::Config::S_PROGRAM_URL?f=edit_subscriber&amp;email=$email&amp;type=$type\">$email</a><br />\n";

					if($args{-as_string} == 1){ 
						$r .= $str; 
					}else{ 
						print $found; 
					}


					$found++;
				}
			}
		}elsif($method eq "service"){ 
			while((my $email) = $sth->fetchrow_array){ 


				if($email =~ m/\@$keyword$/i){ 
					my $str = "<input type=\"checkbox\" name=\"address\" value=\"$email\" />&nbsp;&nbsp; <a href=\"$DADA::Config::S_PROGRAM_URL?f=edit_subscriber&amp;email=$email&amp;type=$type\">$email</a><br />\n";

					if($args{-as_string} == 1){ 
						$r .= $str; 
					}else{ 
						print $found; 
					}



					$found++;  
				}	   
			}  
		}else{ 
			while((my $email) = $sth->fetchrow_array){ 
				if($email =~ m/$keyword/i){ 
					my $str = "<input type=\"checkbox\" name=\"address\" value=\"$email\" />&nbsp;&nbsp; <a href=\"$DADA::Config::S_PROGRAM_URL?f=edit_subscriber&amp;email=$email&amp;type=$type\">$email</a><br />\n";

					if($args{-as_string} == 1){ 
						$r .= $str; 
					}else{ 
						print $found; 
					}


					$found++;
				}
			}
		}
	}
	$sth->finish;


	if($args{-as_string} == 1){ 
		return($found, $r); 	
	}else{ 
		return($found); 
		 
	}
	
	
}

 
sub get_black_list_match{ 
	my $self = shift; 
	my @got_black; 
	my @got_white; 
	my $black_list = shift; 
	my $try_this = shift; 
	if($black_list and $try_this){ 
		my $black; 
		foreach $black(@$black_list){ 
				$black = quotemeta($black);
			my $try; 
			foreach $try(@$try_this){ 
				my $qm_try = $try; 
				
				next if ! $black || $black eq ''; 
				
				if($qm_try =~ m/$black/i){ 
					push(@got_black, $try); 
				}
			}
		}
		return (\@got_black);
	}else{ 
		return 0;
	}
}

sub print_out_list { 

	my $self = shift; 

	my %args = (-Type => 'list',
				-FH  => \*STDOUT,
				@_); 
	my $fh = $args{-FH};
	
	my $count; 
	if($self->{list}){ 
		my $query = "SELECT "; 
		
		   $query .= "DISTINCT "
		   	if $args{-Type} eq 'black_list'; # slight kludge. 
		   	
	 	   $query .= "email  FROM " . $self->{sql_params}->{subscriber_table} . 
		            " WHERE list_type = ? AND list_status = 1";
		   $query .= " AND list = ?"
		   	unless $DADA::Config::GLOBAL_BLACK_LIST  && $args{-Type} eq 'black_list'; 
		            
		   $query .= ' ORDER BY email' 
		   	if $DADA::Config::LIST_IN_ORDER == 1;
		
		my $sth = $self->{dbh}->prepare($query);
			
		if ($DADA::Config::GLOBAL_BLACK_LIST  && $args{-Type} eq 'black_list'){ 
		
		   $sth->execute($args{-Type}) 
		   		or croak "cannot do statment (for print out list)! $DBI::errstr\n";   
		
		}else{ 
		
			$sth->execute($args{-Type}, $self->{list}) 
		   		or croak "cannot do statment (for print out list)! $DBI::errstr\n";   
		
		}
		
		while((my $email) = $sth->fetchrow_array){ 
			print $fh $email;
			print $fh "\n"; 
			$count++; 
		}
		$sth->finish;		
		return $count; 
	}
}




sub subscription_list { 
	my $self = shift; 
		
		my %args = (-start    => 1, 
	               '-length' => 100,
	                -Type     => 'list',
	            	@_); 
	             
	my $count = 0; 
	my $list = []; 
	my $email; 
	
	my $query  = 'SELECT ';
	   	
	   	if($DADA::Config::SUBSCRIBER_DB_TYPE eq 'PostgreSQL'){ # This is messy. 
			$query .= "DISTINCT ON (email) "
				if $args{-Type} eq 'black_list'; # slight kludge. 
		}   	
	   $query .= ' * FROM ' . $self->{sql_params}->{subscriber_table} . 
	            ' WHERE list_type = ? AND list_status = 1';
	   
	   $query .= ' AND list = ?'
			unless $DADA::Config::GLOBAL_BLACK_LIST  && $args{-Type} eq 'black_list';
		
	   if($DADA::Config::SUBSCRIBER_DB_TYPE eq 'MySQL'){ # This is messy. 
			$query .= " GROUP BY email "
				if $args{-Type} eq 'black_list'; # slight kludge. 
		}   
		
	   $query .= ' ORDER BY email' 
		   	if $DADA::Config::LIST_IN_ORDER == 1;
		   	
	my $sth = $self->{dbh}->prepare($query);
	
	if($DADA::Config::GLOBAL_BLACK_LIST  && $args{-Type} eq 'black_list'){ 
  	
  		$sth->execute($args{-Type}) 
	  		or croak "cannot do statment (subscription_list bl)! $DBI::errstr\n";    
	
	}else{ 
	
		$sth->execute($args{-Type}, $self->{list}) 
	  		or croak "cannot do statment (for subscription_list 1)! $DBI::errstr\n";    
	
	}
	
	my $hashref; 
	 while($hashref = $sth->fetchrow_hashref){ 
			$count++; 
			next if $count <  $args{-start}; 
			last if $count > ($args{-start} + $args{'-length'});	
			push(@$list, $hashref);  
	}					
		return $list; 
}


sub filter_list_through_blacklist { 

	my $self = shift; 
	my $list = []; 
	
	my $query = 'SELECT * FROM ' . $self->{sql_params}->{subscriber_table} . 
	           " WHERE list_type = 'black_list' AND list_status = 1";
	
	$query .= ' AND list = ?'
		if $DADA::Config::GLOBAL_BLACK_LIST  != 1;
		
	my $sth = $self->{dbh}->prepare($query);
	
	if($DADA::Config::GLOBAL_BLACK_LIST ){ 
		$sth->execute() 
			or croak "cannot do statment (filter_list_through_blacklist)! $DBI::errstr\n";    
	}else{ 
		$sth->execute($self->{list}) 
			or croak "cannot do statment (filter_list_through_blacklist)! $DBI::errstr\n";    
	}
	my $hashref; 
	my $hashref2; 
	
	while($hashref = $sth->fetchrow_hashref){ 
	
		my $query2 = 'SELECT * from ' . $self->{sql_params}->{subscriber_table} . 
		" WHERE list_type = 'list' and list_status = 1 and list = ? AND email like ?"; 
		my $sth2 = $self->{dbh}->prepare($query2); 
		$sth2->execute($self->{list}, '%' . $hashref->{email} . '%')
			or croak "cannot do statment (filter_list_through_blacklist)! $DBI::errstr\n";    
		
		while($hashref2 = $sth2->fetchrow_hashref){ 
			push(@$list, $hashref2);  
		}
	}					
	return $list; 

}




sub check_for_double_email {
	
	my $self = shift; 
	my %args = ( 
	-Email          => undef,
	-Type           => 'list',
	-Status         => 1, 
	@_
	);
	my @list; 
	
	if($self->{list} and $args{-Email}){
		
		$args{-Email} = strip($args{-Email}); 
		$args{-Email} = cased($args{-Email}); 
		
		
		if($args{-Type} eq 'black_list'){ 			 
			
			my $query = "SELECT email FROM " 
			            . $self->{sql_params}->{subscriber_table} .  
						" WHERE list_type = ? AND list_status = ?";
			   $query = " AND list = ?"
			   	if !$DADA::Config::GLOBAL_BLACK_LIST  && $args{-Type} ne 'black_list'; 
											
			my $sth = $self->{dbh}->prepare($query);
											
			if($DADA::Config::GLOBAL_BLACK_LIST ){ 

				$sth->execute($args{-Type}, $args{-Status}) 
					or croak "cannot do statment (for check for double email)! $DBI::errstr\n";  
				
			}else{ 
				
				if(!$DADA::Config::GLOBAL_BLACK_LIST  && $args{-Type} ne 'black_list'){ 
					$sth->execute($args{-Type}, $args{-Status}, $self->{list}) 
						or croak "cannot do statment (for check for double email)! $DBI::errstr\n";  			
				}else{ 
					$sth->execute($args{-Type}, $args{-Status}) 
						or croak "cannot do statment (for check for double email)! $DBI::errstr\n"; 				
				}
			}
			
			while((my $email) = $sth->fetchrow_array){ 

				$email = quotemeta($email); 		
				 
				 next if ! $email || $email eq ''; 
				    
				if(DADA::App::Guts::cased($args{-Email}) =~ m/$email/i){ 
					return 1;
				} 
			} 
			return 0; 
											
		}else{ 
			my $sth = $self->{dbh}->prepare("SELECT email FROM " . $self->{sql_params}->{subscriber_table} .  
											" WHERE list = ? AND list_type = ? AND email= ? AND list_status = ?");   
									  
			$sth->execute($self->{list}, $args{-Type}, $args{-Email}, $args{-Status}) or croak "cannot do statment (for check for double email)! $DBI::errstr\n";  
			while((my $email) = $sth->fetchrow_array){ 
				push(@list, $email); 
			}
			my $in_list = 0; 
			if($list[0]){ 
				$in_list=1;
			}
			$sth->finish;
			return $in_list; 
		}
	}else{ 
		return 0;
	}
}

sub num_subscribers { 

	my $self = shift; 
	my %args = (-Type => 'list',
	            @_);
	my @row; 
	
	my $query = '';
	
				
	if($args{-Type} eq 'black_list'){  # slight kludge. 
		$query .= 'SELECT COUNT(DISTINCT email) ';
	}else{		
		$query .= 'SELECT COUNT(*) '; 
	}
	
	$query .= ' FROM '. $self->{sql_params}->{subscriber_table} . 
			  ' WHERE list_type = ? AND list_status = 1 ';
	 
	$query .= ' AND list = ?'
	   	unless $DADA::Config::GLOBAL_BLACK_LIST  == 1 && $args{-Type} eq 'black_list';  
			    
	my $sth = $self->{dbh}->prepare($query);
														 
	if($DADA::Config::GLOBAL_BLACK_LIST  == 1 && $args{-Type} eq 'black_list'){ 											
		$sth->execute($args{-Type}) 
			or croak "cannot do statment (num_subscribers)! $DBI::errstr\n";    
	}else{ 
		$sth->execute($args{-Type}, $self->{list}) 
			or croak "cannot do statment (num_subscribers)! $DBI::errstr\n";    
	}
 	  	
	   @row = $sth->fetchrow_array(); 
	   $sth->finish; 
	return $row[0];  
} 


sub add_to_email_list { 

	my $self = shift; 
	
	my %args = (-Email_Ref => undef, 
				-Type      => "list",
				-Mode      => 'append',
				@_);
	
	my $address = $args{-Email_Ref} || undef;
	my $email_count = 0;
	my $ending = $args{-Type}; 
	my $write_list = $self->{list} || undef; 
	
	if($write_list and $address){
	
		if($self->{list_info}->{hard_remove} == 1){ 
		
			foreach(@$address){
				chomp($_);
				$_ = strip($_); 
				my $sth = $self->{dbh}->prepare("INSERT INTO ".$self->{sql_params}->{subscriber_table}." VALUES (nextval('".$self->{sql_params}->{subscriber_table}."_".$email_id."_seq'),?,?,?,?)");    
				   $sth->execute($_, $self->{list}, $args{-Type}, 1) or croak "cannot do statment (for add_to_email_list1)! $DBI::errstr\n";   
				   $sth->finish;
					$email_count++;
					$self->{'log'}->mj_log($self->{list},"Subscribed to $write_list.$ending", $_) if (($DADA::Config::LOG{subscriptions}) && ($args{-Mode} ne 'writeover')); 
			}
		
		
		}else{ 
		
			foreach(@$address){
				chomp($_);
				$_ = strip($_);
				
				if($self->check_for_double_email(-Email => $_, -Type => $args{-Type}, -Status => 0)){ 
					my $sth = $self->{dbh}->prepare("UPDATE " . $self->{sql_params}->{subscriber_table} . 
											" SET list_status = 1 WHERE email   = ? AND list = ? AND list_type = ?");    
			   		$sth->execute($_, $self->{list}, $args{-Type}) or croak "cannot do statment (for add_email_list2)! $DBI::errstr\n";   
			   		$sth->finish;
				}else{ 
			
					my $sth = $self->{dbh}->prepare("INSERT INTO ".$self->{sql_params}->{subscriber_table}." VALUES (nextval('".$self->{sql_params}->{subscriber_table}."_".$email_id."_seq'),?,?,?,?)");    
					   $sth->execute($_, $self->{list}, $args{-Type}, 1) or croak "cannot do statment (for add_email_list3)! $DBI::errstr\n";   
					   $sth->finish;
						$email_count++;
						$self->{'log'}->mj_log($self->{list},"Subscribed to $write_list.$ending", $_) if (($DADA::Config::LOG{subscriptions}) && ($args{-Mode} ne 'writeover')); 	
				}
			}
		}

		return $email_count; 
	}else{ 
		carp('$DADA::Config::PROGRAM_NAME $DADA::Config::VER Error: No list, or list ref was given in add_email_list()');
		return undef;
	}
}


sub remove_from_list { 

	my $self = shift; 
	my %args = ( 
	-Email_List => [], 
	-Type       => 'list',
	@_,
	); 
	
	my $list         = $self->{list}; 
	 
	my $type         = $args{-Type}; 
	my $deep_six     = $args{-Email_List}; 
	my $count        = 0;
	
	return 0        if !@$deep_six;
	croak "no list?!" if !$list; 


	if($self->{list_info}->{hard_remove} == 1){ 
		
		foreach(@$deep_six){ 
		
			$_ = strip($_);
			$_ = cased($_);
			
			my $query = "DELETE FROM " . $self->{sql_params}->{subscriber_table} . " 
						 WHERE email   = ?
						 AND list_type = ?";
						 
			if($args{-Type} eq 'black_list'){ 
				if($DADA::Config::GLOBAL_BLACK_LIST  != 1){ 
					$query .= 'AND list      = ?'
				}
			}elsif($args{-Type} eq 'list'){ 
				if($DADA::Config::GLOBAL_UNSUBSCRIBE  != 1){ 
					$query .= 'AND list      = ?'
				}
			}else{ 
				$query .= 'AND list      = ?'
			}
			
			my $sth = $self->{dbh}->prepare($query);   
										
		   # For a non-SELECT statement, execute returns the number of rows affected, if known. 
		   my $rv; 
		   
		   if(($DADA::Config::GLOBAL_BLACK_LIST   && $args{-Type} eq 'black_list') ||
		   	   $DADA::Config::GLOBAL_UNSUBSCRIBE  && $args{-Type} eq 'list'){ 
		   
			   $rv = $sth->execute($_, $args{-Type}) 
					or croak "cannot do statment! $DBI::errstr\n";   
			   
		   }else{ 
		   
		   		$rv = $sth->execute($_, $args{-Type}, $self->{list}) 
					or croak "cannot do statment! $DBI::errstr\n";
		   }
		   
		   $sth->finish;
		   
		   if($rv >= 1){ 
				$self->{'log'}->mj_log($self->{list},"Unsubscribed from $list - $type", $_) if $DADA::Config::LOG{subscriptions}; 
				$count++;
				if($rv > 1){ 
					carp "$DADA::Config::PROGRAM_NAME $DADA::Config::VER warning: more than one row removed for email, '$_'";
				}
			}
			
		}
	}else{ 
		foreach(@$deep_six){
			$_ = strip($_);
			$_ = cased($_);
		 
		 	my $query = "UPDATE " . $self->{sql_params}->{subscriber_table} . " 
						 SET list_status   = 0
						 WHERE email   = ?
						 AND list_type = ?";
		 	
		 	if($args{-Type} eq 'black_list'){ 
				if($DADA::Config::GLOBAL_BLACK_LIST  != 1){ 
					$query .= 'AND list      = ?'
				}
			}elsif($args{-Type} eq 'list'){ 
				if($DADA::Config::GLOBAL_UNSUBSCRIBE  != 1){ 
					$query .= 'AND list      = ?'
				}
			}else{ 
				$query .= 'AND list      = ?'
			}


			my $sth = $self->{dbh}->prepare($query);    
			# For a non-SELECT statement, execute returns the number of rows affected, if known. 
			my $rv;
			
			
		if(($DADA::Config::GLOBAL_BLACK_LIST   && $args{-Type} eq 'black_list') ||
		   	$DADA::Config::GLOBAL_UNSUBSCRIBE  && $args{-Type} eq 'list'){ 
			$rv = $sth->execute($_, $args{-Type}) 
				or croak "cannot do statment! $DBI::errstr\n";   
		}else{ 
			$rv = $sth->execute($_, $args{-Type}, $self->{list}) 
				or croak "cannot do statment! $DBI::errstr\n";			
		
		}


			$sth->finish;
		   
			if($rv >= 1){ 
				$self->{'log'}->mj_log($self->{list},"Unsubscribed (soft) from $list - $type", $_) if $DADA::Config::LOG{subscriptions}; 
				$count++;
				if($rv > 1){ 
					carp "$DADA::Config::PROGRAM_NAME $DADA::Config::VER warning: more than one row removed (soft) for email, '$_'";
				}
			}
		}
	
	}
	return $count; 
}


sub list_option_form { 

my $self = shift; 

my %args = (
-Type           =>  'list', 
-In_Order       =>  0, 
@_,
);


if(defined($self->{list})){ 

#make the 'Show Domains' Hash
my %domains; 
foreach(@DADA::Config::DOMAINS){ $domains{$_} = 0; }
$domains{Other} = 0;

#make the 'Show Services' Hash
my %SERVICES; 
foreach(@DADA::Config::SERVICES){ $SERVICES{$_} = 0; }


	my $email;
	my $count = 0; 

my $test = 0;
   $test = $DADA::Config::SHOW_DOMAIN_TABLE + $DADA::Config::SHOW_SERVICES_TABLE + $DADA::Config::SHOW_EMAIL_LIST; 
  
unless ($test == 0){

#this is a stupid, dumb, idiotic idea, but, people want it. 
if($args{-In_Order} == 1){
	$self->sort_email_list(%args);
}

	my $query =  'SELECT email FROM '. $self->{sql_params}->{subscriber_table} .  ' WHERE list = ? AND list_type = ? AND list_status = 1';
       $query .= ' ORDER BY email' if $DADA::Config::LIST_IN_ORDER == 1;
	                       	                       
	my $sth = $self->{dbh}->prepare($query);    

   $sth->execute($self->{list}, $args{-Type}) or croak "cannot do statment! $DBI::errstr\n";   
			while((my $email) = $sth->fetchrow_array){
				chomp($email);
				if($DADA::Config::SHOW_EMAIL_LIST ==1){
					print "<option value=",$email,">",$email,"</option>\n";
				}
                # this is the 'Show Domains' Hash Generator
				
				if($DADA::Config::SHOW_DOMAIN_TABLE ==1){
					my $domain_email = $email;
					#delete everything before the first . after the @. 
					$domain_email =~ s/^(.*)@//; 
					#lowercase the ending..
					$domain_email = lc($domain_email); 
					#strip everything before the last . 
					$domain_email  =~ s/^(.*?)\.//;

					# hey, if it matches, recorde it. 
					if(exists($domains{$domain_email})){ 
					 $domains{$domain_email}++; 
					 }else{ 
					 $domains{Other}++;
					 } 
                 }
        
				
				
				
				
				
				# Here be the 'Show Services' Hash Generator; 
			    if($DADA::Config::SHOW_SERVICES_TABLE == 1){ 
				    my $services_email = $email;
					#delete everything before the first . after the @. 
	                $services_email =~ s/(^.*)@//; 
	                #lowercase the ending..
	                $services_email = lc($services_email); 

	                # hey, if it matches, record it. 
	                if(exists($SERVICES{$services_email})){ 
	                $SERVICES{$services_email}++ ;
	                }else{ 
	                $SERVICES{Other}++;
	                }
                }
 				$count++;
				
				
			}
		$sth->finish;	
}	
	return($count, \%domains,\%SERVICES);
	
}else{ 
	return undef; 
}

}


sub create_bulk_sending_file { 

	my $self = shift; 
	
	my %args = ( 
				-Type             => 'list', 
				-Pin              =>  1,
				-ID               =>  undef, 
				-Ban              =>  undef, 
				-Bulk_Test        =>  0,

				-Sending_Lists    => [], 
                
                -Save_At          => undef, 

				-Test_Recipient   => undef,  
				@_
			  ); 
	
	
	my $list   =  $self->{list}; 
	my $type   =  $args{-Type}; 
	
	my $also_send_to = $args{-Sending_Lists};
	my $clean_also_send_to = []; 
	
	foreach(@$also_send_to){ 
		next if $_ eq $self->{list}; 
		push(@$clean_also_send_to, $_);
	}
	
	my @f_a_lists = available_lists(-dbi_handle => $dbi_obj); 
	my %list_names;    
	foreach(@f_a_lists){ 
		my $als = DADA::MailingList::Settings->new(-List => $_); 
		my $ali = $als->get; 
		$list_names{$_} = $ali->{list_name};
	}
			
			
			
	$list =~ s/ /_/g; # really...
	
	my ($sec, $min, $hour, $day, $month, $year) = (localtime)[0,1,2,3,4,5];
	my $message_id = sprintf("%02d%02d%02d%02d%02d%02d", $year+1900, $month+1, $day,  $hour, $min, $sec);
	
	#use the message ID, If we have one. 
	my $letter_id = $args{'-ID'} ||  $message_id;
	   $letter_id =~ s/\@/_at_/g; 
	   $letter_id =~ s/\>|\<//g; 
	
	my $n_msg_id = $args{'-ID'} || $message_id;
	   $n_msg_id =~ s/\<|\>//g;
	   $n_msg_id =~ s/\.(.*)//; #greedy
	   
	my %banned_list; 
	
	if($args{-Ban}){ 
		my $banned_list = $args{-Ban};
		$banned_list{$_} = 1 foreach(@$banned_list); 
	}

	my $list_file    = make_safer($DADA::Config::FILES . '/' . $list . '.' . $type); 	
     my $sending_file = make_safer($args{-Save_At}) || make_safer($DADA::Config::TMP    . '/msg-' . $list . '-' . $type . '-' . $letter_id); 

		
	#open one file, write to the other. 
	my $email; 
		
	sysopen (SENDINGFILE, "$sending_file",  O_RDWR|O_CREAT, $DADA::Config::FILE_CHMOD ) or
		croak "$DADA::Config::PROGRAM_NAME $DADA::Config::VER Error: Cannot create temporary email list file for sending out bulk message: $!"; 
	flock(SENDINGFILE, LOCK_EX); 	



    my $first_email = $self->{list_info}->{list_owner_email}; 
    if($args{'-Bulk_Test'} == 1 && $args{-Test_Recipient}){ 
        $first_email = $args{-Test_Recipient};
    }




	my $to_pin = make_pin(-Email => $first_email);
	
	my ($lo_e_name, $lo_e_domain) = split('@', $first_email); 


    my $total = 0; 


	print SENDINGFILE join('::', $first_email,
	                             $lo_e_name, 
	                             $lo_e_domain, 
	                             $to_pin, 
	                             $self->{list_info}->{list},
	                             $list_names{$self->{list_info}->{list}},
	                             $n_msg_id, 
	                       );
	
	$total++;
	                       
	
	# TODO: these three lines need to be one
	my $test_test = $args{'-Bulk_Test'};
	chomp($test_test);
	unless($test_test == 1){ 
		
		my $merge_field_query; 
		my @merge_fields = split(',',  $self->{list_info}->{merge_fields}); 
	    foreach(@merge_fields){  
	       	$merge_field_query .= ', ' . $_; 
	    }
    
        my $query; 
        
        if($clean_also_send_to->[0]){ 

            
            $query  = "SELECT ";
            if($DADA::Config::SUBSCRIBER_DB_TYPE eq 'PostgreSQL'){ # This is messy. 
                $query .= "DISTINCT (email) ";
            }
            
            $query .= " email, list";  
            
            $query .= $merge_field_query; 
            $query .= " FROM " . $self->{sql_params}->{subscriber_table};  
            $query .= " WHERE (list    = ? ";
            
            foreach(@$clean_also_send_to){ 
                $query .= " OR list = '$_'"; 
            }

                $query .= ") AND list_type = ?
                         AND list_status= 1";
                         
        if($DADA::Config::SUBSCRIBER_DB_TYPE eq 'MySQL'){ # This is messy. 
            $query .= ' GROUP BY email '; 
        }	
                        
        }else{ 
            
            $query = "SELECT email, list";  
            $query .= $merge_field_query; 
            $query .= " FROM " . $self->{sql_params}->{subscriber_table} .  
                      " WHERE list    = ? 
                        AND list_type = ?
                        AND list_status= 1";
        }			
        

        $query .= ' ORDER BY email' 
            if $DADA::Config::LIST_IN_ORDER == 1;
            
        my $sth = $self->{dbh}->prepare($query);    	  								  
           $sth->execute($self->{list}, $args{-Type}) 
            or croak "cannot do statment! $DBI::errstr\n";     
        
        my $field_ref; 

        
        while($field_ref = $sth->fetchrow_hashref){ 
            chomp $field_ref->{email}; #new.. 	
            unless(exists($banned_list{$field_ref->{email}})){
                my $pin = make_pin(-Email => $field_ref->{email}); 
                
                my ($e_name, $e_domain) = split('@', $field_ref->{email}); 

                print SENDINGFILE "\n" . 
                                      join('::', $field_ref->{email},
                                                 $e_name,
                                                 $e_domain, 
                                                 $pin, 
                                                 $field_ref->{list},
                                                 $list_names{$field_ref->{list}},
                                                 $n_msg_id, 
                                                 
                                          ); 
                
                $total++;
                
                #print SENDINGFILE "\n". $field_ref->{email} . '::' . $pin . '::' . $field_ref->{list};		
                
                foreach(@merge_fields){ 
                    chomp $field_ref->{$_}; 
                    print SENDINGFILE '::' . $field_ref->{$_};
                }
            }
        }				
	
		$sth->finish;	
	}
	close(SENDINGFILE) 
		or croak ("$DADA::Config::PROGRAM_NAME $DADA::Config::VER Error - could not close temporary sending  file '$sending_file' successfully"); 

	return ($sending_file, $total); 	

}



=pod

=head2 my ($unique, $duplicate) = unique_and_duplicate(-List=> $list, -New_List => \@new_list); 

This is used to mass add and remove email addresses from a list, it takes a 
array ref full of new email addresses, and see if they are already in the list. 

my ($unique_ref, $duplicate_ref) = weed_out_subscribers(-List     => $list, 
														
														-Type     => 'list', 
														-New_List => \@addresses, 
														);

=cut

 
sub unique_and_duplicate { 

my $self = shift; 

my %args = ( 
-New_List      => undef, 
-Type          => 'list', 
@_,
); 


# first thing we got to do is to make a lookup hash. 
my %lookup_table; 
my $address_ref = $args{-New_List}; 
my $list        = $self->{fields}->{-List};

if($list and $address_ref){

foreach(@$address_ref){$lookup_table{$_} = 0}
				 
my $email; 

my $sth = $self->{dbh}->prepare("SELECT email FROM " . $self->{sql_params}->{subscriber_table} .  
	                            " WHERE list = ? 
	                              AND list_type = ?
	                              AND  list_status   = 1");    
   $sth->execute($self->{list}, $args{-Type}) or croak "cannot do statment! $DBI::errstr\n";     
    while((my $email) = $sth->fetchrow_array){  
		chomp($email);
		$lookup_table{$email} = 1 if(exists($lookup_table{$email})); 
		#nabbed it, 
	}
	$sth->finish;	    
    
    
#lets lookie and see what we gots.     
my @unique; 
my @double; 
my $value; 



foreach(keys %lookup_table){
	$value = $lookup_table{$_}; 
	if($value == 1){ 
		push(@double, $_)
	}else{ 
		push(@unique, $_) 
	}
}

#again, harmony is restored to the force.
return(\@unique, \@double); 
}else{ 

carp("$DADA::Config::PROGRAM_NAME $DADA::Config::VER Error: No list name or list reference provided");
return undef;
}

}




sub tables { 
	my $self = shift; 
	my @tables = $self->{dbh}->tables();
	return \@tables;
}

sub columns { 
	my $self = shift; 
	my $sth = $self->{dbh}->prepare("SELECT * FROM " . $self->{sql_params}->{subscriber_table} ." where (1 - 0)");    
	$sth->execute() or croak "cannot do statment! $DBI::errstr\n";  
	my $i; 
	my @cols;
	for($i = 1; $i <= $sth->{NUM_OF_FIELDS}; $i++){ 
		push(@cols, $sth->{NAME}->[$i-1]);
	} 
	$sth->finish;
	return \@cols;
}

sub merge_fields { 
	my $self = shift;
	my $l = $self->columns;
	my %omit_fields = (id          => 1,
					   email       => 1,
					   list        => 1,
					   list_type   => 1,
					   list_status => 1);	

	my @r;
	foreach(@$l){ 
		push(@r, $_) unless defined($omit_fields{$_});
	}
	return \@r;
}






sub remove_this_listtype {
	my $self = shift; 
	my %args = (-Type => 'list', @_); 
	my $sth = $self->{dbh}->prepare("DELETE FROM " . $self->{sql_params}->{subscriber_table} .
		                            " WHERE list    = ?
		                              AND list_type = ?");    
		   $sth->execute($self->{list}, $args{-Type}) or croak "cannot do statment! $DBI::errstr\n";   
		   $sth->finish;
}




sub can_use_global_list_sending { 

	my $self = shift; 
	return 1; 
}




sub can_use_global_black_list { 

	my $self = shift; 
	return 1; 

}




sub can_use_global_unsubscribe { 

	my $self = shift; 
	return 1; 

}




sub can_filter_subscribers_through_blacklist { 
	
	my $self = shift; 
	return 1; 
}





1;


=pod

=head1 COPYRIGHT 

Copyright (c) 1999-2007 Justin Simoni All rights reserved. 

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.

=cut