private ExcelFileProcess ProcessExpenseSheet(string path, string fileName, DateTime expenseDate)
{
logger.DebugFormat("Processing User Expense Sheet Name [{0}]", fileName);
DataSet ds = ExcelFileReader.Read(path);
DataTable table = ds.Tables[0];
table = table.Rows.Cast <DataRow>().Where(row => !row.ItemArray.All(field => field is System.DBNull || string.Compare((Convert.ToString(field)).Trim(), string.Empty) == 0)).CopyToDataTable();
if (table.Rows.Count == 0)
{
logger.DebugFormat("No rows found in file [{0}]", fileName);
return(new ExcelFileProcess {
Message = "No rows to insert.", Response = false, MessageType = MessageClass.Error
});
}
string missingcolumn = ExcelFileReader.CheckAllColumnExist(table, ColumnList);
if (!string.IsNullOrWhiteSpace(missingcolumn))
{
logger.Debug(string.Format(Resources.Messages.MSG_GENERIC_COLUMN_MISSING, missingcolumn));
return(new ExcelFileProcess {
Message = string.Format(Resources.Messages.MSG_GENERIC_COLUMN_MISSING, missingcolumn), Response = false, MessageType = MessageClass.Error
});
}
string info = ExcelFileReader.ValidateData(table, MandatoryColumnList);
if (!string.IsNullOrWhiteSpace(info))
{
logger.Debug(info);
return(new ExcelFileProcess {
Message = info, Response = false, MessageType = MessageClass.Error
});
}
string errors = ExcelFileReader.ValidateAmount(table, ValidateColumnList);
if (!string.IsNullOrWhiteSpace(errors))
{
logger.Debug(errors);
return(new ExcelFileProcess {
Message = errors, Response = false, MessageType = MessageClass.Error
});
}
var department = departmentManagement.GetAllDepartments();
var allocation = userAllocationManagement.GetAllUsersActiveAllocations();
var users = UserManager.Users.Where(x => !x.IsDeleted).ToList();
errors = ExcelFileReader.ValidateDepartmentAndUsersAndAllocation(table, department, users, allocation);
if (!string.IsNullOrWhiteSpace(errors))
{
logger.Debug(errors);
return(new ExcelFileProcess {
Message = errors, Response = false, MessageType = MessageClass.Error
});
}
ExcelFileProcess process = InsertData(table, fileName, users, expenseDate, allocation);
return(process);
}
private ExcelFileProcess ProcessExpenseSheet(string path, string fileName)
{
logger.DebugFormat("Processing Expense Sheet Name [{0}]", fileName);
DataSet ds = ExcelFileReader.Read(path);
DataTable dataTable = ds.Tables[0];
dataTable = dataTable.Rows.Cast <DataRow>().Where(row => !row.ItemArray.All(field => field is System.DBNull || string.Compare((Convert.ToString(field)).Trim(), string.Empty) == 0)).CopyToDataTable();
if (dataTable.Rows.Count == 0)
{
logger.DebugFormat("No rows found in file [{0}]", fileName);
return(new ExcelFileProcess {
Message = "No rows to insert.", Response = false, MessageType = MessageClass.Error
});
}
string missingcolumn = ExcelFileReader.CheckAllColumnExist(dataTable, ColumnList);
if (!string.IsNullOrWhiteSpace(missingcolumn))
{
logger.Debug(string.Format(Resources.Messages.MSG_GENERIC_COLUMN_MISSING, missingcolumn));
return(new ExcelFileProcess {
Message = string.Format(Resources.Messages.MSG_GENERIC_COLUMN_MISSING, missingcolumn), Response = false, MessageType = MessageClass.Error
});
}
string info = ExcelFileReader.ValidateData(dataTable, MandatoryColumnList);
if (!string.IsNullOrWhiteSpace(info))
{
logger.Debug(info);
return(new ExcelFileProcess {
Message = info, Response = false, MessageType = MessageClass.Error
});
}
string errors = ExcelFileReader.ValidateDataFormat(dataTable);
if (!string.IsNullOrWhiteSpace(errors))
{
logger.Debug(errors);
return(new ExcelFileProcess {
Message = errors, Response = false, MessageType = MessageClass.Error
});
}
var userCards = userCardManagement.GetAllUserCards();
string accNumberError = ExcelFileReader.ValidateAccountNumber(dataTable, userCards);
if (!string.IsNullOrWhiteSpace(accNumberError))
{
logger.Debug(accNumberError);
return(new ExcelFileProcess {
Message = accNumberError, Response = false, MessageType = MessageClass.Error
});
}
ExcelFileProcess process = InsertData(dataTable, fileName, userCards);
return(process);
}