// データ取得
$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) {
case "ad_code":
$xmlData[] = $key . ":" . urlencode($val);
break;
case "accrual_date":
$xmlData[] = $key . ":" . str_replace("-", "", $val);
break;
default:
$xmlData[] = $key . ":" . (is_null($val) ? 0 : $val);
break;
}
}
