/// <summary>
/// Retrieve a list of SmallGroupPlacemark objects that are in the designated Arena Area.
/// Possibly retrieve only a subet of those.
/// </summary>
/// <param name="areaid">The Area Id to load from.</param>
/// <param name="start">The 0-based starting index of the records to retrieve.</param>
/// <param name="count">The maximum number of records to retrieve. If the returned number is less than this number then no more records are available.</param>
/// <returns>List of SmallGroupPlacemarks that identify groups in range.</returns>
public List <SmallGroupPlacemark> SmallGroupPlacemarksInArea(int areaid, int start, int count)
{
List <SmallGroupPlacemark> groups;
SqlConnection con;
SqlDataReader rdr;
SqlCommand cmd;
//
// Prepare the SQL query to request all small groups within a radius of an address.
//
con = new Arena.DataLib.SqlDbConnection().GetDbConnection();
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = "SELECT DISTINCT(sg.group_id) FROM smgp_group AS sg" +
" LEFT JOIN core_person_address AS cpa ON cpa.person_id = sg.target_location_person_id" +
" LEFT JOIN core_address AS ca ON ca.address_id = cpa.address_id" +
" WHERE cpa.primary_address = 1" +
" AND ca.area_id = @AreaID" +
" AND sg.is_group_private = 0" +
" AND sg.active = 1" +
" ORDER BY sg.group_id";
cmd.Parameters.Add(new SqlParameter("@AreaID", areaid));
//
// Execute the reader and process all results.
rdr = cmd.ExecuteReader();
groups = SmallGroupPlacemarksFromReader(rdr, start, count);
rdr.Close();
return(groups);
}
/// <summary>
/// Retrieve a list of FamilyPlacemark objects that are in the designated radius of the given
/// latitude and longitude coordinates. Possibly retrieve only a subet of those individuals.
/// </summary>
/// <param name="latitude">The latitude of the center point.</param>
/// <param name="longitude">The longitude of the center point.</param>
/// <param name="radius">The distance from the center point to look up to.</param>
/// <param name="start">The 0-based starting index of the records to retrieve.</param>
/// <param name="count">The maximum number of records to retrieve. If the returned number is less than this number then no more records are available.</param>
/// <returns>List of FamilyPlacemarks that identify families in range.</returns>
public List <FamilyPlacemark> FamilyPlacemarksInRadius(Double latitude, Double longitude, Double radius, int start, int count)
{
List <FamilyPlacemark> families;
SqlConnection con;
SqlDataReader rdr;
SqlCommand cmd;
if (PersonFieldOperationAllowed(PersonFields.Profile_Name, OperationType.View) == false)
{
return(new List <FamilyPlacemark>());
}
if (latitude == 0 && longitude == 0)
{
throw new ArgumentException("Address has not been geocoded.");
}
//
// Prepare the SQL query to request all people within a radius of an address.
//
con = new Arena.DataLib.SqlDbConnection().GetDbConnection();
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = "SELECT DISTINCT(cfm.family_id) FROM core_family_member AS cfm" +
" LEFT JOIN core_person_address AS cpa ON cpa.person_id = cfm.person_id" +
" LEFT JOIN core_address AS ca ON ca.address_id = cpa.address_id" +
" LEFT JOIN core_person AS cp ON cp.person_id = cfm.person_id" +
" WHERE cpa.primary_address = 1" +
" AND dbo.cust_hdc_googlemaps_funct_distance_between(@LatFrom, @LongFrom, ca.Latitude, ca.Longitude) < @Distance" +
" AND cp.record_status = 0" +
" ORDER BY cfm.family_id";
cmd.Parameters.Add(new SqlParameter("@LatFrom", latitude));
cmd.Parameters.Add(new SqlParameter("@LongFrom", longitude));
cmd.Parameters.Add(new SqlParameter("@Distance", radius));
//
// Execute the reader and process all results.
rdr = cmd.ExecuteReader();
families = FamilyPlacemarksFromReader(rdr, start, count);
rdr.Close();
return(families);
}
protected void BindRegistrationRepeater(Repeater myRepeater, string myStoredProcedureName, string myParameter, string myParameterName)
{
using (SqlConnection connection = new Arena.DataLib.SqlDbConnection().GetDbConnection())
{
// Create the command and set its properties.
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = myStoredProcedureName;
command.CommandType = CommandType.StoredProcedure;
command.StatementCompleted += new StatementCompletedEventHandler(Bind_SqlCommand_StatementCompleted);
// Add the input parameter and set its properties.
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = myParameterName;
parameter.SqlDbType = SqlDbType.VarChar;
parameter.Direction = ParameterDirection.Input;
parameter.Value = myParameter;
// Add the OrganizationID parameter and set its properties.
SqlParameter orgIdParam = new SqlParameter();
orgIdParam.ParameterName = "@OrganizationId";
orgIdParam.SqlDbType = SqlDbType.Int;
orgIdParam.Direction = ParameterDirection.Input;
orgIdParam.Value = ArenaContext.Current.Organization.OrganizationID;
// Add the parameter to the Parameters collection.
command.Parameters.Add(parameter);
command.Parameters.Add(orgIdParam);
// Open the connection and execute the reader.
connection.Open();
SqlDataReader reader = command.ExecuteReader();
myRepeater.DataSource = reader;
myRepeater.DataBind();
reader.Close();
}
}
/// <summary>
/// Retrieve a list of SmallGroupPlacemark objects that are in the designated radius of the given
/// latitude and longitude coordinates. Possibly retrieve only a subet of those.
/// </summary>
/// <param name="latitude">The latitude of the center point.</param>
/// <param name="longitude">The longitude of the center point.</param>
/// <param name="radius">The distance from the center point to look up to.</param>
/// <param name="start">The 0-based starting index of the records to retrieve.</param>
/// <param name="count">The maximum number of records to retrieve. If the returned number is less than this number then no more records are available.</param>
/// <returns>List of SmallGroupPlacemarks that identify groups in range.</returns>
public List <SmallGroupPlacemark> SmallGroupPlacemarksInRadius(Double latitude, Double longitude, Double radius, int start, int count)
{
List <SmallGroupPlacemark> groups;
SqlConnection con;
SqlDataReader rdr;
SqlCommand cmd;
if (latitude == 0 && longitude == 0)
{
throw new ArgumentException("Address has not been geocoded.");
}
//
// Prepare the SQL query to request all small groups within a radius of an address.
//
con = new Arena.DataLib.SqlDbConnection().GetDbConnection();
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = "SELECT DISTINCT(sg.group_id) FROM smgp_group AS sg" +
" LEFT JOIN core_person_address AS cpa ON cpa.person_id = sg.target_location_person_id" +
" LEFT JOIN core_address AS ca ON ca.address_id = cpa.address_id" +
" WHERE cpa.primary_address = 1" +
" AND dbo.cust_hdc_googlemaps_funct_distance_between(@LatFrom, @LongFrom, ca.Latitude, ca.Longitude) < @Distance" +
" AND sg.is_group_private = 0" +
" AND sg.active = 1" +
" ORDER BY sg.group_id";
cmd.Parameters.Add(new SqlParameter("@LatFrom", latitude));
cmd.Parameters.Add(new SqlParameter("@LongFrom", longitude));
cmd.Parameters.Add(new SqlParameter("@Distance", radius));
//
// Execute the reader and process all results.
rdr = cmd.ExecuteReader();
groups = SmallGroupPlacemarksFromReader(rdr, start, count);
rdr.Close();
return(groups);
}
/// <summary>
/// Retrieve a list of FamilyPlacemark objects that are in the designated Arena Area.
/// Possibly retrieve only a subet of those individuals.
/// </summary>
/// <param name="areaid">The Arena area to load from.</param>
/// <param name="start">The 0-based starting index of the records to retrieve.</param>
/// <param name="count">The maximum number of records to retrieve. If the returned number is less than this number then no more records are available.</param>
/// <returns>List of FamilyPlacemarks that identify families in range.</returns>
public List <FamilyPlacemark> FamilyPlacemarksInArea(int areaid, int start, int count)
{
List <FamilyPlacemark> families;
SqlConnection con;
SqlDataReader rdr;
SqlCommand cmd;
if (PersonFieldOperationAllowed(PersonFields.Profile_Name, OperationType.View) == false)
{
return(new List <FamilyPlacemark>());
}
//
// Prepare the SQL query to request all people within a radius of an address.
//
con = new Arena.DataLib.SqlDbConnection().GetDbConnection();
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = "SELECT DISTINCT(cfm.family_id) FROM core_family_member AS cfm" +
" LEFT JOIN core_person_address AS cpa ON cpa.person_id = cfm.person_id" +
" LEFT JOIN core_address AS ca ON ca.address_id = cpa.address_id" +
" LEFT JOIN core_person AS cp ON cp.person_id = cfm.person_id" +
" WHERE cpa.primary_address = 1" +
" AND ca.area_id = @AreaID" +
" AND cp.record_status = 0" +
" ORDER BY cfm.family_id";
cmd.Parameters.Add(new SqlParameter("@AreaID", areaid));
//
// Execute the reader and process all results.
rdr = cmd.ExecuteReader();
families = FamilyPlacemarksFromReader(rdr, start, count);
rdr.Close();
return(families);
}
protected void BindRegistrationRepeater(Repeater myRepeater, string myStoredProcedureName, string myParameter, string myParameterName)
{
using (SqlConnection connection = new Arena.DataLib.SqlDbConnection().GetDbConnection())
{
// Create the command and set its properties.
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = myStoredProcedureName;
command.CommandType = CommandType.StoredProcedure;
command.StatementCompleted += new StatementCompletedEventHandler(Bind_SqlCommand_StatementCompleted);
// Add the input parameter and set its properties.
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = myParameterName;
parameter.SqlDbType = SqlDbType.VarChar;
parameter.Direction = ParameterDirection.Input;
parameter.Value = myParameter;
// Add the OrganizationID parameter and set its properties.
SqlParameter orgIdParam = new SqlParameter();
orgIdParam.ParameterName = "@OrganizationId";
orgIdParam.SqlDbType = SqlDbType.Int;
orgIdParam.Direction = ParameterDirection.Input;
orgIdParam.Value = ArenaContext.Current.Organization.OrganizationID;
// Add the parameter to the Parameters collection.
command.Parameters.Add(parameter);
command.Parameters.Add(orgIdParam);
// Open the connection and execute the reader.
connection.Open();
SqlDataReader reader = command.ExecuteReader();
myRepeater.DataSource = reader;
myRepeater.DataBind();
reader.Close();
}
}
private void Page_Load(object sender, System.EventArgs e)
{
StringBuilder sb = new StringBuilder();
XmlDocument doc = new XmlDocument();
XmlNode root, fields, rows;
SqlConnection con = null;
SqlDataReader rdr = null;
SqlCommand cmd;
ArrayList parameters = new ArrayList();
int i;
//
// Connect to SQL.
//
try {
con = new Arena.DataLib.SqlDbConnection().GetDbConnection();
con.Open();
cmd = con.CreateCommand();
//
// Do some custom replacement.
//
cmd.CommandText = SQLQuerySetting.ReplaceNonCaseSensitive("@@PersonID@@", (ArenaContext.Current.Person != null ? ArenaContext.Current.Person.PersonID.ToString() : "-1"));
//
// Put in all Query Parameters configured.
//
foreach (String qp in QueryParametersSetting)
{
if (!String.IsNullOrEmpty(qp))
{
String[] opts = qp.Split('=');
String o, v = null;
o = opts[0];
if (opts.Length == 2)
{
v = opts[1];
}
if (Request.QueryString[o] != null)
{
cmd.Parameters.Add(new SqlParameter(String.Format("@{0}", o), Request.QueryString[o]));
}
else
{
cmd.Parameters.Add(new SqlParameter(String.Format("@{0}", o), (v == null ? (object)DBNull.Value : (object)v)));
}
}
}
//
// Execute the reader.
//
rdr = cmd.ExecuteReader();
//
// Start creating the XML output.
//
root = doc.CreateElement("sql");
doc.AppendChild(root);
//
// Put in all the field names under a fields element.
//
fields = doc.CreateElement("fields");
root.AppendChild(fields);
for (i = 0; i < rdr.FieldCount; i++)
{
XmlNode field;
if (!SuppressColumnsSetting.Contains(rdr.GetName(i)))
{
field = doc.CreateElement("field");
field.InnerText = rdr.GetName(i).Replace("_", " ");
fields.AppendChild(field);
}
}
//
// Load in each row of data under a rows element.
//
rows = doc.CreateElement("rows");
root.AppendChild(rows);
while (rdr.Read())
{
XmlNode row;
row = doc.CreateElement("row");
rows.AppendChild(row);
//
// Each row is comprised of one or more field name elements.
//
for (i = 0; i < rdr.FieldCount; i++)
{
XmlNode node;
if (!SuppressColumnsSetting.Contains(rdr.GetName(i)))
{
node = doc.CreateElement(rdr.GetName(i));
node.InnerText = rdr[i].ToString();
row.AppendChild(node);
}
}
}
//
// Prepare the translator to convert the XML via XSLT.
//
XPathNavigator navigator = doc.CreateNavigator();
XslCompiledTransform transform = new XslCompiledTransform();
XsltArgumentList argsList = new XsltArgumentList();
transform.Load(base.Server.MapPath(XsltUrlSetting));
argsList.AddParam("ControlID", "", this.ClientID);
foreach (String p in XSLTParametersSetting)
{
try {
String[] s = p.Split('=');
if (s.Length == 1)
{
if (Request.QueryString[s[0]] != null)
{
argsList.AddParam(s[0], "", Request.QueryString[s[0]]);
}
}
else
{
argsList.AddParam(s[0], "", s[1]);
}
}
catch (System.Exception ex)
{
}
}
//
// Translate and store the data.
//
transform.Transform((IXPathNavigable)navigator, argsList, new StringWriter(sb));
ltContent.Text = sb.ToString();
//
// If RawXmlSetting is True, output only XML.
//
if (RawXmlSetting == true)
{
Response.Write(sb.ToString());
Response.ContentType = "application/xml";
Response.End();
}
}
catch (System.Exception ex)
{
throw ex;
}
finally
{
//
// Close all our SQL connections.
//
if (rdr != null)
{
rdr.Close();
}
if (con != null)
{
con.Close();
}
}
}
/// <summary>
/// Go through the loaded small groups and apply the filter to the list and then
/// add everything remaining to the map.
/// </summary>
private void AddFilteredGroups()
{
SqlConnection con;
SqlDataReader rdr;
List <Group> groups = new List <Group>();
SqlCommand cmd;
Placemark placemark;
DataTable dt = new DataTable();
DataRow dr;
//
// Setup the data table.
//
dt.Columns.Add(new DataColumn("ID", typeof(Int32)));
dt.Columns.Add(new DataColumn("Name", typeof(String)));
dt.Columns.Add(new DataColumn("MeetingDay", typeof(String)));
dt.Columns.Add(new DataColumn("MeetingTime", typeof(DateTime)));
dt.Columns.Add(new DataColumn("Type", typeof(String)));
dt.Columns.Add(new DataColumn("Topic", typeof(String)));
dt.Columns.Add(new DataColumn("AverageAge", typeof(Int32)));
dt.Columns.Add(new DataColumn("Notes", typeof(String)));
dt.Columns.Add(new DataColumn("Distance", typeof(Double)));
//
// Build a SQL query to enumerate groups in these categories.
//
con = new Arena.DataLib.SqlDbConnection().GetDbConnection();
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = "SELECT sg.group_id,dbo.cust_hdc_googlemaps_funct_distance_between(@LatFrom, @LongFrom, ca.Latitude, ca.Longitude) AS 'distance'" +
" FROM smgp_group AS sg" +
" LEFT OUTER JOIN core_person_address AS cpa ON cpa.person_id = sg.target_location_person_id" +
" LEFT OUTER JOIN core_address AS ca ON ca.address_id = cpa.address_id" +
" LEFT JOIN smgp_group_cluster AS sgc ON sgc.group_cluster_id = sg.group_cluster_id" +
" LEFT JOIN smgp_cluster_type AS sgt ON sgt.cluster_type_id = sgc.cluster_type_id" +
" WHERE sgt.category_id = @CategoryID" +
" AND sg.is_group_private = 0" +
" AND sg.active = 1" +
" AND ISNULL(cpa.primary_address, 1) = 1" +
" AND ISNULL(dbo.cust_hdc_googlemaps_funct_distance_between(@LatFrom, @LongFrom, ca.Latitude, ca.Longitude), -1) <= " + Convert.ToInt32(ddlDistance.SelectedValue).ToString();
if (LimitToClusterTypeSetting != -1)
{
cmd.CommandText += " AND sgc.cluster_type_id = @ClusterTypeID";
}
cmd.CommandText += " ORDER BY 'distance'";
cmd.Parameters.Add(new SqlParameter("@CategoryID", CategorySetting.CategoryID));
cmd.Parameters.Add(new SqlParameter("@ClusterTypeID", LimitToClusterTypeSetting));
cmd.Parameters.Add(new SqlParameter("@LatFrom", map.Center.Latitude));
cmd.Parameters.Add(new SqlParameter("@LongFrom", map.Center.Longitude));
//
// Execute the reader and process all results.
//
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
//
// Process each small group.
//
try
{
Group g = new Group(Convert.ToInt32(rdr[0]));
int activeCount = 1; // Count the leader
if (ddlMeetingDay.SelectedValue != "-1" && g.MeetingDay.LookupID != Convert.ToInt32(ddlMeetingDay.SelectedValue))
{
continue;
}
if (ddlTopic.SelectedValue != "-1" && g.Topic.LookupID != Convert.ToInt32(ddlTopic.SelectedValue))
{
continue;
}
if (ddlMaritalPreference.SelectedValue != "-1" && g.PrimaryMaritalStatus.LookupID != Convert.ToInt32(ddlMaritalPreference.SelectedValue))
{
continue;
}
if (ddlAgeRange.SelectedValue != "-1" && g.PrimaryAge.LookupID != Convert.ToInt32(ddlAgeRange.SelectedValue))
{
continue;
}
if (ddlType.SelectedValue != "-1" && g.GroupType.LookupID != Convert.ToInt32(ddlType.SelectedValue))
{
continue;
}
if (ddlArea.SelectedValue != "-1" && g.AreaID != -1 && g.AreaID != Convert.ToInt32(ddlArea.SelectedValue))
{
continue;
}
if (ddlCampus.SelectedValue != "-1" && g.Leader.Campus.CampusId != Convert.ToInt32(ddlCampus.SelectedValue))
{
continue;
}
//
// Check if the group is full.
//
foreach (GroupMember gm in g.Members)
{
if (gm.Active == true)
{
activeCount += 1;
}
}
if (activeCount >= g.MaxMembers)
{
continue;
}
//
// Add the group to the map.
//
placemark = new SmallGroupPlacemark(g);
placemark.SetAddedHandler("RegisterSmallGroup");
map.Placemarks.Add(placemark);
//
// Add the group to the list results.
//
dr = dt.NewRow();
dr["ID"] = g.GroupID;
dr["Name"] = g.Name;
dr["MeetingDay"] = g.MeetingDay.Value;
dr["MeetingTime"] = g.MeetingStartTime;
dr["Type"] = g.GroupType.Value;
dr["Topic"] = g.Topic.Value;
dr["AverageAge"] = Convert.ToInt32(g.AverageAge);
dr["Notes"] = g.Notes;
try
{
dr["Distance"] = Math.Round(Convert.ToDouble(rdr[1]), 2);
}
catch
{
dr["Distance"] = -1.0f;
}
dt.Rows.Add(dr);
}
catch { }
}
rdr.Close();
dgResults.DataSource = new DataView(dt);
dgResults.DataBind();
}
