public ActionResult TelesalesAgent_Save([DataSourceRequest]
DataSourceRequest request, [Bind(Prefix = "models")]
IEnumerable <EmployeeInfo> us)
{
using (var dbConn = OrmliteConnection.openConn())
{
int count = 0;
foreach (var item in us)
{
DC_Telesales_Agent ta = dbConn.FirstOrDefault <DC_Telesales_Agent>("UserName={0}", item.UserName);
try
{
if (ta == null)
{
ta = new DC_Telesales_Agent();
ta.XLiteID = item.XLiteID == null ? "" : item.XLiteID;
ta.UserName = item.UserName == null ? "" : item.UserName;
ta.Region = item.Region == null ? "" : item.Region;
ta.Team = item.Team == null ? "" : item.Team;
ta.CreatedAt = DateTime.Now;
ta.CreatedBy = currentUser.UserName;
dbConn.Insert(ta);
}
else
{
ta.XLiteID = item.XLiteID == null ? "" : item.XLiteID;
ta.Region = item.Region == null ? "" : item.Region;
ta.Team = item.Team == null ? "" : item.Team;
ta.CreatedAt = DateTime.Now;
ta.CreatedBy = currentUser.UserName;
dbConn.Update(ta);
}
}
catch
{
ModelState.AddModelError("", "Can not update this data");
}
count++;
}
if (count > 0)
{
return(Json("success"));
}
else
{
ModelState.AddModelError("", "Can not update this data");
return(Json(us.ToDataSourceResult(request, ModelState)));
}
}
}
public ActionResult TelesalesAgent_Read([DataSourceRequest] DataSourceRequest request)
{
if (asset.View)
{
List <DC_Telesales_Agent> data = new List <DC_Telesales_Agent>();
data = DC_Telesales_Agent.GetDC_Telesales_Agent();
return(Json(data.ToDataSourceResult(request)));
}
else
{
return(RedirectToAction("NoAccessRights", "Error"));
}
}
public ActionResult ImportFromExcel()
{
try
{
if (ModelState.IsValid)
{
using (var dbConn = OrmliteConnection.openConn())
{
List <DC_Telesales_Agent_Meta> listData = new List <DC_Telesales_Agent_Meta>();
string filename = @"DC_TelesaleAgent Master Error_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xls";
int total = 0;
var listrow = new List <NPOI.SS.UserModel.IRow>();
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];
int t = 0;
//excel data saves in temp file here.
foreach (DataRow row in dt.Rows)
{
excelSheets[t] = row["TABLE_NAME"].ToString();
t++;
}
OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);
DataSet ds = new DataSet();
string query = string.Format("Select * from [{0}]", excelSheets[0]);
using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
{
dataAdapter.Fill(ds);
}
//khoi tao save file error
int rowNumber = 1;
string currentUserName = currentUser.UserName;
FileStream fs = new FileStream(Server.MapPath(@"~\ExportExcelFile\DC_TelesaleAgent.xls"), FileMode.Open, FileAccess.Read);
string ErrorfileLocation = string.Format("{0}/{1}", Server.MapPath("~/Excel"), filename);
var workbook = new HSSFWorkbook(fs, true);
FileStream fileStream = new FileStream(ErrorfileLocation, FileMode.Create, FileAccess.Write);
var sheet = workbook.GetSheet("TelesaleAgent");
//for (int k = 0; k <= ds.Tables[0].Rows.Count; k++)
//{
// var row = sheet.GetRow(k);
// listrow.Add(row);
// sheet.RemoveRow(row);
//}
//chay vong lap qua row
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string importnote = "";
string UserName = ds.Tables[0].Rows[i]["UserName"].ToString();
string Team = ds.Tables[0].Rows[i]["Team"].ToString();
string Region = ds.Tables[0].Rows[i]["Region"].ToString();
string XLiteID = ds.Tables[0].Rows[i]["XLiteID"].ToString();
try
{
if (string.IsNullOrWhiteSpace(UserName))
{
importnote += "Null error in required field(s)! ";
}
if (importnote != "")
{
throw new Exception(importnote);
}
EmployeeInfo us = new Models.EmployeeInfo();
us.UserName = UserName;
//var requestID = DC_Customer_lvCredit_Request.GetDC_Customer_lvCredit_Requests("[OrganizationID]='" + request.OrganizationID + "' AND [CustomerID] = '" + request.CustomerID + "'", "").FirstOrDefault();
DC_Telesales_Agent ta = dbConn.FirstOrDefault <DC_Telesales_Agent>("UserName={0}", UserName);
try
{
if (ta == null)
{
importnote += "Không tìm thấy agent này trong hệ thống!";
throw new Exception(importnote);
}
else
{
ta.XLiteID = XLiteID;
ta.Region = Region;
ta.Team = Team;
ta.CreatedAt = DateTime.Now;
ta.CreatedBy = currentUser.UserName;
dbConn.Update(ta);
}
}
catch
{
importnote += "Không tìm thấy agent này trong hệ thống!";
throw new Exception(importnote);
}
}
catch (Exception e)
{
DC_Telesales_Agent_Meta org = new DC_Telesales_Agent_Meta();
org.AgentID = UserName;
org.Team = Team;
org.ImportNote = e.Message;
listData.Add(org);
//Create a new row
var row = sheet.CreateRow(rowNumber++);
//Set values for the cells
row.CreateCell(0).SetCellValue(UserName);
row.CreateCell(1).SetCellValue(Team);
row.CreateCell(2).SetCellValue(Region);
row.CreateCell(2).SetCellValue(XLiteID);
row.CreateCell(4).SetCellValue(importnote);
//Write the workbook to a memory stream
importnote = "";
continue;
}
}
workbook.Write(fileStream);
fileStream.Close();
excelConnection.Close();
excelConnection1.Close();
}
else
{
ModelState.AddModelError("", "Plese select Excel File.");
}
}
return(Json(new { success = true, data = listData, total = total, link = filename }));
}
}
else
{
return(Json(new { success = false }));
}
}
catch (Exception ex)
{
return(Json(new { success = false }));
}
}
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
}
}
