com.viettel.hqmc.DAOHE.FilesNoClobDAOHE.java Source code

Java tutorial

Introduction

Here is the source code for com.viettel.hqmc.DAOHE.FilesNoClobDAOHE.java

Source

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.viettel.hqmc.DAOHE;

import com.viettel.common.util.Constants;
import com.viettel.common.util.LogUtil;
import com.viettel.common.util.StringUtils;
import com.viettel.hqmc.BO.FilesNoClob;
import com.viettel.hqmc.BO.Procedure;
import com.viettel.hqmc.FORM.FilesForm;
import com.viettel.voffice.database.DAO.GridResult;
import com.viettel.voffice.database.DAOHibernate.GenericDAOHibernate;
import java.util.ArrayList;
import java.util.List;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import java.util.Date;
import com.viettel.voffice.common.util.DateTimeUtils;
import org.hibernate.SQLQuery;

/**
 *
 * @author gpcp_dund1
 */
public class FilesNoClobDAOHE extends GenericDAOHibernate<FilesNoClob, Long> {

    private static final org.apache.log4j.Logger log = org.apache.log4j.Logger.getLogger(FilesNoClobDAOHE.class);

    public FilesNoClobDAOHE() {
        super(FilesNoClob.class);
    }

    /**
     * Tra cuu ho so
     *
     * @return
     */
    public GridResult searchLookupFiles(FilesForm form, Long deptId, Long userId, String userType, int start,
            int count, String sortField, String sortCustom) {
        try {
            return createQueryLookupFiles(form, deptId, userId, userType, start, count, sortField, sortCustom);
        } catch (Exception ex) {
            LogUtil.addLog(ex);//binhnt sonar a160901
            return new GridResult(0, null);
        }
    }

    public GridResult searchLookupReport(FilesForm form, Long deptId, Long userId, String userType, int start,
            int count, String sortField, String sortCustom) {
        try {
            return reportLookup(form, deptId, userId, userType, start, count, sortField, sortCustom);
        } catch (Exception ex) {
            LogUtil.addLog(ex);//binhnt sonar a160901
            return new GridResult(0, null);
        }
    }

    /**
     * Tng s h s ch? tip nhn
     *
     * @return
     */
    public int getCountSelectNewHomePage() {
        try {

            String hql = "select count(distinct f.fileId) from FilesNoClob f, Fee fe, FeePaymentInfo fpi"
                    + " where f.fileId = fpi.fileId and (f.isTemp is null or f.isTemp=0) and f.isActive=1"
                    + " and fe.feeId = fpi.feeId and fe.feeType=2 and fe.isActive=1" + " and fpi.isActive=1"
                    + " and fpi.status=1" + " and f.userSigned is not null" + " and f.status= "
                    + Constants.FILE_STATUS.NEW;

            Query query = getSession().createQuery(hql);
            int total = Integer.parseInt(query.list().get(0).toString());
            return total;
        } catch (NumberFormatException | HibernateException ex) {
            LogUtil.addLog(ex);//binhnt sonar a160901
            return 0;
        }
    }

    /**
     * chua tiep nhan
     *
     * @return
     */
    public int getCountSelectNewSdbs() {
        try {
            String hql = "select count(distinct f.fileId) from FilesNoClob f " + "where f.isActive = 1 "
                    + "and (f.isTemp is null or f.isTemp = 0) " + "and (f.status = "
                    + Constants.FILE_STATUS.NEW_TO_ADD + ") ";
            Query query = getSession().createQuery(hql);
            int total = Integer.parseInt(query.list().get(0).toString());
            return total;
        } catch (NumberFormatException | HibernateException ex) {
            LogUtil.addLog(ex);//binhnt sonar a160901
            return 0;
        }
    }

    /**
     * ho so dang cho bo sung
     *
     * @return
     */
    public int getCountSelectEVALUATED_TO_ADDHomePage() {
        try {
            String hql = "select count(f.fileId) from FilesNoClob f " + "where (f.status = "
                    + Constants.FILE_STATUS.EVALUATED_TO_ADD + ") " + "and f.isActive = 1 "
                    + "and (f.isTemp is null or f.isTemp = 0)";
            Query query = getSession().createQuery(hql);
            int total = Integer.parseInt(query.list().get(0).toString());
            return total;
        } catch (NumberFormatException | HibernateException ex) {
            LogUtil.addLog(ex);//binhnt sonar a160901
            return 0;
        }
    }

    /**
     * ho so da tra lai
     *
     * @return
     */
    public int getCountSelectGIVE_BACKHomePage() {
        try {
            /*
             binhnt update 150211 ti u truy vn
             */
            List lstParam = new ArrayList();
            String hql = "select count(f.fileId) from FilesNoClob f " + " where " + " f.status in (?,?,?,?,?,?)"
                    + " and f.isActive = 1 " + " and (f.isTemp is null or f.isTemp = 0)";
            lstParam.add(Constants.FILE_STATUS.GIVE_BACK);
            lstParam.add(Constants.FILE_STATUS.ALERT_COMPARISON);
            lstParam.add(Constants.FILE_STATUS.COMPARED);
            lstParam.add(Constants.FILE_STATUS.REVIEW_COMPARISON);
            lstParam.add(Constants.FILE_STATUS.COMPARED_FAIL);
            lstParam.add(Constants.FILE_STATUS.REVIEW_COMPARISON_FAIL);
            Query query = getSession().createQuery(hql);
            for (int i = 0; i < lstParam.size(); i++) {
                query.setParameter(i, lstParam.get(i));
            }
            int total = Integer.parseInt(query.list().get(0).toString());
            return total;
        } catch (NumberFormatException | HibernateException ex) {
            LogUtil.addLog(ex);//binhnt sonar a160901
            return 0;
        }
    }

    //tong so ho so dang cho xac nhan phi tham xem
    public int getCountSelectRECEIVE_PAYHomePage() {
        try {
            String sql = "select count(distinct fpi.PAYMENT_INFO_ID)" + " from files f"
                    + " inner join fee_payment_info fpi" + " on f.file_id = fpi.file_id"
                    + " where fpi.fee_id in (select f.fee_id from fee f where f.fee_type = 2 and f.is_active = 1 )"
                    + " and fpi.is_active = 1" + " and f.is_active = 1"
                    + " and (f.user_Signed is not null or f.status = 18)" + " and f.status <> 0"
                    + " and fpi.status > 2 ";
            //            String sql = "select count(distinct fpi.PAYMENT_INFO_ID) from Files f "
            //                    + "inner join "
            //                    + "fee_payment_info fpi on f.file_id = fpi.file_id "
            //                    + "inner join "
            //                    + "fee fe on fe.fee_id = fpi.fee_id and fe.fee_type = 2 and fe.is_active = 1 "
            //                    + "where fpi.is_active = 1 and fpi.status <> 1 "
            //                    + "and f.is_active = 1 "
            //                    + "and (f.is_temp is null or f.is_temp = 0) "
            //                    + "and (f.user_Signed is not null or f.status = 18) "
            //                    + "and f.status <> 0 ";
            SQLQuery countQuery = (SQLQuery) getSession().createSQLQuery(sql);
            int total = Integer.parseInt(countQuery.uniqueResult().toString());
            return total;
        } catch (NumberFormatException | HibernateException ex) {
            LogUtil.addLog(ex);//binhnt sonar a160901
            return 0;
        }
    }

    //tong so ho so da phe duyet cho nop le phi
    public int getCountSelectAPPROVE_PAYHomePage() {
        try {
            String sql = "select count(distinct fpi.PAYMENT_INFO_ID) from Files f " + "inner join "
                    + "fee_payment_info fpi on f.file_id = fpi.file_id " + "inner join "
                    + "fee fe on fe.fee_id = fpi.fee_id and fe.fee_type = 1 and fe.is_active = 1 "
                    + "where fpi.is_active = 1 " + "and f.is_active = 1 "
                    + "and (f.is_temp is null or f.is_temp = 0) " + "and f.status = "
                    + Constants.FILE_STATUS.APPROVED + "and fpi.status > 2 ";
            SQLQuery countQuery = (SQLQuery) getSession().createSQLQuery(sql);
            int total = Integer.parseInt(countQuery.uniqueResult().toString());
            return total;
        } catch (NumberFormatException | HibernateException ex) {
            LogUtil.addLog(ex);//binhnt sonar a160901
            return 0;
        }
    }

    /**
     * ho so cho tra
     *
     * @return
     */
    public int getCountSelectWaitGiveBackHomePage() {
        try {
            List lstParam = new ArrayList();
            /*
             binhnt update 150211 ti u truy vn
             */
            String sql = "select count(f.file_id) from Files f " + "where f.is_active = 1"
                    + " and (f.is_temp is null or f.is_temp = 0) " + " and f.status in (?,?,?,?,?)";
            lstParam.add(Constants.FILE_STATUS.ALERT_COMPARISON);
            lstParam.add(Constants.FILE_STATUS.REVIEW_COMPARISON);
            lstParam.add(Constants.FILE_STATUS.REVIEW_COMPARISON_FAIL);
            lstParam.add(Constants.FILE_STATUS.COMPARED_FAIL);
            lstParam.add(Constants.FILE_STATUS.COMPARED);
            SQLQuery countQuery = (SQLQuery) getSession().createSQLQuery(sql);
            for (int i = 0; i < lstParam.size(); i++) {
                countQuery.setParameter(i, lstParam.get(i));
            }
            int total = Integer.parseInt(countQuery.uniqueResult().toString());
            return total;
        } catch (NumberFormatException | HibernateException ex) {
            LogUtil.addLog(ex);//binhnt sonar a160901
            return 0;
        }
    }

    /**
     * ho so cho tra
     *
     * @return
     */
    public int getCountSelectGiveBackHomePage() {
        try {
            String sql = "select count(f.file_id) from Files f " + "where f.is_active = 1 "
                    + "and (f.is_temp is null or f.is_temp = 0) " + "and (f.status = "
                    + Constants.FILE_STATUS.GIVE_BACK + ") ";
            SQLQuery countQuery = (SQLQuery) getSession().createSQLQuery(sql);
            int total = Integer.parseInt(countQuery.uniqueResult().toString());
            return total;
        } catch (NumberFormatException | HibernateException ex) {
            LogUtil.addLog(ex);//binhnt sonar a160901
            return 0;
        }
    }

    /**
     * ho so dang xu ly
     *
     * @return
     */
    public int getCountFileIsProcessHomePage() {
        try {//Bo sung trang thai 8, 14, 17, 23
            /*
             binhnt update 150211 ti u truy vn
             */
            List lstParam = new ArrayList();
            String hql = "select count(f.fileId) from FilesNoClob f " + "where"
                    + " f.isActive = 1 and (f.isTemp is null or f.isTemp = 0)"
                    + " and f.status in (?,?,?,?,?,?,?,?,?,?,?,?,?)";
            lstParam.add(Constants.FILE_STATUS.ASSIGNED);
            lstParam.add(Constants.FILE_STATUS.EVALUATED);
            lstParam.add(Constants.FILE_STATUS.REVIEWED);
            lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_EVALUATE);
            lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_REVIEW);
            lstParam.add(Constants.FILE_STATUS.RECEIVED);
            lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_ADD);
            lstParam.add(Constants.FILE_STATUS.REVIEW_TO_ADD);
            lstParam.add(Constants.FILE_STATUS.APPROVE_TO_ADD);
            lstParam.add(Constants.FILE_STATUS.RECEIVED_TO_ADD);
            lstParam.add(Constants.FILE_STATUS.REVIEWED_TO_ADD);
            lstParam.add(Constants.FILE_STATUS.REVIEW_TO_BOSS);
            lstParam.add(Constants.FILE_STATUS.APPROVE_TO_LEADER);
            Query query = getSession().createQuery(hql);
            for (int i = 0; i < lstParam.size(); i++) {
                query.setParameter(i, lstParam.get(i));
            }
            int total = Integer.parseInt(query.list().get(0).toString());
            return total;
        } catch (NumberFormatException | HibernateException ex) {
            LogUtil.addLog(ex);//binhnt sonar a160901
            return 0;
        }
    }

    /**
     * Tao cau truy van Lookup dung chung
     *
     * @param form
     * @param deptId
     * @param userId
     * @param userType
     * @param count
     * @param sortField
     * @param start
     * @param sortCustom
     * @return
     */
    public GridResult createQueryLookupFiles(FilesForm form, Long deptId, Long userId, String userType, int start,
            int count, String sortField, String sortCustom) {
        List lstParam = new ArrayList();
        //        String hql = " from FilesNoClob f, Process p, DetailProduct d, Business b, Category c ";
        String hql = " from FilesNoClob f, Process p, DetailProduct d, Business b ";
        String condition = "";
        condition += " and f.fileId = p.objectId";
        condition += " and  f.detailProductId = d.detailProductId";
        // hieptq update 251214
        //condition += " and  f.productTypeId = c.categoryId";
        condition += " and f.deptId = b.businessId";
        condition += " and f.isActive = 1" + " and f.fileId = p.objectId"
                + "  and (f.isTemp = null or f.isTemp = 0 )";
        if (userType.equals(Constants.ROLES.LEAD_OFFICE_ROLE) || userType.equals(Constants.ROLES.STAFF_ROLE)
                || userType.equals(Constants.ROLES.LEAD_UNIT)) {
        }
        //        if (userType.equals(Constants.ROLES.CLERICAL_ROLE)) {
        //            if (userId != null) {
        //                condition += " and p.sendUserId = ? ";
        //                lstParam.add(userId);
        //            }
        //        }
        if (form != null) {
            if (userType.equals(Constants.ROLES.STAFF_ROLE) && form.getSearchType() != null) {//vcv
                switch (Integer.parseInt(form.getSearchType().toString())) {
                case 42:
                    condition += " and (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.EVALUATED);
                    condition += " and f.staffProcess =?";
                    lstParam.add(userId);
                    break;
                case 47:
                    condition += " and (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_ADD);
                    condition += " and f.staffProcess =?";
                    lstParam.add(userId);
                    break;
                case -26://H s  xem xt cv sbs ch? ph duyt cng vn
                    lstParam.clear();
                    hql = "from FilesNoClob f, Process p, DetailProduct d ";
                    condition = " AND f.isActive = 1" + " AND (f.isTemp = null or f.isTemp = 0 )"
                            + " AND f.fileId = p.objectId" + " and f.detailProductId = d.detailProductId"
                            + " AND f.status = ?" + " AND p.receiveGroupId = ?" + " AND p.receiveUserId = ?";
                    lstParam.add(Constants.FILE_STATUS.REVIEW_TO_ADD);
                    lstParam.add(deptId);
                    lstParam.add(userId);
                    break;
                case -20://Ho so da gui thong bao sdbs cho doanh nghiep
                    lstParam.clear();
                    hql = "from FilesNoClob f, DetailProduct d ";
                    condition = " AND f.isActive=1";
                    condition += " and f.detailProductId = d.detailProductId";
                    condition += " AND (f.status = ?)";
                    condition += " AND f.staffProcess=?";
                    condition += " AND (f.isTemp = null or f.isTemp = 0 ) ";
                    lstParam.add(Constants.FILE_STATUS.EVALUATED_TO_ADD);
                    lstParam.add(userId);
                    break;
                case 33://H s ch? thm nh S?BS CV
                    lstParam.clear();
                    hql = " from FilesNoClob f, Process p , DetailProduct d ";
                    condition = " AND f.fileId = p.objectId";
                    condition += " and f.detailProductId = d.detailProductId";
                    condition += " AND f.isActive = 1 and f.fileId = p.objectId  and (f.isTemp = null or f.isTemp = 0 )";
                    condition += " AND p.receiveGroupId = ?";
                    lstParam.add(deptId);
                    if (userId != null) {
                        //condition += " and p.receiveUserId=?";//141217u binhnt53
                        condition += " and f.staffProcess=?";//141217u binhnt53 cap nhat ho so chuyen vien xu ly nhin thay ho so sdbs cua minh
                        lstParam.add(userId);
                    }
                    condition += " AND f.status = ?";
                    lstParam.add(Constants.FILE_STATUS.RECEIVED_TO_ADD);
                    break;
                case 19://H s lnh o cc yu cu b sung CV
                    lstParam.clear();
                    hql = " from FilesNoClob f, Process p, DetailProduct d ";
                    condition = " AND f.fileId = p.objectId";
                    condition += " and f.detailProductId = d.detailProductId";
                    condition += " AND f.isActive = 1 and (f.isTemp = null or f.isTemp = 0 )";
                    condition += " AND p.receiveGroupId = ?";
                    lstParam.add(deptId);
                    if (userId != null) {
                        condition += " and p.receiveUserId=?";
                        lstParam.add(userId);
                    }
                    condition += " AND f.status = ?";
                    lstParam.add(Constants.FILE_STATUS.REVIEWED_TO_ADD);
                    break;
                case 5://H s cn thng bo i chiu
                    lstParam.clear();
                    hql = " from FilesNoClob f, Process p , DetailProduct d ";
                    condition = " and f.fileId = p.objectId";
                    condition += " and f.detailProductId = d.detailProductId";
                    condition += " AND f.isActive = 1" + " and f.fileId = p.objectId"
                            + " and (f.isTemp = null or f.isTemp = 0 )";
                    condition += " AND p.receiveGroupId = ?";
                    lstParam.add(deptId);
                    if (userId != null) {
                        condition += " and p.receiveUserId=?";
                        lstParam.add(userId);
                    }
                    condition += " AND f.status = ?";
                    lstParam.add(Constants.FILE_STATUS.REVIEWED);
                    break;
                case 50:
                    lstParam.clear();
                    hql = " from FilesNoClob f, Process p, DetailProduct d ";
                    condition = " and f.fileId = p.objectId";
                    condition += " and f.detailProductId = d.detailProductId";
                    condition += " AND f.isActive = 1 and f.fileId = p.objectId  and (f.isTemp = null or f.isTemp = 0 )";
                    condition += " AND (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                    condition += " AND p.receiveGroupId = ?";
                    lstParam.add(deptId);
                    if (userId != null) {
                        condition += " and p.receiveUserId=?";
                        lstParam.add(userId);
                    }
                    break;
                case 417:
                    lstParam.clear();
                    hql = " from FilesNoClob f , DetailProduct d ";
                    condition = " AND f.isActive = 1" + " and (f.isTemp = null or f.isTemp = 0)";
                    condition += " and f.detailProductId = d.detailProductId";
                    condition += " AND (f.status =?)";
                    lstParam.add(Constants.FILE_STATUS.APPROVED);
                    if (userId != null) {
                        condition += " and f.staffProcess=?";
                        lstParam.add(userId);
                    }
                    condition += " AND f.isSignPdf <> 2";
                    break;
                case 422:
                    lstParam.clear();
                    hql = " from FilesNoClob f, DetailProduct d ";
                    condition = " AND f.isActive = 1" + " and (f.isTemp = null or f.isTemp = 0)";
                    condition += " AND (f.status = ?)";
                    condition += " and f.detailProductId = d.detailProductId";
                    lstParam.add(Constants.FILE_STATUS.GIVE_BACK);
                    if (userId != null) {
                        condition += " and f.staffProcess=?";
                        lstParam.add(userId);
                    }
                    condition += " AND f.isSignPdf = 2";
                    break;
                case 423:
                    lstParam.clear();
                    hql = " from FilesNoClob f, DetailProduct d ";
                    condition = " AND f.isActive = 1" + " and (f.isTemp = null or f.isTemp = 0)";
                    condition += " and f.detailProductId = d.detailProductId";
                    condition += " AND (f.status <> ?)";
                    lstParam.add(Constants.FILE_STATUS.GIVE_BACK);
                    if (userId != null) {
                        condition += " and f.staffProcess=?";
                        lstParam.add(userId);
                    }
                    condition += " AND f.isSignPdf = 2";
                    break;
                case 447:
                    lstParam.clear();
                    hql = " from FilesNoClob f, DetailProduct d ";
                    condition = " AND f.isActive = 1 and (f.isTemp = null or f.isTemp = 0)";
                    condition += " and f.detailProductId = d.detailProductId";
                    condition += " AND f.staffProcess=?";
                    lstParam.add(userId);
                    condition += " AND (f.status =?)";
                    lstParam.add(Constants.FILE_STATUS.EVALUATED);
                    break;
                case 448:
                    lstParam.clear();
                    hql = " from FilesNoClob f , DetailProduct d ";
                    condition = " AND f.isActive = 1 and (f.isTemp = null or f.isTemp = 0)";
                    condition += " and f.detailProductId = d.detailProductId";
                    condition += " AND f.staffProcess=?";
                    lstParam.add(userId);
                    condition += " AND (f.status =?)";
                    lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_ADD);
                    break;
                case 20://H s ch? chuyn vin trong t thm nh
                    lstParam.clear();
                    hql = "from FilesNoClob f, Process p, DetailProduct d ";
                    condition = " AND f.isActive = 1";
                    condition += " and f.detailProductId = d.detailProductId";
                    condition += " AND (f.isTemp = null or f.isTemp = 0 )";
                    condition += " AND f.fileId = p.objectId";
                    if (userId != null) {
                        condition += " and p.receiveUserId=?";
                        lstParam.add(userId);
                    }
                    condition += " and p.processType=0";
                    condition += " and f.status = ?";
                    lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                    break;
                case 21://H s gi phi hp cha cho  kin 21
                    lstParam.clear();
                    hql = " from FilesNoClob f, Process p, DetailProduct d ";

                    condition = " AND f.isActive = 1 ";
                    condition += " and f.detailProductId = d.detailProductId";
                    condition += " AND (f.isTemp = null or f.isTemp = 0 ) ";
                    condition += " AND f.fileId = p.objectId ";
                    condition += " AND p.receiveGroupId = ? ";
                    lstParam.add(deptId);
                    if (userId != null) {
                        condition += " AND p.sendUserId = ? ";
                        lstParam.add(userId);
                    }
                    condition += " AND (f.status = ?) ";
                    condition += " AND p.processStatus = ?";
                    lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                    lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                    condition += " AND (p.processId in (select p.processId from Process p where p.processId not in (select distinct pc.processId from ProcessComment pc))) ";
                    condition += " AND (p.processType=0 or p.processType=4) ";
                    break;
                case 22://H s chuyn vin  gi thng bo sa i b sung
                    lstParam.clear();
                    hql = " from FilesNoClob f, Process p, DetailProduct d ";
                    condition = " AND f.fileId = p.objectId";
                    condition += " and f.detailProductId = d.detailProductId";
                    condition += " AND f.isActive = 1 and (f.isTemp = null or f.isTemp = 0)";
                    condition += " AND p.receiveGroupId =?";
                    lstParam.add(deptId);
                    if (userId != null) {
                        condition += " and p.receiveUserId=?";
                        lstParam.add(userId);
                    }
                    condition += " AND (f.status =?)";
                    lstParam.add(Constants.FILE_STATUS.EVALUATED_TO_ADD);
                    break;
                case 26:
                    lstParam.clear();
                    hql = "from FilesNoClob f, Process p, DetailProduct d ";
                    condition = " AND f.isActive=1";
                    condition += " AND f.fileId = p.objectId";
                    condition += " and f.detailProductId = d.detailProductId";
                    condition += " AND f.status = ?";
                    condition += " AND p.receiveUserId=?";
                    condition += " AND p.processType=1";
                    condition += " AND (f.isTemp = null or f.isTemp = 0 ) ";
                    lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_EVALUATE);
                    lstParam.add(userId);
                    break;
                case 27:
                    lstParam.clear();
                    hql = " from FilesNoClob f, Process p, DetailProduct d ";
                    condition = " AND f.isActive = 1 ";
                    condition += " AND (f.isTemp = null or f.isTemp = 0 ) ";
                    condition += " AND f.fileId = p.objectId ";
                    condition += " and f.detailProductId = d.detailProductId";
                    condition += " AND p.receiveGroupId = ? ";
                    lstParam.add(deptId);
                    if (userId != null) {
                        condition += " AND p.sendUserId = ? ";
                        lstParam.add(userId);
                    }
                    condition += " AND (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.EVALUATED_TO_ADD);
                    condition += " AND sysdate - f.evaluateAddDate >= 54";
                    break;
                case 28://H s  c  kin ca t thm xt 28
                    lstParam.clear();
                    hql = " from FilesNoClob f, Process p, ProcessComment pc, DetailProduct d ";
                    condition = " AND f.isActive = 1 ";
                    condition += " AND (f.isTemp = null or f.isTemp = 0 ) ";
                    condition += " AND f.fileId = p.objectId ";
                    condition += " and f.detailProductId = d.detailProductId";
                    condition += " AND pc.processId = p.processId";
                    condition += " AND (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                    if (userId != null) {
                        condition += " AND p.receiveGroupId = (select distinct p.receiveGroupId from Process p where p.receiveUserId = ?)";
                        lstParam.add(userId);
                    }
                    condition += " AND (p.processType=0 or p.processType=4)";
                    condition += " AND p.objectType = 30";
                    break;
                }
            }
            if (userType.equals(Constants.ROLES.CLERICAL_ROLE) && form.getSearchType() != null) {//vvt
                switch (Integer.parseInt(form.getSearchType().toString())) {
                case -11:
                    /*tim kiem bao cao van thu xu ly ho so trong khoang thoi gian.
                     binhnt53 add 150211
                     */
                    lstParam.clear();
                    hql = " from FilesNoClob f, Process p, DetailProduct d ";
                    condition = " AND f.fileId = p.objectId";
                    condition += " AND f.isActive = 1 and (f.isTemp = null or f.isTemp = 0)";
                    condition += " and f.detailProductId = d.detailProductId";
                    if (form.getStatus() != null) {
                        condition += " AND p.status = ?";
                        lstParam.add(form.getStatus());
                    }
                    if (form.getSearchDateFrom() != null) {
                        condition += " and p.sendDate >= ?";
                        lstParam.add(form.getSearchDateFrom());
                    }
                    if (form.getSearchDateTo() != null) {
                        condition += " and p.sendDate <= ?";
                        lstParam.add(form.getSearchDateTo());
                    }
                    break;
                case 1623://h s cn i chiu
                    lstParam.clear();
                    hql = " from FilesNoClob f, Process p, DetailProduct d ";
                    condition = " AND f.fileId = p.objectId";
                    condition += " and f.detailProductId = d.detailProductId";
                    condition += " AND f.isActive = 1 and (f.isTemp = null or f.isTemp = 0)";
                    condition += " AND (f.status =? )";
                    //lstParam.add(Constants.FILE_STATUS.COMPARED_FAIL);
                    lstParam.add(Constants.FILE_STATUS.ALERT_COMPARISON);
                    break;
                case 22:
                    hql = " from FilesNoClob f, Process p, DetailProduct d ";
                    lstParam.clear();
                    condition = " and f.isActive = 1" + " and f.fileId = p.objectId"
                            + " and (f.isTemp = null or f.isTemp = 0)";
                    condition += " and f.detailProductId = d.detailProductId ";
                    condition += " and (f.status = ? or f.status = ? or f.status = ? or f.status = ? or f.status = ? or f.status = ?)";
                    //                        lstParam.add(Constants.FILE_STATUS.APPROVED);
                    lstParam.add(Constants.FILE_STATUS.REVIEW_COMPARISON_FAIL);
                    lstParam.add(Constants.FILE_STATUS.COMPARED_FAIL);
                    lstParam.add(Constants.FILE_STATUS.REVIEW_COMPARISON);
                    lstParam.add(Constants.FILE_STATUS.COMPARED);
                    lstParam.add(Constants.FILE_STATUS.ALERT_COMPARISON);
                    lstParam.add(Constants.FILE_STATUS.GIVE_BACK);
                    condition += " and p.receiveGroupId = ?";
                    lstParam.add(deptId);
                    break;
                case 15:
                    hql = " from FilesNoClob f, Process p, DetailProduct d ";

                    lstParam.clear();
                    condition = " and f.isActive = 1" + " and f.fileId = p.objectId"
                            + " and (f.isTemp = null or f.isTemp = 0)" + " and (f.status = ?)";
                    condition += " and f.detailProductId = d.detailProductId ";
                    lstParam.add(Constants.FILE_STATUS.COMPARED);
                    condition += " and p.receiveGroupId = ?";
                    condition += " and f.isFee = 1 and f.isSignPdf = 2";
                    lstParam.add(deptId);
                    break;
                case 6:
                    hql = " from FilesNoClob f, Process p, DetailProduct d ";

                    lstParam.clear();
                    condition = " and f.isActive = 1" + " and f.fileId = p.objectId"
                            + " and (f.isTemp = null or f.isTemp = 0)" + " and (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.APPROVED);
                    condition += " and f.detailProductId = d.detailProductId ";
                    condition += " and p.receiveGroupId = ?";
                    condition += " and f.isFee = 1 and f.isSignPdf = 2";
                    condition += " and f.isDownload <> 1";//u150112 binhnt53
                    lstParam.add(deptId);
                    break;
                case 20:
                    hql = " from FilesNoClob f, Process p, DetailProduct d ";

                    lstParam.clear();
                    condition = " and f.isActive = 1" + " and f.fileId = p.objectId"
                            + " and (f.isTemp = null or f.isTemp = 0)" + " and (f.status = ?)";
                    lstParam.add(form.getStatus());
                    condition += " and f.detailProductId = d.detailProductId ";
                    condition += " and p.sendGroupId = ?";
                    lstParam.add(deptId);
                    break;
                case -27:
                    //                        hql = " from FilesNoClob f, Process p, DetailProduct d ";
                    hql = " from FilesNoClob f, Process p ";
                    lstParam.clear();
                    condition = " and f.isActive = 1" + " and f.fileId = p.objectId"
                            + " and (f.isTemp = null or f.isTemp = 0)" + " and (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.APPROVE_TO_ADD);
                    //                        condition += " and f.detailProductId = d.detailProductId ";
                    condition += " and p.receiveGroupId = ?";
                    lstParam.add(deptId);
                    break;
                case -6://h s cn k xc thc vn th: h s  i chiu,  thanh ton
                    hql = " from FilesNoClob f, Process p, DetailProduct d ";
                    condition = " and f.fileId = p.objectId" + " and (f.isTemp=null or f.isTemp=0)"
                            + " and f.isActive= 1" + " and f.isSignPdf = 1" + " and f.isFee = 1"
                            + " and f.status = ?" + " and p.receiveGroupId = ?";
                    condition += " and f.detailProductId = d.detailProductId ";
                    lstParam.clear();
                    lstParam.add(Constants.FILE_STATUS.APPROVED);
                    lstParam.add(deptId);
                    break;
                case -66://h s cn k xc thc vn th: h s  i chiu,  thanh ton 4AA
                    hql = " from FilesNoClob f, Process p ";
                    condition = " and f.fileId = p.objectId" + " and (f.isTemp=null or f.isTemp=0)"
                            + " and f.isActive= 1" + " and f.isSignPdf = 1" + " and f.isFee = 1"
                            + " and f.status = ?" + " and p.receiveGroupId = ?"
                            + " and f.filesSourceID is not null";
                    lstParam.clear();
                    lstParam.add(Constants.FILE_STATUS.APPROVED);
                    lstParam.add(deptId);
                    break;
                case -4:
                    //5- H s  yu cu np ph cp s = ? ph duyt, cha np l ph (files.status = 6, fee_payment_info.status = 0,fee.fee_type = 1 , files.isSignPdf=1)
                    /*hql = " from FilesNoClob f, Fee fe, FeePaymentInfo fpi";
                     condition = " and f.fileId = fpi.fileId"
                     + " and (f.isTemp=null or f.isTemp=0)"
                     + " and fe.feeId = fpi.feeId"
                     + " and f.isActive=1 "
                     + " and fpi.isActive=1"
                     + " and fe.isActive=1"
                     + " and fe.feeType=1"
                     + " and fpi.status=0"
                     + " and f.userSigned is not null"
                     + " and (f.status=?)"
                     + " and f.agencyId = ?";*/
                    hql = " from FilesNoClob f, DetailProduct d ";
                    condition = " and (f.isTemp=null or f.isTemp=0)" + " and f.isActive=1 "
                            + " and f.isFee <> 1 and f.isSignPdf <> 2" + " and (f.status=?)"
                            + " and f.agencyId = ?";
                    condition += " and f.detailProductId = d.detailProductId ";
                    lstParam.clear();
                    lstParam.add(Constants.FILE_STATUS.APPROVED);
                    lstParam.add(deptId);
                    break;
                case -3://H s  np ph cp s, ch? tr h s = ? ph duyt,  np l ph (files.status = 6, fee_payment_info.status = 1, fee.fee_type=1, files.isSignPdf=2)
                    hql = " from FilesNoClob f, Fee fe, FeePaymentInfo fpi, DetailProduct d ";
                    condition = " and f.fileId = fpi.fileId and (f.isTemp=null or f.isTemp=0) and f.isActive=1"
                            + " and fe.feeId = fpi.feeId and fe.feeType=1 and fe.isActive=1" + " and fpi.isActive=1"
                            + " and fpi.status=1" + " and f.userSigned is not null" + " and f.isSignPdf = 2"
                            + " and (f.status=?)" + " and f.agencyId = ?";
                    condition += " and f.detailProductId = d.detailProductId ";
                    lstParam.clear();
                    // lstParam.add(Constants.FILE_STATUS.APPROVED);
                    lstParam.add(Constants.FILE_STATUS.COMPARED);//da doi chieu, thanh toan xong tra ho so binhnt53 140926
                    lstParam.add(deptId);
                    break;
                case -2://H s  yu cu np ph cp s = ? ph duyt, cha np l ph (files.status = 6, fee_payment_info.status = 0,fee.fee_type = 1 , files.isSignPdf=1)
                    hql = " from FilesNoClob f, Fee fe, FeePaymentInfo fpi, DetailProduct d ";
                    condition = " and f.fileId = fpi.fileId" + " and (f.isTemp=null or f.isTemp=0)"
                            + " and f.isActive=1" + " and fe.feeId = fpi.feeId and fe.feeType=1 and fe.isActive=1"
                            + " and fpi.isActive=1" + " and fpi.status=0" + " and f.userSigned is not null"
                            + " and f.isSignPdf = 1" + " and (f.status=?)" + " and f.agencyId = ?";
                    condition += " and f.detailProductId = d.detailProductId ";
                    lstParam.clear();
                    lstParam.add(Constants.FILE_STATUS.APPROVED);
                    lstParam.add(deptId);
                    break;
                case -1://H s ch? tip nhn = Mi np v  xc nhn ph (files.status = 1, fee_payment_info.status = 1, fee.fee_type=2), Mi np S?BS (18)
                    hql = " from FilesNoClob f, Fee fe, FeePaymentInfo fpi, Category c , DetailProduct d ";
                    condition = " and f.fileId = fpi.fileId and (f.isTemp=null or f.isTemp=0) and f.isActive=1"
                            + " and fe.feeId = fpi.feeId and fe.feeType=2 and fe.isActive=1"
                            + " and  f.productTypeId = c.categoryId " + " and fpi.isActive=1" + " and fpi.status=1"
                            + " and f.userSigned is not null" + " and f.status=? and f.agencyId = ?";
                    condition += " and f.detailProductId = d.detailProductId ";
                    lstParam.clear();
                    lstParam.add(Constants.FILE_STATUS.NEW);
                    lstParam.add(deptId);
                    break;
                case -7://H s ch? k ton xc nhn = Mi np v cha ng ph thm nh (files.status = 1, fee_payment_info.status <> 1, fee.fee_type=2)
                    hql = " from FilesNoClob f, Fee fe, FeePaymentInfo fpi, DetailProduct d ";
                    condition = " and f.fileId = fpi.fileId and (f.isTemp=null or f.isTemp=0) and f.isActive=1"
                            + " and fe.feeId = fpi.feeId and fe.feeType=2 and fe.isActive=1" + " and fpi.isActive=1"
                            + " and fpi.status > 2" + " and f.userSigned is not null"
                            + " and (f.status=?) and f.agencyId = ?";
                    ;
                    condition += " and f.detailProductId = d.detailProductId ";
                    lstParam.clear();
                    lstParam.add(Constants.FILE_STATUS.NEW);
                    lstParam.add(deptId);
                    break;
                case 0:
                    hql = " from FilesNoClob f, Process p, DetailProduct d ";
                    lstParam.clear();
                    condition = " and f.isActive = 1" + " and f.fileId = p.objectId"
                            + " and (f.isTemp = null or f.isTemp = 0)" + " and (f.status = ?)";
                    lstParam.add(form.getStatus());
                    condition += " and f.detailProductId = d.detailProductId ";
                    condition += " and p.receiveGroupId = ?";
                    lstParam.add(deptId);
                    break;
                case 110://danh sach ho so can luu tru
                    hql = " from FilesNoClob f, Process p, DetailProduct d ";
                    lstParam.clear();
                    condition = " and f.isActive = 1" + " and f.fileId = p.objectId"
                            + " and (f.isTemp = null or f.isTemp = 0)" + " and f.status <> ?" + " and f.isFee <> 2";
                    condition += " and p.receiveGroupId = ?";
                    condition += " and f.detailProductId = d.detailProductId ";
                    lstParam.add(Constants.FILE_STATUS.NEW_CREATE);
                    lstParam.add(deptId);
                    break;
                default:
                    ;
                }
            }
            if (userType.equals(Constants.ROLES.LEAD_UNIT) && form.getSearchType() != null) {//vldp
                switch (Integer.parseInt(form.getSearchType().toString())) {
                case 39:
                    condition += " and f.isActive = 1 and f.fileId = p.objectId AND f.status = ?  and (f.isTemp = null or f.isTemp = 0 )";
                    lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_REVIEW);
                    if (userId != null) {
                        condition += " and f.leaderReviewId=?";
                        lstParam.add(userId);
                    }
                    break;
                case 30:
                    condition += " and f.isActive = 1 and f.fileId = p.objectId AND f.status = ?  and (f.isTemp = null or f.isTemp = 0 )";
                    lstParam.add(Constants.FILE_STATUS.EVALUATED_TO_ADD);
                    if (userId != null) {
                        condition += " and p.receiveUserId=?";
                        lstParam.add(userId);
                    }
                    if (deptId != null) {
                        condition += " AND p.receiveGroupId = ? ";
                        lstParam.add(deptId);
                    }
                    break;
                case 2:
                    condition += " and (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.EVALUATED);
                    condition += " and p.receiveGroupId = ? and p.receiveUserId = ?";
                    lstParam.add(deptId);
                    lstParam.add(userId);
                    condition += " and ((f.leaderEvaluateId = ? and f.leaderReviewId = null) or f.leaderReviewId =?)";
                    lstParam.add(userId);
                    lstParam.add(userId);
                    break;
                case 7:
                    condition += " and (f.status = ?)";////150204 binhnt53 update
                    lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_ADD);
                    condition += " and p.receiveGroupId = ? and p.receiveUserId = ?";
                    lstParam.add(deptId);
                    lstParam.add(userId);
                    condition += " and (f.leaderReviewId =?)";
                    lstParam.add(userId);
                    break;
                case 8:// ho so cho xem xet du thao sdbs
                    condition += " and (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.EVALUATE_TO_ADD);
                    condition += " and p.receiveGroupId = ?";
                    lstParam.add(deptId);
                    condition += " and p.receiveUserId = ?";
                    lstParam.add(userId);
                    condition += " and f.leaderReviewId = ?";
                    lstParam.add(userId);
                    break;
                case 5://ho so cho lanh dao cuc phe duyet, lanh dao phong da duyet
                    condition += " and (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.REVIEWED);
                    condition += " and p.receiveGroupId = ? and p.receiveUserId = ?";
                    lstParam.add(deptId);
                    lstParam.add(userId);
                    break;
                case 22://ho so cho lanh dao cuc phe duyet, lanh dao phong da duyet
                    condition += " and (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.GIVE_BACK);
                    //                        condition += " and p.receiveGroupId = ? and p.receiveUserId = ?";
                    //                        lstParam.add(deptId);
                    //                        lstParam.add(userId);
                    break;
                }
            }
            if (userType.equals(Constants.ROLES.LEAD_OFFICE_ROLE) && form.getSearchType() != null) {//vldc
                switch (Integer.parseInt(form.getSearchType().toString())) {
                case 3:
                    condition += " and (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.REVIEWED);
                    condition += " and p.receiveGroupId = ? and p.receiveUserId = ?";
                    lstParam.add(deptId);
                    lstParam.add(userId);
                    break;
                case 10:
                    condition += " and (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.APPROVED);
                    condition += " and p.receiveGroupId = ? and p.receiveUserId = ?";
                    lstParam.add(deptId);
                    lstParam.add(userId);
                    break;
                case 26:
                    hql = " from FilesNoClob f, Process p, Business b ";
                    lstParam.clear();
                    condition = " and f.isActive=1" + " and f.fileId = p.objectId" + " and f.deptId = b.businessId"
                            + " and (f.isTemp = null or f.isTemp = 0 )" + " and (f.status = ?)"
                            + " and p.receiveGroupId = ?" + " and p.receiveUserId = ?" + " and f.agencyId = ?";
                    lstParam.add(Constants.FILE_STATUS.REVIEW_TO_ADD);
                    lstParam.add(deptId);
                    lstParam.add(userId);
                    lstParam.add(deptId);
                    break;
                case 36://a260515 binhnt
                    condition += " and f.status in (?,?,?,?,?,?)" + " and f.isActive = 1 "
                            + " and (f.isTemp is null or f.isTemp = 0)";
                    lstParam.add(Constants.FILE_STATUS.GIVE_BACK);
                    lstParam.add(Constants.FILE_STATUS.ALERT_COMPARISON);
                    lstParam.add(Constants.FILE_STATUS.COMPARED);
                    lstParam.add(Constants.FILE_STATUS.REVIEW_COMPARISON);
                    lstParam.add(Constants.FILE_STATUS.COMPARED_FAIL);
                    lstParam.add(Constants.FILE_STATUS.REVIEW_COMPARISON_FAIL);
                    break;
                }
            }
            if (userType.equals(Constants.ROLES.ADMIN) && form.getSearchType() != null) {//vQTHT
                switch (Integer.parseInt(form.getSearchType().toString())) {
                case 110://h s cn i chiu
                    lstParam.clear();
                    hql = " from FilesNoClob f, Process p , DetailProduct d ";
                    condition = " AND f.fileId = p.objectId";
                    condition += " and f.detailProductId = d.detailProductId ";
                    condition += " AND f.isActive = 1 and (f.isTemp = null or f.isTemp = 0)";
                    condition += " AND (f.staffProcess <> null)";
                    break;
                }
            }
            if (form.getSearchType() == null) {
                if (form.getStatus() != null && form.getSearchType() == null
                        && (form.getStatus() == 40l || form.getStatus() == 41l || form.getStatus() == 42l
                                || form.getStatus() == 43l || form.getStatus() == 44l || form.getStatus() == 45l
                                || form.getStatus() == 46l || form.getStatus() == 47l || form.getStatus() == 48l
                                || form.getStatus() == 49l || form.getStatus() == 30l
                                || form.getStatus().equals(Constants.FILE_STATUS.REVIEW_COMPARISON_FAIL)
                                || form.getStatus() == 50l)) {
                    lstParam.clear();
                    hql = " from FilesNoClob f, Process p, DetailProduct d, Business b";
                    condition = " and f.fileId = p.objectId and f.detailProductId = d.detailProductId and f.deptId = b.businessId";
                    // Nh sn xut
                    if (form.getManufactureName() != null && form.getManufactureName().length() > 0) {
                        hql += ", Announcement ann";
                        condition += " and f.announcementId = ann.announcementId and lower(ann.manufactureName) like ? ESCAPE '/'";
                        lstParam.add(StringUtils.toLikeString(form.getManufactureName().toLowerCase().trim()));
                    }
                    // S chng nhn cng b
                    if (form.getAnnouncementNo() != null && form.getAnnouncementNo().length() > 0) {
                        hql += ", AnnouncementReceiptPaper ann";
                        condition += " and f.announcementReceiptPaperId = ann.announcementReceiptPaperId and lower(ann.receiptNo) like ? ESCAPE '/'";
                        lstParam.add(StringUtils.toLikeString(form.getAnnouncementNo().toLowerCase().trim()));
                    }
                    //                // thong ke ho so trong ngay
                    if (form.getStatus() != null && form.getStatus() == 40l) {
                        condition += " and f.isActive = 1 and (f.status = ? or f.status = ?) and  to_date(f.sendDate,'yyyy/mm/dd') = to_date(sysdate,'yyyy/mm/dd')  ";
                        lstParam.add(3l);
                        lstParam.add(5l);
                        if (userId != null) {
                            condition += " and p.receiveUserId=?";
                            lstParam.add(userId);
                        }
                    }
                    if (form.getStatus() != null && form.getStatus() == 41l) {
                        condition += " AND f.isActive = 1" + " AND (f.status = ? or f.status = ?)"
                                + " AND  to_date(f.sendDate,'yyyy/mm/dd') = to_date(sysdate,'yyyy/mm/dd')"
                                + " AND (f.isTemp = null or f.isTemp = 0 )";
                        lstParam.add(4l);
                        lstParam.add(5l);
                    }
                    if (form.getStatus() != null && form.getStatus() == 42l) {
                        condition += " and f.isActive = 1" + " and (f.status = ? or f.status = ?)"
                                + " and  to_date(f.sendDate,'yyyy/mm/dd') = to_date(sysdate,'yyyy/mm/dd')"
                                + " and (f.isTemp = null or f.isTemp = 0 )";
                        lstParam.add(6l);
                        lstParam.add(5l);
                    }
                    // ho so bi tra tham dinh lai
                    if (form.getStatus() != null && form.getStatus() == 43l) {
                        condition += " and f.isActive=1" + " and f.status = ?" + " and p.processType=1"
                                + " and (f.isTemp = null or f.isTemp = 0 )";
                        lstParam.add(8l);
                        if (userId != null) {
                            condition += " and p.receiveUserId=?";
                            lstParam.add(userId);
                        }
                    }
                    // ho so cho phoi hop tham dinh chua cho y kien
                    if (form.getStatus() != null && form.getStatus() == 44l) {
                        condition += " and f.isActive=1" + " and (f.status = ?)"
                                + " and (p.processType=0 or p.processType=4)"
                                + " and (p.processId in (select p.processId from Process p where p.processId not in (select distinct pc.processId from ProcessComment pc)))"
                                + " and p.processStatus = ?";
                        lstParam.add(3l);
                        lstParam.add(3l);
                    }

                    //ho so cho xem xet
                    if (form.getStatus() != null && form.getStatus() == 48L) {
                        condition += " and f.isActive=1" + " and (f.status = ? or f.status = ?)"
                                + " and (p.processType=1 or p.processType=0)"
                                + " and (f.isTemp = null or f.isTemp = 0 )";
                        lstParam.add(Constants.FILE_STATUS.EVALUATED);
                        lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_ADD);
                        if (userId != null) {
                            condition += " and p.receiveUserId=?";
                            lstParam.add(userId);
                        }
                    }
                    // ho so lanh dao phong da xem xet
                    if (form.getStatus() != null && form.getStatus() == 46l) {
                        condition += " and f.isActive=1" + " and f.status = ?" + " and p.processType=1"
                                + " and (f.isTemp = null or f.isTemp = 0 )";
                        lstParam.add(5l);///xx
                        if (userId != null) {
                            condition += " and p.receiveUserId=?";
                            lstParam.add(userId);
                        }
                    }
                    //ho so da phe duyet
                    if (form.getStatus() != null && form.getStatus() == 47l) {
                        condition += " and f.isActive=1" + " and f.status = ?" + " and p.processType=1"
                                + " and (f.isTemp = null or f.isTemp = 0 )";
                        lstParam.add(Constants.FILE_STATUS.APPROVED);
                        if (userId != null) {
                            condition += " and p.receiveUserId=?";
                            lstParam.add(userId);
                        }
                    }
                    // ho so tham dnh da gui  kin phn hi
                    if (form.getStatus() != null && form.getStatus() == 49l) {
                        lstParam.clear();
                        hql = " from FilesNoClob f, Process p, ProcessComment pc, DetailProduct d ";
                        condition = " AND f.isActive=1" + " AND f.fileId = p.objectId" + " AND p.objectType = 30"
                                + " AND (f.status = ?)" + " AND (p.processType=0 or p.processType=1)"
                                + " AND pc.processId = p.processId" + " AND (f.isTemp = null or f.isTemp = 0 )";
                        condition += " and f.detailProductId = d.detailProductId";
                        lstParam.add(3l);
                        if (userId != null) {
                            condition += " AND p.receiveGroupId = (select distinct p.receiveGroupId from Process p where p.receiveUserId = ?)";
                            lstParam.add(userId);
                        }
                    }
                    // hieptq update 17.11.14 da phan cong

                    if (form.getStatus() != null && form.getStatus() == 50l) {
                        condition += " and f.isActive=1" + " and f.status = ?" + " and p.processType=1"
                                + " and (f.isTemp = null or f.isTemp = 0 )";
                        lstParam.add(3l);
                        if (userId != null) {
                            condition += " and p.receiveUserId=?";
                            lstParam.add(userId);
                        }
                    }
                    // da thong bao SDBS
                    if (form.getStatus() != null && form.getStatus() == 30l) {
                        condition += " and f.isActive=1" + " and f.status = ?" + " and p.processType=1"
                                + " and (f.isTemp = null or f.isTemp = 0 )";
                        lstParam.add(20l);
                        if (userId != null) {
                            condition += " and p.receiveUserId=?";
                            lstParam.add(userId);
                        }
                    }

                    // da tham dinh - hieptq
                    if (form.getStatus() != null && form.getStatus() == 45l) {
                        condition += " and f.isActive=1" + " and f.status = ?" + " and p.processType=1"
                                + " and (f.isTemp = null or f.isTemp = 0 )";
                        lstParam.add(4l);
                        if (userId != null) {
                            condition += " and p.receiveUserId=?";
                            lstParam.add(userId);
                        }
                    }

                    //
                    if (form.getStatus() != null
                            && form.getStatus() == Constants.FILE_STATUS.REVIEW_COMPARISON_FAIL) {
                        condition += " and f.isActive = 1" + " and f.status = ?"
                                + " and (f.isTemp = null or f.isTemp = 0)";
                        lstParam.add(25l);
                    }
                } else if (form.getStatus() != null && form.getStatus() >= 0l) {
                    condition += " AND f.status = ?";
                    lstParam.add(form.getStatus());
                } else {
                }

                if (userType.equals(Constants.ROLES.LEAD_OFFICE_ROLE)) {
                    if (deptId != null) {
                        condition += " and p.receiveGroupId = ? ";
                        lstParam.add(deptId);
                    }
                    //                    if (userId != null) {
                    //                        condition += " and p.receiveUserId = ? ";
                    //                        lstParam.add(userId);
                    //                    }
                }
                if (userType.equals(Constants.ROLES.STAFF_ROLE)) {
                    if (deptId != null) {
                        if (form.getStatus() != null && form.getStatus() != 40l) {
                            condition += " and p.receiveGroupId = ? ";
                            lstParam.add(deptId);
                        }
                    }
                    if (userId != null) {
                        condition += " and p.receiveUserId = ? ";
                        lstParam.add(userId);
                    }
                }
                if (userType.equals(Constants.ROLES.LEAD_UNIT)) {
                    if (deptId != null) {
                        condition += " and p.receiveGroupId = ?"
                                + " and ( p.receiveUserId = null or p.receiveUserId = ?) ";
                        lstParam.add(deptId);
                        lstParam.add(userId);
                    }
                }
                if (userType.equals(Constants.ROLES.CLERICAL_ROLE)) {
                    if (deptId != null) {
                        //                    hql += "AND f.agencyId = ?)";
                        //                    lstParam.add(deptId);
                        condition += " and p.receiveGroupId = ? ";
                        lstParam.add(deptId);
                    }
                }
            }
            // Nh sn xut
            if (form.getManufactureName() != null && form.getManufactureName().length() > 0) {
                String str = ", Announcement ann";
                if (!str.contains(hql)) {
                    hql += ", Announcement ann";
                }
                condition += " and f.announcementId = ann.announcementId and lower(ann.manufactureName) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(form.getManufactureName().toLowerCase().trim()));
            }
            // S chng nhn cng b
            if (form.getAnnouncementNo() != null && form.getAnnouncementNo().length() > 0) {
                String str = ", AnnouncementReceiptPaper arp";
                if (!str.contains(hql)) {
                    hql += ", AnnouncementReceiptPaper arp";
                    condition += " and f.announcementReceiptPaperId = arp.announcementReceiptPaperId";
                }
                condition += " and lower(arp.receiptNo) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(form.getAnnouncementNo().toLowerCase().trim()));
            }
            // ngay cap
            if (form.getReceiptDate() != null) {
                String str = ", AnnouncementReceiptPaper arp";
                if (!str.contains(hql)) {
                    hql += ", AnnouncementReceiptPaper arp";
                    condition += " and f.announcementReceiptPaperId = arp.announcementReceiptPaperId";
                }
                condition += " and lower(arp.receiptDate) = ?";
                lstParam.add(form.getReceiptDate());
            }
            //thng nhn chu trch nhim
            if (form.getOrigin() != null && form.getOrigin().length() > 0) {
                String str = ", DetailProduct d";
                if (!hql.contains(str)) {
                    hql += ", DetailProduct d";
                    condition += " and f.detailProductId = d.detailProductId";
                }
                condition += " and lower(d.origin) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(form.getOrigin().toLowerCase().trim()));
            }
            // in thoi doanh nghip
            if (form.getBusinessTelephone() != null && form.getBusinessTelephone().length() > 0) {
                String str = ", Announcement ann";
                if (!hql.contains(str)) {
                    hql += ", Announcement ann";
                    condition += " and f.announcementId = ann.announcementId";
                }
                condition += " and lower(ann.businessTelephone) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(form.getBusinessTelephone().toLowerCase().trim()));
            }
            // fax doanh nghip
            if (form.getBusinessFax() != null && form.getBusinessFax().length() > 0) {
                String str = ", Announcement ann";
                if (!hql.contains(str)) {
                    hql += ", Announcement ann";
                    condition += " and f.announcementId = ann.announcementId";
                }
                condition += " and lower(ann.businessFax) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(form.getBusinessFax().toLowerCase().trim()));
            }
            // m h s
            if (form.getFileCode() != null && !"".equals(form.getFileCode().trim())) {
                condition += " AND lower(f.fileCode) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(form.getFileCode().toLowerCase().trim()));
            }
            if (form.getFileType() != null && form.getFileType().longValue() != -1) {
                condition += " AND f.fileType = ?";
                lstParam.add(form.getFileType());
            }
            if (form.getSignDate() != null) {
                condition += " AND f.signDate = ?";
                lstParam.add(form.getSignDate());
            }
            // Su dung chung - DuND
            // Haitv21 thm ch tiu thng tin tm kim
            // Do hieptq reset li params nn on code ny phi di cc case if_ else bn trn
            if (form.getSendDateFrom() != null) {
                condition += " AND f.sendDate >= ?";
                lstParam.add(minDayToCompare(form.getSendDateFrom()));
            }
            if (form.getSendDateTo() != null) {
                condition += " AND f.sendDate <= ?";
                lstParam.add(maxDayToCompare(form.getSendDateTo()));
            }

            // hieptq update 300115
            if (form.getSignDateFrom() != null) {
                condition += " AND f.signDate >= ? ";
                lstParam.add(minDayToCompare(form.getSignDateFrom()));
            }
            if (form.getSignDateTo() != null) {
                condition += " AND f.signDate <= ? ";
                lstParam.add(maxDayToCompare(form.getSignDateTo()));
            }
            if (form.getSignDateFrom() != null) {
                condition += " AND f.status = ? ";
                lstParam.add(22l);
            }

            // ngy thm kho lu tr
            if (form.getRepDateFrom() != null) {
                condition += " AND f.repDate >= ?";
                lstParam.add(minDayToCompare(form.getRepDateFrom()));
            }
            if (form.getRepDateTo() != null) {
                condition += " AND f.repDate <= ?";
                lstParam.add(maxDayToCompare(form.getRepDateTo()));
            }

            // Ngy ph duyt t ngy x ti ngy x
            if (form.getApproveDateFrom() != null) {
                condition += " AND f.approveDate >= ?";
                lstParam.add(minDayToCompare(form.getApproveDateFrom()));
            }
            if (form.getApproveDateTo() != null) {
                condition += " AND f.approveDate <= ?";
                lstParam.add(maxDayToCompare(form.getApproveDateTo()));
            }
            // Ng?i ph duyt
            if (form.getLeaderStaffSignName() != null && form.getLeaderStaffSignName().length() > 0) {
                condition += " AND lower(f.leaderStaffSignName) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(form.getLeaderStaffSignName().toLowerCase().trim()));
            }
            // Tn doanh nghip
            if (form.getBusinessName() != null && form.getBusinessName().length() > 0) {
                condition += " AND lower(f.businessName) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(form.getBusinessName().toLowerCase().trim()));
            }
            // Xut x
            if (form.getNationName() != null && form.getNationName().length() > 0) {
                condition += " AND lower(f.nationName) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(form.getNationName().toLowerCase().trim()));
            }
            // Ng?i thm nh
            if (form.getReceiveUser() != null && form.getReceiveUser().length() > 0) {
                condition += " AND lower(p.receiveUser) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(form.getReceiveUser().toLowerCase().trim()));
            }
            // S ng k kinh doanh
            if (form.getBusinessLicence() != null && form.getBusinessLicence().length() > 0) {
                condition += " AND lower(b.businessLicense) like ? ESCAPE '/' ";
                lstParam.add(StringUtils.toLikeString(form.getBusinessLicence().toLowerCase().trim()));
            }
            // ?a ch doanh nghip
            if (form.getBusinessAddress() != null && form.getBusinessAddress().length() > 0) {
                condition += " AND lower(f.businessAddress) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(form.getBusinessAddress().toLowerCase().trim()));
            }
            // Tn sn phm
            if (form.getProductName() != null && form.getProductName().length() > 0) {
                condition += " AND lower(f.productName) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(form.getProductName().toLowerCase().trim()));
            }
            // Nhm sn phm
            if (form.getProductType() != null && form.getProductType() != -1l) {
                condition += " AND d.productType = ?";
                lstParam.add(form.getProductType());
            }
            // Lnh o phng
            if (form.getLeaderStaff() != null && form.getLeaderStaff().length() > 0) {
                condition += " AND lower(p.receiveUser) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(form.getLeaderStaff().toLowerCase().trim()));
            }
            // Ng?i thm xt
            if (form.getStaff() != null && form.getStaff().trim().length() > 0) {
                condition += " AND lower(f.nameStaffProcess) like ? ESCAPE '/' ";
                lstParam.add(StringUtils.toLikeString(form.getStaff().toLowerCase().trim()));
            }
            if (form.getNameStaffProcess() != null && form.getNameStaffProcess().length() > 0) {
                condition += " AND lower(f.nameStaffProcess) like ? ESCAPE '/' ";
                lstParam.add(StringUtils.toLikeString(form.getNameStaffProcess().toLowerCase().trim()));
            }
            if (form.getLeaderApproveName() != null && form.getLeaderApproveName().length() > 0) {
                condition += " AND lower(f.leaderApproveName) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(form.getLeaderApproveName().toLowerCase().trim()));
            }
            if (form.getLeaderAssignName() != null && form.getLeaderAssignName().length() > 0) {
                condition += " AND lower(f.leaderAssignName) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(form.getLeaderAssignName().toLowerCase().trim()));
            }
            if (form.getLeaderReviewName() != null && form.getLeaderReviewName().length() > 0) {
                condition += " AND lower(f.leaderReviewName) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(form.getLeaderReviewName().toLowerCase().trim()));
            }
            // Tnh -  Thnh Ph
            if (form.getProductType() != null && form.getBusinessProvinceId() != -1l) {
                condition += " AND b.businessProvinceId = ? ";
                lstParam.add(form.getBusinessProvinceId());
            }
            // Ni lu tr
            if (form.getRepositoriesId() != null && form.getRepositoriesId() != -1l) {
                condition += " AND (f.repositoriesId = ? ) ";
                lstParam.add(form.getRepositoriesId());
            }
            // L?c theo ng?i to ( lu tr h s giy )
            if (form.getRepCreator() != null && (form.getSearchType() == 0 || form.getSearchType() == 2)) {
                //condition += " AND (f.fileId in (select p.objectId  from Process p where p.processStatus = 3 and p.receiveUserId = ?)) ";
                //                condition += " AND (p.processStatus = 3 and p.receiveUserId = ?) ";
                //                lstParam.add(form.getRepCreator());
            }
            // Kho lu tr
            if (form.getRepName() != null && form.getRepName() != -1l) {
                condition += " AND (f.repositoriesId = ? )";
                lstParam.add(form.getRepName());
            }
            // Trng thi lu tr
            // ? lu tr
            if (form.getRepStatus() != null && form.getRepStatus() == 1) {
                condition += " AND (f.repositoriesId <> null )";
            }
            // Cha lu tr
            if (form.getRepStatus() != null && form.getRepStatus() == 2) {
                condition += " AND (f.repositoriesId = null )";
            }
            //lnh o phn cng - 140915 binhnt53
            if (form.getLeaderAssignId() != null && form.getLeaderAssignId() > 0L) {
                condition += " AND (f.leaderAssignId = ? )";
                lstParam.add(form.getLeaderAssignId());
            }
            // tra cuc van thu ngay tiep nhan tu ngay den ngay
            if (form.getReceivedDate() != null) {
                condition += " and f.receivedDate >= ?";
                lstParam.add(minDayToCompare(form.getReceivedDate()));
            }
            if (form.getReceivedDateTo() != null) {
                condition += " and f.receivedDate <= ?";
                lstParam.add(maxDayToCompare(form.getReceivedDateTo()));
            }
            if (form.getReceiveNo() != null && !"".equals(form.getReceiveNo())
                    && !"".equals(form.getReceiveNo().trim())) {
                condition += " and f.receiveNo like ?";
                lstParam.add(form.getReceiveNo().trim());
            }
            if (form.getIs30() != null && form.getIs30() != -1l) {
                if (form.getIs30() == 1) {
                    condition += " AND (f.is30 = 1 )";
                } else {
                    condition += " AND (f.is30 = null )";
                }
            }
            if (form.getSearchFullText() != null && form.getSearchFullText().trim().length() > 0) {
                condition += " and f.fileId in (select ffs.fileId from FileForSearch ffs where lower(ffs.content) like ? ESCAPE '/')";
                lstParam.add(StringUtils.toLikeString(form.getSearchFullText()));
            }
        }
        // loc file_id nhom san pham khac
        // hieptq update 251214
        if (form.getProductTypeNew() != null && form.getProductTypeNew().longValue() != -1) {
            String str = ", Category c";
            if (!hql.contains(str)) {
                hql += ", Category c";
                condition += " and  f.productTypeId = c.categoryId";
            }
            if (form.getProductTypeNew() == 1) {
                condition += " AND (c.code <> ? and c.code <> ?) ";
                lstParam.add(Constants.CATEGORY_TYPE.TPCN);
                lstParam.add(Constants.CATEGORY_TYPE.DBT);
            } else {
                condition += " AND (c.code = ? or c.code = ?) ";
                lstParam.add(Constants.CATEGORY_TYPE.TPCN);
                lstParam.add(Constants.CATEGORY_TYPE.DBT);
            }
        }
        //hieptq update 251114
        if (form.getSearchTypeNew() != null) {
            if (form.getSearchTypeNew() == 1) {
                condition += " AND (c.code <> ? and c.code <> ?) ";
                lstParam.add(Constants.CATEGORY_TYPE.TPCN);
                lstParam.add(Constants.CATEGORY_TYPE.DBT);
            } else {
                condition += " AND (c.code = ? or c.code = ?) ";
                lstParam.add(Constants.CATEGORY_TYPE.TPCN);
                lstParam.add(Constants.CATEGORY_TYPE.DBT);
            }
        }
        //!hieptq update
        Query countQuery = getSession()
                .createQuery("select count(distinct f.fileId) " + hql + " where 1=1 " + condition);
        String finalSql = "select distinct f " + hql + " where 1=1 " + condition + " order by ";
        String orderBy = "";
        if (form.getOrderBy() != null) {
            switch (form.getOrderBy()) {
            case 1:
                orderBy = "f.modifyDate DESC";
                break;
            case 2:
                orderBy = "f.modifyDate ASC";
                break;
            default:
                orderBy = "f.modifyDate DESC";
            }
            finalSql += orderBy;
        } else if (sortCustom.isEmpty()) {
            finalSql += "f.modifyDate DESC";
        } else {
            finalSql += sortCustom;
        }

        Query query = getSession().createQuery(finalSql);

        for (int i = 0; i < lstParam.size(); i++) {
            query.setParameter(i, lstParam.get(i));
            countQuery.setParameter(i, lstParam.get(i));
        }

        int total = Integer.parseInt(countQuery.uniqueResult().toString());
        query.setFirstResult(start);
        if (count > 0) {
            query.setMaxResults(count);
        } else {
            query.setMaxResults(total);
        }
        List<FilesNoClob> lstResult = query.list();

        return new GridResult(total, lstResult);
    }

    /**
     * Tm h s ca doanh nghip
     *
     * @param form
     * @param start
     * @param count
     * @param sortField
     * @return
     */
    public GridResult searchBusinessFiles(FilesForm form, int start, int count, String sortField, Long deptId) {
        String hql = " from FilesNoClob f where f.isActive=1 and (f.isTemp = null or f.isTemp = 0 )";
        List lstParam = new ArrayList();
        if (form != null) {
            if (form.getStatus() != null && form.getStatus() != -1L) {
                // ho so da doi chieu
                if (form.getStatus().equals(Constants.FILE_STATUS.COMPARED)) {
                    hql += " AND ( f.status = ? or f.status = ? )";
                    lstParam.add(Constants.FILE_STATUS.COMPARED);
                    lstParam.add(Constants.FILE_STATUS.COMPARED_FAIL);
                } else if (form.getStatus().equals(Constants.FILE_STATUS.EVALUATED_TO_ADD)) {
                    hql += " AND ( f.status = ? or f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.EVALUATED_TO_ADD);
                    lstParam.add(Constants.FILE_STATUS.RECEIVED_REJECT);
                } else if (form.getStatus().equals(Constants.FILE_STATUS.NEW)) {
                    hql += " AND ( f.status = ? or f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.NEW);
                    lstParam.add(Constants.FILE_STATUS.NEW_TO_ADD);
                } else if (form.getStatus() != null) {
                    hql += " AND ( f.status = ?)";
                    lstParam.add(form.getStatus());
                }
            }
            if (form.getSearchType() != null) {
                switch (Integer.parseInt(form.getSearchType().toString())) {
                case -77:
                    lstParam.clear();
                    hql = "from FilesNoClob f" + " where f.isActive = 1" + " AND (f.isTemp = null or f.isTemp = 0 )"
                            + " AND f.deptId = ? " + " AND f.isFee = ? ";
                    lstParam.add(deptId);
                    lstParam.add(Constants.FEE_STATUS.DA_TU_CHOI);
                    break;
                case -2:// binhnt53 update 150209
                    hql = "from FilesNoClob f where f.isActive = 1 and (f.isTemp = null or f.isTemp = 0 ) and (sysdate - 366  <= f.createDate) ";
                    hql += " and f.status in (?,?,?,?,?,?,?,?,?,?,?)" + " and f.deptId = ? ";
                    lstParam.clear();
                    lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                    lstParam.add(Constants.FILE_STATUS.EVALUATED);
                    lstParam.add(Constants.FILE_STATUS.REVIEWED);
                    lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_ADD);
                    lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_EVALUATE);
                    lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_REVIEW);
                    lstParam.add(Constants.FILE_STATUS.REVIEWED_TO_ADD);
                    lstParam.add(Constants.FILE_STATUS.COMPARED_FAIL);
                    lstParam.add(Constants.FILE_STATUS.REVIEW_COMPARISON);
                    lstParam.add(Constants.FILE_STATUS.REVIEW_COMPARISON_FAIL);
                    lstParam.add(Constants.FILE_STATUS.REVIEW_TO_ADD);
                    //lstParam.add(Constants.FILE_STATUS.NEW_TO_ADD);
                    //lstParam.add(Constants.FILE_STATUS.RECEIVED);
                    //lstParam.add(Constants.FILE_STATUS.COMPARED);
                    lstParam.add(deptId);
                    break;
                case -5:
                    hql = " from FilesNoClob f, Fee fe, FeePaymentInfo fpi where f.fileId = fpi.fileId and (f.isTemp=null or f.isTemp=0) and f.isActive=1"
                            + " and fe.feeId = fpi.feeId and fe.feeType=1 and fe.isActive=1" + " and fpi.isActive=1"
                            + " and (fpi.status=0)"
                            // + " and f.userSigned is not null"
                            + " and (f.status=?) and f.deptId =" + deptId + "";
                    lstParam.clear();
                    lstParam.add(Constants.FILE_STATUS.APPROVED);
                    break;
                case -4://3- H s ch? tip nhn =
                    //  + Mi np + ch? xc nhn vn th (files.status = 1, fee_payment_info.status = 3 or 4)
                    //  + Mi np S?BS + ch? xc nhn vn th(files.status = 18, fee_payment_info.status = 3 or 4)
                    hql = " from FilesNoClob f, Fee fe, FeePaymentInfo fpi " + " where fpi.fileId = f.fileId "
                            + " and f.isActive = 1 " + " and f.userSigned is not null"
                            + " and (f.isTemp is null or f.isTemp = 0) and f.isFee = 1"
                            //                                + " and fe.feeId = fpi.feeId and fe.feeType=2 and fe.isActive=1"
                            + " and (f.status = ?) " + " and fpi.isActive = 1" + " and f.deptId = ?";
                    lstParam.clear();
                    lstParam.add(Constants.FILE_STATUS.NEW);
                    //lstParam.add(Constants.FILE_STATUS.NEW_TO_ADD);
                    lstParam.add(deptId);
                    break;

                // ho so cho tiep nhan SDBS
                case -7:
                    hql = " from FilesNoClob f, Fee fe, FeePaymentInfo fpi " + " where fpi.fileId = f.fileId "
                            + " and f.isActive = 1 " + " and f.userSigned is not null"
                            + " and (f.isTemp is null or f.isTemp = 0) and f.isFee = 1"
                            //                                + " and fe.feeId = fpi.feeId and fe.feeType=2 and fe.isActive=1"
                            + " and (f.status = ?) " + " and fpi.isActive = 1" + " and f.deptId = ?";
                    lstParam.clear();
                    //lstParam.add(Constants.FILE_STATUS.NEW);
                    lstParam.add(Constants.FILE_STATUS.NEW_TO_ADD);
                    lstParam.add(deptId);
                    break;

                // ho so cho ke toan xac nhan nop phi
                case -100:
                    hql = " from FilesNoClob f, Fee fe, FeePaymentInfo fpi where f.fileId = fpi.fileId  and (f.isTemp=null or f.isTemp=0) and f.isActive=1"
                            + " and fe.feeId = fpi.feeId and fe.feeType=2 and fe.isActive=1" + " and fpi.isActive=1"
                            + " and fpi.status not in (2,1)"//u150211 by binhnt //+ " and fpi.status <> 1"
                            + " and f.userSigned is not null" + " and f.status=?" + " and f.deptId = ? ";
                    lstParam.clear();
                    lstParam.add(Constants.FILE_STATUS.NEW);
                    lstParam.add(deptId);
                    break;
                // hieptq update 161214 ho so cho ke toan xac nhan le phi cap so
                case -109:
                    hql = " from FilesNoClob f, Fee fe, FeePaymentInfo fpi where f.fileId = fpi.fileId and (f.isTemp=null or f.isTemp=0) and f.isActive=1"
                            + " and fe.feeId = fpi.feeId and fe.feeType=1 and fe.isActive=1" + " and fpi.isActive=1"
                            + " and (fpi.status > 1)" + " and f.userSigned is not null" + " and (f.status=?) ";
                    lstParam.clear();
                    lstParam.add(Constants.FILE_STATUS.APPROVED);
                    //lstParam.add(deptId);
                    break;
                case -3://2- H s cha np ph  = Mi to, cha np ph,  k or upload file k(files.status = 0, fee_payment_info.status = 0, files.user_signed is
                    hql = " from FilesNoClob f, Fee fe, FeePaymentInfo fpi"
                            + " where  f.fileId = fpi.fileId and f.isActive = 1 and (f.isTemp = null or f.isTemp = 0)"
                            + " and fe.feeId = fpi.feeId and fe.feeType=2 and fe.isActive=1" + " and fpi.isActive=1"
                            + " and f.userSigned is not null" + " and f.status=?";
                    lstParam.clear();
                    lstParam.add(form.getStatus());
                    break;
                case 1:
                    hql = "from FilesNoClob f" + " where f.isActive = 1" + " and (f.isTemp = null or f.isTemp = 0 )"
                            + " and (sysdate - 366  <= f.createDate) ";
                    hql += " AND f.status = ? ";
                    lstParam.clear();
                    lstParam.add(form.getStatus());
                    break;
                case 33:
                    hql = "from FilesNoClob f" + " where f.isActive = 1" + " and (f.isTemp = null or f.isTemp = 0 )"
                            + " and (sysdate - 366  <= f.createDate) ";
                    hql += " AND (f.status = ? or f.status = ?)";
                    lstParam.clear();
                    lstParam.add(Constants.FILE_STATUS.RECEIVED_REJECT);
                    lstParam.add(Constants.FILE_STATUS.RECEIVED_REJECT_TO_ADD);
                    break;
                case 2:
                    hql = "from FilesNoClob f" + " where f.isActive = 1" + " and (f.isTemp = null or f.isTemp = 0 )"
                            + " and (sysdate - 366  <= f.createDate) ";
                    hql += " AND f.status = ? ";
                    lstParam.clear();
                    lstParam.add(form.getStatus());
                    break;
                case 3:
                    hql = "from FilesNoClob f" + " where f.isActive = 1" + " and (f.isTemp = null or f.isTemp = 0 )"
                            + " and (sysdate - 366  <= f.createDate) ";
                    hql += " AND f.status = ? ";
                    lstParam.clear();
                    lstParam.add(form.getStatus());
                    break;
                case 4:
                    hql = "from FilesNoClob f" + " where f.isActive = 1" + " and (f.isTemp = null or f.isTemp = 0 )"
                            + " and (sysdate - 366  <= f.createDate) ";
                    hql += " AND f.status = ? ";
                    lstParam.clear();
                    lstParam.add(form.getStatus());
                    break;
                case 5:
                    hql = "from FilesNoClob f" + " where f.isActive = 1" + " and (f.isTemp = null or f.isTemp = 0 )"
                            + " and (sysdate - 366  <= f.createDate) " + " and f.userSigned = null"
                            + " AND f.status = ? ";
                    lstParam.clear();
                    lstParam.add(form.getStatus());
                    break;
                case 55:
                    hql = "from FilesNoClob f" + " where f.isActive = 1" + " and (f.isTemp = null or f.isTemp = 0 )"
                            + " and (sysdate - 366  <= f.createDate) " + " and f.userSigned = null"
                            + " AND (f.status = ? or f.status = ? )";
                    lstParam.clear();
                    lstParam.add(Constants.FILE_STATUS.NEW_CREATE);
                    lstParam.add(Constants.FILE_STATUS.NEW);
                    break;
                case 6:
                    hql = "from FilesNoClob f" + " where f.isActive = 1" + " and (f.isTemp = null or f.isTemp = 0 )"
                            + " and (sysdate - 366  <= f.createDate) " + " and f.isFee = 1 and f.isSignPdf = 2"
                            + " AND f.status = ? ";
                    lstParam.clear();
                    lstParam.add(form.getStatus());
                    break;

                ///H s vn th  tr bn cng b (bn m?m) =  ho so da ky so + dong tien + dong dau so - binhnt53 150202
                case 76:
                    hql = "from FilesNoClob f" + " where f.isActive = 1 AND (f.isTemp = null OR f.isTemp = 0 )"
                            + " AND f.isFee = 1 AND f.isSignPdf = 2" + " AND f.deptId = ?"
                            + " AND f.status in (?,?,?,?,?,?,?)";
                    lstParam.clear();
                    lstParam.add(deptId);
                    lstParam.add(Constants.FILE_STATUS.APPROVED);
                    lstParam.add(Constants.FILE_STATUS.GIVE_BACK);
                    lstParam.add(Constants.FILE_STATUS.ALERT_COMPARISON);
                    lstParam.add(Constants.FILE_STATUS.COMPARED);
                    lstParam.add(Constants.FILE_STATUS.REVIEW_COMPARISON);
                    lstParam.add(Constants.FILE_STATUS.REVIEW_COMPARISON_FAIL);
                    lstParam.add(Constants.FILE_STATUS.COMPARED_FAIL);
                    break;
                default:
                    //                        hql += " AND f.status = ? ";
                    //                        lstParam.add(form.getStatus());
                }
            }
        }
        if (form.getFileCode() != null && !"".equals(form.getFileCode().trim())) {
            hql += " AND lower(f.fileCode) like ? ESCAPE '/' ";
            lstParam.add(StringUtils.toLikeString(form.getFileCode().toLowerCase().trim()));
        }
        if (form.getFileType() != null && form.getFileType().longValue() != -1) {
            hql += " AND f.fileType = ? ";
            lstParam.add(form.getFileType());
        }
        if (form.getDeptId() != null) {
            hql += " AND f.deptId = ? ";
            lstParam.add(form.getDeptId());
        }
        if (form.getSendDateFrom() != null) {
            hql += " AND f.sendDate >= ? ";
            lstParam.add(form.getSendDateFrom());
        }
        if (form.getSendDateTo() != null) {
            hql += " AND f.sendDate <= ? ";
            lstParam.add(addOneDay(form.getSendDateTo()));
        }

        if (form.getApproveDate() != null) {
            hql += " AND f.approveDate = ? ";
            lstParam.add(form.getApproveDate());
        }
        if (form.getProductName() != null && !"".equals(form.getProductName().trim())) {
            hql += " AND lower(f.productName) like ? ESCAPE '/' ";
            lstParam.add(StringUtils.toLikeString(form.getProductName().toLowerCase().trim()));
        }

        Query countQuery = getSession().createQuery("select count(distinct f.fileId) " + hql);
        Query query = getSession().createQuery("select distinct f " + hql + " order by f.modifyDate DESC");
        for (int i = 0; i < lstParam.size(); i++) {
            query.setParameter(i, lstParam.get(i));
            countQuery.setParameter(i, lstParam.get(i));
        }
        query.setFirstResult(start);
        query.setMaxResults(count);
        int total = Integer.parseInt(countQuery.uniqueResult().toString());
        List lstResult = query.list();
        GridResult gr = new GridResult(total, lstResult);
        return gr;
    }

    public GridResult searchBusinessFilesNoSDBSAnd4Star(FilesForm form, int start, int count, String sortField,
            Long deptId) {
        String hql = " from FilesNoClob f, Procedure p where f.isActive=1 and (f.isTemp = null or f.isTemp = 0 ) and f.fileType = p.procedureId and p.description!='announcementFile05' and p.description!='announcement4star' ";
        List lstParam = new ArrayList();
        if (form != null) {
            if (form.getStatus() != null && form.getStatus() != -1L) {
                // ho so da doi chieu
                if (form.getStatus().equals(Constants.FILE_STATUS.COMPARED)) {
                    hql += " AND ( f.status = ? or f.status = ? )";
                    lstParam.add(Constants.FILE_STATUS.COMPARED);
                    lstParam.add(Constants.FILE_STATUS.COMPARED_FAIL);
                } else if (form.getStatus().equals(Constants.FILE_STATUS.EVALUATED_TO_ADD)) {
                    hql += " AND ( f.status = ? or f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.EVALUATED_TO_ADD);
                    lstParam.add(Constants.FILE_STATUS.RECEIVED_REJECT);
                } else if (form.getStatus().equals(Constants.FILE_STATUS.NEW)) {
                    hql += " AND ( f.status = ? or f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.NEW);
                    lstParam.add(Constants.FILE_STATUS.NEW_TO_ADD);
                } else if (form.getStatus() != null) {
                    hql += " AND ( f.status = ?)";
                    lstParam.add(form.getStatus());
                }
            }
            if (form.getSearchType() != null) {
                switch (Integer.parseInt(form.getSearchType().toString())) {
                case -77:
                    lstParam.clear();
                    hql = "from FilesNoClob f" + " where f.isActive = 1" + " AND (f.isTemp = null or f.isTemp = 0 )"
                            + " AND f.deptId = ? " + " AND f.isFee = ? ";
                    lstParam.add(deptId);
                    lstParam.add(Constants.FEE_STATUS.DA_TU_CHOI);
                    break;
                case -2:// binhnt53 update 150209
                    hql = "from FilesNoClob f where f.isActive = 1 and (f.isTemp = null or f.isTemp = 0 ) and (sysdate - 366  <= f.createDate) ";
                    hql += " and f.status in (?,?,?,?,?,?,?,?,?,?,?)" + " and f.deptId = ? ";
                    lstParam.clear();
                    lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                    lstParam.add(Constants.FILE_STATUS.EVALUATED);
                    lstParam.add(Constants.FILE_STATUS.REVIEWED);
                    lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_ADD);
                    lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_EVALUATE);
                    lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_REVIEW);
                    lstParam.add(Constants.FILE_STATUS.REVIEWED_TO_ADD);
                    lstParam.add(Constants.FILE_STATUS.COMPARED_FAIL);
                    lstParam.add(Constants.FILE_STATUS.REVIEW_COMPARISON);
                    lstParam.add(Constants.FILE_STATUS.REVIEW_COMPARISON_FAIL);
                    lstParam.add(Constants.FILE_STATUS.REVIEW_TO_ADD);
                    //lstParam.add(Constants.FILE_STATUS.NEW_TO_ADD);
                    //lstParam.add(Constants.FILE_STATUS.RECEIVED);
                    //lstParam.add(Constants.FILE_STATUS.COMPARED);
                    lstParam.add(deptId);
                    break;
                case -5:
                    hql = " from FilesNoClob f, Fee fe, FeePaymentInfo fpi where f.fileId = fpi.fileId and (f.isTemp=null or f.isTemp=0) and f.isActive=1"
                            + " and fe.feeId = fpi.feeId and fe.feeType=1 and fe.isActive=1" + " and fpi.isActive=1"
                            + " and (fpi.status=0)"
                            // + " and f.userSigned is not null"
                            + " and (f.status=?) and f.deptId =" + deptId + "";
                    lstParam.clear();
                    lstParam.add(Constants.FILE_STATUS.APPROVED);
                    break;
                case -4://3- H s ch? tip nhn =
                    //  + Mi np + ch? xc nhn vn th (files.status = 1, fee_payment_info.status = 3 or 4)
                    //  + Mi np S?BS + ch? xc nhn vn th(files.status = 18, fee_payment_info.status = 3 or 4)
                    hql = " from FilesNoClob f, Fee fe, FeePaymentInfo fpi " + " where fpi.fileId = f.fileId "
                            + " and f.isActive = 1 " + " and f.userSigned is not null"
                            + " and (f.isTemp is null or f.isTemp = 0) and f.isFee = 1"
                            //                                + " and fe.feeId = fpi.feeId and fe.feeType=2 and fe.isActive=1"
                            + " and (f.status = ?) " + " and fpi.isActive = 1" + " and f.deptId = ?";
                    lstParam.clear();
                    lstParam.add(Constants.FILE_STATUS.NEW);
                    //lstParam.add(Constants.FILE_STATUS.NEW_TO_ADD);
                    lstParam.add(deptId);
                    break;

                // ho so cho tiep nhan SDBS
                case -7:
                    hql = " from FilesNoClob f, Fee fe, FeePaymentInfo fpi " + " where fpi.fileId = f.fileId "
                            + " and f.isActive = 1 " + " and f.userSigned is not null"
                            + " and (f.isTemp is null or f.isTemp = 0) and f.isFee = 1"
                            //                                + " and fe.feeId = fpi.feeId and fe.feeType=2 and fe.isActive=1"
                            + " and (f.status = ?) " + " and fpi.isActive = 1" + " and f.deptId = ?";
                    lstParam.clear();
                    //lstParam.add(Constants.FILE_STATUS.NEW);
                    lstParam.add(Constants.FILE_STATUS.NEW_TO_ADD);
                    lstParam.add(deptId);
                    break;

                // ho so cho ke toan xac nhan nop phi
                case -100:
                    hql = " from FilesNoClob f, Fee fe, FeePaymentInfo fpi where f.fileId = fpi.fileId  and (f.isTemp=null or f.isTemp=0) and f.isActive=1"
                            + " and fe.feeId = fpi.feeId and fe.feeType=2 and fe.isActive=1" + " and fpi.isActive=1"
                            + " and fpi.status not in (2,1)"//u150211 by binhnt //+ " and fpi.status <> 1"
                            + " and f.userSigned is not null" + " and f.status=?" + " and f.deptId = ? ";
                    lstParam.clear();
                    lstParam.add(Constants.FILE_STATUS.NEW);
                    lstParam.add(deptId);
                    break;
                // hieptq update 161214 ho so cho ke toan xac nhan le phi cap so
                case -109:
                    hql = " from FilesNoClob f, Fee fe, FeePaymentInfo fpi where f.fileId = fpi.fileId and (f.isTemp=null or f.isTemp=0) and f.isActive=1"
                            + " and fe.feeId = fpi.feeId and fe.feeType=1 and fe.isActive=1" + " and fpi.isActive=1"
                            + " and (fpi.status > 1)" + " and f.userSigned is not null" + " and (f.status=?) ";
                    lstParam.clear();
                    lstParam.add(Constants.FILE_STATUS.APPROVED);
                    //lstParam.add(deptId);
                    break;
                case -3://2- H s cha np ph  = Mi to, cha np ph,  k or upload file k(files.status = 0, fee_payment_info.status = 0, files.user_signed is
                    hql = " from FilesNoClob f, Fee fe, FeePaymentInfo fpi"
                            + " where  f.fileId = fpi.fileId and f.isActive = 1 and (f.isTemp = null or f.isTemp = 0)"
                            + " and fe.feeId = fpi.feeId and fe.feeType=2 and fe.isActive=1" + " and fpi.isActive=1"
                            + " and f.userSigned is not null" + " and f.status=?";
                    lstParam.clear();
                    lstParam.add(form.getStatus());
                    break;
                case 1:
                    hql = "from FilesNoClob f" + " where f.isActive = 1" + " and (f.isTemp = null or f.isTemp = 0 )"
                            + " and (sysdate - 366  <= f.createDate) ";
                    hql += " AND f.status = ? ";
                    lstParam.clear();
                    lstParam.add(form.getStatus());
                    break;
                case 33:
                    hql = "from FilesNoClob f" + " where f.isActive = 1" + " and (f.isTemp = null or f.isTemp = 0 )"
                            + " and (sysdate - 366  <= f.createDate) ";
                    hql += " AND (f.status = ? or f.status = ?)";
                    lstParam.clear();
                    lstParam.add(Constants.FILE_STATUS.RECEIVED_REJECT);
                    lstParam.add(Constants.FILE_STATUS.RECEIVED_REJECT_TO_ADD);
                    break;
                case 2:
                    hql = "from FilesNoClob f" + " where f.isActive = 1" + " and (f.isTemp = null or f.isTemp = 0 )"
                            + " and (sysdate - 366  <= f.createDate) ";
                    hql += " AND f.status = ? ";
                    lstParam.clear();
                    lstParam.add(form.getStatus());
                    break;
                case 3:
                    hql = "from FilesNoClob f" + " where f.isActive = 1" + " and (f.isTemp = null or f.isTemp = 0 )"
                            + " and (sysdate - 366  <= f.createDate) ";
                    hql += " AND f.status = ? ";
                    lstParam.clear();
                    lstParam.add(form.getStatus());
                    break;
                case 4:
                    hql = "from FilesNoClob f" + " where f.isActive = 1" + " and (f.isTemp = null or f.isTemp = 0 )"
                            + " and (sysdate - 366  <= f.createDate) ";
                    hql += " AND f.status = ? ";
                    lstParam.clear();
                    lstParam.add(form.getStatus());
                    break;
                case 5:
                    hql = "from FilesNoClob f" + " where f.isActive = 1" + " and (f.isTemp = null or f.isTemp = 0 )"
                            + " and (sysdate - 366  <= f.createDate) " + " and f.userSigned = null"
                            + " AND f.status = ? ";
                    lstParam.clear();
                    lstParam.add(form.getStatus());
                    break;
                case 55:
                    hql = "from FilesNoClob f" + " where f.isActive = 1" + " and (f.isTemp = null or f.isTemp = 0 )"
                            + " and (sysdate - 366  <= f.createDate) " + " and f.userSigned = null"
                            + " AND (f.status = ? or f.status = ? )";
                    lstParam.clear();
                    lstParam.add(Constants.FILE_STATUS.NEW_CREATE);
                    lstParam.add(Constants.FILE_STATUS.NEW);
                    break;
                case 6:
                    hql = "from FilesNoClob f" + " where f.isActive = 1" + " and (f.isTemp = null or f.isTemp = 0 )"
                            + " and (sysdate - 366  <= f.createDate) " + " and f.isFee = 1 and f.isSignPdf = 2"
                            + " AND f.status = ? ";
                    lstParam.clear();
                    lstParam.add(form.getStatus());
                    break;

                ///H s vn th  tr bn cng b (bn m?m) =  ho so da ky so + dong tien + dong dau so - binhnt53 150202
                case 76:
                    hql = "from FilesNoClob f" + " where f.isActive = 1 AND (f.isTemp = null OR f.isTemp = 0 )"
                            + " AND f.isFee = 1 AND f.isSignPdf = 2" + " AND f.deptId = ?"
                            + " AND f.status in (?,?,?,?,?,?,?)";
                    lstParam.clear();
                    lstParam.add(deptId);
                    lstParam.add(Constants.FILE_STATUS.APPROVED);
                    lstParam.add(Constants.FILE_STATUS.GIVE_BACK);
                    lstParam.add(Constants.FILE_STATUS.ALERT_COMPARISON);
                    lstParam.add(Constants.FILE_STATUS.COMPARED);
                    lstParam.add(Constants.FILE_STATUS.REVIEW_COMPARISON);
                    lstParam.add(Constants.FILE_STATUS.REVIEW_COMPARISON_FAIL);
                    lstParam.add(Constants.FILE_STATUS.COMPARED_FAIL);
                    break;
                default:
                    //                        hql += " AND f.status = ? ";
                    //                        lstParam.add(form.getStatus());
                }
            }
        }
        if (form.getFileCode() != null && !"".equals(form.getFileCode().trim())) {
            hql += " AND lower(f.fileCode) like ? ESCAPE '/' ";
            lstParam.add(StringUtils.toLikeString(form.getFileCode().toLowerCase().trim()));
        }
        if (form.getFileType() != null && form.getFileType().longValue() != -1) {
            hql += " AND f.fileType = ? ";
            lstParam.add(form.getFileType());
        }
        if (form.getDeptId() != null) {
            hql += " AND f.deptId = ? ";
            lstParam.add(form.getDeptId());
        }
        if (form.getSendDateFrom() != null) {
            hql += " AND f.sendDate >= ? ";
            lstParam.add(form.getSendDateFrom());
        }
        if (form.getSendDateTo() != null) {
            hql += " AND f.sendDate <= ? ";
            lstParam.add(addOneDay(form.getSendDateTo()));
        }

        if (form.getApproveDate() != null) {
            hql += " AND f.approveDate = ? ";
            lstParam.add(form.getApproveDate());
        }
        if (form.getProductName() != null && !"".equals(form.getProductName().trim())) {
            hql += " AND lower(f.productName) like ? ESCAPE '/' ";
            lstParam.add(StringUtils.toLikeString(form.getProductName().toLowerCase().trim()));
        }

        Query countQuery = getSession().createQuery("select count(distinct f.fileId) " + hql);
        Query query = getSession().createQuery("select distinct f " + hql + " order by f.modifyDate DESC");
        for (int i = 0; i < lstParam.size(); i++) {
            query.setParameter(i, lstParam.get(i));
            countQuery.setParameter(i, lstParam.get(i));
        }
        query.setFirstResult(start);
        query.setMaxResults(count);
        int total = Integer.parseInt(countQuery.uniqueResult().toString());
        List lstResult = query.list();
        GridResult gr = new GridResult(total, lstResult);
        return gr;
    }

    /**
     * Hiepvv Tm danh sach h s bo sung sau cong bo cua mot ho so ca doanh
     * nghip
     *
     * @param filesID
     * @param start
     * @param count
     * @param sortField
     * @return
     */
    public GridResult searchListFilesChangesAfterAnnouned(Long fileSourceID, int start, int count, String sortField,
            Long fileType) {
        String hql = " from FilesNoClob f where f.isActive=1 and f.filesSourceID=? and f.fileType = ? ";

        Query countQuery = getSession().createQuery("select count(distinct f.fileId) " + hql);
        Query query = getSession().createQuery("select distinct f " + hql + " order by f.modifyDate DESC");
        if (fileSourceID != null && fileSourceID > 0L) {
            query.setParameter(0, fileSourceID);
            query.setParameter(1, fileType);
            countQuery.setParameter(0, fileSourceID);
            countQuery.setParameter(1, fileType);
        } else {
            return new GridResult(0, new ArrayList());
        }
        query.setFirstResult(start);
        query.setMaxResults(count);
        int total = Integer.parseInt(countQuery.uniqueResult().toString());
        List lstResult = query.list();
        GridResult gr = new GridResult(total, lstResult);
        return gr;
    }

    /**
     * Tim ho so de phan cong tham dinh
     *
     * @param form
     * @param deptId
     * @param userId
     * @param start
     * @param count
     * @param sortField
     * @return
     */
    public GridResult findAllFileForAssignEvaluation(FilesForm form, Long deptId, Long userId, int start, int count,
            String sortField) {
        try {
            String hql = " from FilesNoClob f, Process p, Category c" + " where f.isActive=1"
                    + " and f.fileId = p.objectId" + " and f.productTypeId = c.categoryId" + " and p.objectType = ?"
                    + " and p.isActive = 1" + " and (f.isTemp is null or f.isTemp = 0 )";
            /*
             *Hiepvv 13/01/16
             *Phan cong cong viec cho ho so sua doi sau cong bo
             */
            if (form != null && form.getFileType() != null && form.getFileType().longValue() != -1) {
                ProcedureDAOHE pdaohe = new ProcedureDAOHE();
                Procedure p = pdaohe.getProcedureById(form.getFileType());
                if (p != null && "announcementFile05".equals(p.getDescription())) {
                    hql = " from FilesNoClob f, Process p ,DetailProduct d" + " where f.isActive=1"
                            + " and f.fileId = p.objectId" + " and f.detailProductId = d.detailProductId"
                            + " and p.objectType = ?" + " and p.isActive = 1"
                            + " and (f.isTemp is null or f.isTemp = 0 )";
                }
                if (p != null && "announcement4star".equals(p.getDescription())) {
                    hql = " from FilesNoClob f, Process p,DetailProduct d " + " where f.isActive=1"
                            + " and f.fileId = p.objectId" + " and f.detailProductId = d.detailProductId"
                            + " and p.objectType = ?" + " and p.isActive = 1"
                            + " and (f.isTemp is null or f.isTemp = 0 )";
                }
            }
            List lstParam = new ArrayList();
            lstParam.add(Constants.OBJECT_TYPE.FILES);
            if (form != null) {
                if (form.getStatus() == null) {
                    //                hql += " and (f.status = ? or f.status = ? or f.status = ? or f.status = ?)";
                    //                hql += " and (f.status = ? or f.status = ?)";
                    hql += " and (f.status = ?)";
                    //                lstParam.add(Constants.FILE_STATUS.NEW);
                    lstParam.add(Constants.FILE_STATUS.RECEIVED);
                    //lstParam.add(Constants.FILE_STATUS.PROPOSED);
                    //lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                } else {
                    hql += " and (f.status = ?)";
                    //                hql += " and (f.status = ? or f.status = ?)";
                    //                lstParam.add(Constants.FILE_STATUS.NEW);
                    lstParam.add(form.getStatus());
                    //                lstParam.add(Constants.FILE_STATUS.PROPOSED);
                    //lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                }
                if (form.getBusinessName() != null && !"".equals(form.getBusinessName().trim())) {
                    hql += " AND lower(f.businessName) like ? ESCAPE '/' ";
                    lstParam.add(StringUtils.toLikeString(form.getBusinessName().toLowerCase().trim()));
                }

                if (form.getProductName() != null && form.getProductName().length() > 0) {
                    hql += " AND lower(f.productName) like ? ESCAPE '/'";
                    lstParam.add(StringUtils.toLikeString(form.getProductName().toLowerCase().trim()));
                }
                if (form.getFileCode() != null && !"".equals(form.getFileCode().trim())) {
                    hql += "AND lower(f.fileCode) like ? ESCAPE '/' ";
                    lstParam.add(StringUtils.toLikeString(form.getFileCode().toLowerCase().trim()));
                }
                if (form.getFileType() != null && form.getFileType().longValue() != -1) {
                    hql += "AND f.fileType = ? ";
                    lstParam.add(form.getFileType());
                }
                if (form.getSendDateFrom() != null) {
                    hql += "AND f.sendDate >= ? ";
                    lstParam.add(form.getSendDateFrom());
                }
                if (form.getSendDateTo() != null) {
                    hql += "AND f.sendDate <= ? ";
                    lstParam.add(form.getSendDateTo());
                }
                if (deptId != null) {
                    //                hql += "AND (f.agencyId = ? or p.receiveUserId = ? or p.receiveGroupId=? ) and (p.processStatus=? or p.processStatus=? or p.processStatus =? or p.processStatus =? ) ";
                    hql += "AND (f.agencyId = ? or p.receiveUserId = ? or p.receiveGroupId=? ) and (p.processStatus=? or p.processStatus=? or p.processStatus =? ) ";

                    lstParam.add(deptId);
                    lstParam.add(userId);
                    lstParam.add(deptId);
                    lstParam.add(Constants.FILE_STATUS.NEW);
                    lstParam.add(Constants.FILE_STATUS.RECEIVED);
                    lstParam.add(Constants.FILE_STATUS.PROPOSED);
                    //lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                } else {
                    //                hql += "AND (p.receiveUserId = ? and (p.processStatus=? or p.processStatus=? or p.processStatus =? )and p.status=? ) ";
                    hql += "AND (p.receiveUserId = ? and (p.processStatus=?) and p.status=? ) ";
                    lstParam.add(userId);
                    //lstParam.add(Constants.FILE_STATUS.NEW);
                    lstParam.add(Constants.FILE_STATUS.RECEIVED);
                    //lstParam.add(Constants.FILE_STATUS.PROPOSED);
                    lstParam.add(0l);
                }
            }
            // loc file_id nhom san pham khac
            if (form.getProductTypeNew() != null && form.getProductTypeNew().longValue() != -1) {
                if (form.getProductTypeNew() == 1) {
                    hql += " and (c.code <> ? and c.code <> ?)";
                    lstParam.add("TPCN");
                    lstParam.add("DBT");
                } else {
                    hql += " and (c.code = ? or c.code = ?)";
                    lstParam.add("TPCN");
                    lstParam.add("DBT");
                }
            }

            // hieptq update 251114
            if (form.getSearchTypeNew() != null && form.getSearchTypeNew() != -1) {
                if (form.getSearchTypeNew() == 1) {
                    hql += " and (c.code <> ? and c.code <> ?)";
                    lstParam.add("TPCN");
                    lstParam.add("DBT");
                } else {
                    hql += " and (c.code = ? or c.code = ?)";
                    lstParam.add("TPCN");
                    lstParam.add("DBT");
                }
            }

            Query countQuery = getSession().createQuery("select count(distinct f.fileId) " + hql);
            Query query = getSession().createQuery("select distinct f " + hql + "order by f.modifyDate DESC)");
            for (int i = 0; i < lstParam.size(); i++) {
                query.setParameter(i, lstParam.get(i));
                countQuery.setParameter(i, lstParam.get(i));
            }

            query.setFirstResult(start);
            query.setMaxResults(count);
            int total = Integer.parseInt(countQuery.uniqueResult().toString());
            List<FilesNoClob> lstResult = query.list();
            GridResult gr = new GridResult(total, lstResult);
            return gr;
        } catch (Exception ex) {
            LogUtil.addLog(ex);
            return new GridResult(0, null);
        }
    }

    /**
     * Tim ho so de phan cong tham dinh
     *
     * @param form
     * @param deptId
     * @param userId
     * @param start
     * @param count
     * @param sortField
     * @return
     */
    public GridResult findAllFileForReAssignEvaluation(FilesForm form, Long deptId, Long userId, int start,
            int count, String sortField) {
        try {
            String hql = " from FilesNoClob f, Process p, Category c" + " where f.isActive=1"
                    + " and f.fileId = p.objectId" + " and f.productTypeId = c.categoryId" + " and p.objectType = ?"
                    + " and p.isActive = 1" + " and (f.isTemp = null or f.isTemp = 0 ) ";
            List lstParam = new ArrayList();
            lstParam.add(Constants.OBJECT_TYPE.FILES);
            if (form != null) {
                if (form.getStatus() == null) {
                    hql += " and (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                } else {
                    hql += " and (f.status = ?)";
                    lstParam.add(form.getStatus());
                }
                if (form.getBusinessName() != null && !"".equals(form.getBusinessName().trim())) {
                    hql += " AND lower(f.businessName) like ? ESCAPE '/' ";
                    lstParam.add(StringUtils.toLikeString(form.getBusinessName().toLowerCase().trim()));
                }

                if (form.getProductName() != null && form.getProductName().length() > 0) {
                    hql += " AND lower(f.productName) like ? ESCAPE '/'";
                    lstParam.add(StringUtils.toLikeString(form.getProductName().toLowerCase().trim()));
                }
                if (form.getFileCode() != null && !"".equals(form.getFileCode().trim())) {
                    hql += "AND lower(f.fileCode) like ? ESCAPE '/' ";
                    lstParam.add(StringUtils.toLikeString(form.getFileCode().toLowerCase().trim()));
                }
                if (form.getFileType() != null && form.getFileType().longValue() != -1) {
                    hql += "AND f.fileType = ? ";
                    lstParam.add(form.getFileType());
                }
                if (form.getSendDateFrom() != null) {
                    hql += "AND f.sendDate >= ? ";
                    lstParam.add(form.getSendDateFrom());
                }
                if (form.getSendDateTo() != null) {
                    hql += "AND f.sendDate <= ? ";
                    lstParam.add(form.getSendDateTo());
                }
                if (deptId != null) {
                    hql += "AND (f.agencyId = ? or p.receiveUserId = ? or p.receiveGroupId=? ) and (p.processStatus=? ) ";
                    lstParam.add(deptId);
                    lstParam.add(userId);
                    lstParam.add(deptId);
                    lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                } else {
                    hql += "AND (p.receiveUserId = ? and (p.processStatus=?) and p.status=? ) ";
                    lstParam.add(userId);
                    lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                    lstParam.add(0l);
                }
            }
            if (form.getProductTypeNew() != null && form.getProductTypeNew().longValue() != -1) {
                if (form.getProductTypeNew() == 1) {
                    hql += " and (c.code <> ? and c.code <> ?)";
                    lstParam.add("TPCN");
                    lstParam.add("DBT");
                } else {
                    hql += " and (c.code = ? or c.code = ?)";
                    lstParam.add("TPCN");
                    lstParam.add("DBT");
                }
            }
            if (form.getSearchTypeNew() != null) {
                if (form.getSearchTypeNew() == 1) {
                    hql += " and (c.code <> ? and c.code <> ?)";
                    lstParam.add("TPCN");
                    lstParam.add("DBT");
                } else {
                    hql += " and (c.code = ? or c.code = ?)";
                    lstParam.add("TPCN");
                    lstParam.add("DBT");
                }
            }

            Query countQuery = getSession().createQuery("select count(distinct f.fileId) " + hql);
            Query query = getSession().createQuery("select distinct f " + hql + "order by f.modifyDate DESC)");
            for (int i = 0; i < lstParam.size(); i++) {
                query.setParameter(i, lstParam.get(i));
                countQuery.setParameter(i, lstParam.get(i));
            }

            query.setFirstResult(start);
            query.setMaxResults(count);
            int total = Integer.parseInt(countQuery.uniqueResult().toString());
            List<FilesNoClob> lstResult = query.list();
            GridResult gr = new GridResult(total, lstResult);
            return gr;
        } catch (Exception ex) {
            LogUtil.addLog(ex);//binhnt sonar a160901
            return new GridResult(0, null);
        }
    }

    /**
     * Tm h s  ? xut thm nh
     *
     * @param form
     * @param deptId
     * @param start
     * @param count
     * @param sortField
     * @return
     */
    public GridResult findAllFileForProposeEvaluation(FilesForm form, Long deptId, int start, int count,
            String sortField) {
        try {
            String hql = " from FilesNoClob f where f.isActive=1 and (f.status=?) and (f.isTemp = null or f.isTemp = 0 ) ";
            List lstParam = new ArrayList();
            //        lstParam.add(Constants.FILE_STATUS.RECEIVED);// ho s va tip nhn
            //        lstParam.add(Constants.FILE_STATUS.PROPOSED);
            lstParam.add(Constants.FILE_STATUS.RECEIVED);
            //140404
            //        lstParam.add(Constants.FILE_STATUS.REVIEWED_TO_ADD);
            //!140404
            if (form != null) {
                if (form.getFileCode() != null && !"".equals(form.getFileCode().trim())) {
                    hql += "AND lower(f.fileCode) like ? ESCAPE '/' ";
                    lstParam.add(StringUtils.toLikeString(form.getFileCode().trim().toLowerCase()));
                }
                if (form.getFileType() != null && form.getFileType().longValue() != -1) {
                    hql += "AND f.fileType = ? ";
                    lstParam.add(form.getFileType());
                }
                if (form.getSendDateFrom() != null) {
                    hql += "AND f.sendDate >= ? ";
                    lstParam.add(form.getSendDateFrom());
                }
                if (form.getSendDateTo() != null) {
                    hql += "AND f.sendDate <= ? ";
                    lstParam.add(form.getSendDateTo());
                }

                if (deptId != null) {
                    hql += "AND f.agencyId = ? ";
                    lstParam.add(deptId);
                }
            }
            Query countQuery = getSession().createQuery("select count(distinct f.fileId) " + hql);
            Query query = getSession().createQuery("select distinct f " + hql + " order by f.modifyDate desc ");
            for (int i = 0; i < lstParam.size(); i++) {
                query.setParameter(i, lstParam.get(i));
                countQuery.setParameter(i, lstParam.get(i));
            }

            query.setFirstResult(start);
            query.setMaxResults(count);
            int total = Integer.parseInt(countQuery.uniqueResult().toString());
            List lstResult = query.list();
            GridResult gr = new GridResult(total, lstResult);
            return gr;
        } catch (Exception ex) {
            LogUtil.addLog(ex);//binhnt sonar a160901
            return new GridResult(0, null);
        }
    }

    /**
     * tm cc h s  x l
     *
     * @param form
     * @param deptId
     * @param userId
     * @param searchType
     * @param start
     * @param count
     * @param sortField
     * @return
     */
    public GridResult searchFilesToProcess(FilesForm form, Long deptId, Long userId, Long searchType, int start,
            int count, String sortField) {
        GridResult gr;
        try {
            String hql = " from FilesNoClob f, Process p" + " where f.isActive=1" + " and f.fileId = p.objectId"
                    + " and p.objectType = ?" + " and p.isActive = 1" + " and (f.isTemp = null or f.isTemp = 0 ) ";
            List lstParam = new ArrayList();
            lstParam.add(Constants.OBJECT_TYPE.FILES);
            if (form != null) {
                if (form.getFileCode() != null && !"".equals(form.getFileCode().trim())) {
                    hql += " AND lower(f.fileCode) like ? ESCAPE '/' ";
                    lstParam.add(StringUtils.toLikeString(form.getFileCode().toLowerCase().trim()));
                }
                if (form.getAnnouncementNo() != null && !"".equals(form.getAnnouncementNo().trim())) {
                    hql += " AND lower(f.announcementNo) like ? ESCAPE '/' ";
                    lstParam.add(StringUtils.toLikeString(form.getAnnouncementNo().toLowerCase().trim()));
                }
                if (form.getBusinessName() != null && !"".equals(form.getBusinessName().trim())) {
                    hql += " AND lower(f.businessName) like ? ESCAPE '/' ";
                    lstParam.add(StringUtils.toLikeString(form.getBusinessName().toLowerCase().trim()));
                }
                if (form.getBusinessLicence() != null && !"".equals(form.getBusinessLicence().trim())) {
                    hql += " AND lower(f.businessLicence) like ? ESCAPE '/' ";
                    lstParam.add(StringUtils.toLikeString(form.getBusinessLicence().toLowerCase().trim()));
                }
                if (form.getBusinessAddress() != null && !"".equals(form.getBusinessAddress().trim())) {
                    hql += " AND lower(f.businessAddress) like ? ESCAPE '/' ";
                    lstParam.add(StringUtils.toLikeString(form.getBusinessAddress().toLowerCase().trim()));
                }
                if (form.getProductName() != null && !"".equals(form.getProductName().trim())) {
                    hql += " AND lower(f.productName) like ? ESCAPE '/' ";
                    lstParam.add(StringUtils.toLikeString(form.getProductName().toLowerCase().trim()));
                }
                if (form.getNationName() != null && !"".equals(form.getNationName().trim())) {
                    hql += " AND lower(f.nationName) like ? ESCAPE '/' ";
                    lstParam.add(StringUtils.toLikeString(form.getNationName().toLowerCase().trim()));
                }
                if (form.getManufactureName() != null && !"".equals(form.getManufactureName().trim())) {
                    hql += " AND lower(f.manufactureName) like ? ESCAPE '/' ";
                    lstParam.add(StringUtils.toLikeString(form.getManufactureName().toLowerCase().trim()));
                }
                if (form.getManufactureAddress() != null && !"".equals(form.getManufactureAddress().trim())) {
                    hql += " AND lower(f.manufactureAddress) like ? ESCAPE '/' ";
                    lstParam.add(StringUtils.toLikeString(form.getManufactureAddress().toLowerCase().trim()));
                }
                if (form.getMatchingTarget() != null && !"".equals(form.getMatchingTarget().trim())) {
                    hql += " AND lower(f.matchingTarget) like ? ESCAPE '/' ";
                    lstParam.add(StringUtils.toLikeString(form.getMatchingTarget().toLowerCase().trim()));
                }
                //141215u binhnt53
                if (form.getStaff() != null && form.getStaff().length() > 0) {
                    hql += " AND lower(f.nameStaffProcess) like ? ESCAPE '/' ";
                    lstParam.add(StringUtils.toLikeString(form.getStaff().toLowerCase().trim()));
                }
                if (form.getNameStaffProcess() != null && form.getNameStaffProcess().length() > 0) {
                    hql += " AND lower(f.nameStaffProcess) like ? ESCAPE '/' ";
                    lstParam.add(StringUtils.toLikeString(form.getNameStaffProcess().toLowerCase().trim()));
                }
                //!141215u binhnt53
                if (form.getFileType() != null && form.getFileType().longValue() != -1) {
                    hql += " AND f.fileType = ? ";
                    lstParam.add(form.getFileType());
                }
            }
            switch (Integer.parseInt(searchType.toString())) {
            case 29:
                //tim de cuc truong phe duyet
                hql += " and (f.status = ?) and p.receiveGroupId = ?";
                lstParam.add(Constants.FILE_STATUS.REVIEW_TO_BOSS);
                lstParam.add(deptId);
                hql += " and f.agencyId = ?";
                lstParam.add(deptId);
                break;
            case 1:
                // tim de tham dinh
                if (form.getStatus() != null) {
                    hql += " and (f.status = ?)";
                    lstParam.add(form.getStatus());
                } else {
                    hql += " and f.status in (?,?,?,?,?)";
                    lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                    lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_EVALUATE);
                    lstParam.add(Constants.FILE_STATUS.EVALUATED);
                    lstParam.add(Constants.FILE_STATUS.RECEIVED_TO_ADD);
                    lstParam.add(Constants.FILE_STATUS.NEW_TO_ADD);
                }
                //Hiepvv Tach rieng SDBS sau cong bo
                if (form != null && (form.getNoteEdit() == null || form.getNoteEdit().isEmpty())) {
                    hql += " and f.fileType NOT IN (select pr.procedureId from Procedure pr where pr.description=?)";
                    lstParam.add("announcementFile05");
                }
                //end hiepvv
                // Chi tim ho so giao cho ca nhan xu ly thoi
                hql += " and p.receiveGroupId = ?" + " and p.receiveUserId = ?"
                        + " and (p.processStatus=? or p.processStatus =? or p.processStatus =?)" + " and p.status=?"
                        + " and p.processType=?";//binhnt update 141211
                lstParam.add(deptId);
                lstParam.add(userId);
                lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_EVALUATE);//binhnt update 141211
                lstParam.add(Constants.FILE_STATUS.RECEIVED_TO_ADD);
                lstParam.add(0l);
                lstParam.add(Constants.PROCESS_TYPE.MAIN);
                break;
            case 2:
                // tim de review
                if (form.getStatus() == null) {
                    //                        hql += " and (f.status = ? or f.status = ?)";
                    hql += " and (f.status = ? or f.status = ? or f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.EVALUATED);
                    lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_REVIEW);
                    lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_ADD);
                } else {
                    hql += " and f.status = ? ";
                    lstParam.add(form.getStatus());
                    hql += " and p.receiveGroupId = ?" + " and ( p.receiveUserId = null or p.receiveUserId = ?) ";
                    lstParam.add(deptId);
                    lstParam.add(userId);
                }
                if (userId != null) {
                    hql += " and f.leaderReviewId = ?";
                    lstParam.add(userId);
                }
                //Hiepvv Tach rieng SDBS sau cong bo
                if (form != null && (form.getNoteEdit() == null || form.getNoteEdit().isEmpty())) {
                    hql += " and f.fileType NOT IN (select pr.procedureId from Procedure pr where pr.description=?)";
                    lstParam.add("announcementFile05");
                }
                //end hiepvv
                break;
            case 3:
                // tim de phe duyet
                hql += " and (f.status = ?)" + " and p.receiveGroupId = ?" + " and f.leaderApproveId = ? ";
                lstParam.add(Constants.FILE_STATUS.REVIEWED);
                lstParam.add(deptId);
                lstParam.add(userId);
                // Phe duyet cac ho so ban dau gui den cho don vi minh
                hql += " and f.agencyId = ?";
                lstParam.add(deptId);
                //Hiepvv Tach rieng SDBS sau cong bo
                if (form != null && (form.getNoteEdit() == null || form.getNoteEdit().isEmpty())) {
                    hql += " and f.fileType NOT IN (select pr.procedureId from Procedure pr where pr.description=?)";
                    lstParam.add("announcementFile05");
                }
                //end hiepvv
                break;
            case -3:
                // tim de phan cong phe duyet
                hql += " and (f.status = ?)" + " and p.receiveGroupId = ?";
                lstParam.add(Constants.FILE_STATUS.REVIEWED);
                lstParam.add(deptId);
                // Phe duyet cac ho so ban dau gui den cho don vi minh
                hql += " and f.agencyId = ?";
                lstParam.add(deptId);
                break;
            case 26:
                // tim de phe duyet
                hql += " and (f.status = ?)" + " and p.receiveGroupId = ?" + " and p.receiveUserId = ? ";
                lstParam.add(Constants.FILE_STATUS.REVIEW_TO_ADD);
                lstParam.add(deptId);
                lstParam.add(userId);
                // Phe duyet cac ho so ban dau gui den cho don vi minh
                hql += " and f.agencyId = ?";
                lstParam.add(deptId);
                break;
            case 30:
                hql += " and f.status in(?,?,?,?,?,?,?) and p.receiveGroupId = ? ";
                //                    hql += " and (f.status = ? or f.status = ?) and p.receiveGroupId = ? and p.receiveUserId = ? ";
                lstParam.add(Constants.FILE_STATUS.APPROVED);
                lstParam.add(Constants.FILE_STATUS.GIVE_BACK);
                lstParam.add(Constants.FILE_STATUS.ALERT_COMPARISON);
                lstParam.add(Constants.FILE_STATUS.REVIEW_COMPARISON);
                lstParam.add(Constants.FILE_STATUS.COMPARED_FAIL);
                lstParam.add(Constants.FILE_STATUS.REVIEW_COMPARISON_FAIL);
                lstParam.add(Constants.FILE_STATUS.COMPARED);
                lstParam.add(deptId);
                //lstParam.add(userId);
                /*
                 //140714 binhnt53
                 if (userId != null) {
                 hql += " and p.sendUserId = ? ";
                 lstParam.add(userId);
                 }//!140714 binhnt53
                 */
                //
                // Phe duyet cac ho so ban dau gui den cho don vi minh
                //
                //                hql += " and f.agencyId = ?";
                //                lstParam.add(deptId);
                //                hql += " and p.receiveGroupId = ? and ( p.receiveUserId = null or p.receiveUserId = ?) and p.processStatus=? and p.status=?";
                //                lstParam.add(deptId);
                //                lstParam.add(userId);
                //                lstParam.add(Constants.FILE_STATUS.REVIEWED);
                //                lstParam.add(0l);
                break;
            case 4:
                //
                //
                //
                hql += " and f.status in (?,?,?,?)";
                lstParam.add(Constants.FILE_STATUS.APPROVED);
                lstParam.add(Constants.FILE_STATUS.SIGNING);
                lstParam.add(Constants.FILE_STATUS.LICENSING);
                lstParam.add(Constants.FILE_STATUS.REJECT);
                //
                //
                //
                hql += " and p.receiveGroupId = ?" + " and p.receiveUserId = ?" + " and p.processStatus=?"
                        + " and p.status=?";
                lstParam.add(deptId);
                lstParam.add(userId);
                lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                lstParam.add(Constants.FILE_STATUS.EVALUATED);
                break;
            case 5:
                //
                //
                //
                hql += " and f.status in (?,?,?,?)";
                lstParam.add(Constants.FILE_STATUS.APPROVED);
                lstParam.add(Constants.FILE_STATUS.SIGNING);
                lstParam.add(Constants.FILE_STATUS.LICENSING);
                lstParam.add(Constants.FILE_STATUS.SIGNED);
                //
                //
                //
                if (deptId != null) {
                    hql += " AND f.agencyId = ?";
                    lstParam.add(deptId);
                }
                break;
            case 6:
                // Chi tim ho so giao cho ca nhan xu ly thoi!
                hql += " and f.status in (?,?,?)" + " and p.receiveGroupId =?" + " and p.receiveUserId =?"
                        + " and (p.processStatus=? or p.processStatus=?)" + " and p.status=?"
                        + " and p.processType=?";
                lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                lstParam.add(Constants.FILE_STATUS.RECEIVED_TO_ADD);
                lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_EVALUATE);

                lstParam.add(deptId);
                lstParam.add(userId);
                lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                lstParam.add(Constants.FILE_STATUS.RECEIVED_TO_ADD);
                lstParam.add(0l);
                lstParam.add(Constants.PROCESS_TYPE.COOPERATE);
                break;
            case 7:
                //
                // tim de thong bao ket qua tham dinh
                //
                if (form.getStatus() != null) {
                    hql += " and (f.status = ?)";
                    lstParam.add(form.getStatus());
                } else {
                    hql += " and (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.REVIEWED_TO_ADD);
                }
                //
                // Chi tim ho so giao cho ca nhan xu ly thoi
                //
                hql += " and p.receiveGroupId = ?" + " and p.receiveUserId = ?"
                        + " and (p.processStatus=? or p.processStatus =? )" + " and p.status=?"
                        + " and p.processType=?";
                lstParam.add(deptId);
                lstParam.add(userId);
                lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                lstParam.add(Constants.FILE_STATUS.REVIEWED_TO_ADD);
                lstParam.add(0l);
                lstParam.add(Constants.PROCESS_TYPE.MAIN);
                break;
            case 8:// tim de review SDBS //150204 binhnt53 update    
                hql += " and (f.status = ?)";
                lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_ADD);
                hql += " and p.receiveGroupId = ? and p.receiveUserId = ?";
                lstParam.add(deptId);
                lstParam.add(userId);
                hql += " and (f.leaderReviewId =?)";
                lstParam.add(userId);
                /* 150204 binhnt53u 
                 if (form.getStatus() != null) {
                 hql += " and (f.status = ?)";
                 lstParam.add(form.getStatus());
                 } else {
                 hql += " and (f.status = ? or f.status = ?)";
                 lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_ADD);
                 lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_EVALUATE);
                 }
                 // Lanh dao don vi duoc phan cong tham dinh, tim cac ho so duoc giao cho don vi minh tham dinh
                 hql += " and p.receiveGroupId = ? and ( p.receiveUserId = null or p.receiveUserId = ?) and (p.processStatus=? or p.processStatus =? or p.processStatus =?)and p.status=?";
                 lstParam.add(deptId);
                 lstParam.add(userId);
                 lstParam.add(Constants.FILE_STATUS.EVALUATED);
                 lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_ADD);
                 lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_EVALUATE);
                 lstParam.add(0l);
                 */
                break;
            case 9:
                //linhdx 20161022 Tach rieng SDBS sau cong bo
                if (form != null && form.getFileType() != 81) {
                    hql += " and f.fileType NOT IN (select pr.procedureId from Procedure pr where pr.description=?)";
                    lstParam.add("announcementFile05");
                }
                //tim de pho phong vao tham dinh
                // tim de tham dinh
                if (form.getStatus() != null) {
                    hql += " and (f.status = ?)";
                    lstParam.add(form.getStatus());
                } else {
                    hql += " and (f.status = ? or f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_ADD);
                    lstParam.add(Constants.FILE_STATUS.EVALUATED);
                }
                // Chi tim ho so giao cho ca nhan xu ly thoi
                hql += " and p.receiveGroupId = ?" + " and p.receiveUserId = ?"
                        + " and (p.processStatus=? or p.processStatus =? )" + " and p.status=?"
                        + " and p.processType=?";
                lstParam.add(deptId);
                lstParam.add(userId);
                lstParam.add(Constants.FILE_STATUS.EVALUATED);
                lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_ADD);
                lstParam.add(0l);
                lstParam.add(Constants.PROCESS_TYPE.MAIN);
                if (userId != null) {
                    hql += " and f.leaderEvaluateId = ?";
                    lstParam.add(userId);
                }
                break;
            default:
            }
            Query countQuery = getSession().createQuery("select count(distinct f.fileId) " + hql);
            Query query = getSession().createQuery("select distinct f " + hql + " order by f.modifyDate asc");
            for (int i = 0; i < lstParam.size(); i++) {
                query.setParameter(i, lstParam.get(i));
                countQuery.setParameter(i, lstParam.get(i));
            }

            query.setFirstResult(start);
            query.setMaxResults(count);
            int total = Integer.parseInt(countQuery.uniqueResult().toString());
            List<FilesNoClob> lstResult = query.list();
            //            for (FilesNoClob f : lstResult) {
            //                hql = "select p from Process p where p.isActive = 1 and p.objectType=? and p.objectId = ? and p.processStatus=? and (p.receiveUserId=? or p.sendUserId=?) order by p.processId ASC";
            //                query = getSession().createQuery(hql);
            //                query.setParameter(0, Constants.OBJECT_TYPE.FILES);
            //                query.setParameter(1, f.getFileId());
            //                query.setParameter(2, Constants.FILE_STATUS.ASSIGNED);
            //                query.setParameter(3, userId);
            //                query.setParameter(4, userId);
            //                List<Process> lstPro = query.list();
            //                if (lstPro.size() > 0) {
            //                    String leaderprocess = "";
            //                    String staffprocess = "";
            //                    if (lstPro.get(0).getSendUser() != null && lstPro.get(0).getSendUserId() != userId) {
            //                        if (lstPro.get(0).getSendUser().length() > 0) {
            //                            leaderprocess = lstPro.get(0).getSendUser();
            //                        } else {
            //                            leaderprocess = "N/A";
            //                        }
            //                    }
            //                    if (lstPro.get(0).getReceiveUser() != null && lstPro.get(0).getReceiveUserId() != userId) {
            //                        if (lstPro.get(0).getReceiveUser().length() > 0) {
            //                            staffprocess = lstPro.get(0).getReceiveUser();
            //                        } else {
            //                            staffprocess = "N/A";
            //                        }
            //                    }
            //                    f.setLeaderProcess(leaderprocess);
            //                    f.setStaffProcess(staffprocess);
            //                }
            //            }
            gr = new GridResult(total, lstResult);
            return gr;
        } catch (Exception ex) {
            LogUtil.addLog(ex);//binhnt sonar a160901.
            gr = new GridResult(0, null);
        }
        return gr;
    }

    /**
     * Count h s cn gi
     *
     * @param businessId
     * @param status
     * @return
     */
    public int getCountFileToSend(Long businessId, Long status) {
        try {
            String hql = "select count(distinct f.fileId)" + " from FilesNoClob f, DetailProduct d "
                    + " where f.isActive = 1" + " and f.detailProductId = d.detailProductId" + " and f.deptId = ?"
                    + " and (f.isTemp = null or f.isTemp = 0 )" + " and (sysdate - 366  <= f.createDate) ";
            List lstParam = new ArrayList();
            lstParam.add(businessId);
            switch (Integer.parseInt(status.toString())) {
            case -77:
                lstParam.clear();
                hql = "select count(distinct f.fileId) from FilesNoClob f, DetailProduct d "
                        + " where f.isActive = 1" + " and f.detailProductId = d.detailProductId"
                        + " and (f.isTemp = null or f.isTemp = 0 )" + " and f.deptId = ? " + " AND f.isFee = ? ";
                lstParam.clear();
                lstParam.add(businessId);
                lstParam.add(Constants.FEE_STATUS.DA_TU_CHOI);
                break;
            case -100:
                hql = "select count(distinct f.fileId)"
                        + " from FilesNoClob f, Fee fe, FeePaymentInfo fpi, DetailProduct d where f.fileId = fpi.fileId  and (f.isTemp=null or f.isTemp=0) and f.isActive=1"
                        + " and fe.feeId = fpi.feeId and fe.feeType=2 and fe.isActive=1" + " and fpi.isActive=1 "
                        + " and f.detailProductId = d.detailProductId" + " and fpi.status not in (2,1)"//u150211 by binhnt //+ " and fpi.status <> 1"
                        + " and f.userSigned is not null" + " and f.status=?" + " and f.deptId = ? ";
                lstParam.clear();
                lstParam.add(Constants.FILE_STATUS.NEW);
                lstParam.add(businessId);
                break;
            case -6:// (files.status = 1, fee_payment_info.status = 1, fee.fee_type=2)
                hql = "select count(distinct f.fileId)"
                        + " from FilesNoClob f, Fee fe, FeePaymentInfo fpi , DetailProduct d where f.fileId = fpi.fileId and (f.isTemp=null or f.isTemp=0) and f.isActive=1"
                        + " and fe.feeId = fpi.feeId and fe.feeType=2 and fe.isActive=1" + " and fpi.isActive=1"
                        + " and (fpi.status=1)" + " and f.detailProductId = d.detailProductId"
                        + " and f.userSigned is not null" + " and (f.status=? or f.status=?) and f.deptId = ?";
                lstParam.clear();
                lstParam.add(Constants.FILE_STATUS.NEW);
                lstParam.add(Constants.FILE_STATUS.NEW_TO_ADD);
                lstParam.add(businessId);
                break;
            case -5:// 12- H s ch? np l ph = ? ph duyt, cha np l ph (files.status = 6, fee_payment_info.status = 0, loi l ph)
                hql = "select count(distinct f.fileId)"
                        + " from FilesNoClob f, Fee fe, FeePaymentInfo fpi, DetailProduct d  where f.fileId = fpi.fileId and (f.isTemp=null or f.isTemp=0) and f.isActive=1"
                        + " and fe.feeId = fpi.feeId and fe.feeType=1 and fe.isActive=1" + " and fpi.isActive=1"
                        + " and (fpi.status=0)" + " and f.detailProductId = d.detailProductId"
                        + " and f.userSigned is not null" + " and (f.status=?) and f.deptId = ?";
                lstParam.clear();
                lstParam.add(Constants.FILE_STATUS.APPROVED);
                lstParam.add(businessId);
                break;

            // ho so cho ke toan xac nhan le phi cap so - hieptq 161214
            case -109:
                hql = "select count(distinct f.fileId)"
                        + " from FilesNoClob f, Fee fe, FeePaymentInfo fpi , DetailProduct d where f.fileId = fpi.fileId and (f.isTemp=null or f.isTemp=0) and f.isActive=1"
                        + " and fe.feeId = fpi.feeId and fe.feeType=1 and fe.isActive=1" + " and fpi.isActive = 1"
                        + " and (fpi.status > 1)" + " and f.detailProductId = d.detailProductId "
                        + " and f.userSigned is not null" + " and (f.status=?) and f.deptId = ?";
                lstParam.clear();
                lstParam.add(Constants.FILE_STATUS.APPROVED);
                lstParam.add(businessId);
                break;

            //H s vn th  tr bn cng b (bn m?m) =  ho so da ky so + dong tien + dong dau so
            case 76:
                hql = "select count(distinct f.fileId)" + " from FilesNoClob f, DetailProduct d "
                        + " where  f.isActive = 1 and (f.isTemp = null or f.isTemp = 0)"
                        + " and f.isFee = 1 and f.isSignPdf = 2" + " and f.detailProductId = d.detailProductId "
                        + " and f.status in (?,?,?,?,?,?,?)" + " and f.deptId = ?";
                lstParam.clear();
                lstParam.add(Constants.FILE_STATUS.APPROVED);
                lstParam.add(Constants.FILE_STATUS.GIVE_BACK);
                lstParam.add(Constants.FILE_STATUS.ALERT_COMPARISON);
                lstParam.add(Constants.FILE_STATUS.COMPARED);
                lstParam.add(Constants.FILE_STATUS.REVIEW_COMPARISON);
                lstParam.add(Constants.FILE_STATUS.REVIEW_COMPARISON_FAIL);
                lstParam.add(Constants.FILE_STATUS.COMPARED_FAIL);
                lstParam.add(businessId);
                break;
            case -4://3- H s ch? tip nhn =
                //  + Mi np + ch? xc nhn vn th (files.status = 1, fee_payment_info.status = 3 or 4)
                //  + Mi np S?BS + ch? xc nhn vn th(files.status = 18, fee_payment_info.status = 3 or 4)
                //                    hql = "select count(distinct f.fileId)"
                //                            + " from FilesNoClob f, Fee fe, FeePaymentInfo fpi where f.fileId = fpi.fileId and (f.isTemp=null or f.isTemp=0) and f.isActive=1"
                //                            + " and fe.feeId = fpi.feeId and fe.feeType=2 and fe.isActive=1"
                //                            + " and fpi.isActive=1"
                //                            + " and (fpi.status=3 or fpi.status=4)"
                //                            + " and f.userSigned is not null"
                //                            + " and (f.status=? or f.status=?)"
                //                            + " and f.userCreateId = ?";
                hql = "select count(distinct f.fileId)"
                        + " from FilesNoClob f, Fee fe, FeePaymentInfo fpi , DetailProduct d "
                        + " where fpi.fileId = f.fileId " + " and f.isActive = 1 "
                        + " and f.detailProductId = d.detailProductId " + " and f.userSigned is not null"
                        + " and (f.isTemp is null or f.isTemp = 0) and f.isFee = 1"
                        //                            + " and fe.feeId = fpi.feeId and fe.feeType=2 and fe.isActive=1"
                        + " and (f.status = ?) " + " and fpi.isActive = 1" + " and f.deptId = ?";
                lstParam.clear();
                lstParam.add(Constants.FILE_STATUS.NEW);
                //lstParam.add(Constants.FILE_STATUS.NEW_TO_ADD);
                lstParam.add(businessId);
                break;

            // ho so cho tiep nhan SDBS
            case -7://3- H s ch? tip nhn =
                hql = "select count(distinct f.fileId)"
                        + " from FilesNoClob f, Fee fe, FeePaymentInfo fpi , DetailProduct d "
                        + " where fpi.fileId = f.fileId " + " and f.isActive = 1 "
                        + " and f.detailProductId = d.detailProductId " + " and f.userSigned is not null"
                        + " and (f.isTemp is null or f.isTemp = 0) and f.isFee = 1"
                        //                            + " and fe.feeId = fpi.feeId and fe.feeType=2 and fe.isActive=1"
                        + " and (f.status = ?) " + " and fpi.isActive = 1" + " and f.deptId = ?";
                lstParam.clear();
                //lstParam.add(Constants.FILE_STATUS.NEW);
                lstParam.add(Constants.FILE_STATUS.NEW_TO_ADD);
                lstParam.add(businessId);
                break;

            case -3://2- H s cha np ph  = Mi to, cha np ph,  k or upload file k(files.status = 0, fee_payment_info.status = 0, files.user_signed is not null)
                hql = "select count(distinct f.fileId)"
                        + " from FilesNoClob f, Fee fe, FeePaymentInfo fpi, DetailProduct d "
                        + " where f.fileId = fpi.fileId" + " and (f.isTemp = null or f.isTemp = 0)"
                        + " and f.isActive = 1" + " and f.detailProductId = d.detailProductId "
                        + " and fe.feeId = fpi.feeId" + " and fe.feeType = 2" + " and fe.isActive = 1"
                        + " and fpi.isActive = 1" + " and f.userSigned is not null" + " and f.status = ?"
                        + " and f.deptId = ?";
                lstParam.clear();
                lstParam.add(Constants.FILE_STATUS.NEW_CREATE);
                lstParam.add(businessId);
                break;
            case -2://xxxx binhnt53 update 150209
                hql = "select count(distinct f.fileId)" + " from FilesNoClob f, DetailProduct d "
                        + " where f.isActive = 1" + " and f.detailProductId = d.detailProductId "
                        + " and (f.isTemp = null or f.isTemp = 0 ) " + " and f.status in (?,?,?,?,?,?,?,?,?,?,?)"
                        + " and f.deptId = ? ";
                lstParam.clear();
                lstParam.add(Constants.FILE_STATUS.ASSIGNED);//4
                lstParam.add(Constants.FILE_STATUS.EVALUATED);//4
                lstParam.add(Constants.FILE_STATUS.REVIEWED);//5
                lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_ADD);//7
                lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_EVALUATE);//8
                lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_REVIEW);//9
                lstParam.add(Constants.FILE_STATUS.REVIEWED_TO_ADD);//19
                lstParam.add(Constants.FILE_STATUS.COMPARED_FAIL);//16
                lstParam.add(Constants.FILE_STATUS.REVIEW_COMPARISON);//24
                lstParam.add(Constants.FILE_STATUS.REVIEW_COMPARISON_FAIL);//25
                lstParam.add(Constants.FILE_STATUS.REVIEW_TO_ADD);//26
                //                    lstParam.add(Constants.FILE_STATUS.NEW_TO_ADD);//18
                //                    lstParam.add(Constants.FILE_STATUS.RECEIVED);//14
                //                    lstParam.add(Constants.FILE_STATUS.COMPARED);//15
                lstParam.add(businessId);
                break;
            case -1:
                lstParam.clear();
                hql = "select count(distinct f.fileId) from FilesNoClob f , DetailProduct d "
                        + "where f.isActive = 1 " + " and f.detailProductId = d.detailProductId "
                        + " and f.deptId = ? " + " nd (f.isTemp = null or f.isTemp = 0 ) ";
                lstParam.add(businessId);
                hql += "and (f.status = ? or f.status = ?) ";
                lstParam.add(Constants.FILE_STATUS.NEW);
                lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_ADD);
                break;

            case 15:
                hql += "and (f.status = ?) " + " and f.deptId = ? ";
                lstParam.add(Constants.FILE_STATUS.COMPARED);
                lstParam.add(businessId);
                break;
            case 20:
                lstParam.clear();
                //                    hql = "select count(distinct f.fileId) from FilesNoClob f where f.isActive = 1 and f.deptId = ? and (f.isTemp = null or f.isTemp = 0 ) and (sysdate - 366  <= f.createDate) ";
                hql = "select count(distinct f.fileId) from FilesNoClob f , DetailProduct d "
                        + "where f.isActive = 1 " + " and f.detailProductId = d.detailProductId "
                        + " and f.deptId = ? and (f.isTemp = null or f.isTemp = 0 )";
                lstParam.add(businessId);
                hql += "and (f.status = ?) ";
                lstParam.add(Constants.FILE_STATUS.EVALUATED_TO_ADD);
                break;
            case 14:
                lstParam.clear();
                hql = "select count(distinct f.fileId) from FilesNoClob f , DetailProduct d "
                        + "where f.isActive = 1 " + " and f.detailProductId = d.detailProductId "
                        + "and f.deptId = ? and (f.isTemp = null or f.isTemp = 0 ) and (sysdate - 366  <= f.createDate) ";
                lstParam.add(businessId);
                hql += "and (f.status = ?) ";
                lstParam.add(Constants.FILE_STATUS.RECEIVED);
                break;
            case 23:
                lstParam.clear();
                hql = "select count(distinct f.fileId) from FilesNoClob f , DetailProduct d  "
                        + "where f.isActive = 1 " + " and f.detailProductId = d.detailProductId "
                        + "and f.deptId = ? and (f.isTemp = null or f.isTemp = 0 ) and (sysdate - 366  <= f.createDate) ";
                lstParam.add(businessId);
                hql += "and (f.status = ?) ";
                lstParam.add(Constants.FILE_STATUS.ALERT_COMPARISON);
                break;
            case 0:
                lstParam.clear();
                hql = "select count(distinct f.fileId) from FilesNoClob f, DetailProduct d  "
                        + " where f.isActive = 1" + " and f.detailProductId = d.detailProductId "
                        + " AND (f.isTemp = null or f.isTemp = 0 )" + " AND (sysdate - 366  <= f.createDate) "
                        + " AND f.userSigned = null" + " AND f.deptId = ?" + " AND (f.status = ? or f.status = ?)";
                lstParam.clear();
                lstParam.add(businessId);
                lstParam.add(Constants.FILE_STATUS.NEW_CREATE);
                lstParam.add(Constants.FILE_STATUS.NEW);
                break;
            case 33:
                lstParam.clear();
                hql = "select count(distinct f.fileId)" + " from FilesNoClob f, DetailProduct d "
                        + " where f.isActive = 1" + " and f.detailProductId = d.detailProductId "
                        + " AND f.deptId = ?" + " AND (f.isTemp = null or f.isTemp = 0 )"
                        + " AND (sysdate - 366  <= f.createDate)" + " AND (f.status = ? or f.status = ?)";
                lstParam.clear();
                lstParam.add(businessId);
                lstParam.add(Constants.FILE_STATUS.RECEIVED_REJECT);
                lstParam.add(Constants.FILE_STATUS.RECEIVED_REJECT_TO_ADD);
                break;
            default:
                hql += "and f.status = ? " + " and f.deptId = ? ";
                lstParam.add(status);
                lstParam.add(businessId);
                break;
            }
            Query query = getSession().createQuery(hql);
            for (int i = 0; i < lstParam.size(); i++) {
                query.setParameter(i, lstParam.get(i));
            }
            int total = Integer.parseInt(query.list().get(0).toString());
            return total;
        } catch (NumberFormatException | HibernateException ex) {
            LogUtil.addLog(ex);//binhnt sonar a160901
            return 0;
        }
    }

    /**
     * Count h s cn ? xut
     *
     * @param userId
     * @param deptId
     * @return
     */
    public int getCountFileToPropose(Long userId, Long deptId) {
        try {
            String hql = "select count(distinct f.fileId) from FilesNoClob f where f.isActive=1 and (f.status=? or f.status=? ) and f.agencyId = ? and (f.isTemp = null or f.isTemp = 0 ) ";
            Query query = getSession().createQuery(hql);
            query.setParameter(0, Constants.FILE_STATUS.NEW);//h s vn th  tip nhn
            query.setParameter(1, Constants.FILE_STATUS.NEW_TO_ADD);
            query.setParameter(2, deptId);
            int total = Integer.parseInt(query.list().get(0).toString());
            return total;
        } catch (Exception ex) {
            LogUtil.addLog(ex);//binhnt sonar a160901
            return 0;
        }
    }

    /**
     * count h s cn phn cng
     *
     * @param userId
     * @param deptId
     * @return
     */
    public int getCountFileToAssign(Long userId, Long deptId) {
        try {
            String hql = "select count(distinct f.fileId) from FilesNoClob f" + " where f.isActive=1"
                    + " and f.status in (?,?,?)" + " and f.agencyId = ?"
                    + " and (f.isTemp = null or f.isTemp = 0 ) ";
            Query query = getSession().createQuery(hql);
            query.setParameter(0, Constants.FILE_STATUS.RECEIVED);
            query.setParameter(1, Constants.FILE_STATUS.PROPOSED);
            query.setParameter(2, Constants.FILE_STATUS.NEW);
            query.setParameter(3, deptId);
            int total = Integer.parseInt(query.list().get(0).toString());
            return total;
        } catch (HibernateException | NumberFormatException ex) {
            LogUtil.addLog(ex);//binhnt sonar a160901
            return 0;
        }
    }

    /**
     * Count H s cn thm nh
     *
     * @param userId
     * @param deptId
     * @param searchType
     * @param status
     * @return
     */
    public int getCountFileToProcess(Long userId, Long deptId, Long searchType, Long status) {//
        try {
            String hql = " from" + " FilesNoClob f," + " Process p," + " Business b" + " where f.isActive=1"
                    + " and f.fileId = p.objectId"
                    //+ " and f.detailProductId = d.detailProductId"
                    + " and f.deptId = b.businessId" + " and (f.isTemp = null or f.isTemp = 0)";
            List lstParam = new ArrayList();
            switch (Integer.parseInt(searchType.toString())) {
            case -29:
                //tim de cuc truong phe duyet
                hql += " and (f.status = ?)" + " and p.receiveGroupId = ?";
                lstParam.add(Constants.FILE_STATUS.REVIEW_TO_BOSS);
                lstParam.add(deptId);
                hql += " and f.agencyId = ?";
                lstParam.add(deptId);
                break;
            case -23:// tim de phan cong phe duyet
                hql += " and (f.status = ?)" + " and p.receiveGroupId = ?";
                lstParam.add(Constants.FILE_STATUS.REVIEWED);
                lstParam.add(deptId);
                // Phe duyet cac ho so ban dau gui den cho don vi minh
                hql += " and f.agencyId = ?";
                lstParam.add(deptId);
                break;
            case 1:// tim de tham dinh
                hql += " and (f.status = ?)";
                lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                // Chi tim ho so giao cho ca nhan xu ly thoi
                hql += " and p.receiveGroupId = ?" + " and p.receiveUserId = ?";
                lstParam.add(deptId);
                lstParam.add(userId);
                break;
            case -20://Ho so da gui thong bao sdbs cho doanh nghiep
                lstParam.clear();
                hql = "from FilesNoClob f," + " DetailProduct d " + " where f.isActive=1" + " and (f.status = ?)"
                        + " and f.staffProcess=?" + " and f.detailProductId = d.detailProductId"
                        + " and (f.isTemp = null or f.isTemp = 0 ) ";
                lstParam.add(Constants.FILE_STATUS.EVALUATED_TO_ADD);
                lstParam.add(userId);
                break;
            case 39:// ho so cho xem xt
                lstParam.clear();
                hql = "from FilesNoClob f," + " DetailProduct d " + " where f.isActive=1" + " and (f.status = ?)"
                        + " and f.leaderReviewId=?" + " and f.detailProductId = d.detailProductId"
                        + " and (f.isTemp = null or f.isTemp = 0 ) ";
                lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_REVIEW);
                lstParam.add(userId);
                break;
            case 447:// ho so cho xem xt
                lstParam.clear();
                hql = "from FilesNoClob f," + " DetailProduct d " + " where f.isActive=1" + " and (f.status = ?)"
                        + " and f.staffProcess=?" + " and f.detailProductId = d.detailProductId"
                        + " and (f.isTemp = null or f.isTemp = 0 ) ";
                lstParam.add(Constants.FILE_STATUS.EVALUATED);
                lstParam.add(userId);
                break;
            case 448:// ho so cho xem xt
                lstParam.clear();
                hql = "from FilesNoClob f," + " DetailProduct d" + " where f.isActive=1" + " and (f.status = ?)"
                        + " and f.staffProcess=?" + " and f.detailProductId = d.detailProductId"
                        + " and (f.isTemp = null or f.isTemp = 0 ) ";
                lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_ADD);
                lstParam.add(userId);
                break;
            case 1623:// h s cn i chiu, stt =  thng bo i chiu or i chiu c sai lch
                if (status == null) {
                    hql += " and (f.status = ?)";
                    //lstParam.add(Constants.FILE_STATUS.COMPARED_FAIL);
                    lstParam.add(Constants.FILE_STATUS.ALERT_COMPARISON);
                }
                if (deptId != null) {
                    hql += " and p.receiveGroupId = ?";
                    lstParam.add(deptId);
                }
                //                    if (userId != null) {
                //                        hql += " and p.receiveUserId = ? ";
                //                        lstParam.add(userId);
                //                    }
                break;
            case 5://H s  trnh, ch? lnh o cc ph duyt 
                lstParam.clear();
                hql = " FROM" + " FilesNoClob f," + " Process p," + " DetailProduct d" + " WHERE f.isActive = 1"
                        + " AND f.fileId = p.objectId" + " and f.detailProductId = d.detailProductId"
                        + " AND f.status = ?" + " AND (f.isTemp = null or f.isTemp = 0)";
                lstParam.add(Constants.FILE_STATUS.REVIEWED);
                hql += " and p.receiveGroupId = ?";
                lstParam.add(deptId);
                hql += " and p.receiveUserId = ?";
                lstParam.add(userId);
                break;
            case -5://A 160701 - H s  trnh, ch? lnh o cc ph duyt 
                lstParam.clear();
                hql = " FROM" + " FilesNoClob f," + " Process p," + " DetailProduct d" + " WHERE f.isActive = 1"
                        + " AND f.fileId = p.objectId" + " AND f.detailProductId = d.detailProductId"
                        + " AND f.status = ?" + " AND (f.isTemp = null or f.isTemp = 0 )";
                lstParam.add(Constants.FILE_STATUS.REVIEWED);
                hql += " AND p.receiveGroupId = ?";
                lstParam.add(deptId);
                hql += " AND p.receiveUserId = ?";
                lstParam.add(userId);
                break;
            case -22://H s  cp giy chng nhn
                if (status == null) {
                    hql += " AND f.status = ?";
                    lstParam.add(Constants.FILE_STATUS.GIVE_BACK);
                } else {
                    hql += " AND f.status = ?";
                    lstParam.add(status);
                }
                break;
            case -3:/*Ho so da phan cong*/

                hql += " and (f.status = ?)";
                lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                if (deptId != null) {
                    hql += " AND (f.agencyId = ? or p.receiveUserId = ? or p.receiveGroupId=?)"
                            + " AND (p.processStatus=?)";
                    lstParam.add(deptId);
                    lstParam.add(userId);
                    lstParam.add(deptId);
                    lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                } else {
                    hql += " AND (p.receiveUserId = ?" + " AND (p.processStatus=?)" + " AND p.status=? ) ";
                    lstParam.add(userId);
                    lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                    lstParam.add(0l);
                }
                break;

            //                case 0:
            //                    hql += " and (f.status = ?)";
            //                    lstParam.add(Constants.FILE_STATUS.RECEIVED);
            //                    if (deptId != null) {
            ////                    hql += " AND (f.agencyId = ? or p.receiveUserId = ? or p.receiveGroupId=? ) and (p.processStatus=? or p.processStatus=? or p.processStatus =? or p.processStatus =? ) ";
            //                        hql += " AND (f.agencyId = ? or p.receiveUserId = ? or p.receiveGroupId=? ) and (p.processStatus=? or p.processStatus=? or p.processStatus =? ) ";
            //                        lstParam.add(deptId);
            //                        lstParam.add(userId);
            //                        lstParam.add(deptId);
            //                        lstParam.add(Constants.FILE_STATUS.NEW);
            //                        lstParam.add(Constants.FILE_STATUS.RECEIVED);
            //                        lstParam.add(Constants.FILE_STATUS.PROPOSED);
            //                        //lstParam.add(Constants.FILE_STATUS.ASSIGNED);
            //                    } else {
            //                        hql += "AND (p.receiveUserId = ? and (p.processStatus=? or p.processStatus=? or p.processStatus =? )and p.status=? ) ";
            ////                hql += "AND (p.receiveUserId = ? and (p.processStatus=? or p.processStatus=? )and p.status=? ) ";
            //                        lstParam.add(userId);
            //                        lstParam.add(Constants.FILE_STATUS.NEW);
            //                        lstParam.add(Constants.FILE_STATUS.RECEIVED);
            //                        lstParam.add(Constants.FILE_STATUS.PROPOSED);
            //                        lstParam.add(0l);
            //                    }
            //                    break;
            //hieptq update 191114 dem thuc pham thuong
            case 211:
                hql = "from" + " FilesNoClob f," + " DetailProduct d," + " Process p," + " Category c" + " where"
                        + " f.isActive=1" + " AND f.fileId = p.objectId" + " AND f.productTypeId = c.categoryId"
                        + " and f.detailProductId = d.detailProductId" + " AND p.objectType = ?"
                        + " AND p.isActive = 1" + " AND (f.isTemp = null or f.isTemp = 0 )" + " AND (f.status = ?)"
                        + " AND (f.agencyId = ? or p.receiveUserId = ? or p.receiveGroupId=?)"
                        + " AND (p.processStatus=? or p.processStatus=? or p.processStatus =?)"
                        + " AND (c.code <> ? AND c.code <> ?)";
                lstParam.clear();
                lstParam.add(30l);
                lstParam.add(14l);
                lstParam.add(deptId);
                lstParam.add(userId);
                lstParam.add(deptId);
                lstParam.add(1l);
                lstParam.add(14l);
                lstParam.add(2l);
                lstParam.add("TPCN");
                lstParam.add("DBT");

                //                    Query queryCheckFileId = getSession().createQuery("select f.fileId " + hql + "order by f.sendDate desc");
                //                    // hieptq 181114 tach nhom san pham
                //                    for (int i = 0; i < lstParam.size(); i++) {
                //                        queryCheckFileId.setParameter(i, lstParam.get(i));
                //                    }
                //                    // loc fileId all
                //                    List<Long> lstResultCheckFileId = new ArrayList<Long>();
                //                    lstResultCheckFileId = queryCheckFileId.list();
                //                    String lstFileId = "";
                //                    for (int i = 0; i < lstResultCheckFileId.size(); i++) {
                //                        if (i == (lstResultCheckFileId.size() - 1)) {
                //                            lstFileId += lstResultCheckFileId.get(i).toString();
                //                        } else {
                //                            lstFileId += lstResultCheckFileId.get(i).toString() + ",";
                //                        }
                //                    }
                //
                //                    // loc file_id nhom san pham thuong
                //                    Query hql_1 = getSession().createQuery("select fpi.fileId from FeePaymentInfo fpi where fpi.fileId in (" + lstFileId + ") and fpi.cost = 500000 ");
                //                    List<Long> lsthql_1 = new ArrayList<Long>();
                //                    lsthql_1 = hql_1.list();
                //                    String hql1_FileId = "";
                //                    for (int i = 0; i < lsthql_1.size(); i++) {
                //                        if (i == (lsthql_1.size() - 1)) {
                //                            hql1_FileId += lsthql_1.get(i).toString();
                //                        } else {
                //                            hql1_FileId += lsthql_1.get(i).toString() + ",";
                //                        }
                //                    }
                //                    if ("".equals(hql1_FileId)) {
                //                        hql1_FileId = "0";
                //                    }
                //                    hql += "AND f.fileId in (" + hql1_FileId + ")";// n (1,2,3,4,5)
                break;

            case 212:
                hql = "from" + " FilesNoClob f," + " Process p," + " Category c ," + " DetailProduct d" + " where"
                        + " f.isActive=1" + " AND f.fileId = p.objectId"
                        + " and f.detailProductId = d.detailProductId" + " AND f.productTypeId = c.categoryId"
                        + " AND p.objectType = ?" + " AND p.isActive = 1" + " AND (f.isTemp = null or f.isTemp = 0)"
                        + " AND (f.status = ?)"
                        + " AND (f.agencyId = ? or p.receiveUserId = ? or p.receiveGroupId=?)"
                        + " AND (p.processStatus=? or p.processStatus=? or p.processStatus =?)"
                        + " AND (c.code = ? or c.code = ?)";
                lstParam.clear();
                lstParam.add(30l);
                lstParam.add(14l);
                lstParam.add(deptId);
                lstParam.add(userId);
                lstParam.add(deptId);
                lstParam.add(1l);
                lstParam.add(14l);
                lstParam.add(2l);
                lstParam.add("TPCN");
                lstParam.add("DBT");
                //                    Query queryCheckFileId2 = getSession().createQuery("select f.fileId " + hql + "order by f.sendDate desc");
                //                    // hieptq 181114 tach nhom san pham
                //                    for (int i = 0; i < lstParam.size(); i++) {
                //                        queryCheckFileId2.setParameter(i, lstParam.get(i));
                //                    }
                //                    // loc fileId all
                //                    List<Long> lstResultCheckFileId2 = new ArrayList<Long>();
                //                    lstResultCheckFileId2 = queryCheckFileId2.list();
                //                    String lstFileId2 = "";
                //                    for (int i = 0; i < lstResultCheckFileId2.size(); i++) {
                //                        if (i == (lstResultCheckFileId2.size() - 1)) {
                //                            lstFileId2 += lstResultCheckFileId2.get(i).toString();
                //                        } else {
                //                            lstFileId2 += lstResultCheckFileId2.get(i).toString() + ",";
                //                        }
                //                    }
                //                    Query hql_2 = getSession().createQuery("select fpi.fileId from FeePaymentInfo fpi where fpi.fileId in (" + lstFileId2 + ") and fpi.cost = 1500000 ");
                //                    List<Long> lsthql_2 = new ArrayList<Long>();
                //                    lsthql_2 = hql_2.list();
                //                    String hql1_FileId2 = "";
                //                    for (int i = 0; i < lsthql_2.size(); i++) {
                //                        if (i == (lsthql_2.size() - 1)) {
                //                            hql1_FileId2 += lsthql_2.get(i).toString();
                //                        } else {
                //                            hql1_FileId2 += lsthql_2.get(i).toString() + ",";
                //                        }
                //                    }
                //                    if ("".equals(hql1_FileId2)) {
                //                        hql1_FileId2 = "0";
                //                    }
                //                    hql += "AND f.fileId in (" + hql1_FileId2 + ")";
                break;

            case 4:
                // tim de phoi hop tham dinh
                if (status == null) {
                    hql += " and (f.status = ? or f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                    lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_EVALUATE);
                } else {
                    hql += " and f.status = ? ";
                    lstParam.add(status);
                }
                //
                // Chi tim ho so giao cho ca nhan xu ly thoi
                //
                hql += " and p.receiveGroupId = ?" + " and p.receiveUserId = ?"
                        + " and (p.processStatus=? or p.processStatus =? )" + " and p.status=?"
                        + " and p.processType = ? ";
                lstParam.add(deptId);
                lstParam.add(userId);
                lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_EVALUATE);
                lstParam.add(0l);
                lstParam.add(Constants.PROCESS_TYPE.COOPERATE);
                break;
            case -1:
                // tim de tiep nhan
                if (status == null) {
                    hql += " and f.status in (?,?)";
                    lstParam.add(Constants.FILE_STATUS.NEW);
                    lstParam.add(Constants.FILE_STATUS.NEW_TO_ADD);
                } else {
                    hql += " and f.status = ? ";
                    lstParam.add(status);
                }
                if (deptId != null) {
                    hql += "AND f.agencyId = ? ";
                    lstParam.add(deptId);
                }
                break;
            case 42:
                lstParam.clear();
                //                    hql = "from FilesNoClob f"
                //                            + " where f.isActive=1 "
                //                            + " and (f.isTemp = null or f.isTemp = 0 ) "
                //                            + " and (f.status = ?)"
                //                            + " and f.staffProcess=?";
                hql += " and (f.status = ?) and f.staffProcess=? ";
                lstParam.add(Constants.FILE_STATUS.EVALUATED);
                lstParam.add(userId);
                break;
            case 2://
                   // tim de review
                   //
                hql = "from FilesNoClob f," + " Process p," + " DetailProduct d," + " Business b"
                        + " where f.fileId = p.objectId" + " and f.detailProductId = d.detailProductId"
                        + " and f.deptId = b.businessId" + " and f.isActive = 1"
                        + " and (f.isTemp = null or f.isTemp = 0 )";
                hql += " and f.status = ?";
                lstParam.add(Constants.FILE_STATUS.EVALUATED);
                hql += " and p.receiveGroupId = ?";
                lstParam.add(deptId);
                hql += " and p.receiveUserId = ?";
                lstParam.add(userId);
                hql += " and ((f.leaderEvaluateId = ?" + " and f.leaderReviewId = null)"
                        + " or f.leaderReviewId =?)";
                lstParam.add(userId);
                lstParam.add(userId);
                // Lanh dao don vi duoc phan cong tham dinh, tim cac ho so duoc giao cho don vi minh tham dinh
                //hql += " and p.receiveGroupId = ? and ( p.receiveUserId = null or p.receiveUserId = ?) and (p.processStatus=? or p.processStatus =? )and p.status=? ";
                //hql += " and p.receiveGroupId = ? and (p.processStatus=? ";
                //            lstParam.add(deptId);
                //lstParam.add(userId);
                //            lstParam.add(Constants.FILE_STATUS.EVALUATED);
                //            lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_REVIEW);
                //            lstParam.add(0l);
                break;
            case 34:
                // tim de review
                hql += " and (f.status = ?)";
                lstParam.add(Constants.FILE_STATUS.EVALUATED);
                hql += " and p.receiveGroupId = ?" + " and p.receiveUserId = ?";
                lstParam.add(deptId);
                lstParam.add(userId);
                hql += " and f.leaderReviewId = ?";
                lstParam.add(userId);
                break;
            case 417://H s lnh o cc  ph duyt
                if (status == null) {
                    lstParam.clear();
                    hql = "from FilesNoClob f," + " DetailProduct d" + " where f.isActive=1 "
                            + " and f.detailProductId = d.detailProductId"
                            + " and (f.isTemp = null or f.isTemp = 0)" + " and f.isSignPdf <> 2"
                            + " and (f.status = ?)" + " and f.staffProcess=?";
                    lstParam.add(Constants.FILE_STATUS.APPROVED);
                    lstParam.add(userId);
                }
                break;
            case 422://H s VT  tr giy cng bn cng
                if (status == null) {
                    lstParam.clear();
                    hql = "from FilesNoClob f, DetailProduct d" + " where f.isActive=1 "
                            + " and f.detailProductId = d.detailProductId"
                            + " and (f.isTemp = null or f.isTemp = 0 ) " + " and f.isSignPdf = 2"
                            + " and (f.status = ?)" + " and f.staffProcess=?";
                    lstParam.add(Constants.FILE_STATUS.GIVE_BACK);
                    lstParam.add(userId);
                }
                break;
            case 423://H s VT  tr giy cng bn m?m
                if (status == null) {
                    lstParam.clear();
                    hql = "from FilesNoClob f, DetailProduct d" + " where f.isActive=1 "
                            + " and f.detailProductId = d.detailProductId"
                            + " and (f.isTemp = null or f.isTemp = 0 ) " + " and f.isSignPdf = 2"
                            + " and (f.status <> ?)" + " and f.staffProcess=?";
                    lstParam.add(Constants.FILE_STATUS.GIVE_BACK);
                    lstParam.add(userId);
                }
                break;
            // ho so lanh dao phong da xem xet
            case 18:
                if (status == null) {
                    lstParam.clear();
                    hql = "from FilesNoClob f, Process p, DetailProduct d" + " where f.isActive=1"
                            + " and f.fileId = p.objectId" + " and f.detailProductId = d.detailProductId"
                            + " and f.status = ?" + " and p.receiveUserId=?" + " and p.processType=1"
                            + " and (f.isTemp = null or f.isTemp = 0 )";
                    lstParam.add(Constants.FILE_STATUS.REVIEWED);
                    lstParam.add(userId);
                } else {
                    hql += " and f.status = ?";
                    lstParam.add(status);
                }
                break;
            case 45:
                if (status == null) {
                    lstParam.clear();
                    hql = " from FilesNoClob f, Process p, DetailProduct d" + " where f.isActive=1"
                            + " and f.fileId = p.objectId" + " and f.detailProductId = d.detailProductId"
                            + " and f.status = ?" + " and (f.isTemp = null or f.isTemp = 0)";
                    lstParam.add(Constants.FILE_STATUS.REVIEWED);
                    hql += " and f.staffProcess=?";
                    lstParam.add(userId);
                } else {
                    hql += " and f.status = ?";
                    lstParam.add(status);
                }
                break;
            case 19://H s lnh o cc yu cu b sung CV
                lstParam.clear();
                hql = " from FilesNoClob f, Process p, DetailProduct d " + " where f.isActive = 1"
                        + " AND (f.isTemp = null or f.isTemp = 0 )" + " AND f.fileId = p.objectId"
                        + " and f.detailProductId = d.detailProductId" + " AND f.status = ?"
                        + " AND p.receiveGroupId = ?" + " AND p.receiveUserId = ?";
                lstParam.add(Constants.FILE_STATUS.REVIEWED_TO_ADD);
                lstParam.add(deptId);
                lstParam.add(userId);
                break;
            case -26://H s  xem xt cv sbs ch? ph duyt cng vn
                lstParam.clear();
                hql = " from FilesNoClob f, Process p , DetailProduct d " + " where f.isActive = 1"
                        + " AND (f.isTemp = null or f.isTemp = 0 )" + " AND f.fileId = p.objectId"
                        + " and f.detailProductId = d.detailProductId" + " AND f.status = ?"
                        + " AND p.receiveGroupId = ?" + " AND p.receiveUserId = ?";
                lstParam.add(Constants.FILE_STATUS.REVIEW_TO_ADD);
                lstParam.add(deptId);
                lstParam.add(userId);
                break;
            case 33://H s ch? thm nh S?BS CV
                lstParam.clear();
                hql = " from FilesNoClob f," + " Process p," + " DetailProduct d " + " where f.isActive=1"
                        + " and f.fileId = p.objectId" + " and f.detailProductId = d.detailProductId"
                        + " and f.status = ?" + " and p.receiveGroupId =?"
                        //                            + " and p.receiveUserId=?"//141217u binhnt53
                        + " and f.staffProcess=?"//141217u binhnt53
                        + " and p.processType=1" + " and (f.isTemp = null or f.isTemp = 0 ) ";
                //                hql += " and (f.status = ?)";
                lstParam.add(Constants.FILE_STATUS.RECEIVED_TO_ADD);
                lstParam.add(deptId);
                lstParam.add(userId);
                break;
            case 20://H s ch? chuyn vin trong t thm nh
                lstParam.clear();
                hql = " from FilesNoClob f," + " Process p," + " DetailProduct d " + " where f.isActive=1"
                        + " and f.fileId = p.objectId" + " and f.detailProductId = d.detailProductId"
                        + " and f.status = ?" + " and p.receiveUserId=?" + " and p.processType=0"
                        + " and (f.isTemp = null or f.isTemp = 0 ) ";
                lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                lstParam.add(userId);
                break;
            case 21://H s gi phi hp cha cho  kin
                lstParam.clear();
                hql = " from FilesNoClob f, Process p , DetailProduct d" + " where f.isActive=1 "
                        + " and f.fileId = p.objectId " + " and f.detailProductId = d.detailProductId"
                        + " and (f.status = ?) " + " and (p.processType=0 or p.processType=4) "
                        + " and (p.processId in (select p.processId from Process p where p.processId not in (select distinct pc.processId from ProcessComment pc))) "
                        + " and p.processStatus = ?";
                lstParam.add(3l);
                lstParam.add(3l);
                if (deptId != null) {
                    hql += "and p.receiveGroupId = ? ";
                    lstParam.add(deptId);
                }
                if (userId != null) {
                    hql += "and p.receiveUserId = ? ";
                    lstParam.add(userId);
                }
                break;
            case 22://H s chuyn vin  gi thng bo sa i b sung
                lstParam.clear();
                hql = " from FilesNoClob f," + " Process p," + " DetailProduct d" + " where f.isActive = 1"
                        + " AND (f.isTemp = null or f.isTemp = 0 )" + " AND f.fileId = p.objectId"
                        + " and f.detailProductId = d.detailProductId " + " AND f.status = ?"
                        + " AND p.receiveGroupId = ?" + " AND p.receiveUserId = ?";
                lstParam.add(Constants.FILE_STATUS.EVALUATED_TO_ADD);
                lstParam.add(deptId);
                lstParam.add(userId);
                break;
            case 23:// dem ho so trong ngay chuyen vien
                if (status == null) {
                    lstParam.clear();
                    hql = " from FilesNoClob f," + " Process p," + " DetailProduct d " + " where f.isActive = 1"
                            + " and f.fileId = p.objectId" + " and f.detailProductId = d.detailProductId "
                            + " AND (f.status = ? or f.status = ?)"
                            + " and  to_date(f.sendDate,'yyyy/mm/dd') = to_date(sysdate,'yyyy/mm/dd')"
                            + " and p.receiveUserId=?" + " and p.processType=1"
                            + " and (f.isTemp = null or f.isTemp = 0 ) ";
                    lstParam.add(3l);
                    lstParam.add(5l);
                    lstParam.add(userId);

                } else {
                    hql += " and f.status = ? ";
                    lstParam.add(status);
                }
                break;
            case 24:
                if (status == null) {
                    lstParam.clear();
                    hql = "from FilesNoClob f," + " Process p," + " DetailProduct d " + " where f.isActive=1"
                            + " and f.fileId = p.objectId" + " and f.detailProductId = d.detailProductId "
                            + " and p.objectType = ?" + " and f.status in (?,?)"
                            + " and  to_date(f.sendDate,'yyyy/mm/dd') = to_date(sysdate,'yyyy/mm/dd')"
                            + " and (f.isTemp = null or f.isTemp = 0 ) ";
                    lstParam.add(Constants.OBJECT_TYPE.FILES);
                    lstParam.add(Constants.FILE_STATUS.REVIEWED);
                    lstParam.add(Constants.FILE_STATUS.EVALUATED);
                    hql += " and p.receiveGroupId = ?" + " and ( p.receiveUserId = null or p.receiveUserId = ?) ";
                    lstParam.add(deptId);
                    lstParam.add(userId);

                } else {
                    hql += " and f.status = ? ";
                    lstParam.add(status);
                }
                break;
            case 25://Tng h s x l trong ngy Lnh o cc
                if (status == null) {
                    lstParam.clear();
                    hql = "from FilesNoClob f," + " Process p," + " DetailProduct d " + " where f.isActive=1"
                            + " and f.fileId = p.objectId" + " and f.detailProductId = d.detailProductId "
                            + " and p.objectType = ?" + " and (f.status = ? or f.status = ? )"
                            + " and  to_date(f.sendDate,'yyyy/mm/dd') = to_date(sysdate,'yyyy/mm/dd')"
                            + " and (f.isTemp = null or f.isTemp = 0 ) ";
                    lstParam.add(Constants.OBJECT_TYPE.FILES);
                    lstParam.add(Constants.FILE_STATUS.REVIEWED);
                    lstParam.add(Constants.FILE_STATUS.APPROVED);
                    hql += "AND f.agencyId = ?)";
                    lstParam.add(deptId);

                } else {
                    hql += " and f.status = ? ";
                    lstParam.add(status);
                }
                break;
            case 26:// ho so bi tra tham dinh lai
                if (status == null) {//binhnt update 141211
                    lstParam.clear();
                    hql = "from FilesNoClob f," + " Process p," + " DetailProduct d " + " where f.isActive=1"
                            + " and f.fileId = p.objectId" + " and f.detailProductId = d.detailProductId "
                            + " and f.status = ?" + " and p.receiveUserId=?" + " and p.processType=1"
                            + " and (f.isTemp = null or f.isTemp = 0 ) ";
                    lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_EVALUATE);
                    lstParam.add(userId);

                } else {
                    hql += " and f.status = ? ";
                    lstParam.add(status);
                }
                break;
            case 28://H s  c  kin ca t thm xt
                lstParam.clear();
                hql = "from FilesNoClob f," + " Process p," + " ProcessComment pc," + " DetailProduct d "
                        + " where f.isActive=1" + " and f.fileId = p.objectId"
                        + " and f.detailProductId = d.detailProductId " + " and p.objectType = 30"
                        + " and (f.status = ?)"
                        + " and p.receiveGroupId = (select distinct p.receiveGroupId from Process p where p.receiveUserId = ?) and (p.processType=0 or p.processType=4)"
                        + " and pc.processId = p.processId" + " and (f.isTemp = null or f.isTemp = 0 ) ";
                lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                lstParam.add(userId);
                break;
            case 29:// ho so cho xem xet SDBS
                hql += " and f.status = ? and p.receiveGroupId = ? ";
                lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_ADD);
                lstParam.add(deptId);
                hql += " and p.receiveUserId = ? ";
                lstParam.add(userId);
                hql += " and (f.leaderReviewId =?)"; ////150204 binhnt53 update
                lstParam.add(userId);
                break;
            case 47:// ho so cho xem xet SDBS
                hql += " and f.status = ?";
                lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_ADD);
                hql += " and f.staffProcess = ? ";
                lstParam.add(userId);
                break;
            case 35:// ho so cho xem xet du thao sdbs
                hql += " and f.status = ? ";
                lstParam.add(Constants.FILE_STATUS.EVALUATE_TO_ADD);
                hql += " and p.receiveGroupId = ? ";
                lstParam.add(deptId);
                hql += " and p.receiveUserId = ? ";
                lstParam.add(userId);
                hql += " and f.leaderReviewId = ? ";
                lstParam.add(userId);
                break;
            case 30:
                if (status == null) {
                    hql += " and f.status = ? and p.receiveGroupId = ? ";
                    lstParam.add(Constants.FILE_STATUS.COMPARED);
                    lstParam.add(deptId);

                } else {
                    hql += " and f.status = ? ";
                    lstParam.add(status);
                }
                break;
            case 31:
                if (status == null) {
                    hql += " and f.status = ? and p.receiveGroupId = ? ";
                    lstParam.add(Constants.FILE_STATUS.REVIEW_COMPARISON_FAIL);
                    lstParam.add(deptId);

                } else {
                    hql += " and f.status = ? ";
                    lstParam.add(status);
                }
                break;
            case 3:
                // tim de phe duyet
                if (status != null) {
                    hql += " and (f.status = ?)";
                    lstParam.add(status);
                } else {
                    hql += " and (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.REVIEWED);
                }
                if (deptId != null) {
                    hql += " and p.receiveGroupId = ? ";
                    lstParam.add(deptId);
                }
                if (userId != null) {
                    hql += " and f.leaderApproveId = ? ";
                    lstParam.add(userId);
                }
                // Phe duyet cac ho so ban dau gui den cho don vi minh
                hql += " and f.agencyId = ?";
                lstParam.add(deptId);
                break;
            case 36:
                hql += " and f.status in (?,?,?,?,?,?)" + " and f.isActive = 1 "
                        + " and (f.isTemp is null or f.isTemp = 0)";
                lstParam.add(Constants.FILE_STATUS.GIVE_BACK);
                lstParam.add(Constants.FILE_STATUS.ALERT_COMPARISON);
                lstParam.add(Constants.FILE_STATUS.COMPARED);
                lstParam.add(Constants.FILE_STATUS.REVIEW_COMPARISON);
                lstParam.add(Constants.FILE_STATUS.COMPARED_FAIL);
                lstParam.add(Constants.FILE_STATUS.REVIEW_COMPARISON_FAIL);
                break;
            case 10:
                // tim de phe duyet
                if (status != null) {
                    hql += " and (f.status = ?)";
                    lstParam.add(status);
                } else {
                    hql += " and (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.APPROVED);
                }
                if (deptId != null) {
                    hql += " and p.receiveGroupId = ? ";
                    lstParam.add(deptId);
                }
                if (userId != null) {
                    hql += " and p.receiveUserId = ? ";
                    lstParam.add(userId);
                }
                // Phe duyet cac ho so ban dau gui den cho don vi minh
                hql += " and f.agencyId = ?";
                lstParam.add(deptId);
                break;
            case 226://ho so cho lanh dao phe duyet thong bao sdbs (26)
                hql = " from" + " FilesNoClob f," + " Process p," + " Business b," + " DetailProduct d"
                        + " where f.isActive=1" + " and f.fileId = p.objectId"
                        + " and f.detailProductId = d.detailProductId" + " and f.deptId = b.businessId"
                        + " and (f.isTemp = null or f.isTemp = 0 ) ";
                if (status != null) {
                    hql += " and (f.status = ?)";
                    lstParam.add(status);
                } else {
                    hql += " and (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.REVIEW_TO_ADD);
                }
                if (deptId != null) {
                    hql += " and p.receiveGroupId = ? ";
                    lstParam.add(deptId);
                }
                if (userId != null) {
                    hql += " and p.receiveUserId = ? ";
                    lstParam.add(userId);
                }
                // Phe duyet cac ho so ban dau gui den cho don vi minh
                hql += " and f.agencyId = ?";
                lstParam.add(deptId);
                break;
            case 6:
                if (status == null) {
                    hql += " and (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.APPROVED);

                } else {
                    hql = "from FilesNoClob f," + " DetailProduct d " + " where f.isActive=1"
                            + " and f.detailProductId = d.detailProductId "
                            + " and (f.isTemp = null or f.isTemp = 0 ) ";
                    hql += " and f.status = ? ";
                    lstParam.add(status);
                }
                if (deptId != null) {
                    hql += "AND f.agencyId = ? ";
                    lstParam.add(deptId);
                }
                break;
            case 16:
                // tim ho so doi chieu sai lech
                if (status == null) {
                    hql += " and (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.COMPARED_FAIL);
                } else {
                    hql += " and f.status = ? ";
                    lstParam.add(status);
                }
                if (deptId != null) {
                    hql += "AND f.agencyId = ? ";
                    lstParam.add(deptId);
                }
                break;
            case -2:
                // tim ho so da doi chieu
                lstParam.clear();
                hql = "from FilesNoClob f," + " Process p," + " DetailProduct d  " + " where f.isActive = 1 "
                        + " AND (f.isTemp = null or f.isTemp = 0 ) " + " AND f.fileId = p.objectId "
                        + " and f.detailProductId = d.detailProductId " + " AND p.sendUserId = ? "
                        + " AND p.receiveGroupId = ? " + " AND f.status = ? ";
                lstParam.add(deptId);
                lstParam.add(userId);
                lstParam.add(Constants.FILE_STATUS.APPROVED);
                break;
            case 27://H s sp ht hn b sung trc 5 ngy
                lstParam.clear();
                hql = "from FilesNoClob f," + " Process p," + " DetailProduct d  " + " where f.isActive = 1 "
                        + " AND (f.isTemp = null or f.isTemp = 0 ) " + " AND f.fileId = p.objectId "
                        + " and f.detailProductId = d.detailProductId " + " AND p.sendUserId = ? "
                        + " AND p.receiveGroupId = ? " + " AND (f.status = ?)";

                lstParam.add(deptId);
                lstParam.add(userId);
                lstParam.add(Constants.FILE_STATUS.EVALUATED_TO_ADD);
                hql += " AND sysdate - f.evaluateAddDate >= 54";
                break;
            case 32:
                lstParam.clear();
                hql = "from FilesNoClob f, Process p,DetailProduct d  " + " where f.isActive = 1 "
                        + " AND (f.isTemp = null or f.isTemp = 0 ) " + " AND f.fileId = p.objectId "
                        + " and f.detailProductId = d.detailProductId " + " AND p.sendUserId = ? "
                        + " AND p.receiveGroupId = ? ";
                lstParam.add(deptId);
                lstParam.add(userId);
                hql += " AND f.status in (?,?,?)";
                lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                lstParam.add(Constants.FILE_STATUS.RECEIVED_TO_ADD);
                lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_EVALUATE);
                hql += " AND (p.processStatus=? or p.processStatus=?)";
                lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                lstParam.add(Constants.FILE_STATUS.RECEIVED_TO_ADD);
                hql += " AND p.status=?";
                lstParam.add(0l);
                hql += " AND p.processType=?";
                lstParam.add(Constants.PROCESS_TYPE.COOPERATE);
                break;
            case 9://tim de pho phong vao tham dinh
                hql += " and (f.status = ? or f.status = ?)";
                lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_ADD);
                lstParam.add(Constants.FILE_STATUS.EVALUATED);
                // Chi tim ho so giao cho ca nhan xu ly thoi
                hql += " and p.receiveGroupId = ?" + " and p.receiveUserId = ?"
                        + " and (p.processStatus=? or p.processStatus =? )" + " and p.status=?"
                        + " and p.processType=?";
                lstParam.add(deptId);
                lstParam.add(userId);
                lstParam.add(Constants.FILE_STATUS.EVALUATED);
                lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_ADD);
                lstParam.add(0l);
                lstParam.add(Constants.PROCESS_TYPE.MAIN);
                if (userId != null) {
                    hql += " and f.leaderEvaluateId = ?";
                    lstParam.add(userId);
                }
                break;
            }
            Query countQuery = getSession().createQuery("select count(distinct f.fileId) " + hql);
            for (int i = 0; i < lstParam.size(); i++) {
                countQuery.setParameter(i, lstParam.get(i));
            }

            int total = Integer.parseInt(countQuery.uniqueResult().toString());
            return total;
        } catch (Exception ex) {
            LogUtil.addLog(ex);//binhnt sonar a160901
            return 0;
        }
    }

    public int getCountFileOnHomePage(Long userId, Long deptId, Long searchType, Long status) {//firstpagecount
        try {
            String hql = " from FilesNoClob f, Process p , DetailProduct d " + "where f.isActive=1 "
                    + " and f.detailProductId = d.detailProductId"
                    + "and f.fileId = p.objectId and p.objectType = ? and (f.isTemp = null or f.isTemp = 0 ) ";
            List lstParam = new ArrayList();
            lstParam.add(Constants.OBJECT_TYPE.FILES);
            switch (Integer.parseInt(searchType.toString())) { //dvanthu
            case 22://Ho so lanh dao cuc da phe duyet ho so
                hql = " from FilesNoClob f, Process p , DetailProduct d " + " where f.isActive = 1"
                        + " and f.fileId = p.objectId" + " and f.detailProductId = d.detailProductId"
                        + " and (f.isTemp = null or f.isTemp = 0 ) ";
                lstParam.clear();
                hql += " and f.status in ( ?,?,?,?,?,?)";
                //                    lstParam.add(Constants.FILE_STATUS.APPROVED);
                lstParam.add(Constants.FILE_STATUS.REVIEW_COMPARISON_FAIL);
                lstParam.add(Constants.FILE_STATUS.COMPARED_FAIL);
                lstParam.add(Constants.FILE_STATUS.REVIEW_COMPARISON);
                lstParam.add(Constants.FILE_STATUS.COMPARED);
                lstParam.add(Constants.FILE_STATUS.ALERT_COMPARISON);
                lstParam.add(Constants.FILE_STATUS.GIVE_BACK);
                hql += " and p.receiveGroupId = ?";
                lstParam.add(deptId);
                break;
            case 15://Ho so can thong bao doi chieu
                hql = " from FilesNoClob f, Process p , DetailProduct d " + " where f.isActive = 1"
                        + " and f.fileId = p.objectId" + " and f.detailProductId = d.detailProductId"
                        + " and (f.isTemp = null or f.isTemp = 0 ) ";
                lstParam.clear();
                hql += " and (f.status = ?)";
                lstParam.add(Constants.FILE_STATUS.COMPARED);
                hql += " and f.agencyId = ?";
                lstParam.add(deptId);
                hql += " and f.isFee = 1 and f.isSignPdf = 2";
                break;
            case 6://Ho so can thong bao doi chieu
                hql = " from FilesNoClob f, Process p, DetailProduct d " + " where f.isActive = 1"
                        + " and f.fileId = p.objectId" + " and f.detailProductId = d.detailProductId "
                        + " and (f.isTemp = null or f.isTemp = 0 ) ";
                lstParam.clear();
                hql += " and (f.status = ?)";
                lstParam.add(Constants.FILE_STATUS.APPROVED);
                hql += " and f.agencyId = ?";
                lstParam.add(deptId);
                hql += " and f.isFee = 1 and f.isSignPdf = 2";
                hql += " and f.isDownload <> 1";//u150112 binhnt53
                break;
            case 20://h s  thng bo sdbs
                hql = " from FilesNoClob f, Process p, DetailProduct d " + " where f.isActive = 1"
                        + " and f.fileId = p.objectId" + " and f.detailProductId = d.detailProductId "
                        + " and (f.isTemp = null or f.isTemp = 0 ) ";
                lstParam.clear();
                hql += " and (f.status = ?)";
                lstParam.add(status);
                hql += " and p.sendGroupId = ? ";
                lstParam.add(deptId);
                break;
            case -27://H s cn gi cng vn S?BS cho doanh nghip
                hql = " from FilesNoClob f, Process p, DetailProduct d " + " where f.isActive = 1"
                        + " and f.fileId = p.objectId" + " and f.detailProductId = d.detailProductId "
                        + " and (f.isTemp = null or f.isTemp = 0 ) ";
                lstParam.clear();
                hql += " and (f.status = ?)";
                lstParam.add(status);
                hql += " and p.receiveGroupId = ? ";
                lstParam.add(deptId);
                break;
            case -6://h s cn vn th k xc nhn
                hql = " from FilesNoClob f, Process p, DetailProduct d " + " where f.isActive = 1"
                        + " and f.fileId = p.objectId" + " and f.detailProductId = d.detailProductId "
                        + " and f.isSignPdf = 1" + " and f.isFee = 1" + " and (f.isTemp = null or f.isTemp = 0 ) ";
                lstParam.clear();
                hql += " and (f.status = ?)";//6
                lstParam.add(Constants.FILE_STATUS.APPROVED);
                hql += " and f.agencyId = ?";
                lstParam.add(deptId);
                break;
            case -4://5- H s  yu cu np ph cp s = ? ph duyt, cha np l ph (files.status = 6, fee_payment_info.status = 0,fee.fee_type = 1 , files.isSignPdf=1)
                /*hql = " from FilesNoClob f, Fee fe, FeePaymentInfo fpi"
                 + " where f.fileId = fpi.fileId"
                 + " and (f.isTemp=null or f.isTemp=0)"
                 + " and fe.feeId = fpi.feeId"
                 + " and fe.feeType=1"
                 + " and f.isActive=1"
                 + " and fe.isActive=1"
                 + " and fpi.isActive=1"
                 + " and fpi.status=0"
                 + " and f.userSigned is not null"
                 + " and (f.status=?) and f.agencyId = ?";*/
                hql = " from FilesNoClob f, DetailProduct d " + " where (f.isTemp=null or f.isTemp=0)"
                        + " and f.isActive=1" + " and f.detailProductId = d.detailProductId "
                        + " and f.isFee <> 1 and f.isSignPdf <> 2" + " and (f.status=?) and f.agencyId = ?";
                lstParam.clear();
                lstParam.add(Constants.FILE_STATUS.APPROVED);
                lstParam.add(deptId);
                break;
            case -3://H s  np ph cp s, ch? tr h s = ? ph duyt,  np l ph (files.status = 6, fee_payment_info.status = 1, fee.fee_type=1, files.isSignPdf=2)
                hql = " from FilesNoClob f, Fee fe, FeePaymentInfo fpi, DetailProduct d "
                        + " where f.fileId = fpi.fileId and (f.isTemp=null or f.isTemp=0) and f.isActive=1"
                        + " and fe.feeId = fpi.feeId and fe.feeType=1 and fe.isActive=1" + " and fpi.isActive=1"
                        + " and fpi.status=1" + " and f.detailProductId = d.detailProductId "
                        + " and f.userSigned is not null" + " and f.isSignPdf = 2" + " and (f.status=?)"
                        + " and f.agencyId = ?";

                lstParam.clear();
                lstParam.add(Constants.FILE_STATUS.COMPARED);
                //                    lstParam.add(Constants.FILE_STATUS.APPROVED);
                lstParam.add(deptId);
                break;
            case -2://H s  yu cu np ph cp s = ? ph duyt, cha np l ph (files.status = 6, fee_payment_info.status = 0,fee.fee_type = 1 , files.isSignPdf=1)
                hql = " from FilesNoClob f, Fee fe, FeePaymentInfo fpi , DetailProduct d "
                        + " where f.fileId = fpi.fileId and (f.isTemp=null or f.isTemp=0) and f.isActive=1"
                        + " and fe.feeId = fpi.feeId and fe.feeType=1 and fe.isActive=1" + " and fpi.isActive=1"
                        + " and f.detailProductId = d.detailProductId " + " and fpi.status=0"
                        + " and f.userSigned is not null" + " and f.isSignPdf = 1" + " and (f.status=?)"
                        + " and f.agencyId = ?";

                lstParam.clear();
                lstParam.add(Constants.FILE_STATUS.APPROVED);
                lstParam.add(deptId);
                break;

            // hieptq update nhom thuc pham thuong 201114
            //hieptq update 261214 sua query nhomsp
            case -1://H s ch? tip nhn = Mi np v  xc nhn ph (files.status = 1, fee_payment_info.status = 1, fee.fee_type=2), Mi np S?BS (18)
                hql = " from FilesNoClob f, Fee fe, FeePaymentInfo fpi, Category c , DetailProduct d "
                        + " where f.fileId = fpi.fileId and (f.isTemp=null or f.isTemp=0) and f.isActive=1"
                        + " and fe.feeId = fpi.feeId and fe.feeType=2 and fe.isActive=1" + " and fpi.isActive=1"
                        + " and f.productTypeId = c.categoryId " + " and f.detailProductId = d.detailProductId "
                        + " and fpi.status=1" + " and f.userSigned is not null"
                        + " and (f.status=?) and f.agencyId = ?" + " AND (c.code <> ? and c.code <> ?) ";

                //                    hql = " from FilesNoClob f, Fee fe, FeePaymentInfo fpi, Category c ";
                //                    condition = " and f.fileId = fpi.fileId and (f.isTemp=null or f.isTemp=0) and f.isActive=1"
                //                            + " and fe.feeId = fpi.feeId and fe.feeType=2 and fe.isActive=1"
                //                            + " and  f.productTypeId = c.categoryId "
                //                            + " and fpi.isActive=1"
                //                            + " and fpi.status=1"
                //                            + " and f.userSigned is not null"
                //                            + " and f.status=? and f.agencyId = ?";
                //                    ;
                lstParam.clear();
                lstParam.add(Constants.FILE_STATUS.NEW);
                lstParam.add(deptId);
                lstParam.add(Constants.CATEGORY_TYPE.TPCN);
                lstParam.add(Constants.CATEGORY_TYPE.DBT);
                //                    Query queryCheckFileId = getSession().createQuery("select f.fileId " + hql);
                //
                //                    // hieptq 181114 tach nhom san pham
                //                    for (int i = 0; i < lstParam.size(); i++) {
                //                        queryCheckFileId.setParameter(i, lstParam.get(i));
                //                    }
                //                    // loc fileId all
                //                    List<Long> lstResultCheckFileId = new ArrayList<Long>();
                //                    lstResultCheckFileId = queryCheckFileId.list();
                //                    String lstFileId = "";
                //                    for (int i = 0; i < lstResultCheckFileId.size(); i++) {
                //                        if (i == (lstResultCheckFileId.size() - 1)) {
                //                            lstFileId += lstResultCheckFileId.get(i).toString();
                //                        } else {
                //                            lstFileId += lstResultCheckFileId.get(i).toString() + ",";
                //                        }
                //                    }
                //                    if ("".equals(lstFileId)) {
                //                        lstFileId = "0";
                //                    }
                //                    // loc file_id nhom san pham khac
                //                    Query hql_1 = getSession().createQuery("select fpi.fileId from FeePaymentInfo fpi where fpi.fileId in (" + lstFileId + ") and fpi.cost = 500000 ");
                //                    List<Long> lsthql_1 = new ArrayList<Long>();
                //                    lsthql_1 = hql_1.list();
                //                    String hql1_FileId = "";
                //                    for (int i = 0; i < lsthql_1.size(); i++) {
                //                        if (i == (lsthql_1.size() - 1)) {
                //                            hql1_FileId += lsthql_1.get(i).toString();
                //                        } else {
                //                            hql1_FileId += lsthql_1.get(i).toString() + ",";
                //                        }
                //                    }
                //                    if ("".equals(hql1_FileId)) {
                //                        hql1_FileId = "0";
                //                    }
                //                    hql += "AND f.fileId in (" + hql1_FileId + ")";

                break;

            // hieptq update nhom thuc pham khac 201114
            case -8:
                hql = " from FilesNoClob f, Fee fe, FeePaymentInfo fpi,Category c, DetailProduct d "
                        + " where f.fileId = fpi.fileId" + " AND (f.isTemp=null or f.isTemp=0)"
                        + " AND f.isActive=1" + " AND fe.feeId = fpi.feeId" + " AND fe.feeType=2"
                        + " AND fe.isActive=1" + " AND f.productTypeId = c.categoryId "
                        + " and f.detailProductId = d.detailProductId " + " AND fpi.isActive=1"
                        + " AND fpi.status=1" + " AND f.userSigned is not null" + " AND (f.status=?)"
                        + " AND f.agencyId = ?" + " AND (c.code = ? or c.code = ?) ";

                lstParam.clear();
                lstParam.add(Constants.FILE_STATUS.NEW);
                lstParam.add(deptId);
                lstParam.add(Constants.CATEGORY_TYPE.TPCN);
                lstParam.add(Constants.CATEGORY_TYPE.DBT);
                //                    Query queryCheckFileId2 = getSession().createQuery("select f.fileId " + hql);
                //                    // hieptq 181114 tach nhom san pham
                //                    for (int i = 0; i < lstParam.size(); i++) {
                //                        queryCheckFileId2.setParameter(i, lstParam.get(i));
                //                    }
                //                    // loc fileId all
                //                    List<Long> lstResultCheckFileId2 = new ArrayList<Long>();
                //                    lstResultCheckFileId2 = queryCheckFileId2.list();
                //                    String lstFileId2 = "";
                //                    for (int i = 0; i < lstResultCheckFileId2.size(); i++) {
                //                        if (i == (lstResultCheckFileId2.size() - 1)) {
                //                            lstFileId2 += lstResultCheckFileId2.get(i).toString();
                //                        } else {
                //                            lstFileId2 += lstResultCheckFileId2.get(i).toString() + ",";
                //                        }
                //                    }
                //                    if ("".equals(lstFileId2)) {
                //                        lstFileId2 = "0";
                //                    }
                //                    Query hql_2 = getSession().createQuery("select fpi.fileId from FeePaymentInfo fpi where fpi.fileId in (" + lstFileId2 + ") and fpi.cost = 1500000 ");
                //                    List<Long> lsthql_2 = new ArrayList<Long>();
                //                    lsthql_2 = hql_2.list();
                //                    String hql1_FileId2 = "";
                //                    for (int i = 0; i < lsthql_2.size(); i++) {
                //                        if (i == (lsthql_2.size() - 1)) {
                //                            hql1_FileId2 += lsthql_2.get(i).toString();
                //                        } else {
                //                            hql1_FileId2 += lsthql_2.get(i).toString() + ",";
                //                        }
                //                    }
                //                    if ("".equals(hql1_FileId2)) {
                //                        hql1_FileId2 = "0";
                //                    }
                //                    hql += "AND f.fileId in (" + hql1_FileId2 + ")";

                break;

            case -7://H s ch? k ton xc nhn
                hql = " from FilesNoClob f, Fee fe, FeePaymentInfo fpi, DetailProduct d "
                        + " where f.fileId = fpi.fileId" + " AND (f.isTemp=null or f.isTemp=0)"
                        + " AND f.isActive=1" + " and f.detailProductId = d.detailProductId "
                        + " AND fe.feeId = fpi.feeId" + " AND fe.feeType=2" + " AND fe.isActive=1"
                        + " AND fpi.isActive=1" + " AND fpi.status > 2" + " AND f.userSigned is not null"
                        + " AND (f.status=?)" + " AND f.agencyId = ?";
                lstParam.clear();
                lstParam.add(Constants.FILE_STATUS.NEW);
                lstParam.add(deptId);
                break;
            case 0:
                hql = " from FilesNoClob f, Process p, DetailProduct d " + " where f.isActive = 1"
                        + " and f.fileId = p.objectId" + " and f.detailProductId = d.detailProductId "
                        + " and (f.isTemp = null or f.isTemp = 0 ) ";
                lstParam.clear();
                hql += " and (f.status = ?)";
                lstParam.add(status);
                hql += " and p.receiveGroupId = ? ";
                lstParam.add(deptId);
                break;
            default:
            }

            Query countQuery = getSession().createQuery("select count(distinct f.fileId) " + hql);
            for (int i = 0; i < lstParam.size(); i++) {
                countQuery.setParameter(i, lstParam.get(i));
            }

            int total = Integer.parseInt(countQuery.uniqueResult().toString());
            return total;
        } catch (Exception ex) {
            LogUtil.addLog(ex);//binhnt sonar a160901
            return 0;
        }
    }

    /**
     * H s sp qu hn
     *
     * @param userId
     * @param deptId
     * @param searchType
     * @param status
     * @return
     */
    public int getCountFileToProcessAlarm(Long userId, Long deptId, Long searchType, Long status) {
        try {
            Date alarmDate = new Date();
            try {
                alarmDate = getSysdate();
            } catch (Exception ex) {
                LogUtil.addLog(ex);//binhnt sonar a160901
            }
            //
            // tim cac ho so co deadline dien ra trong 2 ngay toi
            //
            alarmDate = DateTimeUtils.getAddDate(alarmDate, 0, 3);
            alarmDate.setHours(0);
            alarmDate.setMinutes(0);
            alarmDate.setSeconds(0);

            String hql = " from FilesNoClob f, Process p" + " where f.isActive=1" + " and f.fileId = p.objectId"
                    + " and p.objectType = ?" + " and f.deadline <= ?" + " and (f.isTemp = null or f.isTemp = 0 ) ";
            List lstParam = new ArrayList();
            lstParam.add(Constants.OBJECT_TYPE.FILES);
            lstParam.add(alarmDate);

            if (searchType == 1l) {
                //
                // tim de tham dinh
                //
                if (status == null) {
                    hql += " and f.status in (?,?)";
                    lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                    lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_EVALUATE);
                } else {
                    hql += " and f.status = ? ";
                    lstParam.add(status);
                }
                //
                // Chi tim ho so giao cho ca nhan xu ly thoi
                //
                hql += " and p.receiveGroupId = ?" + " and p.receiveUserId = ?"
                        + " and (p.processStatus=? or p.processStatus =? )"
                        + " and p.status=? and p.processType = ?";
                lstParam.add(deptId);
                lstParam.add(userId);
                lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_EVALUATE);
                lstParam.add(0l);
                lstParam.add(Constants.PROCESS_TYPE.MAIN);
            } else if (searchType == 4l) {
                //
                // tim de phoi hop tham dinh
                //
                if (status == null) {
                    hql += " and (f.status = ? or f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                    lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_EVALUATE);
                } else {
                    hql += " and f.status = ? ";
                    lstParam.add(status);
                }
                //
                // Chi tim ho so giao cho ca nhan xu ly thoi
                //
                hql += " and p.receiveGroupId = ?" + " and p.receiveUserId = ?"
                        + " and (p.processStatus=? or p.processStatus =? )"
                        + " and p.status=? and p.processType = ? ";
                lstParam.add(deptId);
                lstParam.add(userId);
                lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_EVALUATE);
                lstParam.add(0l);
                lstParam.add(Constants.PROCESS_TYPE.COOPERATE);
            } else if (searchType == 2l) {
                //
                // tim de review
                //
                //            if (status == null) {
                hql += " and (f.status = ? or f.status = ?)";
                lstParam.add(Constants.FILE_STATUS.EVALUATED);
                lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_REVIEW);
                //            } else {
                //                hql += " and f.status = ? ";
                //                lstParam.add(status);
                //            }
                //
                // Lanh dao don vi duoc phan cong tham dinh, tim cac ho so duoc giao cho don vi minh tham dinh
                //
                hql += " and p.receiveGroupId = ?" + " and ( p.receiveUserId = null or p.receiveUserId = ?)"
                        + " and (p.processStatus=? or p.processStatus =? )" + " and p.status=? ";
                lstParam.add(deptId);
                lstParam.add(userId);
                lstParam.add(Constants.FILE_STATUS.EVALUATED);
                lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_REVIEW);
                lstParam.add(0l);

            } else if (searchType == 3l) {
                //
                // tim de phe duyet
                //
                if (status == null) {
                    hql += " and (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.REVIEWED);
                } else {
                    hql += " and (f.status = ?)";
                    lstParam.add(status);
                }
                //
                // Phe duyet cac ho so ban dau gui den cho don vi minh
                //
                hql += " and p.receiveGroupId = ?" + " and ( p.receiveUserId = null or p.receiveUserId = ?)"
                        + " and p.processStatus=?" + " and p.status=? ";
                lstParam.add(deptId);
                lstParam.add(userId);
                lstParam.add(Constants.FILE_STATUS.REVIEWED);
                lstParam.add(0l);
            }

            Query countQuery = getSession().createQuery("select count(f) " + hql);
            for (int i = 0; i < lstParam.size(); i++) {
                countQuery.setParameter(i, lstParam.get(i));
            }

            int total = Integer.parseInt(countQuery.uniqueResult().toString());
            return total;
        } catch (HibernateException | NumberFormatException ex) {
            LogUtil.addLog(ex);//binhnt sonar a160901
            return 0;
        }

    }

    /**
     * Tra cuu
     *
     *
     * @return
     */
    public GridResult searchLookupFilesDonothing(FilesForm form, Long deptId, Long userId, String userType,
            int start, int count, String sortField) {

        try {
            //return createQueryLookupFiles(form, deptId, userId, userType, start, count, sortField);
            return createQueryLookupFiles(form, deptId, null, userType, start, count, sortField, "");
        } catch (Exception ex) {
            LogUtil.addLog(ex);//binhnt sonar a160901
            return new GridResult(0, null);
        }

        /*
         String hql = "";
         List lstParam = new ArrayList();
         if (userType.equals(Constants.ROLES.LEAD_OFFICE_ROLE)) {
         hql = " from FilesNoClob f, Process p where f.isActive = 1 and f.fileId = p.objectId and (f.isTemp = null or f.isTemp = 0 ) ";
         }
         if (userType.equals(Constants.ROLES.STAFF_ROLE)) {
         hql = " from FilesNoClob f, Process p where f.isActive = 1 and f.fileId = p.objectId and (f.isTemp = null or f.isTemp = 0 ) ";
         }
         if (userType.equals(Constants.ROLES.LEAD_UNIT)) {
         hql = " from FilesNoClob f, Process p where f.isActive = 1 and f.fileId = p.objectId and (f.isTemp = null or f.isTemp = 0 ) ";
         }
         if (userType.equals(Constants.ROLES.CLERICAL_ROLE)) {
         hql = " from FilesNoClob f, Process p where f.isActive = 1 and f.fileId = p.objectId  and (f.isTemp = null or f.isTemp = 0 ) ";
         //140714 binhnt53
         if (userId != null) {
         hql += " and p.sendUserId = ? ";
         lstParam.add(userId);
         }//!140714 binhnt53
         }
            
         if (form != null) {
         if (form.getFileCode() != null && !"".equals(form.getFileCode().trim())) {
         hql += "AND lower(f.fileCode) like ? ESCAPE '/' ";
         lstParam.add(StringUtils.toLikeString(form.getFileCode().toLowerCase().trim()));
         }
         if (form.getFileType() != null && form.getFileType().longValue() != -1) {
         hql += " AND f.fileType = ? ";
         lstParam.add(form.getFileType());
         }
            
         if (form.getSignDate() != null) {
         hql += "AND f.signDate = ? ";
         lstParam.add(form.getSignDate());
         }
         if (form.getStatus() != null && form.getStatus() >= 0l) {
         hql += "AND f.status = ? ";
         lstParam.add(form.getStatus());
         }
         if (form.getStatus() != null && form.getStatus() == 30l) {
         lstParam.clear();
         hql = "from FilesNoClob f, Process p where f.isActive = 1 and f.fileId = p.objectId AND f.status = ?  and (f.isTemp = null or f.isTemp = 0 ) ";
         lstParam.add(20l);
         }
         // thong ke ho so trong ngay
         if (form.getStatus() != null && form.getStatus() == 40l) {
         lstParam.clear();
         hql = "from FilesNoClob f, Process p where f.isActive = 1 and f.fileId = p.objectId AND (f.status = ? or f.status = ?) and  to_date(f.sendDate,'yyyy/mm/dd') = to_date(sysdate,'yyyy/mm/dd') and p.receiveUserId=? and p.processType=1 and (f.isTemp = null or f.isTemp = 0 ) ";
         lstParam.add(3l);
         lstParam.add(5l);
         lstParam.add(userId);
         }
         //searchtype 5
         if (form.getSearchType() != null && form.getSearchType() == 5l) {
         lstParam.clear();
         hql = "from FilesNoClob f, Process p where f.isActive = 1 and f.fileId = p.objectId AND f.status = ?  and (f.isTemp = null or f.isTemp = 0 ) ";
         lstParam.add(5l);
         }
            
         // ho so can doi chieu
         if (form.getSearchType() != null && form.getSearchType() == 23l) {
         lstParam.clear();
         hql = "from FilesNoClob f, Process p where f.isActive = 1 and f.fileId = p.objectId AND f.status = ?  and (f.isTemp = null or f.isTemp = 0 ) and p.receiveGroupId = ? ";
         lstParam.add(23l);
         lstParam.add(deptId);
            
         }
         // ho so cho tham dinh sdbs
         if (form.getSearchType() != null && form.getSearchType() == 44l) {
         lstParam.clear();
         hql = "from FilesNoClob f, Process p where f.isActive=1 and f.fileId = p.objectId and f.status = ? and p.receiveUserId=? and p.processType=1 and (f.isTemp = null or f.isTemp = 0 ) ";
         lstParam.add(Constants.FILE_STATUS.RECEIVED_TO_ADD);
         lstParam.add(userId);
         }
            
         if (form.getStatus() != null && form.getStatus() == 41l) {
         lstParam.clear();
         hql = "from FilesNoClob f, Process p where f.isActive = 1 and f.fileId = p.objectId AND (f.status = ? or f.status = ?) and  to_date(f.sendDate,'yyyy/mm/dd') = to_date(sysdate,'yyyy/mm/dd') and (f.isTemp = null or f.isTemp = 0 ) ";
         lstParam.add(4l);
         lstParam.add(5l);
         }
         if (form.getStatus() != null && form.getStatus() == 42l) {
         lstParam.clear();
         hql = "from FilesNoClob f, Process p where f.isActive = 1 and f.fileId = p.objectId AND (f.status = ? or f.status = ?) and  to_date(f.sendDate,'yyyy/mm/dd') = to_date(sysdate,'yyyy/mm/dd') and (f.isTemp = null or f.isTemp = 0 ) ";
         lstParam.add(6l);
         lstParam.add(5l);
         }
         // ho so bi tra tham dinh lai
         if (form.getStatus() != null && form.getStatus() == 43l) {
         lstParam.clear();
         hql = "from FilesNoClob f, Process p where f.isActive=1 and f.fileId = p.objectId and f.status = ? and p.receiveUserId=? and p.processType=1 and (f.isTemp = null or f.isTemp = 0 ) ";
         lstParam.add(8l);
         lstParam.add(userId);
         }
         // ho so cho phoi hop tham dinh chua cho y kien
         if (form.getStatus() != null && form.getStatus() == 44l) {
         lstParam.clear();
         hql = "from FilesNoClob f, Process p where f.isActive=1 and f.fileId = p.objectId and (f.status = ?) and (p.processType=0 or p.processType=4) and (p.processId in (select p.processId from Process p where p.processId not in (select distinct pc.processId from ProcessComment pc))) and p.processStatus = ?";
         lstParam.add(3l);
         lstParam.add(3l);
         // lstParam.add(userId);
         }
         //ho so cho xem xet
            
         if (form.getStatus() != null && form.getStatus() == 45l) {
         lstParam.clear();
         hql = "from FilesNoClob f, Process p where f.isActive=1 and f.fileId = p.objectId and f.status = ? and p.receiveUserId=? and (p.processType=1 or p.processType=0) and (f.isTemp = null or f.isTemp = 0 ) ";
         lstParam.add(4l);
         lstParam.add(userId);
         }
         // ho so lanh dao phong da xem xet
         if (form.getStatus() != null && form.getStatus() == 46l) {
         lstParam.clear();
         hql = "from FilesNoClob f, Process p where f.isActive=1 and f.fileId = p.objectId and f.status = ? and p.receiveUserId=? and p.processType=1 and (f.isTemp = null or f.isTemp = 0 ) ";
         lstParam.add(5l);
         lstParam.add(userId);
         }
         //ho so da phe duyet
         if (form.getStatus() != null && form.getStatus() == 47l) {
         lstParam.clear();
         hql = "from FilesNoClob f, Process p where f.isActive=1 and f.fileId = p.objectId and f.status = ? and p.receiveUserId=? and p.processType=1 and (f.isTemp = null or f.isTemp = 0 ) ";
         lstParam.add(Constants.FILE_STATUS.APPROVED);
         lstParam.add(userId);
         }
         // ho so tra lai yeu cau bo sung (status 9)
         if (form.getStatus() != null && form.getStatus() == 48l) {
         lstParam.clear();
         hql = "from FilesNoClob f, Process p where f.isActive=1 and f.fileId = p.objectId and f.status = ? and p.receiveUserId=? and p.processType=1 and (f.isTemp = null or f.isTemp = 0 ) ";
         lstParam.add(9l);
         lstParam.add(userId);
         }
         // ho so tham dnh da gui  kin phn hi
         if (form.getStatus() != null && form.getStatus() == 49l) {
         lstParam.clear();
         hql = "from FilesNoClob f, Process p,ProcessComment pc where f.isActive=1 and f.fileId = p.objectId and p.objectType = 30  and (f.status = ?) and p.receiveGroupId = (select distinct p.receiveGroupId from Process p where p.receiveUserId = ?) and (p.processType=0 or p.processType=1) and pc.processId = p.processId and (f.isTemp = null or f.isTemp = 0 ) ";
         lstParam.add(3l);
         lstParam.add(userId);
         }
         if (form.getStatus() != null && form.getStatus() == Constants.FILE_STATUS.REVIEW_COMPARISON_FAIL) {
         lstParam.clear();
         hql = "from FilesNoClob f, Process p where f.isActive = 1 and f.fileId = p.objectId AND f.status = ?  and (f.isTemp = null or f.isTemp = 0 ) ";
         lstParam.add(25l);
            
         }
         // ho so cho phe duyet
         if (form.getStatus() != null && form.getStatus() == 50l) {
         lstParam.clear();
         hql = "from FilesNoClob f, Process p where f.isActive=1 and f.fileId = p.objectId and (f.isTemp = null or f.isTemp = 0 ) ";
         hql += " and (f.status = ? or f.status = ? or f.status = ?)";
         lstParam.add(Constants.FILE_STATUS.ASSIGNED);
         lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_EVALUATE);
         lstParam.add(Constants.FILE_STATUS.RECEIVED_TO_ADD);
         hql += " and p.receiveGroupId = ? and p.receiveUserId = ? and (p.processStatus=? or p.processStatus =? or p.processStatus =? )and p.status=? and p.processType = ?";
         lstParam.add(deptId);
         lstParam.add(userId);
         lstParam.add(Constants.FILE_STATUS.ASSIGNED);
         lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_EVALUATE);
         lstParam.add(Constants.FILE_STATUS.RECEIVED_TO_ADD);
         lstParam.add(0l);
         lstParam.add(Constants.PROCESS_TYPE.MAIN);
         }
            
         // Haitv21 thm ch tiu thng tin tm kim
         // Do hieptq reset li params nn on code ny phi di cc case if_ else bn trn
         if (form.getSendDateFrom() != null) {
         hql += " AND f.sendDate >= ? ";
         lstParam.add(form.getSendDateFrom());
         }
         if (form.getSendDateTo() != null) {
         hql += " AND f.sendDate <= ? ";
         lstParam.add(addOneDay(form.getSendDateTo()));
         }
         // ngy thm kho lu tr
         if (form.getRepDateFrom() != null) {
         hql += " AND f.repDate >= ? ";
         lstParam.add(form.getRepDateFrom());
         }
         if (form.getRepDateTo() != null) {
         hql += " AND f.repDate <= ? ";
         lstParam.add(addOneDay(form.getRepDateTo()));
         }
            
         // Ngy ph duyt t ngy x ti ngy x
         if (form.getApproveDateFrom() != null) {
         hql += "AND f.approveDate >= ? ";
         lstParam.add(form.getApproveDateFrom());
         }
         if (form.getApproveDateTo() != null) {
         hql += " AND f.approveDate <= ? ";
         lstParam.add(addOneDay(form.getApproveDateTo()));
         }
            
         // Ng?i ph duyt
         if (form.getLeaderStaffSignName() != null && form.getLeaderStaffSignName().length() > 0) {
         hql += "AND lower(f.leaderStaffSignName) like ? ESCAPE '/' ";
         lstParam.add(StringUtils.toLikeString(form.getLeaderStaffSignName().toLowerCase().trim()));
         }
            
         // Tn doanh nghip
         if (form.getBusinessName() != null && form.getBusinessName().length() > 0) {
         hql += "AND lower(f.businessName) like ? ESCAPE '/' ";
         lstParam.add(StringUtils.toLikeString(form.getBusinessName().toLowerCase().trim()));
         }
         // Xut x
         if (form.getNationName() != null && form.getNationName().length() > 0) {
         hql += "AND lower(f.nationName) like ? ESCAPE '/' ";
         lstParam.add(StringUtils.toLikeString(form.getNationName().toLowerCase().trim()));
         }
            
         // Nh sn xut
         if (form.getManufactureName() != null && form.getManufactureName().length() > 0) {
         hql += "AND  (f.announcementId in (select ann.announcementId from Announcement ann where lower(ann.manufactureName) like ? ESCAPE '/'))";
         lstParam.add(StringUtils.toLikeString(form.getManufactureName().toLowerCase().trim()));
         }
         // Ng?i thm nh
         if (form.getReceiveUser() != null && form.getReceiveUser().length() > 0) {
         hql += "AND  (f.fileId in (select p.objectId from Process p where lower(p.receiveUser) like ? ESCAPE '/'))";
         lstParam.add(StringUtils.toLikeString(form.getReceiveUser().toLowerCase().trim()));
         }
            
         // S chng nhn cng b
         if (form.getAnnouncementNo() != null && form.getAnnouncementNo().length() > 0) {
         hql += "AND (f.announcementReceiptPaperId in (select a.announcementReceiptPaperId from AnnouncementReceiptPaper a where lower(a.receiptNo) like ? ESCAPE '/')) ";
         lstParam.add(StringUtils.toLikeString(form.getAnnouncementNo().toLowerCase().trim()));
         }
            
         // S ng k kinh doanh
         if (form.getBusinessLicence() != null && form.getBusinessLicence().length() > 0) {
         hql += "AND (f.deptId in (select b.businessId from Business b where lower(b.businessLicense) like ? ESCAPE '/')) ";
         lstParam.add(StringUtils.toLikeString(form.getBusinessLicence().toLowerCase().trim()));
         }
            
         // ?a ch doanh nghip
         if (form.getBusinessAddress() != null && form.getBusinessAddress().length() > 0) {
         hql += "AND lower(f.businessAddress) like ? ESCAPE '/' ";
         lstParam.add(StringUtils.toLikeString(form.getBusinessAddress().toLowerCase().trim()));
         }
            
         // Tn sn phm
         if (form.getProductName() != null && form.getProductName().length() > 0) {
         hql += "AND lower(f.productName) like ? ESCAPE '/' ";
         lstParam.add(StringUtils.toLikeString(form.getProductName().toLowerCase().trim()));
         }
            
         // Nhm sn phm
         if (form.getProductType() != null && form.getProductType() != -1l) {
         hql += "AND (f.detailProductId in ( select d.detailProductId from DetailProduct d where d.productType = ?)) ";
         lstParam.add(form.getProductType());
         }
            
         // Lnh o phng
         if (form.getLeaderStaff() != null && form.getLeaderStaff().length() > 0) {
         hql += "AND  (f.fileId in (select p.objectId from Process p where lower(p.receiveUser) like ? ESCAPE '/')) ";
         lstParam.add(StringUtils.toLikeString(form.getLeaderStaff().toLowerCase().trim()));
         }
            
         // Ng?i thm xt
         if (form.getStaff() != null && form.getStaff().length() > 0) {
         hql += "AND  (f.fileId in (select p.objectId from Process p where lower(p.receiveUser) like ? ESCAPE '/')) ";
         lstParam.add(StringUtils.toLikeString(form.getStaff().toLowerCase().trim()));
         }
            
         // Tnh -  Thnh Ph
         if (form.getProductType() != null && form.getBusinessProvinceId() != -1l) {
         hql += "AND (f.deptId in (select b.businessId from Business b where b.businessProvinceId= ? )) ";
         lstParam.add(form.getBusinessProvinceId());
         }
            
         // Ni lu tr
         if (form.getRepositoriesId() != null && form.getRepositoriesId() != -1l) {
         hql += "AND (f.repositoriesId = ? ) ";
         lstParam.add(form.getRepositoriesId());
         }
            
         // L?c theo ng?i to ( lu tr h s giy )
         if (form.getRepCreator() != null && (form.getSearchType() == 0 || form.getSearchType() == 2)) {
         hql += "AND (f.fileId in (select p.objectId  from Process p where p.processStatus = 3 and p.receiveUserId = ?)) ";
         lstParam.add(form.getRepCreator());
         }
            
         // Kho lu tr
         if (form.getRepName() != null && form.getRepName() != -1l) {
         hql += "AND (f.repositoriesId = ? )";
         lstParam.add(form.getRepName());
         }
            
         // Trng thi lu tr
         // ? lu tr
         if (form.getRepStatus() != null && form.getRepStatus() == 1) {
         hql += "AND (f.repositoriesId <> null )";
         }
         // Cha lu tr
         if (form.getRepStatus() != null && form.getRepStatus() == 2) {
         hql += "AND (f.repositoriesId = null )";
         }
            
         if (userType.equals(Constants.ROLES.LEAD_OFFICE_ROLE)) {
         if (deptId != null) {
         hql += "AND f.agencyId = ? ";
         lstParam.add(deptId);
         }
         }
         if (userType.equals(Constants.ROLES.STAFF_ROLE)) {
         if (deptId != null) {
         hql += "and p.receiveGroupId = ? ";
         lstParam.add(deptId);
         }
         }
         if (userType.equals(Constants.ROLES.LEAD_UNIT)) {
         if (deptId != null) {
         hql += " and p.receiveGroupId = ? and ( p.receiveUserId = null or p.receiveUserId = ?) ";
         lstParam.add(deptId);
         lstParam.add(userId);
         }
         }
         if (userType.equals(Constants.ROLES.CLERICAL_ROLE)) {
         if (deptId != null) {
         //                    hql += "AND f.agencyId = ?)";
         //                    lstParam.add(deptId);
         hql += "and p.receiveGroupId = ? ";
         lstParam.add(deptId);
         }
         }
            
         }
         if (userType.equals(Constants.ROLES.CLERICAL_ROLE) && form.getSearchType() != null) {
         switch (Integer.parseInt(form.getSearchType().toString())) {
         case -4://5- H s  yu cu np ph cp s = ? ph duyt, cha np l ph (files.status = 6, fee_payment_info.status = 0,fee.fee_type = 1 , files.isSignPdf=1)
         hql = " from FilesNoClob f, FeePaymentInfo fpi where f.fileId = fpi.fileId and (f.isTemp=null or f.isTemp=0) and f.isActive=1"
         + " and fpi.feeId in (select fe.feeId from Fee fe where fe.feeType=1 and fe.isActive=1)"
         + " and fpi.isActive=1"
         + " and fpi.status=0"
         + " and f.userSigned is not null"
         + " and (f.status=?) and f.agencyId = ?";
         lstParam.clear();
         lstParam.add(Constants.FILE_STATUS.APPROVED);
         lstParam.add(deptId);
         break;
         case -3://H s  np ph cp s, ch? tr h s = ? ph duyt,  np l ph (files.status = 6, fee_payment_info.status = 1, fee.fee_type=1, files.isSignPdf=2)
         hql = " from FilesNoClob f, FeePaymentInfo fpi where f.fileId = fpi.fileId and (f.isTemp=null or f.isTemp=0) and f.isActive=1"
         + " and fpi.feeId in (select fe.feeId from Fee fe where fe.feeType=1 and fe.isActive=1)"
         + " and fpi.isActive=1"
         + " and fpi.status=1"
         + " and f.userSigned is not null"
         + " and f.isSignPdf = 2"
         + " and (f.status=?)"
         + " and f.agencyId = ?";
            
         lstParam.clear();
         lstParam.add(Constants.FILE_STATUS.APPROVED);
         lstParam.add(deptId);
         break;
         case -2://H s  yu cu np ph cp s = ? ph duyt, cha np l ph (files.status = 6, fee_payment_info.status = 0,fee.fee_type = 1 , files.isSignPdf=1)
         hql = " from FilesNoClob f, FeePaymentInfo fpi where f.fileId = fpi.fileId and (f.isTemp=null or f.isTemp=0) and f.isActive=1"
         + " and fpi.feeId in (select fe.feeId from Fee fe where fe.feeType=1 and fe.isActive=1)"
         + " and fpi.isActive=1"
         + " and fpi.status=0"
         + " and f.userSigned is not null"
         + " and f.isSignPdf = 1"
         + " and (f.status=?)"
         + " and f.agencyId = ?";
         lstParam.clear();
         lstParam.add(Constants.FILE_STATUS.APPROVED);
         lstParam.add(deptId);
         break;
         case -1://H s ch? tip nhn = Mi np v  xc nhn ph (files.status = 1, fee_payment_info.status = 1, fee.fee_type=2), Mi np S?BS (18)
         hql = " from FilesNoClob f, FeePaymentInfo fpi where f.fileId = fpi.fileId and (f.isTemp=null or f.isTemp=0) and f.isActive=1"
         + " and fpi.feeId in (select fe.feeId from Fee fe where fe.feeType=2 and fe.isActive=1)"
         + " and fpi.isActive=1"
         + " and fpi.status=1"
         + " and f.userSigned is not null"
         + " and (f.status=? or f.status=?) and f.agencyId = ?";
         lstParam.clear();
         lstParam.add(Constants.FILE_STATUS.NEW);
         lstParam.add(Constants.FILE_STATUS.NEW_TO_ADD);
         lstParam.add(deptId);
         break;
         case 0:
         hql = " from FilesNoClob f, Process p where f.isActive = 1 and f.fileId = p.objectId and (f.isTemp = null or f.isTemp = 0 ) ";
         lstParam.clear();
         hql += " and (f.status = ?)";
         lstParam.add(form.getStatus());
         hql += " and p.receiveGroupId = ? ";
         lstParam.add(deptId);
         break;
         default:;
         }
         }
         Query countQuery = getSession().createQuery("select count(f) from FilesNoClob f where f.fileId in (select distinct f.fileId " + hql + ")");
         Query query = getSession().createQuery("select f from FilesNoClob f where f.fileId in ( select distinct f.fileId " + hql + ") order by f.sendDate ASC");
         for (int i = 0; i < lstParam.size(); i++) {
         query.setParameter(i, lstParam.get(i));
         countQuery.setParameter(i, lstParam.get(i));
         }
            
         query.setFirstResult(start);
         query.setMaxResults(count);
         int total = Integer.parseInt(countQuery.uniqueResult().toString());
         List<FilesNoClob> lstResult = query.list();
         GridResult gr = new GridResult(total, lstResult);
         return gr;
            
         */
    }

    public GridResult searchLookupFilesOnHomePage(FilesForm form, Long deptId, Long userId, String userType,
            int start, int count, String sortField) {
        try {
            String hql = "";
            List lstParam = new ArrayList();
            if (userType.equals(Constants.ROLES.CLERICAL_ROLE)) {
                if (form != null) {
                    if (form.getSearchType() != null) {
                        switch (Integer.parseInt(form.getSearchType().toString())) {
                        case 1:
                            hql = " from FilesNoClob f, Fee fe, FeePaymentInfo fpi" + " where f.fileId = fpi.fileId"
                                    + " and (f.isTemp=null or f.isTemp=0)" + " and f.isActive=1"
                                    + " and fe.feeId = fpi.feeId" + " and fe.feeType=2" + " and fe.isActive=1"
                                    + " and fpi.isActive=1" + " and fpi.status=1" + " and f.userSigned is not null"
                                    + " and (f.status=? or f.status=?)" + " and f.deptId = ?";
                            lstParam.clear();
                            lstParam.add(Constants.FILE_STATUS.NEW);
                            lstParam.add(Constants.FILE_STATUS.NEW_TO_ADD);
                            lstParam.add(deptId);
                            break;
                        default:
                        }
                    }
                }
            }
            Query countQuery = getSession().createQuery("select count(distinct f.fileId) " + hql);
            Query query = getSession().createQuery("select distinct f " + hql);
            for (int i = 0; i < lstParam.size(); i++) {
                query.setParameter(i, lstParam.get(i));
                countQuery.setParameter(i, lstParam.get(i));
            }

            query.setFirstResult(start);
            query.setMaxResults(count);
            int total = Integer.parseInt(countQuery.uniqueResult().toString());
            List<FilesNoClob> lstResult = query.list();
            GridResult gr = new GridResult(total, lstResult);
            return gr;
        } catch (Exception ex) {
            LogUtil.addLog(ex);//binhnt sonar a160901
            return new GridResult(0, null);
        }
    }

    /**
     * Tm h s  tip nhn hoc t chi tip nhn
     *
     * @param form
     * @param deptId
     * @param start
     * @param count
     * @param sortField
     * @return
     */
    public GridResult findAllFileForReceived(FilesForm form, Long deptId, int start, int count, String sortField) {
        try {
            String hql = " from FilesNoClob f, Fee fe, FeePaymentInfo fpi" + " where f.fileId = fpi.fileId"
                    + " and (f.isTemp=null or f.isTemp=0)" + " and f.isActive=1" + " and fe.feeId = fpi.feeId"
                    + " and fe.feeType=2" + " and fe.isActive=1" + " and fpi.isActive=1" + " and fpi.status=1"
                    + " and f.userSigned is not null" + " and (f.status=?)";
            //                    + " and (f.status=? or f.status=?)";//update bo luong tiep nhan sdbs van thu
            List lstParam = new ArrayList();
            lstParam.add(Constants.FILE_STATUS.NEW);
            //            lstParam.add(Constants.FILE_STATUS.NEW_TO_ADD);
            if (form != null) {
                if (form.getFileCode() != null && !"".equals(form.getFileCode().trim())) {
                    hql += "AND lower(f.fileCode) like ? ESCAPE '/' ";
                    lstParam.add(StringUtils.toLikeString(form.getFileCode().toLowerCase().trim()));
                }
                if (form.getFileType() != null && form.getFileType().longValue() != -1) {
                    hql += "AND f.fileType = ? ";
                    lstParam.add(form.getFileType());
                }
                if (form.getSendDateFrom() != null) {
                    hql += "AND f.sendDate >= ? ";
                    lstParam.add(form.getSendDateFrom());
                }
                if (form.getSendDateTo() != null) {
                    hql += "AND f.sendDate <= ? ";
                    lstParam.add(form.getSendDateTo());
                }
                if (deptId != null) {
                    hql += "AND f.agencyId = ? ";
                    lstParam.add(deptId);
                }
            }

            Query countQuery = getSession().createQuery("select count(distinct f.fileId) " + hql);
            Query query = getSession().createQuery("select distinct f " + hql + " " + "order by f.modifyDate DESC");
            for (int i = 0; i < lstParam.size(); i++) {
                query.setParameter(i, lstParam.get(i));
                countQuery.setParameter(i, lstParam.get(i));
            }

            query.setFirstResult(start);
            query.setMaxResults(count);
            int total = Integer.parseInt(countQuery.uniqueResult().toString());
            List<FilesNoClob> lstResult = query.list();
            GridResult gr = new GridResult(total, lstResult);
            return gr;
        } catch (Exception ex) {
            LogUtil.addLog(ex);//binhnt sonar a160901
            return new GridResult(0, null);
        }
    }

    /**
     * Tm h s  i chiu
     *
     * @param form
     * @param deptId
     * @param start
     * @param count
     * @param sortField
     * @return
     */
    public GridResult findAllFileForComparison(FilesForm form, Long deptId, int start, int count,
            String sortField) {
        try {
            String hql = " from FilesNoClob f where f.isActive=1 and (f.isTemp = null or f.isTemp = 0 ) ";
            List lstParam = new ArrayList();
            if (form != null) {

                hql += " and (f.status = ?)";
                lstParam.add(Constants.FILE_STATUS.APPROVED);

                if (form.getFileCode() != null && !"".equals(form.getFileCode().trim())) {
                    hql += "AND lower(f.fileCode) like ? ESCAPE '/' ";
                    lstParam.add(StringUtils.toLikeString(form.getFileCode().toLowerCase().trim()));
                }
                if (form.getFileType() != null && form.getFileType().longValue() != -1) {
                    hql += "AND f.fileType = ? ";
                    lstParam.add(form.getFileType());
                }
                if (form.getSendDateFrom() != null) {
                    hql += "AND f.sendDate >= ? ";
                    lstParam.add(form.getSendDateFrom());
                }
                if (form.getSendDateTo() != null) {
                    hql += "AND f.sendDate <= ? ";
                    lstParam.add(form.getSendDateTo());
                }
                if (deptId != null) {
                    hql += "AND f.agencyId = ? ";
                    lstParam.add(deptId);
                }
            }

            Query countQuery = getSession().createQuery("select count(distinct f.fileId) " + hql);
            Query query = getSession().createQuery("select distinct f " + hql + " order by f.modifyDate desc");
            for (int i = 0; i < lstParam.size(); i++) {
                query.setParameter(i, lstParam.get(i));
                countQuery.setParameter(i, lstParam.get(i));
            }

            query.setFirstResult(start);
            query.setMaxResults(count);
            int total = Integer.parseInt(countQuery.uniqueResult().toString());
            List<FilesNoClob> lstResult = query.list();
            //        for (FilesNoClob f : lstResult) {
            //            hql = "select p from Process p where p.objectType=? and p.objectId = ? and (p.processStatus=? or p.processType=? ) ";
            //            query = getSession().createQuery(hql);
            //            query.setParameter(0, Constants.OBJECT_TYPE.FILES);
            //            query.setParameter(1, f.getFileId());
            //            query.setParameter(2, Constants.FILE_STATUS.ASSIGNED);
            //            query.setParameter(3, Constants.PROCESS_TYPE.PROPOSE);
            //            List<Process> lstPro = query.list();
            //            if (lstPro.size() > 0) {
            //                StringBuilder processName = new StringBuilder("");
            //                for (int i = 0; i < lstPro.size(); i++) {
            //                    if (lstPro.get(i).getReceiveUser() != null) {
            //                        if (processName.length() == 0) {
            //                            processName.append(lstPro.get(i).getReceiveUser());
            //                        } else {
            //                            processName.append(",").append(lstPro.get(i).getReceiveUser());
            //                        }
            //                    }
            //                }
            //                f.setProposeUserName(processName.toString());
            //            }
            //        }
            GridResult gr = new GridResult(total, lstResult);
            return gr;
        } catch (Exception ex) {
            LogUtil.addLog(ex);//binhnt sonar a160901
            return new GridResult(0, null);
        }
    }

    /**
     * ?m h s khng c  kin thm xt
     *
     * @param userId
     * @param deptId
     * @param searchType
     * @param status
     * @return
     */
    public int getCountFileToProcessNotComment(Long userId, Long deptId, Long searchType, Long status) {
        try {
            String hql = " from FilesNoClob f, Process p where f.isActive=1 and f.fileId = p.objectId and p.objectType = ? and (f.isTemp = null or f.isTemp = 0 ) ";
            List lstParam = new ArrayList();
            lstParam.add(Constants.OBJECT_TYPE.FILES);

            if (searchType == 1l) {
                //
                // tim de tham dinh
                //
                if (status == null) {
                    hql += " and (f.status = ? or f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                    lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_EVALUATE);
                } else {
                    hql += " and f.status = ? ";
                    lstParam.add(status);
                }
                //
                // Chi tim ho so giao cho ca nhan xu ly thoi
                //
                hql += " and p.receiveGroupId = ? and p.receiveUserId = ? and (p.processStatus=? or p.processStatus =? )and p.status=? and p.processType = ?";
                lstParam.add(deptId);
                lstParam.add(userId);
                lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_EVALUATE);
                lstParam.add(0l);
                lstParam.add(Constants.PROCESS_TYPE.MAIN);
            } else if (searchType == 4l) {
                //
                // tim de phoi hop tham dinh
                //
                if (status == null) {
                    hql += " and (f.status = ? or f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                    lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_EVALUATE);
                } else {
                    hql += " and f.status = ? ";
                    lstParam.add(status);
                }
                //
                // Chi tim ho so giao cho ca nhan xu ly thoi
                //
                hql += " and p.receiveGroupId = ? and p.receiveUserId = ? and (p.processStatus=? or p.processStatus =? )and p.status=? and p.processType = ? ";
                lstParam.add(deptId);
                lstParam.add(userId);
                lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_EVALUATE);
                lstParam.add(0l);
                lstParam.add(Constants.PROCESS_TYPE.COOPERATE);
            } else if (searchType == -1l) {
                //
                // tim de tiep nhan
                //
                if (status == null) {
                    hql += " and (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.NEW);
                } else {
                    hql += " and f.status = ? ";
                    lstParam.add(status);
                }
                if (deptId != null) {
                    hql += "AND f.agencyId = ? ";
                    lstParam.add(deptId);
                }
                //            hql += " and ( p.receiveUserId = null or p.receiveUserId = ?) ";
                //            lstParam.add(userId);
            } else if (searchType == 2l) {
                //
                // tim de review
                //
                if (status == null) {
                    hql += " and (f.status = ? or f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.EVALUATED);
                    lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_REVIEW);
                } else {
                    hql += " and f.status = ? ";
                    lstParam.add(status);
                }
                //
                // Lanh dao don vi duoc phan cong tham dinh, tim cac ho so duoc giao cho don vi minh tham dinh
                //
                hql += " and p.receiveGroupId = ? and ( p.receiveUserId = null or p.receiveUserId = ?) and (p.processStatus=? or p.processStatus =? )and p.status=? ";
                lstParam.add(deptId);
                lstParam.add(userId);
                lstParam.add(Constants.FILE_STATUS.EVALUATED);
                lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_REVIEW);
                lstParam.add(0l);

            } else if (searchType == 3l) {
                //
                // tim de phe duyet
                //
                if (status == null) {
                    hql += " and (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.REVIEWED);
                } else {
                    hql += " and (f.status = ?)";
                    lstParam.add(status);
                }
                //
                // Phe duyet cac ho so ban dau gui den cho don vi minh
                //
                hql += " and p.receiveGroupId = ? and ( p.receiveUserId = null or p.receiveUserId = ?) and p.processStatus=? and p.status=? ";
                lstParam.add(deptId);
                lstParam.add(userId);
                lstParam.add(Constants.FILE_STATUS.REVIEWED);
                lstParam.add(0l);
            } else if (searchType == 0L) {
                /*tim de phan cong*/

                hql += " and (f.status = ? or f.status = ? or f.status = ?)";
                //                hql += " and (f.status = ? or f.status = ?)";
                lstParam.add(Constants.FILE_STATUS.NEW);
                lstParam.add(Constants.FILE_STATUS.RECEIVED);
                lstParam.add(Constants.FILE_STATUS.PROPOSED);
                if (deptId != null) {
                    hql += "AND (f.agencyId = ? or ( (p.receiveUserId = ? or p.receiveGroupId=? )"
                            + " and p.processStatus in (?,?,?,?) ) )";

                    lstParam.add(deptId);
                    lstParam.add(userId);
                    lstParam.add(deptId);
                    lstParam.add(Constants.FILE_STATUS.NEW);
                    lstParam.add(Constants.FILE_STATUS.RECEIVED);
                    lstParam.add(Constants.FILE_STATUS.PROPOSED);
                    lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                } else {
                    hql += "AND (p.receiveUserId = ?" + " and p.processStatus in (?,?,?)" + " and p.status=? ) ";
                    lstParam.add(userId);
                    lstParam.add(Constants.FILE_STATUS.NEW);
                    lstParam.add(Constants.FILE_STATUS.RECEIVED);
                    lstParam.add(Constants.FILE_STATUS.PROPOSED);
                    lstParam.add(0l);
                }
            }

            Query countQuery = getSession().createQuery("select count(distinct f.fileId) " + hql);
            for (int i = 0; i < lstParam.size(); i++) {
                countQuery.setParameter(i, lstParam.get(i));
            }

            int total = Integer.parseInt(countQuery.uniqueResult().toString());
            return total;
        } catch (Exception ex) {
            LogUtil.addLog(ex);//binhnt sonar a160901
            return 0;
        }
    }

    public GridResult reportLookup(FilesForm form, Long deptId, Long userId, String userType, int start, int count,
            String sortField, String sortCustom) {
        List lstParam = new ArrayList();
        String hql = " from FilesNoClob f, Process p";
        String condition = "";
        condition += " and f.isActive = 1" + " and f.fileId = p.objectId"
                + "  and (f.isTemp = null or f.isTemp = 0 )";
        if (form != null) {
            if (userType.equals(Constants.ROLES.CLERICAL_ROLE) && form.getSearchType() != null) {
                switch (Integer.parseInt(form.getSearchType().toString())) {
                case 1:
                    lstParam.clear();
                    hql = " from FilesNoClob f, Process p";
                    condition = " AND f.fileId = p.objectId";
                    condition += " AND f.isActive = 1 and (f.isTemp = null or f.isTemp = 0)";
                    condition += " AND p.processStatus = ?";
                    lstParam.add(Constants.FILE_STATUS.RECEIVED);
                    if (form.getSearchDateFrom() != null) {
                        condition += " and p.receiveDate >= to_date( ? ,'dd/MM/yyyy hh24:mi:ss') ";
                        String param = "" + com.viettel.common.util.DateTimeUtils
                                .convertDateToString(form.getSearchDateFrom(), "dd/MM/yyyy") + " 00:00:00";
                        lstParam.add(param);
                        //                            condition += " and p.receiveDate >= ?";
                        //                            lstParam.add(form.getSearchDateFrom());
                    }
                    if (form.getSearchDateTo() != null) {
                        //                            condition += " and p.receiveDate <= ?";
                        //                            lstParam.add(form.getSearchDateTo());
                        condition += " and p.receiveDate <= to_date( ? ,'dd/MM/yyyy hh24:mi:ss') ";
                        String param = "" + com.viettel.common.util.DateTimeUtils
                                .convertDateToString(form.getSearchDateTo(), "dd/MM/yyyy") + " 23:59:59";
                        lstParam.add(param);
                    }
                    break;
                case 2:
                    lstParam.clear();
                    hql = " from FilesNoClob f, Process p";
                    condition = " AND f.fileId = p.objectId";
                    condition += " AND f.isActive = 1 and (f.isTemp = null or f.isTemp = 0)";
                    condition += " AND p.processStatus = ?";
                    lstParam.add(Constants.FILE_STATUS.RECEIVED_TO_ADD);
                    //                        if (form.getSearchDateFrom() != null) {
                    //                            condition += " and p.receiveDate >= ?";
                    //                            lstParam.add(form.getSearchDateFrom());
                    //                        }
                    //                        if (form.getSearchDateTo() != null) {
                    //                            condition += " and p.receiveDate <= ?";
                    //                            lstParam.add(form.getSearchDateTo());
                    //                        }
                    if (form.getSearchDateFrom() != null) {
                        condition += " and p.receiveDate >= to_date( ? ,'dd/MM/yyyy hh24:mi:ss') ";
                        String param = "" + com.viettel.common.util.DateTimeUtils
                                .convertDateToString(form.getSearchDateFrom(), "dd/MM/yyyy") + " 00:00:00";
                        lstParam.add(param);
                        //                            condition += " and p.receiveDate >= ?";
                        //                            lstParam.add(form.getSearchDateFrom());
                    }
                    if (form.getSearchDateTo() != null) {
                        //                            condition += " and p.receiveDate <= ?";
                        //                            lstParam.add(form.getSearchDateTo());
                        condition += " and p.receiveDate <= to_date( ? ,'dd/MM/yyyy hh24:mi:ss') ";
                        String param = "" + com.viettel.common.util.DateTimeUtils
                                .convertDateToString(form.getSearchDateTo(), "dd/MM/yyyy") + " 23:59:59";
                        lstParam.add(param);
                    }
                    break;
                case 3:
                    lstParam.clear();
                    hql = " from FilesNoClob f, Process p";
                    condition = " AND f.fileId = p.objectId";
                    condition += " AND f.isActive = 1 and (f.isTemp = null or f.isTemp = 0)";
                    condition += " AND p.processStatus = ?";
                    lstParam.add(Constants.FILE_STATUS.EVALUATED_TO_ADD);
                    //                        if (form.getSearchDateFrom() != null) {
                    //                            condition += " and p.receiveDate >= ?";
                    //                            lstParam.add(form.getSearchDateFrom());
                    //                        }
                    //                        if (form.getSearchDateTo() != null) {
                    //                            condition += " and p.receiveDate <= ?";
                    //                            lstParam.add(form.getSearchDateTo());
                    //                        }
                    if (form.getSearchDateFrom() != null) {
                        condition += " and p.receiveDate >= to_date( ? ,'dd/MM/yyyy hh24:mi:ss') ";
                        String param = "" + com.viettel.common.util.DateTimeUtils
                                .convertDateToString(form.getSearchDateFrom(), "dd/MM/yyyy") + " 00:00:00";
                        lstParam.add(param);
                        //                            condition += " and p.receiveDate >= ?";
                        //                            lstParam.add(form.getSearchDateFrom());
                    }
                    if (form.getSearchDateTo() != null) {
                        //                            condition += " and p.receiveDate <= ?";
                        //                            lstParam.add(form.getSearchDateTo());
                        condition += " and p.receiveDate <= to_date( ? ,'dd/MM/yyyy hh24:mi:ss') ";
                        String param = "" + com.viettel.common.util.DateTimeUtils
                                .convertDateToString(form.getSearchDateTo(), "dd/MM/yyyy") + " 23:59:59";
                        lstParam.add(param);
                    }
                    break;
                case 4:
                    lstParam.clear();
                    hql = " from FilesNoClob f, Process p";
                    condition = " AND f.fileId = p.objectId";
                    condition += " AND f.isActive = 1 and (f.isTemp = null or f.isTemp = 0)";
                    condition += " AND p.processStatus = ?";
                    lstParam.add(Constants.FILE_STATUS.GIVE_BACK);
                    //                        if (form.getSearchDateFrom() != null) {
                    //                            condition += " and p.receiveDate >= ?";
                    //                            lstParam.add(form.getSearchDateFrom());
                    //                        }
                    //                        if (form.getSearchDateTo() != null) {
                    //                            condition += " and p.receiveDate <= ?";
                    //                            lstParam.add(form.getSearchDateTo());
                    //                        }
                    if (form.getSearchDateFrom() != null) {
                        condition += " and p.receiveDate >= to_date( ? ,'dd/MM/yyyy hh24:mi:ss') ";
                        String param = "" + com.viettel.common.util.DateTimeUtils
                                .convertDateToString(form.getSearchDateFrom(), "dd/MM/yyyy") + " 00:00:00";
                        lstParam.add(param);
                        //                            condition += " and p.receiveDate >= ?";
                        //                            lstParam.add(form.getSearchDateFrom());
                    }
                    if (form.getSearchDateTo() != null) {
                        //                            condition += " and p.receiveDate <= ?";
                        //                            lstParam.add(form.getSearchDateTo());
                        condition += " and p.receiveDate <= to_date( ? ,'dd/MM/yyyy hh24:mi:ss') ";
                        String param = "" + com.viettel.common.util.DateTimeUtils
                                .convertDateToString(form.getSearchDateTo(), "dd/MM/yyyy") + " 23:59:59";
                        lstParam.add(param);
                    }
                    break;
                default:
                    ;
                }
            }
            // m h s
            if (form.getFileCode() != null && !"".equals(form.getFileCode().trim())) {
                condition += " AND lower(f.fileCode) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(form.getFileCode().toLowerCase().trim()));
            }
            if (form.getFileType() != null && form.getFileType().longValue() != -1) {
                condition += " AND f.fileType = ?";
                lstParam.add(form.getFileType());
            }
            if (form.getBusinessName() != null && form.getBusinessName().length() > 0) {
                condition += " AND lower(f.businessName) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(form.getBusinessName().toLowerCase().trim()));
            }
            // Tn sn phm
            if (form.getProductName() != null && form.getProductName().length() > 0) {
                condition += " AND lower(f.productName) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(form.getProductName().toLowerCase().trim()));
            }
            // Nhm sn phm
            if (form.getProductType() != null && form.getProductType() != -1l) {
                condition += " AND d.productType = ?";
                lstParam.add(form.getProductType());
            }
        }
        Query countQuery = getSession().createQuery("select count(f.fileId) " + hql + " where 1=1 " + condition);
        String finalSql = "select distinct f " + hql + " where 1=1 " + condition + " order by ";
        if (sortCustom.isEmpty()) {
            finalSql += "p.modifyDate DESC";
        } else {
            finalSql += sortCustom;
        }
        Query query = getSession().createQuery(finalSql);

        for (int i = 0; i < lstParam.size(); i++) {
            query.setParameter(i, lstParam.get(i));
            countQuery.setParameter(i, lstParam.get(i));
        }

        int total = Integer.parseInt(countQuery.uniqueResult().toString());
        query.setFirstResult(start);
        if (count > 0) {
            query.setMaxResults(count);
        } else {
            query.setMaxResults(total);
        }
        List<FilesNoClob> lstResult = query.list();
        return new GridResult(total, lstResult);
    }

    public GridResult findAllFileForAssignEvaluationAfterAnnounced(FilesForm form, Long deptId, Long userId,
            int start, int count, String sortField) {
        try {
            String hql = " from FilesNoClob f, Process p " + " where f.isActive=1" + " and f.fileType = ?"
                    + " and f.fileId = p.objectId" + " and p.objectType = ?" + " and p.isActive = 1"
                    + " and (f.isTemp is null or f.isTemp = 0 ) ";
            /*
             *Hiepvv 13/01/16
             *Phan cong cong viec cho ho so sua doi sau cong bo
             */
            ProcedureDAOHE pdaohe = new ProcedureDAOHE();
            Procedure pbo = pdaohe.getProcedureByDescription(Constants.FILE_DESCRIPTION.ANNOUNCEMENT_FILE05);
            List lstParam = new ArrayList();
            lstParam.add(pbo.getProcedureId());
            lstParam.add(Constants.OBJECT_TYPE.FILES);
            if (form != null) {
                if (form.getStatus() == null) {
                    hql += " and (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.RECEIVED);
                } else {
                    hql += " and (f.status = ?)";
                    lstParam.add(form.getStatus());
                }
                if (form.getBusinessName() != null && !"".equals(form.getBusinessName().trim())) {
                    hql += " AND lower(f.businessName) like ? ESCAPE '/' ";
                    lstParam.add(StringUtils.toLikeString(form.getBusinessName().toLowerCase().trim()));
                }

                if (form.getProductName() != null && form.getProductName().length() > 0) {
                    hql += " AND lower(f.productName) like ? ESCAPE '/'";
                    lstParam.add(StringUtils.toLikeString(form.getProductName().toLowerCase().trim()));
                }
                if (form.getFileCode() != null && !"".equals(form.getFileCode().trim())) {
                    hql += "AND lower(f.fileCode) like ? ESCAPE '/' ";
                    lstParam.add(StringUtils.toLikeString(form.getFileCode().toLowerCase().trim()));
                }
                if (form.getFileType() != null && form.getFileType().longValue() != -1) {
                    hql += "AND f.fileType = ? ";
                    lstParam.add(form.getFileType());
                }
                if (form.getSendDateFrom() != null) {
                    hql += "AND f.sendDate >= ? ";
                    lstParam.add(form.getSendDateFrom());
                }
                if (form.getSendDateTo() != null) {
                    hql += "AND f.sendDate <= ? ";
                    lstParam.add(form.getSendDateTo());
                }
                if (deptId != null) {
                    hql += "AND (f.agencyId = ? or p.receiveUserId = ? or p.receiveGroupId=? ) and (p.processStatus=? or p.processStatus=? or p.processStatus =? ) ";

                    lstParam.add(deptId);
                    lstParam.add(userId);
                    lstParam.add(deptId);
                    lstParam.add(Constants.FILE_STATUS.NEW);
                    lstParam.add(Constants.FILE_STATUS.RECEIVED);
                    lstParam.add(Constants.FILE_STATUS.PROPOSED);
                } else {
                    hql += "AND (p.receiveUserId = ? and (p.processStatus=?) and p.status=? ) ";
                    lstParam.add(userId);
                    lstParam.add(Constants.FILE_STATUS.RECEIVED);
                    lstParam.add(0l);
                }
            }
            // loc file_id nhom san pham khac
            if (form.getProductTypeNew() != null && form.getProductTypeNew().longValue() != -1) {
                if (form.getProductTypeNew() == 1) {
                    hql += " and (c.code <> ? and c.code <> ?)";
                    lstParam.add("TPCN");
                    lstParam.add("DBT");
                } else {
                    hql += " and (c.code = ? or c.code = ?)";
                    lstParam.add("TPCN");
                    lstParam.add("DBT");
                }
            }

            // hieptq update 251114
            if (form.getSearchTypeNew() != null) {
                if (form.getSearchTypeNew() == 1) {
                    hql += " and (c.code <> ? and c.code <> ?)";
                    lstParam.add("TPCN");
                    lstParam.add("DBT");
                } else {
                    hql += " and (c.code = ? or c.code = ?)";
                    lstParam.add("TPCN");
                    lstParam.add("DBT");
                }
            }

            Query countQuery = getSession().createQuery("select count(distinct f.fileId) " + hql);
            Query query = getSession().createQuery("select distinct f " + hql + "order by f.modifyDate DESC)");
            for (int i = 0; i < lstParam.size(); i++) {
                query.setParameter(i, lstParam.get(i));
                countQuery.setParameter(i, lstParam.get(i));
            }

            query.setFirstResult(start);
            query.setMaxResults(count);
            int total = Integer.parseInt(countQuery.uniqueResult().toString());
            List<FilesNoClob> lstResult = query.list();
            GridResult gr = new GridResult(total, lstResult);
            return gr;
        } catch (Exception ex) {
            LogUtil.addLog(ex);//binhnt sonar a160901
            return new GridResult(0, null);
        }
    }

    public GridResult searchLookupFilesAfterAnnounced(FilesForm form, Long deptId, Long userId, String userType,
            int start, int count, String sortField, String sortCustom) {
        try {
            return createQueryLookupFilesAfterAnnounced(form, deptId, userId, userType, start, count, sortField,
                    sortCustom);
        } catch (Exception ex) {
            LogUtil.addLog(ex);//binhnt sonar a160901
            return new GridResult(0, null);
        }
    }

    public GridResult createQueryLookupFilesAfterAnnounced(FilesForm form, Long deptId, Long userId,
            String userType, int start, int count, String sortField, String sortCustom) {
        List lstParam = new ArrayList();
        String hql = " from FilesNoClob f, Process p, Business b ";
        String condition = "";
        condition += " and f.fileId = p.objectId";
        condition += " and f.deptId = b.businessId";
        condition += " and f.isActive = 1" + " and f.fileId = p.objectId"
                + "  and (f.isTemp = null or f.isTemp = 0 )";
        if (userType.equals(Constants.ROLES.LEAD_OFFICE_ROLE) || userType.equals(Constants.ROLES.STAFF_ROLE)
                || userType.equals(Constants.ROLES.LEAD_UNIT)) {
        }
        if (form != null) {
            if (userType.equals(Constants.ROLES.STAFF_ROLE) && form.getSearchType() != null) {//vcv
                switch (Integer.parseInt(form.getSearchType().toString())) {
                case 42:
                    condition += " and (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.EVALUATED);
                    condition += " and f.staffProcess =?";
                    lstParam.add(userId);
                    break;
                case 47:
                    condition += " and (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_ADD);
                    condition += " and f.staffProcess =?";
                    lstParam.add(userId);
                    break;
                case -26://H s  xem xt cv sbs ch? ph duyt cng vn
                    lstParam.clear();
                    hql = "from FilesNoClob f, Process p";
                    condition = " AND f.isActive = 1" + " AND (f.isTemp = null or f.isTemp = 0 )"
                            + " AND f.fileId = p.objectId" + " AND f.status = ?" + " AND p.receiveGroupId = ?"
                            + " AND p.receiveUserId = ?";
                    lstParam.add(Constants.FILE_STATUS.REVIEW_TO_ADD);
                    lstParam.add(deptId);
                    lstParam.add(userId);
                    break;
                case -20://Ho so da gui thong bao sdbs cho doanh nghiep
                    lstParam.clear();
                    hql = "from FilesNoClob f";
                    condition = " AND f.isActive=1";
                    condition += " AND (f.status = ?)";
                    condition += " AND f.staffProcess=?";
                    condition += " AND (f.isTemp = null or f.isTemp = 0 ) ";
                    lstParam.add(Constants.FILE_STATUS.EVALUATED_TO_ADD);
                    lstParam.add(userId);
                    break;
                case 33://H s ch? thm nh S?BS CV
                    lstParam.clear();
                    hql = " from FilesNoClob f, Process p";
                    condition = " AND f.fileId = p.objectId";
                    condition += " AND f.isActive = 1" + " and f.fileId = p.objectId"
                            + " and (f.isTemp = null or f.isTemp = 0 )";
                    condition += " AND p.receiveGroupId = ?";
                    lstParam.add(deptId);
                    if (userId != null) {
                        condition += " and f.staffProcess=?";
                        lstParam.add(userId);
                    }
                    condition += " AND f.status = ?";
                    lstParam.add(Constants.FILE_STATUS.RECEIVED_TO_ADD);
                    break;
                case 19://H s lnh o cc yu cu b sung CV
                    lstParam.clear();
                    hql = " from FilesNoClob f";
                    condition = " AND f.fileId = p.objectId";
                    condition += " AND f.isActive = 1" + " and (f.isTemp = null or f.isTemp = 0 )";
                    condition += " AND p.receiveGroupId = ?";
                    lstParam.add(deptId);
                    if (userId != null) {
                        condition += " and p.receiveUserId=?";
                        lstParam.add(userId);
                    }
                    condition += " AND f.status = ?";
                    lstParam.add(Constants.FILE_STATUS.REVIEWED_TO_ADD);
                    break;
                case 5://H s cn thng bo i chiu
                    lstParam.clear();
                    hql = " from FilesNoClob f, Process p";
                    condition = " and f.fileId = p.objectId";
                    condition += " AND f.isActive = 1" + " and f.fileId = p.objectId"
                            + " and (f.isTemp = null or f.isTemp = 0 )";
                    condition += " AND p.receiveGroupId = ?";
                    lstParam.add(deptId);
                    if (userId != null) {
                        condition += " and p.receiveUserId=?";
                        lstParam.add(userId);
                    }
                    condition += " AND f.status = ?";
                    lstParam.add(Constants.FILE_STATUS.REVIEWED);
                    break;
                case 50:
                    lstParam.clear();
                    hql = " from FilesNoClob f, Process p";
                    condition = " and f.fileId = p.objectId";
                    condition += " AND f.isActive = 1 and f.fileId = p.objectId"
                            + " and (f.isTemp = null or f.isTemp = 0 )";
                    condition += " AND (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                    condition += " AND p.receiveGroupId = ?";
                    lstParam.add(deptId);
                    if (userId != null) {
                        condition += " and p.receiveUserId=?";
                        lstParam.add(userId);
                    }
                    break;
                case 417:
                    lstParam.clear();
                    hql = " from FilesNoClob f";
                    condition = " AND f.isActive = 1" + " and (f.isTemp = null or f.isTemp = 0)";
                    condition += " AND (f.status =?)";
                    lstParam.add(Constants.FILE_STATUS.APPROVED);
                    if (userId != null) {
                        condition += " and f.staffProcess=?";
                        lstParam.add(userId);
                    }
                    condition += " AND f.isSignPdf <> 2";
                    break;
                case 422:
                    lstParam.clear();
                    hql = " from FilesNoClob f";
                    condition = " AND f.isActive = 1" + " and (f.isTemp = null or f.isTemp = 0)";
                    condition += " AND (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.GIVE_BACK);
                    if (userId != null) {
                        condition += " and f.staffProcess=?";
                        lstParam.add(userId);
                    }
                    condition += " AND f.isSignPdf = 2";
                    break;
                case 423:
                    lstParam.clear();
                    hql = " from FilesNoClob f";
                    condition = " AND f.isActive = 1" + " and (f.isTemp = null or f.isTemp = 0)";
                    condition += " AND (f.status <> ?)";
                    lstParam.add(Constants.FILE_STATUS.GIVE_BACK);
                    if (userId != null) {
                        condition += " and f.staffProcess=?";
                        lstParam.add(userId);
                    }
                    condition += " AND f.isSignPdf = 2";
                    break;
                case 447:
                    lstParam.clear();
                    hql = " from FilesNoClob f";
                    condition = " AND f.isActive = 1 and (f.isTemp = null or f.isTemp = 0)";
                    condition += " AND f.staffProcess=?";
                    lstParam.add(userId);
                    condition += " AND (f.status =?)";
                    lstParam.add(Constants.FILE_STATUS.EVALUATED);
                    break;
                case 448:
                    lstParam.clear();
                    hql = " from FilesNoClob f";
                    condition = " AND f.isActive = 1 and (f.isTemp = null or f.isTemp = 0)";
                    condition += " AND f.staffProcess=?";
                    lstParam.add(userId);
                    condition += " AND (f.status =?)";
                    lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_ADD);
                    break;
                case 20://H s ch? chuyn vin trong t thm nh
                    lstParam.clear();
                    hql = "from FilesNoClob f, Process p";
                    condition = " AND f.isActive = 1";
                    condition += " AND (f.isTemp = null or f.isTemp = 0 )";
                    condition += " AND f.fileId = p.objectId";
                    if (userId != null) {
                        condition += " and p.receiveUserId=?";
                        lstParam.add(userId);
                    }
                    condition += " and p.processType=0";
                    condition += " and f.status = ?";
                    lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                    break;
                case 21://H s gi phi hp cha cho  kin 21
                    lstParam.clear();
                    hql = " from FilesNoClob f, Process p";
                    condition = " AND f.isActive = 1 ";
                    condition += " AND (f.isTemp = null or f.isTemp = 0 ) ";
                    condition += " AND f.fileId = p.objectId ";
                    condition += " AND p.receiveGroupId = ? ";
                    lstParam.add(deptId);
                    if (userId != null) {
                        condition += " AND p.sendUserId = ? ";
                        lstParam.add(userId);
                    }
                    condition += " AND (f.status = ?) ";
                    condition += " AND p.processStatus = ?";
                    lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                    lstParam.add(Constants.FILE_STATUS.ASSIGNED);
                    condition += " AND (p.processId in (select p.processId from Process p where p.processId not in (select distinct pc.processId from ProcessComment pc))) ";
                    condition += " AND (p.processType=0 or p.processType=4) ";
                    break;
                case 22://H s chuyn vin  gi thng bo sa i b sung
                    lstParam.clear();
                    hql = " from FilesNoClob f, Process p";
                    condition = " AND f.fileId = p.objectId";
                    condition += " AND f.isActive = 1 and (f.isTemp = null or f.isTemp = 0)";
                    condition += " AND p.receiveGroupId =?";
                    lstParam.add(deptId);
                    if (userId != null) {
                        condition += " and p.receiveUserId=?";
                        lstParam.add(userId);
                    }
                    condition += " AND (f.status =?)";
                    lstParam.add(Constants.FILE_STATUS.EVALUATED_TO_ADD);
                    break;
                case 26:
                    lstParam.clear();
                    hql = "from FilesNoClob f, Process p";
                    condition = " AND f.isActive=1";
                    condition += " AND f.fileId = p.objectId";
                    condition += " AND f.status = ?";
                    condition += " AND p.receiveUserId=?";
                    condition += " AND p.processType=1";
                    condition += " AND (f.isTemp = null or f.isTemp = 0 ) ";
                    lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_EVALUATE);
                    lstParam.add(userId);
                    break;
                case 27:
                    lstParam.clear();
                    hql = " from FilesNoClob f, Process p";
                    condition = " AND f.isActive = 1 ";
                    condition += " AND (f.isTemp = null or f.isTemp = 0 ) ";
                    condition += " AND f.fileId = p.objectId ";
                    condition += " AND p.receiveGroupId = ? ";
                    lstParam.add(deptId);
                    if (userId != null) {
                        condition += " AND p.sendUserId = ? ";
                        lstParam.add(userId);
                    }
                    condition += " AND (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.EVALUATED_TO_ADD);
                    condition += " AND sysdate - f.evaluateAddDate >= 54";
                    break;
                }
            }
            if (userType.equals(Constants.ROLES.CLERICAL_ROLE) && form.getSearchType() != null) {//vvt
                switch (Integer.parseInt(form.getSearchType().toString())) {
                case -11:
                    /*tim kiem bao cao van thu xu ly ho so trong khoang thoi gian.
                     binhnt53 add 150211
                     */
                    lstParam.clear();
                    hql = " from FilesNoClob f, Process p";
                    condition = " AND f.fileId = p.objectId";
                    condition += " AND f.isActive = 1 and (f.isTemp = null or f.isTemp = 0)";
                    if (form.getStatus() != null) {
                        condition += " AND p.status = ?";
                        lstParam.add(form.getStatus());
                    }
                    if (form.getSearchDateFrom() != null) {
                        condition += " and p.sendDate >= ?";
                        lstParam.add(form.getSearchDateFrom());
                    }
                    if (form.getSearchDateTo() != null) {
                        condition += " and p.sendDate <= ?";
                        lstParam.add(form.getSearchDateTo());
                    }
                    break;
                case 1623://h s cn i chiu
                    lstParam.clear();
                    hql = " from FilesNoClob f, Process p";
                    condition = " AND f.fileId = p.objectId";
                    condition += " AND f.isActive = 1 and (f.isTemp = null or f.isTemp = 0)";
                    condition += " AND (f.status =? )";
                    //lstParam.add(Constants.FILE_STATUS.COMPARED_FAIL);
                    lstParam.add(Constants.FILE_STATUS.ALERT_COMPARISON);
                    break;
                case 22:
                    hql = " from FilesNoClob f, Process p";
                    lstParam.clear();
                    condition = " and f.isActive = 1" + " and f.fileId = p.objectId"
                            + " and (f.isTemp = null or f.isTemp = 0)";
                    condition += " and (f.status = ? or f.status = ? or f.status = ? or f.status = ? or f.status = ? or f.status = ?)";
                    //                        lstParam.add(Constants.FILE_STATUS.APPROVED);
                    lstParam.add(Constants.FILE_STATUS.REVIEW_COMPARISON_FAIL);
                    lstParam.add(Constants.FILE_STATUS.COMPARED_FAIL);
                    lstParam.add(Constants.FILE_STATUS.REVIEW_COMPARISON);
                    lstParam.add(Constants.FILE_STATUS.COMPARED);
                    lstParam.add(Constants.FILE_STATUS.ALERT_COMPARISON);
                    lstParam.add(Constants.FILE_STATUS.GIVE_BACK);
                    condition += " and p.receiveGroupId = ?";
                    lstParam.add(deptId);
                    break;
                case 15:
                    hql = " from FilesNoClob f, Process p";

                    lstParam.clear();
                    condition = " and f.isActive = 1" + " and f.fileId = p.objectId"
                            + " and (f.isTemp = null or f.isTemp = 0)" + " and (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.COMPARED);
                    condition += " and p.receiveGroupId = ?";
                    condition += " and f.isFee = 1 and f.isSignPdf = 2";
                    lstParam.add(deptId);
                    break;
                case 6:
                    hql = " from FilesNoClob f, Process p";

                    lstParam.clear();
                    condition = " and f.isActive = 1" + " and f.fileId = p.objectId"
                            + " and (f.isTemp = null or f.isTemp = 0)" + " and (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.APPROVED);
                    condition += " and p.receiveGroupId = ?";
                    condition += " and f.isFee = 1 and f.isSignPdf = 2";
                    condition += " and f.isDownload <> 1";//u150112 binhnt53
                    lstParam.add(deptId);
                    break;
                case 20:
                    hql = " from FilesNoClob f, Process p";

                    lstParam.clear();
                    condition = " and f.isActive = 1" + " and f.fileId = p.objectId"
                            + " and (f.isTemp = null or f.isTemp = 0)" + " and (f.status = ?)";
                    lstParam.add(form.getStatus());
                    condition += " and p.sendGroupId = ?";
                    lstParam.add(deptId);
                    break;
                case -27:
                    hql = " from FilesNoClob f, Process p";

                    lstParam.clear();
                    condition = " and f.isActive = 1" + " and f.fileId = p.objectId"
                            + " and (f.isTemp = null or f.isTemp = 0)" + " and (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.APPROVE_TO_ADD);
                    condition += " and p.receiveGroupId = ?";
                    lstParam.add(deptId);
                    break;
                case -6://h s cn k xc thc vn th: h s  i chiu,  thanh ton
                    hql = " from FilesNoClob f, Process p";
                    condition = " and f.fileId = p.objectId" + " and (f.isTemp=null or f.isTemp=0)"
                            + " and f.isActive= 1" + " and f.isSignPdf = 1" + " and f.isFee = 1"
                            + " and f.status = ?" + " and p.receiveGroupId = ?";
                    lstParam.clear();
                    lstParam.add(Constants.FILE_STATUS.APPROVED);
                    lstParam.add(deptId);
                    break;
                case -4:
                    hql = " from FilesNoClob f";
                    condition = " and (f.isTemp=null or f.isTemp=0)" + " and f.isActive=1 "
                            + " and f.isFee <> 1 and f.isSignPdf <> 2" + " and (f.status=?)"
                            + " and f.agencyId = ?";
                    lstParam.clear();
                    lstParam.add(Constants.FILE_STATUS.APPROVED);
                    lstParam.add(deptId);
                    break;
                case 0:
                    hql = " from FilesNoClob f, Process p";
                    lstParam.clear();
                    condition = " and f.isActive = 1" + " and f.fileId = p.objectId"
                            + " and (f.isTemp = null or f.isTemp = 0)" + " and (f.status = ?)";
                    lstParam.add(form.getStatus());
                    condition += " and p.receiveGroupId = ?";
                    lstParam.add(deptId);
                    break;
                case 110://danh sach ho so can luu tru
                    hql = " from FilesNoClob f, Process p";
                    lstParam.clear();
                    condition = " and f.isActive = 1" + " and f.fileId = p.objectId"
                            + " and (f.isTemp = null or f.isTemp = 0)" + " and f.status <> ?" + " and f.isFee <> 2";
                    condition += " and p.receiveGroupId = ?";
                    lstParam.add(Constants.FILE_STATUS.NEW_CREATE);
                    lstParam.add(deptId);
                    break;
                default:
                    ;
                }
            }
            if (userType.equals(Constants.ROLES.LEAD_UNIT) && form.getSearchType() != null) {//vldp
                switch (Integer.parseInt(form.getSearchType().toString())) {
                case 39:
                    condition += " and f.isActive = 1" + " and f.fileId = p.objectId" + " AND f.status = ?"
                            + " and (f.isTemp = null or f.isTemp = 0 )";
                    lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_REVIEW);
                    if (userId != null) {
                        condition += " and f.leaderReviewId=?";
                        lstParam.add(userId);
                    }
                    break;
                case 30:
                    condition += " and f.isActive = 1" + " and f.fileId = p.objectId" + " AND f.status = ?"
                            + " and (f.isTemp = null or f.isTemp = 0 )";
                    lstParam.add(Constants.FILE_STATUS.EVALUATED_TO_ADD);
                    if (userId != null) {
                        condition += " and p.receiveUserId=?";
                        lstParam.add(userId);
                    }
                    if (deptId != null) {
                        condition += " AND p.receiveGroupId = ? ";
                        lstParam.add(deptId);
                    }
                    break;
                case 2:
                    condition += " and (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.EVALUATED);
                    condition += " and p.receiveGroupId = ?" + " and p.receiveUserId = ?";
                    lstParam.add(deptId);
                    lstParam.add(userId);
                    condition += " and ((f.leaderEvaluateId = ?"
                            + " and f.leaderReviewId = null) or f.leaderReviewId =?)";
                    lstParam.add(userId);
                    lstParam.add(userId);
                    break;
                case 7:
                    condition += " and (f.status = ?)";////150204 binhnt53 update
                    lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_ADD);
                    condition += " and p.receiveGroupId = ?" + " and p.receiveUserId = ?";
                    lstParam.add(deptId);
                    lstParam.add(userId);
                    condition += " and (f.leaderReviewId =?)";
                    lstParam.add(userId);
                    break;
                case 8:// ho so cho xem xet du thao sdbs
                    condition += " and (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.EVALUATE_TO_ADD);
                    condition += " and p.receiveGroupId = ?";
                    lstParam.add(deptId);
                    condition += " and p.receiveUserId = ?";
                    lstParam.add(userId);
                    condition += " and f.leaderReviewId = ?";
                    lstParam.add(userId);
                    break;
                case 5://ho so cho lanh dao cuc phe duyet, lanh dao phong da duyet
                    condition += " and (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.REVIEWED);
                    condition += " and p.receiveGroupId = ?" + " and p.receiveUserId = ?";
                    lstParam.add(deptId);
                    lstParam.add(userId);
                    break;
                case 22://ho so cho lanh dao cuc phe duyet, lanh dao phong da duyet
                    condition += " and (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.GIVE_BACK);
                    break;
                }
            }
            if (userType.equals(Constants.ROLES.LEAD_OFFICE_ROLE) && form.getSearchType() != null) {//vldc
                switch (Integer.parseInt(form.getSearchType().toString())) {
                case 3:
                    condition += " and (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.REVIEWED);
                    condition += " and p.receiveGroupId = ?" + " and p.receiveUserId = ?";
                    lstParam.add(deptId);
                    lstParam.add(userId);
                    break;
                case 10:
                    condition += " and (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.APPROVED);
                    condition += " and p.receiveGroupId = ?" + " and p.receiveUserId = ?";
                    lstParam.add(deptId);
                    lstParam.add(userId);
                    break;
                case 26:
                    condition += " and (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.REVIEW_TO_ADD);
                    condition += " and p.receiveGroupId = ?" + " and p.receiveUserId = ?";
                    lstParam.add(deptId);
                    lstParam.add(userId);
                    break;
                case 36://a260515 binhnt
                    condition += " and f.status in (?,?,?,?,?,?)" + " and f.isActive = 1 "
                            + " and (f.isTemp is null or f.isTemp = 0)";
                    lstParam.add(Constants.FILE_STATUS.GIVE_BACK);
                    lstParam.add(Constants.FILE_STATUS.ALERT_COMPARISON);
                    lstParam.add(Constants.FILE_STATUS.COMPARED);
                    lstParam.add(Constants.FILE_STATUS.REVIEW_COMPARISON);
                    lstParam.add(Constants.FILE_STATUS.COMPARED_FAIL);
                    lstParam.add(Constants.FILE_STATUS.REVIEW_COMPARISON_FAIL);
                    break;
                }
            }
            if (userType.equals(Constants.ROLES.ADMIN) && form.getSearchType() != null) {//vQTHT
                switch (Integer.parseInt(form.getSearchType().toString())) {
                case 110://h s cn i chiu
                    lstParam.clear();
                    hql = " from FilesNoClob f, Process p";
                    condition = " AND f.fileId = p.objectId";
                    condition += " AND f.isActive = 1" + " and (f.isTemp = null or f.isTemp = 0)";
                    condition += " AND (f.staffProcess <> null)";
                    break;
                }
            }
            if (form.getSearchType() == null) {
                if (form.getStatus() != null && form.getSearchType() == null
                        && (form.getStatus() == 40l || form.getStatus() == 41l || form.getStatus() == 42l
                                || form.getStatus() == 43l || form.getStatus() == 44l || form.getStatus() == 45l
                                || form.getStatus() == 46l || form.getStatus() == 47l || form.getStatus() == 48l
                                || form.getStatus() == 49l || form.getStatus() == 30l
                                || form.getStatus().equals(Constants.FILE_STATUS.REVIEW_COMPARISON_FAIL)
                                || form.getStatus() == 50l)) {
                    lstParam.clear();
                    hql = " from FilesNoClob f, Process p, Business b";
                    condition = " and f.fileId = p.objectId" + " and f.deptId = b.businessId";
                    // Nh sn xut
                    if (form.getManufactureName() != null && form.getManufactureName().length() > 0) {
                        hql += ", Announcement ann";
                        condition += " and f.announcementId = ann.announcementId"
                                + " and lower(ann.manufactureName) like ? ESCAPE '/'";
                        lstParam.add(StringUtils.toLikeString(form.getManufactureName().toLowerCase().trim()));
                    }
                    // S chng nhn cng b
                    if (form.getAnnouncementNo() != null && form.getAnnouncementNo().length() > 0) {
                        hql += ", AnnouncementReceiptPaper ann";
                        condition += " and f.announcementReceiptPaperId = ann.announcementReceiptPaperId and lower(ann.receiptNo) like ? ESCAPE '/'";
                        lstParam.add(StringUtils.toLikeString(form.getAnnouncementNo().toLowerCase().trim()));
                    }
                    //                // thong ke ho so trong ngay
                    if (form.getStatus() != null && form.getStatus() == 40l) {
                        condition += " and f.isActive = 1" + " and (f.status = ? or f.status = ?)"
                                + " and  to_date(f.sendDate,'yyyy/mm/dd') = to_date(sysdate,'yyyy/mm/dd')  ";
                        lstParam.add(3l);
                        lstParam.add(5l);
                        if (userId != null) {
                            condition += " and p.receiveUserId=?";
                            lstParam.add(userId);
                        }
                    }
                    if (form.getStatus() != null && form.getStatus() == 41l) {
                        condition += " AND f.isActive = 1" + " AND (f.status = ? or f.status = ?)"
                                + " AND  to_date(f.sendDate,'yyyy/mm/dd') = to_date(sysdate,'yyyy/mm/dd')"
                                + " AND (f.isTemp = null or f.isTemp = 0 )";
                        lstParam.add(4l);
                        lstParam.add(5l);
                    }
                    if (form.getStatus() != null && form.getStatus() == 42l) {
                        condition += " and f.isActive = 1" + " and (f.status = ? or f.status = ?)"
                                + " and  to_date(f.sendDate,'yyyy/mm/dd') = to_date(sysdate,'yyyy/mm/dd')"
                                + " and (f.isTemp = null or f.isTemp = 0 )";
                        lstParam.add(6l);
                        lstParam.add(5l);
                    }
                    // ho so bi tra tham dinh lai
                    if (form.getStatus() != null && form.getStatus() == 43l) {
                        condition += " and f.isActive=1" + " and f.status = ?" + " and p.processType=1"
                                + " and (f.isTemp = null or f.isTemp = 0 )";
                        lstParam.add(8l);
                        if (userId != null) {
                            condition += " and p.receiveUserId=?";
                            lstParam.add(userId);
                        }
                    }
                    // ho so cho phoi hop tham dinh chua cho y kien
                    if (form.getStatus() != null && form.getStatus() == 44l) {
                        condition += " and f.isActive=1" + " and (f.status = ?)"
                                + " and (p.processType=0 or p.processType=4)"
                                + " and (p.processId in (select p.processId from Process p where p.processId not in (select distinct pc.processId from ProcessComment pc)))"
                                + " and p.processStatus = ?";
                        lstParam.add(3l);
                        lstParam.add(3l);
                    }

                    //ho so cho xem xet
                    if (form.getStatus() != null && form.getStatus() == 48L) {
                        condition += " and f.isActive=1" + " and (f.status = ? or f.status = ?)"
                                + " and (p.processType=1 or p.processType=0)"
                                + " and (f.isTemp = null or f.isTemp = 0 )";
                        lstParam.add(Constants.FILE_STATUS.EVALUATED);
                        lstParam.add(Constants.FILE_STATUS.FEDBACK_TO_ADD);
                        if (userId != null) {
                            condition += " and p.receiveUserId=?";
                            lstParam.add(userId);
                        }
                    }
                    // ho so lanh dao phong da xem xet
                    if (form.getStatus() != null && form.getStatus() == 46l) {
                        condition += " and f.isActive=1" + " and f.status = ?" + " and p.processType=1"
                                + " and (f.isTemp = null or f.isTemp = 0 )";
                        lstParam.add(5l);
                        if (userId != null) {
                            condition += " and p.receiveUserId=?";
                            lstParam.add(userId);
                        }
                    }
                    //ho so da phe duyet
                    if (form.getStatus() != null && form.getStatus() == 47l) {
                        condition += " and f.isActive=1" + " and f.status = ?" + " and p.processType=1"
                                + " and (f.isTemp = null or f.isTemp = 0 )";
                        lstParam.add(Constants.FILE_STATUS.APPROVED);
                        if (userId != null) {
                            condition += " and p.receiveUserId=?";
                            lstParam.add(userId);
                        }
                    }
                    // ho so tham dnh da gui  kin phn hi
                    if (form.getStatus() != null && form.getStatus() == 49l) {
                        lstParam.clear();
                        hql = " from FilesNoClob f, Process p, ProcessComment pc";
                        condition = " AND f.isActive=1" + " AND f.fileId = p.objectId" + " AND p.objectType = 30"
                                + " AND (f.status = ?)" + " AND (p.processType=0 or p.processType=1)"
                                + " AND pc.processId = p.processId" + " AND (f.isTemp = null or f.isTemp = 0 )";
                        lstParam.add(3l);
                        if (userId != null) {
                            condition += " AND p.receiveGroupId = (select distinct p.receiveGroupId from Process p where p.receiveUserId = ?)";
                            lstParam.add(userId);
                        }
                    }
                    if (form.getStatus() != null && form.getStatus() == 50l) {
                        condition += " and f.isActive=1" + " and f.status = ?" + " and p.processType=1"
                                + " and (f.isTemp = null or f.isTemp = 0 )";
                        lstParam.add(3l);
                        if (userId != null) {
                            condition += " and p.receiveUserId=?";
                            lstParam.add(userId);
                        }
                    }
                    // da thong bao SDBS
                    if (form.getStatus() != null && form.getStatus() == 30l) {
                        condition += " and f.isActive=1" + " and f.status = ?" + " and p.processType=1"
                                + " and (f.isTemp = null or f.isTemp = 0 )";
                        lstParam.add(20l);
                        if (userId != null) {
                            condition += " and p.receiveUserId=?";
                            lstParam.add(userId);
                        }
                    }

                    // da tham dinh - hieptq
                    if (form.getStatus() != null && form.getStatus() == 45l) {
                        condition += " and f.isActive=1" + " and f.status = ?" + " and p.processType=1"
                                + " and (f.isTemp = null or f.isTemp = 0 )";
                        lstParam.add(4l);
                        if (userId != null) {
                            condition += " and p.receiveUserId=?";
                            lstParam.add(userId);
                        }
                    }

                    //
                    if (form.getStatus() != null
                            && form.getStatus() == Constants.FILE_STATUS.REVIEW_COMPARISON_FAIL) {
                        condition += " and f.isActive = 1" + " and f.status = ?"
                                + " and (f.isTemp = null or f.isTemp = 0)";
                        lstParam.add(25l);
                    }
                } else if (form.getStatus() != null && form.getStatus() >= 0l) {
                    condition += " AND f.status = ?";
                    lstParam.add(form.getStatus());
                } else {
                }

                if (userType.equals(Constants.ROLES.LEAD_OFFICE_ROLE)) {
                    if (deptId != null) {
                        condition += " and p.receiveGroupId = ? ";
                        lstParam.add(deptId);
                    }
                }
                if (userType.equals(Constants.ROLES.STAFF_ROLE)) {
                    if (deptId != null) {
                        if (form.getStatus() != null && form.getStatus() != 40l) {
                            condition += " and p.receiveGroupId = ? ";
                            lstParam.add(deptId);
                        }
                    }
                    if (userId != null) {
                        condition += " and p.receiveUserId = ? ";
                        lstParam.add(userId);
                    }
                }
                if (userType.equals(Constants.ROLES.LEAD_UNIT)) {
                    if (deptId != null) {
                        condition += " and p.receiveGroupId = ?"
                                + " and ( p.receiveUserId = null or p.receiveUserId = ?) ";
                        lstParam.add(deptId);
                        lstParam.add(userId);
                    }
                }
                if (userType.equals(Constants.ROLES.CLERICAL_ROLE)) {
                    if (deptId != null) {
                        //                    hql += "AND f.agencyId = ?)";
                        //                    lstParam.add(deptId);
                        condition += " and p.receiveGroupId = ? ";
                        lstParam.add(deptId);
                    }
                }
            }
            // Nh sn xut
            if (form.getManufactureName() != null && form.getManufactureName().length() > 0) {
                String str = ", Announcement ann";
                if (!str.contains(hql)) {
                    hql += ", Announcement ann";
                }
                condition += " and f.announcementId = ann.announcementId"
                        + " and lower(ann.manufactureName) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(form.getManufactureName().toLowerCase().trim()));
            }
            // S chng nhn cng b
            if (form.getAnnouncementNo() != null && form.getAnnouncementNo().length() > 0) {
                String str = ", AnnouncementReceiptPaper arp";
                if (!str.contains(hql)) {
                    hql += ", AnnouncementReceiptPaper arp";
                    condition += " and f.announcementReceiptPaperId = arp.announcementReceiptPaperId";
                }
                condition += " and lower(arp.receiptNo) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(form.getAnnouncementNo().toLowerCase().trim()));
            }
            // ngay cap
            if (form.getReceiptDate() != null) {
                String str = ", AnnouncementReceiptPaper arp";
                if (!str.contains(hql)) {
                    hql += ", AnnouncementReceiptPaper arp";
                    condition += " and f.announcementReceiptPaperId = arp.announcementReceiptPaperId";
                }
                condition += " and lower(arp.receiptDate) = ?";
                lstParam.add(form.getReceiptDate());
            }
            //thng nhn chu trch nhim
            if (form.getOrigin() != null && form.getOrigin().length() > 0) {
                String str = ", DetailProduct d";
                if (!hql.contains(str)) {
                    hql += ", DetailProduct d";
                    condition += " and f.detailProductId = d.detailProductId";
                }
                condition += " and lower(d.origin) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(form.getOrigin().toLowerCase().trim()));
            }
            // in thoi doanh nghip
            if (form.getBusinessTelephone() != null && form.getBusinessTelephone().length() > 0) {
                String str = ", Announcement ann";
                if (!hql.contains(str)) {
                    hql += ", Announcement ann";
                    condition += " and f.announcementId = ann.announcementId";
                }
                condition += " and lower(ann.businessTelephone) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(form.getBusinessTelephone().toLowerCase().trim()));
            }
            // fax doanh nghip
            if (form.getBusinessFax() != null && form.getBusinessFax().length() > 0) {
                String str = ", Announcement ann";
                if (!hql.contains(str)) {
                    hql += ", Announcement ann";
                    condition += " and f.announcementId = ann.announcementId";
                }
                condition += " and lower(ann.businessFax) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(form.getBusinessFax().toLowerCase().trim()));
            }
            // m h s
            if (form.getFileCode() != null && !"".equals(form.getFileCode().trim())) {
                condition += " AND lower(f.fileCode) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(form.getFileCode().toLowerCase().trim()));
            }
            if (form.getFileType() != null && form.getFileType().longValue() != -1) {
                condition += " AND f.fileType = ?";
                lstParam.add(form.getFileType());
            }
            if (form.getSignDate() != null) {
                condition += " AND f.signDate = ?";
                lstParam.add(form.getSignDate());
            }
            if (form.getSendDateFrom() != null) {
                condition += " AND f.sendDate >= ?";
                lstParam.add(minDayToCompare(form.getSendDateFrom()));
            }
            if (form.getSendDateTo() != null) {
                condition += " AND f.sendDate <= ?";
                lstParam.add(maxDayToCompare(form.getSendDateTo()));
            }
            if (form.getSignDateFrom() != null) {
                condition += " AND f.signDate >= ? ";
                lstParam.add(minDayToCompare(form.getSignDateFrom()));
            }
            if (form.getSignDateTo() != null) {
                condition += " AND f.signDate <= ? ";
                lstParam.add(maxDayToCompare(form.getSignDateTo()));
            }
            if (form.getSignDateFrom() != null) {
                condition += " AND f.status = ? ";
                lstParam.add(22l);
            }
            if (form.getRepDateFrom() != null) {
                condition += " AND f.repDate >= ?";
                lstParam.add(minDayToCompare(form.getRepDateFrom()));
            }
            if (form.getRepDateTo() != null) {
                condition += " AND f.repDate <= ?";
                lstParam.add(maxDayToCompare(form.getRepDateTo()));
            }
            if (form.getApproveDateFrom() != null) {
                condition += " AND f.approveDate >= ?";
                lstParam.add(minDayToCompare(form.getApproveDateFrom()));
            }
            if (form.getApproveDateTo() != null) {
                condition += " AND f.approveDate <= ?";
                lstParam.add(maxDayToCompare(form.getApproveDateTo()));
            }
            if (form.getLeaderStaffSignName() != null && form.getLeaderStaffSignName().length() > 0) {
                condition += " AND lower(f.leaderStaffSignName) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(form.getLeaderStaffSignName().toLowerCase().trim()));
            }
            if (form.getBusinessName() != null && form.getBusinessName().length() > 0) {
                condition += " AND lower(f.businessName) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(form.getBusinessName().toLowerCase().trim()));
            }
            if (form.getNationName() != null && form.getNationName().length() > 0) {
                condition += " AND lower(f.nationName) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(form.getNationName().toLowerCase().trim()));
            }
            // Ng?i thm nh
            if (form.getReceiveUser() != null && form.getReceiveUser().length() > 0) {
                condition += " AND lower(p.receiveUser) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(form.getReceiveUser().toLowerCase().trim()));
            }
            // S ng k kinh doanh
            if (form.getBusinessLicence() != null && form.getBusinessLicence().length() > 0) {
                condition += " AND lower(b.businessLicense) like ? ESCAPE '/' ";
                lstParam.add(StringUtils.toLikeString(form.getBusinessLicence().toLowerCase().trim()));
            }
            // ?a ch doanh nghip
            if (form.getBusinessAddress() != null && form.getBusinessAddress().length() > 0) {
                condition += " AND lower(f.businessAddress) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(form.getBusinessAddress().toLowerCase().trim()));
            }
            // Tn sn phm
            if (form.getProductName() != null && form.getProductName().length() > 0) {
                condition += " AND lower(f.productName) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(form.getProductName().toLowerCase().trim()));
            }
            // Nhm sn phm
            if (form.getProductType() != null && form.getProductType() != -1l) {
                condition += " AND d.productType = ?";
                lstParam.add(form.getProductType());
            }
            // Lnh o phng
            if (form.getLeaderStaff() != null && form.getLeaderStaff().length() > 0) {
                condition += " AND lower(p.receiveUser) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(form.getLeaderStaff().toLowerCase().trim()));
            }
            // Ng?i thm xt
            if (form.getStaff() != null && form.getStaff().trim().length() > 0) {
                condition += " AND lower(f.nameStaffProcess) like ? ESCAPE '/' ";
                lstParam.add(StringUtils.toLikeString(form.getStaff().toLowerCase().trim()));
            }
            if (form.getNameStaffProcess() != null && form.getNameStaffProcess().length() > 0) {
                condition += " AND lower(f.nameStaffProcess) like ? ESCAPE '/' ";
                lstParam.add(StringUtils.toLikeString(form.getNameStaffProcess().toLowerCase().trim()));
            }
            if (form.getLeaderApproveName() != null && form.getLeaderApproveName().length() > 0) {
                condition += " AND lower(f.leaderApproveName) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(form.getLeaderApproveName().toLowerCase().trim()));
            }
            if (form.getLeaderAssignName() != null && form.getLeaderAssignName().length() > 0) {
                condition += " AND lower(f.leaderAssignName) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(form.getLeaderAssignName().toLowerCase().trim()));
            }
            if (form.getLeaderReviewName() != null && form.getLeaderReviewName().length() > 0) {
                condition += " AND lower(f.leaderReviewName) like ? ESCAPE '/'";
                lstParam.add(StringUtils.toLikeString(form.getLeaderReviewName().toLowerCase().trim()));
            }
            // Tnh -  Thnh Ph
            if (form.getProductType() != null && form.getBusinessProvinceId() != -1l) {
                condition += " AND b.businessProvinceId = ? ";
                lstParam.add(form.getBusinessProvinceId());
            }
            // Ni lu tr
            if (form.getRepositoriesId() != null && form.getRepositoriesId() != -1l) {
                condition += " AND (f.repositoriesId = ? ) ";
                lstParam.add(form.getRepositoriesId());
            }
            // L?c theo ng?i to ( lu tr h s giy )
            if (form.getRepCreator() != null && (form.getSearchType() == 0 || form.getSearchType() == 2)) {
            }
            // Kho lu tr
            if (form.getRepName() != null && form.getRepName() != -1l) {
                condition += " AND (f.repositoriesId = ? )";
                lstParam.add(form.getRepName());
            }
            // Trng thi lu tr
            // ? lu tr
            if (form.getRepStatus() != null && form.getRepStatus() == 1) {
                condition += " AND (f.repositoriesId <> null )";
            }
            // Cha lu tr
            if (form.getRepStatus() != null && form.getRepStatus() == 2) {
                condition += " AND (f.repositoriesId = null )";
            }
            //lnh o phn cng - 140915 binhnt53
            if (form.getLeaderAssignId() != null && form.getLeaderAssignId() > 0L) {
                condition += " AND (f.leaderAssignId = ? )";
                lstParam.add(form.getLeaderAssignId());
            }
            // tra cuc van thu ngay tiep nhan tu ngay den ngay
            if (form.getReceivedDate() != null) {
                condition += " and f.receivedDate >= ?";
                lstParam.add(minDayToCompare(form.getReceivedDate()));
            }
            if (form.getReceivedDateTo() != null) {
                condition += " and f.receivedDate <= ?";
                lstParam.add(maxDayToCompare(form.getReceivedDateTo()));
            }
            if (form.getReceiveNo() != null && !"".equals(form.getReceiveNo())
                    && !"".equals(form.getReceiveNo().trim())) {
                condition += " and f.receiveNo like ?";
                lstParam.add(form.getReceiveNo().trim());
            }
            if (form.getIs30() != null && form.getIs30() != -1l) {
                if (form.getIs30() == 1) {
                    condition += " AND (f.is30 = 1 )";
                } else {
                    condition += " AND (f.is30 = null )";
                }
            }
            if (form.getSearchFullText() != null && form.getSearchFullText().trim().length() > 0) {
                condition += " and f.fileId in (select ffs.fileId from FileForSearch ffs where lower(ffs.content) like ? ESCAPE '/')";
                lstParam.add(StringUtils.toLikeString(form.getSearchFullText()));
            }
        }
        if (form.getProductTypeNew() != null && form.getProductTypeNew().longValue() != -1) {
            String str = ", Category c";
            if (!hql.contains(str)) {
                hql += ", Category c";
                condition += " and  f.productTypeId = c.categoryId";
            }
            if (form.getProductTypeNew() == 1) {
                condition += " AND (c.code <> ? and c.code <> ?) ";
                lstParam.add(Constants.CATEGORY_TYPE.TPCN);
                lstParam.add(Constants.CATEGORY_TYPE.DBT);
            } else {
                condition += " AND (c.code = ? or c.code = ?) ";
                lstParam.add(Constants.CATEGORY_TYPE.TPCN);
                lstParam.add(Constants.CATEGORY_TYPE.DBT);
            }
        }
        if (form.getSearchTypeNew() != null) {
            if (form.getSearchTypeNew() == 1) {
                condition += " AND (c.code <> ? and c.code <> ?) ";
                lstParam.add(Constants.CATEGORY_TYPE.TPCN);
                lstParam.add(Constants.CATEGORY_TYPE.DBT);
            } else {
                condition += " AND (c.code = ? or c.code = ?) ";
                lstParam.add(Constants.CATEGORY_TYPE.TPCN);
                lstParam.add(Constants.CATEGORY_TYPE.DBT);
            }
        }
        //!hieptq update
        Query countQuery = getSession()
                .createQuery("select count(distinct f.fileId) " + hql + " where 1=1 " + condition);
        String finalSql = "select distinct f " + hql + " where 1=1 " + condition + " order by ";
        if (sortCustom.isEmpty()) {
            finalSql += "f.modifyDate DESC";
        } else {
            finalSql += sortCustom;
        }
        Query query = getSession().createQuery(finalSql);

        for (int i = 0; i < lstParam.size(); i++) {
            query.setParameter(i, lstParam.get(i));
            countQuery.setParameter(i, lstParam.get(i));
        }

        int total = Integer.parseInt(countQuery.uniqueResult().toString());
        query.setFirstResult(start);
        if (count > 0) {
            query.setMaxResults(count);
        } else {
            query.setMaxResults(total);
        }
        List<FilesNoClob> lstResult = query.list();

        return new GridResult(total, lstResult);
    }

    /**
     * 160613 tim kiem phan cong cap lai
     *
     * @param form
     * @param deptId
     * @param userId
     * @param start
     * @param count
     * @param sortField
     * @return
     */
    public GridResult findAllFileForAssignEvaluationForRE(FilesForm form, Long deptId, Long userId, int start,
            int count, String sortField) {
        try {
            String hql = " from FilesNoClob f, Process p" + " where f.isActive=1" + " and f.fileId = p.objectId"
            //+ " and (f.productTypeId is null or f.productTypeId = 0)"
                    + " and p.objectType = ?" + " and p.isActive = 1" + " and (f.isTemp is null or f.isTemp = 0 ) "
                    + " and f.fileType in (?,?,?,?,?)";
            List lstParam = new ArrayList();
            lstParam.add(Constants.OBJECT_TYPE.FILES);
            lstParam.add(Constants.FILE_DESCRIPTION.RE_ANNOUNCEMENT_ID);
            lstParam.add(Constants.FILE_DESCRIPTION.RE_CONFIRM_FUNC_IMP_ID);
            lstParam.add(Constants.FILE_DESCRIPTION.REC_CONFIRM_NORMAL_IMP_ID);
            lstParam.add(Constants.FILE_DESCRIPTION.RE_CONFIRM_FUNC_VN_ID);
            lstParam.add(Constants.FILE_DESCRIPTION.RE_CONFIRM_NORMAL_VN_ID);
            if (form != null) {
                if (form.getStatus() == null) {
                    hql += " and (f.status = ?)";
                    lstParam.add(Constants.FILE_STATUS.RECEIVED);
                } else {
                    hql += " and (f.status = ?)";
                    lstParam.add(form.getStatus());
                }
                if (form.getBusinessName() != null && !"".equals(form.getBusinessName().trim())) {
                    hql += " AND lower(f.businessName) like ? ESCAPE '/' ";
                    lstParam.add(StringUtils.toLikeString(form.getBusinessName().toLowerCase().trim()));
                }

                if (form.getProductName() != null && form.getProductName().length() > 0) {
                    hql += " AND lower(f.productName) like ? ESCAPE '/'";
                    lstParam.add(StringUtils.toLikeString(form.getProductName().toLowerCase().trim()));
                }
                if (form.getFileCode() != null && !"".equals(form.getFileCode().trim())) {
                    hql += "AND lower(f.fileCode) like ? ESCAPE '/' ";
                    lstParam.add(StringUtils.toLikeString(form.getFileCode().toLowerCase().trim()));
                }
                if (form.getFileType() != null && form.getFileType().longValue() != -1) {
                    hql += "AND f.fileType = ? ";
                    lstParam.add(form.getFileType());
                }
                if (form.getSendDateFrom() != null) {
                    hql += "AND f.sendDate >= ? ";
                    lstParam.add(form.getSendDateFrom());
                }
                if (form.getSendDateTo() != null) {
                    hql += "AND f.sendDate <= ? ";
                    lstParam.add(form.getSendDateTo());
                }
                if (deptId != null) {
                    hql += "AND (f.agencyId = ?" + " or p.receiveUserId = ?" + " or p.receiveGroupId=? )"
                            + " and (p.processStatus=?" + " or p.processStatus=?" + " or p.processStatus =? ) ";

                    lstParam.add(deptId);
                    lstParam.add(userId);
                    lstParam.add(deptId);
                    lstParam.add(Constants.FILE_STATUS.NEW);
                    lstParam.add(Constants.FILE_STATUS.RECEIVED);
                    lstParam.add(Constants.FILE_STATUS.PROPOSED);
                } else {
                    hql += "AND (p.receiveUserId = ?" + " and (p.processStatus=?)" + " and p.status=? ) ";
                    lstParam.add(userId);
                    lstParam.add(Constants.FILE_STATUS.RECEIVED);
                    lstParam.add(0l);
                }
            }

            Query countQuery = getSession().createQuery("select count(distinct f.fileId) " + hql);
            Query query = getSession().createQuery("select distinct f " + hql + "order by f.modifyDate DESC)");
            for (int i = 0; i < lstParam.size(); i++) {
                query.setParameter(i, lstParam.get(i));
                countQuery.setParameter(i, lstParam.get(i));
            }

            query.setFirstResult(start);
            query.setMaxResults(count);
            int total = Integer.parseInt(countQuery.uniqueResult().toString());
            List<FilesNoClob> lstResult = query.list();
            GridResult gr = new GridResult(total, lstResult);
            return gr;
        } catch (Exception ex) {
            LogUtil.addLog(ex);//binhnt sonar a160901
            return new GridResult(0, null);
        }
    }
}