//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); }