private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
        {
            List <PriceErrorList> list = new List <PriceErrorList>();

            try
            {
                CallParameter cp3 = (CallParameter)e.Argument;
                ExcelTable = cr.readExcelFileToDataTable(cp3.FileName, "QuantitySheet$");

                if (ExcelTable != null && ExcelTable.Columns.Count == 3)
                {
                    if (ExcelTable.Columns[0].ColumnName.Trim().ToUpper() != "HOMEPRODUCTID" ||
                        ExcelTable.Columns[1].ColumnName.Trim().ToUpper() != "PRODUCTAREAGROUPID" ||
                        ExcelTable.Columns[2].ColumnName.Trim().ToUpper() != "QUANTITY"
                        )
                    {
                        PriceErrorList l = new PriceErrorList();
                        l.MessageText = "Column name or number does not match standard format.";
                        list.Add(l);
                        e.Result = list;
                    }
                    else
                    {
                        e.Result = IsSourceValidate(ExcelTable);
                    }
                }
                else
                {
                    imagebox.Visibility = Visibility.Collapsed;
                    MessageBox.Show("Can not find correct format sheet to import.");
                    btnContinue.IsEnabled = false;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                btnContinue.IsEnabled = false;
            }
        }
        private List <PriceErrorList> IsSourceValidate(System.Data.DataTable dtable)
        {
            List <PriceErrorList> listerror = new List <PriceErrorList>();

            // step 1 validate product id duplication

            if (dtable.Rows.Count > 0)
            {
                foreach (DataRow dr in dtable.Rows)
                {
                    if (dr["HomeProductID"] == null || dr["HomeProductID"].ToString() == ""
                        //dr["ProductAreaGroupID"] == null || dr["ProductAreaGroupID"].ToString() == "" ||
                        //dr["Quantity"] == null || dr["Quantity"].ToString() == ""
                        )
                    {
                        PriceErrorList em = new PriceErrorList();

                        em.ProductID   = dr["HomeProductID"].ToString();
                        em.MessageText = @"There are some missing value for this home.";
                        em.goAhead     = false;
                        listerror.Add(em);
                    }

                    try
                    {
                        int eff = int.Parse(dr["productareagroupid"].ToString());
                    }
                    catch (Exception ex)
                    {
                        PriceErrorList em = new PriceErrorList();
                        em.ProductID          = dr["HomeProductID"].ToString();
                        em.ProductAreaGroupID = dr["productareagroupid"].ToString();
                        em.goAhead            = false;
                        em.MessageText        = dr["productareagroupid"].ToString() + @" is an invalid PAG. It should be an integer.";
                        listerror.Add(em);
                    }

                    try
                    {
                        double eff2 = double.Parse(dr["Quantity"].ToString());
                    }
                    catch (Exception ex)
                    {
                        PriceErrorList em = new PriceErrorList();
                        em.ProductID          = dr["HomeProductID"].ToString();
                        em.ProductAreaGroupID = dr["productareagroupid"].ToString();
                        em.MessageText        = dr["Quantity"].ToString() + @" is an invalid quantity. It should be a decimal.";
                        em.goAhead            = false;
                        listerror.Add(em);
                    }
                }

                DataSet ds = cr.ValidateQuantity(dtable, usercode);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        PriceErrorList em = new PriceErrorList();
                        em.ProductID          = dr["HomeProductID"].ToString();
                        em.ProductAreaGroupID = dr["productareagroupid"].ToString();
                        em.goAhead            = true;
                        em.MessageText        = "This product (" + dr["ProductID"].ToString() + ") was not configured to home. If you process it will be ignored.";
                        listerror.Add(em);
                    }
                }
            }
            else
            {
                PriceErrorList em = new PriceErrorList();

                em.ProductID   = "";
                em.MessageText = @"The excel file is blank.";
                em.goAhead     = false;
                listerror.Add(em);
            }

            return(listerror);
        }
Exemplo n.º 3
0
        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);
        }
Exemplo n.º 4
0
        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;
            }
        }
Exemplo n.º 5
0
        private List <PriceErrorList> IsSourceValidate(System.Data.DataTable dtable)
        {
            List <PriceErrorList> listerror = new List <PriceErrorList>();

            // step 1 validate product id duplication

            if (dtable.Rows.Count > 0)
            {
                // step 1 validate region value
                var regionList = from c in cr.SQSRegion
                                 select c.RegionName;


                var sourceregionlist = from dr in dtable.AsEnumerable()
                                       select dr.Field <string>("regionname");

                var nonexist = sourceregionlist.Except(regionList);

                var ps = from dr in dtable.AsEnumerable()
                         join nx in nonexist on dr["regionname"] equals nx.ToString()
                         select new
                {
                    productid     = dr["productid"],
                    region        = dr["regionname"],
                    effectivedate = dr["effectivedate"]
                };

                foreach (var row in ps)
                {
                    PriceErrorList em = new PriceErrorList();

                    em.ProductID = row.productid.ToString();
                    em.Region    = row.region.ToString();

                    DateTime tempdt;
                    try
                    {
                        tempdt = DateTime.Parse(row.effectivedate.ToString());
                        em.EffectiveDateString = tempdt.ToString("dd/MM/yyyy");
                    }
                    catch (Exception exx2)
                    {
                        tempdt = DateTime.MinValue;
                        em.EffectiveDateString = "";
                    }

                    em.EffectiveDate       = tempdt;
                    em.EffectiveDateString = (DateTime.Parse(row.effectivedate.ToString())).ToString("dd/MM/yyyy");
                    em.MessageText         = row.region.ToString() + @" is an invalid price region name. Please check sample import file for valid value.";
                    listerror.Add(em);
                }

                // step 2 validate costprice


                foreach (DataRow dr in dtable.Rows)
                {
                    if (dr["productid"] == null || dr["productid"].ToString() == "" ||
                        dr["regionname"] == null || dr["regionname"].ToString() == "" ||
                        dr["DerivedCost"] == null || dr["DerivedCost"].ToString() == ""
                        )
                    {
                        PriceErrorList em = new PriceErrorList();

                        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 = @"There are some missing value for this product.";
                        listerror.Add(em);
                    }

                    try
                    {
                        double cost = double.Parse(dr["DirectBuildCost"].ToString().Replace("$", "").Replace(",", ""));
                    }
                    catch (Exception ex)
                    {
                        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 = @"Direct Build Cost " + dr["DirectBuildCost"].ToString() + @" is invalid. Please enter a number.";
                        listerror.Add(em);
                    }
                }

                // step 3 validate sell price

                foreach (DataRow dr in dtable.Rows)
                {
                    try
                    {
                        double sellprice = double.Parse(dr["SellPrice"].ToString().Replace("$", "").Replace(",", ""));
                    }
                    catch (Exception ex)
                    {
                        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   = @"Sell price " + dr["sellprice"].ToString() + @" is invalid. Please enter a number.";
                        listerror.Add(em);
                    }
                }

                // step 4 validate derived cost

                foreach (DataRow dr in dtable.Rows)
                {
                    if (dr["derivedcost"].ToString().ToUpper() != "Y" && dr["derivedcost"].ToString().ToUpper() != "N")
                    {
                        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 = @"Derived cost " + dr["derivedcost"].ToString() + @" is invalid. it should be Y or N.";
                        listerror.Add(em);
                    }
                }


                // step 5 validate effective date

                foreach (DataRow dr in dtable.Rows)
                {
                    try
                    {
                        DateTime eff = DateTime.Parse(dr["effectivedate"].ToString());
                    }
                    catch (Exception ex)
                    {
                        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   = "Effective date is invalid.";
                        listerror.Add(em);
                    }
                }

                // step 5.5 validate btp margin
                foreach (DataRow dr in dtable.Rows)
                {
                    if (dr["productid"] == null || dr["productid"].ToString() == "" ||
                        dr["DerivedCost"] == null || dr["DerivedCost"].ToString() == ""
                        )
                    {
                        PriceErrorList em = new PriceErrorList();

                        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 = @"There are some missing value for this product.";
                        listerror.Add(em);
                    }

                    try
                    {
                        double cost = double.Parse(dr["BuildingTransferCost"].ToString().Replace("$", "").Replace(",", ""));
                    }
                    catch (Exception ex)
                    {
                        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 = @"Building Transfer Cost " + dr["BuildingTransferCost"].ToString() + @" is invalid. Please enter a number.";
                        listerror.Add(em);
                    }
                }
                // step 5.5 validate target margin
                foreach (DataRow dr in dtable.Rows)
                {
                    try
                    {
                        double targetmarginpercent = double.Parse(dr["TargetMarginPercent"].ToString());
                    }
                    catch (Exception ex)
                    {
                        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 ex5)
                        {
                            tempdt = DateTime.MinValue;
                            em.EffectiveDateString = "";
                        }
                        em.EffectiveDate = tempdt;

                        em.MessageText = @"TargetMarginPercent " + dr["TargetMarginPercent"].ToString() + @" is invalid. Please enter a number.";
                        listerror.Add(em);
                    }
                }
                //step 6 validate duplication based on productid, effectivedate and regionid on excel

                var ss = from c in dtable.AsEnumerable()
                         group c by new
                {
                    productid     = c["productid"],
                    regionname    = c["regionname"],
                    effectivedate = c["effectivedate"]
                } into newdr
                where newdr.Count() > 1
                select new
                {
                    productid     = newdr.Key.productid,
                    regionname    = newdr.Key.regionname,
                    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.Region        = row.regionname.ToString();
                    em.MessageText   = row.totalcout.ToString() + " Duplicate records found.";
                    listerror.Add(em);
                }

                // step 7 validate against database to check duplication

                if (listerror.Count == 0)
                {
                    DataSet ds = ValidatePriceSheetWithDatabase(dtable);
                    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 = @"The excel file is blank.";
                listerror.Add(em);
            }

            return(listerror);
        }