// 過去一年分の年月キーを弾き出す
for ($i = 1; $i <= 12; $i++) {
$month_array[$i] = date("Ymd", mktime(0, 0, 0, substr($date, 4, 2) - $i, 1, substr($date, 0, 4)));
}
/*
// トップページのアクセス数を取得
$access_sql = "SELECT media_cd AS ad_code, COUNT(id) AS access_count FROM page_view_log "
."WHERE create_datetime >= ".$date."000000 AND create_datetime <= ".$date."235959 "
."GROUP BY media_cd";
$access_result = $calc->calcAccess($access_sql);
*/
//入金ありのアクセス、入金なしのアクセスに変更 20091105 kiso
$access_sql = "SELECT v_user_profile.media_cd AS ad_code," . "SUM(CASE WHEN v_user_profile.last_access_datetime > " . $week_ago . "000000 AND v_user_profile.total_payment > 0 AND v_user_profile.buy_count > 0 THEN 1 ELSE 0 END) AS week_payed," . "SUM(CASE WHEN v_user_profile.last_access_datetime > " . $one_month_ago . "000000 AND v_user_profile.total_payment > 0 AND v_user_profile.buy_count > 0 THEN 1 ELSE 0 END) AS month_payed," . "SUM(CASE WHEN v_user_profile.last_access_datetime > " . $week_ago . "000000 AND v_user_profile.total_payment = 0 AND v_user_profile.buy_count = 0 THEN 1 ELSE 0 END) AS week_nopay," . "SUM(CASE WHEN v_user_profile.last_access_datetime > " . $one_month_ago . "000000 AND v_user_profile.total_payment = 0 AND v_user_profile.buy_count = 0 THEN 1 ELSE 0 END) AS month_nopay " . "FROM v_user_profile " . "WHERE v_user_profile.user_disable = 0 " . "AND v_user_profile.last_access_datetime > " . $one_month_ago . "000000 " . "GROUP BY ad_code ";
//print $access_sql;
$access_result = $calc->calcAccessDetail($access_sql);
// 本登録人数と仮登録人数の算出
$user_sql = "SELECT ";
//媒体名
$user_sql .= "media_cd AS " . $calc->config["access_column_array"]["1"] . "," . "SUM(CASE WHEN regist_status = 0 THEN 1 ELSE 0 END) AS " . $calc->config["user_column_array"]["1"] . "," . "0 AS " . $calc->config["user_column_array"]["2"] . "," . "SUM(CASE WHEN regist_status = 1 THEN 1 ELSE 0 END) AS " . $calc->config["user_column_array"]["3"] . "," . "0 AS " . $calc->config["user_column_array"]["4"] . " " . "FROM user WHERE regist_datetime >= " . $date . "000000 AND regist_datetime <= " . $date . "235959 " . "GROUP BY " . $calc->config["access_column_array"]["1"];
$user_result = $calc->calcNewUser($user_sql);
// 売上構成と入金構成の算出（登録月別で）
$trd_sql = "SELECT user.media_cd AS ad_code,";
$trd_sql .= "SUM(CASE WHEN user.regist_datetime >= " . $month_array["0"] . "000000 THEN payment_log.receive_money ELSE 0 END) AS sales_male_0,";
$trd_sql .= "0 AS sales_female_0,";
foreach ($month_array as $key => $value) {
//最後はそれ以前として全て取得
if ($key + 1 == 12) {
$trd_sql .= "SUM(CASE WHEN user.regist_datetime < " . $value . "000000 THEN payment_log.receive_money ELSE 0 END) AS sales_male_" . ($key + 1) . ",";
$trd_sql .= "0 AS sales_female_" . ($key + 1) . ",";
break;
