<%@ Page Language="C#" MasterPageFile="~/MasterPages/CMSMasterPage.master" AutoEventWireup="true" CodeFile="ZipCodeSrch.aspx.cs" Inherits="ZipCodeSrch" Title="Genesis Facility Management - CMS - Zip Code Search" %>
<%@ Register Assembly="Infragistics35.Web.v9.1, Version=9.1.20091.1015, Culture=neutral, PublicKeyToken=7dd5c3163f2cd0cb"
Namespace="Infragistics.Web.UI.GridControls" TagPrefix="ig" %>
Vendors Within 50 Miles by Zip Code
Show
vendors within 100 miles of zipcode:
<%--
--%>
********************************************************************************************************
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Text.RegularExpressions;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
public partial class ZipCodeSrch : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
#region Variable Declarations
#endregion
#region Common Initialization
tbZipCode.Focus();
#endregion
#region Non-PostBack Related Initialization
if (!IsPostBack)
{
if (Request["ZipCode"] != null)
{
tbZipCode.Text = Request["ZipCode"].ToString();
if (Request["Miles"] != null)
{
hfDistance.Value = Request["Miles"].ToString();
}
else
{
hfDistance.Value = "50";
}
btnZipSrch_Click(btnZipSrch, null);
}
}
#endregion
#region PostBack Related Initialization
if (IsPostBack)
{
// Load the Vendor Result datatable from viewstate to the WebDataGrid.
if (ViewState["dtVendorResults"] != null)
{
wdgVendorsByMiles.DataSource = (DataTable)ViewState["dtVendorResults"];
wdgVendorsByMiles.DataBind();
}
}
#endregion
}
protected void btnZipSrch_Click(object sender, EventArgs e)
{
string sZipCode = tbZipCode.Text;
double dblMiles = Convert.ToDouble(hfDistance.Value.ToString());
double dblLatitude, dblLongitude, dblLatCheck, dblLongCheck, dblDist;
string strLastZip = "00000";
ZIPCodeDownload.RadiusAssistant zcdRadius;
ZIPCodeDownload.DistanceAssistant zcdDistance;
SqlCommand sqlCMD, sqlCMD1, sqlCMD2;
SqlDataReader sdrZipCode, sdrZipResults, sdrResults;
SqlConnection objConnect = new SqlConnection(ConfigurationManager.ConnectionStrings["GenesisAccounts"].ConnectionString);
//TableRow trNew;
//TableCell tcNew;
DataTable dtVendorResults = new DataTable("VendorResults");
lblSrchType.Text = "Vendors Within " + Math.Round(dblMiles, 0).ToString() + " Miles of Zip Code " + sZipCode;
sqlCMD = new SqlCommand("SELECT Latitude, Longitude FROM Geocode WHERE ZIPCode = '" + sZipCode + "'", objConnect);
objConnect.Open();
sdrZipCode = sqlCMD.ExecuteReader();
if (sdrZipCode.HasRows)
{
sdrZipCode.Read();
dblLatitude = Convert.ToDouble(sdrZipCode["Latitude"].ToString());
dblLongitude = Convert.ToDouble(sdrZipCode["Longitude"].ToString());
zcdRadius = new ZIPCodeDownload.RadiusAssistant(dblLatitude, dblLongitude, dblMiles);
zcdDistance = new ZIPCodeDownload.DistanceAssistant();
sqlCMD1 = new SqlCommand("SELECT * FROM Geocode WHERE Latitude >= " + zcdRadius.MinLatitude + " AND Latitude <= " + zcdRadius.MaxLatitude + " AND Longitude >= " + zcdRadius.MinLongitude + " AND Longitude <= " + zcdRadius.MaxLongitude, objConnect);
sdrZipResults = sqlCMD1.ExecuteReader();
if (sdrZipResults.HasRows)
{
while (sdrZipResults.Read())
{
dblLatCheck = Convert.ToDouble(sdrZipResults["Latitude"].ToString());
dblLongCheck = Convert.ToDouble(sdrZipResults["Longitude"].ToString());
dblDist = zcdDistance.Distance(dblLatitude, dblLongitude, dblLatCheck, dblLongCheck);
if ((dblDist <= dblMiles) && (sdrZipResults["ZIPCode"].ToString() != strLastZip))
{
strLastZip = sdrZipResults["ZIPCode"].ToString();
sqlCMD2 = new SqlCommand("SELECT Vendor_ID, Company, Trade, City, State, dbo.FormatPhoneNumber(Phone) AS FormPhone, dbo.FormatPhoneNumber(Emergency_Phone_1) AS FormEmgPhone, " + Math.Round(dblDist, 2).ToString() + " AS Distance FROM Vendor_Information WHERE Zip LIKE '" + strLastZip + "%' ORDER BY Vendor_ID", objConnect);
sdrResults = sqlCMD2.ExecuteReader();
if (sdrResults.HasRows)
{
dtVendorResults.Load(sdrResults);
}
sdrResults.Close();
sdrResults = null;
}
}
}
sdrZipCode.Close();
sdrZipResults.Close();
sdrZipCode = null;
sdrResults = null;
objConnect.Close();
objConnect = null;
}
hlZip100.Text = "Show vendors within 100 miles of zipcode: " + sZipCode;
hlZip100.Visible = (Math.Round(dblMiles, 0) == 50);
CriteriaPanel.Visible = false;
ResultsPanel.Visible = true;
hfDistance.Value = "100";
wdgVendorsByMiles.DataSource = dtVendorResults;
wdgVendorsByMiles.DataBind();
wdgVendorsByMiles.EnableDataViewState = true;
// Create or set a ViewState variable for the Vendor datatable.
if (ViewState["dtVendorResults"] == null)
{
ViewState.Add("dtVendorResults", dtVendorResults);
}
else
{
ViewState["dtVendorResults"] = dtVendorResults;
}
}
}
dor_Information WHERE Zip LIKE '" + strLastZip + "%' ORDER BY Vendor_ID", objConnect);
sdrResults = sqlCMD2.ExecuteReader();
if (sdrResults.HasRows)
{
dtVendorResults.Load(sdrResults);
}
//while (sdrResults.Read())
//{
// trNew = new TableRow();
// tcNew = new TableCell();
// tcNew.Controls.Add(new LiteralControl("" + sdrResults["Company"].ToString() + ""));
// tcNew.CssClass = "ziptd";
// trNew.Cells.Add(tcNew);
// tcNew = new TableCell();
// tcNew.Controls.Add(new LiteralControl(sdrResults["Trade"].ToString()));
// tcNew.CssClass = "ziptd";
// trNew.Cells.Add(tcNew);
// tcNew = new TableCell();
// tcNew.Controls.Add(new LiteralControl(sdrResults["City"].ToString()));
// tcNew.CssClass = "ziptd";
// trNew.Cells.Add(tcNew);
// tcNew = new TableCell();
// tcNew.Controls.Add(new LiteralControl(sdrResults["State"].ToString()));
// tcNew.CssClass = "ziptd";
// trNew.Cells.Add(tcNew);
// tcNew = new TableCell();
// tcNew.Controls.Add(new LiteralControl(Math.Round(dblDist, 2).ToString()));
// tcNew.CssClass = "ziptd";
// trNew.Cells.Add(tcNew);
// tcNew = new TableCell();
// tcNew.Controls.Add(new LiteralControl(FormatUSPhone(sdrResults["Phone"].ToString())));
// tcNew.CssClass = "ziptd";
// trNew.Cells.Add(tcNew);
// tcNew = new TableCell();
// tcNew.Controls.Add(new LiteralControl(FormatUSPhone(sdrResults["Emergency_Phone_1"].ToString())));
// tcNew.CssClass = "ziptd";
// trNew.Cells.Add(tcNew);
// tblSrchResults.Rows.Add(trNew);
//}
sdrResults.Close();
sdrResults = null;
}
}
}
sdrZipCode.Close();
sdrZipResults.Close();
sdrZipCode = null;
sdrResults = null;
objConnect.Close();
objConnect = null;
}
hlZip100.Text = "Show vendors within 100 miles of zipcode: " + sZipCode;
hlZip100.Visible = (Math.Round(dblMiles, 0) == 50);
CriteriaPanel.Visible = false;
ResultsPanel.Visible = true;
hfDistance.Value = "100";
wdgVendorsByMiles.DataSource = dtVendorResults;
wdgVendorsByMiles.DataBind();
wdgVendorsByMiles.EnableDataViewState = true;
// Create or set a ViewState variable for the Vendor datatable.
if (ViewState["dtVendorResults"] == null)
{
ViewState.Add("dtVendorResults", dtVendorResults);
}
else
{
ViewState["dtVendorResults"] = dtVendorResults;
}
}
///
/// Method FormatUSPhone formats a phone string field.
///
///
///
public static string FormatUSPhone(string sParmPhoneNumber)
{
// First we must remove all non-numeric characters.
sParmPhoneNumber = sParmPhoneNumber.Replace("(", "").Replace(")", string.Empty).Replace("-", string.Empty).Replace(" ", string.Empty);
// If length longer than 10, just grab the last 10 character.
if (sParmPhoneNumber.Length > 10)
{
sParmPhoneNumber = sParmPhoneNumber.Substring(sParmPhoneNumber.Length - 10);
}
string formattedPhoneNumber = string.Empty;
string formatPattern = string.Empty;
switch (sParmPhoneNumber.Length)
{
case 10:
formatPattern = @"(\d{3})(\d{3})(\d{4})";
formattedPhoneNumber = Regex.Replace(sParmPhoneNumber, formatPattern, "($1) $2-$3");
break;
case 7:
formatPattern = @"(\d{3})(\d{4})";
formattedPhoneNumber = Regex.Replace(sParmPhoneNumber, formatPattern, "$1-$2");
break;
default:
formattedPhoneNumber = sParmPhoneNumber;
break;
}
// Now return the formatted phone number.
return formattedPhoneNumber;
}
}