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