public IEnumerable<DbSearchResultModel> Search(DbSearchRequestModel dbSearchRequestModel)
{
var dbResult = new List<DbSearchResultModel>();
for (int i = 211; i < 222; i++)
{
dbResult.Add(new DbSearchResultModel() { EntryDateTime = DateTime.Now, VehicleNumber = "TN 07 J 1234" });
}
return dbResult;
}
public void Search(object arg)
{
var startHour = int.Parse(this.StartTimeSpanViewModel.SelectedHour);
if(this.StartTimeSpanViewModel.SelectedSession == "PM")
{
startHour += 12;
}
var endHour = int.Parse(this.EndTimeSpanViewModel.SelectedHour);
if (this.EndTimeSpanViewModel.SelectedSession == "PM")
{
endHour += 12;
}
var startDateWithTime = new DateTime(this.StartDateTime.Year, this.StartDateTime.Month, this.StartDateTime.Day,
startHour, int.Parse(this.StartTimeSpanViewModel.SelectedMinute), 0);
var endDateWithTime = new DateTime(this.EndDateTime.Year, this.EndDateTime.Month, this.EndDateTime.Day,
endHour, int.Parse(this.EndTimeSpanViewModel.SelectedMinute), 0);
var dalSearchModel = new DbSearchRequestModel()
{
StaDateTime = startDateWithTime,
EnDateTime = endDateWithTime,
VehicleNumber = this.VehicleNumber
};
var listDbResultModels = this._dataAccessLayer.Search(dalSearchModel);
// Update the View
this.SearchDataList.Clear();
foreach (var dbSearchResultModel in listDbResultModels)
{
this.SearchDataList.Add(new SearchDataViewItem(this._dataAccessLayer)
{
ID = dbSearchResultModel.UniqueId,
Date = dbSearchResultModel.EntryDateTime,
VehicleNumber = dbSearchResultModel.VehicleNumber
});
}
}
public IEnumerable<DbSearchResultModel> Search(DbSearchRequestModel dbSearchRequestModel)
{
/*
SELECT *
FROM scduvss.vehicle_entry_info
WHERE number = "TN 07 1234"
AND entrytime >= '2015-10-01' AND entrytime < '2015-11-01'
AND DATE_FORMAT(entrytime, '%H:%i:s') >= '10:00:00' AND DATE_FORMAT(entrytime, '%H:%i:s') > '22:00:00'
*/
lock (LockObject)
{
var dbSearchResults = new List<DbSearchResultModel>();
try
{
if (this.Open())
{
var mySqlCommand = this.connection.CreateCommand();
mySqlCommand.CommandText =
"SELECT * FROM vehicle_entry_info " +
"WHERE number = @vehicleNumber " +
"AND entrytime >= @entryDateGreaterThan AND entrytime < @entryDateLessThan ";
// TODO: Fix Time
//+"AND DATE_FORMAT(entrytime, '%H:%i:s') >= @entryTimeGreaterThan AND DATE_FORMAT(entrytime, '%H:%i:s') < @entryTimeLessThan";
mySqlCommand.Parameters.AddWithValue("@vehicleNumber", dbSearchRequestModel.VehicleNumber);
mySqlCommand.Parameters.AddWithValue("@entryDateGreaterThan",
dbSearchRequestModel.StaDateTime.GetDatePortion());
mySqlCommand.Parameters.AddWithValue("@entryDateLessThan",
dbSearchRequestModel.EnDateTime.GetDatePortion());
/* TODO: Fix time filter
mySqlCommand.Parameters.AddWithValue("@entryTimeGreaterThan",
dbSearchRequestModel.StaDateTime.GetTimePortion());
mySqlCommand.Parameters.AddWithValue("@entryTimeLessThan",
dbSearchRequestModel.EnDateTime.GetTimePortion());
*/
var dataTable = new DataTable();
var adapter = new MySqlDataAdapter(mySqlCommand);
adapter.Fill(dataTable);
dbSearchResults.AddRange(
from DataRow dr in dataTable.Rows
select new DbSearchResultModel
{
UniqueId = dr["id"].ToString(),
EntryDateTime = (DateTime) dr["entrytime"],
VehicleNumber = dr["number"].ToString()
}
);
}
return dbSearchResults;
}
catch (Exception ex)
{
Logger.Error("Search Failed..", ex);
return null;
}
}
}
public IEnumerable<DbSearchResultModel> Search(DbSearchRequestModel dbSearchRequestModel)
{
return this._databaseProvider.Search(dbSearchRequestModel);
}
public void TestSearchVehicleData()
{
/*
SELECT *
FROM scduvss.vehicle_entry_info
WHERE number = "TN 07 1234"
AND entrytime >= '2015-10-01' AND entrytime < '2015-11-01'
AND DATE_FORMAT(entrytime, '%H:%i:s') >= '10:00:00' AND DATE_FORMAT(entrytime, '%H:%i:s') > '22:00:00'
*/
var dbSearchRequestModel = new DbSearchRequestModel
{
StaDateTime = new DateTime(2015, 10, 01, 10, 00, 00),
EnDateTime = new DateTime(2015, 11, 01, 22, 00, 00),
VehicleNumber = "TN 07 1234"
};
var mySqlProvider = new MySqlDatabaseProvider
{
ConnectionString = ValidConnectionString
};
var searchResult = mySqlProvider.Search(dbSearchRequestModel);
Assert.IsTrue(searchResult.Any());
}
