public List <RecordSearch> GetPartialRecordSearchesByCriteria(string criteria)
{
using (OleDbConnection connection = new OleDbConnection(ConnectionString))
{
using (OleDbCommand sqlCommand = connection.CreateCommand())
{
sqlCommand.CommandText = @"SELECT ID, ICPrefix, ICYear, ICEnumeration, ICSuffix, ProjectName, Status, LastUpdated FROM tblRecordSearches " + criteria;
connection.Open();
OleDbDataReader reader = sqlCommand.ExecuteReader();
List <RecordSearch> returnCollection = new List <RecordSearch>();
while (reader.Read())
{
int index = 0;
RecordSearch returnValue = new RecordSearch()
{
ID = reader.GetInt32Safe(index++),
ICTypePrefix = reader.GetStringSafe(index++),
ICYear = reader.GetStringSafe(index++),
ICEnumeration = reader.GetInt32Safe(index++),
ICSuffix = reader.GetStringSafe(index++),
ProjectName = reader.GetStringSafe(index++),
Status = reader.GetStringSafe(index++),
LastUpdated = reader.GetDateTimeSafe(index++)
};
returnCollection.Add(returnValue);
}
return(returnCollection);
}
}
}
public RecordSearch GetPartialRecordSearchesByCriteria(int id)
{
using (OleDbConnection connection = new OleDbConnection(ConnectionString))
{
using (OleDbCommand sqlCommand = connection.CreateCommand())
{
sqlCommand.CommandText = @"SELECT ID, ICPrefix, ICYear, ICEnumeration, ICSuffix, ProjectName, Status, LastUpdated FROM tblRecordSearches WHERE ID = " + id;
connection.Open();
OleDbDataReader reader = sqlCommand.ExecuteReader();
int index = 0;
RecordSearch returnValue = new RecordSearch()
{
ID = reader.GetInt32Safe(index++),
ICTypePrefix = reader.GetStringSafe(index++),
ICYear = reader.GetStringSafe(index++),
ICEnumeration = reader.GetInt32Safe(index++),
ICSuffix = reader.GetStringSafe(index++),
ProjectName = reader.GetStringSafe(index++),
Status = reader.GetStringSafe(index++),
LastUpdated = reader.GetDateTimeSafe(index++)
};
return(returnValue);
}
}
}
public RecordSearch GetRecordSearchByID(int id, bool loadAsCurrentSearch = true)
{
using (OleDbConnection connection = new OleDbConnection(ConnectionString))
{
using (OleDbCommand sqlCommand = connection.CreateCommand())
{
sqlCommand.CommandText = "SELECT ID, ICPrefix, ICYear, ICEnumeration, ICSuffix, " +
"DateReceived, DateEntered, DateOfResponse, DateBilled, DatePaid, LastUpdated, " +
"RequestorID, AdditionalRequestors, ClientID, MailingAddressID, IsMailingAddressSameAsBilling, BillingAddressID, " +
"ProjectName, RecordSearchType, Status, SpecialCaseDetails, " +
"MainCounty, AdditionalCounties, PLSS, Acres, LinearMiles, " +
"AreResourcesInProject, Recommendation, IsReportReceived, Processor, EncryptionPassword, " +
"FeeVersion, FeeID, TotalCost, " +
"ProjectNumber, InvoiceNumber, CheckName, CheckNumber, IsPrePaid, IsSelected, Notes " +
"FROM tblRecordSearches WHERE ID = " + id;
connection.Open();
OleDbDataReader reader = sqlCommand.ExecuteReader();
reader.Read();
int index = 0;
RecordSearch returnValue = new RecordSearch()
{
ID = reader.GetInt32Safe(index++), //0
ICTypePrefix = reader.GetStringSafe(index++),
ICYear = reader.GetStringSafe(index++),
ICEnumeration = reader.GetInt32Safe(index++),
ICSuffix = reader.GetStringSafe(index++),
DateReceived = reader.GetDateTimeSafe(index++), //5
DateEntered = reader.GetDateTimeSafe(index++),
DateOfResponse = reader.GetDateTimeSafe(index++),
DateBilled = reader.GetDateTimeSafe(index++),
DatePaid = reader.GetDateTimeSafe(index++),
LastUpdated = reader.GetDateTimeSafe(index++), //10
RequestorID = reader.GetInt32Safe(index),
Requestor = _ps.GetPersonByID(reader.GetInt32Safe(index++)),
AdditionalRequestors = reader.GetStringSafe(index++),
ClientID = reader.GetInt32Safe(index),
ClientModel = _cs.GetClientByID(reader.GetInt32Safe(index++)), //15
MailingAddress = _as.GetAddressByID(reader.GetInt32Safe(index++)),
IsMailingSameAsBilling = reader.GetBooleanSafe(index++),
BillingAddress = _as.GetAddressByID(reader.GetInt32Safe(index++)),
ProjectName = reader.GetStringSafe(index++),
RSType = reader.GetStringSafe(index++), //20
Status = reader.GetStringSafe(index++),
SpecialDetails = reader.GetStringSafe(index++),
MainCounty = reader.GetStringSafe(index++),
AdditionalCounties = ParseAdditionalCounties(reader.GetStringSafe(index++)),
PLSS = reader.GetStringSafe(index++), //25
Acres = reader.GetInt32Safe(index++),
LinearMiles = reader.GetInt32Safe(index++),
AreResourcesInProject = reader.GetBooleanSafe(index++),
Recommendation = reader.GetStringSafe(index++),
IsReportReceived = reader.GetBooleanSafe(index++), //30
Processor = reader.GetStringSafe(index++),
EncryptionPassword = reader.GetStringSafe(index++),
FeeVersion = reader.GetStringSafe(index++),
FeeID = reader.GetInt32Safe(index++),
TotalFee = reader.GetDecimalSafe(index++),
ProjectNumber = reader.GetStringSafe(index++),
InvoiceNumber = reader.GetStringSafe(index++),
CheckName = reader.GetStringSafe(index++),
CheckNumber = reader.GetStringSafe(index++),
IsPrePaid = reader.GetBooleanSafe(index++),
IsSelected = reader.GetBooleanSafe(index++),
Notes = reader.GetStringSafe(index++),
};
returnValue.Fee = GetFeeData(returnValue.FeeVersion, returnValue.FeeID);
if (returnValue.IsMailingSameAsBilling)
{
returnValue.BillingAddress = returnValue.MailingAddress;
}
if (loadAsCurrentSearch)
{
CurrentRecordSearch = returnValue;
}
return(returnValue);
}
}
}
