private static string DoBuildQueryString(EntityMetadata entityMetadata, InternalQueryRequest queryParameter,
QueryCacheKey.QueryMode queryMode, IWhereBuilder compositeWhereBuilder, QueryCacheKey cacheKey)
{
var buffer = new StringBuilder(InitialStringBuilderCapacity);
var projectionBuilder = new StringBuilder(InitialStringBuilderCapacity);
projectionBuilder.Append(QuerySelectBuilder.BuildSelectAttributesClause(entityMetadata, queryMode, queryParameter.SearchDTO));
projectionBuilder.Append(QueryFromBuilder.Build(entityMetadata, queryParameter.SearchDTO));
buffer.Append(projectionBuilder);
buffer.Append(compositeWhereBuilder.BuildWhereClause(entityMetadata.Name, queryMode, queryParameter.SearchDTO));
var hasUnionWhereClauses = queryParameter.SearchDTO != null && queryParameter.SearchDTO.UnionWhereClauses != null;
var isUnion = entityMetadata.HasUnion() || queryMode == QueryCacheKey.QueryMode.Union || hasUnionWhereClauses;
if (queryMode != QueryCacheKey.QueryMode.Count && queryMode != QueryCacheKey.QueryMode.Detail && !isUnion)
{
buffer.Append(QuerySearchSortBuilder.BuildSearchSort(entityMetadata, queryParameter.SearchDTO));
}
if (hasUnionWhereClauses)
{
foreach (var unionWC in queryParameter.SearchDTO.UnionWhereClauses)
{
buffer.Append(" union all ").Append(projectionBuilder).Append(" where (").Append(unionWC).Append(")");
}
buffer.Append(" order by 1 desc");
}
var queryString = buffer.ToString();
return(queryString);
}
public void TestRelationshipQuery3Levels()
{
var dto = new SearchRequestDto();
dto.AppendSearchEntry("asset_aucisowner_person_.hlagdisplayname", "xxx");
var result = QueryFromBuilder.Build(MetadataProvider.Entity("imac"), dto);
Assert.AreEqual("from SR as imac left join asset as asset_ on (imac.assetnum = asset_.assetnum and imac.siteid = asset_.siteid)left join AssetUserCust as asset_aucisowner_ on (asset_.assetnum = asset_aucisowner_.assetnum and asset_aucisowner_.itdassetrole = 'Owner' and asset_aucisowner_.isuser = 1)left join person as asset_aucisowner_person_ on (asset_aucisowner_.personid = asset_aucisowner_person_.personid)", result);
}
public void TestRelationshipQueryField()
{
var dto = new SearchRequestDto();
dto.AppendSearchEntry("aucisnotowner_.personid", "xxx");
var result = QueryFromBuilder.Build(MetadataProvider.Entity("asset"), dto);
Assert.AreEqual("from asset as asset left join AssetUserCust as aucisnotowner_ on (asset.assetnum = aucisnotowner_.assetnum and (aucisnotowner_.itdassetrole is null or aucisnotowner_.itdassetrole !='Owner') and aucisnotowner_.isuser = 1)", result);
}
public void TestMethod1()
{
var dto = new SearchRequestDto();
dto.AppendProjectionField(new ProjectionField("primaryuser_.personid,", "primaryuser_.personid"));
dto.AppendProjectionField(new ProjectionField("primaryuser_person_.displayname", "primaryuser_person_.displayname"));
var result = QueryFromBuilder.Build(MetadataProvider.Entity("asset"), dto);
Assert.IsTrue(result.Contains("left join person as primaryuser_person_ on (primaryuser_.personid = primaryuser_person_.personid)"));
}
public void TestRelationshipQuery()
{
var dto = new SearchRequestDto();
dto.AppendProjectionField(ProjectionField.Default("description"));
dto.AppendSearchEntry("status", "('120 Active','OPERATING')");
dto.AppendSearchEntry("usercustodianuser_.personid", "xxx");
var result = QueryFromBuilder.Build(MetadataProvider.Entity("asset"), dto);
Assert.AreEqual("from asset as asset left join AssetUserCust as usercustodianuser_ on (asset.assetnum = usercustodianuser_.assetnum and asset.siteid = usercustodianuser_.siteid and usercustodianuser_.isuser = 1)", result);
}
public void ChangeSRUnionSearch()
{
var dto = new SearchRequestDto();
var completeOne = MetadataProvider.Application("srforchange");
var metadata = completeOne.ApplyPolicies(new ApplicationMetadataSchemaKey("changeunionschema"), InMemoryUser.TestInstance(),
ClientPlatform.Web);
ApplicationSchemaDefinition schema;
var sliced = SlicedEntityMetadataBuilder.GetInstance(MetadataProvider.Entity("srforchange"), metadata.Schema);
var result = QueryFromBuilder.Build(sliced, dto);
Assert.AreEqual("from SR as srforchange left join asset as asset_ on (srforchange.assetnum = asset_.assetnum and srforchange.siteid = asset_.siteid)", result);
}
public void TestFrom()
{
var sliced = SlicedEntityMetadataBuilder.GetInstance(MetadataProvider.Entity("asset"), _schema);
Assert.AreEqual(5, sliced.InnerMetadatas.Count);
var from = QueryFromBuilder.Build(sliced);
Debug.Write(from);
Assert.IsTrue(from.Contains("address as location_shipto_"));
Assert.IsTrue(from.Contains("address as location_billto_"));
Assert.IsTrue(from.Contains("address as location_serv_"));
Assert.IsTrue(from.Contains("on (location_.billtoaddresscode"));
Assert.IsTrue(from.Contains("on (location_.shiptoaddresscode"));
Assert.IsTrue(from.Contains("on (location_.serviceaddresscode"));
}
public void TestSelect4()
{
var schemas = MetadataProvider.Application("imac").Schemas();
var schema = schemas[new ApplicationMetadataSchemaKey("detail", "output", "web")];
var sliced = SlicedEntityMetadataBuilder.GetInstance(MetadataProvider.Entity("imac"), schema);
var select = QuerySelectBuilder.BuildSelectAttributesClause(sliced, QueryCacheKey.QueryMode.Detail);
Debug.Write(select);
Assert.IsFalse(select.Contains("case when imac.serialnum"));
Assert.IsTrue(select.Contains("case when asset_.serialnum"));
var from = QueryFromBuilder.Build(sliced, null);
// Debug.Write(from);
Assert.IsTrue(from.Contains("left join person as asset_aucisowner_person_ on (asset_aucisowner_.personid = asset_aucisowner_person_.personid)"));
}
public void IncidentHardwareReport()
{
var dto = new SearchRequestDto();
dto.AppendProjectionField(ProjectionField.Default("asset_assetloccomm_commoditiesownedby_.description"));
dto.AppendProjectionField(ProjectionField.Default("asset_assetloccomm_.commodity"));
var completeOne = MetadataProvider.Application("incident");
var metadata = completeOne.ApplyPolicies(new ApplicationMetadataSchemaKey("hardwarerepair"), InMemoryUser.TestInstance(),
ClientPlatform.Web);
ApplicationSchemaDefinition schema;
var sliced = SlicedEntityMetadataBuilder.GetInstance(MetadataProvider.Entity("incident"), metadata.Schema);
var result = QueryFromBuilder.Build(sliced, dto);
Assert.AreEqual("from incident as incident left join asset as asset_ on (incident.assetnum = asset_.assetnum and incident.siteid = asset_.siteid)left join assetloccomm as asset_assetloccomm_ on (asset_.assetnum = asset_assetloccomm_.assetnum and asset_.siteid = asset_assetloccomm_.siteid)left join commodities as asset_assetloccomm_commoditiesownedby_ on (asset_assetloccomm_.commodity = asset_assetloccomm_commoditiesownedby_.commodity and asset_assetloccomm_.itemsetid = asset_assetloccomm_commoditiesownedby_.itemsetid and (asset_assetloccomm_commoditiesownedby_.description like 'Asset owened by%'))", result);
}
public void TestInverseRelationship()
{
// var dto = new SearchRequestDto();
// var completeOne = MetadataProvider.Application("change");
// var metadata = completeOne.ApplyPolicies(new ApplicationMetadataSchemaKey("list"), InMemoryUser.TestInstance(),
// ClientPlatform.Web);
// ApplicationSchemaDefinition schema;
// var sliced = SlicedEntityMetadataBuilder.GetInstance(MetadataProvider.Entity("change"), metadata.Schema);
// var result = QueryFromBuilder.Build(MetadataProvider.Entity("wochange"), dto);
var dto = new SearchRequestDto();
dto.AppendProjectionField(ProjectionField.Default("sr_.description"));
var result = QueryFromBuilder.Build(MetadataProvider.Entity("wochange"), dto);
Assert.AreEqual("from wochange as wochange left join SR as sr_ on (wochange.origrecordid = sr_.ticketid and wochange.origrecordclass = 'SR' and wochange.woclass = 'CHANGE')", result);
}
public BindedEntityQuery CountRowsFromConstraint(EntityMetadata entityMetadata, softWrench.sW4.Security.Entities.DataConstraint constraint)
{
var buffer = new StringBuilder(InitialStringBuilderCapacity);
buffer.Append(QuerySelectBuilder.BuildSelectAttributesClause(entityMetadata, QueryCacheKey.QueryMode.Count, null));
buffer.Append(QueryFromBuilder.Build(entityMetadata));
var dataConstraintsWhereBuilder = SimpleInjectorGenericFactory.Instance.GetObject <DataConstraintsWhereBuilder>(typeof(DataConstraintsWhereBuilder));
var list = new List <IWhereBuilder> {
new EntityWhereClauseBuilder(),
dataConstraintsWhereBuilder,
new MultiTenantCustomerWhereBuilder()
};
var whereBuilder = new CompositeWhereBuilder(list);
buffer.Append(whereBuilder.BuildWhereClause(entityMetadata.Name, QueryCacheKey.QueryMode.Count));
return(new BindedEntityQuery(buffer.ToString(), whereBuilder.GetParameters()));
}
