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