// 取得資料庫連線
include_once dirname(__FILE__) . "/.library.php";
// 常用函數
include_once dirname(__FILE__) . "/.config.php";
// 設定檔
for ($i = 0; $i < count($GA_Profile); $i++) {
$site = $GA_Profile[$i]["site"];
echo "Site: " . $site . "\n";
Sleep(3);
foreach ($dbh->query("SELECT `user`,`cnt` FROM `ga_data` WHERE `tag` = '201253' and `site` = " . $site . " and `is_done` = 0 ORDER BY `user`") as $row) {
if (!empty($row)) {
$user = $row["user"];
echo "- User: "******" - ";
$sql = "SELECT sum(cnt) as cnt FROM ga_data WHERE tag in (201253, 201301) and site = " . $site . " and is_done = 0 and user = "******"cnt"];
echo "cnt: " . $cnt;
$sql = "UPDATE `ga_data` SET `cnt` = :cnt WHERE `tag` = '201301' and `site` = :site and is_done = 0 and `user` = :user";
$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
if ($sth->execute(array(":cnt" => $cnt, ":site" => $site, ":user" => $user))) {
echo " - 寫入成功" . "\n";
} else {
echo " - 寫入失敗: " . get_db_error_msg($sth) . "\n";
}
unset($result);
}
}
unset($row);
}
}
VALUES
(:user, :status, :history, :is_w2f, :w2f_tag, :is_f2w, :f2w_tag)
EOD;
$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
echo "- 寫入資料" . ($sth->execute($data_array) ? "成功" : "失敗: " . get_db_error_msg($sth)) . "\n";
}
}
echo "\n";
}
echo "總數: " . ($i + 1) . "\n";
$w2f_rate = $w2f_cnt > 0 && $wed_cnt > 0 ? sprintf("%01.2f", $w2f_cnt / $wed_cnt * 100) : 0;
$f2w_rate = $f2w_cnt > 0 && $fam_cnt > 0 ? sprintf("%01.2f", $f2w_cnt / $fam_cnt * 100) : 0;
echo "婚前族群數量: " . $wed_cnt . "\n";
echo "婚前轉婚後數量: " . $w2f_cnt . "\n";
echo "婚前轉婚後轉換率: " . $w2f_rate . "%\n";
echo "婚後族群數量: " . $fam_cnt . "\n";
echo "婚後轉婚前數量: " . $f2w_cnt . "\n";
echo "婚後轉婚前轉換率: " . $f2w_rate . "%\n";
// 準備寫入報表
$data_array = array(":tag" => $bw, ":w2f_rate" => $w2f_rate, ":f2w_rate" => $f2w_rate);
$sql = "SELECT COUNT(1) as 'cnt' FROM `report` WHERE `tag` = '" . $bw . "'";
$result = $dbh->query($sql)->fetch();
if (!empty($result["cnt"])) {
$sql = "UPDATE `report` SET `w2f_rate` = :w2f_rate, `f2w_rate` = :f2w_rate WHERE `tag` = :tag";
$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
echo "- 修改資料" . ($sth->execute($data_array) ? "成功" : "失敗: " . get_db_error_msg($sth)) . "\n";
} else {
$sql = "INSERT INTO `report` (`tag`, `w2f_rate`, `f2w_rate`) VALUES (:tag, :w2f_rate, :f2w_rate)";
$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
echo "- 新增資料" . ($sth->execute($data_array) ? "成功" : "失敗: " . get_db_error_msg($sth)) . "\n";
}
$sth->execute(array(":user" => $user, ":tag" => $tag, ":site" => 1));
$result = $sth->fetch();
$wed_cnt = intval($result["cnt"]);
echo "-- 婚前瀏覽頁數: " . $wed_cnt . "\n";
// 婚後(site=2)的總頁數
$sth->execute(array(":user" => $user, ":tag" => $tag, ":site" => 2));
$result = $sth->fetch();
$fam_cnt = intval($result["cnt"]);
echo "-- 婚後瀏覽頁數: " . $fam_cnt . "\n";
// 決定該會員在本週所屬哪一個族群 1 = 婚前; 2 = 婚後
$status = $wed_cnt > $fam_cnt ? 1 : 2;
echo "-- 該週所屬族群: " . ($status == 1 ? "婚前" : "婚後") . "\n";
// 將結果新增到資料庫
$sql = "INSERT INTO `log` (`id`, `tag`, `user`, `status`) VALUES (0, :tag, :user, :status)";
$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
if ($sth->execute(array(":tag" => $tag, ":user" => $user, ":status" => $status))) {
echo "-- 寫入 log 成功\n";
// 設定該筆資料已經處理過
$sql = "UPDATE `ga_data` SET `is_done` = 1 WHERE `tag` = :tag and `user` = :user";
$sth = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
echo "-- 處理 ga_data is_done " . ($sth->execute(array(":tag" => $tag, ":user" => $user)) ? "成功" : "失敗: " . get_db_error_msg($sth)) . "\n";
} else {
echo "-- 寫入 log 失敗: " . get_db_error_msg($sth) . "\n";
}
} else {
echo "- 沒有任何週次資料需要被處理\n";
break;
}
}
}
}