<?php
require_once 'conn.php';
checkLogin();
$datestart = $_POST['start'] ?? '';
$dateend = $_POST['end'] ?? '';
$currentMonth = date('Y-m-1 00:00:00');
if (empty($datestart) || !strtotime($datestart)) {
$datestart = $currentMonth;
} else {
$datestart = date('Y-m-d 00:00:00', strtotime($datestart));
}
if (empty($dateend) || !strtotime($dateend)) {
$dateend = date('Y-m-d H:i:s');
} else {
$dateend = date('Y-m-d 23:59:59', strtotime($dateend));
}
?>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>管理区域</title>
<link rel="stylesheet" href="css/common.css" type="text/css" />
<link rel="stylesheet" href="css/bootstrap-datepicker3.min.css" type="text/css" />
<script src="js/jquery-1.7.2.min.js"></script>
<script src="js/js.js"></script>
<script src="js/bootstrap-datepicker.min.js"></script>
<script src="js/bootstrap-datepicker.zh-CN.min.js"></script>
<script>
$(function(){
$('#sandbox-container .input-daterange').datepicker({
maxViewMode: 2,
language: "zh-CN"
});
});
</script>
</head>
<body class="clear">
<?php require_once 'panel.php'; ?>
<div id="man_zone">
<div class="dashboard">
<h1 class="dashboardHead">各组数据</h1>
<form method="post" action="team.php">
<div class="sandbox-containe" id="sandbox-container">
<div class="input-daterange input-group" id="datepicker">
<span class="input-group-addon">日期筛选</span>
<input type="text" class="input-sm form-control" autocomplete="off" value="<?= $datestart ?>" name="start">
<span class="input-group-addon">至</span>
<input type="text" class="input-sm form-control" autocomplete="off" value="<?= $dateend ?>" name="end">
</div>
<input type="submit" class="submit" value="筛选">
</div>
</form>
<table class="teamStatistics" border="1px" bordercollapse="collapse" cellspacing="0" cellpadding="5" bordercolor="#DDD">
<tr>
<td>组长</td>
<td>组员</td>
<td>总客户数量</td>
<td width="25%">本月成交</td>
<td>进公海客户总数</td>
<td>公海认领</td>
</tr>
<?php
$result = $conn->query("SELECT id, em_user FROM employee WHERE em_role=0 AND id<>13");
while ($row = $result->fetch_assoc()) {
$clstr = "";
// Get total customer count
$c1Result = $conn->query("SELECT COUNT(DISTINCT cs_code) as c1 FROM customer WHERE cs_belong=" . $row['id']);
$c1Row = $c1Result->fetch_assoc();
$c1 = $c1Row['c1'];
// Get customers going to public sea
$c2Result = $conn->query("SELECT COUNT(id) as c2 FROM customer
WHERE cs_deal<>3 AND cs_deal<>0 AND cs_type<>2
AND DATEDIFF(NOW(), cs_updatetime) > 90
AND cs_belongclient=0 AND cs_belong=" . $row['id']);
$c2Row = $c2Result->fetch_assoc();
$c2 = $c2Row['c2'];
// Get deals in date range
$c3Result = $conn->query("SELECT DISTINCT cs_code FROM customer
WHERE cs_dealdate > '" . $conn->real_escape_string($datestart) . "'
AND cs_dealdate <= '" . $conn->real_escape_string($dateend) . "'
AND cs_deal=3 AND cs_belong=" . $row['id']);
$c3 = $c3Result->num_rows;
while ($c3Row = $c3Result->fetch_assoc()) {
$clstr .= $c3Row['cs_code'] . "<br>";
}
// Get claimed customers count
$c4Result = $conn->query("SELECT COUNT(DISTINCT cs_code) as c4 FROM customer
WHERE cs_claimdate > '" . $conn->real_escape_string($datestart) . "'
AND cs_claimdate <= '" . $conn->real_escape_string($dateend) . "'
AND cs_belong=" . $row['id']);
$c4Row = $c4Result->fetch_assoc();
$c4 = $c4Row['c4'];
// Get team member count
$c5Result = $conn->query("SELECT COUNT(id) as c5 FROM employee WHERE em_role=" . $row['id']);
$c5Row = $c5Result->fetch_assoc();
$c5 = $c5Row['c5'];
?>
<tr>
<td rowspan="<?= 1 + $c5 ?>"><?= htmlspecialchars($row['em_user']) ?></td>
<td><?= htmlspecialchars($row['em_user']) ?></td>
<td><?= $c1 ?></td>
<td><?= $clstr ?></td>
<td><?= $c2 ?></td>
<td><?= $c4 ?></td>
</tr>
<?php
// Get team members data
$memberResult = $conn->query("SELECT id, em_user FROM employee WHERE em_role=" . $row['id']);
while ($memberRow = $memberResult->fetch_assoc()) {
$clstr = "";
// Get member's total customer count
$mc1Result = $conn->query("SELECT COUNT(DISTINCT cs_code) as c1 FROM customer WHERE cs_belong=" . $memberRow['id']);
$mc1Row = $mc1Result->fetch_assoc();
$mc1 = $mc1Row['c1'];
// Get member's customers going to public sea
$mc2Result = $conn->query("SELECT COUNT(DISTINCT cs_code) as c2 FROM customer
WHERE cs_deal<>3 AND cs_deal<>0 AND cs_type<>2
AND DATEDIFF(NOW(), cs_updatetime) > 90
AND cs_belongclient=0 AND cs_belong=" . $memberRow['id']);
$mc2Row = $mc2Result->fetch_assoc();
$mc2 = $mc2Row['c2'];
// Get member's deals in date range
$mc3Result = $conn->query("SELECT DISTINCT cs_code FROM customer
WHERE cs_dealdate > '" . $conn->real_escape_string($datestart) . "'
AND cs_dealdate <= '" . $conn->real_escape_string($dateend) . "'
AND cs_deal=3 AND cs_belong=" . $memberRow['id']);
$mc3 = $mc3Result->num_rows;
while ($mc3Row = $mc3Result->fetch_assoc()) {
$clstr .= $mc3Row['cs_code'] . "<br>";
}
// Get member's claimed customers count
$mc4Result = $conn->query("SELECT COUNT(id) as c4 FROM customer
WHERE cs_claimdate > '" . $conn->real_escape_string($datestart) . "'
AND cs_claimdate <= '" . $conn->real_escape_string($dateend) . "'
AND cs_belong=" . $memberRow['id']);
$mc4Row = $mc4Result->fetch_assoc();
$mc4 = $mc4Row['c4'];
?>
<tr>
<td><?= htmlspecialchars($memberRow['em_user']) ?></td>
<td><?= $mc1 ?></td>
<td><?= $clstr ?></td>
<td><?= $mc2 ?></td>
<td><?= $mc4 ?></td>
</tr>
<?php
}
}
?>
</table>
</div>
</div>
</body>
</html>