PHP CampaignDbTables::getTableName_Pv方法代码示例

    /**
     * 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 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;
    }

    /**
     * クリックカウントを取得します。
     */
    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;
    }

    /**
     * 日別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);
//.........这里部分代码省略.........