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 TestProjectionFieldWithQuery() { var dto = new SearchRequestDto(); dto.AppendProjectionField(new ProjectionField("location", "DISTINCT SUBSTR(REPLACE(location.Location,'test',''),1,LOCATE('/',REPLACE(location.Location,'test',''))-1)")); var result = QuerySelectBuilder.BuildSelectAttributesClause(MetadataProvider.Entity("location"), QueryCacheKey.QueryMode.Detail, dto); Assert.AreEqual(("select DISTINCT SUBSTR(REPLACE(location.Location,'test',''),1,LOCATE('/',REPLACE(location.Location,'test',''))-1) as location "), result); }
public void TestReplaceInner() { var dto = new SearchRequestDto(); dto.AppendProjectionField(ProjectionField.Default("primaryuser_.person_.hlagdisplayname")); var result = QuerySelectBuilder.BuildSelectAttributesClause(MetadataProvider.Entity("asset"), QueryCacheKey.QueryMode.Detail, dto); Assert.AreEqual(("select CASE WHEN LOCATE('@',primaryuser_person_.PERSONID) > 0 THEN '(' || SUBSTR(primaryuser_person_.PERSONID,1,LOCATE('@',primaryuser_person_.PERSONID)-1) || ') ' || COALESCE(primaryuser_person_.DISPLAYNAME,'-- Name Not Set --') ELSE '(' || primaryuser_person_.PERSONID || ') ' || COALESCE(primaryuser_person_.DISPLAYNAME,'-- Name Not Set --') END as primaryuser_person_hlagdisplayname "), result); }
public void TestReplace() { var dto = new SearchRequestDto(); dto.AppendProjectionField(new ProjectionField("hlagdescription", "hlagdescription")); var result = QuerySelectBuilder.BuildSelectAttributesClause(MetadataProvider.Entity("asset"), QueryCacheKey.QueryMode.Detail, dto); Assert.IsTrue(result.Contains("CASE WHEN LOCATE('//',asset.Description) > 0 THEN LTRIM(RTRIM(SUBSTR(asset.Description, LOCATE('//', asset.Description)+3))) ELSE LTRIM(RTRIM(asset.Description)) END as hlagdescription")); }
public void TestMethod2() { var dto = new SearchRequestDto(); dto.AppendProjectionField(new ProjectionField("value", "assetnum")); var result = QuerySelectBuilder.BuildSelectAttributesClause(MetadataProvider.Entity("asset"), QueryCacheKey.QueryMode.Detail, dto); Assert.IsTrue(result.Contains("asset.assetnum as value")); }
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 = QuerySelectBuilder.BuildSelectAttributesClause(MetadataProvider.Entity("asset"), QueryCacheKey.QueryMode.Detail, dto); Assert.IsTrue(result.Contains("primaryuser_.personid as primaryuser_personid, primaryuser_person_.displayname as primaryuser_person_displayname")); }
public void TestSelect2() { var sliced = SlicedEntityMetadataBuilder.GetInstance(MetadataProvider.Entity("asset"), _schema); var select = QuerySelectBuilder.BuildSelectAttributesClause(sliced, QueryCacheKey.QueryMode.List); Debug.Write(select); Assert.IsTrue(select.Contains("location_shipto_.address1 as \"location_shipto_.address1\"")); Assert.IsTrue(select.Contains("location_billto_.address1 as \"location_billto_.address1\"")); Assert.IsTrue(select.Contains("location_serv_.address1 as \"location_serv_.address1\"")); }
public void TestAssetListReportBug() { var schemas = MetadataProvider.Application("asset").Schemas(); var schema = schemas[new ApplicationMetadataSchemaKey("assetlistreport", null, "web")]; var sliced = SlicedEntityMetadataBuilder.GetInstance(MetadataProvider.Entity("asset"), schema); var select = QuerySelectBuilder.BuildSelectAttributesClause(sliced, QueryCacheKey.QueryMode.List); Debug.Write(select); Assert.IsTrue(select.Contains("CASE WHEN LOCATE('@',aucisowner_.PERSONID) > 0 THEN SUBSTR(aucisowner_.PERSONID,1,LOCATE('@',aucisowner_.PERSONID)-1) ELSE aucisowner_.PERSONID END as \"aucisowner_.hlagdisplayname\"")); }
public void TestSelect3() { var schemas = MetadataProvider.Application("change").Schemas(); var schema = schemas[new ApplicationMetadataSchemaKey("detail", null, "web")]; var sliced = SlicedEntityMetadataBuilder.GetInstance(MetadataProvider.Entity("wochange"), schema); var select = QuerySelectBuilder.BuildSelectAttributesClause(sliced, QueryCacheKey.QueryMode.Detail); Debug.Write(select); Assert.IsFalse(select.Contains("sr_asset_.installdate")); Assert.IsTrue(select.Contains("sr_asset_.serialnum")); }
public void TestSelectNullAttributes() { var schemas = MetadataProvider.Application("srforchange").Schemas(); var schema = schemas[new ApplicationMetadataSchemaKey("changeunionschema", null, "web")]; var sliced = SlicedEntityMetadataBuilder.GetInstance(MetadataProvider.Entity("srforchange"), schema, 300, true); Assert.AreEqual(15, sliced.Attributes(EntityMetadata.AttributesMode.NoCollections).Count()); var select = QuerySelectBuilder.BuildSelectAttributesClause(sliced, QueryCacheKey.QueryMode.List); Debug.Write(select); Assert.IsTrue(select.Contains("null")); }
public void TestReplace_Nulls() { var dto = new SearchRequestDto(); dto.AppendProjectionField(ProjectionField.Default("#null1")); dto.AppendProjectionField(ProjectionField.Default("#null2")); //unmappped dto.AppendProjectionField(ProjectionField.Default("#attr")); dto.AppendProjectionField(ProjectionField.Default("description")); var result = QuerySelectBuilder.BuildSelectAttributesClause(MetadataProvider.Entity("sr"), QueryCacheKey.QueryMode.List, dto); Assert.AreEqual(("select null, null, SR.description as description "), result); }
public void ChangeSRUnionSearch() { var dto = new SearchRequestDto(); dto.BuildUnionDTO(dto, MetadataProvider.FindSchemaDefinition("change.list")); var completeOne = MetadataProvider.Application("change"); var metadata = completeOne.ApplyPolicies(new ApplicationMetadataSchemaKey("list"), InMemoryUser.TestInstance(), ClientPlatform.Web); ApplicationSchemaDefinition schema; var sliced = SlicedEntityMetadataBuilder.GetInstance(MetadataProvider.Entity("wochange"), metadata.Schema, 300); var result = QuerySelectBuilder.BuildSelectAttributesClause(sliced.UnionSchema, QueryCacheKey.QueryMode.Union, dto.unionDTO); Assert.AreEqual("select null, null, null, null, '-666' as zeroedattr, srforchange.description as hlagchangesummary, srforchange.ticketid as ticketid, asset_.description as asset_description, null, null, CASE WHEN srforchange.PLUSPCUSTOMER IS NOT NULL AND srforchange.PLUSPCUSTOMER = 'HLC-00' then 'HLAG' WHEN srforchange.PLUSPCUSTOMER IS NOT NULL AND LENGTH(srforchange.PLUSPCUSTOMER) >= 3 THEN SUBSTR(srforchange.PLUSPCUSTOMER, LENGTH(srforchange.PLUSPCUSTOMER) - 2, 3) ELSE '' END as hlagpluspcustomer, CASE WHEN LOCATE('@',srforchange.REPORTEDBY) > 0 THEN SUBSTR(srforchange.REPORTEDBY,1,LOCATE('@',srforchange.REPORTEDBY)-1) ELSE srforchange.REPORTEDBY END as hlagreportedby, srforchange.status as status ", result); }
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 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())); }