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