public PatientModelList QueryPatients(PatientModelRequest requestSerach) { var cs = "Server=localhost\\SQLEXPRESS;Database=HospitalDB;Trusted_Connection=True;"; using var con = new SqlConnection(cs); //Using Class SqlConnection for COnnent to database con.Open(); string sqlTypeId = ";"; if (requestSerach.TypeId != -1) { sqlTypeId = string.Format("AND a.[TypeId] = {0};", requestSerach.TypeId); } string sql = string.Format(@"SELECT a.Id ,a.Name ,a.Surname ,a.Age ,a.BirthDay ,a.TypeId ,a.NoOfVisit ,b.TypeName FROM PatientTbl a LEFT JOIN PatientType b ON a.TypeId = b.Id WHERE (a.[Name] LIKE '%{0}%' OR a.[Surname] LIKE '%{0}%' OR a.[Id] LIKE '%{0}%' OR a.[NoOfVisit] LIKE '%{0}%') ", requestSerach.SearchText) + sqlTypeId; using var cmd = new SqlCommand(sql, con); //Using Class SqlCommand for query data using SqlDataReader rdr = cmd.ExecuteReader(); // string output = ""; PatientModelList output = new PatientModelList(); output.Patienttable = new List <PatientModel>(); while (rdr.Read()) { output.Patienttable.Add( new PatientModel() { hn = rdr.GetInt32(0), name = rdr.GetString(1), surname = rdr.GetString(2), age = rdr.GetInt32(3), birthday = rdr.GetDateTime(4), typeId = rdr.GetInt32(5), visit = rdr.GetInt32(6), typeName = rdr.GetString(7) } ); } return(output); }
public async Task <byte[]> GetExport(PatientModelRequest requestSerach) { PatientModelList result = _patientRepository.QueryPatients(requestSerach); List <PatientModel> PatientList = result.Patienttable; byte[] content; MemoryStream stream = new MemoryStream(); using (ExcelPackage package = new ExcelPackage(stream)) { ExcelWorksheet ws = package.Workbook.Worksheets.Add("Patient List"); int totalRow = PatientList.Count + 1; ws.Cells[1, 1].Value = "No"; ws.Cells[1, 2].Value = "HN No"; ws.Cells[1, 3].Value = "Name"; ws.Cells[1, 4].Value = "Surname"; ws.Cells[1, 5].Value = "Age"; ws.Cells[1, 6].Value = "Birthday"; ws.Cells[1, 7].Value = "Type"; ws.Cells[1, 8].Value = "NO. of Visit"; int i = 0; for (int row = 2; row <= totalRow; row++) { ws.Cells[row, 1].Value = i + 1; ws.Cells[row, 2].Value = PatientList[i].hn; ws.Cells[row, 3].Value = PatientList[i].name; ws.Cells[row, 4].Value = PatientList[i].surname; ws.Cells[row, 5].Value = PatientList[i].age; ws.Cells[row, 6].Value = PatientList[i].birthday; ws.Cells[row, 6].Style.Numberformat.Format = "dd/mm/yyyy"; ws.Cells[row, 7].Value = PatientList[i].typeName; ws.Cells[row, 8].Value = PatientList[i].visit; i++; } content = await package.GetAsByteArrayAsync(); } return(content); }
public PatientModelList SelectPatients(PatientModelRequest requestSerach) { PatientModelList result = _patientRepository.QueryPatients(requestSerach); return(result); }
public PatientModelList GetPatientList([FromQuery] PatientModelRequest request) { PatientModelList result = _patientService.SelectPatients(request); return(result); }