337 lines
9.9 KiB
JavaScript
337 lines
9.9 KiB
JavaScript
const dayjs = require("dayjs");
|
||
|
||
let db = null;
|
||
|
||
exports.main = async (content, DB) => {
|
||
db = DB;
|
||
switch (content.type) {
|
||
case "getConsultantStatistics":
|
||
return await getConsultantStatistics(content);
|
||
case "getConsultantSalesRanking":
|
||
return await getConsultantSalesRanking(content);
|
||
case "getDoctorPerformance":
|
||
return await getDoctorPerformance(content);
|
||
case "getBillRecordsWithMemberDetails":
|
||
return await getBillRecordsWithMemberDetails(content);
|
||
}
|
||
};
|
||
|
||
async function getConsultantStatistics(content) {
|
||
try {
|
||
const { corpId, startDate, endDate, consultantFilter, projectIds } =
|
||
content;
|
||
let matchStage = {
|
||
corpId,
|
||
counselorUserId: { $exists: true, $ne: null },
|
||
};
|
||
|
||
if (Array.isArray(consultantFilter) && consultantFilter.length) {
|
||
matchStage.counselorUserId = { $in: consultantFilter };
|
||
}
|
||
if (Array.isArray(projectIds) && projectIds.length) {
|
||
matchStage["projects._id"] = { $in: projectIds };
|
||
}
|
||
// 添加日期筛选条件
|
||
if (startDate || endDate) {
|
||
matchStage.createTime = {};
|
||
if (startDate)
|
||
matchStage.createTime.$gte = dayjs(startDate).startOf("day").valueOf();
|
||
if (endDate)
|
||
matchStage.createTime.$lte = dayjs(endDate).endOf("day").valueOf();
|
||
}
|
||
const records = await db
|
||
.collection("consult-record")
|
||
.aggregate([
|
||
{ $match: matchStage },
|
||
{
|
||
$group: {
|
||
_id: "$counselorUserId",
|
||
consultCount: { $sum: 1 },
|
||
successCount: {
|
||
$sum: { $cond: [{ $eq: ["$visitStatus", "visited"] }, 1, 0] },
|
||
},
|
||
successAmount: {
|
||
$sum: {
|
||
$cond: [
|
||
{ $eq: ["$visitStatus", "visited"] },
|
||
"$tradeAmount",
|
||
0,
|
||
],
|
||
},
|
||
},
|
||
netAmount: {
|
||
$sum: {
|
||
$subtract: [
|
||
{
|
||
$cond: [
|
||
{ $eq: ["$visitStatus", "visited"] },
|
||
"$tradeAmount",
|
||
0,
|
||
],
|
||
},
|
||
"$consumeCount",
|
||
],
|
||
},
|
||
},
|
||
firstVisitCount: {
|
||
$sum: { $cond: [{ $eq: ["$consultStage", "firstVisit"] }, 1, 0] },
|
||
},
|
||
returnVisitCount: {
|
||
$sum: {
|
||
$cond: [{ $eq: ["$consultStage", "returnVisit"] }, 1, 0],
|
||
},
|
||
},
|
||
moreConsumedCount: {
|
||
$sum: {
|
||
$cond: [{ $eq: ["$consultStage", "moreConsumed"] }, 1, 0],
|
||
},
|
||
},
|
||
},
|
||
},
|
||
{
|
||
$project: {
|
||
_id: 0,
|
||
consultant: "$_id",
|
||
consultCount: 1,
|
||
successCount: 1,
|
||
successAmount: 1,
|
||
netAmount: 1,
|
||
firstVisitCount: 1,
|
||
returnVisitCount: 1,
|
||
moreConsumedCount: 1,
|
||
avgOrderValue: {
|
||
$cond: [
|
||
{ $eq: ["$successCount", 0] },
|
||
0,
|
||
{ $divide: ["$successAmount", "$successCount"] },
|
||
],
|
||
},
|
||
},
|
||
},
|
||
{ $sort: { successAmount: -1 } },
|
||
])
|
||
.toArray();
|
||
|
||
return { success: true, data: records, message: "查询成功" };
|
||
} catch (error) {
|
||
return { success: false, message: error.message || "查询失败" };
|
||
}
|
||
}
|
||
|
||
async function getConsultantSalesRanking(content) {
|
||
try {
|
||
const { corpId, startDate, endDate, consultantFilter, projectIds } =
|
||
content;
|
||
let matchStage = {
|
||
corpId,
|
||
counselorUserId: { $exists: true, $ne: null },
|
||
};
|
||
|
||
if (Array.isArray(consultantFilter) && consultantFilter.length) {
|
||
matchStage.counselorUserId = { $in: consultantFilter };
|
||
}
|
||
if (Array.isArray(projectIds) && projectIds.length) {
|
||
matchStage["projects._id"] = { $in: projectIds };
|
||
}
|
||
// 添加日期筛选条件
|
||
if (startDate || endDate) {
|
||
matchStage.createTime = {};
|
||
if (startDate)
|
||
matchStage.createTime.$gte = dayjs(startDate).startOf("day").valueOf();
|
||
if (endDate)
|
||
matchStage.createTime.$lte = dayjs(endDate).endOf("day").valueOf();
|
||
}
|
||
const records = await db
|
||
.collection("consult-record")
|
||
.aggregate([
|
||
{ $match: matchStage },
|
||
{
|
||
$group: {
|
||
_id: "$counselorUserId",
|
||
consultCount: { $sum: 1 },
|
||
successCount: {
|
||
$sum: { $cond: [{ $eq: ["$visitStatus", "visited"] }, 1, 0] },
|
||
},
|
||
successAmount: {
|
||
$sum: {
|
||
$cond: [
|
||
{ $eq: ["$visitStatus", "visited"] },
|
||
"$tradeAmount",
|
||
0,
|
||
],
|
||
},
|
||
},
|
||
netAmount: {
|
||
$sum: {
|
||
$subtract: [
|
||
{
|
||
$cond: [
|
||
{ $eq: ["$visitStatus", "visited"] },
|
||
"$tradeAmount",
|
||
0,
|
||
],
|
||
},
|
||
"$consumeCount",
|
||
],
|
||
},
|
||
},
|
||
},
|
||
},
|
||
{
|
||
$project: {
|
||
_id: 0,
|
||
consultant: "$_id",
|
||
consultCount: 1,
|
||
successCount: 1,
|
||
successAmount: 1,
|
||
netAmount: 1,
|
||
successRate: {
|
||
$cond: [
|
||
{ $eq: ["$consultCount", 0] },
|
||
0,
|
||
{ $divide: ["$successCount", "$consultCount"] },
|
||
],
|
||
},
|
||
avgOrderValue: {
|
||
$cond: [
|
||
{ $eq: ["$successCount", 0] },
|
||
0,
|
||
{ $divide: ["$successAmount", "$successCount"] },
|
||
],
|
||
},
|
||
},
|
||
},
|
||
{ $sort: { successAmount: -1 } },
|
||
])
|
||
.toArray();
|
||
|
||
return { success: true, data: records, message: "查询成功" };
|
||
} catch (error) {
|
||
return { success: false, message: error.message || "查询失败" };
|
||
}
|
||
}
|
||
async function getDoctorPerformance(content) {
|
||
try {
|
||
const { corpId, startDate, endDate, projectIds, receptionDoctorUserId } =
|
||
content;
|
||
let matchStage = {
|
||
corpId,
|
||
receptionDoctorUserId: { $exists: true, $ne: null },
|
||
};
|
||
|
||
if (Array.isArray(receptionDoctorUserId) && receptionDoctorUserId.length) {
|
||
matchStage.receptionDoctorUserId = { $in: receptionDoctorUserId };
|
||
}
|
||
if (Array.isArray(projectIds) && projectIds.length) {
|
||
matchStage["projects._id"] = { $in: projectIds };
|
||
}
|
||
// 添加日期筛选条件
|
||
if (startDate || endDate) {
|
||
matchStage.createTime = {};
|
||
if (startDate) {
|
||
matchStage.createTime.$gte = dayjs(startDate).startOf("day").valueOf();
|
||
}
|
||
if (endDate) {
|
||
matchStage.createTime.$lte = dayjs(endDate).endOf("day").valueOf();
|
||
}
|
||
}
|
||
const records = await db
|
||
.collection("consult-record")
|
||
.aggregate([
|
||
{ $match: matchStage },
|
||
{
|
||
$group: {
|
||
_id: "$receptionDoctorUserId", // 按照接诊医生ID分组
|
||
consultCount: { $sum: 1 },
|
||
successCount: {
|
||
$sum: { $cond: [{ $eq: ["$visitStatus", "visited"] }, 1, 0] },
|
||
},
|
||
successAmount: {
|
||
$sum: {
|
||
$cond: [
|
||
{ $eq: ["$visitStatus", "visited"] },
|
||
"$tradeAmount",
|
||
0,
|
||
],
|
||
},
|
||
},
|
||
consumeCount: { $sum: "$consumeCount" }, // 使用consumeCount字段作为消费次数
|
||
netAmount: {
|
||
$sum: {
|
||
$subtract: [
|
||
{
|
||
$cond: [
|
||
{ $eq: ["$visitStatus", "visited"] },
|
||
"$tradeAmount",
|
||
0,
|
||
],
|
||
},
|
||
"$consumeCount", // 从交易金额中减去消费次数
|
||
],
|
||
},
|
||
},
|
||
firstVisitCount: {
|
||
$sum: { $cond: [{ $eq: ["$consultStage", "firstVisit"] }, 1, 0] },
|
||
},
|
||
returnVisitCount: {
|
||
$sum: {
|
||
$cond: [{ $eq: ["$consultStage", "returnVisit"] }, 1, 0],
|
||
},
|
||
},
|
||
moreConsumedCount: {
|
||
$sum: {
|
||
$cond: [{ $eq: ["$consultStage", "moreConsumed"] }, 1, 0],
|
||
},
|
||
},
|
||
},
|
||
},
|
||
{
|
||
$project: {
|
||
_id: 0,
|
||
receptionDoctorUserId: "$_id",
|
||
consultCount: 1,
|
||
successCount: 1,
|
||
successAmount: 1,
|
||
consumeCount: 1, // 输出消费次数而非退款金额
|
||
netAmount: 1,
|
||
firstVisitCount: 1,
|
||
returnVisitCount: 1,
|
||
moreConsumedCount: 1,
|
||
},
|
||
},
|
||
{ $sort: { successAmount: -1 } },
|
||
])
|
||
.toArray();
|
||
|
||
return { success: true, data: records, message: "查询成功" };
|
||
} catch (error) {
|
||
return { success: false, message: error.message || "查询失败" };
|
||
}
|
||
}
|
||
|
||
// 新函数：从bill-record表查询并关联member表信息和consult-record表信息
|
||
async function getBillRecordsWithMemberDetails(content) {
|
||
try {
|
||
const { payDate, corpId } = content;
|
||
|
||
// 构建bill-record表的匹配条件
|
||
const billMatch = {
|
||
corpId,
|
||
};
|
||
// 设置日期范围
|
||
if (payDate && Array.isArray(payDate) && payDate.length === 2) {
|
||
billMatch.createTime = {
|
||
$gte: dayjs(payDate[0]).startOf("day").valueOf(),
|
||
$lte: dayjs(payDate[1]).endOf("day").valueOf(),
|
||
};
|
||
}
|
||
const records = await db
|
||
.collection("bill-record")
|
||
.find(billMatch)
|
||
.toArray();
|
||
return { success: true, data: records, message: "查询成功" };
|
||
} catch (error) {
|
||
return { success: false, message: error.message || "查询失败" };
|
||
}
|
||
}
|