//[Action(ToolTip = "Import Students TuitionFee Transaction to Database",TargetObjectsCriteria="IsImported = false")]
public void ImportStudentTuitionFee()
{
if (this.Note == "")
throw new UserFriendlyException("Vui lòng thêm thông tin Ghi chú trước khi import!!!");
Session session = this.Session;
string tempStudentFolderPath;
string tempStudentFile;
string tempStudentLogFile;
if (HttpContext.Current != null)
{
tempStudentFolderPath = HttpContext.Current.Request.MapPath("~/tempFolder");
tempStudentFile = HttpContext.Current.Request.MapPath("~/tempFolder/" + CsvFile.FileName);
tempStudentLogFile = HttpContext.Current.Request.MapPath("~/tempFolder/" + CsvFile.FileName+DateTime.Now.ToString("dd-MM-yyyy-HHmmss") + "-log.txt");
}
else
{
tempStudentFolderPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "tempFolder");
tempStudentFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "tempFolder/", CsvFile.FileName);
tempStudentLogFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "tempFolder/", CsvFile.FileName + "-log.txt");
}
if (!Directory.Exists(tempStudentFolderPath))
Directory.CreateDirectory(tempStudentFolderPath);
Dictionary<string, int> columnIndexs = new Dictionary<string, int>();
Dictionary<string, object> valueIndexs = new Dictionary<string, object>();
valueIndexs.Add("MSSV", "");
valueIndexs.Add("HOLOT", "");
valueIndexs.Add("TEN", "");
valueIndexs.Add("SOTIEN", "");
valueIndexs.Add("NGAYNOP", "");
valueIndexs.Add("DIENGIAI", "");
columnIndexs.Add("MSSV", -1);
columnIndexs.Add("HOLOT", -1);
columnIndexs.Add("TEN", -1);
columnIndexs.Add("SOTIEN", -1);
columnIndexs.Add("NGAYNOP", -1);
columnIndexs.Add("DIENGIAI", -1);
using (System.IO.FileStream fileStream = new FileStream(tempStudentFile, FileMode.OpenOrCreate))
{
using (System.IO.StreamWriter fileStreamlog = new StreamWriter(tempStudentLogFile, true))
{
// open xls file
CsvFile.SaveToStream(fileStream);
fileStream.Close();
Workbook book = Workbook.Open(tempStudentFile);
Worksheet sheet = book.Worksheets[0];
bool foundHeader = false;
int iLine;
//Tìm dòng chứa TEN cột
for (iLine = sheet.Cells.FirstRowIndex;
iLine <= sheet.Cells.LastRowIndex && !foundHeader; iLine++)
{
Row row = sheet.Cells.GetRow(iLine);
for (int colIndex = row.FirstColIndex;
colIndex <= row.LastColIndex; colIndex++)
{
Cell cell = row.GetCell(colIndex);
if (columnIndexs.ContainsKey(cell.Value.ToString().ToUpper().Trim()))
{
columnIndexs[cell.Value.ToString().ToUpper().Trim()] = colIndex; //Đã tìm thấy dòng chứa TEN cột. Xác định vị trí của cột
}
}
if (!columnIndexs.Values.Contains(-1))
{
foundHeader = true;
}
else
{
for (int colIndex = row.FirstColIndex; colIndex <= row.LastColIndex; colIndex++)
{
Cell cell = row.GetCell(colIndex);
if (columnIndexs.ContainsKey(cell.Value.ToString().ToUpper().Trim()))
{
columnIndexs[cell.Value.ToString().ToUpper().Trim()] = -1; //không tìm thấy dòng chứa TEN cột. Xác định vị trí của cột
}
}
}
}
if (!foundHeader)
throw new UserFriendlyException("Lỗi cấu trúc file");
//Các dòng sau đó đều là dòng dữ liệu
IsImported = true;
//Các dòng sau đó đều là dòng dữ liệu
for (; iLine <= sheet.Cells.LastRowIndex; iLine++)
{
using (NestedUnitOfWork uow = Session.BeginNestedUnitOfWork())
{
uow.BeginTransaction();
try
{
Row row = sheet.Cells.GetRow(iLine);
foreach (var column in columnIndexs)
{
Cell cell = row.GetCell(column.Value);
valueIndexs[column.Key] = cell.Value;
}
// tìm sinh viên
Student student = session.FindObject<Student>(new BinaryOperator("StudentCode", valueIndexs["MSSV"].ToString()));
if (student == null)
{
fileStreamlog.WriteLine(string.Format("Cannot find student: \"{0}\" on line \"{1}\" on {2:dd-mm-yy HH:MM:ss}", valueIndexs["MSSV"], iLine+1, DateTime.Now));
}
else
{
if (student.FirstName.Trim() != valueIndexs["HOLOT"].ToString().Trim() ||
student.LastName.Trim() != valueIndexs["TEN"].ToString().Trim())
{
fileStreamlog.WriteLine(string.Format("ERROR Found StudentCode: \"{0}\" but Full Name \"{1} \" is not like \"{2} {3}\" on {4:dd-mm-yy HH:MM:ss} - line {5} - DONOT IMPORT DATA",
student.StudentCode, student.FullName, valueIndexs["HOLOT"],
valueIndexs["TEN"], DateTime.Now, iLine+1));
}
else
{
try
{
double date = Double.Parse(valueIndexs["NGAYNOP"].ToString());
AccountTransaction acc = new AccountTransaction(session)
{
Student = student,
MoneyAmount = Convert.ToDecimal(valueIndexs["SOTIEN"]),
//TransactingDate = Convert.ToDateTime(valueIndexs["NGAYNOP"]),
TransactingDate = new DateTime(1900, 1, 1).AddDays(
date - 2),
Description = valueIndexs["DIENGIAI"].ToString(),
DateCreated = DateTime.Now,
DateModified = DateTime.Now,
ImportDescription = this.Note
};
acc.Save();
fileStreamlog.WriteLine(string.Format("Create transaction with money amount {0} for student: \"{1}\"-\"{2}\" on {3:dd-mm-yy HH:MM:ss} - line {4}",
valueIndexs["SOTIEN"], student.StudentCode, student.FullName,
DateTime.Now, iLine+1));
}
catch (Exception ex)
{
fileStreamlog.WriteLine(string.Format("ERROR: Cannot create Transaction for student \"{0}\"-{1} on {2:dd-mm-yy HH:MM:ss} - Line {3}",
student.StudentCode, student.FullName, DateTime.Now, iLine+1));
fileStreamlog.WriteLine(ex.Message + ex.StackTrace);
}
}
}
session.CommitTransaction();
}
catch (Exception ex)
{
fileStreamlog.WriteLine(string.Format("ERROR: Line \"{0}\": \r\n {1}\r\n {1} on {1:dd-mm-yy HH:MM:ss}", iLine+1, ex.Message, ex.StackTrace, DateTime.Now));
}
uow.CommitChanges();
}
}
}
}
#region Importdata
//using (System.IO.FileStream fileStream = new FileStream(tempStudentFile, FileMode.OpenOrCreate))
//{
// using (System.IO.StreamWriter fileStreamlog = new StreamWriter(tempStudentLogFile, true))
// {
// CsvFile.SaveToStream(fileStream);
// fileStream.Position = 0;
// StreamReader r = new StreamReader(fileStream);
// string newLine;
// bool foundHeader = false;
// int iLine = 1;
// try
// {
// //Tìm dòng chứa TEN cột
// while ((newLine = r.ReadLine()) != null)
// {
// string[] row = newLine.Split(new string[] { "~" }, StringSplitOptions.None);
// if (!foundHeader)
// {
// for (int i = 0; i < row.Length; i++)
// if (columnIndexs.ContainsKey(row[i].ToUpper().Trim()))
// {
// columnIndexs[row[i].ToUpper().Trim()] = i; //Đã tìm thấy dòng chứa TEN cột. Xác định vị trí của cột
// }
// if (!columnIndexs.Values.Contains(-1))
// {
// foundHeader = true;
// break;
// }
// else
// {
// for (int i = 0; i < row.Length; i++)
// if (columnIndexs.ContainsKey(row[i].ToUpper().Trim()))
// {
// columnIndexs[row[i].ToUpper().Trim()] = -1; //không tìm thấy dòng chứa TEN cột. Xác định vị trí của cột
// }
// }
// }
// }
// if (!foundHeader)
// throw new UserFriendlyException("Lỗi cấu trúc file");
// //Các dòng sau đó đều là dòng dữ liệu
// IsImported = true;
// //Các dòng sau đó đều là dòng dữ liệu
// while ((newLine = r.ReadLine()) != null)
// {
// iLine++;
// session.BeginTransaction();
// try
// {
// string[] row = newLine.Split(new string[] { "~" }, StringSplitOptions.None);
// foreach (var column in columnIndexs)
// {
// valueIndexs[column.Key] = row[column.Value];
// }
// //tìm sinh viên
// Student student = session.FindObject<Student>(new BinaryOperator("StudentCode", valueIndexs["MSSV"]));
// if (student == null)
// {
// fileStreamlog.WriteLine(string.Format("Cannot find student: \"{0}\" on line \"{1}\" on {2:dd-mm-yy HH:MM:ss}", valueIndexs["MSSV"], iLine, DateTime.Now));
// }
// else
// {
// try
// {
// AccountTransaction acc = new AccountTransaction(session)
// {
// Student = student,
// MoneyAmount = Convert.ToDecimal(valueIndexs["SOTIEN"]),
// TransactingDate = Convert.ToDateTime(valueIndexs["NGAYNOP"]),
// Description = valueIndexs["DIENGIAI"]
// };
// acc.Save();
// fileStreamlog.WriteLine(string.Format("Create transaction with money amount {0} for student: \"{1}\"-\"{2}\" on {3:dd-mm-yy HH:MM:ss} - line {4}",
// valueIndexs["SOTIEN"], student.StudentCode, student.FullName, DateTime.Now, iLine
// ));
// }
// catch (Exception ex)
// {
// fileStreamlog.WriteLine(string.Format("Cannot create Transaction for student \"{0}\"-{1} on {2:dd-mm-yy HH:MM:ss} - Line {3}",
// student.StudentCode, student.FullName, DateTime.Now, iLine));
// fileStreamlog.WriteLine(ex.Message + ex.StackTrace);
// }
// }
// session.CommitTransaction();
// }
// catch (Exception ex)
// {
// fileStreamlog.WriteLine(string.Format("Error Line \"{0}\": \r\n {1}\r\n {1} on {1:dd-mm-yy HH:MM:ss}", iLine, ex.Message, ex.StackTrace, DateTime.Now));
// }
// }
// fileStreamlog.WriteLine(string.Format("Create \"{0}\" all transaction on {1:dd-mm-yy HH:MM:ss}", iLine, DateTime.Now));
// }
// catch (UserFriendlyException ex)
// {
// fileStreamlog.WriteLine(string.Format("Error Line \"{0}\": \"{1}\" on {1:dd-mm-yy HH:MM:ss}", iLine, ex.StackTrace, DateTime.Now));
// fileStream.Close();
// fileStreamlog.Close();
// throw ex;
// }
// finally
// {
// fileStream.Close();
// fileStreamlog.Close();
// }
// }
//}
#endregion
}
//[Action(ToolTip = "Import Students TuitionFee Transaction to Database",TargetObjectsCriteria="IsImported = false")]
public void ImportStudentTuitionFee()
{
if (this.Note == "")
{
throw new UserFriendlyException("Vui lòng thêm thông tin Ghi chú trước khi import!!!");
}
Session session = this.Session;
string tempStudentFolderPath;
string tempStudentFile;
string tempStudentLogFile;
if (HttpContext.Current != null)
{
tempStudentFolderPath = HttpContext.Current.Request.MapPath("~/tempFolder");
tempStudentFile = HttpContext.Current.Request.MapPath("~/tempFolder/" + CsvFile.FileName);
tempStudentLogFile = HttpContext.Current.Request.MapPath("~/tempFolder/" + CsvFile.FileName + DateTime.Now.ToString("dd-MM-yyyy-HHmmss") + "-log.txt");
}
else
{
tempStudentFolderPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "tempFolder");
tempStudentFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "tempFolder/", CsvFile.FileName);
tempStudentLogFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "tempFolder/", CsvFile.FileName + "-log.txt");
}
if (!Directory.Exists(tempStudentFolderPath))
{
Directory.CreateDirectory(tempStudentFolderPath);
}
Dictionary <string, int> columnIndexs = new Dictionary <string, int>();
Dictionary <string, object> valueIndexs = new Dictionary <string, object>();
valueIndexs.Add("MSSV", "");
valueIndexs.Add("HOLOT", "");
valueIndexs.Add("TEN", "");
valueIndexs.Add("SOTIEN", "");
valueIndexs.Add("NGAYNOP", "");
valueIndexs.Add("DIENGIAI", "");
columnIndexs.Add("MSSV", -1);
columnIndexs.Add("HOLOT", -1);
columnIndexs.Add("TEN", -1);
columnIndexs.Add("SOTIEN", -1);
columnIndexs.Add("NGAYNOP", -1);
columnIndexs.Add("DIENGIAI", -1);
using (System.IO.FileStream fileStream = new FileStream(tempStudentFile, FileMode.OpenOrCreate))
{
using (System.IO.StreamWriter fileStreamlog = new StreamWriter(tempStudentLogFile, true))
{
// open xls file
CsvFile.SaveToStream(fileStream);
fileStream.Close();
Workbook book = Workbook.Open(tempStudentFile);
Worksheet sheet = book.Worksheets[0];
bool foundHeader = false;
int iLine;
//Tìm dòng chứa TEN cột
for (iLine = sheet.Cells.FirstRowIndex;
iLine <= sheet.Cells.LastRowIndex && !foundHeader; iLine++)
{
Row row = sheet.Cells.GetRow(iLine);
for (int colIndex = row.FirstColIndex;
colIndex <= row.LastColIndex; colIndex++)
{
Cell cell = row.GetCell(colIndex);
if (columnIndexs.ContainsKey(cell.Value.ToString().ToUpper().Trim()))
{
columnIndexs[cell.Value.ToString().ToUpper().Trim()] = colIndex; //Đã tìm thấy dòng chứa TEN cột. Xác định vị trí của cột
}
}
if (!columnIndexs.Values.Contains(-1))
{
foundHeader = true;
}
else
{
for (int colIndex = row.FirstColIndex; colIndex <= row.LastColIndex; colIndex++)
{
Cell cell = row.GetCell(colIndex);
if (columnIndexs.ContainsKey(cell.Value.ToString().ToUpper().Trim()))
{
columnIndexs[cell.Value.ToString().ToUpper().Trim()] = -1; //không tìm thấy dòng chứa TEN cột. Xác định vị trí của cột
}
}
}
}
if (!foundHeader)
{
throw new UserFriendlyException("Lỗi cấu trúc file");
}
//Các dòng sau đó đều là dòng dữ liệu
IsImported = true;
//Các dòng sau đó đều là dòng dữ liệu
for (; iLine <= sheet.Cells.LastRowIndex; iLine++)
{
using (NestedUnitOfWork uow = Session.BeginNestedUnitOfWork())
{
uow.BeginTransaction();
try
{
Row row = sheet.Cells.GetRow(iLine);
foreach (var column in columnIndexs)
{
Cell cell = row.GetCell(column.Value);
valueIndexs[column.Key] = cell.Value;
}
// tìm sinh viên
Student student = session.FindObject <Student>(new BinaryOperator("StudentCode", valueIndexs["MSSV"].ToString()));
if (student == null)
{
fileStreamlog.WriteLine(string.Format("Cannot find student: \"{0}\" on line \"{1}\" on {2:dd-mm-yy HH:MM:ss}", valueIndexs["MSSV"], iLine + 1, DateTime.Now));
}
else
{
if (student.FirstName.Trim() != valueIndexs["HOLOT"].ToString().Trim() ||
student.LastName.Trim() != valueIndexs["TEN"].ToString().Trim())
{
fileStreamlog.WriteLine(string.Format("ERROR Found StudentCode: \"{0}\" but Full Name \"{1} \" is not like \"{2} {3}\" on {4:dd-mm-yy HH:MM:ss} - line {5} - DONOT IMPORT DATA",
student.StudentCode, student.FullName, valueIndexs["HOLOT"],
valueIndexs["TEN"], DateTime.Now, iLine + 1));
}
else
{
try
{
double date = Double.Parse(valueIndexs["NGAYNOP"].ToString());
AccountTransaction acc = new AccountTransaction(session)
{
Student = student,
MoneyAmount = Convert.ToDecimal(valueIndexs["SOTIEN"]),
//TransactingDate = Convert.ToDateTime(valueIndexs["NGAYNOP"]),
TransactingDate = new DateTime(1900, 1, 1).AddDays(
date - 2),
Description = valueIndexs["DIENGIAI"].ToString(),
DateCreated = DateTime.Now,
DateModified = DateTime.Now,
ImportDescription = this.Note
};
acc.Save();
fileStreamlog.WriteLine(string.Format("Create transaction with money amount {0} for student: \"{1}\"-\"{2}\" on {3:dd-mm-yy HH:MM:ss} - line {4}",
valueIndexs["SOTIEN"], student.StudentCode, student.FullName,
DateTime.Now, iLine + 1));
}
catch (Exception ex)
{
fileStreamlog.WriteLine(string.Format("ERROR: Cannot create Transaction for student \"{0}\"-{1} on {2:dd-mm-yy HH:MM:ss} - Line {3}",
student.StudentCode, student.FullName, DateTime.Now, iLine + 1));
fileStreamlog.WriteLine(ex.Message + ex.StackTrace);
}
}
}
session.CommitTransaction();
}
catch (Exception ex)
{
fileStreamlog.WriteLine(string.Format("ERROR: Line \"{0}\": \r\n {1}\r\n {1} on {1:dd-mm-yy HH:MM:ss}", iLine + 1, ex.Message, ex.StackTrace, DateTime.Now));
}
uow.CommitChanges();
}
}
}
}
#region Importdata
//using (System.IO.FileStream fileStream = new FileStream(tempStudentFile, FileMode.OpenOrCreate))
//{
// using (System.IO.StreamWriter fileStreamlog = new StreamWriter(tempStudentLogFile, true))
// {
// CsvFile.SaveToStream(fileStream);
// fileStream.Position = 0;
// StreamReader r = new StreamReader(fileStream);
// string newLine;
// bool foundHeader = false;
// int iLine = 1;
// try
// {
// //Tìm dòng chứa TEN cột
// while ((newLine = r.ReadLine()) != null)
// {
// string[] row = newLine.Split(new string[] { "~" }, StringSplitOptions.None);
// if (!foundHeader)
// {
// for (int i = 0; i < row.Length; i++)
// if (columnIndexs.ContainsKey(row[i].ToUpper().Trim()))
// {
// columnIndexs[row[i].ToUpper().Trim()] = i; //Đã tìm thấy dòng chứa TEN cột. Xác định vị trí của cột
// }
// if (!columnIndexs.Values.Contains(-1))
// {
// foundHeader = true;
// break;
// }
// else
// {
// for (int i = 0; i < row.Length; i++)
// if (columnIndexs.ContainsKey(row[i].ToUpper().Trim()))
// {
// columnIndexs[row[i].ToUpper().Trim()] = -1; //không tìm thấy dòng chứa TEN cột. Xác định vị trí của cột
// }
// }
// }
// }
// if (!foundHeader)
// throw new UserFriendlyException("Lỗi cấu trúc file");
// //Các dòng sau đó đều là dòng dữ liệu
// IsImported = true;
// //Các dòng sau đó đều là dòng dữ liệu
// while ((newLine = r.ReadLine()) != null)
// {
// iLine++;
// session.BeginTransaction();
// try
// {
// string[] row = newLine.Split(new string[] { "~" }, StringSplitOptions.None);
// foreach (var column in columnIndexs)
// {
// valueIndexs[column.Key] = row[column.Value];
// }
// //tìm sinh viên
// Student student = session.FindObject<Student>(new BinaryOperator("StudentCode", valueIndexs["MSSV"]));
// if (student == null)
// {
// fileStreamlog.WriteLine(string.Format("Cannot find student: \"{0}\" on line \"{1}\" on {2:dd-mm-yy HH:MM:ss}", valueIndexs["MSSV"], iLine, DateTime.Now));
// }
// else
// {
// try
// {
// AccountTransaction acc = new AccountTransaction(session)
// {
// Student = student,
// MoneyAmount = Convert.ToDecimal(valueIndexs["SOTIEN"]),
// TransactingDate = Convert.ToDateTime(valueIndexs["NGAYNOP"]),
// Description = valueIndexs["DIENGIAI"]
// };
// acc.Save();
// fileStreamlog.WriteLine(string.Format("Create transaction with money amount {0} for student: \"{1}\"-\"{2}\" on {3:dd-mm-yy HH:MM:ss} - line {4}",
// valueIndexs["SOTIEN"], student.StudentCode, student.FullName, DateTime.Now, iLine
// ));
// }
// catch (Exception ex)
// {
// fileStreamlog.WriteLine(string.Format("Cannot create Transaction for student \"{0}\"-{1} on {2:dd-mm-yy HH:MM:ss} - Line {3}",
// student.StudentCode, student.FullName, DateTime.Now, iLine));
// fileStreamlog.WriteLine(ex.Message + ex.StackTrace);
// }
// }
// session.CommitTransaction();
// }
// catch (Exception ex)
// {
// fileStreamlog.WriteLine(string.Format("Error Line \"{0}\": \r\n {1}\r\n {1} on {1:dd-mm-yy HH:MM:ss}", iLine, ex.Message, ex.StackTrace, DateTime.Now));
// }
// }
// fileStreamlog.WriteLine(string.Format("Create \"{0}\" all transaction on {1:dd-mm-yy HH:MM:ss}", iLine, DateTime.Now));
// }
// catch (UserFriendlyException ex)
// {
// fileStreamlog.WriteLine(string.Format("Error Line \"{0}\": \"{1}\" on {1:dd-mm-yy HH:MM:ss}", iLine, ex.StackTrace, DateTime.Now));
// fileStream.Close();
// fileStreamlog.Close();
// throw ex;
// }
// finally
// {
// fileStream.Close();
// fileStreamlog.Close();
// }
// }
//}
#endregion
}
