private void RemoveFromSelect(EntityProperty property)
{
if (SelectFields.Contains(property))
{
SelectFields.Remove(property);
}
}
override public void Finish()
{
foreach (object obj in SyntaxList)
{
if (obj is SelectField)
{
SelectField selectField = obj as SelectField;
if (selectField.BetweenRecord)
{
this.Begin = selectField.Begin;
this.End = selectField.End;
}
SelectFields.Add(selectField);
}
else if (obj is SelectFrom)
{
SelectFroms.Add(obj as SelectFrom);
}
else if (obj is OrderBy)
{
OrderBys.Add(obj as OrderBy);
}
else if (obj is Where)
{
Where = obj as Where;
}
}
}
/// <summary>
/// 清除
/// </summary>
public void Clear()
{
SelectFields.Clear();
Sql.Clear();
DbParams.Clear();
_dicTableName.Clear();
_queueEnglishWords = new Queue <string>(S_listEnglishWords);
}
private void Clear()
{
SqlBuilder.Clear();
JoinRelations.Clear();
WhereClause.Clear();
Parameters.Clear();
OrderByFields.Clear();
GroupByFields.Clear();
SelectFields.Clear();
}
private EntityProperty GetMemberByName(string memberName)
{
var property = SelectFields.FirstOrDefault(p => p.Property.Equals(memberName));
if (property != null)
{
return(property);
}
property = InsertFields.FirstOrDefault(p => p.Property.Equals(memberName));
return(property ?? UpdateFields.FirstOrDefault(p => p.Property.Equals(memberName)));
}
protected override IAsyncResult BeginExecute(AsyncCodeActivityContext context,
AsyncCallback callback, object state)
{
var currentScope = context.GetValue(Scope);
var asyncExecute = new AsyncExecuteWMIDelegate(ExecuteWMI);
context.UserState = asyncExecute;
_selectFields = SelectFields.Get(context);
HideExc = HideException.Get(context);
return(asyncExecute.BeginInvoke(Target, Where, currentScope, callback, state));
}
/// <summary>
/// 获取选择动作
/// </summary>
/// <returns></returns>
private string GetSelectVerb()
{
string selectFields;
if (SelectFields.IsEmpty())
{
selectFields = "*";
}
else
{
selectFields = string.Join(Chars.逗号, SelectFields);
}
return($"select {selectFields} from");
}
/// <summary>
/// 添加查询
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="select"></param>
/// <returns></returns>
public JoinQuery Fields <TEntity>(Expression <Func <TEntity, dynamic> > select = null)
{
EntityInfo entityInfo = Caches.EntityInfoCache.Get(typeof(TEntity));
if (select == null)
{
SelectFields.AppendFormat("{0},", entityInfo.SelectFields);
}
else
{
SqlVisitor visitor = new SqlVisitor(DbContext.DataBase.DBType, callIndex++, WithAlias);
string fields = visitor.Translate(select);
SelectFields.Append(fields);
Parameters.AddRange(visitor.Parameters);
}
return(this);
}
private void InitQRCode()
{
if (SelectFields != null && SelectFields.Count > 0)
{
foreach (string item in lstFields.ItemsSource as List <string> )
{
string field = item.Replace(" ", "");
if (SelectFields.Contains(field))
{
lstFields.SelectedItems.Add(item);
}
}
ImageSourceConverter imageSourceConverter = new ImageSourceConverter();
imgQRcode.Source = (ImageSource)imageSourceConverter.ConvertFrom(QRImageSource);
}
}
private void Add(EntityProperty property)
{
if (property.IsKey)
{
KeyField = property;
}
if (property.IsSelectable)
{
SelectFields.Add(property);
}
if (property.IsInsertable)
{
InsertFields.Add(property);
}
if (property.IsUpdatable)
{
UpdateFields.Add(property);
}
}
/// <summary>
/// Returns the Statement for the <see cref="SelectDbOperation"/>.
/// </summary>
/// <returns>A string containing the select statement</returns>
public override string GetStatement()
{
// Make sure it's ok to run the statement
this.PreStatementValidation();
// For performance issues, use the String Builder Class
StringBuilder sbStatement = new StringBuilder();
#region Select
// Display Fields
StringBuilder sbSelect = new StringBuilder("SELECT ");
if (Distinct)
{
sbSelect.Append("DISTINCT ");
}
// Check all Non-Aggregate Fields
if (SelectFields.Count == 0)
{
// Select All Fields
sbSelect.Append('*');
sbSelect.AppendLine();
}
else
{
// For formatting purposes, add a line right after the select
sbSelect.AppendLine();
// Select Specific Fields
sbSelect.AppendLine(String.Join(",\r\n", from field in SelectFields
select String.Concat(String.Empty.PadLeft(7, ' '),
field.Type == FieldTypes.Display ?
Dialect.FormatField((DisplayField)field) : Dialect.FormatField((AggregateField)field))));
}
#endregion Select
#region Select Aggregates
//// Check all Aggregate Fields
//if (SelectFields.Count(field => field.Type == FieldTypes.Aggregate) > 0)
//{
// sbSelect.AppendLine(String.Join(",\r\n", from field in SelectFields
// where field.Type == FieldTypes.Aggregate
// select String.Concat(String.Empty.PadLeft(7, ' '), Dialect.FormatField((AggregateField)field))));
//}
#endregion Select Aggregates
#region From
// From Statement
sbSelect.Append(" FROM ");
if (base.Tables == null)
{
throw new NullReferenceException($"Tables are not defined for the {nameof(SelectDbOperation)}.");
}
if (base.Tables.Count == 0)
{
throw new Exception($"There are no tables assigned for the {nameof(SelectDbOperation)}.");
}
if (base.Tables.Count == 1)
{
// Append Single Table
sbSelect.AppendLine(base.Dialect.FormatTable(base.Tables[0]));
}
else
{
// Append Multiple Tables Separated by a Comma
sbSelect.AppendLine();
sbSelect.AppendLine(String.Join(",\r\n", from tbl in base.Tables
select String.Concat(String.Empty.PadLeft(7, ' '), Dialect.FormatTable(tbl))));
}
#endregion From
#region Joins
//TODO: Implement SelectDbOperation -> Joins
#endregion Joins
#region Where
//TODO: Implement SelectDbOperation -> Where
// Add Filters, if needed
if (Where?.Count > 0)
{
sbSelect.AppendLine(" WHERE ");
}
#endregion Where
#region Group By
// Group By Fields
if (GroupBy)
{
sbSelect.AppendLine("GROUP BY");
if (SelectFields?.Count(field => field.Type == FieldTypes.Display) > 0)
{
// Add all display fields but make sure to not have the alternate name on it
sbSelect.AppendLine(String.Join(",\r\n", from field in SelectFields
where field.Type == FieldTypes.Display
select String.Concat(String.Empty.PadLeft(9, ' '), Dialect.FormatField(new DisplayField(field.Name, field.TableAlias)))));
}
}
#endregion Group By
#region Having
//TODO: Implement SelectDbOperation -> Having
#endregion Having
// Return the Final Statement
return(sbSelect.ToString());
}
public FluentQuery Select(params object[] fields)
{
SelectFields.AddRange(fields);
return(this);
}
/// <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);
}
public IQueryBuilder AddSelect(string fieldName, string asName = null)
{
SelectFields.Add(SelectNameAs(fieldName, asName));
return(this);
}
public FluentQuery Execute(Action <IEnumerable <AssetClassBase> > successCallback = null)
{
if (OnSuccess == null && successCallback == null)
{
throw new NullReferenceException("Must specify the OnSuccess callback before calling Execute or pass it directly to Execute as a parameter");
}
try
{
var attributes = new List <IAttributeDefinition>();
if (SelectFields.Count > 0)
{
attributes.AddRange(
SelectFields.Select(
m => MetaModelProvider.Meta.GetAttributeDefinition(AssetTypeName
+ "." + m.ToString())));
}
RawQuery.Selection.AddRange(attributes);
if (WhereCriteria.Count > 0)
{
var andTerms = new List <FilterTerm>();
foreach (var tuple in WhereCriteria)
{
var attribute = MetaModelProvider.Meta.GetAttributeDefinition(AssetTypeName
+ "." + tuple.Item1);
var item = tuple.Item2;
var term = new FilterTerm(attribute);
term.Operate(tuple.Item3, item);
andTerms.Add(term);
}
var andTerm = new AndFilterTerm(andTerms.ToArray());
RawQuery.Filter = andTerm;
}
var list = new List <AssetClassBase>();
var result = ServicesProvider.Services.Retrieve(RawQuery);
if (result.Assets.Count == 0)
{
OnSuccess(list);
}
list.AddRange(result.Assets.Select(a => new AssetClassBase(a, AssetTypeName)));
if (list.Count == 0 && OnEmptyResults != null)
{
OnEmptyResults();
}
if (successCallback != null)
{
successCallback(list);
}
else
{
OnSuccess(list);
}
}
catch (Exception exception)
{
if (OnError != null)
{
OnError(exception);
}
else
{
throw;
}
}
return(this);
}
public override string RenderString()
{
var result = new StringBuilder();
result.Append(DefaultSqlSelect + " ");
// fields
if (SelectFields.Count > 0)
{
result.Append(string.Join(", ", SelectFields.ToArray()));
}
else
{
result.Append("*");
}
// table
result.Append(" " + DefaultSqlFrom + " " + TableName);
// joins
if (QueryJoins.Count > 0)
{
foreach (QueryJoin qj in QueryJoins)
{
switch (qj.JoinType)
{
default:
case QueryJoinType.Inner:
result.Append(DefaultInnerJoin);
break;
case QueryJoinType.Cross:
result.Append(DefaultCrossJoin);
break;
case QueryJoinType.OutterLeft:
result.Append(DefaultLeftJoin);
break;
case QueryJoinType.OutterRight:
result.Append(DefaultRightJoin);
break;
}
result.Append(" " + DefaultSqlJoin + " " + qj.TableName);
if (qj.JoinConditions.Count > 0)
{
result.Append(DefaultSqlJoinOn + " ");
result.Append(string.Join(DefaultSqlAnd, qj.JoinConditions.ToArray()));
}
}
}
// conditions
if (Conditions.Count > 0)
{
result.Append(" " + DefaultSqlWhere + " ");
result.Append(string.Join(DefaultSqlAnd, Conditions.ToArray()));
}
return(result.ToString());
}
public void Process()
{
string _where, _dateField, _sql, _str;
string _stage = "";
List <string> _xmlFields = new List <string>();
Dictionary <int, Dictionary <string, string> > _results;
try
{
//
_stage = "Preparing WHERE clause";
_where = String.IsNullOrEmpty(SelectCondition) ? "" : " and (" + SelectCondition + ")";
_where += String.IsNullOrEmpty(AlarmField) ? "" : $" and {AlarmField} in (select valor_alarma from det_alarmas where codigo='{Code}')";
// Changes the date column name from xfec to fecha when true
_dateField = ColumnDate ? "fecha" : "xfec";
//
_stage = "Identify XML fields";
var _fields = SelectFields.Split(",");
foreach (string _field in _fields)
{
if (_field.Contains("\\XML"))
{
_xmlFields.Add(_field.Replace("\\XML", ""));
}
}
if (_xmlFields.Count > 0)
{
SelectFields = SelectFields.Replace("\\XML", "");
}
//
_stage = "Building SQL";
_sql = $"select {SelectFields},Fecha=convert(varchar(17),{_dateField},113), Registro={IdRegName} from {DB}..{Table} ";
// If it is a "flagged" alarm, it has to take into consideration the REPORTED flag in the table, not the idreg value.
if (!Flagged)
{
_sql += $"where {IdRegName}>{(int)(IdRegValue == null ? 0 : IdRegValue)} {_where}";
}
else
{
_sql += $"where GENERAL.dbo.checkFlag(flags,'REPORTED')=0 {_where}";
}
_sql += $" order by {_dateField}";
//
// Server change (for future improvement)
// if (String.IsNullOrEmpty(Server)) ...
//
_stage = "Executing query";
Recordset _rs = new Recordset(_sql, DA);
_rs.Open();
// If no results, the alarm was not triggered
Triggered = !_rs.EOF;
if (!Triggered)
{
return;
}
//
_stage = "Converting data to dictionary";
_results = null;// cDBt.ToDictionary();
//
_stage = "Building contents from results";
Contents = $"<table><tr><th colspan=2>Alarm {Code} triggered by the following values:</th></tr> ";
while (!_rs.EOF)
{
foreach (var _field in _rs.Fields)
{
if (_xmlFields.Contains(_field))
{
XmlDocument _xml = new XmlDocument();
_xml.LoadXml(_rs[_field].ToString());
_str = XMLToString(_xml, 0, false);
}
{
_str = _rs[_field].ToString();
}
Contents += $"<tr><td align=\"right\" valign=\"top\">{_field}: </td><td>{_str}</td></tr>";
}
Contents += "<tr><td colspan=2 align=\"center\">-------------------------------</td></tr>";
_rs.MoveNext();
}
using (SP _sp = new SP(DA, "pControl_Alarmas"))
{
string _msg = "", _warningMsg = "";
_sp.AddParameterValues("msg", _msg);
_sp.AddParameterValues("msg_aviso", _warningMsg);
_sp.AddParameterValues("codigo", Code);
_sp.AddParameterValues("resultado", Contents);
_sp.AddParameterValues("idreg", IdRegValue);
_sp.Execute();
_msg = _sp.Msg;
}
}
catch (Exception ex)
{
Error = true;
string _errorMessage = $"[{this.GetType().Name}/{System.Reflection.MethodBase.GetCurrentMethod().Name}#{_stage}] {ex.Message}";
// Match the last occurrence of [xxx#xxx] to ensure it's part of our error message, not part of the system error. This is to show the error message in bold.
Match _match = Regex.Match(_errorMessage, @"\[([^\[]*)#([^\[]*)\]", RegexOptions.RightToLeft);
int _i = _match.Index + _match.Length;
// Set to bold the error message, ignoring all the "call stack" string, and prepare the html code.
Contents = _errorMessage.Substring(0, _i) + "<ul><strong>" + _errorMessage.Substring(_i + 1);
Contents = $"<html><body>Alarm: {Code}<br>Error:<br>" + Contents.Replace("] ", "]<ul>") + "</strong></body></html>";
}
return;
}
new protected string GetSelectFrom()
{
return($"SELECT {GetDistinct()} {SpecialAction}({SelectFields.GetFieldNames()}) FROM {MainTable}");
}
new protected string GetSelectFrom()
{
return($"SELECT {GetDistinct()} {SelectFields.GetFieldNames()} {GetINTO()} FROM {MainTable}");
}
