用perl实现报表自动提取及自动发送（转）

本文转贴自

http://blog.chinaunix.net/u/12178/showart_511527.html

有空的时候总喜欢看看论坛中的perl版，看久了自然也就懂了perl的一些基础知识。今天市场部的人要求我们帐务中心每个周向其出示一份周 报，报表格式固定不变（固定的提数sql语句，每周五提数并发邮）。本想用delphi写一个，但好久不用delphi了，怕语法忘了写不出来，于是便想 用perl试试。没想perl真的很实在，参考了一些资料后便能搞定。现贴出来，也许对需要解决这类问题的人有点用处。写得很稚嫩，不过能完成任务。 呵。。。。

?

#------------------------------------------------------------------
#
#run env Unix/Linux/Windows
#------------------------------------------------------------------
sub send_sm()? ##发短信函数，（配合曾写过的短信接入程序）两个参数，类型为字符串，分别为手机号码及所发短信
?{?
?? use?? IO::Socket;??
?? my $sock=new IO::Socket::INET(PeerAddr?? =>?? '10.243.70.21',??
???????????????????????????????? PeerPort?? =>?? 4119,??
???????????????????????????????? Proto????? =>?? 'tcp'
??????????????????????????????? );??
?? die?? "Socket creat error:$!" unless $sock;??
?? print $sock?? "$_[0]"."#"."$_[1]";??
?? $sock->flush();??
?? close ($sock);??
?}
#------------------------------------------------------------------
sub print_table()
{
? use DBI;
? $ENV{"NLS_LANG"} = 'AMERICAN_AMERICA.ZHS16GBK';
? my $user_id='rpt';
? my $passwd='21';
? my $db='ll';
? my $dbh = DBI->connect("dbi:Oracle:$db",
????????????????????????? $user_id,
????????????????????????? $passwd
??????????????????????? ) || die "can not access the remote dbbase";
? #-----------------------------------------------------------------
? open CVS_FILE,">>",$_[1];
? my $line=undef;
? my $sth=$dbh->prepare($_[0]);
? $sth->execute();
? for ( my $i = 1 ; $i <= $sth->{NUM_OF_FIELDS} ; $i++ )
? {
???? if ($i<$sth->{NUM_OF_FIELDS})
???????? { $line=$line.'"'.$sth->{NAME}->[$i-1].'",';}
????
???? else
???????? { $line=$line.'"'.$sth->{NAME}->[$i-1].'"';}
? }
? print CVS_FILE $line."/n";
? my $data;
? my @recs=undef;
? while ( @recs=$sth->fetchrow_array)
? {
??? $data=undef;
??? for (my $i=0;$i<=$#recs;$i++)
??? {if ($i<$#recs)
??????????? {$data=$data.'"'.$recs[$i].'",';}
???? else
??????? {$data=$data.'"'.$recs[$i].'"';}
??? }
??? print CVS_FILE $data."/n";
? }
? print CVS_FILE "/n/n/n";
? close CVS_FILE;
? $sth->finish();?
? $dbh->disconnect();
}
#------------------------------------------------------------------
#韦传仁自动提取周报并发邮程序
sub get_time_string() #取得时间字符串.
{ use POSIX;
? my? ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime(time()+$_[0]*86400);
? $mon++;
? $mon="0$mon"?? if ($mon<10);
? $mday="0$mday" if ($mday<10);
? $hour="0$hour" if ($hour<10);
? $min="0$min"?? if ($min<10);
? $sec="0$sec"?? if ($sec<10);
? $year+=1900;
? my $timestring=scalar? "$year$mon$mday"."_$hour$min$sec";
? return $timestring;
}
#------------------------------------------------------------------
#文件压缩函数
sub get_zip_file()
{use Archive::Zip;
?my $obj = Archive::Zip->new();
$obj->addFile($_[0]);
if ($obj->writeToFileNamed("$_[0]".".zip") != AZ_OK)
?? {
??? print "Error in archive creation!/n";
?? }
else
?? {
??? print "Archive created successfully!/n";
?? }
}
#------------------------------------------------------------------
#邮件函数
sub Send_Mail($$/@$$/@)
{
use Net::SMTP;
use MIME::Lite;
my ($mailFrom,$password,$mailToRef,$subject,$content,$attachmentRef)=@_;
my ($userName,$mailHost) = split(//@/,$mailFrom);
my $helloPara = $mailHost;
$mailHost = "smtp.".$mailHost;
my @mailTo = @$mailToRef;??
for(my $i=0; $i<=$#mailTo; $i++)
?? { my $no=$i+1;
????? print "Sending.../n";?
???? print "Mail to NO."."$no"." user .../n";
???? print "User_mailbox is $mailTo[$i]/n";
???
?? }
my @attachment = @$attachmentRef;???
my $smtp=Net::SMTP->new($mailHost,Hello => $helloPara,Timeout =>220,Debug =>0)
????????????????????????????????????????? ||die 'Cannot connect to smtp_server /'$mailHost/'';

? $smtp->auth($userName,$password)||print "Auth Error!/n";
? foreach my $mailTo (@mailTo)
? {
??? my $msg = MIME::Lite->new(
??????? From??? => $mailFrom,
??????? To????? => $mailTo,
??????? Subject => $subject,
??????? Type??? =>'multipart/mixed',
??????? )or print "Error creating MIME body: $!/n";
??? $msg->attach(
???????????????? Type???? =>'TEXT',
???????????????? Data??? => $content,
??????????????? );
??? foreach my $attachment (@attachment)
??? {
??????? $msg->attach(
???????????????????? Type???? => 'AUTO',?????
???????????????????? Path???? => $attachment,
??????????????????? ) or print "Err attaching your file: $!/n";
??? }
??? my $str = $msg->as_string() or print "Converting? message as a string: $!/n";
?????? $smtp->mail($mailFrom);
?????? $smtp->to($mailTo);
?????? $smtp->data();
?????? $smtp->datasend("$str");
?????? $smtp->dataend();
? }
$smtp->quit;
return;
}
#------------------------------------------------------------------
my $file_name=& get_time_string().'周报.csv';
my @sql;
$sql[0]=qq {
???????????? SELECT group_name 集团名,'R'||group_id 集团编号,
???????????? TO_CHAR(create_date,'YYYYMMDD HH24:MI:SS') 创建时间,
???????????? rpt_get_company_name_f(region_code) 归属分公司,
???????????? if_valid 是否有效,if_real 是否真集团,b.remark 集团类型?
???????????? FROM? bb_group_info_t a,
?????????????????? bb_grp_occupation_t b
???????????? WHERE a.create_date>=(to_date(to_char(SYSDATE,'YYYYMMDD'),'YYYYMMDD')-7) --上周五
???????????? AND??? a.create_date<(to_date(to_char(SYSDATE,'YYYYMMDD')) --本周四
???????????? AND a.if_real=1
???????????? AND a.if_valid=1
???????????? AND a.grp_occupation=b.kind
?????????? };
$sql[1]=qq {
??????????? SELECT b.service_id 用户名码,
??????????? rpt_get_service_kind_f(b.service_kind) 服务类型,
??????????? a.User_Id 用户ID,
??????????? to_char(b.apply_start_date,'YYYYMMDD HH24:MI:SS') 用户入网时间,
??????????? to_char(a.create_date,'YYYYMMDD HH24:MI:SS') 用户加入集团时间,
??????????? rpt_get_company_name_f( rpt_get_county_code_f(a.user_id)) 用户归属分公司,
??????????? a.group_id 集团编码,c.group_name 集团名称,
??????????? to_char(c.create_date,'YYYYMMDD HH24:MI:SS') 集团创建时间,
??????????? rpt_get_company_name_f(a.region_code) 集团归属分公司
??????????? FROM bb_grp_user_info_t a,
??????????? bb_service_relation_t b,
??????????? bb_group_info_t c
??????????? WHERE? a.create_date>=(to_date(to_char(SYSDATE,'YYYYMMDD')-7)
??????????? AND??? a.create_date<(to_date(to_char(SYSDATE,'YYYYMMDD'))
??????????? AND a.if_real=1
??????????? AND a.if_valid=1
??????????? AND? NOT EXISTS (SELECT * FROM bb_group_info_t
??????????????????????????? WHERE group_id=a.group_id
??????????????????????????? AND if_real=0
?????????????????????????? )
??????????? AND a.user_id=b.user_id
??????????? AND to_char(b.apply_start_date,'YYYY')='2008'
??????????? AND a.group_id=c.group_id
?????????? };