public static dynamic GenerateSystemData(DataRow row, string systemName, object assetId, dynamic bestKnown, bool organizationNa = false, bool locationNa = false, bool custodianNa = false, bool statusNa = false, bool showOrganization = true, bool showLocation = true, bool showCustodian = true, bool showStatus = true) { string originalName = systemName; systemName = systemName == "EAS" ? "SimonEfbi" : systemName; string organization = organizationNa ? "n/a" : TopazUtility.FormatOwningDept(TopazDatabaseUtilities.ValueOrDefault <string>(systemName + "BU", row), TopazDatabaseUtilities.ValueOrDefault <string>(systemName + "Home", row), TopazDatabaseUtilities.ValueOrDefault <string>(systemName + "Department", row)); string organizationTransform = organizationNa ? "n/a" : TopazUtility.FormatOwningDept(TopazDatabaseUtilities.ValueOrDefault <string>(systemName + "BUTransform", row), TopazDatabaseUtilities.ValueOrDefault <string>(systemName + "HomeTransform", row), TopazDatabaseUtilities.ValueOrDefault <string>(systemName + "DepartmentTransform", row)); return(new { name = originalName, assetId = assetId, organizationSource = !showOrganization ? "n/a" : organization, locationSource = !showLocation ? "n/a" : row[systemName + "Building"], custodianSource = !showCustodian ? "n/a" : row[systemName + "Custodian"], statusSource = !showStatus ? "n/a" : row[systemName + "Status"], organizationTransformed = !showOrganization ? "n/a" : organizationTransform, locationTransformed = !showLocation ? "n/a" : row[systemName + "BuildingTransform"], custodianTransformed = !showCustodian ? "n/a" : row[systemName + "CustodianTransform"], statusTransformed = !showStatus ? "n/a" : row[systemName + "PropertyStatusTransform"], organizationValid = organizationNa ? 0 : TopazDatabaseUtilities.ValueOrDefault <bool>(systemName + "OrganizationValid", row) ? 1 : 0, locationValid = locationNa ? 0 : TopazDatabaseUtilities.ValueOrDefault <bool>(systemName + "LocationValid", row) ? 1 : 0, custodianValid = custodianNa ? 0 : TopazDatabaseUtilities.ValueOrDefault <bool>(systemName + "CustodianValid", row) ? 1 : 0, statusValid = statusNa ? 0 : TopazDatabaseUtilities.ValueOrDefault <bool>(systemName + "PropertyStatusValid", row) ? 1 : 0, organizationMatch = organizationNa ? false : organizationTransform.Equals(bestKnown.organization), locationMatch = locationNa ? false : row[systemName + "BuildingTransform"].Equals(bestKnown.location), custodianMatch = custodianNa ? false : row[systemName + "CustodianTransform"].Equals(bestKnown.custodian), statusMatch = statusNa ? false : row[systemName + "PropertyStatusTransform"].Equals(bestKnown.status), organizationNa = organizationNa || organizationTransform.Equals("n/a"), locationNa = locationNa || TopazDatabaseUtilities.ValueOrDefault <string>(systemName + "BuildingTransform", row) == "n/a", custodianNa = custodianNa || TopazDatabaseUtilities.ValueOrDefault <long>(systemName + "CustodianTransform", row) == 0, statusNa = statusNa || TopazDatabaseUtilities.ValueOrDefault <string>(systemName + "PropertyStatusTransform", row) == "n/a" }); }
public static string AssetData(OrganizationSelection organization, LocationSelection location, EmployeeSelection employee, TopazRequest request) { string employeeJoin; string whereClause; string[] parameters; string[] values; Topaz.Domain.Concrete.Dashboards.Assets.Inventory.SetBigAssetQueryData(organization, location, employee, request, out employeeJoin, out whereClause, out parameters, out values); string query = string.Format(@" select top {3} {0} from Assets inner join Organizations on Organizations.BU = [Assets].BU and Organizations.Home = [Assets].Home and Organizations.Department = [Assets].Department inner join Locations on Locations.Building = [Assets].Building {2} inner join AssetRecordDetail on AssetRecordDetail.AssetId=Assets.AssetId left join SimonAssets on SimonAssets.EQUIP_ID=[Assets].SIMON_EQUIP_ID left join CmisAssets on CmisAssets.CONTROL_NUMBER=CMIS_CONTROL_NUMBER left join SIMONEFBI on SIMONEFBI.Asset_Id=[Assets].EFBI_ASSET_ID left join MaximoAssets on MaximoAssets.ASSETID_BestKnown=Assets.Maximo_AssetId left join AsmesAssets AssetSmartAssets on AssetSmartAssets.asset_id=[Assets].asmes_asset_id left join CribmasterAssets CribA on CribA.SERIALID=[Assets].Cribmaster_SERIALID left join CribmasterAssets CribB on CribB.ALTSCANCODE=[Assets].Cribmaster_SERIALID where 1=1 {1}", SELECTIONS, whereClause /*.Replace("[Custodian]", "Assets.[Custodian]")*/, employeeJoin, pageAssetLimit); //Make employee table to fix issue in iteration 34 DataTable employeeData = TopazDatabaseUtilities.BemsToNameDataTable(); DataTable data = TopazDatabaseUtilities.DataTableFromQuery(query, "Topaz", parameters.ToArray(), values.ToArray()); data.Columns.Add("AssetSystemRecordConsistencyData", typeof(string)); data.Columns.Add("Organization", typeof(string)); data.Columns.Add("SourceSystems", typeof(string)); JavaScriptSerializer serializer = new JavaScriptSerializer(); StringBuilder builder = new StringBuilder(); builder.Append("{"); foreach (DataRow row in data.Rows) { dynamic bestKnown = new { name = "Best known", organization = TopazUtility.FormatOwningDept(TopazDatabaseUtilities.ValueOrDefault <string>("AssetBU", row), TopazDatabaseUtilities.ValueOrDefault <string>("AssetHome", row), TopazDatabaseUtilities.ValueOrDefault <string>("AssetDepartment", row)), location = row["AssetBuilding"], custodian = row["AssetCustodian"], status = row["AssetStatus"], organizationValid = TopazDatabaseUtilities.ValueOrDefault <bool>("AssetOrganizationValid", row), locationValid = TopazDatabaseUtilities.ValueOrDefault <bool>("AssetLocationValid", row), custodianValid = TopazDatabaseUtilities.ValueOrDefault <bool>("AssetCustodianValid", row), statusValid = TopazDatabaseUtilities.ValueOrDefault <bool>("AssetStatusValid", row) }; List <string> systems = new List <string>(); if ((int)row["ContainsCmis"] == 1) { systems.Add("CMIS"); } if ((int)row["ContainsSimon"] == 1) { systems.Add("SIMON"); } if ((int)row["ContainsSimonEfbi"] == 1) { systems.Add("EAS"); } if ((int)row["ContainsAssetSmart"] == 1) { systems.Add("AssetSmart"); } if ((int)row["ContainsMaximo"] == 1) { systems.Add("Maximo"); } if ((int)row["ContainsCribmaster"] == 1) { systems.Add("Cribmaster"); } row["Organization"] = bestKnown.organization; row["SourceSystems"] = string.Join(", ", systems); builder.Append(serializer.Serialize(TopazDatabaseUtilities.ValueOrDefault <string>("AssetId", row))).Append(":").Append(GenerateAssetSystemsConsistencyJson(GetSystemsFromDataRow(row, bestKnown), employeeData)).Append(","); row["AssetSystemRecordConsistencyData"] = ""; } builder.Append("}"); string countWarningQuery = string.Format(@" select count(*) as assetCount from Assets inner join Organizations on Organizations.BU = [Assets].BU and Organizations.Home = [Assets].Home and Organizations.Department = [Assets].Department left join Locations on Locations.Building = [Assets].Building {1} inner join AssetRecordDetail on AssetRecordDetail.AssetId=Assets.AssetId where 1=1 {0}", whereClause /*.Replace("[Custodian]", "Assets.[Custodian]")*/, employeeJoin); DataTable dataCount = TopazDatabaseUtilities.DataTableFromQuery(countWarningQuery, "Topaz", parameters.ToArray(), values.ToArray()); string assetCount = dataCount.Rows[0]["assetCount"].ToString(); string recordConsistency = builder.ToString(); string assetList = data.ToJson(); return(new Dictionary <string, string>() { { "matrix", recordConsistency }, { "list", assetList }, { "assetCount", assetCount }, { "pageLimit", pageAssetLimit.ToString() } }.ToJson(quotes: false, autoQuotes: false, serialize: false)); }