public static ShipScheduleTrip GetEarlierTripFromThisSchedule(string tripID, string freightID)
{
//Return an earlier trip from the current schedule than the one specified if one exists
ShipScheduleTrip earlierTrip = null;
try {
//Get all earlier trips (open, not cancelled)
DataSet ds = App.Mediator.FillDataset(USP_SHIPSCHEDULE_PRIORTRIPS, TBL_SHIPSCHEDULE_PRIORTRIPS, new object[] { tripID, freightID });
if (ds.Tables[TBL_SHIPSCHEDULE_PRIORTRIPS].Rows.Count > 0)
{
ShipScheduleDS schedule = new ShipScheduleDS();
schedule.Merge(ds);
int tag = 0;
for (int i = 0; i < schedule.ShipScheduleMasterTable.Rows.Count; i++)
{
//Select only trips with the same schedule date as this.mScheduleDate
ShipScheduleDS.ShipScheduleMasterTableRow trip = (ShipScheduleDS.ShipScheduleMasterTableRow)schedule.ShipScheduleMasterTable.Rows[i];
if (trip.ScheduleDate.CompareTo(_ScheduleDate) == 0)
{
//Capture the trip with the largest tag #
if (int.Parse(trip.Tag.Trim()) > tag)
{
tag = int.Parse(trip.Tag.Trim());
earlierTrip = new ShipScheduleTrip(trip);
}
}
}
}
}
catch (Exception ex) { throw new ApplicationException("Failed determine if an earlier ship schedule trip exists.", ex); }
return(earlierTrip);
}
private ShipScheduleDS updateTrip(ShipScheduleDS trip)
{
//Updated: January 2005
//Update a single trip on the schedule; return updated row (i.e. including new row version)
//We were not updating Stop 2; there could be 2 rows in the stop table
SqlParameter[] spTripParams = null, spStop1Params = null, spStop2Params = null;
SqlConnection sqlConn = null;
try {
sqlConn = new SqlConnection(this.mMediator.Connection);
sqlConn.Open();
using (SqlTransaction sqlTrans = sqlConn.BeginTransaction()) {
try {
//Update trip details
spTripParams = setTripParameters(trip.ShipScheduleTripTable[0]);
SqlHelper.ExecuteNonQuery(sqlTrans, CommandType.StoredProcedure, Lib.USP_TRIPUPDATE, spTripParams);
}
catch (Exception ex) { sqlTrans.Rollback(); throw new ApplicationException("Failed to update ship schedule trip details... Rolling back update transaction.", ex); }
try {
//Update stop 1 details
spStop1Params = setStopParameters(trip.ShipScheduleStopTable[0]);
SqlHelper.ExecuteNonQuery(sqlTrans, CommandType.StoredProcedure, Lib.USP_STOPUPDATE, spStop1Params);
}
catch (Exception ex) { sqlTrans.Rollback(); throw new ApplicationException("Failed to update ship schedule stop 1 details... Rolling back update transaction.", ex); }
try {
//Update stop 2 details
if (trip.ShipScheduleStopTable.Rows.Count == 2)
{
spStop2Params = setStopParameters(trip.ShipScheduleStopTable[1]);
SqlHelper.ExecuteNonQuery(sqlTrans, CommandType.StoredProcedure, Lib.USP_STOPUPDATE, spStop2Params);
}
}
catch (Exception ex) { sqlTrans.Rollback(); throw new ApplicationException("Failed to update ship schedule stop 2 details... Rolling back update transaction.", ex); }
//Commit changes and update row versions
sqlTrans.Commit();
trip.ShipScheduleTripTable[0].RowVersion = spTripParams[15].Value.ToString();
trip.ShipScheduleStopTable[0].RowVersion = spStop1Params[6].Value.ToString();
if (spStop2Params != null)
{
trip.ShipScheduleStopTable[1].RowVersion = spStop2Params[6].Value.ToString();
}
trip.AcceptChanges();
}
}
catch (ApplicationException ex) { throw ex; }
catch (Exception ex) { throw new ApplicationException("Failed to update ship schedule details.", ex); }
finally { if (sqlConn != null)
{
sqlConn.Dispose();
}
}
return(trip);
}
public void Update()
{
//Update: Jan 2005
//We are refreshing the dataset now. We are extracting the row versions and
//updating the updated rows. This will keep the selected row in the view.
//Updated: August 29, 2005
//Checks for duplicate load number within the same carrier during the past and future 7 days.
try {
//Determine changes made to the trips in this ship schedule
ShipScheduleDS trips = (ShipScheduleDS)this.mTrips.GetChanges(DataRowState.Modified);
if (trips != null && trips.ShipScheduleTable.Rows.Count > 0)
{
//Update each modified trip
foreach (ShipScheduleDS.ShipScheduleTableRow row in trips.ShipScheduleTable.Rows)
{
//Check to see if load# or carrier has changed; if so, then make sure it's unique within the same
//carrier (updated once if it's updated along with load#) and during the past one week schedule
if (isLoadNumberOrCarrierChanged(row))
{
DataSet loadNumberDS = this.mMediator.FillDataset(Lib.USP_TRIP, Lib.TBL_TRIP, new object[] { row.ScheduleDate, System.DBNull.Value, row.LoadNumber.Trim() });
if (loadNumberDS.Tables[0].Rows.Count > 0)
{
throw new DuplicateLoadNumberException("Duplicate load# found in ship schedule for " + loadNumberDS.Tables[0].Rows[0][1].ToString() + ".");
}
}
//Save trip details
ShipScheduleDS _trip = updateTrip(populateTrip(row));
try {
//Refresh the details of the current trip (instead of a full refresh)
ShipScheduleDS.ShipScheduleTableRow trip = this.mTrips.ShipScheduleTable.FindByTripID(row.TripID);
trip.SCDERowVersion = _trip.ShipScheduleTripTable[0].RowVersion;
trip.S1RowVersion = _trip.ShipScheduleStopTable[0].RowVersion;
if (_trip.ShipScheduleStopTable.Rows.Count == 2)
{
trip.S2RowVersion = _trip.ShipScheduleStopTable[1].RowVersion;
}
this.mTrips.AcceptChanges();
}
catch (Exception ex) { throw new ApplicationException("Failed to partially refresh the ship schedule.", ex); }
}
//Refresh(); Doing partial refresh above for performance reasons (i.e. cell editing)
}
}
catch (DuplicateLoadNumberException ex) { throw ex; }
catch (ApplicationException ex) { throw ex; }
catch (Exception ex) { throw new ApplicationException("Failed to update ship schedule.", ex); }
}
public static ShipSchedule SchedulesAdd(long sortCenterID, string sortCenter, DateTime scheduleDate)
{
//Add a new ship schedule
ShipSchedule schedule = null;
try {
//Add and update collection
ShipScheduleDS ds = new ShipScheduleDS();
ds.ShipScheduleViewTable.AddShipScheduleViewTableRow("", sortCenterID, sortCenter, scheduleDate, DateTime.Now, Environment.UserName);
schedule = new ShipSchedule(ds.ShipScheduleViewTable[0], Mediator);
schedule.Changed += new EventHandler(OnShipScheduleChanged);
schedule.Create();
}
catch (Exception ex) { throw new ApplicationException("Failed to add new ship schedule.", ex); }
return(schedule);
}
public ShipSchedule(ShipScheduleDS.ShipScheduleViewTableRow schedule)
{
//Constructor
try {
if (schedule != null)
{
this.mScheduleID = schedule.ScheduleID;
this.mSortCenterID = schedule.SortCenterID;
this.mSortCenter = schedule.SortCenter;
this.mScheduleDate = schedule.ScheduleDate;
}
this.mTrips = new ShipScheduleDS();
this.mTemplates = new ShipScheduleDS();
Refresh();
}
catch (ApplicationException ex) { throw ex; }
catch (Exception ex) { throw new ApplicationException("Failed to instantiate a new ship schedule.", ex); }
}
public static ShipScheduleTrip GetEarlierTripFromAPriorSchedule(string tripID, string freightID)
{
//Return an earlier trip from a schedule prior to the one specified
ShipScheduleTrip earlierTrip = null;
try {
//Get all earlier trips (open, not cancelled)
DataSet ds = App.Mediator.FillDataset(USP_SHIPSCHEDULE_PRIORTRIPS, TBL_SHIPSCHEDULE_PRIORTRIPS, new object[] { tripID, freightID });
if (ds.Tables[TBL_SHIPSCHEDULE_PRIORTRIPS].Rows.Count > 0)
{
ShipScheduleDS schedule = new ShipScheduleDS();
schedule.Merge(ds);
DateTime date = _ScheduleDate.AddYears(-5);
int tag = 0;
for (int i = 0; i < schedule.ShipScheduleMasterTable.Rows.Count; i++)
{
//Select a trip with the most recent schedule date (not including this.mScheduleDate)
ShipScheduleDS.ShipScheduleMasterTableRow trip = (ShipScheduleDS.ShipScheduleMasterTableRow)schedule.ShipScheduleMasterTable.Rows[i];
if (trip.ScheduleDate.CompareTo(_ScheduleDate) < 0)
{
//Capture the most recent trip date
if (trip.ScheduleDate.CompareTo(date) > 0)
{
date = trip.ScheduleDate; tag = 0;
}
if (trip.ScheduleDate.CompareTo(date) == 0)
{
//Capture the trip taht is most recent and with the largest tag #
if (int.Parse(trip.Tag.Trim()) > tag)
{
tag = int.Parse(trip.Tag.Trim());
earlierTrip = new ShipScheduleTrip(trip);
}
}
}
}
}
}
catch (Exception ex) { throw new ApplicationException("Failed determine if an earlier ship schedule trip exists.", ex); }
return(earlierTrip);
}
public static ShipScheduleDS GetAvailableTrips(long agentTerminalID, DateTime dt)
{
//Get trips for the specified main zone and date that are available for assignment
ShipScheduleDS trips = null;
try {
//Get the schedule for the specified date
trips = new ShipScheduleDS();
DataSet ds = App.Mediator.FillDataset(USP_SHIPSCHEDULE_SCHEDULE, TBL_SHIPSCHEDULE_SCHEDULE, new object[] { dt });
if (ds.Tables[TBL_SHIPSCHEDULE_SCHEDULE].Rows.Count > 0)
{
//Filter for trips (both stops) matching the specified agent terminal and that are open for assignment
ShipScheduleDS s = new ShipScheduleDS();
s.Merge(ds);
ShipScheduleDS schedule = new ShipScheduleDS();
schedule.Merge(s.ShipScheduleMasterTable.Select("AgentTerminalID='" + agentTerminalID + "' OR S2AgentTerminalID=" + agentTerminalID));
trips.Merge(schedule.ShipScheduleMasterTable.Select("IsNull(FreightAssigned, #08/02/61#) = #08/02/61#"));
}
}
catch (Exception ex) { throw new ApplicationException("Failed to get ship schedule.", ex); }
return(trips);
}
public static ShipSchedule SchedulesArchiveItem(long sortCenterID, string sortCenter, DateTime scheduleDate)
{
//Return an archived ship schedule
ShipSchedule schedule = null;
try {
//
DataSet ds = Mediator.FillDataset(Lib.USP_SCHEDULES, Lib.TBL_SCHEDULES, new object[] { scheduleDate });
if (ds.Tables[Lib.TBL_SCHEDULES].Rows.Count > 0)
{
DataRow[] rows = ds.Tables[Lib.TBL_SCHEDULES].Select("SortCenterID=" + sortCenterID + " AND ScheduleDate='" + scheduleDate + "'");
if (rows.Length > 0)
{
ShipScheduleDS scheduleDS = new ShipScheduleDS();
scheduleDS.ShipScheduleViewTable.AddShipScheduleViewTableRow("", sortCenterID, sortCenter, scheduleDate, DateTime.Now, Environment.UserName);
schedule = new ShipSchedule(scheduleDS.ShipScheduleViewTable[0], Mediator);
schedule.Changed += new EventHandler(OnShipScheduleChanged);
}
}
}
catch (Exception ex) { throw ex; }
return(schedule);
}
//Interface
static ShipScheduleFactory()
{
//Constructor
_Schedules = new ShipScheduleDS();
}
public ShipScheduleTrip(ShipScheduleDS.ShipScheduleMasterTableRow trip, ShipScheduleDS.ShipScheduleDetailTableRow[] tls)
{
//Constructor
try {
this.mAssignedTLs = new ShipScheduleDS();
if (trip != null)
{
this._scheduleid = trip.ScheduleID;
this._sortcenterid = trip.SortCenterID;
if (!trip.IsSortCenterNull())
{
this._sortcenter = trip.SortCenter;
}
this._scheduledate = trip.ScheduleDate;
this._tripid = trip.TripID;
if (!trip.IsTemplateIDNull())
{
this._templateid = trip.TemplateID;
}
if (!trip.IsBolNumberNull())
{
this._bolnumber = trip.BolNumber;
}
if (!trip.IsCarrierServiceIDNull())
{
this._carrierserviceid = trip.CarrierServiceID;
}
if (!trip.IsCarrierNull())
{
this._carrier = trip.Carrier;
}
if (!trip.IsLoadNumberNull())
{
this._loadnumber = trip.LoadNumber;
}
if (!trip.IsTrailerIDNull())
{
this._trailerid = trip.TrailerID;
}
if (!trip.IsTrailerNumberNull())
{
this._trailernumber = trip.TrailerNumber;
}
if (!trip.IsTractorNumberNull())
{
this._tractornumber = trip.TractorNumber;
}
if (!trip.IsScheduledCloseNull())
{
this._scheduledclose = trip.ScheduledClose;
}
if (!trip.IsScheduledDepartureNull())
{
this._scheduleddeparture = trip.ScheduledDeparture;
}
if (!trip.IsIsMandatoryNull())
{
this._ismandatory = trip.IsMandatory;
}
if (!trip.IsFreightAssignedNull())
{
this._freightassigned = trip.FreightAssigned;
}
if (!trip.IsTrailerCompleteNull())
{
this._trailercomplete = trip.TrailerComplete;
}
if (!trip.IsPaperworkCompleteNull())
{
this._paperworkcomplete = trip.PaperworkComplete;
}
if (!trip.IsTrailerDispatchedNull())
{
this._trailerdispatched = trip.TrailerDispatched;
}
if (!trip.IsCanceledNull())
{
this._canceled = trip.Canceled;
}
if (!trip.IsSCDEUserIDNull())
{
this._scdeuserid = trip.SCDEUserID;
}
if (!trip.IsSCDELastUpdatedNull())
{
this._scdelastupdated = trip.SCDELastUpdated;
}
if (!trip.IsSCDERowVersionNull())
{
this._scderowversion = trip.SCDERowVersion;
}
if (!trip.IsStopIDNull())
{
this._stopid = trip.StopID;
}
if (!trip.IsStopNumberNull())
{
this._stopnumber = trip.StopNumber;
}
if (!trip.IsAgentTerminalIDNull())
{
this._agentterminalid = trip.AgentTerminalID;
}
if (!trip.IsAgentNumberNull())
{
this._agentnumber = trip.AgentNumber;
}
if (!trip.IsMainZoneNull())
{
this._mainzone = trip.MainZone;
}
if (!trip.IsTagNull())
{
this._tag = trip.Tag;
}
if (!trip.IsNotesNull())
{
this._notes = trip.Notes;
}
if (!trip.IsScheduledArrivalNull())
{
this._scheduledarrival = trip.ScheduledArrival;
}
if (!trip.IsScheduledOFD1Null())
{
this._scheduledofd1 = trip.ScheduledOFD1;
}
if (!trip.IsS1UserIDNull())
{
this._s1userid = trip.S1UserID;
}
if (!trip.IsS1LastUpdatedNull())
{
this._s1lastupdated = trip.S1LastUpdated;
}
if (!trip.IsS1RowVersionNull())
{
this._s1rowversion = trip.S1RowVersion;
}
if (!trip.IsS2StopIDNull())
{
this._s2stopid = trip.S2StopID;
}
if (!trip.IsS2StopNumberNull())
{
this._s2stopnumber = trip.S2StopNumber;
}
if (!trip.IsS2AgentTerminalIDNull())
{
this._s2agentterminalid = trip.S2AgentTerminalID;
}
if (!trip.IsS2AgentNumberNull())
{
this._s2agentnumber = trip.S2AgentNumber;
}
if (!trip.IsS2MainZoneNull())
{
this._s2mainzone = trip.S2MainZone;
}
if (!trip.IsS2TagNull())
{
this._s2tag = trip.S2Tag;
}
if (!trip.IsS2NotesNull())
{
this._s2notes = trip.S2Notes;
}
if (!trip.IsS2ScheduledArrivalNull())
{
this._s2scheduledarrival = trip.S2ScheduledArrival;
}
if (!trip.IsS2ScheduledOFD1Null())
{
this._s2scheduledofd1 = trip.S2ScheduledOFD1;
}
if (!trip.IsS2UserIDNull())
{
this._s2userid = trip.S2UserID;
}
if (!trip.IsS2LastUpdatedNull())
{
this._s2lastupdated = trip.S2LastUpdated;
}
if (!trip.IsS2RowVersionNull())
{
this._s2rowversion = trip.S2RowVersion;
}
if (!trip.IsNextCarrierNull())
{
this._nextcarrier = trip.NextCarrier;
}
if (!trip.IsCarrierIDNull())
{
this._carrierid = trip.CarrierID;
}
if (tls != null)
{
ShipScheduleDS.ShipScheduleMasterTableRow _trip = this.mAssignedTLs.ShipScheduleMasterTable.NewShipScheduleMasterTableRow();
_trip.ScheduleID = this.ScheduleID;
_trip.SortCenterID = this.SortCenterID;
_trip.TripID = this.TripID;
this.mAssignedTLs.ShipScheduleMasterTable.AddShipScheduleMasterTableRow(_trip);
this.mAssignedTLs.Merge(tls);
}
}
}
catch (Exception ex) { throw new ApplicationException("Could not create a new ship schedule trip.", ex); }
}
private ShipScheduleDS populateTrip(ShipScheduleDS.ShipScheduleTableRow sourceRow)
{
//Updated with new fields - TractorNumber and FreightAssinged
ShipScheduleDS detailDS = new ShipScheduleDS();
//Trip
ShipScheduleDS.ShipScheduleTripTableRow tripRow = detailDS.ShipScheduleTripTable.NewShipScheduleTripTableRow();
tripRow.CarrierServiceID = sourceRow.CarrierServiceID;
tripRow.LastUpdated = System.DateTime.Now;
tripRow.LoadNumber = sourceRow.LoadNumber.Trim();
tripRow.TractorNumber = sourceRow.TractorNumber.Trim();
tripRow.DriverName = sourceRow.DriverName.Trim();
tripRow.RowVersion = sourceRow.SCDERowVersion;
tripRow.ScheduledClose = sourceRow.ScheduledClose;
tripRow.ScheduledDeparture = sourceRow.ScheduledDeparture;
tripRow.TrailerNumber = sourceRow.TrailerNumber.Trim();
tripRow.TripID = sourceRow.TripID;
tripRow.UserID = Environment.UserName;
if (!sourceRow.IsFreightAssignedNull())
{
tripRow.FreightAssigned = sourceRow.FreightAssigned;
}
if (!sourceRow.IsTrailerCompleteNull())
{
tripRow.TrailerComplete = sourceRow.TrailerComplete;
}
if (!sourceRow.IsTrailerDispatchedNull())
{
tripRow.TrailerDispatched = sourceRow.TrailerDispatched;
}
if (!sourceRow.IsPaperworkCompleteNull())
{
tripRow.PaperworkComplete = sourceRow.PaperworkComplete;
}
if (!sourceRow.IsCanceledNull())
{
tripRow.Canceled = sourceRow.Canceled;
}
detailDS.ShipScheduleTripTable.AddShipScheduleTripTableRow(tripRow);
//Associated stops
ShipScheduleDS.ShipScheduleStopTableRow stop1Row = detailDS.ShipScheduleStopTable.NewShipScheduleStopTableRow();
stop1Row.LastUpdated = System.DateTime.Now;
stop1Row.Notes = sourceRow.Notes;
stop1Row.RowVersion = sourceRow.S1RowVersion;
stop1Row.ScheduledArrival = sourceRow.ScheduledArrival;
stop1Row.ScheduledOFD1 = sourceRow.ScheduledOFD1;
stop1Row.StopID = sourceRow.StopID;
stop1Row.UserID = sourceRow.S1UserID;
detailDS.ShipScheduleStopTable.AddShipScheduleStopTableRow(stop1Row);
if (sourceRow.S2MainZone != null & sourceRow.S2MainZone.Trim() != "")
{
ShipScheduleDS.ShipScheduleStopTableRow stop2Row = detailDS.ShipScheduleStopTable.NewShipScheduleStopTableRow();
stop2Row.LastUpdated = System.DateTime.Now;
stop2Row.Notes = sourceRow.S2Notes;
stop2Row.RowVersion = sourceRow.S2RowVersion;
stop2Row.ScheduledArrival = sourceRow.S2ScheduledArrival;
stop2Row.ScheduledOFD1 = sourceRow.S2ScheduledOFD1;
stop2Row.StopID = sourceRow.S2StopID;
stop2Row.UserID = sourceRow.S2UserID;
detailDS.ShipScheduleStopTable.AddShipScheduleStopTableRow(stop2Row);
}
return(detailDS);
}
//Interface
static AgentLineHaulFactory()
{
//Constructor
_Trips = new ShipScheduleDS();
}
