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 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));
}
