List of usage examples for org.hibernate ScrollableResults close
void 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 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())); }/* w w w .ja va 2 s. co m*/ 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 " // +/* ww w .j a va 2 s . c o 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 . j av a 2 s . co 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 a2 s .c om*/ 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(); }
From source file:com.ibm.asset.trails.service.impl.ReportServiceImpl.java
License:Open Source License
@Transactional(readOnly = false, propagation = Propagation.NOT_SUPPORTED) public void getLicenseBaselineReport(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 COALESCE(S.software_name, L.Full_Desc) AS Product_Name, CASE LENGTH(COALESCE(S.software_name, '')) WHEN 0 THEN 'No' ELSE 'Yes' END, L.Full_Desc,L.SKU, L.PID as swproPID, CONCAT(CONCAT(RTRIM(CHAR(CT.Code)), '-'), CT.Description), L.environment, L.Quantity, coalesce(L.Quantity - sum(VLUQ.Used_Quantity), L.Quantity), L.Expire_Date,L.END_DATE, L.Po_Number, L.Cpu_Serial, CASE L.IBM_Owned WHEN 1 THEN 'IBM' ELSE 'Customer' END, L.Ext_Src_Id, CASE L.Pool WHEN 0 THEN 'No' ELSE 'Yes' END, L.Record_Time FROM EAADMIN.License L LEFT OUTER JOIN EAADMIN.License_Sw_Map LSWM ON LSWM.License_Id = L.Id LEFT OUTER JOIN EAADMIN.software S ON S.software_id = LSWM.Software_Id LEFT OUTER JOIN EAADMIN.USED_LICENSE VLUQ ON VLUQ.License_Id = L.Id, EAADMIN.Capacity_Type CT"; String lsBaseWhereClause = "WHERE L.Customer_Id = :customerId AND L.Status = 'ACTIVE' AND CT.Code = L.Cap_Type"; 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"); }//from w w w.j a v a 2 s . c om 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 = L.Customer_Id AND SF.Software_Id = S.software_id 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') "); } } else { lsbSql.append(lsBaseSelectAndFromClause).append(" ").append(lsBaseWhereClause).append(" "); } lsbSql.append( "group by COALESCE(S.software_name, L.Full_Desc), CASE LENGTH(COALESCE(S.software_name, '')) WHEN 0 THEN 'No' ELSE 'Yes' END, L.Full_Desc, L.SKU, L.PID, CONCAT(CONCAT(RTRIM(CHAR(CT.Code)), '-'), CT.Description),L.environment, L.Quantity, L.Expire_Date,L.END_DATE, L.Po_Number, L.Cpu_Serial, CASE L.IBM_Owned WHEN 1 THEN 'IBM' ELSE 'Customer' END, L.Ext_Src_Id, CASE L.Pool WHEN 0 THEN 'No' ELSE 'Yes' END, L.Record_Time "); lsbSql.append("ORDER BY Product_Name ASC"); lsrReport = ((Session) getEntityManager().getDelegate()).createSQLQuery(lsbSql.toString()) .setLong("customerId", pAccount.getId()).scroll(ScrollMode.FORWARD_ONLY); printHeader(LICENSE_BASELINE_REPORT_NAME, pAccount.getAccount(), LICENSE_BASELINE_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 getNonWorkstationAccountsReport(String remoteUser, String lsName, PrintWriter pPrintWriter) throws HibernateException, Exception { ScrollableResults lsrReport = ((Session) getEntityManager().getDelegate()).createSQLQuery( "SELECT C.Account_Number, C.Customer_Name, CT.Customer_Type_Name, G.Name AS G_Name, R.Name AS R_Name, CC.Name AS CC_Name, S.Sector_Name, COUNT(H.Id), H.Hardware_Status FROM EAADMIN.Customer C, EAADMIN.Customer_Type CT, EAADMIN.Country_Code CC, EAADMIN.Region R, EAADMIN.Geography G, EAADMIN.Sector S, EAADMIN.Hardware H, EAADMIN.Machine_Type MT WHERE C.Status = 'ACTIVE' AND C.Customer_Type_Id = CT.Customer_Type_Id AND CT.Customer_Type_Name NOT LIKE '%WORKSTATION%' AND G.Id = R.Geography_Id AND R.Id = CC.Region_Id AND CC.Id = C.Country_Code_Id AND S.Sector_Id = C.Sector_Id AND H.Customer_Id = C.Customer_Id AND H.Status = 'ACTIVE' AND MT.Id = H.Machine_Type_Id AND MT.Type = 'WORKSTATION' GROUP BY C.Account_Number, C.Customer_Name, CT.Customer_Type_Name, G.Name, R.Name, CC.Name, S.Sector_Name, H.Hardware_Status ORDER BY C.Account_Number, H.Hardware_Status") .scroll(ScrollMode.FORWARD_ONLY); printHeader(NON_WORKSTATION_ACCOUNTS_REPORT_NAME, null, NON_WORKSTATION_ACCOUNTS_REPORT_COLUMN_HEADERS, pPrintWriter);//from ww w . j av 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 getReconciliationSummaryReport(Account pAccount, String remoteUser, String lsName, PrintWriter pPrintWriter) throws HibernateException, Exception { ScrollableResults lsrReport = ((Session) getEntityManager().getDelegate()).createSQLQuery("select " + " COALESCE (det.software_name,lic.software_name) " + " ,COALESCE(count(det.isw_id),0) " + " ,COALESCE ((count(det.isw_id) - sum(det.open)),0) " + " ,COALESCE (sum(det.open),0) " + " ,COALESCE(lic.free,0) " + " ,COALESCE(lic.pool_type,'') " + " ,COALESCE(det.swOwner,'') " + " from " + " " + " (select " + " sw.software_name as software_name " + " ,sw.software_id as software_id " + " ,isw.id as isw_id " + " ,AUS.Open as open " + " ,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 = sw.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) ), 'Not specified' ) as swOwner " + " from " + " eaadmin.software_lpar sl " + " join eaadmin.installed_software isw on (sl.id = isw.software_lpar_id) " + " join eaadmin.alert_unlicensed_sw aus on (isw.id = aus.installed_software_id) " + " join eaadmin.hw_sw_composite hwsw on (sl.id = hwsw.software_lpar_id) " + " join eaadmin.hardware_lpar hl on (hwsw.hardware_lpar_id = hl.id and sl.customer_id = hl.customer_id) " + " join eaadmin.hardware h on (hl.hardware_id = h.id) " + " join eaadmin.machine_type mt on (h.machine_type_id = mt.id) " + " join eaadmin.software sw on (sw.software_id = isw.software_id) " + " " + " where " + " (exists (select 1 from eaadmin.reconcile r where isw.id = r.installed_software_id) or (aus.open = 1)) " + " and sl.customer_id = :customerId " + " ) det " + " " + " full outer join " + " ( select " + " l.software_id software_id " + " ,quantity.quantity - l.used free " + " ,l.pool_type pool_type " + " ,quantity.software_name software_name " + " from " + " ( select " + " lsm.software_id as software_id " + " ,COALESCE (sum(l.quantity),0) as quantity " + " ,sw.software_name as software_name " + " from " + " eaadmin.license l " + " join eaadmin.license_sw_map lsm on (l.id = lsm.license_id and l.status = 'ACTIVE') " + " join eaadmin.software sw on ( sw.software_id = lsm.software_id ) " + " where l.customer_id= :customerId " + " group by lsm.software_id,sw.software_name " + " ) quantity " + " join ( " + " select " + " lsm.software_id as software_id " + " ,(RTRIM(CAST(ct.code AS CHAR(8) )) || ' - ' || ct.description ) as pool_type " + " ,COALESCE (sum(ul.used_quantity),0) used " + " from " + " eaadmin.license l " + " join eaadmin.license_sw_map lsm on (l.id = lsm.license_id and l.status = 'ACTIVE') " + " left outer join eaadmin.used_license ul on (l.id = ul.license_id) " + " left outer join eaadmin.capacity_type ct on (l.cap_type = ct.code) " + " where l.customer_id= :customerId " + " group by lsm.software_id,ct.code,ct.description " + " ) l on (l.software_id = quantity.software_id) " + " " + " ) lic on ( det.software_id = lic.software_id ) " + " group by " + " det.software_name " + " ,lic.software_name " + " ,det.swOwner " + " ,lic.pool_type " + " ,lic.free " + " order by 1 " + " with ur ") .setLong("customerId", pAccount.getId()).scroll(ScrollMode.FORWARD_ONLY); printHeader(RECONCILIATION_SUMMARY_REPORT_NAME, pAccount.getAccount(), RECONCILIATION_SUMMARY_COLUMN_HEADERS, pPrintWriter); while (lsrReport.next()) { pPrintWriter.println(outputReconciliationSummaryData(lsrReport.get())); }/*from ww w . j av a 2s . c o m*/ 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 getCauseCodeSummaryReport(Account pAccount, String remoteUser, String lsName, PrintWriter pPrintWriter) throws HibernateException, Exception { ScrollableResults lsrReport = ((Session) getEntityManager().getDelegate()).createSQLQuery( "SELECT VA.CAUSE_CODE_ALERT_TYPE,count(*),CASE WHEN VA.Alert_Age > 90 THEN 'Red' WHEN VA.Alert_Age > 45 THEN 'Yellow' ELSE 'Green' END as color,VA.AC_NAME, VA.AC_RESPONSIBILITY from v_alerts VA where VA.Customer_Id = :customerId and VA.open = 1 group by VA.CAUSE_CODE_ALERT_TYPE,VA.AC_NAME, (CASE WHEN VA.Alert_Age > 90 THEN 'Red' WHEN VA.Alert_Age > 45 THEN 'Yellow' ELSE 'Green' END) ,VA.AC_RESPONSIBILITY order by VA.CAUSE_CODE_ALERT_TYPE,VA.AC_NAME, (CASE WHEN VA.Alert_Age > 90 THEN 'Red' WHEN VA.Alert_Age > 45 THEN 'Yellow' ELSE 'Green' END) ,VA.AC_RESPONSIBILITY ") .setLong("customerId", pAccount.getId()).scroll(ScrollMode.FORWARD_ONLY); printHeader(CAUSE_CODE_SUMMARY_REPORT_NAME, pAccount.getAccount(), CAUSE_CODE_SUMMARY_REPORT_COLUMN_HEADERS, pPrintWriter);/*w w w . j a va 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 getSoftwareComplianceSummaryReport(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 { if (pbSelectAllChecked) { pbCustomerOwnedCustomerManagedSearchChecked = true; pbCustomerOwnedIBMManagedSearchChecked = true; pbIBMOwnedIBMManagedSearchChecked = true; pbIBMO3rdMSearchChecked = true;/*w w w.j a va 2s. c om*/ pbCustO3rdMSearchChecked = true; pbIBMOibmMSWCOSearchChecked = true; pbCustOibmMSWCOSearchChecked = true; pbTitlesNotSpecifiedInContractScopeSearchChecked = true; } ScrollableResults lsrReport = ((Session) getEntityManager().getDelegate()).createSQLQuery( "CALL EAADMIN.SwComplianceSum(:customerId, :customerOwnedCustomerManaged, :customerOwnedIBMManaged, :ibmOwnedIBMManaged, :ibmOwned3RDManaged, :customerOwned3RDManaged, :ibmOwnedIbmManagedSWConsumBased, :custOwnedIbmManagedSWConsumBased, :titlesNotSpecifiedInContractScope)") .setLong("customerId", pAccount.getId()) .setInteger("customerOwnedCustomerManaged", pbCustomerOwnedCustomerManagedSearchChecked ? 1 : 0) .setInteger("customerOwnedIBMManaged", pbCustomerOwnedIBMManagedSearchChecked ? 1 : 0) .setInteger("ibmOwnedIBMManaged", pbIBMOwnedIBMManagedSearchChecked ? 1 : 0) .setInteger("ibmOwned3RDManaged", pbIBMO3rdMSearchChecked ? 1 : 0) .setInteger("customerOwned3RDManaged", pbCustO3rdMSearchChecked ? 1 : 0) .setInteger("ibmOwnedIbmManagedSWConsumBased", pbIBMOibmMSWCOSearchChecked ? 1 : 0) .setInteger("custOwnedIbmManagedSWConsumBased", pbCustOibmMSWCOSearchChecked ? 1 : 0) .setInteger("titlesNotSpecifiedInContractScope", pbTitlesNotSpecifiedInContractScopeSearchChecked ? 1 : 0) .scroll(ScrollMode.FORWARD_ONLY); printHeader(SOFTWARE_COMPLIANCE_SUMMARY_REPORT_NAME, pAccount.getAccount(), SOFTWARE_COMPLIANCE_SUMMARY_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 getSoftwareLparBaselineReport(Account pAccount, String remoteUser, String lsName, PrintWriter pPrintWriter) throws HibernateException, Exception { ScrollableResults lsrReport = ((Session) getEntityManager().getDelegate()) .createSQLQuery("SELECT SL.Name AS SL_Name" + ", SL.Bios_Serial" + ", COALESCE ( hle.Processor_Count,0)" + ", 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_Lpar SL " + "LEFT OUTER JOIN EAADMIN.HW_SW_Composite HSC ON HSC.Software_Lpar_Id = SL.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' )" + " WHERE SL.Customer_Id = :customerId AND SL.Status = 'ACTIVE' " + " ORDER BY SL.Name ASC") .setLong("customerId", pAccount.getId()).scroll(ScrollMode.FORWARD_ONLY); printHeader(SOFTWARE_LPAR_BASELINE_REPORT_NAME, pAccount.getAccount(), SOFTWARE_LPAR_BASELINE_COLUMN_HEADERS, pPrintWriter); while (lsrReport.next()) { pPrintWriter.println(outputData(lsrReport.get())); }//from w w w. j av a2s . co m lsrReport.close(); }