#!/usr/bin/perl516

use strict;
use DBI;
use LWP::UserAgent;
use HTTP::Request::Common;
use URI::Escape;
use Encode;
use utf8;
use JSON;
use File::Copy;

# MySQL INFO
our $DB_NAME = "DB_NAME";
our $DB_USER = "DB_USER";
our $DB_PASS = "DB_PASS";
our $DB_HOST = "DB_HOST";
our $DB_PORT = "DB_PORT";

my $sql =
#ここに情報取得用のＳＱＬを記載
#カスタムフィールドはpostmetaテーブルに情報が格納されています。
#postsテーブルの記事ＩＤを結合して、カスタムフィールドの一覧を取得します。

my $dbh = DBI->connect("dbi:mysql:dbname=$DB_NAME;host=$DB_HOST;port=$DB_PORT","$DB_USER","$DB_PASS") or die "$!\n Error: failed to connect to DB.\n";
$dbh->do("set names utf8");
my $sth = $dbh->prepare($sql);
$sth->execute();
my @jsonAry;

#Y!MAP API
my $ua = LWP::UserAgent -> new;
my $api_key = "APIKEYを記載";
my $output = "json";

while (my $ary_ref = $sth->fetchrow_arrayref) {
	my $jsonStr;
	my ($id, $post_name, $post_title, $inpcustum_revtxt, $inpcustum_rev_shop_name, $inpcustum_rev_address, $inpcustum_rev_telno, $inpcustum_rev_product_image, $rate, $inpcustum_rev_product_image_th,$inpcustum_rev_opentime, $ramen, $okashi, $rest) = @$ary_ref;

	$inpcustum_rev_address = Encode::decode('utf8', $inpcustum_rev_address);
	$post_title = Encode::decode('utf8', $post_title);
	$ramen = Encode::decode('utf8', $ramen);
	$okashi = Encode::decode('utf8', $okashi);
	$rest = Encode::decode('utf8', $rest);
	$inpcustum_rev_shop_name = Encode::decode('utf8', $inpcustum_rev_shop_name);
	$inpcustum_revtxt = Encode::decode('utf8', $inpcustum_revtxt);
	$inpcustum_rev_product_image = Encode::decode('utf8', $inpcustum_rev_product_image);
	$inpcustum_rev_product_image_th = Encode::decode('utf8', $inpcustum_rev_product_image_th);

	#住所がないレコードはスキップする
	if ($inpcustum_rev_address eq "") {
		#print "スキップしました。住所が取れない$post_title　$inpcustum_rev_address\n";
		next;
	}

	my $url = 'http://geo.search.olp.yahooapis.jp/OpenLocalPlatform/V1/geoCoder';
	my $address = uri_escape_utf8($inpcustum_rev_address);

	$url = $url . "?appid=$api_key&query=$address&output=$output";
	#print $url;
	my $loopcnt = 0;
	my $success = 0;
	my $res;

	while(1) {
		$res = $ua->request(GET $url);
		if ($res->is_success) {
			$success = 1;
			last;
		}
		if ($loopcnt > 1) {
			last;
		}
		sleep 1;
		$loopcnt++;
	}

	#通信失敗したものは処理しない
	if ($success == 0) {
		print "スキップしました。通信失敗$post_title　$inpcustum_rev_address\n";
		next;
	}

	#print $res->content;
	my $items = JSON->new()->decode($res->content);
	my @data = $items->{"Feature"};

	my ($zx,$zy);
	foreach my $item (@data) {
		#print $item;
	 	foreach my $item_text (@$item) {
			my $zahyou = $item_text->{"Geometry"}->{"Coordinates"};
			my @list = split(/\,/, $zahyou, 2);
			$zx = $list[1];
			$zy = $list[0];
			#print $zx ."," .$zy;
			last;
		}
	}
	#座標が取れなかったものは使用しない
	if ($zx eq "" || $zy eq "") {
		print "スキップしました。座標取れない$post_title　$inpcustum_rev_address\n";
		next;
	}

	my $domain = "サイトのドメイン";
	my $imgDir = "WPの画像ディレクトリ";
	$inpcustum_revtxt =~ s/[\r\n]//g;
	$inpcustum_revtxt = substr($inpcustum_revtxt, 0, 50) . "...";
	my $categoryName;
	my $dataType;

	#カテゴリとデータタイプを特定させる
	if ($ramen ne "") {
		$categoryName = $ramen;
		$dataType = 1;
	} elsif ($okashi ne "") {
		$categoryName = $okashi;
		$dataType = 3;
	} else {
		$categoryName = $rest;
		$dataType = 2;
	}

	#画像名を置換
	$inpcustum_rev_product_image_th  =~ /(.*)\.(.*)/;
	my $inpcustum_rev_product_image_th_58  = $1 . "-58x58." . $2;
	$categoryName = sprintf("%-5s", $categoryName);
	$categoryName =~ s/ /　/g;
	my $description = sprintf("<a href='%s%s' target='_blank'><img src='%s' width='100px' height='75px' class='rev-thumbnail'></a>%s<br>評価%s点<br><a href='%s%s' target='_blank'>レポート</a><br clear='all'>", $domain,$post_name . "/",$inpcustum_rev_product_image,$categoryName,$rate,$domain,$post_name. "/");
	$jsonStr = $jsonStr . "$zx";
	my $jsonLine = sprintf("{\"lat\":%s, \"lng\":%s, \"id\":\"%s\", \"title\":\"%s\", \"description\":\"%s\", \"rate\":\"%s\",\"type\":%s,\"thimg\":\"%s\"},\n",$zx,$zy,$id,$inpcustum_rev_shop_name,$description,$rate,$dataType,$inpcustum_rev_product_image_th_58);
	push(@jsonAry, $jsonLine);
}

$sth->finish;
$dbh->disconnect;

my $jsfile = "JSON格納パス";
my $backfile = "バックアップディレクトリ";
my $yyyymmdd = localtime_yyyymmdd();

if (-f $jsfile) {
	copy("$jsfile", "$backfile" . "data_$yyyymmdd.js") or die "error: $!";
}

open(OUT, "> $jsfile");

print OUT "var locations = [" . "\n";
foreach my $line (@jsonAry) {
	print OUT encode('utf-8', $line);
}
print OUT "];";

close(OUT);

print "complate!\n";

sub localtime_yyyymmdd
{
    # 返す文字列 YYYY/MM/DD-HH:MM:SS
    my ($sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst)
        = localtime(time);
    $year = $year + 1900;
    $mon  = $mon  + 1;
    return sprintf("%4d%02d%02d%02d%02d%02d", $year, $mon, $mday, $hour, $min, $sec);
}