/** * 承認後成果を指定した日時を基準に取得します。 * @param string $code キャンペーンコード * @param int $mediaId メディアID * @param array $like 条件 * @param string $targetDate 基準にする日時のカラム名 */ private function &Result_MediaResultDownload_getAccept($code, $mediaId, &$like, $targetDate) { $tables = new CampaignDbTables($code); $name_seika = $tables->getTableName_Seika(); $name_media = $tables->getTableName_Media(); $db =& $this->_core->getDB(); $sql = <<<SQL select CS.seika_id, CS.action_time, CS.media_user_id, CS.media_id, CS.accept_flg, CS.accept_date, CS.session_id, card_id, CASE WHEN CS.landing_id is not null THEN concat('キャンペーン', CS.landing_id) ELSE '' END as landing_name from {$name_seika} as CS inner join {$name_media} as CM on CS.media_id=CM.media_id where CS.media_id=? and {$targetDate} between ? and ? and CS.action_flg=1 SQL; $from = $like['from']; $to = $like['to']; $param = array($mediaId, $from, $to); $data =& $db->query($sql, $param); return $data; }
/** * DBから成果情報を取得する */ public function &Result_MediaCompanyDownload_getAnkenSeikaData(&$anken, $where, &$param) { $db =& $this->_core->getDB(); // テーブル名称 $code = $anken['campaign_code']; $tables = new CampaignDbTables($code); $name_seika = $tables->getTableName_Seika(); $name_media = $tables->getTableName_Media(); // SQL $sql = <<<SQL select M.media_id, M.media_name, CS.accept_oroshi, CS.action_time, CS.session_id, CS.media_user_id, CS.accept_flg, CS.accept_date, CS.action_attribute as bikou1 from {$name_seika} as CS inner join {$name_media} as CM on CS.media_id=CM.media_id inner join ald_media as M on CS.media_id=M.media_id where CS.mail_ng=0 and CM.download_flag=1 {$where} SQL; $data = $db->query($sql, $param); return $data; }
/** * 対象成果を取得します。更新対象の正当性をチェックに使います */ public function Result_ClientReportResultUpload_getTargetSeikaBySessionId($isExtra, $code, $sessionId) { $tables = new CampaignDbTables($code); $name_seika = $tables->getTableName_Seika(); $name_media = $tables->getTableName_Media(); $db =& $this->_core->getDB(); $sql = ""; if ($isExtra == false) { $sql = <<<SQL select CS.accept_flg from {$name_seika} as CS inner join {$name_media} as CM on CS.media_id=CM.media_id where CS.session_id=? SQL; } else { $sql = <<<SQL select CS.extra_flg from {$name_seika} as CS inner join {$name_media} as CM on CS.media_id=CM.media_id where CS.session_id=? SQL; } $param = array($sessionId); $data =& $db->query($sql, $param); return $data; }
/** * 承認後成果を指定した日時を基準に取得します。 * @param string $code キャンペーンコード * @param int $mediaId メディアID * @param array $like 条件 * @param string $targetDate 基準にする日時のカラム名 */ private function &Result_MediaResultDownload_getAccept($code, $mediaId, &$like, $targetDate) { $tables = new CampaignDbTables($code); $name_seika = $tables->getTableName_Seika(); $name_media = $tables->getTableName_Media(); $db =& $this->_core->getDB(); $sql = <<<SQL select CS.amount_price, CS.accept_oroshi, CS.seika_id, CS.action_time, CS.media_user_id, CS.media_id, CS.accept_flg, CS.accept_date, CS.session_id from {$name_seika} as CS inner join {$name_media} as CM on CS.media_id=CM.media_id where CS.media_id=? and {$targetDate} between ? and ? SQL; $from = $like['from']; $to = $like['to']; $param = array($mediaId, $from, $to); $data =& $db->query($sql, $param); return $data; }
/** * 対象成果を取得します(セッションIDがキー)。更新対象の正当性をチェックに使います * @param string $code キャンペーンコード * @param int $sessionId セッションID * @param array $item 更新データ */ public function Result_SeikaModifyMediaUpload_getTargetSeikaBySessionId($code, $sessionId) { $tables = new CampaignDbTables($code); $name_seika = $tables->getTableName_Seika(); $name_media = $tables->getTableName_Media(); $db =& $this->_core->getDB(); $sql = <<<SQL select CS.media_id, CS.old_media_id, CS.accept_price, CS.accept_oroshi, CS.extra_price, CS.extra_oroshi, CS.extra2_price, CS.extra2_oroshi, CS.card_id, CS.old_card_id, CS.memo, CS.upd_user_id from {$name_seika} as CS inner join {$name_media} as CM on CS.media_id=CM.media_id where CS.session_id=? SQL; $param = array($sessionId); $data =& $db->query($sql, $param); return $data; }
/** * 成果データを本日と本日以外をそれぞれ集計します。 */ public function &Result_MediaReportAll_getSeikaCountTodayElseDay($code, $mediaId) { $db =& $this->_core->getDB(); // テーブル名称 $tables = new CampaignDbTables($code); $name_seika = $tables->getTableName_Seika(); $name_media = $tables->getTableName_Media(); // SQL $sql = <<<SQL select sum(CASE WHEN CS.action_flg=1 THEN IF(CS.action_date=?,1,0) ELSE 0 END) as action_today, sum(CASE WHEN CS.action_flg=1 THEN IF(CS.action_date=?,0,1) ELSE 0 END) as action_else, sum(CASE WHEN CS.accept_flg=1 THEN IF(CS.accept_date=?,1,0) ELSE 0 END) as accept_today, sum(CASE WHEN CS.accept_flg=1 THEN IF(CS.accept_date=?,0,1) ELSE 0 END) as accept_else, sum(CASE WHEN CS.extra_flg=1 THEN IF(CS.extra_date=?,1,0) ELSE 0 END) as extra_today, sum(CASE WHEN CS.extra_flg=1 THEN IF(CS.extra_date=?,0,1) ELSE 0 END) as extra_else from {$name_seika} as CS inner join {$name_media} CM on CS.media_id=CM.media_id where (CS.action_flg=1 or CS.accept_flg=1 or CS.extra_flg=1) and CS.media_id=? SQL; $today = date('Y-m-d'); $param = array($today, $today, $today, $today, $today, $today, $mediaId); $data = $db->query($sql, $param); return $data[0]; }
/** * 対象成果を取得します(セッションIDがキー)。更新対象の正当性をチェックに使います(2段階用) * @param string $code キャンペーンコード * @param int $sessionId セッションID */ public function Result_SeikaModifyStatusUpload_getTargetSeikaBySessionIdFor2dankai($code, $sessionId) { $tables = new CampaignDbTables($code); $name_seika = $tables->getTableName_Seika(); $name_media = $tables->getTableName_Media(); $db =& $this->_core->getDB(); //↓↓===========nm90023 2009/08/25 start=================================== //有料成果取得条件追加(action_flg=1) $sql = <<<SQL select case when CS.accept_flg=1 then 'Y' when CS.accept_flg=0 then 'D' else 'N' end accept_flg, CS.accept_date, case when CS.extra_flg=1 then 'Y' when CS.extra_flg=0 then 'D' else 'N' end extra_flg, CS.extra_date, CS.memo, CS.upd_user_id from {$name_seika} as CS inner join {$name_media} as CM on CS.media_id=CM.media_id where CS.session_id=? and CS.action_flg=1 SQL; //↑↑===========nm90023 2009/08/25 end===================================== $param = array($sessionId); $data =& $db->query($sql, $param); return $data; }
/** * 承認後成果情報を取得します * @param string $code キャンペーンコード * @param array $like 検索条件(対象期間) * @param string $targetData 日付基準 (action_date or accept_date) */ private function &Result_ClientResultDownload_getAccept($code, &$like, $targetData) { $db =& $this->_core->getDB(); // テーブル名称 $tables = new CampaignDbTables($code); $name_seika = $tables->getTableName_Seika(); $name_media = $tables->getTableName_Media(); // SQL $sql = <<<SQL select CS.action_time, CS.client_user_id, CS.media_id, CS.accept_flg, CS.accept_date from {$name_seika} as CS inner join {$name_media} as CM on CS.media_id=CM.media_id where {$targetData} between ? and ? and CS.media_id<>99 SQL; $param = array($like['from'], $like['to']); $data =& $db->query($sql, $param); return $this->attachDataClientUserId($data); }
/** * メディアリストを取得します。 * @param array $anken 案件情報 */ public function &Search_MediaList_getMediaList(&$anken) { $db =& $this->_core->getDB(); $tables = new CampaignDbTables($anken['campaign_code']); $name_media = $tables->getTableName_Media(); $sql = <<<SQL select ME.media_name from {$name_media} CM left join ald_media ME on ( CM.media_id = ME.media_id ) SQL; $data = $db->query($sql); return $data; }
/** * 成果データを本日と本日以外をそれぞれ集計します。 */ public function &Result_ClientReportAll_getSeikaCountTodayElseDay($code) { $db =& $this->_core->getDB(); // テーブル名称 $tables = new CampaignDbTables($code); $name_seika = $tables->getTableName_Seika(); $name_media = $tables->getTableName_Media(); // SQL $sql = <<<SQL select sum(CASE WHEN CS.action_flg=1 THEN IF(CS.action_date=?,1,0) ELSE 0 END) as action_today, sum(CASE WHEN CS.action_flg=1 THEN IF(CS.action_date=?,0,1) ELSE 0 END) as action_else, sum(CASE WHEN CS.accept_flg=1 THEN IF(CS.accept_date=?,1,0) ELSE 0 END) as accept_today, sum(CASE WHEN CS.accept_flg=1 THEN IF(CS.accept_date=?,0,1) ELSE 0 END) as accept_else, sum(CASE WHEN CS.accept_flg=0 THEN IF(CS.accept_date=?,1,0) ELSE 0 END) as un_accept_today, sum(CASE WHEN CS.accept_flg=0 THEN IF(CS.accept_date=?,0,1) ELSE 0 END) as un_accept_else, sum(CASE WHEN CS.action_flg=1 THEN IF(CS.action_date=?,CS.amount_price,0) ELSE 0 END) as sales_before_today, sum(CASE WHEN CS.action_flg=1 THEN IF(CS.action_date=?,0,CS.amount_price) ELSE 0 END) as sales_before_else, sum(CASE WHEN CS.accept_flg=1 THEN IF(CS.accept_date=?,CS.amount_price,0) ELSE 0 END) as sales_after_today, sum(CASE WHEN CS.accept_flg=1 THEN IF(CS.accept_date=?,0,CS.amount_price) ELSE 0 END) as sales_after_else from {$name_seika} as CS inner join {$name_media} CM on CS.media_id=CM.media_id where CS.action_flg=1 and CS.media_id<>99 SQL; $today = date('Y-m-d'); $param = array($today, $today, $today, $today, $today, $today, $today, $today, $today, $today); $data = $db->query($sql, $param); return $data[0]; }
/** * メディアごとに成果の集計をします */ public function &Result_ClientReportMedia_getMediaActionAcceptCount($code, $like = null) { $db =& $this->_core->getDB(); $tables = new CampaignDbTables($code); $name_seika = $tables->getTableName_Seika(); $name_media = $tables->getTableName_Media(); $where = ""; $param = array(); // 対象日付条件 $targetData = 'CS.action_date'; if (empty($like['date_target']) == false && $like['date_target'] == 2) { $targetData = 'CS.accept_date'; } if (empty($like['from']) == false && empty($like['to']) == false) { $from = trim($like['from']); $to = trim($like['to']); $where .= " and {$targetData} between ? and ? "; $param = array($from, $to); } $sql = <<<SQL select M.media_name, CS.media_id, sum(CASE WHEN (CS.action_flg=1 and CS.mail_ng=0) THEN 1 ELSE 0 END) as action, sum(CASE WHEN (CS.action_attribute='O' and year(CS.birthday)>=1957 and CS.mail_ng=0) THEN 1 ELSE 0 END) as under, sum(CASE WHEN (CS.action_attribute='O' and year(CS.birthday)<=1956 and CS.mail_ng=0) THEN 1 ELSE 0 END) as upper, sum(CASE WHEN (CS.action_attribute in ('M','T','Z','F') or CS.mail_ng=1) THEN 1 ELSE 0 END) as duplicate, sum(CASE WHEN (CS.accept_flg=1 and CS.mail_ng=0) THEN 1 ELSE 0 END) as accept from {$name_seika} as CS inner join {$name_media} as CM on CS.media_id=CM.media_id inner join ald_media as M on CS.media_id=M.media_id where 1 {$where} group by CS.media_id SQL; $data = $db->query($sql, $param); return $data; }
/** * 提携メディア情報を取得します。 * @param string $mediaId メディアID */ public function &Result_HotenUpload_getTeikeiMedia($code, $mediaId, $haveExtraPrice = true) { $db =& $this->_core->getDB(); try { // 該当提携メディア $tables = new CampaignDbTables($code); $name = $tables->getTableName_Media(); if ($haveExtraPrice == true) { $sql = <<<SQL select price, oroshi_price, extra_price, extra_oroshi from {$name} where media_id=? SQL; } else { $sql = <<<SQL select price, oroshi_price from {$name} where media_id=? SQL; } $param = array($mediaId); $data =& $db->query($sql, $param); } catch (Exception $e) { // テーブルなし throw new MediaNotFountException('sql exception', $e); } if (count($data) != 1) { // 提携メディア特定できない throw new MediaNotFountException(); } return $data[0]; }
/** * 対象成果を取得します。更新対象の正当性をチェックに使います */ public function Result_ClientReportResultUpload_getTargetSeikaBySessionId($code, $sessionId) { $tables = new CampaignDbTables($code); $name_seika = $tables->getTableName_Seika(); $name_media = $tables->getTableName_Media(); $db =& $this->_core->getDB(); //↓↓===========nm90022 2009/08/25 start=================================== //有料成果取得条件追加(action_flg=1) $sql = <<<SQL select CS.accept_flg from {$name_seika} as CS inner join {$name_media} as CM on CS.media_id=CM.media_id where CS.session_id=? and CS.action_flg=1 SQL; //↑↑===========nm90022 2009/08/25 end===================================== $param = array($sessionId); $data =& $db->query($sql, $param); return $data; }
/** * DBから成果情報を取得する * 拡張案件の継承用 */ public function &Result_MediaCompanyDownload_getAnkenSeikaData(&$anken, $where, &$param) { $db =& $this->_core->getDB(); // テーブル名称 $code = $anken['campaign_code']; $tables = new CampaignDbTables($code); $name_seika = $tables->getTableName_Seika(); $name_media = $tables->getTableName_Media(); $extendSelect = ""; if ($anken['price_type_cd'] == 2) { $extendSelect = ",CS.amount_price as bikou1 "; } //↓↓===========nm90027 2009/11/10 start=================================== // 検索項目追加、「itrack_info(※index:「ITRACK_INFO」)」(itrack_info) // 検索項目追加、「vcptn」(vcptn) $extendSelect = ",CS.itrack_info, CS.vcptn "; //↑↑===========nm90027 2009/11/10 end===================================== // バージョン3より追加パラメータ情報を取得 if ($anken['version'] >= 3) { $m = new SummaryModel_v3($this->_core); require_once ABS_LIB . 'data_object/DataSeikaOption_v3.php'; $option =& $m->getSeikaOptionDispForDownLoadCsv($anken['anken_id']); $loginKind = $this->_core->getLoginKind(); foreach ($option as $key => $value) { if ($value['media_csv'] == 1) { $extendSelect .= ",CS.{$key} "; } } // 追加パラメータ名称リスト取得 $valueNameList =& $m->getSeikaOptionValueNameList($anken['anken_id']); } // SQL $sql = <<<SQL select M.media_id, M.media_name, CS.accept_oroshi, CS.action_time, CS.session_id, CS.media_user_id, CS.accept_flg, CS.accept_date {$extendSelect} from {$name_seika} as CS inner join {$name_media} as CM on CS.media_id=CM.media_id inner join ald_media as M on CS.media_id=M.media_id where CM.download_flag=1 and CS.action_flg=1 {$where} SQL; $data = $db->query($sql, $param); if (empty($valueNameList) == true) { return $data; } // 追加パラメータ名称変換 foreach ($data as $i => $row) { foreach ($option as $key => $value) { if ($value['media_csv'] != 1) { continue; } if ($row[$key] == "") { continue; } if (key_exists($row[$key], $valueNameList[$key]) == false) { //↓↓========nm00153 2009/05/26 林 start================================ //↓↓該当Optionカラムのデータを「戻り値」とする該当ID(「O01」~)がない場合 //↓↓該当ID(「O01」~)があり、「戻り値」が一致しない場合 if (empty($valueNameList[$key]) == false) { $data[$i][$key] = "その他"; } else { //↓↓該当ID(「O01」~)がない場合、元データを使用 } //↓↓該当Optionカラムのデータを「戻り値」に持つ場合。 //↑↑========nm00153 2009/05/26 林 end================================== } else { $data[$i][$key] = $valueNameList[$key][$row[$key]]; } } } return $data; }
/** * 変更前単価・卸値取得用SQL作成 */ public function getPriceSelectSQL($code, $mediaId) { $tables = new CampaignDbTables($code); $tableName = $tables->getTableName_Media(); $sql = "select "; $sql .= " price as price_1, oroshi_price as oroshi_1, "; $sql .= " extra_price as price_2, extra_oroshi as oroshi_2 "; $sql .= " from {$tableName} "; $sql .= " where media_id={$mediaId}"; return $sql; }
/** * 成果件数(非承認)を取得します */ private function Result_ClientReportResult_getAcceptNonApproved($code, $from, $to, $targetDate, &$like = null) { $tables = new CampaignDbTables($code); $name_media = $tables->getTableName_Media(); $name_seika = $tables->getTableName_Seika(); $db =& $this->_core->getDB(); $where = ""; $param = array($from, $to); if (empty($like['media_company_id']) == false) { $where .= " and M.media_company_id=? "; $param[] = $like['media_company_id']; } $sql = <<<SQL select sum(CASE WHEN (CS.accept_flg=0) THEN 1 ELSE 0 END) as count from {$name_seika} as CS inner join {$name_media} as CM on CS.media_id=CM.media_id inner join ald_media as M on CM.media_id=M.media_id where {$targetDate} between ? and ? and CS.media_id<>99 and CS.accept_flg=0 {$where} SQL; $data = $db->query($sql, $param); return $data; }
/** * キャリアごとに成果・退会数を取得(退会率:キャリア別:月別レポート) */ public function &Result_ClientReportCarrier_getTaikaiCarrierActionAcceptCount($code, $like = null) { $db =& $this->_core->getDB(); $tables = new CampaignDbTables($code); $name_seika = $tables->getTableName_Seika(); $name_media = $tables->getTableName_Media(); $where = ""; $param = array(); // 対象日付条件 $targetData = 'CS.action_date'; if (empty($like['date_target']) == false && $like['date_target'] == 2) { $targetData = 'CS.accept_date'; } if (empty($like['from']) == false && empty($like['to']) == false) { $from = trim($like['from']); $to = trim($like['to']); $where .= " and {$targetData} between ? and ? "; $param = array($from, $to); } //登録数(承認済みY) //退会数(Y and 退会日あり) //キャリアNULLデータはotherとしてグループ $sql = <<<SQL select if(SE.mob_carrier is null,'other',SE.mob_carrier) mob_carrier, sum(accept_flg) accept, sum(taikai_flg) as taikai, sum(month1) taikai1m, sum(month2) taikai2m, sum(month3) taikai3m from ( select CS.mob_carrier, if(accept_flg=1,1,0) accept_flg, if(accept_flg=1 and taikai_date<>0,1,0) taikai_flg, if(accept_flg=1 and DATEDIFF(taikai_date,action_date)<=30,1,0) month1, if(accept_flg=1 and DATEDIFF(taikai_date,action_date)>30 and DATEDIFF(taikai_date,action_date)<=60,1,0) month2, if(accept_flg=1 and DATEDIFF(taikai_date,action_date)>60 and DATEDIFF(taikai_date,action_date)<=90,1,0) month3 from {$name_seika} CS inner join {$name_media} CM on CS.media_id=CM.media_id where 1 and CS.action_flg=1 {$where} ) SE group by if(SE.mob_carrier is null,'other',SE.mob_carrier) SQL; $data = $db->query($sql, $param); return $data; }
/** * DBから成果情報を取得する * 拡張案件の継承用 */ public function &Result_MediaCompanyDownload_getAnkenSeikaData(&$anken, $where, &$param) { $db =& $this->_core->getDB(); // テーブル名称 $code = $anken['campaign_code']; $tables = new CampaignDbTables($code); $name_seika = $tables->getTableName_Seika(); $name_media = $tables->getTableName_Media(); $extendSelect = ""; if ($anken['price_type_cd'] == 2) { $extendSelect = ",CS.amount_price as bikou1 "; } // SQL $sql = <<<SQL select M.media_id, M.media_name, CS.accept_oroshi, CS.action_time, CS.session_id, CS.media_user_id, CS.accept_flg, CS.accept_date {$extendSelect}, CS.sid as bikou1 from {$name_seika} as CS inner join {$name_media} as CM on CS.media_id=CM.media_id inner join ald_media as M on CS.media_id=M.media_id where CM.download_flag=1 and CS.action_flg=1 {$where} SQL; $data = $db->query($sql, $param); return $data; }
/** * クライアント承認後件数表示を取得します。(卸値単位) * @attention メディアID=99はカウントしない */ public function getAcceptCountWithOroshi() { // テーブル名称 $tables = new CampaignDbTables($this->_code); $name_seika = $tables->getTableName_Seika(); $name_media = $tables->getTableName_Media(); /******************************* * 通常成果承認 *******************************/ // 条件作成 $where = ""; $param = array(); // 基準日付 $target = intval($this->_params['date_target']); $targetData = 'CS.action_date'; if (empty($target) == false && $target == 2) { $targetData = 'CS.accept_date'; } // 期間 $from = $this->_params['from']; $to = $this->_params['to']; if (empty($from) == false && empty($to) == false) { $where .= " and {$targetData} between ? and ? "; $param[] = $from; $param[] = $to; } $addCaption2 = @constant("SEIKA_NAME_345_ACCEPT_1"); // SQL $sql = <<<SQL select CS.accept_oroshi as oroshi_price, count(CS.seika_id) as accept, ' ({$addCaption2})' as add_caption_2 from {$name_seika} as CS inner join {$name_media} as CM on CS.media_id=CM.media_id where CM.media_id<>99 and CS.accept_flg=1 {$where} group by CS.accept_oroshi SQL; $dataAccept =& $this->_db->query($sql, $param); /******************************* * 拡張成果承認 *******************************/ // 条件作成 $where = ""; $param = array(); // 基準日付 $target = intval($this->_params['date_target']); $targetData = 'CS.action_date'; if (empty($target) == false && $target == 2) { $targetData = 'CS.extra_date'; } // 期間 $from = $this->_params['from']; $to = $this->_params['to']; if (empty($from) == false && empty($to) == false) { $where .= " and {$targetData} between ? and ? "; $param[] = $from; $param[] = $to; } $addCaption2 = @constant("SEIKA_NAME_345_ACCEPT_2"); // SQL $sql = <<<SQL select CS.extra_oroshi as oroshi_price, count(CS.seika_id) as accept, ' ({$addCaption2})' as add_caption_2 from {$name_seika} as CS inner join {$name_media} as CM on CS.media_id=CM.media_id where CM.media_id<>99 and CS.extra_flg=1 {$where} group by CS.extra_oroshi SQL; $dataExtra =& $this->_db->query($sql, $param); // 合わせる $data =& $dataAccept; array_splice($data, count($data), 0, $dataExtra); return $data; }
/** * メディア企業ごとに成果の集計をします */ public function &Result_ClientReportMediaCompany_getMediaActionAcceptCount($code, $like = null) { $db =& $this->_core->getDB(); $tables = new CampaignDbTables($code); $name_seika = $tables->getTableName_Seika(); $name_media = $tables->getTableName_Media(); $where = ""; $param = array(); // 対象日付条件 $targetData = 'CS.action_date'; if (empty($like['date_target']) == false && $like['date_target'] == 2) { $targetData = 'CS.accept_date'; } if (empty($like['from']) == false && empty($like['to']) == false) { $from = trim($like['from']); $to = trim($like['to']); $where .= " and {$targetData} between ? and ? "; $param = array($from, $to); } $sql = <<<SQL select M.media_company_id, MC.media_company_name, 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.action_flg=1 THEN CS.amount_price ELSE 0 END) as sales_before, sum(CASE WHEN CS.accept_flg=1 THEN CS.amount_price ELSE 0 END) as sales_after, sum(CASE WHEN CS.accept_flg=0 THEN 1 ELSE 0 END) as un_accept, sum(CASE WHEN CS.accept_flg=1 THEN CS.accept_price ELSE 0 END) as accept_price, sum(CASE WHEN CS.accept_flg=1 THEN CS.accept_oroshi ELSE 0 END) as accept_oroshi from {$name_seika} as CS inner join {$name_media} as CM on CS.media_id=CM.media_id inner join alladin.ald_media as M on CS.media_id=M.media_id inner join alladin.ald_media_company as MC on M.media_company_id=MC.media_company_id where CM.media_id<>99 and CS.action_flg=1 {$where} group by M.media_company_id, MC.media_company_name SQL; $data = $db->query($sql, $param); return $data; }
/** * 承認後成果情報を取得します * @param string $code キャンペーンコード * @param array $like 検索条件(対象期間) * @param string $targetData 日付基準 (action_date or accept_date) */ private function &Result_ClientResultDownload_getAccept($code, &$like, $targetData) { $db =& $this->_core->getDB(); // テーブル名称 $tables = new CampaignDbTables($code); $name_seika = $tables->getTableName_Seika(); $name_media = $tables->getTableName_Media(); $where = ""; $param = array($like['from'], $like['to']); if (empty($like['media_company_id']) == false) { $where .= " and M.media_company_id=? "; $param[] = $like['media_company_id']; } // SQL $sql = <<<SQL select CS.reg_time, CS.action_time, CS.client_user_id, CS.prize_type_id, CS.media_id, M.media_name, CS.accept_flg, CS.accept_date, CASE WHEN CS.landing_id is not null THEN concat('キャンペーン', CS.landing_id) ELSE '' END as landing_name, CS.accept_price, CS.accept_oroshi, CS.referer, CS.referer_title from {$name_seika} as CS inner join {$name_media} as CM on CS.media_id=CM.media_id inner join ald_media as M on CM.media_id=M.media_id where {$targetData} between ? and ? and CS.media_id<>99 and CS.action_flg=1 {$where} SQL; return $db->query($sql, $param); }
/** * 承認後成果情報を取得します * @param string $code キャンペーンコード * @param array $like 検索条件(対象期間) * @param string $targetData 日付基準 (action_date or accept_date) */ private function &Result_ClientResultDownload_getAccept($code, &$like, $targetData, $priceTypeCd) { $db =& $this->_core->getDB(); // テーブル名称 $tables = new CampaignDbTables($code); $name_seika = $tables->getTableName_Seika(); $name_media = $tables->getTableName_Media(); $where = ""; $param = array($like['from'], $like['to']); if (empty($like['media_company_id']) == false) { $where .= " and M.media_company_id=? "; $param[] = $like['media_company_id']; } $extendSelecte = ""; if ($priceTypeCd == 2) { $extendSelecte = ", CS.amount_price"; } //↓↓===========nm90008 2009/06/12 start=================================== //↓↓===========nm90008 start=================================== // SQL //$sql =<<<SQL //select //CS.reg_time, //CS.action_time, //CS.client_user_id, //CS.media_id, //M.media_name, //CS.seika_id, //CS.accept_flg, //CS.accept_date, //CS.accept_price, //CS.accept_oroshi, //CS.referer, //CS.referer_title, //CS.option1, //CS.option2, //CS.option3, //CS.option4, //CS.option5 //{$extendSelecte} //from {$name_seika} as CS //inner join {$name_media} as CM //on CS.media_id=CM.media_id //inner join ald_media as M //on CM.media_id=M.media_id //where //{$targetData} between ? and ? //and CS.media_id<>99 //and CS.action_flg=1 //{$where} //SQL; //↑↑===========nm90008 end===================================== // SQL $sql = <<<SQL select CS.reg_time, CS.action_time, CS.client_user_id, CS.syubetu_cd, CS.media_id, M.media_name, CS.seika_id, CS.accept_flg, CS.accept_date, CS.accept_price, CS.accept_oroshi, CS.referer, CS.referer_title, CS.option1, CS.option2, CS.option3, CS.option4, CS.option5 {$extendSelecte} from {$name_seika} as CS inner join {$name_media} as CM on CS.media_id=CM.media_id inner join ald_media as M on CM.media_id=M.media_id where {$targetData} between ? and ? and CS.media_id<>99 and CS.action_flg=1 {$where} SQL; //↑↑===========nm90008 2009/06/12 end===================================== return $db->query($sql, $param); }
/** * メディア企業報酬金額表示を卸値別に取得します。 * @return array 項目が、卸単価, メディア名, 成果数の配列 */ public function &_getAcceptCountWithOroshi() { // テーブル名称 $tables = new CampaignDbTables($this->_code); $name_seika = $tables->getTableName_Seika(); $name_media = $tables->getTableName_Media(); // 条件作成 $where = ""; $param = array(); // メディアID $mediaId = $this->_params['media_id']; if (empty($mediaId) == false) { $where .= " and CS.media_id=? "; $param[] = $mediaId; } // メディア企業ID $mediaCompanyId = $this->_params['media_company_id']; if (empty($mediaCompanyId) == false) { $where .= " and M.media_company_id=? "; $param[] = $mediaCompanyId; } // 基準日付 $target = intval($this->_params['date_target']); $targetData = 'action_date'; if (empty($target) == false && $target == 2) { $targetData = 'accept_date'; } // 期間 $from = $this->_params['from']; $to = $this->_params['to']; if (empty($from) == false && empty($to) == false) { $where .= " and {$targetData} between ? and ? "; $param[] = $from; $param[] = $to; } // SQL $sql = <<<SQL select CS.accept_oroshi as oroshi, count(CS.seika_id) as accept, sum(CS.accept_price) as sum_price from {$name_seika} as CS inner join {$name_media} as CM on CS.media_id=CM.media_id inner join ald_media as M on CS.media_id=M.media_id where CS.accept_flg=1 {$where} group by CS.accept_oroshi SQL; $data =& $this->_db->query($sql, $param); return $data; }
/** * 対象成果を取得します。更新対象の正当性をチェックに使います */ public function Result_ClientReportResultUpload_getTargetSeika($code, $clientUserId) { $tables = new CampaignDbTables($code); $name_seika = $tables->getTableName_Seika(); $name_media = $tables->getTableName_Media(); $db =& $this->_core->getDB(); // Nの中で一番古い成果 $sql = <<<SQL select CS.seika_id from {$name_seika} as CS inner join {$name_media} as CM on CS.media_id=CM.media_id where CS.client_user_id=? and CS.accept_flg is null order by action_time SQL; $param = array($clientUserId); $data =& $db->query($sql, $param); return $data[0]['seika_id']; }
/** * 未承認成果情報を取得します * @note * アクション日付を基準 */ public function &Result_ClientResultDownload_getData($code, &$like) { $tables = new CampaignDbTables($code); // テーブル名称 $name_seika = $tables->getTableName_Seika(); $name_media = $tables->getTableName_Media(); $db =& $this->_core->getDB(); $where = ""; $param = array(); // 条件 if (isset($like['is_under']) == true && is_bool($like['is_under']) == true) { if ($like['is_under'] == true) { $where .= " and year(CS.birthday)>={$this->_outerYear} "; } else { $where .= " and year(CS.birthday)<={$this->_innerYear} "; } } // attributeList if (empty($like['attribute_list']) == false && is_array($like['attribute_list']) == true) { $hatena = array_fill(0, count($like['attribute_list']), '?'); $where .= " and CS.action_attribute in (" . implode(',', $hatena) . ")"; array_splice($param, count($param), 0, $like['attribute_list']); } // 承認フラグ if (empty($like['accept_flg']) == false) { if ($like['accept_flg'] == "N") { $where .= " and CS.accept_flg is null "; } else { if ($like['accept_flg'] == "Y") { $where .= " and CS.accept_flg=1"; } } } // 重複件数 if (empty($like['duplicate']) == false) { // 属性条件 $attributeList = array('M', 'T', 'Z', 'F', 'G'); $hatena = array_fill(0, count($attributeList), '?'); $where .= " and (CS.action_attribute in (" . implode(',', $hatena) . ")"; array_splice($param, count($param), 0, $attributeList); // 不達メール条件 OR $where .= " or CS.mail_ng=1) "; } else { if (empty($like['with_mail_ng']) == false) { // 不達メールを含む(NM用) // 条件を指定しない } else { // 不達メール条件 $where .= " and CS.mail_ng=0 "; } } // 対象日付条件 $targetData = 'CS.action_date'; if (empty($like['date_target']) == false && $like['date_target'] == 2) { $targetData = 'CS.accept_date'; } if (empty($like['from']) == false && empty($like['to']) == false) { $from = trim($like['from']); $to = trim($like['to']); $where .= " and {$targetData} between ? and ? "; $param[] = $from; $param[] = $to; } // メディア企業ID if (empty($like['media_company_id']) == false) { $where .= " and M.media_company_id=? "; $param[] = $like['media_company_id']; } // SQL $sql = <<<SQL select CS.mail, CS.knj_nm_1, CS.knj_nm_2, CS.kna_nm_1, CS.kna_nm_2, CS.birthday, CS.sex, CS.tel, CS.zip, CS.prefecture, CS.adr_1, CS.adr_2, CS.adr_3, CS.torihiki, CS.torihiki_office, CS.password, CS.job, CS.retirement, CS.reg_time, CS.session_id, CS.seika_id, CS.media_id, CS.media_user_id, CS.action_time, CS.accept_flg, CS.accept_date, CS.action_attribute, CS.mail_ng, CS.accept_price, CS.accept_oroshi from {$name_seika} as CS inner join {$name_media} as CM on CS.media_id=CM.media_id inner join ald_media as M on CM.media_id=M.media_id where CS.media_id<>99 {$where} SQL; $data =& $db->query($sql, $param); return $data; }
/** * 承認後成果情報を取得します * @param string $code キャンペーンコード * @param array $like 検索条件(対象期間) * @param string $targetData 日付基準 (action_date or accept_date) */ private function &Result_ClientResultDownload_getAccept($code, &$like, $targetData) { $db =& $this->_core->getDB(); // テーブル名称 $tables = new CampaignDbTables($code); $name_seika = $tables->getTableName_Seika(); $name_media = $tables->getTableName_Media(); $where = ""; $param = array($like['from'], $like['to']); if (empty($like['media_company_id']) == false) { $where .= " and M.media_company_id=? "; $param[] = $like['media_company_id']; } // SQL $sql = <<<SQL select CS.seika_id, CS.session_id, CS.action_time, CS.client_user_id, CS.media_id, CS.accept_flg, CS.accept_date, CS.accept_price, CS.accept_oroshi from {$name_seika} as CS inner join {$name_media} as CM on CS.media_id=CM.media_id inner join ald_media as M on CM.media_id=M.media_id where {$targetData} between ? and ? and CS.media_id<>99 {$where} SQL; $data =& $db->query($sql, $param); return $this->attachDataPrefixActionId($data); }
/** * 提携メディアを削除します */ public function deleteTeikeiMedia($code, $mediaId, $teikeiMediaId) { $log =& $this->_core->getLogger('deleteTeikeiMedia'); // テーブル名称 $tables = new CampaignDbTables($code); $name = $tables->getTableName_Media(); $db =& $this->_core->getDBForUpdate(); // SQL $sql = "delete from {$name} where teikei_media_id=?"; $param = array($teikeiMediaId); $log->info("delete media_id.{$mediaId}"); return $db->query($sql, $param); }
/** * クリックカウントを取得します。 */ public function Lpo_ReportDaily_getPageStay($code, &$like) { $db =& $this->_core->getDB(); // テーブル名称 $tables = new CampaignDbTables($code); $name_pv = $tables->getTableName_Pv(); $name_media = $tables->getTableName_Media(); // 条件 $where = ""; $param = array(); if (empty($like['from']) == false && empty($like['to']) == false) { $from = trim($like['from']); $to = trim($like['to']); $where .= " and PS.reg_date between ? and ? "; $param = array($from, $to); } // メディアID if (empty($like['media_id']) == false) { $mediaId = $like['media_id']; $where .= " and M.media_id=? "; $param[] = $mediaId; } // メディア名称 if (empty($like['media_name']) == false) { $name = mb_strtolower($like['media_name']); $where .= " and LOWER(M.media_name) like ? "; $param[] = "%{$name}%"; } // メディア企業ID if (empty($like['media_company_id']) == false) { $mediaCompanyId = $like['media_company_id']; $where .= " and MC.media_company_id=? "; $param[] = $mediaCompanyId; } // メディア企業名称 if (empty($like['media_company_name']) == false) { $name = mb_strtolower($like['media_company_name']); $where .= " and LOWER(MC.media_company_name) like ? "; $param[] = "%{$name}%"; } // 案件ID $where .= " and PS.anken_id=? "; $param[] = $like['anken_id']; // パターンID $where .= " and PS.pattern_id=? "; $param[] = $like['lpo_pattern_id']; // SQL $sql = <<<SQL select PS.reg_date as date, avg(PS.stay_second) as avg from lpo_page_stay as PS inner join {$name_media} as CM on PS.media_id=CM.media_id inner join ald_media as M on M.media_id=CM.media_id inner join ald_media_company as MC on M.media_company_id = MC.media_company_id where PS.media_id<>99 {$where} group by PS.reg_date SQL; $data = $db->query($sql, $param); return $data; }
/** * 提携メディアをカウントを取得します。 * @param string $code キャンペーン * @param string media */ public function getTeikeiMedieCount($code, $mediaId) { $db =& $this->_core->getDB(); $tables = new CampaignDbTables($code); $name = $tables->getTableName_Media(); $sql = <<<SQL select count(teikei_media_id) as cnt from {$name} where media_id=? SQL; $param = array($mediaId); $data =& $db->query($sql, $param); return intval($data[0]['cnt']); }
/** * 成果更新用SQL作成 */ public function getPriceUpdateSQL($code, $mediaId, $price, $oroshi, $extraPrice, $extraOroshi) { $tables = new CampaignDbTables($code); $tableName = $tables->getTableName_Media(); $sql = "update {$tableName} set price={$price}, oroshi_price={$oroshi}, extra_price={$extraPrice}, extra_oroshi={$extraOroshi} where media_id={$mediaId}"; return $sql; }