#!/usr/bin/perl

use strict;
use warnings;
use utf8;
use Encode qw/from_to decode_utf8 encode_utf8 is_utf8/;
use DBI;
#use Translate;
#use File::Path qw(make_path);
#use LWP::Simple;
#use HTML::Entities;

use CGI::Simple;

my $q = new CGI::Simple();

my $dbh = DBI->connect(‘DBI:mysql:<データベース名>:localhost’, ‘<ユーザ名>’, ‘<パスワード>’,{PrintError=>0,AutoCommit=>0});
unless($dbh){die $DBI::errstr;}
my ($hst,$retval,$ref,$count);

=pod
mysql> DESCRIBE wp_posts;
+———————–+———————+——+—–+———————+—————-+
| Field                 | Type                | Null | Key | Default             | Extra          |
+———————–+———————+——+—–+———————+—————-+
| ID                    | bigint(20) unsigned | NO   | PRI | NULL                | auto_increment |
| post_author           | bigint(20) unsigned | NO   | MUL | 0                   |                |
| post_date             | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
| post_date_gmt         | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
| post_content          | longtext            | NO   | MUL | NULL                |                |
| post_title            | text                | NO   | MUL | NULL                |                |
| post_excerpt          | text                | NO   |     | NULL                |                |
| post_status           | varchar(20)         | NO   |     | publish             |                |
| comment_status        | varchar(20)         | NO   |     | open                |                |
| ping_status           | varchar(20)         | NO   |     | open                |                |
| post_password         | varchar(20)         | NO   |     |                     |                |
| post_name             | varchar(500)        | NO   | MUL |                     |                |
| to_ping               | text                | NO   |     | NULL                |                |
| pinged                | text                | NO   |     | NULL                |                |
| post_modified         | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
| post_modified_gmt     | datetime            | NO   |     | 0000-00-00 00:00:00 |                |
| post_content_filtered | longtext            | NO   |     | NULL                |                |
| post_parent           | bigint(20) unsigned | NO   | MUL | 0                   |                |
| guid                  | varchar(255)        | NO   |     |                     |                |
| menu_order            | int(11)             | NO   |     | 0                   |                |
| post_type             | varchar(20)         | NO   | MUL | post                |                |
| post_mime_type        | varchar(100)        | NO   |     |                     |                |
| comment_count         | bigint(20)          | NO   |     | 0                   |                |
+———————–+———————+——+—–+———————+—————-+
23 rows in set (0.02 sec)
=cut

$hst = $dbh->prepare(‘SELECT ID,post_date,post_name,post_type FROM wp_posts’);
unless($hst){ &dberror("104:",$dbh,$hst);    }

$retval = $hst->execute();
unless($retval){ &dberror("66105:",$dbh,$hst); }

#すべて (2,157) |    公開済み (2,119) |    下書き (37) |    非公開 (1)
#旧URLでアクセスしてきても探すみたいで表示される 場合もある
#https://blog.kumacchi.com/2014/11/nexus-72012-wifi-32gb%e3%83%a2%e3%83%87%e3%83%ab%e3%81%abandroid-5-0-lrx21p%e3%83%ad%e3%83%aa%e3%83%9d%e3%83%83%e3%83%97%e3%82%92%e3%83%95%e3%82%a1%e3%82%af%e3%83%88%e3%83%aa%e3%83%bc%e3%82%a4%e3%83%a1%e3%83%bc%e3%82%b8%e3%81%a7%e3%82%a2%e3%83%83%e3%83%97%e3%83%87%e3%83%bc%e3%83%88%e3%81%97%e3%81%9f.html
#https://blog.kumacchi.com/2014/11/nexus-72012-%e3%82%92android-5-0-lrx21p%e3%83%ad%e3%83%aa%e3%83%9d%e3%83%83%e3%83%97%e3%81%ab%e3%81%97%e3%81%a6%e9%87%8d%e3%81%8b%e3%81%a3%e3%81%9f%e3%82%89wipe-cache-partition%e3%81%99%e3%82%8b%e3%81%a8%e3%81%84%e3%81%84%e3%82%89%e3%81%97%e3%81%84.html#more-6313
#https://blog.kumacchi.com/2014/11/18/6291
#旧：/%year%/%monthnum%/%postname%.html

#https://blog.kumacchi.com/2014/11/01/6256
#新：/%year%/%monthnum%/%day%/%post_id%
#RewriteRule ^old_url$ new_url [R=301,L]

#2157|6351|2014-12-15 13:56:12||post_type:post

my %tmp=();
my $cnt = 0;
while($ref = $hst->fetch()){
    my $ID         = $ref->[0] || ”;
    my $post_date  = $ref->[1] || ”;
    my $post_name  = $ref->[2] || ”;
    my $post_type  = $ref->[3] || ”;

    my $yyyy = substr($post_date,0,4);
    my $mm = substr($post_date,5,2);
    my $dd = substr($post_date,8,2);

    if($post_type ne ‘post’){
        $tmp{$post_type} = 1;
    }

#    next if($post_type ne ‘page’);
    next if($post_type eq ‘revision’ || $post_type eq ‘attachment’ || $post_type eq ‘nav_menu_item’ || $post_type eq ‘wpcf7_contact_form’ || $post_type eq ‘page’);

    $cnt++;
    #print "$cnt|$ID|$post_date|$post_name|post_type:$post_type\n";
#    print "$cnt|$ID|$post_date [$yyyy/$mm/$dd]|$post_name|post_type:$post_type\n";

    #RewriteRule ^old_url$ new_url [R=301,L]
    my $decoded_post_name = $q->url_decode($post_name);

    my $rr_a = "RewriteRule ^$yyyy/$mm/$post_name.html\$ /$yyyy/$mm/$dd/$ID [R=301,NC,L]\n";
    my $rr_b = "RewriteRule ^$yyyy/$mm/$decoded_post_name.html\$ /$yyyy/$mm/$dd/$ID [R=301,NC,L]\n";

    #同じなら一個だけ、違ったら別々に出力
    my $rr = ($rr_a eq $rr_b) ? $rr_a : $rr_a . $rr_b;
    print $rr;

}
$hst->finish;
$dbh->disconnect;

#foreach my $key(keys %tmp){
#    print "$key\n";
#}

#====================================================================
#    dberror
#====================================================================
sub dberror{
    my $msg = shift;
    my $dbh = shift;
    my $hst = shift;

    my $errstr = $dbh->errstr;
    $dbh->rollback;
    if($hst) { $hst->finish; undef $hst; }
    $dbh->disconnect;
    die "$msg$errstr\n";
#    debug_print("$msg$errstr\n");
#    error("data base error [$msg$errstr]");
}