public BulkUpload BulkUpload_Get(Guid uploadId)
{
string sql = "SELECT [FileId], [Created], [CreatedBy], [Completed], [RecordCount] FROM [BulkUploads] WITH (NoLock) WHERE [Id] = @Id";
List <SqlParameter> paramList = new List <SqlParameter>();
paramList.Add(new SqlParameter("@Id", uploadId));
BulkUpload upload = null;
using (SqlDataReader rdr = base.OpenDataReaderInLine(sql, paramList))
{
if ((rdr != null) && (rdr.HasRows))
{
rdr.Read();
upload = new BulkUpload();
upload.Id = uploadId;
upload.FileId = rdr.GetGuid(0);
upload.Created = rdr.GetDateTime(1);
upload.CreatedBy = rdr.GetGuid(2);
if (!rdr.IsDBNull(3))
{
upload.Completed = rdr.GetDateTime(3);
}
if (!rdr.IsDBNull(4))
{
upload.RecordCount = rdr.GetInt32(4);
}
}
}
return(upload);
}
/// <summary>
/// Insert ExcelName and BatchNumber to db
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int InsertBulkUploadDetails(BulkUpload model)
{
model.UploadFile = model.File.FileName;
model.IsActive = true;
model.CreatedOn = DateTime.Now;
model.CreatedBy = 1;
_context.Set <BulkUpload>().Add(model);
return(_context.SaveChanges());
}
public static void Main(string[] args)
{
string baseUri = "http://localhost:9200/";
int size = 100000;
IUploadStrategy uploadStrategy = new BulkUpload();
var esClient = new ElasticSearchDataClient(uploadStrategy);
esClient.Upload(baseUri, size);
esClient.SetUploadingStrategy(new SingleRequestUpload());
esClient.Upload(baseUri, size);
}
private static string LoadProviderName(BulkUpload historyRecord)
{
var providerName = string.Empty;
foreach (var provider in historyRecord.BulkUploadProviders)
{
if (provider.Provider != null)
{
providerName += string.Concat(provider.Provider.ProviderName, Environment.NewLine);
}
}
return(providerName);
}
public ActionResult Upload(HttpPostedFileBase upload, BulkUpload model)
{
string info = "";
try {
// Check if the user has entered application ids
if (model.Applications.Trim().Length > 0)
{
string[] appications = model.Applications.Split(';');
string[] securityRoles = model.SecurityRoles.Split(';');
string[] permissionGroups = model.PermissionGroups.Split(';');
var file = new System.IO.StreamReader(upload.InputStream);
while (file.Peek() >= 0)
{
string line = file.ReadLine();
string[] fields = line.Split(',');
var bulkUser = new BulkUser();
bulkUser.Title = fields[0];
bulkUser.GivenName = fields[1];
bulkUser.MiddleName = fields[2];
bulkUser.Surname = fields[3];
bulkUser.Email = fields[4];
bulkUser.Password = fields[5];
// call to creat user
// call
}
}
else
{
// No Application entered
info = "No Application Ids entered.";
}
}
catch (Exception exp)
{
info = exp.Message;
}
return(View());
}
public ActionResult Upload(HttpPostedFileBase file)
{
string path = string.Empty;
string EntityType = "Assignment";
BulkUpload AssignmentUpload = new BulkUpload();
// Verify that the user selected a file
if (file != null && file.ContentLength > 0)
{
// extract only the filename
var fileName = Path.GetFileName(file.FileName);
// store the file inside ~/App_Data/uploads folder
path = Path.Combine(Server.MapPath("~/App_Data/Uploads"), fileName);
file.SaveAs(path);
}
TextFieldParser parser = new TextFieldParser(path);
parser.TextFieldType = FieldType.Delimited;
parser.SetDelimiters(",");
AssignmentUpload.Upload(path, parser, EntityType);
return RedirectToAction("Index");
}
public ActionResult UploadFile()
{
ExcelHelper excelHelp = new ExcelHelper();
List <FixAssetExcelUploadModel> assets = new List <FixAssetExcelUploadModel>();
BulkUpload currentUpload = new Common.BulkUpload();
BulkUploadProcess bulkProcess = new BulkUploadProcess();
AssetProcess assetProcess = new AssetProcess();
EmployeeProcess employeeProcess = new EmployeeProcess();
Employee sender = new Employee();
ConfigurationProcess configProcess = new ConfigurationProcess();
FastEmailConfiguration emailConfig = configProcess.GetEmailConfiguration();
StringBuilder uploadLog = new StringBuilder();
string filename = string.Empty;
string completeFileName = string.Empty;
//This is for the logging
bulkProcess.UserID = User.Identity.Name.ToInteger();
configProcess.UserID = User.Identity.Name.ToInteger();
employeeProcess.UserID = User.Identity.Name.ToInteger();
assetProcess.UserID = User.Identity.Name.ToInteger();
try
{
#region Get Request Files
foreach (string upload in Request.Files)
{
if (!(Request.Files[upload] != null && Request.Files[upload].ContentLength > 0))
{
continue;
}
string path = HttpContext.Server.MapPath("\\App_Data\\BulkUploads");
filename = Path.GetFileName(Request.Files[upload].FileName);
//check the filename and ensure its an xlsx file
if (String.Compare(filename.Substring(filename.Length - 4), "xlsx", true) != 0)
{
throw new Exception("Invalid file extension.");
}
//add the current time as unique indicator
filename = DateTime.Now.ToFileTime().ToString() + "_" + filename;
// If Upload folder is not yet existing, this code will create that directory.
if (!System.IO.Directory.Exists(path))
{
System.IO.Directory.CreateDirectory(path);
}
completeFileName = Path.Combine(path, filename);
Request.Files[upload].SaveAs(completeFileName);
}
#endregion
BulkUpload newFile = new BulkUpload()
{
EmployeeID = User.Identity.Name.ToInteger(),
FilePath = filename,
TotalRecords = 0,
TotalInserts = 0,
RequestDate = DateTime.Now,
Type = FASTConstant.BULKIPLOAD_TYPE_ASSET
};
if (bulkProcess.Add(newFile) == FASTConstant.RETURN_VAL_SUCCESS)
{
//get the current upload
currentUpload = bulkProcess.GetCurrentUpload(newFile.FilePath, newFile.EmployeeID);
}
if (currentUpload != null)
{
#region Process the excel file
//Success! Lets process the file.
System.Data.DataTable fixAssetTable = new DataTable();
fixAssetTable = excelHelp.GetExcelDataTable(completeFileName, "SELECT * FROM [FixAsset$]");
fixAssetTable = excelHelp.ConvertToFixAssetTable(fixAssetTable);
if (fixAssetTable == null)
{
throw new Exception("The upload file contains null data.");
}
assets = fixAssetTable.ToList <Models.FixAssetExcelUploadModel>();
sender = employeeProcess.GetEmployeeByID(currentUpload.EmployeeID);
if (assets.Count > 0)
{
int totalInserts = 0;
currentUpload.TotalRecords = assets.Count;
bulkProcess.UpdateProcessingStep(currentUpload, FASTConstant.BULKUPLOAD_STATUS_INPROGRESS);
foreach (FixAssetExcelUploadModel asset in assets)
{
FixAsset tempAsset = new FixAsset()
{
AssetTag = asset.AssetTag,
SerialNumber = asset.SerialNumber,
Model = asset.Model,
Brand = asset.Brand,
AssetClassID = asset.AssetClassID,
AssetTypeID = asset.AssetTypeID,
AssetStatusID = asset.AssetStatusID,
AcquisitionDate = asset.AcquisitionDate
};
if (assetProcess.Add(tempAsset) == FASTConstant.RETURN_VAL_SUCCESS)
{
totalInserts++;
uploadLog.AppendLine(String.Format("<p>{0} : {1} inserted.</p>", FASTConstant.SUCCESSFUL, asset.AssetTag));
}
else
{
uploadLog.AppendLine(String.Format("<p>{0} : {1} not inserted.</p>", FASTConstant.FAILURE, asset.AssetTag));
}
}
currentUpload.TotalInserts = totalInserts;
bulkProcess.UpdateProcessingStep(currentUpload, FASTConstant.BULKUPLOAD_STATUS_DONE);
//Send email to the requestor
FastEmail email = new FastEmail();
email.Receipients = new List <string>()
{
sender.EmailAddress
};
email.Subject = FASTConstant.EMAIL_SIMPLE_SUBJECT.Replace("[XXX]", "Fix Asset Bulk Upload Result");
email.HTMLBody = FASTProcess.Helper.EmailHelper.GenerateHTMLBody(FASTProcess.Helper.EmailHelper.EmailType.BULK_UPLOAD);
email.HTMLBody = email.HTMLBody.Replace(FASTConstant.EMAIL_RECEIPIENT_NAME, sender.FirstName + " " + sender.LastName);
email.HTMLBody = email.HTMLBody.Replace(FASTConstant.EMAIL_BULKUPLOAD_INFO, bulkProcess.GenerateUploadinformationHTML(currentUpload));
email.HTMLBody = email.HTMLBody.Replace(FASTConstant.EMAIL_BULKUPLOAD_LOG, uploadLog.ToString());
email.HTMLBody = email.HTMLBody.Replace(FASTConstant.EMAIL_BULKUPLOAD_SUMMARY, bulkProcess.GenerateSummaryinformationHTML(currentUpload));
SMTPEmail emailSender = new SMTPEmail(emailConfig, email);
emailSender.SendEmail();
}
else
{
bulkProcess.UpdateProcessingStep(currentUpload, FASTConstant.BULKUPLOAD_STATUS_DONE);
}
#endregion
}
TempData["Result"] = "SUCCESSFUL";
TempData["Source"] = "File Upload";
TempData["ExtraMessage"] = "An email will be sent to you containing the results of the upload process.";
TempData["Action"] = "Index";
TempData["Controller"] = "FixAsset";
return(View("~/Views/Shared/Result.cshtml"));
}
catch (Exception ex)
{
TempData["Result"] = "FAILURE";
TempData["Source"] = "Fix Asset Bulk Upload";
TempData["ExtraMessage"] = ex.Message;
return(View("~/Views/Shared/Result.cshtml"));
}
}
public JsonResult ProcessUploadedFiles(int assessmentId, IEnumerable <string> fileNames, int classId, int subjectId)
{
try
{
var location = $"{HostingEnvironment.ApplicationPhysicalPath}UploadedFile\\UploadedResults\\{UserInformation.UserInformationCredential.Username}\\{assessmentId}";
if (!Directory.Exists(location))
{
Directory.CreateDirectory(location);
}
var directory = new System.IO.DirectoryInfo(location);
var userId = UserInformation.UserInformationCredential.Id;
var status = Newtonsoft.Json.JsonConvert.SerializeObject(new OperationMessages()
{
FileProcessingState = FileProcessingState.New,
Message = "File Received",
FileProcessingMessageTypes = FileProcessingMessageTypes.Information
});
using (var data = new Entities())
{
var currentSessionId = data.EducationalPeriods.FirstOrDefault(x => x.IsActive)?.Id;
var currentSemesterId = data.SubEducationalPeriods.FirstOrDefault(x => x.IsActive)?.Id;
foreach (var file in fileNames)
{
var path = $"{directory.FullName}\\{file}";
var fileInfo = new FileInfo(path);
var history = data.UploadHistories.Add(new UploadHistory()
{
AssessmentId = assessmentId,
CredentialId = userId,
DateProcessEnd = DateTime.Now,
DateProcessStart = DateTime.Now,
DateUploaded = DateTime.Now,
FileSize = fileInfo.Length,
IsDeleted = false,
OperationMessages = $"{status}|",
ProcessingStatus = (int)FileProcessingState.New,
RawFileName = file,
SystemFileName = $"{Guid.NewGuid().ToString()}.{fileInfo.Extension}",
ClassId = classId,
SubjectId = subjectId
});
data.SaveChanges();
var historyId = history.Id;
history.OperationMessages += Newtonsoft.Json.JsonConvert.SerializeObject(new OperationMessages()
{
FileProcessingState = FileProcessingState.Processing,
Message = "Processing Started",
FileProcessingMessageTypes = FileProcessingMessageTypes.Information
}) + "|";
data.SaveChanges();
new Thread(() =>
{
var configuration = new CsvConfiguration
{
HasHeaderRecord = true,
BufferSize = 1024,
SkipEmptyRecords = true,
IsHeaderCaseSensitive = false
};
configuration.RegisterClassMap <StudentAssessmentResultMap>();
var splitRecords =
BulkUpload.GetFileDataToList <StudentAssessmentResult>(path, configuration)
.Select((x, i) => new { Index = i, Value = x })
.GroupBy(x => x.Index / 500)
.Select(x => x.Select(v => v.Value).ToList())
.ToList();
Parallel.ForEach(splitRecords, (splitRecord, parallellLoopState, i) =>
{
var currentSessionIdInt = Convert.ToInt32(currentSessionId);
var currentSemesterIdInt = Convert.ToInt32(currentSemesterId);
using (var innerData = new Entities())
{
foreach (var record in splitRecord)
{
//remove existing
var resultRaw = innerData.ResultRaws.FirstOrDefault(x => x.IsDeleted == false &&
x.AssessmentId == assessmentId &&
x.SessionId == currentSessionIdInt &&
x.SubSessionId == currentSemesterIdInt &&
x.ClassId == classId &&
x.SubjectId == subjectId &&
x.StudentId == record.StudentId);
if (resultRaw != null)
{
innerData.ResultRaws.Remove(resultRaw);
innerData.SaveChanges();
}
innerData.ResultRaws.Add(new ResultRaw()
{
AssessmentId = assessmentId,
DateUploaded = DateTime.Now,
FileId = historyId,
IsDeleted = false,
ScoreObtained = Convert.ToDecimal(record.MarksObtained.Trim()),
ScoreTotal = Convert.ToDecimal(record.MarksObtainable.Trim()),
SessionId = Convert.ToInt32(currentSessionId),
StudentId = record.StudentId,
SubSessionId = Convert.ToInt32(currentSemesterId),
ClassId = classId,
SubjectId = subjectId,
CredentialId = userId
});
innerData.SaveChanges();
}
}
});
using (var innerData = new Entities())
{
var innerHistory = innerData.UploadHistories.FirstOrDefault(x => x.Id == historyId);
innerHistory.OperationMessages += Newtonsoft.Json.JsonConvert.SerializeObject(new OperationMessages
()
{
FileProcessingState = FileProcessingState.Completed,
Message = "Processing Completed",
FileProcessingMessageTypes = FileProcessingMessageTypes.Information
}) + "|";
innerHistory.DateProcessEnd = DateTime.Now;
innerHistory.ProcessingStatus = (int)FileProcessingState.Completed;
innerData.SaveChanges();
}
System.IO.File.Delete(path);
}).Start();
}
}
return(new JsonResult()
{
Data =
new
{
Status = true,
Message = $"Succesfully Queued for Processing",
Data = ""
},
JsonRequestBehavior = JsonRequestBehavior.AllowGet
});
}
catch (Exception ex)
{
ActivityLogger.Log(ex);
return(new JsonResult()
{
Data = new { Status = false, Message = ex.Message, Data = string.Empty },
JsonRequestBehavior = JsonRequestBehavior.AllowGet
});
}
}
/// <summary>
/// Check condition for upload Excel sheet with combination of rta and filetype
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public JsonResponse ExcelUpload(BulkUpload model)
{
JsonResponse response = new JsonResponse();
if (model.File == null)
{
response.Status = "F";
response.Message = "Failed";
return(response);
}
try
{
System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
var extension = Path.GetExtension(model.File.FileName);
if (extension.Equals(".xls") || extension.Equals(".xlsx"))
{
bool FileHeaderMatching = false;
string[] FileHeader = null;
string[] RtaFileTypeCombo1 = BAL.Utilites.Constants.Message.RtaFileTypeCombination.rtaFileTypecombo1.Split(",");
string[] RtaFileTypeCombo2 = BAL.Utilites.Constants.Message.RtaFileTypeCombination.rtaFileTypecombo2.Split(",");
string[] RtaFileTypeCombo3 = BAL.Utilites.Constants.Message.RtaFileTypeCombination.rtaFileTypecombo3.Split(",");
string[] RtaFileTypeCombo4 = BAL.Utilites.Constants.Message.RtaFileTypeCombination.rtaFileTypecombo4.Split(",");
string[] RtaFileTypeCombo5 = BAL.Utilites.Constants.Message.RtaFileTypeCombination.rtaFileTypecombo5.Split(",");
string[] RtaFileTypeCombo6 = BAL.Utilites.Constants.Message.RtaFileTypeCombination.rtaFileTypecombo6.Split(",");
//string[] RtaFileTypeCombo7 = BAL.Utilites.Constants.Message.RtaFileTypeCombination.rtaFileTypecombo7.Split(",");
string ExpectedHeader = string.Empty;
string TableName = string.Empty;
DataTable table = ExcelDataTable(model.File);
//new folio
if (model.FileTypeId == Convert.ToInt32(RtaFileTypeCombo1[0]) && model.RTAsId == Convert.ToInt32(RtaFileTypeCombo1[1]) && model.File.FileName == RtaFileTypeCombo1[2])
{
ExpectedHeader = BAL.Utilites.Constants.Message.UploadFileHeader.CAMSFolioFile;
FileHeader = ExpectedHeader.Split(",");
TableName = "dbo.tbl_CamsFolio_Staging";
}
//new format camstran
else if (model.FileTypeId == Convert.ToInt32(RtaFileTypeCombo2[0]) && model.RTAsId == Convert.ToInt32(RtaFileTypeCombo2[1]) && model.File.FileName == RtaFileTypeCombo2[2])
{
ExpectedHeader = BAL.Utilites.Constants.Message.UploadFileHeader.CAMSTransactionFile;
FileHeader = ExpectedHeader.Split(",");
TableName = "dbo.tbl_CamsTransaction_Staging";
}
//Franklin tran
else if (model.FileTypeId == Convert.ToInt32(RtaFileTypeCombo5[0]) && model.RTAsId == Convert.ToInt32(RtaFileTypeCombo5[1]) && model.File.FileName == RtaFileTypeCombo5[2])
{
ExpectedHeader = BAL.Utilites.Constants.Message.UploadFileHeader.FranklinTranFileHeader;
FileHeader = ExpectedHeader.Split(",");
TableName = "dbo.tbl_Franklin_Tran_Staging";
}
//Franklin AUM
else if (model.FileTypeId == Convert.ToInt32(RtaFileTypeCombo4[0]) && model.RTAsId == Convert.ToInt32(RtaFileTypeCombo4[1]) && model.File.FileName == RtaFileTypeCombo4[2])
{
ExpectedHeader = BAL.Utilites.Constants.Message.UploadFileHeader.FranklinAUMFileHeader;
FileHeader = ExpectedHeader.Split(",");
TableName = "dbo.tbl_Franklin_AUM_Staging";
}
//Karvy AUM
else if (model.FileTypeId == Convert.ToInt32(RtaFileTypeCombo6[0]) && model.RTAsId == Convert.ToInt32(RtaFileTypeCombo6[1]) && model.File.FileName == RtaFileTypeCombo6[2])
{
ExpectedHeader = BAL.Utilites.Constants.Message.UploadFileHeader.KarvyAUMFileHeader;
FileHeader = ExpectedHeader.Split(",");
TableName = "dbo.tbl_karvy_aum_staging";
}
//Karvy Tran
else if (model.FileTypeId == Convert.ToInt32(RtaFileTypeCombo3[0]) && model.RTAsId == Convert.ToInt32(RtaFileTypeCombo3[1]) && model.File.FileName == RtaFileTypeCombo3[2])
{
ExpectedHeader = BAL.Utilites.Constants.Message.UploadFileHeader.KarvyTranFileHeader;
FileHeader = ExpectedHeader.Split(",");
TableName = "dbo.tbl_karvy_tran_staging";
}
else
{
response.Status = "F";
response.Message = "Rta's FileType Combination is mismatched.";
}
if (FileHeader != null)
{
for (int i = 0; i < table.Columns.Count; i++)
{
if (table.Columns[i].ToString().ToLower() == FileHeader[i].ToLower())
{
FileHeaderMatching = true;
}
else
{
FileHeaderMatching = false;
response.Status = "F";
response.Message = FileHeader[i] + " column is missing in the selected file";
break;
}
}
}
if (FileHeaderMatching)
{
string status = UploadExcelFile(table, TableName, FileHeader);
if (status != null)
{
model.BatchNumber = status;
int i = InsertBulkUploadDetails(model);
if (i != 0)
{
response.Status = "S";
response.Message = "Upload Successfully..";
}
else
{
response.Status = "F";
response.Message = "File is not inserted..";
}
}
else
{
response.Status = "F";
response.Message = "Something went wrong..";
}
}
}
else
{
response.Status = "F";
response.Message = "File is not valid";
}
}
catch (Exception ex)
{
throw ex;
}
return(response);
}
public JsonResult ImportExcelFile(BulkUpload model)
{
var request = _bulkUploadService.ExcelUpload(model);
return(Json(request));
}