$fromDay = $current_month . "01"; $toDay = $next_month . "01"; } else { $y = substr($check_date, "0", "4"); $m = substr($check_date, "4", "2"); $d = substr($check_date, "6", "2"); $fromDay = $check_date; $toDay = date("Ymd", mktime(0, 0, 0, $m, $d + 1, $y)); } // 登録経過別取得のためのサブクエリ $sub = "SELECT " . " media_cd, " . " SUBSTRING(trade_datetime, 1, 10) AS trade_date, " . " SUM(IF(user_regist_datetime >= " . $current_month . "01000000 AND user_regist_datetime < " . $next_month . "01000000, trade_amount, 0)) AS current_month_sales_male, " . " SUM(IF(user_regist_datetime >= " . $two_month . "01000000 AND user_regist_datetime < " . $current_month . "01000000, trade_amount, 0)) AS two_month_sales_male, " . " SUM(IF(user_regist_datetime >= " . $three_month . "01000000 AND user_regist_datetime < " . $two_month . "01000000, trade_amount, 0)) AS three_month_sales_male, " . " SUM(IF(user_regist_datetime < " . $three_month . "01000000, trade_amount, 0)) AS before_sales_male, " . " SUM(IF(user_regist_datetime >= " . $current_month . "01000000 AND user_regist_datetime < " . $next_month . "01000000, 1, 0)) AS current_month_count_male, " . " SUM(IF(user_regist_datetime >= " . $two_month . "01000000 AND user_regist_datetime < " . $current_month . "01000000, 1, 0)) AS two_month_count_male, " . " SUM(IF(user_regist_datetime >= " . $three_month . "01000000 AND user_regist_datetime < " . $two_month . "01000000, 1, 0)) AS three_month_count_male, " . " SUM(IF(user_regist_datetime < " . $three_month . "01000000, 1, 0)) AS before_count_male, " . " COUNT(DISTINCT(IF(user_regist_datetime >= " . $current_month . "01000000 AND user_regist_datetime < " . $next_month . "01000000, user_id, NULL))) AS current_month_unique_count_male, " . " COUNT(DISTINCT(IF(user_regist_datetime >= " . $two_month . "01000000 AND user_regist_datetime < " . $current_month . "01000000, user_id, NULL))) AS two_month_unique_count_male, " . " COUNT(DISTINCT(IF(user_regist_datetime >= " . $three_month . "01000000 AND user_regist_datetime < " . $two_month . "01000000, user_id, NULL))) AS three_month_unique_count_male, " . " COUNT(DISTINCT(IF(user_regist_datetime < " . $three_month . "01000000, user_id, NULL))) AS before_unique_count_male " . " FROM v_trade_user " . " WHERE trade_datetime >= " . $fromDay . "000000 " . " AND trade_datetime < " . $toDay . "000000 " . " GROUP BY media_cd, trade_date "; // データ取得 $sql = "SELECT " . " media_analyze.media_cd AS ad_code, " . " SUBSTRING(media_analyze.analyze_datetime, 1, 10) AS accrual_date, " . " SUM(media_analyze.access_count) AS access_count, " . " SUM(media_analyze.regist_count) AS regist_male, " . " 0 AS regist_female, " . " 0 AS non_regist_male, " . " 0 AS non_regist_female, " . " SUM(media_analyze.trade_amount) AS total_sales_male, " . " 0 AS total_sales_female, " . " tmp.current_month_sales_male, " . " 0 AS current_month_sales_female, " . " tmp.two_month_sales_male, " . " 0 AS two_month_sales_female, " . " tmp.three_month_sales_male, " . " 0 AS three_month_sales_female, " . " tmp.before_sales_male, " . " 0 AS before_sales_female, " . " tmp.current_month_count_male, " . " 0 AS current_month_count_female, " . " tmp.two_month_count_male, " . " 0 AS two_month_count_female, " . " tmp.three_month_count_male, " . " 0 AS three_month_count_female, " . " tmp.before_count_male, " . " 0 AS before_count_female, " . " tmp.current_month_unique_count_male, " . " 0 AS current_month_unique_count_female, " . " tmp.two_month_unique_count_male, " . " 0 AS two_month_unique_count_female, " . " tmp.three_month_unique_count_male, " . " 0 AS three_month_unique_count_female, " . " tmp.before_unique_count_male, " . " 0 AS before_unique_count_female " . " FROM media_analyze " . " LEFT JOIN ({$sub}) AS tmp " . " ON media_analyze.media_cd = tmp.media_cd " . " AND SUBSTRING(media_analyze.analyze_datetime, 1, 10) = tmp.trade_date" . " WHERE analyze_datetime >= " . $fromDay . "000000 " . " AND analyze_datetime < " . $toDay . "000000 " . " GROUP BY accrual_date, ad_code"; //print $sql . "\n<br>"; //exit(); $result = $calc->executeQuery($sql); if (!$result) { echo "<?xml version=\"1.0\"?>"; echo "<delyzer>"; echo "<judge>false</judge>"; echo "</delyzer>"; exit; } echo "<?xml version=\"1.0\"?>\n"; echo "<delyzer>"; $i = 0; while ($data = $calc->fetchAssoc($result)) { echo "<ad_code_" . $i . ">"; $xmlData = array(); foreach ($data as $key => $val) { switch ($key) {
// 男女指定日登録数 $site_code = $_REQUEST['site_cd']; $targetDate = $_REQUEST['target_date']; if (!$site_code) { exit("siteCdError:" . __LINE__); } if (!$calc->isDate($targetDate)) { exit("targetDateError:" . __LINE__); } $targetDateYear = substr($targetDate, 0, 4); $targetDateMonth = substr($targetDate, 4, 2); $targetDateDay = substr($targetDate, 6, 2); $reformTargetDate = $targetDateYear . "-" . $targetDateMonth . "-" . $targetDateDay; //------------トップページのアクセス数を取得---------------------- $access_sql = "SELECT sum(access_count) AS total_access FROM media_analyze" . " WHERE analyze_datetime >= '" . $reformTargetDate . " 00:00:00'" . " AND analyze_datetime <= '" . $reformTargetDate . " 23:59:59'" . " AND disable = 0 "; $access_result = $calc->executeQuery($access_sql); $resultAccessData = $calc->fetchObject($access_result); $i_total_access = $resultAccessData->total_access; if (!$i_total_access) { exit("topCntError:" . __LINE__); } //--------------対象日時の入金額取得--------------------------- $paymentSql = "SELECT SUM(receive_money) AS t_today_uriage"; $paymentSql .= ",SUM(CASE WHEN pay_type = " . PAY_TYPE_BANK_AUTOMATIONBAS . " THEN receive_money ELSE 0 END) AS bank_uriage"; $paymentSql .= ",SUM(CASE WHEN pay_type = " . PAY_TYPE_CREDIT . " THEN receive_money ELSE 0 END) AS credit_uriage"; $paymentSql .= ",SUM(CASE WHEN pay_type = " . PAY_TYPE_CVD . " THEN receive_money ELSE 0 END) AS cvd_uriage"; $paymentSql .= ",SUM(CASE WHEN pay_type = " . PAY_TYPE_BITCASH . " THEN receive_money ELSE 0 END) AS bitcash_uriage"; $paymentSql .= ",SUM(CASE WHEN pay_type = " . PAY_TYPE_TELECOM . " THEN receive_money ELSE 0 END) AS telecom_uriage"; $paymentSql .= ",SUM(CASE WHEN pay_type = " . PAY_TYPE_BANK_AUTOMATION . " THEN receive_money ELSE 0 END) AS omakase_uriage"; $paymentSql .= ",SUM(CASE WHEN pay_type = " . PAY_TYPE_CCHECK . " THEN receive_money ELSE 0 END) AS ccheck_uriage"; $paymentSql .= ",SUM(CASE WHEN pay_type = " . PAY_TYPE_DIGITALEDY . " THEN receive_money ELSE 0 END) AS edy_uriage";