public ActionResult DeleteCountry(string data)
{
if (asset.Delete)
{
try
{
string[] separators = { "@@" };
var listRowID = data.Split(separators, StringSplitOptions.RemoveEmptyEntries);
foreach (var item in listRowID)
{
var checkexist = DC_Location_Region.GetDC_Location_Regions("1=1", "").Where(s => s.CountryID == item);
if (checkexist.Count() > 0)
{
return(Json(new { success = false, alert = "Country exist in Region" }));
}
var check = new DC_Location_Countries();
check.CountryID = item;
check.Delete();
}
}
catch (Exception ex)
{
return(Json(new { success = false, alert = ex.Message }));
}
return(Json(new { success = true }));
}
else
{
return(Json(new { success = false, alert = "You don't have permission to delete record" }));
}
}
public FileResult Export(string where)
{
if (asset.Export)
{
//using (ExcelPackage excelPkg = new ExcelPackage())
FileInfo fileInfo = new FileInfo(Server.MapPath(@"~\ExportExcelFile\DC_Telesales_QuestionList.xlsx"));
var excelPkg = new ExcelPackage(fileInfo);
//data sheet Region.
var listRegion = DC_Location_Region.GetAllDC_Location_Regions().ToList();
ExcelWorksheet dataSheet = excelPkg.Workbook.Worksheets["Region"];
int rowData = 1;
foreach (DC_Location_Region data in listRegion)
{
int i = 1;
rowData++;
dataSheet.Cells[rowData, i++].Value = data.RegionID + ":" + data.RegionName;
}
//data sheet Master.
var master = new List <DC_Telesales_QuestionList>();
using (IDbConnection dbConn = Helpers.OrmliteConnection.openConn())
{
master = dbConn.Select <DC_Telesales_QuestionList>("SELECT * FROM DC_Telesales_QuestionList");
}
ExcelWorksheet dataSheetMaster = excelPkg.Workbook.Worksheets["Master data"];
int rowData1 = 1;
foreach (DC_Telesales_QuestionList data in master)
{
int i = 1;
rowData1++;
foreach (var item in listRegion)
{
if (item.RegionID == data.RegionID)
{
dataSheetMaster.Cells[rowData1, i++].Value = item.RegionID + ": " + item.RegionName;
break;
}
}
dataSheetMaster.Cells[rowData1, i++].Value = data.Question;
dataSheetMaster.Cells[rowData1, i++].Value = data.AnswerType;
dataSheetMaster.Cells[rowData1, i++].Value = data.RowCreatedUser;
dataSheetMaster.Cells[rowData1, i++].Value = data.RowCreatedTime.ToString();
dataSheetMaster.Cells[rowData1, i++].Value = data.RowLastUpdatedUser;
dataSheetMaster.Cells[rowData1, i++].Value = data.RowLastUpdatedTime.ToString();
}
MemoryStream output = new MemoryStream();
excelPkg.SaveAs(output);
string fileName = "DC_Telesales_QuestionList" + where + "_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx";
string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
output.Position = 0;
return(File(output.ToArray(), contentType, fileName));
}
else
{
string fileName = "DC_Telesales_QuestionList" + where + "_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx";
string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
return(File("", contentType, fileName));
}
}
public ActionResult Index()
{
if (asset.View)
{
using (IDbConnection dbConn = Helpers.OrmliteConnection.openConn())
{
ViewData["AllowCreate"] = asset.Create;
ViewData["AllowUpdate"] = asset.Update;
ViewData["AllowDelete"] = asset.Delete;
ViewData["AllowExport"] = asset.Export;
ViewBag.AllowView = true;
ViewBag.canEdit = asset.Update;
ViewBag.listUserName = dbConn.Select <EmployeeInfo>();
ViewBag.listTeam = dbConn.Select <Deca_Code_Master>("CodeType = {0}", "TelesalesAgentTeam");
ViewBag.listTeamDistinc = dbConn.Select <Deca_Code_Master>("CodeType = {0}", "TelesalesAgentTeam");
ViewBag.listRegion = DC_Location_Region.GetAllDC_Location_Regions().ToList();
//ViewBag.listMOPRegion = DC_MOP_Region.GetListRegion();
return(View());
}
}
else
{
return(RedirectToAction("NoAccessRights", "Error"));
}
}
//
// GET: /TelesalePluginCode/
public ActionResult Index()
{
//using (IDbConnection dbConn = Helpers.OrmliteConnection.openConn())
//{
// OrmLiteConfig.DialectProvider.UseUnicode = true;
// dbConn.DropTables(typeof(DC_Org_AvoidCallingTime));
// const bool overwrite = false;
// dbConn.CreateTables(overwrite, typeof(DC_Org_AvoidCallingTime));
//}
using (IDbConnection dbConn = Helpers.OrmliteConnection.openConn())
{
ViewBag.AVoidCallTime = dbConn.Select <DC_AvoidCallingTimeFrame>("SELECT * FROM DC_AvoidCallingTimeFrame");
}
if (asset.View)
{
ViewData["AllowCreate"] = asset.Create;
ViewData["AllowUpdate"] = asset.Update;
ViewData["AllowDelete"] = asset.Delete;
ViewData["AllowExport"] = asset.Export;
ViewData["Asset"] = asset;
using (var dbConn = Helpers.OrmliteConnection.openConn())
{
ViewData["UserGroups"] = dbConn.Select <Groups>();
}
ViewBag.listAvoidCallTime = Deca_Code_Master.GetDeca_Code_Masters("[CodeType]='AvoidCallTime'", "").OrderBy(s => s.CodeID);
ViewBag.listAllowedServices = Deca_Code_Master.GetDeca_Code_Masters("[CodeType]='AllowedServices'", "").OrderBy(s => s.CodeID);
ViewBag.listCollectionType = Deca_Code_Master.GetDeca_Code_Masters("[CodeType]='CollectionType'", "").OrderBy(s => s.CodeID);
ViewBag.listRegion = DC_Location_Region.GetList_Regions();
return(View());
}
else
{
return(RedirectToAction("NoAccessRights", "Error"));
}
}
public FileResult ExportExcel([DataSourceRequest] DataSourceRequest request)
{
if (asset.Export)
{
//var region = DC_Location_Region.GetAllDC_Location_Regions().OrderByDescending(s => s.RegionID).ToList();
var region = DC_Location_Region.GetDC_Location_Regions("1=1", "RegionID DESC");
//using (ExcelPackage excelPkg = new ExcelPackage())
FileInfo fileInfo = new FileInfo(Server.MapPath(@"~\ExportExcelFile\DC_Location_Region.xlsx"));
var excelPkg = new ExcelPackage(fileInfo);
ExcelWorksheet dataSheet = excelPkg.Workbook.Worksheets["DC_Location_Region"];
IEnumerable listData = region.ToDataSourceResult(request).Data;
int rowData = 1;
foreach (DC_Location_Region data in listData)
{
int i = 1;
rowData++;
dataSheet.Cells[rowData, i++].Value = data.RegionID;
dataSheet.Cells[rowData, i++].Value = data.RegionName;
dataSheet.Cells[rowData, i++].Value = (!String.IsNullOrEmpty(data.CountryID) ? data.CountryID : "") + " - " + (!String.IsNullOrEmpty(data.CountryName) ? data.CountryName : "");
dataSheet.Cells[rowData, i++].Value = data.AliasName;
dataSheet.Cells[rowData, i++].Value = data.Active;
dataSheet.Cells[rowData, i++].Value = data.RowCreatedTime.ToString();
dataSheet.Cells[rowData, i++].Value = data.RowCreatedUser;
if (data.RowLastUpdatedTime.ToString("dd/MM/yyyy") != "01/01/1900")
{
dataSheet.Cells[rowData, i++].Value = data.RowLastUpdatedTime.ToString();
}
else
{
dataSheet.Cells[rowData, i++].Value = "";
}
dataSheet.Cells[rowData, i++].Value = data.RowLastUpdatedUser;
}
var listCountry = DC_Location_Countries.GetAllDC_Location_Countries().OrderByDescending(s => s.AliasID).ToList();
ExcelWorksheet dataSheetAliasID = excelPkg.Workbook.Worksheets["List"];
IEnumerable listDataAliasID = listCountry.ToDataSourceResult(request).Data;
int rowDataAliasID = 1;
foreach (DC_Location_Countries data in listDataAliasID)
{
int i = 1;
rowDataAliasID++;
dataSheetAliasID.Cells[rowDataAliasID, i++].Value = (!String.IsNullOrEmpty(data.CountryID) ? data.CountryID : "") + " - " + (!String.IsNullOrEmpty(data.CountryID) ? data.CountryName : "");
}
MemoryStream output = new MemoryStream();
excelPkg.SaveAs(output);
string fileName = "DC_Location_Region_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx";
string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
output.Position = 0;
return(File(output.ToArray(), contentType, fileName));
}
else
{
string fileName = "DC_Location_Region_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx";
string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
return(File("", contentType, fileName));
}
}
public FileResult ExportExcel([DataSourceRequest] DataSourceRequest request)
{
if (asset.Export)
{
var list = DC_Location_MappingLocation.GetAllDC_Location_Mappinged_City().ToList();
//using (ExcelPackage excelPkg = new ExcelPackage())
FileInfo fileInfo = new FileInfo(Server.MapPath(@"~\ExportExcelFile\DC_Location_MappingLocation.xlsx"));
var excelPkg = new ExcelPackage(fileInfo);
//data sheet
ExcelWorksheet dataSheet = excelPkg.Workbook.Worksheets["DC_Location_MappingLocation"];
IEnumerable listData = list.ToDataSourceResult(request).Data;
int rowData = 1;
foreach (DC_Location_MappingLocation data in listData)
{
int i = 1;
rowData++;
dataSheet.Cells[rowData, i++].Value = data.CityID;
dataSheet.Cells[rowData, i++].Value = data.CityName;
dataSheet.Cells[rowData, i++].Value = data.RegionID + " - " + data.RegionName;
}
//data sheet
var region = DC_Location_Region.GetAllDC_Location_Regions();
ExcelWorksheet dataSheetList = excelPkg.Workbook.Worksheets["List"];
IEnumerable listDataRegion = region.ToDataSourceResult(request).Data;
int rowDataRegion = 1;
foreach (DC_Location_Region data in listDataRegion)
{
int i = 1;
rowDataRegion++;
dataSheetList.Cells[rowDataRegion, i++].Value = data.RegionID + " - " + data.RegionName;
}
MemoryStream output = new MemoryStream();
excelPkg.SaveAs(output);
string fileName = "DC_Location_MappingLocation_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx";
string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
output.Position = 0;
return(File(output.ToArray(), contentType, fileName));
}
else
{
string fileName = "DC_Location_MappingLocation_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx";
string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
return(File("", contentType, fileName));
}
}
public ActionResult Region_Update([DataSourceRequest] DataSourceRequest request, [Bind(Prefix = "models")] IEnumerable <DC_Location_Region> listEx)
{
if (asset.Update)
{
try
{
if (listEx != null && ModelState.IsValid)
{
foreach (var regis in listEx)
{
if (String.IsNullOrEmpty(regis.RegionName))
{
ModelState.AddModelError("", "Please input Region Name ");
return(Json(listEx.ToDataSourceResult(request, ModelState)));
}
//if (String.IsNullOrEmpty(regis.CountryID))
//{
// ModelState.AddModelError("", "Please input Country Name");
// return Json(listEx.ToDataSourceResult(request, ModelState));
//}
var write = new DC_Location_Region();
var check = DC_Location_Region.GetDC_Location_Regions("1=1", "").Where(s => s.RegionName.Trim().ToLower() == regis.RegionName.Trim().ToLower() && s.CountryID == regis.CountryID && s.Active == regis.Active);
if (check.Count() > 0)
{
ModelState.AddModelError("", "Region Name is exists.");
return(Json(listEx.ToDataSourceResult(request, ModelState)));
}
write.RegionID = regis.RegionID;
write.RegionName = regis.RegionName.Trim();
write.Active = regis.Active;
write.RowLastUpdatedTime = DateTime.Now;
write.RowLastUpdatedUser = currentUser.UserName;
write.CountryID = regis.CountryID != null ? regis.CountryID : "";
write.Update();
}
}
else
{
ModelState.AddModelError("error", "");
return(Json(new { success = false }));
}
}
catch (Exception e)
{
ModelState.AddModelError("error", e.Message);
return(Json(listEx.ToDataSourceResult(request, ModelState)));
}
}
else
{
ModelState.AddModelError("", "You don't have permission to update record");
return(Json(listEx.ToDataSourceResult(request, ModelState)));
}
return(Json(listEx.ToDataSourceResult(request, ModelState)));
}
public ActionResult Region_Read([DataSourceRequest] DataSourceRequest request)
{
var data = new List <DC_Location_Region>();
if (request.Filters.Any())
{
var where = KendoApplyFilter.ApplyFilter(request.Filters[0], "data.");
data = DC_Location_Region.GetDC_Location_Regions(where, "RegionID DESC");
}
else
{
data = DC_Location_Region.GetDC_Location_Regions("1=1", "RegionID DESC");
}
return(Json(data.ToDataSourceResult(request)));
}
//
// GET: /CityDefinition/
public ActionResult Index()
{
if (asset.View)
{
ViewData["AllowCreate"] = asset.Create;
ViewData["AllowUpdate"] = asset.Update;
ViewData["AllowDelete"] = asset.Delete;
ViewData["AllowExport"] = asset.Export;
ViewBag.listalias = DC_Location_Alias.GetDC_Location_Alias("1=1", "AliasName ASC");
ViewBag.listcountry = DC_Location_Countries.GetDC_Location_Countries("1=1", "CountryName ASC");
ViewBag.listregion = DC_Location_Region.GetDC_Location_Regions("1=1", "RegionName ASC");
return(View());
}
else
{
return(RedirectToAction("NoAccessRights", "Error"));
}
}
public ActionResult listRegion()
{
var data = DC_Location_Region.GetAllDC_Location_Regions().ToList();
return(Json(data, JsonRequestBehavior.AllowGet));
}
public ActionResult GetBranch()
{
var data = DC_Location_Region.GetAllDC_Location_Regions();
return(Json(data, JsonRequestBehavior.AllowGet));
}
public ActionResult Read_Agent_Branch_Region([DataSourceRequest] DataSourceRequest request)
{
var data = DC_Location_Region.GetAllDC_Location_Regions();
return(Json(data.ToDataSourceResult(request)));
}
public FileResult TelesalesAgent_Export([DataSourceRequest]
DataSourceRequest request)
{
if (asset.Export)
{
using (var dbConn = Helpers.OrmliteConnection.openConn())
{
var UserName = currentUser.UserName;
List <DC_Telesales_Agent> d = DC_Telesales_Agent.GetDC_Telesales_Agent();
var listRequest = d;
//Get the data representing the current grid state - page, sort and filter
IEnumerable datas = listRequest.ToDataSourceResult(request).Data;
//Create new Excel workbook
FileStream fs = new FileStream(Server.MapPath(@"~\ExportExcelFile\DC_TelesaleAgent.xls"), FileMode.Open, FileAccess.Read);
var workbook = new HSSFWorkbook(fs, true);
//Create new Excel sheet
var sheetTeam = workbook.GetSheet("List");
int rowNumber1 = 1;
//Populate the sheet with values from the grid data
var list = dbConn.Select <DC_Telesales_Agent>().Select(s => s.Team.Distinct());
foreach (string data in list)
{
//Create a new row
var row = sheetTeam.CreateRow(rowNumber1++);
//Set values for the cells
row.CreateCell(0).SetCellValue(data);
}
//Create new Excel sheet
var sheetRegion = workbook.GetSheet("ListRegion");
int rowNumberRegion = 1;
//Populate the sheet with values from the grid data
var listRegion = DC_Location_Region.GetAllDC_Location_Regions().ToList();
foreach (var data in listRegion)
{
//Create a new row
var row = sheetRegion.CreateRow(rowNumberRegion++);
//Set values for the cells
row.CreateCell(0).SetCellValue(data.RegionName);
}
//Create new Excel sheet
var sheet = workbook.GetSheet("TelesaleAgent");
int rowNumber = 1;
//Populate the sheet with values from the grid data
foreach (DC_Telesales_Agent data in datas)
{
//Create a new row
var row = sheet.CreateRow(rowNumber++);
//Set values for the cells
row.CreateCell(0).SetCellValue(data.UserName);
row.CreateCell(1).SetCellValue(data.Team);
row.CreateCell(2).SetCellValue(data.Region);
row.CreateCell(3).SetCellValue(data.XLiteID);
}
//Write the workbook to a memory stream
MemoryStream output = new MemoryStream();
workbook.Write(output);
//Return the result to the end user
return(File(output.ToArray(), //The binary data of the XLS file
"application/vnd.ms-excel", //MIME type of Excel files
"DC_TelesaleAgent" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xls")); //Suggested file name in the "Save as" dialog which will be displayed to the end user
}
}
else
{
ModelState.AddModelError("", "You don't have permission to export data");
return(File("", //The binary data of the XLS file
"application/vnd.ms-excel", //MIME type of Excel files
"DC_TelesaleAgent" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xls")); //Suggested file name in the "Save as" dialog which will be displayed to the end user
}
}
public FileResult ExportExcel([DataSourceRequest] DataSourceRequest request)
{
if (asset.Export)
{
using (IDbConnection dbConn = Helpers.OrmliteConnection.openConn())
{
var country = new List <DC_Location_District>();
if (request.Filters.Any())
{
var where = KendoApplyFilter.ApplyFilter(request.Filters[0], "data.");
country = DC_Location_District.GetDC_Location_Districts(where, "DistrictID DESC");
}
else
{
country = DC_Location_District.GetDC_Location_Districts("1=1", "DistrictID DESC");
}
//using (ExcelPackage excelPkg = new ExcelPackage())
FileInfo fileInfo = new FileInfo(Server.MapPath(@"~\ExportExcelFile\DC_Location_District.xlsx"));
var excelPkg = new ExcelPackage(fileInfo);
//data sheet
ExcelWorksheet dataSheet = excelPkg.Workbook.Worksheets["DC_Location_District"];
IEnumerable listData = country.ToDataSourceResult(request).Data;
int rowData = 1;
foreach (DC_Location_District data in listData)
{
int i = 1;
rowData++;
dataSheet.Cells[rowData, i++].Value = data.DistrictID;
dataSheet.Cells[rowData, i++].Value = data.DistrictName;
dataSheet.Cells[rowData, i++].Value = (!String.IsNullOrEmpty(data.CityID) ? data.CityID : "") + " - " + (!String.IsNullOrEmpty(data.CityName) ? data.CityName : "");
dataSheet.Cells[rowData, i++].Value = data.RegionName;
dataSheet.Cells[rowData, i++].Value = data.CountryName;
dataSheet.Cells[rowData, i++].Value = data.AliasName;
dataSheet.Cells[rowData, i++].Value = data.Active;
dataSheet.Cells[rowData, i++].Value = data.RowCreatedTime.ToString();
dataSheet.Cells[rowData, i++].Value = data.RowCreatedUser;
if (data.RowLastUpdatedTime != null)
{
dataSheet.Cells[rowData, i++].Value = data.RowLastUpdatedTime.ToString();
}
else
{
dataSheet.Cells[rowData, i++].Value = "";
}
dataSheet.Cells[rowData, i++].Value = data.RowLastUpdatedUser;
}
//data sheet
var alias = DC_Location_Region.GetDC_Location_Regions("1=1", "CountryID DESC").ToList();
ExcelWorksheet dataSheetAliasID = excelPkg.Workbook.Worksheets["List"];
IEnumerable listDataAliasID = alias.ToDataSourceResult(request).Data;
int rowDataAliasID = 1;
foreach (DC_Location_Region data in listDataAliasID)
{
int i = 1;
rowDataAliasID++;
dataSheetAliasID.Cells[rowDataAliasID, i++].Value = (!String.IsNullOrEmpty(data.RegionID) ? data.RegionID : "") + " - " + (!String.IsNullOrEmpty(data.RegionName) ? data.RegionName : "");
}
MemoryStream output = new MemoryStream();
excelPkg.SaveAs(output);
string fileName = "DC_Location_District_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx";
string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
output.Position = 0;
return(File(output.ToArray(), contentType, fileName));
}
}
else
{
string fileName = "DC_Location_District_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx";
string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
return(File("", contentType, fileName));
}
}
public ActionResult Region_Create([DataSourceRequest] DataSourceRequest request, [Bind(Prefix = "models")] IEnumerable <DC_Location_Region> listEx)
{
if (asset.Create)
{
try
{
if (listEx != null && ModelState.IsValid)
{
foreach (var regis in listEx)
{
if (String.IsNullOrEmpty(regis.RegionName))
{
ModelState.AddModelError("", "Please input Region Name ");
return(Json(listEx.ToDataSourceResult(request, ModelState)));
}
//if (String.IsNullOrEmpty(regis.CountryID))
//{
// ModelState.AddModelError("", "Please input Country Name");
// return Json(listEx.ToDataSourceResult(request, ModelState));
//}
string id = "";
var write = new DC_Location_Region();
var checkID = DC_Location_Region.GetDC_Location_Regions("1=1", "").OrderByDescending(m => m.RowID).FirstOrDefault();
if (checkID != null)
{
var nextNo = Int32.Parse(checkID.RegionID.Substring(1, checkID.RegionID.Length - 1)) + 1;
id = "A" + String.Format("{0:0000}", nextNo);
}
else
{
id = "A0001";
}
var check = DC_Location_Region.GetDC_Location_Regions("1=1", "").Where(s => s.RegionName.Trim().ToLower() == regis.RegionName.Trim().ToLower() && s.CountryID == regis.CountryID && s.Active == regis.Active).FirstOrDefault();
if (check != null)
{
ModelState.AddModelError("", " Region Name is exists.");
return(Json(listEx.ToDataSourceResult(request, ModelState)));
}
write.RegionID = id;
write.RegionName = regis.RegionName.Trim();
write.Active = regis.Active;
write.RowCreatedTime = DateTime.Now;
write.RowCreatedUser = currentUser.UserName;
write.CountryID = regis.CountryID != null ? regis.CountryID : "";
write.Save();
}
}
else
{
ModelState.AddModelError("error", "");
return(Json(new { success = false }));
}
}
catch (Exception e)
{
ModelState.AddModelError("error", e.Message);
return(Json(listEx.ToDataSourceResult(request, ModelState)));
}
}
else
{
ModelState.AddModelError("", "You don't have permission to create record");
return(Json(listEx.ToDataSourceResult(request, ModelState)));
}
return(Json(listEx.ToDataSourceResult(request, ModelState)));
}
public ActionResult ImportFromExcel()
{
try
{
if (Request.Files["FileUpload"] != null && Request.Files["FileUpload"].ContentLength > 0)
{
string fileExtension =
System.IO.Path.GetExtension(Request.Files["FileUpload"].FileName);
if (fileExtension == ".xlsx")
{
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);
//Request.Files["fileUpload"].SaveAs(errorFileLocation);
var rownumber = 2;
var total = 0;
FileInfo fileInfo = new FileInfo(fileLocation);
var excelPkg = new ExcelPackage(fileInfo);
FileInfo template = new FileInfo(Server.MapPath(@"~\ExportExcelFile\DC_Location_Region.xlsx"));
template.CopyTo(errorFileLocation);
FileInfo _fileInfo = new FileInfo(errorFileLocation);
var _excelPkg = new ExcelPackage(_fileInfo);
ExcelWorksheet oSheet = excelPkg.Workbook.Worksheets["DC_Location_Region"];
ExcelWorksheet eSheet = _excelPkg.Workbook.Worksheets["DC_Location_Region"];
//remove row
int totalRows = oSheet.Dimension.End.Row;
for (int i = 2; i <= totalRows; i++)
{
string regionID = oSheet.Cells[i, 1].Value != null ? oSheet.Cells[i, 1].Value.ToString() : "";
string regionName = oSheet.Cells[i, 2].Value != null ? oSheet.Cells[i, 2].Value.ToString() : "";
string countryName = oSheet.Cells[i, 3].Value != null ? oSheet.Cells[i, 3].Value.ToString() : "";
string active = oSheet.Cells[i, 5].Value != null ? oSheet.Cells[i, 5].Value.ToString() : "TRUE";
string id_country = !String.IsNullOrEmpty(countryName) ? countryName.Substring(0, countryName.LastIndexOf("-")).Trim() : "";
try
{
var write = new DC_Location_Region();
var checkRegion = DC_Location_Region.GetDC_Location_Regions("[RegionName] = N'" + regionName + "' AND [CountryID] = '" + id_country + "'", "").FirstOrDefault();
var checkCountry = DC_Location_Countries.GetDC_Location_Countries("[CountryID] = '" + id_country + "'", "").FirstOrDefault();
if (string.IsNullOrEmpty(regionName.ToString()) || string.IsNullOrEmpty(countryName.ToString()))
{
eSheet.Cells[rownumber, 2].Value = regionName;
eSheet.Cells[rownumber, 3].Value = countryName;
eSheet.Cells[rownumber, 5].Value = active;
eSheet.Cells[rownumber, 10].Value = "regionName, countryName required";
rownumber++;
}
else if (checkRegion != null)
{
write.RegionID = regionID;
write.RegionName = regionName;
write.Active = Convert.ToBoolean(active);
write.RowLastUpdatedTime = DateTime.Now;
write.RowLastUpdatedUser = currentUser.UserName;
write.CountryID = id_country;
write.Update();
total++;
}
else if (checkCountry == null)
{
eSheet.Cells[rownumber, 2].Value = regionName;
eSheet.Cells[rownumber, 3].Value = countryName;
eSheet.Cells[rownumber, 5].Value = active;
eSheet.Cells[rownumber, 10].Value = "countryName not exist in system";
rownumber++;
}
else
{
string id = "";
var checkID = DC_Location_Region.GetDC_Location_Regions("1=1", "").OrderByDescending(m => m.RowID).FirstOrDefault();
if (checkID != null)
{
var nextNo = Int32.Parse(checkID.RegionID.Substring(1, checkID.RegionID.Length - 1)) + 1;
id = "A" + String.Format("{0:0000}", nextNo);
}
else
{
id = "A0001";
}
write.RegionID = id;
write.RegionName = regionName;
write.Active = bool.Parse(active);
write.RowCreatedTime = DateTime.Now;
write.RowCreatedUser = currentUser.UserName;
write.CountryID = id_country;
write.Save();
total++;
}
}
catch (Exception e)
{
eSheet.Cells[rownumber, 2].Value = regionName;
eSheet.Cells[rownumber, 3].Value = countryName;
eSheet.Cells[rownumber, 5].Value = active;
eSheet.Cells[rownumber, 10].Value = e.Message;
rownumber++;
continue;
}
}
_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 }));
}
}
