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);
}
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);
}
}
