/// <summary>
/// Sets the receiving address on the tracking record based on the UPS Track API response.
/// </summary>
/// <param name="trackingRecord">Object containing tracking data that will be written to UPS Integration DB.</param>
/// <param name="upsResponse">Response object from the UPS Track API.</param>
public void SetDestinationAddress(UPSTracking trackingRecord, UPSResponse upsResponse)
{
var destinationAddress = TrackAPI.GetAddressByType(upsResponse, "ShipTo Address");
trackingRecord.DestinationCity = destinationAddress?.Address.City;
trackingRecord.DestinationState = destinationAddress?.Address.StateProvinceCode;
trackingRecord.DestinationZip = destinationAddress?.Address.PostalCode;
}
/// <summary>
/// Sets the package's current status, location, timestamp and exception (if applicable) on the tracking record based on the
/// UPS Track API response.
/// </summary>
/// <param name="trackingRecord">Object containing tracking data that will be written to UPS Integration DB.</param>
/// <param name="upsResponse">Response object from the UPS Track API.</param>
public void SetLatestActivity(UPSTracking trackingRecord, UPSResponse upsResponse)
{
var latestActivity = TrackAPI.GetLatestActivity(upsResponse);
trackingRecord.Status = latestActivity.Status;
trackingRecord.Location = latestActivity.Location;
trackingRecord.TimeStamp = latestActivity.TimeStamp;
trackingRecord.ExceptionReason = latestActivity.ExceptionReason;
}
/// <summary>
/// Sets the sender address on the tracking record based on the UPS Track API response.
/// </summary>
/// <param name="trackingRecord">Object containing tracking data that will be written to UPS Integration DB.</param>
/// <param name="upsResponse">Response object from the UPS Track API.</param>
public void SetOriginAddress(UPSTracking trackingRecord, UPSResponse upsResponse)
{
var originAddress = TrackAPI.GetAddressByType(upsResponse, "Shipper Address");
trackingRecord.OriginAddress = originAddress?.Address.AddressLine;
trackingRecord.OriginCity = originAddress?.Address.City;
trackingRecord.OriginState = originAddress?.Address.StateProvinceCode;
var originZip = originAddress?.Address.PostalCode;
// Format with hyphen after first 5 digits
if (originZip.Length > 5)
{
originZip = originZip.Insert(5, "-");
}
trackingRecord.OriginZip = originZip;
}
/// <summary>
/// Handles inserts to the TrackingNumber or StatusUpdates by creating a SQL query dynamically based on the table name provided.
/// </summary>
/// <param name="trackingData">UPS Tracking data to insert</param>
/// <param name="tableName">Name of the UPSIntegration table to insert to. Currently handles TrackingNumber and StatusUpdates.</param>
public void InsertLineToUPSTable(UPSTracking trackingData, string tableName)
{
var retryPolicy = Policy.Handle <SqlException>(
e => !e.Message.Contains("Violation of PRIMARY KEY constraint"))
.WaitAndRetry(4, _ => TimeSpan.FromMilliseconds(30), (ex, ts, count, context) =>
{
string errorMessage = $"Error in InsertLineToUPSTable. Table name {tableName}";
_logger.LogWarning(ex, $"{errorMessage} . Retrying...");
if (count == 4)
{
_logger.LogError(ex, errorMessage);
}
});
retryPolicy.Execute(() =>
{
using (var conn = new SqlConnection(connectionString))
{
conn.Open();
var query = $"INSERT INTO UPSIntegration.tracking.{tableName} ";
if (tableName == "StatusUpdates")
{
query += $@"
VALUES (
@trackingNum, @status, @time, @location, @exception)";
conn.Execute(
query,
new
{
trackingNum = trackingData.TrackingNumber,
status = trackingData.Status,
time = trackingData.TimeStamp,
location = trackingData.Location,
exception = trackingData.ExceptionReason
},
commandTimeout: 3);
}
if (tableName == "TrackingNumbers")
{
query += $@"
VALUES (
@trackingNum, @refNum, @originAddress, @originCity, @originState,
@originZip, @destinationCity, @destinationState, @destinationZip)";
conn.Execute(
query,
new
{
trackingNum = trackingData.TrackingNumber,
refNum = trackingData.ReferenceNumber,
originAddress = trackingData.OriginAddress,
originCity = trackingData.OriginCity,
originState = trackingData.OriginState,
originZip = trackingData.OriginZip,
destinationCity = trackingData.DestinationCity,
destinationState = trackingData.DestinationState,
destinationZip = trackingData.DestinationZip
},
commandTimeout: 3);
}
conn.Close();
}
});
}
