/// <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); }
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(); }