$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";
