private List <PriceErrorList> IsHorizontalSourceValidate(System.Data.DataTable dtable)
{
List <PriceErrorList> listerror = new List <PriceErrorList>();
// step 1 validate product id duplication
if (dtable.Rows.Count > 0)
{
if (stateid == 3 || stateid == 2)
{
foreach (DataRow dr in dtable.Rows)
{
#region validate productid
if (dr["productid"] == null || dr["productid"].ToString() == "")
{
PriceErrorList em = new PriceErrorList();
em.ProductID = "";
em.MessageText = @"ProductID is missing.";
DateTime tempdt;
try
{
tempdt = DateTime.Parse(dr["effectivedate"].ToString());
em.EffectiveDateString = tempdt.ToString("dd/MM/yyyy");
}
catch (Exception exx2)
{
tempdt = DateTime.MinValue;
em.EffectiveDateString = "";
}
em.ProductID = dr["productid"].ToString();
em.EffectiveDate = tempdt;
em.EffectiveDate = tempdt;
listerror.Add(em);
}
if (dr["productid"] != null && dr["productid"].ToString() != "" && dr["productid"].ToString().Length > 15)
{
PriceErrorList em = new PriceErrorList();
em.ProductID = dr["productid"].ToString();
em.MessageText = @"Product ID length over 15 characters.";
DateTime tempdt;
try
{
tempdt = DateTime.Parse(dr["effectivedate"].ToString());
em.EffectiveDateString = tempdt.ToString("dd/MM/yyyy");
}
catch (Exception exx2)
{
tempdt = DateTime.MinValue;
em.EffectiveDateString = "";
}
em.EffectiveDate = tempdt;
listerror.Add(em);
}
#endregion
#region validate effective date
try
{
DateTime eff = DateTime.Parse(dr["effectivedate"].ToString());
}
catch (Exception ex)
{
PriceErrorList em = new PriceErrorList();
em.ProductID = dr["productid"].ToString();
em.Region = "";
DateTime tempdt;
try
{
tempdt = DateTime.Parse(dr["effectivedate"].ToString());
em.EffectiveDateString = tempdt.ToString("dd/MM/yyyy");
}
catch (Exception exx2)
{
tempdt = DateTime.MinValue;
em.EffectiveDateString = "";
}
em.EffectiveDate = tempdt;
em.MessageText = "Effective date is invalid.";
listerror.Add(em);
}
#endregion
#region validate derivedcost value
if (dr["derivedcost"].ToString().ToUpper() != "Y" && dr["derivedcost"].ToString().ToUpper() != "N")
{
PriceErrorList em = new PriceErrorList();
em.ProductID = dr["productid"].ToString();
em.Region = "";
DateTime tempdt;
try
{
tempdt = DateTime.Parse(dr["effectivedate"].ToString());
em.EffectiveDateString = tempdt.ToString("dd/MM/yyyy");
}
catch (Exception exx2)
{
tempdt = DateTime.MinValue;
em.EffectiveDateString = "";
}
em.ProductID = dr["productid"].ToString();
em.EffectiveDate = tempdt;
em.MessageText = "Derived cost '" + dr["derivedcost"].ToString() + "' is invalid. The value should be Y or N.";
listerror.Add(em);
}
#endregion
}
#region validate productid
string tempname = "";
foreach (DataRow dr in dtable.Rows)
{
#endregion
foreach (CommonResource.Region s in cr.SQSRegion)
{
#region validate direct build cost
tempname = s.RegionCode.TrimEnd().TrimStart() + "_DirectBuildCost";
try
{
double cost = double.Parse(dr[tempname].ToString().Replace("$", "").Replace(",", "").TrimEnd().TrimStart());
}
catch (Exception ex)
{
PriceErrorList em = new PriceErrorList();
em.ProductID = dr["productid"].ToString();
em.Region = s.RegionName;
DateTime tempdt;
try
{
tempdt = DateTime.Parse(dr["effectivedate"].ToString());
em.EffectiveDateString = tempdt.ToString("dd/MM/yyyy");
}
catch (Exception exx2)
{
tempdt = DateTime.MinValue;
em.EffectiveDateString = "";
}
em.ProductID = dr["productid"].ToString();
em.EffectiveDate = tempdt;
em.EffectiveDate = tempdt;
em.MessageText = @"Direct Build Cost " + dr[tempname].ToString() + @" is invalid. Please enter a number.";
listerror.Add(em);
}
#endregion
#region validate BTP cost
//tempname = s.RegionCode.TrimEnd().TrimStart() + "_CMAPercent";
//try
//{
// double cmapercent = double.Parse(dr[tempname].ToString().TrimEnd().TrimStart());
//}
//catch (Exception ex)
//{
// PriceErrorList em = new PriceErrorList();
// em.ProductID = dr["productid"].ToString();
// em.Region = s.RegionName;
// DateTime tempdt;
// try
// {
// tempdt = DateTime.Parse(dr["effectivedate"].ToString());
// em.EffectiveDateString = tempdt.ToString("dd/MM/yyyy");
// }
// catch (Exception exx2)
// {
// tempdt = DateTime.MinValue;
// em.EffectiveDateString = "";
// }
// em.ProductID = dr["productid"].ToString();
// em.EffectiveDate = tempdt;
// em.EffectiveDate = tempdt;
// em.MessageText = @"CMA percent " + dr[tempname].ToString() + @" is invalid. Please enter a number.";
// listerror.Add(em);
//}
tempname = s.RegionCode.TrimEnd().TrimStart() + "_BuildingTransferCost";
try
{
double cost = double.Parse(dr[tempname].ToString().Replace("$", "").Replace(",", "").TrimEnd().TrimStart());
}
catch (Exception ex)
{
PriceErrorList em = new PriceErrorList();
em.ProductID = dr["productid"].ToString();
em.Region = s.RegionName;
DateTime tempdt;
try
{
tempdt = DateTime.Parse(dr["effectivedate"].ToString());
em.EffectiveDateString = tempdt.ToString("dd/MM/yyyy");
}
catch (Exception exx2)
{
tempdt = DateTime.MinValue;
em.EffectiveDateString = "";
}
em.ProductID = dr["productid"].ToString();
em.EffectiveDate = tempdt;
em.EffectiveDate = tempdt;
em.MessageText = @"Building Transfer Cost " + dr[tempname].ToString() + @" is invalid. Please enter a number.";
listerror.Add(em);
}
#endregion
#region validate target margin percent
tempname = s.RegionCode.TrimEnd().TrimStart() + "_TargetMarginPercent";
try
{
double targetmarginpercent = double.Parse(dr[tempname].ToString().TrimEnd().TrimStart());
}
catch (Exception ex)
{
PriceErrorList em = new PriceErrorList();
em.ProductID = dr["productid"].ToString();
em.Region = s.RegionName;
DateTime tempdt;
try
{
tempdt = DateTime.Parse(dr["effectivedate"].ToString());
em.EffectiveDateString = tempdt.ToString("dd/MM/yyyy");
}
catch (Exception exx2)
{
tempdt = DateTime.MinValue;
em.EffectiveDateString = "";
}
em.ProductID = dr["productid"].ToString();
em.EffectiveDate = tempdt;
em.EffectiveDate = tempdt;
em.MessageText = @"Target Margin Percent " + dr[tempname].ToString() + @" is invalid. Please enter a number.";
listerror.Add(em);
}
#endregion
#region validate sell price
tempname = s.RegionCode.TrimEnd().TrimStart() + "_SellPrice";
try
{
double sell = double.Parse(dr[tempname].ToString().Replace("$", "").Replace(",", "").TrimEnd().TrimStart());
}
catch (Exception ex)
{
PriceErrorList em = new PriceErrorList();
em.ProductID = dr["productid"].ToString();
em.Region = s.RegionName;
DateTime tempdt;
try
{
tempdt = DateTime.Parse(dr["effectivedate"].ToString());
em.EffectiveDateString = tempdt.ToString("dd/MM/yyyy");
}
catch (Exception exx2)
{
tempdt = DateTime.MinValue;
em.EffectiveDateString = "";
}
em.ProductID = dr["productid"].ToString();
em.EffectiveDate = tempdt;
em.EffectiveDate = tempdt;
em.MessageText = @"Sell price " + dr[tempname].ToString() + @" is invalid. Please enter a number.";
listerror.Add(em);
}
#endregion
}
}
#region validate duplication based on productid, effectivedate and regionid
var ss = from c in dtable.AsEnumerable()
group c by new
{
productid = c["productid"],
effectivedate = c["effectivedate"]
} into newdr
where newdr.Count() > 1
select new
{
productid = newdr.Key.productid,
effectivedate = newdr.Key.effectivedate,
totalcout = newdr.Count()
};
foreach (var row in ss)
{
PriceErrorList em = new PriceErrorList();
em.ProductID = row.productid.ToString();
DateTime tempdt;
try
{
tempdt = (DateTime)row.effectivedate;
em.EffectiveDateString = tempdt.ToString("dd/MM/yyyy");
}
catch (Exception exx2)
{
tempdt = DateTime.MinValue;
em.EffectiveDateString = "";
}
em.EffectiveDate = tempdt;
em.MessageText = row.totalcout.ToString() + " Duplicate records found.";
listerror.Add(em);
}
#endregion
#region validate against database to check duplication
if (listerror.Count == 0)
{
DataTable newformat = cr.ConvertToImportFormatTable(dtable);
DataSet ds = ValidatePriceSheetWithDatabase(newformat);
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
PriceErrorList em = new PriceErrorList();
em.ProductID = dr["productid"].ToString();
em.Region = dr["regionname"].ToString();
DateTime tempdt;
try
{
tempdt = DateTime.Parse(dr["effectivedate"].ToString());
em.EffectiveDateString = tempdt.ToString("dd/MM/yyyy");
}
catch (Exception exx2)
{
tempdt = DateTime.MinValue;
em.EffectiveDateString = "";
}
em.EffectiveDate = tempdt;
em.MessageText = "The price exists based on product, region and effective date. Please either deactivate current price or remove this price from import file and try again.";
listerror.Add(em);
}
}
//else
//{
// PriceErrorList em = new PriceErrorList();
// em.ProductID = "";
// em.MessageText = @"Please log on as QLD or NSW user to import this file.";
// listerror.Add(em);
//}
}
#endregion
}
else
{
PriceErrorList em = new PriceErrorList();
em.ProductID = "";
em.MessageText = @"The excel file is blank.";
listerror.Add(em);
}
}
return(listerror);
}