public FreightSyncDS GetUnsortedFreight()
{
//Get all unsorted freight from AS400
FreightSyncDS ds = null;
try {
ds = new FreightSyncDS();
string SQL = "SELECT BELRCD AS ID, CAST(BECKNB AS INT) AS TDSNumber, CAST(BECHCD AS INT) AS TerminalID,BEAACD AS ClientNumber, " +
"BEABCD AS DivisionNumber, BEB2CD AS VendorNumber, BEB4CD AS AgentNumber , CAST(BEAQDT + 19000000 AS CHAR(8)) AS PickupDate, " +
"CAST(BEA2NB AS INT) AS PickupNumber, CAST(BECBDT + 19000000 AS CHAR(8)) AS ReceiveDate, BEEVCD AS VendorKey, " +
"CAST(BEG4NB AS INT) AS Cartons, CAST(BEG5NB AS INT) AS Pallets, CAST(BECMNB AS INT) AS CarrierNumber, CAST(BECLNB AS INT) AS DriverNumber, " +
"BEBGTX AS TrailerNumber, BEDYTX AS StorageTrailerNumber, BEBITX AS CurrentLocation, BEFKST AS FloorStatus, BEBHTX AS SealNumber, BEC5ST AS UnloadedStatus, " +
"CAST(CASE WHEN BEB9DT=0 THEN '' ELSE CAST(BEB9DT + 19000000 AS VARCHAR(8)) END AS CHAR(8)) AS StartSortDate, " +
"CAST(CASE WHEN BEANTM=0 THEN '' ELSE CAST(BEANTM AS CHAR(6)) END AS CHAR(6)) AS StartSortTime, " +
"CAST(CASE WHEN BECADT=0 THEN '' ELSE CAST(BECADT + 19000000 AS VARCHAR(8)) END AS CHAR(8)) AS StopSortDate, " +
"CAST(CASE WHEN BEAOTM=0 THEN '' ELSE CAST(BEAOTM AS CHAR(6)) END AS CHAR(6)) AS StopSortTime, " +
"A4LVCD AS PickupID, BEAADT AS LastUpdatedDate, BEAATM AS LastUpdatedTime, " +
"CASE WHEN BEB2CD='99999' THEN 1 WHEN BEB2CD<>'99999' AND BEGYDT<>0 AND BEGJDT<>0 THEN 1 ELSE 0 END AS IsSortable" +
"FROM TSRTDTA.TSBECPP " +
"INNER JOIN TSRTDTA.TSA4CPP ON BEB2CD=A4B2CD AND BEB4CD=A4B4CD AND BEAQDT=A4AQDT AND BEA2NB=A4A2NB AND BEAACD=A4AACD AND BEABCD=A4ABCD " +
"WHERE (BEB9DT=0) " +
"AND NOT (BEB2CD='99999' AND BEB4CD='9999') ORDER BY LastUpdatedDate, LastUpdatedTime ";
Debug.Write("GetUnsortedFreight(): SQL=" + SQL + "\n");
ds.Merge(base.FillDataSet(SQL, TBL_FREIGHTSYNC), false, MissingSchemaAction.Ignore);
}
catch (Exception ex) { throw new ApplicationException("Failed to get unsorted freight from AS400.", ex); }
return(ds);
}
public DateTime StartSortingShipment(string shipmentID)
{
//Set start sort date and status for this shipment
//Update freight status with StartSort date/time in the AS400 database; if freight is
//found, then check StartSort; if it exists, then don't change anything in AS400 but
//update StartSort in LAN database.
//If StartSort date/time in AS400 is updated, then update LastUpdate date/time; if
//successfull, then update StartSort in LAN database.
//NOTE: Don't touch LastUpdate date/time in LAN- synchronization takes care of this.
//Assignment allowed only after status is changed successfully in both AS400 & LAN databases.
FreightSyncDS ds = null;
bool bStarted = false;
DateTime dt = DateTime.Now;
try {
//On/off switch
if (FreightSyncSvc.AS400Login == "")
{
return(DateTime.Now);
}
//Get the shipment from ths AS400 database
ds = this.mAS400Data.GetShipment(shipmentID);
if (ds.FreightSyncDSTable.Rows.Count == 0)
{
//Shipment doesn't exist in AS400 (removed manually?); remove from LAN database
this.mSQLData.DeleteFreight(shipmentID);
throw new ApplicationException(EX_FREIGHT_NOTFOUND);
}
else
{
//Shipment exists in AS400- update startsort and lastupdated if not set
if (!ds.FreightSyncDSTable[0].IsStartSortDateNull())
{
if (ds.FreightSyncDSTable[0].StartSortDate != "")
{
//Sort started in AS400 record; ensure same date/time in LAN
string sDate = ds.FreightSyncDSTable[0].StartSortDate;
string sTime = ds.FreightSyncDSTable[0].StartSortTime.PadLeft(6, '0');
dt = new DateTime(Convert.ToInt32(sDate.Substring(0, 4)), Convert.ToInt32(sDate.Substring(4, 2)), Convert.ToInt32(sDate.Substring(6, 2)), Convert.ToInt32(sTime.Substring(0, 2)), Convert.ToInt32(sTime.Substring(2, 2)), Convert.ToInt32(sTime.Substring(4, 2)));
bStarted = true;
}
}
if (!bStarted)
{
this.mAS400Data.UpdateShipmentSortStart(shipmentID, dt);
}
}
}
catch (ApplicationException ex) { throw ex; }
catch (Exception ex) { throw new ApplicationException("Failed to start sorting shipment #" + shipmentID + ".", ex); }
return(dt);
}
public FreightSyncDS GetDeletedFreight(int date, int time)
{
//Return all AS400 deleted freight
FreightSyncDS ds = null;
try {
ds = new FreightSyncDS();
string SQL = "SELECT JILTCD AS ID " +
"FROM TSRTDTA.LTJICPP " +
"WHERE ((JIAADT=" + date + " AND JIAATM >=" + time + ") OR " +
"JIAADT> " + date + ")";
Debug.Write("GetDeletedFreight(): SQL=" + SQL + "\n");
ds.Merge(base.FillDataSet(SQL, TBL_FREIGHTSYNC), false, MissingSchemaAction.Ignore);
}
catch (Exception ex) { throw new ApplicationException("Failed to get deleted freight from AS400.", ex); }
return(ds);
}
public FreightSyncDS GetShipment(string freightID)
{
//Get details of a shipment from the AS400 database
FreightSyncDS ds = null;
try {
ds = new FreightSyncDS();
string SQL = "SELECT BELRCD AS ID, " +
"CAST(CASE WHEN BEB9DT=0 THEN '' ELSE CAST(BEB9DT + 19000000 AS VARCHAR(8)) END AS CHAR(8)) AS StartSortDate, " +
"CAST(CASE WHEN BEANTM=0 THEN '' ELSE CAST(BEANTM AS CHAR(6)) END AS CHAR(6)) AS StartSortTime, " +
"CAST(CASE WHEN BECADT=0 THEN '' ELSE CAST(BECADT + 19000000 AS VARCHAR(8)) END AS CHAR(8)) AS StopSortDate, " +
"CAST(CASE WHEN BEAOTM=0 THEN '' ELSE CAST(BEAOTM AS CHAR(6)) END AS CHAR(6)) AS StopSortTime " +
"FROM TSRTDTA.TSBECPP " +
"WHERE BELRCD ='" + freightID + "'";
Debug.Write("GetShipment(): SQL=" + SQL + "\n");
ds.Merge(FillDataSet(SQL, TBL_FREIGHTSYNC), false, MissingSchemaAction.Ignore);
}
catch (Exception ex) { throw new ApplicationException("Failed to get shipment details for #" + freightID + " from AS400.", ex); }
return(ds);
}
public void SyncFreight(SyncType syncType)
{
//Sync all freight
int date = 0; int time = 0;
//On/off switch
if (!AS400Sync || FreightSyncSvc.AS400Login == "")
{
return;
}
switch (syncType)
{
case SyncType.All:
try {
//Get latest date\time from the local Freight table
SQLData.GetLastUpdatedDateTime(ref date, ref time);
//Fetch data from AS400 Freight table; and create a typed FreightSyncDS dataset which will
//be used to update LAN Freight table
FreightSyncDS dsUpdates = this.mAS400Data.GetFreight(date, time);
foreach (FreightSyncDS.FreightSyncDSTableRow row in dsUpdates.FreightSyncDSTable.Rows)
{
if (this.mSQLData.ReadFreight(row.ID).Tables[TBL_FREIGHTSYNC].Rows.Count > 0)
{
this.mSQLData.UpdateFreight(row);
}
else
{
this.mSQLData.CreateFreight(row);
}
}
}
catch (ApplicationException ex) { throw ex; }
catch (Exception ex) { throw new ApplicationException(EX_FREIGHT_SYNC_FAILED, ex); }
try {
//Check AS400 for deleted freight; if found, then delete from local Freight table
FreightSyncDS dsDeletes = this.mAS400Data.GetDeletedFreight(date, time);
foreach (FreightSyncDS.FreightSyncDSTableRow row in dsDeletes.FreightSyncDSTable.Rows)
{
this.mSQLData.DeleteFreight(row.ID);
}
}
catch (ApplicationException ex) { throw ex; }
catch (Exception ex) { throw new ApplicationException(EX_FREIGHT_DELETE_SYNC_FAILED, ex); }
break;
case SyncType.Unsorted:
try {
//Fetch data from AS400 Freight table and create a typed FreightSyncDS dataset which will be used to update LAN Freight table
FreightSyncDS dsUnsorted = this.mAS400Data.GetUnsortedFreight();
foreach (FreightSyncDS.FreightSyncDSTableRow row in dsUnsorted.FreightSyncDSTable.Rows)
{
if (this.mSQLData.ReadFreight(row.ID).Tables[TBL_FREIGHTSYNC].Rows.Count > 0)
{
this.mSQLData.UpdateFreight(row);
}
else
{
this.mSQLData.CreateFreight(row);
}
}
}
catch (ApplicationException ex) { throw ex; }
catch (Exception ex) { throw new ApplicationException(EX_FREIGHT_UNSORT_SYNC_FAILED, ex); }
break;
}
}