public ActionResult uploadexcel(HttpPostedFileBase excelfile, enquiry data)
{
try
{
if (excelfile == null || excelfile.ContentLength == 0)
{
return(View("uploadexcel"));
}
else
{
if (excelfile.FileName.EndsWith("xls") || excelfile.FileName.EndsWith("xlsx"))
{
string path = Server.MapPath("~/ExcelFiles/" + excelfile.FileName);
if (System.IO.File.Exists(path))
{
System.IO.File.Delete(path);
}
excelfile.SaveAs(path);
Enterdata(path);
return(RedirectToAction("enquiry"));
}
else
{
return(RedirectToAction("enquiry"));
}
}
}
catch (Exception e)
{
throw e;
}
}
public void Enterdata(string path)
{
try
{
// string con = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=Yes;'";
string con = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
enquiry stud = new enquiry();
using (OleDbConnection connection = new OleDbConnection(con))
{
connection.Open();
OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
List <dynamic> duplicate = new List <dynamic>();
using (OleDbDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
// stud.NAME = dr[1].ToString();
stud.EMAIL = dr[2].ToString();
if (db.enquiries.Any(k => k.EMAIL == stud.EMAIL))
{
var data = db.enquiries.Where(i => i.EMAIL == stud.EMAIL).FirstOrDefault();
duplicate.Add(data);
}
else
{
stud.END_DATE = Convert.ToDateTime(dr[0]);
stud.NAME = dr[1].ToString();
stud.EMAIL = dr[2].ToString();
if (dr[3].ToString() != null && dr[3].ToString() != "")
{
stud.NUMBER = dr[3].ToString();
}
if (dr[4].ToString() != null && dr[4].ToString() != "")
{
stud.GENDER = dr[4].ToString();
}
else
{
stud.GENDER = "Male";
}
if (dr[5].ToString() != null && dr[5].ToString() != "")
{
stud.DOB = Convert.ToDateTime(dr[5]);
}
stud.REFERENCE = dr[6].ToString();
if (dr[7].ToString() != null && dr[7].ToString() != "")
{
stud.COURSE_ENQUIRY = 5;
}
else
{
stud.COURSE_ENQUIRY = 5;
}
if (dr[8].ToString() != null && dr[8].ToString() != "")
{
stud.JOINING_DATE = Convert.ToDateTime(dr[8]);
}
string stringTime = dr[9].ToString();
TimeSpan time = TimeSpan.Parse(stringTime);
//stud.IN_TIME = time;
//string Time = dr[9].ToString();
//string time2 = Time.ToString('hh:mm:ss tt');
//stud.IN_TIME = time2;
//string stringTime2 = dr[10].ToString();
//stud.OUT_TIME = stringTime2;
stud.TESTIMONIAL = dr[11].ToString();
stud.FEES = dr[12].ToString();
stud.REMAINING_FEES = dr[13].ToString();
stud.IS_DELETED = false;
db.enquiries.Add(stud);
db.SaveChanges();
}
}
ViewBag.dup = duplicate;
}
}
}
catch (Exception e)
{
throw e;
}
}
public ActionResult enquiry(enquiry data)
{
try
{
if (data.ID > 0)
{
var enquiry = db.enquiries.Where(i => i.ID == data.ID).SingleOrDefault();
enquiry.NAME = data.NAME;
enquiry.EMAIL = data.EMAIL;
enquiry.NUMBER = data.NUMBER;
enquiry.REFERENCE = data.REFERENCE;
enquiry.GENDER = data.GENDER;
enquiry.DOB = data.DOB;
if (data.COURSE_ENQUIRY != null)
{
enquiry.COURSE_ENQUIRY = data.COURSE_ENQUIRY;
}
if (data.IN_TIME != null)
{
enquiry.IN_TIME = data.IN_TIME;
}
if (data.OUT_TIME != null)
{
enquiry.OUT_TIME = data.OUT_TIME;
}
if (data.JOINING_DATE != null)
{
enquiry.JOINING_DATE = data.JOINING_DATE;
}
if (data.END_DATE != null)
{
enquiry.END_DATE = data.END_DATE;
}
enquiry.TESTIMONIAL = data.TESTIMONIAL;
if (data.FEES != null)
{
enquiry.FEES = data.FEES;
var course = db.courses.Where(i => i.ID == data.COURSE_ENQUIRY).FirstOrDefault();
var coursefee = Convert.ToInt16(course.FEES);
var datafee = Convert.ToInt16(data.FEES);
var remainingfee = coursefee - datafee;
enquiry.REMAINING_FEES = remainingfee.ToString();
}
db.SaveChanges();
}
else
{
data.IS_DELETED = false;
var course = db.courses.Where(i => i.ID == data.COURSE_ENQUIRY && i.ID != 5).FirstOrDefault();
if (course != null)
{
var coursefee = Convert.ToInt32(course.FEES);
var datafee = Convert.ToInt32(data.FEES);
var remainingfee = coursefee - datafee;
data.REMAINING_FEES = remainingfee.ToString();
}
var enquiry = db.enquiries.Add(data);
db.SaveChanges();
}
}
catch (Exception e)
{
throw e;
}
return(RedirectToAction("enquiry"));
}
