public void Should_return_empty_if_sqlWhereClause_is_null()
{
_where.Add(null);
string result = _builder.ToString();
result.ShouldBeEqualTo("");
}
public void WhereTest()
{
var Fmt = this.GetSqlFormatter();
var Where = new Where();
Where.Add(new ComparisonCondition("col1", ComparisonOperators.GreaterOrEqual, 32));
Where.Add(new ComparisonCondition("col2", ComparisonOperators.InsensitiveLike, "%part%"));
Where.Add(new SqlCondition("col3 IS NOT NULL"));
Assert.AreEqual("(col1>=32 AND col2 LIKE '%part%' AND col3 IS NOT NULL)", Fmt.SqlText(Where));
}
/// <summary>
/// errors are thrown on empty filed name,
/// if value1 or value2 is null
///
/// the expression will be skipped of both values are string.empty.No error thrown.
/// </summary>
/// <param name="fieldName"></param>
/// <param name="value1"></param>
/// <param name="value2"></param>
/// <returns></returns>
public IQueryBuilder AddBetween(string fieldName, string value1, string value2)
{
Contract.Requires <ArgumentNullException>(!string.IsNullOrWhiteSpace(fieldName));
Contract.Requires <ArgumentNullException>(value1 != null || value2 != null);
string betweenExpression = "{0} BETWEEN {1} AND {2}";
var wi = new WhereItem();
// if there is no property name we can't really do anything.
if (string.IsNullOrWhiteSpace(value1) && string.IsNullOrWhiteSpace(value2))
{
return(this);
}
//put the property name in there then send in the other spaces
var expression = string.Format(betweenExpression, fieldName, "{0}", "{1}");
wi.WhereExpression = expression;
var prms = new List <object>()
{
value1, value2
};
wi.SqlParams.AddRange(prms);
Where.Add(wi);
return(this);
}
private void Files_SelectedIndexChanged(object sender, EventArgs e)
{
string status = string.Empty;
Where.Nodes.Clear();
_checked.Clear();
if (Files.SelectedItem != null)
{
if (src.DuplicatesGroupByName.ContainsKey((string)Files.SelectedItem))
{
foreach (long index in src.DuplicatesGroupByName[(string)Files.SelectedItem])
{
foreach (DuplicateFileInfo s in src.DuplicatesByGroup[index])
{
if (s.FileName == Files.Text)
{
status = (string)Files.SelectedItem + " " + s.Length.ToFileSizeString();
Where.Add(s.FullPath);
foreach (DuplicateFileInfo o in src.DuplicatesByGroup[index])
{
Where.Add(o.FullPath);
}
}
}
}
}
Where.ExpandAll();
}
OnItemStatusUpdate?.Invoke(this, status);
}
public void SetWhere(LambdaExpression whereExpression)
{
if (whereExpression != null)
{
Where.Add(new QueryWhere(whereExpression));
}
}
public void SetWhere(string whereSql)
{
if (whereSql.NotNull())
{
Where.Add(new QueryWhere(whereSql));
}
}
public void SetWhere(LambdaExpression expression, QueryOperator queryOperator, INetSqlQueryable subQueryable)
{
if (subQueryable != null)
{
Where.Add(new QueryWhere(expression, queryOperator, subQueryable));
}
}
public QuerySQLHelper AddWhere(ColumnRelevanceMapper column)
{
if (column != null)
{
Where.Add(column);
}
return(this);
}
public IQueryBuilder AddWhere(string sqlParam, string expression)
{
Where.Add(new WhereItem()
{
SqlParam = sqlParam, WhereExpression = expression
});
return(this);
}
public IQueryBuilder AddBetween(DateSearch dateSearch)
{
var cmd = new DateSearchCommand();
var wi = cmd.CreateQuery(dateSearch);
Where.Add(wi);
return(this);
}
public void Add(ICondition condition)
{
if (_conditions == null)
{
Where.Add(condition);
}
else
{
_conditions.Add(condition);
}
}
/// <summary>
/// Prepara o comando antes de executar um select
/// </summary>
/// <param name="command">comando que será preparado antes da execução</param>
/// <param name="where">Filtro where adicional</param>
public override void PrepareReader(Command command, Where where)
{
base.PrepareReader(command, where);
string selectFields = "";
string fieldEmpresa = GetField("GUIDEmpresa");
if(!String.IsNullOrEmpty(fieldEmpresa))
{
selectFields += fieldEmpresa;
string parameterName = Parameter.FixParameterName(fieldEmpresa, "base");
where.Add($"{fieldEmpresa.Split(new string[] { " AS " })[0]} = {parameterName}", new Parameter
{
ParameterName = parameterName,
Value = Settings.EmpresaAtual.ID
});
}
selectFields += GetField("GUID");
selectFields += GetField("EGUID");
selectFields += GetField("DataHoraCadastro");
selectFields += GetField("DataHoraAlteracao");
selectFields += GetField("GUIDUsuarioCadastro");
selectFields += GetField("GUIDUsuarioAlteracao");
string s = where.FirstOrDefault(w => w.StartsWith("GUID", StringComparison.InvariantCultureIgnoreCase));
if(s != null)
{
string tableName = DbContext.FindTableDefinition(this).TableName;
where.Remove(s);
where.Add(tableName + "." + s);
}
command.CommandText = command.CommandText.Replace("{selectFields}", selectFields + " {selectFields}");
}
public IQueryBuilder AddLike(string fieldName, string likeVal, bool excludeIfValIsNullOrWhiteSpace = true)
{
if (string.IsNullOrWhiteSpace(likeVal))
{
System.Diagnostics.Trace.WriteLine(string.Format("{0} was not added to the like statement because the value was null", fieldName));
return(this);
}
var wi = new WhereItem()
{
WhereExpression = LikeExp(fieldName),
SqlParam = LikeSqlParam(likeVal)
};
Where.Add(wi);
return(this);
}
private Where BuildWhereFromIds(Type type, object[] ids)
{
IList <PropertyInfo> keyColumns = GetKeyColumns(type).Select(c => c.Value).ToList();
Where where = new Where
{
ReflectedType = (type)
};
for (int i = 0; i < ids.Length; i++)
{
where.Add(keyColumns[i].Name, Where.EQ, ids[i]);
}
return(where);
}
/// <summary>
/// adds to the where expression.
/// </summary>
/// <param name="sqlField">e.g.. FirstName</param>
/// <param name="operationSymbol"> greater than less than equal too</param>
/// <param name="paramValue">"Fred"</param>
/// <param name="skipOnEmptyParamValue"></param>
/// <returns></returns>
public IQueryBuilder AddWhere(string sqlField, string operationSymbol, string paramValue, bool skipOnEmptyParamValue = true)
{
//don't add this to the list.
if (skipOnEmptyParamValue && string.IsNullOrWhiteSpace(paramValue))
{
return(this);
}
var expression = string.Format(@" {0} {1} ", sqlField, operationSymbol) + " @{0} ";
Where.Add(new WhereItem()
{
SqlParam = paramValue, WhereExpression = expression
});
return(this);
}
protected void FillWhere(params NameValueCollection[] collections)
{
foreach (var collection in collections)
{
foreach (var item in collection)
{
var key = item.ToString();
if (Where.Any(p => p.Key.ToLower() == key.ToLower()) || string.IsNullOrEmpty(collection[key]) ||
key.ToLower() == "page" || key.ToLower() == "rows" || key.ToLower() == "order" || key.ToLower() == "sort")
{
continue;
}
Where.Add(key, collection[key].Trim());
}
}
}
private Where BuildWhereFromIds(object entity)
{
Type type = entity.GetType();
List <PropertyInfo> keyColumns = GetKeyColumns(type).Select(c => c.Value).ToList();
Where where = new Where
{
ReflectedType = (type)
};
for (int i = 0; i < keyColumns.Count; i++)
{
where.Add(keyColumns[i].Name, Where.EQ, GetValue(keyColumns[i], entity));
}
return(where);
}
/// <summary>
/// 添加Where
/// </summary>
/// <param name="key"></param>
/// <param name="value"></param>
/// <returns></returns>
public void Add(string key, object value)
{
if (Where == null)
{
Where = new Dictionary <string, object>();
}
if (Where.ContainsKey(key))
{
Where.Remove(key);
}
if (string.IsNullOrEmpty(key) || value == null || (value is string && string.IsNullOrEmpty(value.ToString())))
{
return;
}
Where.Add(key, value);
}
/// <summary>
/// Instancia um objeto e atribui as chaves como compos de condição
/// </summary>
/// <param name="pTableAdapter">Um table adapter</param>
/// <param name="pKeysAsWhere"></param>
public TableQuery(TableAdapter pTableAdapter, bool pKeysAsWhere = false)
: this()
{
Fields.AddRange(pTableAdapter.Collumns);
DbName = pTableAdapter.DBName;
TableName = pTableAdapter.TableName;
SchemaTable = pTableAdapter.SchemaTable;
WithoutNotLock = pTableAdapter.WithoutNotLock;
if (!pKeysAsWhere)
{
return;
}
foreach (var myItem in pTableAdapter.KeyFields)
{
Where.Add(new QueryParam(myItem));
}
}
/// <summary>
/// Retorna os lançamentos realizados no caixa informado para o usuário informado.
/// <para>Se não for informado o caixa ou o usuário, será buscado todos os lançamentos.</para>
/// </summary>
/// <param name="caixa">Caixa para pesquisar os lançamentos. Se nulo ou vazio irá trazer todos os caixas</param>
/// <param name="usuario">Usuário para filtrar os registros somente por ele. Se nulo ou vazio irá trazer para todos os usuários</param>
/// <param name="status">Status do lançamento. Se informado um valor inválido, não irá filtrar pelo status.</param>
/// <returns></returns>
public IList<IContaCorrenteCaixa> Find(ICaixa caixa, IUsuario usuario, StatusLancamento status)
{
Where w = new Where();
if(!caixa.IsNullOrEmpty())
w.Add("cxa_lancccaixa.guidcaixa", caixa.GUID);
if(!usuario.IsNullOrEmpty())
w.Add("cxa_lancccaixa.guidusuariooperador", usuario.GUID);
if(status.IsValid())
w.Add("cxa_lancccaixa.statuslancamento", (int)status);
return new Data.FrenteCaixa.Lancamento.LancamentoCaixa().Find<IContaCorrenteCaixa>(w);
}
private void sfModelo_OnSelectedValueChanged(object sender, ComponentModel.SearchField.SelectedItemArgs e)
{
sfAnoFabricacao.Enabled = true;
Where w = new Where();
w.Add("GUIDModelo", e.GUID);
sfAnoFabricacao.DataSource = new AnoFabricacao().GetDisplayValues(w);
}
/// <summary>
/// Este método vai ser util para filtrar os cadastros de Exportação, pois o mesmo
/// não possui uma espcialização e utiliza a mesma FieldDefinition da Leiaute Base.
/// Então aqui ele vai filtrar somente os especificos de cada leiaute.
/// </summary>
protected override void PrepareReader(Command command, Where where)
{
base.PrepareReader(command, where);
where.Add("Importacao", Importacao);
}
/// <summary>
/// Pesquisar todos os movimentos utilizando a SearchWindow
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnPesquisarMovimento_Click(object sender, EventArgs e)
{
IParentModel tipoMovimento = null;
// Aqui verifica o tipo de movimento que o usuário quer pesquisar
if (rbtDAV.Checked)
tipoMovimento = new DAV();
else if (rbtDAVOS.Checked)
tipoMovimento = new Data.Faturamento.Lancamento.Movimento.DAV.OS.DAV();
else
tipoMovimento = new PreVenda();
//Filtro para trazer somente os movimentos que estão com o status: Aberto
Where where = new Where();
where.Add("fat_lan.status = @status",
new Parameter
{
ParameterName = "@status",
Value = (int)Enuns.Faturamento.Lancamento.Status.Aberto
});
// Fazer a Pesquisa e pegar o GUID movimento selecionado
SearchWindowResult result = SearchWindow.Show(tipoMovimento, where);
if (!result.GUID.IsNullOrEmpty())
CarregarMovimento(result.GUID);
}
/// <summary>
/// <inheritdoc/>
/// </summary>
protected override void Prepare()
{
CheckKeyHasValue(Name);
CheckOptionsProperties();
Schema = DefaultWhereNull(Schema, () => "dbo");
DbTable = Root !.DbTables !.Where(x => x.Name == Name && x.Schema == Schema).SingleOrDefault();
if (DbTable == null)
{
throw new CodeGenException(this, nameof(Name), $"Specified Schema.Table '{Schema}.{Name}' not found in database.");
}
Alias = DefaultWhereNull(Alias, () => new string(StringConversion.ToSentenceCase(Name) !.Split(' ').Select(x => x.Substring(0, 1).ToLower(System.Globalization.CultureInfo.InvariantCulture).ToCharArray()[0]).ToArray()));
ViewName = DefaultWhereNull(ViewName, () => "vw" + Name);
ViewSchema = DefaultWhereNull(ViewSchema, () => Schema);
if (!string.IsNullOrEmpty(Permission) && Permission.Split(".", StringSplitOptions.RemoveEmptyEntries).Length == 1)
{
Permission += ".Read";
}
ColumnNameIsDeleted = DefaultWhereNull(ColumnNameIsDeleted, () => Root !.ColumnNameIsDeleted);
ColumnNameTenantId = DefaultWhereNull(ColumnNameTenantId, () => Root !.ColumnNameTenantId);
ColumnNameOrgUnitId = DefaultWhereNull(ColumnNameOrgUnitId, () => Root !.ColumnNameOrgUnitId);
ColumnNameRowVersion = DefaultWhereNull(ColumnNameRowVersion, () => Root !.ColumnNameRowVersion);
ColumnNameCreatedBy = DefaultWhereNull(ColumnNameCreatedBy, () => Root !.ColumnNameCreatedBy);
ColumnNameCreatedDate = DefaultWhereNull(ColumnNameCreatedDate, () => Root !.ColumnNameCreatedDate);
ColumnNameUpdatedBy = DefaultWhereNull(ColumnNameUpdatedBy, () => Root !.ColumnNameUpdatedBy);
ColumnNameUpdatedDate = DefaultWhereNull(ColumnNameUpdatedDate, () => Root !.ColumnNameUpdatedDate);
ColumnNameDeletedBy = DefaultWhereNull(ColumnNameDeletedBy, () => Root !.ColumnNameDeletedBy);
ColumnNameDeletedDate = DefaultWhereNull(ColumnNameDeletedDate, () => Root !.ColumnNameDeletedDate);
PrepareJoins();
if (Order != null && Order.Count > 0)
{
foreach (var order in Order)
{
order.Prepare(Root !, this);
}
}
foreach (var c in DbTable.Columns)
{
if (c.IsPrimaryKey)
{
var cc = new QueryColumnConfig {
Name = c.Name, DbColumn = c
};
cc.Prepare(Root !, this);
PrimaryKeyColumns.Add(cc);
}
if ((ExcludeColumns == null || !ExcludeColumns.Contains(c.Name !)) && (IncludeColumns == null || IncludeColumns.Contains(c.Name !)))
{
var cc = new QueryColumnConfig {
Name = c.Name, DbColumn = c
};
var ca = AliasColumns?.Where(x => x.StartsWith(c.Name + "^", StringComparison.Ordinal)).FirstOrDefault();
if (ca != null)
{
var parts = ca.Split("^", StringSplitOptions.RemoveEmptyEntries);
if (parts.Length == 2)
{
cc.NameAlias = parts[1];
}
}
cc.Prepare(Root !, this);
Columns.Add(cc);
}
}
if (Where == null)
{
Where = new List <QueryWhereConfig>();
}
if (ColumnTenantId != null)
{
Where.Add(new QueryWhereConfig {
Statement = $"[{Alias}].[{ColumnTenantId.Name}] = dbo.fnGetTenantId(NULL)"
});
}
if (ColumnIsDeleted != null)
{
Where.Add(new QueryWhereConfig {
Statement = $"([{Alias}].[{ColumnIsDeleted.Name}] IS NULL OR [{Alias}].[{ColumnIsDeleted.Name}] = 0)"
});
}
if (!string.IsNullOrEmpty(Permission))
{
Where.Add(new QueryWhereConfig {
Statement = $"{Root!.GetUserPermissionSql}(NULL, NULL, '{Permission.ToUpperInvariant()}', NULL) = 1"
});
/// <summary>
/// Prepara o comando antes de executar um select
/// </summary>
/// <param name="command">comando que será preparado antes da execução</param>
/// <param name="where">Filtro where adicional</param>
protected override void PrepareReader(Command command, Where where)
{
base.PrepareReader(command, where);
where.Add("fat_Lan.Tipo", (int)Tipo);
}
/// <summary>
/// Executas as validações impostas pela classe
/// </summary>
/// <param name="updating">Determina se é update ou insert</param>
public override void Validate(bool updating)
{
base.Validate(updating);
Where where = new Where();
object value = null;
foreach (IRegraFiltro item in Filtros)
{
//Pesquisar todos os filtros da regra de análise de crédito
value = item.Cliente.IsNullOrEmpty() ? null : (object)item.Cliente.GUID;
where.Add(string.Format("cad_AnaliseCreditoRegraFiltro.GUIDCliente {0} @p1", value == null ? "is" : "="), new Parameter
{
ParameterName = "@p1",
Value = value
});
//Procurar as regras
IList<IRegra> list = new Data.Regra.AnaliseCredito.Regra().Find<IRegra>(where);
if (list.Count(w => w.GUID != GUID) > 0)
throw new FilterAlreadyExists(Convert.ToString(list[0].Descricao));
}
}
/// <summary>
/// Adds the specified filtering field.
/// </summary>
/// <param name="field">The filtering field.</param>
/// <param name="value">The filtering value.</param>
/// <param name="operation">The filtering operation.</param>
public void Add(string field, object value, FilterOperation operation = FilterOperation.Equal)
{
var filterItem = new FilterItem(field, value, operation);
Where.Add(filterItem);
}
/// <summary>
/// Método para efetuar pesquisa de acordo com o digitado
/// </summary>
/// <returns>retorna uma lista de objetos</returns>
private IList<object[]> Pesquisa()
{
try
{
Wait.Show();
IList<object[]> result = null;
if(_dataSource.IsDynamicPaging)
{
#region sql
Where where = new Where();
string nome = "";
string value = "";
string condicao = "";
foreach(Control item in grid.Controls
.Cast<Control>()
.Where(w => w is TextBoxFilter &&
!String.IsNullOrEmpty(w.Text)))
{
TextBoxFilter filter = item as TextBoxFilter;
nome = filter.SourceColumn;
value = item.Text;
Parameter param = PrepareValueToFilter(nome, value, out condicao);
where.Add(condicao, param);
}
_dataSource.ApplyFilters(where);
result = DataSource.Values;
#endregion
}
else
{
#region linq
//Começa do valor 1 pois a coluna zero sempre será o GUID
int indice = 1;
result = DataSource.Values;
foreach(TextBoxFilter text in grid.Controls.OfType<TextBoxFilter>())
{
if(text.Text.Length > 0)
result = (from j in result
where VerificaPesquisa(j, text.Text, indice)
select j).ToList();
indice++;
}
#endregion
}
return result;
}
finally
{
Wait.Close();
}
}
public PageList <T> AddWhere(String fieldName, String condition, Object value)
{
Where.Add(fieldName, condition, value);
return(this);
}
protected override void PrepareReader(Command command, Where where)
{
base.PrepareReader(command, where);
//-------------------------------------------------------------------------
// Filtrar pelo tipo de item especial
//-------------------------------------------------------------------------
where.Add("cad_ItemEsp.Tipo", (int)Tipo);
}
/// <summary>
/// Pesquisa os voucher disponíveis
/// </summary>
/// <param name="sender">Objeto que disparou o evento</param>
/// <param name="e">Argumento do evento gerado</param>
private void btnPesquisar_Click(object sender, EventArgs e)
{
Where where = new Where();
where.Add("fat_lan.status = @status",
new Parameter
{
ParameterName = "@status",
Value = (int)Enuns.Faturamento.Lancamento.Status.Aberto
});
IDevolucao devolucao = new Devolucao();
//Procura somente os vouchers no qual o status está aberto
SearchWindowResult result = SearchWindow.Show(devolucao, where);
//Se encontrou carrega os valores do número e valor do voucher
if (!result.GUID.IsNullOrEmpty())
{
devolucao = Data.Abstract.Faturamento.Lancamento.LancamentoBase.Create(result.GUID, false) as IDevolucao;
txtVoucher.Text = devolucao.EGUID.ToString();
mnValor.Value = devolucao.ValorTotalLiquido;
mnValor.Enabled = false;
}
}
public override void Validate(bool updating)
{
base.Validate(updating);
#region vars
Where where = new Where();
object value = null;
#endregion
foreach (IDescontoRegraFiltro filtro in Filtros)
{
//-------------------------------------------------------------------------
// Pesquisar por todos os campos do fitro
//-------------------------------------------------------------------------
value = filtro.Cliente.IsNullOrEmpty() ? null : (object)filtro.Cliente.GUID;
where.Add(String.Format("(cad_DescontoRegraFiltro.GUIDCliente = @p1 OR cad_DescontoRegraFiltro.GUIDCliente IS NULL)", value), new Parameter
{
ParameterName = "@p1",
Value = value
});
value = filtro.CondicaoPagamento.IsNullOrEmpty() ? null : (object)filtro.CondicaoPagamento.GUID;
where.Add(String.Format("(cad_DescontoRegraFiltro.GUIDCondPgto = @p2 OR cad_DescontoRegraFiltro.GUIDCondPgto IS NULL)", value ), new Parameter
{
ParameterName = "@p2",
Value = value
});
value = filtro.Estado.IsNullOrEmpty() ? null : (object)filtro.Estado.GUID;
where.Add(String.Format("(cad_DescontoRegraFiltro.GUIDEstado = @p3 OR cad_DescontoRegraFiltro.GUIDEstado IS NULL)", value ), new Parameter
{
ParameterName = "@p3",
Value = value
});
value = filtro.Fabricante.IsNullOrEmpty() ? null : (object)filtro.Fabricante.GUID;
where.Add(String.Format("(cad_DescontoRegraFiltro.GUIDFabricante = @p4 OR cad_DescontoRegraFiltro.GUIDFabricante IS NULL)", value), new Parameter
{
ParameterName = "@p4",
Value = value
});
value = filtro.GrupoItem.IsNullOrEmpty() ? null : (object)filtro.GrupoItem.GUID;
where.Add(String.Format("(cad_DescontoRegraFiltro.GUIDGrupoItem = @p5 OR cad_DescontoRegraFiltro.GUIDGrupoItem IS NULL)", value ), new Parameter
{
ParameterName = "@p5",
Value = value
});
value = filtro.Item.IsNullOrEmpty() ? null : (object)filtro.Item.GUID;
where.Add(String.Format("(cad_DescontoRegraFiltro.GUIDItem = @p6 OR cad_DescontoRegraFiltro.GUIDItem IS NULL)", value ), new Parameter
{
ParameterName = "@p6",
Value = value
});
value = filtro.Quantidade;
where.Add(String.Format("(cad_DescontoRegraFiltro.Quantidade = @p7 OR cad_DescontoRegraFiltro.Quantidade IS NULL)", value ), new Parameter
{
ParameterName = "@p7",
Value = value
});
value = filtro.Vendedor.IsNullOrEmpty() ? null : (object)filtro.Vendedor.GUID;
where.Add(String.Format("(cad_DescontoRegraFiltro.GUIDVendedor = @p8 OR cad_DescontoRegraFiltro.GUIDVendedor IS NULL)", value ), new Parameter
{
ParameterName = "@p8",
Value = value
});
IList<IDescontoRegra> regras = new DescontoRegra().Find<IDescontoRegra>(where);
//-------------------------------------------------------------------------
// Localizar a lista de regras
//-------------------------------------------------------------------------
if (regras.Count(w => w.GUID != GUID) > 0)
{
throw new Exceptions.Regra.FilterAlreadyExists(Convert.ToString(regras[0].Descricao));
}
}
}
public void Add(ICondition condition)
{
Where.Add(condition);
}
