protected override void OnRollback(int pass)
{
DeliveryHistoryEntry prepareEntry = null;
string guid = this.CurrentDelivery.DeliveryID.ToString("N");
IEnumerable <DeliveryHistoryEntry> prepareEntries = this.CurrentDelivery.History.Where(entry => entry.Operation == DeliveryOperation.Prepared);
if (prepareEntries != null && prepareEntries.Count() > 0)
{
prepareEntry = (DeliveryHistoryEntry)prepareEntries.Last();
}
if (prepareEntry == null)
{
throw new Exception(String.Format("The delivery '{0}' has never been committed so it cannot be rolled back.", guid));
}
_rollbackCommand = _rollbackCommand ?? DataManager.CreateCommand(this.Options.SqlRollbackCommand, CommandType.StoredProcedure);
_rollbackCommand.Connection = _sqlConnection;
_rollbackCommand.Transaction = _rollbackTransaction;
_rollbackCommand.Parameters["@DeliveryID"].Value = guid;
_rollbackCommand.Parameters["@TableName"].Value = prepareEntry.Parameters[Consts.DeliveryHistoryParameters.CommitTableName];
_rollbackCommand.ExecuteNonQuery();
this.CurrentDelivery.IsCommited = false;
}
protected override IEnumerable <ValidationResult> Validate()
{
Channel channel = new Channel();
progress += 0.1;
this.ReportProgress(progress);
#region Getting Service option params
//Getting Accounts list
string[] accounts;
if (this.Instance.AccountID == -1)
{
if (String.IsNullOrEmpty(this.Instance.Configuration.Options["AccountsList"]))
{
throw new Exception("Missing Configuration option AccountsList");
}
accounts = this.Instance.Configuration.Options["AccountsList"].Split(',');
}
else
{
List <string> account = new List <string>()
{
this.Instance.AccountID.ToString()
};
accounts = account.ToArray();
}
//Getting Table
string comparisonTable;
if (String.IsNullOrEmpty(this.Instance.Configuration.Options["SourceTable"]))
{
throw new Exception("Missing Configuration option SourceTable");
}
else
{
comparisonTable = this.Instance.Configuration.Options["SourceTable"];
}
//Getting Channel List
if (String.IsNullOrEmpty(this.Instance.Configuration.Options["ChannelList"]))
{
throw new Exception("Missing Configuration option ChannelList");
}
string[] channels = this.Instance.Configuration.Options["ChannelList"].Split(',');
//Getting TimePeriod
DateTime fromDate, toDate;
if ((String.IsNullOrEmpty(this.Instance.Configuration.Options["fromDate"])) && (String.IsNullOrEmpty(this.Instance.Configuration.Options["toDate"])))
{
fromDate = this.TargetPeriod.Start.ToDateTime();
toDate = this.TargetPeriod.End.ToDateTime();
}
else
{
fromDate = Convert.ToDateTime(this.Instance.Configuration.Options["fromDate"]);
toDate = Convert.ToDateTime(this.Instance.Configuration.Options["toDate"]);
}
#endregion
if (this.Delivery == null || this.Delivery.DeliveryID.Equals(Guid.Empty))
{
#region Creating Delivery Search List
List <DeliverySearchItem> deliverySearchList = new List <DeliverySearchItem>();
while (fromDate <= toDate)
{
// {start: {base : '2009-01-01', h:0}, end: {base: '2009-01-01', h:'*'}}
var subRange = new DateTimeRange()
{
Start = new DateTimeSpecification()
{
BaseDateTime = fromDate,
Hour = new DateTimeTransformation()
{
Type = DateTimeTransformationType.Exact, Value = 0
},
},
End = new DateTimeSpecification()
{
BaseDateTime = fromDate,
Hour = new DateTimeTransformation()
{
Type = DateTimeTransformationType.Max
},
}
};
foreach (var Channel in channels)
{
foreach (string account in accounts)
{
DeliverySearchItem delivery = new DeliverySearchItem();
delivery.account = new Account()
{
ID = Convert.ToInt32(account)
};
delivery.channel = new Channel()
{
ID = Convert.ToInt32(Channel)
};
delivery.targetPeriod = subRange;
deliverySearchList.Add(delivery);
progress += 0.3 * ((1 - progress) / (channels.LongLength + accounts.LongLength));
this.ReportProgress(progress);
}
}
fromDate = fromDate.AddDays(1);
}
#endregion
foreach (DeliverySearchItem deliveryToSearch in deliverySearchList)
{
#region Foreach
//Getting criterion matched deliveries
Delivery[] deliveriesToCheck = Delivery.GetByTargetPeriod(deliveryToSearch.targetPeriod.Start.ToDateTime(), deliveryToSearch.targetPeriod.End.ToDateTime(), deliveryToSearch.channel, deliveryToSearch.account);
bool foundCommited = false;
progress += 0.3 * (1 - progress);
this.ReportProgress(progress);
foreach (Delivery d in deliveriesToCheck)
{
int rollbackIndex = -1;
int commitIndex = -1;
#region Searching and researching commited and rolledback deliveries
for (int i = 0; i < d.History.Count; i++)
{
if (d.History[i].Operation == DeliveryOperation.Committed)
{
commitIndex = i;
}
else if (d.History[i].Operation == DeliveryOperation.RolledBack)
{
rollbackIndex = i;
}
}
if (commitIndex > rollbackIndex)
{
object totalso;
foundCommited = true;
DeliveryHistoryEntry commitEntry = null;
IEnumerable <DeliveryHistoryEntry> processedEntries = d.History.Where(entry => (entry.Operation == DeliveryOperation.Imported));
if (processedEntries != null && processedEntries.Count() > 0)
{
commitEntry = (DeliveryHistoryEntry)processedEntries.Last();
}
else
{
continue;
}
if (commitEntry.Parameters.TryGetValue(Edge.Data.Pipeline.Common.Importing.Consts.DeliveryHistoryParameters.ChecksumTotals, out totalso))
{
Dictionary <string, double> totals = (Dictionary <string, double>)totalso;
//Check Delivery data vs OLTP
yield return(DeliveryDbCompare(d, totals, "OltpDB", comparisonTable));
}
}
#endregion
}
//could not find deliveries by user criterions
if (deliveriesToCheck.Length == 0)
{
yield return(new ValidationResult()
{
ResultType = ValidationResultType.Error,
AccountID = deliveryToSearch.account.ID,
TargetPeriodStart = deliveryToSearch.targetPeriod.Start.ToDateTime(),
TargetPeriodEnd = deliveryToSearch.targetPeriod.End.ToDateTime(),
Message = "Cannot find deliveries in DB",
ChannelID = deliveryToSearch.channel.ID,
CheckType = this.Instance.Configuration.Name
});
}
else if (!foundCommited)
{
yield return(new ValidationResult()
{
ResultType = ValidationResultType.Error,
AccountID = deliveryToSearch.account.ID,
TargetPeriodStart = deliveryToSearch.targetPeriod.Start.ToDateTime(),
TargetPeriodEnd = deliveryToSearch.targetPeriod.End.ToDateTime(),
Message = "Cannot find Commited deliveries in DB",
ChannelID = deliveryToSearch.channel.ID,
CheckType = this.Instance.Configuration.Name
});
}
#endregion
} // End of foreach
}
else
{
//Getting current Delivery totals
object totalso;
DeliveryHistoryEntry commitEntry = null;
IEnumerable <DeliveryHistoryEntry> processedEntries = this.Delivery.History.Where(entry => (entry.Operation == DeliveryOperation.Imported));
if (processedEntries != null && processedEntries.Count() > 0)
{
commitEntry = (DeliveryHistoryEntry)processedEntries.Last();
if (commitEntry.Parameters.TryGetValue(Edge.Data.Pipeline.Common.Importing.Consts.DeliveryHistoryParameters.ChecksumTotals, out totalso))
{
Dictionary <string, double> totals = (Dictionary <string, double>)totalso;
yield return(DeliveryDbCompare(this.Delivery, totals, "OltpDB", comparisonTable));
}
}
}
}
protected override void OnCommit(int pass)
{
DeliveryHistoryEntry processedEntry = this.CurrentDelivery.History.Last(entry => entry.Operation == DeliveryOperation.Imported);
if (processedEntry == null)
{
throw new Exception("This delivery has not been imported yet (could not find an 'Imported' history entry).");
}
DeliveryHistoryEntry preparedEntry = this.CurrentDelivery.History.Last(entry => entry.Operation == DeliveryOperation.Prepared);
if (preparedEntry == null)
{
throw new Exception("This delivery has not been prepared yet (could not find an 'Prepared' history entry).");
}
// get this from last 'Processed' history entry
string measuresFieldNamesSQL = processedEntry.Parameters[Consts.DeliveryHistoryParameters.MeasureOltpFieldsSql].ToString();
string measuresNamesSQL = processedEntry.Parameters[Consts.DeliveryHistoryParameters.MeasureNamesSql].ToString();
string tablePerfix = processedEntry.Parameters[Consts.DeliveryHistoryParameters.TablePerfix].ToString();
string deliveryId = this.CurrentDelivery.DeliveryID.ToString("N");
// ...........................
// COMMIT data to OLTP
_commitCommand = _commitCommand ?? DataManager.CreateCommand(this.Options.SqlCommitCommand, CommandType.StoredProcedure);
_commitCommand.Connection = _sqlConnection;
_commitCommand.Transaction = _commitTransaction;
_commitCommand.Parameters["@DeliveryFileName"].Size = 4000;
_commitCommand.Parameters["@DeliveryFileName"].Value = tablePerfix;
_commitCommand.Parameters["@CommitTableName"].Size = 4000;
_commitCommand.Parameters["@CommitTableName"].Value = preparedEntry.Parameters["CommitTableName"];
_commitCommand.Parameters["@MeasuresNamesSQL"].Size = 4000;
_commitCommand.Parameters["@MeasuresNamesSQL"].Value = measuresNamesSQL;
_commitCommand.Parameters["@MeasuresFieldNamesSQL"].Size = 4000;
_commitCommand.Parameters["@MeasuresFieldNamesSQL"].Value = measuresFieldNamesSQL;
_commitCommand.Parameters["@Signature"].Size = 4000;
_commitCommand.Parameters["@Signature"].Value = this.CurrentDelivery.Signature;;
_commitCommand.Parameters["@DeliveryIDsPerSignature"].Size = 4000;
_commitCommand.Parameters["@DeliveryIDsPerSignature"].Direction = ParameterDirection.Output;
_commitCommand.Parameters["@DeliveryID"].Size = 4000;
_commitCommand.Parameters["@DeliveryID"].Value = deliveryId;
try
{
_commitCommand.ExecuteNonQuery();
// _commitTransaction.Commit();
string deliveryIDsPerSignature = _commitCommand.Parameters["@DeliveryIDsPerSignature"].Value.ToString();
string[] existDeliveries;
if ((!string.IsNullOrEmpty(deliveryIDsPerSignature) && deliveryIDsPerSignature != "0"))
{
_commitTransaction.Rollback();
existDeliveries = deliveryIDsPerSignature.Split(',');
List <Delivery> deliveries = new List <Delivery>();
foreach (string existDelivery in existDeliveries)
{
deliveries.Add(Delivery.Get(Guid.Parse(existDelivery)));
}
throw new DeliveryConflictException(string.Format("Deliveries with the same signature are already committed in the database\n Deliveries:\n {0}:", deliveryIDsPerSignature))
{
ConflictingDeliveries = deliveries.ToArray()
};
}
else
{
//already updated by sp, this is so we don't override it
this.CurrentDelivery.IsCommited = true;
}
}
finally
{
this.State = DeliveryImportManagerState.Idle;
}
}
protected override void OnPrepare(int pass)
{
DeliveryHistoryEntry processedEntry = this.CurrentDelivery.History.Last(entry => entry.Operation == DeliveryOperation.Imported);
if (processedEntry == null)
{
throw new Exception("This delivery has not been imported yet (could not find an 'Imported' history entry).");
}
// get this from last 'Processed' history entry
string measuresFieldNamesSQL = processedEntry.Parameters[Consts.DeliveryHistoryParameters.MeasureOltpFieldsSql].ToString();
string measuresNamesSQL = processedEntry.Parameters[Consts.DeliveryHistoryParameters.MeasureNamesSql].ToString();
string tablePerfix = processedEntry.Parameters[Consts.DeliveryHistoryParameters.TablePerfix].ToString();
string deliveryId = this.CurrentDelivery.DeliveryID.ToString("N");
if (pass == Prepare_PREPARE_PASS)
{
// ...........................
// PREPARE data
_prepareCommand = _prepareCommand ?? DataManager.CreateCommand(this.Options.SqlPrepareCommand, CommandType.StoredProcedure);
_prepareCommand.Connection = _sqlConnection;
_prepareCommand.Parameters["@DeliveryID"].Size = 4000;
_prepareCommand.Parameters["@DeliveryID"].Value = deliveryId;
_prepareCommand.Parameters["@DeliveryTablePrefix"].Size = 4000;
_prepareCommand.Parameters["@DeliveryTablePrefix"].Value = tablePerfix;
_prepareCommand.Parameters["@MeasuresNamesSQL"].Size = 4000;
_prepareCommand.Parameters["@MeasuresNamesSQL"].Value = measuresNamesSQL;
_prepareCommand.Parameters["@MeasuresFieldNamesSQL"].Size = 4000;
_prepareCommand.Parameters["@MeasuresFieldNamesSQL"].Value = measuresFieldNamesSQL;
_prepareCommand.Parameters["@CommitTableName"].Size = 4000;
_prepareCommand.Parameters["@CommitTableName"].Direction = ParameterDirection.Output;
try { _prepareCommand.ExecuteNonQuery(); }
catch (Exception ex)
{
throw new Exception(String.Format("Delivery {0} failed during Prepare.", deliveryId), ex);
}
this.HistoryEntryParameters[Consts.DeliveryHistoryParameters.CommitTableName] = _prepareCommand.Parameters["@CommitTableName"].Value;
}
else if (pass == Prepare_VALIDATE_PASS)
{
object totalso;
if (processedEntry.Parameters.TryGetValue(Consts.DeliveryHistoryParameters.ChecksumTotals, out totalso))
{
var totals = (Dictionary <string, double>)totalso;
object sql;
if (processedEntry.Parameters.TryGetValue(Consts.DeliveryHistoryParameters.MeasureValidateSql, out sql))
{
string measuresValidateSQL = (string)sql;
measuresValidateSQL = measuresValidateSQL.Insert(0, "SELECT ");
measuresValidateSQL = measuresValidateSQL + string.Format("\nFROM {0}_{1} \nWHERE DeliveryID=@DeliveryID:Nvarchar", tablePerfix, ValidationTable);
SqlCommand validateCommand = DataManager.CreateCommand(measuresValidateSQL);
validateCommand.Connection = _sqlConnection;
validateCommand.Parameters["@DeliveryID"].Value = this.CurrentDelivery.DeliveryID.ToString("N");
using (SqlDataReader reader = validateCommand.ExecuteReader())
{
if (reader.Read())
{
var results = new StringBuilder();
foreach (KeyValuePair <string, double> total in totals)
{
if (reader[total.Key] is DBNull)
{
if (total.Value == 0)
{
Log.Write(string.Format("[zero totals] {0} has no data or total is 0 in table {1} for target period {2}", total.Key, ValidationTable, CurrentDelivery.TargetPeriod), LogMessageType.Information);
}
else
{
results.AppendFormat("{0} is null in table {1}\n but {2} in measure {3}", total.Key, ValidationTable, total.Key, total.Value);
}
}
else
{
double val = Convert.ToDouble(reader[total.Key]);
double diff = Math.Abs((total.Value - val) / total.Value);
if (diff > this.Options.CommitValidationThreshold)
{
results.AppendFormat("{0}: processor totals = {1}, {2} table = {3}\n", total.Key, total.Value, ValidationTable, val);
}
else if (val == 0 && total.Value == 0)
{
Log.Write(string.Format("[zero totals] {0} has no data or total is 0 in table {1} for target period {2}", total.Key, ValidationTable, CurrentDelivery.TargetPeriod), LogMessageType.Information);
}
}
}
if (results.Length > 0)
{
throw new Exception("Commit validation (checksum) failed:\n" + results.ToString());
}
}
else
{
throw new Exception(String.Format("Commit validation (checksum) did not find any data matching this delivery in {0}.", ValidationTable));
}
}
}
}
}
}