private void LoadVehicletype()
    {
        try
        {
            VehicleTypeBL objVehicleTypeBL = new VehicleTypeBL();

            DataSet dsVehicleTypes = new DataSet();

            if (Cache["VehicleType"] == null)
            {
                dsVehicleTypes       = objVehicleTypeBL.GetVehicleType();
                Cache["VehicleType"] = dsVehicleTypes;
            }
            else
            {
                dsVehicleTypes = (DataSet)Cache["VehicleType"];
            }

            ddlVehicleType.DataValueField = "VehicleTypeID";
            ddlVehicleType.DataTextField  = "VehicleType";
            ddlVehicleType.DataSource     = dsVehicleTypes.Tables[0];
            ddlVehicleType.DataBind();
        }
        catch (Exception ex)
        {
        }
    }
Пример #2
0
    private void ReadExcelData(string sFileName)
    {
        ArrayList RowNo = new ArrayList();
        ArrayList ColNo = new ArrayList();

        ArrayList SArray = new ArrayList();

        LeadsBL objLeadsBL = new LeadsBL();

        ExcelReading objExcelData = new ExcelReading();

        DataSet ds       = new DataSet();
        DataSet dsStatus = new DataSet();

        DataSet dsSales = new DataSet();
        bool    bnew    = false;

        try
        {
            lblErrorMsg.Text = "";
            ds = objExcelData.GetLeadsExcelToDataset(sFileName);

            DataSet dsError = new DataSet();

            dsError.Tables.Add();
            dsError.Tables["Table1"].Columns.Add("PhoneNo");
            dsError.Tables["Table1"].Columns.Add("RowNo");
            dsError.Tables["Table1"].Columns.Add("Error");



            if (ds.Tables[0].Columns.Contains("PhoneNo") && ds.Tables[0].Columns.Contains("Price") &&
                ds.Tables[0].Columns.Contains("Header") &&
                ds.Tables[0].Columns.Contains("Description") && (ds.Tables[0].Columns.Contains("URL") || ds.Tables[0].Columns.Contains("City")) &&
                ds.Tables[0].Columns.Contains("State") && ds.Tables[0].Columns.Contains("Lead_Date") &&
                ds.Tables[0].Columns.Contains("Make") &&
                ds.Tables[0].Columns.Contains("Model") &&
                ds.Tables[0].Columns.Contains("Email") &&
                ds.Tables[0].Columns.Contains("Year") &&
                ds.Tables[0].Columns.Contains("VehicleType"))
            {
                int count = objExcelData.GetExcelDistictBTNCOunt_Sales(sFileName);
                ViewState["Count"] = count.ToString();
                if (ds != null)
                {
                    if (ds.Tables[0].Rows.Count > 8000)
                    {
                        lblErrorMsg.Text = "Upload Only 8000 rows Only!";
                        return;
                    }
                    else if (Convert.ToInt32(txtNoofRecords.Text) != Convert.ToInt32(ds.Tables[0].Rows.Count))
                    {
                        lblErrorMsg.Text = "Records Count Does Not Match!";
                        return;
                    }
                    if (ds.Tables[0].Rows.Count == count)
                    {
                        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                        {
                            //return;
                            //RowNo.Add(i);

                            if (ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim() == "" ||
                                ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim().Length != 10 ||
                                !GeneralFunc.IsNumeric(ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim()))
                            {
                                dsError.Tables["Table1"].Rows.Add();
                                dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim();
                                dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["RowNo"]   = (i + 1).ToString();
                                dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["Error"]   = "Please enter valid phone number in the excel sheet";
                            }
                            //else if (ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim().Length != 10)
                            //{
                            //    dsError.Tables["Table1"].Rows.Add();
                            //    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim();
                            //    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["RowNo"] = (i + 1).ToString();
                            //    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["Error"] = "Please enter valid phone number in the excel sheet";

                            //}
                            //else if (!GeneralFunc.IsNumeric(ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim()))
                            //{
                            //    dsError.Tables["Table1"].Rows.Add();
                            //    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim();
                            //    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["RowNo"] = (i + 1).ToString();
                            //    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["Error"] = "Please enter valid phone number in the excel sheet";
                            //}
                            else if (CheckBTN(ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim()))
                            {
                                dsError.Tables["Table1"].Rows.Add();
                                dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim();
                                dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["RowNo"]   = (i + 1).ToString();
                                dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["Error"]   = "Phone number already exists in database.";
                            }
                            else
                            {
                                if (ds.Tables[0].Rows[i]["STATE"].ToString().Trim() == "" ||
                                    ds.Tables[0].Rows[i]["STATE"].ToString().Trim().Length != 2 ||
                                    ds.Tables[0].Rows[i]["STATE"].ToString().Trim() == null)
                                {
                                    dsError.Tables["Table1"].Rows.Add();
                                    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim();
                                    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["RowNo"]   = (i + 1).ToString();
                                    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["Error"]   = "Please enter the state code in the excel sheet";
                                }
                                //else if (ds.Tables[0].Rows[i]["STATE"].ToString().Trim().Length != 2)
                                //{
                                //    dsError.Tables["Table1"].Rows.Add();
                                //    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["Phoneno"] = ds.Tables[0].Rows[i]["Phoneno"].ToString().Trim();
                                //    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["RowNo"] = (i + 1).ToString();
                                //    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["Error"] = "Please enter the proper state code in the excel Sheet";
                                //}
                                //if (ds.Tables[0].Rows[i]["STATE"].ToString().Trim() == null)
                                //{
                                //    dsError.Tables["Table1"].Rows.Add();
                                //    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim();
                                //    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["RowNo"] = (i + 1).ToString();
                                //    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["Error"] = "Error in record state code is not valid..";

                                //}

                                else if (ds.Tables[0].Rows[i]["STATE"].ToString().Trim().Length == 2)
                                {
                                    string Stateid = GetStateId(ds.Tables[0].Rows[i]["STATE"].ToString().Trim().ToUpper());
                                    if (Stateid == "0")
                                    {
                                        btnUpload.Text = "Upload";
                                        dsError.Tables["Table1"].Rows.Add();
                                        dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim();
                                        dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["RowNo"]   = (i + 1).ToString();
                                        dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["Error"]   = "Please check the record in the sheet, state is invalid.";

                                        ColNo.Add(12);
                                    }
                                }
                                //if (ds.Tables[0].Rows[i]["Price"].ToString().Trim().Length < 0)
                                //{
                                //    dsError.Tables["Table1"].Rows.Add();
                                //    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim();
                                //    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["RowNo"] = (i + 1).ToString();
                                //    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["Error"] = "Please enter price in the excel sheet";

                                //}
                                if (ds.Tables[0].Rows[i]["Price"].ToString().Trim() != "")
                                {
                                    if (!GeneralFunc.IsNumeric(ds.Tables[0].Rows[i]["Price"].ToString().Trim()))
                                    {
                                        dsError.Tables["Table1"].Rows.Add();
                                        dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim();
                                        dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["RowNo"]   = (i + 1).ToString();
                                        dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["Error"]   = "Please enter numeric price in the excel sheet";
                                    }
                                }

                                if (ds.Tables[0].Rows[i]["Lead_Date"].ToString().Trim() == "")
                                {
                                    dsError.Tables["Table1"].Rows.Add();
                                    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim();
                                    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["RowNo"]   = (i + 1).ToString();
                                    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["Error"]   = "Please enter lead date in the excel sheet";
                                }
                                else if (!GeneralFunc.ValidateDate(ds.Tables[0].Rows[i]["Lead_Date"].ToString().Trim()))
                                {
                                    dsError.Tables["Table1"].Rows.Add();
                                    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim();
                                    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["RowNo"]   = (i + 1).ToString();
                                    dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["Error"]   = "Please enter valid lead date in the excel sheet";
                                }
                                else
                                {
                                    DataSet  dsDatetime = objHotLeadBL.GetDatetime();
                                    DateTime dtNow      = Convert.ToDateTime(dsDatetime.Tables[0].Rows[0]["Datetime"].ToString());
                                    if (Convert.ToDateTime(ds.Tables[0].Rows[i]["Lead_Date"].ToString().Trim()) > dtNow)
                                    {
                                        dsError.Tables["Table1"].Rows.Add();
                                        dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim();
                                        dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["RowNo"]   = (i + 1).ToString();
                                        dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["Error"]   = "Lead date cannot be greater than today date";
                                    }
                                }
                                if (ds.Tables[0].Rows[i]["Email"].ToString().Trim() != "")
                                {
                                    if (!GeneralFunc.isEmail(ds.Tables[0].Rows[i]["Email"].ToString().Trim()))
                                    {
                                        dsError.Tables["Table1"].Rows.Add();
                                        dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim();
                                        dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["RowNo"]   = (i + 1).ToString();
                                        dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["Error"]   = "Please enter valid email in the excel sheet";
                                    }
                                }
                                if (ds.Tables[0].Rows[i]["Year"].ToString().Trim() != "")
                                {
                                    if (!GeneralFunc.IsNumeric(ds.Tables[0].Rows[i]["Year"].ToString().Trim()))
                                    {
                                        dsError.Tables["Table1"].Rows.Add();
                                        dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim();
                                        dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["RowNo"]   = (i + 1).ToString();
                                        dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["Error"]   = "Please enter valid year in the excel sheet";
                                    }
                                    else if (ds.Tables[0].Rows[i]["Year"].ToString().Trim().Length != 4)
                                    {
                                        dsError.Tables["Table1"].Rows.Add();
                                        dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim();
                                        dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["RowNo"]   = (i + 1).ToString();
                                        dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["Error"]   = "Please enter valid year in the excel sheet";
                                    }
                                    else
                                    {
                                        DataSet  dsDatetime = objHotLeadBL.GetDatetime();
                                        DateTime dtNow      = Convert.ToDateTime(dsDatetime.Tables[0].Rows[0]["Datetime"].ToString());
                                        DateTime EnterDate  = Convert.ToDateTime("1/1/" + ds.Tables[0].Rows[i]["Year"].ToString().Trim());
                                        if (EnterDate > dtNow)
                                        {
                                            dsError.Tables["Table1"].Rows.Add();
                                            dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim();
                                            dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["RowNo"]   = (i + 1).ToString();
                                            dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["Error"]   = "Please enter valid year in the excel sheet";
                                        }
                                    }
                                }
                            }
                            VehicleTypeBL objVehicleTypeBL = new VehicleTypeBL();

                            DataSet dsVehicleTypes = new DataSet();

                            if (Cache["VehicleType"] == null)
                            {
                                dsVehicleTypes       = objVehicleTypeBL.GetVehicleType();
                                Cache["VehicleType"] = dsVehicleTypes;
                            }
                            else
                            {
                                dsVehicleTypes = (DataSet)Cache["VehicleType"];
                            }
                            int cint = 0;

                            foreach (DataRow row in dsVehicleTypes.Tables[0].Rows) // Loop over the rows.
                            {
                                if (row.ItemArray[0].ToString().ToLower() == ds.Tables[0].Rows[i]["VehicleType"].ToString().Trim().ToLower())
                                {
                                    cint = cint + 1;
                                    break;
                                }
                            }
                            if (cint == 0)
                            {
                                dsError.Tables["Table1"].Rows.Add();
                                dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim();
                                dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["RowNo"]   = (i + 1).ToString();
                                dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["Error"]   = "Please Enter Proper Vehicle Type";
                            }
                            cint = 0;
                            LeadSourceBL objLeadSourceBL = new LeadSourceBL();
                            DataSet      dsLeadSource    = new DataSet();
                            if (Cache["LeadSource"] == null)
                            {
                                dsLeadSource        = objLeadSourceBL.GetLeadSources();
                                Cache["LeadSource"] = dsVehicleTypes;
                            }
                            else
                            {
                                dsLeadSource = (DataSet)Cache["LeadSource"];
                            }
                            foreach (DataRow row in dsLeadSource.Tables[0].Rows) // Loop over the rows.
                            {
                                if (row.ItemArray[0].ToString().ToLower() == ds.Tables[0].Rows[i]["LeadSourceID"].ToString().Trim().ToLower())
                                {
                                    cint = cint + 1;
                                    break;
                                }
                            }
                            if (cint == 0)
                            {
                                dsError.Tables["Table1"].Rows.Add();
                                dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim();
                                dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["RowNo"]   = (i + 1).ToString();
                                dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["Error"]   = "Please Enter Proper Lead Source ID";
                            }
                        }

                        if (dsError.Tables["Table1"].Rows.Count > 0)
                        {
                            grdIntroInfo.DataSource = null;
                            grdIntroInfo.DataBind();
                            grdErrors.DataSource = dsError.Tables["Table1"].DefaultView;
                            grdErrors.DataBind();
                            Header.Visible = false;
                        }
                        else
                        {
                            grdErrors.DataSource = null;
                            grdErrors.DataBind();
                            Header.Visible          = true;
                            grdIntroInfo.DataSource = ds.Tables["XLData"].DefaultView;
                            grdIntroInfo.DataBind();
                            btnSubmit.Enabled = false;;
                            btnUpload.Enabled = true;
                        }
                    }
                    else
                    {
                        for (int j = 0; j < ds.Tables[0].Rows.Count; j++)
                        {
                            int phoneCount = 0;
                            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                            {
                                if (ds.Tables[0].Rows[j]["PhoneNo"].ToString() == ds.Tables[0].Rows[i]["PhoneNo"].ToString())
                                {
                                    phoneCount = phoneCount + 1;
                                    if (phoneCount > 1)
                                    {
                                        dsError.Tables["Table1"].Rows.Add();
                                        dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["PhoneNo"] = ds.Tables[0].Rows[i]["PhoneNo"].ToString().Trim();
                                        dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["RowNo"]   = (j + 1).ToString();
                                        dsError.Tables["Table1"].Rows[dsError.Tables["Table1"].Rows.Count - 1]["Error"]   = "Exist Duplicates PhoneNo In Excel Sheet Plz Check Excel Sheet";
                                    }
                                    ColNo.Add(8);
                                }
                            }
                        }

                        if (dsError.Tables["Table1"].Rows.Count > 0)
                        {
                            grdIntroInfo.DataSource = null;
                            grdIntroInfo.DataBind();
                            grdErrors.DataSource = dsError.Tables["Table1"].DefaultView;
                            grdErrors.DataBind();
                            Header.Visible = false;
                        }
                    }
                }
            }

            else
            {
                btnUpload.Text    = "Upload";
                lblErrorMsg.Text  = "Enter Valid Sales Excel Sheet Proper Columns.";
                btnSubmit.Enabled = true;
            }
        }
        catch (Exception ex)
        {
            if (ex.Message.ToString() == "Not a legal OleAut date.")
            {
                Header.Visible   = false;
                lblErrorMsg.Text = "Enter valid date format in excel sheet";
            }
            else
            {
                throw ex;
            }
        }
    }