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 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);
}