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 <IActionResult> GetExport([FromQuery] PatientModelRequest requestSerach)
{
var content = await _patientService.GetExport(requestSerach);
return(File(content,
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
"Company.xlsx"));
}
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);
}