public override void VisitWhereClause(WhereClause whereClause, QueryModel queryModel, int index)
{
var where = new WhereClauseExpressionTreeVisitor(queryModel.MainFromClause.ItemType, _args.ColumnMappings);
where.Visit(whereClause.Predicate);
SqlStatement.Where = where.WhereClause;
SqlStatement.Parameters = where.Params;
SqlStatement.ColumnNamesUsed.AddRange(where.ColumnNamesUsed);
base.VisitWhereClause(whereClause, queryModel, index);
}
public WhereClauseHandler(WhereClause whereClause)
{
_whereClause = whereClause;
_expressionFormatters = new Dictionary<SimpleExpressionType, Func<SimpleExpression, Func<IDictionary<string,object>, bool>>>
{
{SimpleExpressionType.And, LogicalExpressionToWhereClause},
{SimpleExpressionType.Or, LogicalExpressionToWhereClause},
{SimpleExpressionType.Equal, EqualExpressionToWhereClause},
{SimpleExpressionType.NotEqual, NotEqualExpressionToWhereClause},
{SimpleExpressionType.Function, FunctionExpressionToWhereClause},
{SimpleExpressionType.GreaterThan, GreaterThanToWhereClause},
{SimpleExpressionType.LessThan, LessThanToWhereClause},
{SimpleExpressionType.GreaterThanOrEqual, GreaterThanOrEqualToWhereClause},
{SimpleExpressionType.LessThanOrEqual, LessThanOrEqualToWhereClause},
{SimpleExpressionType.Empty, expr => _ => true },
};
}
public static int GetRecordCount(WhereClause where)
{
return (int)EstimateTable.Instance.GetRecordListCount(null, where.GetFilter(), null, null);
}
/// <summary>
/// Recursivly renders a WhereClause
/// </summary>
/// <param name="builder"></param>
/// <param name="group"></param>
protected virtual void WhereClause(StringBuilder builder, WhereClause group)
{
if (group.IsEmpty)
{
return;
}
builder.AppendFormat("(");
for (int i = 0; i < group.Terms.Count; i++)
{
if (i > 0)
{
RelationshipOperator(builder, group.Relationship);
}
WhereTerm term = group.Terms[i];
WhereClause(builder, term);
}
bool operatorRequired = group.Terms.Count > 0;
foreach (WhereClause childGroup in group.SubClauses)
{
if (childGroup.IsEmpty)
{
continue;
}
if (operatorRequired)
{
RelationshipOperator(builder, group.Relationship);
}
WhereClause(builder, childGroup);
operatorRequired = true;
}
builder.AppendFormat(")");
}
/// <summary>
/// Overrides the <see cref="QueryModelVisitorBase.VisitWhereClause(WhereClause, QueryModel, int)"/>.
/// </summary>
/// <param name="whereClause">The <see cref="WhereClause"/>.</param>
/// <param name="queryModel">The <see cref="QueryModel"/>.</param>
/// <param name="index">The index.</param>
public override void VisitWhereClause(WhereClause whereClause, QueryModel queryModel, int index)
{
_aggregator.Where.Add(GetODataExpression(whereClause.Predicate));
base.VisitWhereClause(whereClause, queryModel, index);
}
public virtual WhereClause CreateWhereClause(String searchText, String fromSearchControl, String AutoTypeAheadSearch, String AutoTypeAheadWordSeparators)
{
// This CreateWhereClause is used for loading list of suggestions for Auto Type-Ahead feature.
ScopeTable.Instance.InnerFilter = null;
WhereClause wc= new WhereClause();
// Compose the WHERE clause consiting of:
// 1. Static clause defined at design time.
// 2. User selected search criteria.
// 3. User selected filter criteria.
String appRelativeVirtualPath = (String)HttpContext.Current.Session["AppRelativeVirtualPath"];
// Adds clauses if values are selected in Filter controls which are configured in the page.
return wc;
}
public virtual WhereClause CreateWhereClause()
{
// This CreateWhereClause is used for loading the data.
UOMTable.Instance.InnerFilter = null;
WhereClause wc = new WhereClause();
// CreateWhereClause() Compose the WHERE clause consiting of:
// 1. Static clause defined at design time.
// 2. User selected search criteria.
// 3. User selected filter criteria.
if (MiscUtils.IsValueSelected(this.UOMDescriptionFilter)) {
wc.iAND(UOMTable.UOMDescription, BaseFilter.ComparisonOperator.EqualsTo, MiscUtils.GetSelectedValue(this.UOMDescriptionFilter, this.GetFromSession(this.UOMDescriptionFilter)), false, false);
}
if (MiscUtils.IsValueSelected(this.UOMNameFilter)) {
wc.iAND(UOMTable.UOMName, BaseFilter.ComparisonOperator.EqualsTo, MiscUtils.GetSelectedValue(this.UOMNameFilter, this.GetFromSession(this.UOMNameFilter)), false, false);
}
if (MiscUtils.IsValueSelected(this.UOMSearch)) {
// Strip "..." from begin and ending of the search text, otherwise the search will return 0 values as in database "..." is not stored.
if (this.UOMSearch.Text.StartsWith("...")) {
this.UOMSearch.Text = this.UOMSearch.Text.Substring(3,this.UOMSearch.Text.Length-3);
}
if (this.UOMSearch.Text.EndsWith("...")) {
this.UOMSearch.Text = this.UOMSearch.Text.Substring(0,this.UOMSearch.Text.Length-3);
// Strip the last word as well as it is likely only a partial word
int endindex = this.UOMSearch.Text.Length - 1;
while (!Char.IsWhiteSpace(UOMSearch.Text[endindex]) && endindex > 0) {
endindex--;
}
if (endindex > 0) {
this.UOMSearch.Text = this.UOMSearch.Text.Substring(0, endindex);
}
}
string formatedSearchText = MiscUtils.GetSelectedValue(this.UOMSearch, this.GetFromSession(this.UOMSearch));
// After stripping "..." see if the search text is null or empty.
if (MiscUtils.IsValueSelected(this.UOMSearch)) {
// These clauses are added depending on operator and fields selected in Control's property page, bindings tab.
WhereClause search = new WhereClause();
search.iOR(UOMTable.UOMName, BaseFilter.ComparisonOperator.Contains, formatedSearchText, true, false);
search.iOR(UOMTable.UOMDescription, BaseFilter.ComparisonOperator.Contains, formatedSearchText, true, false);
wc.iAND(search);
}
}
return wc;
}
public static UsersRecord[] GetRecords(
WhereClause where,
OrderBy orderBy,
int pageIndex,
int pageSize)
{
BaseClasses.Data.BaseFilter join = null;
ArrayList recList = UsersTable.Instance.GetRecordList(join, where.GetFilter(), null, orderBy, pageIndex, pageSize);
return (UsersRecord[])recList.ToArray(Type.GetType("FPCEstimate.Business.UsersRecord"));
}
/// <summary>
/// Visits the where clause.
/// </summary>
/// <param name="whereClause">The where clause.</param>
/// <param name="queryModel">The query model.</param>
/// <param name="index">The index.</param>
public override void VisitWhereClause(WhereClause whereClause, QueryModel queryModel, int index)
{
var query = QueryDocumentBuilder.BuildFrom(this.mongoSession, whereClause.Predicate);
query.CopyTo(this.querySpec.Conditions);
}
public static List <T> Select <T>(WhereClause where)
{
return(Select(typeof(T), where) as List <T>);
}
public static IEnumerable Select(this Type type, WhereClause where)
{
var method = type.BaseType.BaseType.GetMethod("Select", new Type[] { typeof(WhereClause) });
return(method.Invoke(null, new object[] { where }) as IEnumerable);
}
public static int GetRecordCount(BaseFilter join, WhereClause where)
{
return((int)FieldTripChoicesTable.Instance.GetRecordListCount(join, where.GetFilter(), null, null));
}
public override void VisitWhereClause(WhereClause whereClause, QueryModel queryModel, int index)
{
base.VisitWhereClause(whereClause, queryModel, index);
filterPredicateAnalyer.Apply(whereClause.Predicate);
}
/// <summary>
/// Adds the is after date or equal.
/// </summary>
/// <param name="whereClause">The where clause.</param>
/// <param name="logicOperator">The logic operator.</param>
/// <param name="column">The column.</param>
/// <param name="date">The date.</param>
/// <returns>The clause.</returns>
public static WhereClause AddIsAfterDateOrEqual(this WhereClause whereClause, LogicOperator logicOperator, string column, DateTime date)
{
whereClause.AddClause(logicOperator, column, Comparison.GreaterOrEquals, date.ToString("yyyy/MM/dd", CultureInfo.InvariantCulture));
return(whereClause);
}
public override void VisitWhereClause(WhereClause whereClause, QueryModel queryModel, int index)
{
_whereJoinDetector.Transform(whereClause);
}
/// <summary>
/// Creates a WHERE clause for a multivalued attribute
/// </summary>
/// <param name="builder">The builder for this query</param>
/// <param name="parameterNames">The parameters to evaluate against</param>
/// <returns>The WhereClause object for this query</returns>
private WhereClause CreateWhereClauseforAVPTarget(DBQueryBuilder builder, IList <string> parameterNames)
{
FromTerm fromTable;
fromTable = builder.GetNextAVPTableReference(this.SearchAttribute.TableName);
WhereClause clause = new WhereClause(WhereClauseRelationship.And);
if (this.Operator != ValueOperator.NotPresent)
{
string paramName = builder.AddParameter(this.SearchAttribute.Name);
WhereTerm attributeNameTerm = WhereTerm.CreateCompare(SqlExpression.Field("attributeName", fromTable), SqlExpression.Parameter(paramName), CompareOperator.Equal);
clause.Terms.Add(attributeNameTerm);
}
if (this.Operator == ValueOperator.IsPresent)
{
clause.Terms.Add(WhereTerm.CreateIsNotNull(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable)));
}
else if (this.Operator == ValueOperator.NotPresent)
{
string sql = this.CreateSubSelectStatementForMVNotExists(builder);
clause.Terms.Add(WhereTerm.CreateNotExists(sql));
}
else
{
WhereClause subClause = new WhereClause(WhereClauseRelationship.Or);
switch (this.Operator)
{
case ValueOperator.And:
case ValueOperator.Contains:
case ValueOperator.GreaterThan:
case ValueOperator.GreaterThanOrEq:
case ValueOperator.LessThan:
case ValueOperator.StartsWith:
case ValueOperator.EndsWith:
case ValueOperator.LessThanOrEq:
foreach (string parameterName in parameterNames)
{
subClause.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable), SqlExpression.Parameter(parameterName), this.CompareOperator));
}
break;
case ValueOperator.Equals:
subClause.Terms.Add(WhereTerm.CreateIn(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable), parameterNames.ToCommaSeparatedString()));
break;
case ValueOperator.NotEquals:
string sql = this.CreateSubSelectStatementForMVNotEquals(builder, parameterNames);
clause.Terms.Add(WhereTerm.CreateNotExists(sql));
break;
default:
throw new InvalidOperationException();
}
clause.SubClauses.Add(subClause);
}
return(clause);
}
/// <summary>
/// Return an array of values from the database. The values returned are DISTINCT values.
/// For example, GetColumnValues("Employees", "City") will return a list of all Cities
/// from the Employees table. There will be no duplicates in the list.
/// This function adds a Where Clause. So you can say something like "Country = 'USA'" and in this
/// case only cities in the US will be returned.
/// You can use the IN operator to compare the values. You can also use the resulting array to display
/// such as String.Join(", ", GetColumnValues("Employees", "City", "Country = 'USA'")
/// to display: New York, Chicago, Los Angeles, San Francisco
/// </summary>
/// <returns>An array of values for the given field as an Object.</returns>
public static string[] GetColumnValues(string tableName, string fieldName, string whereStr)
{
// Find the
PrimaryKeyTable bt = null;
bt = (PrimaryKeyTable)DatabaseObjects.GetTableObject(tableName);
if (bt == null)
{
throw new Exception("GETCOLUMNVALUES(" + tableName + ", " + fieldName + ", " + whereStr + "): " + Resource("Err:NoRecRetrieved"));
}
BaseColumn col = bt.TableDefinition.ColumnList.GetByCodeName(fieldName);
if (col == null)
{
throw new Exception("GETCOLUMNVALUES(" + tableName + ", " + fieldName + ", " + whereStr + "): " + Resource("Err:NoRecRetrieved"));
}
string[] values = null;
try
{
// Always start a transaction since we do not know if the calling function did.
SqlBuilderColumnSelection sqlCol = new SqlBuilderColumnSelection(false, true);
sqlCol.AddColumn(col);
WhereClause wc = new WhereClause();
if (!(whereStr == null) && whereStr.Trim().Length > 0)
{
wc.iAND(whereStr);
}
BaseClasses.Data.BaseFilter join = null;
values = bt.GetColumnValues(sqlCol, join, wc.GetFilter(), null, null, BaseTable.MIN_PAGE_NUMBER, BaseTable.MAX_BATCH_SIZE);
}
catch
{
}
// The value can be null. In this case, return an empty array since
// that is an acceptable value.
if (values == null)
{
values = new string[0];
}
return values;
}
public static T SelectSingle <T>(WhereClause where)
{
return(SelectSingle(typeof(T), where).ConvertTo <T>());
}
public static string Export(WhereClause where)
{
BaseFilter whereFilter = null;
if (where != null)
{
whereFilter = where.GetFilter();
}
return UsersTable.Instance.ExportRecordData(whereFilter);
}
/// <summary>
/// Generates a command that can be executed to update a row -- and return the contents of
/// the updated row.
/// </summary>
/// <param name="connection">The connection the command should be associated with</param>
/// <returns>Command to update the row</returns>
public override DbCommand GetCommand(DbConnection connection)
{
Validate.IsNotNull(nameof(connection), connection);
// Process the cells and columns
List <string> declareColumns = new List <string>();
List <SqlParameter> inParameters = new List <SqlParameter>();
List <string> setComponents = new List <string>();
List <string> outClauseColumns = new List <string>();
List <string> selectColumns = new List <string>();
for (int i = 0; i < AssociatedObjectMetadata.Columns.Length; i++)
{
EditColumnMetadata metadata = AssociatedObjectMetadata.Columns[i];
// Add the output columns regardless of whether the column is read only
declareColumns.Add($"{metadata.EscapedName} {ToSqlScript.FormatColumnType(metadata.DbColumn, useSemanticEquivalent: true)}");
if (metadata.IsHierarchyId)
{
outClauseColumns.Add($"inserted.{metadata.EscapedName}.ToString() {metadata.EscapedName}");
}
else
{
outClauseColumns.Add($"inserted.{metadata.EscapedName}");
}
selectColumns.Add(metadata.EscapedName);
// If we have a new value for the column, proccess it now
CellUpdate cellUpdate;
if (cellUpdates.TryGetValue(i, out cellUpdate))
{
string paramName = $"@Value{RowId}_{i}";
if (metadata.IsHierarchyId)
{
setComponents.Add($"{metadata.EscapedName} = CONVERT(hierarchyid,{paramName})");
}
else
{
setComponents.Add($"{metadata.EscapedName} = {paramName}");
}
inParameters.Add(new SqlParameter(paramName, AssociatedResultSet.Columns[i].SqlDbType)
{
Value = cellUpdate.Value
});
}
}
// Put everything together into a single query
// Step 1) Build a temp table for inserting output values into
string tempTableName = $"@Update{RowId}Output";
string declareStatement = string.Format(DeclareStatement, tempTableName, string.Join(", ", declareColumns));
// Step 2) Build the update statement
WhereClause whereClause = GetWhereClause(true);
string updateStatementFormat = AssociatedObjectMetadata.IsMemoryOptimized
? UpdateOutputMemOptimized
: UpdateOutput;
string updateStatement = string.Format(updateStatementFormat,
AssociatedObjectMetadata.EscapedMultipartName,
string.Join(", ", setComponents),
string.Join(", ", outClauseColumns),
tempTableName,
whereClause.CommandText);
string validateScript = string.Format(CultureInfo.InvariantCulture, validateUpdateOnlyOneRow,
AssociatedObjectMetadata.EscapedMultipartName,
whereClause.CommandText);
// Step 3) Build the select statement
string selectStatement = string.Format(SelectStatement, string.Join(", ", selectColumns), tempTableName);
// Step 4) Put it all together into a results object
StringBuilder query = new StringBuilder();
query.AppendLine(declareStatement);
query.AppendLine(validateScript);
query.AppendLine(updateStatement);
query.AppendLine(selectStatement);
query.Append("END");
// Build the command
DbCommand command = connection.CreateCommand();
command.CommandText = query.ToString();
command.CommandType = CommandType.Text;
command.Parameters.AddRange(inParameters.ToArray());
command.Parameters.AddRange(whereClause.Parameters.ToArray());
return(command);
}
public static string GetSum(
BaseColumn col,
BaseFilter join,
WhereClause where,
OrderBy orderBy,
int pageIndex,
int pageSize)
{
SqlBuilderColumnSelection colSel = new SqlBuilderColumnSelection(false, false);
colSel.AddColumn(col, SqlBuilderColumnOperation.OperationType.Sum);
return UsersTable.Instance.GetColumnStatistics(colSel, join, where.GetFilter(), null, orderBy, pageIndex, pageSize);
}
static void Main(string[] args)
{
var exampleToRun = ExamplesEnumeration.JoinWithExtensionMethodsSyntax;
switch (exampleToRun)
{
case ExamplesEnumeration.BasicSyntax:
BasicSyntax.ShowBasicSyntaxLinq();
break;
case ExamplesEnumeration.GenderCondition:
BasicSyntax.ShowConditionWithGender();
break;
case ExamplesEnumeration.ExtensionMethodSyntax:
ExtensionMethodSyntax.ShowExtensionMethodSyntax();
break;
case ExamplesEnumeration.DefferedExecution:
ExtensionMethodSyntax.DefferedExecution();
break;
case ExamplesEnumeration.WhereBasicSyntax:
WhereClause.ShowWhereWithBasicSyntax();
break;
case ExamplesEnumeration.WhereExtensionSyntax:
WhereClause.ShowWhereWithExtensionSyntax();
break;
case ExamplesEnumeration.OrderByBasicSyntax:
OrderByClause.ShowOrderBy();
break;
case ExamplesEnumeration.OrderByExtensionSyntax:
OrderByClause.OrderByExtensionSyntax();
break;
case ExamplesEnumeration.GroupByBasicSyntax:
GroupByClause.ShowGroupBy();
break;
case ExamplesEnumeration.GroupByExtensionSyntax:
GroupByClause.GroupByExtensionSyntax();
break;
case ExamplesEnumeration.GroupByInto:
GroupByClause.GroupByInto();
break;
case ExamplesEnumeration.AnonymousTypes:
AnonymousTypes.ShowAnonymousTypes();
break;
case ExamplesEnumeration.AnonymousTypesTryChangeProperty:
AnonymousTypes.TryChangeProperty();
break;
case ExamplesEnumeration.AnonymousTypesPropertyNamesInheritance:
AnonymousTypes.PropertiesNamesInheritance();
break;
case ExamplesEnumeration.ExplicitSelectClause:
ExplicitSelectClause.ShowExplicitSelect();
break;
case ExamplesEnumeration.ExplicitSelectAnonymousType:
ExplicitSelectClause.SelectAnonymousType();
break;
case ExamplesEnumeration.DynamicTypes:
DynamicTypes.ShowDynamicTypes();
break;
case ExamplesEnumeration.JoinWithQuerySyntax:
JoinClause.ShowJoinWithQuerySyntax();
break;
case ExamplesEnumeration.JoinWithExtensionMethodsSyntax:
JoinClause.ShowJoinWithExtensionMethodsSyntax();
break;
}
Console.Read();
}
public virtual WhereClause CreateWhereClause(String searchText, String fromSearchControl, String AutoTypeAheadSearch, String AutoTypeAheadWordSeparators)
{
// This CreateWhereClause is used for loading list of suggestions for Auto Type-Ahead feature.
EstimateLineTable.Instance.InnerFilter = null;
WhereClause wc = new WhereClause();
// Compose the WHERE clause consiting of:
// 1. Static clause defined at design time.
// 2. User selected search criteria.
// 3. User selected filter criteria.
String appRelativeVirtualPath = (String)HttpContext.Current.Session["AppRelativeVirtualPath"];
string selectedRecordInScopeRecordControl = HttpContext.Current.Session["EstimateLineTableControlWhereClause"] as string;
if (selectedRecordInScopeRecordControl != null && KeyValue.IsXmlKey(selectedRecordInScopeRecordControl))
{
KeyValue selectedRecordKeyValue = KeyValue.XmlToKey(selectedRecordInScopeRecordControl);
if (selectedRecordKeyValue != null && selectedRecordKeyValue.ContainsColumn(EstimateLineTable.ScopeID))
{
wc.iAND(EstimateLineTable.ScopeID, BaseFilter.ComparisonOperator.EqualsTo, selectedRecordKeyValue.GetColumnValue(EstimateLineTable.ScopeID).ToString());
}
}
// Adds clauses if values are selected in Filter controls which are configured in the page.
return wc;
}
public virtual void LoadData()
{
// Load the data from the database into the DataSource DatabaseOLR_db%dbo.RegistrationTypes record.
// It is better to make changes to functions called by LoadData such as
// CreateWhereClause, rather than making changes here.
// The RecordUniqueId is set the first time a record is loaded, and is
// used during a PostBack to load the record.
if (this.RecordUniqueId != null && this.RecordUniqueId.Length > 0)
{
this.DataSource = RegistrationTypesTable.GetRecord(this.RecordUniqueId, true);
return;
}
// This is the first time a record is being retrieved from the database.
// So create a Where Clause based on the staic Where clause specified
// on the Query wizard and the dynamic part specified by the end user
// on the search and filter controls (if any).
WhereClause wc = this.CreateWhereClause();
System.Web.UI.WebControls.Panel Panel = (System.Web.UI.WebControls.Panel)MiscUtils.FindControlRecursively(this, "RegistrationTypesRecordControlPanel");
if (Panel != null)
{
Panel.Visible = true;
}
// If there is no Where clause, then simply create a new, blank record.
if (wc == null || !(wc.RunQuery))
{
this.DataSource = new RegistrationTypesRecord();
if (Panel != null)
{
Panel.Visible = false;
}
return;
}
// Retrieve the record from the database. It is possible
RegistrationTypesRecord[] recList = RegistrationTypesTable.GetRecords(wc, null, 0, 2);
if (recList.Length == 0)
{
// There is no data for this Where clause.
wc.RunQuery = false;
if (Panel != null)
{
Panel.Visible = false;
}
return;
}
// Set DataSource based on record retrieved from the database.
this.DataSource = RegistrationTypesTable.GetRecord(recList[0].GetID().ToXmlString(), true);
}
// Fill the UOMID list.
protected virtual void PopulateUOMIDDropDownList(string selectedValue, int maxItems)
{
this.UOMID.Items.Clear();
// 1. Setup the static list items
// Add the Please Select item.
this.UOMID.Items.Insert(0, new ListItem(this.Page.GetResourceValue("Txt:PleaseSelect", "FPCEstimate"), "--PLEASE_SELECT--"));
// 2. Set up the WHERE and the ORDER BY clause by calling the CreateWhereClause_UOMIDDropDownList function.
// It is better to customize the where clause there.
WhereClause wc = CreateWhereClause_UOMIDDropDownList();
// Create the ORDER BY clause to sort based on the displayed value.
OrderBy orderBy = new OrderBy(false, false);
orderBy.Add(UOMTable.UOMName, OrderByItem.OrderDir.Asc);
System.Collections.Generic.IDictionary<string, object> variables = new System.Collections.Generic.Dictionary<string, object> ();
// 3. Read a total of maxItems from the database and insert them into the UOMIDDropDownList.
UOMRecord[] itemValues = null;
if (wc.RunQuery)
{
int counter = 0;
int pageNum = 0;
FormulaEvaluator evaluator = new FormulaEvaluator();
do
{
itemValues = UOMTable.GetRecords(wc, orderBy, pageNum, maxItems);
foreach (UOMRecord itemValue in itemValues)
{
// Create the item and add to the list.
string cvalue = null;
string fvalue = null;
if (itemValue.UOMIDSpecified)
{
cvalue = itemValue.UOMID.ToString().ToString();
if (counter < maxItems && this.UOMID.Items.FindByValue(cvalue) == null)
{
Boolean _isExpandableNonCompositeForeignKey = ScopeTable.Instance.TableDefinition.IsExpandableNonCompositeForeignKey(ScopeTable.UOMID);
if(_isExpandableNonCompositeForeignKey && ScopeTable.UOMID.IsApplyDisplayAs)
fvalue = ScopeTable.GetDFKA(itemValue, ScopeTable.UOMID);
if ((!_isExpandableNonCompositeForeignKey) || (String.IsNullOrEmpty(fvalue)))
fvalue = itemValue.Format(UOMTable.UOMName);
if (fvalue == null || fvalue.Trim() == "")
fvalue = cvalue;
ListItem newItem = new ListItem(fvalue, cvalue);
this.UOMID.Items.Add(newItem);
counter += 1;
}
}
}
pageNum++;
}
while (itemValues.Length == maxItems && counter < maxItems);
}
// 4. Set the selected value (insert if not already present).
if (selectedValue != null &&
selectedValue.Trim() != "" &&
!MiscUtils.SetSelectedValue(this.UOMID, selectedValue) &&
!MiscUtils.SetSelectedDisplayText(this.UOMID, selectedValue))
{
// construct a whereclause to query a record with UOM.UOMID = selectedValue
CompoundFilter filter2 = new CompoundFilter(CompoundFilter.CompoundingOperators.And_Operator, null);
WhereClause whereClause2 = new WhereClause();
filter2.AddFilter(new BaseClasses.Data.ColumnValueFilter(UOMTable.UOMID, selectedValue, BaseClasses.Data.BaseFilter.ComparisonOperator.EqualsTo, false));
whereClause2.AddFilter(filter2, CompoundFilter.CompoundingOperators.And_Operator);
// Execute the query
try
{
UOMRecord[] rc = UOMTable.GetRecords(whereClause2, new OrderBy(false, false), 0, 1);
System.Collections.Generic.IDictionary<string, object> vars = new System.Collections.Generic.Dictionary<string, object> ();
// if find a record, add it to the dropdown and set it as selected item
if (rc != null && rc.Length == 1)
{
string fvalue = ScopeTable.UOMID.Format(selectedValue);
ListItem item = new ListItem(fvalue, selectedValue);
item.Selected = true;
this.UOMID.Items.Add(item);
}
}
catch
{
}
}
}
public override void VisitWhereClause(WhereClause whereClause, QueryModel queryModel, int index)
{
whereClause.TransformExpressions(e => MergeAggregatingResultsInExpressionRewriter.Rewrite(e, new NameGenerator(queryModel)));
}
public virtual WhereClause CreateWhereClause_UOMNameFilter()
{
// Create a where clause for the filter UOMNameFilter.
// This function is called by the Populate method to load the items
// in the UOMNameFilterDropDownList
WhereClause wc = new WhereClause();
return wc;
}
internal void Parse(WhereClause whereClause)
{
Visit(whereClause.Predicate);
}
/// <summary>
/// Creates a WHERE clause for a single-valued attribute
/// </summary>
/// <param name="parameterNames">The parameters to evaluate against</param>
/// <returns>The WhereClause object for this query</returns>
private WhereClause CreateWhereClauseforSVTarget(IList <string> parameterNames)
{
FromTerm fromTable;
fromTable = this.SearchAttribute.DBTable;
WhereClause clause = new WhereClause(WhereClauseRelationship.And);
if (this.Operator == ValueOperator.IsPresent)
{
clause.Terms.Add(WhereTerm.CreateIsNotNull(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable)));
}
else if (this.Operator == ValueOperator.NotPresent)
{
clause.Terms.Add(WhereTerm.CreateIsNull(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable)));
}
else if (parameterNames.Count == 0)
{
throw new ArgumentNullException("parameterNames");
}
else if (parameterNames.Count > 1)
{
WhereClause subClause = new WhereClause(WhereClauseRelationship.Or);
switch (this.Operator)
{
case ValueOperator.And:
case ValueOperator.Contains:
case ValueOperator.StartsWith:
case ValueOperator.EndsWith:
case ValueOperator.GreaterThan:
case ValueOperator.GreaterThanOrEq:
case ValueOperator.LessThan:
case ValueOperator.LessThanOrEq:
foreach (string parameterName in parameterNames)
{
subClause.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable), SqlExpression.Parameter(parameterName), this.CompareOperator));
}
break;
case ValueOperator.Equals:
subClause.Terms.Add(WhereTerm.CreateIn(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable), parameterNames.ToCommaSeparatedString()));
break;
case ValueOperator.NotEquals:
subClause.Terms.Add(WhereTerm.CreateNotIn(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable), parameterNames.ToCommaSeparatedString()));
break;
default:
throw new InvalidOperationException();
}
clause.SubClauses.Add(subClause);
}
else
{
clause.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable), SqlExpression.Parameter(parameterNames[0]), this.CompareOperator));
}
return(clause);
}
public override void VisitWhereClause(WhereClause whereClause, QueryModel queryModel, int index)
{
AppendLine();
TransformingVisitor.StringBuilder.Append("where ");
base.VisitWhereClause(whereClause, queryModel, index);
}
public static string GetCount(
BaseColumn col,
WhereClause where,
OrderBy orderBy,
int pageIndex,
int pageSize)
{
SqlBuilderColumnSelection colSel = new SqlBuilderColumnSelection(false, false);
colSel.AddColumn(col, SqlBuilderColumnOperation.OperationType.Count);
return EstimateTable.Instance.GetColumnStatistics(colSel, null, where.GetFilter(), null, orderBy, pageIndex, pageSize);
}
public static int GetRecordCount(WhereClause where)
{
return((int)Roles1Table.Instance.GetRecordListCount(null, where.GetFilter(), null, null));
}
public static EstimateRecord[] GetRecords(
BaseFilter join,
WhereClause where,
OrderBy orderBy,
int pageIndex,
int pageSize)
{
ArrayList recList = EstimateTable.Instance.GetRecordList(join, where.GetFilter(), null, orderBy, pageIndex, pageSize);
return (EstimateRecord[])recList.ToArray(Type.GetType("FPCEstimate.Business.EstimateRecord"));
}
public override void VisitWhereClause(WhereClause whereClause, QueryModel queryModel, int index)
{
_expander.Transform(whereClause);
}
// To customize, override this method in ReportRecordControl.
public virtual WhereClause CreateWhereClause()
{
WhereClause wc;
ReportTable.Instance.InnerFilter = null;
wc = new WhereClause();
// Compose the WHERE clause consiting of:
// 1. Static clause defined at design time.
// 2. User selected search criteria.
// 3. User selected filter criteria.
// Retrieve the record id from the URL parameter.
string recId = this.Page.Request.QueryString["Report"];
if (recId == null || recId.Length == 0) {
return null;
}
recId = ((BaseApplicationPage)(this.Page)).Decrypt(recId);
if (recId == null || recId.Length == 0) {
return null;
}
HttpContext.Current.Session["QueryString in AddReport"] = recId;
if (KeyValue.IsXmlKey(recId)) {
// Keys are typically passed as XML structures to handle composite keys.
// If XML, then add a Where clause based on the Primary Key in the XML.
KeyValue pkValue = KeyValue.XmlToKey(recId);
wc.iAND(ReportTable.ReportID, BaseFilter.ComparisonOperator.EqualsTo, pkValue.GetColumnValueString(ReportTable.ReportID));
}
else {
// The URL parameter contains the actual value, not an XML structure.
wc.iAND(ReportTable.ReportID, BaseFilter.ComparisonOperator.EqualsTo, recId);
}
return wc;
}
public static void Update(EmployeeOtherControlsSubscriptionsViewData data)
{
// Create and execute the command
EmployeeOtherControlsSubscriptionsViewData oldData = Load();
string sql = "Update " + TABLE + " set ";
if (!oldData.Src.Equals(data.Src))
{
sql = sql + "Src=@Src,";
}
if (!oldData.Description.Equals(data.Description))
{
sql = sql + "Description=@Description,";
}
if (!oldData.ClassSrc.Equals(data.ClassSrc))
{
sql = sql + "ClassSrc=@ClassSrc,";
}
if (!oldData.ClassDescription.Equals(data.ClassDescription))
{
sql = sql + "ClassDescription=@ClassDescription,";
}
if (!oldData.AllowUnsubscribe.Equals(data.AllowUnsubscribe))
{
sql = sql + "AllowUnsubscribe=@AllowUnsubscribe,";
}
if (!oldData.SortOrder.Equals(data.SortOrder))
{
sql = sql + "SortOrder=@SortOrder,";
}
if (!oldData.OrgEmployeesID.Equals(data.OrgEmployeesID))
{
sql = sql + "OrgEmployeesID=@OrgEmployeesID,";
}
if (!oldData.ControlsID.Equals(data.ControlsID))
{
sql = sql + "ControlsID=@ControlsID,";
}
if (!oldData.ControlSubscriptionsID.Equals(data.ControlSubscriptionsID))
{
sql = sql + "ControlSubscriptionsID=@ControlSubscriptionsID,";
}
WhereClause w = new WhereClause();
sql = sql.Substring(0, sql.Length - 1) + w.FormatSql();
SqlCommand cmd = GetSqlCommand(DatabaseEnum.INTRANET, sql, CommandType.Text, COMMAND_TIMEOUT);
//Create the parameters and append them to the command object
if (!oldData.Src.Equals(data.Src))
{
cmd.Parameters.Add(new SqlParameter("@Src", SqlDbType.VarChar, 50, ParameterDirection.Input, false, 0, 0, "Src", DataRowVersion.Proposed, data.Src.DBValue));
}
if (!oldData.Description.Equals(data.Description))
{
cmd.Parameters.Add(new SqlParameter("@Description", SqlDbType.VarChar, 50, ParameterDirection.Input, false, 0, 0, "Description", DataRowVersion.Proposed, data.Description.DBValue));
}
if (!oldData.ClassSrc.Equals(data.ClassSrc))
{
cmd.Parameters.Add(new SqlParameter("@ClassSrc", SqlDbType.VarChar, 50, ParameterDirection.Input, false, 0, 0, "ClassSrc", DataRowVersion.Proposed, data.ClassSrc.DBValue));
}
if (!oldData.ClassDescription.Equals(data.ClassDescription))
{
cmd.Parameters.Add(new SqlParameter("@ClassDescription", SqlDbType.VarChar, 30, ParameterDirection.Input, false, 0, 0, "ClassDescription", DataRowVersion.Proposed, data.ClassDescription.DBValue));
}
if (!oldData.AllowUnsubscribe.Equals(data.AllowUnsubscribe))
{
cmd.Parameters.Add(new SqlParameter("@AllowUnsubscribe", SqlDbType.Bit, 0, ParameterDirection.Input, false, 0, 0, "AllowUnsubscribe", DataRowVersion.Proposed, !data.AllowUnsubscribe.IsValid ? data.AllowUnsubscribe.DBValue : data.AllowUnsubscribe.DBValue.Equals("Y") ? 1 : 0));
}
if (!oldData.SortOrder.Equals(data.SortOrder))
{
cmd.Parameters.Add(new SqlParameter("@SortOrder", SqlDbType.Int, 0, ParameterDirection.Input, false, 10, 0, "SortOrder", DataRowVersion.Proposed, data.SortOrder.DBValue));
}
if (!oldData.OrgEmployeesID.Equals(data.OrgEmployeesID))
{
cmd.Parameters.Add(new SqlParameter("@OrgEmployeesID", SqlDbType.Int, 0, ParameterDirection.Input, false, 10, 0, "OrgEmployeesID", DataRowVersion.Proposed, data.OrgEmployeesID.DBValue));
}
if (!oldData.ControlsID.Equals(data.ControlsID))
{
cmd.Parameters.Add(new SqlParameter("@ControlsID", SqlDbType.Int, 0, ParameterDirection.Input, false, 10, 0, "ControlsID", DataRowVersion.Proposed, data.ControlsID.DBValue));
}
if (!oldData.ControlSubscriptionsID.Equals(data.ControlSubscriptionsID))
{
cmd.Parameters.Add(new SqlParameter("@ControlSubscriptionsID", SqlDbType.Int, 0, ParameterDirection.Input, false, 10, 0, "ControlSubscriptionsID", DataRowVersion.Proposed, data.ControlSubscriptionsID.DBValue));
}
// Execute the query
if (cmd.Parameters.Count > 0)
{
cmd.ExecuteNonQuery();
}
}
public static int GetRecordCount(BaseFilter join, WhereClause where)
{
return (int)UsersTable.Instance.GetRecordListCount(join, where.GetFilter(), null, null);
}
/// <summary> /// 获取查询条件 /// </summary> public virtual string GetCondition() => WhereClause.GetCondition();
public static UsersRecord[] GetRecords(
BaseFilter join,
WhereClause where,
OrderBy orderBy,
int pageIndex,
int pageSize,
ref int totalRecords)
{
ArrayList recList = UsersTable.Instance.GetRecordList(join, where.GetFilter(), null, orderBy, pageIndex, pageSize, ref totalRecords);
return (UsersRecord[])recList.ToArray(Type.GetType("FPCEstimate.Business.UsersRecord"));
}
public TObject SelectObject <TObject>(WhereClause whereClause) where TObject : DataObject => (TObject)SelectObjectReturns.FirstOrDefault();
public static String[] GetValues(
BaseColumn col,
BaseFilter join,
WhereClause where,
OrderBy orderBy,
int maxItems)
{
// Create the filter list.
SqlBuilderColumnSelection retCol = new SqlBuilderColumnSelection(false, true);
retCol.AddColumn(col);
return UsersTable.Instance.GetColumnValues(retCol, join, where.GetFilter(), null, orderBy, BaseTable.MIN_PAGE_NUMBER, maxItems);
}
public IList <TObject> SelectObjects <TObject>(WhereClause whereClause) where TObject : DataObject => new List <TObject>();
public virtual WhereClause CreateWhereClause()
{
// This CreateWhereClause is used for loading the data.
EstimateLineTable.Instance.InnerFilter = null;
WhereClause wc = new WhereClause();
// CreateWhereClause() Compose the WHERE clause consiting of:
// 1. Static clause defined at design time.
// 2. User selected search criteria.
// 3. User selected filter criteria.
KeyValue selectedRecordKeyValue = new KeyValue();
FPCEstimate.UI.Controls.EditScope.ScopeRecordControl scopeRecordControlObj = (MiscUtils.FindControlRecursively(this.Page , "ScopeRecordControl") as FPCEstimate.UI.Controls.EditScope.ScopeRecordControl);
if (scopeRecordControlObj != null && scopeRecordControlObj.GetRecord() != null && scopeRecordControlObj.GetRecord().IsCreated && scopeRecordControlObj.GetRecord().ScopeID != null)
{
wc.iAND(EstimateLineTable.ScopeID, BaseFilter.ComparisonOperator.EqualsTo, scopeRecordControlObj.GetRecord().ScopeID.ToString());
selectedRecordKeyValue.AddElement(EstimateLineTable.ScopeID.InternalName, scopeRecordControlObj.GetRecord().ScopeID.ToString());
}
else
{
wc.RunQuery = false;
return wc;
}
HttpContext.Current.Session["EstimateLineTableControlWhereClause"] = selectedRecordKeyValue.ToXmlString();
return wc;
}
public static void Update(KnowledgebasesSectionsArticlesData data)
{
// Create and execute the command
KnowledgebasesSectionsArticlesData oldData = Load(data.KnowledgebasesSectionsArticlesID);
string sql = "Update " + TABLE + " set ";
if (!oldData.Title.Equals(data.Title))
{
sql = sql + "Title=@Title,";
}
if (!oldData.DateStart.Equals(data.DateStart))
{
sql = sql + "DateStart=@DateStart,";
}
if (!oldData.DateEnd.Equals(data.DateEnd))
{
sql = sql + "DateEnd=@DateEnd,";
}
if (!oldData.ParentID.Equals(data.ParentID))
{
sql = sql + "ParentID=@ParentID,";
}
if (!oldData.KnowledgebasesID.Equals(data.KnowledgebasesID))
{
sql = sql + "KnowledgebasesID=@KnowledgebasesID,";
}
if (!oldData.HasChild.Equals(data.HasChild))
{
sql = sql + "HasChild=@HasChild,";
}
if (!oldData.PrevKnowledgebasesSectionsID.Equals(data.PrevKnowledgebasesSectionsID))
{
sql = sql + "PrevKnowledgebasesSectionsID=@PrevKnowledgebasesSectionsID,";
}
if (!oldData.NextKnowledgebasesSectionsID.Equals(data.NextKnowledgebasesSectionsID))
{
sql = sql + "NextKnowledgebasesSectionsID=@NextKnowledgebasesSectionsID,";
}
if (!oldData.ListInParentArticle.Equals(data.ListInParentArticle))
{
sql = sql + "ListInParentArticle=@ListInParentArticle,";
}
if (!oldData.IncludeSummaryinParent.Equals(data.IncludeSummaryinParent))
{
sql = sql + "IncludeSummaryinParent=@IncludeSummaryinParent,";
}
if (!oldData.Sort.Equals(data.Sort))
{
sql = sql + "Sort=@Sort,";
}
if (!oldData.IsSection.Equals(data.IsSection))
{
sql = sql + "IsSection=@IsSection,";
}
if (!oldData.IsTemp.Equals(data.IsTemp))
{
sql = sql + "IsTemp=@IsTemp,";
}
WhereClause w = new WhereClause();
w.And("KnowledgebasesSectionsArticlesID", data.KnowledgebasesSectionsArticlesID.DBValue);
sql = sql.Substring(0, sql.Length - 1) + w.FormatSql();
SqlCommand cmd = GetSqlCommand(DatabaseEnum.INTRANET, sql, CommandType.Text, COMMAND_TIMEOUT);
//Create the parameters and append them to the command object
if (!oldData.KnowledgebasesSectionsArticlesID.Equals(data.KnowledgebasesSectionsArticlesID))
{
cmd.Parameters.Add(new SqlParameter("@KnowledgebasesSectionsArticlesID", SqlDbType.Int, 0, ParameterDirection.Input, false, 10, 0, "KnowledgebasesSectionsArticlesID", DataRowVersion.Proposed, data.KnowledgebasesSectionsArticlesID.DBValue));
}
if (!oldData.Title.Equals(data.Title))
{
cmd.Parameters.Add(new SqlParameter("@Title", SqlDbType.VarChar, 250, ParameterDirection.Input, false, 0, 0, "Title", DataRowVersion.Proposed, data.Title.DBValue));
}
if (!oldData.DateStart.Equals(data.DateStart))
{
cmd.Parameters.Add(new SqlParameter("@DateStart", SqlDbType.SmallDateTime, 0, ParameterDirection.Input, false, 0, 0, "DateStart", DataRowVersion.Proposed, data.DateStart.DBValue));
}
if (!oldData.DateEnd.Equals(data.DateEnd))
{
cmd.Parameters.Add(new SqlParameter("@DateEnd", SqlDbType.SmallDateTime, 0, ParameterDirection.Input, false, 0, 0, "DateEnd", DataRowVersion.Proposed, data.DateEnd.DBValue));
}
if (!oldData.ParentID.Equals(data.ParentID))
{
cmd.Parameters.Add(new SqlParameter("@ParentID", SqlDbType.Int, 0, ParameterDirection.Input, false, 10, 0, "ParentID", DataRowVersion.Proposed, data.ParentID.DBValue));
}
if (!oldData.KnowledgebasesID.Equals(data.KnowledgebasesID))
{
cmd.Parameters.Add(new SqlParameter("@KnowledgebasesID", SqlDbType.Int, 0, ParameterDirection.Input, false, 10, 0, "KnowledgebasesID", DataRowVersion.Proposed, data.KnowledgebasesID.DBValue));
}
if (!oldData.HasChild.Equals(data.HasChild))
{
cmd.Parameters.Add(new SqlParameter("@HasChild", SqlDbType.Bit, 0, ParameterDirection.Input, false, 0, 0, "HasChild", DataRowVersion.Proposed, !data.HasChild.IsValid ? data.HasChild.DBValue : data.HasChild.DBValue.Equals("Y") ? 1 : 0));
}
if (!oldData.PrevKnowledgebasesSectionsID.Equals(data.PrevKnowledgebasesSectionsID))
{
cmd.Parameters.Add(new SqlParameter("@PrevKnowledgebasesSectionsID", SqlDbType.Int, 0, ParameterDirection.Input, false, 10, 0, "PrevKnowledgebasesSectionsID", DataRowVersion.Proposed, data.PrevKnowledgebasesSectionsID.DBValue));
}
if (!oldData.NextKnowledgebasesSectionsID.Equals(data.NextKnowledgebasesSectionsID))
{
cmd.Parameters.Add(new SqlParameter("@NextKnowledgebasesSectionsID", SqlDbType.Int, 0, ParameterDirection.Input, false, 10, 0, "NextKnowledgebasesSectionsID", DataRowVersion.Proposed, data.NextKnowledgebasesSectionsID.DBValue));
}
if (!oldData.ListInParentArticle.Equals(data.ListInParentArticle))
{
cmd.Parameters.Add(new SqlParameter("@ListInParentArticle", SqlDbType.Bit, 0, ParameterDirection.Input, false, 0, 0, "ListInParentArticle", DataRowVersion.Proposed, !data.ListInParentArticle.IsValid ? data.ListInParentArticle.DBValue : data.ListInParentArticle.DBValue.Equals("Y") ? 1 : 0));
}
if (!oldData.IncludeSummaryinParent.Equals(data.IncludeSummaryinParent))
{
cmd.Parameters.Add(new SqlParameter("@IncludeSummaryinParent", SqlDbType.Bit, 0, ParameterDirection.Input, false, 0, 0, "IncludeSummaryinParent", DataRowVersion.Proposed, !data.IncludeSummaryinParent.IsValid ? data.IncludeSummaryinParent.DBValue : data.IncludeSummaryinParent.DBValue.Equals("Y") ? 1 : 0));
}
if (!oldData.Sort.Equals(data.Sort))
{
cmd.Parameters.Add(new SqlParameter("@Sort", SqlDbType.Int, 0, ParameterDirection.Input, false, 10, 0, "Sort", DataRowVersion.Proposed, data.Sort.DBValue));
}
if (!oldData.IsSection.Equals(data.IsSection))
{
cmd.Parameters.Add(new SqlParameter("@IsSection", SqlDbType.Bit, 0, ParameterDirection.Input, false, 0, 0, "IsSection", DataRowVersion.Proposed, !data.IsSection.IsValid ? data.IsSection.DBValue : data.IsSection.DBValue.Equals("Y") ? 1 : 0));
}
if (!oldData.IsTemp.Equals(data.IsTemp))
{
cmd.Parameters.Add(new SqlParameter("@IsTemp", SqlDbType.Bit, 0, ParameterDirection.Input, false, 0, 0, "IsTemp", DataRowVersion.Proposed, !data.IsTemp.IsValid ? data.IsTemp.DBValue : data.IsTemp.DBValue.Equals("Y") ? 1 : 0));
}
// Execute the query
if (cmd.Parameters.Count > 0)
{
cmd.ExecuteNonQuery();
}
}
// To customize, override this method in ScopeRecordControl.
public virtual WhereClause CreateWhereClause()
{
WhereClause wc;
ScopeTable.Instance.InnerFilter = null;
wc = new WhereClause();
// Compose the WHERE clause consiting of:
// 1. Static clause defined at design time.
// 2. User selected search criteria.
// 3. User selected filter criteria.
// Retrieve the record id from the URL parameter.
string recId = ((BaseApplicationPage)(this.Page)).Decrypt(this.Page.Request.QueryString["Scope"]);
if (recId == null || recId.Length == 0) {
// Get the error message from the application resource file.
throw new Exception(Page.GetResourceValue("Err:UrlParamMissing", "FPCEstimate").Replace("{URL}", "Scope"));
}
HttpContext.Current.Session["QueryString in EditScope"] = recId;
if (KeyValue.IsXmlKey(recId)) {
// Keys are typically passed as XML structures to handle composite keys.
// If XML, then add a Where clause based on the Primary Key in the XML.
KeyValue pkValue = KeyValue.XmlToKey(recId);
wc.iAND(ScopeTable.ScopeID, BaseFilter.ComparisonOperator.EqualsTo, pkValue.GetColumnValueString(ScopeTable.ScopeID));
}
else {
// The URL parameter contains the actual value, not an XML structure.
wc.iAND(ScopeTable.ScopeID, BaseFilter.ComparisonOperator.EqualsTo, recId);
}
return wc;
}
public static int GetRecordCount(BaseFilter join, WhereClause where)
{
return((int)UserRolesLinkTable.Instance.GetRecordListCount(join, where.GetFilter(), null, null));
}
// Generate the event handling functions for pagination events.
// Generate the event handling functions for filter and search events.
public virtual WhereClause CreateWhereClause_UOMIDDropDownList()
{
// By default, we simply return a new WhereClause.
// Add additional where clauses to restrict the items shown in the dropdown list.
// This WhereClause is for the UOM table.
// Examples:
// wc.iAND(UOMTable.UOMName, BaseFilter.ComparisonOperator.EqualsTo, "XYZ");
// wc.iAND(UOMTable.Active, BaseFilter.ComparisonOperator.EqualsTo, "1");
WhereClause wc = new WhereClause();
return wc;
}
public static int GetRecordCount(BaseFilter join, WhereClause where)
{
return (int)PhotoClubsTable.Instance.GetRecordListCount(join, where.GetFilter(), null, null);
}
protected virtual void UOMID_SelectedIndexChanged(object sender, EventArgs args)
{
// If a large list selector or a Quick Add link is used, the dropdown list
// will contain an item that was not in the original (smaller) list. During postbacks,
// this new item will not be in the list - since the list is based on the original values
// read from the database. This function adds the value back if necessary.
// In addition, This dropdown can be used on make/model/year style dropdowns. Make filters the result of Model.
// Mode filters the result of Year. When users change the value of Make, Model and Year are repopulated.
// When this function is fire for Make or Model, we don't want the following code executed.
// Therefore, we check this situation using Items.Count > 1
// Since both of these feature use javascript to fire this event, this can be skip if the event is fired by server side,
// so we check if it is not postback
if (!this.Page.IsPostBack && this.UOMID.Items.Count > 1)
{
string selectedValue = MiscUtils.GetValueSelectedPageRequest(this.UOMID);
if (selectedValue != null &&
selectedValue.Trim() != "" &&
!MiscUtils.SetSelectedValue(this.UOMID, selectedValue) &&
!MiscUtils.SetSelectedDisplayText(this.UOMID, selectedValue))
{
// construct a whereclause to query a record with UOM.UOMID = selectedValue
CompoundFilter filter2 = new CompoundFilter(CompoundFilter.CompoundingOperators.And_Operator, null);
WhereClause whereClause2 = new WhereClause();
filter2.AddFilter(new BaseClasses.Data.ColumnValueFilter(UOMTable.UOMID, selectedValue, BaseClasses.Data.BaseFilter.ComparisonOperator.EqualsTo, false));
whereClause2.AddFilter(filter2, CompoundFilter.CompoundingOperators.And_Operator);
// Execute the query
try
{
UOMRecord[] rc = UOMTable.GetRecords(whereClause2, new OrderBy(false, false), 0, 1);
System.Collections.Generic.IDictionary<string, object> vars = new System.Collections.Generic.Dictionary<string, object> ();
// if find a record, add it to the dropdown and set it as selected item
if (rc != null && rc.Length == 1)
{
string fvalue = ScopeTable.UOMID.Format(selectedValue);
ListItem item = new ListItem(fvalue, selectedValue);
item.Selected = true;
this.UOMID.Items.Add(item);
}
}
catch
{
}
}
}
}
/// <summary>
/// 返回数量
/// </summary>
/// <returns></returns>
public int GetCount()
{
List <JoinRelation> tables = new List <JoinRelation>();
SqlBuilder.AppendFormat(" SELECT COUNT(1) AS CT FROM {0}", JoinRelations[0].LeftTable);
if (WithAlias)
{
SqlBuilder.AppendFormat(" AS {0}", JoinRelations[0].LeftTableAlias);
}
foreach (JoinRelation j in JoinRelations)
{
if (j.JoinType == JoinType.Outer)
{
SqlBuilder.Append(" LEFT OUTER JOIN ");
}
else
{
SqlBuilder.Append(" INNER JOIN ");
}
if (WithAlias)
{
if (tables.Count(m => m.LeftTableAlias == j.RightTableAlias || m.RightTableAlias == j.RightTableAlias) == 0)
{
SqlBuilder.AppendFormat("{0} AS {1}", j.RightTable, j.RightTableAlias);
tables.Add(j);
}
else
{
SqlBuilder.AppendFormat("{0} AS {1}", j.LeftTable, j.LeftTableAlias);
tables.Add(j);
}
}
else
{
if (tables.Count(m => m.LeftTable == j.RightTable || m.RightTable == j.RightTable) == 0)
{
SqlBuilder.Append(j.RightTable);
tables.Add(j);
}
else
{
SqlBuilder.Append(j.LeftTable);
tables.Add(j);
}
}
SqlBuilder.AppendFormat(" ON {0}", j.OnSql.TrimEnd("AND".ToCharArray()));
}
if (WhereClause.Length > 0)
{
SqlBuilder.AppendFormat(" WHERE {0}", WhereClause.ToString().Trim().TrimEnd("AND".ToCharArray()));
}
if (GroupByFields.Length > 0)
{
SqlBuilder.AppendFormat(" GROUP BY {0}", GroupByFields.ToString().Trim().TrimEnd(','));
}
if (OrderByFields.Length > 0)
{
SqlBuilder.AppendFormat(" ORDER BY {0}", OrderByFields.ToString().Trim().TrimEnd(','));
}
//开始组装sql
int result = DbContext.GetSingle <int>(SqlBuilder.ToString(), Parameters.ToArray());
if (this.IsAutoDisposeDbContext)
{
DbContext.Dispose();
}
return(result);
}
public virtual WhereClause CreateWhereClause(String searchText, String fromSearchControl, String AutoTypeAheadSearch, String AutoTypeAheadWordSeparators)
{
// This CreateWhereClause is used for loading list of suggestions for Auto Type-Ahead feature.
UOMTable.Instance.InnerFilter = null;
WhereClause wc = new WhereClause();
// Compose the WHERE clause consiting of:
// 1. Static clause defined at design time.
// 2. User selected search criteria.
// 3. User selected filter criteria.
String appRelativeVirtualPath = (String)HttpContext.Current.Session["AppRelativeVirtualPath"];
// Adds clauses if values are selected in Filter controls which are configured in the page.
String UOMDescriptionFilterSelectedValue = (String)HttpContext.Current.Session[HttpContext.Current.Session.SessionID + appRelativeVirtualPath + "UOMDescriptionFilter_Ajax"];
if (MiscUtils.IsValueSelected(UOMDescriptionFilterSelectedValue)) {
wc.iAND(UOMTable.UOMDescription, BaseFilter.ComparisonOperator.EqualsTo, UOMDescriptionFilterSelectedValue, false, false);
}
String UOMNameFilterSelectedValue = (String)HttpContext.Current.Session[HttpContext.Current.Session.SessionID + appRelativeVirtualPath + "UOMNameFilter_Ajax"];
if (MiscUtils.IsValueSelected(UOMNameFilterSelectedValue)) {
wc.iAND(UOMTable.UOMName, BaseFilter.ComparisonOperator.EqualsTo, UOMNameFilterSelectedValue, false, false);
}
if (MiscUtils.IsValueSelected(searchText) && fromSearchControl == "UOMSearch") {
String formatedSearchText = searchText;
// strip "..." from begin and ending of the search text, otherwise the search will return 0 values as in database "..." is not stored.
if (searchText.StartsWith("...")) {
formatedSearchText = searchText.Substring(3,searchText.Length-3);
}
if (searchText.EndsWith("...")) {
formatedSearchText = searchText.Substring(0,searchText.Length-3);
}
// After stripping "...", trim any leading and trailing whitespaces
formatedSearchText = formatedSearchText.Trim();
// After stripping "..." see if the search text is null or empty.
if (MiscUtils.IsValueSelected(searchText)) {
// These clauses are added depending on operator and fields selected in Control's property page, bindings tab.
WhereClause search = new WhereClause();
if (StringUtils.InvariantLCase(AutoTypeAheadSearch).Equals("wordsstartingwithsearchstring")) {
search.iOR(UOMTable.UOMName, BaseFilter.ComparisonOperator.Starts_With, formatedSearchText, true, false);
search.iOR(UOMTable.UOMName, BaseFilter.ComparisonOperator.Contains, AutoTypeAheadWordSeparators + formatedSearchText, true, false);
search.iOR(UOMTable.UOMDescription, BaseFilter.ComparisonOperator.Starts_With, formatedSearchText, true, false);
search.iOR(UOMTable.UOMDescription, BaseFilter.ComparisonOperator.Contains, AutoTypeAheadWordSeparators + formatedSearchText, true, false);
} else {
search.iOR(UOMTable.UOMName, BaseFilter.ComparisonOperator.Contains, formatedSearchText, true, false);
search.iOR(UOMTable.UOMDescription, BaseFilter.ComparisonOperator.Contains, formatedSearchText, true, false);
}
wc.iAND(search);
}
}
return wc;
}
private bool TryApplyWhereClause(WhereClause clause)
{
if (SkipCount.HasValue || TakeCount.HasValue)
return false;
if (_processedClauses.Any() && !(_processedClauses.Peek() is WhereClause))
return false;
Criteria = Criteria == null
? clause.Criteria
: new SimpleExpression(Criteria, clause.Criteria, SimpleExpressionType.And);
return true;
}
/// <summary>
/// Renders the begining of a HAVING statement
/// </summary>
/// <param name="builder"></param>
/// <param name="group"></param>
protected virtual void Having(StringBuilder builder, WhereClause group)
{
if (group.IsEmpty)
{
return;
}
builder.AppendFormat(" having ");
}
public TearOffWhereParameter(WhereClause clause)
{
this._clause = clause;
}
/// <summary>
/// 多表连查分页
/// </summary>
/// <typeparam name="TModel"></typeparam>
/// <param name="page"></param>
/// <returns></returns>
public PageDataSource <TModel> GetPage <TModel>(PageFilter page)
{
List <JoinRelation> tables = new List <JoinRelation>();
SqlBuilder.AppendFormat(" SELECT {0} FROM {1}", SelectFields.ToString().TrimEnd(','), JoinRelations[0].LeftTable);
if (WithAlias)
{
SqlBuilder.AppendFormat(" AS {0}", JoinRelations[0].LeftTableAlias);
}
foreach (JoinRelation j in JoinRelations)
{
if (j.JoinType == JoinType.Outer)
{
SqlBuilder.Append(" LEFT OUTER JOIN ");
}
else
{
SqlBuilder.Append(" INNER JOIN ");
}
if (WithAlias)
{
if (tables.Count(m => m.LeftTableAlias == j.RightTableAlias || m.RightTableAlias == j.RightTableAlias) == 0)
{
SqlBuilder.AppendFormat("{0} AS {1}", j.RightTable, j.RightTableAlias);
tables.Add(j);
}
else
{
SqlBuilder.AppendFormat("{0} AS {1}", j.LeftTable, j.LeftTableAlias);
tables.Add(j);
}
}
else
{
if (tables.Count(m => m.LeftTable == j.RightTable || m.RightTable == j.RightTable) == 0)
{
SqlBuilder.Append(j.RightTable);
tables.Add(j);
}
else
{
SqlBuilder.Append(j.LeftTable);
tables.Add(j);
}
}
SqlBuilder.AppendFormat(" ON {0}", j.OnSql.TrimEnd("AND".ToCharArray()));
}
if (WhereClause.Length > 0)
{
SqlBuilder.AppendFormat(" WHERE {0}", WhereClause.ToString().Trim().TrimEnd("AND".ToCharArray()));
}
if (GroupByFields.Length > 0)
{
SqlBuilder.AppendFormat(" GROUP BY {0}", GroupByFields.ToString().Trim().TrimEnd(','));
}
if (OrderByFields.Length > 0)
{
page.OrderText = OrderByFields.ToString().Trim().TrimEnd(',');
}
//开始组装sql
//开始组装sql
PageDataSource <TModel> result = DbContext.GetPage <TModel>(SqlBuilder.ToString(), page, Parameters.ToArray());
if (this.IsAutoDisposeDbContext)
{
DbContext.Dispose();
}
return(result);
}
protected PostgresUpdateBase(IServiceProvider serviceProvider, UpdateClause updateClause, FromClause fromClause, JoinClause joinClause, WhereClause whereClause)
: base(serviceProvider)
{
this.updateClause = updateClause;
this.fromClause = fromClause;
this.joinClause = joinClause;
this.whereClause = whereClause;
}
public override void FlushData()
{
this._whereClause = null;
this._aggregateClause = null;
base.FlushData();
}
public TearOffWhereParameter(WhereClause clause)
{
this._clause = clause;
}
private static WhereClause GetWhereClause(IDictionary<string, object> whereParameters) {
Debug.Assert((whereParameters != null) && (whereParameters.Count > 0));
WhereClause whereClause = new WhereClause();
whereClause.Parameters = new Dictionary<string, object>(whereParameters.Count);
StringBuilder where = new StringBuilder();
int index = 0;
foreach (KeyValuePair<string, object> parameter in whereParameters) {
string key = parameter.Key;
string value = (parameter.Value == null) ? null : parameter.Value.ToString();
// exclude null and empty values.
if (!(String.IsNullOrEmpty(key) || String.IsNullOrEmpty(value))) {
string newKey = "@p" + index++;
if (where.Length > 0) {
where.Append(" AND ");
}
where.Append(key);
where.Append(" == ");
where.Append(newKey);
whereClause.Parameters.Add(newKey, parameter.Value);
}
}
whereClause.Expression = where.ToString();
return whereClause;
}
public static int ExecuteUpdateParams(this IDbConnection connection, string tableName, WhereClause whereClause, params ColumnValue[] values)
{
return(SqlDialect.ExecuteUpdateParams(connection, tableName, new[] { whereClause }, values));
}
