private void CompareFileWithHMS(DataSet ExcelDataSet)
        {
            if (ExcelDataSet.Tables.Count == 0)
            {
                btnReload.Visible = false;
                return;
            }

            int jobCountPreInvoice  = 0;
            int totalRowsPreInvoice = ExcelDataSet.Tables[0].Rows.Count - 2;
            int totalDiscrepanies   = 0;
            int clientId            = 0;

            Facade.IJob facJob = new Facade.Job();

            DataSet ds = null;

            StringBuilder sb = new StringBuilder();

            ArrayList arrDiscrepancy = new ArrayList();

            // Client
            if (cboClient.Text != "")
            {
                clientId = Convert.ToInt32(cboClient.SelectedValue);
            }

            foreach (DataRow row in ExcelDataSet.Tables[0].Rows)
            {
                // Test format of row Approved Date by trying to convert the date ...
                // thats if it is a date.  If not that row is not a job!
                try
                {
                    // Checking whether the column is a date format
                    Convert.ToDateTime(row[3].ToString());

                    string shipmentNo = string.Empty;

                    shipmentNo = row[0].ToString();

                    ds = facJob.GetJobForLoadNo(shipmentNo, clientId);                      // Shipment No is actually Load No

                    if (ds.Tables[0].Rows.Count == 0)
                    {
                        // Found Discrepancy with Shipment No either not in system or incorrect shipment no
                        PreInvoiceDiscrepancy pid = new PreInvoiceDiscrepancy();

                        pid.JobId                    = 0;
                        pid.ShipmentNo               = shipmentNo.ToString();           // Displayed Pre-Invoice No
                        pid.PreInvoicePONumber       = row[1].ToString();
                        pid.PreInvoicePickUpLocation = row[2].ToString();
                        pid.PreInvoiceApprovedDate   = Convert.ToDateTime(row[3]);
                        pid.PreInvoiceJobCost        = Convert.ToDecimal(row[4]);
                        pid.HMSJobCost               = decimal.Parse("£0.00", NumberStyles.Currency);
                        pid.DiscrepancyNote          = "Discrepancy with the Shipment No, not found in Database or incorrectly given, please investigate.";

                        arrDiscrepancy.Add(pid);

                        totalDiscrepanies++;
                    }
                    else
                    {
                        // Check cost of job with the pre-invoice cost
                        if (decimal.Parse(ds.Tables[0].Rows[0]["ChargeAmount"].ToString(), NumberStyles.Currency) != decimal.Parse(row[4].ToString(), NumberStyles.Currency))
                        {
                            // Found Discrepancy with cost, add to list and highlight cost
                            PreInvoiceDiscrepancy pid = new PreInvoiceDiscrepancy();

                            pid.JobId                    = Convert.ToInt32(ds.Tables[0].Rows[0]["JobId"]);
                            pid.ShipmentNo               = ds.Tables[0].Rows[0]["LoadNo"].ToString();               // Display HMS Load No
                            pid.PreInvoicePONumber       = row[1].ToString();
                            pid.PreInvoicePickUpLocation = row[2].ToString();
                            pid.PreInvoiceApprovedDate   = Convert.ToDateTime(row[3]);
                            pid.PreInvoiceJobCost        = Convert.ToDecimal(row[4]);
                            pid.HMSJobCost               = Convert.ToDecimal(ds.Tables[0].Rows[0]["ChargeAmount"]);
                            pid.DiscrepancyNote          = "Discrepancy with the Amount";

                            arrDiscrepancy.Add(pid);

                            totalDiscrepanies++;
                        }

                        // Check State of job within HMS
                        if ((eJobState)Enum.Parse(typeof(eJobState), (Convert.ToInt32(ds.Tables[0].Rows[0]["JobStateId"])).ToString()) != eJobState.ReadyToInvoice)
                        {
                            // Found Discrepancy with state of the job, add to list and highlight job state
                            PreInvoiceDiscrepancy pid = new PreInvoiceDiscrepancy();

                            pid.JobId                    = Convert.ToInt32(ds.Tables[0].Rows[0]["JobId"]);
                            pid.ShipmentNo               = ds.Tables[0].Rows[0]["LoadNo"].ToString();               // Display HMS Load No
                            pid.PreInvoicePONumber       = row[1].ToString();
                            pid.PreInvoicePickUpLocation = row[2].ToString();
                            pid.PreInvoiceApprovedDate   = Convert.ToDateTime(row[3]);
                            pid.PreInvoiceJobCost        = decimal.Parse(row[4].ToString(), NumberStyles.Currency);                         //Convert.ToDecimal(row[4]);
                            pid.HMSJobCost               = Convert.ToDecimal(ds.Tables[0].Rows[0]["ChargeAmount"]);
                            pid.DiscrepancyNote          = "Discrepancy with the state of the Job, please review.";

                            arrDiscrepancy.Add(pid);

                            totalDiscrepanies++;
                        }

                        // Add JobId to array list
                        m_arrJobId.Add(ds.Tables[0].Rows[0]["JobId"].ToString());
                    }
                    // Count how many jobs are in this Pre Invoice
                    jobCountPreInvoice++;
                }
                catch {}
                finally {}
            }

            string totalCostPreInvoice = string.Empty;

            try
            {
                // Row count and - 2 to get Total Cost (Don't Ask Why Minus 2 rows)
                totalCostPreInvoice = decimal.Parse(ExcelDataSet.Tables[0].Rows[totalRowsPreInvoice][4].ToString(), NumberStyles.Currency).ToString();
            }
            catch
            {
                // Row count and - 3 to get Total Cost (Don't Ask Why Minus 3 rows)
                totalCostPreInvoice = decimal.Parse(ExcelDataSet.Tables[0].Rows[totalRowsPreInvoice - 1][4].ToString(), NumberStyles.Currency).ToString();
            }

            // Apply Discrepancies to Datagrid
            if (arrDiscrepancy.Count != 0)
            {
                dgJobDiscrepancies.DataSource = arrDiscrepancy;
                dgJobDiscrepancies.DataBind();
                btnSave.Visible = true;
            }
            else
            {
                if (m_arrJobId.Count != 0)
                {
                    //#15867 J.Steele
                    //Clear the Invoice Session variables before setting the specific ones
                    Utilities.ClearInvoiceSession();

                    Session["ClientId"]     = Convert.ToInt32(cboClient.SelectedValue);
                    Session["ClientName"]   = cboClient.Text;
                    Session["JobIds"]       = m_arrJobId;
                    Session["FileLocation"] = m_baseLocation;
                    Session["FileName"]     = m_fileNameOnServer;
                    Server.Transfer("addupdateinvoice.aspx");
                }
            }

            ViewState[C_BASE_LOCATION_VS]       = m_baseLocation;
            ViewState[C_FILE_NAME_ON_SERVER_VS] = m_fileNameOnServer;

            lblPreInvoiceOverview.Text = "There are " + jobCountPreInvoice + " jobs for this pre invoice and " + totalDiscrepanies + " job discrepancies. The total amount for this pre invoice is £" + totalCostPreInvoice.ToString();
        }