public static SelectExpression AddOrderExpression(this SelectExpression select, OrderExpression ordering)
{
List<OrderExpression> orderby = new List<OrderExpression>();
if (select.OrderBy != null)
orderby.AddRange(select.OrderBy);
orderby.Add(ordering);
return select.SetOrderBy(orderby);
}
/// <summary>
/// Creates an OrderExpression from a CrmSortingCriteria object and adds it to the query
/// </summary>
/// <param name="query">The query to which the OrderExpression should be added</param>
/// <param name="sortingCriteria">The sortingCriteria</param>
/// <returns>An OrderExpression</returns>
public static void AddOrderExpression(QueryExpression query, CrmSortingCriteria sortingCriteria)
{
if (sortingCriteria == null)
return;
OrderType orderType = sortingCriteria.SortOrder == CrmSortOrder.Ascending
? OrderType.Ascending
: OrderType.Descending;
OrderExpression orderExpression = new OrderExpression(sortingCriteria.AttributeName, orderType);
query.Orders.Add(orderExpression);
}
public EntityCollection FetchEntity(string name, string[] fields, string fieldToSort)
{
Connect();
QueryExpression qe = new QueryExpression(name);
qe.ColumnSet = new ColumnSet();
qe.ColumnSet.AddColumns(fields);
OrderExpression order = new OrderExpression();
order.AttributeName = fieldToSort;
order.OrderType = OrderType.Ascending;
qe.Orders.Add(order);
return service.RetrieveMultiple(qe);
}
public virtual IQueryState Accept(OrderExpression exp)
{
if (exp.NodeType == QueryExpressionType.OrderBy || exp.NodeType == QueryExpressionType.OrderByDesc)
this._resultElement.OrderSegments.Clear();
var r = VisistOrderExpression(this.MoeList, exp);
if (this._resultElement.IsOrderSegmentsFromSubQuery)
{
this._resultElement.OrderSegments.Clear();
this._resultElement.IsOrderSegmentsFromSubQuery = false;
}
this._resultElement.OrderSegments.Add(r);
return this;
}
public List <T> ListarPor(Guid metadotrimestreid)
{
var query = GetQueryExpression <T>(true);
#region Condições
query.Criteria.AddCondition("statecode", ConditionOperator.Equal, 0);
//query.Criteria.AddCondition("itbc_trimestre", ConditionOperator.Equal, trimestre);
query.Criteria.AddCondition("itbc_metadotrimestreid", ConditionOperator.Equal, metadotrimestreid);
//query.AddLink("itbc_metaportrimestre", "itbc_metadotrimestreid", "itbc_metaportrimestreid")
// .LinkCriteria.AddCondition("itbc_metas", ConditionOperator.Equal, metaunidadeId);
#endregion
#region Ordenações
OrderExpression ord1 = new OrderExpression("itbc_name", OrderType.Ascending);
query.Orders.Add(ord1);
#endregion
return((List <T>) this.RetrieveMultiple(query).List);
}
public T ObterPor(Int32 trimestre, Int32 mes, Int32 ano, Guid produto, Guid canal)
{
var query = GetQueryExpression <T>(true);
#region Condições
ConditionExpression cond1 = new ConditionExpression("itbc_trimestre", Microsoft.Xrm.Sdk.Query.ConditionOperator.Equal, trimestre);
query.Criteria.Conditions.Add(cond1);
ConditionExpression cond2 = new ConditionExpression("itbc_ano", Microsoft.Xrm.Sdk.Query.ConditionOperator.Equal, ano);
query.Criteria.Conditions.Add(cond2);
ConditionExpression cond3 = new ConditionExpression("itbc_product", Microsoft.Xrm.Sdk.Query.ConditionOperator.Equal, produto);
query.Criteria.Conditions.Add(cond3);
ConditionExpression cond4 = new ConditionExpression("statuscode", Microsoft.Xrm.Sdk.Query.ConditionOperator.Equal, 1);
query.Criteria.Conditions.Add(cond4);
ConditionExpression cond5 = new ConditionExpression("itbc_mes", Microsoft.Xrm.Sdk.Query.ConditionOperator.Equal, mes);
query.Criteria.Conditions.Add(cond5);
ConditionExpression cond6 = new ConditionExpression("itbc_account", Microsoft.Xrm.Sdk.Query.ConditionOperator.Equal, canal);
query.Criteria.Conditions.Add(cond6);
#endregion
#region Ordenação
OrderExpression ord1 = new OrderExpression("itbc_name", Microsoft.Xrm.Sdk.Query.OrderType.Ascending);
query.Orders.Add(ord1);
#endregion
var colecao = this.RetrieveMultiple(query);
if (colecao.List.Count == 0)
{
return(default(T));
}
return(colecao.List[0]);
}
public IList <SWBin> GetSWBinsBy(string lotID, string code, string defect, string qty, string failRate, string isPassed, string limited, string orderBy, bool desc, int pageIndex, int pageSize, out int recordCount)
{
Conditions conditions = new Conditions {
ConditionExpressions = { new Condition("LotID", Operator.EqualTo, lotID) }
};
if (!StringHelper.isNullOrEmpty(code))
{
conditions.ConditionExpressions.Add(new Condition("Code", Operator.Like, code));
}
if (!StringHelper.isNullOrEmpty(defect))
{
conditions.ConditionExpressions.Add(new Condition("Defect", Operator.Like, defect));
}
if (!StringHelper.isNullOrEmpty(qty))
{
conditions.ConditionExpressions.Add(new Condition("Qty", Operator.Like, qty));
}
if (!StringHelper.isNullOrEmpty(failRate))
{
conditions.ConditionExpressions.Add(new Condition("FailRate", Operator.Like, failRate));
}
if (!StringHelper.isNullOrEmpty(isPassed))
{
conditions.ConditionExpressions.Add(new Condition("IsPassed", Operator.EqualTo, isPassed));
}
if (!StringHelper.isNullOrEmpty(limited))
{
conditions.ConditionExpressions.Add(new Condition("Limited", Operator.EqualTo, limited));
}
conditions.Connector = Connector.AND;
OrderExpression orderExpression = null;
if (!StringHelper.isNullOrEmpty(orderBy))
{
orderExpression = new OrderExpression(orderBy, desc);
return(this.dbGateway.getRecords(pageIndex, pageSize, conditions, orderExpression, null, Connector.AND, out recordCount));
}
return(this.dbGateway.getRecordsWithCustomerOrderStatement(pageIndex, pageSize, conditions, " order by IsTriggerd,IsPassed", null, Connector.AND, out recordCount));
}
public IList <User> GetUsersBy(string keyword, string email, string role, string selJobType, string accountState, int pageIndex, int pageSize, out int recordCount)
{
Conditions conditions = new Conditions();
if (!StringHelper.isNullOrEmpty(keyword))
{
conditions.ConditionExpressions.Add(new Condition("FullName", Operator.Like, keyword));
}
if (!StringHelper.isNullOrEmpty(email))
{
conditions.ConditionExpressions.Add(new Condition("Email", Operator.Like, email));
}
if (!StringHelper.isNullOrEmpty(role))
{
conditions.ConditionExpressions.Add(new Condition("Role", Operator.Like, role));
}
if (!StringHelper.isNullOrEmpty(accountState))
{
string str = accountState.ToUpper();
if (!(str == "ACTIVE"))
{
if (str == "DISABLED")
{
conditions.ConditionExpressions.Add(new Condition("AccountState", Operator.EqualTo, 2));
}
}
else
{
conditions.ConditionExpressions.Add(new Condition("AccountState", Operator.EqualTo, 1));
}
}
if (!string.IsNullOrEmpty(selJobType))
{
conditions.ConditionExpressions.Add(new Condition("JobType", Operator.EqualTo, selJobType));
}
conditions.Connector = Connector.AND;
OrderExpression orderExpression = new OrderExpression("FullName", false);
return(this.dbGateway.getRecords(pageIndex, pageSize, conditions, orderExpression, null, Connector.AND, out recordCount));
}
private Expression BindOrderBy(MethodCallExpression call)
{
Expression source = call.Arguments[0];
LambdaExpression orderSelector = GetLambda(call.Arguments[1]);
OrderType orderType = call.Method.Name.EndsWith("Descending") ? OrderType.Descending : OrderType.Ascending;
List <OrderExpression> myThenBys = _thenBys;
_thenBys = null;
ProjectionExpression projection = VisitSequence(source);
_map[orderSelector.Parameters[0]] = projection.Projector;
List <OrderExpression> orderings = new List <OrderExpression> {
new OrderExpression(orderType, Visit(orderSelector.Body))
};
if (myThenBys != null)
{
for (int i = myThenBys.Count - 1; i >= 0; i--)
{
OrderExpression tb = myThenBys[i];
LambdaExpression lambda = (LambdaExpression)tb.Expression;
_map[lambda.Parameters[0]] = projection.Projector;
orderings.Add(new OrderExpression(tb.OrderType, Visit(lambda.Body)));
}
}
string alias = GetNewAlias();
ProjectedColumns pc = ProjectColumns(projection.Projector, alias, projection.Source.Alias);
return(new ProjectionExpression(
new SelectExpression(
alias,
pc.Columns,
projection.Source,
orderBy: orderings.AsReadOnly()
),
pc.Projector
));
}
public T ObterPor(Guid UnidadeNegocio, Guid Segmento, Guid FamiliaProduto, int ano, int trimestre)
{
var query = GetQueryExpression <T>(true);
#region Condições
ConditionExpression cond1 = new ConditionExpression("itbc_unidadedenegocioid", Microsoft.Xrm.Sdk.Query.ConditionOperator.Equal, UnidadeNegocio);
query.Criteria.Conditions.Add(cond1);
ConditionExpression cond2 = new ConditionExpression("statuscode", Microsoft.Xrm.Sdk.Query.ConditionOperator.Equal, 1);
query.Criteria.Conditions.Add(cond2);
ConditionExpression cond3 = new ConditionExpression("itbc_segmentoid", Microsoft.Xrm.Sdk.Query.ConditionOperator.Equal, Segmento);
query.Criteria.Conditions.Add(cond3);
ConditionExpression cond4 = new ConditionExpression("itbc_familiadeprodutoid", Microsoft.Xrm.Sdk.Query.ConditionOperator.Equal, FamiliaProduto);
query.Criteria.Conditions.Add(cond4);
ConditionExpression cond5 = new ConditionExpression("itbc_ano", Microsoft.Xrm.Sdk.Query.ConditionOperator.Equal, ano);
query.Criteria.Conditions.Add(cond5);
ConditionExpression cond6 = new ConditionExpression("itbc_trimestre", Microsoft.Xrm.Sdk.Query.ConditionOperator.Equal, trimestre);
query.Criteria.Conditions.Add(cond6);
#endregion
#region Ordenação
OrderExpression ord1 = new OrderExpression("itbc_name", Microsoft.Xrm.Sdk.Query.OrderType.Ascending);
query.Orders.Add(ord1);
#endregion
var colecao = this.RetrieveMultiple(query);
if (colecao.List.Count == 0)
{
return(default(T));
}
return(colecao.List[0]);
}
public override CommandData CreateSelectBaseCommand(DataEntityMapping mapping, string customSelect,
QueryExpression query, OrderExpression order, Region region, CreateSqlState state)
{
if (region != null && region.Start > 0)
{
if (order == null)
{
order = CreatePrimaryKeyOrderExpression(mapping);
}
if (order != null)
{
var commandData = base.CreateSelectBaseCommand(mapping, customSelect, query, order, null, state);
commandData.CommandText =
$"{commandData.CommandText} offset {region.Start} row fetch next {region.Size} rows only";
commandData.InnerPage = true;
return(commandData);
}
}
return(base.CreateSelectBaseCommand(mapping, customSelect, query, order, region, state));
}
public List <T> ObterDetalhadoProdutosManual(Guid orcamentounidadeId, Guid canalId)
{
var query = GetQueryExpression <T>(true);
#region Condições
query.Criteria.AddCondition("statecode", ConditionOperator.Equal, 0);
query.Criteria.AddCondition("itbc_canalid", ConditionOperator.Equal, canalId);
query.AddLink("itbc_orcamentodocanal", "itbc_orcamentodocanalid", "itbc_orcamentodocanalid")
.AddLink("itbc_orcamentoportrimestredaunidade", "itbc_orcamentoportrimestredaunidadeid", "itbc_orcamentoportrimestredaunidadeid")
.LinkCriteria.AddCondition("new_orcamentoporunidadeid", ConditionOperator.Equal, orcamentounidadeId);
#endregion
#region Ordenações
OrderExpression ord1 = new OrderExpression("itbc_canalid", OrderType.Ascending);
OrderExpression ord2 = new OrderExpression("itbc_produtoid", OrderType.Ascending);
query.Orders.Add(ord1);
query.Orders.Add(ord2);
#endregion
return((List <T>) this.RetrieveMultiple(query).List);
}
public List <T> Listar(Guid metaunidadeId, List <Guid> lstIdCanal)
{
var query = GetQueryExpression <T>(true);
#region Condições
query.Criteria.AddCondition("statecode", ConditionOperator.Equal, 0);
if (lstIdCanal != null)
{
query.Criteria.AddCondition(new ConditionExpression("itbc_canalid", ConditionOperator.NotIn, lstIdCanal));
}
query.AddLink("itbc_metaportrimestre", "itbc_metadotrimestreid", "itbc_metaportrimestreid")
.LinkCriteria.AddCondition("itbc_metas", ConditionOperator.Equal, metaunidadeId);
#endregion
#region Ordenações
OrderExpression ord1 = new OrderExpression("itbc_name", OrderType.Ascending);
query.Orders.Add(ord1);
#endregion
return((List <T>) this.RetrieveMultiple(query).List);
}
public List <T> ListarSupervisoresPor(Guid unidadeId, List <Guid> lstIdSupervisor)
{
var query = GetQueryExpression <T>(true);
#region Condições
//query.Criteria.AddCondition("isdisabled", ConditionOperator.Equal, false);
query.Criteria.AddCondition("itbc_funcao", ConditionOperator.Equal, 993520000);
query.Criteria.AddCondition("businessunitid", ConditionOperator.Equal, unidadeId);
if (lstIdSupervisor != null && lstIdSupervisor.Count > 0)
{
query.Criteria.AddCondition(new ConditionExpression("systemuserid", ConditionOperator.NotIn, lstIdSupervisor));
}
#endregion
#region Ordenações
OrderExpression ord1 = new OrderExpression("fullname", OrderType.Ascending);
query.Orders.Add(ord1);
#endregion
return((List <T>) this.RetrieveMultiple(query).List);
}
public async Task <List <Responses.Order> > GetUserOrders(string userId)
{
// Get user
var user = await _mainDbContext.Users
.Include(x => x.Orders)
.FirstOrDefaultAsync(x => x.UserId == userId);
// Check if it exists
if (user == null)
{
throw new NotFoundException(UserMessage.UserNotFound);
}
// Get all orders
var orders = await _mainDbContext.Orders.Where(OrderExpression.OrderFilter(userId)).ToListAsync();
// Response
var response = _mapper.Map <List <Responses.Order> >(orders);
// Return
return(response);
}
public List <T> ListarOrcamentos(Guid unidadenegocioId, int ano)
{
var query = GetQueryExpression <T>(true);
#region Condições
query.Criteria.AddCondition("statecode", ConditionOperator.Equal, 0);
query.Criteria.AddCondition("itbc_ano", ConditionOperator.Equal, ano);
if (!unidadenegocioId.Equals(Guid.Empty))
{
query.Criteria.AddCondition("itbc_unidadedenegocioid", ConditionOperator.Equal, unidadenegocioId);
}
#endregion
#region Ordenações
OrderExpression ord1 = new OrderExpression("itbc_name", OrderType.Ascending);
query.Orders.Add(ord1);
#endregion
return((List <T>) this.RetrieveMultiple(query).List);
}
public List <T> ListarTodosDetalheProdutosKaRepresentante(Guid metaId, int?pagina, int?contagem, Guid unidadeId, int ano)
{
var query = GetQueryExpression <T>(true);
query.Distinct = true;
#region Condições
query.Criteria.AddCondition("statecode", ConditionOperator.Equal, 0);
if (ano != null)
{
query.Criteria.AddCondition("itbc_ano", ConditionOperator.Equal, ano);
}
query.AddLink("itbc_metadokaporproduto", "itbc_metadokaporprodutoid", "itbc_metadokaporprodutoid")
.AddLink("itbc_metadokaporsubfamilia", "itbc_metadokaporsubfamiliaid", "itbc_metadokaporsubfamiliaid")
.AddLink("itbc_metadokaporfamilia", "itbc_metadokaporfamiliaid", "itbc_metadokaporfamiliaid")
.AddLink("itbc_metadokaporsegmento", "itbc_metadokaporsegmentoid", "itbc_metadokaporsegmentoid")
.AddLink("itbc_metatrimestreka", "itbc_metatrimestrekaid", "itbc_metatrimestrekaid")
.AddLink("itbc_metakeyaccount", "itbc_metadokarepresentanteid", "itbc_metakeyaccountid")
.AddLink("contact", "itbc_contact", "contactid")
.AddLink("itbc_portfoliokeyaccountrepresentantes", "contactid", "itbc_contatoid")
.LinkCriteria.AddCondition("itbc_unidadedenegocioid", ConditionOperator.Equal, unidadeId);
#endregion
#region Ordenações
OrderExpression ord1 = new OrderExpression("itbc_name", OrderType.Ascending);
query.Orders.Add(ord1);
#endregion
if (pagina.HasValue && contagem.HasValue)
{
PagingInfo paging = new PagingInfo();
paging.Count = contagem.Value;
paging.PageNumber = pagina.Value;
query.PageInfo = paging;
}
return((List <T>) this.RetrieveMultiple(query).List);
}
protected override Expression VisitSelect(SelectExpression select)
{
// visit column projection first
ReadOnlyCollection<ColumnDeclaration> columns = select.Columns;
Expression take = this.Visit(select.Take);
Expression skip = this.Visit(select.Skip);
ReadOnlyCollection<Expression> groupbys = this.VisitExpressionList(select.GroupBy);
ReadOnlyCollection<OrderExpression> orderbys = this.VisitOrderBy(select.OrderBy);
Expression where = this.Visit(select.Where);
Expression from = this.Visit(select.From);
if (columns.Count == 1 && orderbys!=null) {
if (columns[0].Expression.ToString() == "COUNT(*)" && orderbys.Count > 0) {
var newOrders=new OrderExpression[0];
//this is is COUNT/ORDER BY/GROUP BY issue
select = new SelectExpression(select.Alias, columns, from, where, newOrders, groupbys, select.IsDistinct, skip, take);
return select;
}
}
return select;
}
public T ObterPor(int itbc_condicao_pagamento)
{
var query = GetQueryExpression <T>(true);
#region Condições
ConditionExpression cond1 = new ConditionExpression("itbc_condicao_pagamento", ConditionOperator.Equal, itbc_condicao_pagamento);
query.Criteria.Conditions.Add(cond1);
#endregion
#region Ordenação
OrderExpression ord1 = new OrderExpression("itbc_name", OrderType.Ascending);
query.Orders.Add(ord1);
#endregion
var colecao = this.RetrieveMultiple(query);
if (colecao.List.Count == 0)
{
return(default(T));
}
return(colecao.List[0]);
}
public List <T> ObterProdutos(Guid metaunidadeId, List <Guid> lstIdProdutos)
{
var query = GetQueryExpression <T>(true);
#region Condições
query.Criteria.AddCondition("statecode", ConditionOperator.Equal, 0);
query.Criteria.AddCondition(new ConditionExpression("itbc_produtoid", ConditionOperator.NotIn, lstIdProdutos));
query.AddLink("itbc_metadokaporsubfamilia", "itbc_metadokaporsubfamiliaid", "itbc_metadokaporsubfamiliaid")
.AddLink("itbc_metadokaporfamilia", "itbc_metadokaporfamiliaid", "itbc_metadokaporfamiliaid")
.AddLink("itbc_metadokaporsegmento", "itbc_metadokaporsegmentoid", "itbc_metadokaporsegmentoid")
.AddLink("itbc_metakeyaccount", "itbc_metadokarepresentanteid", "itbc_metakeyaccountid")
.AddLink("itbc_metaportrimestre", "itbc_metaportrimestredaunidadeid", "itbc_metaportrimestreid")
.LinkCriteria.AddCondition("itbc_metas", ConditionOperator.Equal, metaunidadeId);
#endregion
#region Ordenações
OrderExpression ord1 = new OrderExpression("itbc_name", OrderType.Ascending);
query.Orders.Add(ord1);
#endregion
return((List <T>) this.RetrieveMultiple(query).List);
}
protected virtual Expression VisitOrder(OrderExpression expression)
{
Visit(expression.Source);
Visit(expression.OrderSelector);
if (expression.Options == OrderOptions.Ascending)
{
_expressionInProgress.Enqueue(new StringFragment(" asc"));
}
else if (expression.Options == OrderOptions.Descending)
{
_expressionInProgress.Enqueue(new StringFragment(" desc"));
}
else
{
throw new ArgumentException("OrderBy expression does not have a sort order");
}
Query.ThenOrderClauses.Enqueue(new CompoundFragment(_expressionInProgress));
_expressionInProgress = new Queue <IFragment>();
return(expression);
}
public T ObterPor(string codigofamilia)
{
var query = GetQueryExpression <T>(true);
#region Condições
query.Criteria.AddCondition("statecode", ConditionOperator.Equal, 0);
query.Criteria.AddCondition("itbc_codigo_familia", ConditionOperator.Equal, codigofamilia);
#endregion
#region Ordenações
OrderExpression ord1 = new OrderExpression("itbc_codigo_familia", OrderType.Ascending);
query.Orders.Add(ord1);
#endregion
var colecao = this.RetrieveMultiple(query);
if (colecao.List.Count == 0)
{
return(default(T));
}
return(colecao.List[0]);
}
private ResultSetBuilder VisitOrderBy(OrderExpression exp)
{
var result = Visit(exp.Body);
var predicate = exp.Predicate;
var lambda = predicate as LambdaExpression;
if (lambda != null)
{
predicate = lambda.Body;
}
var member = ExHelper.GetMemberName(predicate, _itemType, exp);
var column = _journal.Metadata.TryGetColumnByPropertyName(member);
if (column == null)
{
throw QueryExceptionExtensions.ExpressionNotSupported(
string.Format("Column '{0}' does not exist", member), exp);
}
result.ApplyOrderBy(column, (EJournalExpressionType)exp.NodeType);
return(result);
}
public T ObterPorChaveIntegracao(String chaveIntegracao)
{
var query = GetQueryExpression <T>(true);
#region Condições
ConditionExpression cond1 = new ConditionExpression("itbc_chave_integracao", ConditionOperator.Equal, chaveIntegracao);
query.Criteria.Conditions.Add(cond1);
#endregion
#region Ordenação
OrderExpression ord1 = new OrderExpression("productdescription", OrderType.Ascending);
query.Orders.Add(ord1);
#endregion
var colecao = this.RetrieveMultiple(query);
if (colecao.List.Count == 0)
{
return(default(T));
}
return(colecao.List[0]);
}
public List <T> Listar(Guid supervisorId, Guid produtoId, Guid trimestreId)
{
var query = GetQueryExpression <T>(true);
#region Condições
query.Criteria.AddCondition("statecode", ConditionOperator.Equal, 0);
query.Criteria.AddCondition("itbc_supervisor", ConditionOperator.Equal, supervisorId);
query.Criteria.AddCondition("itbc_produtoid", ConditionOperator.Equal, produtoId);
query.AddLink("itbc_potencialdosupervisorporproduto", "itbc_potencialdetalhadodosupervisorid", "itbc_potencialdosupervisorporprodutoid")
.AddLink("itbc_potencial_supervisor_subfamilia", "itbc_potencialsupervisorporprodutoid", "itbc_potencial_supervisor_subfamiliaid")
.AddLink("itbc_potencialdosupervisorporfamilia", "itbc_potencialsupervisorsubfamiliaid", "itbc_potencialdosupervisorporfamiliaid")
.AddLink("itbc_potencial_supervisorporsegmento", "itbc_potencialsupervisorporfamiliaid", "itbc_potencial_supervisorporsegmentoid")
.AddLink("itbc_potencialdosupervisor", "itbc_potencialdosupervisorid", "itbc_potencialdosupervisorid")
.LinkCriteria.AddCondition("itbc_potencialportrimestredaunidadeid", ConditionOperator.Equal, trimestreId);
#endregion
#region Ordenações
OrderExpression ord1 = new OrderExpression("itbc_name", OrderType.Ascending);
query.Orders.Add(ord1);
#endregion
return((List <T>) this.RetrieveMultiple(query).List);
}
protected override Expression VisitRowNumber(RowNumberExpression rowNumber)
{
Write("ROW_NUMBER() OVER(");
if (rowNumber.OrderBy != null && rowNumber.OrderBy.Count > 0)
{
Write("ORDER BY ");
for (int i = 0, n = rowNumber.OrderBy.Count; i < n; i++)
{
OrderExpression exp = rowNumber.OrderBy[i];
if (i > 0)
{
Write(", ");
}
VisitValue(exp.Expression);
if (exp.OrderType != OrderType.Ascending)
{
Write(" DESC");
}
}
}
Write(")");
return(rowNumber);
}
public T ObterPor(Guid accountid)
{
var query = GetQueryExpression <T>(true);
#region Condições
ConditionExpression cond1 = new ConditionExpression("invoicedetailid", ConditionOperator.Equal, accountid);
query.Criteria.Conditions.Add(cond1);
#endregion
#region Ordenação
OrderExpression ord1 = new OrderExpression("productdescription", OrderType.Ascending);
query.Orders.Add(ord1);
#endregion
var colecao = this.RetrieveMultiple(query);
if (colecao.List.Count == 0)
{
return(default(T));
}
return(colecao.List[0]);
}
public T ObterPorCodigo(string chaveIntegracaoUnidadeNegocio, Domain.Enum.OrcamentodaUnidade.Trimestres trimestre, string codigoEmitenteCanal, int ano, params string[] columns)
{
var query = GetQueryExpression <T>(true);
if (columns != null && columns.Length > 0)
{
query.ColumnSet.AddColumns(columns);
}
#region Condições
query.Criteria.AddCondition("statecode", ConditionOperator.Equal, 0);
//query.Criteria.AddCondition("itbc_unidadedenegocioid", ConditionOperator.Equal, UnidNeg);
query.Criteria.AddCondition("itbc_trimestre", ConditionOperator.Equal, (int)trimestre);
query.Criteria.AddCondition("itbc_ano", ConditionOperator.Equal, ano);
query.AddLink("account", "itbc_canalid", "accountid");
query.LinkEntities[0].LinkCriteria.AddCondition("accountnumber", ConditionOperator.Equal, codigoEmitenteCanal);
query.AddLink("businessunit", "itbc_unidadedenegocioid", "businessunitid");
query.LinkEntities[1].LinkCriteria.AddCondition("itbc_chave_integracao", ConditionOperator.Equal, chaveIntegracaoUnidadeNegocio);
#endregion
#region Ordenações
OrderExpression ord1 = new OrderExpression("itbc_name", OrderType.Ascending);
query.Orders.Add(ord1);
#endregion
var colecao = this.RetrieveMultiple(query);
if (colecao.List.Count == 0)
{
return(default(T));
}
return(colecao.List[0]);
}
public List <T> ListarPor(Guid?Canal, int?RazaoDoStatus, DateTime?DataDeEnvioInicio, DateTime?DataDeEnvioFim)
{
var query = GetQueryExpression <T>(true);
#region Condições
if (Canal.HasValue)
{
ConditionExpression cond1 = new ConditionExpression("itbc_canalid", ConditionOperator.Equal, Canal);
query.Criteria.Conditions.Add(cond1);
}
query.Criteria.AddCondition("statecode", ConditionOperator.Equal, 0);
if (RazaoDoStatus.HasValue)
{
ConditionExpression cond2 = new ConditionExpression("statuscode", ConditionOperator.Equal, RazaoDoStatus.Value);
query.Criteria.Conditions.Add(cond2);
}
if (DataDeEnvioInicio.HasValue)
{
ConditionExpression cond3 = new ConditionExpression("itbc_datadeenvio", ConditionOperator.GreaterEqual, DataDeEnvioInicio.Value);
query.Criteria.Conditions.Add(cond3);
}
if (DataDeEnvioFim.HasValue)
{
ConditionExpression cond4 = new ConditionExpression("itbc_datadeenvio", ConditionOperator.LessEqual, DataDeEnvioFim.Value);
query.Criteria.Conditions.Add(cond4);
}
#endregion
OrderExpression ord1 = new OrderExpression("itbc_datadeenvio", OrderType.Descending);
query.Orders.Add(ord1);
return((List <T>) this.RetrieveMultiple(query).List);
}
public IActionResult Export([FromBody] EntityGridModel model)
{
QueryView.Domain.QueryView queryView = null;
if (model.QueryViewId.HasValue && !model.QueryViewId.Equals(Guid.Empty))
{
queryView = _queryViewFinder.FindById(model.QueryViewId.Value);
}
else if (model.EntityId.HasValue && !model.EntityId.Value.Equals(Guid.Empty))
{
queryView = _queryViewFinder.FindEntityDefaultView(model.EntityId.Value);
}
else if (model.EntityName.IsNotEmpty())
{
queryView = _queryViewFinder.FindEntityDefaultView(model.EntityName);
}
else
{
return(NotFound());
}
if (queryView == null)
{
return(NotFound());
}
OrderExpression orderExp = null;
if (model.IsSortBySeted)
{
orderExp = new OrderExpression(model.SortBy, model.SortDirection == 0 ? OrderType.Ascending : OrderType.Descending);
}
string path = _dataExporter.ToExcelFile(queryView, model.Filter, orderExp, queryView.Name, model.ExportType == 1, includeIndex: model.IncludeIndex, title: model.ExportTitle);
if (path.IsEmpty())
{
return(JError(T["list_nodata"]));
}
return(JOk(path));
}
public T ObterPor(Guid treinamentolId)
{
var query = GetQueryExpression <T>(true);
#region Condições
ConditionExpression cond1 = new Microsoft.Xrm.Sdk.Query.ConditionExpression("itbc_treinamcertifid", ConditionOperator.Equal, treinamentolId);
query.Criteria.Conditions.Add(cond1);
#endregion
#region Ordenação
OrderExpression ord1 = new OrderExpression("itbc_name", Microsoft.Xrm.Sdk.Query.OrderType.Ascending);
query.Orders.Add(ord1);
#endregion
var colecao = this.RetrieveMultiple(query);
if (colecao.List.Count == 0)
{
return(default(T));
}
return(colecao.List[0]);
}
protected ReadOnlyCollection <OrderExpression> VisitOrderBy(ReadOnlyCollection <OrderExpression> expressions)
{
if (expressions == null)
{
return(null);
}
List <OrderExpression> alternate = null;
for (int i = 0, n = expressions.Count; i < n; i++)
{
OrderExpression expr = expressions[i];
Expression e = Visit(expr.Expression);
if (alternate == null && e != expr.Expression)
{
alternate = expressions.Take(i).ToList();
}
alternate?.Add(new OrderExpression(expr.OrderType, e));
}
return(alternate != null?alternate.AsReadOnly() : expressions);
}
protected DbOrdering ParseOrderExpression(OrderExpression orderExp)
{
ScopeParameterDictionary scopeParameters = this._resultElement.ScopeParameters.Clone(orderExp.KeySelector.Parameters[0], this._resultElement.MappingObjectExpression);
DbExpression dbExpression = GeneralExpressionVisitor.ParseLambda(orderExp.KeySelector, scopeParameters, this._resultElement.ScopeTables);
DbOrderType orderType;
if (orderExp.NodeType == QueryExpressionType.OrderBy || orderExp.NodeType == QueryExpressionType.ThenBy)
{
orderType = DbOrderType.Asc;
}
else if (orderExp.NodeType == QueryExpressionType.OrderByDesc || orderExp.NodeType == QueryExpressionType.ThenByDesc)
{
orderType = DbOrderType.Desc;
}
else
{
throw new NotSupportedException(orderExp.NodeType.ToString());
}
DbOrdering ordering = new DbOrdering(dbExpression, orderType);
return(ordering);
}
public T ObterOrcamentoSubFamilia(Guid subfamiliaId, Guid orcamentofamiliaId)
{
var query = GetQueryExpression <T>(true);
#region Condições
query.Criteria.AddCondition("statecode", ConditionOperator.Equal, 0);
query.Criteria.AddCondition("itbc_orcamentoporfamiliaid", ConditionOperator.Equal, orcamentofamiliaId);
query.Criteria.AddCondition("itbc_subfamiliaid", ConditionOperator.Equal, subfamiliaId);
#endregion
#region Ordenações
OrderExpression ord1 = new OrderExpression("itbc_name", OrderType.Ascending);
query.Orders.Add(ord1);
#endregion
var colecao = this.RetrieveMultiple(query);
if (colecao.List.Count == 0)
{
return(default(T));
}
return(colecao.List[0]);
}
public IList <User> GetUsersByRole(UserRoles role, int pageIndex, int pageSize, out int recordCount, string userJobType = "FT")
{
Conditions conditions = new Conditions();
switch (role)
{
case UserRoles.OSAT:
case UserRoles.OSATAdmin:
conditions.ConditionExpressions.Add(new Condition("Role", Operator.EqualTo, UserRoles.OSAT));
//conditions.ConditionExpressions.Add(new Condition("Role", Operator.EqualTo, UserRoles.OSATAdmin));
break;
case UserRoles.PC:
case UserRoles.PCAdmin:
conditions.ConditionExpressions.Add(new Condition("Role", Operator.Like, UserRoles.PC));
//conditions.ConditionExpressions.Add(new Condition("Role", Operator.EqualTo, UserRoles.PCAdmin));
break;
case UserRoles.PE:
case UserRoles.PEAdmin:
conditions.ConditionExpressions.Add(new Condition("Role", Operator.Like, UserRoles.PE));
//conditions.ConditionExpressions.Add(new Condition("Role", Operator.EqualTo, UserRoles.PEAdmin));
break;
case UserRoles.QA:
case UserRoles.QAAdmin:
conditions.ConditionExpressions.Add(new Condition("Role", Operator.Like, UserRoles.QA));
//conditions.ConditionExpressions.Add(new Condition("Role", Operator.EqualTo, UserRoles.QAAdmin));
break;
}
conditions.Connector = Connector.AND;
conditions.ConditionExpressions.Add(new Condition("JobType", Operator.Like, userJobType.ToUpper()));
OrderExpression orderExpression = new OrderExpression("FullName", false);
return(this.dbGateway.getRecords(pageIndex, pageSize, conditions, orderExpression, StringHelper.BuildStringList(" AccountState=1 "), Connector.AND, out recordCount));
}
/// <summary>
/// Create and configure the organization service proxy.
/// Create a parent account record and subsequent 10 child account records.
/// Retrieve batch of records using RetrieveMultiple message with paging cookie.
/// Optionally delete any entity records that were created for this sample.
/// </summary>
/// <param name="serverConfig">Contains server connection information.</param>
/// <param name="promptForDelete">When True, the user will be prompted to delete all
/// created entities.</param>
public void Run(ServerConnection.Configuration serverConfig, bool promptForDelete)
{
try
{
// Connect to the Organization service.
// The using statement assures that the service proxy will be properly disposed.
using (_serviceProxy = new OrganizationServiceProxy(serverConfig.OrganizationUri, serverConfig.HomeRealmUri,serverConfig.Credentials, serverConfig.DeviceCredentials))
{
// This statement is required to enable early-bound type support.
_serviceProxy.EnableProxyTypes();
_service = (IOrganizationService)_serviceProxy;
// Call the method to create any data that this sample requires.
CreateRequiredRecords();
//<snippetQueryExpressionPagingWithCookie1>
// Query using the paging cookie.
// Define the paging attributes.
// The number of records per page to retrieve.
int queryCount = 3;
// Initialize the page number.
int pageNumber = 1;
// Initialize the number of records.
int recordCount = 0;
// Define the condition expression for retrieving records.
ConditionExpression pagecondition = new ConditionExpression();
pagecondition.AttributeName = "parentaccountid";
pagecondition.Operator = ConditionOperator.Equal;
pagecondition.Values.Add(_parentAccountId);
// Define the order expression to retrieve the records.
OrderExpression order = new OrderExpression();
order.AttributeName = "name";
order.OrderType = OrderType.Ascending;
// Create the query expression and add condition.
QueryExpression pagequery = new QueryExpression();
pagequery.EntityName = "account";
pagequery.Criteria.AddCondition(pagecondition);
pagequery.Orders.Add(order);
pagequery.ColumnSet.AddColumns("name", "emailaddress1");
// Assign the pageinfo properties to the query expression.
pagequery.PageInfo = new PagingInfo();
pagequery.PageInfo.Count = queryCount;
pagequery.PageInfo.PageNumber = pageNumber;
// The current paging cookie. When retrieving the first page,
// pagingCookie should be null.
pagequery.PageInfo.PagingCookie = null;
Console.WriteLine("Retrieving sample account records in pages...\n");
Console.WriteLine("#\tAccount Name\t\tEmail Address");
while (true)
{
// Retrieve the page.
EntityCollection results = _serviceProxy.RetrieveMultiple(pagequery);
if (results.Entities != null)
{
// Retrieve all records from the result set.
foreach (Account acct in results.Entities)
{
Console.WriteLine("{0}.\t{1}\t{2}", ++recordCount, acct.Name,
acct.EMailAddress1);
}
}
// Check for more records, if it returns true.
if (results.MoreRecords)
{
Console.WriteLine("\n****************\nPage number {0}\n****************", pagequery.PageInfo.PageNumber);
Console.WriteLine("#\tAccount Name\t\tEmail Address");
// Increment the page number to retrieve the next page.
pagequery.PageInfo.PageNumber++;
// Set the paging cookie to the paging cookie returned from current results.
pagequery.PageInfo.PagingCookie = results.PagingCookie;
}
else
{
// If no more records are in the result nodes, exit the loop.
break;
}
}
//</snippetQueryExpressionPagingWithCookie1>
DeleteRequiredRecords(promptForDelete);
}
}
// Catch any service fault exceptions that Microsoft Dynamics CRM throws.
catch (FaultException<Microsoft.Xrm.Sdk.OrganizationServiceFault>)
{
// You can handle an exception here or pass it back to the calling method.
throw;
}
return;
}
protected override OrderExpression VisitOrderBy(OrderExpression o)
{
var e = MakeSqlValue(Visit(o.Expression));
if (e == o.Expression)
return o;
return new OrderExpression(o.OrderType, e);
}
public static SelectExpression RemoveOrderExpression(this SelectExpression select, OrderExpression ordering)
{
if (select.OrderBy != null && select.OrderBy.Count > 0)
{
List<OrderExpression> orderby = new List<OrderExpression>(select.OrderBy);
orderby.Remove(ordering);
return select.SetOrderBy(orderby);
}
return select;
}
private void FillMeetingList(int page)
{
#region Create Table
DataTable dtb = new DataTable();
dtb.Columns.Add("number");
dtb.Columns.Add("name");
dtb.Columns.Add("dateholding");
dtb.Columns.Add("starttime");
dtb.Columns.Add("endtime");
dtb.Columns.Add("status");
dtb.Columns.Add("attendancelistid");
#endregion
#region Get Record From Crm
QueryExpression query = new QueryExpression("new_attendancelist");
query.ColumnSet = new ColumnSet(new string[] { "new_name", "new_meetingnumber", "new_dateholding", "new_starttime",
"new_endtime", "new_status", "new_attendancelistid", "createdon", "new_starttime1", "new_minutesstart", "new_endtime1", "new_minutesend"});
query.Criteria.AddCondition("new_relatedteacher", ConditionOperator.Equal, new Guid("{" + Session["Personnelid"].ToString() + "}"));
query.Criteria.AddCondition("new_relatedclassid", ConditionOperator.Equal, new Guid(ClassId));
OrderExpression _Order = new OrderExpression("new_dateholding", OrderType.Ascending);
query.Orders.Add(_Order);
query.PageInfo = new PagingInfo();
query.PageInfo.ReturnTotalRecordCount = true;
query.PageInfo.Count = 10;
query.PageInfo.PageNumber = page;
query.PageInfo.PagingCookie = null;
EntityCollection retrieved = crmService.RetrieveMultiple(query);
#endregion
#region Fill DataTable
if (retrieved != null && retrieved.Entities.Count > 0)
{
TimeSpan time = new TimeSpan(3, 30, 0);
for (int i = 0; i < retrieved.Entities.Count; i++)
{
DataRow dr = dtb.NewRow();
if (retrieved.Entities[i].Contains("new_meetingnumber"))
dr["number"] = retrieved.Entities[i].Attributes["new_meetingnumber"].ToString();
if (retrieved.Entities[i].Contains("new_name"))
dr["name"] = retrieved.Entities[i].Attributes["new_name"].ToString();
if (retrieved.Entities[i].Contains("new_dateholding"))
dr["dateholding"] = gregorianToShamsi(((DateTime)retrieved.Entities[i].Attributes["new_dateholding"]).Date);
if (retrieved.Entities[i].Contains("new_starttime1") && retrieved.Entities[i].Contains("new_minutesstart"))
dr["starttime"] = GetOptionsSetTextOnValue(crmService, "new_attendancelist", "new_starttime1", ((OptionSetValue)retrieved.Entities[i]["new_starttime1"]).Value, 1065) + ":" +
GetOptionsSetTextOnValue(crmService, "new_attendancelist", "new_minutesstart", ((OptionSetValue)retrieved.Entities[i]["new_minutesstart"]).Value, 1065);
if (retrieved.Entities[i].Contains("new_endtime1") && retrieved.Entities[i].Contains("new_minutesend"))
dr["endtime"] = GetOptionsSetTextOnValue(crmService, "new_attendancelist", "new_endtime1", ((OptionSetValue)retrieved.Entities[i]["new_endtime1"]).Value, 1065) + ":" +
GetOptionsSetTextOnValue(crmService, "new_attendancelist", "new_minutesend", ((OptionSetValue)retrieved.Entities[i]["new_minutesend"]).Value, 1065);
//if (retrieved.Entities[i].Contains("new_endtime"))
// dr["endtime"] = gregorianToShamsi(((DateTime)retrieved.Entities[i].Attributes["new_endtime"]).Date) + " " + String.Format("{0:t}", ((DateTime)retrieved.Entities[i].Attributes["new_endtime"]).Add(time));
if (retrieved.Entities[i].Contains("new_status"))
dr["status"] = GetOptionsSetTextOnValue(crmService, "new_attendancelist", "new_status", ((OptionSetValue)retrieved.Entities[i].Attributes["new_status"]).Value, 1065);
if (retrieved.Entities[i].Contains("new_attendancelistid"))
dr["attendancelistid"] = retrieved.Entities[i].Attributes["new_attendancelistid"].ToString();
dtb.Rows.Add(dr);
}
}
#endregion
if (retrieved.MoreRecords)
{
query.PageInfo.PagingCookie = retrieved.PagingCookie;
RequestMeetingView.VirtualItemCount = retrieved.TotalRecordCount;
}
RequestMeetingView.PageSize = 10;
RequestMeetingView.DataSource = dtb;
RequestMeetingView.DataBind();
}
protected virtual OrderExpression VisitOrderBy(OrderExpression o)
{
var e = Visit(o.Expression);
if (e == o.Expression)
return o;
return new OrderExpression(o.OrderType, e);
}
protected static DbOrderSegment VisistOrderExpression(List<IMappingObjectExpression> moeList, OrderExpression orderExp)
{
DbExpression dbExpression = GeneralExpressionVisitor.VisitPredicate(orderExp.Expression, moeList);
OrderType orderType;
if (orderExp.NodeType == QueryExpressionType.OrderBy || orderExp.NodeType == QueryExpressionType.ThenBy)
{
orderType = OrderType.Asc;
}
else if (orderExp.NodeType == QueryExpressionType.OrderByDesc || orderExp.NodeType == QueryExpressionType.ThenByDesc)
{
orderType = OrderType.Desc;
}
else
throw new NotSupportedException(orderExp.NodeType.ToString());
DbOrderSegment orderSeg = new DbOrderSegment(dbExpression, orderType);
return orderSeg;
}
private void FillTuitionList()
{
#region Create Table
DataTable dtb = new DataTable();
dtb.Columns.Add("requestdate");
dtb.Columns.Add("code");
dtb.Columns.Add("paymentstatus");
dtb.Columns.Add("totalteachinghours_ds");
dtb.Columns.Add("teachersalary");
dtb.Columns.Add("deductions");
dtb.Columns.Add("theamountpaid");
dtb.Columns.Add("typeofpayment");
dtb.Columns.Add("dateofpayment");
dtb.Columns.Add("teacherpaymentrequestid");
#endregion
#region Get Record From Crm
QueryExpression query = new QueryExpression("new_teacherpaymentrequest");
query.ColumnSet = new ColumnSet(new string[] { "new_teacherpaymentrequestid", "new_requestdate", "new_code", "new_paymentstatus",
"new_totalteachinghours_ds", "new_teachersalary", "new_deductions", "new_theamountpaid",
"new_typeofpayment","new_dateofpayment","createdon" });
query.Criteria.AddCondition("new_teacherid", ConditionOperator.Equal, new Guid("{" + Session["Personnelid"].ToString() + "}"));
query.Criteria.AddCondition("new_classid", ConditionOperator.Equal, new Guid(ClassId));
OrderExpression _Order = new OrderExpression("createdon", OrderType.Ascending);
query.Orders.Add(_Order);
EntityCollection retrieved = crmService.RetrieveMultiple(query);
#endregion
#region Fill DataTable
if (retrieved != null && retrieved.Entities.Count > 0)
{
for (int i = 0; i < retrieved.Entities.Count; i++)
{
DataRow dr = dtb.NewRow();
if (retrieved.Entities[i].Contains("new_requestdate") && retrieved.Entities[i]["new_requestdate"] != null)
dr["requestdate"] = gregorianToShamsi(((DateTime)retrieved.Entities[i].Attributes["new_requestdate"]).Date);
if (retrieved.Entities[i].Contains("new_code") && retrieved.Entities[i]["new_code"] != null)
dr["code"] = (string)retrieved.Entities[i]["new_code"];
if (retrieved.Entities[i].Contains("new_paymentstatus") && retrieved.Entities[i]["new_paymentstatus"] != null)
dr["paymentstatus"] = GetOptionsSetTextOnValue(crmService, "new_teacherpaymentrequest", "new_paymentstatus", ((OptionSetValue)retrieved.Entities[i]["new_paymentstatus"]).Value, 1065);
if (retrieved.Entities[i].Contains("new_totalteachinghours_ds") && retrieved.Entities[i]["new_totalteachinghours_ds"] != null)
dr["totalteachinghours_ds"] = ((decimal)retrieved.Entities[i]["new_totalteachinghours_ds"]).ToString("0.#####");
if (retrieved.Entities[i].Contains("new_teachersalary") && retrieved.Entities[i]["new_teachersalary"] != null)
dr["teachersalary"] = (((Money)retrieved.Entities[i]["new_teachersalary"]).Value).ToString("0.#####");
if (retrieved.Entities[i].Contains("new_deductions") && retrieved.Entities[i]["new_deductions"] != null)
dr["deductions"] = ((decimal)retrieved.Entities[i]["new_deductions"]).ToString("0.#####");
if (retrieved.Entities[i].Contains("new_theamountpaid") && retrieved.Entities[i]["new_theamountpaid"] != null)
dr["theamountpaid"] = (((Money)retrieved.Entities[i]["new_theamountpaid"]).Value).ToString("0.#####");
if (retrieved.Entities[i].Contains("new_typeofpayment") && retrieved.Entities[i]["new_typeofpayment"] != null)
dr["typeofpayment"] = GetOptionsSetTextOnValue(crmService, "new_teacherpaymentrequest", "new_typeofpayment", ((OptionSetValue)retrieved.Entities[i]["new_typeofpayment"]).Value, 1065);
if (retrieved.Entities[i].Contains("new_dateofpayment") && retrieved.Entities[i]["new_dateofpayment"] != null)
dr["dateofpayment"] = gregorianToShamsi(((DateTime)retrieved.Entities[i].Attributes["new_dateofpayment"]).Date);
dr["teacherpaymentrequestid"] = retrieved.Entities[i].Attributes["new_teacherpaymentrequestid"].ToString();
dtb.Rows.Add(dr);
}
}
#endregion
RequestTuitionListView.DataSource = dtb;
RequestTuitionListView.DataBind();
}
private static string ResolveOrderExpression(OrderExpression orderExpression, string qualColumnName)
{
StringBuilder sqlExpression = new StringBuilder();
switch (orderExpression.SortOrder)
{
case SortOrder.Asc:
sqlExpression.Append(qualColumnName + " ASC");
break;
case SortOrder.Desc:
sqlExpression.Append(qualColumnName + " DESC");
break;
default:
throw new Exception("Sort order is not specified.");
}
return sqlExpression.ToString();
}
protected virtual Expression VisitSimpleDbOrder(OrderExpression oex)
{
return oex;
}
private void FillRequestList()
{
#region Create Table
DataTable dtb = new DataTable();
dtb.Columns.Add("ticketnumber");
dtb.Columns.Add("title");
dtb.Columns.Add("casetypecode");
dtb.Columns.Add("description");
dtb.Columns.Add("statecode");
dtb.Columns.Add("state");
dtb.Columns.Add("incidentid");
dtb.Columns.Add("helpdesk_requesterconfirm");
dtb.Columns.Add("helpdesk_statusdescription");
#endregion
#region Get Record From Crm
QueryExpression query = new QueryExpression("incident");
query.ColumnSet = new ColumnSet(true);
OrderExpression _Order = new OrderExpression("createdon", OrderType.Descending);
query.Orders.Add(_Order);
FilterExpression _filterCustomer = new FilterExpression(LogicalOperator.And);
_filterCustomer.AddCondition("customerid", ConditionOperator.Equal, new Guid("{" + Session["Personnelid"].ToString() + "}"));
query.Criteria = new FilterExpression(LogicalOperator.And);
query.Criteria.AddFilter(_filterCustomer);
if (SelectType.SelectedValue.Trim() == "2" || SelectType.SelectedValue.Trim() == "3")
{
FilterExpression _filterState = new FilterExpression();
if (SelectType.SelectedValue.Trim() == "2")
{
_filterState.FilterOperator = LogicalOperator.And;
_filterState.AddCondition("statecode", ConditionOperator.Equal, 0);
}
else if (SelectType.SelectedValue.Trim() == "3")
{
_filterState.FilterOperator = LogicalOperator.Or;
_filterState.AddCondition("statecode", ConditionOperator.Equal, 1);
_filterState.AddCondition("statecode", ConditionOperator.Equal, 2);
}
query.Criteria.AddFilter(_filterState);
}
EntityCollection retrieved = crmService.RetrieveMultiple(query);
#endregion
#region Fill DataTable
if (retrieved != null && retrieved.Entities.Count > 0)
{
for (int i = 0; i < retrieved.Entities.Count; i++)
{
DataRow dr = dtb.NewRow();
dr["ticketnumber"] = retrieved.Entities[i].Attributes["ticketnumber"].ToString();
if (retrieved.Entities[i].Contains("helpdesk_requesterconfirm") && retrieved.Entities[i].Attributes["helpdesk_requesterconfirm"] != null)
dr["helpdesk_requesterconfirm"] = retrieved.Entities[i].Attributes["helpdesk_requesterconfirm"].ToString();
else
dr["helpdesk_requesterconfirm"] = false;
if (retrieved.Entities[i].Attributes.Contains("title"))
dr["title"] = retrieved.Entities[i].Attributes["title"].ToString();
int CaseType = 0;
if (retrieved.Entities[i].Attributes.Contains("casetypecode"))
{
CaseType = ((OptionSetValue)retrieved.Entities[i].Attributes["casetypecode"]).Value;
}
dr["casetypecode"] = GetOptionsSetTextOnValue(crmService, "incident", "casetypecode", CaseType, 1065);
if (retrieved.Entities[i].Attributes.Contains("description"))
dr["description"] = retrieved.Entities[i].Attributes["description"].ToString();
if (((OptionSetValue)retrieved.Entities[i].Attributes["statecode"]).Value == 0)
{
dr["statecode"] = getStatuscode(((OptionSetValue)retrieved.Entities[i].Attributes["statuscode"]).Value);
dr["state"] = 0;
}
else
{
dr["statecode"] = GetStateTextOnValue(crmService, "incident", "statecode", ((OptionSetValue)retrieved.Entities[i].Attributes["statecode"]).Value, 1065);
dr["state"] = ((OptionSetValue)retrieved.Entities[i].Attributes["statecode"]).Value;
}
dr["incidentid"] = retrieved.Entities[i].Attributes["incidentid"].ToString();
if (retrieved.Entities[i].Contains("helpdesk_statusdescription") && retrieved.Entities[i].Attributes["helpdesk_statusdescription"] != null)
dr["helpdesk_statusdescription"] = getStatusDesccode(((OptionSetValue)retrieved.Entities[i].Attributes["helpdesk_statusdescription"]).Value);
if (((OptionSetValue)retrieved.Entities[i].Attributes["statecode"]).Value == 1)
dr["helpdesk_statusdescription"] = getStatusDesccode(3);
dtb.Rows.Add(dr);
}
}
#endregion
RequestView.DataSource = dtb;
RequestView.DataBind();
}
private void FillTechnicalList()
{
#region Create Table
DataTable dtb = new DataTable();
dtb.Columns.Add("name");
dtb.Columns.Add("requestdate");
dtb.Columns.Add("owner");
dtb.Columns.Add("done1");
dtb.Columns.Add("requestdescription");
dtb.Columns.Add("donedescription");
dtb.Columns.Add("classstechnicalrequestid");
#endregion
#region Get Record From Crm
QueryExpression query = new QueryExpression("new_classstechnicalrequest");
query.ColumnSet = new ColumnSet(new string[] { "new_name", "createdon", "ownerid", "new_done1",
"new_requestdescription", "new_donedescription", "new_classstechnicalrequestid" });
query.Criteria.AddCondition("new_relatedclassid", ConditionOperator.Equal, new Guid(ClassId));
OrderExpression _Order = new OrderExpression("createdon", OrderType.Ascending);
query.Orders.Add(_Order);
EntityCollection retrieved = crmService.RetrieveMultiple(query);
#endregion
#region Fill DataTable
if (retrieved != null && retrieved.Entities.Count > 0)
{
for (int i = 0; i < retrieved.Entities.Count; i++)
{
DataRow dr = dtb.NewRow();
if (retrieved.Entities[i].Contains("new_name"))
dr["name"] = retrieved.Entities[i].Attributes["new_name"].ToString();
if (retrieved.Entities[i].Contains("createdon"))
dr["requestdate"] = gregorianToShamsi(((DateTime)retrieved.Entities[i].Attributes["createdon"]).Date);
if (retrieved.Entities[i].Contains("ownerid"))
dr["owner"] = ((EntityReference)retrieved.Entities[i]["ownerid"]).Name;
if (retrieved.Entities[i].Contains("new_done1"))
dr["done1"] = GetOptionsSetTextOnValue(crmService, "new_classstechnicalrequest", "new_done1", ((OptionSetValue)retrieved.Entities[i].Attributes["new_done1"]).Value, 1065);
if (retrieved.Entities[i].Contains("new_requestdescription"))
dr["requestdescription"] = retrieved.Entities[i].Attributes["new_requestdescription"].ToString();
if (retrieved.Entities[i].Contains("new_donedescription"))
dr["donedescription"] = retrieved.Entities[i].Attributes["new_donedescription"].ToString();
if (retrieved.Entities[i].Contains("new_classstechnicalrequestid"))
dr["classstechnicalrequestid"] = retrieved.Entities[i].Attributes["new_classstechnicalrequestid"].ToString();
dtb.Rows.Add(dr);
}
}
#endregion
TechnicalListView.DataSource = dtb;
TechnicalListView.DataBind();
}
private void FillStudentList()
{
#region Create Table
DataTable dtb = new DataTable();
dtb.Columns.Add("gendercode");
dtb.Columns.Add("studentnumber");
dtb.Columns.Add("name");
dtb.Columns.Add("relatedclass");
dtb.Columns.Add("relatedcompanyid");
dtb.Columns.Add("attendanceitemsid");
#endregion
#region Get Record From Crm
QueryExpression query = new QueryExpression("new_attendanceitems");
query.ColumnSet = new ColumnSet(new string[] { "new_name", "new_relatedclass", "new_relatedcompanyid", "new_relatedregistrationid",
"new_attendanceitemsid", "createdon", "new_relatedattendanceid", "new_studentid", "new_present"});
query.Criteria.AddCondition("new_relatedattendanceid", ConditionOperator.Equal, new Guid(meetingId));
OrderExpression _Order = new OrderExpression("new_studentid", OrderType.Ascending);
query.Orders.Add(_Order);
EntityCollection retrieved = crmService.RetrieveMultiple(query);
#endregion
#region Fill DataTable
if (retrieved != null && retrieved.Entities.Count > 0)
{
for (int i = 0; i < retrieved.Entities.Count; i++)
{
DataRow dr = dtb.NewRow();
Entity student = crmService.Retrieve("contact", ((EntityReference)retrieved.Entities[i].Attributes["new_studentid"]).Id, new ColumnSet(new string[] { "contactid", "gendercode", "new_studentnumber" }));
if (student.Contains("gendercode") && student["gendercode"] != null)
dr["gendercode"] = GetOptionsSetTextOnValue(crmService, "contact", "gendercode", ((OptionSetValue)student["gendercode"]).Value, 1065);
if (student.Contains("new_studentnumber") && student["new_studentnumber"] != null)
dr["studentnumber"] = (string)student["new_studentnumber"];
if (retrieved.Entities[i].Contains("new_studentid"))
dr["name"] = ((EntityReference)retrieved.Entities[i].Attributes["new_studentid"]).Name;
if (retrieved.Entities[i].Contains("new_relatedclass"))
dr["relatedclass"] = ((EntityReference)retrieved.Entities[i].Attributes["new_relatedclass"]).Name;
if (retrieved.Entities[i].Contains("new_relatedcompanyid") && (EntityReference)retrieved.Entities[i].Attributes["new_relatedcompanyid"] != null)
dr["relatedcompanyid"] = ((EntityReference)retrieved.Entities[i].Attributes["new_relatedcompanyid"]).Name;
if (retrieved.Entities[i].Contains("new_attendanceitemsid"))
dr["attendanceitemsid"] = (retrieved.Entities[i].Attributes["new_attendanceitemsid"]).ToString();
dtb.Rows.Add(dr);
}
}
#endregion
RequestMeetingDetailView.DataSource = dtb;
RequestMeetingDetailView.DataBind();
}
protected virtual void VisitOrder(OrderExpression expression)
{
OrderFields.Add(new OrderField() { FieldName = expression.FieldName, Descending = expression.Descending });
}
/// <summary>
/// This method is used to find a record and return the GUID of records matching the criteria
/// </summary>
/// <param name="entityName_">CRM name of the entity you want to look for</param>
/// <param name="primarykeyName">The attribute name containing the GUID for the entity (contactid,campaignid,...) that will be returned in an array</param>
///<param name="stringParams">The ArrayList containing the attributes names and their values. The research only work with string type attributes. You can use % to replace characters. A logical AND is used to concatenate the parameters in the search</param>
public ArrayList findEntity(string entityName_, string primarykeyName, ArrayList stringParams, string attribute_order)
{
ArrayList arrayResults = new ArrayList();
try
{
isconnected();
ColumnSet cols = new ColumnSet();
createStringConditionExpression(stringParams);
cols.Attributes.Add(primarykeyName);
m_filterExpression.FilterOperator = LogicalOperator.And;
OrderExpression oe = new OrderExpression();
oe.AttributeName = attribute_order;
oe.OrderType = OrderType.Descending;
QueryExpression query = new QueryExpression();
query.EntityName = entityName_;
query.ColumnSet = cols;
query.Criteria = m_filterExpression;
query.Orders.Add(oe);
RetrieveMultipleRequest retrieve = new RetrieveMultipleRequest();
retrieve.Query = query;
retrieve.ReturnDynamicEntities = true;
RetrieveMultipleResponse retrieved = (RetrieveMultipleResponse)m_crmService.Execute(retrieve);
int i = 0;
foreach (DynamicEntity rec in retrieved.BusinessEntityCollection.BusinessEntities)
{
Microsoft.Crm.Sdk.Key sp = (Microsoft.Crm.Sdk.Key)rec.Properties[primarykeyName];
arrayResults.Add(sp.Value);
i++;
}
if (i == 0) throw new entitynotfoundException();
return arrayResults;
}
catch (SoapException ex)
{
throw new InvalidPluginExecutionException("!SoapException!\n" + ex.Detail.SelectSingleNode("//description").InnerText);
}
catch (entitynotfoundException)
{
throw new entitynotfoundException();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
private void FillClassList(int page)
{
#region Create Table
DataTable dtb = new DataTable();
dtb.Columns.Add("name");
dtb.Columns.Add("typeofclass");
dtb.Columns.Add("showtimes");
dtb.Columns.Add("classstatus");
dtb.Columns.Add("vmname");
dtb.Columns.Add("teacherpassword");
dtb.Columns.Add("studentpassword");
dtb.Columns.Add("holdinglocationid");
dtb.Columns.Add("relatedlabid");
dtb.Columns.Add("startdate");
dtb.Columns.Add("starttime");
dtb.Columns.Add("endtime");
dtb.Columns.Add("classid");
#endregion
#region Get Record From Crm
QueryExpression query = new QueryExpression("new_class");
query.ColumnSet = new ColumnSet(new string[] { "new_name", "new_relatedcourseid", "new_holdinglocationid", "new_relatedlabid",
"new_startdate", "new_starttime", "new_endtime", "new_classid", "createdon", "new_minutesstart" , "new_minutesend",
"new_typeofclass", "new_showtimes", "new_classstatus","new_vmname","new_teacherpassword","new_studentpassword",});
query.Criteria.AddCondition("new_teacherid", ConditionOperator.Equal, new Guid("{" + Session["Personnelid"].ToString() + "}"));
query.Criteria.AddCondition("new_classstatus", ConditionOperator.NotEqual, 4);
query.Criteria.AddCondition("new_classstatus", ConditionOperator.NotEqual, 5);
OrderExpression _Order = new OrderExpression("new_startdate", OrderType.Ascending);
query.Orders.Add(_Order);
query.PageInfo = new PagingInfo();
query.PageInfo.ReturnTotalRecordCount = true;
query.PageInfo.Count = 10;
query.PageInfo.PageNumber = page;
query.PageInfo.PagingCookie = null;
EntityCollection retrieved = crmService.RetrieveMultiple(query);
#endregion
#region Fill DataTable
if (retrieved != null && retrieved.Entities.Count > 0)
{
for (int i = 0; i < retrieved.Entities.Count; i++)
{
DataRow dr = dtb.NewRow();
dr["name"] = retrieved.Entities[i].Attributes["new_name"].ToString();
if (retrieved.Entities[i].Contains("new_typeofclass") && retrieved.Entities[i]["new_typeofclass"] != null)
dr["typeofclass"] = GetOptionsSetTextOnValue(crmService, "new_class", "new_typeofclass", ((OptionSetValue)retrieved.Entities[i].Attributes["new_typeofclass"]).Value, 1065);
if (retrieved.Entities[i].Contains("new_showtimes") && retrieved.Entities[i]["new_showtimes"] != null)
dr["showtimes"] = GetOptionsSetTextOnValue(crmService, "new_class", "new_showtimes", ((OptionSetValue)retrieved.Entities[i].Attributes["new_showtimes"]).Value, 1065);
if (retrieved.Entities[i].Contains("new_classstatus") && retrieved.Entities[i]["new_classstatus"] != null)
dr["classstatus"] = GetOptionsSetTextOnValue(crmService, "new_class", "new_classstatus", ((OptionSetValue)retrieved.Entities[i].Attributes["new_classstatus"]).Value, 1065);
if (retrieved.Entities[i].Contains("new_vmname") && retrieved.Entities[i]["new_vmname"] != null)
dr["vmname"] = (string)retrieved.Entities[i].Attributes["new_vmname"];
if (retrieved.Entities[i].Contains("new_teacherpassword") && retrieved.Entities[i]["new_teacherpassword"] != null)
dr["teacherpassword"] = (string)retrieved.Entities[i].Attributes["new_teacherpassword"];
if (retrieved.Entities[i].Contains("new_studentpassword") && retrieved.Entities[i]["new_studentpassword"] != null)
dr["studentpassword"] = (string)retrieved.Entities[i].Attributes["new_studentpassword"];
if (retrieved.Entities[i].Contains("new_holdinglocationid"))
dr["holdinglocationid"] = ((EntityReference)retrieved.Entities[i].Attributes["new_holdinglocationid"]).Name;
if (retrieved.Entities[i].Contains("new_relatedlabid"))
dr["relatedlabid"] = ((EntityReference)retrieved.Entities[i].Attributes["new_relatedlabid"]).Name;
if (retrieved.Entities[i].Contains("new_startdate"))
dr["startdate"] = gregorianToShamsi((DateTime)retrieved.Entities[i].Attributes["new_startdate"]);
if (retrieved.Entities[i].Contains("new_starttime"))
dr["starttime"] = GetOptionsSetTextOnValue(crmService, "new_class", "new_starttime", ((OptionSetValue)retrieved.Entities[i].Attributes["new_starttime"]).Value, 1065)
+ ":" + GetOptionsSetTextOnValue(crmService, "new_class", "new_minutesstart", ((OptionSetValue)retrieved.Entities[i].Attributes["new_minutesstart"]).Value, 1065);
if (retrieved.Entities[i].Contains("new_endtime"))
dr["endtime"] = GetOptionsSetTextOnValue(crmService, "new_class", "new_endtime", ((OptionSetValue)retrieved.Entities[i].Attributes["new_endtime"]).Value, 1065)
+ ":" + GetOptionsSetTextOnValue(crmService, "new_class", "new_minutesend", ((OptionSetValue)retrieved.Entities[i].Attributes["new_minutesend"]).Value, 1065);
if (retrieved.Entities[i].Contains("new_classid"))
dr["classid"] = retrieved.Entities[i].Attributes["new_classid"].ToString();
dtb.Rows.Add(dr);
}
}
#endregion
if (retrieved.MoreRecords)
{
query.PageInfo.PagingCookie = retrieved.PagingCookie;
RequestClassView.VirtualItemCount = retrieved.TotalRecordCount;
}
RequestClassView.PageSize = 10;
RequestClassView.DataSource = dtb;
RequestClassView.DataBind();
}
