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