Java tutorial
package com.sunesoft.ancon.core.saleContract.application; import com.sun.org.apache.xpath.internal.operations.Bool; import com.sunesoft.ancon.core.AnconDbSupport; import com.sunesoft.ancon.core.companys.application.SecondPartyService; import com.sunesoft.ancon.core.companys.application.dto.ContractorPartyDto; import com.sunesoft.ancon.core.saleContract.application.criteria.SaleContractCriteria; import com.sunesoft.ancon.core.saleContract.application.dtos.*; import com.sunesoft.ancon.core.saleContract.domain.SaleContract; import com.sunesoft.ancon.core.uAuth.application.DeptmentService; import com.sunesoft.ancon.core.uAuth.application.dtos.DeptmentDto; import com.sunesoft.ancon.fr.results.CommonResult; import com.sunesoft.ancon.fr.results.PagedResult; import com.sunesoft.ancon.fr.results.ResultFactory; import com.sunesoft.ancon.fr.utils.DateHelper; import com.sunesoft.ancon.fr.utils.DtoFactory; import com.sunesoft.ancon.fr.utils.StringUtils; import org.hibernate.Criteria; import org.hibernate.Query; import org.hibernate.SQLQuery; import org.hibernate.criterion.Order; import org.hibernate.criterion.Projections; import org.hibernate.criterion.Restrictions; import org.hibernate.transform.Transformers; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.io.Console; import java.util.*; /** * Created by admin on 2016/11/24. */ @Service("saleContractService") public class SaleContractServiceImpl implements SaleContractService { @Autowired AnconDbSupport dbSupport; @Autowired DeptmentService deptmentService; @Autowired SecondPartyService secondPartyService; @Override public CommonResult addSaleContract(SaleContractDto dto) { SaleContract sale = new SaleContract(); SaleContract contract = new SaleContract(); if (dto.getParentId() != null) { sale.setId(dto.getParentId()); contract.setParentContract(sale); } //?????? // List<SaleContractDto> list = this.getAll(); // for (SaleContractDto saleContractDto : list) { // if (dto.getNum().equals(saleContractDto.getNum())) { // return ResultFactory.commonError("????"); // } // } SaleContract saleCon = this.getBySaleContractNum(dto.getNum()); if (saleCon != null) return ResultFactory.commonError("????"); SaleContract saleContract = DtoFactory.convert(dto, contract); Long id = (Long) dbSupport.getSaleContract().add(saleContract); return ResultFactory.commonSuccess(id); } @Override public boolean deleteSaleContract(Long[] id) { Criteria criteria = dbSupport.getSession().createCriteria(SaleContract.class); if (id != null && id.length > 0) { criteria.add(Restrictions.in("id", id)); } List<SaleContract> list = criteria.list(); for (SaleContract sale : list) { sale.setIsActive(false); dbSupport.getSaleContract().update(sale); } return true; } @Override public CommonResult updateSaleContract(SaleContractDto dto) { SaleContract contract = this.getBySaleContractNum(dto.getNum()); SaleContractDto contractDto = this.getById(dto.getId());//?? if (contract != null && !dto.getNum().equals(contractDto.getNum())) return ResultFactory.commonError("????"); SaleContract saleContract = dbSupport.getSaleContract().get(dto.getId()); //?????? // List<SaleContractDto> list = this.getAll(); // for (SaleContractDto saleContractDto : list) { // // // if (dto.getNum().equals(saleContractDto.getNum()) && saleContractDto.getId() != dto.getId()) { // return ResultFactory.commonError("????"); // } // } saleContract.setName(dto.getName()); saleContract.setNum(dto.getNum());//???? saleContract.setJiaFangName(dto.getJiaFangName()); saleContract.setContractMoney(dto.getContractMoney()); saleContract.setProjectMajor(dto.getProjectMajor()); saleContract.setContractBeginTime(dto.getContractBeginTime()); saleContract.setContractEndTime(dto.getContractEndTime()); saleContract.setProjectStartTime(dto.getProjectStartTime()); saleContract.setContractStatus(dto.getContractStatus()); saleContract.setContractType(dto.getContractType()); saleContract.setBidNotice(dto.getBidNotice()); saleContract.setConstructLicense(dto.getConstructLicense()); saleContract.setProjectSettlement(dto.getProjectSettlement()); saleContract.setFinishCheck(dto.getFinishCheck()); saleContract.setRemark(dto.getRemark()); saleContract.setJudgeMoney(dto.getJudgeMoney()); saleContract.setJudgeTime(dto.getJudgeTime()); saleContract.setJudgeStatus(dto.getJudgeStatus()); saleContract.setContractIsReturn(dto.getContractIsReturn()); saleContract.setBranchCompany(dto.getBranchCompany()); saleContract.setCompanyId(dto.getCompanyId()); dbSupport.getSaleContract().update(saleContract); return ResultFactory.commonSuccess(dto.getId()); } @Override public List<SaleContractDto> getAll() { Criteria criteria = dbSupport.getSession().createCriteria(SaleContract.class); criteria.add(Restrictions.eq("isActive", true)); // criteria.add(Restrictions.eq("hasParent",false)); List<SaleContract> saleContracts = criteria.list(); List<SaleContractDto> list = new ArrayList<>(); for (SaleContract sale : saleContracts) { SaleContractDto dto = new SaleContractDto(); if (sale.getParentContract() != null) dto.setParentId(sale.getParentContract().getId()); List<SaleContractDto> childDto = new ArrayList<>(); if (sale.getChildContract() != null && sale.getChildContract().size() > 0) { for (SaleContract con : sale.getChildContract()) { childDto.add(DtoFactory.convert(con, new SaleContractDto())); } } dto.setChildContractDto(childDto); SaleContractDto dto_1 = DtoFactory.convert(sale, dto); list.add(dto_1); } return list; } @Override public List<InSimpleSaleContractDto> getInSimpleAll() { String hql = " select new com.sunesoft.ancon.core.saleContract.application.dtos.InSimpleSaleContractDto(s.id,s.name,s.companyId) from SaleContract as s where s.isActive=1"; Query query = dbSupport.getSession().createQuery(hql); List<InSimpleSaleContractDto> list = query.list(); return list == null ? Collections.EMPTY_LIST : list; } @Override public List<SaleContractSimpleDto> getSimpleAll() { // Criteria criteria = dbSupport.getSession().createCriteria(SaleContract.class); // criteria.add(Restrictions.eq("isActive", true)); // criteria.add(Restrictions.eq("hasParent",false)); // List<SaleContract> saleContracts = criteria.list(); SQLQuery query = dbSupport.getSession().createSQLQuery( "select id,contract_type as contractType,contract_status as contractStatus,num name,parent_contract_id as parentId,companyId from ancon_sys_sale_contract"); List<SaleContractSimpleDto> result = dbSupport.queryForObjects(SaleContractSimpleDto.class, query); return result; // for(Object contr : objLink) // { // // } // List<SaleContractSimpleDto> list = new ArrayList<>(); // for (SaleContract sale : saleContracts) { // SaleContractSimpleDto dto = new SaleContractSimpleDto(); // if (sale.getParentId() != null) // dto.setParentId(sale.getParentId()); // // SaleContractSimpleDto dto_1 = DtoFactory.convert(sale, dto); // list.add(dto_1); // } // return list; } @Override public SaleContractDto getById(Long id) { SaleContract saleContract = dbSupport.getSaleContract().get(id); List<SaleContractDto> childDtos = new ArrayList<>();//? SaleContract parentContract = null;// if (saleContract.getChildContract() != null && saleContract.getChildContract().size() > 0) { List<SaleContract> childList = saleContract.getChildContract(); for (SaleContract sale : childList) { SaleContractDto childrenDto = DtoFactory.convert(sale, new SaleContractDto()); childDtos.add(childrenDto); } } if (saleContract.getParentContract() != null) { parentContract = saleContract.getParentContract(); } SaleContractDto dto = new SaleContractDto(); if (childDtos != null && childDtos.size() > 0) dto.setChildContractDto(childDtos); if (parentContract != null) dto.setParentContractDto(DtoFactory.convert(parentContract, new SaleContractDto())); SaleContractDto lastDto = DtoFactory.convert(saleContract, dto); return lastDto; } @Override public SaleContractDto getByNum(String num) { Criteria criteria = dbSupport.getSession().createCriteria(SaleContract.class); criteria.add(Restrictions.eq("isActive", true)); criteria.add(Restrictions.eq("num", num)); List<SaleContract> list = criteria.list(); if (list != null && list.size() > 0) return DtoFactory.convert(list.get(0), new SaleContractDto()); return null; } @Override public PagedResult<SaleContractDtoModel> findPages(SaleContractCriteria contractCriteria) { Criteria criteria = dbSupport.getSession().createCriteria(SaleContract.class); //sql String querySql = "select y.*,sum(c.output_value) sum_output_value from (select x.*,sum(g.gathering_money) sum_gathering_money " + " from (select s.*,sum(b.price_tax_count_money) sum_billing_money " + " from ancon_sys_sale_contract s left join ancon_sys_billing b on b.sale_contract_id=s.id " + " and b.is_active=1 group by id) x " + " left join ancon_sys_gathering_management g on x.id=g.sale_contract_id and g.is_active=1 group by x.id) y " + " left join ancon_sys_construction_value c on y.id=c.sale_contract_id AND c.is_active=1 "; String countSql = "select count(*) from ancon_sys_sale_contract y"; String group = " GROUP BY y.id "; String order = " order by y.contract_begin_time desc "; StringBuffer whereCondition = new StringBuffer(" where y.is_active =1 "); // criteria.add(Restrictions.eq("isActive",true)); if (contractCriteria.getBeginTime() != null) { whereCondition.append(" and y.contract_begin_time >"); //whereCondition.append("'"); whereCondition.append(DateHelper.formatDate(contractCriteria.getBeginTime(), "yyyyMMdd")); //whereCondition.append("'"); } if (contractCriteria.getEndTime() != null) { whereCondition.append(" and y.contract_begin_time <"); //whereCondition.append("'"); whereCondition.append(DateHelper.formatDate(contractCriteria.getEndTime(), "yyyyMMdd")); // whereCondition.append("'"); } if (contractCriteria.getCompanyId() != null) { whereCondition.append(" and y.companyId = "); whereCondition.append("'"); whereCondition.append(contractCriteria.getCompanyId()); whereCondition.append("'"); } if (!StringUtils.isNullOrWhiteSpace(contractCriteria.getJiaFangName())) { whereCondition.append(" and y.jia_fang_name like "); whereCondition.append("'%"); whereCondition.append(contractCriteria.getJiaFangName().trim()); whereCondition.append("%'"); } if (!StringUtils.isNullOrWhiteSpace(contractCriteria.getContractType())) { whereCondition.append(" and y.contract_type like "); whereCondition.append("'%"); whereCondition.append(contractCriteria.getContractType().trim()); whereCondition.append("%'"); } if (!StringUtils.isNullOrWhiteSpace(contractCriteria.getProjectMajor())) { whereCondition.append(" and y.project_major like "); whereCondition.append("'%"); whereCondition.append(contractCriteria.getProjectMajor().trim()); whereCondition.append("%'"); } if (!StringUtils.isNullOrWhiteSpace(contractCriteria.getContractStatus())) { whereCondition.append(" and y.contract_status like "); whereCondition.append("'%"); whereCondition.append(contractCriteria.getContractStatus().trim()); whereCondition.append("%'"); } //? // if (!StringUtils.isNullOrWhiteSpace(contractCriteria.getBidNotice()) || !StringUtils.isNullOrWhiteSpace(contractCriteria.getConstructLicense()) || !StringUtils.isNullOrWhiteSpace(contractCriteria.getFinishCheck()) || !StringUtils.isNullOrWhiteSpace(contractCriteria.getProjectSettlement()) || !StringUtils.isNullOrWhiteSpace(contractCriteria.getContractIsReturn()) || !StringUtils.isNullOrWhiteSpace(contractCriteria.getNumber())) { if (!StringUtils.isNullOrWhiteSpace(contractCriteria.getBidNotice())) { if (contractCriteria.getBidNotice().equals("a_bidNotice")) { order = " order by y.bid_notice ASC ";//? } else { order = " order by y.bid_notice DESC ";//? } } //?? CALL usp_dynamic_total_report(); if (!StringUtils.isNullOrWhiteSpace(contractCriteria.getConstructLicense())) { if (contractCriteria.getConstructLicense().equals("a_constrctLicense")) { order = " order by y.construct_license ASC "; } else { order = " order by y.construct_license DESC "; } } //? if (!StringUtils.isNullOrWhiteSpace(contractCriteria.getFinishCheck())) { if (contractCriteria.getFinishCheck().equals("a_finishCheck")) { order = " order by y.finish_check ASC "; } else { order = " order by y.finish_check DESC "; } } //? if (!StringUtils.isNullOrWhiteSpace(contractCriteria.getProjectSettlement())) { if (contractCriteria.getProjectSettlement().equals("a_projectSettlement")) { order = " order by y.project_settlement ASC "; } else { order = " order by y.project_settlement DESC "; } } //??? if (!StringUtils.isNullOrWhiteSpace(contractCriteria.getContractIsReturn())) { if (contractCriteria.getContractIsReturn().equals("a_contractIsReturn")) { order = " order by y.contract_is_return ASC "; } else { order = " order by y.contract_is_return DESC "; } } //??? if (!StringUtils.isNullOrWhiteSpace(contractCriteria.getNumber())) { if (contractCriteria.getNumber().equals("a_number")) { order = " order by y.num ASC "; } else { order = " order by y.num DESC "; } } } else { order = " order by y.contract_begin_time DESC "; } querySql += whereCondition.toString() + group + order; //? Long totalCount = dbSupport.getCountBySql(countSql + whereCondition.toString(), null); // int totalCount = ((Long) criteria.setProjection(Projections.rowCount()).uniqueResult()).intValue(); // criteria.setProjection(null); SQLQuery query = dbSupport.getSession().createSQLQuery(querySql); query.setFirstResult((contractCriteria.getPageNumber() - 1) * contractCriteria.getPageSize()) .setMaxResults(contractCriteria.getPageSize()); //?? List<SaleContractDtoModel> result = dbSupport.queryForObjects(SaleContractDtoModel.class, query); for (SaleContractDtoModel dto : result) { // ??-? Date beginTime = new Date(); Date endTime = dto.getContract_end_time(); long intervalMilli = endTime.getTime() - beginTime.getTime(); int days = (int) (intervalMilli / (24 * 60 * 60 * 1000)); dto.setSurplus_day(days); } // List<Map<String,Object>> result =query.list(); return new PagedResult<>(result, contractCriteria.getPageNumber(), contractCriteria.getPageSize(), totalCount.intValue()); } public PagedResult<SaleContractDto> findPagesBySaleContract_echarts(SaleContractCriteria contractCriteria) { Criteria criteria = dbSupport.getSession().createCriteria(SaleContract.class); criteria.add(Restrictions.eq("isActive", true)); if (contractCriteria.getBeginTime() != null) criteria.add(Restrictions.ge("contractBeginTime", contractCriteria.getBeginTime())); if (contractCriteria.getEndTime() != null) criteria.add(Restrictions.le("contractBeginTime", contractCriteria.getEndTime())); if (!StringUtils.isNullOrWhiteSpace(contractCriteria.getBranchCompany())) criteria.add(Restrictions.eq("branchCompany", contractCriteria.getBranchCompany())); if (!StringUtils.isNullOrWhiteSpace(contractCriteria.getJiaFangName())) criteria.add(Restrictions.eq("jiaFangName", contractCriteria.getJiaFangName())); if (!StringUtils.isNullOrWhiteSpace(contractCriteria.getContractType())) criteria.add(Restrictions.eq("contractType", contractCriteria.getContractType())); if (!StringUtils.isNullOrWhiteSpace(contractCriteria.getProjectMajor())) criteria.add(Restrictions.eq("projectMajor", contractCriteria.getProjectMajor())); if (!StringUtils.isNullOrWhiteSpace(contractCriteria.getContractStatus())) criteria.add(Restrictions.eq("contractStatus", contractCriteria.getContractStatus())); //? int totalCount = ((Long) criteria.setProjection(Projections.rowCount()).uniqueResult()).intValue(); criteria.setProjection(null); criteria.addOrder(Order.asc("contractBeginTime")); criteria.setFirstResult((contractCriteria.getPageNumber() - 1) * contractCriteria.getPageSize()) .setMaxResults(contractCriteria.getPageSize()); List<SaleContract> saleContracts = criteria.list(); List<SaleContractDto> list = new ArrayList<>(); for (SaleContract sale : saleContracts) { SaleContractDto dto = DtoFactory.convert(sale, new SaleContractDto()); list.add(dto); } return new PagedResult<SaleContractDto>(list, contractCriteria.getPageNumber(), contractCriteria.getPageSize(), totalCount); } @Override public List<SaleContractDto> getByCompanyId(Long companyId) { Criteria criteria = dbSupport.getSession().createCriteria(SaleContract.class); criteria.add(Restrictions.eq("isActive", true)); criteria.add(Restrictions.eq("companyId", companyId)); List<SaleContract> saleContracts = criteria.list(); List<SaleContractDto> list = new ArrayList<>(); for (SaleContract sale : saleContracts) { SaleContractDto dto = DtoFactory.convert(sale, new SaleContractDto()); list.add(dto); } return list; } @Override public List<Map<String, Object>> getSaleContractsCountData(String year, String branchCompany, String jiafangName) { Date date = new Date(); String current_year = DateHelper.formatDate(date, "yyyy"); String year_start = current_year + "0101"; String year_end = current_year + "1231"; String hql = "select MONTH(contract_begin_time) sale_month,sum(judge_money) judge_money " + "from ancon_sys_sale_contract where is_active=1 AND contract_begin_time BETWEEN '" + year_start + "' AND '" + year_end + "' GROUP BY sale_month "; Query query = dbSupport.getSession().createSQLQuery(hql) .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); List<Map<String, Object>> result = query.list(); return result; } @Override public List<Map<String, Object>> getSaleContracts_count_byNumber() { Date date = new Date(); String current_year = DateHelper.formatDate(date, "yyyy"); String year_start = current_year + "0101"; String year_end = current_year + "1231"; String hql = "select MONTH(contract_begin_time) sale_month,count(*) count from ancon_sys_sale_contract" + " where is_active=1 AND contract_begin_time BETWEEN '" + year_start + "' AND '" + year_end + "' GROUP BY sale_month ;"; Query query = dbSupport.getSession().createSQLQuery(hql) .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); List<Map<String, Object>> result = query.list(); return result; } @Override public List<Map<String, Object>> getBilling_count() { Date date = new Date(); String current_year = DateHelper.formatDate(date, "yyyy"); String year_start = current_year + "0101"; String year_end = current_year + "1231"; String hql = "select MONTH(billing_date) month,sum(price_tax_count_money) money FROM ancon_sys_billing " + "where is_active=1 AND billing_date BETWEEN '" + year_start + "' AND '" + year_end + "' GROUP BY month"; Query query = dbSupport.getSession().createSQLQuery(hql) .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); List<Map<String, Object>> result = query.list(); return result; } @Override public List<Map<String, Object>> getGathering_count() { Date date = new Date(); String current_year = DateHelper.formatDate(date, "yyyy"); String year_start = current_year + "0101"; String year_end = current_year + "1231"; String hql = "select MONTH(gathering_time) month,sum(gathering_money) money FROM ancon_sys_gathering_management " + "where is_active=1 AND gathering_time BETWEEN '" + year_start + "' AND '" + year_end + "' GROUP BY month"; Query query = dbSupport.getSession().createSQLQuery(hql) .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); List<Map<String, Object>> result = query.list(); return result; } @Override public List<Map<String, Object>> getConValue_count() { Date date = new Date(); String current_year = DateHelper.formatDate(date, "yyyy"); String year_start = current_year + "0101"; String year_end = current_year + "1231"; String hql = "select MONTH(write_date) month,sum(output_value) money FROM ancon_sys_construction_value " + "where is_active=1 AND write_date BETWEEN '" + year_start + "' AND '" + year_end + "' GROUP BY month"; Query query = dbSupport.getSession().createSQLQuery(hql) .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); List<Map<String, Object>> result = query.list(); return result; } @Override public List<Map<String, Object>> getAllCount_biilingAndGatheringAndConValue(Long saleContractId) { String hql = ""; if (saleContractId != null) { hql = "select y.*,sum(c.output_value) sum_conValue_money from (select x.*,sum(g.gathering_money) sum_gathering_money " + " from (select s.id id,b.sale_contract_id sale_contract_id, s.judge_money,sum(b.price_tax_count_money) sum_billing_money " + " from ancon_sys_sale_contract s left join ancon_sys_billing b on b.sale_contract_id=s.id " + " and b.is_active=1 group by id) x " + " left join ancon_sys_gathering_management g on x.id=g.sale_contract_id and g.is_active=1 group by x.id) y " + " left join ancon_sys_construction_value c on y.id=c.sale_contract_id AND c.is_active=1" + " where y.id='" + saleContractId + "' " + "group by y.id "; } Query query = dbSupport.getSession().createSQLQuery(hql) .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); List<Map<String, Object>> result = query.list(); return result; } @Override public List<DynamicTotalReportFormDto> getDynamicTotalReportForm() { String sql = "CALL usp_dynamic_total_report();"; SQLQuery query = dbSupport.getSession().createSQLQuery(sql); List<DynamicTotalReportFormDto> result = dbSupport.queryForObjects(DynamicTotalReportFormDto.class, query); return result; } @Override public List<ContractReportFormDto> getReportFormByBranchCompany(SaleContractCriteria contractCriteria) { SQLQuery query = null; // //sql //sql String querySql = "select a.companyId,a.branch_company, " + "a.sum_judge_money sum_year_judge_money, " + "b.sum_billing_money, " + "c.sum_gathering_money, " + "d.sum_output_value from " + "(select s.companyId,s.branch_company, " + "sum(s.judge_money) sum_judge_money " + "from ancon_sys_sale_contract s "; // sql String querySql_2 = " group by s.companyId )a left join ( " + "select s.companyId,sum(b.price_tax_count_money) sum_billing_money from ancon_sys_sale_contract s join ancon_sys_billing b on s.id=b.sale_contract_id "; // sql String querySql_3 = " GROUP BY s.companyId " + ") b on a.companyId = b.companyId " + "left join ( " + "select s.companyId,sum(g.gathering_money) sum_gathering_money from ancon_sys_sale_contract s join ancon_sys_gathering_management g on s.id=g.sale_contract_id "; // sql String querySql_4 = " GROUP BY s.companyId " + " ) c on a.companyId = c.companyId " + "left join ( " + "select s.companyId,sum(c.output_value) sum_output_value from ancon_sys_sale_contract s join ancon_sys_construction_value c on s.id = c.sale_contract_id "; //? sql String querySql_5 = " GROUP BY s.companyId ) d on a.companyId = d.companyId "; StringBuffer whereCondition = new StringBuffer(" where s.is_active =1 ");//??? if (contractCriteria.getCompanyId() != null) { whereCondition.append(" and s.companyId = '"); whereCondition.append(contractCriteria.getCompanyId()); whereCondition.append("'"); } if (contractCriteria.getContractType() != null) { whereCondition.append(" and s.contract_type like '%"); whereCondition.append(contractCriteria.getContractType()); whereCondition.append("%'"); } if (contractCriteria.getJiaFangName() != null) { whereCondition.append(" and s.jia_fang_name like '%"); whereCondition.append(contractCriteria.getJiaFangName()); whereCondition.append("%'"); } String timeCondition_1 = "";//? String timeCondition_2 = "";//? String timeCondition_3 = "";//? String timeCondition_4 = "";//? if (contractCriteria.getBeginTime() != null) { timeCondition_1 += " and s.contract_begin_time >=" + DateHelper.formatDate(contractCriteria.getBeginTime(), "yyyyMMdd");//? timeCondition_2 += " and b.billing_date >=" + DateHelper.formatDate(contractCriteria.getBeginTime(), "yyyyMMdd");//? timeCondition_3 += " and g.gathering_time >=" + DateHelper.formatDate(contractCriteria.getBeginTime(), "yyyyMMdd");//? timeCondition_4 += " and c.write_date >=" + DateHelper.formatDate(contractCriteria.getBeginTime(), "yyyyMMdd");//? } if (contractCriteria.getEndTime() != null) { timeCondition_1 += " and s.contract_begin_time <=" + DateHelper.formatDate(contractCriteria.getEndTime(), "yyyyMMdd");//? timeCondition_2 += " and b.billing_date <=" + DateHelper.formatDate(contractCriteria.getEndTime(), "yyyyMMdd");//? timeCondition_3 += " and g.gathering_time <=" + DateHelper.formatDate(contractCriteria.getEndTime(), "yyyyMMdd");//? timeCondition_4 += " and c.write_date <=" + DateHelper.formatDate(contractCriteria.getEndTime(), "yyyyMMdd");//? } querySql = querySql + whereCondition + timeCondition_1 + querySql_2 + whereCondition + timeCondition_2 + querySql_3 + whereCondition + timeCondition_3 + querySql_4 + whereCondition + timeCondition_4 + querySql_5; query = dbSupport.getSession().createSQLQuery(querySql); List<ContractReportFormDto> result = dbSupport.queryForObjects(ContractReportFormDto.class, query); return result; } @Override public List<IndexContractsTotalCountDto> getContractsTotalByMonthAndYear() { String sql = "select t.*,y.* from " + "(select sum_m_judge_money sum_month_judge_money, " + "a.sum_judge_money sum_year_judge_money from " + "(select sum(s.judge_money) sum_judge_money " + "from ancon_sys_sale_contract s where YEAR(s.contract_begin_time)=YEAR(NOW()) )a, " + "(select sum(s.judge_money) sum_m_judge_money " + "from ancon_sys_sale_contract s where YEAR(s.contract_begin_time)=YEAR(NOW()) and MONTH(s.contract_begin_time)=MONTH(NOW()) )ma )t ,( " + "select sum_m_judge_money sum_month_past_judge_money, " + "a.sum_judge_money sum_year_past_judge_money from " + "(select sum(s.judge_money) sum_judge_money " + "from ancon_sys_sale_contract s where YEAR(s.contract_begin_time)=YEAR(NOW())-1 )a , " + "(select sum(s.judge_money) sum_m_judge_money " + "from ancon_sys_sale_contract s where YEAR(s.contract_begin_time)=YEAR(NOW())-1 and MONTH(s.contract_begin_time)=MONTH(NOW()) )ma )y; "; SQLQuery query = dbSupport.getSession().createSQLQuery(sql); List<IndexContractsTotalCountDto> result = dbSupport.queryForObjects(IndexContractsTotalCountDto.class, query); return result; } @Override public List<Map<Integer, Object>> summeryJudgeMoney(SaleContractCriteria contractCriteria) { String sql = " SELECT SUM(IFNULL(s.judge_money,0)) money, DATE_FORMAT(s.contract_begin_time,'%m') mon FROM ancon_sys_sale_contract s WHERE "; if (!StringUtils.isNullOrWhiteSpace(contractCriteria.getBranchCompany())) { sql = sql + " '" + contractCriteria.getBranchCompany() + "'AND "; } if (!StringUtils.isNullOrWhiteSpace(contractCriteria.getJiaFangName())) { sql = sql + " '" + contractCriteria.getJiaFangName() + "'AND "; } if (contractCriteria.getYear() != null) { sql = sql + " DATE_FORMAT(s.contract_begin_time,'%Y')= '" + contractCriteria.getYear() + "' AND"; } int i = sql.lastIndexOf("AND"); if (i != -1) { sql = sql.substring(0, i); } else { int d = sql.indexOf("WHERE"); sql = sql.substring(0, d); } sql = sql + " GROUP BY mon"; Query query = dbSupport.getSession().createSQLQuery(sql) .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); return query.list(); } @Override public List<Map<Integer, Object>> summerysalType(String begin, String end) { // String sql = " SELECT SUM(s.judge_money) v, DATE_FORMAT(s.contract_begin_time,'%m') m , s.contract_type t FROM ancon_sys_sale_contract s "; String sql = " SELECT SUM(if(s.contract_type='??',s.judge_money,0)) b , SUM(if(s.contract_type='???',s.judge_money,0)) z , DATE_FORMAT(s.contract_begin_time,'%m') m FROM ancon_sys_sale_contract s WHERE "; if (!StringUtils.isNullOrWhiteSpace(begin)) { sql = sql + " s.contract_begin_time < '" + begin + "' AND "; } if (!StringUtils.isNullOrWhiteSpace(end)) { sql = sql + " s.contract_begin_time > '" + end + "' AND "; } int i = sql.lastIndexOf("AND"); if (i != -1) { sql = sql.substring(0, i); } else { int d = sql.indexOf("WHERE"); sql = sql.substring(0, d); } sql = sql + " GROUP BY m "; Query query = dbSupport.getSession().createSQLQuery(sql) .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP); return query.list(); } @Override public CommonResult editUploadDtoInfo(SaleContractDto dto) { // SaleContract saleContract = DtoFactory.convert(dto, new SaleContract()); CommonResult commonResult = null; //? List<DeptmentDto> deptmentDtos = deptmentService.getAllDeptCommpany(); Boolean isName = false; for (DeptmentDto deptmentDto : deptmentDtos) { isName = dto.getBranchCompany().equals(deptmentDto.getDeptName()); if (dto.getBranchCompany().equals(deptmentDto.getDeptName())) { dto.setCompanyId(deptmentDto.getId()); break; } } if (!isName) { System.out.println(dto.getBranchCompany() + ",?????"); return ResultFactory.commonError(dto.getBranchCompany() + ",?????"); } //?? List<ContractorPartyDto> contractorPartyDtos = secondPartyService.getByTypeAll(2); Boolean isExist = false; if (dto.getJiaFangName() == null || dto.getJiaFangName().equals("")) { System.out.println("????" + dto.getName() + "\n" + "???"); return ResultFactory .commonError("????" + dto.getName() + "\n" + "???"); } for (ContractorPartyDto partyDto : contractorPartyDtos) { isExist = dto.getJiaFangName().equals(partyDto.getName()); if (isExist) break; } if (!isExist) { ContractorPartyDto contractorPartyDto = new ContractorPartyDto(); contractorPartyDto.setName(dto.getJiaFangName()); contractorPartyDto.setType(2); secondPartyService.addFirstParty(contractorPartyDto); } //?????? // List<SaleContractDto> list = this.getAll(); // Boolean numIsExist = false; // for (SaleContractDto saleContractDto : list) { // numIsExist = dto.getNum().equals(saleContractDto.getNum()); // if (numIsExist) { // dto.setId(saleContractDto.getId()); // commonResult=this.updateSaleContract(dto); // break; // } // } // if (!numIsExist) { // commonResult=this.addSaleContract(dto); // } SaleContract contract = this.getBySaleContractNum(dto.getNum()); if (contract != null) commonResult = this.updateSaleContractByNum(dto); else commonResult = this.addSaleContract(dto); System.out.println("???" + dto.getNum() + "??"); return commonResult; } @Override public SaleContract getBySaleContractNum(String number) { Criteria criteria = dbSupport.getSession().createCriteria(SaleContract.class); criteria.add(Restrictions.eq("isActive", true)); criteria.add(Restrictions.eq("num", number)); SaleContract saleContract = (SaleContract) criteria.uniqueResult(); return saleContract; } @Override public CommonResult updateSaleContractByNum(SaleContractDto dto) { SaleContract saleContract = this.getBySaleContractNum(dto.getNum()); //?????? // List<SaleContractDto> list = this.getAll(); // for (SaleContractDto saleContractDto : list) { // // // if (dto.getNum().equals(saleContractDto.getNum()) && saleContractDto.getId() != dto.getId()) { // return ResultFactory.commonError("????"); // } // } saleContract.setName(dto.getName()); saleContract.setNum(dto.getNum());//???? saleContract.setJiaFangName(dto.getJiaFangName()); saleContract.setContractMoney(dto.getContractMoney()); saleContract.setProjectMajor(dto.getProjectMajor()); saleContract.setContractBeginTime(dto.getContractBeginTime()); saleContract.setContractEndTime(dto.getContractEndTime()); saleContract.setProjectStartTime(dto.getProjectStartTime()); saleContract.setContractStatus(dto.getContractStatus()); saleContract.setContractType(dto.getContractType()); saleContract.setBidNotice(dto.getBidNotice()); saleContract.setConstructLicense(dto.getConstructLicense()); saleContract.setProjectSettlement(dto.getProjectSettlement()); saleContract.setFinishCheck(dto.getFinishCheck()); saleContract.setRemark(dto.getRemark()); saleContract.setJudgeMoney(dto.getJudgeMoney()); saleContract.setJudgeTime(dto.getJudgeTime()); saleContract.setJudgeStatus(dto.getJudgeStatus()); saleContract.setContractIsReturn(dto.getContractIsReturn()); saleContract.setBranchCompany(dto.getBranchCompany()); saleContract.setCompanyId(dto.getCompanyId()); // SaleContract sale = DtoFactory.convert(saleContract,new SaleContract()); // dbSupport.getSession().evict(saleContract); dbSupport.getSaleContract().update(saleContract); return ResultFactory.commonSuccess(saleContract.getId()); } }