PHP CampaignDbTables::getTableName_Media方法代码示例

    /**
     * 承認後成果を指定した日時を基準に取得します。
     * @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;
    }

    /**
     * 対象成果を取得します。更新対象の正当性をチェックに使います
     */
    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;
    }

    /**
     * 対象成果を取得します(セッション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;
    }

    /**
     * 対象成果を取得します(セッション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;
    }

 /**
  * 変更前単価･卸値取得用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;
    }

    /**
     * クライアント承認後件数表示を取得します。(卸値単位)
     * @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_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'];
    }

 /**
  * 提携メディアを削除します
  */
 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;
 }