PHP CampaignDbTables类代码示例

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

 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_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;
    }

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

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

    /**
     * 成果件数(非承認)を取得します
     */
    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;
    }

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