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

来源：互联网发布：centos 6.7网卡安装编辑：程序博客网时间：2024/05/22 14:36

本文转贴自

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'),'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'),'YYYYMMDD')-7)
            AND    a.create_date<(to_date(to_char(SYSDATE,'YYYYMMDD'),'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
           };

$sql[2]=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)) 用户归属分公司,
          'R'||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'),'YYYYMMDD')-7)
          AND    a.create_date<(to_date(to_char(SYSDATE,'YYYYMMDD'),'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 a.group_id=c.group_id
         };
foreach (@sql)
{
   &print_table($_,$file_name);
}
&get_zip_file($file_name);
#------------------------------------------------------------------
#$dbh->disconnect();
my $mailFrom = '1332200@hi165.com';#发件人帐号
my $password = '****'; #发件人smtp密码
my @mailTo = qw!1332203@hi165.com 13322006@hi165.com !;#收件人address list
#此处可加入其他人的电邮地址实现群发，电邮地址用空格隔开。
my $mailSubject = "$file_name周报";
my $mailContent = "$mailSubject周报!/n
自动发送邮件，请分析数据./n谢谢!/n/n      韦传仁/n";
my @mailAttachment = ($file_name.".zip"); #附件文件名数组
Send_Mail($mailFrom, $password, @mailTo, $mailSubject, $mailContent, @mailAttachment);
&send_sm('13322039','周报已发到你邮箱,请查看'); #--发短信通知
&send_sm('13322002','周报已发到你邮箱,请查看'); #--发短信通知