public ActionResult Import() { var file = Request.Files["FileUpload"]; try { if (file == null || file.ContentLength == 0) { return(Json(new { success = false, message = "File rỗng." })); } var fileExtension = System.IO.Path.GetExtension(file.FileName); if (fileExtension != ".xlsx" && fileExtension != ".xls") { return(Json(new { success = false, message = "File không không đúng định dạng excel *.xlsx,*.xls" })); } string datetime = DateTime.Now.ToString("yyyyMMddHHmmss"); string fileLocation = string.Format("{0}/{1}", Server.MapPath("~/ExcelImport"), "[" + currentUser.ma_nguoi_dung + "-" + datetime + file.FileName); string errorFileLocation = string.Format("{0}/{1}", Server.MapPath("~/ExcelImport"), "[" + currentUser.ma_nguoi_dung + "-" + datetime + "-Error]" + file.FileName); string linkerror = "[" + currentUser.ma_nguoi_dung + "-" + datetime + "-Error]" + file.FileName; if (!System.IO.Directory.Exists(Server.MapPath("~/ExcelImport"))) { System.IO.Directory.CreateDirectory(fileLocation); } if (System.IO.File.Exists(fileLocation)) { System.IO.File.Delete(fileLocation); } file.SaveAs(fileLocation); var rownumber = 2; var total = 0; var error = 0; FileInfo fileInfo = new FileInfo(fileLocation); var excelPkg = new ExcelPackage(fileInfo); FileInfo template = new FileInfo(Server.MapPath(@"~\ExportExcelFile\User_Template.xlsx")); template.CopyTo(errorFileLocation); FileInfo _fileInfo = new FileInfo(errorFileLocation); var _excelPkg = new ExcelPackage(_fileInfo); ExcelWorksheet oSheet = excelPkg.Workbook.Worksheets["Data"]; ExcelWorksheet eSheet = _excelPkg.Workbook.Worksheets["Data"]; int totalRows = oSheet.Dimension.End.Row; using (var dbConn = OrmliteConnection.openConn()) { for (int i = 2; i <= totalRows; i++) { if (oSheet.Cells[i, 1].Value == null) { i = totalRows; } else { string ma_nguoi_dung = oSheet.Cells[i, 1].Value != null ? oSheet.Cells[i, 1].Value.ToString().Trim() : ""; string domain = oSheet.Cells[i, 2].Value != null ? oSheet.Cells[i, 2].Value.ToString().Trim() : ""; string ma_nguoi_dung_domain = oSheet.Cells[i, 3].Value != null ? oSheet.Cells[i, 3].Value.ToString().Trim() : ""; string ma_nhan_vien = oSheet.Cells[i, 4].Value != null ? oSheet.Cells[i, 4].Value.ToString().Trim() : ""; string ten_nguoi_dung = oSheet.Cells[i, 5].Value != null ? oSheet.Cells[i, 5].Value.ToString().Trim() : ""; string gioi_tinh = oSheet.Cells[i, 6].Value != null ? oSheet.Cells[i, 6].Value.ToString().Split('-')[0].ToString().Trim() : ""; string email = oSheet.Cells[i, 7].Value != null ? oSheet.Cells[i, 7].Value.ToString().Trim() : ""; string dien_thoai = oSheet.Cells[i, 8].Value != null ? oSheet.Cells[i, 8].Value.ToString().Trim() : ""; string trang_thai = "true"; if (oSheet.Cells[i, 9].Value.ToString() == "Hoạt động") { trang_thai = "true"; } else if (oSheet.Cells[i, 9].Value.ToString() == "Ngưng hoạt động") { trang_thai = "false"; } string don_vi = oSheet.Cells[i, 10].Value != null ? oSheet.Cells[i, 10].Value.ToString().Trim() : ""; string don_vi_quan_ly = oSheet.Cells[i, 11].Value != null ? oSheet.Cells[i, 11].Value.ToString().Split('-')[0].ToString().Trim() : ""; string profit = oSheet.Cells[i, 12].Value != null ? oSheet.Cells[i, 12].Value.ToString().Split('-')[0].ToString().Trim() : ""; string chuc_danh = oSheet.Cells[i, 13].Value != null ? oSheet.Cells[i, 13].Value.ToString().Split('-')[0].ToString().Trim() : ""; string cap_bac = oSheet.Cells[i, 14].Value != null ? oSheet.Cells[i, 14].Value.ToString().Split('-')[0].ToString().Trim() : ""; string bc_dinh_bien = oSheet.Cells[i, 15].Value != null ? oSheet.Cells[i, 15].Value.ToString().Split('-')[0].ToString().Trim() : ""; string dia_chi = oSheet.Cells[i, 16].Value != null ? oSheet.Cells[i, 16].Value.ToString().Trim() : ""; string ghi_chu = oSheet.Cells[i, 17].Value != null ? oSheet.Cells[i, 17].Value.ToString().Trim() : ""; string ngay_vao_lam = oSheet.Cells[i, 18].Value != null ? oSheet.Cells[i, 18].Value.ToString().Trim() : ""; don_vi_quan_ly = don_vi_quan_ly.Replace(" ", ""); try { var itemeexit = dbConn.FirstOrDefault <User>(s => s.ma_nguoi_dung == ma_nguoi_dung); var exituserBrand = dbConn.FirstOrDefault <UsersBranch>(s => s.ma_nguoi_dung == ma_nguoi_dung); var itemubr = new UsersBranch(); var listBranch = dbConn.Select <Branch>(); if (itemeexit != null) { itemeexit.domain = domain; itemeexit.ten_nguoi_dung = ten_nguoi_dung; itemeexit.gioi_tinh = gioi_tinh; itemeexit.email = email; itemeexit.dien_thoai = dien_thoai; itemeexit.trang_thai = trang_thai; itemeexit.dia_chi = dia_chi; itemeexit.ghi_chu = ghi_chu; itemeexit.ngay_cap_nhat = DateTime.Now; itemeexit.nguoi_cap_nhat = currentUser.ma_nguoi_dung; if (exituserBrand != null) { dbConn.Update <User>(itemeexit); dbConn.Update <UsersBranch>(exituserBrand); } else { itemubr.id_nguoi_dung = itemeexit.id; itemubr.ma_nguoi_dung = ma_nguoi_dung; itemubr.ma_chi_nhanh = don_vi_quan_ly; itemubr.ngay_tao = DateTime.Now; itemubr.nguoi_tao = currentUser.ma_nguoi_dung; itemubr.ngay_cap_nhat = DateTime.Parse("1900-01-01"); itemubr.nguoi_cap_nhat = ""; dbConn.Insert <UsersBranch>(itemubr); } } else { var item = new User(); var product_user = new UserProductCategory(); var userproductcate = dbConn.Select <ProductCategory>("Select id ,isnull(ma_phan_cap,'') as ma_phan_cap , isnull(ten_phan_cap,'') as ten_phan_cap From [ProductCategory]"); item.ma_nguoi_dung = ma_nguoi_dung; item.domain = domain; item.ten_nguoi_dung = ten_nguoi_dung; item.gioi_tinh = gioi_tinh; item.email = email; item.dien_thoai = dien_thoai; item.mat_khau = Helpers.GetMd5Hash.Generate(ma_nguoi_dung + "@123"); item.trang_thai = trang_thai; item.dia_chi = dia_chi; item.ghi_chu = ghi_chu; item.ngay_tao = DateTime.Now; item.nguoi_tao = currentUser.ma_nguoi_dung; item.ngay_cap_nhat = DateTime.Parse("1900-01-01"); item.nguoi_cap_nhat = ""; dbConn.Insert <User>(item); int id = (int)dbConn.GetLastInsertId(); itemubr.id_nguoi_dung = id; itemubr.ma_nguoi_dung = ma_nguoi_dung; itemubr.ma_chi_nhanh = don_vi_quan_ly; itemubr.ngay_tao = DateTime.Now; itemubr.nguoi_tao = currentUser.ma_nguoi_dung; itemubr.ngay_cap_nhat = DateTime.Parse("1900-01-01"); itemubr.nguoi_cap_nhat = ""; dbConn.Insert <UsersBranch>(itemubr); foreach (var itemuser in userproductcate) { product_user.id_nguoi_dung = id; product_user.ma_nguoi_dung = item.ma_nguoi_dung; product_user.ma_phan_cap = itemuser.ma_phan_cap; product_user.ngay_tao = DateTime.Now; product_user.nguoi_tao = currentUser.ma_nguoi_dung; product_user.ngay_cap_nhat = DateTime.Parse("1900-01-01"); product_user.nguoi_cap_nhat = ""; dbConn.Insert(product_user); } total++; rownumber++; } total++; rownumber++; } catch (Exception e) { eSheet.Cells[rownumber, 1].Value = ma_nguoi_dung; eSheet.Cells[rownumber, 2].Value = domain; eSheet.Cells[rownumber, 3].Value = ma_nguoi_dung_domain; eSheet.Cells[rownumber, 4].Value = ma_nhan_vien; eSheet.Cells[rownumber, 5].Value = ten_nguoi_dung; eSheet.Cells[rownumber, 6].Value = gioi_tinh; eSheet.Cells[rownumber, 7].Value = email; eSheet.Cells[rownumber, 8].Value = dien_thoai; eSheet.Cells[rownumber, 9].Value = trang_thai; eSheet.Cells[rownumber, 10].Value = don_vi; eSheet.Cells[rownumber, 11].Value = don_vi_quan_ly; eSheet.Cells[rownumber, 12].Value = profit; eSheet.Cells[rownumber, 13].Value = chuc_danh; eSheet.Cells[rownumber, 14].Value = cap_bac; eSheet.Cells[rownumber, 15].Value = bc_dinh_bien; eSheet.Cells[rownumber, 16].Value = dia_chi; eSheet.Cells[rownumber, 17].Value = ghi_chu; eSheet.Cells[rownumber, 18].Value = ngay_vao_lam; eSheet.Cells[rownumber, 19].Value = e.Message; rownumber++; error++; continue; } } } } _excelPkg.Save(); return(Json(new { success = true, total = total, totalError = error, link = linkerror })); } catch (Exception ex) { return(Json(new { success = false, message = ex.Message })); } }
public ActionResult CreateUpdate(User data) { using (var dbConn = Helpers.OrmliteConnection.openConn()) { try { if (data.id > 0) { if (accessDetail.sua) { var exist = dbConn.SingleOrDefault <User>("id ={0}", data.id); exist.dia_chi = !string.IsNullOrEmpty(data.dia_chi) ? data.dia_chi : ""; exist.dien_thoai = !string.IsNullOrEmpty(data.dien_thoai) ? data.dien_thoai : ""; exist.ghi_chu = !string.IsNullOrEmpty(data.ghi_chu) ? data.ghi_chu : ""; exist.ten_nguoi_dung = data.ten_nguoi_dung; exist.domain = data.domain; exist.trang_thai = data.trang_thai; exist.nguoi_cap_nhat = currentUser.ma_nguoi_dung; exist.ngay_cap_nhat = DateTime.Now; exist.gioi_tinh = !string.IsNullOrEmpty(data.gioi_tinh) ? data.gioi_tinh : ""; dbConn.Update(exist); } else { return(Json(new { success = false, error = "Bạn không có quyền cập nhập tài khoản" })); } } else { if (accessDetail.them) { var exist = dbConn.SingleOrDefault <User>("ma_nguoi_dung={0} or email={1}", data.ma_nguoi_dung, data.email); var userproductcate = dbConn.Select <ProductCategory>("Select id ,isnull(ma_phan_cap,'') as ma_phan_cap , isnull(ten_phan_cap,'') as ten_phan_cap From [ProductCategory]"); var product_user = new UserProductCategory(); if (exist != null) { return(Json(new { success = false, error = "Mã người dùng hoặc email đã tồn tại." })); } data.mat_khau = Helpers.GetMd5Hash.Generate(data.mat_khau); data.ngay_tao = DateTime.Now; data.nguoi_tao = currentUser.ma_nguoi_dung; data.ngay_cap_nhat = DateTime.Parse("1900-01-01"); data.dia_chi = !string.IsNullOrEmpty(data.dia_chi) ? data.dia_chi : ""; data.dien_thoai = !string.IsNullOrEmpty(data.dien_thoai) ? data.dien_thoai : ""; data.ghi_chu = !string.IsNullOrEmpty(data.ghi_chu) ? data.ghi_chu : ""; dbConn.Insert(data); data.id = (int)dbConn.GetLastInsertId(); int id = data.id; foreach (var itemuser in userproductcate) { product_user.id_nguoi_dung = id; product_user.ma_nguoi_dung = data.ma_nguoi_dung; product_user.ma_phan_cap = itemuser.ma_phan_cap; product_user.ngay_tao = DateTime.Now; product_user.nguoi_tao = currentUser.ma_nguoi_dung; product_user.ngay_cap_nhat = DateTime.Parse("1900-01-01"); product_user.nguoi_cap_nhat = ""; dbConn.Insert(product_user); } } else { return(Json(new { success = false, error = "Bạn không có quyền tạo tài khoản" })); } } dbConn.Delete <UserInGroup>("ma_nguoi_dung={0}", data.ma_nguoi_dung); if (data.groupId != null && data.groupId.Count() > 0) { foreach (var item in data.groupId) { var exist = dbConn.SingleOrDefault <UserInGroup>("ma_nguoi_dung={0} AND id_nhom_nguoi_dung={1}", data.ma_nguoi_dung, item); if (exist == null) { var userInGroup = new UserInGroup(); userInGroup.ma_nguoi_dung = data.ma_nguoi_dung; userInGroup.id_nhom_nguoi_dung = item; userInGroup.ngay_tao = DateTime.Now; userInGroup.nguoi_tao = currentUser.ma_nguoi_dung; userInGroup.ngay_cap_nhat = DateTime.Parse("1900-01-01"); dbConn.Insert(userInGroup); } } //dbConn.Delete<UserInGroup>("ma_nguoi_dung = {0} AND id_nhom_nguoi_dung NOT IN (" + String.Join(",", data.groupId.Select(s => s)) + ")", data.ma_nguoi_dung); } dbConn.Delete <UsersBranch>("id_nguoi_dung={0}", data.id); if (data.lstbranch != null) { foreach (string item in data.lstbranch) { var newdata = new UsersBranch(); newdata.id_nguoi_dung = data.id; newdata.ma_nguoi_dung = data.ma_nguoi_dung; newdata.ma_chi_nhanh = item; newdata.ngay_tao = DateTime.Now; newdata.nguoi_tao = currentUser.ma_nguoi_dung; newdata.ngay_cap_nhat = DateTime.Now; newdata.nguoi_cap_nhat = currentUser.ma_nguoi_dung; dbConn.Insert(newdata); } } } catch (Exception ex) { return(Json(new { success = false, error = ex.Message })); } return(Json(new { success = true })); } }