2009/12/17

WSUSからパッチのステータスを取り出すスクリプト

WSUSからパッチのステータス一覧等々必要なデータを取り出すスクリプト.
これまでは, Management studioからRTPでデータを取り出して, RTPをCSVに変換するスクリプトを使っていたが, めんどくさくなったので, スクリプト化.

use strict;
use utf8;


use POSIX;
#print setlocale(LC_ALL);
setlocale(LC_ALL, 'Japanese_Japan.932');
#print setlocale(LC_ALL);


sub get_fields($){
  my $type = shift @_;
  my @out;
  
  @out = split /,/, "updateID,LegacyName,Title,Description,Category,KBArticleID,SecurityBulletinID,MoreInfoURL,RevisionID,SupersedeUpdateCount,SupersededUpdateCount";
  return \@out if $type eq "all_patches";

  @out = split /,/, "EventInstanceID,EventID,TimeAtTarget,TimeAtServer,StateID,SeverityID,MessageTemplate,ReplacementStringsXml,TargetID,ComputerID,LastSyncTime,LastReportedStatusTime,LastReportedRebootTime,IPAddress,FullDomainName,updateID,Title,Category,KBArticleID,SecurityBulletinID";
  return \@out if $type eq "error_patches";

  @out = split /,/, "TargetGroupID,TargetID,IsExplicitMember,TargetID,ComputerID,SID,LastSyncTime,LastReportedStatusTime,LastReportedRebootTime,IPAddress,FullDomainName,IsRegistered,LastInventoryTime,LastNameChangeTime,EffectiveLastDetectionTime,ParentServerTargetID,LastSyncResult,TargetGroupTypeID,Name,Description,TargetGroupID,OrderValue,IsBuiltin,ParentGroupID,GroupPriority";
  return \@out if $type eq "host_groups";

  @out = split /,/, "TargetID,ComputerID,SID,LastSyncTime,LastReportedStatusTime,LastReportedRebootTime,IPAddress,FullDomainName,IsRegistered,LastInventoryTime,LastNameChangeTime,EffectiveLastDetectionTime,ParentServerTargetID,LastSyncResult,TargetID,OSMajorVersion,OSMinorVersion,OSBuildNumber,OSServicePackMajorNumber,OSServicePackMinorNumber,OSLocale,ComputerMake,ComputerModel,BiosVersion,BiosName,BiosReleaseDate,ProcessorArchitecture,LastStatusRollupTime,LastReceivedStatusRollupNumber,LastSentStatusRollupNumber,SamplingValue,CreatedTime,SuiteMask,OldProductType,NewProductType,SystemMetrics,ClientVersion,TargetGroupMembershipChanged,OSFamily,OSDescription";
  return \@out if $type eq "hosts";
  
  @out = split /,/, "targetID,FullDomainName,IPAddress,category,Title,SummarizationState,LastReportedStatusTime,SecurityBulletinID,KBArticleID,updateID,RevisionID,SupersedeUpdateCount,SupersededUpdateCount";
  return \@out if $type eq "needed_patches";

  @out = split /,/, "UpdateApprovalId,UpdateID,ComputerTargetGroupId,Title,GroupName,Action,Deadline,CreationDate,AdministratorName,IsOptional,IsStale";

  return \@out if $type eq "patch_approvals";
  
  return undef;
}


sub get_sql($){
  my $type = shift @_;
  return qq{
SELECT 
  RevisionID, 
  COUNT(SupersededUpdateID) AS counter 
INTO #X1 
FROM 
  SUSDB.dbo.tbRevisionSupersedesUpdate 
GROUP BY
  RevisionID;

SELECT
  COUNT(RevisionID) AS counter,
  SupersededUpdateID
INTO #Y1 
FROM
  SUSDB.dbo.tbRevisionSupersedesUpdate 
GROUP BY
  SupersededUpdateID;

SELECT
  B.updateID,
  B.LegacyName, 
  A.Title, 
  A.Description,
  D.Title AS Category,
  F.KBArticleID, 
  G.SecurityBulletinID, 
  H.MoreInfoURL, 
  A.RevisionID, 
  #X1.counter AS SupersedeUpdateCount, 
  #Y1.counter AS SupersededUpdateCount
FROM
  SUSDB.dbo.tbPreComputedLocalizedProperty A
  INNER JOIN
    SUSDB.dbo.tbUpdate B ON A.UpdateID = B.UpdateID 
  INNER JOIN
    SUSDB.dbo.tbRevisionInCategory C ON A.RevisionID = C.RevisionID 
  INNER JOIN
    SUSDB.dbo.tbPrecomputedCategoryLocalizedProperty D ON C.CategoryID = D.CategoryID
  INNER JOIN
    SUSDB.dbo.tbRevision E on E.localUpdateID = B.localUpdateID
  LEFT JOIN
    SUSDB.dbo.tbKBArticleForRevision F ON F.RevisionID = E.RevisionID
  LEFT JOIN 
    SUSDB.dbo.tbSecurityBulletinForRevision G ON G.RevisionID = E.RevisionID
  INNER JOIN
    SUSDB.dbo.tbMoreInfoURLForRevision H ON H.RevisionID = E.RevisionID
  LEFT JOIN
    #X1 ON A.RevisionID = #X1.RevisionID
  LEFT JOIN
    #Y1 ON B.updateID = #Y1.SupersededUpdateID
WHERE
  A.ShortLanguage = 'en' AND
  D.CategoryType = 'UpdateClassification' AND
  D.ShortLanguage = 'en' AND
  E.islatestRevision = 'True' AND
  H.ShortLanguage = 'en'
ORDER BY
  G.SecurityBulletinID

  } if ($type eq 'all_patches'); 

  return qq{
SELECT
  B.updateID,
  B.LegacyName, 
  A.Title, 
  A.Description,
  D.Title AS Category,
  F.KBArticleID, 
  G.SecurityBulletinID, 
  H.MoreInfoURL, 
  A.RevisionID
INTO #X1 
FROM
  SUSDB.dbo.tbPreComputedLocalizedProperty A
  INNER JOIN
    SUSDB.dbo.tbUpdate B ON A.UpdateID = B.UpdateID 
  INNER JOIN
    SUSDB.dbo.tbRevisionInCategory C ON A.RevisionID = C.RevisionID 
  INNER JOIN
    SUSDB.dbo.tbPrecomputedCategoryLocalizedProperty D ON C.CategoryID = D.CategoryID
  INNER JOIN
    SUSDB.dbo.tbRevision E on E.localUpdateID = B.localUpdateID
  LEFT JOIN
    SUSDB.dbo.tbKBArticleForRevision F ON F.RevisionID = E.RevisionID
  LEFT JOIN 
    SUSDB.dbo.tbSecurityBulletinForRevision G ON G.RevisionID = E.RevisionID
  INNER JOIN
    SUSDB.dbo.tbMoreInfoURLForRevision H ON H.RevisionID = E.RevisionID
WHERE
  A.ShortLanguage = 'en' AND
  D.CategoryType = 'UpdateClassification' AND
  D.ShortLanguage = 'en' AND
  E.islatestRevision = 'True' AND
  H.ShortLanguage = 'en'
ORDER BY
  G.SecurityBulletinID


SELECT 
  distinct B.ComputerID, 
  D.updateID
INTO #X2
FROM 
  SUSDB.dbo.tbUpdateStatusPerComputer A
  INNER JOIN
    SUSDB.dbo.tbComputerTarget B ON A.targetID = B.targetID
  INNER JOIN
    SUSDB.dbo.tbUpdate D ON A.LocalUpdateID = D.LocalUpdateID
WHERE
  A.SummarizationState = 5


SELECT
  E.EventInstanceID,
  E.EventID,
  E.TimeAtTarget,
  E.TimeAtServer,
  E.StateID,
  E.SeverityID,
  E.MessageTemplate,
  E.ReplacementStringsXml,
  C.TargetID,
  C.ComputerID,
  C.LastSyncTime,
  C.LastReportedStatusTime,
  C.LastReportedRebootTime,
  C.IPAddress,
  C.FullDomainName,
  U.updateID,
  U.Title,
  U.Category,
  U.KBArticleID,
  U.SecurityBulletinID
FROM
  SUSDB.dbo.vwEventHistory E
  INNER JOIN
    SUSDB.dbo.tbComputerTarget C ON E.ComputerID = C.ComputerID
  INNER JOIN
    #X1 U ON E.updateID = U.updateID
WHERE
  eventID in (161, 163, 182, 185, 186, 192, 195, 198, 364) AND
  E.ComputerID IN (SELECT ComputerID FROM #X2) AND
  E.updateID IN (SELECT updateID FROM #X2) AND
  E.TimeAtServer = (SELECT MAX(TimeAtServer) FROM SUSDB.dbo.vwEventHistory E2
                     WHERE E.updateID = E2.updateID AND
                     E.ComputerID = E2.ComputerID)

  } if ($type eq 'error_patches'); 

  return qq{
select A.*, B.*, C.*
from
SUSDB.dbo.tbTargetInTargetGroup as A
INNER JOIN
SUSDB.dbo.tbComputerTarget B ON A.targetID = B.targetID
INNER JOIN
SUSDB.dbo.tbTargetGroup C ON A.TargetGroupID = C.TargetGroupID
;  } if ($type eq 'host_groups'); 

  return qq{
SELECT distinct C.*, D.*
FROM
SUSDB.dbo.tbTargetGroup A
LEFT OUTER JOIN
SUSDB.dbo.tbTargetInTargetGroup B ON B.TargetGroupID = A.TargetGroupID
RIGHT OUTER JOIN
SUSDB.dbo.tbComputerTarget C ON B.TargetID = C.TargetID
INNER JOIN
SUSDB.dbo.tbComputerTargetDetail D ON C.TargetID = D.TargetID
  } if ($type eq 'hosts'); 

  return qq{
SELECT 
  RevisionID, 
  COUNT(SupersededUpdateID) AS counter 
INTO #X1 
FROM 
  SUSDB.dbo.tbRevisionSupersedesUpdate 
GROUP BY
  RevisionID;

SELECT
  COUNT(RevisionID) AS counter,
  SupersededUpdateID
INTO #Y1 
FROM
  SUSDB.dbo.tbRevisionSupersedesUpdate 
GROUP BY
  SupersededUpdateID;

SELECT 
  distinct B.targetID, 
  B.FullDomainName, 
  B.IPAddress, 
  H.Title AS category,
  C.Title,
  A.SummarizationState, 
  B.LastReportedStatusTime,
  F.SecurityBulletinID,
  I.KBArticleID,
  D.updateID,
  E.RevisionID, 
  #X1.counter AS SupersedeUpdateCount, 
  #Y1.counter AS SupersededUpdateCount
FROM 
  SUSDB.dbo.tbUpdateStatusPerComputer A
  INNER JOIN
    SUSDB.dbo.tbComputerTarget B ON A.targetID = B.targetID
  INNER JOIN
    SUSDB.dbo.tbUpdate D ON A.LocalUpdateID = D.LocalUpdateID
  INNER JOIN
    SUSDB.dbo.tbPreComputedLocalizedProperty C ON C.updateID = D.updateID
  INNER JOIN
    SUSDB.dbo.tbRevision E ON D.localUpdateID = E.localUpdateID
  LEFT OUTER JOIN
    SUSDB.dbo.tbSecurityBulletinForRevision F ON E.RevisionID = F.RevisionID
  INNER JOIN
    SUSDB.dbo.tbRevisionInCategory G ON E.RevisionID = G.RevisionID
  INNER JOIN
    SUSDB.dbo.tbPrecomputedCategoryLocalizedProperty H ON G.CategoryID = H.CategoryID
  LEFT OUTER JOIN
    SUSDB.dbo.tbKBArticleForRevision I ON E.RevisionID = I.RevisionID
  LEFT JOIN
    #X1 ON E.RevisionID = #X1.RevisionID
  LEFT JOIN
    #Y1 ON D.updateID = #Y1.SupersededUpdateID
WHERE
  (H.ShortLanguage = 'en') AND
  (C.ShortLanguage = 'en') AND 
  (H.CategoryType = 'UpdateClassification') AND
  (NOT (A.SummarizationState IN (1, 4)));

  } if ($type eq 'needed_patches'); 

  return qq{
SELECT distinct d.DeploymentGuid AS UpdateApprovalId, u.UpdateID, d.TargetGroupID AS ComputerTargetGroupId, 
   C.Title, g.Name as GroupName, 
  (CASE d .ActionID WHEN 0 THEN N'Install' WHEN 1 THEN N'Uninstall' WHEN 2 THEN N'NotApproved' WHEN 3 THEN N'NotApproved' ELSE NULL END) 
  AS Action, d.Deadline, d.DeploymentTime AS CreationDate, d.AdminName AS AdministratorName, ~ d.IsAssigned AS IsOptional, 
  ~ r.IsLatestRevision AS IsStale
FROM SUSDB.dbo.tbUpdate AS u
INNER JOIN
  SUSDB.dbo.tbRevision AS r ON u.LocalUpdateID = r.LocalUpdateID
INNER JOIN
  SUSDB.dbo.tbProperty AS p ON r.RevisionID = p.RevisionID
INNER JOIN
  SUSDB.dbo.tbDeployment AS d ON d.RevisionID = r.RevisionID
INNER JOIN
  SUSDB.dbo.tbPreComputedLocalizedProperty AS C ON C.updateID = u.updateID
INNER JOIN
  SUSDB.dbo.tbTargetGroup AS g ON d.TargetGroupID = g.TargetGroupID
LEFT OUTER JOIN
  SUSDB.dbo.tbSecurityBulletinForRevision AS b ON r.RevisionID = b.RevisionID
LEFT OUTER JOIN
  SUSDB.dbo.tbKBArticleForRevision AS k ON r.RevisionID = k.RevisionID
INNER JOIN
  SUSDB.dbo.tbRevisionInCategory AS rc ON r.RevisionID = rc.RevisionID
INNER JOIN
  SUSDB.dbo.tbPrecomputedCategoryLocalizedProperty AS cl ON rc.CategoryID = cl.CategoryID
WHERE
  ((p.ExplicitlyDeployable = 1) AND (d.TargetGroupTypeID = 0) AND (d.ActionID IN (0, 1, 3)) OR
  (p.ExplicitlyDeployable = 1) AND (d.TargetGroupTypeID = 0) AND (d.ActionID = 2) AND 
  (d.TargetGroupID <> (select TargetGroupID from SUSDB.dbo.tbTargetGroup where Name = 'All Computers')))
  AND (C.ShortLanguage = 'en') AND(cl.ShortLanguage = 'en') AND (cl.CategoryType = 'UpdateClassification')
  } if ($type eq 'patch_approvals'); 

  return 1;
}

sub get_TimeField ($){
  my @fields = @{shift @_};
  my %output;
  foreach my $i (@fields){
    if ($i =~ m/time/io || $i =~ m/BiosReleaseDate/o || $i =~ m/CreationDate/o){
      $output{$i} = 1
    }else{
      $output{$i} = 0;
    }
  }
  return \%output;
}

sub convert_time ($){
  my $init_str = shift;
  return "" if ($init_str =~ /^\s*$/);
  my @data = split / /, $init_str;
  my @date = split /\//, $data[0];
  my @time = split /:/, $data[1];
  $time[0] = 0 if ($time[0]==12);
  $time[0] += 12 if $data[2] eq 'PM';

  return sprintf("%04d/%02d/%02d %02d:%02d:%02d", $date[2], $date[0], $date[1], @time);
}


use Win32::OLE;

# datetime型のデータが文字列で取得できる。
use Win32::OLE::variant;

# 指定タイプライブラリのコンスタントが参照可能
use Win32::OLE::Const 'Microsoft ActiveX Data Objects 2.0 Library';

use Win32::OLE::NLS qw(:DEFAULT :LANG :SUBLANG :DATE :TIME);

# エラー時に本処理を中止し、Perlがエラーメッセージを出力し、本プロセスが終了する。
Win32::OLE->Option(Warn => 3);

use Encode;


my $usage = qq{WSUS report retrieval tool.

Usage: $0 [Options]
Options:
  -A   Retrieve all supported reports. (Same as "-a -e -g -h -n -p") 
  -a   List of all patches. 
  -e   Error patch list. 
  -g   Hosts list with their group name. 
  -h   host list.
  -n   List of patches need to be applied. 
  -p   Approval Status report. 
  -H   Show this message.
  -P   Prefix for output filename. 

  The output filename will be set as below. 
  -a: <prefix>all_patches_yyyymmdd-HHMM.csv
  -e: <prefix>error_patches_yyyymmdd-HHMM.csv
  -g: <prefix>host_groups_yyyymmdd-HHMM.csv
  -h: <prefix>hosts_yyyymmdd-HHMM.csv
  -n: <prefix>needed_patches_yyyymmdd-HHMM.csv
  -p: <prefix>patch_approvals_yyyymmdd-HHMM.csv

};

die $usage if ( $#ARGV < 0 );
use Getopt::Std;
use vars qw($opt_A $opt_a $opt_e $opt_g $opt_h $opt_n $opt_p $opt_H $opt_P);
getopts('AaeghnpHP:') or die $usage;

die $usage if $opt_H;
die $usage if ( $#ARGV != -1 );

if ($opt_A){
  $opt_a = 1;
  $opt_e = 1;
  $opt_g = 1;
  $opt_h = 1;
  $opt_n = 1;
  $opt_p = 1;
}

my %requests;
$requests{all_patches}     = 1 if ($opt_a);
$requests{error_patches}           = 1 if ($opt_e);
$requests{host_groups}     = 1 if ($opt_g);
$requests{hosts}           = 1 if ($opt_h);
$requests{needed_patches}  = 1 if ($opt_n);
$requests{patch_approvals} = 1 if ($opt_p);

my ($sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst) = localtime();
    #秒   分    時     日     月    年     曜日   年初か 夏時間を
    #                                             らの   適用して
    #                                             経過日 いるか
     
$year += 1900; # localtime関数からは1900年から数えた年が返却される。
$mon++; # 月は0から始まるので、表示するときは1を加える。

my @day_of_week = qw( 日 月 火 水 木 金 土 ); # 日曜日は0になります。

my $date_str=sprintf("%04d%02d%02d-%02d%02d", $year, $mon, $mday, $hour, $min);

$opt_P = '' if (! $opt_P);
my %filenames;
$filenames{all_patches}     = $opt_P . "all_patches_${date_str}.csv";
$filenames{error_patches}           = $opt_P . "error_patches_${date_str}.csv";
$filenames{host_groups}     = $opt_P . "host_groups_${date_str}.csv";
$filenames{hosts}           = $opt_P . "hosts_${date_str}.csv";
$filenames{needed_patches}  = $opt_P . "needed_patches_${date_str}.csv";
$filenames{patch_approvals} = $opt_P . "patch_approvals_${date_str}.csv";

# DBサーバー名とDB名設定
my $server = 'np:\\\\.\pipe\mssql$microsoft##ssee\sql\query';
my $db = 'SUSDB';

# WINDOWS 認証設定
my $connStr = "Provider=sqloledb;".
              "Data Source=$server;".
              "Initial Catalog=$db;".
              "Integrated Security=SSPI;";

#print "$connStr\n";

# SQL Server 認証設定
#my $id = "testid";
#my $ps = "testps";
#my $connStr = "Provider=sqloledb;".
#              "Data Source=$server;".
#              "Initial Catalog=$db;".
#              "User ID=$id;".
#              "Password=$ps;";

# DB接続
my $objDB = Win32::OLE->new("ADODB.Connection");
$objDB->Open($connStr);
$objDB->{Errors}->{Count} and die "cannot connect '$connStr'";

# テーブル有無チェック
my $rs = Win32::OLE->new("ADODB.Recordset");
$rs->Open("Select count(*) existance From sysobjects Where NAME = 'tbUpdate'", $objDB);

if ($rs->{existance}->{Value} == 0) {
  die "Accessing DB is not for WSUS.";
}
$rs->Close();
$objDB->Close();

foreach my $type (keys %requests){

  print "Extracting to " . $filenames{$type} . "\n";
  print "$type:\t";

  $objDB->Open($connStr);
  $objDB->{Errors}->{Count} and die "cannot connect '$connStr'";

  open OUTFILE, ">:utf8", $filenames{$type} or die "cannot open ".$filenames{$type};

  # データ読み出し
  $rs = Win32::OLE->new("ADODB.Recordset");
  $rs->{CursorLocation} = adUseClient;
  my $sql_statement = qq{
  SET NOCOUNT ON;
  SET DATEFORMAT mdy
  } . get_sql($type) . ";";

  #print $sql_statement . "\n";
  $rs->Open($sql_statement, $objDB);

  my @Fields = @{get_fields ($type)};
  my %isTime = %{get_TimeField (\@Fields)};

  print OUTFILE join ',', map {(s/"/""/g or /[\x0d\x0a,]/) ? qq("$_") : $_} @Fields;
  print OUTFILE "\x0a";

  my $counter = 0;
  my $counter_length = 0;
  $| = 1;

  if ($type ne 'error_patches'){
    while(!$rs->EOF and $rs->{RecordCount} != 0){
      $counter++;
      if ($counter % 100 == 0){
        print "\b" x $counter_length;
        $counter_length = length $counter;
        print $counter;
      }

      my @items = ();
      foreach my $field (@Fields){
        my $item =$rs->{Fields}->{$field}->{Value};
        $item =~ s/^\s*//gmo;
        $item =~ s/\s*$//gmo;
        if ($isTime{$field}){
          if (ref( $rs->{Fields}->{$field}->{Value}) eq "Win32::OLE::Variant"){
            $item = $rs->{Fields}->{$field}->{Value}->Date("yyyy/MM/dd") . " " .
              $rs->{Fields}->{$field}->{Value}->Time("hh:mm:ss");
          }else{
            $item = convert_time ($rs->{Fields}->{$field}->{Value});
          }
        }
        push @items, $item;
      }
      print OUTFILE join ',', map {(s/"/""/g or /[\x0d\x0a,]/) ? qq("$_") : $_} @items;
      print OUTFILE "\x0a";
      $rs->MoveNext();
    }
  }else{
    while(!$rs->EOF and $rs->{RecordCount} != 0){
      $counter++;
      if ($counter % 100 == 0){
        print "\b" x $counter_length;
        $counter_length = length $counter;
        print $counter;
      }

      my @items = ();
      my $i = -1;
      foreach my $field (@Fields){
        $i++;
        my $item =  $rs->{Fields}->{$field}->{Value};
        $item =~ s/^\s*//gmo;
        $item =~ s/\s*$//gmo;
        if ($i == 7){
          my @tmp = split /\n/, $item;
          $tmp[2] =~ s/^\s*<string>(.*)<\/string>.*$/\1/go;
          $item = $tmp[2];
        }
        if ($isTime{$field}){
          if (ref( $rs->{Fields}->{$field}->{Value}) eq "Win32::OLE::Variant"){
            $item = $rs->{Fields}->{$field}->{Value}->Date("yyyy/MM/dd") . " " .
              $rs->{Fields}->{$field}->{Value}->Time("hh:mm:ss");
          }else{
            $item = convert_time ($rs->{Fields}->{$field}->{Value});
          }
        }
        push @items, $item;
      }
      print OUTFILE join ',', map {(s/"/""/g or /[\x0d\x0a,]/) ? qq("$_") : $_} @items;
      print OUTFILE "\x0a";
      $rs->MoveNext();
    }
  }
  print "\b" x $counter_length;
  print $counter , " records found.\n\n";


  $rs->Close();

  # DB切断
  $objDB->Close();

}

Perl2Exe (http://www.indigostar.com)でexe化できる.
Exe化したものの使い方

.\WSUS_report.exe -A -P .\reports\server_
これで, reportsフォルダの下にserver_****.csvという出力が出てくる.

参考までに, RTPをCSVに変換するときに使っていたスクリプト
#!/usr/bin/perl

use strict;
#require 'unicore/lib/gc_sc/Digit.pl';        #perl2exe (use -p2x_test switch to debug .exe file.)
#require 'unicore/lib/gc_sc/SpacePer.pl';     #perl2exe (use -p2x_test switch to debug .exe file.)
#use PerlIO;                                  #perl2exe (use -p2x_test switch to debug .exe file.)
#use PerlIO::via;                             #perl2exe (use -p2x_test switch to debug .exe file.)
#use Encode;                                  #perl2exe (use -p2x_test switch to debug .exe file.)
#use Encode::Unicode;                         #perl2exe (use -p2x_test switch to debug .exe file.)
use utf8;
use File::BOM qw/open_bom/;

my $usage = qq{Format converter for WSUS DB. Convert RPT format to CSV.

Usage: $0 [Options] <rpt file="">
Options:
  -E   Parse error comments
  -h   Show this message.
  -o   Output file name. 

  output filename is <rpt file="">.csv unless -o is specified.
};

use Getopt::Std;
use vars qw($opt_E $opt_h $opt_o);

getopts('o:Eh') or die $usage;
die $usage if $opt_h;
die $usage if ( $#ARGV < 0 );

my $filename = $ARGV[0];
my $outfilename = $filename;
$outfilename =~ s/\.rpt$/.csv/i;
$outfilename = $opt_o if $opt_o;

open INFILE, "<:via cannot="" die="" filename="" ile::bom="" open="" or="" outfile="">:utf8", $outfilename or die "cannot open $outfilename";
$/ = "\r\n";

print "$filename:\t";

my $titleline = <infile>;
while ($titleline =~ m/^\s*$/ || $titleline =~ m/^\(/){
  $titleline = <infile>;
}
chomp $titleline;

my $lengthline = <infile>;
chomp $lengthline;

my @length_tmp = split / /, $lengthline;
$lengthline = undef;
my @length;

for (my $i=0; $i <= $#length_tmp; $i++){
  $length[$i] = length $length_tmp[$i];
}

print OUTFILE txt2csv($titleline) , "\r\n";
$titleline = undef;

my $counter = 0;
my $counter_length = 0;
$| = 1;

if ($opt_E){
  while (<infile>){
    $counter++;
    chomp $_;
    print OUTFILE err_txt2csv($_) , "\r\n";
    if ($counter % 100 == 0){
      print "\b" x $counter_length;
      $counter_length = length $counter;
      print $counter;
    }
  }
}else{
  while (<infile>){
    $counter++;
    chomp $_;
    print OUTFILE txt2csv($_) , "\r\n";
    if ($counter % 100 == 0){
      print "\b" x $counter_length;
      $counter_length = length $counter;
      print $counter;
    }
  }
}


print "\b" x $counter_length;
print $counter , "\n";

close INFILE;
close OUTFILE;

exit;

sub txt2csv($){
  my $line = shift @_;

  my @items = ();
  my $str = undef;

  for (my $i=0; $i <= $#length; $i++){
    $str = substr($line, 0, $length[$i]);
    substr($line, 0, $length[$i]+1) = "";
    $str =~ s/^\s*//go;
    $str =~ s/\s*$//go;
    push @items, $str;
  }
  return join ',', map {(s/"/""/g or /[\r\n,]/) ? qq("$_") : $_} @items;
}



sub err_txt2csv($){
  my $line = shift @_;

  my @items = ();
  my $str = undef;

  for (my $i=0; $i <= $#length; $i++){
    $str = substr($line, 0, $length[$i]);
    substr($line, 0, $length[$i]+1) = "";
    $str =~ s/^\s*//go;
    $str =~ s/\s*$//go;
    if ($i == 7){
      my @tmp = split /\n/, $str;
      $tmp[2] =~ s/^\s*<string>(.*)<\/string>.*$/\1/go;
      $str = $tmp[2];
    }
    push @items, $str;
  }
  return join ',', map {(s/"/""/g or /[\r\n,]/) ? qq("$_") : $_} @items;
}

0 件のコメント:

コメントを投稿