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