protected void MergeData(Batch batchFile)
{
// See if there is an existing batch in the DB. Ignore casing in the batch names.
var existingBatch = DatabaseContext.Batches.FirstOrDefault(batch => batch.Name.ToLower() == batch.Name.ToLower());
if (existingBatch == null)
{
// The batch does not exist. Create a new item in the database, and save it directly.
existingBatch = new Batch
{
CreatedBy = "System",
DateCreated = DateTime.Now,
Name = batchFile.Name,
};
DatabaseContext.Batches.Add(existingBatch);
DatabaseContext.SaveChanges();
}
foreach (var subBatch in batchFile.SubBatches)
{
var existingSubBatch = DatabaseContext.SubBatches.FirstOrDefault(databaseSubBatch => subBatch.Code.ToLower() == databaseSubBatch.Code.ToLower());
if (existingSubBatch == null)
{
// The sub batch does not exist. Create a new item in the database and save it directly.
existingSubBatch = new SubBatch
{
Code = subBatch.Code,
BatchID = existingBatch.BatchID,
};
DatabaseContext.SubBatches.Add(existingSubBatch);
DatabaseContext.SaveChanges();
}
else
{
// The sub batch exists, make sure the BatchId's match.
if (existingSubBatch.BatchID != existingBatch.BatchID)
{
throw new InvalidDataException(string.Format("Importing subbatch '{0}' caused an error. The batch id in the file is '{1}' but the batch id in the database is '{2}' ", existingSubBatch.Code, existingSubBatch.BatchID, existingBatch.BatchID));
}
}
foreach (var simCard in subBatch.SimCards)
{
var addToDb = false;
var existingSimcard = DatabaseContext.SIMCards.FirstOrDefault(card => card.ICCID == simCard.ICCID);
if (existingSimcard == null)
{
existingSimcard = new SIMCard
{
ICCID = simCard.ICCID,
SubBatch = existingSubBatch,
SubBatchID = existingSubBatch.SubBatchID,
};
addToDb = true;
}
else
{
if (simCard.SubBatchID != existingSubBatch.SubBatchID)
{
throw new InvalidDataException(string.Format("Simcard with ICCID belongs to subbatch {0} according to the file, but the database says it belongs to subbatch {1}", existingSimcard.SubBatchID, simCard.SubBatchID));
}
}
existingSimcard.CapturedBy = simCard.CapturedBy;
existingSimcard.DateCaptured = simCard.DateCaptured;
var group = GetGroup(simCard.Group.Name);
existingSimcard.Group = group;
existingSimcard.GroupID = group.GroupID;
existingSimcard.IMSI = simCard.IMSI;
existingSimcard.MSISDN = simCard.MSISDN;
var network = GetNetwork(simCard.Network.Name);
existingSimcard.Network = network;
existingSimcard.NetworkID = network.NetworkID;
var package = GetPackage(simCard.Package.Name);
existingSimcard.Package = package;
existingSimcard.PackageID = package.PackageID;
existingSimcard.TrackingCode = simCard.TrackingCode;
if (addToDb)
{
DatabaseContext.SIMCards.Add(existingSimcard);
}
DatabaseContext.SaveChanges();
}
}
}
public Batch Parse()
{
var fileInfo = new FileInfo(FilePath);
if (!fileInfo.Exists)
{
throw new FileNotFoundException(string.Format("The file '{0}' does not exist", FilePath));
}
// The file represents the batch. The name of the batch is the name of the file.
var batch = new Batch();
batch.Name = DetermineFileName(fileInfo);
batch.SubBatches = new List<SubBatch>();
try
{
using (var factory = new LinqToExcel.ExcelQueryFactory(FilePath))
{
// The work sheets are the sub batches.
var subBatchNames = factory.GetWorksheetNames();
foreach (var subBatchName in subBatchNames)
{
// Loop the sub batches.
var subBatch = new SubBatch
{
Code = subBatchName
};
var subBatchRows = factory.Worksheet(subBatchName);
// Read the rows. The rows are matched using the row number.
subBatch.SimCards = subBatchRows.Select(row => new SIMCard
{
TrackingCode = row[0].Value.ToString(),
SubBatch = subBatch,
DateCaptured = DateTime.Parse(row[3].Value.ToString()),
CapturedBy = row[4].Value.ToString(),
Network = new Network
{
Name = row[5].Value.ToString()
},
Package = new Package
{
Name = row[6].Value.ToString()
},
ICCID = row[7].Value.ToString(),
IMSI = row[8].Value.ToString(),
MSISDN = row[9].Value.ToString(),
Group = new Group
{
Name = row[10].Value.ToString()
}
}).ToList();
batch.SubBatches.Add(subBatch);
}
}
}
catch (Exception)
{
// perhaps some cleaning up?
throw;
}
return batch;
}