public void WhereClauseEmpty()
{
var firstName = string.Empty;
var lastName = string.Empty;
var ds = new DateSearch("Created", "", "");
var q = QueryBuilderFactory.Init("Person");
q.AddSelect("FirstName")
.AddSelect("LastName")
.AddLike("FirstName", firstName)
.AddLike("LastName", lastName)
.AddBetween(ds);
var pagingFactory = new PagingFactory(innerQuery: q, currentPage: 2, rowsPerPage: 10, orderBy: "FirstName");
var result = pagingFactory.CreateQuery();
Console.WriteLine(result);
var sqlParams = pagingFactory.CreateParameters();
Assert.IsTrue(sqlParams.Count() == 0);
var expectedNoEmptyWhere = "WHERE ";
Assert.IsFalse(result.Contains(expectedNoEmptyWhere));
var endAnd = AllTests.NoEmptyEndAnd(result);
Assert.IsTrue(endAnd.Result, endAnd.Msg);
}
public void DateBetweenNoStartDateOrEndDate()
{
// empty string for started and end date
var ds = new DateSearch("Created", "","");
var name = "Dave";
var qb = QueryBuilderFactory.Init("Table1");
qb.AddSelect("Name")
.AddWhere(name, "Name = @{0}")
.AddBetween(ds);
var innerQ = qb.Create().CreateQuery();
var pagingfactory = new PagingFactory(qb, 1, 10, name);
var result = pagingfactory.CreateQuery();
var sqlParams = pagingfactory.CreateParameters();
Console.WriteLine(innerQ);
Console.WriteLine(result);
foreach (var sqlParam in sqlParams)
{
Console.WriteLine(sqlParam.ToString());
}
var expected = "Name = @0 AND Created BETWEEN @1 and @2";
Assert.IsTrue(!innerQ.Contains(expected));
Assert.AreEqual(sqlParams.Count() ,1);
//Assert.AreEqual(name, sqlParams[0], "name");
//var date1 = sqlParams[1].ToString();
//var dat1Expected = DateSearch.SqlMin.ToString();
//Assert.AreEqual(dat1Expected,date1, "date1 failed");
//var date2 = sqlParams[2].ToString();
//var date2Expected = DateTime.Today;
//Assert.AreEqual(date2Expected, date2, "date2 failed");
}
public void DateBetweenSunnyDayInnerQueryTest()
{
var start = new DateTime(2011, 11, 5);
var end = new DateTime(2011, 12, 5);
var ds = new DateSearch("Created", start.ToShortDateString(), end.ToShortDateString());
const string name = "Dave";
var qb = QueryBuilderFactory.Init("Table1");
qb.AddSelect("Name")
.AddWhere(name, "Name = @{0}")
.AddBetween(ds);
var innerQ = qb.Create().CreateQuery();
var pagingfactory = new PagingFactory(qb, 1, 10, name);
var result = pagingfactory.CreateQuery();
var sqlParams = pagingfactory.CreateParameters();
Console.WriteLine(innerQ);
Console.WriteLine(result);
foreach (var sqlParam in sqlParams)
{
Console.WriteLine(sqlParam.ToString());
}
var expected = "Name = @0 AND Created BETWEEN @1 and @2";
Assert.IsTrue(innerQ.Contains(expected));
Assert.AreEqual(name, sqlParams[0], "name");
var date1 = sqlParams[1].ToString();
Assert.IsTrue(date1.Contains("11/5/2011"), "date1");
var date2 = sqlParams[2].ToString();
Assert.IsTrue(date2.Contains("12/5/2011"), "date2");
var endAnd= AllTests.NoEmptyEndAnd(result);
Assert.IsTrue(endAnd.Result,endAnd.Msg);
}
public void DateNoStartDate()
{
// empty string for started and end date
var ds = new DateSearch("Created", "", "12/5/2011");
var name = "Dave";
var qb = QueryBuilderFactory.Init("Table1");
qb.AddSelect("Name")
.AddWhere(name, "Name = @{0}")
.AddBetween(ds);
var innerQ = qb.Create().CreateQuery();
var pagingfactory = new PagingFactory(qb, 1, 10, name);
var result = pagingfactory.CreateQuery();
var sqlParams = pagingfactory.CreateParameters();
Console.WriteLine(innerQ);
Console.WriteLine(result);
foreach (var sqlParam in sqlParams)
{
Console.WriteLine(sqlParam.ToString());
}
var expected = "Name = @0 AND Created BETWEEN @1 and @2";
Assert.IsTrue(innerQ.Contains(expected));
Assert.AreEqual(sqlParams.Count(), 3);
Assert.AreEqual(name, sqlParams[0], "name");
var date1 = sqlParams[1].ToString();
var dat1Expected = "1/1/1753";
Assert.IsTrue(date1.Contains(dat1Expected), "date1 failed");
}
private void searchButton_Click(object sender, EventArgs e)
{
DateSearch aDateSearch = new DateSearch();
DateTime from = fromDateTimePicker.Value;
DateTime to = todateTimePicker.Value;
if (from > to)
{
MessageBox.Show("From date is greater then To date");
}
else
{
aDateSearch.FromDate = fromDateTimePicker.Text;
aDateSearch.ToDate = todateTimePicker.Text;
List <DateSearch> dateSearchResult = aDateSearchManager.searchDate(aDateSearch);
serchByDatelistView.Items.Clear();
int counter = 0;
foreach (DateSearch date in dateSearchResult)
{
ListViewItem item = new ListViewItem();
item.Text = (++counter).ToString();
item.SubItems.Add(date.ItemName);
item.SubItems.Add(date.Sell.ToString());
serchByDatelistView.Items.Add(item);
}
}
}
public void Succeeds()
{
var search = new DateSearch("Value");
var result = search.ApplyToQuery(Items().AsQueryable()).ToArray();
result.Length.Should().Be(6);
}
public void ApplyToQuery_EqualDateNullable_CorrectResultReturned()
{
var criteria = new DateSearch();
criteria.Property = "DateNullable";
criteria.TargetTypeName = typeof(SomeClass).AssemblyQualifiedName;
criteria.SearchTerm = new System.DateTime(2000, 1, 1);
criteria.SearchTerm2 = null;
criteria.Comparator = DateComparators.Equal;
Assert.Equal(2, criteria.ApplyToQuery(new Repository().GetQuery()).Count());
}
public override void BuildFechaFilter()
{
var criteria = new DateSearch();
criteria.Property = "Fecha";
criteria.TargetTypeName = typeof(Venta).AssemblyQualifiedName;
criteria.SearchTerm = new System.DateTime(2017, 10, 20);
criteria.SearchTerm2 = null;
criteria.Comparator = DateComparators.Equal;
AbstractSearches.Add(criteria);
}
public void Equal_Succeeds()
{
var search = new DateSearch("Value")
{
Is = DateSearch.Comparer.Equal,
Term1 = new DateTime(2020, 1, 1)
};
var result = search.ApplyToQuery(Items().AsQueryable()).ToArray();
result.Length.Should().Be(1);
result[0].Id.Should().Be(1);
}
private DateSearch BuildPropertyDate <T>(string property, string dateSearch1, string dateSearch2, string comparatorValue)
{
var teste = DateTime.TryParse(dateSearch1, out DateTime resultS1);
var otherTest = resultS1;
var criteria = new DateSearch();
criteria.Property = property;
criteria.TargetTypeName = typeof(T).AssemblyQualifiedName;
criteria.SearchTerm = ConvertDate(dateSearch1);
criteria.SearchTerm2 = ConvertDate(dateSearch2);
criteria.Comparator = (DateComparators)Enum.Parse(typeof(DateComparators), comparatorValue);
return(criteria);
}
//Load Date search Item on Grid vie
public DataTable LoadDateSearchItem(DateSearch dateSearch)
{
SqlConnection conn = new SqlConnection(connection.connectionDb);
//string query = @"SELECT ite.Name ItemName, sto.Quantity Quantity FROM StockOut sto INNER JOIN Items ite ON sto.ItemId = ite.Id WHERE Date BETWEEN '" + dateSearch.FromDate + "' AND '" + dateSearch.ToDate + "' AND MediumId = 1 ORDER BY sto.Id DESC";
string query = @"SELECT Name ItemName, SUM(Quantity) Quantity from StockoutDetail WHERE Date Between '" + Utility.Utility.GetSqlDateOnly(dateSearch.FromDate) + "' AND '" + Utility.Utility.GetSqlDateOnly(dateSearch.ToDate) + "' group by Name, Medium Having Medium = 'Sell' ORDER BY Quantity DESC";
SqlCommand command = new SqlCommand(query, conn);
conn.Open();
SqlDataReader sd = command.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(sd);
conn.Close();
return(dt);
}
public void Greater_Succeeds()
{
var search = new DateSearch("Value")
{
Is = DateSearch.Comparer.Greater,
Term1 = new DateTime(2020, 1, 3)
};
var result = search.ApplyToQuery(Items().AsQueryable()).ToArray();
result.Length.Should().Be(3);
result[0].Id.Should().Be(4);
result[1].Id.Should().Be(5);
result[2].Id.Should().Be(6);
}
public void InRange_Succeeds()
{
var search = new DateSearch("Value")
{
Is = DateSearch.Comparer.InRange,
Term1 = new DateTime(2020, 1, 2),
Term2 = new DateTime(2020, 1, 4)
};
var result = search.ApplyToQuery(Items().AsQueryable()).ToArray();
result.Length.Should().Be(3);
result[0].Id.Should().Be(2);
result[1].Id.Should().Be(3);
result[2].Id.Should().Be(4);
}
private void BuildFechaFilterQuery()
{
if (FilterEntitie.UsarFecha)
{
var criteria = new DateSearch
{
Property = "FechaCreacion",
TargetTypeName = typeof(SubPedidoEntry).AssemblyQualifiedName,
SearchTerm = FilterEntitie.Desde,
SearchTerm2 = FilterEntitie.Hasta,
Comparator = DateComparators.InRange
};
FilterQuerys.Add(criteria);
}
}
public void DateBetweenSunnyDayInnerQueryTest()
{
var start = new DateTime(2011, 11, 5);
var end = new DateTime(2011, 12, 5);
var ds = new DateSearch("Created", start.ToShortDateString(), end.ToShortDateString());
const string name = "Dave";
var qb = QueryBuilderFactory.Init("Table1");
qb.AddSelect("Name")
.AddWhere(name, "Name = @{0}")
.AddBetween(ds);
var innerQ = qb.Create().CreateQuery();
var pagingfactory = new PagingFactory(qb, 1, 10, name);
var result = pagingfactory.CreateQuery();
var sqlParams = pagingfactory.CreateParameters();
Console.WriteLine(innerQ);
Console.WriteLine(result);
foreach (var sqlParam in sqlParams)
{
Console.WriteLine(sqlParam.ToString());
}
var expected = "Name = @0 AND Created BETWEEN @1 and @2";
Assert.IsTrue(innerQ.Contains(expected));
Assert.AreEqual(name, sqlParams[0], "name");
var date1 = sqlParams[1].ToString();
Assert.IsTrue(date1.Contains("11/5/2011"), "date1");
var date2 = sqlParams[2].ToString();
Assert.IsTrue(date2.Contains("12/5/2011"), "date2");
var endAnd = AllTests.NoEmptyEndAnd(result);
Assert.IsTrue(endAnd.Result, endAnd.Msg);
}
public List <DateSearch> searchDate(DateSearch aDateSearch)
{
Query = "SELECT * FROM DateSearch Where Date BETWEEN @from and @to";
Command = new SqlCommand(Query, Connection);
Command.Connection.Open();
Command.Parameters.AddWithValue("from", aDateSearch.FromDate);
Command.Parameters.AddWithValue("to", aDateSearch.ToDate);
Reader = Command.ExecuteReader();
List <DateSearch> DateSearchList = new List <DateSearch>();
while (Reader.Read())
{
DateSearch DateSearch = new DateSearch();
DateSearch.ItemName = Reader["ItemName"].ToString();
DateSearch.Sell = (int)Reader["Sell"];
DateSearchList.Add(DateSearch);
}
Reader.Close();
Connection.Close();
return(DateSearchList);
}
private static AbstractSearch CreateSearchCriterion(Type targetType, Type propertyType, string property)
{
AbstractSearch result = null;
if (propertyType.IsCollectionType())
{
propertyType = propertyType.GetGenericArguments().First();
}
if (propertyType.IsEnum)
{
result = new EnumSearch(propertyType);
}
else if (propertyType == typeof(string))
{
result = new TextSearch();
}
else if (propertyType == typeof(bool) || propertyType == typeof(bool?))
{
result = new BooleanSearch();
}
else if (propertyType == typeof(byte) || propertyType == typeof(byte?))
{
result = new ByteSearch();
}
else if (propertyType == typeof(char) || propertyType == typeof(char?))
{
result = new CharacterSearch();
}
else if (propertyType == typeof(DateTime) || propertyType == typeof(DateTime?))
{
result = new DateSearch();
}
else if (propertyType == typeof(decimal) || propertyType == typeof(decimal?))
{
result = new DecimalSearch();
}
else if (propertyType == typeof(double) || propertyType == typeof(double?))
{
result = new DoubleSearch();
}
else if (propertyType == typeof(float) || propertyType == typeof(float?))
{
result = new FloatSearch();
}
else if (propertyType == typeof(int) || propertyType == typeof(int?))
{
result = new IntegerSearch();
}
else if (propertyType == typeof(long) || propertyType == typeof(long?))
{
result = new LongSearch();
}
else if (propertyType == typeof(sbyte) || propertyType == typeof(sbyte?))
{
result = new SByteSearch();
}
else if (propertyType == typeof(short) || propertyType == typeof(short?))
{
result = new ShortSearch();
}
else if (propertyType == typeof(uint) || propertyType == typeof(uint?))
{
result = new UnsignedIntegerSearch();
}
else if (propertyType == typeof(ulong) || propertyType == typeof(ulong?))
{
result = new UnsignedLongSearch();
}
else if (propertyType == typeof(ushort) || propertyType == typeof(ushort?))
{
result = new UnsignedShortSearch();
}
if (result != null)
{
result.Property = property;
result.TargetTypeName = targetType.AssemblyQualifiedName;
}
return(result);
}
public List <DateSearch> searchDate(DateSearch aDateSearch)
{
return(aDateSearchGateway.searchDate(aDateSearch));
}
public void DateNoEndReturnsToday()
{
// empty string for started and end date
var ds = new DateSearch("Created", "12/5/2011","");
var name = "Dave";
var qb = QueryBuilderFactory.Init("Table1");
qb.AddSelect("Name")
.AddWhere(name, "Name = @{0}")
.AddBetween(ds);
var innerQ = qb.Create().CreateQuery();
var pagingfactory = new PagingFactory(qb, 1, 10, name);
var result = pagingfactory.CreateQuery();
var sqlParams = pagingfactory.CreateParameters();
Console.WriteLine(innerQ);
Console.WriteLine(result);
foreach (var sqlParam in sqlParams)
{
Console.WriteLine(sqlParam.ToString());
}
var expected = "Name = @0 AND Created BETWEEN @1 and @2";
Assert.IsTrue(innerQ.Contains(expected));
Assert.AreEqual(sqlParams.Count(), 3);
Assert.AreEqual(name, sqlParams[0], "name");
var paramVal = sqlParams[2].ToString();
var date2 = new DateTime();
DateTime.TryParse(paramVal,out date2);
Assert.AreEqual(date2.ToShortDateString(),DateTime.Now.ToShortDateString(), "date2 failed");
}
