/**
* 每周客户情况统计 liuwang 说明： 1、客户数=新认识的客户数+约访后回访的客户数+有效的访客数+转介绍客户数
* 2、分值数=新认识的客户数*1+约访后回访的客户数*10+有效的访客数*10+转介绍客户数*2 3、面谈数=有效访客数+约访后回访的客户数
* 合计中的总数就等于每天的数值之和
*
* @Date 2013-12-11
* @param params
* @return
*/
public List getCustomerStaWeekly(Data params) {
/*
* 营业组 营业员 合计 2013-11-15(星期五) 人次 新增 转介绍 邀约 回访 面谈 新增 转介绍 邀约 回访 面谈 客户数
*/
// 日期
String startDate = params.getString("startDate");
String endDate = params.getString("endDate");
String staffId = params.getString("staffId");
String staffGroupId = params.getString("staffGroupId");
String staffGroupName = params.getString("staffGroupName");
String staffName = params.getString("staffName");
String staffDeptId = params.getString("staffDeptId");// 营业部
String staffDeptName = params.getString("staffDeptName");
DateTool dateTool = new DateTool();
if (startDate == null || "".equals(startDate)) {
startDate = dateTool.getDefaultDates().get(0);
}
if (endDate == null || "".equals(endDate)) {
endDate = dateTool.getDefaultDates().get(1);
}
List<String> dates = dateTool.getDates(startDate, endDate);
StringBuffer sql = new StringBuffer();
sql.append(" select t0.dept_name,t0.group_name, ");
sql.append(" t0.full_name, ");
for (int j = 1; j <= dates.size(); j++) {
String customernum = "customernum" + j;// 客户数
String customerScoresnum = "customeScoresrnum" + j;// 分值数
String facenum = "facenum" + j;// 面谈数
String newnum = "newnum" + j;
String visitnum = "visitnum" + j;
String activenum = "activenum" + j;
String referralsnum = "referralsnum" + j;
String newScorenum = "newScorenum" + j;
String visitScorenum = "visitScorenum" + j;
String activeScorenum = "activeScorenum" + j;
String referralsScorenum = "referralsScorenum" + j;
// 客户数
sql.append("isnull(t" + newnum + "." + newnum + ", 0) +");
sql.append("isnull(t" + visitnum + "." + visitnum + ", 0) +");
sql.append("isnull(t" + activenum + "." + activenum + ", 0) +");
sql.append("isnull(t" + referralsnum + "." + referralsnum + ", 0) ");
sql.append(" as " + customernum + " , ");
// 分值数
sql.append("isnull(t" + newnum + "." + newnum + ", 0) +");
sql.append("isnull(10*t" + visitnum + "." + visitnum + ", 0) +");
sql.append("isnull(10*t" + activenum + "." + activenum + ", 0) +");
sql.append("isnull(2*t" + referralsnum + "." + referralsnum
+ ", 0) ");
sql.append(" as " + customerScoresnum + " , ");
// 面谈数
sql.append("isnull(t" + visitnum + "." + visitnum + ", 0) +");
sql.append("isnull(t" + activenum + "." + activenum + ", 0) ");
sql.append(" as " + facenum + " , ");
sql.append("t" + newnum + "." + newnum + ",");
sql.append(" t" + newnum + "." + newnum + " as " + newScorenum
+ " ,");
sql.append("t" + visitnum + "." + visitnum + ",");
sql.append("10*t" + visitnum + "." + visitnum + " as "
+ visitScorenum + " ,");
sql.append("t" + activenum + "." + activenum + ",");
sql.append("10*t" + activenum + "." + activenum + " as "
+ activeScorenum + " ,");
sql.append("t" + referralsnum + "." + referralsnum + ",");
sql.append("2*t" + referralsnum + "." + referralsnum + " as "
+ referralsScorenum + " ,");
}
sql = new StringBuffer(sql.substring(0, sql.length() - 1));
sql.append(" from (select t00.id as id, t01.id as group_id , t02.id as dept_id,t02.name as dept_name, ");
sql.append(" t01.name as group_name, ");
sql.append(" t00.name as full_name ");
sql.append(" from (select id, name, sales_group_id,sales_dept_id from org_staff where is_active = 1 and staff_type = 1 ");
sql.append(" ) t00 ");
sql.append(" left join (select id, name from org_staff where 1=1 ");
sql.append(") t01 ");
sql.append(" on t00.sales_group_id = t01.id ");
sql.append(" left join (select id, name from org_staff where 1 = 1) t02 ");
sql.append(" on t00.sales_dept_id = t02.id ) t0 ");
int i = 1;
for (String date : dates) {
String newnum = "newnum" + i;
String visitnum = "visitnum" + i;
String activenum = "activenum" + i;
String referralsnum = "referralsnum" + i;
// // 新认识的客户数+约访后回访的客户数+有效的访客数+转介绍客户数 Scores
sql.append(" left join (select staff_id, count(*) as " + newnum
+ " ");
sql.append(" from crm_situation_survey ");
sql.append(" where register_date = '" + date
+ "' ");
sql.append(" and nature like '%新认识的客户%' ");
sql.append(" group by staff_id) t" + newnum
+ " ");
sql.append(" on t0.id = t"
+ newnum
+ ".staff_id ");
sql.append(" left join (select staff_id, count(*) as " + visitnum
+ " ");
sql.append(" from crm_situation_survey ");
sql.append(" where register_date = '" + date
+ "' ");
sql.append(" and nature like '%约访后回访的客户%' ");
sql.append(" group by staff_id) t" + visitnum
+ " ");
sql.append(" on t0.id = t"
+ visitnum
+ ".staff_id ");
sql.append(" left join (select staff_id, count(*) as "
+ activenum + " ");
sql.append(" from crm_situation_survey ");
sql.append(" where register_date = '" + date
+ "' ");
sql.append(" and nature like '%有效的访客%' ");
sql.append(" group by staff_id) t" + activenum
+ " ");
sql.append(" on t0.id = t"
+ activenum
+ ".staff_id ");
sql.append(" left join (select staff_id, count(*) as "
+ referralsnum + " ");
sql.append(" from crm_situation_survey ");
sql.append(" where register_date = '" + date
+ "' ");
sql.append(" and nature like '%转介绍的客户%' ");
sql.append(" group by staff_id) t" + referralsnum
+ " ");
sql.append(" on t0.id = t"
+ referralsnum
+ ".staff_id ");
i++;
}
sql.append(" where 1=1 ");
// 如果name有值，id为空；那么按name取like
// 如果name为空，不加判断
// 如果都有；按id。
boolean nameB = false;
boolean idB = false;
if (null != staffName && !"".equals(staffName)
&& !"undefined".equals(staffName)) {
nameB = true;
}
if (null != staffId && !"".equals(staffId)
&& !"undefined".equals(staffId)) {
idB = true;
}
if (nameB & idB) {
sql.append(" and t0.id in(" + staffId
+ ") ");// 营销员的范围限定
} else if (nameB == true && idB == false) {
if (staffName.indexOf(',') != -1) {// 如果含有逗号包含多个name
staffName = "'" + staffName + "'";
staffName = staffName.replaceAll(",", "','");
sql.append(" and t0.full_name in(" + staffName
+ ") ");
} else {
sql.append(" and t0.full_name like('%"
+ staffName
+ "%') ");
}
}
// ///营业组范围控制
// 如果name有值，id为空；那么按name取like
// 如果name为空，不加判断
// 如果都有；按id。
boolean nameGroupB = false;
boolean idGroupB = false;
if (null != staffGroupName && !"".equals(staffGroupName)
&& !"undefined".equals(staffGroupName)) {
nameGroupB = true;
}
if (null != staffGroupId && !"".equals(staffGroupId)
&& !"undefined".equals(staffGroupId)) {
idGroupB = true;
}
if (nameGroupB & idGroupB) {
sql.append(" and t0.group_id in(" + staffGroupId
+ ") ");// 营销员的范围限定
} else if (nameGroupB == true && idGroupB == false) {
if (staffGroupName.indexOf(',') != -1) {// 如果含有逗号包含多个name
staffGroupName = "'" + staffGroupName + "'";
staffGroupName = staffGroupName.replaceAll(",", "','");
sql.append(" and t0.group_name in(" + staffGroupName
+ ") ");
} else {
sql.append(" and t0.group_name like('%"
+ staffGroupName
+ "%') ");
}
}
// ///营业部范围控制
// 如果name有值，id为空；那么按name取like
// 如果name为空，不加判断
// 如果都有；按id。
boolean nameDeptB = false;
boolean idDeptB = false;
if (null != staffDeptName && !"".equals(staffDeptName)
&& !"undefined".equals(staffDeptName)) {
nameDeptB = true;
}
if (null != staffDeptId && !"".equals(staffDeptId)
&& !"undefined".equals(staffDeptId)) {
idDeptB = true;
}
if (nameDeptB & idDeptB) {
sql.append(" and t0.dept_id in(" + staffDeptId
+ ") ");// 营销员的范围限定
} else if (nameDeptB == true && idDeptB == false) {
if (staffDeptName.indexOf(',') != -1) {// 如果含有逗号包含多个name
staffDeptName = "'" + staffDeptName + "'";
staffDeptName = staffDeptName.replaceAll(",", "','");
sql.append(" and t0.dept_name in(" + staffDeptName
+ ") ");
} else {
sql.append(" and t0.dept_name like('%"
+ staffDeptName
+ "%') ");
}
}
// 基于每日的查询结果算出时间段内的合计
StringBuffer reusltRow = new StringBuffer();
reusltRow
.append(" select t.dept_name,t.group_name, t.full_name, ");
String customernumTotal = "";
String customerScoresnumTotal = "";
String facenumTotal = "";
for (int j = 1; j <= dates.size(); j++) {
customernumTotal = customernumTotal + "customernum" + j + "+";// 客户数
customerScoresnumTotal = customerScoresnumTotal
+ "customeScoresrnum" + j + "+";// 分值数
facenumTotal = facenumTotal + "facenum" + j + "+";// 面谈数
}
reusltRow
.append(" "
+ customernumTotal.substring(0,
customernumTotal.length() - 1)
+ " as customernumTotal , "
+ customerScoresnumTotal.substring(0,
customerScoresnumTotal.length() - 1) + " as customerScoresnumTotal ,"
+ facenumTotal.substring(0, facenumTotal.length() - 1)
+ " as facenumTotal , ");
for (int j = 1; j <= dates.size(); j++) {
String customernum = "customernum" + j;// 客户数
String customerScoresnum = "customeScoresrnum" + j;// 分值数
String facenum = "facenum" + j;// 面谈数
String newnum = "newnum" + j;
String visitnum = "visitnum" + j;
String activenum = "activenum" + j;
String referralsnum = "referralsnum" + j;
String newScorenum = "newScorenum" + j;
String visitScorenum = "visitScorenum" + j;
String activeScorenum = "activeScorenum" + j;
String referralsScorenum = "referralsScorenum" + j;
reusltRow.append(" " + customernum + ", " + customerScoresnum + ","
+ facenum + "," + newnum+"," +newScorenum +"," +visitnum+"," + visitScorenum+"," +activenum +","
+ activeScorenum+"," +referralsnum +"," +referralsScorenum +",");
}
reusltRow=new StringBuffer(reusltRow.substring(0, reusltRow.length()-1));
reusltRow.append(" from (");
reusltRow.append(sql);
reusltRow.append(")t");
List<Object[]> result = super.findBySql(reusltRow.toString());
return result;
}
/**
* 获取团队长的名字和id
*
* @return
* @author liuwang
* @date 2013-11-29
*/
public List<Object[]> getTeamLeader(String name) {
String sql = "select name,id,code from org_staff where is_active=1 and is_team=1";
if (null != name && !"".equals(name)) {
sql = sql + " and name like '%" + name.trim() + "%'";
}
List<Object[]> result = super.findBySql(sql);
return result;
}
/**
* 获取组长的名字和id
*
* @return
* @author liuwang
* @date 2013-11-29
*/
public List<Object[]> getGroupLeader(String name, String teamName) {
String sql = "select name,id,code from org_staff where is_active=1 and is_group=1";
if (null != teamName && !"".equals(teamName) && !",".equals(teamName)) {
if (teamName.indexOf(',') != -1) {// 如果含有逗号包含多个name
teamName = "'" + teamName + "'";
teamName = teamName.replaceAll(",", "','");
sql = sql
+ " and sales_dept_id in (select t2.id from org_staff t2 where t2.is_active=1 and t2.is_team=1 and t2.name in("
+ teamName + ") )";
} else {
sql = sql
+ " and sales_dept_id in (select t2.id from org_staff t2 where t2.is_active=1 and t2.is_team=1 and t2.name like '%"
+ teamName.trim() + "%' )";
}
}
if (null != name && !"".equals(name)) {
sql = sql + " and name like '%" + name.trim() + "%'";
}
List<Object[]> result = super.findBySql(sql);
return result;
}
/**
* 获取营业员的名字和id
*
* @return
* @author liuwang
* @date 2013-11-29
*/
public List<Object[]> getStaff(String name, String teamName,
String groupName) {
String sql = "select name,id,code from org_staff where is_active=1 and staff_type=1 ";
if (null != groupName && !"".equals(groupName)
&& !",".equals(groupName)) {
if (groupName.indexOf(',') != -1) {// 如果含有逗号包含多个name
groupName = "'" + groupName + "'";
groupName = groupName.replaceAll(",", "','");
sql = sql
+ " and sales_group_id in (select t.id from org_staff t where t.is_active=1 and t.is_group=1 and t.name in("
+ groupName + ") )";
} else {
sql = sql
+ " and sales_group_id in (select t.id from org_staff t where t.is_active=1 and t.is_group=1 and t.name like '%"
+ groupName.trim() + "%' )";
}
}
if (null != teamName && !"".equals(teamName) && !",".equals(teamName)) {
if (teamName.indexOf(',') != -1) {// 如果含有逗号包含多个name
teamName = "'" + teamName + "'";
teamName = teamName.replaceAll(",", "','");
sql = sql
+ " and sales_dept_id in (select t2.id from org_staff t2 where t2.is_active=1 and t2.is_team=1 and t2.name in("
+ teamName + ") )";
} else {
sql = sql
+ " and sales_dept_id in (select t2.id from org_staff t2 where t2.is_active=1 and t2.is_team=1 and t2.name like '%"
+ teamName.trim() + "%' )";
}
}
if (null != name && !"".equals(name)) {
sql = sql + " and name like '%" + name.trim() + "%'";
}
List<Object[]> result = super.findBySql(sql);
return result;
}
扫一扫
2462
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
