非税代收系统主机创建对账文件的perl脚本

非税代收系统主机每天晚上需要自动生成text格式的对账文本，因此编写了如下perl脚本，加入crontab，让它每天自动执行。

#!/usr/bin/perl
# createDuizhangText.pl

use strict;
use warnings;
use DBI;
#use Text::CSV_XS;

my $server='localhost';
my $username='dsywfs';
my $password='*******';
my $dbname='dsywfs';

my $dsn = "DBI:mysql:host=$server;database=$dbname";
my %conn_attrs = (PrintError => 0, RaiseError => 1, AutoCommit => 1);

my $yhbm='440900015';

#@ _CONNECT_
my $dbh = DBI->connect ($dsn, $username, $password, \%conn_attrs)
            or die "Connection error: "
                   . "$DBI::errstr ($DBI::err/$DBI::state)\n";

#@ _CONNECT_

my ($year,$mon,$day)=(localtime)[5,4,3];
$year+=1900;
$mon+=1;
#$day-=1; //by laihanfen
my $jfdate=sprintf("%04u-%02u-%02u",$year,$mon,$day);
my $dzdate=sprintf("%04u%02u%02u",$year,$mon,$day);

my $filename = '/tmp/YHSC/YHSK' . $yhbm . $dzdate . '01.txt';
#$jfdate = '2010-01-05';

#@ _COUNT_ZBS_
my $sth = $dbh->prepare ("SELECT count(*) AS `zbs` FROM `skmx` WHERE `jfdate` LIKE '$jfdate%' AND `dzLog`='0' AND `czLog`='0'");
$sth->execute ();
my $zbs = $sth->fetchrow_array ();
#my $zbs = $bs[0];
$sth->finish ();
#@ _COUNT_ZBS_

#@ _COUNT_FKJE_
$sth = $dbh->prepare ("SELECT sum(`fkje`) AS `fkje` FROM `skmx` WHERE `jfdate` LIKE '$jfdate%' AND `dzLog`='0' AND `czLog`='0'");
$sth->execute ();
my $fkje = $sth->fetchrow_array ();
#my $fkje = $je[0];
$sth->finish ();
#@ _COUNT_FKJE_

#@ _COUNT_JSJE_
$sth = $dbh->prepare ("SELECT sum(`jsje`) AS `jsje` FROM `skmx` WHERE `jfdate` LIKE '$jfdate%' AND `dzLog`='0' AND `czLog`='0' AND `jsje`>0");
$sth->execute ();
my $jsje = $sth->fetchrow_array ();
#my $jsje = $js[0];
$sth->finish ();
#@ _COUNT_JSJE_

 

注：

在perl中可以使用map来转化任何undel（NULL）值：

// 摘自《MySQL CooKbook中文版》（第二版）115页
while (my @var = $sth->fetchrow_array())
{
  @var = map { defined  ($_) ? $_ : "NULL" } @var;
}