/**
* 既存媒体情報チェック、既存しないかつ媒体情報正常取得レコードを返却
*/
public function checkExistsBaitai($code, &$list, $aspLib)
{
try {
$baitaiList = array();
$actionIdQuestArray = array();
$actionIdQuest = "";
$actionIdArray = array();
// 媒体リスト正常取得レコードを集める
foreach ($list as $key => $baitai) {
if ($baitai['baitai_result'] == 'OK' || $baitai['baitai_result'] == 'nm209' || array_key_exists($baitai['baitai_result'], $aspLib->apiLostDataStatusInfo)) {
$baitaiList[] = $baitai;
$actionIdArray[] = $baitai['action_id'];
$actionIdQuestArray[] = "?";
}
}
$actionIdQuest = implode(',', $actionIdQuestArray);
$baitaiCheckList = array();
// 媒体テーブルから既存アクションIDを抽出
if (count($actionIdArray) != 0) {
// DB接続
$table = new CampaignDbTables($code);
$name = $table->getTableName_Baitai();
$db =& $this->_core->getDBForUpdate();
$sql = <<<SQL
select
action_id
from {$name}
where
action_id in ({$actionIdQuest})
SQL;
$data =& $db->query($sql, $actionIdArray);
$existsActionIdArray = array();
// 既存媒体情報ありの場合、既存しないレコードを返却
if (count($data) != 0) {
// 既存媒体リストのactionIdを集める
foreach ($data as $key => $values) {
$existsActionIdArray[] = $values['action_id'];
}
// 既存媒体情報の場合、媒体情報の更新結果はＮＧ（更新しません）
foreach ($list as $key => $baitai) {
if (in_array($baitai['action_id'], $existsActionIdArray)) {
$list[$key]['baitai_result'] = 'nm208';
}
}
// 更新用媒体リストを集める
foreach ($baitaiList as $key => $baitai) {
if (in_array($baitai['action_id'], $existsActionIdArray) == false) {
$baitaiCheckList[] = $baitai;
}
}
// 既存媒体情報無しの場合、全てレコードを返却
} else {
$baitaiCheckList = $baitaiList;
}
}
return $baitaiCheckList;
} catch (Exception $e) {
// エラーメッセージをメールで送信
// メール件名
$subject = "媒体情報反映処理結果：エラー";
// メール文面を設定
$message = __FUNCTION__ . "\n";
$message .= $e->toText();
$this->sendMailTo($message, $subject);
}
}
/**
* ASP媒体情報を付与
*/
public function &Result_ClientResultDownload_attachClientReportBaitaiInfo(&$data, $anken)
{
$db =& $this->_core->getDB();
// テーブル名称
$tables = new CampaignDbTables($anken['campaign_code']);
$name_baitai = $tables->getTableName_Baitai();
$sql = <<<SQL
select
CB.action_id,
CB.baitai_id,
CB.baitai_name,
CB.baitai_url
from {$name_baitai} as CB
SQL;
$baitaiData =& $db->query($sql);
// 媒体情報リスト整理
$dataBaitaiList = array();
foreach ($baitaiData as $baitai) {
$sessionId = $this->detachPrefixActionId($anken['id_prefix'], $baitai['action_id']);
$dataBaitaiList[$sessionId] = $baitai;
}
// 媒体情報つけ
foreach ($data as $k => $v) {
$sessionId = $v['session_id'];
if (array_key_exists($sessionId, $dataBaitaiList)) {
$data[$k]['baitai_id'] = $dataBaitaiList[$sessionId]['baitai_id'];
$data[$k]['baitai_name'] = $dataBaitaiList[$sessionId]['baitai_name'];
$data[$k]['baitai_url'] = $dataBaitaiList[$sessionId]['baitai_url'];
}
}
return $data;
}
public function &Result_BaitaiReportIndex_getBaitaiSeikaList($code, $idPrefix, $targetDateFrom, $targetDateTo, $baitaiList)
{
//↑↑===========nm00256 2012/01/20 end==================================
$tables = new CampaignDbTables($code);
// テーブル名称
$name_media = $tables->getTableName_Media();
$name_seika = $tables->getTableName_Seika();
$name_baitai = $tables->getTableName_Baitai();
$db =& $this->_core->getDB();
//↓↓===========nm00256 2012/02/02 start==================================
$param = array();
//↑↑===========nm00256 2012/02/02 end==================================
// 結合
$join = "";
//↓↓===========nm00256 2012/03/08 start==================================
// ロスデータ以外の場合
//if ($baitaiName != $this->_lossName) {
if ($baitaiList['baitai_name'] != $this->_lossName) {
$join .= " left join {$name_media} as CM";
$join .= " on CS.media_id = CM.media_id";
$join .= " inner join ald_media as AM";
$join .= " on CM.media_id = AM.media_id";
$join .= " inner join ald_media_company as AMC";
$join .= " on AM.media_company_id = AMC.media_company_id";
}
//↑↑===========nm00256 2012/03/08 end==================================
// 直媒体以外の場合
//if (!$chokuBaitaiMediaFlg) {
if ($baitaiList['choku_baitai_flg'] != 1) {
$join .= " inner join {$name_baitai} as CB";
$join .= " on CS.session_id = abs(replace(CB.action_id, '{$idPrefix}', '')) ";
}
// 条件
$where = "";
//↓↓===========nm00256 2012/01/20 start==================================
//if (empty($targetDate) == false) {
// $where .= " and CS.action_date = '{$targetDate}' ";
//}
// 検索日付From
if (empty($targetDateFrom) == false) {
$where .= " and CS.action_date >= '{$targetDateFrom}' ";
}
// 検索日付To
if (empty($targetDateTo) == false) {
$where .= " and CS.action_date <= '{$targetDateTo}' ";
}
//↑↑===========nm00256 2012/01/20 end==================================
//↓↓===========nm00256 2012/02/17 start================================
// 該当レコードのメディアID
//if (empty($mediaId) == false) {
// $where .= " and CS.media_id = '{$mediaId}' ";
//}
// 該当レコードのメディア企業ID
if (empty($baitaiList['media_company_id']) == false && $baitaiList['baitai_name'] != $this->_lossName) {
$where .= " and AMC.media_company_id = '{$baitaiList['media_company_id']}' ";
}
// 直媒体の場合
//if ($chokuBaitaiMediaFlg && empty($mediaId) == false) {
if ($baitaiList['choku_baitai_flg'] && empty($baitaiList['media_id']) == false) {
//$where .= " and CS.media_id = '{$mediaId}' ";
$where .= " and CS.media_id = '{$baitaiList['media_id']}' ";
}
//↑↑===========nm00256 2012/02/17 end==================================
//↓↓===========nm00256 2012/01/20 start==================================
//// 該当の媒体ID
//if (empty($baitaiId) == false) {
// $where .= " and CB.baitai_id = '{$baitaiId}' ";
//}
// 直媒体以外の場合
//if (!$chokuBaitaiMediaFlg) {
if ($baitaiList['choku_baitai_flg'] != 1) {
// 媒体ID使用の場合、媒体IDからアクションデータと紐付け
if ($baitaiList['use_baitai_id_flg'] == 1) {
//$where .= " and CB.baitai_id = '{$baitaiId}'";
$where .= " and CB.baitai_id = '{$baitaiList['baitai_id']}'";
//↓↓===========nm00256 2012/03/08 start==================================
// 媒体ID使用しない場合、媒体名からアクションデータと紐付け
} else {
//$where .= " and CB.baitai_url = '{$baitaiUrl}'";
//$where .= " and CB.baitai_name = '" . mysql_real_escape_string($baitaiName) . "'";
$where .= " and CB.baitai_name = '" . mysql_real_escape_string($baitaiList['baitai_name']) . "'";
//↑↑===========nm00256 2012/03/08 end==================================
}
}
//↑↑===========nm00256 2012/01/20 end==================================
//↓↓===========nm00256 2012/03/08 start==================================
// 「$name_media」テーブルとの結合：オプション化
$sql = <<<SQL
select
sum(CASE WHEN CS.action_flg=1 THEN 1 ELSE 0 END) as action,
sum(CASE WHEN CS.accept_flg=1 THEN 1 ELSE 0 END) as accept,
sum(CASE WHEN CS.accept_flg=0 THEN 1 ELSE 0 END) as deny,
sum(CASE WHEN (CS.accept_flg is null and CS.action_flg=1) THEN 1 ELSE 0 END) as unaccept
from {$name_seika} as CS
{$join}
where 1
{$where}
SQL;
//↑↑===========nm00256 2012/03/08 end==================================
$data =& $db->query($sql, $param);
return $data[0];
}
