/**
* 承認後成果を指定した日時を基準に取得します。
* @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=====================================
// バージョン３より追加パラメータ情報を取得
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;
}
