using System.Collections.Generic; using System.Text; using System.Linq; using Sockeye.Biz; using Sockeye.Util; namespace Sockeye.PickList { internal static class PickListSqlBuilder { //Maximum number of results to return at any given time //did a little research and may adjust this but it can be fairly girthy in this day and age //and many people might not want or need to autocomplete type if we provide enough leeway. const int MAXIMUM_RESULT_COUNT = 100; //Build the query for a picklist request internal static string Build(IAyaPickList pickList, List templateColumnNames, string autoCompleteQuery, string tagSpecificQuery, bool IncludeInactive, long[] preIds, string variant) { //determine this in advance as it will be used in a loop later bool HasAutoCompleteQuery = !string.IsNullOrWhiteSpace(autoCompleteQuery); bool HasTagSpecificQuery = !string.IsNullOrWhiteSpace(tagSpecificQuery); //Variables to collect the data needed to create the actual clauses later List lSelect = new List(); List lWhere = new List(); List lOrderBy = new List(); string PlIdSelectFragment = string.Empty; string ActiveSelectFragment = string.Empty; string ActiveWhereFragment = string.Empty; string TagSpecificWhereFragment = string.Empty; string PredefinedOnlyWhereFragment = string.Empty; string VariantWhereFragment = string.Empty; bool HasVariantWhereFragment = false; if (!string.IsNullOrWhiteSpace(variant) && pickList is IAyaPickListVariant) { VariantWhereFragment = ((IAyaPickListVariant)pickList).GetVariantCriteria(variant); HasVariantWhereFragment = !string.IsNullOrWhiteSpace(VariantWhereFragment); } //PROCESS ROW ID "VALUE" COLUMN // AyaPickListFieldDefinition rowIdColumn = pickList.ColumnDefinitions.FirstOrDefault(z => z.IsRowId == true); //this should only happen with a development error if (rowIdColumn == null) throw new System.ArgumentNullException($"DEV ERROR in PickListSqlBuilder.cs: picklist for {pickList.DefaultListAType.ToString()} has no rowId column specified in columnDefinitions list"); PlIdSelectFragment = rowIdColumn.SqlIdColumnName + " as plId"; if (preIds.Length > 0) { //select id,name from acustomer where id in(1,3,5,7) //string.Join(",", arr) // PredefinedOnlyWhereFragment = rowIdColumn.SqlIdColumnName + " = " + preId.ToString(); PredefinedOnlyWhereFragment = $"{rowIdColumn.SqlIdColumnName} in ({string.Join(",", preIds)})"; } //PROCESS ACTIVE COLUMN // //NOTE: default is to filter *out* inactive objects because that's the most common need at the Client //but we provide the override for that if necessary as there are often (management usually) cases where user needs to select inactive records //add active column, fake it if necessary AyaPickListFieldDefinition activeColumn = pickList.ColumnDefinitions.FirstOrDefault(z => z.IsActiveColumn == true); if (activeColumn == null) { //no active column which is normal for some types of objects //so make a fake one and return them all as active=true as all lists must return the same format ActiveSelectFragment = "true as plActive"; } else { //we have an active column, set accordingly //regardless of wanting to see inactive, we always want to see the column itself ActiveSelectFragment = activeColumn.SqlValueColumnName + " as plActive"; //this is the normal path unless there is an override //if there is an override to see inactive too then we just don't set the filter on active if (!IncludeInactive) { if (preIds.Length > 0) { //pre-selected need to always appear regardless of active status //ActiveWhereFragment = $"({rowIdColumn.SqlIdColumnName} = {preId}) or ({activeColumn.SqlValueColumnName} = true)"; ActiveWhereFragment = $"({rowIdColumn.SqlIdColumnName} in ({string.Join(",", preIds)})) or ({activeColumn.SqlValueColumnName} = true)"; } else { ActiveWhereFragment = activeColumn.SqlValueColumnName + " = true"; } } } //PROCESS TAG SPECIFIC QUERY // if (HasTagSpecificQuery) { //get the tag column AyaPickListFieldDefinition tagColumn = pickList.ColumnDefinitions.FirstOrDefault(z => z.ColumnDataType == UiFieldDataType.Tags); TagSpecificWhereFragment = $"(array_to_string({tagColumn.GetSqlValueColumnName()},',') like '%{tagSpecificQuery}%')"; } //PROCESS TEMPLATED COLUMNS TO BE RETURNED IN RESULTS // foreach (string ColumnName in templateColumnNames) { AyaPickListFieldDefinition o = pickList.ColumnDefinitions.FirstOrDefault(z => z.FieldKey == ColumnName); #if (DEBUG) if (o == null) { throw new System.ArgumentNullException($"DEV ERROR in PickListSqlBuilder.cs: field {ColumnName} specified in template was NOT found in columnDefinitions list"); } #endif if (o != null) {//Ignore missing fields in production var valueColumnName = o.GetSqlValueColumnName(); string sWhere = string.Empty; //TAGS COLUMN // if (o.ColumnDataType == UiFieldDataType.Tags) { lSelect.Add($"(array_to_string({valueColumnName},','))"); //tags can order by without the arraytostring lOrderBy.Add(valueColumnName); //THIS is the best filter method for a like comparison to each individual tag: //(array_to_string(acustomer.tags,',') like '%zo%') //Note that a tag specific query takes precendence over this which exists //in cases where there are tags in the template and the user has not specified a tag specific query //so this will handle it as a like query against all tags as a composite string of text just like //all the other templated fields if (HasAutoCompleteQuery && !HasTagSpecificQuery) { if (ServerGlobalBizSettings.Cache.FilterCaseSensitive) sWhere = $"(array_to_string({valueColumnName},',') like '%{autoCompleteQuery}%')"; else sWhere = $"(lower(array_to_string({valueColumnName},',')) like lower('%{autoCompleteQuery}%'))"; } } else if (o.ColumnDataType == UiFieldDataType.Text || o.ColumnDataType == UiFieldDataType.EmailAddress || o.ColumnDataType == UiFieldDataType.HTTP) { //TEXT COLUMN // lSelect.Add(valueColumnName); lOrderBy.Add(valueColumnName); if (HasAutoCompleteQuery) if (ServerGlobalBizSettings.Cache.FilterCaseSensitive) sWhere = $"({valueColumnName} like '%{autoCompleteQuery}%')"; else sWhere = $"(lower({valueColumnName}) like lower('%{autoCompleteQuery}%'))"; } else { //NON-TEXT COLUMN // //Note: if any part of a select contatenation query using the || postgres concat is text then all fields are automatically converted to text //so no need to make it text here as the automatic spacing character will force the whole thing to a text concat anyway //ref: https://stackoverflow.com/a/19943343/8939 lSelect.Add(valueColumnName); //order by for now seems to be best as just order by it's value whatever it is lOrderBy.Add(valueColumnName); //Where fragment is different for non text fields: it needs to be cast to text to like query on it if (HasAutoCompleteQuery) if (ServerGlobalBizSettings.Cache.FilterCaseSensitive) sWhere = $"(cast ({valueColumnName} as text) like '%{autoCompleteQuery}%')"; else sWhere = $"(lower(cast ({valueColumnName} as text)) like lower('%{autoCompleteQuery}%'))"; } if (HasAutoCompleteQuery && !string.IsNullOrWhiteSpace(sWhere))//swhere can be empty on a tag in a tag specific query lWhere.Add(sWhere); } } StringBuilder sb = new StringBuilder(); //SELECT sb.Append("select "); //ID COLUMN sb.Append(PlIdSelectFragment); sb.Append(", "); //ACTIVE COLUMN sb.Append(ActiveSelectFragment); sb.Append(", "); //nope, this will return null if any of the values are null, very bad for this use, instead //select name || ' ' || serial || ' ' || array_to_string(tags,',') as display from acustomer //this, on the other hand will work even if all of them are null //concat_ws(' ', acustomer.name, acustomer.serial, auser.name) sb.Append("concat_ws(' ', "); foreach (string s in lSelect) { sb.Append(s); sb.Append(","); } //clear trailing comma sb.Length -= 1; sb.Append(") as plname"); //FROM sb.Append(" "); sb.Append(pickList.SQLFrom); //WHERE //there is a condition where there is no where (inactive=true and no query of any kind) if (preIds.Length > 0 || lWhere.Count > 0 || HasTagSpecificQuery || HasVariantWhereFragment || IncludeInactive == false) { sb.Append(" where "); if (HasVariantWhereFragment) { sb.Append($"({VariantWhereFragment}) and "); } if (HasTagSpecificQuery) { sb.Append(TagSpecificWhereFragment); sb.Append(" and "); } if (!IncludeInactive) { sb.Append(ActiveWhereFragment); sb.Append(" and "); } if (preIds.Length > 0) { sb.Append(PredefinedOnlyWhereFragment); sb.Append(" and "); } if (lWhere.Count > 0) { //Put all the regular query terms in parenthesis to ensure it's all treated as one criteria sb.Append("("); foreach (string s in lWhere) { sb.Append(s); sb.Append(" or "); } //clear trailing or sb.Length -= 4; //enclosing parenthesis sb.Append(")"); } else { //we might have a trailing and to remove //{select acustomer.id as plId, acustomer.active as plActive, concat_ws(' ', acustomer.name) as plname from acustomer where (acustomer.headofficeid = 2) and } if(sb.ToString().EndsWith(" and ")) sb.Length-=5; // // if (!IncludeInactive || HasTagSpecificQuery || preIds.Length > 0) // { // //trailing " and " to remove // sb.Length -= 5; // } } } //ORDER BY sb.Append(" order by "); foreach (string s in lOrderBy) { sb.Append(s); sb.Append(","); } //clear trailing comma sb.Length--; //LIMIT sb.Append($" limit {MAXIMUM_RESULT_COUNT}"); return sb.ToString(); } //"select acustomer.id as plId || ' 'acustomer.active as plActive || ' 'acustomer.name || ' 'acustomer.serial || ' 'auser.name as plname from acustomer left join auser on (acustomer.userid=auser.id) //where acustomer.active = true and ((acustomer.name like '%on%') or (cast (acustomer.serial as text) like '%on%') or (auser.name like '%on%')) order by acustomer.name,acustomer.serial,auser.name limit 100" }//eoc }//ens