// 過去一年分の年月キーを弾き出す 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;