Example usage for org.hibernate ScrollableResults next

List of usage examples for org.hibernate ScrollableResults next

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. ScrollableResults
  6. next

Introduction

In this page you can find the example usage for org.hibernate ScrollableResults next.

Prototype

boolean next();

Source Link

Document

Advance to the next result.

Usage

From source file:com.ibm.asset.trails.service.impl.ReportServiceImpl.java

License:Open Source License

@Transactional(readOnly = false, propagation = Propagation.NOT_SUPPORTED)
public void getAlertHardwareLparReport(Account pAccount, String remoteUser, String lsName, HSSFWorkbook phwb,
        OutputStream pOutputStream) throws HibernateException, Exception {

    AlertType alertType = (AlertType) getEntityManager().createNamedQuery("getAlertTypeByCode")
            .setParameter("code", "HW_LPAR").getSingleResult();

    ScrollableResults lsrReport = ((Session) getEntityManager().getDelegate()).createSQLQuery(
            "SELECT CASE WHEN VA.Alert_Age > 90 THEN 'Red' WHEN VA.Alert_Age > 45 THEN 'Yellow' ELSE 'Green' END, HL.Name AS HL_Name, H.Serial, MT.Name AS MT_Name, VA.Creation_Time, VA.Alert_Age, MT.Type, HL.OS_Type, VA.Remote_User, VA.Comments, VA.Record_Time, AC.name as ac_name, CC.target_date, CC.owner as cc_owner,CC.record_time as cc_record_time,CC.remote_user as cc_remote_user, CC.id as cc_id FROM EAADMIN.V_Alerts VA, EAADMIN.Hardware_Lpar HL, EAADMIN.Hardware H, EAADMIN.Machine_Type MT, EAADMIN.cause_code CC, EAADMIN.alert_cause AC WHERE VA.Customer_Id = :customerId AND VA.Type = 'HARDWARE_LPAR' AND VA.Open = 1 AND HL.Id = VA.FK_Id AND H.Id = HL.Hardware_Id AND MT.Id = H.Machine_Type_Id and VA.id=CC.alert_id and CC.alert_type_id = :alertTypeId and CC.alert_cause_id=AC.id ORDER BY HL.Name ASC")
            .setLong("customerId", pAccount.getId()).setInteger("alertTypeId", alertType.getId().intValue())
            .scroll(ScrollMode.FORWARD_ONLY);
    HSSFSheet sheet = phwb.createSheet("Alert HwLPAR Report");
    printHeader(ALERT_HARDWARE_LPAR_REPORT_NAME, pAccount.getAccount(),
            ALERT_HARDWARE_LPAR_REPORT_COLUMN_HEADERS, sheet);
    int i = 3;/*from  w  w  w.j a  v a2 s .co m*/
    while (lsrReport.next()) {
        int k = 1;
        if (i > 65535) {
            k++;
            sheet = phwb.createSheet("Alert HWLpar Report Sheet" + k);
            i = 1;
        }
        HSSFRow row = sheet.createRow((int) i);
        outputData(lsrReport.get(), row);
        i++;
    }

    @SuppressWarnings("unchecked")
    Iterator<Object[]> vCauseCodeSummary = getEntityManager()
            .createNamedQuery("getValidCauseCodesByAlertTypeId").setParameter("alertTypeId", alertType.getId())
            .getResultList().iterator();
    HSSFSheet sheet_2 = phwb.createSheet("Valid Cause Codes");
    HSSFRow rowhead0 = sheet_2.createRow((int) 0);
    outputData(ALERT_VALID_CAUSE_CODE_HEADERS, rowhead0);
    int j = 1;
    while (vCauseCodeSummary.hasNext()) {
        HSSFRow row = sheet_2.createRow((int) j);
        outputData(vCauseCodeSummary.next(), row);
        j++;
    }
    phwb.write(pOutputStream);
}

From source file:com.ibm.asset.trails.service.impl.ReportServiceImpl.java

License:Open Source License

@Transactional(readOnly = false, propagation = Propagation.NOT_SUPPORTED)
public void getAlertHardwareReport(Account pAccount, String remoteUser, String lsName, HSSFWorkbook phwb,
        OutputStream pOutputStream) throws HibernateException, Exception {

    AlertType alertType = (AlertType) getEntityManager().createNamedQuery("getAlertTypeByCode")
            .setParameter("code", "HARDWARE").getSingleResult();

    ScrollableResults lsrReport = ((Session) getEntityManager().getDelegate()).createSQLQuery(
            "SELECT CASE WHEN VA.Alert_Age > 90 THEN 'Red' WHEN VA.Alert_Age > 45 THEN 'Yellow' ELSE 'Green' END, H.Serial, MT.Name, VA.Creation_Time, VA.Alert_Age, MT.Type, VA.Remote_User, VA.Comments, VA.Record_Time,  AC.name as ac_name, CC.target_date,CC.owner as cc_owner,CC.record_time as cc_record_time,CC.remote_user as cc_remote_user, CC.id as cc_id FROM EAADMIN.V_Alerts VA, EAADMIN.Hardware H, EAADMIN.Machine_Type MT, EAADMIN.cause_code CC, EAADMIN.alert_cause AC WHERE VA.Customer_Id = :customerId AND VA.Type = 'HARDWARE' AND VA.Open = 1 AND H.Id = VA.FK_Id AND MT.Id = H.Machine_Type_Id and VA.id=CC.alert_id and CC.alert_type_id = :alertTypeId and CC.alert_cause_id=AC.id ORDER BY H.Serial ASC")
            .setLong("customerId", pAccount.getId()).setInteger("alertTypeId", alertType.getId().intValue())
            .scroll(ScrollMode.FORWARD_ONLY);
    HSSFSheet sheet = phwb.createSheet("Alert Hardware Report");
    printHeader(ALERT_HARDWARE_REPORT_NAME, pAccount.getAccount(), ALERT_HARDWARE_REPORT_COLUMN_HEADERS, sheet);
    int i = 3;/*from   w  w w  .j ava 2 s.c  o m*/
    while (lsrReport.next()) {
        int k = 1;
        if (i > 65535) {
            k++;
            sheet = phwb.createSheet("Alert HW Report Sheet" + k);
            i = 1;
        }
        HSSFRow row = sheet.createRow((int) i);
        outputData(lsrReport.get(), row);
        i++;
    }

    @SuppressWarnings("unchecked")
    Iterator<Object[]> vCauseCodeSummary = getEntityManager()
            .createNamedQuery("getValidCauseCodesByAlertTypeId").setParameter("alertTypeId", alertType.getId())
            .getResultList().iterator();
    HSSFSheet sheet_2 = phwb.createSheet("Valid Cause Codes");
    HSSFRow rowhead0 = sheet_2.createRow((int) 0);
    outputData(ALERT_VALID_CAUSE_CODE_HEADERS, rowhead0);
    int j = 1;
    while (vCauseCodeSummary.hasNext()) {
        HSSFRow row = sheet_2.createRow((int) j);
        outputData(vCauseCodeSummary.next(), row);
        j++;
    }
    phwb.write(pOutputStream);
}

From source file:com.ibm.asset.trails.service.impl.ReportServiceImpl.java

License:Open Source License

@Transactional(readOnly = false, propagation = Propagation.NOT_SUPPORTED)
public void getAlertSoftwareLparReport(Account pAccount, String remoteUser, String lsName, HSSFWorkbook phwb,
        OutputStream pOutputStream) throws HibernateException, Exception {

    AlertType alertType = (AlertType) getEntityManager().createNamedQuery("getAlertTypeByCode")
            .setParameter("code", "SW_LPAR").getSingleResult();

    ScrollableResults lsrReport = ((Session) getEntityManager().getDelegate()).createSQLQuery(SQL_QUERY_SW_LPAR)
            .setLong("customerId", pAccount.getId()).setString("type", "SOFTWARE_LPAR")
            .setInteger("alertTypeId", alertType.getId().intValue()).scroll(ScrollMode.FORWARD_ONLY);

    HSSFSheet sheet = phwb.createSheet("Alert SwLpar Report");
    printHeader(ALERT_SOFTWARE_LPAR_REPORT_NAME, pAccount.getAccount(), ALERT_SW_LPAR_REPORT_COLUMN_HEADERS,
            sheet);/*w  w  w.j  a v  a 2 s. c  om*/
    int i = 3;
    while (lsrReport.next()) {
        int k = 1;
        if (i > 65535) {
            k++;
            sheet = phwb.createSheet("Alert SWLpar Report Sheet" + k);
            i = 1;
        }
        HSSFRow row = sheet.createRow((int) i);
        outputData(lsrReport.get(), row);
        i++;
    }

    @SuppressWarnings("unchecked")
    Iterator<Object[]> vCauseCodeSummary = getEntityManager()
            .createNamedQuery("getValidCauseCodesByAlertTypeId").setParameter("alertTypeId", alertType.getId())
            .getResultList().iterator();
    HSSFSheet sheet_2 = phwb.createSheet("Valid Cause Codes");
    HSSFRow rowhead0 = sheet_2.createRow((int) 0);
    outputData(ALERT_VALID_CAUSE_CODE_HEADERS, rowhead0);
    int j = 1;
    while (vCauseCodeSummary.hasNext()) {
        HSSFRow row = sheet_2.createRow((int) j);
        outputData(vCauseCodeSummary.next(), row);
        j++;
    }
    phwb.write(pOutputStream);
}

From source file:com.ibm.asset.trails.service.impl.ReportServiceImpl.java

License:Open Source License

@Transactional(readOnly = false, propagation = Propagation.NOT_SUPPORTED)
public void getAccountDataExceptionReport(Account pAccount, String remoteUser, String lsName, String pAlertCode,
        PrintWriter pPrintWriter) throws HibernateException, Exception {
    String sql_query_data_exception = null;
    String[] header_of_data_exception = null;
    for (DataExceptionTypeEnum l : DataExceptionTypeEnum.values()) {
        if (pAlertCode.equals(l.name().toString())) {
            if (l.getLevel().equals("SWLPAR")) {
                sql_query_data_exception = SQL_QUERY_ACCOUNT_DATAEXCEPTION_SWLPAR_Report;
                header_of_data_exception = ACCOUNT_DATA_EXCEPTIONS_REPORT_SWLPAR_COLUMN_HEADERS;
            }/*from w  ww  . j ava  2 s  .  co m*/
            if (l.getLevel().equals("HWLPAR")) {
                sql_query_data_exception = SQL_QUERY_ACCOUNT_DATAEXCEPTION_HWLPAR_Report;
                header_of_data_exception = ACCOUNT_DATA_EXCEPTIONS_REPORT_HWLPAR_COLUMN_HEADERS;
            }
            if (l.getLevel().equals("INSTSW")) {
                sql_query_data_exception = SQL_QUERY_ACCOUNT_DATAEXCEPTION_SWDSCEXP_Report;
                header_of_data_exception = ACCOUNT_DATA_EXCEPTIONS_REPORT_SWDSCEXP_COLUMN_HEADERS;
            }
        }
    }

    ScrollableResults lsrReport = ((Session) getEntityManager().getDelegate())
            .createSQLQuery(sql_query_data_exception).setLong("open", 1).setLong("customerId", pAccount.getId())
            .setString("alertCode", pAlertCode).scroll(ScrollMode.FORWARD_ONLY);

    printHeader(ACCOUNT_DATA_EXCEPTIONS_REPORT_NAME, pAccount.getAccount(), header_of_data_exception,
            pPrintWriter);
    while (lsrReport.next()) {
        pPrintWriter.println(outputData(lsrReport.get()));
    }
    lsrReport.close();
}

From source file:com.ibm.asset.trails.service.impl.ReportServiceImpl.java

License:Open Source License

@Transactional(readOnly = false, propagation = Propagation.NOT_SUPPORTED)
public void getAlertUnlicensedIbmSwReport(Account pAccount, String remoteUser, String lsName, HSSFWorkbook phwb,
        OutputStream pOutputStream) throws HibernateException, Exception {
    ScrollableResults lsrReport = ((Session) getEntityManager().getDelegate())
            .createSQLQuery(SQL_QUERY_UNLICENSED_SW).setLong("customerId", pAccount.getId())
            .setString("type", "UNLICENSED_IBM_SW").scroll(ScrollMode.FORWARD_ONLY);
    HSSFSheet sheet = phwb.createSheet("Alert UNLICENSED_IBM_SW Report");
    printHeader(ALERT_UNLICENSED_IBM_SW_REPORT_NAME, pAccount.getAccount(),
            ALERT_UNLICENSED_SW_REPORT_COLUMN_HEADERS, sheet);
    int i = 3;/*w  ww  . ja v  a 2  s  .com*/
    while (lsrReport.next()) {
        int k = 1;
        if (i > 65535) {
            k++;
            sheet = phwb.createSheet("Alert UIBM SW Report Sheet" + k);
            i = 1;
        }
        HSSFRow row = sheet.createRow((int) i);
        outputData(lsrReport.get(), row);
        i++;
    }

    // lsrReport.close();
    @SuppressWarnings("unchecked")
    Iterator<Object[]> vCauseCodeSummary = getEntityManager()
            .createNamedQuery("getValidCauseCodesByAlertTypeId").setParameter("alertTypeId", new Long(17))
            .getResultList().iterator();
    HSSFSheet sheet_2 = phwb.createSheet("Valid Cause Codes");
    HSSFRow rowhead0 = sheet_2.createRow((int) 0);
    outputData(ALERT_VALID_CAUSE_CODE_HEADERS, rowhead0);
    int j = 1;
    while (vCauseCodeSummary.hasNext()) {
        HSSFRow row = sheet_2.createRow((int) j);
        outputData(vCauseCodeSummary.next(), row);
        j++;
    }
    phwb.write(pOutputStream);
}

From source file:com.ibm.asset.trails.service.impl.ReportServiceImpl.java

License:Open Source License

@Transactional(readOnly = false, propagation = Propagation.NOT_SUPPORTED)
public void getAlertUnlicensedIsvSwReport(Account pAccount, String remoteUser, String lsName, HSSFWorkbook phwb,
        OutputStream pOutputStream) throws HibernateException, Exception {
    ScrollableResults lsrReport = ((Session) getEntityManager().getDelegate())
            .createSQLQuery(SQL_QUERY_UNLICENSED_SW).setLong("customerId", pAccount.getId())
            .setString("type", "UNLICENSED_ISV_SW").scroll(ScrollMode.FORWARD_ONLY);
    HSSFSheet sheet = phwb.createSheet("Alert UNLICENSED_ISV_SW Report");
    printHeader(ALERT_UNLICENSED_ISV_SW_REPORT_NAME, pAccount.getAccount(),
            ALERT_UNLICENSED_SW_REPORT_COLUMN_HEADERS, sheet);
    int i = 3;//from  w  w  w .  j  av  a 2 s.  c  o m
    while (lsrReport.next()) {
        int k = 1;
        if (i > 65535) {
            k++;
            sheet = phwb.createSheet("Alert UISV SW Report Sheet" + k);
            i = 1;
        }
        HSSFRow row = sheet.createRow((int) i);
        outputData(lsrReport.get(), row);
        i++;
    }

    // lsrReport.close();
    @SuppressWarnings("unchecked")
    Iterator<Object[]> vCauseCodeSummary = getEntityManager()
            .createNamedQuery("getValidCauseCodesByAlertTypeId").setParameter("alertTypeId", new Long(17))
            .getResultList().iterator();
    HSSFSheet sheet_2 = phwb.createSheet("Valid Cause Codes");
    HSSFRow rowhead0 = sheet_2.createRow((int) 0);
    outputData(ALERT_VALID_CAUSE_CODE_HEADERS, rowhead0);
    int j = 1;
    while (vCauseCodeSummary.hasNext()) {
        HSSFRow row = sheet_2.createRow((int) j);
        outputData(vCauseCodeSummary.next(), row);
        j++;
    }
    phwb.write(pOutputStream);
}

From source file:com.ibm.asset.trails.service.impl.ReportServiceImpl.java

License:Open Source License

@Transactional(readOnly = false, propagation = Propagation.NOT_SUPPORTED)
public void getFreeLicensePoolReport(Account pAccount, String remoteUser, String lsName,
        PrintWriter pPrintWriter) throws HibernateException, Exception {
    ScrollableResults lsrReport = ((Session) getEntityManager().getDelegate()).getNamedQuery("freePoolReport")
            .setLong("account", pAccount.getAccount()).scroll(ScrollMode.FORWARD_ONLY);

    printHeader(FREE_LICENSE_POOL_REPORT_NAME, pAccount.getAccount(), LICENSE_COLUMN_HEADERS, pPrintWriter);
    while (lsrReport.next()) {
        pPrintWriter.println(outputData(lsrReport.get()));
    }/*from   ww w  .jav a  2 s  . c om*/
    lsrReport.close();
}

From source file:com.ibm.asset.trails.service.impl.ReportServiceImpl.java

License:Open Source License

@Transactional(readOnly = false, propagation = Propagation.NOT_SUPPORTED)
public void getFullReconciliationReport(Account pAccount, String remoteUser, String lsName,
        PrintWriter pPrintWriter, boolean pbCustomerOwnedCustomerManagedSearchChecked,
        boolean pbCustomerOwnedIBMManagedSearchChecked, boolean pbIBMOwnedIBMManagedSearchChecked,
        boolean pbIBMO3rdMSearchChecked, boolean pbCustO3rdMSearchChecked, boolean pbIBMOibmMSWCOSearchChecked,
        boolean pbCustOibmMSWCOSearchChecked, boolean pbTitlesNotSpecifiedInContractScopeSearchChecked,
        boolean pbSelectAllChecked) throws HibernateException, Exception {
    String lsBaseSelectClauseOne = "select " + "CASE WHEN AUS.Open = 0 THEN 'Blue' "
            + "WHEN DAYS(CURRENT TIMESTAMP) - DAYS(AUS.Creation_Time) > 90 THEN 'Red' "
            + "WHEN DAYS(CURRENT TIMESTAMP) - DAYS(AUS.Creation_Time) > 45 THEN 'Yellow' " + "ELSE 'Green' "
            + "END " + ",aus.creation_time "
            + ", case when aus.open = 1 then DAYS(CURRENT TIMESTAMP) - DAYS(AUS.Creation_Time) "
            + "else days(aus.record_time) - days(aus.creation_time) " + "END " + ",aus.type "
            + ",sl.name as swLparName " + ",hl.name as hwLparName"
            + ",cast(sl.ext_id as varchar(8)) as SW_EXT_ID" + ",cast(hl.ext_id as varchar(8)) as HW_EXT_ID"
            + ",sl.tech_img_id as SW_TI_ID" + ",hl.tech_image_id as HW_TI_ID" + ",h.serial as hwSerial "
            + ",mt.name as hwMachType "
            + ",COALESCE ( CAST ( (select 'YES' from eaadmin.reconcile_used_license rul2 "
            + "join eaadmin.reconcile r2 on r2.id = rul2.reconcile_id "
            + "join eaadmin.installed_software is2 on is2.id = r2.installed_software_id "
            + "join eaadmin.software_lpar sl2 on sl2.id = is2.software_lpar_id "
            + "where rul2.used_license_id = ul.id and sl2.customer_id != sl.customer_id fetch first 1 rows only) as char(3)), 'NO') as CrossAccountLevel "
            + ",h.model as cpuModel" + ",h.chassis_id" + ",h.cloud_name" + ",h.owner as hwOwner "
            + ",h.country as hwCountry " + ",mt.type as hwAssetType " + ",hl.server_type as serverType "
            + ",hl.SPLA" + ",hl.virtual_flag" + ",hl.virtual_mobility_restriction" + ",hl.os_type "
            + ",cast(hl.SYSPLEX as VARCHAR(8))" + ",cast(sl.SYSPLEX as VARCHAR(8))" + ",hl.cluster_type"
            + ",hl.backupmethod" + ",hl.INTERNET_ICC_FLAG" + ",hl.capped_lpar" + ",h.MAST_PROCESSOR_TYPE"
            + ",h.PROCESSOR_MANUFACTURER" + ",h.PROCESSOR_MODEL" + ",h.NBR_CORES_PER_CHIP"
            + ",h.NBR_OF_CHIPS_MAX" + ",h.CPU_IFL" + ",h.shared_processor" + ",h.CPU_MIPS"
            + ",h.CPU_GARTNER_MIPS" + ",h.CPU_MSU" + ",hl.PART_MIPS" + ",hl.PART_GARTNER_MIPS" + ",hl.PART_MSU"
            + ",h.SHARED" + ",h.MULTI_TENANT" + ",h.hardware_status" + ",hl.lpar_status"
            + ",h.processor_count as hwProcCount " + ",h.chips as hwChips " + ",hl.vcpu as vCPU "
            + ",COALESCE (hle.processor_count,0) as EffProcCount " + ",hl.EFFECTIVE_THREADS "
            + ",case when ibmb.id is not null then COALESCE( CAST( (select pvui.VALUE_UNITS_PER_CORE from eaadmin.pvu_info pvui where pvui.pvu_id=pvum.pvu_id and "
            + "(case when h.nbr_cores_per_chip = 1 then 'SINGLE-CORE' "
            + "when h.nbr_cores_per_chip = 2 then 'DUAL-CORE' "
            + "when h.nbr_cores_per_chip = 4 then 'QUAD-CORE' "
            + "when h.nbr_cores_per_chip > 0  then 'MULTI-CORE' else '' end) = pvui.PROCESSOR_TYPE  fetch first 1 row only ) as CHAR(8)),'base data missing') else 'Non_IBM Product' end as pvuPerCode"
            + ",instS.software_name as primaryComponent " + ",instS.PID as pid "
            + ",case when ba.version != '8.1' then 'N/A' when insTadz.last_used is null or insTadz.last_used = '1970-01-01' then 'Not used' else cast(insTadz.last_used as char(16)) end ";

    String lsBaseSelectClauseTwo = ",COALESCE( COALESCE ( CAST ( (select scop.description from eaadmin.scope scop join eaadmin.schedule_f sf on sf.scope_id = scop.id "
            + "where sf.customer_id = :customerId " + "and sf.status_id=2 "
            + "and sf.software_name = instS.software_name " + "and ( ( sf.level = 'PRODUCT' ) "
            + "or (( sf.hostname = sl.name ) and ( level = 'HOSTNAME' )) "
            + "or (( sf.serial = h.serial ) and ( sf.machine_type = mt.name ) and ( sf.level = 'HWBOX' )) "
            + "or (( sf.hw_owner = h.owner ) and ( sf.level ='HWOWNER' )) ) "
            + "order by sf.LEVEL fetch first 1 rows only) as varchar(64) ),"
            + "CAST ((select scop.description from eaadmin.scope scop join eaadmin.schedule_f sf on sf.scope_id = scop.id "
            + "where sf.customer_id = :customerId and sf.status_id=2 "
            + "and sf.manufacturer_name = instSwMan.name and sf.level ='MANUFACTURER')as varchar(64)) "
            + "), 'Not specified' ) as swOwner ";

    String lsBaseSelectClauseThree = ", 'Not specified' as swOwner ";
    String lsBaseSelectClauseFour = ",aus.remote_user as alertAssignee " + ",aus.comments as alertAssComments "
            + ",instSwMan.name as instSwManName " + ",dt.name as instSwDiscrepName "
            + ",case when rt.is_manual = 0 and sr.id is not null then rt.name || '(SCARLET)' "
            + "when rt.is_manual = 0 and sr.id is null then rt.name || '(AUTO)' "
            + "when rt.is_manual = 1 and sr.id is not null then rt.name || '(SCARLET)' "
            + "when rt.is_manual = 1 and sr.id is null then rt.name || '(MANUAL)' end "
            + ",am.name as reconAllocMethod " + ",r.remote_user as reconUser " + ",r.record_time as reconTime "
            + ",case when rt.is_manual = 0 then 'Auto Close' when rt.is_manual = 1 then r.comments end as reconComments "
            + ",parentS.software_name as parentName " + ",c.account_number as licAccount "
            + ",l.full_desc as licenseName " + ",l.sku" + ",case when l.id is null then '' "
            + "when lsm.id is null then 'No' " + "else 'Yes' end as catalogMatch "
            + ",l.prod_name as licProdName " + ",l.version as licVersion "
            + ",CONCAT(CONCAT(RTRIM(CHAR(L.Cap_Type)), '-'), CT.Description) "
            + ",l.environment as licEnvironment " + ",ul.used_quantity " + ",case when r.id is null then '' "
            + "when r.machine_level = 0 then 'No' " + "else 'Yes' end "
            + ", REPLACE(RTRIM(CHAR(DATE(L.Expire_Date), USA)), '/', '-') " + ",l.po_number " + ",l.cpu_serial "
            + ",case when l.ibm_owned = 0 then 'Customer' " + "when l.ibm_owned = 1 then 'IBM' "
            + "else '' end " + ",l.ext_src_id " + ",l.record_time ";
    String lsBaseFromClause = "from  " + "eaadmin.software_lpar sl "
            + "inner join eaadmin.hw_sw_composite hsc on " + "sl.id = hsc.software_lpar_id "
            + "inner join eaadmin.hardware_lpar hl on " + "hsc.hardware_lpar_id = hl.id "
            + "left outer join eaadmin.hardware_lpar_eff hle on "
            + "( hle.hardware_lpar_id = hl.id and hle.status = 'ACTIVE' )" + "inner join eaadmin.hardware h on "
            + "hl.hardware_id = h.id " + "inner join eaadmin.machine_type mt on " + "h.machine_type_id = mt.id "
            + "inner join eaadmin.installed_software is on " + "sl.id = is.software_lpar_id "
            + "inner join eaadmin.software instS on " + "is.software_id = instS.software_id "
            + "inner join eaadmin.manufacturer instSwMan on " + "instS.manufacturer_id = instSwMan.id "
            + "inner join eaadmin.discrepancy_type dt on " + "is.discrepancy_type_id = dt.id "
            + "inner join eaadmin.alert_unlicensed_sw aus on " + "is.id = aus.installed_software_id "
            + "left outer join eaadmin.installed_tadz insTadz on " + "is.id = insTadz.installed_software_id "
            + "left outer join eaadmin.bank_account ba on " + "insTadz.bank_account_id =  ba.id "
            + "left outer join eaadmin.reconcile r on " + "is.id = r.installed_software_id "
            + "left outer join eaadmin.reconcile_type rt on " + "r.reconcile_type_id = rt.id "
            + "left outer join eaadmin.scarlet_reconcile sr on " + "r.id = sr.id "
            + "left outer join eaadmin.installed_software parent on "
            + "r.parent_installed_software_id = parent.id " + "left outer join eaadmin.software parentS on "
            + "parent.software_id = parentS.software_id "
            + "left outer join eaadmin.reconcile_used_license rul on " + "r.id = rul.reconcile_id "
            + "left outer join eaadmin.used_license ul on " + "rul.used_license_id = ul.id "
            + "left outer join eaadmin.license l on " + "ul.license_id = l.id "
            + "left outer join eaadmin.license_sw_map lsm on " + "l.id = lsm.license_id "
            + "left outer join eaadmin.capacity_type ct on " + "l.cap_type = ct.code "
            + "left outer join eaadmin.customer c on " + "l.customer_id = c.customer_id "
            + "left outer join eaadmin.pvu_map pvum on h.MACHINE_TYPE_ID = pvum.MACHINE_TYPE_ID and h.MAST_PROCESSOR_TYPE = pvum.PROCESSOR_BRAND and h.MODEL = pvum.PROCESSOR_MODEL "
            + "left outer join eaadmin.ibm_brand ibmb on instSwMan.id=ibmb.manufacturer_id "
            + "left outer join eaadmin.allocation_methodology am on r.allocation_methodology_id=am.id ";
    String lsBaseWhereClausea = "where " + "sl.customer_id = :customerId " + "and hl.customer_id = :customerId "
            + "and (aus.open = 1 or (aus.open = 0 and is.id = r.installed_software_id)) ";
    // + "and (sf.id in (select ssf.id  "
    // +//w ww  . j av  a 2  s  .  co m
    // " from schedule_f ssf where  sl.customer_id =  ssf.customer_id and instS.software_name = ssf.SOFTWARE_NAME  order by "
    // +
    // " CASE WHEN  ssf.level='HOSTNAME' and ssf.hostname = sl.name THEN 1 ELSE "
    // +
    // " CASE WHEN  ssf.level='HWBOX' and ssf.serial = h.serial and ssf.machine_type = mt.name THEN 2 ELSE "
    // +
    // "  CASE WHEN ssf.level='HWOWNER' and  ssf.hw_owner = h.owner THEN 3 ELSE "
    // + "  4 END END END "
    // + " fetch first 1 row only ) )";
    String lsBaseWhereClauseb = "where " + "sl.customer_id = :customerId " + "and hl.customer_id = :customerId "
            + "and (aus.open = 1 or (aus.open = 0 and is.id = r.installed_software_id)) ";
    StringBuffer lsbSql = new StringBuffer();
    StringBuffer lsbScopeSql = new StringBuffer();
    ScrollableResults lsrReport = null;

    // ------- BUGFIX --------
    lsbSql.append(lsBaseSelectClauseOne + lsBaseSelectClauseTwo + lsBaseSelectClauseFour + lsBaseFromClause)
            .append(lsBaseWhereClausea);
    // --------------------------
    /*
     * if (pbCustomerOwnedCustomerManagedSearchChecked ||
     * pbCustomerOwnedIBMManagedSearchChecked ||
     * pbIBMOwnedIBMManagedSearchChecked || pbIBMO3rdMSearchChecked ||
     * pbCustO3rdMSearchChecked || pbIBMOibmMSWCOSearchChecked ||
     * pbCustOibmMSWCOSearchChecked || pbSelectAllChecked) { lsbSql.append(
     * lsBaseSelectClauseOne + lsBaseSelectClauseTwo +
     * lsBaseSelectClauseFour + lsBaseFromClause) .append(
     * "inner join EAADMIN.Schedule_F SF on sf.customer_id = sl.customer_id and instS.software_name = sf.SOFTWARE_NAME inner join EAADMIN.Scope scp on SF.scope_id=scp.id "
     * ) .append(lsBaseWhereClausea); if (pbSelectAllChecked) {
     * lsbScopeSql.append("1, 2, 3, 4, 5, 6, 7");
     * pbTitlesNotSpecifiedInContractScopeSearchChecked = true; } else {
     * 
     * if (pbCustomerOwnedCustomerManagedSearchChecked) {
     * lsbScopeSql.append("1"); } if
     * (pbCustomerOwnedIBMManagedSearchChecked) { if (lsbScopeSql.length() >
     * 0) { lsbScopeSql.append(", 2"); } else { lsbScopeSql.append("2"); } }
     * if (pbIBMOwnedIBMManagedSearchChecked) { if (lsbScopeSql.length() >
     * 0) { lsbScopeSql.append(", 3"); } else { lsbScopeSql.append("3"); } }
     * if (pbIBMO3rdMSearchChecked) { if (lsbScopeSql.length() > 0) {
     * lsbScopeSql.append(", 4"); } else { lsbScopeSql.append("4"); } } if
     * (pbCustO3rdMSearchChecked) { if (lsbScopeSql.length() > 0) {
     * lsbScopeSql.append(", 5"); } else { lsbScopeSql.append("5"); } } if
     * (pbIBMOibmMSWCOSearchChecked) { if (lsbScopeSql.length() > 0) {
     * lsbScopeSql.append(", 6"); } else { lsbScopeSql.append("6"); } } if
     * (pbCustOibmMSWCOSearchChecked) { if (lsbScopeSql.length() > 0) {
     * lsbScopeSql.append(", 7"); } else { lsbScopeSql.append("7"); } } }
     * lsbSql.append(" AND SF.Scope_Id IN (") .append(lsbScopeSql)
     * .append(") ") .append(" AND SF.STATUS_ID = 2 ") .append(" and (\r\n"
     * + "  sf.level = 'PRODUCT'\r\n" +
     * " or (( sf.hostname = sl.name ) and ( sf.level = 'HOSTNAME' )) \r\n"
     * +
     * " or (( sf.serial = h.serial ) and ( sf.machine_type = mt.name ) and ( sf.level = 'HWBOX' )) \r\n"
     * + " or (( sf.hw_owner = h.owner ) and ( sf.level ='HWOWNER' )) \r\n"
     * + " )") .append(pbTitlesNotSpecifiedInContractScopeSearchChecked ?
     * "UNION " : ""); } if
     * (pbTitlesNotSpecifiedInContractScopeSearchChecked) { lsbSql.append(
     * lsBaseSelectClauseOne + lsBaseSelectClauseThree +
     * lsBaseSelectClauseFour + lsBaseFromClause) .append(" ")
     * .append(lsBaseWhereClauseb) .append(
     * " AND NOT EXISTS (SELECT SF.Software_Id FROM EAADMIN.Schedule_F SF, EAADMIN.Status S3 WHERE SF.Customer_Id = :customerId AND SF.SOFTWARE_NAME = instS.software_name AND S3.Id = SF.Status_Id AND S3.Description = 'ACTIVE') "
     * ); }
     */
    lsbSql.append("ORDER BY 4");
    lsrReport = ((Session) getEntityManager().getDelegate()).createSQLQuery(lsbSql.toString())
            .setLong("customerId", pAccount.getId()).scroll(ScrollMode.FORWARD_ONLY);

    printHeader(FULL_RECONCILIATION_REPORT_NAME, pAccount.getAccount(),
            FULL_RECONCILIATION_REPORT_COLUMN_HEADERS, pPrintWriter);
    while (lsrReport.next()) {
        pPrintWriter.println(outputData(lsrReport.get()));
    }
    lsrReport.close();
}

From source file:com.ibm.asset.trails.service.impl.ReportServiceImpl.java

License:Open Source License

@Transactional(readOnly = false, propagation = Propagation.NOT_SUPPORTED)
public void getHardwareBaselineReport(Account pAccount, String remoteUser, String lsName,
        PrintWriter pPrintWriter) throws HibernateException, Exception {
    ScrollableResults lsrReport = ((Session) getEntityManager().getDelegate()).createSQLQuery(
            "SELECT H.Serial, MT.Name AS MT_Name, HL.Name AS HL_Name, MT.Type,HL.server_type,HL.SPLA,cast(hl.SYSPLEX as VARCHAR(8)),HL.INTERNET_ICC_FLAG,H.MAST_PROCESSOR_TYPE,H.PROCESSOR_MANUFACTURER,H.PROCESSOR_MODEL,H.NBR_CORES_PER_CHIP,H.NBR_OF_CHIPS_MAX,H.SHARED,H.Hardware_Status,HL.Lpar_Status, CASE LENGTH(RTRIM(COALESCE(CHAR(HSC.Id), ''))) WHEN 0 THEN 'No' ELSE 'Yes' END FROM EAADMIN.Hardware H LEFT OUTER JOIN EAADMIN.Hardware_Lpar HL ON HL.Hardware_Id = H.Id LEFT OUTER JOIN EAADMIN.HW_SW_Composite HSC ON HSC.Hardware_Lpar_Id = HL.Id LEFT OUTER JOIN EAADMIN.Machine_Type MT ON MT.Id = H.Machine_Type_Id WHERE HL.Customer_Id = :customerId AND HL.Status = 'ACTIVE' ORDER BY H.Serial ASC")
            .setLong("customerId", pAccount.getId()).scroll(ScrollMode.FORWARD_ONLY);

    printHeader(HARDWARE_BASELINE_REPORT_NAME, pAccount.getAccount(), HARDWARE_BASELINE_COLUMN_HEADERS,
            pPrintWriter);//from   w  w  w .ja  v  a  2 s. c o  m
    while (lsrReport.next()) {
        pPrintWriter.println(outputData(lsrReport.get()));
    }
    lsrReport.close();
}

From source file:com.ibm.asset.trails.service.impl.ReportServiceImpl.java

License:Open Source License

@Transactional(readOnly = false, propagation = Propagation.NOT_SUPPORTED)
public void getInstalledSoftwareBaselineReport(Account pAccount, String remoteUser, String lsName,
        PrintWriter pPrintWriter, boolean pbCustomerOwnedCustomerManagedSearchChecked,
        boolean pbCustomerOwnedIBMManagedSearchChecked, boolean pbIBMOwnedIBMManagedSearchChecked,
        boolean pbIBMO3rdMSearchChecked, boolean pbCustO3rdMSearchChecked, boolean pbIBMOibmMSWCOSearchChecked,
        boolean pbCustOibmMSWCOSearchChecked, boolean pbTitlesNotSpecifiedInContractScopeSearchChecked,
        boolean pbSelectAllChecked) throws HibernateException, Exception {
    String lsBaseSelectAndFromClause = "SELECT S.software_name AS SI_Name " + ", M.Name AS M_Name "
            + ", CASE S.VENDOR_MANAGED WHEN 1 THEN 'Yes' ELSE 'No' END " + ", SL.Name AS SL_Name "
            + ", SL.Bios_Serial " + ", COALESCE (hle.Processor_Count,0) " + ", H.Chips " + ", SL.ScanTime "
            + ", CASE LENGTH(RTRIM(COALESCE(CHAR(HSC.Id), ''))) " + "WHEN 0 THEN 'No' ELSE 'Yes' END "
            + ", H.Serial " + ", MT.Name AS MT_Name" + ", MT.Type " + "FROM EAADMIN.software S "
            + ", EAADMIN.Manufacturer M " + ", EAADMIN.Software_Lpar SL " + ", EAADMIN.Installed_Software IS "
            + "LEFT OUTER JOIN EAADMIN.HW_SW_Composite HSC ON HSC.Software_Lpar_Id = IS.Software_Lpar_Id "
            + "LEFT OUTER JOIN EAADMIN.Hardware_Lpar HL ON HL.Id = HSC.Hardware_Lpar_Id "
            + "LEFT OUTER JOIN EAADMIN.Hardware H ON H.Id = HL.Hardware_Id "
            + "LEFT OUTER JOIN EAADMIN.Machine_Type MT ON MT.Id = H.Machine_Type_Id "
            + "LEFT OUTER JOIN EAADMIN.Hardware_lpar_eff hle on ( hle.hardware_lpar_id=hl.id and hle.status = 'ACTIVE' )"
            + ", (SELECT SL2.Customer_Id, S2.software_id, S2.Software_Category_Id, MIN(S2.Priority) AS Priority FROM EAADMIN.Software_Lpar SL2 "
            + ", EAADMIN.software S2 " + ", EAADMIN.Product_Info PI2 " + ", EAADMIN.Installed_Software IS2 "
            + " WHERE SL2.Customer_Id = :customerId AND SL2.Status = 'ACTIVE' AND SL2.Id = IS2.Software_Lpar_Id AND S2.software_id = IS2.Software_Id AND S2.level = 'LICENSABLE' AND IS2.Discrepancy_Type_Id IN (1, 2, 4) AND IS2.Status = 'ACTIVE' GROUP BY SL2.Customer_Id, S2.software_id, S2.Software_Category_Id) AS TEMP ";
    String lsBaseWhereClause = "WHERE S.software_id = IS.Software_Id and S.level = 'LICENSABLE' AND M.Id = S.Manufacturer_Id AND SL.Customer_Id = TEMP.Customer_Id AND SL.Status = 'ACTIVE' AND SL.Id = IS.Software_Lpar_Id AND IS.Discrepancy_Type_Id IN (1, 2, 4) AND IS.Status = 'ACTIVE' AND TEMP.software_Id = S.software_id AND TEMP.Software_Category_Id = S.Software_Category_Id AND TEMP.Priority = S.Priority ";
    StringBuffer lsbSql = new StringBuffer();
    StringBuffer lsbScopeSql = new StringBuffer();
    ScrollableResults lsrReport = null;

    if (!((pbCustomerOwnedCustomerManagedSearchChecked && pbCustomerOwnedIBMManagedSearchChecked
            && pbIBMOwnedIBMManagedSearchChecked && pbTitlesNotSpecifiedInContractScopeSearchChecked
            && pbIBMO3rdMSearchChecked && pbCustO3rdMSearchChecked && pbIBMOibmMSWCOSearchChecked
            && pbCustOibmMSWCOSearchChecked) || pbSelectAllChecked)) {
        if (pbCustomerOwnedCustomerManagedSearchChecked || pbCustomerOwnedIBMManagedSearchChecked
                || pbIBMOwnedIBMManagedSearchChecked || pbIBMO3rdMSearchChecked || pbCustO3rdMSearchChecked
                || pbIBMOibmMSWCOSearchChecked || pbCustOibmMSWCOSearchChecked) {
            lsbSql.append(lsBaseSelectAndFromClause).append(", EAADMIN.Schedule_F SF ")
                    .append(lsBaseWhereClause);
            if (pbCustomerOwnedCustomerManagedSearchChecked) {
                lsbScopeSql.append("1");
            }/*w w  w . j a v a  2s.c o  m*/
            if (pbCustomerOwnedIBMManagedSearchChecked) {
                if (lsbScopeSql.length() > 0) {
                    lsbScopeSql.append(", 2");
                } else {
                    lsbScopeSql.append("2");
                }
            }
            if (pbIBMOwnedIBMManagedSearchChecked) {
                if (lsbScopeSql.length() > 0) {
                    lsbScopeSql.append(", 3");
                } else {
                    lsbScopeSql.append("3");
                }
            }
            if (pbIBMO3rdMSearchChecked) {
                if (lsbScopeSql.length() > 0) {
                    lsbScopeSql.append(", 4");
                } else {
                    lsbScopeSql.append("4");
                }
            }
            if (pbCustO3rdMSearchChecked) {
                if (lsbScopeSql.length() > 0) {
                    lsbScopeSql.append(", 5");
                } else {
                    lsbScopeSql.append("5");
                }
            }
            if (pbIBMOibmMSWCOSearchChecked) {
                if (lsbScopeSql.length() > 0) {
                    lsbScopeSql.append(", 6");
                } else {
                    lsbScopeSql.append("6");
                }
            }
            if (pbCustOibmMSWCOSearchChecked) {
                if (lsbScopeSql.length() > 0) {
                    lsbScopeSql.append(", 7");
                } else {
                    lsbScopeSql.append("7");
                }
            }
            lsbSql.append(" AND SF.Customer_Id = SL.Customer_Id AND SF.Software_Id = S.software_id AND "
                    + "(sf.id in (select ssf.id  "
                    + " from schedule_f ssf where  ol.customer_id =  ssf.customer_id and S.software_id = ssf.software_id order by "
                    + "  CASE WHEN  ssf.level='HOSTNAME' and ssf.hostname = SL.name THEN 1 ELSE "
                    + "  CASE WHEN  ssf.level='HWBOX' and ssf.serial = H.serial and ssf.machine_type = mt.name THEN 2 ELSE"
                    + "  CASE WHEN ssf.level='HWOWNER' and  ssf.hw_owner = H.owner THEN 3 ELSE"
                    + "   4 END END END " + "fetch first 1 row only ) )" + "AND SF.Scope_Id IN (")
                    .append(lsbScopeSql).append(") ")
                    .append(pbTitlesNotSpecifiedInContractScopeSearchChecked ? "UNION " : "");
        }
        if (pbTitlesNotSpecifiedInContractScopeSearchChecked) {
            lsbSql.append(lsBaseSelectAndFromClause).append(" ").append(lsBaseWhereClause).append(
                    " AND NOT EXISTS (SELECT SF.Software_Id FROM EAADMIN.Schedule_F SF, EAADMIN.Status ST WHERE SF.Customer_Id = :customerId AND SF.Software_Id = S.software_id AND ST.Id = SF.Status_Id AND ST.Description = 'ACTIVE'"
                            + " AND (\r\n" + "  sf.level = 'PRODUCT'\r\n"
                            + " or (( sf.hostname = sl.name ) and ( sf.level = 'HOSTNAME' )) \r\n"
                            + " or (( sf.serial = h.serial ) and ( sf.machine_type = mt.name ) and ( sf.level = 'HWBOX' )) \r\n"
                            + " or (( sf.hw_owner = h.owner ) and ( sf.level ='HWOWNER' )) \r\n" + " )" + ") ");
        }
    } else {
        lsbSql.append(lsBaseSelectAndFromClause).append(" ").append(lsBaseWhereClause).append(" ");
    }
    lsbSql.append("ORDER BY SI_Name");
    lsrReport = ((Session) getEntityManager().getDelegate()).createSQLQuery(lsbSql.toString())
            .setLong("customerId", pAccount.getId()).scroll(ScrollMode.FORWARD_ONLY);

    printHeader(INSTALLED_SOFTWARE_BASELINE_REPORT_NAME, pAccount.getAccount(),
            INSTALLED_SOFTWARE_BASELINE_COLUMN_HEADERS, pPrintWriter);
    while (lsrReport.next()) {
        pPrintWriter.println(outputData(lsrReport.get()));
    }
    lsrReport.close();
}