private static void updateTemplate(TemplateDS.TemplateTableRow template, TemplateDS stops)
{
//
SqlConnection sqlConnection = null;
try {
sqlConnection = new SqlConnection(Mediator.Connection);
sqlConnection.Open();
using (SqlTransaction trans = sqlConnection.BeginTransaction()) {
try {
executeNonQueryTypedParams(trans, USP_TEMPLATES_UPDATE, template);
foreach (TemplateDS.TemplateTableRow stop in stops.TemplateTable.Rows)
{
if (stop.IsStopIDNull())
{
//We need to add Stop here instead of update
stop.TemplateID = template.TemplateID;
executeNonQueryTypedParams(trans, USP_TEMPLATESSTOP_NEW, stop);
}
else
{
executeNonQueryTypedParams(trans, USP_TEMPLATESSTOP_UPDATE, stop);
}
}
trans.Commit();
}
catch (Exception ex) { trans.Rollback(); throw ex; }
}
}
finally { if (sqlConnection != null)
{
sqlConnection.Dispose();
}
}
}
private static void addTemplate(TemplateDS.TemplateTableRow template, TemplateDS stops)
{
SqlConnection sqlConnection = null;
try {
sqlConnection = new SqlConnection(Mediator.Connection);
sqlConnection.Open();
using (SqlTransaction trans = sqlConnection.BeginTransaction()) {
try {
template.TemplateLastUpdated = DateTime.Now;
template.TemplateUser = Environment.UserName;
SqlParameter[] spParams = setTemplateParameters(template);
executeNonQuery(trans, USP_TEMPLATES_NEW, spParams);
string templateID = spParams[0].Value.ToString();
foreach (TemplateDS.TemplateTableRow stop in stops.TemplateTable.Rows)
{
stop.TemplateID = templateID;
executeNonQueryTypedParams(trans, USP_TEMPLATESSTOP_NEW, stop);
}
trans.Commit();
}
catch (Exception ex) { trans.Rollback(); throw ex; }
}
}
finally { if (sqlConnection != null)
{
sqlConnection.Dispose();
}
}
}
private static SqlParameter[] setTemplateParameters(TemplateDS.TemplateTableRow template)
{
SqlParameter[] parms = new SqlParameter[13];
parms[0] = new SqlParameter("@TemplateID", SqlDbType.Char, 8);
parms[0].Direction = ParameterDirection.Output;
parms[1] = new SqlParameter("@SortCenterID", SqlDbType.BigInt);
parms[1].Value = template.SortCenterID;
parms[2] = new SqlParameter("@DayOfWeek", SqlDbType.TinyInt);
parms[2].Value = template.DayOfTheWeek;
parms[3] = new SqlParameter("@CarrierServiceID", SqlDbType.BigInt);
parms[3].Value = template.CarrierServiceID;
parms[4] = new SqlParameter("@ScheduledCloseDateOffset", SqlDbType.TinyInt);
parms[4].Value = template.ScheduledCloseDateOffset;
parms[5] = new SqlParameter("@ScheduledCloseTime", SqlDbType.DateTime);
parms[5].Value = template.ScheduledCloseTime;
parms[6] = new SqlParameter("@ScheduledDepartureDateOffset", SqlDbType.TinyInt);
parms[6].Value = template.ScheduledDepartureDateOffset;
parms[7] = new SqlParameter("@ScheduledDepartureTime", SqlDbType.DateTime);
parms[7].Value = template.@ScheduledDepartureTime;
parms[8] = new SqlParameter("@IsMandatory", SqlDbType.TinyInt);
parms[8].Value = template.IsMandatory;
parms[9] = new SqlParameter("@IsActive", SqlDbType.TinyInt);
parms[9].Value = template.IsActive;
parms[10] = new SqlParameter("@LastUpdated", SqlDbType.DateTime);
parms[10].Value = template.TemplateLastUpdated;
parms[11] = new SqlParameter("@UserID", SqlDbType.VarChar, 50);
parms[11].Value = template.TemplateUser;
parms[12] = new SqlParameter("@RowVersion", SqlDbType.Char, 24);
parms[12].Direction = ParameterDirection.Output;
return(parms);
}
private static TemplateDS populateStopTemplateRow(TemplateDS.TemplateTableRow template)
{
//Build First Stop row
TemplateDS ds = new TemplateDS();
TemplateDS.TemplateTableRow stop1 = ds.TemplateTable.NewTemplateTableRow();
stop1.StopID = !template.IsStopIDNull() ? template.StopID : "";
stop1.StopNumber = "1";
stop1.Tag = template.Tag;
stop1.AgentTerminalID = template.AgentTerminalID;
stop1.ScheduledArrivalDateOffset = template.ScheduledArrivalDateOffset;
stop1.ScheduledArrivalTime = template.ScheduledArrivalTime;
stop1.ScheduledOFD1Offset = template.ScheduledOFD1Offset;
stop1.Notes = template.IsNotesNull() ? "" : template.Notes;
stop1.Stop1LastUpdated = DateTime.Today;
stop1.Stop1User = Environment.UserName;
stop1.Stop1RowVersion = !template.IsStop1RowVersionNull() ? template.Stop1RowVersion : "";
ds.TemplateTable.AddTemplateTableRow(stop1);
//check for the second stop
if (!template.IsS2MainZoneNull() && template.S2MainZone.Trim() != "")
{
TemplateDS.TemplateTableRow stop2 = ds.TemplateTable.NewTemplateTableRow();
stop2.StopID = !template.IsS2StopIDNull() ? template.S2StopID : "";
stop2.StopNumber = "2";
stop2.Tag = template.S2Tag;
stop2.AgentTerminalID = template.S2AgentTerminalID;
stop2.ScheduledArrivalDateOffset = template.S2ScheduledArrivalDateOffset;
stop2.ScheduledArrivalTime = template.S2ScheduledArrivalTime;
stop2.ScheduledOFD1Offset = template.S2ScheduledOFD1Offset;
stop2.Notes = template.IsS2NotesNull() ? "" : template.S2Notes;
stop2.Stop1LastUpdated = DateTime.Today;
stop2.Stop1User = Environment.UserName;
stop2.Stop1RowVersion = !template.IsStop2RowVersionNull() ? template.Stop2RowVersion : "";
ds.TemplateTable.AddTemplateTableRow(stop2);
}
return(ds);
}
public ShipScheduleTemplate(TemplateDS.TemplateTableRow template)
{
//Constructor
if (template != null)
{
#region Set members
if (!template.IsTemplateIDNull())
{
this.mTemplateID = template.TemplateID;
}
if (!template.IsSortCenterIDNull())
{
this.mSortCenterID = template.SortCenterID;
}
if (!template.IsSortCenterNull())
{
this.mSortCenter = template.SortCenter;
}
if (!template.IsDayOfTheWeekNull())
{
this.mDayOfTheWeek = template.DayOfTheWeek;
}
if (!template.IsCarrierServiceIDNull())
{
this.mCarrierServiceID = template.CarrierServiceID;
}
if (!template.IsCarrierNull())
{
this.mCarrier = template.Carrier;
}
if (!template.IsScheduledCloseDateOffsetNull())
{
this.mScheduledCloseDateOffset = template.ScheduledCloseDateOffset;
}
if (!template.IsScheduledCloseTimeNull())
{
this.mScheduledCloseTime = template.ScheduledCloseTime;
}
if (!template.IsScheduledDepartureDateOffsetNull())
{
this.mScheduledDepartureDateOffset = template.ScheduledDepartureDateOffset;
}
if (!template.IsScheduledDepartureTimeNull())
{
this.mScheduledDepartureTime = template.ScheduledDepartureTime;
}
if (!template.IsIsMandatoryNull())
{
this.mIsMandatory = template.IsMandatory;
}
if (!template.IsIsActiveNull())
{
this.mIsActive = template.IsActive;
}
if (!template.IsTemplateLastUpdatedNull())
{
this.mTemplateLastUpdated = template.TemplateLastUpdated;
}
if (!template.IsTemplateUserNull())
{
this.mTemplateUser = template.TemplateUser;
}
if (!template.IsTemplateRowVersionNull())
{
this.mTemplateRowVersion = template.TemplateRowVersion;
}
if (!template.IsStopIDNull())
{
this.mStopID = template.StopID;
}
if (!template.IsStopNumberNull())
{
this.mStopNumber = template.StopNumber;
}
if (!template.IsMainZoneNull())
{
this.mMainZone = template.MainZone;
}
if (!template.IsTagNull())
{
this.mTag = template.Tag;
}
if (!template.IsAgentTerminalIDNull())
{
this.mAgentTerminalID = template.AgentTerminalID;
}
if (!template.IsAgentNumberNull())
{
this.mAgentNumber = template.AgentNumber;
}
if (!template.IsScheduledArrivalDateOffsetNull())
{
this.mScheduledArrivalDateOffset = template.ScheduledArrivalDateOffset;
}
if (!template.IsScheduledArrivalTimeNull())
{
this.mScheduledArrivalTime = template.ScheduledArrivalTime;
}
if (!template.IsScheduledOFD1OffsetNull())
{
this.mScheduledOFD1Offset = template.ScheduledOFD1Offset;
}
if (!template.IsNotesNull())
{
this.mNotes = template.Notes;
}
if (!template.IsStop1LastUpdatedNull())
{
this.mStop1LastUpdated = template.Stop1LastUpdated;
}
if (!template.IsStop1UserNull())
{
this.mStop1User = template.Stop1User;
}
if (!template.IsStop1RowVersionNull())
{
this.mStop1RowVersion = template.Stop1RowVersion;
}
if (!template.IsS2StopIDNull())
{
this.mS2StopID = template.S2StopID;
}
if (!template.IsS2StopNumberNull())
{
this.mS2StopNumber = template.S2StopNumber;
}
if (!template.IsS2MainZoneNull())
{
this.mS2MainZone = template.S2MainZone;
}
if (!template.IsS2TagNull())
{
this.mS2Tag = template.S2Tag;
}
if (!template.IsS2AgentTerminalIDNull())
{
this.mS2AgentTerminalID = template.S2AgentTerminalID;
}
if (!template.IsS2AgentNumberNull())
{
this.mS2AgentNumber = template.S2AgentNumber;
}
if (!template.IsS2ScheduledArrivalDateOffsetNull())
{
this.mS2ScheduledArrivalDateOffset = template.S2ScheduledArrivalDateOffset;
}
if (!template.IsS2ScheduledArrivalTimeNull())
{
this.mS2ScheduledArrivalTime = template.S2ScheduledArrivalTime;
}
if (!template.IsS2ScheduledOFD1OffsetNull())
{
this.mS2ScheduledOFD1Offset = template.S2ScheduledOFD1Offset;
}
if (!template.IsS2NotesNull())
{
this.mS2Notes = template.S2Notes;
}
if (!template.IsStop2LastUpdatedNull())
{
this.mStop2LastUpdated = template.Stop2LastUpdated;
}
if (!template.IsStop2UserNull())
{
this.mStop2User = template.Stop2User;
}
if (!template.IsStop2RowVersionNull())
{
this.mStop2RowVersion = template.Stop2RowVersion;
}
#endregion
}
}
public static bool UpdateTemplate(TemplateDS.TemplateTableRow template)
{
updateTemplate(template, populateStopTemplateRow(template));
return(true);
}
