/// <summary> /// Use with the xml file contains paras elements /// </summary> /// <param name="mode"></param> /// <returns></returns> public ActionResult ImportExcel(FileUploadModel model) { //Test run time upload excel //DateTime timeFinish = DateTime.UtcNow; //DateTime timeStart = DateTime.UtcNow; //TimeSpan timeSpan = new TimeSpan(); //timeStart = DateTime.Parse(DateTime.Now.ToString("h:mm:ss tt")); this.GetLanguage(); try { //--------------------GET XML CONFIG ----------------------------------------- this.SetConnectionDB(); EVNImporterServices services = new EVNImporterServices(oracleConnection, DBConnection); List <string> lstCellName = new List <string>(); string xmlFilePath = this.CONFIG_FOLDER + "\\excel_format_" + model.PermissionId.ToString() + ".xml"; ExcelModel lstColumns = services.GetXMLConfig(xmlFilePath); //************************** UPLOAD FILE TO THE UPLOAD FOLDER*********************************************** string excelfilename = BI_Project.Helpers.Utility.APIStringHelper.GenerateId() + Path.GetExtension(model.FileObj.FileName); //string excelFilePath = System.Web.Hosting.HostingEnvironment.MapPath(this.EXCEL_UPLOAD_FOLDER) + lstColumns.FolderUploadedDirectory + "/" + excelfilename; string excelFilePath = System.Web.Hosting.HostingEnvironment.MapPath(this.EXCEL_UPLOAD_FOLDER) + lstColumns.FolderUploadedDirectory + "\\" + excelfilename; model.FileObj.SaveAs(excelFilePath); //************************ INSERT DATA TO DATABASE ******************** int userid = (int)Session[this.SESSION_NAME_USERID]; int uploadRoleId = model.PermissionId; services.ImportPLDatabase(userid, excelFilePath, uploadRoleId, lstColumns, excelfilename); //services.Import2Database(userid, excelFilePath) if (services.ERROR != null) { throw new Exception(services.ERROR); } Session["msg_text"] = BlockLanguageModel.GetElementLang(this.LANGUAGE_OBJECT, "messages.block_upload_excel.success"); Session["msg_code"] = 1; //timeFinish = DateTime.Parse(DateTime.Now.ToString("h:mm:ss tt")); //timeSpan = timeFinish.Subtract(timeStart); //FileHelper.SaveFile(timeSpan, this.LOG_FOLDER + "/TIMESPAN" + this.GetType().ToString() + APIStringHelper.GenerateFileId() + ".txt"); } catch (Exception ex) { //FileHelper.SaveFile(new { ERROR = ex }, this.LOG_FOLDER + "/ERROR_" + this.GetType().ToString() + APIStringHelper.GenerateFileId() + ".txt"); ERRORS = ex.Message; Session["msg_text"] = BlockLanguageModel.GetElementLang(this.LANGUAGE_OBJECT, "messages.block_upload_excel.error") + " Lỗi " + ex.Message; Session["msg_code"] = -1; } return(RedirectToAction("Index/" + model.PermissionId)); }
public ActionResult Delete() { if (Session["IsAdmin"] is false) { return(RedirectToAction("Logout", "Home")); } string id = this.HttpContext.Request["treeId"]; string fileName = this.HttpContext.Request["fileUpload"]; Logging.WriteToLog(this.GetType().ToString() + "-delete(), id=" + id + ", file name = " + fileName, LogType.Access); int output = 0; try { this.SetConnectionDB(); EVNImporterServices services = new EVNImporterServices(oracleConnection, DBConnection); int userid = (int)Session[this.SESSION_NAME_USERID]; output = services.Delete(fileName, userid); if (services.ERROR != null) { throw new Exception(services.ERROR); } } catch (Exception ex) { this.ERRORS = ex.ToString(); FileHelper.SaveFile(ex, this.LOG_FOLDER + "/ERROR_" + this.GetType().ToString() + APIStringHelper.GenerateFileId() + ".txt"); output = -2; } ////*************************************XU LY VAN DE THONG BAO THANH CONG HAY THAT BAI******************** this.GetLanguage(); return(RedirectToAction("Index/" + id)); }
public ActionResult GetTableDescriptionById(string id) { this.SetConnectionDB(); EVNImporterServices services = new EVNImporterServices(oracleConnection, DBConnection); //var dnts = Product.GetSampleProducts().Where(x => x.Id == id); ; var dnts = services.GetMotaDNTs(id); if (dnts != null) { List <MoTaDNTModel> model = new List <MoTaDNTModel>(); foreach (var item in dnts) { MoTaDNTModel dnt = new MoTaDNTModel(); dnt.Col_name = item.Col_name; dnt.Data_type = item.Data_type; dnt.Description = item.Description; model.Add(dnt); } return(PartialView("_GridTableDescriptionPartial", model)); } return(View()); }
public ActionResult ExportExcelInventory(FileUploadModel blockData) { int month = blockData.currentMonth; int year = blockData.currentyear; string id = blockData.PermissionId.ToString(); string xmlFilePath = this.CONFIG_FOLDER + "\\excel_export_format.xml"; BI_Project.Helpers.ExcelHelper excelHelper = new ExcelHelper(); string excelFileName = ""; ExcelXmlModel model = excelHelper.GetUploadExcelXMLConfig(xmlFilePath, id); this.SetConnectionDB(); EVNImporterServices services = new EVNImporterServices(oracleConnection, DBConnection); this.oracleConnection.OpenDBConnect(); List <Dictionary <string, object> > data = services.GetStoreDataInventory(model, month, year); MemoryStream memoryStream = excelHelper.ExportExcel(model, data, ref excelFileName); this.oracleConnection.CloseDBConnect(); return(File(memoryStream.ToArray(), "application/vnd.ms-excel", excelFileName + ".xlsx")); }
public ActionResult Import(FileUploadModel model) { this.GetLanguage(); //DECLARE PAGE MODEL try { //************************** CHECK PERMISSION ************************** this.SetConnectionDB(); //var test = model.FileObj; //************************** GET CONFIG EVNImporterServices services = new EVNImporterServices(oracleConnection, DBConnection); string tablename = "", sheetActive = "", uploadFolder = "", fileNativeName = ""; string note = "", helpDocumentPath = ""; int startRow = 0, numberRow = 0, numberCell = 0; List <string> lstCellName = new List <string>(); string xmlFilePath = this.CONFIG_FOLDER + "\\excel_format_" + model.PermissionId.ToString() + ".xml"; List <MappingExcelDB> lstColumns = services.GetColumnList(xmlFilePath, ref uploadFolder, ref tablename, ref startRow, ref sheetActive, ref helpDocumentPath, ref note, ref fileNativeName); //, ref numberRow, ref numberCell, ref lstCellName //************************** UPLOAD FILE TO THE UPLOAD FOLDER*********************************************** string excelfilename = BI_Project.Helpers.Utility.APIStringHelper.GenerateId(model.currentyear, model.currentyear) + Path.GetExtension(model.FileObj.FileName); string excelFilePath = System.Web.Hosting.HostingEnvironment.MapPath(this.EXCEL_UPLOAD_FOLDER) + uploadFolder + "/" + excelfilename; model.FileObj.SaveAs(excelFilePath); //************************ INSERT DATA TO DATABASE ******************** int userid = (int)Session[this.SESSION_NAME_USERID]; int uploadRoleId = model.PermissionId; int currentYear = model.currentyear; //services.Import2Database(userid,excelFilePath, tablename, startRow, sheetActive,helpDocumentPath, // note,fileNativeName,fileNativeName,uploadRoleId, lstColumns,excelfilename); services.ImportToDatabase(currentYear, userid, excelFilePath, tablename, startRow, sheetActive, helpDocumentPath, note, fileNativeName, fileNativeName, uploadRoleId, lstColumns, excelfilename); //, numberRow, numberCell, lstCellName if (services.ERROR != null) { throw new Exception(services.ERROR); } Session["msg_text"] = BlockLanguageModel.GetElementLang(this.LANGUAGE_OBJECT, "messages.block_upload_excel.success"); Session["msg_code"] = 1; } catch (Exception ex) { FileHelper.SaveFile(new { ERROR = ex }, this.LOG_FOLDER + "/ERROR_" + this.GetType().ToString() + APIStringHelper.GenerateFileId() + ".txt"); Session["msg_text"] = BlockLanguageModel.GetElementLang(this.LANGUAGE_OBJECT, "messages.block_upload_excel.error"); Session["msg_code"] = -1; } return(RedirectToAction("Index/" + model.PermissionId)); }
public ActionResult UploadTableData(BlockDataRowUploadModel model) { if (null == Session[this.SESSION_NAME_USERID]) { return(RedirectToAction("Login", "Home")); } if (Session["IsAdmin"] == null || (bool)Session["IsAdmin"] == false) { return(RedirectToAction("Logout", "Home")); } this.GetLanguage(); try { int output = 0; this.SetConnectionDB(); EVNImporterServices services = new EVNImporterServices(oracleConnection, DBConnection); output = services.ExecuteDataTable(model.Month, model.Year, model.File, model.DataString); if (output == 0) { Session["msg_text"] = BlockLanguageModel.GetElementLang(this.LANGUAGE_OBJECT, "messages.block_upload_excel.success"); Session["msg_code"] = 1; } else { if (services.ERROR != null) { throw new Exception(services.ERROR); } //Session["msg_text"] = BlockLanguageModel.GetElementLang(this.LANGUAGE_OBJECT, "messages.block_upload_excel.error") + " Lỗi " + services.ERROR.ToString(); Session["msg_text"] = BlockLanguageModel.GetElementLang(this.LANGUAGE_OBJECT, "messages.block_upload_excel.error") + " Lỗi dữ liệu nhập vào."; Session["msg_code"] = -1; } } catch (Exception ex) { Session["msg_text"] = BlockLanguageModel.GetElementLang(this.LANGUAGE_OBJECT, "messages.block_upload_excel.error") + " Lỗi dữ liệu nhập vào. "; Session["msg_code"] = -1; } if (model.Type == 1) { var jsonData = new { rows = JsonConvert.DeserializeObject <List <object> >(model.DataString) }; return(Json(jsonData, JsonRequestBehavior.AllowGet)); } return(RedirectToAction("AddDNTData")); }
public ActionResult Index(string id, BlockDataExcelUploadModel uiModel) { int noPages = 0, noRecords = 0; try { uiModel.CurrentPage = Int32.Parse(Request.Params["page"]); } catch (Exception) { uiModel.CurrentPage = 1; } this.CheckPermission(); ViewData["pagename"] = "upload_excel"; ViewData["action_block"] = "Excels/EVN_block_upload_excel"; SetCommonData(); GetLanguage(); //DECLARE PAGE MODEL BI_Project.Models.UI.PageModel pageModel = new Models.UI.PageModel("upload_excel"); pageModel.SetLanguage(this.LANGUAGE_OBJECT); pageModel.H1Title = pageModel.GetElementByPath("page_excel.menu" + id + ".h1"); pageModel.Title = pageModel.GetElementByPath("page_excel.menu" + id + ".title"); ViewData["page_model"] = pageModel; BlockLangExcelUpload blockLang = new BlockLangExcelUpload(); BI_Project.Models.UI.BlockModel blockModel = new Models.UI.BlockModel("block_upload_excel", this.LANGUAGE_OBJECT, blockLang); //BlockDataExcelUploadModel blockData = new BlockDataExcelUploadModel(); uiModel.PermissionID = id; EVNImporterServices services = new EVNImporterServices(oracleConnection, DBConnection); string xmlConfigFilePath = this.CONFIG_FOLDER + "\\excel_format_" + uiModel.PermissionID.ToString() + ".xml"; uiModel.HelpDoc = BI_Project.Helpers.Utility.JTokenHelper.GetElementValue(xmlConfigFilePath, "excel_source.HelpDocumentPath"); uiModel.ListHistory = services.GetHistoryList(uiModel.CurrentPage, uiModel.PerPage, id, ref noPages, ref noRecords, uiModel.Month, uiModel.Year); uiModel.NumberPages = noPages; uiModel.NumberRecords = noPages; uiModel.CurrentPage = uiModel.CurrentPage; uiModel.FolderUpload = pageModel.GetElementByPath("page_excel.menu" + id + ".UploadedDirectory"); blockModel.DataModel = uiModel; //blockModel.DataModel = ViewData["block_menu_left_data"]; if (services.ERROR != null) { FileHelper.SaveFile(services.ERROR, this.LOG_FOLDER + "/ERROR_" + this.GetType().ToString() + APIStringHelper.GenerateFileId() + ".txt"); } ViewData["BlockData"] = blockModel; return(View("~/" + this.THEME_FOLDER + "/" + this.THEME_ACTIVE + "/index.cshtml")); }
public async Task <FileResult> ExportPLExcel(FileUploadModel uploadModel) { var memory = new MemoryStream(); string xmlFilePath = this.CONFIG_FOLDER + "\\excel_export_format.xml"; string id = uploadModel.PermissionId.ToString(); ExcelHelper excelHelper = new ExcelHelper(); ExcelXmlModel model = excelHelper.GetUploadExcelXMLConfig(xmlFilePath, id); try { /**************************************EXPORT EXCEL OFFLINE***************************************/ if (Convert.ToBoolean(uploadModel.ExportOff) == true) { //Get last time write file string path = System.Web.Hosting.HostingEnvironment.MapPath(this.EXCEL_EXPORT_FOLDER) + model.ExcelXmlCommon.exportDirectory; string fileName = excelHelper.GetNewFile(path); if (fileName == null) { throw new Exception(); } string filePath = path + "\\" + fileName; using (var stream = new FileStream(filePath, FileMode.Open)) { await stream.CopyToAsync(memory); } memory.Position = 0; } /**************************************EXPORT EXCEL ONLINE***************************************/ else { this.SetConnectionDB(); EVNImporterServices services = new EVNImporterServices(this.oracleConnection, this.DBConnection); this.oracleConnection.OpenDBConnect(); string filePath = System.Web.Hosting.HostingEnvironment.MapPath(this.EXCEL_EXPORT_FOLDER) + model.ExcelXmlCommon.exportDirectory + "//" + model.ExcelXmlCommon.ExcelFileName + "_" + APIStringHelper.GenerateId() + ".xlsx"; memory = services.ExportExcelWithConfigReport(id, filePath, model); this.oracleConnection.CloseDBConnect(); } } catch (Exception ex) { this.ERRORS = ex.ToString(); } return(File(memory.ToArray(), "application/vnd.ms-excel", model.ExcelXmlCommon.ExcelFileName + ".xlsx")); }
public ActionResult ExportExcelWithConfigReport(string id) { string xmlFilePath = this.CONFIG_FOLDER + "\\excel_export_format.xml"; BI_Project.Helpers.ExcelHelper excelHelper = new ExcelHelper(); string excelFileName = ""; ExcelXmlModel model = excelHelper.GetUploadExcelXMLConfig(xmlFilePath, id); this.SetConnectionDB(); EVNImporterServices services = new EVNImporterServices(oracleConnection, DBConnection); this.oracleConnection.OpenDBConnect(); List <Dictionary <string, object> > data = services.GetExcelConfigData(model); MemoryStream memoryStream = excelHelper.ExportExcel(model, data, ref excelFileName); this.oracleConnection.CloseDBConnect(); return(File(memoryStream.ToArray(), "application/vnd.ms-excel", excelFileName + ".xlsx")); }
public JsonResult GetDataRows(int year, int month, int file) { //string firstname = Request.QueryString["month"]; //string lastname = Request.QueryString["year"]; //string lastname1 = Request.QueryString["file"]; //var products = Product.GetSampleProducts(); //int pageIndex = Convert.ToInt32(page) - 1; //int pageSize = rows; //int totalRecords = products.Count(); //int totalPages = (int)Math.Ceiling((float)totalRecords / (float)pageSize); //int uploadYear = year; //int uploadMonth = month; //int uploadFile = file; //var data = products.OrderBy(x => x.Id) // .Skip(pageSize * (page - 1)) // .Take(pageSize).ToList(); this.SetConnectionDB(); EVNImporterServices services = new EVNImporterServices(oracleConnection, DBConnection); DVKH dataTable = new DVKH(); var obj = (dynamic)null; switch (file) { case 1: dataTable.DNT_QMKLTN_HA1820 = services.GetList_DNT_QMKLTN_HA1820(month, year); obj = dataTable.DNT_QMKLTN_HA1820.Count > 0 ? dataTable.DNT_QMKLTN_HA1820 : null; break; case 2: dataTable.DNT_QMKLTN_NVK = services.GetList_DNT_QMKLTN_NVK(month, year); obj = dataTable.DNT_QMKLTN_NVK.Count > 0 ? dataTable.DNT_QMKLTN_NVK : null; break; case 3: dataTable.DNT_QMKL_QD41 = services.GetList_DNT_QMKL_QD41(month, year); obj = dataTable.DNT_QMKL_QD41.Count > 0 ? dataTable.DNT_QMKL_QD41 : null; break; case 4: dataTable.DNT_QMKLTN_QD2081 = services.GetList_DNT_QMKLTN_QD2081(month, year); obj = dataTable.DNT_QMKLTN_QD2081.Count > 0 ? dataTable.DNT_QMKLTN_QD2081 : null; break; case 5: dataTable.DNT_THCDIEN_PL71 = services.GetList_DNT_THCDIEN_PL71(month, year); obj = dataTable.DNT_THCDIEN_PL71.Count > 0 ? dataTable.DNT_THCDIEN_PL71 : null; break; case 6: dataTable.DNT_THCDIEN_PL72 = services.GetList_DNT_THCDIEN_PL72(month, year); obj = dataTable.DNT_THCDIEN_PL72.Count > 0 ? dataTable.DNT_THCDIEN_PL72 : null; break; default: break; } // var data2 = services.GetList_DNT_QMKLTN_HA1820(); //var json = new JavaScriptSerializer().Serialize(obj); var jsonData = new { total = 20, page = 1, records = 12, rows = obj }; return(Json(jsonData, JsonRequestBehavior.AllowGet)); }