private static void CalculatePercentageChange()
{
using (CimscoPortalEntities _targetContext = new CimscoPortalEntities())
{
//IFormatProvider culture = new System.Globalization.CultureInfo("en-EN", true);
decimal _lastTotal;
decimal _currentTotal;
decimal _percentChange;
DateTime _lastInvoiceDate; // = new DateTime(1970, 1, 1); // Convert.ToDateTime("01/01/2000"); ;
DateTime _invoiceMonth;
foreach (var _energyPointId in _targetContext.EnergyPoints.Select(i => i.EnergyPointId))
{
_lastTotal = 0.00M;
_currentTotal = 0.00M;
_lastInvoiceDate = new DateTime(1970, 1, 1);
foreach (var _invoice in _targetContext.InvoiceSummaries.Where(i => i.EnergyPointId == _energyPointId).OrderBy(i => i.InvoiceDate))
{
_invoiceMonth = new DateTime(_invoice.InvoiceDate.Year, _invoice.InvoiceDate.Month, 1);
if (_invoiceMonth.AddMonths(-1) == _lastInvoiceDate)
{
_currentTotal = _invoice.InvoiceTotal;
_percentChange = 0.0M;
if (_lastTotal > 0.00M)
{
_percentChange = ((_currentTotal - _lastTotal) / _lastTotal) * 100;
}
}
else
{
_percentChange = -999.0M;
}
_invoice.PercentageChange = _percentChange;
_lastTotal = _currentTotal;
_lastInvoiceDate = _invoiceMonth;
}
}
_targetContext.SaveChanges();
}
}
private static void SyncSitesToIDE_and_Portal(CimscoNZEntities _masterDataContext)
{
using (CimscoIDE_dbEntities _ideDataContext = new CimscoIDE_dbEntities())
using (CimscoPortalEntities _portalDataContext = new CimscoPortalEntities())
{
//var _sourceSiteIdList = _sourceSites.Select(s => s.SiteId).ToList();
using (var _transaction1 = _ideDataContext.Database.BeginTransaction())
using (var _transaction2 = _portalDataContext.Database.BeginTransaction())
{
var _alreadyInTarget1 = (from _targetIds in _ideDataContext.sSites
select _targetIds.SiteId).ToList();
var _alreadyInTarget2 = (from _targetIds in _portalDataContext.Sites
select _targetIds.SiteId).ToList();
var _sourceMasterData = (from _sourceRecords in _masterDataContext.Sites
select _sourceRecords.SiteId).ToList();
_ideDataContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[Sites] ON");
_portalDataContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[Sites] ON");
foreach (int _id in _sourceMasterData)
{
sSite _newRecord1 = _masterDataContext.Sites.Where(s => s.SiteId == _id).ProjectTo<sSite>().FirstOrDefault();
if (_alreadyInTarget1.Contains(_id))
{
var _targetRecord = _ideDataContext.sSites.Where(s => s.SiteId == _id).FirstOrDefault();
_ideDataContext.Entry(_targetRecord).CurrentValues.SetValues(_newRecord1);
_ideDataContext.SaveChanges();
}
else
{
_ideDataContext.sSites.Add(_newRecord1);
_ideDataContext.SaveChanges();
}
InvoiceDataUpload.DataTarget.Site _newRecord2 = _masterDataContext.Sites.Where(s => s.SiteId == _id).ProjectTo<InvoiceDataUpload.DataTarget.Site>().FirstOrDefault();
if (_alreadyInTarget2.Contains(_id))
{
var _targetRecord = _portalDataContext.Sites.Where(s => s.SiteId == _id).FirstOrDefault();
_portalDataContext.Entry(_targetRecord).CurrentValues.SetValues(_newRecord2);
_portalDataContext.SaveChanges();
}
else
{
_portalDataContext.Sites.Add(_newRecord2);
_portalDataContext.SaveChanges();
}
}
_ideDataContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[Sites] OFF");
_portalDataContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[Sites] OFF");
_transaction1.Commit();
_transaction2.Commit();
}
}
}
private static string SyncEnergySuppliersToIDE_and_Portal(CimscoNZEntities _masterDataContext)
{
int _updates = 0;
int _adds = 0;
string _dataType = "Energy Suppliers";
using (CimscoIDE_dbEntities _ideDataContext = new CimscoIDE_dbEntities())
using (CimscoPortalEntities _portalDataContext = new CimscoPortalEntities())
{
//var _sourceSiteIdList = _sourceSites.Select(s => s.SiteId).ToList();
using (var _transaction1 = _ideDataContext.Database.BeginTransaction())
using (var _transaction2 = _portalDataContext.Database.BeginTransaction())
{
var _alreadyInTarget1 = (from _targetIds in _ideDataContext.sEnergySuppliers
select _targetIds.SupplierId).ToList();
var _alreadyInTarget2 = (from _targetIds in _portalDataContext.EnergySuppliers
select _targetIds.SupplierId).ToList();
var _sourceMasterData = (from _sourceRecords in _masterDataContext.EnergySuppliers
select _sourceRecords.SupplierId).ToList();
_ideDataContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[EnergySuppliers] ON");
_portalDataContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[EnergySuppliers] ON");
foreach (int _id in _sourceMasterData)
{
sEnergySupplier _newRecord1 = _masterDataContext.EnergySuppliers.Where(s => s.SupplierId == _id).ProjectTo<sEnergySupplier>().FirstOrDefault();
if (_alreadyInTarget1.Contains(_id))
{
var _targetRecord = _ideDataContext.sEnergySuppliers.Where(s => s.SupplierId == _id).FirstOrDefault();
_ideDataContext.Entry(_targetRecord).CurrentValues.SetValues(_newRecord1);
_ideDataContext.SaveChanges();
_updates++;
}
else
{
_ideDataContext.sEnergySuppliers.Add(_newRecord1);
_ideDataContext.SaveChanges();
_adds++;
}
InvoiceDataUpload.DataTarget.EnergySupplier _newRecord2 = _masterDataContext.EnergySuppliers.Where(s => s.SupplierId == _id).ProjectTo<InvoiceDataUpload.DataTarget.EnergySupplier>().FirstOrDefault();
if (_alreadyInTarget2.Contains(_id))
{
var _targetRecord = _portalDataContext.EnergySuppliers.Where(s => s.SupplierId == _id).FirstOrDefault();
_portalDataContext.Entry(_targetRecord).CurrentValues.SetValues(_newRecord2);
_portalDataContext.SaveChanges();
_updates++;
}
else
{
_portalDataContext.EnergySuppliers.Add(_newRecord2);
_portalDataContext.SaveChanges();
_adds++;
}
}
_ideDataContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[EnergySuppliers] OFF");
_portalDataContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[EnergySuppliers] OFF");
_transaction1.Commit();
_transaction2.Commit();
}
}
return String.Format("{2}: Additions : {0}, Updates : {1}", _adds.ToString(), _updates.ToString(), _dataType);
}
private static void RemoveDeletedSitesFromIDE_and_Portal(CimscoNZEntities _masterDataContext)
{
using (CimscoIDE_dbEntities _ideDataContext = new CimscoIDE_dbEntities())
using (CimscoPortalEntities _portalDataContext = new CimscoPortalEntities())
{
using (var transaction1 = _portalDataContext.Database.BeginTransaction())
using (var transaction2 = _ideDataContext.Database.BeginTransaction())
{
var _recordsInSource = (from _targetIds in _masterDataContext.Sites
select _targetIds.SiteId).ToList();
var _deleteFromTarget1 = (from _sourceRecords in _portalDataContext.Sites
where !_recordsInSource.Contains(_sourceRecords.SiteId)
select _sourceRecords.SiteId).ToList();
var _deleteFromTarget2 = (from _sourceRecords in _ideDataContext.sSites
where !_recordsInSource.Contains(_sourceRecords.SiteId)
select _sourceRecords.SiteId).ToList();
if (_deleteFromTarget1.Count != _deleteFromTarget2.Count)
{
// Log event, as these should match
}
if (_deleteFromTarget1.Count > 0)
{
foreach (int _id in _deleteFromTarget1)
{
var _removeRecord = new InvoiceDataUpload.DataTarget.Site { SiteId = _id };
_portalDataContext.Sites.Attach(_removeRecord);
_portalDataContext.Sites.Remove(_removeRecord);
}
_portalDataContext.SaveChanges();
}
if (_deleteFromTarget2.Count > 0)
{
foreach (int _id in _deleteFromTarget2)
{
var _removeRecord = new sSite { SiteId = _id };
_ideDataContext.sSites.Attach(_removeRecord);
_ideDataContext.sSites.Remove(_removeRecord);
}
_ideDataContext.SaveChanges();
}
transaction1.Commit();
transaction2.Commit();
}
}
}
private static string CopyNewInvoicesToTarget(int span, int invoiceId)
{
int _adds = 0;
// IEnumerable<DataSource.sInvoiceSummary> _all = _sourceContext.sInvoiceSummaries.Where(s => s.EnergyCharge.BD0004 > 0.0M);
CimscoPortalEntities _targetContext = new CimscoPortalEntities();
CimscoIDE_dbEntities _sourceContext = new CimscoIDE_dbEntities();
// IEnumerable<DataTarget.InvoiceSummary> _all2 = _targetContext.InvoiceSummaries.Where(s => s.EnergyCharge.BD0004 > 0.0M);
DateTime _cutOffDate;
switch (span)
{
case 0: _cutOffDate = new DateTime(2000, 1, 1);
break;
default: _cutOffDate = DateTime.Now.AddDays(span * -1);
break;
};
var _newInvoices = (from _source in _sourceContext.sInvoiceSummaries
where _source.InvoiceDate > _cutOffDate & _source.CheckedOk == true
select _source.InvoiceSummaryId).ToList();
var _invoicesAlreadyInTarget = (from _target in _targetContext.InvoiceSummaries
where _newInvoices.Contains(_target.InvoiceId)
select _target.InvoiceId).ToList();
foreach (int _id in _newInvoices)
{
if (!_invoicesAlreadyInTarget.Contains(_id))
{
InvoiceSummary _new = _sourceContext.sInvoiceSummaries.Where(s => s.InvoiceSummaryId == _id)
.ProjectTo<InvoiceSummary>().FirstOrDefault();
_targetContext.InvoiceSummaries.Add(_new);
_targetContext.SaveChanges();
_adds++;
}
}
return String.Format("Invoices copied : {0}\n", _adds.ToString());
}