public async Task <IEnumerable <object> > PostSP([FromBody] ClassInput sData)
{
var returnObject = new List <dynamic>();
using (var cmd = _context.Database.GetDbConnection().CreateCommand())
{
cmd.CommandText = "dbo.SP_Test1";
cmd.CommandType = CommandType.StoredProcedure;
//加入參數
cmd.Parameters.Add(new SqlParameter("item", sData.bottom));
cmd.Parameters.Add(new SqlParameter("field", sData.high));
if (cmd.Connection.State != ConnectionState.Open)
{
cmd.Connection.Open();
}
var retObject = new List <dynamic>();
using (var dataReader = await cmd.ExecuteReaderAsync())
{
while (await dataReader.ReadAsync())
{
//定義ExpandoObject,代表無成員物件,意指可以再執行階段動態新增及刪除成員
var dataRow = new ExpandoObject() as IDictionary <string, object>;
for (var iFiled = 0; iFiled < dataReader.FieldCount; iFiled++)
{
dataRow.Add(
//取得欄位名稱
dataReader.GetName(iFiled),
//如果欄位值為null,則回傳null
dataReader.IsDBNull(iFiled) ? null : dataReader[iFiled]
);
}
//將取得的dataRow加入動態List中
retObject.Add((ExpandoObject)dataRow);
}
}
//返回sp results
return(retObject);
}
}
public IActionResult GenerateExcel([FromBody] ClassInput input)
{
string path = "";
List <string> totalStudent = new List <string>();
List <string> studentsPresent = new List <string>();
List <string> absentNames = new List <string>();
string csvAbsentee = "";
string[] excelRows = input.AttendenceData.Split('\n', StringSplitOptions.RemoveEmptyEntries);
foreach (string row in excelRows)
{
string[] rowArray = row.Split('\t', StringSplitOptions.RemoveEmptyEntries);
if (rowArray.Length >= 2)
{
if (DateTime.TryParse(rowArray[0], CultureInfo.InvariantCulture, DateTimeStyles.None, out DateTime dateTime))
{
if (dateTime.Date == DateTime.Now.Date)
{
string[] studentDetail = rowArray[1].Split(' ', StringSplitOptions.RemoveEmptyEntries);
studentsPresent.Add(studentDetail[0]);
}
}
}
}
switch (input.ClassName)
{
//case "6":
// path = AppDomain.CurrentDomain.BaseDirectory + "//Excel//VI A.xlsx";
// break;
case "7":
path = AppDomain.CurrentDomain.BaseDirectory + "//Excel//VII C.xlsx";
break;
case "8":
path = AppDomain.CurrentDomain.BaseDirectory + "//Excel//8c.xlsx";
break;
case "9":
path = AppDomain.CurrentDomain.BaseDirectory + "//Excel//9A.xlsx";
break;
case "10":
path = AppDomain.CurrentDomain.BaseDirectory + "//Excel//10C.xlsx";
break;
}
if (!string.IsNullOrEmpty(path))
{
FileInfo fileInfo = new FileInfo(path);
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
ExcelPackage package = new ExcelPackage(fileInfo);
ExcelWorksheet worksheet = package.Workbook.Worksheets.FirstOrDefault();
// get number of rows and columns in the sheet
int rows = worksheet.Dimension.Rows; // 20
//int columns = worksheet.Dimension.Columns; // 7
//loop through the worksheet rows and columns
for (int i = 1; i <= rows; i++)
{
totalStudent.Add(
worksheet.Cells[i, 1].Value == null ? "" : worksheet.Cells[i, 1].Value.ToString());
}
List <string> absentee = totalStudent.Except(studentsPresent).ToList();
foreach (string num in absentee)
{
if (int.TryParse(num, out int rollNo))
{
absentNames.Add(worksheet.Cells[rollNo, 2].Value == null ? "" : worksheet.Cells[rollNo, 2].Value.ToString());
}
}
csvAbsentee = string.Join(',', absentNames);
}
//var smtpClient1 = new SmtpClient("smtp.gmail.com")
//{
// Port = 587,
// Credentials = new NetworkCredential("*****@*****.**", "Scorpion@#$123"),
// EnableSsl = true,
//};
//var mailMessage = new MailMessage
//{
// From = new MailAddress("*****@*****.**"),
// Subject = input.ClassName + " absentee list | " + DateTime.Now.ToString("dd/MM/yyyy"),
// Body = "<h1>Hello, below rollNo were absent today</h1> <p>" + csvAbsentee + "</p>",
// IsBodyHtml = true,
//};
//mailMessage.To.Add("*****@*****.**");
//try
//{
// SmtpClient smtpClient = new SmtpClient("smtp.mail.yahoo.com", 465);
// smtpClient.UseDefaultCredentials = false;
// smtpClient.Credentials = new NetworkCredential()
// {
// UserName = "******",
// Password = "******"
// };
// smtpClient.DeliveryMethod = SmtpDeliveryMethod.Network;
// smtpClient.EnableSsl = true;
// smtpClient.Send("*****@*****.**", "*****@*****.**", "Account verification", "hhhhhh");
//}
//catch (Exception ex)
//{
// csvAbsentee = ex.Message;
//}
if (csvAbsentee.EndsWith(","))
{
csvAbsentee = csvAbsentee.TrimEnd(',');
}
return(Ok(csvAbsentee));
}
