private DataTable _getContainerLocations(String MaterialId, TierIIData.TierIIDataRequest Request)
{
#region SQL Query
String SelectText =
@"with containerids as (
select n.nodeid
from jct_nodes_props n
left join (select jnp.nodeid, jnp.field1_numeric as quantity
from jct_nodes_props jnp
inner join nodetype_props ntp on jnp.nodetypepropid = ntp.nodetypepropid
inner join object_class_props ocp on ntp.objectclasspropid = ocp.objectclasspropid
where ocp.objectclasspropid = :quantityid) q
on n.nodeid = q.nodeid
inner join nodetype_props ntp on n.nodetypepropid = ntp.nodetypepropid
inner join object_class_props ocp on ntp.objectclasspropid = ocp.objectclasspropid
where ocp.objectclasspropid = :materialpropid
and q.quantity > 0
and field1_fk in
(select jnp.field1_fk as materials
from jct_nodes_props jnp
inner join nodetype_props ntp on jnp.nodetypepropid = ntp.nodetypepropid
inner join object_class_props ocp on ntp.objectclasspropid = ocp.objectclasspropid
where ocp.objectclasspropid = :mixtureid
and nodeid in
(select jnp.nodeid
from jct_nodes_props jnp
inner join nodetype_props ntp on jnp.nodetypepropid = ntp.nodetypepropid
inner join object_class_props ocp on ntp.objectclasspropid = ocp.objectclasspropid
where ocp.objectclasspropid = :constituentid
and jnp.field1_fk = :materialid)
union
(select to_number(:materialid) from dual)
)
)
select unique locationid, fulllocation from (
(select unique jnp.field1_fk as locationid, jnp.field4 as fulllocation
from jct_nodes_props_audit jnp
inner join nodetype_props ntp on jnp.nodetypepropid = ntp.nodetypepropid
inner join object_class_props ocp on ntp.objectclasspropid = ocp.objectclasspropid
where ocp.objectclasspropid = :locationpropid
and exists (select nodeid from containerids where nodeid = jnp.nodeid)
and jnp.recordcreated < " + _CswNbtResources.getDbNativeDate(DateTime.Parse(Request.EndDate)) + @" + 1)
union
(select unique jnp.field1_fk as locationid, jnp.field4 as fulllocation
from jct_nodes_props jnp
inner join nodetype_props ntp on jnp.nodetypepropid = ntp.nodetypepropid
inner join object_class_props ocp on ntp.objectclasspropid = ocp.objectclasspropid
where ocp.objectclasspropid = :locationpropid
and exists (select nodeid from containerids where nodeid = jnp.nodeid))
) where locationid member of csw_number_table(" + LocationIds + @") and fulllocation is not null";
#endregion SQL Query
CswNbtMetaDataObjectClass MaterialComponentOC = _CswNbtResources.MetaData.getObjectClass(CswEnumNbtObjectClass.MaterialComponentClass);
CswNbtMetaDataObjectClass ContainerOC = _CswNbtResources.MetaData.getObjectClass(CswEnumNbtObjectClass.ContainerClass);
CswNbtMetaDataObjectClassProp MixtureProp = MaterialComponentOC.getObjectClassProp(CswNbtObjClassMaterialComponent.PropertyName.Mixture);
CswNbtMetaDataObjectClassProp ConstituentProp = MaterialComponentOC.getObjectClassProp(CswNbtObjClassMaterialComponent.PropertyName.Constituent);
CswNbtMetaDataObjectClassProp MaterialProp = ContainerOC.getObjectClassProp(CswNbtObjClassContainer.PropertyName.Material);
CswNbtMetaDataObjectClassProp LocationProp = ContainerOC.getObjectClassProp(CswNbtObjClassContainer.PropertyName.Location);
CswNbtMetaDataObjectClassProp QuantityProp = ContainerOC.getObjectClassProp(CswNbtObjClassContainer.PropertyName.Quantity);
CswArbitrarySelect TierIILocationsSelect = _CswNbtResources.makeCswArbitrarySelect("Tier II Container Locations Select", SelectText);
TierIILocationsSelect.addParameter("quantityid", QuantityProp.PropId.ToString());
TierIILocationsSelect.addParameter("materialpropid", MaterialProp.PropId.ToString());
TierIILocationsSelect.addParameter("mixtureid", MixtureProp.PropId.ToString());
TierIILocationsSelect.addParameter("constituentid", ConstituentProp.PropId.ToString());
TierIILocationsSelect.addParameter("materialid", MaterialId);
TierIILocationsSelect.addParameter("locationpropid", LocationProp.PropId.ToString());
DataTable TargetTable = TierIILocationsSelect.getTable();
return(TargetTable);
}
private DataTable _getContainerStorageProps(String MaterialId, TierIIData.TierIIDataRequest Request)
{
#region SQL Query
String SelectText =
@"with containerids as (
select n.nodeid
from jct_nodes_props n
left join (select jnp.nodeid, jnp.field1_numeric as quantity
from jct_nodes_props jnp
inner join nodetype_props ntp on jnp.nodetypepropid = ntp.nodetypepropid
inner join object_class_props ocp on ntp.objectclasspropid = ocp.objectclasspropid
where ocp.objectclasspropid = :quantityid) q
on n.nodeid = q.nodeid
inner join nodetype_props ntp on n.nodetypepropid = ntp.nodetypepropid
inner join object_class_props ocp on ntp.objectclasspropid = ocp.objectclasspropid
where ocp.objectclasspropid = :materialpropid
and q.quantity > 0
and field1_fk in
(select jnp.field1_fk as materials
from jct_nodes_props jnp
inner join nodetype_props ntp on jnp.nodetypepropid = ntp.nodetypepropid
inner join object_class_props ocp on ntp.objectclasspropid = ocp.objectclasspropid
where ocp.objectclasspropid = :mixtureid
and nodeid in
(select jnp.nodeid
from jct_nodes_props jnp
inner join nodetype_props ntp on jnp.nodetypepropid = ntp.nodetypepropid
inner join object_class_props ocp on ntp.objectclasspropid = ocp.objectclasspropid
where ocp.objectclasspropid = :constituentid
and jnp.field1_fk = :materialid)
union
(select to_number(:materialid) from dual)
)
)
select unique pressure, temperature, usetype from (
select props.* from (
select codes.*, dense_rank() over(partition by codes.containerid order by recordcreated desc) rank from (
select unique jnpa.nodeid as ContainerId,
last_value(p.pressure ignore nulls) OVER (ORDER BY jnpa.audittransactionid) pressure,
last_value(t.temperature ignore nulls) OVER (ORDER BY jnpa.audittransactionid) temperature,
last_value(u.usetype ignore nulls) OVER (ORDER BY jnpa.audittransactionid) usetype,
jnpa.audittransactionid,
jnpa.recordcreated
from jct_nodes_props_audit jnpa
left join (select jnp.nodeid, jnp.field1 as pressure, jnp.audittransactionid
from jct_nodes_props_audit jnp
inner join nodetype_props ntp on jnp.nodetypepropid = ntp.nodetypepropid
inner join object_class_props ocp on ntp.objectclasspropid = ocp.objectclasspropid
where ocp.objectclasspropid = :pressureid) p
on jnpa.nodeid = p.nodeid and jnpa.audittransactionid = p.audittransactionid
left join (select jnp.nodeid, jnp.field1 as temperature, jnp.audittransactionid
from jct_nodes_props_audit jnp
inner join nodetype_props ntp on jnp.nodetypepropid = ntp.nodetypepropid
inner join object_class_props ocp on ntp.objectclasspropid = ocp.objectclasspropid
where ocp.objectclasspropid = :temperatureid) t
on jnpa.nodeid = t.nodeid and jnpa.audittransactionid = t.audittransactionid
left join (select jnp.nodeid, jnp.field1 as usetype, jnp.audittransactionid
from jct_nodes_props_audit jnp
inner join nodetype_props ntp on jnp.nodetypepropid = ntp.nodetypepropid
inner join object_class_props ocp on ntp.objectclasspropid = ocp.objectclasspropid
where ocp.objectclasspropid = :usetypeid) u
on jnpa.nodeid = u.nodeid and jnpa.audittransactionid = u.audittransactionid
where exists (select nodeid from containerids where nodeid = jnpa.nodeid)
order by containerid, audittransactionid
) codes
) props
where props.rank=1
and props.recordcreated < " + _CswNbtResources.getDbNativeDate(DateTime.Parse(Request.EndDate)) + @" + 1
) union (
select unique
last_value(p.pressure ignore nulls) OVER (ORDER BY jnpa.jctnodepropid) pressure,
last_value(t.temperature ignore nulls) OVER (ORDER BY jnpa.jctnodepropid) temperature,
last_value(u.usetype ignore nulls) OVER (ORDER BY jnpa.jctnodepropid) usetype
from jct_nodes_props jnpa
left join (select jnp.nodeid, jnp.field1 as pressure
from jct_nodes_props jnp
inner join nodetype_props ntp on jnp.nodetypepropid = ntp.nodetypepropid
inner join object_class_props ocp on ntp.objectclasspropid = ocp.objectclasspropid
where ocp.objectclasspropid = :pressureid) p
on jnpa.nodeid = p.nodeid
left join (select jnp.nodeid, jnp.field1 as temperature
from jct_nodes_props jnp
inner join nodetype_props ntp on jnp.nodetypepropid = ntp.nodetypepropid
inner join object_class_props ocp on ntp.objectclasspropid = ocp.objectclasspropid
where ocp.objectclasspropid = :temperatureid) t
on jnpa.nodeid = t.nodeid
left join (select jnp.nodeid, jnp.field1 as usetype
from jct_nodes_props jnp
inner join nodetype_props ntp on jnp.nodetypepropid = ntp.nodetypepropid
inner join object_class_props ocp on ntp.objectclasspropid = ocp.objectclasspropid
where ocp.objectclasspropid = :usetypeid) u
on jnpa.nodeid = u.nodeid
where exists (select nodeid from containerids where nodeid = jnpa.nodeid)
)";
#endregion SQL Query
CswNbtMetaDataObjectClass MaterialComponentOC = _CswNbtResources.MetaData.getObjectClass(CswEnumNbtObjectClass.MaterialComponentClass);
CswNbtMetaDataObjectClass ContainerOC = _CswNbtResources.MetaData.getObjectClass(CswEnumNbtObjectClass.ContainerClass);
CswNbtMetaDataObjectClassProp MixtureProp = MaterialComponentOC.getObjectClassProp(CswNbtObjClassMaterialComponent.PropertyName.Mixture);
CswNbtMetaDataObjectClassProp ConstituentProp = MaterialComponentOC.getObjectClassProp(CswNbtObjClassMaterialComponent.PropertyName.Constituent);
CswNbtMetaDataObjectClassProp MaterialProp = ContainerOC.getObjectClassProp(CswNbtObjClassContainer.PropertyName.Material);
CswNbtMetaDataObjectClassProp PressureProp = ContainerOC.getObjectClassProp(CswNbtObjClassContainer.PropertyName.StoragePressure);
CswNbtMetaDataObjectClassProp TemperatureProp = ContainerOC.getObjectClassProp(CswNbtObjClassContainer.PropertyName.StorageTemperature);
CswNbtMetaDataObjectClassProp UseTypeProp = ContainerOC.getObjectClassProp(CswNbtObjClassContainer.PropertyName.UseType);
CswNbtMetaDataObjectClassProp QuantityProp = ContainerOC.getObjectClassProp(CswNbtObjClassContainer.PropertyName.Quantity);
CswArbitrarySelect TierIIContainerPropsSelect = _CswNbtResources.makeCswArbitrarySelect("Tier II Container Props Select", SelectText);
TierIIContainerPropsSelect.addParameter("quantityid", QuantityProp.PropId.ToString());
TierIIContainerPropsSelect.addParameter("materialpropid", MaterialProp.PropId.ToString());
TierIIContainerPropsSelect.addParameter("mixtureid", MixtureProp.PropId.ToString());
TierIIContainerPropsSelect.addParameter("constituentid", ConstituentProp.PropId.ToString());
TierIIContainerPropsSelect.addParameter("materialid", MaterialId);
TierIIContainerPropsSelect.addParameter("pressureid", PressureProp.PropId.ToString());
TierIIContainerPropsSelect.addParameter("temperatureid", TemperatureProp.PropId.ToString());
TierIIContainerPropsSelect.addParameter("usetypeid", UseTypeProp.PropId.ToString());
DataTable TargetTable = TierIIContainerPropsSelect.getTable();
return(TargetTable);
}
public TierIIData getTierIIData_old(TierIIData.TierIIDataRequest Request)
{
BaseUnit = _setBaseUnit("kg", "Unit_Weight");
CswNbtObjClassUnitOfMeasure PoundsUnit = _setBaseUnit("lb", "Unit_Weight");
CswNbtUnitConversion Conversion = (BaseUnit != null && PoundsUnit != null) ?
new CswNbtUnitConversion(_CswNbtResources, BaseUnit.NodeId, PoundsUnit.NodeId) :
new CswNbtUnitConversion();
LocationIds = _setLocationIds(Request.LocationId);
DataTable MaterialsTable = _getTierIIMaterials(Request);
foreach (DataRow MaterialRow in MaterialsTable.Rows)
{
CswPrimaryKey BaseUnitId = CswConvert.ToPrimaryKey("nodes_" + MaterialRow["unitid"]);
if (null != BaseUnit && BaseUnit.NodeId != BaseUnitId)
{
//Theoretically, this should never happen
//(unless we decide, one day, to change the unit in which we're storing TierII quantity data)
BaseUnit = _CswNbtResources.Nodes.GetNode(BaseUnitId);
Conversion.setOldUnitProps(BaseUnit);
}
Double MaxQty = Math.Round(Conversion.convertUnit(CswConvert.ToDouble(MaterialRow["maxqty"])), 3);
Double AverageQty = Math.Round(Conversion.convertUnit(CswConvert.ToDouble(MaterialRow["avgqty"])), 3);
TierIIData.TierIIMaterial Material = new TierIIData.TierIIMaterial
{
MaterialId = MaterialRow["materialid"].ToString(),
TradeName = MaterialRow["tradename"].ToString(),
CASNo = MaterialRow["casno"].ToString(),
MaterialType = MaterialRow["materialtype"].ToString(),
PhysicalState = MaterialRow["physicalstate"].ToString(),
EHS = MaterialRow["specialflags"].ToString().Contains("EHS"),
TradeSecret = MaterialRow["specialflags"].ToString().Contains("Trade Secret"),
MaxQty = MaxQty,
MaxQtyRangeCode = _getRangeCode(MaxQty),
AverageQty = AverageQty,
AverageQtyRangeCode = _getRangeCode(AverageQty),
DaysOnSite = CswConvert.ToInt32(MaterialRow["daysonsite"]),
Unit = PoundsUnit != null ? PoundsUnit.Name.Text : "lb"
};
CswCommaDelimitedString Hazards = new CswCommaDelimitedString();
Hazards.FromString(MaterialRow["hazardcategories"].ToString());
foreach (String Hazard in Hazards)
{
Material.HazardCategories.Add(Hazard);
}
DataTable ContainerStorageCodesTable = _getContainerStorageProps(Material.MaterialId, Request);
foreach (DataRow ContainerPropsRow in ContainerStorageCodesTable.Rows)
{
TierIIData.StorageCodes StorageCodes = new TierIIData.StorageCodes
{
Pressure = ContainerPropsRow["pressure"].ToString(),
Temperature = ContainerPropsRow["temperature"].ToString(),
UseType = ContainerPropsRow["usetype"].ToString()
};
Material.Storage.Add(StorageCodes);
}
DataTable ContainerLocationsTable = _getContainerLocations(Material.MaterialId, Request);
foreach (DataRow ContainerLocsRow in ContainerLocationsTable.Rows)
{
TierIIData.StorageLocations Location = new TierIIData.StorageLocations
{
LocationId = ContainerLocsRow["locationid"].ToString(),
Location = ContainerLocsRow["fulllocation"].ToString()
};
Material.Locations.Add(Location);
}
Data.Materials.Add(Material);
}
return(Data);
}
private DataTable _getTierIIMaterials(TierIIData.TierIIDataRequest Request)
{
#region SQL Query
String SqlText = @"
select t.materialid, t.casno, max(t.totalquantity) as maxqty, round(avg(t.totalquantity), 6) as avgqty, t.unitid, count(*) as daysonsite,
p.tradename, p.materialtype, p.physicalstate, p.specialflags, p.hazardcategories, p.istierII
from tier2 t
left join (select
n.nodeid as MaterialId,
(select jnp.field1 from jct_nodes_props jnp
inner join nodetype_props ntp on jnp.nodetypepropid = ntp.nodetypepropid
inner join object_class_props ocp on ntp.objectclasspropid = ocp.objectclasspropid
where n.nodeid = jnp.nodeid and ocp.objectclasspropid = :tradenameid) as Tradename,
(select jnp.field1 from jct_nodes_props jnp
inner join nodetype_props ntp on jnp.nodetypepropid = ntp.nodetypepropid
inner join object_class_props ocp on ntp.objectclasspropid = ocp.objectclasspropid
where n.nodeid = jnp.nodeid and ocp.objectclasspropid = :materialtypeid) as MaterialType,
(select jnp.field1 from jct_nodes_props jnp
inner join nodetype_props ntp on jnp.nodetypepropid = ntp.nodetypepropid
inner join object_class_props ocp on ntp.objectclasspropid = ocp.objectclasspropid
where n.nodeid = jnp.nodeid and ocp.objectclasspropid = :physicalstateid) as PhysicalState,
(select dbms_lob.substr(jnp.gestalt) from jct_nodes_props jnp
inner join nodetype_props ntp on jnp.nodetypepropid = ntp.nodetypepropid
inner join object_class_props ocp on ntp.objectclasspropid = ocp.objectclasspropid
where n.nodeid = jnp.nodeid and ocp.objectclasspropid = :specialflagsid) as SpecialFlags,
(select dbms_lob.substr(jnp.gestalt) from jct_nodes_props jnp
inner join nodetype_props ntp on jnp.nodetypepropid = ntp.nodetypepropid
inner join object_class_props ocp on ntp.objectclasspropid = ocp.objectclasspropid
where n.nodeid = jnp.nodeid and ocp.objectclasspropid = :hazardcategoriesid) as HazardCategories,
(select jnp.field1 from jct_nodes_props jnp
inner join nodetype_props ntp on jnp.nodetypepropid = ntp.nodetypepropid
inner join object_class_props ocp on ntp.objectclasspropid = ocp.objectclasspropid
where n.nodeid = jnp.nodeid and ocp.objectclasspropid = :istier2id) as IsTierII
from nodes n) p on p.materialid = t.materialid
where locationid = :locationid
and istierii = 1
and casno is not null
and dateadded >= " + _CswNbtResources.getDbNativeDate(DateTime.Parse(Request.StartDate)) + @"
and dateadded < " + _CswNbtResources.getDbNativeDate(DateTime.Parse(Request.EndDate)) + @" + 1
group by t.materialid, t.casno, t.unitid,
p.tradename, p.materialtype, p.physicalstate, p.specialflags, p.hazardcategories, p.istierII";
#endregion SQL Query
CswNbtMetaDataObjectClass ChemicalOC = _CswNbtResources.MetaData.getObjectClass(CswEnumNbtObjectClass.ChemicalClass);
CswNbtMetaDataObjectClassProp TradeNameProp = ChemicalOC.getObjectClassProp(CswNbtObjClassChemical.PropertyName.TradeName);
CswNbtMetaDataObjectClassProp MaterialTypeProp = ChemicalOC.getObjectClassProp(CswNbtObjClassChemical.PropertyName.MaterialType);
CswNbtMetaDataObjectClassProp PhysicalStateProp = ChemicalOC.getObjectClassProp(CswNbtObjClassChemical.PropertyName.PhysicalState);
CswNbtMetaDataObjectClassProp SpecialFlagsProp = ChemicalOC.getObjectClassProp(CswNbtObjClassChemical.PropertyName.SpecialFlags);
CswNbtMetaDataObjectClassProp HazardCategoriesProp = ChemicalOC.getObjectClassProp(CswNbtObjClassChemical.PropertyName.HazardCategories);
CswNbtMetaDataObjectClassProp IsTierIIProp = ChemicalOC.getObjectClassProp(CswNbtObjClassChemical.PropertyName.IsTierII);
CswArbitrarySelect TierIIMaterialsSelect = _CswNbtResources.makeCswArbitrarySelect("Tier II Material Select", SqlText);
TierIIMaterialsSelect.addParameter("tradenameid", TradeNameProp.PropId.ToString());
TierIIMaterialsSelect.addParameter("materialtypeid", MaterialTypeProp.PropId.ToString());
TierIIMaterialsSelect.addParameter("physicalstateid", PhysicalStateProp.PropId.ToString());
TierIIMaterialsSelect.addParameter("specialflagsid", SpecialFlagsProp.PropId.ToString());
TierIIMaterialsSelect.addParameter("hazardcategoriesid", HazardCategoriesProp.PropId.ToString());
TierIIMaterialsSelect.addParameter("istier2id", IsTierIIProp.PropId.ToString());
TierIIMaterialsSelect.addParameter("locationid", CswConvert.ToPrimaryKey(Request.LocationId).PrimaryKey.ToString());
DataTable TargetTable = TierIIMaterialsSelect.getTable();
return(TargetTable);
}
public TierIIData getTierIIData(TierIIData.TierIIDataRequest Request)
{
CswArbitrarySelect TierIISelect = _CswNbtResources.makeCswArbitrarySelect("Tier II Material Select",
"select * from table(TIER_II_DATA_MANAGER.GET_TIER_II_DATA(" + CswConvert.ToPrimaryKey(Request.LocationId).PrimaryKey +
", " + _CswNbtResources.getDbNativeDate(DateTime.Parse(Request.StartDate)) +
", " + _CswNbtResources.getDbNativeDate(DateTime.Parse(Request.EndDate)) + ")) where casno is not null");
DataTable TierIITable = TierIISelect.getTable();
foreach (DataRow MaterialRow in TierIITable.Rows)
{
TierIIData.TierIIMaterial Material = new TierIIData.TierIIMaterial
{
MaterialId = MaterialRow["materialid"].ToString(),
TradeName = MaterialRow["tradename"].ToString(),
CASNo = MaterialRow["casno"].ToString(),
MaterialType = MaterialRow["materialtype"].ToString(),
PhysicalState = MaterialRow["physicalstate"].ToString(),
EHS = CswConvert.ToBoolean(MaterialRow["ehs"].ToString()),
TradeSecret = CswConvert.ToBoolean(MaterialRow["tradesecret"].ToString()),
MaxQty = Math.Round(CswConvert.ToDouble(MaterialRow["maxqty"]), 3),
MaxQtyRangeCode = MaterialRow["maxqtyrangecode"].ToString(),
AverageQty = Math.Round(CswConvert.ToDouble(MaterialRow["avgqty"]), 3),
AverageQtyRangeCode = MaterialRow["avgqtyrangecode"].ToString(),
DaysOnSite = CswConvert.ToInt32(MaterialRow["daysonsite"]),
Unit = "lb"
};
CswCommaDelimitedString Hazards = new CswCommaDelimitedString();
Hazards.FromString(MaterialRow["hazardcategories"].ToString());
foreach (String Hazard in Hazards)
{
Material.HazardCategories.Add(Hazard);
}
int ContainerPropRows;
CswCommaDelimitedString Pressures = new CswCommaDelimitedString();
Pressures.FromString(MaterialRow["pressure"].ToString());
ContainerPropRows = Pressures.Count;
CswCommaDelimitedString Temperatures = new CswCommaDelimitedString();
Temperatures.FromString(MaterialRow["temperature"].ToString());
ContainerPropRows = Temperatures.Count > ContainerPropRows ? Temperatures.Count : ContainerPropRows;
CswCommaDelimitedString UseTypes = new CswCommaDelimitedString();
UseTypes.FromString(MaterialRow["usetype"].ToString());
ContainerPropRows = UseTypes.Count > ContainerPropRows ? UseTypes.Count : ContainerPropRows;
for (int i = 0; i < ContainerPropRows; i++)
{
int p = Pressures.Count - 1 < i ? Pressures.Count : i;
int t = Pressures.Count - 1 < i ? Pressures.Count : i;
int u = Pressures.Count - 1 < i ? Pressures.Count : i;
TierIIData.StorageCodes StorageCodes = new TierIIData.StorageCodes
{
Pressure = Pressures[p],
Temperature = Temperatures[t],
UseType = UseTypes[u]
};
Material.Storage.Add(StorageCodes);
}
CswCommaDelimitedString Locations = new CswCommaDelimitedString();
Locations.FromString(MaterialRow["storagelocations"].ToString());
foreach (String Location in Locations)
{
Material.Locations.Add(new TierIIData.StorageLocations
{
Location = Location
});
}
Data.Materials.Add(Material);
}
return(Data);
}
