private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
List <PriceErrorList> list = new List <PriceErrorList>();
SQSAdminServiceClient client = new SQSAdminServiceClient();
client.Endpoint.Address = new System.ServiceModel.EndpointAddress(CommonVariables.WcfEndpoint);
try
{
CallParameter cp3 = (CallParameter)e.Argument;
ExcelTable = cr.readExcelFileToDataTable(cp3.FileName, "PriceSheet$");
if (ExcelTable != null && (ExcelTable.Columns.Count == 8 || ExcelTable.Columns.Count == 68 || ExcelTable.Columns.Count == 16))
{
if (ExcelTable.Columns.Count == 8)// vertical format
{
if (ExcelTable.Columns[0].ColumnName.Trim().ToUpper() != "PRODUCTID" ||
ExcelTable.Columns[1].ColumnName.Trim().ToUpper() != "REGIONNAME" ||
ExcelTable.Columns[2].ColumnName.Trim().ToUpper() != "EFFECTIVEDATE" ||
ExcelTable.Columns[3].ColumnName.Trim().ToUpper() != "DIRECTBUILDCOST" ||
ExcelTable.Columns[4].ColumnName.Trim().ToUpper() != "BUILDINGTRANSFERCOST" ||
ExcelTable.Columns[5].ColumnName.Trim().ToUpper() != "SELLPRICE" ||
ExcelTable.Columns[6].ColumnName.Trim().ToUpper() != "TARGETMARGINPERCENT" ||
ExcelTable.Columns[7].ColumnName.Trim().ToUpper() != "DERIVEDCOST"
)
{
PriceErrorList l = new PriceErrorList();
l.MessageText = "Column name or number does not match standard format. Please follow the sample data sheet format.";
list.Add(l);
e.Result = list;
}
else
{
e.Result = IsSourceValidate(ExcelTable);
}
}
else if ((ExcelTable.Columns.Count == 16 && stateid == 2) || (ExcelTable.Columns.Count == 68 && stateid == 3))// NSW/QLD horizontal format
{
DataSet dsTemp = client.SQSAdmin_Generic_GetRegionList(stateid);
string[] colnamearray = new string[dsTemp.Tables[0].Rows.Count * 4 + 4];
int idx = 3;
foreach (DataRow dr in dsTemp.Tables[0].Rows)
{
colnamearray[idx] = dr["BCCode"].ToString().TrimStart().TrimEnd() + "_DIRECTBUILDCOST";
colnamearray[idx + dsTemp.Tables[0].Rows.Count] = dr["BCCode"].ToString().TrimStart().TrimEnd() + "_BUILDINGTRANSFERCOST";
colnamearray[idx + dsTemp.Tables[0].Rows.Count * 2] = dr["BCCode"].ToString().TrimStart().TrimEnd() + "_TARGETMARGINPERCENT";
colnamearray[idx + dsTemp.Tables[0].Rows.Count * 3] = dr["BCCode"].ToString().TrimStart().TrimEnd() + "_SELLPRICE";
idx++;
}
if (ExcelTable.Columns[0].ColumnName.Trim().ToUpper() != "PRODUCTID" ||
ExcelTable.Columns[1].ColumnName.Trim().ToUpper() != "HOUSETYPE" ||
ExcelTable.Columns[2].ColumnName.Trim().ToUpper() != "EFFECTIVEDATE" ||
ExcelTable.Columns[ExcelTable.Columns.Count - 1].ColumnName.Trim().ToUpper() != "DERIVEDCOST")
{
PriceErrorList l = new PriceErrorList();
l.MessageText = "Column name or number does not match standard format.";
list.Add(l);
e.Result = list;
}
else
{
for (int i = 3; i < ExcelTable.Columns.Count - 2; i++)
{
if (ExcelTable.Columns[i].ColumnName.Trim().ToUpper() != colnamearray[i])
{
PriceErrorList l = new PriceErrorList();
l.MessageText = "Column name or number does not match standard format.";
list.Add(l);
e.Result = list;
break;
}
}
}
e.Result = IsHorizontalSourceValidate(ExcelTable);
}
else
{
PriceErrorList l = new PriceErrorList();
l.MessageText = "Column name or number does not match standard format.";
list.Add(l);
e.Result = list;
}
}
else
{
PriceErrorList l = new PriceErrorList();
l.MessageText = "Excel provided does not match the standard format.";
list.Add(l);
e.Result = list;
}
}
catch (Exception ex)
{
PriceErrorList l = new PriceErrorList();
l.MessageText = ex.Message;
list.Add(l);
e.Result = list;
}
}
