public ExcelImportResponse ExcelImport(IUnitOfWork uow, ExcelImportRequest request)
{
request.CheckNotNull();
Check.NotNullOrWhiteSpace(request.FileName, "filename");
UploadHelper.CheckFileNameSecurity(request.FileName);
if (!request.FileName.StartsWith("temporary/"))
{
throw new ArgumentOutOfRangeException("filename");
}
ExcelPackage ep = new ExcelPackage();
using (var fs = new FileStream(UploadHelper.DbFilePath(request.FileName), FileMode.Open, FileAccess.Read))
ep.Load(fs);
var rowF = RowsRow.Fields;
var proj = ProjectorsRow.Fields;
var phas = PhasesRow.Fields;
var clie = ClientsRow.Fields;
var part = PartsRow.Fields;
var response = new ExcelImportResponse();
response.ErrorList = new List <string>();
var worksheet = ep.Workbook.Worksheets[1];
for (var row = 2; row <= worksheet.Dimension.End.Row; row++)
{
try
{
var rowReaded = uow.Connection.TryFirst <RowsRow>(q => q
.Select(rowF.RowId)
.Where(
rowF.ClientName == Convert.ToString(worksheet.Cells[row, 2].Value ?? "") &&
rowF.ClassifierNumber == Convert.ToInt16(worksheet.Cells[row, 3].Value ?? 0) &&
rowF.Number == Convert.ToInt16(worksheet.Cells[row, 4].Value ?? 0)));
if (rowReaded == null)
{
rowReaded = new RowsRow {
}
}
;
else
{
// avoid assignment errors
rowReaded.TrackWithChecks = false;
}
var projectorName = Convert.ToString(worksheet.Cells[row, 7].Value ?? "");
if (!string.IsNullOrWhiteSpace(projectorName))
{
var project = uow.Connection.TryFirst <ProjectorsRow>(q => q
.Select(proj.ProjectorId)
.Where(proj.Name == projectorName));
if (project == null)
{
project = new ProjectorsRow
{
Name = projectorName
};
var phaseId = Convert.ToInt32(uow.Connection.InsertAndGetID(project));
rowReaded.ProjectorId = phaseId;
}
else
{
rowReaded.ProjectorId = project.ProjectorId.Value;
}
}
else
{
response.ErrorList.Add("Error On Row " + row + ", Projector Name can't be empty");
continue;
}
var phaseName = Convert.ToString(worksheet.Cells[row, 10].Value ?? "");
if (!string.IsNullOrWhiteSpace(phaseName))
{
var phase = uow.Connection.TryFirst <PhasesRow>(q => q
.Select(phas.PhaseId)
.Where(phas.Name == phaseName));
if (phase == null)
{
phase = new PhasesRow
{
Name = phaseName
};
var phaseId = Convert.ToInt32(uow.Connection.InsertAndGetID(phase));
rowReaded.PhaseId = phaseId;
}
else
{
rowReaded.PhaseId = phase.PhaseId.Value;
}
}
else
{
response.ErrorList.Add("Error On Row " + row + ", Phase Name can't be empty");
continue;
}
var clientName = Convert.ToString(worksheet.Cells[row, 2].Value ?? "");
if (!string.IsNullOrWhiteSpace(clientName))
{
var client = uow.Connection.TryFirst <ClientsRow>(q => q
.Select(clie.ClientsId)
.Where(clie.Name == clientName));
if (client == null)
{
client = new ClientsRow
{
Name = clientName
};
var clientId = Convert.ToInt32(uow.Connection.InsertAndGetID(client));
rowReaded.ClientId = clientId;
}
else
{
rowReaded.ClientId = client.ClientsId.Value;
}
}
else
{
response.ErrorList.Add("Error On Row " + row + ", Client Name can't be empty");
continue;
}
var partName = Convert.ToString(worksheet.Cells[row, 9].Value ?? "");
if (!string.IsNullOrWhiteSpace(partName))
{
var parts = uow.Connection.TryFirst <PartsRow>(q => q
.Select(part.PartId)
.Where(part.Name == partName));
if (parts == null)
{
parts = new PartsRow()
{
Name = partName
};
var partId = Convert.ToInt32(uow.Connection.InsertAndGetID(parts));
rowReaded.PartId = partId;
}
else
{
rowReaded.PartId = parts.PartId.Value;
}
}
else
{
response.ErrorList.Add("Error On Row " + row + ", Part Name can't be empty");
continue;
}
rowReaded.ClassifierNumber = Convert.ToInt16(worksheet.Cells[row, 3].Value ?? 0);
rowReaded.Number = Convert.ToInt16(worksheet.Cells[row, 4].Value ?? 0);
rowReaded.Object = Convert.ToString(worksheet.Cells[row, 5].Value ?? "");
rowReaded.SubObject = Convert.ToString(worksheet.Cells[row, 6].Value ?? "");
rowReaded.Date = DateTime.Parse(worksheet.Cells[row, 8].Value.ToString(), System.Globalization.CultureInfo.CreateSpecificCulture("bg-BG"));
rowReaded.FoldersCount = Convert.ToString(worksheet.Cells[row, 11].Value ?? "");
if (rowReaded.RowId == null)
{
new MyRepository().Create(uow, new SaveRequest <MyRow>
{
Entity = rowReaded
});
response.Inserted = response.Inserted + 1;
}
else
{
new MyRepository().Update(uow, new SaveRequest <MyRow>
{
Entity = rowReaded,
EntityId = rowReaded.RowId.Value
});
response.Updated = response.Updated + 1;
}
}
catch (Exception ex)
{
response.ErrorList.Add("Exception on Row " + row + ": " + ex.Message);
}
}
return(response);
}
public void RemovePartsRow(PartsRow row) {
this.Rows.Remove(row);
}
public PartsRowChangeEvent(PartsRow row, global::System.Data.DataRowAction action) {
this.eventRow = row;
this.eventAction = action;
}
public void AddPartsRow(PartsRow row) {
this.Rows.Add(row);
}
