internal static Guid Save(Delivery delivery)
{
using (var client = DeliveryDBClient.Connect())
{
Guid guid = delivery.Guid;
if (guid != Guid.Empty)
{
// refer here for an explanation on what's going on:
// http://stackoverflow.com/questions/5848990/retrieve-an-object-in-one-db4o-session-store-in-another-disconnected-scenario
// Get an inactive reference, get its temp ID and bind the supplied object to it instead
Delivery inactiveReference = DeliveryDB.Get(delivery.Guid, false, client);
if (inactiveReference != null)
{
long tempDb4oID = client.Ext().GetID(inactiveReference);
client.Ext().Bind(delivery, tempDb4oID);
}
}
else
{
guid = Guid.NewGuid();
}
// Try to store, and return new guid on success
client.Store(delivery);
return(guid);
}
}
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 = DataManager.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());
}
}
internal static Delivery[] GetByTargetPeriod(int channelID, int accountID, DateTime start, DateTime end, bool exact)
{
List <Delivery> deliveries = new List <Delivery>();
List <string> deliveriesId = new List <string>();
using (var client = DeliveryDBClient.Connect())
{
using (SqlCommand cmd = DataManager.CreateCommand("Delivery_GetByTargetPeriod(@channelID:Int,@accountID:Int,@targetPeriodStart:DateTime2,@targetPeriodEnd:DateTime2,@exact:bit)", 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;
cmd.Parameters["@exact"].Value = exact;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
//deliveriesId.Add(Get(Guid.Parse(reader.GetString(0))));
deliveriesId.Add(reader.GetString(0));
}
}
}
foreach (string id in deliveriesId)
{
deliveries.Add(Get(Guid.Parse(id)));
}
return(deliveries.ToArray());
}
}
internal static void Delete(Delivery delivery)
{
using (SqlConnection connection = DeliveryDBClient.Connect())
{
using (SqlCommand cmd = new SqlCommand(AppSettings.Get(typeof(DeliveryDB), Const.SP_DeliveryDelete)))
{
cmd.Connection = connection;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("@deliveryID", System.Data.SqlDbType.Char);
cmd.Parameters["@deliveryID"].Value = delivery.DeliveryID.ToString("N");
cmd.ExecuteNonQuery();
}
}
}
internal static Delivery Get(Guid deliveryID, bool activate = true, IObjectContainer client = null)
{
Delivery delivery;
bool innerClient = client == null;
int previousActivationDepth = -1;
if (innerClient)
{
client = DeliveryDBClient.Connect();
}
else
{
previousActivationDepth = client.Ext().Configure().ActivationDepth();
}
try
{
// set activation depth to 0 so that only an object reference is retrieved, this can then be used to swap the object
if (!activate)
{
client.Ext().Configure().ActivationDepth(0);
}
var results = (from Delivery d in client where d.Guid == deliveryID select d);
delivery = results.Count() > 0 ? results.First() : null;
}
finally
{
if (innerClient)
{
client.Dispose();
}
else
{
// reset activation depth
client.Ext().Configure().ActivationDepth(previousActivationDepth);
}
}
return(delivery);
}
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 = DataManager.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());
}
internal static Delivery[] GetBySignature(string signature, Guid[] exclude)
{
List <Delivery> deliveries = new List <Delivery>();
using (var client = DeliveryDBClient.Connect())
{
// Select deliveries that match a signature but none of the guids in 'exclude'
using (SqlCommand cmd = DataManager.CreateCommand("Delivery_GetBySignature(@signature:NvarChar,@exclude:NvarChar)", System.Data.CommandType.StoredProcedure))
{
cmd.Connection = client;
cmd.Parameters["@signature"].Value = "'" + signature + "'";
cmd.Parameters["@exclude"].Value = GetGuidStringArray(exclude); //TODO: Talk with doron it's not the way to do it
using (SqlDataReader reader = cmd.ExecuteReader()) //TODO: Talk with doron it's not the way to do it
{
while (reader.Read())
{
deliveries.Add(Get(Guid.Parse(reader.GetString(0))));
}
}
}
}
return(deliveries.ToArray());
}
internal static bool GetRuning(long parentInstanceID)
{
bool runing = false;
using (var client = DeliveryDBClient.Connect())
{
// Select deliveries that match a signature but none of the guids in 'exclude'
using (SqlCommand cmd = DataManager.CreateCommand(@"SELECT InstanceID
FROM ServiceInstance
WHERE State!= 6 AND State!= 7 AND InstanceID=@instanceid:Bigint" , System.Data.CommandType.Text))
{
cmd.Connection = client;
cmd.Parameters["@instanceid"].Value = parentInstanceID;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
runing = true;
}
}
}
}
return(runing);
}
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 = DataManager.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 <long?>("PipelineInstanceID")
};
output.Account.OriginalID = reader.Get <string>("AccountOriginalID");
#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);
}
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 = DataManager.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 = DataManager.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["@accountOriginalID"].Value = output.Account.OriginalID != null ? output.Account.OriginalID : (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 (KeyValuePair <string, object> param in output.Parameters)
{
cmd = DataManager.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 = DataManager.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);
}
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 = DataManager.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,
OriginalID = reader.Get <string>("Account_OriginalID"),
} : 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,
OriginalID = reader.Get <string>("AccountOriginalID")
} : 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 <long>("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);
}
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 = DataManager.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 = DataManager.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["@account_originalID"].Value = delivery.Account == null ? (object)DBNull.Value : delivery.Account.OriginalID == null ? (object)DBNull.Value : delivery.Account.OriginalID;
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 = DataManager.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 = DataManager.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 = DataManager.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 = DataManager.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.OriginalID != null ? output.Account.OriginalID : (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 = DataManager.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 = DataManager.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 Get(Guid deliveryID, bool deep = true, SqlConnection connection = null)
{
Delivery delivery = null;
bool innerConnection = connection == null;
if (innerConnection)
{
connection = DeliveryDBClient.Connect();
}
try
{
SqlCommand cmd = DataManager.CreateCommand("Delivery_Get(@deliveryID:Char, @deep:bit)", 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
};
delivery.Account = reader.Convert <int?, Account>("AccountID", id => id.HasValue ? new Account()
{
ID = id.Value
} : null);
delivery.Channel = reader.Convert <int?, Channel>("ChannelID", id => id.HasValue ? new Channel()
{
ID = id.Value
} : null);
delivery.Account.OriginalID = reader.Get <string>("OriginalID");
delivery.DateCreated = reader.Get <DateTime>("DateCreated");
delivery.DateModified = reader.Get <DateTime>("DateModified");
delivery.Signature = reader.Get <string>("Signature");
delivery.Description = reader.Get <string>("Description");
delivery.TargetLocationDirectory = reader.Get <string>("TargetLocationDirectory");
delivery.InternalSetTargetPeriod(
DateTimeRange.Parse(reader.Get <string>("TargetPeriodDefinition")),
reader.Get <DateTime>("TargetPeriodStart"),
reader.Get <DateTime>("TargetPeriodEnd")
);
delivery.IsCommited = Convert.ToBoolean(reader["Committed"]);
#endregion
if (deep)
{
#region DeliveryParameters
if (deep)
{
if (reader.NextResult())
{
while (reader.Read())
{
delivery.Parameters.Add(reader.Get <string>("Key"), DeserializeJson(reader.Get <string>("Value")));
}
}
}
#endregion
#region DeliveryHistory
if (reader.NextResult())
{
while (reader.Read())
{
delivery.History.Add(new DeliveryHistoryEntry((DeliveryOperation)reader["Operation"], reader.Get <long>("ServiceInstanceID"), new Dictionary <string, object>()));
}
}
#endregion
#region DeliveryHistoryParameters
if (reader.NextResult())
{
while (reader.Read())
{
delivery.History[reader.Get <int>("Index")].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.Account = reader.Convert <int?, Account>("AccountID", id => id.HasValue ? new Account()
{
ID = id.Value
} : null);
deliveryFile.FileID = reader.Convert <string, Guid>("DeliveryID", s => Guid.Parse(s));
deliveryFile.FileFormat = (FileCompression)reader["FileCompression"];
deliveryFile.SourceUrl = reader.Get <string>("SourceUrl");
deliveryFile.Name = reader.Get <string>("Name");
deliveryFile.Location = reader.Get <string>("Location");
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 DeliveryFileHistory
if (reader.NextResult())
{
while (reader.Read())
{
DeliveryFile deliveryFile = delivery.Files[reader["Name"].ToString()];
deliveryFile.History.Add(new DeliveryHistoryEntry((DeliveryOperation)reader["Operation"], Convert.ToInt64(reader["ServiceInstanceID"])));
}
}
#endregion
#region DeliveryFileHistoryParameters
if (reader.NextResult())
{
while (reader.Read())
{
DeliveryFile deliveryFile = delivery.Files[reader["Name"].ToString()];
deliveryFile.History[reader.Get <int>("Index")].Parameters.Add(reader["Key"].ToString(), reader.Get <object>("Value"));
}
}
#endregion
}
}
}
}
finally
{
if (innerConnection)
{
connection.Dispose();
}
}
/*
#if DEBUG
* Log.Write(String.Format("Delivery - {3}found: {0} (activate: {2}, {1} results)\n", deliveryID, resultCount, activate, delivery == null ? "not " : "" ), LogMessageType.Information);
*
#endif
*/
return(delivery);
}
internal static Guid Save(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 all Delivery
SqlCommand cmd = new SqlCommand("Delivery_Delete");
cmd.Connection = client;
cmd.Transaction = transaction;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("@deliveryID", System.Data.SqlDbType.Char);
cmd.Parameters["@deliveryID"].Value = delivery.DeliveryID.ToString("N");
cmd.ExecuteNonQuery();
#endregion
#region Delivery
cmd = new SqlCommand(@"INSERT INTO [Delivery]
([DeliveryID]
,[AccountID]
,[ChannelID]
,[OriginalID]
,[DateCreated]
,[DateModified]
,[Signature]
,[Description]
,[TargetLocationDirectory]
,[TargetPeriodDefinition]
,[TargetPeriodStart]
,[TargetPeriodEnd]
,[Committed])
VALUES
(@deliveryID,
@accountID,
@channelID,
@originalID,
@dateCreated,
@dateModified,
@signature,
@description,
@targetLocationDirectory,
@targetPeriodDefinition,
@targetPeriodStart,
@targetPeriodEnd,
@committed)" , client, transaction);
cmd.Connection = client;
cmd.Parameters.Add("@deliveryID", System.Data.SqlDbType.Char);
cmd.Parameters.Add("@accountID", System.Data.SqlDbType.Int);
cmd.Parameters.Add("@channelID", System.Data.SqlDbType.Int);
cmd.Parameters.Add("@originalID", System.Data.SqlDbType.NVarChar);
cmd.Parameters.Add("@dateCreated", System.Data.SqlDbType.DateTime);
cmd.Parameters.Add("@dateModified", System.Data.SqlDbType.DateTime);
cmd.Parameters.Add("@signature", System.Data.SqlDbType.NVarChar);
cmd.Parameters.Add("@description", System.Data.SqlDbType.NVarChar);
cmd.Parameters.Add("@targetLocationDirectory", System.Data.SqlDbType.NVarChar);
cmd.Parameters.Add("@targetPeriodDefinition", System.Data.SqlDbType.NVarChar);
cmd.Parameters.Add("@targetPeriodStart", System.Data.SqlDbType.DateTime2); //must be date time since sql round or somthing, leave it!!
cmd.Parameters.Add("@targetPeriodEnd", System.Data.SqlDbType.DateTime2); //must be date time since sql round or somthing, leave it!!
cmd.Parameters.Add("@committed", System.Data.SqlDbType.Bit);
cmd.Parameters["@deliveryID"].Value = delivery.DeliveryID.ToString("N");
cmd.Parameters["@accountID"].Value = delivery.Account.ID;
cmd.Parameters["@channelID"].Value = delivery.Channel.ID;;
cmd.Parameters["@originalID"].Value = delivery.Account.OriginalID == null ? (object)DBNull.Value : delivery.Account.OriginalID;
cmd.Parameters["@dateCreated"].Value = delivery.DateCreated;
cmd.Parameters["@dateModified"].Value = delivery.DateModified;
cmd.Parameters["@signature"].Value = delivery.Signature;
cmd.Parameters["@description"].Value = delivery.Description == null ? (object)DBNull.Value : delivery.Description;
cmd.Parameters["@targetLocationDirectory"].Value = delivery.TargetLocationDirectory;
cmd.Parameters["@targetPeriodDefinition"].Value = delivery.TargetPeriod.ToString();
cmd.Parameters["@targetPeriodStart"].Value = delivery.TargetPeriodStart;
cmd.Parameters["@targetPeriodEnd"].Value = delivery.TargetPeriodEnd;
cmd.Parameters["@committed"].Value = delivery.IsCommited;
cmd.ExecuteNonQuery();
#endregion
#region DeliveryParameters
foreach (KeyValuePair <string, object> param in delivery.Parameters)
{
cmd = new SqlCommand(@"INSERT INTO [DeliveryParameters]
([DeliveryID]
,[Key]
,[Value])
VALUES
(@deliveryID
,@key
,@value)" );
cmd.Connection = client;
cmd.Transaction = transaction;
cmd.Parameters.Add("@deliveryID", System.Data.SqlDbType.Char);
cmd.Parameters.Add("@key", System.Data.SqlDbType.NVarChar);
cmd.Parameters.Add("@value", System.Data.SqlDbType.NVarChar);
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 DeliveryHistory
int index = 0;
foreach (DeliveryHistoryEntry historyEntry in delivery.History)
{
cmd = new SqlCommand(@"INSERT INTO [DeliveryHistory]
([DeliveryID]
,[ServiceInstanceID]
,[Index]
,[Operation]
,[DateRecorded])
VALUES
(@deliveryID
,@serviceInstanceID
,@index
,@operation
,@dateRecorded)" );
cmd.Connection = client;
cmd.Transaction = transaction;
cmd.Parameters.Add("@deliveryID", System.Data.SqlDbType.Char);
cmd.Parameters.Add("@serviceInstanceID", System.Data.SqlDbType.BigInt);
cmd.Parameters.Add("@index", System.Data.SqlDbType.Int);
cmd.Parameters.Add("@operation", System.Data.SqlDbType.Int);
cmd.Parameters.Add("@dateRecorded", System.Data.SqlDbType.DateTime);
cmd.Parameters["@deliveryID"].Value = delivery.DeliveryID.ToString("N");
cmd.Parameters["@index"].Value = index;
cmd.Parameters["@operation"].Value = historyEntry.Operation;
cmd.Parameters["@dateRecorded"].Value = historyEntry.DateRecorded;
cmd.Parameters["@serviceInstanceID"].Value = historyEntry.ServiceInstanceID;
cmd.ExecuteNonQuery();
index++;
}
#endregion
#region DeliveryHistoryParameters
index = 0;
if (delivery.History != null)
{
foreach (DeliveryHistoryEntry historyEntry in delivery.History)
{
if (historyEntry.Parameters != null)
{
foreach (KeyValuePair <string, object> param in historyEntry.Parameters)
{
cmd = new SqlCommand(@"INSERT INTO [DeliveryHistoryParameters]
([DeliveryID]
,[Index]
,[Key]
,[Value])
VALUES
(@deliveryID
,@index
,@key
,@value)" );
cmd.Connection = client;
cmd.Transaction = transaction;
cmd.Parameters.Add("@deliveryID", System.Data.SqlDbType.Char);
cmd.Parameters.Add("@index", System.Data.SqlDbType.Int);
cmd.Parameters.Add("@key", System.Data.SqlDbType.NVarChar);
cmd.Parameters.Add("@value", System.Data.SqlDbType.NVarChar);
cmd.Parameters["@deliveryID"].Value = delivery.DeliveryID.ToString("N");
cmd.Parameters["@index"].Value = index;
cmd.Parameters["@key"].Value = param.Key;
cmd.Parameters["@value"].Value = Serialize(param.Value);
cmd.ExecuteNonQuery();
}
}
index++;
}
}
#endregion
#region DeliveryFile
foreach (DeliveryFile file in delivery.Files)
{
if (file.FileID == Guid.Empty)
{
file.FileID = Guid.NewGuid();
}
cmd = new SqlCommand(@"INSERT INTO [DeliveryFile]
([DeliveryID]
,[FileID]
,[Name]
,[AccountID]
,[ChannelID]
,[DateCreated]
,[DateModified]
,[FileCompression]
,[SourceUrl]
,[Location])
VALUES
(@deliveryID
,@fileID
,@name
,@accountID
,@channelID
,@dateCreated
,@dateModified
,@fileCompression
,@sourceUrl
,@location)" );
cmd.Connection = client;
cmd.Transaction = transaction;
cmd.Parameters.Add("@deliveryID", System.Data.SqlDbType.Char);
cmd.Parameters.Add("@fileID", System.Data.SqlDbType.Char);
cmd.Parameters.Add("@name", System.Data.SqlDbType.NVarChar);
cmd.Parameters.Add("@accountID", System.Data.SqlDbType.Int);
cmd.Parameters.Add("@channelID", System.Data.SqlDbType.Int);
cmd.Parameters.Add("@dateCreated", System.Data.SqlDbType.DateTime);
cmd.Parameters.Add("@dateModified", System.Data.SqlDbType.DateTime);
cmd.Parameters.Add("@fileCompression", System.Data.SqlDbType.Int);
cmd.Parameters.Add("@sourceUrl", System.Data.SqlDbType.NVarChar);
cmd.Parameters.Add("@location", System.Data.SqlDbType.NVarChar);
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["@accountID"].Value = delivery.Account.ID;
cmd.Parameters["@channelID"].Value = delivery.Channel.ID;
cmd.Parameters["@dateCreated"].Value = file.DateCreated;
cmd.Parameters["@dateModified"].Value = file.DateModified;
cmd.Parameters["@fileCompression"].Value = file.FileFormat;
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.ExecuteNonQuery();
}
#endregion
#region DeliveryFileParameters
foreach (DeliveryFile file in delivery.Files)
{
foreach (KeyValuePair <string, object> param in file.Parameters)
{
cmd = new SqlCommand(@"INSERT INTO [DeliveryFileParameters]
([DeliveryID]
,[Name]
,[Key]
,[Value])
VALUES
(@deliveryID
,@name
,@key
,@value)" );
cmd.Connection = client;
cmd.Transaction = transaction;
cmd.Parameters.Add("@deliveryID", System.Data.SqlDbType.Char);
cmd.Parameters.Add("@name", System.Data.SqlDbType.NVarChar);
cmd.Parameters.Add("@key", System.Data.SqlDbType.NVarChar);
cmd.Parameters.Add("@value", System.Data.SqlDbType.NVarChar);
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 DeliveryFileHistory
foreach (DeliveryFile file in delivery.Files)
{
index = 0;
if (file.History != null)
{
foreach (DeliveryHistoryEntry historyEntry in file.History)
{
cmd = new SqlCommand(@"INSERT INTO [DeliveryFileHistory]
([DeliveryID]
,[Name]
,[ServiceInstanceID]
,[Index]
,[Operation]
,[DateRecorded])
VALUES
(@deliveryID
,@name
,@serviceInstanceID
,@index
,@operation
,@dateRecorded)" );
cmd.Connection = client;
cmd.Transaction = transaction;
cmd.Parameters.Add("@deliveryID", System.Data.SqlDbType.Char);
cmd.Parameters.Add("@name", System.Data.SqlDbType.NVarChar);
cmd.Parameters.Add("@serviceInstanceID", System.Data.SqlDbType.BigInt);
cmd.Parameters.Add("@index", System.Data.SqlDbType.Int);
cmd.Parameters.Add("@operation", System.Data.SqlDbType.Int);
cmd.Parameters.Add("@dateRecorded", System.Data.SqlDbType.DateTime);
cmd.Parameters["@deliveryID"].Value = delivery.DeliveryID.ToString("N");
cmd.Parameters["@name"].Value = file.Name;
cmd.Parameters["@serviceInstanceID"].Value = historyEntry.ServiceInstanceID;
cmd.Parameters["@index"].Value = index;
cmd.Parameters["@operation"].Value = historyEntry.Operation;
cmd.Parameters["@dateRecorded"].Value = historyEntry.DateRecorded;
cmd.ExecuteNonQuery();
index++;
}
}
}
#endregion
#region DeliveryFileHistoryParameters
foreach (DeliveryFile file in delivery.Files)
{
index = 0;
if (file.History != null)
{
foreach (DeliveryHistoryEntry historyEntry in file.History)
{
if (historyEntry.Parameters != null)
{
foreach (KeyValuePair <string, object> param in historyEntry.Parameters)
{
cmd = new SqlCommand(@"INSERT INTO [DeliveryFileHistoryParameters]
([DeliveryID]
,[Name]
,[Index]
,[Key]
,[Value])
VALUES
(@deliveryID
,@name
,@index
,@key
,@value)" );
cmd.Connection = client;
cmd.Transaction = transaction;
cmd.Parameters.Add("@deliveryID", System.Data.SqlDbType.Char);
cmd.Parameters.Add("@name", System.Data.SqlDbType.NVarChar);
cmd.Parameters.Add("@index", System.Data.SqlDbType.Int);
cmd.Parameters.Add("@key", System.Data.SqlDbType.NVarChar);
cmd.Parameters.Add("@value", System.Data.SqlDbType.NVarChar);
cmd.Parameters["@deliveryID"].Value = delivery.DeliveryID.ToString("N");
cmd.Parameters["@name"].Value = file.Name;
cmd.Parameters["@index"].Value = index;
cmd.Parameters["@key"].Value = param.Key;
cmd.Parameters["@value"].Value = Serialize(param.Value);
cmd.ExecuteNonQuery();
}
}
index++;
}
}
}
#endregion
transaction.Commit();
}
else
{
guid = Guid.NewGuid();
throw new NotSupportedException("In Pipeline 2.9, you cannot save a Delivery without first giving it a GUID.");
}
return(guid);
}
}
