//public List<ReferenceItem> GetCommonNameList()
//{
// List<ReferenceItem> referenceItemList = new List<ReferenceItem>();
// try
// {
// var results = this._dataContext.usp_PVPCommonNames_Select().ToList();
// if (results != null)
// {
// if (results.Count() > 0)
// {
// foreach (var result in results)
// {
// referenceItemList.Add(new ReferenceItem { Context = "scientific-name", ID = result.id, Title = result.title + " (" + result.count.ToString() + ")" });
// }
// }
// }
// }
// catch (Exception ex)
// {
// throw ex;
// }
// return referenceItemList;
//}
//public List<ReferenceItem> GetExpirationPeriodList()
//{
// List<ReferenceItem> referenceItemList = new List<ReferenceItem>();
// try
// {
// var results = this._dataContext.usp_PVPExpirationPeriods_Select().ToList();
// if (results != null)
// {
// if (results.Count() > 0)
// {
// foreach (var result in results)
// {
// referenceItemList.Add(new ReferenceItem { Context = "expiration-period", ID = result.sort_order, Title = result.category + " (" + result.count.ToString() + ")" });
// }
// }
// }
// }
// catch (Exception ex)
// {
// throw ex;
// }
// return referenceItemList;
//}
public PVPApplication GetPVPApplication(int id)
{
PVPApplication pVPApplication = new PVPApplication();
try
{
//var result =
// _dataContext.usp_ARS_PVPApplication_Select("");
//if (result != null)
//{
// pVPApplication.ApplicationNumber = result.pvp_application_number;
// pVPApplication.CultivarName = result.cultivar_name;
// pVPApplication.ExperimentalName = result.experimental_name;
// pVPApplication.ScientificName = result.scientific_name;
// pVPApplication.CommonName = result.common_name;
// pVPApplication.ApplicantName = result.applicant_name;
// pVPApplication.ApplicationDate = result.application_date.GetValueOrDefault();
// pVPApplication.IsCertifiedSeed = result.is_certified_seed.GetValueOrDefault();
// pVPApplication.ApplicationStatus = result.application_status;
// pVPApplication.ApplicationStatusDate = result.status_date.GetValueOrDefault();
// pVPApplication.CertificateIssuedDate = result.certificate_issued_date.GetValueOrDefault();
// pVPApplication.YearsProtected = result.years_protected.GetValueOrDefault();
// if (result.expiration_date != null)
// {
// pVPApplication.ExpirationDate = DateTime.Parse(result.expiration_date);
// }
// pVPApplication.AccessionID = result.accession_id.GetValueOrDefault();
//}
}
catch (Exception e)
{
throw e;
}
return(pVPApplication);
}
public List <PVPApplication> Search(Search search)
{
const string WHERE_CLAUSE_BASE = "WHERE ";
StringBuilder sbSqlWhereClause = new StringBuilder();
StringBuilder sbSqlWhereClauseAppStatuses = new StringBuilder();
StringBuilder sbSqlWhereClauseCertStatuses = new StringBuilder();
List <PVPApplication> pVPCertificateRecords = new List <PVPApplication>();
try
{
if (search.SearchCriteria.Where(x => x.FieldName == "pvp_application_number").Count() > 0)
{
sbSqlWhereClause.Append("pvp_application_number = ");
sbSqlWhereClause.Append(search.SearchCriteria.Where(x => x.FieldName == "pvp_application_number").First().FieldValue);
}
else
{
// TODO: Iterate through search criteria; construct WHERE clause. Default is AND; app statuses and cert statuses, respectively, use OR.
foreach (var result in search.SearchCriteria)
{
if (sbSqlWhereClause.Length > 0)
{
sbSqlWhereClause.Append(" AND ");
}
sbSqlWhereClause.Append(result.FieldName);
sbSqlWhereClause.Append(" LIKE ");
sbSqlWhereClause.Append("'%");
sbSqlWhereClause.Append(result.FieldValue);
sbSqlWhereClause.Append("%'");
}
//if (search.ApplicationStatuses != null)
//{
// if (search.ApplicationStatuses.Length > 0)
// {
// sbSqlWhereClauseAppStatuses.Append(" AND ");
// sbSqlWhereClauseAppStatuses.Append("(");
// foreach (string status in search.ApplicationStatuses)
// {
// sbSqlWhereClauseAppStatuses.Append(" OR ");
// }
// sbSqlWhereClause.Append(")");
// sbSqlWhereClause.Append(sbSqlWhereClauseAppStatuses);
// }
//}
//// TODO: CERT STATUSES
//if (search.CertificateStatuses.Length > 0)
//{
// foreach (string status in search.CertificateStatuses)
// {
// }
//}
}
sbSqlWhereClause.Insert(0, WHERE_CLAUSE_BASE);
var results = _dataContext.usp_ARS_PVPApplications_Search(sbSqlWhereClause.ToString()).ToList();
if (results != null)
{
foreach (var result in results)
{
PVPApplication pVPCertificateRecord = new PVPApplication();
pVPCertificateRecord.ApplicationNumber = result.pvp_application_number.GetValueOrDefault();
pVPCertificateRecord.CommonName = result.common_name;
pVPCertificateRecord.CultivarName = result.cultivar_name;
pVPCertificateRecord.ScientificName = result.scientific_name;
pVPCertificateRecord.ApplicantName = result.applicant_name;
pVPCertificateRecord.ApplicationStatus = result.application_status;
pVPCertificateRecords.Add(pVPCertificateRecord);
}
}
}
catch (Exception e)
{
throw e;
}
return(pVPCertificateRecords);
}
public List <PVPApplication> GetPVPApplications(string context)
{
List <PVPApplication> pVPApplications = new List <PVPApplication>();
try
{
switch (context)
{
case "expiring":
var resultsExpiringApplications = _dataContext.usp_ARS_PVPExpiring6Months_Select().ToList();
foreach (var result in resultsExpiringApplications)
{
PVPApplication pVPApplication = new PVPApplication();
pVPApplication.ApplicationNumber = result.pvp_application_number;
pVPApplication.CultivarName = result.cultivar_name;
pVPApplication.ExperimentalName = result.experimental_name;
pVPApplication.ScientificName = result.scientific_name;
pVPApplication.CommonName = result.common_name;
pVPApplication.ApplicantName = result.applicant_name;
pVPApplication.ApplicationDate = result.application_date.GetValueOrDefault();
pVPApplication.IsCertifiedSeed = result.is_certified_seed.GetValueOrDefault();
pVPApplication.ApplicationStatus = result.application_status;
pVPApplication.CertificateIssuedDate = result.certificate_issued_date.GetValueOrDefault();
pVPApplication.YearsProtected = result.years_protected.GetValueOrDefault();
if (result.expiration_date != null)
{
pVPApplication.ExpirationDate = DateTime.Parse(result.expiration_date);
}
pVPApplication.AccessionID = result.accession_id.GetValueOrDefault();
pVPApplications.Add(pVPApplication);
}
break;
case "recent":
var resultsRecentApplications = _dataContext.usp_ARS_PVPRecentApplications_Select().ToList();
foreach (var result in resultsRecentApplications)
{
PVPApplication pVPApplication = new PVPApplication();
pVPApplication.ApplicationNumber = result.pvp_application_number;
pVPApplication.CultivarName = result.cultivar_name;
pVPApplication.ExperimentalName = result.experimental_name;
pVPApplication.ScientificName = result.scientific_name;
pVPApplication.CommonName = result.common_name;
pVPApplication.ApplicantName = result.applicant_name;
pVPApplication.ApplicationDate = result.application_date.GetValueOrDefault();
pVPApplication.IsCertifiedSeed = result.is_certified_seed.GetValueOrDefault();
pVPApplication.ApplicationStatus = result.application_status;
pVPApplication.CertificateIssuedDate = result.certificate_issued_date.GetValueOrDefault();
pVPApplication.YearsProtected = result.years_protected.GetValueOrDefault();
if (result.expiration_date != null)
{
pVPApplication.ExpirationDate = DateTime.Parse(result.expiration_date);
}
pVPApplication.AccessionID = result.accession_id.GetValueOrDefault();
pVPApplications.Add(pVPApplication);
}
break;
case "recently-expired":
var resultsRecentlyExpiredApplications = _dataContext.usp_ARS_PVPRecentlyExpired_Select().ToList();
foreach (var result in resultsRecentlyExpiredApplications)
{
PVPApplication pVPApplication = new PVPApplication();
pVPApplication.ApplicationNumber = result.pvp_application_number;
pVPApplication.CultivarName = result.cultivar_name;
pVPApplication.ExperimentalName = result.experimental_name;
pVPApplication.ScientificName = result.scientific_name;
pVPApplication.CommonName = result.common_name;
pVPApplication.ApplicantName = result.applicant_name;
pVPApplication.ApplicationDate = result.application_date.GetValueOrDefault();
pVPApplication.IsCertifiedSeed = result.is_certified_seed.GetValueOrDefault();
pVPApplication.ApplicationStatus = result.application_status;
pVPApplication.CertificateIssuedDate = result.certificate_issued_date.GetValueOrDefault();
pVPApplication.YearsProtected = result.years_protected.GetValueOrDefault();
if (result.expiration_date != null)
{
pVPApplication.ExpirationDate = DateTime.Parse(result.expiration_date);
}
pVPApplication.AccessionID = result.accession_id.GetValueOrDefault();
pVPApplications.Add(pVPApplication);
}
break;
case "recently-available":
var resultsRecentlyAvailable = _dataContext.usp_ARS_PVPRecentlyAvailable_Select().ToList();
foreach (var result in resultsRecentlyAvailable)
{
PVPApplication pVPApplication = new PVPApplication();
pVPApplication.ApplicationNumber = result.pvp_application_number;
pVPApplication.CultivarName = result.cultivar_name;
pVPApplication.ExperimentalName = result.experimental_name;
pVPApplication.ScientificName = result.scientific_name;
pVPApplication.CommonName = result.common_name;
pVPApplication.ApplicantName = result.applicant_name;
pVPApplication.ApplicationDate = result.application_date.GetValueOrDefault();
pVPApplication.IsCertifiedSeed = result.is_certified_seed.GetValueOrDefault();
pVPApplication.ApplicationStatus = result.application_status;
pVPApplication.ApplicationStatusDate = result.status_date.GetValueOrDefault();
pVPApplication.CertificateIssuedDate = result.certificate_issued_date.GetValueOrDefault();
pVPApplication.YearsProtected = result.years_protected.GetValueOrDefault();
if (result.expiration_date != null)
{
pVPApplication.ExpirationDate = DateTime.Parse(result.expiration_date);
}
pVPApplication.AccessionID = result.accession_id.GetValueOrDefault();
pVPApplications.Add(pVPApplication);
}
break;
}
//switch (context)
//{
// case "scientific-name":
// var resultsByScientificName = _dataContext.usp_PVPApplicationsByCommonName_Select(referenceItemId).ToList();
// if (resultsByScientificName != null)
// {
// if (resultsByScientificName.Count() > 0)
// {
// foreach (var result in resultsByScientificName)
// {
// PVPApplication pVPApplication = new PVPApplication();
// pVPApplication.ApplicationNumber = result.pvp_application_number;
// pVPApplication.CultivarName = result.cultivar_name;
// pVPApplication.ExperimentalName = result.experimental_name;
// pVPApplication.ScientificName = result.scientific_name;
// pVPApplication.CommonName = result.common_name;
// pVPApplication.ApplicantName = result.applicant_name;
// pVPApplication.ApplicationDate = result.application_date.GetValueOrDefault();
// pVPApplication.IsCertifiedSeed = result.is_certified_seed.GetValueOrDefault();
// pVPApplication.ApplicationStatus = result.application_status;
// pVPApplication.CertificateIssuedDate = result.certificate_issued_date.GetValueOrDefault();
// pVPApplication.YearsProtected = result.years_protected.GetValueOrDefault();
// if (result.expiration_date != null)
// {
// pVPApplication.ExpirationDate = DateTime.Parse(result.expiration_date);
// }
// pVPApplication.AccessionID = result.accession_id.GetValueOrDefault();
// pVPApplications.Add(pVPApplication);
// }
// }
// }
// break;
// case "application-status":
// var resultsByApplicationStatus = _dataContext.usp_PVPApplicationsByApplicationStatus_Select(referenceItemId).ToList();
// if (resultsByApplicationStatus != null)
// {
// if (resultsByApplicationStatus.Count() > 0)
// {
// foreach (var result in resultsByApplicationStatus)
// {
// PVPApplication pVPApplication = new PVPApplication();
// pVPApplication.ApplicationNumber = result.pvp_application_number;
// pVPApplication.CultivarName = result.cultivar_name;
// pVPApplication.ExperimentalName = result.experimental_name;
// pVPApplication.ScientificName = result.scientific_name;
// pVPApplication.CommonName = result.common_name;
// pVPApplication.ApplicantName = result.applicant_name;
// pVPApplication.ApplicationDate = result.application_date.GetValueOrDefault();
// pVPApplication.IsCertifiedSeed = result.is_certified_seed.GetValueOrDefault();
// pVPApplication.ApplicationStatus = result.application_status;
// pVPApplication.CertificateIssuedDate = result.certificate_issued_date.GetValueOrDefault();
// pVPApplication.YearsProtected = result.years_protected.GetValueOrDefault();
// if (result.expiration_date != null)
// {
// pVPApplication.ExpirationDate = DateTime.Parse(result.expiration_date);
// }
// pVPApplication.AccessionID = result.accession_id.GetValueOrDefault();
// pVPApplications.Add(pVPApplication);
// }
// }
// }
// break;
// case "expiration-period":
// var resultsByExpiration = _dataContext.usp_PVPApplicationsByExpiration_Select(referenceItemId).ToList();
// if (resultsByExpiration != null)
// {
// if (resultsByExpiration.Count() > 0)
// {
// foreach (var result in resultsByExpiration)
// {
// PVPApplication pVPApplication = new PVPApplication();
// pVPApplication.ApplicationNumber = result.pvp_application_number;
// pVPApplication.CultivarName = result.cultivar_name;
// pVPApplication.ExperimentalName = result.experimental_name;
// pVPApplication.ScientificName = result.scientific_name;
// pVPApplication.CommonName = result.common_name;
// pVPApplication.ApplicantName = result.applicant_name;
// pVPApplication.ApplicationDate = result.application_date.GetValueOrDefault();
// pVPApplication.IsCertifiedSeed = result.is_certified_seed.GetValueOrDefault();
// pVPApplication.ApplicationStatus = result.application_status;
// pVPApplication.CertificateIssuedDate = result.certificate_issued_date.GetValueOrDefault();
// pVPApplication.YearsProtected = result.years_protected.GetValueOrDefault();
// if (result.expiration_date != null)
// {
// pVPApplication.ExpirationDate = DateTime.Parse(result.expiration_date);
// }
// pVPApplication.AccessionID = result.accession_id.GetValueOrDefault();
// pVPApplications.Add(pVPApplication);
// }
// }
// }
// break;
//}
}
catch (Exception e)
{
throw e;
}
return(pVPApplications);
}
public List <PVPApplication> Find(string context)
{
const string COMMAND_TEXT_EXPIRING = "usp_ARS_PVPExpiring6Months_Select";
const string COMMAND_TEXT_RECENT = "usp_ARS_PVPRecentApplications_Select";
const string COMMAND_TEXT_AVAILABLE = "usp_ARS_PVPRecentlyAvailable_Select";
List <PVPApplication> pVPApplications = new List <PVPApplication>();
try
{
using (SqlConnection conn = GetConnection("DataManager"))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
switch (context)
{
case "expired":
cmd.CommandText = COMMAND_TEXT_AVAILABLE;
break;
case "expiring":
cmd.CommandText = COMMAND_TEXT_EXPIRING;
break;
case "submitted":
cmd.CommandText = COMMAND_TEXT_RECENT;
break;
}
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
PVPApplication pVPApplication = new PVPApplication();
pVPApplication.ApplicationNumber = GetInt(reader["pvp_application_number"].ToString());
pVPApplication.CultivarName = reader["cultivar_name"].ToString();
pVPApplication.ExperimentalName = reader["experimental_name"].ToString();
pVPApplication.ScientificName = reader["scientific_name"].ToString();
pVPApplication.CommonName = reader["common_name"].ToString();
pVPApplication.ApplicantName = reader["applicant_name"].ToString();
pVPApplication.ApplicationDate = GetDate(reader["application_date"].ToString());
pVPApplication.IsCertifiedSeed = ParseBool(reader["is_certified_seed"].ToString());
pVPApplication.ApplicationStatus = reader["application_status"].ToString();
pVPApplication.CertificateIssuedDate = GetDate(reader["certificate_issued_date"].ToString());
pVPApplication.YearsProtected = GetInt(reader["years_protected"].ToString());
pVPApplication.ExpirationDate = GetDate(reader["expiration_date"].ToString());
pVPApplication.AccessionID = GetInt(reader["accession_id"].ToString());
pVPApplication.IsAvailableInGRIN = pVPApplication.AccessionID > 0 ? true : false;
pVPApplications.Add(pVPApplication);
}
}
}
}
}
catch (SqlException ex)
{
throw ex;
}
return(pVPApplications);
}
