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