PHP CampaignDbTables::getTableName_Seika方法代码示例

    /**
     * 承認後成果情報を取得します
     * @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);
    }

    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 Result_MediaReportDaily_getDailySeika($code, $mediaId, &$like)
    {
        $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';
        } else {
            if (empty($like['date_target']) == false && $like['date_target'] == 3) {
                $targetData = 'CS.extra_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
{$targetData} as date,
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,
sum(CASE WHEN CS.extra_flg=1 THEN 1 ELSE 0 END) as extra
from {$name_seika} as CS
inner join {$name_media} as CM
on CS.media_id=CM.media_id
where 1
{$where}
and CS.media_id=?
group by {$targetData}

SQL;
        $param[] = $mediaId;
        $data =& $db->query($sql, $param);
        return $data;
    }

    /**
     * 日別成果カウントを取得します。
     */
    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;
    }

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

    /**
     * 日別成果カウントを取得します。
     */
    public function getDailySeika($code, &$like)
    {
        $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);
        }
        // メディア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
{$targetData} as date,
sum(CASE WHEN (CS.action_flg=1 and CS.mail_ng=0) THEN 1 ELSE 0 END) as action,
sum(CASE WHEN (CS.accept_flg=1 and CS.mail_ng=0) THEN 1 ELSE 0 END) as accept,
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
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 M.media_id=CM.media_id
where 
CS.media_id<>99
{$where}
group by {$targetData}
SQL;
        $data =& $db->query($sql, $param);
        return $data;
    }

    /**
     * 日別成果カウントを取得します。
     */
    public function Result_ClientReportDaily_getDailySeika($code, &$like)
    {
        $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);
        }
        // メディア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
{$targetData} as date,
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,
sum(CASE WHEN CS.accept_flg=1 THEN CS.accept_price ELSE 0 END) as accept_price,
sum(CASE WHEN CS.accept_flg=1 THEN CS.accept_oroshi ELSE 0 END) as 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 M.media_id=CM.media_id

inner join ald_media_company as MC
on M.media_company_id = MC.media_company_id

where
CS.media_id<>99
and CS.action_flg=1
{$where}
group by {$targetData}
SQL;
        $data =& $db->query($sql, $param);
        return $data;
    }

    /**
     * 日別成果カウントを取得します。
     */
    public function Lpo_ReportDaily_getDailySeika($code, &$like)
    {
        $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);
        }
        // メディア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 CS.lpo_pattern_id=? ";
        $param[] = $like['lpo_pattern_id'];
        // SQL
        $sql = <<<SQL
select
{$targetData} as date,
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
inner join {$name_media} as CM
on CS.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
CS.media_id<>99
{$where}
group by {$targetData}
SQL;
        $data =& $db->query($sql, $param);
        return $data;
    }

    /**
     * 成果テーブルに追加します
     */
    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);
    }

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