/**
* PVを本日と本日以外をそれぞれ集計します。
*/
public function &Result_ClientReportAll_getPvCountTodayElseDay($code)
{
$db =& $this->_core->getDB();
// テーブル名称
$tables = new CampaignDbTables($code);
$name_pv = $tables->getTableName_Pv();
$name_media = $tables->getTableName_Media();
// SQL
$sql = <<<SQL
select
sum(CASE WHEN CP.reg_date=? THEN count ELSE 0 END)
as pv_today,
sum(CASE WHEN CP.reg_date=? THEN 0 ELSE count END)
as pv_else
from {$name_pv} as CP
inner join {$name_media} as CM
on CP.media_id=CM.media_id
where
CP.pv_syubetsu='pv'
and CP.media_id<>99
SQL;
$today = date('Y-m-d');
$param = array($today, $today);
$data = $db->query($sql, $param);
return $data[0];
}
/**
* キャリアごとにPVの集計をします
*/
public function &Result_ClientReportCarrier_getCarrierPvCount($code, $like = null)
{
$db =& $this->_core->getDB();
// テーブル名称
$tables = new CampaignDbTables($code);
$name_pv = $tables->getTableName_Pv();
// キャリア一覧
$carriers = "'" . join("', '", $this->_carrierList) . "'";
// 条件
$where = "";
$param = array();
if (empty($like['from']) == false && empty($like['to']) == false) {
$from = trim($like['from']);
$to = trim($like['to']);
$param = array($from, $to);
$where .= " and CP.reg_date between ? and ? ";
}
// SQL
$sql = <<<SQL
select
CP.pv_syubetsu,
sum(CP.count) as pv
from {$name_pv} as CP
where
CP.media_id<>99
and CP.pv_syubetsu in ({$carriers})
{$where}
group by CP.pv_syubetsu
SQL;
$data = $db->query($sql, $param);
return $data;
}
/**
* 日別PVカウントを取得します。
*/
public function getDailyPv($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 CP.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}%";
}
// SQL
$sql = <<<SQL
select
CP.reg_date as date,
sum(CASE CP.pv_syubetsu WHEN 'pv' THEN count ELSE 0 END) as pv
from {$name_pv} as CP
inner join {$name_media} as CM
on CP.media_id=CM.media_id
inner join ald_media as M
on M.media_id=CM.media_id
where 1
and CP.media_id<>99
{$where}
group by CP.reg_date
SQL;
$data = $db->query($sql, $param);
return $data;
}
/**
* メディア業ごとにPVの集計をします
*/
public function &Result_ClientReportMediaCompany_getMediaPvCount($code, $like = null)
{
$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']);
$param = array($from, $to);
$where .= " and CP.reg_date between ? and ? ";
}
// SQL
$sql = <<<SQL
select
MC.media_company_name,
M.media_company_id,
sum(CP.count) as pv
from {$name_pv} as CP
inner join {$name_media} as CM
on CP.media_id=CM.media_id
inner join ald_media as M
on CM.media_id=M.media_id
inner join ald_media_company as MC
on M.media_company_id=MC.media_company_id
where
CM.media_id<>99
and CP.pv_syubetsu='pv'
{$where}
group by M.media_company_id, MC.media_company_name
SQL;
$data = $db->query($sql, $param);
return $data;
}
/**
* クリックカウントを取得します。
*/
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;
}
/**
* 日別PVカウントを取得します。
*/
public function Result_ClientReportDaily_getDailyPv($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 CP.reg_date 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
CP.reg_date as date,
sum(CP.count) as pv
from {$name_pv} as CP
inner join {$name_media} as CM
on CP.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
CP.media_id<>99
and CP.pv_syubetsu='pv'
{$where}
group by CP.reg_date
SQL;
$data = $db->query($sql, $param);
return $data;
}
public function &Result_ClientReportMedia_getMediaPvCount($code, $like = null, $mediaIdList = null)
{
//↑↑===========nm00224 2010/11/17 end=====================================
$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']);
$param = array($from, $to);
$where .= " and CP.reg_date between ? and ? ";
}
//↓↓===========nm00224 2010/11/17 start===================================
// メディアIDリスト
if (empty($mediaIdList) == false) {
$list = explode(",", $mediaIdList);
$item = array();
foreach ($list as $id) {
$item[] = " CP.media_id=? ";
$param[] = $id;
}
$s = implode(" or ", $item);
$where .= " and ({$s}) ";
}
//↑↑===========nm00224 2010/11/17 end=====================================
// SQL
$sql = <<<SQL
select
M.media_name,
CP.media_id,
sum(CP.count) as pv
from {$name_pv} as CP
inner join {$name_media} as CM
on CP.media_id=CM.media_id
inner join ald_media as M
on CP.media_id=M.media_id
where
CM.media_id<>99
and CP.pv_syubetsu='pv'
{$where}
group by CP.media_id, M.media_name
SQL;
$data = $db->query($sql, $param);
return $data;
}
/**
* 日別PVカウントを取得します。
*/
public function Result_MediaReportDaily_getDailyPv($code, $mediaId, &$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) {
$where = ' and CP.reg_date between ? and ? ';
$param = array($like['from'], $like['to']);
}
$sql = <<<SQL
select
CP.reg_date as date,
sum(CP.count) as pv
from {$name_pv} as CP
inner join {$name_media} as CM
on CP.media_id=CM.media_id
where CP.reg_date \\!= ''
{$where}
and CP.media_id=?
and CP.pv_syubetsu = 'pv'
group by CP.reg_date
SQL;
$param[] = $mediaId;
$data = $db->query($sql, $param);
return $data;
}
/**
* 直媒体データの媒体レポートデータを取得。
*
*/
public function Result_BaitaiReportIndex_getChokuBaitaiReportData($like = null, $ankenInfo)
{
$code = $ankenInfo['campaign_code'];
$tables = new CampaignDbTables($code);
// テーブル名称
$name_pv = $tables->getTableName_Pv();
$name_media = $tables->getTableName_Media();
$db =& $this->_core->getDB();
// 検索内容
$select = "";
// カラム「直媒体URL」
$columnName = 'choku_baitai_url';
$isChokuBaitaiUrlExists = $this->Result_BaitaiReportIndex_isColumnExistsInTeikei($code, $columnName);
if ($isChokuBaitaiUrlExists) {
$select .= " CM.choku_baitai_url as baitai_url, ";
} else {
$select .= " '' as baitai_url, ";
}
// カラム「直媒体名」
$columnName = 'choku_baitai_name';
$isChokuBaitaiNameExists = $this->Result_BaitaiReportIndex_isColumnExistsInTeikei($code, $columnName);
if ($isChokuBaitaiNameExists) {
$select .= " CM.choku_baitai_name as baitai_name, ";
} else {
$select .= " '' as baitai_name, ";
}
// カラム「直媒体種別」
$columnName = 'choku_baitai_type';
$isChokuBaitaiTypeExists = $this->Result_BaitaiReportIndex_isColumnExistsInTeikei($code, $columnName);
if ($isChokuBaitaiTypeExists) {
$select .= " CM.choku_baitai_type as baitai_type, ";
} else {
$select .= " '' as baitai_type, ";
}
// 条件
$where = "";
$param = array();
// 検索日付From
if (empty($like['target_date_from']) == false) {
$where .= " and CP.reg_date >= '{$like['target_date_from']}' ";
$targetDateFrom = $like['target_date_from'];
}
// 検索日付To
if (empty($like['target_date_to']) == false) {
$where .= " and CP.reg_date <= '{$like['target_date_to']}' ";
}
// メディアID
if (empty($like['media_id']) == false) {
$mediaId = $like['media_id'];
$where .= " and CM.media_id=? ";
$param[] = $mediaId;
}
// メディア名称
if (empty($like['media_name']) == false) {
$name = mb_strtolower($like['media_name']);
$where .= " and LOWER(AM.media_name) like ? ";
$param[] = "%{$name}%";
}
// メディア企業ID
if (empty($like['media_company_id']) == false) {
$mediaCompanyId = $like['media_company_id'];
$where .= " and AMC.media_company_id=? ";
$param[] = $mediaCompanyId;
}
// メディア企業名称
if (empty($like['media_company_name']) == false) {
$name = mb_strtolower($like['media_company_name']);
$where .= " and LOWER(AMC.media_company_name) like ? ";
$param[] = "%{$name}%";
}
$sql = <<<SQL
select
sum(CASE WHEN CP.pv_syubetsu='pv' THEN CP.count ELSE 0 END) as baitai_click,
CP.reg_date as target_date,
AMC.media_company_id,
AMC.media_company_name,
CM.media_id,
CM.choku_baitai_flg,
AM.media_name,
'-' as baitai_id,
{$select}
'-' as baitai_impression
from {$name_pv} as CP
inner join {$name_media} as CM
on CP.media_id = CM.media_id
inner join ald_media as AM
on CM.media_id = AM.media_id
inner join ald_media_company as AMC
on AM.media_company_id = AMC.media_company_id
where 1
and CM.choku_baitai_flg = '1'
{$where}
GROUP BY CP.media_id
ORDER BY CM.media_id
SQL;
$data =& $db->query($sql, $param);
return $data;
}
