/** * 既存媒体情報チェック、既存しないかつ媒体情報正常取得レコードを返却 */ 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']; } // 既存媒体情報の場合、媒体情報の更新結果はNG(更新しません) 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]; }