private String generateQuery(){
  final StringBuffer query=new StringBuffer(1024);
  final String columnsToShow=" absEst.id, absEst.lat, absEst.lon, absEst.estimateNumber,absEst.name, coalesce(absEst.workValue,0), " + " (select sum(coalesce(ovr.amount,0)) from OverheadValue ovr where ovr.abstractEstimate=absEst)  , " + " (select wpd.worksPackage.wpNumber from WorksPackageDetails wpd where wpd.estimate=absEst and wpd.worksPackage.egwStatus.code not in ('" + WorksConstants.NEW + "','"+ WorksConstants.CANCELLED_STATUS+ "') ), "+ " (select to_char(offLineStatus.statusDate,'dd/MM/YYYY') from OfflineStatus offLineStatus where offLineStatus.objectId = ( select wpd.worksPackage.id from WorksPackageDetails wpd where wpd.estimate=absEst and wpd.worksPackage.egwStatus.code not in ('"+ WorksConstants.NEW+ "','"+ WorksConstants.CANCELLED_STATUS+ "')  )"+ " and offLineStatus.egwStatus.code='"+ WorksConstants.TENDER_DOCUMENT_RELEASED+ "' and objectType='WorksPackage' ), "+ " absEst.parentCategory.description, workSubType.description, projcode.id ";
  final String columnsToShowWhenWOEIsJoined=" absEst.id, absEst.lat, absEst.lon, absEst.estimateNumber,absEst.name, coalesce(absEst.workValue,0)," + " (select sum(coalesce(ovr.amount,0)) from OverheadValue ovr where ovr.abstractEstimate=absEst)  ," + " (select wpd.worksPackage.wpNumber from WorksPackageDetails wpd where wpd.estimate=absEst  and wpd.worksPackage.egwStatus.code not in ('" + WorksConstants.NEW + "','"+ WorksConstants.CANCELLED_STATUS+ "') ), "+ " (select to_char(offLineStatus.statusDate,'dd/MM/YYYY') from OfflineStatus offLineStatus where offLineStatus.objectId = ( select wpd.worksPackage.id from WorksPackageDetails wpd where wpd.estimate=absEst and wpd.worksPackage.egwStatus.code not in ('"+ WorksConstants.NEW+ "','"+ WorksConstants.CANCELLED_STATUS+ "') )"+ " and offLineStatus.egwStatus.code='"+ WorksConstants.TENDER_DOCUMENT_RELEASED+ "' and objectType='WorksPackage' ), "+ " absEst.parentCategory.description, workSubType.description , projcode.id,  "+ " to_char(wo.workOrderDate,'dd/MM/YYYY'), wo.contractPeriod, wo.contractor.name, "+ " (select to_char(offLineStatus.statusDate,'dd/MM/YYYY') from OfflineStatus offLineStatus where offLineStatus.objectId= wo.id  "+ "    and offLineStatus.egwStatus.code='"+ WorksConstants.WO_STATUS_WOCOMMENCED+ "' and objectType='WorkOrder') ";
  if (contractorId != null && contractorId != -1) {
    query.append("select " + columnsToShowWhenWOEIsJoined + " from AbstractEstimate absEst left join absEst.category workSubType left join absEst.projectCode projcode , WorkOrder wo, WorkOrderEstimate woe ");
    query.append(" where  absEst.id=woe.estimate.id and wo.id=woe.workOrder.id  and wo.contractor.id=" + contractorId);
    query.append(" and absEst.parent is null ");
  }
 else {
    query.append("select " + columnsToShow + " from AbstractEstimate absEst left join absEst.category workSubType left join absEst.projectCode projcode ");
    query.append(" where absEst.parent is null ");
  }
  query.append(" and absEst.lat is not null and absEst.lon is not null and absEst.egwStatus.code not in ('" + WorksConstants.NEW + "','"+ WorksConstants.CANCELLED_STATUS+ "') ");
  if (zoneId != null && zoneId != -1)   query.append(" and absEst.ward.parent.id=" + zoneId);
  if (wardId != null && wardId != -1)   query.append(" and absEst.ward.id=" + wardId);
  if (category != null && category != -1)   query.append(" and absEst.category.id=" + category);
 else   if (parentCategory != null && parentCategory != -1)   query.append(" and absEst.parentCategory.id=" + parentCategory);
  if (expenditureType != null && expenditureType != -1)   query.append(" and absEst.type.id=" + expenditureType);
  if (StringUtils.isNotBlank(estimatenumber))   query.append(" and UPPER(absEst.estimateNumber) like '%" + estimatenumber.toUpperCase() + "%'");
  query.append(" order by absEst.id ");
  return query.toString();
}

private String generateQuery ( ) { final StringBuffer query = new StringBuffer ( 1024 ) ; final String columnsToShow = " absEst.id, absEst.lat, absEst.lon, absEst.estimateNumber,absEst.name, coalesce(absEst.workValue,0), " + " (select sum(coalesce(ovr.amount,0)) from OverheadValue ovr where ovr.abstractEstimate=absEst)  , " + " (select wpd.worksPackage.wpNumber from WorksPackageDetails wpd where wpd.estimate=absEst and wpd.worksPackage.egwStatus.code not in ('" + WorksConstants . NEW + "','" + WorksConstants . CANCELLED_STATUS + "') ), " + " (select to_char(offLineStatus.statusDate,'dd/MM/YYYY') from OfflineStatus offLineStatus where offLineStatus.objectId = ( select wpd.worksPackage.id from WorksPackageDetails wpd where wpd.estimate=absEst and wpd.worksPackage.egwStatus.code not in ('" + WorksConstants . NEW + "','" + WorksConstants . CANCELLED_STATUS + "')  )" + " and offLineStatus.egwStatus.code='" + WorksConstants . TENDER_DOCUMENT_RELEASED + "' and objectType='WorksPackage' ), " + " absEst.parentCategory.description, workSubType.description, projcode.id " ; final String columnsToShowWhenWOEIsJoined = " absEst.id, absEst.lat, absEst.lon, absEst.estimateNumber,absEst.name, coalesce(absEst.workValue,0)," + " (select sum(coalesce(ovr.amount,0)) from OverheadValue ovr where ovr.abstractEstimate=absEst)  ," + " (select wpd.worksPackage.wpNumber from WorksPackageDetails wpd where wpd.estimate=absEst  and wpd.worksPackage.egwStatus.code not in ('" + WorksConstants . NEW + "','" + WorksConstants . CANCELLED_STATUS + "') ), " + " (select to_char(offLineStatus.statusDate,'dd/MM/YYYY') from OfflineStatus offLineStatus where offLineStatus.objectId = ( select wpd.worksPackage.id from WorksPackageDetails wpd where wpd.estimate=absEst and wpd.worksPackage.egwStatus.code not in ('" + WorksConstants . NEW + "','" + WorksConstants . CANCELLED_STATUS + "') )" + " and offLineStatus.egwStatus.code='" + WorksConstants . TENDER_DOCUMENT_RELEASED + "' and objectType='WorksPackage' ), " + " absEst.parentCategory.description, workSubType.description , projcode.id,  " + " to_char(wo.workOrderDate,'dd/MM/YYYY'), wo.contractPeriod, wo.contractor.name, " + " (select to_char(offLineStatus.statusDate,'dd/MM/YYYY') from OfflineStatus offLineStatus where offLineStatus.objectId= wo.id  " + "    and offLineStatus.egwStatus.code='" + WorksConstants . WO_STATUS_WOCOMMENCED + "' and objectType='WorkOrder') " ; if ( contractorId != null && contractorId != - 1 ) { query . append ( "select " + columnsToShowWhenWOEIsJoined + " from AbstractEstimate absEst left join absEst.category workSubType left join absEst.projectCode projcode , WorkOrder wo, WorkOrderEstimate woe " ) ; query . append ( " where  absEst.id=woe.estimate.id and wo.id=woe.workOrder.id  and wo.contractor.id=" + contractorId ) ; query . append ( " and absEst.parent is null " ) ; } else { query . append ( "select " + columnsToShow + " from AbstractEstimate absEst left join absEst.category workSubType left join absEst.projectCode projcode " ) ; query . append ( " where absEst.parent is null " ) ; } query . append ( " and absEst.lat is not null and absEst.lon is not null and absEst.egwStatus.code not in ('" + WorksConstants . NEW + "','" + WorksConstants . CANCELLED_STATUS + "') " ) ; if ( zoneId != null && zoneId != - 1 ) query . append ( " and absEst.ward.parent.id=" + zoneId ) ; if ( wardId != null && wardId != - 1 ) query . append ( " and absEst.ward.id=" + wardId ) ; if ( category != null && category != - 1 ) query . append ( " and absEst.category.id=" + category ) ; else if ( parentCategory != null && parentCategory != - 1 ) query . append ( " and absEst.parentCategory.id=" + parentCategory ) ; if ( expenditureType != null && expenditureType != - 1 ) query . append ( " and absEst.type.id=" + expenditureType ) ; if ( StringUtils . isNotBlank ( estimatenumber ) ) query . append ( " and UPPER(absEst.estimateNumber) like '%" + estimatenumber . toUpperCase ( ) + "%'" ) ; query . append ( " order by absEst.id " ) ; return query . toString ( ) ; }
return values in the order - estimate id [ 0 ] , lat [ 1 ] , lon [ 2 ] , estimate number [ 3 ] , name [ 4 ] , work value [ 5 ] , overheads [ 6 ] , wpno [ 7 ] tender document released date [ 8 ] , type of work [ 9 ] , worksubtype [ 10 ] , projcode . id [ 11 ] in case contractor is selected in search criteria , the following work order related values are returned from this query : work order date [ 12 ] , contract period [ 13 ] , contractor name [ 14 ] , work commenced date [ 15 ]
