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 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 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);
}
protected virtual void OnRollbackOutput(DeliveryOutput output, int pass)
{
}
