public static List<OfficeListItem> getOfficesForPanel(int panelID, string connString, double latitude = 999, double longitude = 999)
{
List<OfficeListItem> offices = new List<OfficeListItem>();
DataSet dataSet = new DataSet();
try
{
using (DatabaseConnection dbConn = new DatabaseConnection(connString))
{
dbConn.AddParameter("@PANELID", panelID, ParameterDirection.Input);
if (longitude != 999) dbConn.AddParameter("@LNG", longitude, ParameterDirection.Input);
if (latitude != 999) dbConn.AddParameter("@LAT", latitude, ParameterDirection.Input);
dbConn.GetData("PM_GetOfficeListFromPanel", CommandType.StoredProcedure, "Office", ref dataSet);
dbConn.ClearParameters();
foreach (DataRow dr in dataSet.Tables["Office"].Rows)
{
OfficeListItem office = new OfficeListItem();
office.ID = (int)dr["Office_ID"];
office.Name = dr["OfficeName"] is DBNull ? "" : (string)dr["OfficeName"];
office.Address1 = dr["Address1"] is DBNull ? "" : (string)dr["Address1"];
office.Address2 = dr["Address2"] is DBNull ? "" : (string)dr["Address2"];
office.Phone = dr["PhoneNumber"] is DBNull ? "" : (string)dr["PhoneNumber"];
office.City = dr["City"] is DBNull ? "" : (string)dr["City"];
office.State = dr["State"] is DBNull ? "" : (string)dr["State"];
office.Zip = dr["Zip"] is DBNull ? "" : (string)dr["Zip"];
office.ParentProviderID = dr["provider_ID"] is DBNull ? -1 : (int)dr["provider_ID"];
office.ParentProviderTin = dr["TIN"] is DBNull ? "" : (string)dr["TIN"];
office.ParentProviderAddress = dr["BillingAddress"] is DBNull ? "" : (string)dr["BillingAddress"];
office.Distance = dr["Distance"] is DBNull ? -1 : Convert.ToDouble((string)dr["Distance"]);
office.Discount = dr["Amount"] is DBNull ? "-1" : Convert.ToString(dr["Amount"]);
if (office.Discount != "-1" && office.Discount != "0")
office.Discount = "Yes";
else
office.Discount = "No";
dbConn.AddParameter("@OFFICEID", office.ID, ParameterDirection.Input);
DataSet ds = new DataSet();
dbConn.GetData("PM_GetOfficeSpecialty", CommandType.StoredProcedure, "specialty", ref ds);
foreach (DataRow row in ds.Tables["specialty"].Rows)
{
office.Specialty = office.Specialty + "," + (row["specialty"] is DBNull ? "" : (string)row["specialty"]);
}
if (office.Specialty != null)
office.Specialty = office.Specialty.Substring(1);
dbConn.ClearParameters();
offices.Add(office);
}
}
}
catch
{
throw;
}
return offices;
}
public static List<OfficeListItem> GetOfficesForMap(int panelID, string connString)
{
List<OfficeListItem> offices = new List<OfficeListItem>();
string sql = "SELECT * FROM Panel P JOIN PanelOfficeMapping POM ON P.Panel_ID = POM.Panel_ID JOIN Office O ON POM.Office_ID = O.Office_ID JOIN Geocode GC ON O.Office_ID = GC.Office_ID WHERE P.Panel_ID = @panelId";
using (SqlDataReader reader = DatabaseConnection.ExecuteReader(sql, CommandType.Text, connString, new SqlParameter("@panelId", panelID)))
{
if (reader.HasRows)
{
int officeNameIndex = reader.GetOrdinal("OfficeName");
int latIndex = reader.GetOrdinal("latitude");
int lngIndex = reader.GetOrdinal("longitude");
while (reader.Read())
{
OfficeListItem office = new OfficeListItem();
office.Name = reader.GetString(officeNameIndex);
office.Latitude = reader.GetDouble(latIndex);
office.Longtitude = reader.GetDouble(lngIndex);
offices.Add(office);
}
}
}
return offices;
}