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 List <Dictionary <string, object> > GetStoreData(ExcelXmlModel model)
{
List <Dictionary <string, object> > output = new List <Dictionary <string, object> >();
try
{
string storeName = "shop_name";
string proceName = null;
//get store name to choose store procedure
List <ExcelXMLColumn> lstColumn = new List <ExcelXMLColumn>();
lstColumn = model.LstColumn;
foreach (var column in lstColumn)
{
if (column.Name.Contains(storeName))
{
proceName = "pws_getmetadata_excel"; break;
}
else
{
proceName = "pws_getdata_excel";
}
}
output = GetStoreData(model, "report_name_para", "p_recordset", proceName);
}
catch (Exception ex)
{
}
return(output);
}
public MemoryStream ExportExcelWithConfigReport(string id, string filePath, ExcelXmlModel model)
{
List <Dictionary <string, object> > data = GetExcelConfigData(model);
ExcelHelper excelHelper = new ExcelHelper();
string excelFileName = null;
MemoryStream memoryStream = excelHelper.ExportExcel(model, data, ref excelFileName);
//save file excel exported
var package = new ExcelPackage(memoryStream);
FileInfo Path = new FileInfo(filePath);
package.SaveAs(Path);
return(memoryStream);
}
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"));
}
/// <summary>
/// Get Store Name, Indicator
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public List <Dictionary <string, object> > GetExcelConfigData(ExcelXmlModel model)
{
//Dictionary<string, object> dicParas = new Dictionary<string, object>();
//Dictionary<string, object> dicParaOuts = new Dictionary<string, object>();
List <Dictionary <string, object> > lst_store = new List <Dictionary <string, object> >();
List <Dictionary <string, object> > lst_kpi = new List <Dictionary <string, object> >();
List <Dictionary <string, object> > ouput = new List <Dictionary <string, object> >();
try
{
string procedureName = null;
if (model.ExcelXmlCommon.exportDirectory.Equals("PL_CongTy"))
{
procedureName = "pws_getreporsetupcompany_excel";
}
else
{
procedureName = "pws_getreporsetupstore_excel";
}
this.ConnectOracleDB.command.Parameters.Clear();
var op = new OracleParameter("PK_SRPTNBR_para", OracleDbType.Varchar2);
op.Direction = ParameterDirection.Input;
op.Value = model.ExcelXmlCommon.ReportName;
this.ConnectOracleDB.command.Parameters.Add(op);
this.ConnectOracleDB.command.Parameters.Add("p_recordset", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
this.ConnectOracleDB.command.CommandText = procedureName;
this.ConnectOracleDB.command.CommandType = CommandType.StoredProcedure;
OracleDataReader objReader = this.ConnectOracleDB.command.ExecuteReader();
while (objReader.Read())
{
var columns = Enumerable.Range(0, objReader.FieldCount).Select(objReader.GetName).ToList();
Dictionary <string, object> dic = new Dictionary <string, object>();
for (int i = 0; i < columns.Count; i++)
{
foreach (ExcelXMLColumn xmlColumn in model.LstColumn)
{
if (!xmlColumn.IsGetData)
{
continue;
}
if (xmlColumn.Name != columns[i].ToLower())
{
continue;
}
if ((objReader[xmlColumn.Name] != null) && (xmlColumn.IsGetData))
{
dic.Add(xmlColumn.Name, objReader[xmlColumn.Name]);
}
}
}
ouput.Add(dic);
//objReader.NextResult();
}
objReader.Close();
objReader.Dispose();
}
catch (Exception ex)
{
}
finally
{
this.ConnectOracleDB.CloseDBConnect();
}
return(ouput);
}
public List <Dictionary <string, object> > GetStoreDataInventory(ExcelXmlModel model, int month, int year)
{
//Dictionary<string, object> dicParas = new Dictionary<string, object>();
//Dictionary<string, object> dicParaOuts = new Dictionary<string, object>();
List <Dictionary <string, object> > lst_store = new List <Dictionary <string, object> >();
List <Dictionary <string, object> > lst_kpi = new List <Dictionary <string, object> >();
List <Dictionary <string, object> > ouput = new List <Dictionary <string, object> >();
try
{
this.ConnectOracleDB.command.Parameters.Clear();
var time = month.ToString() + "-" + year.ToString().Substring(2, 2);
if (month < 10)
{
time = '0' + time;
}
var dateTime = new OracleParameter("dateTime", OracleDbType.Varchar2);
dateTime.Direction = ParameterDirection.Input;
dateTime.Value = time;
this.ConnectOracleDB.command.Parameters.Add(dateTime);
this.ConnectOracleDB.command.Parameters.Add("p_recordset", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
this.ConnectOracleDB.command.CommandText = "PWS_GETINVENTORYDATA_EXCEL";
this.ConnectOracleDB.command.CommandType = CommandType.StoredProcedure;
OracleDataReader objReader = this.ConnectOracleDB.command.ExecuteReader();
while (objReader.Read())
{
var columns = Enumerable.Range(0, objReader.FieldCount).Select(objReader.GetName).ToList();
Dictionary <string, object> dic = new Dictionary <string, object>();
for (int i = 0; i < columns.Count; i++)
{
foreach (ExcelXMLColumn xmlColumn in model.LstColumn)
{
if (!xmlColumn.IsGetData)
{
continue;
}
if (xmlColumn.Name.ToLower() != columns[i].ToLower())
{
continue;
}
if ((objReader[xmlColumn.Name] != null) && (xmlColumn.IsGetData))
{
dic.Add(xmlColumn.Name, objReader[xmlColumn.Name]);
}
}
}
ouput.Add(dic);
//objReader.NextResult();
}
objReader.Close();
objReader.Dispose();
}
catch (Exception ex)
{
}
finally
{
this.ConnectOracleDB.CloseDBConnect();
}
return(ouput);
}
