public ActionResult RemoveAgent1(string data)
{
if (asset.Delete)
{
using (var dbConn = Helpers.OrmliteConnection.openConn())
{
int count = 0;
int total = 0;
string message = "";
string[] separators = { ";;" };
var listdata = data.Split(separators, StringSplitOptions.RemoveEmptyEntries);
var check = new DC_TeleSale_Agent_Region();
foreach (var id in listdata)
{
try
{
check.Id = int.Parse(id);
var datacheck = dbConn.Select <DC_TeleSale_Agent_Region>("Id={0}", id).FirstOrDefault();
dbConn.Delete(check);
total++;
}
catch (Exception ex)
{
message = ex.Message;
count = 1;
}
}
return(Json(new { error = count, success = total, message = message }));
}
}
else
{
return(Json(new { error = 1, success = 0, message = "You don't have permisson to Delete" }));
}
}
public ActionResult Create_Agent_Branch_Region([DataSourceRequest] DataSourceRequest request, [Bind(Prefix = "models")] IEnumerable <DC_TeleSale_Agent_Region> listResult)
{
if (asset.Update && asset.Update)
{
using (var dbConn = Helpers.OrmliteConnection.openConn())
{
try
{
if (listResult != null && ModelState.IsValid)
{
foreach (var item in listResult)
{
if (string.IsNullOrEmpty(item.RegionID))
{
ModelState.AddModelError("", "Please input RegionID");
return(Json(listResult.ToDataSourceResult(request, ModelState)));
}
var dataBranch = dbConn.Select <DC_Location_Region>().ToList();
if (dataBranch.Count > 0)
{
foreach (var item1 in dataBranch)
{
var exist = dbConn.Select <DC_TeleSale_Agent_Region>("Select * from DC_TeleSale_Agent_Branch_Region Where UserName ='******' AND RegionID = '" + item1.RegionID + "'").ToList();
if (exist.Count == 0)
{
var dataA = new DC_TeleSale_Agent_Region();
dataA.RegionID = item1.RegionID;
dataA.RowCreatedTime = DateTime.Now;
dataA.RowCreatedUser = currentUser.UserName;
dataA.RowLastUpdatedTime = DateTime.Parse("1900-01-01");
dataA.RowLastUpdatedUser = "";
dataA.UserName = item.UserName;
dbConn.Insert(dataA);
}
}
}
}
return(Json(new { success = true }));
}
else
{
ModelState.AddModelError("error", "");
return(Json(new { success = false }));
}
}
catch (Exception e)
{
return(Json(new { success = false, error = e.Message }));
}
}
}
else
{
ModelState.AddModelError("", "You don't have permission to create record ");
return(Json(listResult.ToDataSourceResult(request, ModelState)));
}
}
public ActionResult Branch_Remove([DataSourceRequest] DataSourceRequest request, string UserName)
{
using (var dbConn = Helpers.OrmliteConnection.openConn())
{
string[] separators = { "@@" };
var data = Request.Form["data"].Split(separators, StringSplitOptions.RemoveEmptyEntries);
try
{
var data1 = new DC_TeleSale_Agent_Region();
for (int i = 0; i < data.Length; i++)
{
data1.Id = int.Parse(data[i]);
dbConn.Delete(data1);
}
return(Json(new { success = true }));
}
catch (Exception e)
{
return(Json(new { success = false, alert = e.Message }));
}
}
}
public ActionResult SaveBranchIDRegionID(string listUser, string RegionID)
{
int dataCount = 0;
using (var dbConn = Helpers.OrmliteConnection.openConn())
{
try
{
listUser = listUser.Remove(listUser.Length - 1);
DC_TeleSale_Agent_Region newrecords = new DC_TeleSale_Agent_Region();
string[] separators = { "," };
var data = listUser.Split(separators, StringSplitOptions.RemoveEmptyEntries);
for (int i = 0; i < data.Length; i++)
{
//check exist DC_TeleSale_Agent_Branch_Region with UserName AND BranchID AND RegionID
var exist = dbConn.Select <DC_TeleSale_Agent_Region>("UserName ='******' AND RegionID = '" + RegionID + "'").ToList();
if (exist.Count == 0)
{
var dataA = new DC_TeleSale_Agent_Region();
dataA.RegionID = RegionID;
dataA.RowCreatedTime = DateTime.Now;
dataA.RowCreatedUser = currentUser.UserName;
dataA.RowLastUpdatedTime = DateTime.Parse("1900-01-01");
dataA.RowLastUpdatedUser = "";
dataA.UserName = data[i];
dbConn.Insert(dataA);
dataCount++;
}
}
return(Json(new { success = true, dataCount = dataCount }));
}
catch (Exception e)
{
return(Json(new { success = false, error = e.Message }));
}
}
}
public ActionResult Branch_Region_Read([DataSourceRequest] DataSourceRequest request, string UserName)
{
List <DC_TeleSale_Agent_Region> data = DC_TeleSale_Agent_Region.GetReadData(UserName);
return(Json(data.ToDataSourceResult(request)));
}
public ActionResult ImportFromExcelAgent_Branch_Region()
{
try
{
if (ModelState.IsValid)
{
List <DC_TeleSale_Agent_Region> listData = new List <DC_TeleSale_Agent_Region>();
int total = 0;
/////create excel error file
string filename = DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx";
int rowData = 1;
FileInfo fileInfo = new FileInfo(Server.MapPath(@"~\ExportExcelFile\DC_TeleSale_Agent_Branch_Region.xlsx"));
var excelPkg = new ExcelPackage(fileInfo);
ExcelWorksheet dataSheet = excelPkg.Workbook.Worksheets["Agent Master"];
string ErrorfileLocation = string.Format("{0}/{1}", Server.MapPath("~/Excel"), "DC_TeleSale_Agent_Branch_Region Error_" + filename);
FileStream fileStream = new FileStream(ErrorfileLocation, FileMode.Create, FileAccess.Write);
if (Request.Files["FileUpload"] != null && Request.Files["fileUpload"].ContentLength > 0)
{
string fileExtension =
System.IO.Path.GetExtension(Request.Files["fileUpload"].FileName);
if (fileExtension == ".xls" || fileExtension == ".xlsx")
{
// Create a folder in App_Data named ExcelFiles because you need to save the file temporarily location and getting data from there.
string fileLocation = string.Format("{0}/{1}", Server.MapPath("~/Excel"), "[" + currentUser.UserName + "-" + DateTime.Now.ToString("yyyyMMddHHmmss") + "]" + Request.Files["fileUpload"].FileName);
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
Request.Files["fileUpload"].SaveAs(fileLocation);
string excelConnectionString = string.Empty;
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
excelConnection.Open();
DataTable dt = new DataTable();
dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
return(null);
}
String[] excelSheets = new String[dt.Rows.Count];
OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);
DataSet ds = new DataSet();
string query = "Select * from ['Agent Master$']";
using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
{
dataAdapter.Fill(ds);
}
var exist_UserName = false;
var exist_BranchID = false;
foreach (var item in ds.Tables[0].Columns)
{
if (item.ToString() == "UserName")
{
exist_UserName = true;
}
if (item.ToString() == "BranchID")
{
exist_BranchID = true;
}
}
if (exist_UserName)
{
using (var dbConn = Helpers.OrmliteConnection.openConn())
{
var listBranch = dbConn.Select <DC_Location_Region>();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
int err1 = 0;
DC_TeleSale_Agent_Region icare = new DC_TeleSale_Agent_Region();
DC_TeleSale_Agent_Region icareerr = new DC_TeleSale_Agent_Region();
icare.UserName = icareerr.UserName = ds.Tables[0].Rows[i]["UserName"].ToString();
string BranchID = ds.Tables[0].Rows[i]["BranchID"].ToString();
if (string.IsNullOrEmpty(icare.UserName) || string.IsNullOrEmpty(BranchID))
{
icareerr.ImportNote = "Vui lòng nhập đủ các thông tin bắt buộc: UserName và Branch";
listData.Add(icareerr);
err1 = 1;
}
if (err1 == 0)
{
if (exist_UserName)
{
icare.UserName = ds.Tables[0].Rows[i]["UserName"].ToString();
}
var dataBranch = dbConn.Select <DC_Location_Region>().ToList();
if (dataBranch.Count > 0)
{
foreach (var item1 in dataBranch)
{
var exist = dbConn.Select <DC_TeleSale_Agent_Region>("Select * from DC_TeleSale_Agent_Branch_Region Where UserName ='******' AND RegionID = '" + item1.RegionID + "'").ToList();
if (exist.Count == 0)
{
icare.UserName = icare.UserName;
icare.RegionID = item1.RegionID;
icare.RowCreatedTime = DateTime.Now;
icare.RowCreatedUser = currentUser.UserName;
icare.RowLastUpdatedTime = DateTime.Parse("1900-01-01");
icare.RowLastUpdatedUser = "";
dbConn.Insert(icare);
}
}
}
}
if (err1 == 1)
{
rowData++;
dataSheet.Cells[rowData, 1].Value = icareerr.UserName;
if (exist_UserName)
{
dataSheet.Cells[rowData, 2].Value = ds.Tables[0].Rows[i]["UserName"].ToString();
}
if (exist_BranchID)
{
dataSheet.Cells[rowData, 3].Value = ds.Tables[0].Rows[i]["BranchID"].ToString();
}
}
else
{
total++;
}
}
}
}
else
{
return(Json(new { success = false, message = "Need Field CampaignID and CampaignName" }));
}
excelPkg.SaveAs(fileStream);
fileStream.Close();
excelConnection.Close();
excelConnection1.Close();
}
else
{
return(Json(new { success = false, message = "Plese select Excel File." }));
}
}
return(Json(new { success = true, data = listData, total = total, link = filename }));
}
else
{
return(Json(new { success = false, message = "You don't havve permission import" }));
}
}
catch (Exception ex)
{
return(Json(new { success = false, message = ex.Message }));
}
}
public FileResult Agent_Branch_Region_Export([DataSourceRequest] DataSourceRequest request)
{
if (asset.Export)
{
using (var dbConn = Helpers.OrmliteConnection.openConn())
{
var listdata = DC_TeleSale_Agent_Region.GetAll();
IEnumerable datas = listdata.ToDataSourceResult(request).Data;
//using (ExcelPackage excelPkg = new ExcelPackage())
FileInfo fileInfo = new FileInfo(Server.MapPath(@"~\ExportExcelFile\DC_TeleSale_Agent_Branch_Region.xlsx"));
var excelPkg = new ExcelPackage(fileInfo);
ExcelWorksheet dataSheet = excelPkg.Workbook.Worksheets["Agent Master"];
ExcelWorksheet dataSheet3 = excelPkg.Workbook.Worksheets["ListUser"];
var listdata3 = dbConn.Select <EmployeeInfo>();
int rowData3 = 1;
foreach (var data in listdata3)
{
rowData3++;
dataSheet3.Cells[rowData3, 1].Value = data.UserName;
}
ExcelWorksheet dataSheet4 = excelPkg.Workbook.Worksheets["listBranch"];
var listdata4 = dbConn.Select <DC_Location_Region>();
int rowData4 = 1;
foreach (var data in listdata4)
{
rowData4++;
dataSheet4.Cells[rowData4, 1].Value = data.RegionName;
}
int rowData = 1;
foreach (DC_TeleSale_Agent_Region data in datas)
{
int i = 1;
rowData++;
dataSheet.Cells[rowData, i++].Value = data.UserName;
dataSheet.Cells[rowData, i++].Value = data.BranchName;
dataSheet.Cells[rowData, i++].Value = data.RegionName;
dataSheet.Cells[rowData, i++].Value = data.RowCreatedTime;
dataSheet.Cells[rowData, i++].Value = data.RowCreatedUser;
if (data.RowLastUpdatedTime.ToString("dd/MM/yyyy") != "01/01/1900")
{
dataSheet.Cells[rowData, i++].Value = data.RowLastUpdatedTime;
}
else
{
dataSheet.Cells[rowData, i++].Value = "";
}
dataSheet.Cells[rowData, i++].Value = data.RowLastUpdatedUser;
}
//Write the workbook to a memory stream
MemoryStream output = new MemoryStream();
excelPkg.SaveAs(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_TeleSale_Agent_Branch_Region_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx")); //Suggested file name in the "Save as" dialog which will be displayed to the end user
}
}
else
{
return(File("", //The binary data of the XLS file
"application/vnd.ms-excel", //MIME type of Excel files
"DC_TeleSale_Agent_Branch_Region_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx")); //Suggested file name in the "Save as" dialog which will be displayed to the end user
}
}
