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)));
}
}
