package com.product.administration.service;
|
|
import java.util.Date;
|
|
import com.product.util.BaseUtil;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.stereotype.Component;
|
|
import com.product.administration.config.CmnConst;
|
import com.product.administration.service.ide.ICustomerInfoService;
|
import com.product.core.dao.BaseDao;
|
import com.product.core.entity.DataTableEntity;
|
import com.product.core.entity.FieldSetEntity;
|
import com.product.core.exception.BaseException;
|
import com.product.core.permission.PermissionService;
|
import com.product.core.service.support.AbstractBaseService;
|
import com.product.core.service.support.QueryFilterService;
|
import com.product.core.spring.context.SpringMVCContextHolder;
|
import com.product.core.transfer.Transactional;
|
|
@Component
|
public class CustomerInfoService extends AbstractBaseService implements ICustomerInfoService{
|
|
@Autowired
|
BaseDao baseDao;
|
|
@Autowired
|
PermissionService permissionService;
|
|
@Autowired
|
QueryFilterService queryFilterService;
|
|
/**
|
* 查询客户列表
|
* @param cpage
|
* @param pageSize
|
* @param fse
|
* @return
|
*/
|
public DataTableEntity listCustomerInfo(Integer cpage,Integer pageSize,FieldSetEntity fse) {
|
//数据权限
|
String dataFilter=permissionService.getDataFilter(CmnConst.CREATED_BY);
|
//高级查询
|
String searchFilter=queryFilterService.getQueryFilter(fse);
|
// StringBuilder sbSql = new StringBuilder();
|
// sbSql.append("SELECT \n");
|
// sbSql.append(" A.uuid,A.customer_name, A.customer_manager, A.customer_manager_leader,A.business_agent, \n");
|
// sbSql.append(" CONCAT(B.linkman, '-', B.mobile) AS customer_contact \n");
|
// sbSql.append("FROM product_project_customer A \n");
|
// sbSql.append("LEFT JOIN product_project_customer_sub B ON B.customer_uuid = A.uuid \n");
|
// if (!BaseUtil.strIsNull(searchFilter)) {
|
// sbSql.append("WHERE ").append(searchFilter);
|
// }
|
|
if(!BaseUtil.strIsNull(dataFilter)) {
|
if (!BaseUtil.strIsNull(searchFilter)) {
|
dataFilter+=" AND "+ searchFilter;
|
}
|
}else {
|
if (!BaseUtil.strIsNull(searchFilter)) {
|
dataFilter=searchFilter;
|
}
|
}
|
|
DataTableEntity dt=baseDao.listTable(CmnConst.PRODUCT_PROJECT_CUSTOMER, searchFilter, null, null, null, pageSize, cpage);
|
// DataTableEntity dt = baseDao.listTable(sbSql.toString(), new Object[] {}, pageSize, cpage);
|
baseDao.loadPromptData(dt);
|
return dt;
|
}
|
|
/**
|
* 查询客户详情
|
* @param uuid
|
* @return
|
*/
|
public FieldSetEntity findCustomerInfo(String uuid) {
|
FieldSetEntity fieldSetEntity = baseDao.getFieldSetEntity(CmnConst.PRODUCT_PROJECT_CUSTOMER, uuid, true);
|
FieldSetEntity fse = this.findCustomerValue(uuid);
|
fieldSetEntity.setValue("money", fse.getString("money"));
|
fieldSetEntity.setValue("returnedMoney", fse.getString("returnedMoney"));
|
fieldSetEntity.setValue("totalNumber", fse.getString("totalNumber"));
|
fieldSetEntity.setValue("endNum", fse.getString("endNum"));
|
FieldSetEntity contractProjectMakeMoney = this.contractProjectMakeMoney(uuid);
|
fieldSetEntity.addSubDataTable(contractProjectMakeMoney.getSubDataTable("product_project_contract_info"));
|
fieldSetEntity.addSubDataTable(contractProjectMakeMoney.getSubDataTable("product_project_info"));
|
fieldSetEntity.addSubDataTable(contractProjectMakeMoney.getSubDataTable("product_project_contract_invoice_info"));
|
fieldSetEntity.addSubDataTable(contractProjectMakeMoney.getSubDataTable("product_project_contract_payment_collection"));
|
return fieldSetEntity;
|
}
|
|
/**
|
* 查询合同,项目,开票,回款
|
* @param uuid
|
* @return
|
*/
|
public FieldSetEntity contractProjectMakeMoney(String uuid) {
|
FieldSetEntity fieldSet = new FieldSetEntity();
|
fieldSet.setTableName(CmnConst.PRODUCT_PROJECT_CUSTOMER);
|
//合同表 product_project_contract_info owner
|
DataTableEntity contractData = baseDao.listTable("product_project_contract_info"," owner = ? and flow_flag = 2 ",new Object[]{uuid});
|
baseDao.loadPromptData(contractData);
|
fieldSet.addSubDataTable(contractData);
|
//项目表 product_project_info
|
DataTableEntity projectData = baseDao.listTable("product_project_info"," owner = ? ",new Object[]{uuid});
|
DataTableEntity invoiceData = new DataTableEntity();
|
DataTableEntity paymentData = new DataTableEntity();
|
if(!BaseUtil.dataTableIsEmpty(contractData)){
|
for (int i = 0; i < contractData.getRows(); i++) {
|
FieldSetEntity fieldSetEntity = contractData.getFieldSetEntity(i);
|
String contractUuid = fieldSetEntity.getString("uuid");
|
StringBuffer sql = new StringBuffer();
|
sql.append(" SELECT a.contract_name,a.change_the_amount,b.finish_time FROM product_project_contract_change a JOIN ")
|
.append("product_sys_flow_task b ON a.uuid = b.record_uuid WHERE b.finish_type = 2 ")
|
.append(" AND a.contract_name = ? ORDER BY b.finish_time DESC");
|
DataTableEntity dataTable = baseDao.listTable(sql.toString(), new String[]{contractUuid});
|
if(!BaseUtil.dataTableIsEmpty(dataTable)){
|
fieldSetEntity.setValue("contract_amount",dataTable.getString(0, "change_the_amount"));
|
}
|
//开票子表
|
StringBuffer invoiceSql = new StringBuffer();
|
invoiceSql.append(" SELECT b.* FROM product_project_contract_invoice a ")
|
.append(" LEFT JOIN product_project_contract_invoice_info b on a.uuid = b.contract_invoice_uuid WHERE a.contract_name = ? ");
|
invoiceData.addFieldSetEntity(baseDao.listTable(invoiceSql.toString(),new Object[]{contractUuid}));
|
//回款子表
|
StringBuffer paymentSql = new StringBuffer();
|
paymentSql.append(" SELECT b.* FROM product_project_contract_payment a LEFT JOIN ")
|
.append(" product_project_contract_payment_collection b on a.uuid = b.contract_payment_uuid WHERE a.contract_name = ? ");
|
paymentData.addFieldSetEntity(baseDao.listTable(paymentSql.toString(),new Object[]{contractUuid}));
|
}
|
}
|
fieldSet.addSubDataTable(projectData);
|
baseDao.loadPromptData(invoiceData);
|
fieldSet.addSubDataTable(invoiceData);
|
fieldSet.addSubDataTable(paymentData);
|
return fieldSet;
|
}
|
|
/**
|
* 查询客户合同金额，合同回款，累计项目数,累计商机
|
* @param uuid
|
* @return
|
*/
|
public FieldSetEntity findCustomerValue(String uuid) {
|
FieldSetEntity fieldSetEntity = new FieldSetEntity();
|
fieldSetEntity.setTableName(CmnConst.PRODUCT_PROJECT_CUSTOMER);
|
//查询合同金额
|
StringBuffer moneySql = new StringBuffer();
|
moneySql.append(" SELECT IFNULL(SUM(d.money),0) money FROM ( ")
|
.append(" SELECT contract_amount money FROM product_project_contract_info WHERE owner = ? and uuid not in (SELECT contract_name FROM product_project_contract_change WHERE owner = ?) ")
|
.append(" UNION ALL ")
|
.append(" SELECT c.change_the_amount money FROM ( ")
|
.append(" SELECT a.contract_name,MAX(b.finish_time) finish_time FROM product_project_contract_change a JOIN ")
|
.append("product_sys_flow_task b ON a.uuid = b.record_uuid WHERE b.finish_type = 2 GROUP BY a.contract_name) ")
|
.append(" b ")
|
.append(" LEFT JOIN ")
|
.append(" (SELECT a.*,b.finish_time FROM product_project_contract_change a LEFT JOIN ")
|
.append("product_sys_flow_task b ON a.uuid = b.record_uuid) c ")
|
.append(" ON b.contract_name = c.contract_name ")
|
.append(" AND b.finish_time = c.finish_time ")
|
.append(" WHERE c.owner = ? ")
|
.append(" ) d");
|
|
DataTableEntity moneyDataTableEntity = baseDao.listTable(moneySql.toString(), new String[]{uuid,uuid,uuid});
|
if(!BaseUtil.dataTableIsEmpty(moneyDataTableEntity)){
|
fieldSetEntity.setValue("money",moneyDataTableEntity.getString(0, "money"));
|
}
|
|
//查询回款
|
StringBuffer returnedMoneySql = new StringBuffer();
|
returnedMoneySql.append(" SELECT SUM(c.collection_amount) returnedMoney FROM product_project_contract_info a ")
|
.append(" RIGHT JOIN product_project_contract_payment b ")
|
.append(" ON a.uuid = b.contract_name RIGHT JOIN product_project_contract_payment_collection c on b.uuid = c.contract_payment_uuid ")
|
.append(" WHERE a.owner = ?" );
|
DataTableEntity returnedMoneyData = baseDao.listTable(returnedMoneySql.toString(), new String[]{uuid});
|
if(!BaseUtil.dataTableIsEmpty(returnedMoneyData)) {
|
fieldSetEntity.setValue("returnedMoney",returnedMoneyData.getString(0, "returnedMoney"));
|
}
|
//查询工程数
|
DataTableEntity projectData = baseDao.listTable("SELECT * FROM product_project_info WHERE owner = ? ", new String[]{uuid});
|
int endNum = 0;
|
if(!BaseUtil.dataTableIsEmpty(projectData)){
|
for (int i = 0; i < projectData.getRows(); i++) {
|
FieldSetEntity fieldSet = projectData.getFieldSetEntity(i);
|
String project_status = fieldSet.getString("project_status");
|
if("完工".equals(project_status)){
|
endNum++;
|
}
|
}
|
}
|
fieldSetEntity.setValue("totalNumber",projectData.getRows());
|
fieldSetEntity.setValue("endNum",endNum);
|
return fieldSetEntity;
|
}
|
|
/**
|
* 新增客户信息
|
* @param fse
|
* @return
|
* @throws BaseException
|
*/
|
@Transactional
|
public boolean addCustomerInfo(FieldSetEntity fse) throws BaseException {
|
return baseDao.saveFieldSetEntity(fse);
|
}
|
|
/**
|
* 修改客户信息
|
* @param fse
|
* @return
|
* @throws BaseException
|
*/
|
@Transactional
|
public boolean updateCustomerInfo(FieldSetEntity fse) throws BaseException{
|
fse.setValue(CmnConst.UPDATED_BY, SpringMVCContextHolder.getCurrentUser().getUser_id());
|
fse.setValue(CmnConst.UPDATED_UTC_DATETIME, new Date());
|
return baseDao.update(fse,true);
|
}
|
|
/**
|
* 删除客户信息
|
* @param uuid
|
* @return
|
* @throws BaseException
|
*/
|
@Transactional
|
public boolean deleteCustomerInfo(String uuid) throws BaseException{
|
String[] uuids = uuid.split(",");
|
return baseDao.delete(CmnConst.PRODUCT_PROJECT_CUSTOMER, BaseUtil.buildQuestionMarkFilter(CmnConst.UUID, uuids.length, true), uuids);
|
}
|
}
|
