track-1.0/ 0000755 0000000 0000000 00000000000 12600700357 011154 5 ustar root root track-1.0/nav.tt2.part 0000644 0000000 0000000 00000001107 12600662510 013335 0 ustar root root ##### This line replaces the existing test for action == 'admin'
[% IF action == 'admin' || action_type == 'admin' || action == 'editfile' || custom_action == 'tracking' %]
##### Insert this piece in the tabs list for list action == 'admin'
[% IF is_priv || is_listmaster %]
[% IF list_conf.merge_feature == 'on' %]
[% IF action == 'lca' && custom_action == 'track' %][% SET class = 'active' %][% ELSE %][% SET class = '' %][% END %]
[% END %]
[% END %]
track-1.0/nav.tt2.example 0000644 0000000 0000000 00000027052 12600662470 014036 0 ustar root root
[% IF action == 'create_list_request' %]
[%# Remove the class "menu-icon" to get rid of menu icon.
Take out inner text of span element to just have icon alone. ~%]
[% END %]
[% IF action == 'serveradmin' or action == 'skinsedit' or action == 'edit_config' or action == 'get_pending_lists' or action == 'get_closed_lists' or action == 'get_latest_lists' or action == 'get_inactive_lists' %]
[%# Remove the class "menu-icon" to get rid of menu icon.
Take out inner text of span element to just have icon alone. ~%]
[% END %]
[% IF action == 'lists' %]
[%|loc%]Index of lists[%END%]
[%# Remove the class "menu-icon" to get rid of menu icon.
Take out inner text of span element to just have icon alone. ~%]
[% letters = ['a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z','others' ] %]
[% END %]
[% IF action == 'admin' || action_type == 'admin' || action == 'editfile' || action == 'lca' %]
[%|loc%]Basic Operations[%END%]
[%# Remove the class "menu-icon" to get rid of menu icon.
Take out inner text of span element to just have icon alone. ~%]
[% END %]
[% IF action == 'compose_mail' %]
[%# Remove the class "menu-icon" to get rid of menu icon.
Take out inner text of span element to just have icon alone. ~%]
[% END %]
track-1.0/Tracking.pm 0000644 0000000 0000000 00000020444 12601125130 013247 0 ustar root root #============================================================= -*-Perl-*-
#
# Template::Plugin::Tracking
#
# DESCRIPTION
# Template Toolkit plugin module for Sympa mailing lists
# Provide tracking for mailmerge messages
#
# AUTHOR
# Steve Shipway
#
# This plugin provides both functions for adding the tracking to mailmerged
# messages, and also the functions to query the database used by the web
# frontend.
#
#============================================================================
# 0.1 : first working version
# 0.2 : extra checks for when not in content-type=~/html/ and non-null return
# more helpful comment code
# multiple ways to seek out the message-id header content
# better defaults for base_url etc
# Tracking.unsubscribe function
# 0.3 : Support Sympa 6.1.21 and later with part.type instead of headers.content-type
# 0.4 : Fix unsubscribe function HTML detection test
# 0.5 : Remove trailing %0A from RCPT_ID
# 1.0 : Sympa 6.2 support
#============================================================================
package Sympa::Template::Plugin::Tracking;
use base 'Template::Plugin';
use Sys::Hostname;
use Digest::MD5;
use URI::Escape;
use strict;
our $VERSION = 1.00;
sub load {
my ($class, $context) = @_;
return $class;
}
sub new {
my ($class, $context, @args) = @_;
# This increments by one for each link we track
my $LINK_ID = 1;
# This is the ID for this message
my $MSG_ID = '';
# This is the ID for this recipient
my $RCPT_ID = 'default';
# The base tracking URL
my $BASE_URL = '/cgi-bin/track.cgi';
# The mailing list address
my $LIST_NAME = 'default';
eval { require Sympa::DatabaseManager; };
if($args[0] and !ref $args[0] and $args[0]=~/^http/) {
$BASE_URL = $args[0] ;
} elsif( $context->{STASH}{'wwsympa_url'} ) {
$BASE_URL = $context->{STASH}{'wwsympa_url'};
$BASE_URL =~ s/sympa$//;
$BASE_URL .= 'cgi-bin/track.cgi';
} else {
# Set up BASE_URL from info in the stash
$BASE_URL = 'http://'.hostname().'/cgi-bin/track.cgi';
}
# Set up default list name
if( $context->{STASH}{listname} ) { # for mail context
$LIST_NAME = $context->{STASH}{listname}.'@'.$context->{STASH}{robot};
} else {
$LIST_NAME = "Unknown";
}
# Define MSG_ID based on message details as held in
# the context.
if($context->{STASH}{headers}{'message-id'}) {
$MSG_ID = Digest::MD5::md5_base64($context->{STASH}{headers}{'message-id'});
} elsif($context->{STASH}{'message-id'}) {
$MSG_ID = Digest::MD5::md5_base64($context->{STASH}{'message-id'});
} elsif($context->{STASH}{'messageid'}) {
$MSG_ID = Digest::MD5::md5_base64($context->{STASH}{'messageid'});
} else {
$MSG_ID = "Unknown";
}
# The message subject
my $SUBJECT = $context->{STASH}{headers}{'subject'} || 'None';
if( defined $args[0] and ref $args[0] =~ /HASH/ ) {
$MSG_ID = $args[0]->{msgid} if($args[0]->{msgid});
$BASE_URL = $args[0]->{url} if($args[0]->{url});
$SUBJECT = $args[0]->{subject} if($args[0]->{subject});
$LIST_NAME = $args[0]->{list}.'@'.$context->{STASH}{robot} if($args[0]->{list});
}
$MSG_ID =~ s/[\/&?@%'"\\+]/_/g; # just in case
# Define RCPT_ID for this recipient based on stash
$RCPT_ID = $context->{STASH}{user}{escaped_email};
$RCPT_ID = "unknown" if(!$RCPT_ID);
$RCPT_ID =~ s/%0A//ig; # remove any trailing newlines
# IF we are in message mode...
# If MSG_ID not already in database, then do initial setup for this
# write msgs=($MSG_ID,$subject,$LIST_NAME,NOW())
if( $MSG_ID ) {
my $sql = "INSERT into `tracking_msgs` (`msg_id`,`subject`,`list_name`,`sent`) VALUES(?,?,?,NOW())";
my( $sdm ) = Sympa::DatabaseManager->instance();
if( $sdm ) {
my( $sth ) = $sdm->do_prepared_query($sql,$MSG_ID,$SUBJECT,$LIST_NAME);
}
# Store it here, since filters cant see the object
$context->{STASH}{Tracking} = { MSG_ID=>$MSG_ID,
RCPT_ID=>$RCPT_ID, LINK_ID=>1, BASE_URL=>$BASE_URL };
# Define a new filter that can be used later
# This filter handles general replacement of links with tracked links
$context->define_filter('Tracking', [ \&track_ff => 1 ]);
}
return bless { _CONTEXT=>$context, SUBJECT=>$SUBJECT, MSG_ID=>$MSG_ID,
RCPT_ID=>$RCPT_ID, BASE_URL=>$BASE_URL, LIST_NAME=>$LIST_NAME, LINK_ID=>$LINK_ID
}, $class;
}
##########################################################
# The below functions are used when in message mode
# Set up HTML for base tracking - 1px image link, etc
# [% Tracking.base %]
sub base {
my($obj,@args) = @_;
my($rv) = '';
return $rv if(!$obj->{MSG_ID}); # not message mode
if($obj->{_CONTEXT}{STASH}{'part'}) {
return $rv if($obj->{_CONTEXT}{STASH}{'part'}{'type'}!~/html/);
} else {
return $rv if($obj->{_CONTEXT}{STASH}{'headers'}{'content-type'}!~/html/);
}
# Set up database entry for MSG_ID, RCPT_ID
my $sql = "INSERT into `tracking_clicks` (`msg_id`,`rcpt_id`,`link_id`,`clicks`,`last_click`,`first_click`) VALUES(?,?,0,0,NULL,NULL)";
my( $sdm ) = Sympa::DatabaseManager->instance();
if(!$sdm) {
$rv .= "";
} else {
my( $sth ) = $sdm->do_prepared_query($sql,$obj->{MSG_ID},$obj->{RCPT_ID});
if($sth) {
if(! $sth->rows ) { # returns 0 for error, 0E0 (==true) for no rows affected
$rv .= "\n";
}
} else {
$rv .= "";
}
}
# Base tracking links
$rv = '\n";
return $rv;
}
sub unsubscribe {
my($obj,$msg) = @_;
my($rv) = '';
my($url);
$url = $obj->{_CONTEXT}{STASH}{'wwsympa_url'}.'/auto_signoff/'.$obj->{_CONTEXT}{STASH}{'listname'}.'/'.$obj->{_CONTEXT}{STASH}{'user'}{'escaped_email'};
return $rv if(!$url);
if(($obj->{_CONTEXT}{STASH}{'part'}
and $obj->{_CONTEXT}{STASH}{'part'}{'type'}=~/html/)
or $obj->{_CONTEXT}{STASH}{'headers'}{'content-type'}=~/html/) {
$msg = "Click here to unsubscribe from this list" if(!$msg);
$rv = "$msg";
} else {
$msg = "To unsubscribe from this list, visit" if(!$msg);
$rv = "$msg $url";
}
return $rv;
}
# Replace links with tracked links
sub _track_links {
my($text, $context) = @_;
my($linkdest);
my(%links) = ();
my($MSG_ID) = $context->{STASH}{Tracking}{MSG_ID};
my($RCPT_ID) = $context->{STASH}{Tracking}{RCPT_ID};
my($linksql) = "INSERT into `tracking_links` (`msg_id`,`link_id`,`url`) VALUES(?,?,?)";
my($clicksql) = "INSERT into `tracking_clicks` (`msg_id`,`link_id`,`rcpt_id`,`clicks`,`last_click`,`first_click`) VALUES(?,?,?,0,NULL,NULL)";
my($rows) = 0;
my($LINK_ID) = $context->{STASH}{Tracking}{LINK_ID};
my($BASE_URL) = $context->{STASH}{Tracking}{BASE_URL};
my($lsth,$csth) = (undef,undef);
return $text if(!$MSG_ID or !$RCPT_ID); # not message mode or not supported
if($context->{STASH}{'part'}) {
return $text if($context->{STASH}{'part'}{'type'}!~/html/);
} else {
return $text if($context->{STASH}{'headers'}{'content-type'}!~/html/);
}
# Check we have a working database connection
my( $sdm ) = Sympa::DatabaseManager->instance();
if(!$sdm) {
$text .= "\n\n";
return $text;
}
# Identify all the links in the text block
$LINK_ID = 1 if(!$LINK_ID);
while( $text =~ s/href="?(https?:\/\/[^\s">]+)"?/href="\001$LINK_ID\001"/i ) {
$links{$LINK_ID}=$1;
$LINK_ID += 1;
}
foreach my $lnk ( keys %links ) {
$linkdest = $links{$lnk};
#$text .= "\n";
# Save to database if not already there links=($MSG_ID,$lnk,$linkdest)
$lsth = $sdm->do_prepared_query($linksql,$MSG_ID,$lnk,$linkdest);
# Save to database clicks=($MSG_ID,$lnk,$RCPT_ID,0,0,0)
$csth = $sdm->do_prepared_query($clicksql,$MSG_ID,$lnk,$RCPT_ID);
if($csth) {
if(! $csth->rows ) {
$text .= "\n";
}
}
$text =~ s/\001$lnk\001/$BASE_URL\/$MSG_ID\/$RCPT_ID\/$lnk/;
}
$context->{STASH}{Tracking}{LINK_ID} = $LINK_ID;
return $text;
}
# This defines a filter, Tracking, that adds tracking to any URLs in HTML A tags
# [% FILTER Tracking %] Click here [% END %]
sub track_ff {
# These are the params passed at plugin load time
my ($context, @args) = @_;
return sub {
# This is the param passed at filter execute time
my $text = shift;
_track_links($text, $context, @args);
}
}
1;
__END__
track-1.0/help_tracking.tt2 0000644 0000000 0000000 00000006003 12600677736 014436 0 ustar root root
[%|helploc%]Message Tracking documentation[%END%]
To use the Tracking module, you first need to enable the merge_feature on your list.
When the merge_feature is enabled, the Tracking tab will be displayed in the Admin web interface. This will display
the details for any tracked messages.
To send a tracked message, you need to add three specific items of TT2 code to the message.
Add the Plugin
In all cases, you should start the message by including the Tracking plugin.
[% TAGS [- -] %]
[% USE Tracking -%]
Note that, if your tracking CGI is not installed at /cgi-bin/track.cgi,
on the same host as the Sympa software, then you need to specify the tracking
redirect URL here:
[% USE Tracking( "https://myserver/sympa/cgi-bin/mytrack.cgi") -%]
Add the Read-track item
This should be done once (and only once) in the message, at any point after the USE statement. Generally, you should place it somewhere right at the end of the message.
[% Tracking.base -%]
Indicate trackable links, if any
This is optional, and can be done any number of times after the USE statement. You should not place any other FILTER, INCLUDE or PROCESS directives within the trackable section.
[% FILTER Tracking -%]
... place your message and any embedded HTML links in here ...
[% END -%]
Create an Unsubscribe link
You can generate an unsubscribe link as a URL, or by using a special
function. The special function will generate a visible URL if the message
is viewed in a non-HTML-capable mail reader, and a clickable link otherwise.
You should make sure that your Unsubscribe URL is not inside the FILTER
block, else it will not work.
If you do not wish to receive this message,
then you can [% Tracking.unsubscribe('Unsubscribe') %].
If you do not wish to receive this message,
then click <A href=[% wwsympa_url %]/auto_signoff/[% listname %]/[% user.escaped_email %]>here</A>
Example
[% USE Tracking -%]
Hello, [% user.gecos %]!
[% FILTER Tracking -%]
This is a wonderful offer for you to purchase. Simply click <A href=http://spammy.com/>here</A> to buy our all-new Chocolate Fireguards!
[% END -%]
<hr>
To be removed from this list, click <A href=[% wwsympa_url %]/auto_signoff/[% listname %]/[% user.escaped_email %]>here</A>
[% Tracking.base -%]
[- TAGS [% %] -]
In this example, the spammy.com link is tracked, but the unsubscribe link is not. It also keeps track of message views, thanks to the Tracking.base at the end. Note that only HTML links (IE, the HREF in an A tag) are modified and tracked; a URL simply placed in text is not modified.
[% IF is_owner || is_listmaster || is_editor %]
[% IF list_conf.merge_feature == 'on' %]
This is the tracking info for list [% list %]@[% robot %].
This shows any messages sent with the Tracking module enabled.
To learn how to use the tracking module, see the [%|helploc(path_cgi)%]tracking documentation[%END%].
[% IF track_cmd == 'delete' && ! errmsg %]
[% SET track_msgid = '' %]
[% END %]
[% IF track_msgid %]
[% IF track_linkid %]
[% IF message_count == 0 %]
There are no messages with tracking in the logs.
[% END %]
[% END %]
[% IF errmsg and errmsg != '' %]
ERROR: [% errmsg %]
SQL: [% message_sql %]
[% END %]
[% ELSE %]
This list does not have the Merge Feature enabled, and so cannot do tracking.
[% END %]
[% ELSE %]
You do not have sufficient rights to view tracking data.
[% END %]
track-1.0/track.pm 0000644 0000000 0000000 00000017620 12601120735 012621 0 ustar root root #============================================================= -*-Perl-*-
#
# DESCRIPTION
# Sympa 6.2 list custom action to reporting on tracking plugin.
# This is installed into custom_actions
#
# AUTHOR
# Steve Shipway
#
#
#============================================================================
# 0.1 : first working version
# 0.2 : extra checks for when not in content-type=~/html/ and non-null return
# more helpful comment code
# multiple ways to seek out the message-id header content
# better defaults for base_url etc
# Tracking.unsubscribe function
# 0.3 : Support Sympa 6.1.21 and later with part.type instead of headers.content-type
# 0.4 : Fix unsubscribe function HTML detection test
# 0.5 : Remove trailing %0A from RCPT_ID
# 1.0 : Sympa 6.2 support, changes to custom_actions format
#============================================================================
package track_plugin;
use strict;
our $VERSION = 1.00;
##########################################################
# These functions are used in web frontend mode
# Fetch the list of all known messages
sub listmsgs($$) {
my($stashref) = shift;
my($listname) = shift;
my($rv) = '';
my(@messages) = ();
my($sth,$sql,$ret);
my(@msg);
$sql = "SELECT m.`msg_id`,m.`subject`,m.`sent`,count(c.`rcpt_id`) numsent from `tracking_msgs` m,`tracking_clicks` c where m.`list_name` = ? AND c.`msg_id` = m.`msg_id` AND c.`link_id` = 0 GROUP BY m.`msg_id` ORDER BY m.`sent` DESC LIMIT 1000";
$sth = SDM::do_prepared_query($sql,$listname);
if( !$sth ) {
$stashref->{errmsg} .= "Database error";
$stashref->{message_sql} = $sql;
return;
}
$ret = $sth->fetchall_arrayref;
if( !$ret or $sth->err ) {
$stashref->{errmsg} .= "Query error: ".$sth->errstr;
$stashref->{message_sql} = $sql;
return;
}
foreach ( @$ret ) {
push @messages, { id=>($_->[0]), subject=>($_->[1]), sent=>($_->[2]), num=>($_->[3]) };
}
$stashref->{message_list} = \@messages;
$stashref->{message_count} = $#messages + 1;
$stashref->{message_sql} = $sql;
return;
}
# Fetch the details for a specific message
sub getmsg($$$) {
my($stashref,$listname,$msgid) = @_;
my($rv) = '';
my(%msgdata) = { id=>$msgid, subject=>'Unknown', sent=>'Unknown', num=>0, seen=>0, rate=>'Unknown' };
my($sql,$ret,$sth);
$sql = "SELECT m.`msg_id`,m.`subject`,m.`sent`,(SELECT count(*) from `tracking_clicks` c WHERE c.`msg_id` = m.`msg_id` and c.`link_id` = 0) numsent, (SELECT count(*) from `tracking_clicks` c WHERE c.`msg_id` = m.`msg_id` and c.`link_id` = 0 and c.`clicks`>0) numseen from `tracking_msgs` m WHERE m.`msg_id` = ? LIMIT 1";
$sth = SDM::do_prepared_query($sql,$msgid);
if( !$sth ) {
$stashref->{errmsg} .= "Database error";
$stashref->{message_sql} = $sql;
return;
}
$ret = $sth->fetchrow_arrayref();
if($ret and @$ret) {
$msgdata{id} = $ret->[0];
$msgdata{subject} = $ret->[1];
$msgdata{sent} = $ret->[2];
$msgdata{num} = $ret->[3];
$msgdata{seen} = $ret->[4];
$msgdata{rate} = $ret->[4]/$ret->[3]*100.0 if($ret->[3]);
}
$stashref->{'msg'} = \%msgdata;
$stashref->{'message_sql'} = $sql;
return;
}
# Fetch the details for a specific link
sub getlink($$$$) {
my($stashref,$listname,$msgid,$linkid) = @_;
my($rv) = '';
my(%lnkdata) = ();
my($sql,$ret,$sth);
$linkid = 0 if($linkid eq 'none');
$sql = "SELECT l.`link_id`,l.`url`,(SELECT count(*) from `tracking_clicks` c WHERE c.`msg_id` = ? and c.`link_id` = ? and c.`clicks`>0 ) num from `tracking_links` l WHERE l.`msg_id` = ? and l.`link_id` = ? LIMIT 1";
$sth = SDM::do_prepared_query($sql,$msgid,$linkid,$msgid,$linkid);
if( !$sth ) {
$stashref->{errmsg} .= "Database error";
$stashref->{message_sql} = $sql;
return;
}
$ret = $sth->fetchrow_arrayref();
if($ret and @$ret) {
$lnkdata{id} = $ret->[0];
$lnkdata{url} = $ret->[1];
$lnkdata{seen} = $ret->[2];
}
$stashref->{message_sql} = $sql;
$stashref->{'lnk'} = \%lnkdata;
return;
}
# List all links for a specific message
sub listlinks($$$) {
my($stashref,$listname,$msgid) = @_;
my($rv) = '';
my(@links) = ();
my($sql,$ret,$sth);
$sql = "SELECT l.`link_id`, l.`url`, ( SELECT count(*) from `tracking_clicks` c where c.`msg_id` = ? and c.`link_id` = l.`link_id` and c.`clicks` > 0 ) clickers "
."FROM `tracking_links` l "
."WHERE l.`msg_id` = ? and l.`link_id` > 0 "
."ORDER BY l.`link_id` LIMIT 1000 " ;
$sth = SDM::do_prepared_query($sql,$msgid,$msgid);
if( !$sth ) {
$stashref->{errmsg} .= "Database error";
$stashref->{message_sql} = $sql;
return;
}
$ret = $sth->fetchall_arrayref();
if( !$ret or $sth->err ) {
$rv = "Database error: ".$sth->errstr;
$stashref->{message_sql} = $sql;
$stashref->{'errstr'} .= $rv;
return;
}
foreach ( @$ret ) {
push @links, { id=>($_->[0]), url=>($_->[1]), rcpt=>($_->[2]), first=>($_->[3]) };
}
$stashref->{link_list} = \@links;
$stashref->{message_sql} = $sql;
return;
}
# List all clicks on a message/link
# If link is null then for message
sub listclicks($$$$) {
my($stashref,$listname,$msgid,$linkid) = @_;
my($rv) = '';
my(@clicks) = ();
my($sql,$ret,$sth);
$linkid = 0 if(!defined $linkid or $linkid eq 'none');
$sql = "SELECT c.`rcpt_id`,c.`clicks`,c.`first_click`,c.`last_click` from `tracking_clicks` c where c.`msg_id` = ? and c.`link_id` = ? and c.`clicks` > 0 ";
$sth = SDM::do_prepared_query($sql,$msgid,$linkid);
if( !$sth ) {
$stashref->{errmsg} .= "Database error";
$stashref->{message_sql} = $sql;
return;
}
$ret = $sth->fetchall_arrayref();
if( !$ret or $sth->err ) {
$rv = "Database error: ".$sth->errstr;
$stashref->{message_sql} = $sql;
$stashref->{'errmsg'} .= $rv;
return;
}
foreach ( @$ret ) {
my $rcpt = $_->[0];
$rcpt =~ s/\%40/\@/g;
push @clicks, { rcpt=>$rcpt, count=>($_->[1]), first=>($_->[2]), last=>($_->[3]) };
}
$stashref->{click_list} = \@clicks;
$stashref->{message_sql} = $sql;
return;
}
# Delete a message entirely from the database, by messageid
# Maybe this should ensure the message relates to this list?
sub delmsg($$$) {
my($stashref,$listname,$msgid) = @_;
my($rv) = '';
my($sql,$sth);
my(@sql) = ();
@sql = (
"DELETE from `tracking_clicks` where `msg_id` = ?",
"DELETE from `tracking_links` where `msg_id` = ?",
"DELETE from `tracking_msgs` where `msg_id` = ?",
);
foreach $sql ( @sql ) {
$sth = SDM::do_prepared_query($sql,$msgid);
if( !$sth ) {
$stashref->{errmsg} .= "Database error";
$stashref->{message_sql} = $sql;
return;
}
if( $sth->err ) {
$rv = "Database error: ".$sth->errstr;
$stashref->{message_sql} = $sql;
$stashref->{'errmsg'} .= $rv;
return;
}
}
return;
}
####################################################################
sub process {
my( $listref ) = shift; # reference to list object
my( $action ) = shift; # sub-action for this lca
my( $track_msgid ) = shift;
my( $track_linkid ) = shift;
my( %stash ) = ();
my( $listname );
my($rv);
return 'home' if(!ref $listref);
$listname = $listref->{'name'}.'@'.$listref->{'domain'};
$action = 'list' if(!$action or $action!~/^(list|show|delete|csv)$/);
$stash{'track_cmd'} = $action;
$track_linkid = '' if($track_linkid eq 'none');
$track_msgid = '' if($track_msgid eq 'none');
$stash{'track_msgid'} = $track_msgid if($track_msgid);
$stash{'track_linkid'} = $track_linkid if(defined $track_linkid);
$stash{'errmsg'} = "";
$stash{next_action} = "lca:track";
eval { require SDM; };
# Identify what our status is.
if( $action eq 'delete' ) {
delmsg(\%stash,$listname,$track_msgid) if($track_msgid);
listmsgs(\%stash,$listname);
} else {
if($track_msgid) {
getmsg(\%stash,$listname,$track_msgid);
if($track_linkid) {
getlink(\%stash,$listname,$track_msgid,$track_linkid);
listclicks(\%stash,$listname,$track_msgid,$track_linkid);
} elsif( $action eq 'list' ) {
listlinks(\%stash,$listname,$track_msgid);
} else {
listclicks(\%stash,$listname,$track_msgid,0);
}
} else {
listmsgs(\%stash,$listname);
}
}
return \%stash;
}
1;
track-1.0/INSTALL 0000644 0000000 0000000 00000006615 12600662173 012220 0 ustar root root Sympa Mail Tracking v1.0
Steve Shipway 2015
steve@steveshipway.org
How to Install
--------------
Note that different people will have installed files into different locations. In this example, I will use the locations where we have installed, but your system may be different.
Install Files
-------------
track.cgi
This is the redirect script that handles the actual tracking
Edit this to have the correct database credentials for the Sympa database (find these in the sympa.conf)
Copy this file to /var/www/cgi-bin (your web server /cgi-bin) and ensure it can be accessed through the web by everyone.
tracking.tt2
This is the custom action web template for the Sympa stats interface
Copy this file to web_tt2 (your sympa base web_tt2 directory)
help_tracking.tt2
This is the help page for the web interface.
Copy this file to web_tt2 (your sympa base web_tt2 directory)
Tracking.pm
This is the TT2 Plugin that does all the work.
Copy this to /usr/share/sympa/lib/Template/Plugin (under your Sympa perl library directory). You may need to create the Template/Plugin directory under lib, if it does not yet exist.
Patch Web Template
------------------
nav.tt2.part
This is the additional sections for the nav.tt2 to add the new Tracking tab.
Copy your default/web_tt2/nav.tt2 (default nav.tt2) to web_tt2 (base web_tt2 dir) if it is not already there.
Edit your web_tt2/nav.tt2 to make the changes as in nav.tt2.part. These changes are
a) Change the IF statement to add the extra criteria, and
b) Add the extra tab at the end of the
See nav.tt2.example for a modifed default nav.tt2, if you have not made any other changes to nav.tt2 on your system
Create Database Tables
----------------------
tables.sql
This contains the SQL to create the tracking tables in the Sympa database.
You may wish to edit this SQL, for example to change the database engine or encoding character set. This is only tested against MySQL but with modification should work on any other database backend that supports subselects.
Run this SQL against your Sympa database, which should create the required three tables.
# mysql sympa < tables.sql
Test
----
To test, create a new mailing list and enable merge_feature on the list.
When logged in as list owner, editor or listmaster you should see a new tab, Tracking, in the Admin page.
Add a test user to the list. Send a message to the list containing at least the following:
[% USE Tracking %]
[% Tracking.base %]
[% FILTER Tracking %]
This is a link http://www.sympa.org/
[% END %]
Message Ends
If you have installed correctly, the message should be delivered to the list.
If a blank message arrives, this is probably a TT2 error. Check your TT2 code and that the Tracking.pm file was correctly installed. Check your sympa.log for TT2 errors.
When the message is distributed, check that the link has been changed to something like http://yourserver/cgi-bin/track.cgi/ABCD/1
In the Sympa web interface, click the Tracking tab, and you should see the message listed. You can view the details for the message and links within it.
Open the message in your mail reader. If you have image download, this should register the read in the Tracking page. Click on the link and you should be sent to the Sympa website, but the link click count will increase in the Tracking page.
Send Feedback
-------------
This is an early version, so please send feedback to steve@steveshipway.org
track-1.0/track.cgi 0000755 0000000 0000000 00000007141 12600660203 012745 0 ustar root root #!/usr/bin/perl
#vim:ts=4
#
# track.cgi : Tracking script to go with the tracking TT2 plugin
#
# S. Shipway 2013
#
# Ver 0.1 : initial
# 0.2 : clicking a link sets views to 1 if it was 0
# 0.3 : Handle missing click records
# 1.0 : Support for Sympa 6.2
use strict;
use CGI;
use DBI;
use DBD::mysql;
my($VERSION) = "0.3";
my $q = new CGI;
my($DBHOST) = 'localhost';
my($DBPORT) = 3306;
my($DBNAME) = 'sympa';
my($DBUSER) = 'sympa';
my($DBPASS) = 'put your password here';
sub error_page($) {
my($msg) = $_[0];
print $q->header();
print $q->start_html();
print $q->h1("Error")."\n";
print $q->p("The tracking script cannot redirect you: $msg");
print $q->end_html();
}
# How many views for this message?
sub getviews($$) {
my($msgid,$rcptid) = @_;
my($dbh,$sql,$views);
$dbh = DBI->connect("DBI:mysql:database=$DBNAME;host=$DBHOST;port=$DBPORT", $DBUSER, $DBPASS);
if(!$dbh) { return 0; }
$sql = "SELECT `clicks` from `tracking_clicks` WHERE `msg_id` = '$msgid' and `link_id` = 0 and `rcpt_id` = '$rcptid'";
( $views ) = $dbh->selectrow_array($sql);
if($dbh->err or !defined $views ) { return 0; }
return $views;
}
# Fetch the URL to go to, and update click count
sub clickon($$$) {
my($msgid,$linkid,$rcptid) = @_;
my($sql,$clicks,$first);
my($dbh,$url);
$dbh = DBI->connect("DBI:mysql:database=$DBNAME;host=$DBHOST;port=$DBPORT", $DBUSER, $DBPASS);
if(!$dbh) {
error_page("Cannot connect to database");
exit(0);
}
$rcptid =~ s/\@/\%40/;
$rcptid =~ s/\%0A//ig;
$rcptid =~ s/\n//g;
$sql = "SELECT `clicks`,`first_click` from `tracking_clicks` WHERE `msg_id` = '$msgid' and `link_id` = $linkid and `rcpt_id` = '$rcptid'";
($clicks,$first) = $dbh->selectrow_array($sql);
if($dbh->err) {
error_page("Error seeking tracking record: ".$dbh->errstr." $sql");
exit(0);
}
if(!defined $clicks) {
# error_page("Cannot find tracking record $sql");
# exit(0);
$sql = "INSERT INTO `tracking_clicks` (`msg_id`,`link_id`,`rcpt_id`,`clicks`,`last_click`,`first_click`) VALUES ('$msgid',$linkid,'$rcptid',1,NOW(),NOW())";
} else {
if($clicks) {
$sql = "UPDATE `tracking_clicks` SET `clicks`=".($clicks+1).", `last_click`=NOW() WHERE `msg_id` = '$msgid' and `link_id` = $linkid and `rcpt_id` = '$rcptid'";
} else {
$sql = "UPDATE `tracking_clicks` SET `clicks`=1, `last_click`=NOW(), `first_click`=NOW() WHERE `msg_id` = '$msgid' and `link_id` = $linkid and `rcpt_id` = '$rcptid'";
}
}
$dbh->do($sql);
if($dbh->err) {
error_page("Cannot find tracking record: ".$dbh->errstr." $sql");
exit(0);
}
return '' if(!$linkid); # was just read log
$sql = "SELECT `url` from `tracking_links` WHERE `msg_id` = '$msgid' and `link_id` = $linkid";
$url = $dbh->selectrow_array($sql);
return $url;
}
# Resirect to the new place
sub redirect_to($$$) {
my($msgid,$linkid,$rcptid) = @_;
my($url) = '';
my( $views );
if( getviews($msgid,$rcptid) == 0 ) {
clickon($msgid,0,$rcptid);
}
$url = clickon($msgid,$linkid,$rcptid);
if($url) {
print $q->redirect($url);
} else {
error_page("Expired link");
}
}
# Return 1x1px image, and update click count
sub read_count($$) {
my($msgid,$rcptid) = @_;
clickon($msgid,0,$rcptid);
print $q->header({ -type=>"image/gif", -expires=>"now" });
binmode STDOUT;
print "GIF89a\001\0\001\0\200\0\0\0\0\0\377\377\377!\371\004\0\0\0\377\0,\0\0\0\0\001\0\001\0\0\002\002L\001\0;";
}
###########################################################
# MAIN
if( $q->path_info() =~ /\/?(\S+)\/(\S+)\/(\d+)/ ) {
redirect_to($1,$3,$2);
} elsif( $q->path_info() =~ /\/?(\S+)\/(\S+)/ ) {
read_count($1,$2);
} else {
error_page("Invalid parameters");
}
exit 0;
track-1.0/tables.sql 0000644 0000000 0000000 00000001515 12600675155 013160 0 ustar root root CREATE TABLE IF NOT EXISTS `tracking_clicks` (
`msg_id` varchar(32) NOT NULL,
`link_id` int(11) NOT NULL,
`rcpt_id` varchar(32) NOT NULL,
`clicks` int(11) NOT NULL DEFAULT '0',
`first_click` datetime DEFAULT NULL,
`last_click` datetime DEFAULT NULL,
UNIQUE KEY `msg_id` (`msg_id`,`link_id`,`rcpt_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `tracking_links` (
`msg_id` varchar(32) NOT NULL,
`link_id` int(11) NOT NULL,
`url` varchar(256) NOT NULL,
UNIQUE KEY `msg_id` (`msg_id`,`link_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `tracking_msgs` (
`msg_id` varchar(32) NOT NULL,
`subject` varchar(64) NOT NULL DEFAULT 'None',
`list_name` varchar(64) NOT NULL,
`sent` datetime NOT NULL,
UNIQUE KEY `msg_id` (`msg_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
track-1.0/README 0000644 0000000 0000000 00000000707 12600660354 012042 0 ustar root root Sympa Message Tracking System
Version 1.0
Steve Shipway, steve@steveshipway.org
Sep 2015
Summary
-------
This is a mod for Sympa. It is based on Sympa 6.2.4 but should work on any of the 6.2 branch.
It provides functionality allowing you to track message views and link click-throughs on messages sent via a Mail-Merge-enabled Sympa mailing list.
This is an initial version, so more reporting functionality should be forthcoming in later versions.