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