public function view_messageStatisticsAli()
{
$starttime = isset($_GET['starttime']) ? trim($_GET['starttime']) : '';
$endtime = isset($_GET['endtime']) ? trim($_GET['endtime']) : '';
$type = isset($_GET['type']) ? trim($_GET['type']) : 'order';
$wheresql = '';
date_default_timezone_set('America/Los_Angeles');
//转换成洛杉矶时间
$start_stamp = strtotime($starttime);
$end_stamp = strtotime($endtime) + 84600;
date_default_timezone_set('Asia/Shanghai');
if (!empty($starttime)) {
$wheresql .= ' and createtimestamp > ' . $start_stamp;
}
if (!empty($endtime)) {
$wheresql .= ' and createtimestamp < ' . $end_stamp;
}
$localuser = new GetLoacalUserModel();
$mysql_obj = new MysqlModel();
$cat_obj = new messagecategoryModel();
$list = $cat_obj->getAllCategoryInfoList('', 2);
// print_r($list);
$userGroup = array();
foreach ($list as $lival) {
$cat_name = trim($lival['category_name']);
$nameBreak = explode("--", $cat_name);
$userName = array_pop($nameBreak);
if (array_key_exists($userName, $userGroup)) {
$userGroup[$userName][] = $lival['id'];
} else {
$userGroup[$userName] = array($lival['id']);
}
}
$userName2loginName = array('韩庆新' => '*****@*****.**', '王丽娟' => '*****@*****.**', '王玲' => '*****@*****.**', '王铭' => '*****@*****.**', '喻情' => '*****@*****.**', '黄海娣' => '*****@*****.**', '袁岸' => '*****@*****.**', '潘小红' => '*****@*****.**', '陈小燕' => '*****@*****.**', '肖奇生' => '*****@*****.**', '戴霞' => '*****@*****.**', '温健玲' => '*****@*****.**', '肖庚莲' => '*****@*****.**');
$name2id = $localuser->getUserId($userName2loginName);
//中文名称到系统用户id的映射关系
// print_r($name2id);
if ($type == 'order') {
//处理订单留言
$sql = "select fieldId , hasread,count(*) as num from msg_aliordermessage where role=1 and hasread in (0,1)\n {$wheresql} group by fieldId, hasread\n ";
// echo $sql;exit;
$resultList = $mysql_obj->getQueryResult($sql);
$statistics = array();
//统计数据
foreach ($userGroup as $username => $ids) {
if (!array_key_exists($username, $statistics)) {
$statistics[$username] = array(0 => 0, 1 => 0);
}
foreach ($resultList as $row) {
if (in_array($row['fieldId'], $ids)) {
if ($row['hasread'] == 0) {
//累加未回复留言
$statistics[$username][0] += $row['num'];
} elseif ($row['hasread'] == 1) {
//累加已读留言
$statistics[$username][1] += $row['num'];
}
}
}
}
foreach ($statistics as $key => $item) {
$total = array_sum($item);
$statistics[$key]['total'] = $total;
}
// print_r($statistics);
$replyedStatistics = array();
//已回复留言统计
$userIdSql = implode(', ', $name2id);
$sqlre = "select replyer, count(*) as num from msg_aliordermessage where role=1 and replyer in ({$userIdSql}) \n and hasread=2 {$wheresql} group by replyer";
// echo $sqlre;exit;
$replyResult = $mysql_obj->getQueryResult($sqlre);
$replyList = array();
foreach ($replyResult as $reRow) {
$replyList[$reRow['replyer']] = $reRow['num'];
}
$totalNohandle = 0;
//未处理的留言总数
$totalRead = 0;
//已读留言总数
$totalReply = 0;
//已回复留言总数
foreach ($statistics as $r) {
$totalNohandle += $r[0];
$totalRead += $r[1];
$totalReply += $r[2];
}
global $dbConn;
$accountGroup = $this->getAliAccountGroup();
$gropAmount = array();
foreach ($accountGroup as $key => $group) {
$sql_str = implode("', '", $group);
$sql = "select count(1) as num from msg_aliordermessage where receiverid in ('{$sql_str}') {$wheresql}";
$row = $dbConn->fetch_first($sql);
$gropAmount[$key] = $row['num'];
}
$filpNameId = array_flip($name2id);
foreach ($statistics as $user => $rows) {
$userId = isset($name2id[$user]) ? $name2id[$user] : -1;
if (isset($replyList[$userId])) {
$statistics[$user][2] = $replyList[$userId];
} else {
$statistics[$user][2] = 0;
}
}
$thirdmenue = 1;
} else {
//处理站内信
$sql = "select fieldId , hasread, count(*) as num from msg_alisitemessage where role=1 and hasread in (0,1)\n {$wheresql} group by fieldId, hasread\n ";
$resultList = $mysql_obj->getQueryResult($sql);
$statistics = array();
// 统计数据
foreach ($userGroup as $username => $ids) {
if (!array_key_exists($username, $statistics)) {
$statistics[$username] = array(0 => 0, 1 => 0);
}
foreach ($resultList as $row) {
if (in_array($row['fieldId'], $ids)) {
if ($row['hasread'] == 0) {
// 累加未回复留言
$statistics[$username][0] += $row['num'];
} elseif ($row['hasread'] == 1) {
// 累加已读留言
$statistics[$username][1] += $row['num'];
}
}
}
}
foreach ($statistics as $key => $item) {
$total = array_sum($item);
$statistics[$key]['total'] = $total;
}
// print_r($statistics);
$replyedStatistics = array();
// 已回复留言统计
$userIdSql = implode(', ', $name2id);
$sqlre = "select replyer, count(*) as num from msg_alisitemessage where role=1 and replyer in ({$userIdSql}) \n and hasread=2 {$wheresql} group by replyer";
// echo $sqlre;exit;
$replyResult = $mysql_obj->getQueryResult($sqlre);
$replyList = array();
foreach ($replyResult as $reRow) {
$replyList[$reRow['replyer']] = $reRow['num'];
}
$totalNohandle = 0;
// 未处理的留言总数
$totalRead = 0;
// 已读留言总数
$totalReply = 0;
// 已回复留言总数
foreach ($statistics as $r) {
$totalNohandle += $r[0];
$totalRead += $r[1];
$totalReply += $r[2];
}
global $dbConn;
$accountGroup = $this->getAliAccountGroup();
$gropAmount = array();
foreach ($accountGroup as $key => $group) {
$sql_str = implode("', '", $group);
$sql = "select count(1) as num from msg_alisitemessage where receiverid in ('{$sql_str}') {$wheresql}";
$row = $dbConn->fetch_first($sql);
$gropAmount[$key] = $row['num'];
}
// print_r($gropAmount);exit;
$filpNameId = array_flip($name2id);
// print_r($filpNameId);
foreach ($statistics as $user => $rows) {
$userId = isset($name2id[$user]) ? $name2id[$user] : -1;
if (isset($replyList[$userId])) {
$statistics[$user][2] = $replyList[$userId];
} else {
$statistics[$user][2] = 0;
}
}
// print_r($statistics);exit;
$thirdmenue = 2;
}
// exit;
// print_r($data);exit;
$this->smarty->assign('third_menue', $thirdmenue);
$this->smarty->assign('starttime', $starttime);
$this->smarty->assign('endtime', $endtime);
$this->smarty->assign('totalNhandle', $totalNohandle);
$this->smarty->assign('totalread', $totalRead);
$this->smarty->assign('totalreply', $totalReply);
$this->smarty->assign('type', $type);
$this->smarty->assign('toplevel', 3);
// $this->smarty->assign('navlist', $navlist);
$this->smarty->assign('statistics', $statistics);
$this->smarty->assign('statistics_2', $gropAmount);
$this->smarty->assign('sec_menue', 2);
$this->smarty->display('msgstatisticsAliorder.htm');
}
public function view_messageStatistics()
{
$starttime = isset($_GET['starttime']) ? trim($_GET['starttime']) : '';
$endtime = isset($_GET['endtime']) ? trim($_GET['endtime']) : '';
$this->smarty->assign('starttime', $starttime);
$this->smarty->assign('endtime', $endtime);
$wheresql = '';
if (!empty($starttime)) {
$start_stamp = strtotime($starttime);
} else {
$start_stamp = strtotime(date('Y-m-d'));
//没有开始时间就默认为当前日期
}
if (!empty($endtime)) {
$end_stamp = strtotime($endtime);
} else {
$end_stamp = $start_stamp + 86399;
//没有结束时间就默认为开始时间加到这天的最后一秒
}
if ($starttime == $endtime) {
$end_stamp = $start_stamp + 86399;
}
$wheresql .= '(replytime between ' . $start_stamp . ' and ' . $end_stamp . ')';
/*获得文件夹列表*/
$cat_obj = new amazonmessagecategoryModel();
$list = $cat_obj->getAllCategoryInfoList('');
$catgroup = array();
$finalcatgroup = array();
//print_r($list);exit;
/*----- 生成数组形式的客服及其所属分类id -----*/
foreach ($list as $v) {
$catname = $v['category_name'];
$breakar = explode('-', $catname);
if (isset($breakar[1])) {
if (array_key_exists($breakar[1], $catgroup)) {
$catgroup[$breakar[1]][] = $v['id'];
} else {
$catgroup[$breakar[1]] = array($v['id']);
}
}
}
// print_r($catgroup);
$localUser_obj = new GetLoacalUserModel();
$userlist = array();
$userlist = $localUser_obj->getUserInfo(74);
//获得所有的74部门的成员
$name2id = $localUser_obj->getUserId($userlist);
$userlist_tmp = array_flip($name2id);
//print_r($userlist_tmp);
//这是防止创建人不是74部的
foreach ($catgroup as $name => $cid) {
if (in_array($name, $userlist_tmp)) {
$finalcatgroup[$name] = $cid;
}
}
// print_r($finalcatgroup);
$idsql = implode(',', $name2id);
//根据上边的全局id查询某个id回复了的邮件数
$sql_user = "******";
//echo $sql_user;
$query_re = mysql_query($sql_user);
$userReply = array();
//为对应的全局id和回复的邮件数
while ($urow = mysql_fetch_assoc($query_re)) {
$userReply[$urow['replyuser_id']] = $urow['num'];
}
//print_r($userReply);
/*----- 生成统计信息 -----*/
/*----- 以下是先获取所有的邮件回复状况 -----*/
$sql = "select classid, status, count(status) as num from msg_amazonmessage where ( {$wheresql} ) or replytime is null group by classid , status";
// echo $sql;
$result = array();
// result结构：array(分类id=>array(邮件状态值=>邮件数量))
$finalresult = array();
$final = array();
//echo $sql;
$qre = mysql_query($sql);
while ($row = mysql_fetch_assoc($qre)) {
if (array_key_exists($row['classid'], $result)) {
if (!array_key_exists($row['status'], $result[$row['classid']])) {
$result[$row['classid']][$row['status']] = $row['num'];
}
} else {
$result[$row['classid']] = array($row['status'] => $row['num']);
}
}
// print_r($result);
foreach ($finalcatgroup as $name => $cids) {
foreach ($result as $classid => $status) {
if (in_array($classid, $cids)) {
$finalresult[$name][] = $status;
} else {
$finalresult[$name][] = array(0, 0, 0, 0);
}
}
}
//print_r($finalresult);
//这里统计的是某个客服邮件的未回复，回复中，标记回复数
foreach ($finalresult as $name => $status) {
$final[$name] = array(0, 0, 0, 0);
foreach ($status as $state) {
$final[$name][0] += isset($state[0]) ? $state[0] : 0;
$final[$name][1] += isset($state[1]) ? $state[1] : 0;
$final[$name][2] += isset($state[2]) ? $state[2] : 0;
$final[$name][3] += isset($state[3]) ? $state[3] : 0;
}
//名字必须在该部门，其回复的数量才会被记录，不然会被置为0
$final[$name][2] = array_key_exists($name, $userlist) ? $final[$name][2] : 0;
$final[$name][4] = $final[$name][0] + $final[$name][2];
$final[$name]['name'] = $name;
}
//print_r($final);
/*----- 处理排序 -----*/
$noreply = array();
//未回复
$replyed = array();
//已回复
$replying = array();
//回复中
$markreply = array();
//标记回复
$total = array();
//总数
foreach ($final as $name => $item) {
$noreply[$name] = $item[0];
$replyed[$name] = $item[2];
$replying[$name] = $item[1];
$markreply[$name] = $item[3];
$total[$name] = $item[4];
}
//print_r($total);exit;
$total_noreply = array_sum($noreply);
$total_replyed = array_sum($replyed);
$total_replying = array_sum($replying);
$total_markreply = array_sum($markreply);
$total_total = array_sum($total);
$this->smarty->assign('total_noreply', $total_noreply);
$this->smarty->assign('total_replyed', $total_replyed);
$this->smarty->assign('total_markreply', $total_markreply);
$this->smarty->assign('total_replying', $total_replying);
$this->smarty->assign('total_total', $total_total);
$sort_no = isset($_GET['srtn']) ? $_GET['srtn'] == 'asc' ? $sortype = 'asc' : ($sortype = 'desc') : FALSE;
//未回复
$sort_h = isset($_GET['srth']) ? $_GET['srth'] == 'asc' ? $sortype = 'asc' : ($sortype = 'desc') : FALSE;
//已回复
$sort_i = isset($_GET['srti']) ? $_GET['srti'] == 'asc' ? $sortype = 'asc' : ($sortype = 'desc') : FALSE;
//回复中
$sort_m = isset($_GET['srtm']) ? $_GET['srtm'] == 'asc' ? $sortype = 'asc' : ($sortype = 'desc') : FALSE;
//标记回复
$sort_t = isset($_GET['srtt']) ? $_GET['srtt'] == 'asc' ? $sortype = 'asc' : ($sortype = 'desc') : FALSE;
//总数
if ($sort_no !== FALSE) {
//未回复排序
if ($sort_no == 'asc') {
//升序
asort($noreply);
} else {
//降序
arsort($noreply);
}
$this->smarty->assign('condition', 'sort_no');
$this->smarty->assign('sortindex', $noreply);
} elseif ($sort_h !== FALSE) {
//已回复
if ($sort_h == 'asc') {
asort($replyed);
} else {
arsort($replyed);
}
$this->smarty->assign('condition', 'sort_h');
$this->smarty->assign('sortindex', $replyed);
} elseif ($sort_i !== FALSE) {
//回复中
if ($sort_i == 'asc') {
asort($replying);
} else {
arsort($replying);
}
$this->smarty->assign('condition', 'sort_i');
$this->smarty->assign('sortindex', $replying);
} elseif ($sort_m !== FALSE) {
//标记回复
if ($sort_m == 'asc') {
asort($markreply);
} else {
arsort($markreply);
}
$this->smarty->assign('condition', 'sort_m');
$this->smarty->assign('sortindex', $markreply);
} elseif ($sort_t !== FALSE) {
//总数量
if ($sort_t == 'asc') {
asort($total);
} else {
arsort($total);
}
$this->smarty->assign('condition', 'sort_t');
$this->smarty->assign('sortindex', $total);
} else {
//默认按未回复数量降序排列
if ($sort_no == 'asc') {
//升序
asort($noreply);
} else {
//降序
arsort($noreply);
}
$this->smarty->assign('condition', 'sort_no');
$this->smarty->assign('sortindex', $noreply);
}
//print_r($noreply);exit;
$this->smarty->assign('sorttype', $sortype);
/*----- 处理排序 -----*/
$this->smarty->assign('sec_menue', 7);
$navlist = array(array('url' => 'index.php?mod=msgCategoryAmazon&act=categoryList', 'title' => 'Amazon message系统'), array('url' => '', 'title' => 'Amazon message统计'));
$isExport = isset($_GET['export']) ? TRUE : FALSE;
if ($isExport) {
$final[] = array($total_noreply, 0, $total_replyed, 0, $total_total, 'name' => '统计');
$this->exportExcell($starttime, $endtime, $final);
exit;
}
$this->smarty->assign('starttime', date('Y-m-d', $start_stamp));
$this->smarty->assign('endtime', date('Y-m-d', $end_stamp));
$this->smarty->assign('toplevel', 3);
$this->smarty->assign('navlist', $navlist);
$this->smarty->assign('statistics', $final);
$this->smarty->assign('sec_menue', 3);
$this->smarty->display('msgstatisticsAmazon.htm');
}
