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);
}
public void Verify(PXCache cache, object item, List <object> pars, ref bool?result, ref object value)
{
object val = null;
if (_parameter == null)
{
_parameter = new Parameter();
}
if (_parameter.HasDefault)
{
Type ft = _parameter.GetReferencedType();
if (ft.IsNested)
{
Type ct = BqlCommand.GetItemType(ft);
PXCache paramcache = cache.Graph.Caches[ct];
if (paramcache.Current != null)
{
val = paramcache.GetValue(paramcache.Current, ft.Name);
}
}
}
if (typeof(IBqlField).IsAssignableFrom(typeof(Field)))
{
if ((cache.GetItemType() == BqlCommand.GetItemType(typeof(Field)) || BqlCommand.GetItemType(typeof(Field)).IsAssignableFrom(cache.GetItemType())))
{
value = cache.GetValue(item, typeof(Field).Name);
}
else
{
value = null;
}
}
else
{
throw new PXArgumentException("Operand", ErrorMessages.OperandNotClassFieldAndNotIBqlCreator);
}
List <int> branches = null;
if (val != null)
{
branches = GetSameOrganizationBranches((int?)val);
}
if (branches != null && branches.Count > 0 && value != null)
{
result = branches.Contains((int)value);
}
}
public void Parse(PXGraph graph, List <IBqlParameter> pars, List <Type> tables, List <Type> fields, List <IBqlSortColumn> sortColumns, StringBuilder text, BqlCommand.Selection selection)
{
if (graph != null && text != null)
{
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);
}
}
}
String toAppend = BqlCommand.GetSingleField(typeof(Field), graph, tables, selection, BqlCommand.FieldPlace.Condition);
text.Append("(").Append(toAppend).Append(" IS NULL ");
List <int> branches = null;
if (val != null)
{
branches = GetSameOrganizationBranches((int?)val);
}
if (branches != null && branches.Count > 0)
{
text.Append(" OR ").Append(toAppend).Append(" IN (");
foreach (int numBranch in branches)
{
text.Append(numBranch).Append(",");
}
text.Remove(text.Length - 1, 1);
text.Append(")");
}
text.Append(")");
}
}
public static object GetParameterValue(PXGraph graph, IBqlParameter parameter)
{
if (parameter.HasDefault)
{
Type ft = parameter.GetReferencedType();
if (ft.IsNested)
{
Type ct = BqlCommand.GetItemType(ft);
PXCache paramcache = graph.Caches[ct];
if (paramcache.Current != null)
{
return(paramcache.GetValue(paramcache.Current, ft.Name));
}
}
}
return(null);
}
protected override void Execute()
{
// Bql拿到银行账户主实体及币种明细数据
IBqlExecuter bqlExecuter = ServiceManager.GetService <IBqlExecuter>();
bqlExecuter.Options.OptionType = OptionType.ExecuteSql; // 执行SQL语句
// List<string> refEntityIDsOfBA = new List<string>();
List <IBqlParameter> list = new List <IBqlParameter>();
string bqlOfBA = @"SELECT bfbankaccountitems.ID,bfbankaccountitems.ParentID,bfbankaccountitems.Currency,bfbankaccountitems.AccountType,bfbankaccounttype.name as AccountTypeName,
bfbankaccounts.AccountName,bfbankaccounts.AccountNo,bfbankaccounts.Bank,bfbankaccounts.OpenDate,bfbankaccounts.corporationUnit,
bfcurrency.name_chs as CurrencyName,bfbank.name_chs as BankName,bfadminorganization.name_chs as CorporationUnitName,tmbalanceofbainitialized.initialdate,
tmbalanceofbainitialized.initialbalance,tmbalanceofbainitialized.currentproduct,tmbalanceofbainitialized.conventionalproduct,
tmbalanceofbainitialized.overdraftproduct
FROM bfbankaccountitems
LEFT JOIN bfbankaccounts ON bfbankaccountitems.ParentID = bfbankaccounts.ID
LEFT JOIN bfcurrency ON bfbankaccountitems.currency = bfcurrency.ID
LEFT JOIN bfbank ON bfbankaccounts.bank = bfbank.ID
LEFT JOIN bfbankaccounttype ON bfbankaccounttype.ID = bfbankaccountitems.accounttype
LEFT JOIN bfadminorganization ON bfbankaccounts.corporationUnit = bfadminorganization.ID
LEFT JOIN tmbalanceofbainitialized on tmbalanceofbainitialized.account=bfbankaccounts.id
and tmbalanceofbainitialized.currency=bfbankaccountitems.currency
and tmbalanceofbainitialized.accounttype=bfbankaccountitems.accounttype
Where bfbankaccounttype.name = @accountTypeName and bfbankaccounts.AccountStatus = @accountStatus";
IBqlParameter parameter = bqlExecuter.MakeInParam("accountTypeName", "活期");
IBqlParameter parameter2 = bqlExecuter.MakeInParam("accountStatus", 2);
List <string> refEntityDatas = new List <string>();
StringBuilder sb = new StringBuilder(bqlOfBA);
list.Add(parameter);
list.Add(parameter2);
// 法人单位
if (this.unitId != null && this.unitId.ToString() != "")
{
sb.Append(" and bfbankaccounts.corporationunit = @unitId");
list.Add(bqlExecuter.MakeInParam("unitId", this.unitId));
}
// 申请日期
if (this.beginDate != null && this.beginDate.ToString() != "" && this.beginDate.ToString() != "0001/1/1 0:00:00")
{
sb.Append(" and bfbankaccounts.opendate >= @beginDate");
list.Add(bqlExecuter.MakeInParam("beginDate", this.beginDate));
}
// 至
if (this.endDate != null && this.endDate.ToString() != "" && this.endDate.ToString() != "0001/1/1 0:00:00")
{
sb.Append(" and bfbankaccounts.opendate <= @endDate");
list.Add(bqlExecuter.MakeInParam("endDate", this.endDate));
}
if (this.bankId != null && this.bankId.ToString() != "")
{
sb.Append(" and bfbank.id = @bankId");
list.Add(bqlExecuter.MakeInParam("bankId", this.bankId));
}
// sb.Append(" order by BizDate desc");
IBqlParameter[] bqlParam = list.ToArray();
List <dynamic> resultOfBA = bqlExecuter.ExecuteSelectStatement(sb.ToString(), refEntityDatas, bqlParam);
// List<dynamic> resultOfBA = bqlExecuter.ExecuteSelectStatement(bqlOfBA, refEntityIDsOfBA, new IBqlParameter[] { parameter, parameter2, parameter3 });
if (resultOfBA == null)
{
return;
}
List <IEntityData> voDatas = ConstructVoData(resultOfBA); // 将Bql拿到的数据构造成Vo实体数据
// List<IEntityData> voDatas1 = ConstructVoDataA(resultOfBA);
// Query拿到余额初始化记录数据
var lcpBalance = ServiceManager.GetService <ILcpFactory>().CreateLcp("BalanceOfBAInitialized");
var queryResultOfBalance = lcpBalance.Query(new EntityFilter());
// 将Bql和Query拿到的数据放入上下文
if (voDatas == null && queryResultOfBalance == null)
{
Result = null;
}
else
{
//Dictionary<string, List<IEntityData>> result = new Dictionary<string, List<IEntityData>>();
//result.Add("BalanceOfBAInitialized", queryResultOfBalance);
//result.Add("BankAccountItems", voDatas);
Result = voDatas;
}
}
