Example usage for org.hibernate SQLQuery addScalar

Introduction

In this page you can find the example usage for org.hibernate SQLQuery addScalar.

Prototype

SQLQuery<T> addScalar(String columnAlias, Type type);

Source Link

Document

Declare a scalar query result.

Usage

From source file:com.gp.cong.lcl.common.constant.ExportUnitQueryUtils.java

public LclModel getPickedVoyageByFileId(Long fileId, String serviceType) throws Exception {
    StringBuilder queryStr = new StringBuilder();
    queryStr.append(" SELECT lsh.schedule_no as scheduleNo,lu.unit_no as unitNo ");
    queryStr.append(" FROM lcl_booking_piece lbp ");
    queryStr.append(" JOIN lcl_booking_piece_unit lbpu ON lbpu.booking_piece_id=lbp.id ");
    queryStr.append(" JOIN lcl_unit_ss lus ON lus.id=lbpu.lcl_unit_ss_id");
    queryStr.append(" JOIN lcl_unit lu ON lu.id=lus.unit_id");
    queryStr.append(" JOIN lcl_ss_header lsh ON lsh.id=lus.ss_header_id");
    queryStr.append(" WHERE lbp.file_number_id=:fileId");
    queryStr.append(" AND lsh.service_type=:serviceType");
    SQLQuery query = getSession().createSQLQuery(queryStr.toString());
    query.setParameter("fileId", fileId);
    query.setParameter("serviceType", serviceType);
    query.setResultTransformer(Transformers.aliasToBean(LclModel.class));
    query.addScalar("scheduleNo", StringType.INSTANCE);
    query.addScalar("unitNo", StringType.INSTANCE);
    List<LclModel> pickedList = query.list();
    if (pickedList != null && !pickedList.isEmpty()) {
        return pickedList.get(0);
    }// w  w w.j  av a  2 s .  c  om
    return null;
}

---

From source file:com.gp.cong.lcl.common.constant.ExportUnitQueryUtils.java

public List<LclModel> getBlChargesValidate(Long fileId) throws Exception {
    StringBuilder queryStr = new StringBuilder();
    queryStr.append(/*from w  ww  . ja  v a  2s  . com*/
            " SELECT IF(chg.ar_bill_to_party = 'A',b.agent_acct_no,IF(chg.ar_bill_to_party = 'F',b.fwd_acct_no, ");
    queryStr.append("IF(chg.ar_bill_to_party = 'S',b.ship_acct_no, b.third_party_acct_no))) AS vendorName, ");
    queryStr.append("gm.Charge_code AS chargeCode,chg.ar_bill_to_party AS billToParty   ");
    queryStr.append("FROM lcl_bl b JOIN lcl_bl_ac chg ON b.file_number_id = chg.file_number_id  ");
    queryStr.append("JOIN gl_mapping gm ON gm.id = ar_gl_mapping_id ");
    queryStr.append(
            "WHERE chg.ar_amount > 0.00 AND b.file_number_id  =:fileId   GROUP BY  chg.ar_bill_to_party HAVING vendorName IS NULL ");
    SQLQuery query = getSession().createSQLQuery(queryStr.toString());
    query.setParameter("fileId", fileId);
    query.setResultTransformer(Transformers.aliasToBean(LclModel.class));
    query.addScalar("vendorName", StringType.INSTANCE);
    query.addScalar("chargeCode", StringType.INSTANCE);
    query.addScalar("billToParty", StringType.INSTANCE);
    return query.list();
}

---

From source file:com.gp.cong.lcl.common.constant.ExportUnitQueryUtils.java

public boolean isPickedFile(Long fileId) throws Exception {
    StringBuilder sb = new StringBuilder();
    sb.append(" SELECT if( count(*) > 0, true, false) as result  FROM lcl_booking_piece bp ");
    sb.append(" JOIN lcl_booking_piece_unit bpu ON bpu.`booking_piece_id` = bp.`id` ");
    sb.append(" JOIN lcl_unit_ss lus ON lus.id = bpu.`lcl_unit_ss_id` ");
    sb.append(" JOIN lcl_ss_detail lsd ON lsd.`ss_header_id` = lus.`ss_header_id` ");
    sb.append(" WHERE bp.`file_number_id` =:fileId ");
    SQLQuery query = getCurrentSession().createSQLQuery(sb.toString());
    query.setParameter("fileId", fileId);
    return (boolean) query.addScalar("result", BooleanType.INSTANCE).uniqueResult();

}

---

From source file:com.gp.cong.lcl.common.constant.ExportUnitQueryUtils.java

public boolean isDrContainCollectCharge(Long fileId) throws Exception {
    StringBuilder sb = new StringBuilder();
    sb.append(" select if( count(*)>0 , true ,false ) as result from lcl_bl_ac ac  ");
    sb.append(" join lcl_bl bl on bl.file_number_id = ac.file_number_id ");
    sb.append(" where ac.file_number_id=:fileId  and ac.ar_bill_to_party = 'A' ");
    SQLQuery query = getCurrentSession().createSQLQuery(sb.toString());
    query.setParameter("fileId", fileId);
    return (boolean) query.addScalar("result", BooleanType.INSTANCE).uniqueResult();
}

---

From source file:com.gp.cong.lcl.common.constant.ExportUnitQueryUtils.java

public boolean validateCollectCharges(String headerId, String unitssId, String fileId) throws Exception {
    SQLQuery query = null;
    if (!fileId.isEmpty()) {
        query = getCurrentSession().createSQLQuery("select if( count(*)>0 ,true ,false) as result from "
                + " lcl_bl_ac ac where ac.file_number_id =:fileId and ac.ar_bill_to_party='A'");
        query.setLong("fileId", Long.parseLong(fileId));
    } else {/*  w w w . j  a  v a 2  s  .com*/
        List<Long> actualDrList = new ExportUnitQueryUtils().getAllPickedCargoBkg(Long.parseLong(headerId),
                Long.parseLong(unitssId));
        query = getCurrentSession().createSQLQuery("select if( count(*)>0 ,true ,false) as result from "
                + " lcl_bl_ac ac where ac.file_number_id in(:fileList) and ac.ar_bill_to_party='A'");
        query.setParameterList("fileList", actualDrList);
    }
    return (boolean) query.addScalar("result", BooleanType.INSTANCE).uniqueResult();
}

---

From source file:com.gp.cong.lcl.common.constant.ExportUnitQueryUtils.java

public String getPickedDrBillToParty(Long fileId) throws Exception {
    StringBuilder sb = new StringBuilder();
    sb.append(" SELECT GROUP_CONCAT(DISTINCT ac.`ar_bill_to_party`) AS billtToParty FROM lcl_bl_ac ac ");
    sb.append(" WHERE ac.`file_number_id` =:fileId ");
    SQLQuery query = getCurrentSession().createSQLQuery(sb.toString());
    query.setParameter("fileId", fileId);
    return (String) query.addScalar("billtToParty", StringType.INSTANCE).uniqueResult();
}

---

From source file:com.gp.cong.lcl.common.constant.ExportUnitQueryUtils.java

public List<ExportVoyageSearchModel> searchVoyageDetails(String voyageNumber) throws Exception {
    StringBuilder queryStr = new StringBuilder();
    queryStr.append(/*from  ww  w . j  av a 2s  . co m*/
            " SELECT lclssh.id AS ssHeaderId,lclssh.service_type as serviceType,lclssd.id AS ssDetailId,");
    queryStr.append(" lclssh.schedule_no AS scheduleNo,lclssd.sp_acct_no AS carrierAcctNo,");
    queryStr.append(
            " (SELECT acct_name FROM trading_partner WHERE acct_no = lclssd.sp_acct_no LIMIT 1) AS carrierName,");
    queryStr.append(" (SELECT COUNT(*) FROM lcl_unit lu LEFT JOIN lcl_unit_ss luss ON luss.unit_id = lu.id ");
    queryStr.append(" WHERE lclssh.id = luss.ss_header_id) AS unitcount,");
    queryStr.append(getAppendQueryForUnitNo());
    queryStr.append(" lclssd.sp_reference_name AS vesselName,lclssd.sp_reference_no AS ssVoyage,");
    queryStr.append(" UnLocationGetCodeByID (lclssd.departure_id) AS departPierUnloc,");
    queryStr.append(" UnLocationGetNameStateCntryByID (lclssd.departure_id) AS departPier,");
    queryStr.append(" UnLocationGetCodeByID (lclssd.arrival_id) AS arrivalPierUnloc,");
    queryStr.append(" UnLocationGetNameStateCntryByID (lclssd.arrival_id) AS arrivalPier,");
    queryStr.append(" lclssd.relay_lrd_override AS lrdOverride,lclssd.relay_lrd_override AS lrdOverrideDays, ");
    queryStr.append(
            " DATE_FORMAT(lclssd.general_lrdt, '%d-%b-%Y') AS polLrdDate,lclssd.general_lrdt as polLrdDates,");
    queryStr.append(" lclssd.sta as etaPodDates,lclssd.std as etaSailDates,");
    queryStr.append(
            " DATE_FORMAT(lclssd.std, '%d-%b-%Y') AS etaSailDate,DATE_FORMAT(lclssd.sta, '%d-%b-%Y') AS etaPodDate,");
    queryStr.append(" lclssh.datasource AS dataSource,");
    queryStr.append(" UserDetailsGetLoginNameByID (lclssh.entered_by_user_id) AS createdBy,");
    queryStr.append(" UserDetailsGetLoginNameByID (lclssh.owner_user_id) AS voyOwner,");
    queryStr.append("  (SELECT COUNT(*) FROM lcl_unit lu LEFT JOIN lcl_unit_ss luss ON luss.unit_id = lu.id ");
    queryStr.append("   WHERE lclssh.id = luss.ss_header_id and luss.status ='M') as manifestUnitCount, ");
    queryStr.append("  (SELECT COUNT(*) FROM lcl_unit lu LEFT JOIN lcl_unit_ss luss ON luss.unit_id = lu.id ");
    queryStr.append("   WHERE lclssh.id = luss.ss_header_id and luss.cob = 1 ) as cobUnitCount, ");
    queryStr.append("  (SELECT MAX(CASE WHEN lclssd.sta = luss.cob_datetime THEN 1 ");
    queryStr.append("  WHEN (SELECT COUNT(*) FROM lcl_unit_ss lus  WHERE lus.`ss_header_id` = lclssh.id)  =  ");
    queryStr.append(
            "  (SELECT COUNT(*) FROM lcl_unit_ss lus WHERE lus.`ss_header_id` = lclssh.id AND lus.cob_datetime IS NULL) THEN 2");
    queryStr.append("  WHEN (SELECT COUNT(*) FROM lcl_unit_ss lus ");
    queryStr.append("  WHERE lus.`ss_header_id` = lclssh.id)>1 AND luss.cob_datetime IS NULL THEN 3 ");
    queryStr.append("  WHEN lclssd.sta <> luss.cob_datetime THEN 4 ELSE 0 END ");
    queryStr.append("  ) AS vETA FROM lcl_unit_ss luss WHERE luss.ss_header_id = lclssh.id) AS verifiedEta ");
    queryStr.append(" FROM lcl_ss_header lclssh ");
    queryStr.append(" LEFT JOIN lcl_ss_detail lclssd ON (lclssh.id = lclssd.ss_header_id)");
    queryStr.append(" WHERE ");

    if (CommonUtils.isNotEmpty(voyageNumber)) {
        queryStr.append(" lclssh.schedule_no=:scheduleNo");
    }
    queryStr.append(" GROUP BY lclssh.schedule_no ");
    SQLQuery query = getSession().createSQLQuery(queryStr.toString());
    if (CommonUtils.isNotEmpty(voyageNumber)) {
        query.setParameter("scheduleNo", voyageNumber);
    }
    query.setResultTransformer(Transformers.aliasToBean(ExportVoyageSearchModel.class));
    query.addScalar("ssHeaderId", StringType.INSTANCE);
    query.addScalar("ssDetailId", StringType.INSTANCE);
    query.addScalar("serviceType", StringType.INSTANCE);
    query.addScalar("scheduleNo", StringType.INSTANCE);
    query.addScalar("carrierName", StringType.INSTANCE);
    query.addScalar("carrierAcctNo", StringType.INSTANCE);
    query.addScalar("unitcount", StringType.INSTANCE);
    query.addScalar("unitNo", StringType.INSTANCE);
    query.addScalar("vesselName", StringType.INSTANCE);
    query.addScalar("ssVoyage", StringType.INSTANCE);
    query.addScalar("departPierUnloc", StringType.INSTANCE);
    query.addScalar("departPier", StringType.INSTANCE);
    query.addScalar("arrivalPierUnloc", StringType.INSTANCE);
    query.addScalar("arrivalPier", StringType.INSTANCE);
    query.addScalar("lrdOverrideDays", StringType.INSTANCE);
    query.addScalar("polLrdDate", StringType.INSTANCE);
    query.addScalar("etaSailDate", StringType.INSTANCE);
    query.addScalar("etaPodDate", StringType.INSTANCE);
    query.addScalar("createdBy", StringType.INSTANCE);
    query.addScalar("voyOwner", StringType.INSTANCE);
    query.addScalar("dataSource", StringType.INSTANCE);
    query.addScalar("manifestUnitCount", StringType.INSTANCE);
    query.addScalar("cobUnitCount", StringType.INSTANCE);
    query.addScalar("verifiedEta", IntegerType.INSTANCE);
    return query.list();
}

---

From source file:com.gp.cong.lcl.common.constant.ExportUnitQueryUtils.java

public List<ExportVoyageSearchModel> getMultiBookingSearchList(LclUnitsScheduleForm lclUnitsScheduleForm)
        throws Exception {
    StringBuilder queryStr = new StringBuilder();
    queryStr.append("SELECT lmb.sp_booking_no AS bookingNo,");
    queryStr.append(" lsh.id AS ssHeaderId,lsh.schedule_no as scheduleNo,");
    queryStr.append("  UnLocationGetCodeByID (lsh.destination_id) AS departPierUnloc,");
    queryStr.append(" UnLocationGetCodeByID(lsh.origin_id) AS arrivalPierUnloc,");
    queryStr.append(" UnLocationGetNameStateCntryByID (lsh.origin_id) AS arrivalPier,");
    queryStr.append(" UnLocationGetNameStateCntryByID (lsh.destination_id) AS departPier,");
    queryStr.append(" lsh.origin_id as pooId,lsh.destination_id as fdId ");
    queryStr.append("  FROM  lcl_ss_masterbl lmb ");
    queryStr.append("  JOIN lcl_ss_header lsh ON lsh.id=lmb.ss_header_id ");
    queryStr.append("  WHERE lmb.`sp_booking_no`=");
    queryStr.append("'").append(lclUnitsScheduleForm.getBookingNo()).append("' ");
    SQLQuery query = getSession().createSQLQuery(queryStr.toString());
    query.setResultTransformer(Transformers.aliasToBean(ExportVoyageSearchModel.class));
    query.addScalar("ssHeaderId", StringType.INSTANCE);
    query.addScalar("scheduleNo", StringType.INSTANCE);
    query.addScalar("departPierUnloc", StringType.INSTANCE);
    query.addScalar("departPier", StringType.INSTANCE);
    query.addScalar("arrivalPierUnloc", StringType.INSTANCE);
    query.addScalar("arrivalPier", StringType.INSTANCE);
    query.addScalar("pooId", StringType.INSTANCE);
    query.addScalar("fdId", StringType.INSTANCE);
    query.addScalar("bookingNo", StringType.INSTANCE);
    return query.list();
}

---

From source file:com.gp.cong.logisoft.hibernate.dao.lcl.LclBookingHotCodeDAO.java

public boolean isHotCodeNotExist(String Code, String fileId) throws Exception {
    SQLQuery query = getCurrentSession()
            .createSQLQuery("select if(count(*)<1,true,false) as result from lcl_booking_hot_code "
                    + " where code=:code and file_number_id=:fileId");
    query.setParameter("fileId", fileId);
    query.setParameter("code", Code);
    query.addScalar("result", BooleanType.INSTANCE);
    return (Boolean) query.uniqueResult();
}

---

From source file:com.gp.cong.logisoft.hibernate.dao.lcl.LclBookingHotCodeDAO.java

public boolean isHotCodeExistForThreeDigit(String fileId, String type, String Code) throws Exception { // type is not but exists method will affected
    SQLQuery query = getCurrentSession()
            .createSQLQuery("select if(count(*)<1,true,false) as result from lcl_booking_hot_code "
                    + " where SUBSTRING_INDEX(code,'/',1)=:code and file_number_id=:fileId");
    query.setParameter("fileId", fileId);
    query.setParameter("code", Code);
    query.addScalar("result", BooleanType.INSTANCE);
    return (Boolean) query.uniqueResult();
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• »