package com.product.contract.service;
|
|
import com.product.admin.service.OrganizationCacheService;
|
import com.product.contract.config.CmnConst;
|
import com.product.core.config.CoreConst;
|
import com.product.core.dao.BaseDao;
|
import com.product.core.entity.DataTableEntity;
|
import com.product.core.entity.FieldSetEntity;
|
import com.product.core.service.support.AbstractBaseService;
|
import com.product.core.service.support.QueryFilterService;
|
import com.product.core.spring.context.SpringMVCContextHolder;
|
import com.product.util.BaseUtil;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.stereotype.Service;
|
|
@Service
|
public class ProjectCommissionService extends AbstractBaseService{
|
|
@Autowired
|
BaseDao baseDao;
|
|
@Autowired
|
QueryFilterService queryFilterService;
|
|
/**
|
* 项目提成列表
|
* @param fse
|
* @return
|
*/
|
public DataTableEntity listCommission(FieldSetEntity fse) {
|
String filter = fse.getString("filter");
|
String queryFilter = queryFilterService.getQueryFilter(fse);
|
StringBuilder sb = new StringBuilder();
|
sb.append("SELECT * FROM( \n");
|
sb.append("SELECT A.*,B.project_name AS project_name_label,E.user_name AS apply_label \n");
|
sb.append("FROM product_project_performance_commission A \n");
|
sb.append("LEFT JOIN product_project_business B ON B.uuid = A.project_uuid \n");
|
sb.append("LEFT JOIN product_sys_users E ON E.user_id = A.apply_user \n");
|
sb.append("WHERE apply_user = ? \n");
|
sb.append(" AND ").append(filter).append(" \n");
|
sb.append(")A \n");
|
if (!BaseUtil.strIsNull(queryFilter)) {
|
sb.append("WHERE ").append(queryFilter).append(" \n");
|
}
|
sb.append("ORDER BY apply_time DESC");
|
return baseDao.listTable(sb.toString(), new Object[] {SpringMVCContextHolder.getCurrentUserId()}, fse.getInteger(CoreConst.PAGESIZE), fse.getInteger(CoreConst.CPAGE));
|
}
|
|
/**
|
* 项目提成详情
|
* @param uuid
|
* @return
|
*/
|
public FieldSetEntity findCommission(String uuid) {
|
return baseDao.getFieldSetEntity(CmnConst.PRODUCT_PROJECT_PERFORMANCE_COMMISSION, uuid, true);
|
}
|
|
/**
|
* 合同回款业绩相关数据带出
|
* @return
|
*/
|
public FieldSetEntity getUnCommissionProject(String projectUUID){
|
String sql ="SELECT\n" +
|
"a.uuid contract_uuid,\n" + //合同名称
|
"a.project_uuid,\n" + //合同名称
|
"a.contract_num,\n" + //合同编号
|
"a.collection_amount receivable_amount,\n" + //累计回款
|
"(a.collection_amount-IF(b.this_settlement IS NULL, 0, b.this_settlement)) this_settlement\n" + //#本次结算回款额
|
"\n" +
|
"FROM product_project_contract_info a\n" +
|
"LEFT JOIN \n" +
|
"(SELECT SUM(this_settlement) this_settlement,contract_uuid,project_uuid FROM product_project_performance_commission GROUP BY contract_uuid,project_uuid) b\n" +
|
"on a.uuid=b.contract_uuid and a.project_uuid=b.project_uuid\n" +
|
"where a.project_uuid=?";
|
FieldSetEntity fs = baseDao.getFieldSetEntityBySQL(sql, new String[]{projectUUID}, false);
|
fs.setTableName(CmnConst.PRODUCT_PROJECT_PERFORMANCE_COMMISSION);
|
DataTableEntity dt = getUnCommissionProjectSub(projectUUID);
|
fs.addSubDataTable(dt);
|
return fs;
|
}
|
/**
|
* 合同回款业绩相关子表字段带出
|
* @return
|
*/
|
private DataTableEntity getUnCommissionProjectSub(String projectUUID) {
|
StringBuilder sb = new StringBuilder();
|
sb.append(" SELECT '项目组长' project_role_name,'1' AS project_role, A.group_leader AS project_user,'0.01' bonus_ratio,b.workload actual_human_days,ROUND(b.workload/C.work_days, 2) human_days_ratio ");
|
sb.append(" FROM product_project_business A ");
|
sb.append(" LEFT JOIN (SELECT project_uuid,sum(workload)workload FROM product_project_schedule_weekly_sub GROUP BY project_uuid) B on A.uuid=b.project_uuid ");
|
sb.append(" LEFT JOIN (SELECT project_uuid,sum(work_days)work_days FROM product_project_business_plan GROUP BY project_uuid) C on A.uuid=C.project_uuid ");
|
sb.append(" WHERE A.uuid = ? ");
|
sb.append(" UNION ALL ");
|
sb.append(" SELECT '项目成员' project_role_name,'2' AS project_role, A.team_members AS project_user,'0.05' bonus_ratio,B.workload,ROUND(b.workload/C.work_days, 2) human_days_ratio ");
|
sb.append(" FROM product_project_business A LEFT JOIN");
|
sb.append(" (SELECT a.apply_user,b.project_uuid,sum(workload) workload FROM product_project_schedule_weekly a LEFT JOIN product_project_schedule_weekly_sub b on b.main_uuid = a.uuid GROUP BY a.apply_user,b.project_uuid) B");
|
sb.append(" on A.team_members=b.apply_user and A.uuid =B.project_uuid LEFT JOIN");
|
sb.append(" (SELECT project_uuid,project_head,sum(work_days) work_days FROM product_project_business_plan GROUP BY project_uuid,project_head) C ");
|
sb.append(" on A.team_members=C.project_head and A.uuid =C.project_uuid ");
|
sb.append(" WHERE A.uuid = ? ");
|
sb.append(" UNION ALL ");
|
sb.append(" SELECT '销售人员' project_role_name,'3' AS project_role, A.sale AS project_user,'0' bonus_ratio,null actual_human_days,1 human_days_ratio ");
|
sb.append(" FROM product_project_business A ");
|
sb.append(" WHERE A.uuid = ? ");
|
DataTableEntity dt = baseDao.listTable(sb.toString(), new Object[] {projectUUID, projectUUID,projectUUID});
|
if (!BaseUtil.dataTableIsEmpty(dt)) {
|
DataTableEntity dtNew = new DataTableEntity();
|
|
for (int i = 0; i < dt.getRows(); i++) {
|
FieldSetEntity fse = dt.getFieldSetEntity(i);
|
fse.setTableName(CmnConst.PRODUCT_PROJECT_PERFORMANCE_COMMISSION_SUB);
|
String projectRole = fse.getString("project_role");
|
String projectUser = fse.getString("project_user");
|
if (BaseUtil.strIsNull(projectRole) || BaseUtil.strIsNull(projectUser)) {
|
continue;
|
}
|
if ("2".equals(projectRole)) {
|
if (projectUser.contains(",")) {
|
String [] userArray = projectUser.split(",");
|
for (int j = 0; j < userArray.length; j++) {
|
FieldSetEntity fseNew = fse.clones();
|
FieldSetEntity fseStaff = OrganizationCacheService.getStaffCacheByUserIdStatic(userArray[j]);
|
fseNew.setValue("project_user_name", fseStaff.getString("show_name"));
|
fseNew.setValue("project_user", userArray[j]);
|
dtNew.addFieldSetEntity(fseNew);
|
}
|
}else {
|
FieldSetEntity fseStaff = OrganizationCacheService.getStaffCacheByUserIdStatic(projectUser);
|
fse.setValue("project_user_name", fseStaff.getString("show_name"));
|
dtNew.addFieldSetEntity(fse);
|
}
|
}else {
|
FieldSetEntity fseStaff = OrganizationCacheService.getStaffCacheByUserIdStatic(projectUser);
|
fse.setValue("project_user_name", fseStaff.getString("show_name"));
|
dtNew.addFieldSetEntity(fse);
|
}
|
}
|
return dtNew;
|
}
|
return null;
|
}
|
}
|
