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