public ActionResult Delete(string data) { using (var dbConn = Helpers.OrmliteConnection.openConn()) if (asset.Delete) { int success = 0; int error = 0; try { string[] separators = { "@@" }; var listRowID = data.Split(separators, StringSplitOptions.RemoveEmptyEntries); var data1 = new DC_AvoidCallingTimeFrame(); foreach (var item in listRowID) { var checkexist = dbConn.Select <DC_DetailAvoidCallingTimeFrame>("SELECT TOP 1 * FROM DC_DetailAvoidCallingTimeFrame WHERE HeaderID = '" + item + "'").FirstOrDefault(); if (checkexist == null) { data1.Id = Int32.Parse(item); dbConn.Delete(data1); success++; } else { error++; return(Json(new { success = false, alert = "Không thể xóa vì đã có thông tin chi tiết!" })); } } } catch (Exception ex) { return(Json(new { success = false, alert = ex.Message })); } return(Json(new { success = true, totalSuccess = success, totalError = error })); } else { return(Json(new { success = false, alert = "You don't have permission to delete record" })); } }
public ActionResult ImportFromExcel_AvoidCalling() { try { if (Request.Files["FileUpload"] != null && Request.Files["FileUpload"].ContentLength > 0) { string fileExtension = System.IO.Path.GetExtension(Request.Files["FileUpload"].FileName); if (fileExtension == ".xlsx") { using (IDbConnection dbConn = Helpers.OrmliteConnection.openConn()) using (var dbTrans = dbConn.OpenTransaction(IsolationLevel.ReadCommitted)) { string fileLocation = string.Format("{0}/{1}", Server.MapPath("~/Excel"), "[" + currentUser.UserName + "-" + DateTime.Now.ToString("yyyyMMddHHmmss") + "]" + Request.Files["FileUpload"].FileName); string errorFileLocation = string.Format("{0}/{1}", Server.MapPath("~/Excel"), "[" + currentUser.UserName + "-" + DateTime.Now.ToString("yyyyMMddHHmmss") + "-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(@"~\ExportExcelFile\DC_AvoidCallingTimeFrame.xlsx")); template.CopyTo(errorFileLocation); FileInfo _fileInfo = new FileInfo(errorFileLocation); var _excelPkg = new ExcelPackage(_fileInfo); ExcelWorksheet oSheet = excelPkg.Workbook.Worksheets["AvoidCallingTimeFrame"]; ExcelWorksheet eSheet = _excelPkg.Workbook.Worksheets["AvoidCallingTimeFrame"]; 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 HeaderID = oSheet.Cells[i, 2].Value != null ? oSheet.Cells[i, 2].Value.ToString() : ""; string HeaderName = oSheet.Cells[i, 3].Value != null ? oSheet.Cells[i, 3].Value.ToString() : ""; string RowCreatedTime = oSheet.Cells[i, 4].Value != null ? oSheet.Cells[i, 4].Value.ToString() : ""; string RowCreatedUser = oSheet.Cells[i, 5].Value != null ? oSheet.Cells[i, 5].Value.ToString() : ""; string RowLastUpdatedTime = oSheet.Cells[i, 6].Value != null ? oSheet.Cells[i, 6].Value.ToString() : ""; string RowLastUpdatedUser = oSheet.Cells[i, 7].Value != null ? oSheet.Cells[i, 7].Value.ToString() : ""; try { if (!String.IsNullOrEmpty(HeaderName)) { var data = new DC_AvoidCallingTimeFrame(); var checkexist = dbConn.Select <DC_AvoidCallingTimeFrame>("SELECT TOP 1 * FROM DC_AvoidCallingTimeFrame WHERE Id = " + Id + "").FirstOrDefault(); if (checkexist != null) { checkexist.HeaderName = HeaderName != null ? HeaderName : ""; checkexist.RowLastUpdatedTime = DateTime.Now; checkexist.RowLastUpdatedUser = currentUser.UserName; dbConn.Update(checkexist); total++; } else { string id = ""; var checkID = dbConn.Select <DC_AvoidCallingTimeFrame>("SELECT TOP 1 * FROM dbo.DC_AvoidCallingTimeFrame ORDER BY Id DESC").FirstOrDefault(); if (checkID != null) { var nextNo = Int32.Parse(checkID.HeaderID.Substring(4, checkID.HeaderID.Length - 4)) + 1; id = "ACTF" + String.Format("{0:00000}", nextNo); } else { id = "ACTF00001"; } data.HeaderID = id; data.HeaderName = HeaderName; data.RowCreatedTime = DateTime.Now; data.RowCreatedUser = currentUser.UserName; data.RowLastUpdatedTime = DateTime.Parse("1900-01-01"); data.RowLastUpdatedUser = ""; dbConn.Save(data); total++; } } } catch (Exception e) { eSheet.Cells[rownumber, 1].Value = Id; eSheet.Cells[rownumber, 2].Value = HeaderID; eSheet.Cells[rownumber, 3].Value = HeaderName; eSheet.Cells[rownumber, 4].Value = RowCreatedTime; eSheet.Cells[rownumber, 5].Value = RowCreatedUser; eSheet.Cells[rownumber, 6].Value = RowLastUpdatedTime; eSheet.Cells[rownumber, 7].Value = RowLastUpdatedUser; eSheet.Cells[rownumber, 8].Value = e.Message; rownumber++; continue; } } dbTrans.Commit(); _excelPkg.Save(); return(Json(new { success = true, total = total, totalError = rownumber - 2, link = errorFileLocation })); } } else { return(Json(new { success = false, error = "File extension is not valid. *.xlsx please." })); } } else { return(Json(new { success = false, error = "File upload null" })); } } catch (Exception ex) { return(Json(new { success = false, error = ex.Message })); } }
public ActionResult Save_AvoidCallingTimeFrame([DataSourceRequest] DataSourceRequest request, [Bind(Prefix = "models")] IEnumerable <DC_AvoidCallingTimeFrame> listAvoiCalling) { using (var dbConn = Helpers.OrmliteConnection.openConn()) using (var dbTrans = dbConn.OpenTransaction(IsolationLevel.ReadCommitted)) if (asset.Create) { try { if (listAvoiCalling != null && ModelState.IsValid) { foreach (var typ in listAvoiCalling) { if (String.IsNullOrEmpty(typ.HeaderName)) { ModelState.AddModelError("", "Please input Header name"); return(Json(listAvoiCalling.ToDataSourceResult(request, ModelState))); } string id = ""; var data = new DC_AvoidCallingTimeFrame(); var checkID = dbConn.Select <DC_AvoidCallingTimeFrame>("SELECT TOP 1 * FROM dbo.DC_AvoidCallingTimeFrame ORDER BY Id DESC").FirstOrDefault(); if (checkID != null) { var nextNo = Int32.Parse(checkID.HeaderID.Substring(4, checkID.HeaderID.Length - 4)) + 1; id = "ACTF" + String.Format("{0:00000}", nextNo); } else { id = "ACTF00001"; } var checkexist = dbConn.Select <DC_AvoidCallingTimeFrame>("SELECT TOP 1 * FROM DC_AvoidCallingTimeFrame WHERE HeaderName = '" + typ.HeaderName + "'").FirstOrDefault(); if (checkexist != null) { ModelState.AddModelError("", " Header Name is exists."); return(Json(listAvoiCalling.ToDataSourceResult(request, ModelState))); } data.HeaderID = id; data.HeaderName = typ.HeaderName.Trim(); data.RowCreatedTime = DateTime.Now; data.RowCreatedUser = currentUser.UserName; data.RowLastUpdatedTime = DateTime.Parse("1900-01-01"); data.RowLastUpdatedUser = ""; dbConn.Insert(data); } dbTrans.Commit(); return(Json(new { success = true })); } else { ModelState.AddModelError("error", ""); dbTrans.Rollback(); return(Json(new { success = false })); } } catch (Exception e) { ModelState.AddModelError("error", e.Message); dbTrans.Rollback(); return(Json(listAvoiCalling.ToDataSourceResult(request, ModelState))); } } else { ModelState.AddModelError("", "You don't have permission to create record"); dbTrans.Rollback(); return(Json(listAvoiCalling.ToDataSourceResult(request, ModelState))); } }