public ActionResult ImportUnit() { IDbConnection dbConn = new OrmliteConnection().openConn(); try { if (Request.Files["FileUpload"] != null && Request.Files["FileUpload"].ContentLength > 0) { string fileExtension = System.IO.Path.GetExtension(Request.Files["FileUpload"].FileName); if (fileExtension == ".xlsx" || fileExtension == ".xls") { string datetime = DateTime.Now.ToString("yyyyMMddHHmmss"); string fileLocation = string.Format("{0}/{1}", Server.MapPath("~/ExcelImport"), "[" + currentUser.UserID + "-" + datetime + Request.Files["FileUpload"].FileName); string errorFileLocation = string.Format("{0}/{1}", Server.MapPath("~/ExcelImport"), "[" + currentUser.UserID + "-" + datetime + "-Error]" + Request.Files["FileUpload"].FileName); string linkerror = "[" + currentUser.UserID + "-" + datetime + "-Error]" + Request.Files["FileUpload"].FileName; if (System.IO.File.Exists(fileLocation)) System.IO.File.Delete(fileLocation); Request.Files["FileUpload"].SaveAs(fileLocation); var rownumber = 2; var total = 0; FileInfo fileInfo = new FileInfo(fileLocation); var excelPkg = new ExcelPackage(fileInfo); //FileInfo template = new FileInfo(Server.MapPath(errorFileLocation)); //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"]; ExcelPackage pck = new ExcelPackage(new FileInfo(errorFileLocation)); ExcelWorksheet ws = pck.Workbook.Worksheets["Data"]; int totalRows = oSheet.Dimension.End.Row; for (int i = 2; i <= totalRows; i++) { string ID = oSheet.Cells[i, 1].Value != null ? oSheet.Cells[i, 1].Value.ToString() : ""; string Name = oSheet.Cells[i, 2].Value != null ? oSheet.Cells[i, 2].Value.ToString() : ""; string Note = oSheet.Cells[i, 3].Value != null ? oSheet.Cells[i, 3].Value.ToString() : ""; //string Status = oSheet.Cells[i, 4].Value == "Đang hoạt động" ? "true" : "false"; string Status = "false"; if (oSheet.Cells[i, 6].Value != null) { if (oSheet.Cells[i, 4].Value.ToString() == "Đang hoạt động") { Status = "true"; } } try { if (string.IsNullOrEmpty(Name)) { ws.Cells["A" + 2].Value = Name; ws.Cells[rownumber, 14].Value = "Vui lòng nhập (*)."; rownumber++; } else { var checkexists = dbConn.SingleOrDefault<DC_AD_Unit>("SELECT * FROM DC_AD_Unit WHERE UnitID = '" + ID + "'"); if (checkexists != null) { checkexists.UnitID = ID; checkexists.UnitName = Name; checkexists.Status = Boolean.Parse(Status); checkexists.UpdatedAt = DateTime.Now; checkexists.UpdatedBy = currentUser.UserID; dbConn.Update<DC_AD_Unit>(checkexists); } else { string id = ""; var checkID = dbConn.SingleOrDefault<DC_AD_Unit>("SELECT UnitID, Id FROM dbo.DC_AD_Unit ORDER BY Id DESC"); if (checkID != null) { var nextNo = int.Parse(checkID.UnitID.Substring(3, checkID.UnitID.Length - 3)) + 1; id = "UIT" + String.Format("{0:00000000}", nextNo); } else { id = "UIT00000001"; } var item = new DC_AD_Unit(); item.UnitID = id; item.UnitName = !string.IsNullOrEmpty(Name) ? Name.Trim() : ""; item.Note = !string.IsNullOrEmpty(Note) ? Note.Trim() : ""; item.CreatedAt = DateTime.Now; item.CreatedBy = currentUser.UserID; item.UpdatedAt = DateTime.Parse("1900-01-01"); item.UpdatedBy = ""; item.Status = Boolean.Parse(Status); dbConn.Insert<DC_AD_Unit>(item); } total++; } } catch (Exception e) { return Json(new { success = false, message = e.Message }); } } return Json(new { success = true, total = total, totalError = rownumber - 2, link = linkerror }); } else { return Json(new { success = false, message = "Không phải là file Excel. *.xlsx" }); } } else { return Json(new { success = false, message = "Không có file hoặc file không phải là Excel" }); } } catch (Exception ex) { return Json(new { success = false, message = ex.Message }); } }
public ActionResult CreateUnit(DC_AD_Unit item) { IDbConnection db = new OrmliteConnection().openConn(); try { var isExist = db.SingleOrDefault<DC_AD_Unit>("SELECT UnitID, Id FROM dbo.DC_AD_Unit Where UnitID ='" + item.UnitID + "'"); if (userAsset.ContainsKey("Insert") && userAsset["Insert"] && item.CreatedAt == null && item.CreatedBy ==null) { if (isExist != null) { return Json(new { success = false, message = "Đơn vị tính đã tồn tại." }); } string id = ""; var checkID = db.SingleOrDefault<DC_AD_Unit>("SELECT UnitID, Id FROM dbo.DC_AD_Unit ORDER BY Id DESC"); if (checkID != null) { var nextNo = int.Parse(checkID.UnitID.Substring(3, checkID.UnitID.Length - 3)) + 1; id = "UIT" + String.Format("{0:00000000}", nextNo); } else { id = "UIT00000001"; } item.UnitID = id; item.UnitName = !string.IsNullOrEmpty(item.UnitName) ? item.UnitName.Trim() : ""; item.Note = !string.IsNullOrEmpty(item.Note) ? item.Note.Trim() : ""; item.CreatedAt = DateTime.Now; item.CreatedBy = currentUser.UserID; item.UpdatedAt = DateTime.Parse("1900-01-01"); item.UpdatedBy = ""; item.Status = item.Status; db.Insert<DC_AD_Unit>(item); return Json(new { success = true, Code = item.UnitID, createdate = item.CreatedAt, createdby = item.CreatedBy }); } else if (userAsset.ContainsKey("Update") && userAsset["Update"] && isExist != null) { var success = db.Execute(@"UPDATE DC_AD_Unit SET Status = @Status, Note = @Note, UpdatedAt = @UpdatedAt, UpdatedBy = @UpdatedBy, UnitName = @UnitName WHERE UnitID = '" + item.UnitID + "'", new { Status = item.Status, //WHName = !string.IsNullOrEmpty(item.WHName) ? item.WHName.Trim() : "", Note = !string.IsNullOrEmpty(item.Note) ? item.Note.Trim() : "", UpdatedAt = DateTime.Now, UpdatedBy = currentUser.UserID, UnitName = !string.IsNullOrEmpty(item.UnitName) ? item.UnitName.Trim() : "", }) == 1; if (!success) { return Json(new { success = false, message = "Cập nhật không thành công." }); } return Json(new { success = true }); } else return Json(new { success = false, message = "Bạn không có quyền" }); } catch (Exception e) { log.Error(" ListPublication - Create - " + e.Message); return Json(new { success = false, message = e.Message }); } finally { db.Close(); } }