public static dynamic[] GetSystemsFromDataRow(DataRow row, dynamic bestKnown)
{
List <dynamic> systems = new List <dynamic>();
systems.Add(bestKnown);
if (TopazDatabaseUtilities.ValueOrDefault <int>("ContainsCmis", row) == 1)
{
systems.Add(GenerateSystemData(row, "CMIS", row["AssetID"], bestKnown, statusNa: true));
}
if (TopazDatabaseUtilities.ValueOrDefault <int>("ContainsSimon", row) == 1)
{
systems.Add(GenerateSystemData(row, "SIMON", row["EQUIP_ID"], bestKnown));
}
if (TopazDatabaseUtilities.ValueOrDefault <int>("ContainsSimonEfbi", row) == 1)
{
systems.Add(GenerateSystemData(row, "EAS", row["AssetID"], bestKnown, custodianNa: true, showCustodian: false));
}
if (TopazDatabaseUtilities.ValueOrDefault <int>("ContainsAssetSmart", row) == 1)
{
systems.Add(GenerateSystemData(row, "AssetSmart", row["AssetID"], bestKnown));
}
if (TopazDatabaseUtilities.ValueOrDefault <int>("ContainsMaximo", row) == 1)
{
systems.Add(GenerateSystemData(row, "Maximo", row["AssetID"], bestKnown, showCustodian: false));
}
if (TopazDatabaseUtilities.ValueOrDefault <int>("ContainsCribmaster", row) == 1)
{
systems.Add(GenerateSystemData(row, "Cribmaster", row["AssetID"], bestKnown, showCustodian: true));
}
return(systems.ToArray());
}
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));
}