public static void ExportRawData(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 {2} Assets.AssetId, Assets.Manufacturer, Assets.Model, Assets.SerialNo, SimonBUTransform, SimonHomeTransform, SimonDepartmentTransform, SimonOrganizationValid, SimonBuildingTransform, SimonLocationValid, SimonCustodianTransform, SimonCustodianValid, SimonPropertyStatusTransform, SimonPropertyStatusValid, CmisBUTransform, CmisHomeTransform, CmisDepartmentTransform, CmisOrganizationValid, CmisBuildingTransform, CmisLocationValid, CmisCustodianTransform, CmisCustodianValid, CmisPropertyStatusTransform, CmisPropertyStatusValid, GoldBUTransform, GoldHomeTransform, GoldDepartmentTransform, GoldOrganizationValid, GoldBuildingTransform, GoldLocationValid, GoldCustodianTransform, GoldCustodianValid, GoldPropertyStatusTransform, GoldPropertyStatusValid, AsmesBUTransform as AssetSmartBUTransform, AsmesHomeTransform as AssetSmartHomeTransform, AsmesDepartmentTransform as AssetSmartDepartmentTransform, AsmesOrganizationValid as AssetSmartOrganizationValid, AsmesBuildingTransform as AssetSmartBuildingTransform, AsmesLocationValid as AssetSmartLocationValid, AsmesCustodianTransform as AssetSmartCustodianTransform, AsmesCustodianValid as AssetSmartCustodianValid, AsmesPropertyStatusTransform as AssetSmartPropertyStatusTransform, AsmesPropertyStatusValid as AssetSmartPropertyStatusValid, CribmasterBUTransform as CribmasterBUTransform, CribmasterHomeTransform as CribmasterHomeTransform, CribmasterDepartmentTransform as CribmasterDepartmentTransform, CribmasterOrganizationValid as CribmasterOrganizationValid, CribmasterBuildingTransform as CribmasterBuildingTransform, CribmasterLocationValid as CribmasterLocationValid, CribmasterCustodianTransform as CribmasterCustodianTransform, CribmasterCustodianValid as CribmasterCustodianValid, CribmasterPropertyStatusTransform as CribmasterPropertyStatusTransform, CribmasterPropertyStatusValid as CribmasterPropertyStatusValid, SimonEfbiBUTransform as EasBUTransform, SimonEfbiHomeTransform as EasHomeTransform, SimonEfbiDepartmentTransform as EasDepartmentTransform, SimonEfbiOrganizationValid as EasOrganizationValid, SimonEfbiBuildingTransform as EasBuildingTransform, SimonEfbiLocationValid as EasLocationValid, SimonEfbiPropertyStatusTransform as EasPropertyStatusTransform, SimonEfbiPropertyStatusValid as EasPropertyStatusValid, MaximoBUTransform as MaximoBUTransform, MaximoHomeTransform as MaximoHomeTransform, MaximoDepartmentTransform as MaximoDepartmentTransform, MaximoOrganizationValid as MaximoOrganizationValid, MaximoBuildingTransform as MaximoBuildingTransform, MaximoLocationValid as MaximoLocationValid, MaximoPropertyStatusTransform as MaximoPropertyStatusTransform, MaximoPropertyStatusValid as MaximoPropertyStatusValid, SystemRecordConsistency, Organizations.*, Locations.* 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 inner join AssetRecordDetail on AssetRecordDetail.AssetId=Assets.AssetId {1} where 1=1 {0} ", whereClause, employeeJoin, pageAssetLimit); DataTable data = TopazDatabaseUtilities.DataTableFromQuery(query, "Topaz", parameters, values); string[] columns = { "AssetId", "Manufacturer", "Model", "SerialNo", "SimonBUTransform", "SimonHomeTransform", "SimonDepartmentTransform", "SimonOrganizationValid", "SimonBuildingTransform", "SimonLocationValid", "SimonCustodianTransform", "SimonCustodianValid", "SimonPropertyStatusTransform", "SimonPropertyStatusValid", "CmisBUTransform", "CmisHomeTransform", "CmisDepartmentTransform", "CmisOrganizationValid", "CmisBuildingTransform", "CmisLocationValid", "CmisCustodianTransform", "CmisCustodianValid", "CmisPropertyStatusTransform", "CmisPropertyStatusValid", "GoldBUTransform", "GoldHomeTransform", "GoldDepartmentTransform", "GoldOrganizationValid", "GoldBuildingTransform", "GoldLocationValid", "GoldCustodianTransform", "GoldCustodianValid", "GoldPropertyStatusTransform", "GoldPropertyStatusValid", "AssetSmartBUTransform", "AssetSmartHomeTransform", "AssetSmartDepartmentTransform", "AssetSmartOrganizationValid","AssetSmartBuildingTransform", "AssetSmartLocationValid", "AssetSmartCustodianTransform", "AssetSmartCustodianValid", "AssetSmartPropertyStatusTransform", "AssetSmartPropertyStatusValid", "CribmasterBUTransform", "CribmasterHomeTransform", "CribmasterDepartmentTransform", "CribmasterOrganizationValid","CribmasterBuildingTransform", "CribmasterLocationValid", "CribmasterCustodianTransform", "CribmasterCustodianValid", "CribmasterPropertyStatusTransform", "CribmasterPropertyStatusValid", "EasBUTransform", "EasHomeTransform", "EasDepartmentTransform", "EasOrganizationValid", "EasBuildingTransform", "EasLocationValid", "EasPropertyStatusTransform", "EasPropertyStatusValid", "MaximoBUTransform", "MaximoHomeTransform", "MaximoDepartmentTransform", "MaximoOrganizationValid", "MaximoBuildingTransform", "MaximoLocationValid", "MaximoPropertyStatusTransform", "MaximoPropertyStatusValid", "SystemRecordConsistency" }; TopazDataTableUtilities.ExportRawData(data, columns, organization, showCustodian: false); }
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)); }