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.common.lang.StringUtils;
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);
		
//		if(!StringUtils.isEmpty(dataFilter)) {
//			if (!StringUtils.isEmpty(searchFilter)) {
//				dataFilter+=" AND "+ searchFilter;
//			}
//		}else {
//			if (!StringUtils.isEmpty(searchFilter)) {
//				dataFilter=searchFilter;
//			}
//		}
        
		DataTableEntity dt=baseDao.listTable(CmnConst.product_project_CUSTOMER, searchFilter, null, null, null, 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);
	}
}