private string GenerateHTMLElement(Diffrence d)
{
if (string.IsNullOrEmpty(GetAttributeValue(d)))
{
return(d.value);
}
else
{
return("<" + TagType + " " + AttributeName + "=\"" + GetAttributeValue(d) + "\">" + d.value + "</" + TagType + ">");
}
}
private string GetAttributeValue(Diffrence d)
{
switch (d.action)
{
case TextDiffAction.Add: return(AddAttributeValue);
case TextDiffAction.Remove: return(RemoveAttributeValue);
case TextDiffAction.Equal: return(EqualAttributeValue);
default: return("");
}
}
private string GenerateMDElement(Diffrence d)
{
string start;
string end;
switch (d.action)
{
case TextDiffAction.Add:
start = AddMDStart;
end = AddMDEnd;
break;
case TextDiffAction.Remove:
start = RemoveMDStart;
end = RemoveMDEnd;
break;
case TextDiffAction.Equal:
start = EqualMDStart;
end = EqualMDEnd;
break;
default: throw new InvalidOperationException("Diffrence.action is not set to valid value");
}
string output = d.value;
if (output[0] == ' ')
{
output = output.Insert(1, start);
}
else
{
output = start + output;
}
if (output[output.Length - 1] == ' ')
{
output = output.Insert(output.Length - 1, end);
}
else
{
output = output + end;
}
return(output);
}
public string GenerateOutput(Diffrence diffrence)
{
return(GenerateMDElement(diffrence));
}
protected override Data.Pipeline.Services.ValidationResult Compare(string SourceTable, string TargetTabel, Dictionary <string, string> Params)
{
//{"AccountID",account},
// {"ChannelID",channel},
// {"Date",fromDate.ToString()}
Edgeobjects.Account account = new Edgeobjects.Account()
{
ID = int.Parse(Params["AccountID"])
};
Edgeobjects.Channel channel = new Edgeobjects.Channel()
{
ID = int.Parse(Params["ChannelID"])
};
Dictionary <string, Diffrence> diffs = new Dictionary <string, Diffrence>();
Dictionary <string, Edgeobjects.Measure> measures;
string checksumThreshold = Instance.Configuration.Options[Consts.ConfigurationOptions.ChecksumTheshold];
double allowedChecksumThreshold = checksumThreshold == null ? 0.01 : double.Parse(checksumThreshold);
Dictionary <string, double> oltpTotals = new Dictionary <string, double>();
Dictionary <string, double> mdxTotals = new Dictionary <string, double>();
string dayCode = Convert.ToDateTime(Params["Date"]).ToString("yyyyMMdd");
#region Getting measuers from oltp
using (SqlConnection sqlCon = new SqlConnection(AppSettings.GetConnectionString(this, "OltpDB")))
{
sqlCon.Open();
StringBuilder sqlBuilder = new StringBuilder();;
measures = Edgeobjects.Measure.GetMeasures(account, channel, sqlCon, Edgeobjects.MeasureOptions.IsTarget | Edgeobjects.MeasureOptions.IsCalculated | Edgeobjects.MeasureOptions.IsBackOffice, Edgeobjects.OptionsOperator.Not);
foreach (var measure in measures)
{
if (measure.Value.Options.HasFlag(Edgeobjects.MeasureOptions.ValidationRequired))
{
sqlBuilder.AppendFormat("{1}SUM([{0}]) as [{0}]", measure.Value.OltpName, sqlBuilder.Length > 0 ? ", " : null);
}
}
sqlBuilder.Insert(0, "SELECT \n");
sqlBuilder.AppendFormat(" FROM {0}\n ", SourceTable);
sqlBuilder.Append("WHERE Account_ID=@accountID:int \nAND Day_Code=@daycode:int \nAND Channel_ID=@Channel_ID:int");
SqlCommand sqlCommand = DataManager.CreateCommand(sqlBuilder.ToString(), CommandType.Text);
sqlCommand.Parameters["@accountID"].Value = account.ID;
sqlCommand.Parameters["@daycode"].Value = dayCode;;
sqlCommand.Parameters["@Channel_ID"].Value = channel.ID;
sqlCommand.Connection = sqlCon;
using (var _reader = sqlCommand.ExecuteReader())
{
progress += 0.5 * (1 - progress);
this.ReportProgress(progress);
if (!_reader.IsClosed)
{
if (_reader.Read())
{
foreach (var measure in measures)
{
Diffrence difference = new Diffrence();
if (measure.Value.Options.HasFlag(Edgeobjects.MeasureOptions.ValidationRequired))
{
if (!_reader[measure.Value.OltpName].Equals(DBNull.Value))
{
oltpTotals.Add(measure.Value.Name, Convert.ToDouble(_reader[measure.Value.OltpName]));
}
}
}
}
}
}
}
#endregion
//bool boool=true;
//return Debug(Params, measures, oltpTotals, mdxTotals, boool);
#region Getting measures from Analysis server (MDX)
StringBuilder withMdxBuilder = new StringBuilder();
StringBuilder selectMdxBuilder = new StringBuilder();
StringBuilder fromMdxBuilder = new StringBuilder();
try
{
string cubeName = string.Empty;
bool isGDN = false;
if (!String.IsNullOrEmpty(this.Instance.Configuration.Options["GDNCubeName"]))
{
isGDN = Convert.ToBoolean(this.Instance.Configuration.Options["GDNCubeName"]);
}
cubeName = GetCubeName(Convert.ToInt32(Params["AccountID"]), isGDN);
selectMdxBuilder.Append("SELECT {{ ");
foreach (var measure in measures)
{
if (measure.Value.Options.HasFlag(Edgeobjects.MeasureOptions.ValidationRequired))
{
string cubeMeasureName = string.Empty;
//checking for GDN cube
if (isGDN)
{
cubeMeasureName = string.Format("Content {0}", measure.Value.DisplayName);
}
else
{
cubeMeasureName = measure.Value.DisplayName;
}
selectMdxBuilder.AppendFormat("[Measures].[{0}],", cubeMeasureName);
}
}
selectMdxBuilder.Remove(selectMdxBuilder.Length - 1, 1); //remove the last ','
selectMdxBuilder.Append("}}\n");
fromMdxBuilder.AppendFormat(@"On Columns ,
(
[Accounts Dim].[Accounts].[Account].&[{0}]
)On Rows
From
[{1}]
WHERE
([Channels Dim].[Channels].[Channel].&[{2}]
,[Time Dim].[Time Dim].[Day].&[{3}]
)
", account.ID, cubeName, channel.ID, Convert.ToDateTime(Params["Date"]).ToString("yyyyMMdd"));
#region Creating Command
SqlCommand command = new SqlCommand("dbo.SP_ExecuteMDX");
command.CommandType = CommandType.StoredProcedure;
SqlParameter withMDX = new SqlParameter("WithMDX", withMdxBuilder.ToString());
command.Parameters.Add(withMDX);
SqlParameter selectMDX = new SqlParameter("SelectMDX", selectMdxBuilder.ToString());
command.Parameters.Add(selectMDX);
SqlParameter fromMDX = new SqlParameter("FromMDX", fromMdxBuilder.ToString());
command.Parameters.Add(fromMDX);
#endregion
using (SqlConnection sqlCon = new SqlConnection(AppSettings.GetConnectionString(this, "OltpDB")))
{
sqlCon.Open();
command.Connection = sqlCon;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
foreach (var measure in measures)
{
if (measure.Value.Options.HasFlag(Edgeobjects.MeasureOptions.ValidationRequired))
{
mdxTotals.Add(measure.Value.Name, reader[string.Format("[Measures].[{0}]", measure.Value.DisplayName)] == DBNull.Value ? 0 : Convert.ToDouble(reader[string.Format("[Measures].[{0}]", measure.Value.DisplayName)]));
}
}
}
}
if (mdxTotals.Count == 0)
{
foreach (var measure in measures)
{
if (measure.Value.Options.HasFlag(Edgeobjects.MeasureOptions.ValidationRequired))
{
mdxTotals.Add(measure.Value.Name, 0);
}
}
}
}
return(IsEqual(Params, oltpTotals, mdxTotals, "Oltp", "Mdx"));
}
catch (Exception e)
{
Log.Write(string.Format("exception while runing MDX - {0} ", selectMdxBuilder.ToString() + " " + fromMdxBuilder.ToString()), e, LogMessageType.Error);
return(new ValidationResult()
{
ResultType = ValidationResultType.Error,
AccountID = Convert.ToInt32(Params["AccountID"]),
Message = e.Message,
TargetPeriodStart = Convert.ToDateTime(Params["Date"]),
TargetPeriodEnd = Convert.ToDateTime(Params["Date"]),
ChannelID = Convert.ToInt32(Params["ChannelID"]),
CheckType = this.Instance.Configuration.Name
});
}
#endregion
}
public string GenerateOutput(Diffrence diffrence)
{
return(GenerateHTMLElement(diffrence).Replace(Environment.NewLine, Environment.NewLine + "<br/>"));
}
protected override ValidationResult DeliveryDbCompare(DeliveryOutput deliveryOutput, Dictionary <string, double> totals, string DbConnectionStringName, string comparisonTable)
{
string dayCode = deliveryOutput.TimePeriodStart.ToString("yyyyMMdd"); // Delivery Per Day = > TargetPeriod.Start = daycode
Dictionary <string, Diffrence> diffs = new Dictionary <string, Diffrence>();
string checksumThreshold = Instance.Configuration.Options[Consts.ConfigurationOptions.ChecksumTheshold];
double allowedChecksumThreshold = checksumThreshold == null ? 0.01 : double.Parse(checksumThreshold);
ValidationResult result;
using (SqlConnection sqlCon = new SqlConnection(AppSettings.GetConnectionString(this, DbConnectionStringName)))
{
sqlCon.Open();
//Get measures need to be check
StringBuilder sqlBuilder = new StringBuilder();;
Dictionary <string, Measure> measures = Measure.GetMeasures(deliveryOutput.Account, deliveryOutput.Channel, sqlCon, MeasureOptions.IsTarget | MeasureOptions.IsCalculated | MeasureOptions.IsBackOffice, OptionsOperator.Not);
foreach (var measure in measures)
{
if (measure.Value.Options.HasFlag(MeasureOptions.ValidationRequired))
{
sqlBuilder.AppendFormat("{1}SUM([{0}]) as [{0}]", measure.Value.OltpName, sqlBuilder.Length > 0 ? ", " : null);
}
}
sqlBuilder.Insert(0, "SELECT \n");
sqlBuilder.AppendFormat(" FROM {0}\n ", comparisonTable);
sqlBuilder.Append("WHERE Account_ID=@accountID:int \nAND Day_Code=@daycode:int \nAND Channel_ID=@Channel_ID:int");
///*******Work around*******************////////
if (deliveryOutput.Account.OriginalID != null)
{
sqlBuilder.Append(" \nand Account_ID_SRC=@OriginalID:nvarchar");
}
SqlCommand sqlCommand = DataManager.CreateCommand(sqlBuilder.ToString());
sqlCommand.Parameters["@accountID"].Value = deliveryOutput.Account.ID;
sqlCommand.Parameters["@daycode"].Value = dayCode;;
sqlCommand.Parameters["@Channel_ID"].Value = deliveryOutput.Channel.ID;
///*******Work around*******************////////
if (deliveryOutput.Account.OriginalID != null)
{
sqlCommand.Parameters["@OriginalID"].Value = deliveryOutput.Account.OriginalID;
}
sqlCommand.Connection = sqlCon;
using (var _reader = sqlCommand.ExecuteReader())
{
if (!_reader.IsClosed)
{
if (_reader.Read())
{
foreach (var measure in measures)
{
Diffrence difference = new Diffrence();
if (measure.Value.Options.HasFlag(MeasureOptions.ValidationRequired))
{
if (!_reader[measure.Value.OltpName].Equals(DBNull.Value))
{
difference.Source = totals[measure.Value.Name];
difference.Target = Convert.ToDouble(_reader[measure.Value.OltpName]);
difference.diff = Math.Abs((Convert.ToDouble(_reader[measure.Value.OltpName]) - totals[measure.Value.Name]));
diffs[measure.Value.Name] = difference;
}
#region Scenario : data exists in delivery and not in DB
else if (totals[measure.Value.Name] > 0)
{
return(new ValidationResult()
{
ResultType = ValidationResultType.Error,
AccountID = deliveryOutput.Account.ID,
DeliveryID = deliveryOutput.DeliveryID,
TargetPeriodStart = deliveryOutput.TimePeriodStart,
TargetPeriodEnd = deliveryOutput.TimePeriodEnd,
Message = string.Format("Data exists in delivery but not in DB for Account {0} output {1}", deliveryOutput.Account.ID, deliveryOutput.OutputID.ToString("N")),
ChannelID = deliveryOutput.Channel.ID,
CheckType = this.Instance.Configuration.Name
});
}
else //diff is 0
{
difference.Source = 0;
difference.Target = 0;
difference.diff = 0;
diffs[measure.Value.Name] = difference;
}
#endregion
}
}
StringBuilder errors = new StringBuilder();
foreach (KeyValuePair <string, Diffrence> diff in diffs)
{
if (diff.Value.diff / diff.Value.Source > allowedChecksumThreshold)
{
errors.AppendFormat("validation Error - differences has been found for Account-{0}-channel{1}-output{2}-measure{3} source{4} target {5}\n", deliveryOutput.Account.ID, deliveryOutput.Channel.ID, deliveryOutput.OutputID.ToString("N"), diff.Key, diff.Value.Source, diff.Value.Target); //TODO: USE
}
}
if (errors.Length > 0)
{
return(new ValidationResult()
{
ResultType = ValidationResultType.Error,
AccountID = deliveryOutput.Account.ID,
DeliveryID = deliveryOutput.DeliveryID,
TargetPeriodStart = deliveryOutput.TimePeriodStart,
TargetPeriodEnd = deliveryOutput.TimePeriodEnd,
Message = errors.ToString(),
ChannelID = deliveryOutput.Channel.ID,
CheckType = this.Instance.Configuration.Name
});
}
else
{
return(new ValidationResult()
{
ResultType = ValidationResultType.Information,
AccountID = deliveryOutput.Account.ID,
DeliveryID = deliveryOutput.DeliveryID,
TargetPeriodStart = deliveryOutput.TimePeriodStart,
TargetPeriodEnd = deliveryOutput.TimePeriodEnd,
Message = "validation Success - Account Original ID: " + deliveryOutput.Account.OriginalID,
ChannelID = deliveryOutput.Channel.ID,
CheckType = this.Instance.Configuration.Name
});
}
}
}
// If reader is closed
else
{
result = new ValidationResult()
{
ResultType = ValidationResultType.Error,
AccountID = deliveryOutput.Account.ID,
DeliveryID = deliveryOutput.DeliveryID,
TargetPeriodStart = deliveryOutput.TimePeriodStart,
TargetPeriodEnd = deliveryOutput.TimePeriodEnd,
Message = "Cannot Read Data from DB connection closed - Account Original ID: " + deliveryOutput.Account.OriginalID,
ChannelID = deliveryOutput.Channel.ID,
CheckType = this.Instance.Configuration.Name
}
};
}
//Could not find check scenario
result = new ValidationResult()
{
ResultType = ValidationResultType.Error,
AccountID = deliveryOutput.Account.ID,
DeliveryID = deliveryOutput.DeliveryID,
TargetPeriodStart = deliveryOutput.TimePeriodStart,
TargetPeriodEnd = deliveryOutput.TimePeriodEnd,
Message = "Could not find check scenario - Account Original ID: " + deliveryOutput.Account.OriginalID,
ChannelID = deliveryOutput.Channel.ID,
CheckType = this.Instance.Configuration.Name
};
}
return(result);
}
}
