/** * 承認後成果情報を取得します * @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); }
/** * 成果データを本日と本日以外をそれぞれ集計します。 */ 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]; }
/** * 承認後成果を指定した日時を基準に取得します。 * @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; }
/** * 承認後成果を指定した日時を基準に取得します。 * @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 updateResult($apiKey, $promotionId, $clientUserId, $status, $seikaList) { // 基本情報 $anken =& $this->getAnkenData($promotionId); // キャンペーンコード $code = $anken['campaign_code']; //締め日は承認APIが実行された日とする $accept_date = date("Y-m-d"); // テーブル名称 $tables = new CampaignDbTables($code); $name = $tables->getTableName_Seika(); // DB接続 $db =& $this->_core->getDBForUpdate(); $soapStatus = "100"; if ($status == "Y") { // 承認 $accept_flg = "1"; } else { if ($status == 'D') { // 否認 $accept_flg = "0"; } else { $apiException = new InternalExecuteException(); $this->_log->info("File:[" . basename(__FILE__) . "] Line:[" . __LINE__ . "] Exception:[" . get_class($apiException) . $name . "]"); throw $apiException; } } // クライアントID(更新者) $clientId = $anken['login_id']; // カラム(nm_soap_status)存在するかどうかを判断する $sql = <<<SQL Describe {$name} nm_soap_status SQL; $nm_soap_status = $db->query($sql); if (empty($nm_soap_status) == true) { $apiException = new InternalExecuteException(); $this->_log->info("File:[" . basename(__FILE__) . "] Line:[" . __LINE__ . "] Exception:[" . get_class($apiException) . $name . "]"); throw $apiException; } foreach ($seikaList as $value) { // 承認、否認に更新 $sqlLine = __LINE__; $sql = <<<SQL update {$name} set accept_flg=?, accept_date=?, nm_soap_status=?, upd_time=now(), upd_user_id=? where client_user_id=? and seika_id=? SQL; $param = array($accept_flg, $accept_date, $soapStatus, $clientId, $clientUserId, $value['seika_id']); $this->outPutSqlLog(basename(__FILE__), $sqlLine, $sql, $param); $db->execute($sql, $param); } }
/** * メディアごとに成果の集計をします */ 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'; } else { if (empty($like['date_target']) == false && $like['date_target'] == 3) { $targetData = 'CS.extra_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.extra_flg=1 THEN 1 ELSE 0 END) as extra, 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, sum(CASE WHEN CS.extra_flg=1 THEN CS.extra_price ELSE 0 END) as extra_price, sum(CASE WHEN CS.extra_flg=1 THEN CS.extra_oroshi ELSE 0 END) as extra_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 CS.action_flg=1 {$where} group by M.media_company_id, MC.media_company_name SQL; $data = $db->query($sql, $param); return $data; }
/** * 単価履歴対象案件リストを取得 */ private function Anken_PriceIndex_getTargetListByHistory($code, $like) { // テーブル名称 $tables = new CampaignDbTables($code); $name = $tables->getTableName_Seika(); // DB接続 $db =& $this->_core->getDB(); // 条件を作成 $where = ""; $param = array(); // メディアID if (empty($like['media_id']) == false) { $mediaId = trim($like['media_id']); $where .= " and CS.media_id=? "; $param[] = $mediaId; } if (empty($like['from']) == false && empty($like['to']) == false) { $from = trim($like['from']); $to = trim($like['to']); $where .= " and CS.action_date between ? and ? "; $param[] = $from; $param[] = $to; } // SQL $sql = <<<SQL select M.media_id, M.media_name, CS.accept_price, CS.accept_oroshi, CS.extra_price, CS.extra_oroshi, count(*) as cnt, max(CS.action_date) as max, min(CS.action_date) as min from {$name} as CS inner join ald_media as M on CS.media_id = M.media_id where 1 {$where} group by M.media_id, M.media_name, CS.accept_price, CS.accept_oroshi, CS.extra_price, CS.extra_oroshi SQL; return $db->query($sql, $param); }
/** * 成果データを本日と本日以外をそれぞれ集計します。 */ 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_MediaReportDaily_getDailySeika($code, $mediaId, &$like) { $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'; } else { if (empty($like['date_target']) == false && $like['date_target'] == 3) { $targetData = 'CS.extra_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 {$targetData} as date, 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.extra_flg=1 THEN 1 ELSE 0 END) as extra from {$name_seika} as CS inner join {$name_media} as CM on CS.media_id=CM.media_id where 1 {$where} and CS.media_id=? group by {$targetData} SQL; $param[] = $mediaId; $data =& $db->query($sql, $param); return $data; }
/** * メディアごとに成果の集計をします */ 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; }
/** * 日別成果カウントを取得します。 */ public function Result_ClientReportGoods_getDailySeika($code, &$like) { $db =& $this->_core->getDB(); // テーブル名称 $tables = new CampaignDbTables($code); $name_seika = $tables->getTableName_Seika(); // 条件 $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 = <<<SQL select {$targetData} as date, classid, 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 from {$name_seika} as CS where CS.media_id<>99 and CS.action_flg=1 {$where} group by {$targetData}, classid 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.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->attachDataClientUserId($data); }
/** * ald_media_for_anken_temp生成用INSERT文を作成 * @attention メディアID=99はカウントしない */ public function getInserSQL_ald_media_for_anken_temp() { // テーブル名称 $tables = new CampaignDbTables($this->_code); $name_seika = $tables->getTableName_Seika(); $sql = <<<SQL insert into ald_media_for_anken_temp select a.media_id, a.anken_id, min(a.min_date) as min_date, max(a.max_date) as max_date from ( select media_id, anken_id, min(accept_date) as min_date, max(accept_date) as max_date from {$name_seika} where media_id<>99 and accept_flg is not NULL group by media_id union all select media_id, anken_id, min(extra_date) as min_date, max(extra_date) as max_date from {$name_seika} where media_id<>99 and extra_flg is not NULL group by media_id ) a group by a.media_id SQL; return $sql; }
/** * 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; }
/** * 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; }
public function updateResult($apiKey, $promotionId, $clientUserId, $status, $seikaList) { // 基本情報 $anken =& $this->getAnkenData($promotionId); // キャンペーンコード $code = $anken['campaign_code']; //締め日 $accept_date = $this->getShimeDate($promotionId); // テーブル名称 $tables = new CampaignDbTables($code); $name = $tables->getTableName_Seika(); // DB接続 $db =& $this->_core->getDBForUpdate(); $soapStatus = "100"; if ($status == "Y") { // 承認 $accept_flg = "1"; } else { if ($status == 'D') { // 否認 $accept_flg = "0"; } else { $apiException = new InternalExecuteException(); $this->_log->info("File:[" . basename(__FILE__) . "] Line:[" . __LINE__ . "] Exception:[" . get_class($apiException) . $name . "]"); throw $apiException; } } // クライアントID(更新者) $clientId = $anken['login_id']; // カラム(nm_soap_status)存在するかどうかを判断する $sql = <<<SQL Describe {$name} nm_soap_status SQL; $nm_soap_status = $db->query($sql); if (empty($nm_soap_status) == true) { $apiException = new InternalExecuteException(); $this->_log->info("File:[" . basename(__FILE__) . "] Line:[" . __LINE__ . "] Exception:[" . get_class($apiException) . $name . "]"); throw $apiException; } foreach ($seikaList as $value) { $columnAry = array(); $param = array(); $columnStr = ""; // 否認の場合は金額を0にする if ($status == 'D') { $columnAry[] = " accept_price=?"; $param[] = 0; $columnAry[] = " accept_oroshi=?"; $param[] = 0; $columnAry[] = " amount_price=?"; $param[] = 0; $columnStr = implode(',', $columnAry) . ", "; } // 承認、否認に更新 $sqlLine = __LINE__; $sql = <<<SQL update {$name} set {$columnStr} accept_flg=?, accept_date= ( CASE WHEN accept_date IS NULL THEN ? ELSE accept_date END ) , nm_soap_status=?, upd_time=now(), upd_user_id=? where client_user_id=? and seika_id=? SQL; $param = array_merge($param, array($accept_flg, $accept_date, $soapStatus, $clientId, $clientUserId, $value['seika_id'])); $this->outPutSqlLog(basename(__FILE__), $sqlLine, $sql, $param); $db->execute($sql, $param); } }
/** * 未承認成果情報を取得します * @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; }
/** * 成果件数(非承認)を取得します */ 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; }
/** * 承認後成果情報を取得します * @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; } $addCaption2 = @constant("SEIKA_NAME_212_ACCEPT_1"); // SQL $sql = <<<SQL select CS.accept_oroshi as oroshi, M.media_name, count(CS.seika_id) as accept, sum(CS.accept_price) as sum_price, ' ({$addCaption2})' as add_caption_2 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; $dataAccept =& $this->_db->query($sql, $param); /******************************* * 拡張成果承認 *******************************/ // 条件作成 $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 = '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_212_ACCEPT_2"); // SQL $sql = <<<SQL select CS.extra_oroshi as oroshi, M.media_name, count(CS.seika_id) as accept, sum(CS.extra_price) as sum_price, ' ({$addCaption2})' as add_caption_2 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.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 getDailySeika($code, &$like) { $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); } // メディア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}%"; } // SQL $sql = <<<SQL select {$targetData} as date, sum(CASE WHEN (CS.action_flg=1 and CS.mail_ng=0) THEN 1 ELSE 0 END) as action, sum(CASE WHEN (CS.accept_flg=1 and CS.mail_ng=0) THEN 1 ELSE 0 END) as accept, 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 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 M.media_id=CM.media_id where CS.media_id<>99 {$where} group by {$targetData} SQL; $data =& $db->query($sql, $param); return $data; }
/** * 日別成果カウントを取得します。 */ public function Result_ClientReportDaily_getDailySeika($code, &$like) { $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); } // メディアID if (empty($like['media_id']) == false) { $list = explode(",", $like['media_id']); $item = array(); foreach ($list as $id) { $item[] = " M.media_id=? "; $param[] = $id; } $s = implode(" or ", $item); $where .= " and ({$s}) "; } // メディア名称 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) { $list = explode(",", $like['media_company_id']); $item = array(); foreach ($list as $id) { $item[] = " MC.media_company_id=? "; $param[] = $id; } $s = implode(" or ", $item); $where .= " and ({$s}) "; } // メディア企業名称 if (empty($like['media_company_name']) == false) { $name = mb_strtolower($like['media_company_name']); $where .= " and LOWER(MC.media_company_name) like ? "; $param[] = "%{$name}%"; } // SQL $sql = <<<SQL select {$targetData} as date, 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=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 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 CS.media_id<>99 and CS.action_flg=1 {$where} group by {$targetData} 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; }
/** * 承認後成果情報を取得します * @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); }
/** * 日別成果カウントを取得します。 */ public function Lpo_ReportDaily_getDailySeika($code, &$like) { $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); } // メディア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 CS.lpo_pattern_id=? "; $param[] = $like['lpo_pattern_id']; // SQL $sql = <<<SQL select {$targetData} as date, 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 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 M.media_id=CM.media_id inner join ald_media_company as MC on M.media_company_id = MC.media_company_id where CS.media_id<>99 {$where} group by {$targetData} SQL; $data =& $db->query($sql, $param); return $data; }
/** * メディア企業報酬金額表示を卸値別に取得します。 * @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_HotenActionIdUpload_insertSeika($code, &$data) { $tables = new CampaignDbTables($code); $name = $tables->getTableName_Seika(); $db =& $this->_core->getDBForUpdate(); $data['action_flg'] = 1; $data['action_date'] = $data['action_time']; //↓↓===========nm90022 2009/08/25 start=================================== //更新項目-無料会員登録flg(track_action_flg1) $data['track_action_flg1'] = 1; //更新項目-無料会員登録日(track_date1)、CSV項目「無料会員登録日時(track_time1)」から取得 $data['track_date1'] = $data['track_time1']; //更新項目-無料成果ステータス(track_accept_flg1)、CSV項目「ステータス(accept_flg)」から取得 $data['track_accept_flg1'] = $data['accept_flg']; //更新項目-無料成果承認日(track_accept_date1)、CSV項目「締め日(accept_date)」から取得 $data['track_accept_date1'] = $data['accept_date']; //↑↑===========nm90022 2009/08/25 end===================================== foreach ($data as $k => $v) { $fieldList[] = $k; $valueList[] = '?'; $param[$k] = $v; } // ---------------------------------------------- // 通常 ステータス、締め日 // ---------------------------------------------- $flg = $data['accept_flg']; if ($flg == 'Y') { $param['accept_flg'] = '1'; } else { if ($flg == 'D') { $param['accept_flg'] = '0'; } else { $param['accept_flg'] = null; } } //↓↓===========nm90022 2009/08/25 start=================================== // 無料成果ステータス設定 $flg = $data['track_accept_flg1']; if ($flg == 'Y') { $param['track_accept_flg1'] = '1'; } else { if ($flg == 'D') { $param['track_accept_flg1'] = '0'; } else { $param['track_accept_flg1'] = null; } } //↑↑===========nm90022 2009/08/25 end===================================== // ---------------------------------------------- // 2段階 ステータス、締め日 // ---------------------------------------------- if (array_key_exists('extra_flg', $data) == true) { $flg = $data['extra_flg']; if ($flg == 'Y') { $param['extra_flg'] = '1'; } else { if ($flg == 'D') { $param['extra_flg'] = '0'; } else { if ($flg == 'N') { $param['extra_flg'] = null; } } } } $fields = implode(', ', $fieldList); $values = implode(', ', $valueList); $sql = <<<SQL insert into {$name} ({$fields}, upd_time) values ({$values}, now()) SQL; $db->execute($sql, $param); }
/** * 対象成果を取得します。更新対象の正当性をチェックに使います */ 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']; }
/** * メディア企業報酬金額表示を卸値別に取得します。 * @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.card_id, count(CS.seika_id) as accept, CASE CS.card_id WHEN 'R' THEN CS.accept_oroshi WHEN 'G' THEN CS.extra_oroshi WHEN 'P' THEN CS.extra2_oroshi END as oroshi, sum( CASE CS.card_id WHEN 'R' THEN CS.accept_price WHEN 'G' THEN CS.extra_price WHEN 'P' THEN CS.extra2_price END ) as sum_price, CASE CS.card_id WHEN 'R' THEN ' (レギュラー)' WHEN 'G' THEN ' (ゴールド)' WHEN 'P' THEN ' (プラチナ)' END as add_caption_2 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 and CS.action_flg=1 {$where} group by CS.card_id, oroshi SQL; $data =& $this->_db->query($sql, $param); return $data; }