/// <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));
}
