private void ShowEdit() { ArrayList paramList = new ArrayList(); string[] selectedLocations = SelectedTemplateLocations.Split(','); pnlOverview.Visible = false; pnlEdit.Visible = true; // // Open the data reader // paramList.Add(new SqlParameter("OccurrenceTypeID", Convert.ToInt32(Page.Request.Params["Type"]))); SqlDataReader reader = new Arena.DataLayer.Organization.OrganizationData().ExecuteReader( "orgn_sp_get_locationByOccurrenceTypeID", paramList); cblLocations.Items.Clear(); while (reader.Read()) { String title, value; bool enabled; title = String.Format("{0} - {1}", reader["building_name"], reader["location_name"]); value = reader["location_id"].ToString(); enabled = (selectedLocations.Contains(value) || selectedLocations.Length == 0); cblLocations.Items.Add(new ListItem(title, value)); cblLocations.Items[cblLocations.Items.Count - 1].Selected = enabled; } }
public void dgLocation_Close(object sender, CommandEventArgs e) { StringBuilder sb = new StringBuilder(); // // Run the function to get the active occurrences for this location. // SqlDataReader reader = new Arena.DataLayer.Organization.OrganizationData().ExecuteReader( String.Format("SELECT * FROM cust_hdc_checkin_func_active_occurrencesByLocation({0})", e.CommandArgument)); while (reader.Read()) { if (sb.Length > 0) { sb.Append(","); } sb.Append(reader[0].ToString()); } hfCloseOccurrenceIDs.Value = sb.ToString(); lbCloseError.Text = ""; pnlCloseOccurrence.Visible = true; pnlDataFilter.Visible = false; pnlLocationGrid.Visible = false; pnlOccurrenceGrid.Visible = false; pnlTotalAttendance.Visible = false; dgClose_Bind(); }
void LoadCurrentOwners() { string currentValue = ddlCurrentOwner.SelectedValue; ddlCurrentOwner.Items.Clear(); ddlCurrentOwner.Items.Add(new ListItem("", "-1")); // Load Current Owners string query = @" SELECT DISTINCT P.person_id, P.last_name + ', ' + P.nick_name AS person_name FROM core_profile T LEFT OUTER JOIN evnt_event_profile E on E.profile_id = T.profile_id INNER JOIN core_person P ON P.person_id = T.owner_id WHERE (T.profile_type in (1,2) OR (T.profile_type = 4 and E.[end] > getdate()) ) ORDER BY person_name"; SqlDataReader rdr = new Arena.DataLayer.Organization.OrganizationData().ExecuteReader(query); while (rdr.Read()) { ListItem li = new ListItem(rdr["person_name"].ToString(), rdr["person_id"].ToString()); li.Selected = li.Value == currentValue; ddlCurrentOwner.Items.Add(li); } rdr.Close(); LoadTags(ddlCurrentOwner.SelectedValue); }
void btnProcess_Click(object sender, EventArgs e) { Arena.DataLayer.Organization.OrganizationData odata = new Arena.DataLayer.Organization.OrganizationData(); foreach (DataGridItem item in dgLosses.Items) { int familyId = (int)dgLosses.DataKeys[item.ItemIndex]; bool process = false; var cbProcess = item.Cells[1].Controls[0] as CheckBox; if (cbProcess != null && cbProcess.Checked) { process = true; } bool send = false; var cbSend = item.Cells[2].Controls[0] as CheckBox; if (cbSend != null && cbSend.Checked) { send = true; } string query = string.Format( "update cust_ccv_era_losses set processed = {0}, send_email = {1} where family_id = {2}", (process ? "1" : "0"), (send ? "1" : "0"), familyId.ToString()); odata.ExecuteNonQuery(query); } ShowList(); }
public static Arena.Core.Attribute GetPcoAttribute(AttributeGroup attributeGroup, string attributeName, DataType attributeType) { Arena.Core.Attribute pcoAttribute = attributeGroup.Attributes.FindByName(attributeName); if (pcoAttribute == null || pcoAttribute.AttributeId == -1) { Arena.DataLayer.Organization.OrganizationData oData = new Arena.DataLayer.Organization.OrganizationData(); string sql = string.Format( "SELECT ISNULL(MAX(attribute_order),-1) FROM core_attribute WHERE attribute_group_id = {0}", attributeGroup.AttributeGroupId.ToString()); int groupOrder = (int)oData.ExecuteScalar(sql); Guid attributeGuid = Guid.NewGuid(); sql = string.Format( "insert into core_attribute (guid, attribute_group_id, attribute_name, attribute_type, attribute_order) values ('{0}', {1}, '{2}', '{3}', (({4})+2))", attributeGuid.ToString(), attributeGroup.AttributeGroupId.ToString(), attributeName, Enum.Format(typeof(DataType), attributeType, "D"), groupOrder.ToString()); oData.ExecuteNonQuery(sql); pcoAttribute = new Arena.Core.Attribute(attributeGuid); attributeGroup.Attributes.Add(pcoAttribute); } return(pcoAttribute); }
public void ShowList() { ArrayList lst = new ArrayList(); lst.Add(new SqlParameter("@FromDate", tbFilterFrom.SelectedDate)); lst.Add(new SqlParameter("@ToDate", tbFilterTo.SelectedDate)); lst.Add(new SqlParameter("@Pastor", Convert.ToInt32(ddlPastor.SelectedValue))); lst.Add(new SqlParameter("@Processed", cbProcessed.Checked)); DataSet ds = new Arena.DataLayer.Organization.OrganizationData().ExecuteDataSet("cust_ccv_sp_era_loss_report", lst); dgLosses.Columns[3].Visible = cbProcessed.Checked; dgLosses.Columns[6].Visible = ddlPastor.SelectedValue == "0"; dgLosses.ItemType = "Family"; dgLosses.ItemBgColor = CurrentPortalPage.Setting("ItemBgColor", string.Empty, false); dgLosses.ItemAltBgColor = CurrentPortalPage.Setting("ItemAltBgColor", string.Empty, false); dgLosses.ItemMouseOverColor = CurrentPortalPage.Setting("ItemMouseOverColor", string.Empty, false); dgLosses.AddEnabled = false; dgLosses.DeleteEnabled = false; dgLosses.EditEnabled = false; dgLosses.MergeEnabled = false; dgLosses.MailEnabled = false; dgLosses.ExportEnabled = true; dgLosses.DataSource = ds.Tables[0]; dgLosses.DataBind(); }
protected void Page_Load(object sender, EventArgs e) { SqlDataReader reader = null; DataTable listDT = null; reader = new Arena.DataLayer.Organization.OrganizationData().ExecuteReader(this.rpt.Query); listDT = SqlReaderToDataTable(reader); Response.Clear(); Response.ContentType="text/csv"; StringBuilder sb = new StringBuilder(); for (int i = 0; i < listDT.Columns.Count; i++) { sb.Append(Convert.ToChar(34) + listDT.Columns[i].ColumnName + Convert.ToChar(34) + ','); } sb.Remove(sb.Length - 1, 1); sb.Append(Environment.NewLine); for (int j = 0; j < listDT.Rows.Count; j++) { for (int k = 0; k < listDT.Columns.Count; k++) { sb.Append(Convert.ToChar(34) + listDT.Rows[j][k].ToString() + Convert.ToChar(34) + ','); } sb.Remove(sb.Length - 1, 1); sb.Append(Environment.NewLine); } Response.Write(sb.ToString()); Response.End(); }
private void UpdateAddressAreas(bool reGeoCode) { Arena.DataLayer.Organization.OrganizationData oData = new Arena.DataLayer.Organization.OrganizationData(); AreaCollection areas = new AreaCollection(OrganizationId); SqlDataReader rdr = new Arena.DataLayer.Core.AddressData().GetAddresses(); while (rdr.Read()) { Address address = new Address((int)rdr["address_id"]); if (address != null && address.AddressID != -1) { if (reGeoCode && !address.StreetLine1.ToUpper().StartsWith("PO BOX") && address.Latitude == 0 && address.Longitude == 0 && address.Standardized && address.DateGeocoded.AddDays(30) < DateTime.Today) { bool active = false; PersonAddressCollection pac = new PersonAddressCollection(); pac.LoadByAddressID(address.AddressID); foreach (PersonAddress pa in pac) { Person person = new Person(pa.PersonID); if (person.RecordStatus == Arena.Enums.RecordStatus.Active) { active = true; break; } } if (active) { status.Text = address.ToString().Replace("\n", " "); address.Geocode("ImportMapPointAreas", true); string breakstring = address.StreetLine1; } } else if (address.Latitude != 0 && address.Longitude != 0) { status.Text = address.ToString().Replace("\n", " "); address.UpdateArea(areas); } System.Windows.Forms.Application.DoEvents(); } } rdr.Close(); status.Text = string.Empty; MessageBox.Show("Addresses have been updated", "Import Complete"); }
public void btnNumberBoard_Click(object sender, CommandEventArgs e) { OccurrenceAttendance oa = new OccurrenceAttendance(Convert.ToInt32(e.CommandArgument)); Arena.DataLayer.Organization.OrganizationData org = new Arena.DataLayer.Organization.OrganizationData(); String securityNumber = oa.SecurityCode.Substring(2); int promotionRequestID; // // Check if the security code is already posted. // promotionRequestID = FindPromotionRequest(oa.OccurrenceAttendanceID); if (promotionRequestID != -1) { PromotionRequest promotion = new PromotionRequest(promotionRequestID); promotion.Delete(); } else { PromotionRequest promotion = new PromotionRequest(); String html; // // Generate the HTML for this note. // html = String.Format("<p id=\"SecurityCode\">{0}</p>", securityNumber); // // Create the new promotion. // if (CampusID != -1) { promotion.Campus = new Campus(CampusID); } promotion.ContactName = ArenaContext.Current.Person.FullName; promotion.ContactEmail = ""; promotion.ContactPhone = ""; promotion.Title = oa.OccurrenceAttendanceID.ToString(); promotion.TopicArea = new Lookup(TopicAreaID); promotion.WebSummary = html; promotion.WebPromote = true; promotion.WebStartDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day); promotion.WebEndDate = promotion.WebStartDate.AddYears(1); promotion.WebApprovedBy = ArenaContext.Current.User.Identity.Name; promotion.WebApprovedDate = DateTime.Now; promotion.Save(ArenaContext.Current.User.Identity.Name); } dgAttendance_ReBind(null, null); }
private DataTable GetLocationData() { ArrayList paramList = new ArrayList(); paramList.Add(new SqlParameter("group_id", Convert.ToInt32(hfFilterTypeGroupID.Value))); paramList.Add(new SqlParameter("service_time", DateTime.Parse(hfFilterService.Value))); // // Open the data reader // SqlDataReader reader = new Arena.DataLayer.Organization.OrganizationData().ExecuteReader( "cust_hdc_checkin_sp_get_service_overviewByLocation", paramList); return(SqlReaderToDataTable(reader)); }
/// <summary> /// Load a list of person placemark objects from the given report ID. The /// list is constrained to the start and count parameters. /// </summary> /// <param name="reportid">The Arena Report to generate a list of people from.</param> /// <param name="start">The member index to start loading from.</param> /// <param name="count">The maximum number of people to load, pass Int32.MaxValue for complete load.</param> /// <returns>A list of PersonPlacemark objects.</returns> public List <PersonPlacemark> PersonPlacemarksInReport(int reportid, int start, int count) { List <PersonPlacemark> people = new List <PersonPlacemark>(); SqlDataReader rdr; ListReport report; if (PersonFieldOperationAllowed(PersonFields.Profile_Name, OperationType.View) == false) { return(people); } report = new ListReport(reportid); rdr = new Arena.DataLayer.Organization.OrganizationData().ExecuteReader(report.Query); people = PersonPlacemarksFromReader(rdr, start, count); rdr.Close(); return(people); }
public override bool PerformAction(Assignment assignment, Person currentPerson) { try { ArrayList lst = new ArrayList(); lst.Add(new SqlParameter("@AssignmentId", assignment != null ? assignment.AssignmentId : -1)); lst.Add(new SqlParameter("@PersonId", currentPerson != null ? currentPerson.PersonID : -1)); bool result = true; SqlDataReader rdr = new Arena.DataLayer.Organization.OrganizationData().ExecuteReader(StoredProcSetting, lst); if (rdr.Read()) { try { result = (bool)rdr["result"]; } catch { } } rdr.Close(); if (result) { // Because the Assignment Object's ProcessState method saves the assignment object before reading any // changes that this action may have made, every property on the passed in object should be updated // prior to returning (since we don't really know what properties the SQL Proc may have updated) Assignment newAssignment = new Assignment(assignment.AssignmentId); assignment.Description = newAssignment.Description; assignment.DueDate = newAssignment.DueDate; assignment.FieldValues = newAssignment.FieldValues; assignment.PriorityId = newAssignment.PriorityId; assignment.RequesterPersonId = newAssignment.RequesterPersonId; assignment.ResolutionText = newAssignment.ResolutionText; assignment.ResolvedDate = newAssignment.ResolvedDate; assignment.StateId = newAssignment.StateId; assignment.Title = newAssignment.Title; assignment.WorkerPersonId = newAssignment.WorkerPersonId; } return(result); } catch (System.Exception ex) { assignment.AddNote("Exception", ex.Message, false, null, "ExecuteSQLProc"); return(false); } }
public static AttributeGroup GetPcoAttributeGroup(int organizationId, Lookup pcoAccount) { if (pcoAccount.Qualifier2.Trim() == string.Empty) { pcoAccount.Qualifier2 = Guid.NewGuid().ToString(); pcoAccount.Save(); } Guid groupGuid = new Guid(pcoAccount.Qualifier2); AttributeGroup pcoAttributeGroup = new AttributeGroup(groupGuid); if (pcoAttributeGroup.AttributeGroupId == -1) { Arena.DataLayer.Organization.OrganizationData oData = new Arena.DataLayer.Organization.OrganizationData(); string sql = "SELECT ISNULL(MAX(group_order),-1) FROM core_attribute_group"; int groupOrder = (int)oData.ExecuteScalar(sql); sql = string.Format( "insert into core_attribute_group (guid, organization_id, group_name, group_order, display_location) values ('{0}', {1}, 'PCO - {2}', (({3})+2), 0)", groupGuid.ToString(), organizationId.ToString(), pcoAccount.Value, groupOrder.ToString()); oData.ExecuteNonQuery(sql); pcoAttributeGroup = new AttributeGroup(groupGuid); } GetPcoAttribute(pcoAttributeGroup, "PCO_Last_Sync", DataType.Document); GetPcoAttribute(pcoAttributeGroup, "PCO_Last_Sync_Arena", DataType.Document); GetPcoAttribute(pcoAttributeGroup, "PCO_ID", DataType.Int); GetPcoAttribute(pcoAttributeGroup, "PCO_Password", DataType.String); return(pcoAttributeGroup); }
void LoadTags(string ownerId) { ddlTag.Items.Clear(); if (ownerId != "" && ownerId != "-1") { // Load Tags string query = @" SELECT DISTINCT T.profile_id, T.profile_type, dbo.cust_ccv_profile_path(T.profile_id) as profile_path FROM core_profile T LEFT OUTER JOIN evnt_event_profile E on E.profile_id = T.profile_id WHERE (T.profile_type in (1,2) OR (T.profile_type = 4 and E.[end] > getdate()) ) AND T.owner_id = " + ownerId + @" ORDER BY T.profile_type, profile_path"; SqlDataReader rdr = new Arena.DataLayer.Organization.OrganizationData().ExecuteReader(query); while (rdr.Read()) { string profileName = rdr["profile_path"].ToString(); switch ((int)rdr["profile_type"]) { case 1: profileName = "[Ministry] " + profileName; break; case 2: profileName = "[Serving] " + profileName; break; case 4: profileName = "[Event] " + profileName; break; } ddlTag.Items.Add(new ListItem(profileName, rdr["profile_id"].ToString())); } rdr.Close(); } }
protected void Page_Load(object sender, System.EventArgs e) { if (!Page.IsPostBack) { // Load Staff Names ddlNewOwner.Items.Add(new ListItem("", "-1")); string query = @" SELECT DISTINCT P.person_id, P.last_name + ', ' + P.nick_name AS person_name FROM core_person P WHERE P.staff_member = 1 ORDER BY person_name"; SqlDataReader rdr = new Arena.DataLayer.Organization.OrganizationData().ExecuteReader(query); while (rdr.Read()) { ddlNewOwner.Items.Add(new ListItem(rdr["person_name"].ToString(), rdr["person_id"].ToString())); } rdr.Close(); LoadCurrentOwners(); } }
/// <summary> /// Retrieve all the CDR records that have not been loaded into the phone_cdr table /// yet. This method is called by the ArenaPbxCdr Agent. /// </summary> /// <returns>A collection of CDR records that are new since the last run.</returns> public CDRCollection GetCDRRecords() { CDRCollection cdrRecords = new CDRCollection(); SqlDataReader rdr; // // Execute the stored procedure. The stored procedure merges the two tables // (the phone_cdr table in Arena and the cdr table in Asterisk) and returns // a reader with only the CDR records from Asterisk that do not exist in Arena. // rdr = new Arena.DataLayer.Organization.OrganizationData().ExecuteReader("cust_asterisk_sp_get_cdr_records"); while (rdr.Read()) { CDR cdr = new CDR(); // // If the source channel is a SIP device (we do not yet use IAX) then // we need to strip out just the device name (extension number). Asterisk // provides this in a "SIP/268-293fab239" format. // string srcChannel = rdr["channel"].ToString(); if (srcChannel.ToUpper().StartsWith("SIP/")) { cdr.Source = srcChannel.Substring(4); cdr.Source = cdr.Source.Substring(0, cdr.Source.IndexOf('-')); } else { cdr.Source = rdr["src"].ToString(); } // // If the destination channel is a SIP device (we do not yet use IAX) then // we need to strip out just the device name (extension number). Asterisk // provides this in a "SIP/268-293fab239" format. // string dstChannel = rdr["dstchannel"].ToString(); if (dstChannel.ToUpper().StartsWith("SIP/")) { cdr.Destination = dstChannel.Substring(4); cdr.Destination = cdr.Destination.Substring(0, cdr.Destination.IndexOf('-')); } else { cdr.Destination = rdr["dst"].ToString(); } // // If the destination begins is 7 or more characters and does not begin with // a 9, then prepend the 9. Some of our phone calls have the 9, some do not. // Make sure they are all the same. // Next if it is a long distance call (e.g. 917605552732) strip out the 1 since // Arena does not use it. // if (cdr.Destination.Length >= 7 && cdr.Destination[0] != '9') { cdr.Destination = "9" + cdr.Destination; // Prepend a 9 for outward calls that don't have it. } if (cdr.Destination.Length > 7 && cdr.Destination.Substring(0, 2) == "91") { cdr.Destination = "9" + cdr.Destination.Substring(2); // Strip out the 1 for long distance } // // Get the CallerID as identified by Asterisk. // cdr.CallerID = rdr["clid"].ToString(); // // Get the time the call began. // if (!rdr.IsDBNull(rdr.GetOrdinal("start"))) { cdr.CallStart = (DateTime)rdr["start"]; } // // Get the time the call was answered (our system does not use this so the // stored procedure sets this to null). // if (!rdr.IsDBNull(rdr.GetOrdinal("answer"))) { cdr.Answered = (DateTime)rdr["answer"]; } // // Get the time the call was ended. // if (!rdr.IsDBNull(rdr.GetOrdinal("end"))) { cdr.CallEnd = (DateTime)rdr["end"]; } // // Get the duration of the call. As of Asterisk 1.6 the duration and billable // seconds is now a floating point, so it might return 129.4 seconds. Convert // to a whole number. // if (!rdr.IsDBNull(rdr.GetOrdinal("duration"))) { cdr.Duration = Convert.ToInt32(rdr["duration"]); } // // Get the billable duration of the call. // if (!rdr.IsDBNull(rdr.GetOrdinal("billsec"))) { cdr.BillSeconds = Convert.ToInt32(rdr["billsec"]); } // // The disposition is the "state" of the call. // if (!rdr.IsDBNull(rdr.GetOrdinal("disposition"))) { switch (rdr["disposition"].ToString()) { case "ANSWERED": cdr.Disposition = CDR_Disposition.Answered; break; case "NO ANSWER": cdr.Disposition = CDR_Disposition.No_Answer; break; case "BUSY": cdr.Disposition = CDR_Disposition.Busy; break; case "FAILED": cdr.Disposition = CDR_Disposition.Failed; break; } } // // Save the foreign key so that our stored procedure can find the // original CDR record later. // cdr.ForeignKey = rdr["uniqueid"].ToString(); // // Add the CDR record to the collection. The agent worker will then match // the CDR record to the person records associated with the call. // cdrRecords.Add(cdr); } rdr.Close(); return(cdrRecords); }
private bool CreateSandboxDatabase() { SqlConnection sql = null; SqlCommand sqlCommand; SqlDataReader rdr; String username = ArenaContext.Current.User.Identity.Name; String database = "ArenaSandbox_" + username; String dbVersion = ddlVersion.SelectedValue.Replace('.', '_'); IntPtr admin_token; // // Impersonate the logged in user. // try { Credentials creds = new Credentials(CurrentOrganization.OrganizationID, CredentialType.ActiveDirectory); if (LogonUser(creds.Username, AuthenticationDomain, creds.Password, 2, 0, out admin_token) != 0) { ImpersonateLoggedOnUser(admin_token); // // Open a connection to the SQL server. // sql = new SqlConnection("Data Source=refreshcacheare\\arena;Trusted_Connection=yes"); sql.Open(); // // Drop and re-create the database. // try { sqlCommand = new SqlCommand("DROP DATABASE " + database, sql); sqlCommand.ExecuteNonQuery(); } catch { } sqlCommand = new SqlCommand("RESTORE DATABASE " + database + " FROM DISK = '" + SandboxPath + "\\Templates\\" + ddlVersion.SelectedValue + ".bak'" + " WITH MOVE 'Template" + dbVersion + "DB' TO '" + SandboxPath + "\\" + username + ".mdf'" + ", MOVE 'Template" + dbVersion + "DB_Log' TO '" + SandboxPath + "\\" + username + ".ldf'", sql); sqlCommand.ExecuteNonQuery(); // // Create the current users login as the Administrator of // the new database. // Arena.DataLayer.Organization.OrganizationData org = new Arena.DataLayer.Organization.OrganizationData(); rdr = org.ExecuteReader("SELECT [password] FROM secu_login WHERE login_id = '" + username + "'"); rdr.Read(); sql.ChangeDatabase(database); sqlCommand = new SqlCommand("secu_sp_save_login", sql); sqlCommand.CommandType = CommandType.StoredProcedure; sqlCommand.Parameters.Add(new SqlParameter("@PersonID", 1)); sqlCommand.Parameters.Add(new SqlParameter("@LoginID", username)); sqlCommand.Parameters.Add(new SqlParameter("@Password", rdr[0])); sqlCommand.Parameters.Add(new SqlParameter("@UserID", "Sandbox")); sqlCommand.Parameters.Add(new SqlParameter("@Active", true)); sqlCommand.Parameters.Add(new SqlParameter("@AuthenticationProvider", AuthenticationProvider.Database)); sqlCommand.Parameters.Add(new SqlParameter("@AccountLocked", false)); sqlCommand.Parameters.Add(new SqlParameter("@DateLockExpires", DateTime.Parse("1900-01-01 00:00:00"))); sqlCommand.Parameters.Add(new SqlParameter("@ForceChangePassword", false)); sqlCommand.ExecuteNonQuery(); // // Close the database. // sql.Close(); } else { return(false); } } catch (Exception ex) { if (sql != null) { sql.Close(); } RevertToSelf(); throw ex; } // // Cleanup after pretending to be the user. // RevertToSelf(); return(true); }
private void ShowView() { Address address = new Address(Int32.Parse(AddressIDSetting)); phMap.Controls.Clear(); Page.ClientScript.RegisterStartupScript(typeof(string), "VirtualEarth", "<script src=\"http://dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=5\"></script>", false); System.Web.UI.WebControls.Panel pnlMap = new System.Web.UI.WebControls.Panel(); pnlMap.ID = "pnlMap"; pnlMap.Style.Add("position", "relative"); pnlMap.Style.Add("width", MapWidthSetting + "px"); pnlMap.Style.Add("height", MapHeightSetting + "px"); phMap.Controls.Add(pnlMap); StringBuilder sbVEScript = new StringBuilder(); sbVEScript.Append("var map = null;\n"); sbVEScript.Append("window.onload = function() {LoadMyMap();};\n"); sbVEScript.Append("\nfunction LoadMyMap(){\n"); sbVEScript.AppendFormat("\tmap = new VEMap('{0}');\n", pnlMap.ClientID); sbVEScript.Append("\tmap.LoadMap();\n\n"); sbVEScript.Append("\tmap.AttachEvent('onclick', mapClick);\n\n"); //sbVEScript.Append("\tmap.ClearInfoBoxStyles();\n\n"); //sbVEScript.Append("\tvar points = new Array(\n"); //for (int i = 0; i < Area.Coordinates.Count; i++) //{ // AreaCoordinate coord = Area.Coordinates[i]; // sbVEScript.AppendFormat("\t\tnew VELatLong({0}, {1})", coord.Latitude.ToString(), coord.Longitude.ToString()); // if (i < Area.Coordinates.Count - 1) // sbVEScript.Append(",\n"); //} //sbVEScript.Append("\n\t);\n"); //sbVEScript.Append("\tvar shape = new VEShape(VEShapeType.Polygon, points);\n"); //sbVEScript.Append("\tmap.SetMapView(points);\n"); //sbVEScript.Append("\tshape.SetLineColor(new VEColor(255,0,0,1));\n"); //sbVEScript.Append("\tshape.SetLineWidth(2);\n"); //sbVEScript.Append("\tshape.SetFillColor(new VEColor(236,183,49,.3));\n"); //sbVEScript.Append("\tshape.HideIcon();\n"); //sbVEScript.AppendFormat("\tshape.SetTitle('{0}');\n", Area.Name); //sbVEScript.Append("\n\tmap.AddShape(shape);\n"); sbVEScript.AppendFormat("\n\tshape = new VEShape(VEShapeType.Pushpin, new VELatLong({0}, {1}));\n", address.Latitude.ToString(), address.Longitude.ToString()); sbVEScript.Append("\tshape.SetCustomIcon('images/map/pin_blue.png');\n"); sbVEScript.Append("\tshape.SetTitle(\"Center\");\n"); sbVEScript.Append("\tmap.AddShape(shape);\n"); double maxLatitude = double.MinValue; double maxLongitude = double.MinValue; double minLatitude = double.MinValue; double minLongitude = double.MinValue; ArrayList lst = new ArrayList(); lst.Add(new SqlParameter("@TargetAddressID", address.AddressID)); SqlDataReader rdr = new Arena.DataLayer.Organization.OrganizationData().ExecuteReader("cust_sp_target_ccv_location_members", lst); while (rdr.Read()) { double latitude = (double)rdr["Latitude"]; double longitude = (double)rdr["Longitude"]; if (maxLatitude == double.MinValue || maxLatitude < latitude) { maxLatitude = latitude; } if (maxLongitude == double.MinValue || maxLongitude < longitude) { maxLongitude = longitude; } if (minLatitude == double.MinValue || minLatitude > latitude) { minLatitude = latitude; } if (minLongitude == double.MinValue || minLongitude > longitude) { minLongitude = longitude; } sbVEScript.AppendFormat("\n\tshape = new VEShape(VEShapeType.Pushpin, new VELatLong({0}, {1}));\n", latitude.ToString(), longitude.ToString()); sbVEScript.AppendFormat("\tshape.SetCustomIcon('images/map/{0}');\n", rdr["pin_icon"].ToString()); sbVEScript.AppendFormat("\tshape.SetTitle(\"{0}\");\n", rdr["family_name"].ToString()); sbVEScript.AppendFormat("\tshape.SetDescription('{0}');\n", BuildDetailPanel(rdr)); sbVEScript.AppendFormat("\tshape.SetMoreInfoURL('default.aspx?page=7&guid={0}');\n", rdr["person_guid"].ToString()); sbVEScript.Append("\tmap.AddShape(shape);\n"); } rdr.Close(); sbVEScript.Append("\tvar allPoints = new Array(\n"); sbVEScript.AppendFormat("\t\tnew VELatLong({0}, {1}),\n", minLatitude.ToString(), minLongitude.ToString()); sbVEScript.AppendFormat("\t\tnew VELatLong({0}, {1}))\n", maxLatitude.ToString(), maxLongitude.ToString()); sbVEScript.Append("\tmap.SetMapView(allPoints);\n"); //sbVEScript.Append("\tmap.ZoomIn();\n"); sbVEScript.Append("}\n"); sbVEScript.Append("\nfunction mapClick(e){\n"); sbVEScript.Append("\tvar shape = map.GetShapeByID(e.elementID);\n"); sbVEScript.Append("\twindow.location = shape.GetMoreInfoURL();\n"); sbVEScript.Append("}\n"); Page.ClientScript.RegisterStartupScript(typeof(string), "LoadMap", sbVEScript.ToString(), true); }
private DataTable GetOccurrenceData() { SqlDataReader reader = null; // // Open the data reader // StringBuilder command = new StringBuilder("SELECT coa.occurrence_attendance_id,coa.occurrence_id,coa.person_id,coa.security_code,coa.attended,coa.check_in_time,coa.check_out_time" + ",cp.last_name + ', ' + cp.first_name AS 'common_name',cp.first_name,cp.last_name,cp.nick_name,cp.gender,cp.birth_date,cp.graduation_date,cp.guid" + ",co.occurrence_name AS 'common_occurrence_name'" + ",cotg.group_name + ' - ' + cot.type_name AS 'common_attendance_name'" + ",ol.building_name + ' - ' + ol.location_name AS 'common_location_name',ol.building_name,ol.location_name"); if (AbilityLevelAttributeIDSetting != "") { command.AppendFormat(",(SELECT cl_al.lookup_value" + " FROM core_person_attribute AS cpa_al" + " LEFT JOIN core_lookup AS cl_al ON cl_al.lookup_id = cpa_al.int_value"+ " WHERE cpa_al.person_id = cp.person_id AND cpa_al.attribute_id = '{0}') AS 'ability_level'", AbilityLevelAttributeIDSetting); } else { command.Append(",'' AS 'ability_level'"); } command.AppendFormat(" FROM core_occurrence_attendance AS coa" + " LEFT JOIN core_person AS cp ON cp.person_id = coa.person_id" + " LEFT JOIN core_occurrence AS co ON coa.occurrence_id = co.occurrence_id" + " LEFT JOIN core_occurrence_type AS cot ON cot.occurrence_type_id = co.occurrence_type" + " LEFT JOIN core_occurrence_type_group AS cotg ON cotg.group_id = cot.group_id" + " LEFT JOIN orgn_location AS ol ON ol.location_id = co.location_id" + " WHERE co.location_id IS NOT NULL" + " AND co.occurrence_start_time = '{0}'", (hfFilterService.Value != "" ? DateTime.Parse(hfFilterService.Value) : DateTime.Parse("01-01-1900 00:00:00"))); // // Do the basic occurrence filtering. // if (hfFilterOccurrenceID.Value != "-1") { command.AppendFormat(" AND coa.occurrence_id = {0}", hfFilterOccurrenceID.Value); } // // Add a filter on name. // if (hfFilterName.Value != "") { if (hfFilterName.Value.IndexOf(' ') == -1) { command.AppendFormat(" AND (cp.first_name LIKE '%{0}%' OR cp.last_name LIKE '%{0}%')", hfFilterName.Value); } else { String[] names = hfFilterName.Value.Split(new char[1] { ' ' }, 2); command.AppendFormat(" AND (cp.first_name LIKE '%{0}%' OR cp.last_name LIKE '%{1}%')", names[0], names[1]); } } // // Add a filter on attendance type. // if (hfFilterTypeGroupID.Value != "-1" && !String.IsNullOrEmpty(hfFilterTypeGroupID.Value)) { if (hfFilterTypeID.Value != "-1") { command.AppendFormat(" AND cot.occurrence_type_id = {0}", hfFilterTypeID.Value); } else { command.AppendFormat(" AND cotg.group_id = {0}", hfFilterTypeGroupID.Value); } } // // Add a filter on location. // if (hfFilterLocationID.Value != "-1") { command.AppendFormat(" AND ol.location_id = {0}", hfFilterLocationID.Value); } // // By default, sort by common_name(last, first). // command.Append(" ORDER BY common_name"); try { reader = new Arena.DataLayer.Organization.OrganizationData().ExecuteReader(command.ToString()); } catch { throw new Exception(command.ToString()); } return(SqlReaderToDataTable(reader)); }
private DataTable GetOverviewData() { Hashtable table; StringBuilder sb; Dictionary <String, Hashtable> overview = new Dictionary <String, Hashtable>(); ArrayList paramList = new ArrayList(), ids; // // Open the data reader // paramList.Add(new SqlParameter("TypeID", Convert.ToInt32(Page.Request.Params["Type"]))); SqlDataReader reader = new Arena.DataLayer.Organization.OrganizationData().ExecuteReader( "cust_hdc_checkin_sp_templateLocationsByTypeID", paramList); while (reader.Read()) { if (overview.ContainsKey(reader["occurrence_type_template_id"].ToString()) == false) { table = new Hashtable(); overview.Add(reader["occurrence_type_template_id"].ToString(), table); table["template_id"] = reader["occurrence_type_template_id"]; table["location_names"] = new StringBuilder(); table["location_ids"] = new ArrayList(); table["occurrence_freq_type"] = reader["occurrence_freq_type"]; table["freq_qualifier"] = reader["freq_qualifier"]; table["start_time"] = reader["start_time"]; table["check_in_start"] = reader["check_in_start"]; table["schedule_name"] = reader["schedule_name"]; } else { table = overview[reader["occurrence_type_template_id"].ToString()]; } sb = (StringBuilder)table["location_names"]; ids = (ArrayList)table["location_ids"]; if (sb.Length == 0) { sb.Append(reader["location_name"]); } else { sb.AppendFormat(",{0}", reader["location_name"]); } ids.Add(reader["location_id"].ToString()); } DataTable dt = new DataTable("hdc_customTable"); dt.Columns.Add("template_id"); dt.Columns.Add("location_names"); dt.Columns.Add("location_ids"); dt.Columns.Add("occurrence_freq_type"); dt.Columns.Add("occurrence_freq_type_name"); dt.Columns.Add("freq_qualifier"); dt.Columns.Add("freq_qualifier_name"); dt.Columns.Add("start_time"); dt.Columns.Add("check_in_start"); dt.Columns.Add("schedule_name"); foreach (KeyValuePair <String, Hashtable> kvp in overview) { DataRow row = dt.NewRow(); table = (Hashtable)kvp.Value; row["template_id"] = table["template_id"]; row["location_names"] = table["location_names"].ToString(); row["location_ids"] = String.Join(",", (string[])((ArrayList)table["location_ids"]).ToArray(typeof(string))); row["occurrence_freq_type"] = table["occurrence_freq_type"]; row["occurrence_freq_type_name"] = ""; row["freq_qualifier"] = table["freq_qualifier"]; row["freq_qualifier_name"] = ""; row["start_time"] = table["start_time"]; row["check_in_start"] = table["check_in_start"]; row["schedule_name"] = table["schedule_name"]; if (row["occurrence_freq_type"].ToString() == "1") { row["occurrence_freq_type_name"] = "Weekly"; if (row["freq_qualifier"].ToString() == "0") { row["freq_qualifier_name"] = "Sunday"; } else if (row["freq_qualifier"].ToString() == "1") { row["freq_qualifier_name"] = "Monday"; } else if (row["freq_qualifier"].ToString() == "2") { row["freq_qualifier_name"] = "Tuesday"; } else if (row["freq_qualifier"].ToString() == "3") { row["freq_qualifier_name"] = "Wednesday"; } else if (row["freq_qualifier"].ToString() == "4") { row["freq_qualifier_name"] = "Thursday"; } else if (row["freq_qualifier"].ToString() == "5") { row["freq_qualifier_name"] = "Friday"; } else if (row["freq_qualifier"].ToString() == "6") { row["freq_qualifier_name"] = "Saturday"; } } dt.Rows.Add(row); } return(dt); }
public WorkerResultStatus ProcessVisitors(out string message, out int state) { Trace.Write("Starting FirstTimeVisitAssignments Agent...\n"); WorkerResultStatus workerResultStatus = WorkerResultStatus.Ok; message = string.Empty; state = STATE_OK; try { //groupAssignmentType = new AssignmentType(GroupVisitAssignmentType); //if (groupAssignmentType.AssignmentTypeId == -1) // throw new Exception("Invalid 'Neighborhood Group Assignment Type' setting"); childAssignmentType = new AssignmentType(ChildVisitAssignmentType); if (childAssignmentType.AssignmentTypeId == -1) { throw new Exception("Invalid 'Child Assignment Type' setting"); } juniorHighAssignmentType = new AssignmentType(JuniorHighVisitAssignmentType); if (juniorHighAssignmentType.AssignmentTypeId == -1) { throw new Exception("Invalid 'Junior High Assignment Type' setting"); } highSchoolAssignmentType = new AssignmentType(HighSchoolVisitAssignmentType); if (highSchoolAssignmentType.AssignmentTypeId == -1) { throw new Exception("Invalid 'High School Assignment Type' setting"); } adultAssignmentType = new AssignmentType(AdultVisitAssignmentType); if (adultAssignmentType.AssignmentTypeId == -1) { throw new Exception("Invalid 'Adult Assignment Type' setting"); } //groupFamilyIDField = GetAssignmentTypeField(groupAssignmentType, "Family ID"); //groupFirstVisitDateField = GetAssignmentTypeField(groupAssignmentType, "First Visit Date"); childFamilyIDField = GetAssignmentTypeField(childAssignmentType, "Family ID"); childFirstVisitDateField = GetAssignmentTypeField(childAssignmentType, "First Visit Date"); juniorHighFamilyIDField = GetAssignmentTypeField(juniorHighAssignmentType, "Family ID"); juniorHighFirstVisitDateField = GetAssignmentTypeField(juniorHighAssignmentType, "First Visit Date"); highSchoolFamilyIDField = GetAssignmentTypeField(highSchoolAssignmentType, "Family ID"); highSchoolFirstVisitDateField = GetAssignmentTypeField(highSchoolAssignmentType, "First Visit Date"); adultFamilyIDField = GetAssignmentTypeField(adultAssignmentType, "Family ID"); adultFirstVisitDateField = GetAssignmentTypeField(adultAssignmentType, "First Visit Date"); Lookup child = new Lookup(SystemLookup.FamilyRole_Child); DateTime beginDate = DateTime.Today.Date.AddDays(-DaysBack); ArrayList lst = new ArrayList(); lst.Add(new SqlParameter("@AttributeID", FirstTimeVisitAttributeID)); lst.Add(new SqlParameter("@BeginDate", beginDate)); //lst.Add(new SqlParameter("@GroupFamilyIDField", groupFamilyIDField.CustomFieldId)); lst.Add(new SqlParameter("@ChildFamilyIDField", childFamilyIDField.CustomFieldId)); lst.Add(new SqlParameter("@JuniorHighFamilyIDField", juniorHighFamilyIDField.CustomFieldId)); lst.Add(new SqlParameter("@HighSchoolFamilyIDField", highSchoolFamilyIDField.CustomFieldId)); lst.Add(new SqlParameter("@AdultFamilyIDField", adultFamilyIDField.CustomFieldId)); Arena.DataLayer.Organization.OrganizationData oData = new Arena.DataLayer.Organization.OrganizationData(); SqlDataReader rdr = oData.ExecuteReader("cust_ccv_recent_visitor_families", lst); while (rdr.Read()) { OccurrenceAttendanceCollection attendances = new OccurrenceAttendanceCollection(); //bool interestedInGroup = false; Family family = new Family((int)rdr["family_id"]); bool newFamily = true; int childCount = 0; int adultCount = 0; bool attendedHS = false; bool attendedJH = false; foreach (FamilyMember fm in family.FamilyMembers) { PersonAttribute pa = (PersonAttribute)fm.Attributes.FindByID(FirstTimeVisitAttributeID); if (pa != null && !pa.DateValue.IsEmptyDate() && pa.DateValue < beginDate) { newFamily = false; } if (newFamily) { if (fm.FamilyRole.LookupID == child.LookupID) { childCount++; } else { adultCount++; } //pa = (PersonAttribute)fm.Attributes.FindByID(InterestedInGroupAttributeID); //if (pa != null && pa.IntValue == 1) // interestedInGroup = true; ArrayList lstOccurrence = new ArrayList(); lstOccurrence.Add(new SqlParameter("@PersonID", fm.PersonID)); SqlDataReader rdrOccurrence = oData.ExecuteReader("cust_ccv_recent_visitor_first_checkin", lstOccurrence); if (rdrOccurrence.Read()) { if (((DateTime)rdrOccurrence["first_attended"]).Date >= beginDate) { OccurrenceAttendance oa = new OccurrenceAttendance((int)rdrOccurrence["occurrence_attendance_id"]); if (oa.Occurrence.OccurrenceType.OccurrenceTypeId == HighSchoolAttendanceTypeID) { attendedHS = true; } else if (oa.Occurrence.OccurrenceType.OccurrenceTypeId == JuniorHighAttendanceTypeID) { attendedJH = true; } attendances.Add(oa); } } rdrOccurrence.Close(); } } if (newFamily) { _regionalPastorID = -1; Assignment assignment = new Assignment(); assignment.Title = family.FamilyName; assignment.Description = BuildDescription(family, attendances); assignment.RequesterPersonId = family.FamilyHead.PersonID; //if (interestedInGroup) //{ // assignment.AssignmentTypeId = groupAssignmentType.AssignmentTypeId; // assignment.PriorityId = groupAssignmentType.DefaultPriorityId; // assignment.StatusId = groupAssignmentType.DefaultStatusId; // AssignmentFieldValue familyIDField = new AssignmentFieldValue(groupFamilyIDField.CustomFieldId); // familyIDField.SelectedValue = family.FamilyID.ToString(); // assignment.FieldValues.Add(familyIDField); // AssignmentFieldValue firstVisitDateField = new AssignmentFieldValue(groupFirstVisitDateField.CustomFieldId); // firstVisitDateField.SelectedValue = ((DateTime)rdr["first_visit"]).ToShortDateString(); // assignment.FieldValues.Add(firstVisitDateField); // if (_regionalPastorID != -1) // assignment.SubmitAssignmentEntry(childAssignmentType.Owner != null ? adultAssignmentType.Owner : family.FamilyHead, "FirstTimeVisitAssignment Agent", _regionalPastorID); // else // assignment.SubmitAssignmentEntry(childAssignmentType.Owner != null ? adultAssignmentType.Owner : family.FamilyHead, "FirstTimeVisitAssignment Agent"); //} //else //{ if (attendedHS) { assignment.AssignmentTypeId = highSchoolAssignmentType.AssignmentTypeId; assignment.PriorityId = highSchoolAssignmentType.DefaultPriorityId; AssignmentFieldValue familyIDField = new AssignmentFieldValue(highSchoolFamilyIDField.CustomFieldId); familyIDField.SelectedValue = family.FamilyID.ToString(); assignment.FieldValues.Add(familyIDField); AssignmentFieldValue firstVisitDateField = new AssignmentFieldValue(highSchoolFirstVisitDateField.CustomFieldId); firstVisitDateField.SelectedValue = ((DateTime)rdr["first_visit"]).ToShortDateString(); assignment.FieldValues.Add(firstVisitDateField); assignment.SubmitAssignmentEntry(highSchoolAssignmentType.Owner != null ? highSchoolAssignmentType.Owner : family.FamilyHead, "FirstTimeVisitAssignment Agent"); } else if (attendedJH) { assignment.AssignmentTypeId = juniorHighAssignmentType.AssignmentTypeId; assignment.PriorityId = juniorHighAssignmentType.DefaultPriorityId; AssignmentFieldValue familyIDField = new AssignmentFieldValue(juniorHighFamilyIDField.CustomFieldId); familyIDField.SelectedValue = family.FamilyID.ToString(); assignment.FieldValues.Add(familyIDField); AssignmentFieldValue firstVisitDateField = new AssignmentFieldValue(juniorHighFirstVisitDateField.CustomFieldId); firstVisitDateField.SelectedValue = ((DateTime)rdr["first_visit"]).ToShortDateString(); assignment.FieldValues.Add(firstVisitDateField); assignment.SubmitAssignmentEntry(highSchoolAssignmentType.Owner != null ? juniorHighAssignmentType.Owner : family.FamilyHead, "FirstTimeVisitAssignment Agent"); } else if (childCount == 0) { assignment.AssignmentTypeId = adultAssignmentType.AssignmentTypeId; assignment.PriorityId = adultAssignmentType.DefaultPriorityId; AssignmentFieldValue familyIDField = new AssignmentFieldValue(adultFamilyIDField.CustomFieldId); familyIDField.SelectedValue = family.FamilyID.ToString(); assignment.FieldValues.Add(familyIDField); AssignmentFieldValue firstVisitDateField = new AssignmentFieldValue(adultFirstVisitDateField.CustomFieldId); firstVisitDateField.SelectedValue = ((DateTime)rdr["first_visit"]).ToShortDateString(); assignment.FieldValues.Add(firstVisitDateField); assignment.SubmitAssignmentEntry(adultAssignmentType.Owner != null ? adultAssignmentType.Owner : family.FamilyHead, "FirstTimeVisitAssignment Agent"); } else if (adultCount > 0) { assignment.AssignmentTypeId = childAssignmentType.AssignmentTypeId; assignment.PriorityId = childAssignmentType.DefaultPriorityId; AssignmentFieldValue familyIDField = new AssignmentFieldValue(childFamilyIDField.CustomFieldId); familyIDField.SelectedValue = family.FamilyID.ToString(); assignment.FieldValues.Add(familyIDField); AssignmentFieldValue firstVisitDateField = new AssignmentFieldValue(childFirstVisitDateField.CustomFieldId); firstVisitDateField.SelectedValue = ((DateTime)rdr["first_visit"]).ToShortDateString(); assignment.FieldValues.Add(firstVisitDateField); assignment.SubmitAssignmentEntry(childAssignmentType.Owner != null ? childAssignmentType.Owner : family.FamilyHead, "FirstTimeVisitAssignment Agent"); } } } rdr.Close(); } catch (Exception ex) { workerResultStatus = WorkerResultStatus.Exception; message = "An error occured while processing First Time Visitor Assignments.\n\nMessage\n------------------------\n" + ex.Message + "\n\nStack Trace\n------------------------\n" + ex.StackTrace; } finally { } return(workerResultStatus); }
public WorkerResultStatus SendEmail(out string message, out int state) { WorkerResultStatus workerResultStatus = WorkerResultStatus.Ok; message = string.Empty; state = STATE_OK; try { AreaOutreachCoordinatorCollection pastors = new AreaOutreachCoordinatorCollection(); pastors.LoadByRole(1623); Arena.DataLayer.Organization.OrganizationData oData = new Arena.DataLayer.Organization.OrganizationData(); string query = "SELECT * FROM cust_ccv_era_losses WHERE processed = 1 AND send_email = 1 AND sent = 0"; SqlDataReader rdr = oData.ExecuteReader(query); while (rdr.Read()) { Family family = new Family((int)rdr["family_id"]); FamilyMember familyHead = family.FamilyHead; if (familyHead != null && familyHead.Emails.Count > 0) { Area area = familyHead.Area; if (area != null) { Person pastor = null; foreach (AreaOutreachCoordinator coord in pastors) { if (coord.AreaId == area.AreaID) { pastor = new Person(coord.PersonId); break; } } if (pastor != null) { Arena.Custom.CCV.Core.Communications.PotentialLossNotification lossNotification = new Arena.Custom.CCV.Core.Communications.PotentialLossNotification(); Dictionary <string, string> fields = new Dictionary <string, string>(); fields.Add("##RecipientFirstName##", familyHead.NickName); fields.Add("##RecipientLastName##", familyHead.LastName); fields.Add("##RecipientEmail##", familyHead.Emails.FirstActive); fields.Add("##PastorName##", pastor.FullName); fields.Add("##PastorEmail##", pastor.Emails.FirstActive); PersonPhone bPhone = pastor.Phones.FindByType(SystemLookup.PhoneType_Business); fields.Add("##PastorBusinessPhone##", bPhone != null ? bPhone.Number : string.Empty); PersonPhone cPhone = pastor.Phones.FindByType(SystemLookup.PhoneType_Cell); fields.Add("##PastorCellPhone##", cPhone != null ? cPhone.Number : string.Empty); if (lossNotification.Send(familyHead.Emails.FirstActive, fields, familyHead.PersonID)) { string updateQuery = string.Format("UPDATE cust_ccv_era_losses SET sent = 1 WHERE family_id = {0}", family.FamilyID.ToString()); oData.ExecuteNonQuery(updateQuery); } } } } } rdr.Close(); } catch (Exception ex) { workerResultStatus = WorkerResultStatus.Exception; message = "An error occured while processing ERA Loss Notifications.\n\nMessage\n------------------------\n" + ex.Message + "\n\nStack Trace\n------------------------\n" + ex.StackTrace; } return(workerResultStatus); }