public void Export_Student_CSV()
{
CSVConvert csv = new CSVConvert();
var sb = new StringBuilder();
IEnumerable <Student> query = unitOfWork.Students.GetAll();
var list = query.ToList();
Type type = typeof(Student);
var props = type.GetProperties();
sb.Append(string.Join(",", "ROLL", "Email"));
sb.Append(Environment.NewLine);
foreach (var item in list)
{
sb.Append(string.Join(",", csv.AddCSVQuotes(item.Roll), csv.AddCSVQuotes(item.Email)));
sb.Append(Environment.NewLine);
}
var response = System.Web.HttpContext.Current.Response;
response.BufferOutput = true;
response.Clear();
response.ClearHeaders();
response.ContentEncoding = Encoding.Unicode;
response.AddHeader("content-disposition", "attachment;filename=Student.CSV ");
response.ContentType = "text/plain";
response.Write(sb.ToString());
response.End();
}
public void UCStateCodeDataLandArea_WhenLoaded_ThenShouldReturnSortedResultByLandArea()
{
CSVConvert jsonState = new CSVConvert(US_SENSUS_FILE_PATH);
string jsonData = jsonState.SortUSCensusDataByWaterArea();
JArray jArray = JArray.Parse(jsonData);
string firstValueFromCsv = jArray[0]["LandArea"].ToString();
Assert.AreEqual("294207.53", firstValueFromCsv);
}
public void UCStateCodeDataPopulationDensity_WhenLoaded_ThenShouldReturnSortedResultByPopulationDensity()
{
CSVConvert jsonState = new CSVConvert(US_SENSUS_FILE_PATH);
string jsonData = jsonState.SortUSCensusDataByPopulousDensity();
JArray jArray = JArray.Parse(jsonData);
string firstValueFromCsv = jArray[0]["PopulationDensity"].ToString();
Assert.AreEqual("0.46", firstValueFromCsv);
}
public void UCStateCodeDataTotalArea_WhenLoaded_ThenldReturnSortedResultByTotalArea()
{
CSVConvert jsonState = new CSVConvert(US_SENSUS_FILE_PATH);
string jsonData = jsonState.SortUSCensusDataByTotalArea();
JArray jArray = JArray.Parse(jsonData);
string firstValueFromCsv = jArray[0]["TotalArea"].ToString();
Assert.AreEqual("104655.80", firstValueFromCsv);
}
public void GivenIndianStateCodeData_WhenLoaded_ThenShouldReturnSortedResultByArea()
{
CSVConvert jsonState = new CSVConvert(FILE_PATH);
string jsonData = jsonState.SortByStatePopullationDensity();
JArray jArray = JArray.Parse(jsonData);
string firstValueFromCsv = jArray[0]["AreaInSqKm"].ToString();
Assert.AreEqual("3702", firstValueFromCsv);
}
public void GivenIndianStateCodeData_WhenLoaded_ThenShouldReturnStateSortedResult()
{
CSVConvert jsonState = new CSVConvert(STATE_CODE_FILE_PATH);
string jsonData = jsonState.SortByState();
JArray jArray = JArray.Parse(jsonData);
string firstValueFromCsv = jArray[0]["StateCode"].ToString();
Assert.AreEqual("AD", firstValueFromCsv);
}
public void Export(string check)
{
string searchCheck = check.Split('^')[0];
string Campus = check.Split('^')[1];
string Semester_ID = check.Split('^')[2];
string Subject_ID = check.Split('^')[3];
string completedSpec = check.Split('^')[4];
string compulsorySpec = check.Split('^')[5];
string ImportedDate = check.Split('^')[6];
string Email = check.Split('^')[7];
var LogList = unitOfWork.CoursesLog.GetPageList();
if (searchCheck != "1")
{
if (Email != "8")
{
LogList = LogList.Where(s => s.Email.Trim().ToUpper().Contains(Email.Trim().ToUpper())).ToList();
}
if (ImportedDate != "7")
{
DateTime dt = DateTime.ParseExact(ImportedDate, "dd/MM/yyyy", CultureInfo.InvariantCulture);
LogList = LogList.Where(s => s.Date_Import == dt).ToList();
}
if (completedSpec != "5")
{
LogList = completedSpec == "Yes" ? LogList.Where(s => s.Completed == true).ToList() : LogList.Where(s => s.Completed == false).ToList();
}
if (compulsorySpec != "6")
{
LogList = compulsorySpec == "Yes" ? LogList = LogList.Where(s => s.Subject_ID != "").ToList() : LogList = LogList.Where(s => s.Subject_ID == "").ToList();
}
if (Subject_ID != "4")
{
LogList = LogList.Where(s => s.Subject_ID == Subject_ID.Trim()).ToList();
}
if (Campus != "2")
{
LogList = LogList.Where(s => s.Campus == Campus.Trim()).ToList();
}
if (Semester_ID != "3")
{
LogList = LogList.Where(s => s.Semester_ID == Semester_ID.Trim()).ToList();
}
}
CSVConvert csv = new CSVConvert();
var sb = new StringBuilder();
var list = LogList.ToList();
sb.Append(string.Join(",", "Name", "Email", "Subject ID", "Campus", "Course Name", "Course Slug", "University", "Enrollment Time", "Start Time", "Last ActivityTime", "Overall Progress", "Estimated",
"Completed", "Status", "Program Slug", "Program Name", "Enrollment Source", "Completion Time", "Course Grade", "Date Import"));
sb.Append(Environment.NewLine);
foreach (var item in list)
{
sb.Append(string.Join(",",
csv.AddCSVQuotes(item.Name),
csv.AddCSVQuotes(item.Email),
csv.AddCSVQuotes(item.Subject_ID == null ? "" : item.Subject_ID),
csv.AddCSVQuotes(item.Campus),
csv.AddCSVQuotes(item.Course_Name),
csv.AddCSVQuotes(item.Course_Slug),
csv.AddCSVQuotes(item.University),
csv.AddCSVQuotes(item.Course_Enrollment_Time.ToString().Contains("1/1/1970") ? "" : item.Course_Enrollment_Time.ToString()),
csv.AddCSVQuotes(item.Course_Start_Time.ToString().Contains("1/1/1970") ? "" : item.Course_Start_Time.ToString()),
csv.AddCSVQuotes(item.Last_Course_Activity_Time.ToString().Contains("1/1/1970") ? "" : item.Last_Course_Activity_Time.ToString()),
csv.AddCSVQuotes(item.Overall_Progress.ToString()),
csv.AddCSVQuotes(item.Estimated.ToString()),
csv.AddCSVQuotes(item.Completed.ToString()),
csv.AddCSVQuotes(item.Status.ToString()),
csv.AddCSVQuotes(item.Program_Slug),
csv.AddCSVQuotes(item.Program_Name),
csv.AddCSVQuotes(item.Enrollment_Source),
csv.AddCSVQuotes(item.Completion_Time.ToString().Contains("1/1/1970") ? "" : item.Completion_Time.ToString()),
csv.AddCSVQuotes(item.Course_Grade.ToString()),
csv.AddCSVQuotes(item.Date_Import.ToString())
));
sb.Append(Environment.NewLine);
}
var response = System.Web.HttpContext.Current.Response;
response.BufferOutput = true;
response.Clear();
response.ClearHeaders();
response.ContentEncoding = Encoding.Unicode;
response.AddHeader("content-disposition", "attachment;filename=Usage-Report.CSV ");
response.ContentType = "text/plain";
response.Write(sb.ToString());
response.End();
}
public ActionResult UploadFilesUsage()
{
int countSusscess = 0;
int countFail = 0;
int userID = int.Parse(Request["UserID"]);
string _dateImport = Request["dateImport"];
CSVConvert csv = new CSVConvert();
string messageImport = "";
HttpFileCollectionBase files = Request.Files;
StringBuilder sb = new StringBuilder();
if (files.Count == 0)
{
messageImport = "Please select the file first to upload!";
return(Json(new { message = messageImport }, JsonRequestBehavior.AllowGet));
}
for (int i = 0; i < files.Count; i++)
{
HttpPostedFileBase postedFile = files[i];
if (postedFile != null)
{
try
{
string fileExtension = Path.GetExtension(postedFile.FileName);
if (fileExtension.ToLower() != ".csv")
{
messageImport = "Please select the excel file with .csv extension";
return(Json(new { message = messageImport }, JsonRequestBehavior.AllowGet));
}
else
if (fileExtension == ".csv")
{
using (var sreader = new StreamReader(postedFile.InputStream))
{
int startRow = 1;
string[] headers = sreader.ReadLine().Split(',');
if (headers.Length == 19)
{
if (!headers[0].Contains("Name") || !headers[1].Contains("Email") || !headers[2].Contains("External Id") || !headers[3].Contains("Course") || !headers[4].Contains("Course ID") ||
!headers[5].Contains("Course Slug") || !headers[6].Contains("University") || !headers[7].Contains("Enrollment Time") || !headers[8].Contains("Class Start Time") ||
!headers[9].Contains("Last Course Activity Time") || !headers[10].Contains("Overall Progress") || !headers[11].Contains("Estimated Learning Hours") ||
!headers[12].Contains("Completed") || !headers[13].Contains("Removed From Program") || !headers[14].Contains("Program Slug") ||
!headers[15].Contains("Program Name") || !headers[16].Contains("Enrollment Source") || !headers[17].Contains("Completion Time") || !headers[18].Contains("Course Grade"))
{
messageImport = "Invalid file structure";
return(Json(new { message = messageImport }));
}
}
else
{
messageImport = "The number of columns is invalid";
return(Json(new { message = messageImport }));
}
if (unitOfWork.CoursesLog.IsExitsDateImport(_dateImport))
{
messageImport = "Reported date existed, if you continue please delete records this date!";
return(Json(new { message = messageImport }));
}
var listCoureseName = unitOfWork.Courses.GetList();
string semesterID = unitOfWork.Semesters.checkDateOfSemester(_dateImport);
while (!sreader.EndOfStream)
{
startRow++;
try
{
List <string> rows = csv.RegexRow(sreader);
if (!unitOfWork.Subject.IsExitsSubject(rows[2].ToString().Split('-')[0]))
{
countFail++;
continue;
}
if (!unitOfWork.Campus.IsExitsCampusID(rows[2].ToString().Split('-')[1]))
{
countFail++;
continue;
}
if (!unitOfWork.Students.IsExtisStudent(rows[2].ToString().Split('-')[2], semesterID))
{
countFail++;
continue;
}
for (int j = 0; j < rows.Count; j++)
{
rows[j] = rows[j].Replace("'", "''");
}
sb.Append(unitOfWork.CoursesLog.getSqlQuery(rows, userID, _dateImport, listCoureseName, semesterID, unitOfWork.CoursesLog.getSubjectID(rows[3].ToString(), rows[2].ToString().Split('-')[2], semesterID)));
}
catch
{
messageImport = "Row " + startRow + " invaild!";
return(Json(new { message = messageImport }, JsonRequestBehavior.AllowGet));
}
}
}
try
{
countSusscess = unitOfWork.CoursesLog.countInsert(sb);
}
catch (Exception ex)
{
messageImport = ex.Message.Split('/')[0];
return(Json(new { message = messageImport }, JsonRequestBehavior.AllowGet));
}
if (countFail == 0 && countSusscess != 0)
{
messageImport = "Import success " + countSusscess + " records";
}
else
{
messageImport = countSusscess == 0 ? "Import Fail" : "Import success " + countSusscess + " and fail " + countFail + " records!";
}
}
}
catch (Exception ex)
{
messageImport = ex.Message;
}
}
else
{
messageImport = "Please select the file first to upload!";
}
}
return(Json(new { message = messageImport }, JsonRequestBehavior.AllowGet));
}
public ActionResult Import()
{
string messageImport = "";
CSVConvert csv = new CSVConvert();
try
{
HttpPostedFileBase postedFile = Request.Files[0];
string semester = Request["Semester"];
if (postedFile != null)
{
try
{
string fileExtension = Path.GetExtension(postedFile.FileName);
if (fileExtension != ".csv")
{
messageImport = "Please select the csv file with .csv extension";
return Json(new { message = messageImport });
}
try
{
using (var sreader = new StreamReader(postedFile.InputStream))
{
string[] headers = sreader.ReadLine().Split(',');
while (!sreader.EndOfStream)
{
List<string> rows = csv.RegexRow(sreader);
//if (!unitOfWork.Classes.CheckExitsClass(getRow.GetClassStudent(rows).Class_ID)
// ||
if (unitOfWork.Subject.CheckExitsSubject(getRow.GetSubjectStudent(rows).Subject_ID))
{
if (unitOfWork.Students.CheckExitsStudent(getRow.GetStudent(rows, semester).Roll)
&& !unitOfWork.Subject.IsExitsSubject(getRow.GetSubjectStudent(rows).Subject_ID))
{
unitOfWork.SubjectStudent.Insert(getRow.GetSubjectStudent(rows));
// unitOfWork.ClassStudent.Insert(getRow.GetClassStudent(rows));
}
else
if (!unitOfWork.Students.CheckExitsStudent(getRow.GetStudent(rows, semester).Roll))
{
unitOfWork.Students.Insert(getRow.GetStudent(rows, semester));
unitOfWork.SubjectStudent.Insert(getRow.GetSubjectStudent(rows));
//unitOfWork.ClassStudent.Insert(getRow.GetClassStudent(rows));
}
}
}
}
if (unitOfWork.Save())
{
messageImport = "Import successfull!";
}
else
{
messageImport = "Import Fail!";
}
}
catch (Exception ex)
{
messageImport = ex.Message;
}
return Json(new { message = messageImport });
}
catch (Exception ex)
{
messageImport = ex.Message;
}
}
else
{
messageImport = "Please select the file first to upload.";
}
}
catch
{
messageImport = "Please select the file first to upload.";
}
return Json(new { message = messageImport });
}
public ActionResult Import()
{
int countFail = 0;
int countSuccess = 0;
string messageImport = "";
CSVConvert csv = new CSVConvert();
try
{
HttpPostedFileBase postedFile = Request.Files[0];
string semester = Request["Semester"];
string campus = Request["Campus"];
if (postedFile != null)
{
try
{
string fileExtension = Path.GetExtension(postedFile.FileName);
if (fileExtension.ToLower() != ".csv")
{
messageImport = "Please select the csv file with .csv extension";
return(Json(new { message = messageImport }));
}
try
{
using (var sreader = new StreamReader(postedFile.InputStream))
{
string[] headers = sreader.ReadLine().Split(',');
if (headers.Length == 4)
{
if (!headers[0].Contains("Roll Number") || !headers[1].Contains("Full Name") || !headers[2].Contains("Email") || !headers[3].Contains("Subject"))
{
messageImport = "Invalid file structure";
return(Json(new { message = messageImport }));
}
}
else
{
messageImport = "The number of columns is invalid";
return(Json(new { message = messageImport }));
}
int startRow = 1;
List <string> listAddRoll = new List <string>();
while (!sreader.EndOfStream)
{
startRow++;
List <string> rows = csv.RegexRow(sreader);
if (!rows[2].Contains("@fpt.edu.vn"))
{
messageImport = "Row " + startRow + ": Email invalid";
return(Json(new { message = messageImport }));
}
if (unitOfWork.Subject.IsExitsSubject(getRow.GetSubjectStudent(rows, semester).Subject_ID))
{
var countAdd = listAddRoll.Where(o => o.Contains(getRow.GetStudent(rows, semester, campus).Roll)).ToList().Count();
if ((unitOfWork.Students.IsExtisStudent(getRow.GetStudent(rows, semester, campus).Roll, semester) &&
!unitOfWork.Subject.IsExitsSubject(getRow.GetSubjectStudent(rows, semester).Subject_ID)) ||
(countAdd > 0))
{
unitOfWork.SubjectStudent.Insert(getRow.GetSubjectStudent(rows, semester));
countSuccess++;
}
else
if (!unitOfWork.Students.IsExtisStudent(getRow.GetStudent(rows, semester, campus).Roll, semester) && countAdd == 0)
{
unitOfWork.Students.Insert(getRow.GetStudent(rows, semester, campus));
unitOfWork.SubjectStudent.Insert(getRow.GetSubjectStudent(rows, semester));
listAddRoll.Add(getRow.GetStudent(rows, semester, campus).Roll);
countSuccess++;
}
else
{
countFail++;
}
}
else
{
messageImport = "Row " + startRow + ": Subject not exist";
return(Json(new { message = messageImport }));
}
}
}
if (unitOfWork.Save())
{
if (countSuccess != 0)
{
messageImport = countFail != 0 ? "Import success " + countSuccess + " students and fail " + countFail + " students!" : "Import success " + countSuccess + " students!";
}
else
{
messageImport = "Import fail " + countFail + " students";
}
}
else
{
messageImport = "Import Fail!";
}
}
catch (Exception ex)
{
messageImport = ex.Message;
}
return(Json(new { message = messageImport }));
}
catch (Exception ex)
{
messageImport = ex.Message;
}
}
else
{
messageImport = "Please select the file first to upload.";
}
}
catch
{
messageImport = "Please select the file first to upload.";
}
return(Json(new { message = messageImport }));
}
public ActionResult UploadFiles()
{
int userID = int.Parse(Request["UserID"]);
string _dateImport = Request["dateImport"];
CSVConvert csv = new CSVConvert();
string messageImport = "";
HttpFileCollectionBase files = Request.Files;
if (files.Count == 0)
{
messageImport = "Please select the file first to upload!";
return(Json(new { message = messageImport }, JsonRequestBehavior.AllowGet));
}
for (int i = 0; i < files.Count; i++)
{
HttpPostedFileBase postedFile = files[i];
if (postedFile != null)
{
try
{
string fileExtension = Path.GetExtension(postedFile.FileName);
if (fileExtension != ".csv")
{
messageImport = "Please select the excel file with .csv extension";
return(Json(new { message = messageImport }, JsonRequestBehavior.AllowGet));
}
else
if (fileExtension == ".csv")
{
using (var sreader = new StreamReader(postedFile.InputStream))
{
if (postedFile.FileName.Contains("specialization-report"))
{
string[] headers = sreader.ReadLine().Split(',');
var listIdSubject = unitOfWork.Specifications.GetAll();
while (!sreader.EndOfStream)
{
List <string> rows = csv.RegexRow(sreader);
if (!unitOfWork.Students.IsExtisStudent(rows[1].ToString().Split('@')[0]))
{
continue;
}
unitOfWork.SpecificationsLog.Insert(getRow.GetStudentSpec(rows, userID, _dateImport, listIdSubject));
}
}
else
if (postedFile.FileName.Contains("usage-report"))
{
string[] headers = sreader.ReadLine().Split(',');
var listIdCourses = unitOfWork.Courses.GetListID();
while (!sreader.EndOfStream)
{
List <string> rows = csv.RegexRow(sreader);
if (!unitOfWork.Students.IsExtisStudent(rows[1].ToString().Split('@')[0]))
{
continue;
}
unitOfWork.CoursesLog.Insert(getRow.GetStudentCourse(rows, userID, _dateImport, listIdCourses));
}
}
else
{
messageImport = "Please choose Specialization-report file or Usage-report file!";
return(Json(new { message = messageImport }, JsonRequestBehavior.AllowGet));
}
}
if (unitOfWork.Save())
{
messageImport = "Import successfull!";
}
else
{
messageImport = "Exception!";
}
}
}
catch (Exception ex)
{
messageImport = ex.Message;
}
}
else
{
messageImport = "Please select the file first to upload!";
}
}
return(Json(new { message = messageImport }, JsonRequestBehavior.AllowGet));
}
