private BaseResponse ReadExcelFile(ProcessFileViewModel viewModel)
{
var response = new BaseResponse();
try
{
int year = DateTime.Now.Year;
int month = DateTime.Now.Month;
var listData = new List <ConfigurationViewModel.Item>();
if (viewModel.Filename != Path.GetFullPath(viewModel.Filename))
{
viewModel.Filename = Server.MapPath(viewModel.Filename);
}
if (!System.IO.File.Exists(viewModel.Filename))
{
response.IsSuccess = false;
response.Message = "File Not Found";
return(response);
}
Workbook workbook = new Workbook();
using (FileStream stream = new FileStream(viewModel.Filename, FileMode.Open))
{
workbook.LoadDocument(stream, DevExpress.Spreadsheet.DocumentFormat.OpenXml);
#region foreach
foreach (var worksheet in workbook.Worksheets)
{
string[] name = worksheet.Name.Split('_');
PeriodeType pType;
if (name[0] == "Daily" || name[0] == "Monthly" || name[0] == "Yearly")
{
string periodeType = name[0];
pType = string.IsNullOrEmpty(periodeType) ? PeriodeType.Yearly : (PeriodeType)Enum.Parse(typeof(PeriodeType), periodeType);
string period = name[name.Count() - 1];
string[] periodes = null;
if (periodeType != period && !string.IsNullOrEmpty(period))
{
switch (periodeType)
{
case "Yearly":
break;
case "Monthly":
year = int.Parse(period);
break;
case "Daily":
periodes = period.Split('-');
year = int.Parse(periodes[0]);
month = int.Parse(periodes[periodes.Count() - 1]);
break;
}
}
workbook.Worksheets.ActiveWorksheet = worksheet;
Range range = worksheet.GetUsedRange();
int rows = range.RowCount;
int column = range.ColumnCount - 2;
int kpiId = 0;
var kpiListWithOperationConfigId = new List <int>();
var operationIds = new GetOperationsInResponse();
#region get kpi list with key operation config
if (viewModel.ConfigType.ToLowerInvariant().Equals("operationdata"))
{
for (int i = 1; i < rows; i++)
{
for (int j = 0; j < column; j++)
{
if (j == 0)
{
if (worksheet.Cells[i, j].Value.Type == CellValueType.Numeric)
{
kpiId = int.Parse(worksheet.Cells[i, j].Value.ToString());
kpiListWithOperationConfigId.Add(kpiId);
}
}
}
}
operationIds = _operationConfigService.GetOperationIn(new GetOperationsInRequest {
KpiIds = kpiListWithOperationConfigId
});
}
#endregion
for (int i = 1; i < rows; i++)
{
bool isAuthorizedKPI = false;
for (int j = 0; j < column; j++)
{
if (j == 0)
{
if (worksheet.Cells[i, j].Value.Type == CellValueType.Numeric)
{
kpiId = int.Parse(worksheet.Cells[i, j].Value.ToString());
//this will validate authorized KPI based on Role
isAuthorizedKPI = ValidateAuthorizeKPI(kpiId);
if (!isAuthorizedKPI)
{
break;
}
}
}
else if (j > 1)
{
if (worksheet.Cells[0, j].Value.Type == CellValueType.DateTime)
{
DateTime periodData = DateTime.Parse(worksheet.Cells[0, j].Value.ToString());
if (worksheet.Cells[i, j].Value.Type == CellValueType.Numeric ||
worksheet.Cells[i, j].Value.Type == CellValueType.Text)
{
string value = worksheet.Cells[i, j].Value.ToString();
int operationId = 0;
if (viewModel.ConfigType.ToLowerInvariant().Equals(ConfigType.OperationData.ToString().ToLowerInvariant()))
{
var operation = operationIds.KeyOperations.FirstOrDefault(x => x.KpiId == kpiId);
if (operation != null)
{
operationId = operation.Id;
}
}
if (!string.IsNullOrEmpty(value))
{
var data = new ConfigurationViewModel.Item
{
Value = value,
KpiId = kpiId,
Periode = periodData,
PeriodeType = pType,
OperationId = operationId,
ScenarioId = viewModel.ScenarioId
};
listData.Add(data);
}
}
}
}
}
if (!isAuthorizedKPI)
{
continue;
}
}
}
else
{
response.IsSuccess = false;
response.Message = "File Not Valid";
break;
}
switch (viewModel.ConfigType)
{
case "KpiTarget":
response = UpdateKpiTarget(listData);
break;
case "KpiAchievement":
response = UpdateKpiAchievement(listData);
break;
case "OperationData":
response = UpdateOperationData(listData);
break;
default:
response.IsSuccess = false;
response.Message = string.Format(@"config type for {0} is not existed",
viewModel.ConfigType);
break;
}
}
#endregion
}
}
catch (Exception exception)
{
response.IsSuccess = false;
response.Message = exception.Message;
}
return(response);
}
private BaseResponse _ReadExcelFile(string filename, int scenarioId, string configType)
{
var response = new BaseResponse();
string periodType = string.Empty;
PeriodeType pType = PeriodeType.Yearly;
int tahun = DateTime.Now.Year, bulan = DateTime.Now.Month;
List <OperationDataConfigurationViewModel.Item> list_data = new List <OperationDataConfigurationViewModel.Item>();
if (filename != Path.GetFullPath(filename))
{
filename = Server.MapPath(filename);
}
/*
* cek file exist and return immediatelly if not exist
*/
if (!System.IO.File.Exists(filename))
{
response.IsSuccess = false;
response.Message = "File Not Found";
return(response);
}
Workbook workbook = new Workbook();
using (FileStream stream = new FileStream(filename, FileMode.Open))
{
workbook.LoadDocument(stream, DevExpress.Spreadsheet.DocumentFormat.OpenXml);
#region foreach
foreach (var worksheet in workbook.Worksheets)
{
string[] name = worksheet.Name.Split('_');
if (name[0] == "Daily" || name[0] == "Monthly" || name[0] == "Yearly")
{
periodType = name[0];
pType = string.IsNullOrEmpty(periodType)
? PeriodeType.Yearly
: (PeriodeType)Enum.Parse(typeof(PeriodeType), periodType);
string period = name[name.Count() - 1];
string[] periodes = null;
//validate and switch value by periodType
if (periodType != period && !string.IsNullOrEmpty(period))
{
switch (periodType)
{
case "Daily":
periodes = period.Split('-');
tahun = int.Parse(periodes[0]);
bulan = int.Parse(periodes[periodes.Count() - 1]);
break;
case "Monthly":
tahun = int.Parse(period);
break;
case "Yearly":
default:
break;
}
}
workbook.Worksheets.ActiveWorksheet = worksheet;
//get row
Range range = worksheet.GetUsedRange();
int rows = range.RowCount;
int column = range.ColumnCount - 2;
int Kpi_Id = 0;
DateTime periodData = new DateTime();
double? nilai = null;
List <int> list_Kpi = new List <int>();
for (int i = 1; i < rows; i++)
{
for (int j = 0; j < column; j++)
{
if (j == 0)
{
if (worksheet.Cells[i, j].Value.Type == CellValueType.Numeric)
{
int Kpis_Id = int.Parse(worksheet.Cells[i, j].Value.ToString());
list_Kpi.Add(Kpis_Id);
}
}
}
}
var operationsId = _operationConfigService.GetOperationIn(new GetOperationsInRequest {
KpiIds = list_Kpi
});
//get rows
for (int i = 1; i < rows; i++)
{
for (int j = 0; j < column; j++)
{
bool fromExistedToNull = false;
if (j == 0)
{
if (worksheet.Cells[i, j].Value.Type == CellValueType.Numeric)
{
Kpi_Id = int.Parse(worksheet.Cells[i, j].Value.ToString());
}
}
else if (j > 1)
{
var operationId = 0;
var operation = operationsId.KeyOperations.FirstOrDefault(x => x.KpiId == Kpi_Id);
if (operation != null)
{
operationId = operation.Id;
}
if (worksheet.Cells[0, j].Value.Type == CellValueType.DateTime)
{
periodData = DateTime.Parse(worksheet.Cells[0, j].Value.ToString());
}
if (worksheet.Cells[i, j].Value.Type == CellValueType.Numeric)
{
nilai = double.Parse(worksheet.Cells[i, j].Value.ToString());
}
else if (worksheet.Cells[i, j].Value.Type == CellValueType.Text)
{
fromExistedToNull = true;
nilai = null;
}
else
{
nilai = null;
}
if (nilai != null || fromExistedToNull)
{
// try to cacth and update
var data = new OperationDataConfigurationViewModel.Item()
{
Value = nilai, KpiId = Kpi_Id, Periode = periodData, PeriodeType = pType, ScenarioId = scenarioId, OperationId = operationId
};
list_data.Add(data);
//switch (configType)
//{
// case "KpiTarget":
// response = this._UpdateKpiTarget(data);
// break;
// case "KpiAchievement":
// response = this._UpdateKpiAchievement(data);
// break;
// case "Economic":
// response = this._UpdateEconomic(data);
// break;
// default:
// response.IsSuccess = false;
// response.Message = "No Table Selected";
// break;
//}
}
}
}
}
}
else
{
response.IsSuccess = false;
response.Message = "File Not Valid";
break;
}
switch (configType)
{
//case "KpiTarget":
// response = this._UpdateKpiTarget(list_data);
// break;
//case "KpiAchievement":
// response = this._UpdateKpiAchievement(list_data, pType.ToString(), tahun, bulan);
// break;
case "Economic":
response = this._UpdateEconomic(list_data);
break;
default:
response.IsSuccess = false;
response.Message = "No Table Selected";
break;
}
}
#endregion
}
//here to read excel fileController
return(response);
}
