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 SelectionData(HierarchicalSelection selection, TopazRequest request)
{
string assetID = TopazDatabaseUtilities.Coalesce(request["AssetID"], "");
string serialNo = TopazDatabaseUtilities.Coalesce(request["SerialNo"], "");
string modelNo = TopazDatabaseUtilities.Coalesce(request["ModelNo"], "");
string modelDesc = TopazDatabaseUtilities.Coalesce(request["ModelDesc"], "");
string manufacturer = TopazDatabaseUtilities.Coalesce(request["Manufacturer"], "");
bool manufacturerExact, modelNoExact, modelDescExact, assetIdExact;
Boolean.TryParse(TopazDatabaseUtilities.Coalesce(request["ManufacturerExact"], "false"), out manufacturerExact);
Boolean.TryParse(TopazDatabaseUtilities.Coalesce(request["ModelNoExact"], "false"), out modelNoExact);
Boolean.TryParse(TopazDatabaseUtilities.Coalesce(request["ModelDescExact"], "false"), out modelDescExact);
Boolean.TryParse(TopazDatabaseUtilities.Coalesce(request["AssetIDExact"], "true"), out assetIdExact);
double minWeight, maxWeight;
minWeight = double.TryParse(TopazDatabaseUtilities.Coalesce(request["MinWeight"], ""), out minWeight) ? minWeight : -1;
maxWeight = double.TryParse(TopazDatabaseUtilities.Coalesce(request["MaxWeight"], ""), out maxWeight) ? maxWeight : -1;
string assetIdWildcard = Topaz.Domain.Concrete.Dashboards.Assets.Inventory.WildcardStringIfChecked(assetIdExact, assetID.Length > 0);
string modelNoWildcard = Topaz.Domain.Concrete.Dashboards.Assets.Inventory.WildcardStringIfChecked(modelNoExact, modelNo.Length > 0);
string modelDescWildcard = Topaz.Domain.Concrete.Dashboards.Assets.Inventory.WildcardStringIfChecked(modelDescExact, modelDesc.Length > 0);
string manufacturerWildcard = Topaz.Domain.Concrete.Dashboards.Assets.Inventory.WildcardStringIfChecked(manufacturerExact, manufacturer.Length > 0);
string assetIDArgument = assetIdWildcard + TopazUtility.SanitizeAssetId(assetID.ToUpper()) + assetIdWildcard;
string serialNoArgument = TopazUtility.SanitizeAssetId(serialNo.ToUpper());
string modelNoArgument = modelNoWildcard + TopazUtility.SanitizeModelNo(modelNo.ToUpper()) + modelNoWildcard;
string modelDescArgument = modelDescWildcard + modelDesc + modelDescWildcard;
string manufacturerArgument = manufacturerWildcard + manufacturer.ToUpper() + manufacturerWildcard;
string weightWhereClause = minWeight >= 0 && maxWeight >= minWeight ? " and @MinWeight <= [Assets].Weight and @MaxWeight >= [Assets].Weight " : "";
string altWhere = assetID.Length > 0 ? string.Format(" and {0} ", TopazUtility.GenerateWildcardQueryConstraint("AssetId", true, assetIdExact, "[Assets]", prefix: "")) : "";
//employeeJoin = employee.WhereClause.Length > 0 ? " left join Employees on Assets.Custodian=Employees.BemsId " : " ";
string joins = string.Format(@" 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 ");
string whereClause = string.Format(" {0} {1} {2} {3} {4} {5}",
altWhere, TopazUtility.GenerateWildcardQueryConstraint("SerialNo", serialNo.Length > 0),
TopazUtility.GenerateWildcardQueryConstraint("Model", modelNo.Length > 0, modelNoExact), TopazUtility.GenerateWildcardQueryConstraint("Noun", modelDesc.Length > 0, modelDescExact), TopazUtility.GenerateWildcardQueryConstraint("Manufacturer", manufacturer.Length > 0, manufacturerExact),
weightWhereClause);
DataTable altAssetId = TopazDatabaseUtilities.DataTableFromQuery("SELECT AssetId FROM AssetAlternateIds WHERE [AssetAlternateIds].AltAssetId = @AltAssetId", "Topaz", new string[] { "@AltAssetId" }, new object[] { assetID });
if (altAssetId.Rows.Count > 0)
{
assetID = altAssetId.Rows[0].Field <string>("AssetId");
}
string[] parameters = new string[] { "@AssetId", "@AltAssetId", "@SerialNo", "@Model", "@Noun", "@Manufacturer", "@MinWeight", "@MaxWeight" };
string[] values = new string[] { assetIDArgument, assetID, serialNoArgument, modelNoArgument, modelDescArgument, manufacturerArgument, minWeight >= 0 ? minWeight.ToString() : "", maxWeight >= minWeight?maxWeight.ToString() : "" };
parameters = TopazDatabaseUtilities.RemoveWhere <string, string>(x => x.Length == 0, values, parameters);
values = TopazDatabaseUtilities.RemoveWhere <string>(x => x.Length == 0, values);
string sumQuery = string.Format(@"SELECT
COALESCE(SUM(CASE WHEN (SystemRecordConsistency < {0}) THEN 1 ELSE 0 END), 0) as [<{2}%],
COALESCE(SUM(CASE WHEN (SystemRecordConsistency >= {0} AND SystemRecordConsistency <= {1}) THEN 1 ELSE 0 END), 0) as [{2}-{3}%],
COALESCE(SUM(CASE WHEN (SystemRecordConsistency > {1}) THEN 1 ELSE 0 END), 0) as [>{3}%] FROM AssetRecordDetail
INNER JOIN Assets on Assets.AssetId=AssetRecordDetail.assetid", badAssetConsistencyThreshold, goodAssetConsistencyThreshold, (int)(badAssetConsistencyThreshold * 100), (int)(goodAssetConsistencyThreshold * 100));
var subData = selection.GetAllSubData(sumQuery, alias: "Assets", dataWhereClause: "1=1 " + whereClause, parameters: parameters, values: values);
string jsonResult = "[" + string.Join(",", subData.Select((x, i) => string.Format("{{label:{0},bars:({1})[0]}}\n", selection.ChildLabels[i].JsonSerialize(), x.ToJson()))) + "]";
string urls = selection.GetChartUrls();
return(new Dictionary <string, string> {
{ "chart", jsonResult },
{ "urls", urls }
}.ToJson(false, false, 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));
}
