public bool AppendExpression(ref SQLExpression exp, PXGraph graph, BqlCommandInfo info, BqlCommand.Selection selection)
{
bool status = true;
SQLExpression userID = null;
status &= BqlCommand.AppendExpression <UserID>(ref userID, graph, info, selection, ref _operand);
if (graph == null || !info.BuildExpression)
{
return(status);
}
SimpleTable epEmployee = new SimpleTable <EPEmployee>(EMPLOYEERALIAS);
exp = exp.In(new Query()
.Select <EPWingman.employeeID>().From <EPWingman>()
.InnerJoin(epEmployee)
.On(new Column <EPEmployee.bAccountID>(epEmployee).EQ(new Column(typeof(EPWingman.wingmanID)))
.And(new Column <EPEmployee.userID>(epEmployee).EQ(userID)))
.Where(new SQLConst(1)
.EQ(new SQLConst(1))));
return(status);
}
protected virtual List <Type> BuildSourceFields(PXCache cache)
{
var exp = SQLExpression.None();
var fields = new List <Type>();
if (BranchSourceFormula != null)
{
BranchSourceFormula.AppendExpression(ref exp, cache.Graph,
new BqlCommandInfo(false)
{
Fields = fields, BuildExpression = false
},
new BqlCommand.Selection());
}
if (BranchSourceType != null)
{
fields.Add(BranchSourceType);
}
if (OrganizationSourceType != null)
{
fields.Add(OrganizationSourceType);
}
return(fields);
}
public bool AppendExpression(ref SQLExpression exp, PXGraph graph, BqlCommandInfo info, BqlCommand.Selection selection)
{
bool status = true;
SQLExpression userID = null;
status &= BqlCommand.AppendExpression <UserID>(ref userID, graph, info, selection, ref _operand);
if (graph == null)
{
return(status);
}
exp.VerifyComparisonExpression();
SimpleTable epEmployee = new SimpleTable <EPEmployee>(EMPLOYEERALIAS);
exp.SetOper(SQLExpression.Operation.IN);
exp = exp.LExpr().In(new Query()
.Select <EPWingman.employeeID>().From <EPWingman>()
.InnerJoin(epEmployee)
.On(new Column <EPEmployee.bAccountID>(epEmployee).Equal(new Column(typeof(EPWingman.wingmanID)))
.And(new Column <EPEmployee.userID>(epEmployee).Equal(userID)))
.Where(PX.Data.SQLTree.Constant.SQLConstant(1)
.Equal(PX.Data.SQLTree.Constant.SQLConstant(1))));
return(status);
}
public bool AppendExpression(ref SQLExpression exp, PXGraph graph, BqlCommandInfo info, BqlCommand.Selection selection)
{
bool status = true;
SQLExpression userID1 = null;
status &= BqlCommand.AppendExpression <UserID>(ref userID1, graph, info, selection, ref _operand);
SQLExpression userID2 = null;
status &= BqlCommand.AppendExpression <UserID>(ref userID2, graph, info, selection, ref _operand);
if (graph == null || !info.BuildExpression)
{
return(status);
}
exp = exp.In(new Query()
.Select <EPApproval.refNoteID>()
.From <EPApproval>()
.Where(typeof(EPApproval.ownerID).EQ(userID1)
.Or(typeof(EPApproval.workgroupID)
.In(new Query()
.Select <EPCompanyTreeH.workGroupID>()
.From <EPCompanyTreeH>()
.Join <EPCompanyTreeMember>()
.On(typeof(EPCompanyTreeH.parentWGID).EQ(typeof(EPCompanyTreeMember.workGroupID))
.And(typeof(EPCompanyTreeH.parentWGID).NE(typeof(EPCompanyTreeH.workGroupID)))
.And(typeof(EPCompanyTreeMember.active).EQ(true))
.And(typeof(EPCompanyTreeMember.userID).EQ(userID2)))
.Where(new SQLConst(1)
.EQ(new SQLConst(1)))))));
return(status);
}
public bool AppendExpression(ref SQLExpression exp, PXGraph graph, BqlCommandInfo info, BqlCommand.Selection selection)
{
bool status = true;
status &= _where.AppendExpression(ref exp, graph, info, selection);
return(status);
}
public virtual bool AppendExpression(ref SQLExpression exp, PXGraph graph, BqlCommandInfo info, BqlCommand.Selection selection)
{
bool status = true;
if (info.Fields is BqlCommand.EqualityList list)
{
list.NonStrict = true;
}
SQLExpression opUser = null;
if (!typeof(IBqlCreator).IsAssignableFrom(typeof(OperandUser)))
{
if (info.BuildExpression)
{
opUser = BqlCommand.GetSingleExpression(typeof(OperandUser), graph, info.Tables, selection, BqlCommand.FieldPlace.Condition);
}
info.Fields?.Add(typeof(OperandUser));
}
else
{
if (_operandUser == null)
{
_operandUser = _operandUser.createOperand <OperandUser>();
}
status &= _operandUser.AppendExpression(ref opUser, graph, info, selection);
}
Query qin = new Query();
qin[typeof(EPCompanyTreeH.workGroupID)].From(typeof(EPCompanyTreeH))
.Join(typeof(EPCompanyTreeMember))
.On(SQLExpression.EQ(typeof(EPCompanyTreeH.parentWGID), typeof(EPCompanyTreeMember.workGroupID))
//.And(Column.SQLColumn(typeof(EPCompanyTreeH.parentWGID)).NotEqual(Column.SQLColumn(typeof(EPCompanyTreeH.workGroupID)))
.And(Column.SQLColumn(typeof(EPCompanyTreeMember.active)).EQ(1))
.And(Column.SQLColumn(typeof(EPCompanyTreeMember.userID)).EQ(opUser)))
//)
.Where(new SQLConst(1).EQ(1));
Query qout = new Query();
//Append Tail removes main object, so we fieldNote will not be mapped. Skipping conditions for AppendTail
if (info.Tables == null || info.Tables.Count <= 0 || info.Tables.Contains(BqlCommand.GetItemType <FieldNote>()))
{
qout[typeof(EntityWorkgroup.refNoteID)].From(typeof(EntityWorkgroup))
.Where(Column.SQLColumn(typeof(EntityWorkgroup.workGroupID)).In(qin)
.And(SQLExpression.EQ(typeof(EntityWorkgroup.refNoteID), typeof(FieldNote))));
}
else
{
qout[typeof(EntityWorkgroup.refNoteID)].From(typeof(EntityWorkgroup))
.Where(Column.SQLColumn(typeof(EntityWorkgroup.workGroupID)).In(qin));
}
qout.Limit(-1); // prevent limiting of IN subqueries
exp = exp.In(qout);
return(status);
}
/// <summary>
/// Method adds a parameter to SQLExpression. This is necessary for correct work with the platform cache,
/// otherwise you can get the result from the cache, even if SOOrder.branchId (or your Parameter) has changed.
/// </summary>
/// <returns>currentExpression and the new expression that contains the parameter, the last expression is alwaya true.</returns>
private SQLExpression AddParameterExpression(SQLExpression currentExpression, BqlCommandInfo info, BqlCommand.Selection selection, object parameterValue)
{
SQLExpression parameterExpression = Literal.NewParameter(selection.ParamCounter++);
SQLExpression parameterTrueExpression = (parameterValue != null) ? parameterExpression.IsNotNull() : parameterExpression.IsNull();
info.Parameters?.Add(_parameter);
return(parameterTrueExpression.And(currentExpression).Embrace());
}
public bool AppendExpression(ref SQLExpression exp, PXGraph graph, BqlCommandInfo info, BqlCommand.Selection selection)
{
bool status = true;
if (graph == null || !info.BuildExpression)
{
return(status);
}
if (_parameter == null)
{
_parameter = new Parameter();
}
object val = null;
if (_parameter.HasDefault)
{
Type ft = _parameter.GetReferencedType();
if (ft.IsNested)
{
Type ct = BqlCommand.GetItemType(ft);
PXCache cache = graph.Caches[ct];
if (cache.Current != null)
{
val = cache.GetValue(cache.Current, ft.Name);
}
}
}
SQLExpression fld = BqlCommand.GetSingleExpression(typeof(Field), graph, info.Tables, selection, BqlCommand.FieldPlace.Condition);
exp = fld.IsNull();
List <int> branches = null;
if (val != null)
{
branches = GetSameOrganizationBranches((int?)val);
}
if (branches != null && branches.Count > 0)
{
SQLExpression inn = null;
foreach (int numBranch in branches)
{
if (null == inn)
{
inn = new Data.SQLTree.Constant(numBranch);
}
else
{
inn = inn.Seq(numBranch);
}
}
exp = exp.Or(fld.In(inn)).Embrace();
}
return(status);
}
//for 2019r1
//public bool AppendExpression(ref SQLExpression exp, PXGraph graph, BqlCommandInfo info, BqlCommand.Selection selection)
//{
// if (graph == null || !info.BuildExpression) return true;
// PXMutableCollection.AddMutableItem(this);
// exp = new PX.Data.SQLTree.Constant(graph.Accessinfo.BranchID);
// return true;
//}
//for 2020r1
public bool AppendExpression(ref SQLExpression exp, PXGraph graph, BqlCommandInfo info, BqlCommand.Selection selection)
{
if (graph == null || !info.BuildExpression)
{
return(true);
}
PXMutableCollection.AddMutableItem(this);
exp = new SQLConst(graph.Accessinfo.BranchID);
return(true);
}
public bool AppendExpression(ref SQLExpression exp, PXGraph graph, BqlCommandInfo info, BqlCommand.Selection selection)
{
if (graph == null || !info.BuildExpression)
{
return(true);
}
SQLExpression fld = BqlCommand.GetSingleExpression(typeof(TOrganizationIDField), graph, info.Tables, selection, BqlCommand.FieldPlace.Condition);
int?organizationID = GetOrganizationID(graph);
exp = fld.Equal(organizationID);
return(true);
}
public bool AppendExpression(ref SQLExpression exp, PXGraph graph, BqlCommandInfo info, BqlCommand.Selection selection)
{
info.Fields?.Add(typeof(Field));
return(true);
}
public bool AppendExpression(ref SQLExpression exp, PXGraph graph, BqlCommandInfo info, BqlCommand.Selection selection)
{
throw new NotImplementedException();
}
public override void CommandPreparing(PXCache sender, PXCommandPreparingEventArgs e)
{
if (!_IsActive || _Fields == null || !e.IsSelect())
{
return;
}
PXDBOperation eOp = e.Operation & PXDBOperation.Option;
bool operationAllowsSelectForXml =
eOp == PXDBOperation.External ||
eOp == PXDBOperation.Internal ||
eOp == PXDBOperation.Normal && e.Value == null ||
eOp == PXDBOperation.GroupBy && sender.BqlSelect != null; // it's ok because the subselect will be used inside FROM (...) part without any aggregation
Type tableToUse = eOp == PXDBOperation.External ? sender.GetItemType() : e.Table ?? _BqlTable;
if (!_BqlTable.IsAssignableFrom(sender.BqlTable))
{
if (sender.Graph.Caches[_BqlTable].BqlSelect != null && operationAllowsSelectForXml)
{
e.BqlTable = _BqlTable;
e.Expr = new Column(_DatabaseFieldName, (e.Operation & PXDBOperation.Option) == PXDBOperation.External ? sender.GetItemType() : _BqlTable);
}
else
{
PXCommandPreparingEventArgs.FieldDescription description;
sender.Graph.Caches[_BqlTable].RaiseCommandPreparing(_DatabaseFieldName, e.Row, e.Value, e.Operation, e.Table, out description);
if (description != null)
{
e.DataType = description.DataType;
e.DataValue = description.DataValue;
e.BqlTable = _BqlTable;
e.Expr = description.Expr;
}
}
}
else
{
var dialect = e.SqlDialect;
if (operationAllowsSelectForXml)
{
if (aggregateAttributes && (e.Operation & PXDBOperation.Option) != PXDBOperation.External)
{
List <Type> init = new List <Type>()
{
tableToUse
};
e.BqlTable = tableToUse;
e.DataType = PXDbType.NVarChar;
e.Expr = new SQLTree.SubQuery(GetAttributesJoinedQuery(sender.Graph, init, ((IBqlSearch)_SingleSelect).GetField(), _PureWhere));
return;
}
else
{
e.BqlTable = _BqlTable;
Query q = new Query().Field(new SQLConst(_Fields.Length));
e.Expr = new SubQuery(q);
}
}
else
{
e.Expr = SQLExpression.Null();
}
}
e.DataType = PXDbType.Int;
e.DataLength = 4;
}
protected virtual void AttributeCommandPreparing(PXCache sender, PXCommandPreparingEventArgs e, PXFieldState state, string attributeName, int iField)
{
if (!_IsActive || (e.Operation & PXDBOperation.Command) != PXDBOperation.Select)
{
return;
}
if (!_BqlTable.IsAssignableFrom(sender.BqlTable))
{
if (sender.Graph.Caches[_BqlTable].BqlSelect != null &&
((e.Operation & PXDBOperation.Option) == PXDBOperation.External ||
(e.Operation & PXDBOperation.Option) == PXDBOperation.Normal && e.Value == null))
{
e.Cancel = true;
e.DataType = PXDbType.NVarChar;
e.DataValue = e.Value;
e.BqlTable = _BqlTable;
e.Expr = new Column(state.Name, (e.Operation & PXDBOperation.Option) == PXDBOperation.External ? sender.GetItemType().Name : _BqlTable.Name);
}
else
{
PXCommandPreparingEventArgs.FieldDescription description;
e.Cancel = !sender.Graph.Caches[_BqlTable].RaiseCommandPreparing(state.Name, e.Row, e.Value, e.Operation, e.Table, out description);
if (description != null)
{
e.DataType = description.DataType;
e.DataValue = description.DataValue;
e.BqlTable = _BqlTable;
e.Expr = description.Expr;
}
}
}
else if (((e.Operation & PXDBOperation.Option) == PXDBOperation.External ||
(e.Operation & PXDBOperation.Option) == PXDBOperation.Normal && e.Value == null))
{
e.Cancel = true;
e.DataValue = e.Value;
string sValue = e.Value as string;
if (state.DataType == typeof(bool))
{
e.DataType = PXDbType.Bit;
bool value;
if (sValue != null && bool.TryParse(sValue, out value))
{
e.DataValue = Convert.ToInt32(value).ToString(CultureInfo.InvariantCulture);
}
}
else if (state.DataType == typeof(DateTime))
{
e.DataType = PXDbType.DateTime;
string dateFormat = "yyyy-MM-dd HH:mm:ss.fff";
if (sValue != null && DateTime.TryParse(sValue, CultureInfo.InvariantCulture, DateTimeStyles.None, out var dt))
{
e.DataValue = dt.ToString(dateFormat, CultureInfo.InvariantCulture);
}
if (e.Value is DateTime date)
{
e.DataValue = date.ToString(dateFormat, CultureInfo.InvariantCulture);
}
}
else
{
e.DataType = PXDbType.NVarChar;
}
List <Type> init = new List <Type>();
if ((e.Operation & PXDBOperation.Option) == PXDBOperation.External)
{
init.Add(sender.GetItemType());
}
else if (e.Table != null)
{
init.Add(e.Table);
}
else
{
init.Add(_BqlTable);
}
if (aggregateAttributes && (e.Operation & PXDBOperation.Option) != PXDBOperation.External)
{
e.Expr = null;
}
else
{
var bld = new StringBuilder();
String field = BqlCommand.GetSingleField(((IBqlSearch)_SingleSelect).GetField(), sender.Graph, new List <Type>(_SingleSelect.GetTables()), null, BqlCommand.FieldPlace.Select);
bld.Append(BqlCommand.SubSelect).Append(field);
_SubSelect.Parse(sender.Graph, null, init, null, null, bld, null);
bld.Replace((string)(new AttributeIDPlaceholder().Value), attributeName);
bld.Append(')');
SQLExpression fexp = BqlCommand.GetSingleExpression(((IBqlSearch)_SingleSelect).GetField(), sender.Graph, new List <Type>(_SingleSelect.GetTables()), null, BqlCommand.FieldPlace.Select);
Query q = _SubSelect.GetQueryInternal(sender.Graph, new BqlCommandInfo(false)
{
Tables = init
}, null);
q.ClearSelection();
q.Field(fexp);
q.GetWhere().substituteConstant((string)(new AttributeIDPlaceholder().Value), attributeName);
var stringState = state as PXStringState;
if (stringState != null && stringState.MultiSelect)
{
e.Expr = new SQLTree.Constant(",").Concat(new SubQuery(q)).Concat(new SQLTree.Constant(","));
}
else if (state?.DataType == typeof(bool))
{
e.Expr = new SQLExpression(SQLExpression.Operation.ISNULL_FUNC).SetLeft(new SubQuery(q)).SetRight(SQLTree.Constant.SQLConstant(0));
}
else
{
e.Expr = new SubQuery(q);
}
}
e.BqlTable = _BqlTable;
}
else
{
e.Expr = null;
}
}
public bool AppendExpression(ref SQLExpression exp, PXGraph graph, BqlCommandInfo info, BqlCommand.Selection selection)
{
return(true);
}
public bool AppendExpression(ref SQLExpression exp, PXGraph graph, BqlCommandInfo info, BqlCommand.Selection selection) => Where.AppendExpression(ref exp, graph, info, selection);
private bool InSQLExpression(out SQLExpression expression)
{
expression = this.sqlStringHandler.Expressions.FirstOrDefault(
item => item.StartIndex <= SQLIndex && item.EndIndex >= SQLIndex);
return(null != expression);
}
