/// <summary>
/// load edge types by account
/// </summary>
public static Dictionary <string, EdgeType> LoadEdgeTypes(int accountId, SqlConnection connection)
{
var edgeTypes = new Dictionary <string, EdgeType>();
try
{
using (var cmd = SqlUtility.CreateCommand("MD_EdgeType_Get", CommandType.StoredProcedure))
{
cmd.Parameters.AddWithValue("@accountID", accountId);
cmd.Connection = connection;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var type = new EdgeType
{
TypeID = int.Parse(reader["TypeID"].ToString()),
Name = reader["Name"].ToString(),
TableName = reader["TableName"].ToString(),
ClrType = Type.GetType(reader["ClrType"].ToString())
};
edgeTypes.Add(type.Name, type);
}
}
}
}
catch (Exception ex)
{
throw new Exception("Error while trying to get edge types from DB", ex);
}
return(edgeTypes);
}
private Dictionary <string, Channel> GetChannelsFromDB()
{
Dictionary <string, Channel> channels = new Dictionary <string, Channel>(StringComparer.CurrentCultureIgnoreCase);
SqlConnection connection;
connection = new SqlConnection(AppSettings.GetConnectionString(typeof(AdMetricsImportManager), "StagingDatabase"));
try
{
using (connection)
{
SqlCommand cmd = SqlUtility.CreateCommand(@"GetChannels()", System.Data.CommandType.StoredProcedure);
cmd.Connection = connection;
connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Channel channel = new Channel()
{
ID = Convert.ToInt16(reader[0]),
Name = Convert.ToString(reader[1])
};
channels.Add(channel.Name, channel);
}
}
}
//var a=channels.Where(pp=>pp.Key.ToLower()=="fff".tol
}
catch (Exception ex)
{
throw new Exception("Error while trying to get Channels from DB", ex);
}
return(channels);
}
/// <summary>
/// Load specific account or all accounts if account id = -1
/// </summary>
public static Dictionary <string, Account> LoadAccounts(int accountId, SqlConnection connection)
{
var accounts = new Dictionary <string, Account>();
try
{
using (var cmd = SqlUtility.CreateCommand("Account_Get", CommandType.StoredProcedure))
{
cmd.Parameters.AddWithValue("@accountID", accountId);
cmd.Connection = connection;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var account = new Account
{
ID = int.Parse(reader["ID"].ToString()),
Name = reader["Name"].ToString()
};
accounts.Add(account.Name, account);
}
}
}
}
catch (Exception ex)
{
throw new Exception("Error while trying to get accounts from DB", ex);
}
return(accounts);
}
/// <summary>
/// Load channels by account (all if account id = -1)
/// </summary>
/// <returns></returns>
public static Dictionary <string, Channel> LoadChannels(SqlConnection connection)
{
var channels = new Dictionary <string, Channel>(StringComparer.CurrentCultureIgnoreCase);
try
{
using (var cmd = SqlUtility.CreateCommand("Channel_Get", CommandType.StoredProcedure))
{
cmd.Connection = connection;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var channel = new Channel
{
ID = int.Parse(reader["ID"].ToString()),
Name = reader["Name"].ToString()
};
channels.Add(channel.Name, channel);
}
}
}
}
catch (Exception ex)
{
throw new Exception("Error while trying to get Channels from DB", ex);
}
return(channels);
}
/// <summary>
/// Search for best match table in staging DB according to delivery table structure
/// </summary>
/// <returns></returns>
public string FindStagingTable()
{
// nothing to do if there is no metrics table (import objects only)
if (String.IsNullOrEmpty(TableName))
{
return(null);
}
using (var cmd = SqlUtility.CreateCommand(SP_FIND_BEST_MATCH_METRICS_TABLE, CommandType.StoredProcedure))
{
cmd.Connection = _deliverySqlConnection;
var tableNameParam = new SqlParameter {
ParameterName = "@BestMatch", Size = 1000, Direction = ParameterDirection.Output
};
cmd.Parameters.Add(tableNameParam);
cmd.Parameters.AddWithValue("@InputTable", TableName);
cmd.ExecuteNonQuery();
if (tableNameParam.Value == null || String.IsNullOrEmpty(tableNameParam.Value.ToString()))
{
throw new Exception(String.Format("No staging table was found for delivery table {0}", TableName));
}
return(tableNameParam.Value.ToString());
}
}
public static DeliveryOutput[] GetOutputsByTargetPeriod(int channelID, int accountID, DateTime start, DateTime end)
{
List <DeliveryOutput> outputs = new List <DeliveryOutput>();
List <string> outputsIds = new List <string>();
using (var client = DeliveryDBClient.Connect())
{
using (SqlCommand cmd = SqlUtility.CreateCommand("Output_GetByTargetPeriod(@channelID:Int,@accountID:Int,@targetPeriodStart:DateTime2,@targetPeriodEnd:DateTime2)", System.Data.CommandType.StoredProcedure))
{
cmd.Connection = client;
cmd.Parameters["@channelID"].Value = channelID;
cmd.Parameters["@accountID"].Value = accountID;
cmd.Parameters["@targetPeriodStart"].Value = start;
cmd.Parameters["@targetPeriodEnd"].Value = end;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
//deliveriesId.Add(Get(Guid.Parse(reader.GetString(0))));
outputsIds.Add(reader.GetString(0));
}
}
}
foreach (string id in outputsIds)
{
outputs.Add(GetOutput(Guid.Parse(id)));
}
return(outputs.ToArray());
}
}
/// <summary>
/// Load measures by account
/// </summary>
public static Dictionary <string, Measure> LoadMeasures(int accountId, SqlConnection connection)
{
var measures = new Dictionary <string, Measure>();
try
{
using (var cmd = SqlUtility.CreateCommand("MD_Measure_Get", CommandType.StoredProcedure))
{
cmd.Parameters.AddWithValue("@accountID", accountId);
cmd.Connection = connection;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var measure = new Measure
{
ID = int.Parse(reader["ID"].ToString()),
Name = reader["Name"].ToString(),
DataType = reader["DataType"] != DBNull.Value ? (MeasureDataType)int.Parse(reader["DataType"].ToString()) : MeasureDataType.Number,
//InheritedByDefault = reader["InheritedByDefault"] != DBNull.Value && bool.Parse(reader["InheritedByDefault"].ToString()),
Options = reader["Options"] != DBNull.Value ? (MeasureOptions)int.Parse(reader["Options"].ToString()) : MeasureOptions.None,
};
measures.Add(measure.Name, measure);
}
}
}
}
catch (Exception ex)
{
throw new Exception("Error while trying to get measures from DB", ex);
}
return(measures);
}
private bool CheckFileConflict(Dictionary <string, string> fileInfo)
{
string fileSignature = string.Format("{0}-{1}-{2}", fileInfo["Name"], fileInfo["ModifyDate"], fileInfo["Size"]);
SqlConnection connection;
try
{
connection = new SqlConnection(AppSettings.GetConnectionString(typeof(FtpImporterPreInitializerService), "DeliveryDB"));
using (connection)
{
SqlCommand cmd = SqlUtility.CreateCommand(@"DeliveryFile_GetBySignature()", System.Data.CommandType.StoredProcedure);
SqlParameter fileSig = new SqlParameter("signature", fileSignature);
cmd.Parameters.Add(fileSig);
cmd.Connection = connection;
connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
Core.Utilities.Log.Write(string.Format("File with same signature already exists in DB,File Signature: {0}", fileSignature), LogMessageType.Warning);
return(true);
}
}
}
}
catch (Exception ex)
{
throw new Exception("Error while trying to get files signature from DB", ex);
}
return(false);
}
private static string GetAuthFromDB(string mccEmail, out string mccPassword)
{
var auth = "";
mccPassword = "";
var connection = new SqlConnection(AppSettings.GetConnectionString(typeof(AdwordsUtill), "MCC_Auth"));
try
{
using (connection)
{
var cmd = SqlUtility.CreateCommand(@"GetGoogleMccAuth(@MccEmail:Nvarchar)", System.Data.CommandType.StoredProcedure);
cmd.Connection = connection;
connection.Open();
cmd.Parameters["@MccEmail"].Value = mccEmail;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
mccPassword = Encryptor.Dec(reader[0].ToString());
auth = reader[1].ToString();
}
}
}
}
catch (Exception ex)
{
throw new Exception("Error while trying to get auth key from DB", ex);
}
return(auth);
}
// ==============================================
#endregion
private Dictionary <string, Account> GetAccountsFromDB(int currentAccountId)
{
Dictionary <string, Account> accounts = new Dictionary <string, Account>();
SqlConnection connection;
connection = new SqlConnection(AppSettings.GetConnectionString(typeof(AdMetricsImportManager), "StagingDatabase"));
try
{
using (connection)
{
SqlCommand cmd = SqlUtility.CreateCommand(@"GetAccountFamily_ById(@CurrentAccountId:int)", System.Data.CommandType.StoredProcedure);
cmd.Connection = connection;
connection.Open();
cmd.Parameters["@CurrentAccountId"].Value = currentAccountId;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Account account = new Account()
{
ID = Convert.ToInt32(reader[1]),
Name = Convert.ToString(reader[0])
};
accounts.Add(account.Name, account);
}
}
}
}
catch (Exception ex)
{
throw new Exception("Error while trying to get accounts from DB", ex);
}
return(accounts);
}
/// <summary>
/// Call DB stored procedure to create all delivery object tables by table prefix
/// currently all delivery objects tables are created for all accounts even if they are not in use
/// </summary>
/// <param name="tablePrefix"></param>
public void CreateDeliveryObjectTables(string tablePrefix)
{
using (var cmd = SqlUtility.CreateCommand("MD_ObjectTables_Create", CommandType.StoredProcedure))
{
cmd.Parameters.AddWithValue("@TablePrefix", string.Format("{0}_", tablePrefix));
cmd.Connection = _objectsSqlConnection;
cmd.CommandTimeout = 80; //DEFAULT IS 30 AND SOMTIME NOT ENOUGH WHEN RUNING CUBE
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Set relationships between edge fields and edge types (many-2-many) according to account id
/// </summary>
public static void SetEdgeTypeEdgeFieldRelation(int accountid, Dictionary <string, EdgeType> edgeTypes, List <EdgeField> edgeFields, SqlConnection connection)
{
try
{
using (var cmd = SqlUtility.CreateCommand("MD_EdgeTypeField_Get", CommandType.StoredProcedure))
{
cmd.Parameters.AddWithValue("@accountID", accountid);
cmd.Connection = connection;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
// find parent edge type nad edge field
var parentTypeId = int.Parse(reader["ParentTypeID"].ToString());
var fieldtId = int.Parse(reader["FieldID"].ToString());
var parentType = edgeTypes.Values.FirstOrDefault(x => x.TypeID == parentTypeId);
if (parentType == null)
{
throw new ConfigurationErrorsException(String.Format("Configuration error: Unknown parent edge type {0} while loading edge type fields", parentTypeId));
}
var field = edgeFields.FirstOrDefault(x => x.FieldID == fieldtId);
if (field == null)
{
throw new ConfigurationErrorsException(String.Format("Configuration error: Unknown edge field {0} while loading edge type fields", fieldtId));
}
var typeField = new EdgeTypeField
{
ColumnName = reader["ColumnName"].ToString(),
IsIdentity = bool.Parse(reader["IsIdentity"].ToString()),
Field = field
};
// add edge field to parent edge type
if (!parentType.Fields.Contains(typeField))
{
parentType.Fields.Add(typeField);
}
else
{
throw new ConfigurationErrorsException(String.Format("Configuration error: Field {0} already exists in parent edge type {1}", field.Name, parentType.Name));
}
}
}
}
}
catch (Exception ex)
{
throw new Exception("Error while trying to get extra fields from DB", ex);
}
}
public void CreateCommand_Simple_Success()
{
IDbCommand cmd = SqlUtility.CreateCommand(new Mocks.MockDBConnection(new Mocks.ExecuteReaderNoAction()), "TestSproc", new Dictionary <string, object>()
{
{ "Arg1", 25 },
{ "Arg2", "string" },
{ "Arg3", false }
});
Assert.That(cmd.CommandText == "TestSproc");
Assert.That(cmd.CommandType == CommandType.StoredProcedure);
Assert.That(cmd.Parameters.Count == 3);
}
protected void CleanDelivery()
{
// delete previous delivery tables
using (var deliveryConnection = new SqlConnection(AppSettings.GetConnectionString(typeof(MetricsDeliveryManager), Consts.ConnectionStrings.Deliveries)))
{
var cmd = SqlUtility.CreateCommand("Drop_Delivery_tables", CommandType.StoredProcedure);
cmd.Parameters.AddWithValue("@TableInitial", String.Format("{0}_", ACCOUNT_ID));
cmd.Connection = deliveryConnection;
deliveryConnection.Open();
cmd.ExecuteNonQuery();
cmd = new SqlCommand("DELETE [dbo].[MD_MetricsMetadata]", deliveryConnection);
cmd.ExecuteNonQuery();
}
}
protected override void OnStage(Delivery delivery, int pass)
{
CurrentDelivery = delivery;
if (pass == STAGING_PASS_OBJECTS)
{
// IDENTITYMANAGER: insert new EdgeObjects and update existing from Delivery to EdgeObject DB by IdentityStatus
Identify(2, delivery);
}
else if (pass == STAGING_PASS_METRICS)
{
// TABLEMANAGER: insert delivery metrics into staging
if (CurrentDelivery.Parameters[Consts.DeliveryHistoryParameters.StagingMetricsTableName] != null &&
CurrentDelivery.Parameters[Consts.DeliveryHistoryParameters.DeliveryMetricsTableName] != null)
{
var deliveryTable = CurrentDelivery.Parameters[Consts.DeliveryHistoryParameters.DeliveryMetricsTableName].ToString();
var stagingTable = CurrentDelivery.Parameters[Consts.DeliveryHistoryParameters.StagingMetricsTableName].ToString();
if (!stagingTable.ToLower().StartsWith("[dbo]"))
{
stagingTable = String.Format("[dbo].[{0}]", stagingTable);
}
// for Debug only - execute Edge Viewer in .NET
if (Options.IdentityInDebug)
{
EdgeViewer.StageMetrics(delivery.Account.ID, deliveryTable, stagingTable, _deliverySqlConnection);
}
// to be executed in real scenario - execute SQL CLR (DB stored procedure that executes .NET code of Edge Viewer)
else
{
using (var cmd = SqlUtility.CreateCommand("EdgeObjects.dbo.MetricsStaging", CommandType.StoredProcedure))
{
cmd.Connection = _objectsSqlConnection;
cmd.CommandTimeout = 300;
var stageSqlParam = new SqlParameter {
ParameterName = "@stagingSql", Size = 4000, Direction = ParameterDirection.Output
};
cmd.Parameters.Add(stageSqlParam);
cmd.Parameters.AddWithValue("@accoutId", delivery.Account.ID);
cmd.Parameters.AddWithValue("@deliveryTable", deliveryTable);
cmd.Parameters.AddWithValue("@stagingTable", stagingTable);
cmd.ExecuteNonQuery();
}
}
}
}
}
/// <summary>
/// Load edge fields by account + set edge type for each field
/// </summary>
public static List <EdgeField> LoadEdgeFields(int accountId, Dictionary <string, EdgeType> edgeTypes, SqlConnection connection)
{
var edgeFields = new List <EdgeField>();
try
{
using (var cmd = SqlUtility.CreateCommand("MD_EdgeField_Get", CommandType.StoredProcedure))
{
cmd.Parameters.AddWithValue("@accountID", accountId);
cmd.Connection = connection;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
EdgeField field = null;
Type type = Type.GetType(reader["FieldType"].ToString());
if (type == null)
{
field = new SystemField();
}
else
{
field = Activator.CreateInstance(type) as EdgeField;
}
if (field != null)
{
field.FieldID = int.Parse(reader["FieldID"].ToString());
field.Name = reader["Name"].ToString();
field.DisplayName = reader["DisplayName"].ToString();
field.FieldEdgeType = edgeTypes.Values.FirstOrDefault(x => x.TypeID == int.Parse(reader["FieldTypeID"].ToString()));
edgeFields.Add(field);
}
}
}
}
}
catch (Exception ex)
{
throw new Exception("Error while trying to get extra fields from DB", ex);
}
return(edgeFields);
}
protected override void OnRollbackDelivery(Delivery delivery, int pass)
{
string guid = delivery.DeliveryID.ToString("N");
_rollbackCommand = _rollbackCommand ?? SqlUtility.CreateCommand(Options.SqlRollbackCommand, CommandType.StoredProcedure);
_rollbackCommand.Connection = _deliverySqlConnection;
_rollbackCommand.Transaction = _rollbackTransaction;
_rollbackCommand.Parameters["@DeliveryID"].Value = guid;
_rollbackCommand.Parameters["@StagingTable"].Value = CurrentDelivery.Parameters[Consts.DeliveryHistoryParameters.StagingMetricsTableName];
_rollbackCommand.ExecuteNonQuery();
// This is redundant (SP already does this) but to sync our objects in memory we do it here also
foreach (DeliveryOutput output in delivery.Outputs)
{
output.Status = DeliveryOutputStatus.RolledBack;
}
}
public string FindStagingTable(string metricsTableName)
{
string stagingTableName;
using (SqlCommand command = SqlUtility.CreateCommand(AppSettings.Get(this, "SP_FindStagingTable"), CommandType.StoredProcedure))
{
command.Parameters["@templateTable"].Value = metricsTableName;
command.Parameters["@templateDB"].Value = "";//TODO: FROM WHERE DO i TAKE THIS TABLE?
command.Parameters["@searchDB"].Value = "";//TODO: FROM WHERE DO i TAKE THIS TABLE?
using (SqlDataReader reader = command.ExecuteReader())
{
if (!reader.Read())
throw new Exception("No staging table Found");
else
stagingTableName = reader["TABLE_NAME"].ToString();
}
}
return stagingTableName;
}
private static void SaveAuthTokenToDB(string mccEmail, string authToken)
{
var connection = new SqlConnection(AppSettings.GetConnectionString(typeof(AdwordsUtill), "MCC_Auth"));
try
{
using (connection)
{
var cmd = SqlUtility.CreateCommand(@"SetGoogleMccAuth(@MccEmail:Nvarchar,@AuthToken:Nvarchar)", System.Data.CommandType.StoredProcedure);
cmd.Connection = connection;
connection.Open();
cmd.Parameters["@MccEmail"].Value = mccEmail;
cmd.Parameters["@AuthToken"].Value = authToken;
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw new Exception("Error while trying to set a new Auth key", ex);
}
}
/*=========================*/
/// <summary>
/// Help function to execute Identity stages:
/// stage 1 - identify delivery object by existing Edge Objects
/// stage 2 - insert new and update odified Edge Objects by delivery objects
/// DEBUG (Options): execute Identity Manager .NET code
/// REAL: execute SQl CLR wich executes Identity Manager .NET code in DB
/// </summary>
protected void Identify(int identityStage, Delivery delivery)
{
// for Debug only - execute Identity Manager in .NET
if (Options.IdentityInDebug)
{
var identityManager = new IdentityManager(_objectsSqlConnection)
{
TablePrefix = delivery.Parameters[Consts.DeliveryHistoryParameters.TablePerfix].ToString(),
TransformTimestamp = DateTime.Parse(delivery.Parameters[Consts.DeliveryHistoryParameters.TransformTimestamp].ToString()),
AccountId = delivery.Account.ID,
ConfigXml = Options.IdentityConfig
};
if (identityStage == 1)
{
identityManager.IdentifyDeliveryObjects();
}
else if (identityStage == 2)
{
identityManager.UpdateEdgeObjects();
}
}
// to be executed in real scenario - execute SQL CLR (DB stored procedure that executes .NET code of Identity Manager)
else
{
var spName = identityStage == 1 ? "EdgeObjects.dbo.IdentityI" : "EdgeObjects.dbo.IdentityII";
using (var cmd = SqlUtility.CreateCommand(spName, CommandType.StoredProcedure))
{
cmd.Connection = _objectsSqlConnection;
cmd.CommandTimeout = 300;
cmd.Parameters.AddWithValue("@accoutId", delivery.Account.ID);
cmd.Parameters.AddWithValue("@deliveryTablePrefix", delivery.Parameters[Consts.DeliveryHistoryParameters.TablePerfix].ToString());
cmd.Parameters.AddWithValue("@identity1Timestamp", DateTime.Parse(delivery.Parameters[Consts.DeliveryHistoryParameters.TransformTimestamp].ToString()));
cmd.Parameters.AddWithValue("@identityConfig", Options.IdentityConfig);
cmd.ExecuteNonQuery();
}
}
}
internal static DeliveryOutput[] GetOutputsBySignature(string signature, Guid exclude)
{
List <DeliveryOutput> outputs = new List <DeliveryOutput>();
using (var client = DeliveryDBClient.Connect())
{
// Select deliveries that match a signature but none of the guids in 'exclude'
using (SqlCommand cmd = SqlUtility.CreateCommand("OutPut_GetBySignature(@signature:NvarChar,@exclude:NvarChar)", System.Data.CommandType.StoredProcedure))
{
cmd.Connection = client;
cmd.Parameters["@signature"].Value = signature;
cmd.Parameters["@exclude"].Value = exclude.ToString("N");
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
outputs.Add(GetOutput(Guid.Parse(reader.GetString(0))));
}
}
}
}
return(outputs.ToArray());
}
protected override void OnRollbackOutput(DeliveryOutput output, int pass)
{
string guid = output.OutputID.ToString("N");
if (output.Status == DeliveryOutputStatus.Staged)
{
_rollbackCommand = _rollbackCommand ?? SqlUtility.CreateCommand(Options.SqlRollbackCommand, CommandType.StoredProcedure);
_rollbackCommand.Connection = _deliverySqlConnection;
_rollbackCommand.Transaction = _rollbackTransaction;
_rollbackCommand.Parameters["@DeliveryOutputID"].Value = guid;
_rollbackCommand.Parameters["@StagingTable"].Value = output.Parameters[Consts.DeliveryHistoryParameters.StagingMetricsTableName];
_rollbackCommand.ExecuteNonQuery();
}
else if (output.Status == DeliveryOutputStatus.Committed)
{
output.Status = DeliveryOutputStatus.RollbackPending;
}
else
{
throw new InvalidOperationException("Delivery output cannot be rolled back because it is neither staged nor committed.");
}
}
internal static Guid SaveDelivery(Delivery delivery)
{
if (!delivery.FullyLoaded)
{
throw new InvalidOperationException("Cannot save a delivery that was loaded with deep = false.");
}
using (var client = DeliveryDBClient.Connect())
{
SqlTransaction transaction = client.BeginTransaction();
Guid guid = delivery.DeliveryID;
if (guid != Guid.Empty)
{
#region [Delete]
// ..................
SqlCommand cmd = SqlUtility.CreateCommand((String.Format("{0}(@deliveryID:Char)", AppSettings.Get(typeof(DeliveryDB), Const.SP_DeliveryDelete))), System.Data.CommandType.StoredProcedure);
cmd.Connection = client;
cmd.Transaction = transaction;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters["@deliveryID"].Value = delivery.DeliveryID.ToString("N");
cmd.ExecuteNonQuery();
// ..................
#endregion
#region Delivery
// ..................
if (delivery.DateCreated == DateTime.MinValue)
{
delivery.DateCreated = DateTime.Now;
}
delivery.DateModified = DateTime.Now;
cmd = SqlUtility.CreateCommand(@"
INSERT INTO [Delivery] (
[DeliveryID],
[Account_ID],
[Account_OriginalID],
[ChannelID],
[DateCreated],
[DateModified],
[Description],
[FileDirectory],
[TimePeriodDefinition],
[TimePeriodStart],
[TimePeriodEnd]
)
VALUES (
@deliveryID:Char,
@account_ID:Int,
@account_OriginalID:NVarChar,
@channelID:Int,
@dateCreated:DateTime,
@dateModified:DateTime,
@description:NVarChar,
@fileDirectory:NVarChar,
@timePeriodDefinition:NVarChar,
@timePeriodStart:DateTime2,
@timePeriodEnd:DateTime2
)
" , System.Data.CommandType.Text);
cmd.Connection = client;
cmd.Transaction = transaction;
cmd.Parameters["@deliveryID"].Value = delivery.DeliveryID.ToString("N");
cmd.Parameters["@account_ID"].Value = delivery.Account != null ? delivery.Account.ID : -1;
cmd.Parameters["@channelID"].Value = delivery.Channel != null ? delivery.Channel.ID : -1;;
cmd.Parameters["@dateCreated"].Value = delivery.DateCreated;
cmd.Parameters["@dateModified"].Value = delivery.DateModified;
cmd.Parameters["@description"].Value = delivery.Description == null ? (object)DBNull.Value : delivery.Description;
cmd.Parameters["@fileDirectory"].Value = delivery.FileDirectory;
cmd.Parameters["@timePeriodDefinition"].Value = delivery.TimePeriodDefinition.ToString();
cmd.Parameters["@timePeriodStart"].Value = delivery.TimePeriodStart;
cmd.Parameters["@timePeriodEnd"].Value = delivery.TimePeriodEnd;
cmd.ExecuteNonQuery();
// ..................
#endregion
#region DeliveryParameters
// ..................
foreach (KeyValuePair <string, object> param in delivery.Parameters)
{
cmd = SqlUtility.CreateCommand(@"
INSERT INTO [DeliveryParameters](
[DeliveryID],
[Key],
[Value]
)
VALUES (
@deliveryID:Char,
@key:NVarChar,
@value:NVarChar
)
" , System.Data.CommandType.Text);
cmd.Connection = client;
cmd.Transaction = transaction;
cmd.Parameters["@deliveryID"].Value = delivery.DeliveryID.ToString("N");
cmd.Parameters["@key"].Value = param.Key;
cmd.Parameters["@value"].Value = Serialize(param.Value);
cmd.ExecuteNonQuery();
}
// ..................
#endregion
#region DeliveryFile
// ..................
foreach (DeliveryFile file in delivery.Files)
{
if (file.FileID == Guid.Empty)
{
file.FileID = Guid.NewGuid();
}
if (file.DateCreated == DateTime.MinValue)
{
file.DateCreated = DateTime.Now;
}
file.DateModified = DateTime.Now;
cmd = SqlUtility.CreateCommand(@"
INSERT INTO [DeliveryFile] (
[DeliveryID],
[FileID],
[Name],
[DateCreated],
[DateModified],
[FileCompression],
[SourceUrl],
[Location],
[Status],
[FileSignature]
)
VALUES (
@deliveryID:Char,
@fileID:Char,
@name:NVarChar,
@dateCreated:DateTime,
@dateModified:DateTime,
@fileCompression:Int,
@sourceUrl:NVarChar,
@location:NVarChar,
@status:Int,
@fileSignature:NVarChar
)" , System.Data.CommandType.Text);
cmd.Connection = client;
cmd.Transaction = transaction;
cmd.Parameters["@deliveryID"].Value = file.Delivery.DeliveryID.ToString("N");
cmd.Parameters["@fileID"].Value = file.FileID.ToString("N");
cmd.Parameters["@name"].Value = file.Name;
cmd.Parameters["@dateCreated"].Value = file.DateCreated;
cmd.Parameters["@dateModified"].Value = file.DateModified;
cmd.Parameters["@fileCompression"].Value = file.FileCompression;
cmd.Parameters["@sourceUrl"].Value = file.SourceUrl == null ? (object)DBNull.Value : file.SourceUrl;
cmd.Parameters["@location"].Value = file.Location == null ? (object)DBNull.Value : file.Location;
cmd.Parameters["@status"].Value = file.Status;
cmd.Parameters["@fileSignature"].Value = file.FileSignature == null ? (object)DBNull.Value : file.FileSignature;
cmd.ExecuteNonQuery();
}
// ..................
#endregion
#region DeliveryFileParameters
// ..................
foreach (DeliveryFile file in delivery.Files)
{
foreach (KeyValuePair <string, object> param in file.Parameters)
{
cmd = SqlUtility.CreateCommand(@"
INSERT INTO [DeliveryFileParameters] (
[DeliveryID],
[Name],
[Key],
[Value]
)
VALUES (
@deliveryID:Char,
@name:NVarChar,
@key:NVarChar,
@value:NVarChar
)" , System.Data.CommandType.Text);
cmd.Connection = client;
cmd.Transaction = transaction;
cmd.Parameters["@deliveryID"].Value = delivery.DeliveryID.ToString("N");
cmd.Parameters["@name"].Value = file.Name;
cmd.Parameters["@key"].Value = param.Key;
cmd.Parameters["@value"].Value = Serialize(param.Value);
cmd.ExecuteNonQuery();
}
}
// ..................
#endregion
#region DeliveryOutput
// ..................
foreach (DeliveryOutput output in delivery.Outputs)
{
if (output.OutputID == Guid.Empty)
{
output.OutputID = Guid.NewGuid();
}
if (output.DateCreated == DateTime.MinValue)
{
output.DateCreated = DateTime.Now;
}
output.DateModified = DateTime.Now;
cmd = SqlUtility.CreateCommand(@"
INSERT INTO [DeliveryOutput] (
[DeliveryID],
[OutputID],
[AccountID],
[AccountOriginalID],
[ChannelID],
[Signature],
[Status] ,
[TimePeriodStart],
[TimePeriodEnd],
[DateCreated],
[DateModified],
[PipelineInstanceID]
)
VALUES (
@deliveryID:Char,
@outputID:Char,
@accountID:Int,
@accountOriginalID:NVarChar,
@channelID:Int,
@signature:NVarChar,
@status:Int,
@timePeriodStart:DateTime2,
@timePeriodEnd:DateTime2,
@dateCreated:DateTime,
@dateModified:DateTime,
@pipelineInstanceID:BigInt
)" , System.Data.CommandType.Text);
cmd.Connection = client;
cmd.Transaction = transaction;
cmd.Parameters["@deliveryID"].Value = output.Delivery.DeliveryID.ToString("N");
cmd.Parameters["@outputID"].Value = output.OutputID.ToString("N");
cmd.Parameters["@accountID"].Value = output.Account != null ? output.Account.ID : -1;
cmd.Parameters["@accountOriginalID"].Value = output.Account != null ? output.Account.OriginalID != null ? output.Account.OriginalID : (object)DBNull.Value : (object)DBNull.Value;
cmd.Parameters["@channelID"].Value = output.Channel != null ? output.Channel.ID : -1;
cmd.Parameters["@signature"].Value = output.Signature;
cmd.Parameters["@status"].Value = output.Status;
cmd.Parameters["@timePeriodStart"].Value = output.TimePeriodStart;
cmd.Parameters["@timePeriodEnd"].Value = output.TimePeriodEnd;
cmd.Parameters["@dateCreated"].Value = output.DateCreated;
cmd.Parameters["@dateModified"].Value = output.DateModified;
cmd.Parameters["@pipelineInstanceID"].Value = output.PipelineInstanceID.HasValue ? output.PipelineInstanceID.Value : (object)DBNull.Value;
cmd.ExecuteNonQuery();
}
// ..................
#endregion
#region DeliveryOutputParameters
// ..................
foreach (DeliveryOutput output in delivery.Outputs)
{
foreach (KeyValuePair <string, object> param in output.Parameters)
{
cmd = SqlUtility.CreateCommand(@"
INSERT INTO [DeliveryOutputParameters] (
[DeliveryID],
[OutputID],
[Key],
[Value]
)
VALUES (
@deliveryID:Char,
@outputID:NVarChar,
@key:NVarChar,
@value:NVarChar
)" , System.Data.CommandType.Text);
cmd.Connection = client;
cmd.Transaction = transaction;
cmd.Parameters["@deliveryID"].Value = delivery.DeliveryID.ToString("N");
cmd.Parameters["@outputID"].Value = output.OutputID.ToString("N");
cmd.Parameters["@key"].Value = param.Key;
cmd.Parameters["@value"].Value = Serialize(param.Value);
cmd.ExecuteNonQuery();
}
}
// ..................
#endregion
#region checkSum
foreach (var output in delivery.Outputs)
{
foreach (KeyValuePair <string, double> sum in output.Checksum)
{
cmd = SqlUtility.CreateCommand(@"
INSERT INTO [DeliveryOutputChecksum] (
[DeliveryID],
[OutputID],
[MeasureName],
[Total]
)
VALUES (
@deliveryID:Char,
@outputid:Char,
@measureName:NVarChar,
@total:decimal
)" , System.Data.CommandType.Text);
cmd.Connection = client;
cmd.Transaction = transaction;
cmd.Parameters["@deliveryID"].Value = delivery.DeliveryID.ToString("N");
cmd.Parameters["@outputid"].Value = output.OutputID.ToString("N");
cmd.Parameters["@measureName"].Value = sum.Key;
cmd.Parameters["@total"].Value = sum.Value;
cmd.ExecuteNonQuery();
}
}
#endregion
transaction.Commit();
}
else
{
throw new NotSupportedException("In Pipeline 2.9, you cannot save a Delivery without first giving it a GUID.");
}
return(guid);
}
}
internal static Delivery GetDelivery(Guid deliveryID, bool deep = true, SqlConnection connection = null)
{
Delivery delivery = null;
bool innerConnection = connection == null;
if (innerConnection)
{
connection = DeliveryDBClient.Connect();
}
try
{
SqlCommand cmd = SqlUtility.CreateCommand(String.Format("{0}(@deliveryID:Char, @deep:bit)", AppSettings.Get(typeof(DeliveryDB), Const.SP_DeliveryGet)), System.Data.CommandType.StoredProcedure);
cmd.Connection = connection;
cmd.Parameters["@deliveryID"].Value = deliveryID.ToString("N");
cmd.Parameters["@deep"].Value = deep;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
#region Delivery
// ..................
delivery = new Delivery(reader.Convert <string, Guid>("DeliveryID", s => Guid.Parse(s)))
{
FullyLoaded = deep,
Account = reader.Convert <int?, Account>("Account_ID", id => id.HasValue ? new Account()
{
ID = id.Value
} : null),
Channel = reader.Convert <int?, Channel>("ChannelID", id => id.HasValue ? new Channel()
{
ID = id.Value
} : null),
DateCreated = reader.Get <DateTime>("DateCreated"),
DateModified = reader.Get <DateTime>("DateModified"),
Description = reader.Get <string>("Description"),
FileDirectory = reader.Get <string>("FileDirectory"),
};
delivery.InternalSetTimePeriod(
DateTimeRange.Parse(reader.Get <string>("TimePeriodDefinition")),
reader.Get <DateTime>("TimePeriodStart"),
reader.Get <DateTime>("TimePeriodEnd")
);
// ..................
#endregion
if (deep)
{
#region DeliveryParameters
// ..................
if (reader.NextResult())
{
while (reader.Read())
{
delivery.Parameters.Add(reader.Get <string>("Key"), DeserializeJson(reader.Get <string>("Value")));
}
}
// ..................
#endregion
#region DeliveryFile
// ..................
if (reader.NextResult())
{
while (reader.Read())
{
DeliveryFile deliveryFile = new DeliveryFile();
deliveryFile.FileID = reader.Convert <string, Guid>("DeliveryID", s => Guid.Parse(s));
deliveryFile.FileCompression = reader.Get <FileCompression>("FileCompression");
deliveryFile.SourceUrl = reader.Get <string>("SourceUrl");
deliveryFile.Name = reader.Get <string>("Name");
deliveryFile.Location = reader.Get <string>("Location");
deliveryFile.Status = reader.Get <DeliveryFileStatus>("Status");
deliveryFile.FileSignature = reader.Get <string>("FileSignature");
delivery.Files.Add(deliveryFile);
}
}
// ..................
#endregion
#region DeliveryFileParameters
// ..................
if (reader.NextResult())
{
while (reader.Read())
{
DeliveryFile deliveryFile = delivery.Files[reader.Get <string>("Name")];
deliveryFile.Parameters.Add(reader.Get <string>("Key"), DeserializeJson(reader.Get <string>("Value")));
}
}
// ..................
#endregion
#region DeliveryOutput
if (reader.NextResult())
{
while (reader.Read())
{
var deliveryOutput = new DeliveryOutput()
{
OutputID = reader.Convert <string, Guid>("OutputID", s => Guid.Parse(s)),
Account = reader.Convert <int?, Account>("AccountID", id => id.HasValue ? new Account()
{
ID = id.Value
} : null),
Channel = reader.Convert <int?, Channel>("ChannelID", id => id.HasValue ? new Channel()
{
ID = id.Value
} : null),
Signature = reader.Get <string>("Signature"),
Status = reader.Get <DeliveryOutputStatus>("Status"),
TimePeriodStart = reader.Get <DateTime>("TimePeriodStart"),
TimePeriodEnd = reader.Get <DateTime>("TimePeriodEnd"),
PipelineInstanceID = reader.Get <Guid>("PipelineInstanceID")
};
delivery.Outputs.Add(deliveryOutput);
}
}
#endregion
#region DeliveryOutputParameters
// ..................
if (reader.NextResult())
{
while (reader.Read())
{
DeliveryOutput deliveryOutput = delivery.Outputs[reader.Get <string>("OutputID")];
deliveryOutput.Parameters.Add(reader.Get <string>("Key"), DeserializeJson(reader.Get <string>("Value")));
}
}
// ..................
#endregion
#region checksum
// ..................
if (reader.NextResult())
{
while (reader.Read())
{
var deliveryOutput = delivery.Outputs[reader.Get <string>("OutputID")];
if (deliveryOutput.Checksum == null)
{
deliveryOutput.Checksum = new Dictionary <string, double>();
}
deliveryOutput.Checksum.Add(reader["MeasureName"].ToString(), Convert.ToDouble(reader["Total"]));
}
}
// ..................
#endregion
}
}
}
}
finally
{
if (innerConnection)
{
connection.Dispose();
}
}
return(delivery);
}
protected override ServiceOutcome DoPipelineWork()
{
// takes deliveries or outputs to rollback from Config
var deliveriesIds = Configuration.Deliveries.Split(',');
var ouputsIds = Configuration.Deliveries.Split(',');
if (deliveriesIds.Length == 0 && ouputsIds.Length == 0)
{
throw new Exception("Option RollbackDeliveries or RollbackOutputs must be defined");
}
// start Rollback
using (var conn = new SqlConnection(AppSettings.GetConnectionString(this, Consts.ConnectionStrings.Staging)))
{
conn.Open();
var tran = conn.BeginTransaction();
// rollback deliveries
if (deliveriesIds.Length > 0)
{
var cmd = SqlUtility.CreateCommand(Configuration.RollbackDeliveriesStoredProc, System.Data.CommandType.StoredProcedure);
cmd.Connection = conn;
cmd.Transaction = tran;
foreach (var deliveryID in deliveriesIds)
{
cmd.Parameters["@DeliveryID"].Value = deliveryID;
cmd.Parameters["@TableName"].Value = Configuration.TableName;
cmd.ExecuteNonQuery();
}
}
// rollback outputs
if (ouputsIds.Length > 0)
{
var cmd = SqlUtility.CreateCommand(Configuration.RollbackOutputsStoredProc, System.Data.CommandType.StoredProcedure);
cmd.Connection = conn;
cmd.Transaction = tran;
foreach (var outputID in ouputsIds)
{
cmd.Parameters["@DeliveryOutputID"].Value = outputID;
cmd.Parameters["@TableName"].Value = Configuration.TableName;
cmd.ExecuteNonQuery();
}
}
// commit transaction
tran.Commit();
}
return(ServiceOutcome.Success);
//for new db
/*
* string checksumThreshold = Configuration.Parameters.Get<T>(Consts.ConfigurationOptions.ChecksumTheshold];
* MetricsImportManagerOptions options = new MetricsImportManagerOptions()
* {
* SqlTransformCommand = Configuration.Parameters.Get<T>(Consts.AppSettings.SqlTransformCommand],
* SqlStageCommand = Configuration.Parameters.Get<T>(Consts.AppSettings.SqlStageCommand],
* SqlRollbackCommand = Configuration.Parameters.Get<T>(Consts.AppSettings.SqlRollbackCommand],
* ChecksumThreshold = checksumThreshold == null ? 0.01 : double.Parse(checksumThreshold)
* };
* string checksumThreshold = Configuration.Parameters.Get<T>(Consts.ConfigurationOptions.ChecksumTheshold];
* string importManagerTypeName = Configuration.GetOption(Consts.ConfigurationOptions.ImportManagerType);
* Type importManagerType = Type.GetType(importManagerTypeName);
* var importManager = (MetricsImportManager)Activator.CreateInstance(importManagerType, this.InstanceID, options);
*
*
* string[] deliveriesIds = null;
* string[] ouputsIds = null;
* if (Configuration.Options.ContainsKey(Consts.ConfigurationOptions.RollbackDeliveries))
* deliveriesIds = this.Configuration.GetOption(Consts.ConfigurationOptions.RollbackDeliveries).Split(',');
* else if (Configuration.Options.ContainsKey(Consts.ConfigurationOptions.RollbackOutputs))
* ouputsIds = this.Configuration.GetOption(Consts.ConfigurationOptions.RollbackOutputs).Split(',');
* else
* throw new Exception("Option RollbackDeliveries or RollbackOutputs must be defined");
*
* string tableName = this.Configuration.GetOption(Consts.ConfigurationOptions.RollbackTableName);
* List<DeliveryOutput> outputs=new List<DeliveryOutput>();
* List<Delivery> Deliveries=new List<Delivery>();
* if (deliveriesIds != null && deliveriesIds.Length > 0)
* {
* foreach (var id in deliveriesIds)
* {
* Deliveries.Add(Delivery.Get(Guid.Parse(id)));
*
* }
* importManager.RollbackDeliveries(Deliveries.ToArray());
*
* }
* if (ouputsIds != null && ouputsIds.Length > 0)
* {
* foreach (var id in ouputsIds)
* {
* outputs.Add(DeliveryOutput.Get(Guid.Parse(id)));
* }
* importManager.RollbackOutputs(outputs.ToArray());
* }
* return Core.Services.ServiceOutcome.Success; */
}
internal static Guid SaveOutput(DeliveryOutput output)
{
Guid guid = output.OutputID;
using (var client = DeliveryDBClient.Connect())
{
SqlTransaction transaction = client.BeginTransaction();
if (guid != Guid.Empty)
{
#region [Delete]
// ..................
SqlCommand cmd = SqlUtility.CreateCommand((String.Format("{0}(@outputID:Char)", AppSettings.Get(typeof(DeliveryDB), Const.SP_OutputDelete))), System.Data.CommandType.StoredProcedure);
cmd.Connection = client;
cmd.Transaction = transaction;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters["@outputID"].Value = output.OutputID.ToString("N");
cmd.ExecuteNonQuery();
// ..................
#endregion
#region DeliveryOutput
// ..................
if (output.OutputID == Guid.Empty)
{
output.OutputID = Guid.NewGuid();
}
if (output.DateCreated == DateTime.MinValue)
{
output.DateCreated = DateTime.Now;
}
output.DateModified = DateTime.Now;
cmd = SqlUtility.CreateCommand(@"
INSERT INTO [DeliveryOutput] (
[DeliveryID],
[OutputID],
[AccountID],
[AccountOriginalID],
[ChannelID],
[Signature],
[Status] ,
[TimePeriodStart],
[TimePeriodEnd],
[DateCreated],
[DateModified],
[PipelineInstanceID]
)
VALUES (
@deliveryID:Char,
@outputID:Char,
@accountID:Int,
@accountOriginalID::NVarChar,
@channelID:Int,
@signature:NVarChar,
@status:Int,
@timePeriodStart:DateTime2,
@timePeriodEnd:DateTime2,
@dateCreated:DateTime,
@dateModified:DateTime,
@pipelineInstanceID:BigInt
)" , System.Data.CommandType.Text);
cmd.Connection = client;
cmd.Transaction = transaction;
cmd.Parameters["@deliveryID"].Value = output.Delivery.DeliveryID;
cmd.Parameters["@outputID"].Value = output.OutputID.ToString("N");
cmd.Parameters["@accountID"].Value = output.Account != null ? output.Account.ID : -1;
cmd.Parameters["@channelID"].Value = output.Channel != null ? output.Channel.ID : -1;
cmd.Parameters["@signature"].Value = output.Signature;
cmd.Parameters["@status"].Value = output.Status;
cmd.Parameters["@timePeriodStart"].Value = output.TimePeriodStart;
cmd.Parameters["@timePeriodEnd"].Value = output.TimePeriodEnd;
cmd.Parameters["@dateCreated"].Value = output.DateCreated;
cmd.Parameters["@dateModified"].Value = output.DateModified;
cmd.Parameters["@pipelineInstanceID"].Value = output.PipelineInstanceID.HasValue ? output.PipelineInstanceID.Value : (object)DBNull.Value;
cmd.ExecuteNonQuery();
// ..................
#endregion
#region DeliveryOutputParameters
// ..................
foreach (KeyValuePair <string, object> param in output.Parameters)
{
cmd = SqlUtility.CreateCommand(@"
INSERT INTO [DeliveryOutputParameters] (
[DeliveryID],
[OutputID],
[Key],
[Value]
)
VALUES (
@deliveryID:Char,
@outputID:NVarChar,
@key:NVarChar,
@value:NVarChar
)" , System.Data.CommandType.Text);
cmd.Connection = client;
cmd.Transaction = transaction;
cmd.Parameters["@deliveryID"].Value = output.Delivery.DeliveryID.ToString("N");
cmd.Parameters["@outputID"].Value = output.OutputID.ToString("N");
cmd.Parameters["@key"].Value = param.Key;
cmd.Parameters["@value"].Value = Serialize(param.Value);
cmd.ExecuteNonQuery();
}
// ..................
#endregion
#region checkSum
foreach (KeyValuePair <string, double> sum in output.Checksum)
{
cmd = SqlUtility.CreateCommand(@"
INSERT INTO [DeliveryOutputChecksum] (
[DeliveryID],
[OutputID],
[MeasureName],
[Total]
)
VALUES (
@deliveryID:Char,
@outputid:Char,
@measureName:NVarChar,
@total:decimal
)" , System.Data.CommandType.Text);
cmd.Connection = client;
cmd.Transaction = transaction;
cmd.Parameters["@deliveryID"].Value = output.Delivery.DeliveryID.ToString("N");
cmd.Parameters["@outputid"].Value = output.OutputID.ToString("N");
cmd.Parameters["@measureName"].Value = sum.Key;
cmd.Parameters["@total"].Value = sum.Value;
cmd.ExecuteNonQuery();
}
#endregion
transaction.Commit();
}
else
{
throw new NotSupportedException("In Pipeline 2.9, you cannot save a Delivery without first giving it a GUID.");
}
}
return(guid);
}
public static DeliveryOutput GetOutput(Guid guid)
{
DeliveryOutput output = null;
using (var client = DeliveryDBClient.Connect())
{
// Select deliveries that match a signature but none of the guids in 'exclude'
using (SqlCommand cmd = SqlUtility.CreateCommand("OutPut_Get(@outputID:Char)", System.Data.CommandType.StoredProcedure))
{
cmd.Connection = client;
cmd.Parameters["@outputID"].Value = guid.ToString("N");
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
#region DeliveryOutput
output = new DeliveryOutput()
{
DeliveryID = reader.Convert <string, Guid>("DeliveryID", s => Guid.Parse(s)),
OutputID = reader.Convert <string, Guid>("OutputID", s => Guid.Parse(s)),
Account = reader.Convert <int?, Account>("AccountID", id => id.HasValue ? new Account()
{
ID = id.Value
} : null),
Channel = reader.Convert <int?, Channel>("ChannelID", id => id.HasValue ? new Channel()
{
ID = id.Value
} : null),
Signature = reader.Get <string>("Signature"),
Status = reader.Get <DeliveryOutputStatus>("Status"),
TimePeriodStart = reader.Get <DateTime>("TimePeriodStart"),
TimePeriodEnd = reader.Get <DateTime>("TimePeriodEnd"),
PipelineInstanceID = reader.Get <Guid?>("PipelineInstanceID")
};
#endregion
#region DeliveryOutputParameters
// ..................
if (reader.NextResult())
{
while (reader.Read())
{
output.Parameters.Add(reader.Get <string>("Key"), DeserializeJson(reader.Get <string>("Value")));
}
}
// ..................
#endregion
#region checksum
// ..................
if (reader.NextResult())
{
while (reader.Read())
{
if (output.Checksum == null)
{
output.Checksum = new Dictionary <string, double>();
}
output.Checksum.Add(reader["MeasureName"].ToString(), Convert.ToDouble(reader["Total"]));
}
}
// ..................
#endregion
}
}
}
}
return(output);
}
ProtocolResponse IProcessMessageStrategy.Process(ProtocolRequest request)
{
ProtocolResponse response = new ProtocolResponse(request.Action);
if (request.IsBulkQuery)
{
// now deserialize this string into a list of dictionaries for parsing
List <Dictionary <string, object> > DataDictionary =
Newtonsoft.Json.JsonConvert.DeserializeObject <List <Dictionary <string, object> > >(request.Data);
try
{
if (CheckConnection())
{
foreach (var d in DataDictionary)
{
IDbCommand cmd = SqlUtility.CreateCommand(Connection, request.Action, d);
List <object> results = SqlUtility.InvokeCommand(cmd, out string error);
response.Error += error;
if (results != null)
{
response.Data.Add(results);
}
}
response.Result = true;
}
else
{
response.Result = false;
response.Error = "Could not connect to MYSQL instance";
}
}
catch (Exception ex)
{
CatchException(ref ex, ref request, ref response);
}
finally
{
CloseConnection();
}
}
else
{
// now deserialize this string into a list of dictionaries for parsing
Dictionary <string, object> DataDictionary = null;
if (request.Type == JTokenType.Object)
{
DataDictionary = Newtonsoft.Json.JsonConvert.DeserializeObject <Dictionary <string, object> >(request.Data);
}
else
{
DataDictionary = new Dictionary <string, object>();
}
// special scenario - because we cant get the ip address of the game server from DCS, we'll get it from the socket sender object
// and specially insert it as a parameter into the data dictionary
// the other special scenario is the server description request can supply - this can contain harmful html, so we must sanitize the input
if (request.Action == ACTION_GET_SERVERID)
{
DataDictionary.Add("IP", request.IPAddress);
if (DataDictionary.ContainsKey("Description"))
{
try
{
string html = Convert.ToString(DataDictionary["Description"]);
DataDictionary["Description"] = Utility.SanitizeHTML(html, ref Config);
}
catch (Exception ex)
{
Logger.Log("Error sanitizing ServerDescription html string (Action: " + request.Action + ") - " + ex.Message);
response.Error = "Error sanitizing ServerDescription html string (Action: " + request.Action + ") - " + ex.Message;
response.Result = false;
return(response);
}
}
// Check the API version that the game is using
if (DataDictionary.ContainsKey("Version") && DataDictionary["Version"].ToString() != Config.VersionKey)
{
Logger.Log("Client Version Mismatch (Expected: " + Config.VersionKey + ", Got: " + DataDictionary["Version"] + ")");
response.Error = "Version mismatch - you are running an older version of KI - the latest version is [" + Config.Version + "] - Please update to the latest version";
response.Result = false;
return(response);
}
else if (!DataDictionary.ContainsKey("Version"))
{
Logger.Log("Client Version Mismatch - Client did not provide version information");
response.Error = "Version mismatch - you are running an older version of KI - the latest version is [" + Config.Version + "] - Please update to the latest version";
response.Result = false;
return(response);
}
}
try
{
if (CheckConnection())
{
IDbCommand cmd = SqlUtility.CreateCommand(Connection, request.Action, DataDictionary);
List <object> results = SqlUtility.InvokeCommand(cmd, out string error);
response.Error += error;
if (results != null)
{
response.Data.Add(results);
}
response.Result = true;
}
else
{
response.Result = false;
response.Error = "Could not connect to MYSQL instance";
}
}
catch (Exception ex)
{
CatchException(ref ex, ref request, ref response);
}
finally
{
CloseConnection();
}
}
return(response);
}
