脚本需求,由于公司有帐户一帐户等级对应表里面只有等级跟经验对照表,但该数据并没有放入数据库里,库里面仅可查询到用户跟经验两个信息,最近有合作商需要我们实现把用户名、经验、等级并每天定时发送一份邮件。
实现方法,数据库查询出来的数据是经过排序的,于是使用数组来取帐户信息文件account.txt,之后通过key值到经验文件exp.txt取相关数据,由于有部分account.txt数据在exp.txt里面并没有对应的值,但此值也需要输出,所以输出没有做严格的判断。运行脚本时会有警告信息关于$exp{${a}}键值的。
看脚本:
#!/usr/bin/perl
use DBI;
#use strict;
use Math::BigInt;
use warnings;
use DBI;
#use strict;
use Math::BigInt;
use warnings;
#取系统时间
my ($year,$mon,$day)=(localtime)[5,4,3];
$year += 1900;
$mon += 1;
$day -= 1;
my ($year,$mon,$day)=(localtime)[5,4,3];
$year += 1900;
$mon += 1;
$day -= 1;
#需要处理的帐户文件
my $file = "/db/setup/sendEmail/account.txt";
my $file2="/db/setup/sendEmail/account2.txt";
my $file = "/db/setup/sendEmail/account.txt";
my $file2="/db/setup/sendEmail/account2.txt";
#数据库
my $dbname="db";
my $dbname2="db1";
my $dbname="db";
my $dbname2="db1";
#最后的生成文件用于邮件发送
my $email_file="/db/setup/sendEmail/$year-$mon-$day-account.txt";
my $email_file2="/db/setup/sendEmail/$year-$mon-$day-account2.txt";
my $expfile="/db/setup/sendEmail/exp.txt";
sub select{
my $lev_message="select a.email,e.name,e.exp from tbl_sch_charactor as e ,tbl_sch_account as a where a.id=e.guid and exp>='139609' order by exp;";
my $user="root";
my $password="xx";
my $driver="DBI:mysql";
my $dbh=DBI->connect("$driver:database=$_[0];host=localhost;user=$user;password=$password");
$dbh->do("use $_[0];") or die "Can't ". $dbh->errstr;
$dbh->do("set names utf8;") or die "Can't set names". $dbh->errstr;
my $sth=$dbh->prepare($lev_message) or die "Can't connect: ". $dbh->errstr;
$sth->execute() or die $sth->errstr;
my($email,$name,$exp);
$sth->bind_columns(\$email,\$name,\$exp);
#由于数据误置里面特殊字符一堆于是放入@@@符号作为文件间隔使用
open (HEAD,">$_[1]")|| die "Can't create $_[1]:$!\n";
printf HEAD "%s\t%-s\t%-s\t%-s\n","email","name","@@@","exp";
while($sth->fetch()){
open (HEAD,">>$_[1]")|| die "Can't write $_[1]:$!\n";
printf HEAD "%s\t%-s\t%-s\t%-s\n",$email,$name,"@@@",$exp;
}
my $email_file="/db/setup/sendEmail/$year-$mon-$day-account.txt";
my $email_file2="/db/setup/sendEmail/$year-$mon-$day-account2.txt";
my $expfile="/db/setup/sendEmail/exp.txt";
sub select{
my $lev_message="select a.email,e.name,e.exp from tbl_sch_charactor as e ,tbl_sch_account as a where a.id=e.guid and exp>='139609' order by exp;";
my $user="root";
my $password="xx";
my $driver="DBI:mysql";
my $dbh=DBI->connect("$driver:database=$_[0];host=localhost;user=$user;password=$password");
$dbh->do("use $_[0];") or die "Can't ". $dbh->errstr;
$dbh->do("set names utf8;") or die "Can't set names". $dbh->errstr;
my $sth=$dbh->prepare($lev_message) or die "Can't connect: ". $dbh->errstr;
$sth->execute() or die $sth->errstr;
my($email,$name,$exp);
$sth->bind_columns(\$email,\$name,\$exp);
#由于数据误置里面特殊字符一堆于是放入@@@符号作为文件间隔使用
open (HEAD,">$_[1]")|| die "Can't create $_[1]:$!\n";
printf HEAD "%s\t%-s\t%-s\t%-s\n","email","name","@@@","exp";
while($sth->fetch()){
open (HEAD,">>$_[1]")|| die "Can't write $_[1]:$!\n";
printf HEAD "%s\t%-s\t%-s\t%-s\n",$email,$name,"@@@",$exp;
}
#先创建一个包含email,name,exp,level相关信息的数据
open (ACCOUNT,">$_[2]")|| die "Can't create $_[2]:$!\n";
open (ACCOUNT,">$_[2]")|| die "Can't create $_[2]:$!\n";
printf ACCOUNT "%s\t%-s\t%-s\t%-s\n","email","name","exp","level";
#使用等级经验那个文件生成一个哈希数组
open (EXP,"$_[3]") || die "Can't open $_[3]: $!\n";
while(<EXP>){
($exp_str,$exp_num) = split(/=/,$_,2);
$b = Math::BigInt->new($exp_num);
$exp{$b}=$exp_str;
}
while(<EXP>){
($exp_str,$exp_num) = split(/=/,$_,2);
$b = Math::BigInt->new($exp_num);
$exp{$b}=$exp_str;
}
#打开帐户相关信息文件,用数组打开是方便按顺序打开,里面的文件句柄需要与上面写入的该文件的文件句柄一致,开始不一致时,老是出现最后一行不输出问题很是纳闷
open (HEAD,"$_[1]") || die "Can't open $_[1]:$!\n";
while(<HEAD>){
@acc = split( /@@@/,$_,2);
$a = Math::BigInt->new($acc[1]);
if ("$a" eq "NaN"){
next;
}else{
chomp($acc[1]);
open (ACCOUNT,">>$_[2]")|| die "Can't open write $_[2]:$!\n";
printf ACCOUNT "%s\t%-s\t%-s\n",$acc[0],$acc[1],$exp{${a}};
}
while(<HEAD>){
@acc = split( /@@@/,$_,2);
$a = Math::BigInt->new($acc[1]);
if ("$a" eq "NaN"){
next;
}else{
chomp($acc[1]);
open (ACCOUNT,">>$_[2]")|| die "Can't open write $_[2]:$!\n";
printf ACCOUNT "%s\t%-s\t%-s\n",$acc[0],$acc[1],$exp{${a}};
}
}
}
}
#调用方法
my @email = &select($dbname,$file,$email_file,$expfile);
my @email2 = &select($dbname2,$file2,$email_file2,$expfile);
my @email2 = &select($dbname2,$file2,$email_file2,$expfile);
#发邮件的一脚本是shell脚本,用到shell命令较为方便于是直接采用调用shell执行了
system("sh /db/setup/sendEmail/send.sh $email_file 13 $file ");
system("sh /db/setup/sendEmail/send.sh $email_file2 16 $file2");
system("sh /db/setup/sendEmail/send.sh $email_file2 16 $file2");
下面是shell脚本
send.sh
unix2dos $1
/db/setup/sendEmail/sendEmail -f wxx@xxxx.com -t cxx@xx.56.com -cc xx@xxcom -u "$2区帐号等级经验" -m "$2区帐号信息" -s xx.xx.com -xu wxx@xxx.com -xp xx-o message-charset="utf-8" -a "$1"
rm -rf $1 $3
使用的是sendEmail网上的一个小程序
/db/setup/sendEmail/sendEmail -f wxx@xxxx.com -t cxx@xx.56.com -cc xx@xxcom -u "$2区帐号等级经验" -m "$2区帐号信息" -s xx.xx.com -xu wxx@xxx.com -xp xx-o message-charset="utf-8" -a "$1"
rm -rf $1 $3
使用的是sendEmail网上的一个小程序
附件是相关的帐户跟经验文件有兴趣的可以自己试下,嗯里面的那个帐户文件并没有使用@@@分隔开来,呵呵