yseto/gist:3938690

## gistfile1.pl
#!/usr/bin/env perl
use DBIx::Simple;
use strict;
use warnings;
use utf8;

#
#C:\Users\Username\AppData\Roaming\Skype\Skype Account Name\main.db
#
my $html = <<HTML;
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="ja" lang="ja">
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
<title>skype message</title>
<style type="text/css">
table,th,td{border:1px #333 solid;padding:0.5em;white-space:nowrap;}
table{border-collapse:collapse;margin:0.5em;line-height:1.1;}
th{font-weight:normal;background:#eee;}
</style>
</head>
<body>
<table>
HTML

my $htmlc = <<HTMLC;
</table>
</body>
</html>

HTMLC

use Digest::SHA1 qw(sha1_hex);
use File::stat;
use File::Copy;
use DateTime;

my $db = DBIx::Simple->connect('dbi:SQLite:dbname=main.db')
  || die DBIx::Simple->error;

my @result = $db->query(
    'SELECT from_dispname, body_xml, timestamp, chatname FROM messages;')
  ->hashes;

warn "Output start.";

mkdir "output";

for my $data (@result) {
    $data->{'body_xml'} =~ s/\r\n/<br\/>/g;

    my $dt =
      DateTime->from_epoch( epoch => $data->{'timestamp'} )
      ->set_time_zone('Asia/Tokyo');

    #    use Data::Dumper;
    #    print Dumper $data;

    my $txt =
        "<tr><td>"
      . $dt->ymd . " "
      . $dt->hms
      . "</td><td>"
      . $data->{'from_dispname'}
      . "</td><td>"
      . $data->{'body_xml'}
      . "</td></tr>\n";
    my $fn = "output/skype-log_" . sha1_hex( $data->{'chatname'} ) . ".html";

    unless ( -f $fn ) {
        open( my $fh, ">", $fn ) or die;
        print $fh $html;
        close($fh);
    }
    open( my $fh, ">>", $fn ) or die;
    print $fh $txt;
    close($fh);

    utime( $data->{'timestamp'}, $data->{'timestamp'}, $fn );
}

warn "Output ended.";
warn "Rename start.";

my @cl = $db->query('SELECT distinct chatname FROM Messages;')->hashes;

for my $data (@cl) {

    my $fn    = "output/skype-log_" . sha1_hex( $data->{'chatname'} ) . ".html";
    my $stat  = stat($fn);
    unless (defined $stat){
        warn "no file: $fn";
	next;
    }
    my $mtime = $stat->mtime;
    if ( -f $fn ) {
        open( my $fh, ">>", $fn ) or die;
        print $fh $htmlc;
        close($fh);
    }
    utime( $mtime, $mtime, $fn );

    my $dt =
      DateTime->from_epoch( epoch => $mtime )->set_time_zone('Asia/Tokyo');

    my $ch = $data->{'chatname'};
    $ch =~ s/_skype_account//g;
    $ch =~ s/\#(.*)\/\$(.*);(.*)/$1=$2/;

    my $dstfn =
      "output/skype-log_" . $dt->ymd . "-" . $dt->hms('-') . "_" . $ch . ".html";
    move( $fn, $dstfn );

}

warn "Rename ended. process completed.";
	#!/usr/bin/env perl
	use DBIx::Simple;
	use strict;
	use warnings;
	use utf8;

	#
	#C:\Users\Username\AppData\Roaming\Skype\Skype Account Name\main.db
	#
	my $html = <<HTML;
	<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
	<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="ja" lang="ja">
	<head>
	<meta http-equiv="content-type" content="text/html; charset=utf-8" />
	<title>skype message</title>
	<style type="text/css">
	table,th,td{border:1px #333 solid;padding:0.5em;white-space:nowrap;}
	table{border-collapse:collapse;margin:0.5em;line-height:1.1;}
	th{font-weight:normal;background:#eee;}
	</style>
	</head>
	<body>
	<table>
	HTML

	my $htmlc = <<HTMLC;
	</table>
	</body>
	</html>

	HTMLC

	use Digest::SHA1 qw(sha1_hex);
	use File::stat;
	use File::Copy;
	use DateTime;

	my $db = DBIx::Simple->connect('dbi:SQLite:dbname=main.db')
	\|\| die DBIx::Simple->error;

	my @result = $db->query(
	'SELECT from_dispname, body_xml, timestamp, chatname FROM messages;')
	->hashes;

	warn "Output start.";

	mkdir "output";

	for my $data (@result) {
	$data->{'body_xml'} =~ s/\r\n/<br\/>/g;

	my $dt =
	DateTime->from_epoch( epoch => $data->{'timestamp'} )
	->set_time_zone('Asia/Tokyo');

	# use Data::Dumper;
	# print Dumper $data;

	my $txt =
	"<tr><td>"
	. $dt->ymd . " "
	. $dt->hms
	. "</td><td>"
	. $data->{'from_dispname'}
	. "</td><td>"
	. $data->{'body_xml'}
	. "</td></tr>\n";
	my $fn = "output/skype-log_" . sha1_hex( $data->{'chatname'} ) . ".html";

	unless ( -f $fn ) {
	open( my $fh, ">", $fn ) or die;
	print $fh $html;
	close($fh);
	}
	open( my $fh, ">>", $fn ) or die;
	print $fh $txt;
	close($fh);

	utime( $data->{'timestamp'}, $data->{'timestamp'}, $fn );
	}

	warn "Output ended.";
	warn "Rename start.";

	my @cl = $db->query('SELECT distinct chatname FROM Messages;')->hashes;

	for my $data (@cl) {

	my $fn = "output/skype-log_" . sha1_hex( $data->{'chatname'} ) . ".html";
	my $stat = stat($fn);
	unless (defined $stat){
	warn "no file: $fn";
	next;
	}
	my $mtime = $stat->mtime;
	if ( -f $fn ) {
	open( my $fh, ">>", $fn ) or die;
	print $fh $htmlc;
	close($fh);
	}
	utime( $mtime, $mtime, $fn );

	my $dt =
	DateTime->from_epoch( epoch => $mtime )->set_time_zone('Asia/Tokyo');

	my $ch = $data->{'chatname'};
	$ch =~ s/_skype_account//g;
	$ch =~ s/\#(.)\/\$(.);(.*)/$1=$2/;

	my $dstfn =
	"output/skype-log_" . $dt->ymd . "-" . $dt->hms('-') . "_" . $ch . ".html";
	move( $fn, $dstfn );

	}

	warn "Rename ended. process completed.";