public function getOnline($StartTime, $EndTime, $ServerId, $oWherePartnerPermission) { if (($StartTime + $EndTime) / 2 > time()) { $Return['AvgOnline'] = 0; $Return['LowOnline'] = 0; $Return['HighOnline'] = 0; } else { //查询列 $select_fields = array('ServerId', 'AvgOnline' => 'avg(CurOnline)', 'LowOnline' => 'min(CurOnline)', 'HighOnline' => 'max(HighestOnline)'); //初始化查询条件 $whereStartTime = " Time >= '" . $StartTime . "' "; $whereEndTime = " Time <= '" . $EndTime . "' "; $whereServer = $ServerId ? " ServerId = " . $ServerId . " " : ""; $whereCondition = array($whereStartTime, $whereEndTime, $whereServer, $oWherePartnerPermission); $group_fields = array('ServerId'); $groups = Base_common::getGroupBy($group_fields); //生成查询列 $fields = Base_common::getSqlFields($select_fields); //生成分类汇总列 $where = Base_common::getSqlWhere($whereCondition); $Date = date("Ym", ($StartTime + $EndTime) / 2); $table_to_process = Base_Widget::getDbTable($this->table) . "_" . $Date; $Return = array('UserCount' => 0, 'OnlineCount' => 0); $sql = "SELECT {$fields} FROM {$table_to_process} as log where 1 " . $where . $groups; $Online = $this->db->getAll($sql); foreach ($Online as $key => $val) { $Return['AvgOnline'] += $val['AvgOnline']; $Return['LowOnline'] += $val['LowOnline']; $Return['HighOnline'] += $val['HighOnline']; } } return $Return; }
public function GetInsertLastLogoutUser($table_suffix) { $table_to_process = Base_Widget::getDbTable($this->table_login_log); $table_to_process .= "_" . $table_suffix; $group_fields = array('UserId', 'AppId', 'PartnerId', 'ServerId'); $groups = Base_common::getGroupBy($group_fields); $where = "and LogoutTime > 0 "; $sql = "select \n t.UserId,t.AppId,t.PartnerId,t.ServerId,t.LoginTime,t.UserLoginIp,t.LogoutTime,t.LoginId,t.UserSourceId,t.UserSourceDetail,t.UserSourceProjectId,t.UserSourceActionId,t.UserRegTime,t.FirstLoginTime \n from (select UserId,AppId,PartnerId,ServerId,max(LogoutTime) as last\n from {$table_to_process} \n where 1 {$where}{$groups}) as log , {$table_to_process} as t \n where t.UserId = log.UserId and t.AppId = log.AppId and t.PartnerId = log.PartnerId and t.ServerId = log.ServerId and t.LogoutTime = log.last"; return $sql; }
public function getMailSentUpByMailAddress($StartDate, $EndDate, $MailType) { //查询列 $select_fields = array('MailSent' => 'count(*)', 'UserMail'); //初始化查询条件 // $whereStart = $StartDate?" end_date >= '".strtotime($StartDate)."' ":""; // $whereEnd = $EndDate?" end_date <= '".(strtotime($EndDate)+86400-1)."' ":""; $whereType = $MailType ? " MailType ='" . $MailType . "' " : ""; $group_fields = array('UserMail'); $groups = Base_common::getGroupBy($group_fields); $whereCondition = array($whereStart, $whereEnd, $whereType); //生成查询列 $fields = Base_common::getSqlFields($select_fields); //生成条件列 $where = Base_common::getSqlWhere($whereCondition); $StatArr['MailSent'] = array(); // foreach($MailFixList as $key => $value) // { // $StatArr['MailSent'][$value['SubFix']] = array('MailSent'=>0,'Detail'=>array()); // } //初始化结果数组 $Date = $StartDate; $DateStart = date("Ymd", strtotime($StartDate)); $DateEnd = date("Ymd", strtotime($EndDate)); $DateList = array(); $Date = $StartDate; do { $D = date("Ymd", strtotime($Date)); $DateList[] = $D; $Date = date("Y-m-d", strtotime("{$Date} +1 day")); } while ($D != $DateEnd); foreach ($DateList as $k => $v) { $table_name = Base_Widget::getDbTable($this->table) . "_log_" . $v; $sql = "SELECT {$fields} FROM {$table_name} as log where 1 " . $where . $groups; $MailSentArr = $this->db->getAll($sql, false); foreach ($MailSentArr as $key => $val) { $t = explode("@", $val['UserMail']); $MailFix = "@" . $t['1']; if (isset($StatArr['MailSent'][$MailFix])) { $StatArr['MailSent'][$MailFix]['MailSent'] += $val['MailSent']; } else { $StatArr['MailSent'][$MailFix] = array('MailSent' => 0, 'Detail' => array()); $StatArr['MailSent'][$MailFix]['MailSent'] += $val['MailSent']; } if (isset($StatArr['MailSent'][$MailFix]['Detail'][$val['UserMail']])) { $StatArr['MailSent'][$MailFix]['Detail'][$val['UserMail']] += $val['MailSent']; } else { $StatArr['MailSent'][$MailFix]['Detail'][$val['UserMail']] = 0; $StatArr['MailSent'][$MailFix]['Detail'][$val['UserMail']] += $val['MailSent']; } } } return $StatArr; }
public function getUserByAge() { //查询列 $select_fields = array('UserAge' => 'floor(datediff(now(),UserBirthDay)/365)', 'UserCount' => 'count(*)'); $group_fields = array('UserAge'); //生成查询列 $fields = Base_common::getSqlFields($select_fields); //生成分类汇总列 $groups = Base_common::getGroupBy($group_fields); $StatArr = array('UserAge' => array(), 'UnDefined' => 0); for ($i = 0; $i <= 15; $i++) { for ($j = 0; $j <= 15; $j++) { $position = array('db_fix' => dechex($i), 'tb_fix' => dechex($j)); $table_name = Base_Common::getUserTable($this->table_communication, $position); $sql = "SELECT {$fields} FROM {$table_name} as log where UserIdCard !='' " . $groups; $UserSexArr = $this->db->getAll($sql, false); if (is_array($UserSexArr)) { foreach ($UserSexArr as $key => $Stat) { if (isset($StatArr['UserAge'][$Stat['UserAge']])) { //累加数据 $StatArr['UserAge'][$Stat['UserAge']]['UserCount'] += $Stat['UserCount']; } else { $StatArr['UserAge'][$Stat['UserAge']] = array('UserCount' => 0); $StatArr['UserAge'][$Stat['UserAge']]['UserCount'] += $Stat['UserCount']; } $StatArr['TotalData']['UserCount'] += $Stat['UserCount']; } } $sql = "SELECT count(*) as UserCount FROM {$table_name} as log where UserIdCard =''"; $UserCount = $this->db->getOne($sql, false); $StatArr['UnDefined'] += $UserCount; $StatArr['TotalData']['UserCount'] += $UserCount; } } return $StatArr; }
public function getQuestionNumList($ConditionList) { $table_to_process = Base_Widget::getDbTable($this->table_question_num); //查询列 $select_fields = array('QuestionNum' => 'sum(questions)', 'QuestionType' => 'question_type', 'Qtype' => 'qtype'); //分类统计列 $group_fields = array('QuestionType', 'Qtype'); //初始化查询条件 if ($ConditionList['StartDate'] == $ConditionList['EndDate']) { $whereDate = $ConditionList['StartDate'] ? " date = '" . $ConditionList['StartDate'] . "' " : ""; $whereStartDate = ""; $whereEndDate = ""; } else { $whereStartDate = $ConditionList['StartDate'] ? " date >= '" . $ConditionList['StartDate'] . "' " : ""; $whereEndDate = $ConditionList['EndDate'] ? " date <= '" . $ConditionList['EndDate'] . "' " : ""; $whereDate = ""; } $whereQtype = $ConditionList['QtypeId'] ? " qtype = " . $ConditionList['QtypeId'] . " " : ""; //$whereQuestionType = $ConditionList['QuestionType']?" question_type in (".$ConditionList['QuestionType'].")":""; $QuestionTypeArrTemp = explode(',', $ConditionList['QuestionType']); $QuestionTypeArr = $this->config->QuestionTypeList; foreach ($QuestionTypeArr as $key => $value) { if (!in_array($key, $QuestionTypeArrTemp)) { unset($QuestionTypeArr[$key]); } } if (count($QuestionTypeArr) < 1) { $QuestionTypeArr = $this->config->QuestionTypeList; } $t = array(); foreach ($QuestionTypeArr as $key => $value) { $t[] = "'" . $key . "'"; } $whereQuestionType = "question_type in (" . implode(",", $t) . ")"; $whereCondition = array($whereDate, $whereStartDate, $whereEndDate, $whereQtype, $whereQuestionType); //print_R($whereCondition); //生成查询列 $fields = Base_common::getSqlFields($select_fields); //生成分类汇总列 $groups = Base_common::getGroupBy($group_fields); //生成条件列 $where = Base_common::getSqlWhere($whereCondition); //初始化问题分类数组 //获取所有需要显示的主分类列表 $QtypeList = $this->getAllQtype(1, 'name,id,trading,pid'); //初始化结果数组 $QuestionNum = array(); foreach ($QuestionTypeArr as $QuestionType => $QuestionTypeName) { $oCategory = new Kubao_Category(); $CategoryInfo = $oCategory->getCategoryByQuestionType($QuestionType, 'name'); $QuestionNum[$QuestionType] = array('content' => $CategoryInfo['name'] . "总量:", 'QuestionNum' => 0, 'QuestionNumList' => array()); foreach ($QtypeList as $key => $QtypeInfo) { if ($QuestionType == "complain") { $TradingConfig = unserialize($QtypeInfo['trading']); //如果该投诉下的问题分类不可直接提交订单申诉则留在列表中,否则去除 if (trim($TradingConfig['directOrderUrl']) == "") { $OrderCount = $this->getOrderCount($QtypeInfo['id'], 'order_count'); $OrderCount = isset($OrderCount['order_count']) ? intval($OrderCount['order_count']) : 0; $QuestionNum[$QuestionType]['QuestionNumList'][$QtypeInfo['id']] = array('QuestionNum' => 0, 'OrderCount' => $OrderCount, 'QtypeId' => $QtypeInfo['id'], 'content' => $QtypeInfo['name'], 'url' => 'http://sc.5173.com/?index/questionTypeDetail/' . $QtypeInfo['id'] . '/' . $QuestionType); $QuestionNum[$QuestionType]['OrderCount'] += $OrderCount; } } else { $QuestionNum[$QuestionType]['QuestionNumList'][$QtypeInfo['id']] = array('QuestionNum' => 0, 'QtypeId' => $QtypeInfo['id'], 'content' => $QtypeInfo['name'], 'url' => 'http://sc.5173.com/?index/questionTypeDetail/' . $QtypeInfo['id'] . '/' . $QuestionType); } } foreach ($QtypeList as $key => $QtypeInfo) { if ($QtypeInfo['pid'] > 0) { unset($QuestionNum[$QuestionType]['QuestionNumList'][$QtypeInfo['pid']]); } } } $sql = "SELECT {$fields} FROM {$table_to_process} where 1 " . $where . $groups; $data = $this->db->getAll($sql); foreach ($data as $key => $value) { //数据累加 if (isset($QuestionNum[$value['QuestionType']]['QuestionNumList'][$value['Qtype']])) { if ($value['QuestionType'] == "complain") { $QuestionNum[$value['QuestionType']]['QuestionNum'] += $value['QuestionNum']; if ($QuestionNum[$value['QuestionType']]['QuestionNumList'][$value['Qtype']]['OrderCount'] > 0) { $value['QuestionNum'] = $value['QuestionNum'] / $QuestionNum[$value['QuestionType']]['QuestionNumList'][$value['Qtype']]['OrderCount']; if ($value['QuestionNum'] > 1 || $value['QuestionNum'] < 1 / 100 / 100) { $value['QuestionNum'] = "0.01%"; //$value['QuestionNum'] = $QuestionNum[$value['QuestionType']]['OrderCount']; } else { $value['QuestionNum'] = sprintf("%2.2f", $value['QuestionNum'] * 100) . "%"; } } else { $value['QuestionNum'] = "0.01%"; //$value['QuestionNum'] = $value['QuestionNum'];//$QuestionNum[$value['QuestionType']]['OrderCount']; } $QuestionNum[$value['QuestionType']]['QuestionNumList'][$value['Qtype']]['QuestionNum'] = $value['QuestionNum']; } else { $QuestionNum[$value['QuestionType']]['QuestionNumList'][$value['Qtype']]['QuestionNum'] = $value['QuestionNum']; $QuestionNum[$value['QuestionType']]['QuestionNum'] += $value['QuestionNum']; } } } //初始化为json可识别对象 foreach ($QuestionTypeArr as $QuestionType => $QuestionTypeName) { $QuestionNum[$QuestionType]['QuestionNumList2'] = array(); foreach ($QuestionNum[$QuestionType]['QuestionNumList'] as $key => $value) { $QuestionNum[$QuestionType]['QuestionNumList2'][] = $value; } $QuestionNum[$QuestionType]['QuestionNumList'] = $QuestionNum[$QuestionType]['QuestionNumList2']; unset($QuestionNum[$QuestionType]['QuestionNumList2']); } if (isset($QuestionNum["complain"])) { $Q = sprintf("%2.2f", $QuestionNum["complain"]['QuestionNum'] / $QuestionNum["complain"]['OrderCount'] * 100) . "%"; if ($Q > 1 || $Q < 1 / 100 / 100) { $QuestionNum["complain"]['QuestionNum'] = "0.01%"; } else { $QuestionNum["complain"]['QuestionNum'] = sprintf("%2.2f", $Q) . "%"; } } return $QuestionNum; }
public function getFirstPayDay($StartDate, $EndDate, $RegStartDate, $RegEndDate, $oWherePartnerPermission) { //查询列 $select_fields = array('FirstPayUser' => 'count(distinct(AcceptUserId))', 'TotalAmount' => 'sum(Amount)', 'Date' => "from_unixtime(PayTime,'%Y-%m-%d')", 'PartnerId', 'AppId'); //分类统计列 $group_fields = array('Date', 'PartnerId', 'AppId'); //初始化查询条件 $whereStartDate = $StartDate ? " PayedTime >= '" . strtotime($StartDate) . "' " : ""; $whereEndDate = $EndDate ? " PayedTime <= '" . (strtotime($EndDate) + 86400 - 1) . "' " : ""; $whereRegStartDate = $RegStartDate ? " UserRegTime >= '" . strtotime($RegStartDate) . "' " : ""; $whereRegEndDate = $RegEndDate ? " UserRegTime <= '" . (strtotime($RegEndDate) + 86400 - 1) . "' " : ""; $whereCondition = array($whereStartDate, $whereEndDate, $whereRegStartDate, $whereRegEndDate, $oWherePartnerPermission); //生成查询列 $fields = Base_common::getSqlFields($select_fields); //生成分类汇总列 $groups = Base_common::getGroupBy($group_fields); //生成条件列 $where = Base_common::getSqlWhere($whereCondition); $date = $StartDate; //初始化结果数组 do { $StatArr['PayDate'][$date] = array('FirstPayUser' => 0, 'Detail' => array()); $date = date("Y-m-d", strtotime($date) + 86400); } while (strtotime($date) <= strtotime($EndDate)); $table_name = Base_Widget::getDbTable($this->table_first); $sql = "SELECT {$fields} FROM {$table_name} as log where 1 " . $where . $groups; $PayDateArr = $this->db->getAll($sql, false); if (is_array($PayDateArr)) { foreach ($PayDateArr as $key => $Stat) { //累加数据 if (isset($StatArr['PayDate'][$Stat['Date']]['Detail'][$Stat['AppId']][$Stat['PartnerId']])) { $StatArr['PayDate'][$Stat['Date']]['FirstPayUser'] += $Stat['FirstPayUser']; $StatArr['PayDate'][$Stat['Date']]['Detail'][$Stat['AppId']][$Stat['PartnerId']]['TotalAmount'] += $Stat['TotalAmount']; } else { $StatArr['PayDate'][$Stat['Date']]['Detail'][$Stat['AppId']][$Stat['PartnerId']] = array('TotalAmount' => 0); $StatArr['PayDate'][$Stat['Date']]['FirstPayUser'] += $Stat['FirstPayUser']; $StatArr['PayDate'][$Stat['Date']]['Detail'][$Stat['AppId']][$Stat['PartnerId']]['TotalAmount'] += $Stat['TotalAmount']; } } } return $StatArr; }
public function getFirstLoginUserByAll($FirstStartDate, $FirstEndDate, $RegStartDate, $RegEndDate, $ServerId, $oWherePartnerPermission, $type) { //查询列 if ($type == 1) { $select_fields = array('LoginUser' => 'count(distinct(UserId))', 'Date' => "from_unixtime(FirstLoginTime,'%Y-%m-%d')", 'DayLag' => "floor((LoginTime-FirstLoginTime)/86400)"); } else { $select_fields = array('LoginUser' => 'count(distinct(UserId))', 'Date' => "from_unixtime(FirstLoginTime,'%Y-%m-%d')", 'DayLag' => "datediff(from_unixtime(LoginTime,'%Y-%m-%d'),from_unixtime(FirstLoginTime,'%Y-%m-%d'))"); } //分类统计列 $group_fields = array('Date', 'DayLag'); //初始化查询条件 $whereStartDate = $FirstStartDate ? " FirstLoginTime >= '" . strtotime($FirstStartDate) . "' " : ""; $whereEndDate = $FirstEndDate ? " FirstLoginTime <= '" . (strtotime($FirstEndDate) + 86400 - 1) . "' " : ""; $whereRegStartDate = $RegStartDate ? " UserRegTime >= '" . strtotime($RegStartDate) . "' " : ""; $whereRegEndDate = $RegEndDate ? " UserRegTime <= '" . (strtotime($RegEndDate) + 86400 - 1) . "' " : ""; $whereServer = $ServerId ? " ServerId = " . $ServerId . " " : ""; $whereCondition = array($whereStartDate, $whereEndDate, $whereRegStartDate, $whereRegEndDate, $whereServer, $oWherePartnerPermission); //生成查询列 $fields = Base_common::getSqlFields($select_fields); //生成分类汇总列 $groups = Base_common::getGroupBy($group_fields); //生成条件列 $where = Base_common::getSqlWhere($whereCondition); $date = $FirstStartDate; //初始化结果数组 do { $StatArr['LoginDate'][$date] = array(); $date = date("Y-m-d", strtotime($date) + 86400); } while (strtotime($date) <= strtotime($FirstEndDate)); $DateStart = date("Ym", strtotime($FirstStartDate)); $DateEnd = date("Ym", strtotime($FirstEndDate)); $DateList = array(); $Date = $FirstStartDate; do { $D = date("Ym", strtotime($Date)); $DateList[] = $D; $Date = date("Y-m-d", strtotime("{$Date} +1 month")); } while ($D != $DateEnd); foreach ($DateList as $key => $value) { $table_name = Base_Widget::getDbTable($this->table_date) . "_" . $value; $sql = "SELECT {$fields} FROM {$table_name} as log where 1 " . $where . $groups; $LoginDateArr = $this->db->getAll($sql, false); if (is_array($LoginDateArr)) { foreach ($LoginDateArr as $key => $Stat) { //累加数据 if (isset($StatArr['LoginDate'][$Stat['Date']][$Stat['DayLag']])) { $StatArr['LoginDate'][$Stat['Date']][$Stat['DayLag']]['LoginUser'] += $Stat['LoginUser']; } else { $StatArr['LoginDate'][$Stat['Date']][$Stat['DayLag']] = array('LoginUser' => 0); $StatArr['LoginDate'][$Stat['Date']][$Stat['DayLag']]['LoginUser'] += $Stat['LoginUser']; } } } } return $StatArr; }
public function getExchangeDay($StartDate, $EndDate, $ExchangeType, $ServerId, $oWherePartnerPermission) { //查询列 $select_fields = array('ExchangeUser' => 'count(distinct(UserId))', 'ExchangeCount' => 'count(*)', 'TotalAppCoin' => 'sum(AppCoin)', 'Date' => "from_unixtime(ExchangeTime,'%Y-%m-%d')"); //分类统计列 $group_fields = array('Date', 'AppId', 'PartnerId'); //初始化查询条件 $whereStartDate = $StartDate ? " ExchangeTime >= '" . strtotime($StartDate) . "' " : ""; $whereEndDate = $EndDate ? " ExchangeTime <= '" . (strtotime($EndDate) + 86400 - 1) . "' " : ""; $whereType = $ExchangeType ? " ExchangeType = " . $ExchangeType . " " : ""; $whereCondition = array($whereStartTime, $whereEndTime, $oWherePartnerPermission, $whereType); //生成查询列 $fields = Base_common::getSqlFields($select_fields); //生成分类汇总列 $groups = Base_common::getGroupBy($group_fields); //生成条件列 $where = Base_common::getSqlWhere($whereCondition); $date = $StartDate; //初始化结果数组 $StatArr['TotalData'] = array('ExchangeUser' => 0, 'ExchangeCount' => 0, 'TotalAppCoin' => 0); do { $StatArr['ExchangeDate'][$date] = array('ExchangeUser' => 0, 'ExchangeCount' => 0, 'TotalAppCoin' => 0); $date = date("Y-m-d", strtotime($date) + 86400); } while (strtotime($date) <= strtotime($EndDate)); $DateStart = date("Ym", strtotime($StartDate)); $DateEnd = date("Ym", strtotime($EndDate)); $DateList = array(); $Date = $StartDate; do { $D = date("Ym", strtotime($Date)); $DateList[] = $D; $Date = date("Y-m-d", strtotime("{$Date} +1 month")); } while ($D != $DateEnd); $oPartnerApp = new Config_Partner_App(); $oArea = new Config_Area(); foreach ($DateList as $key => $value) { $table_name = Base_Widget::getDbTable($this->table) . "_date_" . $value; $sql = "SELECT {$fields} FROM {$table_name} as log where 1 " . $where . $groups; $ExchangeDateArr = $this->db->getAll($sql); if (is_array($ExchangeDateArr)) { foreach ($ExchangeDateArr as $key => $Stat) { if (isset($StatArr['ExchangeDate'][$Stat['Date']])) { $StatArr['ExchangeDate'][$Stat['Date']]['ExchangeCount'] += $Stat['ExchangeCount']; $StatArr['ExchangeDate'][$Stat['Date']]['ExchangeUser'] += $Stat['ExchangeUser']; $StatArr['ExchangeDate'][$Stat['Date']]['TotalAppCoin'] += $Stat['TotalAppCoin']; } else { $StatArr['ExchangeDate'][$Stat['Date']] = array('ExchangeUser' => 0, 'ExchangeCount' => 0, 'TotalAppCoin' => 0); $StatArr['ExchangeDate'][$Stat['Date']]['ExchangeCount'] += $Stat['ExchangeCount']; $StatArr['ExchangeDate'][$Stat['Date']]['ExchangeUser'] += $Stat['ExchangeUser']; $StatArr['ExchangeDate'][$Stat['Date']]['TotalAppCoin'] += $Stat['TotalAppCoin']; } } } } return $StatArr; }
public function getSendStatus($Date, $ServerId, $uType) { $select_fields = array('SendCount' => 'count(*)', 'H' => 'from_unixtime(Sendtime,"%H")', 'm' => 'from_unixtime(SendTime,"%i")'); $whereuType = $uType ? ' uType = ' . $uType . ' ' : ''; $whereServer = $ServerId ? ' ServerId = ' . $ServerId . ' ' : ''; $whereCondition = array($whereuType, $whereServer); $group_fields = array('H', 'm'); //生成分类汇总列 $groups = Base_common::getGroupBy($group_fields); //分类统计列 //生成查询列 $fields = Base_common::getSqlFields($select_fields); //生成条件列 $where = Base_common::getSqlWhere($whereCondition); $table_to_process = $this->getDbTable($this->table_date) . "_" . date("Ymd", strtotime($Date)); $sql = "SELECT {$fields} FROM {$table_to_process} as log where 1 " . $where . $groups; $return = $this->db->getAll($sql); $SendStatus = array(); if (count($return)) { foreach ($return as $key => $value) { $SendStatus[$value['H'] * 60 + $value['m']]['SendCount'] = $value['SendCount']; } } return $SendStatus; }
public function getPveTowerSummary($StartTime, $EndTime, $UserId, $InstMapId, $HeroId, $ServerId, $TowerIndex, $oWherePartnerPermission) { //查询列 $select_fields = array('UserId', 'PassCount' => 'count(*)', 'FirstPassTime' => 'min(EndTowerTime)', 'TotalTime' => 'sum(RunTime)/1000'); //分类统计列 $group_fields = array('UserId'); $groups = Base_common::getGroupBy($group_fields); //初始化查询条件 $whereStartTime = $StartTime ? " CreateTowerTime >= " . $StartTime . " " : ""; $whereEndTime = $EndTime ? " CreateTowerTime <= " . $EndTime . " " : ""; $whereUser = $UserId ? " UserId = " . $UserId . " " : ""; $whereServer = $ServerId ? " ServerId = " . $ServerId . " " : ""; $whereInstMap = $InstMapId ? " SlkId = " . $InstMapId . " " : ""; $whereHero = $HeroId != -1 ? " HeroId = " . $HeroId . " " : ""; $whereIndex = $TowerIndex ? " TowerIndex = " . $TowerIndex . " " : ""; $whereCondition = array($whereUser, $whereStartTime, $whereEndTime, $whereServer, $whereInstMap, $whereHero, $whereIndex, $oWherePartnerPermission); //生成查询列 $fields = Base_common::getSqlFields($select_fields); //生成条件列 $where = Base_common::getSqlWhere($whereCondition); if ($UserId) { //echo "1轮<br>"; $position = Base_Common::getUserDataPositionById($UserId); $table_to_process = Base_Common::getUserTable($this->table_pve_tower_log_user, $position); $sql = "SELECT {$fields} FROM {$table_to_process} as log where 1 " . $where . $groups; $PveTowerSummaryArr = $this->db->getAll($sql, false); if (isset($PveTowerSummaryArr)) { foreach ($PveTowerSummaryArr as $key => $value) { $StatArr['UserList'][$value['UserId']] = $value; } } $StatArr['UserCount'] = count($StatArr['UserList']); } elseif (!$UserId && date('m', $StartTime) == date('m', $EndTime)) { //echo "1轮<br>"; $Date = date("Ym", $StartTime); $table_to_process = Base_Widget::getDbTable($this->table_pve_tower_log) . "_" . $Date; $sql = "SELECT {$fields} FROM {$table_to_process} as log where 1 " . $where . $groups; $PveTowerSummaryArr = $this->db->getAll($sql, false); if (isset($PveTowerSummaryArr)) { foreach ($PveTowerSummaryArr as $key => $value) { $StatArr['UserList'][$value['UserId']] = $value; } } $StatArr['UserCount'] = count($StatArr['UserList']); } if (!$UserId && date('m', $StartTime) != date('m', $EndTime)) { //echo "256轮<br>"; for ($i = 0; $i <= 255; $i++) { $position = Base_Common::getUserDataPositionById(sprintf("%03d", $i)); $table_to_process = Base_Common::getUserTable($this->table_pve_tower_log_user, $position); $sql = "SELECT {$fields} FROM {$table_to_process} as log where 1 " . $where . $groups; $PveTowerSummaryArr = $this->db->getAll($sql, false); if (isset($PveTowerSummaryArr)) { foreach ($PveTowerSummaryArr as $key => $value) { $StatArr['UserList'][$value['UserId']] = $value; } } $StatArr['UserCount'] = count($StatArr['UserList']); } } return $StatArr; }
public function getNpcItemPurchaseDetailCount($StartTime, $EndTime, $UserId, $ServerId, $MoneyType, $oWherePartnerPermission, $ItemListText) { //查询列 $select_fields = array('PurchaseCount' => 'count(*)'); //初始化查询条件 $whereStartTime = $StartTime ? " NpcPurchaseTime >= '" . strtotime($StartTime) . "' " : ""; $whereEndTime = $EndTime ? " NpcPurchaseTime <= '" . strtotime($EndTime) . "' " : ""; $whereServer = $ServerId ? " ServerId = " . $ServerId . " " : ""; $whereItemID = $ItemListText ? " ItemID in ( " . $ItemListText . " )" : ""; $whereUser = $UserId ? " UserId = " . $UserId . " " : ""; $whereMoney = $MoneyType ? " MoneyType = " . $MoneyType . " " : ""; $groups = Base_common::getGroupBy($group_fields); $whereCondition = array($whereStartTime, $whereEndTime, $whereUser, $whereMoney, $whereServer, $oWherePartnerPermission, $whereItemID); //生成查询列 $fields = Base_common::getSqlFields($select_fields); //生成条件列 $where = Base_common::getSqlWhere($whereCondition); if ($UserId) { $position = Base_Common::getUserDataPositionById($UserId); $table_to_process = Base_Widget::getDbTable($this->table_npc_item_purchase) . "_user_" . $position['db_fix']; } else { $Date = date("Ym", strtotime($StartTime)); $table_to_process = Base_Widget::getDbTable($this->table_npc_item_purchase) . "_" . $Date; } $sql = "SELECT {$fields} FROM {$table_to_process} as log where 1 " . $where; $PurchaseCount = $this->db->getOne($sql, false); if ($PurchaseCount) { return $PurchaseCount; } else { return 0; } }
public function getActiveGenLog($StartDate, $EndDate, $oWherePartnerPermission) { //查询列 $select_fields = array('ActiveGenCount' => 'sum(GenedNum)', 'GenDate' => "from_unixtime(GenTime,'%Y-%m-%d')"); //初始化查询条件 $whereStartDate = $StartDate ? " GenTime >= '" . strtotime($StartDate) . "' " : ""; $whereEndDate = $EndDate ? " GenTime <= '" . (strtotime($EndDate) + 86400 - 1) . "' " : ""; $group_fields = array('GenDate'); $groups = Base_common::getGroupBy($group_fields); $whereCondition = array($whereStartDate, $whereEndDate, $oWherePartnerPermission); //生成查询列 $fields = Base_common::getSqlFields($select_fields); //生成条件列 $where = Base_common::getSqlWhere($whereCondition); //初始化结果数组 $Date = $StartDate; $StatArr = array('ActiveGen' => array(), 'TotalData' => array('Total' => 0)); do { $StatArr['ActiveGen'][$Date] = array('ActiveGenCount' => 0); $Date = date("Y-m-d", strtotime($Date) + 86400); } while (strtotime($Date) <= strtotime($EndDate)); $table_name = Base_Widget::getDbTable($this->table_gen_log); $sql = "SELECT {$fields} FROM {$table_name} as log where 1 " . $where . $groups; $CharacterDeadArr = $this->db->getAll($sql, false); foreach ($CharacterDeadArr as $key => $val) { $StatArr['ActiveGen'][$val['GenDate']]['ActiveGenCount'] = $val['ActiveGenCount']; $StatArr['TotalData']['Total'] += $val['ActiveGenCount']; } return $StatArr; }
public function changeHeroByLevel($StartDate, $EndDate, $ServerId, $HeroId, $oWherePartnerPermission, $CurHeroId, $NewHeroId) { //查询列 $select_fields = array('HeroChangeCount' => 'count(*)', 'UserCount' => 'count(distinct(UserId))', 'CharacterLevel' => 'CharacterLevel', 'HeroChangeDate' => "from_unixtime(HeroChangeTime,'%Y-%m-%d')"); //初始化查询条件 $whereStartDate = $StartDate ? " HeroChangeTime >= '" . strtotime($StartDate) . "' " : ""; $whereEndDate = $EndDate ? " HeroChangeTime <= '" . (strtotime($EndDate) + 86400 - 1) . "' " : ""; $whereServer = $ServerId ? " ServerId = " . $ServerId . " " : ""; $whereCurHero = $CurHeroId ? " CurHeroId = " . $CurHeroId . " " : ""; $whereNewHero = $NewHeroId ? " NewHeroId = " . $NewHeroId . " " : ""; $group_fields = array('CharacterLevel'); $groups = Base_common::getGroupBy($group_fields); $whereCondition = array($whereStartDate, $whereEndDate, $whereServer, $oWherePartnerPermission, $whereReason, $whereCurHero, $whereNewHero); //生成查询列 $fields = Base_common::getSqlFields($select_fields); //生成条件列 $where = Base_common::getSqlWhere($whereCondition); //初始化结果数组 $StatArr = array('ChangeHero' => array()); for ($i = 0; $i <= 50; $i++) { $StatArr['ChangeHero'][$i] = array('HeroChangeCount' => 0, 'UserCount' => 0); } $DateStart = date("Ym", strtotime($StartDate)); $DateEnd = date("Ym", strtotime($EndDate)); $DateList = array(); $Date = $StartDate; do { $D = date("Ym", strtotime($Date)); $DateList[] = $D; $Date = date("Y-m-d", strtotime("{$Date} +1 month")); } while ($D != $DateEnd); foreach ($DateList as $k => $v) { $table_name = Base_Widget::getDbTable($this->table_change) . "_" . $v; $sql = "SELECT {$fields} FROM {$table_name} as log where 1 " . $where . $groups; $ChangeHeroLogoutArr = $this->db->getAll($sql, false); foreach ($ChangeHeroLogoutArr as $key => $val) { $StatArr['ChangeHero'][$val['CharacterLevel']]['HeroChangeCount'] += $val['HeroChangeCount']; $StatArr['ChangeHero'][$val['CharacterLevel']]['UserCount'] += $val['UserCount']; } } return $StatArr; }
public function getCharacterLevel($ServerId, $oWherePartnerPermission) { //查询列 $select_fields = array('UserId', 'MaxLevel' => 'max(CharacterLevel)', 'ServerId'); //初始化查询条件 $whereServer = $ServerId ? " ServerId = " . $ServerId . " " : ""; $group_fields = array('UserId', 'ServerId'); $groups = Base_common::getGroupBy($group_fields); $whereCondition = array($whereServer, $oWherePartnerPermission); //生成查询列 $fields = Base_common::getSqlFields($select_fields); //生成条件列 $where = Base_common::getSqlWhere($whereCondition); //初始化结果数组 $StatArr = array('Level' => array()); for ($i = 1; $i <= 50; $i++) { $StatArr['Level'][$i] = array('UserCount' => 0); } for ($i = 0; $i <= 15; $i++) { $table_name = Base_Widget::getDbTable($this->table_levelup_log) . "_user_" . dechex($i); $sql = "SELECT {$fields} FROM {$table_name} as log where 1 " . $where . $groups; $CharacterLevelArr = $this->db->getAll($sql, false); foreach ($CharacterLevelArr as $key => $val) { $StatArr['Level'][$val['MaxLevel']]['UserCount']++; } } return $StatArr; }
public function getCharacterTaskCompleteByType($StartDate, $EndDate, $ServerId, $TaskId, $TaskType, $oWherePartnerPermission, $TaskTypeList) { //查询列 $select_fields = array('CompleteCount' => 'count(*)', 'UserCount' => 'count(distinct(UserId))', 'TaskType', 'TaskId'); //初始化查询条件 $whereStartDate = $StartDate ? " HeroTaskCompleteTime >= '" . strtotime($StartDate) . "' " : ""; $whereEndDate = $EndDate ? " HeroTaskCompleteTime <= '" . (strtotime($EndDate) + 86400 - 1) . "' " : ""; $whereServer = $ServerId ? " ServerId = " . $ServerId . " " : ""; $whereTask = $TaskId ? " TaskId = " . $TaskId . " " : ""; $whereTaskType = $TaskType != 999 ? " TaskType = " . $TaskType . " " : ""; $group_fields = array('TaskType', 'TaskId'); $groups = Base_common::getGroupBy($group_fields); $whereCondition = array($whereStartDate, $whereEndDate, $whereServer, $oWherePartnerPermission, $whereTask, $whereTaskType); //生成查询列 $fields = Base_common::getSqlFields($select_fields); //生成条件列 $where = Base_common::getSqlWhere($whereCondition); //初始化结果数组 foreach ($TaskTypeList as $Type => $TypeData) { $StatArr['TaskType'][$Type] = array("name" => $TypeData, 'CompleteCount' => 0, 'UserCount' => 0); } $DateStart = date("Ym", strtotime($StartDate)); $DateEnd = date("Ym", strtotime($EndDate)); $DateList = array(); $Date = $StartDate; do { $D = date("Ym", strtotime($Date)); $DateList[] = $D; $Date = date("Y-m-d", strtotime("{$Date} +1 month")); } while ($D != $DateEnd); foreach ($DateList as $k => $v) { $table_name = Base_Widget::getDbTable($this->table_task_complete_log) . "_" . $v; $sql = "SELECT {$fields} FROM {$table_name} as log where 1 " . $where . $groups; $CharacterTaskAccpetArr = $this->db->getAll($sql, false); foreach ($CharacterTaskAccpetArr as $key => $val) { $StatArr['TaskType'][$val['TaskType']]['CompleteCount'] += $val['CompleteCount']; $StatArr['TaskType'][$val['TaskType']]['UserCount'] += $val['UserCount']; if (!isset($StatArr['Task'][$val['TaskId']])) { $StatArr['Task'][$val['TaskId']]['CompleteCount'] = $val['CompleteCount']; $StatArr['Task'][$val['TaskId']]['UserCount'] = $val['UserCount']; } else { $StatArr['Task'][$val['TaskId']]['CompleteCount'] += $val['CompleteCount']; $StatArr['Task'][$val['TaskId']]['UserCount'] += $val['UserCount']; } } } return $StatArr; }