{"action":"created","issue":{"url":"https://api.github.com/repos/gmo/arc/issues/8","repository_url":"https://api.github.com/repos/gmo/arc","labels_url":"https://api.github.com/repos/gmo/arc/issues/8/labels{/name}","comments_url":"https://api.github.com/repos/gmo/arc/issues/8/comments","events_url":"https://api.github.com/repos/gmo/arc/issues/8/events","html_url":"https://github.com/gmo/arc/issues/8","id":179551945,"number":8,"title":"Weekly Discipleship Report - OTG checkboxes","user":{"login":"justinmiller24","id":122921,"avatar_url":"https://avatars.githubusercontent.com/u/122921?v=3","gravatar_id":"","url":"https://api.github.com/users/justinmiller24","html_url":"https://github.com/justinmiller24","followers_url":"https://api.github.com/users/justinmiller24/followers","following_url":"https://api.github.com/users/justinmiller24/following{/other_user}","gists_url":"https://api.github.com/users/justinmiller24/gists{/gist_id}","starred_url":"https://api.github.com/users/justinmiller24/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/justinmiller24/subscriptions","organizations_url":"https://api.github.com/users/justinmiller24/orgs","repos_url":"https://api.github.com/users/justinmiller24/repos","events_url":"https://api.github.com/users/justinmiller24/events{/privacy}","received_events_url":"https://api.github.com/users/justinmiller24/received_events","type":"User","site_admin":false},"labels":[{"id":427101767,"url":"https://api.github.com/repos/gmo/arc/labels/bug","name":"bug","color":"fc2929","default":true},{"id":427102891,"url":"https://api.github.com/repos/gmo/arc/labels/story","name":"story","color":"159818","default":false}],"state":"open","locked":false,"assignee":{"login":"mikegillis677","id":5703580,"avatar_url":"https://avatars.githubusercontent.com/u/5703580?v=3","gravatar_id":"","url":"https://api.github.com/users/mikegillis677","html_url":"https://github.com/mikegillis677","followers_url":"https://api.github.com/users/mikegillis677/followers","following_url":"https://api.github.com/users/mikegillis677/following{/other_user}","gists_url":"https://api.github.com/users/mikegillis677/gists{/gist_id}","starred_url":"https://api.github.com/users/mikegillis677/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mikegillis677/subscriptions","organizations_url":"https://api.github.com/users/mikegillis677/orgs","repos_url":"https://api.github.com/users/mikegillis677/repos","events_url":"https://api.github.com/users/mikegillis677/events{/privacy}","received_events_url":"https://api.github.com/users/mikegillis677/received_events","type":"User","site_admin":false},"assignees":[{"login":"mikegillis677","id":5703580,"avatar_url":"https://avatars.githubusercontent.com/u/5703580?v=3","gravatar_id":"","url":"https://api.github.com/users/mikegillis677","html_url":"https://github.com/mikegillis677","followers_url":"https://api.github.com/users/mikegillis677/followers","following_url":"https://api.github.com/users/mikegillis677/following{/other_user}","gists_url":"https://api.github.com/users/mikegillis677/gists{/gist_id}","starred_url":"https://api.github.com/users/mikegillis677/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mikegillis677/subscriptions","organizations_url":"https://api.github.com/users/mikegillis677/orgs","repos_url":"https://api.github.com/users/mikegillis677/repos","events_url":"https://api.github.com/users/mikegillis677/events{/privacy}","received_events_url":"https://api.github.com/users/mikegillis677/received_events","type":"User","site_admin":false}],"milestone":null,"comments":3,"created_at":"2016-09-27T16:57:50Z","updated_at":"2016-11-02T19:08:02Z","closed_at":null,"body":"Background:\n- For OTG connections, GMO made the decision to count \"total disciples with at least one checked connection checkbox\" as part of the reported OTG number. For FY16, this accounted for 4,560 total seekers (disciples). For FY17, the overall goal will be 5,000 or 10,000, however a large number of these will come from the ARC discipleship dashboard checkboxes (de-duped), which Stacy / Becky do not currently have any visibility into.\n\nTasks:\n- Generate the number of \"total disciples with at least one checked connection checkbox\" (all time number) on a weekly basis and send out to the same group that currently gets the automated (weekly) discipleship summary report. (Note: this can be included in the existing report, or sent separately, but the main idea is to give Stacy and others visibility into how this number progresses throughout the year.)\n"},"comment":{"url":"https://api.github.com/repos/gmo/arc/issues/comments/257967989","html_url":"https://github.com/gmo/arc/issues/8#issuecomment-257967989","issue_url":"https://api.github.com/repos/gmo/arc/issues/8","id":257967989,"user":{"login":"mikegillis677","id":5703580,"avatar_url":"https://avatars.githubusercontent.com/u/5703580?v=3","gravatar_id":"","url":"https://api.github.com/users/mikegillis677","html_url":"https://github.com/mikegillis677","followers_url":"https://api.github.com/users/mikegillis677/followers","following_url":"https://api.github.com/users/mikegillis677/following{/other_user}","gists_url":"https://api.github.com/users/mikegillis677/gists{/gist_id}","starred_url":"https://api.github.com/users/mikegillis677/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mikegillis677/subscriptions","organizations_url":"https://api.github.com/users/mikegillis677/orgs","repos_url":"https://api.github.com/users/mikegillis677/repos","events_url":"https://api.github.com/users/mikegillis677/events{/privacy}","received_events_url":"https://api.github.com/users/mikegillis677/received_events","type":"User","site_admin":false},"created_at":"2016-11-02T19:08:02Z","updated_at":"2016-11-02T19:08:02Z","body":"I iterate the following query for each disciple id:\r\n```\r\nSELECT d.contact_id,\r\n\t\t\t\tCOALESCE(lan.description, \"Unknown\") AS language,\r\n\t\t\t\tLOWER(ch.name) AS channel, c.first_name, c.last_name, dom.domain,\r\n\t\t\t\t`dec`.description AS faith_response, c.corr_in AS messages_in, c.messages_out,\r\n\t\t\t\tact.description AS form_action, plt.description AS platform,\r\n\t\t\t\tcty.description AS country, gen.description AS gender, age.description AS age,\r\n\t\t\t\tDATE(c.created_on) AS creation_date, d.first_contact, d.last_contact, d.expire_date,\r\n\t\t\t\td.accept_christ, d.attend_church, d.attend_church_name, d.life_change,\r\n\t\t\t\td.read_bible, d.pray_regularly, d.share_faith, d.connect_to_church,\r\n\t\t\t\td.connect_to_church_name, d.connect_to_om, d.connect_to_om_name,\r\n\t\t\t\td.connect_to_mission, d.connect_to_mission_name,\r\n\t\t\t\tLEAST(1, d.connect_to_church + d.connect_to_om + d.connect_to_mission) AS connect_to_any,\r\n\t\t\t\td.adv_discipleship_materials,\r\n\t\t\t\totg_connector.display_name AS partner_name, connector_languages.description AS partner_language,\r\n\t\t\t\tconnector_countries.description AS partner_country,\r\n\t\t\t\totg_handoff.confirmed_handoff AS is_confirmed_handoff,\r\n\t\t\t\tif(otg_handoff.confirmed_handoff IS NOT NULL, COALESCE(om_handoff_confirmation.is_connected, 0), NULL) AS confirmed_by_om,\r\n\t\t\t\tom_handoff_confirmation.updated_on AS confirmed_by_om_on,\r\n\t\t\t\tif(otg_handoff.confirmed_handoff IS NOT NULL, COALESCE(connector_handoff_confirmation.is_connected, 0), NULL) AS confirmed_by_partner,\r\n\t\t\t\tconnector_handoff_confirmation.updated_on AS confirmed_by_partner_on\r\n\t\t\tFROM arc_disciples2 AS d\r\n\t\t\tLEFT JOIN arc_contacts AS c ON d.contact_id = c.id\r\n\t\t\tLEFT JOIN arc_contact_profile AS p ON d.contact_id = p.contact_id\r\n\t\t\tLEFT JOIN communication_channels AS ch ON c.comm_channel = ch.id\r\n\t\t\tLEFT JOIN arc_domains AS dom ON p.domain_id = dom.id\r\n\t\t\tLEFT JOIN arc_languages AS lan ON p.language_id = lan.id\r\n\t\t\tLEFT JOIN arc_answers AS `dec` ON p.faith_response_id = `dec`.id\r\n\t\t\tLEFT JOIN arc_answers AS act ON p.form_action_id = act.id\r\n\t\t\tLEFT JOIN arc_answers AS plt ON p.platform_id = plt.id\r\n\t\t\tLEFT JOIN arc_answers AS gen ON p.gender_id = gen.id\r\n\t\t\tLEFT JOIN arc_answers AS age ON p.age_id = age.id\r\n\t\t\tLEFT JOIN iso_3166 AS cty ON c.country = cty.id3 AND cty.id2 != \"US\"\r\n\r\n\t\t\tLEFT JOIN otg_handoff ON d.contact_id = otg_handoff.contact_id\r\n\t\t\tLEFT JOIN otg_handoff AS old_handoffs ON otg_handoff.contact_id = old_handoffs.contact_id\r\n\t\t\t\tAND otg_handoff.created_on < old_handoffs.created_on\r\n\t\t\tLEFT JOIN otg_connector ON otg_handoff.otg_connector_id = otg_connector.id\r\n\t\t\tLEFT JOIN arc_languages AS connector_languages ON otg_connector.language_id = connector_languages.id\r\n\t\t\tLEFT JOIN iso_3166 AS connector_countries ON otg_connector.country_id = connector_countries.id2\r\n\t\t\tLEFT JOIN otg_handoff_confirmation AS om_handoff_confirmation ON otg_handoff.id = om_handoff_confirmation.otg_handoff_id\r\n\t\t\t\tAND om_handoff_confirmation.confirmed_by = \"om\"\r\n\t\t\tLEFT JOIN otg_handoff_confirmation AS connector_handoff_confirmation ON otg_handoff.id = connector_handoff_confirmation.otg_handoff_id\r\n\t\t\t\tAND connector_handoff_confirmation.confirmed_by = \"otg_connector\"\r\n\r\n\t\t\tWHERE d.contact_id > ?\r\n\t\t\t\tAND d.expire_date > NOW()\r\n\t\t\t\tAND old_handoffs.id IS NULL\r\n\t\t\tORDER BY contact_id ASC LIMIT 1000\r\n```\r\n\r\nThe part of the query in question is `LEAST(1, d.connect_to_church + d.connect_to_om + d.connect_to_mission) AS connect_to_any,`"}}
{"actor":{"display_login":"mikegillis677"}}