Java tutorial
package com.dz.module.charge; import com.dz.common.factory.HibernateSessionFactory; import com.dz.common.global.DateUtil; import com.dz.common.global.Page; import com.dz.common.global.TimePass; import com.dz.module.contract.BankCard; import com.dz.module.contract.BankCardDao; import com.dz.module.contract.Contract; import com.dz.module.contract.ContractDao; import com.dz.module.driver.Driver; import com.dz.module.driver.DriverDao; import com.dz.module.vehicle.Vehicle; import com.dz.module.vehicle.VehicleDao; import org.apache.commons.collections.CollectionUtils; import org.apache.commons.collections.Predicate; import org.apache.commons.collections.Transformer; import org.apache.commons.lang.StringUtils; import org.hibernate.HibernateException; import org.hibernate.Query; import org.hibernate.Session; import org.hibernate.Transaction; import org.hibernate.transform.Transformers; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.math.BigDecimal; import java.util.*; /** * @author doggy * Created on 15-11-12. */ @Service public class ChargeService { @Autowired private ChargeDao chargeDao; @Autowired private ContractDao contractDao; @Autowired private VehicleDao vehicleDao; @Autowired private DriverDao driverDao; @Autowired private BankCardDao bankCardDao; @Autowired private ClearTimeDao clearTimeDao; @Autowired private BankFileDao bankFileDao; @Autowired private BankRecordTmpDao bankRecordTmpDao; /*********************************************************************************************************************/ /**************************************Add the method that need page limit here.**************************************/ /*********************************************************************************************************************/ /** * added . * ?(??+?++?) * @param dept ? * @param date * @return */ public List<PlanDetail> planDetailMultiplyCar(String dept, Date date, Page page) { List<PlanDetail> table = new ArrayList<>(); List<Contract> contractList = contractDao.contractSearchAllAvilable(date, dept, null, page); //TODO:I don't know if it is necessary to filter here fileterContract(contractList); for (Contract c : contractList) { int id = c.getId(); List<ChargePlan> plans = new ArrayList<>(); plans = chargeDao.getAllRecords(id, date); PlanDetail cct = new PlanDetail(); //set header cct.setContractId(id); cct.setTime(date); Driver d = new Driver(); d.setIdNum(c.getIdNum()); Driver driver = driverDao.selectById(d.getIdNum()); if (driver != null) { cct.setDriverName(driver.getName()); cct.setDriverId(driver.getIdNum()); cct.setDept(driver.getDept()); } //TODO:wait to add department if ("".equals(dept) || c.getBranchFirm().equals(dept)) { Vehicle vehicle = vehicleDao.selectByFrameId(c.getCarframeNum()); if (vehicle != null) { cct.setCarNumber(vehicle.getLicenseNum()); cct.setDept(vehicle.getDept()); } BigDecimal heton_base = calculatePlan(plans, "plan_base_contract"); BigDecimal heton = calculatePlan(plans, "contract"); BigDecimal insurance = calculatePlan(plans, "insurance"); BigDecimal other = calculatePlan(plans, "other"); cct.setBaoxian(insurance); cct.setHeton(heton_base.add(heton)); cct.setOther(other); cct.setTotal(insurance.add(heton).add(heton_base).add(other)); //set message table.add(cct); } } return table; } /** * added . * . * @param time ?. * @param dept ?. * @return . */ @Deprecated public List<BankRecord> exportBankFile(Date time, String dept, Page page) { List<BankRecord> records = new ArrayList<>(); //= from Contract c where c.state in (0,-1,1,4) and c.branchFirm = :dept and List<Contract> cls = contractDao.contractSearchAllAvilable(time, dept, page); //TODO:I don't know if it is necessary to filter this contract fileterContract(cls); // if(time.getYear() > now.getYear()||(time.getYear() == now.getYear() && time.getMonth() > now.getMonth())){ // return records; // } for (Contract c : cls) { BankRecord br = new BankRecord(); String carFrame = c.getCarframeNum(); String idNum = c.getIdNum(); Vehicle vehicle = vehicleDao.selectByFrameId(carFrame); if (vehicle == null) continue; Driver tmp = new Driver(); tmp.setIdNum(idNum); Driver driver = driverDao.selectById(tmp.getIdNum()); //TODO add department if (driver != null && (dept.equals("") || dept.equals(c.getBranchFirm()))) { BankCard bc = bankCardDao.getBankCardForPayByDriverId(idNum, vehicle.getCarframeNum()); List<BankCard> bankCards = new ArrayList<BankCard>(); if (bc != null) { bankCards.add(bc); } Map<String, BankCard> bcs = new HashMap<>(); //set Data br.setLicenseNum(vehicle.getLicenseNum()); for (BankCard bc1 : bankCards) { if (bc1.getCardClass().equals("")) { bcs.put("hrb", bc1); } else { bcs.put("other", bc1); } } br.setBankCards(bcs); br.setDriverName(driver.getName()); BigDecimal derserve = getUnClearAdd(c.getId(), time); BigDecimal lastMonthLeft = getlastMontAccountLeft(c.getId(), time); System.out.println(derserve + "|" + lastMonthLeft); if (derserve.add(lastMonthLeft).doubleValue() >= 0) { br.setMoney(new BigDecimal(0)); } else { br.setMoney(derserve.add(lastMonthLeft).abs()); } records.add(br); } } return records; } public List<BankRecord> exportBankFile(Date time, String dept) { List<BankRecord> records = new ArrayList<>(); Session session = null; try { session = HibernateSessionFactory.getSession(); String hql = "select new com.dz.module.charge.BankRecord(" + "d.idNum as idNum," + "d.name as driverName," + "c.carframeNum as carframeNum," + "c.carNum as licenseNum," + "sum(case when year(p.time)>year(:date) then 0.0 " + " when year(p.time)=year(:date) and month(p.time)>month(:date) then 0.0 " + " when year(cl.current)>year(p.time) then 0.0 " + " when year(cl.current)=year(p.time) and month(cl.current)>month(p.time) then 0.0 " + " when p.feeType like 'plan%add%' then -p.fee " + " when p.feeType like 'plan%sub%' then p.fee " + " when p.feeType like 'plan%' then -p.fee " + " when p.feeType like '%add%' then p.fee " + " else -p.fee " + "end) as derserve" + "," + "avg(case when year(cl.current)<year(:date) then c.account" + " when year(cl.current)=year(:date) and month(cl.current)<=month(:date) then c.account " + " when p.feeType='last_month_left' then p.fee" + " else 0.0 " + "end) as left " + ") " + "from ChargePlan p,Contract c,ClearTime cl,Driver d " + "where cl.department=c.branchFirm " + "and p.contractId=c.id " + "and c.state in (0,-1,1,4) " + "and c.branchFirm like :dept " + "and c.idNum=d.idNum " + "and p.isClear != true " + "and ( " + " (c.abandonedFinalTime is null) " + " or (YEAR(c.abandonedFinalTime )*12+MONTH(c.abandonedFinalTime )+(case when DAY(c.abandonedFinalTime )>26 then 1 else 0 end) >= (YEAR(:date)*12+MONTH(:date)))" + ") " + "group by c.id " + "order by c.branchFirm,c.carNum"; Query query = session.createQuery(hql); if (dept.equals("")) { query.setString("dept", "%"); } else { query.setString("dept", "%" + dept + "%"); } query.setDate("date", time); records = query.list(); } catch (HibernateException ex) { ex.printStackTrace(); } finally { HibernateSessionFactory.closeSession(); } return records; } /** * added. *??? * @param time */ public List<BankRecord> getUnClearRecord(Date time, String department, Page page) { List<BankRecord> records = new ArrayList<>(); List<Contract> cls = contractDao.contractSearchAllAvilable(time, department, null, page); //TODO:I don't know if it is necessary to filter this contract list fileterContract(cls); for (Contract c : cls) { BankRecord br = new BankRecord(); String carFrame = c.getCarframeNum(); String idNum = c.getIdNum(); Vehicle vehicle = vehicleDao.selectByFrameId(carFrame); if (vehicle == null) continue; Driver tmp = new Driver(); tmp.setIdNum(idNum); Driver driver = driverDao.selectById(tmp.getIdNum()); //TODO add department if (driver != null && true) { BankCard bc = bankCardDao.getBankCardForPayByDriverId(idNum, vehicle.getCarframeNum()); List<BankCard> bankCards = new ArrayList<BankCard>(); if (bc != null) { bankCards.add(bc); } Map<String, BankCard> bcs = new HashMap<>(); //set Data br.setLicenseNum(vehicle.getLicenseNum()); for (BankCard bc1 : bankCards) { if (bc1.getCardClass().equals("")) { bcs.put("hrb", bc1); } else { bcs.put("other", bc1); } } br.setBankCards(bcs); br.setDriverName(driver.getName()); BigDecimal derserve = getUnClearAdd(c.getId(), time); BigDecimal lastMonthLeft = getlastMontAccountLeft(c.getId(), time); if (derserve.add(lastMonthLeft).doubleValue() >= 0) { br.setMoney(new BigDecimal(0)); } else { br.setMoney(derserve.add(lastMonthLeft).abs()); records.add(br); } } } return records; } /***********************************************************************************************************/ /*********************************************END***********************************************************/ /***********************************************************************************************************/ /** * ?,??/ * @param date * @return */ public List<ChargePlan> getAll(Date date, String feeType) { return chargeDao.getAll(date, feeType); } /** * ? * @param licenseNum ?? * @param startTime * @param endTime ? * @return */ public List<PlanDetail> planDetailOneCar(String licenseNum, Date startTime, Date endTime) { List<PlanDetail> table = new ArrayList<>(); Vehicle tmp = new Vehicle(); tmp.setLicenseNum(licenseNum); Vehicle vehicle = vehicleDao.selectByLicense(tmp); if (vehicle == null) return table; int startYear = startTime.getYear(); int startMonth = startTime.getMonth(); int endYear = endTime.getYear(); int endMonth = endTime.getMonth(); while (startYear <= endYear) { if (startYear == endYear && startMonth > endMonth) break; Date d = new Date(); d.setYear(startYear); d.setMonth(startMonth); d.setDate(1); Contract contract = contractDao.selectByCarId(vehicle.getCarframeNum(), d); if (contract == null) return table; List<ChargePlan> plans = chargeDao.getAllRecords(contract.getId(), d); // System.out.println(plans); // System.out.println(contract.getId()); // System.out.println(d.getYear()+"-"+d.getMonth()); //set base header PlanDetail cpc = new PlanDetail(); cpc.setTime(d); cpc.setPlans(plans); Driver dx = new Driver(); dx.setIdNum(contract.getIdNum()); Driver driver = driverDao.selectById(dx.getIdNum()); if (driver != null) { cpc.setDriverName(driver.getName()); cpc.setDriverId(driver.getIdNum()); cpc.setDept(driver.getDept()); } cpc.setCarNumber(vehicle.getLicenseNum()); cpc.setDept(vehicle.getDept()); //? BigDecimal heton_base = calculatePlan(plans, "plan_base_contract"); BigDecimal heton_base_raw = calculatePlanNoClear(plans, "plan_base_contract"); BigDecimal heton, insurance, other; if (heton_base.equals(heton_base_raw) || heton_base_raw.equals(BigDecimal.ZERO)) { heton = calculatePlan(plans, "contract"); insurance = calculatePlan(plans, "insurance"); other = calculatePlan(plans, "other"); } else { heton = calculatePlanNoClear(plans, "contract"); insurance = calculatePlanNoClear(plans, "insurance"); other = calculatePlanNoClear(plans, "other"); heton_base = heton_base_raw; } cpc.setBaoxian(insurance); cpc.setHeton(heton_base.add(heton)); cpc.setOther(other); cpc.setTotal(insurance.add(heton).add(heton_base).add(other)); table.add(cpc); if (startMonth < 11) { startMonth++; } else { startMonth = 0; startYear++; } } return table; } /** * ??? * @param licenseNum ? * @param time ?? * @return CheckChargeTable that presen */ public CheckChargeTable getSingleCarAndMonthCheckTableByLicenseNum(String licenseNum, Date time) { Vehicle vehicle = new Vehicle(); vehicle.setLicenseNum(licenseNum); vehicle = vehicleDao.selectByLicense(vehicle); if (vehicle == null) return null; // Contract contract = contractDao.selectByCarId(vehicle.getCarframeNum()); Contract contract = contractDao.selectByCarId(vehicle.getCarframeNum(), time); if (contract == null) return null; List<ChargePlan> plans = chargeDao.getAllRecords(contract.getId(), time); CheckChargeTable cct = new CheckChargeTable(); //set header cct.setContractId(contract.getId()); cct.setTime(time); Driver d = new Driver(); d.setIdNum(contract.getIdNum()); Driver driver = driverDao.selectById(d.getIdNum()); if (driver != null) { cct.setDriverName(driver.getName()); cct.setDriverId(driver.getIdNum()); cct.setDept(driver.getDept()); } cct.setDept(vehicle.getDept()); //set message cct.setCarNumber(licenseNum); cct.setBank(calculateItemIn(plans, "bank")); cct.setCash(calculateItemIn(plans, "cash")); cct.setInsurance(calculateItemIn(plans, "insurance")); cct.setOilAdd(calculateItemIn(plans, "oil")); cct.setOther(calculateItemIn(plans, "other")); cct.setPlanAll(calculateTotalPlan(plans)); BigDecimal lastMonth = getlastMontAccountLeft(contract.getId(), time); cct.generated(lastMonth); return cct; } /** * ? * @param CarId ? * @param timePass * @return ? */ @SuppressWarnings("deprecation") public List<CheckTablePerCar> getACarChargeTable(String CarId, TimePass timePass) { List<CheckTablePerCar> table = new ArrayList<>(); Vehicle tmp = new Vehicle(); tmp.setLicenseNum(CarId); Vehicle vehicle = vehicleDao.selectByLicense(tmp); if (vehicle == null) return table; if (timePass == null || timePass.getStartTime() == null || timePass.getEndTime() == null) { return new ArrayList<>(); } int startYear = timePass.getStartTime().getYear(); int startMonth = timePass.getStartTime().getMonth(); int endYear = timePass.getEndTime().getYear(); int endMonth = timePass.getEndTime().getMonth(); while (startYear <= endYear) { if (startYear == endYear && startMonth > endMonth) break; Date d = new Date(); d.setYear(startYear); d.setMonth(startMonth); d.setDate(1); Contract contract = contractDao.selectByCarId(vehicle.getCarframeNum(), d); if (contract == null) return table; List<ChargePlan> plans = chargeDao.getAllRecords(contract.getId(), d); //set base header CheckTablePerCar cpc = new CheckTablePerCar(); cpc.setTime(d); cpc.setPlans(plans); Driver dx = new Driver(); dx.setIdNum(contract.getIdNum()); Driver driver = driverDao.selectById(dx.getIdNum()); if (driver != null) { cpc.setDriverName(driver.getName()); cpc.setDriverId(driver.getIdNum()); cpc.setDept(driver.getDept()); } cpc.setCarNumber(vehicle.getLicenseNum()); cpc.setDept(vehicle.getDept()); //set data cpc.setOil(calculateItemIn(plans, "oil")); cpc.setInsurance(calculateItemIn(plans, "insurance")); cpc.setBank(calculateItemIn(plans, "bank")); cpc.setOther(calculateItemIn(plans, "other")); cpc.setCash(calculateItemIn(plans, "cash")); cpc.setPlanAll(calculateTotalPlan(plans)); cpc.setRealAll(calculateItemIn(plans, "other").add(calculateItemIn(plans, "insurance")) .add(calculateItemIn(plans, "cash")).add(calculateItemIn(plans, "bank")) .add(calculateItemIn(plans, "oil"))); cpc.setLeft(getlastMontAccountLeft(contract.getId(), d)); cpc.setThisMonthLeft(cpc.getRealAll().subtract(cpc.getPlanAll())); table.add(cpc); if (startMonth < 11) { startMonth++; } else { startMonth = 0; startYear++; } } return table; } /** * ???, * @param dept * @return true if success */ public boolean finalClearAll(String dept) { //????? if (!("".equals(dept) || "".equals(dept) || "".equals(dept))) return false; Date totalTime = clearTimeDao.getCurrent("total"); Date deptTime = clearTimeDao.getCurrent(dept); if (!DateUtil.isYearAndMonth(totalTime, deptTime)) return false; List<Contract> contractList = contractDao.contractSearchAllAvilable(deptTime, dept, null, null); //TODOI don't know if it is necessary to filter fileterContract(contractList); // for(Contract contract:contractList){ // Driver d = new Driver(); // d.setIdNum(contract.getIdNum()); // Driver driver = driverDao.selectById(d.getIdNum()); // //TODO wait to add department // if(driver != null && dept.equals(contract.getBranchFirm())){ // clear(contract.getId(),deptTime); // } // clear(contract.getId(),deptTime); // } List<CheckChargeTable> tables = this.getAllCheckChargeTable(deptTime, dept, null, 4); Session session = HibernateSessionFactory.getSession(); Transaction tx = null; try { tx = session.beginTransaction(); for (Contract contract : contractList) { clear(contract.getId(), deptTime); } String hql = "select c1,c2 from Contract c1,Contract c2 where c1.contractFrom=c2.id and (c2.abandonedFinalTime is null or (YEAR(c2.abandonedFinalTime)*12+MONTH(c2.abandonedFinalTime)+(case when DAY(c2.abandonedFinalTime)>26 then 1 else 0 end) >= (YEAR(:currentClearTime)*12+MONTH(:currentClearTime)) ))"; Query query = session.createQuery(hql); query.setDate("currentClearTime", deptTime); List<Object[]> list = query.list(); for (Object[] oarr : list) { Contract c = (Contract) oarr[0]; Contract oc = (Contract) oarr[1]; if (c.getAccount() == null) c.setAccount(BigDecimal.ZERO); if (oc.getAccount() != null) c.setAccount(c.getAccount().add(oc.getAccount())); oc.setAccount(BigDecimal.ZERO); session.saveOrUpdate(c); session.saveOrUpdate(oc); } //?? boolean res = clearTimeDao.plusAMonth(dept, session); Date time1 = clearTimeDao.getCurrent("", session); Date time2 = clearTimeDao.getCurrent("", session); Date time3 = clearTimeDao.getCurrent("", session); if (!DateUtil.isYearAndMonth(totalTime, time1) && !DateUtil.isYearAndMonth(totalTime, time2) && !DateUtil.isYearAndMonth(totalTime, time3)) { clearTimeDao.plusAMonth("total", session); } else { System.out.println(dept); System.out.println(res); System.out.println(time1); System.out.println(time2); System.out.println(time3); System.out.println(totalTime); } for (CheckChargeTable cct : tables) { session.save(cct); } tx.commit(); } catch (HibernateException ex) { ex.printStackTrace(); if (tx != null) tx.rollback(); return false; } finally { HibernateSessionFactory.closeSession(); } return true; } /** * ?? * @param licenseNum ? * @param date ? * @return */ public List<ChargePlan> getAMonthRecords(String licenseNum, Date date) { List<ChargePlan> plans = new ArrayList<>(); Vehicle vehicle = new Vehicle(); vehicle.setLicenseNum(licenseNum); vehicle = vehicleDao.selectByLicense(vehicle); if (vehicle == null) { return plans; } // Contract contract = contractDao.selectByCarId(vehicle.getCarframeNum()); Contract contract = contractDao.selectByCarId(vehicle.getCarframeNum(), date); if (contract == null) { return plans; } else if (contract.getState() != 0) { if (contract.getBranchFirm() != null && contract.getContractEndDate() != null) { Calendar cl = Calendar.getInstance(); Date clearTime = clearTimeDao.getCurrent(contract.getBranchFirm()); cl.setTime(clearTime); cl.add(Calendar.MONTH, -1); if (cl.getTime().after(contract.getContractEndDate())) { return plans; } } else { return plans; } } plans = chargeDao.getAllRecords(contract.getId(), date); for (ChargePlan plan : plans) { plan.setBatchPlan(null); } return plans; } /** * ????? * @param licenseNum ? * @param time * @return ,?,? */ public List<BigDecimal> couldGetMoney(String licenseNum, Date time) { List<BigDecimal> moneys = new ArrayList<>(); Vehicle vehicle = new Vehicle(); vehicle.setLicenseNum(licenseNum); vehicle = vehicleDao.selectByLicense(vehicle); if (vehicle == null) { System.out.println("??"); return moneys; } Contract contract = contractDao.selectByCarId(vehicle.getCarframeNum(), time); if (contract == null || contract.getState() != 0) { System.out.println("????"); return moneys; } List<ChargePlan> plans = chargeDao.getUnclears(contract.getId(), time); BigDecimal oil = calculateItemIn(plans, "oil"); BigDecimal insurance = calculateItemIn(plans, "insurance"); BigDecimal account = new BigDecimal(0); account = account.add(getUnClearAdd(contract.getId(), time)); account = account.add(getlastMontAccountLeft(contract.getId(), time)); moneys.add(oil); moneys.add(insurance); moneys.add(account); return moneys; } /** * ?. * @param brs * @param recorder * @param fid * @return */ public boolean importFile(List<BankRecord> brs, final String recorder, final int fid) { @SuppressWarnings("unchecked") List<BankRecordTmp> list = (List<BankRecordTmp>) CollectionUtils.collect(brs, new Transformer() { @Override public Object transform(Object o) { if (o == null) return null; BankRecord br = (BankRecord) o; BankRecordTmp brt = new BankRecordTmp(); brt.setFid(fid); brt.setStatus(0); brt.setLicenseNum(br.getLicenseNum()); brt.setDriverName(br.getDriverName()); brt.setInTime(clearTimeDao.getCurrent("total")); brt.setRecodeTime(new Date()); brt.setMoney(br.getMoney()); brt.setRecorder(recorder); BankCard bankCard = (BankCard) br.getBankCards().get("hrb"); brt.setBankCardNum(bankCard == null ? "" : bankCard.getCardNumber()); return brt; } }); CollectionUtils.filter(list, new Predicate() { @Override public boolean evaluate(Object o) { if (o == null) return false; else return true; } }); return bankRecordTmpDao.saveList(list); } /** * * @return true if no exception else false. */ public boolean clearBadBankRecords() { return bankRecordTmpDao.clearBadRecord(); } /** * ChargePlan,????. * @return true. */ public boolean fromTmpToSql() { Date current = clearTimeDao.getCurrent("total"); List<BankRecordTmp> list = bankRecordTmpDao.selectByTimeAndStaus(current, 0); List<BankRecordTmp> badRecords = new ArrayList<>(); List<ChargePlan> cps = new ArrayList<>(); Date now = new Date(); for (BankRecordTmp brt : list) { Vehicle vehicle = new Vehicle(); vehicle.setLicenseNum(brt.getLicenseNum()); vehicle = vehicleDao.selectByLicense(vehicle); if (vehicle == null) { brt.setError("??"); badRecords.add(brt); continue; } Contract c = contractDao.selectByCarId(vehicle.getCarframeNum(), DateUtil.getNextMonth26(brt.getInTime())); if (c == null) { brt.setError("??"); badRecords.add(brt); continue; } Contract nc = contractDao.selectByCarId(vehicle.getCarframeNum(), now); Driver driver = driverDao.selectById(nc.getIdNum()); // if(driver == null || !driver.getName().equals(brt.getDriverName())){ // if(nc.getContractFrom()!=null){ // Contract oldC = contractDao.selectById(nc.getContractFrom()); // driver = driverDao.selectById(oldC.getIdNum()); // // if(driver == null || !driver.getName().equals(brt.getDriverName())){ // brt.setError("??"); // badRecords.add(brt); // continue; // } // }else{ // brt.setError("??"); // badRecords.add(brt); // continue; // } // } BankCard bc = bankCardDao.getBankCardForPayByDriverId(driver.getIdNum(), vehicle.getCarframeNum()); if (bc == null) { brt.setError("??"); badRecords.add(brt); continue; } String cardNum = bc.getCardNumber(); if (!cardNum.equals(brt.getBankCardNum())) { brt.setError("????"); badRecords.add(brt); continue; } ChargePlan cp = new ChargePlan(); cp.setContractId(c.getId()); cp.setFeeType("add_bank"); cp.setFee(brt.getMoney()); cp.setTime(brt.getInTime()); cp.setIsClear(false); cp.setInTime(brt.getRecodeTime()); cp.setRegister(brt.getRecorder()); cp.setComment("" + brt.getId()); cps.add(cp); //chargeDao.addChargePlan(cp); } Session session = null; Transaction tx = null; try { session = HibernateSessionFactory.getSession(); tx = (Transaction) session.beginTransaction(); for (ChargePlan cp : cps) session.save(cp); for (BankRecordTmp brt : badRecords) { brt.setStatus(2); session.update(brt); } Query query = session.createQuery("update BankRecordTmp set status = 1 where status = 0"); query.executeUpdate(); tx.commit(); } catch (HibernateException e) { if (tx != null) { tx.rollback(); } throw e; } finally { HibernateSessionFactory.closeSession(); } return true; } /** * . * @param time ? * @param status ? * @return */ public List<BankRecordTmp> getBankRecordByTimeAndStatus(Date time, int status) { return bankRecordTmpDao.selectByTimeAndStaus(time, status); } /** * MD5. * @param md5 md5 * @param date * @return true if this file never imported else false. */ public int writeMd5(String md5, Date date) { return bankFileDao.importFile(md5, date); } /** * ?? * @param plans none * @return none */ private BigDecimal calculateTotalPlan(List<ChargePlan> plans) { BigDecimal totalPlan = new BigDecimal(0); for (ChargePlan plan : plans) { String feeType = plan.getFeeType(); if (feeType.startsWith("plan")) { if (feeType.contains("sub")) { totalPlan = totalPlan.subtract(plan.getFee()); } else { totalPlan = totalPlan.add(plan.getFee()); } } } return totalPlan; } public Date getCurrentTime(String dept) { return clearTimeDao.getCurrent(dept); } /** * ?? * @param plans ??chargePlan * @param feeType ,???. * @return ??. */ private BigDecimal calculateItemIn(List<ChargePlan> plans, String feeType) { //insurance //sql select COALESCE(sum(fee),0)-(select COALESCE(sum(fee),0) from Charge_Plan where fee_Type like 'plan%' and fee_Type like '%insurance%' and fee_Type like '%sub%') from Charge_Plan where fee_Type like 'plan%' and fee_Type like '%insurance%' and fee_Type like '%add%' //hql select COALESCE(sum(fee),0)-(select COALESCE(sum(fee),0) from ChargePlan where feeType like 'plan%' and feeType like '%insurance%' and feeType like '%sub%') from ChargePlan where feeType like 'plan%' and feeType like '%insurance%' and feeType like '%add%' BigDecimal fee = new BigDecimal(0); for (ChargePlan plan : plans) { if (!plan.getFeeType().startsWith("plan") && plan.getFeeType().contains(feeType)) { if (plan.getFeeType().contains("add")) { fee = fee.add(plan.getFee()); } else { fee = fee.subtract(plan.getFee()); } } } return fee; } /** * ?? * @param plans ?ChargePlan. * @param type . * @return ??. */ private BigDecimal calculatePlan(List<ChargePlan> plans, String type) { BigDecimal fee = new BigDecimal(0); for (ChargePlan plan : plans) { if (type.equals("plan_base_contract")) { if (plan.getFeeType().equals("plan_base_contract")) fee = fee.add(plan.getFee()); continue; } if (plan.getFeeType().startsWith("plan") && plan.getFeeType().contains(type)) { if (type.equals("contract") && plan.getFeeType().equals("plan_base_contract")) continue; if (plan.getFeeType().contains("add")) { fee = fee.add(plan.getFee()); } else { fee = fee.subtract(plan.getFee()); } } } return fee; } private BigDecimal calculatePlanNoClear(List<ChargePlan> plans, String type) { BigDecimal fee = new BigDecimal(0); for (ChargePlan plan : plans) { if (plan.isClear()) continue; if (type.equals("plan_base_contract")) { if (plan.getFeeType().equals("plan_base_contract")) fee = fee.add(plan.getFee()); continue; } if (plan.getFeeType().startsWith("plan") && plan.getFeeType().contains(type)) { if (type.equals("contract") && plan.getFeeType().equals("plan_base_contract")) continue; if (plan.getFeeType().contains("add")) { fee = fee.add(plan.getFee()); } else { fee = fee.subtract(plan.getFee()); } } } return fee; } /** * ? * @param contractId ???id() * @param date X,X??. * @return ? */ public BigDecimal getlastMontAccountLeft(int contractId, Date date) { // //select //(case when year(cl.current)<year(:date) then c.account // when year(cl.current)=year(:date) and month(cl.current)<=month(:date) then c.account // else sum(case when feeType='last_month_left' then fee else 0 end) //end) //from Contract c,ClearTime cl where cl.department=c.branchFirm and c.id=:cid BigDecimal left = null; // from Contract c where c.id=:cid Contract contract = contractDao.selectById(contractId); // select cl.current from ClearTime cl,Contract c where cl.department=c.branchFirm and c.id=:cid Date currentMonth = clearTimeDao.getCurrent(contract.getBranchFirm()); //(case when year(cl.current)<year(:date) then c.account // when year(cl.current)=year(:date) and month(cl.current)<=month(:date) then c.account // else sum(case when feeType='last_month_left' then fee else 0 end) //end) if (DateUtil.isYM1BGYM2(date, currentMonth)) { left = contractDao.getAccount(contractId); } else { //else block select fee from ChargePlan where contractId = :contractId and time is not null and year(time)=year(:date) and month(time)=month(:date) and feeType='last_month_left' List<ChargePlan> plans = chargeDao.getAllRecords(contractId, date); for (ChargePlan plan : plans) { if ("last_month_left".equals(plan.getFeeType())) { left = plan.getFee(); } } if (left == null) { left = new BigDecimal(0); } } return left; } /** * ?? * @param contractId none * @param clearTime none * @return none */ private boolean clear(int contractId, Date clearTime) throws HibernateException { Session session = HibernateSessionFactory.getSession(); Contract c = (Contract) session.get(Contract.class, contractId); BigDecimal account = c.getAccount(); //? ChargePlan lastMonthRecord = new ChargePlan(); lastMonthRecord.setFee(account); lastMonthRecord.setIsClear(true); lastMonthRecord.setContractId(contractId); lastMonthRecord.setTime(clearTime); lastMonthRecord.setFeeType("last_month_left"); session.saveOrUpdate(lastMonthRecord); Query query = session.createQuery( "from ChargePlan where contractId = :contractId and isClear=false and year(time)=year(:date) and month(time)=month(:date)"); query.setInteger("contractId", contractId); query.setDate("date", clearTime); // List<ChargePlan> plans = chargeDao.getUnclears(contractId,clearTime); List<ChargePlan> plans = query.list(); for (ChargePlan plan : plans) { // System.out.println(plan.getFeeType() + " test "+plan.getFee()); String feeType = plan.getFeeType(); if (feeType.startsWith("add") || feeType.startsWith("plan_sub")) { account = account.add(plan.getFee()); } else if (feeType.startsWith("sub") || feeType.startsWith("plan_add") || feeType.startsWith("plan_base")) { account = account.subtract(plan.getFee()); } // chargeDao.cleared(plan); plan.setIsClear(true); session.saveOrUpdate(plan); } // contractDao.updateAccount(contractId,account); c.setAccount(account); session.saveOrUpdate(c); return true; } /** * ??? * @param contractId ??id() * @param clearTime * @return ? */ private BigDecimal getUnClearAdd(int contractId, Date clearTime) { List<ChargePlan> plans = new ArrayList<>(); Contract contract = contractDao.selectById(contractId); String dept = contract.getBranchFirm(); if (dept != null) { Date currentMonth = clearTimeDao.getCurrent(dept); if (DateUtil.isYM1BGYM2(clearTime, currentMonth)) { while (DateUtil.isYM1BGYM2(clearTime, currentMonth)) { plans.addAll(chargeDao.getAllRecords(contractId, currentMonth)); currentMonth = DateUtil.getNextMonth(currentMonth); } } } BigDecimal account = new BigDecimal(0); for (ChargePlan plan : plans) { String feeType = plan.getFeeType(); if (feeType.startsWith("add") || feeType.startsWith("plan_sub")) { account = account.add(plan.getFee()); } else if (feeType.startsWith("sub") || feeType.startsWith("plan_add") || feeType.startsWith("plan_base")) { account = account.subtract(plan.getFee()); } } return account; } /** * ??(?) * @param license ? * @return */ public List<BatchPlan> searchBatchPlans(String license) { List<BatchPlan> bps = new ArrayList<BatchPlan>(); Vehicle vehicle = new Vehicle(); vehicle.setLicenseNum(license); Vehicle v = vehicleDao.selectByLicense(vehicle); if (v != null) { Contract contract = contractDao.selectByCarId(v.getCarframeNum()); if (contract == null || contract.getId() == null) { bps = new ArrayList<>(); } else { bps = chargeDao.searchBatchPlan(contract.getId()); } } return bps; } /** * OK,please ignore this file check. * @param md5 md5check code * @return true if this file's md5 is imported else false. */ public boolean isFileExisted(String md5) { return bankFileDao.isFileImported(md5); } /** * ??,??????? */ private void fileterContract(List<Contract> contractList) { CollectionUtils.filter(contractList, new Predicate() { @Override public boolean evaluate(Object object) { if (object == null) return false; Contract contract = (Contract) object; Date clearTime = clearTimeDao.getCurrent("total"); //TODO return DateUtil.isYM1BGYM2(clearTime, contract.getContractBeginDate()); } }); } /** * * generate + casecase.all => ?? * @param plan * @param isToEnd * @return true if success else false. */ public boolean addBatchPlan(BatchPlan plan, boolean isToEnd) { if (plan == null) throw new NullPointerException("the plan shouldn't be null"); plan.generate(isToEnd); System.out.println(plan.getChargePlanList().size()); chargeDao.addBatchPlan(plan); return true; } /** * * @param plan * @param licenseNum ? * @param isToEnd ? * @return true if success else false */ public boolean addBatchPlanPerCar(BatchPlan plan, String licenseNum, boolean isToEnd) { Vehicle vehicle = new Vehicle(); vehicle.setLicenseNum(licenseNum); vehicle = vehicleDao.selectByLicense(vehicle); if (vehicle == null) { return false; } Contract contract = contractDao.selectByCarId(vehicle.getCarframeNum(), plan.getStartTime()); // if(contract == null || contract.getState() != 0){ if (contract == null) { return false; } List<Integer> cil = new ArrayList<>(); cil.add(contract.getId()); plan.setContractIdList(cil); return addBatchPlan(plan, isToEnd); } /** * ???,??? * @param plan * @return */ public boolean addChargePlan(ChargePlan plan) { if (plan == null) throw new NullPointerException("the plan shouldn't be null"); plan.setIsClear(false); int contractId = plan.getContractId(); Contract contract = contractDao.selectById(contractId); if (contract == null) return false; //??????. if (!plan.getFeeType().equals("plan_base_contract")) if ((plan.getFeeType().startsWith("add") || plan.getFeeType().startsWith("sub"))) { if (!DateUtil.isYearAndMonth(plan.getTime(), clearTimeDao.getCurrent(contract.getBranchFirm()))) return false; } else { plan.setTime(clearTimeDao.getCurrent(contract.getBranchFirm())); } if (plan.getFee() == null) return false; boolean flag = chargeDao.addChargePlan(plan); return flag; } /** * ???,??? * @param plan * @return */ public void addChargePlan(ChargePlan plan, Session session) throws HibernateException { if (plan == null) throw new HibernateException("the plan shouldn't be null"); plan.setIsClear(false); int contractId = plan.getContractId(); Contract contract = (Contract) session.get(Contract.class, contractId); if (contract == null) return; //??????. if (!plan.getFeeType().equals("plan_base_contract")) if ((plan.getFeeType().startsWith("add") || plan.getFeeType().startsWith("sub"))) { if (!DateUtil.isYearAndMonth(plan.getTime(), clearTimeDao.getCurrent(contract.getBranchFirm(), session))) return; } else { plan.setTime(clearTimeDao.getCurrent(contract.getBranchFirm(), session)); } if (plan.getFee() == null) return; session.saveOrUpdate(plan); } /** * ???,addChargePlan. * @param plan * @param licenseNum ? * @return */ public boolean addChargePlanByLicenseNum(ChargePlan plan, String licenseNum) { Vehicle vehicle = new Vehicle(); vehicle.setLicenseNum(licenseNum); vehicle = vehicleDao.selectByLicense(vehicle); if (vehicle == null) { return false; } Contract contract = contractDao.selectByCarId(vehicle.getCarframeNum(), plan.getTime()); // if(contract == null || contract.getState() != 0){ if (contract == null) { return false; } plan.setContractId(contract.getId()); return addChargePlan(plan); } /** * ?,useless * @param plan the plan to delete,it must contains id attribute. * @return true if db success else false. */ public boolean deleteChargePlan(ChargePlan plan) { if (plan == null) throw new NullPointerException("the plan shouldn't be null"); plan = chargeDao.getChargePlanById(plan.getId()); if (plan != null || plan.getIsClear() != true) { return chargeDao.deleteChargePlan(plan); } return false; } /** * Actually this method is useless. * @param plan * @return */ public boolean deleteBatchPlan(BatchPlan plan) { if (plan == null) throw new NullPointerException("the plan shouldn't be null"); plan = chargeDao.getBatchPlanById(plan.getId()); if (plan != null && plan.getStartTime().getTime() > Calendar.getInstance().getTime().getTime()) { return chargeDao.deleteBatchPlan(plan); } return false; } /** * beginDate?chargePlan,beginDate. * @param srcId ??id * @param time * @return */ public boolean setCleared(int srcId, Date time) { return chargeDao.setCleared(srcId, time); } /** * ??? * @param srcId ????id * @param srcTime >=??? * @param destId ??id * @param destTime ? * @return */ public boolean planTransfer(int srcId, Date srcTime, int destId, Date destTime) { return chargeDao.planTransfer(srcId, srcTime, destId, destTime); } public void addAndDiv(int cid, Date time) { chargeDao.addAndDiv(cid, time); } /** * Page limit added. * / . * @param date ? * @param dept "" * @param page * @return */ @Deprecated public List<CheckChargeTable> getAllCheckChargeTable(Date date, String dept, Page page) { List<CheckChargeTable> table = new ArrayList<>(); //from Contract where state in (0,-1,1,4) and (abandonedFinalTime is null or (YEAR(abandonedFinalTime)*12+MONTH(abandonedFinalTime)+(case when DAY(abandonedFinalTime)>26 then 1 else 0 end) >= (YEAR(:currentClearTime)*12+MONTH(:currentClearTime)))) List<Contract> contractList = contractDao.contractSearchAllAvilable(date, dept, page); //TODO:I don't know if it is necessary to filter here fileterContract(contractList); for (Contract c : contractList) { int id = c.getId(); List<ChargePlan> plans = new ArrayList<>(); // Date currentMonth = clearTimeDao.getCurrent(c.getBranchFirm()); // if(isYM1BGYM2(date,currentMonth)){ // while(isYM1BGYM2(date,currentMonth)){ // plans.addAll(chargeDao.getAllRecords(id,currentMonth)); // currentMonth = getNextMonth(currentMonth); // } // }else{ //from ChargePlan where contractId = :contractId and time is not null and year(time)=year(:date) and month(time)=month(:date) plans = chargeDao.getAllRecords(id, date); // } CheckChargeTable cct = new CheckChargeTable(); //set header cct.setContractId(id); cct.setTime(date); Driver d = new Driver(); d.setIdNum(c.getIdNum()); Driver driver = driverDao.selectById(d.getIdNum()); if (driver != null) { cct.setDriverName(driver.getName()); cct.setDriverId(driver.getIdNum()); cct.setDept(driver.getDept()); } //TODO:wait to add department if ("".equals(dept) || c.getBranchFirm().equals(dept)) { Vehicle vehicle = vehicleDao.selectByFrameId(c.getCarframeNum()); if (vehicle != null) { cct.setCarNumber(vehicle.getLicenseNum()); cct.setDept(vehicle.getDept()); } //set message //insurance //hql select COALESCE(sum(fee),0)-(select COALESCE(sum(fee),0) from ChargePlan where feeType like 'plan%' and feeType like '%insurance%' and feeType like '%sub%') from ChargePlan where feeType like 'plan%' and feeType like '%insurance%' and feeType like '%add%' // select (sum(case when feeType like '%add%' then fee else (-fee) end)) from ChargePlan where feeType like '%insurance%' // select ( // sum(case // when feeType not like '%insurance%' then 0 // when feeType like '%add%' then fee // else (-fee) end) // ) from ChargePlan cct.setBank(calculateItemIn(plans, "bank")); cct.setCash(calculateItemIn(plans, "cash")); cct.setInsurance(calculateItemIn(plans, "insurance")); cct.setOilAdd(calculateItemIn(plans, "oil")); cct.setOther(calculateItemIn(plans, "other")); cct.setPlanAll(calculateTotalPlan(plans)); BigDecimal lastMonth = getlastMontAccountLeft(id, date); cct.generated(lastMonth); table.add(cct); } } return table; } public List<CheckChargeTable> getAllCheckChargeTable(Date date, String dept, String licenseNum, int status/**0,1,2,3,4 -- ,,,,*/ ) { if (date == null) date = new Date(); Calendar clear_time = Calendar.getInstance(); clear_time.setTime(getCurrentTime("".equals(dept) ? "total" : dept)); clear_time.add(Calendar.DATE, -1); Session session = HibernateSessionFactory.getSession(); try { if (date.before(clear_time.getTime())) { String ql = "from CheckChargeTable where YEAR(time)=:year and MONTH(time)=:month "; if (dept != null && !"".equals(dept)) { ql += "and dept = :dept "; } if (!StringUtils.isEmpty(licenseNum)) { ql += "and carNumber like :carNum "; } String ql2; switch (status) { case 1:// ThisMonthTotalOwe>0 ql2 = "and thisMonthTotalOwe >0.0 "; break; case 0:// ql2 = "and thisMonthTotalOwe<=0.0 "; break; case 2:// ql2 = "and bank <= 0.0 "; break; case 3:// ql2 = "and bank >0.0 "; break; default: ql2 = ""; } Query qy = session.createQuery(ql + ql2); if (dept != null && !"".equals(dept)) { qy.setString("dept", dept); } if (!StringUtils.isEmpty(licenseNum)) { qy.setString("carNum", "%" + licenseNum + "%"); } qy.setInteger("year", date.getYear() + 1900); qy.setInteger("month", date.getMonth() + 1); List<CheckChargeTable> lst = qy.list(); return lst; } String hql = // "select c.id " //+ "from Contract c " //+ "where c.state in (0,-1,1,4) " "and c.state in (0,-1,1,4) " + "and (c.abandonedFinalTime is null or (YEAR(c.abandonedFinalTime)*12+MONTH(c.abandonedFinalTime)+(case when DAY(c.abandonedFinalTime)>26 then 1 else 0 end) " + ">= (YEAR(:currentClearTime)*12+MONTH(:currentClearTime)))) "; if (dept != null && !"".equals(dept)) { hql += "and c.branchFirm = :dept "; } if (!StringUtils.isEmpty(licenseNum)) { hql += "and c.carNum like :carNum "; } String hql2; switch (status) { case 1:// ThisMonthTotalOwe>0 hql2 = "having avg(case when year(cl.current)<year(:currentClearTime) then c.account" + " when year(cl.current)=year(:currentClearTime) and month(cl.current)<=month(:currentClearTime) then c.account " + " when p.feeType='last_month_left' then p.fee" + " else 0.0 " + "end)- " + "sum(case " + "when p.feeType like '%bank%' then 0.0 " + "when p.feeType like '%cash%' then 0.0 " + "when p.feeType like '%oilAdd%' then 0.0 " + "when p.feeType like '%insurance%' then 0.0 " + "when p.feeType like '%other%' then 0.0 " + "when p.feeType like '%add%' then p.fee " + "else (-p.fee) end)>0.0 "; break; case 0:// hql2 = "having avg(case when year(cl.current)<year(:currentClearTime) then c.account" + " when year(cl.current)=year(:currentClearTime) and month(cl.current)<=month(:currentClearTime) then c.account " + " when p.feeType='last_month_left' then p.fee" + " else 0.0 " + "end)- " + "sum(case " + "when p.feeType like '%bank%' then 0.0 " + "when p.feeType like '%cash%' then 0.0 " + "when p.feeType like '%oilAdd%' then 0.0 " + "when p.feeType like '%insurance%' then 0.0 " + "when p.feeType like '%other%' then 0.0 " + "when p.feeType like '%add%' then p.fee " + "else (-p.fee) end)<=0.0 "; break; case 2:// hql2 = "having " + "sum(case " + "when p.feeType not like '%bank%' then 0.0 " + "when p.feeType like '%add%' then p.fee " + "else (-p.fee) end) <=0.0 "; break; case 3:// hql2 = "having " + "sum(case " + "when p.feeType not like '%bank%' then 0.0 " + "when p.feeType like '%add%' then p.fee " + "else (-p.fee) end) >0.0 "; break; default: hql2 = ""; } String hql_out = "select new com.dz.module.charge.CheckChargeTable(" + "p.contractId as contractId,p.time as time," + "sum(case " + "when p.feeType not like '%bank%' then 0.0 " + "when p.feeType like '%plan%' then 0.0 " + "when p.feeType like '%add%' then p.fee " + "else (-p.fee) end) as bank" + "," + "sum(case " + "when p.feeType not like '%cash%' then 0.0 " + "when p.feeType like '%plan%' then 0.0 " + "when p.feeType like '%add%' then p.fee " + "else (-p.fee) end) as cash" + "," + "sum(case " + "when p.feeType not like '%insurance%' then 0.0 " + "when p.feeType like '%plan%' then 0.0 " + "when p.feeType like '%add%' then p.fee " + "else (-p.fee) end) as insurance" + "," + "sum(case " + "when p.feeType not like '%oil%' then 0.0 " + "when p.feeType like '%plan%' then 0.0 " + "when p.feeType like '%add%' then p.fee " + "else (-p.fee) end) as oilAdd" + "," + "sum(case " + "when p.feeType not like '%other%' then 0.0 " + "when p.feeType like '%plan%' then 0.0 " + "when p.feeType like '%add%' then p.fee " + "else (-p.fee) end) as other" + "," + "sum(case " + "when p.feeType not like '%plan%' then 0.0 " + "when p.feeType like '%sub%' then -p.fee " + "else (p.fee) end) as planAll " + "," + "c.carNum as carNumber," + "c.branchFirm as dept," + "d.name as driverName, " + "avg(case when year(cl.current)<year(:currentClearTime) then c.account" + " when year(cl.current)=year(:currentClearTime) and month(cl.current)<=month(:currentClearTime) then c.account " + " when p.feeType='last_month_left' then p.fee" + " else 0.0 " + "end) as lastMonthOwe " + ") from ChargePlan " //+ "where contractId in (" + hql+ " ) " + "p ,Contract c,Driver d,ClearTime cl " + "where p.contractId=c.id and d.idNum=c.idNum and cl.department=c.branchFirm " + "and p.isClear != true " + hql + "and p.time is not null and year(p.time)=year(:currentClearTime) and month(p.time)=month(:currentClearTime) " + "group by c.id " + hql2 + "order by c.branchFirm,c.carNum"; // Query query = session.createQuery(hql_out).setResultTransformer(Transformers.aliasToBean(CheckChargeTable.class)); Query query = session.createQuery(hql_out); if (dept != null && !"".equals(dept)) { query.setString("dept", dept); } if (!StringUtils.isEmpty(licenseNum)) { query.setString("carNum", "%" + licenseNum + "%"); } query.setDate("currentClearTime", date); return query.list(); } catch (HibernateException ex) { ex.printStackTrace(); return new ArrayList<>(); } finally { HibernateSessionFactory.closeSession(); } } public void addAndDiv(Integer cid, Date time, Session session) throws HibernateException { chargeDao.addAndDiv(cid, time, session); } public void setCleared(Integer srcId, Date beginDate, Session session) throws HibernateException { // TODO Auto-generated method stub chargeDao.setCleared(srcId, beginDate, session); } }