public Message Set_LOAN_CL(AST_LOAN_CL_TMP loan_wo, AppSession session)
{
try
{
if (Connection.State != ConnectionState.Open)
{
Connection.Open();
}
var response = fileProcessRepository.Set_LOAN_CL(loan_wo, session.User.user_id);
if (response.pvc_status == "40999")
{
MessageHelper.Success(Message, "Data process successfully....");
}
else
{
MessageHelper.Error(Message, response.pvc_statusmsg);
}
}
catch (Exception ex)
{
Logging.WriteToErrLog(session.User.StationIp, session.User.user_id, "FileProcessManager-Set_LOAN_CL", ex.Message + "|" + ex.StackTrace.TrimStart());
MessageHelper.Error(Message, ex.Message);
}
finally
{
Connection.Close();
}
return(Message);
}
public IActionResult Index(string loan_number = null)
{
var loancl = new AST_LOAN_CL_TMP();
if (loan_number != null)
{
loancl = fileProcessManager.Getloancl(loan_number, "", "", Session).FirstOrDefault();
}
ViewBag.LoanCl = loancl;
return(View());
}
public ResponseMessage Set_LOAN_CL(AST_LOAN_CL_TMP loan_wo, string pvc_appuser)
{
var responseMessage = new ResponseMessage();
var dyParam = new OracleDynamicParameters();
dyParam.Add("pvc_loan_number", loan_wo.LOAN_AC_NUMBER, OracleMappingType.Varchar2, ParameterDirection.Input, 20);
dyParam.Add("pvc_cl_status", loan_wo.CL_STATUS, OracleMappingType.Varchar2, ParameterDirection.Input, 20);
dyParam.Add("pvc_effdate", loan_wo.EFF_DATE.ToString("dd/MM/yyyy", CultureInfo.InvariantCulture), OracleMappingType.Varchar2, ParameterDirection.Input, 20);
dyParam.Add("pvc_appuser", pvc_appuser, OracleMappingType.Varchar2, ParameterDirection.Input, 50);
dyParam.Add("pvc_status", 0, OracleMappingType.Varchar2, ParameterDirection.Output, 5);
dyParam.Add("pvc_statusmsg", 0, OracleMappingType.Varchar2, ParameterDirection.Output, 255);
var res = responseMessage.QueryExecute(Connection, "pkg_asset_manager.dpd_set_loan_cl", dyParam);
return(res);
}
public IActionResult SubmitLoanCl(AST_LOAN_CL_TMP wo)
{
var message = fileProcessManager.Set_LOAN_CL(wo, Session);
return(Json(message));
}
private Message Process_LOAN_CL(string filepath, AppSession session, string tableName, int businessYear)
{
try
{
if (Connection.State != ConnectionState.Open)
{
Connection.Open();
}
fileProcessRepository.DeleteTable(tableName, session.User.user_id);
var random = new Random();
var fileProcessID = random.Next(10000);
using (var package = new ExcelPackage(new FileInfo(@filepath)))
{
var totalWorkSheet = package.Workbook.Worksheets.Count;
var isProcess = true;
for (var index = 1; index <= totalWorkSheet; index++)
{
var wooksheet = package.Workbook.Worksheets[index];
var xx = wooksheet.Name;
int rowCount = wooksheet.Dimension.Rows;
int columnCount = wooksheet.Dimension.Columns;
for (var i = 1; i <= 1; i++)
{
for (var j = 1; j <= columnCount; j++)
{
if (wooksheet.Cells[i, j].Text.Trim() == null && !ExcelColumn.LOAN_CL.Contains(wooksheet.Cells[i, j].Text.Trim()))
{
isProcess = false;
}
}
}
if (isProcess)
{
var portFolios = new List <AST_LOAN_CL_TMP>();
for (var i = 2; i <= rowCount; i++)
{
var portFolio = new AST_LOAN_CL_TMP
{
File_Process_ID = fileProcessID,
AREA_CODE = wooksheet.Cells[i, 1].Text.Trim(),
AREA_NAME = wooksheet.Cells[i, 2].Text.Trim(),
BRANCH_CODE = wooksheet.Cells[i, 3].Text.Trim(),
BRANCH_NAME = wooksheet.Cells[i, 4].Text.Trim(),
RM_CODE = wooksheet.Cells[i, 5].Text.Trim(),
RM_NAME = wooksheet.Cells[i, 6].Text.Trim(),
BST_CODE = wooksheet.Cells[i, 7].Text.Trim(),
BST_NAME = wooksheet.Cells[i, 8].Text.Trim(),
LOAN_AC_NUMBER = wooksheet.Cells[i, 9].Text.Trim(),
CL_STATUS = valid(wooksheet.Cells[i, 10].Text.Trim(), "CL_STATUS", "Digit"),
EFF_DATE = Convert.ToDateTime(wooksheet.Cells[i, 11].Text.Trim()),
INS_BY = session.User.user_id,
INS_DATE = DateTime.Now
};
portFolios.Add(portFolio);
}
if (portFolios.Count > 0)
{
var row = fileProcessRepository.Process_LOAN_CL(portFolios);
if (row > 0)
{
var response = fileProcessRepository.SetProcess_LOAN_CL(fileProcessID, businessYear, session.User.user_id);
if (response.pvc_status == "40999")
{
MessageHelper.Success(Message, "Data upload and process successfully....");
}
else
{
MessageHelper.Error(Message, response.pvc_statusmsg);
}
}
else
{
MessageHelper.Error(Message, "No data process...");
}
}
else
{
MessageHelper.Error(Message, "No rows found this excel file.");
}
}
else
{
MessageHelper.Error(Message, "File structure is not valid. Invalid Columns.");
}
}
}
}
catch (Exception ex)
{
Logging.WriteToErrLog(session.User.StationIp, session.User.user_id, "FileProcessManager-Process_LOAN_CL", ex.Message + "|" + ex.StackTrace.TrimStart());
MessageHelper.Error(Message, ex.Message);
}
finally
{
Connection.Close();
}
return(Message);
}