Beispiel #1
0
        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);
        }
Beispiel #2
0
        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);
        }
Beispiel #3
0
        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);
        }
Beispiel #4
0
        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);
        }
Beispiel #5
0
        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;
            }
        }