public ActionResult getListDepartment()
{
using (var dbConn = Helpers.OrmliteConnection.openConn())
{
var data = CRM_Department.GetAllCRM_Departments();
return(Json(data, JsonRequestBehavior.AllowGet));
}
}
public ActionResult ImportFromExcel_Department()
{
if (asset.Export)
{
try
{
if (ModelState.IsValid)
{
List <CRM_Department> listData = new List <CRM_Department>();
int total = 0;
if (Request.Files["FileUpload"] != null && Request.Files["FileUpload"].ContentLength > 0)
{
string fileExtension =
System.IO.Path.GetExtension(Request.Files["FileUpload"].FileName);
if (fileExtension == ".xls" || fileExtension == ".xlsx")
{
// Create a folder in App_Data named ExcelFiles because you need to save the file temporarily location and getting data from there.
string fileLocation = string.Format("{0}/{1}", Server.MapPath("~/Excel"), "[" + currentUser.UserName + "-" + DateTime.Now.ToString("yyyyMMddHHmmss") + "]" + Request.Files["FileUpload"].FileName);
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
Request.Files["FileUpload"].SaveAs(fileLocation);
string excelConnectionString = string.Empty;
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
excelConnection.Open();
DataTable dt = new DataTable();
dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
return(null);
}
String[] excelSheets = new String[dt.Rows.Count];
int t = 0;
//excel data saves in temp file here.
foreach (DataRow row in dt.Rows)
{
excelSheets[t] = row["TABLE_NAME"].ToString();
t++;
}
OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);
DataSet ds = new DataSet();
string query = string.Format("Select * from [{0}]", excelSheets[0]);
using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
{
dataAdapter.Fill(ds);
}
List <string> err = new List <string>();
//Chạy vòng vòng để check dữ liệu
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string departmentID = ds.Tables[0].Rows[i]["DepartmentID"].ToString();
if (departmentID.ToString() == "")
{
departmentID = "0";
}
string department = ds.Tables[0].Rows[i]["Department"].ToString();
if (department.ToString() == "")
{
department = "";
}
string active = ds.Tables[0].Rows[i]["Active"].ToString();
if (active.ToString() == "")
{
active = false.ToString();
}
try
{
var listDepartmentID = CRM_Department.GetCRM_Department(int.Parse(departmentID)); // kiểm tra id đã có trong db chưa
if (listDepartmentID == null) // Chưa có thì insert
{
try
{
CRM_Department meta = new CRM_Department();
meta.Department = department;
meta.Active = bool.Parse(active);
meta.CreatedDatetime = DateTime.Now;
meta.CreatedUser = currentUser.UserName;
meta.LastUpdatedDateTime = DateTime.Now;
meta.LastUpdatedUser = currentUser.UserName;
meta.Save();
}
catch (Exception e)
{
}
}
else // Có rồi thì update
{
try
{
listDepartmentID.DepartmentID = int.Parse(departmentID);
listDepartmentID.Department = department;
listDepartmentID.Active = bool.Parse(active);
listDepartmentID.LastUpdatedUser = currentUser.UserName;
listDepartmentID.LastUpdatedDateTime = DateTime.Now;
listDepartmentID.Update();
}
catch (Exception e)
{ }
}
total++;
}
catch (Exception e)
{
continue;
}
}
}
else
{
ModelState.AddModelError("", "Please select Excel File.");
}
}
return(Json(new { success = true, data = listData.Select(a => a.Department).ToList(), total = total }));
}
else
{
return(Json(new { success = false }));
}
}
catch (Exception ex)
{
return(Json(new { success = false }));
}
}
else
{
return(RedirectToAction("NoAccessRights", "Error"));
}
}
