#
#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/'';
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','周报已发到你邮箱,请查看'); #--发短信通知