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