com.dz.module.charge.ChargeService.java Source code

Java tutorial

Introduction

Here is the source code for com.dz.module.charge.ChargeService.java

Source

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);
    }
}