public DataTable CreateDataTableForCustomReport(List <string> aReportConditions, string aAssetIds) { DataTable lResultsDataTable = new DataTable(); bool bPatchesIncluded = false; try { Cursor.Current = Cursors.WaitCursor; bool lDisplayAsAssetRegister = false; // aReportConditions contains a list of all criteria and a list of required assets // loop through each asset and get the data for each column // need to build a DataTable with the required columns string lDisplayResultsAsAssetRegister = ""; foreach (string lReportCondition in aReportConditions) { if (lDisplayResultsAsAssetRegister == "") { if (lReportCondition.StartsWith("ASSET_REGISTER:")) { lDisplayResultsAsAssetRegister = lReportCondition; break; } } } // an empty lSelectedAssets means that all assets have been selected if (aAssetIds == "") { aAssetIds = new AssetDAO().GetAllAssetIdsAsString(); } if (lDisplayResultsAsAssetRegister != "") { lDisplayAsAssetRegister = Convert.ToBoolean(lDisplayResultsAsAssetRegister.Substring(15)); } // if all children of a parent node are selected, we will only see the parent here // i.e. Hardware|CPU means we have selected all children of that root // we need to loop through each of the fields now to handle this case _updatedReportConditions.Clear(); foreach (string lReportCondition in aReportConditions) { List <string> listParts = Utility.ListFromString(lReportCondition, '|', true); switch (listParts[0]) { case AWMiscStrings.AssetDetails: ExpandAssetDetailFieldChildren(lReportCondition); break; case AWMiscStrings.OSNode: ExpandOSFieldChildren(lReportCondition); break; case AWMiscStrings.ApplicationsNode: ExpandApplicationFieldChildren(lReportCondition); break; case AWMiscStrings.HardwareNode: ExpandAuditedItemsChildren(lReportCondition); break; case AWMiscStrings.SystemNode: ExpandAuditedItemsChildren(lReportCondition); break; case AWMiscStrings.UserDataNode: ExpandUserDataChildren(lReportCondition); break; default: break; } } DataRow[] rows; string lCurrentReportCondition; if (!lDisplayAsAssetRegister) { lResultsDataTable = BuildResultsTableStandard(_updatedReportConditions); object[] lNewRowArray = new object[_updatedReportConditions.Count]; DataTable lUnionResultsDataTable = RunCustomUnionStatement(aAssetIds); //object[] lNewRowArray = new object[lUnionResultsDataTable.Rows.Count]; foreach (string lAssetId in aAssetIds.Split(',')) { for (int i = 0; i < _updatedReportConditions.Count; i++) { lCurrentReportCondition = _updatedReportConditions[i]; rows = lUnionResultsDataTable.Select(String.Format("ASSETID = {0} AND REFID = {1}", lAssetId, i)); // deal with applications differently if (lCurrentReportCondition.StartsWith("Applications|")) { lNewRowArray[i] = (rows.Length == 0) ? "Not Installed" : "Installed"; } else { lNewRowArray[i] = (rows.Length == 0) ? "" : rows[0].ItemArray[1].ToString(); } if (lCurrentReportCondition.StartsWith("System|Patches")) { bPatchesIncluded = true; } } if (bPatchesIncluded) { string strAssetName = lNewRowArray[0].ToString(); int iColCount = lResultsDataTable.Columns.Count; object[] lRowArray = new object[iColCount]; for (int j = 1; j < lNewRowArray.Length; j = j + 4) { for (int i = 0; i < iColCount - 2; i++) { lRowArray[i + 2] = lNewRowArray[i + j].ToString(); } lRowArray[0] = strAssetName; string strPatchItem = _updatedReportConditions[j].ToString(); String [] lReportConditions = strPatchItem.Split('|'); strPatchItem = lReportConditions[lReportConditions.Length - 3] + "|" + lReportConditions[lReportConditions.Length - 2]; lRowArray[1] = strPatchItem; //check before adding applies to this asset bool bInsert = false; for (int i = 2; i < iColCount; i++) { if (lRowArray[i].ToString() != "") { bInsert = true; break; } } if (bInsert) { lResultsDataTable.Rows.Add(lRowArray); } } } else { lResultsDataTable.Rows.Add(lNewRowArray); } } } else { lResultsDataTable = new DataTable(); lResultsDataTable.Columns.Add("Asset Name", typeof(string)); lResultsDataTable.Columns.Add("Field Name", typeof(string)); lResultsDataTable.Columns.Add("Value", typeof(string)); DataTable lUnionResultsDataTable = RunCustomUnionStatement(aAssetIds); foreach (string lAssetId in aAssetIds.Split(',')) { string lAssetName = lAssetDAO.ConvertIdListToNames(lAssetId, ';'); bPatchesIncluded = false; bool bItemRowAdded = false; int iCount = 0; List <object[]> listupdatedPatch = new List <object[]>(); for (int i = 0; i < _updatedReportConditions.Count; i++) { object[] lNewRowArray = new object[3]; lCurrentReportCondition = _updatedReportConditions[i]; rows = lUnionResultsDataTable.Select(String.Format("ASSETID = {0} AND REFID = {1}", lAssetId, i)); lNewRowArray[0] = lAssetName; lNewRowArray[1] = lCurrentReportCondition.Split('|')[lCurrentReportCondition.Split('|').Length - 1]; // deal with applications differently if (lCurrentReportCondition.StartsWith("Applications|")) { lNewRowArray[2] = (rows.Length == 0) ? "Not Installed" : "Installed"; } else { lNewRowArray[2] = (rows.Length == 0) ? "" : rows[0].ItemArray[1].ToString(); } if (lCurrentReportCondition.StartsWith("System|Patches")) { bPatchesIncluded = true; } if (bPatchesIncluded) { object[] NewRowArray = new object[3]; NewRowArray[0] = lAssetName; NewRowArray[1] = "Item"; String[] lReportConditions = lCurrentReportCondition.Split('|'); string strPatchItem = lReportConditions[lReportConditions.Length - 3] + "|" + lReportConditions[lReportConditions.Length - 2]; NewRowArray[2] = strPatchItem; if (!bItemRowAdded) { //lResultsDataTable.Rows.Add(NewRowArray); listupdatedPatch.Add(NewRowArray); bItemRowAdded = true; } iCount++; //lResultsDataTable.Rows.Add(lNewRowArray); listupdatedPatch.Add(lNewRowArray); if (iCount == 4) { //We had the details for a patch check it is associated with the asset bItemRowAdded = false; iCount = 0; bool bInsert = false; for (int k = 1; k < listupdatedPatch.Count - 1; k++) { if (listupdatedPatch[k][2].ToString() != "") { bInsert = true; break; } } if (bInsert) { foreach (object[] objItem in listupdatedPatch) { lResultsDataTable.Rows.Add(objItem); } listupdatedPatch.Clear(); } else { listupdatedPatch.Clear(); } } } else { lResultsDataTable.Rows.Add(lNewRowArray); } } } lResultsDataTable.DefaultView.Sort = "Asset Name ASC"; } } catch (OutOfMemoryException ex) { logger.Error(ex.Message); MessageBox.Show( "An out of memory exception has occured. You may have too many items in your Custom Report." + Environment.NewLine + Environment.NewLine + "Please reduce the number of assets and/or report fields and re-run the query.", "AuditWizard", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } catch (System.Data.SqlClient.SqlException ex) { if (ex.Number == 701 || ex.Number == 8623) { MessageBox.Show( "An SQL Exception has occured. You may have too many items in your Custom Report." + Environment.NewLine + Environment.NewLine + "Please reduce the number of assets and/or report fields and re-run the query.", "AuditWizard", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } } catch (Exception ex) { logger.Error(ex.Message); } finally { Cursor.Current = Cursors.Default; } return(lResultsDataTable); }