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