public GraduatedViewModel(Graduated graduated)
{
GraduatedId = graduated.GraduatedId;
Serial1 = graduated.Serial1 ?? "000";
Serial2 = graduated.Serial2 ?? "000";
SerialType = graduated.SerialType;
FirstName = graduated.FirstName;
LastName = graduated.LastName;
FacultyId = graduated.FacultyId;
CourseId = graduated.CourseId;
LocalityId = graduated.LocalityId;
ProvinceId = graduated.ProvinceId;
CountryId = graduated.CountryId;
CurrentCountry = graduated.Country.CountryName;
CurrentLocality = graduated.Locality != null ? graduated.Locality.LocalityName : "";
CurrentProvince = graduated.Province != null ? graduated.Province.ProvinceName : "";
CurrentFaculty = graduated.Faculty.FacultyName;
CurrentCourse = graduated.Course.CourseName;
FacultyFormatter = $"Tomo: {graduated.FacultyTome} Folio: {graduated.FacultyFolio} Número: {graduated.FacultyNumber}";
TomeUH = graduated.TomeUH;
FolioUH = graduated.FolioUH;
NumberUH = graduated.NumberUH;
FinishTime = graduated.FinishTime;
ExpeditionTime = graduated.ExpeditionTime;
Observations = graduated.Observations;
ScientistCredit = graduated.ScientistCredit;
ExpeditionCauses = graduated.ExpeditionCauses;
GoldTitle = graduated.GoldTitle;
IndexTable = new TableViewModel();
}
public ActionResult Create(GraduatedViewModel model)
{
if (!ModelState.IsValid)
{
ViewBag.ShowValidation = true;
ViewBag.Types = new SelectList(Enum.GetValues(typeof(SerialType)).OfType <SerialType>().Where(x => x != SerialType.ADM));
ViewBag.Faculties = new SelectList(ctx.Faculties, "FacultyId", "FacultyName", model.FacultyId);
ViewBag.Courses = new SelectList(ctx.Courses, "CourseId", "CourseName", model.CourseId);
var cs = ctx.Countries.ToList();
var provs = (cs.Count != 0 ? ctx.Provinces.ToList().Where(x => x.CountryId == model.CountryId) : Enumerable.Empty <Province>()).ToList();
var locals = (provs.Count != 0 ? ctx.Localities.ToList().Where(x => x.ProvinceId == (model.ProvinceId ?? provs[0].ProvinceId)) : Enumerable.Empty <Locality>()).ToList();
ViewBag.Countries = new SelectList(cs, "CountryId", "CountryName", model.CountryId);
ViewBag.Provinces = new SelectList(provs, "ProvinceId", "ProvinceName", model.ProvinceId ?? provs[0].ProvinceId);
ViewBag.Localities = new SelectList(locals, "LocalityId", "LocalityName", model.LocalityId ?? locals[0].LocalityId);
return(View(model));
}
var graduated = new Graduated()
{
Serial1 = model.Serial1 ?? "000",
Serial2 = model.Serial2 ?? "000",
SerialType = model.SerialType,
FirstName = model.FirstName,
LastName = model.LastName,
CourseId = model.CourseId,
FacultyId = model.FacultyId,
FolioUH = model.FolioUH,
TomeUH = model.TomeUH,
NumberUH = model.NumberUH,
FacultyTome = model.FacultyTome,
FacultyFolio = model.FacultyFolio,
FacultyNumber = model.FacultyNumber,
ProvinceId = model.ProvinceId,
LocalityId = model.LocalityId,
CountryId = model.CountryId.Value,
Observations = model.Observations,
GoldTitle = model.GoldTitle,
ScientistCredit = model.ScientistCredit,
ExpeditionCauses = model.ExpeditionCauses,
ExpeditionTime = model.ExpeditionTime,
FinishTime = model.FinishTime
};
ctx.Graduates.Add(graduated);
ctx.SaveChanges();
return(RedirectToAction("Index"));
}
public ActionResult UploadExcel(Graduated users, HttpPostedFileBase FileUpload)
{
List <string> data = new List <string>();
if (FileUpload != null)
{
// tdata.ExecuteCommand("truncate table OtherCompanyAssets");
if (FileUpload.ContentType == "application/vnd.ms-excel" || FileUpload.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
string filename = FileUpload.FileName;
string targetpath = Server.MapPath("~/Doc/");
FileUpload.SaveAs(targetpath + filename);
string pathToExcelFile = targetpath + filename;
var connectionString = "";
if (filename.EndsWith(".xls"))
{
connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", pathToExcelFile);
}
else if (filename.EndsWith(".xlsx"))
{
connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", pathToExcelFile);
}
var adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString);
var ds = new DataSet();
adapter.Fill(ds, "ExcelTable");
DataTable dtable = ds.Tables["ExcelTable"];
string sheetName = "Sheet1";
var excelFile = new ExcelQueryFactory(pathToExcelFile);
var graduatedrecords = from a in excelFile.Worksheet <ImportTemplate>(sheetName) select a;
foreach (var a in graduatedrecords)
{
try
{
Graduated TU = new Graduated();
TU.ExpeditionTime = a.FechaExpedicion;
TU.FirstName = a.Nombre;
TU.LastName = a.Apellidos;
TU.CountryId = ctx.Countries.Where(x => x.CountryName.Equals(a.Pais)).Select(x => x.CountryId).ToList()[0];
TU.FinishTime = a.FechaTerminacion;
TU.FacultyId = ctx.Faculties.Where(x => x.FacultyName.Equals(a.Facultad)).Select(x => x.FacultyId).ToList()[0];
TU.CourseId = ctx.Courses.Where(x => x.CourseName.Equals(a.Carrera)).Select(x => x.CourseId).ToList()[0];
TU.TomeUH = a.TomoUH;
TU.FolioUH = a.FolioUH;
TU.NumberUH = a.NumeroUH;
TU.FacultyTome = a.TomoFac;
TU.FacultyFolio = a.FolioFac;
TU.FacultyNumber = a.NumeroFac;
ctx.Graduates.Add(TU);
ctx.SaveChanges();
}
catch (DbEntityValidationException ex)
{
foreach (var entityValidationErrors in ex.EntityValidationErrors)
{
foreach (var validationError in entityValidationErrors.ValidationErrors)
{
Response.Write("Property: " + validationError.PropertyName + " Error: " + validationError.ErrorMessage);
}
}
}
}
//deleting excel file from folder
if ((System.IO.File.Exists(pathToExcelFile)))
{
System.IO.File.Delete(pathToExcelFile);
}
return(RedirectToAction("Index"));
}
else
{
//alert message for invalid file format
//data.Add("<ul>");
//data.Add("<li>Only Excel file format is allowed</li>");
//data.Add("</ul>");
//data.ToArray();
//return Json(data, JsonRequestBehavior.AllowGet);
return(RedirectToAction("Index"));
}
}
else
{
// data.Add("<ul>");
// if (FileUpload == null) data.Add("<li>Please choose Excel file</li>");
// data.Add("</ul>");
// data.ToArray();
// return Json(data, JsonRequestBehavior.AllowGet);
return(RedirectToAction("Index"));
}
}
partial void UpdateGraduated(Graduated instance);
partial void InsertGraduated(Graduated instance);
partial void DeleteGraduated(Graduated instance);
