public InfantDetailsResponse GetInfantDetails(string birthId) { InfantDetailsResponse infantDetails = null; try { var query = string.Format(@"select BirthId, FullName, CONVERT(varchar, DOB, 103) AS DOB, POB, MothersName, a.InfantID, b.NextVaccinationDate from InfantDetails a left join vaccinationDetails b on a.infantId = b.InfantId where BirthId ='{0}' AND b.VaccinationId = (SELECT MAX(VaccinationId) FROM vaccinationDetails where InfantID = a.InfantID)", birthId); var infantData = dbService.ExecuteReader(query); if (infantData.Rows.Count > 0) { infantDetails = new InfantDetailsResponse(); infantDetails.BirthId = Convert.ToString(infantData.Rows[0]["BirthId"]); infantDetails.FullName = Convert.ToString(infantData.Rows[0]["FullName"]); infantDetails.DOB = Convert.ToString(infantData.Rows[0]["DOB"]); infantDetails.POB = Convert.ToString(infantData.Rows[0]["POB"]); infantDetails.MothersName = Convert.ToString(infantData.Rows[0]["MothersName"]); infantDetails.InfantId = Convert.ToInt32(infantData.Rows[0]["InfantID"]); infantDetails.NextVaccinationDate = Convert.ToString(infantData.Rows[0]["NextVaccinationDate"]); } } catch { } return(infantDetails); }
public IEnumerable <InfantDetailsResponse> SearchInfant(InfantSearchRequest searchRequest) { List <InfantDetailsResponse> infantList = new List <InfantDetailsResponse>(); var query = @"SELECT BirthId, FullName, DOB, POB, MothersName, a.InfantID, b.NextVaccinationDate FROM InfantDetails a LEFT JOIN vaccinationDetails b ON a.infantId = b.InfantId WHERE "; Dictionary <string, string> paramlist = new Dictionary <string, string>(); if (searchRequest.DOB != default(DateTime)) { paramlist.Add("DOB", searchRequest.DOB.ToString()); } if (searchRequest.MothersName != "") { paramlist.Add("MothersName", searchRequest.MothersName); } if (searchRequest.PlaceofBirth != "") { paramlist.Add("POB", searchRequest.PlaceofBirth); } if (searchRequest.BirthId != "") { paramlist.Add("BirthId", searchRequest.BirthId); } int i = 0; foreach (var item in paramlist) { query += item.Key + "='" + item.Value + "'"; i++; if (i != paramlist.Count) { query += " AND "; } } query += " AND b.VaccinationId = (SELECT MAX(VaccinationId) FROM vaccinationDetails where InfantID = a.InfantID)"; var infantDataList = dbService.ExecuteReader(query); foreach (DataRow row in infantDataList.Rows) { var infantData = new InfantDetailsResponse(); infantData.BirthId = Convert.ToString(row["BirthId"]); infantData.FullName = Convert.ToString(row["FullName"]); infantData.DOB = Convert.ToString(row["DOB"]); infantData.POB = Convert.ToString(row["POB"]); infantData.MothersName = Convert.ToString(row["MothersName"]); infantData.InfantId = Convert.ToInt32(row["InfantID"]); infantData.NextVaccinationDate = Convert.ToString(row["NextVaccinationDate"]); infantList.Add(infantData); } return(infantList); }
public IEnumerable <InfantDetailsResponse> GetInfantsByGuardian(int guardianId) { List <InfantDetailsResponse> infantList = new List <InfantDetailsResponse>(); var query = string.Format(@"select BirthId, FullName, DOB, POB, MothersName, a.InfantID, b.NextVaccinationDate from InfantDetails a left join vaccinationDetails b on a.infantId = b.InfantId where GuardianId ='{0}' AND b.VaccinationId = (SELECT MAX(VaccinationId) FROM vaccinationDetails where InfantID = a.InfantID)", guardianId); var infantDataList = dbService.ExecuteReader(query); foreach (DataRow row in infantDataList.Rows) { var infantData = new InfantDetailsResponse(); infantData.BirthId = Convert.ToString(row["BirthId"]); infantData.FullName = Convert.ToString(row["FullName"]); infantData.DOB = Convert.ToString(row["DOB"]); infantData.POB = Convert.ToString(row["POB"]); infantData.MothersName = Convert.ToString(row["MothersName"]); infantData.InfantId = Convert.ToInt32(row["InfantID"]); infantData.NextVaccinationDate = Convert.ToString(row["NextVaccinationDate"]); infantList.Add(infantData); } return(infantList); }