/**
* 承認後成果を指定した日時を基準に取得します。
* @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_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 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 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;
}
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);
}
}
/**
* 単価履歴対象案件リストを取得
*/
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);
}
/**
* メディアリストを取得します。
* @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 __construct($code)
{
$code = CampaignDbTables::getRealCode($code);
parent::__construct("cpn_{$code}_media");
// 主キー列
$field =& $this->addField(new DataField('teikei_media_id'));
$field->setPrimaryKey();
$field->setMainteField();
// データ列
$this->addField(new DataField('anken_id'));
$this->addField(new DataField('media_id'));
$this->addField(new DataField('media_url'));
$this->addField(new DataField('media_url2'));
$this->addField(new DataField('redirect_url'));
$this->addField(new DataField('price'));
$this->addField(new DataField('oroshi_price'));
$this->addField(new DataField('extra_price'));
$this->addField(new DataField('extra_oroshi'));
$this->addField(new DataField('teikei_from'));
$this->addField(new DataField('teikei_to'));
$this->addField(new DataField('download_flag'));
$this->addField(new DataField('free1_cd'));
// メンテナンス列
$field =& $this->addField(new DataField('upd_time'));
$field->setMainteField();
$field =& $this->addField(new DataField('upd_user_id'));
$field->setMainteField();
// $field =& $this->addField(new DataField('del_flg'));
// $field->setMainteField();
}
/**
* メディアごとに成果の集計をします
*/
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_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_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);
}
public function __construct($code)
{
$code = CampaignDbTables::getRealCode($code);
parent::__construct("cpn_{$code}_media");
// 主キー列
$field =& $this->addField(new DataField('teikei_media_id'));
$field->setPrimaryKey();
$field->setMainteField();
// データ列
$this->addField(new DataField('anken_id'));
$this->addField(new DataField('media_id'));
$this->addField(new DataField('media_url'));
//↓↓===========nm90008 start===================================
$this->addField(new DataField('media_url2'));
//↑↑===========nm90008 end=====================================
$this->addField(new DataField('redirect_url'));
$this->addField(new DataField('price'));
$this->addField(new DataField('oroshi_price'));
$this->addField(new DataField('extra_price'));
$this->addField(new DataField('extra_oroshi'));
$this->addField(new DataField('teikei_from'));
$this->addField(new DataField('teikei_to'));
$this->addField(new DataField('download_flag'));
$this->addField(new DataField('free1_cd'));
$this->addField(new DataField('suspend_from'));
$this->addField(new DataField('suspend_to'));
$this->addField(new DataField('asp_admin_url'));
$this->addField(new DataField('asp_login_id'));
$this->addField(new DataField('asp_password'));
$this->addField(new DataField('asp_seika_kind'));
$this->addField(new DataField('asp_op_memo'));
// メンテナンス列
$field =& $this->addField(new DataField('upd_time'));
$field->setMainteField();
$field =& $this->addField(new DataField('upd_user_id'));
$field->setMainteField();
// $field =& $this->addField(new DataField('del_flg'));
// $field->setMainteField();
}
/**
* 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=====================================
// バージョン３より追加パラメータ情報を取得
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);
}
}
/**
* 提携メディアを削除します
*/
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);
}
/**
* 承認後成果情報を取得します
* @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);
}
/**
* 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;
}
/**
* キャリアごとに成果・退会数を取得（退会率：キャリア別：月別レポート）
*/
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;
}
/**
* メディア企業報酬金額表示を卸値別に取得します。
* @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;
}
/**
* 成果件数(非承認)を取得します
*/
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;
}
/**
* 未承認成果情報を取得します
* @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;
}
/**
* 変更前単価･卸値取得用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;
}
/**
* クリックカウントを取得します。
*/
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;
}
/**
* 対象成果を取得します。更新対象の正当性をチェックに使います
*/
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'];
}
/**
* 成果更新用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;
}
/**
* 提携メディアをカウントを取得します。
* @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']);
}
/**
* 成果テーブルに追加します
*/
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);
}
